/**
 DEVELOPED BY RITESH TIWARI ON 02/11/13 
 FOR WORK ORDER STOCK TRANSFER
 */
package ibase.webitm.ejb.mfg;
import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.ProcessEJB;
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.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.Stateless;
import javax.naming.InitialContext;
import org.apache.axis.client.Service;
import org.apache.axis.client.Call;
import org.apache.axis.encoding.XMLType;
import org.w3c.dom.Document;
import javax.xml.rpc.ParameterMode;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.GenerateSaleOrderPrc;
import ibase.webitm.ejb.dis.adv.StockTransferConf;
//import ibase.webitm.ejb.fin.FinCommon;

@Stateless
public class WoTransferPrc extends ProcessEJB implements WoTransferPrcLocal,WoTransferPrcRemote 
{
 
	static
	{
		System.out.println("-- WoTransferPrc called -- ");
	}
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		Document detailDom = null;
		Document headerDom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		String retStr = "";
		try
		{				
			System.out.println("xmlString[process]::::::::::;;;"+xmlString);
			System.out.println("xmlString2[process]::::::::::;;;"+xmlString2);
			System.out.println("windowName[process]::::::::::;;;"+windowName);
			System.out.println("xtraParams[process]:::::::::;;;"+xtraParams);
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		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 :WoTransferPrc :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
		}
		return retStr;
	
	} //end of confirm method
	public String process(Document dom, Document dom2, String windowName,String xtraParams) throws RemoteException, ITMException 
	{
		System.out.println("WoTransferPrc process called........");
		String sql = "";
		String errString = "";
		String siteCode = "",invstat="",invstat1="";
		String  worder = "",locCodeTo="";
		int cnt=0;
		Connection connOne = null;
		ConnDriver connDriver = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		GenericUtility genericUtility = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		MfgCommon mfgComm  = new MfgCommon();
		try
		{
			genericUtility = new GenericUtility();
			itmDBAccessEJB = new ITMDBAccessEJB();
			connDriver = new ConnDriver();
			connOne = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			
			worder = checkNull(genericUtility.getColumnValue("work_order", dom));
			locCodeTo = checkNull(genericUtility.getColumnValue("loc_code__to", dom));

			connOne.setAutoCommit(false);
			if(worder == null || worder.trim().length()  <= 0)
			{
				errString = itmDBAccessEJB.getErrorString("","VTWKORD1    ","","",connOne);
				return errString;
			}
			if(locCodeTo == null || locCodeTo.trim().length()  <= 0)
			{
				errString = itmDBAccessEJB.getErrorString("","VTLOCCDTO2 ","","",connOne);
				return errString;
			}
			if(worder != null || worder.trim().length()  > 0)
			{
				sql = "select count(*) from workorder where work_order = ?  and status not in ('F') ";
				pstmt = connOne.prepareStatement(sql);
				pstmt.setString(1,worder);
			    rs = pstmt.executeQuery();
			    if(rs.next())
			    {
			    	cnt = rs.getInt(1);
			    }
			    if(rs != null)
			    	rs.close();
			    rs = null;
			    if(pstmt != null)
			    	pstmt.close();
			    pstmt = null;
			    if(cnt == 0)
			    {
			    	errString = itmDBAccessEJB.getErrorString("","VPWOINV    ","","",connOne);
					return errString;
			    }
			}
			if(locCodeTo != null || locCodeTo.trim().length()  > 0)
			{
				cnt=0;
				invstat = mfgComm.getEnvMfg("999999","SUB_ST_INVSTAT", connOne); 
				invstat1 = mfgComm.getEnvMfg("999999","MAIN_ST_INVSTAT", connOne); 
				if(invstat.equalsIgnoreCase(invstat1))
				{
					errString = itmDBAccessEJB.getErrorString("","VTDISPVAR    ","","",connOne);
					return errString;
				}
				sql = "select count(1) from location where loc_code  = ? and inv_stat = ? ";
				pstmt = connOne.prepareStatement(sql);
				pstmt.setString(1,locCodeTo);
				pstmt.setString(2,invstat);
			    rs = pstmt.executeQuery();
			    if(rs.next())
			    {
			    	cnt = rs.getInt(1);
			    }
			    if(rs != null)
			    	rs.close();
			    rs = null;
			    if(pstmt != null)
			    	pstmt.close();
			    pstmt = null;
			    if(cnt == 0)
			    {
			    	errString = itmDBAccessEJB.getErrorString("","VTLOCCDTO1    ","","",connOne);
					return errString;
			    }

			}
			if(errString == null || errString.trim().length() == 0)
			{
				errString = allocStockToWo(worder,locCodeTo,xtraParams,connOne);
				System.out.println("err String from STOCKTRANS ="+errString);
				if((errString != null ) &&  (errString.indexOf("Success") > -1 || errString.indexOf("CONFSUCC") >-1))
				{
					connOne.commit();
					System.out.println("--process completed--");
					errString = itmDBAccessEJB.getErrorString("","VPSUCC1    ","","",connOne);
				}
				else if(errString != null && errString.indexOf("VTITRF") > -1)
				{
					System.out.println("(((((((((((()]]]]]]]]]]]]]]]]");;
					errString = itmDBAccessEJB.getErrorString("","VTITRF    ","","",connOne);
				}
				else 
				{
					
					connOne.rollback();
					System.out.println("--process not completed--");
					errString = itmDBAccessEJB.getErrorString("","VTPRCERR ","","",connOne);
				}
			}
		}
		catch( Exception e)
		{			
				try 
				{
					connOne.rollback();
				} 
				catch (SQLException ex) 
				{
					Logger.getLogger(WoTransferPrc.class.getName()).log(Level.SEVERE, null, ex);
				}
				e.printStackTrace();
		}
		finally
		{		
				try
				{
					if(errString != null && errString.trim().length() > 0)
					{
						
						if(errString.indexOf("VPSUCC1") > -1 )
							
						{
							connOne.commit();
							System.out.println("--transaction commited--");
						}
						else
						{
							connOne.rollback();
							System.out.println("--transaction rollback--");
						}
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;					
					}		
					if( connOne != null && ! connOne.isClosed() )
					{
						connOne.close();
						connOne = null;
					}
					
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
				}
				catch(Exception e)
				{
					System.out.println("Exception : "+e);e.printStackTrace();
					throw new ITMException(e);
				}
			}
		return errString;
	}
	private String allocStockToWo(String worder,String locCodeTo ,String xtraParams, Connection connOne) throws RemoteException, ITMException
	{	
		String  userId = "";
		String  termId = "";
		String sql = "",sql1="", acctCodeInv1 = "",cctrCodeInv1="";
		String itemCode="",tranIdInvAlloc="",reasCode="",refno = "",itemSer="",tranType="";
		Date tranDate = null,orddate = null;
		int notrans =0;
		PreparedStatement pstmt = null,pstmt1= null;
		ResultSet rs = null,rs1=null;
		GenericUtility genericUtility = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		Timestamp currDate = null;
		String retString  = null;
		String siteCode = "",empCode = "";
		String xmlString = "";
		MfgCommon mfgComm  = null;
		StringBuffer xmlBuff = null;
		ArrayList list = null;
		boolean bool = false;
		try   
		{
			list = new ArrayList();
			itmDBAccessEJB = new ITMDBAccessEJB();
			genericUtility = GenericUtility.getInstance();
			mfgComm  = new MfgCommon();
			System.out.println("@@@@@@@ wostock trans.  function called::");
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
			currDate =  java.sql.Timestamp.valueOf(sdf1.format(new java.util.Date()).toString() + " 00:00:00.0");
			
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  System.out.println("--login code--"+userId);
			termId =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId"); System.out.println("--term id--"+termId);
			
			sql = "SELECT EMP_CODE FROM USERS WHERE CODE = ? ";
			pstmt = connOne.prepareStatement(sql);
			pstmt.setString(1, userId);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				empCode = rs.getString("EMP_CODE")== null ?"":rs.getString("EMP_CODE");
			}
			if(rs != null)
				rs.close();
			rs = null;
			if(pstmt != null)
				pstmt.close();
			pstmt = null;
			
			sql = "select item_code,site_code,bom_code,lot_no,lot_sl,ref_no,ord_date " +
				  " from workorder where work_order = ? ";
					
			pstmt = connOne.prepareStatement(sql);
			pstmt.setString(1,worder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				//ordtype = rs.getString("order_type");
				itemCode = checkNull(rs.getString("item_code"));
				siteCode = checkNull(rs.getString("site_code"));
				orddate = rs.getDate("ord_date");
				//duedate = rs.getString("due_date");
			//	bomcode = checkNull(rs.getString("bom_code"));
				//lotno = checkNull(rs.getString("lot_no"));
				//lotsl = checkNull(rs.getString("lot_sl"));
				refno = checkNull(rs.getString("ref_no"));
				
			}
			if(rs != null)
				rs.close();
			rs = null;
			if(pstmt != null)
				pstmt.close();
			pstmt = null;
			
			sql = "select item_ser from item where item_code = ? ";
			pstmt = connOne.prepareStatement(sql);
			pstmt.setString(1,itemCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				itemSer = rs.getString("item_ser");
				
			}
			if(rs != null)
				rs.close();
			rs = null;
			if(pstmt != null)
				pstmt.close();
			pstmt = null;
			
			sql = "select tran_type,tran_date,tran_id  from inv_allocate where WORK_ORDER = ? ";
			pstmt = connOne.prepareStatement(sql);
			pstmt.setString(1,worder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				tranType = checkNull(rs.getString("tran_type"));
				tranDate = rs.getDate("tran_date");
				tranIdInvAlloc = checkNull(rs.getString("tran_id"));
				
			}
			if(rs != null)
				rs.close();
			rs = null;
			if(pstmt != null)
				pstmt.close();
			pstmt = null;
			
			String invstat = mfgComm.getEnvMfg("999999","MAIN_ST_INVSTAT", connOne); 
		
			
			System.out.println("::: XML Cretation::::");
			//sql1 = "select item_code,quantity,site_code,loc_code__alloc,consider_lot_no__issue from workorder_bill where work_order = ? ";
			sql = "select item_code,site_code,lot_no,lot_sl,remarks,line_no,loc_code,quantity from inv_alloc_det where tran_id = ? " +
					" and loc_code in(select loc_code from location where inv_stat = ?)";
			pstmt = connOne.prepareStatement(sql);
			pstmt.setString(1,tranIdInvAlloc);
			pstmt.setString(2,invstat);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				notrans ++;
				xmlBuff = new StringBuffer();
							
				sql1 = "select item_ser from item where item_code = ? ";
				pstmt1 = connOne.prepareStatement(sql1);
				pstmt1.setString(1,rs.getString("item_code"));
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{
					itemSer = rs1.getString("item_ser");
					
				}
				if(rs1 != null)
					rs1.close();
				rs1 = null;
				if(pstmt1 != null)
					pstmt1.close();
				pstmt1 = null;
				
				sql1 = "select reas_code  from inv_alloc_det where tran_id = ? and item_code = ? ";
				pstmt1 = connOne.prepareStatement(sql1);
				pstmt1.setString(1,tranIdInvAlloc);
				pstmt1.setString(2,rs.getString("item_code"));
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{
					reasCode = checkNull(rs1.getString("reas_code"));
				}
				if(rs1 != null)
					rs1.close();
				rs1 = null;
				if(pstmt1 != null)
					pstmt1.close();
				pstmt1 = null;
			
				sql1 = "select acct_code__inv, cctr_code__inv  from stock where item_code = ? and site_code = ?  and loc_code = ? and lot_no = ? and lot_sl = ? ";
				pstmt1 = connOne.prepareStatement(sql1);
				pstmt1.setString(1,checkNull(rs.getString("item_code")));
				pstmt1.setString(2,checkNull(rs.getString("site_code")));
				pstmt1.setString(3,checkNull(rs.getString("loc_code")));
				pstmt1.setString(4,checkNull(rs.getString("lot_no")));
				pstmt1.setString(5,checkNull(rs.getString("lot_sl")));
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{
					 acctCodeInv1 = rs1.getString("acct_code__inv")==null?"1440":rs1.getString("acct_code__inv");
					 cctrCodeInv1 = rs1.getString("cctr_code__inv")==null?"H651":rs1.getString("cctr_code__inv");
				}
				if(rs1 != null)
					rs1.close();
				rs1 = null;
				if(pstmt1 != null)
					pstmt1.close();
				pstmt1 = null;
				
				xmlBuff.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
				xmlBuff.append("<DocumentRoot>");
				xmlBuff.append("<description>").append("Datawindow Root").append("</description>");
				xmlBuff.append("<group0>");
				xmlBuff.append("<description>").append("Group0 description").append("</description>");
				xmlBuff.append("<Header0>");
				xmlBuff.append("<objName><![CDATA[").append("stock_transfer").append("]]></objName>");  
				xmlBuff.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>");
				xmlBuff.append("<objContext><![CDATA[").append("1").append("]]></objContext>");
				xmlBuff.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>");
				xmlBuff.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>");
				xmlBuff.append("<action><![CDATA[").append("SAVE").append("]]></action>");
				xmlBuff.append("<elementName><![CDATA[").append("").append("]]></elementName>");
				xmlBuff.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>");
				xmlBuff.append("<taxKeyValue><![CDATA[").append("").append("]]></taxKeyValue>");
				xmlBuff.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
				xmlBuff.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
				xmlBuff.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>");
				xmlBuff.append("<description>").append("Header0 members").append("</description>");	
				xmlBuff.append("<Detail1 dbID=\"\" domID=\"1\" objName=\"stock_transfer\" objContext=\"1\">");  
				xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
				xmlBuff.append("<tran_id/>");
				xmlBuff.append("<tran_date><![CDATA["+ sdf.format(currDate).toString()+"]]></tran_date>");
				xmlBuff.append("<ref_date><![CDATA[" + sdf.format(orddate).toString() +"]]></ref_date>");
				xmlBuff.append("<tran_type><![CDATA["+ tranType +"]]></tran_type>");
				xmlBuff.append("<site_code><![CDATA["+ checkNull(rs.getString("site_code")) +"]]></site_code>");
				xmlBuff.append("<item_ser><![CDATA[" + itemSer  +"]]></item_ser>");
				xmlBuff.append("<emp_code__aprv><![CDATA["+ empCode +"]]></emp_code__aprv>");
				xmlBuff.append("<ref_id__for><![CDATA["   + worder   +"]]></ref_id__for>");
				xmlBuff.append("<REF_SER__FOR><![CDATA["   + "w_ord"   +"]]></REF_SER__FOR>");

				xmlBuff.append("<reas_code><![CDATA["     + reasCode+" ]]></reas_code>");
				xmlBuff.append("<truck_no><![CDATA[ ]]></truck_no>");
				xmlBuff.append("</Detail1>");
				
				xmlBuff.append("<Detail2 dbID='' domID=\"1\" objName=\"stock_transfer\" objContext=\"2\">"); 
				xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
				xmlBuff.append("<tran_id/>");
				xmlBuff.append("<item_code><![CDATA["+ checkNull(rs.getString("item_code"))+"]]></item_code>");
				xmlBuff.append("<line_no><![CDATA["  + checkNull(rs.getString("line_no"))  +"]]></line_no>");
				xmlBuff.append("<quantity><![CDATA[" + rs.getDouble("quantity") +"]]></quantity>");
				xmlBuff.append("<remarks><![CDATA["  + checkNull(rs.getString("remarks"))  +"]]></remarks>");
				xmlBuff.append("<loc_code__fr><![CDATA[" + checkNull(rs.getString("loc_code")) +"]]></loc_code__fr>");
				xmlBuff.append("<loc_code__to><![CDATA[" + locCodeTo +"]]></loc_code__to>");
				xmlBuff.append("<lot_no__fr><![CDATA["   + checkNull(rs.getString("lot_no")) +"]]></lot_no__fr>");
				xmlBuff.append("<lot_sl__fr><![CDATA["   + checkNull(rs.getString("lot_sl")) +"]]></lot_sl__fr>");
				xmlBuff.append("<lot_no__to><![CDATA["   + checkNull(rs.getString("lot_no"))  +"]]></lot_no__to>");
				xmlBuff.append("<lot_sl__to><![CDATA["   + checkNull(rs.getString("lot_sl")) +"]]></lot_sl__to>");
				xmlBuff.append("<acct_code__dr><![CDATA["+ acctCodeInv1 +"]]></acct_code__dr>");
				xmlBuff.append("<acct_code__cr><![CDATA["+ acctCodeInv1 +"]]></acct_code__cr>");
				xmlBuff.append("<cctr_code__cr><![CDATA["+ cctrCodeInv1 +"]]></cctr_code__cr>");
				xmlBuff.append("<cctr_code__dr><![CDATA["+ cctrCodeInv1 +"]]></cctr_code__dr>");
				xmlBuff.append("</Detail2>");
				xmlBuff.append("</Header0>");
				xmlBuff.append("</group0>");
				xmlBuff.append("</DocumentRoot>");
				xmlString = xmlBuff.toString();
				System.out.println("@@@@@2: xmlString @ "+notrans+":::: "+xmlBuff.toString());
				System.out.println("...............just before savdata() @ "+notrans);
				siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
				System.out.println("== site code == @"+notrans+": "+siteCode);
				retString = saveData(siteCode,xmlString,connOne);
				System.out.println("@@@@@2: retString: @"+notrans+":: "+retString);
				System.out.println("--retString finished--");
				if (retString.indexOf("Success") > -1)
				{
					connOne.commit();
					System.out.println("@@@@@@3: retString @ "+notrans+":::: "+retString);
					String[] arrayForTranId = retString.split("<TranID>");
					int endIndex = arrayForTranId[1].indexOf("</TranID>");
					String tranIdForIssue = arrayForTranId[1].substring(0,endIndex);
					System.out.println("-tranIdForIssue-"+tranIdForIssue);
					list.add(tranIdForIssue);
						
				}
				
				
			}
			if(rs != null)
				rs.close();
			rs = null;
			if(pstmt != null)
				pstmt.close();
			pstmt = null;
			System.out.println("Total transaction : "+notrans+" total list size"+list.size());
			StockTransferConf stconf = new StockTransferConf();
			if(list.size() > 0){
			for(int i=0;i<= list.size()-1;i++)
			{
				retString = stconf.confirm((String)list.get(i),xtraParams,"");
				System.out.println("retString from confirmStocktrans  :: @"+i+":: "+retString);//CONFSUCC
				bool = true;
			}}
			if(bool){
			if((retString != null ) &&  retString.indexOf("Success") > -1 || retString.indexOf("CONFSUCC") >-1)
			{
			
				sql = " update inv_alloc_det set loc_Code = ? where tran_id = ? ";
				pstmt = connOne.prepareStatement(sql);
				pstmt.setString(1,locCodeTo);
				pstmt.setString(2,tranIdInvAlloc);
				int rows = pstmt.executeUpdate();
				System.out.println("--update rows in process --"+ rows);
				if(pstmt != null)
					pstmt.close();
				pstmt = null;
			}}
		}
		
		catch(Exception e)
		{
			e.printStackTrace();
			retString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}		
				if(pstmt1 != null)
				{
					pstmt1.close();
					pstmt1 = null;
				}
				if(rs1 != null)
				{
					rs1.close();
					rs1 = null;
				}
					
			}
			catch(Exception e)
			{
				e.printStackTrace();
				retString = e.getMessage();
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result [" + retString + "]");
		return retString;
	}
	private String checkNull(String input) 
	{
		if(input == null)
		{
			input = "";
		}
		return input;
	}
	private String saveData(String siteCode,String xmlString, Connection conn) throws ITMException
	{
		System.out.println("saving data...........");
		InitialContext ctx = null;
		String retString = null;
		MasterStatefulLocal masterStateful = null; // for ejb3
		try
		{
			AppConnectParm appConnect = new AppConnectParm();
			ctx = new InitialContext(appConnect.getProperty());
			masterStateful = (MasterStatefulLocal)ctx.lookup("ibase/MasterStatefulEJB/local");
			System.out.println("-----------masterStateful------- " + masterStateful);
			String [] authencate = new String[2];
			authencate[0] = "";
			authencate[1] = "";
			System.out.println("xmlString to masterstateful [" + xmlString + "]");
			retString = masterStateful.processRequest(authencate, siteCode, true, xmlString,true,conn);
			System.out.println("--retString - -"+retString);
		}
		catch(ITMException itme)
		{
			System.out.println("ITMException :CreateDistOrder :saveData :==>");
			throw itme;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception :CreateDistOrder :saveData :==>");
			throw new ITMException(e);
		}
		return retString;
	}
}