PHP SugarBean::create_new_list_query方法代码示例

 function create_new_list_query($order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = '', $return_array = false, $parentbean = null, $singleSelect = false)
 {
     if ($return_array) {
         return parent::create_new_list_query($order_by, $where, $filter, $params, $show_deleted, $join_type, $return_array, $parentbean, $singleSelect);
     }
     if ($this->db->dbType == 'mysql' or $this->db->dbType == 'oci8') {
         $query = "SELECT {$this->table_name}.* ,\n\t\t\t\t\tcampaigns.name as campaign_name,\n\t\t\t\t\temail_marketing.name as message_name,\n\t\t\t\t\t(CASE related_type\n\t\t\t\t\t\tWHEN 'Contacts' THEN CONCAT(CONCAT(contacts.first_name, ' ' ), contacts.last_name)\n\t\t\t\t\t\tWHEN 'Leads' THEN CONCAT(CONCAT(leads.first_name, ' ' ), leads.last_name)\n\t\t\t\t\t\tWHEN 'Accounts' THEN accounts.name\n\t\t\t\t\t\tWHEN 'Users' THEN CONCAT(CONCAT(users.first_name, ' ' ), users.last_name)\n\t\t\t\t\t\tWHEN 'Prospects' THEN CONCAT(CONCAT(prospects.first_name, ' ' ), prospects.last_name)\n\t\t\t\t\tEND) recipient_name";
     }
     if ($this->db->dbType == 'mssql') {
         $query = "SELECT {$this->table_name}.* ,\n\t\t\t\t\tcampaigns.name as campaign_name,\n\t\t\t\t\temail_marketing.name as message_name,\n\t\t\t\t\t(CASE related_type\n\t\t\t\t\t\tWHEN 'Contacts' THEN contacts.first_name + ' ' + contacts.last_name\n\t\t\t\t\t\tWHEN 'Leads' THEN  leads.first_name + ' ' + leads.last_name\n\t\t\t\t\t\tWHEN 'Accounts' THEN  accounts.name\n\t\t\t\t\t\tWHEN 'Users' THEN  users.first_name + ' ' + users.last_name\t\t\t\t\t\t\n\t\t\t\t\t\tWHEN 'Prospects' THEN prospects.first_name + ' ' + prospects.last_name\n\t\t\t\t\tEND) recipient_name";
     }
     /*
     					$query .= " (CASE related_type
     						WHEN 'Contacts' THEN 'aaa'
     						WHEN 'Leads' THEN 'cc'
     						WHEN 'Users' THEN 'bb'
     						WHEN 'Prospects' THEN 'dd'
     					END) recipient_email
     */
     $query .= "\tFROM {$this->table_name}\n\t\t\t\t\tLEFT JOIN users ON users.id = {$this->table_name}.related_id and {$this->table_name}.related_type ='Users'\n\t\t\t\t\tLEFT JOIN contacts ON contacts.id = {$this->table_name}.related_id and {$this->table_name}.related_type ='Contacts'\n\t\t\t\t\tLEFT JOIN leads ON leads.id = {$this->table_name}.related_id and {$this->table_name}.related_type ='Leads'\n\t\t\t\t\tLEFT JOIN accounts ON accounts.id = {$this->table_name}.related_id and {$this->table_name}.related_type ='Accounts'\n\t\t\t\t\tLEFT JOIN prospects ON prospects.id = {$this->table_name}.related_id and {$this->table_name}.related_type ='Prospects'\n\t\t\t\t\tLEFT JOIN prospect_lists ON prospect_lists.id = {$this->table_name}.list_id \n                    LEFT JOIN email_addr_bean_rel ON email_addr_bean_rel.bean_id = {$this->table_name}.related_id and {$this->table_name}.related_type = email_addr_bean_rel.bean_module and email_addr_bean_rel.deleted=0\n\t\t\t\t\tLEFT JOIN campaigns ON campaigns.id = {$this->table_name}.campaign_id\n\t\t\t\t\tLEFT JOIN email_marketing ON email_marketing.id = {$this->table_name}.marketing_id ";
     $where_auto = " {$this->table_name}.deleted=0";
     if ($where != "") {
         $query .= "where {$where} AND " . $where_auto;
     } else {
         $query .= "where " . $where_auto;
     }
     if ($order_by != "") {
         $query .= " ORDER BY {$order_by}";
     }
     return $query;
 }

 /**
 * takes in a seed and creates the list view query based off of that seed
 * if the $limit value is set to -1 then it will use the default limit and offset values
 *
 * it will return an array with two key values
 * 	1. 'data'=> this is an array of row data
 *  2. 'pageData'=> this is an array containg three values
 * 			a.'ordering'=> array('orderBy'=> the field being ordered by , 'sortOrder'=> 'ASC' or 'DESC')
 * 			b.'urls'=>array('baseURL'=>url used to generate other urls ,
 * 							'orderBy'=> the base url for order by
 * 							//the following may not be set (so check empty to see if they are set)
 * 							'nextPage'=> the url for the next group of results,
 * 							'prevPage'=> the url for the prev group of results,
 * 							'startPage'=> the url for the start of the group,
 * 							'endPage'=> the url for the last set of results in the group
 * 			c.'offsets'=>array(
 * 								'current'=>current offset
 * 								'next'=> next group offset
 * 								'prev'=> prev group offset
 * 								'end'=> the offset of the last group
 * 								'total'=> the total count (only accurate if totalCounted = true otherwise it is either the total count if less than the limit or the total count + 1 )
 * 								'totalCounted'=> if a count query was used to get the total count
 *
 * @param SugarBean $seed
 * @param string $where
 * @param int:0 $offset
 * @param int:-1 $limit
 * @param string[]:array() $filter_fields
 * @param array:array() $params
 * 	Potential $params are
 		$params['distinct'] = use distinct key word
 		$params['include_custom_fields'] = (on by default)
         $params['custom_XXXX'] = append custom statements to query
 * @param string:'id' $id_field
 * @return array('data'=> row data, 'pageData' => page data information, 'query' => original query string)
 */
 function getListViewData($seed, $where, $offset = -1, $limit = -1, $filter_fields = array(), $params = array(), $id_field = 'id', $singleSelect = true)
 {
     global $current_user;
     SugarVCR::erase($seed->module_dir);
     $this->seed =& $seed;
     $totalCounted = empty($GLOBALS['sugar_config']['disable_count_query']);
     $_SESSION['MAILMERGE_MODULE_FROM_LISTVIEW'] = $seed->module_dir;
     if (empty($_REQUEST['action']) || $_REQUEST['action'] != 'Popup') {
         $_SESSION['MAILMERGE_MODULE'] = $seed->module_dir;
     }
     $this->setVariableName($seed->object_name, $where, $this->listviewName);
     $this->seed->id = '[SELECT_ID_LIST]';
     // if $params tell us to override all ordering
     if (!empty($params['overrideOrder']) && !empty($params['orderBy'])) {
         $order = $this->getOrderBy(strtolower($params['orderBy']), empty($params['sortOrder']) ? '' : $params['sortOrder']);
         // retreive from $_REQUEST
     } else {
         $order = $this->getOrderBy();
         // retreive from $_REQUEST
     }
     // else use stored preference
     $userPreferenceOrder = $current_user->getPreference('listviewOrder', $this->var_name);
     if (empty($order['orderBy']) && !empty($userPreferenceOrder)) {
         $order = $userPreferenceOrder;
     }
     // still empty? try to use settings passed in $param
     if (empty($order['orderBy']) && !empty($params['orderBy'])) {
         $order['orderBy'] = $params['orderBy'];
         $order['sortOrder'] = empty($params['sortOrder']) ? '' : $params['sortOrder'];
     }
     //rrs - bug: 21788. Do not use Order by stmts with fields that are not in the query.
     // Bug 22740 - Tweak this check to strip off the table name off the order by parameter.
     // Samir Gandhi : Do not remove the report_cache.date_modified condition as the report list view is broken
     $orderby = $order['orderBy'];
     if (strpos($order['orderBy'], '.') && $order['orderBy'] != "report_cache.date_modified") {
         $orderby = substr($order['orderBy'], strpos($order['orderBy'], '.') + 1);
     }
     if ($orderby != 'date_entered' && !in_array($orderby, array_keys($filter_fields))) {
         $order['orderBy'] = '';
         $order['sortOrder'] = '';
     }
     if (empty($order['orderBy'])) {
         $orderBy = '';
     } else {
         $orderBy = $order['orderBy'] . ' ' . $order['sortOrder'];
         //wdong, Bug 25476, fix the sorting problem of Oracle.
         if (isset($params['custom_order_by_override']['ori_code']) && $order['orderBy'] == $params['custom_order_by_override']['ori_code']) {
             $orderBy = $params['custom_order_by_override']['custom_code'] . ' ' . $order['sortOrder'];
         }
     }
     if (empty($params['skipOrderSave'])) {
         // don't save preferences if told so
         $current_user->setPreference('listviewOrder', $order, 0, $this->var_name);
         // save preference
     }
     // If $params tells us to override for the special last_name, first_name sorting
     if (!empty($params['overrideLastNameOrder']) && $order['orderBy'] == 'last_name') {
         $orderBy = 'last_name ' . $order['sortOrder'] . ', first_name ' . $order['sortOrder'];
     }
     $ret_array = $seed->create_new_list_query($orderBy, $where, $filter_fields, $params, 0, '', true, $seed, $singleSelect);
     $ret_array['inner_join'] = '';
     if (!empty($this->seed->listview_inner_join)) {
         $ret_array['inner_join'] = ' ' . implode(' ', $this->seed->listview_inner_join) . ' ';
     }
//.........这里部分代码省略.........

 function create_new_list_query($order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = '', $return_array = false, $parentbean = null, $singleSelect = false)
 {
     if ($return_array) {
         return parent::create_new_list_query($order_by, $where, $filter, $params, $show_deleted, $join_type, $return_array, $parentbean, $singleSelect);
     }
     $custom_join = $this->custom_fields->getJOIN();
     $query = "SELECT " . $this->table_name . ".*, users.user_name as assigned_user_name\n";
     if ($custom_join) {
         $query .= $custom_join['select'];
     }
     $query .= " FROM emails\n";
     if ($where != "" && strpos($where, "contacts.first_name") > 0) {
         $query .= " LEFT JOIN emails_beans ON emails.id = emails_beans.email_id\n";
     }
     $query .= " LEFT JOIN users ON emails.assigned_user_id=users.id \n";
     if ($where != "" && strpos($where, "contacts.first_name") > 0) {
         $query .= " JOIN contacts ON contacts.id= emails_beans.bean_id AND emails_beans.bean_module='Contacts' and contacts.deleted=0 \n";
     }
     if ($custom_join) {
         $query .= $custom_join['join'];
     }
     if ($show_deleted == 0) {
         $where_auto = " emails.deleted=0 \n";
     } else {
         if ($show_deleted == 1) {
             $where_auto = " emails.deleted=1 \n";
         }
     }
     if ($where != "") {
         $query .= "WHERE {$where} AND " . $where_auto;
     } else {
         $query .= "WHERE " . $where_auto;
     }
     if ($order_by != "") {
         $query .= " ORDER BY {$order_by}";
     } else {
         $query .= " ORDER BY date_sent DESC";
     }
     return $query;
 }

 function create_queue_items_query($order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = '', $return_array = false, $parentbean = null, $singleSelect = false)
 {
     if ($return_array) {
         return parent::create_new_list_query($order_by, $where, $filter, $params, $show_deleted, $join_type, $return_array, $parentbean, $singleSelect);
     }
     $query = "SELECT {$this->table_name}.* ,\n\t\t\t\t\tcampaigns.name as campaign_name,\n\t\t\t\t\temail_marketing.name as message_name,\n\t\t\t\t\t(CASE related_type\n\t\t\t\t\t\tWHEN 'Contacts' THEN " . $this->db->concat('contacts', array('first_name', 'last_name'), ' ') . "\n\t\t\t\t\t\tWHEN 'Leads' THEN " . $this->db->concat('leads', array('first_name', 'last_name'), ' ') . "\n\t\t\t\t\t\tWHEN 'Accounts' THEN accounts.name\n\t\t\t\t\t\tWHEN 'Users' THEN " . $this->db->concat('users', array('first_name', 'last_name'), ' ') . "\n\t\t\t\t\t\tWHEN 'Prospects' THEN " . $this->db->concat('prospects', array('first_name', 'last_name'), ' ') . "\n\t\t\t\t\tEND) recipient_name";
     $query .= " FROM {$this->table_name}\n\t\t            LEFT JOIN users ON users.id = {$this->table_name}.related_id and {$this->table_name}.related_type ='Users'\n\t\t\t\t\tLEFT JOIN contacts ON contacts.id = {$this->table_name}.related_id and {$this->table_name}.related_type ='Contacts'\n\t\t\t\t\tLEFT JOIN leads ON leads.id = {$this->table_name}.related_id and {$this->table_name}.related_type ='Leads'\n\t\t\t\t\tLEFT JOIN accounts ON accounts.id = {$this->table_name}.related_id and {$this->table_name}.related_type ='Accounts'\n\t\t\t\t\tLEFT JOIN prospects ON prospects.id = {$this->table_name}.related_id and {$this->table_name}.related_type ='Prospects'\n\t\t\t\t\tLEFT JOIN prospect_lists ON prospect_lists.id = {$this->table_name}.list_id\n                    LEFT JOIN email_addr_bean_rel ON email_addr_bean_rel.bean_id = {$this->table_name}.related_id and {$this->table_name}.related_type = email_addr_bean_rel.bean_module and email_addr_bean_rel.primary_address = 1 and email_addr_bean_rel.deleted=0\n\t\t\t\t\tLEFT JOIN campaigns ON campaigns.id = {$this->table_name}.campaign_id\n\t\t\t\t\tLEFT JOIN email_marketing ON email_marketing.id = {$this->table_name}.marketing_id ";
     //B.F. #37943
     if (isset($params['group_by'])) {
         $group_by = str_replace("emailman", "em", $params['group_by']);
         $query .= "INNER JOIN (select min(id) as id from emailman  em GROUP BY {$group_by}  ) secondary\n\t\t\t           on {$this->table_name}.id = secondary.id\t";
     }
     $where_auto = " {$this->table_name}.deleted=0";
     if ($where != "") {
         $query .= "WHERE {$where} AND " . $where_auto;
     } else {
         $query .= "WHERE " . $where_auto;
     }
     if ($order_by != "") {
         $query .= ' ORDER BY ' . $this->process_order_by($order_by, null);
     }
     return $query;
 }

 /**
 * takes in a seed and creates the list view query based off of that seed 
 * if the $limit value is set to -1 then it will use the default limit and offset values
 * 
 * it will return an array with two key values
 * 	1. 'data'=> this is an array of row data
 *  2. 'pageData'=> this is an array containg three values
 * 			a.'ordering'=> array('orderBy'=> the field being ordered by , 'sortOrder'=> 'ASC' or 'DESC')
 * 			b.'urls'=>array('baseURL'=>url used to generate other urls , 
 * 							'orderBy'=> the base url for order by
 * 							//the following may not be set (so check empty to see if they are set)
 * 							'nextPage'=> the url for the next group of results, 
 * 							'prevPage'=> the url for the prev group of results,
 * 							'startPage'=> the url for the start of the group,
 * 							'endPage'=> the url for the last set of results in the group
 * 			c.'offsets'=>array(
 * 								'current'=>current offset
 * 								'next'=> next group offset
 * 								'prev'=> prev group offset
 * 								'end'=> the offset of the last group
 * 								'total'=> the total count (only accurate if totalCounted = true otherwise it is either the total count if less than the limit or the total count + 1 )
 * 								'totalCounted'=> if a count query was used to get the total count
 *
 * @param SugarBean $seed
 * @param string $where
 * @param int:0 $offset
 * @param int:-1 $limit
 * @param string[]:array() $filter_fields
 * @param array:array() $params
 * 	Potential $params are 
 		$params['distinct'] = use distinct key word
 		$params['include_custom_fields'] = (on by default)
         $params['custom_XXXX'] = append custom statements to query
 * @param string:'id' $id_field
 * @return array('data'=> row data 'pageData' => page data information 
 */
 function getListViewData($seed, $where, $offset = -1, $limit = -1, $filter_fields = array(), $params = array(), $id_field = 'id')
 {
     global $current_user;
     $this->seed =& $seed;
     $totalCounted = empty($GLOBALS['sugar_config']['disable_count_query']);
     $_SESSION['MAILMERGE_MODULE_FROM_LISTVIEW'] = $seed->module_dir;
     if (empty($_REQUEST['action']) || $_REQUEST['action'] != 'Popup') {
         $_SESSION['MAILMERGE_MODULE'] = $seed->module_dir;
     }
     $this->setVariableName($seed->object_name, $where, $this->listviewName);
     $this->seed->id = '[SELECT_ID_LIST]';
     // if $params tell us to override all ordering
     if (!empty($params['overrideOrder']) && !empty($params['orderBy'])) {
         $order = $this->getOrderBy(strtolower($params['orderBy']), empty($params['sortOrder']) ? '' : $params['sortOrder']);
         // retreive from $_REQUEST
     } else {
         $order = $this->getOrderBy();
         // retreive from $_REQUEST
     }
     // else use stored preference
     $userPreferenceOrder = $current_user->getPreference('listviewOrder', $this->var_name);
     if (empty($order['orderBy']) && !empty($userPreferenceOrder)) {
         $order = $userPreferenceOrder;
     }
     // still empty? try to use settings passed in $param
     if (empty($order['orderBy']) && !empty($params['orderBy'])) {
         $order['orderBy'] = $params['orderBy'];
         $order['sortOrder'] = empty($params['sortOrder']) ? '' : $params['sortOrder'];
     }
     if (empty($order['orderBy'])) {
         $orderBy = '';
     } else {
         $orderBy = $order['orderBy'] . ' ' . $order['sortOrder'];
     }
     if (empty($params['skipOrderSave'])) {
         // don't save preferences if told so
         $current_user->setPreference('listviewOrder', $order, 0, $this->var_name);
     }
     // save preference
     $ret_array = $seed->create_new_list_query($orderBy, $where, $filter_fields, $params, 0, '', true, $seed, true);
     if (!is_array($params)) {
         $params = array();
     }
     if (!isset($params['custom_select'])) {
         $params['custom_select'] = '';
     }
     if (!isset($params['custom_from'])) {
         $params['custom_from'] = '';
     }
     if (!isset($params['custom_where'])) {
         $params['custom_where'] = '';
     }
     if (!isset($params['custom_order_by'])) {
         $params['custom_order_by'] = '';
     }
     $main_query = $ret_array['select'] . $params['custom_select'] . $ret_array['from'] . $params['custom_from'] . $ret_array['where'] . $params['custom_where'] . $ret_array['order_by'] . $params['custom_order_by'];
     $_SESSION['export_where'] = $ret_array['where'];
     if ($limit < -1) {
         $result = $GLOBALS['db']->query($main_query);
     } else {
         if ($limit == -1) {
             $limit = $this->getLimit();
         }
         $offset = $this->getOffset();
//.........这里部分代码省略.........

 function create_new_list_query($order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = '', $return_array = false, $parentbean = null, $singleSelect = false)
 {
     $ret_array = parent::create_new_list_query($order_by, $where, $filter, $params, $show_deleted, $join_type, true, $parentbean, $singleSelect);
     // add selection clause to $ret:array['where']
     if ($return_array) {
         return $ret_array;
     }
     return $ret_array['select'] . $ret_array['from'] . $ret_array['where'] . $ret_array['order_by'];
 }

 public function create_new_list_query($order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = '', $return_array = false, $parentbean = null, $singleSelect = false, $ifListForExport = false)
 {
     if (isset($filter['resource_name']) && $filter['resource_name'] === true) {
         $filter['resource_id'] = true;
     }
     return parent::create_new_list_query($order_by, $where, $filter, $params, $show_deleted, $join_type, $return_array, $parentbean, $singleSelect, $ifListForExport);
 }

 public function create_new_list_query($order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = '', $return_array = false, $parentbean = null, $singleSelect = false, $ifListForExport = false)
 {
     if ($return_array) {
         return parent::create_new_list_query($order_by, $where, $filter, $params, $show_deleted, $join_type, $return_array, $parentbean, $singleSelect, $ifListForExport);
     }
     $custom_join = $this->getCustomJoin();
     $query = "SELECT " . $this->table_name . ".*, users.user_name as assigned_user_name\n";
     $query .= $custom_join['select'];
     $query .= " FROM emails\n";
     if ($where != "" && strpos($where, "contacts.first_name") > 0) {
         $query .= " LEFT JOIN emails_beans ON emails.id = emails_beans.email_id\n";
     }
     // We need to confirm that the user is a member of the team of the item.
     $this->addVisibilityFrom($query, array('where_condition' => true));
     $query .= " LEFT JOIN teams ON emails.team_id=teams.id";
     $query .= " LEFT JOIN users ON emails.assigned_user_id=users.id \n";
     if ($where != "" && strpos($where, "contacts.first_name") > 0) {
         $query .= " JOIN contacts ON contacts.id= emails_beans.bean_id AND emails_beans.bean_module='Contacts' and contacts.deleted=0 \n";
     }
     $query .= $custom_join['join'];
     if ($show_deleted == 0) {
         $where_auto = " emails.deleted=0 \n";
     } else {
         if ($show_deleted == 1) {
             $where_auto = " emails.deleted=1 \n";
         }
     }
     $this->addVisibilityWhere($where_auto, array('where_condition' => true));
     if ($where != "") {
         $query .= "WHERE {$where} AND " . $where_auto;
     } else {
         $query .= "WHERE " . $where_auto;
     }
     if ($order_by != "") {
         $query .= " ORDER BY {$order_by}";
     } else {
         $query .= " ORDER BY date_sent DESC";
     }
     return $query;
 }