package ibase.webitm.ejb.mfg;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ITMDBAccessLocal;
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.text.SimpleDateFormat;
import java.util.Calendar;

import javax.ejb.Stateless; // added for ejb3

@Stateless
public  class WorderCampaign extends ActionHandlerEJB implements WorderCampaignLocal,WorderCampaignRemote{
	
	 
	 String  utranid="";
	
	public String confirm(String tranId, String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		
		System.out.println("--------------confirm method of WorderCampaign------------- : "+tranId);
		String retString = "";		
		try
		{
				System.out.println(">>>>BEFORE"+utranid);
				utranid = "";
				System.out.println(">>>>AFTER"+utranid);
				retString = campaign(tranId,xtraParams,forcedFlag);
				System.out.println("retString >>>>"+retString);
			
		}
		catch(Exception e)
		{
			System.out.println("Exception :WorderCampaign :confirm():" + e.getMessage() + ":");
			retString = GenericUtility.getInstance().createErrorString(e);
			e.printStackTrace();
		}
		
		return retString;
	}
	
	
	
	public String campaign(String tranId, String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		
		System.out.println("campaign() called"+tranId);
		ResultSet rs=null;
		Connection conn=null;
        PreparedStatement pstmt=null;
		String errString="",sql="",loginCode="",loginSite="",systemDate="";
		Calendar currentDate;
		SimpleDateFormat simpleDateFormatObj;
		//String keyString = null;
	    //ConnDriver ConnDriver = new ConnDriver();
	    int count=0;
		ITMDBAccessLocal itmdbAccess=new ITMDBAccessEJB();
		String wostatus="",worder="",chgTerm="",chgUser="";
		try
		{
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			GenericUtility genericUtility = GenericUtility.getInstance();
			
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "chg_term");
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "chg_user");
			System.out.println(">>>>>>>chgTerm and  t"+chgTerm+">>>>> chgUser"+chgUser);
			currentDate = Calendar.getInstance();
			simpleDateFormatObj = new SimpleDateFormat(genericUtility.getApplDateFormat());
			systemDate = simpleDateFormatObj.format(currentDate.getTime());
			System.out.println("Now the date is :=>  " + systemDate);
			
			/*loginCode=GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			loginCode=loginCode==null ? "" :loginCode.trim();*/
			loginSite = GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			System.out.println("loginCode---->>["+loginCode+"]");
			conn.setAutoCommit(false);
			
			
			System.out.println("loginCode"+loginCode+"<<<<<<<loginSite"+loginSite);
			
			if(utranid.trim().length() <= 0)
			{
			utranid = generateTranId("w_worder", loginSite, conn);
		    }
			
			System.out.println("GENERATED TRAN ID"+utranid);
			
