webadmin.php

/var/www/(Del)pathways.org.hk/MIS20140127/old20140414/LeaveApplication/CheckFollowUpAction.php

<?php

class LessonState {

    const NORMAL_LESSON = 1;
    const MAKE_UP_LESSON = 2;
    const NEED_MAKE_UP = 3;
    const CANCEL_LESSON = 4;
    const HAD_MAKE_UP = 5;

}

class LeaveAssistant {

    private $db;

    const LEAVE_STATUS_INCOMPLETE = 1;
    const LEAVE_STATUS_COMPLETE = 0;

    public function __construct($database) {
        $this->db = $database;
    }
    
    ##################
    ### validation ###
    ##################
    
    public function isApplicationDateOverlap($staff_id,$student_id,$startDate,$endDate,$bypassLeaveId){
        $content = "";
        if($staff_id >0 && $staff_id!=NULL){
            $query = "seLECT `createdate`,`start_date`,`end_date` FROM `leave_app` WHERE `staff_id`='$staff_id' and `deleted`=0 and `actived` =1 
                and (`start_date`>='$startDate' or `end_date`>='$startDate') AND
                    (`start_date`<='$endDate' or `end_date`<='$endDate')";
            if($bypassLeaveId>0 && $bypassLeaveId!=NULL){
                $query .= " and `leave_id`!='$bypassLeaveId' ";
            }
            $result = $this->db->prepare($query);
            $result->execute();
            $OverlapRecord = $result->fetchAll(PDO::FETCH_ASSOC);
            //var_dump($OverlapRecord);
            foreach ($OverlapRecord as $OR) {
                $content .= "</br><b>Submit Date: </b>".$OR['createdate']."</br>";
                $content .= date_format(date_create($OR['start_date']), 'Y-m-d(H:i)')." <b>to</b> ".date_format(date_create($OR['end_date']), 'Y-m-d(H:i)')."</br>";
            }
        }
        else if($student_id >0 && $student_id!=NULL){
            $query = "seLECT `createdate`,`start_date`,`end_date` FROM `leave_app` WHERE `stu_linking_id`='$student_id' and `deleted`=0 and `actived` =1 
                and (`start_date`>='$startDate' or `end_date`>='$startDate') AND
                    (`start_date`<='$endDate' or `end_date`<='$endDate')";
            if($bypassLeaveId>0 && $bypassLeaveId!=NULL){
                $query .= " and `leave_id`!='$bypassLeaveId' ";
            }
            $result = $this->db->prepare($query);
            $result->execute();
            $OverlapRecord = $result->fetchAll(PDO::FETCH_ASSOC);
            //var_dump($OverlapRecord);
            foreach ($OverlapRecord as $OR) {
                $content .= "</br><b>Submit Date: </b>".$OR['createdate']."</br>";
                $content .= date_format(date_create($OR['start_date']), 'Y-m-d(H:i)')." <b>to</b> ".date_format(date_create($OR['end_date']), 'Y-m-d(H:i)')."</br>";
            }
        }
        return $content;
    }
    
//    public function retrieveData(){
//        //NO FUNCTION
//    }


    ##################
    #### Checking ####
    ##################

    private function getSubstitutedRecords($event_id) {
        $query = "SELECT la.`attend_id`,la.`lesson_id`,la.`staff_id` FROM `lesson_attendance`la,`lesson`l WHERE la.`lesson_id` 
            in (SELECT `lesson_id` FROM `event_lesson` WHERE `event_id` = '$event_id' and deleted= 0 and actived = 1) and 
                la.`actived` = 1 and la.`deleted`=0 and l.`actived` = 1 and l.`deleted` = 0 and l.`lesson_leave_status` = 1 and 
                `staff_id`>0 and `is_substitute` = 1  and la.`lesson_id` = l.`lesson_id`";
        $result = $this->db->prepare($query);
        $result->execute();
        $Substitude_Teacher_Records = $result->fetchAll(PDO::FETCH_ASSOC);
        return $Substitude_Teacher_Records;
    }

