package main_package;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
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.naming.spi.DirStateFactory.Result;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.util.SystemOutLogger;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.primefaces.component.sticky.Sticky;
import org.primefaces.context.RequestContext;
import org.primefaces.model.UploadedFile;

import com.itextpdf.text.pdf.codec.Base64.OutputStream;

@ManagedBean(name="excelImport")
@ViewScoped
public class ExcelImportBean implements Serializable
{
	String filePath, transferShareHolderNo;
	UploadedFile excelFile;
	ArrayList<SelectItem> transferShareList;
	ArrayList<ExcelSheetInfo> stuList=new ArrayList<>();
	int count=1, branchId;
	Date date1;
    
	SimpleDateFormat sdfForDb=new SimpleDateFormat("yyyy-MM-dd"); 
	DataBaseMethods obj=new DataBaseMethods();
	DataBaseMethods dbm = new DataBaseMethods();
	ArrayList<ExcelSheetInfo> dbViewList = new ArrayList<>();
// To get Data from db income_table	
    public void viewFunction()
    {
    	
       try(Connection conn = DataBaseConnection.JAVAConnection())
       {
    	   String query="select loan_no,amount from income_table where item=? and status=? and amount != 0 ";
    	   try(PreparedStatement ps= conn.prepareStatement(query))
    	   {
    		   ps.setString(1, "File Charge");
    		   ps.setString(2, "active");
    		   
    		  ResultSet rs = (ResultSet) ps.executeQuery();
    		  while(rs.next())   			  
    		  {
    			  ExcelSheetInfo es= new ExcelSheetInfo();
    			  
    			  es.setShareholderNo(dbm.shareHolderNoByLoanNo(rs.getString("loan_no")));
    			  es.setLoanNumber(rs.getString("loan_no"));
    			  es.setProAmount(rs.getDouble("amount"));
    			  
    			  dbViewList.add(es);
    		  }
    		  
    	   }
    	   catch(Exception e)
    	   {
    		   e.printStackTrace();
    	   }
       }
       catch(Exception e)
       {
    	   e.printStackTrace();
       }
    }
    
	public ExcelImportBean()
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		 branchId = (int) ss.getAttribute("branchId"); 	
		 viewFunction();
	}

