/********************************************************
	Title     : WorderTrackSchedule.java(M14BSUN003)
	Developer : Kunal Mandhre
	Purpose   : insert work order tracking  
********************************************************/
package ibase.webitm.ejb.sys;

import ibase.scheduler.utility.interfaces.Schedule;
import java.rmi.RemoteException;
import java.util.*;
import java.util.Date;
import java.sql.*;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.TransIDGenerator;
import ibase.utility.CommonConstants;
import ibase.system.config.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;



public class WorderTrackSchedule implements Schedule
{
	
	
	String chgUser = null;
	String chgTerm = null;
	static long count_records=0;
	boolean isError = false;

	public String schedulePriority( String wrkflwPriority )throws Exception
	{
		return "";
	}
	public String schedule(HashMap map)throws Exception
	{
		return "";
	}
	public String schedule(String scheduleParamXML)throws Exception
	{
		String siteCode = "";
		ibase.utility.UserInfoBean userInfo = null;
		try
		{
			System.out.println("************ ["+scheduleParamXML+"]");
			userInfo = new ibase.utility.UserInfoBean( scheduleParamXML );
			siteCode = userInfo.getSiteCode();
			chgUser = userInfo.getLoginCode();
			chgTerm = userInfo.getRemoteHost();
			
			System.out.println("Site code = "+siteCode);
			insertWorderTrack(siteCode,scheduleParamXML);
		}
		catch(Exception e)
		{
			throw new Exception(e);
		}
		return "";
	}
	public String insertWorderTrack(String siteCode , String scheduleParamXML) throws RemoteException,ITMException
	{
		
		System.out.println( " Scheduler activity starts ......");
		String retString = "",sql = "",sqlInsert = "";
		String actCode = "",roleCode = "",trackBasis = "", trackSet = ""  ;
		String workorder= "" ,refSer = "" ,trackReq = "" ,trackLevel = "",tranId = "",prvWorkorder = "";
		String contextCol1 = "",contextCol2 = "",contextCol3 = "" ,contextCol4 = "",contextCol5 = "";
		int expCompDays = 0 ,lineNo = 0;
		int seqNo = 0;
		boolean isPresentFlag = false;
		java.sql.Timestamp currDate = null;
		java.util.Date planDate = null ;
		Connection conn = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		PreparedStatement pstmt = null;
		PreparedStatement pstmtInst = null;
		ResultSet rs = null;
		ArrayList  trackList = new ArrayList();
		StringBuffer trackSql = new StringBuffer("");
		try
		{
			
			ConnDriver connDriver = new ConnDriver(); 
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			//SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
			//Date date = sdf.parse(currDate.toString());
			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());
			String currDateStr = sdfAppl.format(currDate);
			tranId = generateTranId( "w_tran_trk_status", siteCode, currDateStr, conn );
			System.out.println("tranId =="+tranId);
			String trackBasis1 = "" ,trackBasis2 = "",trackBasis3 = "",trackBasis4 = "" ,trackBasis5 = "" , trackBasis6 = ""; 
			sql = " SELECT TRACK_BASIS_COL_1 ,TRACK_BASIS_COL_2,TRACK_BASIS_COL_3,TRACK_BASIS_COL_4,TRACK_BASIS_COL_5,TRACK_BASIS_COL_6 FROM OBJ_TRACK_REQ REQ WHERE  REQ.REF_SER= 'W-ORD'   ";
			pstmt = conn.prepareStatement( sql );
			rs = pstmt.executeQuery();	
			if( rs.next() )
			{
				trackBasis1 = rs.getString(1);
				trackBasis2= rs.getString(2);
				trackBasis3= rs.getString(3);
				trackBasis4= rs.getString(4);
				trackBasis5 = rs.getString(5);
				trackBasis6= rs.getString(6);
				System.out.println("t1="+trackBasis1+"  t2="+trackBasis2+"  t3="+trackBasis3+"  t4="+trackBasis4+"  t5="+trackBasis5+"  t6="+trackBasis6);
				
				if(trackBasis1 != null && trackBasis1.trim().length() > 0)
				{
					trackList.add(trackBasis1);
				}
				if(trackBasis2 != null && trackBasis2.trim().length() > 0)
				{
					trackList.add(trackBasis2);
				}
				if(trackBasis3 != null && trackBasis3.trim().length() > 0)
				{
					trackList.add(trackBasis3);
				}
				if(trackBasis4 != null && trackBasis4.trim().length() > 0)
				{
					trackList.add(trackBasis4);
				}
				if(trackBasis5 != null && trackBasis5.trim().length() > 0)
				{
					trackList.add(trackBasis5);
				}
				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			System.out.println("trackList="+trackList.toString());
			if(trackList.size() == 0)
			{
				trackSql.append("''");
			}
			else
			{
				for ( int i = 0; i < trackList.size(); i++ )
				{
					if(i == (trackList.size() -1))
					{
						trackSql.append("TRIM (WO.").append(trackList.get(i).toString()).append(") ");
					}
					else
					{
						trackSql.append("TRIM (WO.").append(trackList.get(i).toString()).append(") || '-' ||");
					}
				}
			}
			System.out.println("trackSql =="+trackSql.toString());
			
			
			
			sql = "select  wo.work_order,wo.item_code ,wo.site_code,basis,wo.order_type,wo.route_code ,ref_ser , track_req ,track_set,act_code1 ,seq_no,track_level1,role_code1  ,"
			      +"context_col_1,context_col_2,context_col_3,context_col_4 ,context_col_5 "
				  +" from  (  select reqd.ref_ser as ref_ser ,req.track_req as track_req ,req.context_col_1,req.context_col_2 ,req.context_col_3 ,req.context_col_4 ,req.context_col_5 "
			      +", reqd.track_basis_value  as basis ,reqd.track_set as track_set , tsetd.act_code  as act_code1  , tsetd.seq_no as seq_no, act.track_level as track_level1,act.role_code as role_code1 "
				  +" from obj_track_req req,obj_track_req_det  reqd  , tran_track_set  tset ,tran_track_set_det tsetd ,tran_track_acts act  where  req.ref_ser = reqd.ref_ser  and req.track_req = reqd.track_req "
			      +" and  reqd.track_set =  tset.track_set  and  tset.track_set = tsetd.track_set  and tsetd.act_code = act.act_code  and  req.ref_ser= 'W-ORD'  ) "
				  +",  workorder wo  where wo.ord_date  >= sysdate  "
			      +" and trim(basis) = "+trackSql
			      +" group by wo.work_order,wo.item_code,wo.site_code,basis,wo.order_type,wo.route_code ,ref_ser , track_req ,track_set ,act_code1 ,seq_no, track_level1,role_code1 , "
				  +" context_col_1,context_col_2,context_col_3,context_col_4 ,context_col_5 order by wo.work_order ";
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			sqlInsert = " insert into wo_track_status (tran_id,line_no,ref_ser,ref_id,ref_line_no,track_req,context_value,track_set,context_1,context_2,context_3,context_4, "
			         +" context_5,seq_no,act_code,role_code,track_level,days_required,plan_end_date,remarks,chg_user,chg_date,chg_term) "
					 +" values (?,?,?,?,?,?,?,?,?,?,  ?,?,?,?,?,? ,?,?,?,? ,?,?,?) ";

			System.out.println("Comes for insert..."  );
			pstmtInst = conn.prepareStatement(sqlInsert);
			while(rs.next())
			{
				 
				workorder = rs.getString("WORK_ORDER");
				trackBasis = rs.getString("BASIS");
				refSer = rs.getString("REF_SER");
				trackReq = rs.getString("TRACK_REQ");
				trackSet = rs.getString("TRACK_SET");
				actCode = rs.getString("ACT_CODE1");
				seqNo = rs.getInt("SEQ_NO");
				trackLevel = rs.getString("TRACK_LEVEL1");
				roleCode = rs.getString("ROLE_CODE1");
				contextCol1 = rs.getString("context_col_1");
				contextCol2 = rs.getString("context_col_2");
				contextCol3 = rs.getString("context_col_3");
				contextCol4 = rs.getString("context_col_4");
				contextCol5 = rs.getString("context_col_5");
				
			     System.out.println("workorder=="+workorder+"  @ prvWorkorder="+prvWorkorder);
			     
				isPresentFlag = isPresentInStatusTable(workorder,refSer,conn);
				if(!isPresentFlag)
				{
					if(prvWorkorder != null && prvWorkorder.trim().length() > 0 &&  !prvWorkorder.equals(workorder) )
					{
						tranId = generateTranId( "w_tran_trk_status", siteCode, currDateStr, conn );
						System.out.println("tranId NEW =="+tranId);
						lineNo = 0;
					}
					lineNo++;
									
					pstmtInst.setString(1,tranId);//tran_id
					pstmtInst.setInt(2,lineNo);//line_no
					pstmtInst.setString(3,refSer); // Ref series
					pstmtInst.setString(4, workorder ); // Ref-Id
					pstmtInst.setInt(5,1); //REF_LINE_No 
					pstmtInst.setString(6,trackReq); // Track Req
					pstmtInst.setString(7,trackBasis); // context_value
					pstmtInst.setString(8,trackSet);  //track set
					pstmtInst.setString(9,getContextValue(workorder, contextCol1, conn));//Context1
					pstmtInst.setString(10,getContextValue(workorder, contextCol2, conn));//Context2
					pstmtInst.setString(11,getContextValue(workorder, contextCol3, conn));//Context3
					pstmtInst.setString(12,getContextValue(workorder, contextCol4, conn));//Context4
					pstmtInst.setString(13,getContextValue(workorder, contextCol5, conn));//Context5
					pstmtInst.setInt(14,seqNo); //seq_no
					pstmtInst.setString(15,actCode);//act_code
					pstmtInst.setString(16,roleCode);//role_code
					pstmtInst.setString(17,trackLevel);//track level
					expCompDays =  getAcitivityLeadTime( trackSet,  actCode,  conn );
					pstmtInst.setInt(18,expCompDays);//Days Required
					planDate = getDates(new Date(),expCompDays);
					pstmtInst.setTimestamp(19,new java.sql.Timestamp(planDate.getTime()));//plan_end_date
					pstmtInst.setString(20,"");//REMARKS
					pstmtInst.setString(21,chgUser);//CHG_USER
					pstmtInst.setTimestamp(22,currDate);//chg date
					pstmtInst.setString(23,chgTerm);//CHG_TERM
					pstmtInst.addBatch();
					pstmtInst.clearParameters();
					prvWorkorder = workorder ;
				}
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			System.out.println("sql insert "+pstmtInst.executeBatch().length);

			 if(pstmtInst != null) 
			 {
				 pstmtInst.close();
				 pstmtInst = null;

			 }
			conn.commit();
		}
		catch(SQLException se)
		{
			isError = true;
			System.out.println("SQLException : WorderTrackSchedule : " +se.getMessage());
			try
			{
				conn.rollback();
			}
			catch(Exception se1){}
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			isError = true;
			System.out.println("Exception :WorderTrackSchedule :"+e.getMessage());
			try
			{
				conn.rollback();
			}
			catch(Exception e1){}
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				
				if ( conn != null && !conn.isClosed() )
				{
					conn.close();
					conn = null;
				}
			}catch(Exception se){}
		}
		System.out.println("retString........:: " + retString);
		return retString;  	
	}
	
