Location: PHPKode > projects > Homeless Mangement Information System > hmis/update_intake_exit_events_table.php
<?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


$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');


$cur_rpt_type = 'intake_exit_events';

//foreach ( $_GET['report_type'] AS $cur_rpt_type )   {

    if ( ereg ('[^A-Za-z0-9_ ]', $cur_rpt_type ) ) {
        echo 'Illegal report type';
        exit;
    }
    else {

        $report_type = $cur_rpt_type;
    }
/*
    $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 ) {
        echo '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' )  {

            echo '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 )   {
                echo 'Creating table cnsl_'.$report_type.'<p/>';
                build_consolidated_report_table_client ( $report_type );    

            }
            echo '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'  )  {

//            echo 'A household type report<p>';
            $sqlt = 'SELECT * FROM '.$report_type;


            if ( $db_file == 'db_connection_mssql' )    {
                $test = @mssql_query ($sqlt);
                $auto_increment = "INT IDENTITY(1,1) NOT NULL";

            }
            else {
                $test = @pg_exec ($db_link, $sqlt);
                $auto_increment = "SERIAL";
            }
            
            

            //Table does not exist, so create it
            if ( !$test )   {
                echo 'Creating table cnsl_'.$report_type.'<p/>';
                //build_consolidated_report_table_household ( $report_type );
                $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                
                
                )
                                
                ";
                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");    

            }
            echo 'Updating household report table: cnsl_'.$report_type.'<p/>';
            update_intake_exit_events_table (  );    

//        }
/*
    }
}



function build_consolidated_report_table_client ( $report_type_id ) {

  $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.' ( 
    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:
                echo "<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 ) {

    $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 .= ', '; 
        //}    
        echo "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 )  {
            echo "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 .= ' )';
    echo $make_table_sql."<p>";
    
    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.')';
        echo $sqli."<p>";
        run_query ( $sqli, 'Createing consolidated report index' );
    } 


}




function update_consolidated_report_client ( $report_type_id ) {
    

    $table_name = 'cnsl_'.$report_type_id;
    
    $sql = 'SELECT client_rpt_id FROM '.$table_name.' ORDER BY client_rpt_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 client_rpt_profile WHERE client_rpt_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' );
    
    
    $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>";
                //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;
                                
                            }
                            
                        }
                        
                        
                        break;             

                    default:

                }


            }
            
            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;               
            $final_insert_sql = 'INSERT INTO '.$table_name.' ( '.$insert_sql_cols.' ) VALUES ( '.$insert_sql_vals.' )';
            //echo $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_intake_exit_events_table (  ) {
    GLOBAL $unix_date;    
    
    //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 );
    echo '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']."'";
        echo $sqldel.'<br/>';
        run_query ( $sqldel, 'Erasing reports' );   
    
    
    }
    
    
        
    
}    
    
    
    
    
    
    
    
    
    
    
    
    
    
  /*  
    
    
    
    
    
    $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 );
    echo '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']."'";
        echo $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;

}









*/
 
                                    
?>
Return current item: Homeless Mangement Information System