package ibase.webitm.ejb.mfg;

import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ValidatorEJB;
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.sql.Statement;
import java.text.SimpleDateFormat;

import javax.ejb.Stateless;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
// added for ejb3

@Stateless // added for ejb3

public class WoPostSave extends ValidatorEJB implements  WoPostSaveLocal,  WoPostSaveRemote //SessionBean
{
	/*public void ejbCreate() throws RemoteException, CreateException
	{
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate()
	{
	}
	public void ejbPassivate()
	{
	}*/
	public String postSaveRec() throws RemoteException, ITMException
	{
		return "";
	}
	public String postSaveRec(String xmlString1, String domId, String objContext, String editFlag, String xtraParams, Connection conn) throws RemoteException, ITMException
	{
		System.out.println("WoPostSaveEJB Called............!");
		System.out.println("xml............!"+xmlString1);
		Document dom = null;
		String saveRec = "";
		try
		{
			if (xmlString1 != null && xmlString1.trim().length() > 0)
			{
				dom = GenericUtility.getInstance().parseString(xmlString1);
				saveRec = executepostSaveRec(dom,domId,objContext,editFlag,xtraParams,conn);
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception :WoPostSaveEJB :postSaveRec(): " + e.getMessage()+ ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return saveRec;
	}
	private String executepostSaveRec(Document dom, String domID, String ObjContext, String editFlag, String xtraParams, Connection conn) throws RemoteException, ITMException
	{
		String ordertype = "", workorder = "", itemcode = "", sitecode = "" ,lotno = "";
		String ordertypeT = "",genlotnoT = "", sql = "",sql1 = "",keyT = "",wostatus="",campno="";
		NodeList hdrDom = null;
		Node currDetail = null;
		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
	    String ls_fglotnoon = "",ls_setfg = "";
		String ls_win = "fg_lotno";
		String tranIdG = "",sqlUpd = "";
		String errString = "";
		String errCode = "";
		String userId = ""; 
		boolean lb_nositeitem = false;
		int  updCnt = 0,count=0;

	    GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			System.out.println("userId :: "+userId);
			currDetail 	= getCurrentHeaderFromDom(dom,domID);
			if (currDetail != null)
			{
				
				ordertype	= genericUtility.getColumnValueFromNode("order_type",currDetail);
				workorder	= genericUtility.getColumnValueFromNode("work_order",currDetail);
				itemcode	= genericUtility.getColumnValueFromNode("item_code",currDetail);
				sitecode	= genericUtility.getColumnValueFromNode("site_code",currDetail);
				lotno	    = genericUtility.getColumnValueFromNode("lot_no",currDetail);
				ordertype   = ordertype.trim();
				sitecode    = sitecode.trim();
				System.out.println("ordertype :: "+ordertype);
				System.out.println("workorder ::"+workorder);
				System.out.println("itemcode ::"+itemcode);
				System.out.println("sitecode ::"+sitecode);
				System.out.println("lotno ::"+lotno);
				stmt = conn.createStatement();  
				try
		         {
					//ADDED BY DEEPAK SAWANT (02/06/14)
					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 chkk >>>>>>>>>>>>>>>>>>."+wostatus);
					
					sql = "select campgn_no from workorder where work_order = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,workorder);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						campno = rs.getString(1) == null ? "":rs.getString(1);
					}
					pstmt.close();
					rs.close();
					pstmt = null;
					rs = null;
					
					if(wostatus.equalsIgnoreCase("P") || wostatus.equalsIgnoreCase("M") || wostatus.equalsIgnoreCase("U"))
					{
						System.out.println("ENTER IN STATUS CHECK >>>>>");
						sql = "UPDATE WORDER_CAMPAIGN SET CAMPGN_NO = ? WHERE WORK_ORDER = ?";
		                pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,campno);
						pstmt.setString(2,workorder);
						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);
					
					}
					
					//ADDED BY DEEPAK SAWANT (02/06/14)
					
					
	                    sql1 = "SELECT CASE WHEN generate_lot_no IS NULL THEN '0' ELSE generate_lot_no END  "
						+" from siteitem where site_code = '"+sitecode+"' and item_code = '"+itemcode+"' ";
						System.out.println("sql1 ....:"+sql1);
						rs = stmt.executeQuery(sql1);
						if(rs.next())
						{
							genlotnoT = rs.getString(1);
							System.out.println("genlotnoT..."+genlotnoT);
							
						}
						else
						{
							lb_nositeitem = true;
							System.out.println("lb_nositeitem...."+lb_nositeitem);
                            System.out.println("No record found for generate_lot_no");
						}
						rs.close();
			            rs = null;
						
				 }
				catch(Exception e)
					   {
						   System.out.println("Exception in ..genlotnoT....");
						   e.printStackTrace();
						
					   }

				 ls_setfg = getDisparams("999999","FG_LOTNO",conn);
				 ls_setfg = ls_setfg.trim();
				 System.out.println("ls_setfg..."+ls_setfg);
				 ls_fglotnoon = getmfgparams("999999","FG_LOT_NO_ON",conn);
				 System.out.println("ls_fglotnoon......."+ls_fglotnoon);
				 System.out.println("count=>"+("RFP".indexOf(ordertype)));
				 if (ls_fglotnoon.equalsIgnoreCase("R"))
						{
							//Start - Commented by jaimin on 06/01/2009 (MF89DAM004)
					        /*System.out.println("ls_fglotnoon=R "); 
							errCode = "NOLOTNO";
							errString = getErrorString("",errCode,userId);*/
							//End - Commented by jaimin on 06/01/2009 (MF89DAM004)
								
						}	
				 else 
						{
							if("RFP".indexOf(ordertype)>-1)
							{
								    if(genlotnoT.equalsIgnoreCase("1") || (lb_nositeitem && ls_setfg.trim().indexOf(sitecode.trim()) > -1 ))
									{
										if(lotno == null || lotno.trim().length() == 0)
										{	
											tranIdG=generateTranId(ls_win,getCurrdateAppFormat(),lotno,sitecode,itemcode,conn,currDetail);
											System.out.println("tranIdG......."+tranIdG);
											if(tranIdG.equalsIgnoreCase("ERROR") || tranIdG.trim().length() == 0)
											   {
													errCode = "NOLOTNO";
							                        errString = getErrorString("",errCode,userId);
											   }
											System.out.println("workorder ....:"+workorder);
											sqlUpd = "update workorder set lot_no ='"+tranIdG+"' where work_order = '"+workorder+"'";
											updCnt = stmt.executeUpdate(sqlUpd);
											System.out.println("sqlUpd ....:"+sqlUpd);
											if (updCnt == 1)
												{
													System.out.println("Update Into workorder Successfully....:: " + updCnt);
													conn.commit();
												}
											else if (updCnt == 0)
												{
												    System.out.println("No Updation Occur Into workorder ....:: " + updCnt);
													conn.rollback();
													errCode = "NOWORKODRNO";
							                        errString = getErrorString("",errCode,userId);

												}
											
											
										}	
									}
						  
							}
							
						}

				stmt.close();
				stmt = null;
				
			}  	
				
		}
		catch(Exception e)
		{
			System.out.println("The Exception occurs in WoPostSaveEJB....... :"+e);	
			try
			{
				conn.rollback();
			}
			catch (Exception e1){}
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null)
				{
				    rs.close();
					rs=null;
				}
				if(stmt != null)
				{
					stmt.close();
					stmt = null;
				}
					
			} 
			catch(Exception e)
			{
				System.err.println("Exception :WoPostSaveEJB......... :\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}

		System.out.println("errString..."+errString);
		return errString;
	}
	
	//Culculate the trantype value from disparm	
    public String getDisparams(String prdCode,String varName,Connection conn)
	{
		String    retVal = "";
		String    sql    = "";
		ResultSet rs     = null;
		Statement stmt   = null;
		try
		{
			System.out.println("In DISPAREM ");
			stmt = conn.createStatement();
			sql = "SELECT VAR_VALUE FROM DISPARM WHERE PRD_CODE ='"+prdCode+"' AND VAR_NAME ='"+varName+"'";
			rs = stmt.executeQuery(sql);
			if(!rs.next() )
			{
				System.out.println("No record found for PRD_CODE ='"+prdCode+"' AND VAR_NAME ='"+varName+"'");
				retVal = "NULLFOUND";
			}
			else
			{
				retVal = rs.getString(1);
				System.out.println("In DISPAREM retVal.... "+retVal);
			}
			rs.close();
			rs = null;
			stmt.close();
		    stmt = null;
		}
		catch(Exception e)
		{
			System.out.println("Exception in ..getDIS....");
			e.printStackTrace();
		}
		return(retVal);
	}//End of disparm

	//Culculate the trantype value from mfgparm	
    private String getmfgparams(String prdCode,String varName,Connection conn)
	{
			String  retVal   = "";
			String  sql      = "";
			ResultSet rs     = null;
			Statement stmt   = null;
			try
			{
				System.out.println("In MFGPAREM ");
				stmt = conn.createStatement();
				sql  = "SELECT VAR_VALUE FROM  MFGPARM WHERE PRD_CODE ='"+prdCode+"' AND VAR_NAME ='"+varName+"'";
				rs   = stmt.executeQuery(sql);
				if(!rs.next() )
				{
					System.out.println("No record found for PRD_CODE ='"+prdCode+"' AND VAR_NAME ='"+varName+"'");
						
				}
				else
				{
					retVal = rs.getString(1);
					System.out.println("retVal ..in mfg parm...."+retVal);
				}
				rs.close();
			    rs = null;
				stmt.close();
				stmt = null;
				}
			catch(Exception e)
			{
				System.out.println("Exception in ..getMFG....");
				e.printStackTrace();
			}
			return(retVal);
	}//End of MFGparm


    private String generateTranId(String windowName,String orderDate,String lot_no, String siteCode,String itemcode, Connection conn, Node currDetail)
	{
		
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "",sql1="";
		String tranId = "";
		String newKeystring = "";
		String srType = "RS";
		String item_ser="";
		System.out.println("windowName...... :"+windowName);
		System.out.println("itemcode...... :"+itemcode);	
		GenericUtility genericUtility = GenericUtility.getInstance();		
		 try
	     {
	    	sql  = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW= '"+windowName+"'";
			stmt = conn.createStatement();
			rs   = stmt.executeQuery(sql);
			System.out.println("sql...... :"+sql);
			System.out.println("keyString :"+rs.toString());
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			if (rs.next())
			{
				keyString = rs.getString(1);
				keyCol = rs.getString(2);
				tranSer1 = rs.getString(3);				
			}
			rs.close();
			rs = null;
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer1 :"+tranSer1);
			try
		         {
	                    sql1 = "SELECT item_ser from item where item_code = '"+itemcode+"'"	;
						System.out.println("sql1 ....:"+sql1);
						rs = stmt.executeQuery(sql1);
						if(rs.next())
						{
							item_ser = rs.getString(1);
							System.out.println("item_ser..."+item_ser);
							
						}
						else
						{
							System.out.println("No record found ");
						}
						rs.close();
			            rs = null;
						
				 }
			
			catch(Exception e)
				 {
						   System.out.println("Exception in ..item_ser....");
						   e.printStackTrace();
						
			     }
		    stmt.close();
		    stmt = null;
            String xmlValues = "";
			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>"+ orderDate + "</tran_date>";
			xmlValues = xmlValues + "<item_ser>"+ item_ser + "</item_ser>";
			xmlValues = xmlValues + "<item_code>"+itemcode + "</item_code>";
			xmlValues = xmlValues + "<lot_no>" + lot_no + "</lot_no>";
			String tempStr = genericUtility.getColumnValueFromNode("order_type",currDetail);
			if (tempStr == null)
			{
				tempStr = "";
			}
			xmlValues = xmlValues + "<order_type>" + tempStr + "</order_type>";
			tempStr = genericUtility.getColumnValueFromNode("lot_no__issue",currDetail);
			if (tempStr == null)
			{
				tempStr = "";
			}
			xmlValues = xmlValues + "<lot_no__issue>" + tempStr + "</lot_no__issue>";
			tempStr = genericUtility.getColumnValueFromNode("status",currDetail);
			if (tempStr == null)
			{
				tempStr = "";
			}
			xmlValues = xmlValues + "<status>" + tempStr + "</status>";
			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);
			System.out.println("tranId :"+tranId);
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +sql+ ex.getMessage() + ":");
			ex.printStackTrace();
			
		}
		catch (Exception e)
		{
		    System.out.println("Exception ::" + e.getMessage() + ":");
		    e.printStackTrace();
		}
		return tranId;
	}//generateTranTd()
    private Node getCurrentHeaderFromDom(Document dom,String domId)
	{
		NodeList detailList = null;
		Node currDetail = null,reqDetail = null;
		String currDomId = "";
		int	detailListLength = 0;

		detailList = dom.getElementsByTagName("Detail1");
		detailListLength = detailList.getLength();
		for (int ctr = 0;ctr < detailListLength;ctr++)
		{
			currDetail = detailList.item(ctr);
			currDomId = currDetail.getAttributes().getNamedItem("domID").getNodeValue();
			if (currDomId.equals(domId))
			{
				reqDetail = currDetail;
				break;
			}			
		}
		return reqDetail;
	}
	//THIS FUNCTION RETURN DATE IN APPLICATION FORMAT	
    private String getCurrdateAppFormat()
		{
			String currAppdate = "";
			java.sql.Timestamp currDate = null;
			try
			{
				    GenericUtility genericUtility = GenericUtility.getInstance();
					Object date = null;
					currDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
					System.out.println(genericUtility.getDBDateFormat());
					SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
					date = sdf.parse(currDate.toString());
					currDate =	java.sql.Timestamp.valueOf(sdf.format(date).toString() + " 00:00:00.0");
					currAppdate = new SimpleDateFormat(genericUtility.getApplDateFormat()).format(currDate).toString();
			}
			catch(Exception e)
			{
				    System.out.println("Exception in :::currdate"+e.getMessage());
			}
			return (currAppdate);
		}
	
}
