<?php

include_once '../include/DBConnect.php';
$rate6_present = 0;

// ### PayRoll Search Date Range ### //
function Search_daterange($start, $end) {
    global $dbh;
    $payroll_Search_daterange = "SELECT concat( last_name, ' ', first_name ) staffname, ch_name, staff_code,root_id,lesson_id ,payroll_gened 
        FROM lesson_attendance la,staff s WHERE la.actived = 1 and la.deleted = 0 and s.actived = 1 and s.deleted = 0 and 
        la.date <= ? and la.date >= ? and s.root_id = la.staff_id group by root_id HAVING payroll_gened = 0";
    $sth = $dbh->prepare($payroll_Search_daterange);
    $sth->bindParam(1, $end);
    $sth->bindParam(2, $start);
    $sth->execute();
    return $sth;
}

// ### PayRoll Generate page show the info of staff ### //
function Payroll_staff_rootid($root_id) {
    global $dbh;
    $payroll_staff_rootid_sql = "SELECT staff_code,concat(last_name, ' ',first_name,' ( ',ch_name,' ) ') name  FROM `staff` 
        WHERE actived = 1 and deleted = 0 and root_id = ?";
    $sth = $dbh->prepare($payroll_staff_rootid_sql);
    $sth->bindParam(1, $root_id);
    $sth->execute();
    return $sth;
}

// ### PayRoll Generate page search lesson use staff info and date range ### //
function Payroll_lesson_rootid($root_id, $start, $end) {
    global $dbh;
    $payroll_lesson_date_rootid_sql = "SELECT la.date,e.event_code,l.start_time,l.end_time,la.attendance,
        la.rate_type,la.is_charge,la.is_substitute,l.lesson_id";
    $sth = Sub_function($dbh, $payroll_lesson_date_rootid_sql, $root_id, $start, $end, "");
    $lesson = $sth->fetchAll(PDO::FETCH_ASSOC);
    return Payroll_lessonID_searchRemark($lesson, $root_id);
}

// ### Find date range ### //
function Find_dateRange($root_id, $start, $end) {
    global $dbh;
    $Find_dateRange = "SELECT Max(l.date) Maxd, Min(l.date) Mind  ";
    $step = "group by e.event_id";
    $sth = Sub_function($dbh, $Find_dateRange, $root_id, $start, $end, $step);
    while ($result = $sth->fetch(PDO::FETCH_ASSOC)) {
        $MAXD = $result['maxd'];
        $MIND = $result['mind'];
    }
    return array($MAXD, $MIND);
}

// ### Sub-function of above two function ### //
function Sub_function($dbh, $sql, $root_id, $start, $end, $step) {
    $Sub_function_sql = $sql . " FROM lesson_attendance la,event e,lesson l
             WHERE 
             la.actived = 1 and la.deleted = 0 and
             e.actived = 1 and e.deleted = 0 and
             l.actived = 1 and l.deleted = 0
             and
             la.date <= ? and la.date >= ? and la.staff_id = ?
             and
             la.lesson_id = l.lesson_id and e.event_id in (
             select event_id from event_lesson where lesson_id = l.lesson_id
             ) $step order by 1,2";

    $sth = $dbh->prepare($Sub_function_sql);
    $sth->bindParam(1, $end);
    $sth->bindParam(2, $start);
    $sth->bindParam(3, $root_id);
    $sth->execute();
    return $sth;
}

function Payroll_lessonID_searchRemark($lesson, $root_id) {
    global $dbh;
    foreach ($lesson as $k => $a) {
        $Payroll_searchRemark_sql = "SELECT reason_name,reason_id FROM `reason` WHERE actived = 1 and deleted = 0 and 
                                 reason_id in 
                                 (
                                    select reason_id from leave_app where actived = 1 and deleted = 0 and staff_id = ? and
                                    leave_id in 
                                    (
                                        select leave_id from leave_lesson where actived = 1 and deleted = 0 and lesson_id = ?
                                    )
                                 )";
        $sth = $dbh->prepare($Payroll_searchRemark_sql);
        $sth->bindParam(1, $root_id);
        $sth->bindParam(2, $a['lesson_id']);
        $sth->execute();
        $result = $sth->fetch(PDO::FETCH_ASSOC);
        $lesson[$k]['reason_name'] = $result['reason_name'];
        $lesson[$k]['reason_id'] = $result['reason_id'];
    }
    return $lesson;
}

