function genReport($rp_source_query, $rp_destination_query, $rp_period)
{
global $rp_info;
if ($rp_period == 'daily') {
$drange['last']['from'] = date("Y-m-d g:i:s", time() - 60 * 60 * 24 * 2);
$drange['last']['to'] = date("Y-m-d g:i:s", time() - 60 * 60 * 24 * 1);
$drange['this']['from'] = date("Y-m-d g:i:s", time() - 60 * 60 * 24 * 1);
$drange['this']['to'] = date("Y-m-d g:i:s");
$total_days = 1;
}
if ($rp_period == 'weekly') {
$drange['last']['from'] = date("Y-m-d g:i:s", time() - 60 * 60 * 24 * 14);
$drange['last']['to'] = date("Y-m-d g:i:s", time() - 60 * 60 * 24 * 7);
$drange['this']['from'] = date("Y-m-d g:i:s", time() - 60 * 60 * 24 * 7);
$drange['this']['to'] = date("Y-m-d g:i:s");
$total_days = 7;
}
if ($rp_period == 'monthly') {
$drange['last']['from'] = date("Y-m-", time() - 60 * 60 * 24 * 30) . "1 00:00:01";
$drange['last']['to'] = date("Y-m-") . "1 00:00:00";
$drange['this']['from'] = date("Y-m-") . "1 00:00:01";
$drange['this']['to'] = date("Y-m-d g:i:s");
$total_days = 30;
}
if ($rp_period == 'yearly') {
$drange['last']['from'] = date("Y-", time() - 60 * 60 * 24 * 365) . "1-1 00:00:01";
$drange['last']['to'] = date("Y-") . "1-1 00:00:00";
$drange['this']['from'] = date("Y-") . "1-1 00:00:01";
$drange['this']['to'] = date("Y-m-d g:i:s");
$total_days = 365;
}
$drange['last']['sql'] = "'" . $drange['last']['from'] . "' and '" . $drange['last']['to'] . "'";
$drange['this']['sql'] = "'" . $drange['this']['from'] . "' and '" . $drange['this']['to'] . "'";
$last_source_query_td = " td.transactionDate between " . $drange['last']['sql'];
$last_source_query_tdc = " tdc.cancellationDate between " . $drange['last']['sql'];
$this_source_query_td = " td.transactionDate between " . $drange['this']['sql'];
$this_source_query_tdc = " tdc.cancellationDate between " . $drange['this']['sql'];
$days = (strtotime($drange['this']['to']) - strtotime($drange['this']['from'])) / (60 * 60 * 24);
$day_percent = $days / $total_days;
$sql = $rp_source_query;
$sql = str_replace('[transactionDate]', $last_source_query_td, $sql);
$sql = str_replace('[cancellationDate]', $last_source_query_tdc, $sql);
$lastreport = genReportSection($sql);
$sql = $rp_source_query;
$sql = str_replace('[transactionDate]', $this_source_query_td, $sql);
$sql = str_replace('[cancellationDate]', $this_source_query_tdc, $sql);
$thisreport = genReportSection($sql);
foreach ($lastreport as $key => $sect) {
foreach ($sect as $key2 => $var) {
$nextreport[$key][$key2]['val'] = round($lastreport[$key][$key2]['val'] + ($thisreport[$key][$key2]['val'] - $lastreport[$key][$key2]['val']) * 2 * $day_percent, 2);
if ($nextreport[$key][$key2]['val'] < 0) {
$nextreport[$key][$key2]['val'] = 0;
}
}
$nextreport[$key]['cnt']['val'] = round($nextreport[$key]['cnt']['val']);
}
$result = mysql_query($rp_destination_query) or dieLog(mysql_error() . " ~ {$rp_destination_query}");
$destination_data = mysql_fetch_assoc($result);
$final_report['lastreport']['report'] = $lastreport;
$final_report['lastreport']['severity'] = $rp_info['rp_projected_severity'];
$final_report['lastreport']['assoc'] = "Last Report Period";
$final_report['thisreport']['report'] = $thisreport;
$final_report['thisreport']['severity'] = $rp_info['rp_severity'];
$final_report['thisreport']['assoc'] = "This Report Period";
$final_report['nextreport']['report'] = $nextreport;
$final_report['nextreport']['severity'] = $rp_info['rp_projected_severity'];
$final_report['nextreport']['assoc'] = "Projected Report Period";
$str_result = "";
$priority = 0;
foreach ($destination_data as $key => $sect) {
list($mild, $moderate, $severe, $goal) = explode("|", $sect);
foreach ($final_report as $which_report => $rp_report) {
$value_type = "";
if ($type == 'p') {
$fvalue = formatMoney($value) . "%";
}
if ($type == 'apm') {
$fvalue = "\$" . formatMoney($value);
}
$severity = $final_report[$which_report]['severity'];
if ($compare == 'tpgt' && $rp_report['report'][$key]['tper']['val'] > $value) {
$value_type = 'dper';
$str_assoc = "Total Percentage is greater than '{$fvalue}'";
}
if ($compare == 'tplt' && $rp_report['report'][$key]['tper']['val'] < $value) {
$value_type = 'dper';
$str_assoc = "Total Percentage is less than '{$fvalue}'";
}
if ($compare == 'apgt' && $rp_report['report'][$key]['per']['val'] > $value) {
$value_type = 'dper';
$str_assoc = "Approved Percentage is greater than '{$fvalue}'";
}
if ($compare == 'aplt' && $rp_report['report'][$key]['per']['val'] < $value) {
$value_type = 'dper';
$str_assoc = "Approved Percentage is less than '{$fvalue}'";
}
if ($compare == 'mtgt' && $rp_report['report'][$key]['amt']['val'] > $value) {
$value_type = 'amt';
$str_assoc = "Approved Amount is greater than '{$fvalue}'";
}
if ($compare == 'mtlt' && $rp_report['report'][$key]['amt']['val'] < $value) {
$value_type = 'amt';
$str_assoc = "Approved Amount is less than '{$fvalue}'";
}
if ($value_type) {
$final_report[$which_report]['report'][$key][$value_type]['css'] = $severity;
$str_result .= "<span class='{$severity}'>" . ucfirst($severity) . " Warning: {$key} {$str_assoc} for " . $final_report[$which_report]['assoc'] . ".</span>\n";
$priority++;
}
}
}
$final_report['str_result'] = $str_result;
$final_report['priority'] = $priority;
//print "<PRE>";
//print_r($final_report); print "<BR>";
//print_r($destination_data); print "<BR>";
//print "</PRE>";
return $final_report;
}
function genReport($rp_source_query, $rp_destination_query, $total_days, $overview = false, $userId = NULL)
{
global $rp_info;
global $rp_severity;
$drange['last']['from'] = date("Y-m-d 00:00:00", time() - 60 * 60 * 24 * $total_days * 2 + 1);
$drange['last']['to'] = date("Y-m-d g:i:s", time() - 60 * 60 * 24 * $total_days);
$drange['this']['from'] = date("Y-m-d g:i:s", time() - 60 * 60 * 24 * $total_days + 1);
$drange['this']['to'] = date("Y-m-d g:i:s");
$drange['last']['sql'] = "'" . $drange['last']['from'] . "' and '" . $drange['last']['to'] . "'";
$drange['this']['sql'] = "'" . $drange['this']['from'] . "' and '" . $drange['this']['to'] . "'";
$last_source_query_td = " td.transactionDate between " . $drange['last']['sql'];
$last_source_query_tdc = " tdc.cancellationDate between " . $drange['last']['sql'];
$this_source_query_td = " td.transactionDate between " . $drange['this']['sql'];
$this_source_query_tdc = " tdc.cancellationDate between " . $drange['this']['sql'];
$days = (@strtotime($drange['this']['to']) - @strtotime($drange['this']['from'])) / (60 * 60 * 24);
if ($total_days <= 1) {
$day_percent = 1;
} else {
$day_percent = $days / $total_days;
}
$site_sql = "1";
if ($userId) {
$site_sql = "cs.cs_company_id = {$userId}";
}
$company_userId = "1";
if ($userId) {
$company_userId = "userId = {$userId}";
}
$sql = $rp_source_query;
$sql = str_replace('[transactionDate]', $last_source_query_td, $sql);
$sql = str_replace('[cancellationDate]', $last_source_query_tdc, $sql);
$sql = str_replace('[site_sql]', $site_sql, $sql);
$sql = str_replace('[company_userId]', $company_userId, $sql);
$lastreport = genReportSection($sql);
if ($lastreport == -1) {
return -1;
}
$sql = $rp_source_query;
$sql = str_replace('[transactionDate]', $this_source_query_td, $sql);
$sql = str_replace('[cancellationDate]', $this_source_query_tdc, $sql);
$sql = str_replace('[site_sql]', $site_sql, $sql);
$sql = str_replace('[company_userId]', $company_userId, $sql);
$thisreport = genReportSection($sql);
if ($thisreport == -1) {
return -1;
}
$nextreport = array();
foreach ($lastreport as $key => $sect) {
foreach ($sect as $key2 => $var) {
$nextreport[$key][$key2]['val'] = round($lastreport[$key][$key2]['val'] + ($thisreport[$key][$key2]['val'] - $lastreport[$key][$key2]['val']) * 2 * $day_percent, 2);
if ($nextreport[$key][$key2]['val'] < 0) {
$nextreport[$key][$key2]['val'] = 0;
}
}
$nextreport[$key]['type'] = $lastreport[$key]['type'];
if ($nextreport[$key]['cnt']['val'] <= 0 || $nextreport[$key]['amt']['val'] <= 0) {
$nextreport[$key]['cnt']['val'] = 0;
$nextreport[$key]['dper']['val'] = 0;
$nextreport[$key]['amt']['val'] = 0;
}
$nextreport[$key]['cnt']['val'] = round($nextreport[$key]['cnt']['val']);
}
$result = mysql_query($rp_destination_query);
if (!$result) {
dieLog("" . mysql_error() . " ~ {$rp_destination_query}");
}
$destination_data = mysql_fetch_assoc($result);
$final_report['lastreport']['report'] = $lastreport;
$final_report['lastreport']['severity'] = $rp_info['rp_projected_severity'];
$final_report['lastreport']['assoc'] = "Last Report Period";
$final_report['thisreport']['report'] = $thisreport;
$final_report['thisreport']['severity'] = $rp_info['rp_severity'];
$final_report['thisreport']['assoc'] = "This Report Period";
$final_report['nextreport']['report'] = $nextreport;
$final_report['nextreport']['severity'] = $rp_info['rp_projected_severity'];
$final_report['nextreport']['assoc'] = "Projected Report Period";
$str_result = "";
$priority = 0;
if (is_array($destination_data)) {
foreach ($destination_data as $key => $sect) {
list($mild, $moderate, $severe, $goal, $type, $ext_value, $signif) = explode("|", $sect);
foreach ($final_report as $which_report => $rp_report) {
$str_assoc = "";
if ($type == 'p') {
if ($rp_report['report'][$key]['dper']['val'] > $mild) {
$str_assoc = "Mild Alert: ";
$severity = 'mild';
$value = $mild;
}
if ($rp_report['report'][$key]['dper']['val'] > $moderate) {
$str_assoc = "Moderate Alert: ";
$severity = 'moderate';
$value = $moderate;
}
if ($rp_report['report'][$key]['dper']['val'] > $severe) {
$str_assoc = "Severe Alert: ";
$severity = 'severe';
$value = $severe;
}
if ($rp_report['report'][$key]['dper']['val'] > $goal) {
$str_assoc = "Limit Point Reached: ";
$severity = 'goal';
$value = $goal;
}
}
if ($type == 'scr') {
if ($which_report != 'thisreport') {
continue;
}
if ($rp_report['report'][$key]['amt']['val'] > $mild) {
$str_assoc = "Mild Alert: ";
$severity = 'mild';
$value = $mild;
}
if ($rp_report['report'][$key]['amt']['val'] > $moderate) {
$str_assoc = "Moderate Alert: ";
$severity = 'moderate';
$value = $moderate;
}
if ($rp_report['report'][$key]['amt']['val'] > $severe) {
$str_assoc = "Severe Alert: ";
$severity = 'severe';
$value = $severe;
}
if ($rp_report['report'][$key]['amt']['val'] > $goal) {
$str_assoc = "Limit Point Reached: ";
$severity = 'goal';
$value = $goal;
}
}
if ($type == 'str') {
if ($which_report != 'thisreport') {
continue;
}
}
if ($type == 'apm' && $overview == false) {
if ($total_days <= 0) {
$apm_fraction = 1;
} else {
$apm_fraction = 30 / $total_days;
}
$apm_monthly = $rp_report['report'][$key]['amt']['val'] * $apm_fraction;
if ($apm_monthly > $mild / 100 * $ext_value) {
$str_assoc = "Mild Alert: ";
$severity = 'mild';
$value = $mild / 100 * $ext_value;
}
if ($apm_monthly > $moderate / 100 * $ext_value) {
$str_assoc = "Moderate Alert: ";
$severity = 'moderate';
$value = $moderate / 100 * $ext_value;
}
if ($apm_monthly > $severe / 100 * $ext_value) {
$str_assoc = "Severe Alert: ";
$severity = 'severe';
$value = $severe / 100 * $ext_value;
}
if ($apm_monthly > $goal / 100 * $ext_value) {
$str_assoc = "Limit Point Alert: ";
$severity = 'goal';
$value = $goal / 100 * $ext_value;
}
}
if ($mild == 0 && $moderate == 0 && $severe == 0 && $goal == 0) {
$str_assoc = NULL;
$severity = NULL;
$value = NULL;
}
if ($type == 'p') {
$fvalue = formatMoney($value) . "%";
}
if ($type == 'a') {
$fvalue = "\$" . formatMoney($value);
}
if ($type == 'scr') {
$fvalue = "" . formatMoney($value) . " pts";
}
if ($type == 'apm') {
$fvalue = "\$" . formatMoney($value) . "/mo";
}
if ($str_assoc) {
if ($type == 'scr') {
$str_assoc .= "'{$key}' Score is greater than '{$fvalue}'";
} else {
$str_assoc .= "'{$key}' Percentage is greater than '{$fvalue}'";
}
if ($type == 'p') {
$final_report[$which_report]['report'][$key]['dper']['css'] = $severity;
}
if ($type == 'apm' || $type == 'scr') {
$final_report[$which_report]['report'][$key]['amt']['css'] = $severity;
}
$txt_severity = ucfirst($severity);
if ($severity == 'goal') {
$txt_severity = 'Limit Point';
}
if ($rp_report['report'][$key]['cnt']['val'] > $signif) {
$str_result[$severity] .= "<span class='{$severity}'>{$key} {$str_assoc} for " . $final_report[$which_report]['assoc'] . ".</span>\n";
$priority++;
} else {
$priority += 0.5;
}
}
}
}
}
$str_final_result = $str_result['goal'];
if ($rp_severity != 'goal') {
$str_final_result .= $str_result['severe'];
if ($rp_severity != 'severe') {
$str_final_result .= $str_result['moderate'];
if ($rp_severity != 'moderate') {
$str_final_result .= $str_result['mild'];
}
}
}
$final_report['str_result'] = $str_final_result;
$final_report['priority'] = $priority;
//print "<PRE>";
//print_r($final_report); print "<BR>";
//print_r($destination_data); print "<BR>";
//print "</PRE>";
return $final_report;
}
