/**
 DEVELOPED BY  : RITESH ON 10/SEP/2014
 PURPOSE : STOCK TRANSEFR FROM WIP LOCATION THROUGH WIZARD (REG : M14ESUN002)
 */
package ibase.webitm.ejb.mfg;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.adv.StockTransferConf;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;

import java.rmi.RemoteException;
import java.sql.*;
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;
import ibase.utility.CommonConstants;
@javax.ejb.Stateless
public class WoTrfWizPos extends ValidatorEJB implements WoTrfWizPosLocal, WoTrfWizPosRemote
{
	public String postSave()throws RemoteException,ITMException
	{
		return "";
	}
	public String postSave(String domString, String tranId,String editFlag, String xtraParams, Connection conn1 ) throws RemoteException,ITMException
	{
		System.out.println("post save called )( 05092014");
		String errString = "";
		String  sSQL="",itemCode = "", locCode = "",locCodeTo="";
		String siteCode = "";
		String lotNo = "";
		String lotSl = "";
		String itemSer="",tranDate="",refSerFor="",siteCodeDom="",quantityStr="";
		String campaignNo="";
		double quantity = 0d;
		String acctCodeInv1="" ,cctrCodeInv1="";
		NodeList parentNodeList = null;
		Node parentNode = null;
		int parentNodeListLength = 0;
		int ctr = 0;
		int lineNo = 0;
		boolean insertFlag =  false;
		Document dom = null;
		PreparedStatement pstmt = null;		
        ResultSet rs = null;		
        java.util.Date utilDate = new java.util.Date();
		String sql = "";
		String loginCode= "",empCode="",genratedTranId=""; 
		Connection conn = null;
		ConnDriver connDriver = null;
		ArrayList list  = new ArrayList();
		HashMap stockMap = new HashMap();
		
		java.sql.Timestamp expDate = null;
		java.sql.Timestamp mfgDate = null;
		java.sql.Timestamp restestDate = null;
		String packCode = "";
		String siteCodeMfg = "";
		String packInstr = "";
		String suppCodeMfg = "";
		String unitAlt = "";
		String batchNo = "";
		String unit = "";
		String grade = "";
		String remarks1 = "";
		String dimension = "";
		String acctCodeDr = "";
		String ediOption = "";
		String dataStr = "";
		String chgTerm = "";
		double totWoQty= 0.0;
		double grossRate = 0d;
		double convQtyStduom = 0d;
		double batchSize = 0d;
		double stkGrossRate = 0d;
		double stkRate = 0d;
		double actualRate = 0d;
		double stkQuantity = 0,holdQuantity = 0;
		double holdQty = 0;
		double potencyPerc=0,grossWeight=0,tareWeight=0,netWeight=0;
		double noArt = 0d;
		
        try
		{
        	System.out.println("domString::{"+domString+"}");
        	connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;   
			conn.setAutoCommit(false);
			GenericUtility genericUtility = GenericUtility.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
			Timestamp date1 = Timestamp.valueOf(sdf.format(utilDate).toString() + " 00:00:00.0");
			SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
			Timestamp currDate =  java.sql.Timestamp.valueOf(sdf1.format(new java.util.Date()).toString() + " 00:00:00.0");
			
			
			dom = genericUtility.parseString(domString); 
			siteCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" );
			parentNodeList = dom.getElementsByTagName("Detail3");
			parentNodeListLength = parentNodeList.getLength();
			// added on 25/MAR/14 BY RITESH START
			loginCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			System.out.println("********* login code ["+loginCode+"]");
			sql = "SELECT EMP_CODE FROM USERS WHERE CODE = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setString(1, loginCode);						
			rs = pstmt.executeQuery();	
			if( rs.next() )
			{
				empCode = rs.getString("EMP_CODE");
			}
			if(rs != null)
			{	
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{	
				pstmt.close();
				pstmt = null;
			}	
			campaignNo= ((genericUtility.getColumnValueFromNode("campaign_no",dom.getElementsByTagName("Detail1").item(ctr))).trim());

			siteCodeDom= ((genericUtility.getColumnValueFromNode("site_code",dom.getElementsByTagName("Detail2").item(ctr))).trim());
			itemSer= ((genericUtility.getColumnValueFromNode("item_ser",dom.getElementsByTagName("Detail2").item(ctr))).trim());
			tranDate= ((genericUtility.getColumnValueFromNode("tran_date",dom.getElementsByTagName("Detail2").item(ctr))).trim());
			refSerFor= ((genericUtility.getColumnValueFromNode("ref_ser__for",dom.getElementsByTagName("Detail2").item(ctr))).trim());

			System.out.println("empCode::::["+empCode+"]"+"siteCodeDom["+siteCodeDom+"] itemSer["+itemSer+"] tranDate["+tranDate+"] refSerFor["+refSerFor+"] locCodeTo["+locCodeTo+"]");
			genratedTranId = generateTranId( "w_stock_transfer",siteCode, conn );
			sql = "INSERT INTO STOCK_TRANSFER(SITE_CODE,ITEM_SER,REF_SER__FOR,TRAN_DATE,EMP_CODE__APRV,TRAN_ID,CONFIRMED) VALUES(?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement( sql );
			pstmt.setString(1, siteCodeDom);
			pstmt.setString(2, itemSer);	
			pstmt.setString(3, refSerFor);	
			pstmt.setString(4, tranDate);	
			pstmt.setString(5, " ");
			pstmt.setString(6, genratedTranId);
			pstmt.setString(7, "N");
			pstmt.executeUpdate();	
			if(pstmt != null)
			{	
				pstmt.close();
				pstmt = null;
			}	
			
			sql = " SELECT NO_ART FROM WORKORDER WHERE WORK_ORDER IN ( SELECT WORK_ORDER FROM WORKORDER WHERE CAMPGN_NO = ? )  ORDER BY ORD_DATE  ";
			pstmt = conn.prepareStatement( sql );
			pstmt.setString(1, campaignNo);						
			rs = pstmt.executeQuery();	
			if( rs.next() )
			{
				noArt  = rs.getDouble("NO_ART");
			}
			if(rs != null)
			{	
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{	
				pstmt.close();
				pstmt = null;
			}
			for(ctr = 0; ctr < parentNodeListLength ; ctr++ )
			{
				lineNo ++;
				System.out.println(" parentNodeListLength  - ctr ::"+ctr);
//				locCodeTo= ((genericUtility.getColumnValueFromNode("loc_code__to",dom.getElementsByTagName("Detail3").item(ctr))).trim());
//				System.out.println("locCodeTo :: "+locCodeTo);
//				locCodeTo = genericUtility.getColumnValue("loc_code__to", dom);
//				System.out.println("locCodeTo from dom ::"+locCodeTo);
				parentNode = parentNodeList.item(ctr);
				itemCode = genericUtility.getColumnValueFromNode("item_code", parentNode);
				locCode = genericUtility.getColumnValueFromNode("loc_code", parentNode);
				lotNo = genericUtility.getColumnValueFromNode("lot_no", parentNode);
				lotSl = genericUtility.getColumnValueFromNode("lot_sl", parentNode);
				locCodeTo = genericUtility.getColumnValueFromNode("loc_code__to", parentNode);
				siteCode = genericUtility.getColumnValueFromNode("site_code", parentNode);
				
				quantityStr = genericUtility.getColumnValueFromNode("quantity", parentNode);
				list.add(locCodeTo+":"+itemCode+":"+lotNo+":"+lotSl+":"+locCode+":"+siteCode+":"+quantityStr);
				if(quantityStr != null && quantityStr.trim().length() > 0)
				{
					quantity = Double.parseDouble(quantityStr);
				}
				System.out.println("ITEM CODE["+itemCode+"] SITE CODE["+siteCode+"] LOC CODE["+locCode+"] LOT_NO["+lotNo+"] LOT SL["+lotSl+"]"+"locCodeTo["+locCodeTo+"]");
				sql = "select acct_code__inv, cctr_code__inv  from stock where item_code = ? and site_code = ?  and loc_code = ? and lot_no = ? and lot_sl = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,itemCode);
				pstmt.setString(2,siteCode);
				pstmt.setString(3,locCode);
				pstmt.setString(4,lotNo);
				pstmt.setString(5,lotSl);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					 acctCodeInv1 = rs.getString("acct_code__inv")==null?"1440":rs.getString("acct_code__inv");
					 cctrCodeInv1 = rs.getString("cctr_code__inv")==null?"H651":rs.getString("cctr_code__inv");
				}
				if(rs != null)
					rs.close();
				rs = null;
				if(pstmt != null)
					pstmt.close();
				pstmt=null;
				
				sql = " INSERT INTO STOCK_TRANSFER_DET(ITEM_CODE,LINE_NO,QUANTITY,REMARKS,LOC_CODE__FR,LOC_CODE__TO,LOT_NO__FR,LOT_SL__FR,LOT_NO__TO,LOT_SL__TO,ACCT_CODE__DR,ACCT_CODE__CR,CCTR_CODE__CR,CCTR_CODE__DR,TRAN_ID,NO_ART) "+
					 	" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				
				pstmt = conn.prepareStatement( sql );
				pstmt.setString(1, itemCode);
				pstmt.setInt(2, lineNo);	
				pstmt.setDouble(3, quantity);	
				pstmt.setString(4, "Generated from stock transfer wip location wizard ");	
				pstmt.setString(5, locCode);
				pstmt.setString(6, locCodeTo);
				pstmt.setString(7, lotNo);
				pstmt.setString(8, lotSl);
				pstmt.setString(9, lotNo);
				pstmt.setString(10, lotSl);
				pstmt.setString(11, acctCodeInv1);
				pstmt.setString(12, acctCodeInv1);
				pstmt.setString(13, cctrCodeInv1);
				pstmt.setString(14, cctrCodeInv1);
				pstmt.setString(15, genratedTranId);
				pstmt.setDouble(16, noArt);
				if(pstmt.executeUpdate() > 0) insertFlag = true;
				else 	insertFlag = false;
				if(pstmt != null)
				{	
					pstmt.close();
					pstmt = null;
				}	
			}
			System.out.println(" detailData final tree ::"+list.size()+"::"+list);
			if(insertFlag)
			{
				conn.commit();System.out.println(" connection committed @@@@@@");
				StockTransferConf stconf = new StockTransferConf();
				errString = stconf.confirm(genratedTranId,xtraParams,"");
				System.out.println("retString from StockTransferConf  :: @:: "+errString);//CONFSUCC
				if(errString.indexOf("CONFSUCC") > -1)
				{ 
					
			      int count = 0,updInv = 0;
				  for(int i  = 0;i <= list.size()-1;i++)
				  {
					ArrayList errList  = new ArrayList();
					String detDate = (String)list.get(i);System.out.println("String detDate = "+ detDate );
					String detDataArr[] = detDate.split(":");System.out.println("String detDataArr[] = "+ detDataArr );
					locCodeTo  = detDataArr[0];
					itemCode = detDataArr[1];
					lotNo = detDataArr[2];
					lotSl = detDataArr[3];
					locCode = detDataArr[4];
					siteCode = detDataArr[5];
					quantityStr = detDataArr[6];
					if(quantityStr != null && quantityStr.trim().length() > 0)
					{
						quantity = Double.parseDouble(quantityStr);
					}
					stockMap.put("item_code", itemCode);
					stockMap.put("site_code", siteCode);
					stockMap.put("loc_code", locCode);
					stockMap.put("lot_no", lotNo);
					stockMap.put("lot_sl", lotSl);
//					double quantity = 0.0;
//					if(allocQty != null)
//					{
//						quantity = Double.parseDouble(allocQty);
//					}
					stockMap.put("quantity", ""+quantity);
					String invStatLocCodeTo = "";
					sql = "SELECT INV_STAT FROM LOCATION WHERE LOC_CODE = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,locCodeTo);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						invStatLocCodeTo = checkNull(rs.getString("INV_STAT"));
						
					}
					if(rs != null)
						rs.close();
					rs = null;
					if(pstmt != null)
						pstmt.close();
					pstmt = null;

//					//if(invStatLocCodeTo.trim().equalsIgnoreCase(locCodeTo.trim()) && invStatLocCodeTo.trim().equalsIgnoreCase(partialGrlLoc.trim()))
//					{
//						stockMap.put("no_art", allocQty);
//					}
//					else
//					{
						stockMap.put("no_art", "0");
//					}

					stockMap.put("tran_ser", "XFRX");
					stockMap.put("acct_code__cr",acctCodeInv1);
					stockMap.put("cctr_code__cr",cctrCodeInv1);
					stockMap.put("acct_code_inv",acctCodeInv1);
					stockMap.put("cctr_code_inv",cctrCodeInv1);
					
					sql = "select acct_code__inv, cctr_code__inv, "
							+ "exp_date, pack_code, mfg_date, site_code__mfg, "
							+ "pack_instr, supp_code__mfg, retest_date, "
							+ "gross_rate, rate, conv__qty_stduom, unit__alt, batch_no, batch_size, "
							+ "unit, grade, remarks, dimension, quantity ,hold_qty,actual_rate ,partial_used  "
							+ ",potency_perc, inv_stat, gross_weight, tare_weight, net_weight, lot_sl__org 	  "   
	                        + "from stock "
							+ "where item_code = ? "
							+ "and site_code = ? "
							+ "and loc_code = ? "
							+ "and lot_no = ? "
							+ "and lot_sl = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,itemCode);
					pstmt.setString(2,siteCode);
					pstmt.setString(3,locCode);
					pstmt.setString(4,lotNo);
					pstmt.setString(5,lotSl);
					rs = pstmt.executeQuery();
					if ( rs.next() )
					{
						expDate = rs.getTimestamp("exp_date");
						packCode = rs.getString("pack_code");
						mfgDate = rs.getTimestamp("mfg_date");
						siteCodeMfg = rs.getString("site_code__mfg");
						packInstr = rs.getString("pack_instr");
						suppCodeMfg = rs.getString("supp_code__mfg");
						restestDate = rs.getTimestamp("retest_date");
						stkGrossRate = rs.getDouble("gross_rate");
						stkRate = rs.getDouble("rate");
						System.out.println("stkGrossRate"+stkGrossRate+"stkRate"+stkRate);
						convQtyStduom = rs.getDouble("conv__qty_stduom");
						unitAlt = rs.getString("unit__alt");
						batchNo = rs.getString("batch_no");
						batchSize = rs.getDouble("batch_size");
						unit = rs.getString("unit");
						grade = rs.getString("grade");
						remarks1 = rs.getString("remarks");
						dimension = rs.getString("dimension");
						stkQuantity = rs.getDouble("quantity");
						holdQuantity = rs.getDouble("hold_qty");
						actualRate = rs.getDouble("actual_rate");
						//partialUsed =  checkNull(rs.getString("partial_used"));
						
						potencyPerc = rs.getDouble("potency_perc");    
						grossWeight = rs.getDouble("gross_weight");    
						tareWeight = rs.getDouble("tare_weight");      
						netWeight = rs.getDouble("net_weight");        
						//lotSlOrg = rs.getString("lot_sl__org"); 
					
						stockMap.put("rate",Double.toString(stkRate));
						stockMap.put("gross_rate",Double.toString(stkGrossRate));
						System.out.println("stkGrossRate:::"+Double.toString(stkRate)+"stkRate::::"+Double.toString(stkGrossRate));
						stockMap.put("tran_id", genratedTranId );
						stockMap.put("tran_date",currDate  );
	
						stockMap.put("qty_stduom", ""+quantity);
						stockMap.put("unit",unit);
						stockMap.put("grade",grade);
						stockMap.put("remarks",remarks1);
						stockMap.put("dimension",dimension);
						stockMap.put("tran_type", "I");
	
						stockMap.put("exp_date", expDate);
						stockMap.put("pack_code", packCode);
						stockMap.put("mfg_date", mfgDate);
						stockMap.put("site_code__mfg", siteCodeMfg);
						stockMap.put("pack_instr", packInstr);
						stockMap.put("supp_code__mfg", suppCodeMfg);
						stockMap.put("retest_date", restestDate);
						stockMap.put("conv__qty_stduom", ""+convQtyStduom);
						stockMap.put("unit__alt", unitAlt);
						stockMap.put("batch_no", batchNo);
						stockMap.put("batch_size", ""+batchSize);
						stockMap.put("actual_rate", actualRate);
						
					}
					if(rs != null)
						rs.close();
					rs = null;
					if(pstmt != null)
						pstmt.close();
					pstmt = null;
					
					boolean isError = false;
					String retString = "";
					WoTransferPrc mfgStkTrf = new WoTransferPrc();
					errList = mfgStkTrf.allocTransfer(siteCode,itemCode,locCode ,
							  lotNo,lotSl,locCodeTo ,lotNo,lotSl,
							  quantity,stockMap, xtraParams , conn );
					System.out.println("FROM ALLOC TRANSFER=="+errList.toString());
					retString = errList.get(0).toString() ;
					System.out.println("FROM ALLOCTRANSFER 0 : "+retString);
					if ( retString != null && retString.trim().length() > 0 )
					{
						isError = true;
					}
					stockMap.clear();
					if(isError == false)
					{
						
						conn.commit();
						///////////////////////////////////////////////////////////////////
						sql =   " UPDATE WORKORDER_ISSDET SET LOC_CODE  = ? "+
								" WHERE TRAN_ID IN "+
								" (SELECT TRAN_ID FROM WORKORDER_ISS " +
								" WHERE "+
								" WORK_ORDER "+  
								" IN "+
								" (SELECT WORK_ORDER FROM WORKORDER WHERE CAMPGN_NO = ? AND WORK_ORDER IN "+
								" (SELECT WORK_ORDER FROM INV_ALLOCATE WHERE TRAN_ID IN "+
								" (SELECT TRAN_ID FROM INV_ALLOC_DET )))) "+
								" AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,locCodeTo);
						pstmt.setString(2,campaignNo);
						pstmt.setString(3,itemCode);
						pstmt.setString(4,lotNo);
						pstmt.setString(5,lotSl);
						pstmt.setString(6,locCode);
						count = count+pstmt.executeUpdate();
						if(pstmt != null)
							pstmt.close();
						pstmt=null;
						
						sql = " update inv_alloc_det set loc_code = ? where tran_id in ( SELECT TRAN_ID FROM INV_ALLOCATE WHERE WORK_ORDER  IN (SELECT WORK_ORDER FROM WORKORDER WHERE CAMPGN_NO = ? )) and item_code  = ? and lot_no = ? and lot_sl = ? and loc_code =  ? and site_code = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,locCodeTo);
						pstmt.setString(2,campaignNo);
						pstmt.setString(3,itemCode);
						pstmt.setString(4,lotNo);
						pstmt.setString(5,lotSl);
						pstmt.setString(6,locCode);
						pstmt.setString(7,siteCode);
						updInv = updInv+pstmt.executeUpdate();
						if(pstmt != null)
							pstmt.close();
						pstmt = null;
						
					} // if - mfg ctk trf 
					
				  }// end of for
				  	System.out.println( " location updated in workorder iss @ effected lines::"+count);
					System.out.println( " location updated in inventory detail @ effected lines::"+count);
				}
			}else
			{
				conn.rollback();System.out.println(" connection rollback @@@@@@");
			}
		}
		catch(Exception e)
		{
			try
			{
				conn.rollback();System.out.println("  EXCEPTION CATCHED - connection rollback @@@@@@ ");
				System.out.println("Exception "+e.getMessage());
				e.printStackTrace();			
				throw new ITMException(e);
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
		}
		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 String generateTranId( String windowName,String siteCode, Connection conn )throws ITMException
{
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	String selSql = "";
	String tranId = "";
	String tranSer = "";
	String keyString = "";
	String keyCol = "";
	String xmlValues = "";
	java.sql.Timestamp currDate = null;
	GenericUtility genericUtility = GenericUtility.getInstance();
	try
    {

		SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());

		currDate = new java.sql.Timestamp(System.currentTimeMillis());
		String currDateStr = sdfAppl.format(currDate);

		selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
		pstmt = conn.prepareStatement(selSql);
		pstmt.setString( 1, windowName );
		rs = pstmt.executeQuery();
		if (rs.next())
		{
				keyString = rs.getString("KEY_STRING");
				keyCol = rs.getString("TRAN_ID_COL");
				tranSer = rs.getString("REF_SER");
		}
		rs.close();rs = null;
		pstmt.close();pstmt = null;
		System.out.println("keyString :"+keyString);
		System.out.println("keyCol :"+keyCol);
		System.out.println("tranSer :"+tranSer);

		xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
		xmlValues = xmlValues + "<Header></Header>";
		xmlValues = xmlValues + "<Detail1>";
		xmlValues = xmlValues +        "<tran_id></tran_id>";
		xmlValues = xmlValues +        "<site_code>" + siteCode + "</site_code>";
		xmlValues = xmlValues +        "<tran_date>" + currDateStr + "</tran_date>";
		xmlValues = xmlValues + "</Detail1></Root>";
		System.out.println("xmlValues  :["+xmlValues+"]");
		TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
		tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
		System.out.println("tranId :"+tranId);
     }
	catch (SQLException ex)
	{
		System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
		ex.printStackTrace();
		throw new ITMException(ex);
	}
	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.out.println("@@@@@@@@@@@@@@@@@@@@@@tranId[[[[[[[[[[["+tranId+"]]]]]]]]]]]]]]]");
    return tranId;
 }//end of generateTranTd()
private String checkNull(String input) 
{
	if(input == null)
	{
		input = "";
	}
	return input;
}
}


