package group_head_package;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import javax.faces.model.SelectItem;

import main_package.DataBaseMethods;
import main_package.DataBaseConnection;

public class DataBaseIncomeExpense implements Serializable
{	
	DecimalFormat decObj=new DecimalFormat("##.##");
	SimpleDateFormat dateObj=new SimpleDateFormat("dd/MM/yyyy");
	DataBaseMethods db=new DataBaseMethods();
	

	   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+"'";
				}
			}
			
			System.out.println(query);
			return query;
		}

	
	
	public int addCategory(String name, String type, Connection conn) 
	{
		 Statement stt=null;
		 
		int i=0;
		try
		{
		   stt=conn.createStatement();
		    String query="insert into group_head(group_head_name, group_head_type, group_type) values('"+name+"','"+type+"', 'Un-Reserved')";
		    i=stt.executeUpdate(query);
		    if(i>0)
		    {
		    	db.dataInsertInQueryTable(query, conn);
		    }
		}
		catch (Exception e) 
		{
           e.printStackTrace();                  
		}
		finally
		{
			try {
				if(stt!=null)
				{
					stt.close();
				}
				
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return i;
	}	
	
	public ArrayList<CategoryInfo> allCategoryList(Connection conn) 
	{
		int i=1;
		ArrayList<CategoryInfo> list=new ArrayList<CategoryInfo>();
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String query="select * from group_head";
			rr=st.executeQuery(query);
			while(rr.next())
			{
				CategoryInfo ll=new CategoryInfo();
				ll.setsNo(i++);
				ll.setcName(rr.getString("group_head_name"));
				ll.setcType(rr.getString("group_head_type"));
				ll.setGroupType(rr.getString("group_type"));
				
				list.add(ll);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				
				}
			} 
			catch (Exception e2)
			{
				// TODO: handle exception
			}
		}
		return list;
	}
	

	public boolean categoryDuplicacy(String name, String type, Connection conn) 
	{
		Statement st=null;
		ResultSet rr=null;
		boolean duplicate=false;
		try
		{
			st=conn.createStatement();
			String query="select * from group_head where group_head_name='"+name+"' and group_head_type='"+type+"' ";
			rr=st.executeQuery(query);
			if(rr.next())
			{
				duplicate=true;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return duplicate;
	}
	
	public int addIncome(int branchId,Date incomeDate,String category,String paymentMode,String amount,String chequeNo, String bankBranch, 
	String bankName,Date chequeDate,String description, String itemName, double oldAmount, String submittedBankName,
	String returnNoOfItem, double discount, String loanNo, String loanType, String voucherNo, String faId, int itemRate,String entryStatus, String dsaCode) 
	{
		int i=0;
		String status="";
		if(paymentMode.equalsIgnoreCase("Cash") || paymentMode.equalsIgnoreCase("Transfer"))
		{
			status="cleared";
		}
		else
		{
			status="uncleared";
		}
	    
		Timestamp cDate=null;
		if(chequeDate!=null)
		{
			cDate=new Timestamp(chequeDate.getTime());
		}
		String query="insert into income_table(income_date,category,payment_mode,amount,chq_dd_no,chq_dd_date,bank_name,bank_branch,"
		+ " description,item,old_amount,transfer_to_bank,chequeStatus, no_of_item,loan_no,loan_type,discount, voucher_no, fa_purchase_id, item_rate, status,branch_id,dsa_code) "
		+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		System.out.println(query+"knk");
        try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			PreparedStatement st=conn.prepareStatement(query);
			st.setTimestamp(1,new Timestamp(incomeDate.getTime()));
			st.setString(2, category);
			st.setString(3, paymentMode);
			st.setString(4, amount);
			st.setString(5, chequeNo);
			st.setTimestamp(6,cDate);
			st.setString(7,bankName);
			st.setString(8,bankBranch);
			st.setString(9, description);
			st.setString(10, itemName);
			st.setDouble(11, oldAmount);
			st.setString(12, submittedBankName);
			st.setString(13, "cleared");
			st.setString(14, returnNoOfItem);
			st.setString(15, loanNo);
			st.setString(16, loanType);
			st.setDouble(17, discount);
			st.setString(18, voucherNo);
			st.setString(19, faId);
			st.setInt(20, itemRate);
			st.setString(21, entryStatus);
			st.setInt(22, branchId);
			st.setString(23, dsaCode);
			i=st.executeUpdate();
			 if(i>0)
			    {
			    db.dataInsertInQueryTable(String.valueOf(st), conn);
			    }
		}
		catch (Exception e)
		{
			 e.printStackTrace();
		}
		return i;
	}

	public int addExpense(int branchId,Date expenseDate,String category,String paymentMode,String amount,String chequeNo, String bankBranch, 
	String bankName,Date chequeDate,String description, String itemName, String noOfItem, String voucherNo, int itemRate, String otherTableId, String loanNo, String loanType) 
	{
		int i=0;
		String status="";
		if(paymentMode.equalsIgnoreCase("Cash") || paymentMode.equalsIgnoreCase("Transfer"))
		{
			status="cleared";
			bankName="N/A";
		}
		else if(paymentMode.equalsIgnoreCase("Bank Deduction"))
		{
			status="cleared";
		}
		else
		{
			status="uncleared";
		}
		
			
		
		Timestamp cDate=null;
		if(chequeDate!=null)
		{
			cDate=new Timestamp(chequeDate.getTime());
		}
		
		String query="insert into expense_table(expense_date,category,payment_mode,amount,chq_dd_no,chq_dd_date,bank_name,bank_branch,"
		+ " description,item,chequeStatus, no_of_item,voucher_no, item_rate, other_table_id,branch_id,loan_no,loan_type)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			PreparedStatement st=conn.prepareStatement(query);
			st.setTimestamp(1,new Timestamp(expenseDate.getTime()));
			st.setString(2, category);
			st.setString(3, paymentMode);
			st.setString(4, amount);
			st.setString(5, chequeNo);
			st.setTimestamp(6,cDate);
			st.setString(7,bankName);
			st.setString(8,bankBranch);
			st.setString(9, description);
			st.setString(10, itemName);
			st.setString(11, status);
			st.setString(12, noOfItem);
			st.setString(13, voucherNo);
			st.setInt(14, itemRate);
			st.setString(15, otherTableId);
			st.setInt(16, branchId);
			st.setString(17, loanNo);
			st.setString(18, loanType);
			i=st.executeUpdate();
			 if(i>0)
			    {
			    db.dataInsertInQueryTable(String.valueOf(st), conn);
			    }
		}
		catch (Exception e)
		{
			 e.printStackTrace();
		}
		return i;
	}
			
	
	public ArrayList<IncomeList> allIncomeList(int branchId,Date startDate,Date endDate, Connection conn)
	{
		int i=1;
		Statement stt=null;
		ResultSet rr=null;
		ArrayList<IncomeList> list=new ArrayList<>();
		try
		{
			String query="select * from income_table where chequeStatus='cleared' and status='active' ";
			 stt=conn.createStatement();	
			if(startDate!=null && endDate!=null)
			{
				String sDate=String.valueOf(new Timestamp(startDate.getTime())).substring(0,10);
				String eDate=String.valueOf(new Timestamp(endDate.getTime())).substring(0,10);
				query+=" and income_date between '"+sDate+"' and '"+eDate+"'"; 
			}
			query+=" and branch_id='"+branchId+"'  order by income_date desc";
			 rr=stt.executeQuery(query);
			while(rr.next())
			{
				  IncomeList ll=new IncomeList();
				  ll.setsNo(i++);
				  ll.setId(rr.getString("id"));
				  ll.setStrIncomeDate(dateObj.format(rr.getDate("income_date")));
				  ll.setIncomeDate(rr.getDate("income_date"));
				  //ll.setCategory(new DataBaseMethodsIncome_Expense().categoryNameById(rr.getString("category"),"Income"));
				  ll.setCategory(rr.getString("category"));
				  //ll.setItem_name(rr.getString("item"));
				  if(rr.getString("loan_no")==null && rr.getString("loan_no").trim().equalsIgnoreCase(""))
				  {
					  ll.setItem_name(rr.getString("item") +" - "+ rr.getString("loan_no"));
				  }
				  else
				  {
					  ll.setItem_name(rr.getString("item") +" - "+ rr.getString("loan_no")+ " - "+db.applicantNameByShareHolderNo(db.shareHolderNoByLoanNo(rr.getString("loan_no")), conn));
				  }
				  ll.setCategoryId(rr.getString("category"));
				  ll.setAmount(rr.getDouble("amount"));
				  ll.setOldAmount(rr.getDouble("old_amount"));
				  ll.setDescription(rr.getString("description"));
				  ll.setPaymentMode(rr.getString("payment_mode"));
				  ll.setChequeNo(rr.getString("chq_dd_no"));
				  ll.setBankBranch(rr.getString("bank_branch"));
				  ll.setBankName(rr.getString("bank_name"));
				  ll.setTransferBank(rr.getString("transfer_to_bank"));
				  ll.setTransferToBankName(db.bankNameById(rr.getString("transfer_to_bank")));
				  ll.setChqDate(rr.getTimestamp("chq_dd_date"));
				  ll.setDisable(true);
				  if(rr.getTimestamp("chq_dd_date")!=null)
				  {
					  ll.setStrChqDate(dateObj.format(rr.getTimestamp("chq_dd_date")));
				  }
				  else
				  {
					  ll.setStrChqDate("");
				  }
				  
				  if(rr.getString("voucher_no")==null)
				  {
					  ll.setVoucherNo("0");
				  }
				  else 
				  {
					  ll.setVoucherNo(rr.getString("voucher_no"));
				  }
				  ll.setDisableForFreezingData(db.checkDataFreezing(branchId,DataBaseMethods.selectedSessionDetail(), conn));
				  
				  list.add(ll);
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	public ArrayList<IncomeList> allExpenseAndAssetsList(int branchId,Date startDate,Date endDate, String searchType, String searchCategory, Connection conn)
	{ 
		System.out.println("branchId=" + branchId + ", startDate=" + startDate + ", endDate=" + endDate 
			    + ", searchType=" + searchType + ", searchCategory=" + searchCategory);

		
		
		int i=1;
		ArrayList<IncomeList> list=new ArrayList<>();
        
		Statement stt=null;
		ResultSet rr=null;
		try
		{
			String query="select * from expense_table where status='active' and chequeStatus='cleared' and category='"+searchType+"' ";
			 stt=conn.createStatement();	
			if(startDate!=null && endDate!=null)
			{
				String sDate=String.valueOf(new Timestamp(startDate.getTime())).substring(0,10);
				String eDate=String.valueOf(new Timestamp(endDate.getTime())).substring(0,10);
				query+=" and expense_date >= '"+sDate+"' and expense_date<='"+eDate+"'"; 
			}
			if(!searchCategory.equalsIgnoreCase("all"))
			{
				query+=" and item='"+searchCategory+"' ";
			}
			query+=" and branch_id='"+branchId+"' order by expense_date";
			 rr=stt.executeQuery(query);
			while(rr.next())
			{
				  IncomeList ll=new IncomeList();
				  ll.setsNo(i++);
				  ll.setId(rr.getString("id"));
				  ll.setStrIncomeDate(dateObj.format(rr.getDate("expense_date")));
				  ll.setIncomeDate(rr.getDate("expense_date"));
				  /*if(rr.getString("category").equals("Fixed_Asset"))
				  {
					  ll.setCategory(rr.getString("category"));
				  }
				  else
				  {
					  ll.setCategory(new DataBaseMethodsIncome_Expense().categoryNameById(rr.getString("category"),"Expense")); 
				  }*/
				 // ll.setCategoryId(rr.getString("category"));
				  ll.setCategory(rr.getString("category"));
				  ll.setAmount(rr.getDouble("amount"));
				  ll.setItem_name(rr.getString("item"));
				  ll.setDescription(rr.getString("description"));
				  ll.setPaymentMode(rr.getString("payment_mode"));
				  ll.setChequeNo(rr.getString("chq_dd_no"));
				  if(rr.getString("voucher_no")==null)
				  {
					  ll.setVoucherNo("0");
				  }
				  else 
				  {
					  ll.setVoucherNo(rr.getString("voucher_no"));
				  }
				  ll.setBankBranch(rr.getString("bank_branch"));
				  ll.setBankId(rr.getString("bank_name"));
				  ll.setBankName(db.bankNameById(rr.getString("bank_name")));
				  ll.setChqDate(rr.getTimestamp("chq_dd_date"));
				  if(rr.getTimestamp("chq_dd_date")!=null)
				  {
					  ll.setStrChqDate(dateObj.format(rr.getTimestamp("chq_dd_date")));
				  }
				  else
				  {
					  ll.setStrChqDate("");
				  }
				  ll.setStrEntryTime(rr.getString("entry_date"));
				  ll.setDisable(true);
				  ll.setDisableForFreezingData(db.checkDataFreezing(branchId,DataBaseMethods.selectedSessionDetail(), conn));
				  
				  list.add(ll);
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}

finally
		{
			try {
				if(stt!=null)
				{
					stt.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
	
		return list;
	}

	public String categoryNameById(String id, String type) 
	{
		String categoryName="";
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select * from income_expanse_category where id='"+id+"' and type in ('"+type+"','both')";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				categoryName=rr.getString("category_name");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return categoryName;
	}
	
	public int deleteIncome(IncomeList selectedIncome) 
	{
		int i=0;
        try(Connection conn=DataBaseConnection.JAVAConnection())
        {
			Statement stt=conn.createStatement();
			String query="delete from income_table where id='"+selectedIncome.getId()+"'";
			i=stt.executeUpdate(query);
			 if(i>0)
			    {
			    db.dataInsertInQueryTable(query, conn);
			    }
		} 
        catch (Exception e)
        {
        	e.printStackTrace();
		} 		
		return i;
	}
	
	public int deleteExpense(IncomeList selectedIncome) 
	{
		int i=0;
        try(Connection conn=DataBaseConnection.JAVAConnection())
        {
			Statement stt=conn.createStatement();
			String query="update expense_table set status='deleted' where id='"+selectedIncome.getId()+"'";
			i=stt.executeUpdate(query);
			 if(i>0)
			    {
			    db.dataInsertInQueryTable(query, conn);
			    }
		} 
        catch (Exception e)
        {
        	e.printStackTrace();
		} 		
		return i;
	}

	public int addFixedDepositIncome(int branchId,Date incomeDate,
			String fixedDepositAmount, String fixedDepositBank,
			String fixedDepositAccNo, String fixedDepositRate,
			String fixedDepositTime, String fixedDepositAddress, String fdr_no, double oldAmount, String paymentMode,
			String bankName, String bankBranch, String chequeNo, Date chequeDate, String submittedBankName) 
	{
		int i=0;
		String status="";
		if(paymentMode.equals("Cash"))
		{
			status="cleared";
		}
		else
		{
			status="uncleared";
		}
		
		Timestamp cDate=null;
		if(chequeDate!=null)
		{
			cDate=new Timestamp(chequeDate.getTime());
		}
		
		String query="insert into fixed_deposit_income(amount, bank, account_no, rate, time, address, date,fdr_no,old_amount,"
				+ " paymentMode, bankName, bankBranch, chequeNo, chequeDate, transfer_to_bank, chequeStatus, branch_id)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			PreparedStatement st=conn.prepareStatement(query);
			st.setString(1, fixedDepositAmount);
			st.setString(2, fixedDepositBank);
			st.setString(3, fixedDepositAccNo);
			st.setString(4, fixedDepositRate);
			st.setString(5, fixedDepositTime);
			st.setString(6,fixedDepositAddress);
			st.setTimestamp(7,new Timestamp(incomeDate.getTime()));
			st.setString(8, fdr_no);
			st.setDouble(9, oldAmount);
			
			st.setString(10, paymentMode);
			st.setString(11,bankName);
			st.setString(12,bankBranch);
			st.setString(13, chequeNo);
			st.setTimestamp(14,cDate);
			st.setString(15, submittedBankName);
			st.setString(16, status);
			st.setInt(17, branchId);
			i=st.executeUpdate();
			 if(i>0)
			    {
			    db.dataInsertInQueryTable(String.valueOf(st), conn);
			    }
		}
		catch (Exception e)
		{
			 e.printStackTrace();
		}
		return i;
	}

	public ArrayList<IncomeList> allFixedDepositIncomeList(int branchId,Date startDate,
			Date endDate, Connection conn) 
	{Statement stt=null;ResultSet rr=null;
		int i=1;
		ArrayList<IncomeList> list=new ArrayList<>();
		try
		{
			String query="select * from fixed_deposit_income where chequeStatus='cleared' ";
		stt=conn.createStatement();	
			if(startDate!=null && endDate!=null)
			{
				String sDate=String.valueOf(new Timestamp(startDate.getTime())).substring(0,10);
				String eDate=String.valueOf(new Timestamp(endDate.getTime())).substring(0,10);
				query+=" and date between '"+sDate+"' and '"+eDate+"'"; 
			}
			query+=" and branch_id='"+branchId+"' order by date";
			rr=stt.executeQuery(query);
			while(rr.next())
			{
				  IncomeList ll=new IncomeList();
				  ll.setsNo(i++);
				  ll.setId(rr.getString("id"));
				  ll.setStrIncomeDate(dateObj.format(rr.getDate("date")));
				  ll.setIncomeDate(rr.getDate("date"));
				  ll.setFdr_no(rr.getString("fdr_no"));
				  ll.setOldAmount(rr.getDouble("old_amount"));
				  ll.setAmount(rr.getDouble("amount"));
				  ll.setAddress(rr.getString("address"));
				  ll.setAccountNo(rr.getString("account_no"));
				  ll.setRate(rr.getString("rate"));
				  ll.setTime(rr.getString("time"));
				  ll.setBankName(rr.getString("bank"));
				  ll.setTransferBank(rr.getString("transfer_to_bank"));
				  ll.setTransferToBankName(db.bankNameById(rr.getString("transfer_to_bank")));
				  ll.setDisable(true);
				  ll.setDisableForFreezingData(db.checkDataFreezing(branchId,DataBaseMethods.selectedSessionDetail(), conn));

				  list.add(ll);
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(stt!=null)
				{
					stt.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return list;
	}

	public int deleteFixedDepositIncome(IncomeList selectedIncome, Connection conn)
	{
		int i=0;Statement stt=null;
        try
        {
			stt=conn.createStatement();
			String query="delete from fixed_deposit_income where id='"+selectedIncome.getId()+"'";
			i=stt.executeUpdate(query);
			 if(i>0)
			    {
			    db.dataInsertInQueryTable(query, conn);
			    }
		} 
        catch (Exception e)
        {
        	e.printStackTrace();
		} 	finally
		{
			try {
				if(stt!=null)
				{
					stt.close();
				}
			
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}	
		return i;
	}

	public int addFixedDepositExpense(int branchId, Date expenseDate, String fixedDepositAmount, 
			String fixedDepositBank, String fixedDepositAccNo, String fixedDepositRate, String fixedDepositTime, 
			String fixedDepositAddress, String itemName, String paymentMode, String chequeNo, String bankBranch,
			String bankName, Date chequeDate, Connection conn) 
	{
		int i=0;
		PreparedStatement st=null;
		String status="";
		Timestamp chdate = null;
		if (chequeDate != null) 
		{
			chdate = new Timestamp(chequeDate.getTime());
		}
		
		if(paymentMode.equalsIgnoreCase("Cash"))
		{
			status="cleared";
			bankName="N/A";
		}
		else if(paymentMode.equalsIgnoreCase("Bank Deduction"))
		{
			status="cleared";
		}
		else
		{
			status="uncleared";
		}
		
		String query="insert into fixed_deposit_expense(amount, bank, account_no, rate, time, address, date, fdr_no, paymentMode, bankName,"
				+ " bankBranch, chequeNo, chequeDate, chequeStatus, branch_id)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try
		{
			st=conn.prepareStatement(query);
			st.setString(1, fixedDepositAmount);
			st.setString(2, fixedDepositBank);
			st.setString(3, fixedDepositAccNo);
			st.setString(4, fixedDepositRate);
			st.setString(5, fixedDepositTime);
			st.setString(6,fixedDepositAddress);
			st.setTimestamp(7,new Timestamp(expenseDate.getTime()));
			st.setString(8, itemName);
			st.setString(9, paymentMode);
			st.setString(10, bankName);
			st.setString(11, bankBranch);
			st.setString(12, chequeNo);
			st.setTimestamp(13,chdate);
			st.setString(14, status);
			st.setInt(15, branchId);
			i=st.executeUpdate();
			 if(i>0)
			    {
			    db.dataInsertInQueryTable(String.valueOf(st), conn);
			    }
		}
		catch (Exception e)
		{
			 e.printStackTrace();
		}
        finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return i;

	}

	public ArrayList<SelectItem> obtainFDExpenseList(int branchId) 
	{
		ArrayList<SelectItem> list=new ArrayList<>();
		try(Connection con=DataBaseConnection.JAVAConnection();)
		{
			Statement st=con.createStatement();
			String qq="select * from fixed_deposit_expense where status='active' and chequeStatus='cleared'";
			ResultSet rs=st.executeQuery(selectQueWithBranch(qq,branchId));
			while(rs.next())
			{
				SelectItem ii=new SelectItem();
				ii.setLabel(rs.getString("bank")+"-"+rs.getString("fdr_no"));
				ii.setValue(rs.getString("id"));
				 list.add(ii);
				
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		return list;
	}

	public ArrayList<IncomeList> obtainFDExpenseInfo(String selectedReturn)
	{
		ArrayList<IncomeList> list=new ArrayList<>();
		try(Connection con=DataBaseConnection.JAVAConnection();)
		{
			Statement st=con.createStatement();
			String qq="select * from fixed_deposit_expense where id='"+selectedReturn+"' and status='active'";
			ResultSet rs=st.executeQuery(qq);
			while(rs.next())
			{
				IncomeList ii=new IncomeList();
				ii.setId(rs.getString("id"));
				ii.setFdr_no(rs.getString("fdr_no"));
				ii.setAmount(rs.getDouble("amount"));
				ii.setBankName(rs.getString("bank"));
				ii.setAccountNo(rs.getString("account_no"));
				ii.setRate(rs.getString("rate"));
				ii.setTime(rs.getString("time"));
				ii.setAddress(rs.getString("address"));
				ii.setExpenseDate(rs.getDate("date"));
				ii.setStrExpDate(dateObj.format(rs.getDate("date").getTime()));
				
				 list.add(ii);
				
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		return list;
	}

	public ArrayList<SelectItem> obtainFAExpenseList(int branchId) 
	{
		ArrayList<SelectItem> list=new ArrayList<>();
		try(Connection con=DataBaseConnection.JAVAConnection();)
		{
			Statement st=con.createStatement();
			String qq="select DISTINCT item from expense_table where category='Fixed_Asset_Dr' and status='active' and chequeStatus='cleared'";
			ResultSet rs=st.executeQuery(selectQueWithBranch(qq,branchId));
			while(rs.next())
			{
				SelectItem ii=new SelectItem();
				ii.setLabel(rs.getString("item"));
				ii.setValue(rs.getString("item"));
				 list.add(ii);
				
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		return list;
	}

	public ArrayList<IncomeList> obtainFAExpenseInfo(int branchId,String selectedReturn) 
	{
		ArrayList<IncomeList> list=new ArrayList<>();
		try(Connection con=DataBaseConnection.JAVAConnection();)
		{
			Statement st=con.createStatement();
			String qq="select *, sum(amount), sum(no_of_item) from expense_table where item='"+selectedReturn+"' and status='active'";
			ResultSet rs=st.executeQuery(selectQueWithBranch(qq,branchId));
			while(rs.next())
			{
				IncomeList ii=new IncomeList();
				ii.setAmount(rs.getDouble("amount"));
				ii.setItem_name(rs.getString("item"));
				if(rs.getString("no_of_item")==null)
				{
					ii.setNoOfItem("0");
				}
				else
				{
					ii.setNoOfItem(rs.getString("sum(no_of_item)"));
				}
				
				list.add(ii);
				
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		return list;

	}

	public int updateFixedDepositExpenseStatus(String selectedReturn,
			String fdr_no) 
	{
		int i=0;
		try(Connection con=DataBaseConnection.JAVAConnection();)
		{
			Statement st=con.createStatement();
			String qq="update fixed_deposit_expense set status='inactive' where id='"+selectedReturn+"' and fdr_no='"+fdr_no+"'";
			int x=st.executeUpdate(qq);
			if(x>=1)
		    {
		    db.dataInsertInQueryTable(qq, con);
				i++;
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return i;
	}
	
	public int updateExpenseTableStatus(String selectedReturn,
			String itemName) 
	{
		int i=0;
		try(Connection con=DataBaseConnection.JAVAConnection();)
		{
			Statement st=con.createStatement();
			String qq="update expense_table set status='inactive' where id='"+selectedReturn+"' and item='"+itemName+"'";
			int x=st.executeUpdate(qq);
			if(x>=1)
		    {
		    db.dataInsertInQueryTable(qq, con);
				i++;
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return i;
	}

	public ArrayList<IncomeList> allFixedDepositExpenseList(int branchId,Date startDate,
			Date endDate, Connection conn) 
	{
		int i=1;

		Statement stt=null;
		ResultSet rr=null;
		ArrayList<IncomeList> list=new ArrayList<>();
		try
		{
			String query="select * from fixed_deposit_expense ";
			 stt=conn.createStatement();	
			if(startDate!=null && endDate!=null)
			{
				String sDate=String.valueOf(new Timestamp(startDate.getTime())).substring(0,10);
				String eDate=String.valueOf(new Timestamp(endDate.getTime())).substring(0,10);
				query+=" where date between '"+sDate+"' and '"+eDate+"'"; 
			}
			query=selectQueWithBranch(query,branchId)+" order by date";
			 rr=stt.executeQuery(query);
			while(rr.next())
			{
				  IncomeList ll=new IncomeList();
				  ll.setsNo(i++);
				  ll.setId(rr.getString("id"));
				  ll.setStrExpDate(dateObj.format(rr.getDate("date")));
				  ll.setExpenseDate(rr.getDate("date"));
				  ll.setFdr_no(rr.getString("fdr_no"));
				  ll.setAmount(rr.getDouble("amount"));
				  ll.setAddress(rr.getString("address"));
				  ll.setAccountNo(rr.getString("account_no"));
				  ll.setRate(rr.getString("rate"));
				  ll.setTime(rr.getString("time"));
				  ll.setBankName(rr.getString("bank"));
				  ll.setPaymentMode(rr.getString("paymentMode"));
				  ll.setChequeNo(rr.getString("chequeNo"));
				  ll.setBankBranch(rr.getString("bankBranch"));
				  ll.setBankNameChq(db.bankNameById(rr.getString("bankName")));
				  ll.setChqDate(rr.getTimestamp("chequeDate"));
				  if(rr.getTimestamp("chequeDate")!=null)
				  {
					  ll.setStrChqDate(dateObj.format(rr.getTimestamp("chequeDate")));
				  }
				  else
				  {
					  ll.setStrChqDate("");
				  }
				  ll.setDisable(true);
				  ll.setDisableForFreezingData(db.checkDataFreezing(branchId,DataBaseMethods.selectedSessionDetail(), conn));

				  list.add(ll);
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}


			finally
		{
			try {
				if(stt!=null)
				{
					stt.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return list;

	}

	public int deleteFixedDepositExpense(IncomeList selectedExpense) 
	{
		int i=0;
        try(Connection conn=DataBaseConnection.JAVAConnection())
        {
			Statement stt=conn.createStatement();
			String query="update fixed_deposit_Expense set status='inactive' where id='"+selectedExpense.getId()+"'";
			i=stt.executeUpdate(query);
			 if(i>0)
			    {
			    db.dataInsertInQueryTable(query, conn);
			    }
		} 
        catch (Exception e)
        {
        	e.printStackTrace();
		} 		
		return i;
	}

	public ArrayList<SelectItem> allBankList(Connection conn) 
	{
		ArrayList<SelectItem> bankList=new ArrayList<>();
		Statement stt=null;
		ResultSet rr=null;
		try
		{
		   stt=conn.createStatement();
		   String query="select * from bank_table";
		   rr=stt.executeQuery(query);
		   while(rr.next())
		   {
			   SelectItem info=new SelectItem();
			   info.setLabel(rr.getString("bank_name")+","+rr.getString("bank_branch")+","+rr.getString("acc_no"));
			   info.setValue(rr.getString("id"));
			   bankList.add(info);
		   }
		} 
		catch (Exception e) 
		{
			 e.printStackTrace();
		}
		finally
		{
			try {
				if(stt!=null)
				{
					stt.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return bankList;
	}

	public int addCurrentAssetBankDebit(int branchId,String bankName, String bankBranch,
			String accNo, String address, double amount, Date date,
			String description, String paymentMode, String chequeNo, Date chequeDate, String bankNameCheque,String type, Connection conn)
	{
		PreparedStatement st=null;
		ResultSet rr=null;

		Timestamp cDate=null;
		if(chequeDate!=null)
		{
			cDate=new Timestamp(chequeDate.getTime());
		}
		int i=0;
		String query="insert into debit_current_asset_bank(bank_name, bank_branch, acc_no, address, amount, date, description,"
				+ " type,paymentMode, chequeNo, chequeDate, transfer_to_bank,branch_id)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try
		{
			 st=conn.prepareStatement(query);
			
			st.setString(1, bankName);
			st.setString(2, bankBranch);
			st.setString(3, accNo);
			st.setString(4, address);
			st.setDouble(5, amount);
			st.setTimestamp(6,new Timestamp(date.getTime()));
			st.setString(7, description);
			st.setString(8, type);
			st.setString(9, paymentMode);
			st.setString(10, chequeNo);
			st.setTimestamp(11, cDate);
			st.setString(12, bankNameCheque);
			st.setInt(13, branchId);
			
			i=st.executeUpdate();
			 if(i>0)
			    {
			    db.dataInsertInQueryTable(String.valueOf(st), 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 i;

	}

	public int addCurrentAssetBankCredit(int branchId,String bankName, String bankBranch,
			String accNo, String address, double amount, Date date,
			String description, String paymentMode, String chequeNo,
			Date chequeDate, String submittedBankName) 
	{
		Timestamp cDate=null;
		if(chequeDate!=null)
		{
			cDate=new Timestamp(chequeDate.getTime());
		}
		int i=0;
		String query="insert into debit_current_asset_bank(bank_name, bank_branch, acc_no, address, amount, date, description,"
				+ " type, paymentMode, chequeNo, chequeDate, transfer_to_bank,branch_id)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			PreparedStatement st=conn.prepareStatement(query);
			
			st.setString(1, bankName);
			st.setString(2, bankBranch);
			st.setString(3, accNo);
			st.setString(4, address);
			st.setDouble(5, amount);
			st.setTimestamp(6,new Timestamp(date.getTime()));
			st.setString(7, description);
			st.setString(8, "withdrawl");
			st.setString(9, paymentMode);
			st.setString(10, chequeNo);
			st.setTimestamp(11, cDate);
			st.setString(12, submittedBankName);
			st.setInt(13, branchId);

			i=st.executeUpdate();
			 if(i>0)
			    {
			    db.dataInsertInQueryTable(String.valueOf(st), conn);
			    }
		}
		catch (Exception e)
		{
			 e.printStackTrace();
		}
		return i;

	}

	public int addFDRInterest(int branchId,Date incomeDate, String fdr_id, String amount,
			String mode) 
	{
		int i=0;
		Timestamp date=new Timestamp(incomeDate.getTime());
		
		try(Connection con=DataBaseConnection.JAVAConnection();)
		{
			Statement st=con.createStatement();
			String qq="insert into fdr_interest(fdr, interest, paymentMode, date, branch_id) values('"+fdr_id+"','"+amount+"','"+mode+"','"+date+"','"+branchId+"')";
			i=st.executeUpdate(qq);
			 if(i>0)
			    {
			    db.dataInsertInQueryTable(qq, con);
			    }
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return i;
	}

	public void updateStatusOfFdrInterest(int branchId,String selectedReturn)
	{
		try(Connection con=DataBaseConnection.JAVAConnection();)
		{
			Statement st=con.createStatement();
			String qq="update fdr_interest set status='inactive' where fdr='"+selectedReturn+"'";
		int i=	st.executeUpdate(selectQueWithBranch(qq,branchId));
		 if(i>0)
		    {
		    db.dataInsertInQueryTable(qq, con);
		    }
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
	}
	
	public double noOfItemSale(int branchId,String itemName) 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select sum(no_of_item) from income_table where item='"+itemName+"' ";
			ResultSet rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("sum(no_of_item)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}

	public int addPromotorCapital(int branchId,Date incomeDate, String string,
			String paymentMode, double amount, String chequeNo,
			String bankBranch, String bankName, Date chequeDate,
			String description, String submittedBankName, String selectedPromotor, String entryType, Connection conn) 
	{
		int i=0;
		String status="";
		PreparedStatement st=null;
		if(paymentMode.equals("Cash"))
		{
			status="cleared";
		}
		else
		{
			status="uncleared";
		}
		
		Timestamp cDate=null;
		if(chequeDate!=null)
		{
			cDate=new Timestamp(chequeDate.getTime());
		}
		String query="insert into promotors_capital(income_date,category,payment_mode,amount,chq_dd_no,chq_dd_date,bank_name,bank_branch,"
		+ " description,transfer_to_bank,chequeStatus, promotor_id, type, branch_id)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try
		{
			st=conn.prepareStatement(query);
			st.setTimestamp(1,new Timestamp(incomeDate.getTime()));
			st.setString(2, string);
			st.setString(3, paymentMode);
			st.setDouble(4, amount);
			st.setString(5, chequeNo);
			st.setTimestamp(6,cDate);
			st.setString(7,bankName);
			st.setString(8,bankBranch);
			st.setString(9, description);
			st.setString(10, submittedBankName);
			st.setString(11, status);
			st.setString(12, selectedPromotor);
			st.setString(13, entryType);
			st.setInt(14, branchId);
			i=st.executeUpdate();
			if(i>0)
			{
				 db.dataInsertInQueryTable(String.valueOf(st), conn);
			}
		}
		catch (Exception e)
		{
			 e.printStackTrace();
		}
        finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return i;
	}		
	
	public int returnPromotorCapital(int branchId,Date incomeDate, String string,
			String paymentMode, double amount, String chequeNo,
			String bankBranch, String bankName, Date chequeDate,
			String description, String submittedBankName, String selectedPromotor) 
	{
		int i=0;
		String status="";
		if(paymentMode.equalsIgnoreCase("Cash"))
		{
			status="cleared";
		}
		else
		{
			status="uncleared";
		}
		
		Timestamp cDate=null;
		if(chequeDate!=null)
		{
			cDate=new Timestamp(chequeDate.getTime());
		}
		String query="insert into return_promotors_capital(income_date,category,payment_mode,amount,chq_dd_no,chq_dd_date,bank_name,bank_branch,"
		+ " description,transfer_to_bank,chequeStatus, promotor_id, branch_id)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			PreparedStatement st=conn.prepareStatement(query);
			st.setTimestamp(1,new Timestamp(incomeDate.getTime()));
			st.setString(2, string);
			st.setString(3, paymentMode);
			st.setDouble(4, amount);
			st.setString(5, chequeNo);
			st.setTimestamp(6,cDate);
			st.setString(7,bankName);
			st.setString(8,bankBranch);
			st.setString(9, description);
			st.setString(10, submittedBankName);
			st.setString(11, status);
			st.setString(12, selectedPromotor);
			st.setInt(13, branchId);
			i=st.executeUpdate();
			 if(i>0)
			    {
			    db.dataInsertInQueryTable(String.valueOf(st), conn);
			    }
		}
		catch (Exception e)
		{
			 e.printStackTrace();
		}
		return i;
	}		
}	