// ### show rate text ### //
function Rate_display($rate_type, $is_substitute) {
    switch ($rate_type) {
        case 0:
            if ($is_substitute == 1)
                return "Monthly ( Substitute ) ";
            else
                return "Monthly ";
            break;
        case 1:
            if ($is_substitute == 1)
                return 'Teacher ( In House ) ( Substitute ) ';
            else
                return 'Teacher ( In House )';
            break;
        case 2:
            if ($is_substitute == 1)
                return 'Teacher ( Outside School ) ( Substitute )';
            else
                return 'Teacher ( Outside School ) ';
            break;
        case 3:
            if ($is_substitute == 1)
                return'Teacher Assistant ( In House ) ( Substitute )';
            else
                return'Teacher Assistant ( In House ) ';
            break;
        case 4:
            if ($is_substitute == 1)
                return'Teacher Assistant ( Outside School ) ( Substitute )';
            else
                return'Teacher Assistant ( Outside School ) ';
            break;
        case 5:
            if ($is_substitute == 1)
                return'Traveling ( Substitute )';
            else
                return'Traveling ';
            break;
        case 6:
            if ($is_substitute == 1)
                return'Other ( Substitute )';
            else
                return'Other';
            break;
    }
}

// ### Search The salary of staff by staff root id ### //
function Salary_rootid($root_id) {
    global $dbh;
    $salary_rootid = 'SELECT salary, rate1, rate2, rate3, rate4, rate5, rate6,other_rate_desc FROM `staff_salary_history` 
        WHERE actived = 1 and deleted = 0 and staff_id = ? and version = 
        (select max(version) from staff_salary_history where staff_id = ? group by staff_id ORDER BY version DESC LIMIT 1)';
    $sth = $dbh->prepare($salary_rootid);
    $sth->bindParam(1, $root_id);
    $sth->bindParam(2, $root_id);
    $sth->execute();
    return $sth;
}

// ### Calculate salary  use "second" to cal ### //
function Cal_Salary($salary, $rate, $hour) {
    global $rate6_present;
    if ($rate == 6)
        $rate6_present = $salary[7]==""?100:$salary[7];
    return ($rate != 6) ? (Find_Ratetype($salary, $rate) * $hour) : (Find_Ratetype($salary, $rate) * $hour * $rate6_present * 0.01);
}

function Find_Ratetype($salary, $rate) {
    return $salary[$rate];
}

// ### Insert Payroll ### //
function Insert_Payroll($staff_root_id, $create_payroll_date, $total_amount, $start_date, $end_date, $remark) {
    global $dbh;
    $code = find_Payroll_code();
    $insert_Payroll = "INSERT INTO `payroll`(`payroll_code`, `createby`, `createdate`, `lastupby`, `lastupdate`, `actived`, `deleted`, `staff_id`, `payroll_date`, `total_amount`, `is_paid`, `remarks`, `start_date`, `end_date`)
        VALUES(?,1,now(),1,now(),1,0,?,?,?,0,?,?,?)";
    $sth = $dbh->prepare($insert_Payroll);
    $sth->bindParam(1, $code);
    $sth->bindParam(2, $staff_root_id);
    $sth->bindParam(3, $create_payroll_date);
    $sth->bindParam(4, $total_amount);
    $sth->bindParam(5, $remark);
    $sth->bindParam(6, $start_date);
    $sth->bindParam(7, $end_date);
    $sth->execute();
}

// ### find Payroll code ### //
function find_Payroll_code() {
    global $dbh;
    $find_code_sql = "SELECT `payroll_code` FROM `payroll` WHERE createdate in( select Max(createdate) from payroll)";
    $sth = $dbh->query($find_code_sql);
    $result = $sth->fetch(PDO::FETCH_ASSOC);
    $Old_code = $result['payroll_code'];
    $New_code = ((int) str_replace("PR", "00", $Old_code)) + 1;
    return ($New_code < 10) ? "PR00$New_code" : ($New_code < 100 && $New_code >= 10 ? "PR0$New_code" : "PR$New_code");
}

// ### select payroll ID ### //
function Select_Payroll_ID($staff_root_id, $create_payroll_date, $total_amount, $start_date, $end_date) {
    global $dbh;
    $is_paid = 0;
    $remarks = 0;
    $Select_Payroll_ID = "SELECT payroll_id FROM `payroll` WHERE actived = 1 and deleted = 0 and 
        staff_id = ? and payroll_date = ? and total_amount = ? and is_paid = ? and remarks = ? and start_date = ? and end_date = ? ";
    $sth = $dbh->prepare($Select_Payroll_ID);
    $sth->bindParam(1, $staff_root_id);
    $sth->bindParam(2, $create_payroll_date);
    $sth->bindParam(3, $total_amount);
    $sth->bindParam(4, $is_paid);
    $sth->bindParam(5, $remarks);
    $sth->bindParam(6, $start_date);
    $sth->bindParam(7, $end_date);
    $sth->execute();
    //var_dump($staff_root_id." ".$create_payroll_date." ".$total_amount." ".$is_paid." ".$remarks);
    $Result = $sth->fetch(PDO::FETCH_ASSOC);
    return $Result['payroll_id'];
}