    private function checkingSubstituted($event_id) {
        $handle = 'false';
        #############################################
        ######## Substitute Teacher Checking ########
        foreach ($this->getSubstitutedRecords($event_id) as $ResultSet) {
            $get_staff_attend_id = $ResultSet['attend_id'];
            $get_substitute_lesson_id = $ResultSet['lesson_id'];
            echo 'Staff attend_id: ' . $get_staff_attend_id . ', subtitute_lesson_id: ' . $get_substitute_lesson_id;
            #### Update related student attendance record ####
            $query = "UPDATE `lesson_attendance` SET `state`=1 WHERE `stu_linking_id`>0 and `lesson_id` = '$get_substitute_lesson_id' 
                and `actived`=1 and `deleted` = 0";
            $result = $this->db->prepare($query);
            $result->execute();
            #### Update Lesson , lesson_leave_status = 0 ####
            $this->updateLessonLeaveStatus($get_substitute_lesson_id, LeaveAssistant::LEAVE_STATUS_COMPLETE);
            $handle = 'true';
        }
        return $handle;
    }

    private function getMakeUpLessonRecords($event_id) {
        #### find Make Up Class Stu attendance Record ####
        $query = "SELECT la.`attend_id`,la.`stu_linking_id`,la.`make_up_lesson_id`,l.`date`,l.`start_time`,l.`end_time` 
            FROM `lesson_attendance` la,`lesson` l WHERE l.`actived`=1 and l.deleted =0 and la.`actived`=1 and la.`deleted`=0 and
            la.`make_up_lesson_id` > 0 and la.`make_up_lesson_remark` = '' and la.`stu_linking_id`>0 and la.`state`=2 and la.`lesson_id` in 
            (SELECT `lesson_id` FROM `event_lesson` WHERE `event_id` = '$event_id' and deleted= 0 and actived = 1) 
                and l.lesson_id = la.lesson_id ";
        $result = $this->db->prepare($query);
        $result->execute();
        $MakeUp_Lesson_Records = $result->fetchAll(PDO::FETCH_ASSOC);
        return $MakeUp_Lesson_Records;
    }

    private function checkingMakeUpLesson($event_id) {
        ########################################
        ######## Make Up Class Checking ########
        $handle = 'false';
        foreach ($this->getMakeUpLessonRecords($event_id) as $ResultSet) {
            $get_makeuplesson_attend_id = $ResultSet['attend_id'];
            //$get_makeuplesson_staff_id = $ResultSet['staff_id'];
            $get_makeuplesson_stu_id = $ResultSet['stu_linking_id'];
            $get_makeuptarger_lesson_id = $ResultSet['make_up_lesson_id'];
            $Content = 'To: ' . $ResultSet['date'] . ' ' . $ResultSet['start_time'] . '-' . $ResultSet['end_time'];
            #### Update Target Information ####
            $query = "UPDATE `lesson_attendance` SET `state`=5,`make_up_lesson_remark`='$Content' WHERE
                `lesson_id` = '$get_makeuptarger_lesson_id' and `stu_linking_id` = '$get_makeuplesson_stu_id' and `state` = '3'
                    and `actived` = 1 and `deleted` = 0";
            $result = $this->db->prepare($query);
            $result->execute();
            #### Update current Information ####
            $query = "SELECT l.`date`,l.`start_time`,l.`end_time` FROM `lesson_attendance`la,`lesson`l WHERE    
                la.`lesson_id` = '$get_makeuptarger_lesson_id' and la.`actived`=1 and la.`deleted` =0 and la.`state`=5 
                    and la.`make_up_lesson_remark` != '' and l.`actived` = 1 and l.`deleted` =0 and 
                    la.`stu_linking_id` = '$get_makeuplesson_stu_id' and l.`lesson_id` = la.`lesson_id`";
            $result = $this->db->prepare($query);
            $result->execute();
            if ($ResultSet = $result->fetch(PDO::FETCH_ASSOC)) {
                $Content = 'For: ' . $ResultSet['date'] . ' ' . $ResultSet['start_time'] . '-' . $ResultSet['end_time'];
                $query = "UPDATE `lesson_attendance` SET `make_up_lesson_remark`='$Content' WHERE `attend_id`='$get_makeuplesson_attend_id'";
                $result = $this->db->prepare($query);
                $result->execute();
            }
            $handle = 'true';
        }
        return $handle;
    }

