package main_package;

import java.io.Serializable;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.faces.model.SelectItem;
import javax.servlet.http.HttpSession;

import report_package.DataBaseReportMethods;

@ManagedBean(name="umrnshare")
@ViewScoped

public class UpdateUmrnByshareholderBean implements Serializable
{
	String memberNo, dsaCode;
    int branchId;
    Date fromDate = new Date(), toDate =  new Date();
    boolean show;
    
    ArrayList<SelectItem> memberList, dsaList;
    ArrayList<LoanInfo> list, viewList, infolist;
    ArrayList<EnachDataExportInfo> loanList;

    DbYashwant dby = new DbYashwant();
	DataBaseMethods dbm = new DataBaseMethods();
    DataBaseMethodsRaviK dbr = new DataBaseMethodsRaviK();
    DataBaseReportMethods obj1 = new DataBaseReportMethods();
    DataBaseMethodTaheer dbmt = new DataBaseMethodTaheer();
    
    SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd");
    
    
    
	public UpdateUmrnByshareholderBean() 
	{
		HttpSession ss = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		branchId = (int) ss.getAttribute("branchId");

		memberNoListMethod();
		fetchUmrnList();
	}
    
	
	
    public void memberNoListMethod()
	{	
    	HttpSession ss = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		branchId = (int) ss.getAttribute("branchId");
		Connection conn = DataBaseConnection.JAVAConnection();
		memberList = dbr.memberList(branchId,conn);
		dsaList=new DataBaseMethods().allDsaList(branchId,"Loan",conn);
		
		try 
		{
			conn.close();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();	
		}
	}
    
    
    
