package main_package;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.io.Serializable;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.NClob;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.RowId;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.format.TextStyle;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;

import javax.faces.context.FacesContext;
import javax.faces.model.SelectItem;
import javax.print.DocFlavor.READER;
import javax.servlet.http.HttpSession;

import json_package.FinanaceCardInfo;
import json_package.financeCard;

public class DbYashwant  implements Serializable{
	
	
	String onlineFileViewPath="";
	String onlineFileUploadPath="";
	DecimalFormat df=new DecimalFormat("##.##");
	SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd");
	SimpleDateFormat sdfForDb=new SimpleDateFormat("yyyy-MM-dd"); 
	DataBaseMethods dbm= new DataBaseMethods();
	DataBaseMethods obj=new DataBaseMethods();
	DataBaseMethodsRaviK dbrk = new DataBaseMethodsRaviK();
	
	public int savePfEsicSettings(double employeePfRate, double employerPfRate, double employeeEsicRate, double employerEsicRate, double esicLimit, double pfWageLimit, boolean isPfApplicable, boolean isPfLimitApply, int branchId, double basicPer, double hraPer, double specialPer) {

		  int i=0;
		    Connection conn = null;
		    PreparedStatement ps = null;

		    try {

		        conn = DataBaseConnection.JAVAConnection();

		        String query = "INSERT INTO pf_esic_detail "
		                + "(employee_pf_rate, employer_pf_rate, employee_esic_rate, employer_esic_rate, "
		                + "esic_limit, pf_wage_limit, is_pf_applicable, is_pf_limit_apply, branch_id, status,basic_per,hra_per,special_per) "
		                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		        ps = conn.prepareStatement(query);

		        ps.setDouble(1, employeePfRate);
		        ps.setDouble(2, employerPfRate);
		        ps.setDouble(3, employeeEsicRate);
		        ps.setDouble(4, employerEsicRate);

		        ps.setDouble(5, esicLimit);
		        ps.setDouble(6, pfWageLimit);

		        ps.setBoolean(7, isPfApplicable);
		        ps.setBoolean(8, isPfLimitApply);

		        ps.setInt(9, branchId);
		        ps.setString(10, "Active");
		        ps.setDouble(11, basicPer);
		        ps.setDouble(12, hraPer);
		        ps.setDouble(13, specialPer);

		         i = ps.executeUpdate();

//		        if (i > 0) {
//		            FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_INFO,
//		                    "Success", "PF/ESIC Settings Saved Successfully");
//		            FacesContext.getCurrentInstance().addMessage(null, msg);
//		        }

		    } catch (Exception e) {
		        e.printStackTrace();

		    } finally {

		        try {

		            if (ps != null) {
		                ps.close();
		            }

		            if (conn != null) {
		                conn.close();
		            }

		        } catch (Exception e) {
		            e.printStackTrace();
		        }
		    }
			return i;
		}
	
