|
Server IP : 172.26.0.195 / Your IP : 18.188.91.223 Web Server : Apache System : Linux 43-205-77-33.cprapid.com 3.10.0-1160.119.1.el7.tuxcare.els2.x86_64 #1 SMP Mon Jul 15 12:09:18 UTC 2024 x86_64 User : jnclnmuac ( 1026) PHP Version : 8.0.30 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON Directory (0755) : /home/jnclnmuac/public_html/web/../grievance/application/core/../models/ |
| [ Home ] | [ C0mmand ] | [ Upload File ] |
|---|
<?php
/**
* Model Class For Handling All DB Operations Related To Grievance
*
* @author Softpro India Pvt. Ltd.
*/
defined('BASEPATH') OR exit('No direct script access allowed');
class GrievanceManagement extends CI_Model {
function __construct() {
parent::__construct();
$this->load->database();
}
function getTotalNonDeletedGrievanceOfUser($cau_id) {
$this->db->select("count(*) myTotGrievances");
$this->db->from('grievance_mst');
$this->db->where('gm_delete_status', 'F');
$this->db->where('gm_submitted_by', $cau_id);
return $this->db->get();
}
function getTotalPendingGrievancesOfUser($cau_id) {
$this->db->select("count(*) myTotPendingGrievances");
$this->db->from('grievance_mst');
$this->db->where('gm_delete_status', 'F');
$this->db->where("gm_current_status IN ('P','IP','A','V')");
$this->db->where('gm_submitted_by', $cau_id);
return $this->db->get();
}
function getTotalResolvedGrievanceOfUser($cau_id) {
$this->db->select("count(*) myTotResolvedGrievances");
$this->db->from('grievance_mst');
$this->db->where('gm_delete_status', 'F');
$this->db->where("gm_current_status IN ('R')");
$this->db->where('gm_submitted_by', $cau_id);
return $this->db->get();
}
function getTotalPostedGrievances($clg_id) {
$this->db->select("count(*) totalPostedGrievances");
$this->db->from('grievance_mst GM');
$this->db->join('college_admins_and_users CAU', 'GM.gm_submitted_by = CAU.cau_id');
$this->db->where('CAU.clg_id', $clg_id);
return $this->db->get();
}
function getTotalLockedGrievancesByUser($cau_id) {
$this->db->select("count(*) totalLockedGrievances");
$this->db->from('grievance_mst GM');
$this->db->join('college_admins_and_users CAU', 'GM.gm_locked_by = CAU.cau_id');
$this->db->where('CAU.cau_id', $cau_id);
$this->db->where("GM.gm_current_status NOT IN ('R','DL')");
return $this->db->get();
}
function getTotalPendingGrievancesByUser($cau_id) {
$sql = "select count(*) totalPendingGrievances from grievance_mst GM, grievance_cat_mst GCM where GM.gcm_id = GCM.gcm_id AND "
. "GM.gcm_id IN (select gcm_id from gcell_member_allot GMA where GMA.cau_id = " . $cau_id . ") ORDER by "
. "GM.gm_submitted_on desc";
return $this->db->query($sql);
}
function getTotalResolvedGrievancesByUser($cau_id) {
$this->db->select("count(*) totalResolvedGrievances");
$this->db->from('grievance_mst GM');
$this->db->join('college_admins_and_users CAU', 'GM.gm_locked_by = CAU.cau_id');
$this->db->where('CAU.cau_id', $cau_id);
$this->db->where("GM.gm_current_status IN ('R')");
return $this->db->get();
}
function createNewGrievance(array $newGrievanceInfo) {
$this->db->insert('grievance_mst', $newGrievanceInfo);
return $this->db->insert_id();
}
function updateGrievanceInfo(array $grievanceUpdatedInfo) {
$this->db->where('gm_id', $grievanceUpdatedInfo['gm_id']);
return $this->db->update('grievance_mst', $grievanceUpdatedInfo);
}
function getAllGrievances($status = '') {
$this->db->select("*");
$this->db->from('grievance_mst GM');
$this->db->join('grievance_cat_mst GCM', 'GM.gcm_id = GCM.gcm_id');
if ($status != '') {
if ($status == 'R') {
$this->db->where('GM.gm_current_status', $status);
} else {
$this->db->where("GM.gm_current_status NOT IN ('R','DL')");
}
}
$this->db->order_by('GM.gm_submitted_on desc');
return $this->db->get();
}
function getGrvncByUserForListing($cau_id, $status = '') {
$this->db->select("*");
$this->db->from('grievance_mst GM');
$this->db->join('grievance_cat_mst GCM', 'GM.gcm_id = GCM.gcm_id');
$this->db->where('gm_submitted_by', $cau_id);
$this->db->where('gm_delete_status', 'F');
if ($status != '') {
if ($status == 'R') {
$this->db->where('GM.gm_current_status', $status);
} else {
$this->db->where("GM.gm_current_status NOT IN ('R','DL')");
}
}
$this->db->order_by('GM.gm_submitted_on desc');
return $this->db->get();
}
function getAllotteGrievancesOnly($cau_id, $status = '') {
if ($status != '') {
if ($status == 'R') {
$sql = "select * from grievance_mst GM, grievance_cat_mst GCM where GM.gcm_id = GCM.gcm_id AND "
. "GM.gcm_id IN (select gcm_id from gcell_member_allot GMA where GMA.cau_id = " . $cau_id . ") AND"
. " GM.gm_current_status = 'R' ORDER by GM.gm_submitted_on desc";
} else {
$sql = "select * from grievance_mst GM, grievance_cat_mst GCM where GM.gcm_id = GCM.gcm_id AND "
. "GM.gcm_id IN (select gcm_id from gcell_member_allot GMA where GMA.cau_id = " . $cau_id . ") AND"
. " GM.gm_current_status NOT IN ('R','DL') ORDER by GM.gm_submitted_on desc";
}
} else {
$sql = "select * from grievance_mst GM, grievance_cat_mst GCM where GM.gcm_id = GCM.gcm_id AND "
. "GM.gcm_id IN (select gcm_id from gcell_member_allot GMA where GMA.cau_id = " . $cau_id . ") ORDER by "
. "GM.gm_submitted_on desc";
}
return $this->db->query($sql);
}
function getGrievanceInfoById($gm_id) {
$this->db->select("*");
$this->db->from('grievance_mst GM');
$this->db->join('grievance_cat_mst GCM', 'GM.gcm_id = GCM.gcm_id');
$this->db->join('college_admins_and_users CAU', 'GM.gm_submitted_by = CAU.cau_id');
$this->db->where('gm_id', $gm_id);
return $this->db->get();
}
function getGrievancesByFilters($gcm_id = '', $grvncStatus = '', $actionTakenBy = '', $submitionStartDate = '', $submitionEndDate = '', $userRole = '', $cau_id = '') {
if ($actionTakenBy != '' && $grvncStatus == '' && $gcm_id == '') { /* If Only Member Is Selected Without Status/Grievance Combination:Start */
$sql = "select * from grievance_mst GM, grievance_cat_mst GCM where GM.gcm_id = GCM.gcm_id AND "
. "GM.gcm_id IN (select gcm_id from gcell_member_allot GMA where GMA.cau_id = " . $actionTakenBy . ")";
if ($submitionStartDate != "" && $submitionEndDate == "") { /* If Only Start Date Is Selected */
$sql .= " AND GM.gm_submitted_on >= '" . $submitionStartDate . "'";
} else if ($submitionStartDate == "" && $submitionEndDate != "") { /* If Only End Date Is Selected */
$sql .= " AND GM.gm_submitted_on <= '" . $submitionEndDate . "'";
} else if ($submitionStartDate != "" && $submitionEndDate != "") { /* If Both Start & End Date Is Selected */
$sql .= " AND GM.gm_submitted_on >= '" . $submitionStartDate . "' AND GM.gm_submitted_on <= '" . $submitionEndDate . "'";
}
/* If Report Is Accessed By Any G-Cell Or Mgmnt. Members: Only Selecting Those Grievances Whose Heads Has Been Alloted To Him/Her */
if ($userRole == "GCM" || $userRole == "MGMT") {
$sql .= " AND GM.gcm_id IN (select gcm_id from gcell_member_allot GMA where GMA.cau_id = " . $cau_id . ")";
$this->db->where("GM.gcm_id IN (select gcm_id from gcell_member_allot GMA where GMA.cau_id = " . $cau_id . ")");
}
/* Restriction Of Heads Ends */
$sql .= " ORDER BY GM.gm_submitted_on desc";
return $this->db->query($sql);
} else { /* For Other Combination Cases */
$this->db->select("*");
$this->db->from('grievance_mst GM');
$this->db->join('grievance_cat_mst GCM', 'GM.gcm_id = GCM.gcm_id');
/* If Any Grievance Category Is Selected:Start */
if ($gcm_id != '') {
$this->db->where('GM.gcm_id', $gcm_id);
}
/* If Any Grievance Category Is Selected:End */
/* If Any Grievance Status Is Selected:Start */
if ($grvncStatus != '') {
$this->db->where('GM.gm_current_status', $grvncStatus);
}
/* If Any Grievance Status Is Selected:End */
/* If Any Member Is Selected With Status/Grievance Category Combination:Start */
if ($actionTakenBy != '') {
/* Combining The Status Choice With Member:Start */
if ($grvncStatus != '') {
if ($grvncStatus == 'V') {
$this->db->where('GM.gm_viewed_by', $actionTakenBy);
} else if ($grvncStatus == 'A') {
$this->db->where('GM.gm_acknowledge_by', $actionTakenBy);
} else if ($grvncStatus == 'R') {
$this->db->where('GM.gm_resolved_by', $actionTakenBy);
}/* else if($grvncStatus == 'L'){
$this->db->where('GM.gm_locked_by', $actionTakenBy);
} */
} else {
$this->db->where("GM.gm_viewed_by = " . $actionTakenBy . " OR GM.gm_acknowledge_by = " . $actionTakenBy . " OR GM.gm_resolved_by = " . $actionTakenBy . " OR GM.gm_locked_by =" . $actionTakenBy);
}
/* Combining The Status Choice With Member:End */
}
/* If Any Member Is Selected With Status/Grievance Category Combination:End */
if ($submitionStartDate != "" && $submitionEndDate == "") { /* If Only Start Date Is Selected */
$this->db->where("GM.gm_submitted_on >= '" . $submitionStartDate . "'");
} else if ($submitionStartDate == "" && $submitionEndDate != "") { /* If Only End Date Is Selected */
$this->db->where("GM.gm_submitted_on <= '" . $submitionEndDate . "'");
} else if ($submitionStartDate != "" && $submitionEndDate != "") { /* If Both Start & End Date Is Selected */
$this->db->where("GM.gm_submitted_on >= '" . $submitionStartDate . "' AND GM.gm_submitted_on <= '" . $submitionEndDate . "'");
}
/* If Report Is Accessed By Any G-Cell Or Mgmnt. Members: Only Selecting Those Grievances Whose Heads Has Been Alloted To Him/Her */
if ($userRole == "GCM" || $userRole == "MGMT") {
$this->db->where("GM.gcm_id IN (select gcm_id from gcell_member_allot GMA where GMA.cau_id = " . $cau_id . ")");
}
/* Restriction Of Heads Ends */
$this->db->order_by('GM.gm_submitted_on desc');
return $this->db->get();
}
}
function getGrievancesCountByFilters($gcm_id = '', $grvncStatus = '', $actionTakenBy = '', $submitionStartDate = '', $submitionEndDate = '', $userRole = '', $cau_id = '') {
if ($actionTakenBy != '' && $grvncStatus == '' && $gcm_id == '') { /* If Only Member Is Selected Without Status/Grievance Combination:Start */
$sql = "select count(*) totalGrievances from grievance_mst GM, grievance_cat_mst GCM where GM.gcm_id = GCM.gcm_id AND "
. "GM.gcm_id IN (select gcm_id from gcell_member_allot GMA where GMA.cau_id = " . $actionTakenBy . ")";
if ($submitionStartDate != "" && $submitionEndDate == "") { /* If Only Start Date Is Selected */
$sql .= " AND GM.gm_submitted_on >= '" . $submitionStartDate . "'";
} else if ($submitionStartDate == "" && $submitionEndDate != "") { /* If Only End Date Is Selected */
$sql .= " AND GM.gm_submitted_on <= '" . $submitionEndDate . "'";
} else if ($submitionStartDate != "" && $submitionEndDate != "") { /* If Both Start & End Date Is Selected */
$sql .= " AND GM.gm_submitted_on >= '" . $submitionStartDate . "' AND GM.gm_submitted_on <= '" . $submitionEndDate . "'";
}
/* If Report Is Accessed By Any G-Cell Or Mgmnt. Members: Only Selecting Those Grievances Whose Heads Has Been Alloted To Him/Her */
if ($userRole == "GCM" || $userRole == "MGMT") {
$sql .= " AND GM.gcm_id IN (select gcm_id from gcell_member_allot GMA where GMA.cau_id = " . $cau_id . ")";
$this->db->where("GM.gcm_id IN (select gcm_id from gcell_member_allot GMA where GMA.cau_id = " . $cau_id . ")");
}
/* Restriction Of Heads Ends */
$sql .= " ORDER BY GM.gm_submitted_on desc";
return $this->db->query($sql);
} else { /* For Other Combination Cases */
$this->db->select("count(*) totalGrievances");
$this->db->from('grievance_mst GM');
$this->db->join('grievance_cat_mst GCM', 'GM.gcm_id = GCM.gcm_id');
/* If Any Grievance Category Is Selected:Start */
if ($gcm_id != '') {
$this->db->where('GM.gcm_id', $gcm_id);
}
/* If Any Grievance Category Is Selected:End */
/* If Any Grievance Status Is Selected:Start */
if ($grvncStatus != '') {
$this->db->where('GM.gm_current_status', $grvncStatus);
}
/* If Any Grievance Status Is Selected:End */
/* If Any Member Is Selected With Status/Grievance Category Combination:Start */
if ($actionTakenBy != '') {
/* Combining The Status Choice With Member:Start */
if ($grvncStatus != '') {
if ($grvncStatus == 'V') {
$this->db->where('GM.gm_viewed_by', $actionTakenBy);
} else if ($grvncStatus == 'A') {
$this->db->where('GM.gm_acknowledge_by', $actionTakenBy);
} else if ($grvncStatus == 'R') {
$this->db->where('GM.gm_resolved_by', $actionTakenBy);
}/* else if($grvncStatus == 'L'){
$this->db->where('GM.gm_locked_by', $actionTakenBy);
} */
} else {
$this->db->where("GM.gm_viewed_by = " . $actionTakenBy . " OR GM.gm_acknowledge_by = " . $actionTakenBy . " OR GM.gm_resolved_by = " . $actionTakenBy . " OR GM.gm_locked_by =" . $actionTakenBy);
}
/* Combining The Status Choice With Member:End */
}
/* If Any Member Is Selected With Status/Grievance Category Combination:End */
if ($submitionStartDate != "" && $submitionEndDate == "") { /* If Only Start Date Is Selected */
$this->db->where("GM.gm_submitted_on >= '" . $submitionStartDate . "'");
} else if ($submitionStartDate == "" && $submitionEndDate != "") { /* If Only End Date Is Selected */
$this->db->where("GM.gm_submitted_on <= '" . $submitionEndDate . "'");
} else if ($submitionStartDate != "" && $submitionEndDate != "") { /* If Both Start & End Date Is Selected */
$this->db->where("GM.gm_submitted_on >= '" . $submitionStartDate . "' AND GM.gm_submitted_on <= '" . $submitionEndDate . "'");
}
/* If Report Is Accessed By Any G-Cell Or Mgmnt. Members: Only Selecting Those Grievances Whose Heads Has Been Alloted To Him/Her */
if ($userRole == "GCM" || $userRole == "MGMT") {
$this->db->where("GM.gcm_id IN (select gcm_id from gcell_member_allot GMA where GMA.cau_id = " . $cau_id . ")");
}
/* Restriction Of Heads Ends */
$this->db->order_by('GM.gm_submitted_on desc');
return $this->db->get();
}
}
}