//---->1. Import Method For Shareholder_info table(add customer)
	    
	public void importFromExcelSheetforHisarCustomerShareHolderInfo() throws IOException, ParseException
	{
		if(excelFile!=null && excelFile.getSize()>0)
		{
		filePath = excelFile.getFileName();		  
		makeProfile(excelFile, filePath); //Offline       
		ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();		
		String realPath = ctx.getRealPath("/");		
		filePath=realPath+filePath;
		}		
		if(filePath!=null || !filePath.equals(""))
		{
			String filename = filePath;		   
			List sheetData = new ArrayList();  
			FileInputStream fis = null;
			if (filename.endsWith("xlsx"))
			{
				try 
				{ 
					fis = new FileInputStream(filename);

					XSSFWorkbook workbook = new XSSFWorkbook(fis);
					XSSFSheet sheet = workbook.getSheetAt(0);
					Iterator rows = sheet.rowIterator();
					while (rows.hasNext()) 
					{
						XSSFRow row = (XSSFRow) rows.next();
						Iterator cells = row.cellIterator();
                        
						List data = new ArrayList();
						while (cells.hasNext()) 
						{
							XSSFCell cell = (XSSFCell) cells.next();
							data.add(cell);
						}
						
						sheetData.add(data);
					}
				} 
				catch (IOException e) {
					e.printStackTrace();
				} 
				finally {
					if (fis != null) {
						fis.close();
					}
				}				
				//FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Dear User You can upload excel file with extension .xls (97-2003)."));
			}			
			else
			{
				try 
				{					//System.out.println(filename);
					fis = new FileInputStream(filename);
                    
					HSSFWorkbook workbook = new HSSFWorkbook(fis);
					HSSFSheet sheet = workbook.getSheetAt(0);
					Iterator rows = sheet.rowIterator();
					while (rows.hasNext()) 
					{
						HSSFRow row = (HSSFRow) rows.next();
						Iterator cells = row.cellIterator();

						List data = new ArrayList();
						while (cells.hasNext()) 
						{
							HSSFCell cell = (HSSFCell) cells.next();
							data.add(cell);
						}
						
						sheetData.add(data);
					}
				} 
				catch (IOException e) {
					e.printStackTrace();
				} 
				finally {
					if (fis != null) {
						fis.close();
					}
				}
			}
			showExelDataSheetForHisarCustomer(sheetData);
		}			
	}
	
	private void showExelDataSheetForHisarCustomer(List<List<Cell>> sheetData) throws ParseException {
	    Connection conn = DataBaseConnection.JAVAConnection(); // Optional
	    stuList.clear();
	    DataFormatter fmt = new DataFormatter();

	    for (int i = 1; i < sheetData.size(); i++) {
	        List<Cell> list = sheetData.get(i);
	        ExcelSheetInfo info = new ExcelSheetInfo();

	        for (int j = 0; j < list.size(); j++) {
	            Cell cell = list.get(j);
	            if (cell == null) continue;

	            String temp = "";

	            // Get formatted string for all cells (dates also will be formatted to string)
	            temp = fmt.formatCellValue(cell).trim();

	            // Debug print of entire row (optional)
	            System.out.print("Row " + i + ": ");
	            for (int k = 0; k < list.size(); k++) {
	                System.out.print("[" + k + "]=" + fmt.formatCellValue(list.get(k)) + " ");
	            }
	            System.out.println();

	            switch (j) {
	                case 0: // Customer ID
	                    String custId = temp;
	                    if(custId.length() == 2) {
	                        custId = "101000" + custId;
	                    	
	                    }
	                    else  if (custId.length() == 3) {
	                        custId = "10100" + custId;
	                    } else if (custId.length() == 4) {
	                        custId = "1010" + custId;
	                    } else if (custId.length() == 5) {
	                        custId = "101" + custId;
	                    } else if (custId.length() >= 6) {
	                        // For length 6 or more, just prepend 101
	                        custId = "101" + custId;
	                    }
	                    
	                    info.setCustomerId(custId);
	                    break;
                        
	                case 1:
	                    info.setApplicantName(temp);
	                    System.out.println("APPLICANT NAME IS:"+temp);
	                    break;
                        
	                case 2:
	                    info.setAddress(temp);
	                    System.out.println("ADDRESS IS:"+temp);
	                    break;

	                case 3: // Date of Birth
	                    if (temp != null && !temp.isEmpty()) {
	                        try {
	                            Date parsedDate = new SimpleDateFormat("dd-MM-yyyy").parse(temp);
	                            info.setDateOfBirth(parsedDate);
	                        }
	                        catch (ParseException e) 
	                        {
	                            System.out.println("Invalid DOB format in row " + i + ": " + temp);
	                            info.setDateOfBirth(null);
	                        }
	                    }
	                    break;
                    
	                case 4:
	                    info.setNomineeName(temp);
	                    break;
                    
	                case 5: // Application Date
	                    if (temp != null && !temp.isEmpty()) 
	                    {
	                        try {
	                            Date parsedDate = new SimpleDateFormat("dd-MM-yyyy").parse(temp);
	                            
	                            info.setDate(parsedDate);
	                            
	                        } 
	                        catch(ParseException e)
	                        {
	                            System.out.println("Invalid Application Date format in row " + i + ": " + temp);
	                            info.setApDate(null);
	                        }
	                    }
	                    break;

	                case 6:
	                    info.setFatherHusbandName(temp);
	                    break;

	                case 7:
	                    info.setNomineeRelation(temp);
	                    break;

	                case 8:
	                    info.setMobileNo(temp);
	                    break;

	                case 9:
	                    info.setBranch(temp);
	                    break;

	                case 10:
	                    info.setVoterId(temp);
	                    break;

	                case 11:
	                    info.setAadharNumber(temp);
	                    break;
                    
	                case 12:
	                    info.setPanNumber(temp);
	                    break;

	                case 13:
	                    info.setGender(temp);
	                    break;

	                // add other cases if needed...
	            }
	        }

	        if (info.getCustomerId() != null && !info.getCustomerId().isEmpty()) {
	            stuList.add(info);
	        }
	    }
         
	    System.out.println("✅ Parsed " + stuList.size() + " records from Excel.");

	    if (stuList.isEmpty())
	    {
	        FacesContext.getCurrentInstance().addMessage(null,
	            new FacesMessage(FacesMessage.SEVERITY_WARN, "No valid data found in Excel!", null));
	    } else {
	        insertDataSheetForHisarCustomer(stuList);
	    }
	}

	public void insertDataSheetForHisarCustomer(List<ExcelSheetInfo> list) {
	    HttpSession session = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
	    int branchId = (int) session.getAttribute("branchId");

	    String query = "INSERT INTO shareholder_info (" +
	        "office_application_no, office_date, shareholderno, first_applicantName, address, dob, father_husband_name, " +
	        "nominee_name, nominee_relation, mobile, branch, voter_id, nom_aadhar, PAN, gender, branch_id" +
	        ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	    int successCount = 0;

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = (PreparedStatement) conn.prepareStatement(query)) {

	        conn.setAutoCommit(false);
            
	        for (ExcelSheetInfo info : list)
	        {
	            try {
	                ps.setString(1, info.getCustomerId()); // office_application_no
	                if (info.getDate() != null)
	                    ps.setDate(2, new java.sql.Date(info.getDate().getTime()));
	                else
	                    ps.setNull(2, Types.DATE);

	                ps.setString(3, info.getCustomerId()); // shareholder_no
	                ps.setString(4, info.getApplicantName());
	                System.out.println("applicantName is "+info.getApplicantName());
	                
	                ps.setString(5, info.getAddress());
	                
	                System.out.println("address is :"+info.getAddress());
                    
	                if (info.getDateOfBirth() != null)
	                    ps.setDate(6, new java.sql.Date(info.getDateOfBirth().getTime()));
	                else
	                    ps.setNull(6, Types.DATE);

	                ps.setString(7, info.getFatherHusbandName());
	                ps.setString(8, info.getNomineeName());
	                ps.setString(9, info.getNomineeRelation());
	                ps.setString(10, info.getMobileNo());
	                ps.setString(11, info.getBranch());
	                ps.setString(12, info.getVoterId());
	                ps.setString(13, info.getAadharNumber());
	                ps.setString(14, info.getPanNumber());
	                ps.setString(15, info.getGender());
	                ps.setInt(16, branchId);

	                ps.addBatch(); // Add batch instead of executing immediately

	                successCount++;
	            }
	            catch (Exception e) {
	                System.err.println("❌ Error preparing batch for: " + info.getCustomerId());
	                e.printStackTrace();
	            }
	        }

	        ps.executeBatch(); // Execute all batches at once
	        conn.commit();

	        FacesContext context = FacesContext.getCurrentInstance();
	        if (context != null) {
	            context.addMessage(null, new FacesMessage("✅ Inserted " + successCount + " rows successfully."));
	        }
	    } 
	    
	    catch(Exception e)
	    {
	        e.printStackTrace();
	        FacesContext context = FacesContext.getCurrentInstance();
	        if (context != null) {
	            context.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Database Error", e.getMessage()));
	        }
	    }
	}
	
	
	
	public void personalLoanEntry() {
		
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
			
			String query = "INSERT INTO personalloan ("
			        + "id, shareholderno, loanNo, idproof, filecharge, loanAmount, interest, terms, frequency, emi, "
			        + "firstguarantor, secondguarantor, requestDate, status, approvalDate, reason, RD_FD_no, paidStatus, "
			        + "discount, emiType, emi_due_date, app_chq_no, app_acc_no, app_bank_name, app_bank_branch, "
			        + "g1_chq_no, g1_acc_no, g1_bank_name, g1_bank_branch, "
			        + "g2_chq_no, g2_acc_no, g2_bank_name, g2_bank_branch, "
			        + "dsacode, commission, increase_amount, insurance_fee, user_id, restructure_status, plan_no, center, "
			        + "emi_mode, extra_amount, entry_date, remark, doc1, doc2, doc3, doc4, doc5, "
			        + "nom_chq_no, nom_acc_no, nom_bank_name, nom_bank_branch, nom_bank_ifsc, "
			        + "app_bank_ifsc, g1_bank_ifsc, g2_bank_ifsc, branch_id, "
			        + "doc6, doc7, doc8, doc9, doc10, processing_charge"
			        + ") VALUES ("
			        + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
			        + "?, ?, ?, ?, ?, ?, ?, ?, "
			        + "?, ?, ?, ?, ?, ?, ?, "
			        + "?, ?, ?, ?, "
			        + "?, ?, ?, ?, "
			        + "?, ?, ?, ?, ?, ?, ?, ?, "
			        + "?, ?, ?, ?, ?, ?, ?, ?, ?, "
			        + "?, ?, ?, ?, ?, "
			        + "?, ?, ?, ?, "
			        + "?, ?, ?, ?, ?, ?"
			        + ")";

		
			try(PreparedStatement ps = conn.prepareStatement(query)){
				
			}catch(Exception e) {
				e.printStackTrace();
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		
	}
    
// this importFromExcelSheetforHisarCustomerShareHolderInfo() methods ends here.
	public void importFromExcelSheetLoginFeeForIncomeTable() throws IOException, ParseException {
	    if (excelFile != null && excelFile.getSize() > 0) 
	    {
	        filePath = excelFile.getFileName();
     
	        makeProfile(excelFile, filePath); // Offline

	        ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
	        String realPath = ctx.getRealPath("/");
	        filePath = realPath + filePath;
	    }
        
	    if (filePath != null && !filePath.isEmpty()) {
	        String filename = filePath;
	        List<List<Cell>> sheetData = new ArrayList<>();
	        FileInputStream fis = null;

	        if (filename.endsWith("xlsx")) {
	            try {
	                fis = new FileInputStream(filename);
	                XSSFWorkbook workbook = new XSSFWorkbook(fis);
	                XSSFSheet sheet = workbook.getSheetAt(0);
	                Iterator<Row> rows = sheet.rowIterator();

	                while (rows.hasNext()) {
	                    XSSFRow row = (XSSFRow) rows.next();
	                    Iterator<Cell> cells = row.cellIterator();
	                    List<Cell> data = new ArrayList<>();

	                    while (cells.hasNext()) {
	                        XSSFCell cell = (XSSFCell) cells.next();
	                        data.add(cell);
	                    }

	                    sheetData.add(data);
	                }
	            } catch (IOException e) {
	                e.printStackTrace();
	            } finally {
	                if (fis != null) {
	                    fis.close();
	                }
	            }
	        } else {
	            try {
	                fis = new FileInputStream(filename);
	                HSSFWorkbook workbook = new HSSFWorkbook(fis);
	                HSSFSheet sheet = workbook.getSheetAt(0);
	                Iterator<Row> rows = sheet.rowIterator();

	                while (rows.hasNext()) {
	                    HSSFRow row = (HSSFRow) rows.next();
	                    Iterator<Cell> cells = row.cellIterator();
	                    List<Cell> data = new ArrayList<>();

	                    while (cells.hasNext()) {
	                        HSSFCell cell = (HSSFCell) cells.next();
	                        data.add(cell);
	                    }

	                    sheetData.add(data);
	                }
	            } catch (IOException e) {
	                e.printStackTrace();
	            } finally {
	                if (fis != null) {
	                    fis.close();
	                }
	            }
	        }
	        showExelDataSheetLoginFeeForIncomeTable(sheetData);
	    }
	}
	private void showExelDataSheetLoginFeeForIncomeTable(List<List<Cell>> sheetData) throws ParseException {
	    Connection conn = DataBaseConnection.JAVAConnection(); // Optional
	    stuList.clear();
	    DataFormatter fmt = new DataFormatter();

	    for (int i = 1; i < sheetData.size(); i++) {
	        List<Cell> list = sheetData.get(i);
	        ExcelSheetInfo info = new ExcelSheetInfo();

	        for (int j = 0; j < list.size(); j++) {
	            Cell cell = list.get(j);
	            if (cell == null) continue;

	            String temp = "";
	            
	            // Get formatted string for all cells (dates also will be formatted to string)
	            temp = fmt.formatCellValue(cell).trim();
                
	            // Debug print of entire row (optional)
	            System.out.print("Row " + i + ": ");
	            for (int k = 0; k < list.size(); k++)
	            {
	                System.out.print("[" + k + "]=" + fmt.formatCellValue(list.get(k)) + " ");
	            }	            
	            System.out.println();

	            switch (j)
	            { 
	            case 0: // Application Date
	                if (temp != null && !temp.isEmpty()) {
	                    try {
	                        // Excel me aa raha hai "3/21/25" => M/d/yy
	                        Date parsedDate = new SimpleDateFormat("M/d/yy").parse(temp);
	                        info.setDate(parsedDate);
	                    } catch (ParseException e1) {
	                        System.out.println("Invalid Date format in row " + i + ": " + temp);
	                        info.setDate(null);
	                    }
	                }
	                break;

	             case 1:
	            	info.setCategory(temp);
	            	break;
	             case 2:
	            	 info.setPaymentMode(temp);
		            	break;
	             case 3:
	            	    if (temp != null && !temp.isEmpty()) {
	            	        try {
	            	            info.setLoginFee(Double.parseDouble(temp));
	            	        } catch (NumberFormatException e) {
	            	            info.setLoginFee(0.0); // fallback value
	            	        }
	            	    } else {
	            	        info.setLoginFee(0.0);
	            	    }
	            	    break;

	            	
	             case 4: 
	            	 info.setDescription(temp);
		            	break;
		            	
	             case 5: 
	            	 info.setItem(temp);
		            	break;
		            	
	             case 6: 
	            	 info.setChequeStatus(temp);
		            	break;
		            	
	             case 7: 
	            	
	            	 String custId = temp;
	                    if(custId.length() == 1) 
	                    {
	                        custId = "PL-1020000" + custId;
	                    	
	                    }
	                    else if(custId.length() == 2)
	                    {
	                    	
	                        custId = "PL-102000" + custId;

	                    }
	                    else  if (custId.length() == 3)
	                    {
	                        custId = "PL-10200" + custId;
	                    }
	                    else if (custId.length() == 4)
	                    {
	                        custId = "PL-1020" + custId;
	                    } else if (custId.length() == 5) {
	                        custId = "PL-102" + custId;
	                    } else if (custId.length() >= 6) {
	                        // For length 6 or more, just prepend 101
	                        custId = "PL-101" + custId;
	                    }
	                    
	                    info.setCustomerId(temp);
	                    break;
	             case 8: 
	            	 info.setLoanType(temp);
		            	break;
		            	
	             case 9: 
	            	 info.setStatus(temp);
		            	break;
	                // add other cases if needed...
	            }
	        }

	        if (info.getCustomerId() != null && !info.getCustomerId().isEmpty()) {
	            stuList.add(info);
	        }
	    }
         
	    System.out.println("✅ Parsed " + stuList.size() + " records from Excel.");

	    if (stuList.isEmpty())
	    {
	        FacesContext.getCurrentInstance().addMessage(null,
	            new FacesMessage(FacesMessage.SEVERITY_WARN, "No valid data found in Excel!", null));
	    } else {
	        insertDataSheetLoginFeeForIncomeTableHisarCustomer(stuList);
	    }
	}
	public void insertDataSheetLoginFeeForIncomeTableHisarCustomer(List<ExcelSheetInfo> list) {
	    HttpSession session = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
	    int branchId = (int) session.getAttribute("branchId");

	    String query = "INSERT INTO income_table (" +
	    	    "income_date, category, payment_mode, amount, description, item, chequeStatus, loan_no, loan_type, status, branch_id" +
	    	    ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

   int successCount = 0;
   
	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = (PreparedStatement) conn.prepareStatement(query)) {

	        conn.setAutoCommit(false);
            
	        for (ExcelSheetInfo info : list)
	        {
	            try {
	            	ps.setDate(1, info.getDate() != null ? new java.sql.Date(info.getDate().getTime()) : null);
	            	ps.setString(2, info.getCategory());
	            	ps.setString(3, info.getPaymentMode());
	            	ps.setDouble(4, info.getFileCharge());   // amount
	            	ps.setString(5, info.getDescription());
	            	ps.setString(6, info.getItem());
	            	ps.setString(7, info.getChequeStatus());
	            	ps.setString(8, info.getCustomerId());  // loan_no
	            	ps.setString(9, info.getLoanType());
	            	ps.setString(10, info.getStatus());
	            	ps.setInt(11, branchId);
                    
	                ps.addBatch(); // Add batch instead of executing immediately

	                successCount++;
	            }
	            catch (Exception e) {
	                System.err.println("❌ Error preparing batch for: " + info.getCustomerId());
	                e.printStackTrace();
	            }
	        }

	        ps.executeBatch(); // Execute all batches at once
	        conn.commit();

	        FacesContext context = FacesContext.getCurrentInstance();
	        if (context != null) {
	            context.addMessage(null, new FacesMessage("✅ Inserted " + successCount + " rows successfully."));
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	        FacesContext context = FacesContext.getCurrentInstance();
	        if (context != null) {
	            context.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Database Error", e.getMessage()));
	        }
	    }
	}
// Upload Excel Sheet code for income table 
	public void importFromExcelProcessingFeesSheetForIncomeTable() throws IOException, ParseException {
	    if (excelFile != null && excelFile.getSize() > 0) {
	        filePath = excelFile.getFileName();

	        makeProfile(excelFile, filePath); // Offline
            
	        ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
	        String realPath = ctx.getRealPath("/");
	        filePath = realPath + filePath;
	    }

	    if (filePath != null && !filePath.isEmpty()) {
	        String filename = filePath;
	        List<List<Cell>> sheetData = new ArrayList<>();
	        FileInputStream fis = null;

	        if (filename.endsWith("xlsx")) {
	            try {
	                fis = new FileInputStream(filename);
	                XSSFWorkbook workbook = new XSSFWorkbook(fis);
	                XSSFSheet sheet = workbook.getSheetAt(0);
	                Iterator<Row> rows = sheet.rowIterator();

	                while (rows.hasNext()) {
	                    XSSFRow row = (XSSFRow) rows.next();
	                    Iterator<Cell> cells = row.cellIterator();
	                    List<Cell> data = new ArrayList<>();

	                    while (cells.hasNext()) {
	                        XSSFCell cell = (XSSFCell) cells.next();
	                        data.add(cell);
	                    }

	                    sheetData.add(data);
	                }
	            } catch (IOException e) {
	                e.printStackTrace();
	            } finally {
	                if (fis != null) {
	                    fis.close();
	                }
	            }
	        } else {
	            try {
	                fis = new FileInputStream(filename);
	                HSSFWorkbook workbook = new HSSFWorkbook(fis);
	                HSSFSheet sheet = workbook.getSheetAt(0);
	                Iterator<Row> rows = sheet.rowIterator();

	                while (rows.hasNext()) {
	                    HSSFRow row = (HSSFRow) rows.next();
	                    Iterator<Cell> cells = row.cellIterator();
	                    List<Cell> data = new ArrayList<>();

	                    while (cells.hasNext()) {
	                        HSSFCell cell = (HSSFCell) cells.next();
	                        data.add(cell);
	                    }

	                    sheetData.add(data);
	                }
	            } catch (IOException e) {
	                e.printStackTrace();
	            } finally {
	                if (fis != null) {
	                    fis.close();
	                }
	            }
	        }
	        showExelDataSheetForTestingPurposeIncomeTable(sheetData);
	    }
	}
	
	private void showExelDataSheetForTestingPurposeIncomeTable(List<List<Cell>> sheetData) throws ParseException {
	    Connection conn = DataBaseConnection.JAVAConnection(); // Optional
	    stuList.clear();
	    DataFormatter fmt = new DataFormatter();

	    for (int i = 1; i < sheetData.size(); i++) {
	        List<Cell> list = sheetData.get(i);
	        ExcelSheetInfo info = new ExcelSheetInfo();
            
	        for (int j = 0; j < list.size(); j++) {
	            Cell cell = list.get(j);
	            if (cell == null) continue;

	            String temp = "";
	            
	            // Get formatted string for all cells (dates also will be formatted to string)
	            temp = fmt.formatCellValue(cell).trim();
                
	            // Debug print of entire row (optional)
	            System.out.print("Row " + i + ": ");
	            for (int k = 0; k < list.size(); k++)
	            {
	                System.out.print("[" + k + "]=" + fmt.formatCellValue(list.get(k)) + " ");
	            }	            
	            System.out.println();

	            switch (j)
	            { 
//	            case 0: // Application Date
//	                if (temp != null && !temp.isEmpty()) {
//	                    try {
//	                        // Excel me aa raha hai "3/21/25" => M/d/yy
//	                        Date parsedDate = new SimpleDateFormat("M/d/yy").parse(temp);
//	                        info.setDate(parsedDate);
//	                    } catch (ParseException e1) {
//	                        System.out.println("Invalid Date format in row " + i + ": " + temp);
//	                        info.setDate(null);
//	                    }
//	                }
//	                break;

//	             case 1: 
//	            	info.setCategory(temp);
//	            	break;
//	             case 2:
//	            	 info.setPaymentMode(temp);
//		            	break;
	            case 0: 
	            	
	            	 String custId = temp;
	                    if(custId.length() == 1) 
	                    {
	                        custId = "PL-1020000" + custId;
	                    	
	                    }
	                    else if(custId.length() == 2)
	                    {
	                    	
	                        custId = "PL-102000" + custId;

	                    }
	                    else  if (custId.length() == 3)
	                    {
	                        custId = "PL-10200" + custId;
	                    }
	                    else if (custId.length() == 4)
	                    {
	                        custId = "PL-1020" + custId;
	                    } else if (custId.length() == 5) {
	                        custId = "PL-102" + custId;
	                    } else if (custId.length() >= 6) {
	                        // For length 6 or more, just prepend 102
	                        custId = "PL-102" + custId;
	                    }	                    
	                    info.setCustomerId(custId);
	                    break;
	             case 1:
	            	    if (temp != null && !temp.isEmpty()) {
	            	        try {
	            	            info.setFileCharge(Double.parseDouble(temp));
	            	        } catch (NumberFormatException e) {
	            	            info.setFileCharge(0.0); // fallback value
	            	        }
	            	    } else {
	            	        info.setFileCharge(0.0);
	            	    }
	            	    break;
	            
//	             case 4: 
//	            	 info.setDescription(temp);
//		            	break;
		            	
	           		            	
	             
//	             case 8: 
//	            	 info.setLoanType(temp);
//		            	break;
//		            	
//	             case 9: 
//	            	 info.setStatus(temp);
//		            	break;
	                // add other cases if needed...
	            }
	        }

	        if (info.getCustomerId() != null && !info.getCustomerId().isEmpty()) 
	        {
	            
                stuList.add(info);


	            
	        
	        }
	    }
         
	    System.out.println("✅ Parsed " + stuList.size() + " records from Excel.");

	    if (stuList.isEmpty()) {
	        FacesContext.getCurrentInstance().addMessage(null,
	            new FacesMessage(FacesMessage.SEVERITY_WARN, "No valid data found in Excel!", null));
	    } else {
	        // Step 1: Map bana lo dbViewList ka (LoanNo|Amount as key)
	        Map<String, Integer> dbMap = new HashMap<>();
	        for (ExcelSheetInfo es : dbViewList) {
	            String key = es.getShareholderNo().toLowerCase() + "|" + es.getProAmount();
	            dbMap.put(key, dbMap.getOrDefault(key, 0) + 1);
	        }

	        // Step 2: Compare stuList with dbViewList
	        List<ExcelSheetInfo> matchedList = new ArrayList<>();
	        List<ExcelSheetInfo> unmatchedStuList = new ArrayList<>();
	        List<ExcelSheetInfo> extraDbList = new ArrayList<>();

	        for (ExcelSheetInfo ee : stuList) {
	            String key = ee.getCustomerId().toLowerCase() + "|" + ee.getFileCharge();

	            if (dbMap.containsKey(key) && dbMap.get(key) > 0) {
	                matchedList.add(ee);
	                dbMap.put(key, dbMap.get(key) - 1); // consume match
	            } else
	            {
	                unmatchedStuList.add(ee); // excel ka data jo DB me nahi mila
	            }
	        }
	        // Step 3: Jo dbMap me bacha vo extra hai (duplicate/extra in DB)
	        for (ExcelSheetInfo es : dbViewList) {
	            String key = es.getLoanNumber().toLowerCase() + "|" + es.getProAmount();
	            if (dbMap.containsKey(key) && dbMap.get(key) > 0) {
	                extraDbList.add(es);
	                dbMap.put(key, dbMap.get(key) - 1);
	            }
	        }

	        // ✅ Print Summary
	        System.out.println("✅ Matched Records: " + matchedList.size());
	        System.out.println("❌ Missing in DB (present in Excel only): " + unmatchedStuList.size());
	        System.out.println("⚠ Extra in DB (duplicate/extra amount): " + extraDbList.size());

	        // ❌ Print unmatched Excel entries
	        if (!unmatchedStuList.isEmpty()) {
	            System.out.println("\n---- Missing in DB (Excel only) ----");
	            for (ExcelSheetInfo ee : unmatchedStuList) {
	                System.out.println("LoanNo: " + ee.getCustomerId() + " | FileCharge: " + ee.getFileCharge());
	            }
	        }

	        // ⚠ Print extra DB entries
	        if (!extraDbList.isEmpty()) {
	            System.out.println("\n---- Extra in DB (not in Excel) ----");
	            for (ExcelSheetInfo es : extraDbList) {
	                System.out.println("LoanNo: " + es.getLoanNumber() + " | ProAmount: " + es.getProAmount());
	            }
	        }
	    }

	       // insertDataSheetForIncomeTablyuuyeHisarCustomer(stuList);
	    }
	
	private void showExelDataSheetForIncomeTable(List<List<Cell>> sheetData) throws ParseException {
	    Connection conn = DataBaseConnection.JAVAConnection(); // Optional
	    stuList.clear();
	    DataFormatter fmt = new DataFormatter();

	    for (int i = 1; i < sheetData.size(); i++) {
	        List<Cell> list = sheetData.get(i);
	        ExcelSheetInfo info = new ExcelSheetInfo();

	        for (int j = 0; j < list.size(); j++) {
	            Cell cell = list.get(j);
	            if (cell == null) continue;

	            String temp = "";
	            
	            // Get formatted string for all cells (dates also will be formatted to string)
	            temp = fmt.formatCellValue(cell).trim();
                
	            // Debug print of entire row (optional)
	            System.out.print("Row " + i + ": ");
	            for (int k = 0; k < list.size(); k++)
	            {
	                System.out.print("[" + k + "]=" + fmt.formatCellValue(list.get(k)) + " ");
	            }	            
	            System.out.println();

	            switch (j)
	            { 
	            case 0: // Application Date
	                if (temp != null && !temp.isEmpty()) {
	                    try {
	                        // Excel me aa raha hai "3/21/25" => M/d/yy
	                        Date parsedDate = new SimpleDateFormat("M/d/yy").parse(temp);
	                        info.setDate(parsedDate);
	                    } catch (ParseException e1) {
	                        System.out.println("Invalid Date format in row " + i + ": " + temp);
	                        info.setDate(null);
	                    }
	                }
	                break;

	             case 1: 
	            	info.setCategory(temp);
	            	break;
	             case 2:
	            	 info.setPaymentMode(temp);
		            	break;
	             case 3:
	            	    if (temp != null && !temp.isEmpty()) {
	            	        try {
	            	            info.setFileCharge(Double.parseDouble(temp));
	            	        } catch (NumberFormatException e) {
	            	            info.setFileCharge(0.0); // fallback value
	            	        }
	            	    } else {
	            	        info.setFileCharge(0.0);
	            	    }
	            	    break;

	            	
	             case 4: 
	            	 info.setDescription(temp);
		            	break;
		            	
	             case 5: 
	            	 info.setItem(temp);
		            	break;
		            	
	             case 6: 
	            	 info.setChequeStatus(temp);
		            	break;
		            	
	             case 7: 
	            	
	            	 String custId = temp;
	                    if(custId.length() == 1) 
	                    {
	                        custId = "PL-1020000" + custId;
	                    	
	                    }
	                    else if(custId.length() == 2)
	                    {
	                    	
	                        custId = "PL-102000" + custId;

	                    }
	                    else  if (custId.length() == 3)
	                    {
	                        custId = "PL-10200" + custId;
	                    }
	                    else if (custId.length() == 4)
	                    {
	                        custId = "PL-1020" + custId;
	                    } else if (custId.length() == 5) {
	                        custId = "PL-102" + custId;
	                    } else if (custId.length() >= 6) {
	                        // For length 6 or more, just prepend 102
	                        custId = "PL-102" + custId;
	                    }	                    
	                    info.setCustomerId(custId);
	                    break;
	             case 8: 
	            	 info.setLoanType(temp);
		            	break;
		            	
	             case 9: 
	            	 info.setStatus(temp);
		            	break;
	                // add other cases if needed...
	            }
	        }

	        if (info.getCustomerId() != null && !info.getCustomerId().isEmpty()) {
	            stuList.add(info);
	        }
	    }
         
	    System.out.println("✅ Parsed " + stuList.size() + " records from Excel.");

	    if (stuList.isEmpty())
	    {
	        FacesContext.getCurrentInstance().addMessage(null,
	            new FacesMessage(FacesMessage.SEVERITY_WARN, "No valid data found in Excel!", null));
	    } else {
	        insertDataSheetForIncomeTableHisarCustomer(stuList);
	    }
	}
	public void insertDataSheetForIncomeTableHisarCustomer(List<ExcelSheetInfo> list) {
	    HttpSession session = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
	    int branchId = (int) session.getAttribute("branchId");

	    String query = "INSERT INTO income_table (" +
	    	    "income_date, category, payment_mode, amount, description, item, chequeStatus, loan_no, loan_type, status, branch_id" +
	    	    ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

   int successCount = 0;

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = (PreparedStatement) conn.prepareStatement(query)) {

	        conn.setAutoCommit(false);
            
	        for (ExcelSheetInfo info : list)
	        {
	            try {
	            	ps.setDate(1, info.getDate() != null ? new java.sql.Date(info.getDate().getTime()) : null);
	            	ps.setString(2, info.getCategory());
	            	ps.setString(3, info.getPaymentMode());
	            	ps.setDouble(4, info.getFileCharge());   // amount
	            	ps.setString(5, info.getDescription());
	            	ps.setString(6, info.getItem());
	            	ps.setString(7, info.getChequeStatus());
	            	ps.setString(8, info.getCustomerId());  // loan_no
	            	ps.setString(9, info.getLoanType());
	            	ps.setString(10, info.getStatus());
	            	ps.setInt(11, branchId);

	                ps.addBatch(); // Add batch instead of executing immediately

	                successCount++;
	            }
	            catch (Exception e) {
	                System.err.println("❌ Error preparing batch for: " + info.getCustomerId());
	                e.printStackTrace();
	            }
	        }

	        ps.executeBatch(); // Execute all batches at once
	        conn.commit();

	        FacesContext context = FacesContext.getCurrentInstance();
	        if (context != null) {
	            context.addMessage(null, new FacesMessage("✅ Inserted " + successCount + " rows successfully."));
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	        FacesContext context = FacesContext.getCurrentInstance();
	        if (context != null) {
	            context.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Database Error", e.getMessage()));
	        }
	    }
	}

// debit loan code	
//	public void Excel() throws IOException, ParseException
//	{	
//	System.out.println("bbpsExcel2 hitted !!!");
//	
//    if (excelFile != null && excelFile.getSize() > 0) {
//        Connection conn = DataBaseConnection.JAVAConnection();
//        filePath = excelFile.getFileName();
//          
//        dbm.makeProfile(excelFile, filePath);
//
//        String uploadPath = dbm.fileUploadPathDetail();
//        String realPath;
//        
//        if (uploadPath.trim().equalsIgnoreCase("")) {
//            ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
//            realPath = ctx.getRealPath("/");
//        } else {
//            realPath = uploadPath;
//        }
//
//        filePath = realPath + filePath;
//    } 
//
//    if (filePath != null && !filePath.equals("")) {
//        List<List<Cell>> sheetData = new ArrayList<>();
//        FileInputStream fis = null;
//
//        try {
//            fis = new FileInputStream(filePath);
//            if (filePath.endsWith("xlsx")) {
//                XSSFWorkbook workbook = new XSSFWorkbook(fis);
//                XSSFSheet sheet = workbook.getSheetAt(0);
//                for (Row row : sheet) {
//                    List<Cell> rowData = new ArrayList<>();
//                    for (Cell cell : row) {
//                        rowData.add(cell);
//                    }
//                    sheetData.add(rowData);
//                }
//            } else {
//                HSSFWorkbook workbook = new HSSFWorkbook(fis);
//                HSSFSheet sheet = workbook.getSheetAt(0);
//                for (Row row : sheet) {
//                    List<Cell> rowData = new ArrayList<>();
//                    for (Cell cell : row) {
//                        rowData.add(cell);
//                    }
//                    sheetData.add(rowData);
//                }
//            }
//        } finally {
//            if (fis != null) fis.close();
//        }
//
//        showExelData2(sheetData);
//               
//    }
//    else
//    {
//        FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error In File Uploading. Please Check!!!"));
//        
//    }
//}
//
// 
//    
//  
//private void showExelData2(List<List<Cell>> sheetData) throws ParseException
//{
//  System.out.println("showExelData2 hitted !!!");
//  
//  stuList = new ArrayList<>();
//  Connection conn = DataBaseConnection.JAVAConnection();
//
//  for (int i = 1; i < sheetData.size(); i++) {
//      List<Cell> list = sheetData.get(i);
//     ExcelSheetInfo info = new ExcelSheetInfo();
//      String value = "";
//
//      for (int j = 0; j < list.size(); j++) {
//          Cell cell = list.get(j);
//          if (cell == null) continue;
//
//          DataFormatter fmt = new DataFormatter();
//          value = fmt.formatCellValue(cell);
//
//          switch (j) 
//          {
//          case 0:
//        	    String shareHolderNo = value.trim();
//        	    if (shareHolderNo.length() == 2) {
//        	        shareHolderNo = "102000" + shareHolderNo;
//        	    } else if (shareHolderNo.length() == 3) {
//        	        shareHolderNo = "10200" + shareHolderNo;
//        	    } else if (shareHolderNo.length() == 4) {
//        	        shareHolderNo = "1020" + shareHolderNo;
//        	    } else if (shareHolderNo.length() == 5) {
//        	        shareHolderNo = "102" + shareHolderNo;
//        	    } else if (shareHolderNo.length() == 6 && !shareHolderNo.startsWith("PL")) {
//        	        String lno = shareHolderNo.substring(1, 6);
//        	        shareHolderNo = "102" + lno;
//        	    }
//
//        	    info.setLoanNo(shareHolderNo);  // ✅ Set as ShareholderNo
//        	    System.out.println("ShareHolderNo (LoanNo): " + shareHolderNo);
//        	    break;
//
//        	case 2:
//        	    String loanNo2 = value.trim();
//        	    if (loanNo2.length() == 2) {
//        	        loanNo2 = "PL-102000" + loanNo2;
//        	    } else if (loanNo2.length() == 3) {
//        	        loanNo2 = "PL-10200" + loanNo2;
//        	    } else if (loanNo2.length() == 4) {
//        	        loanNo2 = "PL-1020" + loanNo2;
//        	    } else if (loanNo2.length() == 5) {
//        	        loanNo2 = "PL-102" + loanNo2;
//        	    } else if (loanNo2.length() == 6 && !loanNo2.startsWith("PL")) {
//        	        String lno = loanNo2.substring(1, 6);
//        	        loanNo2 = "PL-102" + lno;
//        	    }
//
//        	    info.setLoanNumber(loanNo2);  // ✅ Set formatted loan number
//        	    System.out.println("Loan Number (Formatted): " + loanNo2);
//        	    break;
//
//
//              case 3: // Loan Amount
//            	    try 
//            	    {
//            	        info.setLoanAmount(value != null && !value.trim().isEmpty() ? Double.parseDouble(value) : 0.0);
//            	    } catch (NumberFormatException e) {
//            	        info.setLoanAmount(0.0); // or log the error
//            	    }
//            	    break;
//
//            	case 4: // Disburse Amount
//            	    try {
//            	        info.setDisburseAmount(value != null && !value.trim().isEmpty() ? Double.parseDouble(value) : 0.0);
//            	    } catch (NumberFormatException e) {
//            	        info.setDisburseAmount(0.0); // or log the error
//            	    }
//            	    break;
//              case 5: info.setPaymentMode(value); break;
//              case 6: info.setBankName(value); break;
//              case 7: info.setBankBranch(value); break;
//              case 8: info.setChequeNo(value); break;
//              case 9: // Approval Date
//            	    if (value != null && !value.trim().isEmpty()) {
//            	        try {
//            	            SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); // Change format to match your actual data
//            	            Date approvalDate = sdf.parse(value.trim());
//            	            info.setApprovalDate(approvalDate);
//            	        } catch (ParseException e) {
//            	            System.out.println("Invalid date format for Approval Date: " + value);
//            	            info.setApprovalDate(null); // or set default date
//            	        }
//            	    } else 
//            	    {
//            	        info.setApprovalDate(null);
//            	    }
//            	    break;
//   case 10: info.setStatus(value); break;
//   case 11: // Approval Date
//	    if (value != null && !value.trim().isEmpty()) {
//	        try 
//	        {
//	            SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); // Change format to match your actual data
//	            Date disDate = sdf.parse(value.trim());
//	            info.setDisburseDate(disDate);
//	        }
//	        catch (ParseException e)
//	        {
//	            System.out.println("Invalid date format for Approval Date: " + value);
//	            info.setDisburseDate(null); // or set default date
//	        }
//	    }
//	    else 
//	    {
//	        info.setDisburseDate(null);
//	    }
//	    break;
//
//              case 12: info.setBranch(value); break;
//          }        
//      }
//      // ✅ Add the object only once per row
//      stuList.add(info);
//  }
//
//  insertDataForDebitLoan(stuList);
//
//}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	public String bbpsExcel() throws IOException, ParseException 
	{
    	
        if (excelFile != null && excelFile.getSize() > 0) {
            Connection conn = DataBaseConnection.JAVAConnection();
            filePath = excelFile.getFileName();

            dbm.makeProfile(excelFile, filePath);

            String uploadPath = dbm.fileUploadPathDetail();
            String realPath;
            if (uploadPath.trim().equalsIgnoreCase("")) {
                ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
                realPath = ctx.getRealPath("/");
            } else {
                realPath = uploadPath;
            }

            filePath = realPath + filePath;
        }

        if (filePath != null && !filePath.equals("")) {
            List<List<Cell>> sheetData = new ArrayList<>();
            FileInputStream fis = null;

            try {
                fis = new FileInputStream(filePath);
                if (filePath.endsWith("xlsx")) {
                    XSSFWorkbook workbook = new XSSFWorkbook(fis);
                    XSSFSheet sheet = workbook.getSheetAt(0);
                    for (Row row : sheet) {
                        List<Cell> rowData = new ArrayList<>();
                        for (Cell cell : row) {
                            rowData.add(cell);   
                        }
                        sheetData.add(rowData);
                    }
                } else {
                    HSSFWorkbook workbook = new HSSFWorkbook(fis);
                    HSSFSheet sheet = workbook.getSheetAt(0);
                    for (Row row : sheet) {
                        List<Cell> rowData = new ArrayList<>();
                        for (Cell cell : row) {
                            rowData.add(cell);
                        }
                        sheetData.add(rowData);
                    }
                }
            } finally {
                if (fis != null) fis.close();
            }

            showExelData(sheetData);
            
            
            
        } else {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error In File Uploading. Please Check!!!"));
        }
        
        return "uploadExcel.xhtml";
    }

    private void showExelData(List<List<Cell>> sheetData) throws ParseException {
    	
        Connection conn = DataBaseConnection.JAVAConnection();
       
        for (int i = 1; i < sheetData.size(); i++) {
            List<Cell> list = sheetData.get(i);
            ExcelSheetInfo info = new ExcelSheetInfo();
            String temp = "";

            for (int j = 0;  j < list.size(); j++) {
                Cell cell = list.get(j);
                if (cell == null) continue;
                DataFormatter fmt = new DataFormatter();
                
                temp = fmt.formatCellValue(cell);

                switch (j) {
                
                
                case 0:
            	    String shareHolderNo = temp.trim();
            	    if (shareHolderNo.length() == 3) {
            	        shareHolderNo = "10200" + shareHolderNo;
            	    } 
    
            	    info.setLoanNo(shareHolderNo);  // ✅ Set as ShareholderNo
            	    System.out.println("ShareHolderNo (LoanNo): " + shareHolderNo);
            	    break;
                   
                case 1: 
                	info.setLoanType(temp); break;
                
            	case 2:
            	    String loanNo2 = temp;
            	    if (loanNo2.length() == 3) {
            	        loanNo2 = "PL-10200" + loanNo2;
            	    } 
            	    info.setLoanNumber(loanNo2);  // ✅ Set formatted loan number
            	    System.out.println("Loan Number (Formatted): " + loanNo2);
            	    break; 	
                	
            	case 3:   info.setlAmount(temp); break;  
            	
            	case 4: info.setdAmount(temp);break;
            	
               case 5: info.setPaymentMode(temp); break;
               case 6: info.setBankName(temp); break;
               case 7: info.setBankBranch(temp); break;
               case 8: info.setChequeNo(temp); break;
               case 9 : info.setApDate(temp);
               case 10 : info.setStatus(temp);
               case 11 : info.setApDate(temp);
               case 12: info.setBranch(temp); break;
               }
               
                stuList.add(info);
            }
           
        }
        
        insertDataForDebitLoan(stuList);
    }


public void insertDataForDebitLoan(ArrayList<ExcelSheetInfo> stuList)
{
    HttpSession ss = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
    int branchId = (int) ss.getAttribute("branchId");

    for (ExcelSheetInfo rr : stuList) 
    {   
    	
        try 
        {
            if (rr.getLoanNo() == null || rr.getLoanNumber() == null) 
            {
                System.out.println("❌ Skipping row due to null LoanNo or ShareholderNo: " + rr.getLoanNo());
                continue;
            }

//            Date approvalDate = rr.getApprovalDate();
//            Date disburseDate = rr.getDisburseDate();
//            Date officeDate = approvalDate != null ? approvalDate : disburseDate;

//            // ✅ Still null? Use today's date
//            if (officeDate == null) {
//                officeDate = new Date();
//                System.out.println("⚠️ Both Approval and Disburse Dates are null. Using current date.");
//            }

            double loanAmount = rr.getLoanAmount();
            double netDisburseAmount = rr.getDisburseAmount();

            System.out.println("LoanNo: " + rr.getLoanNumber() + ", LoanAmount: " + loanAmount + ", DisburseAmount: " + netDisburseAmount);

            // First disbursed part
            addDebitLoan(
                branchId,
                rr.getLoanNo(),
                "personalloan",
                rr.getLoanNumber(),
                netDisburseAmount,
                "NEFT",
                "1", "", "", rr.getApDate(),
                rr.getApDate()
            );

            double diffAmount = loanAmount - netDisburseAmount;
            if (diffAmount > 0)
            {
                addDebitLoan
                (
                    branchId,
                    rr.getLoanNo(),
                    "personalloan",
                    rr.getLoanNumber(),
                    diffAmount,
                    "CASH",
                    "1", "", "", rr.getApDate(),
                    rr.getApDate()
                );
            }

        } catch (Exception ex) {
            ex.printStackTrace();
            FacesContext.getCurrentInstance().addMessage(null,
                new FacesMessage(FacesMessage.SEVERITY_ERROR,
                "Error inserting loan number: " + rr.getLoanNumber(), null));
        }
    }
}

public int addDebitLoan(int branchId, String shareHolderNo, String loanCategory,
        String loanNo, double amount, String paymentMode, String bankName,
        String bankBranch, String chequeNo, String chequeDate, String date) 
{
    int i = 0;
    String status = "";
    Timestamp chequeTimestamp = null;
    Timestamp entryTimestamp = null;
    java.sql.Date sqlDate = null;
      
    try 
    {
       

        // Step 1: Convert String to java.util.Date
        SimpleDateFormat inputFormat = new SimpleDateFormat("dd-MM-yyyy");
        Date utilDate = inputFormat.parse(chequeDate);

        // Step 2: Convert java.util.Date to java.sql.Date
        sqlDate = new java.sql.Date(utilDate.getTime());

        // ✅ Now sqlDate contains correct value in yyyy-MM-dd format
        System.out.println("Converted SQL Date: " + sqlDate);

    } catch (Exception e) {
        e.printStackTrace();
    }
     

//    // ✅ Fallback logic for entry date
//    Date now = new Date();
//    if (date == null || date.getTime() < Timestamp.valueOf("1970-01-01 00:00:00").getTime()) {
//        date = now;
//    }
//
//    // ✅ Safe entry timestamp
//    if (date != null) {
//        entryTimestamp = new Timestamp(date.getTime());
//    }

    // ✅ Determine status
    if ("Cash".equalsIgnoreCase(paymentMode) || "Transfer".equalsIgnoreCase(paymentMode)) 
    {
        status = "cleared";
        if (bankName == null || bankName.trim().isEmpty())
        {
            bankName = "N/A";
        }
    }
    else if ("Cheque".equalsIgnoreCase(paymentMode) ||
               "Demand Draft".equalsIgnoreCase(paymentMode) ||
               "NEFT".equalsIgnoreCase(paymentMode) ||
               "RTGS".equalsIgnoreCase(paymentMode) ||
               "IMPS".equalsIgnoreCase(paymentMode)) {
        status = "uncleared";
    } else {
        status = "pending";
    }

    String query = "INSERT INTO debit_loan (shareholderno, loanType, loanNo, amount, paymentMode, bankName, bankBranch, "
                 + "chequeNo, chequeDate, status, date, branch_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    try (Connection conn = DataBaseConnection.JAVAConnection();
         PreparedStatement st = conn.prepareStatement(query)) {

        st.setString(1, shareHolderNo);
        st.setString(2, loanCategory);
        st.setString(3, loanNo);
        st.setDouble(4, amount);
        st.setString(5, paymentMode);
        st.setString(6, (bankName != null && !bankName.trim().isEmpty()) ? bankName : "N/A");
        st.setString(7, (bankBranch != null) ? bankBranch : "");
        st.setString(8, (chequeNo != null) ? chequeNo : "");

     
        st.setDate(9, sqlDate);
        
        
        st.setString(10, status);
        
//        java.util.Date utilDate = new java.util.Date(); // ya jo bhi aapki date aa rahi hai
//        java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());

        st.setDate(11, sqlDate);


        

        st.setInt(12, branchId);

           i = st.executeUpdate();

    } catch (Exception e) {
        System.out.println("❌ Error inserting debit_loan → " + e.getMessage());
        e.printStackTrace();
    }

    return i;
}
public void importProcessingFeesExcelInPersonalloan() throws IOException
{
	System.out.println("import function hit");
	 if (excelFile != null && excelFile.getSize() > 0) {
	        // Step 1: Save uhi ploaded file to server
	        filePath = excelFile.getFileName();
	        makeProfile(excelFile, filePath); // Save the file locally

	        // Step 2: Construct absolute file path
	        ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
	        String realPath = ctx.getRealPath("/");
	        File savedFile = new File(realPath, filePath);
	        filePath = savedFile.getAbsolutePath(); // full path

	        if (filePath != null && !filePath.trim().equals("")) {
	            List<List<Cell>> sheetData = new ArrayList<>();
	            FileInputStream fis = null;

	            try 
	            {
	                fis = new FileInputStream(filePath);

	                if (filePath.endsWith("xlsx")) {
	                    try (XSSFWorkbook workbook = new XSSFWorkbook(fis)) {
	                        XSSFSheet sheet = workbook.getSheetAt(0);
	                        for (Row row : sheet) {
	                            List<Cell> data = new ArrayList<>();
	                            for (Cell cell : row) {
	                                data.add(cell);
	                            }
	                            sheetData.add(data);
	                        }
	                    }
	                } else {
	                    try (HSSFWorkbook workbook = new HSSFWorkbook(fis)) {
	                        HSSFSheet sheet = workbook.getSheetAt(0);
	                        for (Row row : sheet) {
	                            List<Cell> data = new ArrayList<>();
	                            for (Cell cell : row) {
	                                data.add(cell);
	                            }
	                            sheetData.add(data);
	                        }
	                    }
	                }

	                showExelDataSheetForHisarToAddProcessingFeesInPersonalLoanTable(sheetData);

	            } 
	            catch (IOException e) 
	            {
	                e.printStackTrace();
	                FacesContext.getCurrentInstance().addMessage(null,
	                new FacesMessage(FacesMessage.SEVERITY_ERROR, "File read error: " + e.getMessage(), null));
	            } 
	            finally 
	            {
	                if (fis != null) fis.close();
	            }
	        }
	    }
}
public void showExelDataSheetForHisarToAddProcessingFeesInPersonalLoanTable(List<List<Cell>> sheetData)
{
	System.out.println("add in sheetData showExcel methods");
	 Connection conn = DataBaseConnection.JAVAConnection();
	    stuList.clear();
	    DataFormatter fmt = new DataFormatter();

	    for (int i = 1; i < sheetData.size(); i++) {
	        List<Cell> list = sheetData.get(i);
	        ExcelSheetInfo info = new ExcelSheetInfo();

	        for (int j = 0; j < list.size(); j++) {
	            Cell cell = list.get(j);
	            if (cell == null) continue;
	            String temp = fmt.formatCellValue(cell).trim();
                
	            switch (j)
	            {
	                case 0:
	                    String custId2 = temp;
	                    if(custId2.length()==1) custId2="1010000"+custId2;
	                    
	                    else  if (custId2.length() == 2) custId2 = "101000" + custId2;
	                    else if (custId2.length() == 3) custId2 = "10100" + custId2;
	                    else if (custId2.length() == 4) custId2 = "1010" + custId2;
	                    else if (custId2.length() == 5) custId2 = "101" + custId2;
	                    else if (custId2.length() >= 6) custId2 = "101" + custId2;
	                    info.setCustomerId(custId2);
                   
	                break;
	                case 1:
	                	String custId = temp;
	                    if(custId.length()==1) custId="PL-1010000"+custId;
                        
	                    if (custId.length() == 2) custId = "PL-101000" + custId;
	                    else if (custId.length() == 3) custId = "PL-10100" + custId;
	                    else if (custId.length() == 4) custId = "PL-1010" + custId;
	                    else if (custId.length() == 5) custId = "PL-101" + custId;
	                    else if (custId.length() >= 6) custId = "PL-101" + custId;
	                    info.setLoanNo(custId);
	                    break;	                
	                case 2: info.setProcessingAmount(parseDoubleSafe(temp)); break;
	            }
	        }
	        stuList.add(info);
	        
	        System.out.println("add in stuList");
	    }

	    if (stuList.isEmpty()) {
	        FacesContext.getCurrentInstance().addMessage(null,
	            new FacesMessage(FacesMessage.SEVERITY_WARN, "No valid data found in Excel!", null));
	    } else {
	    	updateProcessingFeesInPersonalLoanTable(stuList);
	        FacesContext.getCurrentInstance().addMessage(null,
	            new FacesMessage(FacesMessage.SEVERITY_INFO, stuList.size() + " records imported successfully.", filePath));
	    }
}

