<?php



if ($_SERVER["REQUEST_METHOD"] == "POST") { 

  include('connect.php');
  include('session.php');
  $companyID = $_SESSION['companyselect'];
          $startdate=$_POST['startdate'];
          $enddate=$_POST['enddate'];

        $dateextract = strtotime($enddate);
        $yearOfPeriod = date("Y",$dateextract);


          $sqlView = "SELECT 
                  employees.employeename AS employeename,
                  employees.employeesurname AS employeesurname,
                  employees.employeeid AS employeeid,
                  employees.employeenic AS employeenic,
                  employees.employee_edf_amount AS employee_edf_amount,
                  sum(payrollbasicdetails.netSalary) AS netSalary,
                  sum(payrollbasicdetails.travellingAmt) AS travellingAmt,
                  sum(payrollbasicdetails.specialBonus) AS specialBonus,
                  sum(payrollbasicdetails.housingallowance) AS housingallowance,
                  sum(payrollbasicdetails.carbenefitvalue) AS carbenefitvalue,
                  sum(payrollbasicdetails.commission) AS commission,
                  sum(payrollbasicdetails.overtime) AS overtime,
                  sum(payrollbasicdetails.totalPackage) AS totalPackage,
                  sum(payrollbasicdetails.absencesInput) AS absencesInput,
                  sum(payrollbasicdetails.absencesValue) AS absencesValue,
                  sum(payrollbasicdetails.payeEmp) AS payeEmp
                  FROM payrollbasicdetails
                  LEFT JOIN employees
                  ON payrollbasicdetails.employeeid = employees.employeeid
                  RIGHT JOIN periods
                  ON payrollbasicdetails.payrollid = periods.periodid                  
                  WHERE periods.startDate BETWEEN '$startdate' AND '$enddate'
                  AND employees.companyid = $companyID
                  GROUP BY employeeid";

                  $result = mysqli_query($db,$sqlView);

                  $count = mysqli_num_rows($result);


                  if ($count != 0) {

      //The name of the CSV file that will be downloaded by the user.
    $fileName = 'return-of-employees.csv';
     
    //Set the Content-Type and Content-Disposition headers.
    header('Content-Type: application/excel');
    header('Content-Disposition: attachment; filename="' . $fileName . '"');

    $data = array();
    $data1 = array();

    array_push($data1, array("MNS","ROEA","V1.0"));

    array_push($data1, array("Employer Registration Number (ERN)","Business Registration Number (BRN)","Employer Name","Income Year","Total PAYE Held","Telephone Number","Mobile Number","Name of Declarant","Email Address"));

    $sqlViewCompanyFile = "SELECT *
      FROM companyfile
      WHERE companyid = $companyID";
  
      $resultCompanyFile = mysqli_query($db,$sqlViewCompanyFile);

      $totalPAYEWithheld = 0;

      

       //array_push($data, array("Employee ID","Surname of Employee","Other Names of Employee","Salary/Wages/Allowances/Bonus (MUR)","Entertainment Allowance (MUR)","Transport/Travelling Allowance/ Reimbursement or Travelling Expenses (MUR)","Reimbursement of Other Expenses (MUR)","Car Benefit (MUR)","House Benefit (MUR)","Tax Benefit (MUR)","Other Benefit (MUR)","Lump Sum (MUR)","Retirement Pension/ Annuity (MUR)","Excempt Emoluments (MUR)","Income Excemption Threshold (IET) (MUR)","Additional Exemption for Children pursuing Undergraduate Course (MUR)","Interest Relief on Secured Housing Loan (MUR)","Relief for Medical insurance premium or contr. for approved provident fund (MUR)","Other Reliefs and Allowances (MUR)","PAYE Withheld (MUR)"));

       array_push($data, array("Employee ID","Surname of Employee","Other Names of Employee","Salary/Wages/Allowances/Bonus (MUR)","Entertainment Allowance (MUR)","Transport/Travelling Allowance/ Reimbursement or Travelling Expenses (MUR)","Reimbursement of Other Expenses (MUR)","Car Benefit (MUR)","House Benefit (MUR)","Tax Benefit (MUR)","Other Benefit (MUR)","Lump Sum (MUR)","Retirement Pension/ Annuity (MUR)","Excempt Emoluments (MUR)","Total deductions claimed in EDF","PAYE For Income Tax (MUR)","PAYE for Solidarity Levy (MUR)"));									


       while ($rowpayroll = mysqli_fetch_array($result)) { 

        $getBonusValue = "SELECT sum(finalBonusValue) AS sumBonus,
                           sum(bonusPayeAmount) AS bonusPayeAmount
                          FROM bonuspayouts
                          INNER JOIN periods
                          ON bonuspayouts.bonusPayrollid = periods.periodid 
                          WHERE periods.startDate = '$startdate' AND periods.endDate = '$enddate' 
                          AND bonuspayouts.employeeid = '" . $rowpayroll['employeeid'] . "'";

            $resultBonus = mysqli_query($db,$getBonusValue);

            $bonusCount = mysqli_num_rows($resultBonus);
        
            $totalBonus = 0;

            $payeOnBonus = 0;
        if ($bonusCount !== 0) { 
          while ($rowBonus = mysqli_fetch_array($resultBonus)) { 

            $bonusToDisplay = $rowBonus['sumBonus'];
          }
        } else {
          $bonusToDisplay = 0;
        }

        $firstCellD = $rowpayroll['totalPackage'] - $rowpayroll['absencesValue'] - $rowpayroll['travellingAmt'] - $rowpayroll['housingallowance'] + $bonusToDisplay;

        array_push($data, array(
          $rowpayroll['employeenic'], 
          $rowpayroll['employeesurname'], 
          $rowpayroll['employeename'],
          number_format($firstCellD, 0, '.', ''),
          '0', //entertainment allowance
          number_format($rowpayroll['travellingAmt'], 0, '.', ''),
          '0', //reimbursement of other expenses
          number_format($rowpayroll['carbenefitvalue'], 0, '.', ''),
          number_format($rowpayroll['housingallowance'], 0, '.', ''), // house benefit
          '0', // tax benefit
          '0', // other benefit
          '0', // lump sum
          '0', // annuity
          '0', // excempt emoluments
          number_format($rowpayroll['employee_edf_amount'], 0, '.', ''),
          number_format($rowpayroll['payeEmp'], 0, '.', ''),
          '0' // PAYE for solidarity levy
        )
      );

      $totalPAYEWithheld += $rowpayroll['payeEmp'];
       }

       while ($rowCompanyFile = mysqli_fetch_array($resultCompanyFile)) {

        array_push($data1, array($rowCompanyFile['companyERN'],$rowCompanyFile['companyBRN'],$rowCompanyFile['companyName'],$yearOfPeriod,$totalPAYEWithheld,$rowCompanyFile['accountantPNumber'],$rowCompanyFile['accountantMNumber'],$rowCompanyFile['accountantName'],$rowCompanyFile['accountantEmail']));
       }
       
    //Open up a PHP output stream using the function fopen.
    $fp = fopen('php://output', 'w');
     

       //Loop through the array containing our CSV data.
    foreach ($data1 as $row) {
      //fputcsv formats the array into a CSV format.
      //It then writes the result to our output stream.
      fputcsv($fp, $row);
  }

    //Loop through the array containing our CSV data.
    foreach ($data as $row) {
        //fputcsv formats the array into a CSV format.
        //It then writes the result to our output stream.
        fputcsv($fp, $row);
    }
     
    //Close the file handle.
    fclose($fp);
    }




      }  else { ///////////////////

           include('session.php');
   include('header1.php');
   include('ietpartial.php');

// start role specific 
$user_role = $_SESSION['role'];

  $sqlGetEmployee = "SELECT * FROM employees WHERE companyid = $companyID";

  $resultEmployee = mysqli_query($db,$sqlGetEmployee);

  $countEmployee = mysqli_num_rows($resultEmployee);

  echo "<main role=\"main\" class=\"col-md-9 ml-sm-auto col-lg-10 px-4\">";

  echo "<div class=\"d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom\">";


    	echo "<h1 class=\"h2\">Return of Employees</h1>";
 
    
    	echo "</div>";

    	echo "<form method = \"POST\" action=\"report-getReturnOfEmployees.php\">";

      echo "<p>Start Date:<input type = \"date\" class=\"form-control\" name = \"startdate\"></p>";

      echo "<p>End Date:</p><input type = \"date\" class=\"form-control\" name = \"enddate\"></p>";


    echo "<input type=\"submit\"> </form>";

    include('footer1.php');

   }




?>