<?php
function addBio($fname, $mname, $lname, $email, $tell, $dob, $idNo, $nationality, $county, $disabilty, $gender, $division, $location, $sublocation, $village,$licence, $profession,$nhif, $nssf, $kra){
    if(!isEmployee($idNo)){
    $staffID = getStaffNo();
    $estatus = 'Active';
    $fullName = $fname." ".$mname." ".$lname;
    $query = "INSERT INTO `employee`(`fullname`, `staffID`, `email`, `tell`, `idNo`, `nationality`, `county`, `disability`, `gender`, `dob`, `division`, `location`, `subLocation`, `village`, `drivingLicence`,  `profession`,  `kraPin`, `nssf`, `nhif`, `Estatus`) 
    VALUES ('$fullName', '$staffID', '$email', '$tell','$idNo', '$nationality', '$county', '$disabilty', '$gender', '$dob', '$division', '$location', '$sublocation', '$village','$licence', '$profession','$kra', '$nssf','$nhif','$estatus')";
    exe($query);
    }else{
        array_push($GLOBALS['errors'], " Employee already exists");
    }

}

function isEmployee($idNo){
    $query = "SELECT COUNT(*) as count FROM employee WHERE `idNo`= '$idNo'";
    $result = exe($query);
    $result = mysqli_fetch_array($result);
    if ($result['count'] > 0){
        return true;
    }else{
        return false;
    }
}

function getEmployees(){
    $query = "SELECT * FROM `employee`";
    return  exe($query);
}

function getActiveEmployee(){
    $query = "SELECT * FROM `employee` WHERE `Estatus` = 'active'";
    return  exe($query); 
}

function getEmployeesTime(){
    $result = getEmployees();
    $timed = array();
    foreach($result as $row){
        $type = getPayType($row['jobgroup']);
        if($type == "timeSheet"){
            array_push($timed, $row);
        }
    }
    return $timed;
}

function getEmployeesCom(){
   
    $result = getEmployees();
    $timed = array();
    foreach($result as $row){
        $type = getPayType($row['jobgroup']);
        if($type == "commision"){
            array_push($timed, $row);
        }
    }
    return $timed;
}

function getEmployeesByGroup($jobgroup){
    $query = "SELECT * FROM `employee` WHERE `jobgroup` = '$jobgroup'";
    return  exe($query);
}

function updateEBio(){

}
function getEmployeeByStaffNo($staffID){
    $query = "SELECT * FROM `employee` WHERE `staffID` = '$staffID'";
    $result = exe($query);
    $result = mysqli_fetch_array($result);
    return $result;
}

function updadeConfig($department, $jobGroup,$region,$position, $idNo){
    $query = "UPDATE `employee` SET `department`='$department',`jobgroup`='$jobGroup',`position`='$position', `region`='$region' WHERE `idNo`= '$idNo'";
    exe($query);
    //echo "done";
}


function addKin(){


}

function getStaffNo(){
    $y = date("Y");
    $pre= "K";
    $no = rand(1000,100000);
    return $pre.$no."/".$y;
}
// this function will add financial infotions for the new staff
function updadeFinance($bank, $branch,$account,$swift, $idNo){
    $query = "UPDATE `employee` SET `bank`='$bank',`branch`='$branch',`accNo`='$account', `swiftCode`='$swift' WHERE `idNo`= '$idNo'";
    exe($query);

    
}

function addAccademic(){



}
function econfig(){




}

function  addDepartments($name,$disp, $head){
    $query = "INSERT INTO `departments`(`name`, `description`, `head`)
     VALUES ('$name','$disp','$head')";
     exe($query);
}

function getDepartments(){
    $query = "SELECT * FROM `departments`";
    return  exe($query);
}

function addRegion($name,$address,$head){
    $query = "INSERT INTO `regions`(`name`, `address`, `head`) VALUES ('$name','$address','$head')";
    exe($query);
}

function getRegions(){
    $query = "SELECT * FROM `regions`";
    return  exe($query);
}

function addPosition($name,$disp, $duties,$department){
    $query = "INSERT INTO `positions`(`name`, `description`, `duties`, `department`) 
    VALUES ('$name','$disp', '$duties','$department')";

    exe($query);
}

