package ibase.dashboard.mfg.ejb;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import javax.ejb.Stateless;

/**
 * Session Bean implementation class ProductionVsBudgetReport
 */
@Stateless
public class ProductionVsBudgetReport extends ValidatorEJB implements ProductionVsBudgetReportRemote, ProductionVsBudgetReportLocal 
{
    /**
     * Default constructor. 
     */
	List<String> listDate =null;
    public ProductionVsBudgetReport() throws RemoteException, ITMException
    {
// TODO Auto-generated constructor stub
    	
    	String sql = "";
		ResultSet rs = null;		
		Connection conn = null;		
		PreparedStatement pstmt = null;
		ConnDriver connDriver = new ConnDriver();
	    listDate = new ArrayList<String>();
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			sql="select  to_char ( trunc(sysdate,'Month') + rownum - 1,'DD') as tran_date from item where rownum <= last_day (sysdate) - trunc(sysdate,'Month') + 1 order by 1 ";
			
			pstmt = conn.prepareStatement(sql); 			
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				listDate.add(rs.getString(1));
			}
			
		}
		 catch (Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			} finally
			{
				try
				{
					if (conn != null)
					{
						if (rs != null)
							rs.close();
						rs = null;
						if (pstmt != null)
							pstmt.close();
						pstmt = null;
						conn.close();
						conn = null;
					}
					conn = null;
				} catch (Exception d)
				{
					d.printStackTrace();
					throw new ITMException(d);
				}
			}
    }

    
    public String getProductionBudget(String frmDate,String toDate) throws RemoteException, ITMException
    {
		String sql = "";
		ResultSet rs = null,rs1=null;
		Connection conn = null;
		PreparedStatement pstmt = null,pstmt1;		
		ConnDriver connDriver = new ConnDriver();
		StringBuffer xmlData = null;
		String geoLocation="";
		String unit="";
		String previousUnit="";
		String geoLocationPrevious="";
		int count=0;
		int todaysDay=0;
		int lastDayOfMonth=0;
		double dailyBudgetQty=0;
		String geol_Descr="";
		String unit_Descr="";
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;						
			
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd-MMM-yy");
			Calendar calendar = Calendar.getInstance();
			calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
			calendar = Calendar.getInstance();
			calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMinimum(Calendar.DAY_OF_MONTH));
			todaysDay          =Calendar.getInstance().get(Calendar.DAY_OF_MONTH);			
			lastDayOfMonth    = Calendar.getInstance().getActualMaximum(Calendar.DAY_OF_MONTH);
				       
			/*sql  = "select geo_loc, unit__form,to_char (tran_date,'DD') as tran_date, day_quantity, pyr_cumqty,daily_budqty "+
					"from ("+
					"select s.GEO_LOC,p.tran_date, t.unit__form, "+
					"fn_get_budgetqty(p.tran_date,s.GEO_LOC,t.unit__form ) as daily_budqty ,"+
					"sum(fn_qty_form(p.item_code,p.unit,t.unit__form, p.quantity)) as "+
					"day_quantity, fn_get_pyr_cumqty( "+
					"to_date(add_months(p.tran_date,-12)),s.geo_loc,t.unit__form ) as pyr_cumqty "+
					"from dwh_production_day_events p, item i, item_type t, site s "+
					"where p.site_code = s.site_code "+
					"and  p.item_code = i.item_code "+
					"and i.item_type = t.item_type "+
					"and p.tran_date >= ? "+
					"and p.tran_date <= ? "+
					"and p.ref_type like 'P%' "+
					"and p.ref_ser = 'W-RCP' "+
					"group by s.GEO_LOC, p.tran_date, t.unit__form ) order by geo_loc, unit__form,tran_date ";
			 */
						
			
			sql="select geo_loc, unit__form,to_char (tran_date,'DD') as tran_date, day_quantity, pyr_cumqty, " +
					"daily_budqty , sum(day_quantity) over (partition by geo_loc, unit__form order by tran_date) " +
					"as cur_cumqty from ( select s.GEO_LOC,p.tran_date, t.unit__form, " +
					"sum(fn_qty_form(p.item_code,p.unit,t.unit__form, p.quantity)) " +
					"as day_quantity, fn_get_pyr_cumqty( to_date(add_months(p.tran_date,-12)),s.geo_loc,t.unit__form ) " +
					"as pyr_cumqty, fn_get_budgetqty(p.tran_date,s.GEO_LOC,t.unit__form ) " +
					"as daily_budqty from dwh_production_day_events p, item i, item_type t, " +
					"site s where p.site_code = s.site_code and  p.item_code = i.item_code " +
					"and i.item_type = t.item_type and p.tran_date >= ? " +
					"and p.tran_date <= ? and p.ref_type like 'P%' " +
					"and p.ref_ser = 'W-RCP' group by s.GEO_LOC, p.tran_date, t.unit__form ) " +
					"order by geo_loc, unit__form,tran_date";
			
			
				   pstmt = conn.prepareStatement(sql); 			
			       pstmt.setString(1,frmDate);
			       pstmt.setString(2,toDate);
			       rs = pstmt.executeQuery();
		          	xmlData = new StringBuffer("<?xml version=\"1.0\"?><Root>");
			        xmlData.append("<Detail>");
			        int cntFrBudget=0;			    	
 				    String preCumQty="";
 				
 				    String prePyrDayQty="";
			        int dayCount=0;
			   
			while (rs.next())
			{
   					count++;
   					geoLocation =checkNull(rs.getString("geo_loc"));
   					unit  =checkNull(rs.getString("unit__form"));  
   					
   					
   					
   					if(count > 1 && ((!previousUnit.equals(unit)) ||  (!geoLocationPrevious.equals(geoLocation))) )
   					{
   						//System.out.println("last dayCount outside:"+dayCount);   						
   						if(dayCount<=lastDayOfMonth)
   						{
   							int cntr =dayCount;
   							for(int j=0;j<lastDayOfMonth-cntr;j++)
   							{
   							 cntFrBudget++; 
   							 //System.out.println("Budget Counter remaining last dated ===="+cntFrBudget);
   							 xmlData.append("<tran_date  date=\""+(listDate.get(dayCount))+"\">");
   							 //System.out.println("listDate.get(dayCount) case of after date ==="+listDate.get(dayCount));
	   	   		   			 xmlData.append("<day_quantity quantity=\"0\">");   	   		   					
	   	   		   			 xmlData.append("<pyr_dayqty>").append("<![CDATA["+prePyrDayQty+"]]>").append("</pyr_dayqty>");	   	   		   					
	   	   		   			 xmlData.append("<daily_budqty>").append("<![CDATA["+dailyBudgetQty*(cntFrBudget)+"]]>").append("</daily_budqty>");	   	   		   					
	   	   		   			 xmlData.append("<cur_cumqty>").append("<![CDATA["+preCumQty+"]]>").append("</cur_cumqty>");	   	   		   					
	   	   		   			 xmlData.append("</day_quantity>");   					
	   	   		   		     xmlData.append("</tran_date>");
	   	   		      	     dayCount++;
	   	   		      	     
   							}
   						}
   							
   						xmlData.append("</unit_form>");
   						xmlData.append("</geo_loc>");
   						//dayCount=0;
   					}
   				
   					if((count==1) || (!previousUnit.equals(unit)) ||( !geoLocationPrevious.equals(geoLocation)) )
   					{
   						
   					//Add here for decr pupose	
   	   					
   		   			  //sql="select  g.descr as geol_descr, u.descr as unit_descr  from gencodes g , uom u  where g.FLD_NAME='GEO_LOC' and g.fld_value= ? and u.unit= ?";
   		   				
   					 sql="select  descr from gencodes  where FLD_NAME='GEO_LOC' and fld_value= ?";
   						
   		   			   pstmt1 = conn.prepareStatement(sql); 			
   				       pstmt1.setString(1,geoLocation);   				     
   				       rs1 = pstmt1.executeQuery();
   				       if(rs1.next())
   				       {
   				    	   
   				    	geol_Descr = checkNull(rs1.getString("descr"))  ; 
   				    	
   				    	   
   				       }   				       
   					   pstmt1 = null;   					  
   					   rs1 = null;
   					  //**************************************************
   					  sql="select descr from uom where unit= ?";
						
		   			   pstmt1 = conn.prepareStatement(sql); 			
		   			   pstmt1.setString(1,unit);
				      
				       rs1 = pstmt1.executeQuery();
				       if(rs1.next())
				       {
				    	   
				    	unit_Descr = checkNull(rs1.getString("descr"))  ; 
				    	   
				       }
				       pstmt1.close();
					   pstmt1 = null;
					   rs1.close();
					   rs1 = null;   					
   					   
   					//End here decr pupose for Unit_form and geo_location
   					   
   						cntFrBudget=0;	
   						xmlData.append("<geo_loc location=\""+geoLocation+"\" glocdescr =\""+geol_Descr+"\"  >");
   						xmlData.append("<unit_form unit=\""+checkNull(rs.getString("unit__form"))+"\" unitdescr =\""+unit_Descr+"\">");
   						dayCount=0;
   						preCumQty="0.0";
   						prePyrDayQty="0.0";   						
   						geoLocationPrevious   =geoLocation=checkNull(rs.getString("geo_loc"));
   						previousUnit         =checkNull(rs.getString("unit__form"));
   						//Add descr
   						geol_Descr="";
   						unit_Descr="";
   						
   					}
   					

   					dailyBudgetQty =rs.getDouble("daily_budqty");   					
   					cntFrBudget++; 
   					//System.out.println("Budget Counter===="+cntFrBudget);
   					 			    	
   					
   					if(!listDate.get(dayCount).equals(rs.getString("tran_date")))
   					{
   						int cnt= dayCount;	
   						for(int i=0;i<Integer.parseInt(rs.getString("tran_date"))-(cnt+1);i++)
   						{
   							//System.out.println("before day count:"+dayCount);
   							xmlData.append("<tran_date  date=\""+(listDate.get(dayCount))+"\">");
	   	   		   			xmlData.append("<day_quantity quantity=\"0\">");   	   		   					
	   	   		   			xmlData.append("<pyr_dayqty>").append("<![CDATA["+prePyrDayQty+"]]>").append("</pyr_dayqty>");	   	   		   					
	   	   		   			xmlData.append("<daily_budqty>").append("<![CDATA["+dailyBudgetQty*(cntFrBudget)+"]]>").append("</daily_budqty>");	   	   		   					
	   	   		   			xmlData.append("<cur_cumqty>").append("<![CDATA["+preCumQty+"]]>").append("</cur_cumqty>");	   	   		   					
	   	   		   			xmlData.append("</day_quantity>");   					
	   	   		   		    xmlData.append("</tran_date>");
	   	   		            dayCount++;
	   	   		            cntFrBudget++; 
	   	   		            //System.out.println("Budget Counter in empty tag for loop===="+cntFrBudget);
	   	   		           
   						}
   						
   					}  				
   				    xmlData.append("<tran_date  date=\""+checkNull(rs.getString("tran_date"))+"\">");
   					xmlData.append("<day_quantity quantity=\""+checkNull(rs.getString("day_quantity"))+"\">");
   					xmlData.append("<pyr_dayqty>").append("<![CDATA[" + checkNull(rs.getString("pyr_cumqty"))+"]]>").append("</pyr_dayqty>");
   					xmlData.append("<daily_budqty>").append("<![CDATA[" + dailyBudgetQty*(cntFrBudget) +"]]>").append("</daily_budqty>");
   					xmlData.append("<cur_cumqty>").append("<![CDATA[" + checkNull(rs.getString("cur_cumqty"))+"]]>").append("</cur_cumqty>");
   					xmlData.append("</day_quantity>");   					
   					xmlData.append("</tran_date>");			
   					preCumQty       = checkEmpty(checkNull(rs.getString("cur_cumqty")));     				 
   					prePyrDayQty    =checkEmpty(checkNull(rs.getString("pyr_cumqty"))); 
   					dayCount++;
   					
   					
   					
			     
			}
			
			if(count>0)
			{
				if(dayCount<lastDayOfMonth){
					for(int p=dayCount;p<lastDayOfMonth;p++){
					cntFrBudget++; 
				    xmlData.append("<tran_date  date=\""+(listDate.get(dayCount))+"\">");
  		   			xmlData.append("<day_quantity quantity=\"0\">");   	   		   					
  		   			xmlData.append("<pyr_dayqty>").append("<![CDATA["+prePyrDayQty+"]]>").append("</pyr_dayqty>");	   	   		   					
  		   			xmlData.append("<daily_budqty>").append("<![CDATA["+dailyBudgetQty*(cntFrBudget)+"]]>").append("</daily_budqty>");	   	   		   					
  		   			xmlData.append("<cur_cumqty>").append("<![CDATA["+preCumQty+"]]>").append("</cur_cumqty>");	   	   		   					
  		   			xmlData.append("</day_quantity>");   					
  		   		    xmlData.append("</tran_date>");
					}
			}
			    xmlData.append("</unit_form>");
			    xmlData.append("</geo_loc>");
			}
			
								
			xmlData.append("</Detail>");
		    xmlData.append("</Root>");
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;

		} catch (Exception e)
		{

			e.printStackTrace();
			System.out.println("ibase.dashboard.mfg.ejb :ProductionVsBudgetReport"+e);
			throw new ITMException(e);
		} finally
		{
			try
			{
				if (conn != null)
				{
					if (rs != null)
						rs.close();
					    rs = null;
					
					if (pstmt != null)
						pstmt.close();
					    pstmt = null;				   
					    conn.close();
					    conn = null;
				}
				conn = null;
			} catch (Exception d)
			{
				d.printStackTrace();
				System.out.println("ibase.dashboard.mfg.ejb :ProductionVsBudgetReport"+d);
				throw new ITMException(d);
			}
		}
		return xmlData.toString();
    }
    
    
    private String checkNull(String input)
	{
		
		if (input == null)
		{
			input = "";
		} else
		{
			input = input.trim();
		}
		return input;
	}
    
    private String checkEmpty(String input)
	{
		
		if (input == "")
		{
			input = "0";
		} else
		{
			input = input.trim();
		}
		return input;
	}
    
}
