Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 32 |
CRAP | |
0.00% |
0 / 782 |
| StaffMentorSessionFeedbackService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 32 |
13572.00 | |
0.00% |
0 / 782 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 5 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 1 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| insertMentorSession | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 36 |
|||
| confirmStaffMentorSessionFeedback | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getMentorSessionListForStaffLogin | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getDetailsOfMentorSessionBySessionId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 32 |
|||
| getDetailsOfMentorSessionBySessionIdToGiveFeedback | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 31 |
|||
| saveMentorSessionStudentFeedbackByStaff | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 19 |
|||
| updateMentorSessionStudentFeedbackByStaff | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 15 |
|||
| getMentorSessionStudentFeedbackByStudentIdAndSessionNo | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| updateMentorSessionName | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| sendStudentListWhoNotSubmittedFeedbackToMentor | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 74 |
|||
| sendStaffListWhoNotSubmittedFeedbackComments | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 49 |
|||
| getStaffDetailsForMentorSessionFeedbackReportFilter | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 24 |
|||
| getConsolidatedMentorSessionFeedbackReport | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 60 |
|||
| getConsolidatedMentorSessionFeedbackReportMentees | |
0.00% |
0 / 1 |
132.00 | |
0.00% |
0 / 61 |
|||
| updateMentorSessionDetails | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 21 |
|||
| getSessionDetails | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 13 |
|||
| insertMentorSessionMaterial | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getMentorSessionMaterial | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 21 |
|||
| deleteMentorSessionMaterial | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 19 |
|||
| updateMentorSessionStudentAttendanceByStaff | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 14 |
|||
| getDetailsForDeletion | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 18 |
|||
| DeleteSession | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 30 |
|||
| getAllStaffsForReport | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 9 |
|||
| getSessionsOfStaffs | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 26 |
|||
| getSessionsOfAssociations | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 24 |
|||
| getSessionDetailsForReport | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 40 |
|||
| getSessionAttendanceForReport | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 39 |
|||
| getMentorSessionAttendanceWithSessionId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 21 |
|||
| getStudentPresenceInASession | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 19 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use com\linways\base\dto\Email; | |
| use com\linways\base\dto\EmailTo; | |
| use com\linways\base\dto\EmailFrom; | |
| use com\linways\base\service\EmailService; | |
| use com\linways\core\ams\professional\service\CommonService; | |
| use com\linways\core\ams\professional\service\ResourceService; | |
| use com\linways\core\ams\professional\service\PseudoSubjectService; | |
| use com\linways\core\ams\professional\request\RemoveResourceRequest; | |
| use com\linways\core\ams\professional\dto\StaffMentorSessionFeedback; | |
| use com\linways\core\ams\professional\request\GetPreSignedUrlRequest; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\mapper\StaffMentorSessionFeedBackMapper; | |
| use com\linways\core\ams\professional\mapper\MentorSessionFeedbackReportFilterMapper; | |
| use com\linways\core\ams\professional\mapper\MentorSessionFeedbackReportOfStaffMapper; | |
| use com\linways\core\ams\professional\request\MentorSessionFeedbackConsolidatedReportRequest; | |
| use stdClass; | |
| class StaffMentorSessionFeedbackService extends BaseService | |
| { | |
| // /Condition 1 - Presence of a static member variable | |
| private static $_instance = null; | |
| private $mapper = []; | |
| // /Condition 2 - Locked down the constructor | |
| private function __construct() | |
| { | |
| $this->mapper = StaffMentorSessionFeedBackMapper::getInstance()->getMapper(); | |
| $this->mapper1 = MentorSessionFeedbackReportOfStaffMapper::getInstance()->getMapper(); | |
| $this->mapper2 = MentorSessionFeedbackReportFilterMapper::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; | |
| } | |
| /** | |
| * to create session for a pseudosubject by the staff | |
| * @param StaffMentorSessionFeedback $feedbackDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function insertMentorSession($feedbackDetails) | |
| { | |
| $feedbackDetails->psid = $this->realEscapeString($feedbackDetails->psid); | |
| $feedbackDetails->staffId = $this->realEscapeString($feedbackDetails->staffId); | |
| $feedbackDetails->date = $this->realEscapeString($feedbackDetails->date); | |
| $feedbackDetails->hour = $this->realEscapeString($feedbackDetails->hour); | |
| $feedbackDetails->end_time = $this->realEscapeString($feedbackDetails->end_time); | |
| $sessionNo = 0; | |
| $sessionCount = 0; | |
| $sessionName = ""; | |
| $psbuIds = ""; | |
| $pseudoSubjectSbs = PseudoSubjectService::getInstance()->getDetailsOfPseudosubjectSbs($feedbackDetails->staffId , $feedbackDetails->psid); | |
| $pseudoSubjectIds = PseudoSubjectService::getInstance()->getPesudeosubjectIdsbyBatchSubjectStaff($pseudoSubjectSbs->subjectID, $pseudoSubjectSbs->batchID, $pseudoSubjectSbs->staffID); | |
| $psbuId = []; | |
| foreach($pseudoSubjectIds as $id){ | |
| $psbuId[] = $id->pseudosubjectID; | |
| } | |
| $psbuIds = implode(",", $psbuId); | |
| $sessionCountSql = "SELECT MAX(`session_no`) as count FROM mentor_session WHERE psid in ($psbuIds)"; | |
| try { | |
| $sessionCount = $this->executeQueryForObject($sessionCountSql)->count; | |
| if($sessionCount == NULL || $sessionCount == "") { | |
| $sessionNo = 1; | |
| }else{ | |
| $sessionNo = $sessionCount + 1; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $sessionName = 'Session '.$sessionNo; | |
| $sql = "INSERT INTO mentor_session (session_name,session_no,hour,date,end_time,psid,staffId,created_by,created_date,updated_by,updated_date) VALUES ('$sessionName','$sessionNo','$feedbackDetails->hour','$feedbackDetails->date','$feedbackDetails->end_time','$feedbackDetails->psid','$feedbackDetails->staffId','$feedbackDetails->staffId',utc_timestamp(),'$feedbackDetails->staffId',utc_timestamp())"; | |
| try { | |
| return $this->executeQueryForObject($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * To final submit session by the staff | |
| * @param StaffMentorSessionFeedback $feedbackDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function confirmStaffMentorSessionFeedback($feedbackDetails) | |
| { | |
| $feedbackDetails->staffId = $this->realEscapeString($feedbackDetails->staffId); | |
| $feedbackDetails->sessionId = $this->realEscapeString($feedbackDetails->sessionId); | |
| $feedbackDetails->isFinalSubmitted = $this->realEscapeString($feedbackDetails->isFinalSubmitted); | |
| $feedbackDetails->psid = $this->realEscapeString($feedbackDetails->psid); | |
| $sql = "UPDATE mentor_session SET isFinalSubmitted= '$feedbackDetails->isFinalSubmitted', updated_date = utc_timestamp(), updated_by = '$feedbackDetails->staffId' WHERE staffId = '$feedbackDetails->staffId' AND id = '$feedbackDetails->sessionId' AND psid='$feedbackDetails->psid'"; | |
| try { | |
| $this->executeQuery($sql); | |
| return true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * To list mentor sessions | |
| * @param StaffMentorSessionFeedback $feedbackDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function getMentorSessionListForStaffLogin($feedbackDetails) | |
| { | |
| $feedbackDetails->psid = $this->realEscapeString($feedbackDetails->psid); | |
| $feedbackDetails->staffId = $this->realEscapeString($feedbackDetails->staffId); | |
| $sql = "SELECT id as sessionId,session_name,session_no,hour,date,end_time,psid,staffId,isFinalSubmitted,if(session_end_date is null,if( end_time is null, concat(date,' ','11:59 PM'), concat(date,' ',end_time)),if( end_time is null, concat(session_end_date,' ','11:59 PM'), concat(session_end_date,' ',end_time))) as lastDate,session_end_date as sessionEndDate from mentor_session where psid='$feedbackDetails->psid' and staffId='$feedbackDetails->staffId' order by session_no desc;"; | |
| try { | |
| $sessionList = $this->executeQueryForList($sql); | |
| return $sessionList; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * To get details of mentor session | |
| * @param StaffMentorSessionFeedback $feedbackDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function getDetailsOfMentorSessionBySessionId($feedbackDetails) | |
| { | |
| $feedbackDetails->sessionId = $this->realEscapeString($feedbackDetails->sessionId); | |
| $feedbackDetails->staffId = $this->realEscapeString($feedbackDetails->staffId); | |
| $feedbackDetails->sbsID = $this->realEscapeString($feedbackDetails->sbsID); | |
| $mentorSessionType = CommonService::getInstance()->getSettings("MENTOR_SESSION","MENTOR_SESSION_ATTENDANCE_TYPE"); | |
| if($mentorSessionType == "TIMETABLE"){ | |
| $sql = "SELECT ms.id,ms.session_no,ms.session_name,ms.hour,ms.date,ms.psid,ms.staffId,a.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,a.isAbsent,s.rollNo,mssf.feedback,mssf.isSubmitted | |
| from mentor_session ms | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join attendance a on a.sbsID = psbs.sbsID and ms.date= a.attendanceDate and ms.hour = a.hour and a.staffID = ms.staffId | |
| inner join studentaccount s on s.studentId = a.studentId | |
| inner join batches b on b.batchID = a.batchID | |
| left join mentor_session_student_feedback mssf on mssf.session_id=ms.id and mssf.studentID = a.studentID where ms.id = '$feedbackDetails->sessionId' and ms.staffId='$feedbackDetails->staffId'"; | |
| } | |
| else{ | |
| $sql = "SELECT ms.id,ms.session_no,ms.session_name,ms.hour,ms.date,ms.psid,ms.staffId,pss.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,IF(msstf.is_absent is NULL,1,msstf.is_absent) as isAbsent,s.rollNo,mssf.feedback,mssf.isSubmitted | |
| from mentor_session ms | |
| inner join pseudosubjects_students pss on ms.psid = pss.pseudosubjectID | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join sbs_relation sbsr on sbsr.sbsID = psbs.sbsID | |
| inner join studentaccount s on s.studentId = pss.studentId | |
| inner join batches b on b.batchID = sbsr.batchID and s.batchID = b.batchID | |
| left join mentor_session_student_feedback mssf on mssf.session_id=ms.id and mssf.studentID = pss.studentID | |
| left join mentor_session_staff_feedback msstf on msstf.session_id=ms.id and msstf.studentID = pss.studentID where ms.id = '$feedbackDetails->sessionId' and ms.staffId='$feedbackDetails->staffId'"; | |
| } | |
| try { | |
| $sessionList = $this->executeQueryForList($sql, $this->mapper [StaffMentorSessionFeedBackMapper::GET_MENTOR_SESSION_DETAILS]); | |
| return $sessionList; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * To get details of mentor session | |
| * @param StaffMentorSessionFeedback $feedbackDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function getDetailsOfMentorSessionBySessionIdToGiveFeedback($feedbackDetails) | |
| { | |
| $feedbackDetails->sessionId = $this->realEscapeString($feedbackDetails->sessionId); | |
| $feedbackDetails->staffId = $this->realEscapeString($feedbackDetails->staffId); | |
| $feedbackDetails->sbsID = $this->realEscapeString($feedbackDetails->sbsID); | |
| $mentorSessionType = CommonService::getInstance()->getSettings("MENTOR_SESSION","MENTOR_SESSION_ATTENDANCE_TYPE"); | |
| if($mentorSessionType == "TIMETABLE"){ | |
| $sql = "SELECT ms.id,ms.session_no,ms.session_name,ms.isFinalSubmitted,ms.hour,ms.date,ms.psid,ms.staffId,a.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,a.isAbsent,s.rollNo,mssf.feedback,if(ms.session_end_date is null,if( ms.end_time is null, concat(ms.date,' ','11:59 PM'), concat(ms.date,' ',ms.end_time)),if( ms.end_time is null, concat(ms.session_end_date,' ','11:59 PM'), concat(ms.session_end_date,' ',ms.end_time))) as lastDate,ms.session_end_date as sessionEndDate | |
| from mentor_session ms | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join attendance a on a.sbsID = psbs.sbsID and ms.date= a.attendanceDate and ms.hour = a.hour and a.staffID = ms.staffId | |
| inner join studentaccount s on s.studentId = a.studentId | |
| inner join batches b on b.batchID = a.batchID | |
| left join mentor_session_staff_feedback mssf on mssf.session_id=ms.id and mssf.studentID = a.studentID where ms.id = '$feedbackDetails->sessionId' and ms.staffId='$feedbackDetails->staffId'"; | |
| } | |
| else{ | |
| $sql = "SELECT ms.id,ms.session_no,ms.session_name,ms.isFinalSubmitted,ms.hour,ms.date,ms.psid,ms.staffId,pss.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,IF(mssf.is_absent is NULL,1,mssf.is_absent) as isAbsent,s.rollNo,mssf.feedback,if(ms.session_end_date is null,if( ms.end_time is null, concat(ms.date,' ','11:59 PM'), concat(ms.date,' ',ms.end_time)),if( ms.end_time is null, concat(ms.session_end_date,' ','11:59 PM'), concat(ms.session_end_date,' ',ms.end_time))) as lastDate,ms.session_end_date as sessionEndDate | |
| from mentor_session ms | |
| inner join pseudosubjects_students pss on ms.psid = pss.pseudosubjectID | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join sbs_relation sbsr on sbsr.sbsID = psbs.sbsID | |
| inner join studentaccount s on s.studentId = pss.studentId | |
| inner join batches b on b.batchID = sbsr.batchID and s.batchID = b.batchID | |
| left join mentor_session_staff_feedback mssf on mssf.session_id=ms.id and mssf.studentID = pss.studentID where ms.id = '$feedbackDetails->sessionId' and ms.staffId='$feedbackDetails->staffId'"; | |
| } | |
| try { | |
| $sessionList = $this->executeQueryForList($sql, $this->mapper [StaffMentorSessionFeedBackMapper::GET_MENTOR_SESSION_DETAILS]); | |
| return $sessionList; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * to save session feedback for a student by the staff | |
| * @param StaffMentorSessionFeedback $feedbackDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function saveMentorSessionStudentFeedbackByStaff($feedbackDetails) | |
| { | |
| $feedbackDetails->staffId = $this->realEscapeString($feedbackDetails->staffId); | |
| $feedbackDetails->studentId = $this->realEscapeString($feedbackDetails->studentId); | |
| if($feedbackDetails->studentId == ""){ | |
| $feedbackDetails->studentId = null; | |
| } | |
| $feedbackDetails->sessionId = $this->realEscapeString($feedbackDetails->sessionId); | |
| $feedbackDetails->sessionNo = $this->realEscapeString($feedbackDetails->sessionNo); | |
| $feedbackDetails->feedback = $this->realEscapeString($feedbackDetails->feedback); | |
| $feedbackDetails->isAbsent = $this->realEscapeString($feedbackDetails->isAbsent); | |
| $feedback = ($feedbackDetails->feedback == 'null')?$feedbackDetails->feedback:"'".$feedbackDetails->feedback."'"; | |
| $sql = "INSERT INTO mentor_session_staff_feedback (staffId,studentId,feedback,session_id,session_no,created_by,created_date,updated_by,updated_date,is_absent) VALUES ('$feedbackDetails->staffId',$feedbackDetails->studentId,$feedback,'$feedbackDetails->sessionId','$feedbackDetails->sessionNo','$feedbackDetails->staffId',utc_timestamp(),'$feedbackDetails->staffId',utc_timestamp(),'$feedbackDetails->isAbsent')"; | |
| try { | |
| $this->executeQuery($sql); | |
| return true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * to update session feedback for a student by the staff | |
| * @param StaffMentorSessionFeedback $feedbackDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function updateMentorSessionStudentFeedbackByStaff($feedbackDetails) | |
| { | |
| $feedbackDetails->staffId = $this->realEscapeString($feedbackDetails->staffId); | |
| $feedbackDetails->studentId = $this->realEscapeString($feedbackDetails->studentId); | |
| $feedbackDetails->feedback = $this->realEscapeString($feedbackDetails->feedback); | |
| $feedbackDetails->sessionId = $this->realEscapeString($feedbackDetails->sessionId); | |
| $feedbackDetails->sessionNo = $this->realEscapeString($feedbackDetails->sessionNo); | |
| $feedbackDetails->isAbsent = $this->realEscapeString($feedbackDetails->isAbsent); | |
| $sql = "UPDATE mentor_session_staff_feedback SET feedback= '$feedbackDetails->feedback', updated_date = utc_timestamp(),updated_by = '$feedbackDetails->staffId',is_absent= '$feedbackDetails->isAbsent' WHERE studentId='$feedbackDetails->studentId' AND staffId = '$feedbackDetails->staffId' AND session_id = '$feedbackDetails->sessionId' AND session_no = '$feedbackDetails->sessionNo' "; | |
| try { | |
| $this->executeQuery($sql); | |
| return true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * to check session feedback for a student by the staff is already saved or not | |
| * @param StaffMentorSessionFeedback $feedbackDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function getMentorSessionStudentFeedbackByStudentIdAndSessionNo($feedbackDetails) | |
| { | |
| $feedbackDetails->sessionId = $this->realEscapeString($feedbackDetails->sessionId); | |
| $feedbackDetails->studentId = $this->realEscapeString($feedbackDetails->studentId); | |
| $feedbackDetails->sessionNo = $this->realEscapeString($feedbackDetails->sessionNo); | |
| $sql = "SELECT id from mentor_session_staff_feedback WHERE studentId='$feedbackDetails->studentId' AND session_no = '$feedbackDetails->sessionNo' AND `session_id` = '$feedbackDetails->sessionId' "; | |
| try { | |
| $feedbackId = $this->executeQueryForObject($sql)->id; | |
| return $feedbackId; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * to check session feedback for a student by the staff is already saved or not | |
| */ | |
| public function updateMentorSessionName($sessionId,$sessionName) | |
| { | |
| $sessionId = $this->realEscapeString($sessionId); | |
| $sessionName = $this->realEscapeString($sessionName); | |
| $sql = "UPDATE mentor_session SET session_name='$sessionName' WHERE id = '$sessionId' "; | |
| try { | |
| $this->executeQuery($sql); | |
| return true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * To get details of mentor session | |
| * @param StaffMentorSessionFeedback $feedbackDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function sendStudentListWhoNotSubmittedFeedbackToMentor($feedbackDetails) | |
| { | |
| global $college_MailFromID; | |
| global $MailFromName; | |
| $feedbackDetails->sessionId = $this->realEscapeString($feedbackDetails->sessionId); | |
| $feedbackDetails->staffId = $this->realEscapeString($feedbackDetails->staffId); | |
| $feedbackDetails->sbsID = $this->realEscapeString($feedbackDetails->sbsID); | |
| $sessionsql = "select * from mentor_session where id='$feedbackDetails->sessionId'"; | |
| $resultSql= "SELECT ms.id,ms.session_no,ms.session_name,ms.hour,ms.date,ms.psid,ms.staffId,a.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,a.isAbsent,s.rollNo,mssf.feedback,mssf.isSubmitted | |
| from mentor_session ms | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join attendance a on a.sbsID = psbs.sbsID and ms.date= a.attendanceDate and ms.hour = a.hour and a.staffID = ms.staffId | |
| inner join studentaccount s on s.studentId = a.studentId | |
| inner join batches b on b.batchID = a.batchID | |
| inner join mentor_session_student_feedback mssf on mssf.session_id=ms.id and mssf.studentID = a.studentID where ms.id = '$feedbackDetails->sessionId' and ms.staffId='$feedbackDetails->staffId' and a.isAbsent=0;"; | |
| try { | |
| $sessiondetails = $this->executeQueryForObject($sessionsql); | |
| $result = $this->executeQueryForList($resultSql); | |
| if(count($result) > 0){ | |
| $sql = "SELECT ms.id,ms.session_no,ms.session_name,ms.hour,ms.date,ms.psid,ms.staffId,a.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,a.isAbsent,s.rollNo,mssf.feedback,mssf.isSubmitted | |
| from mentor_session ms | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join attendance a on a.sbsID = psbs.sbsID and ms.date= a.attendanceDate and ms.hour = a.hour and a.staffID = ms.staffId | |
| inner join studentaccount s on s.studentId = a.studentId | |
| inner join batches b on b.batchID = a.batchID | |
| left join mentor_session_student_feedback mssf on mssf.session_id=ms.id and mssf.studentID = a.studentID where ms.id = '$feedbackDetails->sessionId' and ms.staffId='$feedbackDetails->staffId' and a.isAbsent=0 and mssf.studentID is null | |
| union | |
| SELECT ms.id,ms.session_no,ms.session_name,ms.hour,ms.date,ms.psid,ms.staffId,a.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,a.isAbsent,s.rollNo,mssf.feedback,mssf.isSubmitted | |
| from mentor_session ms | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join attendance a on a.sbsID = psbs.sbsID and ms.date= a.attendanceDate and ms.hour = a.hour and a.staffID = ms.staffId | |
| inner join studentaccount s on s.studentId = a.studentId | |
| inner join batches b on b.batchID = a.batchID | |
| inner join mentor_session_student_feedback mssf on mssf.session_id=ms.id and mssf.studentID = a.studentID where ms.id = '$feedbackDetails->sessionId' and ms.staffId='$feedbackDetails->staffId' and a.isAbsent=0 and mssf.feedback is not null and mssf.isSubmitted = 0"; | |
| }else{ | |
| $sql = "SELECT ms.id,ms.session_no,ms.session_name,ms.hour,ms.date,ms.psid,ms.staffId,a.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,a.isAbsent,s.rollNo,mssf.feedback,mssf.isSubmitted | |
| from mentor_session ms | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join attendance a on a.sbsID = psbs.sbsID and ms.date= a.attendanceDate and ms.hour = a.hour and a.staffID = ms.staffId | |
| inner join studentaccount s on s.studentId = a.studentId | |
| inner join batches b on b.batchID = a.batchID | |
| left join mentor_session_student_feedback mssf on mssf.session_id=ms.id and mssf.studentID = a.studentID where ms.id = '$feedbackDetails->sessionId' and ms.staffId='$feedbackDetails->staffId' and a.isAbsent=0 "; | |
| } | |
| $staffSql = "SELECT * from staffaccounts where staffID='$feedbackDetails->staffId'"; | |
| $staffDeatils = $this->executeQueryForObject($staffSql); | |
| $sessionList = $this->executeQueryForList($sql, $this->mapper [StaffMentorSessionFeedBackMapper::GET_MENTOR_SESSION_DETAILS]); | |
| $sessionList = $sessionList[0]; | |
| $mailContent = "Students who have not submitted the feedback for ".$sessiondetails->session_name." (".$sessiondetails->date.") <br>"; | |
| if(count($sessionList->studentList) > 0) { | |
| foreach($sessionList->studentList as $index => $student){ | |
| if($index > 0){ | |
| $mailContent .= ", ".$student->studentName ."(".$student->rollNo.")"; | |
| }else{ | |
| $mailContent .= $student->studentName ."(".$student->rollNo.")"; | |
| } | |
| } | |
| }else{ | |
| $mailContent .= "No students!"; | |
| } | |
| $emailObj = new Email(); | |
| $emailFrom = new EmailFrom(); | |
| $emailFrom->email = $college_MailFromID; | |
| $emailFrom->name = $MailFromName; | |
| $emailTo = new EmailTo(); | |
| $emailTo->email = $staffDeatils->staffEmail; | |
| $emailTo->name = $staffDeatils->staffName; | |
| $emailObj->subject =$sessiondetails->session_name." - Students who have not submitted the feedback"; | |
| $emailObj->body = $mailContent; | |
| $emailObj->from = $emailFrom; | |
| $emailObj->to = $emailTo; | |
| EmailService::getInstance()->sendEmail($emailObj); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * To get details of mentor session | |
| * @param StaffMentorSessionFeedback $feedbackDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function sendStaffListWhoNotSubmittedFeedbackComments() | |
| { | |
| global $college_MailFromID,$MailFromName; | |
| $incrementDays = json_decode(CommonService::getInstance()->getSettings("ACADEMICS","LAST_DATE_INCREMENT_FOR_MENTOR_SESSION_FEEDBACK")); | |
| $incrementDay = ($incrementDays->staffSideIncrementDays)+1; | |
| $currentDate =date('Y-m-d'); | |
| $dateTo = date("Y-m-d", strtotime("$currentDate -$incrementDay day")); | |
| $dateFrom = date("Y-m-d", strtotime("$dateTo - 6 day")); | |
| $sql = "select ms.id,ms.session_name,s.staffName,s.staffAccount,s.staffCode,ms.isFinalSubmitted,ms.date,ms.hour from mentor_session ms inner join staffaccounts s on s.staffid=ms.staffid where ms.isFinalSubmitted = 0 and date between '$dateFrom' and '$dateTo'"; | |
| $sessionList = $this->executeQueryForList($sql, $this->mapper1 [MentorSessionFeedbackReportOfStaffMapper::GET_MENTOR_SESSION_DETAILS_FOR_REPORT]); | |
| $mentorIncharge = CommonService::getInstance()->getSettings("ACADEMICS", "MENTOR_IN_CHARGE_FOR_MENTORING_SESSIONS"); | |
| $staffSql = "SELECT staffName,staffEmail,staffcode from staffaccounts where staffAccount='$mentorIncharge'"; | |
| $mentorInchargeDeatils = $this->executeQueryForObject($staffSql); | |
| try { | |
| $mailContent = "<h2><b>Staff who have not submitted the comments for Mentoring Sessions ( ".$dateFrom." to ".$dateTo." )</h2></b> <br>"; | |
| if(count($sessionList) > 0){ | |
| foreach($sessionList as $index => $date){ | |
| if(count($date->staffList) > 0) { | |
| $mailContent .= "<b><h3>Mentoring Session on - ".$date->date."</b></h3><br>"; | |
| foreach($date->staffList as $index => $staff){ | |
| if(count($staff->sessionList) > 0){ | |
| $mailContent .= "<b>".$staff->staffName."(".$staff->staffCode.")</b><br>"; | |
| foreach($staff->sessionList as $sessionindex => $session){ | |
| $mailContent .= $sessionindex+1 .") ".$session->session_name ."(Hour - ".$session->hour.")<br>"; | |
| } | |
| } | |
| } | |
| }else{ | |
| $mailContent .= "No staffs!"; | |
| } | |
| } | |
| }else{ | |
| $mailContent .= "No staffs!"; | |
| } | |
| $emailObj = new Email(); | |
| $emailFrom = new EmailFrom(); | |
| $emailFrom->email = $college_MailFromID; | |
| $emailFrom->name = $MailFromName; | |
| $emailTo = new EmailTo(); | |
| $emailTo->email = $mentorInchargeDeatils->staffEmail; | |
| $emailTo->name = $mentorInchargeDeatils->staffName; | |
| $emailObj->subject ="Staff who have not submitted the comments for mentoring sessions( ".$dateFrom." to ".$dateTo." )"; | |
| $emailObj->body = $mailContent; | |
| $emailObj->from = $emailFrom; | |
| $emailObj->to = $emailTo; | |
| EmailService::getInstance()->sendEmail($emailObj); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * | |
| */ | |
| public function getStaffDetailsForMentorSessionFeedbackReportFilter() | |
| { | |
| $sql = "SELECT s.staffID, | |
| s.staffName, | |
| p.pseudosubjectID, | |
| p.subjectName, | |
| b.batchID, | |
| b.batchName, | |
| ms.id, | |
| ms.session_name from staffaccounts s | |
| inner join sbs_relation sbs on s.staffID = sbs.staffID | |
| inner join subjects sub on sub.subjectID = sbs.subjectID | |
| inner join pseudosubjects_sbs psbs on psbs.sbsID = sbs.sbsID | |
| inner join pseudosubjects p on p.pseudosubjectID = psbs.pseudosubjectID | |
| inner join pseudo_subject_type pst on pst.id = p.pseudo_subject_type_id | |
| inner join batches b on b.batchID = sbs.batchID | |
| inner join mentor_session ms on ms.psid = p.pseudosubjectID and ms.staffId = s.staffID | |
| WHERE sub.subjectName = 'MENTOR' OR pst.code ='ASSOCIATION'"; | |
| try { | |
| $staffDetails = $this->executeQueryForList($sql, $this->mapper2 [MentorSessionFeedbackReportFilterMapper::GET_MENTOR_SESSION_STAFF_DETAILS_FOR_REPORT_FILTER]); | |
| return $staffDetails; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| *to get consolidated mentor session feedback report | |
| * @param MentorSessionFeedbackConsolidatedReportRequest $reportDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function getConsolidatedMentorSessionFeedbackReport($reportDetails) | |
| { | |
| $reportDetails->staffID = $this->realEscapeString($reportDetails->staffID); | |
| $reportDetails->fromDate = $this->realEscapeString($reportDetails->fromDate); | |
| $reportDetails->toDate = $this->realEscapeString($reportDetails->toDate); | |
| $reportDetails->batchID = $this->realEscapeString($reportDetails->batchID); | |
| $conditions = ""; | |
| if($reportDetails->staffID){ | |
| $conditions .= " AND ms.staffId = '$reportDetails->staffID' "; | |
| } | |
| if ($reportDetails->fromDate && $reportDetails->toDate) { | |
| $fromDate = date("Y-m-d", strtotime($reportDetails->fromDate)); | |
| $toDate = date("Y-m-d", strtotime($reportDetails->toDate)); | |
| $conditions .= " AND ms.date between '$fromDate' and '$toDate'"; | |
| } else if ($toDate) { | |
| $toDate = date("Y-m-d", strtotime($reportDetails->toDate)); | |
| $conditions .= " AND ms.date <= '$toDate'"; | |
| } else if ($reportDetails->fromDate) { | |
| $fromDate = date("Y-m-d", strtotime($reportDetails->fromDate)); | |
| $conditions .= " AND ms.date >= '$fromDate'"; | |
| } | |
| if($reportDetails->batchID){ | |
| $conditions .= " AND b.batchID = '$reportDetails->batchID' "; | |
| } | |
| $mentorSessionType = CommonService::getInstance()->getSettings("MENTOR_SESSION","MENTOR_SESSION_ATTENDANCE_TYPE"); | |
| if($mentorSessionType == "TIMETABLE"){ | |
| $sql = "SELECT a.isAbsent,st.staffAccount,st.staffName,st.staffCode,ms.id,ms.session_no,ms.session_name,ms.hour,ms.date,ms.psid,ms.staffId,a.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,s.rollNo,mssf.feedback,mssf.isSubmitted,ms.session_end_date | |
| from mentor_session ms | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join attendance a on a.sbsID = psbs.sbsID and ms.date= a.attendanceDate and ms.hour = a.hour and a.staffID = ms.staffId | |
| inner join studentaccount s on s.studentID = a.studentID | |
| inner join batches b on b.batchID = a.batchID | |
| left join mentor_session_student_feedback mssf on mssf.session_id=ms.id and mssf.studentID = a.studentID left join staffaccounts st on ms.staffId=st.staffID where 1=1 $conditions order by ms.session_no;"; | |
| } | |
| else{ | |
| $sql = "SELECT IF(msstf.is_absent is NULL,1,msstf.is_absent) as isAbsent,st.staffAccount,st.staffName,st.staffCode,ms.id,ms.session_no,ms.session_name,ms.hour,ms.date,ms.psid,ms.staffId,pss.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,s.rollNo,mssf.feedback,mssf.isSubmitted,ms.session_end_date | |
| from mentor_session ms | |
| inner join pseudosubjects_students pss on ms.psid = pss.pseudosubjectID | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join sbs_relation sbsr on sbsr.sbsID = psbs.sbsID | |
| inner join studentaccount s on s.studentID = pss.studentID | |
| inner join batches b on b.batchID = sbsr.batchID and s.batchID = b.batchID | |
| left join mentor_session_student_feedback mssf on mssf.session_id=ms.id and mssf.studentID = pss.studentID left join staffaccounts st on ms.staffId=st.staffID | |
| left join mentor_session_staff_feedback msstf on msstf.session_id=ms.id and msstf.studentID = pss.studentID | |
| where 1=1 $conditions order by ms.session_no;"; | |
| } | |
| try { | |
| $list = $this->executeQueryForList($sql, $this->mapper1 [MentorSessionFeedbackReportOfStaffMapper::GET_MENTOR_SESSION_CONSOLIDATED_REPORT_FOR_MENTOR]); | |
| foreach($list[0]->studentList as $index => $student){ | |
| $feedback = []; | |
| foreach($student->feedbackList as $session){ | |
| $sessionId =$session->session_id; | |
| $feedback[$sessionId] = $session; | |
| } | |
| $list[0]->studentList[$index]->feedbackList = $feedback; | |
| } | |
| return $list[0]; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| *to get consolidated mentor session feedback report | |
| * @param MentorSessionFeedbackConsolidatedReportRequest $reportDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function getConsolidatedMentorSessionFeedbackReportMentees($reportDetails) | |
| { | |
| $reportDetails->staffID = $this->realEscapeString($reportDetails->staffID); | |
| $reportDetails->fromDate = $this->realEscapeString($reportDetails->fromDate); | |
| $reportDetails->toDate = $this->realEscapeString($reportDetails->toDate); | |
| $reportDetails->batchID = $this->realEscapeString($reportDetails->batchID); | |
| $conditions = ""; | |
| if($reportDetails->staffID){ | |
| $conditions .= " AND ms.staffId = '$reportDetails->staffID' "; | |
| } | |
| if ($reportDetails->fromDate && $reportDetails->toDate) { | |
| $fromDate = date("Y-m-d", strtotime($reportDetails->fromDate)); | |
| $toDate = date("Y-m-d", strtotime($reportDetails->toDate)); | |
| $conditions .= " AND ms.date between '$fromDate' and '$toDate' "; | |
| } else if ($toDate) { | |
| $toDate = date("Y-m-d", strtotime($reportDetails->toDate)); | |
| $conditions .= " AND ms.date <= '$toDate' "; | |
| } else if ($reportDetails->fromDate) { | |
| $fromDate = date("Y-m-d", strtotime($reportDetails->fromDate)); | |
| $conditions .= " AND ms.date >= '$fromDate' "; | |
| } | |
| if($reportDetails->batchID){ | |
| $conditions .= " AND b.batchID = '$reportDetails->batchID' "; | |
| } | |
| $mentorSessionType = CommonService::getInstance()->getSettings("MENTOR_SESSION","MENTOR_SESSION_ATTENDANCE_TYPE"); | |
| if($mentorSessionType == "TIMETABLE"){ | |
| $sql = "SELECT a.isAbsent,st.staffAccount,st.staffName,st.staffCode,ms.id,ms.session_no,ms.session_name,ms.hour,ms.date,ms.psid,ms.staffId,a.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,s.rollNo,mssf.feedback,ms.isFinalSubmitted as isSubmitted | |
| from mentor_session ms | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join attendance a on a.sbsID = psbs.sbsID and ms.date= a.attendanceDate and ms.hour = a.hour and a.staffID = ms.staffId | |
| inner join studentaccount s on s.studentId = a.studentId | |
| inner join batches b on b.batchID = a.batchID | |
| left join mentor_session_staff_feedback mssf on mssf.session_id=ms.id and mssf.studentID = a.studentID left join staffaccounts st on ms.staffId=st.staffID where 1=1 $conditions order by session_no"; | |
| } | |
| else{ | |
| $sql = "SELECT IF(msstf.is_absent is NULL,1,msstf.is_absent) as isAbsent,st.staffAccount,st.staffName,st.staffCode,ms.id,ms.session_no,ms.session_name,ms.hour,ms.date,ms.psid,ms.staffId,pss.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,s.rollNo,mssf.feedback,ms.isFinalSubmitted as isSubmitted | |
| from mentor_session ms | |
| inner join pseudosubjects_students pss on ms.psid = pss.pseudosubjectID | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join sbs_relation sbsr on sbsr.sbsID = psbs.sbsID | |
| inner join studentaccount s on s.studentId = pss.studentId | |
| inner join batches b on b.batchID = sbsr.batchID and s.batchID = b.batchID | |
| left join mentor_session_staff_feedback mssf on mssf.session_id=ms.id and mssf.studentID = pss.studentID | |
| left join staffaccounts st on ms.staffId=st.staffID | |
| left join mentor_session_staff_feedback msstf on msstf.session_id=ms.id and msstf.studentID = pss.studentID | |
| where 1=1 $conditions order by session_no"; | |
| } | |
| try { | |
| $list = $this->executeQueryForList($sql, $this->mapper1 [MentorSessionFeedbackReportOfStaffMapper::GET_MENTOR_SESSION_CONSOLIDATED_REPORT_FOR_MENTOR]); | |
| foreach($list[0]->studentList as $index => $student){ | |
| $feedback = []; | |
| foreach($student->feedbackList as $session){ | |
| $sessionId =$session->session_id; | |
| $feedback[$sessionId] = $session; | |
| } | |
| $list[0]->studentList[$index]->feedbackList = $feedback; | |
| } | |
| return $list[0]; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| *to update Mentor Session | |
| * @param StaffMentorSessionFeedback $feedbackDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function updateMentorSessionDetails($feedbackDetails) | |
| { | |
| $feedbackDetails = $this->realEscapeObject($feedbackDetails); | |
| $where = []; | |
| $set = []; | |
| $set[] = "isFinalSubmitted= '$feedbackDetails->isFinalSubmitted'"; | |
| $set[] = "updated_by = '$feedbackDetails->staffId'"; | |
| $set[] = "updated_date = utc_timestamp()"; | |
| $feedbackDetails->end_time?$set[] = "end_time = '$feedbackDetails->end_time'":NULL; | |
| $feedbackDetails->session_name?$set[] = "session_name ='$feedbackDetails->session_name'":NULL; | |
| $feedbackDetails->staffId?$where[] = "staffId = '$feedbackDetails->staffId'":NULL; | |
| $feedbackDetails->sessionId?$where[] = "id = '$feedbackDetails->sessionId'":NULL; | |
| $feedbackDetails->psid?$where[] = "psid='$feedbackDetails->psid'":NULL; | |
| $feedbackDetails->sessionEndDate?$set[] = "session_end_date ='$feedbackDetails->sessionEndDate'":NULL; | |
| $sql = "UPDATE mentor_session SET ".implode(",",$set)." WHERE ".implode(" AND ",$where)." "; | |
| try { | |
| $this->executeQuery($sql); | |
| return true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * check Mentor Session | |
| * @param $sessionRequest | |
| * @throws ProfessionalException | |
| */ | |
| public function getSessionDetails($sessionRequest){ | |
| $sessionRequest = $this->realEscapeObject($sessionRequest); | |
| $sessionRequest->staffId?$where[] = "staffId = '$sessionRequest->staffId'":NULL; | |
| $sessionRequest->psid?$where[] = "psid='$sessionRequest->psid'":NULL; | |
| $sessionRequest->date?$where[] = "date='$sessionRequest->date'":NULL; | |
| $sessionRequest->hour?$where[] = "hour='$sessionRequest->hour'":NULL; | |
| $sql = "SELECT session_name,session_no,hour,date,end_time,psid,staffId FROM mentor_session WHERE ".implode(" AND ",$where)." "; | |
| try { | |
| $sessionDetails = $this->executeQueryForObject($sql); | |
| return $sessionDetails; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Save Session Material | |
| * @param $sessionRequest | |
| * @throws ProfessionalException | |
| */ | |
| public function insertMentorSessionMaterial($request){ | |
| $request = $this->realEscapeObject($request); | |
| // $request->linResourceId = $request->linResourceId?$request->linResourceId:NULL; | |
| $sql = "INSERT INTO mentor_session_attachments (session_id,lin_resource_id,url,user_type,created_by,created_date) values ('$request->sessionId','$request->linResourceId','$request->url','$request->userType','$request->userId',utc_timestamp())"; | |
| try { | |
| $mentorSessionId = $this->executeQuery($sql,true)->id; | |
| return $mentorSessionId; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getMentorSessionMaterial($request){ | |
| $request = $this->realEscapeObject($request); | |
| $sql = "SELECT id as materialId,session_id as sessionId,lin_resource_id as linResourceId,url from mentor_session_attachments WHERE session_id = '$request->sessionId'"; | |
| try { | |
| $sessionMaterials = $this->executeQueryForList($sql); | |
| foreach($sessionMaterials as $index => $material){ | |
| if($material->linResourceId){ | |
| $getResourceRequest = new GetPreSignedUrlRequest; | |
| $getResourceRequest->resourceId = $material->linResourceId; | |
| $getResourceRequest->accessKey = getenv('AWS_ACCESS_KEY'); | |
| $getResourceRequest->secretKey = getenv('AWS_CLIENT_SECRET_KEY'); | |
| $sessionMaterials[$index]->resource = ResourceService::getInstance()->getPreSignedUrlByResourceId($getResourceRequest); | |
| } | |
| else{ | |
| $sessionMaterials[$index]->resource = NULL; | |
| } | |
| } | |
| return $sessionMaterials; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function deleteMentorSessionMaterial($request){ | |
| $request = $this->realEscapeObject($request); | |
| $this->beginTransaction(); | |
| if($request->resourceId){ | |
| $removeRequest = new RemoveResourceRequest(); | |
| $removeRequest->resourceId = $request->resourceId; | |
| $removeRequest->accessKey = getenv('AWS_ACCESS_KEY'); | |
| $removeRequest->secretKey = getenv('AWS_CLIENT_SECRET_KEY'); | |
| ResourceService::getInstance()->removeResource($removeRequest); | |
| } | |
| $sql = "DELETE FROM mentor_session_attachments WHERE id = '$request->id'"; | |
| try{ | |
| $this->executeQuery($sql); | |
| $this->commit(); | |
| return; | |
| }catch (\Exception $e) { | |
| $this->rollback(); | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * to update session attendance for a student by the staff | |
| * @param StaffMentorSessionFeedback $feedbackDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function updateMentorSessionStudentAttendanceByStaff($feedbackDetails) | |
| { | |
| $feedbackDetails->staffId = $this->realEscapeString($feedbackDetails->staffId); | |
| $feedbackDetails->studentId = $this->realEscapeString($feedbackDetails->studentId); | |
| $feedbackDetails->sessionId = $this->realEscapeString($feedbackDetails->sessionId); | |
| $feedbackDetails->sessionNo = $this->realEscapeString($feedbackDetails->sessionNo); | |
| $feedbackDetails->isAbsent = $this->realEscapeString($feedbackDetails->isAbsent); | |
| $sql = "UPDATE mentor_session_staff_feedback SET updated_date = utc_timestamp(),updated_by = '$feedbackDetails->staffId',is_absent= '$feedbackDetails->isAbsent' WHERE studentId='$feedbackDetails->studentId' AND staffId = '$feedbackDetails->staffId' AND session_id = '$feedbackDetails->sessionId' AND session_no = '$feedbackDetails->sessionNo' "; | |
| try { | |
| $this->executeQuery($sql); | |
| return true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param SessionID,staffID,psID | |
| * @return Array | |
| * @throws ProfessionalException | |
| */ | |
| function getDetailsForDeletion($sessionID) | |
| { | |
| $sessionID = $this->realEscapeString($sessionID); | |
| $result = new stdClass(); | |
| try{ | |
| $sql_attachments = "SELECT count(lin_resource_id) as resources from mentor_session_attachments WHERE session_id = '$sessionID' "; | |
| $attachments = $this->executeQueryForObject($sql_attachments)->resources; | |
| $result->attachments = $attachments; | |
| $sql_staff_feedback = "SELECT count(studentId) as students FROM mentor_session_staff_feedback where session_id = '$sessionID' "; | |
| $staffFeedbacks = $this->executeQueryForObject($sql_staff_feedback)->students; | |
| $result->staffFeedbacks = $staffFeedbacks; | |
| $sql_student_feedback = "SELECT count(studentID) as feedbacks FROM mentor_session_student_feedback WHERE session_id = '$sessionID' "; | |
| $studentFeedbacks = $this->executeQueryForObject($sql_student_feedback)->feedbacks; | |
| $result->studentFeedbacks = $studentFeedbacks; | |
| return $result; | |
| }catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * @param SessionID | |
| * @return Boolean | |
| * @throws ProfessionalException | |
| */ | |
| function DeleteSession($sessionID) | |
| { | |
| $sessionID = $this->realEscapeString($sessionID); | |
| try{ | |
| $details = $this->getDetailsForDeletion($sessionID); | |
| if($details->attachments) | |
| { | |
| $sql = ""; | |
| $sql = "DELETE from mentor_session_attachments WHERE session_id = '$sessionID'"; | |
| $this->executeQuery($sql); | |
| } | |
| if($details->staffFeedbacks) | |
| { | |
| $sql = ""; | |
| $sql = "DELETE FROM mentor_session_staff_feedback where session_id = '$sessionID'"; | |
| $this->executeQuery($sql); | |
| } | |
| if($details->studentFeedbacks) | |
| { | |
| $sql = ""; | |
| $sql = "DELETE FROM mentor_session_student_feedback WHERE session_id = '$sessionID' "; | |
| $this->executeQuery($sql); | |
| } | |
| $sql = ""; | |
| $sql = "DELETE from mentor_session WHERE id = '$sessionID'"; | |
| $this->executeQuery($sql); | |
| return true; | |
| }catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| function getAllStaffsForReport() | |
| { | |
| try{ | |
| $sql = "SELECT DISTINCT ms.staffId as staffID,s.staffName from mentor_session ms inner join staffaccounts s on s.staffID = ms.staffId "; | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| function getSessionsOfStaffs($staffIds) | |
| { | |
| $staffId = $this->realEscapeString($staffIds); | |
| try{ | |
| $sql = "SELECT | |
| distinct p.pseudosubjectID as associationID,p.subjectName as associationName,s2.subjectID ,s2.subjectName ,s2.subjectDesc ,s2.syllabusName ,s.staffID ,s.staffName | |
| from | |
| pseudosubjects p | |
| inner join pseudo_subject_type pst on | |
| pst.id = p.pseudo_subject_type_id | |
| inner join pseudosubjects_sbs ps on | |
| ps.pseudosubjectID = p.pseudosubjectID | |
| inner join sbs_relation sr on | |
| sr.sbsID = ps.sbsID | |
| inner join subjects s2 on s2.subjectID = sr.subjectID | |
| inner join staffaccounts s on s.staffID = sr.staffID | |
| WHERE | |
| pst.code = 'ASSOCIATION' | |
| and pst.is_active = 1 | |
| and p.isFinalized = 1 | |
| and sr.staffID IN ($staffId)"; | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| function getSessionsOfAssociations($psIds, $from, $to) | |
| { | |
| $psIds = $this->realEscapeArray($psIds); | |
| $from = $this->realEscapeString($from); | |
| $from = implode("-",array_reverse(explode('-', $from))); | |
| $to = $this->realEscapeString($to); | |
| $to = implode("-",array_reverse(explode('-', $to))); | |
| $psIds = implode(",",$psIds); | |
| try{ | |
| $sql = "SELECT | |
| ms.id as sessionID, | |
| session_name as sessionName, | |
| ms.psid, | |
| p.subjectName, | |
| ms.date | |
| FROM | |
| mentor_session ms | |
| inner join pseudosubjects p on p.pseudosubjectID = ms.psid | |
| WHERE ms.isFinalSubmitted =1 and psid IN ($psIds) AND ms.date BETWEEN '$from' AND '$to' | |
| order by ms.id"; | |
| return $this->executeQueryForList($sql); | |
| }catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| function getSessionDetailsForReport($sessions) | |
| { | |
| $sessionIds = $this->realEscapeArray($sessions); | |
| $sessionIds = implode(",",$sessionIds); | |
| try{ | |
| $sql = "SELECT | |
| s.batchID , | |
| b.batchName , | |
| s2.semName , | |
| ms.id , | |
| ms.session_name , | |
| count(mssf.studentId ) as studentCount, | |
| sum(mssf.is_absent) as absentCount, | |
| ms.`date` , | |
| ms.`hour` , | |
| ms.psid , | |
| p.subjectName as associationName, | |
| s3.subjectID , | |
| s3.subjectName , | |
| s3.subjectDesc , | |
| s3.syllabusName , | |
| s3.syllabusYear | |
| FROM | |
| pseudosubjects_students ps | |
| inner join mentor_session ms on ms.psid = ps.pseudosubjectID | |
| inner join mentor_session_staff_feedback mssf on mssf.session_id = ms.id | |
| inner join studentaccount s on s.studentID = ps.studentID | |
| inner join batches b on b.batchID = s.batchID | |
| inner join semesters s2 on s2.semID = b.semID | |
| inner join pseudosubjects p on p.pseudosubjectID = ms.psid | |
| inner join pseudosubjects_sbs pss on pss.pseudosubjectID = p.pseudosubjectID | |
| inner join sbs_relation sr on sr.sbsID = pss.sbsID | |
| inner join subjects s3 on s3.subjectID = sr.subjectID | |
| WHERE | |
| ms.id in ($sessionIds) | |
| GROUP by s.batchID,ms.id "; | |
| return $this->executeQueryForList($sql); | |
| }catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| function getSessionAttendanceForReport($sessionIds, $associations) | |
| { | |
| try{ | |
| $sessionIds = $this->realEscapeArray($sessionIds); | |
| $sessionIds = implode(",",$sessionIds); | |
| $associations = $this->realEscapeArray($associations); | |
| $associations = implode(",",$associations); | |
| $sql = "SELECT | |
| p.pseudosubjectID , | |
| p.subjectName , | |
| s.studentID , | |
| s.studentName , | |
| s.regNo, | |
| b.batchID , | |
| b.batchName , | |
| s2.semName , | |
| ms.id as sessionID, | |
| ms.session_name , | |
| ms.`date` , | |
| ms.`hour` , | |
| mssf.is_absent | |
| from | |
| pseudosubjects_students ps | |
| inner join pseudosubjects p on p.pseudosubjectID = ps.pseudosubjectID | |
| inner join studentaccount s on s.studentID = ps.studentID | |
| inner join mentor_session ms on ms.psid = p.pseudosubjectID | |
| left join mentor_session_staff_feedback mssf on mssf.session_id = ms.id and mssf.studentId = s.studentID | |
| inner join batches b on b.batchID = s.batchID | |
| inner join semesters s2 on s2.semID = b.semID | |
| WHERE | |
| ps.pseudosubjectID in ($associations) | |
| and ms.id in ($sessionIds) | |
| GROUP BY ps.pseudosubjectID ,ps.studentID,mssf.session_id | |
| order by ps.pseudosubjectID ,ps.studentID ,ms.id"; | |
| return $this->executeQueryForList($sql); | |
| } | |
| catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| function getMentorSessionAttendanceWithSessionId($sessionId, $staffId = null, $batchID) | |
| { | |
| $sessionId = $this->realEscapeString($sessionId); | |
| $batchID = $this->realEscapeString($batchID); | |
| if($staffId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| } | |
| $sql = "SELECT ms.id,ms.session_no,ms.session_name,ms.isFinalSubmitted,ms.hour,ms.date,ms.psid,ms.staffId,pss.studentID,s.studentEmail as email,s.regNo,b.batchName,s.studentName,IF(mssf.is_absent is NULL,1,mssf.is_absent) as isAbsent,s.rollNo,mssf.feedback,if(ms.session_end_date is null,if( ms.end_time is null, concat(ms.date,' ','11:59 PM'), concat(ms.date,' ',ms.end_time)),if( ms.end_time is null, concat(ms.session_end_date,' ','11:59 PM'), concat(ms.session_end_date,' ',ms.end_time))) as lastDate,ms.session_end_date as sessionEndDate | |
| from mentor_session ms | |
| inner join pseudosubjects_students pss on ms.psid = pss.pseudosubjectID | |
| inner join pseudosubjects_sbs psbs on ms.psid = psbs.pseudosubjectID | |
| inner join sbs_relation sbsr on sbsr.sbsID = psbs.sbsID | |
| inner join studentaccount s on s.studentId = pss.studentId | |
| inner join batches b on b.batchID = sbsr.batchID and s.batchID = b.batchID | |
| left join mentor_session_staff_feedback mssf on mssf.session_id=ms.id and mssf.studentID = pss.studentID where ms.id = '$sessionId' and s.batchID = '$batchID' ".($staffId ? " and ms.staffId='$staffId'" : ""); | |
| try{ | |
| return $this->executeQueryForList($sql); | |
| }catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| function getStudentPresenceInASession($sessionID, $studentID) | |
| { | |
| try{ | |
| $sessionID = $this->realEscapeString($sessionID); | |
| $studentID = $this->realEscapeString($studentID); | |
| $sql = "SELECT | |
| ps.studentID ,ms.id ,ms.session_name ,mssf.feedback ,mssf.is_absent as status | |
| from | |
| pseudosubjects_students ps | |
| inner join mentor_session ms on | |
| ms.psid = ps.pseudosubjectID | |
| inner join mentor_session_staff_feedback mssf on | |
| mssf.session_id = ms.id | |
| WHERE | |
| ms.id in ($sessionID) and mssf.studentId in ($studentID)"; | |
| return $this->executeQueryForObject($sql); | |
| }catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } |