Location: PHPKode > projects > Homeless Mangement Information System > hmis/SETUP.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
set_time_limit(3000);
$page_id = "genesis";
include("initialize_pointer.php");

include($include_root."initialize.inc");
include($include_root."db_connection.inc");
include($include_root.'encryption_class.inc');
include($include_root.'passwords_class.inc');

$db_link = db_generic_connect ();
$sql = "SELECT * FROM gate ";

if ( $db_file == 'db_connection_mssql' )
{
    $test = @mssql_query ($sql);
}
else
{
    $test = @pg_exec ($db_link, $sql);
}
//echo "Test equals".$test."<p/>";
//There is a initial setup of the table gate....
if ($test)
{
    //echo "Initial setup complete<p>";
    
    //If there is more than the superuser, dump them
    ///...are there any users in the gate table?......
    if (num_rows($test) > 0 )
    {
    	//run_query("SELECT setval ('clients_clientrowid_seq', 6)", "Error setting sequence for clients");
        //...yes, taht gate table contains users, so allow no changes
        echo "<p>No access allowed";
        exit;
    }
    //...no, the gate table contains no users, so give them setup options
    else
    {
        //echo "Initial setup not complete<p>";
        
        system_admin_setup();
    
    }
    //$empty_test = run_query ("SELECT * FROM gate", "group login setup");
    //$numrows = num_rows($empty_test);
	//if($numrows > 1){echo "Setup Complete, <a href=\"index.php\">Click Here to Login</a>"; exit;}
    
    
    
}
else
{
    //echo "Tables did not exist, installed base setup.<p>";
    
    echo "<html><head><script language=\"javascript\">function redirectb () { window.location.href =
    \"".$system_directory."SETUP.php?setup_type=insert_questions\";}</script></head><body>";
    $HTTP_GET_VARS["setup_type"] = "setup_base_tables";
    system_admin_setup();
    echo ".";
    echo "<script language=\"javascript\">redirectb()</script>";
    
    echo "</body></html>";
}


exit;


function setup_system ()
{
	GLOBAL $include_root, $unique_seq, $db_file, $group_term, $organization_term;
    
    if ( $db_file == 'db_connection_mssql' )
    {
        $auto_increment = "INT IDENTITY(1,1) NOT NULL";
        //$db_link = @mssql_connect($db_server_address, $db_login, $db_password) or die("Couldn't connect to SQL Server on $myServer");
        //$db_exist_check = @mssql_select_db ( $db_name );
        //if  ( !$db_exist_check )    {
        //    $sql = "CREATE DATABASE ".$db_name;
            //run_query ($sql, "db creation");
        //}
    }
    else
    {
        $auto_increment = "SERIAL";
    }
   
	/////////System User Tables
    
    //gate -- "gate_status" is either "active" or "inactive"  Inactive cannot access system 
    
    //transaction_begin ( 'Begin tables creation' );
	
    //groups -- "group_contract_num" stores the contract number used by the administering organization.  This is not used by the system in any way.
	$sql = 
    "CREATE TABLE groups (
        group_id ".$auto_increment.", 
        group_name VARCHAR(75), 
        group_contract_num VARCHAR(25) NULL, 
        county_served VARCHAR(25) NULL,
        PRIMARY KEY ( group_id )
    )";
	run_query ($sql, "groups table creation");
    
    //organization -- "org_type" and "org_system_type" are basically unused at this point, and may be removed later.  The idea is that you could present different options for different types of organizations (i.e., a transition shelter would get a differnt form from a domestic violence shelter"
	$sql = 
    "CREATE TABLE organizations (
        org_id ".$auto_increment.", 
        org_name VARCHAR(75), 
        group_id INT, 
        org_type VARCHAR(10), 
        org_system_type VARCHAR(15) NULL, 
        web_page_address VARCHAR(125) NULL, 
        org_address varchar (75) NULL ,
        org_city varchar (50) NULL ,
        org_state varchar (2) NULL ,
        org_zip int NULL ,
        mailing_address varchar (75) NULL ,
        mailing_city varchar (50) NULL ,
        mailing_state varchar (2) NULL ,
        mailing_zip int NULL ,
        org_description varchar (160) NULL ,
        org_user_id varchar (10) NULL ,
        account_status VARCHAR(10),
        PRIMARY KEY ( org_id ),
        FOREIGN KEY ( group_id ) REFERENCES groups
        
        )";
	run_query ($sql, "organizations table creation");
    $sql = "CREATE INDEX idx_orgs__org_id ON organizations(org_id)";
    run_query ($sql, "organizations id index");
	
	$sql = "
    CREATE TABLE gate (
        user_id ".$auto_increment.", 
        gate_login VARCHAR(100), 
        gate_password VARCHAR(100), 
        gate_status VARCHAR(10), 
        gate_cookie_value VARCHAR(40) NULL,
        PRIMARY KEY ( user_id ) 
     )";
		
	run_query ($sql, "gate creation");
    
    $sql = "CREATE INDEX idx_gate__login ON gate(gate_login)";
    run_query ($sql, "gate login index");
    
    //user_info -- Stores the data specific to the system user.  "user_access_level" determines what kind of access rights the user has.  A "10" is a system administrator.  A "20" is a group (i.e., a county) administrator.  A "30" is an organization administrator (i.e., "XYZ Homeless Shelter." A "40" is a system user that works for an organization.  Only system users can actually enter client information.
	$sql = 
    "CREATE TABLE user_info (
         user_id INT, 
         user_access_level INT, 
         org_id INT, 
         user_name_first VARCHAR(25), 
         user_name_last VARCHAR(25), 
         user_phone VARCHAR(25) NULL, 
         user_email VARCHAR(60) NULL,
         FOREIGN KEY ( user_id ) REFERENCES gate,
		 FOREIGN KEY ( org_id ) REFERENCES organizations
         
     )";
	run_query ($sql, "user_info creation");
    
    
	//ban -- If "ban_type" is set by the system to "forever," then user will not be able to log in without administrator intervention. "ban_ip" is the ip address of the user that was banned.  Users that are not banned "forever" are allowed back in the system after 1 hour. The ban function is in "authenticate.inc."
	$sql = "CREATE TABLE ban (ban_rowid ".$auto_increment.", ban_ip VARCHAR(25), ban_user_id VARCHAR(8), ban_this_page VARCHAR(40) NULL, ban_bad_login VARCHAR(25) NULL, ban_message VARCHAR(35) NULL, ban_type VARCHAR(10) NULL, ban_date_stamp VARCHAR(12), ban_unix_date INT)";
	run_query ($sql, "login");
	
	//logged_in_log -- Info is recored each time a user sucessfully logs into the system.  Some of it is redundent (i.e., "log_group_id" and "log_group_name" to make it human readable without having to join it with another table.
	//Create successful login ip list
	$sql = "
    CREATE TABLE logged_in_log (
        log_rowid ".$auto_increment.", 
        log_user_id INT, 
        log_user_name VARCHAR(51), 
        log_org_id INT,
        log_org_name VARCHAR(75) NULL, 
        log_group_id INT, 
        log_group_name VARCHAR(75) NULL, 
        log_ip VARCHAR(25), 
        log_date_stamp VARCHAR(12), 
        log_unix_date INT,
        log_session_id INT NULL
    )";
	run_query ($sql, "login");
    $sql = "CREATE INDEX idx_log_in_log__rowid ON logged_in_log(log_rowid)";
    run_query ($sql, "report type profile index");
    $sql = "CREATE INDEX idx_log_in_log__usrid ON logged_in_log(log_user_id)";
    run_query ($sql, "report type profile index");
    $sql = "CREATE INDEX idx_log_in_log__date ON logged_in_log(log_unix_date)";
    run_query ($sql, "report type profile index");
    
    ////log_id_change -- Logs when a user changes their id to a user below them in the system 
    $sql = "
    CREATE TABLE log_id_change (
        log_real_user_id INT,
        log_current_user_id INT,
        log_assumed_user_id INT,
        log_session_id INT,
        log_date_stamp INT
    
    )";
    run_query ( $sql, 'logged_in_log' );
    
    
    ////log_report_activity -- Logs when a user creates, views and edits a report
    //"report_type" is either "identifier", "client" or "household"
    //"report_action" can be "create", "edit", "read", "client_profile", "client_search" 
    $sql = "
    CREATE TABLE log_report_activity (
        user_id INT,
        report_type VARCHAR(10),
        report_id INT,
        report_action VARCHAR(30),
        log_session_id VARCHAR(40),
        log_date_stamp INT    
    )";
    run_query ( $sql, 'log_report_activity' );
    
    
	//failed_logins -- Used to record the failed attempts at loggin in.  If there are too many failed logins, the system shuts down for an hour "turtles" as dictated in the "ban_check" function in "authenticate.inc."
	$sql = "CREATE TABLE failed_logins (failed_rowid ".$auto_increment.", failed_ip VARCHAR(25) NULL, failed_login VARCHAR(25) NULL, failed_message VARCHAR(30) NULL, failed_date_stamp VARCHAR(12), failed_unix_date INT)";
	run_query ($sql, "login");
    
    ////report_type_profile -- Defines how a report type (aka "report_type_id") is used
    //"report_type_id" is the name of the report type (i.e., "client_service")
    //"report_on_unit" is what type of entity we are reporting on, choices include "client" | "household" | "client_identifier"
    //"report_on_timeperiod defiens if this is a static one-time type report (such as "ethnicity") or something with a duration (such as "shelter_provided".  Options include "yes" | "no".  A "yes" adds in the generic timeperiod question to the report
    //"report_that_follows" is the id of the report that should appear after the report is filled in, "none" means it loops back to the current report, "none_blank" takes it back to the current report and erases all the answers (blank form). 
    $sql = "
    CREATE TABLE report_type_profile (
        rpt_type_rowid ".$auto_increment.", 
        report_type_id VARCHAR(40) NOT NULL, 
        report_title VARCHAR(255), 
        report_description VARCHAR(255) NULL, 
        report_instructions VARCHAR(1000) NULL, 
        report_on_unit VARCHAR(30) NULL, 
        report_on_timeperiod VARCHAR(15) NULL, 
        report_that_follows VARCHAR(30) NULL, 
        report_edit_access_levels VARCHAR(20),
        PRIMARY KEY ( report_type_id )
    )";
    run_query ($sql, "report_type_profile");
    $sql = "CREATE INDEX idx_rpt_ty_pro__r_t_id ON report_type_profile(report_type_id)";
    run_query ($sql, "report type profile index");
    
    
	////questions -- The most important table in the system. These variables defines the characteristics of the questions the system asks users. 
    //"question_id" can be any number, but must be unique. 
    //"question_title" is the title used to describe the question. 
    //"question_type" corresponds the the HTML "input" types. The options are: "text", "number", "radio", "checkboxes", "dropdown", "table", "textarea" and "date"
    //"question_owner_group_id" and "question_owner_org_id" record which "group_id" and "org_id" own the question.  This determines who this question is displayed to in the "questions_into_array_class.inc."
    //"question_field_size" determines the size of "text" and "number" HTML form fields HTML "size" field. "question_display_size" is an option argument the defines the HTML "maxlenght" field.  Both of these fiels are used to detemine the size of "textarea" fields.  See "questions_display.inc" for details.
    //"questions_required" defines whether or not an answer is required from the user for this questions.  See "questions_answers_validation_class.inc" for details.
    //"report_type_id" defines what set the question belongs to.  Some pages only have one set of questions.  Others have multiple.  Pages are pullled as a set via this variable in the questions_into_array_class.inc."
    //"question_default_value" contains the default value for "text" and "number" questions.  For questions with "elements" (see below), this field contains the "question_element_id" of the default value.
    //"question_visible" defines if the question is active or inactive.  By making a question incative (as opposed to erasing it from the DB" the question no longer appears on pages, but DB relational integrity is maintained.
    //"question_ecma" defines what ecma script (aka Javascript) should be added to the question (i.e., "onclick").  See "questions_display.inc" for details. 
	//"question_display_size" defines the size of the display, as opposed to field size which limits the number of characters that can
    //    be entered.  If no value, display size defaults to same as field size.   
    //"question_encrypted" if "1" the question is encrypted when stored in the database
    //"question_no_answer_options" Add the following together to determine value: "Don't know" = 1, "Not applicable" = 2, "Refused" = 4
	//"question_special_attribute" is a text used to define special attributes of a question (i.e., date questions).  For date: "F"
    //  produces Month; "j" produces day of month; "Y" produces year.  Anything with a # makes a special function call '#services_list'
    //  creates list of services associated with an organization 
    $sql = 
    "CREATE TABLE questions ( 
        question_id INT, 
        question_title VARCHAR(1000),
        question_type VARCHAR(15), 
        question_owner_group_id INT, 
        question_owner_org_id INT, 
        question_field_size INT NULL, 
        question_range_bottom INT NULL, 
        question_range_top INT NULL, 
        question_display_order INT NULL, 
        question_required VARCHAR(8) NULL, 
        question_default_value VARCHAR(35) NULL, 
        question_visible VARCHAR(5), 
        question_ecma VARCHAR(50) NULL, 
        question_display_size INT NULL,
        question_encrypted INT NULL,
        question_no_answer_options INT NULL,
        question_special_attribute VARCHAR(50) NULL,
        PRIMARY KEY ( question_id )
        
    )";
    //report_type_id VARCHAR(40),
    //FOREIGN KEY ( report_type_id ) REFERENCES report_type_profile
    
    //question_row_id ".$auto_increment.",
        
	run_query ($sql, "questions table creation");
    $sql = "CREATE INDEX idx_questions__q_id ON questions(question_id)";
    run_query ($sql, "questions id index");
    
	////questions_previous_version -- When questions are edited, the old definition is saved here
	$sql = "
    CREATE TABLE questions_previous_versions (
    question_id INT,
    question_title VARCHAR(1000), 
    question_type VARCHAR(15), 
    question_owner_group_id INT, 
    question_owner_org_id INT, 
    question_field_size INT NULL, 
    question_range_bottom INT NULL, 
    question_range_top INT NULL, 
    question_display_order INT NULL, 
    question_required VARCHAR(8) NULL,     
    question_default_value VARCHAR(35) NULL, 
    question_visible VARCHAR(5) NULL, 
    question_ecma VARCHAR(50) NULL, 
    question_display_size INT NULL,
    question_encrypted INT NULL,
    question_no_answer_options INT NULL,
    question_special_attribute VARCHAR(50) NULL, 
    question_edit_date_stamp VARCHAR(12), 
    question_edit_unix_date INT)";
	run_query ($sql, "questions prev version table creation");
    //report_type_id VARCHAR(40),
    
    
