function processSugarBean($html_varName, $seed, $offset)
{
global $row_count, $sugar_config;
global $next_offset;
global $previous_offset;
global $list_view_row_count;
global $current_offset;
if (!empty($sugar_config['disable_vcr'])) {
$seed->retrieve($_REQUEST['record']);
return $seed;
}
$isfirstview = 0;
$nav_history_set = false;
$nav_history_array = array();
$nav_offset = '';
$nav_ids_visited = array();
$nav_stamp = '';
//get the session variable DETAIL_NAV_HISTORY,
//the format of the variable stamp,offset, array of IDs visited.
$nav_history = $this->getLocalSessionVariable($html_varName, "DETAIL_NAV_HISTORY");
if (!empty($nav_history)) {
$nav_history_set = true;
$nav_history_array = explode(":", $nav_history);
$nav_stamp = $nav_history_array[0];
$nav_offset = $nav_history_array[1];
eval("\$nav_ids_visited= " . $nav_history_array[2] . ";");
}
//from list offset is there but $bNavHistorySet is false.
//from next,previous,start and end buttons offset and $bNavHistorySet is true.
//from tracker offset is not there but $bNavHistorySet may or may not exist.
if (isset($_REQUEST['offset']) && !empty($_REQUEST['offset'])) {
//get offset values.
$offset = $_REQUEST['offset'];
if ($offset < 0) {
$offset = 0;
}
//if the stamp has changed, ignore the offset and navigate to the record.
//use case, search, navigate to detail, copy URL, search again, paste URL.
if (!$this->isRequestFromListView($html_varName)) {
$result = $seed->retrieve($_REQUEST['record']);
return $result;
}
if ($nav_history_set) {
if (isset($nav_ids_visited[$offset])) {
unset($nav_ids_visited[$offset]);
}
}
} else {
if ($nav_history_set) {
//try to locate the ID in the nav_history array.
$key = array_search($_REQUEST['record'], $nav_ids_visited);
if ($key === false) {
//do not show the VCR buttons.
$result = $seed->retrieve($_REQUEST['record']);
return $result;
}
$offset = $key;
$_REQUEST['offset'] = $offset;
$_GET['offset'] = $offset;
$_POST['offset'] = $offset;
$_REQUEST['stamp'] = $nav_stamp;
$_GET['stamp'] = $nav_stamp;
$_POST['stamp'] = $nav_stamp;
if (isset($nav_ids_visited[$offset])) {
unset($nav_ids_visited[$offset]);
}
} else {
if (!empty($seed->id)) {
return $seed;
}
$result = $seed->retrieve($_REQUEST['record']);
return $result;
}
}
//Check if this is the first time we have viewed this record
$var = $this->getLocalSessionVariable($html_varName, "IS_FIRST_VIEW");
if (!isset($var) || !$var) {
$isFirstView = true;
} else {
$isFirstView = false;
}
//indicate that this is not the first time anymore
$this->setLocalSessionVariable($html_varName, "IS_FIRST_VIEW", false);
// All 3 databases require this because the limit query does a > db_offset comparison.
$db_offset = $offset - 1;
$this->populateQueryWhere($isFirstView, $html_varName);
if (ACLController::requireOwner($seed->module_dir, 'view')) {
global $current_user;
$seed->getOwnerWhere($current_user->id);
if (!empty($this->query_where)) {
$this->query_where .= ' AND ';
}
$this->query_where .= $seed->getOwnerWhere($current_user->id);
}
/* BEGIN - SECURITY GROUPS */
if (ACLController::requireSecurityGroup($seed->module_dir, 'view')) {
require_once 'modules/SecurityGroups/SecurityGroup.php';
global $current_user;
$owner_where = $seed->getOwnerWhere($current_user->id);
$group_where = SecurityGroup::getGroupWhere($seed->table_name, $seed->module_dir, $current_user->id);
if (empty($this->query_where)) {
$this->query_where = " (" . $owner_where . " or " . $group_where . ")";
} else {
$this->query_where .= " AND (" . $owner_where . " or " . $group_where . ")";
}
}
/* END - SECURITY GROUPS */
$order = $this->getLocalSessionVariable($seed->module_dir . '2_' . $html_varName, "ORDER_BY");
$orderBy = '';
if (!empty($order['orderBy'])) {
$orderBy = $order['orderBy'];
}
if (!empty($orderBy) && !empty($order['direction'])) {
$orderBy .= ' ' . $order['direction'];
}
$this->query_orderby = $seed->process_order_by($orderBy, null);
$current_offset = $_REQUEST['offset'] - 1;
$response = $seed->process_detail_query(SugarVCR::retrieve($seed->module_dir), 0, -1, -1, '', $current_offset);
//$response = $seed->get_detail(, $this->query_where, $db_offset);
$object = $response['bean'];
$row_count = $response['row_count'];
$next_offset = $response['next_offset'];
$previous_offset = $response['previous_offset'];
$list_view_row_count = $row_count;
$this->setListViewRowCount($row_count);
//if the retrieved id is not same as the request ID then hide the VCR buttons.
if (empty($object->id)) {
$this->no_record_found = true;
}
if (empty($_REQUEST['InDetailNav']) and strcmp($_REQUEST['record'], $object->id) != 0) {
$this->offset_key_mismatch = true;
}
if ($this->no_record_found or $this->offset_key_mismatch) {
if ($nav_history_set) {
$this->return_to_list_only = true;
}
$result = $seed->retrieve($_REQUEST['record']);
return $result;
}
//update the request with correct value for the record attribute.
//need only when using the VCR buttons. This is a workaround need to fix the values
//set in the VCR links.
$_REQUEST['record'] = $object->id;
$_GET['record'] = $object->id;
$_POST['record'] = $object->id;
//set nav_history.
if (empty($nav_stamp)) {
$nav_stamp = $_GET['stamp'];
}
if (empty($nav_offset)) {
$nav_offset = $offset;
}
//store a maximum of 20 entries in the nav_ids_visited array.
//remove the oldest entry when this limit is reached.
if (count($nav_ids_visited) >= 20) {
reset($nav_ids_visited);
unset($nav_ids_visited[key($nav_ids_visited)]);
}
$nav_ids_visited[$offset] = $object->id;
$nav_history = sprintf("%s:%s:%s", $nav_stamp, $nav_offset, var_export($nav_ids_visited, true));
$this->setLocalSessionVariable($html_varName, "DETAIL_NAV_HISTORY", $nav_history);
return $object;
}
function findEmailFromBeanIds($beanIds, $beanType, $whereArr)
{
global $current_user;
$q = '';
$whereAdd = "";
$relatedIDs = '';
if ($beanIds != '') {
foreach ($beanIds as $key => $value) {
$beanIds[$key] = '\'' . $value . '\'';
}
$relatedIDs = implode(',', $beanIds);
}
if ($beanType == 'accounts') {
if (isset($whereArr['first_name'])) {
$whereArr['name'] = $whereArr['first_name'];
}
unset($whereArr['last_name']);
unset($whereArr['first_name']);
}
foreach ($whereArr as $column => $clause) {
if (!empty($whereAdd)) {
$whereAdd .= " OR ";
}
$clause = $current_user->db->quote($clause);
$whereAdd .= "{$column} LIKE '{$clause}%'";
}
$table = $beanType;
$module = ucfirst($table);
$class = substr($module, 0, strlen($module) - 1);
require_once "modules/{$module}/{$class}.php";
$person = new $class();
if ($person->ACLAccess('list')) {
if ($relatedIDs != '') {
$where = "({$table}.deleted = 0 AND eabr.primary_address = 1 AND {$table}.id in ({$relatedIDs}))";
} else {
$where = "({$table}.deleted = 0 AND eabr.primary_address = 1)";
}
if (ACLController::requireOwner($module, 'list')) {
$where = $where . " AND ({$table}.assigned_user_id = '{$current_user->id}')";
}
// if
if (!empty($whereAdd)) {
$where .= " AND ({$whereAdd})";
}
if ($beanType === 'accounts') {
$t = "SELECT {$table}.id, '' first_name, {$table}.name last_name, eabr.primary_address, ea.email_address, '{$module}' module ";
} else {
$t = "SELECT {$table}.id, {$table}.first_name, {$table}.last_name, eabr.primary_address, ea.email_address, '{$module}' module ";
}
$t .= "FROM {$table} ";
$t .= "JOIN email_addr_bean_rel eabr ON ({$table}.id = eabr.bean_id and eabr.deleted=0) ";
$t .= "JOIN email_addresses ea ON (eabr.email_address_id = ea.id) ";
$t .= " WHERE {$where}";
/* BEGIN - SECURITY GROUPS */
//this function may not even be used anymore. Seems like findEmailFromBeanIds is preferred now
if ($person->bean_implements('ACL') && ACLController::requireSecurityGroup($module, 'list')) {
require_once 'modules/SecurityGroups/SecurityGroup.php';
global $current_user;
$owner_where = $person->getOwnerWhere($current_user->id);
$group_where = SecurityGroup::getGroupWhere($table, $module, $current_user->id);
$t .= " AND (" . $owner_where . " or " . $group_where . ") ";
}
/* END - SECURITY GROUPS */
}
// if
return $t;
}
/**
* Queries for the list
*/
function get_subscription_lists_query($focus, $additional_fields = null) {
//get all prospect lists belonging to Campaigns of type newsletter
$all_news_type_pl_query = "select c.name, pl.list_type, plc.campaign_id, plc.prospect_list_id";
if(is_array($additional_fields) && !empty($additional_fields)) $all_news_type_pl_query .= ', ' . implode(', ', $additional_fields);
$all_news_type_pl_query .= " from prospect_list_campaigns plc , prospect_lists pl, campaigns c ";
$all_news_type_pl_query .= "where plc.campaign_id = c.id ";
$all_news_type_pl_query .= "and plc.prospect_list_id = pl.id ";
$all_news_type_pl_query .= "and c.campaign_type = 'NewsLetter' and pl.deleted = 0 and c.deleted=0 and plc.deleted=0 ";
$all_news_type_pl_query .= "and (pl.list_type like 'exempt%' or pl.list_type ='default') ";
/* BEGIN - SECURITY GROUPS */
if($focus->bean_implements('ACL') && ACLController::requireSecurityGroup('Campaigns', 'list') )
{
require_once('modules/SecurityGroups/SecurityGroup.php');
global $current_user;
$owner_where = $focus->getOwnerWhere($current_user->id);
$group_where = SecurityGroup::getGroupWhere('c','Campaigns',$current_user->id);
$all_news_type_pl_query .= " AND ( c.assigned_user_id ='".$current_user->id."' or ".$group_where.") ";
}
/* END - SECURITY GROUPS */
$all_news_type_list =$focus->db->query($all_news_type_pl_query);
//build array of all newsletter campaigns
$news_type_list_arr = array();
while ($row = $focus->db->fetchByAssoc($all_news_type_list)){$news_type_list_arr[] = $row;}
//now get all the campaigns that the current user is assigned to
$all_plp_current = "select prospect_list_id from prospect_lists_prospects where related_id = '$focus->id' and deleted = 0 ";
//build array of prospect lists that this user belongs to
$current_plp =$focus->db->query($all_plp_current );
$current_plp_arr = array();
while ($row = $focus->db->fetchByAssoc($current_plp)){$current_plp_arr[] = $row;}
return array('current_plp_arr' => $current_plp_arr, 'news_type_list_arr' => $news_type_list_arr);
}
function get_bean_select_array($add_blank = true, $bean_name, $display_columns, $where = '', $order_by = '', $blank_is_none = false)
{
global $beanFiles;
require_once $beanFiles[$bean_name];
$focus = new $bean_name();
$user_array = array();
$key = $bean_name == 'EmailTemplate' ? $bean_name : $bean_name . $display_columns . $where . $order_by;
$user_array = get_register_value('select_array', $key);
if (!$user_array) {
$db = DBManagerFactory::getInstance();
$temp_result = array();
$query = "SELECT {$focus->table_name}.id, {$display_columns} as display from {$focus->table_name} ";
$query .= "where ";
if ($where != '') {
$query .= $where . " AND ";
}
$query .= " {$focus->table_name}.deleted=0";
/* BEGIN - SECURITY GROUPS */
global $current_user, $sugar_config;
if ($focus->module_dir == 'Users' && !is_admin($current_user) && isset($sugar_config['securitysuite_filter_user_list']) && $sugar_config['securitysuite_filter_user_list'] == true) {
require_once 'modules/SecurityGroups/SecurityGroup.php';
$group_where = SecurityGroup::getGroupUsersWhere($current_user->id);
$query .= " AND (" . $group_where . ") ";
} else {
if ($focus->bean_implements('ACL') && ACLController::requireSecurityGroup($focus->module_dir, 'list')) {
require_once 'modules/SecurityGroups/SecurityGroup.php';
$owner_where = $focus->getOwnerWhere($current_user->id);
$group_where = SecurityGroup::getGroupWhere($focus->table_name, $focus->module_dir, $current_user->id);
if (!empty($owner_where)) {
$query .= " AND (" . $owner_where . " or " . $group_where . ") ";
} else {
$query .= ' AND ' . $group_where;
}
}
}
/* END - SECURITY GROUPS */
if ($order_by != '') {
$query .= " order by {$focus->table_name}.{$order_by}";
}
$GLOBALS['log']->debug("get_user_array query: {$query}");
$result = $db->query($query, true, "Error filling in user array: ");
if ($add_blank == true) {
// Add in a blank row
if ($blank_is_none == true) {
// set 'blank row' to "--None--"
global $app_strings;
$temp_result[''] = $app_strings['LBL_NONE'];
} else {
$temp_result[''] = '';
}
}
// Get the id and the name.
while ($row = $db->fetchByAssoc($result)) {
$temp_result[$row['id']] = $row['display'];
}
$user_array = $temp_result;
set_register_value('select_array', $key, $temp_result);
}
return $user_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);
}
/* 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;
}
function build_report_access_query(SugarBean $module, $alias)
{
$module->table_name = $alias;
$where = '';
if ($module->bean_implements('ACL') && ACLController::requireOwner($module->module_dir, 'list')) {
global $current_user;
$owner_where = $module->getOwnerWhere($current_user->id);
$where = ' AND ' . $owner_where;
}
if (file_exists('modules/SecurityGroups/SecurityGroup.php')) {
/* BEGIN - SECURITY GROUPS */
if ($module->bean_implements('ACL') && ACLController::requireSecurityGroup($module->module_dir, 'list')) {
require_once 'modules/SecurityGroups/SecurityGroup.php';
global $current_user;
$owner_where = $module->getOwnerWhere($current_user->id);
$group_where = SecurityGroup::getGroupWhere($alias, $module->module_dir, $current_user->id);
if (!empty($owner_where)) {
$where .= " AND (" . $owner_where . " or " . $group_where . ") ";
} else {
$where .= ' AND ' . $group_where;
}
}
/* END - SECURITY GROUPS */
}
return $where;
}
function build_from_string()
{
global $db, $app_list_strings, $beanList, $beanFiles, $current_user;
// Create a root GUID
$this->rootGuid = randomstring();
$this->joinSegments = array();
$this->maxDepth = 0;
$kOrgUnits = false;
//check if we do the Org Check
if (file_exists('modules/KOrgObjects/KOrgObject.php') && $GLOBALS['sugarconfig']['orgmanaged']) {
require_once 'modules/KOrgObjects/KOrgObject.php';
$thisKOrgObject = new KOrgObject();
$kOrgUnits = true;
}
/*
* Build the array for the joins based on the various Path we have
*/
foreach ($this->tablePath as $thisPath => $thisPathJoinType) {
// Process backcutting until we have found the node going upwards
// in the segments array or we are on the root segment
// (when no '::' can be found)
if (substr_count($thisPath, '::') > $this->maxDepth) {
$this->maxDepth = substr_count($thisPath, '::');
}
while (strpos($thisPath, '::') && !isset($this->joinSegments[$thisPath])) {
// add the segment to the segments table
$this->joinSegments[$thisPath] = array('alias' => randomstring(), 'linkalias' => randomstring(), 'level' => substr_count($thisPath, '::'), 'jointype' => $thisPathJoinType);
// find last occurence of '::' in the string and cut off there
$thisPath = substr($thisPath, strrpos($thisPath, "::"));
}
}
// Get the main Table we select from
$this->fromString = 'FROM ' . $this->get_table_for_module($this->root_module) . ' ' . $this->rootGuid;
// check if this is an array so we need to add joins ...
// add an entry for the root Object ...
// needed as reference for the GUID
$this->joinSegments['root:' . $this->root_module] = array('alias' => $this->rootGuid, 'level' => 0);
// get ther root Object
require_once $beanFiles[$beanList[$this->root_module]];
$this->joinSegments['root:' . $this->root_module]['object'] = new $beanList[$this->root_module]();
// check for Custom Fields
if ($this->joinSegments['root:' . $this->root_module]['object']->hasCustomFields()) {
$this->joinSegments['root:' . $this->root_module]['customjoin'] = randomstring();
$this->fromString .= ' LEFT JOIN ' . $this->get_table_for_module($this->root_module) . '_cstm as ' . $this->joinSegments['root:' . $this->root_module]['customjoin'] . ' ON ' . $this->rootGuid . '.id = ' . $this->joinSegments['root:' . $this->root_module]['customjoin'] . '.id_c';
}
// changed so we spport teams in Pro
if ($this->authChecklevel != 'none') {
switch ($GLOBALS['sugar_config']['KReports']['authCheck']) {
case 'KOrgObjects':
$this->fromString .= $thisKOrgObject->getOrgunitJoin($this->joinSegments['root:' . $this->root_module]['object']->table_name, $this->joinSegments['root:' . $this->root_module]['object']->object_name, $this->rootGuid, '1');
break;
case 'KAuthObjects':
$selectArray = array('where' => '', 'from' => '', 'select' => '');
$GLOBALS['KAuthAccessController']->addAuthAccessToListArray($selectArray, $this->joinSegments['root:' . $this->root_module]['object'], $this->joinSegments['root:' . $this->root_module]['alias'], true);
if (!empty($selectArray['where'])) {
if (empty($this->whereString)) {
$this->whereString = " " . $selectArray['where'] . " ";
} else {
$this->whereString .= " AND " . $selectArray['where'] . " ";
}
}
if (!empty($selectArray['join'])) {
$this->fromString .= ' ' . $selectArray['join'] . ' ';
}
break;
case 'PRO':
$this->fromString .= ' ';
$this->joinSegments['root:' . $this->root_module]['object']->add_team_security_where_clause($this->fromString, $this->rootGuid);
break;
//2013-03-26 Bug#460 Typo changed
//2013-03-26 Bug#460 Typo changed
case 'SecurityGroups':
if ($this->joinSegments['root:' . $this->root_module]['object']->bean_implements('ACL') && ACLController::requireSecurityGroup($this->joinSegments['root:' . $this->root_module]['object']->module_dir, 'list')) {
require_once 'modules/SecurityGroups/SecurityGroup.php';
global $current_user;
$owner_where = str_replace($this->joinSegments['root:' . $this->root_module]['object']->table_name, $this->rootGuid, $this->joinSegments['root:' . $this->root_module]['object']->getOwnerWhere($current_user->id));
$group_where = SecurityGroup::getGroupWhere($this->rootGuid, $this->joinSegments['root:' . $this->root_module]['object']->module_dir, $current_user->id);
if (!empty($owner_where)) {
if (empty($this->whereString)) {
$this->whereString = " (" . $owner_where . " or " . $group_where . ") ";
} else {
$this->whereString .= " AND (" . $owner_where . " or " . $group_where . ") ";
}
} else {
$this->whereString .= ' AND ' . $group_where;
}
}
break;
}
}
// Index to iterate through the join table building the joins
// from the root object outward going
$levelCounter = 1;
if (is_array($this->joinSegments)) {
while ($levelCounter <= $this->maxDepth) {
// set the array back to the first element in the array
reset($this->joinSegments);
foreach ($this->joinSegments as $thisPath => $thisPathDetails) {
// process only entries for the respective levels
if ($thisPathDetails['level'] == $levelCounter) {
// get the last enrty and the one before and the relevant arrays
$rightPath = substr($thisPath, strrpos($thisPath, "::") + 2, strlen($thisPath));
$leftPath = substr($thisPath, 0, strrpos($thisPath, "::"));
// explode into the relevant arrays
$rightArray = explode(':', $rightPath);
$leftArray = explode(':', $leftPath);
// 2011-07-21 add check for audit records
if ($rightArray[2] == 'audit') {
//handle audit link
$this->fromString .= $thisPathJoinType . $this->joinSegments[$leftPath]['object']->table_name . '_audit ' . $this->joinSegments[$thisPath]['alias'] . ' ON ' . $this->joinSegments[$thisPath]['alias'] . '.parent_id = ' . $this->joinSegments[$leftPath]['alias'] . '.id';
} elseif ($rightArray[0] == 'relationship') {
// set alias for the path to the linkalias of the connected bean
$this->joinSegments[$thisPath]['alias'] = $this->joinSegments[$leftPath]['linkalias'];
} elseif ($rightArray[0] == 'relate') {
//left Path Object must be set since we process from the top
if (!$this->joinSegments[$leftPath]['object'] instanceof $beanList[$rightArray[1]]) {
die('fatal Error in Join');
}
// load the module on the right hand side
require_once $beanFiles[$beanList[$this->joinSegments[$leftPath]['object']->field_defs[$rightArray[2]]['module']]];
$this->joinSegments[$thisPath]['object'] = new $beanList[$this->joinSegments[$leftPath]['object']->field_defs[$rightArray[2]]['module']]();
// join on the id = relate id .. on _cstm if custom field .. on main if regular
$this->fromString .= ' ' . $thisPathDetails['jointype'] . ' ' . $this->joinSegments[$thisPath]['object']->table_name . ' AS ' . $this->joinSegments[$thisPath]['alias'] . ' ON ' . $this->joinSegments[$thisPath]['alias'] . '.id=' . ($this->joinSegments[$leftPath]['object']->field_defs[$this->joinSegments[$leftPath]['object']->field_defs[$rightArray[2]]['id_name']]['source'] == 'custom_fields' ? $this->joinSegments[$leftPath]['customjoin'] : $this->joinSegments[$leftPath]['alias']) . '.' . $this->joinSegments[$leftPath]['object']->field_defs[$rightArray[2]]['id_name'] . ' ';
// check for Custom Fields
if ($this->joinSegments[$thisPath]['object']->hasCustomFields()) {
$this->joinSegments[$thisPath]['customjoin'] = randomstring();
$this->fromString .= ' LEFT JOIN ' . $this->joinSegments[$thisPath]['object']->table_name . '_cstm as ' . $this->joinSegments[$thisPath]['customjoin'] . ' ON ' . $this->joinSegments[$thisPath]['alias'] . '.id = ' . $this->joinSegments[$thisPath]['customjoin'] . '.id_c';
}
} else {
//left Path Object must be set since we process from the top
if (!$this->joinSegments[$leftPath]['object'] instanceof $beanList[$rightArray[1]]) {
$GLOBALS['log']->error('KReporter: fatal error in join with left path ' . $thisPath);
die('fatal Error in Join ' . $thisPath);
}
// load the relationship .. resp link
$this->joinSegments[$leftPath]['object']->load_relationship($rightArray[2]);
// set aliases for left and right .. will be processed properly anyway in the build of the link
// ... funny enough so
//2011-12-29 check if we have a jointpye
if ($thisPathDetails['jointype'] != '') {
//2011-12-29 see if the relationship vuilds on a custom field
if (isset($this->joinSegments[$leftPath]['object']->field_name_map[$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->_relationship->rhs_key]['source']) && ($this->joinSegments[$leftPath]['object']->field_name_map[$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->_relationship->rhs_key]['source'] == 'custom_fields' || $this->joinSegments[$leftPath]['object']->field_name_map[$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->_relationship->lhs_key]['source'] == 'custom_fields')) {
$join_params = array('join_type' => $thisPathDetails['jointype'], 'right_join_table_alias' => $this->joinSegments[$leftPath]['customjoin'], 'left_join_table_alias' => $this->joinSegments[$leftPath]['customjoin'], 'join_table_link_alias' => $this->joinSegments[$thisPath]['linkalias'], 'join_table_alias' => $this->joinSegments[$thisPath]['alias']);
} else {
$join_params = array('join_type' => $thisPathDetails['jointype'], 'right_join_table_alias' => $this->joinSegments[$leftPath]['alias'], 'left_join_table_alias' => $this->joinSegments[$leftPath]['alias'], 'join_table_link_alias' => $this->joinSegments[$thisPath]['linkalias'], 'join_table_alias' => $this->joinSegments[$thisPath]['alias']);
}
//2010-09-09 Bug to handle left side join relationship
if (isset($this->joinSegments[$leftPath]['object']->field_defs[$rightArray[2]]['side']) && $this->joinSegments[$leftPath]['object']->field_defs[$rightArray[2]]['side'] == 'left' && !$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->_swap_sides) {
$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->_swap_sides = true;
}
$linkJoin = $this->joinSegments[$leftPath]['object']->{$rightArray}[2]->getJoin($join_params);
$this->fromString .= ' ' . $linkJoin;
}
// load the module on the right hand side
require_once $beanFiles[$beanList[$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->getRelatedModuleName()]];
$this->joinSegments[$thisPath]['object'] = new $beanList[$this->joinSegments[$leftPath]['object']->{$rightArray}[2]->getRelatedModuleName()]();
//bugfix 2010-08-19, respect ACL role access for owner reuqired in select
if ($this->joinSegments[$leftPath]['object']->bean_implements('ACL') && ACLController::requireOwner($this->joinSegments[$leftPath]['object']->module_dir, 'list')) {
//2013-02-22 missing check if we have a wherestring at all
if ($this->whereString != '') {
$this->whereString .= ' AND ';
}
$this->whereString .= $this->joinSegments[$leftPath]['alias'] . '.assigned_user_id=\'' . $current_user->id . '\'';
}
// check for Custom Fields
if ($this->joinSegments[$thisPath]['object']->hasCustomFields()) {
$this->joinSegments[$thisPath]['customjoin'] = randomstring();
$this->fromString .= ' LEFT JOIN ' . $this->joinSegments[$thisPath]['object']->table_name . '_cstm as ' . $this->joinSegments[$thisPath]['customjoin'] . ' ON ' . $this->joinSegments[$thisPath]['alias'] . '.id = ' . $this->joinSegments[$thisPath]['customjoin'] . '.id_c';
}
// append join for Orgobjects if Object is OrgManaged
if ($this->authChecklevel != 'none' && $this->authChecklevel != 'top') {
switch ($GLOBALS['sugar_config']['KReports']['authCheck']) {
case 'KOrgObjects':
$this->fromString .= $thisKOrgObject->getOrgunitJoin($this->joinSegments[$thisPath]['object']->table_name, $this->joinSegments[$thisPath]['object']->object_name, $this->joinSegments[$thisPath]['alias'], '1');
break;
case 'KAuthObjects':
$selectArray = array('where' => '', 'from' => '', 'select' => '');
$GLOBALS['KAuthAccessController']->addAuthAccessToListArray($selectArray, $this->joinSegments[$thisPath]['object'], $this->joinSegments[$thisPath]['alias'], true);
if (!empty($selectArray['where'])) {
if (empty($this->whereString)) {
$this->whereString = " " . $selectArray['where'] . " ";
} else {
$this->whereString .= " AND " . $selectArray['where'] . " ";
}
}
if (!empty($selectArray['join'])) {
$this->fromString .= ' ' . $selectArray['join'] . ' ';
}
break;
case 'PRO':
$this->fromString .= ' ';
$this->joinSegments[$thisPath]['object']->add_team_security_where_clause($this->fromString, $this->joinSegments[$thisPath]['alias']);
break;
//2013-03-26 Bug#460 Typo changed
//2013-03-26 Bug#460 Typo changed
case 'SecurityGroups':
if ($this->joinSegments[$thisPath]['object']->bean_implements('ACL') && ACLController::requireSecurityGroup($this->joinSegments[$thisPath]['object']->module_dir, 'list')) {
require_once 'modules/SecurityGroups/SecurityGroup.php';
global $current_user;
$owner_where = str_replace($this->joinSegments[$thisPath]['object']->table_name, $this->joinSegments[$thisPath]['alias'], $this->joinSegments[$thisPath]['object']->getOwnerWhere($current_user->id));
$group_where = SecurityGroup::getGroupWhere($this->joinSegments[$thisPath]['alias'], $this->joinSegments[$thisPath]['object']->module_dir, $current_user->id);
if (!empty($owner_where)) {
if (empty($this->whereString)) {
$this->whereString = " (" . $owner_where . " or " . $group_where . ") ";
} else {
$this->whereString .= " AND (" . $owner_where . " or " . $group_where . ") ";
}
} else {
$this->whereString .= ' AND ' . $group_where;
}
}
break;
}
}
}
}
}
// increase Counter to tackle next level
$levelCounter++;
}
}
}
/**
* Return the list query used by the list views and export button. Next generation of create_new_list_query function.
*
* Override this function to return a custom query.
*
* @param string $order_by custom order by clause
* @param string $where custom where clause
* @param array $filter Optioanal
* @param array $params Optional *
* @param int $show_deleted Optional, default 0, show deleted records is set to 1.
* @param string $join_type
* @param boolean $return_array Optional, default false, response as array
* @param object $parentbean creating a subquery for this bean.
* @param boolean $singleSelect Optional, default false.
* @return String select query string, optionally an array value will be returned if $return_array= true.
*/
function create_new_list_query($order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = '', $return_array = false, $parentbean = null, $singleSelect = false, $ifListForExport = false)
{
global $beanFiles, $beanList;
$selectedFields = array();
$secondarySelectedFields = array();
$ret_array = array();
$distinct = '';
if ($this->bean_implements('ACL') && ACLController::requireOwner($this->module_dir, 'list')) {
global $current_user;
$owner_where = $this->getOwnerWhere($current_user->id);
if (empty($where)) {
$where = $owner_where;
} else {
$where .= ' AND ' . $owner_where;
}
}
/* BEGIN - SECURITY GROUPS */
global $current_user, $sugar_config;
if ($this->module_dir == 'Users' && !is_admin($current_user) && isset($sugar_config['securitysuite_filter_user_list']) && $sugar_config['securitysuite_filter_user_list'] == true) {
require_once 'modules/SecurityGroups/SecurityGroup.php';
global $current_user;
$group_where = SecurityGroup::getGroupUsersWhere($current_user->id);
//$group_where = "user_name = 'admin'";
if (empty($where)) {
$where = " (" . $group_where . ") ";
} else {
$where .= " AND (" . $group_where . ") ";
}
} else {
if ($this->bean_implements('ACL') && ACLController::requireSecurityGroup($this->module_dir, 'list')) {
require_once 'modules/SecurityGroups/SecurityGroup.php';
global $current_user;
$owner_where = $this->getOwnerWhere($current_user->id);
$group_where = SecurityGroup::getGroupWhere($this->table_name, $this->module_dir, $current_user->id);
if (!empty($owner_where)) {
if (empty($where)) {
$where = " (" . $owner_where . " or " . $group_where . ") ";
} else {
$where .= " AND (" . $owner_where . " or " . $group_where . ") ";
}
} else {
$where .= ' AND ' . $group_where;
}
}
}
/* END - SECURITY GROUPS */
if (!empty($params['distinct'])) {
$distinct = ' DISTINCT ';
}
if (empty($filter)) {
$ret_array['select'] = " SELECT {$distinct} {$this->table_name}.* ";
} else {
$ret_array['select'] = " SELECT {$distinct} {$this->table_name}.id ";
}
$ret_array['from'] = " FROM {$this->table_name} ";
$ret_array['from_min'] = $ret_array['from'];
$ret_array['secondary_from'] = $ret_array['from'];
$ret_array['where'] = '';
$ret_array['order_by'] = '';
//secondary selects are selects that need to be run after the primary query to retrieve additional info on main
if ($singleSelect) {
$ret_array['secondary_select'] =& $ret_array['select'];
$ret_array['secondary_from'] =& $ret_array['from'];
} else {
$ret_array['secondary_select'] = '';
}
$custom_join = $this->getCustomJoin(empty($filter) ? true : $filter);
if (!isset($params['include_custom_fields']) || $params['include_custom_fields']) {
$ret_array['select'] .= $custom_join['select'];
}
$ret_array['from'] .= $custom_join['join'];
// Bug 52490 - Captivea (Sve) - To be able to add custom fields inside where clause in a subpanel
$ret_array['from_min'] .= $custom_join['join'];
$jtcount = 0;
//LOOP AROUND FOR FIXIN VARDEF ISSUES
require 'include/VarDefHandler/listvardefoverride.php';
if (file_exists('custom/include/VarDefHandler/listvardefoverride.php')) {
require 'custom/include/VarDefHandler/listvardefoverride.php';
}
$joined_tables = array();
if (!empty($params['joined_tables'])) {
foreach ($params['joined_tables'] as $table) {
$joined_tables[$table] = 1;
}
}
if (!empty($filter)) {
$filterKeys = array_keys($filter);
if (is_numeric($filterKeys[0])) {
$fields = array();
foreach ($filter as $field) {
$field = strtolower($field);
//remove out id field so we don't duplicate it
if ($field == 'id' && !empty($filter)) {
continue;
}
if (isset($this->field_defs[$field])) {
$fields[$field] = $this->field_defs[$field];
} else {
$fields[$field] = array('force_exists' => true);
}
}
} else {
$fields = $filter;
}
} else {
$fields = $this->field_defs;
}
$used_join_key = array();
//walk through the fields and for every relationship field add their relationship_info field
//relationshipfield-aliases are resolved in SugarBean::create_new_list_query through their relationship_info field
$addrelate = array();
foreach ($fields as $field => $value) {
if (isset($this->field_defs[$field]) && isset($this->field_defs[$field]['source']) && $this->field_defs[$field]['source'] == 'non-db') {
$addrelatefield = $this->get_relationship_field($field);
if ($addrelatefield) {
$addrelate[$addrelatefield] = true;
}
}
if (!empty($this->field_defs[$field]['id_name'])) {
$addrelate[$this->field_defs[$field]['id_name']] = true;
}
}
$fields = array_merge($addrelate, $fields);
foreach ($fields as $field => $value) {
//alias is used to alias field names
$alias = '';
if (isset($value['alias'])) {
$alias = ' as ' . $value['alias'] . ' ';
}
if (empty($this->field_defs[$field]) || !empty($value['force_blank'])) {
if (!empty($filter) && isset($filter[$field]['force_exists']) && $filter[$field]['force_exists']) {
if (isset($filter[$field]['force_default'])) {
$ret_array['select'] .= ", {$filter[$field]['force_default']} {$field} ";
} else {
//spaces are a fix for length issue problem with unions. The union only returns the maximum number of characters from the first select statement.
$ret_array['select'] .= ", ' ' {$field} ";
}
}
continue;
} else {
$data = $this->field_defs[$field];
}
//ignore fields that are a part of the collection and a field has been removed as a result of
//layout customization.. this happens in subpanel customizations, use case, from the contacts subpanel
//in opportunities module remove the contact_role/opportunity_role field.
if (isset($data['relationship_fields']) and !empty($data['relationship_fields'])) {
$process_field = false;
foreach ($data['relationship_fields'] as $field_name) {
if (isset($fields[$field_name])) {
$process_field = true;
break;
}
}
if (!$process_field) {
continue;
}
}
if ((!isset($data['source']) || $data['source'] == 'db') && (!empty($alias) || !empty($filter))) {
$ret_array['select'] .= ", {$this->table_name}.{$field} {$alias}";
$selectedFields["{$this->table_name}.{$field}"] = true;
} else {
if ((!isset($data['source']) || $data['source'] == 'custom_fields') && (!empty($alias) || !empty($filter))) {
//add this column only if it has NOT already been added to select statement string
$colPos = strpos($ret_array['select'], "{$this->table_name}" . "_cstm" . ".{$field}");
if (!$colPos || $colPos < 0) {
$ret_array['select'] .= ", {$this->table_name}" . "_cstm" . ".{$field} {$alias}";
}
$selectedFields["{$this->table_name}.{$field}"] = true;
}
}
if ($data['type'] != 'relate' && isset($data['db_concat_fields'])) {
$ret_array['select'] .= ", " . $this->db->concat($this->table_name, $data['db_concat_fields']) . " as {$field}";
$selectedFields[$this->db->concat($this->table_name, $data['db_concat_fields'])] = true;
}
//Custom relate field or relate fields built in module builder which have no link field associated.
if ($data['type'] == 'relate' && (isset($data['custom_module']) || isset($data['ext2']))) {
$joinTableAlias = 'jt' . $jtcount;
$relateJoinInfo = $this->custom_fields->getRelateJoin($data, $joinTableAlias, false);
$ret_array['select'] .= $relateJoinInfo['select'];
$ret_array['from'] .= $relateJoinInfo['from'];
//Replace any references to the relationship in the where clause with the new alias
//If the link isn't set, assume that search used the local table for the field
$searchTable = isset($data['link']) ? $relateJoinInfo['rel_table'] : $this->table_name;
$field_name = $relateJoinInfo['rel_table'] . '.' . !empty($data['name']) ? $data['name'] : 'name';
$where = preg_replace('/(^|[\\s(])' . $field_name . '/', '${1}' . $relateJoinInfo['name_field'], $where);
$jtcount++;
}
//Parent Field
if ($data['type'] == 'parent') {
//See if we need to join anything by inspecting the where clause
$match = preg_match('/(^|[\\s(])parent_(\\w+)_(\\w+)\\.name/', $where, $matches);
if ($match) {
$joinTableAlias = 'jt' . $jtcount;
$joinModule = $matches[2];
$joinTable = $matches[3];
$localTable = $this->table_name;
if (!empty($data['custom_module'])) {
$localTable .= '_cstm';
}
global $beanFiles, $beanList, $module;
require_once $beanFiles[$beanList[$joinModule]];
$rel_mod = new $beanList[$joinModule]();
$nameField = "{$joinTableAlias}.name";
if (isset($rel_mod->field_defs['name'])) {
$name_field_def = $rel_mod->field_defs['name'];
if (isset($name_field_def['db_concat_fields'])) {
$nameField = $this->db->concat($joinTableAlias, $name_field_def['db_concat_fields']);
}
}
$ret_array['select'] .= ", {$nameField} {$data['name']} ";
$ret_array['from'] .= " LEFT JOIN {$joinTable} {$joinTableAlias}\n ON {$localTable}.{$data['id_name']} = {$joinTableAlias}.id";
//Replace any references to the relationship in the where clause with the new alias
$where = preg_replace('/(^|[\\s(])parent_' . $joinModule . '_' . $joinTable . '\\.name/', '${1}' . $nameField, $where);
$jtcount++;
}
}
if ($this->is_relate_field($field)) {
$this->load_relationship($data['link']);
if (!empty($this->{$data}['link'])) {
$params = array();
if (empty($join_type)) {
$params['join_type'] = ' LEFT JOIN ';
} else {
$params['join_type'] = $join_type;
}
if (isset($data['join_name'])) {
$params['join_table_alias'] = $data['join_name'];
} else {
$params['join_table_alias'] = 'jt' . $jtcount;
}
if (isset($data['join_link_name'])) {
$params['join_table_link_alias'] = $data['join_link_name'];
} else {
$params['join_table_link_alias'] = 'jtl' . $jtcount;
}
$join_primary = !isset($data['join_primary']) || $data['join_primary'];
$join = $this->{$data}['link']->getJoin($params, true);
$used_join_key[] = $join['rel_key'];
$rel_module = $this->{$data}['link']->getRelatedModuleName();
$table_joined = !empty($joined_tables[$params['join_table_alias']]) || !empty($joined_tables[$params['join_table_link_alias']]) && isset($data['link_type']) && $data['link_type'] == 'relationship_info';
//if rname is set to 'name', and bean files exist, then check if field should be a concatenated name
global $beanFiles, $beanList;
// °3/21/2014 FIX NS-TEAM - Relationship fields could not be displayed in subpanels
//if($data['rname'] && !empty($beanFiles[$beanList[$rel_module]])) {
if (isset($data['rname']) && $data['rname'] == 'name' && !empty($beanFiles[$beanList[$rel_module]])) {
//create an instance of the related bean
require_once $beanFiles[$beanList[$rel_module]];
$rel_mod = new $beanList[$rel_module]();
//if bean has first and last name fields, then name should be concatenated
if (isset($rel_mod->field_name_map['first_name']) && isset($rel_mod->field_name_map['last_name'])) {
$data['db_concat_fields'] = array(0 => 'first_name', 1 => 'last_name');
}
}
if ($join['type'] == 'many-to-many') {
if (empty($ret_array['secondary_select'])) {
$ret_array['secondary_select'] = " SELECT {$this->table_name}.id ref_id ";
if (!empty($beanFiles[$beanList[$rel_module]]) && $join_primary) {
require_once $beanFiles[$beanList[$rel_module]];
$rel_mod = new $beanList[$rel_module]();
if (isset($rel_mod->field_defs['assigned_user_id'])) {
$ret_array['secondary_select'] .= " , " . $params['join_table_alias'] . ".assigned_user_id {$field}_owner, '{$rel_module}' {$field}_mod";
} else {
if (isset($rel_mod->field_defs['created_by'])) {
$ret_array['secondary_select'] .= " , " . $params['join_table_alias'] . ".created_by {$field}_owner , '{$rel_module}' {$field}_mod";
}
}
}
}
if (isset($data['db_concat_fields'])) {
$ret_array['secondary_select'] .= ' , ' . $this->db->concat($params['join_table_alias'], $data['db_concat_fields']) . ' ' . $field;
} else {
if (!isset($data['relationship_fields'])) {
$ret_array['secondary_select'] .= ' , ' . $params['join_table_alias'] . '.' . $data['rname'] . ' ' . $field;
}
}
if (!$singleSelect) {
$ret_array['select'] .= ", ' ' {$field} ";
}
$count_used = 0;
foreach ($used_join_key as $used_key) {
if ($used_key == $join['rel_key']) {
$count_used++;
}
}
if ($count_used <= 1) {
//27416, the $ret_array['secondary_select'] should always generate, regardless the dbtype
// add rel_key only if it was not aready added
if (!$singleSelect) {
$ret_array['select'] .= ", ' ' " . $join['rel_key'] . ' ';
}
$ret_array['secondary_select'] .= ', ' . $params['join_table_link_alias'] . '.' . $join['rel_key'] . ' ' . $join['rel_key'];
}
if (isset($data['relationship_fields'])) {
foreach ($data['relationship_fields'] as $r_name => $alias_name) {
if (!empty($secondarySelectedFields[$alias_name])) {
continue;
}
$ret_array['secondary_select'] .= ', ' . $params['join_table_link_alias'] . '.' . $r_name . ' ' . $alias_name;
$secondarySelectedFields[$alias_name] = true;
}
}
if (!$table_joined) {
$ret_array['secondary_from'] .= ' ' . $join['join'] . ' AND ' . $params['join_table_alias'] . '.deleted=0';
if (isset($data['link_type']) && $data['link_type'] == 'relationship_info' && $parentbean instanceof SugarBean) {
$ret_array['secondary_where'] = $params['join_table_link_alias'] . '.' . $join['rel_key'] . "='" . $parentbean->id . "'";
}
}
} else {
if (isset($data['db_concat_fields'])) {
$ret_array['select'] .= ' , ' . $this->db->concat($params['join_table_alias'], $data['db_concat_fields']) . ' ' . $field;
} else {
$ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.' . $data['rname'] . ' ' . $field;
}
if (isset($data['additionalFields'])) {
foreach ($data['additionalFields'] as $k => $v) {
if (!empty($data['id_name']) && $data['id_name'] == $v && !empty($fields[$data['id_name']])) {
continue;
}
$ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.' . $k . ' ' . $v;
}
}
if (!$table_joined) {
$ret_array['from'] .= ' ' . $join['join'] . ' AND ' . $params['join_table_alias'] . '.deleted=0';
if (!empty($beanList[$rel_module]) && !empty($beanFiles[$beanList[$rel_module]])) {
require_once $beanFiles[$beanList[$rel_module]];
$rel_mod = new $beanList[$rel_module]();
if (isset($value['target_record_key']) && !empty($filter)) {
$selectedFields[$this->table_name . '.' . $value['target_record_key']] = true;
$ret_array['select'] .= " , {$this->table_name}.{$value['target_record_key']} ";
}
if (isset($rel_mod->field_defs['assigned_user_id'])) {
$ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.assigned_user_id ' . $field . '_owner';
} else {
$ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.created_by ' . $field . '_owner';
}
$ret_array['select'] .= " , '" . $rel_module . "' " . $field . '_mod';
}
}
}
// To fix SOAP stuff where we are trying to retrieve all the accounts data where accounts.id = ..
// and this code changes accounts to jt4 as there is a self join with the accounts table.
//Martin fix #27494
if (isset($data['db_concat_fields'])) {
$buildWhere = false;
if (in_array('first_name', $data['db_concat_fields']) && in_array('last_name', $data['db_concat_fields'])) {
$exp = '/\\(\\s*?' . $data['name'] . '.*?\\%\'\\s*?\\)/';
if (preg_match($exp, $where, $matches)) {
$search_expression = $matches[0];
//Create three search conditions - first + last, first, last
$first_name_search = str_replace($data['name'], $params['join_table_alias'] . '.first_name', $search_expression);
$last_name_search = str_replace($data['name'], $params['join_table_alias'] . '.last_name', $search_expression);
$full_name_search = str_replace($data['name'], $this->db->concat($params['join_table_alias'], $data['db_concat_fields']), $search_expression);
$buildWhere = true;
$where = str_replace($search_expression, '(' . $full_name_search . ' OR ' . $first_name_search . ' OR ' . $last_name_search . ')', $where);
}
}
if (!$buildWhere) {
$db_field = $this->db->concat($params['join_table_alias'], $data['db_concat_fields']);
$where = preg_replace('/' . $data['name'] . '/', $db_field, $where);
// For relationship fields replace their alias by the corresponsding link table and r_name
if (isset($data['relationship_fields'])) {
foreach ($data['relationship_fields'] as $r_name => $alias_name) {
$db_field = $this->db->concat($params['join_table_link_alias'], $r_name);
$where = preg_replace('/' . $alias_name . '/', $db_field, $where);
}
}
}
} else {
$where = preg_replace('/(^|[\\s(])' . $data['name'] . '/', '${1}' . $params['join_table_alias'] . '.' . $data['rname'], $where);
// For relationship fields replace their alias by the corresponsding link table and r_name
if (isset($data['relationship_fields'])) {
foreach ($data['relationship_fields'] as $r_name => $alias_name) {
$where = preg_replace('/(^|[\\s(])' . $alias_name . '/', '${1}' . $params['join_table_link_alias'] . '.' . $r_name, $where);
}
}
}
if (!$table_joined) {
$joined_tables[$params['join_table_alias']] = 1;
$joined_tables[$params['join_table_link_alias']] = 1;
}
$jtcount++;
}
}
}
if (!empty($filter)) {
if (isset($this->field_defs['assigned_user_id']) && empty($selectedFields[$this->table_name . '.assigned_user_id'])) {
$ret_array['select'] .= ", {$this->table_name}.assigned_user_id ";
} else {
if (isset($this->field_defs['created_by']) && empty($selectedFields[$this->table_name . '.created_by'])) {
$ret_array['select'] .= ", {$this->table_name}.created_by ";
}
}
if (isset($this->field_defs['system_id']) && empty($selectedFields[$this->table_name . '.system_id'])) {
$ret_array['select'] .= ", {$this->table_name}.system_id ";
}
}
if ($ifListForExport) {
if (isset($this->field_defs['email1'])) {
$ret_array['select'] .= " ,email_addresses.email_address email1";
$ret_array['from'] .= " LEFT JOIN email_addr_bean_rel on {$this->table_name}.id = email_addr_bean_rel.bean_id and email_addr_bean_rel.bean_module='{$this->module_dir}' and email_addr_bean_rel.deleted=0 and email_addr_bean_rel.primary_address=1 LEFT JOIN email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id ";
}
}
$where_auto = '1=1';
if ($show_deleted == 0) {
$where_auto = "{$this->table_name}.deleted=0";
} else {
if ($show_deleted == 1) {
$where_auto = "{$this->table_name}.deleted=1";
}
}
if ($where != "") {
$ret_array['where'] = " where ({$where}) AND {$where_auto}";
} else {
$ret_array['where'] = " where {$where_auto}";
}
//make call to process the order by clause
$order_by = $this->process_order_by($order_by);
if (!empty($order_by)) {
$ret_array['order_by'] = " ORDER BY " . $order_by;
}
if ($singleSelect) {
unset($ret_array['secondary_where']);
unset($ret_array['secondary_from']);
unset($ret_array['secondary_select']);
}
if ($return_array) {
return $ret_array;
}
return $ret_array['select'] . $ret_array['from'] . $ret_array['where'] . $ret_array['order_by'];
}
/**
* Returns a detail object like retrieving of the current object type.
*
* It is intended for use in navigation buttons on the DetailView. It will pass an offset and limit argument to the sql query.
* @internal This method must be called on a new instance. It overrides the values of all the fields in the current one.
*
* @param string $order_by
* @param string $where Additional where clause
* @param int $offset
* @param int $row_offset Optional,default 0, starting row number
* @param int $limit Optional, default -1
* @param int $max Optional, default -1
* @param int $show_deleted Optional, default 0, if set to 1 system will show deleted records.
* @return array Fetched data.
*
* Internal function, do not override.
*/
public function get_detail($order_by = "", $where = "", $offset = 0, $row_offset = 0, $limit = -1, $max = -1, $show_deleted = 0)
{
$GLOBALS['log']->debug("get_detail: order_by = '{$order_by}' and where = '{$where}' and limit = '{$limit}' and offset = '{$offset}'");
if (isset($_SESSION['show_deleted'])) {
$show_deleted = 1;
}
if ($this->bean_implements('ACL') && ACLController::requireOwner($this->module_dir, 'list')) {
global $current_user;
$owner_where = $this->getOwnerWhere($current_user->id);
if (empty($where)) {
$where = $owner_where;
} else {
$where .= ' AND ' . $owner_where;
}
}
/* BEGIN - SECURITY GROUPS */
if ($this->bean_implements('ACL') && ACLController::requireSecurityGroup($this->module_dir, 'list')) {
require_once 'modules/SecurityGroups/SecurityGroup.php';
global $current_user;
$owner_where = $this->getOwnerWhere($current_user->id);
$group_where = SecurityGroup::getGroupWhere($this->table_name, $this->module_dir, $current_user->id);
if (!empty($owner_where)) {
if (empty($where)) {
$where = " (" . $owner_where . " or " . $group_where . ") ";
} else {
$where .= " AND (" . $owner_where . " or " . $group_where . ") ";
}
} else {
$where .= ' AND ' . $group_where;
}
}
/* END - SECURITY GROUPS */
$query = $this->create_new_list_query($order_by, $where, array(), array(), $show_deleted, $offset);
//Add Limit and Offset to query
//$query .= " LIMIT 1 OFFSET $offset";
return $this->process_detail_query($query, $row_offset, $limit, $max, $where, $offset);
}
/**
* builds up a delimited string for export
* @param string type the bean-type to export
* @param array records an array of records if coming directly from a query
* @return string delimited string for export
*/
function export($type, $records = null, $members = false, $sample = false)
{
global $locale;
global $beanList;
global $beanFiles;
global $current_user;
global $app_strings;
global $app_list_strings;
global $timedate;
global $mod_strings;
global $current_language;
$sampleRecordNum = 5;
$contact_fields = array("id" => "Contact ID", "lead_source" => "Lead Source", "date_entered" => "Date Entered", "date_modified" => "Date Modified", "first_name" => "First Name", "last_name" => "Last Name", "salutation" => "Salutation", "birthdate" => "Lead Source", "do_not_call" => "Do Not Call", "email_opt_out" => "Email Opt Out", "title" => "Title", "department" => "Department", "birthdate" => "Birthdate", "do_not_call" => "Do Not Call", "phone_home" => "Phone (Home)", "phone_mobile" => "Phone (Mobile)", "phone_work" => "Phone (Work)", "phone_other" => "Phone (Other)", "phone_fax" => "Fax", "email1" => "Email", "email2" => "Email (Other)", "assistant" => "Assistant", "assistant_phone" => "Assistant Phone", "primary_address_street" => "Primary Address Street", "primary_address_city" => "Primary Address City", "primary_address_state" => "Primary Address State", "primary_address_postalcode" => "Primary Address Postalcode", "primary_address_country" => "Primary Address Country", "alt_address_street" => "Other Address Street", "alt_address_city" => "Other Address City", "alt_address_state" => "Other Address State", "alt_address_postalcode" => "Other Address Postalcode", "alt_address_country" => "Other Address Country", "description" => "Description");
$account_fields = array("id" => "Account ID", "name" => "Account Name", "website" => "Website", "industry" => "Industry", "account_type" => "Type", "ticker_symbol" => "Ticker Symbol", "employees" => "Employees", "ownership" => "Ownership", "phone_office" => "Phone", "phone_fax" => "Fax", "phone_alternate" => "Other Phone", "email1" => "Email", "email2" => "Other Email", "rating" => "Rating", "sic_code" => "SIC Code", "annual_revenue" => "Annual Revenue", "billing_address_street" => "Billing Address Street", "billing_address_city" => "Billing Address City", "billing_address_state" => "Billing Address State", "billing_address_postalcode" => "Billing Address Postalcode", "billing_address_country" => "Billing Address Country", "shipping_address_street" => "Shipping Address Street", "shipping_address_city" => "Shipping Address City", "shipping_address_state" => "Shipping Address State", "shipping_address_postalcode" => "Shipping Address Postalcode", "shipping_address_country" => "Shipping Address Country", "description" => "Description");
//Array of fields that should not be exported, and are only used for logic
$remove_from_members = array("ea_deleted", "ear_deleted", "primary_address");
$focus = 0;
$bean = $beanList[$type];
require_once $beanFiles[$bean];
$focus = new $bean();
$searchFields = array();
$db = DBManagerFactory::getInstance();
if ($records) {
$records = explode(',', $records);
$records = "'" . implode("','", $records) . "'";
$where = "{$focus->table_name}.id in ({$records})";
} elseif (isset($_REQUEST['all'])) {
$where = '';
} else {
if (!empty($_REQUEST['current_post'])) {
$ret_array = generateSearchWhere($type, $_REQUEST['current_post']);
$where = $ret_array['where'];
$searchFields = $ret_array['searchFields'];
} else {
$where = '';
}
}
$order_by = "";
if ($focus->bean_implements('ACL')) {
if (!ACLController::checkAccess($focus->module_dir, 'export', true)) {
ACLController::displayNoAccess();
sugar_die('');
}
if (ACLController::requireOwner($focus->module_dir, 'export')) {
if (!empty($where)) {
$where .= ' AND ';
}
$where .= $focus->getOwnerWhere($current_user->id);
}
/* BEGIN - SECURITY GROUPS */
if (ACLController::requireSecurityGroup($focus->module_dir, 'export')) {
require_once 'modules/SecurityGroups/SecurityGroup.php';
global $current_user;
$owner_where = $focus->getOwnerWhere($current_user->id);
$group_where = SecurityGroup::getGroupWhere($focus->table_name, $focus->module_dir, $current_user->id);
if (!empty($owner_where)) {
if (empty($where)) {
$where = " (" . $owner_where . " or " . $group_where . ")";
} else {
$where .= " AND (" . $owner_where . " or " . $group_where . ")";
}
} else {
if (!empty($where)) {
$where .= ' AND ';
}
$where .= $group_where;
}
}
/* END - SECURITY GROUPS */
}
// Export entire list was broken because the where clause already has "where" in it
// and when the query is built, it has a "where" as well, so the query was ill-formed.
// Eliminating the "where" here so that the query can be constructed correctly.
if ($members == true) {
$query = $focus->create_export_members_query($records);
} else {
$beginWhere = substr(trim($where), 0, 5);
if ($beginWhere == "where") {
$where = substr(trim($where), 5, strlen($where));
}
$query = $focus->create_export_query($order_by, $where);
}
$result = '';
$populate = false;
if ($sample) {
$result = $db->limitQuery($query, 0, $sampleRecordNum, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query);
if ($focus->_get_num_rows_in_query($query) < 1) {
$populate = true;
}
} else {
$result = $db->query($query, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query);
}
$fields_array = $db->getFieldsArray($result, true);
//set up the order on the header row
$fields_array = get_field_order_mapping($focus->module_dir, $fields_array);
//set up labels to be used for the header row
$field_labels = array();
foreach ($fields_array as $key => $dbname) {
//Remove fields that are only used for logic
if ($members && in_array($dbname, $remove_from_members)) {
continue;
}
//default to the db name of label does not exist
$field_labels[$key] = translateForExport($dbname, $focus);
}
$user_agent = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '';
if ($locale->getExportCharset() == 'UTF-8' && !preg_match('/macintosh|mac os x|mac_powerpc/i', $user_agent)) {
//Bug 55520 - add BOM to the exporting CSV so any symbols are displayed correctly in Excel
$BOM = "";
$content = $BOM;
} else {
$content = '';
}
// setup the "header" line with proper delimiters
$content .= "\"" . implode("\"" . getDelimiter() . "\"", array_values($field_labels)) . "\"\r\n";
$pre_id = '';
if ($populate) {
//this is a sample request with no data, so create fake datarows
$content .= returnFakeDataRow($focus, $fields_array, $sampleRecordNum);
} else {
$records = array();
//process retrieved record
while ($val = $db->fetchByAssoc($result, false)) {
//order the values in the record array
$val = get_field_order_mapping($focus->module_dir, $val);
$new_arr = array();
if ($members) {
if ($pre_id == $val['id']) {
continue;
}
if ($val['ea_deleted'] == 1 || $val['ear_deleted'] == 1) {
$val['primary_email_address'] = '';
}
unset($val['ea_deleted']);
unset($val['ear_deleted']);
unset($val['primary_address']);
}
$pre_id = $val['id'];
foreach ($val as $key => $value) {
//getting content values depending on their types
$fieldNameMapKey = $fields_array[$key];
if (isset($focus->field_name_map[$fieldNameMapKey]) && $focus->field_name_map[$fieldNameMapKey]['type']) {
$fieldType = $focus->field_name_map[$fieldNameMapKey]['type'];
switch ($fieldType) {
//if our value is a currency field, then apply the users locale
case 'currency':
require_once 'modules/Currencies/Currency.php';
$value = currency_format_number($value);
break;
//if our value is a datetime field, then apply the users locale
//if our value is a datetime field, then apply the users locale
case 'datetime':
case 'datetimecombo':
$value = $timedate->to_display_date_time($db->fromConvert($value, 'datetime'));
$value = preg_replace('/([pm|PM|am|AM]+)/', ' \\1', $value);
break;
//kbrill Bug #16296
//kbrill Bug #16296
case 'date':
$value = $timedate->to_display_date($db->fromConvert($value, 'date'), false);
break;
// Bug 32463 - Properly have multienum field translated into something useful for the client
// Bug 32463 - Properly have multienum field translated into something useful for the client
case 'multienum':
$value = str_replace("^", "", $value);
if (isset($focus->field_name_map[$fields_array[$key]]['options']) && isset($app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']])) {
$valueArray = explode(",", $value);
foreach ($valueArray as $multikey => $multivalue) {
if (isset($app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']][$multivalue])) {
$valueArray[$multikey] = $app_list_strings[$focus->field_name_map[$fields_array[$key]]['options']][$multivalue];
}
}
$value = implode(",", $valueArray);
}
break;
}
}
// Keep as $key => $value for post-processing
$new_arr[$key] = preg_replace("/\"/", "\"\"", $value);
}
// Use Bean ID as key for records
$records[$pre_id] = $new_arr;
}
// Check if we're going to export non-primary emails
if ($focus->hasEmails() && in_array('email_addresses_non_primary', $fields_array)) {
// $records keys are bean ids
$keys = array_keys($records);
// Split the ids array into chunks of size 100
$chunks = array_chunk($keys, 100);
foreach ($chunks as $chunk) {
// Pick all the non-primary mails for the chunk
$query = "\n SELECT eabr.bean_id, ea.email_address\n FROM email_addr_bean_rel eabr\n LEFT JOIN email_addresses ea ON ea.id = eabr.email_address_id\n WHERE eabr.bean_module = '{$focus->module_dir}'\n AND eabr.primary_address = '0'\n AND eabr.bean_id IN ('" . implode("', '", $chunk) . "')\n AND eabr.deleted != '1'\n ORDER BY eabr.bean_id, eabr.reply_to_address, eabr.primary_address DESC\n ";
$result = $db->query($query, true, $app_strings['ERR_EXPORT_TYPE'] . $type . ": <BR>." . $query);
while ($val = $db->fetchByAssoc($result, false)) {
if (empty($records[$val['bean_id']]['email_addresses_non_primary'])) {
$records[$val['bean_id']]['email_addresses_non_primary'] = $val['email_address'];
} else {
// No custom non-primary mail delimeter yet, use semi-colon
$records[$val['bean_id']]['email_addresses_non_primary'] .= ';' . $val['email_address'];
}
}
}
}
foreach ($records as $record) {
$line = implode("\"" . getDelimiter() . "\"", $record);
$line = "\"" . $line;
$line .= "\"\r\n";
$content .= $line;
}
}
return $content;
}
/**
* 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;
}
*/
require_once 'include/formbase.php';
global $beanFiles, $beanList;
$bean_name = $beanList[$_REQUEST['module']];
require_once $beanFiles[$bean_name];
$focus = new $bean_name();
$uids = array();
if ($_REQUEST['select_entire_list'] == '1') {
$order_by = '';
require_once 'include/MassUpdate.php';
$mass = new MassUpdate();
$mass->generateSearchWhere($_REQUEST['module'], $_REQUEST['current_query_by_page']);
$ret_array = create_export_query_relate_link_patch($_REQUEST['module'], $mass->searchFields, $mass->where_clauses);
/* BEGIN - SECURITY GROUPS */
//need to hijack the $ret_array['where'] of securitygorup required
if ($focus->bean_implements('ACL') && ACLController::requireSecurityGroup($focus->module_dir, 'list')) {
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($ret_array['where'])) {
$ret_array['where'] = " (" . $owner_where . " or " . $group_where . ") ";
} else {
$ret_array['where'] .= " AND (" . $owner_where . " or " . $group_where . ") ";
}
} else {
$ret_array['where'] .= ' AND ' . $group_where;
}
}
/* END - SECURITY GROUPS */
/**
* 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");
$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 = '';
$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); // Fix by Thanh Le
//set up labels to be used for the header row
// $field_labels = $fields_array; // Fix by Thanh Le
foreach ($fields_array as $dbname) {
//default to the db name of label does not exist
$field_labels[$dbname] = translateForExport($dbname, $focus);
}
// setup the "header" line with proper delimiters
$header = implode("\"" . getDelimiter() . "\"", array_values($field_labels));
if ($members) {
$header = str_replace('"ea_deleted"' . getDelimiter() . '"ear_deleted"' . getDelimiter() . '"primary_address"' . getDelimiter() . '', '', $header);
}
$header = "\"" . $header;
$header .= "\"\r\n";
$content .= $header;
$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, -1, false)) {
//order the values in the record array
// $val = get_field_order_mapping($focus->module_dir,$val); // Fix by Thanh Le
$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
$fields_array[$key] = $key;
// Fix by Thanh Le
$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($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 'enum':
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;
}
