XinYu_LZMES/.svn/pristine/1f/1f8b06444b35cde340d2b2b5c1c86e32fd02a1ae.svn-base

package UIM;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;

/**
 * 可发量查询
 * 
 * @author siy
 * @date 2010-09-26
 */
public class UIM010270 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(" ,C_PKG_UIM.GET_STDDATESTR(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(" '是'\n");
		sqlBuffer.append(" ELSE\n");
		sqlBuffer.append(" '否'\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(" C_PKG_UIM.GET_STDDATESTR(A.ORD_PLAN_DATA) 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_TBC02_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;
	}
}