			sql = "select work_order from workorder where tran_id = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				worder = rs.getString(1) == null ? "":rs.getString(1);
			}
			pstmt.close();
			rs.close();
			pstmt = null;
			rs = null;
			System.out.println("WORK ORDER NO"+worder);
			sql = "select status from workorder where tran_id = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				wostatus = rs.getString(1) == null ? "":rs.getString(1);
			}
			pstmt.close();
			rs.close();
			pstmt = null;
			rs = null;
			System.out.println("wostatusWEWEWEE >>>>>>>>>>>>>>>>>>."+wostatus);
			if(wostatus.equalsIgnoreCase("P") || wostatus.equalsIgnoreCase("M") || wostatus.equalsIgnoreCase("U"))
			{   
				
				    System.out.println("ENTER IN STATUS CHECK >>>>>");
					sql = "UPDATE WORKORDER SET CAMPGN_NO = ? WHERE WORK_ORDER = ?";
	                pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,utranid);
					pstmt.setString(2,worder);
					count = pstmt.executeUpdate();
					if(count < 1)
				 	{
						System.out.println("updation of record is failed");
					}
					else
					{
						conn.commit();
					}
					
					pstmt.close(); 
					pstmt = null;  
					System.out.println("NO. OF RECORDS UPDATED FOR CURRENT PERIODE.. "+count);
				
				
				
			   
				sql = "INSERT INTO WORDER_CAMPAIGN (WORK_ORDER,CAMPGN_NO,CHG_DATE,CHG_USER,CHG_TERM) VALUES (?,?,?,?,?)";
                pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,worder);
				pstmt.setString(2,utranid);
				pstmt.setString(3,systemDate);
				pstmt.setString(4,chgUser);
				pstmt.setString(5,chgTerm);
				count = pstmt.executeUpdate();
				if(count < 1)
			 	{
					System.out.println("Insertion of record is failed");
				}
				else
				{
					conn.commit();
				}
				
				pstmt.close(); 
				pstmt = null;  
				System.out.println("NO. OF RECORDS INSERTED FOR CURRENT PERIODE.. "+count);
				
				
			}
			
		}catch(SQLException se)
		{

			System.out.println("SQLException : class StockValAdjTranConf : ");
			se.printStackTrace();
			try
			{
			  conn.rollback();
			  
			    errString = itmdbAccess.getErrorString("", "STKVNCONF", "", "", conn);
				return errString;
			}
			catch(Exception e){
				System.out.println("Exception : Occure during rollback........");e.printStackTrace();
				}
		
		}
		catch(Exception e)
		{
			try
			{
				conn.rollback();
				errString = itmdbAccess.getErrorString("", "STKVNCONF", "", "", conn);
				return errString;
			}
			catch (Exception e1)
			{
			}
			e.printStackTrace();
			System.out.println("Exception Class [StockValAdjTranConf]::"+e.getMessage());
			
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;					
				}	
				if (conn!=null)
				{
					conn.close();
					conn=null;
				}
							
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				e.printStackTrace();
				
			}
		}
		
		errString = itmdbAccess.getErrorString("","STKVACONF",loginCode);
		
		System.out.println("[WorderCampaign]errstring :"+errString);
		return errString;
	}
	 

    public static String generateTranId(String windowName, String siteCode, Connection conn) throws ITMException {
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	String selSql = "";
	String tranId = "";
	String tranSer = "";
	String keyString = "";
	String keyCol = "";
	String xmlValues = "";
	java.sql.Timestamp currDate = null;
	GenericUtility genericUtility = GenericUtility.getInstance();

	try {

	    SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());
        currDate = new java.sql.Timestamp(System.currentTimeMillis());
	    String currDateStr = sdfAppl.format(currDate);
        selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
	    pstmt = conn.prepareStatement(selSql);
	    pstmt.setString(1, windowName);
	    rs = pstmt.executeQuery();
	    if (rs.next()) {
		keyString = rs.getString("KEY_STRING");
		keyCol = rs.getString("TRAN_ID_COL");
		tranSer = rs.getString("REF_SER");
	    }
	    rs.close();
	    rs = null;
	    pstmt.close();
	    pstmt = null;
	    keyString = "seq10";
	    System.out.println("keyString :" + keyString);
	    System.out.println("keyCol :" + keyCol);
	    System.out.println("tranSer :" + tranSer);

	    xmlValues = "<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
	    xmlValues = xmlValues + "<Header></Header>";
	    xmlValues = xmlValues + "<Detail1>";
	    xmlValues = xmlValues + "<tran_id></tran_id>";
	    xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";
	    xmlValues = xmlValues + "<tran_date>" + currDateStr + "</tran_date>";
	    xmlValues = xmlValues + "</Detail1></Root>";
	    System.out.println("xmlValues  :[" + xmlValues + "]");
	    TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
	    tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
	    System.out.println("tranId :" + tranId);
	} catch (SQLException ex) {
	    System.out.println("Exception ::" + selSql + ex.getMessage() + ":");
	    ex.printStackTrace();
	    throw new ITMException(ex);
	} catch (Exception e) {
	    System.out.println("Exception ::" + e.getMessage() + ":");
	    e.printStackTrace();
	    throw new ITMException(e);
	} finally {
	    try {
		if (rs != null) {
		    rs.close();
		    rs = null;
		}
		if (pstmt != null) {
		    pstmt.close();
		    pstmt = null;
		}
	    } catch (Exception e) {
	    }
	}
	return tranId;
    }
	  
	 
}
