package ibase.webitm.ejb.wms;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ProcessEJB;
//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc
//import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.start
//import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.start
/*
import java.sql.PreparedStatement;

//import java.sql.SQLException;
//import java.sql.Timestamp;
//import java.text.SimpleDateFormat;
//import java.util.ArrayList;
//import java.util.HashMap;
//import java.util.Iterator;
//import java.util.Set;
*/
//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.end

import org.w3c.dom.Document;

@javax.ejb.Stateless
public class ConsolidatToDocPrc extends ProcessEJB implements ConsolidatToDocPrcLocal, ConsolidatToDocPrcRemote{
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	
	public String process() throws RemoteException,ITMException
	{
		return "";
	}

	//Process Method 
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;

		GenericUtility genericUtility = GenericUtility.getInstance();

		try
		{	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
				System.out.println("headerDom" + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
				System.out.println("detailDom" + detailDom);
			}

			retStr = process(headerDom, detailDom, windowName, xtraParams);

		}
		catch (Exception e)
		{
			System.out.println("Exception :ConsolidationPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			System.out.println("exception :"+e.getMessage());
			retStr = e.getMessage();
		}
		return retStr;

	}//END

	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.start
		/*
		String waveId = "", pickOrder = "", siteCode = "", itemCode = "", locCode = "", ptcn = "",locCodeTo="";
		String lotNo = "", lotSl = "", saleOrder = "", despId = "";
		String sql = "", sql1 = "",sqlOrd="", partNo = "", siteCodeMfg = "", packCode = "", packInstr = ""; 		
		String errString = "", rateClg = "", custItemRef = "", rateStduom = "", lineType = "I";		
		String refId = "",refSer = "", refType = "";
		String mbaseUnit = "", unit = "", expLev = "",unitStd = "";
		

		Connection conn = null;
		PreparedStatement pstmt = null, pstmt1 = null,pstmtOrder = null;
		ResultSet rs = null, rs1 = null;
		
		PreparedStatement pstmtSordItem = null;
		PreparedStatement pstmtSordDet = null;
		PreparedStatement pstmtStock= null;
		PreparedStatement pstmtItem= null;
		PreparedStatement pstmtIss= null;
		PreparedStatement pstmtSord= null;

		ResultSet rsSordItem = null;
		ResultSet rsSordDet = null;
		ResultSet rsStock = null;
		ResultSet rsItem = null;
		ResultSet rsIss = null;
		ResultSet rsSord = null;

		int lineNo = 0 , lineNoOrd = 0; 
		int update = 0, count = 0;
		double meffQty = 0, qtyStduom = 0,qtyStduom1= 0, convQtyStduom = 0, conRtuStduom = 0, convFact = 1, quantity = 0;
		double grossWeight = 0d, tareWeight = 0d, netWeight = 0d;//Gulzar on 29/12/11
		double rate = 0d;
		double noArt = 0d;
		double shipperSize = 0;
		boolean isError = false, isConfirmed = true;
		
		Timestamp mfgDate = null, expDate = null, retestDate = null;
		DistCommon disCommon = new DistCommon();
		*/
		String ptcn = "",errString = "",sql = "";
		String despId = "";
		int count = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		HashMap dataMap = new HashMap();
		boolean isTasksConfirmed = false;
		boolean isShipExist = false;
		
		boolean isError = false;
		
		ConsolidatToDoc consToPrc = new ConsolidatToDoc();
		