	private String generateTranId( String windowName, String siteCode, String tranDateStr, Connection conn )throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		String xmlValues = "";
	
		try
		{
			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
			//System.out.println("selSql :"+selSql);
			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;
			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>" + tranDateStr + "</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);
		}
		return tranId;
	}//generateTranTd()
	
	
	private boolean isPresentInStatusTable( String tranId,String refSer, Connection conn )throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		boolean flag = false;
		int cnt = 0;
		try
		{
			selSql = " SELECT COUNT(*) AS COUNT FROM wo_track_status WHERE REF_ID =  ? and REF_SER =  ?   ";
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, tranId );
			pstmt.setString( 2, refSer );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				cnt = rs.getInt("COUNT");
				if(cnt>0)
				{
					flag = true;
				}
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			System.out.println("isPresentInStatusTable().flag value :"+flag);
		
		}
		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);
		}
		return flag;
	}//isPresentInStatusTable()
	
	public int getAcitivityLeadTime( String trackSet, String actCode, Connection conn )throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		int leadTime = 0;

		try
		{
			selSql = "  SELECT EXP_COMP_DAYS FROM TRAN_TRACK_SET_DET WHERE TRACK_SET = ? AND ACT_CODE = ? ";
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, trackSet );
			pstmt.setString( 2, actCode );
			rs = pstmt.executeQuery();
			
			if (rs.next())
			{
				leadTime = rs.getInt("EXP_COMP_DAYS");
			}
			
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			
			System.out.println("getAcitivityLeadTime().leadTime value :"+leadTime);
		
		}
		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 leadTime;
	}//getAcitivityLeadTime()
	private String getContextValue( String Workorder,String contextCol, Connection conn )throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		String contextValue = "";
		try
		{
			if(contextCol == null || contextCol.trim().length() == 0)
			{
				return "";
			}
			else
			{
				sql = " select "+contextCol+" from workorder where work_order = ?  ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString( 1, Workorder );
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					contextValue = rs.getString(1);
				}
				rs.close();rs = null;
				pstmt.close();pstmt = null;
				
			}
		
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +sql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return contextValue;
	}
	
	public static java.util.Date getDates( java.util.Date dt, int addDay)
	{
		 Calendar cal = Calendar.getInstance();
	        cal.setTime(dt);
	        cal.add(Calendar.DATE, addDay);
	        dt = cal.getTime();
	        
	        return dt;
	}
	 public static String getDaysAddDate(String dateStr, int validity ) { 
	 		
		  String date="";
		try
		{
			SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy");
			Date upto_date_temp = (java.util.Date)formatter1.parse(dateStr.trim());
      Date  upto_date = new java.sql.Date(upto_date_temp.getTime());
      Calendar nowCal = Calendar.getInstance();

              nowCal=Calendar.getInstance();
              nowCal.setTime(upto_date);

         int day = nowCal.get(Calendar.DAY_OF_MONTH);
         int month = nowCal.get(Calendar.MONTH);
         int year = nowCal.get(Calendar.YEAR);

       Calendar cal = Calendar.getInstance();
       cal.clear();
       cal.set(Calendar.YEAR, year+(Integer.valueOf(validity)/12));
       cal.set(Calendar.MONTH, month+(Integer.valueOf(validity)%12));
       cal.set(Calendar.DAY_OF_MONTH, day+validity);
       Date dueDate = new Date(cal.getTimeInMillis());

     //  System.out.println("dueDate =" +dueDate);
       DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
            date =formatter.format(dueDate);
           System.out.println("== DATE==="+date);
        

		}
		catch(Exception E)
		{
			System.out.println("Exception = "+E);
			E.printStackTrace();
		}


		return date;
  }
}