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.GenericUtility;
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.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.ejb.Stateless;

import org.json.simple.JSONObject;

/**
 * Session Bean implementation class CustomerInfoEJB
 */
@Stateless
public class CustomerInfoEJB extends ValidatorEJB implements CustomerInfoRemote, CustomerInfoLocal {

    /**
     * Default constructor. 
     */
    public CustomerInfoEJB() {
        
    }
    GenericUtility genericUtility = GenericUtility.getInstance();
    @SuppressWarnings("unchecked")
	@Override
    public JSONObject getCurrentReceivable(String custCode, String lastMonth,String dataSourceName) throws RemoteException, ITMException
    {
		JSONObject rawDataCurntRecvbleJson = null;
		JSONObject rowData = null;		
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		DashboardUtility dashbordUtil = null;
		Date frmDate = null;
		Date toDate = null;
		try
		{			
			System.out.println(" Inside CustomerInfoEJB for getCurrentReceivable Method Data Soruc Name ="+dataSourceName+" custCode:"+custCode+" lastMonth is =" + lastMonth);
			dashbordUtil             = new DashboardUtility();
			conn                     = connDriver.getConnectDB(dataSourceName);
			connDriver               = null;	
			rawDataCurntRecvbleJson  = new JSONObject(); 
			SimpleDateFormat gridDateFormat = new SimpleDateFormat( genericUtility.getApplDateFormat());
			SimpleDateFormat convrtToTimStmDate= new SimpleDateFormat(genericUtility.getDBDateFormat());
			if(lastMonth!=null && lastMonth.trim().length()>0 )
			{
				Calendar cal = Calendar.getInstance();		
				toDate = new Date(cal.get(Calendar.YEAR) - 1900, cal.get(Calendar.MONTH), cal.get(Calendar.DATE));
				cal.get(Calendar.MONTH + 1);
				cal.add(Calendar.MONTH,-(Integer.parseInt(lastMonth)));	
				int month = cal.get(Calendar.MONTH);
				frmDate = new Date(cal.get(Calendar.YEAR) - 1900, month, cal.get(Calendar.DATE));
				
			}
			/** start of Customer name top custoer code conversion**/
			custCode           = dashbordUtil.getCommaSeparated(custCode);
			System.out.println("in recceivalble:"+custCode+":");
			/** end of Customer name top custoer code conversion**/
			
			if (custCode!=null && custCode.trim().length()>0 && frmDate != null && toDate != null)
			{
					sql = " SELECT A.SITE_CODE,A.CUST_CODE,B.CUST_NAME,A.TRAN_SER,A.REF_NO,A.REF_DATE,A.CURR_CODE, "
							+" A.EXCH_RATE,TOT_AMT,ADJ_AMT,(TOT_AMT-ADJ_AMT) BAL_AMT, DUE_DATE,trunc(sysdate-DUE_DATE ) as days,A.CR_TERM "  
							+" FROM RECEIVABLES A,CUSTOMER B WHERE A.CUST_CODE=B.CUST_CODE AND "
							+" A.CUST_CODE IN ("+custCode+") AND A.REF_DATE  between  ?  and  ?  "
							+" and  TOT_AMT-ADJ_AMT<>0";
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
					pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));

					rs = pstmt.executeQuery();
					int count = 0;					
					while (rs.next())	
					{
						rowData = new JSONObject();
					    rowData.put("site_code", checkNull(rs.getString("SITE_CODE")));
						rowData.put("cust_code", checkNull(rs.getString("CUST_CODE")));
						rowData.put("cust_name", checkNull(rs.getString("CUST_NAME")));
						rowData.put("tran_ser",  checkNull(rs.getString("TRAN_SER")));
						rowData.put("ref_no",    checkNull(rs.getString("REF_NO")));
						String refDate          =checkNull(rs.getString("REF_DATE"));
						
						if(refDate.trim().length()>0)
						{
							rowData.put("ref_date", gridDateFormat.format(convrtToTimStmDate.parse(refDate)));
						}
						else
						{							
							rowData.put("ref_date",checkNull(rs.getString("REF_DATE")));
						}						
						rowData.put("curr_code",checkNull( rs.getString("CURR_CODE")));
						rowData.put("exch_rate", checkNull(rs.getString("EXCH_RATE")));		
						rowData.put("tot_amt", checkNull(rs.getString("TOT_AMT")));		
						rowData.put("adj_amt",checkNull( rs.getString("ADJ_AMT")));		
						rowData.put("bal_amt", checkNull(rs.getString("BAL_AMT")));	
						String dueDate   =checkNull(rs.getString("due_date"));
						if(dueDate.trim().length()>0)
						{
							rowData.put("due_date", gridDateFormat.format(convrtToTimStmDate.parse(dueDate)));
						}
						else
						{
							rowData.put("due_date",checkNull (rs.getString("DUE_DATE")));
						}						
						rowData.put("cr_term", checkNull(rs.getString("CR_TERM")));						
						rawDataCurntRecvbleJson.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 :CustomerInfoEJB for getCurrentReceivable Method :" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataCurntRecvbleJson;
		
	}

  //Add new method for create a Pie chart 
    
    
   	@SuppressWarnings("unchecked")
       public JSONObject getCurrentReceivableChartView(String custCode, String lastMonth,String dataSourceName) throws RemoteException, ITMException
       {
   		JSONObject rawDataCurntRecvbleChartJson = null;
   		JSONObject rowData = null;
   		String sql = "";
   		ResultSet rs = null;
   		PreparedStatement pstmt = null;
   		Connection conn = null;
   		ConnDriver connDriver = new ConnDriver();
   		DashboardUtility dashbordUtil = null;
   		Date frmDate = null;
		Date toDate = null;
   		try
   		{			
   			System.out.println(" Inside CustomerInfoEJB for getCurrentReceivableChartView Method Data Soruc Name ="+dataSourceName+" custCode:"+custCode+" lastMonth is =" + lastMonth);
   			conn = connDriver.getConnectDB(dataSourceName);
   			connDriver = null;	
   			rawDataCurntRecvbleChartJson  = new JSONObject(); 
   			
   		    SimpleDateFormat convrtToTimStmDate= new SimpleDateFormat(genericUtility.getDBDateFormat());
		    
   		 if(lastMonth!=null && lastMonth.trim().length()>0 )
			{
				Calendar cal = Calendar.getInstance();		
				toDate = new Date(cal.get(Calendar.YEAR) - 1900, cal.get(Calendar.MONTH), cal.get(Calendar.DATE));
				cal.get(Calendar.MONTH + 1);
				cal.add(Calendar.MONTH,-(Integer.parseInt(lastMonth)));	
				int month = cal.get(Calendar.MONTH);
				frmDate = new Date(cal.get(Calendar.YEAR) - 1900, month, cal.get(Calendar.DATE));
				
			}		
   			//end date conversion form
   		
   			dashbordUtil = new DashboardUtility();
   			
   			/** start of Customer name top custoer code conversion**/
			custCode           = dashbordUtil.getCommaSeparated(custCode);
			System.out.println("in recceivalble:"+custCode+":");
			/** end of Customer name top custoer code conversion**/
   			
   			 if (custCode != null && custCode.trim().length() > 0  && frmDate != null && toDate != null)
   				{
   					sql = " SELECT sum (bal_amt) as balAmt,days from (SELECT A.SITE_CODE,A.CUST_CODE,B.CUST_NAME,A.TRAN_SER,A.REF_NO,A.REF_DATE,A.CURR_CODE, "
   							+" A.EXCH_RATE,TOT_AMT,ADJ_AMT,(TOT_AMT-ADJ_AMT) BAL_AMT, DUE_DATE,trunc(sysdate-DUE_DATE ) as days,A.CR_TERM "  
   							+" FROM RECEIVABLES A,CUSTOMER B WHERE A.CUST_CODE=B.CUST_CODE  "
   							+" and A.CUST_CODE IN  ("+custCode+")  AND A.REF_DATE  between  ?  and ? "
   							+" and  TOT_AMT-ADJ_AMT<>0) group by days order by days";   					
   					
   					pstmt = conn.prepareStatement(sql);
   					pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
					pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));
   					rs = pstmt.executeQuery();
   					int count = 0;					
   					int    dueDays=0;
   					double thirthySlab=0.0;
   					double sixthySlab=0.0;
   					double ninthySlab=0.0;
   					double oneTwinthySlab=0.0;
   					double oneTwinthyPlus=0.0;
   					double balAmt=0.0;					
   					while (rs.next())
   					{
   						rowData = new JSONObject();
   					    
   						//start code for Pie chart creation 
   						balAmt           =  Double.parseDouble(rs.getString("balAmt"));
   						dueDays          =  Integer.parseInt(rs.getString("days"));
   						
   						if(dueDays >=0 && dueDays<=30)
   					    {
   							thirthySlab = thirthySlab+balAmt;
   						}
   				    	else if(dueDays>30 && dueDays<=60)
   						{			
   							sixthySlab = sixthySlab +balAmt;								
   						}
   						else if(dueDays>60 && dueDays<=90)
   					    {			
   							ninthySlab = ninthySlab +balAmt;								
   						}
   						else if(dueDays>90 && dueDays<=120)
   						{			
   							oneTwinthySlab = oneTwinthySlab +balAmt;								
   						}
   						else 
   						{			
   							oneTwinthyPlus = oneTwinthyPlus +balAmt;								
   						} 				
   					}
   					rowData = new JSONObject();
   						rowData.put("thirty", "0-30");
   						rowData .put("thirty_slab", thirthySlab);
   						rawDataCurntRecvbleChartJson.put(count, rowData);
   						
   						count++;
   						rowData = new JSONObject();
   						rowData.put("thirty",  "30-60");
   						rowData.put("thirty_slab",  sixthySlab);
   						rawDataCurntRecvbleChartJson.put(count, rowData);
   						count++;
   						rowData = new JSONObject();
   						rowData.put("thirty",  "60-90");
   						rowData.put("thirty_slab",  ninthySlab);
   						rawDataCurntRecvbleChartJson.put(count, rowData);
   						count++;
   						rowData = new JSONObject();
   						rowData.put("thirty","90-120");
   						rowData.put("thirty_slab",oneTwinthySlab);
   						rawDataCurntRecvbleChartJson.put(count, rowData);
   						count++;
   						rowData = new JSONObject();
   						rowData.put("thirty", "120 +");						
						rowData.put("thirty_slab", oneTwinthyPlus);	
						rawDataCurntRecvbleChartJson.put(count, rowData);
   						System.out.println("rawDataCurntRecvbleChartJson:"+rawDataCurntRecvbleChartJson);
   				}
   		}
   		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 :CustomerInfoEJB for getCurrentReceivableChartView Method :" + d.getMessage());
   				throw new ITMException(d);
   			}
   		}
   		return rawDataCurntRecvbleChartJson;
   		
   	}
     
       //end of create pie char
    
    
    @SuppressWarnings("unchecked")
   	@Override
   	public JSONObject getPendingOrder(String custCode, String lastMonth,String dataSourceName) throws RemoteException, ITMException 
   	{
   		JSONObject rawDataPendingOrderJson = null;
   		JSONObject rowData = null;
   		String sql = "";
   		ResultSet rs = null;
   		PreparedStatement pstmt = null;
   		Connection conn = null;
   		ConnDriver connDriver = new ConnDriver();
   		DashboardUtility dashbordUtil = null;
   		double pendingValue=0.0;
   		String qntyStduom="";
   		String qntyDesp="";
   		String rate="";
   		String unit="";
   		String preUnit="";
   		double netPendingValue=0.0;
   		double backOrderValue=0.0;
   		double totBackOrderValue=0.0;
   		String month1="";
   		String itemType="";
   		String key="";
   		ArrayList<String> values = null;
   		LinkedHashMap<String, Double> catValues = null;
   		LinkedHashMap<String, Double> graphDataMap = new LinkedHashMap<String, Double>(); 
   		LinkedHashMap<String, Double> sortedGraphDataMap = new LinkedHashMap<String, Double>(); 
   		int totMonth=0;
   		int frmMonth=0;
   		int toMonth=0;
   		ArrayList<String> itemTypeList =  new ArrayList<String>();
   		ArrayList<String> monthList = new ArrayList<String> ();
   		Date frmDate = null;
		Date toDate = null;
   		try
   		{			
   			System.out.println(" Inside CustomerInfoEJB for getPendingOrder Method Data Soruc Name ="+dataSourceName+" custCode:"+custCode+" lastMonth is =" + lastMonth);
   			conn = connDriver.getConnectDB(dataSourceName);
   			connDriver = null;	
   			rawDataPendingOrderJson = new JSONObject(); 
   			dashbordUtil = new DashboardUtility();
   			//start date conversion form	
   			SimpleDateFormat gridDateFormat = new SimpleDateFormat( genericUtility.getApplDateFormat());
   			 
   			SimpleDateFormat graphDateFormat =  new SimpleDateFormat("dd-MMM-yy");	
   		    SimpleDateFormat convrtToTimStmDate= new SimpleDateFormat(genericUtility.getDBDateFormat());
   			
   		    Calendar cal = Calendar.getInstance();		
   		 if(lastMonth!=null && lastMonth.trim().length()>0 )
			{
				toDate = new Date(cal.get(Calendar.YEAR) - 1900, cal.get(Calendar.MONTH), cal.get(Calendar.DATE));
				cal.get(Calendar.MONTH + 1);
				toMonth=cal.get(Calendar.MONTH);
				cal.add(Calendar.MONTH,-(Integer.parseInt(lastMonth)));	
				frmMonth = cal.get(Calendar.MONTH);
				frmDate = new Date(cal.get(Calendar.YEAR) - 1900, frmMonth, cal.get(Calendar.DATE));
				totMonth = (toMonth-frmMonth);
			}		
   		  
   			//end date conversion form

   			/** start of Customer name top custoer code conversion**/
   			custCode           = dashbordUtil.getCommaSeparated(custCode);
   			/** end of Customer name top custoer code conversion**/
   			
   			/*if(custCode!=null && custCode.trim().length()>0)
   		       {
   				        custCode           = dashbordUtil.getCommaSeparated(custCode);
   		    	        custCodeSorder     ="and A.CUST_CODE IN  ("+custCode+")";
   		    	        System.out.println("in custcode block :: custCodeSorder-->"+custCodeSorder );
   		       }*/
      			
   			 if (custCode != null && custCode.trim().length() > 0  && frmDate != null  && toDate != null )
   				{
   				 /** Start of rawDataPendingOrderJson object for GRID Chart**/
   				 
   				 sql  =  "  SELECT A.SITE_CODE,A.SALE_ORDER,A.ORDER_DATE,A.CUST_CODE,C.CUST_NAME,A.CUST_ORDER__NO, "
   						   +" CASE WHEN A.ORDER_STATUS='O' THEN 'Open' WHEN A.ORDER_STATUS='S' THEN 'Submit' WHEN A.ORDER_STATUS='P' THEN 'Partially Shipped' END ORDER_STATUS, "
   						   +" A.CONFIRMED,A.CONF_DATE, CASE WHEN A.STATUS='P' THEN 'Pending' WHEN A.STATUS='U' THEN 'Under Shipment' WHEN A.STATUS='H' THEN 'Hold' END STATUS,A.STATUS_DATE, "
   						   +" B.ITEM_CODE__ORD,D.DESCR,D.ITEM_SER,B.UNIT,B.QUANTITY__STDUOM, "
   						   +" B.DSP_DATE,B.DSP_DATE-A.ORDER_DATE DELAY_DAYS,B.HOLD_FLAG , A.CUST_CODE__DLV,f.cust_name cust_name_dlv, "
   						   +" a.DLV_CITY,a.CUST_PORD,a.PORD_DATE,e.QTY_DESP,d.site_code,d.item_type, "
   						   +" (select count(1) from despatch des where des.sord_no = a.sale_order) no_of_ship,b.rate, "
   						   +" DDF_STOCK_PICK_RATE((select var_value from disparm where prd_code = '999999' and var_name = 'NET_REAL_PRICE_LIST'), "
   						   +" a.pl_date, B.ITEM_CODE__ORD, null, null, null, A.SITE_CODE) net_rate "
   						   +" FROM SORDER A,SORDDET B,CUSTOMER C,ITEM D, SORDITEM E, CUSTOMER F "
   						   +" WHERE A.SALE_ORDER=B.SALE_ORDER AND A.CUST_CODE=C.CUST_CODE AND B.ITEM_CODE__ORD=D.ITEM_CODE "
   						   +" AND F.CUST_CODE = A.CUST_CODE__DLV AND e.sale_order = a.sale_order and e.line_no = b.line_no "
   						   +" and e.item_code = B.ITEM_CODE__ORD and e.ITEM_FLAG = 'I' and A.CUST_CODE IN  ("+custCode+") "  
   						   +" and A.ORDER_DATE  between  ?  and ? AND B.sordform_no IS NOT NULL AND A.STATUS NOT IN('C','D','X') AND A.ORDER_STATUS NOT IN('C') " 
   						   +" UNION ALL " 
   						   +" SELECT A.SITE_CODE,A.TRAN_ID,A.ORDER_DATE,A.CUST_CODE,C.CUST_NAME,'' CUST_ORDER__NO, "
   						   +" '' ORDER_STATUS,'N' CONFIRMED,NULL CONF_DATE,A.STATUS,NULL STATUS_DATE,B.ITEM_CODE,D.DESCR, "
   						   +" D.ITEM_SER,B.UNIT,B.QUANTITY__STDUOM,B.DSP_DATE,B.DSP_DATE-A.ORDER_DATE DELAY_DAYS,'N' HOLD_FLAG , "
   						   +" A.CUST_CODE__DLV,f.cust_name cust_name_dlv,a.DLV_CITY,a.CUST_PORD,a.PORD_DATE,0,d.site_code,d.item_type,0,b.rate, "
   						   +" DDF_STOCK_PICK_RATE((select var_value from disparm where prd_code = '999999' and var_name = 'NET_REAL_PRICE_LIST'), "
   						   +" a.pl_date, B.ITEM_CODE, null, null, null, A.SITE_CODE) net_rate "
   						   +" FROM SORDFORM A,SORDFORMDET B,CUSTOMER C,ITEM D, customer f WHERE A.TRAN_ID=B.TRAN_ID " 
   						   +" AND A.CUST_CODE=C.CUST_CODE AND B.ITEM_CODE=D.ITEM_CODE and f.cust_code = a.cust_code__dlv "
   						   +" AND STATUS <> 'S'  and A.CUST_CODE IN  ("+custCode+") and A.ORDER_DATE  between  ?  and ? ORDER BY ITEM_TYPE,UNIT";
   				  		
   					pstmt = conn.prepareStatement(sql);
   					pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
   					pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));
   					pstmt.setTimestamp(3,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
   					pstmt.setTimestamp(4,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));
   					rs = pstmt.executeQuery();
   					int count = 0;			
   					while (rs.next())
   					{
   						rowData = new JSONObject();   						
   						qntyStduom=(checkNull( rs.getString("QUANTITY__STDUOM")));
   						qntyDesp=(checkNull(rs.getString("QTY_DESP")));
   						rate =(checkNull(rs.getString("rate")));
   						
   						rowData.put("site_code", checkNull(rs.getString("SITE_CODE")));
   						rowData.put("sale_order", checkNull(rs.getString("SALE_ORDER")));
   						String ordDate=checkNull(rs.getString("ORDER_DATE"));
   						if(ordDate!=null && ordDate.trim().length()>0)
   						{
   							rowData.put("order_date", gridDateFormat.format(convrtToTimStmDate.parse(ordDate)));
   							rowData.put("graphdate", String.valueOf(graphDateFormat.format(rs.getDate("ORDER_DATE"))).substring(0, 6));
   						}
   						else
   						{
   							rowData.put("order_date",checkNull (rs.getString("ORDER_DATE")));
   						}						
   						rowData.put("cust_code", checkNull(rs.getString("CUST_CODE")));
   						rowData.put("cust_name", checkNull(rs.getString("CUST_NAME")));
   						rowData.put("cust_order__no", checkNull(rs.getString("CUST_ORDER__NO")));
   						rowData.put("order_status",checkNull (rs.getString("ORDER_STATUS")));
   						rowData.put("confirmed", checkNull(rs.getString("CONFIRMED")));		
   						String confDate=checkNull(rs.getString("CONF_DATE"));
   						if(confDate!=null && confDate.trim().length()>0)
   						{
   							rowData.put("conf_date", gridDateFormat.format(convrtToTimStmDate.parse(confDate)));
   						}
   						else
   						{
   							rowData.put("conf_date",checkNull (rs.getString("CONF_DATE")));
   						}
   						rowData.put("status", checkNull(rs.getString("STATUS")));		
   						rowData.put("status_date", checkNull(rs.getString("STATUS_DATE")));	
   						rowData.put("item_code__ord", checkNull(rs.getString("ITEM_CODE__ORD")));
   						rowData.put("descr", checkNull(rs.getString("DESCR")));
   						rowData.put("item_ser", checkNull(rs.getString("ITEM_SER")));
   						rowData.put("unit", checkNull(rs.getString("UNIT")));
   						rowData.put("quantity__stduom",checkNull( rs.getString("QUANTITY__STDUOM")));
   						String dispDate=checkNull(rs.getString("DSP_DATE"));
   						if(dispDate!=null && dispDate.trim().length()>0)
   						{
   							rowData.put("dsp_date", gridDateFormat.format(convrtToTimStmDate.parse(dispDate)));
   						}
   						else
   						{
   							rowData.put("dsp_date",checkNull (rs.getString("DSP_DATE")));
   						}
   						rowData.put("delay_days",checkNull( rs.getString("DELAY_DAYS")));
   						rowData.put("hold_flag", checkNull(rs.getString("HOLD_FLAG")));
   						
   						/***************start of newly added arguments*/
   						rowData.put("cust_code__dlv", checkNull(rs.getString("CUST_CODE__DLV")));
   			            rowData.put("cust_name_dlv", checkNull(rs.getString("cust_name_dlv")));
   			            rowData.put("dlv_city", checkNull(rs.getString("DLV_CITY")));
   			            rowData.put("cust_pord", checkNull(rs.getString("CUST_PORD")));
   			            rowData.put("pord_date", checkNull(rs.getString("PORD_DATE")));
   			            rowData.put("qty_desp", checkNull(rs.getString("QTY_DESP")));
   			            rowData.put("site_code", checkNull(rs.getString("site_code")));
   			            rowData.put("item_type", checkNull(rs.getString("item_type")));
   			            rowData.put("no_of_ship", checkNull(rs.getString("no_of_ship")));
   			            rowData.put("rate", checkNull(rs.getString("rate")));
   			            
   			            /*start of pending value and net pending value calculation*/
   			            if(qntyStduom != null && qntyStduom.trim().length()>0 && qntyDesp != null && qntyDesp.trim().length()>0)
   			            {
   			            	pendingValue = (Double.parseDouble(qntyStduom) - Double.parseDouble(qntyDesp));
   			            }
   			            rowData.put("pending_value", pendingValue);
   			            if(rate != null && rate.trim().length()>0)
   			            {
   			            	netPendingValue = (pendingValue*Double.parseDouble(rate));
   			            }			            
   			            rowData.put("net_pending_value", netPendingValue);
   			            /*end of pending value and net pending value calculation*/
   			            
   			            /*start of Back order value calculation*/
   			            unit = checkNull(rs.getString("UNIT"));
   			            if(("").equalsIgnoreCase(preUnit) && unit != null && unit.trim().length()>0)
   			            {
   			            	backOrderValue = pendingValue;
   			            }
   			            if(!("").equalsIgnoreCase(preUnit) && preUnit.equalsIgnoreCase(unit))
   			            {
   			            	backOrderValue =backOrderValue + pendingValue;
   			            }
   			            if( !("").equalsIgnoreCase(preUnit) && !preUnit.equalsIgnoreCase(unit) )
   			            {
   			            	backOrderValue = pendingValue;
   			            }
   			            rowData.put("back_order_value", backOrderValue);
   			            preUnit=unit;
   			            /*end of Back order value calculation*/
   			            
   			            rowData.put("month","");
   						rowData.put("product_category","");
   						rowData.put("category_value","");
   						/********************end of newly added arguments*/
   						
   						rawDataPendingOrderJson.put(count, rowData);
   						count++;
   						
   						//start of  code for stack chart hashmap 
   						month1 =String.valueOf(graphDateFormat.format(rs.getDate("ORDER_DATE"))).substring(3, 9);
   				        itemType = checkNull(rs.getString("item_type"));
   				        if(! itemTypeList.contains(itemType))
   				        {
   				        	itemTypeList.add(itemType);
   				        }
   				        key = (month1.trim()+"@"+itemType.trim());
   				        if(graphDataMap.size()==0)
   				        {
   				        	graphDataMap.put(key, pendingValue);
   				        }
   				        if(graphDataMap !=null && graphDataMap.size()>0)
   				        {
   				        	if(graphDataMap.containsKey(key))
   				        	{
   				        		totBackOrderValue = pendingValue +  graphDataMap.get(key);
   				        	}
   				        	else  if(! graphDataMap.containsKey(key))
   				        	{
   				        		totBackOrderValue = pendingValue;
   				        	}
   				        	graphDataMap.put(key, totBackOrderValue);
   				        }
   				      //end of  code for stack chart hashmap 
   					}	
   					//start of list of month
   					ArrayList<String> sortMonth = new ArrayList<String>();
   					cal = Calendar.getInstance();
   					for(int i=0;i<=totMonth;i++)
   					{
   						monthList.add(String.valueOf(graphDateFormat.format(cal.getTime())).substring(3, 9));
   						cal.add(Calendar.MONTH,-(1));	
   						sortMonth.add(monthList.get(i));
   					}
   					
   					System.out.println("sortMonth:;;;"+sortMonth);
   					for(int k=1;k<=monthList.size();k++)
   					{
   						sortMonth.set(k-1, monthList.get(monthList.size()-k));
   					}
   					// end of list of month
   					//start of graphDataMap in sorted format
   					String itType="";
   					String grpKey="";
   					for(int itmCtr=0;itmCtr<itemTypeList.size();itmCtr++)
   					{
   						itType = itemTypeList.get(itmCtr);
   						for (Map.Entry<String, Double> grpentry : graphDataMap.entrySet())
						{
   							String mapGrpKey     = grpentry.getKey();
   							for(int mnthCtr=0;mnthCtr<sortMonth.size();mnthCtr++)
   	   						{
   								grpKey = (sortMonth.get(mnthCtr).trim()+"@"+itType.trim());
   								if(graphDataMap.containsKey(grpKey))
   								{
   									sortedGraphDataMap.put(grpKey, graphDataMap.get(grpKey));
   								}
   								if(!graphDataMap.containsKey(grpKey) )
   								{
   									sortedGraphDataMap.put(grpKey, 0.0);
   								}
   							}
   						}
   					}
   					//end of graphDataMap in sorted format 
   					
   					//start of  code for stack chart hashmap to json 
   					rowData = new JSONObject();
   					String mnth="";
   					String itmType="",prvItmType="";
   					values = new ArrayList<String>();
   					catValues = new LinkedHashMap<String, Double>();
   					int cnt=0;
   					for (Map.Entry<String, Double> entry : sortedGraphDataMap.entrySet())
   					{
   						cnt++;
   						String mapKey     = entry.getKey();
   						double value  = entry.getValue();
   						String[] temp;
   						temp = mapKey.split("@");
   						mnth = temp[0].trim();
   						itmType = temp[1].trim();
   						
   						if( itmType != null && ("").equals(prvItmType) && itmType.trim().length()>0)
   						{
   							catValues.put(mnth,value);							
   							rowData.put("month",cnt);
   							rowData.put("product_category",itmType);
   							rowData.put("category_value",catValues);
   							rowData.put("pending_value", "");
   						}
   						else if(!("").equals(prvItmType) && prvItmType.equals(itmType))
   						{
   							catValues.put(mnth,value);
   						}
   						if(!("").equals(prvItmType) && ! prvItmType.equals(itmType))
   						{
   							rowData.put("month",cnt);
   							rowData.put("product_category",prvItmType);
   							rowData.put("category_value",catValues);
   							rowData.put("pending_value", "");
   							rawDataPendingOrderJson.put(count, rowData);
   							count++;
   							rowData = new JSONObject();
   							catValues = new LinkedHashMap<String, Double>();
   							catValues.put(mnth,value);
   						}
   						if(cnt==sortedGraphDataMap.size())
   						{
   							rowData.put("month",cnt);
   							rowData.put("product_category",prvItmType);
   							rowData.put("category_value",catValues);
   							rowData.put("pending_value", "");
   							rawDataPendingOrderJson.put(count, rowData);
   						}
   						prvItmType=itmType;
   					}
   					 System.out.println("rawData in pending order for stack chart:\n"+rawDataPendingOrderJson);
   					//end of  code for stack chart hashmap to json 
   					 
   					/** End of rawDataPendingOrderJson object for GRID Chart**/
   					}
   			 
   		}
   		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 :CustomerInfoEJB for getPendingOrder Method :" + d.getMessage());
   				throw new ITMException(d);
   			}
   		}
   		return rawDataPendingOrderJson;
   	}
       
    
    
    @SuppressWarnings("unchecked")
	@Override
	public JSONObject getOrderHistory(String custCode, String lastMonth,String dataSourceName) throws RemoteException, ITMException 
	{
		JSONObject rawDataOrderHistoryJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		DashboardUtility dashbordUtil = null;
		Date frmDate = null;
		Date toDate = null;
		try
		{
			
			System.out.println(" Inside CustomerInfoEJB for getOrderHistory Method Data Soruc Name ="+dataSourceName+" custCode:"+custCode+" lastMonth is =" + lastMonth);
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;	
			
			//start date conversion form	
			SimpleDateFormat gridDateFormat = new SimpleDateFormat( genericUtility.getApplDateFormat());
		    SimpleDateFormat convrtToTimStmDate= new SimpleDateFormat(genericUtility.getDBDateFormat());
		    SimpleDateFormat graphDateFormat =  new SimpleDateFormat("dd-MMM-yy");	
			
		    if(lastMonth!=null && lastMonth.trim().length()>0 )
			{
				Calendar cal = Calendar.getInstance();		
				toDate = new Date(cal.get(Calendar.YEAR) - 1900, cal.get(Calendar.MONTH), cal.get(Calendar.DATE));
				cal.get(Calendar.MONTH + 1);
				cal.add(Calendar.MONTH,-(Integer.parseInt(lastMonth)));	
				int month = cal.get(Calendar.MONTH);
				frmDate = new Date(cal.get(Calendar.YEAR) - 1900, month, cal.get(Calendar.DATE));
				
			}	
			//end date conversion form
			dashbordUtil = new DashboardUtility();

			/** start of Customer name top custoer code conversion**/
			
			custCode           = dashbordUtil.getCommaSeparated(custCode);
			System.out.println("in recceivalble:"+custCode+":");
			/** end of Customer name top custoer code conversion**/
			
			 if (custCode != null && custCode.trim().length() > 0  && frmDate != null && toDate != null)
				{
					sql = " SELECT A.SITE_CODE,A.SALE_ORDER,A.ORDER_DATE,A.CUST_CODE,C.CUST_NAME,A.CUST_ORDER__NO, "
							+" CASE WHEN A.ORDER_STATUS='O' THEN 'Open' WHEN A.ORDER_STATUS='S' THEN 'Submit' WHEN A.ORDER_STATUS='P' THEN 'Partially Shipped' WHEN A.ORDER_STATUS='C' THEN 'Fully Shipped' END ORDER_STATUS " 
							+" ,A.CONFIRMED,A.CONF_DATE,CASE WHEN A.STATUS='P' THEN 'Pending' WHEN A.STATUS='U' THEN 'Under Shipment' WHEN A.STATUS='H' THEN 'Hold' " 
							+" WHEN A.STATUS='C' THEN 'Closed' WHEN A.STATUS='D' THEN 'Despatched' END STATUS ,A.STATUS_DATE, B.ITEM_CODE__ORD, "
							+" D.DESCR,D.ITEM_SER,B.UNIT,B.QUANTITY__STDUOM, B.DSP_DATE,B.DSP_DATE-A.ORDER_DATE DELAY_DAYS,B.HOLD_FLAG "
							+" FROM SORDER A,SORDDET B,CUSTOMER C,ITEM D "
							+" WHERE A.SALE_ORDER=B.SALE_ORDER AND A.CUST_CODE=C.CUST_CODE AND B.ITEM_CODE__ORD=D.ITEM_CODE "
							+" and A.CUST_CODE IN  ("+custCode+")   AND B.sordform_no IS NOT NULL AND A.STATUS NOT IN('X') AND A.ORDER_STATUS NOT IN('C') "
							+" AND A.ORDER_DATE BETWEEN ? AND ? "
							+" UNION ALL "
							+" SELECT A.SITE_CODE,A.TRAN_ID,A.ORDER_DATE,A.CUST_CODE,C.CUST_NAME,'' CUST_ORDER__NO, "
							+" '' ORDER_STATUS,'N' CONFIRMED,NULL CONF_DATE,A.STATUS,NULL STATUS_DATE,B.ITEM_CODE,D.DESCR,"
							+" D.ITEM_SER,B.UNIT,B.QUANTITY__STDUOM,B.DSP_DATE,B.DSP_DATE-A.ORDER_DATE DELAY_DAYS,'N' HOLD_FLAG "  
							+" FROM SORDFORM A,SORDFORMDET B,CUSTOMER C,ITEM D "
							+" WHERE A.TRAN_ID=B.TRAN_ID AND A.CUST_CODE=C.CUST_CODE AND B.ITEM_CODE=D.ITEM_CODE AND STATUS<>'S' "
							+" and A.CUST_CODE IN  ("+custCode+")  AND A.ORDER_DATE BETWEEN ? AND ? ORDER BY ORDER_DATE"; 
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
					pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));
					pstmt.setTimestamp(3,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
					pstmt.setTimestamp(4,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));
					rs = pstmt.executeQuery();
					int count = 0;			
					while (rs.next())
					{
						rowData = new JSONObject();
						rowData.put("site_code",checkNull (rs.getString("SITE_CODE")));
						rowData.put("sale_order", checkNull(rs.getString("SALE_ORDER")));
						String ordDate=checkNull(rs.getString("ORDER_DATE"));
						if(ordDate.trim().length()>0)
						{
							rowData.put("order_date", gridDateFormat.format(convrtToTimStmDate.parse(ordDate)));
							rowData.put("graphdate", String.valueOf(graphDateFormat.format(rs.getDate("ORDER_DATE"))).substring(0, 6));
						}
						else{
							
							rowData.put("order_date",checkNull (rs.getString("ORDER_DATE")));
						}						
						rowData.put("CUST_CODE_SUNUSA", checkNull(rs.getString("CUST_CODE")));
						rowData.put("cust_name", checkNull(rs.getString("CUST_NAME")));
						rowData.put("cust_order__no", checkNull(rs.getString("CUST_ORDER__NO")));
						rowData.put("order_status", checkNull(rs.getString("ORDER_STATUS")));
						rowData.put("status", checkNull(rs.getString("STATUS")));		
						rowData.put("status_date", checkNull(rs.getString("STATUS_DATE")));		
						rowData.put("item_code__ord", checkNull(rs.getString("ITEM_CODE__ORD")));		
						rowData.put("descr", checkNull(rs.getString("DESCR")));		
						rowData.put("item_ser", checkNull(rs.getString("ITEM_SER")));
						rowData.put("unit",checkNull( rs.getString("UNIT")));
						rowData.put("quantity__stduom", checkNull(rs.getString("QUANTITY__STDUOM")));
						String dispDate=checkNull(rs.getString("DSP_DATE"));
						if(dispDate.trim().length()>0)
						{
							rowData.put("dsp_date", gridDateFormat.format(convrtToTimStmDate.parse(dispDate)));
						}
						else{
							
							rowData.put("dsp_date",checkNull(rs.getString("DSP_DATE")));
						}
						rowData.put("delay_days","100");
						//rowData.put("delay_days",checkNull(rs.getString("DELAY_DAYS")));
						rowData.put("hold_flag", checkNull(rs.getString("HOLD_FLAG")));
						rawDataOrderHistoryJson.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 :CustomerInfoEJB for getOrderHistory Method :" + d.getMessage());
				throw new ITMException(d);
			}
		}
		System.out.println("rawDataOrderHistoryJson--["+rawDataOrderHistoryJson.toString()+"]");
		return rawDataOrderHistoryJson;
	
	}
    
    
    @SuppressWarnings("unchecked")
	@Override
	public JSONObject getSalesHistory(String custCode, String lastMonth,String dataSourceName) throws RemoteException, ITMException 
	{
		JSONObject rawDataSalesHistoryJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		DashboardUtility dashbordUtil = null;
		Date frmDate = null;
		Date toDate = null;
		try
		{
			
			System.out.println(" Inside CustomerInfoEJB for getSalesHistory Method Data Soruc Name ="+dataSourceName+" custCode:"+custCode+" lastMonth is =" + lastMonth);
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;	
			dashbordUtil = new DashboardUtility();
			//start date conversion form	
			SimpleDateFormat gridDateFormat = new SimpleDateFormat( genericUtility.getApplDateFormat());
			SimpleDateFormat graphDateFormat =  new SimpleDateFormat("dd-MMM-yy");	 
		    SimpleDateFormat convrtToTimStmDate= new SimpleDateFormat(genericUtility.getDBDateFormat());
			if(lastMonth!=null && lastMonth.trim().length()>0 )
			{
				Calendar cal = Calendar.getInstance();		
				toDate = new Date(cal.get(Calendar.YEAR) - 1900, cal.get(Calendar.MONTH), cal.get(Calendar.DATE));
				cal.get(Calendar.MONTH + 1);
				cal.add(Calendar.MONTH,-(Integer.parseInt(lastMonth)));	
				int month = cal.get(Calendar.MONTH);
				frmDate = new Date(cal.get(Calendar.YEAR) - 1900, month, cal.get(Calendar.DATE));
				
			}			
			//end date conversion form
			
			/** start of Customer name top custoer code conversion**/
			
			custCode           = dashbordUtil.getCommaSeparated(custCode);
			System.out.println("in recceivalble:"+custCode+":");
			/** end of Customer name top custoer code conversion**/
			
   			if (custCode!=null && custCode.trim().length()>0 && frmDate != null && toDate != null)
			{
				sql = " SELECT A.SITE_CODE,A.SORD_NO,B.LINE_NO__SORD,SORD_DATE,A.DESP_DATE,A.CUST_CODE, "
						+" C.CUST_NAME,A.CUST_CODE__DLV,A.CURR_CODE,A.EXCH_RATE,B.ITEM_CODE,D.DESCR,D.ITEM_SER, "
						+" B.LOT_NO,B.LOT_SL,B.QUANTITY,B.UNIT,B.QUANTITY__STDUOM,B.RATE__STDUOM, "
						+" B.QUANTITY__STDUOM*B.RATE__STDUOM AMOUNT,B.NO_ART,B.MFG_DATE,B.EXP_DATE, "
						+" B.SITE_CODE__MFG FROM DESPATCH A,DESPATCHDET B,CUSTOMER C,ITEM D "
						+" WHERE A.DESP_ID=B.DESP_ID AND A.CUST_CODE=C.CUST_CODE "
						+" AND B.ITEM_CODE=D.ITEM_CODE and A.CUST_CODE IN  ("+custCode+")  AND "
						+" A.DESP_DATE BETWEEN ? AND ? order by DESP_DATE,SORD_NO";
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
				pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));
				rs = pstmt.executeQuery();
				int count = 0;			
				while (rs.next())
				{
					rowData = new JSONObject();
					rowData.put("site_code", checkNull(rs.getString("SITE_CODE")));
					rowData.put("sord_no", checkNull(rs.getString("SORD_NO")));
					rowData.put("line_no__sord", checkNull(rs.getString("LINE_NO__SORD")));
					
					
					String sordDate=checkNull(rs.getString("SORD_DATE"));
					String despDate=checkNull(rs.getString("DESP_DATE"));
					if(sordDate.trim().length()>0)
					{
						rowData.put("sord_date", gridDateFormat.format(convrtToTimStmDate.parse(sordDate)));
					}
					else{
						
						rowData.put("sord_date",checkNull(rs.getString("SORD_DATE")));
					}
					
					System.out.println("despDate in sales hisotry : "+despDate);
					if(despDate.trim().length()>0)
					{
						rowData.put("desp_date", gridDateFormat.format(convrtToTimStmDate.parse(despDate)));
						rowData.put("graphdate", String.valueOf(graphDateFormat.format(rs.getDate("DESP_DATE"))).substring(0, 6));
					}
					else{
						
						rowData.put("desp_date",checkNull(rs.getString("DESP_DATE")));
					}
					rowData.put("cust_code",checkNull (rs.getString("CUST_CODE")));
					rowData.put("cust_name",checkNull( rs.getString("CUST_NAME")));
					rowData.put("cust_code__dlv",checkNull( rs.getString("CUST_CODE__DLV")));		
					rowData.put("curr_code", checkNull(rs.getString("CURR_CODE")));		
					rowData.put("exch_rate", checkNull(rs.getString("EXCH_RATE")));		
					rowData.put("item_code", checkNull(rs.getString("ITEM_CODE")));				
					rowData.put("descr", checkNull(rs.getString("DESCR")));
					rowData.put("item_ser",checkNull( rs.getString("ITEM_SER")));
					rowData.put("lot_no",checkNull( rs.getString("LOT_NO")));
					rowData.put("lot_sl", checkNull(rs.getString("LOT_SL")));
					rowData.put("quantity", checkNull(rs.getString("QUANTITY")));
					rowData.put("unit", checkNull(rs.getString("UNIT")));
					rowData.put("quantity__stduom", checkNull(rs.getString("QUANTITY__STDUOM")));
					rowData.put("rate__stduom", checkNull(rs.getString("RATE__STDUOM")));
					rowData.put("amount", checkNull(rs.getString("AMOUNT")));
					rowData.put("no_art", checkNull(rs.getString("NO_ART")));
					rowData.put("mfg_date", checkNull(rs.getString("MFG_DATE")));
					rowData.put("exp_date", checkNull(rs.getString("EXP_DATE")));
					rowData.put("site_code__mfg",checkNull(rs.getString("SITE_CODE__MFG")));					
					rawDataSalesHistoryJson.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 :CustomerInfoEJB for getSalesHistory Method :" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataSalesHistoryJson;	
	}
    @SuppressWarnings("unchecked")
	@Override
	public JSONObject getTransactionSummary(String custCode, String lastMonth,String dataSourceName) throws RemoteException, ITMException 
	{

		JSONObject rawDataTrnsctionSumryJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		DashboardUtility dashbordUtil = null;
		Date frmDate = null;
		Date toDate = null;
		try
		{
			
			System.out.println(" Inside CustomerInfoEJB for getTransactionSummary Method Data Soruc Name ="+dataSourceName+" custCode:"+custCode+" lastMonth is =" + lastMonth);
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;		
			//start date conversion form	
			SimpleDateFormat gridDateFormat = new SimpleDateFormat( genericUtility.getApplDateFormat());
			
			SimpleDateFormat graphDateFormat =  new SimpleDateFormat("dd-MMM-yy");	
		    SimpleDateFormat convrtToTimStmDate= new SimpleDateFormat(genericUtility.getDBDateFormat());
			
		    if(lastMonth!=null && lastMonth.trim().length()>0 )
			{
				Calendar cal = Calendar.getInstance();		
				toDate = new Date(cal.get(Calendar.YEAR) - 1900, cal.get(Calendar.MONTH), cal.get(Calendar.DATE));
				cal.get(Calendar.MONTH + 1);
				cal.add(Calendar.MONTH,-(Integer.parseInt(lastMonth)));	
				int month = cal.get(Calendar.MONTH);
				frmDate = new Date(cal.get(Calendar.YEAR) - 1900, month, cal.get(Calendar.DATE));
				
			}		
			//end date conversion form
			dashbordUtil = new DashboardUtility();
			
			/** start of Customer name top custoer code conversion**/
			
			custCode           = dashbordUtil.getCommaSeparated(custCode);
			System.out.println("in recceivalble:"+custCode+":");
			/** end of Customer name top custoer code conversion**/
			
			if (custCode!=null && custCode.trim().length()>0 && frmDate != null && toDate != null)
			{
				sql = " SELECT SDATE,SUM(NO_OF_ORDER) as NO_OF_ORDER ,SUM(ORDER_VALUE) as ORDER_VALUE,SUM(NO_OF_SALES) as NO_OF_SALES,SUM(SALES_VALUE) as SALES_VALUE, "
						+" SUM(RECEIVABLES) as RECEIVABLES ,SUM(COLLECTION) as COLLECTION ,SUM(NO_OF_DN) as NO_OF_DN,SUM(DN_VALUE) as DN_VALUE,SUM(NO_OF_CN) as NO_OF_CN, "
						+" SUM(CN_VALUE) as CN_VALUE ,SUM(NO_OF_SR) as NO_OF_SR ,SUM(SR_VALUE) as SR_VALUE FROM ( SELECT A.ORDER_DATE SDATE, "
						+" 1 NO_OF_ORDER,B.QUANTITY__STDUOM*B.RATE__STDUOM ORDER_VALUE,0 NO_OF_SALES," 
						+" 0 SALES_VALUE,0 RECEIVABLES,0 COLLECTION,0 NO_OF_DN,0 DN_VALUE,0 NO_OF_CN, "
						+" 0 CN_VALUE,0 NO_OF_SR,0 SR_VALUE FROM SORDER A,SORDDET B "
						+" WHERE A.SALE_ORDER=B.SALE_ORDER  AND A.CUST_CODE IN  ("+custCode+")  AND A.ORDER_DATE "
						+" BETWEEN ?  AND ? AND A.STATUS NOT IN('X') "
						+" UNION ALL SELECT A.REF_DATE SDATE,0 NO_OF_ORDER,0 ORDER_VALUE,0 NO_OF_SALES,0 "
						+" SALES_VALUE,(TOT_AMT-ADJ_AMT) RECEIVABLES,0 COLLECTION,0 NO_OF_DN,0 DN_VALUE,0 "
						+" NO_OF_CN,0 CN_VALUE,0 NO_OF_SR,0 SR_VALUE "
						+" FROM RECEIVABLES A  where A.CUST_CODE IN  ("+custCode+") AND REF_DATE BETWEEN "
						+" ? AND ? ) GROUP BY SDATE order by SDATE";
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
				pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));
				pstmt.setTimestamp(3,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
				pstmt.setTimestamp(4,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));
				rs = pstmt.executeQuery();
				int count = 0;			
				while (rs.next())
				{
					rowData = new JSONObject();
					String sdate=checkNull(rs.getString("SDATE"));
					if(sdate.trim().length()>0)
					{
						rowData.put("sdate", gridDateFormat.format(convrtToTimStmDate.parse(sdate)));
						rowData.put("graphdate", String.valueOf(graphDateFormat.format(rs.getDate("SDATE"))).substring(0, 6));
					}
					else{
						
						rowData.put("sdate",checkNull(rs.getString("SDATE")));
					}
					/*rowData.put("sdate",     sdf1.format(checkNull(rs.getString("SDATE"))));*/
					System.out.println("DATE===="+String.valueOf(graphDateFormat.format(rs.getDate("SDATE"))).substring(0, 6));
					/*rowData.put("graphdate", String.valueOf(sdf.format(rs.getDate("SDATE"))).substring(0, 6));*/
					rowData.put("no_of_order", checkNull(rs.getString("NO_OF_ORDER")));
					rowData.put("order_value", checkNull(rs.getString("ORDER_VALUE")));
					rowData.put("no_of_sales", checkNull(rs.getString("NO_OF_SALES")));
					rowData.put("sales_value", checkNull(rs.getString("SALES_VALUE")));
					rowData.put("receivables", checkNull(rs.getString("RECEIVABLES")));
					rowData.put("collection", checkNull(rs.getString("COLLECTION")));
					rowData.put("no_of_dn", 	checkNull(rs.getString("NO_OF_DN")));		
					rowData.put("dn_value", checkNull(rs.getString("DN_VALUE")));		
					rowData.put("no_of_cn", checkNull(rs.getString("NO_OF_CN")));		
					rowData.put("cn_value", checkNull(rs.getString("CN_VALUE")));
					rowData.put("no_of_sr",checkNull( rs.getString("NO_OF_SR")));		
					rowData.put("sr_value", checkNull(rs.getString("SR_VALUE")));	
					rawDataTrnsctionSumryJson.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 :CustomerInfoEJB for getTransactionSummary Method :" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataTrnsctionSumryJson;
	
	
	}
	
    private String checkNull(String str)
	{
		if(str == null)
		{
			return "";
		}
		else
		{
			return str ;
		}

	}

}