		//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.end
		try
		{
			System.out.println("*********inside try block*******");
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			ptcn = genericUtility.getColumnValue("ptcn", headerDom);
			System.out.println("ptcn ["+ptcn+"]");
            
			//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.start
			/*
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			java.util.Date sysDate = new java.util.Date();
			String sysDateStr = sdf.format(sysDate);
			*/
			//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.end
			if(ptcn == null)
			{
				isError = true;
				System.out.println("PTCN number is null");
				errString = itmDBAccessEJB.getErrorString("","NULLPTCNID","");
				return errString;
				
				
			}
			else
			{
				
				sql = "SELECT COUNT(DISTINCT WTD.PTCN) AS COUNT FROM WAVE_TASK WT, WAVE_TASK_DET WTD, DESPATCH D WHERE WT.CANCEL <> 'Y' " 
					  +" AND WT.HOLD_STATUS <> 'Y' AND WTD.STATUS <> 'Y' AND WTD.WAVE_STATUS <> 'C' AND WTD.REF_ID = D.DESP_ID AND D.CONFIRMED <> 'Y' AND WTD.PTCN = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, ptcn );
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					count = rs.getInt("COUNT");
				}
				if( rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(count == 0)
				{
					errString = itmDBAccessEJB.getErrorString("","INVPTCNID","");
					return errString;
				}
				//to check all task confirm.start
				
				
				dataMap = consToPrc.getAllTaskID(ptcn,conn);
				isTasksConfirmed = consToPrc.isAllTaskConfirmed(dataMap,conn);
				
				despId = (String)((ArrayList)dataMap.get("S-DSP")).get(0);
				System.out.println("Despatch ID::::::::::::::: ["+despId+"]"+"isTasksConfirmed"+isTasksConfirmed);
				if(!isTasksConfirmed)
				{
					errString = itmDBAccessEJB.getErrorString("","ALLTASKNOF","");
					isError = true;
					return errString;
				}
				//despId = (String)((ArrayList)dataMap.get("S-DSP")).get(0);
				System.out.println("Despatch ID:::::::::::::8888:: ["+despId+"]");

				isShipExist = consToPrc.isShipmentExist(despId,conn);
				System.out.println("isShipExist ID::::::::::::::: ["+isShipExist+"]");
				if(isShipExist)
				{
					isError = true;
					errString = itmDBAccessEJB.getErrorString("","CONALEXIST","");
					return errString;
				}
				//to check all task confirm.end
				if(isTasksConfirmed && !isShipExist)
				{	
					errString = consToPrc.process(ptcn,conn,xtraParams);
				}	
				
				System.out.println("ERROR STRING:::::::::::::"+errString);
				
				//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.start
				/*
				sql = "SELECT COUNT(*) AS COUNT FROM WAVE_TASK_DET WHERE PTCN = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, ptcn );
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					count = rs.getInt("COUNT");
				}
				if( rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if( count == 0 )
				{	
					isError = true;
					System.out.println("invalide ptcn id");
					errString = itmDBAccessEJB.getErrorString("","INVPTCNID","");
					return errString;			
				}
				else
				{
					HashMap dataMap = getAllTaskID(ptcn,conn);
					boolean isTasksConfirmed = isAllTaskConfirmed(dataMap,conn);
					ArrayList tempList = null;
					if(isTasksConfirmed)
					{
						

						despId = (String)((ArrayList)dataMap.get("S-DSP")).get(0);
						System.out.println("Despatch ID::::::::::::::: ["+despId+"]");

						boolean isShipExist = isShipmentExist(despId,conn);

						if(!isShipExist)
						{
							/*sql = "";
							sql =   "SELECT DISTINCT P.PICK_ORDER, P.LINE_NO__ORD, P.SITE_CODE, P.ITEM_CODE, P.LOC_CODE, P.LOT_NO, " +
									"P.LOT_SL, P.NO_ART, P.QUANTITY , PI.SALE_ORDER , W.WAVE_ID " +
									"FROM PICK_ISS_DET P, WAVE_TASK_DET W , PICK_ORD_HDR PI, WAVE_TASK WI " +
									"WHERE W.REF_ID  = P.PICK_ORDER " +
									"AND PI.WAVE_ID = W.WAVE_ID " +
									"AND W.WAVE_ID = WI.WAVE_ID " +
									"AND WI.PTCN = ? ";*/
							/*sql = "SELECT DISTINCT P.PICK_ORDER, P.LINE_NO__ORD, P.SITE_CODE, P.ITEM_CODE, P.LOC_CODE, " +
									"P.LOT_NO, P.LOT_SL, P.NO_ART, P.QUANTITY , PO.SALE_ORDER, W.WAVE_ID , POD.EXP_LEV " +
									"FROM PICK_ISS_DET P, WAVE_TASK_DET W , PICK_ORD_HDR PO , PICK_ORD_DET POD " +
									"WHERE P.PICK_ORDER = W.REF_ID " +
									"AND PO.PICK_ORDER = P.PICK_ORDER " +
									"AND POD.PICK_ORDER = PO.PICK_ORDER " +
									"AND POD.LINE_NO = P.LINE_NO " +
									"AND W.PTCN = ? ";
							*/
				           /*
							sql1 =  "INSERT INTO DESPATCHDET (DESP_ID, LINE_NO, SORD_NO, LINE_NO__SORD, EXP_LEV, " +
												"ITEM_CODE__ORD, ITEM_CODE, LOT_NO, LOT_SL, QUANTITY__ORD, QUANTITY, LOC_CODE, " +
												"STATUS, CONV__QTY_STDUOM, UNIT__STD, UNIT, QUANTITY__STDUOM, QUANTITY_REAL, " +
												"RATE__STDUOM, PACK_INSTR, NO_ART, SITE_CODE, PACK_QTY, GROSS_WEIGHT, TARE_WEIGHT,  " +
												"NETT_WEIGHT, MFG_DATE, EXP_DATE, SITE_CODE__MFG, RATE__CLG, COST_RATE, LINE_TYPE, " +
												"CONV__RTUOM_STDUOM, PACK_CODE, CUST_ITEM__REF, RETEST_DATE, PART_NO) " +
												"VALUES (?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?) ";
							
							pstmt1 = conn.prepareStatement(sql1);

							/*--Commented by gulzar on 29/12/11
							sqlOrd = "SELECT DISTINCT I.UNIT, I.MFR_PART_NO, S.GROSS_WEIGHT, S.TARE_WEIGHT, S.NET_WEIGHT, " +
												"S.MFG_DATE, S.EXP_DATE, S.SITE_CODE__MFG, S.PACK_CODE, S.RETEST_DATE, S.RATE, " +
												"SD.PACK_INSTR, SD.RATE__CLG, SD.CUST_ITEM__REF, SD.RATE__STDUOM, " +
												"SI.LINE_TYPE, ILP.SHIPPER_SIZE " +
												"FROM ITEM I, STOCK S, SORDER SO, SORDDET SD, SORDITEM SI, ITEM_LOT_PACKSIZE ILP " +
												"WHERE I.ITEM_CODE = ? " +
												"AND S.SITE_CODE = ? " +
												"AND S.LOC_CODE = ? " +
												"AND S.LOT_NO = ? " +
												"AND S.LOT_SL =  ? " +
												"AND S.ITEM_CODE = I.ITEM_CODE " +
												"AND SO.SALE_ORDER= ? " +
												"AND SD.SALE_ORDER = SO.SALE_ORDER " +
												"AND ILP.ITEM_CODE = I.ITEM_CODE " +
												"AND SI.SALE_ORDER = SO.SALE_ORDER " +
												"AND SI.LINE_NO =  ? " +
												"AND SI.EXP_LEV = ? ";
										
							pstmtOrder  = conn.prepareStatement(sqlOrd);
							*/
							/*
							Set itmList = dataMap.keySet();
							Iterator it = itmList.iterator();

							String lineNoSord = "";
							String despLineNo = "";

							//changes made by gulzar on 29/12/11
							String sordItemSql = "";
							String sordDetSql = "";
							String stockSql = "";
							String itemSql = "";
							String itemCodeOrd = "";
							String siteCodeSord = "";
							String sordSql = "";

							double quantityOrd = 0d;
							double grossWtPerArt = 0d;
							double tareWtPerArt = 0d;
							double palletWt = 0d;
							double stkRate = 0d;


							sordItemSql = "SELECT ITEM_CODE__ORD, (CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END - CASE WHEN QTY_DESP IS NULL THEN 0 ELSE QTY_DESP END ) AS QUANTITY__ORD FROM SORDITEM WHERE SALE_ORDER = ? AND LINE_NO = ? AND LINE_TYPE = 'I'  ";
							pstmtSordItem = conn.prepareStatement(sordItemSql);

							sordSql = "SELECT PRICE_LIST FROM SORDER WHERE SALE_ORDER = ? ";
							pstmtSord = conn.prepareStatement(sordSql);

							sordDetSql = "SELECT PACK_INSTR, SITE_CODE, CUST_ITEM__REF, RATE, RATE__STDUOM, RATE__CLG, UNIT, CONV__QTY_STDUOM,UNIT__STD " +
										"FROM SORDDET WHERE SALE_ORDER = ? AND LINE_NO = ? ";
							pstmtSordDet = conn.prepareStatement(sordDetSql);

							stockSql = "SELECT (CASE WHEN GROSS_WT_PER_ART IS NULL THEN 0 ELSE GROSS_WT_PER_ART END) AS GROSS_WT_PER_ART,"
									+ "(CASE WHEN TARE_WT_PER_ART IS NULL THEN 0 ELSE TARE_WT_PER_ART END) AS TARE_WT_PER_ART,"
									+ "(CASE WHEN PALLET_WT IS NULL THEN 0 ELSE PALLET_WT END) AS PALLET_WT, "
									+ "MFG_DATE, EXP_DATE, SITE_CODE__MFG, PACK_CODE, RETEST_DATE, RATE FROM STOCK "
									+ "WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";
							pstmtStock = conn.prepareStatement(stockSql);

							itemSql = "SELECT I.UNIT AS UNIT_STD, I.MFR_PART_NO, ILP.SHIPPER_SIZE FROM ITEM I , ITEM_LOT_PACKSIZE ILP "
									+ "WHERE I.ITEM_CODE = ? AND I.ITEM_CODE = ILP.ITEM_CODE ";
							pstmtItem = conn.prepareStatement(itemSql);

							String issSql = "SELECT LOC_CODE__TO FROM PICK_ISS_DET WHERE PICK_ORDER = ? AND LINE_NO__ORD = ? ";
							pstmtIss = conn.prepareStatement(issSql);

							//End changes by gulzar on 29/12/11

							while( it.hasNext())			
							{
								String key = (String)it.next();
								System.out.println("CURRENT KEY ["+key+"]");

								if(key.equalsIgnoreCase("P-TASK"))
								{
									/*--Commented and changes below by gulzar on 12/27/2011
									sql =	"SELECT P.PICK_ORDER AS ID, P.LINE_NO__ORD, P.SITE_CODE, P.ITEM_CODE,P.LOC_CODE, P.LOC_CODE__TO, " +
											"P.LOT_NO, P.LOT_SL, P.NO_ART, P.QUANTITY , PO.SALE_ORDER, POD.EXP_LEV,PO.WAVE_ID " +
											"FROM PICK_ISS_DET P, PICK_ORD_HDR PO , PICK_ORD_DET POD " +
											"WHERE  PO.PICK_ORDER = P.PICK_ORDER " +
											"AND POD.PICK_ORDER = PO.PICK_ORDER " +
											"AND POD.LINE_NO = P.LINE_NO " +
											"AND PO.PICK_ORDER = ?";
									sql =	"SELECT DISTINCT POD.PICK_ORDER AS ID, POD.LINE_NO AS LINE_NO__ORD, POD.SITE_CODE, " +
											"POD.ITEM_CODE, POD.LOC_CODE, P.LOC_CODE__TO, POD.LOT_NO, POD.LOT_SL, " +
											"POD.NO_ART, POD.QUANTITY , PO.SALE_ORDER, POD.EXP_LEV, PO.WAVE_ID, POD.LINE_NO__SORD " +
											"FROM PICK_ISS_DET P, PICK_ORD_HDR PO , PICK_ORD_DET POD " +
											"WHERE  PO.PICK_ORDER = P.PICK_ORDER " +
											"AND POD.PICK_ORDER = PO.PICK_ORDER " +
											"AND POD.LINE_NO = P.LINE_NO__ORD " +
											"AND PO.PICK_ORDER = ?";

									*/
			                    	/*
									sql =	"SELECT POD.PICK_ORDER AS ID, POD.LINE_NO AS LINE_NO__ORD, POD.SITE_CODE, " +
											"POD.ITEM_CODE, POD.LOC_CODE, POD.LOT_NO, POD.LOT_SL, " +
											"POD.NO_ART, POD.QUANTITY , PO.SALE_ORDER, POD.EXP_LEV, PO.WAVE_ID, POD.LINE_NO__SORD " +
											"FROM PICK_ORD_HDR PO , PICK_ORD_DET POD " +
											"WHERE POD.PICK_ORDER = PO.PICK_ORDER " +
											"AND POD.PICK_ORDER = ? ";
									
									//End changes by gulzar on 12/27/2011
									
								}
								else if(key.equalsIgnoreCase("R-TASK"))
								{
									sql =	"SELECT P.REPL_ORDER AS ID, P.LINE_NO__ORD, P.SITE_CODE, P.ITEM_CODE,P.LOC_CODE, P.LOC_CODE__TO, " +
											"P.LOT_NO, P.LOT_SL, P.NO_ART, P.QUANTITY , PO.SALE_ORDER, POD.EXP_LEV,PO.WAVE_ID, POD.LINE_NO__SORD " +
											"FROM REPL_ISS_DET P, REPL_ORD_HDR PO , REPL_ORD_DET POD " +
											"WHERE  PO.REPL_ORDER = P.REPL_ORDER " +
											"AND POD.REPL_ORDER = PO.REPL_ORDER " +
											//"AND POD.LINE_NO = P.LINE_NO " +//Commented by Gulzar on 12/27/2011
											"AND POD.LINE_NO = P.LINE_NO__ORD " +//Added by Gulzar on 12/27/2011
											"AND PO.ORDER_TYPE = 'D' " +
											"AND PO.REPL_ORDER = ?";
								}
								else
								{
									continue;
								}

								System.out.println("CURRENT SQL ["+sql+"]");

								tempList = (ArrayList)dataMap.get(key);
								pstmt = conn.prepareStatement(sql);

								int ordLen = tempList.size();
								if ( ordLen > 0 )
								{
									for(int i = 0;i < ordLen; i++)
									{
										String 	refIdCtr = (String)tempList.get(i);	
										System.out.println("CURRENT REF ID ["+refIdCtr+"]");
										pstmt.setString(1,refIdCtr);
										rs = pstmt.executeQuery(); 
										System.out.println("after excute query---------------");
										while(rs.next())
										{
											System.out.println("** inside while block *** ");
											pickOrder = rs.getString("ID");
											lineNoOrd = rs.getInt("LINE_NO__ORD");
											siteCode = rs.getString("SITE_CODE");
											itemCode = rs.getString("ITEM_CODE");
											locCode = rs.getString("LOC_CODE");

											lotNo = rs.getString("LOT_NO");
											lotSl = rs.getString("LOT_SL");
											noArt = rs.getInt("NO_ART");
											quantity = rs.getDouble("QUANTITY");
											saleOrder = rs.getString("SALE_ORDER");		
											waveId = rs.getString("WAVE_ID");
											expLev = rs.getString("EXP_LEV");
											lineNoSord = rs.getString("LINE_NO__SORD");//Gulzar on 12/28/2011
			
											if( key.equalsIgnoreCase("R-TASK"))
											{
												locCodeTo = rs.getString("LOC_CODE__TO");
											}
											else if ( key.equalsIgnoreCase("P-TASK") )
											{
												//Selecting loc_code__to from pick_iss_det
												pstmtIss.setString(1, pickOrder);
												//Changed By Pragyan
												//pstmtIss.setString(2, lineNoSord);
												pstmtIss.setInt(2, lineNoOrd);
												
												rsIss = pstmtIss.executeQuery();
												
												if ( rsIss.next() )
												{
													locCodeTo = rsIss.getString("LOC_CODE__TO");
												}
												rsIss.close(); rsIss = null;
												pstmtIss.clearParameters();
											}
											
											System.out.println("pick/repl order ["+pickOrder.trim()+"] site code ["+siteCode.trim()+"] item code ["+itemCode.trim()+"] loc code ["+locCode.trim()+"]");
											System.out.println("lot no ["+lotNo.trim()+"] lot sl["+lotSl.trim()+"] no art ["+noArt+"] quantity["+quantity+"] sale_order["+saleOrder.trim()+"]");
																	
											//Changes by gulzar on 29/12/11
											//Form sorditem
											pstmtSordItem.setString(1, saleOrder);
											
											//Changed By Pragyan this needs to be change after solving design bug in pick order and pick issue has line_no__sord number(3) to char(3).start
											if(lineNoSord.length() == 2)
											{
												pstmtSordItem.setString(2, " "+lineNoSord);
											}
											else if(lineNoSord.length() == 1)
											{
												pstmtSordItem.setString(2, "  "+lineNoSord);
												
											}
											else
											{
												pstmtSordItem.setString(2, lineNoSord);
											}
											//Changed By Pragyan this needs to be change after solving design bug in pick order and pick issue has line_no__sord number(3) to char(3).end
											
											rsSordItem = pstmtSordItem.executeQuery();
											if ( rsSordItem.next() )
											{
												itemCodeOrd = checkNull(rsSordItem.getString("ITEM_CODE__ORD"));
												quantityOrd = rsSordItem.getDouble("QUANTITY__ORD");
											}
											rsSordItem.close();rsSordItem = null;
											pstmtSordItem.clearParameters();
											
											//Form sorddet
											pstmtSordDet.setString(1, saleOrder);
											//Changed By Pragyan this needs to be change after solving design bug in pick order and pick issue has line_no__sord number(3) to char(3).start
											if(lineNoSord.length() == 2)
											{
												pstmtSordDet.setString(2, " "+lineNoSord);
											}
											else if(lineNoSord.length() == 1)
											{
												pstmtSordDet.setString(2, "  "+lineNoSord);
											
											}
											else
											{
												pstmtSordDet.setString(2, lineNoSord);
											}
											//Changed By Pragyan this needs to be change after solving design bug in pick order and pick issue has line_no__sord number(3) to char(3).start
											rsSordDet = pstmtSordDet.executeQuery();
											if ( rsSordDet.next() )
											{
												packInstr = checkNull(rsSordDet.getString("PACK_INSTR"));
												siteCodeSord = checkNull(rsSordDet.getString("SITE_CODE"));
												custItemRef = checkNull(rsSordDet.getString("CUST_ITEM__REF"));
												rateStduom = checkNull(rsSordDet.getString("RATE__STDUOM"));
												rateClg = checkNull(rsSordDet.getString("RATE__CLG"));
												unit = checkNull(rsSordDet.getString("UNIT"));
												convQtyStduom = rsSordDet.getDouble("CONV__QTY_STDUOM");
												unitStd = checkNull(rsSordDet.getString("UNIT__STD"));
												rate = rsSordDet.getDouble("RATE");//Gulzar on 5/14/2012
											}
											rsSordDet.close();rsSordDet = null;
											pstmtSordDet.clearParameters();
											System.out.println("packInstr["+packInstr+"] siteCodeSord["+siteCodeSord+"] custItemRef ["+custItemRef+"] rateStduom["+rateStduom+"] rateClg["+rateClg+"]");
											System.out.println("unit =["+unit+"]");
											System.out.println("convQtyStduom =["+convQtyStduom+"]");
											System.out.println("unitStd =["+unitStd+"]");
											System.out.println("rate from sorddet =["+rate+"]");
											String priceList = "";

											if ( rate == 0 )
											{
												pstmtSord.setString(1,saleOrder);
												rsSord = pstmtSord.executeQuery();
												if ( rsSord.next() )
												{
													priceList = checkNull(rsSord.getString("PRICE_LIST"));
												}
												rsSord.close(); rsSord = null;
												pstmtSord.clearParameters();
												if ( priceList.trim().length() > 0 )
												{
													rate = disCommon.pickRate( priceList, sysDateStr, itemCode, " ", "L", quantity, conn );
												}
											}
											//Enc changes by Gulzar on 5/14/2012
											//Form stock
											pstmtStock.setString(1, itemCode);
											pstmtStock.setString(2, siteCode);
											pstmtStock.setString(3, locCodeTo);
											pstmtStock.setString(4, lotNo);
											pstmtStock.setString(5, lotSl);
											
											rsStock = pstmtStock.executeQuery();
											if ( rsStock.next() )
											{
												System.out.println("unitStd["+unitStd+"]");
												//MFG_DATE, EXP_DATE, SITE_CODE__MFG, PACK_CODE, RETEST_DATE, RATE 
												grossWtPerArt = rsStock.getDouble("GROSS_WT_PER_ART");
												tareWtPerArt = rsStock.getDouble("TARE_WT_PER_ART");
												palletWt = rsStock.getDouble("PALLET_WT");
												mfgDate = rsStock.getTimestamp("MFG_DATE");
												expDate = rsStock.getTimestamp("EXP_DATE");
												siteCodeMfg = checkNull(rsStock.getString("SITE_CODE__MFG"));
												packCode = checkNull(rsStock.getString("PACK_CODE"));
												retestDate = rsStock.getTimestamp("RETEST_DATE");
												stkRate = rsStock.getDouble("RATE");//Gulzar on 5/14/2012
												System.out.println("INSIDE STOCKS DATA>>>>>>>>>>>>>>>>>>>>>>>>>>["+grossWtPerArt+"]");
											}
											
											
											rsStock.close();rsStock = null;
											pstmtStock.clearParameters();

											grossWeight = (grossWtPerArt * noArt);
											tareWeight = (tareWtPerArt * noArt);
											netWeight  = grossWeight -  tareWeight	;

											//Form item and item_lot_packsize
											pstmtItem.setString(1, itemCode);
											rsItem = pstmtItem.executeQuery();
											if ( rsItem.next() )
											{
												//UNIT_STD, I.MFR_PART_NO, ILP.SHIPPER_SIZE
												mbaseUnit = checkNull(rsItem.getString("UNIT_STD"));
												partNo = checkNull(rsItem.getString("MFR_PART_NO"));
												shipperSize = rsItem.getDouble("SHIPPER_SIZE");
											}
											rsItem.close();rsItem = null;
											pstmtItem.clearParameters();							
																					
											if(unitStd != "")
											{
												mbaseUnit = unitStd;
											}
											//End changes by gulzar on 29/12/11
											
											/*												
											pstmtOrder.setString(1, itemCode);
											pstmtOrder.setString(2, siteCode);
											//pstmtOrder.setString(3, locCode);//Commented by Gulzar on 12/27/2011
											pstmtOrder.setString(3, locCodeTo);//Changes added by Gulzar on 12/27/2011
											pstmtOrder.setString(4, lotNo);
											pstmtOrder.setString(5, lotSl);
											pstmtOrder.setString(6, saleOrder);
											pstmtOrder.setInt(7, lineNoOrd);
											pstmtOrder.setString(8, expLev);
											rs1 = pstmtOrder.executeQuery();

											if(rs1.next())
											{
												mbaseUnit = rs1.getString("UNIT");
												partNo = rs1.getString("MFR_PART_NO");
												grossWeight = rs1.getInt("GROSS_WEIGHT");
												tareWeight = rs1.getInt("TARE_WEIGHT");
												netWeight = rs1.getInt("NET_WEIGHT");
												mfgDate = rs1.getTimestamp("MFG_DATE");
												expDate = rs1.getTimestamp("EXP_DATE");
												siteCodeMfg = rs1.getString("SITE_CODE__MFG");
												packCode = rs1.getString("PACK_CODE");
												retestDate = rs1.getTimestamp("RETEST_DATE");
												rate = rs1.getFloat("RATE");
												packInstr = rs1.getString("PACK_INSTR");
												rateClg = rs1.getString("RATE__CLG");
												custItemRef = rs1.getString("CUST_ITEM__REF");
												rateStduom = rs1.getString("RATE__STDUOM");
												lineType = rs1.getString("LINE_TYPE");
												shipperSize = rs1.getLong("SHIPPER_SIZE");								
											}
											if(rs1 !=null)
											{
												rs1.close();
												rs1 = null;
											}
											
											pstmtOrder.clearParameters();
											// calculating weight
											netWeight = netWeight*noArt;
											System.out.println("--netWeight-->"+netWeight);
											grossWeight = grossWeight*noArt;
											System.out.println("--grossWeight-->"+grossWeight);
											tareWeight =tareWeight*noArt;
											System.out.println("--tareWeight-->"+tareWeight);		
											
											//*********Calculating conversion quantity start
											meffQty =0;
											*/
				                            /*
											if(qtyStduom == 0)
											{
												if(!unit.equalsIgnoreCase(mbaseUnit))
												{
													if(convQtyStduom != 0)
													{
														convFact = convQtyStduom;
													}
													ArrayList convQtyList = disCommon.getConvQuantityFact(unit, mbaseUnit, itemCode, quantity, convFact,conn);
													System.out.println("convQtyList = "+convQtyList);
													meffQty = Double.parseDouble(convQtyList.get(1).toString());
													System.out.println("meffQty ["+meffQty+"]");
													
													if(meffQty == -999999999)
													{
														meffQty = 0;								
													}
													if (convQtyStduom == 0)
													{
														convQtyStduom= Double.parseDouble(convQtyList.get(0).toString());
													}
												}
												else
												{
													meffQty = quantity ;
													if (convQtyStduom == 0) 
													{
														convQtyStduom=1;
													}
													
												}
												qtyStduom = meffQty;
											}
											//*********Calculating conversion quantity end

											//*********Calculating reverse conversion quantity start
											if(qtyStduom1 == 0)
											{
												if(!unit.equalsIgnoreCase(mbaseUnit))
												{
													if(conRtuStduom != 0)
													{
														convFact = conRtuStduom;
													}
													ArrayList convQtyList = disCommon.getConvQuantityFact(mbaseUnit, unit, itemCode, quantity, convFact,conn);
													System.out.println("convQtyList = "+convQtyList);
																			
													if (conRtuStduom == 0)
													{
														conRtuStduom= Double.parseDouble(convQtyList.get(0).toString());
													}
												}
												else
												{
													
													if (conRtuStduom == 0) 
													{
														conRtuStduom=1;
													}
													
												}
												
											}
											lineNo++;
											
											//Changes by gulzar on 12/28/2011
											despLineNo = "   " + lineNo;
											despLineNo = despLineNo.substring( despLineNo.length()-3 );

											lineNoSord = "   " + lineNoSord;
											lineNoSord = lineNoSord.substring( lineNoSord.length()-3 );
											//End changes by gulzar on 12/28/2011

											//DESP_ID, LINE_NO, SORD_NO, LINE_NO__SORD, EXP_LEV, " +
											pstmt1.setString(1, despId);
											//pstmt1.setInt(2, lineNo);//Gulzar on 12/28/2011
											pstmt1.setString(2, despLineNo);//Gulzar on 12/28/2011
											pstmt1.setString(3, saleOrder.trim());
											//pstmt1.setInt(4, lineNo);//Commented by gulzar on 12/28/2011
											pstmt1.setString(4, lineNoSord);//Added by gulzar on 12/28/2011

											pstmt1.setString(5, expLev);
											
											//"ITEM_CODE__ORD, ITEM_CODE, LOT_NO, LOT_SL, QUANTITY__ORD, QUANTITY, LOC_CODE, " +
											if(itemCodeOrd != null && itemCodeOrd.length() > 0)
											{
												pstmt1.setString(6, itemCodeOrd.trim());
											}
											else
											{
												pstmt1.setString(6, itemCode);
											}
													
											pstmt1.setString(7, itemCode.trim());
											pstmt1.setString(8, lotNo.trim());
											pstmt1.setString(9, lotSl.trim());
											pstmt1.setDouble(10, quantityOrd);
											pstmt1.setDouble(11, quantity);
											pstmt1.setString(12, checkNull(locCodeTo));
											
											//Changed By Pragyan to set " " one blank character in status as per manoharan sir
											//"STATUS, CONV__QTY_STDUOM, UNIT__STD, UNIT, QUANTITY_STDUOM, QUANTITY_REAL, " +
											//pstmt1.setString(13,"0");
											pstmt1.setString(13," ");
											pstmt1.setDouble(14 , convQtyStduom );
											pstmt1.setString(15 , mbaseUnit);
											pstmt1.setString(16 , unit);
											pstmt1.setDouble(17 , qtyStduom );
											pstmt1.setDouble(18 , quantity);
											
											//"RATE_STDUOM, PACK_INSTR, NO_ART, SITE_CODE, PACK_QTY, GROSS_WEIGHT, TARE_WEIGHT,  " +
											pstmt1.setDouble(19 , rate );
											pstmt1.setString(20 , checkNull(packInstr) );
											pstmt1.setDouble( 21 , noArt);
											pstmt1.setString( 22 , checkNull(siteCode));
											pstmt1.setDouble( 23 , shipperSize); // pack_qty is shipper_size 
											pstmt1.setDouble( 24 , grossWeight );
											pstmt1.setDouble( 25 , tareWeight );
											
											//"NETT_WEIGHT, MFG_DATE, EXP_DATE, SITE_CODE__MFG, RATE__CLG, COST_RATE, LINE_TYPE, " +
											pstmt1.setDouble( 26 , netWeight);
											pstmt1.setTimestamp( 27 , mfgDate );
											pstmt1.setTimestamp( 28 , expDate );
											pstmt1.setString( 29 , checkNull(siteCodeMfg));
											pstmt1.setString( 30 , checkNull(rateClg));
											pstmt1.setDouble( 31 , stkRate);
											pstmt1.setString( 32 , checkNull(lineType));
											
											//"CONV_RTUOM_STDUOM, PACK_CODE, CUST_ITEM__REF, RETEST_DATE, PART_NO) " + // PALLET_WT Hided 
											pstmt1.setDouble( 33 , conRtuStduom);
											pstmt1.setString( 34 , checkNull(packCode));
											//pstmt1.setString( 34 , pallet_wt);
											pstmt1.setString( 35, checkNull(custItemRef));
											pstmt1.setTimestamp( 36 ,retestDate);
											pstmt1.setString( 37 , checkNull(partNo));
																	
											pstmt1.addBatch();
											pstmt1.clearParameters();
											System.out.println("lOOPPPPPPPPPPPPPPPPPPPPPP111");

											itemCodeOrd = ""; quantityOrd = 0d; itemCode = ""; lotNo = ""; lotSl = ""; quantity = 0d;
											locCodeTo = ""; noArt = 0d; mbaseUnit = ""; packInstr = ""; siteCode = ""; qtyStduom = 0d;
											rate = 0d; convQtyStduom = 0d; shipperSize = 0d; grossWeight = 0d; tareWeight = 0d;
											netWeight = 0d; mfgDate = null; expDate = null; siteCodeMfg = ""; rateClg = ""; 
											conRtuStduom = 0d; packCode = ""; custItemRef = ""; retestDate = null; partNo = "";
							
										}//While loop
										System.out.println("lOOPPPPPPPPPPPPPPPPPPPPPP2222");
										pstmt.clearParameters();
									}//for(int i = 0;i < ordLen; i++)
								}
							}

							System.out.println("BEFORE BATCH UPDATE================");
							pstmt1.executeBatch();
							System.out.println("AFTER BATCH UPDATE================");
							pstmt1.clearBatch();	

							if(pstmtOrder != null)
							{
								pstmtOrder.close();
								pstmtOrder = null;
							}

							if(pstmt1 != null)
							{
								pstmt1.close();
								pstmt1 = null;
							}	
							
						}//if(!isShipExist)
						else
						{
							isError = true;
							System.out.println("SHIPMENT ALREADY CONSOLIDATED");
							errString = itmDBAccessEJB.getErrorString("","CONALEXIST","");
							return errString;
						}
							
					}//if(isTasksConfirmed)
					else
					{
						isError = true;
						System.out.println("ALL TASK NOT COFIRMED SYSTEM SHOULD NOT ALLOW CONSOLIDATED TO DOCK");
						errString = itmDBAccessEJB.getErrorString("","ALLTASKNOF","");
						return errString;
					}
				}
				*/
				
				//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.end	
			}
				
		}
		//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.start
		/*
		catch(BatchUpdateException b)
        {
			isError = true;
			System.out.println("BatchUpdateException :" + b);
			b.printStackTrace();
			errString = b.getMessage();
			try
			{
				if(conn != null)
				{
					conn.rollback();
				}
			}
			catch(Exception e1)
			{
				e1.printStackTrace();
			}
			return errString;
			
        }
		catch(SQLException se)
		{
			isError = true;
			System.out.println("SQLException :" + se);
			se.printStackTrace();
			System.out.println("error ****"+se.getMessage());
			errString = se.getMessage();
			try
			{
				if(conn != null)
				{
					conn.rollback();
				}
			}
			catch(SQLException e1)
			{
				se = e1;
			}
			return errString;
		}
		*/
		catch(Exception e)
		{
			isError = true;
			System.out.println("Exception :" + e);
			errString = e.getMessage();
			e.printStackTrace();
			System.out.println("error-->"+e.getMessage());
			
			
			try
			{
				if(conn != null)
				{
					conn.rollback();
				}
			}
			catch(Exception e1)
			{
				e = e1;

			}
			
			//return errString ;
			//Changed by Rohan on 29-03-13 for throwing exception
			throw new ITMException(e);
		}			
		finally
		{
			try
			{
				
				if(conn != null)
				{	

					if ( !isError )
					{
						System.out.println("COMMITING the changes......");
						conn.commit();
						errString = itmDBAccessEJB.getErrorString("","PRCSUCCESS","");
					}
					else
					{
						System.out.println("ROLLBACK the changes......");
						conn.rollback();
						
						//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.start
						/*
						System.out.println("Error String Coming from the validation......["+errString+"]");
						if(errString != null && errString.length() > 0 && errString.indexOf("error") != -1)
						{
							System.out.println("Error String Coming from the validation......");
						}
						else
						{
							errString = itmDBAccessEJB.getErrorString("","PRCFAILED","");
						}
						*/
					}
					//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.start
					/*
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
					
					if(pstmt1 != null )
					{
						pstmt1.close();
						pstmt1 = null;
					}

					if( rsSordItem != null )
					{
						rsSordItem.close();
						rsSordItem = null;
					}
					if( rsSordDet != null )
					{
						rsSordDet.close();
						rsSordDet = null;
					}
					if( rsStock != null )
					{
						rsStock.close();
						rsStock = null;
					}
					if( rsItem != null )
					{
						rsItem.close();
						rsItem = null;
					}
					if( rsIss != null )
					{
						rsIss.close();
						rsIss = null;
					}
					if( pstmtSordItem != null )
					{
						pstmtSordItem.close();
						pstmtSordItem = null;
					}
					if( pstmtSordDet != null )
					{
						pstmtSordDet.close();
						pstmtSordDet = null;
					}
					if( pstmtStock != null )
					{
						pstmtStock.close();
						pstmtStock = null;
					}
					if( pstmtItem != null )
					{
						pstmtItem.close();
						pstmtItem = null;
					}
					if( pstmtIss != null )
					{
						pstmtIss.close();
						pstmtIss = null;
					}
					if( pstmtSord != null )
					{
						pstmtSord.close();
						pstmtSord = null;
					}//pstmtSord
					*/
					//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.end
					conn.close();
					conn = null;
					
					if( rs != null)
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
				}
				System.out.println("errString ["+errString +"]");

				
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				System.out.println("error =>"+e.getMessage());
				//Changed by Rohan on 29-03-13 for throwing exception
				throw new ITMException(e);				
			}
			
			
			/*if(!isError)
			{
				
				errString = itmDBAccessEJB.getErrorString("","VTCONPARM","");
				return errString;
			}
			else
			{
		
				if(errString != null && errString.length() > 0 && errString.indexOf("error") != -1)
				{
					errString = errString;
				}
				else
				{
					errString = itmDBAccessEJB.getErrorString("","VTNOTCONFM","");
				}
				return errString;
			}*/

		}

