package hrm_package;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
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.commons.net.ntp.TimeStamp;
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.quartz.utils.counter.sampled.TimeStampedCounterValue;

import main_package.DataBaseConnection;
import main_package.DataBaseMethods;
import main_package.DataBaseMethodsSalary;
import main_package.NachExcelInfo;
import other_package.ReportInfo;
import report_package.CibilInfo;


@ManagedBean(name="defineHolidayBean")
@ViewScoped
public class DefineHolidayBean implements Serializable
{
	private static final long serialVersionUID = 1L;
	Date date= new Date();
	Date tillDate = new Date();
	String description;
	boolean ontime = false;
	int totalHoliday;
	ArrayList<ReportInfo> holidayList;
	DataBaseMethodsSalary obj =new DataBaseMethodsSalary();
	public DefineHolidayBean()
	{
		Connection con= DataBaseConnection.JAVAConnection();
		holidayList=obj.allHolidayList(con);
		totalHoliday=holidayList.size();
		try {
			con.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	public void defineHoliday()
	{   		
		 Calendar cal = Calendar.getInstance();
	        cal.setTime(date);

	        int dayOfWeek = cal.get(Calendar.DAY_OF_WEEK);

	        if (dayOfWeek == Calendar.SUNDAY) {
	        	Connection con= DataBaseConnection.JAVAConnection();
	    		int j=obj.checkDuplicateHoliday(date, con);
	    		if(j>=1)
	    		{
	    			date=null;
	    			FacesContext.getCurrentInstance().addMessage(null,new FacesMessage(" Date Maintioned Already Holiday!!! "));
	    		}
	    		else
	    		{   
	    			int i=obj.addHoliday(date,tillDate,description,con);
	    			
	    			if(i==1)
	    			{
	    				FacesContext.getCurrentInstance().addMessage(null,new FacesMessage("Holiday Defined SucessFully"));
	    				holidayList=obj.allHolidayList(con);
	    				date=null;
	    				totalHoliday=holidayList.size();
	    			}
	    			else
	    			{
	    				FacesContext.getCurrentInstance().addMessage(null,new FacesMessage("Holiday Defined Sucessfully...."));
	    			}
	    		}
	    		try {
	    			
	    			con.close();
	    		} catch (Exception e) {

                e.printStackTrace();
                
	    		}

	        	
	        } else {
	        	
	        	FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(" Please Select Date Only when Sunday ..!!!! "));
	        
	        }
		
		
		
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	public void expense() {
		
		System.out.println("Date value :"+date);
		
//		String[] item = {"Alwar Barnch_Salary", "Hisar Branch_Salary",
//				"Ac Service Hisar Branch", "Airtel Wifi Alwar",  "Alwar Office Exp",
//				 "Alwar Office Rent",  "Alwar Stationary Exp", 
//				"Bank Charges", "CA Professional Fee", "Cleaning Exp", "Commision to DSA",
//				"Computer Repair", "Courier Charges Hisar", "Courier Exp Alwar", "Diwali Exp Alwar", 
//				"Diwali Exp Hisar Branch", "Donation Alwar Branch", "Donation Hisar Branch", "Dsc Exp", 
//				"Electicity Bill_Alwar", "Electicity Exp Hisar", "Electricity Repair Alwar", "EPF Employer Share",
//				"Equifax", "Escrow Bank Charges", "ESI Employer Share", "Flex Hisar Branch", "Fuel Alwar Branch",
//				"Fuel Hisar Branch", "Hisar Office Rent", "Holi Exp Alwar", "Hotel Exp", "Kavita Gupta CS", "Lohri Exp Hisar Branch",
//				"Manisha Peaon", "Mobile Exp", "Mobile Recharge", "Nach Charges", "Office Exp Alwar Branch",
//				"Office Exp Hisar Branch", "Office Inugration Exp_Alwar Branch", "Office Paint_Alwar", "Professional Fee PF ESI", 
//				"Refershment_Hisar Branch", "Rent Delhi Office", "Stationary and Staff Walefare Aex Alwar Branch",
//				"Stationery Exp Hisar Branch", "Tea Exp Alwar Branch", "Travelling Exp Alwar Branch", "Travelling Exp Hisar", 
//				"Water Exp Alwar", "Water Exp Hisar Branch"};
		
		
		String[] item = {
			   
				"Forecloser Charges",
			    "Alwar Branch Application Fee",
			    "Application Fee",
			    "Bounce Charges",			    "Broken Period Interest",
			    "Interest on Loan",
			    "Mis",			    "Penel Charges",
			    "Processing Fee"
	
		};			   

			 

			
			 
			    

			   

		
		
         
		String category = "Other_Dr"; 
		String payment_mode = "transfer"; 
		
//		double[] expenseAmounts = {1234548.00, 1295347.00, 600.00,
//				 3128.00,  20824.00,  127800.00,
//				1665.00,  30780.21,29500.00, 1817.00, 14700.00, 
//				10000.00, 832.00, 440.00, 4490.00, 25170.00, 250.00, 3000.00, 2740.00, 26477.00, 
//				50819.00, 1500.00, 99954.00, 10000.00, 116.82, 21925.00, 9300.00, 36000.00, 38700.00,
//				240345.00, 350.00, 6484.00, 121380.00, 1230.00, 6000.00, 4747.00, 3225.00, 13754.42, 
//				1949.00, 19456.86, 13338.00, 21400.00, 10000.00, 53209.00, 120000.00, 5610.00, 22136.00, 
//				21449.00, 20457.00, 18602.00, 4400.00, 10080.00};
		
		
		double[] expenseAmounts = {
			    2927.00,
			  
			    143000.00,
	
			    80480.00,
		
			    82250.00,
			
			    124768.00,
			 
			    3097882.00,
		
			    59956.10,
			
			    157330.00,
			
			    1374400.00
			};


		String bank_name ="N/A";
		String bank_branch = "N/A";
		String description = "";
		String status = "active";
		
		String no_of_item ="1";
	
		String item_rate="0";
		String other_table_id="0";
		String branch_id="3";
		
//		String expensecolumns = "expense_date, category, payment_mode, amount, chq_dd_no, chq_dd_date,"
//				+ " bank_name, bank_branch, description, item, status, chequeStatus, no_of_item,"
//				+ " voucher_no, item_rate, entry_date, other_table_id, branch_id, loan_no, loan_type";
		
		String incomeColumns = "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";
		
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
//		
//			String query = "INSERT INTO expense_table(" +
//				    "expense_date, category, payment_mode, amount, chq_dd_no, chq_dd_date, " +
//				    "bank_name, bank_branch, description, item, status, chequeStatus, no_of_item, " +
//				    "voucher_no, item_rate, other_table_id, branch_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
//			
			
			String query = "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, dsa_code, branch_id" +
				") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

			
//			String query = "insert into group_head (group_head_name, group_head_type, group_type)values(?,?,?)";


			try(PreparedStatement ps = conn.prepareStatement(query)){
				
                    for(int i = 0; i < item.length; i++) {
					
                  	ps.setDate(1, new java.sql.Date(date.getTime()));
					ps.setString(2, category);
					ps.setString(3, payment_mode);
					ps.setDouble(4, expenseAmounts[i]);
					ps.setString(5, "");
					ps.setDate(6 , new java.sql.Date(date.getTime()));
					ps.setString(7, bank_name);
					ps.setString(8, bank_branch);
					ps.setString(9, "");
					ps.setString(10, item[i]);
					ps.setInt(11, 0);
					ps.setString(12, "N/A");
				
					ps.setString(13, "cleared");
					ps.setString(14, no_of_item);
					ps.setString(15, "");
					ps.setString(16, "");
					ps.setString(17, "0");
					ps.setString(18, "");
					ps.setString(19, "");
					ps.setString(20, item_rate);
					ps.setString(21, "active");
					ps.setString(22, "");
					ps.setString(23, "3");
					
					
					
					ps.executeUpdate();	
					
					
                   
                    
				}
				

				
				
				
				
			}catch(Exception e) {
				
				e.printStackTrace();
				
			}
			
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
		
	}
		
	
	
	
	public void insertExcel(){
		
		String[] shareholderNos = {"10200347","10200348","10200349","10200350","10200351","10200352","10200353",
				"10200354","10200355","10200356","10200357","10200358","10200359","10200360","10200361",
				"10200362","10200363","10200364","10200365","10200366","10200367","10200368","10200370",
				"10200371","10200372","10200373","10200374","10200375","10200376","10200379","10200382",
				"10200383","10200385","10200390","10200391","10200392","10200393","10200395","10200396",
				"10200398","10200401","10200402","10200403","10200404","10200405","10200407","10200408"};
            
		String loanType = "personalloan";
		
		
		String[] loanNumber = {"PL-10200347","PL-10200348","PL-10200349","PL-10200350","PL-10200351",
				"PL-10200352","PL-10200353","PL-10200354","PL-10200355","PL-10200356","PL-10200357",
				"PL-10200358","PL-10200359","PL-10200360","PL-10200361","PL-10200362","PL-10200363",
				"PL-10200364","PL-10200365","PL-10200366","PL-10200367","PL-10200368","PL-10200370",
				"PL-10200371","PL-10200372","PL-10200373","PL-10200374","PL-10200375","PL-10200376",
				"PL-10200379","PL-10200382","PL-10200383","PL-10200385","PL-10200390","PL-10200391",
				"PL-10200392","PL-10200393","PL-10200395","PL-10200396","PL-10200398","PL-10200401",
				"PL-10200402","PL-10200403","PL-10200404","PL-10200405","PL-10200407","PL-10200408"};

		
		
		double[] disbursedAmounts = {56130, 57580, 65711, 47017, 95564, 56517, 93792, 47057, 65880, 37833,
				42351, 66218, 56807, 75807, 56710, 94597, 56855, 57145, 56952, 57193, 57242, 47741, 65316,
				47782, 76258, 66726, 56323, 51675, 75162, 47097, 104056, 75613, 140929, 66556, 76644, 76258, 
				28597, 95725, 47862, 95806, 122139, 46976, 96128, 65541, 28138, 93792, 94517};
		
		double[] differenceAmounts = {3870.00, 2420.00, 4289.00, 2983.00, 4436.00, 3483.00, 6208.00, 2943.00, 4120.00, 2167.00, 2649.00, 3780.00, 3193.00, 4193.00, 3290.00, 5403.00, 3145.00, 2855.00, 3048.00, 2807.00, 2758.00, 2259.00, 4684.00, 2218.00, 3742.00, 3274.00, 3687.00, 3325.00, 4840.00, 2903.00, 5944.00, 4387.00, 9059.00, 3444.00, 3366.00, 3742.00, 1403.00, 4275.00, 2138.00, 4200.00, 7861.00, 3024.00, 3872.00, 4460.00, 1862.00, 6208.00, 5483.00};

		String paymentMode="NEFT";
		String bankName = "1";
		String bankBranch = "ALWAR";
		String chequeNumber = null; 
		
		String[] date={"2025-05-22", "2025-05-22", "2025-05-26", "2025-05-26", "2025-05-27", "2025-05-28", "2025-05-28", "2025-05-30", "2025-05-30", "2025-05-31", "2025-05-30", "2025-05-31", "2025-06-04", "2025-06-05", "2025-06-04", "2025-06-05", "2025-06-05", "2025-06-11", "2025-06-09", "2025-06-12", "2025-06-13", "2025-06-14", "2025-06-17", "2025-06-17", "2025-06-17", "2025-06-17", "2025-06-24", "2025-06-25", "2025-06-25", "2025-06-28", "2025-07-03", "2025-07-04", "2025-07-07", "2025-07-09", "2025-07-18", "2025-07-12", "2025-07-12", "2025-07-15", "2025-07-17", "2025-07-18", "2025-07-22", "2025-07-22", "2025-07-22", "2025-07-24", "2025-07-24", "2025-07-24", "2025-07-30"};	
	    
		
	        
	        System.out.println("Converted Date: " + date);
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
		
			String query = "INSERT INTO debit_loan (shareholderno, loanType, loanNo, amount, paymentMode, bankName, bankBranch, "
	                 + "chequeNo, chequeDate, status, date, branch_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			
			try(PreparedStatement ps = conn.prepareStatement(query)){
				
				 for(int i = 0; i < loanNumber.length; i++) {
						
	                  	ps.setString(1, shareholderNos[i]);
						ps.setString(2, loanType);
						ps.setString(3,  loanNumber[i]);
						ps.setDouble(4, disbursedAmounts[i]);
						ps.setString(5,  paymentMode);
						ps.setString(6 , bankName);
						ps.setString(7,  bankBranch);
						ps.setString(8,  chequeNumber);
						String dateStr = date[i];
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						Date finDate = sdf.parse(dateStr);
						Timestamp tt = new Timestamp(finDate.getTime());
						
						ps.setTimestamp(9,  tt);
						ps.setString(10, "cleared");
						ps.setTimestamp(11,  tt);
					    ps.setString(12, "3");
					    ps.executeUpdate();
			            
					    ontime = true;
					    insertExcel2(i);
				 }	
				
			}catch(Exception e) {
				
				e.printStackTrace();
				
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
		
		
		
		
	}
	
	
	
	
	public void insertExcel2(int i){
		
		String[] shareholderNos = {"10200347","10200348","10200349","10200350","10200351","10200352","10200353",
				"10200354","10200355","10200356","10200357","10200358","10200359","10200360","10200361",
				"10200362","10200363","10200364","10200365","10200366","10200367","10200368","10200370",
				"10200371","10200372","10200373","10200374","10200375","10200376","10200379","10200382",
				"10200383","10200385","10200390","10200391","10200392","10200393","10200395","10200396",
				"10200398","10200401","10200402","10200403","10200404","10200405","10200407","10200408"};
            
		String loanType = "personalloan";
		
		
		String[] loanNumber = {"PL-10200347","PL-10200348","PL-10200349","PL-10200350","PL-10200351",
				"PL-10200352","PL-10200353","PL-10200354","PL-10200355","PL-10200356","PL-10200357",
				"PL-10200358","PL-10200359","PL-10200360","PL-10200361","PL-10200362","PL-10200363",
				"PL-10200364","PL-10200365","PL-10200366","PL-10200367","PL-10200368","PL-10200370",
				"PL-10200371","PL-10200372","PL-10200373","PL-10200374","PL-10200375","PL-10200376",
				"PL-10200379","PL-10200382","PL-10200383","PL-10200385","PL-10200390","PL-10200391",
				"PL-10200392","PL-10200393","PL-10200395","PL-10200396","PL-10200398","PL-10200401",
				"PL-10200402","PL-10200403","PL-10200404","PL-10200405","PL-10200407","PL-10200408"};

		
		
		double[] disbursedAmounts = {56130, 57580, 65711, 47017, 95564, 56517, 93792, 47057, 65880, 37833,
				42351, 66218, 56807, 75807, 56710, 94597, 56855, 57145, 56952, 57193, 57242, 47741, 65316,
				47782, 76258, 66726, 56323, 51675, 75162, 47097, 104056, 75613, 140929, 66556, 76644, 76258, 
				28597, 95725, 47862, 95806, 122139, 46976, 96128, 65541, 28138, 93792, 94517};
		
		double[] differenceAmounts = {3870.00, 2420.00, 4289.00, 2983.00, 4436.00, 3483.00, 6208.00, 2943.00, 4120.00, 2167.00, 2649.00, 3780.00, 3193.00, 4193.00, 3290.00, 5403.00, 3145.00, 2855.00, 3048.00, 2807.00, 2758.00, 2259.00, 4684.00, 2218.00, 3742.00, 3274.00, 3687.00, 3325.00, 4840.00, 2903.00, 5944.00, 4387.00, 9059.00, 3444.00, 3366.00, 3742.00, 1403.00, 4275.00, 2138.00, 4200.00, 7861.00, 3024.00, 3872.00, 4460.00, 1862.00, 6208.00, 5483.00};

		String paymentMode="NEFT";
		String bankName = "1";
		String bankBranch = "ALWAR";
		String chequeNumber = null; 
		
		String[] date={"2025-05-22", "2025-05-22", "2025-05-26", "2025-05-26", "2025-05-27", "2025-05-28", "2025-05-28", "2025-05-30", "2025-05-30", "2025-05-31", "2025-05-30", "2025-05-31", "2025-06-04", "2025-06-05", "2025-06-04", "2025-06-05", "2025-06-05", "2025-06-11", "2025-06-09", "2025-06-12", "2025-06-13", "2025-06-14", "2025-06-17", "2025-06-17", "2025-06-17", "2025-06-17", "2025-06-24", "2025-06-25", "2025-06-25", "2025-06-28", "2025-07-03", "2025-07-04", "2025-07-07", "2025-07-09", "2025-07-18", "2025-07-12", "2025-07-12", "2025-07-15", "2025-07-17", "2025-07-18", "2025-07-22", "2025-07-22", "2025-07-22", "2025-07-24", "2025-07-24", "2025-07-24", "2025-07-30"};	
	    
		
	        
	        System.out.println("Converted Date: " + date);
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
		
			String query = "INSERT INTO debit_loan (shareholderno, loanType, loanNo, amount, paymentMode, bankName, bankBranch, "
	                 + "chequeNo, chequeDate, status, date, branch_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			
			try(PreparedStatement ps = conn.prepareStatement(query)){
			
				
						
	                  	ps.setString(1, shareholderNos[i]);
						ps.setString(2, loanType);
						ps.setString(3,  loanNumber[i]);
						ps.setDouble(4, differenceAmounts[i]);
						ps.setString(5, "CASH");
						ps.setString(6 , bankName);
						ps.setString(7,  bankBranch);
						ps.setString(8,  chequeNumber);
						String dateStr = date[i];
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						Date finDate = sdf.parse(dateStr);
						Timestamp tt = new Timestamp(finDate.getTime());
						
						ps.setTimestamp(9,  tt);
						ps.setString(10, "cleared");
						ps.setTimestamp(11,  tt);
					    ps.setString(12, "3");
					   
					   
					    ps.executeUpdate();
					    
				
				
			}catch(Exception e) {
				
				e.printStackTrace();
				
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
		
		
		
		
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public int getTotalHoliday() {
		return totalHoliday;
	}
	public void setTotalHoliday(int totalHoliday) {
		this.totalHoliday = totalHoliday;
	}

	public ArrayList<ReportInfo> getHolidayList() {
		return holidayList;
	}

	public void setHolidayList(ArrayList<ReportInfo> holidayList) {
		this.holidayList = holidayList;
	}

	public Date getTillDate() {
		return tillDate;
	}

	public void setTillDate(Date tillDate) {
		this.tillDate = tillDate;
	}

	
	
}
