/** * 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 $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; $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 = ''; $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); } // 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 { //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; } } array_push($new_arr, preg_replace("/\"/", "\"\"", $value)); } $line = implode("\"" . getDelimiter() . "\"", $new_arr); $line = "\"" . $line; $line .= "\"\r\n"; $content .= $line; } } return $content; }
/** * 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; }
/** * getExportContentsFromResult * * This is a function to handle the processing of generating the export contents. * * @param Mixed $focus SugarBean instance we are retrieving export results for * @param Mixed $result database result resource from the export SQL * @param bool $members used to indicate whether or not to apply filtering for header rows; false by default * @param array $remove_from_members Array of header columns to filter out; empty by default * @param bool $populate boolean used to indicate whether or not to populate with test data; false by default * @return string */ function getExportContentFromResult($focus, $result, $members = false, $remove_from_members = array(), $populate = false) { global $current_user, $locale, $app_strings; $sampleRecordNum = 5; $delimiter = getDelimiter(); $timedate = TimeDate::getInstance(); $db = DBManagerFactory::getInstance(); $fields_array = $db->getFieldsArray($result, true); // check if ID field is contained in query result $is_id_exported = in_array('id', $fields_array); //set up the order on the header row $fields_array = get_field_order_mapping($focus->module_dir, $fields_array, true, $focus->fields_to_exclude); //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 = ''; } $pre_id = ''; // Setup the "header" row with proper delimiters $content .= "\"" . implode("\"" . getDelimiter() . "\"", array_values($field_labels)) . "\"\r\n"; 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 $isAdminUser = is_admin($current_user); 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 (!$isAdminUser) { $focus->id = !empty($val['id']) ? $val['id'] : ''; $focus->assigned_user_id = !empty($val['assigned_user_id']) ? $val['assigned_user_id'] : ''; $focus->created_by = !empty($val['created_by']) ? $val['created_by'] : ''; $focus->ACLFilterFieldList($val, array(), array("blank_value" => true)); } if ($members) { if ($is_id_exported && $pre_id == $val['id']) { continue; } if (isset($val['ea_deleted']) && isset($val['primary_email_address']) && ($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 = $is_id_exported ? $val['id'] : ''; require_once 'include/SugarFields/SugarFieldHandler.php'; //replace user_name with full name if use_real_name preference setting is enabled //and this is a user name field $useRealNames = $current_user->getPreference('use_real_names'); foreach ($val as $key => $value) { //if key is not part of field map, then continue if (!isset($fields_array[$key])) { continue; } //getting content values depending on their types $fieldNameMapKey = $fields_array[$key]; if (isset($focus->field_name_map[$fieldNameMapKey]) && $focus->field_name_map[$fieldNameMapKey]['type']) { $sfh = SugarFieldHandler::getSugarField($focus->field_name_map[$fieldNameMapKey]['type']); $value = $sfh->exportSanitize($value, $focus->field_defs[$key], $focus, $val); } if (isset($focus->field_name_map[$fields_array[$key]]['custom_type']) && $focus->field_name_map[$fields_array[$key]]['custom_type'] == 'teamset') { require_once 'modules/Teams/TeamSetManager.php'; $value = TeamSetManager::getCommaDelimitedTeams($val['team_set_id'], !empty($val['team_id']) ? $val['team_id'] : ''); } if ($useRealNames) { $value = formatRealNameField($focus, $fields_array, $key, $value); } // Keep as $key => $value for post-processing $new_arr[$key] = str_replace('"', '""', $value); } //foreach // Use Bean ID as key for records if it exists if ($is_id_exported) { $records[$pre_id] = $new_arr; } else { $records[] = $new_arr; } } // Check if we're going to export non-primary emails if ($is_id_exported && $focus->hasEmails()) { // Add header column $field_labels['email_addresses_non_primary'] = translateForExport('email_addresses_non_primary', $focus); // $records keys are bean ids $keys = array_keys($records); $email_data = getNonPrimaryEmailsData($focus, $keys); foreach (array_keys($records) as $bean_id) { $records[$bean_id]['email_addresses_non_primary'] = isset($email_data[$bean_id]) ? $email_data[$bean_id] : ''; } } // Write the export data foreach ($records as $record) { $line = implode("\"" . $delimiter . "\"", $record); $line = "\"" . $line; $line .= "\"\r\n"; $content .= $line; } } return $content; }
/** * 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; }