Exemplo n.º 1
0
  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});
    }
  }
Exemplo n.º 2
0
  @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;
  }
Exemplo n.º 3
0
  /**
   * 此方法用于查询付款单的头部信息 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;
  }
Exemplo n.º 4
0
  /**
   * 如果机构是公司或者配送公司(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();
  }
Exemplo n.º 5
0
  @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;
  }
Exemplo n.º 6
0
  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);
    }
  }
Exemplo n.º 7
0
  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");
  }
Exemplo n.º 8
0
  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");
  }
Exemplo n.º 9
0
  /**
   * 取打印的烟草公司名称
   *
   * @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;
  }
Exemplo n.º 10
0
 public String getVenderId() throws SQLException {
   String sql = " select venderid from purchase" + month + " where sheetid=?";
   return SqlUtil.queryPS4SingleColumn(conn, sql, sheetid).get(0);
 }
Exemplo n.º 11
0
 /**
  * 按单品汇总
  *
  * @return
  */
 private Element getGoodsGroup(String sheetid) {
   return SqlUtil.getRowSetElement(conn, sql4group, sheetid, "goodsgroup");
 }
Exemplo n.º 12
0
 /**
  * 订货审批单对应要货门店清单.
  *
  * @return
  */
 private Element getOderCatalogue(String sheetid) {
   return SqlUtil.getRowSetElement(conn, sql4cat, sheetid, "body");
 }
Exemplo n.º 13
0
 @Override
 public Element getBody(String sheetid) {
   return SqlUtil.getRowSetElement(conn, sql4Body, sheetid, "bodydetail");
 }
Exemplo n.º 14
0
  /**
   * 查询数据库,生成一个包含门店信息的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();
  }