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 campgnNo=null;
		//String workOrder1=null;
		String retString = "" ;
		String errString = "";
		String errCode = "";
		String returnString = "";
		String lsErrcode = "";
		String chgUser = "";
		String userId = null;
		String chgTerm = "";
		ArrayList workOrderList=new ArrayList();//added by priyanka
		boolean result=false;
		boolean updateFlag=false;
	
		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
		{
		
			System.out.println("Enter in process ======================");
			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 );
			campgnNo =	genericUtility.getColumnValue( "campgn_no", headerDom );
			System.out.println("workOrder========"+workOrder);
			System.out.println("campgnNo========"+campgnNo);
		
			// added by priyanka as per manoj sharma instruction on 29/08/14
			//validation : user is allow to enter either workorder no or campaign no 
			//on process of campaign no,system release all the work order associated with that  Campaign No
			 if((workOrder==null || workOrder.trim().length()==0) && (campgnNo==null|| campgnNo.trim().length()==0))
			 {
				errString = itmDBAccessEJB.getErrorString( "", "VTCMPWORD", userId );
				System.out.println("@@@@@@@@@ errString@@@@@@@@" +errString);
				return errString;				 
			 }
			
			 if((workOrder!=null && workOrder.trim().length()>0 && campgnNo==null||campgnNo.trim().length()==0)||(campgnNo!=null && campgnNo.trim().length()>0 && workOrder==null||workOrder.trim().length()==0))
			 {
				System.out.println("Its Working");				
			 }
			 
			 else if(workOrder!=null && workOrder.trim().length()>0 && campgnNo!=null && campgnNo.trim().length()>0)
			 {
				errString = itmDBAccessEJB.getErrorString( "", "VTWORDCPNO", userId );
				System.out.println("@@@@@@@@@ errString@@@@@@@@" +errString);
				return errString;
			 }
			 
			 if(workOrder!=null && workOrder.trim().length()>0)
			 {
				 if (!(isExist(conn, "workorder", "work_order", workOrder)))
					{
					    errString = itmDBAccessEJB.getErrorString( "", "VTWORDNO", userId );
						System.out.println("@@@@@@@@@ errString@@@@@@@@" +errString);
						return errString;
					}				 
			 }
			 
			if(campgnNo!=null && campgnNo.trim().length()>0)
			 {
				 if (!(isExist(conn, "workorder", "campgn_no", campgnNo)))
					{
					    errString = itmDBAccessEJB.getErrorString( "", "VTCMPGNO", userId );
						System.out.println("@@@@@@@@@ errString@@@@@@@@" +errString);
						return errString;
					}				 
			 }
			
			if(campgnNo!=null && campgnNo.trim().length()>0)
			{
				System.out.println("IF==========");
				sql="select work_order from workorder where campgn_no= ? and status= 'F'";
				pstmt = conn.prepareStatement( sql );
				pstmt.setString(1,campgnNo);				
				rs=pstmt.executeQuery();
				while(rs.next())
				{
					workOrder=rs.getString("work_order");
					System.out.println("Getting workorder against Campaign No==========="+workOrder);					
					workOrderList.add(workOrder);
					System.out.println("workOrderList Size============="+workOrderList.size());
					System.out.println("workOrderList============="+workOrderList);		
					result=true;
					System.out.println("result==========="+result);
					
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				
			}
			else
			{
				System.out.println("else==========");
				
				workOrder=genericUtility.getColumnValue( "work_order", headerDom );
				workOrderList.add(workOrder);
				System.out.println("Getting workorder==========="+workOrder);
				result=false;
				System.out.println("result==========="+result);
				
			}
			
			for(int i =0;i<workOrderList.size();i++)
			{
				workOrder=(String) workOrderList.get(i);
				System.out.println("Total WorkOrder==================="+workOrder);
			
			
				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;
				// 11/12/14 manoharan
				orderType = orderType.trim();
				// end 11/12/14 manoharan
				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;				 
						System.out.println("before continue===========");
						continue;
					
					}
					//***************** 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;
					System.out.println("11/12/14 orderType [" + orderType + "] mfgDateOn [" + mfgDateOn + "]");

					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();
					if(count>0)
					{
						System.out.println(">>>>>>>>>>>>>>>>Count from update:"+count);
						updateFlag=true;
					}
					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 if(errString!=null && errString.trim().length()>0 && result==true && updateFlag==true)
				{
					System.out.println(">>>>>>>>>>>>>>>>>>>>If result is true ,commit");
					conn.commit();
				}
				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
	
	

	private boolean isExist(Connection conn, String tableName, String columnName, String value) throws ITMException, RemoteException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		boolean status = false;
		try
		{
			sql = "SELECT count(*) from " + tableName + " where " + columnName + "  = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, value);
			rs = pstmt.executeQuery();

			if (rs.next())
			{
				if (rs.getBoolean(1))
				{
					status = true;
				}
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
		} catch (Exception e)
		{
			System.out.println("Exception in isExist ");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning String from isExist ");
		return status;
	}

 } // END OF EJB 