/**
* 根据外键（主表ID）查找采购到货单明细
*
* @param con
* @param param
* @return
* @throws SQLException
*/
public Collection findPurchasesItemsByFK(Connection con, FinPurchaseForm param)
throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
Collection coll = new ArrayList();
try {
sql =
"select a.*, b.item_code, b.name as item_name "
+ "from fin_ps_dtl a inner join prd_items b on a.item_id = b.item_id "
+ "where a.ps_id = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, param.getPsID());
rs = pstmt.executeQuery();
while (rs.next()) {
FinPurchaseItemsForm data = new FinPurchaseItemsForm();
data.setPsDtlID(rs.getInt("PS_DTL_ID"));
data.setPsID(rs.getInt("PS_ID"));
data.setItemID(rs.getInt("ITEM_ID"));
data.setItemCode(rs.getString("ITEM_CODE"));
data.setItemName(rs.getString("ITEM_NAME"));
data.setPurQty(rs.getDouble("PUR_QTY"));
data.setPurPrice(rs.getDouble("PUR_PRICE"));
data.setTaxPrice(rs.getDouble("TAX_PRICE"));
data.setPurAmt(rs.getDouble("PUR_AMT"));
data.setTaxAmt(rs.getDouble("TAX_AMT"));
data.setAmt(rs.getDouble("AMT"));
data.setTax(rs.getDouble("TAX"));
data.setUseQty(rs.getDouble("USE_QTY"));
data.setFinishQty(rs.getDouble("FINISH_QTY"));
data.setFinishAmt(rs.getDouble("FINISH_AMT"));
data.setReturnQty(rs.getDouble("RETURN_QTY"));
data.setStatus(rs.getString("STATUS"));
coll.add(data);
}
} catch (SQLException e) {
logger.error("query error: [" + param.getPsID() + "]");
throw e;
} finally {
if (rs != null)
try {
rs.close();
} catch (Exception e) {
}
if (pstmt != null)
try {
pstmt.close();
} catch (Exception e) {
}
}
return coll;
}
/**
* 根据发票明细的一些（个）主键查询发票明细行 用于弹出页关闭返回主页面
*
* @param con
* @param PKs：格式为用逗号分开的字符串格式，如：1001,1002,1003；
* @return
* @throws SQLException
*/
public Collection findCheckedPurchasesItemsByPKs(Connection con, String PKs) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
Collection coll = new ArrayList();
try {
sql =
"select d.*, b.pro_no, b.pro_name, c.op_desc, e.item_code, e.name as item_name "
+ "from fin_ps_mst a inner join providers b on a.pro_no = b.pro_no "
+ "inner join fin_operation_mst c on a.operation_class = c.operation_class "
+ "inner join fin_ps_dtl d on a.ps_id = d.ps_id "
+ "inner join prd_items e on d.item_id = e.item_id "
+ "where a.status in ('2', '3') and d.status in('1', '2') "
+ "and d.ps_dtl_id in ("
+ PKs
+ ")";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
FinPurchaseItemsForm data = new FinPurchaseItemsForm();
data.setPsDtlID(rs.getInt("PS_DTL_ID"));
data.setPsID(rs.getInt("PS_ID"));
data.setItemID(rs.getInt("ITEM_ID"));
data.setItemCode(rs.getString("ITEM_CODE"));
data.setItemName(rs.getString("ITEM_NAME"));
data.setPurQty(rs.getDouble("PUR_QTY"));
data.setPurPrice(rs.getDouble("PUR_PRICE"));
data.setTaxPrice(rs.getDouble("TAX_PRICE"));
data.setPurAmt(rs.getDouble("PUR_AMT"));
data.setTaxAmt(rs.getDouble("TAX_AMT"));
data.setAmt(rs.getDouble("AMT"));
data.setTax(rs.getDouble("TAX"));
data.setUseQty(rs.getDouble("USE_QTY"));
data.setFinishQty(rs.getDouble("FINISH_QTY"));
data.setFinishAmt(rs.getDouble("FINISH_AMT"));
data.setReturnQty(rs.getDouble("RETURN_QTY"));
data.setStatus(rs.getString("STATUS"));
data.setProNO(rs.getString("PRO_NO"));
data.setProName(rs.getString("PRO_NAME"));
coll.add(data);
}
} catch (SQLException e) {
logger.error("query error: [" + PKs + "]");
throw e;
} finally {
if (rs != null)
try {
rs.close();
} catch (Exception e) {
}
if (pstmt != null)
try {
pstmt.close();
} catch (Exception e) {
}
}
return coll;
}
/**
* 根据供应商或产品查询主表状态为：2-审核，3-部分结算 从表状态为：1-未结算，2-部分结算的记录 用于新增发票时的弹出页
*
* @param con
* @param param
* @return
* @throws SQLException
*/
public Collection findCheckedPurchasesItems(Connection con, FinPurchaseForm param)
throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
Collection coll = new ArrayList();
try {
sql =
"select d.*, b.pro_no, b.pro_name, c.op_desc, e.item_code, e.name as item_name, "
+ "a.ps_code, purchasedate, res_no "
+ "from fin_ps_mst a inner join providers b on a.pro_no = b.pro_no "
+ "inner join fin_operation_mst c on a.operation_class = c.operation_class "
+ "inner join fin_ps_dtl d on a.ps_id = d.ps_id "
+ "inner join prd_items e on d.item_id = e.item_id "
+ "where a.status in ('2', '3') and d.status in('1', '2') ";
if (param.getProOrItem() == 1) { // 供应商名称
sql += "and b.pro_name like '%" + param.getProOrItemCondition() + "%' ";
} else if (param.getProOrItem() == 2) { // 产品名称
sql += "and e.name like '%" + param.getProOrItemCondition() + "%' ";
} else if (param.getProOrItem() == 3) { // 供应商代码
sql += "and b.pro_no = '" + param.getProOrItemCondition() + "' ";
} else if (param.getProOrItem() == 4) { // 产品代码
sql += "and e.item_code = '" + param.getProOrItemCondition() + "' ";
}
if (param.getIds() != null && !param.getIds().equals("")) {
sql += "and d.ps_dtl_id not in(" + param.getIds() + ")";
}
// sql += "order by item_name ";
if (param.getOrderByCondition1() != 0) {
switch (param.getOrderByCondition1()) {
case 1:
sql += "order by purchasedate ";
break;
case 2:
sql += "order by e.item_code ";
break;
case 3:
sql += "order by e.name ";
break;
case 4:
sql += "order by b.pro_no ";
break;
case 5:
sql += "order by b.pro_name ";
break;
case 6:
sql += "order by d.PUR_QTY ";
break;
case 7:
sql += "order by d.PUR_PRICE ";
break;
case 8:
sql += "order by d.USE_QTY ";
break;
case 9:
sql += "order by d.FINISH_QTY ";
break;
case 10:
sql += "order by a.ps_code ";
break;
case 11:
sql += "order by res_no ";
break;
}
if (param.getAscOrDesc1() == 0) {
sql += "asc ";
} else {
sql += "desc ";
}
}
if (param.getOrderByCondition2() != 0) {
if (param.getOrderByCondition1() == 0) {
switch (param.getOrderByCondition2()) {
case 1:
sql += "order by purchasedate ";
break;
case 2:
sql += "order by e.item_code ";
break;
case 3:
sql += "order by e.name ";
break;
case 4:
sql += "order by b.pro_no ";
break;
case 5:
sql += "order by b.pro_name ";
break;
case 6:
sql += "order by d.PUR_QTY ";
break;
case 7:
sql += "order by d.PUR_PRICE ";
break;
case 8:
sql += "order by d.USE_QTY ";
break;
case 9:
sql += "order by d.FINISH_QTY ";
break;
case 10:
sql += "order by a.ps_code ";
break;
case 11:
sql += "order by res_no ";
break;
}
if (param.getAscOrDesc2() == 0) {
sql += "asc ";
} else {
sql += "desc ";
}
} else {
switch (param.getOrderByCondition2()) {
case 1:
sql += ",purchasedate ";
break;
case 2:
sql += ",e.item_code ";
break;
case 3:
sql += ",e.name ";
break;
case 4:
sql += ",b.pro_no ";
break;
case 5:
sql += ",b.pro_name ";
break;
case 6:
sql += ",d.PUR_QTY ";
break;
case 7:
sql += ",d.PUR_PRICE ";
break;
case 8:
sql += ",d.USE_QTY ";
break;
case 9:
sql += ",d.FINISH_QTY ";
break;
case 10:
sql += ",a.ps_code ";
break;
case 11:
sql += ",res_no ";
break;
}
if (param.getAscOrDesc2() == 0) {
sql += "asc ";
} else {
sql += "desc ";
}
}
}
if (param.getOrderByCondition3() != 0) {
if (param.getOrderByCondition1() == 0 && param.getOrderByCondition2() == 0) {
switch (param.getOrderByCondition3()) {
case 1:
sql += "order by purchasedate ";
break;
case 2:
sql += "order by e.item_code ";
break;
case 3:
sql += "order by e.name ";
break;
case 4:
sql += "order by b.pro_no ";
break;
case 5:
sql += "order by b.pro_name ";
break;
case 6:
sql += "order by d.PUR_QTY ";
break;
case 7:
sql += "order by d.PUR_PRICE ";
break;
case 8:
sql += "order by d.USE_QTY ";
break;
case 9:
sql += "order by d.FINISH_QTY ";
break;
case 10:
sql += "order by a.ps_code ";
break;
case 11:
sql += "order by res_no ";
break;
}
if (param.getAscOrDesc3() == 0) {
sql += "asc ";
} else {
sql += "desc ";
}
} else {
switch (param.getOrderByCondition3()) {
case 1:
sql += ",purchasedate ";
break;
case 2:
sql += ",e.item_code ";
break;
case 3:
sql += ",e.name ";
break;
case 4:
sql += ",b.pro_no ";
break;
case 5:
sql += ",b.pro_name ";
break;
case 6:
sql += ",d.PUR_QTY ";
break;
case 7:
sql += ",d.PUR_PRICE ";
break;
case 8:
sql += ",d.USE_QTY ";
break;
case 9:
sql += ",d.FINISH_QTY ";
break;
case 10:
sql += ",a.ps_code ";
break;
case 11:
sql += ",res_no ";
break;
}
if (param.getAscOrDesc3() == 0) {
sql += "asc ";
} else {
sql += "desc ";
}
}
}
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
FinPurchaseItemsForm data = new FinPurchaseItemsForm();
data.setPsDtlID(rs.getInt("PS_DTL_ID"));
data.setPsID(rs.getInt("PS_ID"));
data.setItemID(rs.getInt("ITEM_ID"));
data.setItemCode(rs.getString("ITEM_CODE"));
data.setItemName(rs.getString("ITEM_NAME"));
data.setPurQty(rs.getDouble("PUR_QTY"));
data.setPurPrice(rs.getDouble("PUR_PRICE"));
data.setTaxPrice(rs.getDouble("TAX_PRICE"));
data.setPurAmt(rs.getDouble("PUR_AMT"));
data.setTaxAmt(rs.getDouble("TAX_AMT"));
data.setAmt(rs.getDouble("AMT"));
data.setTax(rs.getDouble("TAX"));
data.setUseQty(rs.getDouble("USE_QTY"));
data.setFinishQty(rs.getDouble("FINISH_QTY"));
data.setFinishAmt(rs.getDouble("FINISH_AMT"));
data.setReturnQty(rs.getDouble("RETURN_QTY"));
data.setStatus(rs.getString("STATUS"));
data.setProNO(rs.getString("PRO_NO"));
data.setProName(rs.getString("PRO_NAME"));
data.setResNO(rs.getString("RES_NO"));
data.setPsCode(rs.getString("PS_CODE"));
data.setPurchaseDate(rs.getDate("PURCHASEDATE"));
coll.add(data);
}
System.out.println(sql);
} catch (SQLException e) {
logger.error("query error: [" + param.getProOrItemCondition() + "]");
throw e;
} finally {
if (rs != null)
try {
rs.close();
} catch (Exception e) {
}
if (pstmt != null)
try {
pstmt.close();
} catch (Exception e) {
}
}
return coll;
}