package main_package;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import javax.faces.model.SelectItem;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpSession;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.json.simple.JSONObject;

import group_head_package.IncomeList;



public class DataBaseMethodsHemant implements Serializable 
{
    private static final long serialVersionUID = 1L;
    DataBaseMethods db = new DataBaseMethods();
    SimpleDateFormat sdfForDb = new SimpleDateFormat("yyyy-MM-dd");

    String onlineFileViewPath = "";
	String onlineFileUploadPath = "";
	
    private CollectionAnalysisBean collectionAnalysisBean;
    String branchUserId;
    int branchId;
    ArrayList<SelectItem>centerList;
	List<SelectItem> branchList = new ArrayList<SelectItem>();
	String userType;
	boolean disAll;

	ArrayList<SelectItem> dsaList; 
	DataBaseMethods obj = new DataBaseMethods();
	DecimalFormat df = new DecimalFormat("##.##");
	String reportType, applicantName, rdDate, status, dt, dt1, dateRangeAmount, penaltyField = "no", dsaCode;
	Date endDate = new Date(), initialDt, initialDt1, startDate = new Date();
	Boolean tbl1 = false, showPenaltyField, showCenter;
	ArrayList<LoanInfo> collectionList;
	ArrayList<SelectItem> loanTypeList;
	double amount = 0.00, count1, count2, count3, sumAmount, sumAmountDateRange, dailyCollection;
	ArrayList<String> list;
	double amountToPaid, penaltyDue, amountPaid, dueAmount, totalPenalty, duePenalty, paidPenalty, amountDateRange, 
		   rdAmountToPaid, rdAmountPaid, rdAmountDateRange, amounToPaidDateRange, rdAmountToPaidDateRange, dailyCall = 0;
	DbYashwant dby = new DbYashwant();
	ArrayList<String> list1;
	ArrayList<LoanInfo> loanEMIList, loanEMIList1;
	ArrayList<String> list2 = new ArrayList<>();
	ArrayList<InstallmentInfo> rdFdList = new ArrayList<>();
	String center, paidStatus, frequency;
	double installAmount;
	
	
	
	public UserInfo firmDetails() 
	{
		UserInfo uu=new UserInfo();
		try(Connection con=DataBaseConnection.JAVAConnection();) 
		{  
			Statement st=con.createStatement();
			String	qq="select * from firm_detail LIMIT 1 ";
			//System.out.println(qq);
			ResultSet rs=st.executeQuery(qq);
			if(rs.next())
			{   
				
				uu.setName(rs.getString("name"));
			
				uu.setLogo(rs.getString("logo"));				
				
			}
		}
		catch (Exception e)
		{
			 e.printStackTrace();fileCreation(e.getMessage());
		} 
		return uu;
	}	
	
	public ArrayList<RdDetails> fetchAllRdDetailsByShareHolderNo(
	        String shareholderNo) {

	    ArrayList<RdDetails> rdList = new ArrayList<>();

	    String qq = "SELECT " +
	                "user_no, " +
	                "scheme_type, " +
	                "terms, " +
	                "installment_amount, " +
	                "interest, " +
	                "maturity_value, " +
	                "maturity_date, " +
	                "frequency " +
	                "FROM customer_info " +
	                "WHERE shareHolderNo = ? " +
	                "AND account_status = 'active' " +
	                "AND paid_status = 'unpaid' " +
	                "AND scheme_type = 'RD' " ;
	               

	    try (Connection con = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = con.prepareStatement(qq)) {

	        ps.setString(1, shareholderNo);
	     

	        try (ResultSet rs = ps.executeQuery()) {

	            while (rs.next()) {

	                RdDetails rd = new RdDetails();

	                rd.setRdNumber(rs.getString("user_no"));
	                rd.setSchemeType(rs.getString("scheme_type"));
	                rd.setTerms(rs.getString("terms"));
	                rd.setInstallmentAmount(rs.getString("installment_amount"));
	                rd.setInterestRate(rs.getString("interest"));
	                rd.setMaturityValue(rs.getString("maturity_value"));
	                rd.setMaturityDate(rs.getString("maturity_date"));
	                rd.setFrequency(rs.getString("frequency"));

	                rdList.add(rd);
	            }
	        }

	    } catch (SQLException e) {

	        e.printStackTrace();

	        try {
	            fileCreation(e.getMessage());
	        } catch (Exception logException) {
	            logException.printStackTrace();
	        }
	    }

	    return rdList;
	}
	

	

	
	public String RdFdNoByshareHolderNo(int branchId,String accno)
    {
        try (Connection con = DataBaseConnection.JAVAConnection();)
        {
            Statement st = con.createStatement();
            String qq = "select user_no from customer_info where shareHolderNo='"+accno+"'";
            
            ResultSet rs = st.executeQuery(selectQueWithBranch(qq,branchId));
            if(rs.next())
            {
               return rs.getString("user_no");
            }
        }
        catch (Exception e) {
             e.printStackTrace();fileCreation(e.getMessage());
        }
        return "";
    }
	
