package main_package;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.Serializable;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.faces.context.FacesContext;
import javax.faces.model.SelectItem;
import javax.servlet.http.HttpSession;

import org.json.simple.JSONObject;

import group_head_package.IncomeList;
import report_package.CommanInfo;
import report_package.DailyInfo;
import report_package.DataBaseReportMethods;

public class DataBaseMethodTaheer implements Serializable
{
	private static final int[][] BUCKETS = {{1, 30},{31, 60},{61, 90},{90, -1}};
	DataBaseMethods db = new DataBaseMethods();
	DataBaseMethodsHemant dbh = new DataBaseMethodsHemant();
	DataBaseReportMethods obj = new DataBaseReportMethods();
	DataBaseMethodsRavi dbrj = new DataBaseMethodsRavi();
	DataBaseMethodRahul dbr = new DataBaseMethodRahul();
	DecimalFormat dc = new DecimalFormat("##.##");
	DecimalFormat df = new DecimalFormat("##.##");
	SimpleDateFormat sf = new SimpleDateFormat("dd/MM/yyyy");
	SimpleDateFormat dateObj = new SimpleDateFormat("dd/MM/yyyy");
	SimpleDateFormat sdfForDb = new SimpleDateFormat("yyyy-MM-dd");
	DataBaseMethodsRavi dbmr = new DataBaseMethodsRavi();
	
	
	
	public String checkLastPaidEmiNoByLoanNo(String loanNo)
	{
	    String lastPaidEmiNo = null;
        
	    try(Connection conn = DataBaseConnection.JAVAConnection())
	    {   
	        String query = "SELECT * FROM loanpayment WHERE loanNo = ? ORDER BY pay_date DESC LIMIT 1";

	        try(PreparedStatement ps = conn.prepareStatement(query))
	        {	
	            ps.setString(1, loanNo);

	            ResultSet rs = ps.executeQuery();   
	            if(rs.next())
	            {
	                lastPaidEmiNo = rs.getString("emi_no");
	                System.out.println(lastPaidEmiNo);
	            }
	        }
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	    }

	    return lastPaidEmiNo;
	}
	
	
	
