/*
 * CaseTransferPos.java
 * Developer - Mahesh Patidar
 * Date - 23/08/12 
 */

package ibase.webitm.ejb.wms;

import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@javax.ejb.Stateless
public class CaseTransferPos extends ValidatorEJB implements CaseTransferPosLocal, CaseTransferPosRemote
{
	public String postSave()throws RemoteException,ITMException
	{
		return "";
	}
	public String postSave(String domString, String tranId,String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		String errString = "";
		String  sSQL="";
		String palletNo = "";
		String palletNoNew = "";
        String cartonNo = "";
        String ptcn = "";
        String pickOrder = "";
        String despId = "";
        String itemCode = "";
        String lotNo = "";
        String lotSl = "";
        
		int ctr = 0;
		int count = 0;
		int lineNo = 0;
		String despLineNo = "";
		NodeList parentNodeList = null;
		Node parentNode = null;
		Document dom = null;
		PreparedStatement pstmt = null,pstmt1 = null;
        ResultSet rs = null ;
        HashMap<String, Number> map = null;
		// 15/10/12 manoharan get the old despatch line detail
		String despLineOld = "0", sordNo = "", lineNoSord = "", expLev = "", itemCodeOrd = "", locCode = "", detStatus = "", unitStd = "";
		String unit = "", packInstr = "", siteCode = "", siteCodeMfg = "", lineType = "", packCode = "", custItemRef = "",partNo = "";
		int noArtOld = 0;
		java.sql.Timestamp 	mfgDate = null, expDate = null, restetDate = null;
		double qtyOrd = 0, quantityOld = 0, convQtyStduom = 0, quantityStduom = 0, quantityReal = 0, rateStduom = 0, packQty = 0, grossWeightOld = 0;
		double tareWeightOld = 0, netWeightOld = 0, rateClg = 0, costRate = 0, convRateStduom = 0,quantityOrd = 0;
		double caseQty = 0, caseGrossWeight = 0, caseNetWeight = 0, caseTareWeight = 0, caseQtyStduom = 0;
		//changed by sankara on 06/JUN/14 for case transfer compareing quantity start.
		DistCommon disCommon = new DistCommon ();  
		String mbaseUnit = "";
		double meffQty = 0, qtyStduom = 0,qtyStduom1= 0, conRtuStduom = 0, convFact = 1, quantity = 0, convertQty = 0.0;
        try
		{
        	System.out.println("tran id in post["+tranId+"] saveString is in post save ["+domString+"]");
			conn.setAutoCommit(false);

			GenericUtility genericUtility = GenericUtility.getInstance();
			System.out.println("STRING{"+domString+"}");
			
			dom = genericUtility.parseString(domString); 
			NodeList detail3List = dom.getElementsByTagName("Detail3");
			if(detail3List != null && detail3List.getLength() > 0)
			{

				palletNo = genericUtility.getColumnValueFromNode("pallet_no__fr", dom.getElementsByTagName("Detail3").item(0));
				palletNoNew = genericUtility.getColumnValueFromNode("pallet_no__to", dom.getElementsByTagName("Detail3").item(0));
				
				 //PICK_ORDER             
				 //LINE_NO__PICK          
				 //DESP_ID                
				 //LINE_NO__DESP          
				 //LINE_NO__DESP_NEW      
				 //ITEM_CODE              
				 //LOT_NO                 
				 //LOT_SL                 
				 
				System.out.println("PALLET NO["+palletNo+"] NEW PALLET NO["+palletNoNew+"]");
				parentNodeList = dom.getElementsByTagName("Detail2");
				for(ctr = 0; ctr < parentNodeList.getLength(); ctr++)
				{
					parentNode = parentNodeList.item(ctr);
					cartonNo = genericUtility.getColumnValueFromNode("carton_no", parentNode);
					pickOrder = genericUtility.getColumnValueFromNode("pick_order", parentNode);
					itemCode = genericUtility.getColumnValueFromNode("item_code", parentNode);
					lotNo = genericUtility.getColumnValueFromNode("lot_no", parentNode);
					lotSl = genericUtility.getColumnValueFromNode("lot_sl", parentNode);
					String palletlineNo = genericUtility.getColumnValueFromNode("line_no", parentNode);
					
					sSQL = "UPDATE CARTON_MASTER SET PALLET_NO = ? WHERE CARTON_NO = ? ";
					pstmt = conn.prepareStatement(sSQL);
					pstmt.setString(1, palletNoNew);
					pstmt.setString(2, cartonNo);
					pstmt.executeUpdate();	
					pstmt.close();
					pstmt = null;
					
					
					sSQL = "UPDATE PALLET_NO SET STATUS = ? WHERE PALLET_NO = ? ";
					pstmt = conn.prepareStatement(sSQL);
					pstmt.setString(1, "U");
					pstmt.setString(2, palletNoNew);
					pstmt.executeUpdate();	
					pstmt.close();
					pstmt = null;
					
					sSQL = "SELECT PTCN FROM WAVE_TASK_DET WHERE REF_ID = ?";
					pstmt = conn.prepareStatement(sSQL);
					pstmt.setString(1,pickOrder);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						ptcn = rs.getString("PTCN");
					}
					pstmt.close();
					pstmt = null;									 
					rs.close();
					rs = null;

					sSQL = "SELECT REF_ID FROM WAVE_TASK_DET WHERE PTCN = ? AND REF_SER = 'S-DSP'";
					pstmt = conn.prepareStatement(sSQL);
					pstmt.setString(1,ptcn);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						despId = rs.getString("REF_ID");
					}
					pstmt.close();
					pstmt = null;									 
					rs.close();
					rs = null;

					System.out.println("Calling Case trasfer sql check");
					////////// 15/10/12 manoharan get the old despatch line info ///////////////////////////////////////////////////
					sSQL =  "SELECT  LINE_NO, SORD_NO, LINE_NO__SORD, EXP_LEV, " +
					"ITEM_CODE__ORD, 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  " +
					" FROM  DESPATCHDET WHERE DESP_ID = ? AND ITEM_CODE = ? AND LOT_SL = ? AND LOT_NO = ? AND PALLET_NO = ?";
					pstmt = conn.prepareStatement(sSQL);
					pstmt.setString(1,despId);
					pstmt.setString(2,itemCode);
					pstmt.setString(3,lotSl);
					pstmt.setString(4,lotNo);
					pstmt.setString(5,palletNo);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						despLineOld = rs.getString("LINE_NO");
						sordNo = rs.getString("SORD_NO");
						lineNoSord = rs.getString("LINE_NO__SORD");
						expLev = rs.getString("EXP_LEV");
						itemCodeOrd = rs.getString("ITEM_CODE__ORD");
						qtyOrd = rs.getDouble("QUANTITY__ORD");
						quantityOld = rs.getDouble("QUANTITY");
						locCode = rs.getString("LOC_CODE");
						detStatus = rs.getString("STATUS");
						convQtyStduom = rs.getDouble("CONV__QTY_STDUOM");
						unitStd = rs.getString("UNIT__STD");
						unit = rs.getString("UNIT");
						quantityStduom = rs.getDouble("QUANTITY__STDUOM");
						quantityReal = rs.getDouble("QUANTITY_REAL");
						rateStduom = rs.getDouble("RATE__STDUOM");
						packInstr = rs.getString("PACK_INSTR");
						noArtOld = rs.getInt("NO_ART");
						siteCode = rs.getString("SITE_CODE");
						packQty = rs.getDouble("PACK_QTY");
						grossWeightOld = rs.getDouble("GROSS_WEIGHT");
						tareWeightOld = rs.getDouble("TARE_WEIGHT");
						netWeightOld = rs.getDouble("NETT_WEIGHT");
						mfgDate = rs.getTimestamp("MFG_DATE");
						expDate = rs.getTimestamp("EXP_DATE");
						siteCodeMfg = rs.getString("SITE_CODE__MFG");
						rateClg = rs.getDouble("RATE__CLG");
						costRate = rs.getDouble("COST_RATE");
						lineType = rs.getString("LINE_TYPE");
						convRateStduom = rs.getDouble("CONV__RTUOM_STDUOM");
						packCode = rs.getString("PACK_CODE");
						custItemRef = rs.getString("CUST_ITEM__REF");
						restetDate = rs.getTimestamp("RETEST_DATE");
						partNo = rs.getString("PART_NO");
						caseQty = packQty;
						//changed by sankara on 06/JUN/14 for case transfer compareing case quantity start.
						mbaseUnit = unitStd;			
						if(quantityStduom == 0)
						{											
							if(!unit.equalsIgnoreCase(mbaseUnit))											
							{
								if(convQtyStduom != 0)
								{
									convFact = convQtyStduom;
								}

								ArrayList convQtyList = disCommon.getConvQuantityFact(unit, mbaseUnit, itemCode, caseQty, 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 = caseQty ;
								if (convQtyStduom == 0) 
								{
									convQtyStduom=1;
								}

							}
							qtyStduom = meffQty;
						}

						if(qtyStduom1 == 0)
						{
							if(!unit.equalsIgnoreCase(mbaseUnit))											
							{
								convFact = 0;						
								ArrayList convQtyList = disCommon.getConvQuantityFact(mbaseUnit, unit, itemCode, caseQty, convFact,conn);												
								System.out.println("convQtyList = "+convQtyList);				
								caseQty = Double.parseDouble(convQtyList.get(1).toString());												
								if(caseQty == -999999999)
								{
									caseQty = 0;								
								}
							}

						}
						//changed by sankara on 06/JUN/14 for case transfer compareing case quantity end.
						caseGrossWeight = grossWeightOld / noArtOld;
						caseNetWeight = netWeightOld / noArtOld;
						caseTareWeight = tareWeightOld / noArtOld;
						caseQtyStduom = quantityStduom / noArtOld;
					}
					pstmt.close();
					pstmt = null;									 
					rs.close();
					rs = null;
					/////////////////////////////////////////////////////////////
					
					sSQL = "SELECT COUNT(*) AS COUNT FROM DESPATCHDET WHERE DESP_ID = ? AND ITEM_CODE = ? AND LOT_SL = ? AND LOT_NO = ? AND PALLET_NO = ?";
					pstmt = conn.prepareStatement(sSQL);
					pstmt.setString(1,despId);
					pstmt.setString(2,itemCode);
					pstmt.setString(3,lotSl);
					pstmt.setString(4,lotNo);
					//pstmt.setString(5,palletNo);
					pstmt.setString(5,palletNoNew);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						count = rs.getInt("COUNT");
					}
					pstmt.close();
					pstmt = null;									 
					rs.close();
					rs = null;

					//Changed as per Manohran sir on 25/10/12 for No Of articale should be NO_ART = NO_ART - 1
					//sSQL = "UPDATE DESPATCHDET SET  QUANTITY = QUANTITY - ?, NO_ART = 1, GROSS_WEIGHT =  GROSS_WEIGHT - ?, TARE_WEIGHT = TARE_WEIGHT - ?, "
					sSQL = "UPDATE DESPATCHDET SET  QUANTITY = QUANTITY - ?, NO_ART = NO_ART - 1, GROSS_WEIGHT =  GROSS_WEIGHT - ?, TARE_WEIGHT = TARE_WEIGHT - ?, "
							+ " NETT_WEIGHT = NETT_WEIGHT - ? , QUANTITY__STDUOM = QUANTITY__STDUOM - ?,QUANTITY_REAL =  QUANTITY_REAL - ? WHERE  DESP_ID = ? AND LINE_NO = ?";
					pstmt = conn.prepareStatement(sSQL);
					pstmt.setDouble(1,caseQty);
					pstmt.setDouble(2,caseGrossWeight);
					pstmt.setDouble(3,caseTareWeight);
					pstmt.setDouble(4,caseNetWeight);
					pstmt.setDouble(5,caseQtyStduom);
					pstmt.setDouble(6,caseQty);
					pstmt.setString(7, despId);
					pstmt.setString(8, despLineOld);
					pstmt.executeUpdate();	
					pstmt.close();
					pstmt = null;

					if(count == 0)
					{
						//map = addDespatchDetail(itemCode, lotNo, lotSl,pickOrder, despId, palletNoNew, xtraParams, conn);
						//if(map.size() > 0)
						//{
						// 15/10/12 manoharan 1. reduce value in  despatchdet for old record
						// 2. insert a new record in despatch det
						

						sSQL = "SELECT MAX(TO_NUMBER(LINE_NO)) AS LINE_NO FROM DESPATCHDET WHERE DESP_ID = ?";
						pstmt = conn.prepareStatement(sSQL);
						pstmt.setString(1,despId);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							lineNo = rs.getInt("LINE_NO");
						}
						pstmt.close();
						pstmt = null;									 
						rs.close();
						rs = null;
						
						lineNo++;
						//despLineNo = "   " + (Integer)map.get("lineNo");
						despLineNo = "   " + lineNo;
						despLineNo = despLineNo.substring( despLineNo.length()-3 );
						
						///////////sample
						
						sSQL =  "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, PALLET_NO,PART_NO ) " +
								"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?) ";  

						pstmt1 = conn.prepareStatement(sSQL);
						pstmt1.setString(1, despId);
						pstmt1.setString(2, despLineNo);
						pstmt1.setString(3, sordNo);
						pstmt1.setString(4, lineNoSord);
						pstmt1.setString(5, expLev);
						pstmt1.setString(6, itemCodeOrd);
						pstmt1.setString(7, itemCode);
						pstmt1.setString(8, lotNo);
						pstmt1.setString(9, lotSl);
						//pstmt1.setDouble(10, quantityOrd);
						pstmt1.setDouble(10, qtyOrd);
						pstmt1.setDouble(11, caseQty);
						pstmt1.setString(12, checkNull(locCode));
						pstmt1.setString(13,detStatus);
						pstmt1.setDouble(14 , convQtyStduom );
						pstmt1.setString(15 , unitStd);
						pstmt1.setString(16 , unit);
						pstmt1.setDouble(17 , caseQty * convQtyStduom );
						pstmt1.setDouble(18 , caseQty);
						pstmt1.setDouble(19 , rateStduom );
						pstmt1.setString(20 , checkNull(packInstr) );
						pstmt1.setDouble( 21 , 1);
						pstmt1.setString( 22 , checkNull(siteCode));
						pstmt1.setDouble( 23 , packQty);  
						pstmt1.setDouble( 24 , caseGrossWeight );
						pstmt1.setDouble( 25 , caseTareWeight );
						pstmt1.setDouble( 26 , caseNetWeight);
						pstmt1.setTimestamp( 27 , mfgDate );
						pstmt1.setTimestamp( 28 , expDate );
						pstmt1.setString( 29 , checkNull(siteCodeMfg));
						pstmt1.setDouble( 30 , rateClg);
						pstmt1.setDouble( 31 , costRate);
						pstmt1.setString( 32 , lineType);
						pstmt1.setDouble( 33 , convRateStduom);
						pstmt1.setString( 34 , checkNull(packCode));
						pstmt1.setString( 35, checkNull(custItemRef));
						pstmt1.setTimestamp( 36 ,restetDate);
						pstmt1.setString( 37 , checkNull(palletNoNew));
						pstmt1.setString( 38 , checkNull(partNo));

						pstmt1.executeUpdate();
						pstmt1.close();
						pstmt1 = null;
						System.out.println("Complete the despatch insertion");
						

						sSQL = "UPDATE PALLETMERGE_DET SET DESP_ID = ? , LINE_NO__DESP = ?, LINE_NO__DESP_NEW = ? WHERE TRAN_ID = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sSQL);
						pstmt.setString(1, despId);
						pstmt.setString(2, despLineNo);
						pstmt.setString(3, despLineNo);
						pstmt.setString(4, tranId);
						pstmt.setString(5, palletlineNo);
						pstmt.executeUpdate();	
						pstmt.close();
						pstmt = null;
						//}
					}
					else
					{
						sSQL = "SELECT LINE_NO FROM DESPATCHDET WHERE DESP_ID = ? AND ITEM_CODE = ? AND LOT_SL = ? AND LOT_NO = ? AND PALLET_NO = ?";
						pstmt = conn.prepareStatement(sSQL);
						pstmt.setString(1,despId);
						pstmt.setString(2,itemCode);
						pstmt.setString(3,lotSl);
						pstmt.setString(4,lotNo);
						pstmt.setString(5,palletNoNew);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							despLineNo = rs.getString("LINE_NO");
						}
						pstmt.close();
						pstmt = null;									 
						rs.close();
						rs = null;
					
