/********************************************************
	Title : ShipmentLocConf
	Date  : 25/04/2014
	Developer: chandrakant patil
	req id : DI3GSUN047
 ********************************************************/

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
	{
		String errString = "";
		String sql = "";
		String lorryNo = "";
		String lrNo = "";
		String confirmed = "";
		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 " +
					" 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,cntConsIss=0;
		String confirmed="",lrNo="",lorryNo="",recallFrt="",despId="",lrNoUpd="",lorryNoUpd="",distOrdIssId="",conOrdIssId="";
		double totalFreight=0,gwtDesp=0,gwtDist=0,totGwt=0,grossWeight=0,calcFrt=0,totFrt=0,frtAmt=0,grossWeightCIss=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 " +
					" 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) " +
					" 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 )
				{

					sql = " select count(1) from   CONSUME_ISS " +
							"  where  cons_issue in ( select ref_id from ship_docs where shipment_id = ? ) and confirmed = 'Y' ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					while (rs.next())
					{
						cntConsIss = rs.getInt(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					if( cntConsIss == 0 )  // ll_cnt_dist = 0 then  /// cntDist == 0  change 
					{	
						errCode = "VTSHPALL";   
						return errCode;
					}
				}//end if
			} //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) " +
					"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+"]");

			// added by cpatil on 25/04/14

			sql = "	 select nvl(sum(gross_weight),0)   from   CONSUME_ISS " +
					"  where  cons_issue in ( select ref_id from ship_docs where shipment_id = ? ) and confirmed = 'Y' ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				grossWeightCIss = rs.getDouble(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			System.out.println("@@@@@@@@grossWeightCIss["+grossWeightCIss+"]");

			// end 25/04/14 

			totGwt = gwtDesp + gwtDist + grossWeightCIss;

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



			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+"]");
			//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( lrDate != null )
			{
				lrDateUpd = lrDate;
			}

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

			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' ";
			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+"]");

			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;

			////

			sql = " select cons_issue,gross_weight,lr_date, lr_no, lorry_no from   CONSUME_ISS " +
					"  where  cons_issue in ( select ref_id from ship_docs where shipment_id = ? ) and confirmed = 'Y' ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				conOrdIssId = rs.getString("cons_issue");
				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("@@@@conOrdIssId["+conOrdIssId+"]::grossWeight["+grossWeight+"]lrDate["+lrDate+"]");

			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+"]");

			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+"]");

			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 CONSUME_ISS set FREIGHT_AMT = ?, lr_no 	= ?	, " +
					" lr_date = ?, lorry_no = ?  " +
					"  where  cons_issue = ?  ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, calcFrt);
			pstmt.setString(2, lrNoUpd);
			pstmt.setTimestamp(3, lrDateUpd);
			pstmt.setString(4, lorryNoUpd);
			pstmt.setString(5, conOrdIssId);
			updCnt = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;


			// END TEST

			//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+"]");

			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;

			}

		}  // 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;
	}

}