	public String fetchShareHolderNoByLoanNo(String loanNo, Connection conn)
	{
		String loanType, loanNoColumn, shareHolderNoColumn;
		
		if (loanNo.contains("GL"))
		{
			loanNoColumn = "sub_loan_no";
			loanType = "grouploan";
			shareHolderNoColumn = "teamLeader";
		}
		else if (loanNo.contains("SL"))
		{
			loanType = "secure_loan";
			loanNoColumn = "loanNo";
			shareHolderNoColumn = "shareholderno";
		}
		else
		{
			loanType = "personalloan";
			loanNoColumn = "loanNo";
			shareHolderNoColumn = "shareholderno";
		}
		
		String qq = "SELECT * FROM "+loanType+" WHERE "+loanNoColumn+" = ? ";
				
		try (PreparedStatement ps = conn.prepareStatement(qq))
		{
			ps.setString(1, loanNo);
			
			try (ResultSet rs = ps.executeQuery())
			{
				if (rs.next()) 
				{
					return rs.getString(shareHolderNoColumn);
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return "";
	}
	
	
	
	public String fetchPreEMIPaidDate(String loanNo, Connection conn)
	{
		String qq = "SELECT * FROM income_table where item = 'Pre Emi Charge' AND loan_no = ? AND status = 'active' AND "
				  + "chequeStatus = 'cleared' ";
		
		try (PreparedStatement ps = conn.prepareStatement(qq))
		{
			ps.setString(1, loanNo);
			
			try (ResultSet rs = ps.executeQuery())
			{
				if (rs.next()) 
				{
					if (rs.getDate("income_date") != null)
					{
						return sf.format(rs.getDate("income_date"));
					}
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return null;
	}
	
	

	public double fetchPreEMIPaidAmount(String loanNo, Connection conn)
	{
		String qq = "SELECT * FROM income_table where item = 'Pre Emi Charge' AND loan_no = ? AND status = 'active' AND "
				  + "chequeStatus = 'cleared' ";
		
		try (PreparedStatement ps = conn.prepareStatement(qq))
		{
			ps.setString(1, loanNo);
			
			try (ResultSet rs = ps.executeQuery())
			{
				if (rs.next()) 
				{
					return rs.getDouble("amount");
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public ArrayList<LoanInfo> fetchActiveLoanListByLoanType(String loanType, int branchId, String status, Connection conn)
	{
		ArrayList<LoanInfo> activeLoanList = new ArrayList<>();
		
		try 
		{
			String qq = "SELECT * FROM "+loanType+" WHERE status = 'Approve' ";
			
			if (branchId != -1)
			{
				qq += " AND branch_id = '"+branchId+"' ";
			}
						
			try (PreparedStatement ps = conn.prepareStatement(qq))
			{
				try (ResultSet rs = ps.executeQuery())
				{
					while (rs.next()) 
					{
						LoanInfo li = new LoanInfo();
						
						if (loanType.contains("grouploan")) 
						{
							li.setLoanNo(rs.getString("sub_loan_no"));
							li.setShareHolderNo(rs.getString("teamLeader"));
						}
						else
						{
							li.setLoanNo(rs.getString("loanNo"));
							li.setShareHolderNo(rs.getString("shareholderno"));
						}
						
						li.setApplicantName(db.shareHolderNameByShareHolderNo(li.getShareHolderNo(), conn));
						li.setStrEmiDueDate(fetchPreEMIPaidDate(li.getLoanNo(), conn));
						
						if (li.getStrEmiDueDate() != null)
						{
							li.setStatus("Paid");
							li.setAmount(fetchPreEMIPaidAmount(li.getLoanNo(), conn));
						}
						else 
						{
							li.setStatus("Unpaid");
							li.setAmount(db.preEmiChargeCalculationMethod(li.getLoanNo(), loanType, conn));
						}
						
						if (!status.equalsIgnoreCase("all"))
						{
							if (status.equalsIgnoreCase(li.getStatus()) && li.getAmount() > 0)
							{
								activeLoanList.add(li);
							}
						}
						else
						{
							if (li.getAmount() > 0) 
							{
								activeLoanList.add(li);
							}
						}
					}
				} 
				catch (Exception e) 
				{
					e.printStackTrace();
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return activeLoanList;
	}
	
	
	public boolean disAllByCompanyNameUserType(String userType,String cc)
    {
    	boolean disAll;
    	
    	if ((cc == null || "SUDERSHANA FINANCE".equalsIgnoreCase(cc)) && !userType.equalsIgnoreCase("masterAdmin")) 
    	{
    	    disAll = true;
    	} 
    	else 
    	{
    	    disAll = false;
    	}
        
		return disAll;	
    }
	
	
	
	public String dailyLoanDemandDetail(String tableName, String shareHolderNo, java.util.Date searchDate, boolean checkDl, 
										Connection conn, String dsaCode) 
	{
		int amt = 0;
		String detail = "";
		String searchDt = sdfForDb.format(searchDate);
		String strSearchDate = dateObj.format(searchDate);
		Statement st = null;
		ResultSet rr = null;
		String qq = "";
		
		try
		{
			st = conn.createStatement();
			if(tableName.equalsIgnoreCase("grouploan"))
			{
				qq = "select sub_loan_no, frequency, terms, emi_due_date, approvalDate from " + tableName + " where "
				   + "teamLeader = '" + shareHolderNo + "' ";
			}
			else
			{
				qq = "select loanNo, frequency, terms, emi_due_date, approvalDate from " + tableName + " where "
				   + "shareholderno = '" + shareHolderNo + "' ";
			}
			qq += " and status = 'Approve' and paidStatus = 'unpaid' and approvalDate <= '" + searchDt + "' and "
			    + "restructure_status = 'no' ";
			
			if(checkDl == false)
			{
				qq += " and frequency not in('365','365.0')";
			}
			
			if(!dsaCode.equalsIgnoreCase("all"))
			{
				qq += " and dsacode = '" + dsaCode + "' ";
			}
			
			rr = st.executeQuery(qq);
			while(rr.next())
			{
				String loanNo = "";
				if(tableName.equals("grouploan"))
				{
					loanNo = rr.getString("sub_loan_no");
				}
				else
				{
					loanNo = rr.getString("loanNo");
				}
				
				ArrayList<String> dList = db.loanEmiStrDateList(rr.getInt("frequency"), rr.getInt("terms"), 
										  rr.getDate("emi_due_date"), rr.getDate("approvalDate"), loanNo, conn);
				if(dList.contains(strSearchDate))
				{
					if(!dbrj.isLoanDisbursed(loanNo, conn))
				    {
				        continue;
				    }
					
					ArrayList<LoanInfo> loanEMIList = db.loanInstallment(tableName, loanNo, conn);
					
					for(LoanInfo ll: loanEMIList)
					{
//						if(ll.getStatus().equalsIgnoreCase("Due"))
//						{
							//System.out.println(strPayDate+"due : "+ll.getStrInstlmntDate());
							if(ll.getStrInstlmntDate().equals(strSearchDate))
							{
								if(detail.equals(""))
								{
//									detail=loanNo+"="+ll.getDueAmount();
									detail=loanNo+"="+ll.getInstallAmount();
								}
								else
								{
//									detail+=", "+loanNo+"="+ll.getDueAmount();
									detail += ", " + loanNo+"="+ll.getInstallAmount();
								}
//								amt+=ll.getDueAmount();
								amt+=ll.getInstallAmount();
							}
//						}	
					}
				}
			}
		}
		catch (Exception e) 
		{
			 e.printStackTrace();db.fileCreation(e.getMessage());
		}
		finally 
        {
            try 
            {
            	if(st!=null)
            	{
            		  st.close();
                }
            	if(rr!=null)
            	{
            	     rr.close();
                }
            } 
            catch (Exception s) 
            {
            	db.fileCreation(s.getMessage());
            }
        }
		
		return detail + "#" + String.valueOf(amt);
	}	


	
	public double checkPenaltyDetailByPlanId(String planId, String string, Connection conn) 
	{
		Statement st = null;
		ResultSet rr = null;
		
		try
		{
			st = conn.createStatement();
			String qq = "select " + string + " from plans where idplans = '" + planId + "' ";

			rr = st.executeQuery(qq);
			if(rr.next())
			{
				if(rr.getString(string) != null && !rr.getString(string).equalsIgnoreCase(""))
				{
					return rr.getDouble(string);
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try 
			{
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} 
			catch (Exception e2) 
			{
				e2.printStackTrace();
			}
		}
		return 0;
	}
	
	
	
	public ArrayList<UserInfo> loanEmiPaymentReportForCashFlow(int branchId, java.util.Date tdyDate, String mnth, String yr, 
															   java.util.Date strtdt, java.util.Date enddt, Connection conn) 
	{
		ArrayList<UserInfo> list = new ArrayList<UserInfo>();
		Timestamp tdt = null;
		Timestamp start = null, endd = null;
		String strt = "";  
		Statement st = null;
		ResultSet rr = null;

		try
		{
			String query = "SELECT DATE(pay_date) AS pay_date, loanNo, branch_id, MAX(id) AS id, MAX(loanType) AS loanType, "
						 + "MAX(user_id) AS user_id, MAX(transfer_to_bank) AS transfer_to_bank, MAX(chq_date) AS chq_date, "
						 + "MAX(chq_no) AS chq_no, MAX(bank_name) AS bank_name, pay_mode, MAX(receipt_no) AS receipt_no, "
						 + "MAX(penalty) AS penalty, MAX(entry_date) AS entry_date, SUM(amount) AS total_amount "
						 + "FROM loanpayment where receipt_status = 'approved' AND chequeStatus = 'cleared' ";
			
			query = selectQueWithBranch(query, branchId);
			st = conn.createStatement();
			if(tdyDate != null)
			{
				tdt = new Timestamp(tdyDate.getTime());
				strt = String.valueOf(tdt).substring(0, 10);
				query = query + " and pay_date ='" + strt + "'";
			}
			else if(strtdt != null && enddt != null && (enddt.after(strtdt) || enddt.equals(strtdt)))
			{
				start = new Timestamp(strtdt.getTime());
				endd = new Timestamp(enddt.getTime());
				query = query + " and pay_date >= '" + start + "' and pay_date <= '" + endd + "' ";
			}
			
			query += "GROUP BY DATE(pay_date), loanNo, branch_id, pay_mode ORDER BY loanNo, pay_mode";
			
			rr = st.executeQuery(query);
			while(rr.next())
			{
				String lno = rr.getString("loanNo");
				String ltp = rr.getString("loanType");
				String shno = obj.shareHolderNoByLoanNoLoanType(lno, ltp);
				UserInfo uu = db.memberBasicDetail(shno, conn);
				UserInfo ll = new UserInfo();
				
				ll.setId(rr.getString("id"));
				ll.setLoanNo(rr.getString("loanNo"));
				ll.setAccountNo(shno);
				ll.setFirst_applicantName(uu.getFirst_applicantName());
				ll.setContactNo(uu.getContactNo());
				ll.setEntryDate(rr.getDate("pay_date"));
				ll.setEntryDateStr(dateObj.format(ll.getEntryDate()));
				ll.setPenalty(rr.getDouble("penalty"));
				ll.setAmount(rr.getDouble("total_amount"));
				
				ll.setStatus("EMI ");
				
				ll.setReceiptNo(rr.getString("receipt_no"));
				ll.setPayMode(rr.getString("pay_mode"));
				ll.setBank_name(rr.getString("bank_name"));
				ll.setChq_ddNo(rr.getString("chq_no"));
				ll.setChq_ddDate(rr.getDate("chq_date"));
				ll.setSubmittedToBank(rr.getString("transfer_to_bank"));
				ll.setTransferredToBankName(db.bankNameById(rr.getString("transfer_to_bank")));
				
				if(ll.getChq_ddDate() != null)
				{
					ll.setChq_ddDateStr(dateObj.format(ll.getChq_ddDate()));
				}
				
				ll.setActualEntryDateStr(rr.getString("entry_date"));
				ll.setUserId(rr.getString("user_id"));
				ll.setBranchId(rr.getInt("branch_id"));
				ll.setBranch(db.branchNameByBranchId(ll.getBranchId(), conn));
				
				list.add(ll);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}  
		
		finally
		{
			try 
			{
				if(st !=null)
				{
					st.close();
				}
				if(rr !=null)
				{
					rr.close();
				}
			}
			catch (Exception e2) 
			{
				e2.printStackTrace();
			}
		}
		
		return list;
	}
	
	
	
	public ArrayList<SelectItem> bankNameList() 
	{
	    ArrayList<SelectItem> iL = new ArrayList<>();

	    String query = "SELECT id, bank_name, bank_branch FROM bank_table";

	    try (Connection conn = DataBaseConnection.JAVAConnection()) 
	    {

	        if (conn == null) 
	        {
	            System.err.println("Database connection is null.");
	            return iL;
	        }

	        try (PreparedStatement ps = conn.prepareStatement(query);
	             ResultSet rs = ps.executeQuery()) 
	        {
	            while (rs.next()) 
	            {
	                SelectItem rr = new SelectItem();

	                String bankName = rs.getString("bank_name");
	                String bankBranch = rs.getString("bank_branch");

	                rr.setLabel((bankName != null ? bankName : "") + "/" +
	                			(bankBranch != null ? bankBranch : ""));

	                rr.setValue(rs.getString("id"));

	                iL.add(rr);
	            }
	        }
	    }
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }

	    return iL;
	}
	
	
	
	public ArrayList<LoanInfo> activeLoanListByLoanType(int branchId, String loanType, Connection conn, String frequency, 
														String dsaCode)
	{
		String query = "";
		ArrayList<LoanInfo> list = new ArrayList<>();
		Statement stt = null; 
		ResultSet ss = null;
		
		try 
		{
			stt = conn.createStatement();
			
			query = "select * from " + loanType + " where status = 'Approve' and paidStatus = 'unpaid' and "
				  + "restructure_status = 'no' ";
				
//			if (!frequency.equalsIgnoreCase("all")) 
//			{
//				query += " and frequency in ('" + frequency + "','" + frequency + ".0') ";
//			}
//			
//			if (!dsaCode.equalsIgnoreCase("all")) 
//			{
//				query += " and dsacode='" + dsaCode + "' ";
//			}
			
			query = selectQueWithBranch(query, branchId) + " order by loanNo asc";
			
			ss = stt.executeQuery(query);
			while (ss.next()) 
			{
				LoanInfo info = new LoanInfo();
				String shno = "";
				
				if (loanType.equals("grouploan")) 
				{
					shno = ss.getString("teamLeader");
					info.setLoanNo(ss.getString("sub_loan_no"));
				} 
				else
				{
					shno = ss.getString("shareholderno");
					info.setLoanNo(ss.getString("loanNo"));
				}
				
				UserInfo uu = db.userInfoByShareHolderNo(shno, conn);
			
				info.setShareHolderNo(shno);
				info.setApplicantName(uu.getFirst_applicantName());
				info.setFatherHusbandName(uu.getFather_husband_name());
				info.setContactNo(uu.getContactNo());
				info.setId(ss.getString("id"));
				info.setLoanAmount(ss.getString("loanAmount"));
				
				double interestAmount = (ss.getDouble("terms") * ss.getDouble("emi")) - ss.getDouble("loanAmount");
				info.setInterestAmount(String.valueOf(interestAmount));
				
				info.setApprovalDate(ss.getDate("approvalDate"));
				info.setStrApprovalDate(dateObj.format(ss.getDate("approvalDate")));
				
				list.add(info);
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		} 
		
		finally 
		{
			try 
			{
				if (stt != null) stt.close();
				if (ss != null) ss.close();
			} 
			catch (Exception e2) 
			{
				e2.printStackTrace();
			}
		}
		
		return list;
	}
	
	
	
	public int addLoanCloser(String receipt_no, double totalPenaltyRemain, double totalIntrestRemain, double totalChqChargeRemain, 
							 double totalRemaining, double discount, double amount, java.util.Date date, String paymentMode, 
							 String transfer, String loanNo, double discountInterest, double discountPenalty, 
							 double discountChequeBounce, String chequeNo, java.util.Date chequeDate, double totalPrincipalRemain, 
							 String dsaName, int branchId, double foreclosureCharges, String rating, String remark, 
							 double CalculatedIntAmtAfterTenureTillDate, double disCalIntAmtAfterTenureTillDate)
	{
		int i = 0;
		Timestamp tt = null;
		Timestamp ss = null;
		
		if(chequeDate != null )
		{
			tt = new Timestamp(chequeDate.getTime());	
		}
		
		if(date != null )
		{
			ss = new Timestamp(date.getTime());
		}
		
		try(Connection conn = DataBaseConnection.JAVAConnection();) 
		{     
			String query="insert into add_loan_closer(receipt_no, total_penalty_remain, total_intrest_remain, "
					   + "total_chq_charge_remain, total_remaining, discount, amount, paid_date, payment_mode, transfer, "
					   + "loan_no, discount_penalty, discount_chequebounce, discount_interest, cheque_date, cheque_no,"
					   + "principal_amount_remaining, dsa_name, branch_id, foreclosure_charges, rating, remark, "
					   + "calIntAmt_aftertenure, disIntAmt_aftertenure) "
					   + "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";	
		    
			PreparedStatement stt = conn.prepareStatement(query);
			
		    stt.setString(1, receipt_no);
		    stt.setDouble(2, totalPenaltyRemain);
		    stt.setDouble(3, totalIntrestRemain);
		    stt.setDouble(4, totalChqChargeRemain);
		    stt.setDouble(5, totalRemaining);
		    stt.setDouble(6, discount);
		    stt.setDouble(7, amount);
		    stt.setTimestamp(8, ss);
		    stt.setString(9, paymentMode);
		    stt.setString(10, transfer);
		    stt.setString(11, loanNo);
		    stt.setDouble(12, discountPenalty);
		    stt.setDouble(13, discountChequeBounce);
		    stt.setDouble(14, discountInterest);
		    stt.setTimestamp(15, tt);
		    stt.setString(16, chequeNo);
		    stt.setDouble(17, totalPrincipalRemain);
		    stt.setString(18, dsaName);
		    stt.setInt(19, branchId);
		    stt.setDouble(20, foreclosureCharges);
		    stt.setString(21, rating);
		    stt.setString(22, remark);
		    stt.setDouble(23, CalculatedIntAmtAfterTenureTillDate);
		    stt.setDouble(24, disCalIntAmtAfterTenureTillDate);
		    
		    i = stt.executeUpdate();
		    if(i > 0)
		    {
		    	db.dataInsertInQueryTable(String.valueOf(stt), conn);
		    }
		}
		catch (Exception e) 
		{
			 e.printStackTrace();
			 db.fileCreation(e.getMessage());
		}
		return i;
	}
	
	
	
	public ArrayList<PreviousLoanInfo> selectLoanPreLoanNo(Connection conn, String disbloanNo, String loanType, String memeberNo) 
	{
		String loanNo = "";
		ArrayList<PreviousLoanInfo> list = new ArrayList<>();
		
		String[] queries = 
		{
			"SELECT loanNo FROM personalloan WHERE shareholderno = ? AND paidStatus = 'unpaid' AND status = 'Approve' ",
			"SELECT loanNo FROM secure_loan WHERE shareholderno = ? AND paidStatus = 'unpaid' AND status = 'Approve' ",
			"SELECT sub_loan_no FROM grouploan WHERE teamLeader = ? AND paidStatus = 'unpaid' AND status = 'Approve' " 
		};

		for (String query : queries) 
		{
			try (PreparedStatement st = conn.prepareStatement(query + " AND restructure_status = 'no' ")) 
			{
				st.setString(1, memeberNo);

				try (ResultSet rs = st.executeQuery()) 
				{
					while (rs.next()) 
					{
						PreviousLoanInfo li = new PreviousLoanInfo();
						
						loanNo = rs.getString(1);

						System.out.println("Checking PreClose Loan = " + loanNo + " | Current Disburse Loan = " + disbloanNo);

						if (!loanNo.equals(disbloanNo)) 
						{
							System.out.println("OLD LOAN SELECTED FOR PRECLOSURE = " + loanNo);
							li.setLoanNo(loanNo);
							li.setLoanCategory(loanTypeByLoanNo(loanNo));
							list.add(li);
						}
					}
				}
			}
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		}

		if (list.isEmpty()) 
		{
		    System.out.println("NO OLD LOAN FOUND FOR PRECLOSURE");
		}
		return list;
	}
	
	
	
	public ArrayList<SalaryCreationInfo> manageSalaryList(Connection conn, String selectedMonth, String selectedYear, 
														  String selectedName, int branchId)
	{	
		int i = 1;
		String query = "";
		ArrayList<SalaryCreationInfo>list = new ArrayList<>();
	
		DataBaseMethods dbs = new DataBaseMethods();
		DataBaseMethodsRaviK dbr = new DataBaseMethodsRaviK();
		
		String employeeDetail = dbr.getEmployeeDetailByEmpName(conn, selectedName);
		query = "select * from emp_salary where salary_month = '" + selectedMonth + "' AND salary_year = '" + selectedYear + "' ";

		 if(branchId != -1)
		{
			query += " AND branch_id = '" + branchId + "' ";					
		}
		 
		if(!selectedName.equalsIgnoreCase("-1"))
		{
			query += " AND employee_name = '" + selectedName + "' ";
		}
		
		query += "  and salary_status = 'active' ";
				
		try(PreparedStatement st = conn.prepareStatement(query)) 
		{	
			ResultSet rr = st.executeQuery();
			while(rr.next())
			{
				SalaryCreationInfo ss = new SalaryCreationInfo();
				
				ss.setsNo(i++);
				ss.setId(rr.getInt("id"));
				ss.setEmployeeCode(rr.getString("employee_code"));
				ss.setLableEmpCode(manageEmployeeId(rr.getString("employee_code")));
				ss.setName(employeeNameBydsaCode(conn, ss.getEmployeeCode()));

			    ss.setEmployeeName(rr.getString("employee_name"));
			    if(employeeDetail != null && !employeeDetail.isEmpty())
			    {
			        String[] detail = employeeDetail.split("#", -1);
                    String dept = dbs.hrmBasicNameById(detail[0], conn);
			        ss.setDepartment(dept);
			        ss.setCity(detail[1]);
			    }	
			    
			    ss.setFuntion(rr.getString("funtion"));
				ss.setDesination(rr.getString("designation"));
				ss.setPfNumber(rr.getString("pf_number"));
				ss.setUanNo(rr.getString("uan_no"));
				ss.setState(rr.getString("state"));
				ss.setEsicNo(rr.getString("esic_no"));
				ss.setBankName(rr.getString("bank_name"));
				ss.setTotalDays(rr.getInt("total_days"));
				ss.setBankAccountNo(rr.getString("bank_acc_no"));
				ss.setWorkingDays(rr.getInt("working_days"));
				
				String doj = rr.getString("doj");
				System.out.println("INPUT" + doj);
				if (doj != null && !doj.isEmpty()) 
				{
				    try 
				    {
				        SimpleDateFormat inputFormat = new SimpleDateFormat("yyyy-MM-dd");
				        SimpleDateFormat outputFormat = new SimpleDateFormat("dd/MM/yyyy");

				        java.util.Date date = inputFormat.parse(doj);

				        ss.setDateOfJoining(outputFormat.format(date));
				    } 
				    catch (ParseException e) 
				    {
				        e.printStackTrace();
				    }
				}

				ss.setLopDays(rr.getString("lop_days"));
				ss.setBasicMonth(rr.getInt("basic"));
				ss.setHouseRentAllowance(rr.getInt("house_rent_allowance"));
				ss.setSpecialAllowance(rr.getInt("special_allowance"));
				ss.setOtherAllowance(rr.getInt("other_allowance"));
				ss.setGrossSalary(rr.getInt("grand_total"));
				ss.setEmployeeEsicContribution(rr.getInt("esic_con"));
				ss.setEmployeePfContribution(rr.getInt("pf_con"));
				ss.setTotalFixedPay(rr.getInt("total_fixed_pay"));
				ss.setTotalPfDeduction(rr.getInt("total_pf_deduction"));
				ss.setTotalEsicDeduction(rr.getInt("total_esic_deduction"));
				ss.setNetCashInHand(rr.getInt("net_cash_in_hand"));
				ss.setSalaryMonth(rr.getString("salary_month"));
				ss.setSalaryYear(rr.getString("salary_year"));
				ss.setArrear(rr.getInt("arrear_salary"));
				ss.setArrearIncentive(rr.getInt("arrear_incentive"));
				ss.setIncentive(rr.getInt("incentive"));
				ss.setOthersDeduction(rr.getInt("other_deduction"));
				ss.setFuelAmount(rr.getInt("fuel_amount"));
				
				String dateStr = rr.getString("salary_pay_date");
				if (dateStr != null && !dateStr.trim().isEmpty()) 
				{
				    DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
				    LocalDateTime dateTime = LocalDateTime.parse(dateStr, inputFormatter);
				    LocalDate date = dateTime.toLocalDate();
				    DateTimeFormatter outputFormatter = DateTimeFormatter.ofPattern("dd-MM-yyyy");
				    String formattedDate = date.format(outputFormatter);

				    ss.setPayDate(formattedDate);
				} 
				else 
				{
				    ss.setPayDate(null); 
				}

				ss.setPaymentMode(rr.getString("payment_mode"));
				
				String dateStr1 =rr.getString("pay_mode_date");
				if (dateStr1 != null && !dateStr1.trim().isEmpty()) 
				{
				    DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
				    LocalDateTime dateTime = LocalDateTime.parse(dateStr, inputFormatter);
				    LocalDate date = dateTime.toLocalDate();
				    DateTimeFormatter outputFormatter = DateTimeFormatter.ofPattern("dd-MM-yyyy");
				    String formattedDate = date.format(outputFormatter);
				    
				    ss.setChequeDate(formattedDate);
				} 
				else 
				{
				    ss.setChequeDate(null);
				}
				
				ss.setChequeNo(rr.getString("pay_mode_no"));
				ss.setFromBank(rr.getString("form_bank_name"));
				ss.setDescription(rr.getString("remark"));
				ss.setPanNumber(rr.getString("pan_no"));
				ss.setStatus(rr.getString("status"));
				
				String status = rr.getString("status");
				if(!status.equals("paid"))
				{
					ss.setPayModeRen(true);
					ss.setPrintRen(false);
				}
				else 
				{		
					ss.setPayModeRen(false);
					ss.setPrintRen(true);
				}
					
				ss.setBranch(dbs.branchNameByBranchId(rr.getInt("branch_id"), conn));
				
				if ("paid".equalsIgnoreCase(ss.getStatus())) 
				{
					ss.setRenDelete(true);
				}
				else 
				{
					ss.setRenDelete(false);
				}
				
				list.add(ss);	
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return list;
	}
	
	
	
	public String manageEmployeeId(String empId)
	{
		String label = "";
		String fmt = selectEmpFor();
		
		if(!"".equalsIgnoreCase(fmt)) 
		{
			if(empId.contains("-")) 
			{
				String data = empId;
				String[] arr = data.split("-");

				String second = arr[1]; 
					
				label = (fmt + "-" + second);
			}
			else 
			{
				label = (fmt + "-" + empId);
			}
		}
		else 
		{
			label = empId;
		}
		
		return label;
	}
	
	
	
	public String selectEmpFor()
	{
		String fmt = "";
		try(Connection conn = DataBaseConnection.JAVAConnection()) 
		{	
			String qq = "select * from firm_detail";
				
			try(PreparedStatement st = conn.prepareStatement(qq)) 
			{
				ResultSet rr = st.executeQuery();
				
				if(rr.next())
				{
					fmt = rr.getString("emp_id_format");
				}	
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return fmt;
	}
	
	
	
	public boolean checkLoanClosed(String loanNo, String loanType)
	{
		try (Connection conn = DataBaseConnection.JAVAConnection())
		{
			String qq = "";
			
			if(loanType.equalsIgnoreCase("grouploan"))
			{
				qq = "SELECT paidStatus FROM " + loanType + " where sub_loan_no = '" + loanNo + "' and status = 'approve'";
			}
			else
			{
				qq = "SELECT paidStatus FROM " + loanType + " where loanNo = '" + loanNo + "' and status = 'approve'";
			}

			try (PreparedStatement ps = conn.prepareStatement(qq)) 
			{
				ResultSet rs = ps.executeQuery();
				if (rs.next()) 
				{
					String status = rs.getString("paidStatus");
					if (status.equalsIgnoreCase("paid")) 
					{
						return true;
					}
					else 
					{
						return false;
					}
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return false;
	}
	
	
	
	public boolean isDLTMessageApi(Connection conn) 
	{
	    String query = "SELECT 1 FROM message_api WHERE type = 'DLT' LIMIT 1";

	    try (PreparedStatement ps = conn.prepareStatement(query);
	         ResultSet rs = ps.executeQuery()) 
	    {
	        return rs.next();
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	        return false;
	    }
	}
	
	
	
	public double bankTransferForCashBook(int branchId,java.util.Date result, java.util.Date result2, String paymentMode, 
										  Connection con, String bank)
    {    
    	Statement st = null;
    	ResultSet rr = null;

        try
        {
             st = con.createStatement();
             String query = "";
             
             if(paymentMode.equalsIgnoreCase("out"))
             {
            	 query = "select sum(amount) from debit_current_asset_bank where type = 'withdrawl' and "
            	 	   + "transfer_to_bank != 'N/A' ";
            
            	 if (!bank.equalsIgnoreCase("All")) 
                 {
                	 query += "and bank_name = '"+bank+"' ";
                 }
             }
             else
             {
            	 query = "select sum(amount) from debit_current_asset_bank where type = 'withdrawl' ";   
                 
                 if (!bank.equalsIgnoreCase("All")) 
                 {
                	 query += "and transfer_to_bank = '"+bank+"' ";
                 }
             }
             
             query += "  and date >='"+new SimpleDateFormat("yyyy-MM-dd").format(result)+"' and date <='" 
            	   + new SimpleDateFormat("yyyy-MM-dd").format(result2)+"' ";
            
             rr = st.executeQuery(selectQueWithBranch(query, 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) 
            {
            	e2.printStackTrace();
            }
        }
        return 0;
    }
	
	
	
	private double getLoanPaidAmountOptimized(String loanNo, Connection conn) 
	{
	    double paidAmount = 0;

	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try 
	    {
	        String sql = "SELECT COALESCE(SUM(amount),0) FROM loanpayment WHERE loanNo = ? AND restructure = 'No' " +
		                 "AND receipt_status = 'approved' AND chequeStatus = 'cleared' ";

	        ps = conn.prepareStatement(sql);
	        ps.setString(1, loanNo);

	        rs = ps.executeQuery();

	        if (rs.next()) 
	        {
	            paidAmount = rs.getDouble(1);
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();

	    }
	    finally 
	    {
	        try
	        {
	            if (rs != null) rs.close();
	        } 
	        catch (Exception e) 
	        {
	        	e.printStackTrace();
	        }

	        try 
	        {
	            if (ps != null) ps.close();
	        } 
	        catch (Exception e) 
	        {
	        	e.printStackTrace();
	        }
	    }

	    return paidAmount;
	}
	
	
	
	private HashMap<Integer, Date> getLoanPaidDateMap(String loanNo, Connection conn) 
	{
	    HashMap<Integer, Date> map = new HashMap<Integer, Date>();

	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try 
	    {
	        String sql = "SELECT emi_no, pay_date FROM loanpayment WHERE loanNo = ? AND restructure = 'No' " +
		                 "AND receipt_status = 'approved' AND chequeStatus = 'cleared' ORDER BY emi_no";

	        ps = conn.prepareStatement(sql);
	        ps.setString(1, loanNo);

	        rs = ps.executeQuery();

	        while (rs.next()) 
	        {
	            int emiNo = rs.getInt("emi_no");

	            if (!map.containsKey(emiNo)) 
	            {
	                map.put(emiNo, rs.getDate("pay_date"));
	            }
	        }

	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();

	    } 
	    finally 
	    {
	        try
	        {
	            if (rs != null) rs.close();
	        } 
	        catch (Exception e) {}

	        try 
	        {
	            if (ps != null) ps.close();
	        } 
	        catch (Exception e) {}
	    }

	    return map;
	}
	
	
	
	private HashMap<Integer, Date> getGroupLoanEmiDateMap(String loanNo, Connection conn) 
	{
	    HashMap<Integer, Date> map = new HashMap<Integer, Date>();

	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try 
	    {
	        String sql = "SELECT emi_no, emi_date FROM grouploan_emi WHERE sub_loan_no = ? ORDER BY emi_no";

	        ps = conn.prepareStatement(sql);
	        ps.setString(1, loanNo);

	        rs = ps.executeQuery();

	        while (rs.next()) 
	        {
	            int emiNo = rs.getInt("emi_no");
	            Date emiDate = rs.getDate("emi_date");

	            map.put(emiNo, emiDate);
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    } 
	    finally 
	    {
	        try 
	        {
	            if (rs != null) rs.close();
	        } 
	        catch (Exception e) {}
	        
	        try 
	        {
	            if (ps != null) ps.close();
	        } 
	        catch (Exception e) {}
	    }

	    return map;
	}
	
	
	
	public ArrayList<LoanInfo> loanInstallment(String loanType, String loanNo, Connection conn) 
	{
	    ArrayList<LoanInfo> list = new ArrayList<LoanInfo>();

	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try 
	    {
	        if (loanType == null || loanType.trim().equals("")) 
	        {
	            return list;
	        }

	        if (loanNo == null || loanNo.trim().equals("")) 
	        {
	            return list;
	        }

	        if (loanType.equalsIgnoreCase("vehicleLoan")) 
	        {
	            loanType = "vechicleLoan";
	        }

	        if (!loanType.equalsIgnoreCase("grouploan") && !loanType.equalsIgnoreCase("personalloan") && 
	        	!loanType.equalsIgnoreCase("secure_loan") && !loanType.equalsIgnoreCase("vechicleLoan") && 
	        	!loanType.equalsIgnoreCase("vehicleLoan")) 
	        {
	            return list;
	        }

	        String query;

	        if (loanType.equalsIgnoreCase("grouploan")) 
	        {
	            query = "SELECT * FROM grouploan WHERE sub_loan_no = ? AND status = 'Approve'";
	        } 
	        else 
	        {
	            query = "SELECT * FROM " + loanType + " WHERE loanNo = ? AND status = 'Approve'";
	        }

	        ps = conn.prepareStatement(query);
	        ps.setString(1, loanNo);

	        rs = ps.executeQuery();

	        if (!rs.next()) 
	        {
	            return list;
	        }

	        String emiType = rs.getString("emiType");

	        int terms = rs.getInt("terms");
	        int frequency = rs.getInt("frequency");

	        double originalEmi = rs.getDouble("emi");
	        double loanAmount = rs.getDouble("loanAmount");
	        double interestRate = rs.getDouble("interest");

	        Date approvalDate = rs.getDate("approvalDate");

	        Date emiDueDate = rs.getDate("emi_due_date");

	        double discount = rs.getDouble("discount");


	        HashMap<Integer, Date> groupEmiDateMap = new HashMap<Integer, Date>();

	        boolean checkGroupLoanEmi = false;

	        if (loanType.equalsIgnoreCase("grouploan")) 
	        {
	            groupEmiDateMap = getGroupLoanEmiDateMap(loanNo, conn);

	            if (!groupEmiDateMap.isEmpty()) 
	            {
	                checkGroupLoanEmi = true;
	            }
	        }
	        
	        int firstGroupEmiNo = 1;

	        if (checkGroupLoanEmi) 
	        {
	            for (Integer emiNo : groupEmiDateMap.keySet()) 
	            {
	                if (emiNo != null) 
	                {
	                    firstGroupEmiNo = emiNo.intValue();
	                    break;
	                }
	            }
	        }

	        HashMap<Integer, Date> paidDateMap = getLoanPaidDateMap(loanNo, conn);

	        double paidAmount = getLoanPaidAmountOptimized(loanNo, conn);

	        int j = 365 / frequency;

	        java.util.Date offcdt = null;

	        if (emiDueDate == null) 
	        {
	            offcdt = approvalDate;
	        } 
	        else 
	        {
	            offcdt = emiDueDate;
	        }

	        if (emiType != null && emiType.equalsIgnoreCase("reducing")) 
	        {
	            java.util.Date installDate;

	            if (emiDueDate == null) 
	            {
	                installDate = approvalDate;
	            } 
	            else 
	            {
	                installDate = emiDueDate;

	                if (frequency == 12) 
	                {
	                    installDate = new Date(installDate.getTime());

	                    installDate.setMonth(installDate.getMonth() - 1);
	                } 
	                else 
	                {
	                    installDate = new Date(installDate.getTime());
	                    installDate.setDate(installDate.getDate() - j);
	                }
	            }

	            int y = 1;

	            double emi = originalEmi;

	            for (int i = 1; i <= terms; i++) 
	            {
	                LoanInfo ll = new LoanInfo();

	                ll.setSno(i);

	                double currentEmi = emi;

	                if (i == terms) 
	                {
	                    currentEmi = emi - discount;
	                }

	                ll.setInstallAmount(currentEmi);

	                if (paidAmount >= currentEmi) 
	                {
	                    ll.setStatus("Paid");
	                    ll.setPaidAmount(currentEmi);
	                    ll.setDueAmount(0);

	                    paidAmount = Double.parseDouble(df.format(paidAmount - currentEmi));
	                } 
	                else 
	                {
	                    ll.setStatus("Due");
	                    ll.setPaidAmount(paidAmount);
	                    ll.setDueAmount(Double.parseDouble(df.format(currentEmi - paidAmount)));

	                    paidAmount = 0;
	                }

	                java.util.Date dt = new Date(installDate.getTime());

	                if (emiDueDate == null) 
	                {
	                    dt.setDate(dt.getDate() + j);
	                }
	                else 
	                {
	                    if (frequency == 12 || frequency == 4 || frequency == 2 || frequency == 1) 
	                    {
	                        if (frequency == 4) 
	                        {
	                            y = 4;
	                        } 
	                        else if (frequency == 2) 
	                        {
	                            y = 6;
	                        }
	                        else if (frequency == 1) 
	                        {
	                            y = 12;
	                        }
	                        else if (frequency == 12)
	                        {
	                            y = 1;
	                        }

	                        offcdt = createDate2(offcdt, y, i, offcdt.getDate(), emiDueDate);
	                    } 
	                    else 
	                    {
	                        dt.setDate(dt.getDate() + j);
	                    }
	                }

	                if (checkGroupLoanEmi && firstGroupEmiNo == i) 
	                {
	                    Date groupDate = groupEmiDateMap.get(i);

	                    if (groupDate != null) 
	                    {
	                        dt = groupDate;
	                    }

	                    firstGroupEmiNo++;
	                }

	                if (!checkGroupLoanEmi && (frequency == 12 || frequency == 4 || frequency == 2 || frequency == 1)) 
	                {
	                    ll.setStrInstlmntDate(sf.format(offcdt));

	                    java.util.Date ss = sf.parse(ll.getStrInstlmntDate());

	                    ll.setInstallDate(ss);
	                } 
	                else 
	                {
	                    ll.setStrInstlmntDate(sf.format(dt));

	                    java.util.Date ss = sf.parse(ll.getStrInstlmntDate());

	                    ll.setInstallDate(ss);
	                }

	                /*
	                 * PAID DATE
	                 */

	                Date paidDate = paidDateMap.get(i);

	                ll.setPaidDate(paidDate);

	                ll.setEmiPaidDate(paidDate);

	                if (paidDate != null) 
	                {
	                    ll.setEmiPaidDateStr(new SimpleDateFormat("dd/MM/yyyy").format(paidDate));
	                } 
	                else 
	                {
	                    ll.setEmiPaidDateStr("- - - -");
	                }

	                ll.setPaidDateStr("- - - -");

	                double firstInterestAmount = (loanAmount * interestRate) / (100 * frequency);

	                ll.setInterestAmount(df.format(firstInterestAmount));

	                ll.setInterest(Double.parseDouble(df.format(firstInterestAmount)));

	                double paidPrincipalAmount = currentEmi - firstInterestAmount;

	                ll.setPrincipalAmount(df.format(paidPrincipalAmount));

	                ll.setPrincipal(Double.parseDouble(df.format(paidPrincipalAmount)));

	                double principalRemaining = loanAmount - paidPrincipalAmount;

	                ll.setPrincipalBalanceStr(df.format(principalRemaining));

	                list.add(ll);

	                loanAmount = principalRemaining;
	            }
	        }
	        else 
	        {
	            String emiMode = "";

	            if (loanType.equalsIgnoreCase("grouploan")) 
	            {
	                emiMode = "both";
	            } 
	            else 
	            {
	                emiMode = rs.getString("emi_mode");
	            }

	            if (emiMode != null && emiMode.equalsIgnoreCase("both")) 
	            {
	                double totalInterest = Double.parseDouble(df.format((originalEmi * terms) - loanAmount));

	                double interestMonthly = totalInterest / terms;

	                Date installDate;

	                if (emiDueDate == null) 
	                {
	                    installDate = approvalDate;
	                } 
	                else 
	                {
	                    installDate = new Date(emiDueDate.getTime());

	                    if (frequency == 12) 
	                    {
	                        installDate.setMonth(installDate.getMonth() - 1);
	                    }
	                    else 
	                    {
	                        installDate.setDate(installDate.getDate() - j);
	                    }
	                }

	                int y = 1;

	                double emi = originalEmi;

	                for (int i = 1; i <= terms; i++) 
	                {
	                    LoanInfo ll = new LoanInfo();

	                    ll.setSno(i);

	                    ll.setEmiPaidDate1(selectEmiPaidDate(i, loanNo, conn));

	                    Date paidDate = paidDateMap.get(i);

	                    ll.setPaidDate(paidDate);

	                    ll.setEmiPaidDate(paidDate);

	                    double currentEmi = emi;

	                    if (i == terms) 
	                    {
	                        currentEmi = emi - discount;
	                    }

	                    ll.setInstallAmount(currentEmi);

	                    if (paidAmount >= currentEmi) 
	                    {
	                        ll.setStatus("Paid");

	                        ll.setPaidAmount(currentEmi);

	                        ll.setDueAmount(0);

	                        paidAmount = Double.parseDouble(df.format(paidAmount - currentEmi));
	                    }
	                    else 
	                    {
	                        ll.setStatus("Due");

	                        ll.setPaidAmount(paidAmount);

	                        ll.setDueAmount(Double.parseDouble(df.format(currentEmi - paidAmount)));

	                        paidAmount = 0;
	                    }

	                    Date dt = new Date(installDate.getTime());

	                    if (emiDueDate == null) 
	                    {
	                        dt.setDate(dt.getDate() + j);
	                    } 
	                    else 
	                    {
	                        if (frequency == 12 || frequency == 4 || frequency == 2 || frequency == 1) 
	                        {
	                            if (frequency == 4)
	                            {
	                                y = 4;
	                            }
	                            else if (frequency == 2)
	                            {
	                                y = 6;
	                            }
	                            else if (frequency == 1)
	                            {
	                                y = 12;
	                            }
	                            else if (frequency == 12) 
	                            {
	                                y = 1;
	                            }

	                            offcdt = createDate2(offcdt, y, i, offcdt.getDate(), emiDueDate);
	                        } 
	                        else 
	                        {
	                            dt.setDate(dt.getDate() + j);
	                        }
	                    }
	                    if (!checkGroupLoanEmi && (frequency == 12 || frequency == 4 || frequency == 2 || frequency == 1)) 
	                    {
	                        ll.setStrInstlmntDate(sf.format(offcdt));

	                        java.util.Date ss = sf.parse(ll.getStrInstlmntDate());

	                        ll.setInstallDate(ss);
	                    } 
	                    else 
	                    {
	                        if (checkGroupLoanEmi) 
	                        {
	                            Date groupDate = groupEmiDateMap.get(i);

	                            if (groupDate != null) 
	                            {
	                                dt = groupDate;
	                            }
	                        }

	                        ll.setStrInstlmntDate(sf.format(dt));

	                        java.util.Date ss = sf.parse(ll.getStrInstlmntDate());

	                        ll.setInstallDate(ss);
	                    }

	                    ll.setPaidDateStr("- - - -");

	                    ll.setInterestAmount(df.format(interestMonthly));

	                    ll.setInterest(Double.parseDouble(df.format(interestMonthly)));

	                    double paidPrincipalAmount = currentEmi - interestMonthly;

	                    ll.setPrincipalAmount(df.format(paidPrincipalAmount));

	                    ll.setPrincipal(Double.parseDouble(df.format(paidPrincipalAmount)));

	                    list.add(ll);
	                }
	            }
	            else 
	            {
	                Date installDate;

	                if (emiDueDate == null) 
	                {
	                    installDate = approvalDate;
	                } 
	                else 
	                {
	                    installDate = new Date(emiDueDate.getTime());

	                    if (frequency == 12) 
	                    {
	                        installDate.setMonth(installDate.getMonth() - 1);
	                    }
	                    else if (frequency == 4) 
	                    {
	                        installDate.setMonth(installDate.getMonth() - 3);
	                    } 
	                    else 
	                    {
	                        installDate.setDate(installDate.getDate() - j);
	                    }
	                }

	                double totalInterest = Double.parseDouble(df.format(originalEmi * terms));

	                double interestMonthly = totalInterest / terms;

	                int y = 1;

	                boolean chk = true;

	                double emi = originalEmi;

	                for (int i = 1; i <= terms; i++) 
	                {
	                    LoanInfo ll = new LoanInfo();

	                    ll.setSno(i);

	                    double currentEmi = emi;

	                    if (i == terms) 
	                    {
	                        currentEmi = emi - discount;
	                    }

	                    ll.setInstallAmount(currentEmi);

	                    if (paidAmount >= currentEmi) 
	                    {
	                        ll.setStatus("Paid");

	                        ll.setPaidAmount(currentEmi);

	                        ll.setDueAmount(0);

	                        paidAmount = Double.parseDouble(df.format(paidAmount - currentEmi));
	                    }
	                    else 
	                    {
	                        ll.setStatus("Due");

	                        ll.setPaidAmount(paidAmount);

	                        ll.setDueAmount(Double.parseDouble(df.format(currentEmi - paidAmount)));

	                        paidAmount = 0;
	                    }

	                    Date dt = new Date(installDate.getTime());

	                    if (emiDueDate == null) 
	                    {
	                        dt.setDate(dt.getDate() + j);
	                    } 
	                    else 
	                    {
	                        if (frequency == 12 || frequency == 4 || frequency == 2 || frequency == 1) 
	                        {
	                            if (frequency == 4) 
	                            {
	                                y = 4;
	                            } 
	                            else if (frequency == 2) 
	                            {
	                                y = 6;
	                            } 
	                            else if (frequency == 1) 
	                            {
	                                y = 12;
	                            } 
	                            else if (frequency == 12) 
	                            {
	                                y = 1;
	                            }

	                            offcdt = createDate2(offcdt, y, i, offcdt.getDate(), emiDueDate);
	                        } 
	                        else 
	                        {
	                            dt.setDate(dt.getDate() + j);
	                        }
	                    }

	                    if (!checkGroupLoanEmi && (frequency == 12 || frequency == 4 || frequency == 2 || frequency == 1)) 
	                    {
	                        ll.setStrInstlmntDate(sf.format(offcdt));

	                        java.util.Date ss = sf.parse(ll.getStrInstlmntDate());

	                        ll.setInstallDate(ss);
	                    } 
	                    else 
	                    {
	                        if (checkGroupLoanEmi) 
	                        {
	                            Date groupDate = groupEmiDateMap.get(i);

	                            if (groupDate != null) 
	                            {
	                                dt = groupDate;
	                            }
	                        }

	                        ll.setStrInstlmntDate(sf.format(dt));

	                        java.util.Date ss = sf.parse(ll.getStrInstlmntDate());

	                        ll.setInstallDate(ss);
	                    }

	                    ll.setPaidDateStr("- - - -");

	                    ll.setInterestAmount(df.format(interestMonthly));

	                    ll.setInterest(Double.parseDouble(df.format(interestMonthly)));

	                    double paidPrincipalAmount = currentEmi - interestMonthly;

	                    ll.setPrincipalAmount(df.format(paidPrincipalAmount));

	                    ll.setPrincipal(Double.parseDouble(df.format(paidPrincipalAmount)));

	                    list.add(ll);
	                }
	            }
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    } 
	    
	    finally 
	    {
	        try 
	        {
	            if (rs != null) 
	            {
	                rs.close();
	            }
	        } 
	        catch (Exception e) {}

	        try 
	        {
	            if (ps != null) 
	            {
	                ps.close();
	            }
	        } 
	        catch (Exception e) {}
	    }

	    return list;
	}

	
	
	public ArrayList<DailyInfo> dailyDemandMethod(int branchId, java.util.Date searchDate, String dsaCode, boolean checkDl, 
												  Connection conn) 
	{
	    ArrayList<DailyInfo> list = new ArrayList<DailyInfo>();

	    try 
	    {
	        String shareholderQuery = "SELECT first_applicantName, father_husband_name, shareholderno, mobile, address, center " 
	        						+ "FROM shareholder_info WHERE branch_id=? ";

	        if (!dsaCode.equalsIgnoreCase("all")) 
	        {
	            shareholderQuery += " AND dsacode=? ";
	        }

	        PreparedStatement psShareholder = conn.prepareStatement(shareholderQuery);

	        psShareholder.setInt(1, branchId);

	        if (!dsaCode.equalsIgnoreCase("all")) 
	        {
	            psShareholder.setString(2, dsaCode);
	        }

	        ResultSet rsShareholder = psShareholder.executeQuery();

	        HashMap<String, DailyInfo> shareholderMap = new HashMap<String, DailyInfo>();

	        HashMap<String, String> centerMap = new HashMap<String, String>();

	        while (rsShareholder.next()) 
	        {
	            DailyInfo dd = new DailyInfo();

	            String shareholderNo = rsShareholder.getString("shareholderno");

	            dd.setName(rsShareholder.getString("first_applicantName"));

	            dd.setFatherHusbandName(rsShareholder.getString("father_husband_name"));

	            dd.setShareHolderNo(shareholderNo);

	            dd.setMobileNo(rsShareholder.getString("mobile"));

	            dd.setAddress(rsShareholder.getString("address"));

	            dd.setCenter(rsShareholder.getString("center"));

	            if (dd.getCenter() != null && !dd.getCenter().trim().equals("")) 
	            {
	                String centerName = centerMap.get(dd.getCenter());

	                if (centerName == null) 
	                {
	                    centerName = db.centerNameById(dd.getCenter(), conn);

	                    if (centerName == null) 
	                    {
	                        centerName = "";
	                    }

	                    centerMap.put(dd.getCenter(), centerName);
	                }

	                dd.setCenterName(centerName);
	            }

	            dd.setPlDetail("");
	            dd.setGlDetail("");
	            dd.setSlDetail("");
	            dd.setDdDetail("");
	            dd.setRdDetail("");

	            dd.setTotalPl(0);
	            dd.setTotalGl(0);
	            dd.setTotalSl(0);
	            dd.setTotalDd(0);
	            dd.setTotalRd(0);
	            dd.setTotal(0);

	            shareholderMap.put(shareholderNo, dd);
	        }

	        rsShareholder.close();
	        psShareholder.close();

	        

	        processLoanDemandTable("personalloan", false, branchId, dsaCode, searchDate, checkDl, shareholderMap, conn);

	        processLoanDemandTable("grouploan", true, branchId, dsaCode, searchDate, checkDl, shareholderMap, conn);

	        processLoanDemandTable("secure_loan", false, branchId, dsaCode, searchDate, checkDl, shareholderMap, conn);

	        processDepositDemand("DD", searchDate, shareholderMap, conn);

	        processDepositDemand("RD", searchDate, shareholderMap, conn);


	        
	        int count = 1;

	        for (DailyInfo dd : shareholderMap.values()) 
	        {
	            int total = dd.getTotalPl() + dd.getTotalGl() + dd.getTotalSl() + dd.getTotalDd() + dd.getTotalRd();

	            dd.setTotal(total);

	            if (total > 0) 
	            {
	                dd.setSno(count++);

	                list.add(dd);
	            }
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    } 

	    return list;
	}
	
	
	
	private void processLoanDemandTable(String tableName, boolean groupLoan, int branchId, String dsaCode, 
									    java.util.Date searchDate, boolean checkDl, HashMap<String, DailyInfo> shareholderMap, 
									    Connection conn) 
	{
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try 
	    {
	        String shareholderColumn;

	        if (groupLoan) 
	        {
	            shareholderColumn = "teamLeader";
	        } 
	        else
	        {
	            shareholderColumn = "shareholderno";
	        }

	        String loanNoColumn;

	        if (groupLoan) 
	        {
	            loanNoColumn = "sub_loan_no";
	        }
	        else
	        {
	            loanNoColumn = "loanNo";
	        }

	        String query = "SELECT l." + shareholderColumn + ", l." + loanNoColumn + ", l.frequency, l.terms, l.emi_due_date, " +
			               "l.approvalDate, l.emi, l.discount FROM " + tableName + " l INNER JOIN shareholder_info s " +
			               "ON s.shareholderno = l." + shareholderColumn + " WHERE s.branch_id = ? AND l.status = 'Approve' " +
			               "AND l.paidStatus = 'unpaid' AND l.approvalDate <= ? AND l.restructure_status = 'no' ";

	        if (!dsaCode.equalsIgnoreCase("all")) 
	        {
	            query += " AND s.dsacode = ? ";
	        }

	        if (!checkDl) 
	        {
	            query += " AND l.frequency NOT IN ('365','365.0') ";
	        }

	        ps = conn.prepareStatement(query);

	        int index = 1;

	        ps.setInt(index++, branchId);

	        ps.setDate(index++, new java.sql.Date(searchDate.getTime()));

	        if (!dsaCode.equalsIgnoreCase("all")) 
	        {
	            ps.setString(index++, dsaCode);
	        }

	        rs = ps.executeQuery();

	        
	        
	        ArrayList<LoanCandidate> candidates = new ArrayList<LoanCandidate>();

	        while (rs.next()) 
	        {
	            String shareholderNo = rs.getString(shareholderColumn);

	            if (!shareholderMap.containsKey(shareholderNo)) 
	            {
	                continue;
	            }

	            LoanCandidate candidate = new LoanCandidate();

	            candidate.shareholderNo = shareholderNo;	            
	            candidate.tableName = tableName; 
	            candidate.loanNo = rs.getString(loanNoColumn);
	            candidate.frequency = rs.getInt("frequency");
	            candidate.terms = rs.getInt("terms");
	            candidate.emiDueDate = rs.getDate("emi_due_date");
	            candidate.approvalDate = rs.getDate("approvalDate");
	            candidate.emi = rs.getDouble("emi");
	            candidate.discount = rs.getDouble("discount");
	            candidate.groupLoan = groupLoan;
	            
	            candidates.add(candidate);
	        }

	        HashSet<String> disbursedLoans = getDisbursedLoans(candidates, conn);

	        for (LoanCandidate candidate : candidates) 
	        {
	            if (!disbursedLoans.contains(candidate.loanNo)) 
	            {
	                continue;
	            }

	            LoanDemandInfo demand = getDemandUsingOldLoanInstallment(candidate, searchDate, conn);

	            if (demand == null) 
	            {
	                continue;
	            }

	            if (demand.getDueAmount() <= 0) 
	            {
	                continue;
	            }

	            DailyInfo dd = shareholderMap.get(candidate.shareholderNo);

	            if (dd == null)
	            {
	                continue;
	            }

	            String detail = demand.getLoanNo() + "=" + demand.getDueAmount();

	            if (tableName.equalsIgnoreCase("personalloan")) 
	            {
	                dd.setPlDetail(appendDetail(dd.getPlDetail(), detail));

	                dd.setTotalPl(dd.getTotalPl() + (int) demand.getDueAmount());
	            }
	            else if (tableName.equalsIgnoreCase("grouploan")) 
	            {
	                dd.setGlDetail(appendDetail(dd.getGlDetail(), detail));

	                dd.setTotalGl(dd.getTotalGl() + (int) demand.getDueAmount());
	            } 
	            else if (tableName.equalsIgnoreCase("secure_loan")) 
	            {
	                dd.setSlDetail(appendDetail(dd.getSlDetail(), detail));

	                dd.setTotalSl(dd.getTotalSl() + (int) demand.getDueAmount());
	            }
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    } 
	    finally 
	    {
	        try 
	        {
	            if (rs != null) 
	            {
	                rs.close();
	            }
	        } 
	        catch (Exception e) {}

	        try 
	        {
	            if (ps != null) 
	            {
	                ps.close();
	            }
	        } 
	        catch (Exception e) {}
	    }
	}
	
	
	
	private LoanDemandInfo getDemandUsingOldLoanInstallment(LoanCandidate candidate, java.util.Date searchDate, Connection conn) 
	{
	    try 
	    {
	        String searchDateStr = new SimpleDateFormat("dd/MM/yyyy").format(searchDate);

	        ArrayList<LoanInfo> installmentList = db.loanInstallment(candidate.groupLoan
				                                ? "grouploan"
				                                : candidate.tableName, candidate.loanNo, conn);

	        if (installmentList == null || installmentList.size() == 0) 
	        {
	            return null;
	        }

	        for (LoanInfo installment : installmentList) 
	        {
	            if (installment.getStatus() == null) 
	            {
	                continue;
	            }

	            if (!installment.getStatus().equalsIgnoreCase("Due")) 
	            {
	                continue;
	            }

	            if (installment.getStrInstlmntDate() == null) 
	            {
	                continue;
	            }

	            if (!installment.getStrInstlmntDate().equals(searchDateStr)) 
	            {
	                continue;
	            }

	            double dueAmount = installment.getDueAmount();

	            if (dueAmount <= 0) 
	            {
	                continue;
	            }

	            return new LoanDemandInfo(candidate.loanNo, dueAmount, installment.getSno());
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }

	    return null;
	}
	
	
	
	public Map<String, JSONObject> loadPaymentSources(ArrayList<String> loanNumbers, java.util.Date searchDate, 
													  Connection conn) throws Exception 
	{
	    Map<String, JSONObject> result = new java.util.HashMap<String, JSONObject>();

	    if (loanNumbers == null || loanNumbers.isEmpty()) 
	    {
	        return result;
	    }

	    if (conn == null || conn.isClosed()) 
	    {
	        throw new Exception("Database connection is closed before loadPaymentSources()");
	    }

	    StringBuilder placeholders = new StringBuilder();

	    for (int i = 0; i < loanNumbers.size(); i++) 
	    {
	        if (i > 0) 
	        {
	            placeholders.append(",");
	        }

	        placeholders.append("?");
	    }

	    String sql = "SELECT loan_no, amount, employee, pay_date FROM loan_payment_from_app " +
		             "WHERE loan_no IN (" + placeholders.toString() + ") " +
		             "AND pay_date >= ? AND pay_date < ? ORDER BY pay_date DESC";

	    java.sql.Date startDate = new java.sql.Date(searchDate.getTime());
	    java.sql.Date endDate = new java.sql.Date(searchDate.getTime() + 24L * 60L * 60L * 1000L);

	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try 
	    {
	        ps = conn.prepareStatement(sql);

	        int index = 1;

	        for (String loanNo : loanNumbers) 
	        {
	            ps.setString(index++, loanNo);
	        }

	        ps.setDate(index++, startDate);
	        ps.setDate(index++, endDate);

	        rs = ps.executeQuery();

	        while (rs.next()) 
	        {
	            String loanNo = rs.getString("loan_no");

	            if (result.containsKey(loanNo)) 
	            {
	                continue;
	            }

	            JSONObject obj = new JSONObject();

	            obj.put("loanNo", loanNo);
	            obj.put("amount", rs.getString("amount"));
	            obj.put("userId", rs.getString("employee"));
	            obj.put("empName", "");
	            obj.put("source", "by app");

	            result.put(loanNo, obj);
	        }
	    } 
	    finally 
	    {
	        try 
	        {
	            if (rs != null) 
	            {
	                rs.close();
	            }
	        } 
	        catch (Exception e) {}

	        try 
	        {
	            if (ps != null) 
	            {
	                ps.close();
	            }
	        } 
	        catch (Exception e) {}
	    }

	    ArrayList<String> remaining = new ArrayList<String>();

	    for (String loanNo : loanNumbers)
	    {
	        if (!result.containsKey(loanNo)) 
	        {
	            remaining.add(loanNo);
	        }
	    }

	    if (!remaining.isEmpty()) 
	    {
	        Map<String, JSONObject> webPayments = loadWebPayments(remaining, searchDate, conn);

	        result.putAll(webPayments);
	    }

	    return result;
	}


	
	private Map<String, JSONObject> loadWebPayments(ArrayList<String> loanNumbers, java.util.Date searchDate, 
													Connection conn) throws Exception 
	{
	    Map<String, JSONObject> result = new java.util.HashMap<String, JSONObject>();

	    if (loanNumbers == null || loanNumbers.isEmpty()) 
	    {
	        return result;
	    }

	    if (conn == null || conn.isClosed()) 
	    {
	        throw new Exception("Database connection is closed before loadWebPayments()");
	    }

	    StringBuilder placeholders = new StringBuilder();

	    for (int i = 0; i < loanNumbers.size(); i++) 
	    {
	        if (i > 0) 
	        {
	            placeholders.append(",");
	        }

	        placeholders.append("?");
	    }

	    String sql = "SELECT loanNo, amount, user_id, pay_date FROM loanpayment WHERE loanNo IN (" + placeholders.toString() +
		             ") AND pay_date >= ? AND pay_date < ? AND restructure = 'No' AND receipt_status = 'approved' " +
		             "AND chequeStatus = 'cleared' ORDER BY pay_date DESC";

	    java.sql.Date startDate = new java.sql.Date(searchDate.getTime());
	    java.sql.Date endDate = new java.sql.Date(searchDate.getTime() + 24L * 60L * 60L * 1000L);

	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try 
	    {
	        ps = conn.prepareStatement(sql);

	        int index = 1;

	        for (String loanNo : loanNumbers) 
	        {
	            ps.setString(index++, loanNo);
	        }

	        ps.setDate(index++, startDate);
	        ps.setDate(index++, endDate);

	        rs = ps.executeQuery();

	        while (rs.next()) 
	        {
	            String loanNo = rs.getString("loanNo");

	            if (result.containsKey(loanNo)) 
	            {
	                continue;
	            }

	            JSONObject obj = new JSONObject();

	            obj.put("loanNo", loanNo);
	            obj.put("amount", rs.getString("amount"));
	            obj.put("userId", rs.getString("user_id"));
	            obj.put("empName", "");
	            obj.put("source", "by web");

	            result.put(loanNo, obj);
	        }
	    } 
	    finally 
	    {
	        try 
	        {
	            if (rs != null) 
	            {
	                rs.close();
	            }
	        } 
	        catch (Exception e) {}

	        try 
	        {
	            if (ps != null) 
	            {
	                ps.close();
	            }
	        } 
	        catch (Exception e) {}
	    }

	    return result;
	}
	
	
	
	public java.util.Date createDate2(java.util.Date date, int inc, int terms, int d2, java.util.Date emiDate)
    {
		java.util.Date dt=new java.util.Date();
		java.util.Date dt1=new java.util.Date();
        int d=date.getDate();
        int m=date.getMonth();
        int y=date.getYear();
        java.util.Date mm1=new Date(y,m,01);
        int d1=mm1.getDate();
        int m1=mm1.getMonth();
        int y1=mm1.getYear();
        String mm=String.valueOf(m);
	       
	       
        dt1.setDate(d1);
        dt1.setMonth(m1);
        dt1.setYear(y1);
        dt.setDate(d);
        dt.setMonth(m);
        dt.setYear(y);
        if(terms==1)
        {
            dt.setMonth((dt.getMonth()-1)+inc);
        }
        else
        {
            dt.setMonth(dt.getMonth()+inc);
           
        }
       
        dt.setHours(00);
        dt.setMinutes(00);
        dt.setSeconds(00);

        return dt;   
    }
	
	
	
	private HashMap<String, Double> getLoanPaidAmounts(ArrayList<LoanCandidate> candidates, Connection conn) 
	{
	    HashMap<String, Double> result = new HashMap<String, Double>();

	    if (candidates == null || candidates.size() == 0) 
	    {
	        return result;
	    }

	    StringBuilder sql = new StringBuilder();

	    sql.append("SELECT loanNo, COALESCE(SUM(amount),0) AS paidAmount FROM loanpayment WHERE restructure = 'No' " +
		           "AND receipt_status = 'approved' AND chequeStatus = 'cleared' AND loanNo IN (");

	    for (int i = 0; i < candidates.size(); i++) 
	    {
	        if (i > 0) 
	        {
	            sql.append(",");
	        }

	        sql.append("?");
	    }

	    sql.append(") GROUP BY loanNo");

	    try (PreparedStatement ps = conn.prepareStatement(sql.toString())) 
	    {
	        for (int i = 0; i < candidates.size(); i++) 
	        {
	            ps.setString(i + 1, candidates.get(i).loanNo);
	        }

	        try (ResultSet rs = ps.executeQuery()) 
	        {
	            while (rs.next()) 
	            {
	                result.put(rs.getString("loanNo"), rs.getDouble("paidAmount"));
	            }
	        }
	    }
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }

	    return result;
	}
	
	
	
	public String selectEmiPaidDate(int i, String loanNo, Connection conn)
	{
	    String payDate = null;
	    SimpleDateFormat sf = new SimpleDateFormat("dd/MM/yyyy");

	    String qq = "SELECT pay_date FROM loanpayment WHERE loanNo = ? AND emi_no = ? ";

	    try (PreparedStatement st = conn.prepareStatement(qq))
	    {
	        st.setString(1, loanNo);
	        st.setInt(2, i);

	        try (ResultSet rr = st.executeQuery())
	        {
	            if (rr.next())
	            {
	                if (rr.getDate("pay_date") != null)
	                {
	                    payDate = sf.format(rr.getDate("pay_date"));
	                }
	            }
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }

	    return payDate;
	}
	
	
	
	private HashSet<String> getDisbursedLoans(ArrayList<LoanCandidate> candidates, Connection conn) 
	{
	    HashSet<String> result = new HashSet<String>();

	    if (candidates == null || candidates.size() == 0) 
	    {
	        return result;
	    }

	    StringBuilder sql = new StringBuilder();

	    sql.append("SELECT loanNo FROM debit_loan WHERE status = 'cleared' AND loanNo IN (");

	    for (int i = 0; i < candidates.size(); i++) 
	    {
	        if (i > 0) 
	        {
	            sql.append(",");
	        }

	        sql.append("?");
	    }

	    sql.append(")");

	    try (PreparedStatement ps = conn.prepareStatement(sql.toString())) 
	    {
	        for (int i = 0; i < candidates.size(); i++) 
	        {
	            ps.setString(i + 1, candidates.get(i).loanNo);
	        }

	        try (ResultSet rs = ps.executeQuery()) 
	        {
	            while (rs.next()) 
	            {
	                result.add(rs.getString("loanNo"));
	            }
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }

	    return result;
	}
	
	
	
	private HashMap<Integer, Date> getGroupLoanEmiMap(String loanNo, Connection conn) 
	{
	    HashMap<Integer, Date> result = new HashMap<Integer, Date>();

	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try 
	    {
	        String sql = "SELECT emi_no, emi_date FROM grouploan_emi WHERE sub_loan_no = ? ORDER BY emi_no ";

	        ps = conn.prepareStatement(sql);
	        ps.setString(1, loanNo);

	        rs = ps.executeQuery();

	        while (rs.next()) 
	        {
	            result.put(rs.getInt("emi_no"), rs.getDate("emi_date"));
	        }
	    } 
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    } 
	    finally 
	    {
	        try 
	        {
	            if (rs != null) rs.close();
	        } 
	        catch (Exception e) {}
	        
	        try 
	        {
	            if (ps != null) ps.close();
	        }
	        catch (Exception e) {}
	    }

	    return result;
	}
	
	
	
	private HashMap<Integer, Date> getGroupLoanEmiDates(String loanNo, Connection conn) 
	{
	    HashMap<Integer, Date> result = new HashMap<Integer, Date>();

	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try 
	    {
	        String sql = "SELECT emi_no, emi_date FROM grouploan_emi WHERE sub_loan_no = ? ORDER BY emi_no";

	        ps = conn.prepareStatement(sql);
	        ps.setString(1, loanNo);

	        rs = ps.executeQuery();

	        while (rs.next()) 
	        {
	            result.put(rs.getInt("emi_no"), rs.getDate("emi_date"));
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    } 
	    finally 
	    {
	        try 
	        {
	            if (rs != null) rs.close();
	        } 
	        catch (Exception e) {}

	        try 
	        {
	            if (ps != null) ps.close();
	        } 
	        catch (Exception e) {}
	    }

	    return result;
	}
	
	
	
	private HashMap<String, InstallmentCandidate> getDueInstallments(ArrayList<LoanCandidate> candidates, String searchDate, 
																	 String tableName, Connection conn) 
	{
	    HashMap<String, InstallmentCandidate> result = new HashMap<String, InstallmentCandidate>();

	    if (candidates == null || candidates.size() == 0) 
	    {
	        return result;
	    }

	    StringBuilder sql = new StringBuilder();

	    sql.append("SELECT loanNo, installment_date, status, due_amount FROM loan_installment WHERE loanNo IN (");

	    for (int i = 0; i < candidates.size(); i++) 
	    {
	        if (i > 0) 
	        {
	            sql.append(",");
	        }

	        sql.append("?");
	    }

	    sql.append(")");

	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try 
	    {
	        ps = conn.prepareStatement(sql.toString());

	        for (int i = 0; i < candidates.size(); i++) 
	        {
	            ps.setString(i + 1, candidates.get(i).loanNo);
	        }

	        rs = ps.executeQuery();
	        while (rs.next()) 
	        {
	            String loanNo = rs.getString("loanNo");
	            String installmentDate = rs.getString("installment_date");
	            String status = rs.getString("status");

	            if (status == null || !status.equalsIgnoreCase("Due"))
	            {
	                continue;
	            }

	            if (installmentDate == null || !installmentDate.equals(searchDate)) 
	            {
	                continue;
	            }

	            InstallmentCandidate info = new InstallmentCandidate();

	            info.loanNo = loanNo;
	            info.installmentDate = installmentDate;
	            info.status = status;
	            info.dueAmount = rs.getDouble("due_amount");

	            result.put(loanNo, info);
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }
	    
	    finally 
	    {
	        try 
	        {
	            if (rs != null) 
	            {
	                rs.close();
	            }
	        } 
	        catch (Exception e) {}

	        try 
	        {
	            if (ps != null) 
	            {
	                ps.close();
	            }
	        } 
	        catch (Exception e) {}
	    }

	    return result;
	}
	
	
	
	private LoanDemandInfo calculateLoanDemand(LoanCandidate loan, java.util.Date searchDate, HashMap<String, Double> paidMap, 
											   HashMap<Integer, Date> groupDates) 
	{
	    try 
	    {
	        int terms = loan.terms;

	        if (terms <= 0) 
	        {
	            return null;
	        }

	        int frequency = loan.frequency;

	        int requestedEmiNo = 0;

	        if (loan.groupLoan && groupDates != null && groupDates.size() > 0) 
	        {
	            for (int i = 1; i <= terms; i++) 
	            {
	                Date emiDate = groupDates.get(i);
	                if (emiDate == null) 
	                {
	                    continue;
	                }

	                if (sameDate(emiDate, searchDate)) 
	                {
	                    requestedEmiNo = i;
	                    break;
	                }
	            }
	        }
	        else 
	        {
	            requestedEmiNo = findNormalLoanEmiNo(frequency, terms, loan.emiDueDate, loan.approvalDate, searchDate);
	        }

	        if (requestedEmiNo <= 0) 
	        {
	            return null;
	        }

	        Double paidObject = paidMap.get(loan.loanNo);

	        double paidAmount = 0;

	        if (paidObject != null) 
	        {
	            paidAmount = paidObject.doubleValue();
	        }

	        double remainingPaid = paidAmount;

	        double requestedEmiAmount = loan.emi;
	        

	        for (int i = 1; i <= requestedEmiNo; i++) 
	        {
	            double currentEmi = loan.emi;

	            if (i == terms) 
	            {
	                currentEmi = currentEmi - loan.discount;
	            }

	            if (remainingPaid >= currentEmi) 
	            {
	                remainingPaid = roundAmount(remainingPaid - currentEmi);
	            } 
	            else 
	            {
	                requestedEmiAmount = currentEmi;
	                break;
	            }
	        }

	        if (remainingPaid >= requestedEmiAmount) 
	        {
	            return null;
	        }

	        double dueAmount = requestedEmiAmount - remainingPaid;

	        dueAmount = roundAmount(dueAmount);

	        if (dueAmount <= 0) 
	        {
	            return null;
	        }

	        return new LoanDemandInfo(loan.loanNo, dueAmount, requestedEmiNo);
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }

	    return null;
	}
	
	
	
	private int findNormalLoanEmiNo(int frequency, int terms, java.util.Date emiDueDate, java.util.Date approvalDate, 
									java.util.Date searchDate) 
	{
	    if (terms <= 0) 
	    {
	        return 0;
	    }

	    Date baseDate;

	    if (emiDueDate == null) 
	    {
	        baseDate = new Date(approvalDate.getTime());
	    } 
	    else 
	    {
	        baseDate = new Date(emiDueDate.getTime());
	    }

	    int j = 365 / frequency;

	    if (emiDueDate == null) 
	    {
	        Calendar cal = Calendar.getInstance();

	        cal.setTime(baseDate);

	        for (int i = 1; i <= terms; i++) 
	        {
	            cal.add(Calendar.DATE, j);

	            java.util.Date emiDate = cal.getTime();

	            if (sameDate(emiDate, searchDate)) 
	            {
	                return i;
	            }

	            if (emiDate.after(searchDate)) 
	            {
	                break;
	            }
	        }

	        return 0;
	    }

	    if (frequency == 12 || frequency == 4 || frequency == 2 || frequency == 1) 
	    {
	        int months;

	        if (frequency == 12) 
	        {
	            months = 1;
	        } 
	        else if (frequency == 4) 
	        {
	            months = 4;
	        } 
	        else if (frequency == 2) 
	        {
	            months = 6;
	        } 
	        else 
	        {
	            months = 12;
	        }

	        java.util.Date previous = new java.util.Date(emiDueDate.getTime());

	        for (int i = 1; i <= terms; i++) 
	        {
	            java.util.Date emiDate = createDate2(previous, months, i, previous.getDate(), emiDueDate);

	            if (sameDate(emiDate, searchDate)) 
	            {
	                return i;
	            }

	            if (emiDate.after(searchDate)) 
	            {
	                break;
	            }
	        }

	        return 0;
	    }

	    Calendar cal = Calendar.getInstance();

	    cal.setTime(new Date(emiDueDate.getTime()));

	    boolean first = true;

	    for (int i = 1; i <= terms; i++) 
	    {
	        if (!first) 
	        {
	            cal.add(Calendar.DATE, j);
	        }

	        first = false;

	        java.util.Date emiDate = cal.getTime();

	        if (sameDate(emiDate, searchDate)) 
	        {
	            return i;
	        }

	        if (emiDate.after(searchDate)) 
	        {
	            break;
	        }
	    }

	    return 0;
	}
	
	
	
	private double roundAmount(double amount) 
	{
	    return Math.round(amount * 100.0) / 100.0;
	}
	
	
	
	private String appendDetail(String existing, String detail) 
	{
	    if (existing == null || existing.trim().equals("")) 
	    {
	        return detail;
	    }

	    return existing + ", " + detail;
	}
	
	
	
	private boolean sameDate(java.util.Date d1, java.util.Date d2) 
	{
	    if (d1 == null || d2 == null) 
	    {
	        return false;
	    }

	    Calendar c1 = Calendar.getInstance();

	    Calendar c2 = Calendar.getInstance();

	    c1.setTime(d1);
	    c2.setTime(d2);

	    return c1.get(Calendar.YEAR) == c2.get(Calendar.YEAR) && c1.get(Calendar.MONTH) == c2.get(Calendar.MONTH) && 
	    	   c1.get(Calendar.DAY_OF_MONTH) == c2.get(Calendar.DAY_OF_MONTH);
	}
	
	
	
	private void processDepositDemand(String schemeType, java.util.Date searchDate, HashMap<String, DailyInfo> shareholderMap, 
									  Connection conn) 
	{
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try 
	    {
	        String query = "SELECT user_no, shareHolderNo FROM customer_info WHERE scheme_type = ? AND account_status = 'active' " 
	        			 + "AND office_date <= ? ";

	        ps = conn.prepareStatement(query);

	        ps.setString(1, schemeType);
	        ps.setDate(2, new java.sql.Date(searchDate.getTime()));

	        rs = ps.executeQuery();

	        while (rs.next()) 
	        {
	            String userNo = rs.getString("user_no");

	            String shareholderNo = rs.getString("shareHolderNo");

	            DailyInfo dd = shareholderMap.get(shareholderNo);

	            if (dd == null) 
	            {
	                continue;
	            }

	            ArrayList<InstallmentInfo> installments = db.showInstallment(userNo, conn);

	            String detail = "";

	            int amount = 0;

	            String searchDt = dateObj.format(searchDate);

	            for (InstallmentInfo info : installments) 
	            {
	                if (!info.getStatus().equalsIgnoreCase("due")) 
	                {
	                    continue;
	                }

	                if (!info.getIns_date().equals(searchDt)) 
	                {
	                    continue;
	                }

	                String item = userNo + "=" + info.getDueAmount();

	                detail = appendDetail(detail, item);

	                amount += info.getDueAmount();
	            }

	            if (amount <= 0) 
	            {
	                continue;
	            }


	            if (schemeType.equalsIgnoreCase("DD")) 
	            {
	                dd.setDdDetail(appendDetail(dd.getDdDetail(), detail));
	                dd.setTotalDd(dd.getTotalDd() + amount);
	            }
	            else 
	            {
	                dd.setRdDetail(appendDetail(dd.getRdDetail(), detail));
	                dd.setTotalRd(dd.getTotalRd() + amount);
	            }
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    } 
	    finally 
	    {
	        try 
	        {
	            if (rs != null) 
	            {
	                rs.close();
	            }
	        } 
	        catch (Exception e) {}

	        try 
	        {
	            if (ps != null) 
	            {
	                ps.close();
	            }
	        } 
	        catch (Exception e) {}
	    }
	}
	
	
	
	public String adminSealScanImage()
	{
		Connection conn=DataBaseConnection.JAVAConnection();
		
		String qr = db.fileViewPathDetail(conn)+"/admin_seal.jpg";
		System.out.println("iamge path is:"+qr);
		
		try 
		{
			conn.close();
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return qr;
	}
	
	
	
	public int add_employee_salary(Connection conn, String employeeCode, String employeeName, String funtion, String desination, 
								   String pfNumber, String uanNo, String state, String esicNo, String bankName, int totalDays, 
								   String bankAccountNo, int workingDays, String dateOfJoining, int lopDays, double basicMonth, 
								   double houseRentAllowance, double specialAllowance, double otherAllowance, double grossSalary, 
								   double employeeEsicContribution, double employeePfContribution, double totalFixedPay,  
								   double totalPfDeduction, double totalEsicDeduction, double netCashInHand, String paymentMode, 
								   Timestamp chequeDate, String chequeNo, String fromBank, String description, Timestamp payDate, 
								   String selectedYear, String selectedMonth, int branchId, String panNumber, String designation, 
								   double incentive, double arrearIncentive,double  arrearSalary, double  grandTotal, 
								   double otherDeduction, java.util.Date salaryFromDate, java.util.Date salaryToDate, 
								   double fuelAmount, double advanceSalary)
	{
		Timestamp from = new Timestamp(salaryFromDate.getTime());
		Timestamp to = new Timestamp(salaryToDate.getTime());
		
		int i = 0;
		
		String qq = "insert into emp_salary (employee_code, employee_name, funtion, designation, pf_number, uan_no, state, "
				  + "esic_no, bank_name, total_days, bank_acc_no, working_days, doj, lop_days, basic, house_rent_allowance, "
				  + "special_allowance, other_allowance, gross_salary, esic_con, pf_con, total_fixed_pay, total_pf_deduction,"
				  + " total_esic_deduction, net_cash_in_hand, status, salary_month, salary_year, salary_create_date, "
				  + "payment_mode, pay_mode_no, pay_mode_date, form_bank_name, remark,branch_id, pan_no,incentive, "
				  + "arrear_incentive, arrear_salary, grand_total, other_deduction, salary_from_date, salary_to_date, "
				  + "fuel_amount, advance_salary)"
				  + "values(? ,? ,? ,? ,?, ? ,? ,? ,? ,?, ? ,? ,? ,? ,?, ? ,? ,? ,? ,?, ? ,? ,? ,? ,?, ? ,? ,? ,?, ?, ? ,? ,"
				  + "? ,?, ?, ?,? ,? ,?, ?, ?, ?, ?, ?, ?)";
		
		try(PreparedStatement st = conn.prepareStatement(qq))
		{   
			
			st.setString(1, employeeCode);
			st.setString(2, employeeName);
			st.setString(3, "JLG BL");
			st.setString(4, designation);
			st.setString(5, pfNumber);
			st.setString(6, uanNo);
			st.setString(7, state);
			st.setString(8, esicNo);
			st.setString(9, bankName);
			st.setInt(10, totalDays);
			st.setString(11, bankAccountNo);
			st.setInt(12, workingDays);
			st.setString(13, dateOfJoining);
			st.setInt(14, lopDays);
			st.setDouble(15, basicMonth);
			st.setDouble(16, houseRentAllowance);
			st.setDouble(17, specialAllowance);
			st.setDouble(18, otherAllowance);
			st.setDouble(19, grossSalary);
			st.setDouble(20, employeeEsicContribution);
			st.setDouble(21, employeePfContribution);
			st.setDouble(22, totalFixedPay);
			st.setDouble(23, totalPfDeduction);
			st.setDouble(24, totalEsicDeduction);
			st.setDouble(25, netCashInHand);
			st.setString(26, "unpaid");
			st.setString(27, selectedMonth);
			st.setString(28, selectedYear);
			st.setTimestamp(29, payDate);
			st.setString(30, paymentMode);
			st.setString(31, chequeNo);
			st.setTimestamp(32 ,chequeDate);
			st.setString(33, fromBank);
			st.setString(34, description);
			st.setInt(35, branchId);
			st.setString(36, panNumber );
			st.setDouble(37, incentive);
			st.setDouble(38, arrearIncentive);
			st.setDouble(39, arrearSalary);
			st.setDouble(40, grandTotal);
			st.setDouble(41, otherDeduction);
			st.setTimestamp(42, from);
			st.setTimestamp(43, to);
			st.setDouble(44,fuelAmount);
			st.setDouble(45, advanceSalary);
			
			i = st.executeUpdate();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return i;
	}
	
	
	
	public String fetchAPIKeyAndSecretByCibilType(String cibilType, String columnName)
	{
		
		try (Connection conn = DataBaseConnection.JAVAConnection())
		{
			String qq = "SELECT " +columnName + " FROM api_keys WHERE provider_name = ? ";
			
			try (PreparedStatement ps = conn.prepareStatement(qq))
			{
				ps.setString(1, cibilType);
				
				try (ResultSet rs = ps.executeQuery())
				{
					if (rs.next()) 
					{
						return rs.getString(columnName);
					}
				} 
				catch (Exception e) 
				{
					e.printStackTrace();
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return "";
	}
	
	
	
	public String filePathDetail()  
	{
		try (Connection conn = DataBaseConnection.JAVAConnection())
		{
			Statement st = conn.createStatement();
			String qq = "select * from firm_detail limit 1";
			ResultSet rs = st.executeQuery(qq);
			if(rs.next())
			{
				return rs.getString("file_view_path");
			}
		}
		catch (Exception e)
		{
			 e.printStackTrace();
		} 
		
		return "";
	}
	
	
	
	public Date loanPaidDate(int i,String loanNo, Connection conn)
    {
		Statement st = null;
		ResultSet rr = null;
        try
        {
            st = conn.createStatement();
            String query = "select pay_date from loanpayment where emi_no = '" + i + "' and loanNo = '" + loanNo + "' and "
            			 + "restructure = 'No' and receipt_status = 'approved' and chequeStatus = 'cleared' ORDER BY id DESC";
            
            rr = st.executeQuery(query);
            if(rr.next())
            {
                return rr.getDate("pay_date");
            }
        }
        catch(Exception e)
        {
             e.printStackTrace();db.fileCreation(e.getMessage());
        }
        finally
		{
			try 
			{
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} 
			catch (Exception e2) {}
		}
        return null;
    }
	
	
	
	public double fetchInterestAmountBooked(LoanInfo li, String loanNo, String type, Connection conn)
	{
		String qq = "SELECT amount FROM income_booking WHERE loan_no = ? AND emi_no = ? AND type = ? AND due_date = ? ";
		
		try (PreparedStatement ps = conn.prepareStatement(qq)) 
		{
			ps.setString(1, loanNo);
			ps.setInt(2, li.getSno());
			ps.setString(3, type);
			ps.setDate(4, new java.sql.Date(li.getInstallDate().getTime()));
			
			try (ResultSet rs = ps.executeQuery())
			{
				if (rs.next())
				{
					return rs.getDouble("amount");
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public Date loanPaidDateByASCOrder(int i,String loanNo, Connection conn)
    {
		Statement st = null;
		ResultSet rr = null;
        try
        {
            st = conn.createStatement();
            String query = "select pay_date from loanpayment where emi_no = '" + i + "' and loanNo = '" + loanNo + "' and "
            			 + "restructure = 'No' and receipt_status = 'approved' and chequeStatus = 'cleared' ORDER BY id ASC";
            
            rr=st.executeQuery(query);
            if(rr.next())
            {
                return rr.getDate("pay_date");
            }
        }
        catch(Exception e)
        {
             e.printStackTrace();
             db.fileCreation(e.getMessage());
        }
        finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} 
			catch (Exception e2) 
			{
				e2.printStackTrace();
			}
		}
        return null;
    }
	
	
	
	public Date fetchDateInterestBooked(LoanInfo li, String loanNo, String type, Connection conn)
	{
		String qq = "SELECT due_date FROM income_booking WHERE loan_no = ? AND emi_no = ? AND type = ?";
		
		try (PreparedStatement ps = conn.prepareStatement(qq)) 
		{
			ps.setString(1, loanNo);
			ps.setInt(2, li.getSno());
			ps.setString(3, type);
			
			try (ResultSet rs = ps.executeQuery())
			{
				if (rs.next())
				{
					return rs.getDate("due_date");
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return null;
	}
	
	
	
	public double fetchAmountByLoanNoFromIncomeAndExpenseTable(String tableName, String loanNo, String item, Connection conn)
	{
		String qq  = "SELECT amount FROM "+ tableName +" WHERE status = 'active' AND chequeStatus = 'cleared' AND loan_no = ? "
				   + "AND item = ? ";
		
		try (PreparedStatement ps = conn.prepareStatement(qq))
		{
			ps.setString(1, loanNo);
			ps.setString(2, item);
			
			try (ResultSet rs = ps.executeQuery())
			{
				if (rs.next())
				{
					return rs.getDouble("amount");
				}
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public ArrayList<String> allChargesList(Connection conn)
	{
		ArrayList<String> chargeList = new ArrayList<>();
		
		String qq = "SELECT module_name FROM master_setting_new ";
		
		try (PreparedStatement ps = conn.prepareStatement(qq))
		{
			try (ResultSet rs = ps.executeQuery())
			{
				while (rs.next()) 
				{
					String name = rs.getString("module_name");
					
					chargeList.add(name);
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return chargeList;
	}
	
	
	
	public String loanTypeByLoanNo(String loanNo)
	{
		if (loanNo.contains("GL"))
		{
			return "grouploan";
		}
		else if (loanNo.contains("SL"))
		{
			return "secure_loan";
		}
		else if (loanNo.contains("BL"))
		{
			return "business_loan";
		}
		else  
		{
			return "personalloan";
		}
	}
	
	
	
	public double debitLoanAmountByLoanNo(String loanNo, Connection conn)
	{
		String qq  = "SELECT SUM(amount) as total FROM debit_loan WHERE loanNo = ? ";
		
		try (PreparedStatement ps = conn.prepareStatement(qq))
		{
			ps.setString(1, loanNo);
			
			try (ResultSet rs = ps.executeQuery())
			{
				if (rs.next()) 
				{
					return rs.getDouble("total");
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public String debitDateByLoanNo(String loanNo, Connection conn)
	{
		String qq  = "SELECT date FROM debit_loan WHERE loanNo = ? ";
		
		try (PreparedStatement ps = conn.prepareStatement(qq))
		{
			ps.setString(1, loanNo);
			
			try (ResultSet rs = ps.executeQuery())
			{
				if (rs.next()) 
				{
					return dateObj.format(rs.getDate("date"));
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return "";
	}
	
	
	
	public int insertBookingAmountByType(LoanInfo li, int branchId, UserInfo ui, String voucherNo, String category, String type, 
										 Connection conn)
	{	
		String qq = "INSERT INTO income_booking (booking_date, category, type, due_date, amount, emi_no, item, loan_no, "
				  + "loan_type, voucher_no, status, branch_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		try (PreparedStatement ps = conn.prepareStatement(qq))
		{
			ps.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
			ps.setString(2, "Dr");
			ps.setString(3, category);
			ps.setDate(4, new java.sql.Date(li.getInstallDate().getTime()));
			
			if (Double.valueOf(li.getInterestAmount()) < li.getPaidAmount()) 
			{
				ps.setDouble(5, Double.valueOf(li.getInterestAmount()));
			}
			else 
			{
				ps.setDouble(5, (Double.valueOf(li.getInterestAmount()) - li.getPaidAmount()));
			}
			
			ps.setInt(6, li.getSno());
			ps.setString(7, type);
			ps.setString(8, ui.getLoanNo());
			ps.setString(9, ui.getLoanType());
			ps.setString(10, voucherNo);
			ps.setString(11, "booked");
			ps.setInt(12, branchId);
			
			return ps.executeUpdate();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public boolean checkDuplicateBooking(LoanInfo li, String loanNo, String type, Connection conn)
	{
		String qq = "SELECT * FROM income_booking WHERE loan_no = ? AND emi_no = ? AND type = ? AND due_date = ?";
		
		try (PreparedStatement ps = conn.prepareStatement(qq)) 
		{
			ps.setString(1, loanNo);
			ps.setInt(2, li.getSno());
			ps.setString(3, type);
			ps.setDate(4, new java.sql.Date(li.getInstallDate().getTime()));
			
			try (ResultSet rs = ps.executeQuery())
			{
				if (rs.next())
				{
					return true;
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return false;
	}
	
	
	
	public ArrayList<LoanInfo> loanInstallmentByStatus(String loanType, String loanNo, String status, Connection conn)
    {
        String qq = "";
        int j = 0;
        double principal = 0.0;
        
        Statement st = null;
		ResultSet rs = null;
		boolean checkGroupLoanEmi = false;
        ArrayList<LoanInfo> list = new ArrayList<LoanInfo>();
        
        try
        {
            st = conn.createStatement();
            if(loanType.equals("grouploan"))
            {
                qq = "select * from "+loanType+" where sub_loan_no = '" + loanNo + "' and status = 'Approve'";
            }
            else
            {
                 qq = "select * from "+loanType+" where loanNo = '" + loanNo + "' and status = 'Approve'";
            }  
           
            checkGroupLoanEmi = db.checkGroupLoanEmiFromTable(loanNo, conn);
            rs = st.executeQuery(qq);
            if(rs.next())
            {   
                if(rs.getString("emiType").equals("reducing"))
                {
                    double discount = db.discountOnLastEMI(loanType, loanNo, conn);
                    double paidAmount = db.loanPaidAmountByUser(loanType, loanNo, conn);
                    int terms = rs.getInt("terms");
                    int frequency = rs.getInt("frequency");
                    double emi = rs.getDouble("emi");
                    java.util.Date offcdt = null;
                    if(rs.getDate("emi_due_date") == null)
                    {
                        offcdt = rs.getDate("approvalDate");
                    }
                    else
                    {
                        offcdt = rs.getDate("emi_due_date");
                        System.out.println("value of the emi Due Date :" + offcdt);   
                    }
                    double interstRate = rs.getDouble("interest");
                    double loanAmount = rs.getDouble("loanAmount");
                    double fr = (double) frequency;
                    Date installDate = null;
                    j = 365/frequency;
                    if(rs.getDate("emi_due_date") == null)
                    {
                        installDate = rs.getDate("approvalDate");
                    }
                    else
                    {
                        installDate = rs.getDate("emi_due_date");
                        if(frequency == 12)
                        {
                            installDate.setMonth(installDate.getMonth()-1);
                        }
                        else
                        {
                            installDate.setDate(installDate.getDate()-j);
                        }
                    }  
                    
                   
                    
                    int y = 1;
                    for (int i = 1; i <= terms; i++)
                    {
                        LoanInfo ll = new LoanInfo();
                        
                        ll.setSno(i);
                        if(i == terms)
                        {
                            emi = emi - discount;
                            ll.setInstallAmount(emi);
                        }
                        else
                        {
                            ll.setInstallAmount(emi);
                        }
                      
                        if(paidAmount >= emi)
                        {
                            ll.setStatus("Paid");
                            ll.setPaidAmount(emi);
                            ll.setDueAmount(0);
                            paidAmount=Double.parseDouble(dc.format(paidAmount - emi));
                        }
                        else
                        {
                            ll.setStatus("Due");
                            ll.setPaidAmount(paidAmount);
                            ll.setDueAmount(Double.parseDouble(dc.format(emi - paidAmount)));
                            	
                            paidAmount = 0;
                        }
                  
                        java.util.Date dt = installDate;
                        if(rs.getDate("emi_due_date") == null)
                        {
                            dt.setDate(dt.getDate()+j);
                        }
                        else
                        {
                            if(frequency == 12 || frequency == 4 || frequency == 2 || frequency == 1)
                            {
                            	if(frequency == 4)
                                {
                                    y = 4;
                                }
                                else if(frequency == 2)
                                {
                                    y = 6;
                                }
                                else if(frequency == 1)
                                {
                                    y = 12;
                                }
                                else if(frequency == 12)
                                {
                                    y=1;
                                }
                                
                                offcdt=db.createDate2(offcdt,y,i,offcdt.getDate(),rs.getDate("emi_due_date"));
                            }
                            else
                            {
                                dt.setDate(dt.getDate()+j);
                            }
                        }
                        
                        if(checkGroupLoanEmi == false && (frequency == 12 || frequency == 4 || frequency == 2 || frequency == 1))
                        {
                            ll.setStrInstlmntDate(dateObj.format(offcdt));
                            SimpleDateFormat dd = dateObj;
                            java.util.Date ss = dd.parse(ll.getStrInstlmntDate());
                            ll.setInstallDate(ss);
                        }
                        else
                        {
                        	  if(checkGroupLoanEmi == true)  // Condition for Manage Meeting Day Change
                        	  {
                        		  dt = db.groupLoanEmiDateFromTable(i,loanNo,conn);
                        	  }
	                        ll.setStrInstlmntDate(dateObj.format(dt));
	                        SimpleDateFormat dd = dateObj;
	                        java.util.Date ss = dd.parse(ll.getStrInstlmntDate());
	                        ll.setInstallDate(ss);
                        }
                      
                        ll.setPaidDateStr("- - - -");
                       
                        double frstIntrstAmnt = (loanAmount*interstRate) / (100*fr);
                        ll.setInterestAmount(dc.format((frstIntrstAmnt)));
                        ll.setInterest(Double.parseDouble(dc.format(frstIntrstAmnt)));
                      
                        double paidPrincipalAmnt = emi - (frstIntrstAmnt);
                        ll.setPrincipalAmount(dc.format(paidPrincipalAmnt));
                       
 	                   if(principal == 0.0)
 	                   {
 	                	   ll.setPrincipalRemaing(Math.round(loanAmount - Double.parseDouble(ll.getPrincipalAmount())));
 	                   }
 	                   else
 	                   {
 	                	   ll.setPrincipalRemaing(Math.round(principal - Double.parseDouble(ll.getPrincipalAmount())));
 	                   }
 	                   
 	                   if(ll.getPrincipalRemaing() < 5)
 	                   {
 	                	   ll.setPrincipalRemaing(0);
 	                   }
 	                   
 	                   principal = ll.getPrincipalRemaing();
                        
                        ll.setPrincipal(Double.parseDouble(dc.format(paidPrincipalAmnt)));
                      
                        double principalRemaining = loanAmount - paidPrincipalAmnt;
                        ll.setPrincipalBalanceStr(dc.format(principalRemaining));
                        
                        if (ll.getStatus().equalsIgnoreCase(status)) 
                        {
                        	list.add(ll);
						}
                        
                        loanAmount = principalRemaining;
                    }
                }
                else
                {
                	String emiMode = "";
                    if(loanType.equalsIgnoreCase("grouploan"))
                    {
                    	emiMode = "both";
                    }
                    else
                    {
                    	emiMode = rs.getString("emi_mode");
                    }
                    if(emiMode.equalsIgnoreCase("both"))
                    {
                    	double discount = db.discountOnLastEMI(loanType,loanNo, conn);
                    	java.util.Date offcdt = null;
                    	if(rs.getDate("emi_due_date") == null)
                        {
                    		offcdt = rs.getDate("approvalDate");
                        }
                        else
                        {
                        	offcdt = rs.getDate("emi_due_date");
                        }
                        
                    	double paidAmount = db.loanPaidAmountByUser(loanType,loanNo, conn);
                        int terms = rs.getInt("terms");
                        int frequency = rs.getInt("frequency");
                        double emi = rs.getDouble("emi");
                        double interstRate = rs.getDouble("interest");
                        double loanAmount = rs.getDouble("loanAmount");
                        double totalInterest = Double.parseDouble(dc.format((emi*terms)-loanAmount));
                        double interestMonthly = totalInterest/terms;
                        Date installDate = null;
                        j = 365 / frequency;
                    
                        if(rs.getDate("emi_due_date") == null)
                        {
                        	installDate = rs.getDate("approvalDate");
                        }
                        else
                        {
                        	installDate = rs.getDate("emi_due_date");
                        	if(frequency == 12)
                        	{
                        		installDate.setMonth(installDate.getMonth()-1);
                        	}
                        	else
                        	{
                        		installDate.setDate(installDate.getDate()-j);
                        	}
                        }
                        
                        int y = 1;
                        for (int i = 1; i <= terms; i++)
                        {
                        	LoanInfo ll = new LoanInfo();
                        	ll.setSno(i);
                        	if(i == terms)
                        	{
                        		emi = emi-discount;
                        		ll.setInstallAmount(emi);
                        	}
                        	else
                        	{
                        		ll.setInstallAmount(emi);
                        	}
                        
                        	if(paidAmount >= emi)
                        	{
                        		ll.setStatus("Paid");
                        		ll.setPaidAmount(emi);
                        		ll.setDueAmount(0);
                        		paidAmount = Double.parseDouble(dc.format(paidAmount - emi));
                        	}
                        	else
                        	{
                        		ll.setStatus("Due");
                        		ll.setPaidAmount(paidAmount);
                        		ll.setDueAmount(Double.parseDouble(dc.format(emi - paidAmount)));
                        		paidAmount = 0;
                        	}
                    
                        	java.util.Date dt = installDate;
                        	if(rs.getDate("emi_due_date") == null)
                        	{
                        		dt.setDate(dt.getDate()+j);
                        	}
                        	else
                        	{
                        		if(frequency == 12 || frequency == 4 || frequency == 2 || frequency == 1)
                        		{
                        			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;
                                    }
                        			offcdt=db.createDate2(offcdt,y,i,offcdt.getDate(),rs.getDate("emi_due_date"));
                        		}
                        		else
                        		{
                        			dt.setDate(dt.getDate()+j);
                        		}
                        	}
                        	if(checkGroupLoanEmi==false && (frequency==12 || frequency==4 || frequency==2 || frequency==1))
                        	{
                        		ll.setStrInstlmntDate(dateObj.format(offcdt));
                              	SimpleDateFormat dd=dateObj;
                              	java.util.Date ss=dd.parse(ll.getStrInstlmntDate());
                              	ll.setInstallDate(ss);
                        	}
                        	else
                        	{
                        		if(checkGroupLoanEmi==true)  // Condition for Manage Meeting Day Change
                        		{
                        			dt=db.groupLoanEmiDateFromTable(i,loanNo,conn);
                        		}
                              ll.setStrInstlmntDate(dateObj.format(dt));
                              SimpleDateFormat dd=dateObj;
                              java.util.Date ss=dd.parse(ll.getStrInstlmntDate());
                              ll.setInstallDate(ss);
                          }
                         
                        ll.setPaidDateStr("- - - -");
                         
                          double frstIntrstAmnt=interestMonthly;
                          ll.setInterestAmount(dc.format((frstIntrstAmnt)));
                          ll.setInterest(Double.parseDouble(dc.format(frstIntrstAmnt)));
                        
                          double paidPrincipalAmnt=emi-(frstIntrstAmnt);
                          ll.setPrincipalAmount(dc.format(paidPrincipalAmnt));
                          
                       if(principal == 0.0)
 	                   {
                           ll.setPrincipalRemaing(Math.round(loanAmount-Double.parseDouble(ll.getPrincipalAmount())));
 	                   }
                       else
 	                   {
 	                	  ll.setPrincipalRemaing(Math.round(principal-Double.parseDouble(ll.getPrincipalAmount())));
 	                   }
 	                   
 	                   if(ll.getPrincipalRemaing() < 5)
 	                   {
 	                	   ll.setPrincipalRemaing(0);
 	                   }
 	                   
 	                   principal=ll.getPrincipalRemaing();
//                              ll.setPrincipalRemaing();
                          ll.setPrincipal(Double.parseDouble(dc.format(paidPrincipalAmnt)));
                            
                          //double principalRemaining=loanAmount-paidPrincipalAmnt;
                        
                          if (ll.getStatus().equalsIgnoreCase(status)) 
                          {
                          	list.add(ll);
                          }
                          //loanAmount=principalRemaining;
                      }
                    }
                    else
                    {
                        double discount=rs.getDouble("discount");
                        java.util.Date offcdt =null;
                        if(rs.getDate("emi_due_date")==null)
                        {
                            offcdt =rs.getDate("approvalDate");
                        }
                        else
                        {
                            offcdt = rs.getDate("emi_due_date");
                        }
                        double paidAmount=db.loanPaidAmountByUser(loanType,loanNo, conn);
                        int terms = rs.getInt("terms");
                        int frequency=rs.getInt("frequency");
                        double emi=rs.getDouble("emi");
                        double interstRate=rs.getDouble("interest");
                        double loanAmount=rs.getDouble("loanAmount");
                        //double interestMonthly =Double.parseDouble(new DecimalFormat("##.#######").format(interstRate / 
                        //																				   (100.00 * frequency)));
                        double totalInterest=Double.parseDouble(dc.format((emi*terms)));
                       System.out.println("Total interst :"+totalInterest);
                        double interestMonthly=totalInterest/terms;
                        System.out.println("Total interst :"+totalInterest);
                        Date installDate=null;
                        boolean chk=true;
                        j=365/frequency;
                        double fr=(double) frequency;

                        if(rs.getDate("emi_due_date")==null)
                        {
                            installDate=rs.getDate("approvalDate");
                        }
                        else
                          {
                              installDate=rs.getDate("emi_due_date");
                              if(frequency==12)
                              {
                                  installDate.setMonth(installDate.getMonth()-1);
                              }
                              else if(frequency==4)
                              {
                                  installDate.setMonth(installDate.getMonth()-3);
                              }
                              else
                              {
                                  installDate.setDate(installDate.getDate()-j);
                              }
                          }
                        
                        int y=1;
                        for (int i = 1; i<= terms; i++)
                        {
                            LoanInfo ll=new LoanInfo();
                            //int jj=365/frequency;
                            ll.setSno(i);
                            if(i==terms)
                            {
                                emi=emi-discount;
                                ll.setInstallAmount(emi);
                            }
                            else
                            {
                                ll.setInstallAmount(emi);
                            }
                           
                            if(paidAmount>=emi)
                            {
                                ll.setStatus("Paid");
                                ll.setPaidAmount(emi);
                                ll.setDueAmount(0);
                                paidAmount=Double.parseDouble(dc.format(paidAmount-emi));
                            }
                            else
                            {
                                ll.setStatus("Due");
                                ll.setPaidAmount(paidAmount);
                                ll.setDueAmount(Double.parseDouble(dc.format(emi-paidAmount)));
                                paidAmount=0;
                            }
                            
                            //comment on 23-oct-2019 due to problem in MTOS nidhi
                            /*Date dt=installDate;
                            if(rs.getDate("emi_due_date")==null)
                            {
                                dt.setDate(dt.getDate()+jj);
                            }
                            else
                            {
                                if(chk==true)
                                {
                                    dt.setMonth(dt.getMonth());
                                    chk=false;
                                }
                                else
                                {
                                    dt.setMonth(dt.getMonth()+1);
                                }
                            }
                            ll.setInstallDate(dt);
                            */
                            

                            java.util.Date dt=installDate;
                              if(rs.getDate("emi_due_date")==null)
                              {
                                  dt.setDate(dt.getDate()+j);
                              }
                              else
                              {
                                  if(frequency==12 || frequency==4 || frequency==2 || frequency==1)
                                  {
                                        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;s
                                        }
                                        offcdt=db.createDate2(offcdt,y,i,offcdt.getDate(),rs.getDate("emi_due_date"));
                                  }
                                  else
                                  {
                                      dt.setDate(dt.getDate()+j);
                                  }
                              }
                              if(checkGroupLoanEmi==false && (frequency==12 || frequency==4 || frequency==2 || frequency==1))
                              {
                                  // System.out.println("hsjhhjhjhjhjhjhh "+offcdt);
                                  ll.setStrInstlmntDate(dateObj.format(offcdt));
                                  SimpleDateFormat dd=dateObj;
                                  java.util.Date ss=dd.parse(ll.getStrInstlmntDate());
                                  ll.setInstallDate(ss);
                              }
                              else
                              {
                            	  if(checkGroupLoanEmi==true)  // Condition for Manage Meeting Day Change
                            	  {
                            		  dt=db.groupLoanEmiDateFromTable(i,loanNo,conn);
                            	  }
	                              ll.setStrInstlmntDate(dateObj.format(dt));
	                              SimpleDateFormat dd=dateObj;
	                              java.util.Date ss=dd.parse(ll.getStrInstlmntDate());
	                              ll.setInstallDate(ss);
                              }
                              
                              ll.setPaidDateStr("- - - -");
                           
                              double frstIntrstAmnt=interestMonthly;
                              ll.setInterestAmount(dc.format((frstIntrstAmnt)));
                              ll.setInterest(Double.parseDouble(dc.format(frstIntrstAmnt)));
                            
                            double paidPrincipalAmnt = emi - (frstIntrstAmnt);
                            ll.setPrincipalAmount(dc.format(paidPrincipalAmnt));
                            ll.setPrincipal(Double.parseDouble(dc.format(paidPrincipalAmnt)));
                       
                            if (ll.getStatus().equalsIgnoreCase(status)) 
                            {
                            	list.add(ll);
    						}
                        }
                    }
                }
            }
        }
        catch (Exception e)
        {
             e.printStackTrace();db.fileCreation(e.getMessage());
        }
        
        finally
		{
			try 
			{
				if(st!=null)
				{
					st.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} 
			catch (Exception e2) 
			{
				e2.printStackTrace();
			}
		}
      
        return list;
    }
	
	
	
	public ArrayList<UserInfo> allDueEMIList(int branchId)
	{
		Connection conn = DataBaseConnection.JAVAConnection();
		ArrayList<UserInfo> templist = allDueEMI(branchId, "loan", "all", "All", conn);
		ArrayList<UserInfo> list = new ArrayList<>();
		
		for(int i=0;i<templist.size();i++)
		{
			if(!templist.get(i).getEntryDateStr().equalsIgnoreCase(
								new SimpleDateFormat("dd/MM/yyyy").format(new java.util.Date())))
			{
				list.add(templist.get(i));
			}
		}
		
		Collections.sort(list);
		System.out.println("due list size: " + list.size());
		
		try 
		{
			conn.close();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
			
		return list;
	}
	
	
	
	public ArrayList<UserInfo> allDueEMI(int branchId,String category, String freqy, String dsaCode, Connection conn) 
	{
		boolean checkGroupLoanEmi = false;
		java.util.Date endDate = new java.util.Date();
		Timestamp tt = new Timestamp(endDate.getTime());
		tt.setHours(0);
		tt.setMinutes(0);
		tt.setSeconds(0);
		tt.setNanos(0);
		
		ArrayList<UserInfo> list = new ArrayList<>();
		Statement st = null;
		
		try
		{
			st = conn.createStatement();
			ArrayList<SelectItem> myList = new ArrayList<>();
			myList.add(new SelectItem("personalloan","Personal Loan"));
			myList.add(new SelectItem("grouploan","Group Loan"));
			myList.add(new SelectItem("secure_loan","Secure Loan"));
			
			if(category.equalsIgnoreCase("loan"))
			{
				for(SelectItem item : myList)
				{
					String loanType = (String) item.getValue();
					String query = "";
					if(!loanType.equals("grouploan"))
					{
						query = "select * from " + loanType + " INNER JOIN shareholder_info on " 
							  + loanType + ".shareholderno = shareholder_info.shareholderno where " 
							  + loanType + ".status = 'approve' and " + loanType + ".paidStatus = 'unpaid' ";
					}
					else
					{
					  query = "select * from " + loanType + " INNER JOIN shareholder_info on " 
							+ loanType + ".teamLeader = shareholder_info.shareholderno where " 
							+ loanType+".status='approve' and "+loanType+".paidStatus='unpaid' ";
					}
					
					if(!freqy.equalsIgnoreCase("all"))
					{
						query += " and (frequency = '" + freqy + "' || frequency = '" + freqy + ".0') ";
					}
					
					if(!dsaCode.equalsIgnoreCase("all"))
					{
						query += " and " + loanType + ".dsacode='" + dsaCode + "' ";
					}
					
					query = obj.selectQueWithBranch(query, branchId, "shareholder_info") + "order by loanNo asc";
					
					ResultSet rr = st.executeQuery(query);
					while(rr.next())
					{
						String loanNo = "";
						if(loanType.equals("grouploan"))
						{
							loanNo = rr.getString("sub_loan_no");;
						}
						else
						{
							loanNo = rr.getString("loanNo");
						}
						checkGroupLoanEmi = db.checkGroupLoanEmiFromTable(loanNo, conn);
						double paidAmount = db.loanPaidAmountByUser(loanType, loanNo, conn);
						
						int terms = rr.getInt("terms");
						int frequency = rr.getInt("frequency");
						double emi = rr.getDouble("emi");
						int j = 365 / frequency;
						Date installDate;
						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);
		                    }
						}
						for (int i = 1; i <= terms; i++) 
						{
							UserInfo ll = new UserInfo();
							
							if(paidAmount >= emi)
							{
								ll.setStatus("Paid");
								paidAmount = Double.parseDouble(dc.format(paidAmount-emi));
							}
							else
							{
								ll.setStatus("Due");
								ll.setAmount(Double.parseDouble(dc.format(emi - paidAmount)) + rr.getDouble("extra_amount"));
								paidAmount = 0;
							}
							
							java.util.Date dt=installDate;
							if(rr.getDate("emi_due_date") == null && checkGroupLoanEmi == false)
							{
								dt.setDate(dt.getDate()+j);
							}
							else
							{
								if(frequency == 12 && checkGroupLoanEmi == false)
	                            {
	                                dt.setMonth(dt.getMonth()+1);
	                            }
	                            else
	                            {
	                            	 if(checkGroupLoanEmi == true)  
	                            	 {
	                            		 dt = db.groupLoanEmiDateFromTable(i, loanNo, conn);
	                            	 }
	                            	 else
	                            	 {
	                            		 dt.setDate(dt.getDate()+j);
	                            	 }
	                            }
							}
							
							if(loanType.equals("grouploan"))
							{
								ll.setLoanNo(loanNo + " - " + rr.getString("sub_loan_no"));
							}
							else
							{
								ll.setLoanNo(loanNo);
							}
							
							ll.setId(rr.getString("id"));
							ll.setLoanType(loanType);
							ll.setChq_ddNo(item.getLabel());
							ll.setFirst_applicantName(rr.getString("shareholder_info.first_applicantName"));
							ll.setContactNo(rr.getString("shareholder_info.mobile"));
							ll.setShareHolderNo(rr.getString("shareholder_info.shareholderno"));
							ll.setEntryDateStr(dateObj.format(dt));
							ll.setEntryDate(dateObj.parse(ll.getEntryDateStr()));

							if(ll.getAmount() > 0 && ll.getEntryDate().before(endDate))
							{
								if(ll.getStatus().equals("Due"))
								{
									list.add(ll);
								}
								
							}
						}
					}
				}
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return list;
	}
	
	
	
	public int sendDLTMessage(String mobile, String typeMessage, Map<String, String> variables, Connection conn, 
							  String templateName)
	{
		try 
		{
			if(mobile == null || !mobile.matches("\\d{10}"))
			{
			    return 0;
			}

			Map<String, String> template = getSmsTemplate(templateName, conn);
			if (template == null || template.isEmpty())
			{
				System.out.println("Template not found for : " + templateName);
				return 0;
			}
			
			String templateId = template.get("template_id");
			System.out.println("Template approved on DLT : " + templateId);
			
			if(templateId == null || templateId.equalsIgnoreCase("pending"))
			{
				System.out.println("Template not approved on DLT");
				return 0;
			}
			
			String message = buildMessage(template.get("message_format"), variables);
			System.out.println("Message for template : " + message);
			
			String url = buildDLTUrl(mobile, message, templateId, conn);
			
			if(url == null || url.trim().isEmpty())
			{
				System.out.println("URL build failed");
				return 0;
			}
			
			System.out.println("Final URL: " + url);
			
			Map<String, String> apiResult = callSmsApi(url);

			String response  = apiResult.get("response");
			String messageId = apiResult.get("message_id");
			
			saveSmsLog(mobile, message, templateId, messageId, response, conn);

			return 1;			
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			return 0;
		}
	}
	
	
	
	public Map<String, String> getSmsTemplate(String templateName, Connection conn)
	{
	    Map<String, String> map = new HashMap<>();

	    try 
	    {
	        String q = "SELECT template_id, message_format FROM dlt_templates WHERE template_name=? and status = 'active' ";
	        PreparedStatement ps = conn.prepareStatement(q);
	        ps.setString(1, templateName);

	        ResultSet rs = ps.executeQuery();
	        if (rs.next()) 
	        {
	            map.put("template_id", rs.getString("template_id"));
	            map.put("message_format", rs.getString("message_format"));
	        }

	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }

	    return map;
	}
	
	
	
	public String buildMessage(String template, Map<String, String> variables)
	{
	    try
	    {
	        if(template == null || template.trim().isEmpty())
	        {
	            return "";
	        }

	        if(variables != null)
	        {
	            for(Map.Entry<String, String> entry : variables.entrySet())
	            {
	                String key = entry.getKey();
	                String value = entry.getValue();

	                if(value == null)
	                {
	                    value = "";
	                }

	                value = value.replaceAll("\\s+", " ").trim();

	                template = template.replace("{#" + key + "#}", value);
	            }
	        }

	        if(template.matches(".*\\{#.*?#\\}.*"))
	        {
	            System.out.println("Unreplaced placeholders found : " + template);
	            return "";
	        }

	        return template.trim();
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	        return "";
	    }
	}
	
	
	
	public String buildDLTUrl(String mobile, String message, String templateId, Connection conn)
	{
		try
		{
			String apiUrl    = checkSMessageApiDetailFromTable("api_url", "", conn);
			String senderId  = checkSMessageApiDetailFromTable("sender_id", "", conn);
			String apiKey    = checkSMessageApiDetailFromTable("api_key", "", conn);
			String userName  = checkSMessageApiDetailFromTable("user_name", "", conn);
			String entityId  = checkSMessageApiDetailFromTable("entity_id", "", conn);
			String smsType  = checkSMessageApiDetailFromTable("sms_type", "", conn);
			
			if(apiUrl == null || senderId == null || apiKey == null || userName == null || entityId == null)
			{
				return "";
			}
			
			if(!apiUrl.endsWith("?"))
			{
				apiUrl += "?";
			}
			
			return apiUrl + "username=" + encodeMessage(userName)
						  + "&message=" + encodeMessage(message)
						  + "&sendername=" + encodeMessage(senderId)
						  + "&smstype=" + encodeMessage(smsType)
						  + "&numbers=" + mobile
						  + "&apikey=" + encodeMessage(apiKey)
						  + "&peid=" + encodeMessage(entityId)
						  + "&templateid=" + encodeMessage(templateId);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return "";
		}
	}
	
	
	
	public Map<String, String> callSmsApi(String url)
	{
	    Map<String, String> result = new HashMap<>();

	    HttpURLConnection connection = null;

	    try
	    {
	        URL myURL = new URL(url);

	        connection = (HttpURLConnection) myURL.openConnection();

	        connection.setRequestMethod("GET");
	        connection.setConnectTimeout(10000); 
	        connection.setReadTimeout(10000);

	        int responseCode = connection.getResponseCode();

	        result.put("http_code", String.valueOf(responseCode));

	        BufferedReader br;

	        if(responseCode >= 200 && responseCode < 300)
	        {
	            br = new BufferedReader(new InputStreamReader(connection.getInputStream()));
	        }
	        else
	        {
	            br = new BufferedReader(new InputStreamReader(connection.getErrorStream()));
	        }

	        StringBuilder responseBuilder = new StringBuilder();

	        String line;

	        while((line = br.readLine()) != null)
	        {
	            responseBuilder.append(line);
	        }

	        br.close();

	        String response = responseBuilder.toString();

	        result.put("response", response);

	        if(response != null && response.contains(":"))
	        {
	        	Pattern p = Pattern.compile("\"msgid\"\\s*:\\s*\"?(\\d+)\"?");
	        	Matcher m = p.matcher(response);

	        	if(m.find())
	        	{
	        	    result.put("message_id", m.group(1));
	        	}
	        }

	        result.put("success", responseCode >= 200 && responseCode < 300 ? "true" : "false");
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();

	        result.put("success", "false");
	        result.put("response", e.getMessage());
	    }
	    finally
	    {
	        if(connection != null)
	        {
	            connection.disconnect();
	        }
	    }

	    return result;
	}
	
	
	
	public long saveSmsLog(String mobileNo, String message, String templateId, String responseId, String apiResponse,
	        			   Connection conn)
	{
		String userId = null;
	
		FacesContext facesContext = FacesContext.getCurrentInstance();
		if (facesContext != null) 
		{
		    HttpSession ss = (HttpSession) facesContext.getExternalContext().getSession(false);

		    if (ss != null) 
		    {
		        Object username = ss.getAttribute("username");

		        if (username != null) 
		        {
		            userId = username.toString();
		        }
		    }
		}

		if (userId == null) 
		{
		    userId = "SYSTEM";
		}
		
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try
	    {
	        String entityId = checkSMessageApiDetailFromTable("entity_id", "", conn);
	        String senderId = checkSMessageApiDetailFromTable("sender_id", "", conn);
	        String smsType  = checkSMessageApiDetailFromTable("sms_type", "", conn);

	        String status = "FAILED";
	        String errorMessage = null;

	        if(apiResponse != null &&
	           apiResponse.toLowerCase().contains("success"))
	        {
	            status = "SUBMITTED";
	        }
	        else
	        {
	            errorMessage = apiResponse;
	        }

	        String query = "INSERT INTO dlt_message_log (mobile_no, message, template_id, entity_id, sender_id, message_type, "
	        			 + "status, error_message, provider_name, response_id, sent_time, user_id) "
	        			 + "VALUES(?,?,?,?,?,?,?,?,?,?,NOW(), ?)";

	        ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);

	        ps.setString(1, mobileNo);
	        ps.setString(2, message);
	        ps.setString(3, templateId);
	        ps.setString(4, entityId);
	        ps.setString(5, senderId);
	        ps.setString(6, smsType);
	        ps.setString(7, status);
	        ps.setString(8, errorMessage);
	        ps.setString(9, "BulkSMSInd");
	        ps.setString(10, responseId);
	        ps.setString(11, userId);

	        ps.executeUpdate();

	        rs = ps.getGeneratedKeys();

	        if(rs.next())
	        {
	            return rs.getLong(1);
	        }
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	    }

	    return 0;
	}
	
	
	
	public String checkSMessageApiDetailFromTable(String columnName, String checkStatus, Connection conn) 
	{
		String qq = "";
		try
		{
			Statement st = conn.createStatement();
			if(columnName.equalsIgnoreCase("admin_app") || columnName.equalsIgnoreCase("user_app") || 
			   columnName.equalsIgnoreCase("employee_app") || columnName.equalsIgnoreCase("software_type") || 
			   checkStatus.equalsIgnoreCase("active") || checkStatus.equalsIgnoreCase("message_status") || 
			   columnName.equalsIgnoreCase("employee_attendance") || columnName.equalsIgnoreCase("employee_wise_data") || 
			   columnName.equalsIgnoreCase("gps_tracking") || columnName.equalsIgnoreCase("nach_no") || 
			   columnName.equalsIgnoreCase("crif_no") || columnName.equalsIgnoreCase("equifax_no") || 
			   columnName.equalsIgnoreCase("experial_no") || columnName.equalsIgnoreCase("saving_percent"))
			{
				qq = "select * from message_api where type = 'DLT' ";
			}
			else 
			{
				qq = "select * from message_api where message_status='active' and type = 'DLT' ";
			}
			
			ResultSet rr=st.executeQuery(qq);
			if(rr.next())
			{
				return rr.getString(columnName);
			}
		}
		catch(Exception e)
		{
			 e.printStackTrace();
		}
		return "";
	}
	
	
	
	public String encodeMessage(String msg)
	{
	    try
	    {
	        if(msg == null || msg.trim().isEmpty())
	        {
	            return "";
	        }

	        msg = msg.replaceAll("\\s+", " ").trim();

	        return URLEncoder.encode(msg, "UTF-8").replace("+", "%20");
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	        return "";
	    }
	}
	
	
	
	public ArrayList<LoanInfo> dltMessageReport(int branchId,String sDate, Timestamp eDate) 
	{
		ArrayList<LoanInfo> list = new ArrayList<>();
		
		try(Connection conn = DataBaseConnection.JAVAConnection())
		{
			String query = "SELECT * FROM dlt_message_log WHERE sent_time BETWEEN ? AND ?";
			
			try (PreparedStatement ps = conn.prepareStatement(query)) 
			{
				ps.setString(1, sDate);
				ps.setTimestamp(2, eDate);
				
				ResultSet rr = ps.executeQuery();
				while(rr.next())
				{
					LoanInfo vv = new LoanInfo();
					
					vv.setUserId(rr.getString("user_id"));
					vv.setContactNo(rr.getString("mobile_no"));
					vv.setSendDt(rr.getString("sent_time"));
					vv.setMessage(rr.getString("message"));
					
					list.add(vv);
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		}
		catch(Exception e)
		{
			 e.printStackTrace();
		}
		
		return list;
	}
	
	
	
	public ArrayList<EnachDataExportInfo> selectEnachLoanNo(Connection conn, java.util.Date fromDate, java.util.Date toDate, 
															String dsaCode)
	{
		Timestamp tt = null,tf = new Timestamp(fromDate.getTime());
		tf = new Timestamp(toDate.getTime());
		
		ArrayList<EnachDataExportInfo>list = new ArrayList<>();

		String qq = "SELECT loanNo FROM personalloan";
		
		if (!dsaCode.equalsIgnoreCase("all")) 
		{
			qq += " WHERE dsacode = '"+ dsaCode +"' ";
		}
		
		try(PreparedStatement st = conn.prepareStatement(qq)) 
		{	
			ResultSet rr =st.executeQuery();	
			while(rr.next())
			{
				EnachDataExportInfo ss=new EnachDataExportInfo();
				ss.setLoanNo(rr.getString("loanNo"));
				list.add(ss);
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		
		
		qq = "SELECT sub_loan_no FROM grouploan";
		
		if (!dsaCode.equalsIgnoreCase("all")) 
		{
			qq += " WHERE dsacode = '"+ dsaCode +"' ";
		}
		
		try(PreparedStatement st = conn.prepareStatement(qq)) 
		{	
			ResultSet rr = st.executeQuery();	
			while(rr.next())
			{
				EnachDataExportInfo ss = new EnachDataExportInfo();
				ss.setLoanNo(rr.getString("sub_loan_no"));
				list.add(ss);
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return list;
	}
	
	
	
	public BankInfo fetchBankInfo()
	{
		BankInfo bi = new BankInfo();
		
		try (Connection conn = DataBaseConnection.JAVAConnection()) 
		{
			String qq = "SELECT *  FROM bank_table";
			
			try (PreparedStatement ps = conn.prepareStatement(qq))
			{
				ResultSet rs = ps.executeQuery();
				if (rs.next()) 
				{
					bi.setBank_name(rs.getString("bank_name"));
					bi.setAcc_no(rs.getString("acc_no"));
					bi.setBank_IFSC(rs.getString("bank_IFSC"));
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return bi;
	}
	
	
	
	public String fetchLastEMIPaymentByLoanNo(Connection conn, String loanNo, java.util.Date startDate, java.util.Date endDate)
	{
		try 
		{
			String qq = "SELECT * FROM loanpayment WHERE loanNo = ? AND pay_date BETWEEN ? AND ? ORDER BY id DESC LIMIT 1 ";
			
			try (PreparedStatement ps = conn.prepareStatement(qq))
			{
				ps.setString(1, loanNo);
				ps.setDate(2, new java.sql.Date(startDate.getTime()));
				ps.setDate(3, new java.sql.Date(endDate.getTime()));
				
				ResultSet rs = ps.executeQuery();
				if (rs.next()) 
				{
					Date date = rs.getDate("pay_date");
					return dateObj.format(date);
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return "";
	}
	
	
	
	public String EMIDateByLoanNo(Connection conn, String loanNo)
	{
		try 
		{
			String qq = "SELECT * FROM loanpayment WHERE loanNo = ? ORDER BY id DESC LIMIT 1 ";
			
			try (PreparedStatement ps = conn.prepareStatement(qq))
			{
				ps.setString(1, loanNo);
				
				ResultSet rs = ps.executeQuery();
				if (rs.next()) 
				{
					Date date = rs.getDate("pay_date");
					return dateObj.format(date);
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return "";
	}
	
	
	
	public String shareholderNoByLoanNo(Connection conn, String loanNo) 
	{
		try  
		{
			String qq = "SELECT shareholderno, loanNo FROM debit_loan WHERE loanNo = ?";
			
			try (PreparedStatement ps = conn.prepareStatement(qq))  
			{
				ps.setString(1, loanNo);
				
				ResultSet rs = ps.executeQuery();
				if (rs.next()) 
				{
					return rs.getString("shareholderno");
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return "";
	}
	
	
	
	public String shareholderNameByShareHolderNo(Connection conn, String shareholderno) 
	{
		try  
		{
			String qq = "SELECT first_applicantName, shareholderno FROM shareholder_info WHERE shareholderno = ?";
			
			try (PreparedStatement ps = conn.prepareStatement(qq))  
			{
				ps.setString(1, shareholderno);
				
				ResultSet rs = ps.executeQuery();
				if (rs.next()) 
				{
					return rs.getString("first_applicantName");
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return "";
	}
	
	
	
	public String shareholderMobileNoByShareHolderNo(Connection conn, String shareholderno) 
	{
		try  
		{
			String qq = "SELECT mobile, shareholderno FROM shareholder_info WHERE shareholderno = ?";
			
			try (PreparedStatement ps = conn.prepareStatement(qq))  
			{
				ps.setString(1, shareholderno);
				
				ResultSet rs = ps.executeQuery();
				if (rs.next()) 
				{
					return rs.getString("mobile");
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return "";
	}
	
	
	
	public ArrayList<LoanInfo> viewLoanUmrnlist(int branchId)
	{
	    ArrayList<LoanInfo> umrnlist = new ArrayList<>();

	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();

	        String query = "SELECT d.loanNo, d.shareholderno, d.loanType, d.umrn_no, d.branch_id, d.status, d.amount, s.mobile "
	        			 + "FROM debit_loan d LEFT JOIN shareholder_info s ON d.shareholderno = s.shareholderno " +
	                       "WHERE d.status = ? ORDER BY d.loanNo";

	        ps = conn.prepareStatement(query);

	        ps.setString(1, "cleared");
	        
	        rs = ps.executeQuery();

	        int i = 1;
	        LoanInfo lastLoan = null;

	        while (rs.next())
	        {
	            String currentLoanNo = rs.getString("loanNo");
	            currentLoanNo = currentLoanNo.replace("-", "");
	            double currentAmount = rs.getDouble("amount");

	            if (lastLoan != null && lastLoan.getLoanNo().equalsIgnoreCase(currentLoanNo))
	            {
	                lastLoan.setAmount(lastLoan.getAmount() + currentAmount);
	            }
	            else
	            {
	                LoanInfo ll = new LoanInfo();

	                ll.setSno(i++);
	                ll.setLoanNo(currentLoanNo);
	                ll.setUmrnNo(rs.getString("umrn_no"));
	                ll.setShareHolderNo(rs.getString("shareholderno"));
	                ll.setMobile(rs.getString("mobile"));
	                ll.setAmount(currentAmount);

	                if (ll.getUmrnNo() != null && !ll.getUmrnNo().trim().isEmpty()) 
	                {
	                	double emiAmount = fetchActiveLoanEMIAmountByLoanTypeAndLoanNo(conn, ll.getLoanNo(), 
	                																   rs.getString("loanType"));
		                if (emiAmount > 0)
		                {
		                	ll.setAmount(emiAmount);
		                	umrnlist.add(ll);
		                }
					}
	                
	                lastLoan = ll;
	            }
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if (rs != null) rs.close();

	            if (ps != null) ps.close();

	            if (conn != null) conn.close();
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }

	    return umrnlist;
	}
	
	
	
	public ArrayList<LoanInfo> fetchLoanUmrnlist(int branchId, String memberNo)
	{
	    ArrayList<LoanInfo> umrnlist = new ArrayList<>();

	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();

	        StringBuilder query = new StringBuilder();

	        query.append("SELECT ")
	             .append("d.loanNo, ")
	             .append("d.shareholderno, ")
	             .append("d.loanType, ")
	             .append("d.umrn_no, ")
	             .append("SUM(d.amount) AS totalAmount, ")
	             .append("s.mobile ")
	             .append("FROM debit_loan d ")
	             .append("LEFT JOIN shareholder_info s ")
	             .append("ON d.shareholderno = s.shareholderno ")
	             .append("WHERE d.status = ? ");

	        if (memberNo != null && !"all".equalsIgnoreCase(memberNo))
	        {
	            query.append("AND d.shareholderno = ? ");
	        }

	        query.append("GROUP BY ")
	             .append("d.loanNo, ")
	             .append("d.shareholderno, ")
	             .append("d.loanType, ")
	             .append("d.umrn_no, ")
	             .append("s.mobile ")
	             .append("ORDER BY d.loanNo");

	        ps = conn.prepareStatement(query.toString());

	        int index = 1;

	        ps.setString(index++, "cleared");

	        if (memberNo != null && !"all".equalsIgnoreCase(memberNo))
	        {
	            ps.setString(index++, memberNo);
	        }

	        System.out.println(query);
	        
	        rs = ps.executeQuery();

	        int sno = 1;

	        while (rs.next())
	        {
	            LoanInfo ll = new LoanInfo();

	            ll.setSno(sno++);
	            ll.setLoanNo(rs.getString("loanNo"));
	            ll.setUmrnNo(rs.getString("umrn_no"));
	            ll.setShareHolderNo(rs.getString("shareholderno"));
	            ll.setMobile(rs.getString("mobile"));
	            ll.setAmount(rs.getDouble("totalAmount"));

	            double emiAmount = fetchActiveLoanEMIAmountByLoanTypeAndLoanNo(conn, ll.getLoanNo(), rs.getString("loanType"));
	            if (emiAmount > 0)
	            {
	                umrnlist.add(ll);
	            }
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if (rs != null) rs.close();

	            if (ps != null) ps.close();

	            if (conn != null) conn.close();
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }

	    return umrnlist;
	}
	
	
	
	public double fetchActiveLoanEMIAmountByLoanTypeAndLoanNo(Connection conn, String loanNo, String loanType)
	{
		String loanNoColumn = "loanNo";
		if (loanType.equalsIgnoreCase("grouploan")) 
		{
			loanNoColumn = "sub_loan_no";
		}
		
		try
		{
			String qq = "SELECT emi, loanNo, paidStatus FROM "+ loanType +" WHERE "+ loanNoColumn +" = ? AND status = 'Approve' "
					  + "AND paidStatus = 'unpaid' ";
			
			PreparedStatement ps = conn.prepareStatement(qq);
			
			ps.setString(1, loanNo);
			
			ResultSet rs = ps.executeQuery();
			if (rs.next()) 
			{
				return rs.getDouble("emi");
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public double paidPenaltyAmountByLoanNoAndDate(String loanNo, Date payDate, Connection conn)
	{
		try
		{
			String qq = "SELECT amount FROM income_table WHERE loan_no = ? AND item = 'Receive Penalty' AND income_date = ?";
			
			PreparedStatement ps = conn.prepareStatement(qq);
			
			ps.setString(1, loanNo);
			ps.setDate(2, payDate);
			
			ResultSet rs = ps.executeQuery();
			if (rs.next()) 
			{
				return rs.getDouble("amount");
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public List<String> showLoanNoByLoanType(int branchId,String name, String loanType) 
	{
		ArrayList<String> list = new ArrayList<String>();
		String query = "";
		String loanNoColumn = "loanNo";
		
		if(loanType.equals("grouploan"))
		{
			query = "select * from " + loanType + " INNER JOIN shareholder_info on "
				  + "" + loanType + ".teamLeader = shareholder_info.shareholderno where (loanNo like '%" + name + "%' or "
				  + "shareholder_info.first_applicantName like '%" + name + "%') and " + loanType + ".status = 'Approve' "
				  + "and " + loanType + ".paidStatus = 'unpaid' and shareholder_info.branch_id = '" + branchId + "' ";
			loanNoColumn = "sub_loan_no";
		}
		else
		{
			query = "select * from "+loanType+" INNER JOIN shareholder_info on "
				  + ""+loanType+".shareholderno=shareholder_info.shareholderno  where (loanNo like '%"+ name + "%' or "
				  + "shareholder_info.first_applicantName like '%"+ name + "%') and "+loanType+".status='Approve' "
				  + "and "+loanType+".paidStatus = 'unpaid' and shareholder_info.branch_id = '"+branchId+"' ";
			loanNoColumn = "loanNo";
		}
		
		query += " limit 25 ";
		
		try (Connection conn = DataBaseConnection.JAVAConnection();)
		{
			Statement st = conn.createStatement();
			
			ResultSet rr = st.executeQuery(query);
			while (rr.next())
			{
				String name1 = rr.getString(loanNoColumn) + "/" + rr.getString("shareholder_info.shareholderno") + " - " 
							 + rr.getString("shareholder_info.first_applicantName") + " - " 
							 + rr.getString("shareholder_info.father_husband_name");
				list.add(name1);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	public ArrayList<LoanInfo> currentLoanStatusList(int branchId, String loanType, Connection conn, String searchType, 
													 String frequency, String dsaCode, java.util.Date startDate, 
													 java.util.Date endDate, String dealer) 
	{
		String sDate = sdfForDb.format(startDate);
		String eDate = sdfForDb.format(endDate);
		String query = "";
		ArrayList<LoanInfo> list = new ArrayList<>();
		Statement stt = null; 
		ResultSet ss = null;
		
		try 
		{
			stt = conn.createStatement();
			
			if (searchType.equals("all")) 
			{
				query = "select * from " + loanType + " where status = 'Approve' and restructure_status = 'no' ";
			} 
			else 
			{
				query = "select * from " + loanType + " where status = 'Approve' and paidStatus = '" + searchType + "' and "
					  + "restructure_status = 'no' ";
			}
			
			if (!frequency.equalsIgnoreCase("all")) 
			{
				query += " and frequency in ('" + frequency + "','" + frequency + ".0') ";
			}
			
			if (!dsaCode.equalsIgnoreCase("all")) 
			{
				query += " and dsacode='" + dsaCode + "' ";
			}
			
			if (loanType.equalsIgnoreCase("secure_loan")) 
			{
				if (!dealer.equalsIgnoreCase("all")) 
				{
					query += " and mob_dealer = '"+ dealer +"' ";
				}
			}
			
			query = selectQueWithBranch(query, branchId) + " and approvalDate>='" + sDate + "' and "
														 + "approvalDate<='" + eDate + "' order by loanNo asc";
			
			System.out.println("Final Query: " + query);
			
			Map<String, String> manualChargesMap = new HashMap<>();
			PreparedStatement pstCharges = conn.prepareStatement("SELECT loan_no, SUM(amount) AS total_amount FROM manual_charges "
															   + "GROUP BY loan_no");
			ResultSet rsCharges = pstCharges.executeQuery();
			while (rsCharges.next()) 
			{
				manualChargesMap.put(rsCharges.getString("loan_no"), rsCharges.getString("total_amount"));
			}
			
			rsCharges.close();
			pstCharges.close();
			
			ss = stt.executeQuery(query);
			while (ss.next()) 
			{
				LoanInfo info = new LoanInfo();
				String shno = "";
				
				if (loanType.equals("grouploan")) 
				{
					shno = ss.getString("teamLeader");
				} 
				else
				{
					shno = ss.getString("shareholderno");
				}
				
				UserInfo uu = db.userInfoByShareHolderNo(shno, conn);
				
				if (loanType.equals("grouploan")) 
				{
					info.setLoanNo(ss.getString("sub_loan_no"));
				} 
				else 
				{
					info.setLoanNo(ss.getString("loanNo"));
				}
				
				info.setShareHolderNo(shno);
				info.setApplicantName(uu.getFirst_applicantName());
				info.setFatherHusbandName(uu.getFather_husband_name());
				info.setContactNo(uu.getContactNo());
				info.setAddress(uu.getAddress());
				info.setId(ss.getString("id"));
				info.setLoanAmount(ss.getString("loanAmount"));
				
				double interestAmount = (ss.getDouble("terms") * ss.getDouble("emi")) - ss.getDouble("loanAmount");
				info.setInterestAmount(String.valueOf(interestAmount));
				info.setRequestDate(ss.getDate("requestDate"));
				info.setPaidStatus(ss.getString("paidStatus"));
				info.setStrRequestDate(dateObj.format(ss.getDate("requestDate")));
				info.setIntrestPaid(db.totalIntrestPaidByLoanNo(info.getLoanNo()));
				
				double paidpp = db.totalPrincipalPaidByLoanNo(info.getLoanNo());
				info.setPrincipalPaid(Double.parseDouble(dc.format(paidpp)));
				info.setPrincipalBalance(Double.parseDouble(info.getLoanAmount()) - info.getPrincipalPaid());
				
				if (info.getPrincipalBalance() < 0 || ss.getString("paidStatus").equals("paid")) 
				{
					info.setPrincipalBalance(0);
				}
				
				info.setPrincipalBalance(Double.parseDouble(dc.format(info.getPrincipalBalance())));
				info.setApprovalDate(ss.getDate("approvalDate"));
				info.setStrApprovalDate(dateObj.format(ss.getDate("approvalDate")));
				
				ArrayList<java.util.Date> dateList = db.loanEmiDateList(ss.getInt("frequency"), ss.getInt("terms"), 
																		ss.getDate("emi_due_date"), ss.getDate("approvalDate"));
				if (!dateList.isEmpty()) 
				{
					java.util.Date closeDate = dateList.get(dateList.size() - 1);
					info.setStrLoanCloseDate(dateObj.format(closeDate));
				}
				
				String manualAmount = manualChargesMap.getOrDefault(info.getLoanNo(), "0");
				String manualChargeAmount = manualChargesMap.getOrDefault(info.getLoanNo(), "0");
				info.setManualChargeAmount(Double.parseDouble(manualAmount)); 
								
				list.add(info);
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		} 
		
		finally 
		{
			try 
			{
				if (stt != null) stt.close();
				if (ss != null) ss.close();
			} 
			catch (Exception e2) 
			{
				e2.printStackTrace();
			}
		}
		
		return list;
	}
	
	
	
	public ArrayList<SelectItem> dealerList(Connection con)
	{
		ArrayList<SelectItem> list = new ArrayList<SelectItem>();
		Statement st = null; ResultSet rs = null;
        
		try 
		{ 
			st = con.createStatement();
			String query = "select id, name from dealers where status = 'active' ";
		  
			rs = st.executeQuery(query);
			while(rs.next())
			{
				SelectItem obj  = new SelectItem();
			  	
				obj.setValue(rs.getString("id"));
			  	obj.setLabel(rs.getString("name"));
			  	
			  	list.add(obj);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		finally 
		{
			try
			{
				if(st!=null)
				{
					st.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} 
			catch (Exception e2) 
			{
				e2.printStackTrace();
			}
		}
		
		return list;
	}
	
	
	
	public String selectQueWithBranch(String query, int branchId)
	{
		if(branchId == -1)
		{
			
			if(query.contains("where"))
			{
				query = query + " and " + new DataBaseMethods().branchBuilder();
			}
			else
			{
				query = query + " where" + new DataBaseMethods().branchBuilder();
			}
		}
		else
		{
			if(query.contains("where"))
			{
				query = query + " and branch_id = '" + branchId + "' ";
			}
			else
			{
				query = query + " where branch_id = '" + branchId + "' ";
			}
		}
		
		return query;
	}
	
	
	
	public ArrayList<UserInfo> dailyBounceCollection(int branchId, java.util.Date startDate, java.util.Date endDate, 
													 String payMode, Connection conn) 
	{
		int count = 1;
		
		String dt = sdfForDb.format(startDate);
		String et = sdfForDb.format(endDate);
		
		ArrayList<UserInfo> list = new ArrayList<>();
		
		Statement st = null;
		ResultSet rr = null;
		
		try
		{
			st = conn.createStatement();
			
			String qq = "select * from income_table where income_date >= '" + dt + "' and  income_date <= '" + et + "' and "
					  + "loan_no != '' and status = 'active' and chequeStatus = 'cleared' and "
					  + "item = 'Receive Cheque Bounce Charge'  ";
			
			if(!payMode.equalsIgnoreCase("all"))
			{
			if(payMode.equalsIgnoreCase("bank"))
			{
			qq += " and payment_mode not in ('cash','transfer') ";
			}
			else
			{
			qq += " and payment_mode='"+payMode+"' ";
			}
			}
			
			rr = st.executeQuery(selectQueWithBranch(qq,branchId));
						
			while(rr.next())
			{
				if(rr.getString("loan_no") == null)
				{
				
				}
				else
				{
					UserInfo uu = new UserInfo();
					
					uu.setSno(count++);
					uu.setShareHolderNo(db.shareHolderNoByLoanNo(rr.getString("loan_no")));
					uu.setFirst_applicantName(db.firstApplicantNameBySHNo(uu.getShareHolderNo(),conn));
					uu.setAccountNo(rr.getString("loan_no"));
					uu.setPayMode(rr.getString("payment_mode"));
					uu.setAmount(rr.getDouble("amount"));
					uu.setEntryDateStr(dateObj.format(rr.getDate("income_date")));
					uu.setDiscount(Double.parseDouble(dc.format(rr.getDouble("discount"))) );
					uu.setRemark(rr.getString("description"));  
					
					list.add(uu);
					}
			
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		finally
		{
			try 
			{
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} 
			catch (Exception e2)
			{
				e2.printStackTrace();
			}
		}
		
		return list;
	}
	
	
	
	public ArrayList<IncomeList> salaryLedgerOfEmp(String selectedEmp)
	{
		ArrayList<IncomeList> list = new ArrayList<>();
		try(Connection conn = DataBaseConnection.JAVAConnection())
		{
			String type = "";
			Statement st = conn.createStatement();
			String query = "select * from salary_ledger where empId= '" + selectedEmp + "' and chequeStatus='cleared'";
			
			ResultSet rr = st.executeQuery(query);
			while(rr.next())
			{
				IncomeList ll = new IncomeList();
				type = rr.getString("type");
				
				if(type.equalsIgnoreCase("Debit"))
				{
					ll.setDebit(rr.getDouble("amount"));
				}
				else if(type.equalsIgnoreCase("Credit"))
				{
					ll.setCredit(rr.getDouble("amount"));
				}
				
				ll.setIncomeDate(rr.getTimestamp("payDate"));
				
				String desc = rr.getString("description");
				if(desc == null)
				{
					desc = "";
				}
				
				ll.setBankName(new DataBaseMethodsSalary().monthList(rr.getInt("month")) + " " + rr.getInt("year"));
				ll.setDescription(desc);
				ll.setStrIncomeDate(dateObj.format(ll.getIncomeDate()));
				list.add(ll);
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	public ArrayList<IncomeList> salaryLedgerOfEmpFromCreationII(String selectedEmp)
	{
		ArrayList<IncomeList> list = new ArrayList<>();
		try(Connection conn = DataBaseConnection.JAVAConnection())
		{
			String type = "";
			
			Statement st = conn.createStatement();
			
			String query = "select * from emp_salary where employee_name = '" + selectedEmp + "' and status = 'unpaid' and "
						 + "salary_status = 'active' ";
			
			ResultSet rr = st.executeQuery(query);
			while(rr.next())
			{
				IncomeList ll = new IncomeList();
				type = "Debit";
				
				if(type.equalsIgnoreCase("Debit"))
				{
					ll.setDebit(rr.getDouble("grand_total"));
				}
				else if(type.equalsIgnoreCase("Credit"))
				{
					ll.setCredit(rr.getDouble("grand_total"));
				}
				
				ll.setIncomeDate(rr.getTimestamp("salary_pay_date"));
				
				String desc = rr.getString("remark");
				if(desc == null)
				{
					desc = "";
				}
				
				ll.setBankName(new DataBaseMethodsSalary().monthList(rr.getInt("salary_month")) + " " + rr.getInt("salary_year"));
				ll.setDescription(desc);
				
				if (ll.getIncomeDate() != null) 
				{
					ll.setStrIncomeDate(dateObj.format(ll.getIncomeDate()));
				}
				
				list.add(ll);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	public int deleteSalaryCreationIIEntry(String id) 
	{
		try (Connection conn = DataBaseConnection.JAVAConnection())
		{
			String qq = "UPDATE emp_salary SET salary_status = 'inactive' where id = ? ";
			
			try (PreparedStatement ps = conn.prepareStatement(qq))
			{
				ps.setString(1, id);
				
				int i = ps.executeUpdate();
				return i;
			}
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public int deleteSalaryLedgerEntry(String empId,String month,String year) 
	{
		System.out.println("hit delete salary ledger method"+empId);
		System.out.println("month is"+month);
		System.out.println("year is"+year);
        
		try (Connection conn = DataBaseConnection.JAVAConnection())
		{
			String qq = "UPDATE salary_ledger SET chequeStatus = 'uncleared' where empId = ? and month = ? and year = ? ";
			
			try (PreparedStatement ps = conn.prepareStatement(qq))
			{
				ps.setString(1, empId);
				ps.setString(2, month);
				ps.setString(3, year);

				int i = ps.executeUpdate();
				return i;
			}
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public int updateEmpSalaryEntryPayStatus(String name, String month, String year) 
	{
	    String query = "UPDATE emp_salary SET status = 'unpaid' WHERE employee_name = ? AND salary_month = ? AND salary_year = ?";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query)) 
	    {
	        ps.setString(1, name);
	        ps.setString(2, month);
	        ps.setString(3, year);

	        return ps.executeUpdate();
	    } 
	    catch (Exception e) 
	    {
	        System.err.println("Error updating salary status for: " + name + " | " + month + "-" + year);
	        e.printStackTrace();
	    }

	    return 0;
	}
	
	
	
	public String secureLoanDetailsByLoanNoAndColumnName(String loanNo, String columnName) 
	{
		try(Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			String	qq = "SELECT * FROM secure_loan WHERE loanNo = ? ";
			
			try (PreparedStatement ps = conn.prepareStatement(qq);) 
			{
				ps.setString(1, loanNo);
				
				try (ResultSet rs = ps.executeQuery())
				{
					if(rs.next())
					{
						return rs.getString(columnName);
					}
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
			
		}
		catch (Exception e)
		{
			 e.printStackTrace();
		} 
		
		return "";
	}
	
	
	
	public String fetchValueByTableNameAndId(String tableName, String id, String valueColumn) 
	{
		try(Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			String	qq = "SELECT * FROM "+ tableName +" WHERE id = ? ";
			
			try (PreparedStatement ps = conn.prepareStatement(qq);) 
			{
				ps.setString(1, id);
				
				try (ResultSet rs = ps.executeQuery())
				{
					if(rs.next())
					{
						return rs.getString(valueColumn);
					}
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
			
		}
		catch (Exception e)
		{
			 e.printStackTrace();
		} 
		
		return "";
	}
	
	
	
	public ArrayList<NachMandateData> fetchSubscriptionByLoanNo() 
	{
		ArrayList<NachMandateData> allLoanNo = new ArrayList<>();
		
		String query = "SELECT * FROM subscriptions_overview WHERE subscription_status = 'ACTIVE' ";

		try (Connection conn = DataBaseConnection.JAVAConnection();
			 PreparedStatement ps = conn.prepareStatement(query)) 
		{
			try (ResultSet rs = ps.executeQuery()) 
			{
				while (rs.next()) 
				{
					NachMandateData nm = new NachMandateData();
					
					nm.setLn(rs.getString("loan_no"));
					
					String type = "";
					if (nm.getLn() != null) 
					{
						if (nm.getLn().contains("PL")) 
						{
							type = "personalloan";
						}
						else if (nm.getLn().contains("GL"))
						{
							type = "grouploan";
						}
						else if (nm.getLn().contains("SL")) 
						{
							type = "secure_loan";
						}
					}
					
					nm.setAmount(Double.valueOf(loanInfoByLoanNoTypeColumn(nm.getLn(), type, "emi")));
					nm.setCustomerName(rs.getString("customer_name"));
					nm.setMobile(rs.getString("customer_phone"));
					nm.setStartDate(rs.getDate("subscription_first_charge_time"));
					nm.setEndDate(rs.getDate("subscription_expiry_time"));
					nm.setPlanIntervalType(rs.getString("plan_interval_type"));
					nm.setPlanInterval(rs.getInt("plan_intervals"));
					
					allLoanNo.add(nm);
				}
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace(); 
		}
 
		return allLoanNo;
	}
	
	
	
	public String loanInfoByLoanNoTypeColumn(String loanNo, String loanType, String columnName)
	{
		try (Connection conn = DataBaseConnection.JAVAConnection()) 
		{
			String qq = "SELECT " + columnName + " FROM " + loanType + " WHERE loanNo = ? ";
			
			try (PreparedStatement ps = conn.prepareStatement(qq))
			{
				ps.setString(1, loanNo);
				
				ResultSet rs = ps.executeQuery();
				if (rs.next()) 
				{
					return rs.getString(columnName);
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return "";
	}
	
	
	
	public LoanInfo loanCloserList(String loanNo, LoanInfo info) 
	{
	    String query = "SELECT * FROM add_loan_closer WHERE loan_no = ?";
	
	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query)) 
	    {
	        ps.setString(1, loanNo);
	
	        try (ResultSet ss = ps.executeQuery()) 
	        {
	            if (ss.next()) 
	            {
	                info.setRecieptNo(ss.getString("receipt_no"));
	                info.setDiscountPenalty(ss.getDouble("discount_penalty"));
	                info.setDiscountChequebounce(ss.getDouble("discount_chequebounce"));
	                info.setDiscountInterest(ss.getDouble("discount_interest"));
	
	                info.setTotalPrincipalRemain(ss.getDouble("principal_amount_remaining"));
	                info.setTotalPenaltyRemain(ss.getDouble("total_penalty_remain"));
	
	                String companyName = companyNameByBranchId(ss.getInt("branch_id"));
	
	                if (!"DHAKA CARE ASSOCIATION".equalsIgnoreCase(companyName)) 
	                {
	                    info.setTotalIntrestRemain(ss.getDouble("total_intrest_remain"));
	                }
	                else 
	                {
	                    info.setTotalIntrestRemain(totalInterestPaidByLoanNo(loanNo));
	                }
	
	                info.setTotalChqChargeRemain(ss.getDouble("total_chq_charge_remain"));
	                info.setTotalRemaining(ss.getDouble("total_remaining"));
	                info.setDiscount(ss.getInt("discount"));
	                info.setDsaName(ss.getString("dsa_name"));
	                info.setPaidAmount(ss.getDouble("amount"));
	
	                java.sql.Date paidDate = ss.getDate("paid_date");
	
	                if (paidDate != null) 
	                {
	                    info.setPaidDateStr(dateObj.format(paidDate));
	                    info.setPaidDate(paidDate);
	                } 
	                else
	                {
	                    info.setPaidDateStr(null);
	                    info.setPaidDate(null);
	                }
	
	                String paymentMode = ss.getString("payment_mode");
	                info.setCheckPaymentMode(paymentMode != null && !"cash".equalsIgnoreCase(paymentMode));
	                info.setPaymentMode(paymentMode);
	                info.setChqNo(ss.getString("cheque_no"));
	
	                java.sql.Date chequeDate = ss.getDate("cheque_date");
	
	                if (chequeDate != null) 
	                {
	                    info.setChqDateStr(dateObj.format(chequeDate));
	                } 
	                else 
	                {
	                    info.setChqDateStr(null);
	                }
	
	                info.setTransfer(ss.getString("transfer"));
	                info.setForeclosureCharges(ss.getDouble("foreclosure_charges"));
	            }
	        }
	    }
	    catch (Exception e) 
	    {
	        e.printStackTrace(); 
	    }
	
	    return info;
	}

	
	
	public double totalInterestPaidByLoanNo(String loanNo)
	{
		try (Connection conn = DataBaseConnection.JAVAConnection())
		{
			String qq = "SELECT SUM(interestAmount) AS total FROM loanpayment WHERE loanNo = ? AND emi_no != '-1' AND "
					  + "receipt_status = 'approved' AND chequeStatus = 'cleared' ";
			
			try (PreparedStatement ps = conn.prepareStatement(qq))
			{
				ps.setString(1, loanNo);
				
				try (ResultSet rs = ps.executeQuery())
				{ 
					
					if (rs.next()) 
					{
						return rs.getDouble("total");
					}
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public static java.util.Date getCurrentMonthStartDate() 
	{
	    Calendar cal = Calendar.getInstance();
	    cal.set(Calendar.DAY_OF_MONTH, 1);
	    cal.set(Calendar.HOUR_OF_DAY, 0);
	    cal.set(Calendar.MINUTE, 0);
	    cal.set(Calendar.SECOND, 0);
	    cal.set(Calendar.MILLISECOND, 0);
	    return cal.getTime();
	}

	
	
	public static java.util.Date getCurrentMonthEndDate() 
	{
	    Calendar cal = Calendar.getInstance();
	    cal.set(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH));
	    cal.set(Calendar.HOUR_OF_DAY, 23);
	    cal.set(Calendar.MINUTE, 59);
	    cal.set(Calendar.SECOND, 59);
	    cal.set(Calendar.MILLISECOND, 999);
	    return cal.getTime();
	}
	

	
	public ArrayList<LoanInfo> debitLoanList(int branchId, String loanType, Date startdate, Date enddate, String dsaCode, 
			 Connection conn)
	{
		int i = 1;
		String qq = "";
		String sDate = sdfForDb.format(startdate);
		String eDate = sdfForDb.format(enddate);
		
		ArrayList<LoanInfo> list = new ArrayList<>();
		Statement st = null;
		ResultSet rs = null;
		
		try
		{
			st = conn.createStatement();
			
			qq = "SELECT dl.loanNo, dl.loanType, dl.shareholderno, dl.branch_id, "
			   + "SUM(CASE WHEN dl.paymentMode='Cash' THEN dl.amount ELSE 0 END) AS cashAmount, "
			   + "SUM(CASE WHEN dl.paymentMode!='Cash' THEN dl.amount ELSE 0 END) AS bankAmount, "
			   + "MAX(CASE WHEN dl.paymentMode!='Cash' THEN dl.bankName ELSE NULL END) AS bankName, "
			   + "MAX(CASE WHEN dl.paymentMode!='Cash' THEN dl.chequeNo ELSE NULL END) AS chequeNo, "
			   + "MAX(dl.date) AS date FROM debit_loan dl ";
			
			if (!loanType.equalsIgnoreCase("all"))
			{
				qq += " INNER JOIN " + loanType + " lt ON ";
				
				if (loanType.equalsIgnoreCase("grouploan"))
				{
					qq += " lt.sub_loan_no = dl.loanNo ";
				}
				else
				{
					qq += " lt.loanNo = dl.loanNo ";
				}
			}
			
			qq += " WHERE dl.status='cleared' ";
			
			if (!loanType.equalsIgnoreCase("all"))
			{
				qq += " AND dl.loanType='" + loanType + "' ";
			}
			
			if (dsaCode != null && !dsaCode.equalsIgnoreCase("") && !dsaCode.equalsIgnoreCase("all"))
			{
				qq += " AND lt.dsacode='" + dsaCode + "' ";
			}
			
			qq += " AND dl.date >= '" + sDate + "' AND dl.date <= '" + eDate + "' ";
			
			if (branchId != -1)
			{
				qq += " AND dl.branch_id='" + branchId + "' ";
			}
			
			qq += " GROUP BY dl.loanNo, dl.loanType, dl.shareholderno, dl.branch_id ";
			
			
			rs = st.executeQuery(qq);
			
			while (rs.next())
			{
				LoanInfo ll = new LoanInfo();
				
				ll.setSno(i++);
				ll.setShareHolderNo(rs.getString("shareholderno"));
				ll.setLoanType(rs.getString("loanType"));
				ll.setLoanNo(rs.getString("loanNo"));
				ll.setBranchId(rs.getInt("branch_id"));
				
				ll.setCashAmount(rs.getDouble("cashAmount"));
				ll.setBankAmount(rs.getDouble("bankAmount"));
				
				ll.setLoanAmount(String.valueOf(
				rs.getDouble("cashAmount") + rs.getDouble("bankAmount")));
				
				ll.setRequestDate(rs.getDate("date"));
				ll.setStrRequestDate(dateObj.format(rs.getDate("date")));
				ll.setChqNo(rs.getString("chequeNo"));
				
				if(!rs.getString("bankName").equals("N/A"))
				{
					ll.setBankId(rs.getString("bankName"));
					ll.setBankName(db.bankNameById(rs.getString("bankName")));
				}
				else
				{
					ll.setBankName(rs.getString("bankName"));
				}
				
				UserInfo uu = db.memberBasicDetail(ll.getShareHolderNo(), conn);
				ll.setApplicantName(uu.getName());
				ll.setFatherHusbandName(uu.getFather_husband_name());
				ll.setAddress(uu.getAddress());
				ll.setContactNo(uu.getContactNo());
				ll.setBrideFhname(uu.getBrideFhname());
				ll.setBrideMname(uu.getBrideMname());
				ll.setBrideAddress(uu.getBrideAddress());
				
				String branchName = db.selectBranchName(ll.getBranchId());
				ll.setBranchName(branchName);
				
				ll.setCenterName(db.centerNameCodeById(db.centerIdByGroupLoanNo(branchId, ll.getLoanNo(), conn), conn));
				
				LoanInfo ldt = db.loanInfoByLoanNoLoanType(ll.getLoanNo(), ll.getLoanType(), conn);
				
				ll.setDsacode(ldt.getDsacode());
				ll.setDsaName(ldt.getDsaName());
				ll.setTerms(ldt.getTerms());
				ll.setFrequency(ldt.getFrequency());
				ll.setEmiAmount(ldt.getInstallAmount());
				
				if (db.checkDataFreezing(branchId, DataBaseMethods.selectedSessionDetail(), conn))
				{
					ll.setDisable(true);
				}
				else
				{
					if (db.loanPaidAmountByUser(ll.getLoanType(), ll.getLoanNo(), conn) > 0)
					{
						ll.setDisable(true);
					}
					else
					{
						ll.setDisable(false);
					}
				}
				
				list.add(ll);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		finally
		{
			try
			{
				if (rs != null) rs.close();
				if (st != null) st.close();
			}
			catch (Exception e2)
			{
				e2.printStackTrace();
			}
		}
		
		return list;
	}



	public String companyNameByBranchId(int branchId) 
	{
		try(Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			String	qq = "SELECT name FROM firm_detail WHERE id = ? ";
			
			try (PreparedStatement ps = conn.prepareStatement(qq);) 
			{
				ps.setInt(1, branchId);
				
				try (ResultSet rs = ps.executeQuery())
				{
					if(rs.next())
					{
						return rs.getString("name");
					}
				}
			} 
			catch (Exception e) 
			{
			e.printStackTrace();
			}
			
		}
		catch (Exception e)
		{
			e.printStackTrace();
		} 
		
		return "";
	}
	
	
	
	public ArrayList<PerformanceDataInfo> branchPerformanceDataByType(int branchId, Date startDate, Date endDate, 
																	  Date yearStartDate, Date yearEndDate, 
																	  Date lastMonthStartDate, Date lastMonthEndDate,
																	  String dsaCode, java.util.Date searchDate)
	{	
	    ArrayList<PerformanceDataInfo> list = new ArrayList<>();

	    try (Connection conn = DataBaseConnection.JAVAConnection())
	    {
	    	String qq = "SELECT DISTINCT branch_id, branch_name FROM branch_allocate_table";
	        
	        if (branchId != -1) 
	        {
	        	qq += " WHERE branch_id = " + branchId + " " ;
			}
	        
	        try (PreparedStatement ps = conn.prepareStatement(qq);
	             ResultSet rs = ps.executeQuery())
	        {
	            while (rs.next())
	            {
	                PerformanceDataInfo pd = new PerformanceDataInfo();

	                pd.setBranchId(rs.getInt("branch_id"));
	                pd.setBranchName(rs.getString("branch_name"));
	                pd.setBranchManagerName(branchManagerNameAndByBranchId(conn, pd.getBranchId(), "name"));
	                pd.setBranchManagerId(branchManagerNameAndByBranchId(conn, pd.getBranchId(), "dsacode"));
	                
	                if (!dsaCode.equalsIgnoreCase("all")) 
	                {
	                	pd.setEmpId(dsaCode);
	                	pd.setEmpName(employeeNameBydsaCode(conn, dsaCode));
	                }
	                
	                pd.setTdLoginMTD(totalMemberByDate(conn, startDate, endDate, pd.getBranchId(), dsaCode));
	                pd.setTdLoginLMTD(totalMemberByDate(conn, lastMonthStartDate, endDate, pd.getBranchId(), dsaCode));
	                pd.setTdLoginYTD(totalMemberByDate(conn, yearStartDate, yearEndDate, pd.getBranchId(), dsaCode));
	                pd.setGldbMTD(totalDisburseAmountByDateAndLoanType(conn, startDate, endDate, pd.getBranchId(), "grouploan", 
	                												   dsaCode));
	                pd.setGldbLMTD(totalDisburseAmountByDateAndLoanType(conn, lastMonthStartDate, endDate, pd.getBranchId(),
	                													"grouploan", dsaCode));
	                pd.setGldbYTD(totalDisburseAmountByDateAndLoanType(conn, yearStartDate, yearEndDate, pd.getBranchId(), 
	                												   "grouploan", dsaCode));
	                pd.setBldbMTD(totalDisburseAmountByDateAndLoanType(conn, startDate, endDate, pd.getBranchId(), "personalloan", 
	                												   dsaCode));
	                pd.setBldbLMTD(totalDisburseAmountByDateAndLoanType(conn, lastMonthStartDate, endDate, pd.getBranchId(),
	                													"personalloan", dsaCode));
	                pd.setBldbYTD(totalDisburseAmountByDateAndLoanType(conn, yearStartDate, yearEndDate, pd.getBranchId(), 
	                													"personalloan", dsaCode));
	                pd.setGlcm(totalCenterByBranchId(conn, pd.getBranchId(), dsaCode));
	                pd.setGlTotalMembers(totalLoanMemberByBranchIdAndLoanType(conn, pd.getBranchId(), "grouploan", dsaCode));
	           
	                
	                pd.setGlosAmount(outstandingAmountByLoanType(conn, "grouploan", pd.getBranchId(), dsaCode));
	                pd.setBlMembers(totalLoanMemberByBranchIdAndLoanType(conn, pd.getBranchId(), "personalloan", dsaCode));
	                pd.setBlosAmount(outstandingAmountByLoanType(conn, "personalloan", pd.getBranchId(), dsaCode));
	                
	                
	                filterBucketFromList("grouploan", pd.getBranchId(), conn, pd, dsaCode, searchDate, startDate);
	                filterBucketFromList("personalloan", pd.getBranchId(), conn, pd, dsaCode, searchDate, startDate);

	                
	                pd.setNewGlBucketAddMTD(pd.getGlBucket1() - pd.getGlBucketCloseMTD1());
	                pd.setNewGlBucketAddAmountMTD(pd.getGlBucketAmount1() - pd.getGlBucketCloseAmountMTD1());
	                
					
					pd.setTotalGlBucketCloseMTD(pd.getGlBucketCloseMTD1() + pd.getGlBucketCloseMTD2() + pd.getGlBucketCloseMTD3() 
											  + pd.getGlBucketCloseMTD3plus());
					pd.setTotalGlBucketCloseAmountMTD(pd.getGlBucketCloseAmountMTD1() + pd.getGlBucketCloseAmountMTD2() + 
													  pd.getGlBucketCloseAmountMTD3() + pd.getGlBucketCloseAmountMTD3plus());
	              
					pd.setNewBlBucketAddMTD(pd.getBlBucket1() - pd.getBlBucketCloseMTD1());
					pd.setNewBlBucketAddAmountMTD(pd.getBlBucketAmount1() - pd.getBlBucketCloseAmountMTD1());

					
					pd.setTotalBlBucketCloseMTD(pd.getBlBucketCloseMTD1() + pd.getBlBucketCloseMTD2() + pd.getBlBucketCloseMTD3() 
											  + pd.getBlBucketCloseMTD3plus());
					pd.setTotalBlBucketCloseAmountMTD(pd.getBlBucketCloseAmountMTD1() + pd.getBlBucketCloseAmountMTD2() + 
								  					  pd.getBlBucketCloseAmountMTD3() + pd.getBlBucketCloseAmountMTD3plus());
	                
	                list.add(pd);
	            }
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	
	
	public ArrayList<PerformanceDataInfo> employeePerformanceData(int branchId, Date startDate, Date endDate, Date yearStartDate,
																  Date yearEndDate,Date lastMonthStartDate,Date lastMonthEndDate,
																  String dsaCode, java.util.Date searchDate)
	{	
		ArrayList<PerformanceDataInfo> list = new ArrayList<>();
		
		try (Connection conn = DataBaseConnection.JAVAConnection())
		{
			String qq = "SELECT * FROM employee WHERE status = 'active' ";
			
			if (branchId != -1) 
			{
				qq += " AND branch_id = " + branchId + " " ;
			}
			
			if (!"All".equalsIgnoreCase(dsaCode)) 
			{
				qq += " AND dsacode = '" + dsaCode + "' " ;
			}
			
			try (PreparedStatement ps = conn.prepareStatement(qq);
				 ResultSet rs = ps.executeQuery())
			{
				while (rs.next())
				{
					PerformanceDataInfo pd = new PerformanceDataInfo();
					
					pd.setBranchId(rs.getInt("branch_id"));
					pd.setBranchName(branchNameById(rs.getString("branch_id")));
					pd.setEmpId(rs.getString("dsacode"));
					pd.setEmpName(employeeNameBydsaCode(conn, rs.getString("dsacode")));
					
					pd.setTdLoginMTD(totalMemberByDate(conn, startDate, endDate, pd.getBranchId(), rs.getString("dsacode")));
					pd.setTdLoginLMTD(totalMemberByDate(conn, lastMonthStartDate, endDate, pd.getBranchId(), 
														rs.getString("dsacode")));
					pd.setTdLoginYTD(totalMemberByDate(conn, yearStartDate, yearEndDate, pd.getBranchId(), 
													   rs.getString("dsacode")));
					pd.setGldbMTD(totalDisburseAmountByDateAndLoanType(conn, startDate, endDate, pd.getBranchId(), "grouploan", 
								  rs.getString("dsacode")));
					pd.setGldbLMTD(totalDisburseAmountByDateAndLoanType(conn, lastMonthStartDate, endDate, pd.getBranchId(),
								   "grouploan", rs.getString("dsacode")));
					pd.setGldbYTD(totalDisburseAmountByDateAndLoanType(conn, yearStartDate, yearEndDate, pd.getBranchId(), 
								  "grouploan", rs.getString("dsacode")));
					pd.setBldbMTD(totalDisburseAmountByDateAndLoanType(conn, startDate, endDate, pd.getBranchId(), "personalloan", 
								  rs.getString("dsacode")));
					pd.setBldbLMTD(totalDisburseAmountByDateAndLoanType(conn, lastMonthStartDate, endDate, pd.getBranchId(),
									"personalloan", rs.getString("dsacode")));
					pd.setBldbYTD(totalDisburseAmountByDateAndLoanType(conn, yearStartDate, yearEndDate, pd.getBranchId(), 
									"personalloan", rs.getString("dsacode")));
					pd.setGlcm(totalCenterByBranchId(conn, pd.getBranchId(), rs.getString("dsacode")));
					pd.setGlTotalMembers(totalLoanMemberByBranchIdAndLoanType(conn, pd.getBranchId(), "grouploan", 
																			  rs.getString("dsacode")));
					
					
					pd.setGlosAmount(outstandingAmountByLoanType(conn, "grouploan", pd.getBranchId(), rs.getString("dsacode")));
					pd.setBlMembers(totalLoanMemberByBranchIdAndLoanType(conn, pd.getBranchId(), "personalloan", 
																		 rs.getString("dsacode")));
					pd.setBlosAmount(outstandingAmountByLoanType(conn, "personalloan", pd.getBranchId(), rs.getString("dsacode")));
					
					
					filterBucketFromList("grouploan", pd.getBranchId(), conn, pd, rs.getString("dsacode"), searchDate, startDate);
					filterBucketFromList("personalloan", pd.getBranchId(), conn, pd, rs.getString("dsacode"), searchDate, 
										 startDate);
					
					
					pd.setNewGlBucketAddMTD(pd.getGlBucket1() - pd.getGlBucketCloseMTD1());
					pd.setNewGlBucketAddAmountMTD(pd.getGlBucketAmount1() - pd.getGlBucketCloseAmountMTD1());
					
					
					pd.setTotalGlBucketCloseMTD(pd.getGlBucketCloseMTD1() + pd.getGlBucketCloseMTD2() + pd.getGlBucketCloseMTD3() 
											  + pd.getGlBucketCloseMTD3plus());
					pd.setTotalGlBucketCloseAmountMTD(pd.getGlBucketCloseAmountMTD1() + pd.getGlBucketCloseAmountMTD2() + 
													  pd.getGlBucketCloseAmountMTD3() + pd.getGlBucketCloseAmountMTD3plus());
					
					
					pd.setNewBlBucketAddMTD(pd.getBlBucket1() - pd.getBlBucketCloseMTD1());
					pd.setNewBlBucketAddAmountMTD(pd.getBlBucketAmount1() - pd.getBlBucketCloseAmountMTD1());
					
					
					pd.setTotalBlBucketCloseMTD(pd.getBlBucketCloseMTD1() + pd.getBlBucketCloseMTD2() + pd.getBlBucketCloseMTD3() 
											  + pd.getBlBucketCloseMTD3plus());
					pd.setTotalBlBucketCloseAmountMTD(pd.getBlBucketCloseAmountMTD1() + pd.getBlBucketCloseAmountMTD2() + 
													  pd.getBlBucketCloseAmountMTD3() + pd.getBlBucketCloseAmountMTD3plus());
					
					list.add(pd);
				}
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		return list;
	}
	
	
	
	public String branchManagerNameAndByBranchId(Connection conn, int branchId, String column)
	{
	    String sql = "SELECT id, name, dsacode FROM employee WHERE designation = 28 AND branch_id = ? AND status = 'active' ";

	    try (PreparedStatement ps = conn.prepareStatement(sql))
	    {
	        ps.setInt(1, branchId);

	        try (ResultSet rs = ps.executeQuery())
	        {
	            if (rs.next())
	            {
	            	if (column.equalsIgnoreCase("dsacode")) 
	            	{
						return rs.getString("dsacode");
					}
	            	else if (column.equalsIgnoreCase("name"))
	            	{
	            		return rs.getString("name");
	            	}
	            }
	            else 
	            {
	            	String ab = assistantBranchManagerNameAndByBranchId(conn, branchId, column);
	            	return ab;
				}
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }

	    return "";
	}
	
	
	
	public String assistantBranchManagerNameAndByBranchId(Connection conn, int branchId, String column)
	{
	    String sql = "SELECT id, name, dsacode FROM employee WHERE designation = 37 AND branch_id = ? AND status = 'active'";

	    try (PreparedStatement ps = conn.prepareStatement(sql))
	    {
	        ps.setInt(1, branchId);

	        try (ResultSet rs = ps.executeQuery())
	        {
	            if (rs.next())
	            {
	            	if (column.equalsIgnoreCase("dsacode")) 
	            	{
						return rs.getString("dsacode");
					}
	            	else if (column.equalsIgnoreCase("name"))
	            	{
	            		return rs.getString("name");
	            	}
	            }
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }

	    return "";
	}
	
	
	
	public int totalMemberByDate(Connection conn, Date startDate, Date endDate, int branchId, String dsacode)
	{
		String qq = "SELECT COUNT(*) AS total FROM shareholder_info WHERE office_date BETWEEN ? AND ? AND "
				  + "account_status = 'active' AND branch_id = " + branchId + " ";
		
		if (!dsacode.equalsIgnoreCase("all"))
		{
			qq += "AND dsacode = '"+ dsacode +"' ";
		}
		
		try (PreparedStatement ps = conn.prepareStatement(qq)) 
		{
			ps.setDate(1, startDate);
			ps.setDate(2, endDate);
			
			try (ResultSet rs = ps.executeQuery()) 
			{
				if (rs.next()) 
				{
					return rs.getInt("total");
				}
			} 
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public int totalLoanMemberByBranchIdAndLoanType(Connection conn, int branchId, String loanType, String dsacode)
	{
		if (loanType.equalsIgnoreCase("grouploan"))
		{
			int totalLoan = 0;
			ArrayList<CenterInfo> center_info = db.allCenterInfo(branchId, dsacode, conn);
			
			for(CenterInfo cc : center_info)
			{
				totalLoan += cc.getTotalLoan();
			}
			return totalLoan;
		}
		else
		{
			String qq = "SELECT COUNT(*) AS total FROM "+ loanType +" WHERE status = 'Approve' AND paidStatus = 'unpaid' AND "
					  + "branch_id = ? ";
			
			if (!dsacode.equalsIgnoreCase("all")) 
			{	
				qq += "AND dsacode = '" + dsacode + "' ";
			}
			
			try (PreparedStatement ps = conn.prepareStatement(qq))
			{
				ps.setInt(1, branchId); 
				
				try (ResultSet rs = ps.executeQuery()) 
				{
					if (rs.next()) 
					{
						return rs.getInt("total");
					}
				} 
			}
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		}
		
		return 0;
	}
	
	
	
	public List<String> allLoanMemberListByBranchIdAndLoanType(Connection conn, Date startDate, Date endDate, int branchId,
	        												   String loanType)
	{
	    List<String> list = new ArrayList<>();

	    if (!loanType.equalsIgnoreCase("grouploan") && !loanType.equalsIgnoreCase("personalloan")) 
	    {
	        return list;
	    }

	    String loanColumn = loanType.equalsIgnoreCase("grouploan") ? "sub_loan_no" : "loanNo";

	    String sql = "SELECT " + loanColumn + " FROM " + loanType + " WHERE status = 'Approve' AND paidStatus = 'unpaid' " +
	            	 " AND approvalDate BETWEEN ? AND ? AND branch_id = ?";

	    try (PreparedStatement ps = conn.prepareStatement(sql)) 
	    {
	        ps.setDate(1, startDate);
	        ps.setDate(2, endDate);
	        ps.setInt(3, branchId);

	        try (ResultSet rs = ps.executeQuery()) 
	        {
	            while (rs.next()) 
	            {
	                String loanNo = rs.getString(loanColumn);
	                if (loanNo != null && !loanNo.trim().isEmpty()) 
	                {
	                    list.add(loanNo);
	                }
	            }
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }
	    return list;
	}
	
	
	
	public int totalMemberBeforeDate(Connection conn, Date startDate, int branchId)
	{
		String qq = "SELECT COUNT(*) AS total FROM shareholder_info WHERE office_date < ? AND account_status = 'active' AND "
				  + "branch_id = ? " ;
		
		try (PreparedStatement ps = conn.prepareStatement(qq)) 
		{
			ps.setDate(1, startDate);
			ps.setInt(2, branchId);
			
			try (ResultSet rs = ps.executeQuery()) 
			{
				if (rs.next()) 
				{
					return rs.getInt("total");
				}
			} 
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public double totalDisburseAmountByDateAndLoanType(Connection conn, Date startDate, Date endDate, int branchId, String type, 
													   String dsacode)
	{
		String qq = "";
		
		if ("grouploan".equalsIgnoreCase(type)) 
		{
			qq = "SELECT SUM(amount) AS total FROM debit_loan inner join grouploan on grouploan.sub_loan_no = debit_loan.loanNo "
			   + "WHERE debit_loan.date BETWEEN ? AND ? AND loanType = 'grouploan' AND debit_loan.status = 'cleared' AND "
			   + "debit_loan.branch_id = ? ";
		}
		else 
		{
			qq = "SELECT SUM(amount) AS total FROM debit_loan inner join personalloan on personalloan.loanNo = debit_loan.loanNo "
			   + "WHERE debit_loan.date BETWEEN ? AND ? AND loanType = 'personalloan' AND debit_loan.status = 'cleared' AND "
			   + "debit_loan.branch_id = ? ";
		}
		
		if (!dsacode.equalsIgnoreCase("all")) 
		{
			qq += "AND " + type + ".dsacode = '"+ dsacode +"' ";
		}
		
		try (PreparedStatement ps = conn.prepareStatement(qq)) 
		{
			ps.setDate(1, startDate);
			ps.setDate(2, endDate);
			ps.setInt(3, branchId);
			
			try (ResultSet rs = ps.executeQuery()) 
			{
				if (rs.next()) 
				{
					return rs.getDouble("total");
				}
			} 
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public String branchNameById(String branchId)
	{
		String bn = "";
		
		try (Connection conn = DataBaseConnection.JAVAConnection()) 
		{
			String qq = "SELECT id, branch_name FROM branch_allocate_table WHERE branch_id = ?";
			
			try (PreparedStatement ps = conn.prepareStatement(qq))
			{
				ps.setString(1, branchId);
				
				ResultSet rs = ps.executeQuery();
				if (rs.next()) 
				{
					bn = rs.getString("branch_name");
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return bn;
	}
	
	
	
	public int totalCenterByBranchId(Connection conn, int  branchId, String dsacode)
	{
		String qq = "SELECT COUNT(*) AS total FROM center WHERE branch_id = ? ";
		
		if (!dsacode.equalsIgnoreCase("all")) 
		{
			qq += "AND dsacode = '"+ dsacode +"' ";
		}
		
		try (PreparedStatement ps = conn.prepareStatement(qq))
		{
			ps.setInt(1, branchId);
			
			try (ResultSet rs = ps.executeQuery())
			{
				if (rs.next()) 
				{
					return rs.getInt("total");
				}
			} 
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public double outstandingAmountByLoanType(Connection conn, String loanType, int branchId, String dsaCode)
	{
		double balanceAmount, glosAmount;
		ArrayList<LoanInfo> list;
		
		balanceAmount = glosAmount = 0;
		if(loanType.equalsIgnoreCase("grouploan"))
		{
            ArrayList<CommanInfo> glos = new DataBaseReportMethods().loanMasterReportMethod(branchId,dsaCode);
            for (CommanInfo ci : glos)
            {
            	glosAmount += ci.getTotalOs();
            }
            
            return glosAmount;
		}
		else
		{
			list = loanWiseBalanceReportAllOtherLoan(branchId, loanType, dsaCode);
			if(list.size() > 0)
			{
				for(LoanInfo ll : list)
				{
					balanceAmount += ll.getBalance();
				}
			}	
			return balanceAmount;
		}
	}
	
	
	
	public ArrayList<LoanInfo> loanWiseBalanceReport(Connection conn, int branchId) 
	{
		int count = 1;
		String qq = "";
		ArrayList<LoanInfo> list = new ArrayList<>();
		try(Statement st = conn.createStatement())
		{
			qq = "select * from grouploan inner join shareholder_info where shareholder_info.shareholderno=grouploan.teamLeader "
			   + "and grouploan.status = 'Approve' and grouploan.paidStatus = 'unpaid' ";
			
			qq = selectQueWithBranch(qq, branchId, "shareholder_info");

			ResultSet rr = st.executeQuery(qq);
			while(rr.next())
			{
				LoanInfo ll=new LoanInfo();
				
				ll.setLoanNo(rr.getString("grouploan.loanNo"));
				ll.setSubLoanNo(rr.getString("grouploan.sub_loan_no"));
				ll.setApplicantName(rr.getString("shareholder_info.first_applicantName"));
				ll.setShareHolderNo(rr.getString("shareholder_info.shareholderno"));
				ll.setContactNo(rr.getString("shareholder_info.mobile"));
				ll.setFatherHusbandName(rr.getString("shareholder_info.father_husband_name"));
				ll.setCenterName(db.centerNameById(rr.getString("grouploan.center"), conn));
				ll.setLoanAmount(rr.getString("grouploan.loanAmount"));
				ll.setTotalAmount(rr.getDouble("terms")*rr.getDouble("emi"));
				ll.setPaidAmount(db.loanPaidAmountByUser("grouploan", ll.getSubLoanNo(),conn));
				ll.setBalance(ll.getTotalAmount()-ll.getPaidAmount());
				ll.setEmiAmount(rr.getDouble("grouploan.emi"));
				
				double chkAmt = db.checkLoanDisburse(ll.getSubLoanNo(),conn);
				if(chkAmt > 0)
				{
					ll.setSno(count++);
					list.add(ll);
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	

	public ArrayList<LoanInfo> loanWiseBalanceReportAllOtherLoan(int branchId, String loanType) 
	{
		int count = 1;
		ArrayList<LoanInfo> list = new ArrayList<>();
		try(Connection conn = DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq = "select * from "+loanType+" inner join shareholder_info where "
					  + "shareholder_info.shareholderno = "+loanType+".shareholderno and "
					  + ""+loanType+".status='Approve' and "+loanType+".paidStatus='unpaid' ";
			
			qq = selectQueWithBranch(qq, branchId, "shareholder_info");
			System.out.println(qq);
			
			ResultSet rr = st.executeQuery(qq);
			while(rr.next())
			{
				LoanInfo ll = new LoanInfo();
				
				ll.setLoanNo(rr.getString(loanType+".loanNo"));
				ll.setApplicantName(rr.getString("shareholder_info.first_applicantName"));
				ll.setShareHolderNo(rr.getString("shareholder_info.shareholderno"));
				ll.setContactNo(rr.getString("shareholder_info.mobile"));
				ll.setFatherHusbandName(rr.getString("shareholder_info.father_husband_name"));
				ll.setLoanAmount(rr.getString(loanType + ".loanAmount"));
				ll.setTotalAmount((rr.getDouble("terms") * rr.getDouble("emi")) - rr.getInt("discount"));
				ll.setPaidAmount(db.loanPaidAmountByUser(loanType, ll.getLoanNo(), conn));
				ll.setBalance(ll.getTotalAmount() - ll.getPaidAmount());
				ll.setEmiAmount(rr.getDouble(loanType + ".emi"));
				
				double chkAmt = db.checkLoanDisburse(ll.getLoanNo(), conn);
				if(chkAmt > 0)
				{
					ll.setSno(count++);
					list.add(ll);
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	public ArrayList<UserInfo> allDueEMI(int branchId,String loantype, Connection conn) 
	{
		boolean checkGroupLoanEmi = false;
		java.util.Date endDate = new java.util.Date();
		Timestamp tt = new Timestamp(endDate.getTime());
		tt.setHours(0);
		tt.setMinutes(0);
		tt.setSeconds(0);
		tt.setNanos(0);

		ArrayList<UserInfo> list = new ArrayList<>();
		Statement st = null;
		try
		{
			st = conn.createStatement();
			ArrayList<SelectItem> myList = new ArrayList<>();
			if (loantype.equalsIgnoreCase("personalloan")) 
			{
				myList.add(new SelectItem("personalloan", "Personal Loan"));
			}
			
			if (loantype.equalsIgnoreCase("grouploan")) 
			{
				myList.add(new SelectItem("grouploan", "Group Loan"));
			}
			
			if (loantype.equalsIgnoreCase("secure_loan")) 
			{
				myList.add(new SelectItem("secure_loan", "Secure Loan"));
			}
			
			
			for(SelectItem item : myList)
			{
				String loanType = (String) item.getValue();
				String query = "";
				if(!loanType.equals("grouploan"))
				{
					query = "select * from " + loanType + " INNER JOIN shareholder_info on " 
						  + loanType + ".shareholderno = shareholder_info.shareholderno where " 
						  + loanType + ".status = 'approve' "
						  + "and " + loanType + ".paidStatus = 'unpaid' ";
				}
				else
				{
				  query = "select * from " + loanType + " INNER JOIN shareholder_info on " 
						+ loanType + ".teamLeader = shareholder_info.shareholderno where " 
						+ loanType + ".status = 'approve' "
						+ "and " + loanType + ".paidStatus = 'unpaid' ";
				}
				
				query = selectQueWithBranch(query, branchId, "shareholder_info") + "order by loanNo asc";
				
				ResultSet rr = st.executeQuery(query);
				while(rr.next())
				{
					String loanNo = "";
					if(loanType.equals("grouploan"))
					{
						loanNo = rr.getString("sub_loan_no");;
					}
					else
					{
						loanNo = rr.getString("loanNo");
					}
					checkGroupLoanEmi = db.checkGroupLoanEmiFromTable(loanNo, conn);
					double paidAmount = db.loanPaidAmountByUser(loanType, loanNo, conn);

					int terms = rr.getInt("terms");
					int frequency = rr.getInt("frequency");
					double emi = rr.getDouble("emi");
					int j = 365/frequency;
					Date installDate;
					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);
	                    }
					}
					
					for (int i = 1; i <= terms; i++) 
					{
						UserInfo ll = new UserInfo();
						
						if(paidAmount >= emi)
						{
							ll.setStatus("Paid");
							paidAmount = Double.parseDouble(dc.format(paidAmount - emi));
						}
						else
						{
							ll.setStatus("Due");
							ll.setAmount(Double.parseDouble(dc.format(emi - paidAmount)) + rr.getDouble("extra_amount"));
							paidAmount = 0;
						}
						
						java.util.Date dt = installDate;
						
						if(rr.getDate("emi_due_date") == null && checkGroupLoanEmi == false)
						{
							dt.setDate(dt.getDate() + j);
						}
						else
						{
							if(frequency == 12 && checkGroupLoanEmi == false)
                            {
                                dt.setMonth(dt.getMonth() + 1);
                            }
							else
                            {
                            	if(checkGroupLoanEmi == true) 
                            	{
                            		dt = db.groupLoanEmiDateFromTable(i, loanNo, conn);
                            	}
                            	else
                            	{
                            		dt.setDate(dt.getDate()+j);
                            	}
                            }
						}
						
						if(loanType.equals("grouploan"))
						{
							ll.setLoanNo(loanNo + " - " + rr.getString("sub_loan_no"));
						}
						else
						{
							ll.setLoanNo(loanNo);
						}
						
						ll.setId(rr.getString("id"));
						ll.setChq_ddNo(item.getLabel());
						ll.setFirst_applicantName(rr.getString("shareholder_info.first_applicantName"));
						ll.setContactNo(rr.getString("shareholder_info.mobile"));
						ll.setShareHolderNo(rr.getString("shareholder_info.shareholderno"));
						ll.setEntryDateStr(dateObj.format(dt));
						ll.setEntryDate(dateObj.parse(ll.getEntryDateStr()));

						if(ll.getAmount() > 0 && ll.getEntryDate().before(endDate))
						{
							if(ll.getStatus().equals("Due"))
							{
								list.add(ll);
							}			
						}
					}
				}
			}	
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	public void filterBucketFromList(String loanType, int branchId, Connection conn, PerformanceDataInfo pd, String dsacode, 
									 java.util.Date searchDate, Date startDate)
	{
		String dueAmtType = "both";
		ArrayList<String> list, dueDateList, dateList;
		ArrayList<InstallmentInfo> pdueList = new ArrayList<>();
		ArrayList<LoanInfo> dueLoanList;
		ArrayList<InstallmentInfo> list1, list2, list3, list3plus;
        list1 = list2 = list3 = list3plus = new ArrayList<>();
		LoanInfo lInfo = null;
		    
		list = new ArrayList<>();
		dueDateList = new ArrayList<>();
	   
	   
		list = db.loanEmiDueList(branchId, loanType, "all", dsacode, null, conn);
	    if(list.size() == 0)
	    {
		  //  FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("No Record Found !!"));
	    }
	    else
	    {
	    	int sn=1;
	    	for(int i=0;i<list.size();i++)
	    	{
	    		int count = 0;
	    		int balEmi = 0;
	    		int days = 0;
	    		
	    		double dueAmt = 0;
	    		double totalDue = 0;
	    		
	    		java.util.Date dueDate = null;
	    		
	    		boolean chk = false;
	    		Boolean checkDays = true;
	    		
	    		
	    		dueLoanList = db.loanInstallment(loanType, list.get(i), conn);
	    		for(int l = 0; l < dueLoanList.size(); l++)
	    		{
					if(!dueLoanList.get(l).getStatus().equals("Paid"))
					{
						if(searchDate.after(dueLoanList.get(l).getInstallDate()) && 
						  !dateObj.format(searchDate).equals(dueLoanList.get(l).getStrInstlmntDate()))
						{
							dueDateList.add(count + 1 + " - " + dateObj.format(dueLoanList.get(l).getInstallDate()) + "?" 
										  + list.get(i));
							count++;
							  
							if(dueAmtType.equalsIgnoreCase("onlyPrincipal"))
							{
								dueAmt += dueLoanList.get(l).getPrincipal();
							}
							else if(dueAmtType.equalsIgnoreCase("onlyInterest"))
							{
								dueAmt += dueLoanList.get(l).getInterest();
							}
							else
							{
								dueAmt += dueLoanList.get(l).getDueAmount();
							}
						  	 
							if(chk == false)
							{
								dueDate = dueLoanList.get(l).getInstallDate();
								chk = true;
							}
							  
							if(checkDays == true)
							{
								days = (int) ((searchDate.getTime() - dueLoanList.get(l).getInstallDate().getTime()) / 
											 (1000 * 60 * 60 * 24));
								checkDays=false;
							}
							  
							dueAmt = Double.parseDouble(dc.format(dueAmt));
						}
						 
						if(dueAmtType.equalsIgnoreCase("onlyPrincipal"))
						{
							totalDue += dueLoanList.get(l).getPrincipal();
						}
						else if(dueAmtType.equalsIgnoreCase("onlyInterest"))
						{
							totalDue += dueLoanList.get(l).getInterest();
						}
						else
						{
							totalDue += dueLoanList.get(l).getDueAmount();
						}
						balEmi++;
					}
				
					if(l == dueLoanList.size() -1 && count != 0)
					{
						InstallmentInfo cate = new InstallmentInfo();
						
						cate.setCount(count);
                        cate.setSno(sn++);
                        cate.setShareHolderNo(db.shareHolderNoByLoanNoLoanType(list.get(i), loanType));
                        
                        UserInfo basicDetail = db.memberBasicDetail(cate.getShareHolderNo(), conn);
                        
                        cate.setUser_no(list.get(i));
                        cate.setName(basicDetail.getName());
                        lInfo = db.loanInfoByLoanNoLoanType(list.get(i), loanType, conn);
                        
                        cate.setPaidAmount(Double.parseDouble(dc.format(dueAmt)));
                        cate.setEmiAmount(lInfo.getInstallAmount());
                        cate.setContactNo(basicDetail.getContactNo());
                        cate.setFatherHusbandName(basicDetail.getFather_husband_name());
                        cate.setAddress(basicDetail.getAddress());
                        cate.setStrPayOutDate(dateObj.format(dueDate));
                                              
                        int bid = dbr.branchIdByLoanTypeAndLoanNo(loanType, list.get(i));
                        String branchNN = db.branchNameByBranchId(bid, conn);
                        String lastPaidDate = dbr.lastPaidEmiDateByLoanNo(list.get(i));
                       
                        cate.setLastEmiPaidDate(lastPaidDate);
                        
                        cate.setBranchName(branchNN);
                        if(loanType.equalsIgnoreCase("grouploan"))
                        {
                        	 cate.setStatus(lInfo.getCenterName());
                        }
                        cate.setDays(days);
                        if (days >= 1 && days <= 30) 
                        {
                            cate.setBucket("1");
                        } 
                        else if (days >= 31 && days <= 60) 
                        {
                            cate.setBucket("2");
                        } 
                        else if (days >= 61 && days <= 90) 
                        {
                            cate.setBucket("3");
                        } 
                        else 
                        {
                            cate.setBucket("3+");
                        }
                      
                        LoanInfo ll=db.loanInfoByLoanNoLoanType(list.get(i), loanType, conn);
                        cate.setDsaName(ll.getDsaName());
                        cate.setDsaCode(ll.getDsacode());
                        cate.setLoanAmount(ll.getLoanAmtInt());
                        cate.setTerms(ll.getTerms());
                        cate.setBalTerms(balEmi);
                        cate.setTotalDueAmount(Double.parseDouble(dc.format(totalDue)));
                        
                        UserInfo g1BasicDetail=db.memberBasicDetail(ll.getFirstGuarantor(), conn); 
                        cate.setG1Name(g1BasicDetail.getName());
                        cate.setG1Address(g1BasicDetail.getAddress());
                        cate.setG1ContactNo(g1BasicDetail.getContactNo());
                      
						pdueList.add(cate);     
					}
	    		}	   
	    	}
	    } 
	    
	    int bucket1 = 0, bucket2 = 0, bucket3 = 0, bucket3plus = 0;
	    int gl1 = 0, gl2 = 0, gl3 = 0, gl3plus = 0;
	    int bl1 = 0, bl2 = 0, bl3 = 0, bl3plus = 0;
	    double grandTotalOfDueAmount = 0, grandTotalOfEmiAmount = 0, totDueEmi=0;
	    double glAmount1 = 0, glAmount2 = 0, glAmount3 = 0, glAmount3plus = 0;
	    double blAmount1 = 0, blAmount2 = 0, blAmount3 = 0, blAmount3plus = 0;
	    double bucketamount1 = 0, bucketamount2 = 0, bucketamount3 = 0, bucketamount3plus = 0;
	   
	    for(InstallmentInfo ll : pdueList)
	    {
	    	grandTotalOfDueAmount += ll.getPaidAmount();
	    	grandTotalOfEmiAmount += ll.getEmiAmount();
	    	totDueEmi += ll.getCount();
	    	
	    	if ("1".equalsIgnoreCase(ll.getBucket())) 
	    	{	
				bucket1++;
				bucketamount1 += ll.getPaidAmount();
				
				double check = checkEmiPaymentAmountByDate(ll.getUser_no(), startDate, new java.sql.Date(searchDate.getTime()));
				
				int count = (int) (check / ll.getEmiAmount());
				if (check > 0) 
				{
					if (count == 0) 
					{
						count = 1;
					}
				}
				
				if (loanType.equalsIgnoreCase("grouploan")) 
				{
					gl1 += count;
					glAmount1 += check;
				}
				else 
				{
					bl1 += count;
					blAmount1 += check;
				}
				
				list1.add(ll);
			}
	    	else if ("2".equalsIgnoreCase(ll.getBucket()))
	    	{
	    		bucket2++;
				bucketamount2 += ll.getPaidAmount();
				
				double check = checkEmiPaymentAmountByDate(ll.getUser_no(), startDate, new java.sql.Date(searchDate.getTime()));
				
				int count = (int) (check / ll.getEmiAmount());
				if (check > 0) 
				{
					if (count == 0) 
					{
						count = 1;
					}
				}
				
				if (loanType.equalsIgnoreCase("grouploan")) 
				{
					gl2 += count;		
					glAmount2 += check;
				}
				else 
				{
					bl2 += count;
					blAmount2 += check;
				}
				
				list2.add(ll);
			}
	    	else if ("3".equalsIgnoreCase(ll.getBucket())) 
	    	{
	    		bucket3++;
				bucketamount3 += ll.getPaidAmount();
				
				double check = checkEmiPaymentAmountByDate(ll.getUser_no(), startDate, new java.sql.Date(searchDate.getTime()));
				
				int count = (int) (check / ll.getEmiAmount());
				if (check > 0) 
				{
					if (count == 0) 
					{
						count = 1;
					}
				}
				
				if (loanType.equalsIgnoreCase("grouploan")) 
				{
					gl3 += count;
					glAmount3 += check;
				}
				else 
				{
					bl3 += count;
					blAmount3 += check;
				}
				
				list3.add(ll);
			}
	    	else if ("3+".equalsIgnoreCase(ll.getBucket()))
	    	{
	    		bucket3plus++;
				bucketamount3plus += ll.getPaidAmount();
				
				double check = checkEmiPaymentAmountByDate(ll.getUser_no(), startDate, new java.sql.Date(searchDate.getTime()));
				
				int count = (int) (check / ll.getEmiAmount());
				if (check > 0) 
				{
					if (count == 0) 
					{
						count = 1;
					}
				}
				
				if (loanType.equalsIgnoreCase("grouploan")) 
				{
					gl3plus += count;
					glAmount3plus += check;
				}
				else 
				{
					bl3plus += count;
					blAmount3plus += check;
				}
				
				list3plus.add(ll);
			}
	    }   
		
		if (loanType.equalsIgnoreCase("grouploan")) 
		{
			pd.setGlBucket1(bucket1);
			pd.setGlBucketAmount1(bucketamount1);
			pd.setGlBucket2(bucket2);
			pd.setGlBucketAmount2(bucketamount2);
			pd.setGlBucket3(bucket3);
			pd.setGlBucketAmount3(bucketamount3);
			pd.setGlBucket3plus(bucket3plus);
			pd.setGlBucketAmount3plus(bucketamount3plus);
			pd.setTotalGlBucket(bucket1 + bucket2 + bucket3 + bucket3plus);
			pd.setTotalGlBucketAmount(bucketamount1 + bucketamount2 + bucketamount3 + bucketamount3plus);
			
			
			pd.setGlBucketCloseMTD1(gl1);
            pd.setGlBucketCloseAmountMTD1(glAmount1);
            pd.setGlBucketCloseMTD2(gl2);
            pd.setGlBucketCloseAmountMTD2(glAmount2);
            pd.setGlBucketCloseMTD3(gl3);
            pd.setGlBucketCloseAmountMTD3(glAmount3);
            pd.setGlBucketCloseMTD3plus(gl3plus);
            pd.setGlBucketCloseAmountMTD3plus(glAmount3plus);
		}
		else if (loanType.equalsIgnoreCase("personalloan"))
		{
			pd.setBlBucket1(bucket1);
			pd.setBlBucketAmount1(bucketamount1);
			pd.setBlBucket2(bucket2);
			pd.setBlBucketAmount2(bucketamount2);
			pd.setBlBucket3(bucket3);
			pd.setBlBucketAmount3(bucketamount3);
			pd.setBlBucket3plus(bucket3plus);
			pd.setBlBucketAmount3plus(bucketamount3plus);
			pd.setTotalBlBucket(bucket1 + bucket2 + bucket3 + bucket3plus);
			pd.setTotalBlBucketAmount(bucketamount1 + bucketamount2 + bucketamount3 + bucketamount3plus);
			
			
			pd.setBlBucketCloseMTD1(bl1);
            pd.setBlBucketCloseAmountMTD1(blAmount1);
            pd.setBlBucketCloseMTD2(bl2);
            pd.setBlBucketCloseAmountMTD2(blAmount2);
            pd.setBlBucketCloseMTD3(bl3);
            pd.setBlBucketCloseAmountMTD3(blAmount3);
            pd.setBlBucketCloseMTD3plus(bl3plus);
            pd.setBlBucketCloseAmountMTD3plus(blAmount3plus);
		}
	}
	
	
	
	public double checkEmiPaymentAmountByDate(String loanNo, Date startDate, Date endDate)
	{
		String qq = "SELECT SUM(amount) AS total FROM loanpayment where loanNo = ? AND receipt_status = 'approved' AND "
				  + "chequeStatus = 'cleared' AND pay_date BETWEEN ? AND ?";
		
		try (Connection conn = DataBaseConnection.JAVAConnection()) 
		{
			try (PreparedStatement ps = conn.prepareStatement(qq))
			{
				ps.setString(1, loanNo);
				ps.setDate(2, startDate);
				ps.setDate(3, endDate);
				
				try (ResultSet rs = ps.executeQuery())
				{
					if (rs.next()) 
					{
						return rs.getDouble("total");
					}
				} 
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	
	
	public void fillEmiBuckets(Connection conn, PerformanceDataInfo pd, String loanType)
	{
	    int[] count = new int[4];
	    double[] amount = new double[4];

	    List<String> bucket1Loans = new ArrayList<>();
	    List<String> bucket2Loans = new ArrayList<>();
	    List<String> bucket3Loans = new ArrayList<>();
	    List<String> bucket3PlusLoans = new ArrayList<>();

	    Calendar cal = Calendar.getInstance();
	    cal.set(Calendar.HOUR_OF_DAY, 23);
	    cal.set(Calendar.MINUTE, 59);
	    cal.set(Calendar.SECOND, 59);
	    cal.set(Calendar.MILLISECOND, 999);
	    java.util.Date today = cal.getTime();

	    List<UserInfo> list = allDueEMI(pd.getBranchId(), loanType, conn);

	    for (UserInfo ui : list)
	    {
	        java.util.Date dueDate = ui.getEntryDate();
	        if (dueDate.after(today))
	        {
	            continue;
	        }

	        long daysDue = (today.getTime() - dueDate.getTime()) / (24L * 60 * 60 * 1000);

	        for (int i = 0; i < BUCKETS.length; i++)
	        {
	            int from = BUCKETS[i][0];
	            int to = BUCKETS[i][1];

	            boolean inBucket = (to == -1) ? daysDue > from : daysDue >= from && daysDue <= to;

	            if (inBucket)
	            {
	                count[i]++;
	                amount[i] += ui.getAmount();

	                switch (i)
	                {
	                    case 0: bucket1Loans.add(ui.getLoanNo()); break;
	                    case 1: bucket2Loans.add(ui.getLoanNo()); break;
	                    case 2: bucket3Loans.add(ui.getLoanNo()); break;
	                    case 3: bucket3PlusLoans.add(ui.getLoanNo()); break;
	                }
	                break;
	            }
	        }
	    }

	    if ("grouploan".equalsIgnoreCase(loanType))
	    {
	        pd.setGlBucket1(count[0]);
	        pd.setGlBucketAmount1((int) amount[0]);
	        pd.setGlBucket1LoanNos(bucket1Loans);

	        pd.setGlBucket2(count[1]);
	        pd.setGlBucketAmount2((int) amount[1]);
	        pd.setGlBucket2LoanNos(bucket2Loans);

	        pd.setGlBucket3(count[2]);
	        pd.setGlBucketAmount3((int) amount[2]);
	        pd.setGlBucket3LoanNos(bucket3Loans);

	        pd.setGlBucket3plus(count[3]);
	        pd.setGlBucketAmount3plus((int) amount[3]);
	        pd.setGlBucket3plusLoanNos(bucket3PlusLoans);

	        pd.setTotalGlBucket(count[0] + count[1] + count[2] + count[3]);
	        pd.setTotalGlBucketAmount((int) (amount[0] + amount[1] + amount[2] + amount[3]));
	    }
	    else if ("personalloan".equalsIgnoreCase(loanType))
	    {
	        pd.setBlBucket1(count[0]);
	        pd.setBlBucketAmount1((int) amount[0]);
	        pd.setBlBucket1LoanNos(bucket1Loans);

	        pd.setBlBucket2(count[1]);
	        pd.setBlBucketAmount2((int) amount[1]);
	        pd.setBlBucket2LoanNos(bucket2Loans);

	        pd.setBlBucket3(count[2]);
	        pd.setBlBucketAmount3((int) amount[2]);
	        pd.setBlBucket3LoanNos(bucket3Loans);

	        pd.setBlBucket3plus(count[3]);
	        pd.setBlBucketAmount3plus((int) amount[3]);
	        pd.setBlBucket3plusLoanNos(bucket3PlusLoans);

	        pd.setTotalBlBucket(count[0] + count[1] + count[2] + count[3]);
	        pd.setTotalBlBucketAmount((int) (amount[0] + amount[1] + amount[2] + amount[3]));
	    }
	}
	
	
	
	public int totalDueEmiCountAndAmountByDate(Connection conn, int branchId, String type, String loantype, int fromDays, 
											   int toDays)
	{
	    double totalAmount = 0;
	    int totalCount = 0;

	    Calendar cal = Calendar.getInstance();
	    cal.set(Calendar.HOUR_OF_DAY, 23);
	    cal.set(Calendar.MINUTE, 59);
	    cal.set(Calendar.SECOND, 59);
	    cal.set(Calendar.MILLISECOND, 999);
	    java.util.Date today = cal.getTime();

	    ArrayList<UserInfo> list = allDueEMI(branchId, loantype, conn);
	    
	    for (UserInfo ui : list)
	    {
	        java.util.Date dueDate = ui.getEntryDate();

	        if (dueDate.after(today)) 
	        {
	            continue;
	        }

	        long daysDue = (today.getTime() - dueDate.getTime()) / (24L * 60 * 60 * 1000);

	        boolean inBucket;

	        if (toDays == -1) 
	        {            
	            inBucket = daysDue > fromDays;
	        }
	        else 
	        {
	            inBucket = daysDue >= fromDays && daysDue <= toDays;
	        }

	        if (inBucket)
	        {
	            totalCount++;
	            totalAmount += ui.getAmount();
	        }
	    }

	    if ("amount".equalsIgnoreCase(type)) 
	    {
	        return (int) Math.round(totalAmount);
	    } 
	    else 
	    {
	        return totalCount;
	    }
	}
	
	
	
	public int totalDueEmiAmountByLoanNoList(Connection conn, int branchId, String loanType, List<String> loanNolist)
	{
	    if (loanNolist == null || loanNolist.isEmpty()) 
	    {
	        return 0;
	    }

	    double totalAmount = 0;
	    Set<String> loanNoSet = new HashSet<>(loanNolist);
	    
	    ArrayList<UserInfo> dueEmiList = allDueEMI(branchId, loanType, conn);

	    for (UserInfo ui : dueEmiList) 
	    {
	        if (ui.getLoanNo() != null &&
	            loanNoSet.contains(ui.getLoanNo())) 
	        {

	            totalAmount += ui.getAmount(); 
	        }
	    }
	    return (int) Math.round(totalAmount);
	}
	
	
	
	public String selectQueWithBranch(String query,int branchId,String tableName)
	{
		if(branchId == -1)
		{		
			if(query.contains("where"))
			{
				query = query + " and " + new DataBaseMethods().branchBuilder(tableName);
			}
			else
			{
				query = query + " where" + new DataBaseMethods().branchBuilder(tableName);
			}
		}
		else
		{
			if(query.contains("where"))
			{
				query = query + " and " + tableName + ".branch_id = '" + branchId + "'";
			}
			else
			{
				query = query + " where " + tableName + ".branch_id = '" + branchId + "'";
			}
		}
		return query;
	}
	
	
	
	public ArrayList<UserInfo> allDueEMIByLoanType(int branchId, Connection conn, String loantype, Date sd, Date ed) 
	{
		boolean checkGroupLoanEmi=false;
		
		java.util.Date startDate, endDate;
		startDate = new java.util.Date(sd.getTime());
		endDate = new java.util.Date(ed.getTime());
		
		Calendar cal = Calendar.getInstance();

		cal.setTime(startDate);
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);
		startDate = cal.getTime();

		cal.setTime(endDate);
		cal.set(Calendar.HOUR_OF_DAY, 23);
		cal.set(Calendar.MINUTE, 59);
		cal.set(Calendar.SECOND, 59);
		cal.set(Calendar.MILLISECOND, 999);
		endDate = cal.getTime();
		
		System.out.println("sd:" + startDate);
		System.out.println("ed:" + endDate);

		ArrayList<UserInfo> list=new ArrayList<>();
		Statement st=null;
		try
		{
			st = conn.createStatement();
			ArrayList<SelectItem> myList = new ArrayList<>();
			if (loantype.equalsIgnoreCase("personalloan")) 
			{
				myList.add(new SelectItem("personalloan","Personal Loan"));
			}
			if (loantype.equalsIgnoreCase("grouploan")) 
			{
				myList.add(new SelectItem("grouploan","Group Loan"));
			}
			
			if (loantype.equalsIgnoreCase("secure_loan")) 
			{
				myList.add(new SelectItem("secure_loan","Secure Loan"));
			}
			
			for(SelectItem item : myList)
			{
				String loanType = (String) item.getValue();
				String query = "";
				if(!loanType.equals("grouploan"))
				{
					query="select * from " + loanType + " INNER JOIN shareholder_info on "
						+ "" + loanType + ".shareholderno = shareholder_info.shareholderno "
						+ "where " + loanType + ".status = 'approve' and " + loanType + ".paidStatus = 'unpaid' ";
				}
				else
				{
					query = "select * from " + loanType + " INNER JOIN shareholder_info on "
						  + "" + loanType + ".teamLeader = shareholder_info.shareholderno where "
						  + "" + loanType + ".status = 'approve' and " + loanType + ".paidStatus = 'unpaid' ";
				}
				
				query = selectQueWithBranch(query, branchId, "shareholder_info") + "order by loanNo asc";
				ResultSet rr = st.executeQuery(query);
				while(rr.next())
				{
					String loanNo = "";
					if(loanType.equals("grouploan"))
					{
						loanNo = rr.getString("sub_loan_no");;
					}
					else
					{
						loanNo = rr.getString("loanNo");
					}
					checkGroupLoanEmi = db.checkGroupLoanEmiFromTable(loanNo, conn);
					double paidAmount = db.loanPaidAmountByUser(loanType, loanNo, conn);
					
					int terms = rr.getInt("terms");
					int frequency = rr.getInt("frequency");
					double emi = rr.getDouble("emi");
					int j = 365 / frequency;
					java.util.Date installDate;
					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);
	                    }
					}
					for (int i = 1; i <= terms; i++) 
					{
						UserInfo ll = new UserInfo();
						
						if(paidAmount >= emi)
						{
							ll.setStatus("Paid");
							paidAmount = Double.parseDouble(dc.format(paidAmount-emi));
						}
						else
						{
							ll.setStatus("Due");
							ll.setAmount(Double.parseDouble(dc.format(emi-paidAmount))+rr.getDouble("extra_amount"));
							paidAmount = 0;
						}
						
						java.util.Date dt = new java.util.Date(installDate.getTime());
						if(rr.getDate("emi_due_date")==null && checkGroupLoanEmi==false)
						{
							dt.setDate(dt.getDate()+j);
						}
						else
						{
							if(frequency==12 && checkGroupLoanEmi==false)
                            {
                                dt.setMonth(dt.getMonth()+1);
                            }
                            else
                            {
                            	 if(checkGroupLoanEmi==true)  // Condition for Manage Meeting Day Change
                            	 {
                            		 dt=db.groupLoanEmiDateFromTable(i,loanNo,conn);
                            	 }
                            	 else
                            	 {
                            		 dt.setDate(dt.getDate()+j);
                            	 }
                            }
						}
						
						if(loanType.equals("grouploan"))
						{
							ll.setLoanNo(loanNo+" - "+rr.getString("sub_loan_no"));
						}
						else
						{
							ll.setLoanNo(loanNo);
						}				
						
						ll.setId(rr.getString("id"));
						ll.setChq_ddNo(item.getLabel());
						ll.setFirst_applicantName(rr.getString("shareholder_info.first_applicantName"));
						ll.setContactNo(rr.getString("shareholder_info.mobile"));
						ll.setShareHolderNo(rr.getString("shareholder_info.shareholderno"));
						ll.setEntryDateStr(dateObj.format(dt));
						ll.setEntryDate(dateObj.parse(ll.getEntryDateStr()));
						
						java.util.Date dueEmiDate = ll.getEntryDate();

						if (ll.getAmount() > 0 && ll.getStatus().equals("Due") && !dueEmiDate.before(startDate) &&
						    !dueEmiDate.after(endDate)) 
						{
						    list.add(ll);
						}
						
						installDate = new java.util.Date(dt.getTime());
					}
				}
			}								
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}	
	
	
	
	public ArrayList<LoanInfo> loanWiseBalanceReport(int branchId, String center, String dsaCode) 
	{
		int count = 1;
		String qq = "";
		
		ArrayList<LoanInfo> list = new ArrayList<>();
		ArrayList<LoanInfo> loanEMIList = new ArrayList<>();
		
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			if(center.equalsIgnoreCase("-1"))
			{
				qq = "select * from grouploan inner join shareholder_info where "
				   + "shareholder_info.shareholderno = grouploan.teamLeader and grouploan.status = 'Approve' and "
				   + "grouploan.paidStatus = 'unpaid'";
			}
			else
			{
				qq = "select * from grouploan inner join shareholder_info where "
				   + "shareholder_info.shareholderno = grouploan.teamLeader and grouploan.status = 'Approve' and "
				   + "grouploan.center = '"+center+"' and grouploan.paidStatus = 'unpaid'";
			}
			
			if(!dsaCode.equalsIgnoreCase("all"))
			{
				qq += " and grouploan.dsacode = '"+dsaCode+"' ";
			}
			
			qq = selectQueWithBranch(qq, branchId, "shareholder_info");
			
			ResultSet rr = st.executeQuery(qq);
			while(rr.next())
			{
				LoanInfo ll=new LoanInfo();
							
				ll.setLoanNo(rr.getString("grouploan.loanNo"));
				ll.setSubLoanNo(rr.getString("grouploan.sub_loan_no"));
				ll.setApplicantName(rr.getString("shareholder_info.first_applicantName"));
				ll.setAddress(rr.getString("shareholder_info.address"));
				ll.setShareHolderNo(rr.getString("shareholder_info.shareholderno"));
				ll.setContactNo(rr.getString("shareholder_info.mobile"));
				ll.setFatherHusbandName(rr.getString("shareholder_info.father_husband_name"));
				ll.setCenterName(db.centerNameById(rr.getString("grouploan.center"), conn));
				ll.setLoanAmount(rr.getString("grouploan.loanAmount"));
				ll.setTotalAmount(rr.getDouble("terms")*rr.getDouble("emi"));
				ll.setPaidAmount(db.loanPaidAmountByUser("grouploan", ll.getSubLoanNo(),conn));
				ll.setBalance(ll.getTotalAmount()-ll.getPaidAmount());
				ll.setEmiAmount(rr.getDouble("grouploan.emi"));
				ll.setFrequency(db.frequencyLabel(rr.getString("grouploan.frequency")));
				ll.setTerms(rr.getInt("terms"));
				ll.setDisbursementDate(dbmr.loanDisburseDateByLoanNo(ll.getSubLoanNo(), conn));
				ll.setLastEmiPaidDate(db.lastEmiPaidDateByLoanNo(ll.getSubLoanNo(), conn));
				
				double remainingInterest, remainingPrincipal;
				remainingInterest = remainingPrincipal = 0;
				
				loanEMIList = db.loanInstallment2("grouploan", ll.getSubLoanNo(), conn);
			    
				
				String emiDayIfWeekly = "";

				if (rr.getInt("grouploan.frequency") == 52 && loanEMIList.size() > 0) 
				{
				    java.util.Date firstEmiDate = loanEMIList.get(0).getInstallDate();

				    if (firstEmiDate != null) 
				    {
				        SimpleDateFormat dayFormat = new SimpleDateFormat("EEEE");
				        emiDayIfWeekly = dayFormat.format(firstEmiDate);
				    }
				}

				ll.setEmiDayIfWeekly(emiDayIfWeekly);
				
				ll.setLoanCloserDate(db.loanCloserDate("grouploan", ll.getSubLoanNo(), conn));
				
				
				int paidEmi = 0;
				int pendingEmi = 0;

				for (LoanInfo li : loanEMIList)
				{
				    if ("paid".equalsIgnoreCase(li.getStatus()))
				    {
				        paidEmi++;
				    }
				    else if ("due".equalsIgnoreCase(li.getStatus()))
				    {
				        pendingEmi++;
				    }
				}
				ll.setPaidEmi(paidEmi);
				ll.setPendingEmi(pendingEmi);
				
				
				int advanceEmiPaid = 0;
				double advanceAmountPaid = 0;

				for (LoanInfo li : loanEMIList)
				{
				    if ("paid".equalsIgnoreCase(li.getStatus()))
				    {
				        java.util.Date installDate = li.getInstallDate();
				        java.util.Date paidDate = li.getPaidDate();

				        if (installDate != null && paidDate != null && installDate.after(paidDate))
				        {
				            advanceEmiPaid++;
				            advanceAmountPaid += li.getPaidAmount();
				        }
				    }
				}

				ll.setAdvanceEmiPaid(advanceEmiPaid);
				ll.setAdvanceAmountPaid(advanceAmountPaid);
				
				
				int overDueEmi = 0;
				double arrearEmiAmount = 0;

				java.util.Date today1 = new java.util.Date();

				for (LoanInfo li : loanEMIList)
				{
				    if ("due".equalsIgnoreCase(li.getStatus()) && li.getInstallDate() != null && 
				    	today1.after(li.getInstallDate()))
				    {
				        overDueEmi++;

				        arrearEmiAmount += li.getDueAmount();
				    }
				}

				ll.setOverDueEmi(overDueEmi);
				ll.setArrearEmiAmount(arrearEmiAmount);
				
				
//				
				for (LoanInfo li : loanEMIList)
				{
					if ("due".equalsIgnoreCase(li.getStatus())) 
					{
						double interest  = parseAmount(li.getInterestAmount());
				        double principal = parseAmount(li.getPrincipalAmount());
                        
				        remainingInterest  += interest;
				        remainingPrincipal += principal;
					}
				}
				
				ll.setRemainingPrincipal(dc.format(remainingPrincipal));
				ll.setInterestAmount(dc.format(remainingInterest));
				
				double chkAmt = db.checkLoanDisburse(ll.getSubLoanNo(), conn);
				if(chkAmt > 0)
				{
					ll.setSno(count++);
					list.add(ll);
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	public ArrayList<LoanInfo> loanWiseBalanceReportAllOtherLoan(int branchId, String loanType, String dsaCode) 
	{
		int count = 1;
		ArrayList<LoanInfo> list = new ArrayList<>();
		ArrayList<LoanInfo> loanEMIList = new ArrayList<>();
		
		try(Connection conn = DataBaseConnection.JAVAConnection())
		{
			Statement st = conn.createStatement();
			String qq = "select * from " + loanType + " inner join shareholder_info where "
					  + "shareholder_info.shareholderno = " + loanType + ".shareholderno and " + loanType + ".status = 'Approve' "
					  + "and " + loanType + ".paidStatus = 'unpaid' ";
			
			if(!dsaCode.equalsIgnoreCase("all"))
			{
				qq += " and " + loanType + ".dsacode = '" + dsaCode + "' ";
			}
			
			qq = selectQueWithBranch(qq, branchId, "shareholder_info");

			ResultSet rr=st.executeQuery(qq);
			while(rr.next())
			{
				LoanInfo ll = new LoanInfo();
						
				ll.setLoanNo(rr.getString(loanType + ".loanNo"));

				ll.setApplicantName(rr.getString("shareholder_info.first_applicantName"));
				ll.setShareHolderNo(rr.getString("shareholder_info.shareholderno"));
				ll.setAddress(rr.getString("shareholder_info.address"));
				ll.setContactNo(rr.getString("shareholder_info.mobile"));
				ll.setFatherHusbandName(rr.getString("shareholder_info.father_husband_name"));

				ll.setLoanAmount(rr.getString(loanType+".loanAmount"));
				ll.setTotalAmount((rr.getDouble("terms")*rr.getDouble("emi"))-rr.getInt("discount"));
				ll.setPaidAmount(db.loanPaidAmountByUser(loanType, ll.getLoanNo(),conn));
				ll.setBalance(ll.getTotalAmount()-ll.getPaidAmount());
				ll.setEmiAmount(rr.getDouble(loanType+".emi"));
				ll.setFrequency(db.frequencyLabel(rr.getString(loanType+".frequency")));
				ll.setTerms(rr.getInt(loanType+".terms"));
				ll.setLastEmiPaidDate(db.lastEmiPaidDateByLoanNo(ll.getLoanNo(), conn));
				ll.setDisbursementDate(db.loanDisburseDateByLoanNo(ll.getLoanNo(), conn));
				
				double remainingInterest, remainingPrincipal;
				remainingInterest = remainingPrincipal = 0;
				
				loanEMIList = db.loanInstallment2(loanType, ll.getLoanNo(), conn);
				
				String emiDayIfWeekly = "";
				
				if (rr.getInt(loanType + ".frequency") == 52 && loanEMIList.size() > 0) {

				    java.util.Date firstEmiDate = loanEMIList.get(0).getInstallDate();

				    if (firstEmiDate != null) {
				        SimpleDateFormat dayFormat = new SimpleDateFormat("EEEE");
				        emiDayIfWeekly = dayFormat.format(firstEmiDate);
				    }
				}

				ll.setEmiDayIfWeekly(emiDayIfWeekly);
				ll.setLoanCloserDate(db.loanCloserDate(loanType, ll.getLoanNo(), conn));
				
				int paidEmi = 0;
				int pendingEmi = 0;

				for (LoanInfo li : loanEMIList)
				{
				    if ("paid".equalsIgnoreCase(li.getStatus()))
				    {
				        paidEmi++;
				    }
				    else if ("due".equalsIgnoreCase(li.getStatus()))
				    {
				        pendingEmi++;
				    }
				}
				
				ll.setPaidEmi(paidEmi);
				ll.setPendingEmi(pendingEmi);
				
				
//				
				int advanceEmiPaid = 0;
				double advanceAmountPaid = 0;

				for (LoanInfo li : loanEMIList)
				{
				    if ("paid".equalsIgnoreCase(li.getStatus()))
				    {
				        java.util.Date installDate = li.getInstallDate();
				        java.util.Date paidDate = li.getPaidDate();

				        if (installDate != null
				                && paidDate != null
				                && installDate.after(paidDate))
				        {
				            advanceEmiPaid++;
				            advanceAmountPaid += li.getPaidAmount();
				        }
				    }
				}

				ll.setAdvanceEmiPaid(advanceEmiPaid);
				ll.setAdvanceAmountPaid(advanceAmountPaid);
				
				
				int overDueEmi = 0;
				double arrearEmiAmount = 0;

				java.util.Date today1 = new java.util.Date();

				for (LoanInfo li : loanEMIList)
				{
				    if ("due".equalsIgnoreCase(li.getStatus()) && li.getInstallDate() != null && 
				    	today1.after(li.getInstallDate()))
				    {
				        overDueEmi++;

				        arrearEmiAmount += li.getDueAmount();
				    }
				}

				ll.setOverDueEmi(overDueEmi);
				ll.setArrearEmiAmount(arrearEmiAmount);
				
				
				
				for (LoanInfo li : loanEMIList)
				{
					if ("due".equalsIgnoreCase(li.getStatus())) 
					{
						double interest  = parseAmount(li.getInterestAmount());
				        double principal = parseAmount(li.getPrincipalAmount());

				        remainingInterest  += interest;
				        remainingPrincipal += principal;
					}
				}
				
				ll.setRemainingPrincipal(dc.format(remainingPrincipal));
				ll.setInterestAmount(dc.format(remainingInterest));
				
				double chkAmt = db.checkLoanDisburse(ll.getLoanNo(), conn);
				if(chkAmt > 0)
				{
					ll.setSno(count++);
					list.add(ll);
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	public String employeeNameBydsaCode(Connection conn, String dsaCode)
	{
	    String qq = "SELECT name FROM employee WHERE dsacode = ?";

	    try (PreparedStatement ps = conn.prepareStatement(qq)) 
	    {
	        ps.setString(1, dsaCode);

	        try (ResultSet rs = ps.executeQuery()) 
	        {
	            if (rs.next()) 
	            {
	                return rs.getString("name");
	            }
	        }

	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }
	    return "";
	}
	
	
	
	public String branchNameById(Connection conn, String id)
	{
	    String qq = "SELECT branch_name FROM branch_allocate_table WHERE id = ?";

	    try (PreparedStatement ps = conn.prepareStatement(qq)) 
	    {
	        ps.setString(1, id);

	        try (ResultSet rs = ps.executeQuery()) 
	        {
	            if (rs.next()) 
	            {
	                return rs.getString("name");
	            }
	        }

	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }
	    return "";
	}
	
	
	
	public LoanInfo getLoanCloseResult(Connection conn, Date startDate, Date endDate, List<String> loanNos, int branchId) 
									   throws SQLException
	{
	    LoanInfo result = new LoanInfo();

	    if (loanNos == null || loanNos.isEmpty())
	    {
	        return result;
	    }

	    StringBuilder sql = new StringBuilder("SELECT COUNT(DISTINCT loan_no) AS cnt, COALESCE(SUM(amount),0) AS amt FROM "
	    									+ "add_loan_closer WHERE paid_date BETWEEN ? AND ? AND branch_id = ? AND "
	    									+ "loan_no IN (");

	    for (int i = 0; i < loanNos.size(); i++)
	    {
	        sql.append("?");
	        if (i < loanNos.size() - 1) sql.append(",");
	    }
	    
	    sql.append(")");

	    try (PreparedStatement ps = conn.prepareStatement(sql.toString()))
	    {
	        ps.setDate(1, startDate);
	        ps.setDate(2, endDate);
	        ps.setInt(3, branchId);

	        int idx = 4; 
	        for (String loanNo : loanNos)
	        {
	            ps.setString(idx++, loanNo);
	        }

	        try (ResultSet rs = ps.executeQuery())
	        {
	            if (rs.next())
	            {
	                result.setSno(rs.getInt("cnt"));     
	                result.setAmount(rs.getDouble("amt")); 
	            }
	        }
	    }
	    return result;
	}
	
	
	
	private double parseAmount(String value)
	{
	    if (value == null || value.trim().isEmpty()) return 0.0;

	    try
	    {
	        return Double.parseDouble(value);
	    }
	    catch (NumberFormatException e)
	    {
	        return 0.0;
	    }
	}

}