コード例 #1
0
ファイル: ArInvoiceDao.java プロジェクト: zskang/scm
  /**
   * 查询过期发票,关联customer、order、invoice表
   *
   * @return
   */
  public Page<ArInvoice> queryOverDueInvoice(Page<ArInvoice> page) {
    Page<ArInvoice> result = page;
    Long total = 0l;
    List list =
        this.getSession()
            .createQuery(
                "select count(a) from ArInvoice a where a.status in ('New','In Progress','Overdue') and a.exprDate <= ? ")
            .setDate(0, new java.util.Date())
            .list();
    if (list != null && list.size() > 0) {
      total = Long.parseLong(list.get(0).toString());
    }
    // String hql = "from ArInvoice a where a.status in ('New','In Progress','Overdue') and
    // a.exprDate <= ? "; //a.invoice_id,a.invoice_no,a.order_no,a.comment,a.expr_date
    String sql =
        "select a.invoice_id,a.invoice_no,a.order_no,a.comment,a.expr_date,b.order_date,c.bus_email,c.cust_no from accounting.ar_invoices a,order.order b,customer.customer_addresses c "
            + " where a.order_no = b.order_no and c.cust_no = b.cust_no and c.addr_type = 'CONTACT' and a.status in ('New','In Progress','Overdue') and a.expr_date <= '"
            + Tools.getCurrentTime("yyyy-MM-dd")
            + "'  order by invoice_id desc";
    System.out.println(sql);
    List list1 =
        this.getSession()
            .createSQLQuery(sql)
            .setFirstResult((page.getPageNo() - 1) * page.getPageSize())
            .setMaxResults(page.getPageSize())
            .list();

    List<ArInvoice> list2 = new ArrayList<ArInvoice>();
    ArInvoice ar = null;
    for (int i = 0; i < list1.size(); i++) {
      ar = new ArInvoice();
      Object[] obj = (Object[]) list1.get(i);
      ar.setInvoiceId((Integer) obj[0]);
      ar.setInvoiceNo(obj[1] == null ? "" : obj[1].toString());
      ar.setOrderNo((Integer) obj[2]);
      ar.setComment(obj[3].toString());
      ar.setExprDate((Date) obj[4]);
      ar.setOrderDate((Date) obj[5]);
      ar.setBusEmail(obj[6].toString());
      ar.setCustNo(Tools.String2Integer((obj[7].toString())));
      list2.add(ar);
    }

    page.setTotalCount(total);
    page.setResult(list2);
    return result;
  }
コード例 #2
0
ファイル: ArInvoiceDao.java プロジェクト: zskang/scm
 /**
  * 根据order_NO查询出所有的invoice
  *
  * @param page
  * @param filters
  * @return
  */
 @SuppressWarnings({"unchecked", "rawtypes"})
 public Map searchInvoiceList(Page<ArInvoice> page, int orderNo, String invoiceNo) {
   Map m = new HashMap();
   String hql = " from ArInvoice a where a.orderNo = " + orderNo;
   if (invoiceNo != null && !invoiceNo.trim().equals("")) {
     hql += " and a.invoiceNo like '%" + invoiceNo + "%'";
   }
   hql += " order by a.invoiceId desc ";
   List list1 =
       this.getSession()
           .createQuery(hql)
           .setFirstResult((page.getPageNo() - 1) * page.getPageSize())
           .setMaxResults(page.getPageSize())
           .list(); // 获取结果
   hql = "select count(a) from ArInvoice a where a.orderNo = " + orderNo;
   if (invoiceNo != null && !invoiceNo.trim().equals("")) {
     hql += " and a.invoiceNo like '%" + invoiceNo + "%'";
   }
   Object obj = this.getSession().createQuery(hql).uniqueResult(); // 获取数量
   long total = Long.parseLong(obj.toString());
   m.put("total", total);
   m.put("list", list1);
   return m;
 }
