package UIJ.UIJ04;

import java.sql.SQLException;

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

/**
 * 发运计划查询
 * 
 * @author siy
 * @date 2010-9-6
 */
public class UIJ040010 extends CoreIComponent {

	/**
	 * 查询发运计划
	 * 
	 * @param dlivDirDate
	 * @param dlivTp
	 * @param ordNo
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryShippingPlan(String dlivDirDate,
			String dlivTp, String ordNo) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();

		sqlBuffer.append("SELECT '' CHK,\n");
		sqlBuffer.append("(SELECT TBZ00_COMMCD.CD_DESC\n");
		sqlBuffer.append(" FROM TBZ00_COMMCD\n");
		sqlBuffer.append(" WHERE TBZ00_COMMCD.SM_CD = A.DEST_PCD\n");
		sqlBuffer.append(" AND ROWNUM = 1) YWD,  --运往地\n");
		sqlBuffer.append(" A.DLIV_DIR_DATE, \n");
		sqlBuffer
				.append(" SUBSTR(A.ORDERNO, 1, LENGTH(A.ORDERNO) - 3) PACTNO, --合同号\n");
		sqlBuffer.append(" ORDERNO, --计划号\n");
		sqlBuffer.append(" A.ISOUT,  --是否出口\n");
		sqlBuffer.append(" A.DLIV_DIR_DATE PLANDATE, --计划时间\n");
		sqlBuffer.append(" '热轧线' PL,  --产线\n");
		sqlBuffer.append(" B.SHIP_DIR_DTIME REALSENDTIME, --实际发货时间\n");
		sqlBuffer.append(" B.TRANS_CAR_NO,  --车牌号\n");
		sqlBuffer.append(" A.DLIV_DIRNO,  --出库指示号\n");

		sqlBuffer.append(" (SELECT CUST_NM\n");
		sqlBuffer.append(" FROM TBZ00_CUSTOMER\n");
		sqlBuffer.append(" WHERE CUST_CD = A.CUST_CD\n");
		sqlBuffer.append(" AND ROWNUM = 1) BUYERCODE, --客户名称\n");
		sqlBuffer.append(" '承运' TRANSTYPE,   --合同性质\n");
		sqlBuffer.append(" (SELECT CUST_NM\n");
		sqlBuffer.append(" FROM TBZ00_CUSTOMER\n");
		sqlBuffer.append(" WHERE CUST_CD = ORD_CUST_CD\n");
		sqlBuffer.append(" AND ROWNUM = 1) INCEPTCORPCODE, --收货单位,\n");

		sqlBuffer
				.append(" (SELECT SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD = A.CATEGORY)STL_GRD, --钢种,\n");
		sqlBuffer.append(" B.SPEC_STL_GRD, --牌号,\n");
		sqlBuffer.append(" B.ACT_WGT,--重量\n");
		sqlBuffer.append(" '热轧厂' LOADPOSITION,--装车点\n");
		sqlBuffer.append(" B.INSTR_COIL_THK,    --订单厚度\n");
		sqlBuffer.append(" B.INSTR_COIL_WTH,    --订单宽度\n");
		sqlBuffer.append(" B.CNT,               -- 总件数\n");
		sqlBuffer.append(" B.CUR_LOAD_LOC,      --垛位信息\n");
		sqlBuffer.append(" (SELECT TBZ00_COMMCD.SM_CFNM\n");
		sqlBuffer.append(" FROM TBZ00_COMMCD\n");
		sqlBuffer.append(" WHERE TBZ00_COMMCD.SM_CD = A.TRAIN_DLIVNO\n");
		sqlBuffer.append(" AND ROWNUM = 1) MOTORMANNAME,--承运单位\n");

		sqlBuffer.append(" (SELECT TBZ00_COMMCD.SM_CFNM\n");
		sqlBuffer.append(" FROM TBZ00_COMMCD\n");
		sqlBuffer.append(" WHERE TBZ00_COMMCD.SM_CD = A.DEST_PCD\n");
		sqlBuffer.append(" AND ROWNUM = 1) STATIONCODE, --到站\n");

		sqlBuffer.append(" A.BY_SEA_YN,  --是否水运\n");
		sqlBuffer
				.append("  --这里通过状态标志位来区分是否火运批车（01为下车02为待批车03为已批车）。这里汽运无批车情况\n");
		sqlBuffer
				.append(" DECODE(B.SHIP_PROG_CD,'01','销售下车','02','待批车','03','已批车/排车','04','待装车','05','行车作业结束','06','发货待机','07','发货结束','08','明细发行结束','09','板加移送') SHIP_PROG_CD\n");

		sqlBuffer.append(" FROM TBJ01_DLIV_DIR A,\n");
		sqlBuffer.append(" (SELECT MIN(TRANS_CAR_NO) TRANS_CAR_NO,\n");
		sqlBuffer.append(" MIN(SPEC_STL_GRD) SPEC_STL_GRD,\n");
		sqlBuffer.append(" SUM(ACT_WGT) ACT_WGT,\n");
		sqlBuffer.append(" DLIV_DIRNO,\n");
		sqlBuffer.append(" MIN(SHIP_DIR_DTIME) SHIP_DIR_DTIME,\n");
		sqlBuffer.append(" MIN(INSTR_COIL_THK) INSTR_COIL_THK,\n");
		sqlBuffer.append(" MIN(INSTR_COIL_WTH) INSTR_COIL_WTH,\n");
		sqlBuffer.append(" COUNT(OLD_SAMPL_NO) CNT,\n");
		sqlBuffer.append(" MIN(SHIP_PROG_CD) SHIP_PROG_CD,\n");
		sqlBuffer.append(" MIN(CUR_LOAD_LOC) CUR_LOAD_LOC,\n");
		sqlBuffer.append(" MIN(ORD_NO) ORD_NO\n");
		sqlBuffer.append("  FROM C_TBL02_COIL_COMM\n");
		sqlBuffer.append(" GROUP BY DLIV_DIRNO UNION \n");
		sqlBuffer.append(" SELECT MIN(TRANS_CAR_NO) TRANS_CAR_NO,\n");
		sqlBuffer.append(" MIN(SPEC_STL_GRD) SPEC_STL_GRD,\n");
		sqlBuffer.append(" SUM(ACT_WGT) ACT_WGT,\n");
		sqlBuffer.append(" DLIV_DIRNO,\n");
		sqlBuffer.append(" MIN(SHIP_DIR_DTIME) SHIP_DIR_DTIME,\n");
		sqlBuffer.append(" MIN(INSTR_COIL_THK) INSTR_COIL_THK,\n");
		sqlBuffer.append(" MIN(INSTR_COIL_WTH) INSTR_COIL_WTH,\n");
		sqlBuffer.append(" COUNT(OLD_SAMPL_NO) CNT,\n");
		sqlBuffer.append(" MIN(SHIP_PROG_CD) SHIP_PROG_CD,\n");
		sqlBuffer.append(" MIN(CUR_LOAD_LOC) CUR_LOAD_LOC,\n");
		sqlBuffer.append(" MIN(ORD_NO) ORD_NO\n");
		sqlBuffer.append(" FROM C_TBM02_COIL_COMM\n");
		sqlBuffer.append(" GROUP BY DLIV_DIRNO) B\n");
		sqlBuffer.append(" WHERE A.DLIV_DIRNO = B.DLIV_DIRNO\n");
		sqlBuffer.append(" AND A.DLIV_DIR_DATE = ?  --做主要查询条件\n");
		sqlBuffer.append(" AND A.DLIV_TP = ?     --  'T' 火车   'C' 汽车\n");
		sqlBuffer.append(" AND B.ORD_NO LIKE ?||'%'\n");
		sqlBuffer.append(" AND A.ISDELETED <> '1'\n");
		sqlBuffer.append(" ORDER BY ACT_WGT\n");

		cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(),
				new Object[] { dlivDirDate, dlivTp, ordNo });

		return cro;
	}

	/**
	 * 查询发运计划对应的钢卷
	 * 
	 * @param dlivDirNo
	 * @return
	 */
	public CoreReturnObject queryShippingCoil(String dlivDirNo) {
		CoreReturnObject cro = new CoreReturnObject();

		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer
				.append("SELECT    '' CHK,A.C_COIL_NO,A.SPEC_STL_GRD,A.COIL_THK||'*'||A.COIL_WTH T_W,A.INSTR_COIL_THK||'*'||A.INSTR_COIL_WTH IT_IW,\n");
		sqlBuffer
				.append(" DECODE(NVL(A.ACT_WGT,0),0,A.CAL_WGT,A.ACT_WGT) AS ACT_WGT,A.ORD_NO,A.ORD_SEQ,A.CUR_LOAD_LOC,\n");
		sqlBuffer
				.append(" DECODE(A.TOT_DEC_GRD,'1','合格','2','不合格','待判定') as TOT_DEC_GRD,\n");
		sqlBuffer
				.append(" DECODE(A.CUR_PROG_CD,'SED','判定待机','SRC','充当待机','SFA','发货待机','SFB','运送待机','SFF','发货完成',null) CUR_PROG_CD,\n");
		sqlBuffer.append(" D.CHARGE_NO,\n");
		sqlBuffer.append(" B.CAS_END_DTIME,\n");
		sqlBuffer.append(" A.MILL_DTIME,\n");
		sqlBuffer
				.append(" (SELECT sm_cfnm   FROM TBZ00_COMMCD      WHERE LG_CD = 'A01009'     AND C.DEST_PCD = SM_CD) DEST_PCD_DESC,\n");
		sqlBuffer
				.append(" (select CUST_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = C.CUST_CD and REC_TP='01') CUST_NM, \n");
		sqlBuffer
				.append(" (select CUST_NM ORD_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = C.ORD_CUST_CD and REC_TP='02') ORD_NM,\n");
		sqlBuffer.append(" A.HCOLOUR_REM,\n");
		sqlBuffer.append(" A.SPEC_ABBSYM,\n");
		sqlBuffer.append(" A.DLIV_DIRNO ,\n");
		sqlBuffer
				.append(" XB_PACKAGE.GF_COMNNAME_FIND('A01004' , A.PRODNM_CD )    PRODNM_CD,\n");
		sqlBuffer.append(" C.DLIV_TP\n");
		sqlBuffer
				.append(" FROM C_TBL02_COIL_COMM A,TBG02_CHARGE_COMM b,TBJ01_DLIV_DIR C,TBG02_CHARGE_COMM_D D \n");
		sqlBuffer.append(" WHERE substr(A.SLAB_NO,0,10) = D.CHARGE_NO \n");
		sqlBuffer.append(" AND A.DLIV_DIRNO = C.DLIV_DIRNO\n");
		sqlBuffer
				.append(" AND substr(B.CHARGE_NO,0,9) = SUBSTR(D.CHARGE_NO,0,9)\n");
		sqlBuffer.append(" AND A.DLIV_DIRNO = ?\n");
		sqlBuffer.append(" UNION \n");
		sqlBuffer
				.append(" SELECT    '' CHK,A.OLD_SAMPL_NO,A.SPEC_STL_GRD,A.COIL_THK||'*'||A.COIL_WTH T_W,A.INSTR_COIL_THK||'*'||A.INSTR_COIL_WTH IT_IW,\n");
		sqlBuffer
				.append(" DECODE(NVL(A.ACT_WGT,0),0,A.CAL_WGT,A.ACT_WGT) AS ACT_WGT,A.ORD_NO,A.ORD_SEQ,A.CUR_LOAD_LOC,\n");
		sqlBuffer
				.append(" DECODE(A.TOT_DEC_GRD,'1','合格','2','不合格','待判定') as TOT_DEC_GRD,\n");
		sqlBuffer
				.append(" DECODE(A.CUR_PROG_CD,'SED','判定待机','SRC','充当待机','SFA','发货待机','SFB','运送待机','SFF','发货完成',null) CUR_PROG_CD,\n");
		sqlBuffer.append(" D.CHARGE_NO,\n");
		sqlBuffer.append(" B.CAS_END_DTIME,\n");
		sqlBuffer.append(" A.MILL_DTIME,\n");
		sqlBuffer
				.append(" (SELECT sm_cfnm   FROM TBZ00_COMMCD      WHERE LG_CD = 'A01009'     AND C.DEST_PCD = SM_CD) DEST_PCD_DESC,\n");
		sqlBuffer
				.append(" (select CUST_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = C.CUST_CD and REC_TP='01') CUST_NM, \n");
		sqlBuffer
				.append(" (select CUST_NM ORD_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = C.ORD_CUST_CD and REC_TP='02') ORD_NM,\n");
		sqlBuffer.append(" A.HCOLOUR_REM,\n");
		sqlBuffer.append(" A.SPEC_ABBSYM,\n");
		sqlBuffer.append(" A.DLIV_DIRNO ,\n");
		sqlBuffer
				.append(" XB_PACKAGE.GF_COMNNAME_FIND('A01004' , A.PRODNM_CD )    PRODNM_CD,\n");
		sqlBuffer.append(" C.DLIV_TP\n");
		sqlBuffer
				.append(" FROM C_TBM02_COIL_COMM A,TBG02_CHARGE_COMM b,TBJ01_DLIV_DIR C,TBG02_CHARGE_COMM_D D \n");
		sqlBuffer.append(" WHERE substr(A.SLAB_NO,0,10) = D.CHARGE_NO \n");
		sqlBuffer.append(" AND A.DLIV_DIRNO = C.DLIV_DIRNO\n");
		sqlBuffer
				.append(" AND substr(B.CHARGE_NO,0,9) = SUBSTR(D.CHARGE_NO,0,9)\n");
		sqlBuffer.append(" AND A.DLIV_DIRNO = ? \n");

		cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(),
				new Object[] { dlivDirNo, dlivDirNo });
		
		return cro;
	}
}