function getPositions(){
    $query = "SELECT * FROM `positions`";
    return  exe($query);
}

function addJobGroup($name,$department,$disp, $payType,$basic, $rate,$commission){
    $query = "INSERT INTO `jobgroup`(`name`, `department`, `description`, `payType`, `basic`, `rate`, `commision`) 
    VALUES ('$name','$department','$disp', '$payType','$basic', '$rate','$commission')";
    exe($query);
}

function getJobGroup(){
    $query = "SELECT * FROM `jobgroup`";
    return exe($query);
}
function getJobGroupByID($jobGroupID){
    $query = "SELECT * FROM `jobgroup` WHERE `id` = '$jobGroupID'";
    $result = exe($query);
    $result = mysqli_fetch_array($result);
    return $result;

}

function addittions($name,$disp, $type, $employee, $jobGroup, $edate,$expDate,$amount){
    $query = "INSERT INTO `additions`(`name`, `description`, `type`, `employee`, `jobgroup`, `startDate`, `endDate`,`amount`) 
    VALUES ('$name','$disp','$type','$employee','$jobGroup','$edate','$expDate','$amount')";
    exe($query);
}

function getAllAdditions(){
    $query = "SELECT * FROM `additions`";
   return exe($query);
}
function getAdditionsByJobGID($jobgroupID){
    $query = "SELECT * FROM `additions` WHERE `jobgroup`='$jobgroupID'";
   return exe($query);
}


function deductions($name,$disp, $type, $employee, $jobGroup, $edate,$expDate,$amount){
    $query = "INSERT INTO `deductions`(`name`, `description`, `type`, `employee`, `jobgroup`, `startDate`, `endDate`,`amount`) 
    VALUES ('$name','$disp','$type','$employee','$jobGroup','$edate','$expDate','$amount')";
    exe($query);
}

function getAdditionsByStaffID($staffID){
    $query = "SELECT * FROM `additions` WHERE `employee`='$staffID'";
   return exe($query);
}

function getDeductions(){
    $query = "SELECT * FROM `deductions`";
   return exe($query);
}

function getDeductionsByJobGID($jobgroupID){
    $query = "SELECT * FROM `deductions` WHERE `jobgroup`='$jobgroupID'";
    return exe($query);
}

function getDeductionsByStaffID($staffID){
    $query = "SELECT * FROM `deductions` WHERE `employee`='$staffID'";
    return exe($query);
}

function getPayType($jobgroup){
    $query = "SELECT * FROM `jobgroup` WHERE `id`='$jobgroup'";
    $result = exe($query);
    $result = mysqli_fetch_array($result);
    return $result['payType'];
}

function getBasic($jobGroup){
    $query = "SELECT * FROM `jobgroup` WHERE `id`='$jobGroup'";
    $result = exe($query);
    $result = mysqli_fetch_array($result);
    return $result['basic'];
}
function getRate($jobGroup){
    $query = "SELECT * FROM `jobgroup` WHERE `id`='$jobGroup'";
    $result = exe($query);
    $result = mysqli_fetch_array($result);
    return $result['rate'];
}

function getCommissions($jobGroup){
    $query = "SELECT * FROM `jobgroup` WHERE `id`='$jobGroup'";
    $result = exe($query);
    $result = mysqli_fetch_array($result);
    return $result['commision'];
}

function getTaxable($staffID){
    $taxable = 0;
    $staff = getEmployeeByStaffNo($staffID);
    $jobGroup = $staff['jobgroup'];
    $type = getPayType($jobGroup);
    $gAdditions = getAdditionsByJobGID($jobGroup);
    $pAdditions = getAdditionsByStaffID($staffID);
    if($type =="basic"){
        $basic = getBasic($jobGroup);
        $taxable = $basic;

        foreach($gAdditions as $add){
            $taxable = $taxable + $add['amount'];
        }
        foreach($pAdditions as $add){
            $taxable = $taxable + $add['amount'];
        }
    }else{
        $basic = 50000;
        $taxable = $basic;

        foreach($gAdditions as $add){
            $taxable = $taxable + $add['amount'];
        }
        foreach($pAdditions as $add){
            $taxable = $taxable + $add['amount'];
        }
    }

    return $taxable;
}

