PHP listQueryNonAdminChange函数代码示例

 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 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);
//.........这里部分代码省略.........

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

//.........这里部分代码省略.........
    $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;
}