////question_report_ids -- Defines the "reports" a particular question is associated with
    $sql = "
    CREATE TABLE question_report_type_ids (
        question_id INT,
        report_type_id VARCHAR(40),
        FOREIGN KEY ( question_id ) REFERENCES questions,
        FOREIGN KEY ( report_type_id ) REFERENCES report_type_profile   
    )";
    run_query ($sql, "question report id creation");
    $sql = "CREATE INDEX idx_qu_rpt_ids__q_id ON question_report_type_ids(question_id)";
    run_query ($sql, "questions id index for q rpt ids");
    $sql = "CREATE INDEX idx_qu_rpt_ids__rpt_typ_id ON question_report_type_ids(report_type_id)";
    run_query ($sql, "questions id index for q rpt ids");
    
    
////question_report_ids -- Defines the "reports" a particular question is associated with
    $sql = "
    CREATE TABLE question_report_type_id_previous_versions (
        question_id INT,
        report_type_id VARCHAR(40)
        
    )";
     run_query ($sql, "question report id creation prev v.");
    
////question_elements -- Defines the elements of questions above such as "radio" or "checkboxes".  Example question:"Favorite Color"; quesiton_elements: "red", "green", "blue".
//"question_element_id" can be any number but must be unique.
//"question_id" is the id of the question above this is an element of.
//"question_element_title" is the text displayed next to the button/checkbox (i.e., "Red").
//"question_element_value" is the actual value that ends up being stored in the database (i.e., "red").  By making this different from the "title", you can have long titles (and even formatting) but still only store concise answers in the DB.
//"question_element_checked" is only used by "checkboxes" questions to detemine if the box is checked by default. "Yes" or blank
//"question_element_attribute" is unused, and will probably be deleted later.
	$sql = 
    "CREATE TABLE question_elements (
        question_element_rowid ".$auto_increment.", 
        question_element_id INT, 
        question_id INT,
        question_element_title VARCHAR(60),
        question_element_value VARCHAR(70) NULL,
        question_element_checked VARCHAR(10) NULL,
        question_element_attribute VARCHAR(10) NULL,
        FOREIGN KEY ( question_id ) REFERENCES questions 
     )";
	run_query ($sql, "questions elements table creation");
    $sql = "CREATE INDEX idx_qu_eles__qu_ele_id ON question_elements(question_element_id)";
    run_query ($sql, "question element id index q ele ids");
    
    
    