    public function checkAll($check_event_id, $leave_id) {
        $checkedSubstitute = $this->checkingSubstituted($check_event_id);
        $checkedMakeUpLesson = $this->checkingMakeUpLesson($check_event_id);
        echo 'checked Substitute teacher: ' . $checkedSubstitute . '</br>';
        echo 'checked Make Up Lesson: ' . $checkedMakeUpLesson . '</br>';

        ######## Final Checking ########
        $query = "SELECT `lesson_id` FROM `lesson` WHERE `lesson_leave_status` = 1 and `actived`=1 and `deleted`=0";
        $result = $this->db->prepare($query);
        $result->execute();
        $Checking_Lesson_Records = $result->fetchAll(PDO::FETCH_ASSOC);
        //while ($ResultSet = $result->fetch(PDO::FETCH_ASSOC)) {
        foreach ($Checking_Lesson_Records as $ResultSet) {
            $get_checking_lesson_id = $ResultSet['lesson_id'];
            $query = "SELECT `attend_id` FROM `lesson_attendance` WHERE `lesson_id` ='$get_checking_lesson_id' and `actived`=1 and 
                `deleted`=0 and `state` = 3 and `make_up_lesson_remark` = '' and `stu_linking_id`>0";
            $result = $this->db->prepare($query);
            $result->execute();
            #### No Need Make Up record find ####
            if (!($ResultSet = $result->fetch(PDO::FETCH_ASSOC))) {
                $this->updateLessonLeaveStatus($get_checking_lesson_id, LeaveAssistant::LEAVE_STATUS_COMPLETE);
            }
        }
    }

    #####################
    #### UNDO ACTION ####
    #####################

    private function findSubstituteLesson($lesson_id) {
        $query = "SELECT `attend_id` FROM `lesson_attendance` WHERE `actived`=1 and `deleted`=0 and `lesson_id`='$lesson_id' and `is_substitute`='1'";
        $result = $this->db->prepare($query);
        $result->execute();
        $SubstituteAttendanceID = $result->fetchAll(PDO::FETCH_ASSOC);
        return $SubstituteAttendanceID;
    }

    private function undoSubstituteLesson($lesson_id) {
        $handle = FALSE;
        foreach ($this->findSubstituteLesson($lesson_id) as $substitute_attend_record) {
            $undo_attendance_id = $substitute_attend_record['attend_id'];
            $query = "UPDATE `lesson_attendance` SET `deleted`=1 WHERE `attend_id`='$undo_attendance_id'";
            $sth = $this->db->prepare($query);
            $sth->execute();
            $handle = TRUE;
        }
        return $handle;
    }

    #### support function of MakeUp Undo ####

    private function findMakeUpLessonPreviousVersionOf($makeup_lesson_attend_id,$makeup_lesson_id, $stu_id) {
        $query = "SELECT MAX( `attend_id` ) as attend_id,state FROM `lesson_attendance` WHERE `actived`=1 AND `lesson_id`='$makeup_lesson_id' AND
            `stu_linking_id` =  '$stu_id' AND  (`state` !=  '2' AND `state` != '3') and attend_id!='$makeup_lesson_attend_id'";
        $result = $this->db->prepare($query);
        $result->execute();
        $PreviousVersionAttendanceID = $result->fetchAll(PDO::FETCH_ASSOC);
        return $PreviousVersionAttendanceID;
    }

