package main_package;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import javax.faces.model.SelectItem;

import other_package.ReportInfo;

public class DataBaseMethodsSalary implements Serializable
{
	DecimalFormat df=new DecimalFormat("##.##");
	SimpleDateFormat sf=new SimpleDateFormat("dd/MM/yyyy");
	SimpleDateFormat sdfForDb=new SimpleDateFormat("yyyy-MM-dd");   // For Database query
	SimpleDateFormat sdf=new SimpleDateFormat("dd/MM/yyyy");
	
   public String selectQueWithBranch(String query,int branchId)
	{
		
		if(branchId == -1)
		{
			
		}
		else
		{
			if(query.contains("where"))
			{
				query=query+" and branch_id='"+branchId+"'";
			}
			else
			{
				query=query+" where branch_id='"+branchId+"'";
			}
		}
		
		//System.out.println(query);
		return query;
	}
	
	public ArrayList<SelectItem> allMonths()
	{
		ArrayList<SelectItem> list=new ArrayList<>();
		for(int i=1;i<=12;i++)
		{
			list.add(new SelectItem(String.valueOf(i),monthName(i)));
		}
		return list;
	}
	
	public String monthName(int i) 
	{
		String name="";
		if(i==1)
		{
			name="January";
		}
		else if(i==2)
		{
			name="February";
		}
		else if(i==3)
		{
			name="March";
		}
		else if(i==4)
		{
			name="April";
		}
		else if(i==5)
		{
			name="May";
		}
		else if(i==6)
		{
			name="June";
		}
		else if(i==7)
		{
			name="July";
		}
		else if(i==8)
		{
			name="August";
		}
		else if(i==9)
		{
			name="September";
		}
		else if(i==10)
		{
			name="October";
		}
		else if(i==11)
		{
			name="November";
		}
		else if(i==12)
		{
			name="December";
		}
		return name;
	}
	public ArrayList<SelectItem> allYearList() 
	{
		ArrayList<SelectItem> list=new ArrayList<>();
		for(int i=2013;i<=2030;i++)
		{
			list.add(new SelectItem(String.valueOf(i),String.valueOf(i)));
		}
		return list;
	}
	public  int calculateDayInMonth(int month, int year) 
	{
		int flag=0;
		if(year%4==0  )
		{
			if(year%100==0)
			{
				if(year %400 ==0)
				{
					flag=1;
				}
			}
			else
			{
				flag=1;
			}
		}
		int days=31;
		if(month==4 || month==6 || month==9 || month==11)
			days=30;

		else if(month==2)
		{
			if(flag==1)
			{
				days=29;
			}
			else
			{
				days=28;
			}
		}
		return days;
	}
	