////question_elements_previous_version -- When questions are edited, the old element definitions are stored here.    
$sql = "CREATE TABLE question_elements_previous_version (quest_ele_prev_row_id ".$auto_increment.", question_element_id INT, question_id
INT, question_element_title VARCHAR(60), question_element_value VARCHAR(70) NULL, question_element_checked VARCHAR(10) NULL,
question_element_attribute VARCHAR(10) NULL, question_edit_date_stamp VARCHAR(15) NOT NULL, question_edit_unix_date INT )";
	run_query ($sql, "questions elements previous version table creation");    
    
    
////table_profiles -- Defines the macro-level contruction "table" type questions. MAYBE ROLL THIS INTO THE questions table??
//"table_type" can be numbers|checkboxes|text
    $sql = 
    "CREATE TABLE table_profiles (
        table_profile_row_id ".$auto_increment.", 
        question_id INT, 
        x_axis_title VARCHAR(200) NULL, 
        y_axis_title VARCHAR(200) NULL, 
        table_type VARCHAR(10) NULL,
        FOREIGN KEY ( question_id ) REFERENCES questions 
    )";
    run_query ($sql, "table profile table creation");
    
////table_profiles_previous_version 
    $sql = "CREATE TABLE table_profiles_previous_version (table_prof_prev_row_id ".$auto_increment.", question_id INT, x_axis_title VARCHAR(400) NULL, y_axis_title VARCHAR(400) NULL, table_type VARCHAR(10) NULL, question_edit_unix_date INT)";
    run_query ($sql, "table profile previous table creation");
    
//table_elements -- Defines the construction of individual x and y elements
//"question_id" is the same as the question-id used in the "questions" and "table_profile" tables
//"axis_type" is "x" or "y"
//"axis_label" is the text put next to the axis, or if the axis is defined as "text_axis" under "axis_special_type", it is the text that spans the whole row.
//"axis_special_type" is default|total|subtotal|text_axis
//"axis_required is "all" if every cell must have an answer and "one" if only one answer is needed, or "none"
//"axis_visible" [yes|no] allows the hiding of the axis without the loss of the underlying definition 
    $sql = 
    "CREATE TABLE table_elements (
        table_elements_row_id ".$auto_increment.", 
        axis_id INT, 
        question_id INT, 
        axis_type VARCHAR(5), 
        axis_label VARCHAR(150) NULL, 
        axis_special_type VARCHAR(10) NULL, 
        axis_display_order INT NULL, 
        axis_owner_group_id INT, 
        axis_owner_org_id INT, 
        axis_required VARCHAR(5) NULL, 
        axis_range_bottom INT NULL, 
        axis_range_top INT NULL, 
        axis_visible VARCHAR(8) NULL,
        PRIMARY KEY ( axis_id ),
        FOREIGN KEY ( question_id ) REFERENCES questions 
    )";  
    run_query ($sql, "table elements table creation");
    
//table_elements_previous_version    
    $sql = 
    "CREATE TABLE table_elements_previous_version (
        table_ele_prev_row_id ".$auto_increment.", 
        axis_id INT, 
        question_id INT, 
        axis_type VARCHAR(5), 
        axis_label VARCHAR(150) NULL, 
        axis_special_type VARCHAR(10) NULL, 
        axis_display_order INT NULL, 
        axis_owner_group_id INT, axis_owner_org_id INT, 
        axis_required VARCHAR(5) NULL, 
        axis_range_bottom INT NULL, 
        axis_range_top INT NULL, 
        axis_visible VARCHAR(8) NULL, 
        question_edit_date_stamp VARCHAR(12), 
        question_edit_unix_date INT 
    )";  
    run_query ($sql, "table elements table creation");
    
//A table that defines which optional questions (defined by having a "question_owner" of "1000000") are active for a particular group or organization.  See "questions_into_array_class.inc" for details.
	$sql =	
    "CREATE TABLE question_optional (
        quest_opt_rowid ".$auto_increment.", 
        question_id INT, 
        group_id INT NULL, 
        org_id INT NULL,
        FOREIGN KEY ( question_id ) REFERENCES questions,
        FOREIGN KEY ( group_id ) REFERENCES groups,
        FOREIGN KEY ( org_id ) REFERENCES organizations
        
    )";
	run_query ($sql, "questions elements table creation");
	
	$sql = "CREATE INDEX idx_qu_opt__qu_id ON question_optional(question_id)";
    run_query ($sql, "report type profile index");
	
	//Because the "questions_into_array" queries fail if it is empty, put a fake sharing permission into question_optional table
	$sql = "INSERT INTO question_optional (question_id, group_id, org_id) VALUES ('1', '1', '1')";
	////////run_query ($sql, "login");
    
    
	$sql = "
	CREATE TABLE table_elements_optional (
    table_axis_opt_rowid ".$auto_increment.", 
    axis_id INT, 
    group_id INT NULL, 
    org_id INT NULL,
    FOREIGN KEY ( axis_id ) REFERENCES table_elements,
    FOREIGN KEY ( group_id ) REFERENCES table_elements,
    FOREIGN KEY ( org_id ) REFERENCES organizations
    )";
	run_query ($sql, "questions elements table creation");
    
	//Because the "questions_into_array" queries fail if it is empty, put a fake sharing permission into question_optional table
	$sql = "INSERT INTO table_elements_optional (axis_id, group_id, org_id) VALUES ('1', '1', '1')";
	//run_query ($sql, "login");
    
//validation -- The table that contains client-side ECMA script inserted into pages.  This script is called by the "display_ecma" function in "page_elements_display.inc." Only the "client.php" page uses this at this time.
	$sql = "CREATE TABLE validation (validation_rowid ".$auto_increment.", validation_page_id VARCHAR(40), validation_ecma TEXT)";
	run_query ($sql, "validation table creation");
    
//validation_php -- Used by the question_set_validation_function.inc to call up php that is applied to the page.  Can be used to test the inter-question validity.  See function for details.
	$sql = "CREATE TABLE validation_php (validation_rowid ".$auto_increment.", validation_id INT, validation_question_set VARCHAR(40), validation_logic TEXT)";
	run_query ($sql, "validation table creation");
    
//validation_actions -- The one or more actions taken if the "validation_logic" above is true. See the question_set_validation_function.inc for details
	$sql = "CREATE TABLE validation_actions (validation_actions_rowid ".$auto_increment.", validation_id INT, action_target_id INT, message_target VARCHAR(100), message_head VARCHAR(100), message_type VARCHAR(25))";
	run_query ($sql, "validation table creation");
    
//clients -- Contains the unique client identifier and cleint_id for each client (i.e., homeless person). See "client_id_class.inc" for details
/*	$sql = 
    "CREATE TABLE clients (
        clientrowid ".$auto_increment.", 
        client_id INT, 
        client_identifier VARCHAR(50), 
        client_entry_timestamp VARCHAR(15),
        PRIMARY KEY (client_id)
    )";
	run_query ($sql, "clients table creation");
    
    $sql = "CREATE INDEX idx_clients__client_id ON clients(client_id)";
    run_query ($sql, "clients index creation");
    $sql = "CREATE INDEX idx_clients__client_ident ON clients(client_identifier)";
    run_query ($sql, "clients index creation");
*/  
    
    $sql = 
    "CREATE TABLE clients (
        clientrowid ".$auto_increment.", 
        client_id INT,
        client_iden VARCHAR(255), 
        date_stamp INT,
        PRIMARY KEY (client_id)
    )";
	run_query ($sql, "clients table creation");
    
    $sql = "CREATE INDEX idx_clients__client_id ON clients(client_id)";
    run_query ($sql, "clients index creation");
    
    
    $sql = 
    "CREATE TABLE client_system_ids (
        client_id INT, 
        client_identifier VARCHAR(255), 
        date_stamp INT,
        FOREIGN KEY ( client_id ) REFERENCES clients
        
    )";
	run_query ($sql, "clients sys ids table creation");    
    $sql = "CREATE INDEX idx_client_sysid__client_id ON client_system_ids(client_id)";
    run_query ($sql, "clients index creation");
    $sql = "CREATE INDEX idx_client_sysid__client_ident ON client_system_ids(client_identifier)";
    run_query ($sql, "clients index creation");
    
