/*******************************************    
Title : DocTranPostSaveEJB
    Date  : 03/08/12
    Author: CPandey

 ********************************************************/
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 DocTranPos extends ValidatorEJB implements DocTranPosLocal, DocTranPosRemote
{
	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 = "";
		String itemCode = "",siteCode = "",locCode = "",lotNo = "",lotSl = "",locCodeSys = "",
				saleOrder = "",lineNoSord = "",replOrder = "",retString = "";
		double quantity = 0.0,noArt = 0.0;
		int totOrdDet = 0,lineNo = 0;
		int ordDetScaned = 0;
		boolean toCommit = false;

		java.sql.Timestamp expDate = null,mfgDate = null,restestDate = null;
		Document dom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();

		java.util.Date currDate1 = new java.util.Date();

		java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
		StockUpdate stkUpdate = null;
		String acctCodeDr = "",cctrCodeDr = "",packCode = "",unit = "",grade = "",remarks = "",
				unitAlt = "",stagingLoc = "", batchNo = "",siteCodeMfg = "",packInstr = "",
				suppCodeMfg = "",dimension = "",locCodeTo = "";
		String updateSql = "";
		boolean isError = false;
		String empCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" );
		//StockUpdate stkUpdate = null;
		InvAllocTraceBean invAllocTrace = null;
		HashMap strAllocate = null;
		DistCommon distCommon = new DistCommon();
		stagingLoc = distCommon.getDisparams( "999999", "STAGING_LOC", conn );
		stkUpdate = new StockUpdate();
		invAllocTrace = new InvAllocTraceBean();		
		double stkRate = 0.0,stkGrossRate = 0.0,convQtyStduom = 0.0,batchSize = 0.0;
		PreparedStatement pstmt = null,pstmtStock = null,pstmtUpd = null;
		ResultSet rs = null,rsStock = null;
		HashMap updateRowMap = null;
		PickIssConf pickConf = new PickIssConf();
		try
		{
			String applDateFormat = genericUtility.getApplDateFormat();
			String dbDateFormat = genericUtility.getDBDateFormat();
			SimpleDateFormat sdf = new SimpleDateFormat(applDateFormat);
			String currDateStr = sdf.format(currDate1);
			java.sql.Timestamp tranDate = java.sql.Timestamp.valueOf(genericUtility.getValidDateString( currDateStr , applDateFormat, dbDateFormat ) + " 00:00:00.0") ;
			conn.setAutoCommit(false);

			dom = genericUtility.parseString(domString);

			//palletNo = genericUtility.getColumnValue("pallet_no", dom, "1", "1");
			//locCodeSys = genericUtility.getColumnValue("loc_code__sys", dom, "1", "1");
			System.out.println("Location code to ->>["+locCodeSys+"]");
			//locCodeTo = checkNull(genericUtility.getColumnValue("loc_code__to", allFormDataDom, "3" ));
			pickOrder = genericUtility.getColumnValueFromNode("pick_order", dom.getElementsByTagName("Detail2").item(0));
			System.out.println("palert no from generic utility ["+palletNo+"]");
			sql = "SELECT HDR.PICK_TYPE,HDR1.PICK_ORDER FROM PICK_ORD_HDR HDR,PICK_ISS_HDR HDR1 WHERE HDR.PICK_ORDER = HDR1.PICK_ORDER AND HDR1.PALLET_NO = ?";//PICK_ISS_HDR
			pstmt = conn.prepareStatement(sql);
			pstmt.setString( 1, palletNo);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				pickType = rs.getString("pick_type")==null?"":rs.getString("pick_type");
				pickOrder = rs.getString("pick_order")==null?"":rs.getString("pick_order");
				System.out.println("pick order and pick type----  ["+palletNo+"] and pickType is ----["+pickType+"]");
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;

			//Update the carton master 
			sql = "UPDATE CARTON_MASTER SET STATUS = 'U' WHERE CARTON_NO IN(SELECT CARTON_NO FROM PICK_ISS_DET WHERE PICK_ORDER = ? )";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pickOrder);
			int updCnt = pstmt.executeUpdate();
			pstmt.close(); pstmt = null;
			if(updCnt > 0)
			{
				System.out.println("---row updated sucessfully of master pick---->"+updCnt);
			}
			//validation for null
			stagingLoc = distCommon.getDisparams( "999999", "STAGING_LOC", conn )==null?"":distCommon.getDisparams( "999999", "STAGING_LOC", conn );
			//allocation for dock location 
			
			sql = "select acct_code__inv, cctr_code__inv, "
					+ "exp_date, pack_code, mfg_date, site_code__mfg, "
					+ "pack_instr, supp_code__mfg, retest_date, rate, "
					+ "gross_rate, conv__qty_stduom, unit__alt, batch_no, batch_size, unit, grade, remarks, dimension "
					+ "from stock "
					+ "where item_code = ? "
					+ "and site_code = ? "
					+ "and loc_code = ? "
					+ "and lot_no = ? "
					+ "and lot_sl = ? ";
			pstmtStock = conn.prepareStatement(sql);

			sql = "SELECT R.LINE_NO LINE_NO, R.ITEM_CODE ITEM_CODE, R.SITE_CODE SITE_CODE, R.LOC_CODE LOC_CODE, "
					+ " R.LOT_NO LOT_NO, R.LOT_SL LOT_SL, R.QUANTITY QUANTITY, R.NO_ART NO_ART, "
					+ " R.LOC_CODE__TO LOC_CODE__TO, O.SALE_ORDER SALE_ORDER, O.LINE_NO__SORD LINE_NO__SORD, O.PICK_ORDER,H.PICK_TYPE  "
					+ " FROM PICK_ISS_DET R,  PICK_ORD_DET O,PICK_ORD_HDR H" 
					+ " WHERE R.TRAN_ID = ? "
					+ " AND R.PICK_ORDER = O.PICK_ORDER "   
					+"AND H.PICK_ORDER = O.PICK_ORDER"  
					+ " AND O.LINE_NO = R.LINE_NO__ORD " 
					+" AND R.LOC_CODE <> R.LOC_CODE__TO ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			while(rs.next())
			{

				itemCode = rs.getString( "item_code" );
				siteCode = rs.getString( "site_code" );
				locCode = rs.getString( "loc_code" );
				lotNo = rs.getString( "lot_no" );
				lotSl = rs.getString( "lot_sl" );
				quantity = rs.getDouble( "quantity" );
				noArt = rs.getDouble( "no_art" );
				locCodeTo = rs.getString( "loc_code__to" );
				saleOrder = rs.getString( "SALE_ORDER" );
				lineNoSord = rs.getString( "LINE_NO__SORD" );
				replOrder = rs.getString( "PICK_ORDER" );
				pickType = rs.getString( "PICK_TYPE" );
				System.out.println("pick type from hdr->> ["+pickType+"]");

				// 16/10/11 manoharan check whether allocation against sales order is there
				// if allocation against sales order found change allocation to point to loc_code__to
				// and deallocate the same for loc_code in stock
				// update sordalloc with loc_code__to
				// This utility methods to update old and new location should be in 
				// a separate probably in stockupdate so that we can call the same from various places 
				//
				// 
				lineNo = rs.getInt( "LINE_NO" );

				pstmtStock.setString(1,itemCode);
				pstmtStock.setString(2,siteCode);
				pstmtStock.setString(3,locCode);
				pstmtStock.setString(4,lotNo);
				pstmtStock.setString(5,lotSl);
				rsStock = pstmtStock.executeQuery();
				if ( rsStock.next() )
				{ 

					acctCodeDr = rsStock.getString("acct_code__inv");
					cctrCodeDr = rsStock.getString("cctr_code__inv");

					expDate = rsStock.getTimestamp("exp_date");
					packCode = rsStock.getString("pack_code");
					mfgDate = rsStock.getTimestamp("mfg_date");
					siteCodeMfg = rsStock.getString("site_code__mfg");
					packInstr = rsStock.getString("pack_instr");
					suppCodeMfg = rsStock.getString("supp_code__mfg");
					restestDate = rsStock.getTimestamp("retest_date");
					stkRate = rsStock.getDouble("rate");
					stkGrossRate = rsStock.getDouble("gross_rate");
					convQtyStduom = rsStock.getDouble("conv__qty_stduom");
					unitAlt = rsStock.getString("unit__alt");
					batchNo = rsStock.getString("batch_no");
					batchSize = rsStock.getDouble("batch_size");
					unit = rsStock.getString("unit");
					grade = rsStock.getString("grade");
					remarks = rsStock.getString("remarks");
					dimension = rsStock.getString("dimension");
				}
				rsStock.close(); rsStock = null;
				pstmtStock.clearParameters();
				updateRowMap.put("item_code", itemCode);
				updateRowMap.put("site_code", siteCode);
				updateRowMap.put("loc_code", stagingLoc);
				updateRowMap.put("lot_no", lotNo);
				updateRowMap.put("lot_sl", lotSl);
				updateRowMap.put("quantity", ""+quantity);
				updateRowMap.put("no_art", noArt);
				updateRowMap.put("tran_ser", "PICISS");
				updateRowMap.put("acct_code__cr",acctCodeDr);
				updateRowMap.put("cctr_code__cr",cctrCodeDr);
				updateRowMap.put("acct_code_inv",acctCodeDr);
				updateRowMap.put("cctr_code_inv",cctrCodeDr);
				updateRowMap.put("rate",Double.toString(stkRate));
				updateRowMap.put("gross_rate",Double.toString(stkGrossRate));
				updateRowMap.put("tran_id", tranId );
				// 17/10/11 manoharan
				updateRowMap.put("line_no", "" + lineNo );
				// end 17/10/11 manoharan
				updateRowMap.put("tran_date", tranDate );
				updateRowMap.put("tran_type","I"); 

				updateRowMap.put("qty_stduom", ""+quantity);
				updateRowMap.put("unit",unit);
				updateRowMap.put("grade",grade);
				updateRowMap.put("remarks",remarks);
				updateRowMap.put("dimension",dimension);

				//updateRowMap.put("tran_type", "I");//Creating issue

				//changed by gulzar on 12/24/2011
				updateRowMap.put("exp_date", expDate);
				updateRowMap.put("pack_code", packCode);
				updateRowMap.put("mfg_date", mfgDate);
				updateRowMap.put("site_code__mfg", siteCodeMfg);
				updateRowMap.put("pack_instr", packInstr);
				updateRowMap.put("supp_code__mfg", suppCodeMfg);
				updateRowMap.put("retest_date", restestDate);
				updateRowMap.put("conv__qty_stduom", ""+convQtyStduom);
				updateRowMap.put("unit__alt", unitAlt);
				updateRowMap.put("batch_no", batchNo);
				updateRowMap.put("batch_size", ""+batchSize);
				//End changes by gulzar on 12/24/2011
				retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
				if ( retString != null && retString.trim().length() > 0 )
				{
					isError = true;
					break;
				}
					updateRowMap.put("loc_code", locCodeTo);
//				else 
//				{
//					updateRowMap.put("loc_code", locCodeTo);
//				}
				updateRowMap.put("tran_type", "R");//Creating receipt

				retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
				if ( retString != null && retString.trim().length() > 0 )
				{
					isError = true;
					break;
				}

				updateRowMap.clear();
				// 16/10/11 manoharan check whether allocation/hold are there
				// if so update the same
				//added by cpandey on 
				//if(!("A".equalsIgnoreCase(pickType)))
				//{
				strAllocate = new HashMap();
				strAllocate.put("tran_date",tranDate);
				strAllocate.put("ref_ser","PICISS");
				strAllocate.put("ref_id",tranId);
				strAllocate.put("ref_line", "" + lineNo);
				strAllocate.put("site_code",siteCode);
				strAllocate.put("item_code",itemCode);
				strAllocate.put("loc_code",locCodeTo);
				strAllocate.put("lot_no",lotNo);
				strAllocate.put("lot_sl",lotSl);
				strAllocate.put("alloc_qty",new Double(quantity));
				strAllocate.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
				strAllocate.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
				strAllocate.put("chg_win","w_pick_iss");
				//}

				retString = invAllocTrace.updateInvallocTrace(strAllocate, conn);
				if ( retString != null && retString.trim().length() > 0 )
				{
					isError = true;
					break;
				}
				System.out.println("retString ::: " + retString);
				if (retString != null && retString.trim().length() > 0 )
				{
					isError = true;
					break;
				}
				strAllocate = null;
				// end 16/10/11 manoharan allocation stock for new location
				// 16/10/11 manoharan update sordalloc
//				updateSql = "UPDATE SORDALLOC SET LOC_CODE = ? "
//						+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
//						+ " AND ITEM_CODE = ? "
//						+ " AND LOC_CODE = ? "
//						+ " AND LOT_NO = ? "
//						+ " AND LOT_SL = ? "
//						+ " AND QTY_ALLOC > 0";
//				pstmtUpd = conn.prepareStatement(updateSql);
//				pstmtUpd.setString(1, locCodeTo);
//				pstmtUpd.setString(2, saleOrder);
//				pstmtUpd.setString(3, lineNoSord);				
//				pstmtUpd.setString(4, itemCode);				
//				pstmtUpd.setString(5, locCode);				
//				pstmtUpd.setString(6, lotNo);				
//				pstmtUpd.setString(7, lotSl);				
//
//				updCnt = pstmtUpd.executeUpdate();
				// end 16/10/11 manoharan update sordalloc
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if (retString != null && retString.trim().length() > 0 )
			{
				isError = true;
				return retString;
			}

			//update the status of pick order in wave_task_det table
			updateSql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ? ";
			pstmt = conn.prepareStatement(updateSql);
			pstmt.setString(1, pickOrder);

			updCnt = pstmt.executeUpdate();

			if( updCnt > 0 )
			{					
				System.out.println( updCnt + " rows updated successfully" );
			}	
			pstmt.close();pstmt = null;
			/*
			sql = "select ptcn from wave_task_det where ref_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,replOrder);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				ptcn = rs.getString("ptcn") == null?"":rs.getString("ptcn");
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}

			ConsolidatToDoc conToDoc = new ConsolidatToDoc();
			retString = conToDoc.process(ptcn, conn);
			 */


			System.out.println("pick type from pick_ord_hdr table--->>["+pickType+"]");
		}
		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;
	}
}

