package report_package;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import javax.faces.model.SelectItem;

import main_package.BankInfo;
import main_package.DataBaseMethods;
import main_package.HeaderBean;
import main_package.TrialInfo;



public class DataBaseFinalAccountMethod implements Serializable
{
	DataBaseMethods obj=new DataBaseMethods();
	DataBaseReportMethods dbmr=new DataBaseReportMethods();
	DecimalFormat decObj=new DecimalFormat("##.##");
	SimpleDateFormat dateObj=new SimpleDateFormat("dd/MM/yyyy");
	SimpleDateFormat sdfForDb=new SimpleDateFormat("yyyy-MM-dd");   // For Database query
	
	public String selectQueWithBranch(String query,int branchId)
	{
		if(branchId == -1)
		{
			
		}
		else
		{
			if(query.contains("where"))
			{
				query=query+" and branch_id='"+branchId+"'";
			}
			else
			{
				query=query+" where branch_id='"+branchId+"'";
			}
		}
		return query;
	}
	
	
	public ArrayList<TrialInfo> libilityList(int branchId,String sDate, String eDate, Connection conn, Date startDate, Date endDate) 
	{
		ArrayList<TrialInfo> list=new ArrayList<>();
		
		TrialInfo tt = new TrialInfo();
		
		double shareAmount = shareAmountBetweenDates(branchId,sDate,eDate,conn);
		shareAmount += shareExtraAmountBetweenDates(branchId,sDate,eDate,conn);
		tt.setParticular("Share Account");
		tt.setCategory("Liabilities");
		tt.setOpeningCr(openingShareAmount(branchId, sDate, conn));
		tt.setCredit(shareAmount);
		tt.setDebit(0.0);
		tt.setBalance(shareAmount);
		list.add(tt);
		
		
		TrialInfo tt3 = new TrialInfo();
		double ddAmount = depositParticularAmountBetweenDates(branchId,"DD",sDate,eDate,conn);
		double ddDebit = rdFdDebitAmountBetweenDates(branchId,"DD",sDate,eDate,conn);
		tt3.setParticular("DD Account");
		tt3.setCategory("Liabilities");
		tt3.setOpeningCr(openingDepositParticular(branchId, "DD", sDate, conn));
		tt3.setOpeningDr(openingRdFdDebitAmount(branchId, "DD", sDate, conn));
		tt3.setCredit(ddAmount);
		tt3.setDebit(ddDebit);
		tt3.setBalance(ddAmount-ddDebit);
		list.add(tt3);
		
		TrialInfo tt1 = new TrialInfo();
		double rdAmount = depositParticularAmountBetweenDates(branchId,"RD",sDate,eDate,conn);
		double rdDebit = rdFdDebitAmountBetweenDates(branchId,"RD",sDate,eDate,conn);
		rdDebit = Double.parseDouble(decObj.format(rdDebit));
		tt1.setParticular("RD Account");
		tt1.setCategory("Liabilities");
		tt1.setOpeningCr(openingDepositParticular(branchId, "RD", sDate, conn));
		tt1.setOpeningDr(openingRdFdDebitAmount(branchId,"RD", sDate, conn));
		tt1.setCredit(rdAmount);
		tt1.setDebit(rdDebit);
		tt1.setBalance(rdAmount-rdDebit);
		list.add(tt1);
		
		TrialInfo tt2 = new TrialInfo();
		double fdAmount = depositParticularAmountBetweenDates(branchId,"FD",sDate,eDate,conn);
		double fdDebit = rdFdDebitAmountBetweenDates(branchId,"FD",sDate,eDate,conn);
		tt2.setParticular("FD Account");
		tt2.setCategory("Liabilities");
		tt2.setOpeningCr(openingDepositParticular(branchId, "FD", sDate, conn));
		tt2.setOpeningDr(openingRdFdDebitAmount(branchId, "FD", sDate, conn));
		tt2.setCredit(fdAmount);
		tt2.setDebit(fdDebit);
		tt2.setBalance(fdAmount-fdDebit);
		list.add(tt2);
		
		TrialInfo tt7 = new TrialInfo();
		double misAmount = depositParticularAmountBetweenDates(branchId,"MIS",sDate,eDate,conn);
		double misDebit = rdFdDebitAmountBetweenDates(branchId,"MIS",sDate,eDate,conn);
		tt7.setParticular("MIS Account");
		tt7.setCategory("Liabilities");
		tt7.setCredit(misAmount);
		tt7.setDebit(misDebit);
		tt7.setBalance(misAmount-misDebit);
		list.add(tt7);
		
		TrialInfo tt4 = new TrialInfo();
		double savingDepositAmount = savingDepositAmountBetweenDates(branchId,sDate,eDate, conn);
		double savingWithdrawlAmount = savingWithdrawlAmountBetweenDates(branchId,sDate, eDate, conn);
		tt4.setParticular("Saving Account");
		tt4.setCategory("Liabilities");
		tt4.setOpeningCr(openingSavingDepositAmount(branchId, sDate, conn));
		tt4.setOpeningDr(openingSavingWithdrawlAmount(branchId, sDate, conn));
		tt4.setCredit(savingDepositAmount);
		tt4.setDebit(savingWithdrawlAmount);
		tt4.setBalance(savingDepositAmount-savingWithdrawlAmount);
		list.add(tt4);
		
		TrialInfo tt5 = new TrialInfo();
		double promotorAmount = obj.promotorAmount(branchId,"-1", sDate, eDate);
		double returnPromotorAmount = obj.returnAmountPromotorWise(branchId,"-1", sDate, eDate);
		tt5.setParticular("Promotor Account");
		tt5.setCategory("Liabilities");
		tt5.setOpeningCr(obj.openingPromotorAmount(branchId,"-1", sDate));
		tt5.setOpeningDr(obj.openingReturnAmountPromotor(branchId,"-1", sDate));
		tt5.setCredit(promotorAmount);
		tt5.setDebit(returnPromotorAmount);
		tt5.setBalance((tt5.getOpeningCr()+tt5.getCredit())-(tt5.getOpeningDr()+tt5.getDebit()));
		list.add(tt5);
		
		TrialInfo tt8 = new TrialInfo();
		tt8.setParticular("Instant Borrow");
		tt8.setCategory("Liabilities");
		tt8.setOpeningCr(dbmr.openingInstantBorrowAmount(branchId, startDate, "-1", "credit"));
		tt8.setOpeningDr(dbmr.openingInstantBorrowAmount(branchId, startDate, "-1", "debit"));
		tt8.setCredit(dbmr.instantBorrowAmount(branchId, startDate, endDate, "-1", "credit"));
		tt8.setDebit(dbmr.instantBorrowAmount(branchId, startDate, endDate, "-1", "debit"));
		tt8.setBalance((tt8.getOpeningCr()+tt8.getCredit())-(tt8.getOpeningDr()+tt8.getDebit()));
		list.add(tt8);
		
		/*ArrayList<SelectItem> libList=new DataBaseMethods().allOtherIncomeCategory("Liability_Cr", conn);
		for(SelectItem ss:libList)
		{
			System.out.println(ss.getLabel());
			TrialInfo tt6 = new TrialInfo();
			
			tt6.setParticular(ss.getLabel());
			tt6.setCredit(liabilitiesAmountTempByItem(sDate, eDate, ss.getLabel(), conn));
			tt6.setDebit(liabilitiesDebitAmountTempByItem(sDate, eDate, ss.getLabel(), conn));
			tt6.setBalance(tt6.getCredit()-tt6.getDebit());
			
			list.add(tt6);
		}*/
		
		
		
		return list;
	}
	