//client_rpt_profile -- Contains the details for each client report.  Each client can have serveral reports (defined by "report_type"). Each organization an have its own report on a client. See client_profile_class.inc" for details. 
    $sql = 
    "CREATE TABLE client_rpt_profile (
        clnt_rpt_rowid ".$auto_increment.", 
        client_id INT, 
        client_rpt_id INT,
	    report_type VARCHAR(40) NOT NULL, 
        report_org_id INT, 
        report_user_id INT, 
        report_timestamp VARCHAR(50), 
        report_date_beginc INT,	
        report_date_endc INT, 
        succeeded_by INT NULL, 
        client_rpt_sharing_permission VARCHAR(10) NULL,
        PRIMARY KEY ( client_rpt_id ),
        FOREIGN KEY ( client_id ) REFERENCES clients 
    )";
	run_query ($sql, "client_rpt_profile table creation");
    $sql = "CREATE INDEX idx_clt_rpt_prf__rowid ON client_rpt_profile( clnt_rpt_rowid )";
    run_query ($sql, "client_rpt_profile creation");    
    $sql = "CREATE INDEX idx_clt_rpt_prf__clt_id ON client_rpt_profile( client_id )";
    run_query ($sql, "client_rpt_profile creation");
    $sql = "CREATE INDEX idx_clt_rpt_prf__clt_rpt_id ON client_rpt_profile( client_rpt_id )";
    run_query ($sql, "client_rpt_profile creation");
	$sql = "CREATE INDEX idx_clt_rpt_prf__rpt_org_id ON client_rpt_profile( report_org_id )";
    run_query ($sql, "client_rpt_profile creation");
    $sql = "CREATE INDEX idx_clt_rpt_prf__succd_by ON client_rpt_profile( succeeded_by )";
    run_query ($sql, "client_rpt_profile creation");
    
//client_rpt_answers --  Each client_rpt_profile defined above can have multiple "answers." For example the a report called "service" (defined above) can have multiple enteries in the client_rpt_answers table such as "ethnicity," "gender" etc.
//"client_rpt_id" defines the answers as part of the report defined in "client_rpt_profile" above.
//"client_rpt_question_id" defines which question (contained in the table "questions") that this is related to.
//"client_rpt_answer" is the answer to the question. 
    $sql = 
    "CREATE TABLE client_rpt_answers (
        clnt_rpt_ans_rowid ".$auto_increment.", 
        client_rpt_id INT, 
        client_rpt_question_id INT, 
        axis_x INT NULL, 
        axis_y INT NULL, 
        client_rpt_answer VARCHAR(255) NULL, 
        client_rpt_answer_int INT NULL,
        FOREIGN KEY ( client_rpt_id ) REFERENCES client_rpt_profile 
    )";
    run_query ($sql, "client_rpt_answers table creation");
    
	$sql = "CREATE INDEX idx_clt_rpt_ans__clt_rpt_id ON client_rpt_answers(client_rpt_id)";
    run_query ($sql, "client_rpt_profile creation");  
    
//textarea_answers -- Because text area answers can be huge, put them in a seperate table
//"textarea_answer_id" 
//"textarea_answer" is the actual answer
//"textarea_unique" is the id stored in the *_rpt_answer_int field in "client_rpt_answers" and "report-answers", and is the MD5 of the userid, timestamp, and message
    $sql = "
    CREATE TABLE textarea_answers (
        textarea_answer_id ".$auto_increment.",
        textarea_answer TEXT,
        textarea_unique VARCHAR(40)   
    )";
    run_query ($sql, 'textarea_answers table creation' );
    $sql = 'CREATE INDEX idx_txtare_ans__txt_ans_uniqe ON textarea_answers( textarea_unique )';
    run_query ($sql, "textarea index creation");
    
//org_generated_client_ids -- Allows organization to assign their own client identifiers to a particular client.  The system still uses the "client_identifier" and "client_id" defined in the table "clients," but the org can use this "org_generated_client_id" to look the client up.  Can be a name, a Social Security number, phone number, etc. 
	$sql = 
    "CREATE TABLE org_generated_client_ids (
        org_gen_rowid ".$auto_increment.", 
        client_id INT, 
        id_type VARCHAR(30), 
        org_generated_client_id VARCHAR(100),
        id_char_one VARCHAR(30),
        id_char_two VARCHAR(30),
        id_char_three VARCHAR(30),
        id_char_four VARCHAR(30),
        id_char_five VARCHAR(30),
        id_char_six VARCHAR(30),
        id_char_seven VARCHAR(30),
        id_char_eight VARCHAR(30), 
        org_id INT,
        succeeded_by INT NULL,
        date_stamp VARCHAR(50),
        FOREIGN KEY ( client_id ) REFERENCES clients,
        FOREIGN KEY ( org_id ) REFERENCES organizations
    )";
    run_query ($sql, "validation table creation");
    $sql = '
    CREATE INDEX idx_org_gen_clt_id__id_type ON org_generated_client_ids(id_type)';
    run_query ( $sql, 'org_generated_client_ids index creation' );
    $sql = "CREATE INDEX idx_org_gen_clt_id__org_gen_clt_id ON
    org_generated_client_ids(org_generated_client_id)";
    run_query ($sql, "org_generated_client_ids index creation");
    $sql = '
    CREATE INDEX idx_org_gen_clt_id__id_char_one ON org_generated_client_ids(id_char_one)';
    run_query ( $sql, 'org_generated_client_ids index creation' );
    $sql = '
    CREATE INDEX idx_org_gen_clt_id__id_char_two ON org_generated_client_ids(id_char_two)';
    run_query ( $sql, 'org_generated_client_ids index creation' );
    $sql = '
    CREATE INDEX idx_org_gen_clt_id__id_char_three ON org_generated_client_ids(id_char_three)';
    run_query ( $sql, 'org_generated_client_ids index creation' );
    $sql = '
    CREATE INDEX idx_org_gen_clt_id__id_char_four ON org_generated_client_ids(id_char_four)';
    run_query ( $sql, 'org_generated_client_ids index creation' );
    $sql = '
    CREATE INDEX idx_org_gen_clt_id__id_char_five ON org_generated_client_ids(id_char_five)';
    run_query ( $sql, 'org_generated_client_ids index creation' );
    $sql = '
    CREATE INDEX idx_org_gen_clt_id__id_char_six ON org_generated_client_ids(id_char_six)';
    run_query ( $sql, 'org_generated_client_ids index creation' );
    $sql = '
    CREATE INDEX idx_org_gen_clt_id__id_char_seven ON org_generated_client_ids(id_char_seven)';
    run_query ( $sql, 'org_generated_client_ids index creation' );
    $sql = '
    CREATE INDEX idx_org_gen_clt_id__id_char_eight ON org_generated_client_ids(id_char_eight)';
    run_query ( $sql, 'org_generated_client_ids index creation' );
        
    $sql = "CREATE INDEX idx_org_gen_clt_id__clt_id ON
    org_generated_client_ids(client_id)";
    run_query ($sql, "org_generated_client_ids index creation");
    $sql = "CREATE INDEX idx_org_gen_clt_id__org_id ON
    org_generated_client_ids(org_id)";
    run_query ($sql, "org_generated_client_ids index creation");
    $sql = "CREATE INDEX idx_org_gen_clt_id__suc_by ON
    org_generated_client_ids(succeeded_by)";
    run_query ($sql, "org_generated_client_ids index creation");
    
//client status ids -- associates ids with status
	$sql = 
    "CREATE TABLE org_client_status_ids (
        status_id INT,
		status VARCHAR(50),
	    PRIMARY KEY ( status_id )
    )";
    run_query ($sql, "validation table creation");
    //table is so small doesn't need a second index... $sql = '
    //CREATE INDEX idx_get_id_for_status ON org_client_status(status)';
    //run_query ( $sql, 'idx_get_id_for_status index creation' );
    
//Populate Status (0 is reserved for [SELECT STATUS] in search).  Default is 1, "No Status Set"...
//Todo: This table is no longer used.
run_query("INSERT INTO org_client_status_ids (status_id, status) VALUES ('1', 'No Status Set')", "Error inserting status ids");
run_query("INSERT INTO org_client_status_ids (status_id, status) VALUES ('2', 'Active')", "Error inserting status ids");
run_query("INSERT INTO org_client_status_ids (status_id, status) VALUES ('3', 'Inactive/Dormant')", "Error inserting status ids");
run_query("INSERT INTO org_client_status_ids (status_id, status) VALUES ('4', 'Exited')", "Error inserting status ids");
run_query("INSERT INTO org_client_status_ids (status_id, status) VALUES ('5', 'Followup Required')", "Error inserting status ids");
run_query("INSERT INTO org_client_status_ids (status_id, status) VALUES ('6', 'Followup Required - 3 month')", "Error inserting status ids");
run_query("INSERT INTO org_client_status_ids (status_id, status) VALUES ('7', 'Followup Required - 6 month')", "Error inserting status ids");
run_query("INSERT INTO org_client_status_ids (status_id, status) VALUES ('8', 'Followup Required - 9 month')", "Error inserting status ids");
run_query("INSERT INTO org_client_status_ids (status_id, status) VALUES ('9', 'Followup Required - 12 month')", "Error inserting status ids");
run_query("INSERT INTO org_client_status_ids (status_id, status) VALUES ('10', 'Deceased')", "Error inserting status ids");


