/**
* Function to get the list view entries
* @param Vtiger_Paging_Model $pagingModel
* @return <Array> - Associative array of record id mapped to Vtiger_Record_Model instance.
*/
public function getListViewEntries($pagingModel)
{
$db = PearDatabase::getInstance();
$moduleName = $this->getModule()->get('name');
$moduleFocus = CRMEntity::getInstance($moduleName);
$moduleModel = Vtiger_Module_Model::getInstance($moduleName);
$queryGenerator = $this->get('query_generator');
$listViewContoller = $this->get('listview_controller');
$searchParams = $this->get('search_params');
if (empty($searchParams)) {
$searchParams = array();
}
$glue = "";
if (count($queryGenerator->getWhereFields()) > 0 && (count($searchParams)) > 0) {
$glue = QueryGenerator::$AND;
}
$queryGenerator->parseAdvFilterList($searchParams, $glue);
$searchKey = $this->get('search_key');
$searchValue = $this->get('search_value');
$operator = $this->get('operator');
if (!empty($searchKey)) {
$queryGenerator->addUserSearchConditions(array('search_field' => $searchKey, 'search_text' => $searchValue, 'operator' => $operator));
}
$orderBy = $this->getForSql('orderby');
$sortOrder = $this->getForSql('sortorder');
//List view will be displayed on recently created/modified records
if (empty($orderBy) && empty($sortOrder) && $moduleName != "Users") {
$orderBy = 'modifiedtime';
$sortOrder = 'DESC';
}
if (!empty($orderBy)) {
$columnFieldMapping = $moduleModel->getColumnFieldMapping();
$orderByFieldName = $columnFieldMapping[$orderBy];
$orderByFieldModel = $moduleModel->getField($orderByFieldName);
if ($orderByFieldModel && ($orderByFieldModel->isReferenceField() || $orderByFieldModel->getFieldDataType() == Vtiger_Field_Model::CURRENCY_LIST)) {
//IF it is reference add it in the where fields so that from clause will be having join of the table
$queryGenerator = $this->get('query_generator');
$queryGenerator->addWhereField($orderByFieldName);
//$queryGenerator->whereFields[] = $orderByFieldName;
}
}
if (!empty($orderBy) && $orderBy === 'smownerid') {
$fieldModel = Vtiger_Field_Model::getInstance('assigned_user_id', $moduleModel);
if ($fieldModel->getFieldDataType() == 'owner') {
$orderBy = 'COALESCE(' . getSqlForNameInDisplayFormat(['first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'], 'Users') . ',vtiger_groups.groupname)';
}
}
$listQuery = $this->getQuery();
$sourceModule = $this->get('src_module');
$sourceField = $this->get('src_field');
if (!empty($sourceModule)) {
if (method_exists($moduleModel, 'getQueryByModuleField')) {
$overrideQuery = $moduleModel->getQueryByModuleField($sourceModule, $this->get('src_field'), $this->get('src_record'), $listQuery, $this->get('currency_id'));
if (!empty($overrideQuery)) {
$listQuery = $overrideQuery;
}
}
}
$startIndex = $pagingModel->getStartIndex();
$pageLimit = $pagingModel->getPageLimit();
if (!empty($orderBy)) {
if ($orderByFieldModel && $orderByFieldModel->isReferenceField()) {
$referenceModules = $orderByFieldModel->getReferenceList();
$referenceNameFieldOrderBy = array();
foreach ($referenceModules as $referenceModuleName) {
$referenceModuleModel = Vtiger_Module_Model::getInstance($referenceModuleName);
$referenceNameFields = $referenceModuleModel->getNameFields();
$columnList = array();
foreach ($referenceNameFields as $nameField) {
$fieldModel = $referenceModuleModel->getField($nameField);
$columnList[] = $fieldModel->get('table') . $orderByFieldModel->getName() . '.' . $fieldModel->get('column');
}
if (count($columnList) > 1) {
$referenceNameFieldOrderBy[] = getSqlForNameInDisplayFormat(array('first_name' => $columnList[0], 'last_name' => $columnList[1]), 'Users') . ' ' . $sortOrder;
} else {
$referenceNameFieldOrderBy[] = implode('', $columnList) . ' ' . $sortOrder;
}
}
$listQuery .= ' ORDER BY ' . implode(',', $referenceNameFieldOrderBy);
} else if ($orderByFieldModel && $orderByFieldModel->getFieldDataType() == Vtiger_Field_Model::CURRENCY_LIST) {
$listQuery .= ' ORDER BY ' . $orderByFieldModel->getUITypeModel()->getCurrenyListReferenceFieldName() . ' ' . $sortOrder;
} else {
$listQuery .= ' ORDER BY ' . $orderBy . ' ' . $sortOrder;
}
}
$viewid = ListViewSession::getCurrentView($moduleName);
if (empty($viewid)) {
$viewid = $pagingModel->get('viewid');
}
$_SESSION['lvs'][$moduleName][$viewid]['start'] = $pagingModel->get('page');
ListViewSession::setSessionQuery($moduleName, $listQuery, $viewid);
//For Pricebooks popup in Products and Services Related list
if ($sourceField !== 'productsRelatedList') {
$listQuery .= " LIMIT $startIndex," . ($pageLimit + 1);
}
$listResult = $db->pquery($listQuery, array());
$listViewRecordModels = array();
$listViewEntries = $listViewContoller->getListViewRecords($moduleFocus, $moduleName, $listResult);
$pagingModel->calculatePageRange($listViewEntries);
//To check if next page
if ($db->num_rows($listResult) > $pageLimit && $sourceField !== 'productsRelatedList') {
array_pop($listViewEntries);
$pagingModel->set('nextPageExists', true);
} else {
$pagingModel->set('nextPageExists', false);
}
$index = 0;
foreach ($listViewEntries as $recordId => $record) {
$rawData = $db->query_result_rowdata($listResult, $index++);
$record['id'] = $recordId;
// Pass through the src_record state to dependent model
if ($this->has('src_record')) {
$rawData['src_record'] = $this->get('src_record');
}
$listViewRecordModels[$recordId] = $moduleModel->getRecordFromArray($record, $rawData);
$listViewRecordModels[$recordId]->lockEditView = Users_Privileges_Model::checkLockEdit($moduleName, $recordId);
$listViewRecordModels[$recordId]->isPermittedToEditView = Users_Privileges_Model::isPermitted($moduleName, 'EditView', $recordId);
$listViewRecordModels[$recordId]->colorList = Settings_DataAccess_Module_Model::executeColorListHandlers($moduleName, $recordId, $listViewRecordModels[$recordId]);
}
return $listViewRecordModels;
}
/**
* Overrided to add HTML content for callstatus irrespective of the filters
*/
public function getListViewEntries($pagingModel)
{
$db = PearDatabase::getInstance();
$moduleName = $this->getModule()->get('name');
$moduleFocus = CRMEntity::getInstance($moduleName);
$moduleModel = Vtiger_Module_Model::getInstance($moduleName);
//Add the direction field to the query irrespective of filter
$queryGenerator = $this->get('query_generator');
$fields = $queryGenerator->getFields();
array_push($fields, 'direction');
$queryGenerator->setFields($fields);
$this->set('query_generator', $queryGenerator);
//END
$listViewContoller = $this->get('listview_controller');
$searchKey = $this->get('search_key');
$searchValue = $this->get('search_value');
$operator = $this->get('operator');
if (!empty($searchKey)) {
$queryGenerator->addUserSearchConditions(array('search_field' => $searchKey, 'search_text' => $searchValue, 'operator' => $operator));
}
$orderBy = $this->getForSql('orderby');
$sortOrder = $this->getForSql('sortorder');
//List view will be displayed on recently created/modified records
if (empty($orderBy) && empty($sortOrder) && $moduleName != "Users") {
$orderBy = 'modifiedtime';
$sortOrder = 'DESC';
}
if (!empty($orderBy)) {
$columnFieldMapping = $moduleModel->getColumnFieldMapping();
$orderByFieldName = $columnFieldMapping[$orderBy];
$orderByFieldModel = $moduleModel->getField($orderByFieldName);
if ($orderByFieldModel->getFieldDataType() == Vtiger_Field_Model::REFERENCE_TYPE) {
//IF it is reference add it in the where fields so that from clause will be having join of the table
$queryGenerator = $this->get('query_generator');
$queryGenerator->addWhereField($orderByFieldName);
//$queryGenerator->whereFields[] = $orderByFieldName;
}
}
$listQuery = $this->getQuery();
$sourceModule = $this->get('src_module');
if (!empty($sourceModule)) {
if (method_exists($moduleModel, 'getQueryByModuleField')) {
$overrideQuery = $moduleModel->getQueryByModuleField($sourceModule, $this->get('src_field'), $this->get('src_record'), $listQuery);
if (!empty($overrideQuery)) {
$listQuery = $overrideQuery;
}
}
}
$startIndex = $pagingModel->getStartIndex();
$pageLimit = $pagingModel->getPageLimit();
if (!empty($orderBy)) {
if ($orderByFieldModel->isReferenceField()) {
$referenceModules = $orderByFieldModel->getReferenceList();
$referenceNameFieldOrderBy = array();
foreach ($referenceModules as $referenceModuleName) {
$referenceModuleModel = Vtiger_Module_Model::getInstance($referenceModuleName);
$referenceNameFields = $referenceModuleModel->getNameFields();
$columnList = array();
foreach ($referenceNameFields as $nameField) {
$fieldModel = $referenceModuleModel->getField($nameField);
$columnList[] = $fieldModel->get('table') . $orderByFieldModel->getName() . '.' . $fieldModel->get('column');
}
if (count($columnList) > 1) {
$referenceNameFieldOrderBy[] = getSqlForNameInDisplayFormat(array('first_name' => $columnList[0], 'last_name' => $columnList[1]), 'Users') . ' ' . $sortOrder;
} else {
$referenceNameFieldOrderBy[] = implode('', $columnList) . ' ' . $sortOrder;
}
}
$listQuery .= ' ORDER BY ' . implode(',', $referenceNameFieldOrderBy);
} else {
$listQuery .= ' ORDER BY ' . $orderBy . ' ' . $sortOrder;
}
}
$viewid = ListViewSession::getCurrentView($moduleName);
ListViewSession::setSessionQuery($moduleName, $listQuery, $viewid);
$listQuery .= " LIMIT {$startIndex}," . ($pageLimit + 1);
$listResult = $db->pquery($listQuery, array());
$listViewRecordModels = array();
$listViewEntries = $listViewContoller->getListViewRecords($moduleFocus, $moduleName, $listResult);
$pagingModel->calculatePageRange($listViewEntries);
if ($db->num_rows($listResult) > $pageLimit) {
array_pop($listViewEntries);
$pagingModel->set('nextPageExists', true);
} else {
$pagingModel->set('nextPageExists', false);
}
//Adding the HTML content based on the callstatus and direction to the records
foreach ($listViewEntries as $recordId => $record) {
//To Replace RecordingUrl by Icon
$recordingUrl = explode('>', $listViewEntries[$recordId]['recordingurl']);
$url = explode('<', $recordingUrl[1]);
if ($url[0] != '' && $listViewEntries[$recordId]['callstatus'] == 'completed') {
$listViewEntries[$recordId]['recordingurl'] = $recordingUrl[0] . '>' . '<i class="icon-volume-up"></i>' . '</a>';
} else {
$listViewEntries[$recordId]['recordingurl'] = '';
}
if ($listViewEntries[$recordId]['direction'] == 'outbound') {
if ($listViewEntries[$recordId]['callstatus'] == 'ringing' || $listViewEntries[$recordId]['callstatus'] == 'in-progress') {
$listViewEntries[$recordId]['callstatus'] = '<span class="label label-info"><i class="icon-arrow-up icon-white">
</i> ' . $listViewEntries[$recordId]["callstatus"] . '</span>';
} else {
if ($listViewEntries[$recordId]['callstatus'] == 'completed') {
$listViewEntries[$recordId]['callstatus'] = '<span class="label label-success"><i class="icon-arrow-up icon-white">
</i> ' . $listViewEntries[$recordId]["callstatus"] . '</span>';
} else {
if ($listViewEntries[$recordId]['callstatus'] == 'no-answer') {
$listViewEntries[$recordId]['callstatus'] = '<span class="label label-important"><i class="icon-arrow-up icon-white">
</i> ' . $listViewEntries[$recordId]["callstatus"] . '</span>';
} else {
$listViewEntries[$recordId]['callstatus'] = '<span class="label label-warning"><i class="icon-arrow-up icon-white">
</i> ' . $listViewEntries[$recordId]["callstatus"] . '</span>';
}
}
}
} else {
if ($listViewEntries[$recordId]['direction'] == 'inbound') {
if ($listViewEntries[$recordId]['callstatus'] == 'ringing' || $listViewEntries[$recordId]['callstatus'] == 'in-progress') {
$listViewEntries[$recordId]['callstatus'] = '<span class="label label-info"><i class="icon-arrow-down icon-white">
</i> ' . $listViewEntries[$recordId]["callstatus"] . '</span>';
} else {
if ($listViewEntries[$recordId]['callstatus'] == 'completed') {
$listViewEntries[$recordId]['callstatus'] = '<span class="label label-success"><i class="icon-arrow-down icon-white">
</i> ' . $listViewEntries[$recordId]["callstatus"] . '</span>';
} else {
if ($listViewEntries[$recordId]['callstatus'] == 'no-answer') {
$listViewEntries[$recordId]['callstatus'] = '<span class="label label-important"><i class="icon-arrow-down icon-white">
</i> ' . $listViewEntries[$recordId]["callstatus"] . '</span>';
} else {
$listViewEntries[$recordId]['callstatus'] = '<span class="label label-warning"><i class="icon-arrow-down icon-white">
</i> ' . $listViewEntries[$recordId]["callstatus"] . '</span>';
}
}
}
}
}
}
//END
$index = 0;
foreach ($listViewEntries as $recordId => $record) {
$rawData = $db->query_result_rowdata($listResult, $index++);
$record['id'] = $recordId;
$listViewRecordModels[$recordId] = $moduleModel->getRecordFromArray($record, $rawData);
}
return $listViewRecordModels;
}
/**
* Function to get the list view entries
* @param Vtiger_Paging_Model $pagingModel
* @return <Array> - Associative array of record id mapped to Vtiger_Record_Model instance.
*/
public function getListViewEntries($pagingModel)
{
$db = PearDatabase::getInstance();
$moduleName = $this->getModule()->get('name');
$moduleFocus = CRMEntity::getInstance($moduleName);
$moduleModel = Vtiger_Module_Model::getInstance($moduleName);
$queryGenerator = $this->get('query_generator');
$listViewContoller = $this->get('listview_controller');
$searchParams = $this->get('search_params');
if (empty($searchParams)) {
$searchParams = array();
}
$glue = "";
if (count($queryGenerator->getWhereFields()) > 0 && count($searchParams) > 0) {
$glue = QueryGenerator::$AND;
}
$queryGenerator->parseAdvFilterList($searchParams, $glue);
$searchKey = $this->get('search_key');
$searchValue = $this->get('search_value');
$operator = $this->get('operator');
if (!empty($searchKey)) {
$queryGenerator->addUserSearchConditions(array('search_field' => $searchKey, 'search_text' => $searchValue, 'operator' => $operator));
}
$orderBy = $this->getForSql('orderby');
$sortOrder = $this->getForSql('sortorder');
//List view will be displayed on recently created/modified records
if (empty($orderBy) && empty($sortOrder) && $moduleName != "Users") {
$orderBy = 'modifiedtime';
$sortOrder = 'DESC';
}
if (!empty($orderBy)) {
$columnFieldMapping = $moduleModel->getColumnFieldMapping();
$orderByFieldName = $columnFieldMapping[$orderBy];
$orderByFieldModel = $moduleModel->getField($orderByFieldName);
if ($orderByFieldModel && $orderByFieldModel->getFieldDataType() == Vtiger_Field_Model::REFERENCE_TYPE) {
//IF it is reference add it in the where fields so that from clause will be having join of the table
$queryGenerator = $this->get('query_generator');
$queryGenerator->addWhereField($orderByFieldName);
}
}
if (!empty($orderBy) && $orderBy === 'smownerid') {
$fieldModel = Vtiger_Field_Model::getInstance('assigned_user_id', $moduleModel);
if ($fieldModel->getFieldDataType() == 'owner') {
$orderBy = 'COALESCE(CONCAT(vtiger_users.first_name,vtiger_users.last_name),vtiger_groups.groupname)';
}
}
$listQuery = $this->getQuery();
$request = new Vtiger_Request($_REQUEST, $_REQUEST);
$potential_id = $this->get('potential_id');
if (Settings_SalesProcesses_Module_Model::checkRelatedToPotentialsLimit() && Settings_SalesProcesses_Module_Model::isLimitForModule($request->get('module'))) {
if (empty($potential_id)) {
$potential_id = $this->get('potentialid');
if ($potential_id == '') {
$potential_id = -1;
}
}
$newListQuery = '';
$explodedListQuery = explode('INNER JOIN', $listQuery);
foreach ($explodedListQuery as $key => $value) {
$newListQuery .= 'INNER JOIN' . $value;
if ($key == 0 && $moduleName == 'Products') {
$newListQuery .= ' INNER JOIN vtiger_seproductsrel AS seproductsrel ON vtiger_products.productid = seproductsrel.productid ';
} elseif ($key == 0 && $moduleName == 'Services') {
$newListQuery .= ' INNER JOIN vtiger_crmentityrel ON (vtiger_crmentityrel.relcrmid = vtiger_service.serviceid OR vtiger_crmentityrel.crmid = vtiger_service.serviceid) ';
}
}
$newListQuery = trim($newListQuery, 'INNER JOIN');
if ($moduleName == 'Products') {
$newListQuery .= " AND seproductsrel.crmid = '{$potential_id}' ";
} elseif ($moduleName == 'Services') {
$newListQuery .= " AND ( (vtiger_crmentityrel.crmid = '{$potential_id}' AND module = 'Potentials') OR (vtiger_crmentityrel.relcrmid = '{$potential_id}' AND relmodule = 'Potentials')) ";
}
$listQuery = $newListQuery;
}
if ($this->get('subProductsPopup')) {
$listQuery = $this->addSubProductsQuery($listQuery);
}
$sourceModule = $this->get('src_module');
$sourceField = $this->get('src_field');
if (!empty($sourceModule)) {
if (method_exists($moduleModel, 'getQueryByModuleField')) {
$overrideQuery = $moduleModel->getQueryByModuleField($sourceModule, $sourceField, $this->get('src_record'), $listQuery);
if (!empty($overrideQuery)) {
$listQuery = $overrideQuery;
}
}
}
$startIndex = $pagingModel->getStartIndex();
$pageLimit = $pagingModel->getPageLimit();
if (!empty($orderBy)) {
if ($orderByFieldModel && $orderByFieldModel->getFieldDataType() == Vtiger_Field_Model::REFERENCE_TYPE) {
$referenceModules = $orderByFieldModel->getReferenceList();
$referenceNameFieldOrderBy = array();
foreach ($referenceModules as $referenceModuleName) {
$referenceModuleModel = Vtiger_Module_Model::getInstance($referenceModuleName);
$referenceNameFields = $referenceModuleModel->getNameFields();
$columnList = array();
foreach ($referenceNameFields as $nameField) {
$fieldModel = $referenceModuleModel->getField($nameField);
$columnList[] = $fieldModel->get('table') . $orderByFieldModel->getName() . '.' . $fieldModel->get('column');
}
if (count($columnList) > 1) {
$referenceNameFieldOrderBy[] = getSqlForNameInDisplayFormat(array('first_name' => $columnList[0], 'last_name' => $columnList[1]), 'Users') . ' ' . $sortOrder;
} else {
$referenceNameFieldOrderBy[] = implode('', $columnList) . ' ' . $sortOrder;
}
}
$listQuery .= ' ORDER BY ' . implode(',', $referenceNameFieldOrderBy);
} else {
$listQuery .= ' ORDER BY ' . $orderBy . ' ' . $sortOrder;
}
}
$viewid = ListViewSession::getCurrentView($moduleName);
if (empty($viewid)) {
$viewid = $pagingModel->get('viewid');
}
$_SESSION['lvs'][$moduleName][$viewid]['start'] = $pagingModel->get('page');
ListViewSession::setSessionQuery($moduleName, $listQuery, $viewid);
//For Products popup in Price Book Related list
if ($sourceModule !== 'PriceBooks' && $sourceField !== 'priceBookRelatedList') {
$listQuery .= " LIMIT {$startIndex}," . ($pageLimit + 1);
}
$listResult = $db->pquery($listQuery, array());
$listViewRecordModels = array();
$listViewEntries = $listViewContoller->getListViewRecords($moduleFocus, $moduleName, $listResult);
$pagingModel->calculatePageRange($listViewEntries);
if ($db->num_rows($listResult) > $pageLimit && $sourceModule !== 'PriceBooks' && $sourceField !== 'priceBookRelatedList') {
array_pop($listViewEntries);
$pagingModel->set('nextPageExists', true);
} else {
$pagingModel->set('nextPageExists', false);
}
$index = 0;
foreach ($listViewEntries as $recordId => $record) {
$rawData = $db->query_result_rowdata($listResult, $index++);
$record['id'] = $recordId;
$listViewRecordModels[$recordId] = $moduleModel->getRecordFromArray($record, $rawData);
$listViewRecordModels[$recordId]->lockEditView = Users_Privileges_Model::checkLockEdit($moduleName, $recordId);
$listViewRecordModels[$recordId]->isPermittedToEditView = Users_Privileges_Model::isPermitted($moduleName, 'EditView', $recordId);
$listViewRecordModels[$recordId]->colorList = Settings_DataAccess_Module_Model::executeColorListHandlers($moduleName, $recordId, $listViewRecordModels[$recordId]);
}
return $listViewRecordModels;
}
/**
* Function to get the list view entries
* @param Vtiger_Paging_Model $pagingModel
* @return <Array> - Associative array of record id mapped to Vtiger_Record_Model instance.
*/
public function getListViewEntries($pagingModel)
{
$db = PearDatabase::getInstance();
$moduleName = $this->getModule()->get('name');
$moduleFocus = CRMEntity::getInstance($moduleName);
$moduleModel = Vtiger_Module_Model::getInstance($moduleName);
$queryGenerator = $this->get('query_generator');
$listViewContoller = $this->get('listview_controller');
$searchKey = $this->get('search_key');
$searchValue = $this->get('search_value');
$operator = $this->get('operator');
if (!empty($searchKey)) {
$queryGenerator->addUserSearchConditions(array('search_field' => $searchKey, 'search_text' => $searchValue, 'operator' => $operator));
}
$orderBy = $this->getForSql('orderby');
$sortOrder = $this->getForSql('sortorder');
//List view will be displayed on recently created/modified records
if (empty($orderBy) && empty($sortOrder) && $moduleName != "Users") {
$orderBy = 'modifiedtime';
$sortOrder = 'DESC';
}
if (!empty($orderBy)) {
$columnFieldMapping = $moduleModel->getColumnFieldMapping();
$orderByFieldName = $columnFieldMapping[$orderBy];
$orderByFieldModel = $moduleModel->getField($orderByFieldName);
if ($orderByFieldModel && $orderByFieldModel->getFieldDataType() == Vtiger_Field_Model::REFERENCE_TYPE) {
//IF it is reference add it in the where fields so that from clause will be having join of the table
$queryGenerator = $this->get('query_generator');
$queryGenerator->addWhereField($orderByFieldName);
//$queryGenerator->whereFields[] = $orderByFieldName;
}
}
//To combine date and time fields for sorting
if ($orderBy == 'date_start') {
$orderBy = "str_to_date(concat(date_start,time_start),'%Y-%m-%d %H:%i:%s')";
} else {
if ($orderBy == 'due_date') {
$orderBy = "str_to_date(concat(due_date,time_end),'%Y-%m-%d %H:%i:%s')";
}
}
$listQuery = $this->getQuery();
$sourceModule = $this->get('src_module');
if (!empty($sourceModule)) {
if (method_exists($moduleModel, 'getQueryByModuleField')) {
$overrideQuery = $moduleModel->getQueryByModuleField($sourceModule, $this->get('src_field'), $this->get('src_record'), $listQuery);
if (!empty($overrideQuery)) {
$listQuery = $overrideQuery;
}
}
}
$startIndex = $pagingModel->getStartIndex();
$pageLimit = $pagingModel->getPageLimit();
if (!empty($orderBy)) {
if ($orderByFieldModel && $orderByFieldModel->isReferenceField()) {
$referenceModules = $orderByFieldModel->getReferenceList();
$referenceNameFieldOrderBy = array();
foreach ($referenceModules as $referenceModuleName) {
$referenceModuleModel = Vtiger_Module_Model::getInstance($referenceModuleName);
$referenceNameFields = $referenceModuleModel->getNameFields();
$columnList = array();
foreach ($referenceNameFields as $nameField) {
$fieldModel = $referenceModuleModel->getField($nameField);
$columnList[] = $fieldModel->get('table') . $orderByFieldModel->getName() . '.' . $fieldModel->get('column');
}
if (count($columnList) > 1) {
$referenceNameFieldOrderBy[] = getSqlForNameInDisplayFormat(array('first_name' => $columnList[0], 'last_name' => $columnList[1]), 'Users') . ' ' . $sortOrder;
} else {
$referenceNameFieldOrderBy[] = implode('', $columnList) . ' ' . $sortOrder;
}
}
$listQuery .= ' ORDER BY ' . implode(',', $referenceNameFieldOrderBy);
} else {
$listQuery .= ' ORDER BY ' . $orderBy . ' ' . $sortOrder;
}
}
$viewid = ListViewSession::getCurrentView($moduleName);
ListViewSession::setSessionQuery($moduleName, $listQuery, $viewid);
$listQueryWithNoLimit = $listQuery;
$listQuery .= " LIMIT {$startIndex}," . ($pageLimit + 1);
$listResult = $db->pquery($listQuery, array());
$listViewRecordModels = array();
$listViewEntries = $listViewContoller->getListViewRecords($moduleFocus, $moduleName, $listResult);
$pagingModel->calculatePageRange($listViewEntries);
if ($db->num_rows($listResult) > $pageLimit) {
array_pop($listViewEntries);
$pagingModel->set('nextPageExists', true);
} else {
$pagingModel->set('nextPageExists', false);
}
$index = 0;
foreach ($listViewEntries as $recordId => $record) {
$rawData = $db->query_result_rowdata($listResult, $index++);
$record['id'] = $recordId;
$listViewRecordModels[$recordId] = $moduleModel->getRecordFromArray($record, $rawData);
}
return $listViewRecordModels;
}
/**
* Function to get the list view entries
* @param Vtiger_Paging_Model $pagingModel
* @return <Array> - Associative array of record id mapped to Vtiger_Record_Model instance.
*/
public function getListViewEntries($pagingModel, $searchResult = false)
{
$db = PearDatabase::getInstance();
$moduleName = $this->getModule()->get('name');
$moduleFocus = CRMEntity::getInstance($moduleName);
$moduleModel = Vtiger_Module_Model::getInstance($moduleName);
$currentUser = Users_Record_Model::getCurrentUserModel();
$queryGenerator = $this->get('query_generator');
$listViewContoller = $this->get('listview_controller');
$listViewFields = array('visibility', 'assigned_user_id', 'activitystatus');
$queryGenerator->setFields(array_unique(array_merge($queryGenerator->getFields(), $listViewFields)));
$searchParams = $this->get('search_params');
if (empty($searchParams)) {
$searchParams = array();
}
$glue = "";
if (count($queryGenerator->getWhereFields()) > 0 && count($searchParams) > 0) {
$glue = QueryGenerator::$AND;
}
$queryGenerator->parseAdvFilterList($searchParams, $glue);
$searchKey = $this->get('search_key');
$searchValue = $this->get('search_value');
$operator = $this->get('operator');
if (!empty($searchKey)) {
$queryGenerator->addUserSearchConditions(array('search_field' => $searchKey, 'search_text' => $searchValue, 'operator' => $operator));
}
$orderBy = $this->getForSql('orderby');
$sortOrder = $this->getForSql('sortorder');
//List view will be displayed on recently created/modified records
if (empty($orderBy) && empty($sortOrder) && $moduleName != "Users") {
$orderBy = 'modifiedtime';
$sortOrder = 'DESC';
}
if (!empty($orderBy)) {
$columnFieldMapping = $moduleModel->getColumnFieldMapping();
$orderByFieldName = $columnFieldMapping[$orderBy];
$orderByFieldModel = $moduleModel->getField($orderByFieldName);
if ($orderByFieldModel && $orderByFieldModel->isReferenceField()) {
//IF it is reference add it in the where fields so that from clause will be having join of the table
$queryGenerator = $this->get('query_generator');
$queryGenerator->setConditionField($orderByFieldName);
//$queryGenerator->whereFields[] = $orderByFieldName;
}
}
if (!empty($orderBy) && $orderBy === 'smownerid') {
$fieldModel = Vtiger_Field_Model::getInstance('assigned_user_id', $moduleModel);
if ($fieldModel->getFieldDataType() == 'owner') {
$orderBy = 'COALESCE(' . getSqlForNameInDisplayFormat(['first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'], 'Users', '') . ', vtiger_groups.groupname)';
}
}
//To combine date and time fields for sorting
if ($orderBy == 'date_start') {
$orderBy = "str_to_date(concat(date_start,time_start),'%Y-%m-%d %H:%i:%s')";
} else {
if ($orderBy == 'due_date') {
$orderBy = "str_to_date(concat(due_date,time_end),'%Y-%m-%d %H:%i:%s')";
}
}
$listQuery = $this->getQuery();
if ($searchResult && $searchResult != '' && is_array($searchResult)) {
$listQuery .= " AND vtiger_crmentity.crmid IN (" . implode(', ', $searchResult) . ") ";
}
unset($searchResult);
$sourceModule = $this->get('src_module');
if (!empty($sourceModule)) {
if (method_exists($moduleModel, 'getQueryByModuleField')) {
$overrideQuery = $moduleModel->getQueryByModuleField($sourceModule, $this->get('src_field'), $this->get('src_record'), $listQuery);
if (!empty($overrideQuery)) {
$listQuery = $overrideQuery;
}
}
}
$startIndex = $pagingModel->getStartIndex();
$pageLimit = $pagingModel->getPageLimit();
if (!empty($orderBy)) {
if ($orderByFieldModel && $orderByFieldModel->isReferenceField()) {
$referenceModules = $orderByFieldModel->getReferenceList();
$referenceNameFieldOrderBy = array();
foreach ($referenceModules as $referenceModuleName) {
$referenceModuleModel = Vtiger_Module_Model::getInstance($referenceModuleName);
$referenceNameFields = $referenceModuleModel->getNameFields();
$columnList = array();
foreach ($referenceNameFields as $nameField) {
$fieldModel = $referenceModuleModel->getField($nameField);
$columnList[] = $fieldModel->get('table') . $orderByFieldModel->getName() . '.' . $fieldModel->get('column');
}
if (count($columnList) > 1) {
$referenceNameFieldOrderBy[] = getSqlForNameInDisplayFormat(array('first_name' => $columnList[0], 'last_name' => $columnList[1]), 'Users') . ' ' . $sortOrder;
} else {
$referenceNameFieldOrderBy[] = implode('', $columnList) . ' ' . $sortOrder;
}
}
$listQuery .= ' ORDER BY ' . implode(', ', $referenceNameFieldOrderBy);
} else {
$listQuery .= ' ORDER BY ' . $orderBy . ' ' . $sortOrder;
}
}
$viewid = ListViewSession::getCurrentView($moduleName);
if (empty($viewid)) {
$viewid = $pagingModel->get('viewid');
}
$_SESSION['lvs'][$moduleName][$viewid]['start'] = $pagingModel->get('page');
ListViewSession::setSessionQuery($moduleName, $listQuery, $viewid);
$listQueryWithNoLimit = $listQuery;
$listQuery .= " LIMIT {$startIndex}," . ($pageLimit + 1);
$listResult = $db->pquery($listQuery, array());
$listViewRecordModels = array();
$listViewEntries = $listViewContoller->getListViewRecords($moduleFocus, $moduleName, $listResult);
$pagingModel->calculatePageRange($listViewEntries);
if ($db->num_rows($listResult) > $pageLimit) {
array_pop($listViewEntries);
$pagingModel->set('nextPageExists', true);
} else {
$pagingModel->set('nextPageExists', false);
}
$groupsIds = Vtiger_Util_Helper::getGroupsIdsForUsers($currentUser->getId());
$index = 0;
foreach ($listViewEntries as $recordId => $record) {
$rawData = $db->query_result_rowdata($listResult, $index++);
$visibleFields = array('activitytype', 'date_start', 'due_date', 'assigned_user_id', 'visibility', 'smownerid');
$ownerId = $rawData['smownerid'];
$visibility = true;
if (in_array($ownerId, $groupsIds)) {
$visibility = false;
} else {
if ($ownerId == $currentUser->getId()) {
$visibility = false;
}
}
if (!$currentUser->isAdminUser() && $rawData['activitytype'] != 'Task' && $rawData['visibility'] == 'Private' && $ownerId && $visibility) {
foreach ($record as $data => $value) {
if (in_array($data, $visibleFields) != -1) {
unset($rawData[$data]);
unset($record[$data]);
}
}
$record['subject'] = vtranslate('Busy', 'Events') . '*';
}
if ($record['activitytype'] == 'Task') {
unset($record['visibility']);
unset($rawData['visibility']);
}
$record['id'] = $recordId;
$listViewRecordModels[$recordId] = $moduleModel->getRecordFromArray($record, $rawData);
$listViewRecordModels[$recordId]->lockEditView = Users_Privileges_Model::checkLockEdit($moduleName, $recordId);
$listViewRecordModels[$recordId]->isPermittedToEditView = Users_Privileges_Model::isPermitted($moduleName, 'EditView ', $recordId);
$listViewRecordModels[$recordId]->colorList = Settings_DataAccess_Module_Model::executeColorListHandlers($moduleName, $recordId, $listViewRecordModels[$recordId]);
}
return $listViewRecordModels;
}
/**
* Function to get the list view entries
* @param Vtiger_Paging_Model $pagingModel
* @return <Array> - Associative array of record id mapped to Vtiger_Record_Model instance.
*/
public function getListViewEntries($pagingModel)
{
$db = PearDatabase::getInstance();
$moduleName = $this->getModule()->get('name');
$moduleFocus = CRMEntity::getInstance($moduleName);
$moduleModel = Vtiger_Module_Model::getInstance($moduleName);
$queryGenerator = $this->get('query_generator');
$listViewContoller = $this->get('listview_controller');
$folderKey = $this->get('folder_id');
$folderValue = $this->get('folder_value');
if (!empty($folderValue)) {
$queryGenerator->addCondition($folderKey, $folderValue, 'e');
}
$searchParams = $this->get('search_params');
if (empty($searchParams)) {
$searchParams = array();
}
$glue = "";
if (count($queryGenerator->getWhereFields()) > 0 && count($searchParams) > 0) {
$glue = QueryGenerator::$AND;
}
$queryGenerator->parseAdvFilterList($searchParams, $glue);
$searchKey = $this->get('search_key');
$searchValue = $this->get('search_value');
$operator = $this->get('operator');
if (!empty($searchKey)) {
$queryGenerator->addUserSearchConditions(array('search_field' => $searchKey, 'search_text' => $searchValue, 'operator' => $operator));
}
$orderBy = $this->getForSql('orderby');
$sortOrder = $this->getForSql('sortorder');
//List view will be displayed on recently created/modified records
if (empty($orderBy) && empty($sortOrder) && $moduleName != "Users") {
$orderBy = 'modifiedtime';
$sortOrder = 'DESC';
}
if (!empty($orderBy)) {
$columnFieldMapping = $moduleModel->getColumnFieldMapping();
$orderByFieldName = $columnFieldMapping[$orderBy];
$orderByFieldModel = $moduleModel->getField($orderByFieldName);
if ($orderByFieldModel && $orderByFieldModel->getFieldDataType() == Vtiger_Field_Model::REFERENCE_TYPE) {
//IF it is reference add it in the where fields so that from clause will be having join of the table
$queryGenerator = $this->get('query_generator');
$queryGenerator->addWhereField($orderByFieldName);
//$queryGenerator->whereFields[] = $orderByFieldName;
}
}
if (!empty($orderBy) && $orderBy === 'smownerid') {
$fieldModel = Vtiger_Field_Model::getInstance('assigned_user_id', $moduleModel);
if ($fieldModel->getFieldDataType() == 'owner') {
$orderBy = 'COALESCE(CONCAT(vtiger_users.first_name,vtiger_users.last_name),vtiger_groups.groupname)';
}
}
$listQuery = $this->getQuery();
$sourceModule = $this->get('src_module');
if (!empty($sourceModule)) {
if (method_exists($moduleModel, 'getQueryByModuleField')) {
$overrideQuery = $moduleModel->getQueryByModuleField($sourceModule, $this->get('src_field'), $this->get('src_record'), $listQuery);
if (!empty($overrideQuery)) {
$listQuery = $overrideQuery;
}
}
}
$startIndex = $pagingModel->getStartIndex();
$pageLimit = $pagingModel->getPageLimit();
if (!empty($orderBy)) {
if ($orderByFieldModel && $orderByFieldModel->isReferenceField()) {
$referenceModules = $orderByFieldModel->getReferenceList();
$referenceNameFieldOrderBy = array();
foreach ($referenceModules as $referenceModuleName) {
$referenceModuleModel = Vtiger_Module_Model::getInstance($referenceModuleName);
$referenceNameFields = $referenceModuleModel->getNameFields();
$columnList = array();
foreach ($referenceNameFields as $nameField) {
$fieldModel = $referenceModuleModel->getField($nameField);
$columnList[] = $fieldModel->get('table') . $orderByFieldModel->getName() . '.' . $fieldModel->get('column');
}
if (count($columnList) > 1) {
$referenceNameFieldOrderBy[] = getSqlForNameInDisplayFormat(array('first_name' => $columnList[0], 'last_name' => $columnList[1]), 'Users') . ' ' . $sortOrder;
} else {
$referenceNameFieldOrderBy[] = implode('', $columnList) . ' ' . $sortOrder;
}
}
$listQuery .= ' ORDER BY ' . implode(',', $referenceNameFieldOrderBy);
} else {
$listQuery .= ' ORDER BY ' . $orderBy . ' ' . $sortOrder;
}
}
$viewid = ListViewSession::getCurrentView($moduleName);
if (empty($viewid)) {
$viewid = $pagingModel->get('viewid');
}
$_SESSION['lvs'][$moduleName][$viewid]['start'] = $pagingModel->get('page');
ListViewSession::setSessionQuery($moduleName, $listQuery, $viewid);
$listQuery .= " LIMIT {$startIndex}," . ($pageLimit + 1);
$listResult = $db->pquery($listQuery, array());
$listViewRecordModels = array();
$listViewEntries = $listViewContoller->getListViewRecords($moduleFocus, $moduleName, $listResult);
$pagingModel->calculatePageRange($listViewEntries);
if ($db->num_rows($listResult) > $pageLimit) {
array_pop($listViewEntries);
$pagingModel->set('nextPageExists', true);
} else {
$pagingModel->set('nextPageExists', false);
}
$index = 0;
foreach ($listViewEntries as $recordId => $record) {
$rawData = $db->query_result_rowdata($listResult, $index++);
$record['id'] = $recordId;
$listViewRecordModels[$recordId] = $moduleModel->getRecordFromArray($record, $rawData);
}
return $listViewRecordModels;
}
public function handleFilter($handlerType, $parameter)
{
$adb = \PearDatabase::getInstance();
switch ($handlerType) {
case "vtiger.filter.process.customview.save.action.before":
/**
* @var $request Vtiger_Request
*/
$request = $parameter[1];
if (!empty($_POST['order_col'])) {
$sql = 'UPDATE vtiger_customview SET order_col=?, order_dir=?,order_numeric_check=?, order_numeric=? WHERE cvid=?';
$params = array($request->get('order_col'), $request->get('order_dir'), intval($request->get('order_numeric_check')), $request->get('order_numeric'), intval($request->get('record')));
$adb->pquery($sql, $params);
// var_dump($adb->convert2Sql($sql, $params));
}
break;
case "vtiger.filter.listview.orderby":
/**
* @var $queryGenerator QueryGenerator
*/
$queryGenerator = func_get_arg(2);
$viewId = ListViewSession::getCurrentView($queryGenerator->getModule());
if (!empty($viewId)) {
$sql = 'SELECT order_col, order_dir, order_numeric_check, order_numeric FROM vtiger_customview WHERE cvid = ?';
$result = $adb->pquery($sql, array($viewId));
if ($adb->num_rows($result) > 0) {
$data = $adb->fetchByAssoc($result);
if (!empty($data['order_col'])) {
$parameter[0] = $data['order_col'];
$parameter[1] = $data['order_dir'];
}
}
}
// var_dump($queryGenerator);
// $queryGenerator->
// var_dump($queryGenerator);
break;
case "vtiger.filter.process.customview.editajax.view.before":
$recordId = $parameter[1]->get('record');
self::$DATA['recordId'] = $recordId;
self::$DATA['moduleName'] = $parameter[1]->get('source_module');
ob_start();
break;
}
return $parameter;
}
/**
* Function to get the list view entries
* @param Vtiger_Paging_Model $pagingModel
* @return <Array> - Associative array of record id mapped to Vtiger_Record_Model instance.
*/
public function getListViewEntries($pagingModel)
{
global $current_user;
$db = PearDatabase::getInstance();
$moduleName = $this->getModule()->get('name');
$moduleFocus = CRMEntity::getInstance($moduleName);
$moduleModel = Vtiger_Module_Model::getInstance($moduleName);
$queryGenerator = $this->get('query_generator');
$listViewContoller = $this->get('listview_controller');
$searchKey = $this->get('search_key');
$searchValue = $this->get('search_value');
$operator = $this->get('operator');
if (!empty($searchKey)) {
$queryGenerator->addUserSearchConditions(array('search_field' => $searchKey, 'search_text' => $searchValue, 'operator' => $operator));
}
$orderBy = $this->getForSql('orderby');
$sortOrder = $this->getForSql('sortorder');
//List view will be displayed on recently created/modified records
if (empty($orderBy) && empty($sortOrder) && $moduleName != "Users") {
$orderBy = 'modifiedtime';
$orderBy = 'createdtime';
$sortOrder = 'DESC';
}
if (!empty($orderBy)) {
$columnFieldMapping = $moduleModel->getColumnFieldMapping();
$orderByFieldName = $columnFieldMapping[$orderBy];
$orderByFieldModel = $moduleModel->getField($orderByFieldName);
if ($orderByFieldModel && $orderByFieldModel->getFieldDataType() == Vtiger_Field_Model::REFERENCE_TYPE) {
//IF it is reference add it in the where fields so that from clause will be having join of the table
$queryGenerator = $this->get('query_generator');
$queryGenerator->addWhereField($orderByFieldName);
//$queryGenerator->whereFields[] = $orderByFieldName;
}
}
$listQuery = $this->getQuery();
$sourceModule = $this->get('src_module');
if (!empty($sourceModule)) {
if (method_exists($moduleModel, 'getQueryByModuleField')) {
$overrideQuery = $moduleModel->getQueryByModuleField($sourceModule, $this->get('src_field'), $this->get('src_record'), $listQuery);
if (!empty($overrideQuery)) {
$listQuery = $overrideQuery;
}
}
}
$startIndex = $pagingModel->getStartIndex();
$pageLimit = $pagingModel->getPageLimit();
//Modified by jmangarret 16jun2015
if ($current_user->roleid == "H9") {
$listQuery .= ' AND smownerid=' . $current_user->id;
}
//Modified by jmangarret 13ene2016 popUP seleccionar localizadores desde Registro de Ventas
if ($moduleName == "Localizadores" && $sourceModule == "RegistroDeVentas") {
$listQuery .= ' AND vtiger_localizadores.procesado=0';
}
//Modified by jmangarret 03feb2016 popUP seleccionar cuentas desde Comsiones Satelites
if ($moduleName == "Accounts" && $sourceModule == "ComisionSatelites") {
$listQuery .= ' AND vtiger_account.account_type="Satelite"';
}
if (!empty($orderBy)) {
if ($orderByFieldModel && $orderByFieldModel->isReferenceField()) {
$referenceModules = $orderByFieldModel->getReferenceList();
$referenceNameFieldOrderBy = array();
foreach ($referenceModules as $referenceModuleName) {
$referenceModuleModel = Vtiger_Module_Model::getInstance($referenceModuleName);
$referenceNameFields = $referenceModuleModel->getNameFields();
$columnList = array();
foreach ($referenceNameFields as $nameField) {
$fieldModel = $referenceModuleModel->getField($nameField);
$columnList[] = $fieldModel->get('table') . $orderByFieldModel->getName() . '.' . $fieldModel->get('column');
}
if (count($columnList) > 1) {
$referenceNameFieldOrderBy[] = getSqlForNameInDisplayFormat(array('first_name' => $columnList[0], 'last_name' => $columnList[1]), 'Users') . ' ' . $sortOrder;
} else {
$referenceNameFieldOrderBy[] = implode('', $columnList) . ' ' . $sortOrder;
}
}
$listQuery .= ' ORDER BY ' . implode(',', $referenceNameFieldOrderBy);
} else {
$listQuery .= ' ORDER BY ' . $orderBy . ' ' . $sortOrder;
}
}
$viewid = ListViewSession::getCurrentView($moduleName);
ListViewSession::setSessionQuery($moduleName, $listQuery, $viewid);
$listQuery .= " LIMIT {$startIndex}," . ($pageLimit + 1);
$listResult = $db->pquery($listQuery, array());
$listViewRecordModels = array();
$listViewEntries = $listViewContoller->getListViewRecords($moduleFocus, $moduleName, $listResult);
$pagingModel->calculatePageRange($listViewEntries);
if ($db->num_rows($listResult) > $pageLimit) {
array_pop($listViewEntries);
$pagingModel->set('nextPageExists', true);
} else {
$pagingModel->set('nextPageExists', false);
}
$index = 0;
foreach ($listViewEntries as $recordId => $record) {
$rawData = $db->query_result_rowdata($listResult, $index++);
$record['id'] = $recordId;
$listViewRecordModels[$recordId] = $moduleModel->getRecordFromArray($record, $rawData);
}
return $listViewRecordModels;
}
