package main_package;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.servlet.ServletContext;

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.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.primefaces.model.UploadedFile;

@ManagedBean(name = "umrn")
@ViewScoped
public class UpdateUmrnBean implements Serializable {
	
	
	  String filePath,filePath2, branchId;
	    UploadedFile excelFile , excelFile2;
	    DataBaseMethods dbm = new DataBaseMethods();
	    ArrayList<NachExcelInfo> stuList = new ArrayList<>();

	private static final long serialVersionUID = 1L;

	public String updateUmrn() throws ParseException, IOException {
		
		   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 "updateUmrn.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);
            NachExcelInfo info = new NachExcelInfo();
            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: info.setLoanNo(temp); 
                
                break;
                case 1: info.setUmrn(temp); 
              
                break;
              

                }
                stuList.add(info);
            }
           
        }
        
       updateData(stuList);
    }
    
    
    public void updateData(ArrayList<NachExcelInfo> stuList) {
   
    	int i=0;
    	int count =0;
    	
        try (Connection conn = DataBaseConnection.JAVAConnection()) {

            String query = "UPDATE debit_loan SET umrn_no = ? WHERE loanNo = ?";

            try (PreparedStatement ps = conn.prepareStatement(query)) {
               
            	System.out.println("Method update start !!!");
                for (NachExcelInfo ff : stuList) {
                	
                    ps.setString(1, ff.getUmrn());
                    ps.setString(2, ff.getLoanNo());
                    
                   i = ps.executeUpdate();
            
                } 
              
               if(i>0) {
            	   
            	    FacesContext.getCurrentInstance().addMessage(null,  new FacesMessage("Umrn Number Updated Succesfully..!!"));
            	   
               }
                
                          
            } catch (Exception e) {
                e.printStackTrace();
                // Consider logging this error
            }

        

        } catch (Exception e) {
            e.printStackTrace();
            // Consider logging or alerting here
        }
    }

    	
    


	public String getFilePath() {
		return filePath;
	}


	public void setFilePath(String filePath) {
		this.filePath = filePath;
	}


	public String getFilePath2() {
		return filePath2;
	}


	public void setFilePath2(String filePath2) {
		this.filePath2 = filePath2;
	}


	public String getBranchId() {
		return branchId;
	}


	public void setBranchId(String branchId) {
		this.branchId = branchId;
	}


	public UploadedFile getExcelFile() {
		return excelFile;
	}


	public void setExcelFile(UploadedFile excelFile) {
		this.excelFile = excelFile;
	}


	public UploadedFile getExcelFile2() {
		return excelFile2;
	}


	public void setExcelFile2(UploadedFile excelFile2) {
		this.excelFile2 = excelFile2;
	}


	public DataBaseMethods getDbm() {
		return dbm;
	}


	public void setDbm(DataBaseMethods dbm) {
		this.dbm = dbm;
	}


	public ArrayList<NachExcelInfo> getStuList() {
		return stuList;
	}


	public void setStuList(ArrayList<NachExcelInfo> stuList) {
		this.stuList = stuList;
	}
    
    
    
    
    
    
    
    
    
    
	
}
