package UIJ.UIJ04;

import java.sql.ResultSet;
import java.sql.SQLException;

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

/**
 * 发运明细回退
 * 
 * @author siy
 * @date 2010-9-13
 */
public class UIJ040050 extends CoreIComponent {

	/**
	 * 根据发运指示号查询可回退的发运明细
	 * 
	 * @param dlivDirNo
	 *            发运指示号
	 * @return CoreReturnObject
	 * @throws SQLException
	 */
	public CoreReturnObject queryShipRetRs(String dlivDirNo)
			throws SQLException {
		String shipProgCd = "08";// 发货明细结束
		CoreReturnObject cro = this.query(dlivDirNo, shipProgCd);
		return cro;
	}

	/**
	 * 查询没有车号的发货明细的卷
	 * 
	 * @param dlivDirNo
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryShipRetRsNoCarNo(String dlivDirNo)
			throws SQLException {
		String shipProgCd = "03";
		CoreReturnObject cro = this.query(dlivDirNo, shipProgCd);
		return cro;
	}

	private CoreReturnObject query(String dlivDirNo, String shipProgCd)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("SELECT A.OLD_SAMPL_NO\n");
		sqlBuffer.append(" ,A.DLIV_DIRNO\n");
		sqlBuffer.append(" ,A.SHIP_DIRNO\n");
		sqlBuffer.append(" ,A.SHIP_INVNO\n");
		sqlBuffer.append(" ,A.CUR_PROG_CD\n");
		sqlBuffer.append(" ,A.COIL_STAT\n");
		sqlBuffer.append(" ,A.SHIP_PROG_CD\n");
		sqlBuffer.append(" ,A.TRNF_DTIME\n");
		sqlBuffer.append(" ,DECODE(C.DLIV_TP,'C','汽运','T','火运') DLIV_TP \n");
		sqlBuffer.append(" ,A.TRANS_CAR_NO\n");
		sqlBuffer.append(" ,A.TRNF_REG\n");
		sqlBuffer.append(" ,A.ORD_NO\n");
		sqlBuffer.append(" ,A.ORD_SEQ\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(" ,(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 A.SM_CFNM FROM TBZ00_COMMCD A WHERE A.LG_CD = 'A01009' AND A.SM_CD = C.DEST_PCD) DEST_PCD_DESC\n");
		sqlBuffer.append(" FROM TBH02_COIL_COMM A,TBJ01_DLIV_DIR C \n");
		sqlBuffer.append(" WHERE A.DLIV_DIRNO = C.DLIV_DIRNO \n");
		sqlBuffer.append(" AND A.DLIV_DIRNO = ? \n");
		sqlBuffer.append(" AND A.COIL_STAT = '3' \n");
		sqlBuffer.append(" AND A.CUR_PROG_CD = 'SFF'\n");
		sqlBuffer.append(" AND A.SHIP_PROG_CD = ?\n");
		sqlBuffer.append(" AND A.TRNF_DTIME IS NOT NULL\n");

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

	/**
	 * 发运明细回退
	 * @param dlivDirNo
	 * @param regId
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject returnShipResult(String dlivDirNo, String regId)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = "select A.ISCHANGEWGT from TBJ02_COIL_COMM A WHERE A.DLIV_DIRNO = '"
				+ dlivDirNo + "'";
		ResultSet rs = this.getDao("KgDapo").ExceuteQueryForResultSet(sql);

		boolean isClearing = false;// 销售是否已结算
		if (rs.next()) {
			String isChangeWgt = rs.getString("ISCHANGEWGT");
			if (null == isChangeWgt || !"1".equals(isChangeWgt)) {
				isClearing = true;
			}
		}

		if (isClearing) {
			cro.setV_errCode(new Integer(-1));
			cro.setV_errMsg("明细数据已经发送到销售，请联系销售退明细后成品才能进行明细回退！");
		} else {
			// 更新钢卷公共表钢卷状态及发运状态
			StringBuffer sqlBuffer = new StringBuffer();
			sqlBuffer.append("UPDATE TBH02_COIL_COMM A SET \n");
			sqlBuffer.append(" A.COIL_STAT = '2'\n");
			sqlBuffer.append(" ,A.CUR_PROG_CD = 'SFB'\n");
			sqlBuffer.append(" ,A.SHIP_PROG_CD = '03'\n");
			sqlBuffer.append(" ,A.TRNF_DTIME = ''\n");
			sqlBuffer.append(" ,A.TRANS_CAR_NO = ''\n");
			sqlBuffer.append(" ,A.TRNF_REG = ''\n");
			sqlBuffer.append(" ,A.TRNF_SHIFT = ''\n");
			sqlBuffer.append(" ,A.TRNF_GROUP = ''\n");
			sqlBuffer.append(" ,A.TRNF_USE_TIME = ''\n");
			sqlBuffer.append(" ,A.BEF_SHIP_INVNO = A.SHIP_INVNO\n");
			sqlBuffer.append(" ,A.BEF_CRET_NO = A.CRET_NO\n");
			sqlBuffer.append(" ,A.RETURN_SHIP_REG = ?\n");
			sqlBuffer
					.append(" ,A.RETURN_SHIP_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')\n");
			sqlBuffer.append(" WHERE A.DLIV_DIRNO = ?\n");
			this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
					new Object[] { regId, dlivDirNo });
			// 清除发运表发运信息
			sqlBuffer = new StringBuffer();
			sqlBuffer.append("UPDATE TBJ01_DLIV_DIR A SET \n");
			sqlBuffer.append(" A.LANE_TP = ''\n");
			sqlBuffer.append(" ,A.ENTERANCE_SEQ = ''\n");
			sqlBuffer.append(" ,A.ALLOC_SEQ = ''\n");
			sqlBuffer.append(" ,a.TRANS_CAR_NO = ''\n");
			sqlBuffer.append(" WHERE A.DLIV_DIRNO = ?\n");
			this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
					new Object[] { dlivDirNo });
			// 更新运送表运送状态
			sqlBuffer = new StringBuffer();
			sqlBuffer.append("UPDATE TBJ01_SHIP_DIR A SET \n");
			sqlBuffer.append(" A.SHIP_PROG_CD = '03'\n");
			sqlBuffer
					.append(" WHERE A.SHIP_DIRNO = (SELECT T.SHIP_DIRNO FROM TBJ01_DLIV_DIR T WHERE T.DLIV_DIRNO = ?)\n");
			this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
					new Object[] { dlivDirNo });
			// 删除结算接口表数据
			sqlBuffer = new StringBuffer();
			sqlBuffer
					.append("DELETE TBJ02_COIL_COMM A WHERE A.DLIV_DIRNO = ? AND A.ISCHANGEWGT = '1'\n");
			this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
					new Object[] { dlivDirNo });
		}

		return cro;
	}
}