package main_package;

import java.io.Serializable;
import java.sql.Connection;
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.SimpleDateFormat;
import java.time.LocalDate;
import java.time.Period;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import javax.faces.model.SelectItem;

import other_package.DealerInfo;
import report_package.DataBaseReportMethods;

public class DataBaseMethodsRaviK implements Serializable
{
	private static final long serialVersionUID = 1L;
	
	String startSessionDateStr = "2017-04-01";
	DecimalFormat dc = new DecimalFormat("##.##");
	SimpleDateFormat dateObj = new SimpleDateFormat("dd/MM/yyyy");
	DataBaseMethods db = new DataBaseMethods();
	DataBaseReportMethods dbrm = new DataBaseReportMethods();
	SimpleDateFormat sdfForDb = new SimpleDateFormat("yyyy-MM-dd");
	
	
	
	public String selectLoanPreLoanNo(Connection conn, String disbloanNo, String loanType, String memeberNo) 
	{
		String loanNo = "";

		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()) 
					{
						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);
							return loanNo;
						}
					}
				}
			}
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		}

		System.out.println("NO OLD LOAN FOUND FOR PRECLOSURE");
		return "";
	}
	
	
	
	public String getEmployeeDetailByEmpName(Connection conn, String selectedName) 
	{
	    String detail = "";

	    try 
	    {
	        String query = "select department, city, doj "
	                     + "from employee "
	                     + "where uname=? "
	                     + "limit 1";

	        try(PreparedStatement ps = conn.prepareStatement(query)) 
	        {
	            ps.setString(1, selectedName);

	            try(ResultSet rs = ps.executeQuery()) 
	            {
	                if(rs.next()) 
	                {
	                    String department = rs.getString("department");
	                    String city = rs.getString("city");
	                    String doj = rs.getString("doj");

	                    detail = department + "#" + city + "#" + doj;
	                }
	            }
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }

	    return detail;
	}
	
	
	
	public double monthlyDepositMaturityBetweenDate(int branchId,Date startDate, Date endDate, String payMode) 
	{
		String dt=sdfForDb.format(startDate);
		String et=sdfForDb.format(endDate); 
		
		String qq="";
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			
			if(payMode.equalsIgnoreCase("cash"))
			{
				qq="select sum(maturity_value) from returned_amount_info where date>='"+dt+"' and date<='"+et+"' and "
						+ "chequeStatus='cleared' and paymentMode='cash'  ";
					
			}
			else if(payMode.equalsIgnoreCase("bank"))
			{
				qq="select sum(maturity_value) from returned_amount_info where date>='"+dt+"' and date<='"+et+"' and "
						+ "chequeStatus='cleared' and paymentMode not in ('cash','Transfer')  ";
			}
			else
			{
				qq="select sum(maturity_value) from returned_amount_info where date>='"+dt+"' and date<='"+et+"' and chequeStatus='cleared'   ";
			}
			
		//	//// // //System.out.println(qq);
			ResultSet rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("sum(maturity_value)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	} double monthlyDisburseLoanBetweenDate(int branchId,Date startDate, Date endDate, String payMode) 
	{
		String dt=sdfForDb.format(startDate);
		String et=sdfForDb.format(endDate);
		String qq="";
		ArrayList<UserInfo> list=new ArrayList<>();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			if(payMode.equals("cash"))
			{
				qq="select sum(amount) from debit_loan where date>='"+dt+"' and date<='"+et+"' and status='cleared' and paymentMode='cash' ";
			}
			else if(payMode.equalsIgnoreCase("bank"))
			{
				qq="select sum(amount) from debit_loan where date>='"+dt+"' and date<='"+et+"' and status='cleared' and paymentMode not in ('cash','Transfer') ";
			}
			else
			{
				qq="select sum(amount) from debit_loan where date>='"+dt+"' and date<='"+et+"' and status='cleared' ";
			}
		//	// // //System.out.println(qq);
			ResultSet rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("sum(amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}
	
	public double monthlyDepositCollectionBetweenDate(int branchId, Date startDate, Date endDate, Connection conn) 
	{
		String dt=sdfForDb.format(startDate); 
		String et=sdfForDb.format(endDate); 
		
		String qq=""; 
		try 
		{
			Statement st=conn.createStatement();
			qq="select sum(amount) from payment_receipt where date>='"+dt+"' and date<='"+et+"' and receipt_status='approved' and chequeStatus='cleared' ";
			ResultSet rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getDouble("sum(amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}	
	public double monthlyEmiCollectionBetweenDate(int branchId,Date startDate, Date endDate, String payMode, String amtType, Connection conn) 
	{
		System.out.println("monthStrt date"+startDate);

		double amt=0;
		String dt=sdfForDb.format(startDate);
		String et=sdfForDb.format(endDate);
		String qq="";
		ArrayList<UserInfo> list=new ArrayList<>();
		try
		{
			Statement st=conn.createStatement();
			if(payMode.equals("cash"))
			{   
				qq="select sum(amount), sum(interestAmount) from loanpayment where pay_date>='"+dt+"' and pay_date<='"+et+"' and receipt_status='approved' and chequeStatus='cleared' and pay_mode='cash' and transfer_to_bank='N/A' ";
			}
			else if(payMode.equals("both"))
			{
				qq="select sum(amount), sum(interestAmount) from loanpayment where pay_date>='"+dt+"' and pay_date<='"+et+"' and receipt_status='approved' and chequeStatus='cleared' ";
			}
			else
			{
				qq="select sum(amount), sum(interestAmount) from loanpayment where pay_date>='"+dt+"' and pay_date<='"+et+"' and receipt_status='approved' and chequeStatus='cleared' and pay_mode not in ('cash','Transfer') ";
			}
			System.out.println("noob:"+selectQueWithBranch(qq,branchId));
			ResultSet rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{   
				if(amtType.equalsIgnoreCase("Principal"))
				{
					amt=rr.getDouble("sum(amount)")-rr.getDouble("sum(interestAmount)");
				}
				else if(amtType.equalsIgnoreCase("Interest"))
				{
					amt=rr.getDouble("sum(interestAmount)");
				}
				else
				{   
					amt=rr.getDouble("sum(amount)");
					System.out.println(amt);
				}
				
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return amt;
	}
	
	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+"'";
			}
		}
		
		//System.out.println(query);
		return query;
	}
	
	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+"'";
			}
		}
		
		////System.out.println(query);
		return query;
	}
	
	//method for autocomplete 
			public java.util.List<String> userList(String name, int branchId) {
			    ArrayList<String> list = new ArrayList<>();

			    try (Connection conn = DataBaseConnection.JAVAConnection()) {

			        Statement st = conn.createStatement();

			        String query =  "SELECT uname, name " +
					                "FROM employee " +
					                "WHERE status='active' " +
					                "AND branch_id='" + branchId + "' " +
					                "AND (uname LIKE '%" + name + "%' " +
					                "OR name LIKE '%" + name + "%') " +
					                "ORDER BY uname";

			        ResultSet rs = st.executeQuery(query);

			        while (rs.next()) {
			            list.add(rs.getString("uname") + " / " + rs.getString("name"));
			        }

			        rs.close();
			        st.close();

			    } catch (Exception e) {
			        e.printStackTrace();
			    }

			    return list;
			}
			
			public java.util.List<String> dsaList(String dName, int branchId) {

			    ArrayList<String> list = new ArrayList<>();

			    try (Connection conn = DataBaseConnection.JAVAConnection()) {

			        Statement st = conn.createStatement();

			        String query = "SELECT code, name FROM dsa " +
					               "WHERE status='active' " +
					               "AND branch_id='" + branchId + "' " +
					               "AND (code LIKE '%" + dName + "%' " +
					               "OR name LIKE '%" + dName + "%') " +
					               "ORDER BY code";

			        ResultSet rs = st.executeQuery(query);

			        while (rs.next()) {
			            list.add(rs.getString("code") + " / " + rs.getString("name"));
			        }

			        rs.close();
			        st.close();

			    } catch (Exception e) {
			        e.printStackTrace();
			    }

			    return list;
			}
	
	public String getDsaNameByCode(String dsaCode, int branchId)
	{
	    String dsaName = "";

	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();

	        String query = "SELECT name " +
	                       "FROM dsa " +
	                       "WHERE code = ? " +
	                       "AND branch_id = ? " +
	                       "AND status='active'";

	        ps = conn.prepareStatement(query);

	        ps.setString(1, dsaCode);
	        ps.setInt(2, branchId);

	        rs = ps.executeQuery();

	        if(rs.next())
	        {
	            dsaName = rs.getString("name");
	        }
	    }
	    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 dsaName;
	}
	
	public String userIdByuserType(String userType, Connection conn) {

	    String sql = "SELECT userid FROM login WHERE type=?";

	    try (PreparedStatement ps = conn.prepareStatement(sql)) {

	        ps.setString(1, userType);

	        try (ResultSet rs = ps.executeQuery()) {
	            if (rs.next()) {
	                return rs.getString("userid");
	            }
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return "";
	}
	
	
	public ArrayList<SelectItem> userAppList(int branchId) {

	    ArrayList<SelectItem> list = new ArrayList<SelectItem>();

	    try (Connection conn = DataBaseConnection.JAVAConnection()) {

	        Statement st = conn.createStatement();

	        String query = "SELECT uname, name FROM employee " +
	                       "WHERE status='active' " +
	                       "AND branch_id='" + branchId + "' " +
	                       "ORDER BY uname";

	        ResultSet rs = st.executeQuery(query);

	        while (rs.next()) {

	            list.add(new SelectItem(
	                    rs.getString("uname"),
	                    rs.getString("uname") + " / " + rs.getString("name")
	            ));
	        }

	        rs.close();
	        st.close();

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	public ArrayList<dsaWiseInfo> webWiseCollection(Date startDate, Date endDate,
	        String reportType, String webType, String uid, int branchId)
	{
	    ArrayList<dsaWiseInfo> dsaCollection = new ArrayList<>();
	    System.out.println("Method is running for WEB data");

	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();
	        
	     
	        String query =
	                "SELECT lp.* " +
	                "FROM loanpayment lp " +
	                "WHERE lp.receipt_status='approved' " +
	                "AND lp.chequeStatus='cleared' " +
	                "AND lp.pay_date BETWEEN ? AND ? " +
	                "AND lp.branch_id=? " ;
	        
	                if (!"all".equalsIgnoreCase(reportType)) {
	                    query += "AND lp.loanType=? ";
	                }
	               		                
	                if ("employee".equalsIgnoreCase(webType) && "all".equalsIgnoreCase(uid)) {

	                    query += "AND EXISTS ( " +
	                             "SELECT 1 FROM login l " +
	                             "WHERE l.userid = lp.user_id " +
	                             "AND l.type = 'employee'  )";
	                }
	                else if (!"all".equalsIgnoreCase(uid)) {

	                    query += "AND lp.user_id=? ";
	                }
	                
	                query += "AND NOT EXISTS ( " +
	                "   SELECT 1 " +
	                "   FROM loan_payment_from_app app " +
	                "   WHERE app.loan_no = lp.loanNo " ;
	                query += "   AND app.employee = lp.user_id ";
	                		                
	                query +=  "   AND app.pay_date = lp.pay_date " +
	                "   AND app.loan_type = lp.loanType " +
	                "   AND app.branch_id = lp.branch_id " +
	                ") " +
	                "ORDER BY lp.pay_date ASC";

	        System.out.println("Query before inputs : " + query);

	        ps = conn.prepareStatement(query);
	        int index=1;
	        ps.setDate(index++, new java.sql.Date(startDate.getTime()));
	        ps.setDate(index++, new java.sql.Date(endDate.getTime()));
	        ps.setInt(index++, branchId);
	        if (!"all".equalsIgnoreCase(reportType)) {
	            ps.setString(index++, reportType);
	        }
	        if (!"all".equalsIgnoreCase(uid)) {
	            ps.setString(index++, uid);
	        }
	    		        
	        System.out.println("UID = " + uid);
	        rs = ps.executeQuery();

	        int sno = 1;
	        int count=1;

	        while (rs.next())
	        {	 count++;

	            dsaWiseInfo info = new dsaWiseInfo();
	            info.setSno(sno++);
	            info.setLoanNo(rs.getString("loanNo"));
	            info.setDsaName(getEmployeeNameByUname(rs.getString("user_id"), conn));
	            info.setLoanType(rs.getString("loanType"));
	           // info.setDsaName(rs.getString("user_id"));
	            info.setAmount(rs.getDouble("amount"));
	            info.setPaydate(rs.getDate("pay_date"));
	            dsaCollection.add(info);
	            		            
	        }System.out.println("query after all the inputs:: "+query);
	    }
	    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 dsaCollection;
	}



	public ArrayList<dsaWiseInfo> appWiseCollection(Date startDate, Date endDate, String reportType, String userName, int branchId)
	{
	    ArrayList<dsaWiseInfo> dsaCollection = new ArrayList<>();
	    System.out.println("method is reuning for app data::");
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    
	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();
	     
	        String query = "SELECT * FROM loan_payment_from_app " +
		        	       "WHERE status='active' " +
		        	       "AND pay_date BETWEEN ? AND ? " +
		        	       "AND branch_id=? ";
	
	        	if (!"all".equalsIgnoreCase(reportType)) {
	        	    query += " AND loan_type=? ";
	        	}
	        	if (!"all".equalsIgnoreCase(userName)) {
	        	    query += " AND employee=? ";
	        	}
	        	query += " ORDER BY employee ";
		
	        ps = conn.prepareStatement(query);
	        int index = 1;
	        ps.setDate(index++, new java.sql.Date(startDate.getTime()));
	        ps.setDate(index++, new java.sql.Date(endDate.getTime()));
	        ps.setInt(index++, branchId);
	        if (!"all".equalsIgnoreCase(reportType)) {
	            ps.setString(index++, reportType);
	        }
	        if (!"all".equalsIgnoreCase(userName)) {
	        	 ps.setString(index++, userName);
	        }
	       
	        
	
	        rs = ps.executeQuery();
	
	        int sno = 1;
	        
	        while (rs.next())
	        {
	        	dsaWiseInfo info = new dsaWiseInfo();
	        	
	        	info.setSno(sno++);
	            info.setLoanNo(rs.getString("loan_no"));
	            info.setDsaName(rs.getString("employee"));
	            info.setAmount(rs.getDouble("amount"));
	            info.setPaydate(rs.getDate("pay_date"));
	            info.setLoanType(rs.getString("loan_type"));
	           
	            dsaCollection.add(info);
	            
	        }System.out.println("query after all the inputs:: "+query);
	    }
	    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 dsaCollection;
	}
	
	
	public String getEmployeeNameByUname(String uname, Connection conn)
	{
	    String employeeName = "";

	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try
	    {
	        String query = "SELECT name FROM employee " +
	                       "WHERE uname=? AND status='active'";

	        ps = conn.prepareStatement(query);
	        ps.setString(1, uname);

	        rs = ps.executeQuery();

	        if (rs.next())
	        {
	            employeeName = rs.getString("name");
	        }
	        else
	        {
	            employeeName = uname; // agar employee record na mile to uname hi dikha do
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if (rs != null)
	                rs.close();

	            if (ps != null)
	                ps.close();
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }

	    return employeeName;
	}
	
	
	public int entryBackDaysByUserId(String userId, Connection conn) {
		 String userid= db.userId();

		    int days = 0;

		    try {
		        PreparedStatement ps = conn.prepareStatement(
		            "SELECT entry_back_days FROM employee WHERE uname=?");
		        ps.setString(1, userId);

		        ResultSet rs = ps.executeQuery();

		        if(rs.next()) {
		            days = rs.getInt("entry_back_days");
		        }

		    } catch(Exception e) {
		        e.printStackTrace();
		    }

		    return days;
		}
	
	//method for surnath
	public ArrayList<dsaWiseInfo> dsaWiseCollection(Date startDate, Date endDate, String reportType, String dsaCode, int branchId)
	{
	    ArrayList<dsaWiseInfo> dsaCollection = new ArrayList<>();

	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();

	       
		 String query = "SELECT * FROM loan_payment_from_app " +
		                "WHERE status='active' " +
		                "AND pay_date BETWEEN ? AND ? " +
		                "AND branch_id=? " +
		                "AND loan_type=? ";

	        if (!"all".equalsIgnoreCase(dsaCode)) {
	            query += "AND employee=? ";
	        }

	        query += "ORDER BY employee";

	        System.out.println(query);

	        ps = conn.prepareStatement(query);

	        ps.setDate(1, new java.sql.Date(startDate.getTime()));
	        ps.setDate(2, new java.sql.Date(endDate.getTime()));
	        ps.setInt(3, branchId);
	        ps.setString(4, reportType);

	        if (!"all".equalsIgnoreCase(dsaCode))
	        {
	            ps.setString(5, dsaCode);
	        }

	        rs = ps.executeQuery();

	        int sno = 1;

	        while (rs.next())
	        {
	        	dsaWiseInfo info = new dsaWiseInfo();
	        	
	        	info.setSno(sno++);
	            info.setLoanNo(rs.getString("loan_no"));
	            info.setDsaName(rs.getString("employee"));
	            info.setAmount(rs.getDouble("amount"));
	            info.setPaydate(rs.getDate("pay_date"));
	            
	            System.out.println("DSA Code : " + dsaCode);
	            System.out.println("Report Type : " + reportType);
	            System.out.println("Branch : " + branchId);
	            System.out.println("Date : " + new java.sql.Date(startDate.getTime()));

	            dsaCollection.add(info);
	        }
	    }
	    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 dsaCollection;
	}
		 
		 
		 
	public String getDsaUNameById(String dsaCode, int branchId)
	{
	     String dsaName = "";

	     Connection conn = null;
	     PreparedStatement ps = null;
	     ResultSet rs = null;

	     try
	     {
	         conn = DataBaseConnection.JAVAConnection();

	         String query = "SELECT dsacode, uname FROM employee " +
	                        "WHERE dsacode	 = ? " +
	                        "AND branch_id = ?";

	         ps = conn.prepareStatement(query);

	         ps.setString(1, dsaCode);
	         ps.setInt(2, branchId);

	         rs = ps.executeQuery();

	         if (rs.next())
	         {
	             dsaName = rs.getString("uname");
	         }
	     }
	     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 dsaName;
	}
	
	
	
	public ArrayList<CollectionInfo> report(String loanType, Date startDate, int searchBase) {
	      System.out.println("fetch MIS Report is calling:::+++");
	
	      ArrayList<CollectionInfo> mislist = new ArrayList<>();
	
	      Connection conn = null;
	      PreparedStatement ps = null;
	      ResultSet rs = null;
	
	      try
	      {
	          conn = DataBaseConnection.JAVAConnection();
	          String query =
	        	        "SELECT " +
	        	        "    p.loanNo, " +
	        	        "    p.shareholderno, " +
	        	        "    p.frequency, " +
	        	        "    p.emi_due_date, " +

	        	        "    CAST(p.loanAmount AS DECIMAL(15,2)) AS loan_amount, " +
	        	        "    CAST(p.emi AS DECIMAL(15,2)) AS emi, " +
	        	        "    CAST(p.terms AS DECIMAL(15,2)) AS terms, " +
	        	        "    CAST(p.interest AS DECIMAL(15,2)) AS interest_rate, " +

	        	        "    ( " +
	        	        "        CAST(p.emi AS DECIMAL(15,2)) * CAST(p.terms AS DECIMAL(15,2)) " +
	        	        "    ) AS total_payable, " +

	        	        "    CASE " +
	        	        "        WHEN CAST(p.interest AS DECIMAL(15,2)) = 0 THEN 0 " +
	        	        "        ELSE ( " +
	        	        "            (CAST(p.emi AS DECIMAL(15,2)) * CAST(p.terms AS DECIMAL(15,2))) " +
	        	        "            - CAST(p.loanAmount AS DECIMAL(15,2)) " +
	        	        "        ) " +
	        	        "    END AS total_interest, " +

	        	        "    CASE " +
	        	        "        WHEN CAST(p.interest AS DECIMAL(15,2)) = 0 THEN 0 " +
	        	        "        ELSE ( " +
	        	        "            ( " +
	        	        "                (CAST(p.emi AS DECIMAL(15,2)) * CAST(p.terms AS DECIMAL(15,2))) " +
	        	        "                - CAST(p.loanAmount AS DECIMAL(15,2)) " +
	        	        "            ) / NULLIF(CAST(p.terms AS DECIMAL(15,2)), 0) " +
	        	        "        ) " +
	        	        "    END AS interest_per_emi, " +

	        	        "    CAST(p.loanAmount AS DECIMAL(15,2)) AS total_principal, " +

	        	        "    LEAST( " +
	        	        "        COALESCE(lp.total_paid, 0), " +
	        	        "        (CAST(p.emi AS DECIMAL(15,2)) * CAST(p.terms AS DECIMAL(15,2))) " +
	        	        "    ) AS total_paid, " +

	        	        "    CASE " +
	        	        "        WHEN CAST(p.interest AS DECIMAL(15,2)) = 0 THEN 0 " +
	        	        "        ELSE COALESCE(lp.interest_paid, 0) " +
	        	        "    END AS interest_paid, " +

	        	        "    CASE " +
	        	        "        WHEN CAST(p.interest AS DECIMAL(15,2)) = 0 THEN " +
	        	        "            LEAST( " +
	        	        "                COALESCE(lp.total_paid, 0), " +
	        	        "                CAST(p.loanAmount AS DECIMAL(15,2)) " +
	        	        "            ) " +
	        	        "        ELSE " +
	        	        "            LEAST( " +
	        	        "                (COALESCE(lp.total_paid, 0) - COALESCE(lp.interest_paid, 0)), " +
	        	        "                CAST(p.loanAmount AS DECIMAL(15,2)) " +
	        	        "            ) " +
	        	        "    END AS principal_paid, " +

	        	        "    CASE " +
	        	        "        WHEN CAST(p.interest AS DECIMAL(15,2)) = 0 THEN 0 " +
	        	        "        ELSE ( " +
	        	        "            ( " +
	        	        "                (CAST(p.emi AS DECIMAL(15,2)) * CAST(p.terms AS DECIMAL(15,2))) " +
	        	        "                - CAST(p.loanAmount AS DECIMAL(15,2)) " +
	        	        "            ) - COALESCE(lp.interest_paid, 0) " +
	        	        "        ) " +
	        	        "    END AS interest_remaining, " +

	        	        "    CASE " +
	        	        "        WHEN CAST(p.interest AS DECIMAL(15,2)) = 0 THEN " +
	        	        "            GREATEST( " +
	        	        "                CAST(p.loanAmount AS DECIMAL(15,2)) " +
	        	        "                - LEAST( " +
	        	        "                    COALESCE(lp.total_paid, 0), " +
	        	        "                    CAST(p.loanAmount AS DECIMAL(15,2)) " +
	        	        "                ), " +
	        	        "                0 " +
	        	        "            ) " +
	        	        "        ELSE " +
	        	        "            GREATEST( " +
	        	        "                CAST(p.loanAmount AS DECIMAL(15,2)) " +
	        	        "                - LEAST( " +
	        	        "                    GREATEST( " +
	        	        "                        COALESCE(lp.total_paid, 0) " +
	        	        "                        - COALESCE(lp.interest_paid, 0), " +
	        	        "                        0 " +
	        	        "                    ), " +
	        	        "                    CAST(p.loanAmount AS DECIMAL(15,2)) " +
	        	        "                ), " +
	        	        "                0 " +
	        	        "            ) " +
	        	        "    END AS principal_remaining, " +

	        	        "    GREATEST( " +
	        	        "        ( " +
	        	        "            (CAST(p.emi AS DECIMAL(15,2)) * CAST(p.terms AS DECIMAL(15,2))) " +
	        	        "            - COALESCE(lp.total_paid, 0) " +
	        	        "        ), " +
	        	        "        0 " +
	        	        "    ) AS total_remaining " +

	        	        "FROM " +loanType+" p " +

	        	        "INNER JOIN ( " +
	        	        "    SELECT " +
	        	        "        loanNo, " +
	        	        "        MAX(date) AS disbursement_date " +
	        	        "    FROM debit_loan " +
	        	        "    WHERE date <= ? " +
	        	        "    GROUP BY loanNo " +
	        	        ") dl ON dl.loanNo = p.loanNo " +

	        	        "LEFT JOIN ( " +
	        	        "    SELECT " +
	        	        "        loanNo, " +
	        	        "        SUM(CAST(amount AS DECIMAL(15,2))) AS total_paid, " +
	        	        "        SUM(CAST(interestAmount AS DECIMAL(15,2))) AS interest_paid " +
	        	        "    FROM loanpayment " +
	        	        "    WHERE receipt_status = 'approved' " +
	        	        "      AND pay_date <= ? " +
	        	        "    GROUP BY loanNo " +
	        	        ") lp ON lp.loanNo = p.loanNo " +

	        	        "WHERE p.status = 'approve' " +
	        	        "  AND p.paidStatus = 'unpaid' " +

	        	        "ORDER BY p.loanNo";
	          
	          System.out.println("query"+query);
	        	
	          ps = (PreparedStatement) conn.prepareStatement(query);
	          
	          ps.setDate(1, new java.sql.Date(startDate.getTime()));
	          ps.setDate(2, new java.sql.Date(startDate.getTime()));
	
	
	          rs = (ResultSet) ps.executeQuery();
	
	          while(rs.next())
	          {
	              CollectionInfo mis = new CollectionInfo();              
	              
	              mis.setLoanNo(rs.getString("loanNo"));
	              mis.setShareholderNo(rs.getString("shareholderno"));
	
	              mis.setEmi(rs.getDouble("emi"));
	              mis.setTerms(rs.getDouble("terms"));
	              mis.setLoanAmount(rs.getDouble("loan_amount"));
	              
	            if(searchBase == 1) {
	            	  
	                  boolean dueOnSelectedDate = false;

	                  int terms = rs.getInt("terms");
	                  int frequency = rs.getInt("frequency");

	                  java.sql.Date firstDate = rs.getDate("emi_due_date");                  
	               	                  
	                  if(firstDate == null)
	                  {
	                      continue;
	                  }

	                  LocalDate firstEmiDate = firstDate.toLocalDate();

	                  LocalDate selectedDate = new java.sql.Date(startDate.getTime()).toLocalDate();

	                  for(int emiNo = 0; emiNo < terms; emiNo++)
	                  {
	                      LocalDate currentDueDate;
	                      

	                      if(frequency == 52)              // 1 Week
	                      {
	                          currentDueDate = firstEmiDate.plusWeeks(emiNo);
	                      }
	                      else if(frequency == 36)         // 10 Days
	                      {
	                          currentDueDate = firstEmiDate.plusDays(emiNo * 10L);
	                      }
	                      else if(frequency == 26)         // 2 Week
	                      {
	                          currentDueDate = firstEmiDate.plusWeeks(emiNo * 2L);
	                      }
	                      else if(frequency == 24)         // 15 Days
	                      {
	                          currentDueDate = firstEmiDate.plusDays(emiNo * 15L);
	                      }
	                      else if(frequency == 13)         // 4 Week
	                      {
	                          currentDueDate = firstEmiDate.plusWeeks(emiNo * 4L);
	                      }
	                      else if(frequency == 12)         // Monthly
	                      {
	                          currentDueDate = firstEmiDate.plusMonths(emiNo);
	                      }
	                      else
	                      {
	                          currentDueDate = firstEmiDate.plusMonths(emiNo);
	                      }
	                      System.out.println("Generated EMI Date = " + currentDueDate);
	                      System.out.println(
	                    		    "Loan=" + rs.getString("loanNo")
	                    		    + " FirstDate=" + firstEmiDate
	                    		    + " EMI Date=" + currentDueDate
	                    		    + " Selected=" + selectedDate);
	                    	                      
	                      if(currentDueDate.equals(selectedDate))
	                      {
	                          dueOnSelectedDate = true;

	                          int generatedEmiNo = emiNo + 1;

	                          PreparedStatement psEmi = conn.prepareStatement(
	                              "SELECT " +
	                              "COALESCE(SUM(CAST(amount AS DECIMAL(15,2))),0) AS emiPaid, " +
	                              "MAX(pay_date) AS payDate " +
	                              "FROM loanpayment " +
	                              "WHERE loanNo=? " +
	                              "AND emi_no=? " +
	                              "AND receipt_status='approved'"
	                          );

	                          psEmi.setString(1, rs.getString("loanNo"));
	                          psEmi.setString(2, String.valueOf(generatedEmiNo));

	                          ResultSet rsEmi = psEmi.executeQuery();

	                          if(rsEmi.next())
	                          {
	                              mis.setEmiAmountPaid(rsEmi.getDouble("emiPaid"));
	                              mis.setEmiPayDate(rsEmi.getDate("payDate"));
	                          }

	                          rsEmi.close();
	                          psEmi.close();

	                          break;
	                      }
	                      
	                      
	                     

	              }
	                  if(!dueOnSelectedDate)
	                  {
	                      continue;
	                  }
	             	         
	             
	         
	            
	              
	          }
	            
	            mis.setTotalPayableAmount(rs.getDouble("total_payable"));
	        	
	              mis.setPrincipalPaid(rs.getDouble("principal_paid"));
	              mis.setInterestPaid(rs.getDouble("interest_paid"));
	
	              mis.setTotalInterest(rs.getDouble("total_interest"));
	
	              double remainingI = rs.getDouble("interest_remaining");
	              double remainingP = rs.getDouble("principal_remaining");
	
	              // 0.02, -0.02, 0.01, -0.01 ko zero dikhao
	              
	              if (Math.abs(remainingI) <= 0.05) {
	                  remainingI = 0.0;
	              }
	
	              if (Math.abs(remainingP) <= 0.05) {
	                  remainingP = 0.0;
	              }
	
	              
	              mis.setInterestRemaining(remainingI);
	              mis.setPrincipalRemaining(remainingP);
	              mis.setTotalOutstanding(remainingI + remainingP);	
	              mislist.add(mis);
	            
	      
	      }}
	      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 mislist;
	  }
	
	
	//method by ravi kumar
	
		public ArrayList<UserInfo> ddList(int branchId, String dsaType, String date, Connection conn, String searchType, String frequencyType)
		{
		    Statement st = null, st1 = null;
		    ResultSet rr = null, rr1 = null;

		    ArrayList<UserInfo> list = new ArrayList<>();
		    int sno = 1;
		    String query = "";

		    try
		    {
		        if(frequencyType == null || frequencyType.trim().equals(""))
		        {
		            frequencyType = "all";
		        }

		        if(searchType.equalsIgnoreCase("DD") || searchType.equalsIgnoreCase("RD"))
		        {
		            if(dsaType.equals("all"))
		            {
		                query = "select * from customer_info where account_status='active'";
		            }
		            else
		            {
		                query = "select * from customer_info where dsa_code='" + dsaType + "' and account_status='active'";
		            }

		            query += " and scheme_type='" + searchType + "' ";

		            if(!frequencyType.equalsIgnoreCase("all"))
		            {
		                query += " and frequency in ('" + frequencyType + "','" + frequencyType + ".0') ";
		            }

		            st = conn.createStatement();
		            rr = st.executeQuery(db.selectQueWithBranch(query, branchId));

		            while(rr.next())
		            {
		                UserInfo cat = new UserInfo();
		                cat.setSno(sno++);
		                cat.setScheme_type(db.planNameByPlanId(rr.getString("plan_no"), conn));
		                cat.setDsaName(db.dsaNameById(rr.getString("dsa_code"), conn));
		                cat.setAccountNo(rr.getString("user_no"));
		                cat.setFirst_applicantName(db.applicantNameByShareHolderNo(rr.getString("shareHolderNo"), conn));
		                cat.setFather_husband_name(db.shareHolderDetailByColumnName(rr.getString("shareHolderNo"), "father_husband_name", conn));
		                cat.setMobile(db.shareHolderDetailByColumnName(rr.getString("shareHolderNo"), "mobile", conn));
		                cat.setOpeningAmt(rr.getInt("installment_amount"));
		                cat.setDepositAmt(db.paidAmountByUser(rr.getString("user_no"), conn));

		                list.add(cat);
		            }
		        }

		        else if(searchType.equalsIgnoreCase("DL"))
		        {
		            if(dsaType.equals("all"))
		            {
		            	
		                query = "select * from personalloan where paidStatus='unpaid' and status='Approve'";
		            }
		            else
		            {
		                query = "select * from personalloan where  paidStatus='unpaid' and status='Approve' and dsacode='" + dsaType + "'";
		            }

		            if(!frequencyType.equalsIgnoreCase("all"))
		            {
		                if(frequencyType.equals("365"))
		                {
		                    query += " and (frequency='365' or frequency='365.0') ";
		                }
		                else
		                {	
		                    query += " and frequency='" + frequencyType + "' ";
		                }
		            }

		            st1 = conn.createStatement();
		            rr1 = st1.executeQuery(db.selectQueWithBranch(query, branchId));

		            while(rr1.next())
		            {
		                UserInfo cat = new UserInfo();

		                cat.setSno(sno++);
		                cat.setScheme_type(db.planNameByPlanId(rr1.getString("plan_no"), conn));
		                cat.setDsaName(db.dsaNameById(rr1.getString("dsacode"), conn));
		                cat.setAccountNo(rr1.getString("loanNo"));
		                cat.setFirst_applicantName(db.applicantNameByShareHolderNo(rr1.getString("shareholderno"), conn));
		                cat.setOpeningAmt(rr1.getInt("emi") + rr1.getInt("extra_amount"));
		                cat.setDepositAmt(db.loanPaidAmountByUser("personalloan", rr1.getString("loanNo"), conn));
		                cat.setDueAmt(String.valueOf(Double.valueOf(rr1.getString("terms")) * Double.valueOf(rr1.getString("emi")) - Double.valueOf(cat.getDepositAmt())));
		                cat.setFather_husband_name(db.shareHolderDetailByColumnName(rr1.getString("shareholderno"), "father_husband_name", conn));
		                cat.setMobile(db.shareHolderDetailByColumnName(rr1.getString("shareholderno"), "mobile", conn));

		                list.add(cat);
		            }
		        }

		        else if(searchType.equalsIgnoreCase("PL"))
		        {
		            if(dsaType.equals("all"))
		            {
		                query = "select * from personalloan where  paidStatus='unpaid' and status='Approve'";
		            }
		            else
		            {
		                query = "select * from personalloan where  paidStatus='unpaid' and status='Approve' and dsacode='" + dsaType + "'";
		            }

		            if(!frequencyType.equalsIgnoreCase("all"))
		            {
		                query += " and frequency in ('" + frequencyType + "','" + frequencyType + ".0') ";
		            }

		            st1 = conn.createStatement();
		            rr1 = st1.executeQuery(db.selectQueWithBranch(query, branchId));

		            while(rr1.next())
		            {
		                UserInfo cat = new UserInfo();

		                cat.setSno(sno++);
		                cat.setScheme_type(db.planNameByPlanId(rr1.getString("plan_no"), conn));
		                cat.setDsaName(db.dsaNameById(rr1.getString("dsacode"), conn));
		                cat.setAccountNo(rr1.getString("loanNo"));
		                cat.setFirst_applicantName(db.applicantNameByShareHolderNo(rr1.getString("shareholderno"), conn));
		                cat.setOpeningAmt(rr1.getInt("emi") + rr1.getInt("extra_amount"));
		                cat.setDepositAmt(db.loanPaidAmountByUser("personalloan", rr1.getString("loanNo"), conn));
		                cat.setDueAmt(String.valueOf(Double.valueOf(rr1.getString("terms")) * Double.valueOf(rr1.getString("emi")) - Double.valueOf(cat.getDepositAmt())));
		                cat.setFather_husband_name(db.shareHolderDetailByColumnName(rr1.getString("shareholderno"), "father_husband_name", conn));
		                cat.setMobile(db.shareHolderDetailByColumnName(rr1.getString("shareholderno"), "mobile", conn));

		                list.add(cat);
		            }
		        }

		        else if(searchType.equalsIgnoreCase("GL"))
		        {
		            if(dsaType.equals("all"))
		            {
		                query = "select * from grouploan where  paidStatus='unpaid' and status='Approve'";
		            }
		            else
		            {
		                query = "select * from grouploan where  paidStatus='unpaid' and status='Approve' and dsacode='" + dsaType + "'";
		            }

		            if(!frequencyType.equalsIgnoreCase("all"))
		            {
		                query += " and frequency in ('" + frequencyType + "','" + frequencyType + ".0') ";
		            }

		            st1 = conn.createStatement();
		            rr1 = st1.executeQuery(db.selectQueWithBranch(query, branchId));

		            while(rr1.next())
		            {
		                UserInfo cat = new UserInfo();

		                cat.setSno(sno++);
		                cat.setScheme_type(db.planNameByPlanId(rr1.getString("plan_no"), conn));
		                cat.setDsaName(db.dsaNameById(rr1.getString("dsacode"), conn));
		                cat.setAccountNo(rr1.getString("sub_loan_no"));
		                cat.setFirst_applicantName(db.applicantNameByShareHolderNo(rr1.getString("teamLeader"), conn));
		                cat.setOpeningAmt(rr1.getInt("emi") + rr1.getInt("extra_amount"));
		                cat.setDepositAmt(db.loanPaidAmountByUser("grouploan", rr1.getString("sub_loan_no"), conn));
		                cat.setDueAmt(String.valueOf(Double.valueOf(rr1.getString("terms")) * Double.valueOf(rr1.getString("emi")) - Double.valueOf(cat.getDepositAmt())));
		                cat.setFather_husband_name(db.shareHolderDetailByColumnName(rr1.getString("teamLeader"), "father_husband_name", conn));
		                cat.setMobile(db.shareHolderDetailByColumnName(rr1.getString("teamLeader"), "mobile", conn));

		                list.add(cat);
		            }
		        }
		    }
		    catch(Exception e)
		    {
		        e.printStackTrace();
		        db.fileCreation(e.getMessage());
		    }
		    finally
		    {
		        try
		        {
		            if(st != null)
		            {
		                st.close();
		            }
		            if(rr != null)
		            {
		                rr.close();
		            }
		            if(st1 != null)
		            {
		                st1.close();
		            }
		            if(rr1 != null)
		            {
		                rr1.close();
		            }
		        }
		        catch(Exception e2)
		        {
		            e2.printStackTrace();
		        }
		    }

		    return list;
		}
	
	
	public int updateDisbursalDateByLoanNo(String loanNo, Date date, String umrnNo,Connection conn)
	{
		System.out.println("update disburse date by loan no. is running");

		int i = 0;

		PreparedStatement ps = null;
		PreparedStatement ps2 = null;

		java.sql.Date approvalDate = null;

		try
		{
			java.sql.Date requestDate = new java.sql.Date(date.getTime());

		
		// approval date fetch from all loan tables
		   String approvalQuery =   "SELECT s.approvalDate " +
									"FROM secure_loan s " +
									"WHERE s.loanNo=? " +
				
									"UNION ALL " +
				
									"SELECT p.approvalDate " +
									"FROM personalloan p " +
									"WHERE p.loanNo=? " +
				
									"UNION ALL " +
				
									"SELECT g.approvalDate " +
									"FROM grouploan g " +
									"WHERE g.loanNo=?";

			ps2 = conn.prepareStatement(approvalQuery);

			ps2.setString(1, loanNo);
			ps2.setString(2, loanNo);
			ps2.setString(3, loanNo);

			java.sql.ResultSet rs = ps2.executeQuery();

			if(rs.next())
			{
				approvalDate = rs.getDate("approvalDate");
			}

			System.out.println("Approval Date : " + approvalDate);
			 
			if(approvalDate != null && requestDate.before(approvalDate))
			{
				System.out.println("Disbursal date cannot be before approval date");
				FacesContext.getCurrentInstance().addMessage(null,
						new FacesMessage(FacesMessage.SEVERITY_ERROR,"Disbursal date cannot be before approval date",""));
				return 0;
			}
		//  update query
			String updateQuery = "UPDATE debit_loan SET date=?, umrn_no=? WHERE loanNo=?";

			ps = conn.prepareStatement(updateQuery);

			ps.setDate(1, requestDate);
			ps.setString(2, umrnNo);
			ps.setString(3, loanNo);

			i = ps.executeUpdate();

			System.out.println("Loan No : " + loanNo);
			System.out.println("Updated Rows : " + i);

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(ps != null)
				{
					ps.close();
				}

				if(ps2 != null)
				{
					ps2.close();
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}

		return i;
	}
	
	//Umrn List fetched
	public ArrayList<LoanInfo> fetchLoanUmrnData(int branchId, String loanType, String shareHolderNo)
	{
	    ArrayList<LoanInfo> umrnlist = new ArrayList<>();

	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();

	        String query = "SELECT loanNo, shareholderno, umrn_no, loanType, branch_id, status, date "
	                     + "FROM debit_loan WHERE status = ? "
	                     + "AND branch_id = ? AND loanType = ? AND shareholderno=?";

	        ps = conn.prepareStatement(query);

	        ps.setString(1, "cleared");
	        ps.setInt(2, branchId);
	        ps.setString(3, loanType);
	        ps.setString(4, shareHolderNo);

	        rs = ps.executeQuery();

	        int i = 1;

	        while (rs.next())
	        {
	            LoanInfo ll = new LoanInfo();

	            ll.setSno(i++);
	            ll.setLoanNo(rs.getString("loanNo"));
	            ll.setUmrnNo(rs.getString("umrn_no"));
	            ll.setShareHolderNo(rs.getString("shareholderno"));

	            // Optional fields if needed
	            // ll.setLoanType(rs.getString("loanType"));
	            // ll.setBranchId(rs.getInt("branch_id"));
	            // ll.setStatus(rs.getString("status"));
	            // ll.setDate(rs.getDate("date"));

	            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 ArrayList<LoanInfo> fetchLoanUmrnbyShare(int branchId, String shareHolderNo)
	{
	    ArrayList<LoanInfo> umrnlist = new ArrayList<>();

	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();

	        String query = "SELECT loanNo, shareholderno, umrn_no, branch_id, status, date "
	                     + "FROM debit_loan WHERE status = ? "
	                     + "AND branch_id = ? AND shareholderno=?" 
	                     + "ORDER BY loanNo";

	        ps = conn.prepareStatement(query);

	        ps.setString(1, "cleared");
	        ps.setInt(2, branchId);
	        ps.setString(3, shareHolderNo);

	        rs = ps.executeQuery();

	        int i = 1;
	        String ln = "";

	        while (rs.next())
	        {
	            LoanInfo ll = new LoanInfo();

	            ll.setSno(i++);
	            ll.setLoanNo(rs.getString("loanNo"));
	            ll.setUmrnNo(rs.getString("umrn_no"));
	            ll.setShareHolderNo(rs.getString("shareholderno"));
	            
	            
	           // umrnlist.add(ll);
	            if (!ln.equalsIgnoreCase(ll.getLoanNo()))
	            {
	                umrnlist.add(ll);
	            }

	            ln = ll.getLoanNo();

	        }
	    }
	    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)
	{
	    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.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 = ? " +
	                       "AND d.branch_id = ? " +
	                       "ORDER BY d.loanNo";

	        ps = conn.prepareStatement(query);

	        ps.setString(1, "cleared");
	        ps.setInt(2, branchId);

	        rs = ps.executeQuery();

	        int i = 1;
	        LoanInfo lastLoan = null;

	        while (rs.next())
	        {
	            String currentLoanNo = rs.getString("loanNo");
	            double currentAmount = rs.getDouble("amount");

	            // Same loanNo mila to amount add karo
	            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);

	                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> 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.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 = ? " +
	                       "AND d.branch_id = ? " +
	                       "ORDER BY d.loanNo";

	        ps = conn.prepareStatement(query);

	        ps.setString(1, "cleared");
	        ps.setInt(2, branchId);

	        rs = ps.executeQuery();

	        int i = 1;
	        LoanInfo lastLoan = null;

	        while (rs.next())
	        {
	            String currentLoanNo = rs.getString("loanNo");
	            double currentAmount = rs.getDouble("amount");

	            // Same loanNo mila to amount add karo
	            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()) 
	                {
	                	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 boolean isShareHolderEligibleForLoan(String shareHolderNo, int branchId) {

	    String query = "SELECT dob FROM shareholder_info " +
	    			   "WHERE shareholderno = ? AND branch_id = ?";
	       

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query)) {

	        ps.setString(1, shareHolderNo);
	        ps.setInt(2, branchId);

	        try (ResultSet rs = ps.executeQuery()) {

	            if (!rs.next()) {
	                System.err.println("Shareholder not found: " + shareHolderNo);
	                return false;
	            }

	            java.sql.Date dobFromDb = rs.getDate("dob");

	            if (dobFromDb == null) {
	                System.err.println("Shareholder DOB is missing");
	                return false;
	            }

	            LocalDate dob = dobFromDb.toLocalDate();
	            LocalDate today = LocalDate.now();

	            if (dob.isAfter(today)) {
	                System.err.println("Invalid DOB: future date");
	                return false;
	            }

	            int age = Period.between(dob, today).getYears();
	            int ageLimit = memberAgelimitByBranchid(branchId, conn);

	            return age < ageLimit;
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	        return false;
	    }
	}
	
	
	
	
	
	
	
	public ArrayList<SelectItem> memberList(int branchId, Connection conn)
	{
	    System.out.println("member list is hitting+++++++");

	    ArrayList<SelectItem> list = new ArrayList<>();
	    PreparedStatement ps = null;
	    ResultSet rr = null;

	    try
	    {
	        String query = "SELECT shareholderno, first_applicantName, "
	                     + "father_husband_name, mobile "
	                     + "FROM shareholder_info ";

	        ps = conn.prepareStatement(query);

	        rr = ps.executeQuery();

	        while(rr.next())
	        {
	            SelectItem ss = new SelectItem();

	            ss.setLabel(
	                    rr.getString("shareholderno") + " - "
	                  + rr.getString("first_applicantName") + " - "
	                  + rr.getString("father_husband_name") + " - "
	                  + rr.getString("mobile")
	            );

	            ss.setValue(rr.getString("shareholderno"));

	            list.add(ss);
	        }

	        System.out.println("Member List Size : " + list.size());
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if(rr != null)
	                rr.close();

	            if(ps != null)
	                ps.close();
	        }
	        catch(Exception e)
	        {
	            e.printStackTrace();
	        }
	    }

	    return list;
	}
	
	
	
	//function for delights 
	
	public DealerInfo getDeviceDetailsByloanNo(String loanNo, Connection conn) {

	    String sql = "SELECT sl.mob_imei_no_1, sl.mob_imei_no_2, sl.mob_product ," +
	    			 " p.brand, p.model " +
	    			 " FROM secure_loan sl " +
	    			 " JOIN products p ON p.id = sl.mob_product " +
	    			 " where sl.loanNo=? ";
	    

	    DealerInfo info = null;
	    try (PreparedStatement ps = conn.prepareStatement(sql)) {
	        ps.setString(1, loanNo);
	        ResultSet rs = ps.executeQuery();

	        if (rs.next()) {
	            info = new DealerInfo();

	            String modal = rs.getString("brand") + " - " + rs.getString("model");
	            
	            info.setImeiNo1(rs.getString("mob_imei_no_1"));
	            info.setImeiNo2(rs.getString("mob_imei_no_2"));
	            info.setModalName(modal);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return info;
	}
	
	
	//for getting bounce charge from table
	public double getbounce(Connection conn) {
		double total = 0.0;
	    String sql = "SELECT bounce_charge FROM cheque_bounce ";
	    
	    try (PreparedStatement ps = conn.prepareStatement(sql)) {
	        ResultSet rs = ps.executeQuery();
	        
	        if(rs.next())
			{
				total=rs.getDouble("bounce_charge");
			}
	        
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return total;
	}
	
	
	public ArrayList<LoanInfo> paidChequeBounceCharges(String loanNo, String itemName, Connection conn) {

	    ArrayList<LoanInfo> list = new ArrayList<LoanInfo>();
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    String sql = "SELECT loan_no,income_date,amount,voucher_no,status,item FROM income_table "
	               + "WHERE loan_no = ?  AND  item LIKE ? "
	               + "AND status = 'active'";

	    try {
	        ps = conn.prepareStatement(sql);
	        ps.setString(1, loanNo);
	        ps.setString(2, "%" + itemName + "%");
	        rs = ps.executeQuery();

	        while (rs.next()) {

	            System.out.println("Record Found : " + rs.getString("loan_no"));
	            System.out.println("Status : " + rs.getString("status"));

	            LoanInfo info = new LoanInfo();

	            info.setLoanNo(rs.getString("loan_no"));
	            Date incDate = rs.getDate("income_date");
	            if (incDate != null) {
	                String formattedDate = new SimpleDateFormat("dd/MM/yyyy").format(incDate);
	                info.setIncomeDate(formattedDate);
	            }
                info.setAmount(rs.getDouble("amount"));
                info.setVoucherNo(rs.getString("voucher_no"));
               

	            list.add(info);
	        }

	        System.out.println("Total Records : " + list.size());

	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        try {
	            if (rs != null)
	                rs.close();

	            if (ps != null)
	                ps.close();

	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	    }

	    return list;
	}
	
	
	public String getRemark(String loanNo, int emino, Connection conn) {
		String remark = "";
	    String sql = "SELECT loanNo, remark FROM loanpayment WHERE loanNo = ? "
	    		   + " AND emi_no=? ";
	    
	    try (PreparedStatement ps = conn.prepareStatement(sql)) {
	    	 ps.setString(1, loanNo);
	    	 ps.setInt(2, emino);
	        ResultSet rs = ps.executeQuery();
	        
	        if(rs.next())
			{
				remark =rs.getString("remark");
			}
	        
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return remark;
	}
	
	public double getPaidBounce(String loanNo, Date recieptDate, Connection conn) {
		double amount= 0.0;
	    String sql = "SELECT income_date,amount,chq_dd_date, loan_no FROM income_table "
	    		   + " WHERE chequeStatus='cleared' AND item='Receive Cheque Bounce Charge' "
	    		   + " AND loan_no=? "
	    		   + " AND income_date=? ";
	    
	    try (PreparedStatement ps = conn.prepareStatement(sql)) {
	    	 ps.setString(1, loanNo);
	         ps.setDate(2, new java.sql.Date(recieptDate.getTime()));
	        ResultSet rs = ps.executeQuery();
	        
	        if(rs.next())
			{
				amount+=rs.getDouble("amount");
			}
	        
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return amount;
	}
	
	
	
	//cheque bounce charges mehthod
	
	public double chequeBouncechargesforDueEmi(String loanNo, String loanType, Connection conn, ArrayList<LoanInfo> loanEMIList){
		System.out.println("method is running");
	
	    double total = 0.0;
	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();
	        int j = 0;
	        double chargeB = getbounce(conn);

	        for(LoanInfo ll : loanEMIList)
	        {
	            if(ll.getStatus().equalsIgnoreCase("Paid")
	                    && ll.getEmiPaidDate() != null
	                    && ll.getEmiPaidDate().after(ll.getInstallDate()))
	            {	
	            	String remark = getRemark(loanNo, ll.getSno(), conn);
	            	 if (remark != null && remark.toLowerCase().contains("payment successfully debited from the customer".toLowerCase())) {

//	 		            System.out.println("Skipping eNACH EMI : " + ll.getSno()
//	 		                                         + " | Remark : " + remark);
	 		            continue;
	            	 }
	                j++;
	            }
	            else if(ll.getStatus().equalsIgnoreCase("Due")
	            		&& ll.getInstallDate() != null
	                    && ll.getInstallDate().before(new Date()))
	            {	
	                j++;
	            }
	            total = chargeB * j;
		        //System.out.println("bOOunce charge for due emi RAVI=="+total);
	            
	        }
	        conn.close();
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	    }
	    return total;
	}
	
	
	
	//secureloan module for category list 
	public ArrayList<SelectItem> categoryTypeListt(Connection conn) 
	{
		ArrayList<SelectItem> list=new ArrayList<>();
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq = " select * from category_table ";
			rr=st.executeQuery(qq);
			while(rr.next())
			{
				SelectItem ss=new SelectItem();
				ss.setLabel(rr.getString("category"));
				ss.setValue(rr.getString("id"));
				list.add(ss);
			}
		}
	    
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return list;
	}

	
	//methods for add category with sequuirity Type
	
	public int addCategory(Connection conn, String categoryName, String securityType)
	{
		int i=0;
		String qq="insert into category_table (category,security_type,status) values (?, ?, ?)";
		
		try(PreparedStatement st = conn.prepareStatement(qq)) {
			st.setString(1, categoryName);
			st.setString(2, securityType);
			st.setString(3, "active");
			
			 i=st.executeUpdate();
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return i;
		
	}
	
	
	public ArrayList<categoryInfo> selectCategory(Connection conn)
	{
		ArrayList<categoryInfo> list=new ArrayList<>();
		String qq ="select * from category_table where status='"+"active"+"'";
		
		try(PreparedStatement st = conn.prepareStatement(qq)) {
			
			ResultSet rr =st.executeQuery();
			while(rr.next())
			{
				categoryInfo ss = new categoryInfo();
				ss.setCategoryName(rr.getString("category"));
				String securityId = rr.getString("security_type");
				ss.setSecurityType(securityId);
				String sName = securityTypeById(securityId,conn);				
				ss.setSecurityTypeName(sName);
				ss.setId(rr.getString("id"));
				
				list.add(ss);
				
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		return list;
		
	}
	
	
	
	public int editCategory(Connection conn, categoryInfo selectRow)
	{
		int i=0;
		String qq = "UPDATE category_table SET category = ?, security_type = ? WHERE id = ?";
		
		try(PreparedStatement st = conn.prepareStatement(qq)) {
			
			st.setString(1, selectRow.getCategoryName());
			st.setString(2, selectRow.getSecurityType());
			st.setString(3, selectRow.getId());
			
			i=st.executeUpdate();
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return i;
		
		
	}
	
	
	public int deleteCategory(Connection conn , categoryInfo selectRow)
	{
		int	i=0;
		String qq = "UPDATE category_table SET status = ? WHERE id = ?";
		System.out.println(qq+selectRow.getId());

		
		try(PreparedStatement st =conn.prepareStatement(qq)) {
			
			st.setString(1, "inactive");
			st.setString(2, selectRow.getId());
			
			i=st.executeUpdate();
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		
		return i;
		
	}
	
	public String categoryNameById(String categoryid, Connection conn)
	{
	    String name = "";
	    String qq = "SELECT category FROM category_table WHERE id = ?";

	    try (PreparedStatement st = conn.prepareStatement(qq))
	    {
	        st.setString(1, categoryid);

	        try (ResultSet rr = st.executeQuery())
	        {
	            if (rr.next())
	            {
	                name = rr.getString("category");
	            }
	        }

	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }

	    return name;
	}
	
	public String securityTypeById(String securityType, Connection conn)
	{
	    String name = "";
	    String qq = "SELECT security_type FROM secure_type_table WHERE id = ?";

	    try (PreparedStatement st = conn.prepareStatement(qq))
	    {
	        st.setString(1, securityType);

	        try (ResultSet rr = st.executeQuery())
	        {
	            if (rr.next())
	            {
	                name = rr.getString("security_type");
	            }
	        }

	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }

	    return name;
	}
	
	//this is method for 
	public ArrayList<SelectItem> productListBySecurityType(Connection conn, String securityType)
	{
	    ArrayList<SelectItem> list = new ArrayList<>();
	    
	    String qq = "SELECT p.id, p.brand, p.name, p.model " +
		            "FROM category_table c " +
		            "INNER JOIN products p " +
		            "ON c.id = p.category_id " +
		            "WHERE p.status = 'active' " +
		            "AND c.security_type = ?";

	    try (PreparedStatement st = conn.prepareStatement(qq))
	    {
	        st.setString(1, securityType);

	        try (ResultSet rs = st.executeQuery())
	        {
	            while (rs.next())
	            {
	                SelectItem item = new SelectItem();

	                item.setValue(rs.getString("id"));

	                item.setLabel(
	                	rs.getString("brand") + " / " +
	                    rs.getString("name") + " / " +
	                    rs.getString("model")
	                );

	                list.add(item);
	            }
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	
	
	public ArrayList<SelectItem> secureLoanTypeListr(int branchId,Connection conn) 
	{
		ArrayList<SelectItem> list=new ArrayList<>();
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
		//	String qq="select * from secure_type_table ";
			String qq = "SELECT * FROM secure_type_table WHERE id IN (14, 15)";
			rr=st.executeQuery(qq);
			while(rr.next())
			{
				SelectItem ss=new SelectItem();
				ss.setLabel(rr.getString("security_type"));
				ss.setValue(rr.getString("id"));
				list.add(ss);
			}
		}
	    
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return list;
	}
	
	
	public ArrayList<SelectItem> categoryListByCategoryType(Connection conn, String securityType) 
	{
	    ArrayList<SelectItem> list = new ArrayList<>();

	    String sql = "SELECT id, category, security_type FROM category_table " +
	                 "WHERE security_type = ? AND status='active'";

	    try (PreparedStatement st = conn.prepareStatement(sql)) 
	    {
	        st.setString(1, securityType);

	        try (ResultSet rs = st.executeQuery()) 
	        {
	            while (rs.next()) 
	            {
	                SelectItem item = new SelectItem();

	                item.setValue(rs.getString("id"));
	                item.setLabel(rs.getString("category"));

	                list.add(item);
	            }
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	public ArrayList<SelectItem> categoryListBySecurityType(Connection conn, String securityType) 
	{
	    ArrayList<SelectItem> list = new ArrayList<>();

	    String sql = "SELECT id, category, security_type FROM category_table " +
	                 "WHERE security_type = ? AND status='active'";

	    try (PreparedStatement st = conn.prepareStatement(sql)) 
	    {
	        st.setString(1, securityType);

	        try (ResultSet rs = st.executeQuery()) 
	        {
	            while (rs.next()) 
	            {
	                SelectItem item = new SelectItem();

	                item.setValue(rs.getString("id"));
	                item.setLabel(rs.getString("category"));

	                list.add(item);
	            }
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	public ArrayList<SelectItem> productListByCategory(Connection conn, String categoryId) 
	{
	    ArrayList<SelectItem> list = new ArrayList<>();

	    String sql = "SELECT id, brand, name, model, category_id " +
	                 "FROM products " +
	                 "WHERE status = 'active' " +
	                 "AND category_id = ?";

	    try (PreparedStatement st = conn.prepareStatement(sql)) 
	    {
	        st.setString(1, categoryId);

	        try (ResultSet rs = st.executeQuery()) 
	        {
	            while (rs.next()) 
	            {
	                SelectItem item = new SelectItem();

	                item.setValue(rs.getString("id"));

	                item.setLabel(
	                    rs.getString("brand") + " / " +
	                    rs.getString("name") + " / " +
	                    rs.getString("model")
	                );

	                list.add(item);
	            }
	        }
	    } 
	    catch (Exception e) 
	    {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	
	public int memberAgelimitByBranchid(int branchId, Connection conn)
	{
	    int ageLimit = 0;
	    String qq = "SELECT member_age_limit FROM firm_detail WHERE id = ?";

	    try (PreparedStatement st = conn.prepareStatement(qq))
	    {
	        st.setInt(1, branchId);

	        try (ResultSet rr = st.executeQuery())
	        {
	            if (rr.next())
	            {
	                ageLimit = rr.getInt("member_age_limit");
	            }
	        }

	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }

	    return ageLimit;
	}
	
	
	public double interestAmtByLoannoId(int branchId, String loanNo,String loanType, Connection conn)
	{
	    double interestAmt = 0.0;
	    String qq = "SELECT interestAmount FROM loanpayment WHERE branch_id=? AND loanNo=? AND loanType=? ";

	    try (PreparedStatement st = conn.prepareStatement(qq))
	    {	
	    	st.setInt(1, branchId);
	        st.setString(2, loanNo);
	        st.setString(3, loanType);
	        
	        try (ResultSet rr = st.executeQuery())
	        {
	            if (rr.next())
	            {
	                interestAmt = rr.getDouble("interestAmount");
	            }
	        }

	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }

	    return interestAmt;
	}
	
	
	public String dsaCodeByLoanNo(String loanNo, String loanType, int branchId, Connection conn)
	{
	    String dsaNameCode = "";

	    String qq = "SELECT l.dsacode, e.name " +
	                "FROM " + loanType + " l " +
	                "LEFT JOIN employee e ON l.dsacode = e.dsacode " +
	                "WHERE l.branch_id = ? AND l.loanNo = ?";

	    try (PreparedStatement st = conn.prepareStatement(qq))
	    {
	        st.setInt(1, branchId);
	        st.setString(2, loanNo);

	        try (ResultSet rr = st.executeQuery())
	        {
	            if (rr.next())
	            {
	                String dsaCode = rr.getString("dsacode");
	                String dsaName = rr.getString("name");

	                dsaNameCode = dsaName + " / " + dsaCode;
	            }
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }

	    return dsaNameCode;
	}
	
	
	public double advanceAmountByEmp(String empId, String selectedMonth, Date startDate,
			                         Date endDate, int branchId, Connection conn) {
		double advanceAmt = 0.0;
        
		String qq = "SELECT amount FROM salary_ledger WHERE empId = ? " + "AND type = 'Debit' "
				+ "AND payDate BETWEEN ? AND ? " + "AND branch_id =?  AND month=?";
        
		try (PreparedStatement st = conn.prepareStatement(qq)) {
			st.setString(1, empId);
			st.setDate(2, new java.sql.Date(startDate.getTime()));
			st.setDate(3, new java.sql.Date(endDate.getTime()));
			st.setInt(4, branchId);
			st.setString(5, selectedMonth);

			try (ResultSet rr = st.executeQuery()) {
				while (rr.next()) {
					advanceAmt += rr.getDouble("amount");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return advanceAmt;
	}
	
	
	public double fetchEMIPaidAmountByLoanNo(Connection conn, String loanNo, int emiNo, int branchId) {
		double paidAmount = 0.00;
		try {

			String query = "SELECT SUM(amount) AS emiAmount FROM loanpayment WHERE loanNo=? AND emi_no=? "
					   	 + "AND branch_id=?";

			PreparedStatement ps = conn.prepareStatement(query);

			ps.setString(1, loanNo);
			ps.setInt(2, emiNo);
			ps.setInt(3, branchId);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				paidAmount = rs.getDouble("emiAmount");
			}

			rs.close();
			ps.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		return paidAmount;
	}
	
	public String fetchLastEMIPaymentByLoanNo(Connection conn, String loanNo, int emiNo, int branchId) {

	    String paidDate = "";

	    String query = "SELECT pay_date FROM loanpayment WHERE loanNo = ? AND emi_no = ? "
					 + "AND branch_id = ? AND pay_date IS NOT NULL ORDER BY pay_date DESC LIMIT 1";

	    try (PreparedStatement ps = conn.prepareStatement(query)) {

	        ps.setString(1, loanNo);
	        ps.setInt(2, emiNo);
	        ps.setInt(3, branchId);

	        System.out.println("================================");
	        System.out.println("LOAN NO  : " + loanNo);
	        System.out.println("EMI NO   : " + emiNo);
	        System.out.println("BRANCH   : " + branchId);

	        try (ResultSet rs = ps.executeQuery()) {

	            if (rs.next()) {

	                Date date = rs.getDate("pay_date");

	                if (date != null) {
	                    paidDate = new SimpleDateFormat("dd-MM-yyyy").format(date);
	                }

	            } else {

	                System.out.println("NO PAYMENT FOUND");

	            }
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return paidDate;
	}

	
	
	public double paidRdAmount(String loanNo,  int branchId, Connection conn) {
		//String eDate=sdfForDb.format(endDate);
		ArrayList<String> list= new ArrayList<>();
		double amount=0.00;
		try(Connection con = DataBaseConnection.JAVAConnection())
		{
			
			Statement st=con.createStatement();
			String query="select sum(amount) from payment_receipt where user_no='"+loanNo+"' and branch_id='"+branchId+"' and receipt_status='approved' and chequeStatus='cleared'";
		    ResultSet rs= st.executeQuery(query);
		    //System.out.println(query);
		    if(rs.next())
			{
				amount=rs.getDouble("sum(amount)");
			}
		}
		catch(Exception e)
		{
			 e.printStackTrace();//fileCreation(e.getMessage());
		}
		return amount;
	}
	
	public int LoanTermByLoanNo(String loanNo, int branchId,Connection conn) {
		int terms = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			String loanType;
			if (loanNo.contains("GL")) {
				loanType = "grouploan";
			} else if (loanNo.contains("SL")) {
				loanType = "secure_loan";
			} else {
				loanType = "personalloan";
			}

			String query = "SELECT terms FROM " + loanType + " WHERE loanNo = ? AND branch_id= ?";
			ps = conn.prepareStatement(query);
			ps.setString(1, loanNo);
			ps.setInt(2, branchId);
			rs = ps.executeQuery();

			if (rs.next()) {
				terms = rs.getInt("terms");
			}

		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return terms;
	}
	
	// PTP WORK MODULE INSERT UPDATE DELETE AND VIEW FUNCTION

			public int addPtp(String shareholderNo, String loanNo, double emiAmt, double ptpAmount, Date ptpDate,String emiNo,
					          String remarks, String createdBy, boolean ptpCreate, int branchId, Connection conn)
			{
				int i=0;
				String qq = "INSERT INTO ptp_table "
		                  + "(shareholder_no, loan_no, emi_amt, ptp_amount, ptp_date, emi_no, status, remarks, created_by, ptp_created, branch_id) "
		                  + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
				
				try(PreparedStatement ps = conn.prepareStatement(qq)) {
					 ps.setString(1, shareholderNo);
			         ps.setString(2, loanNo);
			         ps.setDouble(3, emiAmt);
			         ps.setDouble(4, ptpAmount);
			         ps.setDate(5, new java.sql.Date(ptpDate.getTime()));
			         ps.setString(6, emiNo);
			         ps.setString(7, "pending");
			         ps.setString(8, remarks == null ? "" : remarks);
			         ps.setString(9, createdBy);
			         ps.setBoolean(10, ptpCreate);
			         ps.setInt(11, branchId);
					
					 i=ps.executeUpdate();
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return i;
				
			}
			
			
			
			public boolean checkPtpCreated(String shareholderNo, String loanNo,
			        String emiNo, int branchId, Connection conn)
			{
			    boolean exists = false;

				String sql = "SELECT id FROM ptp_table WHERE shareholder_no=? AND loan_no=? AND emi_no=? "
						   + "AND branch_id=? AND ptp_created=1 LIMIT 1";

			    try (PreparedStatement ps = conn.prepareStatement(sql))
			    {
			        ps.setString(1, shareholderNo);
			        ps.setString(2, loanNo);
			        ps.setString(3, emiNo);
			        ps.setInt(4, branchId);

			        ResultSet rs = ps.executeQuery();

			        if (rs.next())
			        {
			            exists = true;
			        }
			    }
			    catch (Exception e)
			    {
			        e.printStackTrace();
			    }

			    return exists;
			}
			
			
			public int addBulkPtp(List<UserInfo> rows, Date ptpDate, String remarks, String createdBy, int branchId,
					Connection conn) {
				System.out.println("bulk ptp method is calling ");
				int successCount = 0;

				String sql = "INSERT INTO ptp_table (shareholder_no, loan_no, emi_amt, ptp_amount, ptp_date, emi_no, "
						   + "status, remarks, created_by, ptp_created, branch_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

				try (PreparedStatement ps = conn.prepareStatement(sql)) {

					for (UserInfo u : rows) {

						if (u == null) {
							continue;
						}

						String memberNo = u.getShareHolderNo();
						String loanNo = u.getLoanNo();
						String emiNo = u.getPtpemiNo();

						if (memberNo == null || memberNo.trim().equals("") || loanNo == null || loanNo.trim().equals("")
								|| emiNo == null || emiNo.trim().equals("")) {
							System.out.println("is it failed");
							continue;
						}

						double emiAmount = u.getAmount();

						// Bulk PTP amount = EMI amount
						double ptpAmount = emiAmount;

						ps.setString(1, memberNo);
						ps.setString(2, loanNo);
						ps.setDouble(3, emiAmount);
						ps.setDouble(4, ptpAmount);
						ps.setDate(5, new java.sql.Date(ptpDate.getTime()));
						ps.setString(6, emiNo);
						ps.setString(7, "pending");
						ps.setString(8, remarks == null ? "" : remarks);
						ps.setString(9, createdBy);
						ps.setBoolean(10, true);
						ps.setInt(11, branchId);

						ps.addBatch();
					}

					int[] result = ps.executeBatch();

					for (int r : result) {

						if (r > 0 || r == Statement.SUCCESS_NO_INFO) {
							successCount++;
						}
					}

				} catch (Exception e) {
					e.printStackTrace();
				}

				return successCount;
			}
			
			public ArrayList<PTPInfo> showPtpReport(Date startDate, Date endDate, String status, int branchId,  Connection conn)
			{	
				DataBaseMethodTaheer dbh = new DataBaseMethodTaheer();
				String sDate = sdfForDb.format(startDate);
				String eDate = sdfForDb.format(endDate);
				ArrayList<PTPInfo> list=new ArrayList<>();
				String qq = "SELECT * FROM ptp_table WHERE ptp_date BETWEEN ? AND ? "
				          + "AND status = ? AND branch_id = ?";
//				String qq =
//					    "SELECT p.*, " +
//					    "CASE WHEN EXISTS ( " +
//					    "SELECT 1 FROM ptp_records r " +
//					    "WHERE r.previous_ptp_id = p.id " +
//					    "AND r.new_ptp_created = 1 " +
//					    "AND r.branch_id = CAST(p.branch_id AS CHAR) " +
//					    ") THEN 1 ELSE 0 END AS button_disabled " +
//					    "FROM ptp_table p " +
//					    "WHERE p.ptp_date BETWEEN ? AND ? " +
//					    "AND p.status = ? " +
//					    "AND p.branch_id = ?";

				try(PreparedStatement ps = conn.prepareStatement(qq)) {
						ps.setString(1, sDate);
						ps.setString(2, eDate);
						ps.setString(3, status);
						ps.setInt(4, branchId);
					ResultSet rr =ps.executeQuery();
					System.out.println("query"+qq);
					while(rr.next())
					{	
						PTPInfo pi = new PTPInfo();
						pi.setId(rr.getInt("id"));
						String shareholderno = rr.getString("shareholder_no");
						pi.setShareholderNo(shareholderno);
						String name = dbh.shareholderNameByShareHolderNo(conn, shareholderno);
						pi.setCustomerName(name);
						pi.setLoanNo(rr.getString("loan_no"));
						pi.setAmount(rr.getDouble("emi_amt"));
						pi.setPtpAmount(rr.getDouble("ptp_amount"));
						pi.setPtpEmiNo(rr.getString("emi_no"));
						pi.setPtpStatus(rr.getString("status"));
						pi.setPtpRemarks(rr.getString("remarks"));
						pi.setPtpDate(rr.getDate("ptp_date"));
						pi.setActualPtpPayDate(rr.getDate("actual_payment_date"));
						pi.setId(rr.getInt("id"));
						// Check whether new PTP already exists
						boolean alreadyCreated = isNewPtpAlreadyCreated(pi.getId(), branchId, conn);
						pi.setNewPtpCreated(alreadyCreated);
						System.out.println("boolean is creatd"+ alreadyCreated);
						
						list.add(pi);
					 }
					
				} catch (Exception e) {
					 e.printStackTrace();
				}
				return list;
				
			}
			
			
			public int updatePtpAfterEmiPay(String loanNo, int emiNo, double actualPaidAmount, Date actualPaymentDate,
									  		 int branchId, Connection conn) {
			    int result = 0;
			    
				String query = "UPDATE ptp_table SET actual_paid_amount = ?, actual_payment_date = ?, "
						     + "status = 'kept' WHERE loan_no = ? AND emi_no = ? AND branch_id = ? "
						     + "AND status = 'pending'";

			    try (PreparedStatement ps = conn.prepareStatement(query)) {
			        ps.setDouble(1, actualPaidAmount);
			        ps.setDate(2, new java.sql.Date(actualPaymentDate.getTime()));
			        ps.setString(3, loanNo);
			        ps.setInt(4, emiNo);
			        ps.setString(5, String.valueOf(branchId));
			        result = ps.executeUpdate();

			    } catch (Exception e) {
			        e.printStackTrace();
			    }

			    return result;
			}
			
			
			 public int updateBrokenPtp(int branchId,Connection conn) {

			        int result = 0;

					String query = "UPDATE ptp_table SET status = 'broken' WHERE status = 'pending' "
							     + "AND ptp_date < CURDATE() AND actual_payment_date IS NULL AND branch_id = ?";

			        try (PreparedStatement ps = conn.prepareStatement(query)) {
			        	 ps.setInt(1, branchId);
			            result = ps.executeUpdate();
						System.out.println("Branch ID = " + branchId + " | Broken PTP = " + result);
			        } catch (Exception e) {
			            e.printStackTrace();
			        }

			        return result;
			    }
			
			 

				public int addPtpRecordForBroken(int ptpOldId, String shareholderNo, String loanNo, double emiAmt, double ptpAmount,
						   Date oldPtpDate, String emiNo, Date newPtpDate, String status, String remarks, String createdBy,
						   Boolean newPtpCreated, int branchId, Connection conn) {

					int i = 0;

					String qq = "INSERT INTO ptp_records (previous_ptp_id,ptp_shareholder_no, ptp_loan_no, emi_amt, ptp_amount, "
							+ "old_ptp_date, emi_no, new_ptp_date, status, ptp_remarks, created_by, new_ptp_created, branch_id) "
							+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

					try (PreparedStatement ps = conn.prepareStatement(qq)) {
						ps.setInt(1, ptpOldId);
						ps.setString(2, shareholderNo);
						ps.setString(3, loanNo);
						ps.setDouble(4, emiAmt);
						ps.setDouble(5, ptpAmount);
						if (oldPtpDate != null) {
							ps.setDate(6, new java.sql.Date(oldPtpDate.getTime()));
						} else {
							ps.setNull(6, java.sql.Types.DATE);
						}
						ps.setString(7, emiNo);

						if (newPtpDate != null) {
							ps.setDate(8, new java.sql.Date(newPtpDate.getTime()));
						} else {
							ps.setNull(8, java.sql.Types.DATE);
						}
						ps.setString(9, status);
						ps.setString(10, remarks == null ? "" : remarks);
						ps.setString(11, createdBy);
						ps.setBoolean(12, newPtpCreated);
						ps.setString(13, String.valueOf(branchId));

						i = ps.executeUpdate();

					} catch (Exception e) {
						e.printStackTrace();
					}

					return i;
				}
			 
				public boolean isNewPtpAlreadyCreated(int previousPtpId, int branchId, Connection conn) {

				    boolean created = false;

				    String query = "SELECT COUNT(*) FROM ptp_records "
				                 + "WHERE previous_ptp_id = ? "
				                 + "AND branch_id = ? "
				                 + "AND new_ptp_created = 1";

				    try (PreparedStatement ps = conn.prepareStatement(query)) {

				        ps.setInt(1, previousPtpId);
				        ps.setString(2, String.valueOf(branchId));

				        try (ResultSet rs = ps.executeQuery()) {

				            if (rs.next()) {
				                created = rs.getInt(1) > 0;
				            }
				        }

				        System.out.println("Previous PTP ID = " + previousPtpId);
				        System.out.println("New PTP Already Created = " + created);

				    } catch (Exception e) {
				        e.printStackTrace();
				    }

				    return created;
				}
	
				
				
				// function to check enach mandate for loandisburse

				public String subscriptionStatusCheckByLoanno(String loanNo, Connection conn) {
					String status = null;
					System.out.println("this method is running"+loanNo);
					String query = "SELECT subscription_status FROM subscriptions_overview " 
								 + "WHERE loan_no = ?";
					try (PreparedStatement ps = conn.prepareStatement(query)) {

						ps.setString(1, loanNo);

						try (ResultSet rs = ps.executeQuery()) {

							if (rs.next()) {
								status = rs.getString("subscription_status");
							}
						}

					} catch (Exception e) {
						e.printStackTrace();
					}

					return status;
				}
				
				
				public Map<String, Integer> activeLoanMemberList(int branchId, Connection conn,String dsacode) {

				    Map<String, Integer> loanCounts = new LinkedHashMap<>();

				    String[] tables = {"personalloan", "secure_loan", "grouploan"};

				    for (String table : tables) {
				    	String column = table.equals("grouploan") ? "teamLeader" : "shareholderno";
				    	String qq = "SELECT COUNT(DISTINCT " + column + ") FROM " + table + " WHERE paidStatus = ? AND status = 'Approve' AND branch_id = ? AND dsacode=?";
				        try (PreparedStatement ps = conn.prepareStatement(qq)) {

				            ps.setString(1, "unpaid");
				            ps.setInt(2, branchId);
				            ps.setString(3, dsacode);

				            try (ResultSet rs = ps.executeQuery()) {
				                if (rs.next()) {
				                    loanCounts.put(table, rs.getInt(1));
				                }
				            }

				        } catch (SQLException e) {
				            e.printStackTrace();
				            loanCounts.put(table, 0);
				        }
				    }

				    return loanCounts;
				}
	
	
				public ArrayList<LoanInfo> showManualCharges(String loanNo, Connection conn) {

				    ArrayList<LoanInfo> list = new ArrayList<LoanInfo>();

				    String query = "SELECT id, loan_no, loan_type, date, amount, remark, entry_date "
				                 + "FROM manual_charges WHERE loan_no = ? ORDER BY `date` DESC";

				    try (PreparedStatement ps = conn.prepareStatement(query)) {

				        ps.setString(1, loanNo);

				        try (ResultSet rs = ps.executeQuery()) {

				            while (rs.next()) {

				            	LoanInfo info = new LoanInfo();
				            	
				            	int cId = rs.getInt("id");
				                info.setId(String.valueOf(cId));
				                info.setLoanNo(rs.getString("loan_no"));
				                info.setLoanType(rs.getString("loan_type"));
				                info.setPaidDate(rs.getDate("date"));
				                info.setAmount(rs.getDouble("amount"));
				                info.setRemark(rs.getString("remark"));
				              
				                list.add(info);
				            }
				        }

				    } catch (Exception e) {
				        e.printStackTrace();
				    }

				    return list;
				}
				
				
			public double TotalManualCharges(String loanNo, Connection conn) {

			    double totalAmount = 0.0;
			    String query = "SELECT COALESCE(SUM(amount), 0) AS total_amount "
			                 + "FROM manual_charges WHERE loan_no = ?";
			    
			    try (PreparedStatement ps = conn.prepareStatement(query)) {
			        ps.setString(1, loanNo);
			        try (ResultSet rs = ps.executeQuery()) {
			            if (rs.next()) {
			                totalAmount = rs.getDouble("total_amount");
			            }
			        }
			    } catch (SQLException e) {
			        e.printStackTrace();
			    }
			    return totalAmount;
			}
	
			
			public int addManualCharges(String loanNo, String loanType, Date date, String amount, String remark,
									    Date entryDate, Connection conn) {

				int i = 0;
				String qq = "INSERT INTO manual_charges(loan_no, loan_type, date, amount, remark, entry_date) "
					      + "VALUES (?, ?, ?, ?, ?, ?)";
				
				try (PreparedStatement ps = conn.prepareStatement(qq)) {
				ps.setString(1, loanNo);
				ps.setString(2, loanType);
				if (date != null) {
					ps.setDate(3, new java.sql.Date(date.getTime()));
				} else {
					ps.setNull(3, java.sql.Types.DATE);
				}
				ps.setString(4, amount);
				ps.setString(5, "System Generated Bounce Charge");
				ps.setTimestamp(6, new Timestamp(entryDate.getTime()));
				
				i = ps.executeUpdate();
				
				} catch (Exception e) {
				e.printStackTrace();
				}
				
				return i;
				}
	
	
		
		
 //  Getter And Setter		
	public String getStartSessionDateStr() {
		return startSessionDateStr;
	}
	public void setStartSessionDateStr(String startSessionDateStr) {
		this.startSessionDateStr = startSessionDateStr;
	}
	public DecimalFormat getDc() {
		return dc;
	}
	public void setDc(DecimalFormat dc) {
		this.dc = dc;
	}
	public SimpleDateFormat getDateObj() {
		return dateObj;
	}
	public void setDateObj(SimpleDateFormat dateObj) {
		this.dateObj = dateObj;
	}
	public DataBaseMethods getDb() {
		return db;
	}
	public void setDb(DataBaseMethods db) {
		this.db = db;
	}
	public DataBaseReportMethods getDbrm() {
		return dbrm;
	}
	public void setDbrm(DataBaseReportMethods dbrm) {
		this.dbrm = dbrm;
	}
	public SimpleDateFormat getSdfForDb() {
		return sdfForDb;
	}
	public void setSdfForDb(SimpleDateFormat sdfForDb) {
		this.sdfForDb = sdfForDb;
	}
	
	
	

}
