<?php

namespace App\Controller;

use App\Entity\LeaveApplication;
use App\Form\LeaveApplicationType;
use App\Repository\LeaveApplicationRepository;
use App\Repository\UserRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\Form\Extension\Core\Type\TextType;
use Symfony\Component\Form\Extension\Core\Type\SubmitType;
use Symfony\Component\Mailer\MailerInterface;
use Symfony\Component\Mime\Email;
use Symfony\Bridge\Twig\Mime\TemplatedEmail;
use Doctrine\DBAL\DriverManager;

/**
 * @Route("/leaveapplication")
 */
class LeaveApplicationController extends AbstractController
{
    /**
     * @Route("/{tag}/index", name="app_leave_application_index", methods={"GET", "POST"})
     */
    public function index($tag, LeaveApplicationRepository $leaveApplicationRepository): Response
    {
        if($tag == 1)
        {
            return $this->render('leave_application/index.html.twig', [
                'leave_applications' => $leaveApplicationRepository->findBy(
                    ['empId' => $this->getUser()->getUsername() ],
                    ['id' => 'DESC']
                ),
                'title' => 'All Leave',
            ]);
        }
        else if($tag == 2)
        {
            return $this->render('leave_application/index.html.twig', [
                'leave_applications' => $leaveApplicationRepository->findBy(
                    ['empId' => $this->getUser()->getUsername(), 'dhApproval' => null ],
                    ['id' => 'DESC']
                ),
                'title' => 'Pending Leave',
            ]);
        }
        else if($tag == 3)
        {
            return $this->render('leave_application/index.html.twig', [
                'leave_applications' => $leaveApplicationRepository->findBy(
                    ['empId' => $this->getUser()->getUsername(), 'dhApproval' => 1 ],
                    ['id' => 'DESC']
                ),
                'title' => 'Approved Leave',
            ]);
        }
        else
        {
            return $this->render('leave_application/index.html.twig', [
                'leave_applications' => $leaveApplicationRepository->findBy(
                    ['empId' => $this->getUser()->getUsername(), 'dhApproval' => 0 ],
                    ['id' => 'DESC']
                ),
                'title' => 'Rejected Leave',
            ]);
        }
    }

