function getTopPotentials($maxval, $calCnt) { $log = LoggerManager::getLogger('top opportunity_list'); $log->debug("Entering getTopPotentials() method ..."); require_once "data/Tracker.php"; require_once 'modules/Potentials/Potentials.php'; require_once 'include/logging.php'; require_once 'include/ListView/ListView.php'; global $app_strings; global $adb; global $current_language; global $current_user; $current_module_strings = return_module_language($current_language, "Potentials"); $title = array(); $title[] = 'myTopOpenPotentials.gif'; $title[] = $current_module_strings['LBL_TOP_OPPORTUNITIES']; $title[] = 'home_mypot'; $where = "AND vtiger_potential.potentialid > 0 AND vtiger_potential.sales_stage not in ('Closed Won','Closed Lost','" . $current_module_strings['Closed Won'] . "','" . $current_module_strings['Closed Lost'] . "') AND vtiger_crmentity.smownerid='" . $current_user->id . "' AND vtiger_potential.amount > 0"; $header = array(); $header[] = $current_module_strings['LBL_LIST_OPPORTUNITY_NAME']; //$header[]=$current_module_strings['LBL_LIST_ACCOUNT_NAME']; $currencyid = fetchCurrency($current_user->id); $rate_symbol = getCurrencySymbolandCRate($currencyid); $rate = $rate_symbol['rate']; $curr_symbol = $rate_symbol['symbol']; $header[] = $current_module_strings['LBL_LIST_AMOUNT'] . '(' . $curr_symbol . ')'; $list_query = "SELECT vtiger_crmentity.crmid, vtiger_potential.potentialname,\n\t\t\tvtiger_potential.amount, potentialid\n\t\t\tFROM vtiger_potential\n\t\t\tIGNORE INDEX(PRIMARY) INNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_potential.potentialid"; $list_query .= getNonAdminAccessControlQuery('Potentials', $current_user); $list_query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; $list_query .= " ORDER BY amount DESC"; $list_query .= " LIMIT " . $adb->sql_escape_string($maxval); if ($calCnt == 'calculateCnt') { $list_result_rows = $adb->query(mkCountQuery($list_query)); return $adb->query_result($list_result_rows, 0, 'count'); } $list_result = $adb->query($list_query); $open_potentials_list = array(); $noofrows = $adb->num_rows($list_result); $entries = array(); if ($noofrows) { for ($i = 0; $i < $noofrows; $i++) { $open_potentials_list[] = array('name' => $adb->query_result($list_result, $i, 'potentialname'), 'id' => $adb->query_result($list_result, $i, 'potentialid'), 'amount' => $adb->query_result($list_result, $i, 'amount')); $potentialid = $adb->query_result($list_result, $i, 'potentialid'); $potentialname = $adb->query_result($list_result, $i, 'potentialname'); $Top_Potential = strlen($potentialname) > 20 ? substr($potentialname, 0, 20) . '...' : $potentialname; $value = array(); $value[] = '<a href="index.php?action=DetailView&module=Potentials&record=' . $potentialid . '">' . $Top_Potential . '</a>'; $value[] = CurrencyField::convertToUserFormat($adb->query_result($list_result, $i, 'amount')); $entries[$potentialid] = $value; } } $advft_criteria_groups = array('1' => array('groupcondition' => null)); $advft_criteria = array(array('groupid' => 1, 'columnname' => 'vtiger_potential:sales_stage:sales_stage:Potentials_Sales_Stage:V', 'comparator' => 'k', 'value' => 'closed', 'columncondition' => 'and'), array('groupid' => 1, 'columnname' => 'vtiger_potential:amount:amount:Potentials_Amount:N', 'comparator' => 'g', 'value' => '0', 'columncondition' => 'and'), array('groupid' => 1, 'columnname' => 'vtiger_crmentity:smownerid:assigned_user_id:Leads_Assigned_To:V', 'comparator' => 'e', 'value' => getFullNameFromArray('Users', $current_user->column_fields), 'columncondition' => null)); $search_qry = '&advft_criteria=' . Zend_Json::encode($advft_criteria) . '&advft_criteria_groups=' . Zend_Json::encode($advft_criteria_groups) . '&searchtype=advance&query=true'; $values = array('ModuleName' => 'Potentials', 'Title' => $title, 'Header' => $header, 'Entries' => $entries, 'search_qry' => $search_qry); if (count($open_potentials_list) == 0 || count($open_potentials_list) > 0) { $log->debug("Exiting getTopPotentials method ..."); return $values; } }
function getTopPotentials($maxval, $calCnt) { $log = LoggerManager::getLogger('top opportunity_list'); $log->debug("Entering getTopPotentials() method ..."); require_once "data/Tracker.php"; require_once 'modules/Potentials/Potentials.php'; require_once 'include/logging.php'; require_once 'include/ListView/ListView.php'; global $app_strings; global $adb; global $current_language; global $current_user; $current_module_strings = return_module_language($current_language, "Potentials"); $title = array(); $title[] = 'myTopOpenPotentials.gif'; $title[] = $current_module_strings['LBL_TOP_OPPORTUNITIES']; $title[] = 'home_mypot'; $where = "AND vtiger_potential.potentialid > 0 AND vtiger_potential.sales_stage not in ('Closed Won','Closed Lost','" . $current_module_strings['Closed Won'] . "','" . $current_module_strings['Closed Lost'] . "') AND vtiger_crmentity.smownerid='" . $current_user->id . "' AND vtiger_potential.amount > 0"; $header = array(); $header[] = $current_module_strings['LBL_LIST_OPPORTUNITY_NAME']; //$header[]=$current_module_strings['LBL_LIST_ACCOUNT_NAME']; $currencyid = fetchCurrency($current_user->id); $rate_symbol = getCurrencySymbolandCRate($currencyid); $rate = $rate_symbol['rate']; $curr_symbol = $rate_symbol['symbol']; $header[] = $current_module_strings['LBL_LIST_AMOUNT'] . '(' . $curr_symbol . ')'; $list_query = "SELECT vtiger_crmentity.crmid, vtiger_potential.potentialname,\n\t\t\tvtiger_potential.amount, potentialid\n\t\t\tFROM vtiger_potential\n\t\t\tIGNORE INDEX(PRIMARY) INNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_potential.potentialid"; $list_query .= getNonAdminAccessControlQuery('Potentials', $current_user); $list_query .= "WHERE vtiger_crmentity.deleted = 0 " . $where; $list_query .= " ORDER BY amount DESC"; $list_query .= " LIMIT " . $adb->sql_escape_string($maxval); if ($calCnt == 'calculateCnt') { $list_result_rows = $adb->query(mkCountQuery($list_query)); return $adb->query_result($list_result_rows, 0, 'count'); } $list_result = $adb->query($list_query); $open_potentials_list = array(); $noofrows = $adb->num_rows($list_result); $entries = array(); if ($noofrows) { for ($i = 0; $i < $noofrows; $i++) { $open_potentials_list[] = array('name' => $adb->query_result($list_result, $i, 'potentialname'), 'id' => $adb->query_result($list_result, $i, 'potentialid'), 'amount' => $adb->query_result($list_result, $i, 'amount')); $potentialid = $adb->query_result($list_result, $i, 'potentialid'); $potentialname = $adb->query_result($list_result, $i, 'potentialname'); $Top_Potential = strlen($potentialname) > 20 ? substr($potentialname, 0, 20) . '...' : $potentialname; $value = array(); $value[] = '<a href="index.php?action=DetailView&module=Potentials&record=' . $potentialid . '">' . $Top_Potential . '</a>'; $value[] = convertFromDollar($adb->query_result($list_result, $i, 'amount'), $rate); $entries[$potentialid] = $value; } } $search_qry = "&query=true&Fields0=assigned_user_id&Condition0=e&Srch_value0=" . $current_user->column_fields['user_name'] . "&Fields1=sales_stage&Condition1=k&Srch_value1=closed&searchtype=advance&search_cnt=2&matchtype=all"; $values = array('ModuleName' => 'Potentials', 'Title' => $title, 'Header' => $header, 'Entries' => $entries, 'search_qry' => $search_qry); if ($display_empty_home_blocks && count($open_potentials_list) == 0 || count($open_potentials_list) > 0) { $log->debug("Exiting getTopPotentials method ..."); return $values; } }
public function showEmailsList($srecord, $smodule, $Config, $type) { $return = array(); $adb = PearDatabase::getInstance(); $widgets = $this->modules_email_actions_widgets; $queryParams = array(); if ($widgets[$smodule]) { $ids = array(); $result = $adb->pquery("SELECT * FROM vtiger_crmentityrel WHERE (vtiger_crmentityrel.crmid = ?) OR ( vtiger_crmentityrel.relcrmid = ? ) ", array($srecord, $srecord), true); foreach ($result->GetArray() as $row) { if ($row['module'] == 'OSSMailView') { $ids[] = $row['crmid']; } elseif ($row['relmodule'] == 'OSSMailView') { $ids[] = $row['relcrmid']; } } if (count($ids) == 0) { return array(); } $queryParams[] = $ids; if ($type != 'all') { $ifwhere = "AND type = ?"; $queryParams[] = $type; } $query = "SELECT * FROM vtiger_ossmailview INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_ossmailview.ossmailviewid"; global $current_user; $query .= ' ' . getNonAdminAccessControlQuery('OSSMailView', $current_user); $query .= " WHERE ossmailviewid IN (" . generateQuestionMarks($ids) . ") {$ifwhere} ORDER BY ossmailviewid DESC LIMIT " . $Config['widget_limit']; $result = $adb->pquery($query, $queryParams, true); foreach ($result->GetArray() as $row) { $from = $this->findRecordsById($row['from_id']); $to = $this->findRecordsById($row['to_id']); $return[$row['ossmailviewid']]['date'] = $row['createdtime']; $return[$row['ossmailviewid']]['subject'] = '<a href="index.php?module=OSSMailView&view=preview&record=' . $row['ossmailviewid'] . '" target="' . $Config['target'] . '"> ' . $this->limit_text($row['subject']) . '</a>'; $return[$row['ossmailviewid']]['attachments'] = $row['attachments_exist']; $return[$row['ossmailviewid']]['from'] = $from == '' && $from ? $from : $this->limit_text($row['from_email']); $return[$row['ossmailviewid']]['to'] = $to == '' && $to ? $to : $this->limit_text($row['to_email']); $return[$row['ossmailviewid']]['type'] = $row['type']; $return[$row['ossmailviewid']]['body'] = $row['content']; } } return $return; }
/** Function to get the Contacts assigned to a user with a valid email address. * @param varchar $username - User Name * @param varchar $emailaddress - Email Addr for each contact. * Used By vtigerCRM Outlook Plugin * Returns the Query */ function get_searchbyemailid($username, $emailaddress) { global $log, $current_user; require_once "modules/Users/Users.php"; $seed_user = new Users(); $user_id = $seed_user->retrieve_user_id($username); $current_user = $seed_user; $current_user->retrieve_entity_info($user_id, 'Users'); require 'user_privileges/user_privileges_' . $current_user->id . '.php'; require 'user_privileges/sharing_privileges_' . $current_user->id . '.php'; $log->debug("Entering get_searchbyemailid(" . $username . "," . $emailaddress . ") method ..."); $query = "select vtiger_contactdetails.lastname,vtiger_contactdetails.firstname,\n\t\t\t\tvtiger_contactdetails.contactid, vtiger_contactdetails.salutation,\n\t\t\t\tvtiger_contactdetails.email,vtiger_contactdetails.title,\n\t\t\t\tvtiger_contactdetails.mobile,vtiger_account.accountname,\n\t\t\t\tvtiger_account.accountid as accountid from vtiger_contactdetails\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_contactdetails.contactid\n\t\t\tinner join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid\n\t\t\tleft join vtiger_account on vtiger_account.accountid=vtiger_contactdetails.accountid\n\t\t\tleft join vtiger_contactaddress on vtiger_contactaddress.contactaddressid=vtiger_contactdetails.contactid\n\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery('Contacts', $current_user); $query .= "where vtiger_crmentity.deleted=0"; if (trim($emailaddress) != '') { $query .= " and ((vtiger_contactdetails.email like '" . formatForSqlLike($emailaddress) . "') or vtiger_contactdetails.lastname REGEXP REPLACE('" . $emailaddress . "',' ','|') or vtiger_contactdetails.firstname REGEXP REPLACE('" . $emailaddress . "',' ','|')) and vtiger_contactdetails.email != ''"; } else { $query .= " and (vtiger_contactdetails.email like '" . formatForSqlLike($emailaddress) . "' and vtiger_contactdetails.email != '')"; } $log->debug("Exiting get_searchbyemailid method ..."); return $this->plugin_process_list_query($query); }
/** function to get report query for the given module * @ param $module : type String * this returns join query for the given module */ function getReportsQuery($module, $type = '') { $log = vglobal('log'); $current_user = vglobal('current_user'); $secondary_module = "'"; $secondary_module .= str_replace(":", "','", $this->secondarymodule); $secondary_module .= "'"; if ($module == "Leads") { $query = "from vtiger_leaddetails\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_leaddetails.leadid"; if ($this->queryPlanner->requireTable('vtiger_leadsubdetails')) { $query .= "\tinner join vtiger_leadsubdetails on vtiger_leadsubdetails.leadsubscriptionid=vtiger_leaddetails.leadid"; } if ($this->queryPlanner->requireTable('vtiger_leadaddress')) { $query .= "\tinner join vtiger_leadaddress on vtiger_leadaddress.leadaddressid=vtiger_leaddetails.leadid"; } if ($this->queryPlanner->requireTable('vtiger_leadscf')) { $query .= " inner join vtiger_leadscf on vtiger_leaddetails.leadid = vtiger_leadscf.leadid"; } if ($this->queryPlanner->requireTable('vtiger_groupsLeads')) { $query .= "\tleft join vtiger_groups as vtiger_groupsLeads on vtiger_groupsLeads.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable('vtiger_usersLeads')) { $query .= " left join vtiger_users as vtiger_usersLeads on vtiger_usersLeads.id = vtiger_crmentity.smownerid"; } $query .= " 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"; if ($this->queryPlanner->requireTable('vtiger_lastModifiedByLeads')) { $query .= " left join vtiger_users as vtiger_lastModifiedByLeads on vtiger_lastModifiedByLeads.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyLeads')) { $query .= " left join vtiger_users as vtiger_createdbyLeads on vtiger_createdbyLeads.id = vtiger_crmentity.smcreatorid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0 and vtiger_leaddetails.converted=0"; } else { if ($module == "Accounts") { $query = "from vtiger_account\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_account.accountid"; if ($this->queryPlanner->requireTable('vtiger_accountaddress')) { $query .= " inner join vtiger_accountaddress on vtiger_account.accountid=vtiger_accountaddress.accountaddressid"; } if ($this->queryPlanner->requireTable('vtiger_accountscf')) { $query .= " inner join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid"; } if ($this->queryPlanner->requireTable('vtiger_groupsAccounts')) { $query .= " left join vtiger_groups as vtiger_groupsAccounts on vtiger_groupsAccounts.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable('vtiger_accountAccounts')) { $query .= "\tleft join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid"; } if ($this->queryPlanner->requireTable('vtiger_usersAccounts')) { $query .= " left join vtiger_users as vtiger_usersAccounts on vtiger_usersAccounts.id = vtiger_crmentity.smownerid"; } $query .= " 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"; if ($this->queryPlanner->requireTable('vtiger_lastModifiedByAccounts')) { $query .= " left join vtiger_users as vtiger_lastModifiedByAccounts on vtiger_lastModifiedByAccounts.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyAccounts')) { $query .= " left join vtiger_users as vtiger_createdbyAccounts on vtiger_createdbyAccounts.id = vtiger_crmentity.smcreatorid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0 "; } else { if ($module == "Contacts") { $query = "from vtiger_contactdetails\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid = vtiger_contactdetails.contactid"; if ($this->queryPlanner->requireTable('vtiger_contactaddress')) { $query .= "\tinner join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid"; } if ($this->queryPlanner->requireTable('vtiger_customerdetails')) { $query .= "\tinner join vtiger_customerdetails on vtiger_customerdetails.customerid = vtiger_contactdetails.contactid"; } if ($this->queryPlanner->requireTable('vtiger_contactsubdetails')) { $query .= "\tinner join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid"; } if ($this->queryPlanner->requireTable('vtiger_contactscf')) { $query .= "\tinner join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid"; } if ($this->queryPlanner->requireTable('vtiger_groupsContacts')) { $query .= " left join vtiger_groups vtiger_groupsContacts on vtiger_groupsContacts.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable('vtiger_contactdetailsContacts')) { $query .= "\tleft join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto"; } if ($this->queryPlanner->requireTable('vtiger_accountContacts')) { $query .= "\tleft join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.parentid"; } if ($this->queryPlanner->requireTable('vtiger_usersContacts')) { $query .= " left join vtiger_users as vtiger_usersContacts on vtiger_usersContacts.id = vtiger_crmentity.smownerid"; } $query .= " left 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"; foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } if ($this->queryPlanner->requireTable('vtiger_lastModifiedByContacts')) { $query .= " left join vtiger_users as vtiger_lastModifiedByContacts on vtiger_lastModifiedByContacts.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyContacts')) { $query .= " left join vtiger_users as vtiger_createdbyContacts on vtiger_createdbyContacts.id = vtiger_crmentity.smcreatorid"; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0"; } else { if ($module == "Potentials") { $query = "from vtiger_potential\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_potential.potentialid"; if ($this->queryPlanner->requireTable('vtiger_potentialscf')) { $query .= " inner join vtiger_potentialscf on vtiger_potentialscf.potentialid = vtiger_potential.potentialid"; } if ($this->queryPlanner->requireTable('vtiger_accountPotentials')) { $query .= " left join vtiger_account as vtiger_accountPotentials on vtiger_potential.related_to = vtiger_accountPotentials.accountid"; } if ($this->queryPlanner->requireTable('vtiger_campaignPotentials')) { $query .= " left join vtiger_campaign as vtiger_campaignPotentials on vtiger_potential.campaignid = vtiger_campaignPotentials.campaignid"; } if ($this->queryPlanner->requireTable('vtiger_groupsPotentials')) { $query .= " left join vtiger_groups vtiger_groupsPotentials on vtiger_groupsPotentials.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable('vtiger_usersPotentials')) { $query .= " left join vtiger_users as vtiger_usersPotentials on vtiger_usersPotentials.id = vtiger_crmentity.smownerid"; } // TODO optimize inclusion of these tables $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; if ($this->queryPlanner->requireTable('vtiger_lastModifiedByPotentials')) { $query .= " left join vtiger_users as vtiger_lastModifiedByPotentials on vtiger_lastModifiedByPotentials.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyPotentials')) { $query .= " left join vtiger_users as vtiger_createdbyPotentials on vtiger_createdbyPotentials.id = vtiger_crmentity.smcreatorid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0 "; } else { if ($module == "Products") { $query .= " from vtiger_products"; $query .= " inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_products.productid"; if ($this->queryPlanner->requireTable("vtiger_productcf")) { $query .= " left join vtiger_productcf on vtiger_products.productid = vtiger_productcf.productid"; } if ($this->queryPlanner->requireTable("vtiger_lastModifiedByProducts")) { $query .= " left join vtiger_users as vtiger_lastModifiedByProducts on vtiger_lastModifiedByProducts.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyProducts')) { $query .= " left join vtiger_users as vtiger_createdbyProducts on vtiger_createdbyProducts.id = vtiger_crmentity.smcreatorid"; } if ($this->queryPlanner->requireTable("vtiger_usersProducts")) { $query .= " left join vtiger_users as vtiger_usersProducts on vtiger_usersProducts.id = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable("vtiger_groupsProducts")) { $query .= " left join vtiger_groups as vtiger_groupsProducts on vtiger_groupsProducts.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable("vtiger_vendorRelProducts")) { $query .= " left join vtiger_vendor as vtiger_vendorRelProducts on vtiger_vendorRelProducts.vendorid = vtiger_products.vendor_id"; } if ($this->queryPlanner->requireTable("innerProduct")) { $query .= " LEFT JOIN (\n\t\t\t\t\t\tSELECT vtiger_products.productid,\n\t\t\t\t\t\t\t\t(CASE WHEN (vtiger_products.currency_id = 1 ) THEN vtiger_products.unit_price\n\t\t\t\t\t\t\t\t\tELSE (vtiger_products.unit_price / vtiger_currency_info.conversion_rate) END\n\t\t\t\t\t\t\t\t) AS actual_unit_price\n\t\t\t\t\t\tFROM vtiger_products\n\t\t\t\t\t\tLEFT JOIN vtiger_currency_info ON vtiger_products.currency_id = vtiger_currency_info.id\n\t\t\t\t\t\tLEFT JOIN vtiger_productcurrencyrel ON vtiger_products.productid = vtiger_productcurrencyrel.productid\n\t\t\t\t\t\tAND vtiger_productcurrencyrel.currencyid = " . $current_user->currency_id . "\n\t\t\t\t) AS innerProduct ON innerProduct.productid = vtiger_products.productid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0"; } else { if ($module == "HelpDesk") { $matrix = $this->queryPlanner->newDependencyMatrix(); $matrix->setDependency('vtiger_crmentityRelHelpDesk', array('vtiger_accountRelHelpDesk', 'vtiger_contactdetailsRelHelpDesk')); $query = "from vtiger_troubletickets inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_troubletickets.ticketid"; if ($this->queryPlanner->requireTable('vtiger_ticketcf')) { $query .= " inner join vtiger_ticketcf on vtiger_ticketcf.ticketid = vtiger_troubletickets.ticketid"; } if ($this->queryPlanner->requireTable('vtiger_crmentityRelHelpDesk', $matrix)) { $query .= " left join vtiger_crmentity as vtiger_crmentityRelHelpDesk on vtiger_crmentityRelHelpDesk.crmid = vtiger_troubletickets.parent_id"; } if ($this->queryPlanner->requireTable('vtiger_accountRelHelpDesk')) { $query .= " left join vtiger_account as vtiger_accountRelHelpDesk on vtiger_accountRelHelpDesk.accountid=vtiger_crmentityRelHelpDesk.crmid"; } if ($this->queryPlanner->requireTable('vtiger_contactdetailsRelHelpDesk')) { $query .= " left join vtiger_contactdetails as vtiger_contactdetailsRelHelpDesk on vtiger_contactdetailsRelHelpDesk.contactid= vtiger_troubletickets.contact_id"; } if ($this->queryPlanner->requireTable('vtiger_productsRel')) { $query .= " left join vtiger_products as vtiger_productsRel on vtiger_productsRel.productid = vtiger_troubletickets.product_id"; } if ($this->queryPlanner->requireTable('vtiger_projectRelHelpDesk')) { $query .= " left join vtiger_project as vtiger_projectRelHelpDesk on vtiger_projectRelHelpDesk.projectid=vtiger_crmentityRelHelpDesk.crmid"; } if ($this->queryPlanner->requireTable('vtiger_groupsHelpDesk')) { $query .= " left join vtiger_groups as vtiger_groupsHelpDesk on vtiger_groupsHelpDesk.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable('vtiger_usersHelpDesk')) { $query .= " left join vtiger_users as vtiger_usersHelpDesk on vtiger_crmentity.smownerid=vtiger_usersHelpDesk.id"; } // TODO optimize inclusion of these tables $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " left join vtiger_users on vtiger_crmentity.smownerid=vtiger_users.id"; if ($this->queryPlanner->requireTable('vtiger_lastModifiedByHelpDesk')) { $query .= " left join vtiger_users as vtiger_lastModifiedByHelpDesk on vtiger_lastModifiedByHelpDesk.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyHelpDesk')) { $query .= " left join vtiger_users as vtiger_createdbyHelpDesk on vtiger_createdbyHelpDesk.id = vtiger_crmentity.smcreatorid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0 "; } else { if ($module == "Calendar") { $matrix = $this->queryPlanner->newDependencyMatrix(); $query = "from vtiger_activity\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid"; if ($this->queryPlanner->requireTable('vtiger_activitycf')) { $query .= " left join vtiger_activitycf on vtiger_activitycf.activityid = vtiger_crmentity.crmid"; } if ($this->queryPlanner->requireTable('vtiger_contactdetailsCalendar')) { $query .= " left join vtiger_contactdetails as vtiger_contactdetailsCalendar on vtiger_contactdetailsCalendar.contactid= vtiger_activity.link"; } if ($this->queryPlanner->requireTable('vtiger_groupsCalendar')) { $query .= " left join vtiger_groups as vtiger_groupsCalendar on vtiger_groupsCalendar.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable('vtiger_usersCalendar')) { $query .= " left join vtiger_users as vtiger_usersCalendar on vtiger_usersCalendar.id = vtiger_crmentity.smownerid"; } // TODO optimize inclusion of these tables $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; if ($this->queryPlanner->requireTable('vtiger_activity_reminder')) { $query .= " left join vtiger_activity_reminder on vtiger_activity_reminder.activity_id = vtiger_activity.activityid"; } if ($this->queryPlanner->requireTable('vtiger_recurringevents')) { $query .= " left join vtiger_recurringevents on vtiger_recurringevents.activityid = vtiger_activity.activityid"; } if ($this->queryPlanner->requireTable('vtiger_accountRelCalendar')) { $query .= " left join vtiger_account as vtiger_accountRelCalendar on vtiger_accountRelCalendar.accountid=vtiger_activity.link"; } if ($this->queryPlanner->requireTable('vtiger_leaddetailsRelCalendar')) { $query .= " left join vtiger_leaddetails as vtiger_leaddetailsRelCalendar on vtiger_leaddetailsRelCalendar.leadid = vtiger_activity.link"; } if ($this->queryPlanner->requireTable('vtiger_potentialRelCalendar')) { $query .= " left join vtiger_potential as vtiger_potentialRelCalendar on vtiger_potentialRelCalendar.potentialid = vtiger_activity.process"; } if ($this->queryPlanner->requireTable('vtiger_troubleticketsRelCalendar')) { $query .= " left join vtiger_troubletickets as vtiger_troubleticketsRelCalendar on vtiger_troubleticketsRelCalendar.ticketid = vtiger_activity.process"; } if ($this->queryPlanner->requireTable('vtiger_campaignRelCalendar')) { $query .= " left join vtiger_campaign as vtiger_campaignRelCalendar on vtiger_campaignRelCalendar.campaignid = vtiger_activity.process"; } if ($this->queryPlanner->requireTable('vtiger_lastModifiedByCalendar')) { $query .= " left join vtiger_users as vtiger_lastModifiedByCalendar on vtiger_lastModifiedByCalendar.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyCalendar')) { $query .= " left join vtiger_users as vtiger_createdbyCalendar on vtiger_createdbyCalendar.id = vtiger_crmentity.smcreatorid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " WHERE vtiger_crmentity.deleted=0 and (vtiger_activity.activitytype != 'Emails')"; } else { if ($module == "Quotes") { $matrix = $this->queryPlanner->newDependencyMatrix(); $matrix->setDependency('vtiger_inventoryproductrelQuotes', array('vtiger_productsQuotes', 'vtiger_serviceQuotes')); $query = "from vtiger_quotes\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_quotes.quoteid"; if ($this->queryPlanner->requireTable('vtiger_quotesaddress')) { $query .= " inner join vtiger_quotesaddress on vtiger_quotes.quoteid=vtiger_quotesaddress.quoteaddressid"; } if ($this->queryPlanner->requireTable("vtiger_currency_info{$module}")) { $query .= " left join vtiger_currency_info as vtiger_currency_info{$module} on vtiger_currency_info{$module}.id = vtiger_quotes.currency_id"; } if ($type !== 'COLUMNSTOTOTAL' || $this->lineItemFieldsInCalculation == true) { if ($this->queryPlanner->requireTable("vtiger_inventoryproductrelQuotes", $matrix)) { $query .= " left join vtiger_inventoryproductrel as vtiger_inventoryproductrelQuotes on vtiger_quotes.quoteid = vtiger_inventoryproductrelQuotes.id"; } if ($this->queryPlanner->requireTable("vtiger_productsQuotes")) { $query .= " left join vtiger_products as vtiger_productsQuotes on vtiger_productsQuotes.productid = vtiger_inventoryproductrelQuotes.productid"; } if ($this->queryPlanner->requireTable("vtiger_serviceQuotes")) { $query .= " left join vtiger_service as vtiger_serviceQuotes on vtiger_serviceQuotes.serviceid = vtiger_inventoryproductrelQuotes.productid"; } } if ($this->queryPlanner->requireTable("vtiger_quotescf")) { $query .= " left join vtiger_quotescf on vtiger_quotes.quoteid = vtiger_quotescf.quoteid"; } if ($this->queryPlanner->requireTable("vtiger_groupsQuotes")) { $query .= " left join vtiger_groups as vtiger_groupsQuotes on vtiger_groupsQuotes.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable("vtiger_usersQuotes")) { $query .= " left join vtiger_users as vtiger_usersQuotes on vtiger_usersQuotes.id = vtiger_crmentity.smownerid"; } // TODO optimize inclusion of these tables $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; if ($this->queryPlanner->requireTable("vtiger_lastModifiedByQuotes")) { $query .= " left join vtiger_users as vtiger_lastModifiedByQuotes on vtiger_lastModifiedByQuotes.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyQuotes')) { $query .= " left join vtiger_users as vtiger_createdbyQuotes on vtiger_createdbyQuotes.id = vtiger_crmentity.smcreatorid"; } if ($this->queryPlanner->requireTable("vtiger_usersRel1")) { $query .= " left join vtiger_users as vtiger_usersRel1 on vtiger_usersRel1.id = vtiger_quotes.inventorymanager"; } if ($this->queryPlanner->requireTable("vtiger_potentialRelQuotes")) { $query .= " left join vtiger_potential as vtiger_potentialRelQuotes on vtiger_potentialRelQuotes.potentialid = vtiger_quotes.potentialid"; } if ($this->queryPlanner->requireTable("vtiger_accountQuotes")) { $query .= " left join vtiger_account as vtiger_accountQuotes on vtiger_accountQuotes.accountid = vtiger_quotes.accountid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0"; } else { if ($module == "PurchaseOrder") { $matrix = $this->queryPlanner->newDependencyMatrix(); $matrix->setDependency('vtiger_inventoryproductrelPurchaseOrder', array('vtiger_productsPurchaseOrder', 'vtiger_servicePurchaseOrder')); $query = "from vtiger_purchaseorder\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_purchaseorder.purchaseorderid"; if ($this->queryPlanner->requireTable("vtiger_purchaseorderaddress")) { $query .= " inner join vtiger_purchaseorderaddress on vtiger_purchaseorder.purchaseorderid=vtiger_purchaseorderaddress.purchaseorderaddressid"; } if ($this->queryPlanner->requireTable("vtiger_currency_info{$module}")) { $query .= " left join vtiger_currency_info as vtiger_currency_info{$module} on vtiger_currency_info{$module}.id = vtiger_purchaseorder.currency_id"; } if ($type !== 'COLUMNSTOTOTAL' || $this->lineItemFieldsInCalculation == true) { if ($this->queryPlanner->requireTable("vtiger_inventoryproductrelPurchaseOrder", $matrix)) { $query .= " left join vtiger_inventoryproductrel as vtiger_inventoryproductrelPurchaseOrder on vtiger_purchaseorder.purchaseorderid = vtiger_inventoryproductrelPurchaseOrder.id"; } if ($this->queryPlanner->requireTable("vtiger_productsPurchaseOrder")) { $query .= " left join vtiger_products as vtiger_productsPurchaseOrder on vtiger_productsPurchaseOrder.productid = vtiger_inventoryproductrelPurchaseOrder.productid"; } if ($this->queryPlanner->requireTable("vtiger_servicePurchaseOrder")) { $query .= " left join vtiger_service as vtiger_servicePurchaseOrder on vtiger_servicePurchaseOrder.serviceid = vtiger_inventoryproductrelPurchaseOrder.productid"; } } if ($this->queryPlanner->requireTable("vtiger_purchaseordercf")) { $query .= " left join vtiger_purchaseordercf on vtiger_purchaseorder.purchaseorderid = vtiger_purchaseordercf.purchaseorderid"; } if ($this->queryPlanner->requireTable("vtiger_groupsPurchaseOrder")) { $query .= " left join vtiger_groups as vtiger_groupsPurchaseOrder on vtiger_groupsPurchaseOrder.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable("vtiger_usersPurchaseOrder")) { $query .= " left join vtiger_users as vtiger_usersPurchaseOrder on vtiger_usersPurchaseOrder.id = vtiger_crmentity.smownerid"; } // TODO optimize inclusion of these tables $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; if ($this->queryPlanner->requireTable("vtiger_lastModifiedByPurchaseOrder")) { $query .= " left join vtiger_users as vtiger_lastModifiedByPurchaseOrder on vtiger_lastModifiedByPurchaseOrder.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyPurchaseOrder')) { $query .= " left join vtiger_users as vtiger_createdbyPurchaseOrder on vtiger_createdbyPurchaseOrder.id = vtiger_crmentity.smcreatorid"; } if ($this->queryPlanner->requireTable("vtiger_vendorRelPurchaseOrder")) { $query .= " left join vtiger_vendor as vtiger_vendorRelPurchaseOrder on vtiger_vendorRelPurchaseOrder.vendorid = vtiger_purchaseorder.vendorid"; } if ($this->queryPlanner->requireTable("vtiger_contactdetailsPurchaseOrder")) { $query .= " left join vtiger_contactdetails as vtiger_contactdetailsPurchaseOrder on vtiger_contactdetailsPurchaseOrder.contactid = vtiger_purchaseorder.contactid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0"; } else { if ($module == "Invoice") { $matrix = $this->queryPlanner->newDependencyMatrix(); $matrix->setDependency('vtiger_inventoryproductrelInvoice', array('vtiger_productsInvoice', 'vtiger_serviceInvoice')); $query = "from vtiger_invoice\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_invoice.invoiceid"; if ($this->queryPlanner->requireTable("vtiger_invoiceaddress")) { $query .= " inner join vtiger_invoiceaddress on vtiger_invoice.invoiceid=vtiger_invoiceaddress.invoiceaddressid"; } if ($this->queryPlanner->requireTable("vtiger_currency_info{$module}")) { $query .= " left join vtiger_currency_info as vtiger_currency_info{$module} on vtiger_currency_info{$module}.id = vtiger_invoice.currency_id"; } // lineItemFieldsInCalculation - is used to when line item fields are used in calculations if ($type !== 'COLUMNSTOTOTAL' || $this->lineItemFieldsInCalculation == true) { // should be present on when line item fields are selected for calculation if ($this->queryPlanner->requireTable("vtiger_inventoryproductrelInvoice", $matrix)) { $query .= " left join vtiger_inventoryproductrel as vtiger_inventoryproductrelInvoice on vtiger_invoice.invoiceid = vtiger_inventoryproductrelInvoice.id"; } if ($this->queryPlanner->requireTable("vtiger_productsInvoice")) { $query .= " left join vtiger_products as vtiger_productsInvoice on vtiger_productsInvoice.productid = vtiger_inventoryproductrelInvoice.productid"; } if ($this->queryPlanner->requireTable("vtiger_serviceInvoice")) { $query .= " left join vtiger_service as vtiger_serviceInvoice on vtiger_serviceInvoice.serviceid = vtiger_inventoryproductrelInvoice.productid"; } } if ($this->queryPlanner->requireTable("vtiger_salesorderInvoice")) { $query .= " left join vtiger_salesorder as vtiger_salesorderInvoice on vtiger_salesorderInvoice.salesorderid=vtiger_invoice.salesorderid"; } if ($this->queryPlanner->requireTable("vtiger_invoicecf")) { $query .= " left join vtiger_invoicecf on vtiger_invoice.invoiceid = vtiger_invoicecf.invoiceid"; } if ($this->queryPlanner->requireTable("vtiger_groupsInvoice")) { $query .= " left join vtiger_groups as vtiger_groupsInvoice on vtiger_groupsInvoice.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable("vtiger_usersInvoice")) { $query .= " left join vtiger_users as vtiger_usersInvoice on vtiger_usersInvoice.id = vtiger_crmentity.smownerid"; } // TODO optimize inclusion of these tables $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; if ($this->queryPlanner->requireTable("vtiger_lastModifiedByInvoice")) { $query .= " left join vtiger_users as vtiger_lastModifiedByInvoice on vtiger_lastModifiedByInvoice.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyInvoice')) { $query .= " left join vtiger_users as vtiger_createdbyInvoice on vtiger_createdbyInvoice.id = vtiger_crmentity.smcreatorid"; } if ($this->queryPlanner->requireTable("vtiger_accountInvoice")) { $query .= " left join vtiger_account as vtiger_accountInvoice on vtiger_accountInvoice.accountid = vtiger_invoice.accountid"; } if ($this->queryPlanner->requireTable("vtiger_contactdetailsInvoice")) { $query .= " left join vtiger_contactdetails as vtiger_contactdetailsInvoice on vtiger_contactdetailsInvoice.contactid = vtiger_invoice.contactid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0"; } else { if ($module == "SalesOrder") { $matrix = $this->queryPlanner->newDependencyMatrix(); $matrix->setDependency('vtiger_inventoryproductrelSalesOrder', array('vtiger_productsSalesOrder', 'vtiger_serviceSalesOrder')); $query = "from vtiger_salesorder\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid"; if ($this->queryPlanner->requireTable("vtiger_salesorderaddress")) { $query .= " inner join vtiger_salesorderaddress on vtiger_salesorder.salesorderid=vtiger_salesorderaddress.salesorderaddressid"; } if ($this->queryPlanner->requireTable("vtiger_currency_info{$module}")) { $query .= " left join vtiger_currency_info as vtiger_currency_info{$module} on vtiger_currency_info{$module}.id = vtiger_salesorder.currency_id"; } if ($type !== 'COLUMNSTOTOTAL' || $this->lineItemFieldsInCalculation == true) { if ($this->queryPlanner->requireTable("vtiger_inventoryproductrelSalesOrder", $matrix)) { $query .= " left join vtiger_inventoryproductrel as vtiger_inventoryproductrelSalesOrder on vtiger_salesorder.salesorderid = vtiger_inventoryproductrelSalesOrder.id"; } if ($this->queryPlanner->requireTable("vtiger_productsSalesOrder")) { $query .= " left join vtiger_products as vtiger_productsSalesOrder on vtiger_productsSalesOrder.productid = vtiger_inventoryproductrelSalesOrder.productid"; } if ($this->queryPlanner->requireTable("vtiger_serviceSalesOrder")) { $query .= " left join vtiger_service as vtiger_serviceSalesOrder on vtiger_serviceSalesOrder.serviceid = vtiger_inventoryproductrelSalesOrder.productid"; } } if ($this->queryPlanner->requireTable("vtiger_salesordercf")) { $query .= " left join vtiger_salesordercf on vtiger_salesorder.salesorderid = vtiger_salesordercf.salesorderid"; } if ($this->queryPlanner->requireTable("vtiger_contactdetailsSalesOrder")) { $query .= " left join vtiger_contactdetails as vtiger_contactdetailsSalesOrder on vtiger_contactdetailsSalesOrder.contactid = vtiger_salesorder.contactid"; } if ($this->queryPlanner->requireTable("vtiger_quotesSalesOrder")) { $query .= " left join vtiger_quotes as vtiger_quotesSalesOrder on vtiger_quotesSalesOrder.quoteid = vtiger_salesorder.quoteid"; } if ($this->queryPlanner->requireTable("vtiger_accountSalesOrder")) { $query .= " left join vtiger_account as vtiger_accountSalesOrder on vtiger_accountSalesOrder.accountid = vtiger_salesorder.accountid"; } if ($this->queryPlanner->requireTable("vtiger_potentialRelSalesOrder")) { $query .= " left join vtiger_potential as vtiger_potentialRelSalesOrder on vtiger_potentialRelSalesOrder.potentialid = vtiger_salesorder.potentialid"; } if ($this->queryPlanner->requireTable("vtiger_invoice_recurring_info")) { $query .= " left join vtiger_invoice_recurring_info on vtiger_invoice_recurring_info.salesorderid = vtiger_salesorder.salesorderid"; } if ($this->queryPlanner->requireTable("vtiger_groupsSalesOrder")) { $query .= " left join vtiger_groups as vtiger_groupsSalesOrder on vtiger_groupsSalesOrder.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable("vtiger_usersSalesOrder")) { $query .= " left join vtiger_users as vtiger_usersSalesOrder on vtiger_usersSalesOrder.id = vtiger_crmentity.smownerid"; } // TODO optimize inclusion of these tables $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; if ($this->queryPlanner->requireTable("vtiger_lastModifiedBySalesOrder")) { $query .= " left join vtiger_users as vtiger_lastModifiedBySalesOrder on vtiger_lastModifiedBySalesOrder.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbySalesOrder')) { $query .= " left join vtiger_users as vtiger_createdbySalesOrder on vtiger_createdbySalesOrder.id = vtiger_crmentity.smcreatorid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0"; } else { if ($module == "Campaigns") { $query = "from vtiger_campaign\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_campaign.campaignid"; if ($this->queryPlanner->requireTable("vtiger_campaignscf")) { $query .= " inner join vtiger_campaignscf as vtiger_campaignscf on vtiger_campaignscf.campaignid=vtiger_campaign.campaignid"; } if ($this->queryPlanner->requireTable("vtiger_productsCampaigns")) { $query .= " left join vtiger_products as vtiger_productsCampaigns on vtiger_productsCampaigns.productid = vtiger_campaign.product_id"; } if ($this->queryPlanner->requireTable("vtiger_groupsCampaigns")) { $query .= " left join vtiger_groups as vtiger_groupsCampaigns on vtiger_groupsCampaigns.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable("vtiger_usersCampaigns")) { $query .= " left join vtiger_users as vtiger_usersCampaigns on vtiger_usersCampaigns.id = vtiger_crmentity.smownerid"; } // TODO optimize inclusion of these tables $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; if ($this->queryPlanner->requireTable("vtiger_lastModifiedBy{$module}")) { $query .= " left join vtiger_users as vtiger_lastModifiedBy" . $module . " on vtiger_lastModifiedBy" . $module . ".id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable("vtiger_createdby{$module}")) { $query .= " left join vtiger_users as vtiger_createdby{$module} on vtiger_createdby{$module}.id = vtiger_crmentity.smcreatorid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0"; } else { if ($module == "Emails") { $query = "from vtiger_activity\n\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid AND vtiger_activity.activitytype = 'Emails'"; if ($this->queryPlanner->requireTable("vtiger_email_track")) { $query .= " LEFT JOIN vtiger_email_track ON vtiger_email_track.mailid = vtiger_activity.activityid"; } if ($this->queryPlanner->requireTable("vtiger_groupsEmails")) { $query .= " LEFT JOIN vtiger_groups AS vtiger_groupsEmails ON vtiger_groupsEmails.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable("vtiger_usersEmails")) { $query .= " LEFT JOIN vtiger_users AS vtiger_usersEmails ON vtiger_usersEmails.id = vtiger_crmentity.smownerid"; } // TODO optimize inclusion of these tables $query .= " LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid"; if ($this->queryPlanner->requireTable("vtiger_lastModifiedBy{$module}")) { $query .= " LEFT JOIN vtiger_users AS vtiger_lastModifiedBy" . $module . " ON vtiger_lastModifiedBy" . $module . ".id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable("vtiger_createdby{$module}")) { $query .= " left join vtiger_users as vtiger_createdby{$module} on vtiger_createdby{$module}.id = vtiger_crmentity.smcreatorid"; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " WHERE vtiger_crmentity.deleted = 0"; } else { if ($module != '') { $focus = CRMEntity::getInstance($module); $query = $focus->generateReportsQuery($module, $this->queryPlanner) . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " WHERE vtiger_crmentity.deleted=0"; } } } } } } } } } } } } } } $log->info("ReportRun :: Successfully returned getReportsQuery" . $module); 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 to get report query for the given module * @ param $module : type String * this returns join query for the given module */ function getReportsQuery($module) { global $log, $current_user; $secondary_module = "'"; $secondary_module .= str_replace(":", "','", $this->secondarymodule); $secondary_module .= "'"; if ($module == "Leads") { $query = "from vtiger_leaddetails \n inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_leaddetails.leadid \n inner join vtiger_leadsubdetails on vtiger_leadsubdetails.leadsubscriptionid=vtiger_leaddetails.leadid \n inner join vtiger_leadaddress on vtiger_leadaddress.leadaddressid=vtiger_leadsubdetails.leadsubscriptionid \n inner join vtiger_leadscf on vtiger_leaddetails.leadid = vtiger_leadscf.leadid \n left join vtiger_groups as vtiger_groupsLeads on vtiger_groupsLeads.groupid = vtiger_crmentity.smownerid\n left join vtiger_users as vtiger_usersLeads on vtiger_usersLeads.id = vtiger_crmentity.smownerid\n left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid\n " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n where vtiger_crmentity.deleted=0 and vtiger_leaddetails.converted=0"; } else { if ($module == "Accounts") { $query = "from vtiger_account \n inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_account.accountid \n inner join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid \n inner join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid \n inner join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid \n left join vtiger_groups as vtiger_groupsAccounts on vtiger_groupsAccounts.groupid = vtiger_crmentity.smownerid\n left join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid\n left join vtiger_users as vtiger_usersAccounts on vtiger_usersAccounts.id = vtiger_crmentity.smownerid\n left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid\n " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n where vtiger_crmentity.deleted=0 "; } else { if ($module == "Contacts") { $query = "from vtiger_contactdetails\n inner join vtiger_crmentity on vtiger_crmentity.crmid = vtiger_contactdetails.contactid \n inner join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid \n inner join vtiger_customerdetails on vtiger_customerdetails.customerid = vtiger_contactdetails.contactid\n inner join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid \n inner join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid \n left join vtiger_groups vtiger_groupsContacts on vtiger_groupsContacts.groupid = vtiger_crmentity.smownerid\n left join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto\n left join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.accountid \n left join vtiger_users as vtiger_usersContacts on vtiger_usersContacts.id = vtiger_crmentity.smownerid\n left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid\n left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n where vtiger_crmentity.deleted=0"; } else { if ($module == "Potentials") { $query = "from vtiger_potential \n inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_potential.potentialid \n inner join vtiger_potentialscf on vtiger_potentialscf.potentialid = vtiger_potential.potentialid\n left join vtiger_account as vtiger_accountPotentials on vtiger_potential.related_to = vtiger_accountPotentials.accountid\n left join vtiger_contactdetails as vtiger_contactdetailsPotentials on vtiger_potential.related_to = vtiger_contactdetailsPotentials.contactid \n left join vtiger_campaign as vtiger_campaignPotentials on vtiger_potential.campaignid = vtiger_campaignPotentials.campaignid\n left join vtiger_groups vtiger_groupsPotentials on vtiger_groupsPotentials.groupid = vtiger_crmentity.smownerid\n left join vtiger_users as vtiger_usersPotentials on vtiger_usersPotentials.id = vtiger_crmentity.smownerid \n left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid \n " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n where vtiger_crmentity.deleted=0 "; } else { if ($module == "Products") { $query = "from vtiger_products \n inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_products.productid \n left join vtiger_productcf on vtiger_products.productid = vtiger_productcf.productid \n left join vtiger_users as vtiger_usersProducts on vtiger_usersProducts.id = vtiger_products.handler \n left join vtiger_vendor as vtiger_vendorRelProducts on vtiger_vendorRelProducts.vendorid = vtiger_products.vendor_id \n LEFT JOIN (\n SELECT vtiger_products.productid, \n (CASE WHEN (vtiger_products.currency_id = 1 ) THEN vtiger_products.unit_price\n ELSE (vtiger_products.unit_price / vtiger_currency_info.conversion_rate) END\n ) AS actual_unit_price\n FROM vtiger_products\n LEFT JOIN vtiger_currency_info ON vtiger_products.currency_id = vtiger_currency_info.id\n LEFT JOIN vtiger_productcurrencyrel ON vtiger_products.productid = vtiger_productcurrencyrel.productid\n AND vtiger_productcurrencyrel.currencyid = " . $current_user->currency_id . "\n ) AS innerProduct ON innerProduct.productid = vtiger_products.productid\n " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n where vtiger_crmentity.deleted=0"; } else { if ($module == "HelpDesk") { $query = "from vtiger_troubletickets \n inner join vtiger_crmentity \n on vtiger_crmentity.crmid=vtiger_troubletickets.ticketid \n inner join vtiger_ticketcf on vtiger_ticketcf.ticketid = vtiger_troubletickets.ticketid\n left join vtiger_crmentity as vtiger_crmentityRelHelpDesk on vtiger_crmentityRelHelpDesk.crmid = vtiger_troubletickets.parent_id\n left join vtiger_account as vtiger_accountRelHelpDesk on vtiger_accountRelHelpDesk.accountid=vtiger_crmentityRelHelpDesk.crmid \n left join vtiger_contactdetails as vtiger_contactdetailsRelHelpDesk on vtiger_contactdetailsRelHelpDesk.contactid= vtiger_crmentityRelHelpDesk.crmid\n left join vtiger_products as vtiger_productsRel on vtiger_productsRel.productid = vtiger_troubletickets.product_id \n left join vtiger_groups as vtiger_groupsHelpDesk on vtiger_groupsHelpDesk.groupid = vtiger_crmentity.smownerid\n left join vtiger_users as vtiger_usersHelpDesk on vtiger_crmentity.smownerid=vtiger_usersHelpDesk.id \n left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n left join vtiger_users on vtiger_crmentity.smownerid=vtiger_users.id \n " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n where vtiger_crmentity.deleted=0 "; } else { if ($module == "Calendar") { $query = "from vtiger_activity \n inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid\n left join vtiger_activitycf on vtiger_activitycf.activityid = vtiger_crmentity.crmid\n left join vtiger_cntactivityrel on vtiger_cntactivityrel.activityid= vtiger_activity.activityid \n left join vtiger_contactdetails as vtiger_contactdetailsCalendar on vtiger_contactdetailsCalendar.contactid= vtiger_cntactivityrel.contactid\n left join vtiger_groups as vtiger_groupsCalendar on vtiger_groupsCalendar.groupid = vtiger_crmentity.smownerid\n left join vtiger_users as vtiger_usersCalendar on vtiger_usersCalendar.id = vtiger_crmentity.smownerid\n left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid\n left join vtiger_seactivityrel on vtiger_seactivityrel.activityid = vtiger_activity.activityid\n left join vtiger_activity_reminder on vtiger_activity_reminder.activity_id = vtiger_activity.activityid\n left join vtiger_recurringevents on vtiger_recurringevents.activityid = vtiger_activity.activityid\n left join vtiger_crmentity as vtiger_crmentityRelCalendar on vtiger_crmentityRelCalendar.crmid = vtiger_seactivityrel.crmid\n left join vtiger_account as vtiger_accountRelCalendar on vtiger_accountRelCalendar.accountid=vtiger_crmentityRelCalendar.crmid\n left join vtiger_leaddetails as vtiger_leaddetailsRelCalendar on vtiger_leaddetailsRelCalendar.leadid = vtiger_crmentityRelCalendar.crmid\n left join vtiger_potential as vtiger_potentialRelCalendar on vtiger_potentialRelCalendar.potentialid = vtiger_crmentityRelCalendar.crmid\n left join vtiger_quotes as vtiger_quotesRelCalendar on vtiger_quotesRelCalendar.quoteid = vtiger_crmentityRelCalendar.crmid\n left join vtiger_purchaseorder as vtiger_purchaseorderRelCalendar on vtiger_purchaseorderRelCalendar.purchaseorderid = vtiger_crmentityRelCalendar.crmid\n left join vtiger_invoice as vtiger_invoiceRelCalendar on vtiger_invoiceRelCalendar.invoiceid = vtiger_crmentityRelCalendar.crmid\n left join vtiger_salesorder as vtiger_salesorderRelCalendar on vtiger_salesorderRelCalendar.salesorderid = vtiger_crmentityRelCalendar.crmid\n left join vtiger_troubletickets as vtiger_troubleticketsRelCalendar on vtiger_troubleticketsRelCalendar.ticketid = vtiger_crmentityRelCalendar.crmid\n left join vtiger_campaign as vtiger_campaignRelCalendar on vtiger_campaignRelCalendar.campaignid = vtiger_crmentityRelCalendar.crmid\n " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n WHERE vtiger_crmentity.deleted=0 and (vtiger_activity.activitytype != 'Emails')"; } else { if ($module == "Quotes") { $query = "from vtiger_quotes \n inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_quotes.quoteid \n inner join vtiger_quotesbillads on vtiger_quotes.quoteid=vtiger_quotesbillads.quotebilladdressid \n inner join vtiger_quotesshipads on vtiger_quotes.quoteid=vtiger_quotesshipads.quoteshipaddressid\n left join vtiger_inventoryproductrel as vtiger_inventoryproductrelQuotes on vtiger_quotes.quoteid = vtiger_inventoryproductrelQuotes.id\n left join vtiger_products as vtiger_productsQuotes on vtiger_productsQuotes.productid = vtiger_inventoryproductrelQuotes.productid \n left join vtiger_service as vtiger_serviceQuotes on vtiger_serviceQuotes.serviceid = vtiger_inventoryproductrelQuotes.productid\n left join vtiger_quotescf on vtiger_quotes.quoteid = vtiger_quotescf.quoteid\n left join vtiger_groups as vtiger_groupsQuotes on vtiger_groupsQuotes.groupid = vtiger_crmentity.smownerid\n left join vtiger_users as vtiger_usersQuotes on vtiger_usersQuotes.id = vtiger_crmentity.smownerid\n left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid\n left join vtiger_users as vtiger_usersRel1 on vtiger_usersRel1.id = vtiger_quotes.inventorymanager\n left join vtiger_potential as vtiger_potentialRelQuotes on vtiger_potentialRelQuotes.potentialid = vtiger_quotes.potentialid\n left join vtiger_contactdetails as vtiger_contactdetailsQuotes on vtiger_contactdetailsQuotes.contactid = vtiger_quotes.contactid\n left join vtiger_account as vtiger_accountQuotes on vtiger_accountQuotes.accountid = vtiger_quotes.accountid\n " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n where vtiger_crmentity.deleted=0"; } else { if ($module == "PurchaseOrder") { $query = "from vtiger_purchaseorder \n inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_purchaseorder.purchaseorderid \n inner join vtiger_pobillads on vtiger_purchaseorder.purchaseorderid=vtiger_pobillads.pobilladdressid \n inner join vtiger_poshipads on vtiger_purchaseorder.purchaseorderid=vtiger_poshipads.poshipaddressid\n left join vtiger_inventoryproductrel as vtiger_inventoryproductrelPurchaseOrder on vtiger_purchaseorder.purchaseorderid = vtiger_inventoryproductrelPurchaseOrder.id\n left join vtiger_products as vtiger_productsPurchaseOrder on vtiger_productsPurchaseOrder.productid = vtiger_inventoryproductrelPurchaseOrder.productid \n left join vtiger_service as vtiger_servicePurchaseOrder on vtiger_servicePurchaseOrder.serviceid = vtiger_inventoryproductrelPurchaseOrder.productid\n left join vtiger_purchaseordercf on vtiger_purchaseorder.purchaseorderid = vtiger_purchaseordercf.purchaseorderid\n left join vtiger_groups as vtiger_groupsPurchaseOrder on vtiger_groupsPurchaseOrder.groupid = vtiger_crmentity.smownerid\n left join vtiger_users as vtiger_usersPurchaseOrder on vtiger_usersPurchaseOrder.id = vtiger_crmentity.smownerid \n left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid \n left join vtiger_vendor as vtiger_vendorRelPurchaseOrder on vtiger_vendorRelPurchaseOrder.vendorid = vtiger_purchaseorder.vendorid \n left join vtiger_contactdetails as vtiger_contactdetailsPurchaseOrder on vtiger_contactdetailsPurchaseOrder.contactid = vtiger_purchaseorder.contactid \n " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0"; } else { if ($module == "Invoice") { $query = "from vtiger_invoice \n inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_invoice.invoiceid \n inner join vtiger_invoicebillads on vtiger_invoice.invoiceid=vtiger_invoicebillads.invoicebilladdressid \n inner join vtiger_invoiceshipads on vtiger_invoice.invoiceid=vtiger_invoiceshipads.invoiceshipaddressid\n left join vtiger_inventoryproductrel as vtiger_inventoryproductrelInvoice on vtiger_invoice.invoiceid = vtiger_inventoryproductrelInvoice.id\n left join vtiger_products as vtiger_productsInvoice on vtiger_productsInvoice.productid = vtiger_inventoryproductrelInvoice.productid\n left join vtiger_service as vtiger_serviceInvoice on vtiger_serviceInvoice.serviceid = vtiger_inventoryproductrelInvoice.productid\n left join vtiger_salesorder as vtiger_salesorderInvoice on vtiger_salesorderInvoice.salesorderid=vtiger_invoice.salesorderid\n left join vtiger_invoicecf on vtiger_invoice.invoiceid = vtiger_invoicecf.invoiceid \n left join vtiger_groups as vtiger_groupsInvoice on vtiger_groupsInvoice.groupid = vtiger_crmentity.smownerid\n left join vtiger_users as vtiger_usersInvoice on vtiger_usersInvoice.id = vtiger_crmentity.smownerid\n left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid\n left join vtiger_account as vtiger_accountInvoice on vtiger_accountInvoice.accountid = vtiger_invoice.accountid\n left join vtiger_contactdetails as vtiger_contactdetailsInvoice on vtiger_contactdetailsInvoice.contactid = vtiger_invoice.contactid\n " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n where vtiger_crmentity.deleted=0"; } else { if ($module == "SalesOrder") { $query = "from vtiger_salesorder \n inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid \n inner join vtiger_sobillads on vtiger_salesorder.salesorderid=vtiger_sobillads.sobilladdressid \n inner join vtiger_soshipads on vtiger_salesorder.salesorderid=vtiger_soshipads.soshipaddressid\n left join vtiger_inventoryproductrel as vtiger_inventoryproductrelSalesOrder on vtiger_salesorder.salesorderid = vtiger_inventoryproductrelSalesOrder.id\n left join vtiger_products as vtiger_productsSalesOrder on vtiger_productsSalesOrder.productid = vtiger_inventoryproductrelSalesOrder.productid \n left join vtiger_service as vtiger_serviceSalesOrder on vtiger_serviceSalesOrder.serviceid = vtiger_inventoryproductrelSalesOrder.productid\n left join vtiger_contactdetails as vtiger_contactdetailsSalesOrder on vtiger_contactdetailsSalesOrder.contactid = vtiger_salesorder.contactid\n left join vtiger_quotes as vtiger_quotesSalesOrder on vtiger_quotesSalesOrder.quoteid = vtiger_salesorder.quoteid\t\t\t\t\n left join vtiger_account as vtiger_accountSalesOrder on vtiger_accountSalesOrder.accountid = vtiger_salesorder.accountid\n left join vtiger_potential as vtiger_potentialRelSalesOrder on vtiger_potentialRelSalesOrder.potentialid = vtiger_salesorder.potentialid \n left join vtiger_invoice_recurring_info on vtiger_invoice_recurring_info.salesorderid = vtiger_salesorder.salesorderid\n left join vtiger_groups as vtiger_groupsSalesOrder on vtiger_groupsSalesOrder.groupid = vtiger_crmentity.smownerid\n left join vtiger_users as vtiger_usersSalesOrder on vtiger_usersSalesOrder.id = vtiger_crmentity.smownerid \n left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid \n " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n where vtiger_crmentity.deleted=0"; } else { if ($module == "Campaigns") { $query = "from vtiger_campaign\n inner join vtiger_campaignscf as vtiger_campaignscf on vtiger_campaignscf.campaignid=vtiger_campaign.campaignid \n inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_campaign.campaignid\n left join vtiger_products as vtiger_productsCampaigns on vtiger_productsCampaigns.productid = vtiger_campaign.product_id\n left join vtiger_groups as vtiger_groupsCampaigns on vtiger_groupsCampaigns.groupid = vtiger_crmentity.smownerid\n left join vtiger_users as vtiger_usersCampaigns on vtiger_usersCampaigns.id = vtiger_crmentity.smownerid\n left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid\n " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n where vtiger_crmentity.deleted=0"; } else { if ($module != '') { //fixing the bug of missing join to related tables $focus = CRMEntity::getInstance($module); $query = $this->generateReportsQuery($module); if ($module == "Cashflow4You" && $this->secondarymodule == "Invoice") { $query .= " left join its4you_cashflow4you_associatedto on its4you_cashflow4you_associatedto.cashflow4youid = vtiger_crmentity.crmid "; } $query .= $this->getRelatedModulesQuery($module, $this->secondarymodule); if ($module == "Cashflow4You" && $this->secondarymodule == "Invoice") { $query = str_replace("left join vtiger_invoice on its4you_cashflow4you.relationid=vtiger_invoice.invoiceid", "left join vtiger_invoice on its4you_cashflow4you.relationid=vtiger_invoice.invoiceid or its4you_cashflow4you_associatedto.cashflow4you_associated_id = vtiger_invoice.invoiceid", $query); } switch ($this->secondarymodule) { //case "HelpDesk": // $query .= " left join vtiger_ticketcomments on vtiger_ticketcomments.ticketid=vtiger_troubletickets.ticketid "; // break; case "Faq": $query .= " left join vtiger_products as vtiger_productsfaq on vtiger_productsfaq.productid=vtiger_faq.product_id \n left join vtiger_faqcomments on vtiger_faqcomments.faqid=vtiger_faq.id "; break; } $query .= getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " WHERE vtiger_crmentity.deleted=0"; } } } } } } } } } } } } } // ITS4YOU MaJu if ($module == 'PriceBooks' && $this->secondarymodule == 'Products') { $query = str_replace('left join vtiger_crmentity as vtiger_crmentityProducts', 'inner join vtiger_crmentity as vtiger_crmentityProducts', $query); } elseif ($module == "Potentials" && $this->secondarymodule == "Contacts") { $query = str_replace("left join vtiger_contactdetails as vtiger_contactdetailsPotentials on vtiger_potential.related_to = vtiger_contactdetailsPotentials.contactid", "left join vtiger_contpotentialrel on vtiger_contpotentialrel.potentialid = vtiger_potential.potentialid\n left join vtiger_contactdetails as vtiger_contactdetailsPotentials on vtiger_contpotentialrel.contactid = vtiger_contactdetailsPotentials.contactid ", $query); $query = str_replace("on vtiger_potential.related_to=vtiger_contactdetails.contactid", "on vtiger_contpotentialrel.contactid = vtiger_contactdetails.contactid", $query); } // ITS4YOU-END $log->info("ReportRun :: Successfully returned getReportsQuery" . $module); $sdpos = strpos($query, "vtiger_crmentity" . $this->secondarymodule . "."); if ($sdpos !== false) { $query .= " AND vtiger_crmentity" . $this->secondarymodule . ".deleted= '0'"; } $query .= " AND vtiger_crmentity.crmid= '" . $this->crmid . "'"; return $query; }
/** function to get report query for the given module * @ param $module : type String * this returns join query for the given module */ function getReportsQuery($module, $type = '', $where_condition = '') { global $log, $current_user; $secondary_module = "'"; $secondary_module .= str_replace(":", "','", $this->secondarymodule); $secondary_module .= "'"; if ($module == "Leads") { $query = "from vtiger_leaddetails\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_leaddetails.leadid\n\t\t\t\tinner join vtiger_leadsubdetails on vtiger_leadsubdetails.leadsubscriptionid=vtiger_leaddetails.leadid\n\t\t\t\tinner join vtiger_leadaddress on vtiger_leadaddress.leadaddressid=vtiger_leadsubdetails.leadsubscriptionid\n\t\t\t\tinner join vtiger_leadscf on vtiger_leaddetails.leadid = vtiger_leadscf.leadid\n\t\t\t\tleft join vtiger_groups as vtiger_groupsLeads on vtiger_groupsLeads.groupid = vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_users as vtiger_usersLeads on vtiger_usersLeads.id = vtiger_crmentity.smownerid\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\n\t\t\t\tleft join vtiger_users as vtiger_lastModifiedByLeads on vtiger_lastModifiedByLeads.id = vtiger_crmentity.modifiedby\n\t\t\t\t" . $this->getRelatedModulesQuery($module, $this->secondarymodule, $type, $where_condition) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0 and vtiger_leaddetails.converted=0"; } else { if ($module == "Accounts") { $focus = CRMEntity::getInstance($module); $query = $focus->generateReportsQuery($module); $query .= " inner join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid\n\t\t\t\tinner join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid\n\t\t\t\tleft join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid\n\t\t\t\t" . $this->getRelatedModulesQuery($module, $this->secondarymodule, $type, $where_condition) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0 "; } else { if ($module == "Contacts") { $query = "from vtiger_contactdetails\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid = vtiger_contactdetails.contactid\n\t\t\t\tinner join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid\n\t\t\t\tinner join vtiger_customerdetails on vtiger_customerdetails.customerid = vtiger_contactdetails.contactid\n\t\t\t\tinner join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid\n\t\t\t\tinner join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid\n\t\t\t\tleft join vtiger_groups vtiger_groupsContacts on vtiger_groupsContacts.groupid = vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto\n\t\t\t\tleft join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.accountid\n\t\t\t\tleft join vtiger_users as vtiger_usersContacts on vtiger_usersContacts.id = vtiger_crmentity.smownerid\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\tleft join vtiger_users as vtiger_lastModifiedByContacts on vtiger_lastModifiedByContacts.id = vtiger_crmentity.modifiedby\n\t\t\t\t" . $this->getRelatedModulesQuery($module, $this->secondarymodule, $type, $where_condition) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0"; } else { if ($module == "Potentials") { $query = "from vtiger_potential\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_potential.potentialid\n\t\t\t\tinner join vtiger_potentialscf on vtiger_potentialscf.potentialid = vtiger_potential.potentialid\n\t\t\t\tleft join vtiger_account as vtiger_accountPotentials on vtiger_potential.related_to = vtiger_accountPotentials.accountid\n\t\t\t\tleft join vtiger_contactdetails as vtiger_contactdetailsPotentials on vtiger_potential.related_to = vtiger_contactdetailsPotentials.contactid\n\t\t\t\tleft join vtiger_campaign as vtiger_campaignPotentials on vtiger_potential.campaignid = vtiger_campaignPotentials.campaignid\n\t\t\t\tleft join vtiger_groups vtiger_groupsPotentials on vtiger_groupsPotentials.groupid = vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_users as vtiger_usersPotentials on vtiger_usersPotentials.id = vtiger_crmentity.smownerid\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\n\t\t\t\tleft join vtiger_users as vtiger_lastModifiedByPotentials on vtiger_lastModifiedByPotentials.id = vtiger_crmentity.modifiedby\n\t\t\t\t" . $this->getRelatedModulesQuery($module, $this->secondarymodule, $type, $where_condition) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0 "; } else { if ($module == "Products") { $focus = CRMEntity::getInstance($module); $query = $focus->generateReportsQuery($module); $query .= " left join vtiger_vendor as vtiger_vendorRelProducts on vtiger_vendorRelProducts.vendorid = vtiger_products.vendor_id\n\t\t\t\tLEFT JOIN (\n\t\t\t\t\t\tSELECT vtiger_products.productid,\n\t\t\t\t\t\t\t\t(CASE WHEN (vtiger_products.currency_id = 1 ) THEN vtiger_products.unit_price\n\t\t\t\t\t\t\t\t\tELSE (vtiger_products.unit_price / vtiger_currency_info.conversion_rate) END\n\t\t\t\t\t\t\t\t) AS actual_unit_price\n\t\t\t\t\t\tFROM vtiger_products\n\t\t\t\t\t\tLEFT JOIN vtiger_currency_info ON vtiger_products.currency_id = vtiger_currency_info.id\n\t\t\t\t\t\tLEFT JOIN vtiger_productcurrencyrel ON vtiger_products.productid = vtiger_productcurrencyrel.productid\n\t\t\t\t\t\tAND vtiger_productcurrencyrel.currencyid = " . $current_user->currency_id . "\n\t\t\t\t) AS innerProduct ON innerProduct.productid = vtiger_products.productid\n\t\t\t\t" . $this->getRelatedModulesQuery($module, $this->secondarymodule, $type, $where_condition) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0"; } else { if ($module == "HelpDesk") { $query = "from vtiger_troubletickets\n\t\t\t\tinner join vtiger_crmentity\n\t\t\t\ton vtiger_crmentity.crmid=vtiger_troubletickets.ticketid\n\t\t\t\tinner join vtiger_ticketcf on vtiger_ticketcf.ticketid = vtiger_troubletickets.ticketid\n\t\t\t\tleft join vtiger_crmentity as vtiger_crmentityRelHelpDesk on vtiger_crmentityRelHelpDesk.crmid = vtiger_troubletickets.parent_id\n\t\t\t\tleft join vtiger_account as vtiger_accountRelHelpDesk on vtiger_accountRelHelpDesk.accountid=vtiger_crmentityRelHelpDesk.crmid\n\t\t\t\tleft join vtiger_contactdetails as vtiger_contactdetailsRelHelpDesk on vtiger_contactdetailsRelHelpDesk.contactid= vtiger_crmentityRelHelpDesk.crmid\n\t\t\t\tleft join vtiger_products as vtiger_productsRel on vtiger_productsRel.productid = vtiger_troubletickets.product_id\n\t\t\t\tleft join vtiger_groups as vtiger_groupsHelpDesk on vtiger_groupsHelpDesk.groupid = vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_users as vtiger_usersHelpDesk on vtiger_crmentity.smownerid=vtiger_usersHelpDesk.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_crmentity.smownerid=vtiger_users.id\n\t\t\t\tleft join vtiger_users as vtiger_lastModifiedByHelpDesk on vtiger_lastModifiedByHelpDesk.id = vtiger_crmentity.modifiedby\n\t\t\t\t" . $this->getRelatedModulesQuery($module, $this->secondarymodule, $type, $where_condition) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0 "; } else { if ($module == "Calendar") { $query = "from vtiger_activity\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid\n\t\t\t\tleft join vtiger_activitycf on vtiger_activitycf.activityid = vtiger_crmentity.crmid\n\t\t\t\tleft join vtiger_cntactivityrel on vtiger_cntactivityrel.activityid= vtiger_activity.activityid\n\t\t\t\tleft join vtiger_contactdetails as vtiger_contactdetailsCalendar on vtiger_contactdetailsCalendar.contactid= vtiger_cntactivityrel.contactid\n\t\t\t\tleft join vtiger_groups as vtiger_groupsCalendar on vtiger_groupsCalendar.groupid = vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_users as vtiger_usersCalendar on vtiger_usersCalendar.id = vtiger_crmentity.smownerid\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\n\t\t\t\tleft join vtiger_seactivityrel on vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\tleft join vtiger_activity_reminder on vtiger_activity_reminder.activity_id = vtiger_activity.activityid\n\t\t\t\tleft join vtiger_recurringevents on vtiger_recurringevents.activityid = vtiger_activity.activityid\n\t\t\t\tleft join vtiger_crmentity as vtiger_crmentityRelCalendar on vtiger_crmentityRelCalendar.crmid = vtiger_seactivityrel.crmid\n\t\t\t\tleft join vtiger_account as vtiger_accountRelCalendar on vtiger_accountRelCalendar.accountid=vtiger_crmentityRelCalendar.crmid\n\t\t\t\tleft join vtiger_leaddetails as vtiger_leaddetailsRelCalendar on vtiger_leaddetailsRelCalendar.leadid = vtiger_crmentityRelCalendar.crmid\n\t\t\t\tleft join vtiger_potential as vtiger_potentialRelCalendar on vtiger_potentialRelCalendar.potentialid = vtiger_crmentityRelCalendar.crmid\n\t\t\t\tleft join vtiger_quotes as vtiger_quotesRelCalendar on vtiger_quotesRelCalendar.quoteid = vtiger_crmentityRelCalendar.crmid\n\t\t\t\tleft join vtiger_purchaseorder as vtiger_purchaseorderRelCalendar on vtiger_purchaseorderRelCalendar.purchaseorderid = vtiger_crmentityRelCalendar.crmid\n\t\t\t\tleft join vtiger_invoice as vtiger_invoiceRelCalendar on vtiger_invoiceRelCalendar.invoiceid = vtiger_crmentityRelCalendar.crmid\n\t\t\t\tleft join vtiger_salesorder as vtiger_salesorderRelCalendar on vtiger_salesorderRelCalendar.salesorderid = vtiger_crmentityRelCalendar.crmid\n\t\t\t\tleft join vtiger_troubletickets as vtiger_troubleticketsRelCalendar on vtiger_troubleticketsRelCalendar.ticketid = vtiger_crmentityRelCalendar.crmid\n\t\t\t\tleft join vtiger_campaign as vtiger_campaignRelCalendar on vtiger_campaignRelCalendar.campaignid = vtiger_crmentityRelCalendar.crmid\n\t\t\t\tleft join vtiger_vendor as vtiger_vendorRelCalendar on vtiger_vendorRelCalendar.vendorid = vtiger_crmentityRelCalendar.crmid\n\t\t\t\tleft join vtiger_users as vtiger_lastModifiedByCalendar on vtiger_lastModifiedByCalendar.id = vtiger_crmentity.modifiedby\n\t\t\t\t" . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\tWHERE vtiger_crmentity.deleted=0 and (vtiger_activity.activitytype != 'Emails')"; } else { if ($module == "Emails") { $query = "FROM vtiger_activity\n\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid AND vtiger_activity.activitytype = 'Emails'"; $query .= " LEFT JOIN vtiger_email_track ON vtiger_email_track.mailid = vtiger_activity.activityid"; $query .= " LEFT JOIN vtiger_emaildetails ON vtiger_emaildetails.emailid=vtiger_activity.activityid"; // TODO optimize inclusion of these tables $query .= " LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid"; $query .= " LEFT JOIN vtiger_users AS vtiger_lastModifiedBy" . $module . " ON vtiger_lastModifiedBy" . $module . ".id = vtiger_crmentity.modifiedby"; $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " WHERE vtiger_crmentity.deleted = 0"; } else { if ($module == "Quotes") { $query = "from vtiger_quotes\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_quotes.quoteid\n\t\t\t\tinner join vtiger_quotesbillads on vtiger_quotes.quoteid=vtiger_quotesbillads.quotebilladdressid\n\t\t\t\tinner join vtiger_quotesshipads on vtiger_quotes.quoteid=vtiger_quotesshipads.quoteshipaddressid\n\t\t\t\tleft join vtiger_currency_info as vtiger_currency_info{$module} on vtiger_currency_info{$module}.id = vtiger_quotes.currency_id"; if ($type !== 'COLUMNSTOTOTAL' || $type == 'COLUMNSTOTOTAL' && $where_condition == 'add') { $query .= " left join vtiger_inventoryproductrel as vtiger_inventoryproductrelQuotes on vtiger_quotes.quoteid = vtiger_inventoryproductrelQuotes.id\n\t\t\t\tleft join vtiger_products as vtiger_productsQuotes on vtiger_productsQuotes.productid = vtiger_inventoryproductrelQuotes.productid\n\t\t\t\tleft join vtiger_service as vtiger_serviceQuotes on vtiger_serviceQuotes.serviceid = vtiger_inventoryproductrelQuotes.productid"; } $query .= " left join vtiger_quotescf on vtiger_quotes.quoteid = vtiger_quotescf.quoteid\n\t\t\t\tleft join vtiger_groups as vtiger_groupsQuotes on vtiger_groupsQuotes.groupid = vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_users as vtiger_usersQuotes on vtiger_usersQuotes.id = vtiger_crmentity.smownerid\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\n\t\t\t\tleft join vtiger_users as vtiger_lastModifiedByQuotes on vtiger_lastModifiedByQuotes.id = vtiger_crmentity.modifiedby\n\t\t\t\tleft join vtiger_users as vtiger_usersRel1 on vtiger_usersRel1.id = vtiger_quotes.inventorymanager\n\t\t\t\tleft join vtiger_potential as vtiger_potentialRelQuotes on vtiger_potentialRelQuotes.potentialid = vtiger_quotes.potentialid\n\t\t\t\tleft join vtiger_contactdetails as vtiger_contactdetailsQuotes on vtiger_contactdetailsQuotes.contactid = vtiger_quotes.contactid\n\t\t\t\tleft join vtiger_account as vtiger_accountQuotes on vtiger_accountQuotes.accountid = vtiger_quotes.accountid\n\t\t\t\t" . $this->getRelatedModulesQuery($module, $this->secondarymodule, $type, $where_condition) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0"; } else { if ($module == "PurchaseOrder") { $query = "from vtiger_purchaseorder\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_purchaseorder.purchaseorderid\n\t\t\t\tinner join vtiger_pobillads on vtiger_purchaseorder.purchaseorderid=vtiger_pobillads.pobilladdressid\n\t\t\t\tinner join vtiger_poshipads on vtiger_purchaseorder.purchaseorderid=vtiger_poshipads.poshipaddressid\n\t\t\t\tleft join vtiger_currency_info as vtiger_currency_info{$module} on vtiger_currency_info{$module}.id = vtiger_purchaseorder.currency_id"; if ($type !== 'COLUMNSTOTOTAL' || $type == 'COLUMNSTOTOTAL' && $where_condition == 'add') { $query .= " left join vtiger_inventoryproductrel as vtiger_inventoryproductrelPurchaseOrder on vtiger_purchaseorder.purchaseorderid = vtiger_inventoryproductrelPurchaseOrder.id\n\t\t\t\tleft join vtiger_products as vtiger_productsPurchaseOrder on vtiger_productsPurchaseOrder.productid = vtiger_inventoryproductrelPurchaseOrder.productid\n\t\t\t\tleft join vtiger_service as vtiger_servicePurchaseOrder on vtiger_servicePurchaseOrder.serviceid = vtiger_inventoryproductrelPurchaseOrder.productid"; } $query .= " left join vtiger_purchaseordercf on vtiger_purchaseorder.purchaseorderid = vtiger_purchaseordercf.purchaseorderid\n\t\t\t\tleft join vtiger_groups as vtiger_groupsPurchaseOrder on vtiger_groupsPurchaseOrder.groupid = vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_users as vtiger_usersPurchaseOrder on vtiger_usersPurchaseOrder.id = vtiger_crmentity.smownerid\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\n\t\t\t\tleft join vtiger_users as vtiger_lastModifiedByPurchaseOrder on vtiger_lastModifiedByPurchaseOrder.id = vtiger_crmentity.modifiedby\n\t\t\t\tleft join vtiger_vendor as vtiger_vendorRelPurchaseOrder on vtiger_vendorRelPurchaseOrder.vendorid = vtiger_purchaseorder.vendorid\n\t\t\t\tleft join vtiger_contactdetails as vtiger_contactdetailsPurchaseOrder on vtiger_contactdetailsPurchaseOrder.contactid = vtiger_purchaseorder.contactid\n\t\t\t\t" . $this->getRelatedModulesQuery($module, $this->secondarymodule, $type, $where_condition) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0"; } else { if ($module == "Invoice") { $query = "from vtiger_invoice\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_invoice.invoiceid\n\t\t\t\tinner join vtiger_invoicebillads on vtiger_invoice.invoiceid=vtiger_invoicebillads.invoicebilladdressid\n\t\t\t\tinner join vtiger_invoiceshipads on vtiger_invoice.invoiceid=vtiger_invoiceshipads.invoiceshipaddressid\n\t\t\t\tleft join vtiger_currency_info as vtiger_currency_info{$module} on vtiger_currency_info{$module}.id = vtiger_invoice.currency_id"; if ($type !== 'COLUMNSTOTOTAL' || $type == 'COLUMNSTOTOTAL' && $where_condition == 'add') { $query .= " left join vtiger_inventoryproductrel as vtiger_inventoryproductrelInvoice on vtiger_invoice.invoiceid = vtiger_inventoryproductrelInvoice.id\n\t\t\t\t\tleft join vtiger_products as vtiger_productsInvoice on vtiger_productsInvoice.productid = vtiger_inventoryproductrelInvoice.productid\n\t\t\t\t\tleft join vtiger_service as vtiger_serviceInvoice on vtiger_serviceInvoice.serviceid = vtiger_inventoryproductrelInvoice.productid"; } $query .= " left join vtiger_salesorder as vtiger_salesorderInvoice on vtiger_salesorderInvoice.salesorderid=vtiger_invoice.salesorderid\n\t\t\t\tleft join vtiger_invoicecf on vtiger_invoice.invoiceid = vtiger_invoicecf.invoiceid\n\t\t\t\tleft join vtiger_groups as vtiger_groupsInvoice on vtiger_groupsInvoice.groupid = vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_users as vtiger_usersInvoice on vtiger_usersInvoice.id = vtiger_crmentity.smownerid\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\n\t\t\t\tleft join vtiger_users as vtiger_lastModifiedByInvoice on vtiger_lastModifiedByInvoice.id = vtiger_crmentity.modifiedby\n\t\t\t\tleft join vtiger_account as vtiger_accountInvoice on vtiger_accountInvoice.accountid = vtiger_invoice.accountid\n\t\t\t\tleft join vtiger_contactdetails as vtiger_contactdetailsInvoice on vtiger_contactdetailsInvoice.contactid = vtiger_invoice.contactid\n\t\t\t\t" . $this->getRelatedModulesQuery($module, $this->secondarymodule, $type, $where_condition) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0"; } else { if ($module == "SalesOrder") { $query = "from vtiger_salesorder\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid\n\t\t\t\tinner join vtiger_sobillads on vtiger_salesorder.salesorderid=vtiger_sobillads.sobilladdressid\n\t\t\t\tinner join vtiger_soshipads on vtiger_salesorder.salesorderid=vtiger_soshipads.soshipaddressid\n\t\t\t\tleft join vtiger_currency_info as vtiger_currency_info{$module} on vtiger_currency_info{$module}.id = vtiger_salesorder.currency_id"; if ($type !== 'COLUMNSTOTOTAL' || $type == 'COLUMNSTOTOTAL' && $where_condition == 'add') { $query .= " left join vtiger_inventoryproductrel as vtiger_inventoryproductrelSalesOrder on vtiger_salesorder.salesorderid = vtiger_inventoryproductrelSalesOrder.id\n\t\t\t\tleft join vtiger_products as vtiger_productsSalesOrder on vtiger_productsSalesOrder.productid = vtiger_inventoryproductrelSalesOrder.productid\n\t\t\t\tleft join vtiger_service as vtiger_serviceSalesOrder on vtiger_serviceSalesOrder.serviceid = vtiger_inventoryproductrelSalesOrder.productid"; } $query .= " left join vtiger_salesordercf on vtiger_salesorder.salesorderid = vtiger_salesordercf.salesorderid\n\t\t\t\tleft join vtiger_contactdetails as vtiger_contactdetailsSalesOrder on vtiger_contactdetailsSalesOrder.contactid = vtiger_salesorder.contactid\n\t\t\t\tleft join vtiger_quotes as vtiger_quotesSalesOrder on vtiger_quotesSalesOrder.quoteid = vtiger_salesorder.quoteid\n\t\t\t\tleft join vtiger_account as vtiger_accountSalesOrder on vtiger_accountSalesOrder.accountid = vtiger_salesorder.accountid\n\t\t\t\tleft join vtiger_potential as vtiger_potentialRelSalesOrder on vtiger_potentialRelSalesOrder.potentialid = vtiger_salesorder.potentialid\n\t\t\t\tleft join vtiger_invoice_recurring_info on vtiger_invoice_recurring_info.salesorderid = vtiger_salesorder.salesorderid\n\t\t\t\tleft join vtiger_groups as vtiger_groupsSalesOrder on vtiger_groupsSalesOrder.groupid = vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_users as vtiger_usersSalesOrder on vtiger_usersSalesOrder.id = vtiger_crmentity.smownerid\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\n\t\t\t\tleft join vtiger_users as vtiger_lastModifiedBySalesOrder on vtiger_lastModifiedBySalesOrder.id = vtiger_crmentity.modifiedby\n\t\t\t\t" . $this->getRelatedModulesQuery($module, $this->secondarymodule, $type, $where_condition) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0"; } else { if ($module == "Campaigns") { $query = "from vtiger_campaign\n\t\t\t\tinner join vtiger_campaignscf as vtiger_campaignscf on vtiger_campaignscf.campaignid=vtiger_campaign.campaignid\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_campaign.campaignid\n\t\t\t\tleft join vtiger_products as vtiger_productsCampaigns on vtiger_productsCampaigns.productid = vtiger_campaign.product_id\n\t\t\t\tleft join vtiger_groups as vtiger_groupsCampaigns on vtiger_groupsCampaigns.groupid = vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_users as vtiger_usersCampaigns on vtiger_usersCampaigns.id = vtiger_crmentity.smownerid\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\n\t\t\t\tleft join vtiger_users as vtiger_lastModifiedBy" . $module . " on vtiger_lastModifiedBy" . $module . ".id = vtiger_crmentity.modifiedby\n\t\t\t\t" . $this->getRelatedModulesQuery($module, $this->secondarymodule, $type, $where_condition) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0"; } else { if ($module != '') { $focus = CRMEntity::getInstance($module); $query = $focus->generateReportsQuery($module) . $this->getRelatedModulesQuery($module, $this->secondarymodule, $type, $where_condition) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " WHERE vtiger_crmentity.deleted=0"; } } } } } } } } } } } } } } $log->info("ReportRun :: Successfully returned getReportsQuery" . $module); return $query; }
function getListQuery($module, $usewhere = '') { global $current_user; $query = "SELECT vtiger_crmentity.*,\n\t\t\tvtiger_sp_consignment.*,\n\t\t\tvtiger_sp_consignmentbillads.*,\n\t\t\tvtiger_sp_consignmentshipads.*,\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_sp_consignment\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_sp_consignment.consignmentid\n\t\t\tINNER JOIN vtiger_sp_consignmentbillads\n\t\t\t\tON vtiger_sp_consignment.consignmentid = vtiger_sp_consignmentbillads.consignmentbilladdressid\n\t\t\tINNER JOIN vtiger_sp_consignmentshipads\n\t\t\t\tON vtiger_sp_consignment.consignmentid = vtiger_sp_consignmentshipads.consignmentshipaddressid\n\t\t\tLEFT JOIN vtiger_currency_info\n\t\t\t\tON vtiger_sp_consignment.currency_id = vtiger_currency_info.id\n\t\t\tLEFT OUTER JOIN vtiger_salesorder\n\t\t\t\tON vtiger_salesorder.salesorderid = vtiger_sp_consignment.salesorderid\n\t\t\tLEFT OUTER JOIN vtiger_account\n\t\t\t ON vtiger_account.accountid = vtiger_sp_consignment.accountid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_contactdetails.contactid = vtiger_sp_consignment.contactid\n\t\t\tINNER JOIN vtiger_sp_consignmentcf\n\t\t\t\tON vtiger_sp_consignment.consignmentid = vtiger_sp_consignmentcf.consignmentid\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; 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 __getRLQuery($id, $module, $relatedModule, $queryParameters, $user) { global $adb, $currentModule, $log, $current_user; // Initialize required globals $currentModule = $module; // END if (empty($queryParameters['productDiscriminator'])) { $queryParameters['productDiscriminator'] = ''; } if (empty($queryParameters['columns'])) { $queryParameters['columns'] = '*'; } $productDiscriminator = strtolower($queryParameters['productDiscriminator']); // check modules $webserviceObject = VtigerWebserviceObject::fromName($adb, $relatedModule); $handlerPath = $webserviceObject->getHandlerPath(); $handlerClass = $webserviceObject->getHandlerClass(); require_once $handlerPath; $handler = new $handlerClass($webserviceObject, $user, $adb, $log); $meta = $handler->getMeta(); $relatedModule = $meta->getEntityName(); if (!$meta->isModuleEntity()) { throw new WebserviceException('INVALID_MODULE', "Given related module ({$relatedModule}) cannot be found"); } $relatedModuleId = getTabid($relatedModule); $webserviceObject = VtigerWebserviceObject::fromName($adb, $module); $handlerPath = $webserviceObject->getHandlerPath(); $handlerClass = $webserviceObject->getHandlerClass(); require_once $handlerPath; $handler = new $handlerClass($webserviceObject, $user, $adb, $log); $meta = $handler->getMeta(); $module = $meta->getEntityName(); if (!$meta->isModuleEntity()) { throw new WebserviceException('INVALID_MODULE', "Given module ({$module}) cannot be found"); } $moduleId = getTabid($module); // check permission on module $webserviceObject = VtigerWebserviceObject::fromId($adb, $id); $handlerPath = $webserviceObject->getHandlerPath(); $handlerClass = $webserviceObject->getHandlerClass(); require_once $handlerPath; $handler = new $handlerClass($webserviceObject, $user, $adb, $log); $meta = $handler->getMeta(); $entityName = $meta->getObjectEntityName($id); $types = vtws_listtypes(null, $user); if (!in_array($entityName, $types['types'])) { throw new WebServiceException(WebServiceErrorCode::$ACCESSDENIED, "Permission to perform the operation on module ({$module}) is denied"); } if ($entityName !== $webserviceObject->getEntityName()) { throw new WebServiceException(WebServiceErrorCode::$INVALIDID, "Id specified is incorrect"); } if (!$meta->hasPermission(EntityMeta::$RETRIEVE, $id)) { throw new WebServiceException(WebServiceErrorCode::$ACCESSDENIED, "Permission to read given object is denied"); } $idComponents = vtws_getIdComponents($id); if (!$meta->exists($idComponents[1])) { throw new WebServiceException(WebServiceErrorCode::$RECORDNOTFOUND, "Record you are trying to access is not found"); } $crmid = $idComponents[1]; // check permission on related module and pickup meta data for further processing $webserviceObject = VtigerWebserviceObject::fromName($adb, $relatedModule); $handlerPath = $webserviceObject->getHandlerPath(); $handlerClass = $webserviceObject->getHandlerClass(); require_once $handlerPath; $handler = new $handlerClass($webserviceObject, $user, $adb, $log); $meta = $handler->getMeta(); if (!in_array($relatedModule, $types['types'])) { throw new WebServiceException(WebServiceErrorCode::$ACCESSDENIED, "Permission to perform the operation on module ({$relatedModule}) is denied"); } if (!$meta->hasReadAccess()) { throw new WebServiceException(WebServiceErrorCode::$ACCESSDENIED, "Permission to read given object is denied"); } // user has enough permission to start process $query = ''; switch ($relatedModule) { case 'ModComments': $wsUserIdrs = $adb->query("select id from vtiger_ws_entity where name='Users'"); $wsUserId = $adb->query_result($wsUserIdrs, 0, 0) . 'x'; $wsContactIdrs = $adb->query("select id from vtiger_ws_entity where name='Contacts'"); $wsContactId = $adb->query_result($wsContactIdrs, 0, 0) . 'x'; switch ($module) { case 'HelpDesk': $query = "select\n\t\t\t\t\t\tconcat(case when (ownertype = 'user') then '{$wsUserId}' else '{$wsContactId}' end,ownerid) as creator,\n\t\t\t\t\t\tconcat(case when (ownertype = 'user') then '{$wsUserId}' else '{$wsContactId}' end,ownerid) as assigned_user_id,\n\t\t\t\t\t\t'TicketComments' as setype,\n\t\t\t\t\t\tcreatedtime,\n\t\t\t\t\t\tcreatedtime as modifiedtime,\n\t\t\t\t\t\t0 as id,\n\t\t\t\t\t\tcomments as commentcontent, \n\t\t\t\t\t\t'{$id}' as related_to, \n\t\t\t\t\t\t'' as parent_comments,\n\t\t\t\t\t\townertype,\n\t\t\t\t\t\tcase when (ownertype = 'user') then vtiger_users.user_name else vtiger_portalinfo.user_name end as owner_name \n\t\t\t\t\t from vtiger_ticketcomments\n\t\t\t\t\t left join vtiger_users on vtiger_users.id = ownerid\n\t\t\t\t\t left join vtiger_portalinfo on vtiger_portalinfo.id = ownerid\n\t\t\t\t\t where ticketid={$crmid}"; break; case 'Faq': $query = "select\n\t\t\t\t\t\t0 as creator,\n\t\t\t\t\t\t0 as assigned_user_id,\n\t\t\t\t\t\t'FaqComments' as setype,\n\t\t\t\t\t\tcreatedtime,\n\t\t\t\t\t\tcreatedtime as modifiedtime,\n\t\t\t\t\t\t0 as id,\n\t\t\t\t\t\tcomments as commentcontent, \n\t\t\t\t\t\t'{$id}' as related_to, \n\t\t\t\t\t\t'' as parent_comments\n\t\t\t\t\t from vtiger_faqcomments where faqid={$crmid}"; break; default: $entityInstance = CRMEntity::getInstance($relatedModule); $queryCriteria = ''; $criteria = 'All'; // currently hard coded to all ** TODO ** switch ($criteria) { // currently hard coded to all ** TODO ** case 'All': $queryCriteria = ''; break; case 'Last5': $queryCriteria = sprintf(" ORDER BY %s.%s DESC LIMIT 5", $entityInstance->table_name, $entityInstance->table_index); break; case 'Mine': $queryCriteria = ' AND vtiger_crmentity.smownerid=' . $current_user->id; break; } $query = $entityInstance->getListQuery($moduleName, sprintf(" AND %s.related_to={$crmid}", $entityInstance->table_name)); $query .= $queryCriteria; $qfields = __getRLQueryFields($meta, $queryParameters['columns']); // Remove all the \n, \r and white spaces to keep the space between the words consistent. $query = preg_replace("/[\n\r\\s]+/", " ", $query); $query = "select {$qfields} " . substr($query, stripos($query, ' FROM '), strlen($query)); break; } // end switch ModComments break; default: $relation_criteria = ''; switch ($relatedModule) { case 'Products': if ($module == 'Products') { // Product Bundles if (!empty($productDiscriminator) and $productDiscriminator == 'productparent') { $relation_criteria = " and label like '%parent%'"; } else { $relation_criteria = " and label like '%bundle%'"; // bundle by default } } break; case 'Calendar': $relation_criteria = " and label like '%Activities%'"; // History not supported //$relation_criteria = " and label like '%History%'"; break; } // special product relation with Q/SO/I/PO if ($relatedModule == 'Products' and in_array($module, array('Invoice', 'Quotes', 'SalesOrder', 'PurchaseOrder'))) { $query = 'select productid as id,sequence_no,quantity,listprice,discount_percent,discount_amount,comment,description,tax1,tax2,tax3 FROM vtiger_inventoryproductrel where id=' . $crmid; } else { $relationResult = $adb->pquery("SELECT * FROM vtiger_relatedlists WHERE tabid=? AND related_tabid=? {$relation_criteria}", array($moduleId, $relatedModuleId)); if (!$relationResult || !$adb->num_rows($relationResult)) { throw new WebserviceException('MODULES_NOT_RELATED', "Cannot find relation between {$module} and {$relatedModule}"); } if ($adb->num_rows($relationResult) > 1) { throw new WebserviceException('MANY_RELATIONS', "More than one relation exists between {$module} and {$relatedModule}"); } $relationInfo = $adb->fetch_array($relationResult); $moduleInstance = CRMEntity::getInstance($module); $params = array($crmid, $moduleId, $relatedModuleId); $relationData = call_user_method_array($relationInfo['name'], $moduleInstance, $params); $query = $relationData['query']; // select the fields the user has access to and prepare query $qfields = __getRLQueryFields($meta, $queryParameters['columns']); // Remove all the \n, \r and white spaces to keep the space between the words consistent. $query = preg_replace("/[\n\r\\s]+/", " ", $query); $query = "select {$qfields} " . substr($query, stripos($query, ' FROM '), strlen($query)); // Append additional joins for some queries $query = __getRLQueryFromJoins($query, $meta); //Appending Access Control if ($relatedModule != 'Faq' && $relatedModule != 'PriceBook' && $relatedModule != 'Vendors' && $relatedModule != 'Users') { $secQuery = getNonAdminAccessControlQuery($relatedModule, $current_user); if (strlen($secQuery) > 1) { $query = appendFromClauseToQuery($query, $secQuery); } } // This is for getting products related to Account/Contact through their Quote/SO/Invoice if (($module == 'Accounts' or $module == 'Contacts') and ($relatedModule == 'Products' or $relatedModule == 'Services') and in_array($productDiscriminator, array('productlineinvoice', 'productlinesalesorder', 'productlinequote', 'productlineall', 'productlineinvoiceonly', 'productlinesalesorderonly', 'productlinequoteonly'))) { // Here we add list of products contained in related invoice, so and quotes $relatedField = $module == 'Accounts' ? 'accountid' : 'contactid'; $pstable = $meta->getEntityBaseTable(); $psfield = $meta->getIdColumn(); if (substr($productDiscriminator, -4) == 'only') { $productDiscriminator = substr($productDiscriminator, 0, strlen($productDiscriminator) - 4); $query = ''; } if ($productDiscriminator == 'productlinequote' or $productDiscriminator == 'productlineall') { $q = "select distinct {$qfields} from vtiger_quotes\n\t\t\t\t\t\tinner join vtiger_crmentity as crmq on crmq.crmid=vtiger_quotes.quoteid\n\t\t\t\t\t\tleft join vtiger_inventoryproductrel on vtiger_inventoryproductrel.id=vtiger_quotes.quoteid\n\t\t\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_inventoryproductrel.productid \n\t\t\t\t\t\tleft join {$pstable} on {$pstable}.{$psfield} = vtiger_inventoryproductrel.productid \n\t\t\t\t\t\twhere vtiger_inventoryproductrel.productid = {$pstable}.{$psfield} AND crmq.deleted=0\n\t\t\t\t\t\t and {$relatedField} = {$crmid}"; $query .= ($query == '' ? '' : ' UNION DISTINCT ') . $q; } if ($productDiscriminator == 'productlineinvoice' or $productDiscriminator == 'productlineall') { $q = "select distinct {$qfields} from vtiger_invoice\n\t\t\t\t\t\tinner join vtiger_crmentity as crmi on crmi.crmid=vtiger_invoice.invoiceid\n\t\t\t\t\t\tleft join vtiger_inventoryproductrel on vtiger_inventoryproductrel.id=vtiger_invoice.invoiceid\n\t\t\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_inventoryproductrel.productid\n\t\t\t\t\t\tleft join {$pstable} on {$pstable}.{$psfield} = vtiger_inventoryproductrel.productid\n\t\t\t\t\t\twhere vtiger_inventoryproductrel.productid = {$pstable}.{$psfield} AND crmi.deleted=0\n\t\t\t\t\t\t and {$relatedField} = {$crmid}"; $query .= ($query == '' ? '' : ' UNION DISTINCT ') . $q; } if ($productDiscriminator == 'productlinesalesorder' or $productDiscriminator == 'productlineall') { $q = "select distinct {$qfields} from vtiger_salesorder \n\t\t\t\t\tinner join vtiger_crmentity as crms on crms.crmid=vtiger_salesorder.salesorderid\n\t\t\t\t\tleft join vtiger_inventoryproductrel on vtiger_inventoryproductrel.id=vtiger_salesorder.salesorderid\n\t\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_inventoryproductrel.productid\n\t\t\t\t\tleft join {$pstable} on {$pstable}.{$psfield} = vtiger_inventoryproductrel.productid\n\t\t\t\t\twhere vtiger_inventoryproductrel.productid = {$pstable}.{$psfield} AND crms.deleted=0\n\t\t\t\t\tand {$relatedField} = {$crmid}"; $query .= ($query == '' ? '' : ' UNION DISTINCT ') . $q; } } } // q/so/i/po-product relation break; } // end switch $relatedModule // now we add order by if needed if ($query != '' and !empty($queryParameters['orderby'])) { $query .= ' order by ' . $queryParameters['orderby']; } // now we add limit and offset if needed if ($query != '' and !empty($queryParameters['limit'])) { $query .= ' limit ' . $queryParameters['limit']; if (!empty($queryParameters['offset'])) { $query .= ',' . $queryParameters['offset']; } } 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); $query = "SELECT {$fields_list}, foldername, filename,\n\t\t\t\t\tconcat(path,vtiger_attachments.attachmentsid,'_',filename) as storagename,\n\t\t\t\t\tconcat(account_no,' ',accountname) as account, concat(contact_no,' ',firstname,' ',lastname) as contact\n\t\t\t\tFROM vtiger_notes\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_notes.notesid\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_attachments.attachmentsid=vtiger_seattachmentsrel.attachmentsid\n\t\t\t\tLEFT JOIN vtiger_attachmentsfolder on vtiger_notes.folderid=vtiger_attachmentsfolder.folderid\n\t\t\t\tLEFT JOIN vtiger_senotesrel ON vtiger_senotesrel.notesid=vtiger_notes.notesid\n\t\t\t\tLEFT JOIN vtiger_account ON vtiger_account.accountid=vtiger_senotesrel.crmid\n\t\t\t\tLEFT JOIN vtiger_contactdetails ON vtiger_contactdetails.contactid=vtiger_senotesrel.crmid\n\t\t\t\tLEFT JOIN vtiger_users ON vtiger_crmentity.smownerid=vtiger_users.id\n\t\t\t\tLEFT 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; } $query .= ' group by vtiger_notes.notesid'; $log->debug("Exiting create_export_query method ..."); return $query; }
/** function to get report query for the given module * @ param $module : type String * this returns join query for the given module */ function getReportsQuery($module, $type = '') { $log = vglobal('log'); $current_user = vglobal('current_user'); $secondary_module = "'"; $secondary_module .= str_replace(":", "','", $this->secondarymodule); $secondary_module .= "'"; if ($module == "Leads") { $query = "from vtiger_leaddetails\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_leaddetails.leadid"; if ($this->queryPlanner->requireTable('vtiger_leadsubdetails')) { $query .= "\tinner join vtiger_leadsubdetails on vtiger_leadsubdetails.leadsubscriptionid=vtiger_leaddetails.leadid"; } if ($this->queryPlanner->requireTable('vtiger_leadaddress')) { $query .= "\tinner join vtiger_leadaddress on vtiger_leadaddress.leadaddressid=vtiger_leaddetails.leadid"; } if ($this->queryPlanner->requireTable('vtiger_leadscf')) { $query .= " inner join vtiger_leadscf on vtiger_leaddetails.leadid = vtiger_leadscf.leadid"; } if ($this->queryPlanner->requireTable('vtiger_groupsLeads')) { $query .= "\tleft join vtiger_groups as vtiger_groupsLeads on vtiger_groupsLeads.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable('vtiger_usersLeads')) { $query .= " left join vtiger_users as vtiger_usersLeads on vtiger_usersLeads.id = vtiger_crmentity.smownerid"; } $query .= " 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"; if ($this->queryPlanner->requireTable('vtiger_lastModifiedByLeads')) { $query .= " left join vtiger_users as vtiger_lastModifiedByLeads on vtiger_lastModifiedByLeads.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyLeads')) { $query .= " left join vtiger_users as vtiger_createdbyLeads on vtiger_createdbyLeads.id = vtiger_crmentity.smcreatorid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0 and vtiger_leaddetails.converted=0"; } else { if ($module == "Accounts") { $query = "from vtiger_account\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_account.accountid"; if ($this->queryPlanner->requireTable('vtiger_accountaddress')) { $query .= " inner join vtiger_accountaddress on vtiger_account.accountid=vtiger_accountaddress.accountaddressid"; } if ($this->queryPlanner->requireTable('vtiger_accountscf')) { $query .= " inner join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid"; } if ($this->queryPlanner->requireTable('vtiger_groupsAccounts')) { $query .= " left join vtiger_groups as vtiger_groupsAccounts on vtiger_groupsAccounts.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable('vtiger_accountAccounts')) { $query .= "\tleft join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid"; } if ($this->queryPlanner->requireTable('vtiger_usersAccounts')) { $query .= " left join vtiger_users as vtiger_usersAccounts on vtiger_usersAccounts.id = vtiger_crmentity.smownerid"; } $query .= " 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"; if ($this->queryPlanner->requireTable('vtiger_lastModifiedByAccounts')) { $query .= " left join vtiger_users as vtiger_lastModifiedByAccounts on vtiger_lastModifiedByAccounts.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyAccounts')) { $query .= " left join vtiger_users as vtiger_createdbyAccounts on vtiger_createdbyAccounts.id = vtiger_crmentity.smcreatorid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0 "; } else { if ($module == "Contacts") { $query = "from vtiger_contactdetails\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid = vtiger_contactdetails.contactid"; if ($this->queryPlanner->requireTable('vtiger_contactaddress')) { $query .= "\tinner join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid"; } if ($this->queryPlanner->requireTable('vtiger_customerdetails')) { $query .= "\tinner join vtiger_customerdetails on vtiger_customerdetails.customerid = vtiger_contactdetails.contactid"; } if ($this->queryPlanner->requireTable('vtiger_contactsubdetails')) { $query .= "\tinner join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid"; } if ($this->queryPlanner->requireTable('vtiger_contactscf')) { $query .= "\tinner join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid"; } if ($this->queryPlanner->requireTable('vtiger_groupsContacts')) { $query .= " left join vtiger_groups vtiger_groupsContacts on vtiger_groupsContacts.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable('vtiger_contactdetailsContacts')) { $query .= "\tleft join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto"; } if ($this->queryPlanner->requireTable('vtiger_accountContacts')) { $query .= "\tleft join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.parentid"; } if ($this->queryPlanner->requireTable('vtiger_usersContacts')) { $query .= " left join vtiger_users as vtiger_usersContacts on vtiger_usersContacts.id = vtiger_crmentity.smownerid"; } $query .= " left 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"; foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } if ($this->queryPlanner->requireTable('vtiger_lastModifiedByContacts')) { $query .= " left join vtiger_users as vtiger_lastModifiedByContacts on vtiger_lastModifiedByContacts.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyContacts')) { $query .= " left join vtiger_users as vtiger_createdbyContacts on vtiger_createdbyContacts.id = vtiger_crmentity.smcreatorid"; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0"; } else { if ($module == "Products") { $query .= " from vtiger_products"; $query .= " inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_products.productid"; if ($this->queryPlanner->requireTable("vtiger_productcf")) { $query .= " left join vtiger_productcf on vtiger_products.productid = vtiger_productcf.productid"; } if ($this->queryPlanner->requireTable("vtiger_lastModifiedByProducts")) { $query .= " left join vtiger_users as vtiger_lastModifiedByProducts on vtiger_lastModifiedByProducts.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyProducts')) { $query .= " left join vtiger_users as vtiger_createdbyProducts on vtiger_createdbyProducts.id = vtiger_crmentity.smcreatorid"; } if ($this->queryPlanner->requireTable("vtiger_usersProducts")) { $query .= " left join vtiger_users as vtiger_usersProducts on vtiger_usersProducts.id = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable("vtiger_groupsProducts")) { $query .= " left join vtiger_groups as vtiger_groupsProducts on vtiger_groupsProducts.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable("vtiger_vendorRelProducts")) { $query .= " left join vtiger_vendor as vtiger_vendorRelProducts on vtiger_vendorRelProducts.vendorid = vtiger_products.vendor_id"; } if ($this->queryPlanner->requireTable("innerProduct")) { $query .= " LEFT JOIN (\n\t\t\t\t\t\tSELECT vtiger_products.productid,\n\t\t\t\t\t\t\t\t(CASE WHEN (vtiger_products.currency_id = 1 ) THEN vtiger_products.unit_price\n\t\t\t\t\t\t\t\t\tELSE (vtiger_products.unit_price / vtiger_currency_info.conversion_rate) END\n\t\t\t\t\t\t\t\t) AS actual_unit_price\n\t\t\t\t\t\tFROM vtiger_products\n\t\t\t\t\t\tLEFT JOIN vtiger_currency_info ON vtiger_products.currency_id = vtiger_currency_info.id\n\t\t\t\t\t\tLEFT JOIN vtiger_productcurrencyrel ON vtiger_products.productid = vtiger_productcurrencyrel.productid\n\t\t\t\t\t\tAND vtiger_productcurrencyrel.currencyid = " . $current_user->currency_id . "\n\t\t\t\t) AS innerProduct ON innerProduct.productid = vtiger_products.productid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . "\n\t\t\t\twhere vtiger_crmentity.deleted=0"; } else { if ($module == "HelpDesk") { $matrix = $this->queryPlanner->newDependencyMatrix(); $matrix->setDependency('vtiger_crmentityRelHelpDesk', array('vtiger_accountRelHelpDesk', 'vtiger_contactdetailsRelHelpDesk')); $query = "from vtiger_troubletickets inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_troubletickets.ticketid"; if ($this->queryPlanner->requireTable('vtiger_ticketcf')) { $query .= " inner join vtiger_ticketcf on vtiger_ticketcf.ticketid = vtiger_troubletickets.ticketid"; } if ($this->queryPlanner->requireTable('vtiger_crmentityRelHelpDesk', $matrix)) { $query .= " left join vtiger_crmentity as vtiger_crmentityRelHelpDesk on vtiger_crmentityRelHelpDesk.crmid = vtiger_troubletickets.parent_id"; } if ($this->queryPlanner->requireTable('vtiger_accountRelHelpDesk')) { $query .= " left join vtiger_account as vtiger_accountRelHelpDesk on vtiger_accountRelHelpDesk.accountid=vtiger_crmentityRelHelpDesk.crmid"; } if ($this->queryPlanner->requireTable('vtiger_contactdetailsRelHelpDesk')) { $query .= " left join vtiger_contactdetails as vtiger_contactdetailsRelHelpDesk on vtiger_contactdetailsRelHelpDesk.contactid= vtiger_troubletickets.contact_id"; } if ($this->queryPlanner->requireTable('vtiger_productsRel')) { $query .= " left join vtiger_products as vtiger_productsRel on vtiger_productsRel.productid = vtiger_troubletickets.product_id"; } if ($this->queryPlanner->requireTable('vtiger_projectRelHelpDesk')) { $query .= " left join vtiger_project as vtiger_projectRelHelpDesk on vtiger_projectRelHelpDesk.projectid=vtiger_crmentityRelHelpDesk.crmid"; } if ($this->queryPlanner->requireTable('vtiger_groupsHelpDesk')) { $query .= " left join vtiger_groups as vtiger_groupsHelpDesk on vtiger_groupsHelpDesk.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable('vtiger_usersHelpDesk')) { $query .= " left join vtiger_users as vtiger_usersHelpDesk on vtiger_crmentity.smownerid=vtiger_usersHelpDesk.id"; } // TODO optimize inclusion of these tables $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " left join vtiger_users on vtiger_crmentity.smownerid=vtiger_users.id"; if ($this->queryPlanner->requireTable('vtiger_lastModifiedByHelpDesk')) { $query .= " left join vtiger_users as vtiger_lastModifiedByHelpDesk on vtiger_lastModifiedByHelpDesk.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyHelpDesk')) { $query .= " left join vtiger_users as vtiger_createdbyHelpDesk on vtiger_createdbyHelpDesk.id = vtiger_crmentity.smcreatorid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0 "; } else { if ($module == "Calendar") { $matrix = $this->queryPlanner->newDependencyMatrix(); $query = "from vtiger_activity\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid"; if ($this->queryPlanner->requireTable('vtiger_activitycf')) { $query .= " left join vtiger_activitycf on vtiger_activitycf.activityid = vtiger_crmentity.crmid"; } if ($this->queryPlanner->requireTable('vtiger_contactdetailsCalendar')) { $query .= " left join vtiger_contactdetails as vtiger_contactdetailsCalendar on vtiger_contactdetailsCalendar.contactid= vtiger_activity.link"; } if ($this->queryPlanner->requireTable('vtiger_groupsCalendar')) { $query .= " left join vtiger_groups as vtiger_groupsCalendar on vtiger_groupsCalendar.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable('vtiger_usersCalendar')) { $query .= " left join vtiger_users as vtiger_usersCalendar on vtiger_usersCalendar.id = vtiger_crmentity.smownerid"; } // TODO optimize inclusion of these tables $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; if ($this->queryPlanner->requireTable('vtiger_activity_reminder')) { $query .= " left join vtiger_activity_reminder on vtiger_activity_reminder.activity_id = vtiger_activity.activityid"; } if ($this->queryPlanner->requireTable('vtiger_recurringevents')) { $query .= " left join vtiger_recurringevents on vtiger_recurringevents.activityid = vtiger_activity.activityid"; } if ($this->queryPlanner->requireTable('vtiger_accountRelCalendar')) { $query .= " left join vtiger_account as vtiger_accountRelCalendar on vtiger_accountRelCalendar.accountid=vtiger_activity.link"; } if ($this->queryPlanner->requireTable('vtiger_leaddetailsRelCalendar')) { $query .= " left join vtiger_leaddetails as vtiger_leaddetailsRelCalendar on vtiger_leaddetailsRelCalendar.leadid = vtiger_activity.link"; } if ($this->queryPlanner->requireTable('vtiger_troubleticketsRelCalendar')) { $query .= " left join vtiger_troubletickets as vtiger_troubleticketsRelCalendar on vtiger_troubleticketsRelCalendar.ticketid = vtiger_activity.process"; } if ($this->queryPlanner->requireTable('vtiger_campaignRelCalendar')) { $query .= " left join vtiger_campaign as vtiger_campaignRelCalendar on vtiger_campaignRelCalendar.campaignid = vtiger_activity.process"; } if ($this->queryPlanner->requireTable('vtiger_lastModifiedByCalendar')) { $query .= " left join vtiger_users as vtiger_lastModifiedByCalendar on vtiger_lastModifiedByCalendar.id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable('vtiger_createdbyCalendar')) { $query .= " left join vtiger_users as vtiger_createdbyCalendar on vtiger_createdbyCalendar.id = vtiger_crmentity.smcreatorid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " WHERE vtiger_crmentity.deleted=0 and (vtiger_activity.activitytype != 'Emails')"; } else { if ($module == "Campaigns") { $query = "from vtiger_campaign\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_campaign.campaignid"; if ($this->queryPlanner->requireTable("vtiger_campaignscf")) { $query .= " inner join vtiger_campaignscf as vtiger_campaignscf on vtiger_campaignscf.campaignid=vtiger_campaign.campaignid"; } if ($this->queryPlanner->requireTable("vtiger_productsCampaigns")) { $query .= " left join vtiger_products as vtiger_productsCampaigns on vtiger_productsCampaigns.productid = vtiger_campaign.product_id"; } if ($this->queryPlanner->requireTable("vtiger_groupsCampaigns")) { $query .= " left join vtiger_groups as vtiger_groupsCampaigns on vtiger_groupsCampaigns.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable("vtiger_usersCampaigns")) { $query .= " left join vtiger_users as vtiger_usersCampaigns on vtiger_usersCampaigns.id = vtiger_crmentity.smownerid"; } // TODO optimize inclusion of these tables $query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid"; if ($this->queryPlanner->requireTable("vtiger_lastModifiedBy{$module}")) { $query .= " left join vtiger_users as vtiger_lastModifiedBy" . $module . " on vtiger_lastModifiedBy" . $module . ".id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable("vtiger_createdby{$module}")) { $query .= " left join vtiger_users as vtiger_createdby{$module} on vtiger_createdby{$module}.id = vtiger_crmentity.smcreatorid"; } foreach ($this->queryPlanner->getCustomTables() as $customTable) { $query .= " left join " . $customTable['refTable'] . " as " . $customTable['reference'] . " on " . $customTable['reference'] . "." . $customTable['refIndex'] . " = " . $customTable['table'] . "." . $customTable['field']; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " where vtiger_crmentity.deleted=0"; } else { if ($module == "Emails") { $query = "from vtiger_activity\n\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid AND vtiger_activity.activitytype = 'Emails'"; if ($this->queryPlanner->requireTable("vtiger_email_track")) { $query .= " LEFT JOIN vtiger_email_track ON vtiger_email_track.mailid = vtiger_activity.activityid"; } if ($this->queryPlanner->requireTable("vtiger_groupsEmails")) { $query .= " LEFT JOIN vtiger_groups AS vtiger_groupsEmails ON vtiger_groupsEmails.groupid = vtiger_crmentity.smownerid"; } if ($this->queryPlanner->requireTable("vtiger_usersEmails")) { $query .= " LEFT JOIN vtiger_users AS vtiger_usersEmails ON vtiger_usersEmails.id = vtiger_crmentity.smownerid"; } // TODO optimize inclusion of these tables $query .= " LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= " LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid"; if ($this->queryPlanner->requireTable("vtiger_lastModifiedBy{$module}")) { $query .= " LEFT JOIN vtiger_users AS vtiger_lastModifiedBy" . $module . " ON vtiger_lastModifiedBy" . $module . ".id = vtiger_crmentity.modifiedby"; } if ($this->queryPlanner->requireTable("vtiger_createdby{$module}")) { $query .= " left join vtiger_users as vtiger_createdby{$module} on vtiger_createdby{$module}.id = vtiger_crmentity.smcreatorid"; } $query .= " " . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " WHERE vtiger_crmentity.deleted = 0"; } else { if ($module != '') { $focus = CRMEntity::getInstance($module); $query = $focus->generateReportsQuery($module, $this->queryPlanner) . $this->getRelatedModulesQuery($module, $this->secondarymodule) . getNonAdminAccessControlQuery($this->primarymodule, $current_user) . " WHERE vtiger_crmentity.deleted=0"; } } } } } } } } } $log->info("ReportRun :: Successfully returned getReportsQuery" . $module); return $query; }
$smarty->assign("TAG_SEARCH", $search_tag); $smarty->assign("SEARCH_MODULE", vtlib_purify($_REQUEST['search_module'])); $smarty->assign("SINGLE_MOD", $module); $smarty->assign("SEARCH_STRING", htmlentities($search_val, ENT_QUOTES, $default_charset)); if ($module == 'Calendar') { $listquery = 'SELECT vtiger_activity.activityid as act_id,vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_crmentity.setype, vtiger_activity.* FROM vtiger_activity LEFT JOIN vtiger_activitycf ON vtiger_activitycf.activityid = vtiger_activity.activityid LEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid LEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid LEFT OUTER JOIN vtiger_activity_reminder ON vtiger_activity_reminder.activity_id = vtiger_activity.activityid LEFT 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'; $listquery .= getNonAdminAccessControlQuery($module, $current_user); $listquery .= ' where vtiger_crmentity.deleted=0 '; } else { $listquery = getListQuery($module); } $oCustomView = ''; $oCustomView = new CustomView($module); //Instead of getting current customview id, use cvid of All so that all entities will be found //$viewid = $oCustomView->getViewId($module); $cv_res = $adb->pquery("select cvid from vtiger_customview where viewname='All' and entitytype=?", array($module)); $viewid = $adb->query_result($cv_res, 0, 'cvid'); $customviewcombo_html = $oCustomView->getCustomViewCombo($viewid); $listquery = $oCustomView->getModifiedCvListQuery($viewid, $listquery, $module); if ($module == 'Calendar') { if (!isset($oCustomView->list_fields['Close'])) { $oCustomView->list_fields['Close'] = array('activity' => 'status');
function getDashboardQuery($query, $module) { global $current_user; $secQuery = getNonAdminAccessControlQuery($module, $current_user); if (strlen($secQuery) > 1) { $query = appendFromClauseToQuery($query, $secQuery); } 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; }
/** To get the events of the specified user and shared events * @param $userid -- The user Id:: Type integer * @param $from_datetime -- The start date Obj :: Type Array * @param $to_datetime -- The end date Obj :: Type Array * @param $view -- The calendar view :: Type String * @returns $list :: Type Array */ function readAppointment($userid, &$from_datetime, &$to_datetime, $view) { global $current_user, $adb; require 'user_privileges/user_privileges_' . $current_user->id . '.php'; require 'user_privileges/sharing_privileges_' . $current_user->id . '.php'; $and = "AND ((vtiger_activity.date_start between ? AND ?)\n\t\t\tOR (vtiger_activity.date_start < ? AND vtiger_activity.due_date > ?)\n\t\t\tOR (vtiger_activity.due_date between ? AND ?))"; $q = "select vtiger_activity.*, vtiger_crmentity.*, case when (vtiger_users.user_name not like '') then vtiger_users.user_name else vtiger_groups.groupname end as user_name FROM vtiger_activity inner join vtiger_crmentity on vtiger_activity.activityid = vtiger_crmentity.crmid left join vtiger_recurringevents on vtiger_activity.activityid=vtiger_recurringevents.activityid left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid WHERE vtiger_crmentity.deleted = 0 and vtiger_activity.activitytype not in ('Emails','Task') {$and} "; // User Select Customization: Changes should made also in (calendayLaout getEventList) and one more BELOW $query_filter_prefix = calendarview_getSelectedUserFilterQuerySuffix(); $q .= $query_filter_prefix; // END $params = array($from_datetime->get_formatted_date(), $to_datetime->get_formatted_date(), $from_datetime->get_formatted_date(), $from_datetime->get_formatted_date(), $from_datetime->get_formatted_date(), $to_datetime->get_formatted_date()); if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[16] == 3) { //Added for User Based Custom View for Calendar $sec_parameter = getCalendarViewSecurityParameter(); $q .= $sec_parameter; } $q .= " AND vtiger_recurringevents.activityid is NULL "; $q .= " group by vtiger_activity.activityid ORDER by vtiger_activity.date_start,vtiger_activity.time_start"; $r = $adb->pquery($q, $params); $n = $adb->getRowCount($r); $a = 0; $list = array(); while ($a < $n) { $result = $adb->fetchByAssoc($r); $start_timestamp = strtotime($result["date_start"]); $end_timestamp = strtotime($result["due_date"]); if ($from_datetime->ts <= $start_timestamp) { $from = $start_timestamp; } else { $from = $from_datetime->ts; } if ($to_datetime->ts <= $end_timestamp) { $to = $to_datetime->ts; } else { $to = $end_timestamp; } for ($j = $from; $j <= $to; $j = $j + 60 * 60 * 24) { $obj =& new Appointment(); $temp_start = date("Y-m-d", $j); $result["date_start"] = $temp_start; list($obj->temphour, $obj->tempmin) = explode(":", $result["time_start"]); if ($start_timestamp != $end_timestamp && $view == 'day') { if ($j == $start_timestamp) { $result["duration_hours"] = 24 - $obj->temphour; } elseif ($j > $start_timestamp && $j < $end_timestamp) { list($obj->temphour, $obj->tempmin) = $current_user->start_hour != '' ? explode(":", $current_user->start_hour) : explode(":", "08:00"); $result["duration_hours"] = 24 - $obj->temphour; } elseif ($j == $end_timestamp) { list($obj->temphour, $obj->tempmin) = $current_user->start_hour != '' ? explode(":", $current_user->start_hour) : explode(":", "08:00"); list($ehr, $emin) = explode(":", $result["time_end"]); $result["duration_hours"] = $ehr - $obj->temphour; } } $obj->readResult($result, $view); $list[] = $obj; unset($obj); } $a++; } //Get Recurring events $q = "SELECT vtiger_activity.*, vtiger_crmentity.*, case when (vtiger_users.user_name not like '') then vtiger_users.user_name else vtiger_groups.groupname end as user_name , vtiger_recurringevents.recurringid, vtiger_recurringevents.recurringdate as date_start ,vtiger_recurringevents.recurringtype,vtiger_groups.groupname from vtiger_activity inner join vtiger_crmentity on vtiger_activity.activityid = vtiger_crmentity.crmid inner join vtiger_recurringevents on vtiger_activity.activityid=vtiger_recurringevents.activityid left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid"; $q .= getNonAdminAccessControlQuery('Calendar', $current_user); $q .= " where vtiger_crmentity.deleted = 0 and vtiger_activity.activitytype not in ('Emails','Task') AND (recurringdate between ? and ?) "; // User Select Customization $q .= $query_filter_prefix; // END $params = array($from_datetime->get_formatted_date(), $to_datetime->get_formatted_date()); $q .= " ORDER by vtiger_recurringevents.recurringid"; $r = $adb->pquery($q, $params); $n = $adb->getRowCount($r); $a = 0; while ($a < $n) { $obj =& new Appointment(); $result = $adb->fetchByAssoc($r); list($obj->temphour, $obj->tempmin) = explode(":", $result["time_start"]); $obj->readResult($result, $view); $a++; $list[] = $obj; unset($obj); } usort($list, 'compare'); return $list; }
/** * function to get pending activities for today * @param integer $maxval - the maximum number of records to display * @param integer $calCnt - returns the count query if this is set * return array $values - activities record in array format */ function homepage_getPendingActivities($maxval, $calCnt) { require_once "data/Tracker.php"; require_once "include/utils/utils.php"; require_once 'include/utils/CommonUtils.php'; global $adb; global $current_user; $today = date("Y-m-d", time()); $pending_condition = " AND (due_date = '{$today}' OR vtiger_recurringevents.recurringdate = '{$today}')"; $list_query = "select vtiger_crmentity.crmid,vtiger_crmentity.smownerid,vtiger_crmentity." . "setype, vtiger_recurringevents.recurringdate, vtiger_activity.* from vtiger_activity " . "inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid LEFT " . "JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid left outer join " . "vtiger_recurringevents on vtiger_recurringevents.activityid=vtiger_activity.activityid" . ($list_query .= getNonAdminAccessControlQuery('Calendar', $current_user)); $list_query .= "WHERE vtiger_crmentity.deleted=0 and (vtiger_activity.activitytype not in " . "('Emails')) AND (vtiger_activity.status is NULL OR vtiger_activity.status not in " . "('Completed','Deferred')) and (vtiger_activity.eventstatus is NULL OR vtiger_activity." . "eventstatus not in ('Held','Not Held')) " . $pending_condition; $list_query .= " GROUP BY vtiger_activity.activityid"; $list_query .= " ORDER BY date_start,time_start ASC"; $list_query .= " limit {$maxval}"; $res = $adb->query($list_query); $noofrecords = $adb->num_rows($res); if ($calCnt == 'calculateCnt') { return $noofrecords; } $open_activity_list = array(); $noofrows = $adb->num_rows($res); if (count($res) > 0) { for ($i = 0; $i < $noofrows; $i++) { $open_activity_list[] = array('name' => $adb->query_result($res, $i, 'subject'), 'id' => $adb->query_result($res, $i, 'activityid'), 'type' => $adb->query_result($res, $i, 'activitytype'), 'module' => $adb->query_result($res, $i, 'setype'), 'date_start' => getDisplayDate($adb->query_result($res, $i, 'date_start')), 'due_date' => getDisplayDate($adb->query_result($res, $i, 'due_date')), 'recurringdate' => getDisplayDate($adb->query_result($res, $i, 'recurringdate')), 'priority' => $adb->query_result($res, $i, 'priority')); } } $values = getActivityEntries($open_activity_list); $values['ModuleName'] = 'Calendar'; $values['search_qry'] = "&action=ListView&from_homepage=pending_activities"; return $values; }
/** To get the events of the specified user and shared events * @param $userid -- The user Id:: Type integer * @param $from_datetime -- The start date Obj :: Type Array * @param $to_datetime -- The end date Obj :: Type Array * @param $view -- The calendar view :: Type String * @returns $list :: Type Array */ function readAppointment($userid, &$from_datetime, &$to_datetime, $view) { global $current_user, $adb; require 'user_privileges/user_privileges_' . $current_user->id . '.php'; require 'user_privileges/sharing_privileges_' . $current_user->id . '.php'; $and = "AND (\n\t\t\t\t\t(\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\t(CAST(CONCAT(date_start,' ',time_start) AS DATETIME) >= ? AND CAST(CONCAT(date_start,' ',time_start) AS DATETIME) <= ?)\n\t\t\t\t\t\t\tOR\t(CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) <= ? )\n\t\t\t\t\t\t\tOR\t(CAST(CONCAT(date_start,' ',time_start) AS DATETIME) <= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ?)\n\t\t\t\t\t\t)\n\t\t\t\t\t\tAND vtiger_recurringevents.activityid is NULL\n\t\t\t\t\t)\n\t\t\t\tOR (\n\t\t\t\t\t\t(CAST(CONCAT(vtiger_recurringevents.recurringdate,' ',time_start) AS DATETIME) >= ?\n\t\t\t\t\t\t\tAND CAST(CONCAT(vtiger_recurringevents.recurringdate,' ',time_start) AS DATETIME) <= ?)\n\t\t\t\t\t\tOR\t(CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) <= ?)\n\t\t\t\t\t\tOR\t(CAST(CONCAT(vtiger_recurringevents.recurringdate,' ',time_start) AS DATETIME) <= ?\n\t\t\t\t\t\t\tAND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ?)\n\t\t\t\t\t)\n\t\t\t\t)"; $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $q = "select vtiger_activity.*, vtiger_crmentity.*,\n\t\t\t\t\tcase when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name\n\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tinner join vtiger_crmentity on vtiger_activity.activityid = vtiger_crmentity.crmid\n\t\t\t\t\t\tleft join vtiger_recurringevents on vtiger_activity.activityid=vtiger_recurringevents.activityid\n\t\t\t\t\t\tleft join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 and vtiger_activity.activitytype not in ('Emails','Task') {$and} "; // User Select Customization: Changes should made also in (calendayLaout getEventList) and one more BELOW $query_filter_prefix = calendarview_getSelectedUserFilterQuerySuffix(); $q .= $query_filter_prefix; // END $h = $from_datetime->z_hour; $m = $from_datetime->min; if (empty($m)) { $m = '00'; } $startDate = new DateTimeField($from_datetime->year . "-" . $from_datetime->z_month . "-" . $from_datetime->z_day . " {$h}:{$m}"); $h = '23'; $m = '59'; $endDate = new DateTimeField($to_datetime->year . "-" . $to_datetime->z_month . "-" . $to_datetime->z_day . " {$h}:{$m}"); $params = array($startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue()); if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[16] == 3) { //Added for User Based Custom View for Calendar $sec_parameter = getCalendarViewSecurityParameter(); $q .= $sec_parameter; } $q .= " AND vtiger_recurringevents.activityid is NULL "; $q .= " group by vtiger_activity.activityid ORDER by vtiger_activity.date_start,vtiger_activity.time_start"; $r = $adb->pquery($q, $params); $n = $adb->getRowCount($r); $a = 0; $list = array(); while ($a < $n) { $result = $adb->fetchByAssoc($r); $from = strtotime($result['date_start']); $to = strtotime($result['due_date'] . ' ' . $result["time_end"]); $windowTo = strtotime($endDate->getDBInsertDateTimeValue()); for ($j = $from; $j <= $to; $j = $j + 60 * 60 * 24) { $obj =& new Appointment(); $temp_start = date("Y-m-d", $j); $endTime = strtotime($temp_start . ' ' . $result['time_start']); if ($endTime > $windowTo) { break; } $result["date_start"] = $temp_start; list($obj->temphour, $obj->tempmin) = explode(":", $result["time_start"]); if ($start_timestamp != $end_timestamp && $view == 'day') { if ($j == $start_timestamp) { $result["duration_hours"] = 24 - $obj->temphour; } elseif ($j > $start_timestamp && $j < $end_timestamp) { list($obj->temphour, $obj->tempmin) = $current_user->start_hour != '' ? explode(":", $current_user->start_hour) : explode(":", "08:00"); $result["duration_hours"] = 24 - $obj->temphour; } elseif ($j == $end_timestamp) { list($obj->temphour, $obj->tempmin) = $current_user->start_hour != '' ? explode(":", $current_user->start_hour) : explode(":", "08:00"); list($ehr, $emin) = explode(":", $result["time_end"]); $result["duration_hours"] = $ehr - $obj->temphour; } } $obj->readResult($result, $view); $list[] = $obj; unset($obj); } $a++; } //Get Recurring events $q = "SELECT vtiger_activity.*, vtiger_crmentity.*, case when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name , vtiger_recurringevents.recurringid, vtiger_recurringevents.recurringdate as date_start ,vtiger_recurringevents.recurringtype,vtiger_groups.groupname from vtiger_activity inner join vtiger_crmentity on vtiger_activity.activityid = vtiger_crmentity.crmid inner join vtiger_recurringevents on vtiger_activity.activityid=vtiger_recurringevents.activityid left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid"; $q .= getNonAdminAccessControlQuery('Calendar', $current_user); $q .= " where vtiger_crmentity.deleted = 0 and vtiger_activity.activitytype not in ('Emails','Task') AND (cast(concat(recurringdate, ' ', time_start) as datetime) between ? and ?) "; // User Select Customization $q .= $query_filter_prefix; // END $params = array($startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue()); $q .= " ORDER by vtiger_recurringevents.recurringid"; $r = $adb->pquery($q, $params); $n = $adb->getRowCount($r); $a = 0; while ($a < $n) { $obj =& new Appointment(); $result = $adb->fetchByAssoc($r); list($obj->temphour, $obj->tempmin) = explode(":", $result["time_start"]); $obj->readResult($result, $view); $a++; $list[] = $obj; unset($obj); } usort($list, 'compare'); return $list; }
/** * Function to get Pending/Upcoming activities * @param integer $mode - number to differentiate upcoming and pending activities * return array $values - activities record in array format */ function getPendingActivities($mode, $view = '') { global $log; $log->debug("Entering getPendingActivities() method ..."); require_once 'data/Tracker.php'; require_once 'include/utils/utils.php'; require_once 'user_privileges/default_module_view.php'; global $currentModule; global $singlepane_view; global $theme; global $focus; global $action; global $adb; global $app_strings; global $current_language; global $current_user; $current_module_strings = return_module_language($current_language, 'Calendar'); $theme_path = "themes/" . $theme . "/"; $image_path = $theme_path . "images/"; if ($_REQUEST['activity_view'] == '') { $activity_view = 'today'; } else { $activity_view = vtlib_purify($_REQUEST['activity_view']); } $today = date("Y-m-d", time()); if ($view == 'today') { $upcoming_condition = " AND (date_start = '{$today}' OR vtiger_recurringevents.recurringdate = '{$today}')"; $pending_condition = " AND (due_date = '{$today}' OR vtiger_recurringevents.recurringdate = '{$today}')"; } else { if ($view == 'all') { $upcoming_condition = " AND (date_start >= '{$today}' OR vtiger_recurringevents.recurringdate >= '{$today}')"; $pending_condition = " AND (due_date <= '{$today}' OR vtiger_recurringevents.recurringdate <= '{$today}')"; } } if ($mode != 1) { $list_query = " select vtiger_crmentity.crmid,vtiger_crmentity.smownerid,vtiger_crmentity." . "setype, vtiger_recurringevents.recurringdate, vtiger_activity.activityid, " . "vtiger_activity.activitytype, vtiger_activity.date_start, vtiger_activity.due_date," . "from vtiger_activity inner join vtiger_crmentity on vtiger_crmentity.crmid=" . "vtiger_activity.activityid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = " . "vtiger_crmentity.smownerid left outer join vtiger_recurringevents on " . "vtiger_recurringevents.activityid=vtiger_activity.activityid"; $list_query .= getNonAdminAccessControlQuery('Calendar', $current_user); $list_query .= " WHERE vtiger_crmentity.deleted=0 and vtiger_activity.activitytype not in " . "('Emails') AND ( vtiger_activity.status is NULL OR vtiger_activity.status not in" . "('Completed','Deferred')) and ( vtiger_activity.eventstatus is NULL OR vtiger_activity." . "eventstatus not in ('Held','Not Held') )" . $upcoming_condition; } else { $list_query = "select vtiger_crmentity.crmid,vtiger_crmentity.smownerid,vtiger_crmentity" . "setype, vtiger_recurringevents.recurringdate, vtiger_activity.activityid, vtiger_activity" . ".activitytype, vtiger_activity.date_start, vtiger_activity.due_date, from vtiger_activity" . "inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid " . "LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid " . "left outer join vtiger_recurringevents on vtiger_recurringevents.activityid=" . "vtiger_activity.activityid"; $list_query .= getNonAdminAccessControlQuery('Calendar', $current_user); $list_query .= "WHERE vtiger_crmentity.deleted=0 and (vtiger_activity." . "activitytype not in ('Emails')) AND (vtiger_activity.status is NULL OR vtiger_activity." . "status not in ('Completed','Deferred')) and (vtiger_activity.eventstatus is NULL OR " . "vtiger_activity.eventstatus not in ('Held','Not Held')) " . $pending_condition; $list_query .= " GROUP BY vtiger_activity.activityid"; $list_query .= " ORDER BY date_start,time_start ASC"; $res = $adb->query($list_query); $noofrecords = $adb->num_rows($res); $open_activity_list = array(); $noofrows = $adb->num_rows($res); if (count($res) > 0) { for ($i = 0; $i < $noofrows; $i++) { $open_activity_list[] = array('name' => $adb->query_result($res, $i, 'subject'), 'id' => $adb->query_result($res, $i, 'activityid'), 'type' => $adb->query_result($res, $i, 'activitytype'), 'module' => $adb->query_result($res, $i, 'setype'), 'date_start' => getDisplayDate($adb->query_result($res, $i, 'date_start')), 'due_date' => getDisplayDate($adb->query_result($res, $i, 'due_date')), 'recurringdate' => getDisplayDate($adb->query_result($res, $i, 'recurringdate')), 'priority' => $adb->query_result($res, $i, 'priority')); } } $title = array(); $title[] = $view; $title[] = 'myUpcoPendAct.gif'; $title[] = 'home_myact'; $title[] = 'showActivityView'; $title[] = 'MyUpcumingFrm'; $title[] = 'activity_view'; $header = array(); $header[] = $current_module_strings['LBL_LIST_SUBJECT']; $header[] = 'Type'; $return_url = "&return_module={$currentModule}&return_action=DetailView&return_id=" . (is_object($focus) ? $focus->id : ""); $oddRow = true; $entries = array(); foreach ($open_activity_list as $event) { $recur_date = preg_replace('/--/', '', $event['recurringdate']); if ($recur_date != "") { $event['date_start'] = $event['recurringdate']; } $font_color_high = "color:#00DD00;"; $font_color_medium = "color:#DD00DD;"; switch ($event['priority']) { case 'High': $font_color = $font_color_high; break; case 'Medium': $font_color = $font_color_medium; break; default: $font_color = ''; } if ($event['type'] != 'Task' && $event['type'] != 'Emails' && $event['type'] != '') { $activity_type = 'Events'; } else { $activity_type = 'Task'; } } $entries[$event['id']] = array('0' => '<a href="index.php?action=DetailView&module=' . $event["module"] . '&activity_mode=' . $activity_type . '&record=' . $event["id"] . '' . $return_url . '" style="' . $font_color . ';">' . $event["name"] . '</a>', 'IMAGE' => '<IMG src="' . $image_path . $event["type"] . 's.gif">'); } $values = array('noofactivities' => $noofrecords, 'Title' => $title, 'Header' => $header, 'Entries' => $entries); $log->debug("Exiting getPendingActivities method ..."); return $values; }
/** * Function to get todos list scheduled between specified dates * @param array $calendar - collection of objects and strings * @param string $start_date - date string * @param string $end_date - date string * @param string $info - string 'listcnt' or empty string. if 'listcnt' means it returns no. of todos and no. of pending todos in array format else it returns todos list in array format * return array $Entries - todolists in array format */ function getTodoList(&$calendar, $start_date, $end_date, $info = '') { global $log, $app_strings, $theme; $Entries = array(); $category = getParentTab(); global $adb, $current_user, $mod_strings, $cal_log, $list_max_entries_per_page; $cal_log->debug("Entering getTodoList() method..."); require 'user_privileges/user_privileges_' . $current_user->id . '.php'; require 'user_privileges/sharing_privileges_' . $current_user->id . '.php'; $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $query = "SELECT vtiger_groups.groupname, {$userNameSql} as user_name, vtiger_crmentity.crmid, vtiger_cntactivityrel.contactid,\n\t\t\t\tvtiger_activity.* FROM vtiger_activity\n INNER JOIN vtiger_crmentity\n\t\t\t\t\tON vtiger_crmentity.crmid = vtiger_activity.activityid\n LEFT JOIN vtiger_cntactivityrel\n\t\t\t\t\tON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\tLEFT JOIN vtiger_users\n\t\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery('Calendar', $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.activitytype = 'Task'" . " AND ((CAST(CONCAT(date_start,' ',time_start) AS DATETIME) >= ? AND CAST(CONCAT(date_start,' ',time_start) AS DATETIME) <= ?)\n\t\t\t\t\t\t\tOR\t(CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) <= ? )\n\t\t\t\t\t\t\tOR\t(CAST(CONCAT(date_start,' ',time_start) AS DATETIME) <= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ?)\n\t\t\t\t\t\t)"; $list_query = $query . " AND vtiger_crmentity.smownerid = " . $current_user->id; $startDate = new DateTimeField($start_date . ' 00:00'); $endDate = new DateTimeField($end_date . ' 23:59'); $params = $info_params = array($startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue()); if ($info != '') { //added to fix #4816 $groupids = explode(",", fetchUserGroupids($current_user->id)); if (count($groupids) > 0 && !is_admin($current_user)) { $com_q = " AND (vtiger_crmentity.smownerid = ?\n\t\t\t\t\tOR vtiger_groups.groupid in (" . generateQuestionMarks($groupids) . "))"; array_push($info_params, $current_user->id); array_push($info_params, $groupids); } elseif (!is_admin($current_user)) { $com_q = " AND vtiger_crmentity.smownerid = ?"; array_push($info_params, $current_user->id); } //end $pending_query = $query . " AND (vtiger_activity.status != 'Completed')" . $com_q; $total_q = $query . "" . $com_q; $total_res = $adb->pquery($total_q, $info_params); $total = $adb->num_rows($total_res); $res = $adb->pquery($pending_query, $info_params); $pending_rows = $adb->num_rows($res); $cal_log->debug("Exiting getTodoList() method..."); return array('totaltodo' => $total, 'pendingtodo' => $pending_rows); } $group_cond = ''; $group_cond .= " ORDER BY vtiger_activity.date_start,vtiger_activity.time_start ASC"; if (isset($_REQUEST['start']) && $_REQUEST['start'] != '') { $start = vtlib_purify($_REQUEST['start']); } else { $start = 1; } //T6477 changes if (PerformancePrefs::getBoolean('LISTVIEW_COMPUTE_PAGE_COUNT', false) === true) { $count_res = $adb->pquery(mkCountQuery($query), $params); $total_rec_count = $adb->query_result($count_res, 0, 'count'); } else { $total_rec_count = null; } $navigation_array = VT_getSimpleNavigationValues($start, $list_max_entries_per_page, $total_rec_count); $start_rec = ($start - 1) * $list_max_entries_per_page; $end_rec = $navigation_array['end_val']; $list_query = $adb->convert2Sql($query, $params); $_SESSION['Calendar_listquery'] = $list_query; if ($start_rec < 0) { $start_rec = 0; } //ends $query .= $group_cond . " limit {$start_rec},{$list_max_entries_per_page}"; $result = $adb->pquery($query, $params); $rows = $adb->num_rows($result); $c = 0; if ($start > 1) { $c = ($start - 1) * $list_max_entries_per_page; } for ($i = 0; $i < $rows; $i++) { $element = array(); $contact_name = ''; $element['no'] = $c + 1; $more_link = ""; $start_time = $adb->query_result($result, $i, "time_start"); $date_start = $adb->query_result($result, $i, "date_start"); $due_date = $adb->query_result($result, $i, "due_date"); $date = new DateTimeField($date_start . ' ' . $start_time); $endDate = new DateTimeField($due_date); if (!empty($start_time)) { $start_time = $date->getDisplayTime(); } $format = $calendar['calendar']->hour_format; $value = getaddEventPopupTime($start_time, $start_time, $format); $element['starttime'] = $value['starthour'] . ':' . $value['startmin'] . '' . $value['startfmt']; $element['startdate'] = $date->getDisplayDate(); $element['duedate'] = $endDate->getDisplayDate(); $id = $adb->query_result($result, $i, "activityid"); $subject = $adb->query_result($result, $i, "subject"); $more_link = "<a href='index.php?action=DetailView&module=Calendar&record=" . $id . "&activity_mode=Task&viewtype=calendar&parenttab=" . $category . "' class='webMnu'>" . $subject . "</a>"; $element['tododetail'] = $more_link; if (getFieldVisibilityPermission('Calendar', $current_user->id, 'taskstatus') == '0') { $taskstatus = $adb->query_result($result, $i, "status"); if (!$is_admin && $taskstatus != '') { $roleid = $current_user->roleid; $roleids = array(); $subrole = getRoleSubordinates($roleid); if (count($subrole) > 0) { $roleids = $subrole; } array_push($roleids, $roleid); //here we are checking wheather the table contains the sortorder column .If sortorder is present in the main picklist table, then the role2picklist will be applicable for this table... $sql = "select * from vtiger_taskstatus where taskstatus=?"; $res = $adb->pquery($sql, array(decode_html($taskstatus))); $picklistvalueid = $adb->query_result($res, 0, 'picklist_valueid'); if ($picklistvalueid != null) { $pick_query = "select * from vtiger_role2picklist where picklistvalueid={$picklistvalueid} and roleid in (" . generateQuestionMarks($roleids) . ")"; $res_val = $adb->pquery($pick_query, array($roleids)); $num_val = $adb->num_rows($res_val); } if ($num_val > 0) { $element['status'] = getTranslatedString(decode_html($taskstatus)); } else { $element['status'] = "<font color='red'>" . $app_strings['LBL_NOT_ACCESSIBLE'] . "</font>"; } } else { $element['status'] = getTranslatedString(decode_html($taskstatus)); } } if (isPermitted("Calendar", "EditView") == "yes" || isPermitted("Calendar", "Delete") == "yes") { $element['action'] = "<img onClick='getcalAction(this,\"taskcalAction\"," . $id . ",\"" . $calendar['view'] . "\",\"" . $calendar['calendar']->date_time->hour . "\",\"" . $calendar['calendar']->date_time->get_DB_formatted_date() . "\",\"todo\");' src='" . vtiger_imageurl('cal_event.jpg', $theme) . "' border='0'>"; } $assignedto = $adb->query_result($result, $i, "user_name"); if (!empty($assignedto)) { $element['assignedto'] = $assignedto; } else { $element['assignedto'] = $adb->query_result($result, $i, "groupname"); } $c++; $Entries[] = $element; } $ret_arr[0] = $Entries; $ret_arr[1] = $navigation_array; $cal_log->debug("Exiting getTodoList() method..."); return $ret_arr; }
/** 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 returns non admin access control check query * @param <String> $module * @return <String> */ public static function getNonAdminAccessControlQuery($module) { $currentUser = vglobal('current_user'); return getNonAdminAccessControlQuery($module, $currentUser); }
/** Function to export the ticket records in CSV Format * @param reference variable - where condition is passed when the query is executed * Returns Export Tickets 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("HelpDesk", "detail_view"); $fields_list = getFieldsListFromQuery($sql); //Ticket changes--5198 $fields_list = str_replace(",vtiger_ticketcomments.comments as 'Add Comment'", ' ', $fields_list); $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\n\t\t\tFROM " . $this->entity_table . "\n\t\t\tINNER JOIN vtiger_troubletickets ON vtiger_troubletickets.ticketid =vtiger_crmentity.crmid\n\t\t\tLEFT JOIN vtiger_crmentity vtiger_crmentityRelatedTo ON vtiger_crmentityRelatedTo.crmid = vtiger_troubletickets.parent_id\n\t\t\tLEFT JOIN vtiger_account ON vtiger_account.accountid = vtiger_troubletickets.parent_id\n\t\t\tLEFT JOIN vtiger_contactdetails ON vtiger_contactdetails.contactid = vtiger_troubletickets.parent_id\n\t\t\tLEFT JOIN vtiger_ticketcf ON vtiger_ticketcf.ticketid=vtiger_troubletickets.ticketid\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 and vtiger_users.status='Active'\n\t\t\tLEFT JOIN vtiger_seattachmentsrel ON vtiger_seattachmentsrel.crmid =vtiger_troubletickets.ticketid\n\t\t\tLEFT JOIN vtiger_attachments ON vtiger_attachments.attachmentsid=vtiger_seattachmentsrel.attachmentsid\n\t\t\tLEFT JOIN vtiger_products ON vtiger_products.productid=vtiger_troubletickets.product_id"; $query .= getNonAdminAccessControlQuery('HelpDesk', $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 Contacts assigned to a user with a valid email address. * @param varchar $username - User Name * @param varchar $emailaddress - Email Addr for each contact. * Used By vtigerCRM Outlook Plugin * Returns the Query */ function get_searchbyemailid($username, $emailaddress) { global $log, $current_user; require_once "modules/Users/Users.php"; $seed_user = new Users(); $user_id = $seed_user->retrieve_user_id($username); $current_user = $seed_user; $current_user->retrieve_entity_info($user_id, 'Users'); require 'user_privileges/user_privileges_' . $current_user->id . '.php'; require 'user_privileges/sharing_privileges_' . $current_user->id . '.php'; $log->debug("Entering get_searchbyemailid(" . $username . "," . $emailaddress . ") method ..."); //get users group ID's $gquery = 'SELECT groupid FROM vtiger_users2group WHERE userid=?'; $gresult = $adb->pquery($gquery, array($user_id)); for ($j = 0; $j < $adb->num_rows($gresult); $j++) { $groupidlist .= "," . $adb->query_result($gresult, $j, 'groupid'); } //crm-now changed query to search in groups too and make only owned contacts available $query = "select vtiger_contactdetails.lastname,vtiger_contactdetails.firstname,\n\t\t\t\tvtiger_contactdetails.contactid, vtiger_contactdetails.salutation,\n\t\t\t\tvtiger_contactdetails.email,vtiger_contactdetails.title,\n\t\t\t\tvtiger_contactdetails.mobile,vtiger_account.accountname,\n\t\t\t\tvtiger_account.accountid as accountid from vtiger_contactdetails\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_contactdetails.contactid\n\t\t\tinner join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid\n\t\t\tleft join vtiger_account on vtiger_account.accountid=vtiger_contactdetails.accountid\n\t\t\tleft join vtiger_contactaddress on vtiger_contactaddress.contactaddressid=vtiger_contactdetails.contactid\n\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery('Contacts', $current_user); $query .= "where vtiger_crmentity.deleted=0"; if (trim($emailaddress) != '') { $query .= " and ((vtiger_contactdetails.email like '" . formatForSqlLike($emailaddress) . "') or vtiger_contactdetails.lastname REGEXP REPLACE('" . $emailaddress . "',' ','|') or vtiger_contactdetails.firstname REGEXP REPLACE('" . $emailaddress . "',' ','|')) and vtiger_contactdetails.email != ''"; } else { $query .= " and (vtiger_contactdetails.email like '" . formatForSqlLike($emailaddress) . "' and vtiger_contactdetails.email != '')"; if (isset($groupidlist)) { $query .= " and (vtiger_users.user_name='" . $username . "' OR vtiger_crmentity.smownerid IN (" . substr($groupidlist, 1) . "))"; } else { $query .= " and vtiger_users.user_name='" . $username . "'"; } } $log->debug("Exiting get_searchbyemailid method ..."); return $this->plugin_process_list_query($query); }
function getReadEntityIds($module) { global $log; $log->debug("Entering getReadEntityIds(" . $module . ") method ..."); global $current_user; require 'user_privileges/user_privileges_' . $current_user->id . '.php'; require 'user_privileges/sharing_privileges_' . $current_user->id . '.php'; $tab_id = getTabid($module); if ($module == "Leads") { $query = "SELECT vtiger_crmentity.crmid\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\tLEFT JOIN vtiger_groups\n ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0\n\t\t\tAND vtiger_leaddetails.converted = 0 "; } elseif ($module == "Accounts") { //Query modified to sort by assigned to $query = "SELECT vtiger_crmentity.crmid\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\tLEFT JOIN vtiger_groups\n ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 "; } elseif ($module == "Potentials") { //Query modified to sort by assigned to $query = "SELECT vtiger_crmentity.crmid\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\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 "; } elseif ($module == "Contacts") { //Query modified to sort by assigned to $query = "SELECT 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\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 "; } elseif ($module == "Products") { $query = "SELECT DISTINCT vtiger_crmentity.crmid\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\tLEFT JOIN vtiger_seproductsrel\n\t\t\t\tON vtiger_seproductsrel.productid = vtiger_products.productid\n\t\t\tWHERE vtiger_crmentity.deleted = 0\n\t\t\tAND (vtiger_seproductsrel.crmid IS NULL\n\t\t\t\tOR vtiger_seproductsrel.crmid IN (" . getReadEntityIds('Leads') . ")\n\t\t\t\tOR vtiger_seproductsrel.crmid IN (" . getReadEntityIds('Accounts') . ")\n\t\t\t\tOR vtiger_seproductsrel.crmid IN (" . getReadEntityIds('Potentials') . ")\n\t\t\t\tOR vtiger_seproductsrel.crmid IN (" . getReadEntityIds('Contacts') . ")) "; } elseif ($module == "PurchaseOrder") { //Query modified to sort by assigned to $query = "SELECT vtiger_crmentity.crmid\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 JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 "; } elseif ($module == "SalesOrder") { //Query modified to sort by assigned to $query = "SELECT vtiger_crmentity.crmid\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\tLEFT JOIN vtiger_groups\n ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 "; } elseif ($module == "Invoice") { $query = "SELECT vtiger_crmentity.crmid\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 JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 "; } elseif ($module == "Quotes") { $query = "SELECT vtiger_crmentity.crmid\n\t\t FROM vtiger_quotes\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t ON vtiger_crmentity.crmid = vtiger_quotes.quoteid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 "; } elseif ($module == "HelpDesk") { $query = "SELECT vtiger_crmentity.crmid\n\t\t\tFROM vtiger_troubletickets\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"; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "WHERE vtiger_crmentity.deleted = 0 "; } $log->debug("Exiting getReadEntityIds method ..."); return $query; }
/** function used to get the services which are related to the pricebook * @param int $id - pricebook id * @return array - return an array which will be returned from the function getPriceBookRelatedServices **/ function get_pricebook_services($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_pricebook_services(" . $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 = ''; 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='submit' name='button' onclick=\"this.form.action.value='AddServicesToPriceBook';this.form.module.value='{$related_module}';this.form.return_module.value='{$currentModule}';this.form.return_action.value='PriceBookDetailView'\" value='" . getTranslatedString('LBL_SELECT') . " " . getTranslatedString($related_module) . "'> "; } } $query = 'SELECT vtiger_service.serviceid, vtiger_service.servicename, vtiger_service.commissionrate, vtiger_service.qty_per_unit, vtiger_service.unit_price, vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_pricebookproductrel.listprice FROM vtiger_service INNER JOIN vtiger_pricebookproductrel on vtiger_service.serviceid = vtiger_pricebookproductrel.productid INNER JOIN vtiger_crmentity on vtiger_crmentity.crmid = vtiger_service.serviceid INNER JOIN vtiger_pricebook on vtiger_pricebook.pricebookid = vtiger_pricebookproductrel.pricebookid LEFT JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid ' . getNonAdminAccessControlQuery($related_module, $current_user) . ' WHERE vtiger_pricebook.pricebookid = ' . $id . ' and vtiger_crmentity.deleted = 0'; $this->retrieve_entity_info($id, $this_module); $return_value = $other->getPriceBookRelatedServices($query, $this, $returnset); if ($return_value == null) { $return_value = array(); } $return_value['CUSTOM_BUTTON'] = $button; $log->debug("Exiting get_pricebook_services method ..."); return $return_value; }
/** * Returns a list of the Emails to be exported */ function create_export_query(&$order_by, &$where) { global $log; global $current_user; $log->debug("Entering create_export_query(" . $order_by . "," . $where . ") method ..."); include "include/utils/ExportUtils.php"; //To get the Permitted fields query and the permitted fields list $sql = getPermittedFieldsQuery("Emails", "detail_view"); $fields_list = getFieldsListFromQuery($sql); $query = "SELECT {$fields_list} FROM 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_seactivityrel\n\t\t\t\tON vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_contactdetails.contactid = vtiger_seactivityrel.crmid\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\n\t\t\tLEFT JOIN vtiger_seattachmentsrel \n\t\t\t\tON vtiger_activity.activityid=vtiger_seattachmentsrel.crmid \n\t\t\tLEFT JOIN vtiger_attachments \n\t\t\t\tON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid"; $query .= getNonAdminAccessControlQuery('Emails', $current_user); $query .= "WHERE vtiger_activity.activitytype='Emails' AND vtiger_crmentity.deleted=0 "; $log->debug("Exiting create_export_query method ..."); return $query; }
/** * Get list view query (send more WHERE clause condition if required) */ function getListQuery($module, $usewhere = false) { $query = "SELECT vtiger_crmentity.*, {$this->table_name}.*"; // Keep track of tables joined to avoid duplicates $joinedTables = array(); // Select Custom Field Table Columns if present if (!empty($this->customFieldTable)) { $query .= ", " . $this->customFieldTable[0] . ".* "; } $query .= " FROM {$this->table_name}"; $query .= "\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = {$this->table_name}.{$this->table_index}"; $joinedTables[] = $this->table_name; $joinedTables[] = 'vtiger_crmentity'; // Consider custom table join as well. if (!empty($this->customFieldTable)) { $query .= " INNER JOIN " . $this->customFieldTable[0] . " ON " . $this->customFieldTable[0] . '.' . $this->customFieldTable[1] . " = {$this->table_name}.{$this->table_index}"; $joinedTables[] = $this->customFieldTable[0]; } $query .= " LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid"; $query .= " LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid"; $joinedTables[] = 'vtiger_users'; $joinedTables[] = 'vtiger_groups'; $linkedModulesQuery = $this->db->pquery("SELECT distinct fieldname, columnname, relmodule FROM vtiger_field" . " INNER JOIN vtiger_fieldmodulerel ON vtiger_fieldmodulerel.fieldid = vtiger_field.fieldid" . " WHERE uitype='10' AND vtiger_fieldmodulerel.module=?", array($module)); $linkedFieldsCount = $this->db->num_rows($linkedModulesQuery); for ($i = 0; $i < $linkedFieldsCount; $i++) { $related_module = $this->db->query_result($linkedModulesQuery, $i, 'relmodule'); $fieldname = $this->db->query_result($linkedModulesQuery, $i, 'fieldname'); $columnname = $this->db->query_result($linkedModulesQuery, $i, 'columnname'); $other = CRMEntity::getInstance($related_module); vtlib_setup_modulevars($related_module, $other); if (!in_array($other->table_name, $joinedTables)) { $query .= " LEFT JOIN {$other->table_name} ON {$other->table_name}.{$other->table_index} = {$this->table_name}.{$columnname}"; $joinedTables[] = $other->table_name; } } global $current_user; $query .= getNonAdminAccessControlQuery($module, $current_user); $query .= "\tWHERE vtiger_crmentity.deleted = 0 "; if ($usewhere) { $query .= $usewhere; } $query .= $this->getListViewSecurityParameter($module); return $query; }