/*****************************************
 * VALLABH KADAM
 * MatchVoucherConfSch.java
 * Request Id:- [F14KSUN010]
 * 02/MAR/15
 * ********************************************/
package ibase.webitm.ejb.fin;

import java.sql.Connection;
import java.sql.Date;
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 java.util.Set;

import javax.naming.InitialContext;
import javax.xml.rpc.ParameterMode;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import ibase.scheduler.utility.interfaces.Schedule;
import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.utility.E12GenericUtility;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;
import ibase.utility.CommonConstants;

import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.encoding.XMLType;


public class MatchVoucherConfSch implements Schedule
{
	E12GenericUtility genericUtility=new E12GenericUtility();
	//FileOutputStream fos1 = null;
	@Override
	public String schedule(String scheduleParamXML)throws Exception{	
		ConnDriver connDriver = new ConnDriver();
		Connection conn = null;
		conn = connDriver.getConnectDB("DriverITM");
		connDriver = null;
		PreparedStatement pstmt1 = null,pstmt2=null,pstmt3=null,pstmt4=null,pstmt5=null;
		ResultSet rs1 = null,rs2=null,rs3=null,rs4=null,rs5=null;
		String sql="",sql1="",sql2="",sql3="",sql4="",sql5="";
		String tranId="",loginSiteCode="";
		ibase.utility.UserInfoBean userInfo = null;
		String sysDate ="";
		int cnt1=0, cnt2=0, cnt=0,vCnt=0,rcpCnt=0;
		String retString="";
		String xtraParams = "",siteCode="",porderNo="",billNo="",billDate="",billAmt="";   //The parameters from BaseApplication.xml
		Date bllDate;
		String voucherNo="", receiptNo="",suppBillId="",strToWrite="";; //Other parameters		
		
		try
		{
			System.out.println("********* In Scheduler Begine TRY ***************");			
			Document dom = null;
			int noOfParam=0;			
			userInfo = new ibase.utility.UserInfoBean( scheduleParamXML );
			loginSiteCode = userInfo.getSiteCode();			
			xtraParams = "loginCode="+userInfo.getLoginCode()+"~~termId="+userInfo.getRemoteHost()+"~~loginSiteCode="+loginSiteCode+"~~loginEmpCode="+userInfo.getEmpCode();
			
			System.out.println("XTRAPARAMS contain :- ["+xtraParams+"]");
			
			System.out.println("ScheduleParamXML contains :- ["+scheduleParamXML+"]");
			HashMap<String, String> voucherDetails=new HashMap<String, String>();
			dom=genericUtility.parseString(scheduleParamXML);
			
			java.util.Date today=new java.util.Date();
			Calendar cal = Calendar.getInstance(); 
			cal.setTime(today); 
			today = cal.getTime();
			SimpleDateFormat sdf=new SimpleDateFormat(genericUtility.getApplDateFormat());
			sysDate=sdf.format(today);
			
			NodeList paramList = dom.getElementsByTagName( "SCHEDULE" );
            noOfParam = paramList.getLength();            
            NodeList parentNodeList = null,childNodeList = null;
			Node parentNode = null,childNode = null;			
			int childNodeListLength = 0;
			String childNodeName = null;						
			parentNodeList = dom.getElementsByTagName("ACTUALPARAMETERS");
			parentNode = parentNodeList.item(0);
			childNodeList = parentNode.getChildNodes();
			childNodeListLength = childNodeList.getLength();
			System.out.println("childNodeListLength  !!!!!"+childNodeListLength);
			
			//Select porder_no from 'SUPPLIER_BILL'
//			sql1 = "select TRAN_ID as supplier_bill_id,PORDER_NO,BILL_NO,BILL_AMT,BILL_DATE,VOUCHER_NO from SUPPLIER_BILL where MATCH_STATUS='1'";  
			sql1 = "select TRAN_ID as supplier_bill_id,PORDER_NO,BILL_NO,BILL_AMT,BILL_DATE,VOUCHER_NO"
					+ " from SUPPLIER_BILL where (CASE WHEN MATCH_STATUS IS NULL THEN '1' ELSE MATCH_STATUS END) ='1'";// VALLABH KADAM check for NULL also 25/MAY/15  
			pstmt1 = conn.prepareStatement(sql1);			
			rs1 = pstmt1.executeQuery();
			while (rs1.next())
			{	
				suppBillId=rs1.getString("supplier_bill_id");
				porderNo=rs1.getString("PORDER_NO");
				billNo=rs1.getString("BILL_NO");
				billDate=rs1.getString("BILL_DATE");
				bllDate=rs1.getDate("BILL_DATE");
				billAmt=rs1.getString("BILL_AMT");
				voucherNo=rs1.getString("VOUCHER_NO");
				/**
				 * If voucherNo is NULL
				 * select from table 'VOUCHER'
				 * for porderNo,billNo
				 * and update it in table 'SUPPLIER_BILL'
				 * 25/MAY/15  
				 * */
				if(voucherNo==null || voucherNo.trim().length()<=0){	
					sql4 ="select TRAN_ID from voucher where BILL_NO=? AND PURC_ORDER=?";  
					pstmt4 = conn.prepareStatement(sql4);
					pstmt4.setString(1,billNo);							
					pstmt4.setString(2,porderNo);		
					rs4 = pstmt4.executeQuery();
					while (rs4.next())
						{
						voucherNo=rs4.getString("TRAN_ID");						
						}
					rs4.close();
					rs4=null;
					pstmt4.close();
					pstmt4=null;
					
					/**UPDATE voucherNo in table 'SUPPLIER_BILL'*/
					sql5 = "UPDATE SUPPLIER_BILL set VOUCHER_NO=? WHERE TRAN_ID=?";
					pstmt5 = conn.prepareStatement(sql5);
					pstmt5.setString(1,voucherNo.trim());
					pstmt5.setString(2,suppBillId.trim());		
					pstmt5.executeUpdate();
					pstmt5.close();
					pstmt5 = null;
				}
				
//				sql2 = "select A.tran_id,A.tran_id__rcp from VOUCHER A,VOUCHRCP B where A.TRAN_ID=B.TRAN_ID AND B.PURC_ORDER=? and A.confirmed='N' ";  //check from vouchrcp
				sql2 = "select A.tran_id,A.tran_id__rcp from VOUCHER A,VOUCHRCP B where A.TRAN_ID=B.TRAN_ID AND B.PURC_ORDER=? AND A.tran_id=? and A.confirmed='N' ";  //check from vouchrcp
//				sql2 = "select tran_id,tran_id__rcp from VOUCHER where TRAN_ID=? and PURC_ORDER=? AND CONFIRMED='N'";
				pstmt2 = conn.prepareStatement(sql2);
				pstmt2.setString(1,porderNo);
				pstmt2.setString(2,voucherNo);
				rs2 = pstmt2.executeQuery();
				while (rs2.next())
				{
					//cnt1=rs2.getInt("cnt");
					voucherNo=rs2.getString("tran_id");
					receiptNo=rs2.getString("tran_id__rcp");
				
					//Match the billNo,billDate,billAmt in table VOUCHER for voucherNo.
					vCnt=threeWayMatching(suppBillId,voucherNo,porderNo,receiptNo,billNo,billDate,bllDate,billAmt,"VOUCHER",conn,xtraParams);
					if(vCnt>0)
					{
						//Match the billNo,billDate,billAmt in table PORCP for porderNo
						rcpCnt=threeWayMatching(suppBillId,voucherNo,porderNo,receiptNo,billNo,billDate,bllDate,billAmt,"PORCP",conn,xtraParams);
						if(rcpCnt>0){
						voucherDetails.put(suppBillId, voucherNo);
						rcpCnt=0;
						}						
					}
				}				
			}
			rs1.close();
			rs1=null;
			pstmt1.close();
			pstmt1=null;
			
			//Check HashMap size for match found
			System.out.println("VoucherDetails Map size :-["+voucherDetails.size()+"]");
			if(voucherDetails.size()>0){
				// For VOUCHER confirmation.
				Set<String> keySet=voucherDetails.keySet();
				
				//iterate keySet
				for(String key: keySet){
					System.out.println("Supplier_bill_id :- ["+key+"]");
					voucherNo=voucherDetails.get(key);
					System.out.println("Voucher No 2 :- ["+voucherNo+"]");					
//				retString=confirmVoucher("voucher",voucherNo,key,xtraParams,conn);   //CONFIRMETION process commented 16/MAR/15
					
//				if(retString!=null && (retString.indexOf("VTSUCC1")>-1)){
//					strToWrite = "VOUCHER Confirmation for Tran Id "+voucherNo+" AND supplier bill id :-["+key+"] : \r\n\r\n";
					cnt1=updateStatus(key,voucherNo,"3",conn);		//Update the status from table SUPPLIER_BILL set MATCH_STATUS to '3', 'MATCH'
					if(cnt1>0){
					System.out.println("Status update count2 :-["+cnt1+"]");
					conn.commit();
					}// Connection commit
//				}
				else
				{
					System.out.println("Transaction ROLL BACK At voucher confirm***********");
					strToWrite = "VOUCHER UnConfirmation for Tran Id "+voucherNo+" is :  "+retString+" \r\n\r\n";
					conn.rollback();						//Connection roll back
				}
			}
			}
			else
			{
				System.out.println("***************No Matching DATA found FINALLY*************");
			}
		}		
		catch(Exception e)
		{
			System.out.println(">>>>>>>>>>>>>In catch:");
			e.printStackTrace();
			System.out.println(e);
		}
		finally
		{
			if(conn!=null)
			{
//				conn.commit();
				conn.close();
				conn=null;
			}			
		}
		return retString;
	}
	/**
	 * The method match the bellow fields 
	 * billNo, billDate, billAmt
	 * return matching count
	 * in tables VOUCHER or PORCP
	 * */
	private int threeWayMatching(String suppBillId, String voucherNo, String porderNo,String receiptNo, String billNo, String billDate,Date bllDate, String billAmt,String tableName, Connection conn, String xtraParams)
    {
		int finalCount=0,billNoCnt=0,billAmtCnt=0,billDateCnt=0,failCnt=0;
		
		if(tableName.equalsIgnoreCase("PORCP"))
		{
			billNoCnt=matchTableField("PORCP","TRAN_ID",receiptNo,"INVOICE_NO",billNo,conn);		// Change to pordNo to receiptNo
			if(billNoCnt<=0){
			//Insert mismatch status of PORCP bill_no in table PAY_3WAY_STAT
				failCnt=insertMismatchReason(suppBillId,voucherNo,porderNo,billNo,bllDate,"PORCP","BILL_NO",conn,
						xtraParams,"BNO_MISMMATCH_REAS");
			}
			billDateCnt=matchTableField("PORCP","TRAN_ID",receiptNo,"INVOICE_DATE",billDate,conn);
			if(billDateCnt<=0){
			//Insert mismatch status of PORCP bill_date in table PAY_3WAY_STAT
				failCnt=insertMismatchReason(suppBillId,voucherNo,porderNo,billNo,bllDate,"PORCP","BILL_DATE",conn,
						xtraParams,"BDT_MISMMATCH_REAS");
			}
			billAmtCnt=matchTableField("PORCP","TRAN_ID",receiptNo,"AMOUNT",billAmt,conn);
			if(billAmtCnt<=0){
			//Insert mismatch status of PORCP bill_amt in table PAY_3WAY_STAT
				failCnt=insertMismatchReason(suppBillId,voucherNo,porderNo,billNo,bllDate,"PORCP","BILL_AMT",conn,
						xtraParams,"BAMT_MISMMATCH_REAS");
			}
		}
		else
		{
			billNoCnt=matchTableField("VOUCHER","TRAN_ID",voucherNo, "BILL_NO", billNo, conn);		
			if(billNoCnt<=0){
				//Insert mismatch status of VOUCHER bill_no in table PAY_3WAY_STAT
				failCnt=insertMismatchReason(suppBillId,voucherNo,porderNo,billNo,bllDate,"VOUCHER","BILL_NO",conn,
						xtraParams,"BNO_MISMMATCH_REAS");
			}
			billDateCnt=matchTableField("VOUCHER","TRAN_ID",voucherNo, "BILL_DATE", billDate, conn);
			if(billDateCnt<=0){
				//Insert mismatch status of VOUCHER bill_date in table PAY_3WAY_STAT
				failCnt=insertMismatchReason(suppBillId,voucherNo,porderNo,billNo,bllDate,"VOUCHER","BILL_DATE",conn,
						xtraParams,"BDT_MISMMATCH_REAS");
			}
//			billAmtCnt=matchTableField("VOUCHER","TRAN_ID",voucherNo, "BILL_AMT", billAmt, conn);
			billAmtCnt=matchTableField("VOUCHER","TRAN_ID",voucherNo, "BILL_AMT", billAmt, conn);
			if(billAmtCnt<=0){
				//Insert mismatch status of VOUCHER bill_amt in table PAY_3WAY_STAT				
				failCnt=insertMismatchReason(suppBillId,voucherNo,porderNo,billNo,bllDate,"VOUCHER","BILL_AMT",conn,
						xtraParams,"BAMT_MISMMATCH_REAS");
			}
		}
		if(billNoCnt>0 && billDateCnt>0 && billAmtCnt>0){
			finalCount=1;
		}
	    return finalCount;
    }
	
