<?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.taxableAllowance AS taxableAllowance,
                  payrollbasicdetails.nonTaxableAllowance AS nonTaxableAllowance,
                  payrollbasicdetails.housingallowance AS housingallowance,
                  payrollbasicdetails.telephoneallowance AS telephoneallowance,
                  payrollbasicdetails.driverallowance AS driverallowance,
                  payrollbasicdetails.otherallowance AS otherallowance,
                  payrollbasicdetails.totalcarallowance AS totalcarallowance,
                  payrollbasicdetails.loan AS loan,
                  payrollbasicdetails.medical AS medical,
                  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.oneOffDeduction AS oneOffDeduction,
                  payrollbasicdetails.totalDed AS totalDed,
                  payrollbasicdetails.employerCSG AS employerCSG,
                  payrollbasicdetails.employerNSF AS employerNSF,
                  payrollbasicdetails.employerLEVY AS employerLEVY,
                  payrollbasicdetails.employerPRGF AS employerPRGF,
                  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
                  WHERE companyid = $companyID";
                

                  $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>";
          
          echo "<th>Taxable Allowance</th>";
          echo "<th>Non-Taxable Allowance</th>";
          echo "<th>Housing Allowance</th>";
          echo "<th>Telephone Allowance</th>";
          echo "<th>Driver Allowance</th>";
          echo "<th>Other Allowance</th>";
          echo "<th>Total Car Allowance</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>";

          
          echo "<th>Loan</th>";
          echo "<th>Medical</th>";
          echo "<th>One-Off Deduction</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>PRGF 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 PAYE</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'], 0, '.', ',') . "</td>";
                    echo "<td>" . number_format($rowpaysheet['travellingAmt'], 0, '.', ',') . "</td>";

                    
    echo "<td>" . number_format($rowpaysheet['taxableAllowance'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['nonTaxableAllowance'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['housingallowance'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['telephoneallowance'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['driverallowance'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['otherallowance'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['totalcarallowance'], 0, '.', ',') . "</td>";


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



          echo "<td>" . number_format($rowpaysheet['loan'], 0, '.', ',') . "</td>";
          echo "<td>" . number_format($rowpaysheet['medical'], 0, '.', ',') . "</td>";
          echo "<td>" . number_format($rowpaysheet['oneOffDeduction'], 0, '.', ',') . "</td>";

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

            echo "<td>" . number_format($totaldeductiondisplayed, 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['netSalary'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employerCSG'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employerNSF'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employerLEVY'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employerPRGF'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employeeCSG'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employeeNSF'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalCSGVal'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalNSFVal'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['payeEmp'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalMRAVal'], 0, '.', ',') . "</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>";

  echo "<th>Taxable Allowance</th>";
  echo "<th>Non-Taxable Allowance</th>";
  echo "<th>Housing Allowance</th>";
  echo "<th>Telephone Allowance</th>";
  echo "<th>Driver Allowance</th>";
  echo "<th>Other Allowance</th>";
  echo "<th>Total Car Allowance</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'], 0, '.', ',') . "</td>";
                    echo "<td>" . number_format($rowpaysheet['travellingAmt'], 0, '.', ',') . "</td>";


    echo "<td>" . number_format($rowpaysheet['taxableAllowance'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['nonTaxableAllowance'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['housingallowance'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['telephoneallowance'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['driverallowance'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['otherallowance'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['totalcarallowance'], 0, '.', ',') . "</td>";


            echo "<td>" . number_format($rowpaysheet['specialBonus'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['commission'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['overtime'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalPackage'], 0, '.', ',') . "</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>";

        
  echo "<th>Loan</th>";
  echo "<th>Medical</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'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['nsfEmp'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['payeEmp'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employeeCSG'], 0, '.', ',') . "</td>";



    echo "<td>" . number_format($rowpaysheet['loan'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['medical'], 0, '.', ',') . "</td>";
    echo "<td>" . number_format($rowpaysheet['oneOffDeduction'], 0, '.', ',') . "</td>";

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

            echo "<td>" . number_format($totaldeductiondisplayed, 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['netSalary'], 0, '.', ',') . "</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 PAYE</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'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employerNSF'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employerLEVY'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employeeCSG'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['employeeNSF'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalCSGVal'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalNSFVal'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['payeEmp'], 0, '.', ',') . "</td>";
            echo "<td>" . number_format($rowpaysheet['totalMRAVal'], 0, '.', ',') . "</td>";


           
            echo "</tr>";


          } // FETCHING EMPLOYEE DATA END

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

            // TABLE 3 ENDS HERE




            } // COUNT EMPLOYEES END HERE


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

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

    	echo "<form method = \"POST\" action=\"report-payrollByPeriod.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');
?>