//client status -- tracks status of clients.  status ids found in client_status_ids.  Another possible primary key would be using org_gen_rowid but seems more appropriate to use client_id and org_id pair.  This table is only joined to anyways so only foreign keys are important...
	$sql = 
    "CREATE TABLE org_client_status (
        client_id INT,
        status_id INT,
        org_id INT,
        date_stamp VARCHAR(50),
		PRIMARY KEY ( client_id, org_id ),
        FOREIGN KEY ( client_id ) REFERENCES clients,
		FOREIGN KEY ( status_id ) REFERENCES org_client_status_ids,
        FOREIGN KEY ( org_id ) REFERENCES organizations
    )";
    run_query ($sql, "org_client_status table creation");
    
//report_profile -- Basically works the same as client_rpt_profile above, except this report is for the household (made up of one or more clients).  The householdds are defined by the "report_relationship" table below. See "reports_class.inc" and "client_report_entry_class.inc" for details.		
	$sql = 
    "CREATE TABLE report_profile (
        rpt_prof_rowid ".$auto_increment.", 
        hh_report_id INT, 
        report_date_begin INT, 
        report_date_end INT, 
        report_type VARCHAR(40) NOT NULL, 
        report_org_id INT, 
        report_user_id INT, 
        report_timestamp VARCHAR(50), 
        report_sharing_permission VARCHAR(5) NULL, 
        succeeded_by INT NULL,
        PRIMARY KEY ( hh_report_id )
    )";
	run_query ($sql, "validation table creation");
    $sql = "CREATE INDEX idx_rpt_prf__rowid ON report_profile(rpt_prof_rowid)";
    run_query ($sql, "report_profile index creation");
    $sql = "CREATE INDEX idx_rpt_prf__rpt_id ON report_profile(hh_report_id)";
    run_query ($sql, "report_profile index creation");
    $sql = "CREATE INDEX idx_rpt_prf__report_type ON
    report_profile(report_type)";
    run_query ($sql, "report_profile index creation");
    $sql = "CREATE INDEX idx_rpt_prf__rpt_org_id ON
    report_profile(report_org_id)";
    run_query ($sql, "report_profile index creation");
    
    
//report_answers -- Same as the "client_rpt_answers" table above. See "reports_class.inc" and "client_report_entry_class.inc" for details.	
	$sql = 
    "CREATE TABLE report_answers (
        rpt_ans_rowid ".$auto_increment.", 
        hh_report_id INT, 
        question_id INT, 
        axis_x INT NULL, 
        axis_y INT NULL, 
        report_answer VARCHAR(255) NULL, 
        report_answer_int INT NULL,
    FOREIGN KEY ( hh_report_id ) REFERENCES report_profile,
    FOREIGN KEY ( question_id ) REFERENCES questions
    )";
	run_query ($sql, "report answer table creation");
    $sql = "CREATE INDEX idx_rpt_ans__rpt_ans_rpt_id ON report_answers(hh_report_id)";
    run_query ($sql, "rpt_ans index creation");
    
//report_relationship -- Defines the relationships between individual clients that make up a "household." See "reports_class.inc" and "client_report_entry_class.inc" for details.
//"report_id" is the id of the reports defined in the "report_profile" table above.
//"client_id" is the client_id as defined in the "clients" table.
//"client_relationship_to_lead" can be: "child", "spouse", "friend", "sibling", and "non_immediate_family".
	$sql = 
    "CREATE TABLE report_relationship (
        rpt_rel_rowid ".$auto_increment.", 
        hh_report_id INT, 
        client_id INT, 
        client_relationship_to_lead VARCHAR (24) NULL,
        FOREIGN KEY ( hh_report_id ) REFERENCES report_profile,
        FOREIGN KEY ( client_id ) REFERENCES clients 
    )";
	run_query ($sql, "validation table creation");
    $sql = "CREATE INDEX idx_rpt_rel__rpt_id ON report_relationship(hh_report_id)";
    run_query ($sql, "rpt_rel index creation");
    $sql = "CREATE INDEX idx_rpt_rel__cln_id ON report_relationship(client_id)";
    run_query ($sql, "rpt_rel index creation");
    
/* DEPRECATED
//sharing_permissions -- Defines what organizations can share (view) reports entered by other organizations.  See sharing_permissions_class.inc" and "reports_class.inc" for details.
	$sql = 
    "CREATE TABLE sharing_permissions (
        sharing_permissions_rowid ".$auto_increment.", 
        sharing_org_id INT, 
        sharing_with_org_id INT
    )";
	run_query ($sql, "validation table creation");
*/
/////PERMISSIONS TABLES

//per_group_profile -- Defines the group owner, name, and id.  If the owner is "1", everyone can add users to the group, but cannot modify the
// permissions
//"group_id" the key
//"org_id" owner of the group
//"group_name" is the common name of the group
    $sql = "
    CREATE TABLE per_group_profile (
       per_group_id ".$auto_increment.",
       org_id INT,
       user_id INT,
       group_name VARCHAR(100), 
       date_stamp INT,
       PRIMARY KEY ( per_group_id ),
       FOREIGN KEY ( org_id ) REFERENCES organizations, 
       FOREIGN KEY ( user_id ) REFERENCES gate
     )";
    run_query ($sql, "group_profile creation");
    
    $sql = "CREATE INDEX idx_per_grp_pro__gr_id ON per_group_profile(per_group_id)";
    run_query ($sql, "group_profile index creation");
    
    $sql = "CREATE INDEX idx_per_grp_pro__gr_org ON per_group_profile(org_id)";
    run_query ($sql, "group_profile owener org id creation");
    
    
//per_group_associated_orgs -- Defines which organizations are members of the group
//"group_id" is key from above
//"org_id" is organizations associated with the group.  "1" means all organizations
    $sql = "
    CREATE TABLE per_group_associated_orgs (
        per_group_id INT, 
        org_id INT,        
        date_stamp INT,
        FOREIGN KEY ( per_group_id ) REFERENCES per_group_profile, 
        FOREIGN KEY ( org_id ) REFERENCES organizations
     )";
     run_query ($sql, "per_group_associated_orgs creation");
     
     $sql = "CREATE INDEX idx_per_grp_org__gr_org ON per_group_associated_orgs(per_group_id)";
     run_query ($sql, "group_profile index creation");
     
     $sql = "CREATE INDEX idx_per_gr_org__mem_org_id ON per_group_associated_orgs(org_id)";
     run_query ($sql, "member_org_id index creation");
     

//per_associated_users -- Defines users associated with groups
//"group_id" is key from above
//"user_id" is the users associated with a group.  "1" means all users.
    $sql = "
    CREATE TABLE per_associated_users (
        per_group_id INT, 
        user_id INT,
        date_stamp INT, 
        FOREIGN KEY ( per_group_id ) REFERENCES per_group_profile,   
        FOREIGN KEY ( user_id ) REFERENCES gate 
    )";
    run_query ($sql, "per_associated_users table creation");
        
    $sql = "CREATE INDEX idx_per_as_usr__gr_org ON per_associated_users(per_group_id)";
    run_query ($sql, "group_as_users_gp_id index creation");
        
    $sql = "CREATE INDEX idx_per_as_usr__us_id ON per_associated_users(user_id)";
    run_query ($sql, "group_as_users_us_id index creation");
    
//per_associated_user_notification -- Defines users who receive notification 
//"group_id" is key from above
//"user_id" is the users associated with a group.  "1" means all users.
    $sql = "
    CREATE TABLE per_associated_users_notificati (
        per_group_id INT, 
        user_id INT,
        date_stamp INT, 
        FOREIGN KEY ( per_group_id ) REFERENCES per_group_profile,   
        FOREIGN KEY ( user_id ) REFERENCES gate 
        
    )";
    run_query ($sql, "per_associated_users table creation");
        
    $sql = "CREATE INDEX idx_per_as_usr_notf__gr_org ON per_associated_users_notificati(per_group_id)";
    run_query ($sql, "group_as_users_gp_id index creation");
        
    $sql = "CREATE INDEX idx_per_as_usr_notf__us_id ON per_associated_users_notificati(user_id)";
    run_query ($sql, "group_as_users_us_id index creation");
    