    private function findMakeUpLessonOf($lesson_id, $specific_student_id) {
        //var_dump($lesson_id);
        $query = "select `lesson_id`,`attend_id`,`stu_linking_id` from `lesson_attendance` WHERE `make_up_lesson_id` = '$lesson_id'
            AND `deleted` = 0 AND `actived`=1";// and `state`= '2'
        if ($specific_student_id > 0 && $specific_student_id != NULL) {
            $query .= " AND `stu_linking_id`='$specific_student_id'";
        }
        $result = $this->db->prepare($query);
        $result->execute();
        $MakeUpLessonRecord = $result->fetchAll(PDO::FETCH_ASSOC);
        //var_dump($MakeUpLessonRecord);
        return $MakeUpLessonRecord;
    }

    private function activeLessonByAttendanceID($attend_id) {
        $query = "UPDATE `lesson_attendance` SET `deleted`=0 WHERE `attend_id`='$attend_id' AND `deleted`=1";
        $sth = $this->db->prepare($query);
        $sth->execute();
    }

    private function deleteLessonByAttendanceID($attend_id) {
        $query = "UPDATE `lesson_attendance` SET `deleted`=1 WHERE `attend_id`='$attend_id' AND `deleted`=0";
        $sth = $this->db->prepare($query);
        $sth->execute();
    }

    private function deleteWholeLessonByLessonID($lesson_id) {
        #### delete all lesson_attendance records ####
        $query = "UPDATE `lesson_attendance` SET `deleted`=1 WHERE `lesson_id`='$lesson_id' AND `deleted`=0";
        $sth = $this->db->prepare($query);
        $sth->execute();
        #### delete event_lesson linkage ####
        $query = "UPDATE `event_lesson` SET `deleted`=1 WHERE `lesson_id`='$lesson_id' AND `deleted`=0";
        $sth = $this->db->prepare($query);
        $sth->execute();
    }

    private function CheckEmptyLesson($lesson_id) {
        $query = "SELECT `attend_id` FROM `lesson_attendance` WHERE `actived`=1 AND `deleted`=0 AND `stu_linking_id`>0 AND 
            `lesson_id`='$lesson_id'";
        $result = $this->db->prepare($query);
        $result->execute();
        $StudentsInLesson = $result->fetchAll(PDO::FETCH_ASSOC);
        //var_dump($StudentsInLesson);
        if (count(($StudentsInLesson)) == 0) {
            //echo 'No Student in this lesson: ' . $lesson_id;
            ## No Students in this lesson ##
            $this->deleteWholeLessonByLessonID($lesson_id);
        }
    }

    private function undoMakeUpLesson($lesson_id, $specific_student_id) {
        ######################################
        #### Undo Make Up Lesson Arranged ####
        $handle = FALSE;
        $makeUpLessonRecords = $this->findMakeUpLessonOf($lesson_id, $specific_student_id);
        if (count(($makeUpLessonRecords)) == 0) {
            //echo 'this lesson no make up record: ' . $lesson_id;
        } else {
            foreach ($makeUpLessonRecords as $MULR) {
                $get_makeup_lesson_id = $MULR['lesson_id'];
                $get_makeup_stu_id = $MULR['stu_linking_id'];
                $get_makeup_attendance_id = $MULR['attend_id'];
                $previousVersion = $this->findMakeUpLessonPreviousVersionOf($get_makeup_attendance_id,$get_makeup_lesson_id, $get_makeup_stu_id);

                foreach ($previousVersion as $PV) {
                    $previousLessonAttendanceID = $PV['attend_id'];
                    //echo '</br>PreviousLessonAttendanceID: '.$previousLessonAttendanceID.'</br>State: '.$PV['state'];
                    $this->activeLessonByAttendanceID($previousLessonAttendanceID);
                }
                
                //echo '</br>Lesson ID: '.$get_makeup_lesson_id.' A_ID: '.$get_makeup_attendance_id;
                $this->deleteLessonByAttendanceID($get_makeup_attendance_id);
                $this->CheckEmptyLesson($get_makeup_lesson_id);
            }
            $handle = TRUE;
        }
        return $handle;
    }

