/********************************************************
	Title : ShipmentLocConf
	Date  : 01/06/2012
	Developer: Mahesh Patidar
 ********************************************************/

package ibase.webitm.ejb.dis.adv;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.E12CreateBatchLoadEjb;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ITMDBAccessLocal;
import ibase.webitm.ejb.sys.CreateRCPXML;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;

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.Calendar;
import java.util.HashMap;
import java.util.Map;

import javax.ejb.Stateless;
import javax.xml.rpc.ParameterMode;

import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.encoding.XMLType;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
//Changed by sumit on 06/10/12
import org.w3c.dom.*;


@Stateless
public class ShipmentLocConf extends ActionHandlerEJB implements ShipmentLocConfLocal, ShipmentLocConfRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	public String confirm(String tranId, String xtraParam, String forcedFlag) throws RemoteException,ITMException
	{
		//int cnt = 0;
		String errString = "";
		String sql = "";
		//String refId = "",proNo = "",tranProNo = "";
		String lorryNo = "";
		//String tranCode = "";
		String lrNo = "";
		String confirmed = "";
		//ArrayList<String> errList = new ArrayList<String>();
		ConnDriver connDriver = new ConnDriver();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null; 
		String errCode="",recallFrt="";
		double totalFreight=0;
		Timestamp lrDate=null;

		try
		{
			conn = connDriver.getConnectDB( "DriverITM" );
			String userID = genericUtility.getValueFromXTRA_PARAMS( xtraParam, "LoginCode" );
			System.out.println(" forced flag ["+forcedFlag+"]");

			ITMDBAccessLocal itmDBAccessLocal = new ITMDBAccessEJB();


			// check whether already confirmed or not
			sql = " select confirmed	, total_freight , lr_no		, lr_date		, recall_frt, lorry_no " +
					//	  " into   :mconf		, :lc_frt_amt	 , :ls_lr_no, :ldt_lr_date	, :ls_recal, :ls_lorry_no " +
					" from   shipment  where  shipment_id = ? ";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				confirmed = rs.getString("confirmed");
				totalFreight = rs.getDouble("total_freight");
				lrNo = checkNull(rs.getString("lr_no"));
				lrDate = rs.getTimestamp("lr_date");
				recallFrt = checkNull(rs.getString("recall_frt"));
				lorryNo = checkNull(rs.getString("lorry_no"));
			}
			System.out.println("@@@@confirmed["+confirmed+"]totalFreight["+totalFreight+"]lrNo["+lrNo+"]");
			System.out.println("@@@@lrDate["+lrDate+"]recallFrt["+recallFrt+"]lorryNo["+lorryNo+"]");
			rs.close();rs = null;
			pstmt.close();pstmt = null;

			if( "Y".equalsIgnoreCase(confirmed))
			{
				errString = itmDBAccessLocal.getErrorString("","VTDIST26","");
				return errString;
			}
			else
			{

				errCode = confirmShipmentLoc(tranId,1, xtraParam,conn);
				System.out.println("@@@@ confirmQcTransfer::errCode["+errCode+"]");
				if( errCode != null && errCode.trim().length() > 0 )
				{
					conn.rollback();
					System.out.println("@@@@@@@@@ rollback successful...........");
					errString = itmDBAccessLocal.getErrorString("",errCode,"");
					System.out.println("@@@@@@@@@ confirm failed.....");
				}
				else
				{
					conn.commit();
					System.out.println("@@@@@@@@@ commit successful...........");
					errString = itmDBAccessLocal.getErrorString("","VTCONFIRM","");
					System.out.println("@@@@@@@@@ confirm successful...........");

				}

			}
		}
		catch(Exception e)
		{
			try{
				conn.rollback();
			}
			catch(SQLException es)
			{
				System.out.println("Rollback exception : " + es.getMessage() + ":");
			}
			System.out.println("Exception : Shipment Confirm():" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			System.out.println(" Closing Connection in Shipment Confirm() !!");
			try
			{
				conn.close();
				conn = null;
			}
			catch(Exception se){
				System.out.println(se.getMessage());
				throw new ITMException(se);
			}
		}
		return errString;
	}


	private String confirmShipmentLoc(String tranId, int i, String xtraParams, Connection conn)
			throws RemoteException, ITMException

			{
		PreparedStatement pstmt = null, pstmtSql = null;
		ResultSet rs = null;
		int cnt = 0,updCnt=0;
		String retString = "", sql = "", errCode = "";
		Timestamp sysDate = null, tranDate = null,lrDate=null,lrDateUpd=null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		int cntDist=0;
		String confirmed="",lrNo="",lorryNo="",recallFrt="",despId="",lrNoUpd="",lorryNoUpd="",distOrdIssId="";
		double totalFreight=0,gwtDesp=0,gwtDist=0,totGwt=0,grossWeight=0,calcFrt=0,totFrt=0,frtAmt=0;

		try 
		{

			System.out.println("@@@@@@@@@@@@@@@ confirmShipmentLoc method called next..............");
			Calendar currentDate = Calendar.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			String sysDateStr = sdf.format(currentDate.getTime());
			System.out.println("Now the date is :=>  " + sysDateStr);
			sysDate= Timestamp.valueOf(genericUtility.getValidDateString(sysDateStr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");

			sql = " select confirmed	, total_freight , lr_no		, lr_date		, recall_frt, lorry_no " +
					//	  " into   :mconf		, :lc_frt_amt	 , :ls_lr_no, :ldt_lr_date	, :ls_recal, :ls_lorry_no " +
				  " from   shipment  where  shipment_id = ? ";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				confirmed = rs.getString("confirmed");
				totalFreight = rs.getDouble("total_freight");
				lrNo = checkNull(rs.getString("lr_no"));
				lrDate = rs.getTimestamp("lr_date");
				recallFrt = checkNull(rs.getString("recall_frt"));
				lorryNo = checkNull(rs.getString("lorry_no"));
			}
			System.out.println("@@@@confirmed["+confirmed+"]totalFreight["+totalFreight+"]lrNo["+lrNo+"]");
			System.out.println("@@@@lrDate["+lrDate+"]recallFrt["+recallFrt+"]lorryNo["+lorryNo+"]");
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;


			// check shipment allocated to at least one despatch or dist issue
			sql = " select count(1) " +
					//	"			into 	 :ll_cnt " +
					" from 	 despatch where  shipment_id = ?  and    confirmed = 'Y' ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				cnt = rs.getInt(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			System.out.println("cnt["+cnt+"]");
			if( cnt == 0 )  // ll_cnt = 0 then
			{	
				sql = " select count(1) " +
						" from distord_iss where  shipment_id = ? and confirmed = 'Y' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					cntDist = rs.getInt(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				System.out.println("cntDist["+cntDist+"]");
				if( cntDist == 0 )  // ll_cnt_dist = 0 then
				{	
					errCode = "VTSHPALL";   
					return errCode;
				}//end if
			} //end if
			
			
			/*
					if ls_recal = 'Y' then
							if dw_edit.Retrieve(ls_shipment) = 1 then 
							formobj[1].Retrieve(ls_shipment)
							formno = 0
							end if   
							cb_recal.triggerevent('clicked')
							parent.triggerevent('ue_abort')
							end if
			 */

			//allocating despatches and distord issue, freight proportionate to the gross weight
			sql = " select nvl(sum(gross_weight),0) " +
					//" into   :lc_gwt_desp " +
					" from   despatch  where  shipment_id = ? and confirmed = 'Y' ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				gwtDesp = rs.getDouble(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			System.out.println("@@@@gwtDesp["+gwtDesp+"]");

			sql = " select nvl(sum(gross_weight),0) " +
					//"into   :lc_gwt_dist " +
					"from   distord_iss where  shipment_id = ? and    confirmed = 'Y' ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				gwtDist = rs.getDouble(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			System.out.println("@@@@gwtDist["+gwtDist+"]");
			
			totGwt = gwtDesp + gwtDist ;

			System.out.println("@@@@totGwt["+totGwt+"]");
			////  test


			sql = " select desp_id,gross_weight, lr_date, lr_no, lorry_no from   despatch " +
					" where  shipment_id =  ?  and    confirmed = 'Y' ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				despId = rs.getString("desp_id");
				grossWeight = rs.getDouble("gross_weight");
				lrDate = rs.getTimestamp("lr_date");
				lrNo = rs.getString("lr_no");
				lorryNo = rs.getString("lorry_no");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			System.out.println("@@@@totGwt["+totGwt+"]::totalFreight["+totalFreight+"]gwtDesp["+gwtDesp+"]");
			//fetch c1 into :ls_tranid,:lc_gwt_desp,:ldt_lrdate, :ls_lrno, :ls_lorryno;
			//calculating the freight amt proportionately
			if ( totGwt != 0 )   // lc_tot_gwt <> 0 then
			{
				//lc_calc_frt = round(((lc_frt_amt * lc_gwt_desp) / lc_tot_gwt),0)
				calcFrt = Math.round(((totalFreight * gwtDesp ) / totGwt));
			}
			else
			{
				calcFrt = 0;
			} 
			
			System.out.println("@@@["+calcFrt+"]");
			//end if
			//this is to check whether total calculated freight doesnot exceed 
			//the total freight during rounding the figure
			totFrt = totFrt + calcFrt;
			System.out.println("@@@totFrt["+totFrt+"]");
			if( totFrt > frtAmt )  // lc_tot_frt > lc_frt_amt then
			{
				calcFrt = frtAmt - ( totFrt - calcFrt );
			} //end if

			System.out.println("@@@calcFrt["+calcFrt+"]");
			
			// if not isnull(ldt_lrdate) then ldt_lr_date = ldt_lrdate

			if( lrDate != null )
			{
				lrDateUpd = lrDate;
			}

			// if not isnull(ls_lrno) then ls_lr_no = ls_lrno

			if( lrNo != null )
			{
				lrNoUpd = lrNo;
			}

			// if not isnull(ls_lorryno) then ls_lorry_no = ls_lorryno

			if( lorryNo != null )
			{
				lorryNoUpd = lorryNo;
			}
			
			System.out.println("@@@@@@[despatch]::lrDate["+lrDate+"]lrNo["+lrNo+"]lorryNo["+lorryNo+"]");
			System.out.println("@@@@@@[despatch]::lrDateUpd["+lrDateUpd+"]lrNoUpd["+lrNoUpd+"]lorryNoUpd["+lorryNoUpd+"]");
			
			sql = " update despatch set freight = ?, lr_no 	= ?	, lr_date = ? ," +
					" lorry_no = ? 	where  desp_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, calcFrt);
			pstmt.setString(2, lrNoUpd);
			pstmt.setTimestamp(3, lrDateUpd);
			pstmt.setString(4, lorryNoUpd);
			pstmt.setString(5, despId);
			updCnt = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;




			sql = " select tran_id,gross_weight,lr_date, lr_no, lorry_no from   distord_iss " +
					" where  shipment_id =  ?  and    confirmed = 'Y' ";
			// fetch c2 into :ls_tranid,:lc_gwt_dist,:ldt_lrdate, :ls_lrno, :ls_lorryno;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				distOrdIssId = rs.getString("tran_id");
				grossWeight = rs.getDouble("gross_weight");
				lrDate = rs.getTimestamp("lr_date");
				lrNo = rs.getString("lr_no");
				lorryNo = rs.getString("lorry_no");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			System.out.println("@@@@distOrdIssId["+distOrdIssId+"]::grossWeight["+grossWeight+"]lrDate["+lrDate+"]");

			//calculating the freight amt proportionately
			System.out.println("@@@@totGwt["+totGwt+"]::totalFreight["+totalFreight+"]lrNo["+lrNo+"]");
			if( totGwt != 0 )  // lc_tot_gwt <> 0 then
			{
				calcFrt = Math.round((( totalFreight * grossWeight) / totGwt));
			}//end if

			System.out.println("calcFrt["+calcFrt+"]");
			//this is to check whether total calculated freight doesnot exceed 
			//the total freight during rounding the figure

			totFrt = totFrt + calcFrt;
			System.out.println("totFrt["+totFrt+"]");
			if( totFrt > totalFreight ) // lc_tot_frt > lc_frt_amt then
			{
				calcFrt = totalFreight - ( totFrt - calcFrt ) ;
			}
			
			System.out.println("calcFrt["+calcFrt+"]totalFreight["+totalFreight+"]totFrt["+totFrt+"]");
			
			//end if

			//if not isnull(ldt_lrdate) then ldt_lr_date = ldt_lrdate
			//if not isnull(ls_lrno) then ls_lr_no = ls_lrno
			//if not isnull(ls_lorryno) then ls_lorry_no = ls_lorryno	

			if( lrDate != null )
			{
				lrDateUpd = lrDate;
			}

			if( lrNo != null )
			{
				lrNoUpd = lrNo;
			}

			if( lorryNo != null )
			{
				lorryNoUpd = lorryNo;
			}

			System.out.println("@@@@@@[distord_iss]::lrDate["+lrDate+"]lrNo["+lrNo+"]lorryNo["+lorryNo+"]");
			System.out.println("@@@@@@[distord_iss]::lrDateUpd["+lrDateUpd+"]lrNoUpd["+lrNoUpd+"]lorryNoUpd["+lorryNoUpd+"]");
			
			sql = " update distord_iss set frt_amt = ?, lr_no 	= ?	, " +
					" lr_date = ?, lorry_no = ?  " +
					" where  tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, calcFrt);
			pstmt.setString(2, lrNoUpd);
			pstmt.setTimestamp(3, lrDateUpd);
			pstmt.setString(4, lorryNoUpd);
			pstmt.setString(5, distOrdIssId);
			updCnt = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;

			/// test

			if( errCode == null || errCode.trim().length() == 0 )
			{	

				sql = " update shipment set confirmed = 'Y', conf_date 	 = ? where  shipment_id = ? ";

				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1, sysDate);
				pstmt.setString(2, tranId);
				updCnt = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;

			}

/*

			sql = "select edi_option from transetup where tran_window = 'w_qc_transfer' ";
			pstmtSql = conn.prepareStatement(sql);
			rs = pstmtSql.executeQuery();
			if (rs.next())
			{
				ediOption = rs.getString("EDI_OPTION");
			}
			rs.close();
			rs = null;
			pstmtSql.close();
			pstmtSql = null;

			System.out.println("@@@@@@@@@@@@@@@ ediOption  called next..............");
			if ("2".equals(ediOption)) 
			{
				CreateRCPXML createRCPXML = new CreateRCPXML("w_qc_transfer","tran_id");
				dataStr = createRCPXML.getTranXML(tranId, conn);
				System.out.println("dataStr =[ " + dataStr + "]");
				Document ediDataDom = genericUtility.parseString(dataStr);

				E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
				retString = e12CreateBatchLoad.createBatchLoad(ediDataDom,"w_qc_transfer", "2", xtraParams, conn);
				createRCPXML = null;
				e12CreateBatchLoad = null;

				if (retString != null && "SUCCESS".equalsIgnoreCase(retString)) 
				{
					System.out.println("retString from batchload = [" + retString + "]");
				}
			}

			else 
			{
				CreateRCPXML createRCPXML = new CreateRCPXML("w_qc_transfer","tran_id");
				dataStr = createRCPXML.getTranXML(tranId, conn);
				System.out.println("dataStr =[ " + dataStr + "]");
				Document ediDataDom = genericUtility.parseString(dataStr);

				E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
				retString = e12CreateBatchLoad.createBatchLoad(ediDataDom,"w_qc_transfer", ediOption, xtraParams, conn);
				createRCPXML = null;
				e12CreateBatchLoad = null;

				if (retString != null && "SUCCESS".equalsIgnoreCase(retString)) 
				{
					System.out.println("retString from batchload = ["+ retString + "]");
				}
			}
			System.out.println("@@@@@@@@@@@@@@@ ediOption  called end..............");

 	*/


		}  // end try
		catch (Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::" + e.getMessage());
			throw new ITMException(e);
		}

		return errCode;
			}



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

}
