XinYu_LZMES/.svn/pristine/9c/9c3f3fbcb117a7f5f50ad6ce01760767a1108ef3.svn-base

package UIJ.UIJ06;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Map;
import java.sql.Connection;
import java.text.SimpleDateFormat;

import org.apache.commons.lang.StringUtils;

import UIB.COM.XmlSqlParsersFactory;

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

/**
 * 发货实绩录入与发行装车明细
 * 
 * @author zsx
 * @date 2017-8-4
 */
public class UIJ060020 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();
		String sql = XmlSqlParsersFactory.getSql("UIJ060020_01.SELECT");
		cro = this.getDao("KgDao").ExcuteQuery(
				sql,
				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();
		Connection conn = null;
		Statement sta = null;
		ArrayList<String> list=new ArrayList<String>();
		try {
			conn = this.getDao("KgDao").getConnection();
			conn.setAutoCommit(false);
			// 修改钢卷发运状态
			updateCoilProgStatus(conn,trnfShift, trnfGroup, trnfRegId, trnfDTime,
					params);
			// 修改钢卷表发运状态及发运相关信息 、发运表发运状态
			updateShipProgStatus(conn,czdWgt, params);
			conn.commit();
		} catch (Exception ex) {
			if(conn != null){
				conn.rollback();
			}
			cro.setV_errCode(-1);
			cro.setV_errMsg("发运明细生成失败！");
		} finally{
			if(conn != null && !conn.isClosed()){
				conn.close();
			}
		}
		return cro;
	}
	
	/**
	 * 修改钢卷发运状态
	 * 
	 * @param trnfShift
	 * @param trnfGroup
	 * @param trnfRegId
	 * @param trnfDTime
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private void updateCoilProgStatus(Connection conn,String trnfShift, String trnfGroup,
			String trnfRegId, String trnfDTime, ArrayList<String[]> params)
			throws SQLException {
		PreparedStatement pSta = null;
		PreparedStatement pSta1 = null;
		String trnftime ;
		Date d = new Date(); 
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
		trnftime = sdf.format(d);
		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			// 更新钢卷公共表发货状态
			String sql1 = XmlSqlParsersFactory.getSql("UIJ060020_01.UPDATE");
			pSta = conn.prepareStatement(sql1);
			pSta.setString(1, param[25]);
			pSta.setString(2, param[26]);
			pSta.setString(3, param[27]);
			pSta.setString(4, param[19]);
			pSta.setString(5, param[20]);
			pSta.setString(6, param[21]);
			pSta.setString(7, param[2]);
			pSta.setString(8, trnfShift);
			pSta.setString(9, trnfGroup);
			pSta.setString(10, trnfRegId);
			pSta.setString(11, trnfDTime);
			pSta.setString(12, param[10]);
			pSta.executeUpdate();
			pSta.close();
			
			//保持同一车号下面操作时间一致  20190411  wl
			String sql3 = XmlSqlParsersFactory.getSql("UIJ030030_time.UPDATE");
			pSta1 = conn.prepareStatement(sql3);
			pSta1.setString(1, trnftime);
			pSta1.setString(2, param[1]);
			pSta1.executeUpdate();
			//conn.commit();
			pSta1.close();
			
			// 更新发运表车辆号
			String sql2 = XmlSqlParsersFactory.getSql("UIJ060020_02.UPDATE");
			pSta = conn.prepareStatement(sql2);
			pSta.setString(1, param[2]);
			pSta.setString(2, param[1]);
			pSta.executeUpdate();
			pSta.close();
		}
	}

	/**
	 * 修改钢卷表发运状态及发运相关信息
	 * 
	 * @param czdWgt
	 * @param params
	 * @throws SQLException
	 */
	private void updateShipProgStatus(Connection conn,String czdWgt,ArrayList<String[]> params)
			throws SQLException {
		PreparedStatement pSta = null;
		Statement sta = null;
		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			// 更新钢卷公共表发运状态及发运相关信息
			String sql1 = XmlSqlParsersFactory.getSql("UIJ060020_03.UPDATE");
			pSta = conn.prepareStatement(sql1);
			pSta.setString(1, param[17]);
			pSta.setString(2, param[2]);
			pSta.setString(3, czdWgt);
			pSta.setString(4, param[10]);
			pSta.executeUpdate();
			pSta.close();
		}

		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			// 查询钢卷公共表中SHIP_PROG_CD状态,看是否全部改变
			boolean flag = true;
			String sql2 = "select a.SHIP_PROG_CD from C_TBL02_COIL_COMM a  where a.ship_dirno = '"
					+ param[0] + "' ";
			sta = conn.createStatement();			
			ResultSet rs = sta.executeQuery(sql2);
			while (rs.next()) {
				String shipProgCd = rs.getString("SHIP_PROG_CD");
				//&& !"09".equals(shipProgCd)
				if (!"07".equals(shipProgCd) && !"08".equals(shipProgCd)
						) {
					flag = false;
				}
			}
			rs.close();
			sta.close();
			// 判断同一发货指示下的钢卷是否全部保存，如果没有的话不能改变发货指示的状态
			if (flag) {
				String sql3 = XmlSqlParsersFactory.getSql("UIJ060020_04.UPDATE");
				pSta = conn.prepareStatement(sql3);
				pSta.setString(1, param[17]);
				pSta.setString(2, param[0]);
				pSta.executeUpdate();
				pSta.close();
			}
			
		}
		
	}
	/**
	 * 保存发货明细状态的同时把钢卷数据保存到货运中国中间表去
	 * @param params
	 */
	public void saveCargocnData(ArrayList<String[]> params,String czdWgt,String trnfDTime,ArrayList<String> list) {
		try {
			for (int i = 0; i < params.size(); i++) {
				String[] param = params.get(i);
				String sql3 = XmlSqlParsersFactory.getSql("UIJ060020_cargocn.select");
				String flat="1";
				ArrayList coilList = (ArrayList) this.getDao("KgDao").ExcuteQueryReturnList(sql3,new Object[]{param[31]});
				//若表中存在该钢卷则删除
				if(null != coilList && 0 != coilList.size()){
					String sql4 = XmlSqlParsersFactory.getSql("UIJ060020_cargocn.delete");
					this.getDao("KgDao").ExcuteNonQuery(sql4, new Object[]{param[31]});
					flat="0";
				}
				String sql2 = XmlSqlParsersFactory.getSql("UIJ060020_cargocn.insert");
				Object[] param2 =  new Object[25];
				param2[0] = param[10]; //真实钢卷号
				param2[1] = param[1]; //发运指示号
				param2[2] = param[18]; //牌号
				param2[3] = param[2]; //车牌号
				param2[4] = param[12]; //重量
				param2[5] = trnfDTime; //发货时间
				param2[6] = param[33]; //收货单位
				param2[7] = param[34]; //到站
				param2[8] = param[32]; //合同单位
				String SHIP_COMP = param[35];
				if(StringUtils.isEmpty(SHIP_COMP))
					SHIP_COMP = param[24];
				param2[9] = SHIP_COMP; //运输公司
				param2[10] = param[36]; //产品名称
				param2[11] = "新余市"; //发货城市
				param2[12] = "冷轧厂"; //发货地点
				param2[13] = 1;  //订单类型
				//通过到站地点从产销数据库查收货城市、收货人、电话
				String receiveCity = param[34];
				String dlivno = param[1];
				String sql5="select a.UNLOADDOCK, a.SHR, a.SHRDH from sel_stationinfo a where a.STATIONCODE = (select DEST_PCD from tbj01_dliv_dir b where b.DLIV_DIRNO='"+dlivno+"' and rownum = 1)";
				String receiveNM = "";
				String receivePhone =  "";
				ResultSet rs = this.getDao("KgDao").ExceuteQueryForResultSet(sql5);
				if (rs.next()) {
					receiveNM =  rs.getString("SHR"); //收货人
				    receivePhone =  rs.getString("SHRDH"); //收货人电话
				    if(StringUtils.isEmpty(receiveNM))
				    	receiveNM="";
				    if(StringUtils.isEmpty(receivePhone))
				    	receivePhone="";
					if(!StringUtils.isEmpty(rs.getString("UNLOADDOCK")))
						receiveCity = rs.getString("UNLOADDOCK"); //收货城市
				}
				this.getDao("KgDao").closeRs(rs);
				param2[14] =  receiveCity; //收货城市
				param2[15] = receiveNM; //收货联系人
				param2[16] = receivePhone; //收货联系人电话
				param2[17] = "6"; //未发送状态
				param2[18] = "LZMES_UIJ060020"; //程序id
				param2[19] = param[28]; //操作人
				param2[20]= param[29]; // 运输方式
				param2[21]= "A"; // 修改标示
				param2[22]= param[22]; // 合同号
				String sql4 = XmlSqlParsersFactory.getSql("UIJ060020_cargocn.dlivselect");
				ArrayList dlivList = (ArrayList) this.getDao("KgDao").ExcuteQueryReturnList(sql4,new Object[]{param[1]});
				//若表中存在该钢卷则删除
				if(null != dlivList && 0 != dlivList.size())
				{
					param2[23]="0";
				}
				else
				{
					param2[23]= czdWgt; // 草支垫扣重
				}
				param2[24]= param[37]; // 包装重量
				this.getDao("KgDao").ExcuteNonQuery(sql2, param2);
				if("0".equals(flat)){
					String sql = XmlSqlParsersFactory.getSql("UIJ060020_cargocn_01.update");
					this.getDao("KgDao").ExcuteNonQuery(sql, new Object[]{param2[1]});
				}
			}
			if(!list.isEmpty())
			{
				for (int i = 0; i < list.size(); i++) {
				String sql = XmlSqlParsersFactory.getSql("UIJ060020_cargocn_02.update");
				this.getDao("KgDao").ExcuteNonQuery(sql, new Object[]{list.get(i)});
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 打印发货明细后修改相关状态
	 * 
	 * @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();
		Connection conn = null;
		Statement sta = null;
		ArrayList<String> list=new ArrayList<String>();
		double sumCzdWgt = 0;
		int a=0;
		if(params != null){
			sumCzdWgt = params.size()*25;//草支垫重量修改为每卷25KG
		}
		try {
			conn = this.getDao("KgDao").getConnection();
			conn.setAutoCommit(false);
			updateInvoiceInfoOfDelivery(conn,params);
			
			for (int i = 0; i < params.size(); i++) {
				String[] param = params.get(i);
				String coilno=param[10];
				StringBuffer str = new StringBuffer();
				str.append(" SELECT COIL_NO, DLIV_DIRNO, SPEC_STL_GRD, TRANS_CAR_NO, COIL_WGT, TRNF_DTIME, ORD_NM, \n");
				str.append(" DEST_PCD_DESC, CUST_NM, SHIP_COMP_CD, SHIP_COMP_NM, SM_CFNM, SHIPPERCITY, SHIPPERADDR, \n");
				str.append(" ORDERTYPE, SHIPPERCONTACTNAME, SHIPPERCONTACTPHONE, RECEIVECONTACTNAME, RECEIVECONTACTPHONE, \n");
				str.append(" FREIGHTPAYSIDE, DRIVERNAME, DRIVERPHONE, SHIP_PROG_CD, REG_PGM_ID, REG_ID, REG_DTIME, \n");
				str.append(" MOD_PGM_ID, MOD_ID, MOD_DTIME, RECEIVECITY, TRANSMODE, EDITFLAG, PACTNO, KZZL, PKG_WGT \n");
				str.append(" FROM TBB01_CARGOCN_DATA WHERE COIL_NO = '");
				str.append(param[10]);
				str.append("' ");
			sta = conn.createStatement();			
			ResultSet rs = sta.executeQuery(str.toString());
			while (rs.next()) {
				list.add(a, coilno);
				String sql4 = XmlSqlParsersFactory.getSql("UIJ060020_cargocn.delete");
				this.getDao("KgDao").ExcuteNonQuery(sql4,new Object[] { coilno });
				a++;
			}
			rs.close();
			sta.close();				
			}
			saveCargocnData(params,czdWgt,trnfDTime,list);
            // 修改钢卷表发运状态及发运相关信息 、发运表发运状态
			updateShipProgStatus(conn,String.valueOf(sumCzdWgt), params);
			updateShipProgStatusOfPrint(conn,String.valueOf(sumCzdWgt), params);
			addshipdetail(conn,String.valueOf(sumCzdWgt),params);
			addzycoil(conn,params);
			conn.commit();
		} catch (Exception ex) {
			if(conn != null){
				conn.rollback();
			}
			cro.setV_errCode(-2);
			cro.setV_errMsg("发运明细打印失败！");
			ex.printStackTrace();
		} finally{
			if(conn != null && !conn.isClosed()){
				conn.close();
			}
		}
        return cro;
	}
	
	private void addshipdetail(Connection conn, String czdWgt,
			ArrayList<String[]> params) throws SQLException {
		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			Double wgt = Double.parseDouble(param[12]) / 1000;
			String sql = XmlSqlParsersFactory.getSql("UIJ060020_09.CALL");
			CallableStatement cstm = conn.prepareCall(sql);
			cstm.setString(1, "1");// 标志
			cstm.setString(2, param[1]);
			cstm.setString(3, param[2]);
			cstm.setString(4, param[18]);
			cstm.setString(5, param[30]);
			cstm.setString(6, "1");
			cstm.setString(7, wgt.toString());
			cstm.setString(8, czdWgt);
			cstm.setString(9, "4001LZ1");
			cstm.setString(10, "冷轧线");
			cstm.execute();
			cstm.close();
		}
	}
	
	private void addzycoil(Connection conn, ArrayList<String[]> params)
			throws SQLException {
		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			if (param[31].equals("224902")||param[31].equals("243187")) { // 中冶新材客户代码

				String sql = XmlSqlParsersFactory.getSql("UIJ060020_10.CALL");
				CallableStatement cstm = conn.prepareCall(sql);
				cstm.setString(1, param[10]);// 钢卷号
				cstm.execute();
				cstm.close();
			}
		}
	}
	
	/**
	 * 修改车辆号
	 * @param dlivDirNo
	 * @param carNo
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject updateCarNo(String dlivDirNo,String carNo) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		Map result = this.getDao("KgDao").CoreBeginTranscation();
		
		String sql = XmlSqlParsersFactory.getSql("UIJ060020_07.UPDATE");
		this.getDao("KgDao").ExcuteNonQuery(sql, new Object[]{carNo,dlivDirNo});
		sql = XmlSqlParsersFactory.getSql("UIJ060020_08.UPDATE");
		this.getDao("KgDao").ExcuteNonQuery(sql, new Object[]{carNo,dlivDirNo}); 
		sql = XmlSqlParsersFactory.getSql("UIJ060020_09.UPDATE");
		this.getDao("KgDao").ExcuteNonQuery(sql, new Object[]{carNo,dlivDirNo});
		this.getDao("KgDao").CoreCommit(result);
		return cro;
	}

	private void updateInvoiceInfoOfDelivery(Connection conn,ArrayList<String[]> params)
			throws SQLException {
		PreparedStatement pSta = null;
		String sql = XmlSqlParsersFactory.getSql("UIJ060020_05.UPDATE");
    	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 {
					pSta = conn.prepareStatement(sql);
					pSta.setString(1, param[2]);
					pSta.setString(2, param[1]);
					pSta.executeUpdate();
					pSta.close();
				}
			} else {
				pSta = conn.prepareStatement(sql);
				pSta.setString(1, param[2]);
				pSta.setString(2, param[1]);
				pSta.executeUpdate();
				pSta.close();
			}
			dlivDirNo = param[1];
		}
	}

	/**
	 * 修改钢卷表发运状态及发运相关信息
	 * 
	 * @param czdWgt
	 * @param params
	 * @throws SQLException
	 */
	private void updateShipProgStatusOfPrint(Connection conn,String czdWgt,
			ArrayList<String[]> params) throws SQLException {
		PreparedStatement pSta = null;
		Statement sta = null;
		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			// 更新钢卷公共表发运状态及发运相关信息
			String sql1 = XmlSqlParsersFactory.getSql("UIJ060020_06.UPDATE");
			pSta = conn.prepareStatement(sql1);
			pSta.setString(1, param[17]);
			pSta.setString(2, param[2]);
			pSta.setString(3, czdWgt);
			pSta.setString(4, param[10]);
			pSta.executeUpdate();
			pSta.close();
		}

		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 C_TBL02_COIL_COMM a  where a.ship_dirno = '"
					+ param[0] + "' ";
			sta = conn.createStatement();
			ResultSet rs = sta.executeQuery(sql);
			while (rs.next()) {
				String shipProgCd = rs.getString("SHIP_PROG_CD");
				if (!"08".equals(shipProgCd)) {
					flag = false;
				}
			}
			rs.close();
			sta.close();
			// 判断同一发货指示下的钢卷是否全部保存，如果没有的话不能改变发货指示的状态
			if (flag) {
				String sql2 = XmlSqlParsersFactory.getSql("UIJ060020_04.UPDATE");
				pSta = conn.prepareStatement(sql2);
				pSta.setString(1, param[17]);
				pSta.setString(2, param[0]);
				pSta.executeUpdate();
				pSta.close();
			}
		}
	}

	/**
	 * 生成质保书
	 * 
	 * @param dlivDirNo
	 * @param ordNo
	 * @param ordSeq
	 * @return
	 */
	private boolean createQltyBook(ArrayList<String[]> params)
			throws SQLException {
		boolean flag = true;
		for (int i = 0; i < params.size(); i++) {
			String[] param = params.get(i);
			CoreReturnObject cro = new CoreReturnObject();
			String proName = XmlSqlParsersFactory.getSql("UIJ060020_01.CALL");
			String QltyBookNo = "";
			String retMsg = "";
			String[] inparams = new String[] { param[0], param[22], param[23] };
			String[] outparams = new String[] { QltyBookNo, retMsg };
			cro = this.getDao("KgDao").ExcuteProcedure(proName, inparams,
					outparams);
			System.out.println("createQltyBook    QltyBookNo = " + QltyBookNo
					+ "     retMsg = " + retMsg + "     cro = "
					+ cro.getResult());
		}
		return flag;
	}
}