public function addSseVisibilityFilter($engine, $filter) { if ($this->bean->bean_implements('ACL') && ACLController::requireOwner($this->bean->module_dir, 'list')) { if ($engine instanceof SugarSearchEngineElastic) { $filter->addMust($engine->getOwnerTermFilter()); } } return $filter; }
/** * builds up a delimited string for export * @param string type the bean-type to export * @param array records an array of records if coming directly from a query * @return string delimited string for export */ function export($type, $records = null, $members = false) { global $beanList; global $beanFiles; global $current_user; global $app_strings; global $app_list_strings; global $timedate; $contact_fields = array("id" => "Contact ID", "lead_source" => "Lead Source", "date_entered" => "Date Entered", "date_modified" => "Date Modified", "first_name" => "First Name", "last_name" => "Last Name", "salutation" => "Salutation", "birthdate" => "Lead Source", "do_not_call" => "Do Not Call", "email_opt_out" => "Email Opt Out", "title" => "Title", "department" => "Department", "birthdate" => "Birthdate", "do_not_call" => "Do Not Call", "phone_home" => "Phone (Home)", "phone_mobile" => "Phone (Mobile)", "phone_work" => "Phone (Work)", "phone_other" => "Phone (Other)", "phone_fax" => "Fax", "email1" => "Email", "email2" => "Email (Other)", "assistant" => "Assistant", "assistant_phone" => "Assistant Phone", "primary_address_street" => "Primary Address Street", "primary_address_city" => "Primary Address City", "primary_address_state" => "Primary Address State", "primary_address_postalcode" => "Primary Address Postalcode", "primary_address_country" => "Primary Address Country", "alt_address_street" => "Other Address Street", "alt_address_city" => "Other Address City", "alt_address_state" => "Other Address State", "alt_address_postalcode" => "Other Address Postalcode", "alt_address_country" => "Other Address Country", "description" => "Description"); $account_fields = array("id" => "Account ID", "name" => "Account Name", "website" => "Website", "industry" => "Industry", "account_type" => "Type", "ticker_symbol" => "Ticker Symbol", "employees" => "Employees", "ownership" => "Ownership", "phone_office" => "Phone", "phone_fax" => "Fax", "phone_alternate" => "Other Phone", "email1" => "Email", "email2" => "Other Email", "rating" => "Rating", "sic_code" => "SIC Code", "annual_revenue" => "Annual Revenue", "billing_address_street" => "Billing Address Street", "billing_address_city" => "Billing Address City", "billing_address_state" => "Billing Address State", "billing_address_postalcode" => "Billing Address Postalcode", "billing_address_country" => "Billing Address Country", "shipping_address_street" => "Shipping Address Street", "shipping_address_city" => "Shipping Address City", "shipping_address_state" => "Shipping Address State", "shipping_address_postalcode" => "Shipping Address Postalcode", "shipping_address_country" => "Shipping Address Country", "description" => "Description"); $focus = 0; $content = ''; $bean = $beanList[$type]; require_once $beanFiles[$bean]; $focus = new $bean(); $searchFields = array(); $db = DBManagerFactory::getInstance(); if ($records) { $records = explode(',', $records); $records = "'" . implode("','", $records) . "'"; $where = "{$focus->table_name}.id in ({$records})"; } elseif (isset($_REQUEST['all'])) { $where = ''; } else { if (!empty($_REQUEST['current_post'])) { $ret_array = generateSearchWhere($type, $_REQUEST['current_post']); $where = $ret_array['where']; $searchFields = $ret_array['searchFields']; } else { $where = ''; } } $order_by = ""; if ($focus->bean_implements('ACL')) { if (!ACLController::checkAccess($focus->module_dir, 'export', true)) { ACLController::displayNoAccess(); sugar_die(''); } if (ACLController::requireOwner($focus->module_dir, 'export')) { if (!empty($where)) { $where .= ' AND '; } $where .= $focus->getOwnerWhere($current_user->id); } } // Export entire list was broken because the where clause already has "where" in it // and when the query is built, it has a "where" as well, so the query was ill-formed. // Eliminating the "where" here so that the query can be constructed correctly. if ($members == true) { $query = $focus->create_export_members_query($records); } else { $beginWhere = substr(trim($where), 0, 5); if ($beginWhere == "where") { $where = substr(trim($where), 5, strlen($where)); } $ret_array = create_export_query_relate_link_patch($type, $searchFields, $where); if (!empty($ret_array['join'])) { $query = $focus->create_export_query($order_by, $ret_array['where'], $ret_array['join']); } else { $query = $focus->create_export_query($order_by, $ret_array['where']); } } $result = $db->query($query, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query); $fields_array = $db->getFieldsArray($result, true); // setup the "header" line with proper delimiters $header = implode("\"" . getDelimiter() . "\"", array_values($fields_array)); if ($members) { $header = str_replace('"ea_deleted"' . getDelimiter() . '"ear_deleted"' . getDelimiter() . '"primary_address"' . getDelimiter() . '', '', $header); } $header = "\"" . $header; $header .= "\"\r\n"; $content .= $header; $pre_id = ''; while ($val = $db->fetchByAssoc($result, -1, false)) { $new_arr = array(); if ($members) { if ($pre_id == $val['id']) { continue; } if ($val['ea_deleted'] == 1 || $val['ear_deleted'] == 1) { $val['primary_email_address'] = ''; } unset($val['ea_deleted']); unset($val['ear_deleted']); unset($val['primary_address']); } $pre_id = $val['id']; $vals = array_values($val); foreach ($vals as $key => $value) { //getting content values depending on their types $fieldType = $focus->field_name_map[$fields_array[$key]]['type']; if (isset($fieldType)) { switch ($fieldType) { //if our value is a currency field, then apply the users locale case 'currency': require_once 'modules/Currencies/Currency.php'; $value = currency_format_number($value, array('currency_symbol' => false)); break; //if our value is a datetime field, then apply the users locale //if our value is a datetime field, then apply the users locale case 'datetime': case 'datetimecombo': $value = $timedate->to_display_date_time($value); $value = preg_replace('/([pm|PM|am|AM]+)/', ' \\1', $value); break; //kbrill Bug #16296 //kbrill Bug #16296 case 'date': $value = $timedate->to_display_date($value, false); break; // Bug 32463 - Properly have multienum field translated into something useful for the client // Bug 32463 - Properly have multienum field translated into something useful for the client case 'multienum': $value = str_replace("^", "", $value); if (isset($focus->field_name_map[$fields_array[$key]]['options']) && isset($app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']])) { $valueArray = explode(",", $value); foreach ($valueArray as $multikey => $multivalue) { if (isset($app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']][$multivalue])) { $valueArray[$multikey] = $app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']][$multivalue]; } } $value = implode(",", $valueArray); } break; } } array_push($new_arr, preg_replace("/\"/", "\"\"", $value)); } $line = implode("\"" . getDelimiter() . "\"", $new_arr); $line = "\"" . $line; $line .= "\"\r\n"; $content .= $line; } return $content; }
function findEmailFromBeanIds($beanIds, $beanType, $whereArr) { global $current_user; $q = ''; $whereAdd = ""; $relatedIDs = ''; if ($beanIds != '') { foreach ($beanIds as $key => $value) { $beanIds[$key] = '\'' . $value . '\''; } $relatedIDs = implode(',', $beanIds); } if ($beanType == 'accounts') { if (isset($whereArr['first_name'])) { $whereArr['name'] = $whereArr['first_name']; } unset($whereArr['last_name']); unset($whereArr['first_name']); } foreach ($whereArr as $column => $clause) { if (!empty($whereAdd)) { $whereAdd .= " OR "; } $clause = $current_user->db->quote($clause); $whereAdd .= "{$column} LIKE '{$clause}%'"; } $table = $beanType; $module = ucfirst($table); $class = substr($module, 0, strlen($module) - 1); require_once "modules/{$module}/{$class}.php"; $person = new $class(); if ($person->ACLAccess('list')) { if ($relatedIDs != '') { $where = "({$table}.deleted = 0 AND eabr.primary_address = 1 AND {$table}.id in ({$relatedIDs}))"; } else { $where = "({$table}.deleted = 0 AND eabr.primary_address = 1)"; } if (ACLController::requireOwner($module, 'list')) { $where = $where . " AND ({$table}.assigned_user_id = '{$current_user->id}')"; } // if if (!empty($whereAdd)) { $where .= " AND ({$whereAdd})"; } if ($beanType === 'accounts') { $t = "SELECT {$table}.id, '' first_name, {$table}.name last_name, eabr.primary_address, ea.email_address, '{$module}' module "; } else { $t = "SELECT {$table}.id, {$table}.first_name, {$table}.last_name, eabr.primary_address, ea.email_address, '{$module}' module "; } $t .= "FROM {$table} "; $t .= "JOIN email_addr_bean_rel eabr ON ({$table}.id = eabr.bean_id and eabr.deleted=0) "; $t .= "JOIN email_addresses ea ON (eabr.email_address_id = ea.id) "; $t .= " WHERE {$where}"; /* BEGIN - SECURITY GROUPS */ //this function may not even be used anymore. Seems like findEmailFromBeanIds is preferred now if ($person->bean_implements('ACL') && ACLController::requireSecurityGroup($module, 'list')) { require_once 'modules/SecurityGroups/SecurityGroup.php'; global $current_user; $owner_where = $person->getOwnerWhere($current_user->id); $group_where = SecurityGroup::getGroupWhere($table, $module, $current_user->id); $t .= " AND (" . $owner_where . " or " . $group_where . ") "; } /* END - SECURITY GROUPS */ } // if return $t; }
/** * @return array */ public function create_where_statement() { $where_clauses = array(); foreach ($this->field_search_params as $merge_field => $vDefArray) { if (isset($vDefArray['source']) && $vDefArray['source'] == 'custom_fields') { $table_name = $this->merge_bean->table_name . '_cstm'; } else { $table_name = $this->merge_bean->table_name; } //Should move these if's into a central location for extensibility and addition for other search filters //Must do the same for pulling values in js dropdown if (isset($vDefArray['search_type']) && $vDefArray['search_type'] == 'like') { if ($merge_field != 'email1' && $merge_field != 'email2' && $merge_field != 'release_name') { if ($vDefArray['value'] != '') { array_push($where_clauses, $table_name . '.' . $merge_field . " LIKE '%" . $GLOBALS['db']->quote($vDefArray['value']) . "%'"); } } elseif ($merge_field == 'release_name') { if (isset($vDefArray['value'])) { $in = $this->release_name_query('like', $vDefArray['value']); array_push($where_clauses, $table_name . ".found_in_release IN ({$in})"); } } else { $query = $this->email_addresses_query($table_name, $this->merge_module, $this->merge_bean->id); $query .= " AND ea.email_address LIKE '%" . $GLOBALS['db']->quote($vDefArray['value']) . "%')"; $where_clauses[] = $query; } } elseif (isset($vDefArray['search_type']) && $vDefArray['search_type'] == 'start') { if ($merge_field != 'email1' && $merge_field != 'email2' && $merge_field != 'release_name') { array_push($where_clauses, $table_name . '.' . $merge_field . " LIKE '" . $GLOBALS['db']->quote($vDefArray['value']) . "%'"); } elseif ($merge_field == 'release_name') { if (isset($vDefArray['value'])) { $in = $this->release_name_query('start', $vDefArray['value']); array_push($where_clauses, $table_name . ".found_in_release IN ({$in})"); } } else { $query = $this->email_addresses_query($table_name, $this->merge_module, $this->merge_bean->id); $query .= " AND ea.email_address LIKE '" . $GLOBALS['db']->quote($vDefArray['value']) . "%')"; $where_clauses[] = $query; } } else { if ($merge_field != 'email1' && $merge_field != 'email2' && $merge_field != 'release_name') { array_push($where_clauses, $table_name . '.' . $merge_field . "='" . $GLOBALS['db']->quote($vDefArray['value']) . "'"); } elseif ($merge_field == 'release_name') { if (isset($vDefArray['value'])) { $in = $this->release_name_query('exact', $vDefArray['value']); array_push($where_clauses, $table_name . ".found_in_release IN ({$in})"); } } else { $query = $this->email_addresses_query($table_name, $this->merge_module, $this->merge_bean->id); $query .= " AND ea.email_address = '" . $GLOBALS['db']->quote($vDefArray['value']) . "')"; $where_clauses[] = $query; } } } // Add ACL Check if ($this->merge_bean->bean_implements('ACL') && ACLController::requireOwner($this->merge_bean->module_dir, 'delete')) { global $current_user; $where_clauses[] = $this->merge_bean->getOwnerWhere($current_user->id); } array_push($where_clauses, $this->merge_bean->table_name . ".id !='" . $GLOBALS['db']->quote($this->merge_bean->id) . "'"); return $where_clauses; }
function processSugarBean($html_varName, $seed, $offset) { global $row_count, $sugar_config; global $next_offset; global $previous_offset; global $list_view_row_count; global $current_offset; if (!empty($sugar_config['disable_vcr'])) { $seed->retrieve($_REQUEST['record']); return $seed; } $isfirstview = 0; $nav_history_set = false; $nav_history_array = array(); $nav_offset = ''; $nav_ids_visited = array(); $nav_stamp = ''; //get the session variable DETAIL_NAV_HISTORY, //the format of the variable stamp,offset, array of IDs visited. $nav_history = $this->getLocalSessionVariable($html_varName, "DETAIL_NAV_HISTORY"); if (!empty($nav_history)) { $nav_history_set = true; $nav_history_array = explode(":", $nav_history); $nav_stamp = $nav_history_array[0]; $nav_offset = $nav_history_array[1]; eval("\$nav_ids_visited= " . $nav_history_array[2] . ";"); } //from list offset is there but $bNavHistorySet is false. //from next,previous,start and end buttons offset and $bNavHistorySet is true. //from tracker offset is not there but $bNavHistorySet may or may not exist. if (isset($_REQUEST['offset']) && !empty($_REQUEST['offset'])) { //get offset values. $offset = $_REQUEST['offset']; if ($offset < 0) { $offset = 0; } //if the stamp has changed, ignore the offset and navigate to the record. //use case, search, navigate to detail, copy URL, search again, paste URL. if (!$this->isRequestFromListView($html_varName)) { $result = $seed->retrieve($_REQUEST['record']); return $result; } if ($nav_history_set) { if (isset($nav_ids_visited[$offset])) { unset($nav_ids_visited[$offset]); } } } else { if ($nav_history_set) { //try to locate the ID in the nav_history array. $key = array_search($_REQUEST['record'], $nav_ids_visited); if ($key === false) { //do not show the VCR buttons. $result = $seed->retrieve($_REQUEST['record']); return $result; } $offset = $key; $_REQUEST['offset'] = $offset; $_GET['offset'] = $offset; $_POST['offset'] = $offset; $_REQUEST['stamp'] = $nav_stamp; $_GET['stamp'] = $nav_stamp; $_POST['stamp'] = $nav_stamp; if (isset($nav_ids_visited[$offset])) { unset($nav_ids_visited[$offset]); } } else { if (!empty($seed->id)) { return $seed; } $result = $seed->retrieve($_REQUEST['record']); return $result; } } //Check if this is the first time we have viewed this record $var = $this->getLocalSessionVariable($html_varName, "IS_FIRST_VIEW"); if (!isset($var) || !$var) { $isFirstView = true; } else { $isFirstView = false; } //indicate that this is not the first time anymore $this->setLocalSessionVariable($html_varName, "IS_FIRST_VIEW", false); // All 3 databases require this because the limit query does a > db_offset comparison. $db_offset = $offset - 1; $this->populateQueryWhere($isFirstView, $html_varName); if (ACLController::requireOwner($seed->module_dir, 'view')) { global $current_user; $seed->getOwnerWhere($current_user->id); if (!empty($this->query_where)) { $this->query_where .= ' AND '; } $this->query_where .= $seed->getOwnerWhere($current_user->id); } /* BEGIN - SECURITY GROUPS */ if (ACLController::requireSecurityGroup($seed->module_dir, 'view')) { require_once 'modules/SecurityGroups/SecurityGroup.php'; global $current_user; $owner_where = $seed->getOwnerWhere($current_user->id); $group_where = SecurityGroup::getGroupWhere($seed->table_name, $seed->module_dir, $current_user->id); if (empty($this->query_where)) { $this->query_where = " (" . $owner_where . " or " . $group_where . ")"; } else { $this->query_where .= " AND (" . $owner_where . " or " . $group_where . ")"; } } /* END - SECURITY GROUPS */ $order = $this->getLocalSessionVariable($seed->module_dir . '2_' . $html_varName, "ORDER_BY"); $orderBy = ''; if (!empty($order['orderBy'])) { $orderBy = $order['orderBy']; } if (!empty($orderBy) && !empty($order['direction'])) { $orderBy .= ' ' . $order['direction']; } $this->query_orderby = $seed->process_order_by($orderBy, null); $current_offset = $_REQUEST['offset'] - 1; $response = $seed->process_detail_query(SugarVCR::retrieve($seed->module_dir), 0, -1, -1, '', $current_offset); //$response = $seed->get_detail(, $this->query_where, $db_offset); $object = $response['bean']; $row_count = $response['row_count']; $next_offset = $response['next_offset']; $previous_offset = $response['previous_offset']; $list_view_row_count = $row_count; $this->setListViewRowCount($row_count); //if the retrieved id is not same as the request ID then hide the VCR buttons. if (empty($object->id)) { $this->no_record_found = true; } if (empty($_REQUEST['InDetailNav']) and strcmp($_REQUEST['record'], $object->id) != 0) { $this->offset_key_mismatch = true; } if ($this->no_record_found or $this->offset_key_mismatch) { if ($nav_history_set) { $this->return_to_list_only = true; } $result = $seed->retrieve($_REQUEST['record']); return $result; } //update the request with correct value for the record attribute. //need only when using the VCR buttons. This is a workaround need to fix the values //set in the VCR links. $_REQUEST['record'] = $object->id; $_GET['record'] = $object->id; $_POST['record'] = $object->id; //set nav_history. if (empty($nav_stamp)) { $nav_stamp = $_GET['stamp']; } if (empty($nav_offset)) { $nav_offset = $offset; } //store a maximum of 20 entries in the nav_ids_visited array. //remove the oldest entry when this limit is reached. if (count($nav_ids_visited) >= 20) { reset($nav_ids_visited); unset($nav_ids_visited[key($nav_ids_visited)]); } $nav_ids_visited[$offset] = $object->id; $nav_history = sprintf("%s:%s:%s", $nav_stamp, $nav_offset, var_export($nav_ids_visited, true)); $this->setLocalSessionVariable($html_varName, "DETAIL_NAV_HISTORY", $nav_history); return $object; }
/** * Generates a UNION query to get one list of users, contacts, leads, and * prospects; used specifically for the addressBook */ function _getPeopleUnionQuery($whereArr, $person) { global $current_user, $app_strings; global $db; if (!isset($person) || $person === 'LBL_DROPDOWN_LIST_ALL') { $peopleTables = array("users", "contacts", "leads", "prospects"); } else { $peopleTables = array($person); } $q = ''; $whereAdd = ""; foreach ($whereArr as $column => $clause) { if (!empty($whereAdd)) { $whereAdd .= " AND "; } $clause = $current_user->db->helper->escape_quote($clause); $whereAdd .= "{$column} LIKE '{$clause}%'"; } foreach ($peopleTables as $table) { $module = ucfirst($table); $class = substr($module, 0, strlen($module) - 1); require_once "modules/{$module}/{$class}.php"; $person = new $class(); if (!$person->ACLAccess('list')) { continue; } // if $where = "({$table}.deleted = 0 AND eabr.primary_address = 1 AND {$table}.id <> '{$current_user->id}')"; if (ACLController::requireOwner($module, 'list')) { $where = $where . " AND ({$table}.assigned_user_id = '{$current_user->id}')"; } // if if (!empty($whereAdd)) { $where .= " AND ({$whereAdd})"; } $t = "SELECT {$table}.id, {$table}.first_name, {$table}.last_name, eabr.primary_address, ea.email_address, '{$module}' module "; $t .= "FROM {$table} "; $t .= "JOIN email_addr_bean_rel eabr ON ({$table}.id = eabr.bean_id and eabr.deleted=0) "; $t .= "JOIN email_addresses ea ON (eabr.email_address_id = ea.id) "; $t .= " WHERE {$where}"; //_pp($t); //$t .= " LIMIT 10"; if (!empty($q)) { $q .= "\n UNION ALL \n"; } $q .= "({$t})"; } $countq = "SELECT count(people.id) c from ({$q}) people"; $q .= "ORDER BY last_name"; //_ppd($q); return array('query' => $q, 'countQuery' => $countq); }
function build_report_access_query(SugarBean $module, $alias) { $module->table_name = $alias; $where = ''; if ($module->bean_implements('ACL') && ACLController::requireOwner($module->module_dir, 'list')) { global $current_user; $owner_where = $module->getOwnerWhere($current_user->id); $where = ' AND ' . $owner_where; } if (file_exists('modules/SecurityGroups/SecurityGroup.php')) { /* BEGIN - SECURITY GROUPS */ if ($module->bean_implements('ACL') && ACLController::requireSecurityGroup($module->module_dir, 'list')) { require_once 'modules/SecurityGroups/SecurityGroup.php'; global $current_user; $owner_where = $module->getOwnerWhere($current_user->id); $group_where = SecurityGroup::getGroupWhere($alias, $module->module_dir, $current_user->id); if (!empty($owner_where)) { $where .= " AND (" . $owner_where . " or " . $group_where . ") "; } else { $where .= ' AND ' . $group_where; } } /* END - SECURITY GROUPS */ } return $where; }
/** * builds up a delimited string for export * @param string type the bean-type to export * @param array records an array of records if coming directly from a query * @return string delimited string for export */ function export($type, $records = null) { global $beanList; global $beanFiles; global $current_user; global $app_strings; $contact_fields = array("id" => "Contact ID", "lead_source" => "Lead Source", "date_entered" => "Date Entered", "date_modified" => "Date Modified", "first_name" => "First Name", "last_name" => "Last Name", "salutation" => "Salutation", "birthdate" => "Lead Source", "do_not_call" => "Do Not Call", "email_opt_out" => "Email Opt Out", "title" => "Title", "department" => "Department", "birthdate" => "Birthdate", "do_not_call" => "Do Not Call", "phone_home" => "Phone (Home)", "phone_mobile" => "Phone (Mobile)", "phone_work" => "Phone (Work)", "phone_other" => "Phone (Other)", "phone_fax" => "Fax", "email1" => "Email", "email2" => "Email (Other)", "assistant" => "Assistant", "assistant_phone" => "Assistant Phone", "primary_address_street" => "Primary Address Street", "primary_address_city" => "Primary Address City", "primary_address_state" => "Primary Address State", "primary_address_postalcode" => "Primary Address Postalcode", "primary_address_country" => "Primary Address Country", "alt_address_street" => "Other Address Street", "alt_address_city" => "Other Address City", "alt_address_state" => "Other Address State", "alt_address_postalcode" => "Other Address Postalcode", "alt_address_country" => "Other Address Country", "description" => "Description"); $account_fields = array("id" => "Account ID", "name" => "Account Name", "website" => "Website", "industry" => "Industry", "account_type" => "Type", "ticker_symbol" => "Ticker Symbol", "employees" => "Employees", "ownership" => "Ownership", "phone_office" => "Phone", "phone_fax" => "Fax", "phone_alternate" => "Other Phone", "email1" => "Email", "email2" => "Other Email", "rating" => "Rating", "sic_code" => "SIC Code", "annual_revenue" => "Annual Revenue", "billing_address_street" => "Billing Address Street", "billing_address_city" => "Billing Address City", "billing_address_state" => "Billing Address State", "billing_address_postalcode" => "Billing Address Postalcode", "billing_address_country" => "Billing Address Country", "shipping_address_street" => "Shipping Address Street", "shipping_address_city" => "Shipping Address City", "shipping_address_state" => "Shipping Address State", "shipping_address_postalcode" => "Shipping Address Postalcode", "shipping_address_country" => "Shipping Address Country", "description" => "Description"); $focus = 0; $content = ''; $bean = $beanList[$type]; require_once $beanFiles[$bean]; $focus = new $bean(); $db = PearDatabase::getInstance(); if ($records) { $records = explode(',', $records); $records = "'" . implode("','", $records) . "'"; $where = "{$focus->table_name}.id in ({$records})"; } elseif (isset($_REQUEST['all'])) { $where = ''; } else { if (isset($_SESSION['export_where']) && !empty($_SESSION['export_where'])) { // bug 4679 $where = $_SESSION['export_where']; } else { $where = ''; } } $order_by = ""; if ($focus->bean_implements('ACL')) { if (!ACLController::checkAccess($focus->module_dir, 'export', true)) { ACLController::displayNoAccess(); sugar_die(''); } if (ACLController::requireOwner($focus->module_dir, 'export')) { if (!empty($where)) { $where .= ' AND '; } $where .= $focus->getOwnerWhere($current_user->id); } } // Export entire list was broken because the where clause already has "where" in it // and when the query is built, it has a "where" as well, so the query was ill-formed. // Eliminating the "where" here so that the query can be constructed correctly. $beginWhere = substr(trim($where), 0, 5); if ($beginWhere == "where") { $where = substr(trim($where), 5, strlen($where)); } $query = $focus->create_export_query($order_by, $where); $result = $db->query($query, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query); $fields_array = $db->getFieldsArray($result); // setup the "header" line with quotation marks $header = implode("\"" . getDelimiter() . "\"", array_values($fields_array)); $header = "\"" . $header; $header .= "\"\r\n"; $content .= $header; while ($val = $db->fetchByAssoc($result, -1, false)) { $new_arr = array(); foreach (array_values($val) as $value) { array_push($new_arr, preg_replace("/\"/", "\"\"", $value)); } $line = implode("\"" . getDelimiter() . "\"", $new_arr); $line = "\"" . $line; $line .= "\"\r\n"; $content .= $line; } return $content; }
function findEmailFromBeanIds($beanIds, $beanType, $whereArr) { global $current_user; $q = ''; $whereAdd = ""; $relatedIDs = ''; if ($beanIds != '') { foreach ($beanIds as $key => $value) { $beanIds[$key] = '\'' . $value . '\''; } $relatedIDs = implode(',', $beanIds); } if ($beanType == 'accounts') { if (isset($whereArr['first_name'])) { $whereArr['name'] = $whereArr['first_name']; } unset($whereArr['last_name']); unset($whereArr['first_name']); } foreach ($whereArr as $column => $clause) { if (!empty($whereAdd)) { $whereAdd .= " OR "; } $clause = $current_user->db->quote($clause); $whereAdd .= "{$column} LIKE '{$clause}%'"; } $table = $beanType; $module = ucfirst($table); $class = substr($module, 0, strlen($module) - 1); require_once "modules/{$module}/{$class}.php"; $person = new $class(); if ($person->ACLAccess('list')) { if ($relatedIDs != '') { $where = "({$table}.deleted = 0 AND eabr.primary_address = 1 AND {$table}.id in ({$relatedIDs}))"; } else { $where = "({$table}.deleted = 0 AND eabr.primary_address = 1)"; } if (ACLController::requireOwner($module, 'list')) { $where = $where . " AND ({$table}.assigned_user_id = '{$current_user->id}')"; } // if if (!empty($whereAdd)) { $where .= " AND ({$whereAdd})"; } if ($beanType === 'accounts') { $t = "SELECT {$table}.id, '' first_name, {$table}.name last_name, eabr.primary_address, ea.email_address, '{$module}' module "; } else { $t = "SELECT {$table}.id, {$table}.first_name, {$table}.last_name, eabr.primary_address, ea.email_address, '{$module}' module "; } $t .= "FROM {$table} "; $t .= "JOIN email_addr_bean_rel eabr ON ({$table}.id = eabr.bean_id and eabr.deleted=0) "; $t .= "JOIN email_addresses ea ON (eabr.email_address_id = ea.id) "; $t .= " WHERE {$where}"; } // if return $t; }
/** * builds up a delimited string for export * @param boolean args api argument * @param boolean sample whether it's sample export * @return string delimited string for export */ function exportFromApi($args, $sample = false) { global $current_user; global $app_strings; $sampleRecordNum = 5; $type = clean_string($args['module']); require_once 'include/RecordListFactory.php'; $recordList = RecordListFactory::getRecordList($args['record_list_id']); if (empty($recordList)) { throw new SugarApiExceptionNotFound(); } $records = $recordList['records']; $members = isset($args['members']) ? $args['members'] : false; //Array of fields that should not be exported, and are only used for logic $remove_from_members = array("ea_deleted", "ear_deleted", "primary_address"); $focus = BeanFactory::newBean($type); $searchFields = array(); $db = DBManagerFactory::getInstance(); if ($records) { // we take an array, but we'll make an exception for one record. if (!is_array($records)) { $records = array($records); } $records = "'" . implode("','", $records) . "'"; $where = "{$focus->table_name}.id in ({$records})"; } elseif (isset($args['all'])) { $where = ''; } else { // use filter to get data instead of building a sql if (!empty($args['filter'])) { $content = getExportContentFromFilter($args, $remove_from_members, $focus, $members); return $content; } else { $where = ''; } } if ($focus->bean_implements('ACL')) { if (ACLController::requireOwner($focus->module_dir, 'export')) { if (!empty($where)) { $where .= ' AND '; } $where .= $focus->getOwnerWhere($current_user->id); } } if ($focus->bean_implements('ACL')) { $focus->addVisibilityWhere($where); } // Export entire list was broken because the where clause already has "where" in it // and when the query is built, it has a "where" as well, so the query was ill-formed. // Eliminating the "where" here so that the query can be constructed correctly. if ($members == true) { $query = $focus->create_export_members_query($records); } else { $beginWhere = substr(trim($where), 0, 5); if ($beginWhere == "where") { $where = substr(trim($where), 5, strlen($where)); } $query = $focus->create_export_query("", $where); } $result = null; if ($sample) { $result = $db->limitQuery($query, 0, $sampleRecordNum, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query); $sample = $focus->_get_num_rows_in_query($query) < 1; } else { $result = $db->query($query, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query); } $content = getExportContentFromResult($focus, $result, $members, $remove_from_members, $sample); return $content; }
function build_from_string() { global $db, $app_list_strings, $beanList, $beanFiles, $current_user; // Create a root GUID $this->rootGuid = randomstring(); $this->joinSegments = array(); $this->maxDepth = 0; $kOrgUnits = false; //check if we do the Org Check if (file_exists('modules/KOrgObjects/KOrgObject.php') && $GLOBALS['sugarconfig']['orgmanaged']) { require_once 'modules/KOrgObjects/KOrgObject.php'; $thisKOrgObject = new KOrgObject(); $kOrgUnits = true; } /* * Build the array for the joins based on the various Path we have */ foreach ($this->tablePath as $thisPath => $thisPathJoinType) { // Process backcutting until we have found the node going upwards // in the segments array or we are on the root segment // (when no '::' can be found) if (substr_count($thisPath, '::') > $this->maxDepth) { $this->maxDepth = substr_count($thisPath, '::'); } while (strpos($thisPath, '::') && !isset($this->joinSegments[$thisPath])) { // add the segment to the segments table $this->joinSegments[$thisPath] = array('alias' => randomstring(), 'linkalias' => randomstring(), 'level' => substr_count($thisPath, '::'), 'jointype' => $thisPathJoinType); // find last occurence of '::' in the string and cut off there $thisPath = substr($thisPath, strrpos($thisPath, "::")); } } // Get the main Table we select from $this->fromString = 'FROM ' . $this->get_table_for_module($this->root_module) . ' ' . $this->rootGuid; // check if this is an array so we need to add joins ... // add an entry for the root Object ... // needed as reference for the GUID $this->joinSegments['root:' . $this->root_module] = array('alias' => $this->rootGuid, 'level' => 0); // get ther root Object require_once $beanFiles[$beanList[$this->root_module]]; $this->joinSegments['root:' . $this->root_module]['object'] = new $beanList[$this->root_module](); // check for Custom Fields if ($this->joinSegments['root:' . $this->root_module]['object']->hasCustomFields()) { $this->joinSegments['root:' . $this->root_module]['customjoin'] = randomstring(); $this->fromString .= ' LEFT JOIN ' . $this->get_table_for_module($this->root_module) . '_cstm as ' . $this->joinSegments['root:' . $this->root_module]['customjoin'] . ' ON ' . $this->rootGuid . '.id = ' . $this->joinSegments['root:' . $this->root_module]['customjoin'] . '.id_c'; } // changed so we spport teams in Pro if ($this->authChecklevel != 'none') { switch ($GLOBALS['sugar_config']['KReports']['authCheck']) { case 'KOrgObjects': $this->fromString .= $thisKOrgObject->getOrgunitJoin($this->joinSegments['root:' . $this->root_module]['object']->table_name, $this->joinSegments['root:' . $this->root_module]['object']->object_name, $this->rootGuid, '1'); break; case 'KAuthObjects': $selectArray = array('where' => '', 'from' => '', 'select' => ''); $GLOBALS['KAuthAccessController']->addAuthAccessToListArray($selectArray, $this->joinSegments['root:' . $this->root_module]['object'], $this->joinSegments['root:' . $this->root_module]['alias'], true); if (!empty($selectArray['where'])) { if (empty($this->whereString)) { $this->whereString = " " . $selectArray['where'] . " "; } else { $this->whereString .= " AND " . $selectArray['where'] . " "; } } if (!empty($selectArray['join'])) { $this->fromString .= ' ' . $selectArray['join'] . ' '; } break; case 'PRO': $this->fromString .= ' '; $this->joinSegments['root:' . $this->root_module]['object']->add_team_security_where_clause($this->fromString, $this->rootGuid); break; //2013-03-26 Bug#460 Typo changed //2013-03-26 Bug#460 Typo changed case 'SecurityGroups': if ($this->joinSegments['root:' . $this->root_module]['object']->bean_implements('ACL') && ACLController::requireSecurityGroup($this->joinSegments['root:' . $this->root_module]['object']->module_dir, 'list')) { require_once 'modules/SecurityGroups/SecurityGroup.php'; global $current_user; $owner_where = str_replace($this->joinSegments['root:' . $this->root_module]['object']->table_name, $this->rootGuid, $this->joinSegments['root:' . $this->root_module]['object']->getOwnerWhere($current_user->id)); $group_where = SecurityGroup::getGroupWhere($this->rootGuid, $this->joinSegments['root:' . $this->root_module]['object']->module_dir, $current_user->id); if (!empty($owner_where)) { if (empty($this->whereString)) { $this->whereString = " (" . $owner_where . " or " . $group_where . ") "; } else { $this->whereString .= " AND (" . $owner_where . " or " . $group_where . ") "; } } else { $this->whereString .= ' AND ' . $group_where; } } break; } } // Index to iterate through the join table building the joins // from the root object outward going $levelCounter = 1; if (is_array($this->joinSegments)) { while ($levelCounter <= $this->maxDepth) { // set the array back to the first element in the array reset($this->joinSegments); foreach ($this->joinSegments as $thisPath => $thisPathDetails) { // process only entries for the respective levels if ($thisPathDetails['level'] == $levelCounter) { // get the last enrty and the one before and the relevant arrays $rightPath = substr($thisPath, strrpos($thisPath, "::") + 2, strlen($thisPath)); $leftPath = substr($thisPath, 0, strrpos($thisPath, "::")); // explode into the relevant arrays $rightArray = explode(':', $rightPath); $leftArray = explode(':', $leftPath); // 2011-07-21 add check for audit records if ($rightArray[2] == 'audit') { //handle audit link $this->fromString .= $thisPathJoinType . $this->joinSegments[$leftPath]['object']->table_name . '_audit ' . $this->joinSegments[$thisPath]['alias'] . ' ON ' . $this->joinSegments[$thisPath]['alias'] . '.parent_id = ' . $this->joinSegments[$leftPath]['alias'] . '.id'; } elseif ($rightArray[0] == 'relationship') { // set alias for the path to the linkalias of the connected bean $this->joinSegments[$thisPath]['alias'] = $this->joinSegments[$leftPath]['linkalias']; } elseif ($rightArray[0] == 'relate') { //left Path Object must be set since we process from the top if (!$this->joinSegments[$leftPath]['object'] instanceof $beanList[$rightArray[1]]) { die('fatal Error in Join'); } // load the module on the right hand side require_once $beanFiles[$beanList[$this->joinSegments[$leftPath]['object']->field_defs[$rightArray[2]]['module']]]; $this->joinSegments[$thisPath]['object'] = new $beanList[$this->joinSegments[$leftPath]['object']->field_defs[$rightArray[2]]['module']](); // join on the id = relate id .. on _cstm if custom field .. on main if regular $this->fromString .= ' ' . $thisPathDetails['jointype'] . ' ' . $this->joinSegments[$thisPath]['object']->table_name . ' AS ' . $this->joinSegments[$thisPath]['alias'] . ' ON ' . $this->joinSegments[$thisPath]['alias'] . '.id=' . ($this->joinSegments[$leftPath]['object']->field_defs[$this->joinSegments[$leftPath]['object']->field_defs[$rightArray[2]]['id_name']]['source'] == 'custom_fields' ? $this->joinSegments[$leftPath]['customjoin'] : $this->joinSegments[$leftPath]['alias']) . '.' . $this->joinSegments[$leftPath]['object']->field_defs[$rightArray[2]]['id_name'] . ' '; // check for Custom Fields if ($this->joinSegments[$thisPath]['object']->hasCustomFields()) { $this->joinSegments[$thisPath]['customjoin'] = randomstring(); $this->fromString .= ' LEFT JOIN ' . $this->joinSegments[$thisPath]['object']->table_name . '_cstm as ' . $this->joinSegments[$thisPath]['customjoin'] . ' ON ' . $this->joinSegments[$thisPath]['alias'] . '.id = ' . $this->joinSegments[$thisPath]['customjoin'] . '.id_c'; } } else { //left Path Object must be set since we process from the top if (!$this->joinSegments[$leftPath]['object'] instanceof $beanList[$rightArray[1]]) { $GLOBALS['log']->error('KReporter: fatal error in join with left path ' . $thisPath); die('fatal Error in Join ' . $thisPath); } // load the relationship .. resp link $this->joinSegments[$leftPath]['object']->load_relationship($rightArray[2]); // set aliases for left and right .. will be processed properly anyway in the build of the link // ... funny enough so //2011-12-29 check if we have a jointpye if ($thisPathDetails['jointype'] != '') { //2011-12-29 see if the relationship vuilds on a custom field if (isset($this->joinSegments[$leftPath]['object']->field_name_map[$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->_relationship->rhs_key]['source']) && ($this->joinSegments[$leftPath]['object']->field_name_map[$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->_relationship->rhs_key]['source'] == 'custom_fields' || $this->joinSegments[$leftPath]['object']->field_name_map[$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->_relationship->lhs_key]['source'] == 'custom_fields')) { $join_params = array('join_type' => $thisPathDetails['jointype'], 'right_join_table_alias' => $this->joinSegments[$leftPath]['customjoin'], 'left_join_table_alias' => $this->joinSegments[$leftPath]['customjoin'], 'join_table_link_alias' => $this->joinSegments[$thisPath]['linkalias'], 'join_table_alias' => $this->joinSegments[$thisPath]['alias']); } else { $join_params = array('join_type' => $thisPathDetails['jointype'], 'right_join_table_alias' => $this->joinSegments[$leftPath]['alias'], 'left_join_table_alias' => $this->joinSegments[$leftPath]['alias'], 'join_table_link_alias' => $this->joinSegments[$thisPath]['linkalias'], 'join_table_alias' => $this->joinSegments[$thisPath]['alias']); } //2010-09-09 Bug to handle left side join relationship if (isset($this->joinSegments[$leftPath]['object']->field_defs[$rightArray[2]]['side']) && $this->joinSegments[$leftPath]['object']->field_defs[$rightArray[2]]['side'] == 'left' && !$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->_swap_sides) { $this->joinSegments[$leftPath]['object']->{$rightArray}[2]->_swap_sides = true; } $linkJoin = $this->joinSegments[$leftPath]['object']->{$rightArray}[2]->getJoin($join_params); $this->fromString .= ' ' . $linkJoin; } // load the module on the right hand side require_once $beanFiles[$beanList[$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->getRelatedModuleName()]]; $this->joinSegments[$thisPath]['object'] = new $beanList[$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->getRelatedModuleName()](); //bugfix 2010-08-19, respect ACL role access for owner reuqired in select if ($this->joinSegments[$leftPath]['object']->bean_implements('ACL') && ACLController::requireOwner($this->joinSegments[$leftPath]['object']->module_dir, 'list')) { //2013-02-22 missing check if we have a wherestring at all if ($this->whereString != '') { $this->whereString .= ' AND '; } $this->whereString .= $this->joinSegments[$leftPath]['alias'] . '.assigned_user_id=\'' . $current_user->id . '\''; } // check for Custom Fields if ($this->joinSegments[$thisPath]['object']->hasCustomFields()) { $this->joinSegments[$thisPath]['customjoin'] = randomstring(); $this->fromString .= ' LEFT JOIN ' . $this->joinSegments[$thisPath]['object']->table_name . '_cstm as ' . $this->joinSegments[$thisPath]['customjoin'] . ' ON ' . $this->joinSegments[$thisPath]['alias'] . '.id = ' . $this->joinSegments[$thisPath]['customjoin'] . '.id_c'; } // append join for Orgobjects if Object is OrgManaged if ($this->authChecklevel != 'none' && $this->authChecklevel != 'top') { switch ($GLOBALS['sugar_config']['KReports']['authCheck']) { case 'KOrgObjects': $this->fromString .= $thisKOrgObject->getOrgunitJoin($this->joinSegments[$thisPath]['object']->table_name, $this->joinSegments[$thisPath]['object']->object_name, $this->joinSegments[$thisPath]['alias'], '1'); break; case 'KAuthObjects': $selectArray = array('where' => '', 'from' => '', 'select' => ''); $GLOBALS['KAuthAccessController']->addAuthAccessToListArray($selectArray, $this->joinSegments[$thisPath]['object'], $this->joinSegments[$thisPath]['alias'], true); if (!empty($selectArray['where'])) { if (empty($this->whereString)) { $this->whereString = " " . $selectArray['where'] . " "; } else { $this->whereString .= " AND " . $selectArray['where'] . " "; } } if (!empty($selectArray['join'])) { $this->fromString .= ' ' . $selectArray['join'] . ' '; } break; case 'PRO': $this->fromString .= ' '; $this->joinSegments[$thisPath]['object']->add_team_security_where_clause($this->fromString, $this->joinSegments[$thisPath]['alias']); break; //2013-03-26 Bug#460 Typo changed //2013-03-26 Bug#460 Typo changed case 'SecurityGroups': if ($this->joinSegments[$thisPath]['object']->bean_implements('ACL') && ACLController::requireSecurityGroup($this->joinSegments[$thisPath]['object']->module_dir, 'list')) { require_once 'modules/SecurityGroups/SecurityGroup.php'; global $current_user; $owner_where = str_replace($this->joinSegments[$thisPath]['object']->table_name, $this->joinSegments[$thisPath]['alias'], $this->joinSegments[$thisPath]['object']->getOwnerWhere($current_user->id)); $group_where = SecurityGroup::getGroupWhere($this->joinSegments[$thisPath]['alias'], $this->joinSegments[$thisPath]['object']->module_dir, $current_user->id); if (!empty($owner_where)) { if (empty($this->whereString)) { $this->whereString = " (" . $owner_where . " or " . $group_where . ") "; } else { $this->whereString .= " AND (" . $owner_where . " or " . $group_where . ") "; } } else { $this->whereString .= ' AND ' . $group_where; } } break; } } } } } // increase Counter to tackle next level $levelCounter++; } } }
/** * Returns a detail object like retrieving of the current object type. * * It is intended for use in navigation buttons on the DetailView. It will pass an offset and limit argument to the sql query. * @internal This method must be called on a new instance. It overrides the values of all the fields in the current one. * * @param string $order_by * @param string $where Additional where clause * @param int $offset * @param int $row_offset Optional,default 0, starting row number * @param int $limit Optional, default -1 * @param int $max Optional, default -1 * @param int $show_deleted Optional, default 0, if set to 1 system will show deleted records. * @return array Fetched data. * * Internal function, do not override. */ public function get_detail($order_by = "", $where = "", $offset = 0, $row_offset = 0, $limit = -1, $max = -1, $show_deleted = 0) { $GLOBALS['log']->debug("get_detail: order_by = '{$order_by}' and where = '{$where}' and limit = '{$limit}' and offset = '{$offset}'"); if (isset($_SESSION['show_deleted'])) { $show_deleted = 1; } if ($this->bean_implements('ACL') && ACLController::requireOwner($this->module_dir, 'list')) { global $current_user; $owner_where = $this->getOwnerWhere($current_user->id); if (empty($where)) { $where = $owner_where; } else { $where .= ' AND ' . $owner_where; } } /* BEGIN - SECURITY GROUPS */ if ($this->bean_implements('ACL') && ACLController::requireSecurityGroup($this->module_dir, 'list')) { require_once 'modules/SecurityGroups/SecurityGroup.php'; global $current_user; $owner_where = $this->getOwnerWhere($current_user->id); $group_where = SecurityGroup::getGroupWhere($this->table_name, $this->module_dir, $current_user->id); if (!empty($owner_where)) { if (empty($where)) { $where = " (" . $owner_where . " or " . $group_where . ") "; } else { $where .= " AND (" . $owner_where . " or " . $group_where . ") "; } } else { $where .= ' AND ' . $group_where; } } /* END - SECURITY GROUPS */ $query = $this->create_new_list_query($order_by, $where, array(), array(), $show_deleted, $offset); //Add Limit and Offset to query //$query .= " LIMIT 1 OFFSET $offset"; return $this->process_detail_query($query, $row_offset, $limit, $max, $where, $offset); }
/** * builds up a delimited string for export * @param string type the bean-type to export * @param array records an array of records if coming directly from a query * @return string delimited string for export */ function export($type, $records = null, $members = false, $sample = false) { global $locale; global $beanList; global $beanFiles; global $current_user; global $app_strings; global $app_list_strings; global $timedate; global $mod_strings; global $current_language; $sampleRecordNum = 5; $contact_fields = array("id" => "Contact ID", "lead_source" => "Lead Source", "date_entered" => "Date Entered", "date_modified" => "Date Modified", "first_name" => "First Name", "last_name" => "Last Name", "salutation" => "Salutation", "birthdate" => "Lead Source", "do_not_call" => "Do Not Call", "email_opt_out" => "Email Opt Out", "title" => "Title", "department" => "Department", "birthdate" => "Birthdate", "do_not_call" => "Do Not Call", "phone_home" => "Phone (Home)", "phone_mobile" => "Phone (Mobile)", "phone_work" => "Phone (Work)", "phone_other" => "Phone (Other)", "phone_fax" => "Fax", "email1" => "Email", "email2" => "Email (Other)", "assistant" => "Assistant", "assistant_phone" => "Assistant Phone", "primary_address_street" => "Primary Address Street", "primary_address_city" => "Primary Address City", "primary_address_state" => "Primary Address State", "primary_address_postalcode" => "Primary Address Postalcode", "primary_address_country" => "Primary Address Country", "alt_address_street" => "Other Address Street", "alt_address_city" => "Other Address City", "alt_address_state" => "Other Address State", "alt_address_postalcode" => "Other Address Postalcode", "alt_address_country" => "Other Address Country", "description" => "Description"); $account_fields = array("id" => "Account ID", "name" => "Account Name", "website" => "Website", "industry" => "Industry", "account_type" => "Type", "ticker_symbol" => "Ticker Symbol", "employees" => "Employees", "ownership" => "Ownership", "phone_office" => "Phone", "phone_fax" => "Fax", "phone_alternate" => "Other Phone", "email1" => "Email", "email2" => "Other Email", "rating" => "Rating", "sic_code" => "SIC Code", "annual_revenue" => "Annual Revenue", "billing_address_street" => "Billing Address Street", "billing_address_city" => "Billing Address City", "billing_address_state" => "Billing Address State", "billing_address_postalcode" => "Billing Address Postalcode", "billing_address_country" => "Billing Address Country", "shipping_address_street" => "Shipping Address Street", "shipping_address_city" => "Shipping Address City", "shipping_address_state" => "Shipping Address State", "shipping_address_postalcode" => "Shipping Address Postalcode", "shipping_address_country" => "Shipping Address Country", "description" => "Description"); //Array of fields that should not be exported, and are only used for logic $remove_from_members = array("ea_deleted", "ear_deleted", "primary_address"); $focus = 0; $bean = $beanList[$type]; require_once $beanFiles[$bean]; $focus = new $bean(); $searchFields = array(); $db = DBManagerFactory::getInstance(); if ($records) { $records = explode(',', $records); $records = "'" . implode("','", $records) . "'"; $where = "{$focus->table_name}.id in ({$records})"; } elseif (isset($_REQUEST['all'])) { $where = ''; } else { if (!empty($_REQUEST['current_post'])) { $ret_array = generateSearchWhere($type, $_REQUEST['current_post']); $where = $ret_array['where']; $searchFields = $ret_array['searchFields']; } else { $where = ''; } } $order_by = ""; if ($focus->bean_implements('ACL')) { if (!ACLController::checkAccess($focus->module_dir, 'export', true)) { ACLController::displayNoAccess(); sugar_die(''); } if (ACLController::requireOwner($focus->module_dir, 'export')) { if (!empty($where)) { $where .= ' AND '; } $where .= $focus->getOwnerWhere($current_user->id); } /* BEGIN - SECURITY GROUPS */ if (ACLController::requireSecurityGroup($focus->module_dir, 'export')) { require_once 'modules/SecurityGroups/SecurityGroup.php'; global $current_user; $owner_where = $focus->getOwnerWhere($current_user->id); $group_where = SecurityGroup::getGroupWhere($focus->table_name, $focus->module_dir, $current_user->id); if (!empty($owner_where)) { if (empty($where)) { $where = " (" . $owner_where . " or " . $group_where . ")"; } else { $where .= " AND (" . $owner_where . " or " . $group_where . ")"; } } else { if (!empty($where)) { $where .= ' AND '; } $where .= $group_where; } } /* END - SECURITY GROUPS */ } // Export entire list was broken because the where clause already has "where" in it // and when the query is built, it has a "where" as well, so the query was ill-formed. // Eliminating the "where" here so that the query can be constructed correctly. if ($members == true) { $query = $focus->create_export_members_query($records); } else { $beginWhere = substr(trim($where), 0, 5); if ($beginWhere == "where") { $where = substr(trim($where), 5, strlen($where)); } $query = $focus->create_export_query($order_by, $where); } $result = ''; $populate = false; if ($sample) { $result = $db->limitQuery($query, 0, $sampleRecordNum, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query); if ($focus->_get_num_rows_in_query($query) < 1) { $populate = true; } } else { $result = $db->query($query, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query); } $fields_array = $db->getFieldsArray($result, true); //set up the order on the header row $fields_array = get_field_order_mapping($focus->module_dir, $fields_array); //set up labels to be used for the header row $field_labels = array(); foreach ($fields_array as $key => $dbname) { //Remove fields that are only used for logic if ($members && in_array($dbname, $remove_from_members)) { continue; } //default to the db name of label does not exist $field_labels[$key] = translateForExport($dbname, $focus); } $user_agent = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : ''; if ($locale->getExportCharset() == 'UTF-8' && !preg_match('/macintosh|mac os x|mac_powerpc/i', $user_agent)) { //Bug 55520 - add BOM to the exporting CSV so any symbols are displayed correctly in Excel $BOM = ""; $content = $BOM; } else { $content = ''; } // setup the "header" line with proper delimiters $content .= "\"" . implode("\"" . getDelimiter() . "\"", array_values($field_labels)) . "\"\r\n"; $pre_id = ''; if ($populate) { //this is a sample request with no data, so create fake datarows $content .= returnFakeDataRow($focus, $fields_array, $sampleRecordNum); } else { $records = array(); //process retrieved record while ($val = $db->fetchByAssoc($result, false)) { //order the values in the record array $val = get_field_order_mapping($focus->module_dir, $val); $new_arr = array(); if ($members) { if ($pre_id == $val['id']) { continue; } if ($val['ea_deleted'] == 1 || $val['ear_deleted'] == 1) { $val['primary_email_address'] = ''; } unset($val['ea_deleted']); unset($val['ear_deleted']); unset($val['primary_address']); } $pre_id = $val['id']; foreach ($val as $key => $value) { //getting content values depending on their types $fieldNameMapKey = $fields_array[$key]; if (isset($focus->field_name_map[$fieldNameMapKey]) && $focus->field_name_map[$fieldNameMapKey]['type']) { $fieldType = $focus->field_name_map[$fieldNameMapKey]['type']; switch ($fieldType) { //if our value is a currency field, then apply the users locale case 'currency': require_once 'modules/Currencies/Currency.php'; $value = currency_format_number($value); break; //if our value is a datetime field, then apply the users locale //if our value is a datetime field, then apply the users locale case 'datetime': case 'datetimecombo': $value = $timedate->to_display_date_time($db->fromConvert($value, 'datetime')); $value = preg_replace('/([pm|PM|am|AM]+)/', ' \\1', $value); break; //kbrill Bug #16296 //kbrill Bug #16296 case 'date': $value = $timedate->to_display_date($db->fromConvert($value, 'date'), false); break; // Bug 32463 - Properly have multienum field translated into something useful for the client // Bug 32463 - Properly have multienum field translated into something useful for the client case 'multienum': $value = str_replace("^", "", $value); if (isset($focus->field_name_map[$fields_array[$key]]['options']) && isset($app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']])) { $valueArray = explode(",", $value); foreach ($valueArray as $multikey => $multivalue) { if (isset($app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']][$multivalue])) { $valueArray[$multikey] = $app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']][$multivalue]; } } $value = implode(",", $valueArray); } break; } } // Keep as $key => $value for post-processing $new_arr[$key] = preg_replace("/\"/", "\"\"", $value); } // Use Bean ID as key for records $records[$pre_id] = $new_arr; } // Check if we're going to export non-primary emails if ($focus->hasEmails() && in_array('email_addresses_non_primary', $fields_array)) { // $records keys are bean ids $keys = array_keys($records); // Split the ids array into chunks of size 100 $chunks = array_chunk($keys, 100); foreach ($chunks as $chunk) { // Pick all the non-primary mails for the chunk $query = "\n SELECT eabr.bean_id, ea.email_address\n FROM email_addr_bean_rel eabr\n LEFT JOIN email_addresses ea ON ea.id = eabr.email_address_id\n WHERE eabr.bean_module = '{$focus->module_dir}'\n AND eabr.primary_address = '0'\n AND eabr.bean_id IN ('" . implode("', '", $chunk) . "')\n AND eabr.deleted != '1'\n ORDER BY eabr.bean_id, eabr.reply_to_address, eabr.primary_address DESC\n "; $result = $db->query($query, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query); while ($val = $db->fetchByAssoc($result, false)) { if (empty($records[$val['bean_id']]['email_addresses_non_primary'])) { $records[$val['bean_id']]['email_addresses_non_primary'] = $val['email_address']; } else { // No custom non-primary mail delimeter yet, use semi-colon $records[$val['bean_id']]['email_addresses_non_primary'] .= ';' . $val['email_address']; } } } } foreach ($records as $record) { $line = implode("\"" . getDelimiter() . "\"", $record); $line = "\"" . $line; $line .= "\"\r\n"; $content .= $line; } } return $content; }
function create_new_list_query($order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = '', $return_array = false, $parentbean, $singleSelect = false) { global $beanFiles, $beanList; $selectedFields = array(); $secondarySelectedFields = array(); $ret_array = array(); $distinct = ''; if ($this->bean_implements('ACL') && ACLController::requireOwner($this->module_dir, 'list')) { global $current_user; $owner_where = $this->getOwnerWhere($current_user->id); if (empty($where)) { $where = $owner_where; } else { $where .= ' AND ' . $owner_where; } } if (!empty($params['distinct'])) { $distinct = ' DISTINCT '; } if (empty($filter)) { $ret_array['select'] = " SELECT {$distinct} {$this->table_name}.* "; } else { $ret_array['select'] = " SELECT {$distinct} {$this->table_name}.id "; } $ret_array['from'] = " FROM {$this->table_name} "; $ret_array['from_min'] = $ret_array['from']; $ret_array['secondary_from'] = $ret_array['from']; $ret_array['where'] = ''; $ret_array['order_by'] = ''; //secondary selects are selects that need to be run after the primarty query to retrieve additional info on main if ($singleSelect) { $ret_array['secondary_select'] =& $ret_array['select']; $ret_array['secondary_from'] =& $ret_array['from']; } else { $ret_array['secondary_select'] = ''; } $custom_join = false; if ((!isset($params['include_custom_fields']) || $params['include_custom_fields']) && isset($this->custom_fields)) { $custom_join = $this->custom_fields->getJOIN(); if ($custom_join) { $ret_array['select'] .= ' ' . $custom_join['select']; } } if ($custom_join) { $ret_array['from'] .= ' ' . $custom_join['join']; } $jtcount = 0; //LOOP AROUND FOR FIXIN VARDEF ISSUES require 'include/VarDefHandler/listvardefoverride.php'; $joined_tables = array(); if (isset($params['joined_tables'])) { foreach ($params['joined_tables'] as $table) { $joined_tables[$table] = 1; } } if (!empty($filter)) { $filterKeys = array_keys($filter); if (is_numeric($filterKeys[0])) { $fields = array(); foreach ($filter as $field) { $field = strtolower($field); if (isset($this->field_defs[$field])) { $fields[$field] = $this->field_defs[$field]; } else { $fields[$field] = array('force_exists' => true); } } } else { $fields = $filter; } } else { $fields = $this->field_defs; } foreach ($fields as $field => $value) { //alias is used to alias field names $alias = ''; if (isset($value['alias'])) { $alias = ' as ' . $value['alias'] . ' '; } if (empty($this->field_defs[$field])) { if (!empty($filter) && isset($filter[$field]['force_exists']) && $filter[$field]['force_exists']) { //spaces are a fix for length issue problem with unions. The union only returns the maximum number of characters from the first select statemtn. $ret_array['select'] .= ", ' ' {$field} "; } continue; } else { $data = $this->field_defs[$field]; } //ignore fields that are a part of the collection and a field has been removed as a result of //layout customization.. this happens in subpanel customizations, use case, from the contacts subpanel //in opportunities module remove the contact_role/opportunity_role field. $process_field = true; if (isset($data['relationship_fields']) and !empty($data['relationship_fields'])) { foreach ($data['relationship_fields'] as $field_name) { if (!isset($fields[$field_name])) { $process_field = false; } } } if (!$process_field) { continue; } if ((!isset($data['source']) || $data['source'] == 'db') && (!empty($alias) || !empty($filter))) { $ret_array['select'] .= ", {$this->table_name}.{$field} {$alias}"; } if ($data['type'] != 'relate' && isset($data['db_concat_fields'])) { $ret_array['select'] .= ", " . db_concat($this->table_name, $data['db_concat_fields']) . " as {$field}"; } if ($data['type'] == 'relate' && isset($data['link'])) { $this->load_relationship($data['link']); if (!empty($this->{$data}['link'])) { $params = array(); if (empty($join_type)) { $params['join_type'] = ' LEFT JOIN '; } else { $params['join_type'] = $join_type; } if (isset($data['join_name'])) { $params['join_table_alias'] = $data['join_name']; } else { $params['join_table_alias'] = 'jt' . $jtcount; } if (isset($data['join_link_name'])) { $params['join_table_link_alias'] = $data['join_link_name']; } else { $params['join_table_link_alias'] = 'jtl' . $jtcount; } $join = $this->{$data}['link']->getJoin($params, true); $rel_module = $this->{$data}['link']->getRelatedModuleName(); $table_joined = !empty($joined_tables[$params['join_table_alias']]) || !empty($joined_tables[$params['join_table_link_alias']]) && isset($data['link_type']) && $data['link_type'] == 'relationship_info'; if ($join['type'] == 'many-to-many') { if (empty($ret_array['secondary_select'])) { $ret_array['secondary_select'] = " SELECT {$this->table_name}.id ref_id "; if (!empty($beanFiles[$beanList[$rel_module]])) { require_once $beanFiles[$beanList[$rel_module]]; $rel_mod = new $beanList[$rel_module](); if (isset($rel_mod->field_defs['assigned_user_id'])) { $ret_array['secondary_select'] .= " , " . $params['join_table_alias'] . ".assigned_user_id {$field}_owner, '{$rel_module}' {$field}_mod"; } else { if (isset($rel_mod->field_defs['created_by'])) { $ret_array['secondary_select'] .= " , " . $params['join_table_alias'] . ".created_by {$field}_owner , '{$rel_module}' {$field}_mod"; } } } } if (isset($data['db_concat_fields'])) { $ret_array['secondary_select'] .= ' , ' . db_concat($params['join_table_alias'], $data['db_concat_fields']) . ' ' . $field; } else { if (!isset($data['relationship_fields'])) { $ret_array['secondary_select'] .= ' , ' . $params['join_table_alias'] . '.' . $data['rname'] . ' ' . $field; } } if (!$singleSelect) { $ret_array['select'] .= ", ' ' {$field} "; $ret_array['select'] .= ", ' ' " . $join['rel_key'] . ' '; } $ret_array['secondary_select'] .= ', ' . $params['join_table_link_alias'] . '.' . $join['rel_key'] . ' ' . $join['rel_key']; if (isset($data['relationship_fields'])) { foreach ($data['relationship_fields'] as $r_name => $alias_name) { if (!empty($secondarySelectedFields[$alias_name])) { continue; } $ret_array['secondary_select'] .= ', ' . $params['join_table_link_alias'] . '.' . $r_name . ' ' . $alias_name; $secondarySelectedFields[$alias_name] = true; } } if (!$table_joined) { $ret_array['secondary_from'] .= ' ' . $join['join'] . ' AND ' . $params['join_table_alias'] . '.deleted=0'; if (isset($data['link_type']) && $data['link_type'] == 'relationship_info') { $ret_array['secondary_where'] = $params['join_table_link_alias'] . '.' . $join['rel_key'] . "='" . $parentbean->id . "'"; } } } else { if (isset($data['db_concat_fields'])) { $ret_array['select'] .= ' , ' . db_concat($params['join_table_alias'], $data['db_concat_fields']) . ' ' . $field; } else { $ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.' . $data['rname'] . ' ' . $field; } if (!$table_joined) { $ret_array['from'] .= ' ' . $join['join'] . ' AND ' . $params['join_table_alias'] . '.deleted=0'; if (!empty($beanList[$rel_module]) && !empty($beanFiles[$beanList[$rel_module]])) { require_once $beanFiles[$beanList[$rel_module]]; $rel_mod = new $beanList[$rel_module](); if (isset($value['target_record_key']) && !empty($filter)) { $selectedFields[$this->table_name . '.' . $value['target_record_key']] = true; $ret_array['select'] .= " , {$this->table_name}.{$value['target_record_key']} "; } if (isset($rel_mod->field_defs['assigned_user_id'])) { $ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.assigned_user_id ' . $field . '_owner'; } else { $ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.created_by ' . $field . '_owner'; } $ret_array['select'] .= " , '" . $rel_module . "' " . $field . '_mod'; } } } if (!$table_joined) { $joined_tables[$params['join_table_alias']] = 1; $joined_tables[$params['join_table_link_alias']] = 1; } $jtcount++; } } } if (!empty($filter)) { if (isset($this->field_defs['assigned_user_id']) && empty($selectedFields[$this->table_name . '.assigned_user_id'])) { $ret_array['select'] .= ", {$this->table_name}.assigned_user_id "; } else { if (isset($this->field_defs['created_by']) && empty($selectedFields[$this->table_name . '.created_by'])) { $ret_array['select'] .= ", {$this->table_name}.created_by "; } } if (isset($this->field_defs['system_id']) && empty($selectedFields[$this->table_name . '.system_id'])) { $ret_array['select'] .= ", {$this->table_name}.system_id "; } } $where_auto = '1=1'; if ($show_deleted == 0) { $where_auto = "{$this->table_name}.deleted=0"; } else { if ($show_deleted == 1) { $where_auto = "{$this->table_name}.deleted=1"; } } if ($where != "") { $ret_array['where'] = " where ({$where}) AND {$where_auto}"; } else { $ret_array['where'] = " where {$where_auto}"; } if (!empty($order_by)) { //make call to process the order by clause $ret_array['order_by'] = " ORDER BY " . $this->process_order_by($order_by, null); } if ($singleSelect) { unset($ret_array['secondary_where']); unset($ret_array['secondary_from']); unset($ret_array['secondary_select']); } if ($return_array) { return $ret_array; } return $ret_array['select'] . $ret_array['from'] . $ret_array['where'] . $ret_array['order_by']; }
/** * Equivalent of get_list function within SugarBean but allows the possibility to pass in an indicator * if the list should filter for favorites. Should eventually update the SugarBean function as well. * */ function get_data_list($seed, $order_by = "", $where = "", $row_offset = 0, $limit = -1, $max = -1, $show_deleted = 0, $favorites = false, $singleSelect = false) { Log::debug("get_list: order_by = '{$order_by}' and where = '{$where}' and limit = '{$limit}'"); if (isset($_SESSION['show_deleted'])) { $show_deleted = 1; } $order_by = $seed->process_order_by($order_by, null); if ($seed->bean_implements('ACL') && ACLController::requireOwner($seed->module_dir, 'list')) { global $current_user; $owner_where = $seed->getOwnerWhere($current_user->id); if (!empty($owner_where)) { if (empty($where)) { $where = $owner_where; } else { $where .= ' AND ' . $owner_where; } } } $params = array(); if ($favorites) { $params['favorites'] = true; } $query = $seed->create_new_list_query($order_by, $where, array(), $params, $show_deleted, '', false, null, $singleSelect); return $seed->process_list_query($query, $row_offset, $limit, $max, $where); }
/** * builds up a delimited string for export * @param string type the bean-type to export * @param array records an array of records if coming directly from a query * @return string delimited string for export */ function export($type, $records = null, $members = false) { global $beanList; global $beanFiles; global $current_user; global $app_strings; global $app_list_strings; global $timedate; $contact_fields = array("id" => "Contact ID", "lead_source" => "Lead Source", "date_entered" => "Date Entered", "date_modified" => "Date Modified", "first_name" => "First Name", "last_name" => "Last Name", "salutation" => "Salutation", "birthdate" => "Lead Source", "do_not_call" => "Do Not Call", "email_opt_out" => "Email Opt Out", "title" => "Title", "department" => "Department", "birthdate" => "Birthdate", "do_not_call" => "Do Not Call", "phone_home" => "Phone (Home)", "phone_mobile" => "Phone (Mobile)", "phone_work" => "Phone (Work)", "phone_other" => "Phone (Other)", "phone_fax" => "Fax", "email1" => "Email", "email2" => "Email (Other)", "assistant" => "Assistant", "assistant_phone" => "Assistant Phone", "primary_address_street" => "Primary Address Street", "primary_address_city" => "Primary Address City", "primary_address_state" => "Primary Address State", "primary_address_postalcode" => "Primary Address Postalcode", "primary_address_country" => "Primary Address Country", "alt_address_street" => "Other Address Street", "alt_address_city" => "Other Address City", "alt_address_state" => "Other Address State", "alt_address_postalcode" => "Other Address Postalcode", "alt_address_country" => "Other Address Country", "description" => "Description"); $account_fields = array("id" => "Account ID", "name" => "Account Name", "website" => "Website", "industry" => "Industry", "account_type" => "Type", "ticker_symbol" => "Ticker Symbol", "employees" => "Employees", "ownership" => "Ownership", "phone_office" => "Phone", "phone_fax" => "Fax", "phone_alternate" => "Other Phone", "email1" => "Email", "email2" => "Other Email", "rating" => "Rating", "sic_code" => "SIC Code", "annual_revenue" => "Annual Revenue", "billing_address_street" => "Billing Address Street", "billing_address_city" => "Billing Address City", "billing_address_state" => "Billing Address State", "billing_address_postalcode" => "Billing Address Postalcode", "billing_address_country" => "Billing Address Country", "shipping_address_street" => "Shipping Address Street", "shipping_address_city" => "Shipping Address City", "shipping_address_state" => "Shipping Address State", "shipping_address_postalcode" => "Shipping Address Postalcode", "shipping_address_country" => "Shipping Address Country", "description" => "Description"); $focus = 0; $content = ''; $bean = $beanList[$type]; require_once $beanFiles[$bean]; $focus = new $bean(); $searchFields = array(); $db = DBManagerFactory::getInstance(); if ($records) { $records = explode(',', $records); $records = "'" . implode("','", $records) . "'"; $where = "{$focus->table_name}.id in ({$records})"; } elseif (isset($_REQUEST['all'])) { $where = ''; } else { if (!empty($_REQUEST['current_post'])) { $ret_array = generateSearchWhere($type, $_REQUEST['current_post']); $where = $ret_array['where']; $searchFields = $ret_array['searchFields']; } else { $where = ''; } } $order_by = ""; if ($focus->bean_implements('ACL')) { if (!ACLController::checkAccess($focus->module_dir, 'export', true)) { ACLController::displayNoAccess(); sugar_die(''); } if (ACLController::requireOwner($focus->module_dir, 'export')) { if (!empty($where)) { $where .= ' AND '; } $where .= $focus->getOwnerWhere($current_user->id); } /* BEGIN - SECURITY GROUPS */ if (ACLController::requireSecurityGroup($focus->module_dir, 'export')) { require_once 'modules/SecurityGroups/SecurityGroup.php'; global $current_user; $owner_where = $focus->getOwnerWhere($current_user->id); $group_where = SecurityGroup::getGroupWhere($focus->table_name, $focus->module_dir, $current_user->id); if (!empty($owner_where)) { if (empty($where)) { $where = " (" . $owner_where . " or " . $group_where . ")"; } else { $where .= " AND (" . $owner_where . " or " . $group_where . ")"; } } else { if (!empty($where)) { $where .= ' AND '; } $where .= $group_where; } } /* END - SECURITY GROUPS */ } // Export entire list was broken because the where clause already has "where" in it // and when the query is built, it has a "where" as well, so the query was ill-formed. // Eliminating the "where" here so that the query can be constructed correctly. if ($members == true) { $query = $focus->create_export_members_query($records); } else { $beginWhere = substr(trim($where), 0, 5); if ($beginWhere == "where") { $where = substr(trim($where), 5, strlen($where)); } $ret_array = create_export_query_relate_link_patch($type, $searchFields, $where); if (!empty($ret_array['join'])) { $query = $focus->create_export_query($order_by, $ret_array['where'], $ret_array['join']); } else { $query = $focus->create_export_query($order_by, $ret_array['where']); } } $result = $db->query($query, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query); $fields_array = $db->getFieldsArray($result, true); global $sugar_config; $utf = utf8_encode("????"); $file = fopen('Export.csv', 'w'); //$val=iconv("UTF-8","WINDOWS-1255",html_entity_decode($utf,ENT_COMPAT,'utf-8')); foreach ($fields_array as $head) { $fields_array[] = array(utf8_decode($head)); } fputcsv($file, $fields_array); // setup the "header" line with proper delimiters /*$header = implode("\"".getDelimiter()."\"", array_values($fields_array)); if($members){ $header = str_replace('"ea_deleted"'.getDelimiter().'"ear_deleted"'.getDelimiter().'"primary_address"'.getDelimiter().'','',$header); } $header = "\"" .$header; $header .= "\"\r\n"; $content .= $header;*/ $pre_id = ''; while ($val = $db->fetchByAssoc($result, -1, false)) { $new_arr = array(); if ($members) { if ($pre_id == $val['id']) { continue; } if ($val['ea_deleted'] == 1 || $val['ear_deleted'] == 1) { $val['primary_email_address'] = ''; } unset($val['ea_deleted']); unset($val['ear_deleted']); unset($val['primary_address']); } $pre_id = $val['id']; $vals = array_values($val); foreach ($vals as $key => $value) { //if our value is a datetime field, then apply the users locale if (isset($focus->field_name_map[$fields_array[$key]]['type']) && ($focus->field_name_map[$fields_array[$key]]['type'] == 'datetime' || $focus->field_name_map[$fields_array[$key]]['type'] == 'datetimecombo')) { $value = $timedate->to_display_date_time($value); $value = preg_replace('/([pm|PM|am|AM]+)/', ' \\1', $value); } //kbrill Bug #16296 if (isset($focus->field_name_map[$fields_array[$key]]['type']) && $focus->field_name_map[$fields_array[$key]]['type'] == 'date') { $value = $timedate->to_display_date($value, false); } // Bug 32463 - Properly have multienum field translated into something useful for the client if (isset($focus->field_name_map[$fields_array[$key]]['type']) && $focus->field_name_map[$fields_array[$key]]['type'] == 'multienum') { $value = str_replace("^", "", $value); if (isset($focus->field_name_map[$fields_array[$key]]['options']) && isset($app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']])) { $valueArray = explode(",", $value); foreach ($valueArray as $multikey => $multivalue) { if (isset($app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']][$multivalue])) { $valueArray[$multikey] = $app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']][$multivalue]; } } $value = implode(",", $valueArray); } } array_push($new_arr, $value); } //$line = implode("\"".getDelimiter()."\"", $new_arr); //$line = "\"" .$line; //$line .= "\"\r\n"; $row = array(); foreach ($val as $cont) { $from = mb_detect_encoding($cont); $to = 'UTF-8'; $cont = iconv("gbk", "utf-8", $cont); // array_push($row,mb_convert_encoding($cont, $from, $to)); } fputcsv($file, $row); //$content .= $line; } header('Content-Type: text/x-csv; charset="UTF-8"'); header('Content-Disposition: attachment; filename="Report_Week1.csv"'); header('Pragma: no-cache'); header('Expires: 0'); header('Location: ' . $sugar_config['site_url'] . '/Export.csv'); die; //return $content; }
/** * Return the list query used by the list views and export button. Next generation of create_new_list_query function. * * Override this function to return a custom query. * * @param string $order_by custom order by clause * @param string $where custom where clause * @param array $filter Optioanal * @param array $params Optional * * @param int $show_deleted Optional, default 0, show deleted records is set to 1. * @param string $join_type * @param boolean $return_array Optional, default false, response as array * @param object $parentbean creating a subquery for this bean. * @param boolean $singleSelect Optional, default false. * @return String select query string, optionally an array value will be returned if $return_array= true. */ 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) { global $beanFiles, $beanList; $selectedFields = array(); $secondarySelectedFields = array(); $ret_array = array(); $distinct = ''; if ($this->bean_implements('ACL') && ACLController::requireOwner($this->module_dir, 'list')) { global $current_user; $owner_where = $this->getOwnerWhere($current_user->id); if (empty($where)) { $where = $owner_where; } else { $where .= ' AND ' . $owner_where; } } if (!empty($params['distinct'])) { $distinct = ' DISTINCT '; } if (empty($filter)) { $ret_array['select'] = " SELECT {$distinct} {$this->table_name}.* "; } else { $ret_array['select'] = " SELECT {$distinct} {$this->table_name}.id "; } $ret_array['from'] = " FROM {$this->table_name} "; $ret_array['from_min'] = $ret_array['from']; $ret_array['secondary_from'] = $ret_array['from']; $ret_array['where'] = ''; $ret_array['order_by'] = ''; //secondary selects are selects that need to be run after the primarty query to retrieve additional info on main if ($singleSelect) { $ret_array['secondary_select'] =& $ret_array['select']; $ret_array['secondary_from'] =& $ret_array['from']; } else { $ret_array['secondary_select'] = ''; } $custom_join = false; if ((!isset($params['include_custom_fields']) || $params['include_custom_fields']) && isset($this->custom_fields)) { $custom_join = $this->custom_fields->getJOIN(empty($filter) ? true : $filter); if ($custom_join) { $ret_array['select'] .= ' ' . $custom_join['select']; } } if ($custom_join) { $ret_array['from'] .= ' ' . $custom_join['join']; } $jtcount = 0; //LOOP AROUND FOR FIXIN VARDEF ISSUES require 'include/VarDefHandler/listvardefoverride.php'; $joined_tables = array(); if (isset($params['joined_tables'])) { foreach ($params['joined_tables'] as $table) { $joined_tables[$table] = 1; } } if (!empty($filter)) { $filterKeys = array_keys($filter); if (is_numeric($filterKeys[0])) { $fields = array(); foreach ($filter as $field) { $field = strtolower($field); //remove out id field so we don't duplicate it if ($field == 'id' && !empty($filter)) { continue; } if (isset($this->field_defs[$field])) { $fields[$field] = $this->field_defs[$field]; } else { $fields[$field] = array('force_exists' => true); } } } else { $fields = $filter; } } else { $fields = $this->field_defs; } $used_join_key = array(); foreach ($fields as $field => $value) { //alias is used to alias field names $alias = ''; if (isset($value['alias'])) { $alias = ' as ' . $value['alias'] . ' '; } if (empty($this->field_defs[$field]) || !empty($value['force_blank'])) { if (!empty($filter) && isset($filter[$field]['force_exists']) && $filter[$field]['force_exists']) { if (isset($filter[$field]['force_default'])) { $ret_array['select'] .= ", {$filter[$field]['force_default']} {$field} "; } else { //spaces are a fix for length issue problem with unions. The union only returns the maximum number of characters from the first select statemtn. $ret_array['select'] .= ", ' ' {$field} "; } } continue; } else { $data = $this->field_defs[$field]; } //ignore fields that are a part of the collection and a field has been removed as a result of //layout customization.. this happens in subpanel customizations, use case, from the contacts subpanel //in opportunities module remove the contact_role/opportunity_role field. $process_field = true; if (isset($data['relationship_fields']) and !empty($data['relationship_fields'])) { foreach ($data['relationship_fields'] as $field_name) { if (!isset($fields[$field_name])) { $process_field = false; } } } if (!$process_field) { continue; } if ((!isset($data['source']) || $data['source'] == 'db') && (!empty($alias) || !empty($filter))) { $ret_array['select'] .= ", {$this->table_name}.{$field} {$alias}"; $selectedFields["{$this->table_name}.{$field}"] = true; } if ($data['type'] != 'relate' && isset($data['db_concat_fields'])) { $ret_array['select'] .= ", " . db_concat($this->table_name, $data['db_concat_fields']) . " as {$field}"; $selectedFields[db_concat($this->table_name, $data['db_concat_fields'])] = true; } //Custom relate field or relate fields built in module builder which have no link field associated. if ($data['type'] == 'relate' && (isset($data['custom_module']) || isset($data['ext2']))) { $joinTableAlias = 'jt' . $jtcount; $relateJoinInfo = $this->custom_fields->getRelateJoin($data, $joinTableAlias); $ret_array['select'] .= $relateJoinInfo['select']; $ret_array['from'] .= $relateJoinInfo['from']; //Replace any references to the relationship in the where clause with the new alias //If the link isn't set, assume that search used the local table for the field $searchTable = isset($data['link']) ? $relateJoinInfo['rel_table'] : $this->table_name; $field_name = $relateJoinInfo['rel_table'] . '.' . !empty($data['name']) ? $data['name'] : 'name'; $where = preg_replace('/(^|[\\s(])' . $field_name . '/', '${1}' . $relateJoinInfo['name_field'], $where); $jtcount++; } //Parent Field if ($data['type'] == 'parent') { //See if we need to join anything by inspecting the where clause $match = preg_match('/(^|[\\s(])parent_(\\w+)_(\\w+)\\.name/', $where, $matches); if ($match) { $joinTableAlias = 'jt' . $jtcount; $joinModule = $matches[2]; $joinTable = $matches[3]; $localTable = $this->table_name; if (!empty($data['custom_module'])) { $localTable .= '_cstm'; } global $beanFiles, $beanList, $module; require_once $beanFiles[$beanList[$joinModule]]; $rel_mod = new $beanList[$joinModule](); $nameField = "{$joinTableAlias}.name"; if (isset($rel_mod->field_defs['name'])) { $name_field_def = $rel_mod->field_defs['name']; if (isset($name_field_def['db_concat_fields'])) { $nameField = db_concat($joinTableAlias, $name_field_def['db_concat_fields']); } } $ret_array['select'] .= ", {$nameField} {$data['name']} "; $ret_array['from'] .= " LEFT JOIN {$joinTable} {$joinTableAlias}\n ON {$localTable}.{$data['id_name']} = {$joinTableAlias}.id"; //Replace any references to the relationship in the where clause with the new alias $where = preg_replace('/(^|[\\s(])parent_' . $joinModule . '_' . $joinTable . '\\.name/', '${1}' . $nameField, $where); $jtcount++; } } if ($data['type'] == 'relate' && isset($data['link'])) { $this->load_relationship($data['link']); if (!empty($this->{$data}['link'])) { $params = array(); if (empty($join_type)) { $params['join_type'] = ' LEFT JOIN '; } else { $params['join_type'] = $join_type; } if (isset($data['join_name'])) { $params['join_table_alias'] = $data['join_name']; } else { $params['join_table_alias'] = 'jt' . $jtcount; } if (isset($data['join_link_name'])) { $params['join_table_link_alias'] = $data['join_link_name']; } else { $params['join_table_link_alias'] = 'jtl' . $jtcount; } $join_primary = !isset($data['join_primary']) || $data['join_primary']; $join = $this->{$data}['link']->getJoin($params, true); $used_join_key[] = $join['rel_key']; $rel_module = $this->{$data}['link']->getRelatedModuleName(); $table_joined = !empty($joined_tables[$params['join_table_alias']]) || !empty($joined_tables[$params['join_table_link_alias']]) && isset($data['link_type']) && $data['link_type'] == 'relationship_info'; //if rnanme is set to 'name', and bean files exist, then check if field should be a concatenated name global $beanFiles, $beanList; if ($data['rname'] && !empty($beanFiles[$beanList[$rel_module]])) { //create an instance of the related bean require_once $beanFiles[$beanList[$rel_module]]; $rel_mod = new $beanList[$rel_module](); //if bean has first and last name fields, then name should be concatenated if (isset($rel_mod->field_name_map['first_name']) && isset($rel_mod->field_name_map['last_name'])) { $data['db_concat_fields'] = array(0 => 'first_name', 1 => 'last_name'); } } if ($join['type'] == 'many-to-many') { if (empty($ret_array['secondary_select'])) { $ret_array['secondary_select'] = " SELECT {$this->table_name}.id ref_id "; if (!empty($beanFiles[$beanList[$rel_module]]) && $join_primary) { require_once $beanFiles[$beanList[$rel_module]]; $rel_mod = new $beanList[$rel_module](); if (isset($rel_mod->field_defs['assigned_user_id'])) { $ret_array['secondary_select'] .= " , " . $params['join_table_alias'] . ".assigned_user_id {$field}_owner, '{$rel_module}' {$field}_mod"; } else { if (isset($rel_mod->field_defs['created_by'])) { $ret_array['secondary_select'] .= " , " . $params['join_table_alias'] . ".created_by {$field}_owner , '{$rel_module}' {$field}_mod"; } } } } if (isset($data['db_concat_fields'])) { $ret_array['secondary_select'] .= ' , ' . db_concat($params['join_table_alias'], $data['db_concat_fields']) . ' ' . $field; } else { if (!isset($data['relationship_fields'])) { $ret_array['secondary_select'] .= ' , ' . $params['join_table_alias'] . '.' . $data['rname'] . ' ' . $field; } } if (!$singleSelect) { $ret_array['select'] .= ", ' ' {$field} "; $ret_array['select'] .= ", ' ' " . $join['rel_key'] . ' '; } $count_used = 0; if ($this->db->dbType != 'mysql') { //bug 26801, these codes are just used to duplicate rel_key in the select sql, or it will throw error in MSSQL and Oracle. foreach ($used_join_key as $used_key) { if ($used_key == $join['rel_key']) { $count_used++; } } } if ($count_used <= 1) { //27416, the $ret_array['secondary_select'] should always generate, regardless the dbtype $ret_array['secondary_select'] .= ', ' . $params['join_table_link_alias'] . '.' . $join['rel_key'] . ' ' . $join['rel_key']; } if (isset($data['relationship_fields'])) { foreach ($data['relationship_fields'] as $r_name => $alias_name) { if (!empty($secondarySelectedFields[$alias_name])) { continue; } $ret_array['secondary_select'] .= ', ' . $params['join_table_link_alias'] . '.' . $r_name . ' ' . $alias_name; $secondarySelectedFields[$alias_name] = true; } } if (!$table_joined) { $ret_array['secondary_from'] .= ' ' . $join['join'] . ' AND ' . $params['join_table_alias'] . '.deleted=0'; if (isset($data['link_type']) && $data['link_type'] == 'relationship_info' && $parentbean instanceof SugarBean) { $ret_array['secondary_where'] = $params['join_table_link_alias'] . '.' . $join['rel_key'] . "='" . $parentbean->id . "'"; } } } else { if (isset($data['db_concat_fields'])) { $ret_array['select'] .= ' , ' . db_concat($params['join_table_alias'], $data['db_concat_fields']) . ' ' . $field; } else { $ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.' . $data['rname'] . ' ' . $field; } if (isset($data['additionalFields'])) { foreach ($data['additionalFields'] as $k => $v) { $ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.' . $k . ' ' . $v; } } if (!$table_joined) { $ret_array['from'] .= ' ' . $join['join'] . ' AND ' . $params['join_table_alias'] . '.deleted=0'; if (!empty($beanList[$rel_module]) && !empty($beanFiles[$beanList[$rel_module]])) { require_once $beanFiles[$beanList[$rel_module]]; $rel_mod = new $beanList[$rel_module](); if (isset($value['target_record_key']) && !empty($filter)) { $selectedFields[$this->table_name . '.' . $value['target_record_key']] = true; $ret_array['select'] .= " , {$this->table_name}.{$value['target_record_key']} "; } if (isset($rel_mod->field_defs['assigned_user_id'])) { $ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.assigned_user_id ' . $field . '_owner'; } else { $ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.created_by ' . $field . '_owner'; } $ret_array['select'] .= " , '" . $rel_module . "' " . $field . '_mod'; } } } //Replace references to this table in the where clause with the new alias $join_table_name = $this->{$data}['link']->getRelatedTableName(); // To fix SOAP stuff where we are trying to retrieve all the accounts data where accounts.id = .. // and this code changes accounts to jt4 as there is a self join with the accounts table. //Martin fix #27494 if (isset($data['db_concat_fields'])) { $buildWhere = false; if (in_array('first_name', $data['db_concat_fields']) && in_array('last_name', $data['db_concat_fields'])) { $exp = '/\\(\\s*?' . $data['name'] . '.*?\\%\'\\s*?\\)/'; if (preg_match($exp, $where, $matches)) { $search_expression = $matches[0]; //Create three search conditions - first + last, first, last $first_name_search = str_replace($data['name'], $params['join_table_alias'] . '.first_name', $search_expression); $last_name_search = str_replace($data['name'], $params['join_table_alias'] . '.last_name', $search_expression); $full_name_search = str_replace($data['name'], db_concat($params['join_table_alias'], $data['db_concat_fields']), $search_expression); $buildWhere = true; $where = str_replace($search_expression, '(' . $full_name_search . ' OR ' . $first_name_search . ' OR ' . $last_name_search . ')', $where); } } if (!$buildWhere) { $db_field = db_concat($params['join_table_alias'], $data['db_concat_fields']); $where = preg_replace('/' . $data['name'] . '/', $db_field, $where); } } else { $where = preg_replace('/(^|[\\s(])' . $data['name'] . '/', '${1}' . $params['join_table_alias'] . '.' . $data['rname'], $where); } if (!$table_joined) { $joined_tables[$params['join_table_alias']] = 1; $joined_tables[$params['join_table_link_alias']] = 1; } $jtcount++; } } } if (!empty($filter)) { if (isset($this->field_defs['assigned_user_id']) && empty($selectedFields[$this->table_name . '.assigned_user_id'])) { $ret_array['select'] .= ", {$this->table_name}.assigned_user_id "; } else { if (isset($this->field_defs['created_by']) && empty($selectedFields[$this->table_name . '.created_by'])) { $ret_array['select'] .= ", {$this->table_name}.created_by "; } } if (isset($this->field_defs['system_id']) && empty($selectedFields[$this->table_name . '.system_id'])) { $ret_array['select'] .= ", {$this->table_name}.system_id "; } } if ($ifListForExport) { if (isset($this->field_defs['email1'])) { $ret_array['select'] .= " ,email_addresses.email_address email1"; $ret_array['from'] .= " LEFT JOIN email_addr_bean_rel on {$this->table_name}.id = email_addr_bean_rel.bean_id and email_addr_bean_rel.bean_module='{$this->module_dir}' and email_addr_bean_rel.deleted=0 and email_addr_bean_rel.primary_address=1 LEFT JOIN email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id "; } } $where_auto = '1=1'; if ($show_deleted == 0) { $where_auto = "{$this->table_name}.deleted=0"; } else { if ($show_deleted == 1) { $where_auto = "{$this->table_name}.deleted=1"; } } if ($where != "") { $ret_array['where'] = " where ({$where}) AND {$where_auto}"; } else { $ret_array['where'] = " where {$where_auto}"; } if (!empty($order_by)) { //make call to process the order by clause $ret_array['order_by'] = " ORDER BY " . $this->process_order_by($order_by, null); } if ($singleSelect) { unset($ret_array['secondary_where']); unset($ret_array['secondary_from']); unset($ret_array['secondary_select']); } if ($return_array) { return $ret_array; } return $ret_array['select'] . $ret_array['from'] . $ret_array['where'] . $ret_array['order_by']; }
/** * builds up a delimited string for export * @param string type the bean-type to export * @param array records an array of records if coming directly from a query * @return string delimited string for export */ function export($type, $records = null, $members = false, $sample = false) { global $locale; global $beanList; global $beanFiles; global $current_user; global $app_strings; global $app_list_strings; global $timedate; global $mod_strings; global $current_language; $sampleRecordNum = 5; //Array of fields that should not be exported, and are only used for logic $remove_from_members = array("ea_deleted", "ear_deleted", "primary_address"); $focus = 0; $bean = $beanList[$type]; require_once $beanFiles[$bean]; $focus = new $bean(); $searchFields = array(); $db = DBManagerFactory::getInstance(); if ($records) { $records = explode(',', $records); $records = "'" . implode("','", $records) . "'"; $where = "{$focus->table_name}.id in ({$records})"; } elseif (isset($_REQUEST['all'])) { $where = ''; } else { if (!empty($_REQUEST['current_post'])) { $ret_array = generateSearchWhere($type, $_REQUEST['current_post']); $where = $ret_array['where']; $searchFields = $ret_array['searchFields']; } else { $where = ''; } } $order_by = ""; if ($focus->bean_implements('ACL')) { if (!ACLController::checkAccess($focus->module_dir, 'export', true)) { ACLController::displayNoAccess(); sugar_die(''); } if (ACLController::requireOwner($focus->module_dir, 'export')) { if (!empty($where)) { $where .= ' AND '; } $where .= $focus->getOwnerWhere($current_user->id); } /* BEGIN - SECURITY GROUPS */ if (ACLController::requireSecurityGroup($focus->module_dir, 'export')) { require_once 'modules/SecurityGroups/SecurityGroup.php'; global $current_user; $owner_where = $focus->getOwnerWhere($current_user->id); $group_where = SecurityGroup::getGroupWhere($focus->table_name, $focus->module_dir, $current_user->id); if (!empty($owner_where)) { if (empty($where)) { $where = " (" . $owner_where . " or " . $group_where . ")"; } else { $where .= " AND (" . $owner_where . " or " . $group_where . ")"; } } else { if (!empty($where)) { $where .= ' AND '; } $where .= $group_where; } } /* END - SECURITY GROUPS */ } // Export entire list was broken because the where clause already has "where" in it // and when the query is built, it has a "where" as well, so the query was ill-formed. // Eliminating the "where" here so that the query can be constructed correctly. if ($members == true) { $query = $focus->create_export_members_query($records); } else { $beginWhere = substr(trim($where), 0, 5); if ($beginWhere == "where") { $where = substr(trim($where), 5, strlen($where)); } $query = $focus->create_export_query($order_by, $where); } $result = ''; $populate = false; if ($sample) { $result = $db->limitQuery($query, 0, $sampleRecordNum, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query); if ($focus->_get_num_rows_in_query($query) < 1) { $populate = true; } } else { $result = $db->query($query, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query); } $fields_array = $db->getFieldsArray($result, true); //set up the order on the header row $fields_array = get_field_order_mapping($focus->module_dir, $fields_array); //set up labels to be used for the header row $field_labels = array(); foreach ($fields_array as $key => $dbname) { //Remove fields that are only used for logic if ($members && in_array($dbname, $remove_from_members)) { continue; } //If labels should not be exportable skip them if (isset($focus->field_name_map[$key]) && isset($focus->field_name_map[$key]['exportable']) && $focus->field_name_map[$key]['exportable'] === false) { continue; } //default to the db name of label does not exist $field_labels[$key] = translateForExport($dbname, $focus); } $user_agent = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : ''; if ($locale->getExportCharset() == 'UTF-8' && !preg_match('/macintosh|mac os x|mac_powerpc/i', $user_agent)) { //Bug 55520 - add BOM to the exporting CSV so any symbols are displayed correctly in Excel $BOM = ""; $content = $BOM; } else { $content = ''; } // setup the "header" line with proper delimiters $content .= "\"" . implode("\"" . getDelimiter() . "\"", array_values($field_labels)) . "\"\r\n"; $pre_id = ''; if ($populate) { //this is a sample request with no data, so create fake datarows $content .= returnFakeDataRow($focus, $fields_array, $sampleRecordNum); } else { $records = array(); //process retrieved record while ($val = $db->fetchByAssoc($result, false)) { if ($members) { $focus = BeanFactory::getBean($val['related_type']); } else { // field order mapping is not applied for member-exports, as they include multiple modules //order the values in the record array $val = get_field_order_mapping($focus->module_dir, $val); } $new_arr = array(); if ($members) { if ($pre_id == $val['id']) { continue; } if ($val['ea_deleted'] == 1 || $val['ear_deleted'] == 1) { $val['primary_email_address'] = ''; } unset($val['ea_deleted']); unset($val['ear_deleted']); unset($val['primary_address']); } $pre_id = $val['id']; foreach ($val as $key => $value) { //getting content values depending on their types $fieldNameMapKey = $fields_array[$key]; //Dont export fields that have been explicitly marked not to be exportable if (isset($focus->field_name_map[$fieldNameMapKey]) && isset($focus->field_name_map[$fieldNameMapKey]['exportable']) && $focus->field_name_map[$fieldNameMapKey]['exportable'] === false) { continue; } if (isset($focus->field_name_map[$fieldNameMapKey]) && $focus->field_name_map[$fieldNameMapKey]['type']) { $fieldType = $focus->field_name_map[$fieldNameMapKey]['type']; switch ($fieldType) { //if our value is a currency field, then apply the users locale case 'currency': require_once 'modules/Currencies/Currency.php'; $value = currency_format_number($value); break; //if our value is a datetime field, then apply the users locale //if our value is a datetime field, then apply the users locale case 'datetime': case 'datetimecombo': $value = $timedate->to_display_date_time($db->fromConvert($value, 'datetime')); $value = preg_replace('/([pm|PM|am|AM]+)/', ' \\1', $value); break; //kbrill Bug #16296 //kbrill Bug #16296 case 'date': $value = $timedate->to_display_date($db->fromConvert($value, 'date'), false); break; // Bug 32463 - Properly have multienum field translated into something useful for the client // Bug 32463 - Properly have multienum field translated into something useful for the client case 'multienum': $valueArray = unencodeMultiEnum($value); if (isset($focus->field_name_map[$fields_array[$key]]['options']) && isset($app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']])) { foreach ($valueArray as $multikey => $multivalue) { if (isset($app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']][$multivalue])) { $valueArray[$multikey] = $app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']][$multivalue]; } } } $value = implode(",", $valueArray); break; case 'enum': if (isset($focus->field_name_map[$fields_array[$key]]['options']) && isset($app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']]) && isset($app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']][$value])) { $value = $app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']][$value]; } break; } } // Keep as $key => $value for post-processing $new_arr[$key] = preg_replace("/\"/", "\"\"", $value); } // Use Bean ID as key for records $records[$pre_id] = $new_arr; } // Check if we're going to export non-primary emails if ($focus->hasEmails() && in_array('email_addresses_non_primary', $fields_array)) { // $records keys are bean ids $keys = array_keys($records); // Split the ids array into chunks of size 100 $chunks = array_chunk($keys, 100); foreach ($chunks as $chunk) { // Pick all the non-primary mails for the chunk $query = "\n SELECT eabr.bean_id, ea.email_address\n FROM email_addr_bean_rel eabr\n LEFT JOIN email_addresses ea ON ea.id = eabr.email_address_id\n WHERE eabr.bean_module = '{$focus->module_dir}'\n AND eabr.primary_address = '0'\n AND eabr.bean_id IN ('" . implode("', '", $chunk) . "')\n AND eabr.deleted != '1'\n ORDER BY eabr.bean_id, eabr.reply_to_address, eabr.primary_address DESC\n "; $result = $db->query($query, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query); while ($val = $db->fetchByAssoc($result, false)) { if (empty($records[$val['bean_id']]['email_addresses_non_primary'])) { $records[$val['bean_id']]['email_addresses_non_primary'] = $val['email_address']; } else { // No custom non-primary mail delimeter yet, use semi-colon $records[$val['bean_id']]['email_addresses_non_primary'] .= ';' . $val['email_address']; } } } } $customRelateFields = array(); $selects = array(); foreach ($records as $record) { foreach ($record as $recordKey => $recordValue) { if (preg_match('/{relate\\s+from=""([^"]+)""\\s+to=""([^"]+)""}/', $recordValue, $matches)) { $marker = $matches[0]; $relatedValue = ''; $splits = explode('.', $matches[1]); $currentModule = $splits[0]; $currentField = $splits[1]; $currentBean = BeanFactory::getBean($currentModule); $currentTable = $currentBean->table_name; $splits = explode('.', $matches[2]); $relatedModule = $splits[0]; $relatedField = $splits[1]; $relatedBean = BeanFactory::getBean($relatedModule); $relatedTable = $relatedBean->table_name; $relatedLabel = "{$relatedTable}.name AS related_label, NULL AS related_label1"; if (isset($relatedBean->field_defs['name']['source']) && $relatedBean->field_defs['name']['source'] == 'non-db') { //$relatedLabel = 'NULL AS related_label, NULL AS related_label1'; if (!isset($relatedBean->field_defs['first_name']['source']) || $relatedBean->field_defs['first_name']['source'] != 'non-db' && !isset($relatedBean->field_defs['last_name']['source']) || $relatedBean->field_defs['last_name']['source'] != 'non-db') { $relatedLabel = "{$relatedTable}.last_name AS related_label, {$relatedTable}.first_name AS related_label1"; } } $relatedTableCustomJoin = ''; $relatedFieldSelect = "NULL AS related_value"; if (!isset($existsTables["{$relatedTable}_cstm"])) { $existsTables["{$relatedTable}_cstm"] = $db->tableExists("{$relatedTable}_cstm"); } if ($existsTables["{$relatedTable}_cstm"]) { $relatedTableCustomJoin = "\n JOIN {$relatedTable}_cstm ON {$relatedTable}_cstm.id_c = {$currentTable}_cstm.{$relatedField}\n "; $relatedFieldSelect = "{$currentTable}_cstm.{$relatedField} AS related_value"; } $relatedTableJoin = "LEFT JOIN {$relatedTable} ON {$relatedTable}.id = {$currentTable}_cstm.id_c"; if (isset($currentBean->field_defs[$relatedField])) { $relatedTableJoin = "LEFT JOIN {$relatedTable} ON {$relatedTable}.id = {$currentTable}_cstm.{$relatedField}"; } //-- $relatedTable.id AS related_id, //-- {$currentTable}_cstm.id_c AS current_id_c, //-- {$relatedTable}_cstm.id_c AS related_id_c, $selects[] = "(SELECT {$currentTable}.id AS current_id,'{$currentModule}' AS current_module,'{$currentField}' AS current_field,'{$relatedModule}' AS related_module,'{$relatedField}' AS related_field,{$relatedFieldSelect},{$relatedLabel} FROM {$currentTable} JOIN {$currentTable}_cstm ON {$currentTable}_cstm.id_c={$currentTable}.id {$relatedTableCustomJoin} {$relatedTableJoin} WHERE {$currentTable}.id='{$record['id']}')"; } } } $selects = array_unique($selects); // grab custom related fields information // query max length optimization, measured by mssql FreeTDS connection too $queryMaxLength = 620000; $query = ''; $i = 0; $selectsCount = count($selects) - 1; foreach ($selects as $select) { $queryTemp = $query . ($i == 0 ? $select : " UNION {$select}"); if ($i == $selectsCount || strlen($queryTemp) > $queryMaxLength) { $result = $db->query($query, 'export error on custom related type: ' . $query); while ($val = $db->fetchByAssoc($result, false)) { $customRelateFields[$val['current_module']][$val['current_id']][$val['related_module']][$val['related_field']] = trim($val['related_label'] . ' ' . $val['related_label1']); } $query = $select; } else { $query = $queryTemp; } $i++; } foreach ($records as $record) { $line = implode("\"" . getDelimiter() . "\"", $record); $line = "\"" . $line; $line .= "\"\r\n"; $line = parseRelateFields($line, $record, $customRelateFields); $content .= $line; } } return $content; }