package main_package;

import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Map;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.faces.model.SelectItem;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.primefaces.context.RequestContext;

import group_head_package.DataBaseIncomeExpense;

@ManagedBean(name = "pendingLoanDisburseList")
@ViewScoped

public class PendingLoanDisburseListBean implements Serializable
{
	private static final long serialVersionUID = 1L;
	ArrayList<LoanInfo> personalLoanList,SelectedpersonalLoanList;
	LoanInfo selectedLoan;
	Date releaseDate=new Date();
	String loanType,selType,paymentMode,bank,loanNo, center;
	boolean show,checkdisablityForIndividual,checkDisablityForMultiple, showCenter,mobDealer;
	DataBaseMethods obj=new DataBaseMethods();
	DbYashwant dby =new DbYashwant();
	double amount,loanAmount,paidAmount,totalAmount;
	ArrayList<DebitInfo> transactionHistory;
	ArrayList<SelectItem> centerList,bankList;
	String companyType;
	DataBaseMethodsNaman dbn= new DataBaseMethodsNaman();
	 
	int totalEntry;
	SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
	String companyName;

	// for e nach status
	String enachStatus, enachMessage;
	boolean showEnachOptions,enachDialogVisible;
	boolean cashfreeActive;
	
	DataBaseMethodsHemant dbmh = new DataBaseMethodsHemant();
	DataBaseMethodRaviJ dbrj = new DataBaseMethodRaviJ();
	DataBaseMethodsRaviK dbrk = new DataBaseMethodsRaviK();
	
	
	public PendingLoanDisburseListBean() 
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		int branchId =(int) ss.getAttribute("branchId");
		Connection conn=DataBaseConnection.JAVAConnection();
		centerList=obj.allcenter(branchId,"all",conn);
		companyType =  (String) ss.getAttribute("companyType");
		bankList=new DataBaseIncomeExpense().allBankList(conn);
		
		companyType =  (String) ss.getAttribute("companyType");
		companyName = (String) ss.getAttribute("companyName");
		cashfreeActive = checkCashfreeActive();
		
		try 
		{
			conn.close();
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
	}
	
	
	
	public void checkLoanType()
	{
		if(loanType.equalsIgnoreCase("grouploan"))
		{
			showCenter = true;
		}
		else
		{
			showCenter = false;
		}
	}
	
	
	
