public static Map searchOtherInfoByCustomId(GenericDelegator delegator,String customId,StringBuffer sqlCommletter){ SQLProcessor sqlP=new SQLProcessor(delegator.getEntityHelperName("CustomsRelationList")); StringBuffer sql=new StringBuffer(); sql.append(" SELECT ("); sql.append(" SELECT count(*) FROM GW_PDE_HEAD wph,GW_PDE_LIST_ORG gplo WHERE wph.ID=gplo.HEAD_ID "); sql.append(" AND wph.head_Status='customsStatusNew' AND gplo.COMM_LETTER_ID IN "); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(") as bg,");//报关 sql.append(" (SELECT count(distinct fi.COMM_LETTER_ID) FROM FEE_item fi ,FEE_CONFIRM_ITEM fci,Work_Invoice_Info wii "); sql.append(" WHERE fi.FEE_C_ITEM_ID=fci.ID AND wii.FEE_ID= fci.ID "); sql.append(" and fi.COMM_LETTER_ID in "); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(") as kp,");//开票 sql.append(" (SELECT count(*) FROM Gw_Back_Bill gbl WHERE gbl.COMM_LETTER_ID IN"); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(" ) AS ty,");//退运 sql.append(" (SELECT count(*) FROM GW_XZONE_MOVE WHERE COMM_LETTER_ID IN"); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(" ) AS qj,");//区间 sql.append(" (SELECT count(*) FROM GW_Move_Bill WHERE COMM_LETTER_ID IN"); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(" ) AS qn,");//区内 sql.append(" (SELECT count(*) FROM GW_Delivery_Out WHERE COMM_LETTER_ID IN"); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(" ) AS cq,");//出区 sql.append(" (SELECT count(*) FROM GW_Remove_Bill WHERE COMM_LETTER_ID IN"); sql.append(sqlCommletter); sql.append(" AND dc.CUSTOMER_ID='"+customId+"') "); sql.append(" ) AS fd");//反调拨单 Map resultsMap=new HashMap(); try { sqlP.prepareStatement(sql.toString()); System.out.println("searchOtherInfoByCustomId"+sql.toString()); ResultSet rs=sqlP.executeQuery(); if(rs.next()){ resultsMap.put("bg", rs.getString("bg")); resultsMap.put("ty", rs.getString("ty")); resultsMap.put("qj", rs.getString("qj")); resultsMap.put("qn", rs.getString("qn")); resultsMap.put("cq", rs.getString("cq")); resultsMap.put("fd", rs.getString("fd")); resultsMap.put("kp", rs.getString("kp")); } } catch (GenericDataSourceException e) { e.printStackTrace(); } catch (GenericEntityException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return resultsMap; }