//per_group_associated_reports -- Defines which reports are associated with the group    
//"per_group_id" key from per_group_profile
//"report_type_id" is the id originated in the report_type_profile table, can be "ALL", meaning all reports
//"create" 1 means yes
//"read" 1 means yes
//"edit" 1 means yes 
    $sql = "
    CREATE TABLE per_group_associated_reports ( 
        per_group_id INT,
        report_type_id VARCHAR(40),
        create_rpt INT NULL,
        read_rpt INT NULL,
        edit_rpt INT NULL,
        date_stamp INT,  
        FOREIGN KEY ( per_group_id ) REFERENCES per_group_profile,
        FOREIGN KEY ( report_type_id ) REFERENCES report_type_profile
        
    )";
    run_query ($sql, "per_group_associated_reports creation"); 
    
    $sql = "CREATE INDEX idx_per_as_rt__gr_id ON per_group_associated_reports(per_group_id)";
    run_query ($sql, "group_profile index creation");    
    
    $sql = "CREATE INDEX idx_per_gr_org__rpt_id ON per_group_associated_reports(report_type_id)";
    run_query ($sql, "associated reports index creation");   
    
    
//Backup versions of above
    
    $sql = "CREATE TABLE per_group_profile_b (
     per_group_id INT,
     org_id INT,
     user_id INT, 
     group_name VARCHAR(100),  
     date_stamp INT )";
    run_query ($sql, "group_profile creationb");
    
    
    $sql = "CREATE TABLE per_group_associated_orgs_b (
      per_group_id INT, 
      org_id INT,
      
      
      date_stamp INT
      )";
    run_query ($sql, "per_group_associated_orgs creation_b");
    
    
    $sql = "CREATE TABLE per_associated_users_b (
        per_group_id INT, 
        user_id INT,
        
        date_stamp INT
        
        )";
    run_query ($sql, "per_associated_users table creationb");
    
    $sql = "CREATE TABLE per_assocted_usr_notif_b (
        per_group_id INT, 
        user_id INT,
        
        date_stamp INT
        
        )";
    run_query ($sql, "per_associated_users_notification table creationb");
           
    $sql = "CREATE TABLE per_group_associated_reports_b ( 
        per_group_id INT,
        report_type_id VARCHAR(40),
        create_rpt INT NULL,
        read_rpt INT NULL,
        edit_rpt INT NULL,  
        
        date_stamp INT
    )";
    run_query ($sql, "per_group_associated_reports creationb"); 
//FOREIGN KEY ( per_group_id ) REFERENCES per_group_profile_b,     


///////SERVICES Tables
    $sql = "CREATE TABLE services_taxonomy (
        service_id int NOT NULL ,
        taxonomy_code varchar (13) NOT NULL ,
        taxonomy_term varchar (60) NOT NULL ,
        taxonomy_summary TEXT NOT NULL ,
        service_display_term varchar (60) NOT NULL ,
        service_custom SMALLINT NOT NULL,
        service_active INT NOT NULL,
        PRIMARY KEY ( service_id )
    )";
    run_query ($sql, "services_taxonomy creation");
    $sql = "CREATE INDEX idx_ser_tax__ser_id ON services_taxonomy(service_id)";
    run_query ($sql, "services_taxonomy index");   
    
    
    $sql = "CREATE TABLE services_provided_by_org (
        org_id int NOT NULL ,
        service_id int NOT NULL,
        FOREIGN KEY ( org_id ) REFERENCES organizations,
        FOREIGN KEY ( service_id ) REFERENCES services_taxonomy
    )";
    run_query ($sql, "services_provided_by_org creation");
    $sql = "CREATE INDEX idx_ser_prov__ser_id ON services_provided_by_org(service_id)";
    run_query ($sql, "services provided index");
    $sql = "CREATE INDEX idx_ser_prov__org_id ON services_provided_by_org(org_id)";
    run_query ($sql, "services provided index"); 


/////////MENU Definition Tables

//menu -- Defines menu items displayed in the menu section on each page.  See the "menu" function in "page_elements_display.inc" for details.
//"menu_element_id" must be a unique identifier used to relate with the "menu_permissions" table below.
//"menu_title" is the text displayed in the menu.
//"menu_link" is the html link associated with the "menu_title".
//"menu_order" is determines the dsiplay order on the menu listing (smaller numebrs first).
	$sql = "
    CREATE TABLE menu (
        menu_rowid ".$auto_increment.", 
        menu_element_id VARCHAR(25), 
        menu_title VARCHAR(50), 
        menu_link TEXT, 
        menu_order INT,
        PRIMARY KEY ( menu_element_id )
    )";
	run_query ($sql, "menu table creation");
    $sql = "CREATE INDEX idx_menu__menu_el_id ON menu(menu_element_id)";
    run_query ($sql, "menu index creation");   
    

//menu_permissions -- Defines which pages and for what users each menu item defined above appears on. See the "menu" function in "page_elements_display.inc" for details.
//"menu_element_id" ties the entry to the menu item defined in "menu" table.
//"menu_permisions_page_id" defines which pages this item will appear on. "all" entered in this field shows the item on all pages.
//"menu_permissions_level" is the type of user the item appears for.  The user "levels" are defined in the "user_access_level" filed in the "user_info" table. "all" entered in this field shows the item to all levels of users. 
	$sql = "
        CREATE TABLE menu_permissions (
        menu_per_rowid ".$auto_increment.", 
        menu_element_id VARCHAR(25), 
        menu_permissions_page_id VARCHAR(40), 
        menu_permissions_level VARCHAR(4),
        FOREIGN KEY ( menu_element_id ) REFERENCES menu
    )";
	run_query ($sql, "menu permissions table creation");
    $sql = "CREATE INDEX idx_menu_pers__menu_el_id ON menu_permissions(menu_element_id)";
    run_query ($sql, "menu perms index creation"); 

//pre_built_queries -- Holds the sql for pre-build queries such as those used in the HUD APR
//"query_title" is the name of the query placed in an HTML template with brackets, that is replaced with the result of the query;{clients_served}
//"query_sql" holds the SQL template for the query
//"query_type" defines wats sort of processsing the SQL should undergo; options are count|household_service_days|client_service_days|client_list
//"client_list_elements_template" consists of an html template defing which client_report elements are displayed
	$sql = "
    CREATE TABLE pre_built_queries ( 
        query_title VARCHAR(60), 
        query_sql TEXT, 
        query_type VARCHAR(30),
        client_list_elements_template TEXT NULL
    )";
	run_query ($sql, "pre-built query table creation");
    $sql = "CREATE INDEX idx_pre_built__q_title ON pre_built_queries(query_title)";
    run_query ($sql, "menu perms index creation"); 

//The following two tables are part of the bug reporting/feature request system. See "system_critique.php" for details.
	$sql = "CREATE TABLE critiques (critique_rowid ".$auto_increment.", critique_id INT, critique_title VARCHAR(200), critique_type VARCHAR(15), critique_score INT, critique_status VARCHAR(15), critique_page_url VARCHAR(200), critique_author VARCHAR(51), critique_last_comment_date INT)";
	run_query ($sql, "critique table creation");

	$sql = "CREATE TABLE critique_entries (critique_entry_rowid ".$auto_increment.", critique_id INT, critique_user_id
	INT, critique_priority INT, critique_comment TEXT, critique_date INT, critique_author_name VARCHAR(100), critique_author_email VARCHAR(100), critique_comment_title
    VARCHAR(200) )";
	run_query ($sql, "critique_entries table creation");

//referrals -- Records the referrals made from "vacancies.php"->"clients.php"
//"report_id" The household report id of the report that contains the referral report
//"referred_by_org_id" The organization that initiated the referral
//"referred_by_user_id" The user id of the person who made the referral
//"referred_to_org_id" The organization id of the organization the referral as made to
//"referral_closed_by_user_id" The id of the user that closed the referral
//"date_created" The date the referral was created
//"date_closed" The date the referral was closed by the receiving organization
	$sql = "
    CREATE TABLE referrals (
        referrals_rowid ".$auto_increment.", 
        hh_report_id INT, 
        referred_by_org_id INT,
	    referred_by_user_id INT, 
        referred_to_org_id INT, 
        referral_closed_by_user_id INT NULL, 
        date_created INT, 
        date_closed INT NULL,
        FOREIGN KEY ( hh_report_id ) REFERENCES report_profile
    )";
	run_query ($sql, "referrals table creation");