		return errString;

	}
	//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.start
	/*
	private String checkNull( String input )
	{
		if ( input == null )
		{
			input = "";
		}
		return input;
	}	

	private HashMap getAllTaskID(String PTCN, Connection conn)throws Exception
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;	
		HashMap hasMap = new HashMap();
		ArrayList tempList = null; 
		ArrayList arrayList = null; 
		String refSer = "";
		String refId = "";

		
		try
		{
			sql = "SELECT REF_SER, REF_ID FROM WAVE_TASK_DET WHERE PTCN = ?";
			System.out.println("sql ["+sql+"]");
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, PTCN);
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				refSer = rs.getString("REF_SER");
				refId =  rs.getString("REF_ID");

				if(hasMap.containsKey(refSer))
				{
					tempList = (ArrayList)hasMap.get(refSer.trim());
					tempList.add(refId);
					hasMap.put(refSer,tempList);
				}
				else
				{
					arrayList = new ArrayList();
					arrayList.add(refId);
					hasMap.put(refSer.trim(),arrayList);
				}

			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			try
			{
				if( rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				
			}
			catch (Exception e)
			{
			}
		}

		return 	hasMap;
	}

	private boolean isAllTaskConfirmed(HashMap taskMap , Connection conn)throws Exception
	{
		String sql = "";
		String cofirmed  = "";
		boolean isConfirmed  = true;		
		PreparedStatement pstmt = null;
		ResultSet rs = null;	

		try
		{
			Set itmList = taskMap.keySet();
			Iterator it = itmList.iterator();

			while( it.hasNext())			
			{
				String key = (String)it.next();

				System.out.println("Key  ["+key+"]");
				if(key.equalsIgnoreCase("P-TASK"))
				{
					sql = "SELECT CONFIRMED FROM PICK_ISS_HDR WHERE PICK_ORDER = ?";
				}
				else if(key.equalsIgnoreCase("R-TASK"))
				{
					sql = "SELECT CONFIRMED FROM REPL_ISS_HDR WHERE REPL_ORDER = ?";
				}

				else if(key.equalsIgnoreCase("M-PACK"))
				{
					sql = "SELECT CONFIRMED FROM PACK_HDR WHERE TRAN_ID = ?";
				}
				else
				{
					continue;
				}

				
				ArrayList tempList = (ArrayList)taskMap.get(key);
				int ordLen = tempList.size();
				pstmt = conn.prepareStatement(sql);

				for(int i = 0;i < ordLen; i++)
				{
					String 	refId = (String)tempList.get(i);				
					pstmt.setString(1,refId);
					rs = pstmt.executeQuery();

					if(rs.next())
					{
						String conf = rs.getString("CONFIRMED");

						if(conf != null && conf.equalsIgnoreCase("N"))
						{
							return isConfirmed = false;
						}
					}
					else
					{
						return isConfirmed = false;
					}

					pstmt.clearParameters();
				}
					
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			try
			{
				if( rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				
			}
			catch (Exception e)
			{
			}
		}

		return isConfirmed;
	}


	private boolean isShipmentExist(String despId , Connection conn)throws Exception
	{
		String sql = "";
		boolean isDespExist  = false;		
		PreparedStatement pstmt = null;
		ResultSet rs = null;	

		try
		{
				
			sql = "SELECT COUNT(*) AS COUNT FROM DESPATCHDET WHERE DESP_ID = ?";
						
			
			pstmt = conn.prepareStatement(sql);	
			pstmt.setString(1,despId);
			rs = pstmt.executeQuery();

			if(rs.next())
			{
				int count = rs.getInt(1);
				if(count >= 1)
				{
					return isDespExist = true;
				}
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			try
			{
				if( rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				
			}
			catch (Exception e)
			{
			}
		}

		return isDespExist;
	}
	*/
	//Changed by Rohan on 29-03-13 for calling ConsolidatDoc from ConsolidatToDocPrc.end

}
