package ibase.webitm.ejb.mfg;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ITMDBAccessLocal;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;
import ibase.webitm.ejb.mfg.MfgCommon;

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 javax.ejb.Stateless;

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

@Stateless
public class WorderCampaign extends ProcessEJB implements WorderCampaignLocal, WorderCampaignRemote {
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();

	@Override
	public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException, ITMException {
		System.out.println("^^^^^^^ Inside WorderCampaign -> GetData Method 1 ^^^^^^^");
		Document detailDom = null;
		Document headerDom = null;
		String retString = "";

		System.out.println("XMLSTRING ..> " + xmlString);
		System.out.println("XMLSTRING2 ..> " + xmlString2);

		try {
			if (xmlString != null && xmlString.trim().length() != 0) {
				headerDom = genericUtility.parseString(xmlString);
			}
			if (xmlString2 != null && xmlString2.trim().length() != 0) {
				detailDom = genericUtility.parseString(xmlString2);
			}
			retString = getData(headerDom, detailDom, windowName, xtraParams);
		} catch (Exception e) {
			System.out.println("Exception WorderCampaign GetData Method 1");
			e.printStackTrace();
			retString = itmDBAccessEJB.getErrorString("", "", "", 1); // failed to execute
			throw new ITMException(e);
		}
		return retString;
	}

