Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 34 |
CRAP | |
0.00% |
0 / 961 |
| StudentLeaveService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 34 |
34040.00 | |
0.00% |
0 / 961 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 3 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| getStudentLeaves | |
0.00% |
0 / 1 |
600.00 | |
0.00% |
0 / 136 |
|||
| getLeaveTypes | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 15 |
|||
| getAssignedLeaveTypes | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| createStudentLeaveApplication | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getStudentLeaveApplyDetailsByDate | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getStudentLeavesByBatch | |
0.00% |
0 / 1 |
342.00 | |
0.00% |
0 / 125 |
|||
| getStudentLeaveApplicationById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 60 |
|||
| checkIfLeaveBelongsToStudent | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 14 |
|||
| updateStudentLeaveApplication | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 18 |
|||
| getAllParentLeaveTypes | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getStudentLeavesByParentLeavetypeId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getRulesAssignedToLeaveType | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteRuleAssignedToLeave | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| assignRulesToLeave | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| getAllAssignedStudentLeaves | |
0.00% |
0 / 1 |
462.00 | |
0.00% |
0 / 131 |
|||
| getAllOtherLeavesWithoutGivenId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 14 |
|||
| insertExcludedSubjectCategoryForStudentLeave | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 22 |
|||
| deleteExcludedSubjectCategoryFromStudentLeaveByID | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getExcludedSubjectByLeaveTypeId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getLeaveTypeById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| canApplyForLeaveByLeaveTypeIdSubjectCatId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| createStudentLeaveType | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 16 |
|||
| getStudentsDlLeaveReport | |
0.00% |
0 / 1 |
342.00 | |
0.00% |
0 / 47 |
|||
| getSubjectWiseStudentDutyLeaveReport | |
0.00% |
0 / 1 |
342.00 | |
0.00% |
0 / 47 |
|||
| getStudentsDlLeaveTotalReport | |
0.00% |
0 / 1 |
272.00 | |
0.00% |
0 / 41 |
|||
| getAllApprovedStaff | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| getLeavesAppliedByStudent | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 30 |
|||
| getApprovedLeaves | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 31 |
|||
| getParentAndChildLeaveTypes | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 19 |
|||
| getCurrentLeaveTypeDetails | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| deleteWorkFlow | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 26 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\request\GetStudentLeaveRequest; | |
| use com\linways\core\ams\professional\response\GetStudentLeaveResponse; | |
| use com\linways\core\ams\professional\mapper\StudentLeaveServiceMapper; | |
| use com\linways\core\ams\professional\dto\LeaveType; | |
| use com\linways\core\ams\professional\dto\StudentLeaveApplication; | |
| use com\linways\core\ams\professional\util\CommonUtil; | |
| use Respect\Validation\Rules\FloatVal; | |
| use stdClass; | |
| class StudentLeaveService extends BaseService | |
| { | |
| private $mapper = []; | |
| // /Condition 1 - Presence of a static member variable | |
| private static $_instance = null; | |
| // /Condition 2 - Locked down the constructor | |
| private function __construct() | |
| { | |
| $this->mapper = StudentLeaveServiceMapper::getInstance()->getMapper(); | |
| } | |
| // Prevent any oustide instantiation of this class | |
| // /Condition 3 - Prevent any object or instance of that class to be cloned | |
| private function __clone() | |
| { | |
| } | |
| // Prevent any copy of this object | |
| // /Condition 4 - 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; | |
| } | |
| /** | |
| * Method for search student Leaves. | |
| * For search params see GetStudentLeaveRequest | |
| * @param GetStudentLeaveRequest $getStudentLeaveRequest | |
| * @return GetStudentLeaveResponse | |
| */ | |
| public function getStudentLeaves($getStudentLeaveRequest) | |
| { | |
| $response = new GetStudentLeaveResponse(); | |
| $criteria = " WHERE slp.id IS NOT NULL "; | |
| $sortBy = null; | |
| $sql = "SELECT | |
| slp.id, | |
| slt.id as leaveTypeId, | |
| slt.name AS leaveTypeName, | |
| slt.code AS leaveTypeCode, | |
| slt.code AS leaveTypeDescription, | |
| slp.description, | |
| slp.reason, | |
| slp.applied_date as appliedDate, | |
| slp.start_date AS startDate, | |
| slp.end_date as endDate, | |
| slp.session_key as sessionKey, | |
| slp.status , | |
| slp.assigned_to as assignedToStaffId, | |
| group_concat(sa_assignd.staffName SEPARATOR ', ') as assignedToStaffName, | |
| sa_recommnd.staffName as recommendedStaffName, | |
| sa_recommnd.staffID as recommendedStaffId, | |
| sa_approved.staffName as approvedStaffName, | |
| sa_approved.staffID as approvedStaffId, | |
| sa_rejected.staffName as rejectedStaffName, | |
| sa_rejected.staffID as rejectedStaffId, | |
| sa.studentID as studenId, | |
| sa.studentName, | |
| sa.rollNo, | |
| sa.deptID | |
| from student_leave_application slp | |
| inner join student_leave_type slt on slp.leave_type_id = slt.id and slt.isActive=1 | |
| inner join studentaccount sa on sa.studentID = slp.student_id | |
| left join staffaccounts sa_assignd on FIND_IN_SET( sa_assignd.staffID, slp.assigned_to) > 0 | |
| left join staffaccounts sa_recommnd on sa_recommnd.staffID = slp.recommended_by | |
| left join staffaccounts sa_approved on sa_approved.staffID = slp.approved_by | |
| left join staffaccounts sa_rejected on sa_rejected.staffID = slp.rejected_by"; | |
| $sqlCount = "SELECT | |
| count(slp.id) as totalRecords | |
| from student_leave_application slp | |
| inner join student_leave_type slt on slp.leave_type_id = slt.id and slt.isActive=1 | |
| inner join studentaccount sa on sa.studentID = slp.student_id | |
| left join staffaccounts sa_assignd on sa_assignd.staffID = slp.assigned_to | |
| left join staffaccounts sa_recommnd on sa_recommnd.staffID = slp.recommended_by | |
| left join staffaccounts sa_approved on sa_approved.staffID = slp.approved_by | |
| left join staffaccounts sa_rejected on sa_rejected.staffID = slp.rejected_by"; | |
| if (!empty($getStudentLeaveRequest)) { | |
| //leaveTypeCode | |
| if (!empty($getStudentLeaveRequest->leaveTypeCode)) { | |
| $getStudentLeaveRequest->leaveTypeCode = $this->realEscapeString($getStudentLeaveRequest->leaveTypeCode); | |
| $criteria .= " AND slt.code='$getStudentLeaveRequest->leaveTypeCode'"; | |
| } | |
| //leaveTypeName | |
| if (!empty($getStudentLeaveRequest->leaveTypeName)) { | |
| $getStudentLeaveRequest->leaveTypeName = $this->realEscapeString($getStudentLeaveRequest->leaveTypeName); | |
| $criteria .= " AND slt.name='$getStudentLeaveRequest->leaveTypeName'"; | |
| } | |
| //assignedToStaffName | |
| if (!empty($getStudentLeaveRequest->assignedToStaffName) && !empty($getStudentLeaveRequest->deptId)) { | |
| $getStudentLeaveRequest->assignedToStaffId = $this->realEscapeString($getStudentLeaveRequest->assignedToStaffId); | |
| $getStudentLeaveRequest->deptId = $this->realEscapeString($getStudentLeaveRequest->deptId); | |
| $criteria .= " AND ( FIND_IN_SET( '$getStudentLeaveRequest->assignedToStaffId', slp.assigned_to) > 0 AND sa.deptID = '$getStudentLeaveRequest->deptId')"; | |
| if (!empty($getStudentLeaveRequest->batchId)) { | |
| $getStudentLeaveRequest->batchId = $this->realEscapeString($getStudentLeaveRequest->batchId); | |
| $criteria .= " OR sa.batchID in ($getStudentLeaveRequest->batchId)"; | |
| } | |
| } // assignedToStaffId | |
| else if (!empty($getStudentLeaveRequest->assignedToStaffId) && !empty($getStudentLeaveRequest->deptId)) { | |
| $getStudentLeaveRequest->assignedToStaffId = $this->realEscapeString($getStudentLeaveRequest->assignedToStaffId); | |
| $getStudentLeaveRequest->deptId = $this->realEscapeString($getStudentLeaveRequest->deptId); | |
| $criteria .= " AND ( FIND_IN_SET( '$getStudentLeaveRequest->assignedToStaffId', slp.assigned_to) > 0 OR sa.deptID = '$getStudentLeaveRequest->deptId')"; | |
| } elseif (!empty($getStudentLeaveRequest->assignedToStaffName) && !empty($getStudentLeaveRequest->batchId)) { | |
| $getStudentLeaveRequest->assignedToStaffId = $this->realEscapeString($getStudentLeaveRequest->assignedToStaffId); | |
| $getStudentLeaveRequest->batchId = $this->realEscapeString($getStudentLeaveRequest->batchId); | |
| $criteria .= " AND ( FIND_IN_SET( '$getStudentLeaveRequest->assignedToStaffId', slp.assigned_to) > 0 AND sa.batchId in ($getStudentLeaveRequest->batchId))"; | |
| } elseif (!empty($getStudentLeaveRequest->assignedToStaffId) && !empty($getStudentLeaveRequest->batchId)) { | |
| $getStudentLeaveRequest->assignedToStaffId = $this->realEscapeString($getStudentLeaveRequest->assignedToStaffId); | |
| $getStudentLeaveRequest->batchId = $this->realEscapeString($getStudentLeaveRequest->batchId); | |
| $criteria .= " AND ( FIND_IN_SET( '$getStudentLeaveRequest->assignedToStaffId', slp.assigned_to) > 0 OR sa.batchId in ($getStudentLeaveRequest->batchId))"; | |
| } else { | |
| if (!empty($getStudentLeaveRequest->assignedToStaffId)) { | |
| $getStudentLeaveRequest->assignedToStaffId = $this->realEscapeString($getStudentLeaveRequest->assignedToStaffId); | |
| // $criteria .= " AND slp.assigned_to='$getStudentLeaveRequest->assignedToStaffId'"; | |
| $criteria .= " AND FIND_IN_SET( '$getStudentLeaveRequest->assignedToStaffId', slp.assigned_to) > 0"; | |
| } | |
| if (!empty($getStudentLeaveRequest->deptId)) { | |
| $getStudentLeaveRequest->deptId = $this->realEscapeString($getStudentLeaveRequest->deptId); | |
| $criteria .= " AND sa.deptID = '$getStudentLeaveRequest->deptId'"; | |
| } | |
| //batchId | |
| if (!empty($getStudentLeaveRequest->batchId)) { | |
| $getStudentLeaveRequest->batchId = $this->realEscapeString($getStudentLeaveRequest->batchId); | |
| $criteria .= " AND sa.batchID in ($getStudentLeaveRequest->batchId)"; | |
| } | |
| } | |
| // if(empty($getStudentLeaveRequest->assignedToStaffName) && !empty($getStudentLeaveRequest->deptId)) | |
| // { | |
| // $getStudentLeaveRequest->deptId = $this->realEscapeString($getStudentLeaveRequest->deptId); | |
| // $criteria.= " AND sa.deptID = '$getStudentLeaveRequest->deptId'"; | |
| // } | |
| // if(empty($getStudentLeaveRequest->assignedToStaffName) && !empty($getStudentLeaveRequest->batchId)) | |
| // { | |
| // $getStudentLeaveRequest->batchId = $this->realEscapeString($getStudentLeaveRequest->batchId); | |
| // $criteria.= " AND sa.batchId in ($getStudentLeaveRequest->batchId)"; | |
| // } | |
| //rollNo | |
| if (!empty($getStudentLeaveRequest->rollNo)) { | |
| $getStudentLeaveRequest->rollNo = $this->realEscapeString($getStudentLeaveRequest->rollNo); | |
| $criteria .= " AND sa.rollNo='$getStudentLeaveRequest->rollNo'"; | |
| } | |
| //studentName | |
| if (!empty($getStudentLeaveRequest->studentName)) { | |
| $getStudentLeaveRequest->studentName = $this->realEscapeString($getStudentLeaveRequest->studentName); | |
| $criteria .= " AND sa.studentName like '%$getStudentLeaveRequest->studentName%'"; | |
| } | |
| //status | |
| if (!empty($getStudentLeaveRequest->status)) { | |
| $getStudentLeaveRequest->status = $this->realEscapeString($getStudentLeaveRequest->status); | |
| $criteria .= " AND slp.status = '$getStudentLeaveRequest->status'"; | |
| } | |
| //startDate | |
| if (!empty($getStudentLeaveRequest->startDate)) { | |
| $getStudentLeaveRequest->startDate = $this->realEscapeString($getStudentLeaveRequest->startDate); | |
| $criteria .= " AND (slp.start_date >= '$getStudentLeaveRequest->startDate' OR | |
| ('$getStudentLeaveRequest->startDate' BETWEEN slp.start_date and slp.end_date) ) "; | |
| } | |
| //endDate | |
| if (!empty($getStudentLeaveRequest->endDate)) { | |
| $getStudentLeaveRequest->endDate = $this->realEscapeString($getStudentLeaveRequest->endDate); | |
| $criteria .= " AND (slp.end_date <= '$getStudentLeaveRequest->endDate' OR | |
| ('$getStudentLeaveRequest->endDate' BETWEEN slp.start_date and slp.end_date) ) "; | |
| } | |
| //Add criteria | |
| $sqlCount .= $criteria; | |
| $sql .= $criteria; | |
| $sql .= " GROUP BY slp.id"; | |
| //Add sort order | |
| if (!empty($getStudentLeaveRequest->sortBy)) { | |
| $getStudentLeaveRequest->sortBy = $this->realEscapeString($getStudentLeaveRequest->sortBy); | |
| $getStudentLeaveRequest->sortOrder = $this->realEscapeString($getStudentLeaveRequest->sortOrder); | |
| $sortBy = " ORDER BY $getStudentLeaveRequest->sortBy $getStudentLeaveRequest->sortOrder "; | |
| } else { | |
| //default | |
| $sortBy = " ORDER BY appliedDate DESC "; | |
| } | |
| $sql .= $sortBy; | |
| //Add Pagination | |
| if (!$getStudentLeaveRequest->isExport) { | |
| $sql .= " LIMIT $getStudentLeaveRequest->startIndex,$getStudentLeaveRequest->endIndex "; | |
| } | |
| } else { | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST, "Invalid Request"); | |
| } | |
| try { | |
| $response->totalRecords = $this->executeQueryForObject($sqlCount)->totalRecords; | |
| $response->studentLeaves = $this->executeQueryForList($sql, $this->mapper[StudentLeaveServiceMapper::GET_STUDENT_LEAVES]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * Get Available Leave Types which is configured in admin side | |
| * @return array LeaveType | |
| */ | |
| public function getLeaveTypes($isParentLeaveTypeOnly = false) | |
| { | |
| $leaveTypes = []; | |
| if($isParentLeaveTypeOnly){ | |
| $sql = "SELECT * FROM student_leave_type where isActive=1 AND (parentId IS NULL OR parentId = 0) order by name;"; | |
| } | |
| else{ | |
| $sql = "SELECT * FROM student_leave_type where isActive=1 order by name;"; | |
| } | |
| try { | |
| $leaveTypes = $this->executeQueryForList($sql, $this->mapper[StudentLeaveServiceMapper::GET_LEAVE_TYPES]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $leaveTypes; | |
| } | |
| /** | |
| * Get Available Leave Types which is configured in admin side | |
| * @return array LeaveType | |
| */ | |
| public function getAssignedLeaveTypes($isParentLeaveTypeOnly = false) | |
| { | |
| $leaveTypes = []; | |
| $sql = "SELECT DISTINCT slt.* FROM student_leave_type slt | |
| INNER JOIN student_leave_application sla ON sla.leave_type_Id = slt.id | |
| INNER JOIN workflow_instance wi ON wi.id = sla.workflowInstanceId | |
| INNER JOIN workflow_transition_assignee wta ON wi.id = wta.workflow_instance_id | |
| WHERE wta.user_id = $GLOBALS[userId] AND slt.isActive=1 order by name"; | |
| try { | |
| $leaveTypes = $this->executeQueryForList($sql, $this->mapper[StudentLeaveServiceMapper::GET_LEAVE_TYPES]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $leaveTypes; | |
| } | |
| /** | |
| * create student leave application | |
| * @param StudentLeaveApplication $studentLeaveApplication | |
| * @return \com\linways\base\dto\MySqlResult | |
| * @throws ProfessionalException | |
| */ | |
| public function createStudentLeaveApplication($studentLeaveApplication) | |
| { | |
| $studentLeaveApplication = $this->realEscapeObject($studentLeaveApplication); | |
| $sql = "INSERT student_leave_application (student_Id, leave_type_Id, session_key, start_date, end_date, applied_date, noof_days, description, reason, approved_by, status, created_by, created_date, updated_by, updated_date, assigned_to, recommended_by, categoryId, userType, workflowInstanceId) VALUE ($studentLeaveApplication->studenId, $studentLeaveApplication->leaveType, '$studentLeaveApplication->sessionKey', '$studentLeaveApplication->startDate', '$studentLeaveApplication->endDate', '$studentLeaveApplication->appliedDate', $studentLeaveApplication->noofDays, '$studentLeaveApplication->description', '$studentLeaveApplication->reason', '$studentLeaveApplication->approvedBy', '$studentLeaveApplication->status', $studentLeaveApplication->createdBy, UTC_TIMESTAMP(), $studentLeaveApplication->updatedBy, UTC_TIMESTAMP(), '$studentLeaveApplication->assignedTo', '$studentLeaveApplication->recommendedBy', '$studentLeaveApplication->categoryId', '$studentLeaveApplication->userType', '$studentLeaveApplication->workflowInstanceId')"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get student leave apply details | |
| * @param int $studentId | |
| * @param string $startDate | |
| * @param string $endDate | |
| * @throws ProfessionalException | |
| */ | |
| public function getStudentLeaveApplyDetailsByDate($studentId, $startDate, $endDate) | |
| { | |
| $studentId = $this->realEscapeString($studentId); | |
| $startDate = $this->realEscapeString($startDate); | |
| $endDate = $this->realEscapeString($endDate); | |
| $sql = "SELECT DISTINCT(start_date),session_key,end_date FROM student_leave_application WHERE ((start_date <='$startDate' AND end_date>='$startDate') OR (start_date <='$endDate' AND end_date>='$endDate') OR ('$startDate'<=start_date AND '$endDate'>=start_date) OR ('$startDate'<=end_date AND '$endDate'>=end_date)) AND student_Id = '$studentId' AND status !='REJECTED'"; | |
| try { | |
| $leaveDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $leaveDetails; | |
| } | |
| /** | |
| * Method for search student Leaves. | |
| * For search params see GetStudentLeaveRequest | |
| * @param GetStudentLeaveRequest $getStudentLeaveRequest | |
| * @return GetStudentLeaveResponse | |
| */ | |
| public function getStudentLeavesByBatch($getStudentLeaveRequest) | |
| { | |
| $response = new GetStudentLeaveResponse(); | |
| $criteria = " WHERE slp.id IS NOT NULL "; | |
| $sortBy = null; | |
| $sql = "SELECT | |
| slp.id, | |
| slt.id as leaveTypeId, | |
| slt.name AS leaveTypeName, | |
| slt.code AS leaveTypeCode, | |
| slt.code AS leaveTypeDescription, | |
| slp.description, | |
| slp.reason, | |
| slp.applied_date as appliedDate, | |
| slp.start_date AS startDate, | |
| slp.end_date as endDate, | |
| slp.session_key as sessionKey, | |
| slp.status , | |
| slp.assigned_to as assignedToStaffId, | |
| group_concat(sa_assignd.staffName SEPARATOR ', ') as assignedToStaffName, | |
| sa_recommnd.staffName as recommendedStaffName, | |
| sa_recommnd.staffID as recommendedStaffId, | |
| sa_approved.staffName as approvedStaffName, | |
| sa_approved.staffID as approvedStaffId, | |
| sa_rejected.staffName as rejectedStaffName, | |
| sa_rejected.staffID as rejectedStaffId, | |
| sa.studentID as studenId, | |
| sa.studentName, | |
| sa.rollNo, | |
| sa.deptID | |
| from student_leave_application slp | |
| inner join student_leave_type slt on slp.leave_type_id = slt.id and slt.isActive=1 | |
| inner join studentaccount sa on sa.studentID = slp.student_id | |
| left join staffaccounts sa_assignd on FIND_IN_SET( sa_assignd.staffID, slp.assigned_to) > 0 | |
| left join staffaccounts sa_recommnd on sa_recommnd.staffID = slp.recommended_by | |
| left join staffaccounts sa_approved on sa_approved.staffID = slp.approved_by | |
| left join staffaccounts sa_rejected on sa_rejected.staffID = slp.rejected_by"; | |
| $sqlCount = "SELECT | |
| count(slp.id) as totalRecords | |
| from student_leave_application slp | |
| inner join student_leave_type slt on slp.leave_type_id = slt.id and slt.isActive=1 | |
| inner join studentaccount sa on sa.studentID = slp.student_id | |
| left join staffaccounts sa_assignd on sa_assignd.staffID = slp.assigned_to | |
| left join staffaccounts sa_recommnd on sa_recommnd.staffID = slp.recommended_by | |
| left join staffaccounts sa_approved on sa_approved.staffID = slp.approved_by | |
| left join staffaccounts sa_rejected on sa_rejected.staffID = slp.rejected_by"; | |
| if (!empty($getStudentLeaveRequest)) { | |
| //leaveTypeCode | |
| if (!empty($getStudentLeaveRequest->leaveTypeCode)) { | |
| $getStudentLeaveRequest->leaveTypeCode = $this->realEscapeString($getStudentLeaveRequest->leaveTypeCode); | |
| $criteria .= " AND slt.code='$getStudentLeaveRequest->leaveTypeCode'"; | |
| } | |
| //leaveTypeName | |
| if (!empty($getStudentLeaveRequest->leaveTypeName)) { | |
| $getStudentLeaveRequest->leaveTypeName = $this->realEscapeString($getStudentLeaveRequest->leaveTypeName); | |
| $criteria .= " AND slt.name='$getStudentLeaveRequest->leaveTypeName'"; | |
| } | |
| //assignedToStaffName | |
| // if(!empty($getStudentLeaveRequest->assignedToStaffName)) | |
| // { | |
| // $getStudentLeaveRequest->assignedToStaffName = $this->realEscapeString($getStudentLeaveRequest->assignedToStaffName); | |
| // $criteria .= " AND sa_assignd.staffName like '%$getStudentLeaveRequest->assignedToStaffName%'"; | |
| // } | |
| //assignedToStaffId | |
| if (!empty($getStudentLeaveRequest->assignedToStaffId)) { | |
| $getStudentLeaveRequest->assignedToStaffId = $this->realEscapeString($getStudentLeaveRequest->assignedToStaffId); | |
| // $criteria .= " AND slp.assigned_to='$getStudentLeaveRequest->assignedToStaffId'"; | |
| $criteria .= " AND FIND_IN_SET( '$getStudentLeaveRequest->assignedToStaffId', slp.assigned_to) > 0"; | |
| } | |
| //rollNo | |
| if (!empty($getStudentLeaveRequest->rollNo)) { | |
| $getStudentLeaveRequest->rollNo = $this->realEscapeString($getStudentLeaveRequest->rollNo); | |
| $criteria .= " AND sa.rollNo='$getStudentLeaveRequest->rollNo'"; | |
| } | |
| //studentName | |
| if (!empty($getStudentLeaveRequest->studentName)) { | |
| $getStudentLeaveRequest->studentName = $this->realEscapeString($getStudentLeaveRequest->studentName); | |
| $criteria .= " AND sa.studentName like '%$getStudentLeaveRequest->studentName%'"; | |
| } | |
| //batchId | |
| if (!empty($getStudentLeaveRequest->batchId)) { | |
| $getStudentLeaveRequest->batchId = $this->realEscapeString($getStudentLeaveRequest->batchId); | |
| $criteria .= " AND sa.batchID = $getStudentLeaveRequest->batchId"; | |
| } | |
| //status | |
| if (!empty($getStudentLeaveRequest->status)) { | |
| $getStudentLeaveRequest->status = $this->realEscapeString($getStudentLeaveRequest->status); | |
| $criteria .= " AND slp.status = '$getStudentLeaveRequest->status'"; | |
| } | |
| if($getStudentLeaveRequest->filterWithLeaveAppliedDate) | |
| { | |
| //Applied date : startDate | |
| if (!empty($getStudentLeaveRequest->startDate)) { | |
| $startDate = date("Y-m-d", strtotime($getStudentLeaveRequest->startDate)); | |
| $criteria .= " AND sla.applied_date >= '$startDate' "; | |
| } | |
| //Applied date : endDate | |
| if (!empty($getStudentLeaveRequest->endDate)) { | |
| $endDate = CommonUtil::addOneDay(date("Y-m-d", strtotime($getStudentLeaveRequest->endDate))); | |
| $criteria .= " AND sla.applied_date <= '$endDate' "; | |
| } | |
| } | |
| else | |
| { | |
| //startDate | |
| if (!empty($getStudentLeaveRequest->startDate)) { | |
| $startDate = date("Y-m-d", strtotime($this->realEscapeString($getStudentLeaveRequest->startDate))); | |
| $criteria .= " AND (slp.start_date >= '$startDate' OR ('$startDate' BETWEEN slp.start_date and slp.end_date) ) "; | |
| } | |
| //endDate | |
| if (!empty($getStudentLeaveRequest->endDate)) { | |
| $endDate = date("Y-m-d", strtotime($this->realEscapeString($getStudentLeaveRequest->endDate))); | |
| $criteria .= " AND (slp.end_date <= '$endDate' OR ('$endDate' BETWEEN slp.start_date and slp.end_date) ) "; | |
| } | |
| } | |
| if (!empty($getStudentLeaveRequest->deptId)) { | |
| $getStudentLeaveRequest->deptId = $this->realEscapeString($getStudentLeaveRequest->deptId); | |
| $criteria .= " AND sa.deptID = $getStudentLeaveRequest->deptId"; | |
| } | |
| //Add criteria | |
| $sqlCount .= $criteria; | |
| $sql .= $criteria; | |
| $sql .= " GROUP BY slp.id"; | |
| //Add sort order | |
| if (!empty($getStudentLeaveRequest->sortBy)) { | |
| $getStudentLeaveRequest->sortBy = $this->realEscapeString($getStudentLeaveRequest->sortBy); | |
| $getStudentLeaveRequest->sortOrder = $this->realEscapeString($getStudentLeaveRequest->sortOrder); | |
| $sortBy = " ORDER BY $getStudentLeaveRequest->sortBy $getStudentLeaveRequest->sortOrder "; | |
| } else { | |
| //default | |
| $sortBy = " ORDER BY appliedDate DESC "; | |
| } | |
| $sql .= $sortBy; | |
| //Add Pagination | |
| if (!$getStudentLeaveRequest->isExport) { | |
| $sql .= " LIMIT $getStudentLeaveRequest->startIndex,$getStudentLeaveRequest->endIndex "; | |
| } | |
| } else { | |
| throw new ProfessionalException(ProfessionalException::INVALID_REQUEST, "Invalid Request"); | |
| } | |
| try { | |
| $response->totalRecords = $this->executeQueryForObject($sqlCount)->totalRecords; | |
| $response->studentLeaves = $this->executeQueryForList($sql, $this->mapper[StudentLeaveServiceMapper::GET_STUDENT_LEAVES]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * Get student leaves by id | |
| * @param int $id | |
| * @return unknown | |
| * @throws ProfessionalException | |
| */ | |
| public function getStudentLeaveApplicationById($id) | |
| { | |
| $id = $this->realEscapeString($id); | |
| $sql = "SELECT | |
| student_Id, | |
| leave_type_Id, | |
| session_key, | |
| start_date, | |
| end_date, | |
| applied_date, | |
| noof_days, | |
| sla.description, | |
| reason, | |
| approved_by, | |
| rejected_by, | |
| status, | |
| assigned_to, | |
| recommended_by, | |
| categoryId, | |
| userType, | |
| workflowInstanceId, | |
| studentName, | |
| sa.deptID, | |
| sa.batchID, | |
| batchName, | |
| semID, | |
| sla.dlrId as dlrId, | |
| lt.id, | |
| lt.code, | |
| lt.isDL, | |
| stf_rejected.staffID AS rejectedBy, | |
| stf_rejected.staffName AS rejectedStaffName, | |
| sla.updated_date, | |
| sla.created_date, | |
| dlr.dlrName as dutyLeaveRemarkName, | |
| dlr.dlrDescription as dutyLeaveRemarkDescription, | |
| lr.id as resourceId, | |
| lr.path as resourcePath, | |
| lr.backend_type as storedType, | |
| lr.storage_object as storedObject | |
| FROM | |
| student_leave_application sla | |
| INNER JOIN | |
| studentaccount sa ON sla.student_id = sa.studentID | |
| INNER JOIN | |
| batches ba ON ba.batchID = sa.batchID | |
| INNER JOIN | |
| student_leave_type lt ON lt.id = sla.leave_type_Id and lt.isActive=1 | |
| LEFT JOIN | |
| staffaccounts stf_rejected ON stf_rejected.staffID = sla.rejected_by | |
| LEFT JOIN | |
| duty_leave_remarks dlr ON dlr.dlrId = sla.dlrId | |
| LEFT JOIN lin_resource lr ON lr.id = sla.lin_resources_id | |
| WHERE sla.id = $id"; | |
| try { | |
| $leaveDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $leaveDetails; | |
| } | |
| /** | |
| * A service to check whether a leave is applied by a student or not | |
| * | |
| * @param int $leaveId | |
| * @param int $studentId | |
| * @return void | |
| */ | |
| public function checkIfLeaveBelongsToStudent($leaveId, $studentId) | |
| { | |
| $leaveId = $this->realEscapeString($leaveId); | |
| $studentId = $this->realEscapeString($studentId); | |
| $sql = "SELECT id FROM student_leave_application WHERE student_Id = '$studentId' AND id = '$leaveId'"; | |
| try { | |
| $leaveDetails = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if (empty($leaveDetails) && empty($leaveDetails->id)) { | |
| return false; | |
| } | |
| return true; | |
| } | |
| /** | |
| * Update student leave application | |
| * @param int $staffId | |
| * @param string $leaveIds | |
| * @param string $status | |
| * @return \com\linways\base\dto\MySqlResult | |
| * @throws ProfessionalException | |
| */ | |
| public function updateStudentLeaveApplication($staffId, $leaveIds, $status,$rejectedRemarks = null) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $leaveId = $this->realEscapeString($leaveIds); | |
| $status = $this->realEscapeString($status); | |
| $recommend = ", recommended_by = IF(recommended_by = 0, " . $staffId . ", recommended_by )"; | |
| $sql_rejectLeave = $status == "REJECTED" ? "rejected_by = $staffId, " : "approved_by = " . $staffId . ", "; | |
| if($rejectedRemarks) | |
| { | |
| $remarkSql = ",rejectionReason = '$rejectedRemarks'"; | |
| } | |
| $sql = "UPDATE student_leave_application | |
| SET status = '$status', updated_by = " . $staffId . ", $sql_rejectLeave updated_date = UTC_DATE() $remarkSql WHERE id in ( $leaveIds)"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get all parent leave types | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllParentLeaveTypes() | |
| { | |
| $sql = "SELECT id, code, name, description, parentId, isDL FROM student_leave_type WHERE (parentId is null or parentId = 0) and isActive=1"; | |
| try { | |
| $studentLeaves = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentLeaves; | |
| } | |
| /** | |
| * Get leave types by parent leave type | |
| * @param int $parentId | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getStudentLeavesByParentLeavetypeId($parentId) | |
| { | |
| $parentId = $this->realEscapeString($parentId); | |
| $sql = "SELECT id, code, name, description, parentId, isDL FROM student_leave_type WHERE parentId = $parentId and isActive=1"; | |
| try { | |
| $studentLeaves = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $studentLeaves; | |
| } | |
| /** | |
| * Get rules assigned to leavetype | |
| * @param int $leaveTypeId | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getRulesAssignedToLeaveType($leaveTypeId) | |
| { | |
| $leaveTypeId = $this->realEscapeString($leaveTypeId); | |
| $sql = "select lr.id as leaveTypeRuleId, leaveTypeRuleId, leaveTypeId, code, name, description, parentId, isDL, ruleName, ruleDefinition from leaveTypeRule lr inner join student_leave_type slt on lr.leaveTypeId = slt.id and slt.isActive=1 inner join DLRules dlr on dlr.id = lr.leaveTypeRuleId where lr.leaveTypeId = $leaveTypeId"; | |
| try { | |
| $leaveRuleDetails = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $leaveRuleDetails; | |
| } | |
| /** | |
| * Delete rules assigned to leave | |
| * @param int $leaveTypeId | |
| * @return \com\linways\base\dto\MySqlResult | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteRuleAssignedToLeave($leaveTypeId) | |
| { | |
| $leaveTypeId = $this->realEscapeString($leaveTypeId); | |
| $sql = "DELETE FROM leaveTypeRule WHERE leaveTypeId = $leaveTypeId"; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Assign rules to leave | |
| * @param int $leaveTypeId | |
| * @param int $ruleIds | |
| * @return object|NULL|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function assignRulesToLeave($leaveTypeId, $ruleIds) | |
| { | |
| $leaveTypeId = $this->realEscapeString($leaveTypeId); | |
| $ruleIds = $this->realEscapeArray($ruleIds); | |
| foreach ($ruleIds as $ruleId) { | |
| $sql_values .= " ($leaveTypeId, $ruleId, '1', utc_timestamp(), '1', utc_timestamp()), "; | |
| } | |
| $sql_values = rtrim($sql_values, ", "); | |
| $sql = "INSERT INTO leaveTypeRule (leaveTypeId, leaveTypeRuleId, createdBy, createdDate, updatedBy, updatedDate) VALUES $sql_values"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get all assigned student leaves | |
| * @param GetStudentLeaveRequest $getStudentLeaveRequest | |
| * @return unknown | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllAssignedStudentLeaves(GetStudentLeaveRequest $getStudentLeaveRequest) | |
| { | |
| $getStudentLeaveRequest = $this->realEscapeObject($getStudentLeaveRequest); | |
| $sql = "SELECT | |
| @slNo:=@slNo+1 slNo, | |
| sla.id, | |
| slt.id as leaveTypeId, | |
| slt.name AS leaveTypeName, | |
| slt.code AS leaveTypeCode, | |
| slt.code AS leaveTypeDescription, | |
| sla.description, | |
| sla.reason, | |
| sla.rejectionReason, | |
| sla.applied_date as appliedDate, | |
| sla.start_date AS startDate, | |
| sla.end_date as endDate, | |
| sla.session_key as sessionKey, | |
| sla.status , | |
| sla.assigned_to as assignedToStaffId, | |
| sla.approved_by as approvedStaffId , | |
| sa.studentID as studenId, | |
| sa.studentName, | |
| sa.rollNo, | |
| sa.deptID, | |
| sla.workflowInstanceId, | |
| d.deptName, | |
| g.name as batchName , | |
| s.staffID as actionStaffID, | |
| s.staffName as actionBy | |
| FROM | |
| (SELECT @slNo:= 0) AS slNo, | |
| student_leave_application sla | |
| INNER JOIN | |
| studentaccount sa ON sa.studentID = sla.student_Id | |
| INNER JOIN | |
| student_leave_type slt ON slt.id = sla.leave_type_Id and slt.isActive=1 | |
| INNER JOIN | |
| student_program_account spa on spa.student_id=sa.studentID | |
| INNER JOIN | |
| `groups` g on g.id = spa.current_batch_id | |
| INNER JOIN | |
| program_department_relation pdr on pdr.program_id = spa.current_program_id | |
| INNER JOIN | |
| department d on d.deptID = pdr.department_id | |
| LEFT JOIN | |
| staffaccounts s on s.staffID = sla.updated_by | |
| WHERE | |
| sla.id is not null"; | |
| $sqlCount = "SELECT | |
| count(sla.id) as totalRecords | |
| FROM | |
| student_leave_application sla | |
| INNER JOIN | |
| studentaccount sa ON sa.studentID = sla.student_Id | |
| INNER JOIN | |
| student_leave_type slt ON slt.id = sla.leave_type_Id and slt.isActive=1 | |
| WHERE | |
| sla.id is not null"; | |
| $criteria =""; | |
| if (!empty($getStudentLeaveRequest->batchId)) { | |
| $criteria .= " AND sa.batchID = $getStudentLeaveRequest->batchId"; | |
| } | |
| if (!empty($getStudentLeaveRequest->deptId)) { | |
| $criteria .= " AND sa.deptID = $getStudentLeaveRequest->deptId"; | |
| } | |
| if (!empty($getStudentLeaveRequest->workflowInstanceId)) { | |
| $criteria .= " AND workflowInstanceId IN (" . implode(",", $getStudentLeaveRequest->workflowInstanceId) . ")"; | |
| } | |
| if (!empty($getStudentLeaveRequest->leaveTypeCode)) { | |
| $criteria .= " AND slt.code='$getStudentLeaveRequest->leaveTypeCode'"; | |
| } | |
| //leaveTypeName | |
| if (!empty($getStudentLeaveRequest->leaveTypeName)) { | |
| $criteria .= " AND slt.name='$getStudentLeaveRequest->leaveTypeName'"; | |
| } | |
| if (!empty($getStudentLeaveRequest->assignedToStaffId)) { | |
| $getStudentLeaveRequest->assignedToStaffId = $this->realEscapeString($getStudentLeaveRequest->assignedToStaffId); | |
| // $criteria .= " AND slp.assigned_to='$getStudentLeaveRequest->assignedToStaffId'"; | |
| $criteria .= " AND FIND_IN_SET( '$getStudentLeaveRequest->assignedToStaffId', sla.assigned_to) > 0"; | |
| } | |
| //number of choice | |
| if (!empty($getStudentLeaveRequest->rollNo && $getStudentLeaveRequest->numberChoice)) { | |
| $getStudentLeaveRequest->numberChoice = $getStudentLeaveRequest->numberChoice?$getStudentLeaveRequest->numberChoice:"rollNo"; | |
| $criteria .= " AND sa.".$getStudentLeaveRequest->numberChoice."='$getStudentLeaveRequest->rollNo'"; | |
| } | |
| //studentName | |
| if (!empty($getStudentLeaveRequest->studentName)) { | |
| $criteria .= " AND sa.studentName like '%$getStudentLeaveRequest->studentName%'"; | |
| } | |
| //status | |
| if (!empty($getStudentLeaveRequest->status)) { | |
| $criteria .= " AND sla.status = '$getStudentLeaveRequest->status'"; | |
| } | |
| if($getStudentLeaveRequest->filterWithLeaveAppliedDate) | |
| { | |
| //Applied date : startDate | |
| if (!empty($getStudentLeaveRequest->startDate)) { | |
| $criteria .= " AND sla.applied_date >= '$getStudentLeaveRequest->startDate' "; | |
| } | |
| //Applied date : endDate | |
| if (!empty($getStudentLeaveRequest->endDate)) { | |
| $endDate = CommonUtil::addOneDay($getStudentLeaveRequest->endDate); | |
| $criteria .= " AND sla.applied_date <= '$endDate' "; | |
| } | |
| } | |
| else | |
| { | |
| //startDate | |
| if (!empty($getStudentLeaveRequest->startDate)) { | |
| $criteria .= " AND (sla.start_date > '$getStudentLeaveRequest->startDate' OR | |
| ('$getStudentLeaveRequest->startDate' BETWEEN sla.start_date and sla.end_date) ) "; | |
| } | |
| //endDate | |
| if (!empty($getStudentLeaveRequest->endDate)) { | |
| $criteria .= " AND (sla.end_date < '$getStudentLeaveRequest->endDate' OR | |
| ('$getStudentLeaveRequest->endDate' BETWEEN sla.start_date and sla.end_date) ) "; | |
| } | |
| } | |
| $sql .= $criteria; | |
| $sqlCount .= $criteria; | |
| if (!empty($getStudentLeaveRequest->sortBy)) { | |
| $sortBy = " ORDER BY $getStudentLeaveRequest->sortBy $getStudentLeaveRequest->sortOrder "; | |
| } else { | |
| $sortBy = " ORDER BY appliedDate DESC "; | |
| } | |
| $sql .= $sortBy; | |
| // //Add Pagination | |
| if (!$getStudentLeaveRequest->isExport) { | |
| // $sql .= " LIMIT $getStudentLeaveRequest->startIndex,$getStudentLeaveRequest->endIndex "; | |
| if($getStudentLeaveRequest->startIndex) | |
| { | |
| $sql .= " LIMIT ".((int)$getStudentLeaveRequest->startIndex - 1).",50"; | |
| } | |
| } | |
| try { | |
| $response = new stdClass(); | |
| $response->totalRecords = $this->executeQueryForObject($sqlCount)->totalRecords; | |
| $response->studentLeaves = $this->executeQueryForList($sql, $this->mapper[StudentLeaveServiceMapper::GET_STUDENT_LEAVES]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $response; | |
| } | |
| /** | |
| * Get all leave types except the given id | |
| * @param int $id | |
| * @return object|array|\com\linways\base\util\$objectList[] | |
| * @throws ProfessionalException | |
| */ | |
| public function getAllOtherLeavesWithoutGivenId($id,$isParentLeaveTypeOnly = false) | |
| { | |
| $id = $this->realEscapeString($id); | |
| if($isParentLeaveTypeOnly){ | |
| $sql = "SELECT * FROM student_leave_type where id != " . $id . " and isActive=1 and (parentId IS NULL OR parentId = 0) order by name;"; | |
| } | |
| else{ | |
| $sql = "SELECT * FROM student_leave_type where id != " . $id . " and isActive=1 order by name;"; | |
| } | |
| try { | |
| return $this->executeQueryForList($sql, $this->mapper[StudentLeaveServiceMapper::GET_LEAVE_TYPES]); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Insert Included Subject-Category For StudentLeave | |
| * | |
| * @param [type] $subjectcatobject | |
| * @param [type] $leave_type_id | |
| * @param [type] $userID | |
| * @param [type] $attendancePer | |
| * @return void | |
| */ | |
| public function insertExcludedSubjectCategoryForStudentLeave($excludedSubjectcatobject, $last_id, $userID, $attendancePer) | |
| { | |
| $values = ""; | |
| $sql = "INSERT INTO v4_included_subject_category_for_student_leave (leave_type_id,subject_category_id,attendance_percentage, created_by, updated_by, created_date, updated_date) VALUES"; | |
| $excludedSubjectcatobject = $this->realEscapeObject($excludedSubjectcatobject); | |
| $last_id = $this->realEscapeString($last_id); | |
| $userID = $this->realEscapeString($userID); | |
| foreach ($excludedSubjectcatobject as $category) { | |
| if ($category) { | |
| $attendancePercentage = floatval($attendancePer[$category]) ? floatval($attendancePer[$category]) : "NULL"; | |
| $values .= "('$last_id', '$category'," . $attendancePercentage . ",'" . $userID . "', '" . $userID . "', now(), now()),"; | |
| } | |
| } | |
| if ($values) { | |
| $sql .= rtrim($values, ","); | |
| try { | |
| $this->executeQueryForObject($sql); | |
| return true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } | |
| public function deleteExcludedSubjectCategoryFromStudentLeaveByID($leaveTypeId) | |
| { | |
| $leaveTypeId = $this->realEscapeString($leaveTypeId); | |
| $sql = "DELETE FROM v4_included_subject_category_for_student_leave WHERE leave_type_id = $leaveTypeId;"; | |
| try { | |
| $this->executeQueryForObject($sql); | |
| return true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getExcludedSubjectByLeaveTypeId($leaveTypeId) | |
| { | |
| $leaveTypeId = $this->realEscapeString($leaveTypeId); | |
| $sql = "SELECT subject_category_id,attendance_percentage FROM v4_included_subject_category_for_student_leave WHERE leave_type_id = $leaveTypeId;"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get leave type by id | |
| * @param Integer $leaveTypeId | |
| * @return Object $leaveType | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getLeaveTypeById($leaveTypeId) | |
| { | |
| $leaveType = null; | |
| $leaveTypeId = (int)$this->realEscapeString($leaveTypeId); | |
| try { | |
| $sql = "SELECT code, name, description, parentId, isDL, check_attendance_marked AS checkAttendanceMarked FROM student_leave_type WHERE id = '$leaveTypeId' and isActive=1 "; | |
| $leaveType = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $leaveType; | |
| } | |
| /** | |
| * Get leave type by id | |
| * @param Integer $leaveTypeId | |
| * @param Integer $subjectCategoryId | |
| * @return Object $leaveType | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function canApplyForLeaveByLeaveTypeIdSubjectCatId($leaveTypeId, $subjectCategoryId) | |
| { | |
| $excludedSubjectCat = null; | |
| $leaveTypeId = (int)$this->realEscapeString($leaveTypeId); | |
| $subjectCategoryId = (int)$this->realEscapeString($subjectCategoryId); | |
| try { | |
| $sql = "SELECT category_id FROM v4_included_subject_category_for_student_leave WHERE leave_type_id = '$leaveTypeId' AND subject_category_id = '$subjectCategoryId' "; | |
| $excludedSubjectCat = $this->executeQueryForObject($sql); | |
| if (empty ($excludedSubjectCat)) { | |
| return true; | |
| } else { | |
| return false; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function createStudentLeaveType($leaveType) | |
| { | |
| $properties = !empty($leaveType->properties) ? "'" . json_encode($leaveType->properties) . "'" : "{}"; | |
| $sql = "INSERT INTO student_leave_type | |
| (code,name, description, parentId, isDL,check_attendance_marked,createdBy,createdDate,updatedBy,updatedDate,properties) | |
| VALUES ('" . $leaveType->typeCode . "','" . $leaveType->typeName . "', '" . $leaveType->typeDesc . "', '" . $leaveType->parentId . "', '" . $leaveType->isDl . "','" . $leaveType->checkAttendanceMarked . "','" . $leaveType->createdUser . "',utc_timestamp(),'" . $leaveType->updatedUser . "',utc_timestamp(),$properties)"; | |
| try { | |
| $leaveTypeID = $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if ($leaveTypeID) { | |
| return $leaveTypeID; | |
| } else { | |
| return false; | |
| } | |
| } | |
| /** | |
| * Get student(s) DL leave reports | |
| * @param Object $request | |
| * @return Object|array $AttendanceDetails | |
| * @throws ProfessionalException | |
| * @author Nandu | |
| */ | |
| public function getStudentsDlLeaveReport($request) | |
| { | |
| $cond = ""; | |
| $request = $this->realEscapeObject($request); | |
| $cond .= $request->studentID ? " AND att.studentID = '$request->studentID' " : ""; | |
| $cond .= $request->semID ? " AND att.semID = '$request->semID' " : ""; | |
| $cond .= $request->batchID ? " AND att.batchID = '$request->batchID' " : ""; | |
| $cond .= !empty($request->leaveTypeId) ? " AND sla.leave_type_Id IN (" . implode($request->leaveTypeId, ',') . ") " : ""; | |
| $cond .= !empty($request->subjectID) ? " AND s.subjectID IN (" . implode($request->subjectID, ',') . ") " : ""; | |
| $cond .= $request->staffId ? " AND sla.approved_by = '$request->staffId' " : ""; | |
| // $cond .= $request->inDate?" AND s.subjectID = '$request->fromDate' ":""; | |
| if ($request->fromDate && !$request->toDate && !$request->inDate) { | |
| $cond .= " AND tt.attendanceDate > '$request->fromDate' "; | |
| } elseif (!$request->fromDate && $request->toDate && !$request->inDate) { | |
| $cond .= " AND tt.attendanceDate < '$request->toDate' "; | |
| } elseif ($request->fromDate && $request->toDate && !$request->inDate) { | |
| $cond .= " AND att.attendanceDate BETWEEN '$request->fromDate' and '$request->toDate' "; | |
| } | |
| try { | |
| if ($request) { | |
| $sql = "SELECT std.studentID, std.studentName, std.regNo, s.subjectID, s.subjectName, s.subjectDesc, | |
| sg.id subjectGroupId, sg.name as subjectGroupName,sg.priority as subjectGroupOrder, | |
| IF(slt.id is null, 0,slt.id ) as student_leave_type_id, slt.code as student_leave_type_code, | |
| IF(sla.approved_by is null, 0,sla.approved_by) as approved_by , sa.staffName as approvedStaffName, | |
| (COUNT(CASE WHEN att.isAbsent = 0 OR att.isAbsent = 2 THEN 1 END)) AS atthour, | |
| COUNT(att.studentID) AS totalhour, | |
| (COUNT(CASE WHEN sla.session_key IS NOT NULL THEN 1 END)) AS numberOfLeaveHour, | |
| (COUNT(CASE WHEN att.isAbsent = 0 OR att.isAbsent = 2 THEN 1 END) / COUNT(att.studentID)) * 100 AS attendancePer, | |
| (COUNT(CASE WHEN sla.session_key IS NOT NULL THEN 1 END) / COUNT(att.studentID)) * 100 AS leavePer | |
| FROM attendance att | |
| INNER JOIN studentaccount std ON att.studentID = std.studentID | |
| INNER JOIN batches bat ON bat.batchID = std.batchID | |
| INNER JOIN sbs_relation sbs ON sbs.sbsID = att.sbsID | |
| INNER JOIN subjects s ON s.subjectID = sbs.subjectID | |
| LEFT JOIN subjectGroups_subjects sgs ON sgs.subjects_id=s.subjectID AND sgs.batches_id = bat.batchID | |
| AND sgs.semesters_id = att.semID | |
| LEFT JOIN subjectGroups sg ON sg.id = sgs.subjectGroups_id | |
| LEFT JOIN student_leave_application sla ON att.studentID = sla.student_Id | |
| AND att.attendanceDate BETWEEN sla.start_date AND sla.end_date | |
| AND FIND_IN_SET(att.hour, sla.session_key) AND sla.status='APPROVED' | |
| LEFT JOIN student_leave_type slt ON slt.id = sla.leave_type_Id AND slt.isDL = 1 and slt.isActive=1 | |
| LEFT JOIN staffaccounts sa ON sa.staffID=sla.approved_by | |
| WHERE 1=1 $cond GROUP BY std.studentID, s.subjectID, slt.id"; | |
| return $this->executeQueryForList($sql, $this->mapper[StudentLeaveServiceMapper::STUDENT_ATTENDANCE_AND_LEAVE_REPORT]); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param $request | |
| * @return Object | |
| * @throws ProfessionalException | |
| */ | |
| public function getSubjectWiseStudentDutyLeaveReport($request) | |
| { | |
| $cond = ""; | |
| $studentLeaveCondition = ""; | |
| $request = $this->realEscapeObject($request); | |
| $cond .= $request->studentID ? " AND att.studentID = '$request->studentID' " : ""; | |
| $cond .= $request->semID ? " AND att.semID = '$request->semID' " : ""; | |
| $cond .= $request->batchID ? " AND att.batchID = '$request->batchID' " : ""; | |
| $studentLeaveCondition .= !empty($request->leaveTypeId) ? " AND sla.leave_type_Id IN (" . implode($request->leaveTypeId, ',') . ") " : ""; | |
| $cond .= !empty($request->subjectID) ? " AND s.subjectID IN (" . implode($request->subjectID, ',') . ") " : ""; | |
| $studentLeaveCondition .= $request->staffId ? " AND sla.approved_by = '$request->staffId' " : ""; | |
| // $cond .= $request->inDate?" AND s.subjectID = '$request->fromDate' ":""; | |
| if ($request->fromDate && !$request->toDate && !$request->inDate) { | |
| $cond .= " AND att.attendanceDate > '$request->fromDate' "; | |
| } elseif (!$request->fromDate && $request->toDate && !$request->inDate) { | |
| $cond .= " AND att.attendanceDate < '$request->toDate' "; | |
| } elseif ($request->fromDate && $request->toDate && !$request->inDate) { | |
| $cond .= " AND att.attendanceDate BETWEEN '$request->fromDate' and '$request->toDate' "; | |
| } | |
| try { | |
| if ($request) { | |
| $sql = "SELECT std.studentID, std.studentName, std.regNo, s.subjectID, s.subjectName, s.subjectDesc, | |
| sg.id subjectGroupId, sg.name as subjectGroupName,sg.priority as subjectGroupOrder, | |
| IF(slt.id is null, 0,slt.id ) as student_leave_type_id, slt.code as student_leave_type_code, | |
| IF(sla.approved_by is null, 0,sla.approved_by) as approved_by , sa.staffName as approvedStaffName, | |
| (COUNT(CASE WHEN att.isAbsent = 0 OR att.isAbsent = 2 THEN 1 END)) AS atthour, | |
| COUNT(att.studentID) AS totalhour, | |
| (COUNT(CASE WHEN sla.session_key IS NOT NULL THEN 1 END)) AS numberOfLeaveHour, | |
| (COUNT(CASE WHEN att.isAbsent = 0 OR att.isAbsent = 2 THEN 1 END) / COUNT(att.studentID)) * 100 AS attendancePer, | |
| (COUNT(CASE WHEN sla.session_key IS NOT NULL THEN 1 END) / COUNT(att.studentID)) * 100 AS leavePer | |
| FROM attendance att | |
| INNER JOIN studentaccount std ON att.studentID = std.studentID | |
| INNER JOIN batches bat ON bat.batchID = std.batchID | |
| INNER JOIN sbs_relation sbs ON sbs.sbsID = att.sbsID | |
| INNER JOIN subjects s ON s.subjectID = sbs.subjectID | |
| LEFT JOIN subjectGroups_subjects sgs ON sgs.subjects_id=s.subjectID AND sgs.batches_id = bat.batchID | |
| LEFT JOIN subjectGroups sg ON sg.id = sgs.subjectGroups_id | |
| LEFT JOIN student_leave_application sla ON att.studentID = sla.student_Id | |
| AND att.attendanceDate BETWEEN sla.start_date AND sla.end_date | |
| AND FIND_IN_SET(att.hour, sla.session_key) AND sla.status='APPROVED' $studentLeaveCondition | |
| LEFT JOIN student_leave_type slt ON slt.id = sla.leave_type_Id AND slt.isDL = 1 and slt.isActive=1 | |
| LEFT JOIN staffaccounts sa ON sa.staffID=sla.approved_by | |
| WHERE 1=1 $cond GROUP BY std.studentID, s.subjectID, slt.id"; | |
| return $this->executeQueryForList($sql, $this->mapper[StudentLeaveServiceMapper::STUDENT_ATTENDANCE_AND_LEAVE_REPORT]); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get student(s) DL leave reports | |
| * @param Object $request | |
| * @return Object|array $AttendanceDetails | |
| * @throws ProfessionalException | |
| * @author Nandu | |
| */ | |
| public function getStudentsDlLeaveTotalReport($request) | |
| { | |
| $cond = ""; | |
| $request = $this->realEscapeObject($request); | |
| $cond .= $request->studentID ? " AND att.studentID = '$request->studentID' " : ""; | |
| $cond .= $request->semID ? " AND att.semID = '$request->semID' " : ""; | |
| $cond .= $request->batchID ? " AND att.batchID = '$request->batchID' " : ""; | |
| $cond .= !empty($request->subjectID) ? " AND s.subjectID IN (" . implode($request->subjectID, ',') . ") " : ""; | |
| // $cond .= $request->inDate?" AND s.subjectID = '$request->fromDate' ":""; | |
| if ($request->fromDate && !$request->toDate && !$request->inDate) { | |
| $cond .= " AND tt.attendanceDate > '$request->fromDate' "; | |
| } elseif (!$request->fromDate && $request->toDate && !$request->inDate) { | |
| $cond .= " AND tt.attendanceDate < '$request->toDate' "; | |
| } elseif ($request->fromDate && $request->toDate && !$request->inDate) { | |
| $cond .= " AND att.attendanceDate BETWEEN '$request->fromDate' and '$request->toDate' "; | |
| } | |
| try { | |
| if ($request) { | |
| $sql = "SELECT | |
| std.studentID, std.studentName, | |
| (COUNT(CASE WHEN att.isAbsent = 0 OR att.isAbsent = 2 THEN 1 END)) AS atthour, | |
| COUNT(att.studentID) AS totalhour, | |
| (COUNT(CASE WHEN sla.session_key IS NOT NULL THEN 1 END)) AS numberOfLeaveHour, | |
| (COUNT(CASE WHEN att.isAbsent = 0 OR att.isAbsent = 2 THEN 1 END) / COUNT(att.studentID)) * 100 AS attendancePer, | |
| (COUNT(CASE WHEN sla.session_key IS NOT NULL THEN 1 END) / COUNT(att.studentID)) * 100 AS leavePer | |
| FROM | |
| attendance att | |
| INNER JOIN studentaccount std ON att.studentID = std.studentID | |
| INNER JOIN batches bat ON bat.batchID = std.batchID | |
| INNER JOIN sbs_relation sbs ON sbs.sbsID = att.sbsID | |
| INNER JOIN subjects s ON s.subjectID = sbs.subjectID | |
| LEFT JOIN student_leave_application sla ON att.studentID = sla.student_Id | |
| AND att.attendanceDate BETWEEN sla.start_date AND sla.end_date | |
| AND FIND_IN_SET(att.hour, sla.session_key) AND sla.status='APPROVED' | |
| LEFT JOIN student_leave_type slt ON slt.id = sla.leave_type_Id | |
| AND slt.isDL = 1 and slt.isActive=1 | |
| LEFT JOIN staffaccounts sa ON sa.staffID=sla.approved_by WHERE 1=1 $cond GROUP BY std.studentID"; | |
| return $this->executeQueryForList($sql); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getAllApprovedStaff() | |
| { | |
| $sql = "select distinct sa.staffID, sa.staffName from staffaccounts sa INNER JOIN student_leave_application sla ON sla.approved_by=sa.staffID order by sa.staffName"; | |
| try { | |
| return $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Leave(s) applied by a student | |
| * @param Integer $studentId | |
| * @param date $fromDate | |
| * @param date $toDate | |
| * @return Object $leaveDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function getLeavesAppliedByStudent($studentId,$fromDate,$toDate){ | |
| $studentId = $this->realEscapeString($studentId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $sql = "SELECT | |
| apsla.session_key, apsla.noof_days | |
| FROM | |
| student_leave_application apsla | |
| INNER JOIN | |
| student_leave_type apslt ON apsla.leave_type_id = apslt.id | |
| WHERE | |
| apsla.student_id = $studentId | |
| AND apsla.start_date >= '$fromDate' | |
| AND CASE | |
| WHEN | |
| (apsla.end_date = '0000-00-00' | |
| || apsla.end_date IS NULL) | |
| THEN | |
| (apsla.end_date = '0000-00-00' | |
| || apsla.end_date IS NULL) | |
| ELSE apsla.end_date <= '$toDate' | |
| END | |
| and apslt.isActive=1"; | |
| try{ | |
| $leaveDetails = $this->executeQueryForObject($sql); | |
| return $leaveDetails; | |
| } | |
| catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Approved leave(s) of a student | |
| * @param Integer $studentId | |
| * @param date $fromDate | |
| * @param date $toDate | |
| * @return Object $leaveDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function getApprovedLeaves($studentId,$fromDate,$toDate){ | |
| $studentId = $this->realEscapeString($studentId); | |
| $fromDate = $this->realEscapeString($fromDate); | |
| $toDate = $this->realEscapeString($toDate); | |
| $sql = "SELECT | |
| apsla.session_key, apsla.noof_days | |
| FROM | |
| student_leave_application apsla | |
| INNER JOIN | |
| student_leave_type apslt ON apsla.leave_type_id = apslt.id | |
| WHERE | |
| apsla.student_id = $studentId | |
| AND apsla.status = 'APPROVED' | |
| AND apsla.start_date >= '$fromDate' | |
| AND CASE | |
| WHEN | |
| (apsla.end_date = '0000-00-00' | |
| || apsla.end_date IS NULL) | |
| THEN | |
| (apsla.end_date = '0000-00-00' | |
| || apsla.end_date IS NULL) | |
| ELSE apsla.end_date <= '$toDate' | |
| END | |
| and apslt.isActive=1"; | |
| try{ | |
| $leaveDetails = $this->executeQueryForObject($sql); | |
| return $leaveDetails; | |
| } | |
| catch(\Exception $e) { | |
| throw new ProfessionalException($e->getCode(),$e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Get Parent And child leave types | |
| * @return array LeaveType | |
| */ | |
| public function getParentAndChildLeaveTypes($parentLeaveTypeId) | |
| { | |
| $leaveTypes = []; | |
| $sql = "SELECT id, | |
| code, | |
| name, | |
| description, | |
| parentId, | |
| isDL, | |
| properties->>'$.totalHours' as totalHours, | |
| properties->>'$.isShowInReport' as isShowInReport, | |
| properties->>'$.isDocumentCompulsory' as isDocumentCompulsory | |
| FROM student_leave_type where isActive=1 AND (id = '$parentLeaveTypeId' OR parentId = '$parentLeaveTypeId') order by name;"; | |
| try { | |
| $leaveTypes = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $leaveTypes; | |
| } | |
| /** | |
| * Get Parent And child leave types | |
| * @return array LeaveType | |
| */ | |
| public function getCurrentLeaveTypeDetails($leaveTypeId) | |
| { | |
| $sql = "SELECT id as id, | |
| code as code, | |
| name as name, | |
| description as description, | |
| parentId as parentId, | |
| isDL as isDL, | |
| properties->>'$.totalHours' as totalHours, | |
| properties->>'$.isShowInReport' as isShowInReport, | |
| properties->>'$.isDocumentCompulsory' as isDocumentCompulsory | |
| FROM student_leave_type where isActive=1 AND id = '$leaveTypeId'"; | |
| try { | |
| $leaveType = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $leaveType; | |
| } | |
| public function deleteWorkFlow($workflowId) | |
| { | |
| $workflowId = $this->realEscapeString($workflowId); | |
| try { | |
| $sql = "SELECT id from workflow_instance wi WHERE wi.workflow_id = $workflowId"; | |
| $leaveExists = $this->executeQueryForObject($sql); | |
| if($leaveExists->id) | |
| { | |
| return false; | |
| } | |
| else{ | |
| $sqlDelete1 = " DELETE from workflow_transition | |
| where workflow_state_id_source in (SELECT id from workflow_state ws WHERE workflow_id = $workflowId)"; | |
| $sqlDelete2 = " DELETE from workflow_transition | |
| where workflow_state_id_destination in (SELECT id from workflow_state ws WHERE workflow_id = $workflowId)"; | |
| $sqlDelete3 = " DELETE from workflow_state ws WHERE workflow_id = $workflowId"; | |
| $sqlDelete4 = " DELETE FROM workflow WHERE id = $workflowId"; | |
| $this->executeQueryForObject($sqlDelete1); | |
| $this->executeQueryForObject($sqlDelete2); | |
| $this->executeQueryForObject($sqlDelete3); | |
| $this->executeQueryForObject($sqlDelete4); | |
| return true; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } | |
| ?> |