/********************************************************
	Title 	 : 	QCApprovalWizPos[M14HFRA002]
	Date  	 : 	04/12/14
	Developer:  Sagar M.

********************************************************/
package ibase.webitm.ejb.mfg;

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 ibase.webitm.ejb.dis.adv.StockTransferConf;
import ibase.webitm.ejb.mfg.adv.ConfirmQcSample;

import java.io.*;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.encoding.XMLType;
import org.w3c.dom.*;

import javax.ejb.*;
import javax.naming.InitialContext;
import javax.xml.rpc.ParameterMode;
@javax.ejb.Stateless
public class QCApprovalWizPos extends ValidatorEJB implements QCApprovalWizPosLocal, QCApprovalWizPosRemote
{
	public String postSave()throws RemoteException,ITMException
	{
		return "";
	}
	public String postSave(String domString, String tranId,String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		
		PreparedStatement pstmt = null,pstmt1=null;
        ResultSet rs = null,rs1=null;
        GenericUtility genericUtility = GenericUtility.getInstance();
        String siteCode="",locCode="",itemCode="",lotNo="",lotSl="",testStat="";
        String chgTerm="",userId="";
        String forcedFlag = "";
		String  retString="";
		String sql = "";
        int detCnt=0,totalLine=0,updCnt=0;
        int parentNodeListLength = 0;
        double totalSampleQty=0.0,totalRejectQty=0.0,totalPassedQty=0.0,totalRejSampQty=0.0;
        Document dom = null;
        NodeList hdrDom=null;
        NodeList parentNodeList = null;
		Node parentNode = null;
        StringBuffer xmlBuff = null;
        Date currentDate = new Date();
        boolean isError = false;
        boolean totalScan=true;
        
		try
		{
			System.out.println(">>>>>>>In post Save conn status:"+conn);
			conn.setAutoCommit(false);
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");
			chgTerm = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" ));
			System.out.println( ">>>>>>>>>QCApprovalWizPos QC Order No:"+tranId);
			System.out.println(">>>>>>>>>>>>>>>>>>>>>domString in post save:"+domString);
			dom = genericUtility.parseString(domString);
			System.out.println(">>>>>>>>>>Calling QC Order Confirm method:"+tranId);
			hdrDom = dom.getElementsByTagName("Detail2");
			if(hdrDom != null && hdrDom.getLength() > 0)
			{
				siteCode = checkNull(genericUtility.getColumnValueFromNode("site_code", dom.getElementsByTagName("Detail2").item(0)));
				itemCode = checkNull(genericUtility.getColumnValueFromNode("item_code", dom.getElementsByTagName("Detail2").item(0)));
				lotNo = checkNull(genericUtility.getColumnValueFromNode("lot_no", dom.getElementsByTagName("Detail2").item(0)));
				totalPassedQty = Double.parseDouble(checkNull(genericUtility.getColumnValueFromNode("qty_passed", dom.getElementsByTagName("Detail2").item(0))));
				System.out.println(">>>>>>>>>>Header siteCode:"+siteCode);
				System.out.println(">>>>>>>dom totalPassedQty:"+totalPassedQty);
			}
			//Get total quantity from qc_order_lots..
			/*sql = "select sum(sample_qty) as sample_qty from qc_order_lots where qorder_no= ? ";
			pstmt= conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery(); 
			if(rs.next())
			{
				totalSampleQty= rs.getDouble("sample_qty");
			}
			pstmt.close();
			rs.close();
			pstmt = null;
			rs = null;
			System.out.println(">>>>>>>totalSampleQty:"+totalSampleQty);
			*/
			//Get total Reject Quantity from qc_order_lots..
			sql = "select sum(quantity) as quantity from qc_order_lots where qorder_no= ?  and loctype='R' ";
			pstmt= conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery(); 
			if(rs.next())
			{
				totalRejectQty= rs.getDouble("quantity");
			}
			pstmt.close();
			rs.close();
			pstmt = null;
			rs = null;
			System.out.println(">>>>>>>totalRejectQty:"+totalRejectQty);
			//totalRejSampQty= totalRejectQty + totalSampleQty;
			//System.out.println(">>>>>>>>>>totalRejSampQty:"+totalRejSampQty);
			//totalPassedQty= totalPassedQty - totalRejectQty; 
			//System.out.println(">>>>>>>>>>Final totalPassedQty:"+totalPassedQty);
			
			/*sql = "update qc_order set qty_passed= ? , qty_rejected= ? where qorder_no = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, totalPassedQty);
			pstmt.setDouble(2, totalRejectQty);
			//pstmt.setDouble(3, totalSampleQty);
			pstmt.setString(3, tranId);
			updCnt = pstmt.executeUpdate();
			pstmt.close();
			pstmt1 = null;	
			if(updCnt > 0)
			{
				conn.commit();
				System.out.println(">>>>>after update qc_order conn commit success:");
			}
			else
			{
				conn.rollback();
				System.out.println(">>>>>after update qc_order conn rollback success:");
			}
			System.out.println(">>>>>>>>>>>update qc_order updCnt:"+updCnt);
			*/
			conn.commit();
			//if(updCnt > 0)
			//{
				sql = " SELECT stock.item_ser,stock.item_code,stock.unit,stock.loc_code,stock.lot_no, stock.lot_sl,   "
						+ " stock.site_code, stock.quantity  - case "
						+" when stock.alloc_qty is null then 0 else stock.alloc_qty end as quantity "
						+" FROM stock,invstat  "
						+" WHERE ( stock.inv_stat = invstat.inv_stat ) and  "
						+" ( stock.item_code = ? ) AND  "
						+" ( stock.site_code = ?) AND  "
						+" ( stock.lot_no = ?) AND  "
						+" ( stock.quantity  - case when stock.alloc_qty is null then 0 else stock.alloc_qty end >  0 )"; 
				pstmt= conn.prepareStatement(sql);
				pstmt.setString(1, itemCode);
				pstmt.setString(2, siteCode);
				pstmt.setString(3, lotNo);
				rs = pstmt.executeQuery(); 
				while(rs.next())
				{
					totalLine=totalLine+1;
					System.out.println(">>>>>Display Stock details In post Save totalLine:"+totalLine);
			    	locCode= rs.getString("loc_code") == null ? "" : rs.getString("loc_code");
			    	itemCode= rs.getString("item_code") == null ? "" : rs.getString("item_code");
			    	lotNo= rs.getString("lot_no") == null ? "" : rs.getString("lot_no");
			    	lotSl= rs.getString("lot_sl")== null ? "" : rs.getString("lot_sl");
			    	//Check in QC ORDER DETAILS for exist or not 
			    	sql = "select count(1) cnt from qc_order_lots where item_code= ? and lot_no= ? and lot_sl= ? and loc_code__issue= ? and qorder_no= ? ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, itemCode);
					pstmt1.setString(2, lotNo);
					pstmt1.setString(3, lotSl);
					pstmt1.setString(4, locCode);
					pstmt1.setString(5, tranId);
					rs1 = pstmt1.executeQuery();
					if (rs1.next())
					{
						detCnt = rs1.getInt("cnt");
						System.out.println(">>>>>>>detCnt:"+detCnt);
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
					if(detCnt==0)
					{
						totalScan=false;
						//break;
					}
				}
				pstmt.close();
				rs.close();
				pstmt = null;
				rs = null;
				System.out.println(">>>>>>>>>>>final totalScan:"+totalScan);
				if(totalScan)
				{
					forcedFlag="N";
					sql = "select test_stat from qc_order where qorder_no= ? ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, tranId);
					rs1 = pstmt1.executeQuery();
					if (rs1.next())
					{
						testStat = rs1.getString("test_stat");
						System.out.println(">>>>>>>testStat:"+testStat);
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
					if(!"P".equals(testStat))
					{
						System.out.println(">>>>>>Automatic Call QC Order Passed:"+tranId);
						retString= qcPassed("qcorder_new",tranId,xtraParams,forcedFlag,conn);
						System.out.println(">>>>>>>>>>qcPassed retString:"+retString);
						if((retString != null ) && (retString.indexOf("VTSUCC1") > -1))
						{
							System.out.println(">>>>QC Order Passed Successfully:"+tranId);
							//isError=false;
							System.out.println(">>>>>>Automatic Call QC Order Confirmation:"+tranId);
							retString= qcConfirm("qcorder_new",tranId,xtraParams,forcedFlag,conn);
							System.out.println(">>>>>>>>>>qcConfirm retString:"+retString);
							if((retString != null ) && (retString.indexOf("VTSUCC1") > -1))
							{
								System.out.println(">>>>QC Order Confirmed Successfully:"+tranId);
								isError=false;
							}
							else 
							{
								isError=true;
								System.out.println(">>>>QC Order Not Confirmed:"+tranId);
							}
						}
						else 
						{
							isError=true;
							System.out.println(">>>>QC Order Not Confirmed:"+tranId);
						}
						
					}
					
				}
				
			//}
			
		}
		catch(Exception e)
		{
			isError = true;
			System.out.println("Exception "+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println(">>>>>>>>>>in finaly isError:"+isError);
				if(!isError)
				{
					conn.commit(); 
					System.out.println(">>>>>>>Transaction commit successfully");
				}
				else if(isError)
				{
					conn.rollback();
					System.out.println(">>>>>>>Transaction rollback successfully");
				}
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return retString;
	}
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		else
		{
			input = input.trim();
		}
		return input;
	}
	public String qcConfirm(String businessObj,String tranId,String xtraParams,String forcedFlag, Connection conn) throws ITMException
	{
		String methodName = "";
		String compName = "";
		String retString = "";
		String serviceCode = "";
		String serviceURI = "";
		String actionURI = "";
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try
		{
			System.out.println(">>>>>>>>In qcConfirm Method>>>>>");
			methodName = "gbf_post";
			actionURI = "http://NvoServiceurl.org/" + methodName;

			sql = "SELECT SERVICE_CODE,COMP_NAME FROM SYSTEM_EVENTS WHERE OBJ_NAME = ? AND EVENT_CODE = 'pre_confirm' ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,businessObj);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				serviceCode = rs.getString("SERVICE_CODE");
				compName = rs.getString("COMP_NAME");
			}
			System.out.println(">>>qcConfirm serviceCode = "+serviceCode+" compName "+compName);
			sql = "SELECT SERVICE_URI FROM SYSTEM_EVENT_SERVICES WHERE SERVICE_CODE = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,serviceCode);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				serviceURI = rs.getString("SERVICE_URI");
			}
			System.out.println(">>>qcConfirm serviceURI = "+serviceURI+" compName = "+compName);
			Service service = new Service();
			Call call = (Call)service.createCall();
			call.setTargetEndpointAddress(new java.net.URL(serviceURI));
			call.setOperationName( new javax.xml.namespace.QName("http://NvoServiceurl.org", methodName ) );
			call.setUseSOAPAction(true);
			call.setSOAPActionURI(actionURI);
			Object[] aobj = new Object[4];

			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "component_name"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "tran_id"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "xtra_params"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "forced_flag"), XMLType.XSD_STRING, ParameterMode.IN);

			aobj[0] = new String(compName);
			aobj[1] = new String(tranId);
			aobj[2] = new String(xtraParams);
			aobj[3] = new String(forcedFlag);
			
			System.out.println("@@@@@@ call.setReturnType(XMLType.XSD_STRING) executed........");
			call.setReturnType(XMLType.XSD_STRING);
			retString = (String)call.invoke(aobj);

			System.out.println(">>>>Confirm Complete Return string from NVO is:==>["+retString+"]");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{		
			try{

				if (pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				/*if( conn != null ){
					conn.close();
					conn = null;
				}*/
			}
			catch(Exception e)
			{
				System.out.println("Exception inCalling confirmed");
				e.printStackTrace();
				try{
					conn.rollback();

				}catch (Exception s)
				{
					System.out.println("Unable to rollback");
					s.printStackTrace();
				}
				throw new ITMException(e);
			}

		}
		return retString;
	}
	public String qcPassed(String businessObj,String tranId,String xtraParams,String forcedFlag, Connection conn) throws ITMException
	{
		String methodName = "";
		String compName = "";
		String retString = "";
		String serviceCode = "";
		String serviceURI = "";
		String actionURI = "";
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try
		{
			System.out.println(">>>>>>>>In qcPassed Method>>>>>");
			methodName = "gbf_post";
			actionURI = "http://NvoServiceurl.org/" + methodName;

			sql = "SELECT SERVICE_CODE,COMP_NAME FROM SYSTEM_EVENTS WHERE OBJ_NAME = ? AND EVENT_CODE = 'pre_pass' ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,businessObj);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				serviceCode = rs.getString("SERVICE_CODE");
				compName = rs.getString("COMP_NAME");
			}
			System.out.println(">>>qcPassed serviceCode = "+serviceCode+" compName "+compName);
			sql = "SELECT SERVICE_URI FROM SYSTEM_EVENT_SERVICES WHERE SERVICE_CODE = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,serviceCode);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				serviceURI = rs.getString("SERVICE_URI");
			}
			System.out.println(">>>qcPassed serviceURI = "+serviceURI+" compName = "+compName);
			Service service = new Service();
			Call call = (Call)service.createCall();
			call.setTargetEndpointAddress(new java.net.URL(serviceURI));
			call.setOperationName( new javax.xml.namespace.QName("http://NvoServiceurl.org", methodName ) );
			call.setUseSOAPAction(true);
			call.setSOAPActionURI(actionURI);
			Object[] aobj = new Object[4];

			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "component_name"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "tran_id"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "xtra_params"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "forced_flag"), XMLType.XSD_STRING, ParameterMode.IN);

			aobj[0] = new String(compName);
			aobj[1] = new String(tranId);
			aobj[2] = new String(xtraParams);
			aobj[3] = new String(forcedFlag);
			
			System.out.println("@@@@@@ call.setReturnType(XMLType.XSD_STRING) executed........");
			call.setReturnType(XMLType.XSD_STRING);
			retString = (String)call.invoke(aobj);

			System.out.println(">>>>QC Order Passed Complete Return string from NVO is:==>["+retString+"]");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{		
			try{

				if (pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				/*if( conn != null ){
					conn.close();
					conn = null;
				}*/
			}
			catch(Exception e)
			{
				System.out.println("Exception inCalling confirmed");
				e.printStackTrace();
				try{
					conn.rollback();

				}catch (Exception s)
				{
					System.out.println("Unable to rollback");
					s.printStackTrace();
				}
				throw new ITMException(e);
			}

		}
		return retString;
	}
}


