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

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

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

          $startdate=$_POST['startdate'];
          $enddate=$_POST['enddate'];
          $getallemployees=$_POST['getallemployees'];
          $getemployeeid=$_POST['getemployeeid'];


          $sqlView = "SELECT 
                  employees.employeename AS employeename,
                  employees.employeesurname AS employeesurname,
                  employees.employeeid AS employeeid,
                  departments.departmentname AS departmentname,
                  positions.positionname AS positionname,
                  periods.periodName AS periodName,
                  payrollbasicdetails.netSalary AS netSalary,
                  payrollbasicdetails.basicSal AS basicSal,
                  payrollbasicdetails.travellingAmt AS travellingAmt,
                  payrollbasicdetails.specialBonus AS specialBonus,
                  payrollbasicdetails.commission AS commission,
                  payrollbasicdetails.overtime AS overtime,
                  payrollbasicdetails.totalPackage AS totalPackage,
                  payrollbasicdetails.absencesInput AS absencesInput,
                  payrollbasicdetails.absencesValue AS absencesValue,
                  payrollbasicdetails.nsfEmp AS nsfEmp,
                  payrollbasicdetails.payeEmp AS payeEmp,
                  payrollbasicdetails.totalDed AS totalDed,
                  payrollbasicdetails.employerCSG AS employerCSG,
                  payrollbasicdetails.employerNSF AS employerNSF,
                  payrollbasicdetails.employerLEVY AS employerLEVY,
                  payrollbasicdetails.employeeCSG AS employeeCSG,
                  payrollbasicdetails.employeeNSF AS employeeNSF,
                  payrollbasicdetails.totalCSGVal AS totalCSGVal,
                  payrollbasicdetails.totalNSFVal AS totalNSFVal,
                  payrollbasicdetails.totalMRAVal AS totalMRAVal
                  FROM payrollbasicdetails
                  LEFT JOIN employees
                  ON payrollbasicdetails.employeeid = employees.employeeid
                  LEFT JOIN employee_job
                  ON employee_job.employeeid = employees.employeeid
                  LEFT JOIN positions
                  ON employee_job.positionid = positions.positionid
                  LEFT JOIN departments
                  ON employee_job.departmentid = departments.departmentid
                  RIGHT JOIN periods
                  ON payrollbasicdetails.payrollid = periods.periodid
                  WHERE periods.startDate BETWEEN '$startdate' AND '$enddate'
                  AND periods.bonusBoolean = 0
                  AND employees.companyid = $companyID";

                  if ($getallemployees !== "on") {
                    $sqlAddonEmployee = " AND payrollbasicdetails.employeeid = '$getemployeeid';";
                    $sqlView = $sqlView . $sqlAddonEmployee;
                  }

                  

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

                  $count = mysqli_num_rows($result);

                   echo "<script src=\"https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js\"></script>";

                    echo "<script type=\"text/javascript\" src=\"js/tableExport.js\"></script>";
                    echo "<script type=\"text/javascript\" src=\"js/jquery.base64.js\"></script>";

                       echo "<style type=\"text/css\" media=\"print\">
          @page { size: landscape; }
        </style>";


                  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\">";

            if ($count !== 0) { // COUNT START HERE
              
                $sqlViewCompany = "SELECT *
                  FROM companyfile";
                

                  $resultCompany = mysqli_query($db,$sqlViewCompany);

                  $countCompany = mysqli_num_rows($resultCompany);

                  while ($rowCompany = mysqli_fetch_array($resultCompany)) {

                    echo "<h1 class=\"h2\">" . $rowCompany['companyName'] . "</h1>";

                   }

                  echo "<h1 class=\"h2\">Payroll Report - " . $startdate . " - " . $enddate . "</h1>";

                  if ($getallemployees !== "on") {
                    echo " Single Employee ";
                  }

                  echo "</h1>";
             
              echo "<div class=\"btn-toolbar mb-2 mb-md-0\">"; // TOOLBAR STARTS HERE

                      echo "<div class=\"btn-group mr-2\">";
                        echo "<a href=\"#\" class=\"btn btn-sm btn-outline-secondary sortialler\" role=\"button\" onclick=\"window.print()\">Print</a>";
                        echo "<a href=\"#\" class=\"btn btn-sm btn-outline-secondary sortialler\" role=\"button\" onclick=\"$('#tabletoexport').tableExport({type:'excel',escape:'false',headings:'true'});\">Export to Excel</a>";

                      echo "</div>";

                  echo "</div>";

            echo "</div>";

            // --- ALLOWANCES START HERE

            $allowances = array();

            $sqlAllowances = "SELECT *
            FROM benefits
            ORDER BY benefitname ASC;";

            $resultAllowances = mysqli_query($db,$sqlAllowances);

            $countAllowances = mysqli_num_rows($resultAllowances);

              if ($countAllowances !== 0) { 

                while ($rowAllowances = mysqli_fetch_array($resultAllowances)) {
                  $allowances[$rowAllowances['benefitid']] = $rowAllowances['benefitname'];
                }

               }

            // --- ALLOWANCES END HERE

            // --- DEDUCTIONS START HERE

            $deductions1 = array();

            $sqlDeductions1 = "SELECT *
            FROM deductions
            ORDER BY deductionname ASC;";

            $resultDeductions = mysqli_query($db,$sqlDeductions1);

            $countDeductions = mysqli_num_rows($resultAllowances);

              if ($countDeductions !== 0) { 

                while ($rowdDeductions = mysqli_fetch_array($resultDeductions)) {
                  $deductions1[$rowdDeductions['deductionid']] = $rowdDeductions['deductionname'];
                }

               }

            // --- DEDUCTIONS END HERE


            // TABLE SCREEN STARTS HERE

          echo "<table class = \"table sortialler\" border=\"1\" id = \"tabletoexport\">";

          echo "<thead>";
          echo "<th>Period Name</th>";
          echo "<th>Employee Name</th>";
          echo "<th>Department</th>";
          echo "<th>Position</th>";
          echo "<th>Basic Salary</th>";
          echo "<th>Bus Fare</th>";

          foreach (array_filter($allowances) as $benefitid => $benefitname) {
            echo "<th>" . $benefitname . "</th>";
          }

          echo "<th>Special Bonus</th>";
          echo "<th>Commission</th>";
          echo "<th>Overtime</th>";
          echo "<th>Total Pacakge</th>";
          echo "<th>Absences</th>";
          echo "<th>Deducted Absences</th>";
          echo "<th>NSF</th>";
          echo "<th>PAYE</th>";
          echo "<th>CSG Employee</th>";

          foreach (array_filter($deductions1) as $deductionid => $deductionname) {
            echo "<th>" . $deductionname . "</th>";
          }

          echo "<th>Total Deductions</th>";
          echo "<th>Net Salary</th>";
          echo "<th>CSG Employer</th>";
          echo "<th>NSF Employer</th>";
          echo "<th>LEVY Employer</th>";
          echo "<th>CSG Employee</th>";
          echo "<th>NSF Employee</th>";
          echo "<th>Total CSG</th>";
          echo "<th>Total NSF</th>";
          echo "<th>Total MRA</th>";
          echo "</thead>";
          echo "<tbody>";

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

          while ($rowpaysheet = mysqli_fetch_array($result)) { // FETCHING EMPLOYEE DATA START
            echo "<tr>";
            echo "<td>" . $rowpaysheet['periodName'] . "</td>";
            echo "<td>" . $rowpaysheet['employeesurname'] . ", " . $rowpaysheet['employeename'] . "</td>";
            echo "<td>" . $rowpaysheet['departmentname'] . "</td>";
            echo "<td>" . $rowpaysheet['positionname'] . "</td>";
                echo "<td>" . number_format($rowpaysheet['basicSal'], 2, '.', ',') . "</td>";
                    echo "<td>" . number_format($rowpaysheet['travellingAmt'], 2, '.', ',') . "</td>";

            //-- benefit display start
                  foreach (array_filter($allowances) as $benefitid => $benefitname) {
                    $sqlGetBenefit = "SELECT benefitvalue
                    FROM employee_benefits
                      WHERE employeeid = " . $rowpaysheet['employeeid'] . "
                      AND benefitid = $benefitid";

                        $resultbenefitvalue = mysqli_query($db,$sqlGetBenefit);

                        $countbenefitvalue = mysqli_num_rows($resultbenefitvalue);

                        if ($countbenefitvalue !== 0) { 

                          while ($rowBenefitValue = mysqli_fetch_array($resultbenefitvalue)) {
                              echo "<td>" . number_format($rowBenefitValue['benefitvalue'], 2, '.', ',') . "</td>";
                            }

                          } else {
                            echo "<td>0</td>";
                          }

                    } 
              // benefits end here


            echo "<td>" . number_format($rowpaysheet['specialBonus'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['commission'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['overtime'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalPackage'], 2, '.', ',') . "</td>";
            echo "<td>" . $rowpaysheet['absencesInput'] . "</td>";
            echo "<td>" . number_format($rowpaysheet['absencesValue'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['nsfEmp'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['payeEmp'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employeeCSG'], 2, '.', ',') . "</td>";



            // deductions display start
                $deductionsTotal = 0;
                foreach (array_filter($deductions1) as $deductionid => $deductionname) {
                  $sqlGetDeduction = "SELECT deductionvalue
                  FROM employee_deductions
                    WHERE employeeid = " . $rowpaysheet['employeeid'] . "
                    AND deductionid = $deductionid";

                      $resultdeductionvalue = mysqli_query($db,$sqlGetDeduction);

                      $countdeductionvalue = mysqli_num_rows($resultdeductionvalue);

                      if ($countdeductionvalue !== 0) { 

                        while ($rowDeductionValue = mysqli_fetch_array($resultdeductionvalue)) {
                            echo "<td>" . number_format($rowDeductionValue['deductionvalue'], 2, '.', ',') . "</td>";
                          }

                        } else {
                          echo "<td>0</td>";
                        }

                  } 
            // deductions display ends here

                    $totaldeductiondisplayed = $rowpaysheet['totalDed'] + $rowpaysheet['employeeCSG'];

            echo "<td>" . number_format($totaldeductiondisplayed, 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['netSalary'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employerCSG'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employerNSF'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employerLEVY'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employeeCSG'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employeeNSF'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalCSGVal'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalNSFVal'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalMRAVal'], 2, '.', ',') . "</td>";


           
            echo "</tr>";


          } // FETCHING EMPLOYEE DATA END

          echo "</tbody>";
          echo "</table>";

            // TABLE SCREEN ENDS HERE


// TABLE 1 STARTS HERE

          echo "<table class = \"table sortirester pagebreak\" border=\"1\" id = \"tabletoexport\">";

          echo "<thead>";
          echo "<th>Period Name</th>";
          echo "<th>Employee Name</th>";
          echo "<th>Department</th>";
          echo "<th>Position</th>";
          echo "<th>Basic Salary</th>";
          echo "<th>Bus Fare</th>";

          foreach (array_filter($allowances) as $benefitid => $benefitname) {
            echo "<th>" . $benefitname . "</th>";
          }

          echo "<th>Special Bonus</th>";
          echo "<th>Commission</th>";
          echo "<th>Overtime</th>";
          echo "<th>Total Pacakge</th>";
          echo "</thead>";
          echo "<tbody>";

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

          while ($rowpaysheet = mysqli_fetch_array($result)) { // FETCHING EMPLOYEE DATA START
            echo "<tr>";
            echo "<td>" . $rowpaysheet['periodName'] . "</td>";
            echo "<td>" . $rowpaysheet['employeesurname'] . ", " . $rowpaysheet['employeename'] . "</td>";
            echo "<td>" . $rowpaysheet['departmentname'] . "</td>";
            echo "<td>" . $rowpaysheet['positionname'] . "</td>";
                echo "<td>" . number_format($rowpaysheet['basicSal'], 2, '.', ',') . "</td>";
                    echo "<td>" . number_format($rowpaysheet['travellingAmt'], 2, '.', ',') . "</td>";

            //-- benefit display start
                  foreach (array_filter($allowances) as $benefitid => $benefitname) {
                    $sqlGetBenefit = "SELECT benefitvalue
                    FROM employee_benefits
                      WHERE employeeid = " . $rowpaysheet['employeeid'] . "
                      AND benefitid = $benefitid";

                        $resultbenefitvalue = mysqli_query($db,$sqlGetBenefit);

                        $countbenefitvalue = mysqli_num_rows($resultbenefitvalue);

                        if ($countbenefitvalue !== 0) { 

                          while ($rowBenefitValue = mysqli_fetch_array($resultbenefitvalue)) {
                              echo "<td>" . number_format($rowBenefitValue['benefitvalue'], 2, '.', ',') . "</td>";
                            }

                          } else {
                            echo "<td>0</td>";
                          }

                    } 
              // benefits end here


            echo "<td>" . number_format($rowpaysheet['specialBonus'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['commission'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['overtime'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalPackage'], 2, '.', ',') . "</td>";


           
            echo "</tr>";


          } // FETCHING EMPLOYEE DATA END

          echo "</tbody>";
          echo "</table>";

            // TABLE 1 ENDS HERE





          // TABLE 2 STARTS HERE

          echo "<table class = \"table sortirester pagebreak\" border=\"1\" id = \"tabletoexport\">";

          echo "<thead>";
          echo "<th>Period Name</th>";
          echo "<th>Employee Name</th>";
          echo "<th>Absences</th>";
          echo "<th>Deducted Absences</th>";
          echo "<th>NSF</th>";
          echo "<th>PAYE</th>";
          echo "<th>CSG Employee</th>";

          foreach (array_filter($deductions1) as $deductionid => $deductionname) {
            echo "<th>" . $deductionname . "</th>";
          }

          echo "<th>Total Deductions</th>";
          echo "<th>Net Salary</th>";
          echo "</thead>";
          echo "<tbody>";

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

          while ($rowpaysheet = mysqli_fetch_array($result)) { // FETCHING EMPLOYEE DATA START
            echo "<tr>";
            echo "<td>" . $rowpaysheet['periodName'] . "</td>";
            echo "<td>" . $rowpaysheet['employeesurname'] . ", " . $rowpaysheet['employeename'] . "</td>";
            echo "<td>" . $rowpaysheet['absencesInput'] . "</td>";
            echo "<td>" . number_format($rowpaysheet['absencesValue'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['nsfEmp'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['payeEmp'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employeeCSG'], 2, '.', ',') . "</td>";



            // deductions display start
                $deductionsTotal = 0;
                foreach (array_filter($deductions1) as $deductionid => $deductionname) {
                  $sqlGetDeduction = "SELECT deductionvalue
                  FROM employee_deductions
                    WHERE employeeid = " . $rowpaysheet['employeeid'] . "
                    AND deductionid = $deductionid";

                      $resultdeductionvalue = mysqli_query($db,$sqlGetDeduction);

                      $countdeductionvalue = mysqli_num_rows($resultdeductionvalue);

                      if ($countdeductionvalue !== 0) { 

                        while ($rowDeductionValue = mysqli_fetch_array($resultdeductionvalue)) {
                            echo "<td>" . number_format($rowDeductionValue['deductionvalue'], 2, '.', ',') . "</td>";
                          }

                        } else {
                          echo "<td>0</td>";
                        }

                  } 
            // deductions display ends here

                    $totaldeductiondisplayed = $rowpaysheet['totalDed'] + $rowpaysheet['employeeCSG'];

            echo "<td>" . number_format($totaldeductiondisplayed, 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['netSalary'], 2, '.', ',') . "</td>";


           
            echo "</tr>";


          } // FETCHING EMPLOYEE DATA END

          echo "</tbody>";
          echo "</table>";

            // TABLE 2 ENDS HERE

          // TABLE 3 STARTS HERE

          echo "<table class = \"table sortirester pagebreak\" border=\"1\" id = \"tabletoexport\">";

          echo "<thead>";
          echo "<th>Period Name</th>";
          echo "<th>Employee Name</th>";
          echo "<th>CSG Employer</th>";
          echo "<th>NSF Employer</th>";
          echo "<th>LEVY Employer</th>";
          echo "<th>CSG Employee</th>";
          echo "<th>NSF Employee</th>";
          echo "<th>Total CSG</th>";
          echo "<th>Total NSF</th>";
          echo "<th>Total MRA</th>";
          echo "</thead>";
          echo "<tbody>";

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

          while ($rowpaysheet = mysqli_fetch_array($result)) { // FETCHING EMPLOYEE DATA START
            echo "<tr>";
            echo "<td>" . $rowpaysheet['periodName'] . "</td>";
            echo "<td>" . $rowpaysheet['employeesurname'] . ", " . $rowpaysheet['employeename'] . "</td>";

            echo "<td>" . number_format($rowpaysheet['employerCSG'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employerNSF'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employerLEVY'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employeeCSG'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employeeNSF'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalCSGVal'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalNSFVal'], 2, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalMRAVal'], 2, '.', ',') . "</td>";


           
            echo "</tr>";


          } // FETCHING EMPLOYEE DATA END

          echo "</tbody>";
          echo "</table>";

            // TABLE 3 ENDS HERE




            } // COUNT EMPLOYEES END HERE


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

  $sqlGetPeriod = "SELECT * FROM periods WHERE bonusBoolean = 0 AND companyid = $companyID AND status = 2 ";

  $resultPeriod = mysqli_query($db,$sqlGetPeriod);

  $countPeriod = mysqli_num_rows($resultPeriod);

  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\">Paysheet by Period</h1>";
 
    
    	echo "</div>";

    	echo "<form method = \"GET\" action=\"viewPaysheet.php\">";

      

  if ($countPeriod !== 0) { 
    echo "<p>Select Period";

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

    while ($rowPeriod = mysqli_fetch_array($resultPeriod)) {

      echo "<option value ='" . $rowPeriod['periodid'] . "'>" . $rowPeriod['periodName'] .  "</option>";

    }

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

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

   }



include('footer1.php');
?>