package ibase.webitm.ejb.mfg;

import java.sql.*;
import java.util.*;
import org.w3c.dom.*;
import java.rmi.RemoteException;
import java.text.SimpleDateFormat;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.sys.*;
import ibase.webitm.ejb.dis.*;
import ibase.system.config.*;
import ibase.utility.CommonConstants;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.GenericUtility;


import javax.ejb.Stateless; 


@Stateless
public class WorkorderRelease extends ProcessEJB implements WorkorderReleaseLocal, WorkorderReleaseRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	CommonConstants commonConstants = new CommonConstants();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	UtilMethods utilMethods = UtilMethods.getInstance();
	DistCommon distCommon = new DistCommon();
	MfgCommon mfgCommon = new MfgCommon();
	String processStr = null;

	
	

	public String process() throws RemoteException,ITMException
	{
		System.out.println("Called Empty process");
		return "";
	}
	//process()
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;
		System.out.println("xmlString2-->"+ xmlString2);
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			System.out.println("xmlString   ["+xmlString+"]");
			System.out.println("xmlString2   ["+xmlString2+"]");
			System.out.println("windowName   ["+windowName+"]");
			System.out.println("xtraParams   ["+xtraParams+"]");
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2);
			}
		    retStr = process(headerDom, detailDom, windowName, xtraParams);
		}
		
		catch (Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return retStr;
	}
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmtInsert = null;
		ResultSet rs = null;
		String workOrder = null;
		String retString = "" ;
		String errString = "";
		String errCode = "";
		String returnString = "";
		String lsErrcode = "";
		String chgUser = "";
		String userId = null;
		String chgTerm = "";
	
		String sql = null, mfgDateOn = "";

		String siteCode = "", bomCode = "", status = "", orderType = "", lotNoIssue = "", itemCode = "", saleOrder = "", routeCode = "",templateName = "";
		String xmlString = "";
		double quantity = 0;
		int count = 0,mfgLeadTime = 0;
		java.sql.Timestamp dueDate = null, mfgDate = null, expDate = null, today = null, mfgDateWO = null, expDateWO = null, completionDate = null;
		//oracle.jdbc.OracleConnection oracleConnection = null; change done by kunal on 12/Sep/13
		
		try
		{
		
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB( "DriverITM" );
			conn.setAutoCommit( false );
			connDriver = null;
			//oracleConnection = new ConnDriver().getOracleConnection("DriverITM");
			chgUser = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" ); 
			chgTerm = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "termId" );
			userId = chgUser;

			workOrder =	genericUtility.getColumnValue( "work_order", headerDom );

		
			if(workOrder == null || workOrder.trim().length()== 0)
			{
				errString = itmDBAccessEJB.getErrorString( "", "VTWORDER1", userId );
				System.out.println("@@@@@@@@@ errString@@@@@@@@" +errString);
				return errString;
			}
			if ((CommonConstants.DB_NAME).equalsIgnoreCase("db2") || (CommonConstants.DB_NAME).equalsIgnoreCase("mysql") )
			{
				sql = "SELECT SITE_CODE, BOM_CODE, STATUS, ORDER_TYPE, "
					+ " LOT_NO__ISSUE, QUANTITY, ITEM_CODE, SALE_ORDER, "
					+ " DUE_DATE, MFG_DATE__START, EXP_DATE, ROUTE_CODE "
					+ " FROM WORKORDER WHERE WORK_ORDER = ?  FOR UPDATE ";
			}
			else if ((CommonConstants.DB_NAME).equalsIgnoreCase("mssql"))
			{
				sql = "SELECT SITE_CODE, BOM_CODE, STATUS, ORDER_TYPE, "
					+ " LOT_NO__ISSUE, QUANTITY, ITEM_CODE, SALE_ORDER, "
					+ " DUE_DATE, MFG_DATE__START, EXP_DATE, ROUTE_CODE "
					+ " FROM WORKORDER (UPDLOCK) WHERE WORK_ORDER = ?  FOR UPDATE ";
			}
			else
			{
				sql = "SELECT SITE_CODE, BOM_CODE, STATUS, ORDER_TYPE, "
					+ " LOT_NO__ISSUE, QUANTITY, ITEM_CODE, SALE_ORDER, "
					+ " DUE_DATE, MFG_DATE__START, EXP_DATE, ROUTE_CODE "
					+ " FROM WORKORDER WHERE WORK_ORDER = ?  FOR UPDATE NOWAIT ";
			}
			pstmt = conn.prepareStatement( sql );
			pstmt.setString(1,workOrder);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				siteCode = rs.getString("SITE_CODE");
				bomCode = rs.getString("BOM_CODE");
				status = rs.getString("STATUS");
				orderType = rs.getString("ORDER_TYPE");
				lotNoIssue = rs.getString("LOT_NO__ISSUE");
				quantity = rs.getDouble("QUANTITY");
				itemCode = rs.getString("ITEM_CODE");
				saleOrder = rs.getString("SALE_ORDER");
				dueDate = rs.getTimestamp("DUE_DATE");
				mfgDateWO = rs.getTimestamp("MFG_DATE__START");
				expDateWO = rs.getTimestamp("EXP_DATE");
				routeCode = rs.getString("ROUTE_CODE");
			}
			else
			{
				errString = itmDBAccessEJB.getErrorString("","VTLCKERR",userId);
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null ;
				return errString;
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			if ("P".equalsIgnoreCase(status) || "M".equalsIgnoreCase(status) || "U".equalsIgnoreCase(status))
			{
				errString = itmDBAccessEJB.getErrorString("","VTNOTFPLAN",userId);
				return errString;
			}
			else if ("X".equalsIgnoreCase(status))
			{
				errString = itmDBAccessEJB.getErrorString("","VTWOCANCEL",userId);
				return errString;
			}
			else if ("C".equalsIgnoreCase(status))
			{
				errString = itmDBAccessEJB.getErrorString("","VTWOCLOSE",userId);
				return errString;
			}
			else if ("R".equalsIgnoreCase(status))
			{
				errString = itmDBAccessEJB.getErrorString("","VTWOARES",userId);
				return errString;
			}
			else
			{
				sql = "SELECT COUNT(1) FROM WORKORDER_ENHANC "
					+ " WHERE WORK_ORDER = ? "
					+ " AND CASE WHEN CONFIRMED IS NULL THEN 'N' ELSE CONFIRMED END = 'N' ";
				pstmt = conn.prepareStatement( sql );
				pstmt.setString(1,workOrder);
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					count = rs.getInt(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				if (count > 0 )
				{
					errString = itmDBAccessEJB.getErrorString("","VTWOEUNCF",userId);
					return errString;
				}
				//***************** TO BE MIGRATED LATER ******************************************
				//Added by Sharon on 15-Feb-04 //This function will generate a PO if any item is subcontracted
				//ls_errcode = gbf_create_po(as_tran_id)
				//If Len(ls_errcode) > 0 Then goto errfound
				//***************** END TO BE MIGRATED LATER ******************************************

				today = new java.sql.Timestamp(System.currentTimeMillis()) ;
				
				SimpleDateFormat sdt = new SimpleDateFormat("yyyy-MM-dd");
				today = java.sql.Timestamp.valueOf(sdt.format(today) + " 00:00:00.000");
						
				// 28-12-04 manoharan
				if( saleOrder == null || saleOrder.trim().length() == 0)
				{
					sql = "SELECT MFG_LEAD_TIME FROM SITEITEM "
						+ " WHERE SITE_CODE = ? AND ITEM_CODE = ? "	;
					pstmt = conn.prepareStatement( sql );
					pstmt.setString(1,siteCode);
					pstmt.setString(2,itemCode);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						mfgLeadTime = rs.getInt(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					if (mfgLeadTime == 0)
					{
						sql = "SELECT MFG_LEAD FROM ITEM "
							+ " WHERE  ITEM_CODE = ? "	;
						pstmt = conn.prepareStatement( sql );
						pstmt.setString(1,itemCode);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							mfgLeadTime = rs.getInt(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
					}
					dueDate = utilMethods.RelativeDate(today, mfgLeadTime);
				}
				
				sql = "SELECT MFG_DATE_ON FROM ITEM WHERE ITEM_CODE = ? ";
				pstmt = conn.prepareStatement( sql );
				pstmt.setString(1,itemCode);
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					mfgDateOn = rs.getString("MFG_DATE_ON");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				
				if (!"F".equalsIgnoreCase(orderType) && "S".equalsIgnoreCase(mfgDateOn) )
				{
					ArrayList mfgExpList = mfgCommon.getMfgExpDate(workOrder, "W", completionDate,  conn);
					
					if (mfgExpList.size() > 0)
					{
						mfgDate = (Timestamp) mfgExpList.get(0);
						expDate = (Timestamp) mfgExpList.get(1);
						
						
						sql = "update workorder set mfg_date__start = ?, exp_date= ? where work_order = ? ";
						pstmt = conn.prepareStatement( sql );
						pstmt.setTimestamp(1,mfgDate);
						pstmt.setTimestamp(2,expDate);
						pstmt.setString(3,workOrder);
						pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
						
					}
				}
				else
				{
					if (mfgDateWO != null)
					{
						mfgDate = mfgDateWO;
					}
					else
					{
						mfgDate = null;
					}
					if (expDateWO != null)
					{
						expDate = expDateWO;
					}
					else
					{
						expDate = null;
					}
					sql = "update workorder set mfg_date__start = ?, exp_date= ? where work_order = ? ";
					pstmt = conn.prepareStatement( sql );
					pstmt.setTimestamp(1,mfgDate);
					pstmt.setTimestamp(2,expDate);
					pstmt.setString(3,workOrder);
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				}

				if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME ) )
				{
					sql = " INSERT INTO WORDER_RT_STEPS (" 
						+ " WORK_ORDER, OPERATION, SEQ_NO, SEQ_NO__LEVEL, "
						+ " DESCR, 	STATUS, SCH_DATE, START_DATE, END_DATE, TEMPLATE_NAME, DATA_XML ,WORK_CTR,MC_CODE )"
						+ " VALUES ( ?,?,?,?,?,?,?,?,?,?,? , ? , ?) " ; 
				}
				else
				{
					sql = " INSERT INTO WORDER_RT_STEPS (" 
						+ " WORK_ORDER, OPERATION, SEQ_NO, SEQ_NO__LEVEL, "
						+ " DESCR, 	STATUS, SCH_DATE, START_DATE, END_DATE, TEMPLATE_NAME, DATA_XML ,WORK_CTR,MC_CODE )"
						+ " VALUES ( ?,?,?,?,?,?,?,?,?,?,XmlType(?) , ? , ?) " ; 
				}
				pstmtInsert = conn.prepareStatement( sql );

				//change sql done  by kunal on 27/AUG/13 remove BMR_TEMPLATE table ,template name and xml get from procroute_oper_instr table 
				if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME ) )
				{
					sql = "SELECT P.OPERATION, P.SEQ_NO, P.SEQ_NO__LEVEL,P.DESCR, P.TEMPLATE_NAME, P.DATA_XML, P.WORK_CTR,P.MC_CODE "
						+ " FROM procroute_oper_instr p  "
						+ " WHERE P.ROUTE_CODE = ? ";
				}
				else
				{
					sql = "SELECT P.OPERATION, P.SEQ_NO, P.SEQ_NO__LEVEL,P.DESCR, P.TEMPLATE_NAME, P.DATA_XML.GETCLOBVAL()  ,P.WORK_CTR,P.MC_CODE "
						+ " FROM procroute_oper_instr p  "
						+ " WHERE P.ROUTE_CODE = ? ";
				}
				pstmt = conn.prepareStatement( sql );
				pstmt.setString(1,routeCode);
				rs = pstmt.executeQuery();
				while( rs.next() )
				{
					//CLOB xmlData = CLOB.createTemporary( conn.getMetaData().getConnection(), true, CLOB.DURATION_SESSION );
					
					xmlString = rs.getString(6);
					System.out.println("xmlString =="+xmlString);
					pstmtInsert.setString( 1, workOrder );
					pstmtInsert.setInt( 2, rs.getInt("OPERATION") );
					pstmtInsert.setInt( 3, rs.getInt("SEQ_NO") );
					pstmtInsert.setDouble( 4, rs.getDouble("SEQ_NO__LEVEL") );
					pstmtInsert.setString( 5, rs.getString("DESCR") );
					pstmtInsert.setString( 6, "P" );
					pstmtInsert.setTimestamp( 7, mfgDate); // rs.getTimestamp("SCH_DATE") );
					pstmtInsert.setTimestamp( 8, mfgDate); // rs.getTimestamp("START_DATE") );
					pstmtInsert.setTimestamp( 9, dueDate); //rs.getTimestamp("END_DATE") );
					pstmtInsert.setString( 10, rs.getString("TEMPLATE_NAME") );
					//pstmtInsert.setString( 11, rs.getString(6) );
					if(xmlString != null && xmlString.trim().length() > 0) //added by kunal for null value
					{
						//oracle.xdb.XMLType dataXml = XMLType.createXML(oracleConnection, xmlString);
						//pstmtInsert.setObject(11,dataXml) ;
						pstmtInsert.setString( 11, xmlString );
					}
					else
					{
						pstmtInsert.setString(11,"<Root></Root>") ;//change done by kunal on 12/Sep/13 if xml string blank insert "<Root></Root>"  
					}
					//ADDED BY KUNAL On 05/DEC/13 ADD work center and mc_ode 
					pstmtInsert.setString( 12, rs.getString("WORK_CTR") );
					pstmtInsert.setString( 13, rs.getString("MC_CODE") );
					
					pstmtInsert.addBatch();
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				pstmtInsert.executeBatch();
				pstmtInsert.close();
				pstmtInsert =null;
				

				sql = "update workorder  set status = 'R', status_date = ?, rel_date = ?, due_date	 = ? "
					+ " where work_order = ? " ;

				pstmtInsert = conn.prepareStatement( sql );
				
				pstmtInsert.setTimestamp(1,new Timestamp(System.currentTimeMillis()));
				pstmtInsert.setTimestamp(2,today);
				pstmtInsert.setTimestamp(3,dueDate);
				pstmtInsert.setString(4,workOrder);
				count = pstmtInsert.executeUpdate();
				pstmtInsert.close();
				pstmtInsert =null;
			}
			
		}    					
		catch(SQLException ex)
		{
			ex.printStackTrace();  
			System.out.println("Exception in Process1 :: " + ex.getMessage());
			errString = itmDBAccessEJB.getErrorString("","VTSQLEXC",userId);
			return errString;
		}
		catch(Exception e)
		{
			e.printStackTrace();  
			System.out.println("Exception in Process2 :: " + e.getMessage());
			errString = itmDBAccessEJB.getErrorString("","VTPRCERR",userId);
			return errString;
		}
		finally
		{
			try
			{
				if( pstmtInsert != null )
				{
					pstmtInsert.close();
					pstmtInsert =null;
				}
				/* if(oracleConnection != null)
				  {
                     oracleConnection.close();
                     oracleConnection = null;
                 }*/
				
				if( errString == null || errString.trim().length() == 0 )
				{
					conn.commit();
					System.out.println("Transaction Commit!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
					errString =	itmDBAccessEJB.getErrorString( "", "VTCOMPL", userId );
				}
				else
				{
					conn.rollback();
					System.out.println("Transaction RollBack!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
				} 
				
				if(conn != null)
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println("Error In closing connection::==> "+e);
				e.printStackTrace();
			}
		}
	
	    return errString;
    }//END OF PROCESS

 } // END OF EJB 