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