	public void checkList()
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		int branchId =(int) ss.getAttribute("branchId");
		personalLoanList=obj.pendingAllLoanListForLoanDisburse(branchId,loanType, center);
		
		
		if(personalLoanList.size()>0)
		{
			totalEntry=personalLoanList.size();
			show=true;
		}
		else
		{
			show=false;
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("No Record Found"));
		}
		
		for(LoanInfo ss2 :personalLoanList)
		{
			if("14".equalsIgnoreCase(ss2.getSecurityype())||"15".equalsIgnoreCase(ss2.getSecurityype()))
			{
				mobDealer=true;
			}
		}
	}
	
	
	public void disburse12()
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		int branchId =(int) ss.getAttribute("branchId");
		if(SelectedpersonalLoanList.size() > 0)
		{
			totalAmount=0;
			paymentMode="cash";
			Connection conn=DataBaseConnection.JAVAConnection();
			int i=0;
			for(LoanInfo tt :SelectedpersonalLoanList)
			{
			paidAmount=0;
		
			transactionHistory=obj.viewDebitLoanHistory(tt.getLoanType(),tt.getLoanNo(),tt.getShareHolderNo(),conn);
			if(tt.getLoanType().equalsIgnoreCase("grouploan"))
			{
				loanNo=tt.getSubLoanNo();
			}
			else
			{
				loanNo=tt.getLoanNo();
			}
			
			loanAmount=obj.findLoanAmountByLoanNoLoanType(tt.getLoanType(),loanNo,tt.getShareHolderNo(),conn);
			
			for(DebitInfo dd: transactionHistory)
			{
				paidAmount=paidAmount+Double.valueOf(dd.getAmount());
			}
			// System.out.println("sddsdsdsdsdsdaaaaaaaaaa" +loanAmount);
			amount=loanAmount-paidAmount;
			if(!tt.getColorStatus().equalsIgnoreCase("done"))
			{
			if(amount>0)
			{
				// System.out.println("sddsdsdsdsdsdaaaaaaaaaa");
				double paybleAmount=loanAmount-paidAmount;
				double remainingCompanyCapitalAmount=obj.remainingCompanyCapitalAmount(branchId,conn);
				if(paymentMode.equalsIgnoreCase("Cash"))
				{
					// System.out.println("sddsdsdsdsdassadasdasdasdadadddadadadasdasdasdadadadad");
					if(amount<=remainingCompanyCapitalAmount)
				    {
						if(amount<=paybleAmount)
						{
							// System.out.println("sqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq");
							
							 i=obj.addDebitLoan(branchId,tt.getShareHolderNo(),tt.getLoanType(),loanNo,amount,paymentMode,"","","",null,releaseDate,"");
						
							 if(i>=1)
					        {
								totalAmount +=amount;
								sendMessageForMultiple(tt.getApplicantName(), tt.getLoanNo(), tt.getLoanType(), tt.getLoanAmount(), tt.getContactNo(), conn);
								obj.increaseSharedCompanyAmount(branchId,amount,conn);
								transactionHistory=obj.viewDebitLoanHistory(tt.getLoanType(), tt.getLoanNo(), tt.getShareHolderNo(),conn);
								
								FacesContext fc=FacesContext.getCurrentInstance();
								// File Charge Amount Add on Loan Approval
								conn=DataBaseConnection.JAVAConnection();
								String fcStatus=obj.checkSettingFromMasterSettingNewTable("File Charge", conn);
								if(fcStatus.equalsIgnoreCase("loan_disburse"))
								{
									int z=obj.updateIncomeTableSelectedPendingEntryStatus("File Charge","active" , loanNo,conn, releaseDate);
									if(z>0)
									{
										if(Double.valueOf( tt.getFileCharge())>0)
										{
											obj.increaseCompanyCapitalAmount(branchId,Double.valueOf( tt.getFileCharge()), conn);
											fc.addMessage(null, new FacesMessage("File Charge "+ tt.getFileCharge() +" Rs. Add Sucessfully."));
										}
									}
								}	
								
								// Insurance Fee Amount Add on Loan Approval
								String insFeeStatus=obj.checkSettingFromMasterSettingNewTable("Insurance Fee", conn);
								if(insFeeStatus.equalsIgnoreCase("loan_disburse"))
								{
									int z=obj.updateIncomeTableSelectedPendingEntryStatus("Insurance Fee","active" , loanNo,conn,releaseDate);
									if(z>0)
									{
										if(Double.valueOf( tt.getInsuranceFee())>0)
										{
											obj.increaseCompanyCapitalAmount(branchId,Double.valueOf( tt.getInsuranceFee()), conn);
											fc.addMessage(null, new FacesMessage("Insurance Fee "+ tt.getInsuranceFee() +" Rs. Add Sucessfully."));
										}
									}
								}	
								// Insurance Fee Amount Add on Loan Approval
								String pcStatus=obj.checkSettingFromMasterSettingNewTable("Processing Charge", conn);
								if(pcStatus.equalsIgnoreCase("loan_disburse"))
								{
									int z=obj.updateIncomeTableSelectedPendingEntryStatus("Processing Charge","active" , loanNo,conn,releaseDate);
									if(z>0)
									{
										if(Double.valueOf( tt.getProcessingCharge())>0)
										{
											obj.increaseCompanyCapitalAmount(branchId,Double.valueOf( tt.getProcessingCharge()), conn);
											fc.addMessage(null, new FacesMessage("Processing Charge "+ tt.getProcessingCharge() +" Rs. Add Sucessfully."));
										}
									}
								}	
								
							//	return "pendingLoanDisburseList.xhtml";
						    }
					        else
					        {
					        	FacesContext fc=FacesContext.getCurrentInstance();
					        	fc.addMessage(null, new FacesMessage("An Error Occured... Please Try Again"));
					        }
						}
						}
						else
						{
							FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Only Rs."+paybleAmount+" is to be paid. Please Check!"));
						}
				    }
				    else
					{
					   FacesContext fc=FacesContext.getCurrentInstance();
					   fc.addMessage(null, new FacesMessage("Sorry! Loan Amount Can't Be released. Company's Remaining Capital Amount is '"+remainingCompanyCapitalAmount+"'"));
					}
				}
				
				
			}
			
		}
			if(i > 0)
			{
				FacesContext fc=FacesContext.getCurrentInstance();
				fc.addMessage(null, new FacesMessage("Amount "+totalAmount+" /- Released Succesfully in Cash"));
				personalLoanList=obj.pendingAllLoanListForLoanDisburse(branchId,loanType, center);

			}
			try {
				conn.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
		else
		{
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Please Select Atleast One Row"));
		}
		
		
	}
	
	
	public void reversePending() throws IOException
	{
		Connection conn=DataBaseConnection.JAVAConnection();
		int i=0;
		Date emiDueDate=null;
		System.out.println("calll"+selectedLoan.getLoanType());
		if(selectedLoan.getLoanType().equals("Personal Loan") || selectedLoan.getLoanType().equals("personalloan"))
		{
			i=obj.updateStatusForPersonalLoan(selectedLoan.getId(),"pending",selectedLoan.getApprovalDate(),selectedLoan.getLoanAmount(), emiDueDate, conn);
		}
		/*else if(selectedLoan.getLoanType().equals("Vehicle Loan") || selectedLoan.getLoanType().equals("vehicleloan"))
		{
			i=obj.updateStatusForVechileLoan(selectedLoan.getId(),"pending",selectedLoan.getApprovalDate(),selectedLoan.getLoanAmount(), emiDueDate, conn);
		}
		else if(selectedLoan.getLoanType().equals("Property Loan") || selectedLoan.getLoanType().equals("propertyloan"))
		{
			i=obj.updateStatusForPropertyLoan(selectedLoan.getId(),"pending",selectedLoan.getApprovalDate(),selectedLoan.getLoanAmount(), emiDueDate, conn);
		}*/
		else if(selectedLoan.getLoanType().equals("Group Loan") || selectedLoan.getLoanType().equals("grouploan"))
		{
			i=obj.updateStatusForGroupLoan(selectedLoan.getId(),"pending",selectedLoan.getApprovalDate(),selectedLoan.getLoanAmount(), emiDueDate, selectedLoan.getLoanNo(), selectedLoan.getSubLoanNo(), conn);
		}
		else if(selectedLoan.getLoanType().equals("Secure Loan") || selectedLoan.getLoanType().equals("secure_loan"))
		{
			i=obj.updateStatusForSecureLoan(selectedLoan.getId(),"pending",selectedLoan.getApprovalDate(),selectedLoan.getLoanAmount(), emiDueDate, conn);
		}
		
		if(i>=1)
		{
			FacesContext fc=FacesContext.getCurrentInstance();
			fc.addMessage(null, new FacesMessage("Loan No. "+selectedLoan.getLoanNo() +" Reverse Successfully. Please Visit Loan Approval For Again Approve  ofThis Loan"));
			fileChargeAndInsuranceFee(conn);
			try {
				conn.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
			
			FacesContext.getCurrentInstance().getExternalContext().redirect("checkLoanRequest.xhtml");
		}
	}
	
	
	// This Method only for Reverse of Loan Approval.  
	private void fileChargeAndInsuranceFee(Connection conn) 
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		int branchId =(int) ss.getAttribute("branchId");
		String selLoanNo="";
		if(selectedLoan.getLoanType().equalsIgnoreCase("grouploan"))
		{
			selLoanNo=selectedLoan.getSubLoanNo();
		}
		else
		{
			selLoanNo=selectedLoan.getLoanNo();
		}
		LoanInfo loan_info=obj.loanInfoByLoanNoLoanType(selLoanNo,selectedLoan.getLoanType(), conn);
		FacesContext fc=FacesContext.getCurrentInstance();
		String fcStatus=obj.checkSettingFromMasterSettingNewTable("File Charge", conn);
		if(fcStatus.equalsIgnoreCase("loan_approve"))
		{
			int z=obj.updateIncomeTableSelectedActiveEntryStatus("File Charge","pending" , selLoanNo,conn, releaseDate);
			if(z>0)
			{
				if(Double.valueOf(loan_info.getFileCharge())>0)
				{
					obj.decreaseCapitalCompanyAmount(branchId,Double.valueOf(loan_info.getFileCharge()));
					fc.addMessage(null, new FacesMessage("File Charge "+loan_info.getFileCharge() +" Rs. Revert Sucessfully."));
				}
			}
		}	
		
		// Insurance Fee Amount  Add on Loan Approval
		String insFeeStatus=obj.checkSettingFromMasterSettingNewTable("Insurance Fee", conn);
		if(insFeeStatus.equalsIgnoreCase("loan_approve"))
		{
			int z=obj.updateIncomeTableSelectedActiveEntryStatus("Insurance Fee","pending" ,selLoanNo,conn,releaseDate);
			if(z>0)
			{
				if(Double.valueOf(loan_info.getInsuranceFee())>0)
				{
					obj.decreaseCapitalCompanyAmount(branchId,Double.valueOf(loan_info.getInsuranceFee()));
					fc.addMessage(null, new FacesMessage("Insurance Fee "+loan_info.getInsuranceFee() +" Rs. Revert Sucessfully."));
				}
			}
		}	
	}
	
	
	public boolean isShowDownloadButton() {
	    if (!"DELIGHTS MICRO CREDIT INDIA FOUNDATION".equalsIgnoreCase(companyName)) {
	        return false;
	    }

	    return "personalLoan".equalsIgnoreCase(loanType)
	        || "secure_Loan".equalsIgnoreCase(loanType);
	}
	
	public void downloadCustomExcel() throws IOException {

	    Connection conn = null;
	    Workbook workbook = null;

	    try {
	        conn = DataBaseConnection.JAVAConnection();
	        workbook = new XSSFWorkbook();
	        Sheet sheet = workbook.createSheet("Pending Loan Report");

	        int rowIndex = 0;

	        // ================= HEADER =================
	        Row header = sheet.createRow(rowIndex++);
	        header.createCell(0).setCellValue("Transaction Type");
	        header.createCell(1).setCellValue("Beneficiary Code");
	        header.createCell(2).setCellValue("Beneficiary Account Number");
	        header.createCell(3).setCellValue("Instrument Amount");
	        header.createCell(4).setCellValue("Beneficiary Name");
	        header.createCell(5).setCellValue("Instruction Reference Number");
	        header.createCell(6).setCellValue("Customer Reference Number");
	        header.createCell(7).setCellValue("Cheque Date");
	        header.createCell(8).setCellValue("IFSC CODE");
	        header.createCell(9).setCellValue("BENE BANK");
	        header.createCell(10).setCellValue("Bene Email Id");

	        // ================= DATA =================
	        if (personalLoanList != null) {
	        	CreationHelper creationHelper = workbook.getCreationHelper();
	        	CellStyle dateStyle = workbook.createCellStyle();
	        	dateStyle.setDataFormat(
	        	    creationHelper.createDataFormat().getFormat("dd/MM/yyyy")
	        	);

	            for (LoanInfo loan : personalLoanList) {

	                String loanNo = loan.getLoanNo();

	                // ---------- DEFAULT VALUES ----------
	                String accountNumber = "";
	                String ifsc = "";
	                String bankCode = "";
	                String email = "";
	                String beneficiaryName = "";
	                String beneficiaryCode = "";
	                String customerRefNo = "";
	                String transactionType = "N"; // default NEFT

	                Map<String, String> bankDetails = null;

	                // ---------- LOAN TYPE LOGIC ----------
	                if ("secure_loan".equalsIgnoreCase(loanType)) {

	                    // loanNo -> dealerId
	                    String dealerId =
	                            dbn.findDealerIdBySlLoanNo(loanNo, conn);

	                    // dealerId -> bank details
	                    bankDetails =
	                            dbn.findDealerBankDetailsByDealerId(dealerId, conn);

	                    beneficiaryCode = generateBeneficiaryCode(dealerId);
	                    customerRefNo =
	                            dbn.findLoanSecurityTypeByLoanNumber(loanNo, conn);

	                    if (bankDetails != null) {
	                        beneficiaryName =
	                                bankDetails.getOrDefault("name", "");
	                    }

	                } else if ("personalloan".equalsIgnoreCase(loanType)) {

	                    // loanNo -> customer bank details
	                    bankDetails =
	                            dbn.findCustomerBankDetailsByLoanNo(loanNo, conn);

	                    beneficiaryCode = generateBeneficiaryCode(loanNo);
	                    customerRefNo = "Personal loan";
	                    beneficiaryName = loan.getApplicantName();
	                }

	                // ---------- BANK DETAILS ----------
	                if (bankDetails != null && !bankDetails.isEmpty()) {
	                    accountNumber =
	                            bankDetails.getOrDefault("account_number", "");
	                    ifsc =
	                            bankDetails.getOrDefault("ifsc_code", "");
	                    bankCode =
	                            bankDetails.getOrDefault("bank_code", "");
	                    email =
	                            bankDetails.getOrDefault("email", "");
	                }

	                if (bankDetails != null) {
	                    System.out.println(
	                        "LoanNo=" + loanNo +
	                        ", Acc=" + bankDetails.get("account_number") +
	                        ", IFSC=" + bankDetails.get("ifsc_code")
	                    );
	                } else {
	                    System.out.println(
	                        "LoanNo=" + loanNo + " bankDetails is NULL"
	                    );
	                }
	                System.out.println(loanType);
	                // ---------- TRANSACTION TYPE ----------
	                if ("HDFC".equalsIgnoreCase(bankCode)) {
	                    transactionType = "I"; // Internal
	                }

	                // ---------- EXCEL ROW ----------
	                Row row = sheet.createRow(rowIndex++);
	                row.createCell(0).setCellValue(transactionType);
	                row.createCell(1).setCellValue(beneficiaryCode);
	                row.createCell(2).setCellValue(accountNumber);
	                row.createCell(3).setCellValue(loan.getLoanAmount());
	                row.createCell(4).setCellValue(beneficiaryName);
	                row.createCell(5).setCellValue("CHEQUE");
	                row.createCell(6).setCellValue(customerRefNo);
//	                Cell dateCell = row.createCell(7);
//	                dateCell.setCellValue(new Date());
//	                dateCell.setCellStyle(dateStyle);
	                row.createCell(7).setCellValue(sdf.format(new Date()));
	                row.createCell(8).setCellValue(ifsc);
	                row.createCell(9).setCellValue(bankCode);
	                row.createCell(10).setCellValue(email);
	            }
	        }

	        // ================= AUTO SIZE =================
	        for (int i = 0; i <= 10; i++) {
	            sheet.autoSizeColumn(i);
	        }

	        // ================= DOWNLOAD =================
	        FacesContext context = FacesContext.getCurrentInstance();
	        HttpServletResponse response =
	                (HttpServletResponse) context.getExternalContext().getResponse();

	        response.reset();
	        response.setContentType(
	                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
	        response.setHeader(
	                "Content-Disposition",
	                "attachment; filename=PendingLoanCustomReport.xlsx");

	        workbook.write(response.getOutputStream());
	        context.responseComplete();

	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {

	        if (workbook != null) {
	            workbook.close();
	        }

	        if (conn != null) {
	            try { conn.close(); } catch (Exception ignored) {}
	        }
	    }
	}
	
	public String generateBeneficiaryCode(String dealerId) {

	    int hash = Math.abs(dealerId.hashCode());

	    // limit to 3–5 digits if bank requires
	    int benNumber = hash % 1000; // 000–999

	    return String.format("BEN%03d", benNumber);
	}
	
	
	public void disburseListForSingle() throws IOException
	{   
		System.out.println("securityyy :"+selectedLoan.getSecurityype());
		System.out.println("LAMOUNT :"+selectedLoan.getLoanAmount());

		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		ss.setAttribute("selected", selectedLoan);
		ss.setAttribute("loanType", loanType);
		//FacesContext.getCurrentInstance().getExternalContext().redirect(".xhtml");
	
		//RequestContext.getCurrentInstance().execute("window.open('debitLoan.xhtml')");
		//return "debitLoan.xhtml";
		
	}
	
	
		public boolean checkCashfreeActive() {
	
		    boolean active = false;
	
		    String query = "SELECT is_active FROM cashfree_setting LIMIT 1";
	
		    try (Connection conn = DataBaseConnection.JAVAConnection  ();
		         PreparedStatement ps = conn.prepareStatement(query);
		         ResultSet rs = ps.executeQuery()) {
	
		        if (rs.next()) {
		            active = rs.getInt("is_active") == 1;
		        }
	
		        System.out.println("Cashfree Active Status = " + active);
	
		    } catch (Exception e) {
		        e.printStackTrace();
		    }
	
		    return active;
		}
		
		private void handleInactiveCashfree() {
		
		    	 try { HttpSession session = (HttpSession) FacesContext.getCurrentInstance().getExternalContext()
							.getSession(false);
	
		            if (session == null) {
						FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR,
								"Session expired. Please login again.", null));
		                return;
		            }
	
		            session.setAttribute("selected", selectedLoan);
		            session.setAttribute("loanType", loanType);
					RequestContext.getCurrentInstance().execute("window.open('debitLoan.xhtml')");
	
		        } catch (Exception e) {
		            e.printStackTrace();
	
					FacesContext.getCurrentInstance().addMessage(null,
							new FacesMessage(FacesMessage.SEVERITY_ERROR, "Unable to open loan disbursement page.", null));
		        }
	
		        return;
		    }
		    
		public void checkEnachStatus(LoanInfo loan) {
	
		    if (loan == null || loan.getLoanNo() == null
		            || loan.getLoanNo().trim().isEmpty()) {
	
				FacesContext.getCurrentInstance().addMessage(null,
						new FacesMessage(FacesMessage.SEVERITY_ERROR, "Please select a valid loan.", null));
		        return;
		    }
	
		    selectedLoan = loan;
	
		    if (!cashfreeActive) {
		    	FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Cashfree is Inactive", null));
	
		    	handleInactiveCashfree();  
		    	return;
		    	}
	
		    enachStatus = null;
		    enachMessage = "";
		    showEnachOptions = false;
		    enachDialogVisible = true;
	
		    Connection conn = null;
	
		    try {
		        String loanNo = selectedLoan.getLoanNo().trim();
	
		        System.out.println("========== ENACH STATUS CHECK ==========");
		        System.out.println("Loan No = " + loanNo);
		        System.out.println("Cashfree Active = " + cashfreeActive);
	
		        conn = DataBaseConnection.JAVAConnection();
	
		        if (conn == null || conn.isClosed()) {
		            throw new SQLException("Database connection is unavailable.");
		        }
	
		        enachStatus = dbrk.subscriptionStatusCheckByLoanno(loanNo, conn);
	
		        System.out.println("eNACH Status = " + enachStatus);
	
		        if (enachStatus == null || enachStatus.trim().isEmpty()) {
		            enachMessage = "No eNACH subscription record found for this loan. "
		                         + "Do you want to continue without e-Nach?";
		            
		        } else if ("active".equalsIgnoreCase(enachStatus.trim())
		                  || "pending from bank side".equalsIgnoreCase(enachStatus.trim())) {
		            enachMessage = "eNACH is " + enachStatus
		                         + " for this loan. Do you want to continue?";
		            
		        } else {
		            enachMessage = "Current eNACH status is: " + enachStatus
		                         + ". Do you want to continue without e-Nach?";
		        }
		   
		        showEnachOptions = true;
	
		    } catch (Exception e) {
	
		        e.printStackTrace();
	
		        enachMessage = "Unable to verify eNACH status. Please try again.";
		        showEnachOptions = false;
	
		    } finally {
	
		        if (conn != null) {
		            try {
		                conn.close();
		            } catch (SQLException e) {
		                e.printStackTrace();
		            }
		        }
		    }
	
		    RequestContext.getCurrentInstance().execute("PF('loanEnachDialog').show();");
		}
	
	
		
		public void continueWithoutEnach() throws IOException {
			if (selectedLoan == null || selectedLoan.getLoanNo() == null || selectedLoan.getLoanNo().trim().isEmpty()) {
				FacesContext.getCurrentInstance().addMessage(null,
						new FacesMessage(FacesMessage.SEVERITY_ERROR, "Selected loan not found.", null));
				return;
			}
	
			HttpSession session = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
			if (session == null) {
				FacesContext.getCurrentInstance().addMessage(null,
						new FacesMessage(FacesMessage.SEVERITY_ERROR, "Session expired. Please login again.", null));
				return;
			}
	
			session.setAttribute("selected", selectedLoan);
			session.setAttribute("loanType", loanType);
			RequestContext.getCurrentInstance().execute("window.open('debitLoan.xhtml')");
		
		}
	
	
	public void viewLoanDisburse()
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		ss.setAttribute("selected", selectedLoan);
		ss.setAttribute("loanType", loanType);
		//FacesContext.getCurrentInstance().getExternalContext().redirect(".xhtml");
	
		RequestContext.getCurrentInstance().execute("window.open('viewLoanDisburse.xhtml')");
	}

	private void sendMessageForMultiple(String applicantName, String loanNo, String loanType, String loanAmount, String contactNo, Connection conn) 
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		int branchId =(int) ss.getAttribute("branchId");
		String userId=obj.userId();
		String check=obj.checkStatusInMessageSettingTable("Loan Disburse");
		if(check.equals("active"))
		{
			String typeMessage="Dear "+applicantName+", Your "+loanType+" No. "+loanNo +", Loan Amount "+loanAmount+",  is Approved Sucessfully.";
			obj.runMessageUrl(branchId,contactNo, typeMessage,  "System","1207166054349037096", conn);
			typeMessage="";
		}
		else
		{
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Message For This Module is not Active. Please Go to In User Block and Active Message For This Module."));
		}
	}
	
	
	public void releasePayment()
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		int branchId = (int) ss.getAttribute("branchId");
		Connection conn=DataBaseConnection.JAVAConnection();
		if(SelectedpersonalLoanList.size() > 0)
		{
			for(LoanInfo tt :SelectedpersonalLoanList) {
				if(!tt.getLoanNo().equals("")&& !tt.getTransactionNo().equals("")) {
					int i=0;
					if(tt.getLoanType().equals("grouploan"))
					{
						 i=dby.addDebitLoan(branchId,tt.getShareHolderNo(),tt.getLoanType(),tt.getSubLoanNo(),tt.getDisburseAmount(),paymentMode,bank,"",tt.getTransactionNo(),releaseDate,releaseDate);
						 	obj.decreaseBankBalance(bank, tt.getDisburseAmount(), conn);
						 
						 if(i>0) {
							 double fileCh = Double.parseDouble(tt.getFileCharge());
							 double insuranceFee =Double.parseDouble(tt.getInsuranceFee());
							 Double sum= fileCh + insuranceFee + tt.getProcessingCharge();
							 i=dby.addDebitLoan(branchId,tt.getShareHolderNo(),tt.getLoanType(),tt.getSubLoanNo(),sum,"cash","","","",releaseDate,releaseDate);
							 
							 	if(i>0)
							 	{	
									int z=obj.updateIncomeTableSelectedPendingEntryStatus("File Charge","active" , tt.getSubLoanNo(),conn,releaseDate);
									
								
									personalLoanList=obj.pendingAllLoanListForLoanDisburse(branchId,loanType, center);

									FacesContext fc=FacesContext.getCurrentInstance();
									fc.addMessage(null, new FacesMessage("Payment successfully done "+tt.getSubLoanNo()));
									int X=obj.updateIncomeTableSelectedPendingEntryStatus("Insurance Fee","active" ,tt.getSubLoanNo(),conn,releaseDate);
									if(X>0)
									{
										if(Double.valueOf( tt.getInsuranceFee())>0)
										{									

											obj.increaseCompanyCapitalAmount(branchId,Double.valueOf( tt.getInsuranceFee()), conn);
											fc.addMessage(null, new FacesMessage("Insurance Fee "+ tt.getInsuranceFee() +" Rs. Add Sucessfully."));
										}
									}

									int u=0;
									if ("NBFC".equalsIgnoreCase(companyName)) {
										
										u=dby.updateIncomeTableSelectedPendingEntryStatusForOnline("Processing Charge","active" ,tt.getSubLoanNo(),conn,releaseDate, paymentMode, tt.getTransactionNo(), releaseDate, bank);
										
									}
									else {
											u=obj.updateIncomeTableSelectedPendingEntryStatus("Processing Charge","active" ,tt.getSubLoanNo(),conn,releaseDate);
									}
									
									if(u>0)
									{
										if(Double.valueOf( tt.getProcessingCharge())>0)
										{									

											obj.increaseCompanyCapitalAmount(branchId,Double.valueOf( tt.getProcessingCharge()), conn);
											fc.addMessage(null, new FacesMessage("Processing Charge "+ tt.getProcessingCharge() +" Rs. Add Sucessfully."));
										}
									}


							 	}
							 


						 		}
					}
					else
					{
						 i=dby.addDebitLoan(branchId,tt.getShareHolderNo(),tt.getLoanType(),tt.getLoanNo(),tt.getDisburseAmount(),paymentMode,bank,"",tt.getTransactionNo(),releaseDate,releaseDate);
						 obj.decreaseBankBalance(bank, tt.getDisburseAmount(), conn);
						 
						 if(i>0) {
							 double fileCh = Double.parseDouble(tt.getFileCharge());
							 double insuranceFee =Double.parseDouble(tt.getInsuranceFee());
							 
							 Double sum= fileCh+insuranceFee+tt.getProcessingCharge();
							 if("NBFC".equals(companyType))
							 {
								 i=dby.addDebitLoan(branchId,tt.getShareHolderNo(),tt.getLoanType(),tt.getLoanNo(),sum,paymentMode,bank,"",tt.getTransactionNo(),releaseDate,releaseDate);

							 }
							 else
							 {
								 i=dby.addDebitLoan(branchId,tt.getShareHolderNo(),tt.getLoanType(),tt.getLoanNo(),sum,"cash","","","",releaseDate,releaseDate);

							 }
	
							 
							 	if(i>0)
							 	{
							 		
									int z=obj.updateIncomeTableSelectedPendingEntryStatus("File Charge","active" , tt.getLoanNo(),conn,releaseDate);

									
										
											personalLoanList=obj.pendingAllLoanListForLoanDisburse(branchId,loanType, center);

											FacesContext fc=FacesContext.getCurrentInstance();
											fc.addMessage(null, new FacesMessage("Payment successfully done "+tt.getLoanNo()));
										
											int X=obj.updateIncomeTableSelectedPendingEntryStatus("Insurance Fee","active" ,tt.getLoanNo(),conn,releaseDate);
											if(X>0)
											{
												if(Double.valueOf( tt.getInsuranceFee())>0)
												{									

													obj.increaseCompanyCapitalAmount(branchId,Double.valueOf( tt.getInsuranceFee()), conn);
													fc.addMessage(null, new FacesMessage("Insurance Fee "+ tt.getInsuranceFee() +" Rs. Add Sucessfully."));
												}
											}
											int u=0;
												if ("NBFC".equalsIgnoreCase(companyType)) {
													
													u=dby.updateIncomeTableSelectedPendingEntryStatusForOnline("Processing Charge","active" ,tt.getLoanNo(),conn,releaseDate, paymentMode, tt.getTransactionNo(), releaseDate, bank);
													
												}
												else {
														u=obj.updateIncomeTableSelectedPendingEntryStatus("Processing Charge","active" ,tt.getLoanNo(),conn,releaseDate);
												}
											if(u>0)
											{
												if(Double.valueOf( tt.getProcessingCharge())>0)
												{									

													obj.increaseCompanyCapitalAmount(branchId,Double.valueOf( tt.getProcessingCharge()), conn);
													fc.addMessage(null, new FacesMessage("Processing Charge "+ tt.getProcessingCharge() +" Rs. Add Sucessfully."));
												}
											}


											
							 	}
							
								


						 		}
					}
				}
			}

		}
		else
		{
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Please Select Atleast One Row"));
		}
		
	}
	
	//this method by ys
	public void calculateDisburseAmount()
	{
		checkPreviousLoan();
		
		for(LoanInfo tt :SelectedpersonalLoanList) {
			double fileC = Double.parseDouble(tt.getFileCharge());
			double insuranceFee= Double.parseDouble(tt.getInsuranceFee());
			double processingCharge=tt.getProcessingCharge();
			double loanA = Double.parseDouble(tt.getLoanAmount());
			tt.setDisburseAmount(loanA-(fileC+insuranceFee+processingCharge));
			
			if("14".equalsIgnoreCase(tt.getSecurityype())||"15".equalsIgnoreCase(tt.getSecurityype()))
			{
				tt.setDisburseAmount(loanA);
			}
		}
	}
	
	
	
	public void checkPreviousLoan()
	{
		for(LoanInfo tt :SelectedpersonalLoanList)
		{
			if (tt.getPreviousLoan() != null && !tt.getPreviousLoan().isEmpty())
			{
				FacesContext.getCurrentInstance().addMessage(null, 
						new FacesMessage("Customer " + tt.getShareHolderNo() + " already have exiting Loan : " + tt.getPreviousLoan()));
			}
		}
	}
	
	

	public void postProcessXLS(Object document)
	{
	    dbmh.postProcessXLS(document);
	}

	
	
	public ArrayList<LoanInfo> getPersonalLoanList() {
		return personalLoanList;
	}

	public void setPersonalLoanList(ArrayList<LoanInfo> personalLoanList) {
		this.personalLoanList = personalLoanList;
	}

	public LoanInfo getSelectedLoan() {
		return selectedLoan;
	}

	public void setSelectedLoan(LoanInfo selectedLoan) {
		this.selectedLoan = selectedLoan;
	}


	public String getLoanType() {
		return loanType;
	}


	public void setLoanType(String loanType) {
		this.loanType = loanType;
	}


	public boolean isShow() {
		return show;
	}


	public void setShow(boolean show) {
		this.show = show;
	}


	public ArrayList<LoanInfo> getSelectedpersonalLoanList() {
		return SelectedpersonalLoanList;
	}


	public void setSelectedpersonalLoanList(
			ArrayList<LoanInfo> selectedpersonalLoanList) {
		SelectedpersonalLoanList = selectedpersonalLoanList;
	}


	public String getSelType() {
		return selType;
	}


	public void setSelType(String selType) {
		this.selType = selType;
	}

	public boolean isCheckDisablityForMultiple() {
		return checkDisablityForMultiple;
	}


	public void setCheckDisablityForMultiple(boolean checkDisablityForMultiple) {
		this.checkDisablityForMultiple = checkDisablityForMultiple;
	}


	public boolean isCheckdisablityForIndividual() {
		return checkdisablityForIndividual;
	}


	public void setCheckdisablityForIndividual(boolean checkdisablityForIndividual) {
		this.checkdisablityForIndividual = checkdisablityForIndividual;
	}


	public Date getReleaseDate() {
		return releaseDate;
	}


	public void setReleaseDate(Date releaseDate) {
		this.releaseDate = releaseDate;
	}


	public double getTotalAmount() {
		return totalAmount;
	}


	public void setTotalAmount(double totalAmount) {
		this.totalAmount = totalAmount;
	}

	public String getCenter() {
		return center;
	}

	public void setCenter(String center) {
		this.center = center;
	}

	public boolean isShowCenter() {
		return showCenter;
	}

	public void setShowCenter(boolean showCenter) {
		this.showCenter = showCenter;
	}

	public ArrayList<SelectItem> getCenterList() {
		return centerList;
	}

	public void setCenterList(ArrayList<SelectItem> centerList) {
		this.centerList = centerList;
	}

	public int getTotalEntry() {
		return totalEntry;
	}

	public void setTotalEntry(int totalEntry) {
		this.totalEntry = totalEntry;
	}

	public ArrayList<SelectItem> getBankList() {
		return bankList;
	}

	public void setBankList(ArrayList<SelectItem> bankList) {
		this.bankList = bankList;
	}

	public String getPaymentMode() {
		return paymentMode;
	}

	public void setPaymentMode(String paymentMode) {
		this.paymentMode = paymentMode;
	}

	public String getBank() {
		return bank;
	}

	public void setBank(String bank) {
		this.bank = bank;
	}

	public String getCompanyType() {
		return companyType;
	}

	public void setCompanyType(String companyType) {
		this.companyType = companyType;
	}

	public boolean isMobDealer() {
		return mobDealer;
	}

	public void setMobDealer(boolean mobDealer) {
		this.mobDealer = mobDealer;
	}

	public String getCompanyName() {
		return companyName;
	}

	public void setCompanyName(String companyName) {
		this.companyName = companyName;
	}



	public boolean isCashfreeActive() {
		return cashfreeActive;
	}



	public void setCashfreeActive(boolean cashfreeActive) {
		this.cashfreeActive = cashfreeActive;
	}



	public String getEnachStatus() {
		return enachStatus;
	}



	public void setEnachStatus(String enachStatus) {
		this.enachStatus = enachStatus;
	}



	public String getEnachMessage() {
		return enachMessage;
	}



	public void setEnachMessage(String enachMessage) {
		this.enachMessage = enachMessage;
	}



	public boolean isEnachDialogVisible() {
		return enachDialogVisible;
	}



	public void setEnachDialogVisible(boolean enachDialogVisible) {
		this.enachDialogVisible = enachDialogVisible;
	}



	public boolean isShowEnachOptions() {
		return showEnachOptions;
	}



	public void setShowEnachOptions(boolean showEnachOptions) {
		this.showEnachOptions = showEnachOptions;
	}
	
	
	
}