package hrm_package;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import main_package.DataBaseConnection;
import main_package.DataBaseMethods;
import main_package.EmpInfo;
import main_package.EmployeeInfo;

public class DbEmployeeMethod implements Serializable
{
	DecimalFormat df=new DecimalFormat("##.##");
	SimpleDateFormat sdfForDb=new SimpleDateFormat("yyyy-MM-dd"); 
	SimpleDateFormat sdf=new SimpleDateFormat("dd-MM-yyyy"); 
    DataBaseMethods dbm=new DataBaseMethods();
    
    
	public ArrayList<EmployeeInfo> viewEmpTarget(int branchId) 
	{
		ArrayList<EmployeeInfo> list=new ArrayList<>();
		int count=1;
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select * from employee where status='active' and branch_id="+branchId+" "; 
			ResultSet rr=st.executeQuery(qq);
			while(rr.next())
			{
				EmployeeInfo ee=new EmployeeInfo();
				
				ee.setSno(count++);
				ee.setUname(rr.getString("uname"));
				ee.setName(rr.getString("name"));
				employeeTargetDetail(ee,conn);
				
				list.add(ee);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}


	private void employeeTargetDetail(EmployeeInfo ee, Connection conn) 
	{
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String query="select * from employee_target where uname='"+ee.getUname()+"' and status='active' ";
			rr=st.executeQuery(query);
			while(rr.next())
			{
				ee.setDbAmount(rr.getDouble("db_amount"));
				ee.setDbFiles(rr.getDouble("db_files"));
				ee.setIncentive(rr.getDouble("incentive"));
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally 
        {
            try 
            {
            	if(st!=null)
            	{
            		  st.close();
                }
            	if(rr!=null)
            	{
            	     rr.close();
                }
            } 
            catch (Exception s) 
            {
                s.printStackTrace();
            }
        }
	}


	public int updateEmpTargetDetail(ArrayList<EmployeeInfo> list) 
	{
		int i=0;
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			if(list.size()>0)
			{
				Statement st1=conn.createStatement();
				String qq1="update employee_target set status='inactive' ";
				st1.executeUpdate(qq1);
				
				String userId=dbm.userId();
				
				for(EmployeeInfo ee: list)
				{
					Statement st=conn.createStatement();
					String qq="insert into employee_target(uname, db_amount, db_files, incentive, status, user_id) values('"+ee.getUname()+"','"+ee.getDbAmount()+"'"
							+ ",'"+ee.getDbFiles()+"','"+ee.getIncentive()+"','active','"+userId+"')";
					//System.out.println(qq);
					i=st.executeUpdate(qq);
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return i;
	}


	public ArrayList<EmployeeInfo> viewEmpSalesPerformanceReport(int branchId, Date startDate, Date endDate) 
	{
		String sdt=sdfForDb.format(startDate);
		String edt=sdfForDb.format(endDate);
		ArrayList<EmployeeInfo> list=new ArrayList<>();
		int count=1;
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select * from employee where status='active' and branch_id="+branchId+" "; 
			ResultSet rr=st.executeQuery(qq);
			while(rr.next())
			{
				EmployeeInfo ee=new EmployeeInfo();
				
				ee.setSno(count++);
				ee.setUname(rr.getString("uname"));
				ee.setName(rr.getString("name"));
				ee.setDsaCode(rr.getString("dsacode"));
				employeeTargetDetail(ee,conn);
				employeeSalesAchiveDetail(ee,sdt,edt,conn);
				ee.setBalanceDbAmount(ee.getDbAmount()-ee.getAchiveDbAmount());
				ee.setBalanceDbFiles(ee.getDbFiles()-ee.getAchiveDbFiles());
				ee.setTotalIncentive(ee.getAchiveDbFiles()*ee.getIncentive());
				double perform=(ee.getAchiveDbAmount()/ee.getDbAmount())*100;
				
				if(perform>=0 && perform<=100)
				{
					ee.setPerformance(perform);
					ee.setPerformance(Double.parseDouble(df.format(ee.getPerformance())));
				}
				
				
				list.add(ee);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}


	private void employeeSalesAchiveDetail(EmployeeInfo ee, String sdt, String edt, Connection conn) 
	{
		Statement st=null;
		ResultSet rr=null;
		try
		{
			st=conn.createStatement();
			String query="SELECT sum(amount),count(*) FROM debit_loan inner join grouploan where grouploan.dsacode='"+ee.getDsaCode()+"' and debit_loan.loanNo="
					+ "grouploan.sub_loan_no and date>='"+sdt+"' and date<='"+edt+"' and grouploan.status='Approve' ";
			//System.out.println(query);
			rr=st.executeQuery(query);
			while(rr.next())
			{
				ee.setAchiveDbAmount(rr.getDouble("sum(amount)"));
				ee.setAchiveDbFiles(rr.getDouble("count(*)"));
			}
		}
		catch(Exception e )
		{
			e.printStackTrace();
		}
		finally 
        {
            try 
            {
            	if(st!=null)
            	{
            		  st.close();
                }
            	if(rr!=null)
            	{
            	     rr.close();
            	     
                }
            } 
            catch (Exception s) 
            {
                s.printStackTrace();
            }
        }
	}

	
	
private void employeeSalesAchiveDeatailForPl(EmployeeInfo ee, String sdt, String edt, Connection conn)
{
	
	
		String qq ="SELECT sum(amount),count(*) FROM debit_loan inner join personalloan where persoanlloan.dsacode='"+ee.getDsaCode()+"' and debit_loan.loanNo="
				+ "	personalloan.loanNo and date>='"+sdt+"' and date<='"+edt+"' and personalloan.status='Approve' ";
		
		
		try(PreparedStatement st = conn.prepareStatement(qq)) {
			
			ResultSet rr = st.executeQuery();
			
			while(rr.next())
			{
				ee.setAchiveDbAmount(rr.getDouble("sum(amount)"));
				ee.setAchiveDbFiles(rr.getDouble("count(*)"));
				
			}
			
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	
	
}


	
	

	public int employeeBranchTransfer(EmpInfo selected, int newBranchId,Date transferDate,Date joinningDate) 
	{
		System.out.println("transfer called");
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			String qq="update employee set branch_id =? , transfer_date=?,doj=? where id = ?";
			
			
			try(PreparedStatement st =conn.prepareStatement(qq)) {
				
				st.setInt(1, newBranchId);
			    st.setDate(2, new java.sql.Date(transferDate.getTime())); // transfer_date
			    st.setDate(3, new java.sql.Date(joinningDate.getTime()));

			    st.setString(4, selected.getId()); // id (INT assumed)
				
				int i=	st.executeUpdate();
				if(i >0)
				{
					int userId =employeeLoginId(selected.getUname());
					updateEmployeeBranch(selected,newBranchId,userId);
					return i;
					
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}
    
	public int employeeLoginId(String uName)
	{
		Connection conn=DataBaseConnection.JAVAConnection();
		
		String qq ="select id from login where userid = '"+uName+"'";
		
		try(PreparedStatement st = conn.prepareStatement(qq)) {
			
			ResultSet rr = st.executeQuery();
			
			if(rr.next())
			{ 
				return rr.getInt("id");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
		
	}
	
	public void updateEmployeeBranch(EmpInfo selected, int newBranchId, int userId)
	{
		DataBaseMethods obj = new  DataBaseMethods();
		
		
		try(Connection conn = DataBaseConnection.JAVAConnection()) {
			String newBranchName=obj.branchNameByBranchId(newBranchId, conn);
			
			String qq ="update branch_allocate_table set branch_id = ? , branch_name = ? where user_id = ? " ;
			
			try(PreparedStatement st = conn.prepareStatement(qq)) {
				
				st.setInt(1, newBranchId);
				st.setString(2, newBranchName);
				st.setInt(3, userId);
				
				
				st.executeUpdate();
				
			} catch (Exception e) {
				e.printStackTrace();
			}
			
		} catch (Exception e) {
		
			e.printStackTrace();
		}
	}
	
    
}
