<?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"] == "POST") { 

          $startdate=$_POST['startdate'];
          $enddate=$_POST['enddate'];
          $numofmonths=$_POST['numofmonths'];


          $sqlView = "SELECT 
                  employees.employeename AS employeename,
                  employees.employeesurname AS employeesurname,
                  employees.employeeid AS employeeid,
                  employees.employee_paye_rate AS employee_paye_rate,
                  employees.employee_edf_amount AS employee_edf_amount,
                  employees.edf_months_value AS edf_months_value,
                  departments.departmentname AS departmentname,
                  positions.positionname AS positionname,
                  SUM(carbenefits.tiervalue) AS carbenefitvalue,
                  SUM(payrollbasicdetails.basicSal) AS basicSal,                  
                  SUM(payrollbasicdetails.specialBonus) AS specialBonus,                  
                  SUM(payrollbasicdetails.commission) AS commission,                  
                  SUM(payrollbasicdetails.absencesValue) AS absencesValue,                  
                  SUM(payrollbasicdetails.totalPackage) AS totalPackage,                  
                  SUM(payrollbasicdetails.overtime) AS overtime,
                  SUM(payrollbasicdetails.totalcarallowance) AS totalcarallowance
                  FROM payrollbasicdetails
                  LEFT JOIN employees
                  ON payrollbasicdetails.employeeid = employees.employeeid
                  LEFT JOIN carbenefits
                  ON employees.employee_carbenefit = carbenefits.carbenefitid
                  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 employees.companyid = $companyID
                  GROUP BY employeename";
                

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

                  $count = mysqli_num_rows($result);

                  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
              
                  echo "<h1 class=\"h2\">Bonus - " . $startdate . " - " . $enddate . "</h1>";

                   $checkEntry = "SELECT * FROM periods WHERE startDate = '$startdate' AND endDate = '$enddate' AND bonusBoolean = '1' AND companyid = '$companyID';";

                  $resultCheck = mysqli_query($db,$checkEntry);

                  $resultCheckcount = mysqli_num_rows($resultCheck);

                  if ($resultCheckcount == '0') {

                    $insertbonusdata=" INSERT INTO periods (
                      companyid,
                    startDate,
                    endDate,
                    bonusBoolean)
                    VALUES( 
                      '" . $companyID . "',
                    '" . $startdate . "',
                    '" . $enddate . "',
                    1) ";

            
                    if(mysqli_query($db, $insertbonusdata)) {
                      echo 'Bonus Data for employee inserted successfully';
                      $last_id = mysqli_insert_id($db);
                      echo "New record created successfully. Last inserted ID is: " . $last_id;
                      } else {
                         echo "Error: " . mysqli_error($db);
                         echo "Bonus could not be added to database";
                    }

                  }

                  

                  echo "</h1>";
             
              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 STARTS HERE

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

          echo "<thead>";
          echo "<td>Employee Name</td>";
          echo "<td>Department</td>";
          echo "<td>Position</td>";
          echo "<td>Basic Salary - SUM</td>";
          echo "<td>Special Bonus - SUM</td>";
          echo "<td>Allowances - SUM</td>";
          echo "<td>Commission - SUM</td>";
          echo "<td>Absences - SUM</td>";
          echo "<td>Overtime - SUM</td>";          
          echo "<td>PAYE</td>";
          //echo "<td>PRGF</td>";
          echo "<td>Employee CSG</td>";
          //echo "<td>Employer CSG</td>";
          echo "<td>Bonus (Total Package - CarBenefit - TCA)</td>";
          echo "</thead>";
          echo "<tbody>";

          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>" . $rowpaysheet['basicSal'] . "</td>";
            echo "<td>" . $rowpaysheet['specialBonus'] . "</td>";


                $sqlTotalBonusAllowances = "SELECT
                benefits.benefitname AS benefitname,
                employee_benefits.employeeid AS employeeid,
                sum(employee_benefits.benefitvalue) AS benefitvalue
                FROM employee_benefits
                INNER JOIN benefits
                ON employee_benefits.benefitid = benefits.benefitid
                WHERE employee_benefits.employeeid = '" . $rowpaysheet['employeeid'] . "' 
                AND benefitname IN ('House Allowance','Other Allowance');";

                $resultTotalBonusAllowance = mysqli_query($db,$sqlTotalBonusAllowances);

                $countTotalBonusAllowance = mysqli_num_rows($resultTotalBonusAllowance);

                $totalBonusAllowances = 0;

                  if ($countTotalBonusAllowance !== 0) { 

                    while ($rowTotalBonusAllowance = mysqli_fetch_array($resultTotalBonusAllowance)) {
                      $totalBonusAllowances = $rowTotalBonusAllowance['benefitvalue'] * $numofmonths;

                    }

                   } 


            echo "<td>" . $totalBonusAllowances . "</td>";
            echo "<td>" . $rowpaysheet['commission'] . "</td>";
            echo "<td>" . $rowpaysheet['absencesValue'] . "</td>";
            echo "<td>" . $rowpaysheet['overtime'] . "</td>";



          $bonusValue = round(($rowpaysheet['basicSal'] + $rowpaysheet['specialBonus'] + $totalBonusAllowances + $rowpaysheet['commission'] - $rowpaysheet['absencesValue']  + $rowpaysheet['overtime']  )/$numofmonths,0);
            
            $calculatedPAYE = round(($bonusValue - ($rowpaysheet['employee_edf_amount'] / $rowpaysheet['edf_months_value'])) * ($rowpaysheet['employee_paye_rate']/100),0);

            if ($calculatedPAYE < 0 ){ 
              $calculatedPAYE = 0;
            }

            echo "<td>" . $calculatedPAYE . "</td>";

            // PRGF
            
            $calculatedPRGF = getPRGFEmployer(intval($bonusValue),$enddate,$companyID,$db);
            $employerPRGF = $calculatedPRGF;
            //echo "<td>" . $calculatedPRGF . "</td>";


            // Employee CSG
            $employeeCSG = getCSGEmployee(intval($bonusValue),$db);

            echo "<td>" . $employeeCSG . "</td>";


            // Employer CSG
            
            $employerCSG = getCSGEmployer(intval($bonusValue),$db);

            //echo "<td>" . $employerCSG . "</td>";


            $finalBonus = $bonusValue - $calculatedPAYE - $employeeCSG;
            echo "<td>" . $finalBonus . "</td>";



           
            echo "</tr>";

           

           if ($resultCheckcount == 0) {

            $insertbonusdata=" INSERT INTO bonuspayouts (
              bonusPayrollid,
              employeeid,
              bonusPayeAmount,
              employeeCSG,
              employerCSG,
              employerPRGF,
              bonusAmount
              )
              VALUES( 
              '" . $last_id . "',
              " . $rowpaysheet['employeeid'] . ",
              " . $calculatedPAYE . ",
              " . $employeeCSG . ",
              " . $employerCSG . ",
              " . $employerPRGF . ",
              " . $finalBonus . "

              ) ";

            } else {

              while ($rowBonus = mysqli_fetch_array($resultCheck)) {

                  $insertbonusdata = "UPDATE bonuspayouts 
                  SET bonusPayeAmount = " . $calculatedPAYE . ",
                  employeeCSG = " . $employeeCSG . ",
                  employerCSG = " . $employerCSG . ",
                  employerPRGF = " . $employerPRGF . ",
                    bonusAmount = " . $finalBonus . "
                    WHERE employeeid = '" . $rowpaysheet['employeeid'] . "'
                    AND bonusPayrollid = '" . $rowBonus['periodid'] . "';";
               }

              

            }
                  if(mysqli_query($db, $insertbonusdata)) {
                      //echo 'Bonus Data for employee inserted successfully';
                    } else {
                       echo "Error: " . mysqli_error($db);
                  }



          } // FETCHING EMPLOYEE DATA END

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

            // TABLE ENDS HERE


            } // COUNT EMPLOYEES END HERE


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


  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 Bonus</h1>";
 
    
    	echo "</div>";

    	echo "<form method = \"POST\" action=\"generateBonus.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>No. of Months:</p><input type = \"number\" class=\"form-control\" name = \"numofmonths\" min=\"1\" max=\"12\"></p>";
   

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

   }



include('footer1.php');
?>