/** * Function to get relation query for particular module with function name * @param <record> $recordId * @param <String> $functionName * @param Vtiger_Module_Model $relatedModule * @return <String> */ public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false) { if ($functionName === 'get_activities') { $focus = CRMEntity::getInstance($this->getName()); $focus->id = $recordId; $entityIds = $focus->getRelatedContactsIds(); $entityIds[] = $recordId; $entityIds = implode(',', $entityIds); $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, \n\t\t\t\t\t\tvtiger_activity.status AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.link IN (" . $entityIds . ')'; $time = vtlib_purify($_REQUEST['time']); if ($time == 'current') { $stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('current'); $query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))"; } if ($time == 'history') { $stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('history'); $query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))"; } $relatedModuleName = $relatedModule->getName(); $query .= $this->getSpecificRelationQuery($relatedModuleName); $instance = CRMEntity::getInstance($relatedModuleName); $securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName, false, $recordId); if ($securityParameter != '') { $query .= $securityParameter; } // There could be more than one contact for an activity. $query .= ' GROUP BY vtiger_activity.activityid'; } elseif ($functionName === 'get_mails' && $relatedModule->getName() == 'OSSMailView') { $query = OSSMailView_Record_Model::getMailsQuery($recordId, $relatedModule->getName()); } else { $query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel); } return $query; }
/** * 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(); $module = $this->getModule(); $moduleName = $module->getName(); $parentModuleName = $module->getParentName(); $qualifiedModuleName = $moduleName; if (!empty($parentModuleName)) { $qualifiedModuleName = $parentModuleName . ':' . $qualifiedModuleName; } $recordModelClass = Vtiger_Loader::getComponentClassName('Model', 'Record', $qualifiedModuleName); $listQuery = $this->getBasicListQuery(); $startIndex = $pagingModel->getStartIndex(); $pageLimit = $pagingModel->getPageLimit(); $orderBy = $this->getForSql('orderby'); 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)'; } } if (!empty($orderBy)) { $listQuery .= ' ORDER BY ' . $orderBy . ' ' . $this->getForSql('sortorder'); } if ($module->isPagingSupported()) { $nextListQuery = $listQuery . ' LIMIT ' . ($startIndex + $pageLimit) . ',1'; $listQuery .= " LIMIT $startIndex, $pageLimit"; } $listResult = $db->pquery($listQuery, array()); $noOfRecords = $db->num_rows($listResult); $listViewRecordModels = array(); for ($i = 0; $i < $noOfRecords; ++$i) { $row = $db->query_result_rowdata($listResult, $i); $record = new $recordModelClass(); $record->setData($row); if (method_exists($record, 'getModule') && method_exists($record, 'setModule')) { $moduleModel = Settings_Vtiger_Module_Model::getInstance($qualifiedModuleName); $record->setModule($moduleModel); } $listViewRecordModels[$record->getId()] = $record; } if ($module->isPagingSupported()) { $pagingModel->calculatePageRange($listViewRecordModels); $nextPageResult = $db->pquery($nextListQuery, array()); $nextPageNumRows = $db->num_rows($nextPageResult); if ($nextPageNumRows <= 0) { $pagingModel->set('nextPageExists', false); } } return $listViewRecordModels; }
public static function getListRecord($module = NULL, array $columnList) { $db = PearDatabase::getInstance(); $moduleList = array(); if (!$module) { $getListModuleSql = "SELECT name FROM vtiger_tab WHERE isentitytype = 1 AND vtiger_tab.presence != 1"; $getListModuleResult = $db->pquery($getListModuleSql, array(), TRUE); for ($i = 0; $i < $db->getFieldsCount($getListModuleResult); $i++) { $moduleList[] = $db->query_result($getListModuleResult, $i, 'name'); } } else { $moduleList[] = $module; } $recordList = array(); if (!in_array('smownerid', $columnList)) { $columnList[] = 'smownerid'; } for ($i = 0; $i < count($moduleList); $i++) { $getRecordListSql = "SELECT " . implode(',', $columnList) . "," . getSqlForNameInDisplayFormat(['first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'], 'Users') . " as smownerid FROM vtiger_crmentity " . " INNER JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid " . " WHERE was_read = 0 AND vtiger_crmentity.deleted = 0 AND setype = ?"; $getRecordListResult = $db->pquery($getRecordListSql, array($moduleList[$i]), TRUE); for ($k = 0; $k < $db->num_rows($getRecordListResult); $k++) { $singelRecord = array(); foreach ($columnList as $col) { $singelRecord[$col] = $db->query_result($getRecordListResult, $k, $col); } if (!empty($singelRecord)) { $recordList[] = $singelRecord; } } } if (!count($recordList)) { return false; } return $recordList; }
/** * Function to get relation query for particular module with function name * @param <record> $recordId * @param <String> $functionName * @param Vtiger_Module_Model $relatedModule * @return <String> */ public function getRelationQuery($recordId, $functionName, $relatedModule) { if ($functionName === 'get_activities') { $focus = CRMEntity::getInstance($this->getName()); $focus->id = $recordId; $entityIds = $focus->getRelatedContactsIds(); $entityIds = implode(',', $entityIds); $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, vtiger_seactivityrel.crmid AS parent_id,\n\t\t\t\t\t\tCASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.activitytype <> 'Emails'\n\t\t\t\t\t\t\t\tAND (vtiger_seactivityrel.crmid = " . $recordId; if ($entityIds) { $query .= " OR vtiger_cntactivityrel.contactid IN (" . $entityIds . "))"; } else { $query .= ")"; } $relatedModuleName = $relatedModule->getName(); $query .= $this->getSpecificRelationQuery($relatedModuleName); $nonAdminQuery = $this->getNonAdminAccessControlQueryForRelation($relatedModuleName); if ($nonAdminQuery) { $query = appendFromClauseToQuery($query, $nonAdminQuery); } // There could be more than one contact for an activity. $query .= ' GROUP BY vtiger_activity.activityid'; } else { $query = parent::getRelationQuery($recordId, $functionName, $relatedModule); } return $query; }
public function getAllMobileKeys($service, $userid = false) { $adb = PearDatabase::getInstance(); $params = array('Active'); $sql = ''; if ($userid) { $sql .= ' AND vtiger_users.id <> ?'; $params[] = $userid; } if ($service) { $sql .= ' AND yetiforce_mobile_keys.service = ?'; $params[] = $service; } $result = $adb->pquery('SELECT yetiforce_mobile_keys.*, vtiger_users.user_name,'.getSqlForNameInDisplayFormat(['first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'], 'Users').' as fullusername, vtiger_users.id AS userid FROM yetiforce_mobile_keys INNER JOIN vtiger_users ON vtiger_users.id = yetiforce_mobile_keys.user WHERE vtiger_users.status = ? ' . $sql, $params); $rows = $adb->num_rows($result); $keys = Array(); for ($i = 0; $i < $rows; $i++) { $row = $adb->raw_query_result_rowdata($result, $i); $keys[$row['id']] = $row; $keys[$row['id']]['name'] = 'LBL_MOBILE_' . strtoupper($row['service']); $privileges_users = unserialize($row['privileges_users']); $keys[$row['id']]['privileges_users'] = $privileges_users != '' ? $privileges_users : array(); } return $keys; }
/** * Function to get relation query for particular module with function name * @param <record> $recordId * @param <String> $functionName * @param Vtiger_Module_Model $relatedModule * @return <String> */ public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false) { if ($functionName === 'get_activities') { $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility,\n\t\t\t\t\t\tCASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.process = " . $recordId; $time = vtlib_purify($_REQUEST['time']); if ($time == 'current') { $query .= " AND ((vtiger_activity.activitytype='Task' and vtiger_activity.status not in ('Completed','Deferred'))\n\t\t\t\tOR (vtiger_activity.activitytype not in ('Emails','Task') and vtiger_activity.eventstatus not in ('','Held')))"; } if ($time == 'history') { $query .= " AND ((vtiger_activity.activitytype='Task' and vtiger_activity.status in ('Completed','Deferred'))\n\t\t\t\tOR (vtiger_activity.activitytype not in ('Emails','Task') and vtiger_activity.eventstatus in ('','Held')))"; } $relatedModuleName = $relatedModule->getName(); $query .= $this->getSpecificRelationQuery($relatedModuleName); $instance = CRMEntity::getInstance($relatedModuleName); $securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName); if ($securityParameter != '') { $sql .= $securityParameter; } } elseif ($functionName === 'get_mails' && $relatedModule->getName() == 'OSSMailView') { $query = OSSMailView_Record_Model::getMailsQuery($recordId, $relatedModule->getName()); } else { $query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel); } return $query; }
/** * Funtion to get the Login history basic query * @return type */ public function getBasicListQuery() { $module = $this->getModule(); $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT login_id, {$userNameSql} AS user_name, user_ip, logout_time, login_time, vtiger_loginhistory.status FROM {$module->baseTable} \r\n\t\t\t\tINNER JOIN vtiger_users ON vtiger_users.user_name = {$module->baseTable}.user_name"; $search_key = $this->get('search_key'); $value = $this->get('search_value'); if (!empty($search_key) && !empty($value)) { $query .= " WHERE {$module->baseTable}.{$search_key} = '{$value}'"; } return $query; }
/** * Function to get relation query for particular module with function name * @param <record> $recordId * @param <String> $functionName * @param Vtiger_Module_Model $relatedModule * @return <String> */ public function getRelationQuery($recordId, $functionName, $relatedModule) { if ($functionName === 'get_activities') { $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, vtiger_seactivityrel.crmid AS parent_id,\n\t\t\t\t\t\tCASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.activitytype = 'Task'\n\t\t\t\t\t\t\t\tAND vtiger_seactivityrel.crmid = " . $recordId; $relatedModuleName = $relatedModule->getName(); $query .= $this->getSpecificRelationQuery($relatedModuleName); $nonAdminQuery = $this->getNonAdminAccessControlQueryForRelation($relatedModuleName); if ($nonAdminQuery) { $query = appendFromClauseToQuery($query, $nonAdminQuery); } } else { $query = parent::getRelationQuery($recordId, $functionName, $relatedModule); } return $query; }
public function process(Vtiger_Request $request) { $db = PearDatabase::getInstance(); $moduleName = $request->getModule(); $record = $request->get('record'); $load = $request->get('noloadlibs'); $recordModel = Vtiger_Record_Model::getInstanceById($record, $moduleName); $from = $recordModel->get('from_email'); $to = $recordModel->get('to_email'); $to = explode(',', $to); $cc = $recordModel->get('cc_email'); $bcc = $recordModel->get('bcc_email'); $subject = $recordModel->get('subject'); $owner = $recordModel->get('assigned_user_id'); $sent = $recordModel->get('createdtime'); // pobierz zaลฤ czniki $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT case when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name,\n\t\t\t\t'Documents' ActivityType,vtiger_attachments.type FileType,vtiger_crmentity.modifiedtime,\n\t\t\t\tvtiger_seattachmentsrel.attachmentsid attachmentsid, vtiger_notes.notesid crmid, vtiger_notes.notecontent description,vtiger_notes.*\n\t\t\t\tfrom vtiger_notes\n\t\t\t\tLEFT JOIN vtiger_notescf ON vtiger_notescf.notesid= vtiger_notes.notesid\n\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid= vtiger_notes.notesid and vtiger_crmentity.deleted=0\n\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\tLEFT JOIN vtiger_seattachmentsrel ON vtiger_seattachmentsrel.crmid =vtiger_notes.notesid\n\t\t\t\tLEFT JOIN vtiger_attachments ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid\n\t\t\t\tLEFT JOIN vtiger_users ON vtiger_crmentity.smownerid= vtiger_users.id\n\t\t\t\tLEFT JOIN vtiger_ossmailview_files ON vtiger_ossmailview_files.documentsid =vtiger_notes.notesid\n\t\t\t\tWHERE vtiger_ossmailview_files.ossmailviewid = ?"; $params = array($record); $result = $db->pquery($query, $params, true); $num = $db->num_rows($result); $attachments = array(); for ($i = 0; $i < $num; $i++) { $attachments[$i]['name'] = $db->query_result($result, $i, 'title'); $attachments[$i]['file'] = $db->query_result($result, $i, 'filename'); $attachments[$i]['id'] = $db->query_result($result, $i, 'attachmentsid'); $attachments[$i]['docid'] = $db->query_result($result, $i, 'crmid'); } $viewer = $this->getViewer($request); $viewer->assign('MODULENAME', $moduleName); $viewer->assign('NOLOADLIBS', $load); $viewer->assign('FROM', $from); $viewer->assign('TO', $to); $viewer->assign('CC', $cc); $viewer->assign('BCC', $bcc); $viewer->assign('SUBJECT', $subject); $viewer->assign('URL', "index.php?module={$moduleName}&view=mbody&record={$record}"); $viewer->assign('OWNER', $owner); $viewer->assign('SENT', $sent); $viewer->assign('ATTACHMENTS', $attachments); $viewer->assign('RECORD', $record); $viewer->assign('RECORD_MODEL', $recordModel); $viewer->assign('ISMODAL', $request->isAjax()); $viewer->view('preview.tpl', 'OSSMailView'); }
/** * 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) { $reportFolderModel = Reports_Folder_Model::getInstance(); $reportFolderModel->set('folderid', $this->get('folderid')); $orderBy = $this->get('orderby'); 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)'; } } if (!empty($orderBy)) { $reportFolderModel->set('orderby', $orderBy); $reportFolderModel->set('sortby', $this->get('sortorder')); } $reportRecordModels = $reportFolderModel->getReports($pagingModel); $pagingModel->calculatePageRange($reportRecordModels); return $reportRecordModels; }
public function getTimeEmployee($id) { $db = PearDatabase::getInstance(); $usersSqlFullName = getSqlForNameInDisplayFormat(['first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'], 'Users'); //TODO need to handle security $result = $db->pquery('SELECT count(*) AS count, ' . $usersSqlFullName . ' 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 returns Tickets grouped by Status * @param type $data * @return <Array> */ public function getOpenTickets() { $db = PearDatabase::getInstance(); $ticketStatus = Settings_SupportProcesses_Module_Model::getTicketStatusNotModify(); $currentUser = Users_Record_Model::getCurrentUserModel(); $module = 'HelpDesk'; $instance = CRMEntity::getInstance($module); $securityParameter = $instance->getUserAccessConditionsQuerySR($module, $currentUser); $usersSqlFullName = getSqlForNameInDisplayFormat(['first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'], 'Users'); $sql = 'SELECT count(*) AS count, case when (' . $usersSqlFullName . ' not like "") then ' . $usersSqlFullName . ' else vtiger_groups.groupname end as name, case when (' . $usersSqlFullName . ' not like "") then vtiger_users.cal_color else vtiger_groups.color end as color, smownerid as id FROM vtiger_troubletickets INNER JOIN vtiger_crmentity ON vtiger_troubletickets.ticketid = vtiger_crmentity.crmid LEFT JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid LEFT JOIN vtiger_groups on vtiger_groups.groupid=vtiger_crmentity.smownerid WHERE vtiger_crmentity.deleted = 0'; if ($securityParameter != '') { $sql .= $securityParameter; } if (!empty($ticketStatus)) { $ticketStatusSearch = implode("','", $ticketStatus); $sql .= " AND vtiger_troubletickets.status NOT IN ('{$ticketStatusSearch}')"; } $sql .= ' GROUP BY smownerid'; $result = $db->pquery($sql, array()); $moduleModel = Vtiger_Module_Model::getInstance('HelpDesk'); $listViewUrl = $moduleModel->getListViewUrl(); $chartData = array(); for ($i = 0; $i < $db->num_rows($result); $i++) { $row = $db->query_result_rowdata($result, $i); $data['id'] = $row['id']; $data['label'] = trim($row['name']); $data['data'] = $row['count']; $data['color'] = $row['color']; $data['links'] = $listViewUrl . $this->getSearchParams($row['name']); $chartData[] = $data; } return $chartData; }
/** * Function to get relation query for particular module with function name * @param <record> $recordId * @param <String> $functionName * @param Vtiger_Module_Model $relatedModule * @return <String> */ public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false) { if ($functionName === 'get_activities') { $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN $userNameSql ELSE vtiger_groups.groupname END AS user_name, vtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start, vtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, CASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status FROM vtiger_activity INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid WHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.process = " . $recordId; $time = vtlib_purify($_REQUEST['time']); if ($time == 'current') { $stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('current'); $query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))"; } if ($time == 'history') { $stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('history'); $query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))"; } $relatedModuleName = $relatedModule->getName(); $query .= $this->getSpecificRelationQuery($relatedModuleName); $instance = CRMEntity::getInstance($relatedModuleName); $securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName, false, $recordId); if ($securityParameter != '') $query .= $securityParameter; } elseif ($functionName === 'get_mails' && $relatedModule->getName() == 'OSSMailView') { $query = OSSMailView_Record_Model::getMailsQuery($recordId, $relatedModule->getName()); } else { $query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel); } return $query; }
/** * Function to get relation query for particular module with function name * @param <record> $recordId * @param <String> $functionName * @param Vtiger_Module_Model $relatedModule * @return <String> */ public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false) { if ($functionName === 'get_activities') { $focus = CRMEntity::getInstance($this->getName()); $focus->id = $recordId; $entityIds = $focus->getRelatedContactsIds(); $entityIds[] = $recordId; $entityIds = implode(',', $entityIds); $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, \n\t\t\t\t\t\tCASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.link IN (" . $entityIds . ')'; $time = vtlib_purify($_REQUEST['time']); if ($time == 'current') { $query .= " AND ((vtiger_activity.activitytype='Task' and vtiger_activity.status not in ('Completed','Deferred'))\n\t\t\t\tOR (vtiger_activity.activitytype not in ('Emails','Task') and vtiger_activity.eventstatus not in ('','Held')))"; } if ($time == 'history') { $query .= " AND ((vtiger_activity.activitytype='Task' and vtiger_activity.status in ('Completed','Deferred'))\n\t\t\t\tOR (vtiger_activity.activitytype not in ('Emails','Task') and vtiger_activity.eventstatus in ('','Held')))"; } $relatedModuleName = $relatedModule->getName(); $query .= $this->getSpecificRelationQuery($relatedModuleName); $instance = CRMEntity::getInstance($relatedModuleName); $securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName); if ($securityParameter != '') { $query .= $securityParameter; } // There could be more than one contact for an activity. $query .= ' GROUP BY vtiger_activity.activityid'; } elseif ($functionName === 'get_dependents_list' && $relatedModule->getName() == 'OutsourcedProducts') { $query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel); $query .= " OR potential IN (SELECT potentialid FROM vtiger_potential INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_potential.potentialid WHERE vtiger_crmentity.deleted = 0 AND related_to = '{$recordId}')"; } elseif ($functionName === 'get_mails' && $relatedModule->getName() == 'OSSMailView') { $query = OSSMailView_Record_Model::getMailsQuery($recordId, $relatedModule->getName()); } else { $query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel); } return $query; }
/** Function to export the notes in CSV Format * @param reference variable - where condition is passed when the query is executed * Returns Export Documents Query. */ function create_export_query($where) { global $log, $current_user; $log->debug("Entering create_export_query(" . $where . ") method ..."); include "include/utils/ExportUtils.php"; //To get the Permitted fields query and the permitted fields list $sql = getPermittedFieldsQuery("Documents", "detail_view"); $fields_list = getFieldsListFromQuery($sql); $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT {$fields_list}, case when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name" . " FROM vtiger_notes\n\t\t\t\tinner join vtiger_crmentity\n\t\t\t\t\ton vtiger_crmentity.crmid=vtiger_notes.notesid\n\t\t\t\tLEFT JOIN `vtiger_trees_templates_data` on vtiger_notes.folderid=`vtiger_trees_templates_data`.tree\n\t\t\t\tLEFT JOIN vtiger_users ON vtiger_crmentity.smownerid=vtiger_users.id " . " LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid=vtiger_groups.groupid "; $query .= getNonAdminAccessControlQuery('Documents', $current_user); $where_auto = " vtiger_crmentity.deleted=0"; if ($where != "") { $query .= " WHERE ({$where}) AND " . $where_auto; } else { $query .= " WHERE " . $where_auto; } $log->debug("Exiting create_export_query method ..."); return $query; }
function get_project_mail($id, $cur_tab_id, $rel_tab_id, $actions = false) { global $currentModule, $app_strings, $singlepane_view; $this_module = $currentModule; $related_module = vtlib_getModuleNameById($rel_tab_id); $other = CRMEntity::getInstance($related_module); vtlib_setup_modulevars($related_module, $other); $singular_modname = vtlib_toSingular($related_module); $button = ''; if ($actions) { if (is_string($actions)) { $actions = explode(',', strtoupper($actions)); } if (in_array('SELECT', $actions) && isPermitted($related_module, 4, '') == 'yes') { $button .= "<input title='" . getTranslatedString('LBL_SELECT') . " " . getTranslatedString($related_module) . "' class='crmbutton small edit' type='button' onclick=\"return window.open('index.php?module={$related_module}&return_module={$currentModule}&action=Popup&popuptype=detailview&select=enable&form=EditView&form_submit=false&recordid={$id}','test','width=640,height=602,resizable=0,scrollbars=0');\" value='" . getTranslatedString('LBL_SELECT') . " " . getTranslatedString($related_module) . "'> "; } if (in_array('ADD', $actions) && isPermitted($related_module, 1, '') == 'yes') { $button .= "<input type='hidden' name='createmode' id='createmode' value='link' />" . "<input title='" . getTranslatedString('LBL_ADD_NEW') . " " . getTranslatedString($singular_modname) . "' class='crmbutton small create'" . " onclick='this.form.action.value=\"EditView\";this.form.module.value=\"{$related_module}\"' type='submit' name='button'" . " value='" . getTranslatedString('LBL_ADD_NEW') . " " . getTranslatedString($singular_modname) . "'> "; } } if ($singlepane_view == 'true') { $returnset = "&return_module={$this_module}&return_action=DetailView&return_id={$id}"; } else { $returnset = "&return_module={$this_module}&return_action=CallRelatedList&return_id={$id}"; } $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT case when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name,\n\t\t\t\tvtiger_crmentity.*, vtiger_project.*, vtiger_projectcf.*\n\t\t\t\tFROM vtiger_project\n\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_project.projectid\n\t\t\t\tLEFT JOIN vtiger_projectcf ON vtiger_projectcf.projectid = vtiger_project.projectid\n\t\t\t\tLEFT JOIN vtiger_ossmailview_project ON vtiger_ossmailview_project.crmid = vtiger_project.projectid\n\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_ossmailview_project.ossmailviewid = '{$id}'"; $return_value = GetRelatedList($this_module, $related_module, $other, $query, $button, $returnset); if ($return_value == null) { $return_value = array(); } $return_value['CUSTOM_BUTTON'] = $button; return $return_value; }
/** * Invoked when special actions are performed on the module. * @param String Module name * @param String Event Type (module.postinstall, module.disabled, module.enabled, module.preuninstall) */ function get_osstimecontrol($id, $cur_tab_id, $rel_tab_id, $actions = false) { global $log, $singlepane_view, $currentModule, $current_user; $log->debug("Entering get_osstimecontrol(" . $id . ") method ..."); $this_module = $currentModule; $related_module = vtlib_getModuleNameById($rel_tab_id); require_once "modules/{$related_module}/{$related_module}.php"; $other = new $related_module(); vtlib_setup_modulevars($related_module, $other); $singular_modname = vtlib_toSingular($related_module); $record = Vtiger_Record_Model::getInstanceById($id); $userId = $record->get('assigned_user_id'); if ($singlepane_view == 'true') { $returnset = '&return_module=' . $this_module . '&return_action=DetailView&return_id=' . $id; } else { $returnset = '&return_module=' . $this_module . '&return_action=CallRelatedList&return_id=' . $id; } $button = ''; if ($actions && getFieldVisibilityPermission($related_module, $current_user->id, 'parent_id', 'readwrite') == '0') { if (is_string($actions)) { $actions = explode(',', strtoupper($actions)); } if (in_array('SELECT', $actions) && isPermitted($related_module, 4, '') == 'yes') { $button .= "<input title='" . getTranslatedString('LBL_SELECT') . " " . getTranslatedString($related_module) . "' class='crmbutton small edit' type='button' onclick=\"return window.open('index.php?module={$related_module}&return_module={$currentModule}&action=Popup&popuptype=detailview&select=enable&form=EditView&form_submit=false&recordid={$id}','test','width=640,height=602,resizable=0,scrollbars=0');\" value='" . getTranslatedString('LBL_SELECT') . " " . getTranslatedString($related_module) . "'> "; } if (in_array('ADD', $actions) && isPermitted($related_module, 1, '') == 'yes') { $button .= "<input title='" . getTranslatedString('LBL_ADD_NEW') . " " . getTranslatedString($singular_modname) . "' class='crmbutton small create'" . " onclick='this.form.action.value=\"EditView\";this.form.module.value=\"{$related_module}\"' type='submit' name='button'" . " value='" . getTranslatedString('LBL_ADD_NEW') . " " . getTranslatedString($singular_modname) . "'> "; } } $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT case when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name, vtiger_users.id,\n\t\t\t\tvtiger_osstimecontrol.name, vtiger_osstimecontrol.osstimecontrolid as crmid, vtiger_osstimecontrol.osstimecontrol_status, vtiger_osstimecontrol.payment,\n\t\t\t\tvtiger_osstimecontrol.osstimecontrol_no, vtiger_osstimecontrol.date_start, vtiger_osstimecontrol.due_date, vtiger_osstimecontrol.time_end, vtiger_osstimecontrol.sum_time, vtiger_crmentity.smownerid, vtiger_crmentity.modifiedtime\n\t\t\t\tFROM vtiger_osstimecontrol\n\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_osstimecontrol.osstimecontrolid\n\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid\n\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_crmentity.`smownerid`= " . $userId; $return_value = GetRelatedList($this_module, $related_module, $other, $query, $button, $returnset); if ($return_value == null) { $return_value = array(); } $return_value['CUSTOM_BUTTON'] = $button; $log->debug("Exiting get_osstimecontrol method ..."); return $return_value; }
function get_emails($id, $cur_tab_id, $rel_tab_id, $actions = false) { $log = vglobal('log'); $current_user = vglobal('current_user'); $singlepane_view = vglobal('singlepane_view'); $currentModule = vglobal('currentModule'); $log->debug("Entering get_emails(" . $id . ") method ..."); $this_module = $currentModule; $related_module = vtlib_getModuleNameById($rel_tab_id); require_once "modules/{$related_module}/{$related_module}.php"; $other = new $related_module(); vtlib_setup_modulevars($related_module, $other); $singular_modname = vtlib_toSingular($related_module); if ($singlepane_view == 'true') { $returnset = '&return_module=' . $this_module . '&return_action=DetailView&return_id=' . $id; } else { $returnset = '&return_module=' . $this_module . '&return_action=CallRelatedList&return_id=' . $id; } $button = ''; $button .= '<input type="hidden" name="email_directing_module"><input type="hidden" name="record">'; if ($actions) { if (is_string($actions)) { $actions = explode(',', strtoupper($actions)); } if (in_array('SELECT', $actions) && isPermitted($related_module, 4, '') == 'yes') { $button .= "<input title='" . getTranslatedString('LBL_SELECT') . " " . getTranslatedString($related_module) . "' class='crmbutton small edit' type='button' onclick=\"return window.open('index.php?module={$related_module}&return_module={$currentModule}&action=Popup&popuptype=detailview&select=enable&form=EditView&form_submit=false&recordid={$id}','test','width=640,height=602,resizable=0,scrollbars=0');\" value='" . getTranslatedString('LBL_SELECT') . " " . getTranslatedString($related_module) . "'> "; } if (in_array('ADD', $actions) && isPermitted($related_module, 1, '') == 'yes') { $button .= "<input title='" . getTranslatedString('LBL_ADD_NEW') . " " . getTranslatedString($singular_modname) . "' accessyKey='F' class='crmbutton small create' onclick='fnvshobj(this,\"sendmail_cont\");sendmail(\"{$this_module}\",{$id});' type='button' name='button' value='" . getTranslatedString('LBL_ADD_NEW') . " " . getTranslatedString($singular_modname) . "'></td>"; } } $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT vtiger_ossmailview.*, vtiger_crmentity.modifiedtime, vtiger_crmentity.crmid, vtiger_crmentity.smownerid, case when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name FROM vtiger_ossmailview \n\t\t\tINNER JOIN vtiger_ossmailview_relation ON vtiger_ossmailview_relation.ossmailviewid = vtiger_ossmailview.ossmailviewid\n\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_ossmailview.ossmailviewid \n\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid=vtiger_crmentity.smownerid \n\t\t\tLEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id\n\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_ossmailview_relation.crmid = " . $id . " "; $return_value = GetRelatedList($this_module, $related_module, $other, $query, $button, $returnset); if ($return_value == null) { $return_value = array(); } $return_value['CUSTOM_BUTTON'] = $button; $log->debug("Exiting get_emails method ..."); return $return_value; }
/** function used to get the list of invoices which are related to the service * @param int $id - service id * @return array - array which will be returned from the function GetRelatedList */ function get_invoices($id, $cur_tab_id, $rel_tab_id, $actions = false) { global $log, $singlepane_view, $currentModule, $current_user; $log->debug("Entering get_invoices(" . $id . ") method ..."); $this_module = $currentModule; $related_module = vtlib_getModuleNameById($rel_tab_id); require_once "modules/{$related_module}/{$related_module}.php"; $other = new $related_module(); vtlib_setup_modulevars($related_module, $other); $singular_modname = vtlib_toSingular($related_module); $parenttab = getParentTab(); if ($singlepane_view == 'true') { $returnset = '&return_module=' . $this_module . '&return_action=DetailView&return_id=' . $id; } else { $returnset = '&return_module=' . $this_module . '&return_action=CallRelatedList&return_id=' . $id; } $button = ''; if ($actions) { if (is_string($actions)) { $actions = explode(',', strtoupper($actions)); } if (in_array('SELECT', $actions) && isPermitted($related_module, 4, '') == 'yes') { $button .= "<input title='" . getTranslatedString('LBL_SELECT') . " " . getTranslatedString($related_module) . "' class='crmbutton small edit' type='button' onclick=\"return window.open('index.php?module={$related_module}&return_module={$currentModule}&action=Popup&popuptype=detailview&select=enable&form=EditView&form_submit=false&recordid={$id}&parenttab={$parenttab}','test','width=640,height=602,resizable=0,scrollbars=0');\" value='" . getTranslatedString('LBL_SELECT') . " " . getTranslatedString($related_module) . "'> "; } if (in_array('ADD', $actions) && isPermitted($related_module, 1, '') == 'yes') { $button .= "<input title='" . getTranslatedString('LBL_ADD_NEW') . " " . getTranslatedString($singular_modname) . "' class='crmbutton small create'" . " onclick='this.form.action.value=\"EditView\";this.form.module.value=\"{$related_module}\"' type='submit' name='button'" . " value='" . getTranslatedString('LBL_ADD_NEW') . " " . getTranslatedString($singular_modname) . "'> "; } } $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT vtiger_crmentity.*,\n\t\t\tvtiger_invoice.*,\n\t\t\tvtiger_account.accountname,\n\t\t\tcase when (vtiger_users.user_name not like '') then {$userNameSql}\n\t\t\t\telse vtiger_groups.groupname end as user_name\n\t\t\tFROM vtiger_invoice\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_invoice.invoiceid\n\t\t\tLEFT OUTER JOIN vtiger_account\n\t\t\t\tON vtiger_account.accountid = vtiger_invoice.accountid\n\t\t\tINNER JOIN (SELECT DISTINCT(vtiger_inventoryproductrel.id) as id FROM vtiger_inventoryproductrel WHERE vtiger_inventoryproductrel.productid = {$id}) as invrel\n\t\t\t\tON invrel.id = vtiger_invoice.invoiceid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0"; $return_value = GetRelatedList($this_module, $related_module, $other, $query, $button, $returnset); if ($return_value == null) { $return_value = array(); } $return_value['CUSTOM_BUTTON'] = $button; $log->debug("Exiting get_invoices method ..."); return $return_value; }
/** * 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'); $orderBy = $this->getForSql('orderby'); $sortOrder = $this->getForSql('sortorder'); 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(' . getSqlForNameInDisplayFormat(['first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'], 'Users') . ',vtiger_groups.groupname)'; } } $listQuery = $this->getQuery(); $listQuery = preg_replace("/vtiger_crmentity.deleted\s*=\s*0/i", 'vtiger_crmentity.deleted = 1', $listQuery); $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').'.'.$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; } } $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); $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; }
/** * Default (generic) function to handle the dependents list for the module. * NOTE: UI type '10' is used to stored the references to other modules for a given record. * These dependent records can be retrieved through this function. * For eg: A trouble ticket can be related to an Account or a Contact. * From a given Contact/Account if we need to fetch all such dependent trouble tickets, get_dependents_list function can be used. */ function get_dependents_list($id, $cur_tab_id, $rel_tab_id, $actions = false) { global $currentModule, $app_strings, $singlepane_view, $current_user; $parenttab = getParentTab(); $related_module = vtlib_getModuleNameById($rel_tab_id); $other = CRMEntity::getInstance($related_module); // Some standard module class doesn't have required variables // that are used in the query, they are defined in this generic API vtlib_setup_modulevars($currentModule, $this); vtlib_setup_modulevars($related_module, $other); $singular_modname = 'SINGLE_' . $related_module; $button = ''; // To make the edit or del link actions to return back to same view. if ($singlepane_view == 'true') { $returnset = "&return_module={$currentModule}&return_action=DetailView&return_id={$id}"; } else { $returnset = "&return_module={$currentModule}&return_action=CallRelatedList&return_id={$id}"; } $return_value = null; $dependentFieldSql = $this->db->pquery("SELECT tabid, fieldname, columnname FROM vtiger_field WHERE uitype='10' AND" . " fieldid IN (SELECT fieldid FROM vtiger_fieldmodulerel WHERE relmodule=? AND module=?)", array($currentModule, $related_module)); $numOfFields = $this->db->num_rows($dependentFieldSql); if ($numOfFields > 0) { $dependentColumn = $this->db->query_result($dependentFieldSql, 0, 'columnname'); $dependentField = $this->db->query_result($dependentFieldSql, 0, 'fieldname'); $button .= '<input type="hidden" name="' . $dependentColumn . '" id="' . $dependentColumn . '" value="' . $id . '">'; $button .= '<input type="hidden" name="' . $dependentColumn . '_type" id="' . $dependentColumn . '_type" value="' . $currentModule . '">'; if ($actions) { if (is_string($actions)) { $actions = explode(',', strtoupper($actions)); } if (in_array('ADD', $actions) && isPermitted($related_module, 1, '') == 'yes' && getFieldVisibilityPermission($related_module, $current_user->id, $dependentField, 'readwrite') == '0') { $button .= "<input title='" . getTranslatedString('LBL_ADD_NEW') . " " . getTranslatedString($singular_modname, $related_module) . "' class='crmbutton small create'" . " onclick='this.form.action.value=\"EditView\";this.form.module.value=\"{$related_module}\"' type='submit' name='button'" . " value='" . getTranslatedString('LBL_ADD_NEW') . " " . getTranslatedString($singular_modname, $related_module) . "'> "; } } $query = "SELECT vtiger_crmentity.*, {$other->table_name}.*"; $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query .= ", CASE WHEN (vtiger_users.user_name NOT LIKE '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name"; $more_relation = ''; if (!empty($other->related_tables)) { foreach ($other->related_tables as $tname => $relmap) { $query .= ", {$tname}.*"; // Setup the default JOIN conditions if not specified if (empty($relmap[1])) { $relmap[1] = $other->table_name; } if (empty($relmap[2])) { $relmap[2] = $relmap[0]; } $more_relation .= " LEFT JOIN {$tname} ON {$tname}.{$relmap['0']} = {$relmap['1']}.{$relmap['2']}"; } } $query .= " FROM {$other->table_name}"; $query .= " INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = {$other->table_name}.{$other->table_index}"; $query .= " INNER JOIN {$this->table_name} ON {$this->table_name}.{$this->table_index} = {$other->table_name}.{$dependentColumn}"; $query .= $more_relation; $query .= " LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid"; $query .= " LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " WHERE vtiger_crmentity.deleted = 0 AND {$this->table_name}.{$this->table_index} = {$id}"; $return_value = GetRelatedList($currentModule, $related_module, $other, $query, $button, $returnset); } if ($return_value == null) { $return_value = array(); } $return_value['CUSTOM_BUTTON'] = $button; return $return_value; }
/** function used to get the the activity history related to the quote * @param int $id - quote id * @return array - return an array which will be returned from the function GetHistory */ function get_history($id) { global $log; $log->debug("Entering get_history(" . $id . ") method ..."); $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT vtiger_activity.activityid, vtiger_activity.subject, vtiger_activity.status,\n\t\t\tvtiger_activity.eventstatus, vtiger_activity.activitytype,vtiger_activity.date_start,\n\t\t\tvtiger_activity.due_date,vtiger_activity.time_start, vtiger_activity.time_end,\n\t\t\tvtiger_contactdetails.contactid,\n\t\t\tvtiger_contactdetails.firstname,vtiger_contactdetails.lastname, vtiger_crmentity.modifiedtime,\n\t\t\tvtiger_crmentity.createdtime, vtiger_crmentity.description, case when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name\n\t\t\tfrom vtiger_activity\n\t\t\t\tinner join vtiger_seactivityrel on vtiger_seactivityrel.activityid=vtiger_activity.activityid\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid\n\t\t\t\tleft join vtiger_cntactivityrel on vtiger_cntactivityrel.activityid= vtiger_activity.activityid\n\t\t\t\tleft join vtiger_contactdetails on vtiger_contactdetails.contactid= vtiger_cntactivityrel.contactid\n left join vtiger_groups on vtiger_groups.groupid=vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid\n\t\t\t\twhere vtiger_activity.activitytype='Task'\n \t\t\t\tand (vtiger_activity.status = 'Completed' or vtiger_activity.status = 'Deferred')\n\t \t \tand vtiger_seactivityrel.crmid=" . $id . "\n and vtiger_crmentity.deleted = 0"; //Don't add order by, because, for security, one more condition will be added with this query in include/RelatedListView.php $log->debug("Exiting get_history method ..."); return getHistory('Quotes', $query, $id); }
function getReferenceFieldColumnList($moduleName, $fieldInfo) { $adb = PearDatabase::getInstance(); $columnsSqlList = array(); $fieldInstance = WebserviceField::fromArray($adb, $fieldInfo); $referenceModuleList = $fieldInstance->getReferenceList(); $reportSecondaryModules = explode(':', $this->secondarymodule); if ($moduleName != $this->primarymodule && in_array($this->primarymodule, $referenceModuleList)) { $entityTableFieldNames = getEntityFieldNames($this->primarymodule); $entityTableName = $entityTableFieldNames['tablename']; $entityFieldNames = $entityTableFieldNames['fieldname']; $columnList = array(); if (is_array($entityFieldNames)) { foreach ($entityFieldNames as $entityColumnName) { $columnList["{$entityColumnName}"] = "{$entityTableName}.{$entityColumnName}"; } } else { $columnList[] = "{$entityTableName}.{$entityFieldNames}"; } if (count($columnList) > 1) { $columnSql = getSqlForNameInDisplayFormat($columnList, $this->primarymodule); } else { $columnSql = implode('', $columnList); } $columnsSqlList[] = $columnSql; } else { foreach ($referenceModuleList as $referenceModule) { $entityTableFieldNames = getEntityFieldNames($referenceModule); $entityTableName = $entityTableFieldNames['tablename']; $entityFieldNames = $entityTableFieldNames['fieldname']; $referenceTableName = ''; $dependentTableName = ''; if ($moduleName == 'HelpDesk' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountRelHelpDesk'; } elseif ($moduleName == 'HelpDesk' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsRelHelpDesk'; } elseif ($moduleName == 'HelpDesk' && $referenceModule == 'Products') { $referenceTableName = 'vtiger_productsRel'; } elseif ($moduleName == 'HelpDesk' && $referenceModule == 'Project') { $referenceTableName = 'vtiger_projectRelHelpDesk'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Leads') { $referenceTableName = 'vtiger_leaddetailsRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Potentials') { $referenceTableName = 'vtiger_potentialRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Invoice') { $referenceTableName = 'vtiger_invoiceRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Quotes') { $referenceTableName = 'vtiger_quotesRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'PurchaseOrder') { $referenceTableName = 'vtiger_purchaseorderRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'SalesOrder') { $referenceTableName = 'vtiger_salesorderRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'HelpDesk') { $referenceTableName = 'vtiger_troubleticketsRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Campaigns') { $referenceTableName = 'vtiger_campaignRelCalendar'; } elseif ($moduleName == 'Contacts' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountContacts'; } elseif ($moduleName == 'Contacts' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsContacts'; } elseif ($moduleName == 'Accounts' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountAccounts'; } elseif ($moduleName == 'Campaigns' && $referenceModule == 'Products') { $referenceTableName = 'vtiger_productsCampaigns'; } elseif ($moduleName == 'Faq' && $referenceModule == 'Products') { $referenceTableName = 'vtiger_productsFaq'; } elseif ($moduleName == 'Invoice' && $referenceModule == 'SalesOrder') { $referenceTableName = 'vtiger_salesorderInvoice'; } elseif ($moduleName == 'Invoice' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsInvoice'; } elseif ($moduleName == 'Invoice' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountInvoice'; } elseif ($moduleName == 'Potentials' && $referenceModule == 'Campaigns') { $referenceTableName = 'vtiger_campaignPotentials'; } elseif ($moduleName == 'Products' && $referenceModule == 'Vendors') { $referenceTableName = 'vtiger_vendorRelProducts'; } elseif ($moduleName == 'PurchaseOrder' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsPurchaseOrder'; } elseif ($moduleName == 'PurchaseOrder' && $referenceModule == 'Vendors') { $referenceTableName = 'vtiger_vendorRelPurchaseOrder'; } elseif ($moduleName == 'Quotes' && $referenceModule == 'Potentials') { $referenceTableName = 'vtiger_potentialRelQuotes'; } elseif ($moduleName == 'Quotes' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountQuotes'; } elseif ($moduleName == 'SalesOrder' && $referenceModule == 'Potentials') { $referenceTableName = 'vtiger_potentialRelSalesOrder'; } elseif ($moduleName == 'SalesOrder' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountSalesOrder'; } elseif ($moduleName == 'SalesOrder' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsSalesOrder'; } elseif ($moduleName == 'SalesOrder' && $referenceModule == 'Quotes') { $referenceTableName = 'vtiger_quotesSalesOrder'; } elseif ($moduleName == 'Potentials' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsPotentials'; } elseif ($moduleName == 'Potentials' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountPotentials'; } elseif ($moduleName == 'ModComments' && $referenceModule == 'Users') { $referenceTableName = 'vtiger_usersModComments'; } elseif (in_array($referenceModule, $reportSecondaryModules)) { $referenceTableName = "{$entityTableName}Rel{$referenceModule}"; $dependentTableName = "vtiger_crmentityRel{$referenceModule}{$fieldInstance->getFieldId()}"; } elseif (in_array($moduleName, $reportSecondaryModules)) { $referenceTableName = "{$entityTableName}Rel{$moduleName}"; $dependentTableName = "vtiger_crmentityRel{$moduleName}{$fieldInstance->getFieldId()}"; } else { $referenceTableName = "{$entityTableName}Rel{$moduleName}{$fieldInstance->getFieldId()}"; $dependentTableName = "vtiger_crmentityRel{$moduleName}{$fieldInstance->getFieldId()}"; $this->queryPlanner->addCustomTable(array('reference' => $referenceTableName, 'field' => $fieldInstance->getColumnName(), 'table' => $fieldInfo['tablename'], 'refTable' => $entityTableFieldNames['tablename'], 'refIndex' => $entityTableFieldNames['entityidfield'])); } $this->queryPlanner->addTable($referenceTableName); if (isset($dependentTableName)) { $this->queryPlanner->addTable($dependentTableName); } $columnList = array(); if (is_array($entityFieldNames)) { foreach ($entityFieldNames as $entityColumnName) { $columnList["{$entityColumnName}"] = "{$referenceTableName}.{$entityColumnName}"; } } else { $columnList[] = "{$referenceTableName}.{$entityFieldNames}"; } if (count($columnList) > 1) { $columnSql = getSqlForNameInDisplayFormat($columnList, $referenceModule); } else { $columnSql = implode('', $columnList); } /*if ($referenceModule == 'DocumentFolders' && $fieldInstance->getFieldName() == 'folderid') { $columnSql = 'vtiger_attachmentsfolder.foldername'; $this->queryPlanner->addTable("vtiger_attachmentsfolder"); }*/ if ($referenceModule == 'Currency' && $fieldInstance->getFieldName() == 'currency_id') { $columnSql = "vtiger_currency_info{$moduleName}.currency_name"; $this->queryPlanner->addTable("vtiger_currency_info{$moduleName}"); } $columnsSqlList[] = "trim({$columnSql})"; } } return $columnsSqlList; }
public function getWhereClause($onlyWhereQuery = false) { $current_user = vglobal('current_user'); if (!empty($this->query) || !empty($this->whereClause)) { return $this->whereClause; } $deletedQuery = $this->meta->getEntityDeletedQuery(); $sql = ''; if (!empty($deletedQuery) && !$onlyWhereQuery) { $sql .= " WHERE {$deletedQuery}"; } if ($this->conditionInstanceCount > 0) { $sql .= ' AND '; } elseif (empty($deletedQuery)) { $sql .= ' WHERE '; } $baseModule = $this->getModule(); $moduleFieldList = $this->getModuleFields(); $baseTable = $this->meta->getEntityBaseTable(); $moduleTableIndexList = $this->meta->getEntityTableIndexList(); $baseTableIndex = $moduleTableIndexList[$baseTable]; $groupSql = $this->groupInfo; $fieldSqlList = array(); foreach ($this->conditionals as $index => $conditionInfo) { $fieldName = $conditionInfo['name']; $field = $moduleFieldList[$fieldName]; if (empty($field) || $conditionInfo['operator'] == 'None') { continue; } $fieldSql = '('; $fieldGlue = ''; $valueSqlList = $this->getConditionValue($conditionInfo['value'], $conditionInfo['operator'], $field); $operator = strtolower($conditionInfo['operator']); if ($operator == 'between' && $this->isDateType($field->getFieldDataType())) { $start = explode(' ', $conditionInfo['value'][0]); if (count($start) == 2) { $conditionInfo['value'][0] = getValidDBInsertDateTimeValue($start[0] . ' ' . $start[1]); } $end = explode(' ', $conditionInfo['values'][1]); // Dates will be equal for Today, Tomorrow, Yesterday. if (count($end) == 2) { if ($start[0] == $end[0]) { $dateTime = new DateTime($conditionInfo['value'][0]); $nextDay = $dateTime->modify('+1 days'); $nextDay = $nextDay->format('Y-m-d H:i:s'); $values = explode(' ', $nextDay); $conditionInfo['value'][1] = getValidDBInsertDateTimeValue($values[0]) . ' ' . $values[1]; } else { $end = $conditionInfo['value'][1]; $dateObject = new DateTimeField($end); $conditionInfo['value'][1] = $dateObject->getDBInsertDateTimeValue(); } } } if (!is_array($valueSqlList)) { $valueSqlList = array($valueSqlList); } foreach ($valueSqlList as $valueSql) { if (in_array($fieldName, $this->referenceFieldList)) { if ($conditionInfo['operator'] == 'y') { $columnName = $field->getColumnName(); $tableName = $field->getTableName(); // We are checking for zero since many reference fields will be set to 0 if it doest not have any value $fieldSql .= "{$fieldGlue} {$tableName}.{$columnName} {$valueSql} OR {$tableName}.{$columnName} = '0'"; $fieldGlue = ' OR'; } else { $moduleList = $this->referenceFieldInfoList[$fieldName]; foreach ($moduleList as $module) { $nameFields = $this->moduleNameFields[$module]; $nameFieldList = explode(',', $nameFields); $meta = $this->getMeta($module); $columnList = array(); foreach ($nameFieldList as $column) { if ($module == 'Users') { $instance = CRMEntity::getInstance($module); $referenceTable = $instance->table_name; if (count($this->ownerFields) > 0 || $this->getModule() == 'Quotes') { $referenceTable .= $fieldName; } } else { $referenceField = $meta->getFieldByColumnName($column); $referenceTable = $referenceField->getTableName() . $fieldName; } if (isset($moduleTableIndexList[$referenceTable])) { $referenceTable = "{$referenceTable}{$fieldName}"; } $columnList[$column] = "{$referenceTable}.{$column}"; } if (count($columnList) > 1) { $columnSql = getSqlForNameInDisplayFormat($columnList, $module); } else { $columnSql = implode('', $columnList); } $fieldSql .= "{$fieldGlue} trim({$columnSql}) {$valueSql}"; $fieldGlue = ' OR'; } } } elseif (in_array($fieldName, $this->ownerFields)) { if ($conditionInfo['operator'] == 'om') { $fieldSql .= $fieldGlue . $field->getTableName() . '.' . $field->getColumnName() . " {$valueSql}"; } elseif ($fieldName == 'created_user_id') { $concatSql = getSqlForNameInDisplayFormat(array('first_name' => "vtiger_users{$fieldName}.first_name", 'last_name' => "vtiger_users{$fieldName}.last_name"), 'Users'); $fieldSql .= "{$fieldGlue} (trim({$concatSql}) {$valueSql})"; } else { $entityFields = Vtiger_Functions::getEntityModuleInfoFieldsFormatted('Users'); if (count($entityFields['fieldname']) > 1) { $columns = []; foreach ($entityFields['fieldname'] as $i => $fieldname) { $columns[$fieldname] = $entityFields['tablename'] . '.' . $fieldname; } $concatSql = getSqlForNameInDisplayFormat($columns, 'Users'); $fieldSql .= "{$fieldGlue} (trim({$concatSql}) {$valueSql} OR " . "vtiger_groups.groupname {$valueSql})"; } else { $columnSql = $entityFields['tablename'] . '.' . $entityFields['fieldname']; $fieldSql .= "{$fieldGlue} (trim({$columnSql}) {$valueSql} OR " . "vtiger_groups.groupname {$valueSql})"; } } } elseif ($field->getFieldDataType() == 'date' && ($baseModule == 'Events' || $baseModule == 'Calendar') && ($fieldName == 'date_start' || $fieldName == 'due_date')) { $value = $conditionInfo['value']; $operator = $conditionInfo['operator']; if ($fieldName == 'date_start') { $dateFieldColumnName = 'vtiger_activity.date_start'; $timeFieldColumnName = 'vtiger_activity.time_start'; } else { $dateFieldColumnName = 'vtiger_activity.due_date'; $timeFieldColumnName = 'vtiger_activity.time_end'; } if ($operator == 'bw') { $values = explode(',', $value); $startDateValue = explode(' ', $values[0]); $endDateValue = explode(' ', $values[1]); if (count($startDateValue) == 2 && count($endDateValue) == 2) { $fieldSql .= " CAST(CONCAT({$dateFieldColumnName},' ',{$timeFieldColumnName}) AS DATETIME) {$valueSql}"; } else { $fieldSql .= "{$dateFieldColumnName} {$valueSql}"; } } else { if (is_array($value)) { $value = $value[0]; } $values = explode(' ', $value); if (count($values) == 2) { $fieldSql .= "{$fieldGlue} CAST(CONCAT({$dateFieldColumnName},' ',{$timeFieldColumnName}) AS DATETIME) {$valueSql} "; } else { $fieldSql .= "{$fieldGlue} {$dateFieldColumnName} {$valueSql}"; } } } elseif ($field->getFieldDataType() == 'datetime') { $value = $conditionInfo['value']; $operator = strtolower($conditionInfo['operator']); if ($operator == 'bw') { $values = explode(',', $value); $startDateValue = explode(' ', $values[0]); $endDateValue = explode(' ', $values[1]); if ($startDateValue[1] == '00:00:00' && ($endDateValue[1] == '00:00:00' || $endDateValue[1] == '23:59:59')) { $fieldSql .= "{$fieldGlue} CAST(" . $field->getTableName() . '.' . $field->getColumnName() . " AS DATE) {$valueSql}"; } else { $fieldSql .= "{$fieldGlue} " . $field->getTableName() . '.' . $field->getColumnName() . ' ' . $valueSql; } } elseif ($operator == 'between' || $operator == 'notequal' || $operator == 'a' || $operator == 'b') { $fieldSql .= "{$fieldGlue} " . $field->getTableName() . '.' . $field->getColumnName() . ' ' . $valueSql; } else { $values = explode(' ', $value); if ($values[1] == '00:00:00') { $fieldSql .= "{$fieldGlue} CAST(" . $field->getTableName() . '.' . $field->getColumnName() . " AS DATE) {$valueSql}"; } else { $fieldSql .= "{$fieldGlue} " . $field->getTableName() . '.' . $field->getColumnName() . ' ' . $valueSql; } } } else { if (($baseModule == 'Events' || $baseModule == 'Calendar') && ($field->getColumnName() == 'status' || $field->getColumnName() == 'eventstatus')) { $otherFieldName = 'eventstatus'; if ($field->getColumnName() == 'eventstatus') { $otherFieldName = 'taskstatus'; } $otherField = $moduleFieldList[$otherFieldName]; $specialCondition = ''; $specialConditionForOtherField = ''; $conditionGlue = ' OR '; if ($conditionInfo['operator'] == 'n' || $conditionInfo['operator'] == 'k' || $conditionInfo['operator'] == 'y') { $conditionGlue = ' AND '; if ($conditionInfo['operator'] == 'n') { $specialCondition = ' OR ' . $field->getTableName() . '.' . $field->getColumnName() . ' IS NULL '; if (!empty($otherField)) { $specialConditionForOtherField = ' OR ' . $otherField->getTableName() . '.' . $otherField->getColumnName() . ' IS NULL '; } } } $otherFieldValueSql = $valueSql; if ($conditionInfo['operator'] == 'ny' && !empty($otherField)) { $otherFieldValueSql = "IS NOT NULL AND " . $otherField->getTableName() . '.' . $otherField->getColumnName() . " != ''"; } $fieldSql .= "{$fieldGlue} ((" . $field->getTableName() . '.' . $field->getColumnName() . ' ' . $valueSql . " {$specialCondition}) "; if (!empty($otherField)) { $fieldSql .= $conditionGlue . '(' . $otherField->getTableName() . '.' . $otherField->getColumnName() . ' ' . $otherFieldValueSql . ' ' . $specialConditionForOtherField . '))'; } else { $fieldSql .= ')'; } } else { if ($fieldName == 'birthday' && !$this->isRelativeSearchOperators($conditionInfo['operator'])) { $fieldSql .= "{$fieldGlue} DATE_FORMAT(" . $field->getTableName() . '.' . $field->getColumnName() . ",'%m%d') " . $valueSql; } else { $fieldSql .= "{$fieldGlue} " . $field->getTableName() . '.' . $field->getColumnName() . ' ' . $valueSql; } } } if (($conditionInfo['operator'] == 'n' || $conditionInfo['operator'] == 'k') && ($field->getFieldDataType() == 'owner' || $field->getFieldDataType() == 'picklist')) { $fieldGlue = ' AND'; } else { $fieldGlue = ' OR'; } } $fieldSql .= ')'; $fieldSqlList[$index] = $fieldSql; } foreach ($this->manyToManyRelatedModuleConditions as $index => $conditionInfo) { $relatedModuleMeta = RelatedModuleMeta::getInstance($this->meta->getTabName(), $conditionInfo['relatedModule']); $relationInfo = $relatedModuleMeta->getRelationMeta(); $relatedModule = $this->meta->getTabName(); $fieldSql = "(" . $relationInfo['relationTable'] . '.' . $relationInfo[$conditionInfo['column']] . $conditionInfo['SQLOperator'] . $conditionInfo['value'] . ")"; $fieldSqlList[$index] = $fieldSql; } // This is added to support reference module fields if ($this->referenceModuleField) { foreach ($this->referenceModuleField as $index => $conditionInfo) { $handler = vtws_getModuleHandlerFromName($conditionInfo['relatedModule'], $current_user); $meta = $handler->getMeta(); $fieldName = $conditionInfo['fieldName']; $fields = $meta->getModuleFields(); $fieldObject = $fields[$fieldName]; $columnName = $fieldObject->getColumnName(); $tableName = $fieldObject->getTableName(); $valueSQL = $this->getConditionValue($conditionInfo['value'], $conditionInfo['SQLOperator'], $fieldObject); $fieldSql = "(" . $tableName . $conditionInfo['referenceField'] . '.' . $columnName . ' ' . $valueSQL[0] . ")"; $fieldSqlList[$index] = $fieldSql; } } // This is needed as there can be condition in different order and there is an assumption in makeGroupSqlReplacements API // that it expects the array in an order and then replaces the sql with its the corresponding place ksort($fieldSqlList); $groupSql = $this->makeGroupSqlReplacements($fieldSqlList, $groupSql); if ($this->conditionInstanceCount > 0) { $this->conditionalWhere = $groupSql; $sql .= $groupSql; } if (!$onlyWhereQuery) { $sql .= " AND {$baseTable}.{$baseTableIndex} > 0"; $instance = CRMEntity::getInstance($baseModule); $sql .= $instance->getUserAccessConditionsQuerySR($baseModule, $current_user); } $this->whereClause = $sql; return $sql; }
/** to get the related name for the given module * @param $comparator :: type string, * @param $value :: type string, * @param $datatype :: type string, * @returns $value :: string */ function getSalesRelatedName($comparator, $value, $datatype, $tablename, $fieldname) { global $log; $log->info("in getSalesRelatedName " . $comparator . "==" . $value . "==" . $datatype . "==" . $tablename . "==" . $fieldname); global $adb; $adv_chk_value = $value; $value = '('; $sql = "select distinct(setype) from vtiger_crmentity c INNER JOIN " . $adb->sql_escape_string($tablename) . " t ON t." . $adb->sql_escape_string($fieldname) . " = c.crmid"; $res = $adb->pquery($sql, array()); for ($s = 0; $s < $adb->num_rows($res); $s++) { $modulename = $adb->query_result($res, $s, "setype"); if ($modulename == 'Vendors') { continue; } if ($s != 0) { $value .= ' or '; } if ($modulename == 'Accounts') { //By Pavani : Related to problem in calender, Ticket: 4284 and 4675 if (($comparator == 'e' || $comparator == 's' || $comparator == 'c') && trim($adv_chk_value) == '') { if ($tablename == 'vtiger_seactivityrel' && $fieldname == 'crmid') { $value .= 'vtiger_account2.accountname IS NULL or '; } else { $value .= 'vtiger_account.accountname IS NULL or '; } } if ($tablename == 'vtiger_seactivityrel' && $fieldname == 'crmid') { $value .= 'vtiger_account2.accountname'; } else { $value .= 'vtiger_account.accountname'; } } if ($modulename == 'Leads') { $concatSql = getSqlForNameInDisplayFormat(array('lastname' => 'vtiger_leaddetails.lastname', 'firstname' => 'vtiger_leaddetails.firstname'), 'Leads'); if (($comparator == 'e' || $comparator == 's' || $comparator == 'c') && trim($adv_chk_value) == '') { $value .= " {$concatSql} IS NULL or "; } $value .= " {$concatSql}"; } if ($modulename == 'Potentials') { if (($comparator == 'e' || $comparator == 's' || $comparator == 'c') && trim($adv_chk_value) == '') { $value .= ' vtiger_potential.potentialname IS NULL or '; } $value .= ' vtiger_potential.potentialname'; } if ($modulename == 'Products') { if (($comparator == 'e' || $comparator == 's' || $comparator == 'c') && trim($adv_chk_value) == '') { $value .= ' vtiger_products.productname IS NULL or '; } $value .= ' vtiger_products.productname'; } if ($modulename == 'Invoice') { if (($comparator == 'e' || $comparator == 's' || $comparator == 'c') && trim($adv_chk_value) == '') { $value .= ' vtiger_invoice.subject IS NULL or '; } $value .= ' vtiger_invoice.subject'; } if ($modulename == 'PurchaseOrder') { if (($comparator == 'e' || $comparator == 's' || $comparator == 'c') && trim($adv_chk_value) == '') { $value .= ' vtiger_purchaseorder.subject IS NULL or '; } $value .= ' vtiger_purchaseorder.subject'; } if ($modulename == 'SalesOrder') { if (($comparator == 'e' || $comparator == 's' || $comparator == 'c') && trim($adv_chk_value) == '') { $value .= ' vtiger_salesorder.subject IS NULL or '; } $value .= ' vtiger_salesorder.subject'; } if ($modulename == 'Quotes') { if (($comparator == 'e' || $comparator == 's' || $comparator == 'c') && trim($adv_chk_value) == '') { $value .= ' vtiger_quotes.subject IS NULL or '; } $value .= ' vtiger_quotes.subject'; } if ($modulename == 'Contacts') { $concatSql = getSqlForNameInDisplayFormat(array('lastname' => 'vtiger_contactdetails.lastname', 'firstname' => 'vtiger_contactdetails.firstname'), 'Contacts'); if (($comparator == 'e' || $comparator == 's' || $comparator == 'c') && trim($adv_chk_value) == '') { $value .= " {$concatSql} IS NULL or "; } $value .= " {$concatSql}"; } if ($modulename == 'HelpDesk') { if (($comparator == 'e' || $comparator == 's' || $comparator == 'c') && trim($adv_chk_value) == '') { $value .= ' vtiger_troubletickets.title IS NULL or '; } $value .= ' vtiger_troubletickets.title'; } if ($modulename == 'Campaigns') { if (($comparator == 'e' || $comparator == 's' || $comparator == 'c') && trim($adv_chk_value) == '') { $value .= ' vtiger_campaign.campaignname IS NULL or '; } $value .= ' vtiger_campaign.campaignname'; } $value .= $this->getAdvComparator($comparator, $adv_chk_value, $datatype); } $value .= ")"; $log->info("in getSalesRelatedName " . $comparator . "==" . $value . "==" . $datatype . "==" . $tablename . "==" . $fieldname); return $value; }
/** Function to get the invoices associated with the Sales Order * This function accepts the id as arguments and execute the MySQL query using the id * and sends the query and the id as arguments to renderRelatedInvoices() method. */ function get_invoices($id) { global $log, $singlepane_view; $log->debug("Entering get_invoices(" . $id . ") method ..."); require_once 'modules/Invoice/Invoice.php'; $focus = new Invoice(); $button = ''; if ($singlepane_view == 'true') { $returnset = '&return_module=SalesOrder&return_action=DetailView&return_id=' . $id; } else { $returnset = '&return_module=SalesOrder&return_action=CallRelatedList&return_id=' . $id; } $userNameSql = getSqlForNameInDisplayFormat(array('f' => 'vtiger_users.first_name', 'l' => 'vtiger_users.last_name')); $query = "select vtiger_crmentity.*, vtiger_invoice.*, vtiger_account.accountname, \r\n\t\t\tvtiger_salesorder.subject as salessubject, case when \r\n\t\t\t(vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname\r\n\t\t\tend as user_name from vtiger_invoice \r\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_invoice.invoiceid \r\n\t\t\tleft outer join vtiger_account on vtiger_account.accountid=vtiger_invoice.accountid \r\n\t\t\tinner join vtiger_salesorder on vtiger_salesorder.salesorderid=vtiger_invoice.salesorderid \r\n\t\t\tleft join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid\r\n\t\t\tleft join vtiger_groups on vtiger_groups.groupid=vtiger_crmentity.smownerid \r\n\t\t\twhere vtiger_crmentity.deleted=0 and vtiger_salesorder.salesorderid=" . $id; $log->debug("Exiting get_invoices method ..."); return GetRelatedList('SalesOrder', 'Invoice', $focus, $query, $button, $returnset); }
/** Function to export the lead records in CSV Format * @param reference variable - where condition is passed when the query is executed * Returns Export Invoice Query. */ function create_export_query($where) { $log = vglobal('log'); $current_user = vglobal('current_user'); $log->debug("Entering create_export_query(" . $where . ") method ..."); include "include/utils/ExportUtils.php"; //To get the Permitted fields query and the permitted fields list $sql = getPermittedFieldsQuery("Invoice", "detail_view"); $fields_list = getFieldsListFromQuery($sql); $fields_list .= getInventoryFieldsForExport($this->table_name); $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT {$fields_list} FROM " . $this->entity_table . "\n\t\t\t\tINNER JOIN vtiger_invoice ON vtiger_invoice.invoiceid = vtiger_crmentity.crmid\n\t\t\t\tLEFT JOIN vtiger_invoicecf ON vtiger_invoicecf.invoiceid = vtiger_invoice.invoiceid\n\t\t\t\tLEFT JOIN vtiger_salesorder ON vtiger_salesorder.salesorderid = vtiger_invoice.salesorderid\n\t\t\t\tLEFT JOIN vtiger_invoiceaddress ON vtiger_invoiceaddress.invoiceaddressid = vtiger_invoice.invoiceid\n\t\t\t\tLEFT JOIN vtiger_inventoryproductrel ON vtiger_inventoryproductrel.id = vtiger_invoice.invoiceid\n\t\t\t\tLEFT JOIN vtiger_products ON vtiger_products.productid = vtiger_inventoryproductrel.productid\n\t\t\t\tLEFT JOIN vtiger_service ON vtiger_service.serviceid = vtiger_inventoryproductrel.productid\n\t\t\t\tLEFT JOIN vtiger_contactdetails ON vtiger_contactdetails.contactid = vtiger_invoice.contactid\n\t\t\t\tLEFT JOIN vtiger_account ON vtiger_account.accountid = vtiger_invoice.accountid\n\t\t\t\tLEFT JOIN vtiger_currency_info ON vtiger_currency_info.id = vtiger_invoice.currency_id\n\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid"; $query .= $this->getNonAdminAccessControlQuery('Invoice', $current_user); $where_auto = " vtiger_crmentity.deleted=0"; if ($where != "") { $query .= " where ({$where}) AND " . $where_auto; } else { $query .= " where " . $where_auto; } $log->debug("Exiting create_export_query method ..."); return $query; }
/** Function to get the list query for a module * @param $module -- module name:: Type string * @param $where -- where:: Type string * @returns $query -- query:: Type query */ function getListQuery($module, $where = '') { $log = vglobal('log'); $log->debug("Entering getListQuery(" . $module . "," . $where . ") method ..."); $current_user = vglobal('current_user'); require 'user_privileges/user_privileges_' . $current_user->id . '.php'; require 'user_privileges/sharing_privileges_' . $current_user->id . '.php'; $tab_id = getTabid($module); $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); switch ($module) { case "HelpDesk": $query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid,\n\t\t\tvtiger_troubletickets.title, vtiger_troubletickets.status,\n\t\t\tvtiger_troubletickets.priority, vtiger_troubletickets.parent_id,\n\t\t\tvtiger_contactdetails.contactid, vtiger_contactdetails.firstname,\n\t\t\tvtiger_contactdetails.lastname, vtiger_account.accountid,\n\t\t\tvtiger_account.accountname, vtiger_ticketcf.*, vtiger_troubletickets.ticket_no\n\t\t\tFROM vtiger_troubletickets\n\t\t\tINNER JOIN vtiger_ticketcf\n\t\t\t\tON vtiger_ticketcf.ticketid = vtiger_troubletickets.ticketid\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_troubletickets.ticketid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_troubletickets.parent_id = vtiger_contactdetails.contactid\n\t\t\tLEFT JOIN vtiger_account\n\t\t\t\tON vtiger_account.accountid = vtiger_troubletickets.parent_id\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_crmentity.smownerid = vtiger_users.id\n\t\t\tLEFT JOIN vtiger_products\n\t\t\t\tON vtiger_products.productid = vtiger_troubletickets.product_id"; $query .= ' ' . getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; break; case "Accounts": //Query modified to sort by assigned to $query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid,\n\t\t\tvtiger_account.*, vtiger_accountaddress.*, vtiger_accountscf.*\n\t\t\tFROM vtiger_account\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_account.accountid\n\t\t\tINNER JOIN vtiger_accountaddress\n\t\t\t\tON vtiger_account.accountid = vtiger_accountaddress.accountaddressid\n\t\t\tINNER JOIN vtiger_accountscf\n\t\t\t\tON vtiger_account.accountid = vtiger_accountscf.accountid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_account vtiger_account2\n\t\t\t\tON vtiger_account.parentid = vtiger_account2.accountid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; break; case "Potentials": //Query modified to sort by assigned to $query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid,\n\t\t\tvtiger_account.accountname,\n\t\t\tvtiger_potential.related_to, vtiger_potential.potentialname,\n\t\t\tvtiger_potential.sales_stage, vtiger_potential.amount,\n\t\t\tvtiger_potential.currency, vtiger_potential.closingdate,\n\t\t\tvtiger_potential.typeofrevenue,\n\t\t\tvtiger_potentialscf.*\n\t\t\tFROM vtiger_potential\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_potential.potentialid\n\t\t\tINNER JOIN vtiger_potentialscf\n\t\t\t\tON vtiger_potentialscf.potentialid = vtiger_potential.potentialid\n\t\t\tLEFT JOIN vtiger_account\n\t\t\t\tON vtiger_potential.related_to = vtiger_account.accountid\n\t\t\tLEFT JOIN vtiger_campaign\n\t\t\t\tON vtiger_campaign.campaignid = vtiger_potential.campaignid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; break; case "Leads": $query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid,\n\t\t\tvtiger_leaddetails.lastname,\n\t\t\tvtiger_leaddetails.company, vtiger_leadaddress.phone,\n\t\t\tvtiger_leadsubdetails.website, vtiger_leaddetails.email,\n\t\t\tvtiger_leadscf.*\n\t\t\tFROM vtiger_leaddetails\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_leaddetails.leadid\n\t\t\tINNER JOIN vtiger_leadsubdetails\n\t\t\t\tON vtiger_leadsubdetails.leadsubscriptionid = vtiger_leaddetails.leadid\n\t\t\tINNER JOIN vtiger_leadaddress\n\t\t\t\tON vtiger_leadaddress.leadaddressid = vtiger_leadsubdetails.leadsubscriptionid\n\t\t\tINNER JOIN vtiger_leadscf\n\t\t\t\tON vtiger_leaddetails.leadid = vtiger_leadscf.leadid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 AND vtiger_leaddetails.converted = 0 " . $where; break; case "Products": $query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_crmentity.description, vtiger_products.*, vtiger_productcf.*\n\t\t\tFROM vtiger_products\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_products.productid\n\t\t\tINNER JOIN vtiger_productcf\n\t\t\t\tON vtiger_products.productid = vtiger_productcf.productid\n\t\t\tLEFT JOIN vtiger_vendor\n\t\t\t\tON vtiger_vendor.vendorid = vtiger_products.vendor_id\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid"; if (isset($_REQUEST["from_dashboard"]) && $_REQUEST["from_dashboard"] == true && (isset($_REQUEST["type"]) && $_REQUEST["type"] == "dbrd")) { $query .= " INNER JOIN vtiger_inventoryproductrel on vtiger_inventoryproductrel.productid = vtiger_products.productid"; } $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= " WHERE vtiger_crmentity.deleted = 0 " . $where; break; case "Documents": $query = "SELECT case when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name,vtiger_crmentity.crmid, vtiger_crmentity.modifiedtime,\n\t\t\tvtiger_crmentity.smownerid,`vtiger_trees_templates_data`.*,vtiger_notes.*\n\t\t\tFROM vtiger_notes\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_notes.notesid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN `vtiger_trees_templates_data`\n\t\t\t\tON vtiger_notes.folderid = `vtiger_trees_templates_data`.tree"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; break; case "Contacts": //Query modified to sort by assigned to $query = "SELECT vtiger_contactdetails.firstname, vtiger_contactdetails.lastname,\n\t\t\tvtiger_contactdetails.title, vtiger_contactdetails.parentid,\n\t\t\tvtiger_contactdetails.email, vtiger_contactdetails.phone,\n\t\t\tvtiger_crmentity.smownerid, vtiger_crmentity.crmid\n\t\t\tFROM vtiger_contactdetails\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_contactdetails.contactid\n\t\t\tINNER JOIN vtiger_contactaddress\n\t\t\t\tON vtiger_contactaddress.contactaddressid = vtiger_contactdetails.contactid\n\t\t\tINNER JOIN vtiger_contactsubdetails\n\t\t\t\tON vtiger_contactsubdetails.contactsubscriptionid = vtiger_contactdetails.contactid\n\t\t\tINNER JOIN vtiger_contactscf\n\t\t\t\tON vtiger_contactscf.contactid = vtiger_contactdetails.contactid\n\t\t\tLEFT JOIN vtiger_account\n\t\t\t\tON vtiger_account.accountid = vtiger_contactdetails.parentid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_contactdetails vtiger_contactdetails2\n\t\t\t\tON vtiger_contactdetails.reportsto = vtiger_contactdetails2.contactid\n\t\t\tLEFT JOIN vtiger_customerdetails\n\t\t\t\tON vtiger_customerdetails.customerid = vtiger_contactdetails.contactid"; if (isset($_REQUEST["from_dashboard"]) && $_REQUEST["from_dashboard"] == true && (isset($_REQUEST["type"]) && $_REQUEST["type"] == "dbrd")) { $query .= " INNER JOIN vtiger_campaigncontrel on vtiger_campaigncontrel.contactid = " . "vtiger_contactdetails.contactid"; } $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; break; case "Calendar": $query = "SELECT vtiger_activity.activityid as act_id,vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_crmentity.setype,\n\t\tvtiger_activity.*\n\t\tFROM vtiger_activity\n\t\tLEFT JOIN vtiger_activitycf\n\t\t\tON vtiger_activitycf.activityid = vtiger_activity.activityidd\n\t\tLEFT OUTER JOIN vtiger_activity_reminder\n\t\t\tON vtiger_activity_reminder.activity_id = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_crmentity\n\t\t\tON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_users\n\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\tLEFT JOIN vtiger_groups\n\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\tLEFT JOIN vtiger_users vtiger_users2\n\t\t\tON vtiger_crmentity.modifiedby = vtiger_users2.id\n\t\tLEFT JOIN vtiger_groups vtiger_groups2\n\t\t\tON vtiger_crmentity.modifiedby = vtiger_groups2.groupid"; //added to fix #5135 if (isset($_REQUEST['from_homepage']) && ($_REQUEST['from_homepage'] == "upcoming_activities" || $_REQUEST['from_homepage'] == "pending_activities")) { $query .= " LEFT OUTER JOIN vtiger_recurringevents\n\t\t\t ON vtiger_recurringevents.activityid=vtiger_activity.activityid"; } //end $instance = CRMEntity::getInstance($module); $query .= " WHERE vtiger_crmentity.deleted = 0 AND activitytype != 'Emails' "; $securityParameter = $instance->getUserAccessConditionsQuerySR($module, $current_user); if ($securityParameter != '') { $query .= $securityParameter; } $query .= ' ' . $where; break; case "Emails": $query = "SELECT DISTINCT vtiger_crmentity.crmid, vtiger_crmentity.smownerid,\n\t\t\tvtiger_activity.activityid, vtiger_activity.subject,\n\t\t\tvtiger_activity.date_start,\n\t\t\tvtiger_contactdetails.lastname, vtiger_contactdetails.firstname,\n\t\t\tvtiger_contactdetails.contactid\n\t\t\tFROM vtiger_activity\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_contactdetails.contactid = vtiger_activity.link\n\t\t\tLEFT JOIN vtiger_cntactivityrel\n\t\t\t\tON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\tAND vtiger_cntactivityrel.contactid = vtiger_cntactivityrel.contactid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_salesmanactivityrel\n\t\t\t\tON vtiger_salesmanactivityrel.activityid = vtiger_activity.activityid\n\t\t\tLEFT JOIN vtiger_emaildetails\n\t\t\t\tON vtiger_emaildetails.emailid = vtiger_activity.activityid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_activity.activitytype = 'Emails'"; $query .= "AND vtiger_crmentity.deleted = 0 " . $where; break; case "Faq": $query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.createdtime, vtiger_crmentity.modifiedtime,\n\t\t\tvtiger_faq.*\n\t\t\tFROM vtiger_faq\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_faq.id\n\t\t\tLEFT JOIN vtiger_products\n\t\t\t\tON vtiger_faq.product_id = vtiger_products.productid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; break; case "Vendors": $query = "SELECT vtiger_crmentity.crmid, vtiger_vendor.*, vtiger_vendoraddress.*\n\t\t\tFROM vtiger_vendor\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_vendor.vendorid\n\t\t\tINNER JOIN vtiger_vendoraddress\n\t\t\t\tON vtiger_vendor.vendorid = vtiger_vendoraddress.vendorid\n\t\t\tINNER JOIN vtiger_vendorcf\n\t\t\t\tON vtiger_vendor.vendorid = vtiger_vendorcf.vendorid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where; break; case "PriceBooks": $query = "SELECT vtiger_crmentity.crmid, vtiger_pricebook.*, vtiger_currency_info.currency_name\n\t\t\tFROM vtiger_pricebook\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_pricebook.pricebookid\n\t\t\tINNER JOIN vtiger_pricebookcf\n\t\t\t\tON vtiger_pricebook.pricebookid = vtiger_pricebookcf.pricebookid\n\t\t\tLEFT JOIN vtiger_currency_info\n\t\t\t\tON vtiger_pricebook.currency_id = vtiger_currency_info.id\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where; break; case "Quotes": //Query modified to sort by assigned to $query = "SELECT vtiger_crmentity.*,\n\t\t\tvtiger_quotes.*,\n\t\t\tvtiger_quotesaddress.*,\n\t\t\tvtiger_potential.potentialname,\n\t\t\tvtiger_account.accountname,\n\t\t\tvtiger_currency_info.currency_name\n\t\t\tFROM vtiger_quotes\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_quotes.quoteid\n\t\t\tINNER JOIN vtiger_quotesaddress\n\t\t\t\tON vtiger_quotes.quoteid = vtiger_quotesaddress.quoteaddressid\n\t\t\tLEFT JOIN vtiger_quotescf\n\t\t\t\tON vtiger_quotes.quoteid = vtiger_quotescf.quoteid\n\t\t\tLEFT JOIN vtiger_currency_info\n\t\t\t\tON vtiger_quotes.currency_id = vtiger_currency_info.id\n\t\t\tLEFT OUTER JOIN vtiger_account\n\t\t\t\tON vtiger_account.accountid = vtiger_quotes.accountid\n\t\t\tLEFT OUTER JOIN vtiger_potential\n\t\t\t\tON vtiger_potential.potentialid = vtiger_quotes.potentialid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users as vtiger_usersQuotes\n\t\t\t ON vtiger_usersQuotes.id = vtiger_quotes.inventorymanager"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; break; case "PurchaseOrder": //Query modified to sort by assigned to $query = "SELECT vtiger_crmentity.*,\n\t\t\tvtiger_purchaseorder.*,\n\t\t\tvtiger_purchaseorderaddress.*,\n\t\t\tvtiger_vendor.vendorname,\n\t\t\tvtiger_currency_info.currency_name\n\t\t\tFROM vtiger_purchaseorder\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_purchaseorder.purchaseorderid\n\t\t\tLEFT OUTER JOIN vtiger_vendor\n\t\t\t\tON vtiger_purchaseorder.vendorid = vtiger_vendor.vendorid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_purchaseorder.contactid = vtiger_contactdetails.contactid\n\t\t\tINNER JOIN vtiger_purchaseorderaddress\n\t\t\t\tON vtiger_purchaseorder.purchaseorderid = vtiger_purchaseorderaddress.purchaseorderaddressid\n\t\t\tLEFT JOIN vtiger_purchaseordercf\n\t\t\t\tON vtiger_purchaseordercf.purchaseorderid = vtiger_purchaseorder.purchaseorderid\n\t\t\tLEFT JOIN vtiger_currency_info\n\t\t\t\tON vtiger_purchaseorder.currency_id = vtiger_currency_info.id\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; break; case "SalesOrder": //Query modified to sort by assigned to $query = "SELECT vtiger_crmentity.*,\n\t\t\tvtiger_salesorder.*,\n\t\t\tvtiger_salesorderaddress.*,\n\t\t\tvtiger_quotes.subject AS quotename,\n\t\t\tvtiger_account.accountname,\n\t\t\tvtiger_currency_info.currency_name\n\t\t\tFROM vtiger_salesorder\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_salesorder.salesorderid\n\t\t\tINNER JOIN vtiger_salesorderaddress\n\t\t\t\tON vtiger_salesorder.salesorderid = vtiger_salesorderaddress.salesorderaddressid\n\t\t\tLEFT JOIN vtiger_salesordercf\n\t\t\t\tON vtiger_salesordercf.salesorderid = vtiger_salesorder.salesorderid\n\t\t\tLEFT JOIN vtiger_currency_info\n\t\t\t\tON vtiger_salesorder.currency_id = vtiger_currency_info.id\n\t\t\tLEFT OUTER JOIN vtiger_quotes\n\t\t\t\tON vtiger_quotes.quoteid = vtiger_salesorder.quoteid\n\t\t\tLEFT OUTER JOIN vtiger_account\n\t\t\t\tON vtiger_account.accountid = vtiger_salesorder.accountid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_salesorder.contactid = vtiger_contactdetails.contactid\n\t\t\tLEFT JOIN vtiger_potential\n\t\t\t\tON vtiger_potential.potentialid = vtiger_salesorder.potentialid\n\t\t\tLEFT JOIN vtiger_invoice_recurring_info\n\t\t\t\tON vtiger_invoice_recurring_info.salesorderid = vtiger_salesorder.salesorderid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; break; case "Invoice": //Query modified to sort by assigned to //query modified -Code contribute by Geoff(http://forums.vtiger.com/viewtopic.php?t=3376) $query = "SELECT vtiger_crmentity.*,\n\t\t\tvtiger_invoice.*,\n\t\t\tvtiger_invoiceaddress.*,\n\t\t\tvtiger_salesorder.subject AS salessubject,\n\t\t\tvtiger_account.accountname,\n\t\t\tvtiger_currency_info.currency_name\n\t\t\tFROM vtiger_invoice\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_invoice.invoiceid\n\t\t\tINNER JOIN vtiger_invoiceaddress\n\t\t\t\tON vtiger_invoice.invoiceid = vtiger_invoiceaddress.invoiceaddressid\n\t\t\tLEFT JOIN vtiger_currency_info\n\t\t\t\tON vtiger_invoice.currency_id = vtiger_currency_info.id\n\t\t\tLEFT OUTER JOIN vtiger_salesorder\n\t\t\t\tON vtiger_salesorder.salesorderid = vtiger_invoice.salesorderid\n\t\t\tLEFT OUTER JOIN vtiger_account\n\t\t\t ON vtiger_account.accountid = vtiger_invoice.accountid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_contactdetails.contactid = vtiger_invoice.contactid\n\t\t\tINNER JOIN vtiger_invoicecf\n\t\t\t\tON vtiger_invoice.invoiceid = vtiger_invoicecf.invoiceid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; break; case "Campaigns": //Query modified to sort by assigned to //query modified -Code contribute by Geoff(http://forums.vtiger.com/viewtopic.php?t=3376) $query = "SELECT vtiger_crmentity.*,\n\t\t\tvtiger_campaign.*\n\t\t\tFROM vtiger_campaign\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_campaign.campaignid\n\t\t\tINNER JOIN vtiger_campaignscf\n\t\t\t ON vtiger_campaign.campaignid = vtiger_campaignscf.campaignid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_products\n\t\t\t\tON vtiger_products.productid = vtiger_campaign.product_id"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; break; case "Users": $query = "SELECT id,user_name,first_name,last_name,email1,is_admin,status,\n\t\t\t\t\tvtiger_user2role.roleid as roleid,vtiger_role.depth as depth\n\t\t\t\t \tFROM vtiger_users\n\t\t\t\t \tINNER JOIN vtiger_user2role ON vtiger_users.id = vtiger_user2role.userid\n\t\t\t\t \tINNER JOIN vtiger_role ON vtiger_user2role.roleid = vtiger_role.roleid\n\t\t\t\t\tWHERE deleted=0 AND status <> 'Inactive'" . $where; break; default: // vtlib customization: Include the module file $focus = CRMEntity::getInstance($module); $query = $focus->getListQuery($module, $where); // END } if ($module != 'Users') { $query = listQueryNonAdminChange($query, $module); } $log->debug("Exiting getListQuery method ..."); return $query; }
/** * 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; }
/** * Function to get Potential related SalesOrder * @param integer $id - potentialid * returns related SalesOrder record in array format */ function get_salesorder($id, $cur_tab_id, $rel_tab_id, $actions = false) { global $log, $singlepane_view, $currentModule, $current_user; $log->debug("Entering get_salesorder(" . $id . ") method ..."); $this_module = $currentModule; $related_module = vtlib_getModuleNameById($rel_tab_id); require_once "modules/{$related_module}/{$related_module}.php"; $other = new $related_module(); vtlib_setup_modulevars($related_module, $other); $singular_modname = vtlib_toSingular($related_module); $parenttab = getParentTab(); if ($singlepane_view == 'true') { $returnset = '&return_module=' . $this_module . '&return_action=DetailView&return_id=' . $id; } else { $returnset = '&return_module=' . $this_module . '&return_action=CallRelatedList&return_id=' . $id; } $button = ''; if ($actions && getFieldVisibilityPermission($related_module, $current_user->id, 'potential_id', 'readwrite') == '0') { if (is_string($actions)) { $actions = explode(',', strtoupper($actions)); } if (in_array('SELECT', $actions) && isPermitted($related_module, 4, '') == 'yes') { $button .= "<input title='" . getTranslatedString('LBL_SELECT') . " " . getTranslatedString($related_module) . "' class='crmbutton small edit' type='button' onclick=\"return window.open('index.php?module={$related_module}&return_module={$currentModule}&action=Popup&popuptype=detailview&select=enable&form=EditView&form_submit=false&recordid={$id}&parenttab={$parenttab}','test','width=640,height=602,resizable=0,scrollbars=0');\" value='" . getTranslatedString('LBL_SELECT') . " " . getTranslatedString($related_module) . "'> "; } if (in_array('ADD', $actions) && isPermitted($related_module, 1, '') == 'yes') { $button .= "<input title='" . getTranslatedString('LBL_ADD_NEW') . " " . getTranslatedString($singular_modname) . "' class='crmbutton small create'" . " onclick='this.form.action.value=\"EditView\";this.form.module.value=\"{$related_module}\"' type='submit' name='button'" . " value='" . getTranslatedString('LBL_ADD_NEW') . " " . getTranslatedString($singular_modname) . "'> "; } } $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "select vtiger_crmentity.*, vtiger_salesorder.*, vtiger_quotes.subject as quotename\n\t\t\t, vtiger_account.accountname, vtiger_potential.potentialname,case when\n\t\t\t(vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname\n\t\t\tend as user_name from vtiger_salesorder\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid\n\t\t\tleft outer join vtiger_quotes on vtiger_quotes.quoteid=vtiger_salesorder.quoteid\n\t\t\tleft outer join vtiger_account on vtiger_account.accountid=vtiger_salesorder.accountid\n\t\t\tleft outer join vtiger_potential on vtiger_potential.potentialid=vtiger_salesorder.potentialid\n\t\t\tleft join vtiger_groups on vtiger_groups.groupid=vtiger_crmentity.smownerid\n\t\t\tleft join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid\n\t\t\t where vtiger_crmentity.deleted=0 and vtiger_potential.potentialid = " . $id; $return_value = GetRelatedList($this_module, $related_module, $other, $query, $button, $returnset); if ($return_value == null) { $return_value = array(); } $return_value['CUSTOM_BUTTON'] = $button; $log->debug("Exiting get_salesorder method ..."); return $return_value; }