package main_package;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.faces.model.SelectItem;

// This class mostly having auto complete related Methods and duplicate methods
public class DataBaseAutoCompAndDuplicate implements Serializable
{
	
	private static final long serialVersionUID = 1L;
	DataBaseMethods obj=new DataBaseMethods();
	
	 public String selectQueWithBranch(String query,int branchId)
		{
			
			if(branchId ==-1)
			{
				
			}
			else
			{
				if(query.contains("where"))
				{
					query=query+" and branch_id='"+branchId+"'";
				}
				else
				{
					query=query+" where branch_id='"+branchId+"'";
				}
			}
			
			//System.out.println(query);
			return query;
		}
	
	 
	 
	 public ArrayList<String> getDuplicateDetails(int branchId, String columnName, String value, Connection conn) {

			ArrayList<String> list = new ArrayList<>();

			if (!"mobile".equalsIgnoreCase(columnName) && !"PAN".equalsIgnoreCase(columnName)
					&& !"unique_id".equalsIgnoreCase(columnName)) {
				return list;
			}

			String query = "SELECT shareholderno, first_applicantName FROM shareholder_info WHERE " + columnName
					+ " = ? AND branch_id = ?";

			try (PreparedStatement ps = conn.prepareStatement(query)) {

				ps.setString(1, value);
				ps.setInt(2, branchId);

				try (ResultSet rs = ps.executeQuery()) {
					while (rs.next()) {
						list.add(rs.getString("shareholderno") + " - " + rs.getString("first_applicantName"));
					}
				}

			} catch (Exception e) {
				e.printStackTrace();
			}

			return list;
		}
	 
	 
	 
