PHP Users_Privileges_Model::getNonAdminAccessControlQuery方法代码示例

    public function getMailCount($owner, $dateFilter)
    {
        $db = PearDatabase::getInstance();
        if (!$owner) {
            $currenUserModel = Users_Record_Model::getCurrentUserModel();
            $owner = $currenUserModel->getId();
        } else {
            if ($owner === 'all') {
                $owner = '';
            }
        }
        $params = array();
        if (!empty($owner)) {
            $ownerSql = ' AND smownerid = ? ';
            $params[] = $owner;
        }
        if (!empty($dateFilter)) {
            $dateFilterSql = ' AND createdtime BETWEEN ? AND ? ';
            $params[] = $dateFilter['start'] . ' 00:00:00';
            $params[] = $dateFilter['end'] . ' 23:59:59';
        }
        $result = $db->pquery('SELECT COUNT(*) count, ossmailview_sendtype FROM vtiger_ossmailview
						INNER JOIN vtiger_crmentity ON vtiger_ossmailview.ossmailviewid = vtiger_crmentity.crmid
						AND deleted = 0 ' . Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()) . $ownerSql . $dateFilterSql . ' GROUP BY ossmailview_sendtype', $params);
        $response = array();
        for ($i = 0; $i < $db->num_rows($result); $i++) {
            $saleStage = $db->query_result($result, $i, 'ossmailview_sendtype');
            $response[$i][0] = $saleStage;
            $response[$i][1] = $db->query_result($result, $i, 'count');
            $response[$i][2] = vtranslate($saleStage, $this->getName());
        }
        return $response;
    }

    /**
     * Function returns Rumors grouped by Status
     * @param type $data
     * @return <Array>
     */
    public function getRumorsByStatus($owner, $dateFilter)
    {
        $db = PearDatabase::getInstance();
        $ownerSql = $this->getOwnerWhereConditionForDashBoards($owner);
        if (!empty($ownerSql)) {
            $ownerSql = ' AND ' . $ownerSql;
        }
        $params = array();
        if (!empty($dateFilter)) {
            $dateFilterSql = ' AND createdtime BETWEEN ? AND ? ';
            //client is not giving time frame so we are appending it
            $params[] = $dateFilter['start'] . ' 00:00:00';
            $params[] = $dateFilter['end'] . ' 23:59:59';
        }
        $result = $db->pquery('SELECT COUNT(*) as count, CASE WHEN vtiger_rumors.rumor_status IS NULL OR vtiger_rumors.rumor_status = "" THEN "" ELSE vtiger_rumors.rumor_status END AS statusvalue 
							FROM vtiger_rumors INNER JOIN vtiger_crmentity ON vtiger_rumors.rumorsid = vtiger_crmentity.crmid AND vtiger_crmentity.deleted=0
							' . Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()) . $ownerSql . ' ' . $dateFilterSql . ' INNER JOIN vtiger_rumor_status ON vtiger_rumors.rumor_status = vtiger_rumor_status.rumor_status GROUP BY statusvalue ORDER BY vtiger_rumor_status.sortorderid', $params);
        $response = array();
        for ($i = 0; $i < $db->num_rows($result); $i++) {
            $row = $db->query_result_rowdata($result, $i);
            $response[$i][0] = $row['count'];
            $rumorStatusVal = $row['statusvalue'];
            if ($rumorStatusVal == '') {
                $rumorStatusVal = 'LBL_BLANK';
            }
            $response[$i][1] = vtranslate($rumorStatusVal, $this->getName());
            $response[$i][2] = $rumorStatusVal;
        }
        return $response;
    }

    public function getTimeEmployee($id)
    {
        $db = PearDatabase::getInstance();
        $moduleModel = Vtiger_Record_Model::getCleanInstance('OSSTimeControl');
        $Ids = $moduleModel->getProjectRelatedIDS($id);
        foreach ($Ids as $module) {
            foreach ($module as $moduleId) {
                $idArray .= $moduleId . ',';
            }
        }
        if (null == $idArray) {
            $response = false;
        } else {
            $idArray = substr($idArray, 0, -1);
            $addSql = ' WHERE vtiger_osstimecontrol.osstimecontrolid IN (' . $idArray . ') ';
            //TODO need to handle security
            $result = $db->pquery('SELECT count(*) AS count, concat(vtiger_users.first_name, " " ,vtiger_users.last_name) as name, vtiger_users.id as id, SUM(vtiger_osstimecontrol.sum_time) as time  FROM vtiger_osstimecontrol
							INNER JOIN vtiger_crmentity ON vtiger_osstimecontrol.osstimecontrolid = vtiger_crmentity.crmid
							INNER JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid AND vtiger_users.status="ACTIVE"
							AND vtiger_crmentity.deleted = 0' . Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()) . $addSql . ' GROUP BY smownerid', array());
            $data = array();
            $numRows = $db->num_rows($result);
            for ($i = 0; $i < $numRows; $i++) {
                $row = $db->query_result_rowdata($result, $i);
                $data[$i]['label'] = $row['name'];
                $ticks[$i][0] = $i;
                $ticks[$i][1] = $row['name'];
                $data[$i]['data'][0][0] = $i;
                $data[$i]['data'][0][1] = $row['time'];
            }
            $response['ticks'] = $ticks;
            $response['chart'] = $data;
        }
        return $response;
    }

 /**
  * Function returns the Calendar Events for the module
  * @param <Vtiger_Paging_Model> $pagingModel
  * @return <Array>
  */
 public function getCalendarActivities($mode, $pagingModel, $user, $recordId = false)
 {
     $currentUser = Users_Record_Model::getCurrentUserModel();
     $db = PearDatabase::getInstance();
     if (!$user) {
         $user = $currentUser->getId();
     }
     $nowInUserFormat = Vtiger_Datetime_UIType::getDisplayDateValue(date('Y-m-d H:i:s'));
     $nowInDBFormat = Vtiger_Datetime_UIType::getDBDateTimeValue($nowInUserFormat);
     list($currentDate, $currentTime) = explode(' ', $nowInDBFormat);
     $query = "SELECT vtiger_crmentity.crmid, crmentity2.crmid AS contact_id, vtiger_crmentity.smownerid, vtiger_crmentity.setype, vtiger_activity.* FROM vtiger_activity\n\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\tINNER JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\tINNER JOIN vtiger_crmentity AS crmentity2 ON vtiger_cntactivityrel.contactid = crmentity2.crmid AND crmentity2.deleted = 0 AND crmentity2.setype = ?\n\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid";
     $query .= Users_Privileges_Model::getNonAdminAccessControlQuery('Calendar');
     $query .= " WHERE vtiger_crmentity.deleted=0\n\t\t\t\t\tAND (vtiger_activity.activitytype NOT IN ('Emails'))\n\t\t\t\t\tAND (vtiger_activity.status is NULL OR vtiger_activity.status NOT IN ('Completed', 'Deferred'))\n\t\t\t\t\tAND (vtiger_activity.eventstatus is NULL OR vtiger_activity.eventstatus NOT IN ('Held'))";
     if ($recordId) {
         $query .= " AND vtiger_cntactivityrel.contactid = ?";
     } elseif ($mode === 'upcoming') {
         $query .= " AND due_date >= '{$currentDate}'";
     } elseif ($mode === 'overdue') {
         $query .= " AND due_date < '{$currentDate}'";
     }
     $params = array($this->getName());
     if ($recordId) {
         array_push($params, $recordId);
     }
     if ($user != 'all' && $user != '') {
         if ($user === $currentUser->id) {
             $query .= " AND vtiger_crmentity.smownerid = ?";
             array_push($params, $user);
         }
     }
     $query .= " ORDER BY date_start, time_start LIMIT " . $pagingModel->getStartIndex() . ", " . ($pagingModel->getPageLimit() + 1);
     $result = $db->pquery($query, $params);
     $numOfRows = $db->num_rows($result);
     $activities = array();
     for ($i = 0; $i < $numOfRows; $i++) {
         $row = $db->query_result_rowdata($result, $i);
         $model = Vtiger_Record_Model::getCleanInstance('Calendar');
         $model->setData($row);
         $model->setId($row['crmid']);
         $activities[] = $model;
     }
     $pagingModel->calculatePageRange($activities);
     if ($numOfRows > $pagingModel->getPageLimit()) {
         array_pop($activities);
         $pagingModel->set('nextPageExists', true);
     } else {
         $pagingModel->set('nextPageExists', false);
     }
     return $activities;
 }

    /**
     * Function returns query for PriceBooks-Services Relationship
     * @param <Vtiger_Record_Model> $recordModel
     * @param <Vtiger_Record_Model> $relatedModuleModel
     * @return <String>
     */
    function get_pricebook_services($recordModel, $relatedModuleModel)
    {
        $query = 'SELECT vtiger_service.serviceid, vtiger_service.servicename, vtiger_service.commissionrate,
					vtiger_service.qty_per_unit, vtiger_service.unit_price, vtiger_crmentity.crmid, vtiger_crmentity.smownerid,
					vtiger_pricebookproductrel.listprice
			FROM vtiger_service
			INNER JOIN vtiger_pricebookproductrel on vtiger_service.serviceid = vtiger_pricebookproductrel.productid
			INNER JOIN vtiger_crmentity on vtiger_crmentity.crmid = vtiger_service.serviceid
			INNER JOIN vtiger_pricebook on vtiger_pricebook.pricebookid = vtiger_pricebookproductrel.pricebookid
			LEFT JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid
			LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid ' . Users_Privileges_Model::getNonAdminAccessControlQuery($relatedModuleModel->getName()) . '
			WHERE vtiger_pricebook.pricebookid = ' . $recordModel->getId() . ' and vtiger_crmentity.deleted = 0';
        return $query;
    }

    public function getTimeEmployee($id)
    {
        $db = PearDatabase::getInstance();
        //TODO need to handle security
        $result = $db->pquery('SELECT count(*) AS count, concat(vtiger_users.first_name, " " ,vtiger_users.last_name) as name, vtiger_users.id as id, SUM(vtiger_osstimecontrol.sum_time) as time  FROM vtiger_osstimecontrol
						INNER JOIN vtiger_crmentity ON vtiger_osstimecontrol.osstimecontrolid = vtiger_crmentity.crmid
						LEFT JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid AND vtiger_users.status="ACTIVE"
						AND vtiger_crmentity.deleted = 0' . Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()) . ' WHERE vtiger_osstimecontrol.ticketid = ? AND vtiger_osstimecontrol.osstimecontrol_status = ?  GROUP BY smownerid', array($id, 'Accepted'));
        $data = array();
        for ($i = 0; $i < $db->num_rows($result); $i++) {
            $row = $db->query_result_rowdata($result, $i);
            $data[$i]['label'] = $row['name'];
            $data[$i]['data'][0][0] = $i;
            $data[$i]['data'][0][1] = $row['time'];
        }
        $response['chart'] = $data;
        return $response;
    }

 /**
  * Function to get Non admin access control query
  * @param <String> $relatedModuleName
  * @return <String>
  */
 public function getNonAdminAccessControlQueryForRelation($relatedModuleName)
 {
     $modulesList = array('Faq', 'PriceBook', 'Vendors', 'Users');
     if (!in_array($relatedModuleName, $modulesList)) {
         return Users_Privileges_Model::getNonAdminAccessControlQuery($relatedModuleName);
     }
 }

 /**
  * Function to get the list of recently visisted records
  * @param <Number> $limit
  * @return <Array> - List of Calendar_Record_Model
  */
 public function getRecentRecords($limit = 10)
 {
     $db = PearDatabase::getInstance();
     $currentUserModel = Users_Record_Model::getCurrentUserModel();
     $deletedCondition = parent::getDeletedRecordCondition();
     $nonAdminQuery .= Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName());
     $query = 'SELECT * FROM vtiger_crmentity ';
     if ($nonAdminQuery) {
         $query .= " INNER JOIN vtiger_activity ON vtiger_crmentity.crmid = vtiger_activity.activityid " . $nonAdminQuery;
     }
     $query .= ' WHERE setype=? AND ' . $deletedCondition . ' AND modifiedby = ? ORDER BY modifiedtime DESC LIMIT ?';
     $params = array($this->getName(), $currentUserModel->id, $limit);
     $result = $db->pquery($query, $params);
     $noOfRows = $db->num_rows($result);
     $recentRecords = array();
     for ($i = 0; $i < $noOfRows; ++$i) {
         $row = $db->query_result_rowdata($result, $i);
         $row['id'] = $row['crmid'];
         $recentRecords[$row['id']] = $this->getRecordFromArray($row);
     }
     return $recentRecords;
 }

    public function getTimeEmployee($id)
    {
        $db = PearDatabase::getInstance();
        $QuoteId = array();
        $sumAllTime = 0;
        $SalesId = array();
        $PotentialId = array();
        $sales = array();
        $quote = array();
        $sql = "SELECT * FROM vtiger_crmentityrel WHERE crmid={$id} ";
        $resultSource = $db->query($sql, true);
        $num = $db->num_rows($resultSource);
        for ($i = 0; $i < $num; $i++) {
            if ($db->query_result($resultSource, $i, 'relmodule') == 'SalesOrder') {
                $sales[] = $db->query_result($resultSource, $i, 'relcrmid');
            } elseif ($db->query_result($resultSource, $i, 'relmodule') == 'Quotes') {
                $quote[] = $db->query_result($resultSource, $i, 'relcrmid');
            }
        }
        $sqlTC = "SELECT * FROM vtiger_osstimecontrol WHERE deleted = ? AND osstimecontrol_status = ?";
        $resultTC = $db->pquery($sqlTC, array(0, 'Accepted'), true);
        $num = $db->num_rows($resultTC);
        $q = 0;
        for ($i = 0; $i < $num; $i++) {
            $q = $i;
            if ($db->query_result($resultTC, $i, 'quoteid') == 0 && $db->query_result($resultTC, $i, 'potentialid') == $id && $db->query_result($resultTC, $i, 'salesorderid') == 0) {
                $PotentialId[] = $db->query_result($resultTC, $i, 'osstimecontrolid');
                $q++;
            }
            if ($q == $i && $db->query_result($resultTC, $i, 'quoteid') != 0 && $db->query_result($resultTC, $i, 'potentialid') == $id) {
                $QuoteId[] = $db->query_result($resultTC, $i, 'osstimecontrolid');
                $q++;
            } elseif ($q == $i && $db->query_result($resultTC, $i, 'quoteid') != 0 && $db->query_result($resultTC, $i, 'potentialid') != $id) {
                for ($j = 0; $j < count($quote); $j++) {
                    if ($db->query_result($resultTC, $i, 'quoteid') == $quote[$j]) {
                        $QuoteId[] = $db->query_result($resultTC, $i, 'osstimecontrolid');
                        $q++;
                    }
                }
            }
            if ($q == $i && $db->query_result($resultTC, $i, 'salesorderid') != 0 && $db->query_result($resultTC, $i, 'potentialid') == $id) {
                $SalesId[] = $db->query_result($resultTC, $i, 'osstimecontrolid');
            } elseif ($q == $i && $db->query_result($resultTC, $i, 'salesorderid') != 0 && $db->query_result($resultTC, $i, 'potentialid') != $id) {
                for ($j = 0; $j < count($sales); $j++) {
                    if ($db->query_result($resultTC, $i, 'quoteid') == $sales[$j]) {
                        $SalesId[] = $db->query_result($resultTC, $i, 'osstimecontrolid');
                    }
                }
            }
        }
        $Ids = array($QuoteId, $PotentialId, $SalesId);
        foreach ($Ids as $module) {
            foreach ($module as $moduleId) {
                $idArray .= $moduleId . ',';
            }
        }
        $idArray = substr($idArray, 0, -1);
        $addSql = '';
        if ($idArray) {
            $addSql = ' WHERE vtiger_osstimecontrol.osstimecontrolid IN (' . $idArray . ') ';
        }
        //TODO need to handle security
        $result = $db->pquery('SELECT count(*) AS count, concat(vtiger_users.first_name, " " ,vtiger_users.last_name) as name, vtiger_users.id as id, SUM(vtiger_osstimecontrol.sum_time) as time  FROM vtiger_osstimecontrol
						INNER JOIN vtiger_crmentity ON vtiger_osstimecontrol.osstimecontrolid = vtiger_crmentity.crmid
						INNER JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid AND vtiger_users.status="ACTIVE"
						AND vtiger_crmentity.deleted = 0' . Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()) . $addSql . ' GROUP BY smownerid', array());
        $data = array();
        for ($i = 0; $i < $db->num_rows($result); $i++) {
            $row = $db->query_result_rowdata($result, $i);
            $data[] = $row;
        }
        return $data;
    }

    public function getTimeProject($id)
    {
        $db = PearDatabase::getInstance();
        //TODO need to handle security
        $result = $db->pquery('SELECT   vtiger_project.sum_time AS TIME,  vtiger_project.sum_time_h AS timehelpdesk,
			vtiger_project.sum_time_pt AS projecttasktime FROM  vtiger_project LEFT JOIN vtiger_crmentity   ON vtiger_project.projectid = vtiger_crmentity.crmid 
			AND vtiger_crmentity.deleted = 0 ' . Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()) . 'WHERE vtiger_project.projectid = ?', array($id), true);
        $response = array();
        if ($db->num_rows($result) > 0) {
            $projectTime = $db->query_result($result, $i, 'time');
            $response[0][0] = $projectTime;
            $response[0][1] = vtranslate('Total time [h]', 'Project');
            $response[1][0] = $db->query_result($result, $i, 'timehelpdesk');
            $response[1][1] = vtranslate('Total time [Tickets]', $this->getName());
            $response[2][0] = $db->query_result($result, $i, 'projecttasktime');
            $response[2][1] = vtranslate('Total time [Project Task]', $this->getName());
        }
        $recordModel = Vtiger_Record_Model::getInstanceById($id, $this->getName());
        $response = array();
        $response[0][0] = $recordModel->get('sum_time');
        $response[0][1] = vtranslate('Total time [Project]', $this->getName());
        $response[1][0] = $recordModel->get('sum_time_pt');
        $response[1][1] = vtranslate('Total time [Project Task]', $this->getName());
        $response[2][0] = $recordModel->get('sum_time_h');
        $response[2][1] = vtranslate('Total time [Tickets]', $this->getName());
        $response[3][0] = $recordModel->get('sum_time_all');
        $response[3][1] = vtranslate('Total time [Sum]', $this->getName());
        return $response;
    }

    /**
     * Function returns query for Product-PriceBooks relation
     * @param <Vtiger_Record_Model> $recordModel
     * @param <Vtiger_Record_Model> $relatedModuleModel
     * @return <String>
     */
    function get_product_pricebooks($recordModel, $relatedModuleModel)
    {
        $query = 'SELECT vtiger_pricebook.pricebookid, vtiger_pricebook.bookname, vtiger_pricebook.active, vtiger_crmentity.crmid, 
						vtiger_crmentity.smownerid, vtiger_pricebookproductrel.listprice, vtiger_products.unit_price
					FROM vtiger_pricebook
					INNER JOIN vtiger_pricebookproductrel ON vtiger_pricebook.pricebookid = vtiger_pricebookproductrel.pricebookid
					INNER JOIN vtiger_crmentity on vtiger_crmentity.crmid = vtiger_pricebook.pricebookid
					INNER JOIN vtiger_products on vtiger_products.productid = vtiger_pricebookproductrel.productid
					INNER JOIN vtiger_pricebookcf on vtiger_pricebookcf.pricebookid = vtiger_pricebook.pricebookid
					LEFT JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid
					LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid ' . Users_Privileges_Model::getNonAdminAccessControlQuery($relatedModuleModel->getName()) . '
					WHERE vtiger_products.productid = ' . $recordModel->getId() . ' and vtiger_crmentity.deleted = 0';
        return $query;
    }

    /**
     * Function returns number of Open Potentials in each of the sales stage
     * @param <Integer> $owner - userid
     * @return <Array>
     */
    public function getCampaignsWidget($owner, $dateFilter)
    {
        $db = PearDatabase::getInstance();
        if (!$owner) {
            $currenUserModel = Users_Record_Model::getCurrentUserModel();
            $owner = $currenUserModel->getId();
        } else {
            if ($owner === 'all') {
                $owner = '';
            }
        }
        $params = array();
        if (!empty($owner)) {
            $ownerSql = ' AND smownerid = ? ';
            $params[] = $owner;
        }
        if (!empty($dateFilter)) {
            $dateFilterSql = ' AND closingdate BETWEEN ? AND ? ';
            $params[] = $dateFilter['start'];
            $params[] = $dateFilter['end'];
        }
        $result = $db->pquery('SELECT COUNT(*) count, sales_stage FROM vtiger_potential
						INNER JOIN vtiger_crmentity ON vtiger_potential.potentialid = vtiger_crmentity.crmid
						AND deleted = 0 ' . Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()) . $ownerSql . $dateFilterSql . ' AND sales_stage NOT IN ("Closed Won", "Closed Lost")
							GROUP BY sales_stage ORDER BY count desc', $params);
        $response = array();
        for ($i = 0; $i < $db->num_rows($result); $i++) {
            $saleStage = $db->query_result($result, $i, 'sales_stage');
            $response[$i][0] = $saleStage;
            $response[$i][1] = $db->query_result($result, $i, 'count');
            $response[$i][2] = vtranslate($saleStage, $this->getName());
        }
        return $response;
    }

    /**
     * Function returns Potentials Amount for each Sales Stage
     * @return <Array>
     */
    function getPotentialTotalAmountBySalesStage()
    {
        //$currentUser = Users_Record_Model::getCurrentUserModel();
        $db = PearDatabase::getInstance();
        $picklistValues = Vtiger_Util_Helper::getPickListValues('sales_stage');
        $data = array();
        foreach ($picklistValues as $key => $picklistValue) {
            $result = $db->pquery('SELECT SUM(amount) AS amount FROM vtiger_potential
								   INNER JOIN vtiger_crmentity ON vtiger_potential.potentialid = vtiger_crmentity.crmid
								   AND deleted = 0 ' . Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()) . ' WHERE sales_stage = ?', array($picklistValue));
            $num_rows = $db->num_rows($result);
            for ($i = 0; $i < $num_rows; $i++) {
                $values = array();
                $amount = $db->query_result($result, $i, 'amount');
                if (!empty($amount)) {
                    $values[0] = $db->query_result($result, $i, 'amount');
                    $values[1] = vtranslate($picklistValue, $this->getName());
                    $data[] = $values;
                }
            }
        }
        return $data;
    }

 /**
  * Function returns the Calendar Events for the module
  * @param <String> $mode - upcoming/overdue mode
  * @param <Vtiger_Paging_Model> $pagingModel - $pagingModel
  * @param <String> $user - all/userid
  * @param <String> $recordId - record id
  * @return <Array>
  */
 function getCalendarActivities($mode, $pagingModel, $user, $recordId = false)
 {
     $currentUser = Users_Record_Model::getCurrentUserModel();
     $db = PearDatabase::getInstance();
     if (!$user) {
         $user = $currentUser->getId();
     }
     $nowInUserFormat = Vtiger_Datetime_UIType::getDisplayDateTimeValue(date('Y-m-d H:i:s'));
     $nowInDBFormat = Vtiger_Datetime_UIType::getDBDateTimeValue($nowInUserFormat);
     list($currentDate, $currentTime) = explode(' ', $nowInDBFormat);
     $query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_crmentity.setype, vtiger_activity.* FROM vtiger_activity\n\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid";
     $query .= Users_Privileges_Model::getNonAdminAccessControlQuery('Calendar');
     $query .= " WHERE vtiger_crmentity.deleted=0\n\t\t\t\t\tAND (vtiger_activity.activitytype NOT IN ('Emails'))\n\t\t\t\t\tAND (vtiger_activity.status is NULL OR vtiger_activity.status NOT IN ('Completed', 'Deferred'))\n\t\t\t\t\tAND (vtiger_activity.eventstatus is NULL OR vtiger_activity.eventstatus NOT IN ('Held'))";
     if ($mode === 'upcoming') {
         $query .= " AND CASE WHEN vtiger_activity.activitytype='Task' THEN due_date >= '{$currentDate}' ELSE CONCAT(due_date,' ',time_end) >= '{$nowInDBFormat}' END";
     } elseif ($mode === 'overdue') {
         $query .= " AND CASE WHEN vtiger_activity.activitytype='Task' THEN due_date < '{$currentDate}' ELSE CONCAT(due_date,' ',time_end) < '{$nowInDBFormat}' END";
     }
     $params = array();
     if ($user != 'all' && $user != '') {
         if ($user === $currentUser->id) {
             $query .= " AND vtiger_crmentity.smownerid = ?";
             $params[] = $user;
         }
     }
     $query .= " ORDER BY date_start, time_start LIMIT ?, ?";
     $params[] = $pagingModel->getStartIndex();
     $params[] = $pagingModel->getPageLimit() + 1;
     $result = $db->pquery($query, $params);
     $numOfRows = $db->num_rows($result);
     $activities = array();
     for ($i = 0; $i < $numOfRows; $i++) {
         $row = $db->query_result_rowdata($result, $i);
         $model = Vtiger_Record_Model::getCleanInstance('Calendar');
         $model->setData($row);
         if ($row['activitytype'] == 'Task') {
             $due_date = $row["due_date"];
             $dayEndTime = "23:59:59";
             $EndDateTime = Vtiger_Datetime_UIType::getDBDateTimeValue($due_date . " " . $dayEndTime);
             $dueDateTimeInDbFormat = explode(' ', $EndDateTime);
             $dueTimeInDbFormat = $dueDateTimeInDbFormat[1];
             $model->set('time_end', $dueTimeInDbFormat);
         }
         $model->setId($row['crmid']);
         $activities[] = $model;
     }
     $pagingModel->calculatePageRange($activities);
     if ($numOfRows > $pagingModel->getPageLimit()) {
         array_pop($activities);
         $pagingModel->set('nextPageExists', true);
     } else {
         $pagingModel->set('nextPageExists', false);
     }
     return $activities;
 }