						// despatchdet already has the same stock in another pallet just update the quntity and weights
						sSQL = "UPDATE DESPATCHDET SET  QUANTITY = QUANTITY + ?, NO_ART = NO_ART + 1, GROSS_WEIGHT =  GROSS_WEIGHT + ?, TARE_WEIGHT = TARE_WEIGHT + ?, "
							+ " NETT_WEIGHT = NETT_WEIGHT + ? , QUANTITY__STDUOM = QUANTITY__STDUOM + ?,QUANTITY_REAL = QUANTITY_REAL + ? WHERE  DESP_ID = ? AND LINE_NO = ?";
						pstmt = conn.prepareStatement(sSQL);
						pstmt.setDouble(1,caseQty);
						pstmt.setDouble(2,caseGrossWeight);
						pstmt.setDouble(3,caseTareWeight);
						pstmt.setDouble(4,caseNetWeight);
						pstmt.setDouble(5,caseQtyStduom);
						pstmt.setDouble(6,caseQty);
						pstmt.setString(7, despId);
						pstmt.setString(8, despLineNo);
						pstmt.executeUpdate();	
						pstmt.close();
						pstmt = null;
						sSQL = "UPDATE PALLETMERGE_DET SET DESP_ID = ? , LINE_NO__DESP = ?, LINE_NO__DESP_NEW = ? WHERE TRAN_ID = ? AND LINE_NO = ? ";
						pstmt = conn.prepareStatement(sSQL);
						pstmt.setString(1, despId);
						pstmt.setString(2, despLineNo);
						pstmt.setString(3, despLineNo);
						pstmt.setString(4, tranId);
						pstmt.setString(5, palletlineNo);
						pstmt.executeUpdate();	
						pstmt.close();
						pstmt = null;