	private int insertMismatchReason(String suppBillId, String voucherNo, String porderNo, String billNo, Date billDate, String tableName,
			String fieldName, Connection conn,String xtraParams, String finParam)
    {
		int insertCnt=0,updateCnt=0;
		String sql="";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String rcpTranId="",siteCode="",loginSiteCode="",suppCode="",genDescr="",genShDescr="",tranId="",resCode="",roleCode="";
		String keyString="",keyCol="",tranSer1="",retString="";
		CommonConstants commonConstants = new CommonConstants();
		Calendar currentDate = Calendar.getInstance();
		boolean isdetFlag =  false;
		String chgUser = null;
		String chgTerm = null;
		
		Timestamp sysDate = null,amddate=null;
		
//		java.util.Date currentDate = new java.util.Date();	
		System.out.println("BIlllllllllllllll NO:-"+billNo);
		try
		{		
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
//			SimpleDateFormat sdfDb= new SimpleDateFormat(genericUtility.getApplDateFormat());			
//			sysDate = java.sql.Timestamp.valueOf( sdfDb.format(currentDate)+" 00:00:00.0");
			
			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");
			
			System.out.println("TRAN DATE :-"+sysDate);
			
			//Select more voucher details
			sql ="select tran_id__rcp,site_code,supp_code from VOUCHER where tran_id=?";  
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,voucherNo);		
			rs = pstmt.executeQuery();
			while (rs.next())
				{
				rcpTranId=rs.getString("tran_id__rcp");
				siteCode=rs.getString("site_code");
				suppCode=rs.getString("supp_code");
				}
			rs.close();
			rs=null;
			pstmt.close();
			pstmt=null;
			
			//Select Gencodes details
			sql ="select gc.descr,gc.sh_descr, gc.udf_str1,gc.FLD_VALUE from gencodes gc,finparm fp WHERE fld_name=?"
					+ " AND fp.var_name=? AND mod_name='W_SUPP_BILL_MISMATCH' AND gc.FLD_VALUE=fp.var_value";  
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,fieldName);		
			pstmt.setString(2,finParam);		
			rs = pstmt.executeQuery();
			while (rs.next())
				{
				genDescr=rs.getString("descr");
				genShDescr=rs.getString("sh_descr");
				roleCode=rs.getString("udf_str1");
				resCode=rs.getString("FLD_VALUE");
				}
			rs.close();
			rs=null;
			pstmt.close();
			pstmt=null;
			
			
			isdetFlag =  false;
			StringBuffer xmlBuff = null;
			String xmlString = "";
			xmlBuff = new StringBuffer();
					