private void updateProcessingFeesInPersonalLoanTable(List<ExcelSheetInfo> stuList)
{
	System.out.println("hit update processing method");
    HttpSession ss = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
    Object obj = ss.getAttribute("branchId");
    if (obj instanceof Integer) {
        branchId = (Integer) obj;
    } else if (obj instanceof String) {
        branchId = Integer.parseInt((String) obj);
    }
    System.out.println("reach above query");
    String updateSql = "UPDATE personalloan " +
                       "SET processing_charge = ? " +
                       "WHERE loanNo = ?  AND shareholderno = ? " +
                       "AND branch_id = ? ";
    
    try (Connection conn = DataBaseConnection.JAVAConnection();
         PreparedStatement pstmt = conn.prepareStatement(updateSql)) 
    {
        for (ExcelSheetInfo info : stuList)
        {
        	System.out.println("enter in loop");
            if (info.getLoanNo() != null  && info.getCustomerId() != null) 
            {                          
                pstmt.setDouble(1, info.getProcessingAmount());      // Excel → processingAmount
                pstmt.setString(2, info.getLoanNo());                // Loan No
                pstmt.setString(3, info.getCustomerId());           // Customer Name
                pstmt.setInt(4, branchId);  
                
                System.out.println(info.getLoanNo());
                System.out.println(info.getCustomerId());
                System.out.println(branchId);
                             
                int rows = pstmt.executeUpdate();
                System.out.println(rows);
                
                if (rows > 0) {
                    System.out.println("✅ Updated processing_charge for LoanNo: " 
                        + info.getLoanNo() + " | Branch Id: " + branchId + " | CustomerId: " + info.getCustomerId());
                } else {
                    System.out.println("⚠ No record found for LoanNo: " 
                        + info.getLoanNo() + " | Branch Id: " + branchId + " | CustomerId: " + info.getCustomerId());
                }
            }
        }
        

        FacesContext.getCurrentInstance().addMessage(null,
            new FacesMessage(FacesMessage.SEVERITY_INFO, "Processing charges updated successfully.", null));

    } catch (SQLException e) {
        e.printStackTrace();
        FacesContext.getCurrentInstance().addMessage(null,
            new FacesMessage(FacesMessage.SEVERITY_ERROR, "Database update error: " + e.getMessage(), null));
    }
}