	@Override
	public String getData(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException, ITMException {
		System.out.println("===========Inside WorderCampaign -> GetData Method 2 ================");
		String retString = "";
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		//	Node childNode = null;
		ConnDriver connDriver = null;
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sql = "",workOrdType="";
		String subQuery=" ";
		String workOrdType1="",resultWorkOrdType="",workOrdTypeSplit="";
		ITMDBAccessEJB itmDBAccessEJB = null;
		boolean isError = false;

		SimpleDateFormat sdf = null;
		StringBuffer resultData = new StringBuffer();
		boolean dataFound = false;

		String siteCode = "", fromDate = "", toDate = "";
		java.sql.Date fromDateDtSql = null, toDateDtSql = null;
		java.util.Date fromDateDtUtil = null, toDateDtUtil = null;
		String ordDate="",relDate="",dueDate="",workOrder="",tranID="",orderType="",itemCode="",status="",statDate="",unitStd="",tranMps="",
		acctWp="",cctrWp="",shdscr="",lotNo="",bomCode="",phyAtt1="",phyAtt2="",phyAtt3="",phyAtt4="",phyAtt5="",phyAtt6="",
		addUser="",addTerm="",addDate="",chgDate="",chgUser="",chgTerm="",valMfg="",campNo="",loginSiteCode="";
		Double quantity = 0.0,currQty=0.0,conQstd=0.0,stdQty=0.0,quanMps=0.0;

		String locGroupHdr="",locGroup="",subQuery1="";  // added by cpatil on 22/05/14 for locGroup display 

		try {
			sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			itmDBAccessEJB = new ITMDBAccessEJB();


			loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			System.out.println("loginSiteCode :"+loginSiteCode);
			parentNodeList = detailDom.getElementsByTagName("Detail1");
			parentNode = parentNodeList.item(0);
			childNodeList = parentNode.getChildNodes();
			for (int i = 0; i < childNodeList.getLength(); i++) {


				fromDate = genericUtility.getColumnValue("date_from", headerDom);
				toDate = genericUtility.getColumnValue("date_to", headerDom);
				locGroupHdr = checkNull( genericUtility.getColumnValue("loc_group", headerDom));    // added by cpatil on 22/05/14 for locGroup display
				System.out.println(">>>>>>fromDate["+fromDate+"]>>>>toDate["+toDate+"]locGroupHdr["+locGroupHdr+"]");

				//System.out.println(">>>>>>>>>>MNNNN");
				fromDateDtUtil = sdf.parse(fromDate);
				fromDateDtSql = new java.sql.Date(fromDateDtUtil.getTime());


				//System.out.println(">>>>>>>>>>MNNNN>>>");
				toDateDtUtil = sdf.parse(toDate);
				toDateDtSql = new java.sql.Date(toDateDtUtil.getTime());


			}
			//Start Added by chandrashekar on 12-Feb-15
			workOrdType = new MfgCommon().getEnvMfg("999999","WORK_ORD_TYPE", conn);
			System.out.println("workOrdType>>>>["+workOrdType+"]");
			if (workOrdType == null || "NULLFOUND".equalsIgnoreCase(workOrdType))
			{
				workOrdType = "";
			}
			if(workOrdType != null && (workOrdType.trim().length() > 0))
			{
				String[] arrStr =workOrdType.split(",");
				int len =arrStr.length;
				for(int i =0;i<len;i++)
				{
					workOrdType1 =arrStr[i];
					workOrdTypeSplit=workOrdTypeSplit+"'"+workOrdType1+"',";
				}
				resultWorkOrdType = workOrdTypeSplit.substring(0, workOrdTypeSplit.length() - 1);
				subQuery= " AND (WORKORDER.ORDER_TYPE in("+resultWorkOrdType+"))";
			}
			
			if(locGroupHdr != null && (locGroupHdr.trim().length() > 0))      // added by cpatil on 22/05/14 for locGroup display
			{
				subQuery1= "  AND WORKORDER.LOC_GROUP = '"+locGroupHdr+"' ";
			}
			
			//End Added by chandrashekar on 12-Feb-15
			sql ="SELECT WORKORDER.TRAN_ID,WORKORDER.WORK_ORDER,"
				+ "WORKORDER.ORDER_TYPE,"
				+ "WORKORDER.ITEM_CODE,WORKORDER.SITE_CODE,WORKORDER.QUANTITY,"
				+ "WORKORDER.ORD_DATE,WORKORDER.REL_DATE,WORKORDER.DUE_DATE,WORKORDER.STATUS,WORKORDER.STATUS_DATE,WORKORDER.ACCT_CODE__WP,"
				+ "WORKORDER.CCTR_CODE__WP,ITEM.SH_DESCR,WORKORDER.LOT_NO,WORKORDER.BOM_CODE,WORKORDER.CURRENT_BATCH_QTY,WORKORDER.VAL_MFG_TAXTRAN,"
				+ "WORKORDER.UNIT__STD,WORKORDER.CONV__QTY_STDUOM,WORKORDER.STD_QTY,WORKORDER.QUANTITY__MPS,"
				+ "ITEM.PHY_ATTRIB_1,ITEM.PHY_ATTRIB_2,ITEM.PHY_ATTRIB_3,"
				+ "ITEM.PHY_ATTRIB_4,ITEM.PHY_ATTRIB_5,ITEM.PHY_ATTRIB_6,"
				+ "WORKORDER.TRAN_ID__MPS,"
				+ "WORKORDER.ADD_DATE,WORKORDER.ADD_USER,WORKORDER.ADD_TERM,WORKORDER.CHG_DATE,"
				+ "WORKORDER.CHG_USER,WORKORDER.CHG_TERM,WORKORDER.CAMPGN_NO " 
				+ " , WORKORDER.LOC_GROUP "        // added by cpatil on 22/05/14 for locGroup display
				+ " FROM "
				+ "  WORKORDER  WORKORDER,ITEM  ITEM  WHERE  ( WORKORDER.ITEM_CODE      = ITEM.ITEM_CODE   ) AND ( WORKORDER.ORD_DATE    >=      ? )  AND ( WORKORDER.ORD_DATE    <=      ? )" 
				//+ " AND WORKORDER.ORDER_TYPE='F' "
				+ " AND WORKORDER.STATUS IN('P','M','U') AND WORKORDER.SITE_CODE = ? " //login sitecode added by mahendra dated 07-JAN-2014
				+ " AND WORKORDER.CAMPGN_NO IS NULL "+subQuery + subQuery1;//subQuery condition added by chandrashekar on 12-feb-15    // added by cpatil on 22/05/14 for locGroup display

			pStmt = conn.prepareStatement(sql);
			pStmt.setDate(1, fromDateDtSql);
			pStmt.setDate(2, toDateDtSql);
			pStmt.setString(3, loginSiteCode);
			
			rs = pStmt.executeQuery();
			while (rs.next()) {
				dataFound = true;

				tranID = rs.getString("TRAN_ID") == null ? "" : rs.getString("TRAN_ID");
				workOrder = rs.getString("WORK_ORDER") == null ? "" : rs.getString("WORK_ORDER").trim();
				bomCode = rs.getString("BOM_CODE") == null ? "" : rs.getString("BOM_CODE").trim();
				itemCode = rs.getString("ITEM_CODE") == null ? "" : rs.getString("ITEM_CODE").trim();
				siteCode = rs.getString("SITE_CODE") == null ? "" : rs.getString("SITE_CODE").trim();
				shdscr = rs.getString("SH_DESCR") == null ? "" : rs.getString("SH_DESCR").trim();
				//lotNo = rs.getString("LOT_NO") == null ? "" : rs.getString("LOT_NO").trim();
				//quantity = rs.getString("QUANTITY") == null ? 0.0 : rs.getDouble("QUANTITY");
				//tranMps = rs.getString("TRAN_ID__MPS") == null ? "" : rs.getString("TRAN_ID__MPS");
				status = rs.getString("STATUS") == null ? "" : rs.getString("STATUS").trim();
				statDate = rs.getString("STATUS_DATE") == null ? "" : sdf.format(rs.getDate("STATUS_DATE"));
				//relDate = rs.getString("REL_DATE") == null ? "" : sdf.format(rs.getDate("REL_DATE"));
				//dueDate = rs.getString("DUE_DATE") == null ? "" : sdf.format(rs.getDate("DUE_DATE"));
				//orderType = rs.getString("ORDER_TYPE") == null ? "" : rs.getString("ORDER_TYPE").trim();
				//ordDate = rs.getString("ORD_DATE") == null ? "" : sdf.format(rs.getDate("ORD_DATE"));
				//acctWp = rs.getString("ACCT_CODE__WP") == null ? "" : rs.getString("ACCT_CODE__WP").trim();
				//cctrWp = rs.getString("CCTR_CODE__WP") == null ? "" : rs.getString("CCTR_CODE__WP").trim();
				// currQty = rs.getString("CURRENT_BATCH_QTY") == null ? 0.0 : rs.getDouble("CURRENT_BATCH_QTY");
				// valMfg = rs.getString("VAL_MFG_TAXTRAN") == null ? "" : rs.getString("VAL_MFG_TAXTRAN").trim();
				// unitStd = rs.getString("UNIT__STD") == null ? "" : rs.getString("UNIT__STD").trim();
				//stdQty =   rs.getString("STD_QTY") == null ? 0.0 : rs.getDouble("STD_QTY");
				//conQstd =  rs.getString("CONV__QTY_STDUOM") == null ? 0.0 : rs.getDouble("CONV__QTY_STDUOM");
				//quanMps = rs.getString("QUANTITY__MPS") == null ? 0.0 : rs.getDouble("QUANTITY__MPS");
				//phyAtt1 = rs.getString("PHY_ATTRIB_1") == null ? "" : rs.getString("PHY_ATTRIB_1").trim();
				//phyAtt2 = rs.getString("PHY_ATTRIB_2") == null ? "" : rs.getString("PHY_ATTRIB_2").trim();
				//phyAtt3 = rs.getString("PHY_ATTRIB_3") == null ? "" : rs.getString("PHY_ATTRIB_3").trim();
				//phyAtt4 = rs.getString("PHY_ATTRIB_4") == null ? "" : rs.getString("PHY_ATTRIB_4").trim();
				//phyAtt5 = rs.getString("PHY_ATTRIB_5") == null ? "" : rs.getString("PHY_ATTRIB_5").trim();
				//	phyAtt6 = rs.getString("PHY_ATTRIB_6") == null ? "" : rs.getString("PHY_ATTRIB_6").trim();
				//	addDate = rs.getString("ADD_DATE") == null ? "" : sdf.format(rs.getDate("ADD_DATE"));
				// addUser = rs.getString("ADD_USER") == null ? "" : rs.getString("ADD_USER").trim();
				//  addTerm = rs.getString("ADD_TERM") == null ? "" : rs.getString("ADD_TERM").trim();
				chgDate = rs.getString("CHG_DATE") == null ? "" : sdf.format(rs.getDate("CHG_DATE"));
				chgUser = rs.getString("CHG_USER") == null ? "" : rs.getString("CHG_USER").trim();
				chgTerm = rs.getString("CHG_TERM") == null ? "" : rs.getString("CHG_TERM").trim();
				campNo = rs.getString("CAMPGN_NO") == null ? "" : rs.getString("CAMPGN_NO").trim();
				locGroup = rs.getString("LOC_GROUP") == null ? "" : rs.getString("LOC_GROUP").trim();   // added by cpatil on 22/05/14 for locGroup display

				resultData.append(tranID).append("\t");
				resultData.append(workOrder).append("\t");
				resultData.append(locGroup).append("\t");
				resultData.append(bomCode).append("\t");
				resultData.append(itemCode).append("\t");
				resultData.append(shdscr).append("\t");
//				resultData.append(lotNo).append("\t");
//				resultData.append(quantity).append("\t");
//				resultData.append(tranMps).append("\t");
				resultData.append(status).append("\t");
				resultData.append(statDate).append("\t");
//				resultData.append(relDate).append("\t");
//				resultData.append(dueDate).append("\t");
//				resultData.append(orderType).append("\t");
//				resultData.append(ordDate).append("\t");
				resultData.append(siteCode).append("\t");
//				resultData.append(acctWp).append("\t");
//				resultData.append(cctrWp).append("\t");
//				resultData.append(currQty).append("\t");
//				resultData.append(valMfg).append("\t");
//				resultData.append(unitStd).append("\t");
//				resultData.append(stdQty).append("\t");
//				resultData.append(conQstd).append("\t");
//				resultData.append(phyAtt1).append("\t");
//				resultData.append(phyAtt2).append("\t");
//				resultData.append(phyAtt3).append("\t");
//				resultData.append(phyAtt4).append("\t");
//				resultData.append(phyAtt5).append("\t");
//				resultData.append(phyAtt6).append("\t");
				resultData.append(chgDate).append("\t");
				resultData.append(chgUser).append("\t");
				resultData.append(chgTerm).append("\t");
//				resultData.append(addDate).append("\t");
//				resultData.append(addUser).append("\t");
//				resultData.append(addTerm).append("\t");
				resultData.append(campNo).append("\t");



				resultData.append("\n");
			}
			if(!rs.next())
			{
				System.out.println(">>>>>faildsssss");

				retString = itmDBAccessEJB.getErrorString("", "", "", "", conn);
			}
			rs.close();
			rs = null;
			pStmt.close();
			pStmt = null;
		} catch (Exception e) {
			isError = true;
			throw new ITMException(e);
		} finally {
			try {
				if (conn != null) {
					if (isError) {
						retString = itmDBAccessEJB.getErrorString("", "VTWOCFAIL", "", "", conn); // failed to execute 
					} else {
						retString = resultData.toString();
						System.out.println("RETSTRING ..> " + retString);

						if (!dataFound) {
							retString = itmDBAccessEJB.getErrorString("", "VTNOREC1", "", "", conn); // NO RECORD FOUND
						}
					}
					conn.close();
					conn = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pStmt != null) {
					pStmt.close();
					pStmt = null;
				}
			} catch (Exception e) {
				retString = e.getMessage();
				throw new ITMException(e);
			}
		}
		return retString;
	}

	private String checkNull(String value)
	{
		if (value == null)
		{
			value = "";
		}
		return value;
	}
	
	@Override
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException, ITMException {
		System.out.println("^^^^^^^ Inside process -> Process Method 1 ^^^^^^^");
		Document detailDom = null;
		Document headerDom = null;
		String retString = "";

		System.out.println("XMLSTRING ..> " + xmlString);
		System.out.println("XMLSTRING2 ..> " + xmlString2);

		try {
			if (xmlString != null && xmlString.trim().length() != 0) {
				headerDom = genericUtility.parseString(xmlString);
			}
			if (xmlString2 != null && xmlString2.trim().length() != 0) {
				detailDom = genericUtility.parseString(xmlString2);
			}
			retString = process(headerDom, detailDom, windowName, xtraParams);
		} catch (Exception e) {
			System.out.println("Exception process Process Method 1");
			e.printStackTrace();
			retString = itmDBAccessEJB.getErrorString("", "VTPFTEXE", "", 1); // failed to execute
			throw new ITMException(e);
		}
		return retString;
	}

	@Override
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException, ITMException {
		System.out.println("^^^^^^^ Inside process -> Process Method 2 ^^^^^^^");
		String retString = "";
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		ConnDriver connDriver = null;
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sql = "";
		String woItemCode = "";//Added by chandrashekar on 09-oct-14
		ITMDBAccessEJB itmDBAccessEJB = null;
		boolean isError = false;
		SimpleDateFormat sdf = null;

		ArrayList<String> headerList = new ArrayList<String>();
		String errCode = "";
		int childNodeListLength = 0;
		String childNodeName = "";
		String tranID="";
		PreparedStatement pstmt=null;
		String errString="",loginCode="",loginSite="",systemDate="";
		Calendar currentDate;
		SimpleDateFormat simpleDateFormatObj;
		java.sql.Timestamp currDate = null;
		String currAppdate="";
		Timestamp sysDate = null;
		int count=0;
		ITMDBAccessLocal itmdbAccess=new ITMDBAccessEJB();
		String wostatus="",workorder="",chgTerm="",chgUser="",utranid="";
		GenericUtility genutility = null;
		HashMap<String, String> comMap=new HashMap<String, String>();
		String campItemWise="";
		
		String locGroup="",locGroupPreVal="";
		boolean locGroupFlag = false;
		
		try {
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			itmDBAccessEJB = new ITMDBAccessEJB();
			sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());

			// added by cpatil on 23/03/15 in urse for supreme start
			sql = " SELECT VAR_VALUE FROM MFGPARM WHERE PRD_CODE ='999999' AND VAR_NAME ='CAMP_ITEM_WISE' ";
			pstmt=conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				campItemWise = rs.getString(1) == null ? "":rs.getString(1);
			}
			pstmt.close();
			rs.close();
			pstmt = null;
			rs = null;
			System.out.println("@@@@@@ campItemWise["+campItemWise+"]");
			//added by cpatil on 23/03/15 in urse for supreme end

			parentNodeList = detailDom.getElementsByTagName("Detail2");
			for (int i = 0; i < parentNodeList.getLength(); i++) {
				parentNode = parentNodeList.item(i);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				for (int j = 0; j < childNodeListLength; j++) {
					childNode = childNodeList.item(j);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equals("work_order")) {
						workorder = childNode.getFirstChild() == null ? "" : childNode.getFirstChild().getNodeValue();
					}
					
					if (childNodeName.equals("loc_group")) {   //added by cpatil on 22/05/15 for validation of loc_group
						locGroup = childNode.getFirstChild() == null ? "" : childNode.getFirstChild().getNodeValue();
						
						if(!(locGroupFlag))
						{
							locGroupPreVal = locGroup;
						}
							
					}
				}
				
				System.out.println(">>>>>locGroup i["+i+"].....locGroupPreVal["+locGroupPreVal+"]locGroup["+locGroup+"]locGroupFlag["+locGroupFlag+"]");
				if( locGroupFlag )     // added by cpatil on 22/05/14 for locGroup display
				{
					 if(!(locGroupPreVal.equalsIgnoreCase(locGroup)))
					 { 
							System.out.println(">>>>>locGroup miss match......locGroupVal["+locGroupPreVal+"]locGroup["+locGroup+"]");
							errCode = "VTLOCGRINV"; 
							retString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
							
							isError = true;
							
							return retString;
					 }
				}
				else
				{
					locGroupFlag = true;
				}

				workorder = workorder == null ? "" : workorder.trim();

				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);*/

