package ibase.dashboard.scm.ejb;
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 javax.ejb.Stateless;
import org.json.simple.JSONObject;
/**
 * Session Bean implementation class SalesPersonSales
 */
@Stateless
public class SalesInventory extends ValidatorEJB implements SalesInventoryRemote, SalesInventoryLocal
{
		
	public SalesInventory()
	{}
	GenericUtility genericUtility = GenericUtility.getInstance();
	//1.This is the main method to show the parent graph 
	@SuppressWarnings("unchecked")		
	public JSONObject getSalesInventoryDivWise(String dataSourceName, String month, String nomns) throws RemoteException, ITMException
	{
		
		JSONObject rawDataJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		String finYear="";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();	
		
		try
		{
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			int count=0;
			if(month!=null && month.trim().length()>0)
			{
				System.out.println("month inside EJB SalesInventory.getSalesInventoryDivWise(String dataSourceName, String month) ==="+month+"and Nomns==="+nomns);					
				String selectedmonth  = month.substring(4, 6);				
				if(selectedmonth.trim().equalsIgnoreCase("01") ||selectedmonth.trim().equalsIgnoreCase("02") ||selectedmonth.trim().equalsIgnoreCase("03"))
				{
			    	finYear = String.valueOf(Integer.parseInt(month.substring(0, 4))-1)+"-"+month.substring(2, 4);
					
				}
				else
				{
					finYear = month.substring(0, 4)+"-"+ (String.valueOf(Integer.parseInt(month.substring(0, 4))+1)).substring(2, 4);
				}
				System.out.println("updates month and year from dcoding  =="+month+"finYear =="+finYear);
				
				/*sql = " SELECT ITEMSER.sh_descr AS DIVISION, a.brand, a.ytdsl AS YTD_SALES, a.ytdach AS YTD_ACH, a.cl_stk AS tot_invtry,a.noofmth_invtry FROM ( SELECT item_ser,brand,ytdsl,ytdach,cl_stk, ROUND(cl_stk/(ytdsl/nomn),1) AS noofmth_invtry FROM "
                     +" (SELECT sl.item_ser, i.item_sub_group_descr AS brand, SUM(sl.ytdsl) AS ytdsl, SUM(sl.trgt_ytd) AS trgt_ytd, (CASE WHEN SUM(sl.trgt_ytd) <>0 THEN ROUND( SUM(sl.ytdsl)/ SUM(sl.trgt_ytd)* 100 ,1) ELSE ROUND(2*100,1) END) AS ytdach,"
                     +" SUM(NVL(clstk.cl_stk,0)) AS cl_stk, (CASE WHEN TO_NUMBER(SUBSTR('"+month+"',5,2))>=4 THEN TO_NUMBER (SUBSTR('"+month+"',5,2))- 3 ELSE TO_NUMBER(SUBSTR('"+month+"',5,2))+9 END) AS nomn FROM sales_item i,"
                     +" (SELECT item_code, item_ser, SUM(net_sales_qty) AS ytdsl, SUM( sales_target_qty) AS trgt_YTD FROM SM_SALES_EXP WHERE prd_code >=( CASE WHEN TO_NUMBER(SUBSTR('"+month+"', 5,2))>=4 THEN SUBSTR('"+month+"',1,4)||'04' ELSE TO_CHAR(TO_NUMBER(SUBSTR( '"+month+"',1,4))-1)||'04' END )"
                     +" AND prd_code <= '"+month+"' AND item_code<'A' GROUP BY item_code, item_ser ORDER BY item_code,item_ser ) sl ,( SELECT item_code, SUM(cl_stk) AS cl_stk FROM (  SELECT item_code, SUM(cl_stk_qty) AS cl_stk FROM sun.inventory WHERE inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm'))"
                     +" AND loc_code IN ( SELECT loc_code FROM LOC_SALABLE) GROUP BY item_code UNION ALL SELECT item_code, SUM(cl_stk_qty) AS cl_stk FROM sun.inventory_utl WHERE inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm'))"
                     +" AND loc_code IN ( SELECT loc_code FROM LOC_SALABLE) GROUP BY item_code UNION ALL SELECT item_code, SUM(cl_stk_qty) AS cl_stk FROM sun.inventory_spl WHERE inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND loc_code IN ( SELECT loc_code FROM LOC_SALABLE) GROUP BY item_code )"
                     +" GROUP BY item_code ) CLSTK WHERE Sl.item_code= i.item_code AND i.item_sub_group NOT IN ('CU','BJ') AND sl.item_code=clstk.item_code(+) GROUP BY sl.item_ser, i.item_sub_group_descr HAVING SUM(sl.ytdsl)>0 ) GROUP BY item_ser,brand,ytdsl,ytdach,cl_stk,nomn HAVING ROUND(cl_stk/(ytdsl/nomn),1) >6 ) A,"
                     +" ITEMSER WHERE a.item_ser=ITEMSER.item_ser AND a.brand NOT LIKE 'DUMMY%' ORDER BY DIVISION, brand ";
				*/
				
				
				sql=" SELECT ITEMSER.sh_descr AS DIVISION, a.brand, a.ytdsl AS YTD_SALES, a.ytdach AS YTD_ACH, a.cl_stk AS tot_invtry,a.noofmth_invtry " +
					" FROM ( SELECT item_ser,brand,ytdsl,ytdach,cl_stk, ROUND(cl_stk/(ytdsl/nomn),1) AS noofmth_invtry FROM ( SELECT sl.item_ser, trim(i.item_sub_group_descr) AS brand," +
					" SUM(sl.ytdsl) AS ytdsl, SUM(sl.trgt_ytd) AS trgt_ytd, (CASE WHEN SUM(sl.trgt_ytd) <>0 THEN ROUND( SUM(sl.ytdsl)/ SUM(sl.trgt_ytd)* 100 ,1) " +
					" ELSE ROUND(2*100,1) END) AS ytdach, SUM(NVL(clstk.cl_stk,0)) AS cl_stk, (CASE WHEN TO_NUMBER(SUBSTR('"+month+"',5,2))>=4 THEN" +
					" TO_NUMBER(SUBSTR('"+month+"',5,2))- 3 ELSE TO_NUMBER(SUBSTR('"+month+"',5,2))+9 END) AS nomn FROM sales_item i, " +
					" (SELECT item_code, item_ser, SUM(net_sales_qty) AS ytdsl, SUM(sales_target_qty) AS trgt_YTD FROM SM_SALES_EXP WHERE " +
					" prd_code >=( CASE WHEN TO_NUMBER(SUBSTR('"+month+"',5,2))>=4 THEN SUBSTR('"+month+"',1,4)||'04' ELSE " +
					" TO_CHAR(TO_NUMBER(SUBSTR('"+month+"',1,4))-1)||'04' END ) AND prd_code <= '"+month+"' AND item_code<'A' GROUP BY item_code, " +
					" item_ser ORDER BY item_code,item_ser ) sl ,( SELECT item_code, SUM(cl_stk) AS cl_stk FROM (  SELECT item_code, SUM(cl_stk_qty) AS cl_stk FROM " +
					" sun.inventory WHERE inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND loc_code IN ( SELECT loc_code FROM LOC_SALABLE) GROUP BY item_code UNION ALL SELECT " +
					" item_code, SUM(cl_stk_qty) AS cl_stk FROM sun.inventory_utl WHERE inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND loc_code IN ( SELECT loc_code FROM LOC_SALABLE) " +
					" GROUP BY item_code UNION ALL SELECT item_code, SUM(cl_stk_qty) AS cl_stk FROM sun.inventory_spl WHERE inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND loc_code " +
					" IN ( SELECT loc_code FROM LOC_SALABLE) GROUP BY item_code ) GROUP BY item_code ) CLSTK WHERE Sl.item_code= i.item_code AND ('"+month+"' = '"+month+"') AND i.item_sub_group NOT " +
					" IN ('CU','BJ') AND sl.item_code=clstk.item_code(+) GROUP BY sl.item_ser, trim(i.item_sub_group_descr) HAVING SUM(sl.ytdsl)>0 ) GROUP BY item_ser,brand,ytdsl,ytdach,cl_stk,nomn HAVING " +
					" ( ROUND(cl_stk/(ytdsl/nomn),1) "+nomns +") ) A , ITEMSER WHERE a.item_ser=ITEMSER.item_ser AND a.brand NOT LIKE 'DUMMY%'";
				  //" ( ROUND(cl_stk/(ytdsl/nomn),1) >= '"+from_mns+"' and ROUND(cl_stk/(ytdsl/nomn),1) <= '"+to_mns+"' ) ) A , ITEMSER WHERE a.item_ser=ITEMSER.item_ser AND a.brand NOT LIKE 'DUMMY%'";
					
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
								
				rowData = new JSONObject();
				rowData.put("division",       rs.getString("DIVISION"));
				rowData.put("brand",          rs.getString("brand"));
				rowData.put("ytd_sales",      rs.getString("YTD_SALES"));
				rowData.put("ytd_ach",        rs.getString("YTD_ACH"));
				rowData.put("tot_invtry",     rs.getString("tot_invtry"));
				rowData.put("noofmth_invtry", rs.getString("noofmth_invtry"));
				rawDataJson.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 :SalesInventory.getSalesInventoryDivWise(String dataSourceName, String month):" + d.getMessage());
				throw new ITMException(d);
			}
		}
	
		return rawDataJson;
	}
	
	
// 1.End  the main method to show the parent graph 
// Method No.2, this method calling from clicking on the division group  by Birendra Pandey on dated 04/03/2015		
	@SuppressWarnings("unchecked")		
	public JSONObject getSalesInventoryBrandWise(String dataSourceName, String month,String brandName) throws RemoteException, ITMException
	{
		
		JSONObject rawDataJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		String finYear="";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();	
		
		try
		{
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			int count=0;
			if(month!=null && month.trim().length()>0)
			{
				System.out.println("month inside EJB SalesInventory.getSalesInventoryBrandWise(String dataSourceName, String month,String divCode) ==="+month);					
				String selectedmonth  = month.substring(4, 6);				
				if(selectedmonth.trim().equalsIgnoreCase("01") ||selectedmonth.trim().equalsIgnoreCase("02") ||selectedmonth.trim().equalsIgnoreCase("03"))
				{
			    	finYear = String.valueOf(Integer.parseInt(month.substring(0, 4))-1)+"-"+month.substring(2, 4);
					
				}
				else
				{
					finYear = month.substring(0, 4)+"-"+ (String.valueOf(Integer.parseInt(month.substring(0, 4))+1)).substring(2, 4);
				}
							
				/*sql = " SELECT i.item_sub_group_descr AS brand, s.city AS SITE, SUM(stk.cl_stk) AS site_invtry FROM sales_item i, appl.SITE s, (SELECT item_code, site_code, SUM(cl_stk) AS cl_stk FROM "
					  +" (SELECT item_code, site_code,SUM(cl_stk_qty) AS cl_stk FROM sun.inventory WHERE inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND loc_code IN ( SELECT loc_code FROM LOC_SALABLE) GROUP BY item_code,"
					  +" site_code UNION ALL SELECT item_code, site_code, SUM(cl_stk_qty) AS cl_stk FROM sun.inventory_utl WHERE inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND loc_code IN ( SELECT loc_code FROM LOC_SALABLE) GROUP BY item_code,"
					  +" site_code UNION ALL SELECT item_code, site_code, SUM(cl_stk_qty) AS cl_stk FROM sun.inventory_spl WHERE inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND loc_code IN ( SELECT loc_code FROM LOC_SALABLE) GROUP BY item_code, site_code ) GROUP BY item_code, site_code ) stk WHERE stk.item_code= i.item_code"
					  +" AND s.site_code= stk.site_code AND i.item_sub_group NOT IN ('CU','BJ') AND i.item_sub_group_descr = '"+brandName+"' GROUP BY i.item_sub_group_descr, s.city ORDER BY brand,SITE";
				*/
				
				/*sql=" SELECT stk.brand, stk.site_code,s.city AS SITE,SUM(stk.cl_stk) AS site_invtry, SUM(near_exp) AS near_exp, SUM(stk.cl_stk)-SUM(near_exp) AS long_exp "
					+"  FROM  appl.SITE s, "
					+"  (SELECT item_code, brand, site_code, SUM(cl_stk) AS cl_stk, SUM(near_exp) AS near_exp "
					+"  FROM "
					+"  (  SELECT v.item_code, trim(i.item_sub_group_descr) AS brand,v.site_code,SUM(v.cl_stk_qty) AS cl_stk, "
					+"  (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life "
					+" 	THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp "
					+"  FROM sales_item i,sun.inventory v "
					+"  WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) "
					+"  AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) "
					+" AND v.item_code= i.item_code "
					+" AND i.item_sub_group NOT IN ('CU','BJ') "
					+" AND trim(i.item_sub_group_descr) = '"+brandName+"' "
					+" GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life, v.exp_date "
					+" UNION ALL "
					+" SELECT v.item_code, trim(i.item_sub_group_descr) AS brand, v.site_code, SUM(v.cl_stk_qty) AS cl_stk, "
					+" (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life " 
					+" THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp "
					+" FROM sales_item i,sun.inventory_utl v "
					+"   WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) "
					+" AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) "
					+" AND v.item_code= i.item_code "
					+" AND i.item_sub_group NOT IN ('CU','BJ') "
					+" AND trim(i.item_sub_group_descr) = '"+brandName+"' "
					+" GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life, v.exp_date "
					+" UNION ALL "
					+" SELECT v.item_code, trim(i.item_sub_group_descr) AS brand, v.site_code, SUM(v.cl_stk_qty) AS cl_stk, "
					+" (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life " 
					+" THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp "
					+" FROM sales_item i,sun.inventory_spl v "
					+" WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) "
					+" AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) "
					+" AND v.item_code= i.item_code "
					+" AND i.item_sub_group NOT IN ('CU','BJ') "
					+" AND trim(i.item_sub_group_descr) = '"+brandName+"' "
					+" GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code,i.min_shelf_life, v.exp_date "
					+" ) "
					+" GROUP BY item_code, brand,site_code "
					+" ) stk "
					+" WHERE s.site_code= stk.site_code "
					+" GROUP BY stk.brand, stk.site_code,s.city "
					+" ORDER BY brand,site_invtry DESC";
				*/
				
				sql=" WITH data1 AS (SELECT s.city AS SITE,SUM(stk.cl_stk) AS site_invtry, SUM(near_exp) AS near_exp, SUM(stk.cl_stk)-SUM(near_exp) AS long_exp FROM  appl.SITE s, " +
					" (SELECT item_code, brand, site_code, SUM(cl_stk) AS cl_stk, SUM(near_exp) " +
					" AS near_exp FROM (  SELECT v.item_code, trim(i.item_sub_group_descr) AS brand,v.site_code,SUM(v.cl_stk_qty) AS cl_stk," +
					" (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) " +
					" AS near_exp FROM sales_item i,sun.inventory v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN" +
					" ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND i.item_sub_group NOT IN ('CU','BJ') AND " +
					" trim(i.item_sub_group_descr) = '"+brandName+"' GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life," +
					" v.exp_date UNION ALL SELECT v.item_code, trim(i.item_sub_group_descr) AS brand, v.site_code, SUM(v.cl_stk_qty) " +
					" AS cl_stk, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN" +
					" SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp FROM sales_item i,sun.inventory_utl v " +
					" WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code " +
					" AND i.item_sub_group NOT IN ('CU','BJ') AND trim(i.item_sub_group_descr) = '"+brandName+"' GROUP BY v.item_code, trim(i.item_sub_group_descr), " +
					" v.site_code, i.min_shelf_life, v.exp_date UNION ALL SELECT v.item_code, trim(i.item_sub_group_descr) AS brand, v.site_code," +
					" SUM(v.cl_stk_qty) AS cl_stk, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life " +
					" THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp FROM sales_item i,sun.inventory_spl v WHERE " +
					" v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) AND " +
					" v.item_code= i.item_code AND i.item_sub_group NOT IN ('CU','BJ') AND trim(i.item_sub_group_descr) = '"+brandName+"' " +
					" GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code,i.min_shelf_life, v.exp_date ) GROUP BY item_code, brand,site_code ) " +
					" stk WHERE s.site_code= stk.site_code GROUP BY s.city ) SELECT * FROM ( SELECT 'Tot_Invtry' AS ctgry,SITE,site_invtry FROM data1) pivot ( SUM(site_invtry ) " +
					" FOR SITE IN ( 'AGARTALA' AS AGARTALA, 'ALLEPPEY' AS ALLEPPEY, 'BANGALORE' AS BANGALORE, 'BARODA' AS BARODA, 'BHIWANDI' AS BHIWANDI, 'CALCUTTA' AS CALCUTTA, 'CHANDIGARH'" +
					" AS CHANDIGARH, 'CHENNAI' AS CHENNAI, 'COCHIN' AS COCHIN, 'CUTTACK' AS CUTTACK ,'DEHRADUN' AS DEHRADUN , 'DELHI' AS DELHI, 'GHAZIABAD' AS GHAZIABAD, 'GUWAHATI' AS GUWAHATI, " +
					" 'HOWRAH' AS HOWRAH, 'HYDERABAD' AS HYDERABAD, 'INDORE' AS INDORE, 'JAIPUR' AS JAIPUR, 'JAMMU' AS JAMMU, 'LUCKNOW' AS LUCKNOW ,'MADURAI' AS MADURAI , 'MIRA ROAD' AS MIRAROAD ,'NAGPUR' AS " +
					" NAGPUR ,'PANCHKULA' AS PANCHKULA , 'PATNA' AS PATNA, 'RAIPUR' AS RAIPUR, 'RAJKOT' AS RAJKOT, 'RANCHI' AS RANCHI, 'SALCETTA' AS SALCETTA, 'SILIGURI' AS SILIGURI, 'VAPI' AS VAPI, 'VIJAYAWADA' AS VIJAYAWADA,'ZIRAKPUR' " +
					" AS ZIRAKPUR,'KOLKATA' AS KOLKATA,'NEW DELHI' AS NEWDELHI )   ) UNION ALL SELECT * FROM ( SELECT 'Near_Expiry' AS ctgry,SITE,near_exp FROM data1) pivot ( SUM(near_exp ) FOR SITE IN ( 'AGARTALA' AS AGARTALA, 'ALLEPPEY' AS ALLEPPEY," +
					" 'BANGALORE' AS BANGALORE, 'BARODA' AS BARODA, 'BHIWANDI' AS BHIWANDI, 'CALCUTTA' AS CALCUTTA, 'CHANDIGARH' AS CHANDIGARH, 'CHENNAI' AS CHENNAI, 'COCHIN' AS COCHIN, 'CUTTACK' AS CUTTACK ,'DEHRADUN' AS DEHRADUN , 'DELHI' AS DELHI, 'GHAZIABAD'" +
					" AS GHAZIABAD, 'GUWAHATI' AS GUWAHATI, 'HOWRAH' AS HOWRAH, 'HYDERABAD' AS HYDERABAD, 'INDORE' AS INDORE, 'JAIPUR' AS JAIPUR, 'JAMMU' AS JAMMU, 'LUCKNOW' AS LUCKNOW ,'MADURAI' AS MADURAI , 'MIRA ROAD' AS MIRAROAD ,'NAGPUR' AS NAGPUR ,'PANCHKULA' AS PANCHKULA ," +
					" 'PATNA' AS PATNA, 'RAIPUR' AS RAIPUR, 'RAJKOT' AS RAJKOT, 'RANCHI' AS RANCHI, 'SALCETTA' AS SALCETTA, 'SILIGURI' AS SILIGURI, 'VAPI' AS VAPI, 'VIJAYAWADA' AS VIJAYAWADA,'ZIRAKPUR' AS ZIRAKPUR,'KOLKATA' AS KOLKATA,'NEW DELHI' AS NEWDELHI )   ) UNION ALL SELECT * FROM " +
					" ( SELECT 'Long_Expiry' AS ctgry,SITE,long_exp FROM data1) pivot ( SUM(long_exp ) FOR SITE IN ( 'AGARTALA' AS AGARTALA, 'ALLEPPEY' AS ALLEPPEY, 'BANGALORE' AS BANGALORE, 'BARODA' AS BARODA, 'BHIWANDI' AS BHIWANDI, 'CALCUTTA' AS CALCUTTA, 'CHANDIGARH' AS CHANDIGARH, 'CHENNAI'" +
					" AS CHENNAI, 'COCHIN' AS COCHIN, 'CUTTACK' AS CUTTACK ,'DEHRADUN' AS DEHRADUN , 'DELHI' AS DELHI, 'GHAZIABAD' AS GHAZIABAD, 'GUWAHATI' AS GUWAHATI, 'HOWRAH' AS HOWRAH, 'HYDERABAD' AS HYDERABAD, 'INDORE' AS INDORE, 'JAIPUR' AS JAIPUR, 'JAMMU' AS JAMMU, 'LUCKNOW' AS LUCKNOW ,'MADURAI' " +
					" AS MADURAI , 'MIRA ROAD' AS MIRAROAD ,'NAGPUR' AS NAGPUR ,'PANCHKULA' AS PANCHKULA , 'PATNA' AS PATNA, 'RAIPUR' AS RAIPUR, 'RAJKOT' AS RAJKOT, 'RANCHI' AS RANCHI, 'SALCETTA' AS SALCETTA, 'SILIGURI' AS SILIGURI, 'VAPI' AS VAPI, 'VIJAYAWADA' AS VIJAYAWADA,'ZIRAKPUR' AS ZIRAKPUR,'KOLKATA'" +
					" AS KOLKATA,'NEW DELHI' AS NEWDELHI )) ";
				
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				rowData = new JSONObject();
				rowData.put("ctgry",         checkNull(rs.getString("ctgry")));
				rowData.put("agartala",      checkNull(rs.getString("AGARTALA")));
				rowData.put("alleppey",      checkNull(rs.getString("ALLEPPEY")));		
				rowData.put("bangalore",     checkNull(rs.getString("BANGALORE")));		
				rowData.put("baroda",        checkNull( rs.getString("BARODA")));	
				rowData.put("bhiwandi",      checkNull(rs.getString("BHIWANDI")));		
				rowData.put("calcutta",      checkNull(rs.getString("CALCUTTA")));		
				rowData.put("chandigarh",    checkNull(rs.getString("CHANDIGARH")));		
				rowData.put("chennai",       checkNull(rs.getString("CHENNAI")));		
				rowData.put("cochin",        checkNull(rs.getString("COCHIN")));		
				rowData.put("cuttack",       checkNull(rs.getString("CUTTACK")));		
				rowData.put("dehradun",      checkNull(rs.getString("DEHRADUN")));		
				rowData.put("delhi",         checkNull(rs.getString("DELHI")));		
				rowData.put("ghaziabad",     checkNull( rs.getString("GHAZIABAD")));		
				rowData.put("guwahati",      checkNull(rs.getString("GUWAHATI")));		
				rowData.put("howrah",        checkNull(rs.getString("HOWRAH")));		
				rowData.put("hyderabad",    checkNull(rs.getString("HYDERABAD")));		
				rowData.put("indore",       checkNull(rs.getString("INDORE")));		
				rowData.put("jaipur",       checkNull( rs.getString("JAIPUR")));		
				rowData.put("jammu",        checkNull(rs.getString("JAMMU")));		
				rowData.put("lucknow",      checkNull(rs.getString("LUCKNOW")));		
				rowData.put("madurai",      checkNull(rs.getString("MADURAI")));		
				rowData.put("miraroad",     checkNull(rs.getString("MIRAROAD")));		
				rowData.put("nagpur",       checkNull(rs.getString("NAGPUR")));		
				rowData.put("panchkula",    checkNull(rs.getString("PANCHKULA")));	
				rowData.put("patna",        checkNull(rs.getString("PATNA")));		
				rowData.put("raipur",       checkNull(rs.getString("RAIPUR")));		
				rowData.put("rajkot",       checkNull(rs.getString("RAJKOT")));		
				rowData.put("ranchi",       checkNull(rs.getString("RANCHI")));		
				rowData.put("salcetta",     checkNull(rs.getString("SALCETTA")));		
				rowData.put("siliguri",     checkNull(rs.getString("SILIGURI")));		
				rowData.put("vapi",         checkNull(rs.getString("VAPI")));		
				rowData.put("vijayawada",   checkNull(rs.getString("VIJAYAWADA")));		
				rowData.put("zirakpur",    checkNull(rs.getString("ZIRAKPUR")));	
				rowData.put("kolkata",     checkNull(rs.getString("KOLKATA")));	
				rowData.put("newdelhi",    checkNull(rs.getString("NEWDELHI")));				
				
				rawDataJson.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 :SalesInventory.getSalesInventoryBrandWise(String dataSourceName, String month,String divCode):" + d.getMessage());
				throw new ITMException(d);
			}
		}
	
		return rawDataJson;
	}
// End Method No.2, this method calling from clicking on the division group  by Birendra Pandey on dated 04/03/2015		
	
	private String checkNull(String str)
	{
		if(str == null)
		{
			return "";
		}
		else
		{
			return str ;
		}

	}
		
}