	// List of Loan No. by Loan Type like PL,GL,VL,PPL for Auto Complete without approve
	public List<String> showLoanNoByLoanTypeForAppWithoutApprove(int branchId,String name, String loanType, String userid,Connection conn) 
	{
		ArrayList<String> list = new ArrayList<String>();
		String query="";
		try
		{
			Statement st = conn.createStatement();
			
			String dsaCode=obj.dsaCodeByEmpUserId(userid, conn);
			String empDataStatus=obj.checkSMessageApiDetailFromTable("employee_wise_data", "",conn);
			if(obj.userType().equalsIgnoreCase("admin") || dsaCode==null || dsaCode.equalsIgnoreCase("") || empDataStatus.equalsIgnoreCase("inactive"))
			{ 
				if(loanType.equals("grouploan"))
				{
					query = "select sub_loan_no,shareholder_info.first_applicantName from "+loanType+" INNER JOIN shareholder_info on "+loanType+".teamLeader=shareholder_info.shareholderno  where (sub_loan_no like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%') and "+loanType+".paidStatus='unpaid' and shareholder_info.branch_id='"+branchId+"'";
				}
				else
				{
					query = "select loanNo,shareholder_info.first_applicantName from "+loanType+" INNER JOIN shareholder_info on "+loanType+".shareholderno=shareholder_info.shareholderno  where (loanNo like '%"+ name + "%' or shareholder_info.first_applicantName like '"+ name + "%') and "+loanType+".paidStatus='unpaid'  and shareholder_info.branch_id='"+branchId+"'";
				}
				query+="order by loanNo asc limit 8";
			}
			else
			{
				if(loanType.equals("grouploan"))
				{
					query = "select sub_loan_no,shareholder_info.first_applicantName from "+loanType+" INNER JOIN shareholder_info on "+loanType+".teamLeader=shareholder_info.shareholderno  where (sub_loan_no like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%') and "+loanType+".paidStatus='unpaid' and dsacode='"+dsaCode+"' and shareholder_info.branch_id='"+branchId+"'";
				}
				else
				{
					query = "select loanNo,shareholder_info.first_applicantName from "+loanType+" INNER JOIN shareholder_info on "+loanType+".shareholderno=shareholder_info.shareholderno  where (loanNo like '%"+ name + "%' or shareholder_info.first_applicantName like '"+ name + "%') and "+loanType+".paidStatus='unpaid' and dsacode='"+dsaCode+"' and shareholder_info.branch_id='"+branchId+"'";
				}
				query+=" order by loanNo asc limit 8";
			}
			ResultSet rr = st.executeQuery(query);
			
			System.out.println("Query :"+query);
			
			while (rr.next())
			{
				if(loanType.equals("grouploan"))
				{
					String name1= rr.getString("sub_loan_no")+"/"+rr.getString("shareholder_info.first_applicantName");
					list.add(name1);
				}
				else
				{
					String name1= rr.getString("loanNo")+"/"+rr.getString("shareholder_info.first_applicantName");
					list.add(name1);
				}
			}
		}
		
		
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	// return all shareholder no list which are having vehicle loan and search via like query
	public List<String> shareHolderAllNoFromVechile(int branchId,String name)
	{
		ArrayList<String> list = new ArrayList<String>();
		try(Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = conn.createStatement();
			String query = "select * from vechicleloan where shareholderno  like '%"+ name + "%'  ";
			ResultSet rr = st.executeQuery(selectQueWithBranch(query,branchId));
			while (rr.next())
			{
				String name1 = rr.getString("shareholderno");
				list.add(name1);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}



	public List<String> z(int branchId, String name) {
	    List<String> list = new ArrayList<>();

	    String query = "SELECT shareholderno, first_applicantName "
	                 + "FROM shareholder_info "
	                 + "WHERE branch_id = ? "
	                 + "AND (shareholderno LIKE ? OR first_applicantName LIKE ?) "
	                 + "LIMIT 10";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query)) {

	        ps.setInt(1, branchId);
	        ps.setString(2, "%" + name + "%");
	        ps.setString(3, "%" + name + "%");

	        try (ResultSet rs = ps.executeQuery()) {
	            while (rs.next()) {
	                String shareholderNo = rs.getString("shareholderno");
	                String applicantName = rs.getString("first_applicantName");

	                String result = shareholderNo + " / " + applicantName;
	                list.add(result);
	            }
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return list;
	}



	
	//  return name and Loan no list via search by Loan Type and   
	public List<String> showLoanNoByLoanTypeActiveLoan(int branchId,String name, String loanType) 
	{
		ArrayList<String> list = new ArrayList<String>();
		String query="";
		if(loanType.equals("grouploan"))
		{
			query = "select * from "+loanType+" INNER JOIN shareholder_info on "+loanType+".teamLeader=shareholder_info.shareholderno  where (sub_loan_no like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%' or shareholder_info.shareholderno like '%"+ name + "%') and "+loanType+".status='Approve' and paidStatus='unpaid' and restructure_status='no' ";
		}
		else
		{
			query = "select * from "+loanType+" INNER JOIN shareholder_info on "+loanType+".shareHolderNo=shareholder_info.shareholderno  where (loanNo like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%' or shareholder_info.shareholderno like '%"+ name + "%') and "+loanType+".status='Approve' and paidStatus='unpaid' and restructure_status='no' ";
		}
		query+=" and shareholder_info.branch_id='"+branchId+"' order by loanNo asc";
		try (Connection conn = DataBaseConnection.JAVAConnection();)
		{
			Statement st = conn.createStatement();
			
			ResultSet rr = st.executeQuery(query);
			while (rr.next())
			{
				if(loanType.equals("grouploan"))
				{
					String name1= rr.getString("sub_loan_no")+"/"+rr.getString("shareholder_info.first_applicantName")+"/"+rr.getString("shareholder_info.shareholderno")+"/"+obj.centerNameById(rr.getString("center"), conn);
					list.add(name1);
				}
				else
				{
					String name1= rr.getString("loanNo")+"/"+rr.getString("shareholder_info.first_applicantName")+"/"+rr.getString("shareholder_info.shareholderno");
					list.add(name1);
				}
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	// List of Deposit No. by Deposit Type like DD,RD,FD for Auto Complete
	public List<String> userAccoutnNoFrequencyWise(int branchId,String name, String searchType, String userid)
	{
		String query="";
		ArrayList<String> list = new ArrayList<String>();
		try (Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = conn.createStatement();
			String dsaCode=obj.dsaCodeByEmpUserId(userid, conn);
			String empDataStatus=obj.checkSMessageApiDetailFromTable("employee_wise_data", "",conn);
			if(obj.userType().equalsIgnoreCase("admin") || dsaCode==null || dsaCode.equalsIgnoreCase("") || empDataStatus.equalsIgnoreCase("inactive"))
			{ 
				query = "select * from customer_info INNER JOIN shareholder_info on "
						+ " customer_info.shareHolderNo=shareholder_info.shareholderno where (user_no like '%"+ name + "%' or shareholder_info.first_applicantName like '"+ name + "%' or shareholder_info.shareholderno like '%"+ name + "%') and "
								+ " customer_info.account_status='active' and scheme_type='"+searchType+"' and shareholder_info.branch_id='"+branchId+"' limit 10";
			}
			else
			{
				query = "select * from customer_info INNER JOIN shareholder_info on "
						+ " customer_info.shareHolderNo=shareholder_info.shareholderno where (user_no like '%"+ name + "%' or shareholder_info.first_applicantName like '"+ name + "%' or shareholder_info.shareholderno like '%"+ name + "%') and "
								+ " customer_info.account_status='active' and scheme_type='"+searchType+"' and dsa_code='"+dsaCode+"' and shareholder_info.branch_id='"+branchId+"' limit 10";
				
			}
			System.out.println(query);
			ResultSet rr = st.executeQuery(query);
			while (rr.next()) 
			{
				String name1 = rr.getString("user_no")+"/"+rr.getString("shareholder_info.first_applicantName")+"-"+rr.getString("shareholder_info.shareholderno");
				list.add(name1);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	// List of Loan No. by Loan Type like PL,GL,VL,PPL for Auto Complete
	public List<String> showLoanNoByLoanType(int branchId,String name, String loanType) 
	{
		System.out.println("value of the loanType :"+loanType);
		
		ArrayList<String> list = new ArrayList<String>();
		String query="";
		if(loanType.equals("grouploan"))
		{
			query = "select * from "+loanType+" INNER JOIN shareholder_info on "+loanType+".teamLeader=shareholder_info.shareholderno  where (loanNo like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%') and "+loanType+".status='Approve' and "+loanType+".paidStatus='unpaid' and shareholder_info.branch_id='"+branchId+"'";
		
		}
		else
		{
			query = "select * from "+loanType+" INNER JOIN shareholder_info on "+loanType+".shareholderno=shareholder_info.shareholderno  where (loanNo like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%') and "+loanType+".status='Approve' and "+loanType+".paidStatus='unpaid' and shareholder_info.branch_id='"+branchId+"' ";
		}
		query+=" limit 25 ";
		try (Connection conn = DataBaseConnection.JAVAConnection();)
		{
			Statement st = conn.createStatement();
			
			ResultSet rr = st.executeQuery(query);
			while (rr.next())
			{
				String name1=rr.getString("loanNo")+"/"+rr.getString("shareholder_info.first_applicantName")+" - "+rr.getString("shareholder_info.father_husband_name");
				list.add(name1);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
		
	
		// List of Loan No. by Loan Type like PL,GL,VL,PPL for Auto Complete
		public List<String> showLoanNoByLoanTypeForApp(int branchId,String name, String loanType, String userid) 
		{
			ArrayList<String> list = new ArrayList<String>();
			String query="";
			try (Connection conn = DataBaseConnection.JAVAConnection();)
			{
				Statement st = conn.createStatement();
				
				String dsaCode=obj.dsaCodeByEmpUserId(userid, conn);
				String empDataStatus=obj.checkSMessageApiDetailFromTable("employee_wise_data", "",conn);
				if(obj.userType().equalsIgnoreCase("admin") || dsaCode==null || dsaCode.equalsIgnoreCase("") || empDataStatus.equalsIgnoreCase("inactive"))
				{ 
					if(loanType.equals("grouploan"))
					{
						query = "select sub_loan_no,shareholder_info.first_applicantName,shareholder_info.shareholderno,father_husband_name,mobile from "+loanType+" INNER JOIN shareholder_info on "+loanType+".teamLeader=shareholder_info.shareholderno  where (sub_loan_no like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%') and "+loanType+".status='Approve' and "+loanType+".paidStatus='unpaid' and shareholder_info.branch_id='"+branchId+"'";
					}
					else
					{
						query = "select loanNo,shareholder_info.first_applicantName,shareholder_info.shareholderno,father_husband_name,mobile from "+loanType+" INNER JOIN shareholder_info on "+loanType+".shareholderno=shareholder_info.shareholderno  where (loanNo like '%"+ name + "%' or shareholder_info.first_applicantName like '"+ name + "%') and "+loanType+".status='Approve' and "+loanType+".paidStatus='unpaid'  and shareholder_info.branch_id='"+branchId+"'";
					}
					query+=" order by loanNo asc limit 8";
				}
				else
				{
					if(loanType.equals("grouploan"))
					{
						query = "select sub_loan_no,shareholder_info.first_applicantName,shareholder_info.shareholderno,father_husband_name,mobile from "+loanType+" INNER JOIN shareholder_info on "+loanType+".teamLeader=shareholder_info.shareholderno  where (sub_loan_no like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%') and "+loanType+".status='Approve' and "+loanType+".paidStatus='unpaid' and "+loanType+".dsacode='"+dsaCode+"' and shareholder_info.branch_id='"+branchId+"'";
					}
					else
					{
						query = "select loanNo,shareholder_info.first_applicantName,shareholder_info.shareholderno,father_husband_name,mobile from "+loanType+" INNER JOIN shareholder_info on "+loanType+".shareholderno=shareholder_info.shareholderno  where (loanNo like '%"+ name + "%' or shareholder_info.first_applicantName like '"+ name + "%') and "+loanType+".status='Approve' and "+loanType+".paidStatus='unpaid' and "+loanType+".dsacode='"+dsaCode+"' and shareholder_info.branch_id='"+branchId+"'";
					}
					query+=" order by loanNo asc limit 8";
				}
				ResultSet rr = st.executeQuery(query);
				while (rr.next())
				{
					if(loanType.equals("grouploan"))
					{
						String name1= rr.getString("sub_loan_no")+"/"+rr.getString("shareholder_info.first_applicantName")+" - "+rr.getString("shareholder_info.shareholderno")
						+" - "+rr.getString("shareholder_info.father_husband_name")+" - "+rr.getString("shareholder_info.mobile");
						list.add(name1);
					}
					else
					{
						String name1= rr.getString("loanNo")+"/"+rr.getString("shareholder_info.first_applicantName")+" - "+rr.getString("shareholder_info.shareholderno")
						+" - "+rr.getString("shareholder_info.father_husband_name")+" - "+rr.getString("shareholder_info.mobile");
						list.add(name1);
					}
				}
			}
			catch (Exception e) 
			{
				e.printStackTrace();
			}
			return list;
		}
				
				
	public List<String> showLoanNoByLoanTypeForEmiDelete(int branchId,String name, String loanType, Connection conn) 
	{
		ArrayList<String> list = new ArrayList<String>();
		String query="";
		Statement st=null;
		ResultSet rr=null;

		if(loanType.equals("grouploan"))
		{
			query = "select sub_loan_no,shareholder_info.first_applicantName,shareholder_info.father_husband_name,shareholder_info.mobile from "+loanType+" INNER JOIN shareholder_info on "+loanType+".teamLeader=shareholder_info.shareholderno  where (sub_loan_no like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%') and "+loanType+".status='Approve' and shareholder_info.branch_id='"+branchId+"'";
		}
		else
		{
			query = "select loanNo,shareholder_info.first_applicantName,shareholder_info.father_husband_name,shareholder_info.mobile from "+loanType+" INNER JOIN shareholder_info on "+loanType+".shareHolderNo=shareholder_info.shareholderno  where (loanNo like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%') and "+loanType+".status='Approve' and shareholder_info.branch_id='"+branchId+"'";
		}
		query+=" order by loanNo asc";
		try 
		{
			st = conn.createStatement();
			
			rr = st.executeQuery(query);
			while (rr.next())
			{
				String lno="";
				if(loanType.equals("grouploan"))
				{
					lno=rr.getString("sub_loan_no");
				}
				else
				{
					lno=rr.getString("loanNo");
				}
				String name1=lno+"/"+rr.getString("shareholder_info.first_applicantName")+"-"+rr.getString("shareholder_info.father_husband_name")+"-"+rr.getString("shareholder_info.mobile");
				list.add(name1);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
	
		return list;
	}
	
	// List of Share Holder No. for Auto Complete
	public List<String> shareHolderAllNo(int branchId,String name)
    {
        ArrayList<String> list = new ArrayList<String>();
        try (Connection conn = DataBaseConnection.JAVAConnection();)
        {
            Statement st = conn.createStatement();
            String query = "select * from shareholder_info where (shareholderno  like '%"+ name + "%' or first_applicantName  like '%"+ name + "%' or  shareholder_info.mobile like '%"+ name + "%' or shareholder_info.unique_id like '%"+ name + "%') and account_status='active'";
            ResultSet rr = st.executeQuery(selectQueWithBranch(query,branchId));
            while (rr.next())
            {
                String name1 = rr.getString("shareholderno")+"/"+rr.getString("first_applicantName")+"/"+rr.getString("father_husband_name")+"/"+rr.getString("mobile")+"/"+rr.getString("unique_id");
                list.add(name1);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return list;
    }
	
	public List<String> shareHolderAllNoWithoutTransfer(int branchId,String name)
    {
        ArrayList<String> list = new ArrayList<String>();
        try (Connection conn = DataBaseConnection.JAVAConnection();)
        {
            Statement st = conn.createStatement();
            String query = "select * from shareholder_info where (shareholderno  like '%"+ name + "%' or first_applicantName  like '%"+ name + "%' or  shareholder_info.mobile like '%"+ name + "%' or shareholder_info.unique_id like '%"+ name + "%') and account_status='active'";
            ResultSet rr = st.executeQuery(query);
            while (rr.next())
            {
                String name1 = rr.getString("shareholderno")+"/"+rr.getString("first_applicantName")+"/"+rr.getString("father_husband_name")+"/"+rr.getString("mobile")+"/"+rr.getString("unique_id");
                list.add(name1);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return list;
    }
	
	public List<String> groupAllNo(int branchId,String name)
	{
		ArrayList<String> list = new ArrayList<String>();
		try (Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = conn.createStatement();
			String query = "SELECT grouploan.loanNo, shareholder_info.first_applicantName, shareholder_info.shareholderno, grouploan.center FROM grouploan INNER JOIN shareholder_info ON shareholder_info.shareholderno = grouploan.teamLeader WHERE (grouploan.loanNo LIKE '%" + name + "%' OR shareholder_info.first_applicantName LIKE '%" + name + "%' OR shareholder_info.shareholderno LIKE '%" + name + "%') AND shareholder_info.branch_id = '" + branchId + "' GROUP BY grouploan.loanNo, shareholder_info.first_applicantName, shareholder_info.shareholderno, grouploan.center";
			ResultSet rr = st.executeQuery(query);
			while (rr.next()) 
			{
				String name1 = rr.getString("loanNo")+"/"+rr.getString("first_applicantName")+"/"+rr.getString("shareholderno")+"/"+obj.centerNameCodeById(rr.getString("center"), conn);
				list.add(name1);
			}
			
			System.out.println("query ys :"+query);
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	// List of Saving Account No. for Auto Complete
	public List<String> savingAccountNo(int branchId,String name)
	{
		ArrayList<String> list = new ArrayList<String>();
		try (Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = conn.createStatement();
			String query = "select * from savingaccount inner join shareholder_info where (accountNo like '%"+ name + "%' or shareholder_info.shareholderno like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%') and shareholder_info.shareholderno=savingaccount.shareHolderNo and accountStatus='open' and shareholder_info.branch_id='"+branchId+"'";
			ResultSet rr = st.executeQuery(query);
			while (rr.next()) 
			{
				String name1 = rr.getString("accountNo")+"/"+rr.getString("shareholder_info.first_applicantName");
				list.add(name1);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}

	// List of Deposit No. for Auto Complete
	public List<String> userAccoutnNo(int branchId,String name, Connection conn)
	{
		ArrayList<String> list = new ArrayList<String>();
		Statement st=null;
		ResultSet rr=null;
		String query="";
		
		try 
		{
			st = conn.createStatement();
			String dsaCode=obj.dsaCodeByEmpUserId(obj.userId(), conn);
			if(obj.userType().equalsIgnoreCase("admin") || dsaCode==null || dsaCode.equalsIgnoreCase(""))
			{
				query = "select * from customer_info INNER JOIN shareholder_info on "
						+ " customer_info.shareHolderNo=shareholder_info.shareholderno where (user_no like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%' or shareholder_info.shareholderno like '%"+ name + "%') and "
								+ " customer_info.account_status='active' and shareholder_info.branch_id='"+branchId+"' limit 10";
			}
			else
			{
				query = "select * from customer_info INNER JOIN shareholder_info on "
						+ " customer_info.shareHolderNo=shareholder_info.shareholderno where (user_no like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%' or shareholder_info.shareholderno like '%"+ name + "%') and "
								+ " customer_info.account_status='active' and customer_info.dsa_code='"+dsaCode+"' and shareholder_info.branch_id='"+branchId+"' limit 10";
			}
			rr = st.executeQuery(query);
			while (rr.next()) 
			{
				String name1 = rr.getString("user_no")+"/"+rr.getString("shareholder_info.first_applicantName")+"/"+rr.getString("shareholder_info.shareholderno");
				list.add(name1);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
	
		return list;
	}

	

	//This Method Return First guarantor from the existing Share Holder Data for The Loan 
	public List<String> shareHolderForFirstGuarantor(int branchId,String name,String shareHolderNo) 
	{
		ArrayList<String> list = new ArrayList<String>();
		try (Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = conn.createStatement();
			String query = "select * from shareholder_info where shareholderno!='"+shareHolderNo+"' and (shareholderno like '%"+ name + "%' or first_applicantName like '%"+ name + "%')  ";
			ResultSet rr = st.executeQuery(selectQueWithBranch(query,branchId));
			while (rr.next()) 
			{
				String name1 = rr.getString("shareholderno")+"/"+rr.getString("first_applicantName");
				list.add(name1);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}

	//This Method Return Second guarantor from the existing Share Holder Data for The Loan 
	public List<String> shareHolderForSecondGuarantor(int branchId,String name,String shareHolderNo, String guarantor1) 
	{
		ArrayList<String> list = new ArrayList<String>();
		try (Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = conn.createStatement();
			String query = "select * from shareholder_info where shareholderno not in ('"+shareHolderNo+"','"+guarantor1+"') and (shareholderno like '%"+ name + "%' or first_applicantName like '%"+ name + "%')  ";
			ResultSet rr = st.executeQuery(selectQueWithBranch(query,branchId));
			while (rr.next()) 
			{
				String name1 = rr.getString("shareholderno")+"/"+rr.getString("first_applicantName");
				list.add(name1);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}

	public List<String> rd_FD_NoOfUser(int branchId,String name,String selectedScheme,String shareHolderNo) 
	{
		ArrayList<String> list = new ArrayList<String>();
		try (Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = conn.createStatement();
			String query = "select * from customer_info where scheme_type='"+selectedScheme+"' and shareHolderNo='"+shareHolderNo+"' and user_no like '%"+ name + "%' and account_status='active' limit 10";
			ResultSet rr = st.executeQuery(selectQueWithBranch(query,branchId));
			while (rr.next()) 
			{
				String name1 = rr.getString("user_no");
				list.add(name1);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	public List<String> userAccountNoForSavingAccount(int branchId,String name, String userid) 
	{
		ArrayList<String> list = new ArrayList<String>();
		try (Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = conn.createStatement();
			String query = "select * from savingaccount INNER JOIN shareholder_info on "
					+ " savingaccount.shareHolderNo=shareholder_info.shareholderno where (savingaccount.accountNo like '%"+name+"%' or savingaccount.shareHolderNo like '%"+name+"%' or shareholder_info.first_applicantName like '%"+name+"%') and savingaccount.accountStatus='open' "
							+ " and shareholder_info.branch_id='"+branchId+"' limit 10";
			ResultSet rr = st.executeQuery(query);
			while (rr.next()) 
			{
				String name1 =  rr.getString("savingaccount.accountNo")+"/"+rr.getString("shareholder_info.first_applicantName")+"-"+rr.getString("shareholder_info.father_husband_name");
				list.add(name1);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}

	public List<String> userAccountNoForSavingAccountForWithdrawl(int branchId,String name) 
	{
		ArrayList<String> list = new ArrayList<String>();
		try (Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = conn.createStatement();
			String query = "select * from savingaccount INNER JOIN shareholder_info on "
					+ " savingaccount.shareHolderNo=shareholder_info.shareholderno where (savingaccount.accountNo like '%"+name+"%' or shareholder_info.first_applicantName like '%"+name+"%') and (savingaccount.accountStatus='open' or savingaccount.accountStatus='close') "
							+ " and shareholder_info.branch_id='"+branchId+"' limit 10";
			ResultSet rr = st.executeQuery(query);
			while (rr.next()) 
			{
				String name1 = rr.getString("savingaccount.accountNo")+"/"+rr.getString("shareholder_info.first_applicantName")+"-"+rr.getString("shareholder_info.father_husband_name");
				list.add(name1);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	public ArrayList<SelectItem> allLoanList(String loanCategory, String shareHolderNo)
	{
		String query="";
			ArrayList<SelectItem> loanList=new ArrayList<>();
			try(Connection conn=DataBaseConnection.JAVAConnection()) 
			{
			   Statement stt=conn.createStatement();
			   if(loanCategory.equals("grouploan"))
			   {
				   query="select * from "+loanCategory+" where teamLeader='"+shareHolderNo+"' and status='Approve'";
			   }
			   else
			   {
				   query="select * from "+loanCategory+" where shareholderno='"+shareHolderNo+"' and status='Approve'";  
			   }
	
			   ResultSet ss=stt.executeQuery(query);
			   while(ss.next())
			   {
				   SelectItem info=new SelectItem();
				   info.setLabel(ss.getString("loanNo"));
				   info.setValue(ss.getString("loanNo"));
				   loanList.add(info);
			   }
			} 
			catch (Exception e) 
			{
				 e.printStackTrace();
			}
			return loanList;

	}

	public boolean checkTdsDeduction(int branchId,int selectYear) 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select * from tds_deduction where year='"+selectYear+"' ";
			ResultSet rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return true;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return false;
	}
	public List<String> loanList(int branchId,String name)
	{
		ArrayList<String> list = new ArrayList<String>();
		try (Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = conn.createStatement();
			String query = "select * from personalloan INNER JOIN shareholder_info on "
					+ " personalloan.shareHolderNo=shareholder_info.shareholderno where (loanNo like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%') and "
					+ " personalloan.status='Approve' and shareholder_info.branch_id='"+branchId+"' limit 10";
			ResultSet rr = st.executeQuery(query);
			while (rr.next()) 
			{
				String name1 = rr.getString("loanNo")+"/"+rr.getString("shareholder_info.first_applicantName")+" - "+rr.getString("shareholder_info.shareholderno");
				list.add(name1);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	public List<String> loanListFromMemberList(int branchId, String name) {
	    List<String> list = new ArrayList<>();

	    String query = "SELECT shareholderno, first_applicantName "
	                 + "FROM shareholder_info "
	                 + "WHERE branch_id = ? "
	                 + "AND (shareholderno LIKE ? OR first_applicantName LIKE ?) "
	                 + "LIMIT 10";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query)) {

	        ps.setInt(1, branchId);
	        ps.setString(2, "%" + name + "%");
	        ps.setString(3, "%" + name + "%");

	        try (ResultSet rs = ps.executeQuery()) {
	            while (rs.next()) {
	                String shareholderNo = rs.getString("shareholderno");
	                String applicantName = rs.getString("first_applicantName");

	                String result = shareholderNo + " / " + applicantName;
	                list.add(result);
	            }
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return list;
	}

	
	public ArrayList<SelectItem> groupLoanListCenterWise(int branchId,String center)
	{
		ArrayList<SelectItem> list = new ArrayList<SelectItem>();
		try (Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = conn.createStatement();
			String query = "select * from grouploan inner join shareholder_info where shareholder_info.shareholderno=grouploan.teamLeader and grouploan.status='Approve' and paidStatus='unpaid' and grouploan.center='"+center+"' and shareholder_info.branch_id='"+branchId+"' ";
			ResultSet rr = st.executeQuery(query);
			while (rr.next()) 
			{
				SelectItem ss=new SelectItem();
				ss.setValue(rr.getString("sub_loan_no"));
				ss.setLabel(rr.getString("sub_loan_no")+"/"+rr.getString("shareholder_info.first_applicantName")+" - "+rr.getString("shareholder_info.shareholderno")+" - "+rr.getString("shareholder_info.father_husband_name"));
				
				list.add(ss);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return list;
	}
	
	 public List<String> depositList(int branchId,String name)
	    {
	        ArrayList<String> list = new ArrayList<String>();
	        try (Connection conn = DataBaseConnection.JAVAConnection();)
	        {
	            Statement st = conn.createStatement();
	            String query = "select * from customer_info  INNER JOIN shareholder_info on "
	                    + " customer_info.shareHolderNo=shareholder_info.shareholderno where (user_no like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%' or shareholder_info.mobile like '%"+ name + "%' or shareholder_info.unique_id like '%"+ name + "%' or shareholder_info.shareholderno like '%"+ name + "%') and "
	                    + " customer_info.paid_status='unpaid' and shareholder_info.branch_id='"+branchId+"' limit 10";
	            ResultSet rr = st.executeQuery(query);
	            while (rr.next())
	            {
	                String name1 = rr.getString("user_no")+"/"+rr.getString("shareholder_info.first_applicantName")+" - "+rr.getString("shareholder_info.shareholderno");
	                list.add(name1);
	            }
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	        return list;
	    }

	public boolean checkContactNoDuplicacy(int branchId,String mobile, Connection conn) 
	{
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select mobile from shareholder_info where mobile='"+mobile+"' ";
			rr=st.executeQuery(selectQueWithBranch(qq,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 e2) {
				// TODO: handle exception
			}
		}
		return false;
	}

	public boolean checkCenterInAllTable(String id) 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq1="select center from shareholder_info where center='"+id+"' ";
			ResultSet rr1=st.executeQuery(qq1);
			if(rr1.next())
			{
				return true;
			}
			
			String qq2="select center from savingaccount where center='"+id+"' ";
			ResultSet rr2=st.executeQuery(qq2);
			if(rr2.next())
			{
				return true;
			}
			
			String qq3="select center from personalloan where center='"+id+"' ";
			ResultSet rr3=st.executeQuery(qq3);
			if(rr3.next())
			{
				return true;
			}
			
			String qq4="select center from grouploan where center='"+id+"' ";
			ResultSet rr4=st.executeQuery(qq4);
			if(rr4.next())
			{
				return true;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return false;
	}

	public boolean checkLoanNoDuplicacy(String loanNo, String tableName) 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select loanNo from "+tableName+" where loanNo='"+loanNo+"' ";
			ResultSet rr=st.executeQuery(qq);
			if(rr.next())
			{
				return true;
			}
		}
		catch(Exception e)
		{
			
		}
		return false;
	}
	
	// List of Loan No. by Loan Type like PL,GL,VL,PPL for Auto Complete PAID / UNPAID
		public List<String> showLoanNoByLoanTypePaidUnpaid(String name, String loanType, String userid) 
		{
			ArrayList<String> list = new ArrayList<String>();
			String query="";
			try (Connection conn = DataBaseConnection.JAVAConnection();)
			{
				Statement st = conn.createStatement();
				
				String dsaCode=obj.dsaCodeByEmpUserId(userid, conn);
				String empDataStatus=obj.checkSMessageApiDetailFromTable("employee_wise_data", "",conn);
				if(obj.userType().equalsIgnoreCase("masterAdmin") || dsaCode==null || dsaCode.equalsIgnoreCase("") || empDataStatus.equalsIgnoreCase("inactive"))
				{ 
					if(loanType.equals("grouploan"))
					{
						query = "select sub_loan_no,shareholder_info.first_applicantName,shareholder_info.shareholderno,father_husband_name,mobile from "+loanType+" INNER JOIN shareholder_info on "+loanType+".teamLeader=shareholder_info.shareholderno  where (sub_loan_no like '%"+ name + "%' or shareholder_info.shareholderno like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%' or shareholder_info.mobile like '%"+ name + "%') and "+loanType+".status='Approve'  ";
					}
					else
					{
						query = "select loanNo,shareholder_info.first_applicantName,shareholder_info.shareholderno,father_husband_name,mobile from "+loanType+" INNER JOIN shareholder_info on "+loanType+".shareholderno=shareholder_info.shareholderno  where (loanNo like '%"+ name + "%' or shareholder_info.shareholderno like '%"+ name + "%' or shareholder_info.first_applicantName like '"+ name + "%' or shareholder_info.mobile like '%"+ name + "%') and "+loanType+".status='Approve' ";
					}
					query+=" order by loanNo asc limit 20";
				}
				else
				{
					if(loanType.equals("grouploan"))
					{
						query = "select sub_loan_no,shareholder_info.first_applicantName,shareholder_info.shareholderno,father_husband_name,mobile from "+loanType+" INNER JOIN shareholder_info on "+loanType+".teamLeader=shareholder_info.shareholderno  where (sub_loan_no like '%"+ name + "%' or shareholder_info.first_applicantName like '%"+ name + "%') and "+loanType+".status='Approve' and "+loanType+".dsacode='"+dsaCode+"' ";
					}
					else
					{
						query = "select loanNo,shareholder_info.first_applicantName,shareholder_info.shareholderno,father_husband_name,mobile from "+loanType+" INNER JOIN shareholder_info on "+loanType+".shareholderno=shareholder_info.shareholderno  where (loanNo like '%"+ name + "%' or shareholder_info.first_applicantName like '"+ name + "%') and "+loanType+".status='Approve' and "+loanType+".dsacode='"+dsaCode+"' ";
					}
					query+=" order by loanNo asc limit 20";
				}
				
				System.out.println(query);
				//System.out.println(query);
				ResultSet rr = st.executeQuery(query);
				while (rr.next())
				{
					if(loanType.equals("grouploan"))
					{
						String name1= rr.getString("sub_loan_no")+"/"+rr.getString("shareholder_info.first_applicantName")+" - "+rr.getString("shareholder_info.shareholderno")
						+" - "+rr.getString("shareholder_info.father_husband_name")+" - "+rr.getString("shareholder_info.mobile");
						list.add(name1);
					}
					else
					{
						String name1= rr.getString("loanNo")+"/"+rr.getString("shareholder_info.first_applicantName")+" - "+rr.getString("shareholder_info.shareholderno")
						+" - "+rr.getString("shareholder_info.father_husband_name")+" - "+rr.getString("shareholder_info.mobile");
						System.out.println(rr.getString("loanNo")+"/"+rr.getString("shareholder_info.first_applicantName"));
						
						list.add(name1);
					}
				}
			}
			catch (Exception e) 
			{
				e.printStackTrace();
			}
			return list;
		}

	
}
