/*******************************************    
Title : PickPostSaveEJB
    Date  : 04/11/11
    Author: Chitranjan Pandey

 ********************************************************/
package ibase.webitm.ejb.wms;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.InvAllocTraceBean;
import ibase.webitm.ejb.dis.StockUpdate;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.utility.TransIDGenerator;
import ibase.utility.CommonConstants;

import java.io.*;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import org.w3c.dom.*;

import javax.ejb.*;
import javax.naming.InitialContext;
@javax.ejb.Stateless
public class PickIssPos extends ValidatorEJB implements PickIssPosLocal, PickIssPosRemote
{
	public String postSave()throws RemoteException,ITMException
	{
		return "";
	}
	public String postSave(String domString, String tranId,String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		String forcedFlag = "false" ;
		String errString = "";
		String sql = "";
		String pickOrder = "";
		String pickType = "";
		String palletNo = "", locCodeTo = "";
		System.out.println("TRAN ID FROM POST SAVE = "+tranId);

		int totOrdDet = 0;
		int ordDetScaned = 0;
		int updCnt = 0;


		boolean toCommit = false;

		Document dom = null;

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		System.out.println("Dom String = "+domString);
		PickIssConf pickConf = new PickIssConf();




		try
		{
			conn.setAutoCommit(false);

			GenericUtility genericUtility = GenericUtility.getInstance();
			dom = genericUtility.parseString(domString);

			NodeList detail4List = dom.getElementsByTagName("Detail4");
			NodeList detail3List = dom.getElementsByTagName("Detail3");
			if(detail3List != null && detail3List.getLength() > 0)
			{
				System.out.println("Detail3 length = "+detail3List.getLength());

				pickOrder = genericUtility.getColumnValueFromNode("pick_order", dom.getElementsByTagName("Detail2").item(0));

				System.out.println("pick order = "+pickOrder);
				sql = "select pick_type from pick_ord_hdr where pick_order = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString( 1, pickOrder );
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					pickType = rs.getString("pick_type");
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;

				System.out.println("pick type = "+pickType);

				if(!("A".equalsIgnoreCase(pickType)))
				{
					System.out.println("called for case pick ");
					if(detail4List != null && detail4List.getLength() > 0)
					{
						System.out.println("Detail4 length = "+detail4List.getLength());

						palletNo = genericUtility.getColumnValue("pallet_no", dom, "1", "1");
						locCodeTo = genericUtility.getColumnValueFromNode("loc_code__to", dom.getElementsByTagName("Detail4").item(0)); // 22/09/12 manoharan
						pickOrder = genericUtility.getColumnValueFromNode("pick_order", dom.getElementsByTagName("Detail2").item(0));
						System.out.println("pick order from header ["+pickOrder+"]");

						sql = "UPDATE pick_iss_det SET loc_code__to =  ? WHERE  tran_id = ? ";
						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,locCodeTo);
						pstmt.setString(2,tranId);
						updCnt = pstmt.executeUpdate();
						pstmt.close(); pstmt = null;
						if(updCnt > 0)
						{
							System.out.println("---row updated sucessfully of master pick---->"+updCnt);
						}
						
						//added by kunal mandhre on 09/08/12 as per S Manoharan Sir instruction 
						sql = "UPDATE PALLET_NO SET STATUS = 'U' WHERE PALLET_NO IN(SELECT PALLET_NO FROM PICK_ISS_HDR WHERE PICK_ORDER =  ? ) ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, pickOrder);
						updCnt = pstmt.executeUpdate();
						pstmt.close(); pstmt = null;
						if(updCnt > 0)
						{
							System.out.println("---row updated sucessfully in pallet no ---->"+updCnt);
						}

						//Update the carton master 
						if(!"M".equalsIgnoreCase(pickType)) // 22/09/12 manoharan
						{
							sql = "UPDATE CARTON_MASTER SET STATUS = 'U', pallet_no = ? WHERE PICK_ORDER = ? and line_no__pick in (select line_no__ord from pick_iss_det where tran_id = ?) ";
							
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,palletNo);
							pstmt.setString(2, pickOrder);
							pstmt.setString(3,tranId);
							updCnt = pstmt.executeUpdate();
							pstmt.close(); pstmt = null;
							if(updCnt > 0)
							{
								System.out.println("---row updated sucessfully of master pick---->"+updCnt);
							}
							
							//added by kunal mandhre on 09/08/12 as per S Manoharan Sir instruction 
							sql = "UPDATE PALLET_NO SET STATUS = 'U' WHERE PALLET_NO IN(SELECT PALLET_NO FROM PICK_ISS_HDR WHERE PICK_ORDER =  ? ) ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, pickOrder);
							updCnt = pstmt.executeUpdate();
							pstmt.close(); pstmt = null;
							if(updCnt > 0)
							{
								System.out.println("---row updated sucessfully in pallet no ---->"+updCnt);
							}
							//end by kunal
						}
						//select no of art from pick_ord_det 
						sql = "SELECT SUM(NO_ART) FROM PICK_ORD_DET WHERE PICK_ORDER = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString( 1, pickOrder );
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							totOrdDet = rs.getInt(1);
						}
						rs.close(); rs = null;
						pstmt.close(); pstmt = null;

						//select the no of order scanned 
						sql = "SELECT SUM(NO_ART) AS COUNT FROM PICK_ISS_DET WHERE PICK_ORDER = ? ";
						pstmt = conn.prepareStatement( sql );
						pstmt.setString( 1, pickOrder );
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							ordDetScaned = rs.getInt("COUNT");
						}
						rs.close(); rs = null;
						pstmt.close(); pstmt = null;

						//if total pick order detail is equals to total order scanned then confirm method is called
						System.out.println("Total details of pick order == ["+totOrdDet+"]");
						System.out.println("Total order scanned == ["+ordDetScaned+"]");
					/*	if( totOrdDet == ordDetScaned )
						{ */
							//changes done by cpandey to call confirm method in case of pick type is (m or c) 31//07/12
							System.out.println("pick type from pick_ord_hdr table--->>["+pickType+"]");
							//commented by chitranjan to call confirm method for all pick type on 07/08/12
							//if (!("A".equalsIgnoreCase(pickType)))
							//if(pickType.equalsIgnoreCase("C")|| pickType.equalsIgnoreCase("M"))
							//{
							System.out.println("Calling Confirm method..................");
							errString = pickConf.confirm( tranId, xtraParams, forcedFlag ,conn, toCommit );
							
							if (errString.indexOf("CONFSUCESS") > -1)
							{
								errString = "";
							}
							if ( errString != null && errString.length() > 0 )
							{
								throw new Exception(errString);
							}
							//}
						/*} */
					}
				}
				else
				{
					System.out.println("called for active pick ");
					palletNo = genericUtility.getColumnValue("pallet_no", dom, "1", "1");
					pickOrder = genericUtility.getColumnValueFromNode("pick_order", dom.getElementsByTagName("Detail2").item(0));
					System.out.println("pick order from header ["+pickOrder+"]");
					/*

					sql = "select pick_type from pick_ord_hdr where pick_order = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString( 1, pickOrder );
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						pickType = rs.getString("pick_type");
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					 */

					/*if("M".equalsIgnoreCase(pickType))
			{
				sql = "update pick_iss_det set carton_no = ?  where tran_id = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, palletNo);
				pstmt.setString(2, tranId);
				int updCnt1 = pstmt.executeUpdate();
				pstmt.close(); pstmt = null;
				if(updCnt1 > 0)
				{
					System.out.println("---row updated sucessfully of master pick_iss_det table ---->"+updCnt1);
				}
			}*/

					//Update the carton master 
						sql = "UPDATE CARTON_MASTER SET STATUS = 'U', pallet_no = ? WHERE PICK_ORDER = ? and line_no__pick in (select line_no__ord from pick_iss_det where tran_id = ?) ";
						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,palletNo);
						pstmt.setString(2, pickOrder);
						pstmt.setString(3,tranId);
						updCnt = pstmt.executeUpdate();
						pstmt.close(); pstmt = null;
						if(updCnt > 0)
						{
							System.out.println("---row updated sucessfully of master pick---->"+updCnt);
						}

					/*//added by kunal mandhre on 09/08/12 as per S Manoharan Sir instruction 
					sql = "UPDATE PALLET_NO SET STATUS = 'U' WHERE PALLET_NO IN(SELECT PALLET_NO FROM PICK_ISS_HDR WHERE PICK_ORDER =  ? ) ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, pickOrder);
					updCnt = pstmt.executeUpdate();
					pstmt.close(); pstmt = null;
					if(updCnt > 0)
					{
						System.out.println("---row updated sucessfully in pallet no ---->"+updCnt);
					}*/
					//end by kunal

					//select no of art from pick_ord_det 
					sql = "SELECT SUM(NO_ART) FROM PICK_ORD_DET WHERE PICK_ORDER = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString( 1, pickOrder );
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						totOrdDet = rs.getInt(1);
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;

					//select the no of order scanned 
					sql = "SELECT SUM(NO_ART) AS COUNT FROM PICK_ISS_DET WHERE PICK_ORDER = ? ";
					pstmt = conn.prepareStatement( sql );
					pstmt.setString( 1, pickOrder );
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						ordDetScaned = rs.getInt("COUNT");
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;

					//if total pick order detail is equals to total order scanned then confirm method is called
					System.out.println("Total details of pick order == ["+totOrdDet+"]");
					System.out.println("Total order scanned == ["+ordDetScaned+"]");
					if( totOrdDet == ordDetScaned )
					{
						//changes done by cpandey to call confirm method in case of pick type is (m or c) 31//07/12
						System.out.println("pick type from pick_ord_hdr table--->>["+pickType+"]");
						//commented by chitranjan to call confirm method for all pick type on 07/08/12
						//if (!("A".equalsIgnoreCase(pickType)))
						//if(pickType.equalsIgnoreCase("C")|| pickType.equalsIgnoreCase("M"))
						//{
						
						//added by kunal mandhre on 09/08/12 as per S Manoharan Sir instruction 
						sql = "UPDATE PALLET_NO SET STATUS = 'U' WHERE PALLET_NO IN(SELECT PALLET_NO FROM PICK_ISS_HDR WHERE PICK_ORDER =  ? ) ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, pickOrder);
						updCnt = pstmt.executeUpdate();
						pstmt.close(); pstmt = null;
						if(updCnt > 0)
						{
							System.out.println("---row updated sucessfully in pallet no ---->"+updCnt);
						}
						
						
						System.out.println("Calling Confirm method..................");
						errString = pickConf.confirm( tranId, xtraParams, forcedFlag ,conn, toCommit );
						if (errString.indexOf("CONFSUCESS") > -1)
						{
							errString = "";
						}
						if ( errString != null && errString.length() > 0 )
						{
							throw new Exception(errString);
						}
						//}
					}

				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception "+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				System.err.println("Exception :PickIssPos :\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
	}
}