	public ArrayList<TrialInfo> tempAssetListForTB(int branchId,String stdt, String endt, Connection conn, Date startDate, Date endDate) 
	{
		
		ArrayList<TrialInfo> list = new ArrayList<>();
		Statement st =null;
		ResultSet rs =null;
		try
		{
			st= conn.createStatement();
			String qq = "select item,sum(amount) as amount from expense_table where expense_date>='"+stdt+"' and expense_date<='"+endt+"' and category='Fixed_Asset_Dr'"
					+ " and chequeStatus='cleared' and status='active'  group by item";
			rs= st.executeQuery(selectQueWithBranch(qq,branchId));
			while(rs.next())
			{
				TrialInfo tt = new TrialInfo();
				tt.setParticular(rs.getString("item"));
				tt.setCategory("Assets");
				tt.setOpeningDr(obj.openingFixedAssetsPurchaseBalance(branchId, rs.getString("item"), stdt, conn));
				tt.setDebit(rs.getDouble("amount"));
				tt.setCredit(0);
				tt.setBalance(rs.getDouble("amount"));
				list.add(tt);
			}
			
			double loanCrAmount = dbmr.totalLoanCreditBetweenDates(branchId,stdt,endt,conn);
			double loanDrAmount = dbmr.totalLoanGivenBetweenDates(branchId,stdt, endt,conn);
			TrialInfo tt = new TrialInfo();
			tt.setParticular("Loan Account");
			tt.setCategory("Assets");
			tt.setOpeningCr(dbmr.openingTotalLoanCredit(branchId, stdt, conn));
			tt.setOpeningDr(dbmr.openingTotalLoanGiven(branchId, stdt, conn));
			tt.setCredit(loanCrAmount);
			tt.setDebit(loanDrAmount);
			tt.setBalance(loanDrAmount-loanCrAmount);
			list.add(tt);
			
			TrialInfo tt1 = new TrialInfo();
			tt1.setParticular("FDR Account");
			tt1.setCategory("Assets");
			tt1.setOpeningCr(dbmr.openingFdrIncomeForTB(branchId, startDate, conn));
			tt1.setOpeningDr(dbmr.openingFdrExpenseForTB(branchId, startDate, conn));
			tt1.setCredit(dbmr.allFdrIncomeForTB(branchId,startDate, endDate, conn));
			tt1.setDebit(dbmr.allFdrExpenseForTB(branchId,startDate, endDate, conn));
			tt1.setBalance(loanDrAmount-loanCrAmount);
			list.add(tt1);
			
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rs!=null){
					rs.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
			
		}
		return list;
	}
	
	
	public double shareAmountBetweenDates(int branchId,String stdt, String endt, Connection conn)
	{
		//if(stdt)
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select sum(issue_share) as amount from shareholder_info where account_status='active' and office_date>='"+stdt+"' and office_date<='"+endt+"' ";
			//// // System.out.println("share : "+qq);
			 rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("amount")*obj.extractCompanyShareAmount(conn);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
	public double openingShareAmount(int branchId,String stdt, Connection conn)
	{
		//if(stdt)
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select sum(issue_share) as amount from shareholder_info where account_status='active' and office_date<'"+stdt+"' ";
			//// // System.out.println("share : "+qq);
			 rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("amount")*obj.extractCompanyShareAmount(conn);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
	public double shareExtraAmountBetweenDates(int branchId,String stdt, String endt, Connection conn)
	{
		//if(stdt)
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select sum(issue_share) as amount from share_info where account_status='active' and office_date>='"+stdt+"' and office_date<='"+endt+"' ";
			//// // System.out.println("share : "+qq);
			 rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("amount")*obj.extractCompanyShareAmount(conn);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
	private double depositParticularAmountBetweenDates(int branchId,String schemeType,String stdt, String endt,Connection conn) 
	{
		Statement st=null;
		ResultSet rr=null;

		try
		{
			st=conn.createStatement();
			String qq="select sum(amount) from payment_receipt where scheme_type='"+schemeType+"' and date>='"+stdt+"' and date<='"+endt+"' and receipt_status='approved' and chequeStatus='cleared' ";
			// // System.out.println(qq);
			rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("sum(amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
	private double openingDepositParticular(int branchId,String schemeType,String stdt,Connection conn) 
	{
		Statement st=null;
		ResultSet rr=null;

		try
		{
			st=conn.createStatement();
			String qq="select sum(amount) from payment_receipt where scheme_type='"+schemeType+"' and date<'"+stdt+"'  and receipt_status='approved' and chequeStatus='cleared' ";
			// // System.out.println(qq);
			rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("sum(amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}

	public double rdFdDebitAmountBetweenDates(int branchId,String string, String stdt, String endt, Connection conn) 
	{
		double total=0;
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select * from customer_info where scheme_type='"+string+"' ";
			rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			while(rr.next())
			{
				String userNo=rr.getString("user_no");
				total+=totalRdFdDdsDebitAmountBetweenDates(userNo,stdt,endt, conn);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return total;
	}
	
	public double openingRdFdDebitAmount(int branchId,String schemeType, String stdt, Connection conn) 
	{
		double total=0;
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select * from customer_info where scheme_type='"+schemeType+"' ";
			rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			while(rr.next())
			{
				String userNo=rr.getString("user_no");
				total+=openingTotalRdFdDdsDebitAmount(userNo,stdt, conn);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return total;
	}
	
	public double totalRdFdDdsDebitAmountBetweenDates(String user,String stdt,String endt,Connection conn)
	{
		Statement st=null; ResultSet rr=null;
		try 
		{
			 st=conn.createStatement();
			String qq="select sum(maturity_value) from returned_amount_info where user_no='"+user+"' and chequeStatus='cleared' and date>='"+stdt+"' and date<='"+endt+"' ";
			 rr=st.executeQuery(qq);
			if(rr.next())
			{
				if(rr.getString("sum(maturity_value)")==null)
				{
					return 0;
				}
				else
				{
					double depositedAmt=calculateTotalDepositInSelectedRd(user,conn);
					double amt = depositedAmt;
					amt = Double.parseDouble(decObj.format(amt));
					return amt;
				}
				
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		finally
		{
			try {
				if(st!=null)
				{
				st.close();	
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
	public double openingTotalRdFdDdsDebitAmount(String user,String stdt,Connection conn)
	{
		Statement st=null; ResultSet rr=null;
		try 
		{
			 st=conn.createStatement();
			String qq="select sum(maturity_value) from returned_amount_info where user_no='"+user+"' and chequeStatus='cleared' and date<'"+stdt+"' ";
			 rr=st.executeQuery(qq);
			if(rr.next())
			{
				if(rr.getString("sum(maturity_value)")==null)
				{
					return 0;
				}
				else
				{
					double depositedAmt=calculateTotalDepositInSelectedRd(user,conn);
					double amt = depositedAmt;
					amt = Double.parseDouble(decObj.format(amt));
					return amt;
				}
				
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		finally
		{
			try {
				if(st!=null)
				{
				st.close();	
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
	
	public double savingDepositAmountBetweenDates(int branchId,String stdt, String endt,Connection conn)
	{
		double deposit =0;
		Statement stt=null; ResultSet ss=null;
		try
        {
			  stt=conn.createStatement();
			 String query="select sum(amount) as totalAmount from customer_deposit where chequeStatus='cleared' and status='approved' and type='deposit' and date>='"+stdt+"' and  date<='"+endt+"'";
			 ss=stt.executeQuery(selectQueWithBranch(query,branchId));
			 if(ss.next())
			 {
				deposit=ss.getDouble("totalAmount");
			 }
        }
        catch (Exception e) 
        {
       	 e.printStackTrace();
        }
		 
		
		finally
		{
			try {
				if(stt!=null)
				{
				stt.close();	
				}
				if(ss!=null)
				{
					ss.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		
		//balance = deposit-withdrawl;
		deposit = Double.parseDouble(decObj.format(deposit));
		return deposit;
	}
	
	public double savingWithdrawlAmountBetweenDates(int branchId,String stdt, String endt,Connection conn)
	{
		double withdrawl =0;
		Statement stt=null; ResultSet ss=null;
		try
        {
			  stt=conn.createStatement();
			 String query="select sum(amount) as totalAmount from customer_withdrwal where chequeStatus='cleared' and status='approved' and date>='"+stdt+"' and date<='"+endt+"' ";
			 //// // System.out.println("withdr : "+query);
			  ss=stt.executeQuery(selectQueWithBranch(query,branchId));
			 if(ss.next())
			 {
				withdrawl = ss.getDouble("totalAmount");
			 }
        }
        catch (Exception e) 
        {
       	 e.printStackTrace();
        }
		
		finally
		{
			try {
				if(stt!=null)
				{
				stt.close();	
				}
				if(ss!=null)
				{
					ss.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
	
		//balance = deposit-withdrawl;
		withdrawl = Double.parseDouble(decObj.format(withdrawl));
		
		return withdrawl;
	}
	
	public double openingSavingDepositAmount(int branchId,String stdt, Connection conn)
	{
		double deposit =0;
		Statement stt=null; ResultSet ss=null;
		try
        {
			  stt=conn.createStatement();
			 String query="select sum(amount) as totalAmount from customer_deposit where chequeStatus='cleared' and status='approved' and type='deposit' "
			 		+ "and date<'"+stdt+"' ";
			 ss=stt.executeQuery(selectQueWithBranch(query,branchId));
			 if(ss.next())
			 {
				deposit=ss.getDouble("totalAmount");
			 }
        }
        catch (Exception e) 
        {
       	 e.printStackTrace();
        }
		 
		
		finally
		{
			try {
				if(stt!=null)
				{
				stt.close();	
				}
				if(ss!=null)
				{
					ss.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		
		//balance = deposit-withdrawl;
		deposit = Double.parseDouble(decObj.format(deposit));
		return deposit;
	}
	
	public double openingSavingWithdrawlAmount(int branchId,String stdt, Connection conn)
	{
		double withdrawl =0;
		Statement stt=null; ResultSet ss=null;
		try
        {
			  stt=conn.createStatement();
			 String query="select sum(amount) as totalAmount from customer_withdrwal where chequeStatus='cleared' and status='approved' and date<'"+stdt+"' ";
			 //// // System.out.println("withdr : "+query);
			  ss=stt.executeQuery(selectQueWithBranch(query,branchId));
			 if(ss.next())
			 {
				withdrawl = ss.getDouble("totalAmount");
			 }
        }
        catch (Exception e) 
        {
       	 e.printStackTrace();
        }
		
		finally
		{
			try {
				if(stt!=null)
				{
				stt.close();	
				}
				if(ss!=null)
				{
					ss.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
	
		//balance = deposit-withdrawl;
		withdrawl = Double.parseDouble(decObj.format(withdrawl));
		
		return withdrawl;
	}
	
	private double liabilitiesAmountTempByItem(int branchId,String stdt, String endt, String string, Connection conn) 
	{
		Statement st=null;ResultSet rr=null;
		try 
		{
			st = conn.createStatement();
			String qq="select sum(amount) from income_table where category='Liability_Cr' and item='"+string+"' and income_date>='"+stdt+"' and income_date<='"+endt+"'"
					+ " and chequeStatus='cleared' and status='active' ";
			 rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("sum(amount)");
			}
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
				st.close();	
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}

	
	private double liabilitiesDebitAmountTempByItem(int branchId,String stdt, String endt, String string, Connection conn) 
	{
		Statement st=null;ResultSet rr=null;
		try 
		{
			st = conn.createStatement();
			String qq="select sum(amount) from expense_table where category='Liability_Dr' and item='"+string+"' and expense_date>='"+stdt+"' and expense_date<='"+endt+"'"
					+ " and chequeStatus='cleared' and status='active' ";
			 rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("sum(amount)");
			}
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		finally
		{
			try {
				if(st!=null)
				{
				st.close();	
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
	public int calculateTotalDepositInSelectedRd(String user_no,Connection conn) 
	{
		Statement st=null;
		ResultSet rr=null;
		try
		{
			 st=conn.createStatement();
			String qq="select sum(amount) from payment_receipt where user_no='"+user_no+"' and receipt_status='approved'";
			 rr=st.executeQuery(qq);
			if(rr.next())
			{
				return rr.getInt("sum(amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();//fileCreation(e.getMessage());
		}

		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
	
	public ArrayList<TrialInfo> tempExpenseListForTB(int branchId,String stdt, String endt, Connection conn) 
	{  
		Statement st=null;
		ResultSet rs=null;
		ArrayList<TrialInfo> list = new ArrayList<>();
		try
		{
			st= conn.createStatement();
			  ArrayList<SelectItem> otherList=obj.groupHeadSelectList(conn, "Expense");
			  for(SelectItem ss: otherList)
			  {
				  String item=String.valueOf(ss.getValue());
				  String qq = "select item,sum(amount) as amount from expense_table where expense_date>='"+stdt+"' and expense_date<='"+endt+"' and category='Other_Dr' and"
							+ " chequeStatus='cleared' and status='active' and item='"+item+"' ";
				  
				  
				
					if(branchId!=-1)
					{
						qq+=" and branch_id="+branchId+" ";
					}
					//System.out.println(qq);
					rs= st.executeQuery(qq);
					while(rs.next())
					{
						TrialInfo tt = new TrialInfo();
						tt.setParticular(item);
						
						System.out.println(item+rs.getDouble("amount"));
						tt.setCategory("Expense");
						tt.setOpeningDr(Double.parseDouble(decObj.format(obj.openingExpenseAmount(branchId, item, stdt, conn))));
						tt.setDebit(rs.getDouble("amount"));
						tt.setCredit(0);
						
						list.add(tt);
					}
			  }
			
			double returnedInterest = interestPaidBetweenDates(branchId,stdt,endt,conn);
			TrialInfo bb = new TrialInfo();
			bb.setParticular("Interest Paid");
			bb.setCategory("Expense");
			bb.setOpeningDr(openingDepositInterestPaid(branchId, stdt, conn));
			bb.setDebit(returnedInterest);
			bb.setCredit(0);
			list.add(bb);
			
			double sal = paidSalaryBetweenDates(branchId,stdt,endt, conn);
			TrialInfo sa = new TrialInfo();
			sa.setParticular("Salary Account");
			sa.setCategory("Expense");
			sa.setOpeningDr(openingPaidSalary(branchId, stdt, conn));
			sa.setDebit(sal);
			list.add(sa);
			
			double misAmt = paidMISInterestBetweenDates(branchId,stdt,endt, conn);
			TrialInfo mis = new TrialInfo();
			mis.setParticular("MIS Interest Paid");
			mis.setCategory("Expense");
			mis.setDebit(misAmt);
			list.add(mis);
			
			
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
				st.close();	
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return list;
	}

	public double interestPaidBetweenDates(int branchId,String stdt, String endt, Connection conn)
	{
		double paid = 0,returned = 0,intrst=0;
		Statement st=null;
		ResultSet rs=null;
		try
		{
			st=conn.createStatement();
			String qq="select * from returned_amount_info where date>='"+stdt+"' and date<='"+endt+"' and chequeStatus='cleared'";
			rs = st.executeQuery(selectQueWithBranch(qq,branchId));
			while(rs.next())
			{
				paid+=paidAmountAsRDFDDDS(rs.getString("user_no"),conn);
				returned+=rs.getDouble("maturity_value");
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		intrst=returned-paid;
		intrst = Double.parseDouble(decObj.format(intrst));
		return intrst;
	}
	
	public double openingDepositInterestPaid(int branchId,String stdt, Connection conn)
	{
		double paid = 0,returned = 0,intrst=0;
		Statement st=null;
		ResultSet rs=null;
		try
		{
			st=conn.createStatement();
			String qq="select * from returned_amount_info where date<'"+stdt+"' and chequeStatus='cleared'";
			rs = st.executeQuery(selectQueWithBranch(qq,branchId));
			while(rs.next())
			{
				paid+=paidAmountAsRDFDDDS(rs.getString("user_no"),conn);
				returned+=rs.getDouble("maturity_value");
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		intrst=returned-paid;
		intrst = Double.parseDouble(decObj.format(intrst));
		return intrst;
	}
	
	public double paidSalaryBetweenDates(int branchId,String start, String end,Connection conn) 
	{
		Statement st=null; ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select sum(amount) from salary_ledger where chequeStatus='cleared' and payDate>='"+start+"' and payDate<='"+end+"' and type='Debit'";
			//System.out.println(qq);
			rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("sum(amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
				st.close();	
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
	public double openingPaidSalary(int branchId,String start, Connection conn) 
	{
		Statement st=null; ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select sum(amount) from salary_ledger where chequeStatus='cleared' and payDate<'"+start+"' and type='Debit'";
			//System.out.println(qq);
			rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("sum(amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
				st.close();	
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
	
	public double paidAmountAsRDFDDDS(String user,Connection conn)
	{
		Statement st=null;
		ResultSet rr=null;
		try
		{
			 st=conn.createStatement();
			String qq="select sum(amount) from payment_receipt where user_no='"+user+"' and receipt_status='approved' and chequeStatus='cleared'";
			rr=st.executeQuery(qq);
			if(rr.next())
			{
				return rr.getDouble("sum(amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
	public double paidMISInterestBetweenDates(int branchId,String start, String end,Connection conn) 
	{
		Statement st=null; ResultSet rr=null;
		try
		{
			 st=conn.createStatement();
			String qq="select sum(credit_amount) from payment_receipt where chequeStatus='cleared' and date>='"+start+"' and date<='"+end+"' and receipt_status='approved' ";
			 rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("sum(credit_amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
				st.close();	
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}

	
	public ArrayList<TrialInfo> tempIncomeListForTB(int branchId,String stdt, String endt, Connection conn, Date startDate, Date endDate) 
	{
		ArrayList<TrialInfo> list = new ArrayList<>();
		int i=0;
		Statement st=null;
		ResultSet rs=null;
		try
		{
			st = conn.createStatement();
			String qq = "select item,sum(amount) as amount from income_table where income_date>='"+stdt+"' and income_date<='"+endt+"' and status='active' and"
					+ " chequeStatus='cleared' and category='Other_Cr' ";
			if(branchId!=-1)
			{
				qq+=" and branch_id="+branchId+" ";
			}
			qq+=" group by item";
			//System.out.println(qq);
			rs= st.executeQuery(qq);
			while(rs.next())
			{
				TrialInfo tt = new TrialInfo();
				tt.setParticular(rs.getString("item"));
				tt.setCategory("Income");
				tt.setOpeningDr(0);
				tt.setOpeningCr(obj.openingIncomeBalance(branchId, rs.getString("item"), stdt,conn));
				tt.setCredit(rs.getDouble("amount"));
				tt.setDebit(0);
				
				list.add(tt);
				
				i++;
			}
			
			
			double loanInterest = interestBetweenDates(branchId,stdt,endt,conn);
			TrialInfo aa = new TrialInfo();
			
			aa.setParticular("Interest Account");
			aa.setCategory("Income");
			aa.setOpeningCr(openingLoanInterest(branchId, stdt, conn));	
			aa.setOpeningDr(0);
			aa.setCredit(loanInterest);
			aa.setDebit(0);
			
			list.add(aa);
			
			TrialInfo bb = new TrialInfo();
			
			bb.setParticular("Applicant Fee(RD/FD)");
			bb.setCategory("Income");
			bb.setOpeningCr(obj.totalApplicantFeeForOpening(branchId, startDate, conn));
			bb.setOpeningDr(0);
			bb.setCredit(obj.totalApplicantFee(branchId,startDate,endDate,conn));
			bb.setDebit(0);
			
			list.add(bb);
			
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		finally
		{
			try {
				if(st!=null)
				{
					st.close();	
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		
		return list;
	}

	public double openingLoanInterest(int branchId,String stdt, Connection conn)
	{
		Statement st=null;
		ResultSet rs=null;
		try
		{
			st=conn.createStatement();
			String qq="select sum(interestAmount) from loanpayment where pay_date<'"+stdt+"' and receipt_status='approved' and chequeStatus='cleared'";
			//// // System.out.println("query : "+qq);
			rs = st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rs.next())
			{
				return Double.parseDouble(decObj.format(rs.getDouble("sum(interestAmount)")));
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
	public double interestBetweenDates(int branchId,String stdt, String endt,Connection conn)
	{
		Statement st=null;
		ResultSet rs=null;
		try
		{
			st=conn.createStatement();
			String qq="select sum(interestAmount) from loanpayment where pay_date>='"+stdt+"' and pay_date<='"+endt+"' and receipt_status='approved' and chequeStatus='cleared'";
			//// // System.out.println("query : "+qq);
			rs = st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rs.next())
			{
				return Double.parseDouble(decObj.format(rs.getDouble("sum(interestAmount)")));
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}

	public ArrayList<TrialInfo> cashAndBankList(int branchId,String sDate, String eDate, ArrayList<TrialInfo> assList, Connection conn, Date endDate) throws ParseException 
	{
		DataBaseMethods db=new DataBaseMethods();
		ArrayList<BankInfo> bankList=db.allBankListOfCompany(conn);
		
//		SimpleDateFormat sdfForDb=new SimpleDateFormat("yyyy-MM-dd");
//		endDate.setDate(endDate.getDate()+1);
//		String nDate=sdfForDb.format(endDate);  //// comment on 07 June 2024
		
		

		Date tempdt=endDate;
		Calendar cal = Calendar.getInstance();
        cal.setTime(tempdt);
        cal.add(Calendar.DATE, 1);
        tempdt = cal.getTime();
        String tempDtStr=sdfForDb.format(tempdt.getTime());
		
		for(BankInfo bb: bankList)
		{
			double opBank=0;
			TrialInfo tt=new TrialInfo();
			
	        
			tt.setParticular(bb.getBank_name());
			tt.setCategory("Assets");
			double amt=obj.closingBalanceOfBankOut(branchId,bb.getId(), tempDtStr,conn, "tb");
			double amt2=obj.closingBalanceOfBankOut(branchId,bb.getId(), sDate,conn, "tb");
			
			double amt3=obj.closingBalanceOfBankIn(branchId,bb.getId(), tempDtStr,conn, "tb");
			double amt4=obj.closingBalanceOfBankIn(branchId,bb.getId(), sDate,conn, "tb");
			double amt5=amt4-amt2;
			
			double temp=new DataBaseReportMethods().companyIssueShareCapitalOpeningBalance(branchId,sDate,bb.getId(),conn);
			
			double val=temp+(amt4-amt2);
			if(val>0)
			{
				tt.setOpeningDr(Math.abs(val));
				//tt.setOpeningDr(Double.parseDouble(decObj.format(tt.getOpeningDr())));
			}
			else
			{
				tt.setOpeningCr(Math.abs(val));
				//tt.setOpeningCr(Double.parseDouble(decObj.format(tt.getOpeningCr())));
			}
	
			double tempBetween=new DataBaseReportMethods().companyIssueShareCapital(branchId,sDate,bb.getId(),conn, eDate);
			tempBetween+=new DataBaseReportMethods().companyIssueShareCapitalFromShareInfoTable(branchId,sDate,bb.getId(),conn, eDate);
			
			tt.setDebit(tempBetween+(amt3-amt4));
			tt.setCredit(amt-amt2);
		
			assList.add(tt);
		}
		
		TrialInfo tt=new TrialInfo();
	
		double totalCashIn=closingCashInBalance(branchId,tempDtStr,conn, sDate);  // nDate --> tempDtStr 07-06-2024
		double totalCashOut=closingCashOutBalance(branchId,tempDtStr,conn, sDate); // nDate --> tempDtStr 07-06-2024
		double opCash=totalCashIn-totalCashOut;
		tt.setParticular("Cash In Hand");
		tt.setCategory("Assets");
		if(opCash<0)
		{
			tt.setCredit(Math.abs(opCash));
		}
		else
		{
			tt.setDebit(opCash);
		}
		
		 // below five line comment @9-june-24
//		double shareCashOpening=new DataBaseReportMethods().companyIssueShareCapitalOpeningBalance(branchId,sDate,"cash",conn);
		tt.setOpeningCr(Double.parseDouble(decObj.format(obj.closingCashOutBalance(branchId, sDate, conn, "TB"))));
		tt.setOpeningDr(Double.parseDouble(decObj.format(obj.closingCashInBalance(branchId, sDate, conn, "TB"))));
//		tt.setCredit(Double.parseDouble(decObj.format(totalCashOut)));
//		tt.setDebit(Double.parseDouble(decObj.format(totalCashIn)));
		
		tt.setBalance(Math.abs(opCash));
		assList.add(tt);

		
		
		return assList;
	}
	
	
	public double closingCashInBalance(int branchId,String eDate, Connection con, String ssDate) throws ParseException 
	{
		
		//String ssDate="2018-04-01";
		String bankName="N/A";
		double totalIn = 0;
		double a=dbmr.loanEmiPaymentForOpeningBalance(branchId,eDate, ssDate,con);
		double b=dbmr.rdPayListForOpeningBalance(branchId,eDate, ssDate,con);
		double c=dbmr.depositPayForOpeningBalance(branchId,eDate, ssDate,con);
		double h=dbmr.appFeeForOpeningBalance(branchId,eDate, ssDate,con);
		double i=dbmr.extraIncomeForOpeningBalance(branchId,eDate, ssDate,con);
		double j=dbmr.fdIncomeForOpeningBalance(branchId,eDate, ssDate,con);
		double k=dbmr.bankTransferAmountForOpeningBalance(branchId,eDate, bankName, ssDate,con);
		double l=dbmr.PromotorAmountForOpeningBalance(branchId,eDate, bankName, ssDate,con);
		double m=dbmr.instantBorrowForOpeningBalance(branchId,eDate, "credit", "cash", ssDate,con);
		double n=dbmr.instantLoanForOpeningBalance(branchId,eDate, "debit", "cash",ssDate,con);
		
		totalIn=a+b+c+h+i+j+k+l+m+n;

		double x=dbmr.bankToBankAmountForOpeningBalance(branchId,eDate, ssDate,con);
		double y=dbmr.bankToBankTransferForOpeningBalance(branchId,eDate, ssDate,con);
		double z=dbmr.shareAmountOpeningBalance(branchId,eDate, ssDate, "cash",con);
		double za=dbmr.shareAmountOpeningBalanceFromOtherShareInfo(branchId,eDate, ssDate, "cash",con);
		totalIn+=x+y+z+za;
		
		totalIn=Double.parseDouble(decObj.format(totalIn));
	
		return totalIn;
	}
	
	
	public double closingCashOutBalance(int branchId,String eDate, Connection con, String ssDate) throws ParseException 
	{
		//String ssDate="2018-04-01";
		String bankName="N/A";
		double totalOut=0;
		
		
		double a=dbmr.loanAmountForOpeningBalance(branchId,eDate, ssDate,con);
		double b=dbmr.withdrawAmountForOpeningBalance(branchId,eDate, ssDate,con);
		double c=dbmr.returnAmountForOpeningBalance(branchId,eDate, ssDate,con);
		double d=dbmr.expenseForOpeningBalance(branchId,eDate, ssDate,con);
		double e=dbmr.fdExpenseForOpeningBalance(branchId,eDate, bankName, ssDate,con);
		double f=dbmr.transferAmountForOpeningBalance(branchId,eDate, bankName, ssDate,con);
		double g=dbmr.returnPromotorAmountForOpeningBalance(branchId,eDate, bankName, ssDate,con);
		double j=dbmr.returnDSACommForOpeningBalance(branchId,eDate, ssDate,con);
		double k=dbmr.salaryPayoutForOpeningBalance(branchId,eDate,ssDate,con);
		double l=dbmr.tdsForOpeningBalance(branchId,eDate,ssDate,con);
		
		totalOut=a+b+c+d+e+f+g+j+k+l;
		
		double y=dbmr.companyAmountToBankForOpeningBalance(branchId,eDate, ssDate,con);
		totalOut+=y;
		totalOut=Double.parseDouble(decObj.format(totalOut));
		
		return totalOut;
	}

	public double closingBalanceOfBankIn(int branchId,String sDate, String bankName, Connection conn, String searchReport) 
	{
		DataBaseReportMethods dbmr=new DataBaseReportMethods();
		double totalIn = 0, totalOut = 0, openingBalance=0;
		HeaderBean hh=new HeaderBean();
		String ssDate="";
		if(searchReport.equalsIgnoreCase("TB"))
		{
			ssDate="2010-04-01";	
		}
		else
		{
			ssDate=hh.getStartFinYear()+"-04-01";
		}
		double a=dbmr.loanEmiPayForBankOpeningBalance(branchId,sDate, bankName, ssDate,conn);
		double b=dbmr.allPayReceiptForBankOpeningBalance(sDate, bankName, ssDate,conn);
		double c=dbmr.depositForBankOpeningBalance(sDate, bankName, ssDate,conn);
		double d=dbmr.allIncomePayForBankOpeningBalance(branchId,sDate, bankName, ssDate,conn);
		double e=dbmr.allFixedDepositForBankOpeningBalance(branchId,sDate, bankName, ssDate,conn);
		double f=dbmr.amountTransferForBankOpeningBalance(branchId,sDate, bankName, ssDate,conn);
		double g=dbmr.otherDepositForBankOpeningBalance(branchId,sDate, bankName, ssDate,conn);
		double h=dbmr.bankToBankTransferForBankOpeningBalance(branchId,sDate, bankName, ssDate,conn);
		
		double i=dbmr.instantBorrowForOpeningBalance(branchId,sDate, "credit", bankName, ssDate,conn);
		double j=dbmr.instantLoanForOpeningBalance(branchId,sDate, "debit", bankName, ssDate,conn);
		double k=dbmr.PromotorAmountForOpeningBalance(branchId,sDate, bankName, ssDate,conn);
		
		totalIn+=a+b+c+d+e+g+h+i+j+k;
		totalIn=Double.parseDouble(decObj.format(totalIn));
		
		return totalIn;
	}
	
	public double closingBalanceOfBankOut(int branchId,String bankName, String sDate, Connection conn, String searchReport) 
	{
		DataBaseReportMethods dbmr=new DataBaseReportMethods();;
		HeaderBean hh=new HeaderBean();
		String ssDate="";
		if(searchReport.equalsIgnoreCase("TB"))
		{
			ssDate="2010-04-01";	
		}
		else
		{
			ssDate=hh.getStartFinYear()+"-04-01";
		}
		double totalIn = 0, totalOut = 0, openingBalance=0;
		double m=dbmr.loanAmountForBankOpeningBalance(branchId,sDate, bankName, ssDate,conn);
		double n=dbmr.withdrawForBankOpeningBalance(sDate, bankName, ssDate,conn);
		double o=dbmr.returnAmountForBankOpeningBalance(sDate, bankName, ssDate,conn);
		double p=dbmr.allExpensePayForBankOpeningBalance(branchId,sDate, bankName, ssDate,conn);
		double q=dbmr.allFdExpenseForBankOpeningBalance(branchId,sDate, bankName, ssDate,conn);
		double r=dbmr.amountTransferToCompanyForBankOpeningBalance(branchId,sDate, bankName, ssDate,conn);
		double s=dbmr.instantBorrowForOpeningBalance(branchId,sDate, "debit", bankName, ssDate,conn);
		double t=dbmr.instantLoanForOpeningBalance(branchId,sDate, "credit", bankName, ssDate,conn);
		double u=dbmr.returnPromotorAmountForOpeningBalance(branchId,sDate, bankName, ssDate,conn);
		double v=dbmr.returnDSACommForBankOpeningBalance(sDate, bankName, ssDate,conn);
		double w=dbmr.salaryPayoutForBankOpeningBalance(sDate, bankName, ssDate,conn, branchId);
		double x=dbmr.allTdsPayForBankOpeningBalance(sDate, bankName, ssDate,conn);
		
		totalOut+=m+n+o+p+q+r+s+t+u+v+w+x;
		
		totalOut=Double.parseDouble(decObj.format(totalOut));
		
		return totalOut;
	}
	
	
	
	public ArrayList<TrialInfo> createBalanceSheetList(int branchId,String selectedSession, Connection conn) 
	{
		ArrayList<TrialInfo> bsList=new ArrayList<>();
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select * from balance_sheet where financial_year='"+selectedSession+"' ";
			System.out.println(qq);
			rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			while(rr.next())
			{
				TrialInfo uu=new TrialInfo();
				
				uu.setLiabilitiesHead(rr.getString("liabilities_head"));
				uu.setDebit(rr.getDouble("dr_amount"));
				uu.setOpeningDr(rr.getDouble("dr_amount"));
				uu.setAssetsHead(rr.getString("assets_head"));
				uu.setCredit(rr.getDouble("cr_amount"));
				uu.setOpeningCr(rr.getDouble("cr_amount"));
				
				bsList.add(uu);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
				st.close();	
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return bsList;
	}
	
	
}
