| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247 |
- package UIE.UIE01;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import CoreFS.SA01.CoreIComponent;
- import CoreFS.SA06.CoreReturnObject;
- public class UIE042010 extends CoreIComponent {
- public CoreReturnObject query01(String ordNO,String ordSeq,String productNM,String ordProc
- ,String abnormalCD,String custNM
- ,String hotStlGrd,String coldStlGrd,String delToDateFrom
- ,String delToDateTo,String OrdType,String planDateFrom
- ,String planDateTo,String transfType,String ordWthFrom,String ordWthTo
- ,String ordThkFrom,String ordThkTo,String coldOrdWthFrom,String coldOrdWthTo
- ,String coldOrdThkFrom,String coldOrdThkTo,String saleArea) throws SQLException
- {
- CoreReturnObject cro=new CoreReturnObject();
- StringBuffer str = new StringBuffer();
- ArrayList<String> paramArray = new ArrayList<String>();
-
- str.append(" SELECT A.ORD_NO, -- 合同号 \n");
- str.append(" A.ORD_SEQ, -- 订单号 \n");
- str.append(" XB_PACKAGE.GF_COMNNAME_FIND('E01001', A.ORD_PROG_CD) ORD_PROG_CD, -- 订单进度代码 \n");
- str.append(" XB_PACKAGE.GF_CUSTOMER_FIND(A.CUST_CD) CUST_CD, -- 客户代码 \n");
- str.append(" XB_PACKAGE.GF_COMNNAME_FIND('E01003', A.ABNOR_ORD_CD) ABNOR_ORD_CD, -- 异常订单类型代码 \n");
- str.append(" XB_PACKAGE.GF_COMNNAME_FIND('E01005', A.PROD_HOLD_CD) PROD_HOLD_CD, -- 生产保留区分代码 \n");
- str.append(" XB_PACKAGE.GF_COMNNAME_FIND('A01001', A.RCVORD_CLF) RCVORD_CLF, -- 订单用途 \n");
- str.append(" XB_PACKAGE.GF_COMNNAME_FIND('B01004', A.PROD_LINE) PROD_LINE, -- 产线 \n");
- str.append(" XB_PACKAGE.GF_COMNNAME_FIND('A01004', A.PRODNM_CD) PRODNM_CD, -- 交货状态 \n");
- str.append(" XB_PACKAGE.GF_COMNNAME_FIND('B01118', B.TRTMTH_TP) TRTMTH_TP, -- 产品代码 \n");
- str.append(" A.STL_GRD, -- 厂内牌号 \n");
- str.append(" A.H_SPEC_STL_GRD, \n");
- str.append(" A.C_SPEC_STL_GRD, --冷轧牌号 \n");
- str.append(" A.SPEC_ABBSYM, -- 热轧标准号 \n");
- str.append(" A.C_SPEC_ABBSYM, -- 冷轧标准号 \n");
- str.append(" XB_PACKAGE.GF_COMNNAME_FIND('A01004', A.ORD_USEAGE_CD) ORD_USEAGE_CD, -- 订单用途 \n");
- str.append(" NVL(A.KEEPWARM_YN, 'N') KEEPWARM_YN, -- 是否需要进保温坑 \n");
- str.append(" NVL(A.EACHLOT_YN, 'N') EACHLOT_YN, -- 是否需要全部取样 \n");
- str.append(" A.ORD_THK, -- 订单厚度 \n");
- str.append(" A.ORD_WTH, -- 订单宽度 \n");
- str.append(" A.ORD_LEN, -- 订单长度 \n");
- str.append(" A.ORD_UNIT_WGT, -- 订单单重量 \n");
- str.append(" A.ORD_UNIT_WGT_MIN, -- 订单单重量下限 \n");
- str.append(" A.ORD_UNIT_WGT_MAX, -- 订单单重量上限 \n");
- str.append(" NVL(A.EMGMTR_NMG_CLF, 'N') EMGMTR_NMG_CLF, -- 紧急材代码 \n");
- str.append(" B.ORD_PLAN_DATA, --订单预排期 \n");
- str.append(" A.SMS_DUE_DATE, --炼钢完成期限 \n");
- str.append(" A.MILL_DUE_DATE, --热轧完成期限 \n");
- str.append(" A.PLTCM_DUE_DATE, --酸轧完成期限 \n");
- str.append(" A.CAL_DUE_DATE, --连退完成期限 \n");
- str.append(" A.DEL_TO_DATE, -- 交货日期（到达） \n");
- str.append(" A.ORD_WGT, -- 订单重量 \n");
- str.append(" A.WGT_ACVAL_MIN, -- 交付允许误差最小 \n");
- str.append(" A.WGT_ACVAL_MAX, -- 交付允许误差最大 \n");
- str.append(" PKG_QUALITY_COMM.FID070010(A.ORD_NO, A.ORD_SEQ) SUM_WGT, \n");
- str.append(" DECODE(A.ORD_PROG_CD, 'G', 0, A.ORD_REM_WGT) ORD_REM_WGT, -- 订单未生产量 \n");
- str.append(" (SELECT SUM(TOT_WGT) \n");
- str.append(" FROM TBE02_ORD_PRC_DET \n");
- str.append(" WHERE ORD_NO = A.ORD_NO \n");
- str.append(" AND ORD_SEQ = A.ORD_SEQ \n");
- str.append(" AND PROC_CD IN ('JB', 'JR', 'JC', 'JL')) LG, -- 炼钢 \n");
- str.append(" (SELECT SUM(TOT_WGT) \n");
- str.append(" FROM TBE02_ORD_PRC_DET \n");
- str.append(" WHERE ORD_NO = A.ORD_NO \n");
- str.append(" AND ORD_SEQ = A.ORD_SEQ \n");
- str.append(" AND PROC_CD = 'JJ') LZ, -- 连铸 \n");
- str.append(" (SELECT SUM(TOT_WGT) \n");
- str.append(" FROM TBE02_ORD_PRC_DET \n");
- str.append(" WHERE ORD_NO = A.ORD_NO \n");
- str.append(" AND ORD_SEQ = A.ORD_SEQ \n");
- str.append(" AND PROC_CD IN ('RA', 'RB', 'RC')) ZG, -- 轧钢 \n");
- str.append(" (SELECT SUM(TOT_WGT) \n");
- str.append(" FROM TBE02_ORD_PRC_DET \n");
- str.append(" WHERE ORD_NO = A.ORD_NO \n");
- str.append(" AND ORD_SEQ = A.ORD_SEQ \n");
- str.append(" AND PROC_CD = 'SC') JZ, -- 精整 \n");
- str.append(" (SELECT SUM(TOT_WGT) \n");
- str.append(" FROM TBE02_ORD_PRC_DET \n");
- str.append(" WHERE ORD_NO = A.ORD_NO \n");
- str.append(" AND ORD_SEQ = A.ORD_SEQ \n");
- str.append(" AND PROC_CD = 'LC') SZ, -- 酸轧 \n");
- str.append(" (SELECT SUM(TOT_WGT) \n");
- str.append(" FROM TBE02_ORD_PRC_DET \n");
- str.append(" WHERE ORD_NO = A.ORD_NO \n");
- str.append(" AND ORD_SEQ = A.ORD_SEQ \n");
- str.append(" AND PROC_CD = 'LD') LT, -- 连退 \n");
- str.append(" (SELECT SUM(TOT_WGT) \n");
- str.append(" FROM TBE02_ORD_PRC_DET \n");
- str.append(" WHERE ORD_NO = A.ORD_NO \n");
- str.append(" AND ORD_SEQ = A.ORD_SEQ \n");
- str.append(" AND PROC_CD = 'LE') CJ, -- 重卷 \n");
- str.append(" (SELECT SUM(TOT_WGT) \n");
- str.append(" FROM TBE02_ORD_PRC_DET \n");
- str.append(" WHERE ORD_NO = A.ORD_NO \n");
- str.append(" AND ORD_SEQ = A.ORD_SEQ \n");
- str.append(" AND PROC_CD = 'LF') BZ, -- 包装 \n");
- str.append(" (SELECT SUM(TOT_WGT) \n");
- str.append(" FROM TBE02_ORD_PRC_DET \n");
- str.append(" WHERE ORD_NO = A.ORD_NO \n");
- str.append(" AND ORD_SEQ = A.ORD_SEQ \n");
- str.append(" AND PROC_CD IN ('SB', 'SD', 'SE')) ZH, -- 综合判定 \n");
- str.append(" (SELECT SUM(INS_WGT + WRK_WGT) \n");
- str.append(" FROM TBE02_ORD_PRC_DET \n");
- str.append(" WHERE ORD_NO = A.ORD_NO \n");
- str.append(" AND ORD_SEQ = A.ORD_SEQ \n");
- str.append(" AND PROC_CD = 'SF') DD, -- 发货等待 \n");
- str.append(" A.SHIP_END_WGT, -- 发货完重量 \n");
- str.append(" DECODE(A.ORD_PROG_CD, 'G', 0, (A.ORD_WGT - A.SHIP_END_WGT)) SHIP_REM_WGT -- 剩余发货量 \n");
- str.append(" , \n");
- str.append(" XB_PACKAGE.GF_COMNNAME_FIND('A01012', B.DLIV_TP) DLIV_TP, \n");
- str.append(" DECODE(K.DSN_KIND, '1', 'Y', 'N') AS INGR_YN, \n");
- str.append(" decode(ROUND(A.SHIP_END_WGT / A.ORD_WGT * 100, 2) || '%', \n");
- str.append(" '0%', \n");
- str.append(" '', \n");
- str.append(" ROUND(A.SHIP_END_WGT / A.ORD_WGT * 100, 2) || '%') ORD_RATE --合同兑现率 \n");
- str.append(" , \n");
- str.append(" M.WGT_IN_DEL OVER_DATE_WGT, --超交货期量 \n");
- str.append(" CASE \n");
- str.append(" WHEN B.DLIV_TP = 'T' THEN \n");
- str.append(" DECODE(A.SHIP_END_WGT, 0, '', A.SHIP_END_WGT) \n");
- str.append(" END CALZADA_WGT, --火车交货 \n");
- str.append(" CASE \n");
- str.append(" WHEN B.DLIV_TP = 'C' THEN \n");
- str.append(" DECODE(A.SHIP_END_WGT, 0, '', A.SHIP_END_WGT) \n");
- str.append(" END TRAIN_WGT, --汽车交货 \n");
- str.append(" DECODE(A.ORD_PROG_CD, 'G', A.SHIP_END_DATE, A.PROD_END_DATE) PROD_END_DATE, \n");
- str.append(" B.MK_COIL, \n");
- str.append(" B.XSQY --销售区域 \n");
- str.append(" FROM TBE02_ORD_PRC A, \n");
- str.append(" TBA01_ORD_LINE B -- 订单进程表 \n");
- str.append(" , \n");
- str.append(" TBZ00_CUSTOMER C --客户公共表 \n");
- str.append(" , \n");
- str.append(" (SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND \n");
- str.append(" FROM TBB01_ORD_INGR \n");
- str.append(" WHERE DSN_KIND = '1') K, \n");
- str.append(" (SELECT A.ORD_NO, A.ORD_SEQ, SUM(A.ACT_WGT) WGT_IN_DEL \n");
- str.append(" FROM TBH02_COIL_COMM A, TBE02_ORD_PRC B \n");
- str.append(" WHERE A.ORD_NO = B.ORD_NO \n");
- str.append(" AND A.ORD_SEQ = B.ORD_SEQ \n");
- str.append(" AND A.ORD_FL = '1' \n");
- str.append(" AND A.TRNF_DTIME > B.DEL_TO_DATE \n");
- str.append(" GROUP BY (A.ORD_NO, A.ORD_SEQ)) M \n");
- str.append(" WHERE '1' = '1' \n");
- str.append(" AND A.ORD_NO = B.ORD_NO(+) \n");
- str.append(" AND A.ORD_SEQ = B.ORD_SEQ(+) \n");
- str.append(" AND A.ORD_NO = K.ORD_NO(+) \n");
- str.append(" AND A.ORD_SEQ = K.ORD_SEQ(+) \n");
- str.append(" AND A.ORD_NO = M.ORD_NO(+) \n");
- str.append(" AND A.ORD_SEQ = M.ORD_SEQ(+) \n");
- str.append(" AND A.CUST_CD = C.CUST_CD(+) \n");
- str.append(" AND A.ORD_NO LIKE '41%' \n");
-
- if(!ordNO.equals("")){
- str.append(" and A.ORD_NO LIKE :1||'%' -- 参数：合同号 \n");
- paramArray.add(ordNO);
- }
- if(!ordSeq.equals("")){
- str.append(" AND A.ORD_SEQ LIKE :2 ||'%' -- 参数：订单号 \n");
- paramArray.add(ordSeq);
- }
- if(!productNM.equals("")){
- str.append(" AND A.PROD_LINE LIKE :3 || '%' -- 参数：产品代码 \n");
- paramArray.add(productNM);
- }
- if(!ordProc.equals("")){
- str.append(" AND (:4 IS NULL OR A.ORD_PROG_CD IN \n");
- str.append(" (SELECT COLUMN_VALUE FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(:5) AS TABSTR_T)))) \n");
- paramArray.add(ordProc);
- paramArray.add(ordProc);
- }
- if(!abnormalCD.equals("")){
- str.append(" AND A.ABNOR_ORD_CD || '&' LIKE :6 || '%' -- 参数：异常订单类型代码 \n");
- paramArray.add(abnormalCD);
- }
- if(!custNM.equals("")){
- str.append(" AND C.CUST_NM LIKE '%' || :7 || '%' --参数：客户名称 \n");
- paramArray.add(custNM);
- }
- if(!hotStlGrd.equals("")){
- str.append(" AND A.H_SPEC_STL_GRD || '&' LIKE :8 || '%' -- 参数：钢号 \n");
- paramArray.add(hotStlGrd);
- }
- if(!coldStlGrd.equals("")){
- str.append(" AND A.C_SPEC_STL_GRD || '&' LIKE :9 || '%' -- 参数：钢号 \n");
- paramArray.add(coldStlGrd);
- }
- if(!(delToDateFrom.equals("")||delToDateTo.equals(""))){
- str.append(" AND A.DEL_TO_DATE BETWEEN :10 AND :11 -- 参数：交货日期（到达） \n");
- paramArray.add(delToDateFrom);
- paramArray.add(delToDateTo);
- }
- if(!OrdType.equals("")){
- str.append(" AND (:12 IS NULL OR A.RCVORD_CLF IN \n");
- str.append(" (SELECT COLUMN_VALUE \n");
- str.append(" FROM TABLE(CAST(XB_PACKAGE.GF_TABSTR(:13) AS TABSTR_T)))) \n");
- paramArray.add(OrdType);
- paramArray.add(OrdType);
- }
- if(!(planDateFrom.equals("")||planDateTo.equals(""))){
- str.append(" AND NVL(B.ORD_PLAN_DATA, A.DEL_TO_DATE) BETWEEN NVL(:14, '0000000') AND \n");
- str.append(" NVL(:15, '99999999') \n");
- paramArray.add(planDateFrom);
- paramArray.add(planDateTo);
- }
- if(!transfType.equals("")){
- str.append(" AND B.DLIV_TP || '&' LIKE :16 || '%' --运输方式，查询条件TL 091209 \n");
- paramArray.add(transfType);
- }
- if(!(ordWthFrom.equals("")||ordWthTo.equals(""))){
- str.append(" AND A.ORD_WTH BETWEEN NVL(:17, 0) AND NVL(:18, 99999999) \n");
- paramArray.add(ordWthFrom);
- paramArray.add(ordWthTo);
- }
- if(!(ordThkFrom.equals("")||ordThkTo.equals(""))){
- str.append(" AND A.ORD_THK BETWEEN NVL(:19, 0) AND NVL(:20, 99999999) \n");
- paramArray.add(ordThkFrom);
- paramArray.add(ordThkTo);
- }
- if(!(coldOrdWthFrom.equals("")||coldOrdWthTo.equals(""))){
- str.append(" AND A.C_ORD_WTH BETWEEN NVL(:21, 0) AND NVL(:22, 99999999) \n");
- paramArray.add(coldOrdWthFrom);
- paramArray.add(coldOrdWthTo);
- }
- if(!(coldOrdThkFrom.equals("")||coldOrdThkTo.equals(""))){
- str.append(" AND A.C_ORD_THK BETWEEN NVL(:23, 0) AND NVL(:24, 99999999) \n");
- paramArray.add(coldOrdThkFrom);
- paramArray.add(coldOrdThkTo);
- }
- if(!saleArea.equals("")){
- str.append(" AND B.XSQY || '&' LIKE :25 || '%' \n");
- paramArray.add(saleArea);
- }
- str.append(" ORDER BY A.ORD_NO, A.ORD_SEQ \n");
- String sql = str.toString();
- System.out.println(sql);
- for(String i:paramArray){
- System.out.println(i);
- }
- cro=this.getDao("KgDao").ExcuteQuery(sql,paramArray.toArray());
-
- System.out.println(cro);
- return cro;
- }
- }
|