				genutility=GenericUtility.getInstance();
				sdf=new SimpleDateFormat(genutility.getApplDateFormat());
				currDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
				currAppdate = sdf.format(currDate);
				currAppdate = genericUtility.getValidDateString( currAppdate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat() );
				sysDate = java.sql.Timestamp.valueOf( currAppdate + " 00:00:00.0" );
				/*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);

				//Start Added by chandrashekar  on 09-oct-14
				sql = "select item_code from workorder where work_order = ?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1,workorder);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					woItemCode = rs.getString("item_code") == null ? "":rs.getString("item_code");
				}
				pstmt.close();
				rs.close();
				pstmt = null;
				rs = null;

				if(campItemWise != null && "N".equalsIgnoreCase(campItemWise))  // added condition for supreme  MFGPARM  'CAMP_ITEM_WISE' define as 'N' value  
				{	
					if(utranid.trim().length() <= 0)
					{
						utranid = generateTranId("w_wocampaign", loginSite, conn);
					}
				}
				else if("Y".equalsIgnoreCase(campItemWise) || campItemWise == null || "".equalsIgnoreCase(campItemWise) || campItemWise.trim().length() == 0 )
				{	
					if(comMap.containsKey(woItemCode))
					{
						utranid= comMap.get(woItemCode);
					}
					else
					{	utranid = generateTranId("w_wocampaign", loginSite, conn);
					System.out.println("GENERATED TRAN ID"+utranid);
					comMap.put(woItemCode, utranid);
					System.out.println("comMap[["+comMap+"]]");
					}
				}
				//end by chandrashekar on on 09-oct-14

				/*if(utranid.trim().length() <= 0)
		{
		utranid = generateTranId("w_wocampaign", loginSite, conn);
	    }

		System.out.println("GENERATED TRAN ID"+utranid);*/


