package other_package;

import java.io.Serializable;
import java.sql.Connection;
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 main_package.DataBaseMethods;
import main_package.DataBaseConnection;
import main_package.InstallmentInfo;
import main_package.LoanInfo;
import main_package.UserInfo;

public class DataBaseMethodsOther implements Serializable
{
	DecimalFormat df=new DecimalFormat("##.##");
	SimpleDateFormat sdfForDb=new SimpleDateFormat("yyyy-MM-dd"); 
	SimpleDateFormat sdf=new SimpleDateFormat("dd-MM-yyyy"); 
    DataBaseMethods dbm=new DataBaseMethods();
	SimpleDateFormat sf=new SimpleDateFormat("dd/MM/yyyy");

    
    
    public ArrayList<ReportInfo> paidEmiloanReportForLedger(String loanNo,String shareHolderNo,String type, Connection conn)
    {
        ArrayList<ReportInfo> list=new ArrayList<>();
        Statement st=null;
        ResultSet rr=null;
        try
        {
            st=conn.createStatement();
            String query="";
           
            if(type.equalsIgnoreCase("groupLoan"))
            {
                loanNo=new DataBaseMethods().memberLoanNoForGroupLoan(loanNo,shareHolderNo);
                query="select * from grouploan where sub_loan_no='"+loanNo+"' and teamLeader='"+shareHolderNo+"' and status='Approve' ";
            }
            else
            {
            	query="select * from "+type+" where loanNo='"+loanNo+"' and shareHolderNo='"+shareHolderNo+"' and status='Approve' ";
            }
            
            rr=st.executeQuery(query);
            if(rr.next())
            {
                int terms=rr.getInt("terms");
                int frequency=rr.getInt("frequency");
                double emi=rr.getDouble("emi");
                double discount=rr.getDouble("discount");
                int j=365/frequency;
                Date installDate=null;
                double amount=(terms*rr.getDouble("emi"))-rr.getDouble("loanAmount");
                double totalCr=rr.getDouble("loanAmount")+amount;
                if(rr.getDate("emi_due_date")==null)
                {
                    installDate=rr.getDate("approvalDate");
                }
                else
                {
                    installDate=rr.getDate("emi_due_date");
                    if(frequency==12)
                    {
                        installDate.setMonth(installDate.getMonth()-1);
                    }
                    else
                    {
                        installDate.setDate(installDate.getDate()-j);
                    }
                }
               
                double paidAmount=0;
                for (int i = 1; i<= terms; i++)
                {                   	
                	ReportInfo ll=new ReportInfo();
                    //System.out.println("paid amt : "+paidAmount);
                    Date dt=installDate;
                    if(rr.getDate("emi_due_date")==null)
                    {
                        dt.setDate(dt.getDate()+j);
                    }
                    else
                    {
                        if(frequency==12)
                        {
                            dt.setMonth(dt.getMonth()+1);
                        }
                        else
                        {
                            dt.setDate(dt.getDate()+j);
                        }
                    }
                    
                    ll.setsNo(i);
                    ll.setDateStr(sdf.format(dt));
                    ll.setStatus("Installment");
                    ll.setReference("EMI # "+ i+"/"+terms);
                    ll.setAmount(emi);
                    ll.setDebitAmount(emi);

                    totalCr-=paidAmount;
                    ll.setBalance(totalCr); 
                    
                    
                    SimpleDateFormat dd=sdf;
                    Date ss=dd.parse(ll.getDateStr());
                    ll.setDate(ss);
                    
                    if(ss.before(new Date()))
                    {
                    	 list.add(ll);
                    }
                }
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return list;
    }
    
    public ArrayList<ReportInfo> chequeBounceReport(String loanNo)
	{
		ArrayList<ReportInfo> list = new ArrayList<ReportInfo>();
		int count=0;
		
		try (Connection con = DataBaseConnection.JAVAConnection();) 
		{
			
			Statement st = con.createStatement();
//			String qq = "select pay_date,pay_mode,chq_no,receipt_no,sum(cheque_bounce_charge) from  loanpayment where loanNo='"+loanNo+"' and receipt_status='not approved'"
//					+ " and chequeStatus='returned' group by pay_date";
			
			String qq = "select pay_date, pay_mode, chq_no, receipt_no, sum(cheque_bounce_charge)  "
			        + "from loanpayment "
			        + "where loanNo='"+loanNo+"' "
			        + "and receipt_status='approved' "
			        + "and chequeStatus='cleared' "
			        + "group by pay_date, pay_mode, chq_no, receipt_no";
			
			System.out.println("qq is : book bounce charge"+qq);
			ResultSet rs = st.executeQuery(qq);
			
			while (rs.next())
			{   
				
				ReportInfo obj= new ReportInfo();
				
				obj.setAmount(rs.getDouble("sum(cheque_bounce_charge)"));
				obj.setDebitAmount(obj.getAmount());
				obj.setStatus(rs.getString("pay_mode")+" Bounce Charge");
				obj.setDate(rs.getDate("pay_date"));
				obj.setReference(rs.getString("chq_no"));
				obj.setDateStr(sdf.format(rs.getDate("pay_date")));
				obj.setsNo(++count);
				obj.setReceiptNo(rs.getString("receipt_no"));
				
				list.add(obj);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
    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;
   	}
	
	// conbine list of all Loan
	public ArrayList<LoanInfo> allLoanList(String shareId)
	{
		int i=1;
		ArrayList<LoanInfo> list=new ArrayList<LoanInfo>();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			Statement st1=conn.createStatement();
			Statement st2=conn.createStatement();
			Statement st3=conn.createStatement();
			Statement st4=conn.createStatement();
			String query="select * from personalloan INNER JOIN shareholder_info on personalloan.shareholderno=shareholder_info.shareholderno where personalloan.shareholderno='"+shareId+"' and personalloan.status='Approve' ";
			ResultSet rr=st.executeQuery(query);
			while(rr.next())
			{
				LoanInfo ll=new LoanInfo();
				commonLoanDetails(rr, ll, "Personal Loan", "personalloan");
				ll.setSno(i++);
				ll.setShareHolderNo(shareId);
				ll.setReason(rr.getString("reason"));
			
				list.add(ll);
			}
			
			query="select * from propertyloan INNER JOIN shareholder_info on propertyloan.shareholderno=shareholder_info.shareholderno where propertyloan.shareholderno='"+shareId+"'";
			ResultSet rr1=st1.executeQuery(query);;
			while(rr1.next())
			{
				LoanInfo ll=new LoanInfo();
				commonLoanDetails(rr1, ll, "Property Loan", "propertyloan");
				ll.setSno(i++);
				ll.setShareHolderNo(shareId);
				ll.setCategory(rr1.getString("category"));
				ll.setRegistryNo(rr1.getString("registryNo"));
				ll.setVerified(rr1.getString("verified"));
				ll.setPatta(rr1.getString("patta"));
				ll.setAuthorityPatta(rr1.getString("authorityPatta"));
				ll.setJamabandi(rr1.getString("jamabandi"));
				list.add(ll);
			}
			
			query="select * from vechicleloan INNER JOIN shareholder_info on vechicleloan.shareholderno=shareholder_info.shareholderno where vechicleloan.shareholderno='"+shareId+"'";
			ResultSet rr2=st2.executeQuery(query);
			while(rr2.next())
			{   
				LoanInfo ll=new LoanInfo();
				commonLoanDetails(rr2, ll, "Vehicle Loan", "vechicleloan");
				ll.setSno(i++);
				ll.setShareHolderNo(shareId);
				ll.setCategory(rr2.getString("category"));
				ll.setVechiclename(rr2.getString("vechileName"));
				ll.setVechileModel(rr2.getString("model"));
				ll.setEngineNo(rr2.getString("engineno"));
				ll.setMacNo(rr2.getString("macNo"));
				list.add(ll);
			}
			
			query="select * from grouploan INNER JOIN shareholder_info on grouploan.teamLeader=shareholder_info.shareholderno where grouploan.teamLeader='"+shareId+"' and grouploan.status='Approve'  ";
			ResultSet rr3=st3.executeQuery(query);
			while(rr3.next())
			{
				LoanInfo ll=new LoanInfo();
				commonLoanDetails(rr3, ll, "Group Loan", "grouploan");
				ll.setSno(i++);
				ll.setShareHolderNo(shareId);
				ll.setCenter(rr3.getString("center"));
				ll.setMembers(rr3.getString("members"));
				list.add(ll);
			}
			
			query="select * from secure_loan INNER JOIN shareholder_info on secure_loan.shareholderno=shareholder_info.shareholderno where secure_loan.shareholderno='"+shareId+"' and secure_loan.status='Approve' ";
			//System.out.println(query);
			ResultSet rr4=st4.executeQuery(query);
			while(rr4.next())
			{
				LoanInfo ll=new LoanInfo();
				commonLoanDetails(rr4, ll, "Secure Loan", "secure_loan");
				ll.setSno(i++);
				ll.setShareHolderNo(shareId);
				ll.setReason(rr4.getString("reason"));
				list.add(ll);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	// set comman Detail from all Loan table
	public void commonLoanDetails(ResultSet rr,LoanInfo info,String loanType,String tblName)
	{
		try
		{   
			String loanNo=rr.getString("loanNo");
			info.setMobile(rr.getString("shareholder_info.mobile"));
			info.setApplicantName(rr.getString("shareholder_info.first_applicantName"));
			info.setId(rr.getString(tblName+".id"));
			info.setLoanAmount(rr.getString("loanAmount"));
			info.setLoanInterest(rr.getString(tblName+".interest"));
			info.setLoanTerm(rr.getString(tblName+".terms"));
			info.setInstallAmount(rr.getDouble(tblName+".emi")+rr.getDouble(tblName+".extra_amount"));
			double intrAmt=(Double.parseDouble(info.getLoanTerm())*info.getInstallAmount()) - Double.parseDouble(info.getLoanAmount());
			info.setInterestAmount(new DecimalFormat("##.##").format(intrAmt));
			info.setLoanType(loanType);
			info.setRequestDate(rr.getDate("requestDate"));
			info.setStrRequestDate(new SimpleDateFormat("dd/MM/yyyy").format(rr.getDate("requestDate")));
			info.setApprovalDate(rr.getDate("approvalDate"));
			if(rr.getDate("approvalDate")!=null)
			{
				info.setStrApprovalDate(new SimpleDateFormat("dd/MM/yyyy").format(rr.getDate("approvalDate")));
			}
			if(!tblName.equals("grouploan"))
			{
				info.setFirstGuarantor(rr.getString("firstguarantor"));
				info.setSecondGuarantor(rr.getString("secondguarantor"));
			}
			info.setIdProof(rr.getString("idproof"));
			info.setLoanNo(rr.getString("loanNo"));
			if(loanType.equalsIgnoreCase("Group Loan") || loanType.equalsIgnoreCase("grouploan"))
			{
				info.setSubLoanNo(rr.getString("sub_loan_no"));
				loanNo=rr.getString("sub_loan_no");
			}
			info.setFileCharge(rr.getString("fileCharge"));
			info.setRd_fdNo(rr.getString("RD_FD_no"));
			info.setStatus(rr.getString("status"));
			info.setPaidStatus(rr.getString("paidStatus"));
			if(rr.getString("restructure_status").equalsIgnoreCase("yes"))
			{
				info.setPaidStatus("paid");
			}
			if(info.getPaidStatus().equalsIgnoreCase("paid"))
			{
				info.setDisable(true);
				if(checkLoanSettlementInExpenseTable(loanNo, tblName)==true)  // develop on 19 april 2023 : 
				{
					info.setDisBtn(true);
				}
			}
			else
			{
				info.setDisBtn(true);
			}
			info.setFrequency(String.valueOf(rr.getInt("frequency")));
			info.setTableName(tblName);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public boolean checkLoanSettlementInExpenseTable(String loanNo, String tblName) 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select * from expense_table where item='Loan Settlement' and status='active' and loan_no='"+loanNo+"' and loan_type='"+tblName+"'  ";
			//System.out.println(qq);
			ResultSet rr=st.executeQuery(qq);
			if(rr.next())
			{
				return true;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return false;
	}
	
	// List of DD/RD/FD Account of a Particular share holder 
	public ArrayList<UserInfo> allRd_FDList(String shareId) 
	{
		ArrayList<UserInfo> list = new ArrayList<UserInfo>();
		try (Connection con = DataBaseConnection.JAVAConnection();) 
		{
			int i=0;
			Statement st = con.createStatement();
			String qq = "select * from customer_info where shareHolderNo='"+shareId+"'";
			ResultSet rs = st.executeQuery(qq);
			while (rs.next())
			{
				i=i+1;
				String hno=rs.getString("shareHolderNo");
				UserInfo uu = new UserInfo();
				uu=new DataBaseMethods().userInfoByShareHolderNo(hno, con);
				uu.setSno(i);
         		uu.setId(rs.getString("id"));
         		uu.setOffice_application_no(rs.getString("office_application_no"));
    			uu.setOffice_date(rs.getDate("office_date"));
    			uu.setStrOffcDate(new SimpleDateFormat("dd/MM/yyyy").format(rs.getDate("office_date")));
				uu.setUser_no(rs.getString("user_no"));
				uu.setScheme_type(rs.getString("scheme_type"));
				uu.setPlan_no(rs.getString("plan_no"));
				uu.setTerms(rs.getInt("terms"));
				uu.setFrequency(rs.getInt("frequency"));
				uu.setApplicant_fees(rs.getDouble("applicant_fees"));
				uu.setInstallment_amount(rs.getDouble("installment_amount"));
				uu.setInterest(rs.getDouble("interest"));
				uu.setMaturity_value(Double.valueOf(df.format(rs.getDouble("maturity_value"))));
				uu.setMaturity_date(rs.getDate("maturity_date"));
				uu.setStrMatDate(new SimpleDateFormat("dd/MM/yyyy").format(rs.getDate("maturity_date")));
				uu.setStatus(rs.getString("paid_status"));
				uu.setAccount_status(rs.getString("account_status"));
				if(rs.getString("paid_status").equalsIgnoreCase("paid"))
				{
					uu.setDisable(true);
					uu.setDisableForUnpaid(false);
				}
				else
				{
					uu.setDisable(false);
					uu.setDisableForUnpaid(true);
					
				}
				list.add(uu);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<UserInfo> allSavingAccountBean(String shareId) 
	{
		int i=1;
		ArrayList<UserInfo> list=new ArrayList<>();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select * from savingaccount "
			+ "INNER JOIN shareholder_info on savingaccount.shareHolderNo=shareholder_info.shareholderno where savingaccount.shareHolderNo='"+shareId+"'";
			ResultSet rr=st.executeQuery(query);
			while(rr.next())
			{
				UserInfo ll=new UserInfo();
				ll.setSno(i++);
				ll.setShareHolderNo(rr.getString("savingaccount.shareHolderNo"));
				ll.setFirst_applicantName(rr.getString("shareholder_info.first_applicantName"));
				ll.setContactNo(rr.getString("shareholder_info.mobile"));
				ll.setAccountNo(rr.getString("accountNo"));
				ll.setAmount(rr.getDouble("amount"));
				ll.setEntryDate(rr.getDate("openDate"));
				ll.setEntryDateStr(new SimpleDateFormat("dd/MM/yyyy").format(ll.getEntryDate()));
				ll.setInterest(rr.getDouble("interest"));
				ll.setAccCloseCharge(rr.getDouble("accCloseCharge"));
				ll.setMinBal(rr.getDouble("minBal"));
				ll.setMinDaysForClose(rr.getInt("minDayForClose"));
				if(rr.getString("savingaccount.accountStatus").equals("close") || rr.getString("savingaccount.accountStatus").equals("inactive"))
				{
					ll.setDisable(true);
				}
				else
				{
					ll.setDisable(false);
				}
				list.add(ll);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	public UserInfo savingAccountSelectedDetail(String shareId) 
	{
		UserInfo ll=new UserInfo();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select * from savingaccount "
			+ "INNER JOIN shareholder_info on savingaccount.shareHolderNo=shareholder_info.shareholderno where savingaccount.shareHolderNo='"+shareId+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				ll.setShareHolderNo(rr.getString("savingaccount.shareHolderNo"));
				ll.setFirst_applicantName(rr.getString("shareholder_info.first_applicantName"));
				ll.setContactNo(rr.getString("shareholder_info.mobile"));
				ll.setAccountNo(rr.getString("accountNo"));
				ll.setAmount(rr.getDouble("amount"));
				ll.setEntryDate(rr.getDate("openDate"));
				ll.setEntryDateStr(new SimpleDateFormat("dd/MM/yyyy").format(ll.getEntryDate()));
				ll.setInterest(rr.getDouble("interest"));
				ll.setAccCloseCharge(rr.getDouble("accCloseCharge"));
				ll.setMinBal(rr.getDouble("minBal"));
				ll.setMinDaysForClose(rr.getInt("minDayForClose"));
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ll;
	}

	public ArrayList<ReportInfo> savingAccountReport(String accountNo, Date startDate, Date endDate) 
	{
		ArrayList<ReportInfo> list=new ArrayList<>();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			String query="select * from customer_deposit where account_no='"+accountNo+"' and status='approved' ";
			String query1="select * from customer_withdrwal where account_no='"+accountNo+"' and status='approved' ";
			Statement st=conn.createStatement();
			Statement st1=conn.createStatement();
			if(startDate!=null && endDate!=null)
			{
				String sDate=sdfForDb.format(startDate);
				String eDate=sdfForDb.format(endDate);
				query+=" and date >= '"+sDate+"' and date<= '"+eDate+"'";
				query1+=" and date >='"+sDate+"' and date<= '"+eDate+"'";
			}
			ResultSet rr=st.executeQuery(query);
			while(rr.next())
			{
				ReportInfo info=new ReportInfo();
			    info.setDate(rr.getDate("date"));
			    info.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(info.getDate()));
			    info.setDebitAmount(rr.getDouble("amount"));
			    info.setPayMode(rr.getString("payment_mode"));
			    String remark=rr.getString("remark");
			    if(remark==null)
			    {
			    	remark="";
			    }
			    if(rr.getString("type").equals("interest"))
			    {
			    	info.setStatus("Interest Credit");
			    }
			    else
			    {
			    	info.setStatus("Deposit: "+remark);
			    }
			    
		        list.add(info);
			}
			 
			ResultSet rr1=st1.executeQuery(query1);
			while(rr1.next())
			{
				ReportInfo info=new ReportInfo();
			    info.setDate(rr1.getDate("date"));
			    info.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(info.getDate()));
			    info.setCreditAmount(rr1.getDouble("amount"));
			    info.setPayMode(rr1.getString("payment_mode"));
			    String remark=rr1.getString("remark");
			    if(remark==null)
			    {
			    	remark="";
			    }
			    info.setStatus("Withdrawal: "+remark);
		        list.add(info);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	public ArrayList<ReportInfo> savingAccountLedgerForMember(String accountNo, Date startDate, Date endDate) 
	{
		ArrayList<ReportInfo> list=new ArrayList<>();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			String query="select * from customer_deposit where account_no='"+accountNo+"' and status='approved' ";
			String query1="select * from customer_withdrwal where account_no='"+accountNo+"' and status='approved' ";
			Statement st=conn.createStatement();
			Statement st1=conn.createStatement();
			if(startDate!=null && endDate!=null)
			{
				String sDate=sdfForDb.format(startDate);
				String eDate=sdfForDb.format(endDate);
				query+=" and date >= '"+sDate+"' and date<= '"+eDate+"'";
				query1+=" and date >='"+sDate+"' and date<= '"+eDate+"'";
			}
			ResultSet rr=st.executeQuery(query);
			while(rr.next())
			{
				ReportInfo info=new ReportInfo();
			    info.setDate(rr.getDate("date"));
			    info.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(info.getDate()));
			    info.setCreditAmount(rr.getDouble("amount"));
			    String remark=rr.getString("remark");
			    if(remark==null)
			    {
			    	remark="";
			    }
			    if(rr.getString("type").equals("interest"))
			    {
			    	info.setStatus("Interest Credit");
			    }
			    else
			    {
			    	info.setStatus("Deposit: "+remark);
			    }
			    
		        list.add(info);
			}
			 
			ResultSet rr1=st1.executeQuery(query1);
			while(rr1.next())
			{
				ReportInfo info=new ReportInfo();
			    info.setDate(rr1.getDate("date"));
			    info.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(info.getDate()));
			    info.setDebitAmount(rr1.getDouble("amount"));
			    String remark=rr1.getString("remark");
			    if(remark==null)
			    {
			    	remark="";
			    }
			    info.setStatus("Withdrawal: "+remark);
		        list.add(info);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	
	public ArrayList<ReportInfo> savingAccountDepositEntry(String accountNo) 
	{
		ArrayList<ReportInfo> list=new ArrayList<>();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			String query="select * from customer_deposit where account_no='"+accountNo+"' and status='approved' ";
			Statement st=conn.createStatement();
			ResultSet rr=st.executeQuery(query);
			while(rr.next())
			{
				ReportInfo info=new ReportInfo();
				
				info.setId(rr.getString("id"));
			    info.setDate(rr.getDate("date"));
			    info.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(info.getDate()));
			    info.setDebitAmount(rr.getDouble("amount"));
			    info.setPayMode(rr.getString("payment_mode"));
			    String remark=rr.getString("remark");
			    if(remark==null)
			    {
			    	remark="";
			    }
			    if(rr.getString("type").equalsIgnoreCase("deposit"))
			    {
			    	info.setStatus("Deposit: "+remark);
			    	 list.add(info);
			    }
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}

	public double openingBalance(String accountNo, Date entryDate) 
	{
		double amount=0;
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			String ts=String.valueOf(new Timestamp(entryDate.getTime())).substring(0,10);
			Statement st=conn.createStatement();
			Statement st1=conn.createStatement();
			String query="select sum(amount) as amt from customer_deposit where account_no='"+accountNo+"' and status='approved' and chequeStatus='cleared' and"
					+ " date < '"+ts+"'";
			
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				amount=rr.getDouble("amt");
			}
			
			query="select sum(amount) as amt from customer_withdrwal where account_no='"+accountNo+"' and status='approved' and chequeStatus='cleared'  and date"
					+ " < '"+ts+"'";
			ResultSet rr1=st1.executeQuery(query);
			if(rr1.next())
			{
				amount-=rr1.getDouble("amt");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		//System.out.println(amount);
		return amount;
	}
	
	public double calculateSavingAccountBalance(String accountNo) 
	{
		double amount=0;
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			Statement st1=conn.createStatement();
			String query="select sum(amount) as amt from customer_deposit where account_no='"+accountNo+"' and status='approved' and chequeStatus='cleared' ";
			
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				amount=rr.getDouble("amt");
			}
			
			query="select sum(amount) as amt from customer_withdrwal where account_no='"+accountNo+"' and status='approved' and chequeStatus='cleared' ";
			ResultSet rr1=st1.executeQuery(query);
			if(rr1.next())
			{
				amount-=rr1.getDouble("amt");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		//System.out.println(amount);
		return amount;
	}
	
	public ArrayList<ReportInfo> promotorAccountReport(String ID, Date startDate, Date endDate) 
	{
		ArrayList<ReportInfo> list=new ArrayList<>();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			String query="select * from promotors_capital where promotor_id='"+ID+"' and status='active' and chequeStatus='cleared' ";
			String query1="select * from return_promotors_capital where promotor_id='"+ID+"' and status='active' and chequeStatus='cleared' ";
			Statement st=conn.createStatement();
			Statement st1=conn.createStatement();
			if(startDate!=null && endDate!=null)
			{
				Timestamp sDate=new Timestamp(startDate.getTime());
				Timestamp eDate=new Timestamp(endDate.getTime());
				sDate.setHours(0);sDate.setMinutes(0);sDate.setSeconds(0);sDate.setNanos(0);
				eDate.setHours(0);eDate.setMinutes(0);eDate.setSeconds(0);eDate.setNanos(0);
				query+=" and income_date >= '"+sDate+"' and income_date<= '"+eDate+"'";
				query1+=" and income_date >='"+sDate+"' and income_date<= '"+eDate+"'";
			}
			
			ResultSet rr=st.executeQuery(query);
			while(rr.next())
			{
				ReportInfo info=new ReportInfo();
				
				info.setId(rr.getString("id"));
			    info.setDate(rr.getDate("income_date"));
			    info.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(info.getDate()));
			    info.setDebitAmount(rr.getDouble("amount"));
			    info.setPayMode(rr.getString("payment_mode"));
			    info.setSubmittedToBank(rr.getString("transfer_to_bank"));
			    String remark=rr.getString("description");
			    if(remark==null)
			    {
			    	remark="";
			    }
			    if(rr.getString("type").equals("interest"))
			    {
			    	info.setStatus("Interest Credit");
			    }
			    else
			    {
			    	info.setStatus("Deposit: "+remark);
			    }
			    
		        list.add(info);
			}
			 
			ResultSet rr1=st1.executeQuery(query1);
			while(rr1.next())
			{
				ReportInfo info=new ReportInfo();
				
				info.setId(rr1.getString("id"));
				info.setPayMode(rr1.getString("payment_mode"));
			    info.setDate(rr1.getDate("income_date"));
			    info.setSubmittedToBank(rr1.getString("transfer_to_bank"));
			    info.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(info.getDate()));
			    info.setCreditAmount(rr1.getDouble("amount"));
			    String remark=rr1.getString("description");
			    if(remark==null)
			    {
			    	remark="";
			    }
			    info.setStatus("Withdrawal: "+remark);
		        list.add(info);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	public double openingBalanceForPromotorStatement(String ID, Date entryDate) 
	{
		double amount=0;
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			String ts=String.valueOf(new Timestamp(entryDate.getTime())).substring(0,10);
			Statement st=conn.createStatement();
			Statement st1=conn.createStatement();
			String query="select sum(amount) as amt from promotors_capital where promotor_id='"+ID+"' and income_date < '"+ts+"' and status='active' and chequeStatus='cleared' ";
			
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				amount=rr.getDouble("amt");
			}
			
			query="select sum(amount) as amt from return_promotors_capital where promotor_id='"+ID+"' and income_date < '"+ts+"' and status='active' and chequeStatus='cleared'";
			ResultSet rr1=st1.executeQuery(query);
			if(rr1.next())
			{
				amount-=rr1.getDouble("amt");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		//System.out.println(amount);
		return amount;
	}

	public ArrayList<ReportInfo> loanReport(String loanNo,String shareHolderNo,String type, Connection conn) 
	{
		ArrayList<ReportInfo> list=new ArrayList<>();
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String query=""/*,loanNo=selectedLoan.getLoanNo()*/,loanType="";
			System.out.println("+hii"+type);
			if(type.equals("Personal Loan") || type.equals("personalloan"))
			{
				loanType="personalloan";
				query="select * from personalloan where loanNo='"+loanNo+"' and shareHolderNo='"+shareHolderNo+"'";
			}
			else if(type.equals("Secure Loan") || type.equals("secure_loan"))
			{
				loanType="secure_loan";
				query="select * from secure_loan where loanNo='"+loanNo+"' and shareHolderNo='"+shareHolderNo+"'";
			}
			else if(type.equals("Vehicle Loan"))
			{
				loanType="vehicleloan";
				query="select * from vechicleloan where loanNo='"+loanNo+"' and shareHolderNo='"+shareHolderNo+"'";
			}
			else if(type.equals("Property Loan"))
			{
				loanType="propertyloan";
				query="select * from propertyloan where loanNo='"+loanNo+"' and shareHolderNo='"+shareHolderNo+"'";
			}
			else if(type.equals("Group Loan"))
			{
				loanType="grouploan";
				//loanNo=new DataBaseMethods().memberLoanNoForGroupLoan(loanNo,shareHolderNo);
				query="select * from grouploan where sub_loan_no='"+loanNo+"' and teamLeader='"+shareHolderNo+"'";
			}
			
			rr=st.executeQuery(query);
			if(rr.next())
			{
				int terms=rr.getInt("terms");
				int frequency=rr.getInt("frequency");
				double emi=rr.getDouble("emi");
				double discount=rr.getDouble("discount");
				int j=365/frequency;
				
				Date installDate=null;
				ReportInfo ll=new ReportInfo();
				ll.setStatus("Loan Amount");
				ll.setCreditAmount(rr.getDouble("loanAmount"));
			//	ll.setDate(rr.getDate("approvalDate"));
			//	ll.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(ll.getDate()));
				ll.setAmount(0);
				ll.setPaidStatus(rr.getString("paidStatus"));
				list.add(ll);
				
				ll=new ReportInfo();
				ll.setStatus("Interest Amount");
				double amount=(terms*rr.getDouble("emi"))-rr.getDouble("loanAmount");
				ll.setCreditAmount(Double.parseDouble(new DecimalFormat("##.##").format(amount)));
				
				if(rr.getDate("emi_due_date")==null)
				{
					installDate=rr.getDate("approvalDate");
				}
				else
				{
					installDate=rr.getDate("emi_due_date");
					if(frequency==12)
                    {
                        installDate.setMonth(installDate.getMonth()-1);
                    }
                    else
                    {
                        installDate.setDate(installDate.getDate()-j);
                    }
				}
				
			//	ll.setDate(installDate);
			//	ll.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(ll.getDate()));
				ll.setPaidStatus(rr.getString("paidStatus"));
				ll.setAmount(0);
				list.add(ll);
				
				double paidAmount=new DataBaseMethods().loanPaidAmountByUser(loanType,loanNo, conn);
				for (int i = 1; i<= terms; i++) 
				{
					//System.out.println("paid amt : "+paidAmount);
					
					ll=new ReportInfo();
					
					ll.setsNo(i);
					ll.setStatus("EMI "+ i);
					ll.setAmount(emi);
					
					 if(paidAmount>=emi)
					{
						ll.setDebitAmount(emi);
						paidAmount=Double.parseDouble(new DecimalFormat("##.##").format(paidAmount-emi));
					}
					else
					{
						ll.setDebitAmount(paidAmount);
						paidAmount=0;
					}
					 
					Date dt=installDate;
					if(rr.getDate("emi_due_date")==null)
					{
						dt.setDate(dt.getDate()+j);
					}
					else
					{
						if(frequency==12)
                        {
                            dt.setMonth(dt.getMonth()+1);
                        }
                        else
                        {
                            dt.setDate(dt.getDate()+j);
                        }
					}
					ll.setDate(dt);
					ll.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(ll.getDate()));
					ll.setPaidStatus(rr.getString("paidStatus"));
					list.add(ll);
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<InstallmentInfo> rd_fdReport(String user_no) 
	{
		ArrayList<InstallmentInfo> list=new  ArrayList<InstallmentInfo>();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select * from customer_info where user_no='" + user_no+ "' and account_status='active'";
			ResultSet rs=st.executeQuery(query);
			if (rs.next()) 
			{
				//System.out.println("enter2");
				int i = 0;
				Date offcdt = rs.getDate("office_date");
				int terms = rs.getInt("terms");
				int frequency=rs.getInt("frequency");
				double instlmnt_amnt = rs.getDouble("installment_amount");
				double intrst = rs.getDouble("interest");
				double paidAmount =new DataBaseMethods().paidAmountByUser(user_no,conn);
				double mnthly_intrst = intrst / frequency;
				double instlmnt = instlmnt_amnt;
				
				int x=0,y=1,z=0;
				if(frequency==4)
			    {
					y=4;
			    	//x=terms/3;
			    }
			    else if(frequency==2)
			    {
			    	y=6;
			    	//x=terms/6;
			    }
			    else if(frequency==1)
			    {
			    	y=12;
			    	//x=terms/12;
			    }
			    else if(frequency==12)
			    {
			    	y=1;
			    	//x=terms;
			    }
			    else if(frequency==365)
		        {
			    	z=1;
		        	//z=terms;
		        }
		        else if(frequency==36)
		        {
		        	z=10;
		        	//z=terms*10;
		        }
		        else if(frequency==24)
		        {
		        	z=15;
		        	//z=terms*15;
		        }
				
				if (rs.getString("scheme_type").equals("RD")) 
				{
					int d=offcdt.getDate();
					//System.out.println("enter3");
					//System.out.println("X : "+x);
					for (i = 1; i <= terms; i++) 
					{
						//System.out.println("enter4");
						InstallmentInfo ii = new InstallmentInfo();
						ii.setIns_date(new SimpleDateFormat("dd/MM/yyyy").format(offcdt));
						ii.setInstlmntDate(offcdt);
						if(frequency==12 || frequency==4 || frequency==2 || frequency==1)
				        {
							offcdt=new DataBaseMethods().createDate2(offcdt,y,terms,d,rs.getDate("office_date"));
				        }
						else
						{
							offcdt=new DataBaseMethods().createDate3(offcdt,z);
						}
						if (paidAmount >= instlmnt)
						{
							ii.setPaidAmount(instlmnt);
							ii.setStatus("Paid");
						}
						else if (paidAmount < instlmnt && paidAmount > 0) 
						{
							ii.setPaidAmount(paidAmount);
							ii.setStatus("Due");
						}
						else 
						{
							ii.setStatus("Due");
						}
						paidAmount = paidAmount - instlmnt;
						ii.setSno(i);
						ii.setInstallment_amount(instlmnt);
						ii.setInterest(intrst); 
						ii.setCalc_interest(Math.round(instlmnt_amnt* (mnthly_intrst / 100)));
						instlmnt_amnt = instlmnt_amnt+ (Math.round(instlmnt_amnt* (mnthly_intrst / 100)));
						ii.setTot_sum(Double.parseDouble(new DecimalFormat("#.##").format(instlmnt_amnt)));
						list.add(ii);
						if (i != terms) 
						{
							instlmnt_amnt = instlmnt_amnt + instlmnt;
						}
					}
				} 
				else 
				{
					//System.out.println("enter5");
					InstallmentInfo ii = new InstallmentInfo();
					ii.setInstallment_amount(instlmnt);
					ii.setPaidAmount(instlmnt);
					ii.setSno(1);
					ii.setInterest(intrst);
					ii.setStatus("Paid");
					ii.setTerms(terms);
					double maturityValue =new DataBaseMethods().findInterest(terms, instlmnt,intrst, rs.getString("scheme_type"),frequency);
					ii.setCalc_interest(maturityValue - instlmnt);
					ii.setTot_sum(Double.parseDouble(new DecimalFormat("#.##").format(maturityValue)));
					list.add(ii);
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}

	public UserInfo particularSavingAccountBean(String shareHolderNo) 
	{
		int i=1;
		UserInfo ll=new UserInfo();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select * from savingaccount "
			+ "INNER JOIN shareholder_info on savingaccount.shareHolderNo=shareholder_info.shareholderno where savingaccount.shareHolderNo='"+shareHolderNo+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				
				ll.setSno(i++);
				ll.setShareHolderNo(rr.getString("savingaccount.shareHolderNo"));
				ll.setFirst_applicantName(rr.getString("shareholder_info.first_applicantName"));
				ll.setContactNo(rr.getString("shareholder_info.mobile"));
				ll.setAccountNo(rr.getString("accountNo"));
				ll.setAmount(rr.getDouble("amount"));
				ll.setEntryDate(rr.getDate("openDate"));
				ll.setEntryDateStr(new SimpleDateFormat("dd/MM/yyyy").format(ll.getEntryDate()));
				ll.setInterest(rr.getDouble("interest"));
				ll.setAccCloseCharge(rr.getDouble("accCloseCharge"));
				ll.setMinBal(rr.getDouble("minBal"));
				ll.setMinDaysForClose(rr.getInt("minDayForClose"));
				ll.setDsaCode(rr.getString("dsacode"));
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ll;

	}
	
	public ArrayList<ReportInfo> dailyloanReport(String loanNo)
	{
		System.out.println("loanNo :"+loanNo); 
		
		ArrayList<ReportInfo> list = new ArrayList<ReportInfo>();
		int count=0;double total=0;
		
		try (Connection con = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = con.createStatement();
			String qq = "SELECT sum(amount), pay_date, receipt_no, loanType " +
		            "FROM loanpayment " +
		            "WHERE loanNo='" + loanNo + "' AND receipt_status='approved' AND chequeStatus='cleared' " +
		            "GROUP BY pay_date, receipt_no, loanType";
			ResultSet rs = st.executeQuery(qq);
			while (rs.next())
			{   
				ReportInfo obj= new ReportInfo();
				obj.setAmount(rs.getDouble("sum(amount)"));
				total+=obj.getAmount();
				obj.setBalance(total);
				obj.setCreditAmount(obj.getAmount());
				obj.setStatus(dbm.loanLabelByLoanValue(rs.getString("loanType"))+" No. "+loanNo+" EMI Amount Receive: ");
				obj.setDate(rs.getDate("pay_date"));
				obj.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(rs.getDate("pay_date")));
				obj.setsNo(++count);
				obj.setReceiptNo(rs.getString("receipt_no"));
				list.add(obj);
				
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	
	
	
	public ArrayList<ReportInfo> dailyloanAndSavingReport(String loanNo)
	{
		ArrayList<ReportInfo> list = new ArrayList<ReportInfo>();
		int count=0;
		double total=0;
		String shno="", svno="";
		
		try (Connection con = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = con.createStatement();
			String qq = "select sum(amount),pay_date,receipt_no from  loanpayment where loanNo='"+loanNo+"' and receipt_status='approved' and chequeStatus='cleared' group by pay_date";
			//System.out.println(qq);
			ResultSet rs = st.executeQuery(qq);
			while (rs.next())
			{
				ReportInfo obj= new ReportInfo();
				
				if(count==0)
				{
					shno=dbm.shareHolderNoByLoanNo(loanNo);
					svno=dbm.savingAccountByShareHolderNo(shno);
				}
				obj.setAmount(rs.getDouble("sum(amount)")+new DataBaseMethods().savingDepositBetweenDate(svno, new SimpleDateFormat("yyyy-MM-dd").format(rs.getDate("pay_date")), new SimpleDateFormat("yyyy-MM-dd").format(rs.getDate("pay_date"))));
				total+=obj.getAmount();
				obj.setBalance(total);
				obj.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(rs.getDate("pay_date")));
				obj.setsNo(++count);
				obj.setReceiptNo(rs.getString("receipt_no"));
				list.add(obj);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	public ArrayList<ReportInfo> PaidEmiloanReport(String loanNo,String shareHolderNo,String type, Connection conn)
    {
        ArrayList<ReportInfo> list=new ArrayList<>();
        Statement st=null;
        ResultSet rr=null;
        try
        {
            st=conn.createStatement();
            String query=""/*,loanNo=selectedLoan.getLoanNo()*/,loanType="";
            if(type.equals("Personal Loan"))
            {
                loanType="personalLoan";
                query="select * from personalloan where loanNo='"+loanNo+"' and shareHolderNo='"+shareHolderNo+"'";
            }
            else if(type.equals("Secure Loan"))
            {
                loanType="secure_loan";
                query="select * from secure_loan where loanNo='"+loanNo+"' and shareHolderNo='"+shareHolderNo+"'";
            }
            else if(type.equals("Vehicle Loan"))
            {
                loanType="vehicleLoan";
                query="select * from vechicleloan where loanNo='"+loanNo+"' and shareHolderNo='"+shareHolderNo+"'";
            }
            else if(type.equals("Property Loan"))
            {
                loanType="propertyLoan";
                query="select * from propertyloan where loanNo='"+loanNo+"' and shareHolderNo='"+shareHolderNo+"'";
            }
            
            
            
            else if(type.equals("Group Loan"))
            {
                loanType="groupLoan";
                loanNo=new DataBaseMethods().memberLoanNoForGroupLoan(loanNo,shareHolderNo);
                query="select * from grouploan where sub_loan_no='"+loanNo+"' and teamLeader='"+shareHolderNo+"'";
            }
            
            rr=st.executeQuery(query);
            if(rr.next())
            {
                int terms=rr.getInt("terms");
                int frequency=rr.getInt("frequency");
                double emi=rr.getDouble("emi");
                double discount=rr.getDouble("discount");
                int j=365/frequency;
               
                Date installDate=null;
                ReportInfo ll=new ReportInfo();
                ll.setStatus("Loan Amount");
                ll.setCreditAmount(rr.getDouble("loanAmount"));
            //    ll.setDate(rr.getDate("approvalDate"));
            //    ll.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(ll.getDate()));
                ll.setAmount(0);
                list.add(ll);
               
                ll=new ReportInfo();
                ll.setStatus("Interest Amount");
                double amount=(terms*rr.getDouble("emi"))-rr.getDouble("loanAmount");
                ll.setCreditAmount(Double.parseDouble(new DecimalFormat("##.##").format(amount)));
               
                double totalCr=rr.getDouble("loanAmount")+amount;
                if(rr.getDate("emi_due_date")==null)
                {
                    installDate=rr.getDate("approvalDate");
                }
                else
                {
                    installDate=rr.getDate("emi_due_date");
                    if(frequency==12)
                    {
                        installDate.setMonth(installDate.getMonth()-1);
                    }
                    else
                    {
                        installDate.setDate(installDate.getDate()-j);
                    }
                }
               
            //    ll.setDate(installDate);
             //   ll.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(ll.getDate()));
                ll.setAmount(0);
                list.add(ll);
               
                double paidAmount=0;
                for (int i = 1; i<= terms; i++)
                {
                    //System.out.println("paid amt : "+paidAmount);
                    Date dt=installDate;
                    if(rr.getDate("emi_due_date")==null)
                    {
                        dt.setDate(dt.getDate()+j);
                    }
                    else
                    {
                        if(frequency==12)
                        {
                            dt.setMonth(dt.getMonth()+1);
                        }
                        else
                        {
                            dt.setDate(dt.getDate()+j);
                        }
                    }
                    ll=new ReportInfo();
                   
                    ll.setsNo(i);
                    ll.setStatus("EMI "+ i);
                    ll.setAmount(emi);
                    ll.setDate(dt);
                    ll.setDateStr(new SimpleDateFormat("dd/MM/yyyy").format(ll.getDate()));
                    paidAmount=new DataBaseMethods().loanPaidAmountByparticularDate(ll.getDate(), loanNo);
                    
                    if(paidAmount>0)
                    {
                        ll.setDebitAmount(paidAmount);
                    }
                    else
                    {
                        ll.setDebitAmount(0);
                    }
                    totalCr-=ll.getDebitAmount();
                    ll.setBalance(totalCr); 
                   
                    list.add(ll);
                }
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return list;
    }

	public UserInfo allRd_FDInfo(String shareId, UserInfo uu)
    {
        try (Connection con = DataBaseConnection.JAVAConnection();)
        {
            int i=0;
            Statement st = con.createStatement();
            String qq = "select * from customer_info where user_no='"+shareId+"'";
            ResultSet rs = st.executeQuery(qq);
            //System.out.println("dklaskdlkasdkasd"+qq);
            if (rs.next())
            {
                i=i+1;
                String hno=rs.getString("shareHolderNo");
                uu=new DataBaseMethods().userInfoByShareHolderNo(hno, con);
                uu.setSno(i);
                 uu.setId(rs.getString("id"));
                 uu.setOffice_application_no(rs.getString("office_application_no"));
                uu.setOffice_date(rs.getDate("office_date"));
                uu.setStrOffcDate(new SimpleDateFormat("dd/MM/yyyy").format(rs.getDate("office_date")));
                uu.setUser_no(rs.getString("user_no"));
                uu.setScheme_type(rs.getString("scheme_type"));
                uu.setPlan_no(rs.getString("plan_no"));
                uu.setTerms(rs.getInt("terms"));
                uu.setShareHolderNo(rs.getString("shareHolderNo"));
                uu.setFrequency(rs.getInt("frequency"));
                uu.setApplicant_fees(rs.getDouble("applicant_fees"));
                uu.setInstallment_amount(rs.getDouble("installment_amount"));
                uu.setInterest(rs.getDouble("interest"));
                uu.setMaturity_value(Double.valueOf(df.format(rs.getDouble("maturity_value"))));
            //    uu.setMaturity_value(rs.getDouble("maturity_value"));
                uu.setMaturity_date(rs.getDate("maturity_date"));
                uu.setStrMatDate(new SimpleDateFormat("dd/MM/yyyy").format(rs.getDate("maturity_date")));
                uu.setStatus(rs.getString("paid_status"));
                uu.setAccount_status(rs.getString("account_status"));
                if(rs.getString("paid_status").equalsIgnoreCase("paid"))
                {
                    uu.setDisable(true);
                    uu.setDisableForUnpaid(false);
                }
                else
                {
                    uu.setDisable(false);
                    uu.setDisableForUnpaid(true);
                    
                }
                // list.add(uu);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return uu;
    }
	
	public LoanInfo allLoanInfo(String shareId, String loanNo, LoanInfo ll)
    {
        int i=1;
        ArrayList<LoanInfo> list=new ArrayList<LoanInfo>();
        try(Connection conn=DataBaseConnection.JAVAConnection())
        {
            Statement st=conn.createStatement();
            Statement st1=conn.createStatement();
            Statement st2=conn.createStatement();
            Statement st3=conn.createStatement();
            String query="select * from personalloan INNER JOIN shareholder_info on personalloan.shareholderno=shareholder_info.shareholderno where personalloan.loanNo='"+loanNo+"'";
            ResultSet rr=st.executeQuery(query);
            
            while(rr.next())
            {
                commonLoanDetails(rr, ll, "Personal Loan", "personalloan");
                ll.setSno(i++);
                ll.setShareHolderNo(shareId);
                ll.setReason(rr.getString("reason"));
            }
           
            query="select * from propertyloan INNER JOIN shareholder_info on propertyloan.shareholderno=shareholder_info.shareholderno where propertyloan.loanNo='"+loanNo+"'";
            ResultSet rr1=st1.executeQuery(query);
            while(rr1.next())
            {
                commonLoanDetails(rr1, ll, "Property Loan", "propertyloan");
                ll.setSno(i++);
                ll.setShareHolderNo(shareId);
                ll.setCategory(rr1.getString("category"));
                ll.setRegistryNo(rr1.getString("registryNo"));
                ll.setVerified(rr1.getString("verified"));
                ll.setPatta(rr1.getString("patta"));
                ll.setAuthorityPatta(rr1.getString("authorityPatta"));
                ll.setJamabandi(rr1.getString("jamabandi"));
            }
           
            query="select * from vechicleloan INNER JOIN shareholder_info on vechicleloan.shareholderno=shareholder_info.shareholderno where vechicleloan.loanNo='"+loanNo+"'";
            ResultSet rr2=st2.executeQuery(query);
            while(rr2.next())
            {
                commonLoanDetails(rr2, ll, "Vehicle Loan", "vechicleloan");
                ll.setSno(i++);
                ll.setShareHolderNo(shareId);
                ll.setCategory(rr2.getString("category"));
                ll.setVechiclename(rr2.getString("vechileName"));
                ll.setVechileModel(rr2.getString("model"));
                ll.setEngineNo(rr2.getString("engineno"));
                ll.setMacNo(rr2.getString("macNo"));
            }
           
            query="select * from grouploan INNER JOIN shareholder_info on grouploan.teamLeader=shareholder_info.shareholderno where grouploan.sub_loan_no='"+shareId+"'";
            ResultSet rr3=st3.executeQuery(query);
            while(rr3.next())
            {
                commonLoanDetails(rr3, ll, "Group Loan", "grouploan");
                ll.setSno(i++);
                ll.setShareHolderNo(shareId);
                ll.setCenter(rr3.getString("center"));
                ll.setMembers(rr3.getString("members"));
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return ll;
    }

	public ArrayList<ReportInfo> savingAccStatementForAll(int branchId,Date startDate, Date endDate, String accountNo, String center) 
	{
		int count=1;
		ArrayList<ReportInfo> list=new ArrayList<>();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select * from savingaccount where accountStatus='open' ";
				
			if(!accountNo.equalsIgnoreCase("-1"))
			{
				qq+=" and accountNo='"+accountNo+"' ";
			}
			
			if(!center.equalsIgnoreCase("-1"))
			{
				qq+=" and center='"+center+"' ";
			}

			ResultSet rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			while(rr.next())
			{
				ReportInfo ri=new ReportInfo();
				
				ri.setShareHolderNo(rr.getString("shareHolderNo"));
				ri.setApplicantName(dbm.applicantNameByShareHolderNo(ri.getShareHolderNo(), conn));
				ri.setAccountNo(rr.getString("accountNo"));
				ri.setDateStr(sdf.format(rr.getDate("openDate")));
				ri.setAmount(Double.parseDouble(df.format(openingBalance(ri.getAccountNo(),startDate))));
				ri.setCreditAmount(Double.parseDouble(df.format(dbm.depositValueBetweenDateByAccNo(ri.getAccountNo(),startDate, endDate,"both", conn))));
				ri.setDebitAmount(Double.parseDouble(df.format(dbm.withdrwalValueBetweenDateByAccNo(ri.getAccountNo(), startDate, endDate, conn))));
				ri.setBalance(Double.parseDouble(df.format((ri.getAmount()+ri.getCreditAmount())-ri.getDebitAmount())));
				ri.setNetTot(Double.parseDouble(df.format(rr.getDouble("amount"))));
				
				if(ri.getBalance()>0)
				{
					ri.setsNo(count++);
					list.add(ri);
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}

	public int refreshSavingAccountBalance(ReportInfo selectedAccount) 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="update savingaccount set amount='"+selectedAccount.getBalance()+"' where accountNo='"+selectedAccount.getAccountNo()+"' ";
//			System.out.println(qq);
			int i=st.executeUpdate(qq);
			return i;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}
}
