Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 28 |
CRAP | |
0.00% |
0 / 1067 |
| V4AttendanceService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 28 |
33306.00 | |
0.00% |
0 / 1067 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 4 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| intimationNonSendStudentsForMarkedAttendance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| intimationForUnmarkedFaculty | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 29 |
|||
| updateIntimationSendStatus | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| saveStudentAttendanceDetailsForExternalApi | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 37 |
|||
| saveStudentAttendace | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 53 |
|||
| validateSaveStudentAttendace | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 9 |
|||
| searchStudentAttendance | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 73 |
|||
| insertStudentAttendace | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| updateStudentAttendace | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| saveAttendaceMarkedUser | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 25 |
|||
| validateSaveAttendaceMarkedUser | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 7 |
|||
| searchAttendanceMarkedUser | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 53 |
|||
| insertAttendaceMarkedUser | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| applyDLToV4Attendance | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 52 |
|||
| applyLeaveToV4Attendance | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 31 |
|||
| getV4StudentAttendacneDetails | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 49 |
|||
| getV4StudentAttendacneDraftDetails | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 48 |
|||
| updateApprovedLeaveDetailsToV4Attendance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 21 |
|||
| getTermWiseStudentAttendanceConfirmedReport | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 7 |
|||
| calculateHourWiseAttendanceDetails | |
0.00% |
0 / 1 |
342.00 | |
0.00% |
0 / 111 |
|||
| calculateDayWiseAttendancePercentage | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 32 |
|||
| fetchDayWiseAttendanceDetails | |
0.00% |
0 / 1 |
650.00 | |
0.00% |
0 / 232 |
|||
| getAllEvents | |
0.00% |
0 / 1 |
272.00 | |
0.00% |
0 / 80 |
|||
| getProgramFromBatch | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| searchDepartment | |
0.00% |
0 / 1 |
210.00 | |
0.00% |
0 / 35 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service\v4Attendance; | |
| use com\linways\base\dto\Email; | |
| use com\linways\base\dto\EmailTo; | |
| use com\linways\base\dto\EmailFrom; | |
| use com\linways\base\util\StringUtil; | |
| use com\linways\core\ams\professional\logging\Events; | |
| use com\linways\core\ams\professional\constant\Modules; | |
| use com\linways\core\ams\professional\logging\AMSLogger; | |
| use com\linways\core\ams\professional\constant\StatusConstants; | |
| use com\linways\core\ams\professional\service\BaseService; | |
| use com\linways\academics\core\exception\AcademicException; | |
| use com\linways\core\ams\professional\dto\v4\IntimationData; | |
| use com\linways\core\ams\professional\service\CommonService; | |
| use com\linways\core\ams\professional\logging\entities\Staff; | |
| use com\linways\core\ams\professional\dto\v4\IntimationDataProperties; | |
| use com\linways\core\ams\professional\dto\v4\AttendanceMarkedUser; | |
| use com\linways\core\ams\professional\dto\v4\Attendance; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\mapper\AttendanceServiceMapper; | |
| use com\linways\core\ams\professional\dto\SettingsConstents; | |
| class V4AttendanceService extends BaseService | |
| { | |
| private static $_instance = null; | |
| private $mapper = []; | |
| private $logger = null; | |
| private function __construct() | |
| { | |
| $this->logger = AMSLogger::getLogger(); | |
| $this->mapper = AttendanceServiceMapper::getInstance()->getMapper(); | |
| } | |
| // Prevent any object or instance of that class to be cloned | |
| private function __clone() | |
| { | |
| } | |
| // Have a single globally accessible static method | |
| public static function getInstance() | |
| { | |
| if (!is_object(self::$_instance)) // or if( is_null(self::$_instance) ) or if( self::$_instance == null ) | |
| self::$_instance = new self(); | |
| return self::$_instance; | |
| } | |
| /** | |
| * V4 fetch intimation non sent student details | |
| * | |
| * This method fetches the students who are marked as absent and intimation is not yet sent. | |
| * | |
| * @param stdClass $request | |
| * @return Array $response | |
| */ | |
| public function intimationNonSendStudentsForMarkedAttendance($request) | |
| { | |
| $sql = "SELECT s.studentId, s.studentID as intimationRecipientId, 'STUDENT' as intimationRecipientUserType, s.studentName as name, s.studentEmail as emailId, s.parentPhone as mobileNo, GROUP_CONCAT(distinct s2.staffName) as draftedStaffName, sg.name as batchName , GROUP_CONCAT(distinct vamu.marked_hour ORDER BY vamu.marked_hour SEPARATOR ', ') as absentHour | |
| from v4_attendance va | |
| INNER JOIN studentaccount s ON s.studentID = va.student_id | |
| INNER JOIN staffaccounts s2 ON s2.staffID = va.drafted_user_id | |
| INNER JOIN student_program_account spa ON spa.student_id = s.studentID | |
| -- INNER JOIN cluster_groups_relations cgr ON cgr.cluster_id = va.cluster_id | |
| -- INNER JOIN groups_relations gr ON gr.child_groups_id = cgr.groups_id | |
| INNER JOIN `groups` sg ON sg.id = spa.current_batch_id | |
| INNER JOIN v4_attendance_marked_user vamu ON vamu.id = va.v4_attendance_marked_user_table_id | |
| WHERE va.is_absent =1 and va.intimation_send =0 and va.attendance_date ='$request->date' AND sg.id = spa.current_batch_id GROUP BY s.studentID "; | |
| try { | |
| $response = $this->executeQueryForList($sql); | |
| } catch (\Throwable $e) { | |
| throw new AcademicException($e->getCode(), $e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * Get attendance unmarked faculties of a specifc date. | |
| * | |
| * This method fetches the attendance unmarked faculties of a specific date. | |
| * | |
| * @param stdClass $request | |
| * @return Array $response | |
| * @throws AcademicException | |
| **/ | |
| public function intimationForUnmarkedFaculty($request) | |
| { | |
| $sql ="SELECT | |
| s.staffId, | |
| s.staffId as intimationRecipientId, | |
| 'STAFF' as intimationRecipientUserType, | |
| s.staffName as name, | |
| s.staffEmail as emailId, | |
| s.staffPhone as mobileNo, | |
| GROUP_CONCAT(DISTINCT vt.hour ORDER BY vt.hour SEPARATOR ', ') as hours | |
| FROM | |
| v4_timetable vt | |
| LEFT JOIN v4_attendance_marked_user vamu ON | |
| vamu.time_table_id = vt.id | |
| INNER JOIN staffaccounts s ON | |
| s.staffID = vt.staff_id | |
| WHERE | |
| vt.`date` ='$request->date' | |
| AND vamu.id IS NULL | |
| GROUP BY | |
| s.staffId | |
| ORDER BY | |
| s.staffId"; | |
| try { | |
| $response = $this->executeQueryForList($sql); | |
| } catch (\Throwable $e) { | |
| throw new AcademicException($e->getCode(), $e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * update intimation status for attendance | |
| * | |
| * @param Object $request | |
| * @return void | |
| */ | |
| public function updateIntimationSendStatus($request) | |
| { | |
| $sql = "UPDATE v4_attendance SET intimation_send = 1 where attendance_date ='$request->date' and is_absent =1 "; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Throwable $e) { | |
| throw new AcademicException($e->getCode(), $e->getMessage()); | |
| } | |
| return; | |
| } | |
| /** | |
| * Save Student Attendance Status From External Api | |
| * @param array $requestForSaveAttendance | |
| * @throws ProfessionalException | |
| * @author Krishnajith V | |
| */ | |
| public function saveStudentAttendanceDetailsForExternalApi($requestForSaveAttendance){ | |
| $responseMessages = []; | |
| $studentDetails = $requestForSaveAttendance->students; | |
| foreach($studentDetails as $student){ | |
| foreach($student->Hour as $currentHour){ | |
| $student->currentHour = $currentHour; | |
| // Insert Update Attendance Marked User | |
| $markedUserDetails = new AttendanceMarkedUser(); | |
| $markedUserDetails->clusterId = $student->currentHour->clusterId; | |
| $markedUserDetails->attendanceDate = $student->date; | |
| $markedUserDetails->staffId = $student->currentHour->staffId; | |
| $markedUserDetails->markedHour = $student->currentHour->hour; | |
| $markedUserDetails->totalHour = 0; | |
| $markedUserDetails->startTime = $student->currentHour->fromTime; | |
| $markedUserDetails->endTime = $student->currentHour->toTime; | |
| $markedUserDetails->timeTableId = $student->currentHour->timeTableId; | |
| $markedUserDetails->id = $this->saveAttendaceMarkedUser($markedUserDetails); | |
| // Insert Update Attendance | |
| $studentAttendance = new Attendance(); | |
| $studentAttendance->studentName = $student->studentName; | |
| $studentAttendance->hour = $student->currentHour->hour; | |
| $studentAttendance->clusterName = $student->currentHour->clusterName; | |
| $studentAttendance->clusterId = $student->currentHour->clusterId; | |
| $studentAttendance->studentId = $student->studentID; | |
| $studentAttendance->studentProgramId = $student->studentProgramId; | |
| $studentAttendance->attendanceDate = $student->date; | |
| $studentAttendance->v4AttendanceMarkedUserTableId = $markedUserDetails->id; | |
| $studentAttendance->isAbsent = $student->currentHour->isPresent == '1' ? 0 : 1; | |
| $studentAttendance->staffId = $student->currentHour->staffId; | |
| $studentAttendance->startTime = $student->currentHour->fromTime; | |
| $studentAttendance->endTime = $student->currentHour->toTime; | |
| $studentAttendance->timeTableId = $student->currentHour->timeTableId; | |
| $studentAttendance->attendanceMarkedType = "AI_ATTENDANCE"; | |
| $studentAttendance->totalHour = 0; | |
| $studentAttendance->staffName = $student->currentHour->staffName; | |
| $responseMessages[] = $this->saveStudentAttendace($studentAttendance); | |
| } | |
| } | |
| return $responseMessages; | |
| } | |
| /** | |
| * Save Student Attendance | |
| * @param Attendance $attendance | |
| * @return $id | |
| */ | |
| public function saveStudentAttendace (Attendance $attendance){ | |
| $responseMessage = ""; | |
| $attendance = $this->realEscapeObject($attendance); | |
| $attendance->createdBy = $attendance->staffId; | |
| $attendance->updatedBy = $attendance->staffId; | |
| $staffId = $attendance->staffId; | |
| try{ | |
| $this->validateSaveStudentAttendace($attendance); | |
| // for checking Attendacne already marked in another community in sane time slot | |
| $studentAttendanceRequest = new Attendance(); | |
| $studentAttendanceRequest->studentId = $attendance->studentId; | |
| $studentAttendanceRequest->attendanceDate = $attendance->attendanceDate; | |
| $studentAttendanceRequest->startTime = $attendance->startTime; | |
| $studentAttendanceRequest->endTime = $attendance->endTime; | |
| $currentAttendanceDetails = $this->searchStudentAttendance($studentAttendanceRequest); | |
| if(!empty($currentAttendanceDetails) && (count($currentAttendanceDetails) > 1 || reset($currentAttendanceDetails)->clusterId != $attendance->clusterId)) { | |
| $attendaceUpdateStatusStatus = reset($currentAttendanceDetails)->isAbsent == '1' ? 'Absent' : 'Present'; | |
| $responseMessage = "ERROR : Attendance already marked as " . $attendaceUpdateStatusStatus." for ".$attendance->studentName ." on hour ".$attendance->hour ." , " .$attendance->attendanceDate. " for " .reset($currentAttendanceDetails)->clusterName. " by " .reset($currentAttendanceDetails)->staffName; | |
| return $responseMessage; | |
| } | |
| else{ | |
| $currentAttendanceDetail = reset($currentAttendanceDetails); | |
| $attendance->id = $currentAttendanceDetail->id; | |
| if(empty($attendance->id)){ | |
| $attendance->id = $this->insertStudentAttendace($attendance); | |
| $attendaceStatus = $attendance->isAbsent == 1 ? 'Absent' : 'Present'; | |
| $responseMessage = "SUCCESS : Attendance marked as " . $attendaceStatus." for ".$attendance->studentName ." on hour ".$attendance->hour ." , " .$attendance->attendanceDate. " for " .$attendance->clusterName. " by " .$attendance->staffName; | |
| AMSLogger::log_info($this->logger,Events::SAVE_STUDENT_ATTENDANCE_API, [ | |
| "request" => $attendance, | |
| "status" => StatusConstants::SUCCESS | |
| ]); | |
| } | |
| else{ | |
| if($currentAttendanceDetail->isAbsent != '0'){ | |
| $attendaceStatus = $attendance->isAbsent == 1 ? 'Absent' : 'Present'; | |
| $attendance->id = $this->updateStudentAttendace($attendance); | |
| $responseMessage = "SUCCESS : Attendance marked as " . $attendaceStatus." for ".$attendance->studentName ." on hour ".$attendance->hour ." , " .$attendance->attendanceDate. " for " .$attendance->clusterName. " by " .$attendance->staffName; | |
| } | |
| else{ | |
| $attendaceStatus = 'Present'; | |
| $responseMessage = "WARNING : Attendance already marked as " . $attendaceStatus." for ".$attendance->studentName ." on hour ".$attendance->hour ." , " .$attendance->attendanceDate. " for " .$attendance->clusterName. " by " .$attendance->staffName; | |
| } | |
| } | |
| } | |
| }catch(\Exception $e) { | |
| AMSLogger::log_error($this->logger,Events::SAVE_STUDENT_ATTENDANCE_API, [ | |
| "request" => $attendance, | |
| "errorCode" => $e->getCode(), | |
| "errorMessage" => $e->getMessage(), | |
| "status" => StatusConstants::FAILED | |
| ]); | |
| throw new ProfessionalException ($e->getCode(),"FAILED TO SAVE ATTENDANCE PLEASE CHECK DATA"); | |
| } | |
| return $responseMessage ; | |
| } | |
| /** | |
| * Validate save Student attendance | |
| * @param Attendance $attendance | |
| * @return NULL | |
| */ | |
| private function validateSaveStudentAttendace(Attendance $attendance){ | |
| if(empty($attendance->clusterId) || empty($attendance->attendanceDate) || empty($attendance->staffId) || | |
| empty($attendance->studentId) || empty($attendance->studentProgramId) || | |
| empty($attendance->v4AttendanceMarkedUserTableId) || | |
| empty($attendance->attendanceMarkedType) || | |
| empty($attendance->startTime) || empty($attendance->endTime) || empty($attendance->timeTableId) | |
| ){ | |
| throw new ProfessionalException(ProfessionalException::EMPTY_PARAMETERS, "Parameters some of the value is empty or null "); | |
| } | |
| } | |
| /** | |
| * Search Student Attendance | |
| * @param Attendance $request | |
| * @return studentAttendance | |
| */ | |
| public function searchStudentAttendance(Attendance $request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->id)){ | |
| $whereQuery .= " AND id='$request->id' "; | |
| } | |
| if(!empty($request->clusterId)){ | |
| $whereQuery .= " AND cluster_id = '$request->clusterId' "; | |
| } | |
| if(!empty($request->attendanceDate)){ | |
| $whereQuery .= " AND attendance_date = '$request->attendanceDate' "; | |
| } | |
| if(!empty($request->staffId)){ | |
| $whereQuery .= " AND staff_id = '$request->staffId' "; | |
| } | |
| if(!empty($request->markedHour)){ | |
| $whereQuery .= " AND marked_hour = '$request->markedHour' "; | |
| } | |
| if(!empty($request->startTime)){ | |
| $whereQuery .= " AND start_time = '$request->startTime' "; | |
| } | |
| if(!empty($request->endTime)){ | |
| $whereQuery .= " AND end_time = '$request->endTime' "; | |
| } | |
| if(!empty($request->timeTableId)){ | |
| $whereQuery .= " AND time_table_id = '$request->timeTableId' "; | |
| } | |
| if(!empty($request->studentId)){ | |
| $whereQuery .= " AND student_id = '$request->studentId' "; | |
| } | |
| if(!empty($request->studentProgramId)){ | |
| $whereQuery .= " AND student_program_id = '$request->studentProgramId' "; | |
| } | |
| if(!empty($request->v4AttendanceMarkedUserTableId)){ | |
| $whereQuery .= " AND v4_attendance_marked_user_table_id = '$request->v4AttendanceMarkedUserTableId' "; | |
| } | |
| $query = "SELECT | |
| va.id as id, | |
| va.student_id as studentId, | |
| va.student_program_id as studentProgramId, | |
| va.attendance_date as attendanceDate, | |
| va.is_absent as isAbsent, | |
| va.staff_id as staffId, | |
| va.v4_attendance_marked_user_table_id as v4AttendanceMarkedUserTableId, | |
| va.cluster_id as clusterId, | |
| va.duty_leave, | |
| va.start_time as startTime, | |
| va.end_time as endTime, | |
| va.total_hour, | |
| va.drafted_user_id, | |
| va.attendance_marked_type, | |
| va.time_table_id, | |
| va.created_by, | |
| va.created_date, | |
| va.updated_by, | |
| va.updated_date, | |
| va.intimation_send, | |
| c.name as clusterName, | |
| s.staffName as staffName | |
| FROM | |
| v4_attendance va | |
| INNER JOIN cluster c ON | |
| c.id = va.cluster_id | |
| INNER JOIN staffaccounts s ON | |
| s.staffID = va.staff_id | |
| WHERE | |
| 1 = 1"; | |
| try { | |
| $studentAttendance = $this->executeQueryForList($query.$whereQuery.$sortBy.$limitQuery); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException(ProfessionalException::EMPTY_PARAMETERS,"Cannot fetch student attendance "); | |
| } | |
| return $studentAttendance; | |
| } | |
| /** | |
| * insert Student Attendace | |
| * @param Attendance $studentAttendance | |
| * @return $id | |
| */ | |
| private function insertStudentAttendace(Attendance $studentAttendance){ | |
| $sql = "INSERT INTO v4_attendance( student_id, student_program_id, attendance_date, is_absent, staff_id, v4_attendance_marked_user_table_id, cluster_id, start_time, end_time, total_hour, drafted_user_id, attendance_marked_type, time_table_id, created_by ) | |
| VALUES | |
| ('$studentAttendance->studentId','$studentAttendance->studentProgramId','$studentAttendance->attendanceDate','$studentAttendance->isAbsent','$studentAttendance->staffId','$studentAttendance->v4AttendanceMarkedUserTableId','$studentAttendance->clusterId','$studentAttendance->startTime','$studentAttendance->endTime','$studentAttendance->totalHour','$studentAttendance->staffId','$studentAttendance->attendanceMarkedType','$studentAttendance->timeTableId','$studentAttendance->staffId')"; | |
| try { | |
| $studentAttendance->id = $this->executeQuery($sql,true)->id; | |
| return $studentAttendance->id; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * insert Student Attendace | |
| * @param Attendance $studentAttendance | |
| * @return $id | |
| */ | |
| private function updateStudentAttendace(Attendance $studentAttendance){ | |
| $query = "UPDATE | |
| v4_attendance | |
| SET | |
| is_absent = '$studentAttendance->isAbsent', | |
| updated_by = '$studentAttendance->updatedBy' | |
| WHERE | |
| id = '$studentAttendance->id'"; | |
| try { | |
| $this->executeQuery($query); | |
| return $studentAttendance->id; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Save Attendance Marked User | |
| * @param AttendanceMarkedUser $attendanceMarkedUser | |
| * @return $id | |
| */ | |
| public function saveAttendaceMarkedUser (AttendanceMarkedUser $attendanceMarkedUser){ | |
| $attendanceMarkedUser = $this->realEscapeObject($attendanceMarkedUser); | |
| $attendanceMarkedUser->createdBy = $attendanceMarkedUser->staffId; | |
| $attendanceMarkedUser->updatedBy = $attendanceMarkedUser->staffId; | |
| $staffId = $attendanceMarkedUser->staffId; | |
| try{ | |
| $this->validateSaveAttendaceMarkedUser($attendanceMarkedUser); | |
| $attendanceMarkedUser->id = reset($this->searchAttendanceMarkedUser($attendanceMarkedUser))->id; | |
| if(empty($attendanceMarkedUser->id)){ | |
| $attendanceMarkedUser->id = $this->insertAttendaceMarkedUser($attendanceMarkedUser); | |
| AMSLogger::log_info($this->logger,Events::SAVE_ATTENDANCE_MARKING_STAFF_API,[ | |
| "request" => $attendanceMarkedUser, | |
| "status" => StatusConstants::SUCCESS | |
| ]); | |
| } | |
| }catch(\Exception $e) { | |
| AMSLogger::log_error($this->logger,Events::SAVE_ATTENDANCE_MARKING_STAFF_API, [ | |
| "request" => $attendanceMarkedUser, | |
| "errorCode" => $e->getCode(), | |
| "errorMessage" => $e->getMessage(), | |
| "status" => StatusConstants::FAILED | |
| ]); | |
| throw new ProfessionalException ($e->getCode(),$e->getMessage()); | |
| } | |
| return $attendanceMarkedUser->id ; | |
| } | |
| /** | |
| * Validate save attendance marked user | |
| * @param AttendanceMarkedUser $attendanceMarkedUser | |
| * @return NULL | |
| */ | |
| private function validateSaveAttendaceMarkedUser(AttendanceMarkedUser $attendanceMarkedUser){ | |
| if(empty($attendanceMarkedUser->clusterId) || empty($attendanceMarkedUser->attendanceDate) || empty($attendanceMarkedUser->staffId) || | |
| empty($attendanceMarkedUser->markedHour) || | |
| empty($attendanceMarkedUser->startTime) || empty($attendanceMarkedUser->endTime) || empty($attendanceMarkedUser->timeTableId) | |
| ){ | |
| throw new ProfessionalException(ProfessionalException::EMPTY_PARAMETERS, "Parameters some of the value is empty or null "); | |
| } | |
| } | |
| /** | |
| * Search Attendance Marked User | |
| * @param AttendanceMarkedUser $request | |
| * @return attendanceMarkedUsers | |
| */ | |
| public function searchAttendanceMarkedUser(AttendanceMarkedUser $request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->id)){ | |
| $whereQuery .= " AND id='$request->id' "; | |
| } | |
| if(!empty($request->clusterId)){ | |
| $whereQuery .= " AND cluster_id = '$request->clusterId' "; | |
| } | |
| if(!empty($request->attendanceDate)){ | |
| $whereQuery .= " AND attendance_date = '$request->attendanceDate' "; | |
| } | |
| if(!empty($request->staffId)){ | |
| $whereQuery .= " AND staff_id = '$request->staffId' "; | |
| } | |
| if(!empty($request->markedHour)){ | |
| $whereQuery .= " AND marked_hour = '$request->markedHour' "; | |
| } | |
| if(!empty($request->startTime)){ | |
| $whereQuery .= " AND start_time = '$request->startTime' "; | |
| } | |
| if(!empty($request->endTime)){ | |
| $whereQuery .= " AND end_time = '$request->endTime' "; | |
| } | |
| if(!empty($request->timeTableId)){ | |
| $whereQuery .= " AND time_table_id = '$request->timeTableId' "; | |
| } | |
| $query = "SELECT | |
| id, | |
| cluster_id as clusterId, | |
| attendance_date as attendanceDate, | |
| staff_id as staffId, | |
| marked_hour as markedHour, | |
| total_hour, | |
| start_time as startTime, | |
| end_time as endTime, | |
| time_table_id as timeTableId, | |
| created_by, | |
| created_date, | |
| updated_by, | |
| updated_date, | |
| copied_by, | |
| copied_time | |
| FROM | |
| v4_attendance_marked_user | |
| WHERE | |
| 1 = 1"; | |
| try { | |
| $attendanceMarkedUsers = $this->executeQueryForList($query.$whereQuery.$sortBy.$limitQuery); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException(ProfessionalException::EMPTY_PARAMETERS,"Cannot fetch attendance marked user"); | |
| } | |
| return $attendanceMarkedUsers; | |
| } | |
| /** | |
| * insert Attendace Marked User | |
| * @param AttendanceMarkedUser $attendanceMarkedUser | |
| * @return $id | |
| */ | |
| private function insertAttendaceMarkedUser(AttendanceMarkedUser $markedUserDetails){ | |
| $sql = "INSERT INTO v4_attendance_marked_user( cluster_id, attendance_date, staff_id, marked_hour, total_hour, start_time, end_time, time_table_id, created_by ) | |
| VALUES | |
| ('$markedUserDetails->clusterId','$markedUserDetails->attendanceDate','$markedUserDetails->staffId','$markedUserDetails->markedHour','$markedUserDetails->totalHour','$markedUserDetails->startTime','$markedUserDetails->endTime','$markedUserDetails->timeTableId','$markedUserDetails->staffId')"; | |
| try { | |
| $markedUserDetails->id = $this->executeQuery($sql,true)->id; | |
| return $markedUserDetails->id; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * apply DL To V4 Attendance | |
| * @param $studentAttendance | |
| * @return $id | |
| */ | |
| public function applyDLToV4Attendance($studentId,$hour,$attendanceDate,$dlRemarkId,$leaveAppId){ | |
| try { | |
| if(empty($studentId) || empty($attendanceDate) || empty($hour)){ | |
| throw new ProfessionalException(ProfessionalException::EMPTY_PARAMETERS,"Can't save duty leave "); | |
| } | |
| else{ | |
| $userId = $_SESSION['staffID']; | |
| $attendacneReq = new \stdClass(); | |
| $attendacneReq->studentId = $studentId; | |
| $attendacneReq->markedHour = $hour; | |
| $attendacneReq->attendanceDate = $attendanceDate; | |
| $attendacneReq->dlRemarkId = $dlRemarkId; | |
| $attendacneReq->staffId = $userId; | |
| $v4AttendanceDrafts = $this->getV4StudentAttendacneDraftDetails($attendacneReq); | |
| foreach($v4AttendanceDrafts as $studentAttendanceDraft){ | |
| $query = "UPDATE | |
| v4_attendance_draft | |
| SET | |
| grant_leave = $dlRemarkId, | |
| grant_leave_updated_by = $userId, | |
| grant_leave_approved_date = now() | |
| WHERE | |
| id = '$studentAttendanceDraft->id'"; | |
| $this->executeQuery($query); | |
| } | |
| $v4Attendances = $this->getV4StudentAttendacneDetails($attendacneReq); | |
| foreach($v4Attendances as $studentAttendance){ | |
| $query = "UPDATE | |
| v4_attendance | |
| SET | |
| grant_leave = $dlRemarkId, | |
| grant_leave_updated_by = $userId, | |
| grant_leave_approved_date = now(), | |
| student_leave_type_id = $leaveAppId | |
| WHERE | |
| id = '$studentAttendance->id'"; | |
| $this->executeQuery($query); | |
| } | |
| AMSLogger::log_info($this->logger,Events::SAVE_STUDENT_DUTY_LEAVE,[ | |
| "request" => $attendacneReq, | |
| "status" => StatusConstants::SUCCESS | |
| ]); | |
| } | |
| } catch (\Exception $e) { | |
| AMSLogger::log_error($this->logger,Events::SAVE_STUDENT_DUTY_LEAVE, [ | |
| "request" => $attendacneReq, | |
| "errorCode" => $e->getCode(), | |
| "errorMessage" => $e->getMessage(), | |
| "status" => StatusConstants::FAILED | |
| ]); | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * apply DL To V4 Attendance | |
| * @param $studentAttendance | |
| * @return $id | |
| */ | |
| public function applyLeaveToV4Attendance($studentId,$hour,$attendanceDate,$leaveAppId){ | |
| try { | |
| $userId = $_SESSION['staffID']; | |
| $attendacneReq = new \stdClass(); | |
| $attendacneReq->studentId = $studentId; | |
| $attendacneReq->markedHour = $hour; | |
| $attendacneReq->attendanceDate = $attendanceDate; | |
| $attendacneReq->staffId = $userId; | |
| $v4Attendances = $this->getV4StudentAttendacneDetails($attendacneReq); | |
| foreach($v4Attendances as $studentAttendance){ | |
| $query = "UPDATE | |
| v4_attendance | |
| SET | |
| student_leave_type_id = $leaveAppId | |
| WHERE | |
| id = '$studentAttendance->id'"; | |
| $this->executeQuery($query); | |
| } | |
| AMSLogger::log_info($this->logger,Events::SAVE_STUDENT_LEAVE_TO_ATTENDANCE,[ | |
| "request" => $attendacneReq, | |
| "status" => StatusConstants::SUCCESS | |
| ]); | |
| } catch (\Exception $e) { | |
| AMSLogger::log_error($this->logger,Events::SAVE_STUDENT_LEAVE_TO_ATTENDANCE, [ | |
| "request" => $attendacneReq, | |
| "errorCode" => $e->getCode(), | |
| "errorMessage" => $e->getMessage(), | |
| "status" => StatusConstants::FAILED | |
| ]); | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Search V4 Student Attendance | |
| * @param $request | |
| * @return attendanceMarkedUsers | |
| */ | |
| public function getV4StudentAttendacneDetails($request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->attendanceDate)){ | |
| $whereQuery .= " AND va.attendance_date = '$request->attendanceDate' "; | |
| } | |
| if(!empty($request->markedHour)){ | |
| $whereQuery .= " AND vamu.marked_hour = '$request->markedHour' "; | |
| } | |
| if(!empty($request->studentId)){ | |
| $whereQuery .= " AND va.student_id = '$request->studentId' "; | |
| } | |
| $query = "SELECT DISTINCT | |
| va.id, | |
| va.student_id, | |
| va.student_program_id, | |
| va.attendance_date, | |
| va.is_absent, | |
| va.staff_id, | |
| va.v4_attendance_marked_user_table_id, | |
| va.cluster_id, | |
| va.duty_leave, | |
| va.start_time, | |
| va.end_time, | |
| va.total_hour, | |
| va.drafted_user_id, | |
| va.attendance_marked_type, | |
| va.time_table_id, | |
| va.created_by, | |
| va.created_date, | |
| va.updated_by, | |
| va.updated_date, | |
| va.intimation_send, | |
| va.grant_leave, | |
| va.grant_leave_updated_by, | |
| va.grant_leave_approved_date, | |
| va.timetable_common_hour_id | |
| FROM | |
| v4_attendance va | |
| INNER JOIN v4_attendance_marked_user vamu ON | |
| vamu.id = va.v4_attendance_marked_user_table_id | |
| WHERE | |
| 1 = 1"; | |
| try { | |
| $v4Attendance = $this->executeQueryForList($query.$whereQuery.$sortBy.$limitQuery); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException(ProfessionalException::EMPTY_PARAMETERS,"Cannot fetch attendance marked user"); | |
| } | |
| return $v4Attendance; | |
| } | |
| /** | |
| * Search V4 Student Draft Attendance | |
| * @param $request | |
| * @return attendanceMarkedUsers | |
| */ | |
| public function getV4StudentAttendacneDraftDetails($request){ | |
| $request = $this->realEscapeObject($request); | |
| $whereQuery = ""; | |
| if(!empty($request->attendanceDate)){ | |
| $whereQuery .= " AND vad.attendance_date = '$request->attendanceDate' "; | |
| } | |
| if(!empty($request->markedHour)){ | |
| $whereQuery .= " AND vadu.marked_hour = '$request->markedHour' "; | |
| } | |
| if(!empty($request->studentId)){ | |
| $whereQuery .= " AND vad.student_id = '$request->studentId' "; | |
| } | |
| $query = "SELECT DISTINCT | |
| vad.id, | |
| vad.student_id, | |
| vad.student_program_id, | |
| vad.attendance_date, | |
| vad.is_absent, | |
| vad.staff_id, | |
| vad.v4_attendance_drafted_user_table_id, | |
| vad.cluster_id, | |
| vad.duty_leave, | |
| vad.start_time, | |
| vad.end_time, | |
| vad.total_hour, | |
| vad.drafted_user_id, | |
| vad.attendance_marked_type, | |
| vad.time_table_id, | |
| vad.created_by, | |
| vad.created_date, | |
| vad.updated_by, | |
| vad.updated_date, | |
| vad.grant_leave, | |
| vad.grant_leave_updated_by, | |
| vad.grant_leave_approved_date, | |
| vad.timetable_common_hour_id | |
| FROM | |
| v4_attendance_draft vad | |
| INNER JOIN v4_attendance_drafted_user vadu ON | |
| vadu.id = vad.v4_attendance_drafted_user_table_id | |
| WHERE | |
| 1 = 1"; | |
| try { | |
| $v4AttendanceDraft = $this->executeQueryForList($query.$whereQuery.$sortBy.$limitQuery); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException(ProfessionalException::EMPTY_PARAMETERS,"Cannot fetch attendance marked user"); | |
| } | |
| return $v4AttendanceDraft; | |
| } | |
| /** | |
| * update approved leave details to marked attendacne | |
| * @author Krishnajith | |
| */ | |
| public function updateApprovedLeaveDetailsToV4Attendance(){ | |
| $query = "UPDATE v4_attendance va | |
| INNER JOIN student_leave_application sla ON va.student_id = sla.student_Id | |
| AND va.attendance_date BETWEEN sla.start_date AND sla.end_date | |
| AND FIND_IN_SET(va.timetable_common_hour_id,sla.session_key) | |
| AND va.grant_leave IS NULL | |
| AND va.student_leave_type_id IS NULL | |
| INNER JOIN student_leave_type slt ON slt.id = sla.leave_type_Id | |
| LEFT JOIN duty_leave_remarks dlr ON dlr.dlrId = sla.dlrId | |
| SET | |
| va.student_leave_type_id = slt.id, | |
| va.grant_leave = CASE WHEN slt.isDL = 1 THEN sla.dlrId ELSE va.grant_leave END, | |
| va.grant_leave_updated_by = CASE WHEN slt.isDL = 1 THEN sla.approved_by ELSE va.grant_leave_updated_by END, | |
| va.grant_leave_approved_date = CASE WHEN slt.isDL = 1 THEN NOW() ELSE va.grant_leave_approved_date END | |
| WHERE sla.status = 'APPROVED' "; | |
| try { | |
| $this->executeQuery($query); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException(ProfessionalException::EMPTY_PARAMETERS,"Error occurred while updating attendance by leave automation"); | |
| } | |
| return ; | |
| } | |
| /** | |
| * Ter wise attendance details | |
| * | |
| * fetch aganist hour and day based on conditions | |
| * | |
| * @param Object | |
| * @return Object in case of Day Wise Array in case of Hour Wise | |
| * @author Midhun Shaji | |
| **/ | |
| public function getTermWiseStudentAttendanceConfirmedReport($request){ | |
| if($request->attendanceFetchingType === 'DAY_WISE'){ | |
| return $this->calculateDayWiseAttendancePercentage($request); | |
| } | |
| else{ | |
| return $this->calculateHourWiseAttendanceDetails($request); | |
| } | |
| } | |
| public function calculateHourWiseAttendanceDetails($request){ | |
| $whereQuery = ""; | |
| $groupByCond = ""; | |
| // CHECKING THE SETTINGS FOR CREDIT ENABLED CALCULATION | |
| $attendanceRule = CommonService::getInstance()->getSettings(SettingsConstents::ATTENDANCE_SETTINGS, SettingsConstents::ATTENDANCE_CALCULATION_BASED_ON_CREDIT_RULE); | |
| $atendanceRule = json_decode($attendanceRule); | |
| if($atendanceRule->enableAttendanceBasedOnCredit === '1'){ | |
| $groupByCond = " GROUP BY s.studentID, vas.id, sg.academic_term_id"; | |
| } | |
| if (!empty($request->termId)) { | |
| $whereQuery .= " AND sg.academic_term_id = '$request->termId' "; | |
| } | |
| if (!empty($request->paperSubjectId)) { | |
| $whereQuery .= " AND sg.paperSubjectId = '$request->paperSubjectId' "; | |
| } | |
| if (count($request->paperSubjectIds)) { | |
| $whereQuery .= " AND sg.paperSubjectId IN ('" .implode("','",$request->paperSubjectIds). "')"; | |
| $groupByCond = " GROUP BY s.studentID, vas.id, sg.academic_term_id"; | |
| } | |
| if (!empty($request->studentId)) { | |
| $whereQuery .= " AND spa.student_id = '$request->studentId' "; | |
| } | |
| if(!empty($request->attendanceClosingDate)) { | |
| $whereQuery .= " AND DATE_FORMAT(CONCAT(vamu.attendance_date,' ',vamu.end_time), '%Y-%m-%d %H:%i:%s') <= DATE_FORMAT('$request->attendanceClosingDate', '%Y-%m-%d %H:%i:%s') "; | |
| } | |
| // ************************************************************************************************************************************************ | |
| // FOR TAKING THE ATTENDANCE OF THE STUDENTAFTER THE JOINING DATE | |
| // ***************************************************************************************************************************************************** | |
| $query = "SELECT vaar.properties->>'$.attendanceFromJoiningDate' as attendanceFromJoiningDate , vaar.start_date from v4_ams_attendance_rules vaar | |
| INNER JOIN `groups` g ON g.id = vaar.batch_groups_id | |
| INNER JOIN student_program_account spa ON spa.current_batch_id = g.id | |
| INNER JOIN studentaccount s ON s.studentID = spa.student_id | |
| WHERE 1=1 "; | |
| if($request->studentId) { | |
| $query .= " AND s.studentID = '$request->studentId'"; | |
| } | |
| if ($request->batchId) { | |
| $query .= "AND g.id = '$request->batchId'"; | |
| } | |
| if ($request->termId) { | |
| $query .= " AND vaar.academic_term_id = '$request->termId'"; | |
| }else{ | |
| $query .= " AND g.academic_term_id = vaar.academic_term_id "; | |
| } | |
| $attendanceFromJoiningDate = $this->executeQueryForObject($query); | |
| $studenAttendanceDateCond = ""; | |
| if($attendanceFromJoiningDate->attendanceFromJoiningDate === '1'){ | |
| $studenAttendanceDateCond = " AND va.attendance_date >= s.studentJoindate"; | |
| } | |
| if($attendanceFromJoiningDate->start_date){ | |
| $studenAttendanceDateCond = " AND va.attendance_date >= '$attendanceFromJoiningDate->start_date'"; | |
| } | |
| $sql = "SELECT | |
| s.studentID , | |
| caps.id as paperSubjectId, | |
| caps.properties->>'$.credit' as credit, | |
| COUNT(DISTINCT va.student_id, va.attendance_date, va.start_time, va.end_time) AS totalAttendance, | |
| -- COUNT(DISTINCT CASE WHEN va.is_blocked = 0 AND (va.is_absent = 0 OR va.grant_leave IS NOT NULL) THEN CONCAT(va.student_id, va.attendance_date, va.start_time, va.end_time) END) AS totalPresent, | |
| -- ROUND(IFNULL((COUNT(DISTINCT CASE WHEN va.is_blocked = 0 AND (va.is_absent = 0 OR va.grant_leave IS NOT NULL) THEN CONCAT(va.student_id, va.attendance_date, va.start_time, va.end_time) END) / COUNT(DISTINCT va.student_id, va.attendance_date, va.start_time, va.end_time))*100,0),2) AS attendancePercentage | |
| COUNT(DISTINCT CASE WHEN (va.is_absent = 0 OR va.grant_leave IS NOT NULL) THEN CONCAT(va.student_id, va.attendance_date, va.start_time, va.end_time) END) AS totalPresent, | |
| ROUND(IFNULL((COUNT(DISTINCT CASE WHEN (va.is_absent = 0 OR va.grant_leave IS NOT NULL) THEN CONCAT(va.student_id, va.attendance_date, va.start_time, va.end_time) END) / COUNT(DISTINCT va.student_id, va.attendance_date, va.start_time, va.end_time))*100,0),2) AS attendancePercentage | |
| FROM studentaccount s | |
| INNER JOIN student_program_account spa ON spa.student_id = s.studentID | |
| -- COMMENTED AND ADDED THE CONDITION OF BATCH LOG TO GET THE PREVIOUS SEMESTER ATTENDANCE | |
| INNER JOIN student_program_batch_log spbl ON | |
| spbl.program_student_id = spa.id AND | |
| -- spbl.term_id = sg.academic_term_id AND | |
| spbl.properties->> '$.academicStatus' IN ('ACTIVE', 'COMPLETED') | |
| INNER JOIN `groups` g ON g.id = spbl.batch_group_id | |
| INNER JOIN groups_relations gr ON gr.parent_groups_id = g.id | |
| INNER JOIN `groups` sg ON sg.id = gr.child_groups_id | |
| INNER JOIN group_members gm ON gm.groups_id = sg.id AND gm.student_id = spa.id AND gm.academic_status = 'ACTIVE' | |
| INNER JOIN cm_academic_paper_subjects caps ON caps.id = sg.paperSubjectId | |
| INNER JOIN v4_ams_subject vas ON vas.id = caps.ams_subject_id | |
| INNER JOIN cm_academic_paper_subjects caps2 ON caps2.ams_subject_id = vas.id | |
| INNER JOIN `groups` sg2 ON sg2.paperSubjectId = caps2.id AND sg2.academic_term_id = sg.academic_term_id | |
| INNER JOIN groups_relations gr2 ON gr2.child_groups_id = sg2.id | |
| INNER JOIN `groups` g2 ON g2.id = gr2.parent_groups_id AND g2.properties ->>'$.startYear' = g.properties->>'$.startYear' | |
| INNER JOIN group_members gm2 ON gm2.groups_id = sg2.id AND gm2.student_id = spa.id | |
| INNER JOIN cluster_groups_relations cgr ON cgr.groups_id = sg2.id | |
| INNER JOIN cluster c ON c.id = cgr.cluster_id | |
| LEFT JOIN v4_attendance_marked_user vamu ON vamu.cluster_id = c.id | |
| LEFT JOIN v4_attendance va ON va.v4_attendance_marked_user_table_id = vamu.id and va.student_id = s.studentID | |
| WHERE 1 = 1 | |
| $whereQuery $studenAttendanceDateCond $groupByCond | |
| "; | |
| try{ | |
| $response = $this->executeQueryForList($sql); | |
| $attendanceDetails = []; | |
| if($atendanceRule->enableAttendanceBasedOnCredit === '1'){ | |
| $response[0]->attendanceStatus = 'PASS'; | |
| $response[0]->attendancePercentage = '100'; | |
| $response[0]->enableAttendanceBasedOnCredit = true; | |
| foreach($response as $key => $value){ | |
| $creditTotalHour = ($value->credit == '' ? 1 : $value->credit) * $atendanceRule->creditHourWaitage; | |
| if($value->totalAttendance >= $creditTotalHour){ | |
| $attendanceDetails[$value->paperSubjectId]->attendancePercentage = round(($value->totalPresent / $value->totalAttendance ) * 100,2); | |
| } | |
| else{ | |
| $attendanceDetails[$value->paperSubjectId]->attendancePercentage = round(($value->totalPresent / $creditTotalHour) * 100,2); | |
| } | |
| if($attendanceDetails[$value->paperSubjectId]->attendancePercentage < $request->cutOff){ | |
| $attendanceDetails[$value->paperSubjectId]->hasAttendanceShortage = true; | |
| $response[0]->attendanceStatus = 'FAIL'; | |
| $response[0]->attendancePercentage = '0'; | |
| } | |
| } | |
| $response[0]->attendanceDetails = $attendanceDetails; | |
| $attendaceDetails [] = $response[0]; | |
| return $attendaceDetails; | |
| } | |
| return $response; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Calculating Day Wise Attendance Details | |
| * | |
| * In this case the day wise attendance details is calculated as various type as discussed below | |
| * 1. Total working days aganist timetable | |
| * 2. Total working days aganist Attendance | |
| * 3. Specific hour calculation - not handled (scopes and queies are added for development) | |
| * 4. Rule based on absence in any hour(not - handled) | |
| * | |
| * @param Object $request | |
| * @return Object | |
| * @author Midhun Shaji | |
| **/ | |
| public function calculateDayWiseAttendancePercentage($request){ | |
| try { | |
| $result = $this->fetchDayWiseAttendanceDetails($request); | |
| $attendanceResponse = $result->attendanceDetails; | |
| foreach ($attendanceResponse as $student) { | |
| $totalHours = ($student->properties->hoursInMorningSessionCount) + ($student->properties->hoursInAfternoonSessionCount); | |
| $halfDayPercent = (($student->properties->halfDayPresentHourCount / $totalHours) * 100); | |
| $fullDayPercent = (($student->properties->fullDayPresentHourCount / $totalHours) * 100); | |
| $sum = 0.0; | |
| foreach ($student->attendanceDates as $date) { | |
| $percent = ($date->presentCount / $date->totalMarkedHour) * 100; | |
| if ($percent >= $fullDayPercent) { | |
| $sum = $sum + 1.0; | |
| } else if ($percent >= $halfDayPercent && $percent < $fullDayPercent) { | |
| $sum = $sum + 0.5; | |
| } | |
| } | |
| if($result->twdAganistTimetable === '1'){ | |
| $student->attendancePercentage = round(($sum / sizeof($result->totalWorkingDays)) * 100, 2); | |
| $student->totalWorkingDates = sizeof($result->totalWorkingDays); | |
| $student->totalDaysPresent = $sum; | |
| } | |
| else{ | |
| $student->attendancePercentage = round(($sum / sizeof($student->attendanceDates)) * 100, 2); | |
| $student->totalWorkingDates = sizeof($student->attendanceDates); | |
| $student->totalDaysPresent = $sum; | |
| } | |
| } | |
| return $student; | |
| }catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Fetch Day Wise Attendance Details | |
| * | |
| * In this case the day wise attendance details is calculated as various type as discussed below | |
| * 1. Total working days aganist timetable | |
| * 2. Total working days aganist Attendance | |
| * 3. Specific hour calculation - not handled (scopes and queies are added for development) | |
| * 4. Rule based on absence in any hour(not - handled) | |
| * | |
| * @param Object $request | |
| * @return Object | |
| * @author Midhun Shaji | |
| **/ | |
| public function fetchDayWiseAttendanceDetails($request){ | |
| $request = $this->realEscapeObject($request); | |
| $twdsettings = CommonService::getInstance()->getSettings(SettingsConstents::V4_ATTENDANCE_SETTINGS, SettingsConstents::ENABLE_TOTAL_WORKING_DAYS_FROM_TIMETABLE_IN_CONSOLIDATED); | |
| $joinCond = ""; | |
| $student = ""; | |
| $totalWorkingDaysCond = ""; | |
| // ************************************************************************************************************************************************ | |
| // FOR TAKING THE ATTENDANCE OF THE STUDENTAFTER THE JOINING DATE | |
| // ***************************************************************************************************************************************************** | |
| $query = "SELECT vaar.properties->>'$.attendanceFromJoiningDate' as attendanceFromJoiningDate, vaar.start_date from v4_ams_attendance_rules vaar | |
| INNER JOIN `groups` g ON g.id = vaar.batch_groups_id | |
| INNER JOIN student_program_account spa ON spa.current_batch_id = g.id | |
| INNER JOIN studentaccount s ON s.studentID = spa.student_id | |
| WHERE 1=1 "; | |
| if($request->studentId) { | |
| $query .= " AND s.studentID = '$request->studentId'"; | |
| } | |
| if ($request->batchId) { | |
| $query .= "AND g.id = '$request->batchId'"; | |
| } | |
| if ($request->termId) { | |
| $query .= " AND vaar.academic_term_id = '$request->termId'"; | |
| }else{ | |
| $query .= " AND g.academic_term_id = vaar.academic_term_id "; | |
| } | |
| $attendanceFromJoiningDate = $this->executeQueryForObject($query); | |
| $studenAttendanceDateCond = ""; | |
| if($attendanceFromJoiningDate->attendanceFromJoiningDate === '1'){ | |
| $studenAttendanceDateCond = " AND va.attendance_date >= sa.studentJoindate"; | |
| } | |
| if($attendanceFromJoiningDate->start_date){ | |
| $studenAttendanceDateCond = " AND va.attendance_date >= '$attendanceFromJoiningDate->start_date'"; | |
| } | |
| if($request->studentId) { | |
| $student .= " AND spa.student_id = '$request->studentId'"; | |
| } | |
| if ($request->startDate && $request->endDate) { | |
| $joinCond = "AND va.attendance_date BETWEEN '$request->startDate' AND '$request->endDate'"; | |
| $totalWorkingDaysCond = "AND vt.`date` BETWEEN '$request->startDate' AND '$request->endDate'"; | |
| } | |
| if($request->attendanceClosingDate){ | |
| $joinCond = "AND va.attendance_date <= '$request->attendanceClosingDate'"; | |
| $totalWorkingDaysCond = "AND vt.`date` <= '$request->attendanceClosingDate'"; | |
| } | |
| $calenderRequest = new \stdClass(); | |
| $calenderRequest->fetchOnlyHolidays = true; | |
| $calenderRequest->startDate = $request->startDate ; | |
| $calenderRequest->endDate = $request->endDate; | |
| if($request->attendanceClosingDate){ | |
| $calenderRequest->endDate = $request->attendanceClosingDate; | |
| } | |
| $calenderRequest->deptIds=[]; | |
| if(!empty($request->programId)){ | |
| $calenderRequest->programIds=[$request->programId]; | |
| }else{ | |
| $calenderRequest->programIds=[]; | |
| } | |
| if(!empty($request->batchId)){ | |
| $calenderRequest->batchIds=[$request->batchId]; | |
| }else{ | |
| $calenderRequest->batchIds=[]; | |
| } | |
| $events = $this->getAllEvents($calenderRequest); | |
| $holidays = array_filter($events,function($event){ | |
| return $event->is_holiday === '1'; | |
| }); | |
| if(count($holidays)>0){ | |
| $eventDates = array_column($holidays,'date'); | |
| $joinCond .= " AND va.attendance_date NOT IN ('" .implode("','",$eventDates). "')"; | |
| $totalWorkingDaysCond .= " AND vt.`date` NOT IN ('" .implode("','",$eventDates). "')"; | |
| } | |
| $specificHourEnabled = CommonService::getInstance()->getSettings(SettingsConstents::V4_ATTENDANCE_SETTINGS, SettingsConstents::ENABLE_SPECIFIC_HOUR_CALCULATION_FOR_DAY_WISE_REPORT); | |
| if($specificHourEnabled == 1){ | |
| $sql = "SELECT | |
| st.studentId as studentId, | |
| st.rollNo AS rollNo, | |
| st.regNo AS regNo, | |
| st.studentName AS studentName, | |
| st.studentJoindate, | |
| spa.blocked_status, | |
| vamu.marked_hour as hour, | |
| CASE | |
| -- WHEN va.is_blocked = 1 THEN 'absent' | |
| WHEN va.is_absent = 1 AND va.grant_leave IS NULL THEN 'absent' | |
| ELSE 'present' | |
| END as attendance_status, | |
| va.attendance_date AS attendanceDate, | |
| vaar.properties AS properties | |
| FROM | |
| cluster_groups_relations cgr | |
| INNER JOIN groups_relations gr ON | |
| cgr.groups_id = gr.child_groups_id | |
| INNER JOIN `groups` g ON | |
| g.id = gr.parent_groups_id | |
| INNER JOIN v4_ams_attendance_rules vaar ON | |
| vaar.batch_groups_id = g.id | |
| INNER JOIN `groups` sg ON sg.id = gr.child_groups_id | |
| INNER JOIN group_members subjectGroup ON | |
| subjectGroup.groups_id = sg.id | |
| INNER JOIN student_program_account spa ON | |
| spa.id = subjectGroup.student_id | |
| AND spa.academic_status in ('ACTIVE') | |
| AND spa.current_batch_id = gr.parent_groups_id | |
| INNER JOIN program p ON | |
| p.id = spa.current_program_id | |
| INNER JOIN academic_term at2 ON | |
| at2.id = vaar.academic_term_id | |
| INNER JOIN studentaccount st ON | |
| st.studentID = spa.student_id $student | |
| LEFT JOIN v4_attendance va ON | |
| va.student_id = st.studentID | |
| AND va.student_program_id = spa.id | |
| AND va.cluster_id = cgr.cluster_id $studenAttendanceDateCond | |
| $joinCond | |
| LEFT JOIN v4_attendance_marked_user vamu ON | |
| vamu.id = va.v4_attendance_marked_user_table_id | |
| WHERE | |
| 1 = 1 $joinCond"; | |
| if ($request->programId) { | |
| $sql .= "AND p.id = '$request->programId'"; | |
| } | |
| if ($request->batchId) { | |
| $sql .= "AND g.id = '$request->batchId'"; | |
| } | |
| if ($request->termId) { | |
| $sql .= "AND at2.id = '$request->termId'"; | |
| } | |
| $sql .= " | |
| order by | |
| st.rollNo, | |
| va.attendance_date"; | |
| } | |
| else{ | |
| $sql = "SELECT | |
| st.studentId as studentId, | |
| st.rollNo AS rollNo, | |
| st.regNo AS regNo, | |
| st.studentName AS studentName, | |
| st.studentJoindate, | |
| spa.blocked_status, | |
| -- (count(vamu.id) - sum(if (va.is_blocked = 1 OR (va.is_absent = 1 AND va.grant_leave is null), 1 , 0))) AS presentCount, | |
| (count(vamu.id) - sum(if ((va.is_absent = 1 AND va.grant_leave is null), 1 , 0))) AS presentCount, | |
| count(vamu.id) AS totalMarkedHour, | |
| va.attendance_date AS attendanceDate, | |
| vaar.properties AS properties | |
| FROM | |
| cluster_groups_relations cgr | |
| INNER JOIN groups_relations gr ON | |
| cgr.groups_id = gr.child_groups_id | |
| INNER JOIN `groups` g ON | |
| g.id = gr.parent_groups_id | |
| INNER JOIN v4_ams_attendance_rules vaar ON | |
| vaar.batch_groups_id = g.id | |
| INNER JOIN `groups` sg ON sg.id = gr.child_groups_id | |
| INNER JOIN group_members subjectGroup ON | |
| subjectGroup.groups_id = sg.id | |
| INNER JOIN student_program_account spa ON | |
| spa.id = subjectGroup.student_id | |
| AND spa.academic_status in ('ACTIVE') | |
| AND spa.current_batch_id = gr.parent_groups_id | |
| INNER JOIN studentaccount sa ON | |
| sa.studentID = spa.student_id | |
| INNER JOIN program p ON | |
| p.id = spa.current_program_id | |
| INNER JOIN academic_term at2 ON | |
| at2.id = vaar.academic_term_id | |
| INNER JOIN studentaccount st ON | |
| st.studentID = spa.student_id $student | |
| LEFT JOIN v4_attendance va ON | |
| va.student_id = st.studentID | |
| AND va.student_program_id = spa.id | |
| AND va.cluster_id = cgr.cluster_id $studenAttendanceDateCond | |
| $joinCond | |
| LEFT JOIN v4_attendance_marked_user vamu ON | |
| vamu.id = va.v4_attendance_marked_user_table_id | |
| WHERE | |
| 1 = 1 $joinCond"; | |
| if ($request->programId) { | |
| $sql .= "AND p.id = '$request->programId'"; | |
| } | |
| if ($request->batchId) { | |
| $sql .= "AND g.id = '$request->batchId'"; | |
| } | |
| if ($request->termId) { | |
| $sql .= "AND at2.id = '$request->termId'"; | |
| } | |
| $sql .= " group by | |
| st.studentID, | |
| va.attendance_date | |
| order by | |
| st.rollNo, | |
| va.attendance_date"; | |
| } | |
| $query = "SELECT DISTINCT vt.`date`,st.studentID , st.studentName,st.studentJoindate, c.name, subjectGroup.academic_status , subjectGroup.updated_date FROM cluster c | |
| INNER JOIN cluster_groups_relations cgr ON c.id = cgr.cluster_id | |
| INNER JOIN groups_relations gr ON | |
| cgr.groups_id = gr.child_groups_id | |
| INNER JOIN `groups` g ON | |
| g.id = gr.parent_groups_id | |
| INNER JOIN v4_ams_attendance_rules vaar ON | |
| vaar.batch_groups_id = g.id | |
| INNER JOIN `groups` sg ON sg.id = gr.child_groups_id | |
| INNER JOIN group_members subjectGroup ON | |
| subjectGroup.groups_id = sg.id | |
| INNER JOIN student_program_account spa ON | |
| spa.id = subjectGroup.student_id | |
| AND spa.academic_status in ('ACTIVE') | |
| AND spa.current_batch_id = gr.parent_groups_id | |
| INNER JOIN program p ON | |
| p.id = spa.current_program_id | |
| INNER JOIN academic_term at2 ON | |
| at2.id = vaar.academic_term_id | |
| INNER JOIN studentaccount st ON | |
| st.studentID = spa.student_id $student | |
| INNER JOIN v4_timetable vt ON vt.cluster_id = cgr.cluster_id AND vt.suspended = '0' | |
| WHERE 1=1 $totalWorkingDaysCond | |
| AND (subjectGroup.academic_status = 'ACTIVE' OR (subjectGroup.academic_status = 'REMOVED' AND subjectGroup.updated_date >= vt.`date`)) "; | |
| if ($request->programId) { | |
| $query .= "AND p.id = '$request->programId'"; | |
| } | |
| if ($request->batchId) { | |
| $query .= "AND g.id = '$request->batchId'"; | |
| } | |
| if ($request->termId) { | |
| $query .= "AND at2.id = '$request->termId'"; | |
| } | |
| try { | |
| $response = new \stdClass; | |
| $response->attendanceDetails = $this->executeQueryForList($sql, $this->mapper[AttendanceServiceMapper::CONSOLIDATED_REPORTS]); | |
| $response->totalWorkingDays = $this->executeQueryForList($query, $this->mapper[AttendanceServiceMapper::CONSOLIDATED_REPORTS_TOTAL_WORKING_DAYS]); | |
| $response->twdAganistTimetable = $twdsettings; | |
| $response->specificHourEnabled = $specificHourEnabled; | |
| } catch (\Throwable $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * To fetch all the events in academic calender | |
| * | |
| * To fetch the holiday events in academic calemder to avoid from holidays from timetable and attendance regarding reports | |
| * | |
| * @param Object | |
| * @return Array | |
| * @author Midhun Shaji | |
| **/ | |
| public function getAllEvents($academicCalender) | |
| { | |
| //program | |
| if(count($academicCalender->programIds)>0){ | |
| if(count($academicCalender->deptIds) == 0){ | |
| $fetchedDepartment = []; | |
| $departmentRequest = new \stdClass(); | |
| $departmentRequest->programIds = $academicCalender->programIds; | |
| $departments = $this->searchDepartment($departmentRequest); | |
| $fetchedDepartment = array_column($departments, 'id'); | |
| $academicCalender->deptIds = array_unique($fetchedDepartment); | |
| } | |
| } | |
| //batch | |
| if(count($academicCalender->batchIds)>0){ | |
| if(count($academicCalender->programIds) == 0){ | |
| $prgmIds = []; | |
| foreach ($academicCalender->batchIds as $batchId) { | |
| $programRequest = new \stdClass(); | |
| $programRequest->batchId = $batchId; | |
| $id = $this->getProgramFromBatch($programRequest); | |
| $fetchedPrgmIds = array_column($id->id, 'program_id'); | |
| array_push($prgmIds,$fetchedPrgmIds[0]); | |
| } | |
| $academicCalender->programIds = array_unique($prgmIds ); | |
| } | |
| if(count($academicCalender->deptIds) == 0){ | |
| $fetchedDepartment = []; | |
| $departmentRequest = new \stdClass(); | |
| $departmentRequest->programIds = $academicCalender->programIds; | |
| $departments = $this->searchDepartment($departmentRequest); | |
| $fetchedDepartment = array_column($departments, 'id'); | |
| $academicCalender->deptIds = array_unique($fetchedDepartment); | |
| } | |
| } | |
| $whereQuery = ""; | |
| if(!empty($academicCalender->startDate) && !empty($academicCalender->endDate)){ | |
| $academicCalender->startDate = date("Y-m-d", strtotime($academicCalender->startDate)); | |
| $academicCalender->endDate = date("Y-m-d", strtotime($academicCalender->endDate)); | |
| $whereQuery .= " AND vac.date BETWEEN '$academicCalender->startDate' AND '$academicCalender->endDate'"; | |
| } | |
| if (count($academicCalender->batchIds )>0) { | |
| $whereQuery .= " AND (bem.batch_id IS NULL OR bem.batch_id IN ('" . implode("','", $academicCalender->batchIds) . "'))"; | |
| } else { | |
| $whereQuery .= " AND bem.batch_id IS NULL"; | |
| } | |
| if (count($academicCalender->deptIds)>0) { | |
| $whereQuery .= " AND (dem.dept_id IS NULL OR dem.dept_id IN ('" . implode("','", $academicCalender->deptIds) . "'))"; | |
| } else { | |
| $whereQuery .= " AND dem.dept_id IS NULL"; | |
| } | |
| if (count($academicCalender->programIds)>0) { | |
| $whereQuery .= " AND (pem.program_id IS NULL OR pem.program_id IN ('" . implode("','", $academicCalender->programIds) . "'))"; | |
| } else { | |
| $whereQuery .= " AND pem.program_id IS NULL"; | |
| } | |
| if ($academicCalender->fetchOnlyHolidays == true) { | |
| $whereQuery .= " AND vac.is_holiday = '1' "; | |
| } | |
| if ($academicCalender->fetchOnlySpecialEvents == true) { | |
| $whereQuery .= " AND vac.is_special = '1' "; | |
| } | |
| if ($academicCalender->sortByDate == true) { | |
| $whereQuery .= " ORDER BY vac.date"; | |
| } | |
| $query = "SELECT vac.id,vac.title,vac.description,vac.date, | |
| vac.start_time,vac.end_time,vac.is_all_day,vac.is_holiday,vac.is_special, | |
| (CASE | |
| WHEN vac.is_holiday=1 THEN 'event-holiday' | |
| ELSE 'event-normal' | |
| END) as class_name | |
| FROM v4_academic_calender vac | |
| INNER join batch_event_map bem on vac.id = bem.event_id | |
| INNER JOIN department_event_map dem on vac.id = dem.event_id | |
| INNER JOIN program_event_map pem on vac.id = pem.event_id | |
| WHERE 1=1 $whereQuery; "; | |
| try { | |
| $result = $this->executeQueryForList($query); | |
| return $result; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Program Id From Batch Id | |
| * @param Object | |
| * @author Midhun Shaji | |
| */ | |
| public function getProgramFromBatch($request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $query = "SELECT g.program_id FROM `groups` g WHERE g.id = '$request->batchId'"; | |
| try { | |
| $result = new \stdClass(); | |
| $result->id = $this->executeQueryForList($query); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| return $result; | |
| } | |
| /** | |
| * Search Academic term Details | |
| * @param SearchDepartmentRequest $request | |
| * @return Department | |
| * @author Midhun Shaji | |
| */ | |
| public function searchDepartment( $request) | |
| { | |
| $request = $this->realEscapeObject($request); | |
| $where = []; | |
| $request->id ? $where [] = "deptID = '$request->id'":false; | |
| $request->description ? $where [] = "departmentDesc = '$request->description'":false; | |
| $request->name ? $where [] = "deptName = '$request->name'":false; | |
| $request->checkIsActive ? $where [] = " deptShow = '".( $request->isActive ? '1' : '0' )."'" : false; | |
| $request->programIds ? $where [] = " deptID IN (SELECT department_id FROM program_department_relation WHERE program_id IN ('".implode("','",$request->programIds)."'))" : null; | |
| $request->ids ? $where [] = " deptID IN ('".implode("','",$request->ids)."')" : null; | |
| $request->departmentId ? $where [] = "deptID = '$request->departmentId'" : false; | |
| $request->schoolId ? $where [] = "department.school_id = '$request->schoolId'" : false; | |
| $order = ""; | |
| if($request->filter){ | |
| $order = "order by deptName"; | |
| } | |
| try { | |
| $sql = | |
| "SELECT | |
| `deptID` AS `id`,`deptName` AS name,`deptName` AS text,'DEPARTMENT' AS type,'[]' AS `groups_ids`,JSON_OBJECT('name',`deptName`,'description',`departmentDesc`,'isActive',if(`deptShow` = 1,cast(TRUE AS JSON),cast(FALSE AS JSON)),'admissionShow',if(admissionShow = 1,cast(TRUE AS JSON),cast(FALSE AS JSON)),'departmentSpecialization',`departmentSpecialization`) AS `properties`,1 AS `created_by`,utc_timestamp() AS `created_date`,1 AS `updated_by`,utc_timestamp() AS `updated_date` | |
| FROM `department` | |
| ".(!empty($where) ? " WHERE ".implode(' AND ', $where):" ")." $order;"; | |
| $departments = $this->executeQueryForList($sql); | |
| foreach ($departments as $key => $department) { | |
| $department->groups_ids = []; | |
| $department->properties = json_decode($department->properties); | |
| $department->name = htmlspecialchars_decode($department->name); | |
| $department->text = htmlspecialchars_decode($department->text); | |
| $department->properties->name = htmlspecialchars_decode($department->properties->name); | |
| $department->properties->description = htmlspecialchars_decode($department->properties->description); | |
| } | |
| return $departments; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| } |