package ibase.webitm.ejb.mfg;

import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.utility.E12GenericUtility;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.E12CreateBatchLoadEjb;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.StockUpdate;
import ibase.webitm.ejb.sys.CreateRCPXML;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

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

import javax.ejb.Stateless;
import javax.naming.InitialContext;

import org.w3c.dom.Document;
//import java.lang.reflect.Method;


@Stateless
public class QcOdrProvisionalReleaseConf extends ActionHandlerEJB implements
QcOdrProvisionalReleaseConfLocal, QcOdrProvisionalReleaseConfRemote                                                                            
{                      

	public String actionHandler() throws RemoteException, ITMException
	{
		return "";
	}

	public String actionHandler(String actionType, String xmlString,
			String objContext, String xtraParams) throws RemoteException,
			ITMException 
			{
		System.out.println("item actionHandler(...) called............");
		String str = "";
		System.out.println("actionType---" + actionType);
		System.out.println("xmlString---" + xmlString);
		System.out.println("objContext---" + objContext);
		System.out.println("xtraParams---" + xtraParams);
		return str;
			}

	public String confirm(String tranId, String xtraParams, String forcedFlag)
			throws RemoteException, ITMException
			{
		System.out.println("QCTransferConf confirm called...230914...........");
		String confirmed = "",locCodeTo="",locCodeFrm="",trnfQty="",loginSiteCode="",itemCode="",locCode="",porcpNo="",lotNo="",lotSl="";
		String batchNo="",siteCode="",locDescr="",itemDescr="",siteDescr="",userId="",termId="",xmlString=null,retString=null,quantity=null;
//		int trnfQty=0;
//		double quantity=0.0;
		String sql = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		String errString = "",errCode="",confirmString="",testStat="";
		ResultSet rs = null;
		int recCnt = 0, cnt = 0;
		StringBuffer xmlBuff = null;
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		QCTransferConf qcTrnsCnf=new QCTransferConf(); // Object required to confirm Qc transfer.

		try 
		{
			E12GenericUtility genericUtility=new E12GenericUtility();
			Calendar currentDate = Calendar.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(
					genericUtility.getApplDateFormat());
			String sysDate = sdf.format(currentDate.getTime());
			System.out.println("Now the date is :=>  " + sysDate);
			
			itmDBAccessEJB = new ITMDBAccessEJB();
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);

			if (errString == null || errString.trim().length() == 0)
			{
				//if ( tranIdFr.equalsIgnoreCase(tranIdTo) )
				//{
				//	tranId = tranIdFr;
				//}

				if (tranId != null && tranId.trim().length() > 0) 
				{

					sql = "select status from qc_order where QORDER_NO=?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						confirmed = rs.getString("status");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					System.out.println("Status from QC OREDER &&&&&&&&&&&:- "+confirmed);
					// TEMP Comment VALLABH KADAM 
					if((confirmed.equalsIgnoreCase("C"))){
						errCode = "QCTRFORDNC";   // QC ord not confirmed.
						errString = itmDBAccessLocal.getErrorString("",errCode,"");
					}
					
					sql = "select count(*) as cnt from qc_transfer where qorder_no=? and confirmed='N'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						cnt+=rs.getInt("cnt");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					System.out.println("Qc transfer unconfirmed count :- "+cnt);
					if(cnt>0)
					{
						errCode = "QCTRFUNCNF"; //QC transfer is still unconfirmed.  
						errString = itmDBAccessLocal.getErrorString("",errCode,"");
					}
					
					sql = "select test_stat from qc_order where QORDER_NO=?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						testStat = rs.getString("test_stat");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					if("R".equalsIgnoreCase(testStat)){
						errCode = "QCPRRLISDN"; //Provisional release is already takes place.  
						errString = itmDBAccessLocal.getErrorString("",errCode,"");
					}
					
					sql = "select LOC_CODE,QTY_TRANSFERED from qc_order where QORDER_NO=?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						locCodeFrm=rs.getString("LOC_CODE").trim();
						trnfQty=rs.getString("QTY_TRANSFERED");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					System.out.println("Location code from &&&&&&&&&:- "+locCodeFrm);
					locCodeTo=locCodeFrm+"Q";        //Generate Location To
					System.out.println("Location code to &&&&&&&&&&&:- "+locCodeTo);
					
//					if(trnfQty==null || trnfQty.equalsIgnoreCase("") || Integer.parseInt(trnfQty)<=0){
//						errCode = "QCTRFQTYNV"; //QC transfer quantity is not valid.  
//						errString = itmDBAccessLocal.getErrorString("",errCode,"");
//					}
					
					sql = "select count(*) as cnt from location where loc_code=?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, locCodeTo);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						cnt=rs.getInt("cnt");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					                                               //TEMP COMENT VALLABH KADAM
					if(cnt<=0){
						errCode = "QCTRLOCNVL"; //QC transfer Location code TO not valid.  
						errString = itmDBAccessLocal.getErrorString("",errCode,"");
					}
					
					
				}
				if (errString == null || errString.trim().length() == 0) 
				{
//					errCode = "VTCONFIRM";
//					errString = itmDBAccessLocal.getErrorString("",errCode,"");
//					return errString;
//					loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(
//							xtraParams, "loginSiteCode");
					userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode"); 
					System.out.println("--login code--"+userId);
					termId =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId"); 
					System.out.println("--term id--"+termId);
					//Got sysdate above.
					
					sql = " Select quantity, item_code, loc_code, porcp_no, lot_no, lot_sl, batch_no, site_code,QTY_TRANSFERED"
							+ " from 	 qc_order where  qorder_no = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if (rs.next()) {
//						quantity = rs.getDouble("quantity");
						quantity = checkNull(rs.getString("quantity"));
						itemCode = checkNull(rs.getString("item_code"));
						locCodeFrm = checkNull(rs.getString("loc_code").trim());
						porcpNo = checkNull(rs.getString("porcp_no"));
						lotNo = checkNull(rs.getString("lot_no"));
						lotSl = checkNull(rs.getString("lot_sl"));
						batchNo = checkNull(rs.getString("batch_no"));
						siteCode = checkNull(rs.getString("site_code"));
						trnfQty=checkNull(rs.getString("QTY_TRANSFERED"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;	

					sql = "select fn_getquarloc(?) as locCodTo from dual;";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						locCodeTo = checkNull(rs.getString("locCodTo").trim());
						
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
//					System.out.println("Location code from &&&&&&&&&:- "+locCodeFrm);
//					locCodeTo=locCodeFrm+"Q";        //Generate Location To
//					System.out.println("Location code to &&&&&&&&&&&:- "+locCodeTo);
					
					System.out.println("TransQUANTITY before :-"+trnfQty);
					System.out.println("QUANTITY before :-"+quantity);
					trnfQty=trnfQty==null?"0":trnfQty;
					quantity=quantity==null?"0":quantity;
					locCodeTo=locCodeTo==null?"":locCodeTo;
					
					System.out.println("TransQUANTITY after :-"+trnfQty);
					System.out.println("QUANTITY after :-"+quantity);
					
					System.out.println("Lot sl &&&&&&&&&&&&:-"+lotSl);
					System.out.println("Lot No &&&&&&&&&&&&:-"+lotNo);
					
					xmlBuff = new StringBuffer();
					System.out.println("--XML CREATION --");
					
				    xmlBuff.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
					xmlBuff.append("<DocumentRoot>");
					xmlBuff.append("<description>").append("Datawindow Root").append("</description>");
					xmlBuff.append("<group0>");
					xmlBuff.append("<description>").append("Group0 description").append("</description>");
					xmlBuff.append("<Header0>");
					xmlBuff.append("<objName><![CDATA[").append("qc_transfer").append("]]></objName>");  
					xmlBuff.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>");
					xmlBuff.append("<objContext><![CDATA[").append("1").append("]]></objContext>");
					xmlBuff.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>");
					xmlBuff.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>");
					xmlBuff.append("<action><![CDATA[").append("SAVE").append("]]></action>");
					xmlBuff.append("<elementName><![CDATA[").append("").append("]]></elementName>");
					xmlBuff.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>");
					xmlBuff.append("<taxKeyValue><![CDATA[").append("").append("]]></taxKeyValue>");
					xmlBuff.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
					xmlBuff.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
					xmlBuff.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>");
					xmlBuff.append("<description>").append("Header0 members").append("</description>");	
					xmlBuff.append("<Detail1 dbID=\"\" domID=\"1\" objName=\"sorder\" objContext=\"1\">");  
					xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
					xmlBuff.append("<tran_id/>");
					xmlBuff.append("<site_code><![CDATA["+siteCode+"]]></site_code>");
					xmlBuff.append("<tran_date><![CDATA["+sysDate+"]]></tran_date>");
					xmlBuff.append("<qorder_no><![CDATA["+tranId+"]]></qorder_no>");
					xmlBuff.append("<quantity__o><![CDATA["+quantity+"]]></quantity__o>");
					xmlBuff.append("<quantity><![CDATA["+trnfQty+"]]></quantity>");
					xmlBuff.append("<loc_code__trf><![CDATA["+locCodeTo+"]]></loc_code__trf>");
					xmlBuff.append("<confirmed><![CDATA["+'N'+"]]></confirmed>");
					xmlBuff.append("<conf_date><![CDATA["+sysDate+"]]></conf_date>");
					xmlBuff.append("<chg_user><![CDATA["+userId+"]]></chg_user>");
					xmlBuff.append("<chg_date><![CDATA["+sysDate+"]]></chg_date>");
					xmlBuff.append("<chg_term><![CDATA["+termId+"]]></chg_term>");
					xmlBuff.append("<lot_sl><![CDATA["+lotSl+"]]></lot_sl>");
					xmlBuff.append("<porcp_no><![CDATA["+porcpNo+"]]></porcp_no>");
					xmlBuff.append("<loc_code><![CDATA["+locCodeFrm+"]]></loc_code>");
					xmlBuff.append("<item_code><![CDATA["+itemCode+"]]></item_code>");
					xmlBuff.append("<lot_no><![CDATA["+lotNo+"]]></lot_no>");
					xmlBuff.append("<remarks><![CDATA[]]></remarks>");
					xmlBuff.append("<net_weight><![CDATA[]]></net_weight>");
					xmlBuff.append("<no_art><![CDATA[]]></no_art>");
					xmlBuff.append("<dimension><![CDATA[]]></dimension>");
					
					xmlBuff.append("</Detail1>");
					
					//Now Save generated XML
					
					xmlBuff.append("</Header0>");
					xmlBuff.append("</group0>");
					xmlBuff.append("</DocumentRoot>");
					xmlString = xmlBuff.toString();
					System.out.println("@@@@@2: xmlString:"+xmlBuff.toString());
					System.out.println("...............just before savdata()");
					siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
					System.out.println("== site code =="+siteCode);
					retString = saveData(siteCode,xmlString,conn);
					System.out.println("@@@@@2: retString:"+retString);
					System.out.println("--retString finished--");
					System.out.println("RetString after Transaction :- "+retString);
					if (retString.indexOf("Success") > -1)
					{
						System.out.println("Success *************************************");
						String[] arrayForTranId = retString.split("<TranID>");
						int endIndex = arrayForTranId[1].indexOf("</TranID>");
						String tranIdForDesp = arrayForTranId[1].substring(0,endIndex);
						System.out.println("-tranIdForDesp &&&&&&&&&&& :- "+tranIdForDesp);
						
						System.out.println("In CASE 2 #######");	
						sql="UPDATE qc_order set test_stat='R' where QORDER_NO=?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,tranId);
						cnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
						
						System.out.println("Qc orser update count &&&&&&&:-"+cnt);
						
						// To confirm QC Transfer send tranIdForDesp, xtraParams, forcedFlag
						
						retString=qcTrnsCnf.confirm(tranIdForDesp, xtraParams, forcedFlag,conn);
						// END confirm QC Transfer
						
						System.out.println("RET String after confirm :- ["+retString+"]");
						if (retString.indexOf("VTCONFIRM") > -1){
						errCode = "VTQCTRNSCC";// Message after successful Transfer
						errString = itmDBAccessLocal.getErrorString("",errCode,"");
						return errString;
						}
						else
						{
							errCode = "VTWOCFAIL";// Transaction fail.
							errString = itmDBAccessLocal.getErrorString("",errCode,"");
							return errString;
						}
					}
					else
					{
						errCode = "VTWOCFAIL";// Transaction fail.
						errString = itmDBAccessLocal.getErrorString("",errCode,"");
						return errString;
					}
				}					
				else
				{
					System.out.println("In Else Found Validation error &&&&&&&");
					return errString;
				}
			} // end if errstrng


		} catch (Exception e) 
		{
			e.printStackTrace();
			throw new ITMException(e);
		} 
		finally 
		{
			try 
			{
//				if (errString != null && errString.trim().length() > 0	&& !("VTQCTRNSCC".equalsIgnoreCase(errCode))) 
					if (errCode != null && errCode.trim().length() > 0	&& !("VTQCTRNSCC".equalsIgnoreCase(errCode))) 
				{
					conn.rollback();
					System.out.println("@@@@ Transaction rollback...["+errString+"]::errCode["+errCode+"]");
					conn.close();
					conn = null;
				}
				else 
				{
					conn.commit(); // test
					System.out.println("@@@@ Transaction commit....["+errString+"]::errCode["+errCode+"]");
					conn.close();
					conn = null;
				}
			}
			catch (Exception e) 
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
			}

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

	private String saveData(String siteCode,String xmlString, Connection conn) throws ITMException
	{
		System.out.println("saving data...........");
		InitialContext ctx = null;
		String retString = null;
		MasterStatefulLocal masterStateful = null; // for ejb3
		try
		{
			AppConnectParm appConnect = new AppConnectParm();
			ctx = new InitialContext(appConnect.getProperty());
			masterStateful = (MasterStatefulLocal)ctx.lookup("ibase/MasterStatefulEJB/local");
			System.out.println("-----------masterStateful------- " + masterStateful);
			String [] authencate = new String[2];
			authencate[0] = "";
			authencate[1] = "";
			System.out.println("xmlString to masterstateful [" + xmlString + "]");
			retString = masterStateful.processRequest(authencate, siteCode, true, xmlString,true,conn);
			System.out.println("--retString - -"+retString);
		}
		catch(ITMException itme)
		{
			System.out.println("ITMException :CreateDistOrder :saveData :==>");
			throw itme;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception :CreateDistOrder :saveData :==>");
			throw new ITMException(e);
		}
		return retString;
	}

	private double roundVal(double round,int scale)
	{
		return Math.round(round*Math.pow(10, scale))/Math.pow(10, scale);
	}
}