/**
* 查询过期发票,关联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;
}
/**
* 根据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;
}
/**
* 根据查询条件查询发票结果，从视图中查询
*
* @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;
}