function getPayee($amount){
    $tax=0;
    if ($amount >= 24000){
        $amount = $amount -24000;
        
        $tax = 0.10*24000; 
        
        if($amount>=8333){
            $amount = $amount - 8333;
            $tax = $tax + (0.25*8333);
            if($amount>0){
                $tax = $tax + (0.30*$amount);
                $tax = $tax - 2400;
               
            }
        }else{
          $tax = $tax + (0.25*$amount);
          $tax = $tax - 2400;
        }
      }
      return $tax;
}

function getNhif($basic){
    $nhif = 0;
    if($basic>0 && $basic<6000){
        $nhif = 150;
    }
    if($basic>=6000 && $basic<8000){
        $nhif = 300;
    }
    if($basic>=8000 && $basic<12000){
        $nhif = 400;
    }
    if($basic>=12000 && $basic<15000){
        $nhif = 500;
    }
    if($basic>=15000 && $basic<20000){
        $nhif = 600;
    }
    if($basic>=20000 && $basic<25000){
        $nhif = 750;
    }
    if($basic>=25000 && $basic<30000){
        $nhif = 850;
    }
    if($basic>=30000 && $basic<35000){
        $nhif = 900;
    }
    if($basic>=35000 && $basic<40000){
        $nhif = 950;
    }
    if($basic>=40000 && $basic<45000){
        $nhif = 1000;
    }
    if($basic>=45000 && $basic<50000){
        $nhif = 1100;
    }
    if($basic>=50000 && $basic<60000){
        $nhif = 1200;
    }
    if($basic>=60000 && $basic<70000){
        $nhif = 1300;
    }
    if($basic>=70000 && $basic<80000){
        $nhif = 1400;
    }
    if($basic>=80000 && $basic<90000){
        $nhif = 1500;
    }
    if($basic>=90000 && $basic<100000){
        $nhif = 1600;
    }
    if($basic>=100000){
        $nhif = 1700;
    }

    return $nhif;
}

function getNssf($amount){
    return 200;
}
function totalDeductions($staffID){
    $deductions = 0;
    $staff = getEmployeeByStaffNo($staffID);
    $jobGroup = @$staff["jobgroup"];
    //$type = getPayType($staffID);
    $gdeductions = getDeductionsByJobGID($jobGroup);
    $pdeductions = getDeductionsByStaffID($staffID);
    foreach($gdeductions as $row) {
        $deductions = $deductions + $row['amount'];
    }
    foreach($pdeductions as $row){
        $deductions = $deductions + $row['amount'];
    }
    return $deductions;
}

function saveDeduction($staffID,$month,$year,$user){
    $deductions = 0;
    $staff = getEmployeeByStaffNo($staffID);
    $jobGroup = @$staff["jobgroup"];
    //$type = getPayType($staffID);
    $gdeductions = getDeductionsByJobGID($jobGroup);
    $pdeductions = getDeductionsByStaffID($staffID);
    foreach($gdeductions as $row) {
        $deductions = $deductions + $row['amount'];
        saveTransactions($staffID,"deductions",$row['amount'],$month,$year,$user,$row['name']);
    }
    foreach($pdeductions as $row){
        $deductions = $deductions + $row['amount'];
        saveTransactions($staffID,"deductions",$row['amount'],$month,$year,$user,$row['name']);
    }
    return $deductions;
}

function getNet($staffID){
    $gross = getTaxable($staffID);
    $payee = getPayee($gross);
    $nhif = getNhif($gross);
    $nssf = getNssf($gross);
    $otherDeduc = totalDeductions($gross);
    $net = $gross - $payee - $nhif - $nssf - $otherDeduc;
    return $net;

}

