package UIJ.UIJ04;

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

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

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

	/**
	 * 查询运输单位
	 * 
	 * @return CoreReturnObject
	 * @throws SQLException
	 */
	public CoreReturnObject queryTransUnits() throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer
				.append("SELECT SHIP_COMP_NM AS LABEL, SHIP_COMP_CD AS VALUE\n");
		sqlBuffer.append(" FROM TBJ00_SHIP_COMP\n");
		sqlBuffer.append(" ORDER BY 1\n");

		ArrayList list = (ArrayList) this.getDao("KgDao")
				.ExcuteQueryReturnList(sqlBuffer.toString(), new Object[] {});
		cro.setResult(list);

		return cro;
	}

	/**
	 * 查询排车计划
	 * 
	 * @param shipProgCd
	 *            发货指示状态
	 * @param fromDate
	 *            查询开始时间
	 * @param toDate
	 *            查询结束时间
	 * @param shipDirNo
	 *            发货指示号
	 * @param curProgCd
	 *            物料进度
	 * @param shipCompNm
	 *            运输单位
	 * @param dlivTp
	 *            运输方式
	 * @param laneTp
	 *            火车入库道次
	 * @param enteranceSeq
	 *            装车次数
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryTransPlan(String shipProgCd, String fromDate,
			String toDate, String shipDirNo, String curProgCd,
			String shipCompNm, String dlivTp, String laneTp, String enteranceSeq)
			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(" FROM(SELECT *\n");
		sqlBuffer.append(" FROM TBJ01_SHIP_DIR\n");
		sqlBuffer.append(" WHERE 1 = 1\n");
		sqlBuffer
				.append(" AND SHIP_PROG_CD = ?           -- 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 ?||'%'            -- 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 = 0\n");
		sqlBuffer.append(" ORDER BY B.ALLOC_SEQ,B.DLIV_DIRNO\n");

		cro = this.getDao("KgDao").ExcuteQuery(
				sqlBuffer.toString(),
				new Object[] { shipProgCd, fromDate, toDate, shipDirNo,
						curProgCd, fromDate, toDate, dlivTp, laneTp,
						enteranceSeq, shipCompNm, dlivTp, });
		return cro;
	}

	/**
	 * 查询待装车钢卷
	 * 
	 * @param dlivTp
	 * @return
	 */
	public CoreReturnObject queryShipDir(String dlivTp) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("SELECT '' CHK,\n");
		sqlBuffer.append(" A.OLD_SAMPL_NO,\n");
		sqlBuffer.append(" A.COIL_NO,\n");
		sqlBuffer
				.append(" DECODE(A.TOT_DEC_GRD,'1','合格','2','次品',' ') TOT_DEC_GRD,\n");
		sqlBuffer
				.append(" DECODE(A.MATRL_END_CAUSE_CD,'1','合格','2','次品',' ') MATRL_END_CAUSE_CD,\n");
		sqlBuffer.append(" a.instr_coil_wth COIL_WTH,\n");
		sqlBuffer.append(" a.instr_coil_thk COIL_THK,\n");
		sqlBuffer.append(" a.ORD_NO,\n");
		sqlBuffer.append(" a.ORD_SEQ,\n");
		sqlBuffer.append(" a.spec_stl_grd,\n");
		sqlBuffer.append(" A.COIL_OUTDIA,\n");
		sqlBuffer.append(" NVL(A.ACT_WGT,0) ACT_WGT,\n");
		sqlBuffer.append(" NVL(A.CAL_WGT,0) CAL_WGT,\n");
		sqlBuffer.append(" A.CUR_LOAD_LOC,\n");
		sqlBuffer.append(" B.DEST_PCD,\n");
		sqlBuffer.append(" B.ALLOC_SEQ,\n");
		sqlBuffer.append(" B.TRANS_CAR_NO,\n");
		sqlBuffer.append(" B.DLIV_DIRNO,\n");
		sqlBuffer.append(" B.SHIP_DIRNO,\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(" (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(" A.HCOLOUR_REM    \n");
		sqlBuffer
				.append(" FROM TBH02_COIL_COMM A,TBJ01_DLIV_DIR B,TBJ01_SHIP_DIR C\n");
		sqlBuffer.append(" WHERE A.DLIV_DIRNO = B.DLIV_DIRNO\n");
		sqlBuffer.append(" AND B.SHIP_DIRNO = C.SHIP_DIRNO\n");
		sqlBuffer.append(" AND A.SHIP_PROG_CD = '04'\n");
		sqlBuffer.append(" AND B.DLIV_TP  = ? \n");
		sqlBuffer.append(" ORDER BY B.ALLOC_SEQ\n");

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

	/**
	 * 查询下达排车计划时排车计划对应的钢卷信息
	 * 
	 * @param dlivNo
	 * @return
	 */
	public CoreReturnObject queryPlanCoil(String dlivNo) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer
				.append("SELECT substr(A.DLIV_NO,5,8) DLIV_NO,A.COIL_NO,A.COIL_STEEL,A.COIL_HEIGHT||'*'||A.COIL_WIDTH T_W FROM TBJ01_DLIVNO_COIL A \n");
		sqlBuffer.append(" WHERE A.DLIV_NO = ?  \n");
		sqlBuffer.append(" order by a.COIL_NO\n");

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

	/**
	 * 待装车取消
	 * 
	 * @param list
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject returnSHIPDIRCAR(ArrayList<String[]> list)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String[] param = null;
		for (int i = 0; i < list.size(); i++) {
			param = (String[]) list.get(i);
			// 取消放入运输表的车厢号和排车顺序号，不能改变运输表的状态
			StringBuffer sqlBuffer = new StringBuffer();
			sqlBuffer.append("update tbj01_dliv_dir\n");
			sqlBuffer.append(" set \n");
			sqlBuffer.append(" ALLOC_SEQ = '',\n");
			sqlBuffer.append(" TRANS_CAR_NO = '',\n");
			sqlBuffer.append(" LANE_TP = '',\n");
			sqlBuffer.append(" ENTERANCE_SEQ = ''   \n");
			sqlBuffer.append(" where DLIV_DIRNO = ? \n");
			this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
					new Object[] { param[0] });
			// 取消钢卷公共表中的车辆号，排车顺序号
			sqlBuffer = new StringBuffer();
			sqlBuffer.append(" update TBH02_COIL_COMM\n");
			sqlBuffer.append(" set \n");
			sqlBuffer.append(" TRAIN_STEP_NO = '',\n");
			sqlBuffer.append(" TRANS_CAR_NO = '',\n");
			sqlBuffer.append(" SHIP_PROG_CD = '03' \n");
			sqlBuffer.append(" where DLIV_DIRNO =?\n");
			this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
					new Object[] { param[0] });
			// 更新发货表状态标志
			sqlBuffer = new StringBuffer();
			sqlBuffer.append(" update tbj01_ship_dir\n");
			sqlBuffer.append(" set \n");
			sqlBuffer.append(" ship_prog_cd = '03' \n");
			sqlBuffer.append(" where ship_dirno =?\n");
			this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
					new Object[] { param[1] });
		}
		return cro;
	}

	/**
	 * 汽车排车及装车指示，运送指示状态不由下车改变，下车只读运送指示表。发货指示状态由3改为4，不改变物料进度状态'SFB'(运送待机)
	 * 这里需要判断同一发货指示下的运输指示是否全部发完，没有发完的情况下是不能改变发货指示状态的。
	 * 
	 * @param list
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject saveSHIPDIRCAR(ArrayList<String[]> list)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = null;
		for (int i = 0; i < list.size(); i++) {
			// 判断是否全部选择的标志位
			boolean flag = true;

			String[] param = list.get(i);
			// 把车厢号和排车顺序号放入运输表，不改变运输表的状态
			sqlBuffer = new StringBuffer();
			sqlBuffer.append(" update tbj01_dliv_dir\n");
			sqlBuffer.append(" set \n");
			sqlBuffer.append(" ALLOC_SEQ = ?,\n");
			sqlBuffer.append(" TRANS_CAR_NO = ? \n");
			sqlBuffer.append(" where DLIV_DIRNO = ?\n");
			this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
					new Object[] { param[0], param[1], param[2] });
			// 更新钢卷公共表中的车辆号，排车顺序号
			sqlBuffer = new StringBuffer();
			sqlBuffer.append(" update TBH02_COIL_COMM\n");
			sqlBuffer.append(" set \n");
			sqlBuffer.append(" TRAIN_STEP_NO = ?,\n");
			sqlBuffer.append(" TRANS_CAR_NO = ?,\n");
			sqlBuffer.append(" SHIP_PROG_CD = '04'\n");
			sqlBuffer.append(" where DLIV_DIRNO =?\n");
			this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
					new Object[] { param[0], param[1], param[2] });
			// 查询钢卷公共表中的SHIP_PROG_CD状态
			sqlBuffer = new StringBuffer();
			sqlBuffer
					.append("select a.SHIP_PROG_CD from tbh02_coil_comm a  where a.ship_dirno = '"
							+ param[3] + "'\n");
			ResultSet rs = this.getDao("KgDao").ExceuteQueryForResultSet(
					sqlBuffer.toString());
			while (rs.next()) {
				String shipProgCd = rs.getString("SHIP_PROG_CD");
				if ("03".equals(shipProgCd)) {
					flag = false;
					break;
				}
			}

			// 更新发货表发货状态
			if (flag) {
				sqlBuffer = new StringBuffer();
				sqlBuffer.append(" update tbj01_ship_dir\n");
				sqlBuffer.append(" set \n");
				sqlBuffer.append(" ship_prog_cd = '04' \n");
				sqlBuffer.append(" where ship_dirno =?\n");
				this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
						new Object[] { param[3] });
			}
		}
		return cro;
	}

	/**
	 * 确认排车操作
	 * 
	 * @param regId
	 * @param list
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject saveDLIVDIRCAR(String regId,
			ArrayList<String[]> list) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();

		String[] param = null;
		ArrayList<String> dlivDirNoList = new ArrayList<String>();
		StringBuffer sqlBuffer = null;
		// 根据运输指示号的个数来生成发货清单号
		for (int i = 0; i < list.size(); i++) {
			param = list.get(i);

			boolean flag = false;// 运输号是否重复标识
			for (int j = 0; j < dlivDirNoList.size(); j++) {
				if (param[2].equals(dlivDirNoList.get(i))) {
					flag = true;
					break;
				}
			}
			// 生成发货清单号
			if (!flag) {
				sqlBuffer = new StringBuffer();
				sqlBuffer.append("UPDATE   TBJ01_DLIV_DIR\n");
				sqlBuffer
						.append(" SET      INV_NO = (SELECT   SUBSTRB(A.INV_NO,1,4)||TO_CHAR(TO_NUMBER(SUBSTRB(INV_NO,5,6)) + 1,'FM000000')\n");
				sqlBuffer
						.append(" FROM    (SELECT ROW_NUMBER() OVER (ORDER BY  NVL(INV_NO,'JB0-0000000')  DESC) AS RN\n");
				sqlBuffer.append(" ,INV_DTIME\n");
				sqlBuffer.append(" ,INV_NO\n");
				sqlBuffer.append(" FROM   TBJ01_DLIV_DIR \n");
				sqlBuffer.append(" WHERE    INV_NO LIKE 'JB0%') A\n");
				sqlBuffer.append(" WHERE  A.RN = 1) \n");
				sqlBuffer
						.append(" , INV_DTIME  = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')\n");
				sqlBuffer.append(" WHERE   DLIV_DIRNO = ?\n");
				this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
						new Object[] { param[2] });
			}

			// 将发货清单号更新到钢卷公共表
			sqlBuffer = new StringBuffer();
			sqlBuffer
					.append("SELECT A.INV_NO FROM TBJ01_DLIV_DIR  A WHERE A.DLIV_DIRNO = '"
							+ param[2] + "'\n");
			ResultSet rs = this.getDao("KgDao").ExceuteQueryForResultSet(
					sqlBuffer.toString());
			if (rs.next()) {
				String invNo = rs.getString("INV_NO");
				sqlBuffer = new StringBuffer();
				sqlBuffer.append("update tbh02_coil_comm X SET \n");
				sqlBuffer.append(" X.SHIP_INVNO = ?\n");
				sqlBuffer.append(" WHERE X.DLIV_DIRNO = ?\n");
				this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
						new Object[] { invNo, param[2] });
			}

			// 根据钢卷号更新车厢号和排车顺序
			sqlBuffer = new StringBuffer();
			sqlBuffer.append("update TBH02_COIL_COMM\n");
			sqlBuffer.append(" set \n");
			sqlBuffer.append(" TRANS_CAR_NO = ?,\n");
			sqlBuffer.append(" TRAIN_STEP_NO = ?,\n");
			sqlBuffer.append(" DLIV_DIRNO = ?,\n");
			sqlBuffer.append(" SHIP_DIRNO = ?,\n");
			sqlBuffer.append(" ACT_WGT    = ?,\n");
			sqlBuffer.append(" SHIP_PROG_CD = '06',\n");
			sqlBuffer.append(" CUR_LOAD_LOC = 'C' \n");
			sqlBuffer.append(" where OLD_SAMPL_NO =? \n");
			this.getDao("KgDao").ExcuteNonQuery(
					sqlBuffer.toString(),
					new Object[] { param[1], param[0], param[2], param[3],
							param[4], param[6] });
			// 清除垛位表钢卷信息
			sqlBuffer = new StringBuffer();
			sqlBuffer.append("update TBH05_COIL_YARD A SET \n");
			sqlBuffer.append(" A.COIL_NO = '',\n");
			sqlBuffer.append(" A.REG_ID = '',\n");
			sqlBuffer.append(" A.REG_DTIME = ''\n");
			sqlBuffer.append(" WHERE A.COIL_NO = ?\n");
			this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
					new Object[] { param[6] });

			flag = true;
			// 查询钢卷发运状态
			sqlBuffer = new StringBuffer();
			sqlBuffer
					.append("select a.SHIP_PROG_CD from tbh02_coil_comm a  where a.ship_dirno = '"
							+ param[3] + "'\n");
			rs = this.getDao("KgDao").ExceuteQueryForResultSet(
					sqlBuffer.toString());
			while (rs.next()) {
				String tmpShipProgCd = rs.getString("SHIP_PROG_CD");
				if ("03".equals(tmpShipProgCd) || "04".equals(tmpShipProgCd)) {
					flag = false;
					break;
				}
			}
			if (flag) {
				// 更新发运表发运状态
				sqlBuffer = new StringBuffer();
				sqlBuffer.append("update tbj01_ship_dir\n");
				sqlBuffer.append(" set \n");
				sqlBuffer.append(" ship_prog_cd = '05'\n");
				sqlBuffer.append(" where ship_dirno =?\n");
				this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
						new Object[] { param[3] });
				// 开具质保书
				String[] inparams = new String[] { param[3] };
				String sCRET_NO = "";
				String sRETURN_CD = "";
				String sRETURN_MSG = "";

				this.getDao("KgDao").ExcuteProcedure(
						"ExecuteProcedure_NIB029020", inparams,
						new String[] { sCRET_NO, sRETURN_CD, sRETURN_MSG });

				if ("YY".equals(sRETURN_CD)) {
					// SendProg_NIE012080();
				} else {
					// 这里出现的异常情况是板坯号与钢卷号不匹配
					cro.setV_errMsg("开具质保书失败，请联系管理员！");
					// 回滚
					this.getDao("KgDao").getConnection().rollback();
				}
			}
		}

		return cro;
	}

	public void SendProg_NIE012080() {
	}
}