				System.out.println("WORK ORDER NO "+workorder);
				sql = "select status from workorder where work_order = ?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1,workorder);
				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,workorder);
					count = pstmt.executeUpdate();
					if(count < 1)
					{
						System.out.println("updation of record is failed");
					}
					else
					{
					//	conn.commit();    // commited by cpatil on 22/05/14 for locGroup display
					}

					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,workorder);
					pstmt.setString(2,utranid);
					pstmt.setTimestamp(3,sysDate);
					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();     // commited by cpatil on 22/05/14 for locGroup display
						System.out.println(">>>>>process completed>>>>VV>>>>");
						errCode = "VTWOCPRCGN"; // generated successfully
						retString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
					}

					pstmt.close(); 
					pstmt = null;  
					System.out.println("NO. OF RECORDS INSERTED FOR CURRENT PERIODE.. "+count);




				}
			}
		}catch (Exception e) {
			isError = true;
			errCode = "VTPFTEXE"; // failed to execute
			retString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
			e.printStackTrace();
			throw new ITMException(e);
		} finally {
			try {
				if (conn != null) {
					if (isError) {
						conn.rollback();
					} else {
						conn.commit();
					}
					conn.close();
					conn = null;
				}
				if (pStmt != null) {
					pStmt.close();
					pStmt = null;
				}
			} catch (Exception e) {
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return retString;
	}



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