<?php

namespace App\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use Doctrine\DBAL\DriverManager;

class DashboardController extends AbstractController
{
    /**
     * @Route("/dashboard", name="app_dashboard")
     */
    public function index(): Response
    {
        $connectionParams = array(
            'dbname' => 'HRMDB',
            'user' => 'sa',
            'password' => 'ssl@1234',
            'host' => '192.168.0.206',
            'port' => '1433',
            'driver' => 'pdo_sqlsrv',
        );
       
        $conn = DriverManager::getConnection($connectionParams);

        $sql = '
            DECLARE @employeeId int = :empId
            DECLARE @fisYear int = :fiscalYear
            SELECT Emp_Id, Emp_Name, (SELECT  Join_Date  FROM dbHRM.dbo.Emp_Details where dbHRM.dbo.Emp_Details.Emp_Index = (SELECT max(Emp_Index) from dbHRM.dbo.Emp_Details where Emp_Id = @employeeId)) as JoiningDate, 
            (select dep.Dept_Name from [dbHRM].[dbo].[Dept_Info] dep where dep.Dept_Code = (SELECT m.Dept_Code FROM dbHRM.dbo.[Desig_Info] m WHERE m.Desig_AutoId = (SELECT  Desig_AutoID  FROM dbHRM.dbo.Emp_Details where dbHRM.dbo.Emp_Details.Emp_Index = (SELECT max(Emp_Index) from dbHRM.dbo.Emp_Details where Emp_Id = @employeeId)) ) ) as Department,  
            (SELECT m.Desig_Name FROM dbHRM.dbo.[Desig_Info] m WHERE m.Desig_AutoId = (SELECT  Desig_AutoID  FROM dbHRM.dbo.Emp_Details where dbHRM.dbo.Emp_Details.Emp_Index = (SELECT max(Emp_Index) from dbHRM.dbo.Emp_Details where Emp_Id = @employeeId)) ) as Designation,
            (select com.Comp_Name from [dbHRM].[dbo].[Comp_Info] com where com.Comp_Code = (select dep.Comp_Code from [dbHRM].[dbo].[Dept_Info] dep where dep.Dept_Code = (SELECT m.Dept_Code FROM dbHRM.dbo.[Desig_Info] m WHERE m.Desig_AutoId = (SELECT  Desig_AutoID  FROM dbHRM.dbo.Emp_Details where dbHRM.dbo.Emp_Details.Emp_Index = (SELECT max(Emp_Index) from dbHRM.dbo.Emp_Details where Emp_Id = @employeeId)) ) ) ) as Company,
            (CasualLeave - (isnull((SELECT sum(Total) from dbo.tblLeaveApplication l WHERE l.Emp_id = viewLeaveInfo.Emp_id and l.LeaveType = 1 and l.FiscalYear  = @fisYear),0) + isnull((SELECT sum(CL) from dbo.tblLeaveAdjust b WHERE b.EmpID = viewLeaveInfo.Emp_Id AND b.FiscalYear = @fisYear ), 0))) as CasualLeave,
            (AnnualLeave - (isnull((SELECT sum(Total) from dbo.tblLeaveApplication l WHERE l.Emp_id = viewLeaveInfo.Emp_id and l.LeaveType = 2 and l.FiscalYear  = @fisYear),0) + isnull((SELECT sum(AL) from dbo.tblLeaveAdjust b WHERE b.EmpID = viewLeaveInfo.Emp_Id AND b.FiscalYear = @fisYear ), 0))) as AnnualLeave,
            (SickLeave - isnull((SELECT sum(Total) from dbo.tblLeaveApplication l WHERE l.Emp_id = viewLeaveInfo.Emp_id and l.LeaveType = 3 and l.FiscalYear  = @fisYear),0)) as SickLeave 
            FRom  dbo.viewLeaveInfo WHERE Emp_Id = @employeeId and FiscalYear  = @fisYear

            ';

        $statement  = $conn->prepare($sql); 
        $statement->bindValue("empId", $this->getUser()->getUsername());  
        $statement->bindValue("fiscalYear", date("Y"));     
                               
        // returns an array of arrays (i.e. a raw data set)    
      //  $leaveInfo = $resultSet->fetchAllAssociative();
       
        $statement->execute();

        // returns an array of arrays (i.e. a raw data set)
    
        $leaveInfo = $statement->fetchAll();
        
        return $this->render('dashboard/index.html.twig', [
            'leaveInfo' => $leaveInfo,
            'year' => date("Y"),
        ]);
    }
}
