package ibase.dashboard.scm.ejb;
import ibase.dashboard.Report.Utility.DashboardUtility;
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.sql.SQLException;
import java.util.ArrayList;

import javax.ejb.Stateless;
import org.json.simple.JSONObject;

/**
 * Session Bean implementation class DespatchOrder
 */
@Stateless
public class OrderVsShipmentEJB extends ValidatorEJB implements OrderVsShipmentRemote, OrderVsShipmentLocal {

    /**
     * Default constructor. 
     */
    public OrderVsShipmentEJB() {
        
    }
    
    @SuppressWarnings("unchecked")
	public JSONObject getmonthWiseDespatchOrders(String lastMonth,String siteCode,String custCode,String custCodeDlv,String dataSourceName) throws RemoteException, ITMException
    {
    	JSONObject monthWiseDespatchOrdersJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		DashboardUtility dashbordUtil = null;
		String custCodeSorder="";
		String custCodeDespatch="";
		String custCodeDlvSorder="";
		String custCodeDlvDespatch="";
		
		try
		{
			System.out.println(" Inside DespatchOrder for getmonthWiseDespatchOrders() Method Data Soruc Name ="+dataSourceName+" siteCode:"+siteCode +"lastMonth:="+lastMonth+ " custCode:="+custCode+" custCodeDlv:="+custCodeDlv);
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			dashbordUtil = new DashboardUtility();
			siteCode= dashbordUtil.getCommaSeparated(siteCode);
			//lastMonth="6";
			//start  code for customer code and customer code dlv as dynamic input
		
	   /* if (custName != null && custName.length() > 0)
		{
	    	custNameArry = custName.split("~");
			for (int custCnt = 0; custCnt < custNameArry.length; custCnt++) 
			{
				customerName = custNameArry[custCnt];
				sql =  "SELECT CUST_CODE FROM CUSTOMER WHERE CUST_NAME = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, customerName);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					if (custCodeBuff.length() != 0)
					{
						custCodeBuff.append("," + rs.getString("cust_code").trim());
					} else
					{
						custCodeBuff.append(rs.getString("cust_code").trim());
					}
					System.out.println("custCodeBuff in OrderShipmentEJB===["+custCodeBuff.toString()+"]");
				}
				pstmt.close();
				pstmt = null;
				rs.close();
				rs = null; 
			 }
			custCode = custCodeBuff.toString();
			System.out.println("custCode in OrderShipmentEJB===["+custCode+"]");
		}*/
		       if(custCode!=null && custCode.trim().length()>0)
		       {
				        custCode           = dashbordUtil.getCommaSeparated(custCode);
		    	        custCodeSorder     ="and sorder.cust_code in ("+custCode+")";
		    	        custCodeDespatch   ="and despatch.cust_code in("+custCode+")";
		    	        System.out.println("in custcode block :: custCodeSorder-->"+custCodeSorder + "custCodeDespatch-->"+custCodeDespatch);
		       }
					
		       if(custCodeDlv!=null && custCodeDlv.trim().length()>0)
		       {					
		    	       custCodeDlv          = dashbordUtil.getCommaSeparated(custCodeDlv);
		    	       custCodeDlvSorder    ="and sorder.cust_code__dlv  in ("+custCodeDlv+")";
		    	       custCodeDlvDespatch  ="and despatch.cust_code__dlv in ("+custCodeDlv+")";
		    	       System.out.println("in custCodeDlv block :: custCodeDlvSorder-->"+custCodeDlvSorder + "custCodeDlvDespatch-->"+custCodeDlvDespatch);
			   }
					
								
				//end of code dynamic implementation						
			if((lastMonth!=null && lastMonth.trim().length()>0 ) && (siteCode!=null && siteCode.trim().length()>0))
			{
				sql =  " select month,sum(orders) as orders, sum(despatches) as despatches,month1 "
						+" from (select to_char(sorder.order_date, 'Mon-yy') as month,to_char(sorder.order_date, 'MM') as month1, "
						+" count(sorder.sale_order) as orders,0 as despatches from sorder "
						+" where sorder.order_date between trunc(to_date(TO_CHAR(ADD_MONTHS(SYSDATE, -"+lastMonth+"), 'ddMONyyyy'),'DD-MON-YYYY'),'MM')  and " 
						+" last_day(trunc(sysdate)) "+custCodeSorder + " "+custCodeDlvSorder + " "
						+" and sorder.site_code in ("+siteCode+") "
						+" and sorder.confirmed = 'Y' group by to_char(sorder.order_date, 'Mon-yy'),to_char(sorder.order_date, 'MM') " 
						+" union all select to_char(despatch.desp_date, 'Mon-yy') as month,to_char (despatch.desp_date, 'MM') as month1, "
						+" 0 as orders,count(despatch.desp_id) as despatches from despatch where despatch.desp_date between trunc(to_date(TO_CHAR(ADD_MONTHS(SYSDATE, -"+lastMonth+"), 'ddMONyyyy'),'DD-MON-YYYY'),'MM')  and " 
						+" last_day(trunc(sysdate)) "+custCodeDespatch + " "+custCodeDlvDespatch + " " 
						+" and despatch.site_code in ("+siteCode+") "
						+" and despatch.confirmed = 'Y' group by to_char(despatch.desp_date, 'Mon-yy'),to_char(despatch.desp_date, 'MM') ) "
						+" group by month,month1 order by month1"; 
				pstmt = conn.prepareStatement(sql);				
				rs = pstmt.executeQuery();
				int count = 0;			
				while (rs.next())
				{
					rowData = new JSONObject();
					rowData.put("month", (rs.getString("month")));
					rowData.put("orders", (rs.getString("orders")));
					rowData.put("despatches", (rs.getString("despatches")));
					monthWiseDespatchOrdersJson.put(count, rowData);
					count++;
				}
			}
			
		}
		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();
				System.out.println("Exception in :DespatchOrder for getmonthWiseDespatchOrders Method :" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return monthWiseDespatchOrdersJson;
    }
    /*for futther planning Dhanendra*/
    /*public String getcustCodefrmCust(String splitArry , Connection conn) throws SQLException, ITMException
    {
    	String[] custNameArry = null;
    	String customerName = "";
    	String sql="";
    	PreparedStatement pstmt = null;
		ResultSet rs = null;
		
    	StringBuffer custCodeBuff = new StringBuffer();
    	custNameArry = splitArry.split(",");
    	try
    	{
    	for (int custCnt = 0; custCnt < custNameArry.length; custCnt++) 
		{
			customerName = custNameArry[custCnt];
			sql =  "SELECT CUST_CODE FROM CUSTOMER WHERE CUST_NAME = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, customerName);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				if (custCodeBuff.length() != 0)
				{
					custCodeBuff.append("," + rs.getString("cust_code").trim());
				} else
				{
					custCodeBuff.append(rs.getString("cust_code").trim());
				}
				System.out.println("custCodeBuff in OrderShipmentEJB===["+custCodeBuff.toString()+"]");
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null; 
		 }
    	}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();
				System.out.println("Exception in :DespatchOrder for getmonthWiseDespatchOrders Method :" + d.getMessage());
				throw new ITMException(d);
			}
		}
    	
    	return custCodeBuff.toString();
    			
    }*/

}
