package UIJ.UIJ04;

import java.sql.SQLException;
import java.util.ArrayList;

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

/**
 * 发运计划回退
 * 
 * @author siy
 * @date 2010-9-13
 */
public class UIJ040040 extends CoreIComponent {

	/**
	 * 查询发运计划信息
	 * 
	 * @param fromDate
	 * @param toDate
	 * @param dlivTp
	 * @param dlivDirNo
	 * @param isCancel
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryDlivPlan(String fromDate, String toDate,
			String dlivTp, String dlivDirNo, String isCancel)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("SELECT '' CHK,\n");
		sqlBuffer.append(" B.SHIP_DIRNO,\n");
		sqlBuffer.append("  B.GET_ON_PCD,\n");
		sqlBuffer.append(" B.DEST_PCD,\n");
		sqlBuffer
				.append(" (SELECT sm_cfnm   FROM TBZ00_COMMCD      WHERE LG_CD = 'A01009'     AND b.DEST_PCD = SM_CD) DEST_PCD_DESC,\n");
		sqlBuffer.append(" B.EXLV_LINE_CD,\n");
		sqlBuffer
				.append(" (SELECT x.SM_CFNM   FROM TBZ00_COMMCD   x   WHERE LG_CD = 'A01015'     AND b.EXLV_LINE_CD = SM_CD) EXLV_LINE_CD_DESC,\n");
		sqlBuffer
				.append(" (select CUST_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = b.CUST_CD and REC_TP='01') CUST_NM, \n");
		sqlBuffer
				.append(" (select CUST_NM ORD_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = b.ORD_CUST_CD and REC_TP='02') ORD_NM,\n");
		sqlBuffer.append(" B.GET_OFF_PCD,\n");
		// sqlBuffer.append(" B.DLIV_TP,\n");
		sqlBuffer.append(" D.SM_CFNM AS DLIV_TP_NM,\n");
		// sqlBuffer.append(" B.SHIP_COMP_CD,\n");
		sqlBuffer.append(" C.SHIP_COMP_NM SM_CFNM,\n");
		sqlBuffer.append(" B.TRAIN_DLIVNO,\n");
		sqlBuffer.append(" B.DLIV_DIRNO,\n");
		sqlBuffer.append(" NVL(B.CNT,0) AS CNT,\n");
		sqlBuffer.append(" NVL(B.ACT_WGT,0) AS ACT_WGT,\n");
		sqlBuffer.append(" B.ALLOC_SEQ,\n");
		sqlBuffer.append(" B.TRANS_CAR_NO,\n");
		sqlBuffer.append(" B.LANE_TP,\n");
		sqlBuffer.append(" B.ENTERANCE_SEQ,\n");
		sqlBuffer.append(" B.ORD_NO,\n");
		sqlBuffer.append(" B.ORD_SEQ,\n");
		sqlBuffer.append(" B.SPEC_STL_GRD,\n");
		sqlBuffer.append(" B.INSTR_COIL_THK||'*'||B.INSTR_COIL_WTH T_W,\n");
		sqlBuffer.append(" ISCANCEL   \n");
		sqlBuffer.append(" FROM(SELECT *\n");
		sqlBuffer.append(" FROM TBJ01_SHIP_DIR\n");
		sqlBuffer.append(" WHERE 1 = 1\n");
		sqlBuffer
				.append(" AND SHIP_PROG_CD = '03'           -- SHIP_PROG_CD = '03'\n");
		sqlBuffer
				.append(" AND SHIP_DIRNO BETWEEN NVL(?,'00000101')||'0000' AND NVL(?,'99991231')||'9999'\n");
		sqlBuffer.append(" AND SHIP_DIRNO LIKE ''||'%') A       \n");
		sqlBuffer
				.append(" ,(SELECT X.*, Y.CNT CNT, Y.ACT_WGT ACT_WGT,Y.ORD_NO,Y.ORD_SEQ,Y.SPEC_STL_GRD,Y.INSTR_COIL_THK,Y.INSTR_COIL_WTH \n");
		sqlBuffer.append(" FROM TBJ01_DLIV_DIR X\n");
		sqlBuffer.append(" ,(SELECT DLIV_DIRNO\n");
		sqlBuffer.append(" , COUNT(*)            AS CNT\n");
		sqlBuffer.append(" , NVL(SUM(ACT_WGT),0) AS ACT_WGT\n");
		sqlBuffer.append(" , MAX(ORD_NO)         AS ORD_NO\n");
		sqlBuffer.append(" , MAX(ORD_SEQ)        AS ORD_SEQ\n");
		sqlBuffer.append(" , MAX(SPEC_STL_GRD)   AS SPEC_STL_GRD\n");
		sqlBuffer.append(" , MAX(INSTR_COIL_THK) AS INSTR_COIL_THK\n");
		sqlBuffer.append(" , MAX(INSTR_COIL_WTH) AS INSTR_COIL_WTH\n");
		sqlBuffer.append(" FROM TBH02_COIL_COMM\n");
		sqlBuffer.append(" WHERE 1 = 1\n");
		sqlBuffer
				.append(" AND CUR_PROG_CD like 'SFB'||'%'            -- CUR_PROG_CD = 'SFB'\n");
		sqlBuffer
				.append(" AND SHIP_DIRNO BETWEEN NVL(?,'00000101')||'0000' AND NVL(?,'99991231')||'9999'\n");
		sqlBuffer.append(" GROUP BY DLIV_DIRNO) Y\n");
		sqlBuffer.append(" WHERE X.DLIV_DIRNO = Y.DLIV_DIRNO\n");
		sqlBuffer.append(" AND X.DLIV_TP LIKE ?||'%'\n");
		sqlBuffer.append(" AND NVL(X.LANE_TP,'*') LIKE ''||'%'\n");
		sqlBuffer.append(" AND NVL(X.ENTERANCE_SEQ,0) = NVL('',0)) B\n");
		sqlBuffer.append(" ,(SELECT *\n");
		sqlBuffer.append(" FROM TBJ00_SHIP_COMP\n");
		sqlBuffer.append(" WHERE SHIP_COMP_CD LIKE ''||'%') C\n");
		sqlBuffer
				.append(" ,(SELECT SM_CD, SM_CFNM FROM TBZ00_COMMCD WHERE LG_CD = 'A01012' AND SM_CD LIKE ?||'%') D\n");
		sqlBuffer.append(" WHERE A.SHIP_DIRNO = B.SHIP_DIRNO(+)\n");
		sqlBuffer.append(" AND B.SHIP_COMP_CD = C.SHIP_COMP_CD(+)\n");
		sqlBuffer.append(" AND D.SM_CD = B.DLIV_TP \n");
		sqlBuffer.append(" AND B.ISDELETED = 0\n");
		sqlBuffer.append(" AND B.ISCANCEL = ?\n");
		sqlBuffer.append(" AND B.DLIV_DIRNO LIKE ?||'%'\n");
		sqlBuffer.append(" ORDER BY B.ALLOC_SEQ,B.DLIV_DIRNO \n");
		cro = this.getDao("KgDao").ExcuteQuery(
				sqlBuffer.toString(),
				new Object[] { fromDate, toDate, fromDate, toDate, dlivTp,
						dlivTp, isCancel, dlivDirNo });

		return cro;
	}

	/**
	 * 发运计划回退
	 * 
	 * @param regId
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject returnShipDir(String regId, ArrayList<String> params)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("update TBJ01_DLIV_DIR a\n");
		sqlBuffer.append(" set   A.RETURN_GED   = ?,\n");
		sqlBuffer
				.append("A.RETURN_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),\n");
		sqlBuffer.append(" A.ISCANCEL     = '1',\n");
		sqlBuffer.append(" A.DLIV_PROG_CD = '02' \n");
		sqlBuffer.append(" WHERE A.DLIV_DIRNO   = ? \n");

		for (int i = 0; i < params.size(); i++) {
			String dlivDirNo = params.get(i);
			this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
					new Object[] { regId, dlivDirNo });
		}

		return cro;
	}
}