	public void calculateSalary(EmployeeInfo emp, int month, int year)
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select * from salary_table where month='"+month+"' and year='"+year+"' and empId='"+emp.getUserName()+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				salaryFromTable(rr,emp,month,year);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	private void salaryFromTable(ResultSet rr,EmployeeInfo emp,int month, int year) 
	{
		try
		{
			emp.setSalary(rr.getDouble("empSalary"));
			emp.setLeaveAllow(rr.getString("leaveAllowed"));
			double salaryDay=0,totalSalaryDay=0,grossSalaryDay=0,finalSalaryDay=0,finalSalary=0;
			salaryDay=totalSalaryDay=grossSalaryDay=finalSalaryDay=0;
			SalaryInfo salInfo=new SalaryInfo();
			salInfo.setFullDay(rr.getDouble("fullDay"));
			salInfo.setHalfDay(rr.getDouble("halfDay"));
			salInfo.setAbsent(rr.getDouble("absent"));
			salInfo.setHoliday(rr.getDouble("holiday"));
			salInfo.setLeave(rr.getDouble("leaveTaken"));
			salInfo.setP_f(rr.getDouble("p_f_day"));
			salInfo.setLeaveAllowed(Double.parseDouble(emp.getLeaveAllow()));
			salInfo.setSuddenLeave(rr.getDouble("sudden"));
			salInfo.setMonth(month);
			salInfo.setYear(year);
			salInfo.setNMLF(rr.getDouble("nmlf"));
			salInfo.setLMLF(rr.getDouble("lmlf"));
			salInfo.setSalaryStatus(rr.getString("status"));
			salInfo.setSuddenPenalty(rr.getDouble("suddenPenalty"));
			salInfo.setAbsentPenalty(rr.getDouble("absentPenalty"));
			salInfo.setRelaxation(rr.getDouble("relaxation"));
			salInfo.setExtraWork(rr.getDouble("extraWork"));
			salInfo.setComment(rr.getString("comment"));
			salInfo.setAllowance(rr.getDouble("allowance"));
			
			salaryDay=salInfo.getFullDay()+salInfo.getHalfDay()/2 + salInfo.getLMLF() + salInfo.getHoliday() + salInfo.getLeave() + salInfo.getLeaveAllowed();
			totalSalaryDay= salaryDay-salInfo.getSuddenPenalty()-salInfo.getAbsentPenalty();
			grossSalaryDay=totalSalaryDay+salInfo.getRelaxation()+salInfo.getExtraWork();
			finalSalaryDay=grossSalaryDay-salInfo.getNMLF();
			
			salInfo.setSalaryDay(salaryDay);
			salInfo.setTotalSalaryDay(totalSalaryDay);
			salInfo.setGrossSalaryDay(grossSalaryDay);
			salInfo.setFinalSalaryDay(finalSalaryDay);
			
			finalSalary=finalSalaryDay * emp.getSalary()/30;
			
			finalSalary=(double) Math.round(finalSalary * 100) / 100;
			salInfo.setSalary(finalSalary);
			int salaryfinal=(int) ((int)finalSalary+salInfo.getAllowance());
			salInfo.setFinalSalary((double)salaryfinal);
			emp.setSalaryList(salInfo);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	
	
	public void entryInSalaryTable(EmployeeInfo emp)
    {   
        try(Connection conn=DataBaseConnection.JAVAConnection())
        {
            String query="",empId="";
            int month=0,year=0;
           
            month=emp.getSalaryList().getMonth();
            year=emp.getSalaryList().getYear();
            empId=emp.getUname();
           
            query="insert into salary_table(empId, month, year, fullDay, halfDay, leaveTaken, sudden, absent, holiday, lmlf,"
            		+ " suddenPenalty, absentPenalty, nmlf, extraWork, relaxation, finalDays, salaryAmount, comment, "
            		+ "allowance, empSalary, leaveAllowed, p_f_day, esi, tds, pf,branch_id) "
            		+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
           
            PreparedStatement st=conn.prepareStatement(query);
            st.setString(1,empId);
            st.setInt(2,month);
            st.setInt(3,year);
            st.setDouble(4,emp.getSalaryList().getFullDay());
			st.setDouble(5,emp.getSalaryList().getHalfDay());
			st.setDouble(6,emp.getSalaryList().getLeave());
			st.setDouble(7,emp.getSalaryList().getSuddenLeave());
			st.setDouble(8,emp.getSalaryList().getAbsent());
			st.setDouble(9,emp.getSalaryList().getHoliday());
			st.setDouble(10,emp.getSalaryList().getLMLF());
			
			st.setDouble(11,emp.getSalaryList().getSuddenPenalty());
			st.setDouble(12,emp.getSalaryList().getAbsentPenalty());
			st.setDouble(13,emp.getSalaryList().getNMLF());
			st.setDouble(14,emp.getSalaryList().getExtraWork());
			st.setDouble(15,emp.getSalaryList().getRelaxation());
			
			st.setDouble(16,emp.getSalaryList().getFinalSalaryDay());
			st.setDouble(17,emp.getSalaryList().getFinalSalary());
			st.setString(18,emp.getSalaryList().getComment());
			st.setDouble(19,emp.getSalaryList().getAllowance());
			st.setDouble(20, emp.getSalaryList().getSalary());
			st.setDouble(21, emp.getSalaryList().getLeaveAllowed());
			st.setDouble(22, 0);
			st.setDouble(23, emp.getSalaryList().getEsi());
			st.setDouble(24, emp.getSalaryList().getTds());
			st.setDouble(25, emp.getSalaryList().getPf());
			st.setString(26, emp.getSalaryList().getEmpBranchId());
            st.executeUpdate();
           
            Timestamp ss=new Timestamp(new Date().getTime());
            ss.setMonth(month);
            ss.setDate(1);
            entryInSalaryLedger(empId, month,year,emp.getSalaryList().getFinalSalary(), "Credit","0",ss,Integer.valueOf(emp.getSalaryList().getEmpBranchId()));
            updateStatusInSalaryTable(empId, month, year);
            new DataBaseMethods().dataInsertInQueryTable(String.valueOf(st), conn);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
	
	public int updateSalaryTable(EmployeeInfo emp)
	{	
		int i=0;String empId="";
		int month=0,year=0;
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement stt=conn.createStatement();
			
			month=emp.getSalaryList().getMonth();
			year=emp.getSalaryList().getYear();
			empId=emp.getUname();
			
			String query="select * from salary_table where month='"+month+"' and year='"+year+"' and empId='"+empId+"'";
			ResultSet rr=stt.executeQuery(query);
			if(!rr.next())
			{
				entryInSalaryTable(emp);
			}
			
			query="update salary_table set fullDay=?, halfDay=?, leaveTaken=?, sudden=?, absent=?, holiday=?, lmlf=?,"
					+ " suddenPenalty=?, absentPenalty=?, nmlf=?, extraWork=?, relaxation=?, finalDays=?, salaryAmount=?, comment=?,"
					+ " allowance=?, empSalary=?, leaveAllowed=?, p_f_day=?, esi=?, tds=?, pf=? where month='"+month+"' and year='"+year+"' and empId='"+empId+"'";
			PreparedStatement st=conn.prepareStatement(query);
			st.setDouble(1,emp.getSalaryList().getFullDay());
			st.setDouble(2,emp.getSalaryList().getHalfDay());
			st.setDouble(3,emp.getSalaryList().getLeave());
			st.setDouble(4,emp.getSalaryList().getSuddenLeave());
			st.setDouble(5,emp.getSalaryList().getAbsent());
			st.setDouble(6,emp.getSalaryList().getHoliday());
			st.setDouble(7,emp.getSalaryList().getLMLF());
			
			st.setDouble(8,emp.getSalaryList().getSuddenPenalty());
			st.setDouble(9,emp.getSalaryList().getAbsentPenalty());
			st.setDouble(10,emp.getSalaryList().getNMLF());
			st.setDouble(11,emp.getSalaryList().getExtraWork());
			st.setDouble(12,emp.getSalaryList().getRelaxation());
			
			st.setDouble(13,emp.getSalaryList().getFinalSalaryDay());
			st.setDouble(14,emp.getSalaryList().getFinalSalary());
			st.setString(15,emp.getSalaryList().getComment());
			st.setDouble(16,emp.getSalaryList().getAllowance());
			st.setDouble(17, emp.getSalaryList().getSalary());
			st.setDouble(18, emp.getSalaryList().getLeaveAllowed());
			st.setDouble(19, 0);
			st.setDouble(20, emp.getSalaryList().getEsi());
			st.setDouble(21, emp.getSalaryList().getTds());
			st.setDouble(22, emp.getSalaryList().getPf());
			i+=st.executeUpdate();
			
			Timestamp ss=new Timestamp(new Date().getTime());
            ss.setMonth(month);
            ss.setDate(1);
			updateSalaryLedger(empId, month, year, emp.getSalaryList().getFinalSalary(), "Credit",ss);
			updateStatusInSalaryTable(empId, month, year);
			new DataBaseMethods().dataInsertInQueryTable(String.valueOf(st), conn);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return i;
	}
	
	public void entryInSalaryLedger(String empId,int month,int year,double amount,String type,String expenseId, Date expanseDate,int empBranchId)
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Timestamp ss=new Timestamp(expanseDate.getTime());
		    ss.setHours(0);
		    ss.setNanos(0);
		    ss.setMinutes(0);
		    ss.setSeconds(0);
			Statement st=conn.createStatement();
			String query="insert into salary_ledger (empId, month, year, amount, type,expenseId,payDate,branch_id) values('"+empId+"','"+month+"','"+year+"','"+amount+"','"+type+"','"+expenseId+"','"+ss+"','"+empBranchId+"')";
			int i=st.executeUpdate(query);
			if(i==1)
			{
				updateStatusInSalaryTable(empId,month,year);
				new DataBaseMethods().dataInsertInQueryTable(query, conn);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	  
	public void updateStatusInSalaryTable(String empId, int month, int year) 
	{
		double salaryPaid=0,amount=0;
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			Statement st1=conn.createStatement();
			String query="";
			query="select sum(amount) as total from salary_ledger where type='Debit' and empId='"+empId+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				salaryPaid=rr.getDouble("total");
			}
			
			query="select * from salary_table where empId='"+empId+"' and status='Not Paid'";
			rr=st.executeQuery(query);
			while(rr.next())
			{
				amount=rr.getDouble("salaryAmount");
				if(salaryPaid>amount)
				{
					query="update salary_table set status='Paid' where id='"+rr.getString("id")+"'";
					st1.executeUpdate(query);
					new DataBaseMethods().dataInsertInQueryTable(query, conn);
				}
				salaryPaid=salaryPaid-amount;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	public void updateSalaryLedger(String empId,int month,int year,double amount,String type, Timestamp ss)
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="update salary_ledger set amount ='"+amount+"',payDate='"+ss+"' where empId='"+empId+"' and month='"+month+"' and year='"+year+"' and type='"+type+"' ";
			st.executeUpdate(query);
			new DataBaseMethods().dataInsertInQueryTable(query, conn);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public void deleteFromSalaryLedger(String expenseId)
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="delete from salary_ledger where expenseId='"+expenseId+"' ";
			st.executeUpdate(query);
			new DataBaseMethods().dataInsertInQueryTable(query, conn);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	public String calculateSalaryAmountToBePaid(String empId)
	{
		double salaryPaid=0,totalSalary=0,amount=0;
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select sum(amount) as total from salary_ledger where type='Debit' and empId='"+empId+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				salaryPaid=rr.getDouble("total");
			}
			
			query="select sum(amount) as total from salary_ledger where type='Credit' and empId='"+empId+"'";
			rr=st.executeQuery(query);
			if(rr.next())
			{
				totalSalary=rr.getDouble("total");
			}
			amount=totalSalary-salaryPaid;
			amount=(double) Math.round(amount * 100) / 100;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return String.valueOf(amount);
	}


	public boolean checkSalaryOfMonthAndYear(String selectedEmp, String month,String year) 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select * from salary_table where empId='"+selectedEmp+"' and month='"+month+"' and year='"+year+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				return false;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return true;
	}

	public boolean checkSalaryOfParticularMonth(String selectedMonth,String selectedYear, String selectedEmployee) 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select * from salary_table where empId='"+selectedEmployee+"' and month='"+selectedMonth+"' and year='"+selectedYear+"'";
			ResultSet rr=st.executeQuery(query);
			System.out.println("previous month salary : " + query);
			if(rr.next())
			{
				return true;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return false;
	}

	

	public SalaryInfo particularEmployeeSalaryDescription(String employeeUserName, String selectedMonth, String selectedYear) 
	{
		SalaryInfo salInfo=new SalaryInfo();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select * from salary_table where month='"+selectedMonth+"' and year='"+selectedYear+"' and empId='"+employeeUserName+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				double salary=rr.getDouble("empSalary");
				double salaryDay=0,totalSalaryDay=0,grossSalaryDay=0,finalSalaryDay=0,finalSalary=0;
				salaryDay=totalSalaryDay=grossSalaryDay=finalSalaryDay=0;
				
				salInfo.setFullDay(rr.getDouble("fullDay"));
				salInfo.setHalfDay(rr.getDouble("halfDay"));
				salInfo.setAbsent(rr.getDouble("absent"));
				salInfo.setHoliday(rr.getDouble("holiday"));
				salInfo.setLeave(rr.getDouble("leaveTaken"));
				salInfo.setLeaveAllowed(rr.getDouble("leaveAllowed"));
				salInfo.setP_f(rr.getDouble("p_f_day"));
				salInfo.setSuddenLeave(rr.getDouble("sudden"));
				salInfo.setMonth(Integer.parseInt(selectedMonth));
				salInfo.setYear(Integer.parseInt(selectedYear));
				salInfo.setNMLF(rr.getDouble("nmlf"));
				salInfo.setLMLF(rr.getDouble("lmlf"));
				salInfo.setSalaryStatus(rr.getString("status"));
				salInfo.setSuddenPenalty(rr.getDouble("suddenPenalty"));
				salInfo.setAbsentPenalty(rr.getDouble("absentPenalty"));
				salInfo.setRelaxation(rr.getDouble("relaxation"));
				salInfo.setExtraWork(rr.getDouble("extraWork"));
				salInfo.setComment(rr.getString("comment"));
				salInfo.setAllowance(rr.getDouble("allowance"));
				
				salaryDay=salInfo.getFullDay()+salInfo.getHalfDay()/2 + salInfo.getLMLF() + salInfo.getHoliday() + salInfo.getLeave() + salInfo.getLeaveAllowed();
				totalSalaryDay= salaryDay-salInfo.getSuddenPenalty()-salInfo.getAbsentPenalty();
				grossSalaryDay=totalSalaryDay+salInfo.getRelaxation()+salInfo.getExtraWork();
				finalSalaryDay=grossSalaryDay-salInfo.getNMLF();
				
				salInfo.setSalaryDay(salaryDay);
				salInfo.setTotalSalaryDay(totalSalaryDay);
				salInfo.setGrossSalaryDay(grossSalaryDay);
				salInfo.setFinalSalaryDay(finalSalaryDay);
				
				finalSalary=finalSalaryDay *salary/30;
				
				finalSalary=(double) Math.round(finalSalary * 100) / 100;
				salInfo.setSalary(finalSalary);
				salInfo.setFinalSalary(finalSalary+salInfo.getAllowance());
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return salInfo;
	}

	public boolean checkSalaryPreparation(String selectedEmployee, String selectedMonth, String selectedYear)
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select id,year from salary_table where empId='"+selectedEmployee+"' order by id desc";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				if(rr.getDouble("year")==Double.parseDouble(selectedYear))
				{
					Statement stt=conn.createStatement();
					String query1="select id,month from salary_table where empId='"+selectedEmployee+"' and (month<'"+selectedMonth+"' or month>'"+selectedMonth+"')  order by id desc";
					ResultSet rs=stt.executeQuery(query1);
					if(rs.next())
					{
						return true;
					}
				}
				else if(rr.getDouble("year")>Double.parseDouble(selectedYear))
				{
					return true;
				}
				else
				{
					return false;
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return false;
	}

	public SalaryInfo viewPreparedSalary(String selectedEmployee,
			String selectedMonth, String selectedYear)
	{
		SalaryInfo salInfo=new SalaryInfo();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select * from salary_table where empId='"+selectedEmployee+"' and month='"+selectedMonth+"' and year='"+selectedYear+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				salInfo.setFullDay(rr.getDouble("fullDay"));
				salInfo.setHalfDay(rr.getDouble("halfDay"));
				salInfo.setLeave(rr.getDouble("leaveTaken"));
				salInfo.setSuddenLeave(rr.getDouble("sudden"));
				salInfo.setAbsent(rr.getDouble("absent"));
				salInfo.setHoliday(rr.getDouble("holiday"));
				salInfo.setLMLF(rr.getDouble("lmlf"));
				salInfo.setSuddenPenalty(rr.getDouble("suddenPenalty"));
				salInfo.setAbsentPenalty(rr.getDouble("absentPenalty"));
				salInfo.setNMLF(rr.getDouble("nmlf"));
				salInfo.setRelaxation(rr.getDouble("relaxation"));
				salInfo.setExtraWork(rr.getDouble("extraWork"));
				salInfo.setFinalSalaryDay(rr.getDouble("finalDays"));
				salInfo.setFinalSalary(rr.getDouble("salaryAmount"));
				salInfo.setSalary(rr.getDouble("empSalary"));
				salInfo.setP_f(rr.getDouble("p_f_day"));
				salInfo.setLeaveAllowed(rr.getDouble("leaveAllowed"));
				salInfo.setComment(rr.getString("comment"));
				salInfo.setAllowance(rr.getDouble("allowance"));
				salInfo.setEsi(rr.getDouble("esi"));
				salInfo.setTds(rr.getDouble("tds"));
				salInfo.setPf(rr.getDouble("pf"));
				
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return salInfo;
	}
	

	public double viewPreparedSalaryAmount(String selectedEmployee, String selectedMonth, String selectedYear)
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select * from salary_table where empId='"+selectedEmployee+"' and month='"+selectedMonth+"' and year='"+selectedYear+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				return rr.getDouble("salaryAmount");
				
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	public boolean checkSalaryOfParticularEmployee(String selectedEmployee)
    {
        try(Connection conn=DataBaseConnection.JAVAConnection())
        {
            Statement st=conn.createStatement();
            String query="select empId from salary_table where empId='"+selectedEmployee+"'";
            ResultSet rr=st.executeQuery(query);
            if(rr.next())
            {
                return true;
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return false;
    }
	
    public String checkSalaryofMonth(String selectedMonth,String selectedYear, String selectedEmployee)
    {
        try(Connection conn=DataBaseConnection.JAVAConnection())
        {
            Statement st=conn.createStatement();
            String query="select month from salary_table where empId='"+selectedEmployee+"'  and year='"+selectedYear+"' order by id desc";
            
            ResultSet rr=st.executeQuery(query);
            if(rr.next())
            {
                return rr.getString("month");
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return "";
    }
    
    public String checkSalaryofYear(String selectedMonth,String selectedYear, String selectedEmployee)
    {
        try(Connection conn=DataBaseConnection.JAVAConnection())
        {
            Statement st=conn.createStatement();
            String query="select year from salary_table where empId='"+selectedEmployee+"'  order by id desc";
            ResultSet rr=st.executeQuery(query);
            if(rr.next())
            {
                return rr.getString("year");
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return "";
    }
    
    public Date checkSalaryofEmployeeUsingRegistrationDate(String selectedEmployee)
    {
        try(Connection conn=DataBaseConnection.JAVAConnection())
        {
            Statement st=conn.createStatement();
            String query="select doj from employee where uname='"+selectedEmployee+"'  and status='active'";
            ResultSet rr=st.executeQuery(query);
            if(rr.next())
            {
                return rr.getDate("doj");
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return null;
    }
    
    public String  monthList(int i)
	{
		String monthName="";
		if(i==1)
		{
			monthName="January";
		}
		else if(i==2)
		{
			monthName="February";
		}
		else if(i==3)
		{
			monthName="March";
		}
		else if(i==4)
		{
			monthName="April";
		}
		else if(i==5)
		{
			monthName="May";
		}
		else if(i==6)
		{
			monthName="June";
		}
		else if(i==7)
		{
			monthName="July";
		}
		else if(i==8)
		{
			monthName="August";
		}
		else if(i==9)
		{
			monthName="September";
		}
		else if(i==10)
		{
			monthName="October";
		}
		else if(i==11)
		{
			monthName="November";
		}
		else if(i==12)
		{
			monthName="December";
		}
		return monthName;
	}
	
	public ArrayList<SelectItem> employeeListForSalary(int branchId,String joinDate)
	{
		ArrayList<SelectItem> list=new ArrayList<>();
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select * from employee where status='active'  ";
			ResultSet rr=st.executeQuery(selectQueWithBranch(query, branchId));
			while(rr.next())
			{
				SelectItem ll=new SelectItem();
				ll.setLabel(rr.getString("name")); 
				ll.setValue(rr.getString("uname"));
				list.add(ll);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	
	public EmployeeInfo employeeInfoByUserName(String userName) 
	{
		EmployeeInfo ll=new EmployeeInfo();
		try(Connection connection=DataBaseConnection.JAVAConnection())
		{
			Statement st= connection.createStatement();
			String query="select * from employee where uname='"+userName+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				ll=new DataBaseMethods().employeeInfoById(rr);
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return ll;
	}

	public  EmployeeInfo employeeInfoById(String id) 
	{
		EmployeeInfo ll=new EmployeeInfo();
		try(Connection connection=DataBaseConnection.JAVAConnection())
		{
			Statement st= connection.createStatement();
			Statement st1= connection.createStatement();
			String query="select * from employee where id='"+id+"'";
			//System.out.println(query);
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				ll.setName(rr.getString("name"));
				ll.setDeptId(rr.getString("department"));
				ll.setDesignation(rr.getString("designation"));
				ll.setId(rr.getString("id"));
				ll.setAccNo(rr.getString("accountNo"));
				ll.setAddress(rr.getString("address"));
				ll.setContact(rr.getString("contact"));
				ll.setContact1(rr.getString("contact1"));
				ll.setDob(rr.getTimestamp("dob"));
				ll.setDobStr(new SimpleDateFormat("dd/MM/yyyy").format(ll.getDob()));
				ll.setImagePath("profilePicture/"+rr.getString("profile"));
				ll.setSalary(rr.getDouble("salary"));
				ll.setStatus(rr.getString("emp_status"));
				ll.setType(rr.getString("type"));
				ll.setUserName(rr.getString("userName"));
				ll.setJoinDate(rr.getTimestamp("joiningDate"));
				ll.setStartTime(rr.getTime("startTime"));
				ll.setEndTime(rr.getTime("endTime"));
				ll.setSecurityAmount(rr.getDouble("securityAmt"));
				ll.setJoinDtStr(new SimpleDateFormat("dd/MM/yyyy").format(ll.getJoinDate()));
				
				ll.setBankName(rr.getString("bank_name"));
				ll.setBankAccountNo(rr.getString("bank_account_no"));
				ll.setBankBranch(rr.getString("bank_branch"));
				ll.setBankIfsc(rr.getString("bank_ifsc"));
				ll.setPfAcc(rr.getString("pf_acc"));
				ll.setUan(rr.getString("uan"));
				ll.setPanCard(rr.getString("pan_card"));
				
				
//				String query1="select * from hrm_policy where empId='"+ll.getId()+"'";
//				ResultSet rr1=st1.executeQuery(query1);
//				if(rr1.next())
//				{
//					ll.setLeaveAllow(rr1.getString("perMonth"));
//				}
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return ll;
	}


	public String employeeNameById(String id) 
	{
		try(Connection connection=DataBaseConnection.JAVAConnection())
		{
			Statement st= connection.createStatement();
			String query="select * from employee where uname='"+id+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				return rr.getString("name");
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return "";
	}
	
	public String employeeNameByuserName(String id) 
	{
		try(Connection connection=DataBaseConnection.JAVAConnection())
		{
			Statement st= connection.createStatement();
			String query="select name from employee where uname='"+id+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				return rr.getString("name");
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return "";
	}
	
	
	
	
	public ArrayList<SelectItem> allEmployeeList(int branchId,Connection conn)
	{
		ArrayList<SelectItem> list=new ArrayList<>();
		Statement st=null;
		ResultSet rr=null;

		
		try
		{    
			 st=conn.createStatement();
			 String query;
			 if (branchId == -1) {
				     query = "SELECT * FROM employee ORDER BY name";
				}
			 else {
					 query="select * from employee  where branch_id='"+branchId+"' order by name";

			 }
			 rr=st.executeQuery(query);
			while(rr.next())
			{
				SelectItem ll=new SelectItem();
				ll.setLabel(rr.getString("name"));
				ll.setValue(rr.getString("uname"));
				list.add(ll);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return list;
	}

	public double checkNMLFOfLastMonth(String tempMonth, String tempYear,
			String selectedEmployee) 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String query="select nmlf from salary_table where empId='"+selectedEmployee+"' and month='"+tempMonth+"' and year='"+tempYear+"'";
			ResultSet rr=st.executeQuery(query);
			if(rr.next())
			{
				return rr.getDouble("nmlf");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}

	public int addHrmBasicData(String groupName, String name, Connection conn) 
	{
		Statement st=null;
		ResultSet rr=null;
		try
		{
			 st=conn.createStatement();
			 String qq="insert into hrm_basic(name, group_name) values('"+name+"','"+groupName+"')";
			 int i=st.executeUpdate(qq);
			 return i;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}

	public ArrayList<EmpInfo> viewHrmBasicData() 
	{
		ArrayList<EmpInfo> list=new ArrayList<>();
		Statement st=null;
		ResultSet rr=null;
		int count=1;
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			st=conn.createStatement();
			String qq="select * from hrm_basic ";
			rr=st.executeQuery(qq);
			while(rr.next())
			{
				EmpInfo ee=new EmpInfo();
				
				ee.setSno(count++);
				ee.setName(rr.getString("name"));
				ee.setGroupName(rr.getString("group_name"));
				ee.setId(rr.getString("id"));
				
				list.add(ee);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return list;
	}

	public boolean checkHrmBasicDuplicacy(String selectType, String name,Connection conn) 
	{
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select * from hrm_basic where group_name='"+selectType+"' and name='"+name+"'  ";
			rr=st.executeQuery(qq);
			if(rr.next())
			{
				return true;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return false;
	}

	public int deleteHrmBasicData(String id, Connection conn) 
	{
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="delete from hrm_basic where id='"+id+"'  ";
			int i=st.executeUpdate(qq);
			return i;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}

	public ArrayList<SelectItem> hrmBasicList(String groupName, Connection conn) 
	{
		ArrayList<SelectItem> list=new ArrayList<>();
		Statement st=null;
		ResultSet rr=null;
		try
		{
			 st=conn.createStatement();
			String qq="select * from hrm_basic where group_name='"+groupName+"' ";
			rr=st.executeQuery(qq);
			System.out.println("addEMplistdropdown :"+qq);
			while(rr.next())
			{
				SelectItem ss=new SelectItem();
				
				ss.setLabel(rr.getString("name"));
				ss.setValue(rr.getString("id"));
				
				list.add(ss);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return list;
	}
	
	public boolean cheksalarysearch(int branchId,String month, String year) 
	{
		Statement stt= null;
		ResultSet rr=null;
		boolean valid=false;
		Connection cnt=DataBaseConnection.JAVAConnection();
		try
		{
			stt= cnt.createStatement();
			String qq="select * from salary_auto where month='"+month+"' and year='"+year+"' ";
			rr=stt.executeQuery(selectQueWithBranch(qq,branchId));;
			if(rr.next())
			{
				return true;
			}
		} 
		catch (SQLException e1) 
		{
			e1.printStackTrace();
		}	
		return valid;

	}


	/////////////////////////////////////////////////////////////////////////////

	public ArrayList<EmpInfo> showdataofemply(int branchId,String month, String year) 
	{
		ResultSet rr=null;
		Statement stt=null;
		int count=1;
		ArrayList<EmpInfo> list=new ArrayList<EmpInfo>();
		Connection cnt= DataBaseConnection.JAVAConnection();
		try
		{
			stt= cnt.createStatement();
			String qq="select * from salary_auto where month='"+month+"' and year='"+year+"'";
			rr=stt.executeQuery(selectQueWithBranch(qq, branchId));
			while (rr.next())
			{
				EmpInfo obj=new EmpInfo();
				
				obj.setSno(count++);
				obj.setId(rr.getString("id"));
				obj.setUname(rr.getString("uname"));
				obj.setRate(rr.getDouble("rate"));
				obj.setBusiness(rr.getDouble("business"));
				obj.setGrossSalary(rr.getDouble("gross_salary"));
				obj.setDeduction(rr.getDouble("deduction"));
				obj.setIncentive(rr.getDouble("incentive"));
				obj.setReason(rr.getString("reason"));
				obj.setFinalSalary(rr.getDouble("final_salary"));
				
				list.add(obj);
			}
		}	
		
		catch (SQLException el){
			el.printStackTrace();
		}
			
		return list;
	}

	/////////////////////////////////////////////////////////////////////////////////


	public int addsalary(String month, String year, String emloy_name, double rate,
			double business, double gross_salary, double deducation,
			double incentive, String reson, double final_salary, int empBranchId) 
	{
		Connection cnt=DataBaseConnection.JAVAConnection();
		try 
		{
			String qq="insert into salary_auto (month, year, rate, business, gross_salary, deduction, incentive, reason, final_salary,uname,branch_id) value(?,?,?,?,?,?,?,?,?,?,?)";       
			PreparedStatement stt = cnt.prepareStatement(qq);
			 stt.setString (1, month);
			 stt.setString(2, year);
			 stt.setDouble(3, rate);
			 stt.setDouble(4, business);
			 stt.setDouble(5, gross_salary);
			 stt.setDouble(6, deducation);
			 stt.setDouble(7, incentive);
			 stt.setString(8, reson);
			 stt.setDouble(9, final_salary);
			 stt.setString(10, emloy_name);
			 stt.setInt(11, empBranchId);
			 int rr=stt.executeUpdate();
			if(rr>0)	
			{
	            entryInSalaryLedger(emloy_name,Integer.parseInt(month), Integer.parseInt(year),final_salary, "Credit","0",new Date(),empBranchId);
				
	            return 1;
			}

			cnt.close();
		}
			   
		catch (SQLException el)
		{
			el.printStackTrace();
		}
		return 0;
	}
	/////////////////////////////////////////////////////////////////////////////////

	public int updatesalary( String emloy_name,double rate, double business, double gross_salary, double deducation,
			double incentive, String reson, double final_salary, String id, String month, String year) 
	{
		Connection cnt= DataBaseConnection.JAVAConnection();
		try 
		{
		    Statement stt =cnt.createStatement(); 
			String q="UPDATE salary_auto set rate='"+rate+"', business='"+business+"' , gross_salary='"+gross_salary+"', deduction='"+deducation+"', "
					+ "incentive='"+incentive+"' ,reason='"+reson+"', final_salary='"+final_salary+"'  WHERE id='"+id+"' ";
			int a=stt.executeUpdate(q);	
			if(a>=1)
			{
				Timestamp ss=new Timestamp(new Date().getTime());
	            ss.setMonth(Integer.parseInt(month));
	            ss.setDate(1);
	            ss.setHours(0);
			    ss.setNanos(0);
			    ss.setMinutes(0);
			    ss.setSeconds(0);
	            
				updateSalaryLedger(emloy_name, Integer.parseInt(month), Integer.parseInt(year), final_salary, "Credit",ss);
				return 1;	
			}
		}
		
		catch (SQLException el)
		{
			el.printStackTrace();
		}		
	 
		return 0;
	}
	////////////////////////////////////////////////////////////////////////
	
	public ArrayList<EmpInfo> salaryPayoutData(Date startDate, Date endDate, String empUserId) 
	{
		ArrayList<EmpInfo> list=new ArrayList<>();
		int count=1;
		String sDate=sdfForDb.format(startDate);
		String eDate=sdfForDb.format(endDate);
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select * from salary_ledger where type='Debit' and payDate>='"+sDate+"' and payDate<='"+eDate+"' ";
			if(!empUserId.equalsIgnoreCase("-1"))
			{
				qq+=" and empId='"+empUserId+"' ";
			}
			//System.out.println(qq);
			ResultSet rr=st.executeQuery(qq);
			while(rr.next())
			{
				EmpInfo ee=new EmpInfo();
				
				ee.setSno(count++);
				ee.setId(rr.getString("id"));
				ee.setPayDate(rr.getDate("payDate"));
				ee.setStrPayDate(sdf.format(ee.getPayDate()));
				ee.setUname(rr.getString("empId"));
				ee.setName(employeeNameById(ee.getUname()));
				ee.setAmount(rr.getDouble("amount"));
				ee.setPayMode(rr.getString("payment_mode"));
				ee.setRemark(rr.getString("description"));
				ee.setSubmittedBank(rr.getString("bank_name"));
				
				list.add(ee);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}

	public int deleteSalaryPayoutEntry(String id) 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="delete from salary_ledger where id='"+id+"' ";
			int i=st.executeUpdate(qq);
			return i;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}
	
	
	public int addHoliday(Date startDate, Date tillDate, String description, Connection conn) {
	    int i = 0;

	    try {
	        String query = "INSERT INTO holiday_table (holiday_date, remark) VALUES (?, ?)";

	        try (PreparedStatement ps = conn.prepareStatement(query)) {

	            // Create and initialize Calendar with startDate
	            Calendar calendar = Calendar.getInstance();
	            calendar.setTime(startDate);

	            while (calendar.getTime().before(tillDate)||calendar.getTime().equals(tillDate)) {
	                // Reset time to 00:00:00
	                calendar.set(Calendar.HOUR_OF_DAY, 0);
	                calendar.set(Calendar.MINUTE, 0);
	                calendar.set(Calendar.SECOND, 0);
	                calendar.set(Calendar.MILLISECOND, 0);

	                // Get the current timestamp
	                Timestamp ts = new Timestamp(calendar.getTimeInMillis());

	                // Set values and insert
	                ps.setTimestamp(1, ts);
	                ps.setString(2, description);
	                ps.executeUpdate();

	                // Add 7 days to calendar
	                calendar.add(Calendar.DAY_OF_MONTH, 7);
	            }
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return i;
	}
	

	
	

	public ArrayList<ReportInfo> allHolidayList(Connection conn) 
	{
		ArrayList<ReportInfo> holidayList=new ArrayList<ReportInfo>();
		ResultSet rr=null;
		Statement st=null;
		try
		{
			st=conn.createStatement();
			String query="select * from holiday_table  ";
			rr=st.executeQuery(query);
			int i=1;
			while(rr.next())
			{
				ReportInfo ll=new ReportInfo();
				ll.setsNo(i++);
				ll.setId(rr.getString("id"));
				ll.setDate(rr.getTimestamp("holiday_date"));
				ll.setDateStr(sf.format(ll.getDate()));
				ll.setRemark(rr.getString("remark"));
				holidayList.add(ll);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally 
        {
            try 
            {
                // Close the writer regardless of what happens...
            	if(st!=null)
            	{
            		  st.close();
                }
            	if(rr!=null)
            	{
            	     rr.close();
                }
            } 
            catch (Exception s) 
            {
            	s.printStackTrace();
            }
        }
		return holidayList;
	}

	public int checkDuplicateHoliday(Date date, Connection con) 
	{
		String sDate=sdfForDb.format(date);
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=con.createStatement();
			String query="select * from holiday_table where holiday_date='"+sDate+"' ";
			rr=st.executeQuery(query);
			if(rr.next())
			{
				return 1;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally 
        {
            try 
            {
                // Close the writer regardless of what happens...
            	if(st!=null)
            	{
            		  st.close();
                }
            	if(rr!=null)
            	{
            		rr.close();
            	}
            } 
            catch (Exception s) 
            {
                s.getMessage();
            }
        }
		return 0;
	}
	
	public int countHolidayBetweenDate(Date startDate, Date pay_date, Connection conn) 
	{
		String sDate=sdfForDb.format(pay_date);
		String eDate=sdfForDb.format(startDate);
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String qq="select count(*) from holiday_table where holiday_date>='"+sDate+"' and holiday_date<='"+eDate+"' ";
			//System.out.println(qq);
			rr=st.executeQuery(qq);
			if(rr.next())
			{
				return rr.getInt("count(*)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return 0;
	}
	
}