    private function findCanceledLesson($lesson_id, $specific_student_id) {
        $query = "SELECT `attend_id` FROM `lesson_attendance` WHERE `actived`=1 AND `deleted`=0 AND `stu_linking_id`>0 
            AND `lesson_id`='$lesson_id' AND `state` = 4";
        if ($specific_student_id > 0 && $specific_student_id != NULL) {
            $query .=" and `stu_linking_id`='$specific_student_id'";
        }
        $result = $this->db->prepare($query);
        $result->execute();
        $CancelAttendanceIDs = $result->fetchAll(PDO::FETCH_ASSOC);
        return $CancelAttendanceIDs;
    }

    public function undoCancelLesson($lesson_id, $specific_student_id) {
        ##############################
        #### Undo Canceled Lesson ####
        $handle = FALSE;
        foreach ($this->findCanceledLesson($lesson_id, $specific_student_id) as $cancelAttendanceID) {
            $get_cancel_attendance_id = $cancelAttendanceID['attend_id'];
            $query = "UPDATE `lesson_attendance` SET `state`= 3 WHERE `attend_id`= '$get_cancel_attendance_id'";
            $sth = $this->db->prepare($query);
            $sth->execute();
            $handle = TRUE;
        }
        return $handle;
    }

    public function updateLessonLeaveStatus($lesson_id, $LEAVE_STATUS_TYPE) {
        if ($lesson_id > 0 && ($LEAVE_STATUS_TYPE == LeaveAssistant::LEAVE_STATUS_COMPLETE || $LEAVE_STATUS_TYPE == LeaveAssistant::LEAVE_STATUS_INCOMPLETE)) {
            $query = "UPDATE `lesson` SET `lesson_leave_status`='$LEAVE_STATUS_TYPE' WHERE `actived`='1' AND `deleted`=0 AND `lesson_id`='$lesson_id'";
            $sth = $this->db->prepare($query);
            $sth->execute();
        }
    }

    public function undoOneLesson($lesson_id) {
        $UndoSubstitute = $this->undoSubstituteLesson($lesson_id);
        $UndoCancel = $this->undoCancelLesson($lesson_id, NULL);
        $UndoMakeUp = $this->undoMakeUpLesson($lesson_id, NULL);

//        echo '</br>UndoSubstitude: ' . ($UndoSubstitute?"true":"false") . '</br>';
//        echo '</br>UndoCancel: ' . ($UndoCancel?"true":"false" ) . '</br>';
//        echo '</br>UndoMakeUp: ' . ($UndoMakeUp?"true":"false" ) . '</br>';
        if ($UndoCancel || $UndoMakeUp || $UndoSubstitute) {
//            echo 'Update LeaveStatus to 1';
            $this->updateLessonLeaveStatus($lesson_id, LeaveAssistant::LEAVE_STATUS_INCOMPLETE);
            //$query = "UPDATE `lesson_attendance` SET `state`=3 ,`make_up_lesson_id`=0, `make_up_lesson_remark`='' WHERE `lesson_id` = '$lesson_id' and deleted =0 and actived = 1 and `stu_linking_id` >0";
            $query = "UPDATE `lesson_attendance` SET `state`=3  WHERE `lesson_id` = '$lesson_id' and deleted =0 and actived = 1 and `stu_linking_id` >0";
            $sth = $this->db->prepare($query);
            $sth->execute();
            //$query = "UPDATE `lesson_attendance` SET `state`=6 ,`make_up_lesson_id`=0, `make_up_lesson_remark`='' WHERE `lesson_id` = '$lesson_id' and deleted =0 and actived = 1  and `staff_id` >0";
            $query = "UPDATE `lesson_attendance` SET `state`=6  WHERE `lesson_id` = '$lesson_id' and deleted =0 and actived = 1  and `staff_id` >0";
            $sth = $this->db->prepare($query);
            $sth->execute();
        }
    }

