本文整理汇总了PHP中getSqlForNameInDisplayFormat函数的典型用法代码示例。如果您正苦于以下问题：PHP getSqlForNameInDisplayFormat函数的具体用法？PHP getSqlForNameInDisplayFormat怎么用？PHP getSqlForNameInDisplayFormat使用的例子？那么恭喜您, 这里精选的函数代码示例或许可以为您提供帮助。
在下文中一共展示了getSqlForNameInDisplayFormat函数的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: getRelationQuery
/**
* 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;
}示例2: getListViewEntries
/**
* 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;
}示例3: getListRecord
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;
}示例4: getRelationQuery
/**
* 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;
}示例5: getAllMobileKeys
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;
}示例6: getRelationQuery
/**
* 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;
}示例7: getBasicListQuery
/**
* 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;
}示例8: getRelationQuery
/**
* 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;
}示例9: process
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');
}示例10: getListViewEntries
/**
* 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;
}示例11: getTimeEmployee
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;
}示例12: getOpenTickets
/**
* 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;
}示例13: getRelationQuery
/**
* 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;
}示例14: getRelationQuery
/**
* 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;
}示例15: create_export_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;
}