<?php
//*Client Data System, Copyright (C) 2000, 2001, 2002, 2003 Tedd Kelleher. This is free software, subject to the
//*GNU GENERAL PUBLIC LICENSE, Version 2, June 1991 (in file named gpl.txt), which should accompany
//*any distribution of this file. Tedd Kelleher can be contacted at hide@address.com
set_time_limit(3000);
$page_access_levels = ":10:";
//$page_profile = "View Client Information";
////Header for every page that finds the include directory, connects to db, authenticates user access
include("initialize_pointer.php");
if(!include($include_root."authenticate.inc")){echo "No Authentication"; exit; }
include($include_root."Questions_into_array_class.inc");
//include($include_root."questions_display.inc");
//include($include_root."Questions_answers_validation_class.inc");
//include($include_root."question_set_validation_function.inc");
//include($include_root."questions_into_tags_function.inc");
include($include_root."head.inc");
include($include_root."template_parser.inc");
include($include_root."page_elements_display.inc");
//include($include_root."client_id_class.inc");
include($include_root."client_report_class.inc");
//include($include_root."insert_household_report_class.inc");
//include($include_root."insert_client_report_class.inc");
//include($include_root."client_profile_class.inc");
include($include_root."reports_class.inc");
//include($include_root."report_date_questions_class.inc");
//include($include_root."relationships_class.inc");
//include($include_root."user_info_functions.inc");
//include($include_root."permissions_class.inc");
//include($include_root."add_organization_class.inc");
//include($include_root."add_users_class.inc");
include($include_root."encryption_class.inc");
include($include_root.'date_encrypted_functions.inc');
include($include_root.'services_functions.inc');
foreach ( $_GET['report_type'] AS $cur_rpt_type )
{
if ( ereg ('[^A-Za-z0-9_ ]', $cur_rpt_type ) )
{
$tag_values["{FORM_END}"] .= 'Illegal report type';
exit;
}
else
{
$report_type = $cur_rpt_type;
}
if ( $report_type == 'intake_exit_events' )
{
intake_exit_events_table( $report_type );
}
else
{
$sql = "SELECT * FROM report_type_profile WHERE report_type_id LIKE '".$report_type."'";
//echo $sql;
$res = run_query ( $sql, 'Pulling report on unit' );
//Test to see that there really is such a report type
if ( $res )
{
$tag_values["{FORM_END}"] .= 'Report type: '.$report_type.' exists<p>';
$u = fetch_array ( $res, 'Report on unit for consolidation', 0 );
//Test to see if the consolidation table already exists
if ( $u['report_on_unit'] == 'client' || $u['report_on_unit'] == 'client_identifier' || $u['report_on_unit'] == 'client_service' )
{
$tag_values["{FORM_END}"] .= 'A client type report<p>';
$sqlt = 'SELECT * FROM cnsl_'.$report_type;
if ( $db_file == 'db_connection_mssql' )
{
$test = @mssql_query ($sqlt);
}
else
{
$test = @pg_exec ($db_link, $sqlt);
}
//Table does not exist, so create it
if ( !$test )
{
$tag_values["{FORM_END}"] .= 'Creating table cnsl_'.$report_type.'<p/>';
build_consolidated_report_table_client ( $report_type );
}
$tag_values["{FORM_END}"] .= 'Updating client report table: cnsl_'.$report_type.'<p/>';
update_consolidated_report_client ( $report_type );
}
//Test to see if the consolidation table already exists
if ( $u['report_on_unit'] == 'household' || $u['report_on_unit'] == 'household_service' )
{
$tag_values["{FORM_END}"] .= 'A household type report<p>';
$sqlt = 'SELECT * FROM cnsl_'.$report_type;
if ( $db_file == 'db_connection_mssql' )
{
$test = @mssql_query ($sqlt);
}
else
{
$test = @pg_exec ($db_link, $sqlt);
}
//Table does not exist, so create it
if ( !$test )
{
$tag_values["{FORM_END}"] .= 'Creating table cnsl_'.$report_type.'<p/>';
build_consolidated_report_table_household ( $report_type );
}
$tag_values["{FORM_END}"] .= 'Updating household report table: cnsl_'.$report_type.'<p/>';
update_consolidated_report_household ( $report_type );
}
}
}
}
$page_title = "Consolidate Reports";
$instructions = "This option prepares data for viewing an APR report.";
$tag_values["{FORM_START}"] = " ";
$html_template = "generic_form.html";
$tag_values["{PAGE TITLE}"] = page_title_table();
$tag_values["{MENU}"] = menu();
$tag_values["{HEAD}"] = head ($page_title, $head_page_specific, $head_dynamic_style);
$tag_values["{FOOTER}"] = footer();
template_parser ($html_template, $tag_values);
function build_consolidated_report_table_client ( $report_type_id )
{
//$table_name = substr( 'cnsl_'.$report_type_id, 0, 31) ;
$table_name = 'cnsl_'.$report_type_id;
//echo "Table anme is ".$table_name;
//exit;
$q = new Questions_into_array ( $report_type_id, '10', 1, 1, 'display' );
//Create the client report profile piece of the creation SQL
$make_table_sql = '
CREATE TABLE '.$table_name.' (
client_id INT,
client_rpt_id INT,
report_type VARCHAR(40),
report_org_id INT,
report_user_id INT,
report_timestamp VARCHAR(50),
report_date_beginc INT,
report_date_endc INT,
client_rpt_sharing_permission VARCHAR(10) NULL,
consol_timestamp INT, ';
//lead_client_id INT,
//total_clients INT,
//total_adults INT,
//total_minors INT,
//Pieces to improve query speed
$make_table_sql .= '
adult INT,
seconds_of_service INT, ';
$inx_arr[1] = 'client_id';
$inx_arr[2] = 'client_rpt_id';
$inx_arr[3] = 'report_type';
$inx_arr[4] = 'report_org_id';
$inx_arr[5] = 'report_user_id';
$inx_arr[6] = 'report_date_beginc';
$inx_arr[7] = 'report_date_endc';
$inx_arr[8] = 'adult';
$i = 0;
//Answers
foreach ( $q->questions AS $cur_q )
{
//echo "<p>QA type is ".$cur_q['question_type'].$cur_q['question_id'].'<br>';
if ( $i != 0 && $cur_q['question_type'] != 'text' && $cur_q['question_type'] != 'textarea' )
{
$make_table_sql .= ', ';
//echo "<p>QB type is ".$cur_q['question_type'].$cur_q['question_id'].'<p>';
}
$i++;
switch ( $cur_q['question_type'] )
{
case 'radio':
$make_table_sql .= 'q_'.$cur_q['question_id'].' VARCHAR(100) NULL';
break;
case 'number':
$make_table_sql .= 'q_'.$cur_q['question_id'].' INT NULL';
break;
case 'checkboxes':
foreach ( $q->question_elements[$cur_q['question_id']] AS $cur_q_element )
{
if ( $z != 0 )
{
$make_table_sql .= ', ';
}
$z++;
$make_table_sql .= 'q_'.$cur_q['question_id'].'_'.$cur_q_element['question_element_id'].' INT NULL';
}
$z = 0;
break;
case 'dropdown':
$make_table_sql .= 'q_'.$cur_q['question_id'].' VARCHAR(100) NULL';
break;
case 'date':
$make_table_sql .= 'q_'.$cur_q['question_id'].' INT NULL';
break;
case 'table':
foreach ( $q->question_elements[$cur_q['question_id']]['x'] AS $x_axis_id )
{
foreach ( $q->question_elements[$cur_q['question_id']]['y'] AS $y_axis_id )
{
if ( $z != 0 ) {
$make_table_sql .= ', ';
}
$z++;
$make_table_sql .= 'q_'.$cur_q['question_id'].'_'.$x_axis_id['axis_id'].'_'.$y_axis_id['axis_id'].' INT NULL';
$make_table_sql .= ', q_axis_'.$y_axis_id['axis_id'].'_TOTAL INT NULL';
}
$make_table_sql .= ', q_axis_'.$x_axis_id['axis_id'].'_TOTAL INT NULL';
}
$z = 0;
$yes_ans = 1;
break;
default:
$tag_values["{FORM_END}"] .= "<p>Q type is ".$cur_q['question_type'];
}
}
$make_table_sql .= ' )';
run_query ( $make_table_sql, 'Creating consolidation table' );
//Make associated indexes
foreach ( $inx_arr AS $cur_i )
{
//$sqli = 'CREATE INDEX cnsl_'.$report_type_id.'__'.$cur_i.' ON '.$table_name.'('.$cur_i.')';
$sqli = 'CREATE INDEX cnsl_'.substr( $report_type_id, 0, 1).substr( $report_type_id, -2 ).'__'.$cur_i.' ON '.$table_name.'('.$cur_i.')';
run_query ( $sqli, 'Createing consolidated report index' );
}
}
function build_consolidated_report_table_household ( $report_type_id )
{
//echo 'update_consolidated_report_client()<br>';
$table_name = 'cnsl_'.$report_type_id;
$q = new Questions_into_array ( $report_type_id, '10', 1, 1, 'display' );
//Create the client report profile piece of the creation SQL
$make_table_sql = '
CREATE TABLE '.$table_name.' (
hh_report_id INT,
report_date_begin INT,
report_date_end INT,
report_type VARCHAR(40),
report_org_id INT,
report_user_id INT,
report_timestamp VARCHAR(50),
report_sharing_permission VARCHAR(5) NULL,
consol_timestamp INT, ';
//Pieces to improve query speed
$make_table_sql .= '
lead_client_id INT,
total_clients INT,
total_adults INT,
total_minors INT,
seconds_of_service INT ';
$inx_arr[0] = 'hh_report_id';
$inx_arr[1] = 'report_date_begin';
$inx_arr[2] = 'report_date_end';
$inx_arr[3] = 'report_type';
$inx_arr[4] = 'report_org_id';
$inx_arr[5] = 'report_user_id';
$inx_arr[6] = 'report_timestamp';
$inx_arr[7] = 'consol_timestamp';
//var_dump ( $q->questions );
//Answers
//$q_count = count ( $q->questions );
foreach ( $q->questions AS $cur_q )
{
//if ( $i != 0 && $yes_ans == 1 ) {
// $make_table_sql .= ', ';
//}
$tag_values["{FORM_END}"] .= "Qid is ".$cur_q['question_id']." -- ".$cur_q['question_type']."<p>";
switch ( $cur_q['question_type'] )
{
case 'radio':
$make_table_sqlt = 'q_'.$cur_q['question_id'].' VARCHAR(100) NULL';
$yes_ans = 1;
break;
case 'number':
$make_table_sqlt = 'q_'.$cur_q['question_id'].' INT NULL';
$yes_ans = 1;
break;
case 'checkboxes':
foreach ( $q->question_elements[$cur_q['question_id']] AS $cur_q_element )
{
//echo "Qid is ".$cur_q['question_id'].'--'.$cur_q_element['question_element_id']."<p>";
//if ( $z != 0 ) {
//$make_table_sqlt .= ', \n';
//}
//$z++;
$make_table_sqlt .= ', q_'.$cur_q['question_id'].'_'.$cur_q_element['question_element_id'].' INT NULL';
//echo $make_table_sqlt;
}
// $z = 0;
$yes_ans = 0;
break;
case 'dropdown':
$make_table_sqlt = 'q_'.$cur_q['question_id'].' VARCHAR(100) NULL';
$yes_ans = 1;
break;
case 'date':
$make_table_sqlt = 'q_'.$cur_q['question_id'].' INT NULL';
$yes_ans = 1;
break;
case 'table':
$make_table_sqlt = 'q_'.$cur_q['question_id'].' INT NULL,';
$make_table_sqlt = 'q_'.$cur_q['question_id'].'_x INT NULL,';
$make_table_sqlt = 'q_'.$cur_q['question_id'].'_y INT NULL';
$yes_ans = 1;
break;
case 'text':
$yes_ans = 0;
break;
case 'textarea':
$yes_ans = 0;
break;
default:
//$make_table_sql .= "DEFF";
}
if ( $yes_ans == 1 )
{
$tag_values["{FORM_END}"] .= "q count is: ".$q_count."--i is ".$i."<br/>";
$make_table_sql .= ', '.$make_table_sqlt;
}
else
{
$make_table_sql .= $make_table_sqlt;
}
$make_table_sqlt = '';
$yes_ans = 0;
$i++;
}
$make_table_sql .= ' )';
$tag_values["{FORM_END}"] .= $make_table_sql."<p>";
//echo '<b>make_table_sql:</b><br>'.$make_table_sql;
run_query ( $make_table_sql, 'Creating consolidation table' );
//Make associated indexes
foreach ( $inx_arr AS $cur_i )
{
$sqli = 'CREATE INDEX cnsl_'.substr( $report_type_id, 0, 11 ).'__'.$cur_i.' ON '.$table_name.'('.$cur_i.')';
$tag_values["{FORM_END}"] .= $sqli."<p>";
run_query ( $sqli, 'Createing consolidated report index' );
}
}
function update_consolidated_report_client ( $report_type_id )
{
//echo 'update_consolidated_report_client()<br>';
$table_name = 'cnsl_'.$report_type_id;
$sql = 'SELECT client_rpt_id FROM '.$table_name.' ORDER BY client_rpt_id DESC';
//echo $sql.'<br>';
$result = run_query( $sql, 'Pulling last update' );
if ( num_rows ( $result ) > 0 )
{
$last_report_id = fetch_result ( $result, 'Pull last updated id' );
}
else
{
$last_report_id = 0;
}
$sqlb = "SELECT * FROM client_rpt_profile WHERE client_rpt_id > '".$last_report_id."' AND report_type LIKE '".$report_type_id."'";
//echo $sqlb.'<br>';
$resultb = run_query ( $sqlb, 'Pull all the newer reports' );
$new_rpt_count = num_rows ( $resultb );
//echo 'New Report Count: '.$new_rpt_count.'<br>';
$q = new Questions_into_array ( $report_type_id, '10', 1, 1, 'display' );
$en = new Encryption();
for ( $i = 0; $i < $new_rpt_count; $i++ )
{
$rpt_array = fetch_array ( $resultb, 'Pulling prt prof array', $i );
if ( strlen($rpt_array['succeeded_by']) < 1 )
{
$insert_sql_cols = '
client_id,
client_rpt_id,
report_type,
report_org_id,
report_user_id,
report_timestamp,
report_date_beginc,
report_date_endc,
client_rpt_sharing_permission,
consol_timestamp, ';
$insert_sql_vals = "
'".$rpt_array['client_id']."',
'".$rpt_array['client_rpt_id']."',
'".$rpt_array['report_type']."',
'".$rpt_array['report_org_id']."',
'".$rpt_array['report_user_id']."',
'".$rpt_array['report_timestamp']."',
'".$rpt_array['report_date_beginc']."',
'".$rpt_array['report_date_endc']."',
'".$rpt_array['client_rpt_sharing_permission']."',
'".time()."',
";
//lead_client_id INT,
//total_clients INT,
//total_adults INT,
//total_minors INT,
////Pieces to improve query speed
//Calculate if they are an adult
$insert_sql_cols .= '
adult, ';
//Calculate 18 years in seconds fro begining of report
//60 * 60 * 24 * 365.25 * 18 = 568036800
$begin = date_encrypted_translate_index_to_unix_time ( $rpt_array['report_date_beginc'] );
$year_of_report_start = safe_date( 'Y', $begin );
//echo "Year report started is ".$year_of_report_start."<p>";
$year_eighteen_years_prior_to_rpt_start = $year_of_report_start - 18;
$sqld = "
SELECT clients.client_id
FROM
clients
INNER JOIN
client_rpt_profile AS client_rpt_profile1 ON (clients.client_id = client_rpt_profile1.client_id)
INNER JOIN
client_rpt_answers AS client_rpt_answers1 ON (client_rpt_profile1.client_rpt_id = client_rpt_answers1.client_rpt_id)
WHERE
client_rpt_profile1.client_id = '".$rpt_array['client_id']."'
AND client_rpt_profile1.report_type LIKE 'client_profile'
AND client_rpt_profile1.succeeded_by IS NULL
AND client_rpt_answers1.client_rpt_question_id = '100'
AND client_rpt_answers1.client_rpt_answer_int < '".$year_eighteen_years_prior_to_rpt_start."'
";
//echo $sqld."<p>";
$res = run_query ( $sqld, 'Is an adult check' );
//if ($res
$matches = num_rows ( $res );
if ( $matches > 0 ) {
$insert_sql_vals .= "'1', ";
//echo "An adults<p>";
}
else {
$insert_sql_vals .= "'-1', ";
//echo "A child<p>";
}
//Calcuate length of service provided
$insert_sql_cols .= '
seconds_of_service, ';
$service_in_seconds = $rpt_array['report_date_endc'] - $rpt_array['report_date_beginc'];
$insert_sql_vals .= "'".$service_in_seconds."', ";
////Pull out the answers
$q = new Questions_into_array ( $report_type_id, '10', 1, 1, 'display' );
$cl = new Client_report;
$cl->pull_existing_client_report_answers ( $rpt_array['client_rpt_id'], $q->questions, $q->question_elements, 'do_not_modify');
//var_dump( $cl->vetted_frm_ans[0] );
//exit;
foreach ( $cl->vetted_frm_ans[0] AS $q_id => $cur_a )
{
if ( $p > 0 && $yes_ans == 1 )
{
$insert_sql_cols .= ', ';
$insert_sql_vals .= ', ';
}else {
//echo "HERR--".$q_id."<p>";
//echo pretty_var_dump ($cur_a);
//echo "---<p>";
}
$yes_ans = 0;
$p++;
switch ( $q->questions[$q_id]['question_type'] )
{
case 'radio':
$insert_sql_cols .= 'q_'.$q_id;
//$insert_sql_vals .= "'".$q->question_elements[$q_id][$cur_a]."'";
$insert_sql_vals .= "'".$q->question_elements[$q_id][$cur_a]['question_element_value']."'";
$yes_ans = 1;
break;
case 'number':
$insert_sql_cols .= 'q_'.$q_id;
$insert_sql_vals .= "'".$cur_a."'";
$yes_ans = 1;
break;
case 'checkboxes':
//Loop through all the possible answers...
foreach ( $q->question_elements[$q_id] AS $cur_q_element )
{
//echo "Current element id is ".$cur_q_element['question_element_id']." and current answer is ".$cur_a."<p>";
//...and add to sql if an answer matches
if ( $cur_a[$cur_q_element['question_element_id']] )
{
if ( $z != 0 )
{
$insert_sql_cols .= ', ';
$insert_sql_vals .= ', ';
}
$z++;
$insert_sql_cols .= 'q_'.$q_id.'_'.$cur_q_element['question_element_id'];
$insert_sql_vals .= "'1'";
}
}
$z = 0;
$yes_ans = 1;
break;
case 'dropdown':
$insert_sql_cols .= 'q_'.$q_id;
$insert_sql_vals .= "'".$q->question_elements[$q_id][$cur_a]['question_element_value']."'";
$yes_ans = 1;
break;
case 'table':
//Find the x axis number
foreach ( $cur_a AS $x_axis_num => $cur_b )
{
foreach ( $cur_b AS $y_axis_num => $actual_ans )
{
if ( $z != 0 )
{
$insert_sql_cols .= ', ';
$insert_sql_vals .= ', ';
}
$z++;
$insert_sql_cols .= 'q_'.$q_id.'_'.$x_axis_num.'_'.$y_axis_num.' ';
$insert_sql_vals .= "'".$actual_ans."' ";
$axis_total[$x_axis_num] = $axis_total[$x_axis_num] + $actual_ans;
$axis_total[$y_axis_num] = $axis_total[$y_axis_num] + $actual_ans;
$yes_ans = 1;
}
}
$z = 0;
break;
default:
}
}
if ( is_array ( $axis_total ) )
{
foreach ( $axis_total AS $axis_id => $final_total )
{
//if ( $p > 0 && $yes_ans == 1 ) {
$insert_sql_cols .= ', ';
$insert_sql_vals .= ', ';
//}
//$yes_ans = 0;
//$p++;
$insert_sql_cols .= 'q_axis_'.$axis_id.'_TOTAL';
$insert_sql_vals .= $final_total;
}
$axis_total = '';
}
$p = 0;
//Because it is possible to end with comma-space if the last question(s) are blank... can be done on profile form
if ( substr( $insert_sql_cols, -2 ) == ', ' )
{
$insert_sql_cols = rtrim($insert_sql_cols,', ');
$insert_sql_vals = rtrim($insert_sql_vals,', ');
}
$final_insert_sql = 'INSERT INTO '.$table_name.' ( '.$insert_sql_cols.' ) VALUES ( '.$insert_sql_vals.' )';
//echo '<b>final insert sql:</b><br>'.$final_insert_sql."<p>";
run_query ( $final_insert_sql, 'Inserting updates to consolidated report' );
}
}
//DELETE edited or deleted reports
$sql = "SELECT client_rpt_id FROM client_rpt_profile WHERE report_type = '".$report_type_id."' AND succeeded_by IS NOT NULL";
$result = run_query ( $sql, 'Finding deleted reports' );
$count = num_rows ( $result );
for ( $i = 0; $i < $count; $i++ )
{
$array = fetch_array ( $result, 'Pulling deleted', $i );
$sqldel = "DELETE FROM ".$table_name." WHERE client_rpt_id = '".$array['client_rpt_id']."'";
run_query ( $sqldel, 'Erasing reports' );
}
}
function update_consolidated_report_household ( $report_type_id )
{
$table_name = 'cnsl_'.$report_type_id;
$sql = 'SELECT hh_report_id FROM '.$table_name.' ORDER BY hh_report_id DESC';
$result = run_query( $sql, 'Pulling last update' );
if ( num_rows ( $result ) > 0 ) {
$last_report_id = fetch_result ( $result, 'Pull last updated id' );
}
else
{
$last_report_id = 0;
}
$sqlb = "SELECT * FROM report_profile WHERE hh_report_id > '".$last_report_id."' AND report_type LIKE '".$report_type_id."'";
$resultb = run_query ( $sqlb, 'Pull all the newer reports' );
$new_rpt_count = num_rows ( $resultb );
$q = new Questions_into_array ( $report_type_id, '10', 1, 1, 'display' );
for ( $i = 0; $i < $new_rpt_count; $i++ )
{
$p = 0;
$rpt_array = fetch_array ( $resultb, 'Pulling prt prof array', $i );
if ( strlen($rpt_array['succeeded_by']) < 1 )
{
$insert_sql_cols[$p] = 'hh_report_id';
$insert_sql_vals[$p] = $rpt_array['hh_report_id']; $p++;
$insert_sql_cols[$p] = 'report_date_begin';
$insert_sql_vals[$p] = $rpt_array['report_date_begin']; $p++;
$insert_sql_cols[$p] = 'report_date_end';
$insert_sql_vals[$p] = $rpt_array['report_date_end']; $p++;
$insert_sql_cols[$p] = 'report_type';
$insert_sql_vals[$p] = $rpt_array['report_type']; $p++;
$insert_sql_cols[$p] = 'report_org_id';
$insert_sql_vals[$p] = $rpt_array['report_org_id']; $p++;
$insert_sql_cols[$p] = 'report_user_id';
$insert_sql_vals[$p] = $rpt_array['report_user_id']; $p++;
$insert_sql_cols[$p] = 'report_timestamp';
$insert_sql_vals[$p] = $rpt_array['report_timestamp']; $p++;
$insert_sql_cols[$p] = 'report_sharing_permission';
$insert_sql_vals[$p] = $rpt_array['report_sharing_permission']; $p++;
$insert_sql_cols[$p] = 'consol_timestamp';
$insert_sql_vals[$p] = time(); $p++;
////Pieces to improve query speed
//Lead ID
$insert_sql_cols[$p] = 'lead_client_id';
$sqll = "SELECT client_id FROM report_relationship WHERE hh_report_id = '".$rpt_array['hh_report_id']."' AND
client_relationship_to_lead LIKE 'head of household'";
$resl = run_query ( $sqll, 'Puling lead id' );
$id = fetch_result ( $resl, 'Fetch lead id' );
$insert_sql_vals[$p] = $id; $p++;
//Total clients
$insert_sql_cols[$p] = 'total_clients';
$sqll = "SELECT client_id FROM report_relationship WHERE hh_report_id = '".$rpt_array['hh_report_id']."'";
$resl = run_query ( $sqll, 'Pulling num clients' );
$client_cnt = num_rows ( $resl, 'Pulling num clients' );
$insert_sql_vals[$p] = $client_cnt; $p++;
//Calculate number of children
//echo "date is ".$rpt_array['report_date_begin']."<p>";
$begin = date_encrypted_translate_index_to_unix_time ( $rpt_array['report_date_begin'] );
$year_of_report_start = safe_date( 'Y', $begin );
//$year_of_report_start = safe_date( 'Y', $rpt_array['report_date_beginc'] );
$year_eighteen_years_prior_to_rpt_start = $year_of_report_start - 18;
$sqld = "
SELECT DISTINCT(clients.client_id)
FROM
clients
INNER JOIN
client_rpt_profile AS client_rpt_profile1 ON (clients.client_id = client_rpt_profile1.client_id)
INNER JOIN
client_rpt_answers AS client_rpt_answers1 ON (client_rpt_profile1.client_rpt_id = client_rpt_answers1.client_rpt_id)
INNER JOIN
report_relationship AS report_relationship1 ON (clients.client_id = report_relationship1.client_id)
INNER JOIN
report_profile AS report_profile1 ON (report_relationship1.hh_report_id = report_profile1.hh_report_id)
WHERE
report_profile1.hh_report_id = '".$rpt_array['hh_report_id']."'
AND
client_rpt_profile1.report_type LIKE 'client_profile'
AND
client_rpt_profile1.succeeded_by IS NULL
AND
client_rpt_answers1.client_rpt_question_id = '100'
AND
client_rpt_answers1.client_rpt_answer_int < '".$year_eighteen_years_prior_to_rpt_start."'
";
$res = run_query ( $sqld, 'Is an adult check' );
//if ($res
$matches = num_rows ( $res );
if ( $matches > 0 ) {
$adult_count = $matches;
$minor_count = $client_cnt - $matches;
}
else
{
$adult_count = '0';
$minor_count = $client_cnt;
}
$insert_sql_cols[$p] = 'total_adults';
$insert_sql_vals[$p] = $adult_count; $p++;
$insert_sql_cols[$p] = 'total_minors';
$insert_sql_vals[$p] = $minor_count; $p++;
//Calcuate length of service provided
$insert_sql_cols[$p] = 'seconds_of_service';
$service_in_seconds = $rpt_array['report_date_end'] - $rpt_array['report_date_begin'];
$insert_sql_vals[$p] = $service_in_seconds; $p++;
////Pull out the answers
$q = new Questions_into_array ( $report_type_id, '10', 1, 1, 'display' );
$hh = new Reports;
$hh_rpt_answers = $hh->pull_existing_report_answers ( $rpt_array['hh_report_id'], $q->questions, $q->question_elements );
foreach ( $hh_rpt_answers AS $q_id => $cur_a )
{
switch ( $q->questions[$q_id]['question_type'] )
{
case 'radio':
$insert_sql_cols[$p] = 'q_'.$q_id;
$insert_sql_vals[$p] = $q->question_elements[$q_id][$cur_a]['question_element_value']; $p++;
break;
case 'number':
$insert_sql_cols[$p] = 'q_'.$q_id;
$insert_sql_vals[$p] = $cur_a; $p++;
break;
case 'checkboxes':
//Loop through all the possible answers...
foreach ( $q->question_elements[$q_id] AS $cur_q_element )
{
//...and add to sql if an answer matches
if ( $cur_a[$cur_q_element['question_element_id']] )
{
$insert_sql_cols[$p] = 'q_'.$q_id.'_'.$cur_q_element['question_element_id'];
//echo $insert_sql_cols[$p]."<p>";
$insert_sql_vals[$p] = 1;
$p++;
//break;
}
}
break;
case 'dropdown':
$insert_sql_cols[$p] = 'q_'.$q_id;
$insert_sql_vals[$p] = $q->question_elements[$q_id][$cur_a]['question_element_value']; $p++;
break;
default:
break;
}
}
//var_dump ( $insert_sql_cols);
$col_and_vals_text = place_array_into_cols_and_vals ( $insert_sql_cols, $insert_sql_vals );
$final_insert_sql = 'INSERT INTO '.$table_name.$col_and_vals_text;
//echo $final_insert_sql."<p>";
run_query ( $final_insert_sql, 'Inserting updates to consolidated report' );
$insert_sql_cols = '';
$insert_sql_vals = '';
$p = 0;
}
}
//DELETE edited or deleted reports
$sql = "SELECT hh_report_id FROM report_profile WHERE report_type = '".$report_type_id."' AND succeeded_by IS NOT NULL";
$result = run_query ( $sql, 'Finding deleted reportsss' );
$count = num_rows ( $result );
$tag_values["{FORM_END}"] .= 'row count of delete is '.$count.'<br/>';
for ( $i = 0; $i < $count; $i++ )
{
$array = fetch_array ( $result, 'Pulling deleted', $i );
$sqldel = "DELETE FROM ".$table_name." WHERE hh_report_id = '".$array['hh_report_id']."'";
$tag_values["{FORM_END}"] .= $sqldel.'<br/>';
run_query ( $sqldel, 'Erasing reports' );
}
}
function place_array_into_cols_and_vals ( $cols, $vals ) {
foreach ( $cols AS $key => $cur_col )
{
$ret_col .= $cur_col.', ';
if ( strlen ( $vals[$key] ) < 1 )
{
$ret_val .= 'NULL, ';
}
else
{
$ret_val .= "'".$vals[$key]."', ";
}
}
$ret_col = substr ( $ret_col, 0, -2 );
$ret_val = substr ( $ret_val, 0, -2 );
$final_ret = ' ( '.$ret_col.' ) VALUES ( '.$ret_val.' )';
return $final_ret;
}
function intake_exit_events_table( $report_type_id )
{
GLOBAL $db_file, $tag_values, $db_link;
$sqlt = 'SELECT * FROM '.$report_type_id;
if ( $db_file == 'db_connection_mssql' )
{
$test = @mssql_query ($sqlt);
$auto_increment = "INT IDENTITY(1,1) NOT NULL";
}
else
{
//echo "POSTRES<p>";
$test = @pg_exec ($db_link, $sqlt);
$auto_increment = "SERIAL";
}
//Table does not exist, so create it
//echo "Test is ".$test."<p>";
if ( !$test ) {
$tag_values["{FORM_END}"] .= 'Creating table cnsl_'.$report_type_id.'<p/>';
//build_consolidated_report_table_household ( $report_type_id );
$sql = "
CREATE TABLE intake_exit_events (
event_row_id ".$auto_increment.",
lead_client_id INT,
report_org_id INT,
intake_report_id INT,
intake_date INT,
total_clients INT,
total_adults INT,
total_minors INT,
exit_report_id INT NULL,
exit_date INT NULL,
event_length INT NULL,
consol_timestamp INT
)
";
//echo $sql;
run_query ( $sql, 'Create intake_exit_events table' );
$sql = "CREATE INDEX idx_ink_ex_ev__rowid ON intake_exit_events(event_row_id)";
run_query ($sql, "report type profile index");
$sql = "CREATE INDEX idx_ink_ex_ev__l_client_id ON intake_exit_events(lead_client_id)";
run_query ($sql, "report type profile index");
$sql = "CREATE INDEX idx_ink_ex_ev__org_id ON intake_exit_events(report_org_id)";
run_query ($sql, "report type profile index");
$sql = "CREATE INDEX idx_ink_ex_ev__in_date ON intake_exit_events(intake_date)";
run_query ($sql, "report type profile index");
$sql = "CREATE INDEX idx_ink_ex_ev__ex_date ON intake_exit_events(exit_date)";
run_query ($sql, "report type profile index");
}
$tag_values["{FORM_END}"] .= 'Updating household report table: cnsl_'.$report_type_id.'<p/>';
update_intake_exit_events_table ( );
}
function update_intake_exit_events_table ( )
{
GLOBAL $unix_date, $tag_values;
//Find the id of the last consolidated intake report we added
$sql = "
SELECT intake_report_id
FROM intake_exit_events
ORDER BY intake_report_id DESC
";
$res = run_query ( $sql, 'Finding report id of last update' );
if ( num_rows ( $res ) > 0 ) {
$last_updated_intake_id = fetch_result ( $res, 'ID of last intake' );
}
else {
$last_updated_intake_id = 0;
}
//Add new rows for each of the new intakes
$sqlb = "
SELECT *
FROM cnsl_household_intake
WHERE hh_report_id > ".$last_updated_intake_id."
";
$res = run_query ( $sqlb, 'Pulling intake reports to be inserted' );
$row_count = num_rows ( $res );
for ( $i = 0; $i < $row_count; $i++ )
{
$cur = fetch_array ( $res, 'Pulling individual new intake reports array', $i );
$sqlc = "
INSERT INTO intake_exit_events (
lead_client_id,
report_org_id,
intake_report_id,
intake_date,
total_clients,
total_adults,
total_minors,
consol_timestamp
) VALUES (
".$cur['lead_client_id'].",
".$cur['report_org_id'].",
".$cur['hh_report_id'].",
".$cur['report_date_begin'].",
".$cur['total_clients'].",
".$cur['total_adults'].",
".$cur['total_minors'].",
".$unix_date."
)
";
run_query ( $sqlc, 'Inserting new intakes' );
}
//From the intake_exit_events table, pull out the ids of the intakes with no exits
$sqld = "
SELECT event_row_id, lead_client_id, report_org_id, intake_date
FROM intake_exit_events
WHERE exit_report_id IS NULL
";
$resa = run_query ( $sqld, 'Pulling intakes with no exits' );
$row_count = num_rows ( $resa );
//Look for exits for the unclosed intakes;
for ( $i = 0; $i < $row_count; $i++ ) {
$cur_intake = fetch_array ( $resa, 'Look for exits for the unclosed intakes', $i );
$sqlf = "
SELECT *
FROM cnsl_household_exit_followup
WHERE
lead_client_id = ".$cur_intake['lead_client_id']."
AND report_org_id = ".$cur_intake['report_org_id']."
AND report_date_begin >= ".$cur_intake['intake_date']."
ORDER BY report_date_begin
";
$resb = run_query ( $sqlf, 'Pulling matching exits' );
//When matching exits are found, transfer over the exit info, and calculate service event length
if ( num_rows ( $resb ) > 0 ) {
$cur_exit = fetch_array ( $resb, 'Fetch exits for the unclosed intakes', 0 );
//echo "HELLLLLLLLLLLLLLLLLLLLLLLLL<p>";
//var_dump ( $cur_exit );
//echo "HELLLLLLLLLLLLLuuuuuuuuuuuuuuuuuuuLLLLLL<p>";
//echo "Intake date is: ".$cur_intake['intake_date']."<p>";
//echo "exit date is: ".$cur_exit['report_date_begin']."<p>";
//Calculate event length
$intake_date = date_encrypted_translate_index_to_unix_time ( $cur_intake['intake_date'] );
$exit_date = date_encrypted_translate_index_to_unix_time ( $cur_exit['report_date_begin'] );
$days_service = ( $exit_date - $intake_date ) / 86400;
$sqlg = "
UPDATE intake_exit_events
SET
exit_report_id = ".$cur_exit['hh_report_id'].",
exit_date = ".$cur_exit['report_date_begin'].",
event_length = ".$days_service."
WHERE
event_row_id = ".$cur_intake['event_row_id'];
$res = run_query ( $sqlg, 'Inserting updated exits' );
}
}
//DELETE edited or deleted reports
$sql = "SELECT hh_report_id FROM report_profile WHERE report_type = 'household_intake' AND succeeded_by IS NOT NULL";
$result = run_query ( $sql, 'Finding deleted reports' );
$count = num_rows ( $result );
$tag_values["{FORM_END}"] .= 'row count of delete is '.$count.'<br/>';
for ( $i = 0; $i < $count; $i++ ) {
$array = fetch_array ( $result, 'Pulling deleted', $i );
$sqldel = "DELETE FROM cnsl_household_exit_followup WHERE hh_report_id = '".$array['hh_report_id']."'";
$tag_values["{FORM_END}"] .= $sqldel.'<br/>';
run_query ( $sqldel, 'Erasing reports' );
}
}
?>