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; }