    /**
     * @Route("/new", name="app_leave_application_new", methods={"GET", "POST"})
     */
    public function new(Request $request, LeaveApplicationRepository $leaveApplicationRepository, UserRepository $userRepository, MailerInterface $mailer): Response
    {        
        $leaveApplication = new LeaveApplication();
        $form = $this->createForm(LeaveApplicationType::class, $leaveApplication)
                     ->add('createNew', SubmitType::class);  
        $form->handleRequest($request);

        $pendingApplicationList = $leaveApplicationRepository->findOneBy(
            ['empId' => $this->getUser()->getUsername(), 'fhApproval' => null]
        );
              
        if($pendingApplicationList !== null)
        {
            $this->addFlash('warning', 'Please wait, your leave application is awaiting approval from the Head of the Department.');

            return $this->redirectToRoute('app_leave_application_index', ['tag' => 2], Response::HTTP_SEE_OTHER);
        }

        if ($form->isSubmitted() && $form->isValid()) {
                       
            $fromDaysViewData = clone $leaveApplication->getFromDays();

            $fromDaysViewData = date_sub($fromDaysViewData, date_interval_create_from_date_string("1 days"));
           
            $dateApplicationList = $leaveApplicationRepository->findOneBy(
                [
                    'empId' => $this->getUser()->getUsername(), 
                    'toDays' => $fromDaysViewData 
                ]
            );
            
            if($dateApplicationList !== null)
            {
                $this->addFlash('warning', 'Back-to-back leave is not allowed.');
    
                return $this->render('leave_application/new.html.twig', [
                    'leave_application' => $leaveApplication,
                    'form' => $form->createView(),
                ]);
            }

            $dateApplicationList = $leaveApplicationRepository->findOneBy(
                [
                    'empId' => $this->getUser()->getUsername(), 
                    'fromDays' => $leaveApplication->getFromDays(), 
                    'toDays' => $leaveApplication->getToDays(), 
                ]
            );

            if($dateApplicationList !== null)
            {
                $this->addFlash('warning', 'You have already applied on this date.');
    
                return $this->render('leave_application/new.html.twig', [
                    'leave_application' => $leaveApplication,
                    'form' => $form->createView(),
                ]);
            }
           

            $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"));     
            $statement->execute();

            // returns an array of arrays (i.e. a raw data set)
        
            $leaveInfo = $statement->fetchAll();

            // Data Validation and Save          
            $totalLeaveDays = date_diff($leaveApplication->getFromDays(), $leaveApplication->getToDays());
            $totalLeaveDays = $totalLeaveDays->format("%a");
            $totalLeaveDays +=1;
           
            if($leaveApplication->getLeavename()->getId() == 1)
            {
                if($totalLeaveDays > 3)
                {
                    $this->addFlash('warning', 'Casual leave is only allowed for 3 days. You are applying for '.$totalLeaveDays.' days.');

                    return $this->render('leave_application/new.html.twig', [
                        'leave_application' => $leaveApplication,
                        'form' => $form->createView(),
                    ]);
                }
                else if($totalLeaveDays > $leaveInfo[0]['CasualLeave'])
                {
                    $this->addFlash('warning', 'Your applied casual leave has exceeded the remaining balance.');

                    return $this->render('leave_application/new.html.twig', [
                        'leave_application' => $leaveApplication,
                        'form' => $form->createView(),
                    ]);
                }
            }

            if($leaveApplication->getLeavename()->getId() == 2)
            {               
                if($totalLeaveDays > $leaveInfo[0]['AnnualLeave'])
                {
                    $this->addFlash('warning', 'Your applied annual leave has exceeded the remaining balance.');

                    return $this->render('leave_application/new.html.twig', [
                        'leave_application' => $leaveApplication,
                        'form' => $form->createView(),
                    ]);
                }
            }

            if($leaveApplication->getLeavename()->getId() == 3)
            {               
                if($totalLeaveDays > $leaveInfo[0]['SickLeave'])
                {
                    $this->addFlash('warning', 'Your applied sick leave has exceeded the remaining balance.');

                    return $this->render('leave_application/new.html.twig', [
                        'leave_application' => $leaveApplication,
                        'form' => $form->createView(),
                    ]);
                }
            }

            $leaveApplication->setEmpId($this->getUser()->getUsername());
            $leaveApplication->setStatus(1);

            $entityManager = $this->getDoctrine()->getManager();
            $entityManager->persist($leaveApplication);
            $entityManager->flush();

            $leaveApplicationId = $leaveApplication->getId(); 
            // Data Validation and Save
            
            //Email Send to Functional Head Start
            if($this->getUser()->getFunctionalHead() !== null)
            {
                $functionalHeadObj = $userRepository->find($this->getUser()->getFunctionalHead());
                $tokenVal = 'UOpyIH2IRbtJi3NWE3gokFKUATPuQplhge6BEc92WDw';
                
                $email = (new TemplatedEmail())
                    ->from('hr@cross-world.com')
                    ->to($functionalHeadObj->getOfficeEmail())
                    //->cc('mhn.khan@gmail.com', 'mdaasad89@gmail.com', 'info@famousmartbd.com')
                   // ->bcc('malaya@cross-world.com')
                    //->replyTo('fabien@example.com')
                    //->priority(Email::PRIORITY_HIGH)
                    ->subject('Leave Application of '.$this->getUser()->getfullName())           
                    // path of the Twig template to render
                    ->htmlTemplate('leave_application/leaveApproval.html.twig')
                    // pass variables (name => value) to the template
                    ->context([
                        'leaveInfos' => $leaveInfo,
                        'leaveApplication' => $leaveApplicationRepository->find($leaveApplicationId),
                        'approvalStep' => 1,
                        'recommendedUrl' => 'http://45.64.134.180:8081/cwlms/public/index.php/leaveapplication/functionalApproval/'.$leaveApplicationId.'/1/'.$tokenVal,
                        'notRecommendedUrl' => 'http://45.64.134.180:8081/cwlms/public/index.php/leaveapplication/functionalApproval/'.$leaveApplicationId.'/0/'.$tokenVal,
                    ])
                    ;
                
                try {
                    $mailer->send($email);
                } catch (TransportExceptionInterface $e) {
                    // some error prevented the email sending; display an
                    // error message or try to resend the message
                    dd($e);
                }
            }//Email Send to Functional Head End
            else if($this->getUser()->getDepartmentHead() !== null) //Email Send to Department Head Start
            {
                $departmentHeadObj = $userRepository->find($this->getUser()->getDepartmentHead());                
                $tokenVal = 'UOpyIH2IRbtJi3NWE3gokFKUATPuQplhge6BEc92WDw';
                    
                $email = (new TemplatedEmail())
                    ->from('hr@cross-world.com')
                    ->to($departmentHeadObj->getOfficeEmail())
                    //->cc('mhn.khan@gmail.com', 'mdaasad89@gmail.com', 'info@famousmartbd.com')
                   // ->bcc('malaya@cross-world.com')
                    //->replyTo('fabien@example.com')
                    //->priority(Email::PRIORITY_HIGH)
                    ->subject('Leave Application of '.$this->getUser()->getfullName())           
                    // path of the Twig template to render
                    ->htmlTemplate('leave_application/leaveApproval.html.twig')
                    // pass variables (name => value) to the template
                    ->context([
                        'leaveInfos' => $leaveInfo,
                        'leaveApplication' => $leaveApplicationRepository->find($leaveApplicationId),
                        'approvalStep' => 2,
                        'approvalUrl' => 'http://45.64.134.180:8081/cwlms/public/index.php/leaveapplication/departmentHeadApproval/'.$leaveApplicationId.'/1/'.$tokenVal,
                        'notApprovalUrl' => 'http://45.64.134.180:8081/cwlms/public/index.php/leaveapplication/departmentHeadApproval/'.$leaveApplicationId.'/0/'.$tokenVal,
                        'functionalHeadObj' => null,
                    ])
                    ;
                
                try {
                    $mailer->send($email);
                } catch (TransportExceptionInterface $e) {
                    // some error prevented the email sending; display an
                    // error message or try to resend the message
                    dd($e);
                }
            } //Email Send to Department Head End

            $this->addFlash('notice', 'Leave Application Created successfully! Please wait for approval via email.');
           
            if ($form->get('createNew')->isClicked()) {
                return $this->redirectToRoute('app_leave_application_new');
            }

            return $this->redirectToRoute('app_leave_application_show', ['id' => $leaveApplicationId]);  
        }

        return $this->render('leave_application/new.html.twig', [
            'leave_application' => $leaveApplication,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/{id}/show", name="app_leave_application_show", methods={"GET"})
     */
    public function show(LeaveApplication $leaveApplication): Response
    {
        return $this->render('leave_application/show.html.twig', [
            'leave_application' => $leaveApplication,
        ]);
    }

    /**
     * @Route("/{id}/edit", name="app_leave_application_edit", methods={"GET", "POST"})
     */
    public function edit(Request $request, LeaveApplication $leaveApplication, LeaveApplicationRepository $leaveApplicationRepository, UserRepository $userRepository, MailerInterface $mailer): Response
    {
        $form = $this->createForm(LeaveApplicationType::class, $leaveApplication);
        $form->handleRequest($request);

        if ($form->isSubmitted() && $form->isValid()) {

            $leaveAppObj = $leaveApplicationRepository->find($leaveApplication->getId());
            $editAccess = $this->getUser()->getFunctionalHead() === null ? $leaveAppObj->getDhApproval() : $leaveAppObj->getFhApproval();
            if($editAccess !== null)
            {
                $this->addFlash('warning', 'Your edit access has been denied.');

                return $this->render('leave_application/edit.html.twig', [
                    'leave_application' => $leaveApplication,
                    'form' => $form->createView(),
                ]);
            }

            $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"));     
            $statement->execute();

            // returns an array of arrays (i.e. a raw data set)
        
            $leaveInfo = $statement->fetchAll();

            // Data Validation and Save
            $totalLeaveDays = date_diff($leaveApplication->getFromDays(), $leaveApplication->getToDays());
            $totalLeaveDays = $totalLeaveDays->format("%a");
            $totalLeaveDays +=1;
           
            if($leaveApplication->getLeavename()->getId() == 1)
            {
                if($totalLeaveDays > 3)
                {
                    $this->addFlash('warning', 'Casual leave is only allowed for 3 days. You are applying for '.$totalLeaveDays.' days.');

                    return $this->render('leave_application/edit.html.twig', [
                        'leave_application' => $leaveApplication,
                        'form' => $form->createView(),
                    ]);
                }
                else if($totalLeaveDays > $leaveInfo[0]['CasualLeave'])
                {
                    $this->addFlash('warning', 'Your applied casual leave has exceeded the remaining balance.');

                    return $this->render('leave_application/edit.html.twig', [
                        'leave_application' => $leaveApplication,
                        'form' => $form->createView(),
                    ]);
                }
            }

            if($leaveApplication->getLeavename()->getId() == 2)
            {               
                if($totalLeaveDays > $leaveInfo[0]['AnnualLeave'])
                {
                    $this->addFlash('warning', 'Your applied annual leave has exceeded the remaining balance.');

                    return $this->render('leave_application/edit.html.twig', [
                        'leave_application' => $leaveApplication,
                        'form' => $form->createView(),
                    ]);
                }
            }

            if($leaveApplication->getLeavename()->getId() == 3)
            {               
                if($totalLeaveDays > $leaveInfo[0]['SickLeave'])
                {
                    $this->addFlash('warning', 'Your applied sick leave has exceeded the remaining balance.');

                    return $this->render('leave_application/edit.html.twig', [
                        'leave_application' => $leaveApplication,
                        'form' => $form->createView(),
                    ]);
                }
            }
           
           // $leaveApplicationRepository->add($leaveApplication);
            $this->getDoctrine()->getManager()->flush();
            $leaveApplicationId = $leaveApplication->getId(); 

            $this->addFlash('notice', 'Leave Application Updated successfully! Please wait for approval via email.');
            // Data Validation and Save
            
            //Email Send to Functional Head Start
            if($this->getUser()->getFunctionalHead() !== null)
            {
                $functionalHeadObj = $userRepository->find($this->getUser()->getFunctionalHead());
                $tokenVal = 'UOpyIH2IRbtJi3NWE3gokFKUATPuQplhge6BEc92WDw';
                
                $email = (new TemplatedEmail())
                    ->from('it@cross-world.com')
                    ->to($functionalHeadObj->getOfficeEmail())
                    //->cc('mhn.khan@gmail.com', 'mdaasad89@gmail.com', 'info@famousmartbd.com')
                    //->bcc('malaya@cross-world.com')
                    //->replyTo('fabien@example.com')
                    //->priority(Email::PRIORITY_HIGH)
                    ->subject('Leave Application of '.$this->getUser()->getfullName())           
                    // path of the Twig template to render
                    ->htmlTemplate('leave_application/leaveApproval.html.twig')
                    // pass variables (name => value) to the template
                    ->context([
                        'leaveInfos' => $leaveInfo,
                        'leaveApplication' => $leaveApplicationRepository->find($leaveApplication->getId()),
                        'approvalStep' => 1,
                        'recommendedUrl' => 'http://45.64.134.180:8081/cwlms/public/index.php/leaveapplication/functionalApproval/'.$leaveApplication->getId().'/1/'.$tokenVal,
                        'notRecommendedUrl' => 'http://45.64.134.180:8081/cwlms/public/index.php/leaveapplication/functionalApproval/'.$leaveApplication->getId().'/0/'.$tokenVal,
                    ])
                    ;
                
                try {
                    $mailer->send($email);
                } catch (TransportExceptionInterface $e) {
                    // some error prevented the email sending; display an
                    // error message or try to resend the message
                    dd($e);
                }
            }//Email Send to Functional Head End
            else if($this->getUser()->getDepartmentHead() !== null) //Email Send to Department Head Start
            {
                $departmentHeadObj = $userRepository->find($this->getUser()->getDepartmentHead());                
                $tokenVal = 'UOpyIH2IRbtJi3NWE3gokFKUATPuQplhge6BEc92WDw';
                    
                $email = (new TemplatedEmail())
                    ->from('it@cross-world.com')
                    ->to($departmentHeadObj->getOfficeEmail())
                    //->cc('mhn.khan@gmail.com', 'mdaasad89@gmail.com', 'info@famousmartbd.com')
                    //->bcc('malaya@cross-world.com')
                    //->replyTo('fabien@example.com')
                    //->priority(Email::PRIORITY_HIGH)
                    ->subject('Leave Application of '.$this->getUser()->getfullName())           
                    // path of the Twig template to render
                    ->htmlTemplate('leave_application/leaveApproval.html.twig')
                    // pass variables (name => value) to the template
                    ->context([
                        'leaveInfos' => $leaveInfo,
                        'leaveApplication' => $leaveApplicationObj,
                        'approvalStep' => 2,
                        'approvalUrl' => 'http://45.64.134.180:8081/cwlms/public/index.php/leaveapplication/departmentHeadApproval/'.$id.'/1/'.$tokenVal,
                        'notApprovalUrl' =>  'http://45.64.134.180:8081/cwlms/public/index.php/leaveapplication/departmentHeadApproval/'.$id.'/0/'.$tokenVal,
                        'functionalHeadObj' => $functionalHeadObj,
                    ])
                    ;
                
                try {
                    $mailer->send($email);
                } catch (TransportExceptionInterface $e) {
                    // some error prevented the email sending; display an
                    // error message or try to resend the message
                    dd($e);
                }
            } //Email Send to Department Head End
        
            return $this->redirectToRoute('app_leave_application_show', ['id' => $leaveApplicationId]); 
        }

        return $this->render('leave_application/edit.html.twig', [
            'leave_application' => $leaveApplication,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/{id}/delete", name="app_leave_application_delete", methods={"POST"})
     */
    public function delete(Request $request, LeaveApplication $leaveApplication, LeaveApplicationRepository $leaveApplicationRepository): Response
    {
        if ($this->isCsrfTokenValid('delete'.$leaveApplication->getId(), $request->request->get('_token'))) {
            $leaveApplicationRepository->remove($leaveApplication);
        }

        return $this->redirectToRoute('app_leave_application_index', [], Response::HTTP_SEE_OTHER);
    }

    /**
     * @Route("/searchBar", name="country_searchBar")   
     */
    public function SearchBar()
    {
        $form = $this->createFormBuilder(null)
                     ->add('search', TextType::class, array(
                            'attr' => [
                                'class' => 'search-input',
                                'placeholder' => 'Search by any value'
                            ],
                            'label'=>false,
                        ))
                     ->getForm();

        return $this->render('leave_application/searchBar.html.twig', [
            'form' => $form->createView()
        ]);
    }

    /**
     * @Route("/functionalApproval/{id}/{approvalStatus}/{tokenVal}", name="app_leave_application_functionalHeadApproval")
     */
    public function functionalHeadApproval($id, $approvalStatus, $tokenVal, Request $request, LeaveApplicationRepository $leaveApplicationRepository, UserRepository $userRepository, MailerInterface $mailer)
    {        
        $leaveApplicationObj = $leaveApplicationRepository->find($id);

        if($leaveApplicationObj->getFhApproval() !== null)
        {
            return $this->render('leave_application/functionalHeadApproval.html.twig', [
                'taskTag' => 1,
            ]);
        }

        $leaveApplicationObj->setFhApproval($approvalStatus);
        $leaveApplicationObj->setFhApprovalBy(1);
        $leaveApplicationObj->setFhApprovalDate(new \DateTime("now", new \DateTimeZone("Asia/Dhaka")));

        $entityManager = $this->getDoctrine()->getManager();
        $entityManager->persist($leaveApplicationObj);
        $entityManager->flush();

        if($approvalStatus == 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 @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", $leaveApplicationObj->getEmpId());  
            $statement->bindValue("fiscalYear", date("Y"));     
            $statement->execute();

            // returns an array of arrays (i.e. a raw data set)
        
            $leaveInfo = $statement->fetchAll();

            $applicantUserObj = $userRepository->findOneBy(['username' => $leaveApplicationObj->getEmpId()]);

            $departmentHeadObj = $userRepository->find($applicantUserObj->getDepartmentHead());
            $functionalHeadObj = $userRepository->find($applicantUserObj->getFunctionalHead());
            $tokenVal = 'UOpyIH2IRbtJi3NWE3gokFKUATPuQplhge6BEc92WDw';
                
            $email = (new TemplatedEmail())
                ->from('it@cross-world.com')
                ->to($departmentHeadObj->getOfficeEmail())
                //->cc('mhn.khan@gmail.com', 'mdaasad89@gmail.com', 'info@famousmartbd.com')
                //->bcc('malaya@cross-world.com')
                //->replyTo('fabien@example.com')
                //->priority(Email::PRIORITY_HIGH)
                ->subject('Leave Application of '.$applicantUserObj->getfullName())           
                // path of the Twig template to render
                ->htmlTemplate('leave_application/leaveApproval.html.twig')
                // pass variables (name => value) to the template
                ->context([
                    'leaveInfos' => $leaveInfo,
                    'leaveApplication' => $leaveApplicationObj,
                    'approvalStep' => 2,
                    'approvalUrl' => 'http://45.64.134.180:8081/cwlms/public/index.php/leaveapplication/departmentHeadApproval/'.$id.'/1/'.$tokenVal,
                    'notApprovalUrl' => 'http://45.64.134.180:8081/cwlms/public/index.php/leaveapplication/departmentHeadApproval/'.$id.'/0/'.$tokenVal,
                    'functionalHeadObj' => $functionalHeadObj,
                ])
                ;
            
            try {
                $mailer->send($email);
            } catch (TransportExceptionInterface $e) {
                // some error prevented the email sending; display an
                // error message or try to resend the message
                dd($e);
            }
        }

        return $this->render('leave_application/functionalHeadApproval.html.twig', [
            'taskTag' => $approvalStatus == 1 ? 0 : 2,
        ]);

       // return $this->redirectToRoute('app_leave_application_index', [], Response::HTTP_SEE_OTHER);
    }

    /**
     * @Route("/departmentHeadApproval/{id}/{approvalStatus}/{tokenVal}", name="app_leave_application_departmentHeadApproval")
     */
    public function departmentHeadApproval($id, $approvalStatus, $tokenVal, LeaveApplicationRepository $leaveApplicationRepository)
    {        
        $leaveApplicationObj = $leaveApplicationRepository->find($id); 

        if($leaveApplicationObj->getDhApproval() !== null)
        {
            return $this->render('leave_application/departmentHeadApproval.html.twig', [
                'taskTag' => 1,
            ]);
        }

        $leaveApplicationObj->setDhApproval($approvalStatus);
        $leaveApplicationObj->setDhApprovalBy(1);
        $leaveApplicationObj->setDhApprovalDate(new \DateTime("now", new \DateTimeZone("Asia/Dhaka")));

        $entityManager = $this->getDoctrine()->getManager();
        $entityManager->persist($leaveApplicationObj);
        $entityManager->flush();

        if($approvalStatus == 1)
        {
            $connectionParams = array(
                'dbname' => 'HRMDB',
                'user' => 'sa',
                'password' => 'ssl@1234',
                'host' => '192.168.0.206',
                'port' => '1433',
                'driver' => 'pdo_sqlsrv',
            );
           
            $conn = DriverManager::getConnection($connectionParams);

            $totalLeaveDays = date_diff($leaveApplicationObj->getFromDays(), $leaveApplicationObj->getToDays());
            $totalLeaveDays = $totalLeaveDays->format("%a");
           
            $queryBuilder = $conn->createQueryBuilder();

            // $queryBuilder
            // ->insert('personal_info')
            // ->setValue('name', '?')
            // ->setValue('address', '?')
           
            // ->setParameter(0, $leaveApplicationObj->getReasons())
            // ->setParameter(1, $leaveApplicationObj->getAddress())
                   
            // ;

            $queryBuilder
                ->insert('tblLeaveApplication')
                ->setValue('LeaveType', '?')
                ->setValue('LeaveFrom', '?')
                ->setValue('LeaveTo', '?')
                ->setValue('Total', '?')
                ->setValue('Reason', '?')
                ->setValue('ProcedingAddress', '?')
                ->setValue('FHeadID', '?')
                ->setValue('Remarks', '?')
                ->setValue('Emp_Id', '?')
                ->setValue('CreateBy', '?')
                ->setValue('CreateDate', '?')
                ->setValue('FiscalYear', '?')
                ->setValue('business_id', '?')
                ->setParameter(0, $leaveApplicationObj->getLeavename()->getId())
                ->setParameter(1, $leaveApplicationObj->getFromDays()->format('Y-m-d'))
                ->setParameter(2, $leaveApplicationObj->getToDays()->format('Y-m-d'))
                ->setParameter(3, $totalLeaveDays + 1)
                ->setParameter(4, $leaveApplicationObj->getReasons())
                ->setParameter(5, $leaveApplicationObj->getAddress())
                ->setParameter(6, 0)
                ->setParameter(7, $leaveApplicationObj->getSpecialRemarks())
                ->setParameter(8, $leaveApplicationObj->getEmpId())
                ->setParameter(9, 1)
                ->setParameter(10, (new \DateTime("now", new \DateTimeZone("Asia/Dhaka")))->format('Y-m-d'))
                ->setParameter(11, $leaveApplicationObj->getFromDays()->format('Y'))
                ->setParameter(12, 1)
                                
            ;
            
            $queryBuilder->execute();


        }



        return $this->render('leave_application/departmentHeadApproval.html.twig', [
            'taskTag' => $approvalStatus == 1 ? 0 : 2,
        ]);
    }

}