    public function undoOneLessonforStudent($lesson_id, $specific_student_id) {
        $UndoCancel = $this->undoCancelLesson($lesson_id, $specific_student_id);
        $UndoMakeUp = $this->undoMakeUpLesson($lesson_id, $specific_student_id);

        if ($UndoCancel || $UndoMakeUp) {

            $this->updateLessonLeaveStatus($lesson_id, LeaveAssistant::LEAVE_STATUS_INCOMPLETE);
//            $query = "UPDATE `lesson_attendance` SET `state`=3 ,`make_up_lesson_id`=0, `make_up_lesson_remark`='' WHERE `lesson_id` = '$lesson_id' 
//                and deleted =0 and actived = 1 and `stu_linking_id` >0 AND `stu_linking_id`='$specific_student_id'";
            $query = "UPDATE `lesson_attendance` SET `state`=3 WHERE `lesson_id` = '$lesson_id' 
                and deleted =0 and actived = 1 and `stu_linking_id` >0 AND `stu_linking_id`='$specific_student_id'";
            $sth = $this->db->prepare($query);
            $sth->execute();
        }
    }

    #############################
    #### Delete Leave Record ####
    #############################

    private function isMakeUpClassInPerviousState($lesson_id, $specific_student_id) {
        $query = "SELECT `attend_id` FROM `lesson_attendance` WHERE `lesson_id`='$lesson_id'
            and `actived`=1 and `deleted`=0 and`stu_linking_id` ='$specific_student_id' and
                `make_up_lesson_id`>0 ";
        $result = $this->db->prepare($query);
        $result->execute();
        $MakeUpLessonRecord = $result->fetchAll(PDO::FETCH_ASSOC);
        if (count($MakeUpLessonRecord) <= 0) {
            //if make_up_lesson_id = NULL OR 0, find empty
            return FALSE;
        } else {
            //if make_up_lesson_id != NULL OR 0, find record
            return TRUE;
        }
    }

    public function UpdateLessonStateForStudent($lesson_id, $specific_student_id) {
        $state = LessonState::NORMAL_LESSON;
        if ($this->isMakeUpClassInPerviousState($lesson_id, $specific_student_id)) {
            $state = LessonState::MAKE_UP_LESSON;
        }
        $query = "UPDATE `lesson_attendance` SET `state`='$state' WHERE `actived`=1 and `deleted`=0 and `stu_linking_id`='$specific_student_id'
            and `lesson_id`='$lesson_id'";
        $sth = $this->db->prepare($query);
        $sth->execute();
        
        $this->UpdateLeaveStatusNoLeaveRecord($lesson_id);
    }

    public function UpdateLessonStateNoOtherLeave($lesson_id) {
        #########################################################
        #### Update related student attendance `state` to 1, ####
        ####  Where student is no other leave application    ####
        $query = "SELECT `stu_linking_id` FROM `lesson_attendance` WHERE `lesson_id`='$lesson_id' and `deleted`=0 and `actived`=1 
            and `stu_linking_id` >0 and `stu_linking_id` not in(SELECT `stu_linking_id` FROM `leave_app`la,`leave_lesson`ll 
            WHERE la.`leave_id` = ll.`leave_id` and la.`deleted`= 0 and la.`actived`= 1 and ll.`actived` = 1 and ll.`deleted`= 0 
            and ll.`lesson_id`= '$lesson_id' and la.`stu_linking_id`>0)";
        $result = $this->db->prepare($query);
        $result->execute();
        $StudentsNoOtherLeaveApplication = $result->fetchAll(PDO::FETCH_ASSOC);
        foreach ($StudentsNoOtherLeaveApplication as $SNOLA) {
            $studentNeedToUpdate = $SNOLA['stu_linking_id'];
            $this->UpdateLessonStateForStudent($lesson_id, $studentNeedToUpdate);
        }
        ## Final Checking ##
        $this->UpdateLeaveStatusNoLeaveRecord($lesson_id);
    }