// add personal loan code	
	public void importFromExcelSheetforHisarPersonalLoanInPersonalLoanTable() throws IOException, ParseException {
	    if (excelFile != null && excelFile.getSize() > 0) {
	        // Step 1: Save uhi ploaded file to server
	        filePath = excelFile.getFileName();
	        makeProfile(excelFile, filePath); // Save the file locally

	        // Step 2: Construct absolute file path
	        ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
	        String realPath = ctx.getRealPath("/");
	        File savedFile = new File(realPath, filePath);
	        filePath = savedFile.getAbsolutePath(); // full path

	        if (filePath != null && !filePath.trim().equals("")) {
	            List<List<Cell>> sheetData = new ArrayList<>();
	            FileInputStream fis = null;

	            try 
	            {
	                fis = new FileInputStream(filePath);

	                if (filePath.endsWith("xlsx")) {
	                    try (XSSFWorkbook workbook = new XSSFWorkbook(fis)) {
	                        XSSFSheet sheet = workbook.getSheetAt(0);
	                        for (Row row : sheet) {
	                            List<Cell> data = new ArrayList<>();
	                            for (Cell cell : row) {
	                                data.add(cell);
	                            }
	                            sheetData.add(data);
	                        }
	                    }
	                } else {
	                    try (HSSFWorkbook workbook = new HSSFWorkbook(fis)) {
	                        HSSFSheet sheet = workbook.getSheetAt(0);
	                        for (Row row : sheet) {
	                            List<Cell> data = new ArrayList<>();
	                            for (Cell cell : row) {
	                                data.add(cell);
	                            }
	                            sheetData.add(data);
	                        }
	                    }
	                }

	                showExelDataSheetForHisarToAddPersonalLoanInPersonalLoanTable(sheetData);

	            } catch (IOException e) {
	                e.printStackTrace();
	                FacesContext.getCurrentInstance().addMessage(null,
	                    new FacesMessage(FacesMessage.SEVERITY_ERROR, "File read error: " + e.getMessage(), null));
	            } finally {
	                if (fis != null) fis.close();
	            }
	        }
	    }
	}

	public void makeProfile(UploadedFile file, String filePath) {
	    Connection conn = DataBaseConnection.JAVAConnection();

	    try {
	        ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
	        String savePath = ctx.getRealPath("/");
	        File targetFile = new File(savePath, filePath);
	        File parentDir = targetFile.getParentFile();

	        if (!parentDir.exists()) {
	            parentDir.mkdirs(); // Create directories
	        }

	        System.out.println("Saving file to: " + targetFile.getAbsolutePath());

	        InputStream in = file.getInputstream();
	        FileOutputStream out = new FileOutputStream(targetFile);

	        byte[] bytes = new byte[1024];
	        int read;
	        while ((read = in.read(bytes)) != -1) {
	            out.write(bytes, 0, read);
	        }

	        in.close();
	        out.flush();
	        out.close();
	    } catch (Exception ex) {
	        ex.printStackTrace();
	    } finally {
	        try {
	            conn.close();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	}
	
	private void showExelDataSheetForHisarToAddPersonalLoanInPersonalLoanTable(List<List<Cell>> sheetData) throws ParseException 
	{
	    Connection conn = DataBaseConnection.JAVAConnection();
	    stuList.clear();
	    DataFormatter fmt = new DataFormatter();

	    for (int i = 1; i < sheetData.size(); i++) {
	        List<Cell> list = sheetData.get(i);
	        ExcelSheetInfo info = new ExcelSheetInfo();

	        for (int j = 0; j < list.size(); j++) {
	            Cell cell = list.get(j);
	            if (cell == null) continue;
	            String temp = fmt.formatCellValue(cell).trim();

	            switch (j)
	            {
	                case 0:
	                    String custId2 = temp;
	                    if (custId2.length() == 2) custId2 = "102000" + custId2;
	                    else if (custId2.length() == 3) custId2 = "10200" + custId2;
	                    else if (custId2.length() == 4) custId2 = "1020" + custId2;
	                    else if (custId2.length() == 5) custId2 = "102" + custId2;
	                    else if (custId2.length() >= 6) custId2 = "102" + custId2;
	                    info.setCustomerId(custId2);

	                    String custId = temp;
	                    if (custId.length() == 2) custId = "PL-102000" + custId;
	                    else if (custId.length() == 3) custId = "PL-10200" + custId;
	                    else if (custId.length() == 4) custId = "PL-1020" + custId;
	                    else if (custId.length() == 5) custId = "PL-102" + custId;
	                    else if (custId.length() >= 6) custId = "PL-102" + custId;
	                    info.setLoanNo(custId);
	                    break;
	                
	                case 1: info.setLoanAmount(parseDoubleSafe(temp)); break;
	                
	                case 2: info.setInterestRate(parseDoubleSafe(temp)); break;
	                
	                case 3: info.setTerms(temp); break;
	                
	                case 4: info.setFrequency(temp); break;
	                
	                case 5: info.setEmiAmount(parseDoubleSafe(temp)); break;
	                
	                case 6: info.setApprovalDate(parseDateSafe1(temp, i, "Approval Date")); break;
	                
	                case 7: info.setFirstEmiDate(parseDateSafe1(temp, i, "EMI Date")); break;
	                case 8: info.setFileCharge(parseDoubleSafe(temp)); break;
	                case 9: info.setInsuranceFees(parseDoubleSafe(temp)); break;
	                case 10: info.setDisburseDate(parseDateSafe1(temp, i, "Disburse Date")); break;
	                case 11: info.setDisrsburseAmount(parseDoubleSafe(temp)); break;
	            }
	        }
	        stuList.add(info);
	    }

	    if (stuList.isEmpty()) {
	        FacesContext.getCurrentInstance().addMessage(null,
	            new FacesMessage(FacesMessage.SEVERITY_WARN, "No valid data found in Excel!", null));
	    } else {
	        insertDataSheetForPersonalLoanInPerosnalLoanTable(stuList);
	        FacesContext.getCurrentInstance().addMessage(null,
	            new FacesMessage(FacesMessage.SEVERITY_INFO, stuList.size() + " records imported successfully.", filePath));
	    }
	}

	private void insertDataSheetForPersonalLoanInPerosnalLoanTable(List<ExcelSheetInfo> stuList)
	{
	    HttpSession ss = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
	    branchId = (int) ss.getAttribute("branchId");

	    String personalLoanSql ="INSERT INTO personalloan " +
	        "(shareholderno, loanNo, loanAmount, interest, terms, frequency, emi, approvalDate, emi_due_date, " +
	        "filecharge, insurance_fee, requestDate, paidStatus, emi_mode, branch_id) " +
	        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement personalLoanStmt = conn.prepareStatement(personalLoanSql)) {

	        for (ExcelSheetInfo info : stuList) {
	            personalLoanStmt.setString(1, info.getCustomerId());
	            personalLoanStmt.setString(2, info.getLoanNo());
	            personalLoanStmt.setDouble(3, info.getLoanAmount());
	            personalLoanStmt.setDouble(4, info.getInterestRate());
	            personalLoanStmt.setString(5, info.getTerms());
	            personalLoanStmt.setString(6, "12");
	            personalLoanStmt.setDouble(7, info.getEmiAmount());

	            if (info.getApprovalDate() != null)
	                personalLoanStmt.setDate(8, new java.sql.Date(info.getApprovalDate().getTime()));
	            else
	                personalLoanStmt.setNull(8, java.sql.Types.DATE);

	            if (info.getFirstEmiDate() != null)
	                personalLoanStmt.setDate(9, new java.sql.Date(info.getFirstEmiDate().getTime()));
	            else
	                personalLoanStmt.setNull(9, java.sql.Types.DATE);

	            personalLoanStmt.setDouble(10, info.getFileCharge());
	            personalLoanStmt.setDouble(11, info.getInsuranceFees());

	            if (info.getApprovalDate() != null)
	                personalLoanStmt.setDate(12, new java.sql.Date(info.getApprovalDate().getTime()));
	            else
	                personalLoanStmt.setNull(12, java.sql.Types.DATE);

	            personalLoanStmt.setString(13, "unpaid");
	            personalLoanStmt.setString(14, "both");
	            personalLoanStmt.setInt(15, branchId);
                
	            if(!info.getCustomerId().equals("")&&!info.getCustomerId().equalsIgnoreCase(null)) {
	            	
	            	personalLoanStmt.executeUpdate();

	            }
	        }

	        System.out.println("✅ Records inserted into personalloan table.");
	    } 
	    catch (SQLException e) {
	        e.printStackTrace();
	        FacesContext.getCurrentInstance().addMessage(null,
	            new FacesMessage(FacesMessage.SEVERITY_ERROR, "Database insert error: " + e.getMessage(), null));
	    }
	}

	// Helpers

	private static double parseDoubleSafe(String input) {
	    try {
	        if (input == null || input.trim().isEmpty()) return 0;
	        return Double.parseDouble(input.replace(",", "").trim());
	    } catch (NumberFormatException e) {
	        return 0;
	    }
	}

	private Date parseDateSafe1(String dateStr, int rowIndex, String label) {
	    if (dateStr == null || dateStr.trim().isEmpty()) return null;

	    String[] patterns = {
	        "dd-MM-yyyy", "M/d/yy", "MM/dd/yy",
	        "M/d/yyyy", "MM/dd/yyyy", "dd/MM/yyyy"
	    };

	    for (String pattern : patterns) {
	        try {
	            return new SimpleDateFormat(pattern).parse(dateStr);
	        } catch (ParseException ignored) {}
	    }

	    System.out.println("❌ Invalid " + label + " format in row " + rowIndex + ": " + dateStr);
	    return null;
	}


	
	public void importFromExceForRepayment() throws ParseException, IOException
	{   
		System.out.println("importFromExcelForRepayment");
		  if (excelFile != null && excelFile.getSize() > 0) {
	            Connection conn = DataBaseConnection.JAVAConnection();
	            filePath = excelFile.getFileName();
	              
	            dbm.makeProfile(excelFile, filePath);

	            String uploadPath = dbm.fileUploadPathDetail();
	            String realPath;
	            
	            if (uploadPath.trim().equalsIgnoreCase("")) {
	                ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
	                realPath = ctx.getRealPath("/");
	            } else {
	                realPath = uploadPath;
	            }

	            filePath = realPath + filePath;
	        } 

	        if (filePath != null && !filePath.equals("")) {
	            List<List<Cell>> sheetData = new ArrayList<>();
	            FileInputStream fis = null;

	            try {
	                fis = new FileInputStream(filePath);
	                if (filePath.endsWith("xlsx")) {
	                    XSSFWorkbook workbook = new XSSFWorkbook(fis);
	                    XSSFSheet sheet = workbook.getSheetAt(0);
	                    for (Row row : sheet) {
	                        List<Cell> rowData = new ArrayList<>();
	                        for (Cell cell : row) {
	                            rowData.add(cell);
	                        }
	                        sheetData.add(rowData);
	                    }
	                } else {
	                    HSSFWorkbook workbook = new HSSFWorkbook(fis);
	                    HSSFSheet sheet = workbook.getSheetAt(0);
	                    for (Row row : sheet) {
	                        List<Cell> rowData = new ArrayList<>();
	                        for (Cell cell : row) {
	                            rowData.add(cell);
	                        }
	                        sheetData.add(rowData);
	                        System.out.println("sheetdata"+sheetData);
	                    }
	                }
	            } finally {
	                if (fis != null) fis.close();
	            }

	            showExcelDataForRepayment(sheetData);
	          
	            
	        } 
	        else
	        {
	            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error In File Uploading. Please Check!!!"));
	            
	        }
			
		
}
//excel sheet for RepaymentLoan loan	
	private void showExcelDataForRepayment(List<List<Cell>> sheetData) throws ParseException 
	{
		System.out.println("ponda londa ----");
	    Connection conn = DataBaseConnection.JAVAConnection(); 
	    stuList.clear();
        System.out.println("1");
	    DataFormatter formatter = new DataFormatter();
	    
	    for (int i = 1; i < sheetData.size(); i++) { 
	        List<Cell> row = sheetData.get(i);
	        ExcelSheetInfo info = new ExcelSheetInfo();
	       
	        for (int j = 0; j < row.size(); j++) {
	            Cell cell = row.get(j);
	            if (cell == null) continue;

	            String value = "";

	            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) {
	                Date date = cell.getDateCellValue();
	                value = new SimpleDateFormat("dd-MM-yyyy").format(date);
	            } else {
	                value = formatter.formatCellValue(cell).trim();
	            }

	            // 🔁 Move this outside the 'if' block
	            switch (j) 
	            {
	                case 0:
	                    String loanNo = value.trim();
	                    if (!loanNo.startsWith("PL-101")) 
	                    {
	                    	if (loanNo.length() == 1)
	                    	{
	                            loanNo = "PL-1010000" + loanNo;

	                        }
	                    		                    	
	                    	else	if (loanNo.length() == 2)
	                    	{
	                            loanNo = "PL-101000" + loanNo;

	                        }
	                    	
	                    else if (loanNo.length() == 3)
	                    	{
	                            loanNo = "PL-10100" + loanNo;

	                        }
     	                   
	                    	else  if (loanNo.length() == 4) 
	                        {
	                            loanNo = "PL-1010" + loanNo;
	                        } else if (loanNo.length() == 5) {
	                            loanNo = "PL-101" + loanNo;
	                        } else if (loanNo.length() == 6 && !loanNo.startsWith("PL")) {
	                            String lno = loanNo.substring(1, 6);
	                            loanNo = "PL-101" + lno;
	                        }
	                    }

	                    info.setLoanNumber(loanNo); 
	                  //  System.out.println("lnumber"+info);
	               	       break;
	                
	                case 1: info.setReceivedDate(parseDateSafe(value, i, "Approval Date")); break;
	                case 2: info.setBounceCharge(parseDoubleSafe(value)); 
	               // System.out.println("bounce charge amount from sheet is:"+value);
	                break;//bounce charge
				case 3:info.setInterestLoan(parseDoubleSafe(value));
				//System.out.println("interet laon amont is:"+value);
				break;//interest loan
				
	                case 4: info.setPanelCharge(parseDoubleSafe(value));
	                // System.out.println("panelcharge amount is"+value);
	                 break;// panel charge
	                case 5: info.setReceivedPenalty(parseDoubleSafe(value)); 
	                  System.out.println("recevied penalty is:"+value);
	                break;//
	                
	                case 6: info.setLoanAmount(parseDoubleSafe(value)); 
	               // System.out.println("llllllllllllllllllll :"+value);
	                break;
	                case 7: info.setPaymentMode(value); break;
	            }

	        }
	        stuList.add(info);

	    }
	    System.out.println("stulist" + stuList.size());

	    if (stuList.isEmpty()) {
	        FacesContext.getCurrentInstance().addMessage(null,
	            new FacesMessage(FacesMessage.SEVERITY_WARN, "No valid data found in Excel!", null));
	    } else {
	    	System.out.println("stulist"+stuList.size());
	    	insertDataForRepayment(stuList);
	    }
	}

	public void insertDataForRepayment(ArrayList<ExcelSheetInfo> stuList)
	{   int i = 1;     
	    HttpSession ss = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
	    int branchId = (int) ss.getAttribute("branchId");
	    
	    try (Connection conn = DataBaseConnection.JAVAConnection()) 
	    {
	        conn.setAutoCommit(false);  // Start transaction

	        for (ExcelSheetInfo rr : stuList) {
	            try {
	                Date officeDate = rr.getReceivedDate();
	                if (officeDate == null) continue;

	                double penaltyAmt = parseDoubleSafe(rr.getDuePenalty());
	                double loanAmount = parseDoubleSafe(rr.getLoanAmount());
	                double receivedPenaltyAmt = parseDoubleSafe(rr.getReceivedPenalty());
	                
	                
	             // ✅ Manual charges
	                double bounceCharge   = parseDoubleSafe(rr.getBounceCharge());
	               // System.out.println(bounceCharge);
	                
	                double interestLoan   = parseDoubleSafe(rr.getInterestLoan());
	                //System.out.println(interestLoan);
	                double panelCharge    = parseDoubleSafe(rr.getPanelCharge());
	               // System.out.println(panelCharge);
	                double manualChargeAmount = bounceCharge + interestLoan + panelCharge;
	                System.out.println("manual charge amount is :"+manualChargeAmount);

	             // 🔹 Remark string
	                String remark = "Bounce Charge: " + bounceCharge + 
	                                " | Interest: " + interestLoan + 
	                                " | Panel Charge: " + panelCharge;
	              //  ✅ Insert manual charge only if something > 0
	              
                	System.out.println("mannualCharge oo:"+"NO :"+i+manualChargeAmount +"loanNumber : "+rr.getLoanNumber());

	               
	                if (manualChargeAmount>0) 
	                { 
	                	System.out.println("mannualCharge RR:"+"NO :"+i+manualChargeAmount);
	                    insertManualCharge(rr.getLoanNumber(), "personalloan", officeDate, manualChargeAmount, remark, conn, branchId);
	                 i++;
	                }
	                i=1;

	                // Validate required fields
	                if (rr.getLoanNumber() == null || rr.getLoanNumber().trim().isEmpty()) continue;
	                if (rr.getPaymentMode() == null || rr.getPaymentMode().trim().isEmpty()) {
	                	
	                	continue;
	                	
	                	
	                }
	                
	              
	                // Call insert method
	                int insert = paymentRequest(
	                    branchId,
	                    "admin",
	                    officeDate,
	                    "personalloan",
	                    rr.getLoanNumber(),
	                    String.format("%.2f", loanAmount),
	                    String.format("%.2f", penaltyAmt),
	                    String.format("%.2f", receivedPenaltyAmt),
	                    rr.getPaymentMode(),
	                    "NA", "", "", "",
	                    conn
	                );
	                
	                

	                System.out.println("Inserted rows for loan no " + rr.getLoanNumber() + ": " + insert);

	            } catch (Exception ex) {
	                ex.printStackTrace();
	                FacesContext.getCurrentInstance().addMessage(null,
	                    new FacesMessage(FacesMessage.SEVERITY_ERROR,
	                        "Error processing loan number: " + rr.getLoanNumber(), null));
	            }
	        } // <-- for loop end

	        conn.commit();  // ✅ transaction commit yahan hona chahiye
	        FacesContext.getCurrentInstance().addMessage(null,
	            new FacesMessage(FacesMessage.SEVERITY_INFO, "Data imported successfully!", null));

	    } catch (Exception e) {
	        e.printStackTrace();
	        FacesContext.getCurrentInstance().addMessage(null,
	            new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error importing data", null));
	    }
	} // <-- method close


	 public static int paymentRequest(int branchId, String employee, Date startDate, String loanType, String loanNo,
             String loanAmount, String duePenalty, String receivedPenalty,
             String payMode, String savingType, String transactionId,
             String response, String attendance, Connection conn)
	 {

int j = 0; 
//System.out.println("payment request method hit ");
try {
Timestamp mDate = new Timestamp(startDate.getTime());
mDate.setHours(0); mDate.setMinutes(0); mDate.setSeconds(0); mDate.setNanos(0);

String query = "INSERT INTO loan_payment_from_app(employee, loan_type, loan_no, pay_date, amount, pay_mode, saving_type, transaction_id, response, attendance, branch_id) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
//System.out.println("query is:"+query);

double emi = 0;
try {
	emi = parseDoubleSafe(loanAmount);
	//System.out.println("Trying to insert EMI for loan: " + loanNo + ", amount: " + emi);

} catch (Exception e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}
double penalty = 0;
try
{
	penalty = parseDoubleSafe(receivedPenalty);
} catch (Exception e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}
double receivedPenaltyAmount = 0;
try {
	receivedPenaltyAmount = parseDoubleSafe(receivedPenalty);
} catch (Exception e) { 	
	// TODO Auto-generated catch block
	e.printStackTrace();
}
// EMI insert
PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query);
st1.setString(1, employee);
//System.out.println(employee);
st1.setString(2, loanType);
//System.out.println(loanType);
st1.setString(3, loanNo);
st1.setTimestamp(4, mDate);
st1.setDouble(5, emi);
st1.setString(6, payMode);
st1.setString(7, savingType);
st1.setString(8, transactionId);
st1.setString(9, response);
st1.setString(10, attendance);
st1.setInt(11, branchId);
if (st1.executeUpdate() >= 1) j++;

// Penalty insert
if (receivedPenaltyAmount > 0)
{
PreparedStatement st2 = (PreparedStatement) conn.prepareStatement(query);
st2.setString(1, employee);
st2.setString(2, "penalty");
st2.setString(3, loanNo);
st2.setTimestamp(4, mDate);
st2.setDouble(5, penalty);
st2.setString(6, payMode);
st2.setString(7, savingType);
st2.setString(8, transactionId);
st2.setString(9, response);
st2.setString(10, attendance);
st2.setInt(11, branchId);
if (st2.executeUpdate() >= 1) j++;
}

// Income Table insert for received penalty
if (receivedPenaltyAmount > 0) 
{  Date dd = new Date();
	
	//System.out.println("insert into income table ");
String incomeQuery = "INSERT INTO income_table (income_date, category, payment_mode, amount, chq_dd_no, chq_dd_date, bank_name, bank_branch, description, item, old_amount, transfer_to_bank, chequeStatus, no_of_item, loan_no, loan_type, discount, voucher_no, fa_purchase_id, item_rate, status, entry_date, dsa_code, branch_id) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

PreparedStatement incomeSt = (PreparedStatement) conn.prepareStatement(incomeQuery);
incomeSt.setTimestamp(1, mDate);
incomeSt.setString(2, "Other_Cr");
incomeSt.setString(3, payMode);
incomeSt.setDouble(4, receivedPenaltyAmount);
for (int k = 5; k <= 20; k++) {
incomeSt.setNull(k, java.sql.Types.VARCHAR);
}
incomeSt.setTimestamp(6,mDate);

incomeSt.setString(15, loanNo);
incomeSt.setString(16, loanType);
incomeSt.setNull(21, java.sql.Types.INTEGER);
incomeSt.setDate(22, new java.sql.Date(dd.getTime()));
incomeSt.setNull(23, java.sql.Types.VARCHAR);
incomeSt.setInt(24, branchId);
if (incomeSt.executeUpdate() >= 1) j++;

}

} catch (Exception e) {
e.printStackTrace();
}
return j;
}
	 private void insertManualCharge(String loanNo, String loanType, Date officeDate, double manualChargeAmount, String remark, Connection conn, int branchId) throws SQLException 
	 {
		// System.out.println("manual charge function hitted!");
		    String query = "INSERT INTO manual_charges (loan_no, loan_type, date, amount, remark, entry_date) "
		                 + "VALUES (?, ?, ?, ?, ?, ?)";
		    try (PreparedStatement ps = conn.prepareStatement(query)) {
		        ps.setString(1, loanNo);
		        ps.setString(2, loanType);
		        ps.setDate(3, new java.sql.Date(officeDate.getTime()));
		        ps.setDouble(4, manualChargeAmount);
		        ps.setString(5, remark);
		        ps.setDate(6, new java.sql.Date(new java.util.Date().getTime())); // entry_date = today
		        ps.executeUpdate();
	//	        System.out.println("Inserted manual charge: " + remark + " for loan " + loanNo + " amount " + manualChargeAmount);
		    }
		}

	 private Date parseDateSafe(String dateStr, int rowIndex, String label) {
		    if (dateStr == null || dateStr.trim().isEmpty()) return null;

		    String[] patterns = {
		        "dd-MM-yyyy",
//		        "M/d/yy",
//		        "MM/dd/yy",
//		        "M/d/yyyy",
//		        "MM/dd/yyyy",
//		        "dd/MM/yyyy"
		    };

		    for (String pattern : patterns) {
		        try {
		            return new SimpleDateFormat(pattern).parse(dateStr);
		        } catch (ParseException ignored) {}
		    }

		    System.out.println("❌ Invalid " + label + " format in row " + rowIndex + ": " + dateStr);
		    return null;
		}
		    private static double parseDoubleSafe(Object val)
		    {
			    if (val == null) return 0.0;
			    try {
			        return Double.parseDouble(val.toString().trim());
			    } catch (Exception e) {
			        return 0.0;
			    }
			}
	
	
