/** * 根据外键(主表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; }