//Vacancy system tables    
    $sql = "
    CREATE TABLE vacancy_report_profile (
        vacancy_report_id INT, 
        org_id INT, 
        vacancy_report_date INT, 
        report_updated_by_user INT,
        FOREIGN KEY ( org_id ) REFERENCES organizations
    )";
    run_query ($sql, "Create vacancy_report_profile table");
    
    $sql = "
    CREATE TABLE vacancy_answers (
        vacancy_report_id INT, 
        question_id INT, 
        answer VARCHAR(255) NULL, 
        answer_int INT NULL,
        FOREIGN KEY ( question_id ) REFERENCES questions
    )";
    run_query ($sql, "Create vacancy_answers table");
    
    $sql = "
    CREATE TABLE vacancy_report_profile_previous (
        vacancy_report_id INT, 
        org_id INT, 
        vacancy_report_date INT, 
        report_updated_by_user INT
    )";
    run_query ($sql, "Create vacancy_report_profile table");
    
    $sql = "
    CREATE TABLE vacancy_answers_previous (
        vacancy_report_id INT, 
        question_id INT, 
        answer VARCHAR(255) NULL, 
        answer_int INT NULL
    )";
    run_query ($sql, "Create vacancy_answers table");
 
 
    ////Create obfuscated date index
    $sql = 
        'CREATE TABLE dates_encrypted (
        date_index_encrypted VARCHAR(200),
        date_encrypted VARCHAR(200)
    )';
    run_query ( $sql, 'Create dates encrypted table' );


    create_encrypted_dates ();
    echo '<p/>Indexing encrypted_dates . .';
    
    $sql = 'CREATE INDEX idx_dates_en__d_index ON dates_encrypted(date_index_encrypted)';
    run_query ( $sql, 'Date index creation' );
    echo '. .';
    $sql = 'CREATE INDEX idx_dates_en__date ON dates_encrypted(date_encrypted)';
    run_query ( $sql, 'Date index creation' );
    
    
 
 
    

    //transaction_commit ();

    if ( $db_file == 'db_connection_mssql' )    {
        //transaction_begin ( "Begin identity inser on" );
        $idi = "SET IDENTITY_INSERT gate ON";
        //mssql_query ($idi);
        $idi = "SET IDENTITY_INSERT questions ON";
        //mssql_query ($idi);
        $idi = "SET IDENTITY_INSERT question_elements ON";
        //mssql_query ($idi);
        $idi = "SET IDENTITY_INSERT table_elements ON";
        //mssql_query ($idi);
        //$idi = "SET IDENTITY_INSERT question_elements ON";
        //mssql_query ($idi);
        //transaction_commit ();

    }

}


function setup_test_data ()
{
    GLOBAL $include_root, $db_file, $db_version;
    
    include($include_root."test_data.inc");
    
}



function system_admin_setup ()
{
    GLOBAL $include_root, $form_answer, $tag_values, $question_validation_error, $head_dynamic_style;
    GLOBAL $HTTP_GET_VARS, $HTTP_POST_VARS, $group_term, $organization_term;
	
    if ($HTTP_GET_VARS["setup_type"] == "setup_base_tables")
    {
        setup_system();
    }
    
    elseif ($HTTP_GET_VARS["setup_type"] == "insert_questions")
    {
        echo "<html><head><script language=\"javascript\">function redirectb() { window.location.href =
    \"".$system_directory."SETUP.php?setup_type=insert_services\";}</script></head><body>";
        ////echo 'Creating service type tables';
        include($include_root.'setup_questions.inc');
        echo "<script language=\"javascript\">redirectb()</script>;";
        echo "</body></html>";
    }
    
    elseif ($HTTP_GET_VARS["setup_type"] == "insert_services") {
        
        echo "<html><head><script language=\"javascript\">function redirectb() { window.location.href =
    \"".$system_directory."SETUP.php?setup_type=insert_services2\";}</script></head><body>";
        ////echo 'Creating service type tables';
        include($include_root.'services_taxonomy_insert.inc');
        echo "<script language=\"javascript\">redirectb()</script>;";
        echo "</body></html>";
    }
    elseif ($HTTP_GET_VARS["setup_type"] == "insert_services2")
    {
        echo "<html><head><script language=\"javascript\">function redirectb() { window.location.href =
    \"".$system_directory."SETUP.php?setup_type=setup_options\";}</script></head><body>";
        ////echo 'Creating service type tables';
        include($include_root.'services_taxonomy_insert2.inc');
        echo "<script language=\"javascript\">redirectb()</script>;";
        echo "</body></html>";
    }
    
    
    
    if ($HTTP_GET_VARS["setup_type"] == "setup_options")
    {
        echo "<b><a href=\"".$system_directory."SETUP.php?setup_type=insert_test_data\"><p/>Click Here</a>, to load test data into the system.</b>  Use this option if you have not seen the HMIS.<br/> It will setup an imaginary set of shelters, users, and clients for you to experiment with.<p>";
         echo "<b><a href=\"".$system_directory."SETUP.php?setup_type=setup_superuser\">Click Here</a>, to start with an empty system.</b> Use this option if you want to actually deploy the HMIS system to collect data.<p>"; 
    }
    elseif ($HTTP_GET_VARS["setup_type"] == "insert_test_data")
    {
		echo "Seting up test data. vals is:".$HTTP_GET_VARS["setup_type"];
        setup_test_data();
        echo "<p/>IMPORTANT: A test set of groups, organizations, users and clients has been installed in your HMIS system.<br/>";
        echo "You can gain a basic understanding of how the system works by using the walkthrough, available at <a
href=\"http://homeless-mis.net/hmis/READ_ME.html#walk_through\" target=\"_blank\">homeless-mis.net/hmis/READ_ME.html#walk_through</a>; or (on your server) <a href=\"".$system_directory."READ_ME.html#walk_through\"  target=\"_blank\">".$system_directory."READ_ME.html#walk_through</a><p>";
        echo "Below is login and password information for some of the users in the test setup, that you can use to experiment with the HMIS system. <br/><p><b>You should print this page out for future reference.</b><p>";
        echo "<b>To login to the system <a href=\"".$system_directory."index.php\">click here</a> and enter one of the logins/passwords
		below.</b><p>"; 
        
        echo "<table border=\"1\">";
        echo "<tr><td colspan=\"4\">System Administrator</td><tr>";
        echo "<tr><td>User Type</td><td>Name</td><td>Login</td><td>Password</td></tr>";
        echo "<tr><td>System Administrator: Has ultimate authority over the system</td><td>Ruby Sysmin</td><td>testlogin</td><td>testpassword</td></tr>";
        
        echo "</table><p/>";
        
        echo "<table border=\"1\">";
        echo "<tr><td colspan=\"4\">Trundle County</td><tr>";
        echo "<tr><td>User Type</td><td>Name</td><td>Login</td><td>Password</td></tr>";
        echo "<tr><td>Group Administrator for Trundle County</td><td>Boutros Gali</td><td>boutrosg</td><td>headscrap</td></tr>";
        echo "<tr><td>Organization Administrator for Alpha Shelter</td><td>Jane Sloan</td><td>janesloan</td><td>wicketmash</td></tr>";
        echo "<tr><td>System User for Alpha Shelter</td><td>Carl Turner</td><td>carlturner</td><td>seajumping</td></tr>";
        echo "<tr><td>Organization Administrator for Delta Shelter</td><td>Roger Nelson</td><td>rogernelson</td><td>inkswelling</td></tr>";
        echo "<tr><td>System User for Delta Shelter</td><td>Dory Tildon</td><td>dorytildon</td><td>droppingblue</td></tr>";
        echo "</table><p/>";
        
        
        
    }
    elseif ($HTTP_GET_VARS["setup_type"] == "setup_superuser")
    {
    //echo "Setting up superuser<p>";
    	
	    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."head.inc");	
	    include($include_root."template_parser.inc");
	    include($include_root."Insert_setup_group_class.inc");
	    include($include_root."questions_into_tags_function.inc");
	    include($include_root."page_elements_display.inc");
        include($include_root."user_info_functions.inc");



	    //$empty_test = run_query ("SELECT * FROM gate", "group login setup");
	    //$numrows = num_rows($empty_test);
	    //if($numrows > 0){echo "Setup Complete, <a href=\"index.php\">Click Here to Login</a>"; exit;}

        //$q = new Load_new_entity_questions;
	    //$quest = array ($q->questions["system_name"], $q->questions["first_name"], //$q->questions["last_name"], $q->questions["phone_number"], $q->questions["email_address"], //$q->questions["web_page_address"], $q->questions["login_system"], //$q->questions["password_system"]);


        $quest = new Load_new_entity_questions();
        $quest->load_new_system_questions();

	    //If a form is submitted from a page with the same $page_id, process it
	    if ($HTTP_POST_VARS["form_answer"])
        {
		    //echo "In form processor";
            //Put the relevant question attributes into an array
		    //Put the relevant question attributes into an array



            //$pull_questions = new Questions_into_array("genesis", "10", "1", "1", "display");
		    //$pull_questions->Questions_into_array;

		    //Validate the answers against the questions attributes pulled into an array above
		    $validation = new Questions_answers_validation ($quest->questions, $quest->question_elements, $HTTP_POST_VARS["form_answer"]);
		    //$validation->Questions_answers_validation($pull_questions->questions, $pull_questions->question_elements, $form_answer);

		    if (!$question_validation_error)
            {
			    //echo "<p>actually inserting<p>";
                $insert_login = new Insert_setup_group ($quest, $validation->form_answer, '10');

		        //foreach ($pull_questions->questions as $sayit)	{echo "Doingit";}
		        if (!$question_validation_error)
                {
		    	    echo "To login to the system <a href=\"".$system_directory."add_groups.php\">click here</a> and enter the Administrator's login and password you entered on the previous form."; 
		    	    exit;
		        }
		    }

	    }

	    //Pull appropriate questions attributes into an array (based on page, user, org, and access level) for display
	    //$pull_questions_display = new Questions_into_array("genesis", "10", "1", "1", "display");
        
	    //Define the values for tags contained in the html template
	    $tag_values["{FORM_START}"] = form_start ("SETUP.php?setup_type=setup_superuser", "");
	    $tag_values["{FORM_END}"] = form_end ("Submit System Administrator Login and Password", 1);
	    $tag_values["{HEAD}"] = head ("System Administrator Setup", $head_page_specific, $head_dynamic_style);
        
	    questions_into_tags ($quest->questions, $quest->question_elements, $validation->form_answer);
        
	    //Substitute the specific question tags, leftover question tags, and specific tags in the html template with dynamic values, and display the result
	    template_parser ("genesis.html", $tag_values);
	    //template_parser ("genesis.html", $pull_questions_display->questions, $pull_questions_display->question_elements, $tag_values, $validation->form_answer);
    }
    else
    {
        echo 'Nothing....';
    }
}



