class Show_Statistics{
var $page1;
var $page2;
var $db_link;
var $month_array;
var $parish_array;
var $group_by_array;
var $group_by;
var $themonth;
var $main_header_color;
var $sub_header_color;
var $light_color;
var $dark_color;
var $this_year;
var $show_total_column;
var $starting_year;
var $ending_year;
var $parish_id;
var $min_year;
var $max_year;
var $items_array;
var $the_category;
var $the_item;
function Show_Statistics(){
$this->connect_to_db();
$this->page1="statistics.php";
$this->page2="statistics_results.php";
$this->parish_array = array("All", "Christ Church", "St. Andrew", "St. George", "St. James", "St. John", "St. Joseph", "St. Lucy", "St. Michael", "St. Peter", "St. Philip", "St. Thomas");
$this->month_array = array ("All", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");
$this->group_by_array = array("Year", "Month");
$this->group_by="Year";
$this->main_header_color="#C40400";
$this->sub_header_color="#9B9B9B";//#A6A6A6
$this->light_color="#FFFFFF";
$this->dark_color="#dbdbdb";//
$this->parish_id=0;
$this->this_year=date("Y",strtotime ($this->get_date_today()));
//below function uses this_year for a default value
$this->get_statistics_range();
$this->starting_year=$this->min_year;
$this->ending_year=$this->max_year;
$this->group_by="Year";
$this->themonth=0;
$this->the_category="All";
$this->the_item="All";
$this->set_up_array();
echo "";
}
function set_up_array(){
//array used for criteria page combo boxes - categories and items
$query="SELECT Tbl_BFS_Statistics_Incident_Types.Fld_Alias FROM Tbl_BFS_Statistics_Incident_Types WHERE Tbl_BFS_Statistics_Incident_Types.Fld_Classification='FIRE' ORDER BY Tbl_BFS_Statistics_Incident_Types.Fld_Alias";
$result_handler=mysql_query($query, $this->db_link);
if($result_handler and mysql_affected_rows($this->db_link)>0){
$x=0;
while($data=mysql_fetch_array($result_handler)){
$fire[$x]=$data["Fld_Alias"];
$x=$x+1;
}
$fire[$x]="All";
}
$special_services=array("Motor Vehicle Accidents", "Non Emergencies", "Other Emergencies", "All");
$query="SELECT Tbl_BFS_Statistics_Incident_Types.Fld_Alias FROM Tbl_BFS_Statistics_Incident_Types WHERE Tbl_BFS_Statistics_Incident_Types.Fld_Classification='SS - AS' ORDER BY Tbl_BFS_Statistics_Incident_Types.Fld_Alias";
$result_handler=mysql_query($query, $this->db_link);
if($result_handler and mysql_affected_rows($this->db_link)>0){
$x=0;
while($data=mysql_fetch_array($result_handler)){
$airport_services[$x]=$data["Fld_Alias"];
$x=$x+1;
}
$airport_services[$x]="All";
}
$fatalities=array("Fatalities");
$bomb=array("Bomb Scares");
//'All' => array("All"),
$this->items_array=array('Fires' => $fire, 'Special Services' => $special_services, 'Airport Services' => $airport_services, 'Fatalities' => $fatalities ,'Bomb Scares' => $bomb);
}
function get_criteria(){
?>
echo "
";
?>
}
function get_results(){
$starting_year=$this->min_year;
$ending_year=$this->max_year;
if($this->get_var_sent("cb_categories")){
$this->the_category=$_GET["cb_categories"];
}
if($this->get_var_sent("cb_items")){
$this->the_item=$_GET["cb_items"];
}
if(array_key_exists($this->the_item, $this->items_array)){
$this->the_category=$this->the_item;
$this->the_item="All";
}
elseif((array_key_exists($this->the_category, $this->items_array)) or ($this->the_category=="All" and $this->the_item=="All")){
}
else{
while( $element = each( $this->items_array ) ){
if(in_array($this->the_item,$element[ 'value' ])){
$this->the_category=$element[ 'key' ];
break;
}
}
}
//echo $this->the_category . " - " . $this->the_item;
if($this->get_var_sent("cb_starting_theyear") and is_numeric($_GET["cb_starting_theyear"]) ){
$starting_year=intval($_GET["cb_starting_theyear"]);
}
if($this->get_var_sent("cb_ending_theyear") and is_numeric($_GET["cb_ending_theyear"]) ){
$ending_year=intval($_GET["cb_ending_theyear"]);
}
if($this->get_var_sent("cb_parish_id") and is_numeric($_GET["cb_parish_id"]) and $_GET["cb_parish_id"]>=0 and $_GET["cb_parish_id"]<=11 ){
$this->parish_id=intval($_GET["cb_parish_id"]);
}
if($this->get_var_sent("cb_group_by") and $_GET["cb_group_by"]=="Month"){
$this->group_by="Month";
}
else{
$this->group_by="Year";
}
if($this->get_var_sent("cb_month") and is_numeric($_GET["cb_month"]) and $_GET["cb_month"]>=0 and $_GET["cb_month"]<=12 ){
$this->themonth=intval($_GET["cb_month"]);
}
$parish_clause="";
if($this->parish_id<>0){
$parish_clause=" AND (((Tbl_BFS_Statistics_Incident.Fld_Parish)='". $this->parish_array[$this->parish_id] ."')) ";
}
if($starting_year<$this->min_year){
$starting_year=$this->min_year;
}
if($ending_year>$this->max_year){
$ending_year=$this->max_year;
}
//if to much information to display group by month
if($ending_year-$starting_year>1 and $this->group_by=="Month" and $this->themonth=="0"){
$this->group_by="Year";
}
if($ending_year<$starting_year){
$starting_year=$ending_year;
}
$show_total_column=false;
if(!( ($this->group_by=="Year" and $ending_year-$starting_year==0) or ($ending_year-$starting_year==0 and $this->group_by=="Month" and $this->themonth <>"0") )){
$this->show_total_column=true;
}
$header="";
$num_cols=0;
if($this->group_by=="Month" and $this->themonth<>"0"){
$header1="";
$header2="";
$extract_expression="YEAR_MONTH";
$z=0;
$header1=" | " . date("F", mktime(0, 0, 0, ($this->themonth), 1, 0)) . " | ";
$header2="
";
for ($x=0;$starting_year+$x<=$ending_year;$x++){
$header2=$header2 . "| ". ($starting_year+$x) ." | ";
$time_periods_array[$z]=($starting_year+$x).str_pad(($this->themonth), 3-strlen(($this->themonth)), "0", STR_PAD_LEFT);
$vertical_total[$z]=0;
$vertical_grand_total[$z]=0;
$z=$z+1;
}
if($this->show_total_column){
$header1=$header1. " |
";
$header2=$header2. "Total | ";
}
else{
$header1=$header1. "";
$header2=$header2. "";
}
$header=$header1 . $header2;
}
elseif($this->group_by=="Month" and $this->themonth=="0"){
$header1="";
$header2="";
$extract_expression="YEAR_MONTH";
$z=0;
$header1=" | ";
$header2="
";
for ($x=0;$starting_year+$x<=$ending_year;$x++){
$header1=$header1 . "| ". ($starting_year+$x) ." | ";
for ($y=0;$y<12;$y++){
$header2=$header2. "". date("M", mktime(0, 0, 0, ($y+1), 1, 0)) ." | ";
$time_periods_array[$z]=($starting_year+$x).str_pad(($y+1), 3-strlen(($y+1)), "0", STR_PAD_LEFT);
$vertical_total[$z]=0;
$vertical_grand_total[$z]=0;
$z=$z+1;
}
}
if($this->show_total_column){
$header1=$header1. " |
";
$header2=$header2. "Total | ";
}
else{
$header1=$header1. "";
$header2=$header2. "";
}
$header=$header1 . $header2;
}
else{
$extract_expression="YEAR";
$header="  .") | ";
for ($x=0;$starting_year+$x<=$ending_year;$x++){
$time_periods_array[$x]=$starting_year+$x;
$header=$header. "". ($starting_year+$x) ." | ";
$vertical_total[$x]=0;
$vertical_grand_total[$x]=0;
}
if($this->show_total_column){
$header=$header . "Total | ";
}
else{
$header=$header . "";
}
}
$page_title="";
$page_title="Barbados Fire Service Statistical Reports For ";
$page_title=$page_title . ( ($this->group_by=="Month" and $this->themonth <> 0) ? date("F", mktime(0, 0, 0, ($this->themonth), 1, 0)).", " : "" );
$page_title=$page_title . ( ($ending_year-$starting_year)>0 ? "$starting_year - $ending_year" : " $starting_year" ) ;
$page_title=$page_title . ( $this->parish_id == 0 ? "" : "
Limited to the parish of ". $this->parish_array[$this->parish_id]);
$page_title=$page_title . ( $this->the_category=="All" ? "" : "
$this->the_category" );
$page_title=$page_title . ( ($this->the_item=="All" or $this->the_item=="Bomb Scares") ? "" : " - $this->the_item" );
echo "
" .$page_title . "
";
$bgcolor="";
$num_cols=sizeof($vertical_total);
$dynamic_query="";
$dynamic_query = $dynamic_query . " COALESCE( SUM( IF( EXTRACT($extract_expression FROM Fld_Date ) = '". $time_periods_array[0] . "', 1, 0) $parish_clause ), 0) as '". $time_periods_array[0] ."' \n";
for ($x=1;$x'Motor Vehicle Accident'";
$non_emergencies_query="Select 'Non Emergencies' as 'Incident Type', " . $dynamic_query . $join . " WHERE Tbl_BFS_Statistics_Incident_Types.Fld_Classification='SS - NEM'";
$airport_query="Select Tbl_BFS_Statistics_Incident_Types.Fld_Alias as 'Incident Type', " . $dynamic_query . $join . " WHERE Tbl_BFS_Statistics_Incident_Types.Fld_Classification='SS - AS' Group By Tbl_BFS_Statistics_Incident_Types.Fld_Alias ";
$fatalities_query="Select 'Fatalities' as 'Incident Type', " . $dynamic_query . " FROM Tbl_BFS_Statistics_Incident WHERE Tbl_BFS_Statistics_Incident.Fld_Incident_Type='FATALITY'";
$bomb_query="Select 'Bomb Scares' as 'Incident Type', " . $dynamic_query . $join . " WHERE Tbl_BFS_Statistics_Incident_Types.Fld_Classification='BOMB'";
echo " ";
echo " ";
echo " ";
echo "";
echo $header;
echo "show_hide_category("Fires") . ">";
echo " | Classification of Fires | ";
$this->output_blank_columns($this->sub_header_color, $num_cols);
echo " ";
$result_handler=mysql_query($fire_query, $this->db_link);
if($result_handler and mysql_affected_rows($this->db_link)>0){
$row_count=0;
while($data=mysql_fetch_row($result_handler)){
$this->output_row("Fires", &$vertical_total,&$vertical_grand_total, $data, ($row_count % 2) ? $this->dark_color : $this->light_color );
$row_count++;
}
}
$this->output_vertical_total("Fires", &$vertical_total);
echo "show_hide_blank_columns("Fires") . " >";
$this->output_blank_columns($bgcolor, $num_cols+1);
echo " ";
echo "show_hide_category("Special Services") . ">";
echo " | Special Services | ";
$this->output_blank_columns($this->sub_header_color, $num_cols);
echo " ";
$result_handler=mysql_query($motor_vehicle_query, $this->db_link);
if($result_handler and mysql_affected_rows($this->db_link)>0){
$data=mysql_fetch_row($result_handler);
$this->output_row("Special Services", &$vertical_total,&$vertical_grand_total, $data, $this->dark_color);
}
$result_handler=mysql_query($other_emergencies_query, $this->db_link);
if($result_handler and mysql_affected_rows($this->db_link)>0){
$data=mysql_fetch_row($result_handler);
$this->output_row("Special Services", &$vertical_total,&$vertical_grand_total, $data, $this->light_color);
}
$result_handler=mysql_query($non_emergencies_query, $this->db_link);
if($result_handler and mysql_affected_rows($this->db_link)>0){
$data=mysql_fetch_row($result_handler);
$this->output_row("Special Services", &$vertical_total,&$vertical_grand_total, $data, $this->dark_color);
}
$this->output_vertical_total("Special Services", &$vertical_total);
echo "show_hide_blank_columns("Special Services") . ">";
$this->output_blank_columns($bgcolor, $num_cols+1);
echo " ";
echo "show_hide_category("Airport Services") . " >";
echo " | Airport Services | ";
$this->output_blank_columns($this->sub_header_color, $num_cols);
echo " ";
$result_handler=mysql_query($airport_query, $this->db_link);
if($result_handler and mysql_affected_rows($this->db_link)>0){
$row_count=0;
while($data=mysql_fetch_row($result_handler)){
$this->output_row("Airport Services", &$vertical_total,&$vertical_grand_total, $data, ($row_count % 2) ? $this->dark_color : $this->light_color );
$row_count++;
}
}
$this->output_vertical_total("Airport Services", &$vertical_total);
echo "show_hide_blank_columns("Airport Services") . ">";
$this->output_blank_columns($bgcolor, $num_cols+1);
echo " ";
$result_handler=mysql_query($fatalities_query, $this->db_link);
if($result_handler and mysql_affected_rows($this->db_link)>0){
$data=mysql_fetch_row($result_handler);
$this->output_row("Fatalities",&$vertical_total,&$vertical_grand_total, $data, $this->sub_header_color);
}
echo "show_hide_blank_columns("Fatalities") . ">";
$this->output_blank_columns($bgcolor, $num_cols+1);
echo " ";
$result_handler=mysql_query($bomb_query, $this->db_link);
if($result_handler and mysql_affected_rows($this->db_link)>0){
$data=mysql_fetch_row($result_handler);
$this->output_row("Bomb Scares",&$vertical_total,&$vertical_grand_total, $data, $this->sub_header_color);
}
echo "show_hide_blank_columns("Bomb Scares") . ">";
$this->output_blank_columns($bgcolor, $num_cols+1);
echo " ";
$this->output_vertical_grand_total(&$vertical_grand_total);
echo " ";
echo " | ";
echo "
";
echo "
";
}
function show_hide_category($category){
if(strcmp($this->the_category,$category)<>0 and strcmp($this->the_category,"All")<>0){
return " style='display:none' ";
}
}
function show_hide_total($category){
if(strcmp($this->the_category,$category)==0 and strcmp($this->the_item,"All")==0){
}
elseif(strcmp($this->the_category,"All")==0){
}
else{
return " style='display:none' ";
}
}
function show_hide_blank_columns($category){
if(strcmp($this->the_category,"All")==0){
}
else{
return " style='display:none' ";
}
}
function show_hide_item($category, $item){
$item = str_replace("","" ,str_replace("","" ,$item));
if(strcmp($this->the_category,"All")==0){
}
elseif(strcmp($this->the_category,$category)==0 and strcmp($this->the_item,"All")==0){
}
elseif(strcmp($this->the_category,$category)==0 and strcmp($this->the_item,$item)==0){
}
else{
return " style='display:none' ";
}
}
function connect_to_db(){
// $this->db_link=mysql_connect("fireservice.gov.bb","fireservice", "5tgbnhy6");
// $this->db_link=mysql_connect("192.168.100.20","mbelle", "mbelle");
$this->db_link=mysql_connect("localhost","fireserv_dba", "3edcvfr4");
if(!$this->db_link){
echo "Unable to connect to the Server
";
exit;
}
// if(!mysql_select_db("BFS")){
if(!mysql_select_db("fireserv_db")){
echo "Unable to select the Database
";
exit;
}
}
function output_blank_columns($bgcolor, $num_cols){
for($x=0;$x<$num_cols;$x++){
echo " | ";
}
if($this->show_total_column){
echo " | ";
}
}
function output_vertical_total($category,&$vertical_total){
$sub_total=0;
echo " show_hide_total($category) . ">| Total | ";
for($x=0;$x". $vertical_total[$x] ."";
$sub_total=$sub_total+$vertical_total[$x];
}
if($this->show_total_column){
echo " $sub_total | ";
}
echo "
";
$this->reset_totals(&$vertical_total);
}
function output_vertical_grand_total(&$vertical_grand_total){
$grand_total=0;
echo " the_category=="All" ? "" : " style='display:none' " ) . " >| Grand Total | ";
for($x=0;$x". $vertical_grand_total[$x] ."";
$grand_total=$grand_total+$vertical_grand_total[$x];
}
if($this->show_total_column){
echo " $grand_total | ";
}
echo "
";
}
function output_row($category, &$vertical_total,&$vertical_grand_total, $data, $bg_color){
if($this->the_item <> "All"){
$bg_color="FFFFFF";
}
$horizontal_total=0;
echo " show_hide_item($category,$data[0]) . ">";
echo " | " . $data[0] . " | ";
for($x=1;$x". $data[$x] ."";
$horizontal_total=$horizontal_total+$data[$x];
if($category<>"Fatalities"){
$vertical_total[($x-1)]=$vertical_total[($x-1)]+$data[$x];
$vertical_grand_total[($x-1)]=$vertical_grand_total[($x-1)]+$data[$x];
}
}
if($this->show_total_column){
echo " ". $horizontal_total ." | ";
}
echo "
";
}
function output_date_combo_box($name, $s_year){
echo "";
}
function reset_totals(&$total_array){
for($x=0;$xdb_link);
if(!$result_handler or mysql_affected_rows($this->db_link)<=0){
echo "Unable to connect to the database. Please contact your database administrator
";
exit;
}
$data=mysql_fetch_array($result_handler);
$this->min_year=$data["MinYear"];
$result_handler=mysql_query($get_upper_limit_query,$this->db_link);
if(!$result_handler or mysql_affected_rows($this->db_link)<=0){
echo "Unable to connect to the database. Please contact your database administrator
";
exit;
}
$data=mysql_fetch_array($result_handler);
$this->max_year=$data["MaxYear"];
if (is_null($this->min_year)){
$this->min_year=$this->this_year;
$this->max_year=$this->this_year;
}
}
}
?>