// ### insert payroll line ### //
function Insert_Payrollline($payroll_id, $rate_type, $rate, $hour, $amount) {
    global $dbh;
    $Insert_Payrollline = "INSERT INTO `payroll_line`(`createby`, `createdate`, `lastupby`, `lastupdate`, `actived`, `deleted`, 
        `payroll_id`, `rate_type`, `rate`, `hour`, `amount`, `remarks`) 
        VALUES (1,now(),1,now(),1,0,?,?,?,?,?,0);";
    $sth = $dbh->prepare($Insert_Payrollline);
    $sth->bindParam(1, $payroll_id);
    $sth->bindParam(2, $rate_type);
    $sth->bindParam(3, $rate);
    $sth->bindParam(4, $hour);
    $sth->bindParam(5, $amount);
    $sth->execute();
}

// ### update payroll_gen = 1 ### //
function Change_lessonattendance_payrollgenerate($staff_id, $lesson_id, $gened) {
    global $dbh;
    $change_lessonattendance_payrollgenerate = "UPDATE `lesson_attendance` SET `lastupdate`=now() ,`payroll_gened`=? 
        WHERE actived = 1 and deleted = 0 and staff_id = ? and lesson_id = ?;";
    $sth = $dbh->prepare($change_lessonattendance_payrollgenerate);
    $sth->bindParam(2, $staff_id);
    $sth->bindParam(3, $lesson_id);
    $sth->bindParam(1, $gened);
    $sth->execute();
}

// ### Seaerch for staff ### //
function Search_Staff_Name() {
    global $dbh;
    $search_staff_name = "SELECT concat(last_name,' ',first_name,' ( ',ch_name,' ) ') name,root_id FROM `staff` WHERE actived = 1 and deleted = 0";
    return $sth = $dbh->query($search_staff_name);
    //var_dump($sth->fetch());
}

// ### staffid/startdate/enddate find the payroll ### //
function Find_Payrollby_date_staffid($staff_name, $start_date, $end_date, $position_id) {
    global $dbh;
    $staff_id_sql = (empty($staff_name)) ? "" : " and p.staff_id = $staff_name  and pos.position_id in ( select position_id from `staff_salary_history` where actived = 1 and deleted = 0 and staff_id = $staff_name )";
    $daterange_sql = (!empty($start_date) && !empty($end_date)) ? "and ('$start_date' <= start_date and '$end_date' >= start_date or '$end_date' >= end_date and '$start_date' <= end_date or '$start_date' <= end_date and '$end_date' >= start_date)" : "";
    $position_sql = (empty($position_id)) ? "" : " and pos.position_id = $position_id";
    $Find_Payrollby_date_staffid = "SELECT `payroll_id`,`payroll_code`,`payroll_date`,`total_amount`,concat(last_name,' ',first_name,' ( ',ch_name,' ) ') name,start_date,end_date,s.root_id ,position_name
        FROM `payroll` p,`staff` s , `position` pos
        Where p.actived = 1 and p.deleted = 0 
        and s.actived = 1 and s.deleted = 0         
        and pos.actived = 1 and pos.deleted = 0
        and pos.position_id = ( select position_id from staff_salary_history where actived = 1 and deleted = 0 and staff_id = s.root_id)
        and p.staff_id = s.root_id $staff_id_sql $daterange_sql $position_sql order by 1";
    return $sth = $dbh->query($Find_Payrollby_date_staffid);
}

// ### Search Payroll info ### //
function Search_Payroll_info($payroll_id) {
    global $dbh;
    $Search_Payroll_info_sql = "SELECT pl.`payroll_id`,`rate_type`,`rate`,`hour`, `amount`,`total_amount`,pl.`remarks` FROM `payroll_line` pl, `payroll` p 
        where pl.actived = 1 and pl.deleted = 0 and p.actived = 1 and p.deleted = 0 and pl.payroll_id = p.payroll_id and pl.payroll_id = ?";
    $sth = $dbh->prepare($Search_Payroll_info_sql);
    $sth->bindParam(1, $payroll_id);
    $sth->execute();
    return $sth;
}

// ### Update payroll deleted=0 ### //
function Update_Paryroll_Payrollline_deleted($payroll_id) {
    global $dbh;
    $update_payroll_deleted = "UPDATE `payroll` SET `lastupby`=0,`lastupdate`=now(),`deleted`=1 WHERE payroll_id = $payroll_id and actived = 1 and deleted = 0";
    $dbh->query($update_payroll_deleted);
    $update_payrollline_deleted = "UPDATE `payroll_line` SET `lastupby`=0, `lastupdate`=now(),`deleted`=1 WHERE payroll_id = $payroll_id and actived = 1 and deleted = 0";
    $dbh->query($update_payrollline_deleted);
}

// ### Search Payroll position ### //
function Search_payroll_position($staff_id) {
    global $dbh;
    $search_position = "SELECT position_name FROM `position` 
        WHERE actived = 1 and deleted = 0 and 
        position_id in (
            select position_id from staff_salary_history where actived = 1 and deleted = 0 and staff_id = $staff_id
        )";
    return $sth = $dbh->query($search_position);
}

// ### Position List ### //
function Position_List() {
    global $dbh;
    $position_list = "SELECT position_id,position_name FROM `position` WHERE actived=1 and deleted = 0";
    return $dbh->query($position_list);
}

?>