package UIM; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Iterator; import java.util.List; import java.util.Map; import UIM.UIM01.BEANS.ColdCoilComm; import CoreFS.SA01.CoreIComponent; import CoreFS.SA06.CoreReturnObject; public class UIM030200 extends CoreIComponent{ /** * 查询可发量 * * @param dlivTp * @param ordNo * @param specStlGrd * @param hsTot * @return * @throws SQLException */ public CoreReturnObject queryOrdResultForSale(String dlivTp, String ordNo, String specStlGrd, Integer hsTot) throws SQLException { CoreReturnObject cro = new CoreReturnObject(); StringBuffer sqlBuffer = new StringBuffer(); sqlBuffer.append(" SELECT A.ORD_NO,\n"); sqlBuffer.append(" A.ORD_SEQ,\n"); sqlBuffer.append(" A.ORD_WGT / 1000 ORD_WGT,\n"); sqlBuffer.append(" C.ACT_WGT,\n"); sqlBuffer.append(" A.SPEC_STL_GRD STL_GRD,\n"); sqlBuffer .append(" TO_CHAR(A.ORD_THK || '*' || A.ORD_WTH) COIL_THK, \n"); // sqlBuffer.append(" P.ORD_PROG_CD,\n"); sqlBuffer .append(" (select cust_nm from tbz00_customer where cust_cd=B.CUST_CD)CUST_CD -- 客户代码 B.CUST_CD,\n"); // sqlBuffer.append(" ,(select cust_nm from tbz00_customer where // cust_cd=B.ORDCUST_CD)ORDCUST_CD -- 订单代码B.ORDCUST_CD,\n"); // sqlBuffer.append(" , (select sm_cfnm from tbz00_commcd where sm_cd= // A.GET_ON_PCD and lg_cd='A01013')GET_ON_PCD \n"); sqlBuffer .append(" ,( SELECT SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD= A.DEST_PCD AND LG_CD='A01009')DEST_PCD -- 目的地 A.DEST_PCD, \n"); // sqlBuffer.append(" ,A.GET_OFF_PCD\n"); sqlBuffer .append(" ,(select sm_cfnm from tbz00_commcd where sm_cd= B.PRDNM_CD and lg_cd='A01004' ) PRODNM_CD --B.PRDNM_CD ,\n"); sqlBuffer.append(" ,A.ORD_DEVLMT_DATE DEVLMT_DTIME,\n"); sqlBuffer .append(" (select sm_cfnm from tbz00_commcd where lg_cd='A01015' and sm_cd= A.EXLV_LINE_CD)EXLV_LINE_CD ,-- A.EXLV_LINE_CD,\n"); sqlBuffer.append(" A.DLIV_TP,\n"); sqlBuffer.append(" CASE\n"); sqlBuffer.append(" WHEN NVL(A.BY_SEA_YN, 'N') = 'Y' THEN\n"); sqlBuffer.append(" 'TRUE'\n"); sqlBuffer.append(" ELSE\n"); sqlBuffer.append(" 'FALSE'\n"); sqlBuffer.append(" END BY_SEA_YN,\n"); sqlBuffer .append(" (select sm_cfnm from tbz00_commcd where sm_cd= B.RCVORD_TP and lg_cd='A01001' ) RCVORD_TP -- 订单接受区分B.RCVORD_TP,\n"); // sqlBuffer.append(" ,(SELECT SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD= // A.ARRIVAL_CD AND LG_CD='A01009')ARRIVAL_CD\n"); sqlBuffer.append(" ,CASE\n"); sqlBuffer .append(" WHEN P.ORD_PROG_CD IN ('A', 'C', 'D', 'E', 'H', 'F') THEN\n"); sqlBuffer.append(" (P.ORD_WGT - P.SHIP_END_WGT) / 1000\n"); sqlBuffer.append(" ELSE\n"); sqlBuffer.append(" 0 \n"); sqlBuffer.append(" END LEV_WGT,\n"); sqlBuffer.append(" nvl(D.PLAN_WGT,0) PLAN_WGT,\n"); sqlBuffer.append(" A.ORD_PLAN_DATA --新增订单预排期 \n"); sqlBuffer.append(" FROM TBA01_ORD_LINE A,\n"); sqlBuffer.append(" TBA01_ORD_COMM B,\n"); sqlBuffer.append(" TBE02_ORD_PRC P,\n"); sqlBuffer .append(" (SELECT ORD_NO FROM TBE02_ORD_PRC WHERE ORD_NO NOT LIKE 'TSH%'\n"); sqlBuffer .append(" AND ORD_PROG_CD IN ('A', 'C', 'D', 'E', 'H', 'F')\n"); sqlBuffer .append(" GROUP BY ORD_NO) Q, (SELECT ORD_NO,ORD_SEQ, SUM(ACT_WGT / 1000) ACT_WGT, SUM(CASE\n"); sqlBuffer .append(" WHEN CUR_PROG_CD = 'DFB' THEN ACT_WGT END) / 1000 PLAN_WGT FROM C_TBL02_COIL_COMM\n"); sqlBuffer.append(" WHERE SHIP_PROG_CD IS NULL\n"); sqlBuffer.append(" AND DLIV_DIRNO IS NULL\n"); sqlBuffer.append(" AND COIL_STAT = '2'\n"); if (1 != hsTot.intValue()) { sqlBuffer .append(" AND CUR_LOAD_LOC IS NOT NULL AND CUR_PROG_CD = 'DFA' AND TOT_DEC_GRD = '1'\n"); } sqlBuffer.append(" GROUP BY ORD_NO, ORD_SEQ) C,\n"); sqlBuffer .append(" (SELECT ORD_NO, ORD_SEQ, SUM(ACT_WGT / 1000) PLAN_WGT\n"); sqlBuffer.append(" FROM C_TBC02_COIL_COMM\n"); sqlBuffer.append(" WHERE CUR_PROG_CD = 'DFB'\n"); sqlBuffer.append(" GROUP BY ORD_NO, ORD_SEQ) D\n"); sqlBuffer.append(" WHERE A.ORD_NO = Q.ORD_NO\n"); sqlBuffer.append(" AND B.ORD_NO = Q.ORD_NO\n"); sqlBuffer.append(" AND A.ORD_NO = P.ORD_NO\n"); sqlBuffer.append(" AND A.ORD_SEQ = P.ORD_SEQ\n"); sqlBuffer.append(" AND A.ORD_NO = D.ORD_NO(+)\n"); sqlBuffer.append(" AND A.ORD_SEQ = D.ORD_SEQ(+)\n"); sqlBuffer.append(" AND A.ORD_NO = C.ORD_NO(+)\n"); sqlBuffer.append(" AND A.ORD_SEQ = C.ORD_SEQ(+)\n"); sqlBuffer.append(" AND B.ORD_NO NOT LIKE 'TS%'\n"); sqlBuffer.append(" and C.ACT_WGT > 0 \n"); sqlBuffer.append(" AND B.RCVORD_TP NOT IN ('T') --测试订单\n"); sqlBuffer.append(" AND A.DLIV_TP LIKE ?||'%'\n"); sqlBuffer.append(" AND A.ORD_NO||'&' LIKE ?||'%'\n"); sqlBuffer.append(" AND A.SPEC_STL_GRD||'&' LIKE ?||'%'\n"); sqlBuffer.append(" ORDER BY A.ORD_NO, A.ORD_SEQ\n"); cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(), new Object[] { dlivTp, ordNo, specStlGrd });// .ExcuteQuery(sql); return cro; } public CoreReturnObject queryCoilResultByOrd(String ordNo, String ordSeq) throws SQLException { CoreReturnObject cro = new CoreReturnObject(); StringBuffer sqlBuffer = new StringBuffer(); sqlBuffer.append(" SELECT C.ORD_NO||c.ord_seq ORD_NO \n"); // sqlBuffer.append(" , C.COIL_NO\n"); sqlBuffer.append(" , DECODE(C.TOT_DEC_GRD,'1','合格')TOT_DEC_GRD\n"); sqlBuffer.append(" , TO_CHAR(F.ORD_THK||'*'||F.ORD_WTH) COIL_THK \n"); sqlBuffer.append(" , TO_CHAR(C.COIL_WTH) COIL_WTH \n"); sqlBuffer.append(" , TO_CHAR(C.COIL_LEN) COIL_LTH\n"); sqlBuffer.append(" , C.ACT_WGT\n"); // sqlBuffer.append(" , D.DEPARTURE_CD\n"); // sqlBuffer.append(" , F.ARRIVAL_CD\n"); sqlBuffer.append(" , C.CUR_LOAD_LOC\n"); // sqlBuffer.append(" , E.SHIP_DIRBY_CD\n"); // sqlBuffer.append(" , E.SHIP_DIR_DATE\n"); // sqlBuffer.append(" , E.SHIP_DIRNO \n"); // sqlBuffer.append(" , H.CUST_CD\n"); // sqlBuffer.append(" , H.ORDCUST_CD\n"); // sqlBuffer.append(" , CASE WHEN NVL(F.BY_SEA_YN,'N')='Y' THEN 'TRUE' // ELSE 'FALSE' END BY_SEA_YN \n"); // sqlBuffer.append(" , D.TRAIN_DLIVNO \n"); sqlBuffer.append(" , C.OLD_SAMPL_NO \n"); sqlBuffer.append(" , C.SHIP_PROG_CD \n"); sqlBuffer.append(" FROM ( SELECT * FROM C_TBC02_COIL_COMM \n"); sqlBuffer.append(" WHERE COIL_STAT='2' \n"); sqlBuffer.append(" AND ORD_NO= ?\n"); sqlBuffer.append(" AND ORD_SEQ= ? \n"); sqlBuffer .append(" AND (SHIP_PROG_CD IS NULL OR SHIP_PROG_CD ='01' OR SHIP_PROG_CD='02' OR SHIP_PROG_CD='03')\n"); sqlBuffer.append(" ) C\n"); sqlBuffer.append(" ,TBJ01_DLIV_DIR D\n"); sqlBuffer.append(" ,TBJ01_SHIP_DIR E \n"); sqlBuffer.append(" ,TBA01_ORD_LINE F \n"); sqlBuffer.append(" ,TBA01_ORD_COMM H\n"); sqlBuffer.append(" WHERE C.SHIP_DIRNO=E.SHIP_DIRNO(+) \n"); sqlBuffer.append(" AND C.DLIV_DIRNO=D.DLIV_DIRNO(+) \n"); sqlBuffer.append(" AND C.ORD_NO=F.ORD_NO(+) \n"); sqlBuffer.append(" AND C.ORD_NO=H.ORD_NO(+) \n"); sqlBuffer.append(" AND C.ORD_SEQ=F.ORD_SEQ(+)\n"); cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(), new Object[] { ordNo, ordSeq });// .ExcuteQuery(sql); return cro; } }