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;
}
