package main_package;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.TimeZone;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

public class DataBaseMethodsRavi implements Serializable 
{
	DataBaseMethods db=new DataBaseMethods();

	private static final long serialVersionUID = 1L;
	
	
	public String checkAppLoanPaymentStatus(String loanNo, Connection conn) {

		String apiStatus = "unpaid";
        
		String sql = "select status FROM loan_payment_from_app WHERE loan_no = ? ORDER BY id DESC LIMIT 1";

		try (PreparedStatement ps = conn.prepareStatement(sql)) {
			ps.setString(1, loanNo);
			try (ResultSet rs = ps.executeQuery()) {
				if (rs.next()) {
					String status = rs.getString("status");
					if (status != null) {
						if (status.equalsIgnoreCase("pending")) {
							apiStatus = "raised";
						} else if (status.equalsIgnoreCase("active")) {
							apiStatus = "paid";
						} else if (status.equalsIgnoreCase("delete")) {
							apiStatus = "unpaid";
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return apiStatus;
	}
	
	

public String checkWebLoanPaymentStatus(String loanNo, Connection conn) {

    String apiStatus = "unpaid";

	String sql = "SELECT receipt_status, chequeStatus FROM loanpayment WHERE loanNo = ? ORDER BY id DESC LIMIT 1";

    try (PreparedStatement ps = conn.prepareStatement(sql)) {

        ps.setString(1, loanNo);

        try (ResultSet rs = ps.executeQuery()) {

            if (rs.next()) {

                String receiptStatus = rs.getString("receipt_status");
                String chequeStatus = rs.getString("chequeStatus");

                if ("approved".equalsIgnoreCase(receiptStatus)
                        && "cleared".equalsIgnoreCase(chequeStatus)) {

                    apiStatus = "paid";

                } else {

                    apiStatus = "unpaid";
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return apiStatus;
}
	
	
	public int deleteAdvanceSalaryPay(
	        int id, String employeeCode, String month,
	        String year, int branchId) {

		String checkQuery = "SELECT 1 FROM emp_salary " + "WHERE employee_code=? AND salary_month=? "
				+ "AND salary_year=? AND salary_status='active' LIMIT 1";

		String updateQuery = "UPDATE emp_adv_salary_pay " + "SET adv_salary_status='inactive' "
				+ "WHERE id=? AND month=? AND year=? " + "AND branch_id=? AND adv_salary_status='active'";

	    try (Connection conn = DataBaseConnection.JAVAConnection()) {

	        // Check whether salary is already paid
	        try (PreparedStatement checkPs = conn.prepareStatement(checkQuery)) {

	            checkPs.setString(1, employeeCode);
	            checkPs.setString(2, month);
	            checkPs.setString(3, year);

	            try (ResultSet rs = checkPs.executeQuery()) {
	                if (rs.next()) {
	                    return -1;
	                }
	            }
	        }

	        // Soft delete: change status instead of deleting row
	        try (PreparedStatement ps = conn.prepareStatement(updateQuery)) {

	            ps.setInt(1, id);
	            ps.setString(2, month);
	            ps.setString(3, year);
	            ps.setInt(4, branchId);

	            return ps.executeUpdate();
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return 0;
	}
    
    
	
	public double getAdvanceSalaryAmount(String empId, String month,
	        String year, int branchId) {

	    double amount = 0.0;

		String query = "SELECT COALESCE(SUM(amount), 0) AS totalAmount " + "FROM emp_adv_salary_pay "
				+ "WHERE empId=? AND month=? AND year=? " + "AND branch_id=? AND adv_salary_status='active'";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query)) {

	        ps.setString(1, empId);
	        ps.setString(2, month);
	        ps.setString(3, year);
	        ps.setInt(4, branchId);

	        try (ResultSet rs = ps.executeQuery()) {

	            if (rs.next()) {
	                amount = rs.getDouble("totalAmount");
	            }
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return amount;
	}

	public ArrayList<AdvanceSalaryPayInfo> getAdvanceSalaryPayList(
	        String empId, String month, String year, int branchId) {

	    ArrayList<AdvanceSalaryPayInfo> list = new ArrayList<>();

		String query = "SELECT * FROM emp_adv_salary_pay " + "WHERE empId=? AND month=? AND year=? AND branch_id=? "
				+ "AND adv_salary_status='active' " + "ORDER BY payDate DESC, id DESC";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query)) {

	        ps.setString(1, empId);
	        ps.setString(2, month);
	        ps.setString(3, year);
	        ps.setInt(4, branchId);

	        // Employee code fetch
	        String employeeCode = fetchEmployeeDetail(empId, "employeeCode");

	        String salaryQuery = "SELECT 1 FROM emp_salary "
	                + "WHERE employee_code=? AND salary_month=? "
	                + "AND salary_year=? AND salary_status='active' LIMIT 1";

	        try (PreparedStatement salaryPs = conn.prepareStatement(salaryQuery)) {

	            salaryPs.setString(1, employeeCode);
	            salaryPs.setString(2, month);
	            salaryPs.setString(3, year);

	            try (ResultSet rs = ps.executeQuery()) {

	                while (rs.next()) {

	                    AdvanceSalaryPayInfo obj = new AdvanceSalaryPayInfo();

	                    obj.setId(rs.getInt("id"));
	                    obj.setEmpId(rs.getString("empId"));
	                    obj.setMonth(rs.getString("month"));
	                    obj.setYear(rs.getString("year"));
	                    obj.setAmount(rs.getString("amount"));
	                    obj.setPayMode(rs.getString("payMode"));
	                    obj.setBankName(rs.getString("bank_name"));
	                    obj.setBankBranch(rs.getString("bank_branch"));
	                    obj.setDescription(rs.getString("description"));
	                    obj.setChequeNo(rs.getString("chq_no"));
	                    obj.setPayDate(rs.getDate("payDate"));
	                    obj.setChequeDate(rs.getDate("chq_date"));

	                    // Check salary paid or not
	                    boolean salaryPaid = false;

	                    try (ResultSet salaryRs = salaryPs.executeQuery()) {
	                        if (salaryRs.next()) {
	                            salaryPaid = true;
	                        }
	                    }

	                    obj.setSalaryPaid(salaryPaid);

	                    list.add(obj);
	                }
	            }
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return list;
	}

	public int addAdvanceSalaryPay(String empId, String month, String year, String amount, int branchId, Date payDate,
			String payMode, String bankName, String bankBranch, String description, String chequeNo, Date chequeDate) {

		String query = "INSERT INTO emp_adv_salary_pay " + "(empId, month, year, amount, branch_id, payDate, "
				+ "payMode, bank_name, bank_branch, description, " + "chq_no, chq_date, adv_salary_status) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		try (Connection conn = DataBaseConnection.JAVAConnection();
				PreparedStatement ps = conn.prepareStatement(query)) {

			ps.setString(1, empId);
			ps.setString(2, month);
			ps.setString(3, year);
			ps.setString(4, amount);
			ps.setInt(5, branchId);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			sdf.setTimeZone(TimeZone.getTimeZone("Asia/Kolkata"));

			String dateString = sdf.format(payDate);
			java.sql.Date sqlPayDate = java.sql.Date.valueOf(dateString);

			ps.setDate(6, sqlPayDate);
//			ps.setDate(6, new java.sql.Date(payDate.getTime()));

			ps.setString(7, payMode);
			ps.setString(8, bankName);
			ps.setString(9, bankBranch);
			ps.setString(10, description);
			ps.setString(11, chequeNo);

			if (chequeDate != null) {
				ps.setDate(12, new java.sql.Date(chequeDate.getTime()));
			} else {
				ps.setNull(12, java.sql.Types.DATE);
			}
			
			ps.setString(13, "active");

			return ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}

		return 0;
	}
	
	
	
	public JSONArray todayAttendanceByUserId(String userId, Connection conn) {

	    JSONArray list = new JSONArray();

	    try {
	        Statement st = conn.createStatement();

	        ResultSet rs = st.executeQuery(
	            "SELECT * FROM attendance_table WHERE userid = '" 
	            + userId + "' AND attendance_date = CURDATE()"
	        );

	        while (rs.next()) {
	            JSONObject obj = new JSONObject();
           
	            ResultSetMetaData rsmd = rs.getMetaData();

	            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
	                obj.put(rsmd.getColumnName(i), rs.getString(i));
	            }

	            list.add(obj);
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	
	
	
	public UserInfo employeeDetail(String userId, Connection conn)
	{
	    UserInfo uu = new UserInfo();

	    String query = "SELECT uname, name, email_id, mobile, "
	                 + "address, other_photo "
	                 + "FROM employee "
	                 + "WHERE uname = ? AND status = 'active' "
	                 + "LIMIT 1";

	    try (PreparedStatement ps = conn.prepareStatement(query))
	    {
	        ps.setString(1, userId);

	        try (ResultSet rs = ps.executeQuery())
	        {
	            if (rs.next())
	            {
	                uu.setShareHolderNo(rs.getString("uname"));
	                uu.setName(rs.getString("name"));
	                uu.setMobile(rs.getString("mobile"));
	                uu.setAddress(rs.getString("address"));
	                uu.setApplicant_photo( db.fileViewPathDetail(conn) + rs.getString("other_photo"));
	                uu.setEmail(rs.getString("email_id"));
	               
	            }
	            else
	            {
	                return null;
	            }
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	        return null;
	    }

	    return uu;
	}
	
	public int paymentRequest2(int branchId,String employee, Date startDate, String loanType, String loanNo, String amount,
			String payMode, String savingType, String transactionId, String response, String attendance,String paymentProof) {
		
		 if(savingType == null || savingType.equals(""))
		    {
		        savingType = "N/A";
		    }

		    int j = 0;

		    Timestamp mDate = new Timestamp(startDate.getTime());
		    mDate.setHours(0);
		    mDate.setMinutes(0);
		    mDate.setSeconds(0);
		    mDate.setNanos(0);

		try(Connection conn = DataBaseConnection.JAVAConnection()) {
			
			String query ="insert into loan_payment_from_app("
	                + "employee,loan_type,loan_no,pay_date,amount,pay_mode,"
	                + "saving_type,transaction_id,response,attendance,branch_id,payment_proof,payment_source)"
	                + " values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
			
			PreparedStatement st1 = conn.prepareStatement(query);
			
			st1.setString(1, employee);
	        st1.setString(2, loanType);
	        st1.setString(3, loanNo);
	        st1.setTimestamp(4, mDate);
	        st1.setString(5, amount);
	        st1.setString(6, payMode);
	        st1.setString(7, savingType);
	        st1.setString(8, transactionId);
	        st1.setString(9, response);
	        st1.setString(10, attendance);
	        st1.setInt(11, branchId);
	        st1.setString(12, paymentProof);
	        st1.setString(13, "Customer");
	        
	        int i = st1.executeUpdate();
	        
	        
	        if(i >= 1)
	        {
	            j++;
	        }
			
		} catch (Exception e) {
           e.printStackTrace();
		}
		
		return j;
		
	}
	
	
	
	public String loanTypeByLoanNo(String loanNo)
	{
		if (loanNo.contains("GL"))
		{
			return "grouploan";
		}
		else if (loanNo.contains("SL"))
		{
			return "secure_loan";
		}
		else if (loanNo.contains("BL"))
		{
			return "business_loan";
		}
		else  
		{
			return "personalloan";
		}
	}
	
	
	public String fetchEmployeeDetail(String employee, String type) {
		String value = "";
		try (Connection conn = DataBaseConnection.JAVAConnection()) {

			String qq = " select * from employee where uname ='" + employee + "'";

			try (PreparedStatement st = conn.prepareStatement(qq)) {
				ResultSet rr = st.executeQuery();

				if (rr.next()) {
					if (type.equalsIgnoreCase("salaryAmount")) {
						return value = rr.getString("salary");
					} else if (type.equalsIgnoreCase("employeeName")) {
						return value = rr.getString("name");
					} else if (type.equalsIgnoreCase("employeeCode")) {
						return value = rr.getString("dsacode");
					} else if (type.equalsIgnoreCase("funtion")) {
						return value = "";
					} else if (type.equalsIgnoreCase("panNo")) {
						return value = rr.getString("pan_card");
					}

					else if (type.equalsIgnoreCase("pfNo")) {
						return value = rr.getString("pf_acc");
					}

					else if (type.equalsIgnoreCase("branch")) {
						return value = rr.getString("bank_branch");
					}

					else if (type.equalsIgnoreCase("uanNo")) {
						return value = rr.getString("uan");
					}

					else if (type.equalsIgnoreCase("state")) {
						return value = "rajasthan";
					} else if (type.equalsIgnoreCase("esicNo")) {
						return value = "";
					} else if (type.equalsIgnoreCase("designation")) {
						int id = rr.getInt("designation");
						System.out.println(
								"spj" + rr.getInt("designation") + fetchhrmNsmeById("Designation", conn, id));
						return value = fetchhrmNsmeById("Designation", conn, id);
					} else if (type.equalsIgnoreCase("bankName1")) {
						String bankName = rr.getString("bank_name");
						return value = bankName;
					} else if (type.equalsIgnoreCase("bankAccNo")) {
						return value = rr.getString("bank_account_no");
					} else if (type.equalsIgnoreCase("dateOfJoin")) {
						return value = rr.getString("doj");
					}

				}

			} catch (Exception e) {
				// TODO: handle exception
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return value;

	}
	
	
	public String fetchhrmNsmeById(String groupname ,Connection conn, int id) throws SQLException
	{
		String name="";
		
		    Statement st=conn.createStatement();
			String qq="select * from hrm_basic where group_name='"+groupname+"' AND id='"+id+"' ";
			ResultSet rr =st.executeQuery(qq);
			
			if(rr.next())
			{
				name = rr.getString("name");
			}
		
		return name;
		
	}
	
	public String userIdByLoanNo(String loanNo, Connection conn) {

		String userId = "";
		String loanType = loanTypeByLoanNo(loanNo);

		if (loanType.isEmpty()) {
			return userId;
		}

		String loanColumn = loanType.equals("grouploan") ? "sub_loan_no" : "loanNo";

		String query = "SELECT user_id FROM " + loanType + " WHERE " + loanColumn + " = ? LIMIT 1";

		try (PreparedStatement ps = conn.prepareStatement(query)) {

			ps.setString(1, loanNo);

			try (ResultSet rs = ps.executeQuery()) {
				if (rs.next()) {
					userId = rs.getString("user_id");
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return userId;
	}

	public int branchIdByLoanNo(String loanNo, Connection conn) {

		int branchId = 0;
		String loanType = loanTypeByLoanNo(loanNo);

		if (loanType.isEmpty()) {
			return branchId;
		}

		String loanColumn = loanType.equals("grouploan") ? "sub_loan_no" : "loanNo";

		String query = "SELECT branch_id FROM " + loanType + " WHERE " + loanColumn + " = ? LIMIT 1";

		try (PreparedStatement ps = conn.prepareStatement(query)) {

			ps.setString(1, loanNo);

			try (ResultSet rs = ps.executeQuery()) {
				if (rs.next()) {
					branchId = rs.getInt("branch_id");
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return branchId;
	}
	
	
	
	public UserInfo directorDetail()
	{
	    UserInfo uu = new UserInfo();

	    try(Connection conn = DataBaseConnection.JAVAConnection())
	    {
	        Statement st = conn.createStatement();
            
	        String qq = "select mobile_no, address1, address2, address3, address4,"
	                  + "director_name, director_f_name, director_photo_path "
	                  + "from firm_detail limit 1";
            
	        ResultSet ss = st.executeQuery(qq);

	        if(ss.next())
	        {
	            uu.setMobile(ss.getString("mobile_no"));

	            String address = ss.getString("address1") + ", "
	                           + ss.getString("address2") + ", "
	                           + ss.getString("address3") + ", "
	                           + ss.getString("address4");
	            uu.setAddress(address);

	            uu.setName(ss.getString("director_name"));
	            uu.setFather_husband_name(ss.getString("director_f_name"));

	            db.filePathDetail(conn);
               System.out.println(""+ss.getString("director_photo_path"));
	            uu.setApplicant_photo(
	                db.onlineFileViewPath + ss.getString("director_photo_path")
	            );
	        }
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	    }

	    return uu;
	}
    
	
	public boolean isLoanDisbursed(String loanNo , Connection conn) {
		
		 boolean status = false;

		    String query = "select loanNo from debit_loan "
		                 + "where loanNo=? and status='cleared' limit 1";
		    
		    try(PreparedStatement ps = conn.prepareStatement(query)) {
				
		    	  ps.setString(1, loanNo);

		          try(ResultSet rs = ps.executeQuery())
		          {
		              if(rs.next())
		              {
		                  status = true;
		              }
		          }
		    	
			} catch (Exception e) {
				 e.printStackTrace();
			       
			}
		
		return status;
	}
	
	
  public String appLink() {
		
		String appLink = "";
		
		try(Connection conn = DataBaseConnection.JAVAConnection()) {
			String query = "select app_link from firm_detail limit 1";
			
			try(PreparedStatement ps = conn.prepareStatement(query);
					ResultSet rs = ps.executeQuery())
			{
				if (rs.next()) {
					appLink = rs.getString("app_link");
				}
			}
		} catch (Exception e) {
            e.printStackTrace();
		}
		return appLink;
	}
//	
//  public JSONObject checkLoanPaymentSource(String loanNo, Date searchDate, Connection conn) 
//	{
//	    JSONObject obj = new JSONObject();
//
//	    try 
//	    {
//	        String query = 
//	                "select loan_no, amount, employee " + 
//	                "from loan_payment_from_app " + 
//	                "where loan_no=? " + 
//	                "and DATE(pay_date)=? " + 
//	                "order by pay_date desc " + 
//	                "limit 1";
//
//	        try(PreparedStatement ps = conn.prepareStatement(query)) 
//	        {
//	            ps.setString(1, loanNo);
//	            ps.setDate(2, new java.sql.Date(searchDate.getTime()));
//
//	            try(ResultSet rs = ps.executeQuery()) 
//	            {
//	                if(rs.next()) 
//	                {
//	                    obj.put("loanNo", rs.getString("loan_no"));
//	                    obj.put("amount", rs.getString("amount"));
//
//	                    String empUserId = rs.getString("employee");
//
//	                    obj.put("userId", empUserId);
//	                    obj.put("empName", getEmployeeName(empUserId, conn));
//	                    obj.put("source", "by app");
//
//	                    return obj;
//	                }
//	            }
//	        }
//
//	        query = 
//	                "select loanNo, amount, user_id " + 
//	                "from loanpayment " + 
//	                "where loanNo=? " + 
//	                "and DATE(pay_date)=? " + 
//	                "order by pay_date desc " + 
//	                "limit 1";
//
//	        try(PreparedStatement ps = conn.prepareStatement(query)) 
//	        {
//	            ps.setString(1, loanNo);
//	            ps.setDate(2, new java.sql.Date(searchDate.getTime()));
//
//	            try(ResultSet rs = ps.executeQuery()) 
//	            {
//	                if(rs.next()) 
//	                {
//	                    obj.put("loanNo", rs.getString("loanNo"));
//	                    obj.put("amount", rs.getString("amount"));
//
//	                    String empUserId = rs.getString("user_id");
//
//	                    obj.put("userId", empUserId);
//	                    obj.put("empName", getEmployeeName(empUserId, conn));
//	                    obj.put("source", "by web");
//
//	                    return obj;
//	                }
//	            }
//	        }
//
//	        obj.put("loanNo", loanNo);
//	        obj.put("amount", "");
//	        obj.put("userId", "");
//	        obj.put("source", "not found");
//	    }
//	    catch(Exception e) 
//	    {
//	        e.printStackTrace();
//
//	        obj.put("loanNo", loanNo);
//	        obj.put("amount", "");
//	        obj.put("userId", "");
//	        obj.put("source", "error");
//	    }
//
//	    return obj;
//	}
  
  
  public JSONObject checkLoanPaymentSource(String loanNo, Date searchDate, Connection conn) {

	    DataBaseMethodTaheer dbt = new DataBaseMethodTaheer();
	    ArrayList<LoanInfo> loanEmiList = new ArrayList<>();

	    String loanType = dbt.loanTypeByLoanNo(loanNo);
	    loanEmiList = db.loanInstallment(loanType,loanNo,conn);

	    JSONObject obj = new JSONObject();

	    String emiNo = "";
	    String emiStatus = "";

	    SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
	    String searchDateStr = sdf.format(searchDate);

	   
	    for (LoanInfo emi : loanEmiList) {

	        if (emi.getInstallDate() != null &&
	            searchDateStr.equals(sdf.format(emi.getInstallDate()))) {

//	            emiNo = String.valueOf(emi.getEmiNo());
	        	emiNo = String.valueOf(emi.getSno());
	            emiStatus = emi.getStatus();
	            break;
	        }
	    }

	  
	    System.out.println("Emi No = " + emiNo);
	    System.out.println("Emi Status = " + emiStatus);
	    
	    
	    if (emiNo.isEmpty()) {
	        obj.put("loanNo", loanNo);
	        obj.put("status", "Payment Entry Not Found");
	        obj.put("source", "not found");
	        return obj;
	    }


	    if ("Due".equalsIgnoreCase(emiStatus)) {

	        obj.put("loanNo", loanNo);
	        obj.put("emiNo", emiNo);
	        obj.put("status", "Due");
	        obj.put("source", "");

	        return obj;
	    }

	    if (!"Paid".equalsIgnoreCase(emiStatus)) {

	        obj.put("loanNo", loanNo);
	        obj.put("emiNo", emiNo);
	        obj.put("status", "Payment Entry Not Found");
	        obj.put("source", "not found");

	        return obj;
	    }

	    try {

	        boolean appEntryFound = false;

	        String appAmount = "";
	        String appUserId = "";

	   
			String query = "select loan_no, amount, employee " + "from loan_payment_from_app " + "where loan_no=? "
					+ "and DATE(pay_date)=? " + "and status='active' " + "order by pay_date desc limit 1";

	        try (PreparedStatement ps = conn.prepareStatement(query)) {

	            ps.setString(1, loanNo);
	            ps.setDate(2, new java.sql.Date(searchDate.getTime()));

	            try (ResultSet rs = ps.executeQuery()) {

	                if (rs.next()) {
	                    appEntryFound = true;
	                    appAmount = rs.getString("amount");
	                    appUserId = rs.getString("employee");
	                }
	            }
	        }

	     
			query = "select loanNo, amount, user_id, emi_no " + "from loanpayment " + "where loanNo=? "
				+ "and emi_no=? ";

	        try (PreparedStatement ps = conn.prepareStatement(query)) {

	            ps.setString(1, loanNo);
//	            ps.setDate(2, new java.sql.Date(searchDate.getTime()));
	            ps.setString(2, emiNo);

	            try (ResultSet rs = ps.executeQuery()) {

	                if (rs.next()) {

	                    String paidEmiNo = rs.getString("emi_no");
	                   System.out.println("paidEmiNo = " + paidEmiNo);
	                    if (emiNo.equals(paidEmiNo)) {

	                        if (appEntryFound) {

	                            obj.put("loanNo", loanNo);
	                            obj.put("emiNo", emiNo);
	                            obj.put("amount", appAmount);
	                            obj.put("userId", appUserId);
	                            obj.put("empName", getEmployeeName(appUserId, conn));
	                            obj.put("status", "Paid by App");
	                            obj.put("source", "Paid by App");

	                        } else {

	                      
	                            String empUserId = rs.getString("user_id");

	                            obj.put("loanNo", rs.getString("loanNo"));
	                            obj.put("emiNo", paidEmiNo);
	                            obj.put("amount", rs.getString("amount"));
	                            obj.put("userId", empUserId);
	                            obj.put("empName", getEmployeeName(empUserId, conn));
	                            obj.put("status", "Paid by Web");
	                            obj.put("source", "Paid by Web");
	                        }

	                        return obj;
	                    }
	                    
	                }
	            }
	        }

	        obj.put("loanNo", loanNo);
	        obj.put("emiNo", emiNo);
	        obj.put("amount", "");
	        obj.put("userId", "");
	        obj.put("status", "Payment Entry Not Found");
	        obj.put("source", "not found");

	    } catch (Exception e) {

	        e.printStackTrace();

	        obj.put("loanNo", loanNo);
	        obj.put("emiNo", emiNo);
	        obj.put("amount", "");
	        obj.put("userId", "");
	        obj.put("status", "error");
	        obj.put("source", "error");
	    }

	    return obj;
	}
  

  public String getEmployeeName(String userId, Connection conn)
	{
	    String name = "";

	    try
	    {
	        String query = "select name from employee where uname=?";

	        try(PreparedStatement ps = conn.prepareStatement(query))
	        {
	            ps.setString(1, userId);

	            try(ResultSet rs = ps.executeQuery())
	            {
	                if(rs.next())
	                {
	                    name = rs.getString("name");
	                }
	            }
	        }
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	    }

	    return name;
	}
	
	
	public String latestPersonalLoanNo(Connection conn) {
		String loanNo = "";
        
		try {
			PreparedStatement ps = conn.prepareStatement(

					"select loanNo from secure_loan order by id desc limit 1");

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				loanNo = rs.getString("loanNo");
			}
		} 
		catch (Exception e) {
			e.printStackTrace();
		}

		return loanNo;
	}

	public String approvalDateByLoanNo(String loanNo, Connection conn) {
		String date = "";

		try {
			PreparedStatement ps = conn.prepareStatement("select approvalDate from secure_loan where loanNo=?");

			ps.setString(1, loanNo);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				date = new SimpleDateFormat("dd/MM/yyyy").format(rs.getDate("approvalDate"));

			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return date;
	}

	public String branchNameByLoanNo(String loanNo, Connection conn) {
		String branchName = "";

		try {
			PreparedStatement ps = conn.prepareStatement(

					"SELECT b.branch_name " + "FROM secure_loan v, branch_allocate_table b "
							+ "WHERE v.branch_id = b.branch_id " + "AND v.loanNo=?");

			ps.setString(1, loanNo);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				branchName = rs.getString("branch_name");

				System.out.println("branchName = " + branchName);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return branchName;
	}

	public UserInfo applicantDetailsByLoanNo(String loanNo, Connection conn) {
		UserInfo info = new UserInfo();

		try {
			PreparedStatement ps = conn.prepareStatement("SELECT s.* " + "FROM secure_loan l "
					+ "LEFT JOIN shareholder_info s ON l.shareholderno = s.shareholderno " + "WHERE l.loanNo = ?");

			ps.setString(1, loanNo);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				info.setName(rs.getString("first_applicantName"));
				info.setBank_accountNo(rs.getString("bank_accountNo"));
				info.setAddress(rs.getString("address"));
				info.setDob(rs.getDate("dob"));
				info.setSignPhoto(rs.getString("signPhoto"));
				info.setFather_husband_name(rs.getString("father_husband_name"));
				info.setMobile(rs.getString("mobile"));
				info.setUniqueId(rs.getString("unique_id"));
				info.setMonthlyIncome(rs.getString("monthly_income"));

				System.out.println("Sign Photo = " + info.getSignPhoto());

			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return info;
	}

	public String[] vehicleDetailsByLoanNo(String loanNo, Connection conn) {

		String data[] = new String[6];

		try {

			PreparedStatement ps = conn
					.prepareStatement("SELECT v_company, v_model, v_marketvalue,v_regno,v_chesisno,v_engineno "
							+ "FROM secure_loan_other " + "WHERE loan_no=?");

			ps.setString(1, loanNo);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				data[0] = rs.getString("v_company");
				data[1] = rs.getString("v_model");
				data[2] = rs.getString("v_marketvalue");
				data[3] = rs.getString("v_regno");
				data[4] = rs.getString("v_engineno");
				data[5] = rs.getString("v_chesisno");

			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return data;

	}

	public String[] secureLoanMainDataByLoanNo(String loanNo, Connection conn) {

		String data[] = new String[6];

		try {

			PreparedStatement ps = conn.prepareStatement(

					"SELECT loanAmount, interest, terms, emi,emi_due_date  " + "FROM secure_loan " + "WHERE loanNo=?"

			);

			ps.setString(1, loanNo);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				data[0] = rs.getString("loanAmount");
				data[1] = rs.getString("interest");
				data[2] = rs.getString("terms");
				data[3] = rs.getString("emi");
				data[4] = numberToWords((int) Double.parseDouble(data[3]));
				if (rs.getDate("emi_due_date") != null) {
					data[5] = new SimpleDateFormat("dd/MM/yyyy").format(rs.getDate("emi_due_date"));
				}
			}

		} catch (Exception e) {

			e.printStackTrace();
		}

		return data;

	}

	public String numberToWords(int num) {

		String[] ones = { "", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven",
				"Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen" };

		String[] tens = { "", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety" };

		if (num < 20)
			return ones[num];
		if (num < 100)
			return tens[num / 10] + " " + ones[num % 10];
		if (num < 1000)
			return ones[num / 100] + " Hundred " + numberToWords(num % 100);
		if (num < 100000)
			return numberToWords(num / 1000) + " Thousand " + numberToWords(num % 1000);

		return String.valueOf(num);

	}

	public String companyName(Connection conn) {

		String companyName = "";

		try {

			PreparedStatement ps = conn.prepareStatement(

					"SELECT name FROM firm_detail LIMIT 1"

			);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {

				companyName = rs.getString("name");
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return companyName;
	}

	public String companyAddress(Connection conn) {
		String address = "";

		try {
			PreparedStatement ps = conn.prepareStatement(

					"SELECT address1,address2,address3,address4 " + "FROM firm_detail LIMIT 1"

			);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				address = rs.getString("address1") + ", " + rs.getString("address2") + ", " + rs.getString("address3")
						+ ", " + rs.getString("address4");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return address;
	}

	public String companyAddress1(Connection conn) {

		String companyName = "";

		try {

			PreparedStatement ps = conn.prepareStatement(

					"SELECT address1 FROM firm_detail LIMIT 1"

			);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {

				companyName = rs.getString("address1");
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return companyName;
	}

	public double preEmiByLoanNo(String loanNo, Connection conn) {

		double preEmi = 0.0;

		try {

			DataBaseMethods dbm = new DataBaseMethods();

			preEmi = dbm.preEmiChargeCalculationMethod(loanNo, "secure_loan", conn);

		} catch (Exception e) {
			e.printStackTrace();

		}

		return preEmi;

	}

	public String downPaymentByLoanNo(String loanNo, Connection conn) {

		String downPayment = " ";

		try {

			PreparedStatement ps = conn.prepareStatement(

					"SELECT mob_down_payment " + "FROM secure_loan " + "WHERE loanNo=?"

			);

			ps.setString(1, loanNo);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {

				downPayment = rs.getString("mob_down_payment");

			}

		} catch (Exception e) {

			e.printStackTrace();
		}

		return downPayment;

	}

	public String disbursementAmountByLoanNo(String loanNo, Connection conn) {

		String amount = "0";

		try {

			PreparedStatement ps = conn.prepareStatement(
					"SELECT amount " + "FROM debit_loan " + "WHERE loanNo=? " + "ORDER BY id DESC LIMIT 1");

			ps.setString(1, loanNo);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				amount = rs.getString("amount");

			}
		} catch (Exception e) {

			e.printStackTrace();
		}

		return amount;
	}

	public String dealerNameByLoanNo(String loanNo, Connection conn) {
		String dealerName = "";

		try {
			PreparedStatement ps = conn.prepareStatement(

					"SELECT d.name " + "FROM secure_loan s " + "INNER JOIN dealers d ON s.mob_dealer = d.id "
							+ "WHERE s.loanNo=?"

			);

			ps.setString(1, loanNo);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				dealerName = rs.getString("name");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return dealerName;
	}

	public String dealerAccountNoByLoanNo(String loanNo, Connection conn) {
		String accountNo = "";

		try {
			PreparedStatement ps = conn.prepareStatement(

					"SELECT d.account_number " + "FROM secure_loan s " + "INNER JOIN dealers d ON s.mob_dealer = d.id "
							+ "WHERE s.loanNo=?"

			);

			ps.setString(1, loanNo);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				accountNo = rs.getString("account_number");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return accountNo;
	}
	
	
	public Date loanDisburseDateByLoanNo(String loanNo, Connection conn) throws SQLException
	{
	    Date date = null;

	    String query = "select date from debit_loan where loanNo=? and status='cleared' order by id limit 1";

	    try(PreparedStatement ps = conn.prepareStatement(query))
	    {
	        ps.setString(1, loanNo);

	        try(ResultSet rs = ps.executeQuery())
	        {
	            if(rs.next())
	            {
	                date = rs.getDate("date");
	            }
	        }
	    }

	    return date;
	}
	
	

}