function create_encrypted_dates ()
{
    //Settings for maximum date encryption
    $last_year_in_table = 2030;
    $max_num_fake_records_between_days = 100;
    $max_num_fake_records_within_day = 10;
    
    //Setting for minimal date encryption
    $last_year_in_table = 2010;
    $max_num_fake_records_between_days = 0;
    $max_num_fake_records_within_day = 0;
    
    //Loop through the years we want to create
    $en = new Encryption();
    $vv = 0;
    
    echo 'Creating encrypted dates table, this will take several minutes<br/>';
    
    for ( $y = 1900; $y < $last_year_in_table; $y++ )
    {
        echo $y.'&nbsp;';
        $vv++;
        if ( $vv > 24 ) {
            echo '<br/>';
            $vv = 0;
        }
        
        //Loop through the months in a year
        for ( $m = 1; $m < 13; $m++ )
        {
            if ( $m < 10 )
            {
                $m = '0'.$m;
            }
            
            //Loop through the days in a month
            for ( $d = 1; $d < 32; $d++ )
            {
                if ( $d > 28 && !checkdate ( $m, $d, $y ) )
                {
                    break 1;
                }
                
                if ( $d < 10 )
                {
                    $d = '0'.$d;
                }
                //echo "Year: ".$y.", Month: ".$m.", Day: ".$d."<br/>";
                //Create records with that equal the day, with a usless four digit extension to make the records encrypt to
                //different values
                
                //Determine how many fake records for this day
                $num_fake_records_between_days = rand ( 0, $max_num_fake_records_between_days );
                for ( $jr = 0; $jr < $num_fake_records_between_days; $jr++ )
                {
                    $date_index++;
                    $junk_record_index = rand ( 5001, 9999 );
                    $date_val = $y.$m.$d.$junk_record_index;
                    $date_index_encrypted = $en->encrypt_data ( $date_index );
                    $date_val_encrypted = $en->encrypt_data ( $date_val );
                    $sql = "
                    INSERT INTO dates_encrypted ( date_index_encrypted, date_encrypted ) 
                    VALUES ( '".addslashes ( $date_index_encrypted )."', '".addslashes ( $date_val_encrypted )."' )"; 
                    run_query ( $sql, 'Insert encrypted fake date between days' ); 
                    //echo $date_val.' Between day fake records<br/>';
                    
                }
                
                $num_fake_records_within_day = rand ( 0, $max_num_fake_records_within_day );
                
                
                for ( $i = 1000; $i < 1001; $i++ )
                {
                    $date_index++;
                    $date_val = $y.$m.$d.$i;
                    $date_index_encrypted = $en->encrypt_data ( $date_index );
                    $date_val_encrypted = $en->encrypt_data ( $date_val );
                    $sql = "
                    INSERT INTO dates_encrypted ( date_index_encrypted, date_encrypted ) 
                    VALUES ( '".addslashes ( $date_index_encrypted )."', '".addslashes ( $date_val_encrypted )."' )";  
                    run_query ( $sql, 'Insert encrypted real date' );
                    //echo $date_index.' - '.$date_val.' REAL<br/>';
                    //Insert val into table
                    
                    
                    //Generate a random number of intervening records, that are useless filler to foil simple runumbers attempts to
                    //decode the date index 
                    $junk_records_count = rand ( 0, $num_fake_records_within_day );
                    for ( $jr = 0; $jr < $junk_records_count; $jr++ )
                    {
                        $date_index++;
                        $junk_record_index = rand ( 1301, 5000 );
                        $date_val = $y.$m.$d.$junk_record_index;
                        $date_index_encrypted = $en->encrypt_data ( $date_index );
                        $date_val_encrypted = $en->encrypt_data ( $date_val );
                        $sql = "
                        INSERT INTO dates_encrypted ( date_index_encrypted, date_encrypted ) 
                        VALUES ( '".addslashes ( $date_index_encrypted )."', '".addslashes ( $date_val_encrypted )."' )";  
                        run_query ( $sql, 'Insert encrypted fake date within day' );
                        //echo $date_index.' - '.$date_val.' Within day fake records<br/>';
                    }
                
                }
            
            }
        
        
        }
    
    
    }

/*
$xx = time();
echo $x."<p>";
echo $xx."<p>";

$elapsed = $xx-$x;
echo "Time: ".$elapsed."<p>";
$minutes = $elapsed/60;
echo "Time minutes: ".$minutes."<p>";
$total_minutes = (150/1) * $minutes;
echo "Predicted total Time minutes: ".$total_minutes."<p>";
*/

}

/*
GLOBAL VARIABLES DESCRIPTION:

$head_page_specific:  Contains special head markup.  Output in head.inc

$head_dynamic_style: Contains all the styles added by various functions (i.e., marking errors red).  Output in head.inc.  Generic stypes should be placed directly in head.inc. Styles that are associated with specific page elements (i.e., the menu) should be defined by the function that generates the HTML.

$head_dynamic_ecma[0]: Javascript in the head to html. Output in head.inc.

$head_dynamic_ecma[1]:  Javascript that lives in the opening body tag.  Is executed when the page loads.  Used to start Javascript functions in the head.

$question_validation_error: Question validation functions send their errors to questions_display.inc/question_error_marking, which puts an error message in the following format - $question_validation _error[Question ID] = [Error message displayed with question].  If the variable is set for a particular question, that question is read by various functions as being in error.  As a rule, no data is inserted into the database until this variable is completely not set.  The error message assigned to $question_validation _error is output by the different question type functions in questions_display.inc.
 
$message: The message that appears near the top of the page (i.e. Client info entered successfully OR "Error, see belwow).  Output in page_elements_display.inc/page_title_table.

$message_type: Can be either "ok" (makes message background green) or "error" (makes message background red).  If there is a $question_validation error, it is set to "error." Processed in page_elements_display.inc/page_title_table.

$page_title: Shown in the page_title_table at the top left of each page.

$instructions: Shown in the page_title_table at the top left of each page.

$page_id: Has several functions 1)Helps forms determine where they have come from (depreciated), provides "bookmark to specific section in help page. 

*/

?>
Return current item: Homeless Mangement Information System