public function flush($monitor)
{
$metrics = $monitor->getMetrics();
$columns = array();
$values = array();
foreach ($metrics as $name => $metric) {
if (!empty($monitor->{$name})) {
$columns[] = $name;
if ($metrics[$name]->_type == 'int' || $metrics[$name]->_type == 'double') {
$values[] = $GLOBALS['db']->quote($monitor->{$name});
} else {
if ($metrics[$name]->_type == 'datetime') {
$values[] = $GLOBALS['db']->dbType == 'oci8' ? db_convert("'" . $monitor->{$name} . "'", 'datetime') : "'" . $monitor->{$name} . "'";
} else {
$values[] = "'" . $GLOBALS['db']->quote($monitor->{$name}) . "'";
}
}
}
}
//foreach
if (empty($values)) {
return;
}
if ($monitor->run_count == 1) {
if ($GLOBALS['db']->dbType == 'oci8') {
} else {
$query = "INSERT INTO {$monitor->table_name} (" . implode(",", $columns) . " ) VALUES ( " . implode(",", $values) . ')';
$GLOBALS['db']->query($query);
}
} else {
$query = "UPDATE {$monitor->table_name} set run_count={$monitor->run_count}, sec_avg={$monitor->sec_avg}, sec_total={$monitor->sec_total}, date_modified='{$monitor->date_modified}' where query_hash = '{$monitor->query_hash}'";
$GLOBALS['db']->query($query);
}
}
public function flush($monitor)
{
$metrics = $monitor->getMetrics();
$columns = array();
$values = array();
foreach ($metrics as $name => $metric) {
if (!empty($monitor->{$name})) {
$columns[] = $name;
if ($metrics[$name]->_type == 'int' || $metrics[$name]->_type == 'double') {
$values[] = $GLOBALS['db']->quote($monitor->{$name});
} else {
if ($metrics[$name]->_type == 'datetime') {
$values[] = $GLOBALS['db']->dbType == 'oci8' ? db_convert("'" . $monitor->{$name} . "'", 'datetime') : "'" . $monitor->{$name} . "'";
} else {
$values[] = "'" . $GLOBALS['db']->quote($monitor->{$name}) . "'";
}
}
}
}
//foreach
if (empty($values)) {
return;
}
if ($monitor->round_trips == 1) {
$GLOBALS['db']->query("UPDATE {$monitor->table_name} SET active = 0 WHERE user_id = '{$monitor->user_id}' AND active = '1' AND session_id != '{$monitor->session_id}'");
$query = "INSERT INTO {$monitor->table_name} (" . implode(",", $columns) . " ) VALUES ( " . implode(",", $values) . ')';
$GLOBALS['db']->query($query);
} else {
$query = "UPDATE {$monitor->table_name} SET date_end = '{$monitor->date_end}' , seconds = {$monitor->seconds}, active = '{$monitor->active}', round_trips = {$monitor->round_trips} WHERE session_id = '{$monitor->session_id}'";
$GLOBALS['db']->query($query);
}
}
public function flush($monitor)
{
$metrics = $monitor->getMetrics();
$columns = array();
$values = array();
foreach ($metrics as $name => $metric) {
if (!empty($monitor->{$name})) {
$columns[] = $name;
if ($metrics[$name]->_type == 'int' || $metrics[$name]->_type == 'double') {
$values[] = $GLOBALS['db']->quote($monitor->{$name});
} else {
if ($metrics[$name]->_type == 'datetime') {
$values[] = $GLOBALS['db']->dbType == 'oci8' ? db_convert("'" . $monitor->{$name} . "'", 'datetime') : "'" . $monitor->{$name} . "'";
} else {
$values[] = "'" . $GLOBALS['db']->quote($monitor->{$name}) . "'";
}
}
}
}
//foreach
if (empty($values)) {
return;
}
$query = "INSERT INTO {$monitor->table_name} (" . implode(",", $columns) . " ) VALUES ( " . implode(",", $values) . ')';
$GLOBALS['db']->query($query);
}
protected function getDataset()
{
global $db;
$query = "Select count(id) FROM calls " . " WHERE DATE_FORMAT(calls.date_start, '%Y-%m-%d') >= " . db_convert("'" . $this->v_date_start . "'", 'date') . " AND DATE_FORMAT(calls.date_end, '%Y-%m-%d') <= " . db_convert("'" . $this->v_date_end . "'", 'date') . " AND calls.deleted=0";
if (count($this->v_ids) > 0) {
$query .= " AND calls.assigned_user_id IN ('" . implode("','", $this->v_ids) . "') ";
}
$result = $db->query($query);
$row = $db->fetchByAssoc($result);
$query2 = "Select count(id) FROM meetings " . " WHERE DATE_FORMAT(meetings.date_start, '%Y-%m-%d') >= " . db_convert("'" . $this->v_date_start . "'", 'date') . " AND DATE_FORMAT(meetings.date_end, '%Y-%m-%d') <= " . db_convert("'" . $this->v_date_end . "'", 'date') . " AND meetings.deleted=0";
if (count($this->v_ids) > 0) {
$query2 .= " AND meetings.assigned_user_id IN ('" . implode("','", $this->v_ids) . "') ";
}
$result2 = $db->query($query2);
$row2 = $db->fetchByAssoc($result2);
$query3 = "Select count(id) FROM opportunities";
$query3 .= " WHERE DATE_FORMAT(opportunities.date_closed, '%Y-%m-%d') >= " . db_convert("'" . $this->v_date_start . "'", 'date') . " AND DATE_FORMAT(opportunities.date_closed, '%Y-%m-%d') <= " . db_convert("'" . $this->v_date_end . "'", 'date') . " AND opportunities.deleted=0";
if (count($this->v_ids) > 0) {
$query3 .= " AND opportunities.assigned_user_id IN ('" . implode("','", $this->v_ids) . "') ";
}
$result3 = $db->query($query3);
$row3 = $db->fetchByAssoc($result3);
$query4 = "Select count(id) FROM opportunities WHERE opportunities.sales_stage = 'completed'";
$query4 .= " AND DATE_FORMAT(opportunities.date_closed, '%Y-%m-%d') >= " . db_convert("'" . $this->v_date_start . "'", 'date') . " AND DATE_FORMAT(opportunities.date_closed, '%Y-%m-%d') <= " . db_convert("'" . $this->v_date_end . "'", 'date') . " AND opportunities.deleted=0";
if (count($this->v_ids) > 0) {
$query4 .= " AND opportunities.assigned_user_id IN ('" . implode("','", $this->v_ids) . "') ";
}
$result4 = $db->query($query4);
$row4 = $db->fetchByAssoc($result4);
$returnArray = array(array('a' => 'Звонки', 'total' => $row['count(id)']), array('a' => 'Встречи', 'total' => $row2['count(id)']), array('a' => 'Сделки', 'total' => $row3['count(id)']), array('a' => 'Успешные сделки', 'total' => $row4['count(id)']));
return $returnArray;
}
/**
* @see DashletGenericChart::constructQuery()
*/
protected function constructQuery()
{
$query = "SELECT sales_stage," . db_convert('opportunities.date_closed', 'date_format', array("'%Y-%m'"), array("'YYYY-MM'")) . " as m, " . "sum(amount_usdollar/1000) as total, count(*) as opp_count FROM opportunities ";
$this->getSeedBean()->add_team_security_where_clause($query);
$query .= " WHERE opportunities.date_closed >= " . db_convert("'" . $this->obm_date_start . "'", 'date') . " AND opportunities.date_closed <= " . db_convert("'" . $this->obm_date_end . "'", 'date') . " AND opportunities.deleted=0";
if (count($this->obm_ids) > 0) {
$query .= " AND opportunities.assigned_user_id IN ('" . implode("','", $this->obm_ids) . "')";
}
$query .= " GROUP BY sales_stage," . db_convert('opportunities.date_closed', 'date_format', array("'%Y-%m'"), array("'YYYY-MM'")) . " ORDER BY m";
return $query;
}
public function display()
{
$data = array();
$ss = new Sugar_Smarty();
$focus = BeanFactory::getBean('TeamNotices');
$today = db_convert("'" . TimeDate::getInstance()->nowDbDate() . "'", 'date');
$query = $focus->create_new_list_query("date_start", $focus->table_name . ".date_start <= {$today} and " . $focus->table_name . ".date_end >= {$today} and " . $focus->table_name . '.status=\'Visible\'');
if ($result = $focus->db->query($query)) {
while ($row = $focus->db->fetchByAssoc($result)) {
$data[] = $row;
}
}
$ss->assign("data", $data);
return parent::display() . $ss->fetch('modules/TeamNotices/Dashlets/TeamNoticesDashlet/TeamNoticesDashlet.tpl');
}
function UWrebuild()
{
global $db;
Log::info('Deleting Relationship Cache. Relationships will automatically refresh.');
echo "\n\t<div id='rrresult'></div>\n\t<script>\n\t\tvar xmlhttp=false;\n\t\t/*@cc_on @*/\n\t\t/*@if (@_jscript_version >= 5)\n\t\t// JScript gives us Conditional compilation, we can cope with old IE versions.\n\t\t// and security blocked creation of the objects.\n\t\t try {\n\t\t xmlhttp = new ActiveXObject(\"Msxml2.XMLHTTP\");\n\t\t } catch (e) {\n\t\t try {\n\t\t xmlhttp = new ActiveXObject(\"Microsoft.XMLHTTP\");\n\t\t } catch (E) {\n\t\t xmlhttp = false;\n\t\t }\n\t\t }\n\t\t@end @*/\n\t\tif (!xmlhttp && typeof XMLHttpRequest!='undefined') {\n\t\t\ttry {\n\t\t\t\txmlhttp = new XMLHttpRequest();\n\t\t\t} catch (e) {\n\t\t\t\txmlhttp = false;\n\t\t\t}\n\t\t}\n\t\tif (!xmlhttp && window.createRequest) {\n\t\t\ttry {\n\t\t\t\txmlhttp = window.createRequest();\n\t\t\t} catch (e) {\n\t\t\t\txmlhttp = false;\n\t\t\t}\n\t\t}\n\t\txmlhttp.onreadystatechange = function() {\n\t\t if(xmlhttp.readyState == 4) {\n\t\t document.getElementById('rrresult').innerHTML = xmlhttp.responseText;\n\t\t }\n\t\t }\n\t\txmlhttp.open('GET', 'index.php?module=Administration&action=RebuildRelationship&to_pdf=true', true);\n\t\txmlhttp.send(null);\n\t\t</script>";
Log::info('Rebuilding everything.');
require_once 'ModuleInstall/ModuleInstaller.php';
$mi = new ModuleInstaller();
$mi->rebuild_all();
$query = "DELETE FROM versions WHERE name='Rebuild Extensions'";
Log::info($query);
$db->query($query);
// insert a new database row to show the rebuild extensions is done
$id = create_guid();
$gmdate = TimeDate::getInstance()->nowDb();
$date_entered = db_convert("'{$gmdate}'", 'datetime');
$query = 'INSERT INTO versions (id, deleted, date_entered, date_modified, modified_user_id, created_by, name, file_version, db_version) ' . "VALUES ('{$id}', '0', {$date_entered}, {$date_entered}, '1', '1', 'Rebuild Extensions', '4.0.0', '4.0.0')";
Log::info($query);
$db->query($query);
}
/**
* ShowUsersCumulativeLoggedInTime
*
* Returns a time formmated display of total time logged in for all users from
* the given date interval
*
* @param $data_selected - Optional date filter value (default is one week from today)
* @return Array - dataset
*/
public function ShowUsersCumulativeLoggedInTime($date_selected = null)
{
if (!empty($date_selected)) {
$timeSpan = db_convert("'" . $date_selected . "'", "datetime");
} else {
$firstDay = $GLOBALS['timedate']->get_first_day_of_week($GLOBALS['current_user']);
$timeSpan = db_convert("'" . $GLOBALS['timedate']->getNow()->get_day_by_index_this_week($firstDay)->asDb() . "'", "datetime");
}
$args = array($timeSpan);
$result = $this->execute($this->setup('ShowUsersCumulativeLoggedInTime', $args));
$data = array();
foreach ($result as $row) {
$data[] = array('username' => $row['user_name'], 'total_login_time' => $this->convertSecondsToTime($row['total_login_time'], true));
}
return array_values($data);
}
/**
* runs rebuild scripts
*/
function UWrebuild()
{
global $db;
global $path;
/*
//CCL - Comment this block out, it is called in end.php
logThis('Rebuilding everything...', $path);
require_once('modules/Administration/QuickRepairAndRebuild.php');
$randc = new RepairAndClear();
$randc->repairAndClearAll(array('clearAll'),array(translate('LBL_ALL_MODULES')), false, false);
*/
$query = "DELETE FROM versions WHERE name='Rebuild Extensions'";
$db->query($query);
logThis('Registering rebuild record: ' . $query, $path);
logThis('Rebuild done.', $path);
// insert a new database row to show the rebuild extensions is done
$id = create_guid();
$gmdate = gmdate('Y-m-d H:i:s');
$date_entered = db_convert("'{$gmdate}'", 'datetime');
$query = 'INSERT INTO versions (id, deleted, date_entered, date_modified, modified_user_id, created_by, name, file_version, db_version) ' . "VALUES ('{$id}', '0', {$date_entered}, {$date_entered}, '1', '1', 'Rebuild Extensions', '4.0.0', '4.0.0')";
$db->query($query);
logThis('Registering rebuild record in versions table: ' . $query, $path);
}
/**
* This function retrieves valid jobs, parses the cron format, then returns
* an array of [JOB_ID][EXEC_TIME][JOB]
*
* @return $executeJobs multi-dimensional array
* [job_id][execute_time]
*/
function retrieveSchedulers()
{
$GLOBALS['log']->info('Gathering Schedulers');
$executeJobs = array();
$query = "SELECT id " . "FROM schedulers " . "WHERE deleted=0 " . "AND status = 'Active' " . "AND date_time_start < " . db_convert("'" . gmdate('Y-m-d H:i:s') . "'", 'datetime') . " " . "AND (date_time_end > " . db_convert("'" . gmdate('Y-m-d H:i:s') . "'", 'datetime') . " OR date_time_end IS NULL)";
$result = $this->db->query($query);
$rows = 0;
$executeTimes = array();
$executeIds = array();
$executeJobTimes = array();
while (($arr = $this->db->fetchByAssoc($result)) != null) {
$focus = new Scheduler();
$focus->retrieve($arr['id']);
$executeTimes[$rows] = $this->deriveDBDateTimes($focus);
if (count($executeTimes) > 0) {
foreach ($executeTimes as $k => $time) {
$executeIds[$rows] = $focus->id;
$executeJobTimes[$rows] = $time;
}
}
$rows++;
}
$executeJobs['ids'] = $executeIds;
$executeJobs['times'] = $executeJobTimes;
return $executeJobs;
}
function constructQuery()
{
global $current_user;
global $timedate;
//get the dates to display
$user_date_start = $current_user->getPreference('mypbss_date_start');
if (!empty($user_date_start) && !isset($_REQUEST['mypbss_date_start'])) {
$date_start = $user_date_start;
$GLOBALS['log']->debug("USER PREFERENCES['mypbss_date_start'] is:");
$GLOBALS['log']->debug($user_date_start);
} elseif (isset($_REQUEST['mypbss_date_start']) && $_REQUEST['mypbss_date_start'] != '') {
$date_start = $_REQUEST['mypbss_date_start'];
$current_user->setPreference('mypbss_date_start', $_REQUEST['mypbss_date_start']);
$GLOBALS['log']->debug("_REQUEST['mypbss_date_start'] is:");
$GLOBALS['log']->debug($_REQUEST['mypbss_date_start']);
$GLOBALS['log']->debug("USER PREFERENCES['mypbss_date_start'] is:");
$GLOBALS['log']->debug($current_user->getPreference('mypbss_date_start'));
} else {
$date_start = $timedate->nowDate();
}
$user_date_end = $current_user->getPreference('mypbss_date_end');
if (!empty($user_date_end) && !isset($_REQUEST['mypbss_date_end'])) {
$date_end = $user_date_end;
$GLOBALS['log']->debug("USER PREFERENCES['mypbss_date_end'] is:");
$GLOBALS['log']->debug($user_date_end);
} elseif (isset($_REQUEST['mypbss_date_end']) && $_REQUEST['mypbss_date_end'] != '') {
$date_end = $_REQUEST['mypbss_date_end'];
$current_user->setPreference('mypbss_date_end', $_REQUEST['mypbss_date_end']);
$GLOBALS['log']->debug("_REQUEST['mypbss_date_end'] is:");
$GLOBALS['log']->debug($_REQUEST['mypbss_date_end']);
$GLOBALS['log']->debug("USER PREFERENCES['mypbss_date_end'] is:");
$GLOBALS['log']->debug($current_user->getPreference('mypbss_date_end'));
} else {
$date_end = $timedate->asUserDate($timedate->fromString("2010-01-01"));
$GLOBALS['log']->debug("USER PREFERENCES['mypbss_date_end'] not found. Using: " . $date_end);
}
$user_id = array($current_user->id);
$opp = new Opportunity();
$where = "";
//build the where clause for the query that matches $user
$count = count($user_id);
$id = array();
$user_list = get_user_array(false);
foreach ($user_id as $key) {
$new_ids[$key] = $user_list[$key];
}
if ($count > 0) {
foreach ($new_ids as $the_id => $the_name) {
$id[] = "'" . $the_id . "'";
}
$ids = join(",", $id);
$where .= "opportunities.assigned_user_id IN ({$ids}) ";
}
//build the where clause for the query that matches $datax
$count = count($datax);
$dataxArr = array();
if ($count > 0) {
foreach ($datax as $key => $value) {
$dataxArr[] = "'" . $key . "'";
}
$dataxArr = join(",", $dataxArr);
$where .= "AND opportunities.sales_stage IN\t({$dataxArr}) ";
}
//build the where clause for the query that matches $date_start and $date_end
$where .= "\tAND opportunities.date_closed >= " . db_convert("'" . $date_start . "'", 'date') . "\n\t\t\t\t\tAND opportunities.date_closed <= " . db_convert("'" . $date_end . "'", 'date');
$where .= "\tAND opportunities.assigned_user_id = users.id AND opportunities.deleted=0 ";
//Now do the db queries
//query for opportunity data that matches $datax and $user
$query = "\tSELECT opportunities.sales_stage,\n\t\t\t\t\t\tusers.user_name,\n\t\t\t\t\t\topportunities.assigned_user_id,\n\t\t\t\t\t\tcount( * ) AS opp_count,\n\t\t\t\t\t\tsum(amount_usdollar/1000) AS total\n\t\t\t\t\tFROM users,opportunities ";
$query .= "WHERE " . $where;
$query .= " GROUP BY opportunities.sales_stage,users.user_name,opportunities.assigned_user_id";
return $query;
}
public function rebuildExtensions()
{
global $mod_strings;
if ($this->show_output) {
echo $mod_strings['LBL_QR_REBUILDEXT'];
}
global $current_user;
require_once 'ModuleInstall/ModuleInstaller.php';
$mi = new ModuleInstaller();
$mi->rebuild_all(!$this->show_output);
// Remove the "Rebuild Extensions" red text message on admin logins
if ($this->show_output) {
echo $mod_strings['LBL_REBUILD_REL_UPD_WARNING'];
}
// clear the database row if it exists (just to be sure)
$query = "DELETE FROM versions WHERE name='Rebuild Extensions'";
$GLOBALS['log']->info($query);
$GLOBALS['db']->query($query);
// insert a new database row to show the rebuild extensions is done
$id = create_guid();
$gmdate = gmdate('Y-m-d H:i:s');
$date_entered = db_convert("'{$gmdate}'", 'datetime');
$query = 'INSERT INTO versions (id, deleted, date_entered, date_modified, modified_user_id, created_by, name, file_version, db_version) ' . "VALUES ('{$id}', '0', {$date_entered}, {$date_entered}, '1', '1', 'Rebuild Extensions', '4.0.0', '4.0.0')";
$GLOBALS['log']->info($query);
$GLOBALS['db']->query($query);
// unset the session variable so it is not picked up in DisplayWarnings.php
if (isset($_SESSION['rebuild_extensions'])) {
unset($_SESSION['rebuild_extensions']);
}
}
/**
* Get a list of the relationship records that have been modified within a
* specified date range. This is used to perform a sync with a mobile client.
* The results are paged.
*
* @param xsd:string $session
* @param xsd:string $module_name
* @param xsd:string $related_module
* @param xsd:string $from_date
* @param xsd:string $to_date
* @param xsd:int $offset
* @param xsd:int $max_results
* @param xsd:int $deleted
* @param xsd:int $module_id
* @param tns:select_fields $select_fields
* @param tns:select_fields $ids
* @param xsd:string $relationship_name
* @param xsd:string $deletion_date
* @param xsd:int $php_serialize
* @return
*/
function sync_get_modified_relationships($session, $module_name, $related_module, $from_date, $to_date, $offset, $max_results, $deleted, $module_id = '', $select_fields = array(), $ids = array(), $relationship_name = '', $deletion_date = '', $php_serialize = 1)
{
global $beanList, $beanFiles;
$error = new SoapError();
$output_list = array();
if (!validate_authenticated($session)) {
$error->set_error('invalid_login');
return array('result_count' => -1, 'entry_list' => array(), 'error' => $error->get_soap_array());
}
if (empty($beanList[$module_name]) || empty($beanList[$related_module])) {
$error->set_error('no_module');
return array('result_count' => -1, 'entry_list' => array(), 'error' => $error->get_soap_array());
}
global $current_user;
if (!check_modules_access($current_user, $module_name, 'read') || !check_modules_access($current_user, $related_module, 'read')) {
$error->set_error('no_access');
return array('result_count' => -1, 'entry_list' => array(), 'error' => $error->get_soap_array());
}
// Cast to integer
$deleted = (int) $deleted;
if ($max_results > 0 || $max_results == '-99') {
global $sugar_config;
$sugar_config['list_max_entries_per_page'] = $max_results;
}
$date_query = "(m1.date_modified > " . db_convert("'" . $GLOBALS['db']->quote($from_date) . "'", 'datetime') . " AND m1.date_modified <= " . db_convert("'" . $GLOBALS['db']->quote($to_date) . "'", 'datetime') . " AND {0}.deleted = {$deleted})";
if (isset($deletion_date) && !empty($deletion_date)) {
$date_query .= " OR ({0}.date_modified > " . db_convert("'" . $GLOBALS['db']->quote($deletion_date) . "'", 'datetime') . " AND {0}.date_modified <= " . db_convert("'" . $GLOBALS['db']->quote($to_date) . "'", 'datetime') . " AND {0}.deleted = 1)";
}
$in = '';
if (isset($ids) && !empty($ids)) {
foreach ($ids as $value) {
if (empty($in)) {
$in .= "('" . $GLOBALS['db']->quote($value) . "'";
} else {
$in .= ",'" . $GLOBALS['db']->quote($value) . "'";
}
}
$in .= ')';
}
$query = '';
if (isset($in) && !empty($in)) {
$query .= "( {$date_query} AND m1.id IN {$in}) OR (m1.id NOT IN {$in} AND {0}.deleted = 0)";
} else {
$query .= "( {0}.deleted = 0)";
}
if (isset($module_id) && !empty($module_id)) {
//if(isset($in) && !empty($in)){
$query .= " AND";
//}
$query .= " m2.id = '" . $GLOBALS['db']->quote($module_id) . "'";
}
if ($related_module == 'Meetings' || $related_module == 'Calls') {
$query = string_format($query, array('m1'));
}
$results = retrieve_modified_relationships($module_name, $related_module, $query, $deleted, $offset, $max_results, $select_fields, $relationship_name);
$list = $results['result'];
$xml = '<?xml version="1.0" encoding="utf-8"?><items>';
foreach ($list as $value) {
$val = array_get_return_value($value, $results['table_name']);
if ($php_serialize == 0) {
$xml .= get_name_value_xml($val, $module_name);
}
$output_list[] = $val;
}
$xml .= '</items>';
$next_offset = $offset + sizeof($output_list);
if ($php_serialize == 0) {
$myoutput = base64_encode($xml);
} else {
$myoutput = get_encoded($output_list);
}
return array('result_count' => sizeof($output_list), 'next_offset' => 0, 'total_count' => sizeof($output_list), 'field_list' => array(), 'entry_list' => $myoutput, 'error' => $error->get_soap_array());
}
/**
* Generate the where clause for searching imported emails.
*
*/
function _generateSearchImportWhereClause()
{
global $timedate;
//The clear button was removed so if a user removes the asisgned user name, do not process the id.
if (empty($_REQUEST['assigned_user_name']) && !empty($_REQUEST['assigned_user_id'])) {
unset($_REQUEST['assigned_user_id']);
}
$availableSearchParam = array('name' => array('table_name' => 'emails'), 'data_parent_id_search' => array('table_name' => 'emails', 'db_key' => 'parent_id', 'opp' => '='), 'assigned_user_id' => array('table_name' => 'emails', 'opp' => '='));
$additionalWhereClause = array();
foreach ($availableSearchParam as $key => $properties) {
if (!empty($_REQUEST[$key])) {
$db_key = isset($properties['db_key']) ? $properties['db_key'] : $key;
$searchValue = $_REQUEST[$key];
$opp = isset($properties['opp']) ? $properties['opp'] : 'like';
if ($opp == 'like') {
$searchValue = $searchValue . "%";
}
$additionalWhereClause[] = "{$properties['table_name']}.{$db_key} {$opp} '{$searchValue}' ";
}
}
$isDateFromSearchSet = !empty($_REQUEST['searchDateFrom']);
$isdateToSearchSet = !empty($_REQUEST['searchDateTo']);
$bothDateRangesSet = $isDateFromSearchSet & $isdateToSearchSet;
//Hanlde date from and to seperately
if ($bothDateRangesSet) {
$dbFormatDateFrom = $timedate->to_db_date($_REQUEST['searchDateFrom'], false);
$dbFormatDateFrom = db_convert("'" . $dbFormatDateFrom . "'", 'datetime');
$dbFormatDateTo = $timedate->to_db_date($_REQUEST['searchDateTo'], false);
$dbFormatDateTo = db_convert("'" . $dbFormatDateTo . "'", 'datetime');
$additionalWhereClause[] = "( emails.date_sent >= {$dbFormatDateFrom} AND\n emails.date_sent <= {$dbFormatDateTo} )";
} elseif ($isdateToSearchSet) {
$dbFormatDateTo = $timedate->to_db_date($_REQUEST['searchDateTo'], false);
$dbFormatDateTo = db_convert("'" . $dbFormatDateTo . "'", 'datetime');
$additionalWhereClause[] = "emails.date_sent <= {$dbFormatDateTo} ";
} elseif ($isDateFromSearchSet) {
$dbFormatDateFrom = $timedate->to_db_date($_REQUEST['searchDateFrom'], false);
$dbFormatDateFrom = db_convert("'" . $dbFormatDateFrom . "'", 'datetime');
$additionalWhereClause[] = "emails.date_sent >= {$dbFormatDateFrom} ";
}
$additionalWhereClause = implode(" AND ", $additionalWhereClause);
return $additionalWhereClause;
}
/**
* The fuction will returns an array of filter conditions.
*
*/
function generateSearchWhere($add_custom_fields = false, $module = '')
{
global $timedate;
$values = $this->searchFields;
$where_clauses = array();
$like_char = '%';
$table_name = $this->bean->object_name;
foreach ($this->searchFields as $field => $parms) {
$customField = false;
// Jenny - Bug 7462: We need a type check here to avoid database errors
// when searching for numeric fields. This is a temporary fix until we have
// a generic search form validation mechanism.
$type = !empty($this->bean->field_name_map[$field]['type']) ? $this->bean->field_name_map[$field]['type'] : '';
if (!empty($this->bean->field_name_map[$field]['source']) && $this->bean->field_name_map[$field]['source'] == 'custom_fields') {
$customField = true;
}
if ($type == 'int') {
if (!empty($parms['value'])) {
$tempVal = explode(',', $parms['value']);
$newVal = '';
foreach ($tempVal as $key => $val) {
if (!empty($newVal)) {
$newVal .= ',';
}
if (!empty($val) && !is_numeric($val)) {
$newVal .= -1;
} else {
$newVal .= $val;
}
}
$parms['value'] = $newVal;
}
} elseif ($type == 'bool' && empty($parms['value']) && $customField) {
continue;
} elseif ($type == 'bool' && !empty($parms['value'])) {
if ($parms['value'] == 'on') {
$parms['value'] = 1;
}
}
if (isset($parms['value']) && $parms['value'] != "") {
$operator = 'like';
if (!empty($parms['operator'])) {
$operator = strtolower($parms['operator']);
}
if (is_array($parms['value'])) {
$field_value = '';
// If it is a custom field of mutliselect we have to do some special processing
if ($customField && !empty($this->bean->field_name_map[$field]['isMultiSelect']) && $this->bean->field_name_map[$field]['isMultiSelect']) {
$operator = 'custom_enum';
$db_field = $this->bean->table_name . "_cstm." . $field;
foreach ($parms['value'] as $key => $val) {
if ($val != ' ' and $val != '') {
$qVal = $GLOBALS['db']->quote($val);
if (!empty($field_value)) {
$field_value .= ' or ';
}
$field_value .= "{$db_field} like '{$qVal}' or {$db_field} like '%{$qVal}^%' or {$db_field} like '%^{$qVal}%' or {$db_field} like '%^{$qVal}^%'";
}
}
} else {
$operator = $operator != 'subquery' ? 'in' : $operator;
foreach ($parms['value'] as $key => $val) {
if ($val != ' ' and $val != '') {
if (!empty($field_value)) {
$field_value .= ',';
}
$field_value .= "'" . $GLOBALS['db']->quote($val) . "'";
}
}
}
} else {
$field_value = $GLOBALS['db']->quote($parms['value']);
}
//set db_fields array.
if (!isset($parms['db_field'])) {
$parms['db_field'] = array($field);
}
if (isset($parms['my_items']) and $parms['my_items'] == true) {
global $current_user;
$field_value = $GLOBALS['db']->quote($current_user->id);
$operator = '=';
}
$where = '';
$itr = 0;
if ($field_value != '') {
foreach ($parms['db_field'] as $db_field) {
if (strstr($db_field, '.') === false) {
if (!$customField) {
$db_field = $this->bean->table_name . "." . $db_field;
} else {
$db_field = $this->bean->table_name . "_cstm." . $db_field;
}
}
if ($type == 'date') {
// Collin - Have mysql as first because it's usually the case
// The regular expression check is to circumvent special case YYYY-MM
if ($GLOBALS['db']->dbType == 'mysql') {
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$field_value = $timedate->to_db_date($field_value, false);
$operator = '=';
} else {
$operator = 'db_date';
}
} else {
if ($GLOBALS['db']->dbType == 'oci8') {
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$field_value = $timedate->to_db_date($field_value, false);
$field_value = "to_date('" . $field_value . "', 'YYYY-MM-DD hh24:mi:ss')";
}
$operator = 'db_date';
} else {
if ($GLOBALS['db']->dbType == 'mssql') {
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$field_value = "Convert(DateTime, '" . $timedate->to_db_date($field_value, false) . "')";
}
$operator = 'db_date';
} else {
$field_value = $timedate->to_db_date($field_value, false);
$operation = '=';
}
}
}
}
if ($type == 'datetime' || $type == 'datetimecombo') {
$dates = $timedate->getDayStartEndGMT($field_value);
$field_value = $dates["start"] . "<>" . $dates["end"];
$operator = 'between';
}
if ($GLOBALS['db']->dbType == 'oci8' && isset($parms['query_type']) && $parms['query_type'] == 'case_insensitive') {
$db_field = 'upper(' . $db_field . ")";
$field_value = strtoupper($field_value);
}
$itr++;
if (!empty($where)) {
$where .= " OR ";
}
switch ($operator) {
case 'subquery':
$in = 'IN';
if (isset($parms['subquery_in_clause'])) {
if (!is_array($parms['subquery_in_clause'])) {
$in = $parms['subquery_in_clause'];
} elseif (isset($parms['subquery_in_clause'][$field_value])) {
$in = $parms['subquery_in_clause'][$field_value];
}
}
$sq = $parms['subquery'];
if (is_array($sq)) {
$and_or = ' AND ';
if (isset($sq['OR'])) {
$and_or = ' OR ';
}
$first = true;
foreach ($sq as $q) {
if (empty($q) || strlen($q) < 2) {
continue;
}
if (!$first) {
$where .= $and_or;
}
$where .= " {$db_field} {$in} ({$q} '{$field_value}%') ";
$first = false;
}
} elseif (!empty($parms['query_type']) && $parms['query_type'] == 'format') {
$stringFormatParams = array(0 => $field_value, 1 => $GLOBALS['current_user']->id);
$where .= "{$db_field} {$in} (" . string_format($parms['subquery'], $stringFormatParams) . ")";
} else {
$where .= "{$db_field} {$in} ({$parms['subquery']} '{$field_value}%')";
}
break;
case 'like':
$where .= $db_field . " like '" . $field_value . $like_char . "'";
break;
case 'in':
$where .= $db_field . " in (" . $field_value . ')';
break;
case '=':
$where .= $db_field . " = '" . $field_value . "'";
break;
case 'db_date':
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$where .= $db_field . " = " . $field_value;
} else {
// Create correct date_format conversion String
if ($GLOBALS['db']->dbType == 'oci8') {
$where .= db_convert($db_field, 'date_format', array("'YYYY-MM'")) . " = '" . $field_value . "'";
} else {
$where .= db_convert($db_field, 'date_format', array("'%Y-%m'")) . " = '" . $field_value . "'";
}
}
break;
case 'between':
$field_value = explode('<>', $field_value);
$where .= $db_field . " >= '" . $field_value[0] . "' AND " . $db_field . " <= '" . $field_value[1] . "'";
break;
}
}
}
if (!empty($where)) {
if ($itr > 1) {
array_push($where_clauses, '( ' . $where . ' )');
} else {
array_push($where_clauses, $where);
}
}
}
}
return $where_clauses;
}
if ($_REQUEST['startdate'] && !empty($_REQUEST['startdate'])) {
$startdate = $_REQUEST['startdate'];
}
if ($startdate && !empty($startdate)) {
$where .= "and ec_account.lastorderdate > '{$startdate} 00:00:00' ";
}
if ($_REQUEST['enddate'] && !empty($_REQUEST['enddate'])) {
$enddate = $_REQUEST['enddate'];
}
if ($enddate && !empty($enddate)) {
$where .= "and ec_account.lastorderdate < '{$enddate} 23:59:59' ";
}
}
$findurlstr .= "&stdDateFilter=" . $_REQUEST["stdDateFilter"] . "&startdate=" . $startdate . "&enddate=" . $enddate . "";
$startarr = split("-", $startdate);
$groupdatearr = array("day" => "" . db_convert("ec_account.lastorderdate", 'date_format', array("'%Y-%m-%d'"), array("'{$startarr[0]}'-'{$startarr[1]}'-DD")) . "", "week" => "DATE_FORMAT(ec_account.lastorderdate, '%x %v')", "month" => "" . db_convert('ec_account.lastorderdate', 'date_format', array("'%Y-%m'"), array("'{$startarr[0]}-MM'")) . "", "year" => "" . db_convert("ec_account.lastorderdate", 'date_format', array("'%Y'"), array("'{$startarr[0]}'")) . "");
//统计类型
$grouptype = 'day';
if ($_REQUEST['grouptype'] && !empty($_REQUEST['grouptype'])) {
$grouptype = $_REQUEST['grouptype'];
$findurlstr .= "&grouptype=" . $_REQUEST["grouptype"];
}
$groupsql = $groupdatearr[$grouptype];
$query = "select {$groupsql} as groupdate,count(*) as groupnum from ec_account where ec_account.deleted = 0 and ec_account.smownerid=" . $current_user->id . " ";
if ($where && !empty($where)) {
$query .= $where;
}
$query .= "group by {$groupsql} ";
///page
$currentpage = '1';
if ($_REQUEST['start'] && $_REQUEST['start'] > 0) {
//clean relationship cache..will be rebuilt upon first access.
if (empty($_REQUEST['silent'])) {
echo $mod_strings['LBL_REBUILD_REL_DEL_CACHE'];
}
Relationship::delete_cache();
//////////////////////////////////////////////////////////////////////////////
// Remove the "Rebuild Relationships" red text message on admin logins
if (empty($_REQUEST['silent'])) {
echo $mod_strings['LBL_REBUILD_REL_UPD_WARNING'];
}
// clear the database row if it exists (just to be sure)
$query = "DELETE FROM versions WHERE name='Rebuild Relationships'";
$log->info($query);
$db->query($query);
// insert a new database row to show the rebuild relationships is done
$id = create_guid();
$gmdate = gmdate('Y-m-d H:i:s');
$date_entered = db_convert("'{$gmdate}'", 'datetime');
$query = 'INSERT INTO versions (id, deleted, date_entered, date_modified, modified_user_id, created_by, name, file_version, db_version) ' . "VALUES ('{$id}', '0', {$date_entered}, {$date_entered}, '1', '1', 'Rebuild Relationships', '4.0.0', '4.0.0')";
$log->info($query);
$db->query($query);
$rel = new Relationship();
Relationship::delete_cache();
$rel->build_relationship_cache();
// unset the session variable so it is not picked up in DisplayWarnings.php
if (isset($_SESSION['rebuild_relationships'])) {
unset($_SESSION['rebuild_relationships']);
}
if (empty($_REQUEST['silent'])) {
echo $mod_strings['LBL_DONE'];
}
function server_save_relationships($list, $from_date, $to_date)
{
require_once 'include/utils/db_utils.php';
global $beanList, $beanFiles;
$from_date = db_convert("'" . $from_date . "'", 'datetime');
$to_date = db_convert("'" . $to_date . "'", 'datetime');
global $sugar_config;
$db = DBManagerFactory::getInstance();
$ids = array();
$add = 0;
$modify = 0;
$deleted = 0;
foreach ($list as $record) {
$insert = '';
$insert_values = '';
$update = '';
$select_values = '';
$args = array();
$id = $record['id'];
$table_name = $record['module_name'];
$resolve = 1;
foreach ($record['name_value_list'] as $name_value) {
$name = $name_value['name'];
if ($name == 'date_modified') {
$value = $to_date;
} else {
$value = db_convert("'" . $name_value['value'] . "'", 'varchar');
}
if ($name != 'resolve') {
if (empty($insert)) {
$insert = '(' . $name;
$insert_values = '(' . $value;
if ($name != 'date_modified' && $name != 'id') {
$select_values = $name . "={$value}";
}
if ($name != 'id') {
$update = $name . "={$value}";
}
} else {
$insert .= ', ' . $name;
$insert_values .= ', ' . $value;
if (empty($update)) {
$update .= $name . "={$value}";
} else {
$update .= ',' . $name . "={$value}";
}
if ($name != 'date_modified' && $name != 'id') {
if (empty($select_values)) {
$select_values = $name . "={$value}";
} else {
$select_values .= ' AND ' . $name . "={$value}";
}
}
}
} else {
$resolve = $value;
}
}
//ignore resolve for now server always wins
$resolve = 1;
$insert = "INSERT INTO {$table_name} {$insert}) VALUES {$insert_values})";
$update = "UPDATE {$table_name} SET {$update} WHERE id=";
$delete = "DELETE FROM {$table_name} WHERE id=";
$select_by_id_date = "SELECT id FROM {$table_name} WHERE id ='{$id}' AND date_modified > {$from_date} AND date_modified<= {$to_date}";
$select_by_id = "SELECT id FROM {$table_name} WHERE id ='{$id}'";
$select_by_values = "SELECT id FROM {$table_name} WHERE {$select_values}";
$updated = false;
$result = $db->query($select_by_id_date);
//see if we have a matching id in the date_range
if (!($row = $db->fetchByAssoc($result))) {
//if not lets check if we have one that matches the values
$result = $db->query($select_by_values);
if (!($row = $db->fetchByAssoc($result))) {
$result = $db->query($select_by_id);
if ($row = $db->fetchByAssoc($result)) {
$db->query($update . "'" . $row['id'] . "'");
$ids[] = $row['id'];
$modify++;
} else {
$db->query($insert);
$add++;
$ids[] = $row['id'];
}
}
}
}
return array('add' => $add, 'modify' => $modify, 'ids' => $ids);
}
protected function constructQuery($datay = array(), $targets = array(), $campaign_id, $cache_file_name = 'a_file', $refresh = false, $marketing_id = '', $is_dashlet = false, $dashlet_id = '')
{
//global $app_strings,$mod_strings, $current_module_strings, $charset, $lang, $app_list_strings, $current_language,$sugar_config;
global $mod_strings;
$not_empty = false;
$chartData = array();
$focus = new Campaign();
$focus->retrieve($campaign_id);
$opp_count = 0;
$opp_query = "select count(*) opp_count,sum(" . db_convert("amount_usdollar", "IFNULL", array(0)) . ") total_value";
$opp_query .= " from opportunities";
$opp_query .= " where campaign_id='{$campaign_id}'";
$opp_query .= " and sales_stage='Prospecting'";
$opp_query .= " and deleted=0";
$opp_result = $focus->db->query($opp_query);
$opp_data = $focus->db->fetchByAssoc($opp_result);
if (empty($opp_data['total_value'])) {
$opp_data['total_value'] = 0;
}
$chartData['Total Value'] = $opp_data['total_value'];
//report query
$opp_query1 = "select SUM(opp.amount) as revenue";
$opp_query1 .= " from opportunities opp";
$opp_query1 .= " right join campaigns camp on camp.id = opp.campaign_id";
$opp_query1 .= " where opp.sales_stage = 'Closed Won'and camp.id='{$campaign_id}' and opp.deleted=0";
$opp_query1 .= " group by camp.name";
$opp_result1 = $focus->db->query($opp_query1);
$opp_data1 = $focus->db->fetchByAssoc($opp_result1);
//if (empty($opp_data1[]))
if (empty($opp_data1['revenue'])) {
$opp_data1[$mod_strings['LBL_ROI_CHART_REVENUE']] = 0;
unset($opp_data1['revenue']);
} else {
$opp_data1[$mod_strings['LBL_ROI_CHART_REVENUE']] = $opp_data1['revenue'];
unset($opp_data1['revenue']);
$not_empty = true;
}
$chartData['Revenue'] = $opp_data1[$mod_strings['LBL_ROI_CHART_REVENUE']];
$camp_query1 = "select camp.name, SUM(camp.actual_cost) as investment,SUM(camp.budget) as budget,SUM(camp.expected_revenue) as expected_revenue";
$camp_query1 .= " from campaigns camp";
$camp_query1 .= " where camp.id='{$campaign_id}'";
$camp_query1 .= " group by camp.name";
$camp_result1 = $focus->db->query($camp_query1);
$camp_data1 = $focus->db->fetchByAssoc($camp_result1);
if (empty($camp_data1['investment'])) {
$camp_data1['investment'] = 0;
} else {
$not_empty = true;
}
if (empty($camp_data1['budget'])) {
$camp_data1['budget'] = 0;
} else {
$not_empty = true;
}
if (empty($camp_data1['expected_revenue'])) {
$camp_data1['expected_revenue'] = 0;
} else {
$not_empty = true;
}
$chartData['Investment'] = $camp_data1['investment'];
$chartData['Budget'] = $camp_data1['budget'];
$chartData['Expected Revenue'] = $camp_data1['expected_revenue'];
/*
$opp_data1[$mod_strings['LBL_ROI_CHART_INVESTMENT']]=$camp_data1['investment'];
$opp_data1[$mod_strings['LBL_ROI_CHART_BUDGET']]=$camp_data1['budget'];
$opp_data1[$mod_strings['LBL_ROI_CHART_EXPECTED_REVENUE']]=$camp_data1['expected_revenue'];
$query = "SELECT activity_type,target_type, count(*) hits ";
$query.= " FROM campaign_log ";
$query.= " WHERE campaign_id = '$campaign_id' AND archived=0 AND deleted=0";
//if $marketing id is specified, then lets filter the chart by the value
if (!empty($marketing_id)){
$query.= " AND marketing_id ='$marketing_id'";
}
$query.= " GROUP BY activity_type, target_type";
$query.= " ORDER BY activity_type, target_type";
$result = $focus->db->query($query);
*/
return $chartData;
}
function trimTracker()
{
global $sugar_config, $timedate;
Log::info('----->Scheduler fired job of type trimTracker()');
$db = DBManagerFactory::getInstance();
$admin = new Administration();
$admin->retrieveSettings('tracker');
require 'modules/Trackers/config.php';
$trackerConfig = $tracker_config;
require_once 'include/utils/db_utils.php';
$prune_interval = !empty($admin->settings['tracker_prune_interval']) ? $admin->settings['tracker_prune_interval'] : 30;
foreach ($trackerConfig as $tableName => $tableConfig) {
//Skip if table does not exist
if (!$db->tableExists($tableName)) {
continue;
}
$timeStamp = db_convert("'" . $timedate->asDb($timedate->getNow()->get("-" . $prune_interval . " days")) . "'", "datetime");
if ($tableName == 'tracker_sessions') {
$query = "DELETE FROM {$tableName} WHERE date_end < {$timeStamp}";
} else {
$query = "DELETE FROM {$tableName} WHERE date_modified < {$timeStamp}";
}
Log::info("----->Scheduler is about to trim the {$tableName} table by running the query {$query}");
$db->query($query);
}
//foreach
return true;
}
/**
* @see DashletGenericChart::constructQuery()
*/
protected function constructQuery()
{
$query = " SELECT opportunities.sales_stage,\n users.user_name,\n opportunities.assigned_user_id,\n count(*) AS opp_count,\n sum(amount_usdollar/1000) AS total\n FROM users,opportunities ";
$query .= " WHERE opportunities.date_closed >= " . db_convert("'" . $this->pbss_date_start . "'", 'date') . " AND opportunities.date_closed <= " . db_convert("'" . $this->pbss_date_end . "'", 'date') . " AND opportunities.assigned_user_id = users.id AND opportunities.deleted=0 ";
if (count($this->pbss_sales_stages) > 0) {
$query .= " AND opportunities.sales_stage IN ('" . implode("','", $this->pbss_sales_stages) . "') ";
}
$query .= " GROUP BY opportunities.sales_stage ,users.user_name,opportunities.assigned_user_id";
return $query;
}
function set_relationship($table, $relate_values, $check_duplicates = true, $do_update = false, $data_values = null)
{
$where = '';
// make sure there is a date modified
$date_modified = db_convert("'" . gmdate($GLOBALS['timedate']->get_db_date_time_format()) . "'", 'datetime');
$row = null;
if ($check_duplicates) {
$query = "SELECT * FROM {$table} ";
$where = "WHERE deleted = '0' ";
foreach ($relate_values as $name => $value) {
$where .= " AND {$name} = '{$value}' ";
}
$query .= $where;
$result = $this->db->query($query, false, "Looking For Duplicate Relationship:" . $query);
$row = $this->db->fetchByAssoc($result);
}
if (!$check_duplicates || empty($row)) {
unset($relate_values['id']);
if (isset($data_values)) {
$relate_values = array_merge($relate_values, $data_values);
}
$query = "INSERT INTO {$table} (id, " . implode(',', array_keys($relate_values)) . ", date_modified) VALUES ('" . create_guid() . "', " . "'" . implode("', '", $relate_values) . "', " . $date_modified . ")";
$this->db->query($query, false, "Creating Relationship:" . $query);
} else {
if ($do_update) {
$conds = array();
foreach ($data_values as $key => $value) {
array_push($conds, $key . "='" . $this->db->quote($value) . "'");
}
$query = "UPDATE {$table} SET " . implode(',', $conds) . ",date_modified=" . $date_modified . " " . $where;
$this->db->query($query, false, "Updating Relationship:" . $query);
}
}
}
/**
* function mark_relationships_deleted($id)
*
* special case to delete acl_roles_actions relationship
*
* @param ACLRole GUID $id
*/
function mark_relationships_deleted($id)
{
//we need to delete the actions relationship by hand (special case)
$date_modified = db_convert("'" . TimeDate::getInstance()->nowDb() . "'", 'datetime');
$query = "UPDATE acl_roles_actions SET deleted=1 , date_modified={$date_modified} WHERE role_id = '{$id}' AND deleted=0";
$this->db->query($query);
parent::mark_relationships_deleted($id);
}
/**
* Remove a Security Group from a record
*/
function removeGroupFromRecord($module, $record_id, $securitygroup_id)
{
if (empty($module) || empty($record_id) || empty($securitygroup_id)) {
return;
//missing data
}
global $db;
$query = "update securitygroups_records set deleted = 1, date_modified = " . db_convert('', 'today') . " " . "where securitygroup_id = '" . $securitygroup_id . "' and record_id = '{$record_id}' and module = '{$module}'";
$GLOBALS['log']->debug("SecuritySuite: addGroupToRecord: {$query}");
$db->query($query, true);
}
function constructQuery()
{
global $current_user;
global $timedate;
$user_date_start = $current_user->getPreference('obm_date_start');
if (!empty($user_date_start) && !isset($_REQUEST['obm_date_start'])) {
$date_start = $user_date_start;
Log::debug("USER PREFERENCES['obm_date_start'] is:");
Log::debug($user_date_start);
} elseif (isset($_REQUEST['obm_year']) && $_REQUEST['obm_year'] != '') {
$date_start = $_REQUEST['obm_year'] . '-01-01';
$current_user->setPreference('obm_date_start', $date_start);
Log::debug("_REQUEST['obm_date_start'] is:");
Log::debug($_REQUEST['obm_date_start']);
Log::debug("_SESSION['obm_date_start'] is:");
Log::debug($current_user->getPreference('obm_date_start'));
} else {
$date_start = date('Y') . '-01-01';
}
$user_date_end = $current_user->getPreference('obm_date_end');
if (!empty($user_date_end) && !isset($_REQUEST['obm_date_end'])) {
$date_end = $user_date_end;
Log::debug("USER PREFERENCES['obm_date_end'] is:");
Log::debug($date_end);
} elseif (isset($_REQUEST['obm_year']) && $_REQUEST['obm_year'] != '') {
$date_end = $_REQUEST['obm_year'] . '-12-31';
$current_user->setPreference('obm_date_end', $date_end);
Log::debug("_REQUEST['obm_date_end'] is:");
Log::debug($_REQUEST['obm_date_end']);
Log::debug("USER PREFERENCES['obm_date_end'] is:");
Log::debug($current_user->getPreference('obm_date_end'));
} else {
$date_end = date('Y') . '-12-31';
}
$ids = array();
//get list of user ids for which to display data
$user_ids = $current_user->getPreference('obm_ids');
if (!empty($user_ids) && count($user_ids) != 0 && !isset($_REQUEST['obm_ids'])) {
$ids = $user_ids;
Log::debug("USER PREFERENCES['obm_ids'] is:");
Log::debug($user_ids);
} elseif (isset($_REQUEST['obm_ids']) && count($_REQUEST['obm_ids']) > 0) {
$ids = $_REQUEST['obm_ids'];
$current_user->setPreference('obm_ids', $_REQUEST['obm_ids']);
Log::debug("_REQUEST['obm_ids'] is:");
Log::debug($_REQUEST['obm_ids']);
Log::debug("USER PREFRENCES['obm_ids'] is:");
Log::debug($current_user->getPreference('obm_ids'));
} else {
$ids = get_user_array(false);
$ids = array_keys($ids);
}
$user_id = $ids;
$where = "";
//build the where clause for the query that matches $user
$count = count($user_id);
$id = array();
if ($count > 0) {
foreach ($user_id as $the_id) {
$id[] = "'" . $the_id . "'";
}
$ids = join(",", $id);
$where .= "opportunities.assigned_user_id IN ({$ids}) ";
}
// cn: adding user-pref date handling
$dateStartDisplay = $timedate->asUserDate($timedate->fromString($date_start));
$dateEndDisplay = $timedate->asUserDate($timedate->fromString($date_end));
$opp = new Opportunity();
//build the where clause for the query that matches $date_start and $date_end
$where .= "AND opportunities.date_closed >= " . db_convert("'" . $date_start . "'", 'date') . " AND opportunities.date_closed <= " . db_convert("'" . $date_end . "'", 'date') . " AND opportunities.deleted=0";
$query = "SELECT sales_stage," . db_convert('opportunities.date_closed', 'date_format', array("'%Y-%m'"), array("'YYYY-MM'")) . " as m, sum(amount_usdollar/1000) as total, count(*) as opp_count FROM opportunities ";
$query .= "WHERE " . $where;
$query .= " GROUP BY sales_stage," . db_convert('opportunities.date_closed', 'date_format', array("'%Y-%m'"), array("'YYYY-MM'")) . "ORDER BY m";
return $query;
}
function getSystemInfo($send_usage_info = true)
{
global $sugar_config;
global $db, $authLevel, $administration, $timedate;
$info = array();
$info = getBaseSystemInfo($send_usage_info);
if ($send_usage_info) {
if ($authLevel > 0) {
if (isset($_SERVER['SERVER_ADDR'])) {
$info['ip_address'] = $_SERVER['SERVER_ADDR'];
} else {
$info['ip_address'] = '127.0.0.1';
}
}
$info['application_key'] = $sugar_config['unique_key'];
$info['php_version'] = phpversion();
if (isset($_SERVER['SERVER_SOFTWARE'])) {
$info['server_software'] = $_SERVER['SERVER_SOFTWARE'];
}
// if
//get user count.
$user_list = get_user_array(false, "Active", "", false, null, " AND is_group=0 AND portal_only=0 ", false);
$info['users'] = count($user_list);
if (empty($administration)) {
$administration = new Administration();
}
$administration->retrieveSettings('system');
$info['system_name'] = !empty($administration->settings['system_name']) ? substr($administration->settings['system_name'], 0, 255) : '';
$query = "select count(*) count from users where status='Active' and deleted=0 and is_admin='1'";
$result = $db->query($query, 'fetching admin count', false);
$row = $db->fetchByAssoc($result);
if (!empty($row)) {
$info['admin_users'] = $row['count'];
}
if (empty($authLevel)) {
$authLevel = 0;
}
$query = "select count(*) count from users";
$result = $db->query($query, 'fetching all users count', false);
$row = $db->fetchByAssoc($result);
if (!empty($row)) {
$info['registered_users'] = $row['count'];
}
$lastMonth = db_convert("'" . $timedate->getNow()->modify("-30 days")->asDb(false) . "'", 'datetime');
if (!$send_usage_info) {
$info['users_active_30_days'] = -1;
} else {
$query = "SELECT count( DISTINCT users.id ) user_count FROM tracker, users WHERE users.id = tracker.user_id AND tracker.date_modified >= {$lastMonth}";
$result = $db->query($query, 'fetching last 30 users count', false);
$row = $db->fetchByAssoc($result);
$info['users_active_30_days'] = $row['user_count'];
}
if (!$send_usage_info) {
$info['latest_tracker_id'] = -1;
} else {
$query = "select id from tracker order by date_modified desc";
$id = $db->getOne($query, 'fetching most recent tracker entry', false);
if ($id !== false) {
$info['latest_tracker_id'] = $id;
}
}
$dbManager =& DBManagerFactory::getInstance();
$info['db_type'] = $sugar_config['dbconfig']['db_type'];
$info['db_version'] = $dbManager->version();
}
if (file_exists('distro.php')) {
include 'distro.php';
if (!empty($distro_name)) {
$info['distro_name'] = $distro_name;
}
}
$info['auth_level'] = $authLevel;
$info['os'] = php_uname('s');
$info['os_version'] = php_uname('r');
$info['timezone_u'] = $GLOBALS['current_user']->getPreference('timezone');
$info['timezone'] = date('e');
if ($info['timezone'] == 'e') {
$info['timezone'] = date('T');
}
return $info;
}
/**
* Performs a check if the record has been modified since the specified date
*
* @param date $date Datetime for verification
* @param string $modified_user_id User modified by
*/
function has_been_modified_since($date, $modified_user_id)
{
global $current_user;
if (isset($current_user)) {
if ($this->db->dbType == 'mssql') {
$date_modified_string = 'CONVERT(varchar(20), date_modified, 120)';
} else {
$date_modified_string = 'date_modified';
}
$query = "SELECT date_modified FROM {$this->table_name} WHERE id='{$this->id}' AND modified_user_id != '{$current_user->id}' AND (modified_user_id != '{$modified_user_id}' OR {$date_modified_string} > " . db_convert("'" . $date . "'", 'datetime') . ')';
$result = $this->db->query($query);
if ($this->db->fetchByAssoc($result)) {
return true;
}
}
return false;
}
function constructQuery()
{
global $current_user;
global $timedate;
global $app_list_strings;
//get the dates to display
$user_date_start = $current_user->getPreference('pbss_date_start');
if (!empty($user_date_start) && !isset($_REQUEST['pbss_date_start'])) {
$date_start = $timedate->to_display_date($user_date_start, false);
Log::debug("USER PREFERENCES['pbss_date_start'] is:");
Log::debug($user_date_start);
} elseif (isset($_REQUEST['pbss_date_start']) && $_REQUEST['pbss_date_start'] != '') {
$date_start = $_REQUEST['pbss_date_start'];
$ds = $timedate->to_db_date($date_start, false);
$current_user->setPreference('pbss_date_start', $ds);
Log::debug("_REQUEST['pbss_date_start'] is:");
Log::debug($_REQUEST['pbss_date_start']);
Log::debug("USER PREFERENCES['pbss_date_start'] is:");
Log::debug($current_user->getPreference('pbss_date_start'));
} else {
$date_start = $timedate->nowDate();
}
$user_date_end = $current_user->getPreference('pbss_date_end');
if (!empty($user_date_end) && !isset($_REQUEST['pbss_date_end'])) {
$date_end = $timedate->to_display_date($user_date_end, false);
Log::debug("USER PREFERENCES['pbss_date_end'] is:");
Log::debug($user_date_end);
} elseif (isset($_REQUEST['pbss_date_end']) && $_REQUEST['pbss_date_end'] != '') {
$date_end = $_REQUEST['pbss_date_end'];
$de = $timedate->to_db_date($date_end, false);
$current_user->setPreference('pbss_date_end', $de);
Log::debug("_REQUEST['pbss_date_end'] is:");
Log::debug($_REQUEST['pbss_date_end']);
Log::debug("USER PREFERENCES['pbss_date_end'] is:");
Log::debug($current_user->getPreference('pbss_date_end'));
} else {
$date_end = $timedate->asUserDate($timedate->fromString("2010-01-01"));
Log::debug("USER PREFERENCES['pbss_date_end'] not found. Using: " . $date_end);
}
$tempx = array();
$datax = array();
$datax_selected = array();
$user_tempx = $current_user->getPreference('pbss_sales_stages');
//get list of sales stage keys to display
if (!empty($user_tempx) && count($user_tempx) > 0 && !isset($_REQUEST['pbss_sales_stages'])) {
$tempx = $user_tempx;
Log::debug("USER PREFERENCES['pbss_sales_stages'] is:");
Log::debug($user_tempx);
} elseif (isset($_REQUEST['pbss_sales_stages']) && count($_REQUEST['pbss_sales_stages']) > 0) {
$tempx = $_REQUEST['pbss_sales_stages'];
$current_user->setPreference('pbss_sales_stages', $_REQUEST['pbss_sales_stages']);
Log::debug("_REQUEST['pbss_sales_stages'] is:");
Log::debug($_REQUEST['pbss_sales_stages']);
Log::debug("USER PREFERENCES['pbss_sales_stages'] is:");
Log::debug($current_user->getPreference('pbss_sales_stages'));
}
//set $datax using selected sales stage keys
if (count($tempx) > 0) {
foreach ($tempx as $key) {
$datax[$key] = $app_list_strings['sales_stage_dom'][$key];
array_push($datax_selected, $key);
}
} else {
$datax = $app_list_strings['sales_stage_dom'];
$datax_selected = array_keys($app_list_strings['sales_stage_dom']);
}
Log::debug("datax is:");
Log::debug($datax);
$ids = array();
$new_ids = array();
$user_ids = $current_user->getPreference('pbss_ids');
//get list of user ids for which to display data
if (!empty($user_ids) && count($user_ids) != 0 && !isset($_REQUEST['pbss_ids'])) {
$ids = $user_ids;
Log::debug("USER PREFERENCES['pbss_ids'] is:");
Log::debug($user_ids);
} elseif (isset($_REQUEST['pbss_ids']) && count($_REQUEST['pbss_ids']) > 0) {
$ids = $_REQUEST['pbss_ids'];
$current_user->setPreference('pbss_ids', $_REQUEST['pbss_ids']);
Log::debug("_REQUEST['pbss_ids'] is:");
Log::debug($_REQUEST['pbss_ids']);
Log::debug("USER PREFERENCES['pbss_ids'] is:");
Log::debug($current_user->getPreference('pbss_ids'));
} else {
$ids = get_user_array(false);
$ids = array_keys($ids);
}
$user_id = $ids;
$opp = new Opportunity();
$where = "";
//build the where clause for the query that matches $user
$count = count($user_id);
$id = array();
$user_list = get_user_array(false);
foreach ($user_id as $key) {
$new_ids[$key] = $user_list[$key];
}
if ($count > 0) {
foreach ($new_ids as $the_id => $the_name) {
$id[] = "'" . $the_id . "'";
}
$ids = join(",", $id);
$where .= "opportunities.assigned_user_id IN ({$ids}) ";
}
//build the where clause for the query that matches $datax
$count = count($datax);
$dataxArr = array();
if ($count > 0) {
foreach ($datax as $key => $value) {
$dataxArr[] = "'" . $key . "'";
}
$dataxArr = join(",", $dataxArr);
$where .= "AND opportunities.sales_stage IN\t({$dataxArr}) ";
}
//build the where clause for the query that matches $date_start and $date_end
$where .= "\tAND opportunities.date_closed >= " . db_convert("'" . $date_start . "'", 'date') . "\n\t\t\t\t\tAND opportunities.date_closed <= " . db_convert("'" . $date_end . "'", 'date');
$where .= "\tAND opportunities.assigned_user_id = users.id AND opportunities.deleted=0 ";
//Now do the db queries
//query for opportunity data that matches $datax and $user
$query = "\tSELECT opportunities.sales_stage,\n\t\t\t\t\t\tusers.user_name,\n\t\t\t\t\t\topportunities.assigned_user_id,\n\t\t\t\t\t\tcount( * ) AS opp_count,\n\t\t\t\t\t\tsum(amount_usdollar/1000) AS total\n\t\t\t\t\tFROM users,opportunities ";
$query .= "WHERE " . $where;
$query .= " GROUP BY opportunities.sales_stage,users.user_name,opportunities.assigned_user_id";
return $query;
}
} else {
// cut it off:
$focus->load_relationship($linked_field);
if ($focus->{$linked_field}->_relationship->relationship_name == 'quotes_contacts_shipto') {
unset($focus->{$linked_field}->_relationship->relationship_role_column);
}
$focus->{$linked_field}->delete($record, $linked_id);
}
if ($bean_name == 'Campaign' and $linked_field == 'prospectlists') {
$query = "SELECT email_marketing_prospect_lists.id from email_marketing_prospect_lists ";
$query .= " left join email_marketing on email_marketing.id=email_marketing_prospect_lists.email_marketing_id";
$query .= " where email_marketing.campaign_id='{$record}'";
$query .= " and email_marketing_prospect_lists.prospect_list_id='{$linked_id}'";
$result = $focus->db->query($query);
while (($row = $focus->db->fetchByAssoc($result)) != null) {
$del_query = " update email_marketing_prospect_lists set email_marketing_prospect_lists.deleted=1, email_marketing_prospect_lists.date_modified=" . db_convert("'" . gmdate($GLOBALS['timedate']->get_db_date_time_format(), time()) . "'", 'datetime');
$del_query .= " WHERE email_marketing_prospect_lists.id='{$row['id']}'";
$focus->db->query($del_query);
}
$focus->db->query($query);
}
if ($bean_name == "Meeting") {
$focus->retrieve($record);
$user = new User();
$user->retrieve($linked_id);
if (!empty($user->id)) {
//make sure that record exists. we may have a contact on our hands.
if ($focus->update_vcal) {
vCal::cache_sugar_vcal($user);
}
}
/**
* runs rebuild scripts
*/
function UWrebuild()
{
global $db;
global $path;
logThis('Rebuilding everything...', $path);
require_once 'ModuleInstall/ModuleInstaller.php';
$mi = new ModuleInstaller();
$mi->rebuild_all(true);
$query = "DELETE FROM versions WHERE name='Rebuild Extensions'";
$db->query($query);
logThis('Registering rebuild record: ' . $query, $path);
logThis('Rebuild done.', $path);
// insert a new database row to show the rebuild extensions is done
$id = create_guid();
$gmdate = gmdate($GLOBALS['timedate']->get_db_date_time_format());
$date_entered = db_convert("'{$gmdate}'", 'datetime');
$query = 'INSERT INTO versions (id, deleted, date_entered, date_modified, modified_user_id, created_by, name, file_version, db_version) ' . "VALUES ('{$id}', '0', {$date_entered}, {$date_entered}, '1', '1', 'Rebuild Extensions', '4.0.0', '4.0.0')";
$db->query($query);
logThis('Registering rebuild record in versions table: ' . $query, $path);
}
