<?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;
use Symfony\Component\Form\Extension\Core\Type\ChoiceType;
use Symfony\Component\Form\Extension\Core\Type\TextType;
use Symfony\Component\HttpFoundation\Request;
use App\Services\MyServices;
use Symfony\Component\Mailer\MailerInterface;
use Symfony\Component\Mime\Email;
use Symfony\Bridge\Twig\Mime\TemplatedEmail;

class AttendanceController extends AbstractController
{
    /**
     * @Route("/attendancelist", name="attendance_list")
     */
    public function index(Request $request): Response
    {
        $form = $this->createFormBuilder(null)        
            ->add('pin',TextType::class, [       
                'attr' => ['class' => 'form-control',
                'placeholder' => 'Search by PIN',
            ],
                'label' => false,
                'required' => false,                
            ])
            ->add('name',TextType::class, [       
                'attr' => ['class' => 'form-control',
                'placeholder' => 'Search by Name',
            ],
                'label' => false,
                'required' => false,                
            ])
            ->add('fiscalYear', ChoiceType::class, [                           
                'choices'  => [
                    '2020' => '2020',
                    '2021' => '2021', 
                    '2022' => '2022',                           
                ],
                'placeholder' => 'Select a Fiscal Year',
                'attr' => ['class' => 'form-control'], 
                'label' => false,      
                'data' => '2022',             
                // 'expanded' => true,
                // 'multiple' => false,                
                ])
            ->add('month', ChoiceType::class, [                           
                'choices'  => [
                    'January' => '1', 
                    'February' => '2',
                    'March' => '3', 
                    'April' => '4', 
                    'May' => '5', 
                    'June' => '6', 
                    'July' => '7', 
                    'August' => '8',                           
                    'September' => '9', 
                    'October' => '10', 
                    'November ' => '11', 
                    'December' => '12', 

                ],
                'placeholder' => 'Select a Month',
                'label' => false,
                'attr' => ['class' => 'form-control'],
                'data' => '6',                   
                // 'expanded' => true,
                // 'multiple' => false,                
                ])            
            ->getForm();

        $form->handleRequest($request); 
                        
        if ($form->isSubmitted() && $form->isValid()) {
            $data = $form->getData();                     
            $pin =  $form->get('pin')->getData() !== null ? $form->get('pin')->getData() : "";
            $name =  $form->get('name')->getData() !== null ? $form->get('name')->getData() : "";
            $fiscalYear = $form->get('fiscalYear')->getData() !== null ? $form->get('fiscalYear')->getData() : "";
            $month = $form->get('month')->getData() !== null ? $form->get('month')->getData() : "";   
            $beforeMonth = $month - 1;         
         
            $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 @varPin int = :pin
                    DECLARE @varName varchar(300) = :name
                    DECLARE @varfiscalYear int = :fiscalYear
                    DECLARE @varMonth int = :month
                    DECLARE @varBeforeMonth int = :beforeMonth
                    SELECT EmpID as Pin,  
                    ( case when EmpID < 5001 then (SElECT Emp_Name FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID) else (SElECT Emp_Name FROM dbo.tem_employee m WHERE m.Emp_Id = EmpID) end)  as [Emp_Name], 
                    (select DepName from dbo.tblDepartment Where DepID = (SElECT DepID FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID)) as [Department],
                    (select DesName from dbo.tblDesignation Where DesId  = (SElECT DesID FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID)) as [Designation],
                    (case when EmpID < 5001 then  (SElECT sex FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID)  END ) as [Sex],  
                    DATEPART(MONTH,[Date]) as [Month],  FiscalYear, (count([ID]) - (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 8 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ))as working_day,
                    (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 1 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as absent_day,
                    (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 2 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as late_day,
                    (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 3 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as early_day,
                    (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 4 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as lateEarly_day,
                    (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 5 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as unothorize_day,
                    (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 6 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as present_day,
                    (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 7 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as leave_day,
                    (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 8 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as holiday_day,
                    ((select CL From dbo.tblYearEmployee E WHERE E.Emp_Id = EmpID AND E.FiscalYear = @varfiscalYear)- (isnull((select SUM(Total) FROM dbo.tblLeaveApplication A WHERE A.Emp_Id = EmpID AND A.FiscalYear = @varfiscalYear AND A.LeaveType = 1),0) + isnull((SELECT sum(CL) from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.FiscalYear = @varfiscalYear ), 0))) AS casual_day,
                    ((select AL From dbo.tblYearEmployee E WHERE E.Emp_Id = EmpID AND E.FiscalYear = @varfiscalYear)- (isnull((select SUM(Total) FROM dbo.tblLeaveApplication A WHERE A.Emp_Id = EmpID AND A.FiscalYear = @varfiscalYear AND A.LeaveType = 2),0) + isnull((SELECT sum(AL) from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.FiscalYear = @varfiscalYear ), 0))) AS anual_day, 
                    ((select CL From dbo.tblYearEmployee E WHERE E.Emp_Id = EmpID AND E.FiscalYear = @varfiscalYear)- (isnull((select SUM(Total) FROM dbo.tblLeaveApplication A WHERE A.Emp_Id = EmpID AND A.FiscalYear = @varfiscalYear AND A.LeaveType = 1),0) + isnull((SELECT sum(CL) from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.[Month] <= @varBeforeMonth AND b.FiscalYear = @varfiscalYear ), 0))) AS cl_days,
                    ((select AL From dbo.tblYearEmployee E WHERE E.Emp_Id = EmpID AND E.FiscalYear = @varfiscalYear)- (isnull((select SUM(Total) FROM dbo.tblLeaveApplication A WHERE A.Emp_Id = EmpID AND A.FiscalYear = @varfiscalYear AND A.LeaveType = 2),0) + isnull((SELECT sum(AL) from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.[Month] <= @varBeforeMonth AND b.FiscalYear = @varfiscalYear ), 0))) AS al_days,                    
                    isnull((SELECT PK_ID from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.[Month]  =   @varMonth AND b.FiscalYear = @varfiscalYear ), 0) as adjust_id,  
                    ( case when EmpID < 5001 then (SElECT email_id FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID) else (SElECT email_id FROM dbo.tem_employee m WHERE m.Emp_Id = EmpID) end) as email_id, 
                    ( case when EmpID < 5001 then (SElECT Cc_EmaiId FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID) else (SElECT Cc_EmaiId FROM dbo.tem_employee m WHERE m.Emp_Id = EmpID) end) as cc_emailId  
                    FROM  dbo.tblAttendance  
                    WHERE BID =  1  AND EmpID =  @varPin   AND DATEPART(MONTH,[Date]) =  @varMonth   AND FiscalYear =  @varfiscalYear 
                    group by EmpID,   DATEPART(MONTH,[Date]), FiscalYear order by EmpID, DATEPART(MONTH,[Date]),   FiscalYear 
                ";
    
            $statement  = $conn->prepare($sql); 
            $statement->bindValue("pin", $pin);  
            $statement->bindValue("name", $name); 
            $statement->bindValue("fiscalYear", $fiscalYear);  
            $statement->bindValue("month", $month);       
            $statement->bindValue("beforeMonth", $beforeMonth);

            // 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)
        
            $attendenceActions = $statement->fetchAll();

            return $this->render('attendance/index.html.twig', [
                'form' => $form->createView(),
                'attendenceActions' => $attendenceActions,
                                            
            ]);
        
        }
     
        return $this->render('attendance/index.html.twig', [
            'form' => $form->createView(),
            'attendenceActions' => null,
            
        ]);

    }

    /**
     * @Route("/attendanceEmail/{pin}/{fiscalYear}/{month}", name="attendance_email")
     */
    public function attendanceEmail($pin, $fiscalYear, $month, MyServices $myServices, MailerInterface $mailer): Response
    {
        $beforeMonth = $month - 1;

        $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 @varPin int = :pin
          
                DECLARE @varfiscalYear int = :fiscalYear
                DECLARE @varMonth int = :month
                DECLARE @varBeforeMonth int = :beforeMonth
                SELECT EmpID as Pin,  
                ( case when EmpID < 5001 then (SElECT Emp_Name FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID) else (SElECT Emp_Name FROM dbo.tem_employee m WHERE m.Emp_Id = EmpID) end)  as [Emp_Name], 
                (select DepName from dbo.tblDepartment Where DepID = (SElECT DepID FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID)) as [Department],
                (select DesName from dbo.tblDesignation Where DesId  = (SElECT DesID FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID)) as [Designation],
                (case when EmpID < 5001 then  (SElECT sex FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID)  END ) as [Sex],  
                DATEPART(MONTH,[Date]) as [Month],  FiscalYear, (count([ID]) - (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 8 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ))as working_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 1 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as absent_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 2 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as late_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 3 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as early_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 4 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as lateEarly_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 5 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as unothorize_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 6 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as present_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 7 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as leave_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 8 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as holiday_day,
                ((select CL From dbo.tblYearEmployee E WHERE E.Emp_Id = EmpID AND E.FiscalYear = @varfiscalYear)- (isnull((select SUM(Total) FROM dbo.tblLeaveApplication A WHERE A.Emp_Id = EmpID AND A.FiscalYear = @varfiscalYear AND A.LeaveType = 1),0) + isnull((SELECT sum(CL) from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.FiscalYear = @varfiscalYear ), 0))) AS casual_day,
                ((select AL From dbo.tblYearEmployee E WHERE E.Emp_Id = EmpID AND E.FiscalYear = @varfiscalYear)- (isnull((select SUM(Total) FROM dbo.tblLeaveApplication A WHERE A.Emp_Id = EmpID AND A.FiscalYear = @varfiscalYear AND A.LeaveType = 2),0) + isnull((SELECT sum(AL) from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.FiscalYear = @varfiscalYear ), 0))) AS anual_day, 
                ((select CL From dbo.tblYearEmployee E WHERE E.Emp_Id = EmpID AND E.FiscalYear = @varfiscalYear)- (isnull((select SUM(Total) FROM dbo.tblLeaveApplication A WHERE A.Emp_Id = EmpID AND A.FiscalYear = @varfiscalYear AND A.LeaveType = 1),0) + isnull((SELECT sum(CL) from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.[Month] <= @varBeforeMonth AND b.FiscalYear = @varfiscalYear ), 0))) AS cl_days,
                ((select AL From dbo.tblYearEmployee E WHERE E.Emp_Id = EmpID AND E.FiscalYear = @varfiscalYear)- (isnull((select SUM(Total) FROM dbo.tblLeaveApplication A WHERE A.Emp_Id = EmpID AND A.FiscalYear = @varfiscalYear AND A.LeaveType = 2),0) + isnull((SELECT sum(AL) from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.[Month] <= @varBeforeMonth AND b.FiscalYear = @varfiscalYear ), 0))) AS al_days,                    
                isnull((SELECT PK_ID from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.[Month]  =   @varMonth AND b.FiscalYear = @varfiscalYear ), 0) as adjust_id,  
                ( case when EmpID < 5001 then (SElECT email_id FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID) else (SElECT email_id FROM dbo.tem_employee m WHERE m.Emp_Id = EmpID) end) as email_id,  ( case when EmpID < 5001 then (SElECT Cc_EmaiId FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID) else (SElECT Cc_EmaiId FROM dbo.tem_employee m WHERE m.Emp_Id = EmpID) end) as cc_emailId  
                FROM  dbo.tblAttendance  
                WHERE BID =  1  AND EmpID =  @varPin   AND DATEPART(MONTH,[Date]) =  @varMonth   AND FiscalYear =  @varfiscalYear 
                group by EmpID,   DATEPART(MONTH,[Date]), FiscalYear order by EmpID, DATEPART(MONTH,[Date]),   FiscalYear 
            ";

        $statement  = $conn->prepare($sql); 
        $statement->bindValue("pin", $pin);  
    
        $statement->bindValue("fiscalYear", $fiscalYear);  
        $statement->bindValue("month", $month);       
        $statement->bindValue("beforeMonth", $beforeMonth);

        // 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)
    
        $attendenceActions = $statement->fetchAll();
        
        foreach($attendenceActions as $attendenceAction)
        {  
           
            $leaveAdj = $myServices->leaveCalculation($attendenceAction['absent_day'], $attendenceAction['lateEarly_day'], $attendenceAction['late_day'], 0, 0, $attendenceAction['al_days'], $attendenceAction['cl_days']);
        }
       
        $ccAddresses = explode(",", $attendenceActions['0']['cc_emailId']);       
        $ccAddresses = array_filter($ccAddresses);  // remove array keys with empty values
        
        $email = (new TemplatedEmail())
            ->from('hr@cross-world.com')
            ->to($attendenceActions['0']['email_id'])
            ->cc(...$ccAddresses)
            ->bcc('hr@cross-world.com')
            //->replyTo('fabien@example.com')
            //->priority(Email::PRIORITY_HIGH)
            ->subject('Leave Adjustment')           
            // path of the Twig template to render
            ->htmlTemplate('attendance/attendanceEmail.html.twig')
            // pass variables (name => value) to the template
            ->context([
                'attendenceActions' => $attendenceActions,
                'leaveAdj' => $leaveAdj,
            ])
            ;
        
        try 
        {
            $mailer->send($email);

            $this->addFlash('notice', 'Email sent successfully!');
        } catch (TransportExceptionInterface $e) {
            // some error prevented the email sending; display an
            // error message or try to resend the message
            dd($e);
        }
        
        return $this->redirectToRoute('attendance_list');

        // return $this->render('attendance/attendanceEmail.html.twig', [
        //     'attendenceActions' => $attendenceActions,
        //     'leaveAdj' => $leaveAdj,
        // ]);
    }

    /**
     * @Route("/directEmail/{pin}/{fiscalYear}/{month}", name="attendance_directEmail")
     */
    public function directEmail($pin, $fiscalYear, $month, MyServices $myServices): Response
    {
        $beforeMonth = $month - 1;

        $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 @varPin int = :pin
          
                DECLARE @varfiscalYear int = :fiscalYear
                DECLARE @varMonth int = :month
                DECLARE @varBeforeMonth int = :beforeMonth
                SELECT EmpID as Pin,  
                ( case when EmpID < 5001 then (SElECT Emp_Name FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID) else (SElECT Emp_Name FROM dbo.tem_employee m WHERE m.Emp_Id = EmpID) end)  as [Emp_Name], 
                (select DepName from dbo.tblDepartment Where DepID = (SElECT DepID FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID)) as [Department],
                (select DesName from dbo.tblDesignation Where DesId  = (SElECT DesID FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID)) as [Designation],
                (case when EmpID < 5001 then  (SElECT sex FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID)  END ) as [Sex],  
                DATEPART(MONTH,[Date]) as [Month],  FiscalYear, (count([ID]) - (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 8 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ))as working_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 1 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as absent_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 2 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as late_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 3 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as early_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 4 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as lateEarly_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 5 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as unothorize_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 6 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as present_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 7 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as leave_day,
                (select count([ID]) from dbo.tblAttendance a Where a.AttenStatus = 8 AND a.EmpID = tblAttendance.EmpID AND DATEPART(MONTH,a.[Date]) =  @varMonth AND FiscalYear =  @varfiscalYear ) as holiday_day,
                ((select CL From dbo.tblYearEmployee E WHERE E.Emp_Id = EmpID AND E.FiscalYear = @varfiscalYear)- (isnull((select SUM(Total) FROM dbo.tblLeaveApplication A WHERE A.Emp_Id = EmpID AND A.FiscalYear = @varfiscalYear AND A.LeaveType = 1),0) + isnull((SELECT sum(CL) from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.FiscalYear = @varfiscalYear ), 0))) AS casual_day,
                ((select AL From dbo.tblYearEmployee E WHERE E.Emp_Id = EmpID AND E.FiscalYear = @varfiscalYear)- (isnull((select SUM(Total) FROM dbo.tblLeaveApplication A WHERE A.Emp_Id = EmpID AND A.FiscalYear = @varfiscalYear AND A.LeaveType = 2),0) + isnull((SELECT sum(AL) from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.FiscalYear = @varfiscalYear ), 0))) AS anual_day, 
                ((select CL From dbo.tblYearEmployee E WHERE E.Emp_Id = EmpID AND E.FiscalYear = @varfiscalYear)- (isnull((select SUM(Total) FROM dbo.tblLeaveApplication A WHERE A.Emp_Id = EmpID AND A.FiscalYear = @varfiscalYear AND A.LeaveType = 1),0) + isnull((SELECT sum(CL) from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.[Month] <= @varBeforeMonth AND b.FiscalYear = @varfiscalYear ), 0))) AS cl_days,
                ((select AL From dbo.tblYearEmployee E WHERE E.Emp_Id = EmpID AND E.FiscalYear = @varfiscalYear)- (isnull((select SUM(Total) FROM dbo.tblLeaveApplication A WHERE A.Emp_Id = EmpID AND A.FiscalYear = @varfiscalYear AND A.LeaveType = 2),0) + isnull((SELECT sum(AL) from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.[Month] <= @varBeforeMonth AND b.FiscalYear = @varfiscalYear ), 0))) AS al_days,                    
                isnull((SELECT PK_ID from dbo.tblLeaveAdjust b WHERE b.EmpID = tblAttendance.EmpID AND b.[Month]  =   @varMonth AND b.FiscalYear = @varfiscalYear ), 0) as adjust_id,  
                ( case when EmpID < 5001 then (SElECT email_id FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID) else (SElECT email_id FROM dbo.tem_employee m WHERE m.Emp_Id = EmpID) end) as email_id,  ( case when EmpID < 5001 then (SElECT Cc_EmaiId FROM dbo.Emp_Master m WHERE m.Emp_Id = EmpID) else (SElECT Cc_EmaiId FROM dbo.tem_employee m WHERE m.Emp_Id = EmpID) end) as cc_emailId  
                FROM  dbo.tblAttendance  
                WHERE BID =  1  AND EmpID =  @varPin   AND DATEPART(MONTH,[Date]) =  @varMonth   AND FiscalYear =  @varfiscalYear 
                group by EmpID,   DATEPART(MONTH,[Date]), FiscalYear order by EmpID, DATEPART(MONTH,[Date]),   FiscalYear 
            ";

        $statement  = $conn->prepare($sql); 
        $statement->bindValue("pin", $pin);  
    
        $statement->bindValue("fiscalYear", $fiscalYear);  
        $statement->bindValue("month", $month);       
        $statement->bindValue("beforeMonth", $beforeMonth);

        // 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)
    
        $attendenceActions = $statement->fetchAll();
        
        foreach($attendenceActions as $attendenceAction)
        { 
            $leaveAdj = $myServices->leaveCalculation($attendenceAction['absent_day'], $attendenceAction['lateEarly_day'], $attendenceAction['late_day'], 0, 0, $attendenceAction['al_days'], $attendenceAction['cl_days']);
        }

        return $this->render('attendance/attendanceEmail.html.twig', [
             'attendenceActions' => $attendenceActions,
             'leaveAdj' => $leaveAdj,
        ]);
       
    }


}
