Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 39 |
CRAP | |
0.00% |
0 / 1792 |
| MessageService | |
0.00% |
0 / 1 |
|
0.00% |
0 / 39 |
138012.00 | |
0.00% |
0 / 1792 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| __clone | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| getInstance | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 5 |
|||
| sendMessage | |
0.00% |
0 / 1 |
2652.00 | |
0.00% |
0 / 290 |
|||
| sendMessageBatchStartYearAndCourseType | |
0.00% |
0 / 1 |
10302.00 | |
0.00% |
0 / 555 |
|||
| composeMessage | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 45 |
|||
| addAttachment | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 15 |
|||
| searchMessages | |
0.00% |
0 / 1 |
812.00 | |
0.00% |
0 / 96 |
|||
| getRecipent | |
0.00% |
0 / 1 |
272.00 | |
0.00% |
0 / 63 |
|||
| getMessageById | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getAttachments | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getMessageByUserId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| deleteMessage | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| deleteInboxMessage | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 35 |
|||
| setIsRead | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 19 |
|||
| getUnReadedCount | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 29 |
|||
| getCountOfMessages | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 21 |
|||
| markAllMessageAsRead | |
0.00% |
0 / 1 |
72.00 | |
0.00% |
0 / 33 |
|||
| markSelectedMessageAsRead | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 37 |
|||
| formatMessageContent | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 8 |
|||
| createUserGroup | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| updateUserGroup | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| deleteMessageUserGroupByGroupId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| addUsersToUserGroup | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 25 |
|||
| updateMessageUserGroupUser | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| deleteUsersFromMessageUserGroup | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 10 |
|||
| getNoticesByUserId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 21 |
|||
| getNoticeByUserAndId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 13 |
|||
| getMessageUserGroupsByStaffId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 12 |
|||
| getMessageUserGroupUsersByGroupId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getMessageUserGroupAndUsersByStaffId | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 16 |
|||
| getAllRecipientPhoneNumber | |
0.00% |
0 / 1 |
1560.00 | |
0.00% |
0 / 149 |
|||
| searchForStudentMessages | |
0.00% |
0 / 1 |
240.00 | |
0.00% |
0 / 47 |
|||
| getStaffRollByStaffId | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 16 |
|||
| purifyMessageContent | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 13 |
|||
| getMessageReadReceiptsById | |
0.00% |
0 / 1 |
90.00 | |
0.00% |
0 / 41 |
|||
| unsendMessage | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 11 |
|||
| getMessageByBatchIdMsgIdAndUserId | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 7 |
|||
| callPushNotificationApi | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 55 |
|||
| <?php | |
| namespace com\linways\core\ams\professional\service; | |
| use HTMLPurifier; | |
| use HTMLPurifier_Config; | |
| use com\linways\core\ams\professional\dto\Messages; | |
| use com\linways\core\ams\professional\dto\RecipeintType; | |
| use Symfony\Component\Config\Definition\Exception\Exception; | |
| use com\linways\core\ams\professional\request\MessageRequest; | |
| use com\linways\core\ams\professional\response\MessageResponse; | |
| use com\linways\core\ams\professional\mapper\MessageServiceMapper; | |
| use com\linways\core\ams\professional\exception\ProfessionalException; | |
| use com\linways\core\ams\professional\service\HODService; | |
| use com\linways\core\ams\professional\service\ThirdPartyApiService; | |
| use com\linways\core\ams\professional\dto\ExteralApi; | |
| use com\linways\core\ams\professional\dto\PushNotificationV4; | |
| use com\linways\base\util\SecurityUtils; | |
| use com\linways\base\util\RequestUtil; | |
| class MessageService extends BaseService | |
| { | |
| // /Condition 1 - Presence of a static member variable | |
| private static $_instance = null; | |
| // /Condition 2 - Locked down the constructor | |
| private function __construct() | |
| { | |
| } | |
| // 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; | |
| } | |
| /** | |
| * @param Messages $messages | |
| * @throws ProfessionalException | |
| */ | |
| public function sendMessage($messages) | |
| { | |
| $messages->content = $this->realEscapeString($messages->content); | |
| $messages->subject = $this->realEscapeString($messages->subject); | |
| $messages->batchId = $this->realEscapeString($messages->batchId); | |
| $messages->deptId = $this->realEscapeString($messages->deptId); | |
| $messages->recType = $this->realEscapeString($messages->recType); | |
| $messages->senderType = $this->realEscapeString($messages->senderType); | |
| $msgId = $this->composeMessage($messages); | |
| if($messages->deptId == 'null' || empty($messages->deptId)) { | |
| $messages->deptId =0; | |
| } | |
| if(empty($messages->subjectId)) { | |
| $messages->subjectId = 0; | |
| } | |
| switch ($messages->recType) { | |
| case RecipeintType::ALL: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', $messages->batchId, $messages->deptId, $messages->subjectId, | |
| $messages->isSubbatch FROM studentaccount WHERE student_lock=0 "; | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, staffID, 'FACULTY', $messages->batchId, $messages->deptId, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts WHERE isResigned=0 AND staffID !=$messages->senderId "; | |
| if ($messages->deptId) { | |
| $sqlStudents .= "AND deptID=$messages->deptId"; | |
| $sqlStaff .= "AND deptID=$messages->deptId"; | |
| } | |
| $sqlStaff .= " GROUP BY staffID"; | |
| try { | |
| $this->executeQueryForObject($sqlStudents); | |
| $this->executeQueryForObject($sqlStaff); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_BATCH: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', $messages->batchId, $messages->deptId, $messages->subjectId, | |
| $messages->isSubbatch FROM studentaccount WHERE student_lock=0 AND batchID=$messages->batchId AND deptID=$messages->deptId"; | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, st.staffID, 'FACULTY', $messages->batchId, st.deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts st INNER JOIN sbs_relation sr ON st.staffID = sr.staffID INNER JOIN batches b ON b.batchID = sr.batchID | |
| AND b.semID = sr.semID WHERE st.isResigned=0 AND st.deptID=$messages->deptId | |
| AND sr.batchID = $messages->batchId AND st.staffID !=$messages->senderId GROUP BY st.staffID"; | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| $this->executeQuery($sqlStaff); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_SUBBATCH: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, sa.studentID, 'STUDENT', sub.subbatchID, sa.deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM studentaccount sa INNER JOIN subbatches sub ON sub.batchID =sa.batchID INNER JOIN subbatch_student ss ON sub.subbatchID=ss.subbatchID | |
| AND sa.studentID =ss.studentID WHERE sub.subbatchID=$messages->batchId"; | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, st.staffID, 'FACULTY', $messages->batchId, st.deptID, $messages->subjectId, $messages->isSubbatch FROM staffaccounts st | |
| INNER JOIN sbs_relation sr ON st.staffID = sr.staffID INNER JOIN subbatch_sbs ss ON ss.sbsID=sr.sbsID | |
| WHERE st.isResigned=0 AND st.deptID=$messages->deptId AND ss.subbatchID=$messages->batchId AND st.staffID !=$messages->senderId group by st.staffID"; | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| $this->executeQuery($sqlStaff); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_FACULTY: | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, staffID, 'FACULTY', $messages->batchId, $messages->deptId, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts WHERE isResigned=0 AND staffID !=$messages->senderId group by staffID"; | |
| try { | |
| $this->executeQuery($sqlStaff); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_DEPT_FACULTY: | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, staffID, 'FACULTY', $messages->batchId, deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts WHERE isResigned=0 AND deptID=$messages->deptId AND staffID !=$messages->senderId group by staffID"; | |
| try { | |
| $this->executeQuery($sqlStaff); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_BATCH_FACULTY: | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, st.staffID, 'FACULTY', $messages->batchId, st.deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts st INNER JOIN sbs_relation sr ON st.staffID = sr.staffID INNER JOIN batches b ON b.batchID = sr.batchID | |
| AND b.semID = sr.semID WHERE st.isResigned=0 AND st.deptID=$messages->deptId | |
| AND sr.batchID = $messages->batchId AND st.staffID !=$messages->senderId group by st.staffID"; | |
| try { | |
| $this->executeQuery($sqlStaff); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_SUBBATCH_FACULTY: | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, st.staffID, 'FACULTY', $messages->batchId, st.deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts st INNER JOIN sbs_relation sr ON st.staffID = sr.staffID INNER JOIN subbatch_sbs ss ON ss.sbsID=sr.sbsID | |
| WHERE st.isResigned=0 AND st.deptID=$messages->deptId AND ss.subbatchID=$messages->batchId | |
| AND st.staffID !=$messages->senderId group by st.staffID"; | |
| try { | |
| $this->executeQuery($sqlStaff); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_STUDENTS: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', $messages->batchId, $messages->deptId, $messages->subjectId, $messages->isSubbatch | |
| FROM studentaccount WHERE student_lock=0"; | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_BATCH_STUDENT: | |
| if(!$messages->deptId){ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', batchID, deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM studentaccount WHERE student_lock=0 AND batchID=$messages->batchId "; | |
| } | |
| else{ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', batchID, deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM studentaccount WHERE student_lock=0 AND batchID=$messages->batchId AND deptID=$messages->deptId"; | |
| } | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_DEPT_STUDENT: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', $messages->batchId, deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM studentaccount WHERE student_lock=0 AND deptID=$messages->deptId"; | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| case RecipeintType::ALL_SUBBATCH_STUDENT: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, sa.studentID, 'STUDENT', sub.subbatchID, sa.deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM studentaccount sa INNER JOIN subbatches sub ON sub.batchID =sa.batchID INNER JOIN subbatch_student ss | |
| ON sub.subbatchID=ss.subbatchID AND sa.studentID =ss.studentID WHERE sub.subbatchID=$messages->batchId"; | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::INDIVIDUAL: | |
| if ($messages->recipeint != NULL && count($messages->recipeint) > 0) { | |
| $sql = 'INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) VALUES '; | |
| foreach ($messages->recipeint as $user) { | |
| $cond[] = "($msgId, $user->userId, '$user->userType', $messages->batchId, $messages->deptId, $messages->subjectId, $messages->isSubbatch )"; | |
| } | |
| $sql .= implode(',', $cond); | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| break; | |
| case RecipeintType::HOD: | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, staffID, 'HOD', $messages->batchId, deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts WHERE isResigned=0 AND deptID=$messages->deptId AND isHOD >0"; | |
| try { | |
| $this->executeQuery($sqlStaff); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::PRINCIPAL: | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, staffID, 'PRINCIPAL', $messages->batchId, deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts WHERE isResigned=0 AND isPrincipal >0"; | |
| try { | |
| $this->executeQuery($sqlStaff); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::CURRENT_STAFF: | |
| if ($messages->recipeint != NULL && count($messages->recipeint) > 0) { | |
| $sql = 'INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) VALUES '; | |
| foreach ($messages->recipeint as $user) { | |
| $cond[] = "($msgId, $user->userId, '$user->userType', $user->batchId, $user->deptId, $user->subjectId, $user->isSubbatch )"; | |
| } | |
| $sql .= implode(',', $cond); | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| break; | |
| case RecipeintType::ALL_HOD: | |
| $faculties = HODService::getInstance()->getAllHodDetails(); | |
| $sql = 'INSERT INTO messageRecipeints(msgId, userId, userType, deptId ) VALUES '; | |
| foreach ($faculties as $user) { | |
| $cond[] = "($msgId, $user->staffID, 'HOD', $user->deptID )"; | |
| } | |
| $sql .= implode(',', $cond); | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_STUDENTS_OF_BATCHYEAR: | |
| if($messages->deptId){ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId ) | |
| SELECT | |
| $msgId, studentID, 'STUDENT', b.batchID, s.deptID | |
| FROM | |
| studentaccount s | |
| INNER JOIN | |
| batches b ON b.batchID = s.batchID | |
| WHERE | |
| student_lock = 0 | |
| AND b.batchStartYear = $messages->batchStartYear | |
| And b.deptID = $messages->deptId | |
| ;"; | |
| } | |
| else{ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId ) | |
| SELECT | |
| $msgId, studentID, 'STUDENT', b.batchID, s.deptID | |
| FROM | |
| studentaccount s | |
| INNER JOIN | |
| batches b ON b.batchID = s.batchID | |
| WHERE | |
| student_lock = 0 | |
| AND b.batchStartYear = $messages->batchStartYear | |
| ;"; | |
| } | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_STUDENTS_OF_BATCHYEAR_WITH_COURSETYPE: | |
| if($messages->deptId){ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId ) | |
| SELECT | |
| $msgId, studentID, 'STUDENT', b.batchID, s.deptID | |
| FROM | |
| studentaccount s | |
| INNER JOIN | |
| batches b ON b.batchID = s.batchID | |
| WHERE | |
| student_lock = 0 | |
| AND b.batchStartYear = $messages->batchStartYear | |
| And b.deptID = $messages->deptId | |
| AND b.courseTypeID = '$messages->courseId' | |
| ;"; | |
| } | |
| else{ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId ) | |
| SELECT | |
| $msgId, studentID, 'STUDENT', b.batchID, s.deptID | |
| FROM | |
| studentaccount s | |
| INNER JOIN | |
| batches b ON b.batchID = s.batchID | |
| WHERE | |
| student_lock = 0 | |
| AND b.batchStartYear = $messages->batchStartYear | |
| AND b.courseTypeID = '$messages->courseId' | |
| ;"; | |
| } | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| // case RecipeintType::USER_GROUP: | |
| // $sql = "INSERT INTO messageRecipeints ( msgId, userId, userType ) | |
| // SELECT $msgId, IF(staff_id, staff_id, student_id) AS userId, IF(staff_id, 'FACULTY', 'STUDENT') AS userType FROM message_user_group_users "; | |
| // try { | |
| // $this->executeQuery($sql); | |
| // } catch (\Exception $e) { | |
| // throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| // } | |
| // break; | |
| } | |
| } | |
| /** | |
| * @param Messages $messages | |
| * @throws ProfessionalException | |
| */ | |
| public function sendMessageBatchStartYearAndCourseType($messages) | |
| { | |
| $messages->content = $this->realEscapeString($messages->content); | |
| $messages->subject = $this->realEscapeString($messages->subject); | |
| $messages->batchId = $this->realEscapeString($messages->batchId); | |
| $messages->deptId = $this->realEscapeString($messages->deptId); | |
| $messages->recType = $this->realEscapeString($messages->recType); | |
| $messages->senderType = $this->realEscapeString($messages->senderType); | |
| $msgId = $this->composeMessage($messages); | |
| if($messages->deptId == 'null' || empty($messages->deptId)) { | |
| $messages->deptId =0; | |
| } | |
| if(empty($messages->subjectId)) { | |
| $messages->subjectId = 0; | |
| } | |
| switch ($messages->recType) { | |
| case RecipeintType::ALL: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', $messages->batchId, $messages->deptId, $messages->subjectId, | |
| $messages->isSubbatch FROM studentaccount WHERE student_lock=0 "; | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, staffID, 'FACULTY', $messages->batchId, $messages->deptId, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts WHERE isResigned=0 AND staffID !=$messages->senderId "; | |
| if($messages->pushNotification) | |
| { | |
| $condition1 = ""; | |
| $type1= "STUDENT"; | |
| $condition2 = " WHERE staffID !=$messages->senderId"; | |
| $type2 = "FACULTY"; | |
| } | |
| if ($messages->deptId) { | |
| $sqlStudents .= "AND deptID=$messages->deptId"; | |
| $sqlStaff .= "AND deptID=$messages->deptId"; | |
| if($messages->pushNotification) | |
| { | |
| $condition1 .= " WHERE deptID=$messages->deptId"; | |
| $condition2 .= " AND deptID=$messages->deptId GROUP BY staffID"; | |
| } | |
| } | |
| $sqlStaff .= " GROUP BY staffID"; | |
| try { | |
| $this->executeQueryForObject($sqlStudents); | |
| $this->executeQueryForObject($sqlStaff); | |
| if($messages->pushNotification) | |
| { | |
| $this->callPushNotificationApi("STUDENT",$condition1, $messages); | |
| $this->callPushNotificationApi("FACULTY",$condition2, $messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_DEPT: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', $messages->batchId, $messages->deptId, $messages->subjectId, | |
| $messages->isSubbatch FROM studentaccount WHERE student_lock=0 "; | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, staffID, 'FACULTY', $messages->batchId, $messages->deptId, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts WHERE isResigned=0 AND staffID !=$messages->senderId "; | |
| if($messages->pushNotification) | |
| { | |
| $condition1 = ""; | |
| $type1= "STUDENT"; | |
| $condition2 = " WHERE staffID !=$messages->senderId"; | |
| $type2 = "FACULTY"; | |
| } | |
| if ($messages->deptId) { | |
| $sqlStudents .= "AND deptID=$messages->deptId"; | |
| $sqlStaff .= "AND deptID=$messages->deptId"; | |
| if($messages->pushNotification) | |
| { | |
| $condition1 .= " WHERE deptID=$messages->deptId"; | |
| $condition2 .= " AND deptID=$messages->deptId GROUP BY staffID"; | |
| } | |
| } | |
| $sqlStaff .= " GROUP BY staffID"; | |
| try { | |
| $this->executeQueryForObject($sqlStudents); | |
| $this->executeQueryForObject($sqlStaff); | |
| if($messages->pushNotification) | |
| { | |
| $this->callPushNotificationApi("STUDENT",$condition1, $messages); | |
| $this->callPushNotificationApi("FACULTY",$condition2, $messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_COURSETYPE: | |
| try { | |
| foreach($messages->courseId as $courseId){ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, s.studentID, 'STUDENT', $messages->batchId, $messages->deptId, $messages->subjectId, | |
| $messages->isSubbatch FROM studentaccount s | |
| INNER JOIN batches bat ON bat.batchID = s.batchID | |
| WHERE student_lock=0 AND bat.courseTypeID ='$courseId' "; | |
| if($messages->pushNotification) | |
| { | |
| $condition = " INNER JOIN batches bat ON bat.batchID = s.batchID | |
| WHERE s.student_lock=0 AND bat.courseTypeID ='$courseId'"; | |
| } | |
| if ($messages->deptId) { | |
| $sqlStudents .= "AND s.deptID=$messages->deptId"; | |
| if($messages->pushNotification) | |
| { | |
| $condition .= " AND s.deptID=$messages->deptId"; | |
| } | |
| } | |
| $this->executeQueryForObject($sqlStudents); | |
| if($messages->pushNotification) | |
| { | |
| $this->callPushNotificationApi("STUDENT",$condition, $messages); | |
| } | |
| } | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, staffID, 'FACULTY', $messages->batchId, $messages->deptId, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts WHERE isResigned=0 AND staffID !=$messages->senderId "; | |
| if($messages->pushNotification) | |
| { | |
| $condition = " WHERE isResigned=0 AND staffID !=$messages->senderId"; | |
| } | |
| if ($messages->deptId) { | |
| $sqlStaff .= "AND deptID=$messages->deptId"; | |
| if($messages->pushNotification) | |
| { | |
| $condition .= " AND deptID=$messages->deptId GROUP BY staffID"; | |
| } | |
| } | |
| $sqlStaff .= " GROUP BY staffID"; | |
| $this->executeQueryForObject($sqlStaff); | |
| if($messages->pushNotification) | |
| { | |
| $this->callPushNotificationApi("FACULTY",$condition, $messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_BATCH: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', $messages->batchId, $messages->deptId, $messages->subjectId, | |
| $messages->isSubbatch FROM studentaccount WHERE student_lock=0 AND batchID=$messages->batchId AND deptID=$messages->deptId"; | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, st.staffID, 'FACULTY', $messages->batchId, st.deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts st INNER JOIN sbs_relation sr ON st.staffID = sr.staffID INNER JOIN batches b ON b.batchID = sr.batchID | |
| AND b.semID = sr.semID WHERE st.isResigned=0 AND st.deptID=$messages->deptId | |
| AND sr.batchID = $messages->batchId AND st.staffID !=$messages->senderId GROUP BY st.staffID"; | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| $this->executeQuery($sqlStaff); | |
| if($messages->pushNotification) | |
| { | |
| $condition1 = " WHERE student_lock=0 AND batchID=$messages->batchId AND deptID=$messages->deptId "; | |
| $condition2 = " INNER JOIN sbs_relation sr ON s.staffID = sr.staffID INNER JOIN batches b ON b.batchID = sr.batchID | |
| AND b.semID = sr.semID WHERE s.isResigned=0 AND s.deptID=$messages->deptId | |
| AND sr.batchID = $messages->batchId AND s.staffID !=$messages->senderId GROUP BY s.staffID"; | |
| $this->callPushNotificationApi("STUDENT",$condition1, $messages); | |
| $this->callPushNotificationApi("FACULTY",$condition2, $messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_SUBBATCH: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, sa.studentID, 'STUDENT', sub.subbatchID, sa.deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM studentaccount sa INNER JOIN subbatches sub ON sub.batchID =sa.batchID INNER JOIN subbatch_student ss ON sub.subbatchID=ss.subbatchID | |
| AND sa.studentID =ss.studentID WHERE sub.subbatchID=$messages->batchId"; | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, st.staffID, 'FACULTY', $messages->batchId, st.deptID, $messages->subjectId, $messages->isSubbatch FROM staffaccounts st | |
| INNER JOIN sbs_relation sr ON st.staffID = sr.staffID INNER JOIN subbatch_sbs ss ON ss.sbsID=sr.sbsID | |
| WHERE st.isResigned=0 AND st.deptID=$messages->deptId AND ss.subbatchID=$messages->batchId AND st.staffID !=$messages->senderId group by st.staffID"; | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| $this->executeQuery($sqlStaff); | |
| if($messages->pushNotification) | |
| { | |
| $condition1 = " INNER JOIN subbatches sub ON sub.batchID = s.batchID INNER JOIN subbatch_student ss ON sub.subbatchID=ss.subbatchID | |
| AND s.studentID =ss.studentID WHERE sub.subbatchID=$messages->batchId "; | |
| $condition2 = " INNER JOIN sbs_relation sr ON s.staffID = sr.staffID INNER JOIN subbatch_sbs ss ON ss.sbsID=sr.sbsID | |
| WHERE s.isResigned=0 AND s.deptID=$messages->deptId AND ss.subbatchID=$messages->batchId AND s.staffID !=$messages->senderId group by s.staffID"; | |
| $this->callPushNotificationApi("STUDENT",$condition1, $messages); | |
| $this->callPushNotificationApi("FACULTY",$condition2, $messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_FACULTY: | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, staffID, 'FACULTY', $messages->batchId, $messages->deptId, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts WHERE isResigned=0 AND staffID !=$messages->senderId group by staffID"; | |
| try { | |
| $this->executeQuery($sqlStaff); | |
| if($messages->pushNotification) | |
| { | |
| $condition = " WHERE isResigned=0 AND staffID !=$messages->senderId group by staffID"; | |
| $this->callPushNotificationApi("FACULTY",$condition, $messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_DEPT_FACULTY: | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, staffID, 'FACULTY', $messages->batchId, deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts WHERE isResigned=0 AND deptID=$messages->deptId AND staffID !=$messages->senderId group by staffID"; | |
| try { | |
| $this->executeQuery($sqlStaff); | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " WHERE isResigned=0 AND deptID=$messages->deptId AND staffID !=$messages->senderId group by staffID"; | |
| $type = "FACULTY"; | |
| $this->callPushNotificationApi($type, $condition,$messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_BATCH_FACULTY: | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, st.staffID, 'FACULTY', $messages->batchId, st.deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts st INNER JOIN sbs_relation sr ON st.staffID = sr.staffID INNER JOIN batches b ON b.batchID = sr.batchID | |
| AND b.semID = sr.semID WHERE st.isResigned=0 AND st.deptID=$messages->deptId | |
| AND sr.batchID = $messages->batchId AND st.staffID !=$messages->senderId group by st.staffID"; | |
| try { | |
| $this->executeQuery($sqlStaff); | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " INNER JOIN sbs_relation sr ON s.staffID = sr.staffID INNER JOIN batches b ON b.batchID = sr.batchID | |
| AND b.semID = sr.semID WHERE s.isResigned=0 AND s.deptID=$messages->deptId | |
| AND sr.batchID = $messages->batchId AND s.staffID !=$messages->senderId group by s.staffID"; | |
| $this->callPushNotificationApi("FACULTY", $condition,$messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_SUBBATCH_FACULTY: | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, st.staffID, 'FACULTY', $messages->batchId, st.deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts st INNER JOIN sbs_relation sr ON st.staffID = sr.staffID INNER JOIN subbatch_sbs ss ON ss.sbsID=sr.sbsID | |
| WHERE st.isResigned=0 AND st.deptID=$messages->deptId AND ss.subbatchID=$messages->batchId | |
| AND st.staffID !=$messages->senderId group by st.staffID"; | |
| try { | |
| $this->executeQuery($sqlStaff); | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " INNER JOIN sbs_relation sr ON s.staffID = sr.staffID INNER JOIN subbatch_sbs ss ON ss.sbsID=sr.sbsID | |
| WHERE s.isResigned=0 AND s.deptID=$messages->deptId AND ss.subbatchID=$messages->batchId | |
| AND s.staffID !=$messages->senderId group by s.staffID"; | |
| $this->callPushNotificationApi("FACULTY", $condition,$messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_STUDENTS: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', $messages->batchId, $messages->deptId, $messages->subjectId, $messages->isSubbatch | |
| FROM studentaccount WHERE student_lock=0"; | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " WHERE student_lock=0"; | |
| $this->callPushNotificationApi("STUDENT", $condition,$messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_BATCH_STUDENT: | |
| if(!$messages->deptId){ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', batchID, deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM studentaccount WHERE student_lock=0 AND batchID=$messages->batchId "; | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " WHERE student_lock=0 AND batchID=$messages->batchId"; | |
| } | |
| } | |
| else{ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', batchID, deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM studentaccount WHERE student_lock=0 AND batchID=$messages->batchId AND deptID=$messages->deptId"; | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " WHERE student_lock=0 AND batchID=$messages->batchId AND deptID=$messages->deptId"; | |
| } | |
| } | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| if($messages->pushNotification ) | |
| { | |
| $this->callPushNotificationApi("STUDENT", $condition,$messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_DEPT_STUDENT: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, studentID, 'STUDENT', $messages->batchId, deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM studentaccount WHERE student_lock=0 AND deptID=$messages->deptId"; | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " WHERE student_lock=0 AND deptID=$messages->deptId"; | |
| $this->callPushNotificationApi("STUDENT", $condition,$messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| case RecipeintType::ALL_SUBBATCH_STUDENT: | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, sa.studentID, 'STUDENT', sub.subbatchID, sa.deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM studentaccount sa INNER JOIN subbatches sub ON sub.batchID =sa.batchID INNER JOIN subbatch_student ss | |
| ON sub.subbatchID=ss.subbatchID AND sa.studentID =ss.studentID WHERE sub.subbatchID=$messages->batchId"; | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " INNER JOIN subbatches sub ON sub.batchID =sa.batchID INNER JOIN subbatch_student ss | |
| ON sub.subbatchID=ss.subbatchID AND s.studentID =ss.studentID WHERE sub.subbatchID=$messages->batchId"; | |
| $this->callPushNotificationApi("STUDENT", $condition,$messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::INDIVIDUAL: | |
| if ($messages->recipeint != NULL && count($messages->recipeint) > 0) { | |
| $userIds = []; | |
| $sql = 'INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) VALUES '; | |
| foreach ($messages->recipeint as $user) { | |
| $userIds[] = $user->userId; | |
| $cond[] = "($msgId, $user->userId, '$user->userType', $messages->batchId, $messages->deptId, $messages->subjectId, $messages->isSubbatch )"; | |
| } | |
| $sql .= implode(',', $cond); | |
| try { | |
| $this->executeQuery($sql); | |
| if($messages->pushNotification ) | |
| { | |
| if($user->userType == "STUDENT") | |
| { | |
| $condition = " WHERE student_lock = 0 AND studentID in (".implode(",", $userIds).")"; | |
| } | |
| elseif ($user->userType == "FACULTY") { | |
| $condition = " WHERE s.isResigned = 0 and staffID in (".implode(",", $userIds).")"; | |
| } | |
| $this->callPushNotificationApi($user->userType, $condition,$messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| break; | |
| case RecipeintType::HOD: | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, staffID, 'HOD', $messages->batchId, deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts WHERE isResigned=0 AND deptID=$messages->deptId AND isHOD >0"; | |
| try { | |
| $this->executeQuery($sqlStaff); | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " WHERE isResigned=0 AND deptID=$messages->deptId AND isHOD >0"; | |
| $this->callPushNotificationApi("FACULTY", $condition,$messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::PRINCIPAL: | |
| $sqlStaff = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) | |
| SELECT $msgId, staffID, 'PRINCIPAL', $messages->batchId, deptID, $messages->subjectId, $messages->isSubbatch | |
| FROM staffaccounts WHERE isResigned=0 AND isPrincipal >0"; | |
| try { | |
| $this->executeQuery($sqlStaff); | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " WHERE isResigned=0 AND isPrincipal >0"; | |
| $this->callPushNotificationApi("FACULTY", $condition,$messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::CURRENT_STAFF: | |
| $userIds = []; | |
| if ($messages->recipeint != NULL && count($messages->recipeint) > 0) { | |
| $sql = 'INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId, subjectId, isSubbatch ) VALUES '; | |
| foreach ($messages->recipeint as $user) { | |
| $userIds[] = $user->userId; | |
| $cond[] = "($msgId, $user->userId, '$user->userType', $user->batchId, $user->deptId, $user->subjectId, $user->isSubbatch )"; | |
| } | |
| $sql .= implode(',', $cond); | |
| try { | |
| $this->executeQuery($sql); | |
| if($messages->pushNotification ) | |
| { | |
| if($user->userType == "STUDENT") | |
| { | |
| $condition = " WHERE studentID in (".implode(",",$userIds).")"; | |
| $this->callPushNotificationApi("STUDENT", $condition,$messages); | |
| } | |
| else{ | |
| $condition = " WHERE staffID in (".implode(",",$userIds).")"; | |
| $this->callPushNotificationApi("FACULTY", $condition,$messages); | |
| } | |
| $condition = " WHERE isResigned=0 AND isPrincipal >0"; | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| break; | |
| case RecipeintType::ALL_HOD: | |
| $faculties = HODService::getInstance()->getAllHodDetails(); | |
| $userIds = []; | |
| $sql = 'INSERT INTO messageRecipeints(msgId, userId, userType, deptId ) VALUES '; | |
| foreach ($faculties as $user) { | |
| $userIds[] = $user->staffID; | |
| $cond[] = "($msgId, $user->staffID, 'HOD', $user->deptID )"; | |
| } | |
| $sql .= implode(',', $cond); | |
| try { | |
| $this->executeQuery($sql); | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " WHERE s.isResigned = 0 and staffID in (".implode(",", $userIds).")"; | |
| $type = "FACULTY"; | |
| $this->callPushNotificationApi($type, $condition,$messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_STUDENTS_OF_BATCHYEAR: | |
| $messages->batchStartYears = implode(',',$messages->batchStartYear); | |
| if($messages->deptId){ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId ) | |
| SELECT | |
| $msgId, studentID, 'STUDENT', b.batchID, s.deptID | |
| FROM | |
| studentaccount s | |
| INNER JOIN | |
| batches b ON b.batchID = s.batchID | |
| WHERE | |
| student_lock = 0 | |
| AND b.batchStartYear IN ($messages->batchStartYears) | |
| And b.deptID = $messages->deptId | |
| ;"; | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " INNER JOIN | |
| batches b ON b.batchID = s.batchID | |
| WHERE | |
| student_lock = 0 | |
| AND b.batchStartYear IN ($messages->batchStartYears) | |
| And b.deptID = $messages->deptId"; | |
| } | |
| } | |
| else{ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId ) | |
| SELECT | |
| $msgId, studentID, 'STUDENT', b.batchID, s.deptID | |
| FROM | |
| studentaccount s | |
| INNER JOIN | |
| batches b ON b.batchID = s.batchID | |
| WHERE | |
| student_lock = 0 | |
| AND b.batchStartYear IN ($messages->batchStartYears) | |
| ;"; | |
| if($messages->pushNotification ) | |
| { | |
| $condition = " WHERE | |
| student_lock = 0 | |
| AND b.batchStartYear IN ($messages->batchStartYears)"; | |
| } | |
| } | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| if($messages->pushNotification ) | |
| { | |
| $this->callPushNotificationApi("STUDENT", $condition,$messages); | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_STUDENTS_OF_BATCHYEAR_WITH_COURSETYPE: | |
| $messages->batchStartYears = implode(',',$messages->batchStartYear); | |
| $messages->courseIds = implode(',',$messages->courseId); | |
| if($messages->deptId){ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId ) | |
| SELECT | |
| $msgId, studentID, 'STUDENT', b.batchID, s.deptID | |
| FROM | |
| studentaccount s | |
| INNER JOIN | |
| batches b ON b.batchID = s.batchID | |
| WHERE | |
| student_lock = 0 | |
| AND b.batchStartYear IN ($messages->batchStartYears) | |
| And b.deptID = $messages->deptId | |
| AND b.courseTypeID IN ($messages->courseIds) | |
| ;"; | |
| if($messages->pushNotification) | |
| { | |
| $condition = " INNER JOIN | |
| batches b ON b.batchID = s.batchID | |
| WHERE | |
| student_lock = 0". | |
| ($messages->batchStartYears ? "AND b.batchStartYear IN ($messages->batchStartYears)":""). | |
| ($messages->deptId ? "AND b.deptID = $messages->deptId":""). | |
| ($messages->courseIds ? "AND b.courseTypeID IN ($messages->courseIds)" : ""); | |
| $type = "STUDENT"; | |
| $this->callPushNotificationApi($type, $condition,$messages); | |
| } | |
| } | |
| else{ | |
| $sqlStudents = "INSERT INTO messageRecipeints(msgId, userId, userType, batchId, deptId ) | |
| SELECT | |
| $msgId, studentID, 'STUDENT', b.batchID, s.deptID | |
| FROM | |
| studentaccount s | |
| INNER JOIN | |
| batches b ON b.batchID = s.batchID | |
| WHERE | |
| student_lock = 0 | |
| AND b.batchStartYear IN ($messages->batchStartYears) | |
| AND b.courseTypeID IN ($messages->courseIds) | |
| ;"; | |
| if($messages->pushNotification) | |
| { | |
| $condition = " INNER JOIN | |
| batches b ON b.batchID = s.batchID | |
| WHERE | |
| student_lock = 0". | |
| ($messages->batchStartYears ? "AND b.batchStartYear IN ($messages->batchStartYears)":""). | |
| ($messages->courseIds ? "AND b.courseTypeID IN ($messages->courseIds)" : ""); | |
| $type = "STUDENT"; | |
| $this->callPushNotificationApi($type, $condition,$messages); | |
| } | |
| } | |
| try { | |
| $this->executeQuery($sqlStudents); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| // case RecipeintType::USER_GROUP: | |
| // $sql = "INSERT INTO messageRecipeints ( msgId, userId, userType ) | |
| // SELECT $msgId, IF(staff_id, staff_id, student_id) AS userId, IF(staff_id, 'FACULTY', 'STUDENT') AS userType FROM message_user_group_users "; | |
| // try { | |
| // $this->executeQuery($sql); | |
| // } catch (\Exception $e) { | |
| // throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| // } | |
| // break; | |
| } | |
| } | |
| /** | |
| * for compose message | |
| * @param Messages $messages | |
| * @return null|Object | |
| * @throws ProfessionalException | |
| */ | |
| public function composeMessage($messages) | |
| { | |
| $msgId = null; | |
| $sql = " | |
| INSERT INTO messages | |
| ( | |
| subject, | |
| content, | |
| senderType, | |
| senderId, | |
| sentDate, | |
| senderName, | |
| isNotice, | |
| eventDate, | |
| recType, | |
| createdBy, | |
| createdDate, | |
| updatedBy, | |
| updatedDate | |
| ) | |
| VALUES | |
| ( | |
| '$messages->subject', | |
| '$messages->content', | |
| '$messages->senderType', | |
| $messages->senderId, | |
| '" . date('Y-m-d H:i:s') . "', | |
| '$messages->senderName', | |
| '$messages->isNotice', | |
| '$messages->eventDate', | |
| '$messages->recType', | |
| $messages->createdBy, | |
| utc_timestamp(), | |
| $messages->updatedBy, | |
| utc_timestamp() | |
| )"; | |
| try { | |
| $msgId = $this->executeQueryForObject($sql, TRUE); | |
| if ($msgId && $messages->attachment != NULL && count($messages->attachment) > 0) { | |
| $this->addAttachment($msgId, $messages->attachment); | |
| } | |
| return $msgId; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * | |
| * @param int $msgId | |
| * @param array $files | |
| * @throws ProfessionalException | |
| */ | |
| private function addAttachment($msgId, $files) | |
| { | |
| $sql = "INSERT INTO messageAttachments (messageId, fileName, format, filePath) VALUES "; | |
| $fileList = []; | |
| foreach ($files as $file) { | |
| $fileList[] = "( $msgId, '$file[name]', '$file[format]', '$file[path]')"; | |
| } | |
| $sql .= implode(',', $fileList); | |
| if ($fileList) { | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| } | |
| /** | |
| * | |
| * @param MessageRequest $messageRequest | |
| * @return MessageResponse $messageResponse | |
| * @throws ProfessionalException | |
| * @throws \Exception | |
| */ | |
| public function searchMessages(MessageRequest $messageRequest) | |
| { | |
| $messageRequest = $this->realEscapeObject($messageRequest); | |
| $messageResponse = new MessageResponse(); | |
| $cond = ""; | |
| $sqlJoinQuery = ""; | |
| $sqlCountJoinQuery = ""; | |
| $sqlSel = ""; | |
| if ($messageRequest->userId) { | |
| $sqlSel = ", mr.isRead"; | |
| $cond = "AND m.senderId != $messageRequest->userId "; | |
| } | |
| $sqlCond = ""; | |
| if ((!empty($messageRequest->userId) && !empty($messageRequest->userType)) || $messageRequest->batchId || $messageRequest->subjectId) { | |
| $sqlCountJoinQuery = "INNER JOIN messageRecipeints mr ON mr.msgId = m.id "; | |
| $sqlJoinQuery = "INNER JOIN messageRecipeints mr ON mr.msgId = m.id "; | |
| } | |
| $sql = "SELECT DISTINCT m.*, if(ma.id,'1','0') as isFile $sqlSel from messages m | |
| $sqlJoinQuery | |
| LEFT JOIN messageAttachments ma ON ma.messageId = m.id WHERE m.id >0 $cond"; | |
| $sqlCount = "SELECT COUNT(DISTINCT m.id) AS totalCound from messages m | |
| $sqlCountJoinQuery | |
| LEFT JOIN messageAttachments ma ON ma.messageId = m.id WHERE m.id >0 $cond"; | |
| if ($messageRequest->senderId) { | |
| $sqlCond .= "AND m.senderId=$messageRequest->senderId "; | |
| } | |
| if ($messageRequest->senderType) { | |
| $sqlCond .= "AND m.senderType='$messageRequest->senderType' "; | |
| } | |
| if ($messageRequest->sentDate) { | |
| $sqlCond .= "AND m.sentDate='" . date('Y-m-d', strtotime($messageRequest->senderType)) . "' "; | |
| } | |
| if ($messageRequest->batchId) { | |
| $sqlCond .= "AND mr.batchId=$messageRequest->batchId "; | |
| } | |
| if ($messageRequest->subjectId) { | |
| $sqlCond .= "AND mr.subjectId in ($messageRequest->subjectId) "; | |
| } | |
| if ($messageRequest->subject) { | |
| $sqlCond .= "AND m.subject like '%$messageRequest->subject%'"; | |
| } | |
| if ($messageRequest->recType) { | |
| $sqlCond .= "AND m.recType= '$messageRequest->recType' "; | |
| } | |
| $userTypes = "'$messageRequest->userType'"; | |
| if ($messageRequest->userType == "FACULTY") { | |
| $sqlCheck = "SELECT isHOD, isPrincipal FROM staffaccounts WHERE staffID=$messageRequest->userId"; | |
| try { | |
| $staffDetails = $this->executeQueryForObject($sqlCheck); | |
| } catch (Exception $e) { | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| if ($staffDetails->isHOD) { | |
| $userTypes .= ",'HOD'"; | |
| } | |
| if ($staffDetails->isPrincipal) { | |
| $userTypes .= ",'PRINCIPAL'"; | |
| } | |
| $sqlCond .= "AND mr.userType IN ($userTypes)"; | |
| } else if (!empty($messageRequest->userType)) { | |
| $sqlCond .= "AND mr.userType=$userTypes"; | |
| } | |
| if ($messageRequest->userId) { | |
| $sqlCond .= "AND mr.userId=$messageRequest->userId "; | |
| } | |
| if ($messageRequest->sortBy) { | |
| $sqlCond .= "order by m.$messageRequest->sortBy $messageRequest->sortOrder "; | |
| } else { | |
| $sqlCond .= "order by sentDate $messageRequest->sortOrder "; | |
| } | |
| $sql .= $sqlCond . "LIMIT $messageRequest->startIndex,$messageRequest->endIndex";; | |
| $sqlCount .= $sqlCond; | |
| try { | |
| $messageResponse->totalRecords = $this->executeQueryForObject($sqlCount)->totalCound; | |
| if(!$messageRequest->onlyMessageCount) | |
| $messageResponse->messages = $this->executeQueryForList($sql); | |
| $index = 0; | |
| foreach($messageResponse->messages as $userMessage) { | |
| if(($userMessage->recType === "ALL_STUDENTS_OF_BATCHYEAR") && $_SESSION['studentID']){ | |
| $isSendToStudentBatch = $this->getMessageByBatchIdMsgIdAndUserId($userMessage->id,$_SESSION['studentID'],$_SESSION['batchID']); | |
| if(empty( $isSendToStudentBatch )) { | |
| foreach ($messageResponse->messages as $key => $object) { | |
| if ($key == $index) { | |
| unset($messageResponse->messages[$index]); | |
| } | |
| } | |
| } | |
| } | |
| $index ++; | |
| } | |
| $messageResponse->messages = array_values($messageResponse->messages); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $messageResponse->messages = $this->formatMessageContent($messageResponse->messages); | |
| return $messageResponse; | |
| } | |
| /** | |
| * | |
| * @param int $msgId | |
| * @return \stdClass $recList | |
| * @throws ProfessionalException | |
| */ | |
| public function getRecipent($msgId) | |
| { | |
| $sql = "select recType FROM messages WHERE id=$msgId"; | |
| try { | |
| $resType = $this->executeQueryForObject($sql)->recType; | |
| $recList = new \stdClass(); | |
| $recList->userType = ""; | |
| $recList->recDetails = []; | |
| if ($resType == RecipeintType::INDIVIDUAL || $resType == RecipeintType::CURRENT_STAFF ) { | |
| $sqlRec = "select userType, userId FROM messageRecipeints WHERE msgId=$msgId"; | |
| $recipeints = $this->executeQueryForList($sqlRec); | |
| if ($recipeints != NULL && count($recipeints) > 0) { | |
| foreach ($recipeints as $rec) { | |
| $recList->userType = $rec->userType; | |
| $recList->recDetails[] = $rec->userId; | |
| } | |
| } | |
| switch ($recList->userType) { | |
| case "STUDENT": | |
| $recDetails = StudentService::getInstance()->getStudentDetailsByIds($recList->recDetails); | |
| break; | |
| case "FACULTY": | |
| $recDetails = StaffService::getInstance()->getStaffByIds($recList->recDetails); | |
| break; | |
| } | |
| unset($recList->recDetails); | |
| $recList->recDetails = $recDetails; | |
| } else { | |
| $sqlRec = "select distinct mr.userType, mr.deptId, mr.batchId, mr.isSubbatch, m.senderType from messageRecipeints mr INNER JOIN messages m ON m.id=mr.msgId where msgId=$msgId"; | |
| $recDetails = $this->executeQueryForObject($sqlRec); | |
| $toList = ""; | |
| if ($recDetails != NULL && count($recDetails) > 0) { | |
| $recList->userType = $resType; | |
| $deptName = "Department : All"; | |
| $batchName = "Batch : All"; | |
| $subbatchName = "Subbatch : All"; | |
| if ($recDetails->deptId) { | |
| $deptName = "Department : "; | |
| $deptName .= DepartmentService::getInstance()->getDepartmentById($recDetails->deptId)->name; | |
| } | |
| if ($recDetails->isSubbatch) { | |
| if ($recDetails->batchId) { | |
| $batchName = "Batch : "; | |
| $subbatchName = "Subbatch : "; | |
| $subbatchDetails = BatchService::getInstance()->getSubBatchById($recDetails->batchId); | |
| $batchName .= $subbatchDetails->batchName; | |
| $subbatchName .= $subbatchDetails->name; | |
| } | |
| } else { | |
| if ($recDetails->batchId) { | |
| $batchName = "Batch : "; | |
| $batchName .= BatchService::getInstance()->getBatchById($recDetails->batchId)->name; | |
| } | |
| } | |
| if ($recDetails->senderType != 'STUDENT') { | |
| $recList->recDetails[] = $deptName . ", " . $batchName . ", " . $subbatchName; | |
| } | |
| } | |
| } | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $recList; | |
| } | |
| /** | |
| * get message details by id | |
| * @param int $msgId | |
| * @return object| $messageDetails | |
| * @throws ProfessionalException | |
| */ | |
| public function getMessageById($msgId) | |
| { | |
| $messageDetails = NULL; | |
| $sql = "select distinct m.*, if(ma.id,'1','0') as isFile from messages m LEFT JOIN messageAttachments ma ON ma.messageId = m.id WHERE m.id=$msgId"; | |
| try { | |
| $messageDetails = $this->executeQueryForObject($sql); | |
| $sqlRec = "SELECT count(isRead) as totalReaded FROM messageRecipeints WHERE isRead=1 AND msgId=$msgId"; | |
| $messageDetails->totalReaded = $this->executeQueryForObject($sqlRec)->totalReaded; | |
| $messageDetails->toList = $this->getRecipent($msgId); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $messageDetails; | |
| } | |
| /** | |
| * get message attached files | |
| * @param int $msgId | |
| * @return object $files | |
| * @throws ProfessionalException | |
| */ | |
| public function getAttachments($msgId) | |
| { | |
| $files = []; | |
| $sql = "select * from messageAttachments where messageId=$msgId"; | |
| try { | |
| $files = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $files; | |
| } | |
| /** | |
| * get inbox | |
| * @param int $userId | |
| * @param string $userType | |
| * @return object $messages | |
| * @throws ProfessionalException | |
| */ | |
| public function getMessageByUserId($userId, $userType) | |
| { | |
| $messages = []; | |
| $sql = "select distinct m.*, if(ma.id,'1','0') as isFile from messages m INNER JOIN messageRecipeints mr ON mr.msgId = m.id LEFT JOIN messageAttachments ma ON ma.messageId = m.id WHERE mr.userId=$userId AND userType='$userType'"; | |
| try { | |
| $messages = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $messages; | |
| } | |
| /** | |
| * delete message using id | |
| * @param array $msgIds | |
| * @return \com\linways\base\dto\MySqlResult | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteMessage($msgIds) | |
| { | |
| $sql = "DELETE FROM messages WHERE id IN(" . implode(',', $msgIds) . ")"; | |
| $sqlRec = "DELETE FROM messageRecipeints WHERE msgId IN(" . implode(',', $msgIds) . ")"; | |
| $sqlAtchmnt = "DELETE FROM messageAttachments WHERE messageId IN(" . implode(',', $msgIds) . ")"; | |
| try { | |
| $this->executeQuery($sqlRec); | |
| $this->executeQuery($sqlAtchmnt); | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * delete inbox message using id | |
| * @param array $msgIds | |
| * @param $userId | |
| * @param $userType | |
| * @return bool | |
| * @throws ProfessionalException | |
| */ | |
| public function deleteInboxMessage($msgIds, $userId, $userType) | |
| { | |
| $status = false; | |
| $msgIds = $this->realEscapeArray($msgIds); | |
| $userId = $this->realEscapeString($userId); | |
| $userType = $this->realEscapeString($userType); | |
| if (empty($userId)) | |
| throw new ProfessionalException(ProfessionalException::INVALID_USER_ID, "Invalid user id"); | |
| if (empty($userType)) | |
| throw new ProfessionalException(ProfessionalException::INVALID_USER_TYPE, "Invalid user type"); | |
| if (empty($msgIds)) | |
| throw new ProfessionalException("EMPTY_MESSAGE_ID_ARRAY", "Invalid message id"); | |
| $sqlCond = "'$userType'"; | |
| if ($userType == "FACULTY") { | |
| $sqlCheck = "SELECT isHOD, isPrincipal FROM staffaccounts WHERE staffID=$userId"; | |
| try { | |
| $staffDetails = $this->executeQueryForObject($sqlCheck); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if ($staffDetails->isHOD) { | |
| $sqlCond .= ",'HOD'"; | |
| } | |
| if ($staffDetails->isPrincipal) { | |
| $sqlCond .= ",'PRINCIPAL'"; | |
| } | |
| } | |
| $sqlRec = "DELETE FROM messageRecipeints WHERE msgId IN(" . implode(',', $msgIds) . ") AND userId=$userId AND userType IN ($sqlCond)"; | |
| try { | |
| $this->executeQuery($sqlRec); | |
| $status = true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $status; | |
| } | |
| /** | |
| * set read flag for a user | |
| * @param int $msdId | |
| * @param int $userId | |
| * @param string $userType | |
| * @throws ProfessionalException | |
| * @throws \Exception | |
| */ | |
| public function setIsRead($msdId, $userId, $userType) | |
| { | |
| $sqlCond = "'$userType'"; | |
| if ($userType == "FACULTY") { | |
| $sqlCheck = "SELECT isHOD, isPrincipal FROM staffaccounts WHERE staffID=$userId"; | |
| $staffDetails = $this->executeQueryForObject($sqlCheck); | |
| if ($staffDetails->isHOD) { | |
| $sqlCond .= ",'HOD'"; | |
| } | |
| if ($staffDetails->isPrincipal) { | |
| $sqlCond .= ",'PRINCIPAL'"; | |
| } | |
| } | |
| $sql = "UPDATE messageRecipeints SET isRead=1 WHERE msgId=$msdId AND userId=$userId AND userType IN ($sqlCond) "; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Returns number of unread message count | |
| * @param int $userId | |
| * @param string $userType | |
| * @return int | |
| * @throws ProfessionalException | |
| */ | |
| public function getUnReadedCount($userId, $userType) | |
| { | |
| $totalUnReadMsgCount = 0; | |
| $userId = $this->realEscapeString($userId); | |
| $userType = $this->realEscapeString($userType); | |
| $sqlCond = "'$userType'"; | |
| if ($userType == "FACULTY") { | |
| $sqlCheck = "SELECT isHOD, isPrincipal FROM staffaccounts WHERE staffID=$userId"; | |
| try { | |
| $staffDetails = $this->executeQueryForObject($sqlCheck); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if ($staffDetails->isHOD) { | |
| $sqlCond .= ",'HOD'"; | |
| } | |
| if ($staffDetails->isPrincipal) { | |
| $sqlCond .= ",'PRINCIPAL'"; | |
| } | |
| } | |
| $sql = "SELECT count(m.id) AS totalUnReadMsgCount FROM messages m | |
| INNER JOIN messageRecipeints mr ON m.id = mr.msgId | |
| WHERE mr.userId = $userId AND m.senderId <> mr.userId AND mr.isRead = 0 AND mr.userType IN ($sqlCond);"; | |
| try { | |
| $totalUnReadMsgCount = $this->executeQueryForObject($sql)->totalUnReadMsgCount; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $totalUnReadMsgCount; | |
| } | |
| /** | |
| * Returns count of messages for a single user | |
| * @param $userId | |
| * @param $userType | |
| * @return int | |
| * @throws ProfessionalException | |
| * @throws \Exception | |
| */ | |
| public function getCountOfMessages($userId, $userType) | |
| { | |
| $totalMessageCount = 0; | |
| $sqlCond = "'$userType'"; | |
| if ($userType == "FACULTY") { | |
| $sqlCheck = "SELECT isHOD, isPrincipal FROM staffaccounts WHERE staffID=$userId"; | |
| $staffDetails = $this->executeQueryForObject($sqlCheck); | |
| if ($staffDetails->isHOD) { | |
| $sqlCond .= ",'HOD'"; | |
| } | |
| if ($staffDetails->isPrincipal) { | |
| $sqlCond .= ",'PRINCIPAL'"; | |
| } | |
| } | |
| $sql = "SELECT count(id) AS messageCount FROM messageRecipeints WHERE userId=$userId AND userType IN ($sqlCond)"; | |
| try { | |
| $totalMessageCount = $this->executeQueryForObject($sql)->messageCount; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $totalMessageCount; | |
| } | |
| /** | |
| * Marking all messages as read by user id and user type | |
| * @param int $userId | |
| * @param string $userType | |
| * @return bool | |
| * @throws ProfessionalException | |
| * @throws \Exception | |
| */ | |
| public function markAllMessageAsRead($userId, $userType) | |
| { | |
| $status = false; | |
| $staffDetails = null; | |
| $userId = $this->realEscapeString($userId); | |
| $userType = $this->realEscapeString($userType); | |
| if (empty($userId)) | |
| throw new ProfessionalException(ProfessionalException::INVALID_USER_ID, "Invalid user id"); | |
| if (empty($userType)) | |
| throw new ProfessionalException(ProfessionalException::INVALID_USER_TYPE, "Invalid user type"); | |
| $sqlCond = "'$userType'"; | |
| if ($userType == "FACULTY") { | |
| $sqlCheck = "SELECT isHOD, isPrincipal FROM staffaccounts WHERE staffID=$userId"; | |
| try { | |
| $staffDetails = $this->executeQueryForObject($sqlCheck); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if ($staffDetails->isHOD) { | |
| $sqlCond .= ",'HOD'"; | |
| } | |
| if ($staffDetails->isPrincipal) { | |
| $sqlCond .= ",'PRINCIPAL'"; | |
| } | |
| } | |
| $sql = "UPDATE messageRecipeints SET isRead=1 WHERE userId=$userId AND isRead=0 AND userType IN ($sqlCond)"; | |
| try { | |
| $res = $this->executeQueryForObject($sql); | |
| $status = true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $status; | |
| } | |
| /** | |
| * Marking selected messages as read | |
| * @param $userId | |
| * @param $userType | |
| * @param $messageIds | |
| * @return bool|Object | |
| * @throws ProfessionalException | |
| */ | |
| public function markSelectedMessageAsRead($userId, $userType, $messageIds) | |
| { | |
| $status = false; | |
| $staffDetails = null; | |
| $userId = $this->realEscapeString($userId); | |
| $userType = $this->realEscapeString($userType); | |
| $messageIds = $this->realEscapeArray($messageIds); | |
| if (empty($userId)) | |
| throw new ProfessionalException(ProfessionalException::INVALID_USER_ID, "Invalid user id"); | |
| if (empty($userType)) | |
| throw new ProfessionalException(ProfessionalException::INVALID_USER_TYPE, "Invalid user type"); | |
| if (empty($messageIds)) | |
| throw new ProfessionalException("EMPTY_MESSAGE_ID_ARRAY", "Invalid message id"); | |
| $sqlCond = "'$userType'"; | |
| if ($userType == "FACULTY") { | |
| $sqlCheck = "SELECT isHOD, isPrincipal FROM staffaccounts WHERE staffID=$userId"; | |
| try { | |
| $staffDetails = $this->executeQueryForObject($sqlCheck); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| if ($staffDetails->isHOD) { | |
| $sqlCond .= ",'HOD'"; | |
| } | |
| if ($staffDetails->isPrincipal) { | |
| $sqlCond .= ",'PRINCIPAL'"; | |
| } | |
| } | |
| $messageIds = join(",", $messageIds); | |
| $sql = "UPDATE messageRecipeints SET isRead=1 WHERE userId=$userId AND isRead=0 AND msgId IN ($messageIds) AND userType IN ($sqlCond)"; | |
| try { | |
| $status = $this->executeQueryForObject($sql); | |
| $status = true; | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $status; | |
| } | |
| /** | |
| * removes html special characters and tags from message content and reducing length of message content | |
| * @param $messages | |
| * @return mixed | |
| */ | |
| private function formatMessageContent($messages) | |
| { | |
| foreach ($messages as $message) { | |
| $content = $message->content; | |
| $content = strip_tags(html_entity_decode($content)); | |
| $message->content = substr($content, 0, 100); | |
| } | |
| return $messages; | |
| } | |
| /** | |
| * Create user group by giving a group name and the staff who creates that group | |
| * @param String $userGroupName | |
| * @param Integer $staffId | |
| * @return Integer $groupId | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function createUserGroup($userGroupName, $staffId) | |
| { | |
| $userGroupName = $this->realEscapeString($userGroupName); | |
| $staffId = $this->realEscapeString($staffId); | |
| $groupId = null; | |
| $sql = "INSERT INTO message_user_group (name, staff_id, createdBy, createdDate, updatedBy, updatedDate) VALUES ( '" . $userGroupName . "', " . $staffId . ", " . $staffId . ", utc_timestamp(), " . $staffId . ", utc_timestamp() )"; | |
| try { | |
| $groupId = $this->executeQuery($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $groupId; | |
| } | |
| /** | |
| * Update user group by giving a group name and the staff who creates that group | |
| * @param Integer $groupId | |
| * @param String $userGroupName | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function updateUserGroup($groupId, $userGroupName) | |
| { | |
| $groupId = $this->realEscapeString($groupId); | |
| $userGroupName = $this->realEscapeString($userGroupName); | |
| $sql = "UPDATE message_user_group SET name = '" . $userGroupName . "', updatedDate = utc_timestamp() WHERE id = '$groupId' "; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Delete message user group by groupId | |
| * @param Integer $groupId | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function deleteMessageUserGroupByGroupId($groupId) | |
| { | |
| $groupId = $this->realEscapeString($groupId); | |
| $sql = "DELETE FROM message_user_group WHERE id = '$groupId' "; | |
| try { | |
| $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Add users to message-user-group | |
| * @param Integer $userGroupId | |
| * @param String $userType | |
| * @param Array $userIdArr | |
| * @param Integer $createdBy | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function addUsersToUserGroup($userGroupId, $userType, $userIdArr, $createdBy) | |
| { | |
| $userGroupId = $this->realEscapeString($userGroupId); | |
| $userType = $this->realEscapeString($userType); | |
| $userIdArr = $this->realEscapeArray($userIdArr); | |
| $values = []; | |
| if (!empty ($userIdArr)) { | |
| $sql = "INSERT INTO message_user_group_user ( group_id, staff_id, student_id, createdBy, createdDate, updatedBy, updatedDate ) VALUES "; | |
| foreach ($userIdArr as $userId) { | |
| if ($userType == "INDIVIDUAL_STAFF") { | |
| $values[] = "( '$userGroupId', '$userId', NULL, " . $createdBy . ", utc_timestamp(), " . $createdBy . ", utc_timestamp() )"; | |
| } else if ($userType == "INDIVIDUAL_STUDENT") { | |
| $values[] = "( '$userGroupId', NULL, '$userId', " . $createdBy . ", utc_timestamp(), " . $createdBy . ", utc_timestamp() )"; | |
| } | |
| } | |
| $sql = $sql . implode(",", $values) . " ON DUPLICATE KEY UPDATE updatedBy = VALUES(updatedBy) "; | |
| try { | |
| return $this->executeQuery($sql, true); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } else { | |
| throw new ProfessionalException($e->getCode(), "NO_GROUP_MEMBERS_SELECTED"); | |
| } | |
| return false; | |
| } | |
| /** | |
| * Update users in the message-user-group | |
| * @param Integer $groupId | |
| * @param String $userType | |
| * @param Array $userIdArr | |
| * @param Integer $createdBy | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function updateMessageUserGroupUser($groupId, $userType, $userIdArr, $createdBy) | |
| { | |
| $groupId = $this->realEscapeString($groupId); | |
| $userType = $this->realEscapeString($userType); | |
| $userIdArr = $this->realEscapeArray($userIdArr); | |
| $createdBy = $this->realEscapeArray($createdBy); | |
| try { | |
| $this->deleteUsersFromMessageUserGroup($groupId); | |
| $this->addUsersToUserGroup($groupId, $userType, $userIdArr, $createdBy); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| /** | |
| * Delete users from message user group | |
| * @param Integer $groupId | |
| * @param String $userIds | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function deleteUsersFromMessageUserGroup($groupId, $userIds = NULL) | |
| { | |
| // Delete by using $userIds has to be handled later | |
| $groupId = $this->realEscapeString($groupId); | |
| $sql = "DELETE FROM message_user_group_user WHERE group_id = '$groupId' "; | |
| try { | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return false; | |
| } | |
| /** | |
| * Get all notices send to a faculty | |
| * @param int $userId | |
| * @param string $userType | |
| * @return object $notices | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getNoticesByUserId($userId, $userType,$sortBy, $upcomingEventsOnly = NULL) | |
| { | |
| $userId = $this->realEscapeString($userId); | |
| $userType = $this->realEscapeString($userType); | |
| $upcomingEventsOnly = $this->realEscapeString($upcomingEventsOnly); | |
| $sortBy = $this->realEscapeString($sortBy); | |
| if(!$sortBy){ | |
| $sortBy = 'eventDate'; | |
| } | |
| $condition = null; | |
| if ($upcomingEventsOnly) { | |
| $condition .= " AND eventDate >= '" . date("Y-m-d") . "' "; | |
| } | |
| $notices = []; | |
| $sql = "SELECT DISTINCT msg.id, msg.subject, msg.content, mr.isRead, mr.readedTime, msg.eventDate, msg.senderName, msg.sentDate, msg.senderType, IF(ma.id,'1','0') as isFile from messages msg INNER JOIN messageRecipeints mr ON mr.msgId = msg.id LEFT JOIN messageAttachments ma ON ma.messageId = msg.id WHERE msg.isNotice = 1 AND mr.userId = '$userId' AND mr.userType = '$userType' $condition ORDER BY $sortBy DESC"; | |
| try { | |
| $notices = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $notices; | |
| } | |
| /** | |
| * Get notice by msgid and user details | |
| * @param int $userId | |
| * @param string $userType | |
| * @return object $notices | |
| * @throws ProfessionalException | |
| * @author Vishnu M | |
| */ | |
| public function getNoticeByUserAndId($userId, $userType, $noticeId) | |
| { | |
| $userId = $this->realEscapeString($userId); | |
| $userType = $this->realEscapeString($userType); | |
| $noticeId = $this->realEscapeString($noticeId); | |
| $notice = null; | |
| $sql = "SELECT DISTINCT msg.id, msg.subject, msg.content, mr.isRead, mr.readedTime, msg.eventDate, msg.senderName, msg.sentDate, msg.senderType, IF(ma.id,'1','0') as isFile from messages msg INNER JOIN messageRecipeints mr ON mr.msgId = msg.id LEFT JOIN messageAttachments ma ON ma.messageId = msg.id WHERE msg.isNotice = 1 AND mr.userId = '$userId' AND mr.userType = '$userType' AND msg.id = '$noticeId' ORDER BY msg.eventDate DESC, msg.sentDate DESC"; | |
| try { | |
| $notice = $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $notice; | |
| } | |
| public function getMessageUserGroupsByStaffId($staffId) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $groups = []; | |
| $sql = "SELECT id, name, staff_id AS staffId,DATE_FORMAT(CONVERT_TZ(createdDate,'+00:00', @@global .time_zone), '%Y-%m-%d %H:%i:%s') as createdDate | |
| FROM message_user_group WHERE staff_id = '$staffId' "; | |
| try { | |
| $groups = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $groups; | |
| } | |
| public function getMessageUserGroupUsersByGroupId($groupId) | |
| { | |
| $groupId = $this->realEscapeString($groupId); | |
| $users = []; | |
| $sql = "SELECT id, group_id AS groupId, IF(staff_id, staff_id, student_id) AS userId, IF(staff_id, 'FACULTY', 'STUDENT') AS userType FROM message_user_group_user WHERE group_id = '$groupId' "; | |
| try { | |
| $users = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $users; | |
| } | |
| public function getMessageUserGroupAndUsersByStaffId($staffId, $groupId = NULL) | |
| { | |
| $staffId = $this->realEscapeString($staffId); | |
| $groupId = $this->realEscapeString($groupId); | |
| $condition = NULL; | |
| if ($groupId) { | |
| $condition .= " AND mug.id = '$groupId' "; | |
| } | |
| $users = []; | |
| $sql = "SELECT mugu.id, mugu.group_id AS groupId, mug.name as groupName, mug.staff_id as staffId, IF(mugu.staff_id, mugu.staff_id, mugu.student_id) AS userId, IF(mugu.staff_id, 'FACULTY', 'STUDENT') AS userType, IF(mugu.staff_id, (SELECT staffName FROM staffaccounts WHERE staffID = mugu.staff_id), (SELECT studentName FROM studentaccount WHERE studentID = mugu.student_id)) AS userName, mug.createdDate FROM message_user_group_user mugu INNER JOIN message_user_group mug ON (mug.id = mugu.group_id) WHERE mug.staff_id = '$staffId' $condition ORDER BY mug.createdDate ASC"; | |
| try { | |
| $users = $this->executeQueryForList($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| return $users; | |
| } | |
| public function getAllRecipientPhoneNumber($messages) | |
| { | |
| $messages = $this->realEscapeObject($messages); | |
| $studentList = []; | |
| $staffList = []; | |
| switch ($messages->recType) | |
| { | |
| case RecipeintType::ALL: | |
| $sqlStudents = "SELECT studentID as userId, studentName as name, studentPhone as phone,parentPhone,studentEmail as email,'STUDENT' as userType FROM studentaccount WHERE student_lock=0 "; | |
| $sqlStaff = "SELECT staffID as userId,staffPhone as phone,staffName as name,staffEmail as email,'STAFF' as userType FROM staffaccounts WHERE isResigned=0 AND staffID !=$messages->senderId "; | |
| if($messages->deptId){ | |
| $sqlStudents .=" AND deptID=$messages->deptId"; | |
| $sqlStaff .=" AND deptID=$messages->deptId"; | |
| } | |
| try{ | |
| $studentList = $this->executeQueryForList($sqlStudents); | |
| $staffList = $this->executeQueryForList($sqlStaff); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_BATCH: | |
| $sqlStudents="SELECT studentID as userId,studentName as name,studentPhone as phone,parentPhone,studentEmail as email,'STUDENT' as userType FROM studentaccount WHERE student_lock=0 AND batchID=$messages->batchId AND deptID=$messages->deptId"; | |
| $sqlStaff ="SELECT distinct st.staffID as userId,st.staffPhone as phone,st.staffName as name,st.staffEmail as email,'STAFF' as userType FROM staffaccounts st INNER JOIN sbs_relation sr ON st.staffID = sr.staffID INNER JOIN batches b ON b.batchID = sr.batchID AND b.semID = sr.semID WHERE st.isResigned=0 AND st.deptID=$messages->deptId AND sr.batchID = $messages->batchId AND st.staffID !=$messages->senderId"; | |
| try{ | |
| $studentList = $this->executeQueryForList($sqlStudents); | |
| $staffList = $this->executeQueryForList($sqlStaff); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_SUBBATCH: | |
| $sqlStudents="SELECT distinct sa.studentID as userId,sa.studentName as name,sa.studentPhone as phone,sa.parentPhone,sa.studentEmail as email,'STUDENT' as userType FROM studentaccount sa INNER JOIN subbatches sub ON sub.batchID =sa.batchID INNER JOIN subbatch_student ss ON sub.subbatchID=ss.subbatchID AND sa.studentID =ss.studentID WHERE sub.subbatchID=$messages->batchId"; | |
| $sqlStaff ="SELECT distinct st.staffID as userId,st.staffPhone as phone,st.staffName as name,st.staffEmail as email,'STAFF' as userType FROM staffaccounts st INNER JOIN sbs_relation sr ON st.staffID = sr.staffID INNER JOIN subbatch_sbs ss ON ss.sbsID=sr.sbsID WHERE st.isResigned=0 AND st.deptID=$messages->deptId AND ss.subbatchID=$messages->batchId AND st.staffID !=$messages->senderId"; | |
| try{ | |
| $studentList = $this->executeQueryForList($sqlStudents); | |
| $staffList = $this->executeQueryForList($sqlStaff); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_FACULTY: | |
| $sqlStaff ="SELECT staffID as userId,staffPhone as phone,staffEmail as email,staffName as name,'STAFF' as userType FROM staffaccounts WHERE isResigned=0 AND staffID !=$messages->senderId"; | |
| try{ | |
| $staffList = $this->executeQueryForList($sqlStaff); | |
| } | |
| catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_DEPT_FACULTY: | |
| $sqlStaff ="SELECT staffID as userId,staffPhone as phone,staffName as name,staffEmail as email,'STAFF' as userType FROM staffaccounts WHERE isResigned=0 AND deptID=$messages->deptId AND staffID !=$messages->senderId"; | |
| try{ | |
| $staffList = $this->executeQueryForList($sqlStaff); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_BATCH_FACULTY: | |
| $sqlStaff ="SELECT distinct st.staffID as userId,st.staffPhone as phone,st.staffName as name,st.staffEmail as email,'STAFF' as userType FROM staffaccounts st INNER JOIN sbs_relation sr ON st.staffID = sr.staffID INNER JOIN batches b ON b.batchID = sr.batchID AND b.semID = sr.semID WHERE st.isResigned=0 AND st.deptID=$messages->deptId AND sr.batchID = $messages->batchId AND st.staffID !=$messages->senderId"; | |
| try{ | |
| $staffList = $this->executeQueryForList($sqlStaff); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_SUBBATCH_FACULTY: | |
| $sqlStaff ="SELECT distinct st.staffID as userId,st.staffName as name,st.staffPhone as phone,st.staffEmail as email,'STAFF' as userType FROM staffaccounts st INNER JOIN sbs_relation sr ON st.staffID = sr.staffID INNER JOIN subbatch_sbs ss ON ss.sbsID=sr.sbsID WHERE st.isResigned=0 AND st.deptID=$messages->deptId AND ss.subbatchID=$messages->batchId AND st.staffID !=$messages->senderId"; | |
| try{ | |
| $staffList = $this->executeQueryForList($sqlStaff); | |
| } | |
| catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_STUDENTS: | |
| $sqlStudents = "SELECT studentID as userId,studentPhone as phone, studentName as name, parentPhone ,studentEmail as email,'STUDENT' as userType FROM studentaccount WHERE student_lock=0"; | |
| try{ | |
| $studentList = $this->executeQueryForList($sqlStudents); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_BATCH_STUDENT: | |
| $sqlStudents = "SELECT studentID as userId,studentPhone as phone, studentName as name,parentPhone,studentEmail as email,'STUDENT' as userType FROM studentaccount WHERE student_lock=0 AND batchID=$messages->batchId AND deptID=$messages->deptId"; | |
| try{ | |
| $studentList = $this->executeQueryForList($sqlStudents); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_DEPT_STUDENT: | |
| $sqlStudents = "SELECT studentID as userId,studentName as name,studentPhone as phone,parentPhone,studentEmail as email,'STUDENT' as userType FROM studentaccount WHERE student_lock=0 AND deptID=$messages->deptId"; | |
| try{ | |
| $studentList = $this->executeQueryForList($sqlStudents); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::ALL_SUBBATCH_STUDENT: | |
| $sqlStudents = "SELECT distinct sa.studentID as userId,sa.studentName as name,sa.studentPhone as phone,sa.parentPhone,sa.studentEmail as email,'STUDENT' as userType FROM studentaccount sa INNER JOIN subbatches sub ON sub.batchID =sa.batchID INNER JOIN subbatch_student ss ON sub.subbatchID=ss.subbatchID AND sa.studentID =ss.studentID WHERE sub.subbatchID=$messages->batchId"; | |
| try{ | |
| $studentList = $this->executeQueryForList($sqlStudents); | |
| } | |
| catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::INDIVIDUAL: | |
| if($messages->recipeint !=NULL && count($messages->recipeint) > 0) | |
| { | |
| foreach ($messages->recipeint as $user) | |
| { | |
| if($user->userType == "FACULTY"){ | |
| $staff[] = $user->userId; | |
| }elseif ($user->userType == "STUDENT"){ | |
| $student[] = $user->userId; | |
| } | |
| } | |
| } | |
| $sqlStudents = "SELECT studentID as userId,studentName as name,studentPhone as phone,parentPhone,studentEmail as email,'STUDENT' as userType FROM studentaccount WHERE student_lock=0 and studentID in (".implode(',',$student).");"; | |
| $sqlStaff = "SELECT staffID as userId,staffPhone as phone,staffEmail as email,'STAFF' as userType FROM staffaccounts where isResigned=0 and staffID in (".implode(',',$staff).");"; | |
| $studentList = !empty($student)?$this->executeQueryForList($sqlStudents):[]; | |
| $staffList = !empty($staff)?$this->executeQueryForList($sqlStaff):[]; | |
| break; | |
| case RecipeintType::HOD: | |
| $sqlStaff ="SELECT staffID as userId,staffPhone as phone,staffName as name,staffEmail as email,'STAFF' as userType FROM staffaccounts WHERE isResigned=0 AND deptID = $messages->deptId AND isHOD >0"; | |
| try{ | |
| $staffList = $this->executeQueryForList($sqlStaff); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case RecipeintType::PRINCIPAL: | |
| $sqlStaff ="SELECT staffID as userId,staffPhone as phone,staffName as name,parentPhone,staffEmail as email,'STAFF' as userType FROM staffaccounts WHERE isResigned=0 AND isPrincipal >0"; | |
| try{ | |
| $staffList = $this->executeQueryForList($sqlStaff); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| if(!empty($staffList) && !empty($studentList)) | |
| { | |
| $recipientDetails = array_merge($staffList,$studentList); | |
| } | |
| else { | |
| $recipientDetails = !empty($staffList)?$staffList:$studentList; | |
| } | |
| return $recipientDetails; | |
| } | |
| public function searchForStudentMessages(MessageRequest $messageRequest) | |
| { | |
| $messageRequest = $this->realEscapeObject($messageRequest); | |
| $messageResponse = new MessageResponse(); | |
| $sqlCond .= $messageRequest->senderId?" AND m.senderId=$messageRequest->senderId ":""; | |
| $sqlCond .= $messageRequest->senderType?" AND m.senderType='$messageRequest->senderType' ":""; | |
| $sqlCond .= $messageRequest->sentDate?" AND m.sentDate='" . date('Y-m-d', strtotime($messageRequest->senderType)) . "' ":""; | |
| $sqlCond .= $messageRequest->subject?" AND m.subject like '%$messageRequest->subject%'":""; | |
| $sqlCond .= $messageRequest->recType?" AND m.recType= '$messageRequest->recType' ":""; | |
| $sqlmrCond .= $messageRequest->userId?" AND mr.userId=$messageRequest->userId AND mr.userId <> m.senderId ":""; | |
| $userTypes = "'$messageRequest->userType'"; | |
| if ($messageRequest->userType == "FACULTY") { | |
| $userTypes .= $this->getStaffRollByStaffId($messageRequest->userId); | |
| $sqlmrCond .= " AND mr.userType IN ($userTypes)"; | |
| } else if (!empty($messageRequest->userType)) { | |
| $sqlmrCond .= " AND mr.userType=$userTypes"; | |
| } | |
| $batchCond .= $messageRequest->batchId?" AND std.batchID = " .$messageRequest->batchId:""; | |
| $sqlCond .= $messageRequest->sortBy?" order by m.$messageRequest->sortBy $messageRequest->sortOrder ":" order by sentDate $messageRequest->sortOrder "; | |
| $limitCond .= " LIMIT $messageRequest->startIndex,$messageRequest->endIndex"; | |
| $select[] = "m.*"; | |
| $select[] = "IF(ma.id, '1', '0') AS isFile"; | |
| if($batchCond) | |
| { | |
| $joinTable [] = "INNER JOIN studentaccount std ON std.studentID = m.senderId"; | |
| $joinTable [] = "INNER JOIN batches bat ON bat.batchID = std.batchID"; | |
| } | |
| if($sqlmrCond) | |
| { | |
| $select[] = "mr.isRead"; | |
| $joinTable [] = "INNER JOIN messageRecipeints mr ON m.id = mr.msgId"; | |
| } | |
| $sqlQuery = " | |
| FROM messages m ". implode(" ",$joinTable) ." | |
| LEFT JOIN messageAttachments ma ON ma.messageId = m.id | |
| WHERE m.senderType = 'STUDENT' AND m.recType <> 'CURRENT_STAFF' "; | |
| $countQuery = "SELECT COUNT(DISTINCT m.id) AS totalCound ".$sqlQuery.$sqlmrCond.$batchCond.$sqlCond; | |
| $dataQuery = "SELECT ". implode(",",$select).$sqlQuery.$sqlmrCond.$batchCond.$sqlCond.$limitCond; | |
| try { | |
| $messageResponse->totalRecords = $this->executeQueryForObject($countQuery)->totalCound; | |
| if(!$messageRequest->onlyMessageCount) | |
| $messageResponse->messages = $this->executeQueryForList($dataQuery); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| $messageResponse->messages = $this->formatMessageContent($messageResponse->messages); | |
| return $messageResponse; | |
| } | |
| public function getStaffRollByStaffId($staffId) | |
| { | |
| $userTypes = ""; | |
| $sqlCheck = "SELECT isHOD, isPrincipal FROM staffaccounts WHERE staffID=".$staffId; | |
| try { | |
| $staffDetails = $this->executeQueryForObject($sqlCheck); | |
| } catch (Exception $e) { | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| if ($staffDetails->isHOD) { | |
| $userTypes .= ",'HOD'"; | |
| } | |
| if ($staffDetails->isPrincipal) { | |
| $userTypes .= ",'PRINCIPAL'"; | |
| } | |
| return $userTypes; | |
| } | |
| public function purifyMessageContent($messages){ | |
| $config = HTMLPurifier_Config::createDefault(); | |
| $config->set('Cache.DefinitionImpl', null); | |
| $purifier = new HTMLPurifier($config); | |
| if (!empty($messages->subject)) { | |
| $messages->subject = strip_tags($messages->subject); | |
| $messages->subject = $purifier->purify($messages->subject); | |
| } | |
| if (!empty($messages->content)){ | |
| $messages->content = $purifier->purify($messages->content); | |
| $messages->content = str_replace(array("\n","\r"), '', $messages->content); | |
| } | |
| return $messages; | |
| } | |
| public function getMessageReadReceiptsById($messageId){ | |
| $sql = "SELECT id,userId,userType FROM messageRecipeints WHERE isRead = 1 AND msgId = $messageId"; | |
| try { | |
| $userDetails = $this->executeQueryForList($sql); | |
| $result = []; | |
| foreach($userDetails as $user){ | |
| switch ($user->userType) | |
| { | |
| case 'STUDENT': | |
| $sqlStudents = "SELECT s.regNo,s.studentName as name,b.batchName FROM studentaccount s inner join batches b on b.batchID = s.batchID WHERE studentID = $user->userId"; | |
| try{ | |
| $studentDetails = $this->executeQueryForObject($sqlStudents); | |
| array_push($result,$studentDetails); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case 'FACULTY': | |
| $sqlStaff = "SELECT s.staffID ,s.staffName as name ,d.deptName FROM staffaccounts s inner join department d on d.deptID = s.deptID WHERE staffID=$user->userId"; | |
| try{ | |
| $staffDetails = $this->executeQueryForObject($sqlStaff); | |
| array_push($result,$staffDetails); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| case 'HOD': | |
| $sqlStaff = "SELECT s.staffID ,s.staffName as name ,d.deptName FROM staffaccounts s inner join department d on d.deptID = s.deptID WHERE staffID=$user->userId AND isHOD >0 AND isResigned != 1"; | |
| try{ | |
| $staffDetails = $this->executeQueryForObject($sqlStaff); | |
| array_push($result,$staffDetails); | |
| }catch (\Exception $e){ | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| break; | |
| } | |
| } | |
| return $result; | |
| } catch (Exception $e) { | |
| throw new ProfessionalException($e->getMessage(), $e->getCode()); | |
| } | |
| } | |
| public function unsendMessage($messageId){ | |
| $sql = "DELETE FROM messages WHERE id = $messageId"; | |
| $sqlRec = "DELETE FROM messageRecipeints WHERE msgId = $messageId "; | |
| $sqlAtchmnt = "DELETE FROM messageAttachments WHERE messageId = $messageId"; | |
| try { | |
| $this->executeQuery($sqlRec); | |
| $this->executeQuery($sqlAtchmnt); | |
| return $this->executeQuery($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function getMessageByBatchIdMsgIdAndUserId($msgId,$userId,$batchId){ | |
| $sql = "select * from messageRecipeints where msgId=$msgId and userId =$userId and batchID =$batchId;"; | |
| try { | |
| return $this->executeQueryForObject($sql); | |
| } catch (\Exception $e) { | |
| throw new ProfessionalException($e->getCode(), $e->getMessage()); | |
| } | |
| } | |
| public function callPushNotificationApi($type,$conditions,$messages) | |
| { | |
| $getRedirectionUrl = "SELECT value FROM settings WHERE name = 'V4_REDIRECTION_URL'"; | |
| $redirectionUrl = $this->executeQueryForObject($getRedirectionUrl)->value; | |
| $authStaffSql = "SELECT staffEmail FROM staffaccounts WHERE staffID = ".$_SESSION['staffID']; | |
| $authEmail = $this->executeQueryForObject($authStaffSql)->staffEmail; | |
| $randomKey = SecurityUtils::getRandomString(); | |
| GLOBAL $PUSH_NOTIFICATION_KEY,$PUSH_NOTIFICATION_SECRET_KEY; | |
| $sqlStudentsMain = "SELECT studentEmail ,studentEmail2 from studentaccount s"; | |
| $sqlStaffsMain = "SELECT staffEmail from staffaccounts s "; | |
| $pushNotificationV4 = new PushNotificationV4(); | |
| if($type == "STUDENT") | |
| { | |
| $sqlgetStudents = $sqlStudentsMain." ".$conditions; | |
| $allStudentEmailIds = $this->executeQueryForList($sqlgetStudents); | |
| $studentEmailIds = []; | |
| foreach ($allStudentEmailIds as $k => $mail) { | |
| if(trim($mail->studentEmail) != "") | |
| { | |
| $studentEmailIds[] = $mail->studentEmail; | |
| } | |
| $pushNotificationV4->studentEmailIds = $studentEmailIds; | |
| } | |
| } | |
| else{ | |
| $sqlgetStaffs = $sqlStaffsMain.$conditions; | |
| $allStaffEmailIds = $this->executeQueryForList($sqlgetStaffs); | |
| $staffEmailIds = []; | |
| foreach ($allStaffEmailIds as $k => $mail) { | |
| if(trim($mail->staffEmail) != "" && $k < 5000) | |
| { | |
| $staffEmailIds[] = $mail->staffEmail; | |
| } | |
| } | |
| $pushNotificationV4->staffEmailIds = $staffEmailIds; | |
| } | |
| $pushNotificationV4->authEmail = $authEmail; | |
| $pushNotificationV4->authUserType = 'STAFF'; | |
| $pushNotificationV4->notificationTitle = "You have a message"; | |
| $pushNotificationV4->notificationBody = $messages->senderName; | |
| $initialRequest = json_encode($pushNotificationV4); | |
| $pushNotificationV4->request = $pushNotificationV4; | |
| $setReguest = []; | |
| $setReguest[] = $initialRequest; | |
| $setReguest[] = $randomKey; | |
| $setReguest[] = $PUSH_NOTIFICATION_KEY; | |
| $hash = hash_hmac('sha256',implode("|",$setReguest), $PUSH_NOTIFICATION_SECRET_KEY); | |
| $setReguest[] = $hash; | |
| $request = implode("|",$setReguest); | |
| $body = new \stdClass(); | |
| $body->request = $request; | |
| $exteralApi = new ExteralApi(); | |
| $exteralApi->URL = $redirectionUrl."/notification/keyBasedApi/v1/external/send-pushnotification-using-emailids"; | |
| $exteralApi->body = $body; | |
| RequestUtil::callAPI($exteralApi); | |
| } | |
| } | |
| ?> |