						/*int noArt = 0;
						double quantity = 0;
						double grossWeight = 0;
						double netWeight = 0;
						double tareWeight = 0;
						
						map = addDespatchDetail(itemCode, lotNo, lotSl,pickOrder, despId, palletNoNew, xtraParams, conn);
						if(map.size() > 0)
						{
							sSQL = "SELECT LINE_NO, NO_ART, QUANTITY, GROSS_WEIGHT , TARE_WEIGHT, NETT_WEIGHT  FROM DESPATCHDET WHERE  DESP_ID = ? AND ITEM_CODE = ? AND LOT_SL = ? AND LOT_NO = ? AND PALLET_NO = ?";
							pstmt = conn.prepareStatement(sSQL);
							pstmt.setString(1,despId);
							pstmt.setString(2,itemCode);
							pstmt.setString(3,lotSl);
							pstmt.setString(4,lotNo);
							pstmt.setString(5,palletNo);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								lineNo = rs.getString("LINE_NO");
								noArt = rs.getInt("NO_ART");
								quantity = rs.getDouble("QUANTITY");
								grossWeight = rs.getDouble("GROSS_WEIGHT");
								netWeight = rs.getDouble("NETT_WEIGHT");
								tareWeight = rs.getDouble("TARE_WEIGHT");
								
								System.out.println("grossWeight["+grossWeight+"] netWeight["+netWeight+"] tareweight["+tareWeight+"] noart ["+noArt+"] quantity["+quantity+"] desp new lineNo["+despLineNo+"]");
								
								grossWeight = grossWeight - (Double)map.get("grossWeight");
								tareWeight = tareWeight - (Double)map.get("tareWeight");
								netWeight = netWeight - (Double)map.get("netWeight");
								quantity = quantity - (Double)map.get("quantity");
								noArt = noArt - (Integer)map.get("noArt");
								
								despLineNo = "   " + (Integer)map.get("lineNo");
								despLineNo = despLineNo.substring( despLineNo.length()-3 );
								
								System.out.println("grossWeight["+grossWeight+"] netWeight["+netWeight+"] tareweight["+tareWeight+"] noart ["+noArt+"] quantity["+quantity+"] desp new lineNo["+despLineNo+"]");

								pstmt.close();
								pstmt = null;									 
								rs.close();
								rs = null;
								
								if(quantity <= 0)
								{
									sSQL = "UPDATE DESPATCHDET SET PALLET_NO = ? WHERE DESP_ID = ? AND LINE_NO = ? ";
									pstmt = conn.prepareStatement(sSQL);
									pstmt.setString(1, palletNoNew);
									pstmt.setString(2, despId);
									pstmt.setString(3, lineNo);
									pstmt.executeUpdate();	
									pstmt.close();
									pstmt = null;

									sSQL = "UPDATE PALLETMERGE_DET SET DESP_ID = ?,LINE_NO__DESP = ?, LINE_NO__DESP_NEW = ? WHERE TRAN_ID = ? AND LINE_NO = ? ";
									pstmt = conn.prepareStatement(sSQL);
									pstmt.setString(1, despId);
									pstmt.setString(2, lineNo);
									pstmt.setString(3, lineNo);
									pstmt.setString(4, tranId);
									pstmt.setString(5, palletlineNo);
									pstmt.executeUpdate();	
									pstmt.close();
									pstmt = null;
									
									sSQL = "DELETE FROM DESPATCHDET WHERE DESP_ID = ? AND LINE_NO = ? ";
									pstmt = conn.prepareStatement(sSQL);
									pstmt.setString(1, despId);
									pstmt.setString(2, despLineNo);
									pstmt.executeUpdate();	
									pstmt.close();
									pstmt = null;
									
								}
								else
								{
									sSQL = "UPDATE DESPATCHDET SET NO_ART = ?, QUANTITY = ? , GROSS_WEIGHT = ?, " +
											" TARE_WEIGHT = ?, NETT_WEIGHT = ? WHERE DESP_ID = ? AND LINE_NO = ? ";
									pstmt = conn.prepareStatement(sSQL);
									pstmt.setInt(1, noArt);
									pstmt.setDouble(2, quantity);
									pstmt.setDouble(3, grossWeight);
									pstmt.setDouble(4, tareWeight);
									pstmt.setDouble(5, netWeight);
									pstmt.setString(6, despId);
									pstmt.setString(7, lineNo);
									pstmt.executeUpdate();	
									pstmt.close();
									pstmt = null;
									
									sSQL = "UPDATE PALLETMERGE_DET SET DESP_ID = ? , LINE_NO__DESP = ?, LINE_NO__DESP_NEW = ? WHERE TRAN_ID = ? AND LINE_NO = ? ";
									pstmt = conn.prepareStatement(sSQL);
									pstmt.setString(1, despId);
									pstmt.setString(2, lineNo);
									pstmt.setString(3, despLineNo);
									pstmt.setString(4, tranId);
									pstmt.setString(5, palletlineNo);
									pstmt.executeUpdate();	
									pstmt.close();
									pstmt = null;*/
								//}
							//}
						//}
					}
					//map.clear();
					//map = null;
				}
				sSQL = "DELETE FROM DESPATCHDET WHERE DESP_ID = ?  AND QUANTITY <= 0";
				pstmt = conn.prepareStatement(sSQL);
				pstmt.setString(1, despId);
				pstmt.executeUpdate();	
				pstmt.close();
				pstmt = null;
				
				sSQL = "UPDATE PALLETMERGE SET PALLET_NO__TO = ? , CONFIRMED = ? , CONF_DATE = ? WHERE TRAN_ID = ? ";
				pstmt = conn.prepareStatement(sSQL);
				pstmt.setString(1, palletNoNew);
				pstmt.setString(2, "Y");
				pstmt.setDate(3, new java.sql.Date(new java.util.Date().getTime()));
				pstmt.setString(4, tranId);
				pstmt.executeUpdate();	
				pstmt.close();
				pstmt = null;
			}
		}
		catch(Exception e)
		{
			try
			{
				conn.rollback();
				System.out.println("Exception "+e.getMessage());
				e.printStackTrace();			
				throw new ITMException(e);
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
				throw new ITMException(e1);
			}
		}
		finally
		{
			try
			{				
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
	}	
	
	private HashMap<String ,Number> addDespatchDetail(String itemCode, String lotNo, String lotSl, String pickOrder, String despId, String palletNoNew, String xtraParams, Connection conn) throws ITMException 
	{
		String packInstr = "";
		String siteCodeSord = "";
		String custItemRef = "";
		String rateStduom = "";
		String rateClg = "";
		String unit = "";
		double convQtyStduom = 0;
		double convFact = 0 ;
		double meffQty = 0;
		String unitStd = "";
		double rate = 0;
		double qtyStduom = 0;
		double qtyStduom1 = 0; 
		double conRtuStduom = 0;
		int lineNo = 0;
		String lineNoSord = "";
		String despLineNo = "";
		String sordItemSql = "";
		String sordDetSql = "";
		String stockSql = "";
		String itemSql = "";
		String itemCodeOrd = "";
		String sordSql = "";
		double quantityOrd = 0d;
		double grossWtPerArt = 0d;
		double tareWtPerArt = 0d;
		double palletWt = 0d;
		double stkRate = 0d;
		Timestamp mfgDate = null;
		Timestamp expDate = null;
		String siteCodeMfg = "";
		String packCode = "";
		String mbaseUnit = "";
		String partNo = "";
		String sSQL = "";
		double shipperSize = 0;
		Timestamp retestDate = null;
		HashMap<String, Number> map = new HashMap<String , Number>();
		PreparedStatement pstmt = null, pstmt1= null, pstmtSordItem = null, pstmtSord = null, pstmtSordDet = null, pstmtStock = null;
		PreparedStatement pstmtItem = null ;
        ResultSet rs = null , rsSordItem = null, rsSordDet = null, rsSord = null, rsStock = null , rsItem = null;
		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			DistCommon disCommon = new DistCommon();
			String applDateFormat = genericUtility.getApplDateFormat();
	    	java.util.Date currDate = new java.util.Date();
	    	SimpleDateFormat sdf = new SimpleDateFormat(applDateFormat);
	    	String currDateStr = sdf.format(currDate);
			sSQL = "SELECT MAX(TO_NUMBER(LINE_NO)) AS LINE_NO FROM DESPATCHDET WHERE DESP_ID = ?";
			pstmt = conn.prepareStatement(sSQL);
			pstmt.setString(1,despId);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				lineNo = rs.getInt("LINE_NO");
			}
			pstmt.close();
			pstmt = null;									 
			rs.close();
			rs = null;
			///////////sample
			
			String 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,PALLET_NO , PALLET_WT , CHG_USER , CHG_TERM , CHG_DATE) " +
					"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?,?,?) ";  

			pstmt1 = conn.prepareStatement(sql1);

			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 sql = "SELECT POD.LINE_NO AS LINE_NO__ORD, POD.SITE_CODE,"+ //sql added by Kunal On 03/08/12 as per S Monoharan Sir intruction 
				  " POD.ITEM_CODE,P.ITEM_CODE, P.LOC_CODE, P.LOC_CODE__TO, P.LOT_NO, P.LOT_SL,"+
				  " POD.NO_ART, P.QUANTITY , POD.SALE_ORDER, POD.EXP_LEV, PO.WAVE_ID,"+ 
				  " POD.LINE_NO__SORD,PH.PALLET_NO "+ // change done by Kunal on 03/08/12 select pallet no
				  " FROM PICK_ISS_DET P, PICK_ORD_HDR PO , PICK_ORD_DET POD,PICK_ISS_HDR PH "+
				  " WHERE  POD.PICK_ORDER = PO.PICK_ORDER "+
				  " AND POD.PICK_ORDER = P.PICK_ORDER "+
				  " AND POD.LINE_NO = P.LINE_NO__ORD "+
				  " AND PH.PICK_ORDER = P.PICK_ORDER "+
				  " AND PO.PICK_ORDER = ? ";


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

			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1,pickOrder);
			rs = pstmt.executeQuery(); 
			System.out.println("after excute query---------------");
			if(rs.next())
			{
				System.out.println("** inside while block *** ");
				String siteCode = rs.getString("SITE_CODE");
				String locCode = rs.getString("LOC_CODE");

				int noArt = rs.getInt("NO_ART");
				double quantity = rs.getDouble("QUANTITY");
				String saleOrder = rs.getString("SALE_ORDER");		
				String expLev = rs.getString("EXP_LEV");
				lineNoSord = rs.getString("LINE_NO__SORD");
				String locCodeTo = rs.getString("LOC_CODE__TO");
				
				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()+"]");

				pstmtSordItem.setString(1, saleOrder);
				
				if(lineNoSord.length() == 2)
				{
					pstmtSordItem.setString(2, " "+lineNoSord);
				}
				else if(lineNoSord.length() == 1)
				{
					pstmtSordItem.setString(2, "  "+lineNoSord);
				}
				else
				{
					pstmtSordItem.setString(2, lineNoSord);
				}

				rsSordItem = pstmtSordItem.executeQuery();
				if ( rsSordItem.next() )
				{
					itemCodeOrd = checkNull(rsSordItem.getString("ITEM_CODE__ORD"));
					quantityOrd = rsSordItem.getDouble("QUANTITY__ORD");
				}
				rsSordItem.close();rsSordItem = null;
				pstmtSordItem.close();
				pstmtSordItem = null;

				pstmtSordDet.setString(1, saleOrder);
				if(lineNoSord.length() == 2)
				{
					pstmtSordDet.setString(2, " "+lineNoSord);
				}
				else if(lineNoSord.length() == 1)
				{
					pstmtSordDet.setString(2, "  "+lineNoSord);

				}
				else
				{
					pstmtSordDet.setString(2, lineNoSord);
				}
				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");
				}
				rsSordDet.close();rsSordDet = null;
				pstmtSordDet.close();
				pstmtSordDet = null;
				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, currDateStr, itemCode, " ", "L", quantity, conn );
					}
				}
				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+"]");
					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");
					System.out.println("INSIDE STOCKS DATA>>>>>>>>>>>>>>>>>>>>>>>>>>["+grossWtPerArt+"]");
				}

				rsStock.close();rsStock = null;
				pstmtStock.close();
				pstmtStock = null;

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

				pstmtItem.setString(1, itemCode);
				rsItem = pstmtItem.executeQuery();
				if ( rsItem.next() )
				{
					mbaseUnit = checkNull(rsItem.getString("UNIT_STD"));
					partNo = checkNull(rsItem.getString("MFR_PART_NO"));
					shipperSize = rsItem.getDouble("SHIPPER_SIZE");
				}
				rsItem.close();rsItem = null;
				pstmtItem.close();
				pstmtItem = null;
				
				if(unitStd != "")
				{
					mbaseUnit = unitStd;
				}

				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;
				}
				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++;
				map.put("lineNo", lineNo);
				System.out.println("lineno ["+map.get("lineNo")+"]");
				despLineNo = "   " + lineNo;
				despLineNo = despLineNo.substring( despLineNo.length()-3 );

				lineNoSord = "   " + lineNoSord;
				lineNoSord = lineNoSord.substring( lineNoSord.length()-3 );

				pstmt1.setString(1, despId);
				pstmt1.setString(2, despLineNo);
				pstmt1.setString(3, saleOrder.trim());
				pstmt1.setString(4, lineNoSord);

				pstmt1.setString(5, expLev);

				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);
				map.put("quantity", quantity);
				pstmt1.setString(12, checkNull(locCodeTo));

				pstmt1.setString(13," ");
				pstmt1.setDouble(14 , convQtyStduom );
				pstmt1.setString(15 , mbaseUnit);
				pstmt1.setString(16 , unit);
				pstmt1.setDouble(17 , qtyStduom );
				pstmt1.setDouble(18 , quantity);

				pstmt1.setDouble(19 , rate );
				pstmt1.setString(20 , checkNull(packInstr) );
				pstmt1.setDouble( 21 , noArt);
				map.put("noArt", noArt);
				pstmt1.setString( 22 , checkNull(siteCode));
				pstmt1.setDouble( 23 , shipperSize);  
				pstmt1.setDouble( 24 , grossWeight );
				map.put("grossWeight",grossWeight);
				pstmt1.setDouble( 25 , tareWeight );
				map.put("tareWeight", tareWeight);

				pstmt1.setDouble( 26 , netWeight);
				map.put("netWeight", 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 , "I");

				pstmt1.setDouble( 33 , conRtuStduom);
				pstmt1.setString( 34 , checkNull(packCode));
				pstmt1.setString( 35, checkNull(custItemRef));
				pstmt1.setTimestamp( 36 ,retestDate);
				pstmt1.setString( 37 , checkNull(partNo));
				pstmt1.setString( 38 , checkNull(palletNoNew));
				pstmt1.setDouble( 39 , palletWt);
				pstmt1.setString( 40 , genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" ));
				pstmt1.setString( 41 , genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" ));
				pstmt1.setDate( 42 ,new java.sql.Date(currDate.getTime()));

				pstmt1.executeUpdate();
				pstmt1.close();
				pstmt1 = null;
				System.out.println("Complete the despatch insertion");
			}

			pstmt.close();
			pstmt = null;									 
			rs.close();
			rs = null;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return map;
	}
	private String checkNull(String string) 
	{
		if(string == null)
		{
			string = "";
		}
		return string;
	}
}

