function DateTimeRows2sql($field, $cnt, &$s_sql)
{
global $db;
$tmp2 = "";
$allempty = FALSE;
$time_field = array("mysqli" => ":", "mysql" => ":", "mssql" => ":");
$minsec = array(">=" => "00", "<=" => "59");
//print_r($field)."<br><br>";
if ($cnt >= 1 && count($field) == 0) {
return 0;
}
for ($i = 0; $i < $cnt; $i++) {
$tmp = "";
if (isset($field[$i]) && $field[$i][1] != " " && $field[$i][1] != "") {
//echo "entrando $i\n";
$op = $field[$i][1];
$t = "";
/* Build the SQL string when >, >=, <, <= operator is used */
if ($op != "=") {
/* date */
if ($field[$i][4] != " ") {
/* create the date string */
$t = $field[$i][4];
/* year */
if ($field[$i][2] != " ") {
$t = $t . "-" . $field[$i][2];
/* month */
//echo "<!-- \n\n\n\n\n\n\n dia: -" . $field[$i][3] . "- -->\n\n\n\n\n\n";
if ($field[$i][3] != "") {
$t = $t . "-" . FormatTimeDigit($field[$i][3]);
} else {
$t = $i == 0 ? $t . "-01" : ($t = $t . "-31");
}
} else {
$t = $t . "-01-01";
}
}
/* time */
// For MSSQL, you must have colons in the time fields.
// Otherwise, the DATEDIFF function will return Arithmetic Overflow
if ($field[$i][5] != "") {
$t = $t . " " . FormatTimeDigit($field[$i][5]);
/* hour */
if ($field[$i][6] != "") {
$t = $t . $time_field[$db->DB_type] . FormatTimeDigit($field[$i][6]);
/* minute */
if ($field[$i][7] != "") {
$t = $t . $time_field[$db->DB_type] . FormatTimeDigit($field[$i][7]);
} else {
$t = $t . $time_field[$db->DB_type] . $minsec[$op];
}
} else {
$t = $t . $time_field[$db->DB_type] . $minsec[$op] . $time_field[$db->DB_type] . $minsec[$op];
}
} else {
if ($op == ">" && $field[$i][4] != " ") {
$t = $t . " 23:59:59";
} else {
if ($op == "<=" && $field[$i][4] != " ") {
$t = $t . " 23:59:59";
}
}
}
/* neither date or time */
if ($field[$i][4] == " " && $field[$i][5] == "") {
ErrorMessage("<B>" . gettext("Criteria warning:") . "</B> " . gettext("An operator of") . " '" . $field[$i][1] . "' " . gettext("was selected indicating that some date/time criteria should be matched, but no value was specified."));
} else {
if ($field[$i][4] != " " && $field[$i][5] != "" || $field[$i][4] != " ") {
if ($db->DB_type == "oci8") {
$tmp = $field[$i][0] . " timestamp " . $op . "to_date( '{$t}', 'YYYY-MM-DD HH24MISS' )" . $field[$i][8] . ' ' . $field[$i][9];
} else {
if (count($field) > 1) {
// Better fix for bug #1199128
// Number of values in each criteria line
//print_r($field[$i]);
$count = array_count_values_multidim($field[$i]);
// Number of empty values
$empty = $count[""];
// Total number of values in the criteria line (empty or filled)
$array_count = count($count);
// Check to see if any fields were left empty
//if(isset($count[""]))
// If the number of empty fields is greater than (impossible) or equal to (possible) the number of values in the array, then they must all be empty
//if ($empty >= $array_count)
//$allempty = TRUE;
// Trim off white space
$field[$i][9] = trim($field[$i][9]);
// And if the certain line was empty, then we dont care to process it
if ($allempty) {
// So move on
continue;
} else {
// Otherwise process it
if ($i < $cnt - 1) {
$tmp = $field[$i][0] . " timestamp " . $op . "'{$t}'" . $field[$i][8] . ' ' . CleanVariable($field[$i][9], VAR_ALPHA);
} else {
$tmp = $field[$i][0] . " timestamp " . $op . "'{$t}'" . $field[$i][8];
}
}
} else {
// If we just have one criteria line, then do with it what we must
if ($i < $cnt - 1) {
$tmp = $field[$i][0] . " timestamp " . $op . "'{$t}'" . $field[$i][8] . ' ' . CleanVariable($field[$i][9], VAR_ALPHA);
} else {
$tmp = $field[$i][0] . " timestamp " . $op . "'{$t}'" . $field[$i][8];
}
}
}
} else {
if ($field[$i][5] != " " && $field[$i][5] != "") {
ErrorMessage("<B>" . gettext("Criteria warning:") . "</B> " . gettext("(Invalid Hour) No date criteria were entered with the specified time."));
}
}
}
} else {
$query_str = "";
$query_str = $field[$i][4] . "-";
$query_str .= $field[$i][2] . "-";
$query_str .= $field[$i][3] . " ";
$query_str .= $field[$i][5] . ":";
$query_str .= $field[$i][6] . ":";
$query_str .= $field[$i][7] . "";
$query_str = preg_replace("/\\s*\\:+\\s*\$/", "", $query_str);
addSQLItem($tmp, "timestamp like \"{$query_str}%\"");
/* neither date or time */
if ($tmp == "") {
ErrorMessage("<B>" . gettext("Criteria warning:") . "</B> " . gettext("An operator of") . " '" . $field[$i][1] . "' " . gettext("was selected indicating that some date/time criteria should be matched, but no value was specified."));
} else {
if ($i < $cnt - 1) {
$tmp = $field[$i][0] . $tmp . ') ' . $field[$i][8] . CleanVariable($field[$i][9], VAR_ALPHA);
} else {
$tmp = $field[$i][0] . $tmp . ') ' . $field[$i][8];
}
}
}
} else {
if (isset($field[$i])) {
if (($field[$i][2] != "" || $field[$i][3] != "" || $field[$i][4] != "") && $field[$i][1] == "") {
ErrorMessage("<B>" . gettext("Criteria warning:") . "</B> " . gettext("A date/time value of") . " '" . $field[$i][2] . "-" . $field[$i][3] . "-" . $field[$i][4] . " " . $field[$i][5] . ":" . $field[6] . ":" . $field[7] . "' " . gettext("was entered but no operator was selected."));
}
}
}
if ($i > 0 && $field[$i - 1][9] == ' ' && $field[$i - 1][4] != " ") {
ErrorMessage("<B>" . gettext("Criteria warning:") . "</B> " . gettext("Multiple Date/Time criteria entered without a boolean operator (e.g. AND, OR) between them."));
}
$tmp2 = preg_match("/\\s+(AND|OR)\\s*\$/", $tmp2) || $i == 0 ? $tmp2 . $tmp : $tmp2 . " AND " . $tmp;
}
$tmp2 = trim(preg_replace("/(\\s*(AND|OR)\\s*)+\$/", "", $tmp2));
if ($tmp2 != "" && $tmp2 != "AND" && $tmp2 != "OR") {
BalanceBrackets($tmp2);
$s_sql = $s_sql . " AND ( " . $tmp2 . " ) ";
return 1;
}
BalanceBrackets($s_sql);
return 0;
}
function GetTimeDataSet(&$xdata, $chart_type, $data_source, $min_threshold, $criteria)
{
global $db, $debug_mode;
// if ($debug_mode > 0) {
// echo "chart_type = $chart_type<BR>
// data_source = $data_source<BR>";
// }
$sql = "SELECT min(timestamp), max(timestamp) FROM acid_event " . $criteria[0] . " WHERE " . $criteria[1];
$result = $db->baseExecute($sql);
$myrow = $result->baseFetchRow();
$start_time = $myrow[0];
$stop_time = $myrow[1];
$result->baseFreeRows();
$year_start = date("Y", strtotime($start_time));
$month_start = date("m", strtotime($start_time));
$day_start = date("d", strtotime($start_time));
$hour_start = date("H", strtotime($start_time));
$year_end = date("Y", strtotime($stop_time));
$month_end = date("m", strtotime($stop_time));
$day_end = date("d", strtotime($stop_time));
$hour_end = date("H", strtotime($stop_time));
// using the settings from begin_xyz and end_xyz
// minutes are not supported actually
// begin
global $chart_begin_year;
global $chart_begin_month;
global $chart_begin_day;
global $chart_begin_hour;
if (strcmp($chart_begin_year, " ") and $year_start < $chart_begin_year) {
$year_start = $chart_begin_year;
$month_start = "01";
$day_start = "01";
$hour_start = "00";
}
if (strcmp($chart_begin_month, " ") and $month_start < $chart_begin_month) {
$month_start = $chart_begin_month;
$day_start = "01";
$hour_start = "00";
}
if (strcmp($chart_begin_day, " ") and $day_start < $chart_begin_day) {
$day_start = $chart_begin_day;
$hour_start = "00";
}
if (strcmp($chart_begin_hour, " ") and $hour_start < $chart_begin_hour) {
$hour_start = $chart_begin_hour;
}
//end
global $chart_end_year;
global $chart_end_month;
global $chart_end_day;
global $chart_end_hour;
if (strcmp($chart_end_year, " ") and $year_end < $chart_end_year) {
$year_end = $chart_end_year;
$month_end = "01";
$day_end = "01";
$hour_end = "00";
}
if (strcmp($chart_end_month, " ") and $month_end < $chart_end_month) {
$month_end = $chart_end_month;
$day_end = "01";
$hour_end = "00";
}
if (strcmp($chart_end_day, " ") and $day_end < $chart_end_day) {
$day_end = $chart_end_day;
$hour_end = "00";
}
if (strcmp($chart_end_hour, " ") and $hour_end < $chart_end_hour) {
$hour_end = $chart_end_hour;
}
switch ($chart_type) {
case 1:
// if ($debug_mode > 0) {
// print "chart_begin_hour = \"$chart_begin_hour\", hour_start = \"$hour_start\"<BR>\n";
// print "chart_end_hour = \"$chart_end_hour\", hour_end = \"$hour_end\"<BR>\n";
// }
if (!strcmp($chart_end_hour, " ") || $chart_end_hour == "") {
// hour_start = -1 is NOT possible, because with chart_type == 1
// each hour is to be queried. We want bars hour by hour.
$hour_end = 23;
}
break;
case 2:
$hour_start = -1;
break;
case 4:
$day_start = -1;
$hour_start = -1;
break;
}
// if ($debug_mode > 0) {
// echo '<TABLE BORDER="1">
// <TR>
// <TD>year_start<TD>year_end<TD>month_start<TD>month_end
// <TD>day_start<TD>day_end<TD>hour_start<TD>hour_end
// <TR>
// <TD>' . $year_start . '<TD>' . $year_end . '<TD>' . $month_start . '<TD>' . $month_end . '<TD>' . $day_start . '<TD>' . $day_end . '<TD>' . $hour_start . '<TD>' . $hour_end . '</TABLE>';
// }
$cnt = 0;
$ag = $criteria[0];
$ag_criteria = $criteria[1];
for ($i_year = $year_start; $i_year <= $year_end; $i_year++) {
// removed AND below
// !!! AVN !!!
// to_date() must used!
$sql = "SELECT count(*) FROM acid_event " . $ag . " WHERE {$ag_criteria} AND " . $db->baseSQL_YEAR("timestamp", "=", $i_year);
if ($month_start != -1) {
if ($i_year == $year_start) {
$month_start2 = $month_start;
} else {
$month_start2 = 1;
}
if ($i_year == $year_end) {
$month_end2 = $month_end;
} else {
$month_end2 = 12;
}
for ($i_month = $month_start2; $i_month <= $month_end2; $i_month++) {
$sql = "SELECT count(*) FROM acid_event {$ag} WHERE {$ag_criteria} AND" . $db->baseSQL_YEAR("timestamp", "=", $i_year) . " AND " . $db->baseSQL_MONTH("timestamp", "=", FormatTimeDigit($i_month));
if ($day_start != -1) {
if ($i_month == $month_start) {
$day_start2 = $day_start;
} else {
$day_start2 = 1;
}
if ($i_month == $month_end) {
$day_end2 = $day_end;
} else {
$day_end2 = 31;
}
for ($i_day = $day_start2; $i_day <= $day_end2; $i_day++) {
if (checkdate($i_month, $i_day, $i_year)) {
$sql = "SELECT count(*) FROM acid_event {$ag} WHERE {$ag_criteria} AND " . $db->baseSQL_YEAR("timestamp", "=", $i_year) . " AND " . $db->baseSQL_MONTH("timestamp", "=", FormatTimeDigit($i_month)) . " AND " . $db->baseSQL_DAY("timestamp", "=", FormatTimeDigit($i_day));
if ($hour_start != -1) {
// jl: The condition "i_hour <= hour_end"
// is correct ONLY if the first day is equal
// to the last day of the query.
// Otherwise we want 24 hours of
// all the days preceding the last day of the query.
// Analogously for hour_start.
if ($i_day == $day_start2) {
$hour_start2 = $hour_start;
} else {
$hour_start2 = 0;
}
if ($i_day == $day_end2) {
$hour_end2 = $hour_end;
} else {
$hour_end2 = 23;
}
for ($i_hour = $hour_start2; $i_hour <= $hour_end2; $i_hour++) {
//if($i_hour < 10 && strlen($i_hour) == 1)
// $i_hour = "0".$i_hour;
$i_hour = FormatTimeDigit($i_hour);
$sql = "SELECT count(*) FROM acid_event {$ag} WHERE {$ag_criteria} AND " . $db->baseSQL_YEAR("timestamp", "=", $i_year) . " AND " . $db->baseSQL_MONTH("timestamp", "=", FormatTimeDigit($i_month)) . " AND " . $db->baseSQL_DAY("timestamp", "=", FormatTimeDigit($i_day)) . " AND " . $db->baseSQL_HOUR("timestamp", "=", $i_hour);
StoreAlertNum($sql, FormatTimeDigit($i_month) . "/" . FormatTimeDigit($i_day) . "/" . $i_year . " " . $i_hour . ":00:00 - " . $i_hour . ":59:59", $xdata, $cnt, $min_threshold);
}
// end hour
} else {
StoreAlertNum($sql, FormatTimeDigit($i_month) . "/" . FormatTimeDigit($i_day) . "/" . $i_year, $xdata, $cnt, $min_threshold);
}
}
}
// end day
} else {
StoreAlertNum($sql, FormatTimeDigit($i_month) . "/" . $i_year, $xdata, $cnt, $min_threshold);
}
}
// end month
} else {
StoreAlertNum($sql, $i_year, $xdata, $cnt, $min_threshold);
}
}
// end year
return $cnt;
}
