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