/**
* 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;
}
/**
* 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)
{
$relatedModuleName = $relatedModule->getName();
$focus = CRMEntity::getInstance($this->getName());
$focus->id = $recordId;
$result = $focus->{$functionName}($recordId, $this->getId(), $relatedModule->getId());
$query = $result['query'] . ' ' . $this->getSpecificRelationQuery($relatedModuleName);
$nonAdminQuery = $this->getNonAdminAccessControlQueryForRelation($relatedModuleName);
//modify query if any module has summary fields, those fields we are displayed in related list of that module
$relatedListFields = $relatedModule->getConfigureRelatedListFields();
if ($relatedModuleName == 'Documents') {
$relatedListFields['filelocationtype'] = 'filelocationtype';
$relatedListFields['filestatus'] = 'filestatus';
}
if (count($relatedListFields) > 0) {
$currentUser = Users_Record_Model::getCurrentUserModel();
$queryGenerator = new QueryGenerator($relatedModuleName, $currentUser);
$queryGenerator->setFields($relatedListFields);
$selectColumnSql = $queryGenerator->getSelectClauseColumnSQL();
$newQuery = spliti('FROM', $query);
$selectColumnSql = 'SELECT DISTINCT vtiger_crmentity.crmid,' . $selectColumnSql;
$query = $selectColumnSql . ' FROM ' . $newQuery[1];
}
if ($nonAdminQuery) {
$query = appendFromClauseToQuery($query, $nonAdminQuery);
}
return $query;
}
/** Function to get related list entries in detailed array format
* @param $module -- modulename:: Type string
* @param $relatedmodule -- relatedmodule:: Type string
* @param $focus -- focus:: Type object
* @param $query -- query:: Type string
* @param $button -- buttons:: Type string
* @param $returnset -- returnset:: Type string
* @param $id -- id:: Type string
* @param $edit_val -- edit value:: Type string
* @param $del_val -- delete value:: Type string
* @returns $related_entries -- related entires:: Type string array
*/
function GetRelatedListBase($module, $relatedmodule, $focus, $query, $button, $returnset, $id = '', $edit_val = '', $del_val = '', $skipActions = false)
{
$log = LoggerManager::getLogger('account_list');
$log->debug("Entering GetRelatedList(" . $module . "," . $relatedmodule . "," . get_class($focus) . "," . $query . "," . $button . "," . $returnset . "," . $edit_val . "," . $del_val . ") method ...");
require_once 'Smarty_setup.php';
require_once "data/Tracker.php";
require_once 'include/database/PearDatabase.php';
global $adb, $app_strings, $current_language;
$current_module_strings = return_module_language($current_language, $module);
global $list_max_entries_per_page, $urlPrefix, $currentModule, $theme, $theme_path, $theme_path, $mod_strings;
$smarty = new vtigerCRM_Smarty();
if (!isset($where)) {
$where = "";
}
$button = '<table cellspacing=0 cellpadding=2><tr><td>' . $button . '</td></tr></table>';
// Added to have Purchase Order as form Title
$theme_path = "themes/" . $theme . "/";
$image_path = $theme_path . "images/";
$smarty->assign("MOD", $mod_strings);
$smarty->assign("APP", $app_strings);
$smarty->assign("THEME", $theme);
$smarty->assign("IMAGE_PATH", $image_path);
$smarty->assign("MODULE", $relatedmodule);
// We do not have RelatedListView in Detail View mode of Calendar module. So need to skip it.
if ($module != 'Calendar') {
$focus->initSortByField($relatedmodule);
}
//Retreive the list from Database
//Appending the security parameter Security fix by Don
if ($relatedmodule != 'Faq' && $relatedmodule != 'PriceBook' && $relatedmodule != 'Vendors' && $relatedmodule != 'Users') {
global $current_user;
$secQuery = getNonAdminAccessControlQuery($relatedmodule, $current_user);
if (strlen($secQuery) > 1) {
$query = appendFromClauseToQuery($query, $secQuery);
}
}
if ($relatedmodule == 'Leads') {
$query .= " AND vtiger_leaddetails.converted = 0";
}
if (isset($where) && $where != '') {
$query .= ' and ' . $where;
}
if (!$_SESSION['rlvs'][$module][$relatedmodule]) {
$modObj = new ListViewSession();
$modObj->sortby = $focus->default_order_by;
$modObj->sorder = $focus->default_sort_order;
$_SESSION['rlvs'][$module][$relatedmodule] = get_object_vars($modObj);
}
if (!empty($_REQUEST['order_by'])) {
if (method_exists($focus, getSortOrder)) {
$sorder = $focus->getSortOrder();
}
if (method_exists($focus, getOrderBy)) {
$order_by = $focus->getOrderBy();
}
if (isset($order_by) && $order_by != '') {
$_SESSION['rlvs'][$module][$relatedmodule]['sorder'] = $sorder;
$_SESSION['rlvs'][$module][$relatedmodule]['sortby'] = $order_by;
}
} elseif ($_SESSION['rlvs'][$module][$relatedmodule]) {
$sorder = $_SESSION['rlvs'][$module][$relatedmodule]['sorder'];
$order_by = $_SESSION['rlvs'][$module][$relatedmodule]['sortby'];
} else {
$order_by = $focus->default_order_by;
$sorder = $focus->default_sort_order;
}
//Added by Don for AssignedTo ordering issue in Related Lists
$query_order_by = $order_by;
if ($order_by == 'smownerid') {
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$query_order_by = "case when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end ";
} elseif ($order_by != 'crmid' && !empty($order_by)) {
$tabname = getTableNameForField($relatedmodule, $order_by);
if ($tabname !== '' and $tabname != NULL) {
$query_order_by = $tabname . "." . $query_order_by;
}
}
if (!empty($query_order_by)) {
$query .= ' ORDER BY ' . $query_order_by . ' ' . $sorder;
}
if ($relatedmodule == 'Calendar') {
$mod_listquery = "activity_listquery";
} else {
$mod_listquery = strtolower($relatedmodule) . "_listquery";
}
$_SESSION[$mod_listquery] = $query;
$url_qry = "&order_by=" . $order_by . "&sorder=" . $sorder;
$computeCount = isset($_REQUEST['withCount']) ? $_REQUEST['withCount'] : '';
if (PerformancePrefs::getBoolean('LISTVIEW_COMPUTE_PAGE_COUNT', false) === true || (bool) $computeCount == true) {
//Retreiving the no of rows
if ($relatedmodule == "Calendar") {
//for calendar related list, count will increase when we have multiple contacts
//relationship for single activity
$count_query = mkCountQuery($query);
$count_result = $adb->query($count_query);
$noofrows = $adb->query_result($count_result, 0, "count");
} else {
$count_query = mkCountQuery($query);
$count_result = $adb->query($count_query);
if ($adb->num_rows($count_result) > 0) {
$noofrows = $adb->query_result($count_result, 0, "count");
} else {
$noofrows = $adb->num_rows($count_result);
}
}
} else {
$noofrows = null;
}
//Setting Listview session object while sorting/pagination
if (isset($_REQUEST['relmodule']) && $_REQUEST['relmodule'] != '' && $_REQUEST['relmodule'] == $relatedmodule) {
$relmodule = vtlib_purify($_REQUEST['relmodule']);
if ($_SESSION['rlvs'][$module][$relmodule]) {
setSessionVar($_SESSION['rlvs'][$module][$relmodule], $noofrows, $list_max_entries_per_page, $module, $relmodule);
}
}
global $relationId;
$start = RelatedListViewSession::getRequestCurrentPage($relationId, $query);
$navigation_array = VT_getSimpleNavigationValues($start, $list_max_entries_per_page, $noofrows);
$limit_start_rec = ($start - 1) * $list_max_entries_per_page;
$list_result = $adb->pquery($query . " LIMIT {$limit_start_rec}, {$list_max_entries_per_page}", array());
/* Save the related list in session for when we click in a register
* from this list we will can navigate with the arrows left and right, to move only in this related list
*/
$relcv = new CustomView();
$relviewId = $relcv->getViewId($relatedmodule);
ListViewSession::setSessionQuery($relatedmodule, $query, $relviewId);
$_SESSION['lvs'][$relatedmodule][$relviewId]['start'] = $start;
//Retreive the List View Table Header
$id = vtlib_purify($_REQUEST['record']);
$listview_header = getListViewHeader($focus, $relatedmodule, '', $sorder, $order_by, $id, '', $module, $skipActions);
//"Accounts");
if ($noofrows > 15) {
$smarty->assign('SCROLLSTART', '<div style="overflow:auto;height:315px;width:100%;">');
$smarty->assign('SCROLLSTOP', '</div>');
}
$smarty->assign("LISTHEADER", $listview_header);
if ($module == 'PriceBook' && $relatedmodule == 'Products') {
$listview_entries = getListViewEntries($focus, $relatedmodule, $list_result, $navigation_array, 'relatedlist', $returnset, $edit_val, $del_val, '', '', '', '', $skipActions);
}
if ($module == 'Products' && $relatedmodule == 'PriceBooks') {
$listview_entries = getListViewEntries($focus, $relatedmodule, $list_result, $navigation_array, 'relatedlist', $returnset, 'EditListPrice', 'DeletePriceBookProductRel', '', '', '', '', $skipActions);
} elseif ($relatedmodule == 'SalesOrder') {
$listview_entries = getListViewEntries($focus, $relatedmodule, $list_result, $navigation_array, 'relatedlist', $returnset, 'SalesOrderEditView', 'DeleteSalesOrder', '', '', '', '', $skipActions);
} else {
$listview_entries = getListViewEntries($focus, $relatedmodule, $list_result, $navigation_array, 'relatedlist', $returnset, $edit_val, $del_val, '', '', '', '', $skipActions);
}
$navigationOutput = array();
$navigationOutput[] = getRecordRangeMessage($list_result, $limit_start_rec, $noofrows);
if (empty($id) && !empty($_REQUEST['record'])) {
$id = vtlib_purify($_REQUEST['record']);
}
$navigationOutput[] = getRelatedTableHeaderNavigation($navigation_array, $url_qry, $module, $relatedmodule, $id);
$related_entries = array('header' => $listview_header, 'entries' => $listview_entries, 'navigation' => $navigationOutput);
$log->debug("Exiting GetRelatedList method ...");
return $related_entries;
}
function getDashboardQuery($query, $module)
{
global $current_user;
$secQuery = getNonAdminAccessControlQuery($module, $current_user);
if (strlen($secQuery) > 1) {
$query = appendFromClauseToQuery($query, $secQuery);
}
return $query;
}
function generateRelatedListModels($model)
{
global $adb;
$lists = $adb->pquery("select vtiger_tab.name as relmodulename, vtiger_relatedlists.name as listtype from vtiger_relatedlists inner join vtiger_tab on vtiger_tab.tabid=vtiger_relatedlists.related_tabid where vtiger_relatedlists.tabid=? and vtiger_relatedlists.name in ('get_related_list', 'get_dependents_list')", array(getTabid($this->moduleName)));
for ($i = 0; $i < $adb->num_rows($lists); $i++) {
$relmodulename = $adb->query_result($lists, $i, 'relmodulename');
$listtype = $adb->query_result($lists, $i, 'listtype');
if ($listtype == 'get_related_list') {
$listrecords = $adb->pquery('select vtiger_crmentityrel.relcrmid from vtiger_crmentityrel inner join vtiger_crmentity on (vtiger_crmentity.crmid=vtiger_crmentityrel.crmid and vtiger_crmentity.deleted=0) where vtiger_crmentityrel.crmid=? and vtiger_crmentityrel.relmodule=?', array($this->focus->id, $relmodulename));
for ($j = 0; $j < 10; $j++) {
$entity = CRMEntity::getInstance($relmodulename);
if ($j < $adb->num_rows($listrecords)) {
$relcrmid = $adb->query_result($listrecords, $j, 'relcrmid');
$entity->retrieve_entity_info($relcrmid, $relmodulename);
}
$this->generateEntityModel($entity, $relmodulename, 'Related' . $relmodulename . ($j + 1) . '_', $model);
}
} else {
if ($listtype == 'get_dependents_list') {
$dependentFieldSql = $adb->pquery("SELECT tabid, fieldname, columnname FROM vtiger_field WHERE uitype='10' AND" . " fieldid IN (SELECT fieldid FROM vtiger_fieldmodulerel WHERE relmodule=? AND module=?)", array($this->moduleName, $relmodulename));
$numOfFields = $adb->num_rows($dependentFieldSql);
if ($numOfFields > 0) {
$dependentColumn = $adb->query_result($dependentFieldSql, 0, 'columnname');
$dependentField = $adb->query_result($dependentFieldSql, 0, 'fieldname');
$thisModule = $this->focus;
$other = CRMEntity::getInstance($relmodulename);
vtlib_setup_modulevars($this->moduleName, $this->focus);
vtlib_setup_modulevars($relmodulename, $other);
$query = "SELECT vtiger_crmentity.*";
$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 {$thisModule->table_name} ON {$thisModule->table_name}.{$thisModule->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 {$thisModule->table_name}.{$thisModule->table_index} = {$thisModule->id}";
if ($relmodulename != 'Faq' && $relmodulename != 'PriceBook' && $relmodulename != 'Users') {
global $current_user;
$secQuery = getNonAdminAccessControlQuery($relmodulename, $current_user);
if (strlen($secQuery) > 1) {
$query = appendFromClauseToQuery($query, $secQuery);
}
}
$listrecords = $adb->pquery($query, array());
for ($j = 0; $j < 10; $j++) {
$entity = CRMEntity::getInstance($relmodulename);
if ($j < $adb->num_rows($listrecords)) {
$relcrmid = $adb->query_result($listrecords, $j, 'crmid');
$entity->retrieve_entity_info($relcrmid, $relmodulename);
}
$this->generateEntityModel($entity, $relmodulename, 'Related' . $relmodulename . ($j + 1) . '_', $model);
}
}
}
}
}
}
function __getRLQueryFromJoins($query, $meta)
{
global $log;
if ($meta->getEntityName() == 'Emails') {
// this query is non-standard, I try to fix it a bit to get it working
$chgFrom = "from vtiger_activity, vtiger_seactivityrel, vtiger_contactdetails, vtiger_users, vtiger_crmentity";
$chgTo = "from vtiger_activity\n\t\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\tleft join vtiger_seactivityrel on vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\tleft join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid\n\t\t\t\t\tleft join vtiger_contactdetails on vtiger_contactdetails.contactid = vtiger_seactivityrel.crmid ";
$query = str_replace($chgFrom, $chgTo, $query);
}
$etable = $meta->getEntityBaseTable();
$eindex = $meta->getIdColumn();
$posFrom = stripos($query, " from ");
foreach ($meta->getEntityTableIndexList() as $tbl => $fld) {
if ($tbl == 'vtiger_crmentity' or $tbl == $etable) {
continue;
}
// these are always in the query
if (stripos($query, "join {$tbl}") > 0) {
continue;
}
// it is already joined
if (stripos($query, $tbl) > $posFrom) {
continue;
}
// the table is present after FROM
if ($tbl == 'vtiger_ticketcomments' or $tbl == 'vtiger_faqcomments') {
continue;
}
// these are obtained through comments
$secQuery = " left join {$tbl} on {$tbl}.{$fld} = {$etable}.{$eindex} ";
$query = appendFromClauseToQuery($query, $secQuery);
}
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') {
$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;
}
