/**
   * 此方法用于查询付款单内所选的业务单据清单
   *
   * @return XML element.
   * @throws SQLException
   * @throws IOException
   */
  public Element getSheetset() throws SQLException, IOException {
    String sql_sheet =
        " SELECT "
            + " p.sheetid, p.docno, p.doctype, sn.sheettypename doctypename, "
            + " p.shopid, s.shopname, "
            + " name4code(p.logisticsid,'logistics') as logisticsid, "
            + " p.noteremark, p.saleamt,p.paydocamt, p.paytaxamt17, p.paytaxamt13, "
            + " p.docamt, p.docamt17, p.taxamt13, "
            + " to_char(p.docdate,'YYYY-MM-DD') docdate,p.majorid,p.paytypeid,pt.paytypename "
            + " FROM "
            + this.tab_item
            + " p "
            + " JOIN  shop s on ( s.shopid=p.shopid ) "
            + " JOIN  sheettype sn on ( sn.sheettype = p.doctype  and p.doctype<>5201 and p.doctype<>5258) "
            + " left join paytype pt on (pt.paytypeid=p.paytypeid) "
            + " WHERE p.sheetid= ? order by p.shopid";

    System.out.println(sql_sheet);
    PreparedStatement pstmt = conn.prepareStatement(sql_sheet);
    pstmt.setString(1, this.sheetid);
    ResultSet rs = pstmt.executeQuery();
    XResultAdapter adapter = new XResultAdapter(rs);
    Element elm_charge = adapter.getRowSetElement("unpaidsheet_list", "rows");
    rs.close();
    pstmt.close();
    return new Element("sheetset").addContent(elm_charge);
  }
  /**
   * 此方法用于查询付款单的头部信息 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;
  }
 /**
  * 查询没有付款确认前的付款单的固定扣项信息
  *
  * @param with_tax 主调模块用此参数表达待查询的扣项是否含税. 不含税: {0} 含税: {1} 含税+不含税: {0,1}
  * @return
  * @throws SQLException
  * @throws InvalidDataException
  * @throws IOException
  */
 public Element getChargeGenerated(int with_tax)
     throws SQLException, InvalidDataException, IOException {
   String sql_charge =
       " SELECT p.chargecodeid docno, "
           + " p.shopid, s.shopname, "
           + " p.chargecodeid, c.chargename, "
           + " -p.chargeamt chargeamt, "
           + " name4code( p.invoicemode,'invoicemode' ) as invoicemode, p.noteremark,name4code(p.settleflag,'settlemode') as settleflag "
           + " FROM paymentnotecharge p "
           + " JOIN chargecode c ON (p.chargecodeid=c.chargecodeid) "
           + " JOIN shop s ON (s.shopid=p.shopid) "
           + " WHERE  p.sheetid = ? "
           + " AND p.invoicemode =? and p.settleflag=1 ";
   System.out.println(sql_charge);
   PreparedStatement pstmt = conn.prepareStatement(sql_charge);
   pstmt.setString(1, this.sheetid);
   pstmt.setInt(2, with_tax);
   ResultSet rs = pstmt.executeQuery();
   XResultAdapter adapter = new XResultAdapter(rs);
   String sheetName = (with_tax == 1) ? "chargegenerated_with_tax" : "chargegenerated_without_tax";
   Element elm_charge = adapter.getRowSetElement(sheetName, "rows");
   rs.close();
   pstmt.close();
   return elm_charge;
 }
  /**
   * 此方法用于查询付款单内所选的业务单据清单,按门店分组
   *
   * @return
   * @throws SQLException
   * @throws IOException
   */
  public Element getSheetsetGroupByShop() throws SQLException, IOException {
    String sql4unpaidsheet =
        " SELECT "
            + " p.sheetid, p.docno, p.doctype, sn.sheettypename doctypename, "
            + " p.shopid, s.shopname, "
            + " name4code(p.logisticsid,'logistics') as logisticsid, "
            + " p.noteremark, p.saleamt,p.paydocamt, p.paytaxamt17, p.paytaxamt13, (p.paydocamt+p.paytaxamt17+p.paytaxamt13) total_payamt, "
            + " p.docamt, p.docamt17, p.taxamt13, "
            + " to_char(p.docdate,'YYYY-MM-DD') docdate,p.majorid,p.paytypeid,pt.paytypename "
            + " FROM "
            + this.tab_item
            + " p "
            + " JOIN  shop s on ( s.shopid=p.shopid ) "
            + " JOIN  sheettype sn on ( sn.sheettype = p.doctype  and p.doctype<>5201 and p.doctype<>5258 and p.doctype<>5205 ) "
            + " left join paytype pt on (pt.paytypeid=p.paytypeid) "
            + " WHERE p.sheetid= ? order by p.shopid";

    String sql4csalecost =
        "select sh.shopid,sh.shopname,s.name doctypename,to_char(p.docdate,'YYYYMM') docdate,sum(p.saleamt) saleamt,sum(p.paydocamt+p.paytaxamt17+p.paytaxamt13) total_payamt "
            + "from "
            + this.tab_item
            + " p join shop sh on(p.shopid=sh.shopid) "
            + "join serialnumber s on(p.doctype=s.serialid) "
            + "where p.sheetid= ? and p.doctype=5205 "
            + " group by sh.shopid,sh.shopname,s.name,to_char(p.docdate,'YYYYMM') order by sh.shopid";

    System.out.println(sql4unpaidsheet);
    PreparedStatement pstmt = conn.prepareStatement(sql4unpaidsheet);
    pstmt.setString(1, this.sheetid);
    ResultSet rs = pstmt.executeQuery();
    XResultAdapter adapter = new XResultAdapter(rs);
    Element elmSet = new Element("sheetset");
    Element elm_unpaid = adapter.getRowSetElement("unpaidsheet_list", "rows");
    elmSet.addContent(elm_unpaid);
    rs.close();
    pstmt.close();

    pstmt = conn.prepareStatement(sql4csalecost);
    pstmt.setString(1, this.sheetid);
    rs = pstmt.executeQuery();
    XResultAdapter adapter2 = new XResultAdapter(rs);
    Element elm_salecost = adapter2.getRowSetElement("salecost_list", "rows");
    elmSet.addContent(elm_salecost);
    rs.close();
    pstmt.close();

    return elmSet;
  }
 /**
  * 此方法用于查询付款单的发票信息
  *
  * @return XML element.
  * @throws SQLException
  * @throws IOException
  */
 public Element getInvoice() throws SQLException, IOException {
   String sql_inovice =
       " SELECT  "
           + " d.sheetid, d.invoiceno, d.invoicedate, d.goodsdesc, "
           + " d.taxrate, d.taxableamt, d.taxamt FROM "
           + this.tab_invoice
           + " d "
           + " WHERE d.sheetid = ?  ";
   System.out.println(sql_inovice);
   PreparedStatement pstmt = conn.prepareStatement(sql_inovice);
   pstmt.setString(1, this.sheetid);
   ResultSet rs = pstmt.executeQuery();
   XResultAdapter adapter = new XResultAdapter(rs);
   Element elm_invoice = adapter.getRowSetElement("paymentnotedtl", "rows");
   rs.close();
   pstmt.close();
   return elm_invoice;
 }
  public Element getChargeGroupByCharge(int with_tax)
      throws InvalidDataException, SQLException, IOException {

    String sql_charge =
        " SELECT  "
            + " c.noteno docno, c.shopid, s.shopname, "
            + " c.chargecodeid, cc.chargename, c.chargeamt,  "
            + " name4code( c.invoicemode,'invoicemode' ) as invoicemode, c.noteremark  "
            + " FROM "
            + this.tab_chargesum
            + " c "
            + " JOIN shop s ON ( s.shopid = c.shopid ) "
            + " JOIN chargecode cc ON ( cc.chargecodeid = c.chargecodeid ) "
            + " JOIN "
            + this.tab_item
            + " p ON (p.docno = c.noteno) "
            + " WHERE  p.sheetid = ? "
            + " AND c.invoicemode =? "
            + " union "
            + " select '' as docno, '' as shopid, '' as shopname,   "
            + " i.chargecodeid, cc.chargename,sum(i.docamt+i.taxamt17+taxamt13)*-1 as chargeamt,  "
            + " '' as invoicemode,'' noteremark  "
            + " from "
            + this.tab_item
            + " i "
            + " JOIN shop s ON ( s.shopid = i.shopid ) "
            + " JOIN chargecode cc ON ( cc.chargecodeid = i.chargecodeid ) "
            + " WHERE i.sheetid=? and i.doctype=5258 and i.invoicemode=? "
            + " group by i.chargecodeid, cc.chargename ";
    System.out.println(sql_charge);
    PreparedStatement pstmt = conn.prepareStatement(sql_charge);
    pstmt.setString(1, this.sheetid);
    pstmt.setInt(2, with_tax);
    pstmt.setString(3, this.sheetid);
    pstmt.setInt(4, with_tax);
    ResultSet rs = pstmt.executeQuery();
    XResultAdapter adapter = new XResultAdapter(rs);
    String sheetName = (with_tax == 1) ? "chargesum_with_tax" : "chargesum_without_tax";
    Element elm_charge = adapter.getRowSetElement(sheetName, "rows");
    rs.close();
    pstmt.close();

    return elm_charge;
  }