public void sjPrint(String sheetid) {
String sqlUp = "update paymentnotevenderask set sjprintdate=sysdate,sjflag=1 where sheetid=?";
String sqlIns =
"insert into paymentnotevenderask(sheetid,sjflag,sjprintdate) values(?,1,sysdate)";
// 先更新，更新失败，直接插入
int rows = SqlUtil.executePS(conn, sqlUp, new String[] {sheetid});
if (rows == 0) {
rows = SqlUtil.executePS(conn, sqlIns, new String[] {sheetid});
}
}
@Override
public Element search(Map parms) {
Filter filter = cookFilter(parms);
if (filter.count() == 0) throw new InvalidDataException("请设置查询过滤条件.");
int count = this.getCount(filter);
if (count > VSSConfig.getInstance().getRowsLimitHard())
throw new InvalidDataException("满足条件的记录数已超过系统处理上限,请重新设置查询条件.");
String sql =
" SELECT "
+ " t.sheetid, status4purchase( t.status ) status, t.validdays, "
+ " t.orderdate, t.venderid, v.vendername, sg.categoryname majorname, pay.paytypename, "
+ " t.deadline, name4code(t.logistics,'logistics') as logistics, t.note, "
+ " t.editor, t.editdate, t.checker, t.checkdate, t.releasedate, t.readtime ,status4purchasetype(t.purchasetype) purchasetype "
+ " FROM (SELECT DISTINCT p0.sheetid,p0.validdays,p0.orderdate,p0.venderid,p0.purchasetype, "
+ " p0.deadline,p0.logistics,p0.note,p0.editor,p0.editdate,p0.checker,p0.checkdate, "
+ " p0.paytypeid,p0.sgroupid,c.status,c.releasedate, c.readtime FROM purchase0_bak p0 "
+ " JOIN purchase_bak p ON ( p0.sheetid = p.refsheetid ) "
+ " JOIN cat_order_bak c ON ( c.sheetid = p0.sheetid ) WHERE "
+ filter.toString()
+ ") t "
+ " JOIN vender v ON ( v.venderid = t.venderid ) "
+ " JOIN paytype pay ON ( pay.paytypeid = t.paytypeid ) "
+ " left JOIN category sg ON ( sg.categoryid = t.sgroupid ) "
+ " ORDER BY t.checkdate DESC, t.status";
Element elm_cat = SqlUtil.getRowSetElement(conn, sql, "rowset");
elm_cat.setAttribute("row_total", "" + count);
return elm_cat;
}
/**
* 此方法用于查询付款单的头部信息 NOTE: real_pay: 表示实际应支付金额( 已扣除"补税差应付调整金额" 及 "电汇费" )
*
* @return XML element.
* @throws SQLException
* @throws IOException
*/
public Element getHead() throws SQLException, IOException {
String sql =
" SELECT "
+ " p.sheetid, b.bookname, b.booktitle,b.booklogofname,"
+ " p.venderid, v.vendername, vb.bankname, vb.accno bankaccno, p.finamt,"
+ " paymode.paymodename paymodename, p.payableamt, suspayamt,chequeamt,"
+ " p.chargeamt, p.planpaydate,p.TaxAmt17,p.TaxAmt13, "
+ " p.invtotalamt17,p.invtotalamt13,p.invtotalamt0,p.payamt, p.note, p.editor, p.editdate, '"
+ SqlUtil.toLocal(PayamtToChinese())
+ "' as real_pay FROM "
+ this.tab_head
+ " p "
+ " JOIN book b ON (b.bookno=p.payshopid) "
+ " LEFT JOIN paymode ON (p.paymodeid=paymode.paymodeid) "
+ " JOIN vender v ON (v.venderid=p.venderid) "
+ " LEFT JOIN vender_bank vb ON (vb.venderid=p.venderid) "
+ " WHERE sheetid = ? ";
System.out.println(sql);
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, sheetid);
ResultSet rs = pstmt.executeQuery();
XResultAdapter adapter = new XResultAdapter(rs);
Element elm_sheethead = adapter.getRowSetElement("head", "rows");
rs.close();
pstmt.close();
return elm_sheethead;
}
/**
* 如果机构是公司或者配送公司(0/10/20), 则调用此方法添加记录.
*
* @param typeid
* @throws SQLException
*/
private void addCompany(int typeid) throws SQLException {
String sql =
" SELECT shopid, shopname FROM shop " + " WHERE shoptype = " + typeid + " ORDER BY shopid ";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
Element elm_opt;
while (rs.next()) {
String id = rs.getString("shopid");
String shopname = rs.getString("shopname");
id = (id == null) ? "" : SqlUtil.fromLocal(id).trim();
shopname = (shopname == null) ? "" : SqlUtil.fromLocal(shopname).trim();
shopname = id + " " + shopname;
vec_shopid.add(id);
elm_opt = new Element("option");
elm_opt.setAttribute("value", id);
elm_opt.addContent(shopname);
this.elm_ctrl.addContent(elm_opt);
}
rs.close();
st.close();
}
@Override
public int getCount(Filter filter) {
String sql =
" SELECT count( DISTINCT p0.sheetid ) FROM purchase0_bak p0 "
+ " JOIN purchase_bak p ON ( p0.sheetid = p.refsheetid ) "
+ " JOIN cat_order_bak c ON ( c.sheetid = p0.sheetid ) WHERE "
+ filter.toString();
String temp = SqlUtil.querySQL4SingleColumn(conn, sql).get(0);
int rows = 0;
if (temp != null) {
rows = Integer.parseInt(temp);
}
return rows;
}
public void setPrintInfo(String sheetid) {
String company = null;
String shopid = null;
int controltype = 0;
int majorid = 0;
// controltype 门店控制符合，MR公司用
// 取得门店和课类,抬头公司
String sql_sel_shop =
"select a.destshopid,a."
+ majorName
+ ",s.controltype,s.shopname from purchase_bak a "
+ " inner join shop s on s.shopid=a.destshopid "
+ " where refsheetid=? and rownum=1";
ResultSet rs = SqlUtil.queryPS(conn, sql_sel_shop, sheetid);
try {
if (rs.next()) {
shopid = rs.getString("destshopid") + "X";
majorid = rs.getInt(majorName);
controltype = rs.getInt("controltype");
company = SqlUtil.fromLocal(rs.getString("shopname"));
// this.logo = rs.getString("booklogofname");
}
SqlUtil.close(rs);
// 如果是烟草课类，则修改打印抬头和logo
// if (majorid == VSSConfig.getInstance().getTobatoMajorid() && !shopid.equals("")) {
String sql_sel_headshopid = "select s.shopname from shop s " + " where s.shopid=? ";
rs = SqlUtil.queryPS(conn, sql_sel_headshopid, shopid);
if (rs.next()) {
company = SqlUtil.fromLocal(rs.getString("shopname"));
// this.logo = rs.getString("booklogofname");
}
SqlUtil.close(rs);
// 民润店
/*} else if (controltype == 2) {
company = "MR";
this.logo = VSSConfig.getInstance().getPrintTobatoLogo();
}*/
if (token.site.getSid() == 6) {
this.title = "送货单";
}
this.title = company + this.title + "-已归档";
this.logo = "../img/" + this.logo;
} catch (SQLException e) {
throw new DAOException(e);
}
}
private Element getBody() throws SQLException, InvalidDataException {
String sql =
" SELECT "
+ " s.shopid destshopid,s.shopname destshopname,sh.shopname shopname,sh.shopid shopid, "
+ " i.goodsid, i.barcode, g.goodsname, g.spec, g.unitname, g.ycomp,g.qadays,"
+ " i.qty, i.pkgqty, i.pkgvolume,trunc((i.qty / i.pkgvolume)) as pkqty, i.memo,i.presentqty, "
+ " i.concost,i.cost,i.firstdisc,(i.qty*i.cost) sumcost "
+ " FROM purchase"
+ month
+ " p "
+ " JOIN purchaseitem"
+ month
+ " i ON (p.sheetid=i.sheetid) "
+ " JOIN goods g ON (i.goodsid=g.goodsid) "
+ " JOIN shop s ON (s.shopid=p.destshopid) "
+ " JOIN shop sh ON (sh.shopid=p.shopid) "
+ " WHERE p.sheetid=? order by p.shopid,g.deptid,i.goodsid";
return SqlUtil.getRowSetElement(conn, sql, sheetid, "body");
}
private Element getHead() throws SQLException, InvalidDataException {
String sql =
" SELECT "
+ " p.sheetid, p.refsheetid, p.destshopid,p.shopid, sh.shopname destshopname , s.shopname , s.address shopaddr ,s.telno shoptel ,p.sgroupid,s.shopstatus, p.discountrate, "
+ " c.categoryname, p.paytypeid, pt.paytypename, p.logistics , name4code(p.logistics,'logistics') as logisticsname, "
+ " p.orderdate, p.validdays, (p.orderdate + p.validdays - 1) deadline, p.venderid, v.vendername, v.faxno venderfax, v.address venderaddr,"
+ " p.deliverdate, p.delivertimeid, p.vdeliverdate,p.note, p.editor, p.checker, substr(pt.paytypename,1,1) paytypeflag, "
+ " dt.startTime || '-' || dt.endTime delivertime, s.controltype "
+ " FROM purchase"
+ month
+ " p "
+ " INNER JOIN shop s ON ( s.shopid=p.shopid ) "
+ " INNER JOIN shop sh ON ( sh.shopid=p.destshopid ) "
+ " left JOIN category c ON ( c.categoryid=p.sgroupid ) "
+ " INNER JOIN paytype pt ON ( pt.paytypeid=p.paytypeid ) "
+ " INNER JOIN vender v ON ( v.venderid=p.venderid ) "
+ " LEFT OUTER JOIN DeliverTime dt ON ( dt.DeliverID = p.deliverTimeid ) "
+ " WHERE p.sheetid=? ";
return SqlUtil.getRowSetElement(conn, sql, sheetid, "head");
}
/**
* 取打印的烟草公司名称
*
* @return
*/
private void setPrintInfo() throws SQLException {
String company = null;
String shopid = null;
int controltype = 0;
int majorid = 0;
// controltype 门店控制符合，MR公司用
// 取得门店和课类,抬头公司
String sql_sel_shop =
"select a.shopid,a.sgroupid,s.controltype,s.shopname from purchase"
+ month
+ " a "
+ " inner join shop s on s.shopid=a.shopid "
+ " where sheetid=?";
ResultSet rs = SqlUtil.queryPS(conn, sql_sel_shop, sheetid);
if (rs.next()) {
shopid = rs.getString("shopid") + "X";
majorid = rs.getInt("sgroupid");
controltype = rs.getInt("controltype");
company = SqlUtil.toLocal(rs.getString("shopname"));
// this.logo = rs.getString("booklogofname");
}
SqlUtil.close(rs);
// 如果是烟草课类，则修改打印抬头和logo
/*if( majorid == VSSConfig.getInstance().getTobatoMajorid() && !shopid.equals("") ){
String sql_sel_headshopid = "select b.booktitle,b.booklogofname from shop s " +
" inner join book b on (b.bookno=s.bookno and s.shopstatus=1) " +
" where s.shopid=? ";*/
String sql_sel_headshopid = "select s.shopname from shop s " + " where s.shopid=? ";
rs = SqlUtil.queryPS(conn, sql_sel_headshopid, sheetid);
if (rs.next()) {
company = SqlUtil.toLocal(rs.getString("shopname"));
// this.logo = rs.getString("booklogofname");
}
SqlUtil.close(rs);
// 民润店
/*}else if(controltype==2){
company="MR";
this.logo = VSSConfig.getInstance().getPrintTobatoLogo();
}*/
this.title = company + "订货通知单";
this.logo = "../img/" + this.logo;
}
public String getVenderId() throws SQLException {
String sql = " select venderid from purchase" + month + " where sheetid=?";
return SqlUtil.queryPS4SingleColumn(conn, sql, sheetid).get(0);
}
/**
* 按单品汇总
*
* @return
*/
private Element getGoodsGroup(String sheetid) {
return SqlUtil.getRowSetElement(conn, sql4group, sheetid, "goodsgroup");
}
/**
* 订货审批单对应要货门店清单.
*
* @return
*/
private Element getOderCatalogue(String sheetid) {
return SqlUtil.getRowSetElement(conn, sql4cat, sheetid, "body");
}
@Override
public Element getBody(String sheetid) {
return SqlUtil.getRowSetElement(conn, sql4Body, sheetid, "bodydetail");
}
/**
* 查询数据库,生成一个包含门店信息的XML元素. 如果机构是门店或者配送中心(11/22), 则调用此方法添加记录.
*
* @throws SQLException
* @throws NamingException
*/
private void addShop(int typeid) throws NamingException, SQLException {
Element elm_opt;
String sql_grp =
" SELECT c.shopid, c.shopname "
+ " FROM shop c JOIN shop s ON (c.shopid=s.headshopid) "
+ " WHERE s.shoptype = "
+ typeid
+ " GROUP BY c.shopid,c.shopname ORDER BY 1 ";
PreparedStatement stmt_grp = conn.prepareStatement(sql_grp);
ResultSet rs = stmt_grp.executeQuery();
while (rs.next()) {
String groupid = rs.getString(1);
String groupname = rs.getString(2);
Element elm_grp = new Element("option");
elm_grp.setAttribute("style", "color:blue");
elm_grp.addContent(SqlUtil.fromLocal(groupname));
String sql_shop_count =
" SELECT COUNT(*) FROM shop "
+ " WHERE headshopid = ? "
+ " AND shoptype = "
+ typeid
+ " ORDER BY 1 ";
PreparedStatement pstmt_count = conn.prepareStatement(sql_shop_count);
pstmt_count.setString(1, groupid);
ResultSet rs_count = pstmt_count.executeQuery();
rs_count.next();
int rows = rs_count.getInt(1);
rs_count.close();
pstmt_count.close();
if (rows > 0) {
elm_ctrl.addContent(elm_grp);
}
String sql_shop =
" SELECT shopid, shopname, bookno FROM shop "
+ " WHERE headshopid = ? "
+ " AND shoptype = "
+ typeid
+ " ORDER BY 1 ";
PreparedStatement pstmt = conn.prepareStatement(sql_shop);
pstmt.setString(1, groupid);
ResultSet rs_shop = pstmt.executeQuery();
StringBuffer grpValue = new StringBuffer();
StringBuffer grpNames = new StringBuffer();
rows = 0;
while (rs_shop.next()) {
++rows;
elm_opt = new Element("option");
String shopid = rs_shop.getString("shopid");
String shopname = rs_shop.getString("shopname");
String bookno = rs_shop.getString("bookno");
shopid = (shopid == null) ? "" : SqlUtil.fromLocal(shopid).trim();
shopname = (shopname == null) ? "" : SqlUtil.fromLocal(shopname).trim();
bookno = (bookno == null) ? "" : SqlUtil.fromLocal(bookno).trim();
vec_shopid.add(shopid);
if (rows == 1) {
grpValue.append(shopid);
grpNames.append(shopname);
} else {
grpValue.append("," + shopid);
grpNames.append("," + shopname);
}
elm_opt.setAttribute("value", shopid);
elm_opt.setAttribute("display", shopname);
elm_opt.setAttribute("bookno", bookno);
elm_opt.setAttribute("style", "padding-left:6px;");
shopname = " " + shopid + " " + shopname;
elm_opt.addContent(shopname);
elm_ctrl.addContent(elm_opt);
}
rs_shop.close();
pstmt.close();
if (rows > 0) {
elm_grp.setAttribute("value", grpValue.toString());
elm_grp.setAttribute("display", grpNames.toString());
}
}
rs.close();
stmt_grp.close();
}