<?php
class Query_report
{
var $template_file_info;
var $related_orgs_array;
var $template_table_html;
var $template_row_labels;
var $cross_query_stored_value;
// var $template
function pull_file_name_and_title ()
{
GLOBAL $report_template_path;
$handle = opendir( $report_template_path."." );
while ( ($file = readdir($handle)) !== false )
{
if ($file != "." && $file != "..")
{
//echo "File name is: ".$file."<p>";
$this->template_file_info[$file] = $this->find_report_title_in_template ($file);
}
}
closedir($handle);
}
function pull_table_html ( $template_html )
{
//echo 'The template again from pull table html is: '.$template_html.'<p/>';
$start_pos = strpos ( $template_html, "<table>" );
$end_pos = strpos ( $template_html, "</table>" );
$len = ( $end_pos - $start_pos ) + 8;
$this->template_table_html = substr ( $template_html, $start_pos, $len );
//preg_match_all( '/\<table>.*?\<\/table>/', $template_html, $tags );
//$this->template_table_html = $tags[0][0];
//echo 'The template from pull_table_html is: '.$this->template_table_html.'<p/>\n';
//Remove the table tags
$this->template_table_html = str_replace ( "<table>", '', $this->template_table_html );
$this->template_table_html = str_replace ( "</table>", '', $this->template_table_html );
return $this->template_table_html;
}
function pull_row_labels_html ( $template_html )
{
preg_match_all( '/\<tr class=\"rowlabels\">.*?\<\/tr>/', $template_html, $tags );
$this->template_row_labels = $tags[0][0];
return $this->template_row_labels;
}
function find_report_title_in_template ($file_name)
{
GLOBAL $report_template_path;
if ( $file_name != "CVS" )
{
$template_html = @implode("", (@file($report_template_path.$file_name)));
if(!$template_html){echo "No Template";}
preg_match_all( '/\<title>.*?\<\/title>/', $template_html, $tags );
$this->template_page_title = strip_tags($tags[0][0]);
return strip_tags($tags[0][0]);
}
}
function create_reports_list_table ()
{
GLOBAL $system_directory;
$this->pull_file_name_and_title ();
$ht .= "<table>";
foreach ( $this->template_file_info AS $file_name => $report_title ) {
$ht .= "<tr><td><a
href=\"".$system_directory."query_report.php?report_template=".$file_name."&query_page=blank\">".$report_title."</a></td></tr>";
}
$ht .= "</table>";
return $ht;
}
/********************************************************************************************
* Name: output_report:
* Description: Outputs APR html or html results of a predefined or user created query. Nothing returned.
*
* Details and Notes:
*
* @param: $report_template
* @param: $query_page
* @param: $form_answers
* @return: null
*
* History: Created 1/17/03 by Tedd.
********************************************************************************************/
function output_report ($report_template, $query_page, $form_answers)
{
GLOBAL $group_id, $org_id, $access_level, $unix_date, $this_page, $tag_values, $report_template_path;
$dates = new Report_date_questions();
$qr = new Query;
switch ( $query_page )
{
case 'blank':
$last_year_unix = time_add ( 'Y', -1, $unix_date );
$next_day_unix = time_add ( 'd', 1, $unix_date );
$dates = new Report_date_questions($last_year_unix, $next_day_unix);
$last_year_unix = date_encrypted_find_index_unix_time ( $last_year_unix );
$next_day_unix = date_encrypted_find_index_unix_time ( $next_day_unix );
//$unix_dates['begin'] = $last_year_unix;
//$unix_dates['end'] = $next_day_unix;
$ht .= $qr->build_related_organizations_checkboxes_table ($group_id, $org_id, $access_level, $form_answers, $query_page);
$this->related_orgs_array = $qr->pull_related_organizations ($group_id, $org_id, $access_level);
$ht .= $dates->build_report_date_table ( $valid_date->vetted_form_answer );
$tag_values["{FORM_START}"] = form_start ($this_page."?query_page=submitted&report_template=".$report_template, "_blank");
if ( $access_level == 10 )
{
$tag_values['{FORM_END}'] = "<p/>Results by group<input name=\"form_answer[by_group]\" type=\"checkbox\" value=\"yes\"><p/>".$tag_values['{FORM_END}'];
}
return $ht;
break;
case "submitted":
$dates = new Report_date_questions();
$valid_date = new Questions_answers_validation($dates->questions, $dates->question_elements, $form_answers);
$dates->validate_dates_exist ( true );
$unix_dates = $dates->translate_report_dates_to_unix_time($valid_date->vetted_form_answer);
$unix_dates['begin'] = date_encrypted_find_index_unix_time ( $unix_dates['begin'] );
$unix_dates['end'] = date_encrypted_find_index_unix_time ( $unix_dates['end'] );
if ( $form_answers['by_group'] == 'yes' )
{
echo $this->output_report_result_by_group ($report_template, $form_answers, $unix_dates);
//echo "by group?<p>";
}
else
{
echo $this->output_report_result ($report_template, $form_answers, $unix_dates);
//echo "NOT by group?<p>";
}
break;
default:
echo "In the default query display"; exit;
}
}
/********************************************************************************************
* Name: output_report_result:
* Description: Returns APR html or html results of a predefined or user created query.
*
* Details and Notes: Called by output_report above.
*
* @param: $report_template
* @param: $form_answers
* @param: $unix_date - unix timestamp, seconds since 1/1/1970
* @return: html
*
* History: Created 1/17/03 by Tedd.
********************************************************************************************/
function output_report_result ( $report_template, $form_answers, $unix_dates )
{
GLOBAL $report_template_path, $debug, $group_id, $org_id, $access_level;
//echo "Path is: ".$report_template_path.$report_template."<p>";
$template_html = @implode("", (@file($report_template_path.$report_template)));
if(!$template_html){echo "No template for form";}
preg_match_all( '/\{.*?\}/', $template_html, $tags );
//Put the standard css info into head
$this->pull_file_name_and_title ();
$replace_head_text = head ($this->template_file_info[$report_template], "", "");
$template_html = str_replace ( "<title>".$this->template_file_info[$report_template]."</title>", $replace_head_text, $template_html);
$zz = new Query;
$orgs_array = $zz->pull_related_organizations ($group_id, $org_id, $access_level);
$final_table_html = $this->translate_tags_to_answers ( $template_html, $tags[0], $orgs_array, $form_answers, $unix_dates );
$ht .= $final_table_html;
return $ht;
}
function output_report_result_by_group ( $report_template, $form_answers, $unix_dates )
{
GLOBAL $report_template_path, $debug, $group_id, $org_id, $access_level;
//echo "Path is: ".$report_template_path.$report_template."<p>";
$template_html = @implode("", (@file($report_template_path.$report_template)));
if(!$template_html){echo "No template for form";}
//echo "The template stright from file is: ".$template_html."<p>";
////If we are requesting a report sorted group/county, pull out the label piece of the template
$table_html = $this->pull_table_html ( $template_html );
//echo "The raw html table template is: ".$table_html."<p>";
$labels_row = $this->pull_row_labels_html ( $table_html );
//echo "The lable row is: ".$labels_row."<p>";
//Pull the labels row out
$table_html_template = str_replace ( $labels_row, '', $table_html );
//echo "The html template with no row labels is: ".$table_html_template."<p>";
//Loop through the groups
$sql = 'SELECT * FROM groups WHERE group_id != \'1\' ORDER BY group_name';
$grps_array = run_query_return_array ( $sql, 'Pulling list og groups' );
preg_match_all( '/\{.*?\}/', $table_html_template, $tags );
//Loop through the groups
foreach ( $grps_array AS $cur_grp )
{
//echo $cur_grp['group_name'].' Here<p/>';
//Pull out the related orgs
//Function in "user_info_funtions.inc"
$orgs_array = pull_organizations_info_associated_with_group ( $cur_grp['group_id'] );
//var_dump ( $orgs_array );
//echo "<p>";
$zz = new Query();
$modded_table_html_template = str_replace ( '{GROUP_NAME}', $cur_grp['group_name'], $table_html_template );
// Check all the checkboxes yes
if ( count ( $orgs_array ) > 1 )
{
foreach ( $orgs_array AS $cur_org )
{
$form_answers['included_orgs'][$cur_org['org_id']] = 'yes';
//echo "--------Adding id ".$cur_org['org_name']."<br>";
}
$final_table_html .= $this->translate_tags_to_answers ( $modded_table_html_template, $tags[0], $orgs_array, $form_answers, $unix_dates );
}
}
//Put the standard css info into head
$this->pull_file_name_and_title ();
$replace_head_text = head ( $this->template_file_info[$report_template], '', '' );
//$template_html = str_replace ( "<title>".$this->template_file_info[$report_template]."</title>", $replace_head_text,
//$template_html );
$ht = '<html>'.$replace_head_text.'<table>'.$labels_row.$final_table_html.'</table></body></html>';
return $ht;
}
/********************************************************************************************
* Name: translate_tags_to_answers:
* Description: fills $tags array with values. Nothing returned.
*
* Details and Notes:
*
* @param: $html_template
* @param: $tags
* @param: $orgs_array
* @param: $form_answers
* @param: $unix_dates
* @return: null
*
* History: Created 1/17/03 by Tedd.
********************************************************************************************/
function translate_tags_to_answers ( $html_template, $tags, $orgs_array, $form_answers, $unix_dates )
{
//echo '<hr color=red>tags:'.pretty_var_dump($tags).'<hr color=red>';
//Loop through the tags now stored in $tags[0], replacing the tag with the results of the associated SQL query
foreach ( $tags AS $current_tag )
{
//Strip off the { and } characters
$actual_query_name = str_replace ( "{", "", $current_tag);
$actual_query_name = str_replace ( "}", "", $actual_query_name);
//echo "Actual name is: ".$actual_query_name."<p>";
//Pull out the query
$sql = "SELECT * FROM pre_built_queries WHERE query_title LIKE '".$actual_query_name."'";
//echo "<p>".$sql;
$sql_result = run_query_return_single_row ( $sql, 'Pulling SQL query from database' );
//echo "<p>SQL result is: ".$sql_result[1]."<br/>";
//Replace the date tags with selected values
$final_sql = str_replace ( '{date_begin}', $unix_dates['begin'] + 1, $sql_result[1] );
$final_sql = str_replace ( '{date_end}', $unix_dates['end'] + 1, $final_sql );
////Replace the {report_profile[number]} tag with the apprpriate organization selection SQL
//Pull out the remaining tags
preg_match_all( '/\{.*?\}/', $final_sql, $org_tags );
foreach ( $org_tags[0] AS $current_org_tag )
{
if ( ereg ( 'report_profile', $current_org_tag ) )
{
preg_match ( '/\d+/', $current_org_tag, $join_count );
//echo "<p/>The Join count is: ".$join_count[0]."<p/>";
//$this->related_orgs_array = $zz->pull_related_organizations ($group_id, $org_id, $access_level);
$tmp_sql = $this->build_org_sql (' report_profile', $orgs_array, $join_count[0], $form_answers );
}
elseif ( ereg ( 'client_rpt_profile', $current_org_tag ) )
{
preg_match ( '/\d+/', $current_org_tag, $join_count );
$tmp_sql = $this->build_org_sql ( 'client_rpt_profile', $orgs_array, $join_count[0], $form_answers );
}
elseif ( ereg ( '{date_begin_year}', $current_org_tag ) )
{
$date_begin_unix = date_encrypted_translate_index_to_unix_time ( $unix_dates['begin'] );
$tmp_sql = safe_date( 'Y', $date_begin_unix );
}
elseif ( ereg ( '{date_end_year}', $current_org_tag ) )
{
$date_end_unix = date_encrypted_translate_index_to_unix_time ( $unix_dates['end'] );
$tmp_sql = safe_date( 'Y', $date_end_unix );
}
elseif ( ereg ( '{DAYS_AGO-', $current_org_tag ) )
{
$date_end_unix = date_encrypted_translate_index_to_unix_time ( $unix_dates['end'] );
$actual_days_ago = str_replace ( '{DAYS_AGO-', '', $current_org_tag );
$actual_days_ago = str_replace ( '}', '', $actual_days_ago );
$days_ago_in_seconds = $actual_days_ago * 86400;
$long_ago_unix_date = $date_end_unix - $days_ago_in_seconds;
$long_ago_date_index = date_encrypted_find_index_unix_time ( $long_ago_unix_date );
$tmp_sql = $long_ago_date_index;
}
elseif ( ereg ( '{CALC-', $current_org_tag ) )
{
//$date_end_unix = date_encrypted_translate_index_to_unix_time ( $unix_dates['end'] );
//$tmp_sql = safe_date( 'Y', $date_end_unix );
$tmp_sql = $current_org_tag;
//echo "Cru tag is: ".$current_org_tag.'<br/>';
}
else
{
//echo "Cur org tag is ".$current_org_tag."<br/>";
$actual_table_name = str_replace ( '{', '', $current_org_tag );
$actual_table_name = str_replace ( '}', '', $actual_table_name );
//$actual_table_name = str_replace ( '_NULL', '', $actual_table_name );
$tmp_sql = $this->build_org_sql ( $actual_table_name, $orgs_array, 1, $form_answers, 'no_alias' );
}
$final_sql = str_replace ( $current_org_tag, $tmp_sql, $final_sql );
$tmp_sql = '';
}
//Debug
/*if (preg_match( '/income/', $actual_query_name))
{
echo '<table border=1 bordercolor=red><tr><td>'.$actual_query_name.':'.$final_sql.'</td></tr></table>';
}*/
//
//echo "<p/>The final SQL is $actual_query_name:<br/>".$final_sql."<p>";
//preg_match ("/^\d{1}/", $this->user_access_level, $access_level);
//$access_level[0]
switch ( $sql_result['query_type'] )
{
case 'count':
$answer = $this->calculate_count ( $final_sql, $current_tag );
$this->cross_query_stored_value[$current_tag] = $answer;
break;
case 'sum':
$answer = $this->calculate_sum ( $final_sql, $current_tag );
$this->cross_query_stored_value[$current_tag] = $answer;
break;
// case "household_count":
// $answer = $this->calculate_count ( $final_sql);
// break;
case 'household_service_days':
$answer = $this->calculate_household_service_days ( $final_sql );
$this->cross_query_stored_value[$current_tag] = $answer;
break;
case 'client_service_days':
$answer = $this->calculate_client_service_days ( $final_sql );
$this->cross_query_stored_value[$current_tag] = $answer;
break;
case 'client_list':
$answer = $this->build_client_list_table ( $final_sql, $sql_result['client_list_elements_template'] );
$this->cross_query_stored_value[$current_tag] = $answer;
break;
case 'cross_query_calculation':
$answer = $this->cross_query_calculation ( $final_sql );
$this->cross_query_stored_value[$current_tag] = $answer;
break;
case 'hidden':
$answer = $this->calculate_sum ( $final_sql, $current_tag );
$this->cross_query_stored_value[$current_tag] = $answer;
$answer = ' ';
break;
default:
$answer = 'No query type defined';
}
if ( $answer != 'No query type defined' && $answer != ' ' )
{
$answer = number_format ( $answer );
}
$html_template = str_replace ( $current_tag, $answer, $html_template );
//settype ( $this->cross_query_stored_value[$current_tag] , 'int' );
}
return $html_template;
}
function build_org_sql ( $profile_type, $org_array, $join_count, $form_answers, $no_alias='' )
{
//If there are organizations included in query
//var_dump ($form_answers);
if ( is_array ( $org_array ) ) {
//if ( $form_answers['included_orgs'] ) {
for ( $i=1; $i <= $join_count; $i++ ) {
//if ($i != 0) {
////$sql .= ' AND (';
//}
//display_value($org_array);
foreach ( $org_array AS $org_id => $org_name ) {
if ($form_answers["included_orgs"][$org_id] == "yes")
{
if ($z != 0)
{
$sqla .= ' OR ';
}
if ( $no_alias == 'no_alias' )
{
$alias = '';
}
else {
$alias = $i;
}
if ( ereg ( '_NULL', $profile_type ) )
{
$actual_table_name = str_replace ( '_NULL', '', $profile_type );
$null_alternative = 'yes';
}
else
{
$actual_table_name = $profile_type;
}
$sqla .= $actual_table_name.$alias.".report_org_id = '".$org_id."'";
$z++;
}
}
if ( $null_alternative == 'yes' )
{
$sqla .= ' OR '.$actual_table_name.$alias.".report_org_id IS NULL";
}
if ( $z > 0 )
{
$sql .= ' AND ('. $sqla.' )';
$sqla = '';
}
$z = 0;
}
}
//echo "<p>The org_sql is: ".$sql."</p>";
return $sql;
}
function calculate_count ($sql, $tag )
{
$result = run_query ($sql, "Pulling result of SQL for template - ".$tag );
$answer = num_rows($result);
/*
for ($i=0; $i < $answer; $i++ ) {
$client_id = fetch_array ($result, "mess", $i);
//echo $client_id[0]."<br>";
}
//echo "<p>";
*/
return $answer;
}
// function calculate_household_count ($sql) {
// $result = run_query ($sql, "Pulling result of SQL for template");
// $answer = num_rows($result);
// return $answer;
// }
function calculate_sum ( $sql, $tag )
{
$result = run_query ($sql, 'Pulling result of SQL for template - '.$tag );
$answer = fetch_result ( $result, 'Pulling sum- '.$tag);
/*
for ($i=0; $i < $answer; $i++ ) {
$client_id = fetch_array ($result, "mess", $i);
//echo $client_id[0]."<br>";
}
//echo "<p>";
*/
//echo "Sum is ".$answer."<p>";
if ( strlen ( $answer ) < 1 )
{
settype ( $answer, 'string' );
$answer = '0';
}
return $answer;
}
function calculate_household_service_days ($sql)
{
$resultB = run_query ($sql, "Pulling result of SQL for template");
$row_count = num_rows ($resultB);
for ( $i = 0; $i < $row_count; $i++ )
{
$date_range_array = fetch_array ( $resultB, "Fetching date sql", $i );
$sqlb = "SELECT * FROM report_relationship WHERE hh_report_id =
'".$date_range_array["hh_report_id"]."'";
$cnt_count_result = run_query ($sqlb, "Result for client count" );
$clnt_count = num_rows ($cnt_count_result);
$service_seconds = $service_seconds + (( $date_range_array["report_date_end"] - $date_range_array["report_date_begin"] ) * $clnt_count);
}
//Seconds in a day is: 86400
$service_days = $service_seconds / 86400;
//echo "Household service seconds is: ".$service_days."<br>";
//echo "Household service days is: ".$service_days."<br>";
return $service_days;
}
function calculate_client_service_days ($sql)
{
$resultB = run_query (sql, "Running query SQL");
$row_count = num_rows ($resultB);
for ( $i = 0; $i < $row_count; $i++ )
{
$date_range_array = fetch_array ( $resultB, "Fetching date sql", $i );
$sqlb = "SELECT * FROM client_rpt_profile WHERE client_rpt_id =
'".$date_range_array["client_rpt_id"]."'";
$cnt_count_result = run_query ($sqlb, "Result for client count" );
//Is the below necessary? Test
$clnt_count = num_rows ($cnt_count_result);
$service_seconds = $service_seconds + ( $date_range_array["report_date_endc"] - $date_range_array["report_date_beginc"] ) ;
}
//Seconds in a day is: 86400
$service_days = $service_seconds / 86400;
//echo "Household service seconds is: ".$service_days."<br>";
//echo "Household service days is: ".$service_days."<br>";
return $service_days;
}
function build_client_list_table ( $final_sql, $client_list_elements_template )
{
if ( !$client_list_elements_template ) {
//Default template; 100 is year born, 101 is gender, 102 is ethnicity
$client_list_elements_template = "<td>{org_generated_client_id}</td><td>{101}</td><td>{102}</td><td>{age}</td><td>{link_to_client_profile}</td>";
}
//Pull the client list result
//echo $final_sql."<p>";
$result = run_query ($final_sql, "Pulling result of SQL for client list template");
$num_clients = num_rows($result);
//Create an array of the tages in the client template
preg_match_all( '/\{.*?\}/', $client_list_elements_template, $tags );
$cnt_prof = new Client_profile;
//Loop through the client ids
for ( $i = 0; $i < $num_clients; $i++ )
{
$client_id = fetch_array ( $result, 'Pulling client id for client list', $i );
//echo 'Client id is'.$client_id[0].'<p/>';
//Pull the individual client profile
$client_info_array = $cnt_prof->pull_client_profile_array ( $client_id[0] );
//var_dump ( $client_info_array );
//Freach template for current client line
$client_table_row = $client_list_elements_template;
//Loop through the tags now stored in $tags[0], replacing the tag with the results
foreach ( $tags[0] AS $current_tag )
{
//Strip off the { and } characters
$actual_tag_value = str_replace ( "{", "", $current_tag);
$actual_tag_value = str_replace ( "}", "", $actual_tag_value);
$display_value = $this->find_value_for_client_attribute_tag ( $actual_tag_value, $client_info_array );
//echo 'Display value is: '.$display_value;
$client_table_row = str_replace ( $current_tag, $display_value, $client_table_row );
}
$final_html .= '<tr>'.$client_table_row.'</tr>';
}
return $final_html;
}
function find_value_for_client_attribute_tag ( $actual_tag_value, $client_info_array )
{
switch ( $actual_tag_value )
{
case '102':
$return_this = $this->convert_array_answer_to_single_string ( $client_info_array[102] );
//$return_this = 'booby';
break;
case 'age':
$return_this = $this->convert_year_born_to_current_age ( $client_info_array[100] );
break;
case 'link_to_client_profile':
$return_this = "<a class=\"smaller75\"
href=\"".$system_directory."client_overview.php?display_client_overview=".$client_info_array['client_id']."\">Link to Client
Overview</a>";
break;
default:
$return_this = $client_info_array[$actual_tag_value];
}
return $return_this;
}
function convert_array_answer_to_single_string ( $answer_array )
{
foreach ( $answer_array AS $one_ethnicity )
{
if($ww > 0) {$ethnicity_output .= "-";}
$ww++;
$minus_underscore = str_replace("_", " ", $one_ethnicity);
$minus_underscore = ucwords($minus_underscore);
$ethnicity_output .= str_replace(" ", "-", $minus_underscore);
}
//$final_html .= "<td class=\"smalltd\">Race: ".$ethnicity_output."</td>";
//unset($ethnicity_output);
return $ethnicity_output;
}
function convert_year_born_to_current_age ( $age_answer )
{
GLOBAL $unix_date;
$age = safe_date("Y", $unix_date) - $age_answer;
return $age;
}
function cross_query_calculation ( $final_sql )
{
preg_match_all( '/\{.*?\}/', $final_sql, $calc_tags );
//preg_match_all( '/\{.*?\}/', $template_html, $tags );
//echo "IN CROSS -- ".$final_sql.'<p/>';
$final_calc = $final_sql;
//var_dump ( $calc_tags );
foreach ( $calc_tags[0] AS $cur_tag )
{
//echo "calc tag isu : ".$cur_tag."<br/>";
$real_value = str_replace ( '{CALC-', '', $cur_tag );
$real_value = str_replace ( '}', '', $real_value );
//echo 'real value is: '.$real_value.'<br/>';
if ( !isset( $this->cross_query_stored_value['{'.$real_value.'}'] ) )
{
echo 'Value: '.$real_value.' has not been calculated<p>';
}
$real_value = str_replace ( $real_value, $this->cross_query_stored_value['{'.$real_value.'}'], $real_value );
$final_calc = $real_value = str_replace ( $cur_tag, $real_value, $final_calc );
//eval ();
}
//echo 'Final calc is: '.$final_calc.'<p/>';
eval ( '$final_html = '.$final_calc.';' );
//echo "TADAAA ".$final_html.'<p/>';
if ( strlen ( $final_html ) < 1 ) {
settype ( $final_html, 'string' );
$answer = '0';
}
return $final_html;
}
}// END Class
?>