
package main_package;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;

import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import javax.faces.model.SelectItem;
import javax.naming.spi.DirStateFactory.Result;
import javax.servlet.http.HttpSession;

import org.primefaces.context.RequestContext;

import com.itextpdf.text.log.SysoCounter;
import main_package.masterSettingInfo;
import report_package.DataBaseReportMethods;

public class DataBaseMethodRahul implements Serializable
{

	private static final long serialVersionUID = 1L;
	


	public String umrnNoByLoanNoFromLoanPaymentTable(String loanNo) {
		
		String umrnNo="";
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
		 
			 String query = "select * from loan_payment_from_app where loan_no = ?";
           
			
			try(PreparedStatement ps =conn.prepareStatement(query)){
				
				ps.setString(1, loanNo);
				
				ResultSet rs = ps.executeQuery();
				
				while(rs.next()) {
					
					umrnNo= rs.getString("umrn_no");
//					System.out.println("value for the return :"+umrnNo);
				}
				
				
				
			}catch(Exception e) {
			
				e.printStackTrace();
			}
			
			
		}catch(Exception e) {
			
			e.printStackTrace();
		
		}
		
		return umrnNo;
		
	}
	
	
	
	public String umrnNoByLoanNoFromDebitLoanTable(String loanNo) {
	    String value = null;

	    try (Connection conn = DataBaseConnection.JAVAConnection()) {
	        String query = "SELECT umrn_no FROM debit_loan WHERE loanNo =?";
//	        System.out.println("Executing query: " + query);

	        try (PreparedStatement ps = conn.prepareStatement(query)) {
	            ps.setString(1, loanNo );

	            try (ResultSet rs = ps.executeQuery()) {
	                if (rs.next()) {
	                    value = rs.getString("umrn_no");
	                }
	            }

	        } catch (Exception e) {
	            e.printStackTrace();
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return value;
	}

	public int branchIdByLoanTypeAndLoanNo(String loanType, String loanNo) {
	   
		String column = "loanNo";
		int  branchId = 0;
		
		if("grouploan".equalsIgnoreCase(loanType)) {
			
			column = "sub_loan_no";
			
		}

	    try (Connection conn = DataBaseConnection.JAVAConnection()) {

	        // Add proper spacing and validate or sanitize the table name (loanType)
	        String query = "SELECT branch_id FROM " + loanType + " WHERE " + column +"  = ?";
           
	        try (PreparedStatement ps = conn.prepareStatement(query)) {
	           
	        	ps.setString(1, loanNo);

//	        	 System.out.println(query);
	          	
	            ResultSet rs = ps.executeQuery();

	            if (rs.next()) {
	            	
	                branchId = rs.getInt("branch_id");
	              
	            }

	        } catch (Exception e) {
	            e.printStackTrace();
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return branchId;
	}

	public String lastPaidEmiDateByLoanNo(String loanNo) {
		
		String lastDate = null;
	
		try(Connection conn = DataBaseConnection.JAVAConnection()){
			
			String query = "SELECT pay_date FROM loanpayment WHERE loanNo = ? ORDER BY pay_date DESC LIMIT 1";
		
			try(PreparedStatement ps = conn.prepareStatement(query)){
				
				ps.setString(1, loanNo);
				
				ResultSet rs = ps.executeQuery();
				
				while(rs.next()) {
					
					lastDate = rs.getString("pay_date");
					
				}
			    
			}catch(Exception e) {
				
				e.printStackTrace();
				
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
		}
		
		
	 return	lastDate;
	}
	
	
	
	public String insertFirmDetails(ArrayList<LastBranchDetailsListInfo> List ,String mobile ,String address1,String address2,String address3,String address4,String branchCode, String branchName,String userName,String password, Connection con) 
	{
		int branchId = 0;	
		int i=0;
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
		
			String query = "INSERT INTO firm_detail( address1, address2, address3,"
					+ " address4, name, logo, file_upload_path, file_view_path, mobile_no, member_age_limit, "
					+ "dealer_percent, mobile_no_two, mobile_no_three, check_mobile_duplicacy, software_validity, sent_by, "
					+ "branch_code, branch_name, branch_start, scheme_start) "
					+ "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
			
			try(PreparedStatement ps = conn.prepareStatement(query)){
			    
				for(LastBranchDetailsListInfo pp : List ) { 
					
					ps.setString(1, address1);
					ps.setString(2, address2);
					ps.setString(3, address3);
					ps.setString(4, address4);
					ps.setString(5, pp.getName());
					ps.setString(6 ,pp.getLogo());
					ps.setString(7, pp.getFile_upload_path());
					ps.setString(8, pp.getFile_view_path());
					ps.setString(9, mobile);
					ps.setString(10,"65");
					ps.setString(11,"0");
					ps.setString(12, "");
					ps.setString(13, "");
					ps.setString(14, "active");
					ps.setString(15, pp.getSoftware_validity());
					ps.setString(16, pp.getSent_by());
					ps.setString(17, branchCode);
					ps.setString(18, branchName);
					ps.setString(19, "true");
					ps.setString(20, "false");
				    
				    
				 i = ps.executeUpdate();
				
				}	
				
				
				if(i>0) {
					
					FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("BRANCH ADDED..!!!"));
					
				}
				
				
				
			}catch(Exception e) {
				
				e.printStackTrace();
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
		branchId  = branchId(branchCode);
		cityTableEntry(branchName ,branchId);
		companyShare(branchId);
		fySetting(branchId);
		masterSettingEntry(branchId);
		ArrayList<String> ss = new ArrayList<>();
		ss = loginTableAdminId();
		branchAllocateTableEntry(ss, branchId, branchName);
		loginTableEntry(userName,password,mobile,branchId);
		String id = idByBranchIdFromLogin(branchId);
		branchAllocateFinal( branchId,id, branchName);
	    
	 return "";
	
	} 
	
	
	public void branchAllocateFinal(int branchId ,String userId,String branchName) {
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
			
			String query = "insert into branch_allocate_table(branch_id,branch_name,user_id,status)values(?,?,?,?)";
			
			try(PreparedStatement ps = conn.prepareStatement(query) ){ 
				
				ps.setInt(1	, branchId);
				ps.setString(2, branchName);
				ps.setString(3, userId);
				ps.setString(4, "active");
				
				ps.execute();				
				
			}
			
			catch(Exception e)
			{				
				e.printStackTrace();				
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
	}	
	
	
	

public void fySetting(int branchId) {
	
    String financial_year = "", fy_start_date = "", fy_end_date = "", data_freeze_status = "", status = "";

	
	try(Connection conn = DataBaseConnection.JAVAConnection()){
		
		String query = "select * from fy_setting order by id desc limit 1";

		try(PreparedStatement ps = conn.prepareStatement(query) ){
		 
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				
				
				financial_year = rs.getString("financial_year");
				fy_start_date = rs.getString("fy_start_date");
				fy_end_date = rs.getString("fy_end_date");
				data_freeze_status = rs.getString("data_freeze_status");
				status = rs.getString("status");
				

			}
			
			fysettingfilling(financial_year, fy_start_date, fy_end_date, data_freeze_status, status, branchId);

			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
	}catch(Exception e) {
		
		e.printStackTrace();
		
	}
}  



public void fysettingfilling(String financial_year, String fy_start_date, String fy_end_date, String data_freeze_status, String status, int branch_id) {
	
	try(Connection conn = DataBaseConnection.JAVAConnection()){
		
		String query = "INSERT INTO fy_setting(financial_year, fy_start_date, fy_end_date, data_freeze_status, status, branch_id) VALUES (?, ?, ?, ?, ?, ?)" ;

		
		try(PreparedStatement ps = conn.prepareStatement(query) ){
			
			ps.setString(1, financial_year);
			ps.setString(2, fy_start_date);
			ps.setString(3, fy_end_date);
			ps.setString(4, data_freeze_status);
			ps.setString(5, status);
			ps.setInt(6, branch_id);
         
		   ps.executeUpdate();
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
	}catch(Exception e) {
		
		e.printStackTrace();
		
	}

}


	
	
	public void companyShare(int branchId) {
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
			
		String query = "INSERT INTO company_shares(no_of_shares, share_amount, shares_alloted, company_capital_amount, amount_shared, share_amt_member, share_check, branch_id, transfer_from) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

			
			try(PreparedStatement ps = conn.prepareStatement(query) ){
				
				ps.setString(1, "10000");
				ps.setString(2, "10");
				ps.setString(3, "0.0");
				ps.setString(4, "0");
				ps.setString(5, "0");
				ps.setString(6, "no");
				ps.setString(7, "no");
				ps.setInt(8, branchId);
				ps.setString(9, "");
                
		      ps.executeUpdate();
				
			}catch(Exception e) {
				
				e.printStackTrace();
				
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}

	}
	
   
	public int  branchId(String branchCode) {
		
		int bi = 0;
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
		
       String queryGE = "select * from firm_detail where branch_code = ?";
		
		try(PreparedStatement ps = conn.prepareStatement(queryGE)){
			
			ps.setString(1, branchCode);
		
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				
				bi= rs.getInt("id");
				
			}
			
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
	 	
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
		return bi;
		
	}
	
	
	public void cityTableEntry(String cityname , int branchId) {
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
			
			String query = "insert into cityname(cityname,branch_id)value(?,?)";
			
			try(PreparedStatement ps = conn.prepareStatement(query) )
			{
				
				ps.setString(1, cityname);
				ps.setInt(2, branchId);
				
				ps.executeUpdate();
				
			}catch(Exception e) {
				
				e.printStackTrace();
				
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
	}
	
	public void GLchqueDetailEntry(String loanNo, String chequeHolderName, String chequeType1, String chequeNo, String bankName, String bankIfsc, String bankBranch ,String client_no, String bankAccNo) {
		
		int i = 0 ;
		String subLoanNo = loanNo.split("/")[1].trim();
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
			
			String query = "INSERT INTO cheque_gl_entry (sub_loan_no, cheque_holder_name, cheque_type1, cheque_no, bank_name, bank_Ifsc, bank_branch , shareholder_no, bank_acc_no) VALUES (?, ?, ?, ?, ?, ?, ? ,?, ?)";

			
			try(PreparedStatement ps = conn.prepareStatement(query) ){
				
				ps.setString(1, subLoanNo);
				ps.setString(2, chequeHolderName);
				ps.setString(3, chequeType1);
				ps.setString(4, chequeNo);
				ps.setString(5, bankName);
				ps.setString(6, bankIfsc);
				ps.setString(7, bankBranch);
				ps.setString(8, client_no);
				ps.setString(9, bankAccNo);
                
			   i = ps.executeUpdate();
				
			   if(i>0) {
				   
				   FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Cheque Details Submit Successfully..!!"));
				   
			   }
			   
			}catch(Exception e) {
				
				e.printStackTrace();
				
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
	}
 
	
	
	public ArrayList<SelectItem> bankNameList( ) {
		ArrayList<SelectItem> iL;
		iL = new ArrayList<>();
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
			
			String query = "select id,bank_name,bank_branch from bank_table";
			
			try(PreparedStatement ps = conn.prepareStatement(query) ){
				
				ResultSet rs = ps.executeQuery();
				
				while(rs.next()) {
					
					SelectItem rr = new SelectItem();
					
					rr.setLabel(rs.getString("bank_name")+"/"+rs.getString("bank_branch"));
					rr.setValue(rs.getString("id"));
				
					iL.add(rr);
					
				}
				
				
			}catch(Exception e) {
				
				e.printStackTrace();
				
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
		return iL;
	}
	
	
	public ArrayList<String> loginTableAdminId() {
		
		ArrayList<String> ss = new ArrayList<>();
		String id = "";
		try(Connection conn = DataBaseConnection.JAVAConnection()){
			
			String query = "SELECT id FROM login WHERE userid IN (?, ?, ?)";
			
			try(PreparedStatement ps = conn.prepareStatement(query)){
				
				ps.setString(1, "admin");
				ps.setString(2, "321024");
				ps.setString(3, "pakiya");
			
				ResultSet rs = ps.executeQuery();
				
				while(rs.next()) {
					
					id = rs.getString("id");
					
					ss.add(id);
					
				}
			
				
			}catch(Exception e) {
				
				e.printStackTrace();
				
			}
			
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
		return ss;
		
	}
	

	public void masterSettingEntry(int branchId) {
	  int firstValue = 0;
	  String elValue ="";
	 
	 	
	  String id = lastNumberCheck();   //here method calling for last entry number !
	
	  ArrayList<masterSettingInfo> masterSettingList = new ArrayList<>();
	
	      masterSettingList = masterSettingValueList(masterSettingList,id);
	  
		try(Connection conn = DataBaseConnection.JAVAConnection()){
			
			String query = "insert into master_setting(account_initial,account_length,account_type,branch_id)values(?,?,?,?)";
			
			try(PreparedStatement ps = conn.prepareStatement(query)){
				
			for(masterSettingInfo msi : masterSettingList) {
				
				if(!msi.getAccount_initial().contains("-")) {
					firstValue = Integer.valueOf(msi.getAccount_initial());
					
					 firstValue = firstValue+1;
					 
					ps.setInt(1, firstValue);
				}
				else {
					
					elValue = msi.getAccount_initial();
					elValue = elValue.contains("-") ? elValue.substring(0, elValue.indexOf('-') + 1) : elValue;
					elValue = elValue+String.valueOf(firstValue);
                    ps.setString(1, elValue);
				}
				
				ps.setString(2, msi.getAccount_length());
				ps.setString(3, msi.getAccount_type());
				ps.setInt(4, branchId);
				
				ps.executeUpdate();
				
			}
				
				
			}catch(Exception e) {
				
				e.printStackTrace();
				
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
	}
	
	public ArrayList<masterSettingInfo> masterSettingValueList(ArrayList<masterSettingInfo> List, String lastId) {
	
	try(Connection conn = DataBaseConnection.JAVAConnection()){
		
		String query = "select * from master_setting where branch_id = ?";
		
		try(PreparedStatement ps = conn.prepareStatement(query) ){
			
			ps.setString(1, lastId);
			
			ResultSet rs = ps.executeQuery();
			
		
			
			while(rs.next()) {
				
		  masterSettingInfo ll = new masterSettingInfo(); 
			
			
			
	    	ll.setAccount_initial(rs.getString("account_initial"));
	    	ll.setAccount_length(rs.getString("account_length"));
	    	ll.setAccount_type(rs.getString("account_type")); 
	    	
	        List.add(ll);
				
			}
		 
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
	}catch(Exception e) {
		
		e.printStackTrace();
		
	}
	
	return List;
}
	
	
	public String lastNumberCheck() {
		
		String lastValue="";
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
			
			String query = "SELECT id,branch_id FROM master_setting ORDER BY id DESC LIMIT 1";
			
			try(PreparedStatement ps = conn.prepareStatement(query) ){
				
				ResultSet rs = ps.executeQuery();
				
				while(rs.next()) {
					
					lastValue = rs.getString("branch_id");
					
				
				}
				
			}catch(Exception e) {
				
				e.printStackTrace();
		
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
		return lastValue;
		
	}
	
	
	public ArrayList<ChequeDetailsEntryInfo> checkDetailByshareHolderNo( ArrayList<ChequeDetailsEntryInfo> list  , String shareHolderNo) {
		
		
		// Define a date format      
		
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yyyy");
	
	try(Connection conn = DataBaseConnection.JAVAConnection()){
		
		String query = "select * from cheque_gl_entry where shareholder_no=?";
			
		try(PreparedStatement ps = conn.prepareStatement(query))
		{		
			ps.setString(1, shareHolderNo);
			
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
			
			ChequeDetailsEntryInfo  cde = new ChequeDetailsEntryInfo();
			
			cde.setBank_branch(rs.getString("bank_branch"));
			cde.setBank_Ifsc(rs.getString("bank_ifsc"));
			cde.setBank_name(rs.getString("bank_name"));
			cde.setCheque_holder_name(rs.getString("cheque_holder_name"));
			cde.setCheque_no(rs.getString("cheque_no"));
			cde.setCheque_type1(rs.getString("cheque_type1"));
			cde.setEntry_date(rs.getDate("entry_date").toLocalDate().format(formatter));
			cde.setShareholder_no(rs.getString("shareholder_no"));
			cde.setSub_loan_no(rs.getString("sub_loan_no"));
			cde.setBankAccNo(rs.getString("bank_acc_no"));
				
			list.add(cde);
			
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
	}catch(Exception e) {
		
		e.printStackTrace();
		
	}
	
	return list;
}
	
	
	public void branchAllocateTableEntry(ArrayList<String> userList ,int branchId ,String branchName) {
	
	try(Connection conn = DataBaseConnection.JAVAConnection()){
		
		String query = "insert into branch_allocate_table(branch_id,branch_name,user_id,status)values(?,?,?,?)";
		
		try(PreparedStatement ps = conn.prepareStatement(query) ){ 
			
			for(String tt :userList) {
			 
			  
			  ps.setInt(1, branchId);
			  ps.setString(2, branchName);
			  ps.setString(3, tt);
			  ps.setString(4, "active");	
			  ps.executeUpdate();
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
	}catch(Exception e) {
		
		e.printStackTrace();
		
	}
}	
	


	public void loginTableEntry(String userid,String password, String mobNo ,int branchId) {
	
	try(Connection conn = DataBaseConnection.JAVAConnection()){
		
		String query = "insert into login(userid,password,type,status,mobile_no,branch_id)values(?,?,?,?,?,?)";
		
		try(PreparedStatement ps = conn.prepareStatement(query) )
		{
			
			ps.setString(1, userid);
			ps.setString(2, password);
			ps.setString(3, "admin");
			ps.setString(4, "active");
			ps.setString(5, mobNo);
			ps.setInt(6, branchId);
			ps.executeUpdate();
			
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
	}catch(Exception e) {
		
		e.printStackTrace();
		
	}
}

	public String idByBranchIdFromLogin(int branchId) {
		
		String id = "";
	
	try(Connection conn = DataBaseConnection.JAVAConnection()){
		
		String query = "select * from login where branch_id = ?";
		
		try(PreparedStatement ps = conn.prepareStatement(query) ){
			
			ps.setInt(1, branchId);
			
			ResultSet rs = ps.executeQuery();
			
		   if(rs.next()) {
			   
			  id = rs.getString("id"); 
			   
			   
		   }
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
	}catch(Exception e) {
		
		e.printStackTrace();
		
	}
	
	return id;
}
	//share holder bank details
	public ArrayList<UserInfo>  shareHolderAppBankDetails(String shareHolderNo , int branchId , ArrayList<UserInfo> list) {
	
	try(Connection conn = DataBaseConnection.JAVAConnection()){
		
		String query = "select * from shareholder_info where shareholderno = ? and branch_id =?";
		
		try(PreparedStatement ps = conn.prepareStatement(query) ){
			
			ps.setString(1, shareHolderNo);
			ps.setInt(2, branchId);
			
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				
				UserInfo ui = new UserInfo();
				
				ui.setChequeNo(rs.getString("cheque_no"));
				ui.setAccountNo(rs.getString("bank_accountNo"));
				ui.setBank_name(rs.getString("bank_name"));
				ui.setBranch(rs.getString("branch"));
				ui.setBankIfsc(rs.getString("bank_ifsc"));
				
				list.add(ui);
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
	}catch(Exception e) {
		
		e.printStackTrace();
		
	}
	
	return list;
}	
  	
	//applicant personal details method !
	public ArrayList<nomineeGuarantorBankInfo> applicantPersonalloanDetailByShareHolderNo(String loanType ,String shareHolderNo , ArrayList<nomineeGuarantorBankInfo> nGList ,int branchId)
	{
	
	try(Connection conn = DataBaseConnection.JAVAConnection()){
		
		String query = "select * from "+loanType+" where shareholderno = ? and branch_id=?" ;
		
		try(PreparedStatement ps = conn.prepareStatement(query) ){
			
			ps.setString(1, shareHolderNo);
			ps.setInt(2, branchId);
			
			ResultSet rs = ps.executeQuery();
			
			System.out.println("query for per || :"+query);
			
			while(rs.next()) {
				
			nomineeGuarantorBankInfo ng = new nomineeGuarantorBankInfo();
			ng.setShareholderno(rs.getString("shareholderno"));
			ng.setLoanNo(rs.getString("loanNo"));
			ng.setIdproof(rs.getString("idproof"));
			ng.setFilecharge(rs.getString("filecharge"));
			ng.setLoanAmount(rs.getString("loanAmount"));
			ng.setInterest(rs.getString("interest"));
			ng.setTerms(rs.getString("terms"));
			ng.setFrequency(rs.getString("frequency"));
			ng.setEmi(rs.getString("emi"));
			ng.setFirstguarantor(rs.getString("firstguarantor"));
			ng.setSecondguarantor(rs.getString("secondguarantor"));
			ng.setRequestDate(rs.getString("requestDate"));
			ng.setStatus(rs.getString("status"));
			ng.setApprovalDate(rs.getString("approvalDate"));
			ng.setReason(rs.getString("reason"));
			ng.setRD_FD_no(rs.getString("RD_FD_no"));
			ng.setPaidStatus(rs.getString("paidStatus"));
			ng.setDiscount(rs.getString("discount"));
			ng.setEmiType(rs.getString("emiType"));
			ng.setEmi_due_date(rs.getString("emi_due_date"));
			ng.setApp_chq_no(rs.getString("app_chq_no"));
			ng.setApp_acc_no(rs.getString("app_acc_no"));
			ng.setApp_bank_name(rs.getString("app_bank_name"));
			ng.setApp_bank_branch(rs.getString("app_bank_branch"));
			ng.setG1_chq_no(rs.getString("g1_chq_no"));
			ng.setG1_acc_no(rs.getString("g1_acc_no"));
			ng.setG1_bank_name(rs.getString("g1_bank_name"));
			ng.setG1_bank_branch(rs.getString("g1_bank_branch"));
			ng.setG2_chq_no(rs.getString("g2_chq_no"));
			ng.setG2_acc_no(rs.getString("g2_acc_no"));
			ng.setG2_bank_name(rs.getString("g2_bank_name"));
			ng.setG2_bank_branch(rs.getString("g2_bank_branch"));
			ng.setDsacode(rs.getString("dsacode"));
			ng.setCommission(rs.getString("commission"));
			ng.setIncrease_amount(rs.getString("increase_amount"));
			ng.setInsurance_fee(rs.getString("insurance_fee"));
			ng.setUser_id(rs.getString("user_id"));
			ng.setRestructure_status(rs.getString("restructure_status"));
			ng.setPlan_no(rs.getString("plan_no"));
			ng.setCenter(rs.getString("center"));
			ng.setEmi_mode(rs.getString("emi_mode"));
			ng.setExtra_amount(rs.getString("extra_amount"));
			ng.setEntry_date(rs.getString("entry_date"));
			ng.setRemark(rs.getString("remark"));
			ng.setDoc1(rs.getString("doc1"));
			ng.setDoc2(rs.getString("doc2"));
			ng.setDoc3(rs.getString("doc3"));
			ng.setDoc4(rs.getString("doc4"));
			ng.setDoc5(rs.getString("doc5"));
			ng.setNom_chq_no(rs.getString("nom_chq_no"));
			ng.setNom_acc_no(rs.getString("nom_acc_no"));					
			ng.setNom_bank_name(rs.getString("nom_bank_name"));
			ng.setNom_bank_branch(rs.getString("nom_bank_branch"));
			ng.setNom_bank_ifsc(rs.getString("nom_bank_ifsc"));
			ng.setApp_bank_ifsc(rs.getString("app_bank_ifsc"));
			ng.setG1_bank_ifsc(rs.getString("g1_bank_ifsc"));
			ng.setG2_bank_ifsc(rs.getString("g2_bank_ifsc"));
			ng.setBranch_id(rs.getString("branch_id"));
			ng.setDoc6(rs.getString("doc6"));
			ng.setDoc7(rs.getString("doc7"));
			ng.setDoc8(rs.getString("doc8"));
			ng.setDoc9(rs.getString("doc9"));
			ng.setDoc10(rs.getString("doc10"));
			
         
			nGList.add(ng);	
				
			}
			
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
	}catch(Exception e) {
		
		e.printStackTrace();
		
	}
	
	return nGList;
  }
 
	public void loanStatement( LoanInfo selectedLoan ,ArrayList<UserInfo>loanList )
	{  
		Connection conn=DataBaseConnection.JAVAConnection();
		loanList= new ArrayList<>();
		LoanInfo obj=null ;
		
		if(selectedLoan.getLoanType().equalsIgnoreCase("Group Loan"))
		{
			
			obj=new DataBaseMethods().loanInfoByLoanNoLoanType(selectedLoan.getSubLoanNo(),"grouploan", conn);
			 double amount=((Double.valueOf(obj.getLoanTerm()))*obj.getInstallAmount())-Double.valueOf(obj.getLoanAmount());
			UserInfo tt=new UserInfo();
			tt.setCashRecievedStatus("AMT FINANCED");
			tt.setEntryDate(obj.getApprovalDate());
			tt.setFinanceAmt(Double.valueOf(obj.getLoanAmount())+amount);
			tt.setAmount(0);
			tt.setRecievedAmount(0);
			tt.setStatus("");
			tt.setBalance(0);
			tt.setOpeningAmt(Double.valueOf(obj.getLoanAmount())+amount);
			loanList.add(tt);
			loanList= new DataBaseReportMethods().loanStatementForSurya(selectedLoan.getSubLoanNo(),selectedLoan.getLoanType(),loanList,tt, conn);
		/*	for(UserInfo jj: loanList)
			{
				// System.out.println("dsdsdsdsdsdsdsdsd"+jj.getOpeningAmt());
				if(jj.getStatus().equalsIgnoreCase("due"))
				{
					
				}
			}*/
			
		}
		else
		{
			
			obj=new DataBaseMethods().loanInfoByLoanNoLoanType(selectedLoan.getLoanNo(),"personalloan", conn);
			 double amount=((Double.valueOf(obj.getLoanTerm()))*obj.getInstallAmount())-Double.valueOf(obj.getLoanAmount());
			//// System.out.println("sjdjdksjkdsjdjkskdjjsd"+Double.valueOf(obj.getLoanAmount()));
			UserInfo tt=new UserInfo();
			tt.setCashRecievedStatus("AMT FINANCED");
			tt.setEntryDate(obj.getApprovalDate());
			tt.setFinanceAmt(Double.valueOf(obj.getLoanAmount())+amount);
			tt.setAmount(0);
			tt.setRecievedAmount(0);
			tt.setStatus("");
			tt.setBalance(0);
			tt.setOpeningAmt(Double.valueOf(obj.getLoanAmount())+amount);
			loanList.add(tt);
			loanList= new DataBaseReportMethods().loanStatementForSurya(selectedLoan.getLoanNo(),selectedLoan.getLoanType(),loanList,tt, conn);
			double a=0;
			int i=0;
			/*for(UserInfo jj: loanList)
			{
				
				if(jj.getOpeningAmt() > 0)
				{
					// System.out.println("dsssssssssssssssssss"+jj.getOpeningAmt());
				a=jj.getOpeningAmt();
				}
				if(jj.getStatus().equalsIgnoreCase("due"))
				{
					// System.out.println("dsdsdsds"+a);
					jj.setOpeningAmt(a);
				}
				
			}*/	
		}
		
		Collections.sort(loanList);
		
		
		
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		ss.setAttribute("penaltyAmount", 0.0);
		ss.setAttribute("penaltyPaid", 0.0);
		ss.setAttribute("penaltyDue", 0.0);
		ss.setAttribute("totalChqCharge", 0.0);
		ss.setAttribute("paidChqCharge", 0.0);
		ss.setAttribute("dailyPayList", loanList);
		ss.setAttribute("selectedVal",selectedLoan);
		ss.setAttribute("operationVal", "loan");
		RequestContext.getCurrentInstance().execute("window.open('loanStatementForUser.xhtml')");
  	
		
	}
	
	
	public ArrayList<ProfitAndLoss2Info>  profitAndLossListSunlog(){
		int i = 1 ;
		
		ArrayList<ProfitAndLoss2Info> pl=new ArrayList<>();
//		String group_head_name = "";
//		String group_head_type = "";
	    String query = "";
		
	    try(Connection conn = DataBaseConnection.JAVAConnection()){
	    	
//	    	if(i%2==0) {
//	    	 query = "select * from group_head where group_head_type ='"+"Expense"+"'  ";
//	    	}else
//	    	{
//	    	 query = "select * from group_head where group_head_type ='"+"Income"+"'  ";
//	    	}
	    	
	    	query = "select * from group_head where group_head_type ='"+"Expense"+"' ";
	    	System.out.println(query);
	    	
	    	 
	    	try(PreparedStatement ps = conn.prepareStatement(query)){
	    		
	    	   ResultSet rs = ps.executeQuery();
	    	   
	    	   while(rs.next()) {
	    		   
	    		   ProfitAndLoss2Info ll =new ProfitAndLoss2Info();
	    		   ll.setGroup_head_name(rs.getString("group_head_name"));
	    		   System.out.println(rs.getString("group_head_name"));
	    		 //  ll.setGroup_head_type(rs.getString("group_head_type"));
//	    		   group_head_name = rs.getString("group_head_name");
//	    		   group_head_type = rs.getString("group_head_type");
	    		   
	    		  pl.add(ll); 
	    			
	    		   
	    	   }
		    	
		    }catch(Exception e) {
		    	
		    	e.printStackTrace();
		    	
		    }
			
	    	
	    }catch(Exception e) {
	    	
	    	e.printStackTrace();
	    	
	    }
		return pl;
		
		
	}
	
	public ArrayList<ProfitAndLoss2Info> profitAndLossListSunlogIncome(){
		int i = 1 ;
		ArrayList<ProfitAndLoss2Info> pl=new ArrayList<>();
	
//		String group_head_name = "";
//		String group_head_type = "";
	    String query = "";
		
	    try(Connection conn = DataBaseConnection.JAVAConnection()){
	    	
//	    	if(i%2==0) {
//	    	 query = "select * from group_head where group_head_type ='"+"Expense"+"'  ";
//	    	}else
//	    	{
//	    	 query = "select * from group_head where group_head_type ='"+"Income"+"'  ";
//	    	}
	    	
	    	query = "select * from group_head where group_head_type ='"+"income"+"' ";
	    	
	    	 
	    	try(PreparedStatement ps = conn.prepareStatement(query)){
	    		
	    	   ResultSet rs = ps.executeQuery();
	    	   
	    	   while(rs.next()) {
	    		   
	    		   ProfitAndLoss2Info ll =new ProfitAndLoss2Info();
	    		   ll.setGroup_head_name(rs.getString("group_head_name"));
	    		 //  ll.setGroup_head_type(rs.getString("group_head_type"));
//	    		   group_head_name = rs.getString("group_head_name");
//	    		   group_head_type = rs.getString("group_head_type");
	    		   
	    		  pl.add(ll); 
	    			
	    		   
	    	   }
		    	
		    }catch(Exception e) {
		    	
		    	e.printStackTrace();
		    	
		    }
			
	    	
	    }catch(Exception e) {
	    	
	    	e.printStackTrace();
	    	
	    }
		return pl;
		
		
	}
	
	
	public void selectIncome(ArrayList<ProfitAndLoss2Info> pl , Date date)
	{  
	    String incomeName="";
	    double  incomeAmount=0.0;
	    Timestamp tt =new Timestamp(date.getTime());
		ArrayList<ProfitAndLoss2Info> yy = new ArrayList<>();
		
		for(ProfitAndLoss2Info in: pl)
		{
			incomeAmount=0.0;
			{
				 String qq="select * from income_table where item ='"+in.getGroup_head_name()+"' and income_date='"+tt+"'  ";
			
				  try(Connection conn = DataBaseConnection.JAVAConnection() ){
					  
					  try(PreparedStatement ps = conn.prepareStatement(qq)){
						  
						  
						  
						  ResultSet rs = ps.executeQuery();
						  
						  while(rs.next()) {
							 ProfitAndLoss2Info pp = new ProfitAndLoss2Info();
							 
							
							 incomeAmount+= rs.getDouble("amount");
							
							 
							  
							  
						  }
						  in.setIndirectIncomeAmount(incomeAmount);
						  
					  }catch(Exception e) {
						  
						  e.printStackTrace();
						  
					  }
					  
				  }catch(Exception e) {
					  
					  e.printStackTrace();
					  
				  }
			}
			
			
			
			
		}
		
	}
	

	public void selectExpense(ArrayList<ProfitAndLoss2Info> pl ,String incomeName , double incomeAmount, Date date)
	{  
	    String ExpenseName="";
	    double ExpenseAmount=0.0;
	    Timestamp tt =new Timestamp(date.getTime());
	    
	    
		ArrayList<ProfitAndLoss2Info> yy = new ArrayList<>();
		
		for(ProfitAndLoss2Info in: pl)
		{
			ExpenseAmount=0.0;
			{
				 String qq="select * from expense_table where item ='"+in.getGroup_head_name()+"' and expense_date='"+tt+"'";
				 System.out.println(qq);
			
				  try(Connection conn = DataBaseConnection.JAVAConnection() ){
					  
					  try(PreparedStatement ps = conn.prepareStatement(qq)){
						  
						  
						  
						  ResultSet rs = ps.executeQuery();
						  
						  while(rs.next()) {
							 ProfitAndLoss2Info pp = new ProfitAndLoss2Info();
							 
							
							 ExpenseAmount+= rs.getDouble("amount");
							 
							   
							  
						  }
						  in.setIndirectExpenseAmount(ExpenseAmount);
					  }catch(Exception e) {
						  
						  e.printStackTrace();
						  
					  }
					  
				  }catch(Exception e) {
					  
					  e.printStackTrace();
					  
				  }
			}
			
			
			
			
		}
		
	}
	
	
	
	public int maturityAmountTotalByShareHolderId(String shareHolderId) {
	
		int maturityAmount=0;
	
	try(Connection conn = DataBaseConnection.JAVAConnection()){
		
		String query = "select maturity_value from customer_info where shareHolderNo=?  and account_status='active'";
		
		try(PreparedStatement ps = conn.prepareStatement(query) ){
			
			ps.setString(1, shareHolderId);
			
			ResultSet rs = ps.executeQuery();
			
			if(rs.next()) {
				
				maturityAmount= rs.getInt("maturity_value");
				
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
	}catch(Exception e) {
		
		e.printStackTrace();
		
	}
	
	return maturityAmount;
}
	
	
	public ArrayList<SelectItem> employeeNameAndUserId(ArrayList<SelectItem> items,int branchId)
	{

	    String query = "SELECT name, uname FROM employee where branch_id='"+branchId+"' ";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query);
	    		
	         ResultSet rs = ps.executeQuery()) {

	        while (rs.next())
	        {
	            SelectItem item = new SelectItem(
	                    rs.getString("uname"),   // value
	                    rs.getString("name")     // label
	            );
	            items.add(item);
	        }

	    } catch (Exception e) {
	        e.printStackTrace(); // Consider proper logging instead
	    }

	    return items;
	}

	
	


}
	
	
//	public void generatingProfitAndLossList(ArrayList<ProfitAndLoss2Info> pl , String headName ,String headType , Date date) {
//		
//		String tableType ="";
//		String dateType ;
//		double totalAmount = 0.0;
//		try(Connection conn = DataBaseConnection.JAVAConnection()){
//			
//			if(headType.equalsIgnoreCase("Income")) {
//				
//				tableType = "income_table";
//				dateType = "income_date";
//				
//			}else {
//				
//				tableType = "expense_table	";
//				dateType = "expense_date";
//			}
//			
//			String query = "select * from "+tableType+" where item = ? and "+dateType+" = ? ";
//			
//			try(PreparedStatement ps = conn.prepareStatement(query) ){
//				
//				ps.setString(1, headName);
//				ps.setDate(2, new java.sql.Date(date.getTime()));
//				
//				ResultSet rs = ps.executeQuery();
//				
//				while(rs.next()) {
//				  
//			      totalAmount += rs.getDouble("amount");		
//			        		
//					
//				}
//			   
//				if(headType.equalsIgnoreCase("Expense"))
//				{
//				ProfitAndLoss2Info pli = new ProfitAndLoss2Info();
//				pli.setIndirectExpenseAmount(totalAmount);
//				pli.setIndirectExpense(headName);
//				pl.add(pli);
//				}else {
//			   
//					ProfitAndLoss2Info pli = new ProfitAndLoss2Info();
//					pli.setIndirectIncomeAmount(totalAmount);
//					pli.setIndirectIncome(headName);
//					pl.add(pli);
//					
//				}
//						
//			}catch(Exception e) {
//				
//				e.printStackTrace();
//				
//			}
//			
//		}catch(Exception e) {
//			
//			e.printStackTrace();
//			
//		}
//	}
//	
//	
//	
//}
//	









//public void methodName() {
//	
//	try(Connection conn = DataBaseConnection.JAVAConnection()){
//		
//		String query = "";
//		
//		try(PreparedStatement ps = conn.prepareStatement(query) ){
//			
//		}catch(Exception e) {
//			
//			e.printStackTrace();
//			
//		}
//		
//	}catch(Exception e) {
//		
//		e.printStackTrace();
//		
//	}
//}




//String[] parts = value.split("-");
//int number = Integer.parseInt(parts[1]);
//number=number+1; 
//System.out.println("value that will be use ahead :"+number);
//String finalValue=String.valueOf(number);
//
//String[] values = { finalValue, "EMP-" + finalValue, "PL-" + finalValue, "GL-" + finalValue, "SL-" + finalValue, "BL-" + finalValue };
//String[] account_type = {"Share Holder","DSA","Personal Loan","Group Loan","Secure Loan","Buisness Loan"};
//
//for (int i =0 ; i<6 ; i++) {
//	
//	ps.setString(1, values[i]);
//	ps.setString(2, "6");
//	ps.setString(3, account_type[i]);
//	ps.setInt(4, branchId);
//	ps.executeUpdate();
//	
//}




	