	public ArrayList<pfEsicModel> viewPfEsic()
	  {
		  HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
			int branchId = (int) ss.getAttribute("branchId");
		  
		  int sNo =1;
		 ArrayList<pfEsicModel> list =new ArrayList<>();
		  
		  try(Connection conn = DataBaseConnection.JAVAConnection()) {
			  
			  String qq = "select * from  pf_esic_detail where status = 'Active' And branch_id= '"+branchId+"'";
			  
			  try(PreparedStatement st = conn.prepareStatement(qq)) 
			  {  
				  ResultSet rr = st.executeQuery();
				  
				  while(rr.next())
				  {
					  pfEsicModel ssf =  new pfEsicModel();
					  
//					  System.out.println(rr.getInt("employee_pf_rate"));
					  ssf.setsNo(sNo++);
					  ssf.setId(rr.getInt("id"));
					  ssf.setEmployeePfRate(rr.getDouble("employee_pf_rate"));
					  ssf.setEmployerPfRate(rr.getDouble("employer_pf_rate"));
					  ssf.setEmployeeEsicRate(rr.getDouble("employee_esic_rate"));
					  ssf.setEmployerEsicRate(rr.getDouble("employer_esic_rate"));
					  
//					  System.out.println(rr.getDouble("employer_esic_rate")+"wwwww");
					  ssf.setEsicLimit(rr.getDouble("esic_limit"));
					  ssf.setPfWageLimit(rr.getDouble("pf_wage_limit"));
					  ssf.setPfApplicable(rr.getBoolean("is_pf_applicable"));
					  ssf.setPfLimitApply(rr.getBoolean("is_pf_limit_apply"));
					  ssf.setBasicPer(rr.getDouble("basic_per"));
					  ssf.setHraPer(rr.getDouble("hra_per"));
					  ssf.setSpecialPer(rr.getDouble("special_per"));
					  
					  
					  list.add(ssf);
				  }
				
				  
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			  
			  
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return list;
	  }
	  
	  
	  
	  
	  
	  public int deletePfEsic( int id)
	  {
		  int i=0;
		  try(Connection conn = DataBaseConnection.JAVAConnection()) {
			  
			  String qq ="update pf_esic_detail set status = ? where id = ? ";
			  
			  try(PreparedStatement st = conn.prepareStatement(qq)) {
				  
				  st.setString(1, "inActive");
				  st.setInt(2, id);
				  
				 i= st.executeUpdate();
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return i;
	  }

	  
	  
	  public int editPfEsic(double employeePfRate, double employerPfRate, double employeeEsicRate, double employerEsicRate, double esicLimit, double pfWageLimit, boolean isPfApplicable, boolean isPfLimitApply, double employeeEsicRate2, int j, double basicPer, double hraPer, double specialPer)
	  {
		  int i =0;
		  HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
			int branchId1 = (int) ss.getAttribute("branchId");
		  try(Connection conn = DataBaseConnection.JAVAConnection()) {
			  
			  String qq = "update pf_esic_detail set employee_pf_rate = ?, employer_pf_rate = ?, employee_esic_rate = ?, employer_esic_rate = ?,"
			  		+ "esic_limit = ? , pf_wage_limit = ? , is_pf_applicable = ?, is_pf_limit_apply = ? , branch_id = ? , basic_per = ? , hra_per = ? , special_per = ? where id = ? ";
			  
//			  System.out.println(qq);
			  
			  try(PreparedStatement st = conn.prepareStatement(qq)) {
				  
				  st.setDouble(1, employeePfRate);
				  st.setDouble(2, employerPfRate);
				  st.setDouble(3, employeeEsicRate);
				  st.setDouble(4, employerEsicRate);
				  st.setDouble(5, esicLimit);
				  st.setDouble(6, pfWageLimit);
				  st.setBoolean(7, isPfApplicable);
				  st.setBoolean(8, isPfLimitApply);
				  st.setInt(9, branchId1);
				  st.setDouble(10, basicPer);
				  st.setDouble(11, hraPer);
				  st.setDouble(12, specialPer);
				  st.setInt(13, j);
				  
				  i= st.executeUpdate();
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return i;
	  }
	
	public int addDealer( Connection conn, String name, String mobile, String address, String email, String gst, String pan, String aadhar, String accountNumber, String ifscCode, String bankCode)
	{
		int i=0;
		  String qq =
			        "INSERT INTO dealers " +
			        "(name, mobile, address, email, gst, aadhar_no, pan_no, " +
			        "account_number, ifsc_code, bank_code, status) " +
			        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

			    try (PreparedStatement st = conn.prepareStatement(qq)) {

			        st.setString(1, name);
			        st.setString(2, mobile);
			        st.setString(3, address);
			        st.setString(4, email);
			        st.setString(5, gst);
			        st.setString(6, aadhar);
			        st.setString(7, pan);
			        st.setString(8, accountNumber);
			        st.setString(9, ifscCode);
			        st.setString(10, bankCode);
			        st.setString(11, "active");

			        i = st.executeUpdate();

			    } catch (Exception e) {
			        e.printStackTrace();
			    }

			    return i;
	}
	
	public boolean checkEmployeeCibilStatus(String userid)
	{
		boolean cibilCheck =false;
		
		try(Connection conn = DataBaseConnection.JAVAConnection()) {
			
			Statement st=conn.createStatement();
			
			String qq="select cibil_check from employee where uname='"+userid+"'";
			
			ResultSet rr=st.executeQuery(qq);
			if(rr.next())
			{
				cibilCheck = rr.getBoolean("cibil_check");
			}
			
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		
		return cibilCheck;
		
	}
	
	public int addDebitLoan(int branchId,String shareHolderNo, String loanCategory,
			String loanNo, double amount, String paymentMode, String bankName,
			String bankBranch, String chequeNo, Date chequeDate, Date date)
	{
		int i=0;
		String status="";
		Timestamp chdate = null;
		if (chequeDate != null) 
		{
			chdate = new Timestamp(chequeDate.getTime());
		}
		
		if(paymentMode.equalsIgnoreCase("Cash") || paymentMode.equalsIgnoreCase("transfer"))
		{
			status="cleared";
			bankName="N/A";
		}
		else 
		{
			if (chequeDate==null) 
			{
				chdate = new Timestamp(new Date().getTime());
			}
			status="cleared";
		}
		
		String query="insert into debit_loan(shareholderno, loanType, loanNo, amount, paymentMode, bankName, bankBranch,"
				+ " chequeNo, chequeDate, status,date, branch_id) values (?,?,?,?,?,?,?,?,?,?,?,?)";
        try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			PreparedStatement st=conn.prepareStatement(query);
			st.setString(1, shareHolderNo);
			st.setString(2, loanCategory);
			st.setString(3, loanNo);
			st.setDouble(4, amount);
			st.setString(5, paymentMode);
			st.setString(6, bankName);
			st.setString(7, bankBranch);
			st.setString(8, chequeNo);
			st.setTimestamp(9,chdate);
			st.setString(10, status);
			st.setTimestamp(11,new Timestamp(date.getTime()));
			st.setInt(12, branchId);

			//System.out.println(st);
			i=st.executeUpdate();
			
			if(i>0){
			dataInsertInQueryTable(String.valueOf(st), conn);
		}}
		catch (Exception e)
		{
			  e.printStackTrace();fileCreation(e.getMessage());
		}
		return i;
	}
	
	public void dataInsertInQueryTable(String query, Connection conn) 
	{
		String userId=userId();
		Timestamp tt=new Timestamp(new Date().getTime());
		tt.setNanos(0);
		
		String qq="insert into query_table(query_time, query, user_id) values(?,?,?)";
		try 
		{
			PreparedStatement st=conn.prepareStatement(qq);
			st.setTimestamp(1, tt);
			st.setString(2, query);
			st.setString(3, userId);
			st.executeUpdate();
			
		} 
		catch (SQLException e) 
		{
			 e.printStackTrace();fileCreation(e.getMessage());
		}
	}
	
	// File/Document online path from db 
	public void filePathDetail(Connection conn)  
	{
		try
		{
			Statement st=conn.createStatement();
			String qq="select * from firm_detail limit 1";
			ResultSet rs=st.executeQuery(qq);
			if(rs.next())
			{
				onlineFileUploadPath=rs.getString("file_upload_path");       
				onlineFileViewPath=rs.getString("file_view_path");
			}
		}
		catch (Exception e)
		{
			 e.printStackTrace();fileCreation(e.getMessage());
		} 
	}
	public void fileCreation(String msg) 
	{
		if(msg==null || msg.trim().equalsIgnoreCase(""))
		{
			msg="";
		}
		else
		{
			Date dt=new Date();
			String time=new SimpleDateFormat("dd/MM/yyyy hh:mm:ss a").format(dt);
			msg=msg+time;
		}
		
		
		
		
		/*ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
		String realPath = ctx.getRealPath("/");
			String dirPath=realPath;
		*/  //Offline
		
		String folderName=fileUploadPathDetail();
		String dirPath=folderName;         //online
	    BufferedWriter writer = null;
        try
        {
            boolean dirFlag = false;
            File stockDir = new File(dirPath);
            try 
            {
                dirFlag = stockDir.mkdirs();
                if (dirFlag)
                {
                	File myfile=new File(dirPath+"/ErrorLogDetail.txt");
                	try
                	{
                      myfile.createNewFile();
                	}
                	catch (IOException ss)
                	{
                     ss.printStackTrace();
                	}
                }
             
            } 
            catch (SecurityException Se) 
            {
                 Se.printStackTrace();fileCreation(Se.getMessage());
                 String valie=String.valueOf(Se.getMessage());
            }
            String worktext="Mr. Chetan Garg";
            File myfile=new File(dirPath+"/ErrorLogDetail.txt");
            writer = new BufferedWriter(new FileWriter(myfile, true));
            writer.newLine();
            writer.write(msg);
         } 
         catch (Exception es) 
         {
             es.printStackTrace();
         } 
         finally 
         {
             try 
             {
                 writer.close();
             } 
             catch (Exception s) 
             {
                 s.printStackTrace();
             }
         }
     }

	public String fileUploadPathDetail()
	{
		try(Connection con=DataBaseConnection.JAVAConnection();) 
		{
			Statement st=con.createStatement();
			String qq="select * from firm_detail limit 1";
			ResultSet rs=st.executeQuery(qq);
			if(rs.next())
			{
				return rs.getString("file_upload_path");
			}
		}
		catch (Exception e)
		{
			 e.printStackTrace();fileCreation(e.getMessage());
		} 
		return "";
	}
	
	// Login User-Id
		public String userId()
		{
			HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
			String userId="";
			if(ss!=null)
			{
				 userId=(String) ss.getAttribute("username");
			}
			return userId;
		}
	
		
		public String fetchDisbursalDate(String LoanNo)
		{
			String date ="";
			try(Connection conn =DataBaseConnection.JAVAConnection()) {
				
				Statement st =conn.createStatement();
				String qq="select date from debit_loan where loanNo='"+LoanNo+"'";
				ResultSet rr=st.executeQuery(qq);
				
				while(rr.next())
				{
					date =rr.getString("date");
				}
				
			} catch (Exception e) {
				// TODO: handle exception
			}
			
			return date;
			
		}
		
		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 int add_employee_salary(Connection conn, String employeeCode, String employeeName, String funtion, String desination, String pfNumber, String uanNo, String state, String esicNo, String bankName, int totalDays, String bankAccountNo,
				int workingDays, String dateOfJoining, int lopDays, double basicMonth, double houseRentAllowance, double specialAllowance, double otherAllowance, double grossSalary, double employeeEsicContribution, double employeePfContribution, double totalFixedPay, 
				double totalPfDeduction, double totalEsicDeduction, double netCashInHand, String paymentMode, Timestamp chequeDate, String chequeNo, String fromBank, String description, Timestamp payDate, String selectedYear, String selectedMonth, int branchId, String panNumber, String designation,
				double  incentive,double  arrearIncentive,double  arrearSalary, double  grandTotal,double  otherDeduction, Date salaryFromDate, Date salaryToDate,double fuelAmount)
		{
			Timestamp from = new Timestamp(salaryFromDate.getTime());
			Timestamp to = new Timestamp(salaryToDate.getTime());
			
			int i=0;
			
			String qq ="insert into emp_salary (employee_code, employee_name, funtion, designation, pf_number, uan_no, state, esic_no, bank_name, total_days, bank_acc_no, working_days, doj, lop_days, basic, house_rent_allowance, special_allowance, other_allowance, gross_salary, esic_con, pf_con, total_fixed_pay, total_pf_deduction,"
					+ " total_esic_deduction, net_cash_in_hand, status, salary_month, salary_year, salary_create_date,payment_mode,pay_mode_no,pay_mode_date,form_bank_name,remark,branch_id,pan_no,incentive,arrear_incentive,arrear_salary,grand_total,other_deduction,salary_from_date,salary_to_date,fuel_amount)"
					+ "values(? ,? ,? ,? ,?, ? ,? ,? ,? ,?, ? ,? ,? ,? ,?, ? ,? ,? ,? ,?, ? ,? ,? ,? ,?, ? ,? ,? ,?, ?, ? ,? ,? ,?, ?, ?,? ,? ,?, ?, ?, ?, ?,?)";
			try(PreparedStatement st = conn.prepareStatement(qq))
			{
				
				st.setString(1, employeeCode);
				st.setString(2, employeeName);
				st.setString(3, "JLG BL");
				st.setString(4, designation);
				st.setString(5, pfNumber);
				st.setString(6, uanNo);
				st.setString(7, state);
				st.setString(8, esicNo);
				st.setString(9, bankName);
				st.setInt(10, totalDays);
				st.setString(11, bankAccountNo);
				st.setInt(12, workingDays);
				st.setString(13, dateOfJoining);
				st.setInt(14, lopDays);
				st.setDouble(15, basicMonth);
				st.setDouble(16, houseRentAllowance);
				st.setDouble(17, specialAllowance);
				st.setDouble(18, otherAllowance);
				st.setDouble(19, grossSalary);
				st.setDouble(20, employeeEsicContribution);
				st.setDouble(21, employeePfContribution);
				st.setDouble(22, totalFixedPay);
				st.setDouble(23, totalPfDeduction);
				st.setDouble(24, totalEsicDeduction);
				st.setDouble(25, netCashInHand);
				st.setString(26, "unpaid");
				st.setString(27, selectedMonth);
				st.setString(28, selectedYear);
				st.setTimestamp(29, payDate);
				st.setString(30, paymentMode);
				st.setString(31, chequeNo);
				st.setTimestamp(32 ,chequeDate);
				st.setString(33, fromBank);
				st.setString(34, description);
				st.setInt(35, branchId);
				st.setString(36, panNumber );
				st.setDouble(37, incentive);
				st.setDouble(38, arrearIncentive);
				st.setDouble(39, arrearSalary);
				st.setDouble(40, grandTotal);
				st.setDouble(41, otherDeduction);
				st.setTimestamp(42, from);
				st.setTimestamp(43, to);
				st.setDouble(44,fuelAmount);
			   
				
				i=st.executeUpdate();
				
				
			} catch (Exception e) {
				
				e.printStackTrace();
				
			}
			
			return i;
			
		}
		
		
		public int checkSalaryStatus(Connection conn, String employeeCode, String selectedMonth, String selectedYear)
		{
			int i  = 0;
			String query=" select status from emp_salary where employee_code='"+employeeCode+"' and salary_month='"+selectedMonth+"' and salary_year = '"+selectedYear+"' and salary_status='active'";
			
			
			
			try(PreparedStatement st = conn.prepareStatement(query)) {
				
				ResultSet rr =st.executeQuery();
				if(rr.next())
				{
					i=1;
					
					
				}
					
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
			return i;
			
		}
		
		
		public ArrayList<SalaryCreationInfo>manageSalaryList( Connection conn, String selectedMonth, String selectedYear, String selectedName, int branchId)
		{	
			System.out.println("EMP NAME IS:"+selectedName);
			int i=1;
			ArrayList<SalaryCreationInfo>list=new ArrayList<>();
		
			String query="";
			
			DataBaseMethods dbs =new DataBaseMethods();
			DataBaseMethodsRaviK dbr = new DataBaseMethodsRaviK();
			
			String employeeDetail = dbr.getEmployeeDetailByEmpName(conn, selectedName);
			 query="select * from emp_salary where salary_month='"+selectedMonth+"' AND salary_year='"+selectedYear+"' ";

			 if(branchId != -1)
			{
				query+= " AND branch_id='"+branchId+"' ";					
			}
			 
			if(!selectedName.equalsIgnoreCase("-1"))
			{
				query+= " AND employee_name='"+selectedName+"' ";
			}
			
			query += "  and salary_status='active' ";
			
			System.out.println("emp_salary : " + query);
			
			try(PreparedStatement st =conn.prepareStatement(query)) {
				
				ResultSet rr = st.executeQuery();
				while(rr.next())
				{
					SalaryCreationInfo ss = new SalaryCreationInfo();
					
					ss.setsNo(i++);
					ss.setId(rr.getInt("id"));
					ss.setEmployeeCode(rr.getString("employee_code"));
					ss.setLableEmpCode(manageEmployeeId(rr.getString("employee_code")));
					//ss.setEmployeeName(fetchEmployeeDetail(rr.getString("employee_name"), "employeeName"));
				    ss.setEmployeeName(rr.getString("employee_name"));
				    if(employeeDetail != null && !employeeDetail.isEmpty())
				    {
				        String[] detail = employeeDetail.split("#", -1);
                        String dept=  dbs.hrmBasicNameById(detail[0], conn);
				        ss.setDepartment(dept);
				        ss.setCity(detail[1]);
				       // ss.setDoj(detail[2]);
				    }	
				    ss.setFuntion(rr.getString("funtion"));
					ss.setDesination(rr.getString("designation"));
					ss.setPfNumber(rr.getString("pf_number"));
					ss.setUanNo(rr.getString("uan_no"));
					ss.setState(rr.getString("state"));
					ss.setEsicNo(rr.getString("esic_no"));
					ss.setBankName(rr.getString("bank_name"));
					ss.setTotalDays(rr.getInt("total_days"));
					ss.setBankAccountNo(rr.getString("bank_acc_no"));
					ss.setWorkingDays(rr.getInt("working_days"));
					
					String doj = rr.getString("doj");
					System.out.println("INPUT"+doj);
					if (doj != null && !doj.isEmpty()) {
					    try {
					        SimpleDateFormat inputFormat = new SimpleDateFormat("yyyy-MM-dd");
					        SimpleDateFormat outputFormat = new SimpleDateFormat("dd/MM/yyyy");

					        Date date = inputFormat.parse(doj);

					        ss.setDateOfJoining(outputFormat.format(date));

					    } catch (ParseException e) {
					        e.printStackTrace();
					    }
					}
					System.out.println("OUTPUT"+ss.getDateOfJoining());

					ss.setLopDays(rr.getString("lop_days"));
					ss.setBasicMonth(rr.getInt("basic"));
					ss.setHouseRentAllowance(rr.getInt("house_rent_allowance"));
					ss.setSpecialAllowance(rr.getInt("special_allowance"));
					ss.setOtherAllowance(rr.getInt("other_allowance"));
					ss.setGrossSalary(rr.getInt("grand_total"));
					ss.setEmployeeEsicContribution(rr.getInt("esic_con"));
					ss.setEmployeePfContribution(rr.getInt("pf_con"));
					ss.setTotalFixedPay(rr.getInt("total_fixed_pay"));
					ss.setTotalPfDeduction(rr.getInt("total_pf_deduction"));
					ss.setTotalEsicDeduction(rr.getInt("total_esic_deduction"));
					ss.setNetCashInHand(rr.getInt("net_cash_in_hand"));
					ss.setSalaryMonth(rr.getString("salary_month"));
					ss.setSalaryYear(rr.getString("salary_year"));
					ss.setArrear(rr.getInt("arrear_salary"));
					ss.setArrearIncentive(rr.getInt("arrear_incentive"));
					ss.setIncentive(rr.getInt("incentive"));
					ss.setOthersDeduction(rr.getInt("other_deduction"));
					ss.setFuelAmount(rr.getInt("fuel_amount"));
					
					
					
					String dateStr = rr.getString("salary_pay_date"); // e.g., "2025-07-10 00:00:00"

					if (dateStr != null && !dateStr.trim().isEmpty()) {
					    // Match input format exactly
					    DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

					    // Parse to LocalDateTime
					    LocalDateTime dateTime = LocalDateTime.parse(dateStr, inputFormatter);

					    // Extract only the date part
					    LocalDate date = dateTime.toLocalDate();

					    // Format to desired output, e.g., "dd-MM-yyyy"
					    DateTimeFormatter outputFormatter = DateTimeFormatter.ofPattern("dd-MM-yyyy");
					    String formattedDate = date.format(outputFormatter);

					    ss.setPayDate(formattedDate);
					} else {
					    ss.setPayDate(null); // or "N/A"
					}

					ss.setPaymentMode(rr.getString("payment_mode"));
				
					
					String dateStr1 =rr.getString("pay_mode_date"); // e.g., "2025-07-10 00:00:00"

					if (dateStr1 != null && !dateStr1.trim().isEmpty()) {
					    // Match input format exactly
					    DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

					    // Parse to LocalDateTime
					    LocalDateTime dateTime = LocalDateTime.parse(dateStr, inputFormatter);

					    // Extract only the date part
					    LocalDate date = dateTime.toLocalDate();

					    // Format to desired output, e.g., "dd-MM-yyyy"
					    DateTimeFormatter outputFormatter = DateTimeFormatter.ofPattern("dd-MM-yyyy");
					    String formattedDate = date.format(outputFormatter);

					    ss.setChequeDate(formattedDate);
					} else {
					    ss.setChequeDate(null); // or "N/A"
					}
					
					ss.setChequeNo(rr.getString("pay_mode_no"));
					ss.setFromBank(rr.getString("form_bank_name"));
					ss.setDescription(rr.getString("remark"));
					ss.setPanNumber(rr.getString("pan_no"));
					ss.setStatus(rr.getString("status"));
					String status=rr.getString("status");
						if(!status.equals("paid"))
						{
							ss.setPayModeRen(true);
							ss.setPrintRen(false);
						}
						else {
							
							ss.setPayModeRen(false);
							ss.setPrintRen(true);
						}
						
					ss.setBranch(dbs.branchNameByBranchId(rr.getInt("branch_id"), conn));
					
					if ("paid".equalsIgnoreCase(ss.getStatus())) 
					{
						ss.setRenDelete(true);
					}
					else 
					{
						ss.setRenDelete(false);
					}
					
					
					list.add(ss);
					
					
					
			
					
					
					
					
					
				}
					
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
			return list;
			
		}
		
		public int addSalaryPayout(String selectedName, double amount,
				String type, Date payDate, String paymentMode, String chequeNo,
				Date chequeDate, String bankName, String bankBranch,
				String description,int branchId)
		{
			Timestamp tt=new Timestamp(payDate.getTime());
			String status="";
			if(paymentMode.equalsIgnoreCase("Cash") || paymentMode.equalsIgnoreCase("Transfer"))
			{
				status="cleared";
				bankName="N/A";
			}
			else
			{
				status="uncleared";
			}
			
			Timestamp cDate=null;
			if(chequeDate!=null)
			{
				cDate=new Timestamp(chequeDate.getTime());
			}
			
			String query="insert into salary_ledger(empId, amount, type, payDate, payment_mode, chq_dd_no, chq_dd_date, bank_name,"
					+ " bank_branch, chequeStatus, description, branch_id) "
					+ "values(?,?,?,?,?,?,?,?,?,?,?,?)";
			
			try(Connection conn=DataBaseConnection.JAVAConnection())
			{
				PreparedStatement st=conn.prepareStatement(query);
				st.setString(1, selectedName);
				st.setDouble(2, amount);
				st.setString(3, type);
				st.setTimestamp(4,new Timestamp(payDate.getTime()));
				st.setString(5, paymentMode);
				st.setString(6, chequeNo);
				st.setTimestamp(7, cDate);
				st.setString(8,bankName);
				st.setString(9,bankBranch);
				st.setString(10, "cleared");
				st.setString(11, description);
				st.setInt(12, branchId);
				
				int i=st.executeUpdate();
				if(i>0){
				dataInsertInQueryTable(String.valueOf(st), conn);
				return i;
			}}
			catch(Exception e)
			{
				 e.printStackTrace();fileCreation(e.getMessage());
			}
			return 0;
		}
		
		
		public int checkDsaCodeDulicate(String code ,Connection conn) throws SQLException
		{
			int i =0;
			Statement st = conn.createStatement();
			String qq = "select id from dsa where code='"+code+"'";
			
			ResultSet rr =st.executeQuery(qq);
			if(rr.next())
			{
				i=1;
			}
			
			
			
			return i;
			
			
		}
		
		public ArrayList<SelectItem> secureLoanSecurityList( Connection conn)
		{
			ArrayList<SelectItem> list =new ArrayList<>();
			
			String qq ="select * from secure_type_table";
			try(PreparedStatement st =conn.prepareStatement(qq)) {
				
				ResultSet rr =st.executeQuery();
				
				while(rr.next())
				{
					SelectItem ss =new SelectItem();
					
					ss.setLabel(rr.getString("security_type"));
					ss.setValue(rr.getInt("id"));
					
					list.add(ss);
					
				}
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
			return list;
			
		}
		
		public ArrayList<LoanInfo> fetchGoldDeatail(Connection conn, String loanNo)
		{ 
			ArrayList<LoanInfo>list=new ArrayList<>();
			String qq = "select * from secure_loan_other where loan_no ='"+loanNo+"'";
			int i=1;
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				ResultSet rr =st.executeQuery();
				while(rr.next())
				{
					LoanInfo ss =new LoanInfo();
					ss.setgItemName(rr.getString("g_itemname"));
					ss.setgQty(rr.getString("g_qty"));
					ss.setgGrossWeight(rr.getString("g_grossweight"));
					ss.setgNetWeight(rr.getString("g_netweight"));
					ss.setgPurityWeight(rr.getString("g_purityweight"));
					ss.setgMarketValue(rr.getString("g_marketvalue"));
					ss.setGoldImagePath(rr.getString("gold_image"));
					ss.setpItemName(rr.getString("p_itemname"));
					ss.setPropertyDetail(rr.getString("property_detail"));
					ss.setpArea(rr.getString("p_area"));
					ss.setpMarketValue(rr.getString("p_marketvalue"));
					ss.setpRemark(rr.getString("p_remark"));
					ss.setSno(i++);
					
					list.add(ss);
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}
			return list;
		}

		
		public void salaryPayoutUpdate(Connection conn, String string, int i, String string2, Date payDate, String paymentMode, String chequeNo, Date chequeDate, String fromBank, String bankBranch, String description, int id)
		{
			Timestamp tt = new Timestamp(payDate.getTime());
			Timestamp tf= null;
			if(chequeDate!=null)
			{
				 tf = new Timestamp(chequeDate.getTime());
			}
			
			
			String qq="update emp_salary set salary_pay_date = ?, payment_mode = ?, pay_mode_no = ?, pay_mode_date = ?, form_bank_name = ?, transfer_to_bank = ?, remark = ?, status = ?"
					+ "where id = ?";
			
			
				try(PreparedStatement st = conn.prepareStatement(qq)) {
					
					st.setTimestamp(1, tt);
					st.setString(2, paymentMode);
					st.setString(3, chequeNo);
					st.setTimestamp(4, tf);
					st.setString(5, fromBank);
					st.setString(6, "");
					st.setString(7, description);
					st.setString(8, "paid");
					st.setInt(9, id);
					
					st.executeUpdate();
					
					
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
		}
		
		
		
		public void updateProcessingChargeAndInsuranceFeeInIncomeTable(String loanNo, double processingCharge, String string,Connection conn)
		{
			try
			{
				Statement st=conn.createStatement();
				String qq="update income_table set amount='"+processingCharge+"' where loan_no='"+loanNo+"' and item='"+string+"' ";
				int i=	st.executeUpdate(qq);
				if(i>0)
				{	
					dataInsertInQueryTable(qq, conn);
				}
			}
			catch(Exception e)
			{
				 e.printStackTrace();fileCreation(e.getMessage());
			}
		}
		
		
		public int updateIncomeTableSelectedPendingEntryStatusForOnline(String itemName, String entryStatus, String loanNo, Connection conn, Date releaseDate, String paymentMode, String chequeNo, Date chequeDate, String bankName) 
		{
			Timestamp tt=null;
			if(releaseDate!=null)
			{
				tt=new Timestamp(releaseDate.getTime());
			}
			Timestamp tf=new Timestamp(chequeDate.getTime());
			
			try
			{
				String qq="update income_table set status=?, income_date=?, payment_mode=?, chq_dd_no=?, chq_dd_date=? ,bank_name=? where item='"+itemName+"' and loan_no='"+loanNo+"' and status='pending' ";
				
				PreparedStatement st1 = conn.prepareStatement(qq);
				st1.setString(1, entryStatus);
				st1.setTimestamp(2, tt);
				st1.setString(3, paymentMode);
				st1.setString(4, chequeNo);
				st1.setTimestamp(5, tf);
				st1.setString(6, bankName);
				int i=st1.executeUpdate();
				return i;
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
			return 0;
		}

		
		public ArrayList<LoginFeeInfo> printLoginReceipt(String loanNo, Connection conn)
		{
			ArrayList<LoginFeeInfo> list=new ArrayList<>();
			String qq ="select * from income_table where loan_no='"+loanNo+"'";
			
			try(PreparedStatement st=conn.prepareStatement(qq)) {
				
				ResultSet rr =st.executeQuery();
				while(rr.next())
				{
					LoginFeeInfo ll=new LoginFeeInfo();
					ll.setFeeDate(rr.getString("income_date"));
					ll.setAmount(rr.getDouble("amount"));
					ll.setLoanType(rr.getString("loan_type"));
					ll.setPaymentMode(rr.getString("payment_mode"));
					ll.setReceiptNo(rr.getString("loan_no"));
					ll.setTransactionDate(rr.getString("chq_dd_date"));
					ll.setTransactionDate(rr.getString("chq_dd_date"));
					
					list.add(ll);
				}
				
			} catch (Exception e) {
				// TODO: handle exception
			}
			return list;
		}
		
		public ArrayList<SelectItem> personalLoanNoList(Connection conn)
		{
			ArrayList<SelectItem>list=new ArrayList<>();
			String qq="select loanNo,shareholderno from personalloan";
			
			try(PreparedStatement st =conn.prepareStatement(qq)) {
				
				ResultSet rr =st.executeQuery();
				while(rr.next())
				{
					SelectItem ss =new SelectItem();
					ss.setLabel(rr.getString("loanNo")+"-"+findloanHolderName(conn, rr.getString("shareholderno")));
					ss.setValue(rr.getString("loanNo"));
					
					list.add(ss);
						
				}
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
			return null;
			
		}
		
		public String findloanHolderName(Connection conn, String shareholderno)
		{
			String name="";
			String qq =" select first_applicantName,father_husband_name from shareholder_info where shareholderno='"+shareholderno+"'";
			
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				ResultSet rr = st.executeQuery();
				
				while(rr.next())
				{
					name= rr.getString("first_applicantName")+"-"+rr.getString("father_husband_name");
				}
				
				
				
				
				
			} catch (Exception e) {
				// TODO: handle exception\
				e.printStackTrace();
			}
			
			return name;
			}
		
		public void insertFifteenDayPlanGroupLoanEmi(int branchId,String loanNo, String remark, int terms, Date secEmiDueDate, int sno, Date firstemi) 
		{
			int count=sno;
			
			try(Connection conn=DataBaseConnection.JAVAConnection())
			{
				Statement st=conn.createStatement();
				String qq1 = "delete from grouploan_emi where sub_loan_no='" + loanNo + "' and CAST(emi_no AS UNSIGNED) >="+sno;
				
				st.executeUpdate(qq1);
				    
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		        String dateStr = sdf.format(firstemi);
				
				
				LocalDate localDate2 = secEmiDueDate.toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDate();
//			
//				System.out.println(localDate2);
				ArrayList<String> dateList=fifteenDaysLoanStrDateList(dateStr,localDate2, terms);
				for(int i=1;i<=dateList.size();i++)
				{ 
					String qq="insert into grouploan_emi(sub_loan_no, emi_no, emi_date,branch_id, remark) values('"+loanNo+"','"+count+"','"+dateList.get(i-1)+"','"+branchId+"',"
							+ " '"+remark+"') ";
					count++;
					st.executeUpdate(qq);
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		
		
		public ArrayList<String> fifteenDaysLoanStrDateList(String dateStr, LocalDate secEmiDueDate, int terms) {
		    ArrayList<String> dateList = new ArrayList<>();
		   
		    LocalDate first = LocalDate.parse(dateStr);
		    System.out.println(first+""+dateStr);
		    DateTimeFormatter sdfForDb = DateTimeFormatter.ofPattern("yyyy-MM-dd");

		    if (terms <= 0) return dateList;

		    // Add 1st and 2nd EMI
		    dateList.add(first.format(sdfForDb));
		    if (terms == 1) return dateList;

		    dateList.add(secEmiDueDate.format(sdfForDb));
		    if (terms == 2) return dateList;

		    // Start alternating from 3rd EMI
		    LocalDate lastOdd = first;
		    LocalDate lastEven = secEmiDueDate;

		    for (int i = 1; i < terms-1; i++) {
		        LocalDate nextDate;
		        if (i % 2 == 0) {
		            // Even index → based on lastEven
		            lastEven = lastEven.plusMonths(1);
		            nextDate = lastEven;
		        } else {
		            // Odd index → based on lastOdd
		            lastOdd = lastOdd.plusMonths(1);
		            nextDate = lastOdd;
		        }

		        dateList.add(nextDate.format(sdfForDb));
		    }

		    return dateList;
		}
		
		public String fetchShareholderAddree(String shareholderNo,Connection conn)
		
		{	String address="";
			String qq="select address from shareholder_info where shareholderno='"+shareholderNo+"'";
			try(PreparedStatement st=conn.prepareStatement(qq)) {
				
				ResultSet rr= st.executeQuery();
				if(rr.next())
				{
					address=rr.getString("address");
				}
				
			} catch (Exception e) {
				// TODO: handle exception
			}
			
			return address;
			
		}
		
//		public void insertFileCharge1( ArrayList<incomeListInfo>list)
//		{
//		  String qq = "INSERT INTO income_table (income_date, category, payment_mode, amount, chq_dd_no, chq_dd_date, bank_name, bank_branch, description, item, old_amount, transfer_to_bank, chequeStatus, no_of_item, loan_no, loan_type, discount, voucher_no, fa_purchase_id, item_rate, status, entry_date, dsa_code, branch_id) "
//		        + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//		
//		for(incomeListInfo aa :list)
//		{
//		try (Connection conn = DataBaseConnection.JAVAConnection();
//		     PreparedStatement ps = conn.prepareStatement(qq)) 
//		{			
//			boolean check = checkFileCharge(aa.getLoan_no());
//			if(check == false)
//			{				
//		    int idx = 1;
//		    ps.setString(idx++, aa.getIncomeDate()); // income_date
//		    ps.setString(idx++, "Other_Cr");                              // category
//		    ps.setString(idx++, "NEFT");                           // payment_mode
//		    ps.setDouble(idx++, aa.getFilechargeAmount());                                // amount
//		    ps.setString(idx++, null);                            // chq_dd_no
//		    ps.setString(idx++, aa.getIncomeDate()); // chq_dd_date
//		    ps.setString(idx++, null);                              // bank_name
//		    ps.setString(idx++, null);                            // bank_branch
//		    ps.setString(idx++, "File Charge Receive");                           // description
//		    ps.setString(idx++, "File Charge");                                  // item
//		    ps.setDouble(idx++, 0);                             // old_amount
//		    ps.setString(idx++, null);                        // transfer_to_bank
//		    ps.setString(idx++, "cleared");                          // chequeStatus
//		    ps.setInt(idx++, 0);                                 // no_of_item
//		    ps.setString(idx++, aa.getLoan_no());                                // loan_no
//		    ps.setString(idx++, aa.getLoan_type());                              // loan_type
//		    ps.setDouble(idx++, 0);                              // discount
//		    ps.setString(idx++, "0");                             // voucher_no
//		    ps.setInt(idx++, 0);                             // fa_purchase_id
//		    ps.setDouble(idx++, 0);                              // item_rate
//		    ps.setString(idx++, "active");                                // status
//		    ps.setDate(idx++, new java.sql.Date(new java.util.Date().getTime())); // entry_date
//		    ps.setString(idx++, null);                               // dsa_code
//		    ps.setString(idx++, aa.getBranch_id());                                 // branch_id
//
//		    int rows = ps.executeUpdate();
//			
//		    if (rows > 0) {
//		        System.out.println("✅ Record inserted successfully!");
//		    }
//			}
//			
//
//		} catch (SQLException e) {
//		    e.printStackTrace();
//		}
//		}
//
//		}
//		
//		public ArrayList<incomeListInfo> selectLoan(Connection conn)
//		{
//			ArrayList<incomeListInfo>list = new ArrayList<>();
//			String qq ="select * from personalloan where branch_id='"+"3"+"' and status='"+"Approve"+"'";
//			
//			System.out.println(qq);
//			
//			try(PreparedStatement st = conn.prepareStatement(qq)) {
//				
//				ResultSet rr = st.executeQuery();
//				int i=1;
//				while(rr.next())
//				{
//					i++;
//					incomeListInfo ss = new incomeListInfo();
//					ss.setIncomeDate(rr.getString("approvalDate"));
//					System.out.println(rr.getString("approvalDate"));
//					ss.setLoan_no(rr.getString("loanNo"));
//					ss.setBranch_id(rr.getString("branch_id"));
//					
//					double amount = rr.getDouble("loanAmount");
//					
//					double fileAMount=Math.round((amount*5)/100);
//					ss.setFilechargeAmount(fileAMount);
//							
//					list.add(ss);
//					
//				}
//				System.out.println(i+"jj");
//				
//			} catch (Exception e) {
//				// TODO: handle exception
//				e.printStackTrace();
//			}
//			return list;
//			
//		}
//
//		public boolean checkFileCharge(String loanNo)
//		{
//			boolean chek = false;
//			Connection conn = DataBaseConnection.JAVAConnection();
//			String qq=" select *  from income_table where loan_no='"+loanNo+"' and item='"+"File Charge"+"' and status='"+"active"+"' and branch_id='"+"3"+"'";
//			try(PreparedStatement st = conn.prepareStatement(qq)) {
//				
//				ResultSet rr = st.executeQuery();
//				
//				if(rr.next())
//				{
//					chek=true;
//				}
//				
//			} catch (Exception e) {
//				// TODO: handle exception
//				e.printStackTrace();
//			}
//			return chek;
//			
//			
//		}
		
//		
//		public void insertFileCharge1( ArrayList<incomeListInfo>list)
//		{String qq = "INSERT INTO income_table (income_date, category, payment_mode, amount, chq_dd_no, chq_dd_date, bank_name, bank_branch, description, item, old_amount, transfer_to_bank, chequeStatus, no_of_item, loan_no, loan_type, discount, voucher_no, fa_purchase_id, item_rate, status, entry_date, dsa_code, branch_id) "
//		        + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//		
//		for(incomeListInfo aa :list)
//		{
//		try (Connection conn = DataBaseConnection.JAVAConnection();
//		     PreparedStatement ps = conn.prepareStatement(qq)) {
//
//			
//				boolean check = checkFileCharge(aa.getLoan_no());
//				
//			if(check == false)
//			{
//				
//		    int idx = 1;
//		    ps.setString(idx++, aa.getIncomeDate()); // income_date
//		    ps.setString(idx++, "Other_Cr");                              // category
//		    ps.setString(idx++, "cash");                           // payment_mode
//		    ps.setDouble(idx++, 500);                                // amount
//		    ps.setString(idx++, null);                            // chq_dd_no
//		    ps.setString(idx++, aa.getIncomeDate()); // chq_dd_date
//		    ps.setString(idx++, null);                              // bank_name
//		    ps.setString(idx++, null);                            // bank_branch
//		    ps.setString(idx++, "Login Fee Receive");                           // description
//		    ps.setString(idx++, "Login Fee");                                  // item
//		    ps.setDouble(idx++, 0);                             // old_amount
//		    ps.setString(idx++, null);                        // transfer_to_bank
//		    ps.setString(idx++, "cleared");                          // chequeStatus
//		    ps.setInt(idx++, 0);                                 // no_of_item
//		    ps.setString(idx++, aa.getLoan_no());                                // loan_no
//		    ps.setString(idx++, null);                              // loan_type
//		    ps.setDouble(idx++, 0);                              // discount
//		    ps.setString(idx++, "0");                             // voucher_no
//		    ps.setInt(idx++, 0);                             // fa_purchase_id
//		    ps.setDouble(idx++, 0);                              // item_rate
//		    ps.setString(idx++, "active");                                // status
//		    ps.setDate(idx++, new java.sql.Date(new java.util.Date().getTime())); // entry_date
//		    ps.setString(idx++, null);                               // dsa_code
//		    ps.setString(idx++, aa.getBranch_id());                                 // branch_id
//
//		    int rows = ps.executeUpdate();
//			
//		    if (rows > 0) 
//		    {
//		        System.out.println("✅ Record inserted successfully!");
//		    }
//			}
//			
//
//		} catch (SQLException e) {
//		    e.printStackTrace();
//		}
//		}
//
//		}
		
//		public ArrayList<incomeListInfo> selectLoan(Connection conn)
//		{
//			ArrayList<incomeListInfo>list = new ArrayList<>();
//			String qq ="select * from shareholder_info where branch_id='"+"3"+"' and account_status='"+"active"+"'";
//			
//			System.out.println(qq);
//			
//			try(PreparedStatement st = conn.prepareStatement(qq)) {
//				
//				ResultSet rr = st.executeQuery();
//				int i=1;
//				while(rr.next())
//				{
//					i++;
//					incomeListInfo ss = new incomeListInfo();
//					ss.setIncomeDate(rr.getString("office_date"));
//					
//					ss.setLoan_no(rr.getString("office_application_no"));
//					ss.setBranch_id(rr.getString("branch_id"));
////					
////					double amount = rr.getDouble("loanAmount");
////					
////					double fileAMount=Math.round((amount*5)/100);
////					ss.setFilechargeAmount(fileAMount);
//							
//					list.add(ss);
//					
//				}
//				System.out.println(i+"jj");
//				
//			} catch (Exception e) {
//				// TODO: handle exception
//				e.printStackTrace();
//			}
//			return list;
//			
//		}


		public boolean checkFileCharge(String loanNo)
		{
			boolean chek = false;
			Connection conn = DataBaseConnection.JAVAConnection();
			String qq=" select *  from income_table where loan_no='"+loanNo+"' and item='"+"Login Fee"+"' and status='"+"active"+"' and branch_id='"+"3"+"'";
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				ResultSet rr = st.executeQuery();
				
				if(rr.next())
				{
					chek=true;
				}
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return chek;
			
			
		}

		
		public ArrayList<String> selectDefaultPageList(Connection con, String empType, String groupName)
		{
			Statement st = null;
			ResultSet rs = null;
			ArrayList<String> list = new ArrayList<>();
			try
			{
				st = con.createStatement();
				String query = "select * from default_permission where employee_type='" + "employee" + "' and group_type='"+groupName+"' ";
				
				
				
				rs = st.executeQuery(query);
				while (rs.next())
				{
					list.add(rs.getString("page_name"));
					System.out.println(rs.getString("page_name"));
				}
			}
			catch (Exception e)
			{
				e.printStackTrace();
				fileCreation(e.getMessage());
			}
			finally 
			{
				try {
					if (st != null)
					{
						st.close();
					}
					if (rs != null)
					{
						rs.close();
					}
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
			return list;
		}
		
		
		public ArrayList<EnachDataExportInfo> selectEnachLoanNo(Connection conn, Date fromDate, Date toDate )
		{
			Timestamp tt = null,tf =new Timestamp(fromDate.getTime());
			tf=new Timestamp(toDate.getTime());
			
			ArrayList<EnachDataExportInfo>list=new ArrayList<>();


			String qq = "SELECT loanNo FROM personalloan";
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				ResultSet rr =st.executeQuery();
				
				while(rr.next())
				{
					EnachDataExportInfo ss=new EnachDataExportInfo();
					ss.setLoanNo(rr.getString("loanNo"));
					list.add(ss);
				}
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
			qq = "SELECT sub_loan_no FROM grouploan";
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				ResultSet rr =st.executeQuery();
				
				while(rr.next())
				{
					EnachDataExportInfo ss=new EnachDataExportInfo();
					ss.setLoanNo(rr.getString("sub_loan_no"));
					list.add(ss);
				}
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return list;
			
			
		}
		
		
		public void selectUmrnNo(ArrayList<EnachDataExportInfo>list,Connection conn)
		{
			for(EnachDataExportInfo ee:list)
			{
				
			String qq="select umrn_no from debit_loan where loanNo='"+ee.getLoanNo()+"'";
			
			
			try(PreparedStatement st =conn.prepareStatement(qq) ) {
				
				ResultSet rr = st.executeQuery();
				while(rr.next())
				{
					ee.setUmrnNo(rr.getString("umrn_no"));
//					System.out.println(rr.getString("umrn_no")+"--"+ee.getLoanNo());
					
				}
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
			
			}
		}

		
		public void loanInstallment(String loanType,String loanNo, Connection conn, ArrayList<LoanInfo> list, String umrnNo)
	    {
	        double temp=0;
	        String qq="";
	        int j=0;
	        Statement st=null;
			ResultSet rs=null;
			boolean checkGroupLoanEmi=false;
//	        ArrayList<LoanInfo> list = new ArrayList<LoanInfo>();
	        try
	        {
	            st = conn.createStatement();
	            if(loanType.equals("grouploan"))
	            {
	                qq = "select * from "+loanType+" where sub_loan_no='"+loanNo+"' and status='Approve'";
	            }
	            else
	            {
	                qq = "select * from "+loanType+" where loanNo='"+loanNo+"' and status='Approve'";
	            }
	           
	            checkGroupLoanEmi=dbm.checkGroupLoanEmiFromTable(loanNo,conn);
	            rs = st.executeQuery(qq);
	            if(rs.next())
	            {   
	                if(rs.getString("emiType").equals("reducing"))
	                {
	                    double discount=dbm.discountOnLastEMI(loanType,loanNo,conn);
	                    double paidAmount=dbm.loanPaidAmountByUser(loanType,loanNo,conn);
	                    int terms = rs.getInt("terms");
	                    int frequency=rs.getInt("frequency");
	                    double emi=rs.getDouble("emi");
	                    Date offcdt =null;
	                    if(rs.getDate("emi_due_date")==null)
	                    {
	                        offcdt =rs.getDate("approvalDate");
	                    }
	                    else
	                    {
	                        offcdt = rs.getDate("emi_due_date");
	                    }
	                   
	                    double interstRate=rs.getDouble("interest");
	                    double loanAmount=rs.getDouble("loanAmount");
	                    double fr=(double) frequency;
	                    Date installDate=null;
	                    j=365/frequency;
	                    if(rs.getDate("emi_due_date")==null)
	                    {
	                        installDate=rs.getDate("approvalDate");
	                    }
	                    else
	                    {
	                        installDate=rs.getDate("emi_due_date");
	                        if(frequency==12)
	                        {
	                            installDate.setMonth(installDate.getMonth()-1);
	                        }
	                        else
	                        {
	                            installDate.setDate(installDate.getDate()-j);
	                        }
	                    }  
	                    int y=1;
	                    for (int i = 1; i<= terms; i++)
	                    {
	                        LoanInfo ll=new LoanInfo();
	                        //int j=365/frequency;
	                        ll.setSno(i);
	                        if(i==terms)
	                        {
	                            emi=emi-discount;
	                            ll.setInstallAmount(emi);
	                        }
	                        else
	                        {
	                            ll.setInstallAmount(emi);
	                        }
	                      
	                        if(paidAmount>=emi)
	                        {
	                            ll.setStatus("Paid");
	                            ll.setPaidAmount(emi);
	                            ll.setDueAmount(0);
	                            paidAmount=Double.parseDouble(df.format(paidAmount-emi));
	                        }
	                        else
	                        {	ll.setUmrnNo(umrnNo);
	                        	ll.setLoanNo(loanNo);
	                            ll.setStatus("Due");
	                            ll.setPaidAmount(paidAmount);
	                            ll.setDueAmount(Double.parseDouble(df.format(emi-paidAmount)));
	                            	
	                            paidAmount=0;
	                        }
	                  
	                        Date dt=installDate;
	                        if(rs.getDate("emi_due_date")==null)
	                        {
	                            dt.setDate(dt.getDate()+j);
	                        }
	                        else
	                        {
	                            if(frequency==12 || frequency==4 || frequency==2 || frequency==1)
	                              {
	                                    if(frequency==4)
	                                    {
	                                        y=4;
	                                        //x=terms/3;
	                                    }
	                                    else if(frequency==2)
	                                    {
	                                        y=6;
	                                        //x=terms/6;
	                                    }
	                                    else if(frequency==1)
	                                    {
	                                        y=12;
	                                        //x=terms/12;
	                                    }
	                                    else if(frequency==12)
	                                    {
	                                        y=1;
	                                        //x=terms;
	                                    }
	                                  offcdt=dbm.createDate2(offcdt,y,i,offcdt.getDate(),rs.getDate("emi_due_date"));
	                              }
	                            else
	                            {
	                                dt.setDate(dt.getDate()+j);
	                            }
	                          
	                        }
	                        
	                        if(checkGroupLoanEmi==false && (frequency==12 || frequency==4 || frequency==2 || frequency==1))
	                        {
	                            ll.setStrInstlmntDate(sf.format(offcdt));
	                            SimpleDateFormat dd=sf;
	                            Date ss=dd.parse(ll.getStrInstlmntDate());
	                            ll.setInstallDate(ss);
	                        }
	                        else
	                        {
	                        	  if(checkGroupLoanEmi==true)  // Condition for Manage Meeting Day Change
                            	  {
                            		  dt=dbm.groupLoanEmiDateFromTable(i,loanNo,conn);
                            	  }
		                        ll.setStrInstlmntDate(sf.format(dt));
		                        SimpleDateFormat dd=sf;
		                        Date ss=dd.parse(ll.getStrInstlmntDate());
		                        ll.setInstallDate(ss);
	                        }
	                      
	                        /*ll.setPaidDate(loanPaidDate(i, loanNo, conn));
	                        if(ll.getPaidAmount() > 0)
	                        {
	                           
	                        if(ll.getPaidDate()==null)
	                        {
	                            ll.setPaidDateStr("- - - -");
	                        }
	                        else
	                        {
	                            ll.setPaidDateStr(new SimpleDateFormat("dd/MM/yyyy").format(loanPaidDate(i, loanNo, conn)));
	                            ll.setReceiptNo(loanPayReceiptNoByLoanNo(i, loanNo, conn));
	                        }
	                        }
	                        else
	                        {
	                            ll.setPaidDateStr("- - - -");
	                        }*/// comment by GARG on 07 jan 2020 
	                        ll.setPaidDateStr("- - - -");
	                      
	                       
	                        double frstIntrstAmnt=(loanAmount*interstRate)/(100*fr);
	                        ll.setInterestAmount(df.format((frstIntrstAmnt)));
	                        ll.setInterest(Double.parseDouble(df.format(frstIntrstAmnt)));
	                      
	                        double paidPrincipalAmnt=emi-(frstIntrstAmnt);
	                        ll.setPrincipalAmount(df.format(paidPrincipalAmnt));
	                        ll.setPrincipal(Double.parseDouble(df.format(paidPrincipalAmnt)));
	                      
	                        double principalRemaining=loanAmount-paidPrincipalAmnt;
	                        ll.setPrincipalBalanceStr(df.format(principalRemaining));
	                        
	                        list.add(ll);
	                        loanAmount=principalRemaining;
	                    }
	                }
	                else
	                {
	                	 String emiMode="";
	                    if(loanType.equalsIgnoreCase("grouploan"))
	                    {
	                    	emiMode="both";
	                    }
	                    else
	                    {
	                    	emiMode=rs.getString("emi_mode");
	                    }
	                    if(emiMode.equalsIgnoreCase("both"))
	                    {
	                          double discount=dbm.discountOnLastEMI(loanType,loanNo, conn);
	                             Date offcdt =null;
	                             if(rs.getDate("emi_due_date")==null)
	                             {
	                                 offcdt =rs.getDate("approvalDate");
	                             }
	                             else
	                             {
	                                 offcdt = rs.getDate("emi_due_date");
	                             }
	                            
	                          double paidAmount=dbm.loanPaidAmountByUser(loanType,loanNo, conn);
	                          int terms = rs.getInt("terms");
	                          int frequency=rs.getInt("frequency");
	                          double emi=rs.getDouble("emi");
	                          double interstRate=rs.getDouble("interest");
	                          double loanAmount=rs.getDouble("loanAmount");
	                          double totalInterest=Double.parseDouble(df.format((emi*terms)-loanAmount));
	                          //double interestMonthly=(totalInterest-rs.getDouble("discount"))/terms;
	                          double interestMonthly=totalInterest/terms;
	                          Date installDate=null;
	                          j=365/frequency;
	                        
	                          if(rs.getDate("emi_due_date")==null)
	                          {
	                              installDate=rs.getDate("approvalDate");
	                          }
	                          else
	                          {
	                              installDate=rs.getDate("emi_due_date");
	                              if(frequency==12)
	                              {
	                                  installDate.setMonth(installDate.getMonth()-1);
	                              }
	                              else
	                              {
	                                  installDate.setDate(installDate.getDate()-j);
	                              }
	                          }
	                          int y=1;
	                          for (int i = 1; i<= terms; i++)
	                          {
	                              LoanInfo ll=new LoanInfo();
	                              ll.setSno(i);
	                              if(i==terms)
	                              {
	                                  emi=emi-discount;
	                                  ll.setInstallAmount(emi);
	                              }
	                              else
	                              {
	                                  ll.setInstallAmount(emi);
	                              }
	                            
	                              if(paidAmount>=emi)
	                              {
	                                  ll.setStatus("Paid");
	                                  ll.setPaidAmount(emi);
	                                  ll.setDueAmount(0);
	                                  paidAmount=Double.parseDouble(df.format(paidAmount-emi));
	                              }
	                              else
	                              {	ll.setUmrnNo(umrnNo);
	                            	  ll.setLoanNo(loanNo);
	                                  ll.setStatus("Due");
	                                  ll.setPaidAmount(paidAmount);
	                                  ll.setDueAmount(Double.parseDouble(df.format(emi-paidAmount)));
	                                  paidAmount=0;
	                              }
	                        
	                              Date dt=installDate;
	                              if(rs.getDate("emi_due_date")==null)
	                              {
	                                  dt.setDate(dt.getDate()+j);
	                              }
	                              else
	                              {
	                                  if(frequency==12 || frequency==4 || frequency==2 || frequency==1)
	                                  {
	                                        if(frequency==4)
	                                        {
	                                            y=4;
	                                            //x=terms/3;
	                                        }
	                                        else if(frequency==2)
	                                        {
	                                            y=6;
	                                            //x=terms/6;
	                                        }
	                                        else if(frequency==1)
	                                        {
	                                            y=12;
	                                            //x=terms/12;
	                                        }
	                                        else if(frequency==12)
	                                        {
	                                            y=1;
	                                            //x=terms;
	                                        }
	                                      offcdt=dbm.createDate2(offcdt,y,i,offcdt.getDate(),rs.getDate("emi_due_date"));
	                                  }
	                                  else
	                                  {
	                                      dt.setDate(dt.getDate()+j);
	                                  }
	                              }
	                              if(checkGroupLoanEmi==false && (frequency==12 || frequency==4 || frequency==2 || frequency==1))
	                              {
	                                  // System.out.println("hsjhhjhjhjhjhjhh "+offcdt);
	                                  ll.setStrInstlmntDate(sf.format(offcdt));
	                                  SimpleDateFormat dd=sf;
	                                  Date ss=dd.parse(ll.getStrInstlmntDate());
	                                  ll.setInstallDate(ss);
	                              }
	                              else
	                              {
	                            	  if(checkGroupLoanEmi==true)  // Condition for Manage Meeting Day Change
	                            	  {
	                            		  dt=dbm.groupLoanEmiDateFromTable(i,loanNo,conn);
	                            	  }
		                              ll.setStrInstlmntDate(sf.format(dt));
		                              SimpleDateFormat dd=sf;
		                              Date ss=dd.parse(ll.getStrInstlmntDate());
		                              ll.setInstallDate(ss);
	                              }
	                             
	                             /* ll.setPaidDate(loanPaidDate(i, loanNo, conn));
	                              if(ll.getPaidAmount() > 0)
	                              {
	                                 
	                              if(ll.getPaidDate()==null)
	                              {
	                                  ll.setPaidDateStr("- - - -");
	                              }
	                              else
	                              {
	                                  ll.setPaidDateStr(new SimpleDateFormat("dd/MM/yyyy").format(loanPaidDate(i, loanNo, conn)));
	                                  ll.setReceiptNo(loanPayReceiptNoByLoanNo(i, loanNo,conn));
	                              }
	                              }
	                              else
	                              {
	                                  ll.setPaidDateStr("- - - -");
	                              }*/
	                           // comment by GARG on 07 jan 2020 
	  	                        ll.setPaidDateStr("- - - -");
	                             
	                              double frstIntrstAmnt=interestMonthly;
	                              ll.setInterestAmount(df.format((frstIntrstAmnt)));
	                              ll.setInterest(Double.parseDouble(df.format(frstIntrstAmnt)));
	                            
	                              double paidPrincipalAmnt=emi-(frstIntrstAmnt);
	                              ll.setPrincipalAmount(df.format(paidPrincipalAmnt));
	                              ll.setPrincipal(Double.parseDouble(df.format(paidPrincipalAmnt)));
		                            
	                              //double principalRemaining=loanAmount-paidPrincipalAmnt;
	                            
	                              list.add(ll);
	                              //loanAmount=principalRemaining;
	                          }
	                    }
	                    else
	                    {
	                        double discount=rs.getDouble("discount");
	                        Date offcdt =null;
	                        if(rs.getDate("emi_due_date")==null)
                            {
                                offcdt =rs.getDate("approvalDate");
                            }
                            else
                            {
                                offcdt = rs.getDate("emi_due_date");
                            }
	                        double paidAmount=dbm.loanPaidAmountByUser(loanType,loanNo, conn);
	                        int terms = rs.getInt("terms");
	                        int frequency=rs.getInt("frequency");
	                        double emi=rs.getDouble("emi");
	                        double interstRate=rs.getDouble("interest");
	                        double loanAmount=rs.getDouble("loanAmount");
	                        //double interestMonthly =Double.parseDouble(new DecimalFormat("##.#######").format(interstRate / (100.00 * frequency)));
	                        double totalInterest=Double.parseDouble(df.format((emi*terms)));
	                       System.out.println("Total interst :"+totalInterest);
	                        double interestMonthly=totalInterest/terms;
	                        System.out.println("Total interst :"+totalInterest);
	                        Date installDate=null;
	                        boolean chk=true;
	                        j=365/frequency;
	                        double fr=(double) frequency;

	                        if(rs.getDate("emi_due_date")==null)
	                        {
	                            installDate=rs.getDate("approvalDate");
	                        }
	                        else
	                          {
	                              installDate=rs.getDate("emi_due_date");
	                              if(frequency==12)
	                              {
	                                  installDate.setMonth(installDate.getMonth()-1);
	                              }
	                              else if(frequency==4)
	                              {
	                                  installDate.setMonth(installDate.getMonth()-3);
	                              }
	                              else
	                              {
	                                  installDate.setDate(installDate.getDate()-j);
	                              }
	                          }
	                        
	                        int y=1;
	                        for (int i = 1; i<= terms; i++)
	                        {
	                            LoanInfo ll=new LoanInfo();
	                            //int jj=365/frequency;
	                            ll.setSno(i);
	                            if(i==terms)
	                            {
	                                emi=emi-discount;
	                                ll.setInstallAmount(emi);
	                            }
	                            else
	                            {
	                                ll.setInstallAmount(emi);
	                            }
	                           
	                            if(paidAmount>=emi)
	                            {
	                                ll.setStatus("Paid");
	                                ll.setPaidAmount(emi);
	                                ll.setDueAmount(0);
	                                paidAmount=Double.parseDouble(df.format(paidAmount-emi));
	                            }
	                            else
	                            {	
	                            	ll.setUmrnNo(umrnNo);
	                            	ll.setLoanNo(loanNo);
	                                ll.setStatus("Due");
	                                ll.setPaidAmount(paidAmount);
	                                ll.setDueAmount(Double.parseDouble(df.format(emi-paidAmount)));
	                                paidAmount=0;
	                            }
	                            
	                            //comment on 23-oct-2019 due to problem in MTOS nidhi
	                            /*Date dt=installDate;
	                            if(rs.getDate("emi_due_date")==null)
	                            {
	                                dt.setDate(dt.getDate()+jj);
	                            }
	                            else
	                            {
	                                if(chk==true)
	                                {
	                                    dt.setMonth(dt.getMonth());
	                                    chk=false;
	                                }
	                                else
	                                {
	                                    dt.setMonth(dt.getMonth()+1);
	                                }
	                            }
	                            ll.setInstallDate(dt);
	                            */
	                            

	                            Date dt=installDate;
	                              if(rs.getDate("emi_due_date")==null)
	                              {
	                                  dt.setDate(dt.getDate()+j);
	                              }
	                              else
	                              {
	                                  if(frequency==12 || frequency==4 || frequency==2 || frequency==1)
	                                  {
	                                        if(frequency==4)
	                                        {
	                                            y=4;
	                                            //x=terms/3;
	                                        }
	                                        else if(frequency==2)
	                                        {
	                                            y=6;
	                                            //x=terms/6;
	                                        }
	                                        else if(frequency==1)
	                                        {
	                                            y=12;
	                                            //x=terms/12;
	                                        }
	                                        else if(frequency==12)
	                                        {
	                                            y=1;
	                                            //x=terms;s
	                                        }
	                                        offcdt=dbm.createDate2(offcdt,y,i,offcdt.getDate(),rs.getDate("emi_due_date"));
	                                  }
	                                  else
	                                  {
	                                      dt.setDate(dt.getDate()+j);
	                                  }
	                              }
	                              if(checkGroupLoanEmi==false && (frequency==12 || frequency==4 || frequency==2 || frequency==1))
	                              {
	                                  // System.out.println("hsjhhjhjhjhjhjhh "+offcdt);
	                                  ll.setStrInstlmntDate(sf.format(offcdt));
	                                  SimpleDateFormat dd=sf;
	                                  Date ss=dd.parse(ll.getStrInstlmntDate());
	                                  ll.setInstallDate(ss);
	                              }
	                              else
	                              {
	                            	  if(checkGroupLoanEmi==true)  // Condition for Manage Meeting Day Change
	                            	  {
	                            		  dt=dbm.groupLoanEmiDateFromTable(i,loanNo,conn);
	                            	  }
		                              ll.setStrInstlmntDate(sf.format(dt));
		                              SimpleDateFormat dd=sf;
		                              Date ss=dd.parse(ll.getStrInstlmntDate());
		                              ll.setInstallDate(ss);
	                              }
	                           
	                             /*ll.setPaidDate(loanPaidDate(i, loanNo, conn));
	                                if(ll.getPaidAmount() > 0)
	                                {
	                                   
	                                if(ll.getPaidDate()==null)
	                                {
	                                    ll.setPaidDateStr("- - - -");
	                                }
	                                else
	                                {
	                                    ll.setPaidDateStr(new SimpleDateFormat("dd/MM/yyyy").format(loanPaidDate(i, loanNo, conn)));
	                                    ll.setReceiptNo(loanPayReceiptNoByLoanNo(i, loanNo, conn));
	                                }
	                                }
	                                else
	                                {
	                                    ll.setPaidDateStr("- - - -");
	                                }*/// comment by GARG on 07 jan 2020 
		                        ll.setPaidDateStr("- - - -");
	                              
	                               
	                          //  ll.setStrInstlmntDate(new SimpleDateFormat("dd/MM/yyyy").format(dt));
	                           
	                            double frstIntrstAmnt=interestMonthly;
	                            ll.setInterestAmount(df.format((frstIntrstAmnt)));
	                            ll.setInterest(Double.parseDouble(df.format(frstIntrstAmnt)));
	                            
	                            double paidPrincipalAmnt=emi-(frstIntrstAmnt);
	                            ll.setPrincipalAmount(df.format(paidPrincipalAmnt));
	                            ll.setPrincipal(Double.parseDouble(df.format(paidPrincipalAmnt)));
	                       
	                            list.add(ll);
	                        }
	                    }
	                 
	                }
	            }
	        }
	        catch (Exception e)
	        {
	             e.printStackTrace();fileCreation(e.getMessage());
	        }
	        finally
			{
				try {
					if(st!=null)
					{
						st.close();
					}
					if(rs!=null)
					{
						rs.close();
					}
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
	      
	        /*int i=0;
	        loopx:for(LoanInfo ll: list)
	        {
	            if(ll.getStatus().equalsIgnoreCase("Paid"))
	            {
	                i=i+1;
	                // // // //System.out.println(i);
	                //lastPaidDate=ll.getInstallDate();
	                // // // //System.out.println(ll.getInstallDate());
	                break loopx;
	            }
	          
	        }*/
	        
	    }

		
		
		public String selectLoanPreLoanNo( Connection conn , String loanType, String memeberNo)
		{
			String loanNo="";
			
			String qq="",qq1="";
			
		 {
			
				 qq1="select * from grouploan where teamLeader='"+memeberNo+"' and paidStatus='"+"unpaid"+"' and status='"+"Approve"+"'";
			}
		
			{
				 qq="select * from personalloan where shareholderno='"+memeberNo+"' and paidStatus='"+"unpaid"+"' and status='"+"Approve"+"'";
			}
			
			
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				ResultSet rr = st.executeQuery();
				if(rr.next())
				{
					loanNo = rr.getString("loanNo");
					System.out.println(loanNo+"sdfghj");
				}
				
			} catch (Exception e) {
				// TODO: handle exception
			}
			
			

			try(PreparedStatement st = conn.prepareStatement(qq1)) {
				
				ResultSet rr1 = st.executeQuery();
				if(rr1.next())
				{
					loanNo = rr1.getString("sub_loan_no");
					System.out.println(loanNo+"sdfghj");
				}
				
			} catch (Exception e) {
				// TODO: handle exception
			}
			
			return loanNo;
			
		}
		
		
		public double selectEmibounceCharge(Connection conn ,String loanNo,int emiNo)
		{
			double bounceCharge=0.0;
			String qq ="select cheque_bounce_charge from loanpayment where loanNo='"+loanNo+"' AND emi_no='"+emiNo+"'";
			
				try(PreparedStatement st =conn.prepareStatement(qq)) {
					
					ResultSet rr= st.executeQuery();
					
					while(rr.next())
					{
						bounceCharge=rr.getDouble("cheque_bounce_charge");
					}
					
					
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
			return bounceCharge;
			
		}

		
		
		public int addProduct(Connection conn, String brand, String name, String model, String categoryId)
		{
			int i=0;
			String qq="insert into products (brand, name, model,status, category_id) values (?, ?, ?, ?, ?)";
			
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				st.setString(1, brand);
				st.setString(2, name);
				st.setString(3, model);
				st.setString(4, "active");
				st.setString(5, categoryId);
				
				 i=st.executeUpdate();
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return i;
			
		}
		
		public ArrayList<productInfo> selectProduct(Connection conn)
		{
			ArrayList<productInfo> list=new ArrayList<>();
			String qq ="select * from products where status='"+"active"+"'";
			
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				ResultSet rr =st.executeQuery();
				while(rr.next())
				{
					productInfo ss = new productInfo();
					String categoryId = rr.getString("category_id");
					ss.setCategoryType(categoryId);
					String categoryTypeName = dbrk.categoryNameById(categoryId,conn);
					ss.setCategoryTypeName(categoryTypeName);
					ss.setBrand(rr.getString("brand"));
					ss.setModel(rr.getString("model"));
					ss.setName(rr.getString("name"));
					ss.setId(rr.getString("id"));
					
					list.add(ss);
					
				}
				
			} catch (Exception e) {
				// TODO: handle exception
			}
			return list;
			
		}
		
		
		public int editProduct(Connection conn, productInfo selectRow)
		{
			int i=0;
			String qq = "UPDATE products SET brand = ?, name = ?, model = ?, category_id=? WHERE id = ?";
			
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				st.setString(1, selectRow.getBrand());
				st.setString(2, selectRow.getName());
				st.setString(3, selectRow.getModel());
				st.setString(4, selectRow.getCategoryType());
				st.setString(5, selectRow.getId());
				
				i=st.executeUpdate();
				System.out.println("selectRow.getCategoryType()"+selectRow.getCategoryType());
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return i;
			
			
		}
		
		public int deleteProduct(Connection conn , productInfo selectRow)
		{
			int	i=0;
			String qq = "UPDATE products 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 int addDealer( Connection conn, String name, String mobile, String address, String email, String gst, String pan, String aadhar)
		{
			int i=0;
			String qq ="insert into dealers (name,mobile,address,email,gst,aadhar_no,pan_no,status) values(?, ?, ?, ?, ?, ?, ?, ?)";
				
				try(PreparedStatement st = conn.prepareStatement(qq)) {
					st.setString(1, name);
					st.setString(2, mobile);
					st.setString(3, address);
					st.setString(4, email);
					st.setString(5, gst);
					st.setString(6, aadhar);
					st.setString(7, pan);
					st.setString(8, "active");
					
					i=st.executeUpdate();
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				
			
			return i;
		}
		
		
		public ArrayList<dealerInfo>selectDealer(Connection conn) 
		{
			ArrayList<dealerInfo> list = new ArrayList<>();
			
			
			String qq ="select * from dealers where status ='"+"active"+"'";
				
				try(PreparedStatement st = conn.prepareStatement(qq)) {
						
					ResultSet rr = st.executeQuery();
					
					while(rr.next())
					{
						dealerInfo ss = new dealerInfo();
						ss.setName(rr.getString("name"));
						ss.setMobile(rr.getString("mobile"));
						ss.setAddress(rr.getString("address"));
						ss.setEmail(rr.getString("email"));
						ss.setGst(rr.getString("gst"));
						ss.setAadhar(rr.getString("aadhar_no"));
						ss.setPan(rr.getString("pan_no"));
						ss.setId(rr.getString("id"));
						list.add(ss);
						
					}
										
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
			
			
			
			return list;
			
		}
		
		
		public int editDealer(Connection conn ,dealerInfo selected)
		{ int i=0;
			String qq=" update dealers SET name = ?, mobile=? ,address=? ,email=? ,gst=? ,aadhar_no=? ,pan_no=? where id=?";
			
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				st.setString(1, selected.getName());
				st.setString(2, selected.getMobile());
				st.setString(3, selected.getAddress());
				st.setString(4, selected.getEmail());
				st.setString(5, selected.getGst());
				st.setString(6, selected.getAadhar());
				st.setString(7, selected.getPan());
				st.setString(8, selected.getId());
				
				i=st.executeUpdate();
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return i;
			
			
		}
		
		
		public int deleteDealer( Connection conn ,dealerInfo selected)
		{
			int i=0;
			String qq ="update dealers SET status =? where id=?";
			
			System.out.println(qq+selected.getId());
			
			try(PreparedStatement st= conn.prepareStatement(qq)) {
				
				st.setString(1, "inactive");
				st.setString(2, selected.getId());
				
				i= st.executeUpdate();
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return i;
		}
 

		
		public ArrayList<SelectItem> dealerList( Connection conn)
		{
			ArrayList<SelectItem> list=new ArrayList<>();
			String qq ="select * from dealers where status ='"+"active"+"'";
			
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				ResultSet rr = st.executeQuery();
				while(rr.next())
				{
					SelectItem ss = new SelectItem();
					ss.setLabel(rr.getString("name"));
					ss.setValue(rr.getString("id"));
					
					list.add(ss);
				}
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return list;
			
			
			
		}
		
		public ArrayList<SelectItem> productList( Connection conn)
		{
			ArrayList<SelectItem> list=new ArrayList<>();
			String qq ="select * from products where status ='"+"active"+"'";
			
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				ResultSet rr = st.executeQuery();
				while(rr.next())
				{
					SelectItem ss = new SelectItem();
					ss.setLabel(rr.getString("brand")+"/"+rr.getString("name")+"/"+rr.getString("model"));
					ss.setValue(rr.getString("id"));
					
					list.add(ss);
				}
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return list;
			
			
			
		}
		
		public String idconvertIntoDealer(Connection conn,String id)
		{
			String name ="";
			
			String qq ="select name from dealers where id ='"+id+"'";
			
			
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				ResultSet rr = st.executeQuery();
				while(rr.next())
				{
					name=rr.getString("name");
				}
				
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return name;
		}
		
		
		public ArrayList<LoanInfo> selectSecureLoanOtherDeatail(String loanNo,Connection conn)
		{
			ArrayList<LoanInfo>list = new ArrayList<>();
			
			String qq ="select * from secure_loan_other where loan_no='"+loanNo+"'";
			System.out.println(qq);
			
			
			try(PreparedStatement st=conn.prepareStatement(qq) ) {
				ResultSet rr= st.executeQuery();
				while(rr.next())
				{
				
				LoanInfo ss = new LoanInfo();
				ss.setvType(rr.getString("v_type"));
				ss.setvCompany(rr.getString("v_company"));
				System.out.println(rr.getString("v_company"));
				ss.setvModel(rr.getString("v_model"));
				ss.setvMarketValue(rr.getString("v_marketvalue"));
				ss.setvRegNo(rr.getString("v_regno"));
				ss.setvEngineNo(rr.getString("v_engineno"));
				ss.setvChesisNo(rr.getString("v_chesisno"));
				ss.setvRegOwner(rr.getString("v_regowner"));
				list.add(ss);
				
				}
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
			
			return list;
			}
		
		
		
		    public String dateConvertintoDay(String[] args,String input) {
		        
		        String[] parts = input.split("/");

		        int day = Integer.parseInt(parts[0]);
		        int month = Integer.parseInt(parts[1]);
		        int year = Integer.parseInt(parts[2]);

		        LocalDate date = LocalDate.of(year, month, day);

		        // Get ordinal suffix for the day
		        String suffix;
		        if (day >= 11 && day <= 13) {
		            suffix = "th";
		        } else {
		            switch (day % 10) {
		                case 1:  suffix = "st"; break;
		                case 2:  suffix = "nd"; break;
		                case 3:  suffix = "rd"; break;
		                default: suffix = "th"; break;
		            }
		        }

		        // Format like "1st day of September, 2025"
		        String formatted = day + suffix;

		        System.out.println(formatted);
				return formatted;
		    }
	

		    
		    
		
		    public String selectDealerAddress( Connection conn ,String id)
		    {
		    	String address="";
		    	String qq ="select address from dealers where id ='"+id+"'";
		    	
		    	try(PreparedStatement st = conn.prepareStatement(qq)) {
		    		ResultSet rr = st.executeQuery();
		    		
		    		while(rr.next())
		    		{
		    			address=rr.getString("address");
		    		}
					
				} catch (Exception e) {
					
					// TODO: handle exception
					e.printStackTrace();
				}
		    	
		    	
				return address;
		    	
		    }
		    
		    
		    public String selectDealerMobile( Connection conn ,String id)
		    {
		    	String mob="";
		    	String qq ="select mobile from dealers where id ='"+id+"'";
		    	
		    	try(PreparedStatement st = conn.prepareStatement(qq)) {
		    		ResultSet rr = st.executeQuery();
		    		
		    		while(rr.next())
		    		{
		    			mob=rr.getString("mobile");
		    		}
					
				} catch (Exception e) {
					
					// TODO: handle exception
					e.printStackTrace();
				}
		    	
		    	
				return mob;
		    	
		    }
		    
		    
		    public String selectDealerEmail( Connection conn ,String id)
		    {
		    	String email="";
		    	String qq ="select email from dealers where id ='"+id+"'";
		    	
		    	try(PreparedStatement st = conn.prepareStatement(qq)) {
		    		ResultSet rr = st.executeQuery();
		    		
		    		while(rr.next())
		    		{
		    			email=rr.getString("email");
		    		}
					
				} catch (Exception e) {
					
					// TODO: handle exception
					e.printStackTrace();
				}
		    	
		    	
				return email;
		    	
		    }
		    
		    public String selectDealer( Connection conn ,String id)
		    {
		    	String dealer="";
		    	String qq ="select name from dealers where id ='"+id+"'";
		    	
		    	
		    	try(PreparedStatement st = conn.prepareStatement(qq)) {
		    		ResultSet rr = st.executeQuery();
		    		
		    		while(rr.next())
		    		{
		    			dealer=rr.getString("name");
		    		}
					
				} catch (Exception e) {
					
					// TODO: handle exception
					e.printStackTrace();
				}
		    	
		    	
				return dealer;
		    	
		    }
		    public String selectProduct( Connection conn ,String id)
		    {
		    	String product="";
		    	String qq ="select * from products where id ='"+id+"'";
		    	
		    	try(PreparedStatement st = conn.prepareStatement(qq)) {
		    		ResultSet rr = st.executeQuery();
		    		
		    		while(rr.next())
		    		{
		    			product=rr.getString("brand")+"/"+rr.getString("name")+"/"+rr.getString("model");
		    		}
					
				} catch (Exception e) {
					
					// TODO: handle exception
					e.printStackTrace();
				}
		    	
		    	
				return product;
		    	
		    }
		    public String selectProductModel( Connection conn ,String id)
		    {
		    	String productModel="";
		    	String qq ="select * from products where id ='"+id+"'";
		    	
		    	try(PreparedStatement st = conn.prepareStatement(qq)) {
		    		ResultSet rr = st.executeQuery();
		    		
		    		while(rr.next())
		    		{
		    			productModel=rr.getString("model");
		    		}
					
				} catch (Exception e) {
					
					// TODO: handle exception
					e.printStackTrace();
				}
		    	
		    	
				return productModel;
		    	
		    }
		    
		    
		
		    public boolean checkPromotorInterestCreditDate(int branchId,Date date, String pid,Connection conn,String amount) 
			{
				String sDate=sdfForDb.format(date);
				ArrayList<SelectItem> list=new ArrayList<>();
				Statement st=null;
				ResultSet rr=null;
				
				
				try
				{
					st=conn.createStatement();
					String query="select expense_date from expense_table where item='Promoter Interest' and expense_date='"+sDate+"' and status='active' and other_table_id='"+pid+"' and amount='"+amount+"' ";
					System.out.println(query);
					
//					System.out.println(query);
					
					rr=st.executeQuery(dbm.selectQueWithBranch(query,branchId));
					if(rr.next())
					{
						return true;
					}
					
				}
				catch(Exception e)
				{
					e.printStackTrace();
				}
				finally 
		        {
		            try 
		            {
		            	if(st!=null)
		            	{
		            		  st.close();
		                }
		            	if(rr!=null)
		            	{
		            	     rr.close();
		                }
		            } 
		            catch (Exception s) 
		            {
		                fileCreation(s.getMessage());
		            }
		        }
				return false;
			}
		    
		    
		    public void selectIncome()
		    {
		    	try(Connection conn = DataBaseConnection.JAVAConnection()) {
		    		
		    		String qq=" select * from income ";
		    		
		    		try(PreparedStatement st = conn.prepareStatement(qq)) {
		    			
		    			
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
		    		
		    		
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
		    }
		    
		    
		    
		    public int fechAttendence()
		    {
		    	int count =1;
		    	try(Connection conn = DataBaseConnection.JAVAConnection()) {
		    		String qq="select * from attendance_table where attendance_date between '"+""+"' and '"+""+"'";
		    		
		    			try(PreparedStatement st = conn.prepareStatement(qq)) {
		    				
		    				ResultSet rr = st.executeQuery();
		    				
		    				while(rr.next())
		    				{
		    					count++;
		    				}
							
						} catch (Exception e) {
							// TODO: handle exception
							e.printStackTrace();
						}
		    		
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return count;
		    }
		
		    public void selectHisarLoan()
		    { int i=1;
		    	try(Connection conn = DataBaseConnection.JAVAConnection()) {
		    		
		    		String qq="select * from personalloan where branch_id='"+"4"+"' and requestDate<'"+"2025-06-30"+"'";
		    		
		    		try(PreparedStatement st=conn.prepareStatement(qq)) {
		    			
		    			ResultSet rr = st.executeQuery();
		    			while(rr.next())
		    			{
		    				Boolean check= checkDupliccy(rr.getString("loanNo"));
		    				if(check== false)
		    				{
		    					double fileCharge=(rr.getDouble("loanAmount")*5)/100;
		    					
		    					calcuHisarFilecharge(rr.getString("requestDate"),fileCharge,rr.getString("loanNo"));
		    				}
		    				
		    			}
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
		    }
		    
		    
		    public void calcuHisarFilecharge(String Date, double fileCharge, String loanNo)
		    {try (Connection conn = DataBaseConnection.JAVAConnection()) {

		        String qq = "INSERT INTO income_table "
		                  + "(income_date, category, payment_mode, amount, chq_dd_no, chq_dd_date, "
		                  + "description, item, transfer_to_bank, chequeStatus, loan_no, loan_type, status, branch_id) "
		                  + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		        System.out.println(qq);

		        try (PreparedStatement st = conn.prepareStatement(qq)) {
		            st.setString(1, Date);
		            st.setString(2, "Other_Cr");
		            st.setString(3, "NEFT");
		            st.setDouble(4, fileCharge);
		            st.setString(5, "");
		            st.setString(6, Date);
		            st.setString(7, "File Charge Receive");
		            st.setString(8, "File Charge");
		            st.setString(9, "");
		            st.setString(10, "cleared");
		            st.setString(11, loanNo);
		            st.setString(12, "");
		            st.setString(13, "active");
		            st.setString(14, "4");

		            // ✅ Execute insert
		            int rows = st.executeUpdate();
		            System.out.println(rows + " row(s) inserted.");
		        }

		    } catch (Exception e) {
		        e.printStackTrace();
		    }
}
		    
		    
		    public boolean checkDupliccy(String loanNo)
		    {
		    	boolean check=false;
		    		
		    		try(Connection conn =DataBaseConnection.JAVAConnection()) {
		    			
		    			String qq="select * from income_table where loan_no='"+loanNo+"' and item='"+"File Charge"+"' ";
		    			try(PreparedStatement st = conn.prepareStatement(qq)) {
		    				
		    				ResultSet rr = st.executeQuery();
		    				
		    				while(rr.next())
		    				{
		    					check=true;
		    					System.out.println(rr.getString("loan_no"));
		    					
		    				}
							
						} catch (Exception e) {
							// TODO: handle exception
							e.printStackTrace();
						}
		    			
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
		    	
		    	
		    	
				return check;
		    	
		    }
		    
		    
		    public void selectMemberHIsar()
		    {
		    	int i=1;
		    	try(Connection conn = DataBaseConnection.JAVAConnection()) {
		    		String qq="select * from shareholder_info where branch_id='"+"4"+"'";
		    		
		    		try(PreparedStatement st= conn.prepareStatement(qq)) {
		    			ResultSet rr = st.executeQuery();
		    			while(rr.next())
		    			{
		    				
		    				
//		    				System.out.println(checklogin(rr.getString("shareholderno")));
		    				boolean check =checklogin(rr.getString("shareholderno"));
		    				if(check == false)
		    				{
		    					System.out.println(i++);
		    					insertLoginFee(rr.getString("shareholderno"),rr.getString("office_date"));
		    				}
		    				
		    				
		    			}
		    			
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
		    		
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
		    }
		    
		    
		    public boolean checklogin(String shareNo)
		    {
		    	boolean check=false;
		    	try(Connection conn = DataBaseConnection.JAVAConnection()) {
		    		
		    		String qq="select * from income_table where loan_no='"+shareNo+"' and item='"+"Login Fee"+"'";
		    		
		    		try(PreparedStatement st = conn.prepareStatement(qq)) {
		    			ResultSet rr= st.executeQuery();
		    			while(rr.next())
		    			{
		    				check=true;
		    			}
		    			
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return check;
		    }
		    
		    public void insertLoginFee(String shareNo, String date)
		    {
		    	
		    	try (Connection conn = DataBaseConnection.JAVAConnection()) {

			        String qq = "INSERT INTO income_table "
			                  + "(income_date, category, payment_mode, amount, chq_dd_no, chq_dd_date, "
			                  + "description, item, transfer_to_bank, chequeStatus, loan_no, loan_type, status, branch_id) "
			                  + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

			        System.out.println(qq);

			        try (PreparedStatement st = conn.prepareStatement(qq)) {
			            st.setString(1, date);
			            st.setString(2, "Other_Cr");
			            st.setString(3, "Online");
			            st.setDouble(4, 500);
			            st.setString(5, "");
			            st.setString(6, date);
			            st.setString(7, "Login Fee Receive");
			            st.setString(8, "Login Fee");
			            st.setString(9, "");
			            st.setString(10, "cleared");
			            st.setString(11, shareNo);
			            st.setString(12, "");
			            st.setString(13, "active");
			            st.setString(14, "4");

			            // ✅ Execute insert
			            int rows = st.executeUpdate();
			            System.out.println(rows + " row(s) inserted.");
			        }

			    } catch (Exception e) {
			        e.printStackTrace();
			    }
		    	
		    }
		    
		    
		    
		    
		    public financeCardInfo fetchSharhorder(String employee) {
		    	
		    	financeCardInfo ff = new financeCardInfo();
		    	SimpleDateFormat inputFormat = new SimpleDateFormat("yyyy-MM-dd"); // format from DB
		    	SimpleDateFormat atmFormat = new SimpleDateFormat("MM/yy");
		    	
		    	try(Connection conn = DataBaseConnection.JAVAConnection()) {
		    		
		    		String qq ="select * from shareholder_info where shareholderno='"+employee+"'";
		    		try(PreparedStatement st= conn.prepareStatement(qq)) {
		    			
		    			ResultSet rr=st.executeQuery();
		    			
		    			while(rr.next())
		    			{
		    				
		    				
		    				ff.setName(rr.getString("first_applicantName"));
		    				System.out.println(rr.getString("first_applicantName"));
		    				
		    				ff.setMemberId(rr.getString("shareholderno"));
		    				
		    				Date date = inputFormat.parse(rr.getString("office_date")); // parse DB string to Date
		    			    String atmDate = atmFormat.format(date);  
		    			    ff.setStartDate(atmDate);
		    			    Calendar cal = Calendar.getInstance();
		    			    cal.setTime(date);
		    			    cal.add(Calendar.YEAR, 10);
		    			    Date newDate = cal.getTime();
		    			    
		    			    // Format to MM/yy
		    			    String end = atmFormat.format(newDate);
		    				ff.setEndDate(end);
		    				
		    				ff.setCardLimit(rr.getDouble("credit_limit"));
		    				double a1 = selectLoanAmount(employee);
		    				double a2 = selectLoanAmount2(employee);
		    				double a3 = selectLoanAmount3(employee);

		    				System.out.println("Loan amounts: " + a1 + ", " + a2 + ", " + a3);

		    				double amount = a1 + a2 + a3;
		    				ff.setCurrentCardLoanAmount(amount);

		    				
		    				ff.setCurrentCardLimit(rr.getDouble("credit_limit")-amount);
		    				
		    				System.out.println(rr.getDouble("credit_limit"));
		    				System.out.println(selectLoanAmount(rr.getString("shareholderno")));
		    				
		    				System.out.println(rr.getDouble("credit_limit")-selectLoanAmount(rr.getString("shareholderno")));
		    				
		    				
		    				
		    			}
						
					} catch (Exception e) {
						// TODO: handle 
						e.printStackTrace();
					}
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return ff;
		    	
		    	
		    }
		    
		    
		    public int setLimit(String id, double amount)
		    {
		    	int i=0;
		    	try(Connection conn =DataBaseConnection.JAVAConnection()) {
		    		
		    		String qq ="update shareholder_info set credit_limit=? where id=?";
		    		try(PreparedStatement st = conn.prepareStatement(qq)) {
		    			
		    			st.setDouble(1, amount );
		    			st.setString(2, id);
		    			
		    			i=st.executeUpdate();
		    			
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
					 
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return i;
		    }
		    
		    
		    public double selectLoanAmount(String shareholderno) {
		        double amount = 0.0;

		        try (Connection conn = DataBaseConnection.JAVAConnection()) {
		            if (conn == null) {
		                System.out.println("DB connection failed!");
		                return 0.0;
		            }

//		            System.out.println("shareholderno: " + shareholderno);

		            String queries = 
		                "SELECT loanAmount FROM secure_loan WHERE shareholderno=? AND status='Approve' AND paidStatus='unpaid'";

		            {
		                try (PreparedStatement st = conn.prepareStatement(queries)) {
		                    st.setString(1, shareholderno);
		                    ResultSet rr = st.executeQuery();
		                    while (rr.next()) {
		                        amount += rr.getDouble("loanAmount");
		                    }
		                } catch (Exception e) {
		                    e.printStackTrace();
		                }
		            }
		        } catch (Exception e) {
		            e.printStackTrace();
		        }

		        System.out.println("Total amount: " + amount);
		        return amount;
		    }

		    public double selectLoanAmount2(String shareholderno) {
		        double amount = 0.0;

		        try (Connection conn = DataBaseConnection.JAVAConnection()) {
		            if (conn == null) {
		                System.out.println("DB connection failed!");
		                return 0.0;
		            }

		            System.out.println("shareholderno: " + shareholderno);

		            String queries = 
		                "SELECT loanAmount FROM grouploan WHERE teamLeader=? AND status='Approve' AND paidStatus='unpaid'";

		            {
		                try (PreparedStatement st = conn.prepareStatement(queries)) {
		                    st.setString(1, shareholderno);
		                    ResultSet rr = st.executeQuery();
		                    while (rr.next()) {
		                        amount += rr.getDouble("loanAmount");
		                    }
		                } catch (Exception e) {
		                    e.printStackTrace();
		                }
		            }
		        } catch (Exception e) {
		            e.printStackTrace();
		        }

		        System.out.println("Total amount: " + amount);
		        return amount;
		    }

		    public double selectLoanAmount3(String shareholderno) {
		        double amount = 0.0;

		        try (Connection conn = DataBaseConnection.JAVAConnection()) {
		            if (conn == null) {
//		                System.out.println("DB connection failed!");
		                return 0.0;
		            }

//		            System.out.println("shareholderno: " + shareholderno);

		            String queries = 
		                "SELECT loanAmount FROM personalloan WHERE shareholderno=? AND status='Approve' AND paidStatus='unpaid'";

		            {
		                try (PreparedStatement st = conn.prepareStatement(queries)) {
		                    st.setString(1, shareholderno);
		                    ResultSet rr = st.executeQuery();
		                    while (rr.next()) {
		                        amount += rr.getDouble("loanAmount");
		                    }
		                } catch (Exception e) {
		                    e.printStackTrace();
		                }
		            }
		        } catch (Exception e) {
		            e.printStackTrace();
		        }

		        System.out.println("Total amount: " + amount);
		        return amount;
		    }

		    
		    
		    public int insertLoanRequestByFinanceCard(String memberId, String amount, String frequency, String terms) {
		        int i = 0;
		        java.util.Date currentDate = new java.util.Date();
		        java.sql.Date sqlDate = new java.sql.Date(currentDate.getTime());
		        java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(currentDate.getTime());
		        if (frequency.equalsIgnoreCase("Monthly")) 
		        {
		        	frequency="12";					
				}
		        else if(frequency.equalsIgnoreCase("Daily"))
		        {	
		        	frequency="365";
		        }
		        else if(frequency.equalsIgnoreCase("Weekly"))
		        {
		        	frequency="52";
		        }
		        
		        try (Connection conn = DataBaseConnection.JAVAConnection()) {
		            String query = "INSERT INTO loan_request_from_app (share_holder_no, loan_type, request_date, amount, frequency, terms, status,branch_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
		            
		            try (PreparedStatement st = conn.prepareStatement(query)) {
		                st.setString(1, memberId);
		                st.setString(2, "personalLoan");
		                st.setDate(3, sqlDate);           // for DATE column
		                st.setString(4, amount);
		                st.setString(5, frequency);
		                st.setString(6, terms);
		                st.setString(7, "pending");
		                st.setString(8, selectBranchIdMember(memberId));
		                // for DATETIME or TIMESTAMP column
		                
		                i = st.executeUpdate();
		            }
		        } catch (Exception e) {
		            e.printStackTrace();
		        }
		        return i;
		    }

		    public ArrayList<FinanaceCardInfo> viewFinanceCardLoan(String memberId)
		    {
		    	ArrayList<FinanaceCardInfo> list =new ArrayList<>();
		    	
		    
		    	try(Connection conn = DataBaseConnection.JAVAConnection()) {
		    		
		    		String qq="select * from loan_request_from_app where share_holder_no='"+memberId+"'";
		    		System.out.println(qq);
		    		
		    		
		    		try(PreparedStatement st= conn.prepareStatement(qq)) {
		    			
		    			ResultSet rr = st.executeQuery();
		    			while(rr.next())
		    			{
		    				FinanaceCardInfo ff = new FinanaceCardInfo();
		    				ff.setName(selectMemberName(memberId));
		    				ff.setMemeberId(memberId);
		    				ff.setFrequency(rr.getString("frequency"));
		    				ff.setTerms(rr.getString("terms"));
		    				ff.setAmount(rr.getString("amount"));
		    				ff.setStatus(rr.getString("status"));
		    				
		    				
		    				list.add(ff);
		    				
		    			}
		    	
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
		    	
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return list;
		    }
		    
		    
		    public String selectMemberName(String memberId)
		    {
		    	String name="";
		    	try(Connection conn = DataBaseConnection.JAVAConnection()) {
		    		
		    		String qq="select first_applicantName from shareholder_info where shareholderno='"+memberId+"'";
		    		System.out.println(qq);
		    		
		    		try(PreparedStatement st = conn.prepareStatement(qq)) {
		    			
		    			ResultSet rr = st.executeQuery();
		    			if(rr.next())
		    			{
		    				name=rr.getString("first_applicantName");
		    			}
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
		    	
				return name;
		    	
		    }
		    
		    
		    
		    
		    public ArrayList<FinanaceCardInfo> selectTotalLoanAmount(String shareholderno) {
		       
		    	ArrayList<FinanaceCardInfo>list= new ArrayList<>();
		    
		    	double totalChqCharge=0.0;
		    	ArrayList<LoanInfo> loanEMIList = new ArrayList<>();
		        String[] queries = {
		            "SELECT * FROM secure_loan WHERE shareholderno=? AND status='Approve' AND paidStatus='unpaid'",
		            "SELECT * FROM grouploan WHERE teamLeader=? AND status='Approve' AND paidStatus='unpaid'",
		            "SELECT * FROM personalloan WHERE shareholderno=? AND status='Approve' AND paidStatus='unpaid'"
		        };

		        try (Connection conn = DataBaseConnection.JAVAConnection()) {
//		            if (conn == null) {
//		                System.out.println("DB connection failed!");
//		                return 0.0;
//		            }

		            System.out.println("shareholderno: " + shareholderno);
		            
		            for (String query : queries) {
		                try (PreparedStatement st = conn.prepareStatement(query)) {
		                    st.setString(1, shareholderno);
		                    ResultSet rs = st.executeQuery();
		                    while (rs.next()) {
		                    	FinanaceCardInfo ff = new FinanaceCardInfo();
		                    	
		                    			ff.setLoanAmount(rs.getDouble("loanAmount"));
//		                    			System.out.println(rs.getDouble("loanAmount"));
		                    			String loanNo = rs.getString("loanNo");
		                    			String firstTwo;
		                    			ff.setLoanNo(loanNo);
		                    			if (loanNo != null && loanNo.length() >= 2) {
		                    			    firstTwo = loanNo.substring(0, 2);
		                    			} 
		                    			else {
		                    			    firstTwo = "??"; // fallback or handle appropriately
		                    			}
		                    			
		                    			if(firstTwo.equalsIgnoreCase("PL"))
{
		                    				totalChqCharge=obj.calculatePenaltyAndChequeBounceCharges(loanNo, "Cheque Bounce","personalloan", conn);

		                    			loanEMIList=obj.loanInstallment("personalloan",rs.getString("loanNo"), conn);
		                    			loanEMIList=obj.penaltyCalculationMethod("personalloan", rs.getString("loanNo"), loanEMIList, conn);
}
		                    			else if(firstTwo.equalsIgnoreCase("GL"))
		                    			{
			                    			loanEMIList=obj.loanInstallment("grouploan",rs.getString("sub_loan_no"), conn);
			                    			loanEMIList=obj.penaltyCalculationMethod("grouploan",rs.getString("sub_loan_no"), loanEMIList, conn);
			                    			totalChqCharge=obj.calculatePenaltyAndChequeBounceCharges(loanNo, "Cheque Bounce","grouploan", conn);


		                    			}
		                    			else if(firstTwo.equalsIgnoreCase("SL"))
		                    			{
			                    			loanEMIList=obj.loanInstallment("secure_loan",rs.getString("loanNo"), conn);
			                    			loanEMIList=obj.penaltyCalculationMethod("secure_loan",rs.getString("loanNo"), loanEMIList, conn);
			                    			totalChqCharge=obj.calculatePenaltyAndChequeBounceCharges(loanNo, "Cheque Bounce","secure_loan", conn);

		                    			}
		                    			
		                    			for(int l=0;l<loanEMIList.size();l++)
		                    			{
		                    				Date dt=new Date();
		                    				if(loanEMIList.get(l).getStatus().equals("Paid"))
		                    				{
		                    					loanEMIList.get(l).setColorStatus("paid");
		                    				}
		                    				else
		                    				{
		                    					if(loanEMIList.get(l).getInstallDate().before(dt))
		                    					{
		                    						loanEMIList.get(l).setColorStatus("due");
		                    						
		                    						ff.setDueAmount(ff.getDueAmount() + loanEMIList.get(l).getDueAmount());
		                    						ff.setPenaltyAmount(ff.getPenaltyAmount()+loanEMIList.get(l).getPenaltyDue());

		                    					}
		                    					else
		                    					{
		                    						loanEMIList.get(l).setColorStatus("upcoming");
		                    					}
		                    				}
		                    			}
		                    			
		                    			double totPaid=obj.totalPaidPenaltyAndChequeBounceCharges(loanNo, "Penalty", conn);
		                    			if(totPaid>0)
		                    			{
		                    				for(int i=0; i<loanEMIList.size(); i++)
		                    				{
		                    					if(totPaid>loanEMIList.get(i).getPenaltyDue())
		                    					{
		                    						loanEMIList.get(i).setPenaltyPaid(loanEMIList.get(i).getPenaltyDue());
		                    						ff.setPenaltyPaid(loanEMIList.get(i).getPenaltyDue());
		                    					}
		                    					else
		                    					{
		                    						loanEMIList.get(i).setPenaltyPaid(totPaid);
		                    						ff.setPenaltyPaid(totPaid);

		                    						break;
		                    					}
		                    					totPaid=totPaid-loanEMIList.get(i).getPenaltyDue();
		                    				}
		                    				
		                    			}
		                    			
		                    			ff.setBounceCharge(totalChqCharge);
		                    			
		                    			list.add(ff);
		                       
		                    }
		                } catch (Exception e) {
		                    e.printStackTrace();
		                }
		            }

		        } catch (Exception e) {
		            e.printStackTrace();
		        }

		        
		        return list;
		    }

		    
		    public String selectBranchIdMember(String memberId)
		    {
		    	String branchId="";
		    	try(Connection conn = DataBaseConnection.JAVAConnection()) {
		    		
		    		String qq="select branch_id from shareholder_info where shareholderno='"+memberId+"'";
		    		
		    		try(PreparedStatement st = conn.prepareStatement(qq)) {
		    			
		    			ResultSet rr=st.executeQuery();
		    			while(rr.next())
		    			{
		    				branchId=rr.getString("branch_id");
		    			}
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
					
				}
		    	catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return branchId;
		    }
		    
		    
		    public double selectEmiAmount(String loanNo)
		    { 
		    	double emiAmt=0.0;
		    	try(Connection conn = DataBaseConnection.JAVAConnection()) {
		    		String qq="";
		    		
		    		if(loanNo.contains("GL"))
		    		{
		    			qq="select * from grouploan where sub_loan_no='"+loanNo+"'";
		    		}
		    		else if(loanNo.contains("PL"))
		    		{
		    			qq="select * from personalloan where loanNo='"+loanNo+"'";
		    		}
		    		else if(loanNo.contains("SL"))
		    		{
		    			qq="select * from secure_loan where loanNo='"+loanNo+"'";
		    		}
		    		else {
				    	qq="select * from personalloan where loanNo='"+loanNo+"'";

		    		}
			    	
			    	try(PreparedStatement st= conn.prepareStatement(qq)) {
			    		
						ResultSet rr = st.executeQuery();
						
						if(rr.next())
						{
							emiAmt=rr.getDouble("emi");
						}
						
			    		
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
		    	

				return emiAmt;
				}
		    
		    
		    public ArrayList<SelectItem> allDsaList(int branchId,String type, Connection conn) 
			{
				ArrayList<SelectItem> list=new ArrayList<>();
				Statement st=null;
				ResultSet rr=null;
				String query="";
				try
				{
					st=conn.createStatement();
					if(branchId==-1)
					{
						query="select code,name ,branch_allocate_table.branch_name from dsa where code in (select dsacode from employee where uname in( select userid from login where id in "
								+ "(select user_id from branch_allocate_table) and status='active' and type='employee')) and status='active'";
					}
					else
					{
						query="select code,name,branch_allocate_table.branch_name from dsa where code in (select dsacode from employee where uname in( select userid from login where id in "
								+ "(select user_id from branch_allocate_table where branch_id='"+branchId+"') and status='active' and type='employee')) and status='active'";
					}
					
					rr=st.executeQuery(query);
					while(rr.next())
					{
						SelectItem ll=new SelectItem();
						
						ll.setLabel(rr.getString("code")+"-"+rr.getString("name"));
						ll.setValue(rr.getString("code"));
						list.add(ll);
					}
				}
				catch(Exception e)
				{
					 e.printStackTrace();fileCreation(e.getMessage());
				}
				finally
				{
					try {
						if(st!=null)
						{
							st.close();
						}
						if(rr!=null)
						{
							rr.close();
						}
					} catch (Exception e2) {
						// TODO: handle exception
				
			}
		}
				return list;
			}
			
			
			
			public ArrayList<SelectItem> allDsaListForCommision(String type,int branchId, Connection conn) 
			{
				ArrayList<SelectItem> list=new ArrayList<>();
				Statement st=null;
				ResultSet rr=null;
				try
				{
					st=conn.createStatement();
					String query="select * from dsa where status='active'  ";
					rr=st.executeQuery(obj.selectQueWithBranch(query,branchId));
				//	// // // //System.out.println(query);
					while(rr.next())
					{
						SelectItem ll=new SelectItem();
						
						ll.setLabel(rr.getString("code")+"-"+rr.getString("name"));
						ll.setValue(rr.getString("code")+"-"+rr.getString("name"));
						
						list.add(ll);
					}
				}
				catch(Exception e)
				{
					 e.printStackTrace();fileCreation(e.getMessage());
				}
				finally
				{
					try {
						if(st!=null)
						{
							st.close();
						}
						if(rr!=null)
						{
							rr.close();
						}
					} catch (Exception e2) {
						// TODO: handle exception
				
			}
		}
				return list;
			}


			public ArrayList<String> collectionlist(Date endDate, ArrayList<SelectItem> loanTypeList, String reportType, 
													int branchId, String dsaCode,String center, Connection conn, String frequency)
			{	
				Statement st = null;
				ResultSet rs = null;
				String eDate = sdfForDb.format(endDate);
				ArrayList<String> list = new ArrayList<>();
				
				if(frequency.equalsIgnoreCase("all"))
				{
					try
					{
						if(reportType.equals("Loan"))
						{
							for(SelectItem ff:loanTypeList)
							{
							st=conn.createStatement();
							System.out.println("get value is:"+ff.getValue());
							String query="select * from "+ff.getValue()+" where status='Approve' and paidStatus='unpaid'";
							if(!dsaCode.equalsIgnoreCase("-1") && !dsaCode.equalsIgnoreCase("all"))
							{
								query+=" and dsacode='"+dsaCode+"' ";
							}
						    
							if(!center.equalsIgnoreCase("-1") && !center.equalsIgnoreCase("all"))
							{
								query+=" and center='"+center+"' ";
							}
							
							System.out.println(query);
							rs= st.executeQuery(obj.selectQueWithBranch(query,branchId));
						    while(rs.next())
							    {
							    	if(ff.getValue().equals("grouploan"))
										{
											list.add(rs.getString("sub_loan_no"));
										}
							    	else
								    	{
								    	list.add(rs.getString("loanNo"));
								    	}
							    }   
						    }
						}
					else
						{
						System.out.println("I am in Else condition");
						st=conn.createStatement();
						String query="select * from "+reportType+" where status='Approve' and paidStatus='unpaid' ";
						if(!dsaCode.equalsIgnoreCase("-1") && !dsaCode.equalsIgnoreCase("all"))
						{
							query+=" and dsacode='"+dsaCode+"' ";
						}
						
						if(!center.equalsIgnoreCase("-1") && !center.equalsIgnoreCase("all"))
						{
							query+=" and center='"+center+"' ";
						}
						System.out.println("query in else is"+query);
						rs= st.executeQuery(obj.selectQueWithBranch(query,branchId));
					    while(rs.next())
						    {
						    	if(reportType.equals("grouploan"))
									{
										list.add(rs.getString("sub_loan_no"));
									}
						    	else
							    	{
							    	   list.add(rs.getString("loanNo"));
							    	}
						    }   
					    
						
						}
					}

		catch(Exception e)
		{
			 e.printStackTrace();fileCreation(e.getMessage());
		}
			finally
			{
				try {
					if(st!=null)
					{
						st.close();
					}
					if(rs!=null)
					{
						rs.close();
					}
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}

				}
				else {
					
					try
					{
					if(reportType.equals("Loan"))
						{
						for(SelectItem ff:loanTypeList)
						 {
							st=conn.createStatement();
							String query="select * from "+ff.getValue()+" where status='Approve' and paidStatus='unpaid' and frequency='"+frequency+"'";
							
							if(!dsaCode.equalsIgnoreCase("-1") && !dsaCode.equalsIgnoreCase("all"))
							{
								query+=" and dsacode='"+dsaCode+"' ";
							}
							
							if(!center.equalsIgnoreCase("-1") && !center.equalsIgnoreCase("all"))
							{
								query+=" and center='"+center+"' ";
							}
							
							System.out.println(query);
							rs= st.executeQuery(obj.selectQueWithBranch(query,branchId));
						    while(rs.next())
							    {
							    	if(ff.getValue().equals("grouploan"))
										{
											list.add(rs.getString("sub_loan_no"));
										}
							    	else
								    	{
								    	list.add(rs.getString("loanNo"));
								    	}
							    }   
						    }
						}
					else
						{

						st=conn.createStatement();
						String query="select * from "+reportType+" where status='Approve' and paidStatus='unpaid' and frequency='"+frequency+"' ";
						if(!dsaCode.equalsIgnoreCase("-1") && !dsaCode.equalsIgnoreCase("all"))
						{
							query+=" and dsacode='"+dsaCode+"' ";
						}
						
						if(!center.equalsIgnoreCase("-1") && !center.equalsIgnoreCase("all"))
						{
							query+=" and center='"+center+"' ";
						}
						
						System.out.println(query);
						rs= st.executeQuery(obj.selectQueWithBranch(query,branchId));
					    while(rs.next())
						    {
						    	if(reportType.equals("grouploan"))
									{
										list.add(rs.getString("sub_loan_no"));
									}
						    	else
							    	{
							    	   list.add(rs.getString("loanNo"));
							    	}
						    }   
					    
						
						}
					}

		catch(Exception e)
		{
			 e.printStackTrace();fileCreation(e.getMessage());
		}
			finally
			{
				try {
					if(st!=null)
					{
						st.close();
					}
					if(rs!=null)
					{
						rs.close();
					}
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}

				}
				
							return list;
				
			}
			
			
			public String manageEmployeeId(String empId)
			{
				String label="";
				
				String fmt=selectEmpFor();
				if(!"".equalsIgnoreCase(fmt)) 
				{
					if(empId != null && empId.contains("-")) 
					{
					  
							String data = empId;
							String[] arr = data.split("-");
			
							String second = arr[1];   // index 1 = second element
						
			
							
							label=(fmt+"-"+second);
					}else {
						label=(fmt+"-"+empId);
					}
				}
				else {
					label= empId;
				}
				
				return label;
			}
			
			public String selectEmpFor()
			{
				String fmt="";
				try(Connection conn = DataBaseConnection.JAVAConnection()) {
					
					String qq="select * from firm_detail";
						
						try(PreparedStatement st = conn.prepareStatement(qq)) {
							
							ResultSet rr = st.executeQuery();
							
							if(rr.next())
							{
								fmt=rr.getString("emp_id_format");
							}
								
							
						} catch (Exception e) {
							// TODO: handle exception
							e.printStackTrace();
						}
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return fmt;
			}

			
			public int checkRating(String loanNo)
			{
				int rate=0;
				try(Connection conn = DataBaseConnection.JAVAConnection()) {
					
					String qq ="select * from add_loan_closer where loan_no='"+loanNo+"' ";
					
						try(PreparedStatement st = conn.prepareStatement(qq)) {
							
							ResultSet rr =st.executeQuery();
							if(rr.next())
							{
								rate= rr.getInt("rating");
								
							}
							
						} catch (Exception e) {
							// TODO: handle exception
							e.printStackTrace();
						}
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return rate;
			}
			
			
			public Date selectDisburseDate(String loanNo)
			{
				Date disburseDate = null;
				
				try(Connection conn = DataBaseConnection.JAVAConnection()) {
					
					String qq= "select * from debit_loan where loanNo='"+loanNo+"'";
						
						try(PreparedStatement st = conn.prepareStatement(qq)) {
							
							ResultSet rr = st.executeQuery();
							if(rr.next())
							{
								disburseDate=rr.getDate("date");
							}
							
						} catch (Exception e) {
							// TODO: handle exception
							e.printStackTrace();
						}
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return disburseDate;
			}
			
			
			public void insertPtp()
			{
				try(Connection conn = DataBaseConnection.JAVAConnection()) {
					
					String qq ="insert into ptp_table (loan_no, date, description, emi_no, user_id, entry_time) values(?, ?, ?, ?, ?, ?)" ;
					
					try(PreparedStatement st = conn.prepareStatement(qq)) {
						
						
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
			}
			
			
			public int selectEmiNo( String loanType,String loanNo)
			{ 
				int emiNo =0;
				String first = loanNo.split("/")[0];
				try(Connection conn = DataBaseConnection.JAVAConnection()) {
					
					String qq ="select * from loanpayment where loanNo='"+first+"' and loanType='"+loanType+"'";
//					System.out.println(qq);
					
					try(PreparedStatement st = conn.prepareStatement(qq)) {
						
						ResultSet rr = st.executeQuery();
						while(rr.next())
						{
							emiNo= rr.getInt("emi_no");
//							System.out.println(rr.getInt("emi_no"));
						}
//						
						emiNo+=1;
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return emiNo;
			}
			
			
			public int AddPtp(Date ptpDate, String loanNo, String loanType, int emiNo, String remark, String userId, int branchId)
			{ 
				int i=0;
				
				Timestamp tt = new Timestamp(ptpDate.getTime());
				try(Connection conn = DataBaseConnection.JAVAConnection()) {
					
					String qq ="insert into ptp (loan_no, date, statement, emi_no, user_id, entry_time, loan_type,branch_id) values(?, ?, ?, ?, ?, ?, ?, ?)";
					
					try(PreparedStatement st = conn.prepareStatement(qq)) {
						
						st.setString(1, loanNo);
						st.setTimestamp(2, tt);
						st.setString(3, remark);
						st.setInt(4, emiNo);
						st.setString(5, userId);
						st.setDate(6, new java.sql.Date(System.currentTimeMillis()));
						st.setString(7, loanType);
						st.setInt(8, branchId);
						
						i= st.executeUpdate();
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return i;
			}
					
			public String selectEmiPaidDate(int i, String loanNo, Connection conn)
			{
			    String payDate = null;
			    SimpleDateFormat sf = new SimpleDateFormat("dd/MM/yyyy");

			    String qq = "SELECT pay_date FROM loanpayment " +
			                "WHERE loanNo=? AND emi_no=?";

			    try (PreparedStatement st = conn.prepareStatement(qq))
			    {
			        st.setString(1, loanNo);
			        st.setInt(2, i);

			        try (ResultSet rr = st.executeQuery())
			        {
			            if (rr.next())
			            {
			                if (rr.getDate("pay_date") != null)
			                {
			                    payDate = sf.format(rr.getDate("pay_date"));
			                }
			            }
			        }
			    }
			    catch (Exception e)
			    {
			        e.printStackTrace();
			    }

			    return payDate;
			}
			
			
			
			public String selectRelation(String key)
			{
				String relaKey="K02";
				try(Connection conn = DataBaseConnection.JAVAConnection()) {
					
					String qq="select * from high_mark_table where LOWER(column_label) LIKE '%" +key+"%' ";
					
						try(PreparedStatement sts = conn.prepareStatement(qq)) {
							
							ResultSet rr = sts.executeQuery();
							
							if(rr.next())
							{
								relaKey= rr.getString("column_value");
							}
							
						} catch (Exception e) {
							// TODO: handle exception
							e.printStackTrace();
						}
					
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return relaKey;
			}
			
			
			
			public String selectRelationWiseTitle( String relation)
			{
				String title="";
				
				if(relation.equals("K01"))
				{
					title="MR";
				}
				else if(relation.equals("K02"))
				{
					title="MR";
				}
				else if(relation.equals("K03"))
				{
					title="MS";
				}
				else if(relation.equals("K04"))
				{
					title="MR";
				}
				else if(relation.equals("K05"))
				{
					title="MS";
				}
				else if(relation.equals("K06"))
				{
					title="MS";
				}
				else if(relation.equals("K07"))
				{
					title="MR";
				}
				else if(relation.equals("K08"))
				{
					title="MS";
				}
				else if(relation.equals("K09"))
				{
					title="MR";
				}
				else if(relation.equals("K010"))
				{
					title="MS";
				}
				else if(relation.equals("K011"))
				{
					title="MS";
				}
				else if(relation.equals("K012"))
				{
					title="MR";
				}
				else if(relation.equals("K013"))
				{
					title="MR";
				}
				else if(relation.equals("K014"))
				{
					title="MR";
				}
				else if(relation.equals("K015"))
				{
					title="MR";
				}
				return title;
						
			}
			 public String selectEmployeeGender( String empCode)
			    {
			    	String gen="";
			    	try(Connection conn = DataBaseConnection.JAVAConnection()) {
			    		
			    		String qq= "select gender from employee  where dsacode='"+empCode+"'";
			    		
			    		try(PreparedStatement st = conn.prepareStatement(qq)) {
			    			
			    			ResultSet rr= st.executeQuery();
			    			
			    			while(rr.next())
			    			{
			    				gen= rr.getString("gender");
			    			}
			    			
			    			
							
						} catch (Exception e) {
							// TODO: handle exception
							e.printStackTrace();
						}
						
					} catch (Exception e) {
						// TODO: handle exception
						e.printStackTrace();
					}
			    	
					return gen;
			    }
}


