function OSSGetSQLforReport(&$ReportRun, $ifonly, $module, $recordid, $reportid, $filtersql, $type = '', $chartReport = false)
{
include_once 'include/utils/CommonUtils.php';
$columnlist = $ReportRun->getQueryColumnsList($reportid, $type);
$groupslist = $ReportRun->getGroupingList($reportid);
$groupTimeList = $ReportRun->getGroupByTimeList($reportid);
$stdfilterlist = $ReportRun->getStdFilterList($reportid);
$columnstotallist = $ReportRun->getColumnsTotal($reportid);
$advfiltersql = $ReportRun->getAdvFilterSql($reportid);
$ReportRun->totallist = $columnstotallist;
$tab_id = getTabid($ReportRun->primarymodule);
//Fix for ticket #4915.
$selectlist = $columnlist;
//columns list
if (isset($selectlist)) {
$selectedcolumns = implode(", ", $selectlist);
if ($chartReport == true) {
$selectedcolumns .= ", count(*) AS 'groupby_count'";
}
}
//groups list
if (isset($groupslist)) {
$groupsquery = implode(", ", $groupslist);
}
if (isset($groupTimeList)) {
$groupTimeQuery = implode(", ", $groupTimeList);
}
//standard list
if (isset($stdfilterlist)) {
$stdfiltersql = implode(", ", $stdfilterlist);
}
//columns to total list
if (isset($columnstotallist)) {
$columnstotalsql = implode(", ", $columnstotallist);
}
if ($stdfiltersql != "") {
$wheresql = " and " . $stdfiltersql;
}
if (isset($filtersql) && $filtersql !== false) {
$advfiltersql = $filtersql;
}
if ($advfiltersql != "") {
$wheresql .= " and " . $advfiltersql;
}
$reportquery = $ReportRun->getReportsQuery($ReportRun->primarymodule, $type);
// If we don't have access to any columns, let us select one column and limit result to shown we have not results
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$allColumnsRestricted = false;
if ($type == 'COLUMNSTOTOTAL') {
if ($columnstotalsql != '') {
$reportquery = "select " . $columnstotalsql . " " . $reportquery . " " . $wheresql;
}
} else {
if ($selectedcolumns == '') {
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$selectedcolumns = "''";
// "''" to get blank column name
$allColumnsRestricted = true;
}
if (in_array($ReportRun->primarymodule, array('Invoice', 'Quotes', 'SalesOrder', 'PurchaseOrder'))) {
$selectedcolumns = ' distinct ' . $selectedcolumns;
}
$reportquery = "select DISTINCT " . $selectedcolumns . " " . $reportquery . " " . $wheresql;
}
if ($ifonly == 1) {
require_once 'modules/' . $module . '/' . $module . '.php';
$obiekt = new $module();
$table = $obiekt->table_name;
$index = $obiekt->table_index;
$reportquery .= " AND {$table}.{$index} = '{$recordid}'";
}
$reportquery = listQueryNonAdminChange($reportquery, $ReportRun->primarymodule);
if (trim($groupsquery) != "" && $type !== 'COLUMNSTOTOTAL') {
if ($chartReport == true) {
$reportquery .= "group by " . $ReportRun->GetFirstSortByField($reportid);
} else {
$reportquery .= " order by " . $groupsquery;
}
}
// Prasad: No columns selected so limit the number of rows directly.
if ($allColumnsRestricted) {
$reportquery .= " limit 0";
}
preg_match('/&/', $reportquery, $matches);
if (!empty($matches)) {
$report = str_replace('&', '&', $reportquery);
$reportquery = $ReportRun->replaceSpecialChar($report);
}
return $reportquery;
}
/** function to get query for the given reportid,filterlist,type
* @ param $reportid : Type integer
* @ param $filtersql : Type Array
* @ param $module : Type String
* this returns join query for the report
*/
function sGetSQLforReport($reportid, $filtersql, $type = '', $chartReport = false, $startLimit = false, $endLimit = false)
{
$log = vglobal('log');
$columnlist = $this->getQueryColumnsList($reportid, $type);
$groupslist = $this->getGroupingList($reportid);
$groupTimeList = $this->getGroupByTimeList($reportid);
$stdfilterlist = $this->getStdFilterList($reportid);
$columnstotallist = $this->getColumnsTotal($reportid);
$advfiltersql = $this->getAdvFilterSql($reportid);
$this->totallist = $columnstotallist;
$current_user = vglobal('current_user');
//Fix for ticket #4915.
$selectlist = $columnlist;
//columns list
if (isset($selectlist)) {
$selectedcolumns = implode(", ", $selectlist);
if ($chartReport == true) {
$selectedcolumns .= ", count(*) AS 'groupby_count'";
}
}
//groups list
if (isset($groupslist)) {
$groupsquery = implode(", ", $groupslist);
}
if (isset($groupTimeList)) {
$groupTimeQuery = implode(", ", $groupTimeList);
}
//standard list
if (isset($stdfilterlist)) {
$stdfiltersql = implode(", ", $stdfilterlist);
}
//columns to total list
if (isset($columnstotallist)) {
$columnstotalsql = implode(", ", $columnstotallist);
}
if ($stdfiltersql != "") {
$wheresql = " and " . $stdfiltersql;
}
if (isset($filtersql) && $filtersql !== false && $filtersql != '') {
$advfiltersql = $filtersql;
}
if ($advfiltersql != "") {
$wheresql .= " and " . $advfiltersql;
}
$reportquery = $this->getReportsQuery($this->primarymodule, $type);
// If we don't have access to any columns, let us select one column and limit result to shown we have not results
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$allColumnsRestricted = false;
if ($type == 'COLUMNSTOTOTAL') {
if ($columnstotalsql != '') {
$reportquery = "select " . $columnstotalsql . " " . $reportquery . " " . $wheresql;
}
} else {
if ($selectedcolumns == '') {
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$selectedcolumns = "''";
// "''" to get blank column name
$allColumnsRestricted = true;
}
$reportquery = "select DISTINCT " . $selectedcolumns . " " . $reportquery . " " . $wheresql;
}
$reportquery = listQueryNonAdminChange($reportquery, $this->primarymodule);
if (trim($groupsquery) != "" && $type !== 'COLUMNSTOTOTAL') {
if ($chartReport == true) {
$reportquery .= "group by " . $this->GetFirstSortByField($reportid);
} else {
$reportquery .= " order by " . $groupsquery;
}
}
// Prasad: No columns selected so limit the number of rows directly.
if ($allColumnsRestricted) {
$reportquery .= " limit 0";
} else {
if ($startLimit !== false && $endLimit !== false) {
$reportquery .= " LIMIT {$startLimit}, {$endLimit}";
}
}
preg_match('/&/', $reportquery, $matches);
if (!empty($matches)) {
$report = str_replace('&', '&', $reportquery);
$reportquery = $this->replaceSpecialChar($report);
}
$log->info("ReportRun :: Successfully returned sGetSQLforReport" . $reportid);
$this->queryPlanner->initializeTempTables();
return $reportquery;
}
/** 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 = '')
{
global $log;
$log->debug("Entering getListQuery(" . $module . "," . $where . ") 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);
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.accountname, vtiger_account.email1,\n\t\t\tvtiger_account.email2, vtiger_account.website, vtiger_account.phone,\n\t\t\tvtiger_accountbillads.bill_city,\n\t\t\tvtiger_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_accountbillads\n\t\t\t\tON vtiger_account.accountid = vtiger_accountbillads.accountaddressid\n\t\t\tINNER JOIN vtiger_accountshipads\n\t\t\t\tON vtiger_account.accountid = vtiger_accountshipads.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_contactdetails\n\t\t\t\tON vtiger_potential.related_to = vtiger_contactdetails.contactid\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.firstname, vtiger_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.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_users\n\t\t\t\tON vtiger_users.id = vtiger_products.handler";
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 .= " WHERE vtiger_crmentity.deleted = 0 " . $where;
break;
case "Documents":
$query = "SELECT case when (vtiger_users.user_name not like '') then vtiger_users.user_name else vtiger_groups.groupname end as user_name,vtiger_crmentity.crmid, vtiger_crmentity.modifiedtime,\n\t\t\tvtiger_crmentity.smownerid,vtiger_attachmentsfolder.*,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_attachmentsfolder \n\t\t\t\tON vtiger_notes.folderid = vtiger_attachmentsfolder.folderid";
$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.accountid,\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.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_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\tvtiger_contactdetails.lastname, vtiger_contactdetails.firstname,\n\t\tvtiger_contactdetails.contactid,\n\t\tvtiger_account.accountid, vtiger_account.accountname\n\t\tFROM vtiger_activity\n\t\tLEFT JOIN vtiger_activitycf\n\t\t\tON vtiger_activitycf.activityid = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_cntactivityrel\n\t\t\tON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_contactdetails\n\t\t\tON vtiger_contactdetails.contactid = vtiger_cntactivityrel.contactid\n\t\tLEFT JOIN vtiger_seactivityrel\n\t\t\tON vtiger_seactivityrel.activityid = vtiger_activity.activityid\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 OUTER JOIN vtiger_account\n\t\t\tON vtiger_account.accountid = vtiger_contactdetails.accountid\n\t\tLEFT OUTER JOIN vtiger_leaddetails\n\t \t\tON vtiger_leaddetails.leadid = vtiger_seactivityrel.crmid\n\t\tLEFT OUTER JOIN vtiger_account vtiger_account2\n\t \tON vtiger_account2.accountid = vtiger_seactivityrel.crmid\n\t\tLEFT OUTER JOIN vtiger_potential\n\t \t\tON vtiger_potential.potentialid = vtiger_seactivityrel.crmid\n\t\tLEFT OUTER JOIN vtiger_troubletickets\n\t \t\tON vtiger_troubletickets.ticketid = vtiger_seactivityrel.crmid\n\t\tLEFT OUTER JOIN vtiger_salesorder\n\t\t\tON vtiger_salesorder.salesorderid = vtiger_seactivityrel.crmid\t\n\t\tLEFT OUTER JOIN vtiger_purchaseorder\n\t\t\tON vtiger_purchaseorder.purchaseorderid = vtiger_seactivityrel.crmid\t\n\t\tLEFT OUTER JOIN vtiger_quotes\n\t\t\tON vtiger_quotes.quoteid = vtiger_seactivityrel.crmid\n\t\tLEFT OUTER JOIN vtiger_invoice\n\t ON vtiger_invoice.invoiceid = vtiger_seactivityrel.crmid\n\t\tLEFT OUTER JOIN vtiger_campaign\n\t\tON vtiger_campaign.campaignid = vtiger_seactivityrel.crmid";
//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
$query .= getNonAdminAccessControlQuery($module, $current_user);
$query .= " WHERE vtiger_crmentity.deleted = 0 AND activitytype != 'Emails' " . $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_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\tWHERE vtiger_activity.activitytype = 'Emails'";
$query .= getNonAdminAccessControlQuery($module, $current_user);
$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.*\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_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_quotesbillads.*,\n\t\t\tvtiger_quotesshipads.*,\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_quotesbillads\n\t\t\t\tON vtiger_quotes.quoteid = vtiger_quotesbillads.quotebilladdressid\n\t\t\tINNER JOIN vtiger_quotesshipads\n\t\t\t\tON vtiger_quotes.quoteid = vtiger_quotesshipads.quoteshipaddressid\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_contactdetails\n\t\t\t\tON vtiger_contactdetails.contactid = vtiger_quotes.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_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_pobillads.*,\n\t\t\tvtiger_poshipads.*,\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\t\n\t\t\tINNER JOIN vtiger_pobillads\n\t\t\t\tON vtiger_purchaseorder.purchaseorderid = vtiger_pobillads.pobilladdressid\n\t\t\tINNER JOIN vtiger_poshipads\n\t\t\t\tON vtiger_purchaseorder.purchaseorderid = vtiger_poshipads.poshipaddressid\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_sobillads.*,\n\t\t\tvtiger_soshipads.*,\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_sobillads\n\t\t\t\tON vtiger_salesorder.salesorderid = vtiger_sobillads.sobilladdressid\n\t\t\tINNER JOIN vtiger_soshipads\n\t\t\t\tON vtiger_salesorder.salesorderid = vtiger_soshipads.soshipaddressid\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\t\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_invoicebillads.*,\n\t\t\tvtiger_invoiceshipads.*,\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_invoicebillads\n\t\t\t\tON vtiger_invoice.invoiceid = vtiger_invoicebillads.invoicebilladdressid\n\t\t\tINNER JOIN vtiger_invoiceshipads\n\t\t\t\tON vtiger_invoice.invoiceid = vtiger_invoiceshipads.invoiceshipaddressid\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,phone_mobile,phone_work,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 " . $where;
break;
default:
// vtlib customization: Include the module file
$focus = CRMEntity::getInstance($module);
$query = $focus->getListQuery($module, $where);
// END
}
if ($module != 'Users') {
$query = listQueryNonAdminChange($query, $module);
}
$log->debug("Exiting getListQuery method ...");
return $query;
}
/** function to get query for the given relblockid,filterlist,type
* @ param $relblockid : Type integer
* @ param $filterlist : Type Array
* @ param $module : Type String
* this returns join query for the report
*/
function sGetSQLforReport($relblockid)
{
global $log;
$columnlist = $this->getQueryColumnsList($relblockid);
$sortColsql = $this->getSortColSql($columnlist, $relblockid);
$stdfilterlist = $this->getStdFilterList($relblockid);
$advfiltersql = $this->getAdvFilterSql($relblockid);
$this->totallist = $columnstotallist;
$selectlist = $columnlist;
//columns list
if (isset($selectlist)) {
$selectedcolumns = implode(", ", $selectlist);
}
if (isset($stdfilterlist)) {
$stdfiltersql = implode(", ", $stdfilterlist);
}
//columns to total list
if (isset($columnstotallist)) {
$columnstotalsql = implode(", ", $columnstotallist);
}
if ($stdfiltersql != "") {
$wheresql = " and " . $stdfiltersql;
}
if ($advfiltersql != "") {
$wheresql .= " and " . $advfiltersql;
}
$reportquery = $this->getReportsQuery($this->primarymodule);
// ITS4YOU MaJu fix for multiple rows selected
if ($this->secondarymodule != '' && strpos($reportquery, 'left join vtiger_crmentityrel as ') !== false) {
$Exploded1 = explode('left join vtiger_crmentityrel as ', $reportquery);
$Exploded2 = explode(' ON ', $Exploded1[1]);
$relalias = $Exploded2[0];
$wheresql .= " and ({$relalias}.module='" . $this->secondarymodule . "' OR {$relalias}.relmodule='" . $this->secondarymodule . "') ";
}
// ITS4YOU-END
// If we don't have access to any columns, let us select one column and limit result to shown we have not results
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$allColumnsRestricted = false;
if ($selectedcolumns == '') {
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$selectedcolumns = "''";
// "''" to get blank column name
$allColumnsRestricted = true;
}
if (in_array($this->primarymodule, array('Invoice', 'Quotes', 'SalesOrder', 'PurchaseOrder')) or in_array($this->secondarymodule, array('Invoice', 'Quotes', 'SalesOrder', 'PurchaseOrder'))) {
$selectedcolumns = ' distinct ' . $selectedcolumns;
}
$reportquery = "select " . $selectedcolumns . " " . $reportquery . " " . $wheresql;
$reportquery = listQueryNonAdminChange($reportquery, $this->primarymodule);
//VlZa - Sorting
$reportquery .= " " . $sortColsql;
// Prasad: No columns selected so limit the number of rows directly.
if ($allColumnsRestricted) {
$reportquery .= " limit 0";
}
$log->info("ReportRun :: Successfully returned sGetSQLforReport" . $relblockid);
//$this->queryPlanner->initializeTempTables();
return $reportquery;
}
/** function to get query for the given reportid,filterlist,type
* @ param $reportid : Type integer
* @ param $filtersql : Type Array
* @ param $module : Type String
* this returns join query for the report
*/
function sGetSQLforReport($reportid, $filtersql, $type = '', $chartReport = false)
{
global $log;
$columnlist = $this->getQueryColumnsList($reportid, $type);
$groupslist = $this->getGroupingList($reportid);
$groupTimeList = $this->getGroupByTimeList($reportid);
$stdfilterlist = $this->getStdFilterList($reportid);
$columnstotallist = $this->getColumnsTotal($reportid, $columnlist);
$advfiltersql = $this->getAdvFilterSql($reportid);
$this->totallist = $columnstotallist;
global $current_user;
$tab_id = getTabid($this->primarymodule);
//Fix for ticket #4915.
$selectlist = $columnlist;
//columns list
if (isset($selectlist)) {
$selectedcolumns = implode(", ", $selectlist);
if ($chartReport == true) {
$selectedcolumns .= ", count(*) AS 'groupby_count'";
}
}
//groups list
if (isset($groupslist)) {
$groupsquery = implode(", ", $groupslist);
}
if (isset($groupTimeList)) {
$groupTimeQuery = implode(', ', $groupTimeList);
}
//standard list
if (isset($stdfilterlist)) {
$stdfiltersql = implode(", ", $stdfilterlist);
}
//columns to total list
if (isset($columnstotallist)) {
$columnstotalsql = implode(', ', $columnstotallist);
} else {
$columnstotalsql = '';
}
if ($stdfiltersql != '') {
$wheresql = ' and ' . $stdfiltersql;
} else {
$wheresql = '';
}
if (isset($filtersql) && $filtersql !== false) {
$advfiltersql = $filtersql;
}
$where_condition = '';
if ($advfiltersql != "") {
if ($type == 'COLUMNSTOTOTAL') {
if (strstr($advfiltersql, 'vtiger_products' . $this->primarymodule) || strstr($advfiltersql, 'vtiger_service' . $this->primarymodule)) {
$where_condition = 'add';
}
}
$wheresql .= " and " . $advfiltersql;
}
$reportquery = $this->getReportsQuery($this->primarymodule, $type, $where_condition);
// If we don't have access to any columns, let us select one column and limit result to show we have no results
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$allColumnsRestricted = false;
if ($type == 'COLUMNSTOTOTAL') {
if ($columnstotalsql != '') {
$totalsselectedcolumns = $columnlist;
if (isset($this->_columnstotallistaddtoselect) and is_array($this->_columnstotallistaddtoselect) and count($this->_columnstotallistaddtoselect) > 0) {
$_columnstotallistaddtoselect = ', ' . implode(', ', $this->_columnstotallistaddtoselect);
$totalscolalias = array();
foreach ($this->_columnstotallistaddtoselect as $key => $value) {
list($void, $calias) = explode(' AS ', $value);
$calias = str_replace("'", '', $calias);
$totalscolalias[] = $calias;
}
foreach ($columnlist as $key => $value) {
foreach ($totalscolalias as $cal) {
if (preg_match("/\\b{$cal}\\b/i", $value)) {
unset($totalsselectedcolumns[$key]);
break;
}
}
}
} else {
$_columnstotallistaddtoselect = '';
}
$totalsselectedcolumns = implode(', ', $totalsselectedcolumns);
$reportquery = "select " . $columnstotalsql . ' from (select DISTINCT ' . $totalsselectedcolumns . $_columnstotallistaddtoselect . " " . $reportquery . " " . $wheresql . ') as summary_calcs';
}
} else {
if ($selectedcolumns == '') {
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$selectedcolumns = "''";
// "''" to get blank column name
$allColumnsRestricted = true;
}
$reportquery = "select DISTINCT " . $selectedcolumns . " " . $reportquery . " " . $wheresql;
}
$reportquery = listQueryNonAdminChange($reportquery, $this->primarymodule);
if (trim($groupsquery) != "" && $type !== 'COLUMNSTOTOTAL') {
if ($chartReport == true) {
$reportquery .= " group by " . $this->GetFirstSortByField($reportid);
} else {
$reportquery .= " order by " . $groupsquery;
}
}
// Prasad: No columns selected so limit the number of rows directly.
if ($allColumnsRestricted) {
$reportquery .= " limit 0";
}
preg_match('/&/', $reportquery, $matches);
if (!empty($matches)) {
$report = str_replace('&', '&', $reportquery);
$reportquery = $this->replaceSpecialChar($report);
}
$log->info("ReportRun :: Successfully returned sGetSQLforReport" . $reportid);
if (GlobalVariable::getVariable('Debug_Report_Query', '0') == '1') {
echo '<br>' . $reportquery . '<br>';
}
return $reportquery;
}
function getCalendar4YouListQuery($userid, $invites, $where = '', $type = '1')
{
global $log;
global $current_user;
$log->debug("Entering getCalendar4YouListQuery(" . $userid . "," . $where . ") method ...");
if ($userid != "") {
require 'user_privileges/user_privileges_' . $userid . '.php';
require 'user_privileges/sharing_privileges_' . $userid . '.php';
}
//$tab_id = getTabid("Calendar4You");
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$query = "SELECT distinct vtiger_activity.activityid as act_id, vtiger_crmentity.*, vtiger_activity.*, vtiger_activitycf.*, ";
if ($type == '1') {
$query .= "vtiger_contactdetails.lastname, vtiger_contactdetails.firstname, vtiger_contactdetails.contactid, vtiger_account.accountid, vtiger_account.accountname, ";
}
$query .= "vtiger_seactivityrel.crmid AS parent_id,\n its4you_googlesync4you_events.geventid,\n vtiger_activity_reminder.reminder_time \n\tFROM vtiger_activity\n\tLEFT JOIN vtiger_activitycf\n\t\tON vtiger_activitycf.activityid = vtiger_activity.activityid\n\tLEFT JOIN vtiger_cntactivityrel\n\t\tON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\tLEFT JOIN vtiger_contactdetails\n\t\tON vtiger_contactdetails.contactid = vtiger_cntactivityrel.contactid\n\tLEFT JOIN vtiger_seactivityrel\n\t\tON vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\tLEFT OUTER JOIN vtiger_activity_reminder\n\t\tON vtiger_activity_reminder.activity_id = vtiger_activity.activityid\n\tLEFT JOIN vtiger_crmentity\n\t\tON vtiger_crmentity.crmid = vtiger_activity.activityid\n\tLEFT JOIN vtiger_users\n\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\tLEFT JOIN vtiger_groups\n\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\tLEFT JOIN vtiger_users vtiger_users2\n\t\tON vtiger_crmentity.modifiedby = vtiger_users2.id\n\tLEFT JOIN vtiger_groups vtiger_groups2\n\t\tON vtiger_crmentity.modifiedby = vtiger_groups2.groupid\n\tLEFT OUTER JOIN vtiger_account\n\t\tON vtiger_account.accountid = vtiger_contactdetails.accountid\n\tLEFT OUTER JOIN vtiger_leaddetails\n \t\tON vtiger_leaddetails.leadid = vtiger_seactivityrel.crmid\n\tLEFT OUTER JOIN vtiger_account vtiger_account2\n \tON vtiger_account2.accountid = vtiger_seactivityrel.crmid\n\tLEFT OUTER JOIN vtiger_potential\n \t\tON vtiger_potential.potentialid = vtiger_seactivityrel.crmid\n\tLEFT OUTER JOIN vtiger_troubletickets\n \t\tON vtiger_troubletickets.ticketid = vtiger_seactivityrel.crmid\n\tLEFT OUTER JOIN vtiger_salesorder\n\t\tON vtiger_salesorder.salesorderid = vtiger_seactivityrel.crmid\n\tLEFT OUTER JOIN vtiger_purchaseorder\n\t\tON vtiger_purchaseorder.purchaseorderid = vtiger_seactivityrel.crmid\n\tLEFT OUTER JOIN vtiger_quotes\n\t\tON vtiger_quotes.quoteid = vtiger_seactivityrel.crmid\n\tLEFT OUTER JOIN vtiger_invoice\n ON vtiger_invoice.invoiceid = vtiger_seactivityrel.crmid\n\tLEFT OUTER JOIN vtiger_campaign\n\tON vtiger_campaign.campaignid = vtiger_seactivityrel.crmid ";
//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 ON vtiger_recurringevents.activityid=vtiger_activity.activityid ";
}
//end
//google cal sync
$query .= "LEFT JOIN its4you_googlesync4you_events \n ON its4you_googlesync4you_events.crmid = vtiger_activity.activityid AND its4you_googlesync4you_events.userid = '" . $userid . "' ";
if ($invites && $userid != "") {
$query .= "INNER JOIN vtiger_invitees ON vtiger_invitees.activityid = vtiger_activity.activityid AND vtiger_invitees.inviteeid = '" . $userid . "' ";
}
//$query .= getCalendar4YouNonAdminAccessControlQuery($userid);
$query .= " WHERE vtiger_crmentity.deleted = 0 AND activitytype != 'Emails' " . $where;
$query = listQueryNonAdminChange($query, "Calendar");
$log->debug("Exiting getListQuery method ...");
return $query;
}
/** function to get query for the given reportid,filterlist,type
* @ param $reportid : Type integer
* @ param $filterlist : Type Array
* @ param $module : Type String
* this returns join query for the report
*/
function sGetSQLforReport($reportid, $filterlist, $type = '')
{
global $log;
$columnlist = $this->getQueryColumnsList($reportid, $type);
$groupslist = $this->getGroupingList($reportid);
$stdfilterlist = $this->getStdFilterList($reportid);
$columnstotallist = $this->getColumnsTotal($reportid);
$advfiltersql = $this->getAdvFilterSql($reportid);
$this->totallist = $columnstotallist;
global $current_user;
$tab_id = getTabid($this->primarymodule);
//Fix for ticket #4915.
$selectlist = $columnlist;
//columns list
if (isset($selectlist)) {
$selectedcolumns = implode(", ", $selectlist);
}
//groups list
if (isset($groupslist)) {
$groupsquery = implode(", ", $groupslist);
}
//standard list
if (isset($stdfilterlist)) {
$stdfiltersql = implode(", ", $stdfilterlist);
}
if (isset($filterlist)) {
$stdfiltersql = implode(", ", $filterlist);
}
//columns to total list
if (isset($columnstotallist)) {
$columnstotalsql = implode(", ", $columnstotallist);
}
if ($stdfiltersql != "") {
$wheresql = " and " . $stdfiltersql;
}
if ($advfiltersql != "") {
$wheresql .= " and " . $advfiltersql;
}
$reportquery = $this->getReportsQuery($this->primarymodule, $type);
// If we don't have access to any columns, let us select one column and limit result to shown we have not results
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$allColumnsRestricted = false;
if ($type == 'COLUMNSTOTOTAL') {
if ($columnstotalsql != '') {
$reportquery = "select " . $columnstotalsql . " " . $reportquery . " " . $wheresql;
}
} else {
if ($selectedcolumns == '') {
// Fix for: http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/4758 - Prasad
$selectedcolumns = "''";
// "''" to get blank column name
$allColumnsRestricted = true;
}
if (in_array($this->primarymodule, array('Invoice', 'Quotes', 'SalesOrder', 'PurchaseOrder'))) {
$selectedcolumns = ' distinct ' . $selectedcolumns;
}
$reportquery = "select " . $selectedcolumns . " " . $reportquery . " " . $wheresql;
}
$reportquery = listQueryNonAdminChange($reportquery, $this->primarymodule);
if (trim($groupsquery) != "" && !empty($type) && $type !== 'COLUMNSTOTOTAL') {
$reportquery .= " order by " . $groupsquery;
}
// Prasad: No columns selected so limit the number of rows directly.
if ($allColumnsRestricted) {
$reportquery .= " limit 0";
}
$log->info("ReportRun :: Successfully returned sGetSQLforReport" . $reportid);
return $reportquery;
}
/** 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 "Leads":
$query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid,\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 "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;
}
