<?php
   include('session.php');
   include('connect.php');
   include('logging.php');
   $companyID = $_SESSION['companyselect'];


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

  $payrollid = $_GET['payrollID'];

  $sqlViewPayroll = "SELECT *
  FROM periods
  WHERE periodid = $payrollid;";

    $yearOfPeriod = "0000";
  $monthOfPeriod = "00";
  $periodDateData = "";

  $resultPayroll = mysqli_query($db,$sqlViewPayroll);

  $countPayroll = mysqli_num_rows($resultPayroll);

  if ($countPayroll != 0) {

      while ($rowPayrollData = mysqli_fetch_array($resultPayroll)) {

        $dateextract = strtotime($rowPayrollData['endDate']);
        $yearOfPeriod = date("Y",$dateextract);
        $monthOfPeriod = date("m",$dateextract);
        $periodDateData = substr($yearOfPeriod, -2) . $monthOfPeriod;
      }

  }

  if(isset($_GET['updated'])) {

    $updated = $_GET['updated'];

  }

    $sqlView = "SELECT 
          employees.employeeid AS employeeid,
          employees.employeename AS employeename,
          employees.employeefileno AS employeefileno,
          employees.employeenic AS employeenic,
          employees.employeesurname AS employeesurname,
          employees.employeenic AS employeenic,
          employees.employee_fulltime_boolean AS employeefulltime,
          employees.employee_join_date AS joinDate,
          employees.employee_termination_date AS terminationDate,
          employees.employee_contribution_code AS contributioncode,
          payrollbasicdetails.totalPackage AS totalPackage,
          payrollbasicdetails.travellingAmt AS travellingAmt,
          payrollbasicdetails.payeEmp AS payeEmp,
          payrollbasicdetails.solidaritylevy AS solidaritylevy,
          payrollbasicdetails.employerLEVY AS employerLEVY,
          payrollbasicdetails.basicSal AS basicSal,     
          payrollbasicdetails.employeeCSG AS employeecsg,
          payrollbasicdetails.employeeNSF AS employeensf,        
          periods.startDate AS startDate,
          periods.endDate AS endDate
          FROM payrollbasicdetails
          LEFT JOIN employees
          ON payrollbasicdetails.employeeid = employees.employeeid
          LEFT JOIN periods
          ON payrollbasicdetails.payrollid = periods.periodid
          WHERE payrollbasicdetails.payrollid = $payrollid";

  
    $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 = 'paco-csv' . '-' . $periodDateData . '.csv';
		 
		//Set the Content-Type and Content-Disposition headers.
		header('Content-Type: application/excel');
		header('Content-Disposition: attachment; filename="' . $fileName . '"');

		$data = array();

    array_push($data, array("MRA","PACO","V1.0"));

    array_push($data, array("Employer Registration Number","Employer Business Registration Number","Employer Name","Tax Period","Telephone Number","Mobile Number","Name of Declarant","Email Address"));

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

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

	    	array_push($data, array($rowCompanyFile['companyERN'],$rowCompanyFile['companyBRN'],$rowCompanyFile['companyName'],$periodDateData,$rowCompanyFile['accountantPNumber'],$rowCompanyFile['accountantMNumber'],$rowCompanyFile['accountantName'],$rowCompanyFile['accountantEmail']));
	     }

       array_push($data, array("Employee ID","Surname of Employee","Other Names of Employee","Wage Bill (MUR)","NSF Contribution Code","Pay Code","Frequency","Contribution Sociale Genéralisée Amount (CSG) (MUR)","National Savings Fund Amount (NSF) (MUR)","LEVY Applicable? (Y/N)","Emoluments excluding travelling","Full Time Employment (Y/N)","PAYE for Income Tax (MUR)","PAYE for Solidarity Levy(MUR)","End of Year Bonus (MUR)","Contribution Sociale Genéralisée Amount (CSG) on End of Year Bonus (MUR)"));

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

        $startDateForPeriod = $rowpayroll['startDate'];
        $endDateForPeriod = $rowpayroll['endDate'];

        $employeeJoin = $rowpayroll['joinDate'];
      $employeeTermination = $rowpayroll['terminationDate'];

      if (($employeeTermination == NULL || $employeeTermination == "0000-00-00")) {
        if ($employeeJoin > $endDateForPeriod) {
          continue;
        } 
      } else {
        if ($employeeTermination < $startDateForPeriod) {
          continue;
        } 
      }


      $sqlGetTaxableCarAllowance = "SELECT 
      benefitvalue
      FROM
      employee_benefits
      WHERE
      employeeid = '" . $rowpayroll['employeeid'] . "' AND benefitid = '1'";


      $resultTCA = mysqli_query($db,$sqlGetTaxableCarAllowance);

      $countTCA = mysqli_num_rows($resultTCA);

      
        $newCSVColumn = 0;

        $emolumentCalculated = $rowpayroll['totalPackage'] - $rowpayroll['travellingAmt'];

        if ($countTCA != 0) {
          while ($rowTCA = mysqli_fetch_array($resultTCA)) {
            
            $newCSVColumn = $emolumentCalculated + $rowTCA['benefitvalue'] ;

          }
          
        } else {
          $newCSVColumn = $emolumentCalculated ;
        }

    	 	array_push($data, 
          array(
            $rowpayroll['employeenic'], //employeeid
            $rowpayroll['employeesurname'], //surname
            $rowpayroll['employeename'],//othernames
            number_format($emolumentCalculated, 0, '.', ''),//wagebill
            $rowpayroll['contributioncode'],//contributioncode
            'M',//paycode
            '1',//frequency
            $rowpayroll['employeecsg'],//csg on wagebill
            $rowpayroll['employeensf'],//nsf
            'Y',//levybool
            $rowpayroll['basicSal'],//emolumentslesstravelling
            $rowpayroll['employeefulltime'],//fulltimebool
            number_format($rowpayroll['payeEmp'], 0, '.', ''),//paye
            number_format($rowpayroll['payeEmp'], 0, '.', ''),//payeonsolidaritylevy
            '0',//endofyrbonus
            '0',//csgoneoybonus
            ));
    	 }

    	 
		//Open up a PHP output stream using the function fopen.
		$fp = fopen('php://output', 'w');
		 
		//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 {
	echo "Payroll not specified..";
}

?>