    private function UpdateLeaveStatusNoLeaveRecord($lesson_id) {
        ####################################################
        ####     Update lesson, leave_status to 0,      ####
        #### if the lesson no follow up action to take, ####
        ####       all state = 1 (normal lesson)        ####
        ####   OR state = 2 (make up lesson arranged)   ####
        ## Update lesson leave_status = 0 ##
        $query = "SELECT `lesson_id`,`stu_linking_id` FROM `lesson_attendance` WHERE `stu_linking_id` >0 and `actived`=1 and 
            `deleted`=0 and `state` != 1 and `state`!=2 and `lesson_id`='$lesson_id'";
        $sth = $this->db->prepare($query);
        $sth->execute();
        $Not_Finish_Attendance_record = $sth->fetchAll(PDO::FETCH_ASSOC);
        //var_dump(count(($Not_Finish_Attendance_record)));
        if (count(($Not_Finish_Attendance_record)) == 0) {
            $this->updateLessonLeaveStatus($lesson_id, LeaveAssistant::LEAVE_STATUS_COMPLETE);
        }
    }

    public function DeleteLeaveLessonRecord($leave_lesson_id) {
        $query = "UPDATE `leave_lesson` SET `deleted`=1 WHERE `leave_lesson_id`='$leave_lesson_id' AND `deleted`=0";
        $sth = $this->db->prepare($query);
        $sth->execute();
    }

    ###########################
    #### Button Controller ####
    ###########################

    private function isLessonInvoiceGened($lesson_id, $student_id) {
        $query = "SELECT `stu_linking_id` FROM `lesson_attendance` WHERE `lesson_id`='$lesson_id'
            and `actived` =1 AND `deleted`=0 and `stu_linking_id`='$student_id' AND `invoice_gened`=1";
        $result = $this->db->prepare($query);
        $result->execute();
        $InvoiceGenedRecord = $result->fetchAll(PDO::FETCH_ASSOC);
        if (count($InvoiceGenedRecord) == 0) {
            return FALSE;
        } else {
            return TRUE;
        }
    }

    private function isLessonPayrollGened($lesson_id, $staff_id) {
        $query = "SELECT `staff_id` FROM `lesson_attendance` WHERE `lesson_id`='$lesson_id' 
            and `actived` =1 AND `deleted`=0 and `staff_id`='$staff_id' AND `payroll_gened`=1";
        $result = $this->db->prepare($query);
        $result->execute();
        $PayrollGenedRecord = $result->fetchAll(PDO::FETCH_ASSOC);
        if (count($PayrollGenedRecord) == 0) {
            return FALSE;
        } else {
            return TRUE;
        }
    }

    private function isLessonInMakeUpChain($lesson_id, $student_id) {
        $ThisStudentMakeUpLesson = $this->findMakeUpLessonOf($lesson_id, $student_id);
//        var_dump($ThisStudentMakeUpLesson);
        if (count($ThisStudentMakeUpLesson) == 0) {
            return FALSE;
        } else {
            foreach ($ThisStudentMakeUpLesson as $TSMUL) {
                $ThisStudentMakeUpLesson_id = $TSMUL['lesson_id'];
                $ThisStudentMakeUpLessonOfMakeUpLesson = $this->findMakeUpLessonOf($ThisStudentMakeUpLesson_id, $student_id);
                if (count($ThisStudentMakeUpLessonOfMakeUpLesson) == 0) {
                    return FALSE;
                } else {
                    $query = "SELECT `date`,`start_time`,`end_time` FROM `lesson` WHERE `lesson_id`='$ThisStudentMakeUpLesson_id' 
                        and `actived`=1 and `deleted`=0";
                    $result = $this->db->prepare($query);
                    $result->execute();
                    $makeUpLesson_info = $result->fetchAll(PDO::FETCH_ASSOC);
                    foreach ($makeUpLesson_info as $MUL_info){
                        $info_content = $MUL_info['date'].'('.$MUL_info['start_time'].' - '.$MUL_info['end_time'].')';
                    }
                    return $info_content;
                    //return TRUE;
                }
            }
        }
    }
    