//	// Comman Excel Code
//	public void importFromExcel() throws IOException, ParseException 
//	{
//		DataBaseMethods dbm=new DataBaseMethods();
//		if (excelFile != null && excelFile.getSize() > 0) 
//		{
//			filePath = excelFile.getFileName();
//			
//			obj.makeProfile(excelFile, filePath); //Offline
//			
//			
//			String arr1[]=filePath.split("\\.");
//			String todayDate=new SimpleDateFormat("dd/MM/yyyy").format(new Date());
//			String day=todayDate.substring(0, 2);
//			String month=todayDate.substring(3, 5);
//			String year=todayDate.substring(6, 10);
//			String image1Path=arr1[0]+day+month+year+"."+arr1[1];
//			//// System.out.println(image1Path);
//		//		obj.makeProfile(excelFile, filePath); 	//Online
//			
//			//filePath="D:\\Book1.xls";
//			
//			ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
//			String realPath = ctx.getRealPath("/");
//			
//			filePath=realPath+filePath;
//			
//			
//			//// System.out.println("Path : "+filePath);
//			
//			
//			//String folderName="/home/wwwrkclsp/public_html/Scan_images/";
//			//filePath=folderName+image1Path;
//			//FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("path: "+filePath));
//		}
//		
//		if(filePath!=null || !filePath.equals(""))
//		{
//			String filename = filePath;
//		//	FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("file name: "+filename));
//			List sheetData = new ArrayList();
//
//			FileInputStream fis = null;
//			try 
//			{
//				fis = new FileInputStream(filename);
//
//				HSSFWorkbook workbook = new HSSFWorkbook(fis);
//				HSSFSheet sheet = workbook.getSheetAt(0);
//				Iterator rows = sheet.rowIterator();
//				while (rows.hasNext()) 
//				{
//					HSSFRow row = (HSSFRow) rows.next();
//					Iterator cells = row.cellIterator();
//
//					List data = new ArrayList();
//					while (cells.hasNext()) 
//					{
//						HSSFCell cell = (HSSFCell) cells.next();
//						data.add(cell);
//					}
//
//					sheetData.add(data);
//				}
//			} 
//			
//			catch (IOException e) 
//			{
//				e.printStackTrace();
//			} 
//			finally 
//			{
//				if (fis != null) 
//          {
//					fis.close();
//			}
//				fis.close();
//			}
//
//			showExelData(sheetData);
//		}
//		else
//		{
//			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error In File Uploading.Please Check!!!"));
//		}
//		
//	}

	/*public ExcelImportBean() 
	{
		transferShareList=new DataBaseMethods().transferShareHolderList();
		System.out.println(transferShareList.size());
	}*/
	
	//Member's OD Report
	/*
	
		private void showExelData(List sheetData) throws ParseException {
		
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		int counter=193;
		// System.out.println("size : "+sheetData.size());
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <7; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					if(j==0)
					{
						info.setColumn1(temp);
					}
					else if(j==1)
					{
						info.setColumn2(temp);
					}
					else if(j==2)
					{
						info.setColumn3(temp);
					}
					else if(j==3)
					{
						info.setColumn4(temp);
					}
					else if(j==4)
					{
						info.setColumn5(temp);
					}
					else if(j==5)
					{
						info.setColumn6(temp);
					}
					else if(j==6)
					{
						info.setColumn7(temp);
					}
					
			}
			stuList.add(info);
			//counter++;
		
		}
			insertData(conn, stuList);
		}
	
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		int i=0;
		for(UserInfo info : list)
		{
			
			i=insertStudent(info.getColumn1(),info.getColumn2(),info.getColumn3(),info.getColumn4(),info.getColumn5(),info.getColumn6(),info.getColumn7());
		}
		
		if(i>=1)
		{
			// System.out.println("if");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Upload sucessfully"));
		}
		
		else
		{
			// System.out.println("else");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error Occured!!!"));
		}
		stuList=new ArrayList<>();
	}
	
	private int insertStudent(String shareHolderNo, String loan, String share, String saving, String rd, String fd, String dds) throws ParseException 
	{
		String fy="2016-2017";
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="insert into members_od(share_holder_no, loan_amount, share_amount, sv_amount, rd_amount, fd_amount, dds_amount, financial_year) "
					+ "values('"+shareHolderNo+"','"+loan+"','"+share+"','"+saving+"','"+rd+"','"+fd+"','"+dds+"','"+fy+"') ";
			// System.out.println(qq);
			st.executeUpdate(qq);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}
	*/
	
	
	
	//For Saving Account
	/*
	
		private void showExelData(List sheetData) throws ParseException {
		
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		int counter=193;
		// System.out.println("size : "+sheetData.size());
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <=2; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					
					if(j==0)
					{
						info.setOffice_application_no(temp);
					}
					else if(j==1)
					{
						info.setStrOffcDate(temp);
					}
					else if(j==2)
					{
						info.setClosingBalance(temp);
					}	
			}
			stuList.add(info);
			//counter++;
		
		}
			insertData(conn, stuList);
		}
	
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		int i=0;
		for(UserInfo info : list)
		{
			
			i=insertStudent(info.getOffice_application_no(),info.getStrOffcDate(),info.getClosingBalance());
		}
		
		if(i>=1)
		{
			// System.out.println("if");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Upload sucessfully"));
		}
		
		else
		{
			// System.out.println("else");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error Occured!!!"));
		}
		stuList=new ArrayList<>();
	}
	
	private int insertStudent(String accountno,String officeDt, String closingbalance) throws ParseException 
	{
		// System.out.println("ttttttttttttttttttt"+accountno);
		String shno="NRM-0"+accountno.substring(0, 5);
		// System.out.println(shno);
		// System.out.println(closingbalance);
		String accountNo=obj.savingAccountNo(); 
		Date opendate=new SimpleDateFormat("dd/MM/yyyy").parse(officeDt);
		Date date1=new SimpleDateFormat("dd/MM/yyyy").parse("31/03/2017");
		String user_no=obj.usrno(accountno);
		Double closingamnt= Double.valueOf(closingbalance);
	//	Date date2=new SimpleDateFormat("dd/MM/yyyy").parse(date);
	//	accountNo=obj.savingAccountNo();
		if(closingamnt>0)
		{
			  int i=obj.addSavingAcount(shno,accountNo,opendate,closingamnt,4.0,100,0,0);
			  
			return 1;
		}
		else
		{
			return 0;
		}
	}*/
	
	//RD/FD Payment Receipt
	/*
	
		private void showExelData(List sheetData) throws ParseException {
		
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		int counter=193;
		// System.out.println("size : "+sheetData.size());
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <2; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					
					if(j==0)
					{
						info.setOffice_application_no(temp);
					}
					else if(j==1)
					{
						info.setClosingBalance(temp);
					}
					
			}
			stuList.add(info);
			//counter++;
		
		}
			insertData(conn, stuList);
		}
	
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		int i=0;
		for(UserInfo info : list)
		{
			
			i=insertStudent(info.getOffice_application_no(),info.getClosingBalance());
		}
		
		if(i>=1)
		{
			// System.out.println("if");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Upload sucessfully"));
		}
		
		else
		{
			// System.out.println("else");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error Occured!!!"));
		}
		stuList=new ArrayList<>();
	}
	
	private int insertStudent(String accountno, String closingbalance) throws ParseException 
	{
		// System.out.println("ttttttttttttttttttt"+accountno);
		String shno="NRM-0"+accountno.substring(0, 5);
		// System.out.println(shno);
		// System.out.println(closingbalance);
		String accountNo = null;
		Date date1=new SimpleDateFormat("dd/MM/yyyy").parse("31/03/2017");
		String user_no=obj.usrno(accountno);
		Double closingamnt= Double.valueOf(closingbalance);
	//	Date date2=new SimpleDateFormat("dd/MM/yyyy").parse(date);
	//	accountNo=obj.savingAccountNo();
		if(closingamnt>0)
		{
		int	n = obj.paymentReceipt(user_no, closingamnt, date1,"cash", "", null, "",0.00,"","");
	
			return 0;
		}
		else
		{
			return 0;
		}
	}*/
	
	//For RD/FD Reg.
	/*
	
		private void showExelData(List sheetData) throws ParseException {
		
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		// System.out.println("size : "+sheetData.size());
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <=9; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					if(j==0)
					{
						info.setShareHolderNo(temp);
					}
					else if(j==1)
					{
						info.setUser_no(temp);
					}
					else if(j==2)
					{
						info.setType(temp);
					}
					else if(j==3)
					{
						info.setStrdob(temp);
					}
					else if(j==4)
					{
						info.setAmount(Double.parseDouble(temp));
					}
					else if(j==5)
					{
						info.setInterest(Double.parseDouble(temp));
					}
					else if(j==6)
					{
						info.setTerms(Integer.parseInt(temp));
					}
					else if(j==7)
					{
						info.setStrMatDate(temp);
					}
					else if(j==8)
					{
						info.setMaturity_value(Double.parseDouble(temp));
					}
					else if(j==9)
					{
						info.setPaidAmount(Double.parseDouble(temp));
					}
			}
			stuList.add(info);
			//counter++;
		
		}
			insertData(conn, stuList);
		}
	
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		int i=0;
		for(UserInfo info : list)
		{
			
			i=insertStudent(info.getShareHolderNo(), info.getUser_no(), info.getType(), info.getStrdob(), info.getAmount(), info.getInterest(), info.getTerms(), 
					info.getStrMatDate(), info.getMaturity_value(), info.getPaidAmount());
		}
		
		if(i>=1)
		{
			// System.out.println("if");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Upload sucessfully"));
		}
		
		else
		{
			// System.out.println("else");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error Occured!!!"));
		}
		stuList=new ArrayList<>();
	}
	
	private int insertStudent(String shareNo, String userNo, String depType, String strdob, double amount, double interest, int terms, String strmat, 
			double matValue, double paidAmount) throws ParseException 
	{
		//String officeDt=new SimpleDateFormat("yyyy-MM-dd").format(date1);
		String rdno=shareNo.substring(shareNo.length() - 3);
		String shno=shareNo.substring(0, shareNo.length()- 3);
		if(shno.length()==2)
		{
			shareNo="000"+shno;
		}
		if(shno.length()==3)
		{
			shareNo="00"+shno;
		}
		if(shno.length()==4)
		{
			shareNo="0"+shno;
		}
		if(shno.length()==5)
		{
			shareNo=shno;
		}
		
		userNo="14"+shareNo+rdno;
		
		System.out.println(shareNo+"=="+userNo);
		int branchId=obj.branchIdByMemberNo(shareNo);
		String dsaCode=obj.dsaCodeByMemberNo(shareNo);
		Date office_date=new SimpleDateFormat("dd/MM/yyyy").parse(strdob);
		Date maturity_date=new SimpleDateFormat("dd/MM/yyyy").parse(strmat);
		
		// System.out.println(userNo+" : "+matValue);
		obj.registerUser(branchId,userNo,office_date,0,userNo,depType,"1",terms,
				0,amount,interest,matValue,maturity_date,shareNo,12,dsaCode,0);
		
		if(paidAmount>0)
		{
			String receipt_no=String.valueOf(obj.receiptNo());
			obj.paymentReceipt(branchId,userNo, paidAmount, office_date,"Cash", "", office_date, "",0,receipt_no,"N/A",
					depType, "",dsaCode, 0);
		}
		
			return 0;
			
			
	}*/
	
	
	//For Loan
	/*
	
		private void showExelData(List sheetData) throws ParseException {
		
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		int counter=193;
		// System.out.println("size : "+sheetData.size());
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <=9; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					
					if(j==0)
					{
						info.setShareHolderNo(temp);
					}
					else if(j==1)
					{
						info.setLoanNo(temp);
					}
					else if(j==2)
					{
						info.setStrdob(temp);
					}
					else if(j==3)
					{
						info.setLoanAmount(Integer.parseInt(temp));
					}
					else if(j==4)
					{
						info.setRate(Double.parseDouble(temp));
					}
					else if(j==5)
					{
						info.setTerms(Integer.parseInt(temp));
					}
					else if(j==6)
					{
						info.setFrequency(Integer.parseInt(temp));
					}
					else if(j==7)
					{
						info.setAmount(Double.parseDouble(temp));
					}
					else if(j==8)
					{
						info.setStrDate(temp);
					}
					else if(j==9)
					{
						info.setBranch(temp);
					}
					
					
			}
			stuList.add(info);
			//counter++;
		}
		
			insertData(conn, stuList);
		}
	
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		int i=0;
		for(UserInfo info : list)
		{
			String arr[]=null;
			date1=new SimpleDateFormat("dd-MM-yyyy").parse(info.getStrdob());
			Date date2=new SimpleDateFormat("dd-MM-yyyy").parse(info.getStrDate());
			branchId=obj.branchIdByBranchName(info.getBranch(), conn);
//			if(info.getColumn1().equalsIgnoreCase("PPL"))
//			{
//				obj.addSecureLoanRequest(branchId, info.getShareHolderNo(), "", "", info.getLoanNo(), arr, "0", info.getLoanAmount(), info.getRate(), 
//						Double.valueOf(info.getTerms()),info.getFrequency(),
//						Math.round(info.getAmount()), date1, "", "", 0, "reducing", "", "", "", "", "", "", "",
//						"", "", "", "", "", "", 0.0, "0", "", "1", "both", 0.0,"8");
//				
//				obj.updateStatusForSecureLoanByLoanNo(info.getLoanNo(), "Approve", date1, date2, conn);
//			}
//			else
//			{
				obj.addLoanRequest(branchId, info.getShareHolderNo(), "", "", info.getLoanNo(), arr, 0.0, info.getLoanAmount(), info.getRate(), 
						Double.valueOf(info.getTerms()),info.getFrequency(),
					Math.round(info.getAmount()), date1, "", "", 0, "flat", "", "", "", "", "", "", "",
						"", "", "", "", "", "", 0.0, 0.0, "", "1", "both", 0.0);
				
				obj.updateStatusForPersonalLoanByLoanNo(info.getLoanNo(), "Approve", date1, date2, conn);
			//}
			
			//insertFileChargeInIncomeTable(conn, info);
		}
		
		if(i>=1)
		{
			// System.out.println("if");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Upload sucessfully"));
		}
		
		else
		{
			// System.out.println("else");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error Occured!!!"));
		}
		stuList=new ArrayList<>();
	}
	
	private void insertFileChargeInIncomeTable(Connection conn, UserInfo info) 
	{
		
		String voucherNo=obj.generateVoucherNo(branchId,"income_table",conn);
		new DataBaseIncomeExpense().addIncome(branchId,date1,"Other_Cr","Cash",String.valueOf(3540),"","",
				"",date1,"File Charge Receive","File Charge",0,"N/A",
				"0",0,info.getLoanNo(),info.getColumn1(), voucherNo,"",0, "pending",""); 
	}*/
	
	
	//for share holder Registarion
	/*
	
		private void showExelData(List sheetData) throws ParseException {
		
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		//
		int counter=193;
		// System.out.println("size : "+sheetData.size());
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <=15; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					if(j==0)
					{
						info.setShareHolderNo(temp);
					}
					else if(j==1)
					{
						info.setName(temp);
					}
					else if(j==2)
					{
						info.setAddress(temp);
					}
					else if(j==3)
					{
						info.setStrdob(temp);
					}
					else if(j==4)
					{
						info.setNoOfShare(Double.parseDouble(temp));
					}
					else if(j==5)
					{
						info.setNominee_name(temp);
					}
					else if(j==6)
					{
						info.setStrOffcDate(temp);
					}
					else if(j==7)
					{
						info.setFather_husband_name(temp);
					}
					else if(j==8)
					{
						info.setNominee_relation(temp);
					}
					else if(j==9)
					{
						info.setColorStatus(temp);  //applicant no
					}
					else if(j==10)
					{
						info.setMobile(temp);  //mOBILE no
					}
					else if(j==11)
					{
						info.setBranch(temp);  //Branch Name
					}
					else if(j==12)
					{
						info.setVoterId(temp);
					}
					else if(j==13)
					{
						info.setUniqueId(temp);
					}
					else if(j==14)
					{
						info.setPAN(temp);
					}
					else if(j==15)
					{
						info.setGender(temp);
					}
					
					
			}
			stuList.add(info);
			//counter++;
		}
		
			insertData(conn, stuList);
		}
	
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		
		int i=0;
		for(UserInfo info : list)
		{
			
			i=insertStudent(info.getShareHolderNo(), info.getName(), info.getAddress(), info.getStrdob(), info.getNoOfShare(), info.getNominee_name(), info.getStrOffcDate(),
					info.getFather_husband_name(), info.getNominee_relation(), info.getColorStatus(), info.getMobile(), info.getPAN(), info.getUniqueId(), info.getBranch(),
					info.getDsaCode(), info.getGender(), info.getCategory(), info.getVoterId());
		}
		
		if(i>=1)
		{
			// System.out.println("if");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Upload sucessfully"));
		}
		
		else
		{
			// System.out.println("else");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error Occured!!!"));
		}
		stuList=new ArrayList<>();
	}
	
	private int insertStudent(String shareHolederNo, String name, String address, String strdob, double noOfShare, String nomineeName, String strOfficeSt, 
			String fatherName, String relation, String applicantNo, String mobileNo, String panNo, String uniqueId, String branch, String dsaCode,
			String gender, String category, String voterId) throws ParseException 
	{
		 
		
		/// Branch code static code for CCS
		
		
		Connection conn=DataBaseConnection.JAVAConnection();
		System.out.println(strOfficeSt);
		date1=new SimpleDateFormat("dd-MM-yyyy").parse(strOfficeSt);
		//String officeDt=new SimpleDateFormat("yyyy-MM-dd").format(date1);

		Date date2=new SimpleDateFormat("dd-MM-yyyy").parse(strdob);
	//	String dobDt=new SimpleDateFormat("yyyy-MM-dd").format(date2);
		int i=0;
		double totalAmount=noOfShare*10;
	//	transferShareHolderNo="BGNL-BN000005";
		double shareAmount=obj.extractCompanyShareAmount(conn); 
		//branchId=obj.branchIdByBranchName(branch, conn);
		if(branch.equalsIgnoreCase("BADI"))
		{
			branchId=3;
		}
		else if(branch.equalsIgnoreCase("SEHORE"))
		{
			branchId=4;
		}
		else
		{
			branchId=2;
		}
		//shareHolederNo=obj.shareHolderNo(branchId);
		applicantNo=shareHolederNo;
		int id=obj.addNewMember(branchId,applicantNo,date1,shareHolederNo,name,date2,
				fatherName,"","","",panNo,"","","",address,mobileNo,
				nomineeName,relation,date2,"","",noOfShare,shareAmount,totalAmount,
				address,"","",category,"",voterId,uniqueId,"", "","",
				"", "", "", "N/A", "", "Cash","", "", "", "", "","","",0,conn,"","","","","",gender,"","","","",0,"","","",dsaCode, category, category, category, category, category, category, category, category);
				if(id>=1)
				{
					FacesContext fc=FacesContext.getCurrentInstance();
					fc.addMessage(null, new FacesMessage("Share Holder Registered Successfully"));
					
					String crNo=obj.shareHolderCertificationNo(noOfShare,id,"shareholder_info");
					obj.insertCertificationNoOfShareHolder(crNo,id);
					boolean shareCheck=obj.checkShareAllotmentFromMember();
//					if(shareCheck==true)
//					{
//						int transferId=obj.obtainIdOfShareHolder(transferShareHolderNo);
//						obj.updateCertificationNoOfTransferShareholder(transferId,noOfShare);
//						String crNo=obj.certificationNoOfNewShareHolderNoFromTransfer(transferId,noOfShare);
//						
//						obj.insertCertificationNoOfShareHolder(crNo,id);
//					}
					
					
			
				   autoSavingAccountGenerate(shareHolederNo);
				   
//				   boolean checkAutoTransfer=obj.checkShareAmountFromMember();
//				   if(checkAutoTransfer==true)
//				   {
//					   autoEntrySaving(shareHolederNo, totalAmount, conn);
//				   }
				}
			return 0;
	}
	
	private void autoEntrySaving(String shareHolederNo, double totalAmount, Connection conn)
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		 int branchId = (int) ss.getAttribute("branchId");
		 //String transferShareNo=transferShareHolderNo.substring(0, transferShareHolderNo.indexOf("/"));
		 String accno=obj.savingAccountByShareHolderNo(transferShareHolderNo);
		 int	n = obj.userDeposit(branchId,accno, String.valueOf(totalAmount), date1,"Cash", "", null, "","approved",
				 "N/A","Share Amount From "+shareHolederNo, "");
		 if(n>0)
		 {
			 double preAmount=obj.acconuntHolderAmountByAccountNo(accno);
			 Double newAmount=preAmount+totalAmount;
			 obj.updateSavingAmountByAccountNo(accno,newAmount);
			 obj.increaseCompanyCapitalAmount(branchId,totalAmount,conn);
		 }
	}
	
	private void autoSavingAccountGenerate(String shareHolederNo)
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		 int branchId = (int) ss.getAttribute("branchId");
		String accountNo="15"+shareHolederNo+"001"; //obj.savingAccountNo(branchId); 
		//System.out.println("saving Methid"+accountNo);
		int i=obj.addSavingAcount(branchId,shareHolederNo,accountNo,date1,0,4,0,0,0,"");
		   if(i>=1)
		   {
			   RequestContext.getCurrentInstance().showMessageInDialog(new FacesMessage(FacesMessage.SEVERITY_INFO, "Saving Account Detail", "Saving Account No. "+accountNo+" Auto-Generate For the Member No. "+shareHolederNo));
		   }
	}
*/

	// For Unique ID update
	/*
		private void showExelData(List sheetData) throws ParseException {
		
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		int counter=193;
		// System.out.println("size : "+sheetData.size());
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <=1; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					if(j==0)
					{
						info.setShareHolderNo(temp);
					}
					else if(j==1)
					{
						info.setUniqueId(temp);
					}
					
			}
			stuList.add(info);
			//counter++;
		}
		
			insertData(conn, stuList);
		}
	
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		int i=0;
		for(UserInfo info : list)
		{
			
			i=insertStudent(info.getShareHolderNo(), info.getUniqueId());
		}
		
		if(i>=1)
		{
			// System.out.println("if");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Upload sucessfully"));
		}
		
		else
		{
			// System.out.println("else");
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error Occured!!!"));
		}
		stuList=new ArrayList<>();
	}
	
	private int insertStudent(String shareHolederNo, String uniqueId) throws ParseException 
	{
		Connection conn=DataBaseConnection.JAVAConnection();
		
		try
		{
			Statement st=conn.createStatement();
			String qq="update shareholder_info set unique_id='"+uniqueId+"' where shareholderno='"+shareHolederNo+"' ";
			st.executeUpdate(qq);
			System.out.println(qq);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}*/
	
	// Loan Disburse
	/*private void showExelData(List sheetData) throws ParseException {
	
	Connection conn=DataBaseConnection.JAVAConnection();
	//ExcelRead read=new ExcelRead();
	ExcelImportBean obj=new ExcelImportBean();
	
	
	int counter=193;
	// System.out.println("size : "+sheetData.size());
	for (int i = 1; i < sheetData.size(); i++) {
		List list = (List) sheetData.get(i);
		
		UserInfo info=new UserInfo();
		
		String temp="";
		for (int j = 0; j <=2; j++) {
			Cell cell = (Cell) list.get(j);
			// System.out.println("j: "+list.size());
			if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
				
				DataFormatter fmt = new DataFormatter();

				String valueAsSeenInExcel = fmt.formatCellValue(cell);
				temp=valueAsSeenInExcel;
			
			} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
				
				temp=String.valueOf(cell.getRichStringCellValue());

			} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
				
				temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
			}
			else
			{
				temp="";
			}
				
				
			if(j==0)
			{
				info.setColumn1(temp);;
			}
			else if(j==1)
			{
				info.setColumn2(temp);
			}
			else if(j==2)
			{
				info.setColumn3(temp);
			}
		}
		stuList.add(info);
		//counter++;
	}
	
		insertData(conn, stuList);
	}
	
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		for(UserInfo info : list)
		{
			if(info.getColumn2().contains("Accountno"))
			{
				String a[]=info.getColumn2().split("Accountno :");
				
				if(info.getColumn3().contains("12:00:00 AM"))
				{
					date1=new SimpleDateFormat("MM/dd/yyyy").parse(info.getColumn3());
				}
				else
				{
					date1=new SimpleDateFormat("dd/MM/yyyy").parse(info.getColumn3());
				}
			
				//String memberNo=obj.shareHolderNoByLoanNo(a[1]);
				//branchId=obj.branchIdByMemberNo(memberNo);
				//obj.addDebitLoan(branchId,memberNo,"personalloan",a[1],Double.parseDouble(info.getColumn1()),"Cheque","1","","",date1,date1);
				
				//date update in debit_loan Table
				
				try
				{
					Statement st=conn.createStatement();
					String dt=sdfForDb.format(date1);
					String qq="update debit_loan set date='"+dt+"' where loanNo='"+a[1]+"'  ";
					System.out.println(qq);
					st.executeUpdate(qq);
				}
				catch(Exception e)
				{
					e.printStackTrace();
				}
			}
		}
		
		stuList=new ArrayList<>();
	}*/
	
	// First emi Date Change method
	/*
		private void showExelData(List sheetData) throws ParseException {
		
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		int counter=193;
		// System.out.println("size : "+sheetData.size());
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <=1; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					
				if(j==0)
				{
					info.setColumn1(temp);;
				}
				else if(j==1)
				{
					info.setColumn2(temp);
				}
			}
			stuList.add(info);
			//counter++;
		}
		
			insertData(conn, stuList);
		}
	
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		for(UserInfo info : list)
		{
			date1=new SimpleDateFormat("dd-MM-yyyy").parse(info.getColumn2());
			date1.setMonth(date1.getMonth()+1);
			obj.updateFirstEMIDate("personalloan", info.getColumn1(),date1, conn);
		}
		
		stuList=new ArrayList<>();
	}*/
	
	
	//Loan emi Received Personal loan
	/*private void showExelData(List sheetData) throws ParseException {
	
	Connection conn=DataBaseConnection.JAVAConnection();
	//ExcelRead read=new ExcelRead();
	ExcelImportBean obj=new ExcelImportBean();
	
	
	for (int i = 1; i < sheetData.size(); i++) {
		List list = (List) sheetData.get(i);
		
		UserInfo info=new UserInfo();
		
		String temp="";
		for (int j = 0; j <=7; j++) {
			Cell cell = (Cell) list.get(j);
			// System.out.println("j: "+list.size());
			if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
				
				DataFormatter fmt = new DataFormatter();

				String valueAsSeenInExcel = fmt.formatCellValue(cell);
				temp=valueAsSeenInExcel;
			
			} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
				
				temp=String.valueOf(cell.getRichStringCellValue());

			} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
				
				temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
			}
			else
			{
				temp="";
			}
				
				
			if(j==0)
			{
				info.setLoanNo(temp);;
			}
			else if(j==1)
			{
				info.setStrdob(temp);
			}
			else if(j==2)
			{
				info.setDsaCode(temp);
			}
			else if(j==3)
			{
				info.setAmount(Math.round(Double.parseDouble(temp)));
			}
			else if(j==4)
			{
				info.setPenalty(Math.round(Double.parseDouble(temp)));
			}
			
			else if(j==5)
			{
				info.setPayMode(temp);
			}
			
			else if(j==6)
			{
				info.setChq_ddNo(temp);
			}
			
			else if(j==7)
			{
				info.setBranch(temp);
			}
		}
		stuList.add(info);
		//counter++;
	}
	
		insertData(conn, stuList);
	}
	
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		for(UserInfo info : list)
		{
			date1=new SimpleDateFormat("dd-MM-yyyy").parse(info.getStrdob());
			//obj.updateFirstEMIDate("personalloan", info.getColumn1(),date1, conn);
			branchId=new DataBaseMethods().branchIdByBranchName(info.getBranch(), conn);
			new DatabaseMethods1().paymentRequest(branchId,info.getDsaCode(), date1, "personalloan", info.getLoanNo(), String.valueOf(info.getAmount()),
					info.getPayMode(),"NA", "", info.getChq_ddNo(),"");
			
			if(info.getPenalty()>0)
			{
				 new DatabaseMethods1().paymentRequest(branchId,info.getDsaCode(), date1, "penalty", info.getLoanNo(), String.valueOf(info.getPenalty()), info.getPayMode()
						 ,"NA", "", "","");
			}
		}
		
		stuList=new ArrayList<>();
	}
	*/
	
	// Loan Close Method
	/*private void showExelData(List sheetData) throws ParseException 
	{
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <=2; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					
				if(j==0)
				{
					info.setLoanNo(temp);;
				}
				else if(j==1)
				{
					info.setAmount(Math.round(Double.parseDouble(temp)));
				}
				else if(j==2)
				{
					info.setStrdob(temp);
				}
			}
			stuList.add(info);
			//counter++;
		}
		
			insertData(conn, stuList);
		}
		
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		for(UserInfo info : list)
		{
//			date1=new SimpleDateFormat("dd-MM-yyyy").parse(info.getStrdob());
//			branchId=obj.branchIdByMemberNo(obj.shareHolderNoByLoanNo(info.getLoanNo()));
//			
//			String receipt_no=String.valueOf(obj.loanPayReceiptNo());
//			obj.addLoanCloser(receipt_no, 0, 0, 0,info.getAmount(),0,info.getAmount(),
//					date1,"cash","",info.getLoanNo(),0,0,0,"",date1,0,
//					"",branchId,0);
//			
//			obj.payLoanEMI(branchId,info.getLoanNo(),"personalloan",info.getAmount(),date1,"cash","",date1,"",0,receipt_no,"N/A","No",
//					0,-1,"","","");
//			
//			obj.updateStatusOfLoan("personalloan",info.getLoanNo());
			
			// date format change
			if(info.getStrdob().contains("12:00:00 AM"))
			{
				date1=new SimpleDateFormat("MM/dd/yyyy").parse(info.getStrdob());
			}
			else
			{
				date1=new SimpleDateFormat("dd/MM/yyyy").parse(info.getStrdob());
			}
			
			try
			{
				Statement st=conn.createStatement();
				String dt=sdfForDb.format(date1);
				String qq="update loanpayment set pay_date='"+dt+"' where loanNo='"+info.getLoanNo()+"' and emi_no='-1'  ";
				System.out.println(qq);
				st.executeUpdate(qq);
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		
		stuList=new ArrayList<>();
	}*/
	
	
	// Promotor Accounts Capital Add
	/*private void showExelData(List sheetData) throws ParseException 
	{
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <=6; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					
				if(j==0)
				{
					info.setName(temp);
				}
				else if(j==1)
				{
					info.setAmount(Math.round(Double.parseDouble(temp)));
				}
				else if(j==2)
				{
					info.setStrdob(temp);
				}
				else if(j==3)
				{
					info.setRemark(temp);
				}
				else if(j==4)
				{
					info.setPayMode(temp);
				}
				else if(j==5)
				{
					info.setChq_ddNo(temp);
				}
				else if(j==6)
				{
					info.setBankName(temp);
				}
			}
			stuList.add(info);
			//counter++;
		}
		
			insertData(conn, stuList);
		}
		
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		for(UserInfo info : list)
		{
			date1=new SimpleDateFormat("dd-MM-yyyy").parse(info.getStrdob());
			String pid=new DataBaseMethods().promotorsIdByName(info.getName());
			String bank="";
			if(info.getPayMode().equalsIgnoreCase("CASH"))
			{
				bank="N/A";
			}
			else
			{
				bank="1";
			}
			
			if(info.getRemark().equalsIgnoreCase("BY ONLINE"))
			{
				new DataBaseIncomeExpense().addPromotorCapital(2,date1,"Promotor Capital",info.getPayMode(),info.getAmount(),info.getChq_ddNo(),"",
						info.getBankName(),date1,info.getRemark(),bank, pid,"deposit",conn); 
			}
			else
			{
				new DataBaseIncomeExpense().addPromotorCapital(2,date1,"Promotor Capital",info.getPayMode(),info.getAmount(),info.getChq_ddNo(),"",
						info.getBankName(),date1,info.getRemark(),bank, pid,"interest",conn); 
			}
		}
		
		stuList=new ArrayList<>();
	}*/
	
	
	// Member Father name and Guarantor Update
	/*private void showExelData(List sheetData) throws ParseException 
	{
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <=3; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					
				if(j==0)
				{
					info.setLoanNo(temp);
				}
				else if(j==1)
				{
					info.setShareHolderNo(temp);
				}
				else if(j==2)
				{
					info.setFather_husband_name(temp);
				}
				else if(j==3)
				{
					info.setColumn1(temp);
				}
			}
			stuList.add(info);
			//counter++;
		}
		
			insertData(conn, stuList);
		}
		
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		for(UserInfo info : list)
		{
			try
			{
				Statement st=conn.createStatement();
				String qq="update shareholder_info set father_husband_name='"+info.getFather_husband_name()+"' where shareholderno='"+info.getShareHolderNo()+"' ";
				st.executeUpdate(qq);
				System.out.println(qq);
				
				String g1="";
				if(info.getColumn1().contains("/"))
				{
					String arr[]=info.getColumn1().split("/");
					g1=arr[0];
				}
					
				Statement st1=conn.createStatement();
				String qq1="update personalloan set firstguarantor='"+g1+"' where loanNo='"+info.getLoanNo()+"' ";
				st1.executeUpdate(qq1);
				System.out.println(qq1);
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		
		stuList=new ArrayList<>();
	}*/
	
	// INCOME & EXPENSE
	/*private void showExelData(List sheetData) throws ParseException 
	{
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <=6; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					
				if(j==0)
				{
					info.setEntryDateStr(temp);
				}
				else if(j==1)
				{
					info.setPayMode(temp);
				}
				else if(j==2)
				{
					info.setAccountNo(temp);
				}
				else if(j==3)
				{
					info.setChq_ddNo(temp);
				}
				else if(j==4)
				{
					info.setName(temp);
				}
				else if(j==5)
				{
					info.setRemark(temp);
				}
				else if(j==6)
				{
					info.setBranch(temp);
				}
			}
			stuList.add(info);
			//counter++;
		}
		
			insertData(conn, stuList);
		}
		
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		DataBaseIncomeExpense dbie=new DataBaseIncomeExpense();
		for(UserInfo ff : list)
		{
			int branchId=obj.branchIdByBranchName(ff.getBranch(), conn);
			date1=new SimpleDateFormat("dd-MM-yyyy").parse(ff.getEntryDateStr());
			String bankName="N/A";
			if(!ff.getPayMode().equalsIgnoreCase("CASH"))
			{
				bankName="1";
			}
			
			
			
			// income table 
//			boolean duplicate=dbie.categoryDuplicacy(ff.getName(),"Income",conn);
//			if(duplicate==false)
//			{
//				dbie.addCategory(ff.getName(),"Income",conn);
//			}
//			String voucherNo=obj.generateVoucherNo(branchId,"income_table",conn);
//			dbie.addIncome(branchId,date1,"Other_Cr",ff.getPayMode(),ff.getAccountNo(),ff.getChq_ddNo(),"",
//					"",date1,ff.getRemark(),ff.getName(),0,bankName,
//					"0",0,"","", voucherNo,"",0, "active",""); 
			
			
			// // expense table
			boolean duplicate=dbie.categoryDuplicacy(ff.getName(),"Expense",conn);
			if(duplicate==false)
			{
				dbie.addCategory(ff.getName(),"Expense",conn);
			}
			String voucherNo=obj.generateVoucherNo(branchId,"expense_table",conn);
			dbie.addExpense(branchId,date1,"Other_Dr",ff.getPayMode(),ff.getAccountNo(),ff.getChq_ddNo(),"",bankName,date1,
					ff.getRemark(),ff.getName(), "", voucherNo, 0,"0","",""); 
			
		}
		
		stuList=new ArrayList<>();
	}*/

	// Member Reg Date Update
	/*private void showExelData(List sheetData) throws ParseException 
	{
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <=1; j++) {
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					
				if(j==0)
				{
					info.setShareHolderNo(temp);
				}
				else if(j==1)
				{
					info.setStrdob(temp);
				}
			}
			stuList.add(info);
			//counter++;
		}
		
			insertData(conn, stuList);
		}
		
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		for(UserInfo info : list)
		{
			try
			{
				Statement st=conn.createStatement();
				String qq="update shareholder_info set office_date='"+info.getStrdob()+"' where shareholderno='"+info.getShareHolderNo()+"' ";
				st.executeUpdate(qq);
				System.out.println(qq);
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		
		stuList=new ArrayList<>();
	}*/
	
