package UIJ.UIJ04;

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

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

/**
 * 发货实绩录入与发行装车明细
 * 
 * @author siy
 * @date 2010-9-10
 */
public class UIJ040030 extends CoreIComponent {

	/**
	 * 查询发货实绩信息
	 * 
	 * @param dlivTp
	 * @param curProgCd
	 * @param transCarNo
	 * @param dlivDirNo
	 * @param shipProgCd
	 * @param tranfDTime
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryShippingResult(String dlivTp,
			String curProgCd, String transCarNo, String dlivDirNo,
			String shipProgCd, String tranfDTime) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();

		sqlBuffer.append("SELECT 'N' AS CHK\n");
		sqlBuffer.append(" , A.SHIP_PROG_CD\n");
		sqlBuffer.append(" , Z.SM_CFNM AS SHIP_PROG_NM\n");
		sqlBuffer.append(" , A.SHIP_DIRNO\n");
		sqlBuffer
				.append(" ,(select CUST_NM FROM TBZ00_CUSTOMER WHERE CUST_CD = (SELECT CUST_CD FROM TBA01_ORD_COMM WHERE ORD_NO = A.ORD_NO) 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(" ,(select SM_CFNM FROM TBZ00_COMMCD WHERE SM_CD = A.ORD_USE_CD and LG_CD = 'A01007') SM_CFNM\n");
		sqlBuffer.append(" , A.ORD_USE_CD\n");
		sqlBuffer.append(" , A.SPEC_ABBSYM\n");
		sqlBuffer.append(" , A.SPEC_STL_GRD\n");
		sqlBuffer.append(" , A.DLIV_DIRNO\n");
		sqlBuffer.append(" , C.DLIV_TP\n");
		sqlBuffer.append(" , C.DLIV_TP_DESC\n");
		sqlBuffer.append(" , C.SHIP_COMP_CD\n");
		sqlBuffer.append(" , C.SHIP_COMP_NM\n");
		sqlBuffer.append(" , C.TRANS_CAR_NO\n");
		sqlBuffer.append(" , C.GET_ON_PCD\n");
		sqlBuffer.append(" , C.DEST_PCD\n");
		sqlBuffer.append(" , C.DEST_PCD_DESC\n");
		sqlBuffer.append(" , C.EXLV_LINE_CD\n");
		sqlBuffer.append(" , C.EXLV_LINE_CD_DESC\n");
		sqlBuffer.append(" , C.GET_OFF_PCD\n");
		sqlBuffer.append(" , C.BY_SEA_YN\n");
		sqlBuffer.append(" , C.ARRIVAL_CD \n");
		sqlBuffer.append(" , C.ARRIVAL_CD_DESC\n");
		sqlBuffer.append(" , A.OLD_SAMPL_NO OLD_SAMPL_NO\n");
		sqlBuffer.append(" , A.COIL_NO  \n");
		sqlBuffer.append(" , A.TOT_DEC_GRD \n");
		sqlBuffer
				.append(" , DECODE(A.TOT_DEC_GRD,'1','合格','2','不合格',null) as  TOT_DEC_GRD_DESC\n");
		sqlBuffer.append(" , NVL(A.ACT_WGT,0) COIL_WGT\n");
		sqlBuffer.append(" , NVL(A.CAL_WGT,0) CAL_WGT\n");
		sqlBuffer.append(" , A.COIL_OUTDIA\n");
		sqlBuffer.append(" , A.SHIP_INVNO\n");
		sqlBuffer.append(" , A.CRET_NO\n");
		sqlBuffer
				.append(" , NVL(TO_CHAR(TO_DATE(A.TRNF_DTIME,'YYYYMMDDHH24MISS'),'YYYY-MM-DD HH24:MI:SS'),'-') AS TRNF_DTIME\n");
		sqlBuffer.append(" ,'3'                   AS COIL_STAT\n");
		sqlBuffer.append(" ,'SFF'                 AS CUR_PROG_CD\n");
		sqlBuffer.append(" ,'UIJ040030'           AS CUR_PROG_CD_PGM\n");
		sqlBuffer.append(" , A.CUR_PROG_CD        AS BEF_PROG_CD\n");
		sqlBuffer.append(" , A.CUR_PROG_CD_DTIME  AS BEF_PROG_CD_DTIME\n");
		sqlBuffer.append(" , A.CUR_PROG_CD_PGM    AS BEF_PROG_CD_PGM\n");
		sqlBuffer.append(" , A.ORD_NO, A.ORD_SEQ\n");
		sqlBuffer.append(" ,A.MATLQLTY_DEC_GRD \n");
		sqlBuffer
				.append(" , DECODE(A.MATLQLTY_DEC_GRD,'1','合格','2','不合格',null) as MATLQLTY_DEC_GRD_DESC\n");
		sqlBuffer.append(" , A.INSTR_COIL_THK||'*'||A.INSTR_COIL_WTH T_W\n");
		sqlBuffer.append(" , TO_CHAR(SYSDATE,'YYYY-MM-DD') SYS_TIME\n");
		sqlBuffer.append(" , A.CZD_WGT\n");
		sqlBuffer
				.append(" , DECODE(A.TRNF_SHIFT,'1','早','2','中','3','晚') TRNF_SHIFT\n");
		sqlBuffer
				.append(" , DECODE(A.TRNF_GROUP,'A','甲','B','乙','C','丙','D','丁') TRNF_GROUP\n");
		sqlBuffer.append(" , A.TRNF_USE_TIME\n");
		sqlBuffer.append(" ,A.HCOLOUR_REM\n");
		sqlBuffer.append(" ,A.SLAB_NO       --板加添加，以下相同\n");
		sqlBuffer
				.append(" ,(SELECT T.DEL_TO_DATE FROM TBE02_ORD_PRC T WHERE T.ORD_NO = A.ORD_NO AND T.ORD_SEQ = A.ORD_SEQ) DEVLMT_DTIME\n");
		sqlBuffer.append(" ,A.INSTR_COIL_THK\n");
		sqlBuffer.append(" ,A.INSTR_COIL_WTH\n");
		sqlBuffer.append(" ,A.INSTR_COIL_LEN\n");
		sqlBuffer.append(" ,A.INSTR_COIL_OUTDIA\n");
		sqlBuffer.append(" ,A.INSTR_COIL_INDIA\n");
		sqlBuffer.append(" ,A.INSTR_COIL_WGT\n");
		sqlBuffer.append(" ,C.SM_CD\n");
		sqlBuffer
				.append(" ,(select CUST_CD ORD_CD FROM TBZ00_CUSTOMER WHERE CUST_CD = C.ORD_CUST_CD and REC_TP='02') ORD_CD \n");
		sqlBuffer.append(" ,A.COIL_LEN\n");
		sqlBuffer.append(" ,A.COIL_INDIA\n");
		sqlBuffer.append(" ,A.COIL_THK\n");
		sqlBuffer.append(" ,A.COIL_WTH\n");
		sqlBuffer.append(" ,A.PRODNM_CD\n");
		sqlBuffer.append(" ,A.INGR_DEC_DTIME       --成份判定\n");
		sqlBuffer.append(" ,A.MATLQLTY_DEC_DTIME   --材质判定\n");
		sqlBuffer.append(" ,A.EXTSHAPE_DEC_DTIME   --外观判定\n");
		sqlBuffer.append(" ,A.TOT_DEC_DTIME        --综合判定时间\n");
		sqlBuffer.append(" ,A.INGR_DEC_GRD\n");
		sqlBuffer.append(" ,A.SIZE_DEC_RST\n");
		sqlBuffer.append(" ,A.EXTSHAPE_DEC_GRD\n");
		sqlBuffer.append(" ,A.WGT_DEC_RST\n");
		sqlBuffer.append(" ,A.MILL_DTIME\n");
		sqlBuffer.append(" ,A.CRK_CD1\n");
		sqlBuffer.append(" ,A.CRK_CD2\n");
		sqlBuffer.append(" ,A.CRK_CD3\n");
		sqlBuffer.append(" ,A.CRK_CD4\n");
		sqlBuffer.append(" ,A.CRK_CD5\n");
		sqlBuffer.append(" FROM TBH02_COIL_COMM A\n");
		sqlBuffer.append(" , TBJ01_SHIP_DIR  B\n");
		sqlBuffer
				.append(" ,(SELECT X.*, Y.SHIP_COMP_NM, Z.SM_CFNM AS DLIV_TP_DESC\n");
		sqlBuffer.append(" , Z1.SM_CFNM AS DEST_PCD_DESC\n");
		sqlBuffer.append(" , Z2.SM_CFNM AS EXLV_LINE_CD_DESC\n");
		sqlBuffer.append(" , Z3.SM_CFNM AS ARRIVAL_CD_DESC\n");
		sqlBuffer.append(" , Z1.SM_CD  --板加 目的地代码\n");
		sqlBuffer.append(" FROM TBJ01_DLIV_DIR X\n");
		sqlBuffer.append(" , TBJ00_SHIP_COMP Y\n");
		sqlBuffer
				.append(" ,(SELECT * FROM TBZ00_COMMCD WHERE LG_CD = 'A01012') Z\n");
		sqlBuffer
				.append(" ,(SELECT * FROM TBZ00_COMMCD WHERE LG_CD = 'A01009') Z1\n");
		sqlBuffer
				.append(" ,(SELECT * FROM TBZ00_COMMCD WHERE LG_CD = 'A01015') Z2 \n");
		sqlBuffer
				.append(" ,(SELECT * FROM TBZ00_COMMCD WHERE LG_CD = 'A01009') Z3\n");
		sqlBuffer.append(" WHERE 1 = 1\n");
		sqlBuffer.append(" AND X.SHIP_COMP_CD = Y.SHIP_COMP_CD(+)\n");
		sqlBuffer.append(" AND X.DLIV_TP = Z.SM_CD(+)\n");
		sqlBuffer.append(" AND X.DEST_PCD = Z1.SM_CD(+)\n");
		sqlBuffer.append(" AND X.EXLV_LINE_CD = Z2.SM_CD(+)\n");
		sqlBuffer.append(" AND X.ARRIVAL_CD = Z3.SM_CD(+)\n");
		sqlBuffer.append(" AND X.DLIV_TP LIKE ?||'%') C\n");
		sqlBuffer
				.append(" ,(SELECT * FROM TBZ00_COMMCD WHERE LG_CD = 'J01005' AND SM_CD IN ('05','06','07','08','09')) Z\n");
		sqlBuffer.append(" WHERE 1 = 1\n");
		sqlBuffer.append(" AND A.SHIP_DIRNO = B.SHIP_DIRNO\n");
		sqlBuffer.append(" AND A.DLIV_DIRNO = C.DLIV_DIRNO\n");
		sqlBuffer.append(" AND A.SHIP_PROG_CD = Z.SM_CD(+)\n");
		sqlBuffer.append(" AND A.SHIP_PROG_CD IN ('06','07','08')\n");
		sqlBuffer.append(" AND A.CUR_PROG_CD LIKE ?||'%'\n");
		sqlBuffer.append(" AND A.TRANS_CAR_NO||'&' LIKE ?||'%'\n");
		sqlBuffer.append(" AND A.DLIV_DIRNO LIKE ?||'%'\n");
		sqlBuffer.append(" AND A.SHIP_PROG_CD LIKE ?||'%'\n");
		sqlBuffer.append(" AND A.TRNF_DTIME||'&' LIKE ?||'%'\n");
		sqlBuffer.append(" ORDER BY A.SHIP_DIRNO, A.DLIV_DIRNO\n");

		cro = this.getDao("KgDao").ExcuteQuery(
				sqlBuffer.toString(),
				new Object[] { dlivTp, curProgCd, transCarNo, dlivDirNo,
						shipProgCd, tranfDTime });

		System.out.println(cro);
		return cro;
	}

	/**
	 * 生成发货实绩
	 * 
	 * @param trnfShift
	 * @param trnfGroup
	 * @param trnfRegId
	 * @param trnfDTime
	 * @param czdWgt
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject saveShippingResult(String trnfShift,
			String trnfGroup, String trnfRegId, String trnfDTime,
			String czdWgt, ArrayList<String[]> params) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();

		// 修改钢卷发运状态
		updateCoilProgStatus(trnfShift, trnfGroup, trnfRegId, trnfDTime, params);
		// 调用进程管理进程整理

		// 修改钢卷表发运状态及发运相关信息 、发运表发运状态
		updateShipProgStatus(czdWgt, params);
		return cro;
	}

	/**
	 * 修改钢卷发运状态
	 * 
	 * @param trnfShift
	 * @param trnfGroup
	 * @param trnfRegId
	 * @param trnfDTime
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private void updateCoilProgStatus(String trnfShift, String trnfGroup,
			String trnfRegId, String trnfDTime, ArrayList<String[]> params)
			throws SQLException {
		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			// 更新钢卷公共表发货状态
			StringBuffer updSql1 = new StringBuffer();
			updSql1.append("UPDATE TBH02_COIL_COMM\n");
			updSql1.append(" SET COIL_STAT         = ?\n");
			updSql1.append(" , CUR_PROG_CD       = ?\n");
			updSql1
					.append(" , CUR_PROG_CD_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')\n");
			updSql1.append(" , CUR_PROG_CD_PGM   = ?\n");
			updSql1.append(" , BEF_PROG_CD       = ?\n");
			updSql1.append(" , BEF_PROG_CD_DTIME = ?\n");
			updSql1.append(" , BEF_PROG_CD_PGM   = ?\n");
			updSql1.append(" , TRANS_CAR_NO      = ?\n");
			updSql1.append(" , TRNF_SHIFT        = ?\n");
			updSql1.append(" , TRNF_GROUP        = ?\n");
			updSql1.append(" , TRNF_REG          = ?\n");
			updSql1.append(" , TRNF_DTIME        = ?\n");
			updSql1.append(" --记录修改前重量\n");
			updSql1.append(" , BEF_ACT_WGT       = ACT_WGT\n");
			updSql1.append(" , ACT_WGT           = ?\n");
			updSql1
					.append(" , TRNF_USE_TIME     = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')\n");
			updSql1.append(" WHERE COIL_NO         = ?\n");

			this.getDao("KgDao").ExcuteNonQuery(
					updSql1.toString(),
					new Object[] { param[25], param[26], param[27], param[19],
							param[20], param[21], param[2], trnfShift,
							trnfGroup, trnfRegId, trnfDTime, param[12],
							param[10] });
			// 更新发运表车辆号
			StringBuffer updSql2 = new StringBuffer();
			updSql2.append("UPDATE tbj01_dliv_dir\n");
			updSql2.append(" SET TRANS_CAR_NO      = ?\n");
			updSql2.append(" WHERE DLIV_DIRNO      = ?\n");
			this.getDao("KgDao").ExcuteNonQuery(updSql2.toString(),
					new Object[] { param[2], param[1] });
		}
	}

	/**
	 * 修改钢卷表发运状态及发运相关信息
	 * 
	 * @param czdWgt
	 * @param params
	 * @throws SQLException
	 */
	private void updateShipProgStatus(String czdWgt, ArrayList<String[]> params)
			throws SQLException {
		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			// 更新钢卷公共表发运状态及发运相关信息
			StringBuffer sqlBuffer = new StringBuffer();
			sqlBuffer.append("UPDATE TBH02_COIL_COMM\n");
			sqlBuffer.append(" SET SHIP_PROG_CD = ?\n");
			sqlBuffer.append(" ,TRANS_CAR_NO = ?\n");
			sqlBuffer.append(" ,ACT_WGT     = ?\n");
			sqlBuffer.append(" ,CZD_WGT     = ?	\n");
			sqlBuffer.append(" WHERE COIL_NO    = ?\n");

			this.getDao("KgDao").ExcuteNonQuery(
					sqlBuffer.toString(),
					new Object[] { param[17], param[2], param[12], czdWgt,
							param[10] });
		}

		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			// 查询钢卷公共表中SHIP_PROG_CD状态,看是否全部改变
			boolean flag = true;
			String sql = "select a.SHIP_PROG_CD from tbh02_coil_comm a  where a.ship_dirno = '"
					+ param[0] + "' ";
			ResultSet rs = this.getDao("KgDao").ExceuteQueryForResultSet(sql);
			while (rs.next()) {
				String shipProgCd = rs.getString("SHIP_PROG_CD");
				if (!"07".equals(shipProgCd) && !"08".equals(shipProgCd)
						&& !"09".equals(shipProgCd)) {
					flag = false;
				}
			}
			// 判断同一发货指示下的钢卷是否全部保存，如果没有的话不能改变发货指示的状态
			if (flag) {
				StringBuffer sqlBuffer = new StringBuffer();
				sqlBuffer.append("UPDATE TBJ01_SHIP_DIR\n");
				sqlBuffer.append(" SET SHIP_PROG_CD = ?\n");
				sqlBuffer.append(" WHERE SHIP_DIRNO = ?\n");
				this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
						new Object[] { param[17], param[0] });
			}
		}
	}

	/**
	 * 打印发货明细后修改相关状态
	 * 
	 * @param trnfShift
	 * @param trnfGroup
	 * @param trnfRegId
	 * @param trnfDTime
	 * @param czdWgt
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject printShippingResult(String trnfShift,
			String trnfGroup, String trnfRegId, String trnfDTime,
			String czdWgt, ArrayList<String[]> params) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		
		updateInvoiceInfoOfDelivery(params);
		
		// 修改钢卷表发运状态及发运相关信息 、发运表发运状态
		updateShipProgStatus(czdWgt, params);
		updateShipProgStatusOfPrint(czdWgt, params);
		return cro;
	}
	
	private void updateInvoiceInfoOfDelivery(ArrayList<String[]> params) throws SQLException{
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("UPDATE TBJ01_DLIV_DIR\n");
		sqlBuffer.append(" SET TRANS_CAR_NO = ?\n");
		sqlBuffer.append(" , INV_PRNCNT = NVL(INV_PRNCNT,0) + 1\n");
		sqlBuffer.append(" WHERE DLIV_DIRNO = ?\n");

		String dlivDirNo = "";
		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			if (i > 0) {
				if (dlivDirNo.equals(param[1])) {
					continue;
				} else {
					this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
							new Object[] { param[2], param[1] });
				}
			} else {
				this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
						new Object[] { param[2], param[1] });
			}
			dlivDirNo = param[1];
		}
	}
	
	/**
	 * 修改钢卷表发运状态及发运相关信息
	 * 
	 * @param czdWgt
	 * @param params
	 * @throws SQLException
	 */
	private void updateShipProgStatusOfPrint(String czdWgt, ArrayList<String[]> params)
			throws SQLException {
		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			// 更新钢卷公共表发运状态及发运相关信息
			StringBuffer sqlBuffer = new StringBuffer();
			sqlBuffer.append("UPDATE TBH02_COIL_COMM\n");
			sqlBuffer.append(" SET SHIP_PROG_CD = ?\n");
			sqlBuffer.append(" ,TRANS_CAR_NO = ?\n");
			sqlBuffer.append(" ,ACT_WGT     = ?\n");
			sqlBuffer.append(" ,CZD_WGT     = ?	\n");
			sqlBuffer.append(" WHERE COIL_NO    = ?\n");

			this.getDao("KgDao").ExcuteNonQuery(
					sqlBuffer.toString(),
					new Object[] { param[17], param[2], param[12], czdWgt,
							param[10] });
		}

		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			// 查询钢卷公共表中SHIP_PROG_CD状态,看是否全部改变
			boolean flag = true;
			String sql = "select a.SHIP_PROG_CD from tbh02_coil_comm a  where a.ship_dirno = '"
					+ param[0] + "' ";
			ResultSet rs = this.getDao("KgDao").ExceuteQueryForResultSet(sql);
			while (rs.next()) {
				String shipProgCd = rs.getString("SHIP_PROG_CD");
				if (!"08".equals(shipProgCd)
						&& !"09".equals(shipProgCd)) {
					flag = false;
				}
			}
			// 判断同一发货指示下的钢卷是否全部保存，如果没有的话不能改变发货指示的状态
			if (flag) {
				StringBuffer sqlBuffer = new StringBuffer();
				sqlBuffer.append("UPDATE TBJ01_SHIP_DIR\n");
				sqlBuffer.append(" SET SHIP_PROG_CD = ?\n");
				sqlBuffer.append(" WHERE SHIP_DIRNO = ?\n");
				this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
						new Object[] { param[17], param[0] });
			}
		}
	}
}