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

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

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

  $payrollid = $_GET['payrollID'];

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

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

  $countPayroll = mysqli_num_rows($resultPayroll);

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

    $updated = $_GET['updated'];

  }
  $sqlView = "SELECT 
  employees.employeename AS employeename,
  employees.employeesurname AS employeesurname,
  employees.employeeid AS employeeid,          
  employees.employee_join_date AS joinDate,
  employees.employee_termination_date AS terminationDate,
  departments.departmentname AS departmentname,
  positions.positionname AS positionname,
  payrollbasicdetails.s_taken AS s_taken,
  payrollbasicdetails.s_balance AS s_balance,
  payrollbasicdetails.l_taken AS l_taken,
  payrollbasicdetails.l_balance AS l_balance,
  payrollbasicdetails.WorkingDaysValue AS WorkingDaysValue,
  payrollbasicdetails.numberWrkDays AS numberWrkDays,
  payrollbasicdetails.netSalary AS netSalary,
  payrollbasicdetails.basicSal AS basicSal,
  payrollbasicdetails.travellingAmt AS travellingAmt,
  payrollbasicdetails.taxableAllowance AS taxableAllowance,
  payrollbasicdetails.nonTaxableAllowance AS nonTaxableAllowance,
  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.solidaritylevy AS solidaritylevy,
  payrollbasicdetails.oneOffDeduction AS oneOffDeduction,
  payrollbasicdetails.totalDed AS totalDed,
  payrollbasicdetails.employerCSG AS employerCSG,
  payrollbasicdetails.employerPRGF AS employerPRGF,
  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
  WHERE payrollbasicdetails.payrollid = $payrollid";


  
    $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 "<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 EMPLOYEES START HERE
      $periodSelectID = 0;
      while ($rowperiods = mysqli_fetch_array($resultPayroll)) { 
        $periodSelectID = $rowperiods['periodid'];

        $payrollStart = $rowperiods['startDate'];
          $payrollEnd = $rowperiods['endDate'];

        $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 - " . $rowperiods['startDate'] . " - " . $rowperiods['endDate'] . "</h1>";
      }

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

        echo "<div class=\"btn-group mr-2\">";
          echo "<a href=\"payrolls.php\" class=\"btn btn-sm btn-outline-secondary\" role=\"button\">Back</a>";
          echo "<a href=\"printPayroll.php?payrollID=" . $periodSelectID ."\" class=\"btn btn-sm btn-outline-secondary\" role=\"button\">Print</a>";
          echo "<a href=\"#\" class=\"btn btn-sm btn-outline-secondary\" 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

    echo "<style>td {text-align: right;} </style>";
    // TABLE 1 STARTS HERE
  echo "<div class=\"pagebreak\">";
 echo "<table class = \"table\" border=\"1\" id = \"tabletoexport\">";

  echo "<thead>";
  echo "<th>Employee Name</th>";
  //echo "<th>Department</td>";
  //echo "<th>Position</th>";
  echo "<th>Sick Leaves Taken</th>";
  echo "<th>Local Leaves Taken</th>";
  //echo "<th>Working Days</th>";
  echo "<th>Presence</th>";
  echo "<th>UnAuth Absences (Days)</th>";
  echo "<th>Basic Salary</th>";
  echo "<th>Bus Fare</th>";
  echo "<th>Taxable Allowance</th>";
  echo "<th>Non Taxable Allowance</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 Package</th>";

  $sumbasicsalary = 0.0;
  $sumbusfare = 0.0;

  $sumtaxableallowance = 0.0;
  $sumnontaxableallowance = 0.0;

  $sumhousingallowance = 0.0;
  $sumdriverallowance = 0.0;
  $sumotherallowance = 0.0;
  $sumtotalcarallowance = 0.0;

  $sumtaxablecarallowance = 0.0;

  $sumexceptcarallowance = 0.0;
  $sumtelephoneallowance = 0.0;
  $sumspecialbonus = 0.0;
  $sumcommission = 0.0;
  $sumovertime = 0.0;
  $sumtotalpackage = 0.0;

  $sumdeductabs = 0.0;
  $sumnsf = 0.0;
  $sumemployeecsg = 0.0;
  $sumloan = 0.0;
  $summedical = 0.0;
  $sumoneoffdeduction = 0.0;
  $sumtotaldeduction = 0.0;
  $sumnetsalary = 0.0;

  $sumcsgemployer = 0.0;
  $sumnsfemployer = 0.0;
  $sumlevyemployer = 0.0;
  $sumprgfemployer = 0.0;
  $sumemployeensf = 0.0;
  $sumpaye = 0.0;
  $sumsolidaritylevy = 0.0;
  $sumtotalcsg = 0.0;
  $sumtotalnsf = 0.0;
  $sumtotalmra = 0.0;

  echo "<th>Unauth. Absences (Days)</th>";
  echo "<th>Deducted Absences</th>";
  echo "<th>NSF</th>";
  echo "<th>PAYE</th>";
  echo "<th>Solidarity Levy</th>";
  echo "<th>CSG Employee</th>";

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

  echo "<th>One Off Deductions</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>PAYE Employee</th>";
  echo "<th>Solidarity Levy</th>";
  echo "<th style=\"background-color:#DCDCDC\">Total CSG</th>";
  echo "<th style=\"background-color:#DCDCDC\">Total NSF</th>";
  echo "<th>Total MRA</th>";



  
  echo "</thead>";
  echo "<tbody>";

  while ($rowpaysheet = mysqli_fetch_array($result)) { // FETCHING EMPLOYEE DATA START

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

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

    echo "<tr>";


    echo "<td>" . $rowpaysheet['employeesurname'] . ", " . $rowpaysheet['employeename'] . "<br><i>" . $rowpaysheet['departmentname'] . "</i></td>";
    //echo "<td>" . $rowpaysheet['departmentname'] . "</td>";
    //echo "<td>" . $rowpaysheet['positionname'] . "</td>";

    echo "<td>" . $rowpaysheet['s_taken'] . "</td>";
    echo "<td>" . $rowpaysheet['l_taken'] . "</td>";
    //echo "<td>" . $rowpaysheet['WorkingDaysValue'] . "</td>";
    echo "<td>" . $rowpaysheet['numberWrkDays'] . "</td>";
    echo "<td>" . $rowpaysheet['absencesInput'] . "</td>";
    

    echo "<td>" . number_format($rowpaysheet['basicSal'], 2, '.', ',') . "</td>";
    $sumbasicsalary += $rowpaysheet['basicSal'];
    echo "<td>" . number_format($rowpaysheet['travellingAmt'], 2, '.', ',') . "</td>";
    $sumbusfare += $rowpaysheet['travellingAmt'];

    echo "<td>" . number_format($rowpaysheet['taxableAllowance'], 2, '.', ',') . "</td>";
    $sumtaxableallowance += $rowpaysheet['taxableAllowance'];

    echo "<td>" . number_format($rowpaysheet['nonTaxableAllowance'], 2, '.', ',') . "</td>";
    $sumnontaxableallowance += $rowpaysheet['nonTaxableAllowance'];

    //-- 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>";
                      //echo $benefitname;
                    
                      if ($benefitname == "Taxable Allowance" ) {
                        $sumtaxableallowance += $rowBenefitValue['benefitvalue'];
                      } else if ($benefitname == "Non Taxable Allowance" ) {
                        $sumnontaxableallowance += $rowBenefitValue['benefitvalue'];
                      } else if ($benefitname == "House Allowance" ) {
                        $sumhousingallowance += $rowBenefitValue['benefitvalue'];
                      } else if ($benefitname == "Driver Allowance" ) {
                        $sumdriverallowance += $rowBenefitValue['benefitvalue'];
                      } else if ($benefitname == "Other Allowance" ) {
                        $sumotherallowance += $rowBenefitValue['benefitvalue'];
                      } else if ($benefitname == "Taxable Car Allowance" ) {
                        $sumtaxablecarallowance += $rowBenefitValue['benefitvalue'];
                      } else if ($benefitname == "Telephone Allowance" ) {
                        $sumtelephoneallowance += $rowBenefitValue['benefitvalue'];
                      } else if ($benefitname == "Excempt Car Allowance" ) {
                        $sumexceptcarallowance += $rowBenefitValue['benefitvalue'];
                      } 
                      
                    }

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

            } 
      // benefits end here


    echo "<td>" . number_format($rowpaysheet['specialBonus'], 2, '.', ',') . "</td>";
    $sumspecialbonus += $rowpaysheet['specialBonus'];
    echo "<td>" . number_format($rowpaysheet['commission'], 2, '.', ',') . "</td>";
    $sumcommission += $rowpaysheet['commission'];
    echo "<td>" . number_format($rowpaysheet['overtime'], 2, '.', ',') . "</td>";
    $sumovertime += $rowpaysheet['overtime'];
    echo "<td>" . number_format($rowpaysheet['totalPackage'], 2, '.', ',') . "</td>";
    $sumtotalpackage += $rowpaysheet['totalPackage'];


            // -----

            echo "<td>" . $rowpaysheet['absencesInput'] . "</td>";
            echo "<td>" . $rowpaysheet['absencesValue'] . "</td>";
            $sumdeductabs += $rowpaysheet['absencesValue'];
            echo "<td>" . number_format($rowpaysheet['nsfEmp'], 2, '.', ',') . "</td>";
            $sumnsf += $rowpaysheet['nsfEmp'];
            echo "<td>" . number_format($rowpaysheet['payeEmp'], 2, '.', ',') . "</td>";
            //$sumpaye += $rowpaysheet['payeEmp'];
            echo "<td>" . number_format($rowpaysheet['solidaritylevy'], 2, '.', ',') . "</td>";
            //$sumsolidaritylevy += $rowpaysheet['solidaritylevy'];
            echo "<td>" . number_format($rowpaysheet['employeeCSG'], 2, '.', ',') . "</td>";
            //$sumemployeecsg += $rowpaysheet['employeeCSG'];
        
        
            // 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>";
        
                            if ($deductionname == "Loan" ) {
                              $sumloan += $rowDeductionValue['deductionvalue'];
                            } else if ($deductionname == "Medical" ) {
                              $summedical += $rowDeductionValue['deductionvalue'];
                            } 
        
                          }
        
                        } else {
                          echo "<td>0</td>";
                        }
        
                  } 
            // deductions display ends here
        
        
            echo "<td>" . number_format($rowpaysheet['oneOffDeduction'], 2, '.', ',') . "</td>";
            $sumoneoffdeduction += $rowpaysheet['oneOffDeduction'];
        
                    $totaldeductiondisplayed = $rowpaysheet['totalDed'] ;
        
            echo "<td>" . number_format($totaldeductiondisplayed, 2, '.', ',') . "</td>";
            $sumtotaldeduction += $totaldeductiondisplayed;
            echo "<td>" . number_format($rowpaysheet['netSalary'], 2, '.', ',') . "</td>";
            $sumnetsalary += $rowpaysheet['netSalary'];
        

            //---

            
    echo "<td>" . number_format($rowpaysheet['employerCSG'], 2, '.', ',') . "</td>";
    $sumcsgemployer += $rowpaysheet['employerCSG'];
    echo "<td>" . number_format($rowpaysheet['employerNSF'], 2, '.', ',') . "</td>";
    $sumnsfemployer += $rowpaysheet['employerNSF'];
    echo "<td>" . number_format($rowpaysheet['employerLEVY'], 2, '.', ',') . "</td>";
    $sumlevyemployer += $rowpaysheet['employerLEVY'];
    echo "<td>" . number_format($rowpaysheet['employerPRGF'], 2, '.', ',') . "</td>";
    $sumprgfemployer += $rowpaysheet['employerPRGF'];
    echo "<td>" . number_format($rowpaysheet['employeeCSG'], 2, '.', ',') . "</td>";
    $sumemployeecsg += $rowpaysheet['employeeCSG'];
    echo "<td>" . number_format($rowpaysheet['employeeNSF'], 2, '.', ',') . "</td>";
    $sumemployeensf += $rowpaysheet['employeeNSF'];
    echo "<td>" . number_format($rowpaysheet['payeEmp'], 2, '.', ',') . "</td>";
    $sumpaye += $rowpaysheet['payeEmp'];
    echo "<td>" . number_format($rowpaysheet['solidaritylevy'], 2, '.', ',') . "</td>";
    $sumsolidaritylevy += $rowpaysheet['solidaritylevy'];
    echo "<td style=\"background-color:#DCDCDC\">" . number_format($rowpaysheet['totalCSGVal'], 2, '.', ',') . "</td>";
    $sumtotalcsg += $rowpaysheet['totalCSGVal'];
    echo "<td style=\"background-color:#DCDCDC\">" . number_format($rowpaysheet['totalNSFVal'], 2, '.', ',') . "</td>";
    $sumtotalnsf += $rowpaysheet['totalNSFVal'];
    echo "<td>" . number_format($rowpaysheet['totalMRAVal'], 2, '.', ',') . "</td>";
    $sumtotalmra += $rowpaysheet['totalMRAVal'];

   
    
   
    echo "</tr>";


  } // FETCHING EMPLOYEE DATA END

  echo "<tr style=\"font-weight:bold\">";

  echo "<td></td>";
  echo "<td></td>";
  echo "<td></td>";
  echo "<td></td>";
  echo "<td></td>";
  echo "<td>" . number_format($sumbasicsalary, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumbusfare, 2, '.', ',') . "</td>";

  echo "<td>" . number_format($sumtaxableallowance, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumnontaxableallowance, 2, '.', ',') . "</td>";

  echo "<td>" . number_format($sumdriverallowance, 2, '.', ',') . "</td>";

  echo "<td>" . number_format($sumexceptcarallowance, 2, '.', ',') . "</td>";

  echo "<td>" . number_format($sumhousingallowance, 2, '.', ',') . "</td>";

  echo "<td>" . number_format($sumotherallowance, 2, '.', ',') . "</td>";

  echo "<td>" . number_format($sumtaxablecarallowance, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumtelephoneallowance, 2, '.', ',') . "</td>";
  
  echo "<td>" . number_format($sumspecialbonus, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumcommission, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumovertime, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumtotalpackage, 2, '.', ',') . "</td>";

  echo "<td></td>";
  echo "<td>" . number_format($sumdeductabs, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumnsf, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumpaye, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumsolidaritylevy, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumemployeecsg, 2, '.', ',') . "</td>";
  
  
  echo "<td>" . number_format($sumloan, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($summedical, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumoneoffdeduction, 2, '.', ',') . "</td>";

  echo "<td>" . number_format($sumtotaldeduction, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumnetsalary, 2, '.', ',') . "</td>";

  echo "<td>" . number_format($sumcsgemployer, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumnsfemployer, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumlevyemployer, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumprgfemployer, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumemployeecsg, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumemployeensf, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumpaye, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumsolidaritylevy, 2, '.', ',') . "</td>";
  echo "<td style=\"background-color:#DCDCDC\">" . number_format($sumtotalcsg, 2, '.', ',') . "</td>";
  echo "<td style=\"background-color:#DCDCDC\">" . number_format($sumtotalnsf, 2, '.', ',') . "</td>";
  echo "<td>" . number_format($sumtotalmra, 2, '.', ',') . "</td>";
  


  echo "</tr>";





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

    // TABLE ENDS HERE


    } // COUNT EMPLOYEES END HERE

} // GET ENDS HERE

include('footer1.php');

?>