//	// CASH DEPOSIT TO BANK
//	private void showExelData(List sheetData) throws ParseException 
//		{
//		Connection conn=DataBaseConnection.JAVAConnection();
//		//ExcelRead read=new ExcelRead();
//		ExcelImportBean obj=new ExcelImportBean();
//		
//		
//		for (int i = 1; i < sheetData.size(); i++) {
//			List list = (List) sheetData.get(i);
//			
//			UserInfo info=new UserInfo();
//			
//			String temp="";
//			for (int j = 0; j <=3; j++) {
//				Cell cell = (Cell) list.get(j);
//				// System.out.println("j: "+list.size());
//				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
//					
//					DataFormatter fmt = new DataFormatter();
//
//					String valueAsSeenInExcel = fmt.formatCellValue(cell);
//					temp=valueAsSeenInExcel;
//				
//				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
//					
//					temp=String.valueOf(cell.getRichStringCellValue());
//
//				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
//					
//					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
//				}
//				else
//				{
//					temp="";
//				}
//					
//					
//				if(j==0)
//				{
//					info.setEntryDateStr(temp);
//				}
//				else if(j==1)
//				{
//					info.setAccountNo(temp);
//				}
//				
//				else if(j==2)
//				{
//					info.setRemark(temp);
//				}
//				else if(j==3)
//				{
//					info.setBranch(temp);
//				}
//			}
//			stuList.add(info);
//			//counter++;
//		}
//		
//			insertData(conn, stuList);
//		}
//		
//	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
//	{
//		DataBaseIncomeExpense dbie=new DataBaseIncomeExpense();
//		for(UserInfo ff : list)
//		{
//			int branchId=obj.branchIdByBranchName(ff.getBranch(), conn);
//			date1=new SimpleDateFormat("dd-MM-yyyy").parse(ff.getEntryDateStr());
//			
//			dbie.addCurrentAssetBankDebit(branchId,"1", "", "8726005700000014", "DHOLPUR", Double.parseDouble(ff.getAccountNo()),
//					date1,ff.getRemark(),"Cash","",date1,"N/A","deposited",conn);
//			
//		}
//		
//		stuList=new ArrayList<>();
//	}
//		
	public String getFilePath() {
		return filePath;
	}

	public void setFilePath(String filePath) {
		this.filePath = filePath;
	}

	public UploadedFile getExcelFile() {
		return excelFile;
	}

	public void setExcelFile(UploadedFile excelFile) {
		this.excelFile = excelFile;
	}

	public int getCount() {
		return count;
	}

	public void setCount(int count) {
		this.count = count;
	}

	public String getTransferShareHolderNo() {
		return transferShareHolderNo;
	}

	public void setTransferShareHolderNo(String transferShareHolderNo) {
		this.transferShareHolderNo = transferShareHolderNo;
	}
//
	public ArrayList<SelectItem> getTransferShareList() {
		return transferShareList;
	}

	public void setTransferShareList(ArrayList<SelectItem> transferShareList) {
		this.transferShareList = transferShareList;
	}
//
//	


	public ArrayList<ExcelSheetInfo> getStuList() {
		return stuList;
	}


	public void setStuList(ArrayList<ExcelSheetInfo> stuList) {
		this.stuList = stuList;
	}


	public int getBranchId() {
		return branchId;
	}


	public void setBranchId(int branchId) {
		this.branchId = branchId;
	}


	public Date getDate1() {
		return date1;
	}


	public void setDate1(Date date1) {
		this.date1 = date1;
	}

	public ArrayList<ExcelSheetInfo> getDbViewList() {
		return dbViewList;
	}

	public void setDbViewList(ArrayList<ExcelSheetInfo> dbViewList) {
		this.dbViewList = dbViewList;
	}



	
}