	public double salaryAmountOfEmp(int branchId,String selectedEmp, String type)
	{
		ArrayList<IncomeList> list=new ArrayList<>();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select sum(amount) from salary_ledger where empId='"+selectedEmp+"' and type='"+type+"' AND branch_id='"+branchId+"' ";
			// // //System.out.println(query);
			ResultSet rr=st.executeQuery(selectQueWithBranch(query,branchId));
			if(rr.next())
			{
				return rr.getDouble("sum(amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}
	public String selectQueWithBranch(String query,int branchId)
	{
		if(branchId==-1)
		{
			
			if(query.contains("where"))
			{
				query=query+" and "+new DataBaseMethods().branchBuilder();
			}
			else
			{
				query=query+" where"+new DataBaseMethods().branchBuilder();
			}
		}
		else
		{
			if(query.contains("where"))
			{
				query=query+" and branch_id='"+branchId+"'";
			}
			else
			{
				query=query+" where branch_id='"+branchId+"'";
			}
		}
		
		//System.out.println(query);
		return query;
	}
	
	
	public String loanTypeByLoanNo(String loanNo)
	{
		if (loanNo.contains("PL")) 
		{
			return "personalloan";
		}
		else if (loanNo.contains("GL"))
		{
			return "grouploan";
		}
		else if (loanNo.contains("SL"))
		{
			return "secure_loan";
		}
		else if (loanNo.contains("BL"))
		{
			return "business_loan";
		}
		return "";
	}
	
	
	public ArrayList<UserInfo> centerWiseCollectionDetail(int branchId,
            Date searchDate, Date endDate, String centerId)
    {
		
		int receipt_no=0;
        ArrayList<UserInfo> list = new ArrayList<>();

        String sdt = sdfForDb.format(searchDate);
        String edt = sdfForDb.format(endDate);

        try(Connection conn = DataBaseConnection.JAVAConnection())
        {
            Statement st = conn.createStatement();

            String qq =
                    "SELECT lp.loanNo,lp.amount,lp.penalty,lp.pay_date,"
                  + "lp.receipt_no,lp.emi_no,"
                  + "gl.teamLeader,"
                  + "sh.first_applicantName "
                  + "FROM loanpayment lp "
                  + "INNER JOIN grouploan gl "
                  + "ON lp.loanNo=gl.sub_loan_no "
                  + "INNER JOIN shareholder_info sh "
                  + "ON sh.shareholderno=gl.teamLeader "
                  + "WHERE lp.pay_date>='"+sdt+"' "
                  + "AND lp.pay_date<='"+edt+"' "
                  + "AND lp.receipt_status='approved' "
                  + "AND lp.chequeStatus='cleared' "
                  + "AND gl.center='"+centerId+"' ";

            if(branchId!=-1)
            {
                qq += " AND gl.branch_id='"+branchId+"' ";
            }

            qq += " ORDER BY sh.first_applicantName";

            System.out.println(qq);

            ResultSet rs = st.executeQuery(qq);

            int count = 1;
            Set<String> rdProcessed = new HashSet<>();

            while(rs.next())
            {
                UserInfo uu = new UserInfo();

                uu.setSno(count++);
                uu.setShareHolderNo(rs.getString("teamLeader"));
                uu.setName(rs.getString("first_applicantName"));
                uu.setLoanNo(rs.getString("loanNo"));

                double loanAmt = rs.getDouble("amount");
                double penalty = rs.getDouble("penalty");

                uu.setLoanAmount(loanAmt);
                uu.setPenalty(penalty);

//                /* RD Amount */
//
//                double rdAmt = 0;
//
//                Statement st2 = conn.createStatement();
//
//                String rdQuery =
//                        "SELECT SUM(pr.amount) amount "
//                      + "FROM payment_receipt pr "
//                      + "INNER JOIN customer_info ci "
//                      + "ON pr.user_no=ci.user_no "
//                      + "WHERE ci.shareHolderNo='"
//                      + rs.getString("teamLeader")
//                      + "' "
//                      + "AND pr.receipt_status='approved' "
//                      + "AND pr.chequeStatus='cleared' "
//                      + "AND pr.date>='"+sdt+"' "
//                      + "AND pr.date<='"+edt+"' ";
//
//                if(branchId!=-1)
//                {
//                    rdQuery += " AND pr.branch_id='"+branchId+"'";
//                }
//
//                ResultSet rdRs = st2.executeQuery(rdQuery);
//
//                if(rdRs.next())
//                {
//                    rdAmt = rdRs.getDouble("amount");
//                     receipt_no = (int) rdRs.getDouble("receipt_no");
//                }
//
//                uu.setRdAmount(rdAmt);
//
//                uu.setReceiptNo(rs.getString("receipt_no"));
//                uu.setEmiNo(rs.getString("emi_no"));
//                uu.setPayDate(rs.getDate("pay_date"));
//
//                uu.setTotalAmount(
//                        loanAmt
//                        + rdAmt
//                        + penalty);
                
                /* RD Amount */

                double rdAmt = 0;
                String rdReceiptNo = "";


                String shareHolderNo = rs.getString("teamLeader");

                if (!rdProcessed.contains(shareHolderNo))
                {
                    Statement st2 = conn.createStatement();

                    String rdQuery =
                            "SELECT SUM(pr.amount) AS amount, "
                          + "MIN(pr.receipt_no) AS receipt_no "
                          + "FROM payment_receipt pr "
                          + "INNER JOIN customer_info ci "
                          + "ON pr.user_no=ci.user_no "
                          + "WHERE ci.shareHolderNo='"
                          + shareHolderNo
                          + "' "
                          + "AND pr.receipt_status='approved' "
                          + "AND pr.chequeStatus='cleared' "
                          + "AND pr.date>='"+sdt+"' "
                          + "AND pr.date<='"+edt+"' ";

                    if(branchId != -1)
                    {
                        rdQuery += " AND pr.branch_id='"+branchId+"'";
                    }

                    ResultSet rdRs = st2.executeQuery(rdQuery);

                    if(rdRs.next())
                    {
                        rdAmt = rdRs.getDouble("amount");
                        rdReceiptNo = rdRs.getString("receipt_no");
                    }

                    rdRs.close();
                    st2.close();

                    // Is shareholder/center ka RD dobara nahi aayega
                    rdProcessed.add(shareHolderNo);
                }

                uu.setRdAmount(rdAmt);

                uu.setReceiptNo(rs.getString("receipt_no"));
                uu.setEmiNo(rs.getString("emi_no"));
                uu.setPayDate(rs.getDate("pay_date"));

                uu.setTotalAmount(
                        loanAmt
                        + rdAmt
                        + penalty
                );
                
                System.out.println(
                        "Center = " + uu.getCenterName()
                        + "  ID = " + uu.getCenterId());

                list.add(uu);

               }

            rs.close();
            st.close();

        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

        return list;
    }
	
    
    public double getTargetAmount()
    {
    	Calendar cal = Calendar.getInstance();

    	// Start Date → today 00:00:00
    	cal.setTime(new Date());
    	cal.set(Calendar.HOUR_OF_DAY, 0);
    	cal.set(Calendar.MINUTE, 0);
    	cal.set(Calendar.SECOND, 0);
    	cal.set(Calendar.MILLISECOND, 0);
    	startDate = cal.getTime();

    	// End Date → today 00:00:00
    	cal.setTime(new Date());
    	cal.set(Calendar.HOUR_OF_DAY, 0);
    	cal.set(Calendar.MINUTE, 0);
    	cal.set(Calendar.SECOND, 0);
    	cal.set(Calendar.MILLISECOND, 0);
    	endDate = cal.getTime();
    	
    	penaltyField = "no";
    	frequency = "all";
    	reportType = "personalloan";
    	dsaCode = "all";
    	center = "all";
    	branchId = -1;
    	
    	try 
    	{
			search();
		} 
    	catch (ParseException e) 
    	{
    		e.printStackTrace();
		}
    	
    	System.out.println("collection list hit"+collectionList.size());
    	for(LoanInfo list: collectionList)
    	{
    	   installAmount += list.getInstallAmount();
    	   System.out.println(list.getInstallAmount());
    	   System.out.println("noob");
    	}
    	
    	return installAmount;
    }
    
    
	
    public void search() throws ParseException
	{    	    	
		if(branchId != 0)
		{
			Connection conn = DataBaseConnection.JAVAConnection();
			dt = new SimpleDateFormat("dd-MM-yyyy").format(endDate);
			dt1 = new SimpleDateFormat("dd-MM-yyyy").format(startDate);
			
			if(startDate.after(endDate))
			{
				FacesContext fc = FacesContext.getCurrentInstance();
				fc.addMessage(null, new FacesMessage("Start Date Must Be Before End Date"));			
			}
			
			else
			{
				if(reportType.equalsIgnoreCase("grouploan"))
				{
					showCenter = true;
				}
				else
				{
					showCenter = false;
				}
				
				if(reportType.equals("Loan") || reportType.equals("personalloan") || reportType.equals("grouploan") || 
				   reportType.equals("secure_loan"))
				{
					System.out.println(reportType);
					tbl1 = true;
					int sno = 1;
					collectionList = new ArrayList<>();
					loanTypeList = obj.loanTypeList(conn);
		            
		            count1 = 0.00;
		            count2 = 0.00;
		            count3 = 0.00;
		            dailyCollection = 0.00;
		            dailyCall = 0;
					list = dby.collectionlist(endDate, loanTypeList, reportType, branchId, dsaCode, center, conn, frequency);
					
					for(int i = 0; i < list.size(); i++)
					{
						Date emiDueDate = null;
						list1 = new ArrayList<>();
						loanEMIList = obj.loanInstallment(reportType,list.get(i),conn);
			   		
				   		if(list.get(i).equalsIgnoreCase("PL-10100153"))
				   		{
				   			System.out.println("loan emi list size is"+loanEMIList.size());
				   		}
				   		
				   		amountPaid = obj.amountPaid(list.get(i),endDate,conn);
				   		amountDateRange = obj.amountWithInDateRange(list.get(i),startDate,endDate,conn);
				   		String shrHolderNo = obj.shareHolderNoByLoanNo(list.get(i));
				   		UserInfo ub = obj.memberBasicDetail(shrHolderNo, conn);
				   		applicantName = ub.getName();
				   		double instlAmount = 0.00;
				   		int count = 0;
				   		amountToPaid = 0.00;
				   		paidPenalty = 0.00;
				   		duePenalty = 0.00;
				   		amounToPaidDateRange = 0.00;
				   		status = "";
				   		
				   		for(LoanInfo ff:loanEMIList)
				   		{
					   		try
					   		{
					   			initialDt = new SimpleDateFormat("dd/MM/yyyy").parse(ff.getStrInstlmntDate());	
	   				   		} 
					   		catch (ParseException e) 
					   		{
					   			e.printStackTrace();
							}
					   		
					   		if (emiDueDate == null && (initialDt.before(endDate) || initialDt.equals(endDate))) 
					   		{
					   	        emiDueDate = initialDt;
					   	    }
					   		
				   			if((initialDt.after(startDate) || initialDt.equals(startDate))&& (initialDt.before(endDate) || 
				   				initialDt.equals(endDate)))
				   			{
				   			   amounToPaidDateRange = amounToPaidDateRange + ff.getInstallAmount();
				   			}
				   		
				   			if(initialDt.before(endDate) || initialDt.equals(endDate))
				   			{
				   				String dt = ff.getStrInstlmntDate();
				   				amountToPaid = amountToPaid + ff.getInstallAmount();
				   				list1.add(dt);
				   			}
				   			
				   			if(count == 0  && initialDt.equals(startDate))
				   			{    
				   				instlAmount = ff.getInstallAmount();
				   				// // System.out.println(instlAmount);
				   				count = count + 1;
				   			}
					   	}
					   			
	  					String loanNo=list.get(i);
	  					String loanType=reportType;
	  					
	  					/*if(loanNo.contains("PL") || loanNo.contains("LA"))
	  					{
	  						loanType="personalloan";
	  					}
	  					else
	  					{
	  						loanType="grouploan";
	  					}*/
							   	
		  				if(penaltyField.equalsIgnoreCase("yes"))
		  				{
		  					showPenaltyField=true;
		  					double penaltyAmount,penaltyPaid,penaltyDiscount;
		  					penaltyDue=0;
					   		penaltyAmount=obj.calculatePenaltyAndChequeBounceCharges(loanNo, "Penalty", loanType, conn);
			   				penaltyPaid=obj.totalPaidPenaltyAndChequeBounceCharges(loanNo, "Penalty", conn);
			   				penaltyDiscount=obj.discountOnPenaltyByLoanNo(loanNo);
			   				penaltyDue=penaltyAmount-penaltyPaid;
				   			penaltyAmount=Double.parseDouble(df.format(penaltyAmount));
				   			penaltyPaid=Double.parseDouble(df.format(penaltyPaid));
				   			penaltyDue=Double.parseDouble(df.format(penaltyDue));
		  				}
					  				
					   		if(amountToPaid>amountPaid )
						   		{
					   			 
						   			status="Due";
						   			dueAmount=amountToPaid-amountPaid;
						   			
						   		}
					   		else
				   				if(amountPaid==0.00)
				   				{
				   					status="No Amount Paid";
				   					dueAmount=0.00;
				   				}
					   		else
					   			if(amountToPaid-amountPaid==0.00)
						   			{
						   				status="Clear";
						   				dueAmount=0.00;
						   			}
					   			
				   			else
					   			{
					   				status="Advance";
					   				dueAmount=amountPaid-amountToPaid;
					   			}
					   		 
					   		 
				   		if(amounToPaidDateRange>amountDateRange )
					   		{
					   			dateRangeAmount=String.valueOf(amounToPaidDateRange-amountDateRange)+" (D)";
					   		}
				   		else
			   				if(amountDateRange==0.00)
			   				{
			   					dateRangeAmount=String.valueOf(0.00)+" (NILL)";
			   				}
				   		else
				   			if(amounToPaidDateRange-amountDateRange==0.00)
					   			{
				   				dateRangeAmount=String.valueOf(0.00)+" (C)";
					   			}
				   			
			   			else
				   			{
				   				dateRangeAmount=String.valueOf(amountDateRange-amounToPaidDateRange)+" (A)";
				   			}
				   		    dailyCollection=amountDateRange+dailyCollection;
				   		    dailyCall=dailyCall+amounToPaidDateRange;
				   		    
				   		     // // System.out.println("aaaaaaaaaaa"+amounToPaidDateRange);
					   		LoanInfo cate = new LoanInfo();
					   		
					   		
					   		cate.setLoanNo(list.get(i));
					   		cate.setDueAmount(dueAmount);
					   		cate.setStatus(status);
					   		cate.setDateRangeAmount(dateRangeAmount);
					   		cate.setApplicantName(applicantName);
					   		cate.setSno(sno++);
					   		cate.setInstallAmount(amounToPaidDateRange);
					   		System.out.println("noob2 : "+amounToPaidDateRange);
					   		cate.setPaidAmount(amountDateRange);
					   		cate.setPenaltyDue(penaltyDue);
					   		cate.setShareHolderNo(shrHolderNo);
					   		cate.setContactNo(ub.getContactNo());
					   		cate.setAddress(ub.getAddress());
					   		cate.setFatherHusbandName(ub.getFather_husband_name());
					   		cate.setAltMobileNo(ub.getAltMobileNo());
					   		
					   		if (emiDueDate != null) 
					   		{
					   		    cate.setStrEmiDueDate(new SimpleDateFormat("dd-MM-yyyy").format(emiDueDate));
					   		}
					   		else 
					   		{
					   		    cate.setStrEmiDueDate("");
					   		}
					   		
					   		if(reportType.equalsIgnoreCase("grouploan"))
							{
					   			cate.setCenterName(obj.centerNameCodeById(obj.centerIdByGroupLoanNo(branchId,list.get(i), conn), conn));
							}
					   		collectionList.add(cate);
						   }    
				}
				
				else
					if(reportType.equals("DD")||reportType.equals("RD"))
					{
						showPenaltyField=false;
					   tbl1=true;
					   collectionList=new ArrayList<>();
						
							list2= obj.listRdFd(endDate,reportType,branchId, dsaCode);
						
						
						int sno=1;
						dailyCollection=0.00;
						for(int j=0;j<list2.size();j++)
							{
								rdFdList=obj.showInstallment(list2.get(j),conn);
								rdAmountPaid=obj.rdAmountPaid(list2.get(j), endDate);
								String shareHolderNum= obj.shareHolderNoByRdFdNo(list2.get(j));
								UserInfo ub=obj.memberBasicDetail(shareHolderNum, conn);
						   		applicantName=ub.getName();
								applicantName=obj.applicantNameByShareHolderNo(shareHolderNum, conn);
								rdAmountDateRange= obj.rdAmountWithInDateRange(list2.get(j),startDate,endDate);
							    rdAmountToPaid=0.00;rdAmountToPaidDateRange=0.00;int count=0;double instlAmount=0.00;
								for(InstallmentInfo obj1:rdFdList)
									{
									   String eDate=sdfForDb.format(endDate);
										String sDate=sdfForDb.format(startDate);
										String insDate=sdfForDb.format(obj1.getInstlmntDate());
										
									   if(obj1.getInstlmntDate().before(endDate) || insDate.equals(eDate))
										  {
										      rdDate=obj1.getIns_date();
										      rdAmountToPaid=obj1.getInstallment_amount()+rdAmountToPaid;
										  }
									
									   if((obj1.getInstlmntDate().after(startDate) || insDate.equals(sDate)) && (obj1.getInstlmntDate().before(endDate)  || insDate.equals(eDate)))
						   			{
						   			   rdAmountToPaidDateRange=rdAmountToPaidDateRange+obj1.getInstallment_amount();
						   			}
									   
									   if(count==0  && insDate.equals(sDate))
						   			{ 
						   				           
						   				instlAmount=obj1.getInstallment_amount();
						   				// // System.out.println(instlAmount);
						   				count=count+1;
						   				
						   			}
										 
									}
								if(rdAmountToPaid>rdAmountPaid)
						   		{
						   			status="Due";
						   			dueAmount=rdAmountToPaid-rdAmountPaid;
						   		}
				   		   else
			   				if(rdAmountPaid==0.00)
				   				{
				   					status="No Amount Paid";
				   					dueAmount=0.00;
				   				}
				   		   else
				   			  
				   			if(rdAmountToPaid-rdAmountPaid==0.00)
					   			{
					   				status="Clear";
					   				dueAmount=0.00;
					   			}
				   			else
					   			{
					   				status="Advance";
					   				dueAmount=rdAmountPaid-rdAmountToPaid;
					   			}
								
								if(rdAmountToPaidDateRange>rdAmountDateRange)
					   		{
					   			dateRangeAmount=String.valueOf(rdAmountToPaidDateRange-rdAmountDateRange)+" (D)";
					   		}
					   		else
				   				if(rdAmountDateRange==0.00)
				   				{
				   					dateRangeAmount=String.valueOf(0.00)+" (NILL)";
				   				}
					   		else
					   			if(rdAmountToPaidDateRange-rdAmountDateRange==0.00)
						   			{
					   				  dateRangeAmount=String.valueOf(0.00)+" (C)";
						   			}
				   			
				   			else
					   			{
					   				dateRangeAmount=String.valueOf(rdAmountDateRange-rdAmountToPaidDateRange)+" (A)";
					   			}	
				   		        
								dailyCollection=rdAmountDateRange+dailyCollection;
								// // System.out.println("@@@"+rdAmountDateRange);
								dailyCall=dailyCall+rdAmountToPaidDateRange;
								LoanInfo cate = new LoanInfo();
					   		cate.setLoanNo(list2.get(j));
					   		cate.setDueAmount(dueAmount);
					   		cate.setApplicantName(applicantName);
					   		cate.setDateRangeAmount(dateRangeAmount);
					   		cate.setStatus(status);
					   		// cate.setPenaltyDue(rdAmountDateRange);
					   		cate.setPaidAmount(rdAmountDateRange);
					   		cate.setInstallAmount(rdAmountToPaidDateRange);
					   		cate.setSno(sno++);
					   		cate.setShareHolderNo(shareHolderNum);
					   		cate.setContactNo(ub.getContactNo());
					   		cate.setAddress(ub.getAddress());
					   		cate.setFatherHusbandName(ub.getFather_husband_name());
					   		collectionList.add(cate);
								 
							}
				}
					else
					{
						FacesContext fc=FacesContext.getCurrentInstance();
						fc.addMessage(null, new FacesMessage("Select Scheme Type"));
					}
				}
			try 
			{   
				conn.close();
			}
			catch (Exception e)
			{
				// TODO: handle exception
			}
		    
			ArrayList<LoanInfo> tempList=new ArrayList<>();
			int count=1;
			for(int i=0;i<collectionList.size();i++)
			{
				if(collectionList.get(i).getInstallAmount()>0 || collectionList.get(i).getPaidAmount()>0)
				{
					collectionList.get(i).setSno(count);
					tempList.add(collectionList.get(i));
					count++;
				}
			}
			
			collectionList=new ArrayList<>();
			collectionList=tempList;
			tempList=new ArrayList<>();
		}
		else
		{
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Select Branch"));
		}
		
		System.out.println("test-1"+ collectionList.size());
	}
    
    
	
	public void getTotalEmiAmountTargatedCollectionPending(JSONObject obj) 
	{   		
		Date payDate = new Date();
	    double collectionAmount = 0.0;
	    double totalDue = 0.0;
        
	    try(Connection conn = DataBaseConnection.JAVAConnection()) 
	    {    
	        // ✅ FIX: query sahi likho (no string concat, no missing quote)
	        String query = "SELECT SUM(amount) as total FROM loanpayment WHERE pay_date = ?";

	        try (PreparedStatement ps = conn.prepareStatement(query)) 
	        {

	            ps.setDate(1, new java.sql.Date(payDate.getTime()));

	            ResultSet rs = ps.executeQuery();

	            if (rs.next()) {
	                collectionAmount = rs.getDouble("total");
	            }
	            System.out.println("collection amout is"+collectionAmount);
	        }
//            
//	        // ✅ Yaha collectionList se Due nikaalo
//	        List<LoanInfo>  collectionList = collectionAnalysisBean.getCollectionList();
//	        
//	        if (collectionList != null && !collectionList.isEmpty()) {
//
//	            for (LoanInfo loan : collectionList) {
//
//	                if ("Due".equalsIgnoreCase(loan.getStatus())) {
//	                    totalDue += loan.getDueAmount();
//	                    System.out.println(totalDue);
//	                }
//	            }
//	        
//	        }
//	        

	    } catch (Exception e) {
	        e.printStackTrace();
	    }
         
	    // ✅ Tum kya return karna chahte ho?
	    // Option 1: sirf collection
	    // return collectionAmount;

	    // Option 2: sirf due
	    // return totalDue;
       
	    // ✅ Option 3 (Recommended): dono ka difference (Pending)

	    double targetCollection= getTargetAmount();
	    double pending = targetCollection - collectionAmount;
	    if (pending < 0) 
	    {
	    	pending = 0;
		}
	    
	    obj.put("collected", collectionAmount);
        obj.put("pending", pending);
        obj.put("target", targetCollection);
        obj.put("status", "success");
	    
	    
	}
	
	
	
	
	public String checkLastPaidEmiNoByLoanNo(String loanNo)
	{
	    String lastPaidEmiNo = null;
        
	    try(Connection conn = DataBaseConnection.JAVAConnection())
	    {   
	    	
	        String query = "SELECT * FROM loanpayment WHERE loanNo=? ORDER BY emi_no DESC LIMIT 1";

	        try(PreparedStatement ps = conn.prepareStatement(query))
	        {
	        	
	            ps.setString(1, loanNo);

	            ResultSet rs = ps.executeQuery();
                
	            if(rs.next())
	            {
	                lastPaidEmiNo = rs.getString("emi_no");
	                System.out.println(lastPaidEmiNo);
	            }
	        }
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	    }

	    return lastPaidEmiNo;
	}
	
	public void postProcessXLS(Object document) {

	    Workbook workbook = (Workbook) document;
	    Sheet sheet = workbook.getSheetAt(0);

	    for (Row row : sheet) 
	    {

	        for (org.apache.poi.ss.usermodel.Cell cell : row) 
	        {

	            if (cell != null) {
	                try {

	                    String value = cell.toString().trim();

	                    // check numeric
	                    if (value.matches("-?\\d+(\\.\\d+)?")) {
	                        cell.setCellValue(Double.parseDouble(value));
	                    }

	                } catch (Exception e) {
	                    // ignore non numeric values
	                }
	            }

	        }

	    }
	}
	
	
	public ArrayList<SelectItem> selectProduct(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.setValue(rr.getString("id"));
				ss.setLabel(rr.getString("name") + " " + rr.getString("brand") + " " + rr.getString("model"));
				
				
				list.add(ss);
				
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		return list;
		
	}
	
	public String  getFirmName() 
	{
		String firmName="";
	    String query = "SELECT name FROM firm_detail";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query);
	         ResultSet rs = ps.executeQuery()) {

	        if (rs.next()) 
	        {
	           firmName = rs.getString("name");
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }
		return firmName;
	}

	
	public static String guarantorNameByShareHolderNo(String shno, Connection conn) 
	{
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select firstguarantor from secure_loan where shareholderno='"+shno+"'";
			rr=st.executeQuery(qq);
			if(rr.next())
			{
				return rr.getString("firstguarantor");
			}
		}
		catch (Exception e)
		{
			 e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return "";
	}
	
	public static String highMarkLabelByValues(String columnValue, Connection conn) 
	{
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select column_label from high_mark_table where column_value='"+columnValue+"' ";
			//System.out.println(qq);
			rr=st.executeQuery(qq);
			if(rr.next())
			{
				return rr.getString("column_label");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
			}
		}
		return "";
	}
    
	public static String getReligionNameByReligionCode( String religionCode)
	{
		String religion = null;
	    String sql = "SELECT column_label FROM high_mark_table WHERE column_value = ?";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(sql))
	    {
	        ps.setString(1, religionCode);

	        ResultSet rs = ps.executeQuery();
	        if (rs.next())
	        {
	             religion= rs.getString("column_label");
	        }
	        else
	        {
	        	 religion= "null";
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }
	    return religion;
	}
   
    public ArrayList<SelectItem> highMarkTableListForCustomerStateReligion(String columnType,Connection conn) 
	{
		ArrayList<SelectItem> list=new ArrayList<>();
		Statement st=null;
		ResultSet rr=null;
		
		try
		{
			st=conn.createStatement();
			String qq="select * from high_mark_table where column_type='"+columnType+"' ";
		    rr=st.executeQuery(qq);
		    while(rr.next())
		    {
		    	SelectItem ss=new SelectItem();
		    	
		    	ss.setLabel(rr.getString("column_value")+" - "+rr.getString("column_label"));
		    	ss.setValue(rr.getString("column_label"));
		    	
		    	list.add(ss);
		    }
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return list;
	}
    
    public boolean disAllByCompanyNameUserType(String userType,String cc)
    {
    	
    	System.out.println(userType);
    	System.out.println(cc);
    	boolean disAll;
    	
    	if ((cc == null || "SUDERSHANA FINANCE".equalsIgnoreCase(cc)) 
    	        && !userType.equalsIgnoreCase("masterAdmin")) 
    	{
    	    disAll = true;
    	} 
    	else 
    	{
    	    disAll = false;
    	}
        
    	System.out.println("disall valuse is"+disAll);
		return disAll;	
		
    }
   
    
    public static String getDesignationByUname(String uname) 
    {
    	System.out.println("uname is:"+uname);
        String designationId = null;

        try (Connection conn = DataBaseConnection.JAVAConnection()) 
        {
            String query = "SELECT designation FROM employee WHERE uname=?";
            try (PreparedStatement ps = conn.prepareStatement(query)) 
            {
                ps.setString(1, uname);

                ResultSet rs = ps.executeQuery();
                if (rs.next()) 
                {
                    designationId = rs.getString("designation");
                    
                    System.out.println("desg is:"+designationId);
                }
            }
        }
        catch (Exception e) 
        {
            e.printStackTrace();
        }
        
        String designationName = getDesignationNameByDesignationId(designationId);

        return designationName;
    }
    
    public List<String> generateCenterFullCode(HttpSession ss, Connection conn, String centerCode) 
    {
    	List<String> data = new ArrayList<>();
        System.out.println("Method hit");
        System.out.println("Center ID param: " + centerCode);
        
        String branchId = String.valueOf(ss.getAttribute("branchId"));
        String branchCode = null;
        String centerId = null;
        String finalCode = null;
        
        try
        {
            String query1 = "SELECT branch_code FROM firm_detail WHERE id = ?";
            try (PreparedStatement ps1 = conn.prepareStatement(query1)) {
                ps1.setString(1, branchId);
                ResultSet rs1 = ps1.executeQuery();
                if (rs1.next()) 
                { 
                    branchCode = rs1.getString("branch_code");
                    
                    System.out.println("Branch Code: " + branchCode);
                }
                data.add(branchCode);
            }
            

            String query2 = "SELECT centerCode FROM center WHERE id = ?";
            try (PreparedStatement ps2 = conn.prepareStatement(query2)) {
                ps2.setString(1, centerCode);
                ResultSet rs2 = ps2.executeQuery();
                if (rs2.next()) {
                    centerId = rs2.getString("centerCode");
                    System.out.println("Center Code: " + centerId);
                }
            }
            
            if (branchCode == null || centerId == null)
            {
                System.out.println("❌ Missing branchCode or centerId");
                return null;
            }

            String formattedCenter = String.format("%05d", Integer.parseInt(centerId));
            finalCode = branchCode + formattedCenter;
            System.out.println("final code with branch is :"+finalCode);
            
            data.add(finalCode);
        } 
        catch (Exception e)
        {
            e.printStackTrace();
        }

        return data;
    }

    
    public  String getCenterIdByCenterCode(String centerCode, Connection conn) {
        String getCenterId = null;
        System.out.println("enter in methods is:");
        try {
            String query = "SELECT centerCode FROM center WHERE id = ?";
            try (PreparedStatement ps = conn.prepareStatement(query)) {
                
                // If centerCode is integer in DB:
                try {
                    ps.setInt(1, Integer.parseInt(centerCode));
                } catch (NumberFormatException e)
                {
                    ps.setString(1, centerCode);
                }
                
                try (ResultSet rs = ps.executeQuery())
                {
                    if (rs.next()) 
                    {
                        getCenterId = rs.getString("centerCode");
                    }
                    else 
                    {
                            	  
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return getCenterId;
    }


    // Method 2: Get designation name from hrm_basic table by ID
    public static String getDesignationNameByDesignationId(String designationId) {
        String designationName = null;

        try (Connection conn = DataBaseConnection.JAVAConnection())
        {
            String query = "SELECT name FROM hrm_basic WHERE id=?";
            try (PreparedStatement ps = conn.prepareStatement(query)) {
                ps.setString(1, designationId);

                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    designationName = rs.getString("name");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return designationName;
    }
    
    public static BigDecimal getLoanAmountByLoanNo(String loanNo)
    {

        BigDecimal loanAmount = BigDecimal.ZERO;

        String sql = "SELECT loanAmount FROM grouploan WHERE loanNo = ?";

        try (Connection con = DataBaseConnection.JAVAConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {

            ps.setString(1, loanNo);

            ResultSet rs = ps.executeQuery();

            if (rs.next())
            {
                loanAmount = rs.getBigDecimal("loanAmount");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return loanAmount;
    }
    
    
    
    public List<SelectItem> centerListByBranchId(int branchId)
	{
    	List<SelectItem> list = new ArrayList<>();
    	
		try (Connection conn = DataBaseConnection.JAVAConnection()) 
		{
			String qq = "select id, centerName, centerCode from center ";
			
			if (branchId != -1) 
			{
				qq += "where branch_id = ?";
			}
			
			try (PreparedStatement ps = conn.prepareStatement(qq)) 
			{
				if (branchId != -1) 
				{
					ps.setInt(1, branchId);
				}
				
				ResultSet rs = ps.executeQuery();
				while (rs.next())
				{
					SelectItem si = new SelectItem();
					
					si.setLabel(rs.getString("centerName") + " / " + rs.getString("centerCode"));
					si.setValue(rs.getString("id"));
					
					list.add(si);
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return list;
	}
    
    
    
    public ArrayList<UserInfo> centerWiseCollectionList(int branchId,Date searchDate, Date endDate, int centerId) 
	{
		int count=1;
		String sdt=sdfForDb.format(searchDate);
		String edt=sdfForDb.format(endDate);
		ArrayList<UserInfo> list=new ArrayList<>();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{   
			
			Statement st=conn.createStatement();
			String qq= "select sum(amount) AS loan_amount,"
					 + "grouploan.center AS center_id"
					 + "  from loanpayment "
					 + "inner join grouploan where pay_date>='"+sdt+"' and pay_date<='"+edt+"' and "
					 + " receipt_status='approved' and chequeStatus='cleared' and loanType in ('grouploan','Group Loan') and "
					 + " loanpayment.loanNo=grouploan.sub_loan_no  ";
			
			if(branchId!=-1)
			{
				qq+=" and grouploan.branch_id='"+branchId+"' ";
			}
			if(centerId!=-1)
			{
				qq+=" and grouploan.center='"+centerId+"' ";
			}
//			qq+=" group by grouploan.center, grouploan.sub_loan_no";
			qq += "GROUP BY grouploan.center";
			
			System.out.println(qq);
			ResultSet rr=st.executeQuery(qq);
			
			while(rr.next())
			{
				UserInfo uu=new UserInfo();
				
				uu.setSno(count++);
				String cid = rr.getString("center_id");
			    uu.setCenterId(cid);
				uu.setCenterName(db.centerNameById(cid, conn));				
				uu.setLoanAmount(rr.getDouble("loan_amount"));
//				double amountRd=rdAmountCenterWise(branchId,cid, sdt, edt, conn);
//				uu.setRdAmount(amountRd);
				double amountRd = rdAmountCenterWiseLikeDetailHeader(branchId, cid, sdt, edt, conn);

				uu.setRdAmount(amountRd);
				System.out.println("TESTING FOR TOTAL RD AMOUNT IN LIST"+amountRd);
				
				uu.setTotalAmount(uu.getLoanAmount()+uu.getRdAmount());
				
				list.add(uu);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
    
    
    
    private double rdAmountCenterWise(int branchId, String centerId, String dt, String edt, Connection conn)
    {
    	double amount = 0;

    	PreparedStatement ps = null;
    	ResultSet rr = null;

    	try
    	{
    		String qq = "SELECT IFNULL(SUM(pr.amount),0) AS totalAmount "
    				+ "FROM payment_receipt pr "
    				+ "INNER JOIN customer_info ci ON pr.user_no = ci.user_no "
    				+ "INNER JOIN ( "
    				+ "SELECT DISTINCT teamLeader "
    				+ "FROM grouploan "
    				+ "WHERE center=? "
    				+ "AND status='Approve' "
    				+ "AND paidStatus='unpaid' ";

    		if(branchId != -1)
    		{
    			qq += " AND branch_id=? ";
    		}

    		qq += " ) gl ON gl.teamLeader = ci.shareHolderNo "
    			 + "WHERE ci.scheme_type='RD' "
    			 + "AND pr.receipt_status='approved' "
    			 + "AND pr.chequeStatus='cleared' "
    			 + "AND pr.date>=? "
    			 + "AND pr.date<=? ";

    		if(branchId != -1)
    		{
    			qq += " AND pr.branch_id=? ";
    		}

    		System.out.println(qq);

    		ps = conn.prepareStatement(qq);

    		int i = 1;

    		ps.setString(i++, centerId);

    		if(branchId != -1)
    		{
    			ps.setInt(i++, branchId);
    		}

    		ps.setString(i++, dt);
    		ps.setString(i++, edt);

    		if(branchId != -1)
    		{
    			ps.setInt(i++, branchId);
    		}

    		rr = ps.executeQuery();

    		if(rr.next())
    		{
    			amount = rr.getDouble("totalAmount");
    		}
    	}
    	catch(Exception e)
    	{
    		e.printStackTrace();
    	}
    	finally
    	{
    		try
    		{
    			if(rr != null)
    			{
    				rr.close();
    			}

    			if(ps != null)
    			{
    				ps.close();
    			}
    		}
    		catch(Exception e)
    		{
    			e.printStackTrace();
    		}
    	}

    	return amount;
    }
    public ArrayList<UserInfo> allShareHolders(int branchId,String dsaType, Connection conn, String center)
	{ 
		boolean shareCheck=checkShareAllotmentFromMember();
		String query ="";
		ArrayList<UserInfo> list = new ArrayList<>();
		int i = 1;
		Statement stt=null;
		ResultSet ss =null;
		try
		{
			stt = conn.createStatement();
			if(shareCheck==true && dsaType.equalsIgnoreCase("all"))
			{
				query = "select * from shareholder_info a where noofShare >0 ";
			}
			else if(shareCheck==false && dsaType.equalsIgnoreCase("all"))
			{
				query = "select * from shareholder_info ";
			}
			else if(shareCheck==true && !dsaType.equalsIgnoreCase("all"))
			{
				query = "select * from shareholder_info a where noofShare >0 and dsacode='"+dsaType+"' ";
			}
			else if(shareCheck==false && !dsaType.equalsIgnoreCase("all"))
			{
				query = "select * from shareholder_info where dsacode='"+dsaType+"' ";
			}
			
			if(branchId != -1)
			{
				if(query.contains("where"))
				{
					query=query+" and branch_id='"+branchId+"'";
				}
				else
				{
					query=query+" where branch_id='"+branchId+"'";
				}
			}
			
			if (!center.equalsIgnoreCase("all")) 
			{
				query += "AND center = '"+ center +"' ";
			}
			
			
			query += " order by id desc"; 
			ss= stt.executeQuery(query);
			while (ss.next())
			{
				UserInfo uu = new UserInfo();
				uu.setId(ss.getString("id"));
				uu.setOffice_date(ss.getDate("office_date"));
				uu.setFirst_applicantName(ss.getString("first_applicantName"));
				uu.setContactNo(ss.getString("mobile"));
				uu.setAltMobileNo(ss.getString("alt_mobile_no"));
				
				uu.setShareHolderNo(ss.getString("shareholderno"));
				uu.setFather_husband_name(ss.getString("father_husband_name"));
				uu.setOffice_application_no(ss.getString("office_application_no"));
				uu.setTotalAmount(ss.getDouble("totalAmount"));
				uu.setCrNo(ss.getString("crNo"));
				uu.setAddress(ss.getString("address"));
				uu.setReligion(ss.getString("religion"));
				uu.setNoOfShare(ss.getInt("noofShare"));
				uu.setTransferFrom(ss.getString("transfer_from"));
				uu.setAccount_status(ss.getString("account_status"));
				uu.setSno(i++);
				
				
//				if(ss.getString("transfer_from").equals(""))
//				{
//					uu.setTransferFrom("");
//					uu.setDisable(true);
//				}
//				else
//				{
//					uu.setTransferFrom(ss.getString("transfer_from"));
//					uu.setDisable(false);
//				}
			
				filePathDetail(conn);
				if(onlineFileViewPath==null || onlineFileViewPath.trim().equals(""))
				{
					uu.setApplicant_photo(ss.getString("applicant_photo"));
				} 
				else
				{
					uu.setApplicant_photo(onlineFileViewPath+ss.getString("applicant_photo"));
				}
				if(ss.getString("account_status").equals("inactive"))
				{
					uu.setShowReportType(true);
					uu.setCheckForReturnShare(false);
				}
				else
				{
					uu.setShowReportType(false);
					uu.setCheckForReturnShare(true);
				}
				uu.setDsaCode(ss.getString("dsacode"));
				uu.setCenterId(ss.getString("center"));
				uu.setCenterName(centerNameCodeById(ss.getString("center"), conn));
				uu.setCreditLimit(ss.getDouble("credit_limit"));
				list.add(uu);
			}
		}
		catch (Exception e) {
		           e.printStackTrace();fileCreation(e.getMessage());
		}
		finally
		{
			try {
			if(stt!=null)
			{
			stt.close();
			}
			if(ss!=null)
			{
			ss.close();
			}
			} catch (Exception e2) {
			// TODO: handle exception
			}
		}
	
		return list;
	}
    
    
//    private double rdAmountCenterWiseLikeDetailHeader(
//            int branchId, String centerId, String sdt, String edt, Connection conn)
//    {
//        double amount = 0;
//        
//        StringBuilder sql = new StringBuilder();
//        
//        sql.append("SELECT IFNULL(SUM( ");
//        sql.append("   (SELECT IFNULL(SUM(pr.amount),0) ");
//        sql.append("    FROM payment_receipt pr ");
//        sql.append("    INNER JOIN customer_info ci ON pr.user_no = ci.user_no ");
//        sql.append("    WHERE ci.shareHolderNo = gl.teamLeader ");
//        sql.append("    AND pr.receipt_status='approved' ");
//        sql.append("    AND pr.chequeStatus='cleared' ");
//        sql.append("    AND pr.date >= ? ");
//        sql.append("    AND pr.date <= ? ");
//        
//        
//        
//        if(branchId != -1)
//        {
//            sql.append(" AND pr.branch_id = ? ");
//        }
//           
//        sql.append("   ) ");
//        sql.append("),0) AS totalAmount ");
//        sql.append("FROM loanpayment lp ");
//        sql.append("INNER JOIN grouploan gl ON lp.loanNo = gl.sub_loan_no ");
//        sql.append("WHERE lp.pay_date >= ? ");
//        sql.append("AND lp.pay_date <= ? ");
//        sql.append("AND lp.receipt_status='approved' ");
//        sql.append("AND lp.chequeStatus='cleared' ");
//        sql.append("AND gl.center = ? ");
//
//        if(branchId != -1)
//        {
//            sql.append(" AND gl.branch_id = ? ");
//        }
//    System.out.println(sql);
//        
//        
//        try(PreparedStatement ps = conn.prepareStatement(sql.toString()))
//        {
//            int i = 1;
//
//            ps.setString(i++, sdt);
//            ps.setString(i++, edt);
//
//            if(branchId != -1)
//            {
//                ps.setInt(i++, branchId);
//            }
//
//            ps.setString(i++, sdt);
//            ps.setString(i++, edt);
//            ps.setString(i++, centerId);
//
//            if(branchId != -1)
//            {
//                ps.setInt(i++, branchId);
//            }
//
//            ResultSet rs = ps.executeQuery();
//
//            if(rs.next())
//            {
//                amount = rs.getDouble("totalAmount");
//            }
//
//            rs.close();
//        }
//        catch(Exception e)
//        {
//            e.printStackTrace();
//        }
//
//        return amount;
//    }
    private double rdAmountCenterWiseLikeDetailHeader(
            int branchId, String centerId, String sdt, String edt, Connection conn)
    {
        double amount = 0;

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT IFNULL(SUM(pr.amount), 0) AS totalAmount ");
        sql.append("FROM payment_receipt pr ");
        sql.append("INNER JOIN customer_info ci ON pr.user_no = ci.user_no ");
        sql.append("WHERE ci.shareHolderNo IN ( ");
        sql.append("    SELECT DISTINCT gl.teamLeader ");
        sql.append("    FROM grouploan gl ");
        sql.append("    WHERE gl.center = ? ");

        if(branchId != -1)
        {
            sql.append(" AND gl.branch_id = ? ");
        }
        
        sql.append(") ");
        sql.append("AND pr.receipt_status='approved' ");
        sql.append("AND pr.chequeStatus='cleared' ");
        sql.append("AND pr.date >= ? ");
        sql.append("AND pr.date <= ? ");

        if(branchId != -1)
        {
            sql.append(" AND pr.branch_id = ? ");
        }

        System.out.println("RD QUERY = " + sql);

        try(PreparedStatement ps = conn.prepareStatement(sql.toString()))
        {
            int i = 1;

            // Center
            ps.setString(i++, centerId);

            // Group loan branch
            if(branchId != -1)
            {
                ps.setInt(i++, branchId);
            }

            // RD date
            ps.setString(i++, sdt);
            ps.setString(i++, edt);

            // RD branch
            if(branchId != -1)
            {
                ps.setInt(i++, branchId);
            }

            try(ResultSet rs = ps.executeQuery())
            {
                if(rs.next())
                {
                    amount = rs.getDouble("totalAmount");
                }
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

        return amount;
    }
    
    public String centerNameCodeById(String id, Connection conn) 
	{			
		String name="";
		Statement stt=null;
		ResultSet rr=null;
		try
		{
	         stt=conn.createStatement();
		     String query="select * from center where id='"+id+"' ";	
		     rr=stt.executeQuery(query);
		     if(rr.next())
		     {
		    	 name=rr.getString("centerName")+"/"+rr.getString("centerCode");
		    	 System.out.println(name);
		     }		     
		} 
		 
		
		catch (Exception e) 
		{
			 e.printStackTrace();fileCreation(e.getMessage());
		}
		
		return name;
	}  
    
    
    
    public ArrayList<UserInfo> multipleShareHolders(String[] memberNo, Connection conn, String center)
	{
		String query ="";
		ArrayList<UserInfo> list = new ArrayList<>();
		int i = 1;
		Statement stt=null;
		ResultSet ss =null;
		try
		{
			stt = conn.createStatement();
			for(int k=0;k<memberNo.length;k++)
			{
				query = "select * from shareholder_info  where shareholderno='"+memberNo[k]+"' ";
				if (!center.equalsIgnoreCase("all")) 
				{
					query += "AND center = '"+ center +"' ";
				}
				query += " order by id desc"; 
				System.out.println(query);
				
				ss= stt.executeQuery(query);
				while (ss.next())
				{
					UserInfo uu = new UserInfo();
					//uu = userInfoById(ss);
					uu.setId(ss.getString("id"));
					uu.setFirst_applicantName(ss.getString("first_applicantName"));
					uu.setContactNo(ss.getString("mobile"));
					uu.setShareHolderNo(ss.getString("shareholderno"));
					uu.setFather_husband_name(ss.getString("father_husband_name"));
					uu.setOffice_application_no(ss.getString("office_application_no"));
					//uu.setAccountNo(ss.getString("accountNo"));
					uu.setTotalAmount(ss.getDouble("totalAmount"));
					uu.setCrNo(ss.getString("crNo"));
					uu.setAddress(ss.getString("address"));
					uu.setNoOfShare(ss.getInt("noofShare"));
					uu.setTransferFrom(ss.getString("transfer_from"));
					uu.setAccount_status(ss.getString("account_status"));
					uu.setSno(i++);
					if ("".equals(ss.getString("transfer_from"))) {
					    // Safe even if transfer_from is null
						uu.setTransferFrom("");
						uu.setDisable(true);
					}
										
					else
					{
						uu.setTransferFrom(ss.getString("transfer_from"));
						uu.setDisable(false);
					}
				
					filePathDetail(conn);
					if(onlineFileViewPath==null || onlineFileViewPath.trim().equals(""))
					{
						uu.setApplicant_photo(ss.getString("applicant_photo"));
					}
					else
					{
						uu.setApplicant_photo(onlineFileViewPath+ss.getString("applicant_photo"));
					}
					if(ss.getString("account_status").equals("inactive"))
					{
						uu.setShowReportType(true);
						uu.setCheckForReturnShare(false);
					}
					else
					{
						uu.setShowReportType(false);
						uu.setCheckForReturnShare(true);
					}
					uu.setDsaCode(ss.getString("dsacode"));
					list.add(uu);
				}
			}
		}
		catch (Exception e) {
		           e.printStackTrace();fileCreation(e.getMessage());
		}
		finally
		{
			try {
			if(stt!=null)
			{
			stt.close();
			}
			if(ss!=null)
			{
			ss.close();
			}
			} catch (Exception e2) {
			// TODO: handle exception
			}
		}
	
		return list;
	}
    
    
    
    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 boolean checkShareAllotmentFromMember() 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select share_check from company_shares";
			ResultSet rr=st.executeQuery(qq);
			if(rr.next())
			{
				if(rr.getString("share_check").equalsIgnoreCase("yes"))
				{
					return true;
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return false;
	}
    
    
    
    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;        
	    BufferedWriter writer = null;
			 //online
			
        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 CollectionAnalysisBean getCollectionAnalysisBean() {
		return collectionAnalysisBean;
	}

	public void setCollectionAnalysisBean(CollectionAnalysisBean collectionAnalysisBean) {
		this.collectionAnalysisBean = collectionAnalysisBean;
	}

}