function saveTransactions($staffID,$type,$amount,$month,$year,$user, $disp){
    $query = "INSERT INTO `transactions`(`staffID`, `type`, `month`, `year`, `amount`, `user`,`disp`) 
    VALUES ('$staffID','$type','$month','$year','$amount','$user','$disp')";
    exe($query);
}
function saveTaxables($staffID,$month,$year,$user){
    $taxable = 0;
    $staff = getEmployeeByStaffNo($staffID);
    $jobGroup = $staff['jobgroup'];
    $type = getPayType($jobGroup);
    $gAdditions = getAdditionsByJobGID($jobGroup);
    $pAdditions = getAdditionsByStaffID($staffID);
    if($type =="basic"){
        $basic = getBasic($jobGroup);
        saveTransactions($staffID,"basic",$basic,$month,$year,$user,"Basic salary");
        $taxable = $basic;

        foreach($gAdditions as $add){
            $taxable = $taxable + $add['amount'];
            saveTransactions($staffID,"addition",$add['amount'],$month,$year,$user,$add['name']);
        }
        foreach($pAdditions as $add){
            $taxable = $taxable + $add['amount'];
            saveTransactions($staffID,"addition",$add['amount'],$month,$year,$user, $add['name']);
        }
    }else{
        $basic = 50000;
        $taxable = $basic;

        foreach($gAdditions as $add){
            $taxable = $taxable + $add['amount'];
            saveTransactions($staffID,"addition",$add['amount'],$month,$year,$user,$add['name']);
        }
        foreach($pAdditions as $add){
            $taxable = $taxable + $add['amount'];
            saveTransactions($staffID,"addition",$add['amount'],$month,$year,$user,$add['name']);
        }
    }

    return $taxable;
}
function transact($month,$year,$user,$targeted, $selected){
    $totalCost =0;
    $payee = 0;
    $nhif = 0;
    $nssf = 0;
    $totalDeductions =0;
    $totalNet= 0;
    
    if($targeted=="all"){
        $target = getActiveEmployee();
        foreach ($target as $row){
            $staffID = $row['staffID'];
            //$jobgroups = $row['jobgroup'];
            $amount = @saveTaxables($staffID,$month,$year,$user);
            $totalCost=$totalCost+$amount;
            $payee = $payee + getPayee($amount);
            saveTransactions($staffID,"paye",getPayee($amount),$month,$year,$user, "PAYE");
            $nhif = $nhif + getNhif($amount);
            saveTransactions($staffID,"nhif",getNhif($amount),$month,$year,$user, "NHIF");
            $nssf = $nssf + getNssf($amount);
            saveTransactions($staffID,"nssf",getNssf($amount),$month,$year,$user, "NSSF");
            $totalDeductions = $totalDeductions + @saveDeduction($staffID,$month,$year,$user);
            $totalNet = $totalNet + getNet($staffID);
            saveTransactions($staffID,"net",getNet($staffID),$month,$year,$user, "NET");
        }
    }else{
        $intrest = array();
        //echo json_encode($selected);
        foreach($selected as $group){
            $staffs = getEmployeesByGroup($group);
            foreach($staffs as $row){
                array_push($intrest,$row);
            }
        }

        $target = $intrest;
        foreach ($target as $row){
            $staffID = $row['staffID'];
            //$jobgroups = $row['jobgroup'];
            $amount = @saveTaxables($staffID,$month,$year,$user);
            $totalCost=$totalCost+$amount;
            $payee = $payee + getPayee($amount);
            saveTransactions($staffID,"paye",getPayee($amount),$month,$year,$user, "PAYE");
            $nhif = $nhif + getNhif($amount);
            saveTransactions($staffID,"nhif",getNhif($amount),$month,$year,$user, "NHIF");
            $nssf = $nssf + getNssf($amount);
            saveTransactions($staffID,"nssf",getNssf($amount),$month,$year,$user, "NSSF");
            $totalDeductions = $totalDeductions + @saveDeduction($staffID,$month,$year,$user);
            $totalNet = $totalNet + getNet($staffID);
            saveTransactions($staffID,"net",getNet($staffID),$month,$year,$user, "NET");
        }
    }
}

function payeList($month,$year){
    $query = "SELECT * FROM `transactions` WHERE `month` = '$month' AND `year` = '$year' AND `type` = 'paye'";
    return exe($query);

}

function nhifList($month,$year){
    $query = "SELECT * FROM `transactions` WHERE `month` = '$month' AND `year` = '$year' AND `type` = 'nhif'";
    return exe($query);

}

function nssfList($month,$year){
    $query = "SELECT * FROM `transactions` WHERE `month` = '$month' AND `year` = '$year' AND `type` = 'nssf'";
    return exe($query);

}
function netList($month,$year){
    $query = "SELECT * FROM `transactions` WHERE `month` = '$month' AND `year` = '$year' AND `type` = 'net'";
    return exe($query);

}

?>