function display() { $GLOBALS['log'] = LoggerManager::getLogger('SugarCRM'); global $sugar_config; global $current_user; global $app_strings; global $timedate; $type = clean_string($_REQUEST['module']); $db = DBManagerFactory::getInstance(); $ids = $_REQUEST['uid']; if ($ids) { $ids = explode(',', $ids); $ids = "'" . implode("','", $ids) . "'"; $where = "in ({$ids})"; } else { if (isset($_REQUEST['all'])) { $where = ''; //get all } else { if (!empty($_REQUEST['current_post'])) { $ret_array = generateSearchWhere($type, $_REQUEST['current_post']); $w = $ret_array['where']; $where = "IN ( SELECT id from grouplists WHERE " . $w . ")"; } else { $where = ''; } } } $focus = new GroupLists(); $start_date = $_REQUEST['start_date']; $end_date = $_REQUEST['end_date']; $template = file_get_contents('modules/GroupLists/tpls/report.tpl'); $template = str_replace('{START}', $start_date, $template); $template = str_replace('{END}', $end_date, $template); $template = str_replace('{LIST_GIT}', $focus->get_GIT_to_report($where), $template); $template = str_replace('{LIST_FIT}', $focus->get_FIT_to_report($where), $template); $size = strlen($template); $filename = "DS KHACH DI TOUR TU " . $start_date . " DEN " . $end_date . ".doc"; ob_end_clean(); header("Cache-Control: private"); header("Content-Type: application/force-download;"); header("Content-Disposition:attachment; filename=\"{$filename}\""); header("Content-length:{$size}"); echo $template; ob_flush(); // header("Location: index.php?module=GroupLists&action=index"); sugar_cleanup(true); }
/** * action map_markers * Google Maps - Output the Map Markers */ function action_map_markers() { header_remove('X-Frame-Options'); $this->view = 'map_markers'; // Define globals for use in the view. $this->bean->map_center = array(); $this->bean->map_markers = array(); $this->bean->map_markers_groups = array(); $this->bean->custom_markers = array(); $this->bean->custom_areas = array(); // Create New Sugar_Smarty Object $this->sugarSmarty = new Sugar_Smarty(); $this->sugarSmarty->assign("mod_strings", $GLOBALS['mod_strings']); $this->sugarSmarty->assign("app_strings", $GLOBALS['app_strings']); $this->sugarSmarty->assign('app_list_strings', $GLOBALS['app_list_strings']); $this->sugarSmarty->assign('moduleListSingular', $GLOBALS['app_list_strings']['moduleListSingular']); $this->sugarSmarty->assign('moduleList', $GLOBALS['app_list_strings']['moduleList']); //echo '<pre>'; //var_dump($_REQUEST); // Related Map Record Defines the Map if (!empty($_REQUEST['record']) || !empty($_REQUEST['relate_id']) && !empty($_REQUEST['relate_module']) || !empty($_REQUEST['quick_address']) && !empty($_REQUEST['display_module'])) { // If map 'record' then define map details from current module. if (@is_guid($_REQUEST['record'])) { // Get the map object $map = get_module_info($GLOBALS['currentModule']); $map->retrieve($_REQUEST['record']); // Define map variables $map_parent_type = $map->parent_type; $map_parent_id = $map->parent_id; $map_module_type = $map->module_type; $map_unit_type = $map->unit_type; $map_distance = $map->distance; } else { if (@(is_guid($_REQUEST['relate_id']) && !empty($_REQUEST['relate_module']))) { // Define map variables $map_parent_type = $_REQUEST['relate_module']; $map_parent_id = $_REQUEST['relate_id']; $map_module_type = !empty($_REQUEST['display_module']) ? $_REQUEST['display_module'] : $_REQUEST['relate_module']; $map_distance = !empty($_REQUEST['distance']) ? $_REQUEST['distance'] : $this->settings['map_default_distance']; $map_unit_type = !empty($_REQUEST['unit_type']) ? $_REQUEST['unit_type'] : $this->settings['map_default_unit_type']; } else { if (!empty($_REQUEST['quick_address']) && !empty($_REQUEST['display_module'])) { // Define map variables / No Parent $map_parent_type = null; $map_parent_id = null; $map_module_type = !empty($_REQUEST['display_module']) ? $_REQUEST['display_module'] : $_REQUEST['relate_module']; $map_distance = !empty($_REQUEST['distance']) ? $_REQUEST['distance'] : $this->settings['map_default_distance']; $map_unit_type = !empty($_REQUEST['unit_type']) ? $_REQUEST['unit_type'] : $this->settings['map_default_unit_type']; } } } // Define display object, note - 'Accounts_Members' is a special display type $this->display_object = $map_module_type == 'Accounts_Members' ? get_module_info('Accounts') : get_module_info($map_module_type); $mod_strings_display = return_module_language($GLOBALS['current_language'], $this->display_object->module_name); $mod_strings_display = array_merge($mod_strings_display, $GLOBALS['mod_strings']); // If relate module/id object if (!empty($map_parent_type) && !empty($map_parent_id)) { // Define relate objects $this->relate_object = get_module_info($map_parent_type); $this->relate_object->retrieve($map_parent_id); $mod_strings_related = return_module_language($GLOBALS['current_language'], $this->relate_object->module_name); $mod_strings_related = array_merge($mod_strings_related, $GLOBALS['mod_strings']); // Get the Relate object Assoc Data $where_conds = $this->relate_object->table_name . ".id = '" . $map_parent_id . "'"; $query = $this->relate_object->create_new_list_query("" . $this->relate_object->table_name . ".assigned_user_id", $where_conds, array(), array(), 0, '', false, $this->relate_object, false); //var_dump($query); $relate_result = $this->bean->db->query($query); $relate = $this->bean->db->fetchByAssoc($relate_result); // Add Relate (Center Point) Marker $this->bean->map_center = $this->getMarkerData($map_parent_type, $relate, true, $mod_strings_related); // Define Center Point $center_lat = $this->relate_object->jjwg_maps_lat_c; $center_lng = $this->relate_object->jjwg_maps_lng_c; } else { // Geocode 'quick_address' $aInfo = $this->bean->getGoogleMapsGeocode($_REQUEST['quick_address'], false, true); // If not status 'OK', then fail here and exit. Note: Inside of iFrame if (!empty($aInfo['status']) && $aInfo['status'] != 'OK' && preg_match('/[A-Z\\_]/', $aInfo['status'])) { echo '<br /><br /><div><b>' . $GLOBALS['mod_strings']['LBL_MAP_LAST_STATUS'] . ': ' . $aInfo['status'] . '</b></div><br /><br />'; exit; } //var_dump($aInfo); // Define Marker Data $aInfo['name'] = $_REQUEST['quick_address']; $aInfo['id'] = 0; $aInfo['module'] = $map_module_type == 'Accounts_Members' ? 'Accounts' : $map_module_type; $aInfo['address'] = $_REQUEST['quick_address']; $aInfo['jjwg_maps_address_c'] = $_REQUEST['quick_address']; $aInfo['jjwg_maps_lat_c'] = $aInfo['lat']; $aInfo['jjwg_maps_lng_c'] = $aInfo['lng']; $this->bean->map_center = $this->getMarkerData($map_parent_type, $aInfo, true); // Define Center Point $center_lat = $aInfo['lat']; $center_lng = $aInfo['lng']; } //var_dump($aInfo); // Define $x and $y expressions $x = '(69.1*((' . $this->display_object->table_name . '_cstm.jjwg_maps_lat_c)-(' . $center_lat . ')))'; $y = '(53.0*((' . $this->display_object->table_name . '_cstm.jjwg_maps_lng_c)-(' . $center_lng . ')) * COS((' . $center_lat . ')/57.1))'; $calc_distance_expression = 'SQRT(' . $x . '*' . $x . '+' . $y . '*' . $y . ')'; if (strtolower($map_unit_type) == 'km' || strtolower($map_unit_type) == 'kilometer') { $calc_distance_expression .= '*1.609'; // 1 mile = 1.609 km } // Find the Items to Display // Assume there is no address at 0,0; it's in the Atlantic Ocean! $where_conds = "(" . $this->display_object->table_name . "_cstm.jjwg_maps_lat_c != 0 OR " . "" . $this->display_object->table_name . "_cstm.jjwg_maps_lng_c != 0) " . " AND " . "(" . $this->display_object->table_name . "_cstm.jjwg_maps_geocode_status_c = 'OK')" . " AND " . "(" . $calc_distance_expression . " < " . $map_distance . ")"; $query = $this->display_object->create_new_list_query('display_object_distance', $where_conds, array(), array(), 0, '', false, $this->display_object, false); // Add the disply_object_distance into SELECT list $query = str_replace('SELECT ', 'SELECT (' . $calc_distance_expression . ') AS display_object_distance, ', $query); if ($map_module_type == 'Contacts') { // Contacts - Account Name $query = str_replace(' FROM contacts ', ' ,accounts.name AS account_name, accounts.id AS account_id FROM contacts ', $query); $query = str_replace(' FROM contacts ', ' FROM contacts LEFT JOIN accounts_contacts ON contacts.id=accounts_contacts.contact_id and accounts_contacts.deleted = 0 LEFT JOIN accounts ON accounts_contacts.account_id=accounts.id AND accounts.deleted=0 ', $query); } elseif ($map_module_type == 'Opportunities') { // Opps - Account Name $query = str_replace(' FROM opportunities ', ' ,accounts.name AS account_name, accounts.id AS account_id FROM opportunities ', $query); $query = str_replace(' FROM opportunities ', ' FROM opportunities LEFT JOIN accounts_opportunities ON opportunities.id=accounts_opportunities.opportunity_id and accounts_opportunities.deleted = 0 LEFT JOIN accounts ON accounts_opportunities.account_id=accounts.id AND accounts.deleted=0 ', $query); } elseif ($map_module_type == 'Accounts_Members') { // 'Accounts_Members' is a special display type $query = str_replace(' AND accounts.deleted=0', ' AND accounts.deleted=0 AND accounts.parent_id = \'' . $this->bean->db->quote($map_parent_id) . '\'', $query); } //var_dump($query); $display_result = $this->bean->db->limitQuery($query, 0, $this->settings['map_markers_limit']); while ($display = $this->bean->db->fetchByAssoc($display_result)) { if (!empty($map_distance) && !empty($display['id'])) { $marker_data_module_type = $map_module_type == 'Accounts_Members' ? 'Accounts' : $map_module_type; $marker_data = $this->getMarkerData($marker_data_module_type, $display, false, $mod_strings_display); if (!empty($marker_data)) { $this->bean->map_markers[] = $marker_data; } } } //var_dump($this->bean->map_markers); // Next define the Custom Markers and Areas related to this Map // Define relate and display objects from the necessary classes (utils.php) @($markers_object = get_module_info('jjwg_Markers')); @($areas_object = get_module_info('jjwg_Areas')); // Relationship Names: jjwg_maps_jjwg_areas and jjwg_maps_jjwg_markers // Find the Related Beans: Maps to Markers if (@(is_object($markers_object) && is_object($map))) { $related_custom_markers = $map->get_linked_beans('jjwg_maps_jjwg_markers', 'jjwg_Markers'); if ($related_custom_markers) { foreach ($related_custom_markers as $marker_bean) { $marker_data = $this->getMarkerDataCustom($marker_bean); if (!empty($marker_data)) { $this->bean->custom_markers[] = $marker_data; } } } } // Find the Related Beans: Maps to Areas if (@(is_object($areas_object) && is_object($map))) { $related_custom_areas = $map->get_linked_beans('jjwg_maps_jjwg_areas', 'jjwg_Areas'); if ($related_custom_areas) { foreach ($related_custom_areas as $area_bean) { $area_data = $this->getAreaDataCustom($area_bean); if (!empty($area_data)) { $this->bean->custom_areas[] = $area_data; } } } } // Map Target List (ProspectLists) } elseif (!empty($_REQUEST['list_id'])) { $this->bean->map_markers = array(); $this->display_object = get_module_info('ProspectLists'); // Use the Export Query if (!empty($_REQUEST['list_id'])) { $this->display_object->retrieve($_REQUEST['list_id']); if ($this->display_object->id == $_REQUEST['list_id']) { $prospect_list_object = $this->display_object; $list_id = $this->display_object->id; } } if (!empty($list_id)) { $list_modules = array('Accounts', 'Contacts', 'Leads', 'Users', 'Prospects'); $temp_marker_groups = array(); foreach ($list_modules as $display_module) { $this->display_object = get_module_info($display_module); $mod_strings_display = return_module_language($GLOBALS['current_language'], $this->display_object->module_name); $mod_strings_display = array_merge($mod_strings_display, $GLOBALS['mod_strings']); // Find the Items to Display // Assume there is no address at 0,0; it's in the Atlantic Ocean! $where_conds = "(" . $this->display_object->table_name . "_cstm.jjwg_maps_lat_c != 0 OR " . "" . $this->display_object->table_name . "_cstm.jjwg_maps_lng_c != 0) " . " AND " . "(" . $this->display_object->table_name . "_cstm.jjwg_maps_geocode_status_c = 'OK')"; $query = $this->display_object->create_new_list_query('', $where_conds, array(), array(), 0, '', false, $this->display_object, false); if ($display_module == 'Contacts') { // Contacts - Account Name $query = str_replace(' FROM contacts ', ' ,accounts.name AS account_name, accounts.id AS account_id FROM contacts ', $query); $query = str_replace(' FROM contacts ', ' FROM contacts LEFT JOIN accounts_contacts ON contacts.id=accounts_contacts.contact_id and accounts_contacts.deleted = 0 LEFT JOIN accounts ON accounts_contacts.account_id=accounts.id AND accounts.deleted=0 ', $query); } // Add List JOIN $query = str_replace(' FROM ' . $this->display_object->table_name . ' ', ' FROM ' . $this->display_object->table_name . ' ' . 'LEFT JOIN prospect_lists_prospects ON prospect_lists_prospects.related_id = ' . $this->display_object->table_name . '.id AND prospect_lists_prospects.deleted=0 ' . 'LEFT JOIN prospect_lists ON prospect_lists_prospects.prospect_list_id = prospect_lists.id AND prospect_lists.deleted=0 ', $query); // Restrict WHERE to related type and $list_id $query .= ' AND prospect_lists_prospects.related_type = \'' . $this->display_object->module_name . '\' AND ' . 'prospect_lists.id = \'' . $this->bean->db->quote($list_id) . '\''; //var_dump($query); $display_result = $this->bean->db->limitQuery($query, 0, $this->settings['map_markers_limit']); $display_type_found = false; while ($display = $this->bean->db->fetchByAssoc($display_result)) { if (!empty($display['id'])) { $marker_data = $this->getMarkerData($display_module, $display, false, $mod_strings_display); $marker_data['group'] = $GLOBALS['app_list_strings']['moduleList'][$display_module]; if (!empty($marker_data)) { $this->bean->map_markers[] = $marker_data; } $display_type_found = true; } } if ($display_type_found) { $temp_marker_groups[] = $GLOBALS['app_list_strings']['moduleList'][$display_module]; } } $this->bean->map_markers_groups = $temp_marker_groups; } // Map Records } elseif (!empty($_REQUEST['uid']) || !empty($_REQUEST['current_post'])) { if (in_array($_REQUEST['display_module'], $this->settings['valid_geocode_modules'])) { $display_module = $_REQUEST['display_module']; } else { $display_module = 'Accounts'; } if ($_REQUEST['current_post'] == 'session') { $current_post = $_SESSION['jjwg_Maps']['current_post']; } else { $current_post = $_REQUEST['current_post']; } $query = ''; $selected_query = ''; $records = array(); $order_by = ''; $this->display_object = get_module_info($display_module); $mod_strings_display = return_module_language($GLOBALS['current_language'], $this->display_object->module_name); $mod_strings_display = array_merge($mod_strings_display, $GLOBALS['mod_strings']); if (!empty($_REQUEST['uid'])) { // Several records selected or this page $records = explode(',', $_REQUEST['uid']); } elseif (!empty($current_post)) { // Select all records (advanced search) $search_array = generateSearchWhere($display_module, $current_post); //var_dump($search_array); if (!empty($search_array['where'])) { // Related Field Bug: Get relate/link patched 'where' and 'join' @($ret_array = create_export_query_relate_link_patch($display_module, $search_array['searchFields'], $search_array['where'])); if (!empty($ret_array['join'])) { @($selected_query = $this->display_object->create_export_query($order_by, $ret_array['where'], $ret_array['join'])); } else { @($selected_query = $this->display_object->create_export_query($order_by, $ret_array['where'])); } // SugarOnDemand JOIN Bug: If $ret_array['join'] is not included in query, force it in! if (strpos($ret_array['join'], $selected_query) === false) { $selected_query = str_replace(' where ', $ret_array['join'] . ' where ', $selected_query); } // Avoiding subquery. Let's just record the record ID's for later $selected_result = $this->bean->db->limitQuery($selected_query, 0, $this->settings['map_markers_limit']); while ($display = $this->bean->db->fetchByAssoc($selected_result)) { $records[] = $display['id']; } } } //var_dump($records); // Find the Items to Display // Assume there is no address at 0,0; it's in the Atlantic Ocean! $where_conds = "(" . $this->display_object->table_name . "_cstm.jjwg_maps_lat_c != 0 OR " . "" . $this->display_object->table_name . "_cstm.jjwg_maps_lng_c != 0) " . " AND " . "(" . $this->display_object->table_name . "_cstm.jjwg_maps_geocode_status_c = 'OK')"; $query = $this->display_object->create_new_list_query('', $where_conds, array(), array(), 0, '', false, $this->display_object, false); if ($display_module == 'Contacts') { // Contacts - Account Name $query = str_replace(' FROM contacts ', ' ,accounts.name AS account_name, accounts.id AS account_id FROM contacts ', $query); $query = str_replace(' FROM contacts ', ' FROM contacts LEFT JOIN accounts_contacts ON contacts.id=accounts_contacts.contact_id and accounts_contacts.deleted = 0 LEFT JOIN accounts ON accounts_contacts.account_id=accounts.id AND accounts.deleted=0 ', $query); } elseif ($display_module == 'Opportunities') { // Opps - Account Name $query = str_replace(' FROM opportunities ', ' ,accounts.name AS account_name, accounts.id AS account_id FROM opportunities ', $query); $query = str_replace(' FROM opportunities ', ' FROM opportunities LEFT JOIN accounts_opportunities ON opportunities.id=accounts_opportunities.opportunity_id and accounts_opportunities.deleted = 0 LEFT JOIN accounts ON accounts_opportunities.account_id=accounts.id AND accounts.deleted=0 ', $query); } //var_dump($query); $display_result = $this->bean->db->limitQuery($query, 0, $this->settings['map_markers_limit']); $this->bean->map_markers = array(); while ($display = $this->bean->db->fetchByAssoc($display_result)) { if (!empty($search_array['where'])) { // Select all records (advanced search) with where clause if (in_array($display['id'], $records)) { $this->bean->map_markers[] = $this->getMarkerData($display_module, $display, false, $mod_strings_display); } } elseif (!empty($_REQUEST['uid'])) { // Several records selected or this page selected if (in_array($display['id'], $records)) { $this->bean->map_markers[] = $this->getMarkerData($display_module, $display, false, $mod_strings_display); } } else { // All $this->bean->map_markers[] = $this->getMarkerData($display_module, $display, false, $mod_strings_display); } } } // Sort marker groups for the view sort($this->bean->map_markers_groups); // Set display object for later use $this->bean->display_object = $this->display_object; // Get Prospect List Array Dropdown $list = get_module_info('ProspectLists'); $list_query = $list->create_list_query('prospect_lists.name', '1=1', 0); $list_result = $list->db->query($list_query); $list_array = array(); while ($alist = $list->db->fetchByAssoc($list_result)) { if (!empty($alist['name']) && !empty($alist['id'])) { $list_array[$alist['id']] = $alist['name']; } } $this->bean->list_array = $list_array; }
/** * 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; }
/** * 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; }
/** * 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 * @param boolean $members * @param boolean $sample return a sample of records for testing * @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; $fields_to_exclude = array(); //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; $focus = BeanFactory::newBean($type); $searchFields = array(); $db = DBManagerFactory::getInstance(); if ($records) { $records = explode(',', $records); $records = "'" . implode("','", array_map(array($db, 'quote'), $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 = ''; } } 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); } } if ($focus->bean_implements('ACL')) { if (!ACLController::checkAccess($focus->module_dir, 'export', true)) { ACLController::displayNoAccess(); sugar_die(''); } $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)); } //get the export query. Note that $focus->fields_to_exclude is a temporary variable and is modified within create_export_query() //$focus->fields_to_exclude will be used later by getExportContentFromResult() to exclude fields from export $focus->fields_to_exclude = array(); $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); } return getExportContentFromResult($focus, $result, $members, $remove_from_members, $sample); }
/** * 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; }
/** * 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; }
global $app_strings; global $timedate; $type = clean_string($_REQUEST['module']); $db = DBManagerFactory::getInstance(); $ids = $_REQUEST['uid']; if ($ids) { $ids = explode(',', $ids); $ids = "'" . implode("','", $ids) . "'"; $where = "({$ids})"; } else { if (isset($_REQUEST['all'])) { $where = ''; //get all } else { if (!empty($_REQUEST['current_post'])) { $ret_array = generateSearchWhere($type, $_REQUEST['current_post']); $w = $ret_array['where']; $where = "( SELECT id from grouplists WHERE " . $w . ")"; } else { $where = ''; } } } $focus = new GroupLists(); $start_date = $_REQUEST['start_date']; $end_date = $_REQUEST['end_date']; $template = file_get_contents('modules/GroupLists/tpls/report.tpl'); $template = str_replace('{START}', $start_date, $template); $template = str_replace('{END}', $end_date, $template); $template = str_replace('{LIST_GIT}', $focus->get_GIT_to_report($where), $template); $template = str_replace('{LIST_FIT}', $focus->get_FIT_to_report($where), $template);