package ibase.dashboard.mfg.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 java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.StringTokenizer;

import javax.ejb.Stateless;

import org.json.simple.JSONObject;

/**
 * Session Bean implementation class ProfitLossBalSheet
 */
@Stateless

public class ProfitLossBalSheet extends ValidatorEJB  implements ProfitLossBalSheetLocal, ProfitLossBalSheetRemote {

    /**
     * Default constructor. 
     */
	JSONObject rawDataJson = new JSONObject();   
	String codes[]=new String[20];
		String signs[] =new String[20];
    public ProfitLossBalSheet() 
    {    	
    }
    GenericUtility genericUtility = GenericUtility.getInstance();
    
   
    @SuppressWarnings("unchecked")
	public JSONObject generateReport(String siteCode,String prdFrom,String prdTo,String acctPrd, String formatCode,String reportType,String dataSourceName) throws RemoteException, ITMException
    { 	
    	
    	ResultSet rs=null;
    	ResultSet rs1 = null;
   		PreparedStatement pstmt=null;
   		PreparedStatement pstmt1 = null;
   		Connection conn = null;
   		ConnDriver connDriver = new ConnDriver();
   		String	prevPrd="";
   		String prevPrdCodeFrom="";
   		String prevPrdCodeTo = "";
   		String sql="";
   		String sql1="";
   		JSONObject rowData = null;
   		String titleCode="";
   		String titleDescr="";
   		String siteDescr="";
   		String balType="";
   		String factor="";
   		String codeString="";
   		
   		double curGroupTotal,preGroupTotal,printLevel ;
		int llCount=0;
		Date date = null;
		int month=0;
   		try
   		{
    	prevPrd= String.valueOf(Integer.parseInt(acctPrd.substring(0, 4))-1)+"-"+acctPrd.substring(2, 4);
    	prevPrdCodeFrom= String.valueOf(Integer.parseInt(prdFrom.substring(0, 4))-1)+prdFrom.substring(4, 6);
    	prevPrdCodeTo=String.valueOf(Integer.parseInt(prdTo.substring(0, 4))-1)+prdTo.substring(4, 6);
    	
    	conn = connDriver.getConnectDB(dataSourceName);
		connDriver = null;	
		
		sql="select descr from SITE where site_code = ?";
		pstmt = conn.prepareStatement(sql);		
		pstmt.setString(1, siteCode);
		rs = pstmt.executeQuery();
		while (rs.next())
		{
			siteDescr= rs.getString("descr");
		}
		rs.close();
		rs=null;
		pstmt.close();
		pstmt=null;		
		sql =" select PL_FORMAT.TITLE_CODE, PL_FORMAT.DESCR, PL_FORMAT.SCHEDULE_NO, PL_FORMAT.SCHEDULE_NO_NEXT, "
			 +" PL_FORMAT.TITLE_CODE__PARENT,pl_format.cmp_grp, pl_format.cmp_sgrp,pl_format.cmp_acct, "
			 +" pl_format.disp_tot,pl_format.disp_type,pl_format.print_level,pl_format.underline, "
			 +" pl_format.bal_type FROM pl_format where (( PL_FORMAT.TITLE_CODE__PARENT is null )  "
			 +" or ( PL_FORMAT.FORMAT_CODE = '" + formatCode + "')) and ( pl_format.report_type = '" 
			 + reportType + "') ORDER BY pl_format.print_level ASC ";
			pstmt = conn.prepareStatement(sql);
			//pstmt.setString(1, frDate);			
			rs = pstmt.executeQuery();
			int count = 0;			
			while (rs.next())
			{
				titleCode=rs.getString("TITLE_CODE");
				titleDescr=rs.getString("DESCR");
				balType=rs.getString("bal_type"); 
				rowData = new JSONObject();
				//rowData.put("as_conversion",factor ); //lc_factor
				//rowData.put("site_code",siteCode );	
				//rowData.put("site_descr",siteDescr );
				rowData.put("title_descr",titleDescr );
				//rowData.put("schedule_no",rs.getString("SCHEDULE_NO") );
				//rowData.put("disp_Type", rs.getString("disp_Type"));
				//rowData.put("Print_Level",0 );
				//rowData.put("disp_tot", rs.getString("disp_tot"));
				//rowData.put("underline",rs.getString("underline") );
				//rowData.put("prd_code__from",prdFrom );
				//rowData.put("prd_code__to",prdTo );			
				
				curGroupTotal = 0.0;
				preGroupTotal = 0.0;
				printLevel = 0.0;
				
				sql1="SELECT count(*)  as total "		  
					 +" FROM pl_format "  
					 +" where PL_FORMAT.TITLE_CODE__PARENT = ? " 
					 +" and pl_format.format_code=?";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, titleCode);	
				pstmt1.setString(2, formatCode);	
				rs1 = pstmt1.executeQuery();
				while (rs1.next())
				{
					llCount = (rs1.getInt("total"));
				}
				rs1.close();
				rs1=null;
				pstmt1.close();
				pstmt1=null;
				
				if(llCount >0)
				{
					if(populateSubGroup(siteCode,siteDescr,titleCode,curGroupTotal,preGroupTotal, printLevel,acctPrd,prdFrom,prdTo,prevPrd,
							             prevPrdCodeFrom,prevPrdCodeTo,formatCode,reportType,factor,rawDataJson,dataSourceName))
					{
						
					}
				}
				else
				{
					/*******************start of  group formula******************/
					codeString = (rs.getString("cmp_grp"));
					codeString="A+B-C+";
					if(codeString.trim()!=null && codeString.trim().length() > 0)
					{
						String tempCode="";
						double ldAmount=0.0;
						getformula_arys(codeString);
						System.out.println("codes:"+codes+"///////signs:"+signs);
						for(int llCtr=1;llCtr<=codes.length;llCtr++)
						{
							tempCode = codes[llCtr-1];
							// start of current year 							
							if(balType.trim().equals("C"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									 +" FROM acctbal,  accounts, acctsgrp "  
									 +" WHERE site_code IN ('"+ siteCode + "') AND "  
									 +" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									 +" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code )  " 
									// +" and ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and "  
									 +" and ( (acctbal.prd_code between '000000' and '"+prdTo+"' ) ) " ;
									 //+" AND ( acctsgrp.group_code = '"+tempCode+"' ) )"; 
							}
							else if(balType.trim().equals("O"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal,   accounts, 	acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and "  
									+" ( (acctbal.prd_code >= '000000' and acctbal.prd_code < '"+prdFrom+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							else 
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal, accounts,acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and "  
									+" ( (acctbal.prd_code between '"+prdFrom+"' and '"+prdTo+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}							
							pstmt1 = conn.prepareStatement(sql);
							//pstmt1.setString(1, titleCode);	
							//pstmt1.setString(2, formatCode);	
							rs1 = pstmt1.executeQuery();
							while (rs1.next())
							{
								ldAmount = checkDoubleNull(rs1.getString("total")); //su)m
							}
							pstmt1.close();
							pstmt1=null;
							rs1.close();
							rs1=null;
							if(llCtr==1)
							{
								if(codeString.substring(0, 1) == "-")
								{
									curGroupTotal = -1 * ldAmount;
								}
								else
								{
									curGroupTotal = ldAmount;
								}
							}
							else
							{
								System.out.println("signs:"+signs.length);
								if(signs[llCtr - 1] =="+")
								{
									curGroupTotal += ldAmount;
								}
								else if(signs[llCtr - 1] == "-")
								{
									curGroupTotal -= ldAmount;
								}
							}
							System.out.println("curGroupTotal:"+curGroupTotal);
						//end of current year	
							
						//Start of Previous year
							if(balType.trim().equals("C"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									 +" FROM acctbal,  accounts, acctsgrp "  
									 +" WHERE site_code IN ('"+ siteCode + "') AND "  
									 +" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									 +" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code )  " 
									// +"and ( acctbal.acct_prd		=	'"+ prevPrd +"'	) and "  
									 +" and ( (acctbal.prd_code between '000000' and '"+prevPrdCodeTo+"' ))  " ;
									// +" AND ( acctsgrp.group_code = '"+tempCode+"' ) )"; 
							}
							else if(balType.trim().equals("O"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal,   accounts, 	acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ prevPrd +"'	) and "  
									+" ( (acctbal.prd_code >= '000000' and acctbal.prd_code < '"+prevPrdCodeFrom+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							else 
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal, accounts,acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ prevPrd +"'	) and "  
									+" ( (acctbal.prd_code between '"+prevPrdCodeFrom+"' and '"+prevPrdCodeTo+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							pstmt1 = conn.prepareStatement(sql);
							//pstmt1.setString(1, titleCode);	
							//pstmt1.setString(2, formatCode);	
							rs1 = pstmt1.executeQuery();
							while (rs1.next())
							{
								ldAmount = (rs1.getInt("total")); //sum
							}
							pstmt1.close();
							pstmt1=null;
							rs1.close();
							rs1=null;
							if(llCtr==1)
							{
								if(codeString.substring(0, 1) == "-")
								{
									preGroupTotal = -1 * ldAmount;
								}
								else
								{
									preGroupTotal = ldAmount;
								}
							}
							else
							{
								if(signs[llCtr - 1] =="+")
								{
									preGroupTotal += ldAmount;
								}
								else if(signs[llCtr - 1] == "-")
								{
									preGroupTotal -= ldAmount;
								}
							}
							System.out.println("preGroupTotal:"+preGroupTotal);
						// end of previous year
						} // end of for of codes.length //end of  group formula
						
					}
					System.out.println("end  of group formula");
				/***********end if of group formula**************/
					
				/***********Start of sub group formula********************/	
					codeString = (rs.getString("cmp_sgrp"));	
					codeString="A-B+C+";
					if(codeString.trim()!=null && codeString.trim().length() > 0)
					{
						String tempCode="";
						double ldAmount=0.0;
						getformula_arys(codeString) ;
						
						for(int llCtr=1;llCtr<=codes.length;llCtr++)
						{
							tempCode = codes[llCtr-1];
						// start of current year 							
							if(balType.trim().equals("C"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total "
									+" FROM acctbal, accounts, acctsgrp  WHERE site_code IN ('"+siteCode + "') AND  "
									+" ( acctbal.acct_code = accounts.acct_code ) and  "
									+" ( acctsgrp.sgroup_code = accounts.sgroup_code )  "
									//+" ( acctbal.acct_prd		=	'"+acctPrd +"'	) and  "
									+"and ( (acctbal.prd_code between '000000' and '"+prdTo+"' ))  ";
									//+" AND ( acctsgrp.sgroup_code = '"+tempCode+"' ))"; 
							}
							else if(balType.trim().equals("O"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total "
									+" FROM acctbal, accounts, acctsgrp  WHERE site_code IN ('"+ siteCode + "') AND  "
									+" ( acctbal.acct_code = accounts.acct_code ) and  "
									+" ( acctsgrp.sgroup_code = accounts.sgroup_code ) and "
									+" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and  "
									+" ( (acctbal.prd_code >= '000000' and acctbal.prd_code < '"+prdFrom+"' ) AND "
									+" ( acctsgrp.sgroup_code = '"+tempCode+"' ))";
							}
							else 
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total "
									+" FROM acctbal,accounts,acctsgrp  "
								    +" WHERE site_code IN ('"+ siteCode + "') AND  "
									+" ( acctbal.acct_code = accounts.acct_code ) and  "
									+" ( acctsgrp.sgroup_code = accounts.sgroup_code ) and "
									+" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and "
									+" ( (acctbal.prd_code between '"+prdFrom+"' and '"+prdTo+"' ) AND "
									+" ( acctsgrp.sgroup_code = '"+tempCode+"' ))";
							}
							pstmt1 = conn.prepareStatement(sql);
							//pstmt1.setString(1, titleCode);	
							//pstmt1.setString(1, formatCode);	
							rs1 = pstmt1.executeQuery();
							while (rs1.next())
							{
								ldAmount = (rs1.getInt("total")); //sum
							}
							pstmt1.close();
							pstmt1=null;
							rs1.close();
							rs1=null;
							if(llCtr==1)
							{
								if(codeString.substring(0, 1) == "-")
								{
									curGroupTotal -= ldAmount;
								}
								else
								{
									curGroupTotal += ldAmount;
								}
							}
							else
							{
								if(signs[llCtr - 1] =="+")
								{
									curGroupTotal += ldAmount;
								}
								else if(signs[llCtr - 1] == "-")
								{
									curGroupTotal -= ldAmount;
								}
							}
							System.out.println("curGroupTotal:"+curGroupTotal);
						//end of current year	
						//start  of previous year
							if(balType.trim().equals("C"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									 +" FROM acctbal,  accounts, acctsgrp "  
									 +" WHERE site_code IN ('"+ siteCode + "') AND "  
									 +" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									 +" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									// +" ( acctbal.acct_prd		=	'"+ prevPrd +"'	) and "  
									 +" ( (acctbal.prd_code between '000000' and '"+prevPrdCodeTo+"' ))  " ;
									// +"AND ( acctsgrp.group_code = '"+tempCode+"' ) )"; 
							}
							else if(balType.trim().equals("O"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal,   accounts, 	acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ prevPrd +"'	) and "  
									+" ( (acctbal.prd_code >= '000000' and acctbal.prd_code < '"+prevPrdCodeFrom+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							else 
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal, accounts,acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ prevPrd +"'	) and "  
									+" ( (acctbal.prd_code between '"+prevPrdCodeFrom+"' and '"+prevPrdCodeTo+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							pstmt1 = conn.prepareStatement(sql);
							//pstmt1.setString(1, titleCode);	
							//pstmt1.setString(1, formatCode);	
							rs1 = pstmt1.executeQuery();
							while (rs1.next())
							{
								ldAmount = (rs1.getInt("total")); //sum
							}
							pstmt1.close();
							pstmt1=null;
							rs1.close();
							rs1=null;
							if(llCtr==1)
							{
								if(codeString.substring(0, 1) == "-")
								{
									preGroupTotal -=  ldAmount;
								}
								else
								{
									preGroupTotal += ldAmount;
								}
							}
							else
							{
								if(signs[llCtr - 1] =="+")
								{
									preGroupTotal += ldAmount;
								}
								else if(signs[llCtr - 1] == "-")
								{
									preGroupTotal -= ldAmount;
								}
							}
							System.out.println("preGroupTotal:"+preGroupTotal);
					    //end of previous year
						}
						
					}	
					System.out.println("end  of sub group formula");
				/***********End of sub group formula********************/	
					
				/***********Start of a/c group formula********************/
					codeString = (rs.getString("cmp_acct"));
					codeString="A+B-C-";
					if(codeString.trim()!=null && codeString.trim().length() > 0)
					{
						String tempCode="";
						double ldAmount=0.0;
						getformula_arys(codeString) ;
						
						for(int llCtr=1;llCtr<=codes.length;llCtr++)
						{
							tempCode = codes[llCtr-1];
						// start of current year 							
							if(balType.trim().equals("C"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total "
									+" FROM acctbal, accounts, acctsgrp  WHERE site_code IN ('"+siteCode + "') AND  "
									+" ( acctbal.acct_code = accounts.acct_code ) and  "
									+" ( acctsgrp.sgroup_code = accounts.sgroup_code ) and "
								//	+" ( acctbal.acct_prd		=	'"+acctPrd +"'	) and  "
									+" ( (acctbal.prd_code between '000000' and '"+prdTo+"' ) ) ";
									//+" AND ( acctsgrp.sgroup_code = '"+tempCode+"' ))"; 
							}
							else if(balType.trim().equals("O"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total "
									+" FROM acctbal, accounts, acctsgrp  WHERE site_code IN ('"+ siteCode + "') AND  "
									+" ( acctbal.acct_code = accounts.acct_code ) and  "
									+" ( acctsgrp.sgroup_code = accounts.sgroup_code ) and "
									+" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and  "
									+" ( (acctbal.prd_code >= '000000' and acctbal.prd_code < '"+prdFrom+"' ) AND "
									+" ( acctsgrp.sgroup_code = '"+tempCode+"' ))";
							}
							else 
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total "
									+" FROM acctbal,accounts,acctsgrp  "
								    +" WHERE site_code IN ('"+ siteCode + "') AND  "
									+" ( acctbal.acct_code = accounts.acct_code ) and  "
									+" ( acctsgrp.sgroup_code = accounts.sgroup_code ) and "
									+" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and "
									+" ( (acctbal.prd_code between '"+prdFrom+"' and '"+prdTo+"' ) AND "
									+" ( acctsgrp.sgroup_code = '"+tempCode+"' ))";
							}
							pstmt1 = conn.prepareStatement(sql);
							//pstmt1.setString(1, titleCode);	
							//pstmt1.setString(1, formatCode);	
							rs1 = pstmt1.executeQuery();
							while (rs1.next())
							{
								ldAmount =( rs1.getInt("total")); //sum
							}
							pstmt1.close();
							pstmt1=null;
							rs1.close();
							rs1=null;
							if(llCtr==1)
							{
								if(codeString.substring(0, 1) == "-")
								{
									curGroupTotal -= ldAmount;
								}
								else
								{
									curGroupTotal += ldAmount;
								}
							}
							else
							{
								if(signs[llCtr - 1] =="+")
								{
									curGroupTotal += ldAmount;
								}
								else if(signs[llCtr - 1] == "-")
								{
									curGroupTotal -= ldAmount;
								}
							}
							System.out.println("curGroupTotal:"+curGroupTotal);
						//end of current year	
						//start  of previous year
							if(balType.trim().equals("C"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									 +" FROM acctbal,  accounts, acctsgrp "  
									 +" WHERE site_code IN ('"+ siteCode + "') AND "  
									 +" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									 +" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code )  " 
									// +"and ( acctbal.acct_prd		=	'"+ prevPrd +"'	)  "  
									 +" and ( (acctbal.prd_code between '000000' and '"+prevPrdCodeTo+"' ) ) " ;
									// +"AND ( acctsgrp.group_code = '"+tempCode+"' ) )"; 
							}
							else if(balType.trim().equals("O"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal,   accounts, 	acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ prevPrd +"'	) and "  
									+" ( (acctbal.prd_code >= '000000' and acctbal.prd_code < '"+prevPrdCodeFrom+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							else 
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal, accounts,acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ prevPrd +"'	) and "  
									+" ( (acctbal.prd_code between '"+prevPrdCodeFrom+"' and '"+prevPrdCodeTo+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							pstmt1 = conn.prepareStatement(sql);
							//.setString(1, titleCode);	
							//pstmt1.setString(1, formatCode);	
							rs1 = pstmt1.executeQuery();
							while (rs1.next())
							{
								ldAmount =( rs1.getInt("total")); //sum
							}
							pstmt1.close();
							pstmt1=null;
							rs1.close();
							rs1=null;
							if(llCtr==1)
							{
								if(codeString.substring(0, 1) == "-")
								{
									preGroupTotal -=  ldAmount;
								}
								else
								{
									preGroupTotal += ldAmount;
								}
							}
							else
							{
								if(signs[llCtr - 1] =="+")
								{
									preGroupTotal += ldAmount;
								}
								else if(signs[llCtr - 1] == "-")
								{
									preGroupTotal -= ldAmount;
								}
							}
							System.out.println("preGroupTotal:"+preGroupTotal);
					    //end of previous year
						}						
					}	
					System.out.println("end  of a/c group formula");
				/***********End of a/c group formula********************/	
					rowData.put("curr_out_val", curGroupTotal);
					rowData.put("prev_out_val", preGroupTotal);
					rowData.put("curr_in_val", 0);
					rowData.put("prev_in_val", 0);
					rawDataJson.put(count, rowData);
					count++;	
				} // end of else of llCount >0				
			}// end of while  		
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			
			//for current year
			String date1 =(Integer.parseInt(prdTo.substring(0, 4))+"-"+Integer.parseInt(prdTo.substring(4, 6))+"-"+ 28);
			date =sdf.parse(date1);
			Calendar cal = Calendar.getInstance();
		    cal.setTime(date);
		    month=cal.get(Calendar.MONTH);
		   do
		   {
			   cal.add(Calendar.DAY_OF_MONTH,1);
			   date1=sdf.format(cal.getTime());
			   date =sdf.parse(date1);
		   }while(month!=cal.get(Calendar.MONTH));
			
		  cal.add(Calendar.DAY_OF_MONTH,-1);
		  date1=sdf.format(cal.getTime());
		  date =sdf.parse(date1);
		 
		  //for previous year
		   date1 =(Integer.parseInt(prevPrdCodeTo.substring(0, 4))+"-"+Integer.parseInt(prevPrdCodeTo.substring(4, 6))+"-"+ 28);
			date =sdf.parse(date1);
			 cal = Calendar.getInstance();
		    cal.setTime(date);
		    month=cal.get(Calendar.MONTH);
		   do
		   {
			   cal.add(Calendar.DAY_OF_MONTH,1);
			   date1=sdf.format(cal.getTime());
			   date =sdf.parse(date1);
		   }while(month!=cal.get(Calendar.MONTH));
			
		  cal.add(Calendar.DAY_OF_MONTH,-1);
		  date1=sdf.format(cal.getTime());
		  date =sdf.parse(date1);
		  
		    
   		}
   		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 :RecallDetails:getProductWiseRecallDetails:" + d.getMessage());
   				throw new ITMException(d);
   			}
   		}	
    			
    	return rawDataJson;
    }
    
    @SuppressWarnings("unchecked")
	public boolean populateSubGroup(String siteCode,String siteDescr,String titleCode,double curGroupTotal,double preGroupTotal,
    		double printLevel,String acctPrd,String prdFrom,String prdTo,String prevPrd,String prevPrdCodeFrom,String prevPrdCodeTo,String formatCode,
    		String reportType,String factor,JSONObject rawDataJson,String dataSourceName) throws RemoteException, ITMException
    {
    	/////JSONObject rawDataJsonSGrp = new JSONObject();    	
    	ResultSet rs=null;
    	ResultSet rs1 = null;
   		PreparedStatement pstmt = null;
   		PreparedStatement pstmt1 = null;
   		Connection conn = null;
   		ConnDriver connDriver = new ConnDriver();
   		String	prevPrdSGrp="";
   		String prevPrdCodeFromSGrp="";
   		String prevPrdCodeToSGrp = "";
   		String sql="";
   		String sql1="";
   		JSONObject rowData = null;
   		String titleCodeSGrp="";
   		String titleDescr="";
   		String siteDescrSGrp="";
   		String balType="";
   		String factorSGrp="";
   		String codeString="";
   		String codes[]=new String[20];
   		String signs[] =new String[20];
   		int totalCurr[]=new int[10]; 
   		int totalPrev[]=new int[10]; 
   		double curGroupTotalSGrp=0.0;
   		double preGroupTotalSGrp=0.0;
   		double printLevelSGrp=0.0 ;
		int llCount=0;
		String parentcode="B1010";
   		try
   		{
    	prevPrdSGrp= String.valueOf(Integer.parseInt(acctPrd.substring(0, 4))-1)+"-"+acctPrd.substring(2, 4);
    	prevPrdCodeFromSGrp= String.valueOf(Integer.parseInt(prdFrom.substring(0, 4))-1)+prdFrom.substring(4, 6);
    	prevPrdCodeToSGrp=String.valueOf(Integer.parseInt(prdTo.substring(0, 4))-1)+prdTo.substring(4, 6);
    	
    	conn = connDriver.getConnectDB(dataSourceName);
		connDriver = null;	
		
		for(int i=0;i<10;i++)
		{
			totalCurr[i]=0;
			totalPrev[i]=0;
		}
		sql="select descr from SITE where site_code = ? ";
		pstmt = conn.prepareStatement(sql);		
		pstmt.setString(1, siteCode);
		rs = pstmt.executeQuery();
		while (rs.next())
		{
			siteDescrSGrp= rs.getString("descr");
		}
		rs=null;
		//rs.close();
		pstmt=null;
		//pstmt.close();
		
		sql =" select PL_FORMAT.TITLE_CODE, PL_FORMAT.DESCR, PL_FORMAT.SCHEDULE_NO, PL_FORMAT.SCHEDULE_NO_NEXT, "
			 +" PL_FORMAT.TITLE_CODE__PARENT,pl_format.cmp_grp, pl_format.cmp_sgrp,pl_format.cmp_acct, "
			 +" pl_format.disp_tot,pl_format.disp_type,pl_format.print_level,pl_format.underline, "
			 +" pl_format.bal_type FROM pl_format where (( PL_FORMAT.TITLE_CODE__PARENT = '"+parentcode+"' )  "
			 +" or ( PL_FORMAT.FORMAT_CODE = '" + formatCode + "')) and ( pl_format.report_type = '" 
			 + reportType + "') ORDER BY pl_format.print_level ASC ";
			pstmt = conn.prepareStatement(sql);
			//pstmt.setString(1, frDate);			
			rs = pstmt.executeQuery();
			int count = 0;	
			int printlevel=0;
			while (rs.next())
			{
				printlevel++;
				titleCodeSGrp=rs.getString("TITLE_CODE");
				titleDescr=rs.getString("DESCR");
				balType=rs.getString("bal_type"); 
				rowData = new JSONObject();
			//	rowData.put("as_conversion",factorSGrp ); //lc_factor
				//rowData.put("site_code",siteCode );	
				//rowData.put("site_descr",siteDescrSGrp );
				rowData.put("title_descr",titleDescr );
				//rowData.put("schedule_no",rs.getString("SCHEDULE_NO") );
				//rowData.put("disp_Type", rs.getString("disp_Type"));
				//rowData.put("Print_Level",printlevel );
				//rowData.put("disp_tot", rs.getString("disp_tot"));
				//rowData.put("underline",rs.getString("underline") );
				////rowData.put("prd_code__from",prdFrom );
				//rowData.put("prd_code__to",prdTo );			
				
				curGroupTotalSGrp = 0.0;
				preGroupTotalSGrp = 0.0;
				
				
				sql1="SELECT count(*)  as total "		  
					 +" FROM pl_format "  
					 +" where PL_FORMAT.TITLE_CODE__PARENT = ? " 
					 +" and pl_format.format_code=?";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, titleCodeSGrp);	
				pstmt1.setString(2, formatCode);	
				rs1 = pstmt1.executeQuery();
				while (rs1.next())
				{
					llCount = rs1.getInt("Total");
				}
				rs1.close();
				rs1=null;
				pstmt1.close();
				pstmt1=null;
				
				if(llCount >0)
				{
					if(populateSubGroup(siteCode,siteDescrSGrp,titleCodeSGrp,curGroupTotalSGrp,preGroupTotalSGrp,
							          	printLevelSGrp,acctPrd,prdFrom,prdTo,prevPrdSGrp,prevPrdCodeFromSGrp,prevPrdCodeToSGrp,formatCode,
							          	reportType,factorSGrp,rawDataJson,dataSourceName))
					{
						totalCurr[printlevel] = 0;
						totalPrev[printlevel]= 0;
						printlevel--;
					}
					
				}
				else
				{
					/*******************start of  group formula******************/
					codeString = (rs.getString("cmp_grp"));
					
					if(codeString.trim()!=null && codeString.trim().length() > 0)
					{
						String tempCode="";
						double ldAmount=0.0;
						getformula_arys(codeString) ;
						for(int llCtr=1;llCtr<=codes.length;llCtr++)
						{
							tempCode = codes[llCtr-1];
							// start of current year 							
							if(balType.trim().equals("C"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									 +" FROM acctbal,  accounts, acctsgrp "  
									 +" WHERE site_code IN ('"+ siteCode + "') AND "  
									 +" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									 +" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									 +" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and "  
									 +" ( (acctbal.prd_code between '000000' and '"+prdTo+"' ) AND " 
									 +" ( acctsgrp.group_code = '"+tempCode+"' ) )"; 
							}
							else if(balType.trim().equals("O"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal,   accounts, 	acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and "  
									+" ( (acctbal.prd_code >= '000000' and acctbal.prd_code < '"+prdFrom+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							else 
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal, accounts,acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and "  
									+" ( (acctbal.prd_code between '"+prdFrom+"' and '"+prdTo+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}							
							pstmt1 = conn.prepareStatement(sql);
							//pstmt1.setString(1, titleCodeSGrp);	
							//pstmt1.setString(1, formatCode);	
							rs1 = pstmt1.executeQuery();
							while (rs1.next())
							{
								ldAmount = (rs1.getInt("total")); //su)m
							}
							pstmt1.close();
							pstmt1=null;
							rs1.close();
							rs1=null;
							if(llCtr==1)
							{
								if(codeString.substring(0, 1) == "-")
								{
									curGroupTotalSGrp = -1 * ldAmount;
								}
								else
								{
									curGroupTotalSGrp = ldAmount;
								}
							}
							else
							{
								if(signs[llCtr - 1] =="+")
								{
									curGroupTotalSGrp += ldAmount;
								}
								else if(signs[llCtr - 1] == "-")
								{
									curGroupTotalSGrp -= ldAmount;
								}
							}
						//end of current year	
							
						//Start of Previous year
							if(balType.trim().equals("C"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									 +" FROM acctbal,  accounts, acctsgrp "  
									 +" WHERE site_code IN ('"+ siteCode + "') AND "  
									 +" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									 +" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									 +" ( acctbal.acct_prd		=	'"+ prevPrdSGrp +"'	) and "  
									 +" ( (acctbal.prd_code between '000000' and '"+prevPrdCodeToSGrp+"' ) AND " 
									 +" ( acctsgrp.group_code = '"+tempCode+"' ) )"; 
							}
							else if(balType.trim().equals("O"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal,   accounts, 	acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ prevPrdSGrp +"'	) and "  
									+" ( (acctbal.prd_code >= '000000' and acctbal.prd_code < '"+prevPrdCodeFromSGrp+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							else 
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal, accounts,acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ prevPrdSGrp +"'	) and "  
									+" ( (acctbal.prd_code between '"+prevPrdCodeFromSGrp+"' and '"+prevPrdCodeToSGrp+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							pstmt1 = conn.prepareStatement(sql);
							//pstmt1.setString(1, titleCodeSGrp);	
							//pstmt1.setString(1, formatCode);	
							rs1 = pstmt1.executeQuery();
							while (rs1.next())
							{
								ldAmount = (rs1.getInt("total")); //sum
							}
							pstmt1.close();
							pstmt1=null;
							rs1.close();
							rs1=null;
							if(llCtr==1)
							{
								if(codeString.substring(0, 1) == "-")
								{
									preGroupTotalSGrp = -1 * ldAmount;
								}
								else
								{
									preGroupTotalSGrp = ldAmount;
								}
							}
							else
							{
								if(signs[llCtr - 1] =="+")
								{
									preGroupTotalSGrp += ldAmount;
								}
								else if(signs[llCtr - 1] == "-")
								{
									preGroupTotalSGrp -= ldAmount;
								}
							}
						// end of previous year
						} // end of for of codes.length //end of  group formula
						
					}
				/***********end if of group formula**************/
					
				/***********Start of sub group formula********************/	
					codeString = (rs.getString("cmp_sgrp"));	
					if(codeString.trim()!=null && codeString.trim().length() > 0)
					{
						String tempCode="";
						double ldAmount=0.0;
						getformula_arys(codeString) ;
						
						for(int llCtr=1;llCtr<=codes.length;llCtr++)
						{
							tempCode = codes[llCtr-1];
						// start of current year 							
							if(balType.trim().equals("C"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total "
									+" FROM acctbal, accounts, acctsgrp  WHERE site_code IN ('"+siteCode + "') AND  "
									+" ( acctbal.acct_code = accounts.acct_code ) and  "
									+" ( acctsgrp.sgroup_code = accounts.sgroup_code ) and "
									+" ( acctbal.acct_prd		=	'"+acctPrd +"'	) and  "
									+" ( (acctbal.prd_code between '000000' and '"+prdTo+"' ) AND "
									+" ( acctsgrp.sgroup_code = '"+tempCode+"' ))"; 
							}
							else if(balType.trim().equals("O"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total "
									+" FROM acctbal, accounts, acctsgrp  WHERE site_code IN ('"+ siteCode + "') AND  "
									+" ( acctbal.acct_code = accounts.acct_code ) and  "
									+" ( acctsgrp.sgroup_code = accounts.sgroup_code ) and "
									+" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and  "
									+" ( (acctbal.prd_code >= '000000' and acctbal.prd_code < '"+prdFrom+"' ) AND "
									+" ( acctsgrp.sgroup_code = '"+tempCode+"' ))";
							}
							else 
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total "
									+" FROM acctbal,accounts,acctsgrp  "
								    +" WHERE site_code IN ('"+ siteCode + "') AND  "
									+" ( acctbal.acct_code = accounts.acct_code ) and  "
									+" ( acctsgrp.sgroup_code = accounts.sgroup_code ) and "
									+" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and "
									+" ( (acctbal.prd_code between '"+prdFrom+"' and '"+prdTo+"' ) AND "
									+" ( acctsgrp.sgroup_code = '"+tempCode+"' ))";
							}
							pstmt1 = conn.prepareStatement(sql);
							//pstmt1.setString(1, titleCodeSGrp);	
							//pstmt1.setString(1, formatCode);	
							rs1 = pstmt1.executeQuery();
							while (rs1.next())
							{
								ldAmount = checkDoubleNull(rs1.getString("total")); //sum
							}
							pstmt1.close();
							pstmt1=null;
							rs1.close();
							rs1=null;
							if(llCtr==1)
							{
								if(codeString.substring(0, 1) == "-")
								{
									curGroupTotalSGrp -= ldAmount;
								}
								else
								{
									curGroupTotalSGrp += ldAmount;
								}
							}
							else
							{
								if(signs[llCtr - 1] =="+")
								{
									curGroupTotalSGrp += ldAmount;
								}
								else if(signs[llCtr - 1] == "-")
								{
									curGroupTotalSGrp -= ldAmount;
								}
							}
						//end of current year	
						//start  of previous year
							if(balType.trim().equals("C"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									 +" FROM acctbal,  accounts, acctsgrp "  
									 +" WHERE site_code IN ('"+ siteCode + "') AND "  
									 +" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									 +" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									 +" ( acctbal.acct_prd		=	'"+ prevPrdSGrp +"'	) and "  
									 +" ( (acctbal.prd_code between '000000' and '"+prevPrdCodeToSGrp+"' ) AND " 
									 +" ( acctsgrp.group_code = '"+tempCode+"' ) )"; 
							}
							else if(balType.trim().equals("O"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal,   accounts, 	acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ prevPrdSGrp +"'	) and "  
									+" ( (acctbal.prd_code >= '000000' and acctbal.prd_code < '"+prevPrdCodeFromSGrp+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							else 
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal, accounts,acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ prevPrdSGrp +"'	) and "  
									+" ( (acctbal.prd_code between '"+prevPrdCodeFromSGrp+"' and '"+prevPrdCodeToSGrp+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							pstmt1 = conn.prepareStatement(sql);
						//	pstmt1.setString(1, titleCodeSGrp);	
						//	pstmt1.setString(1, formatCode);	
							rs1 = pstmt1.executeQuery();
							while (rs1.next())
							{
								ldAmount = (rs1.getInt("total")); //sum
							}
							pstmt1.close();
							pstmt1=null;
							rs1.close();
							rs1=null;
							if(llCtr==1)
							{
								if(codeString.substring(0, 1) == "-")
								{
									preGroupTotalSGrp -=  ldAmount;
								}
								else
								{
									preGroupTotalSGrp += ldAmount;
								}
							}
							else
							{
								if(signs[llCtr - 1] =="+")
								{
									preGroupTotalSGrp += ldAmount;
								}
								else if(signs[llCtr - 1] == "-")
								{
									preGroupTotalSGrp -= ldAmount;
								}
							}
					    //end of previous year
						}
						
					}	
				/***********End of sub group formula********************/	
					
				/***********Start of a/c group formula********************/
					codeString = (rs.getString("cmp_acct"));	
					if(codeString.trim()!=null && codeString.trim().length() > 0)
					{
						String tempCode="";
						double ldAmount=0.0;
						getformula_arys(codeString) ;
						
						for(int llCtr=1;llCtr<=codes.length;llCtr++)
						{
							tempCode = codes[llCtr-1];
						// start of current year 							
							if(balType.trim().equals("C"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total "
									+" FROM acctbal, accounts, acctsgrp  WHERE site_code IN ('"+siteCode + "') AND  "
									+" ( acctbal.acct_code = accounts.acct_code ) and  "
									+" ( acctsgrp.sgroup_code = accounts.sgroup_code ) "
									//+" and  ( acctbal.acct_prd		=	'"+acctPrd +"'	)   "
									+" and ( (acctbal.prd_code between '000000' and '"+prdTo+"' )  ";
									//+" AND ( acctsgrp.sgroup_code = '"+tempCode+"' ))"; 
							}
							else if(balType.trim().equals("O"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total "
									+" FROM acctbal, accounts, acctsgrp  WHERE site_code IN ('"+ siteCode + "') AND  "
									+" ( acctbal.acct_code = accounts.acct_code ) and  "
									+" ( acctsgrp.sgroup_code = accounts.sgroup_code ) and "
									+" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and  "
									+" ( (acctbal.prd_code >= '000000' and acctbal.prd_code < '"+prdFrom+"' ) AND "
									+" ( acctsgrp.sgroup_code = '"+tempCode+"' ))";
							}
							else 
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total "
									+" FROM acctbal,accounts,acctsgrp  "
								    +" WHERE site_code IN ('"+ siteCode + "') AND  "
									+" ( acctbal.acct_code = accounts.acct_code ) and  "
									+" ( acctsgrp.sgroup_code = accounts.sgroup_code ) and "
									+" ( acctbal.acct_prd		=	'"+ acctPrd +"'	) and "
									+" ( (acctbal.prd_code between '"+prdFrom+"' and '"+prdTo+"' ) AND "
									+" ( acctsgrp.sgroup_code = '"+tempCode+"' ))";
							}
							pstmt1 = conn.prepareStatement(sql);
							//pstmt1.setString(1, titleCodeSGrp);	
							//pstmt1.setString(1, formatCode);	
							rs1 = pstmt1.executeQuery();
							while (rs1.next())
							{
								ldAmount = (rs1.getInt("total")); //sum
							}
							pstmt1.close();
							pstmt1=null;
							rs1.close();
							rs1=null;
							if(llCtr==1)
							{
								if(codeString.substring(0, 1) == "-")
								{
									curGroupTotalSGrp -= ldAmount;
								}
								else
								{
									curGroupTotalSGrp += ldAmount;
								}
							}
							else
							{
								if(signs[llCtr - 1] =="+")
								{
									curGroupTotalSGrp += ldAmount;
								}
								else if(signs[llCtr - 1] == "-")
								{
									curGroupTotalSGrp -= ldAmount;
								}
							}
							System.out.println("curGroupTotalSGrp:"+curGroupTotalSGrp);
						//end of current year	
						//start  of previous year
							if(balType.trim().equals("C"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									 +" FROM acctbal,  accounts, acctsgrp "  
									 +" WHERE site_code IN ('"+ siteCode + "') AND "  
									 +" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									 +" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									 +" ( acctbal.acct_prd		=	'"+ prevPrdSGrp +"'	) and "  
									 +" ( (acctbal.prd_code between '000000' and '"+prevPrdCodeToSGrp+"' ) AND " 
									 +" ( acctsgrp.group_code = '"+tempCode+"' ) )"; 
							}
							else if(balType.trim().equals("O"))
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal,   accounts, 	acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ prevPrdSGrp +"'	) and "  
									+" ( (acctbal.prd_code >= '000000' and acctbal.prd_code < '"+prevPrdCodeFromSGrp+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							else 
							{
								sql="SELECT sum(case when accounts.typ_bal = 'D' then acctbal.dr_amt__base - acctbal.cr_amt__base else acctbal.cr_amt__base - acctbal.dr_amt__base end) as total " 
									+" FROM acctbal, accounts,acctsgrp "  
									+" WHERE site_code IN ('"+ siteCode + "') AND "  
									+" ( acctbal.acct_code 		= 	accounts.acct_code ) and "  
									+" ( acctsgrp.sgroup_code 	= 	accounts.sgroup_code ) and " 
									+" ( acctbal.acct_prd		=	'"+ prevPrdSGrp +"'	) and "  
									+" ( (acctbal.prd_code between '"+prevPrdCodeFromSGrp+"' and '"+prevPrdCodeToSGrp+"' ) "
									+" AND ( acctsgrp.group_code = '"+tempCode+"' ))";
							}
							pstmt1 = conn.prepareStatement(sql);
							//pstmt1.setString(1, titleCodeSGrp);	
							//pstmt1.setString(1, formatCode);	
							rs1 = pstmt1.executeQuery();
							while (rs1.next())
							{
								ldAmount =( rs1.getInt("total")); //sum
							}
							pstmt1.close();
							pstmt1=null;
							rs1.close();
							rs1=null;
							if(llCtr==1)
							{
								if(codeString.substring(0, 1) == "-")
								{
									preGroupTotalSGrp -=  ldAmount;
								}
								else
								{
									preGroupTotalSGrp += ldAmount;
								}
							}
							else
							{
								if(signs[llCtr - 1] =="+")
								{
									preGroupTotalSGrp += ldAmount;
								}
								else if(signs[llCtr - 1] == "-")
								{
									preGroupTotalSGrp -= ldAmount;
								}
							}
							System.out.println("preGroupTotalSGrp:"+preGroupTotalSGrp);
					    //end of previous year
						}						
					}	
					
				/***********End of a/c group formula********************/
					
					rowData.put("curr_in_val", curGroupTotalSGrp);
					rowData.put("prev_in_val", preGroupTotalSGrp);
					
					totalCurr[printlevel] += curGroupTotalSGrp;
					totalPrev[printlevel] += preGroupTotalSGrp;
					rowData.put("curr_out_val", totalCurr[printlevel]);
					rowData.put("prev_out_val", totalPrev[printlevel]);
					rawDataJson.put(count, rowData);
					count++;	
				} // end of else of llCount >0				
			}// end of while  		
   		}
   		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 :RecallDetails:getProductWiseRecallDetails:" + d.getMessage());
   				throw new ITMException(d);
   			}
   		}	
    			
    	return true;
    }
    
    @SuppressWarnings("unchecked")
   	public JSONObject getProfitBalanceSheetDetails(String sitecode, String prdfrom,String prdto,String acctprd,String formatcode,String reporttype,String dataSourceName) throws RemoteException, ITMException
   	{
       	JSONObject rawDataMnthDivWiseSalesJson = new JSONObject();
   		
   		ResultSet rs = null;
   		PreparedStatement pstmt = null;
   		Connection conn = null;
   		ConnDriver connDriver = new ConnDriver();
   		String frDate="",toDate="";
   		try
   		{
   		
   			conn = connDriver.getConnectDB(dataSourceName);
   			connDriver = null;	
   			rawDataMnthDivWiseSalesJson=generateReport(sitecode,prdfrom,prdto,acctprd,formatcode,reporttype,dataSourceName);
   			System.out.println("getProductWiseRecallDetails data :"+rawDataMnthDivWiseSalesJson);
   		}
   		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 :RecallDetails:getProductWiseRecallDetails:" + d.getMessage());
   				throw new ITMException(d);
   			}
   		}
   		return rawDataMnthDivWiseSalesJson;
   	}
    
    
    
    private int checkIntNull(String str)
	{
		if(str == null || str.trim().length() == 0 )
		{
			return 0;
		}
		else
		{
			return Integer.parseInt(str) ;
		}

	}
    
    private double checkDoubleNull(String str)
	{
		if(str == null || str.trim().length() == 0)
		{
			return 0.0;
		}
		else
		{
			return Math.round(Double.parseDouble(str)* Math.pow(10, 2)) / Math.pow(10, 2); 
		}
	}

    private void getformula_arys(String codeString)
    {
    	List<String> operatorList = new ArrayList<String>();
    	 List<String> operandList = new ArrayList<String>();
    	 StringTokenizer st = new StringTokenizer(codeString, "+-*/", true);
    	 while (st.hasMoreTokens()) {
    	    String token = st.nextToken();

    	    if ("+-/*".contains(token)) {
    	       operatorList.add(token);
    	    } else {
    	       operandList.add(token);
    	    }
    	 }
    	 codes=  operandList.toArray(new String[operandList.size()]);
    	 signs=  operatorList.toArray(new String[operatorList.size()]);
    	// codes= (String[]) operandList.toArray();
    	// signs = (String[]) operatorList.toArray();
    }
    
}