コード例 #3
0
ファイル: ArInvoiceDao.java プロジェクト: zskang/scm
 /**
  * 根据查询条件查询发票结果,从视图中查询
  *
  * @param m
  * @return
  * @throws SQLException
  */
 public List<ArInvoiceView> list(Page<ArInvoiceView> page, Map m) throws SQLException {
   List<ArInvoiceView> list = new ArrayList<ArInvoiceView>();
   Connection conn = this.getSession().connection();
   String sql = "select * from accounting.v_ar_invoice_list a where 1= 1 ";
   if (m != null) {
     if (m.get("filter_LIKES_invoiceNo") != null
         && Tools.String2Integer(m.get("filter_LIKES_invoiceNo").toString()) != 0) {
       sql += " and a.invoice_id =" + m.get("filter_LIKES_invoiceNo");
     }
     if (m.get("filter_LIKES_invoiceId") != null
         && Tools.String2Integer(m.get("filter_LIKES_invoiceId").toString()) != 0) {
       sql +=
           " and a.invoice_id ="
               + Tools.String2Integer(m.get("filter_LIKES_invoiceId").toString());
     }
     if (m.get("filter_EQI_orderNo") != null
         && Tools.String2Integer(m.get("filter_EQI_orderNo").toString()) != 0) {
       sql += " and a.order_no =" + m.get("filter_EQI_orderNo");
     }
     if (m.get("filter_EQI_custNo") != null
         && Tools.String2Integer(m.get("filter_EQI_custNo").toString()) != 0) {
       sql += " and a.cust_no =" + m.get("filter_EQI_custNo");
     }
     if (m.get("filter_EQI_shipmentId") != null
         && Tools.String2Integer(m.get("filter_EQI_shipmentId").toString()) != 0) {
       sql += " and a.shipment_id = " + m.get("filter_EQI_shipmentId");
     }
     if (m.get("filter_EQS_status") != null && !m.get("filter_EQS_status").equals("")) {
       sql += " and a.status='" + m.get("filter_EQS_status") + "'";
     }
     if (m.get("filter_EQS_currency") != null && !m.get("filter_EQS_currency").equals("")) {
       sql += " and a.currency='" + m.get("filter_EQS_currency") + "'";
     }
     if (m.get("filter_GTD_invoiceDate") != null && !m.get("filter_GTD_invoiceDate").equals("")) {
       sql += " and a.invoice_date>='" + m.get("filter_GTD_invoiceDate") + "'";
     }
     if (m.get("filter_LTD_invoiceDate") != null && !m.get("filter_LTD_invoiceDate").equals("")) {
       sql += " and a.invoice_date<='" + m.get("filter_LTD_invoiceDate") + "'";
     }
   } else {
     //			sql += " and a.status !='"+Constant.STATUS_OVERDUE+"'";
   }
   sql += " order by a.invoice_id desc ";
   long start = (page.getPageNo() - 1) * page.getPageSize();
   sql += " limit " + start + ", " + page.getPageSize();
   PreparedStatement ps = conn.prepareStatement(sql);
   ResultSet rs = ps.executeQuery();
   ArInvoiceView a = null;
   while (rs.next()) {
     a = new ArInvoiceView();
     a.setInvoiceId(rs.getInt("invoice_id"));
     a.setInvoiceNo(rs.getString("invoice_no"));
     a.setStatus(rs.getString("status"));
     a.setPaidAmt(rs.getFloat("paid_amt"));
     a.setBalance(rs.getFloat("balance"));
     a.setOrderNo(rs.getInt("order_no"));
     a.setShipmentId(rs.getInt("shipment_id"));
     a.setCustNo(rs.getInt("cust_no"));
     a.setInvoiceType(rs.getString("invoice_type"));
     a.setInvoiceDate(new java.util.Date(rs.getTimestamp("invoice_date").getTime()));
     a.setTotalAmount(rs.getFloat("total_amount"));
     a.setCurrency(rs.getString("currency"));
     a.setSymbol(rs.getString("symbol"));
     a.setPrecision(rs.getInt("precision"));
     list.add(a);
   }
   rs.close();
   ps.close();
   conn.close();
   return list;
 }