<?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'];

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

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

                  $countPeriods = mysqli_num_rows($resultPeriodCounts);


          if(isset($_POST['getallemployees'])) {

            $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,
                  sum(payrollbasicdetails.netSalary) AS netSalary,
                  sum(payrollbasicdetails.basicSal) AS basicSal,
                  sum(payrollbasicdetails.travellingAmt) AS travellingAmt,
                  sum(payrollbasicdetails.specialBonus) AS specialBonus,
                  sum(payrollbasicdetails.commission) AS commission,                  
                  sum(payrollbasicdetails.taxableAllowance) AS taxableAllowance,
                  sum(payrollbasicdetails.nonTaxableAllowance) AS nonTaxableAllowance,
                  sum(payrollbasicdetails.housingallowance) AS housingallowance,
                  sum(payrollbasicdetails.telephoneallowance) AS telephoneallowance,
                  sum(payrollbasicdetails.driverallowance) AS driverallowance,
                  sum(payrollbasicdetails.otherallowance) AS otherallowance,
                  sum(payrollbasicdetails.totalcarallowance) AS totalcarallowance,
                  sum(payrollbasicdetails.loan) AS loan,
                  sum(payrollbasicdetails.medical) AS medical,
                  sum(payrollbasicdetails.overtime) AS overtime,
                  sum(payrollbasicdetails.totalPackage) AS totalPackage,
                  sum(payrollbasicdetails.absencesInput) AS absencesInput,
                  sum(payrollbasicdetails.absencesValue) AS absencesValue,
                  sum(payrollbasicdetails.nsfEmp) AS nsfEmp,
                  sum(payrollbasicdetails.payeEmp) AS payeEmp,
                  sum(payrollbasicdetails.oneOffDeduction) AS oneOffDeduction,
                  sum(payrollbasicdetails.totalDed) AS totalDed,
                  sum(payrollbasicdetails.employerCSG) AS employerCSG,
                  sum(payrollbasicdetails.employerNSF) AS employerNSF,
                  sum(payrollbasicdetails.employerLEVY) AS employerLEVY,
                  sum(payrollbasicdetails.employeeCSG) AS employeeCSG,
                  sum(payrollbasicdetails.employeeNSF) AS employeeNSF,
                  sum(payrollbasicdetails.totalCSGVal) AS totalCSGVal,
                  sum(payrollbasicdetails.totalNSFVal) AS totalNSFVal,
                  sum(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(isset($getallemployees)) {
                    $sqlView = $sqlView . " GROUP BY employees.employeeid";
                  } else {
                      $sqlAddonEmployee = " AND payrollbasicdetails.employeeid = '$getemployeeid';";
                      $sqlView = $sqlView . $sqlAddonEmployee;

                  }

                  

                  
                  

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

                  $count = mysqli_num_rows($result);

                  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 "<h2 class=\"h3\">" . $rowCompany['companyName'] . "</h2>";

                   }




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

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

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

                  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 view STARTS HERE

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

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

          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 "<td>Special Bonus</td>";
          echo "<td>Commission</td>";
          echo "<td>Overtime</td>";
          echo "<td>Total Pacakge</td>";
          echo "<td>Absences</td>";
          echo "<td>Deducted Absences</td>";
          echo "<td>NSF</td>";
          echo "<td>PAYE</td>";
          echo "<td>CSG Employee</td>";

          echo "<th>Loan</th>";
          echo "<th>Medical</th>";
          echo "<th>One-Off Deduction</th>";

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

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

          while ($rowpaysheet = mysqli_fetch_array($result)) { // FETCHING EMPLOYEE DATA START
            echo "<tr>";
            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>" . number_format($rowpaysheet['absencesInput'], 0, '.', ',') . "</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['payeEmp'], 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['totalMRAVal'], 0, '.', ',') . "</td>";


           
            echo "</tr>";


          } // FETCHING EMPLOYEE DATA END

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

            // TABLE view ENDS HERE


                      // TABLE 1 STARTS HERE

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

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


  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 "<td>Special Bonus</td>";
          echo "<td>Commission</td>";
          echo "<td>Overtime</td>";
          echo "<td>Total Pacakge</td>";
          echo "</thead>";
          echo "<tbody>";

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

          while ($rowpaysheet = mysqli_fetch_array($result)) { // FETCHING EMPLOYEE DATA START
            echo "<tr>";
            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 "<tr><td>Page 1</td></tr>";
          echo "</tbody>";
          echo "</table>";

            // TABLE 1 ENDS HERE

          // TABLE 2 STARTS HERE

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

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


  echo "<th>Loan</th>";
  echo "<th>Medical</th>";


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

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

          while ($rowpaysheet = mysqli_fetch_array($result)) { // FETCHING EMPLOYEE DATA START
            echo "<tr>";
            echo "<td>" . $rowpaysheet['employeesurname'] . ", " . $rowpaysheet['employeename'] . "</td>";
            echo "<td>" . number_format($rowpaysheet['absencesInput'], 0, '.', ',') . "</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 "<tr><td>Page 2</td></tr>";
          echo "</tbody>";
          echo "</table>";

            // TABLE 2 ENDS HERE

          // TABLE 3 STARTS HERE

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

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

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

          while ($rowpaysheet = mysqli_fetch_array($result)) { // FETCHING EMPLOYEE DATA START
            echo "<tr>";
            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['payeEmp'], 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['totalMRAVal'], 0, '.', ',') . "</td>";


           
            echo "</tr>";


          } // FETCHING EMPLOYEE DATA END
          echo "<tr><td>Page 3</td></tr>";
          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\">Cummulative Payroll by Period</h1>";
 
    
    	echo "</div>";

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