			xmlBuff.append("<?xml version='1.0' encoding='UTF-8'?>\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("supp_bill_mismatch").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("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
			xmlBuff.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
			
			xmlBuff.append("<description>").append("Header0 members").append("</description>");
			xmlBuff.append("<Detail1 objContext=\"1\" objName=\"supp_bill_mismatch\" domID=\"\" dbID=\"\">");
			xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
			      
			xmlBuff.append("<tran_id/>");
			xmlBuff.append("<tran_date><![CDATA["+ sdf.format(sysDate) +"]]></tran_date>");	
			xmlBuff.append("<site_code><![CDATA["+ siteCode +"]]></site_code>");
			xmlBuff.append("<supp_code><![CDATA["+ suppCode +"]]></supp_code>");
			xmlBuff.append("<purc_order><![CDATA["+ porderNo +"]]></purc_order>");
			xmlBuff.append("<tran_id__rcp><![CDATA["+ rcpTranId +"]]></tran_id__rcp>");
			xmlBuff.append("<tran_id__vch><![CDATA["+ voucherNo +"]]></tran_id__vch>");
			xmlBuff.append("<bill_no><![CDATA["+ billNo +"]]></bill_no>");
			xmlBuff.append("<bill_date><![CDATA["+ sdf.format(billDate) +"]]></bill_date>");
			xmlBuff.append("<reas_code><![CDATA["+ resCode +"]]></reas_code>");
			xmlBuff.append("<remarks><![CDATA["+ genDescr +"]]></remarks>");
			xmlBuff.append("<role_code><![CDATA["+roleCode+"]]></role_code>");
			xmlBuff.append("<status><![CDATA["+ "2" +"]]></status>");
			xmlBuff.append("<status_date><![CDATA["+ sdf.format(sysDate) +"]]></status_date>");
			xmlBuff.append("<status_remarks><![CDATA["+ genDescr+" "+tableName +"]]></status_remarks>");
			xmlBuff.append("<add_date><![CDATA["+ sdf.format(sysDate) +"]]></add_date>");
			xmlBuff.append("<add_user><![CDATA["+chgUser+"]]></add_user>");
			xmlBuff.append("<add_term><![CDATA["+chgTerm+"]]></add_term>");
			xmlBuff.append("<chg_date><![CDATA["+ sdf.format(sysDate) +"]]></chg_date>");
			xmlBuff.append("<chg_user><![CDATA["+chgUser+"]]></chg_user>");
			xmlBuff.append("<chg_term><![CDATA["+chgTerm+"]]></chg_term>");
			xmlBuff.append("</Detail1>");
			
			if(xmlBuff!=null)
			{
			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()");
			loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			System.out.println("==site code =="+loginSiteCode);
			System.out.println("Printing xmlString"+xmlString);
			System.out.println("Going to save data method++++++++++++++++++++++++");
            if(xmlString != null && xmlString.trim().length()>0)
            {
            	retString = saveData(loginSiteCode,xmlString,conn);
            }
			System.out.println("retString at SAVE data :-"+retString);
			
			//Generate new TRAN_ID here
//			tranId=generateTranId(xtraParams,conn);			
//			System.out.println("@@@@ generated TRAN ID ******** :["+tranId+"]");
			
			//INSERT the record in table PAY_3WAY_STAT
//			sql = "INSERT INTO PAY_3WAY_STAT (TRAN_ID,TRAN_DATE,SITE_CODE,SUPP_CODE,PURC_ORDER,TRAN_ID__RCP,TRAN_ID__VCH,BILL_NO,BILL_DATE,"
//					+ "REAS_CODE,REMARKS,STATUS,STATUS_REMARKS)"
//					+ " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
//			pstmt = conn.prepareStatement(sql);
//			pstmt.setString(1,tranId);
//			pstmt.setTimestamp(2, sysDate);;
//			pstmt.setString(3,siteCode);
//			pstmt.setString(4,suppCode);
//			pstmt.setString(5,porderNo);
//			pstmt.setString(6,rcpTranId);
//			pstmt.setString(7,voucherNo);
//			pstmt.setString(8,billNo);
//			pstmt.setDate(9,billDate);
//			pstmt.setString(10,resCode);
//			pstmt.setString(11,genDescr);
//			pstmt.setString(12,"2");
//			pstmt.setString(13,genDescr+" "+tableName);			
//			insertCnt=pstmt.executeUpdate();
//			pstmt.close();
//			pstmt=null;
			
			if (retString.indexOf("Success") > -1)
			{
				System.out.println("suppBillId at mismatch status update :--["+suppBillId+"]");
				updateCnt+=updateStatus(suppBillId,voucherNo,"2",conn);		//Update the status from table SUPPLIER_BILL set MATCH_STATUS to '2' as REJECTED
				if(updateCnt>0)
				{
					System.out.println("Connection commit at not matching **********");
					conn.commit();			//Connection commit
				}
				else
				{
					System.out.println("Connection roll back at not matching ***********");
					conn.rollback();		//Connection roll back
				}
			}
		}
		catch(SQLException sqlE){
			sqlE.printStackTrace();
		}
		catch(Exception e){
			e.printStackTrace();
		}
	    return insertCnt;
    }
	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;
	}
	/**
	 * The method auto generate TRAN_ID
	 * */
	private String generateTranId(String xtraParams, Connection conn){
		String sql="";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String keyString="",keyCol="",tranSer1="",tranId="",loginSiteCode="";
		CommonConstants commonConstants = new CommonConstants();
		try{
		//Generate new TRAN_ID here
		sql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE lower(TRAN_WINDOW) = 'w_supp_bill_mismatch'";
		System.out.println("keyStringQuery--------->>"+sql);
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next())
		{ 
			keyString = rs.getString(1);
			keyCol = rs.getString(2);
			tranSer1 = rs.getString(3);				
		}
		rs.close();
		rs=null;
		pstmt.close();
		pstmt =null;
		
		loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
		String xmlValues = "";
		String tranDateStr = getCurrdateAppFormat();
		xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
		xmlValues = xmlValues + "<Header></Header>";
		xmlValues = xmlValues + "<Detail1>";
		xmlValues = xmlValues +	"<dummy>D</dummy>";
		xmlValues = xmlValues + "<site_code>" + loginSiteCode + "</site_code>";	
		xmlValues = xmlValues + "<tran_date>" + tranDateStr + "</tran_date>"; 
		xmlValues = xmlValues +"</Detail1></Root>";
		System.out.println("xmlValues  :["+xmlValues+"]");
		TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", commonConstants.DB_NAME);
		tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
		}
		catch(SQLException seqE){
			seqE.printStackTrace();
		}
		catch (ITMException itmE) {
			itmE.printStackTrace();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		System.out.println("@@@@ generated TRAN ID ******** :["+tranId+"]");
		return tranId;
	}
	private String getCurrdateAppFormat()
	{
		String s = "";
		try
		{
			java.util.Date date = null;
			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			System.out.println(genericUtility.getDBDateFormat());
			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = simpledateformat.parse(timestamp.toString());
			timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
			s = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
		}
		catch(Exception exception)
		{
			System.out.println("Exception in  getCurrdateAppFormat " + exception.getMessage());
		}
		return s;
	}
	/**
	 * The method match single field
	 * in respective table
	 * and return matching count
	 * */
	private int matchTableField(String tableName, String fieldName1, String fieldValue1, String fieldName2, String fieldValue2, Connection conn)
    {
		int cnt=0;
		String sql="";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		Timestamp bllDate = null,amddate=null;
		java.util.Date tempDt = new java.util.Date();	
		
		
		try{
		if(fieldName2.equalsIgnoreCase("BILL_DATE")||fieldName2.equalsIgnoreCase("INVOICE_DATE"))
			{
			SimpleDateFormat sdfDb= new SimpleDateFormat(genericUtility.getDBDateFormat());
			tempDt=sdfDb.parse(fieldValue2);
			bllDate = java.sql.Timestamp.valueOf( sdfDb.format(tempDt)+" 00:00:00.0");			
		}
		
		sql ="select count(*) as cnt from "+tableName+" where "+fieldName1+"=? AND "+fieldName2+"=?";  
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1,fieldValue1);
		if(fieldName2.equalsIgnoreCase("BILL_DATE")||fieldName2.equalsIgnoreCase("INVOICE_DATE"))
		{
			pstmt.setTimestamp(2,bllDate);
		}
		else
		{
		pstmt.setString(2,fieldValue2);
		}
		rs = pstmt.executeQuery();
		while (rs.next())
		{
			cnt=rs.getInt("cnt");
		}
		rs.close();
		rs=null;
		pstmt.close();
		pstmt=null;
		}
		catch(SQLException sqlE){
			sqlE.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
	    return cnt;
    }	
	/**
	 * The method get voucherNo as tranId
	 * call confirm voucher	 
	 * */
	public String confirmVoucher(String objName,String tranId,String suppBillId,String xtraParams,Connection conn){
		int cnt=0,cnt1=0;
		String methodName = "",compName = "",compType="",retString = "",serviceCode = "",serviceURI = "",actionURI = "";
		String forcedFlag="N";
		String sql;
		PreparedStatement pstmt=null;
		ResultSet rs = null;	
		System.out.println("********************In confirm voucher **************");
		try
		{
			methodName = "gbf_post";
			actionURI = "http://NvoServiceurl.org/" + methodName;
			
			sql = "SELECT SERVICE_CODE,COMP_NAME,COMP_TYPE FROM SYSTEM_EVENTS WHERE OBJ_NAME = ? AND EVENT_CODE = 'pre_confirm'";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,objName);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				serviceCode = rs.getString("SERVICE_CODE");
				compName = rs.getString("COMP_NAME");
				compType = rs.getString("COMP_TYPE");
			}
			rs.close();
			rs=null;
			pstmt.close();
			pstmt=null;
			System.out.println("serviceCode = "+serviceCode+" compName "+compName+" compType "+compType);
			
			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");
			}
			rs.close();
			rs=null;
			pstmt.close();
			pstmt=null;
			System.out.println("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[5];
			
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "component_type"), XMLType.XSD_STRING, ParameterMode.IN);
			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(compType);
			aobj[1] = new String(compName);
			aobj[2] = new String(tranId);
			aobj[3] = new String(xtraParams);
			aobj[4] = 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 eX)
		{			
		eX.printStackTrace();
		}		
		return retString;
	}	
	/**
	 * The method update the status 
	 * from table SUPPLIER_BILL 
	 * set MATCH_STATUS to '3' or '2' that is 'Closed' or 'Rejected'
	 * */
	private int updateStatus(String suppBillId, String voucherNo,String status, Connection conn)
    {
		String sql;
		PreparedStatement pstmt=null;
		int cnt=0;
		try{
		sql = "UPDATE SUPPLIER_BILL set MATCH_STATUS=? WHERE tran_id=? AND voucher_no=?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1,status.trim());
		pstmt.setString(2,suppBillId.trim());
		pstmt.setString( 3, voucherNo.trim());		
		cnt = pstmt.executeUpdate();
		System.out.println("Status update count1 :-["+cnt+"]");
		pstmt.close();
		pstmt = null;
		}
		catch(SQLException sqlE)
		{
			sqlE.printStackTrace();
		}
	    return cnt;
    }	
	@Override
	public String schedule(HashMap arg0) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String schedulePriority(String arg0) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
}