    public void fetchUmrnListShare()
	{	    	
    	HttpSession ss = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
    	int branchId = (int) ss.getAttribute("branchId");
		
		Connection conn = DataBaseConnection.JAVAConnection();
	    try
	    {
//	    	list = dbr.fetchLoanUmrnbyShare(branchId, memberNo);
	    	list = dbmt.fetchLoanUmrnlist(branchId, memberNo);
	    	
	        if (list != null && !list.isEmpty())
	        {
	            show = true;
	            FacesContext.getCurrentInstance().addMessage(null,
	                    							new FacesMessage(FacesMessage.SEVERITY_INFO,"Record Found Successfully", ""));
	        }
	        else
	        {
	            show = false;
	            FacesContext.getCurrentInstance().addMessage(null,
	                    							new FacesMessage(FacesMessage.SEVERITY_WARN,"No Record Found", ""));
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	        FacesContext.getCurrentInstance().addMessage(null,
	                								new FacesMessage(FacesMessage.SEVERITY_ERROR,"Something went wrong", ""));
	    }
	    finally
	    {
	        try
	        {
	            if (conn != null)
	            {
	                conn.close();
	            }
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
	}
    
    
    
    public void updateAllUmrn()
    {
        Connection conn = null;
        PreparedStatement ps = null;

        int count = 0;
        try
        {
            conn = DataBaseConnection.JAVAConnection();

            String query = "UPDATE debit_loan SET umrn_no=? WHERE loanNo=?";

            ps = conn.prepareStatement(query);

            for(LoanInfo ll : list)
            {
                if(ll.getUmrnNo() != null)
                {
                    ps.setString(1, ll.getUmrnNo().trim());
                    ps.setString(2, ll.getLoanNo());

                    ps.addBatch();
                }
            }

            int[] result = ps.executeBatch();

            for(int r : result)
            {
                count += r;
            }

            FacesContext.getCurrentInstance().addMessage(
                    null,
                    new FacesMessage(
                            FacesMessage.SEVERITY_INFO, "Updated Successfully", ""
                    ));
        }
        catch(Exception e)
        {
            e.printStackTrace();

            FacesContext.getCurrentInstance().addMessage(
                    null,
                    new FacesMessage(
                            FacesMessage.SEVERITY_ERROR,
                            "Error while updating",
                            ""
                    ));
        }
        finally
        {
            try
            {
                if(ps != null)
                {
                    ps.close();
                }

                if(conn != null)
                {
                    conn.close();
                }
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }
    }

    
    
    public void totalLoan()
    {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            conn = DataBaseConnection.JAVAConnection();

            String query =
                    "SELECT " +
                    "SUM(CASE WHEN paidStatus='PAID' THEN 1 ELSE 0 END) total_paid, " +
                    "SUM(CASE WHEN paidStatus='UNPAID' THEN 1 ELSE 0 END) total_unpaid " +
                    "FROM (" +
                    "SELECT paidStatus FROM personalloan " +
                    "UNION ALL " +
                    "SELECT paidStatus FROM secure_loan " +
                    "UNION ALL " +
                    "SELECT paidStatus FROM grouploan" +
                    ") t";

            ps = conn.prepareStatement(query);

            rs = ps.executeQuery();

            if(rs.next())
            {
                int totalPaid = rs.getInt("total_paid");
                int totalUnpaid = rs.getInt("total_unpaid");

                System.out.println("Paid : " + totalPaid);
                System.out.println("Unpaid : " + totalUnpaid);

                FacesContext.getCurrentInstance().addMessage(
                        null,
                        new FacesMessage(
                                FacesMessage.SEVERITY_INFO,
                                "Paid : " + totalPaid + " | Unpaid : " + totalUnpaid,
                                "")
                );
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if(rs != null) rs.close();
                if(ps != null) ps.close();
                if(conn != null) conn.close();
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }
    }
    
    
    
    public void fetchUmrnList()
   	{	       	
   		HttpSession ss = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
   		int branchId = (int) ss.getAttribute("branchId");
   		
   		Connection conn = DataBaseConnection.JAVAConnection();
   	    try
   	    {
   	    	list = dbmt.fetchLoanUmrnlist(branchId, "all");
   	    	viewList = dbmt.viewLoanUmrnlist(branchId);

   	        if (list != null && !list.isEmpty())
   	        {
   	            show = true;

//   	            FacesContext.getCurrentInstance().addMessage(null,
//   	                    new FacesMessage(FacesMessage.SEVERITY_INFO,"Record Found Successfully", ""));
   	        }
   	        else
   	        {
   	            show = false;

   	            FacesContext.getCurrentInstance().addMessage(null,
   	                    new FacesMessage(FacesMessage.SEVERITY_WARN,"No Record Found",""));
   	        }
   	    }
   	    catch (Exception e)
   	    {
   	        e.printStackTrace();
   	        FacesContext.getCurrentInstance().addMessage(null,
   	                new FacesMessage(FacesMessage.SEVERITY_ERROR,"Something went wrong",""));
   	    }
   	    finally
   	    {
   	        try
   	        {
   	            if (conn != null)
   	            {
   	                conn.close();
   	            }
   	        }
   	        catch (Exception e)
   	        {
   	            e.printStackTrace();
   	        }
   	    }
   	}
    
    
    
    public void  nachExport()
    {
    	loanList = new ArrayList<>();
    	infolist = new ArrayList<>();
    	
    	Connection con = DataBaseConnection.JAVAConnection();
    	loanList = dbmt.selectEnachLoanNo(con, fromDate, toDate, dsaCode);
    	dby.selectUmrnNo(loanList, con);
    	
    	for(EnachDataExportInfo ss : loanList)
    	{
    		if(null !=(ss.getUmrnNo()))
    		{
	    		dby.loanInstallment("personalloan", ss.getLoanNo(), con,infolist,ss.getUmrnNo());
    			dby.loanInstallment("grouploan", ss.getLoanNo(), con,infolist,ss.getUmrnNo());
    		}
    	}
    	
    	String frmD = sf.format(fromDate);
    	String toD = sf.format(toDate);
    	
    	loanList = new ArrayList<>();
    	
    	for (LoanInfo ll: infolist)
    	{
    		if(ll.getLoanNo()!= null)
    		{
    			if(ll.getStrInstlmntDate().compareTo(frmD) >= 0 && ll.getStrInstlmntDate().compareTo(toD) <= 0)
    			{
	    			EnachDataExportInfo ss = new EnachDataExportInfo();
	    			
	    			String currentLoanNo = ll.getLoanNo();
		            currentLoanNo = currentLoanNo.replace("-", "");
	    			ss.setLoanNo(currentLoanNo);
	    			
	    			
	    			String[] parts = ll.getLoanNo().split("-");
	
	    			// parts[0] = "2025"
	    			ss.setEmiAmount(ll.getDueAmount());
	    			ss.setUmrnNo(ll.getUmrnNo());
	    			ss.setEmiDate(ll.getStrInstlmntDate());
	    			ss.setEmiNO(ll.getEmiNo());
	    			
	    			if(parts[0].equalsIgnoreCase("GL")) 
	    			{
	    				ss.setPreEmiAmount(dbm.preEmiChargeCalculationMethod(ll.getLoanNo(), "grouploan", con));	
	    			}
	    			else
	    			{
	    				ss.setPreEmiAmount(dbm.preEmiChargeCalculationMethod(ll.getLoanNo(), "personalloan", con));
	    			}
	    			
	    			if (ll.getUmrnNo() != null && !ll.getUmrnNo().trim().isEmpty()) 
	                {
	    				String sharholderNo = dbmt.shareholderNoByLoanNo(con, ll.getLoanNo());
	    				String name = dbm.shareHolderNameByShareHolderNo(sharholderNo, con);
	    				String mobile = dbmt.shareholderMobileNoByShareHolderNo(con, sharholderNo);
	    				
	    				ss.setShareHolderNo(sharholderNo);
	    				ss.setName(name);
	    				ss.setMobile(mobile);
	    				
	    				loanList.add(ss);
					}
    			}
    		}
    	}
    	
    	
    }
    
    
    
	public DataBaseMethods getDbm() {
		return dbm;
	}
	public void setDbm(DataBaseMethods dbm) {
		this.dbm = dbm;
	}
	public DataBaseMethodsRaviK getDbr() {
		return dbr;
	}
	public void setDbr(DataBaseMethodsRaviK dbr) {
		this.dbr = dbr;
	}
	public DataBaseReportMethods getObj1() {
		return obj1;
	}
	public void setObj1(DataBaseReportMethods obj1) {
		this.obj1 = obj1;
	
		
	}
	
	public void setBranchId(Integer branchId) {
		this.branchId = branchId;
	}


	public String getMemberNo() {
		return memberNo;
	}


	public void setMemberNo(String memberNo) {
		this.memberNo = memberNo;
	}


	public boolean isShow() {
		return show;
	}


	public void setShow(boolean show) {
		this.show = show;
	}


	public Integer getBranchId() {
		return branchId;
	}


	public ArrayList<SelectItem> getMemberList() {
		return memberList;
	}


	public void setMemberList(ArrayList<SelectItem> memberList) {
		this.memberList = memberList;
	}


	public ArrayList<LoanInfo> getList() {
		return list;
	}


	public void setList(ArrayList<LoanInfo> list) {
		this.list = list;
	}


	public ArrayList<LoanInfo> getViewList() {
		return viewList;
	}


	public void setViewList(ArrayList<LoanInfo> viewList) {
		this.viewList = viewList;
	}



	public Date getFromDate() {
		return fromDate;
	}



	public void setFromDate(Date fromDate) {
		this.fromDate = fromDate;
	}



	public Date getToDate() {
		return toDate;
	}



	public void setToDate(Date toDate) {
		this.toDate = toDate;
	}



	public ArrayList<EnachDataExportInfo> getLoanList() {
		return loanList;
	}



	public void setLoanList(ArrayList<EnachDataExportInfo> loanList) {
		this.loanList = loanList;
	}



	public String getDsaCode() {
		return dsaCode;
	}



	public void setDsaCode(String dsaCode) {
		this.dsaCode = dsaCode;
	}



	public ArrayList<SelectItem> getDsaList() {
		return dsaList;
	}



	public void setDsaList(ArrayList<SelectItem> dsaList) {
		this.dsaList = dsaList;
	}
	
	

}
