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



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

  include('connect.php');
  include('header2.php');

          $startdate=$_POST['startdate'];
          $enddate=$_POST['enddate'];
          if (isset($_POST['getallemployees'])) {

            $getallemployees=$_POST['getallemployees'];
          }
          $getemployeeid=$_POST['getemployeeid'];


          $getPeriodCounts = "SELECT periodid FROM
                  periods
                  WHERE bonusBoolean = 0
                  AND startDate BETWEEN '$startdate' AND '$enddate' ";

                  $resultPeriodCounts = mysqli_query($db,$getPeriodCounts);

                  $countPeriods = mysqli_num_rows($resultPeriodCounts);

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

          $employer_Name = "";
          $employer_ERN = "";
          $employer_BRN = "";

          $travellingValue = 0;
          

          $sqlView = "SELECT 
                  employees.employeename AS employeename,
                  employees.employeesurname AS employeesurname,
                  employees.employee_join_date AS employee_join_date,
                  employees.employee_termination_date AS employee_termination_date,
                  employees.employeeid AS employeeid,
                  employees.employeenic AS employeenic,
                  employees.employee_tan_number AS employee_tan_number,
                  employees.employee_edf_amount AS employee_edf_amount,
                  sum(payrollbasicdetails.netSalary) AS netSalary,
                  count(payrollbasicdetails.netSalary) AS countnetSalary,
                  sum(payrollbasicdetails.travellingAmt) AS travellingAmt,
                  sum(payrollbasicdetails.travellingAmt) AS totalTravellingAmount,
                  sum(payrollbasicdetails.totalcarallowance) AS totalcarallowance,
                  sum(payrollbasicdetails.housingallowance) AS housingallowance,
                  sum(payrollbasicdetails.specialBonus) AS specialBonus,
                  sum(payrollbasicdetails.taxableAllowance) AS taxableAllowance,
                  sum(payrollbasicdetails.nonTaxableAllowance) AS nonTaxableAllowance,
                  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 periods.bonusBoolean = 0
                  AND employees.companyid = $companyID";


                   if (isset($getallemployees)) {
                    $sqlAddonEmployee = " GROUP BY employeeid;";
                    $sqlView = $sqlView . $sqlAddonEmployee;
                  } else {
                    $sqlAddonEmployee = " AND payrollbasicdetails.employeeid = '$getemployeeid';";
                    $sqlView = $sqlView . $sqlAddonEmployee;                    
                  }


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

                  $count = mysqli_num_rows($result);


                  if ($count != 0) {



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

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

        $employer_Name = $rowCompanyFile['companyName']; 
        $employer_ERN = $rowCompanyFile['companyERN']; 
        $employer_BRN = $rowCompanyFile['companyBRN']; 

        
       }


       echo "<style>

       td {
        font-size: 18px;
        font-family: \"Times New Roman\";
       }


       </style>";

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

        echo "<div class=\"pagebreak\">";

        echo "<center><b><h4>STATEMENT OF EMOLUMENTS AND TAX DEDUCTION FOR INCOME YEAR ENDED 30 JUNE " . $YearFromDate . "</h4></b></center>";
        echo " <a class = \"no-print\" href= \"report-getStatementOfEmoluments.php\">Back</a> ";
        echo "<br>";

        echo "<table>";

        echo "<tr><td><b>1. IDENTITFICATION OF EMPLOYER</b></td></tr>";
        echo "<tr><td>Full Name &nbsp;&nbsp;&nbsp;&nbsp; " . $employer_Name . "</td></tr>";
        echo "<tr><td>PAYE Employer Registration Number &nbsp;&nbsp;&nbsp;&nbsp; " . $employer_ERN . "</td></tr>";
        echo "<tr><td>Business Registration Number &nbsp;&nbsp;&nbsp;&nbsp;" . $employer_BRN . "</td></tr>";

        echo "</table><br>";

        echo "<table>";

        echo "<tr><td><b>2. IDENTITFICATION OF EMPLOYEE</b></td></tr>";
        echo "<tr><td>Full Name of Employee " . $rowpayroll['employeesurname'] . " " . $rowpayroll['employeename'] . "</td></tr>";

        if ($rowpayroll['employee_termination_date'] == null || $rowpayroll['employee_termination_date'] == "" || $rowpayroll['employee_termination_date'] == "0000-00-00") {
          
          echo "<tr><td>Employed from " . $rowpayroll['employee_join_date'] . " to </td></tr>";
        
        } else {
          echo "<tr><td>Employed from " . $rowpayroll['employee_join_date'] . " to " . $rowpayroll['employee_termination_date'] . "</td></tr>";
        
        }

        echo "<tr><td>National Identity Card No. " . $rowpayroll['employeenic'] . " &nbsp;&nbsp; Tax Number " . $rowpayroll['employee_tan_number'] ."</td></tr>";

        echo "</table><br>";

        echo "<table>";

        echo "<tr><td><b>EMOLUMENTS</b> </td><td>Rs<td></tr>";

        $firstRow = $rowpayroll['totalPackage'] - $rowpayroll['housingallowance'] - $rowpayroll['absencesValue'] - $rowpayroll['totalcarallowance'] - $rowpayroll['travellingAmt'];

        //echo "<tr><td>Salary, wages leave pay, fee, overtime pay, perquisite, allowance, gratuity, commission or other reward or remuneration </td><td>" . number_format($firstRow, 0, '.', ',') . "</td></tr>";

        

        $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'] + $rowpayroll['specialBonus'];

            $firstRowTodisplay = $firstRow - $bonusToDisplay;

            echo "<tr><td>Salary, wages leave pay, fee, overtime pay, perquisite, allowance, gratuity, commission or other reward or remuneration </td><td>" . number_format($firstRowTodisplay, 0, '.', ',') . "</td></tr>";

            echo "<tr><td>Bonus including end-of-year bonus </td><td>" . number_format($bonusToDisplay, 0, '.', ',') . "</td></tr>";
            $totalBonus = $bonusToDisplay;
            $payeOnBonus = $rowBonus['bonusPayeAmount'];
          }
            
        } else {
          echo "<tr><td>Salary, wages leave pay, fee, overtime pay, perquisite, allowance, gratuity, commission or other reward or remuneration </td><td>" . number_format($firstRow, 0, '.', ',') . "</td></tr>";

          echo "<tr><td>Bonus including end-of-year bonus </td><td>" . $rowpayroll['specialBonus'] . "</td></tr>";
        }

        

        echo "<tr><td>Rent or housing allowance </td><td>" . number_format($rowpayroll['housingallowance'], 0, '.', ',') . "</td></tr>";

        echo "<tr><td>Entertainment allowance </td><td> " . "0" . "</td></tr>";

        echo "<tr><td>Transport, travelling, petrol or driver’s allowance, travel grant, commuted travelling allowance  </td><td> " . number_format($rowpayroll['totalcarallowance'], 0, '.', ',') . "</td></tr>";
        $travellingValue = $travellingAmount = number_format($rowpayroll['travellingAmt'], 0, '.', ',');
        echo "<tr><td>Reimbursement of travelling expenses  </td><td>" . $travellingValue . "</td></tr>";
        
        echo "<tr><td>Any other allowance or sum by whatever name called </td><td>" . "0" . "</td></tr>";

        echo "<tr><td>Reimbursement of the cost or payment of personal and private expenses </td><td>" . "0" . "</td></tr>";

        echo "<tr><td>Reimbursement of the cost or payment of passages, by sea, air or land between Mauritius and another country on behalf of the employee, his spouse and dependents </td><td>" . "0" . "</td></tr>";

        echo "<tr><td>Fringe Benefits  </td><td>" . "0" . "</td></tr>";

        echo "<tr><td>Lump sum by way of commutation of pension, death gratuity, consolidated compensation for death or injury, retiring allowance, severance allowance, compensation for loss of office, superanuation,    </td><td>" . "0" . "</td></tr>";

        echo "<tr><td>Retirement pension, annuity or other reward  </td><td>" . "0" . "</td></tr>";

        //$totalLine = $firstRow + $bonusToDisplay + $rowpayroll['housingallowance'] + $rowpayroll['totalcarallowance'] + $rowpayroll['travellingAmt'];
        $totalLine = $firstRow + $rowpayroll['housingallowance'] + $rowpayroll['totalcarallowance'] + $rowpayroll['travellingAmt'];


        echo "<tr><td>TOTAL </b> </td><td>" . number_format($totalLine, 0, '.', ',') . "</td></tr>";

          $sqlExcemptCarAllowance = "SELECT
            benefits.benefitname AS benefitname,
            employee_benefits.employeeid AS employeeid,
            employee_benefits.benefitvalue AS benefitvalue
            FROM employee_benefits
            INNER JOIN benefits
            ON employee_benefits.benefitid = benefits.benefitid
            WHERE employee_benefits.employeeid = '" . $rowpayroll['employeeid'] . "' 
            AND benefitname = 'Excempt Car Allowance';";



          $sresultExcemptCarAllowance = mysqli_query($db,$sqlExcemptCarAllowance);

          $countExcemptCarAllowance = mysqli_num_rows($sresultExcemptCarAllowance);

          $ExcemptCarValue = 0;

      if ($sresultExcemptCarAllowance !== 0) {
        while ($rowExcemptCarAllowance = mysqli_fetch_array($sresultExcemptCarAllowance)) { 

          $ExcemptCarValue = $rowExcemptCarAllowance['benefitvalue'];

        }

       }

       
        $deductAmount = $rowpayroll['travellingAmt'] + ($rowpayroll['countnetSalary'] * $ExcemptCarValue) + $rowpayroll['nonTaxableAllowance'];
        
        //$deductAmount = $rowpayroll['travellingAmt'];
        

        echo "<tr><td>Deduct: Exempt income in accordance with Sub-Part A of Part II of the Second Schedule to the Act  </td><td> " . number_format($deductAmount, 0, '.', ',') . "</td></tr>";

        $emolumentsNetOfExcempt = $totalLine - $deductAmount ;

        echo "<tr><td><b>EMOLUMENTS NET OF EXEMPT INCOME </b> </td><td>" . number_format($emolumentsNetOfExcempt, 0, '.', ',') . "</td></tr>";


       // - -- start iet period

       $edfAmount = "325000";

    $dqlGetEmployeeIETValues = "
        SELECT value
        FROM valuesbyemployee
        WHERE valuestartdate <= '$startdate' AND valueenddate >= '$enddate'
        AND employeeid = '" . $rowpayroll['employeeid'] . "'
        LIMIT 1
        ";

      $resultValuesByEmployee = mysqli_query($db,$dqlGetEmployeeIETValues);

      $countValuesByEmployee = mysqli_num_rows($resultValuesByEmployee);

      if ($countValuesByEmployee != 0) { 

        while ($rowValuesByEmployee = mysqli_fetch_array($resultValuesByEmployee)) { 
         // $prgfValue = $basicSalary * ($rowValuesByCompany['value'] / 100); 
         //echo "<td><input type = \"number\" readonly = \"readonly\" value = " . $rowValuesByEmployee['value'] . " style=\"background:#FCEC85;\" id = \"edfAmount" . $rowemployee['employeeid'] . "\"></td>";
         echo "<tr><td>Income Exemption Threshold claimed by employee in his/her Employee Declaration Form </td><td>" . number_format($rowValuesByEmployee['value'], 0, '.', ',') . "</td></tr>";

        }  

      } else {
        //echo "<td><input type = \"number\" readonly = \"readonly\" value = " . $rowemployee['edf_amount'] . " style=\"background:#FF0000;\" id = \"edfAmount" . $rowemployee['employeeid'] . "\"></td>";
        echo "<tr><td>Income Exemption Threshold claimed by employee in his/her Employee Declaration Form </td><td><b>" . number_format($rowpayroll['employee_edf_amount'], 0, '.', ',') . "</b></td></tr>";

      }


       // - -- end iet period





        //echo "<tr><td>Income Exemption Threshold claimed by employee in his/her Employee Declaration Form </td><td>" . number_format($rowpayroll['employee_edf_amount'], 0, '.', ',') . "</td></tr>";

        echo "<tr><td><center>TAX DEDUCTION</center></td></tr>";

       $totalTaxWithheld = $rowpayroll['payeEmp'];

        echo "<tr><td>Tax withheld and remitted to the Mauritius Revenue Authority </td><td>" . number_format($totalTaxWithheld, 0, '.', ',') . "</td></tr>";

        echo "<tr><td>";
        echo "<center>DECLARATION BY EMPLOYER</center><br>";

        echo "<center>.............................................................................</center><br>";

        echo "<center>(Full name of signatory in <b>BLOCK LETTERS</b>)</center><br>";

        echo "do hereby declare that the information I have given in this statement is true and correct.";

        echo "<br><br>";

        echo "</td></tr>";

        echo "<tr>";

        echo "<td>Date: ........................ <br> Signature ........................ <br>
        Capacity in which acting ........................ </td>";

        echo "</tr>";

        echo "</table>";

        echo "</div>";


       }

       

}


      }  else { 

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

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

  $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\">Generate Statement of Emoluments</h1>";
 
    
    	echo "</div>";

    	echo "<form method = \"POST\" action=\"report-getStatementOfEmoluments.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 "<p>All Employees <input type = \"checkbox\" name = \"getallemployees\"></p>";

      echo "<p>If Single Employee, Choose here:";

    if ($countEmployee !== 0) { 

      echo "<select name=\"getemployeeid\" id=\"getemployeeid\" class=\"form-control\">";

      while ($rowemployee = mysqli_fetch_array($resultEmployee)) {

        echo "<option value ='" . $rowemployee['employeeid'] . "'>" . $rowemployee['employeesurname'] . ", " . $rowemployee['employeename']  . "</option>";

      }

        echo "</select></p>";
  } else {
    echo "<p>No employee on system..</p>";
  }



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

    include('footer1.php');

   }




?>