package ibase.webitm.ejb.wms;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.net.URL;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import java.util.ArrayList;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import com.adsionline.ADSIShippingWS.ADSIShippingLocator;
import com.adsionline.ADSIShippingWS.ADSIShippingSoap;
import com.adsionline.ADSIShippingWS.StringDictionaryReturn;


@javax.ejb.Stateless

public class PickOrderPrc extends ProcessEJB implements PickOrderPrcLocal,PickOrderPrcRemote
{

	public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("INSIDE GET DATA");
		String rtrStr = "";
		Document headerDom = null;
		Document detailDom = null;
		System.out.println("getData(xmlString,xmlString2,windowName,xtraParams CALLED..."+ xmlString);
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString);
				headerDom = genericUtility.parseString(xmlString); 				
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString2);
				detailDom = genericUtility.parseString(xmlString2); 				
			}
			rtrStr = getData(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception :PickOrderPrcEJB :getData(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			rtrStr = e.getMessage();
		}
		return rtrStr; 
	}

	public String getData(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String fromDate = "";
		String toDate="";
		String waveId = "";
		String saleOrder = "";
		String pickOrder = "";
		String assingTo = "";
		String status = "";
		String ptcn = "";
		String pickOrderFrom = "";
		String pickOrderTo = "";
		String orderDate = "";
		String errCode = "";
		String errString = "";
		//changed by sankara on 28/09/13 for getting repl_order start.
		String pickreplStatus = "";
		String sql = "";
		int count = 0;
		//changed by sankara on 28/09/13 for getting repl_order end.
		PreparedStatement pstmt=null;
		String getDataSql=null;
		String resultString = "";
		Connection conn = null;
		ResultSet rs=null;
		boolean rsflag=false;
		java.sql.Timestamp dateFr =  null;
		java.sql.Timestamp dateTo =  null;
		StringBuffer valueXmlString=new StringBuffer();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();		
		valueXmlString = new StringBuffer( "<?xml version='1.0' encoding='UTF-8'?>\r\n<DocumentRoot>\r\n<description>Datawindow Root</description>\r\n<group0>\r\n" +
				                           "<description>Group0 description</description>\r\n<Header0>\r\n<description>Header0 members</description>\r\n");			
		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			pickOrderFrom = checkNull(genericUtility.getColumnValue("pick_order_from",headerDom));
			System.out.println("PICKORDERFROM==="+pickOrderFrom);
			pickOrderTo = checkNull(genericUtility.getColumnValue("pick_order_to",headerDom));
			System.out.println("PICKORDERTO==="+pickOrderTo);
			fromDate = checkNull(genericUtility.getColumnValue("from_date",headerDom));
			System.out.println("FROMDATE==="+fromDate);
			toDate = checkNull(genericUtility.getColumnValue("to_date",headerDom));
			System.out.println("TODATE==="+toDate);
			//changed by sankara on 28/09/13 for getting unconfirmed repl_orders start.
			pickreplStatus = checkNull(genericUtility.getColumnValue("status",headerDom));
			System.out.println("PICKREPLSTATUS==="+pickreplStatus);
			//changed by sankara on 28/09/13 for getting unconfirmed repl_orders end.
			System.out.println("headerDom:"+headerDom);
			System.out.println("detailDom:"+detailDom);
			System.out.println("windowName:"+windowName);
			System.out.println("xtraParams:"+xtraParams);

			if ( fromDate == null || fromDate.trim().length() == 0 )
			{
				errCode = "VPFROMBTBK";
				errString = itmDBAccessEJB.getErrorString("","VPFROMBTBK",userId);
				return errString; 
			}
			fromDate = genericUtility.getValidDateString(fromDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			System.out.println("FROMDATE==="+fromDate);
			dateFr = java.sql.Timestamp.valueOf(fromDate + " 00:00:00");

			if ( toDate == null || toDate.trim().length() == 0 ) 
			{
				errCode = "VPTODADTBK";
				errString = itmDBAccessEJB.getErrorString("","VPTODADTBK",userId);
				return errString; 
			} 
			toDate = genericUtility.getValidDateString(toDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			System.out.println("TODATE==="+toDate);
			dateTo = java.sql.Timestamp.valueOf(toDate + " 00:00:00");
			int i = toDate.compareTo(fromDate);
			System.out.println("DATEVALIDATE==="+i);
			orderDate = Integer.toString(i);
			if(orderDate.contains("-"))
			{	
				errCode = "VPINVLDATE";
				errString = itmDBAccessEJB.getErrorString("","VPINVLDATE",userId);
				return errString; 			
			}
			//changed by sankara on 28/09/13 for getting unconfirmed repl_orders start.
			if ( pickreplStatus == null || pickreplStatus.trim().length() == 0 )
			{
				System.out.println("PICKREPLSTATUS="+pickreplStatus);
				errCode = "VPBLKSTAUS";
				errString = itmDBAccessEJB.getErrorString("","VPBLKSTAUS",userId);
				return errString; 
			}
			else if(pickreplStatus != null || pickreplStatus.trim().length() != 0)
			{
				sql = " SELECT COUNT(*) FROM GENCODES WHERE FLD_NAME = 'STATUS' AND MOD_NAME = 'W_PICK_ASSIGNTO'  AND FLD_VALUE = ? ";
				pstmt =  conn.prepareStatement(sql);
				pstmt.setString(1,pickreplStatus);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count =  rs.getInt(1);
				}
				if(count == 0) 
				{
					errCode = "VPINVSTAUS";
					errString = itmDBAccessEJB.getErrorString("","VPINVSTAUS",userId);
					return errString; 
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			} 
			/*	getDataSql = " SELECT DISTINCT P.PICK_ORDER, P.SALE_ORDER, P.WAVE_ID, WD.PTCN, P.ASSIGN_TO, P.STATUS " + 
					         " FROM PICK_ORD_HDR P, WAVE_TASK W, WAVE_TASK_DET WD, PICK_ISS_HDR PH, PICK_ORD_DET PT " +
					         " WHERE P.PICK_ORDER = WD.REF_ID AND P.PICK_ORDER = PT.PICK_ORDER AND P.WAVE_ID = W.WAVE_ID " +
					        // changed by sankara on 05-sep-13 for getting pick order start.    
					        //"AND P.WAVE_ID = WD.WAVE_ID AND P.PICK_ORDER = PH.PICK_ORDER AND WD.STATUS = 'N' AND W.CANCEL = 'N' " +
					       // "AND PH.CONFIRMED = 'N' AND P.PICK_ORDER BETWEEN ? AND ? AND P.ORDER_DATE BETWEEN ? AND ? " ;
					          "AND P.WAVE_ID = WD.WAVE_ID AND WD.STATUS = 'N' AND W.CANCEL = 'N' " +
					          "AND ( PH.CONFIRMED = 'N' OR PH.PICK_ORDER IS NULL ) AND P.PICK_ORDER BETWEEN ? AND ? AND P.ORDER_DATE BETWEEN ? AND ? " ;
			              //   changed by sankara on 05-sep-13 for getting pick order end.    */

			if( pickreplStatus.trim().equalsIgnoreCase("P") )
			{
				getDataSql = " SELECT DISTINCT P.PICK_ORDER, P.SALE_ORDER, P.WAVE_ID, WD.PTCN, P.ASSIGN_TO, P.STATUS " + 
						     " FROM PICK_ORD_HDR P, WAVE_TASK W, WAVE_TASK_DET WD, PICK_ISS_HDR PH, PICK_ORD_DET PT " +
						     " WHERE P.PICK_ORDER = WD.REF_ID AND P.PICK_ORDER = PT.PICK_ORDER AND P.WAVE_ID = W.WAVE_ID " +
						     " AND P.WAVE_ID = WD.WAVE_ID AND WD.STATUS = 'N' AND W.CANCEL = 'N' " +
						     " AND ( PH.CONFIRMED = 'N' OR PH.PICK_ORDER IS NULL ) AND P.PICK_ORDER BETWEEN ? AND ? AND P.ORDER_DATE BETWEEN ? AND ? " ;			
			}
			else
			{
				getDataSql = " SELECT DISTINCT R.REPL_ORDER, R.SALE_ORDER, R.WAVE_ID, WD.PTCN, R.ASSIGN_TO, WD.WAVE_STATUS " + 
						     " FROM REPL_ORD_HDR R, WAVE_TASK W, WAVE_TASK_DET WD, REPL_ISS_HDR RH, REPL_ORD_DET RT " +
						     " WHERE R.REPL_ORDER = WD.REF_ID AND R.REPL_ORDER = RT.REPL_ORDER AND R.WAVE_ID = W.WAVE_ID " +
						     " AND R.WAVE_ID = WD.WAVE_ID AND WD.STATUS = 'N' AND W.CANCEL = 'N' AND ( RT.CANCEL_MODE='N' OR RT.CANCEL_MODE IS NULL ) " +
						     " AND ( RH.CONFIRMED = 'N' OR RH.REPL_ORDER IS NULL ) AND R.REPL_ORDER BETWEEN ? AND ? AND R.ORDER_DATE BETWEEN ? AND ? " ;
			}
			//changed by sankara on 28/09/13 for getting unconfirmed repl_orders end.
			System.out.println("**************getDataSql="+getDataSql);
			pstmt = conn.prepareStatement(getDataSql);
			pstmt.setString(1,pickOrderFrom);
			pstmt.setString(2,pickOrderTo);
			pstmt.setTimestamp(3,dateFr);
			pstmt.setTimestamp(4,dateTo);
			rs = pstmt.executeQuery();
			rsflag=false;
			while(rs.next())
			{
				rsflag=true;
				valueXmlString.append( "<Detail2>\r\n" );  
				pickOrder = checkNull(rs.getString(1));
				saleOrder = checkNull(rs.getString(2));
				waveId = checkNull(rs.getString(3));
				ptcn = checkNull(rs.getString(4)); 
				assingTo = checkNull(rs.getString(5)); 
				status = checkNull(rs.getString(6)); 

				valueXmlString.append( "<pick_order><![CDATA[" ).append(pickOrder).append( "]]></pick_order>\r\n" );
				valueXmlString.append( "<sale_order><![CDATA[" ).append (saleOrder).append( "]]></sale_order>\r\n" );
				valueXmlString.append( "<wave_id><![CDATA[" ).append( waveId ).append( "]]></wave_id>\r\n" );
				valueXmlString.append( "<ptcn><![CDATA[" ).append(ptcn).append( "]]></ptcn>\r\n" );
				valueXmlString.append( "<assign_to><![CDATA[" ).append(assingTo).append( "]]></assign_to>\r\n" );
				//changed by sankara on 06/12/13 for getting unconfirmed repl_orders 
				//valueXmlString.append( "<status><![CDATA[" ).append(status).append( "]]></status>\r\n" );
				//changed by sankara on 28/09/13 for getting unconfirmed repl_orders start.
				valueXmlString.append( "<pick_repl_status><![CDATA[" ).append(pickreplStatus).append( "]]></pick_repl_status>\r\n" );
				//changed by sankara on 28/09/13 for getting unconfirmed repl_orders end.
				valueXmlString.append("\n");
				valueXmlString.append( "</Detail2>\r\n" );
			}
			valueXmlString.append( "</Header0>\r\n</group0>\r\n</DocumentRoot>\r\n" );
			resultString=valueXmlString.toString();

			if(rsflag == false)
			{
				errCode = "VPINVLDATA";
				errString = itmDBAccessEJB.getErrorString("","VPINVLDATA",userId);
				return errString;
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
		}	
		catch(Exception e)
		{
			System.out.println("Exception occured while getData()");
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(conn != null)
				{
					if(rs != null) 
					{ 
						rs.close();
						rs = null;
					}
					if(pstmt != null) 
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
				}
				conn = null;	
			}
			catch(Exception ex)
			{
				System.out.println("Exception occured while getData()");
				ex.printStackTrace();
			}
		}
		return resultString;	
	} //get data method closed
	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();
		System.out.println("***Testing Process Method***");
		System.out.println("below genericUtility--------------->>>>>>>>>");
		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 :PickOrderPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			retStr = e.getMessage();
			throw new ITMException(e);
		}
		return retStr;
	}
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		Connection conn = null;
		ResultSet rs = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;				
		PreparedStatement pstmt = null;
		String updateSql = null;
		String sql = null;
		int parentNodeListLength = 0;
		int childNodeListLength = 0;
		NodeList parentNodeList = null;
		String childNodeName = null;
		String ptcn = "";
		String waveId = "";
		String pickOrder = "";
		String saleOrder = "";
		String status = "";
		String assignTo = "";
		int count = 0;
		int totalCount = 0;
		int pickIssCount = 0;
		String errCode = "";
		String errString = "";
		boolean isError = false;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		//changed by sankara on 28/09/13 for getting unconfirmed repl_orders.
		String pickreplStatus = "";
		try 
		{
			String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			System.out.println("headerDom:"+headerDom);
			System.out.println("detailDom:"+detailDom);
			System.out.println("windowName:"+windowName);
			System.out.println("xtraParams:"+xtraParams);

			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength(); 
			for (int selectedRow = 0; selectedRow < parentNodeListLength; selectedRow++)
			{				
				parentNode = parentNodeList.item(selectedRow);				
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				System.out.println("childNodeListLength---->>> "+ childNodeListLength);

				for (int childRow = 0; childRow < childNodeListLength; childRow++)
				{
					childNode = childNodeList.item(childRow);
					childNodeName = childNode.getNodeName();
					System.out.println("childNodeName---->>> "+ childNodeName);

					if("pick_order".equalsIgnoreCase(childNodeName))
					{
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							pickOrder = checkNull(childNode.getFirstChild().getNodeValue());
						}
					}
					else if("sale_order".equalsIgnoreCase(childNodeName))
					{	
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							saleOrder = checkNull(childNode.getFirstChild().getNodeValue());
						}
					}
					else if("wave_id".equalsIgnoreCase(childNodeName))
					{
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							waveId = checkNull(childNode.getFirstChild().getNodeValue());
						}
					}
					else if("ptcn".equalsIgnoreCase(childNodeName))
					{
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							ptcn = checkNull(childNode.getFirstChild().getNodeValue());
						}
					}
					else if("assign_to".equalsIgnoreCase(childNodeName))
					{

						if ( childNode != null && childNode.getFirstChild() != null )
						{
							assignTo = (childNode.getFirstChild().getNodeValue());
							System.out.println("assignTo="+assignTo);
						}
						if( assignTo.trim() == null || assignTo.trim().length() == 0 )
						{
							errString = itmDBAccessEJB.getErrorString("","VPBLKUSRCD",userId);
							return errString; 
						}
						else if ( assignTo != null && assignTo.trim().length() != 0 )
						{
							System.out.println("assignTo==="+assignTo);
							sql = " SELECT COUNT(*) FROM USERS WHERE CODE = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,assignTo.trim());
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								count =  rs.getInt(1);
							}
							if(count == 0) 
							{
								errCode = "VPINVUSRCD";
								errString = itmDBAccessEJB.getErrorString("","VPINVUSRCD",userId);
								return errString; 
							}	
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						}						

						// Changed by sankara on 07-09-13 for checking spaces start..	
						/*   else
						{
							errString = itmDBAccessEJB.getErrorString("","VPBLKUSRCD",userId);
							return errString; 
						}	*/

						// Changed by sankara on 07-09-13 for checking spaces end..		
					}	
					else if("status".equalsIgnoreCase(childNodeName))
					{
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							status = checkNull(childNode.getFirstChild().getNodeValue());
						}
					}
					//changed by sankara on 28/09/13 for getting unconfirmed repl_orders start.
					else if("pick_repl_status".equalsIgnoreCase(childNodeName))
					{
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							pickreplStatus = checkNull(childNode.getFirstChild().getNodeValue());
						}
					}
					//changed by sankara on 28/09/13 for getting unconfirmed repl_orders end.
				}
				System.out.println(" WAVEID=["+waveId+"] PTCN=["+ptcn+"] SALEORDER=["+saleOrder+"] PICKORDER=["+pickOrder+"] ASSIGNTO=["+assignTo+"] PICKREPLSTATUS=["+pickreplStatus+"] ");
				//changed by sankara on 28/09/13 for getting unconfirmed pick orders
				//if ( pickOrder != null && pickOrder.trim().length() != 0 )
				if ( pickOrder != null && pickOrder.trim().length() != 0  && pickreplStatus.trim().equalsIgnoreCase("P"))	
				{
					System.out.println("PICKORDER===="+pickOrder);
					updateSql= " UPDATE PICK_ORD_HDR SET ASSIGN_TO = ? WHERE PICK_ORDER = ? ";
					pstmt = conn.prepareStatement(updateSql);					
					pstmt.setString(1,assignTo);
					pstmt.setString(2,pickOrder);
					count = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;

					updateSql= " UPDATE PICK_ISS_HDR SET CHG_USER = ? WHERE PICK_ORDER = ? ";
					pstmt = conn.prepareStatement(updateSql);					
					pstmt.setString(1,assignTo);
					pstmt.setString(2,pickOrder);
					pickIssCount = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;   				
				}
				//changed by sankara on 28/09/13 for getting unconfirmed repl orders start.
				if ( pickOrder != null && pickOrder.trim().length() != 0  && pickreplStatus.trim().equalsIgnoreCase("R"))
				{
					System.out.println("REPLORDER===="+pickOrder);
					updateSql= " UPDATE REPL_ORD_HDR SET ASSIGN_TO = ? WHERE REPL_ORDER = ? ";
					pstmt = conn.prepareStatement(updateSql);					
					pstmt.setString(1,assignTo);
					pstmt.setString(2,pickOrder);
					pickIssCount = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;

					updateSql= " UPDATE REPL_ISS_HDR SET CHG_USER = ? WHERE REPL_ORDER = ? ";
					pstmt = conn.prepareStatement(updateSql);					
					pstmt.setString(1,assignTo);
					pstmt.setString(2,pickOrder);
					pickIssCount = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;   
				}
				//changed by sankara on 28/09/13 for getting unconfirmed repl orders end.
				totalCount = pickIssCount+count;
			}			
			if( totalCount > 0 )
			{
				System.out.println("totalCount--->"+totalCount);
				errCode = "VPPRCESFUL";
				errString = itmDBAccessEJB.getErrorString("","VPPRCESFUL",userId);
			}
			else
			{
				System.out.println("Updated Rows Count in Pick Ord Hdr and Pick Iss Hdr"+totalCount);
			}

		}
		catch (Exception e)
		{
			isError = true;
			try 
			{
				conn.rollback();
			}
			catch (Exception e1) 
			{
				System.out.println("Gem Message="+e.getCause());
				System.out.println("Complete Message="+e.getMessage());
				e1.printStackTrace();
			}
			e.printStackTrace();
			System.out.println("******Exception"+e.getMessage());
		}
		finally
		{
			try
			{
				if(!isError)
				{						
					conn.commit();					
				}
				else
				{
					conn.rollback();
				}
				if(conn != null)
				{
					if(rs != null) 
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null) 
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
				}
				conn = null;	
			}
			catch (Exception ex)
			{
				isError = true;
				System.out.println("Exception is "+ex.getMessage());
			}
		}
		return errString;
	} //process method

	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		else
		{
			input = input.trim();
		}
		return input;
	}

} // class closed







