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