    private function isMakeUpLessonUsedByOtherLeaveRecord($lesson_id){
        $ThisMakeUpLesson = $this->findMakeUpLessonOf($lesson_id,NULL);
        //var_dump($ThisMakeUpLesson);
        if (count($ThisMakeUpLesson) == 0) {
            return FALSE;
        } else {
            foreach ($ThisMakeUpLesson as $TMUL) {
                $ThisMakeUpLesson_id = $TMUL['lesson_id'];
                //var_dump($ThisMakeUpLesson_id);
                //find the lesson_id in leave_lesson
                $query = "SELECT la.`leave_id` FROM `leave_app` la,`leave_lesson` ll 
                    WHERE la.`actived`=1 and la.`deleted`=0 and ll.`actived`=1 and 
                    ll.`deleted`=0 and ll.`lesson_id`='$ThisMakeUpLesson_id' and 
                        la.`leave_id`=ll.`leave_id`";// and la.`is_approved`=1
                $result = $this->db->prepare($query);
                $result->execute();
                $isLessonIdInLeaveLesson = $result->fetchAll(PDO::FETCH_ASSOC);
                //var_dump($isLessonIdInLeaveLesson);
                if(count($isLessonIdInLeaveLesson)==0){
                    return FALSE;
                }
                else{
                    return TRUE;
                }
            }
        }
    }

    public function isUndoButtonAvailable($lesson_id) {
        $available = true;
        $isLessonInMakeUpChain = FALSE;
        $isLessonInvoiceGened = FALSE;
        $isLessonPayrollGened = FALSE;
        $isMakeUpLessonUsedByOtherLeaveRecord = FALSE;
        $query = "SELECT `stu_linking_id` FROM `lesson_attendance` WHERE `lesson_id`='$lesson_id' 
            and `actived` =1 AND `deleted`=0 and `stu_linking_id`>0";
        $result = $this->db->prepare($query);
        $result->execute();
        $StudentsInLesson = $result->fetchAll(PDO::FETCH_ASSOC);
        foreach ($StudentsInLesson as $StuIL) {
            $student_id = $StuIL['stu_linking_id'];
            if ($available == TRUE) {
                $isLessonInvoiceGened = $this->isLessonInvoiceGened($lesson_id, $student_id);
                $available = !($isLessonInvoiceGened);
            }
            if ($available == TRUE) {
                $isLessonInMakeUpChain = $this->isLessonInMakeUpChain($lesson_id, $student_id);
                $available = !($isLessonInMakeUpChain);
            }
        }

        $query = "SELECT `staff_id` FROM `lesson_attendance` WHERE `lesson_id`='$lesson_id' 
            and `actived` =1 AND `deleted`=0 and `staff_id` >0";
        $result = $this->db->prepare($query);
        $result->execute();
        $StaffsInLesson = $result->fetchAll(PDO::FETCH_ASSOC);
        foreach ($StaffsInLesson as $StaffIL) {
            $staff_id = $StaffIL['staff_id'];
            if ($available == TRUE) {
                $isLessonPayrollGened = $this->isLessonPayrollGened($lesson_id, $staff_id);
                $available = !($isLessonPayrollGened);
            }
        }
        
        if($available == TRUE){
            $isMakeUpLessonUsedByOtherLeaveRecord = $this->isMakeUpLessonUsedByOtherLeaveRecord($lesson_id);
            $available = !($isMakeUpLessonUsedByOtherLeaveRecord);
        }
        if($isLessonInMakeUpChain||$isLessonInvoiceGened||$isLessonPayrollGened||$isMakeUpLessonUsedByOtherLeaveRecord)
            $available = FALSE;
            
        return array(
            'available' => $available,
            'isLessonInMakeUpChain' => $isLessonInMakeUpChain,
            'isLessonInvoiceGened' => $isLessonInvoiceGened,
            'isLessonPayrollGened' => $isLessonPayrollGened,
            'isMakeUpLessonUsed' => $isMakeUpLessonUsedByOtherLeaveRecord
        );
    }

}

?>