1#@!#!123s

: / home / jnclnmuac / public_html / grievance / application / models /
Filename : GrievanceManagement.php
back
<?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();
        }
    }

}