<?php
//*Client Data System, Copyright (C) 2000, 2001, 2002 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
class Query
{
var $raw_quest;
var $raw_quest_elements;
var $quest;
var $quest_elements;
var $form_answers;
var $vetted_form_answer;
var $vetted_value;
var $sql_begin;
var $sql_from;
var $sql_from_survey;
var $sql_where_or;
var $sql_where_and;
var $sql_where_and_date;
var $sql_where_and_date_template;
var $sql_where_and_organizations;
//var $sql_where_and_organizations_template_hh;
//var $sql_where_and_organizations_template_client;
var $sql_where_and_hh_count;
var $and_count;
var $sql_included_orgs;
var $hh_service_days_count_select_field_sql;
var $client_service_days_count_select_field_sql;
var $or_count;
var $hh_client_id_join_count;
var $hh_profile_join_count;
var $hh_answers_join_count;
var $client_profile_join_count;
var $client_answers_join_count;
var $last_or_question_id;
var $related_orgs_array;
var $client_ids_query_result;
var $get_days_of_service = false; //init here since no constructor
function pull_related_organizations ($group_id, $org_id, $access_level)
{
global $unique_seq;
//Only return a value if not the system admin
if($access_level != "10")
{
switch($access_level)
{
case "20":
$select_org_sql = "SELECT org_id, org_name FROM organizations WHERE group_id = '".$group_id."' AND org_id != '".$org_id."'";
break;
case "30":
$select_org_sql = "SELECT org_id, org_name FROM organizations WHERE org_id = '".$org_id."'";
break;
case "40":
$select_org_sql = "SELECT org_id, org_name FROM organizations WHERE org_id = '".$org_id."'";
break;
default:
echo "No pulling of orgs based on level for query.";
}
$resulting_orgs = run_query($select_org_sql, "Finding orgs by access level for query");
$result_count = num_rows($resulting_orgs);
for( $x=0; $x <$result_count; $x++ )
{
$fetched_org_id = fetch_array($resulting_orgs, "Orgs for query", $x);
$z_org_id = "org_id";
$this->related_orgs_array[$fetched_org_id[$z_org_id]] = $fetched_org_id["org_name"];
}
return $this->related_orgs_array;
}
}
function build_related_organizations_checkboxes_table ($group_id, $org_id, $access_level, $form_answer, $query_page)
{
GLOBAL $organization_term;
$related_orgs_array = $this->pull_related_organizations ($group_id, $org_id, $access_level);
//Only perform this function if there are related orgs, ie, not the systam admin access level 10
if ($related_orgs_array)
{
$final_html .= "<table><tr><td class=\"genericsubtop\">".$organization_term." Included in Query:</td></tr><tr><td>";
foreach($related_orgs_array AS $org_id => $org_name)
{
//echo $org_name."-".$org_id."<br>";
$final_html .= str_replace (" ", " ", $org_name);
$final_html .= "<input type=\"checkbox\" name=\"form_answer[included_orgs][".$org_id."]\" value=\"yes\" ";
if($form_answer["included_orgs"][$org_id] == "yes" || $query_page == "blank")
{
$final_html .= "checked ";
}
$final_html .= build_tabindex_html(1010)."> ";
}
$final_html .= "</td></tr></table>";
}
$this->build_included_orgs_query_sql ( $form_answer );
return $final_html;
}
function pull_query_questions ($rpt_name)
{
GLOBAL $access_level, $org_id, $group_id;
//echo "Report name is: ".$rpt_name."<p>";
$tmp = new Questions_into_array ($rpt_name, $access_level, $org_id, $group_id, "display");
///Pull out the ones we can do queries on (text, textbox)
foreach ($tmp->questions AS $current_question)
{
if (
$current_question['question_type'] == 'text'
|| $current_question['question_type'] == 'textbox'
|| ( $current_question['question_type'] == 'number' && $current_question['question_encrypted'] == 1 )
|| ( $current_question['question_type'] == 'date' && $current_question['question_encrypted'] == 1 )
)
{
//do nothing
$t=1;
}
else {
$tmp_questions[$current_question["question_id"]] = $current_question;
$tmp_quest_elements[$current_question["question_id"]] = $tmp->question_elements[$current_question["question_id"]];
}
}
$this->raw_quest[$rpt_name] = $tmp_questions;
$this->raw_quest_elements[$rpt_name] = $tmp_quest_elements;
//echo"Display of tmp:<p> ";
//display_value($this->raw_quest[$rpt_name]);
foreach ($tmp_questions AS $current_question)
{
$modified_id = $rpt_name."_".$current_question["question_id"];
//echo "Mod 1: ".$modified_id."<p>";
//echo "Question key is: ".$current_question["question_id"]."</br>";
$this->quest[$modified_id] = $current_question;
$this->quest[$modified_id]["question_id"] = $modified_id;
$this->quest_elements[$modified_id] = $tmp_quest_elements[$current_question["question_id"]];
}
}
function build_query_table ($report_name)
{
//echo "Report Name 2 is: ".$report_name."<br>";
if ($report_name == 'household_service')
$this->get_days_of_service = true;
//$form_answer;
$title = pull_report_type_profile ($report_name);
$html .= "<table>";
$html .= "<tr><td class=\"generictabletop\" colspan=\"2\">".$title["report_title"]."</td></tr>";
$td_count = 0;
foreach ( $this->raw_quest[$report_name] AS $q_id => $currnt_qust )
{
$modified_id = $report_name."_".$currnt_qust["question_id"];
//echo "Modified ID is: ".$modified_id."<p>";
//Make rows two questions long using <tr> and </tr>
if ($td_count == 2) {$html .= "</tr>\n"; $td_count = 0; }
if ($td_count == 0) {$html .= "<tr>\n"; }
//echo "TD coutn is ".$td_count."<p>";
//$html .= "<tr>";
$html .= $this->build_query_question ( $this->quest[$modified_id], $this->quest_elements[$modified_id] );
//$html .= "<td>".$td_count."</td>";
$td_count++;
//$html .= "<tr>";
}
//If there are an odd numer of questions, finish the table row and close it
if($td_count == "1"){$html .= "<td> </td></tr>\n<tr><td class=\"bottomline\" colspan=\"2\"> </td></tr>";}
if($td_count == "2"){$html .= "</tr>\n";}
$html .= "</table>";
return $html;
}
function build_query_question ($question, $question_elements, $form_answer="")
{
GLOBAL $tag_values;
//echo "Question type is: ".$question["question_type"]."<p>";
//echo "Question elements are:<br>";
//display_value($question_elements);
//Take the question out of the array of questions
//unset ($questions[$current_question["question_id"]]);
//Route the question to an HTML creation function based on what type it is, i.e., "text"
switch ($question["question_type"])
{
case "text":
break;
case "password":
break;
case "textarea":
break;
case "number":
$return_html = $this->query_display_number_question ($question,
$this->vetted_form_answer[$question["question_id"]], "yes");
break;
case "radio":
$return_html = $this->display_checkboxes_question ($question, $question_elements,
$this->vetted_form_answer[$question["question_id"]], "yes");
//echo "vetted for amswer is ".
break;
case "checkboxes":
$return_html = $this->display_checkboxes_question ($question, $question_elements,
$this->vetted_form_answer[$question["question_id"]], "yes");
break;
case "dropdown":
$return_html = $this->display_checkboxes_question ($question, $question_elements,
$this->vetted_form_answer[$question["question_id"]], "yes");
break;
case "table":
//$sql = $this->sql_from."WHERE ".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations;
//$sql["sql_from"] = $this->sql_from;
//$sql["sql_where_and"] =
$temp_class = new Display_table ($question, $question_elements, $this->vetted_form_answer,
"yes", "yes_query_display");
//display_value($this->vetted_form_answer[$question["question_id"]]);
$return_html = $temp_class->final_html;
//display_value ( $question_elements );
break;
case "date":
$return_html = $this->display_date_question_query ($question, $this->vetted_form_answer,
"yes" );
break;
default:
echo "Question #".$tags_num[0][0]."Not Found In Question Database, or question has no type defined<br>";
}
return $return_html;
}
function query_display_number_question ($question_attributes_array, $form_answer, $include_td = "")
{
GLOBAL $question_validation_error;
$question_id = $question_attributes_array["question_id"];
if($include_td) {$final_html .= "<td id=\"QUEST_".$question_id."\"> ";}
$final_html .= $question_attributes_array["question_title"]."<br></br> ";
//Range Bottom
$final_html .= "Greater than:";
$final_html .= "<input type=\"text\" ";
$final_html .= "name=\"form_answer[".$question_id."][range_bottom]\" ";
if($question_attributes_array["question_field_size"] > 10)
{
$final_html .= "size=\"10\" ";
}
else
{
$final_html .= "size=\"".$question_attributes_array["question_field_size"]."\" ";
}
$final_html .= "maxlength=\"".$question_attributes_array["question_field_size"]."\" ";
$final_html .= "value=\"".$form_answer["range_bottom"]."\" ";
$final_html .= build_tabindex_html($question_attributes_array["question_display_order"])."> ";
//Range top
$final_html .= "Less than:";
$final_html .= "<input type=\"text\" ";
$final_html .= "name=\"form_answer[".$question_id."][range_top]\" ";
if($question_attributes_array["question_field_size"] > 10)
{
$final_html .= "size=\"10\" ";
}
else
{
$final_html .= "size=\"".$question_attributes_array["question_field_size"]."\" ";
}
$final_html .= "maxlength=\"".$question_attributes_array["question_field_size"]."\" ";
$final_html .= "value=\"".$form_answer["range_top"]."\" ";
$final_html .= build_tabindex_html($question_attributes_array["question_display_order"]).">";
if ($question_validation_error[$question_id])
{
$final_html .= "<p id=\"error_style_".$question_id."\">".htmlspecialchars($question_validation_error[$question_id])."</p>";
}
if($include_td) {$final_html .= "</td>";}
return $final_html;
}
function display_checkboxes_question ($question_attributes_array, $question_elements_array, $form_answer, $include_td = "")
{
GLOBAL $question_validation_error;
GLOBAL $page_id;
GLOBAL $form_submitted;
$question_id = $question_attributes_array["question_id"];
if($include_td) {$final_html .= "<td id=\"QUEST_".$question_id."\">";}
$final_html .= $question_attributes_array["question_title"]."<br>";
if (is_array($question_elements_array))
{
foreach ($question_elements_array AS $key => $current_element)
{
$final_html .= "<span class=\"smaller\">";
$final_html .= str_replace (" ", " ", $current_element["question_element_title"]);
$final_html .= "</span>";
$final_html .= "<input type=\"checkbox\" name=\"form_answer[".$question_id."][".$key."]\" value=\"yes\" ";
//echo "In the display part question id is: ".$question_id." key is: ".$key." value is ".$form_answer[$key]."<p>";
if($form_answer[$key] == "yes")
{
$final_html .= "checked ";
}
$final_html .= build_tabindex_html($question_attributes_array["question_display_order"])."> ";
}
}
//If not an array of checkbox elements, add error message to HTML
else
{$final_html .= "No Checkboxes Defined for Question #".$question_id." ";}
if($question_attributes_array["question_type"] == "checkboxes")
{
//Output a "AND/OR" logic radio button selector
if ( $this->vetted_form_answer["logic_type"][$question_id] == "and" )
{
$and_checked = "checked=\"checked\"";
}
else
{
$or_checked = " checked=\"checked\"";
}
$final_html .= "<p></p>Logic Type: ";
$final_html .= "OR<input type=\"radio\" name=\"form_answer[logic_type][".$question_id."]\" value=\"or\"
".$or_checked."> ";
$final_html .= "AND<input type=\"radio\" name=\"form_answer[logic_type][".$question_id."]\" value=\"and\" ".$and_checked.">";
}
if ($question_validation_error[$question_id])
{
//echo "<p id=\"qid_2\">".$question_validation_error[$question_id]."</p>";
$final_html .= "<p id=\"error_style_".$question_id."\">".htmlspecialchars($question_validation_error[$question_id])."</p>";
}
if($include_td) {$final_html .= "</td>";}
return $final_html;
}
function display_date_question_query ($question_attributes_array, $form_answer, $include_td = "")
{
$low_attributes = $question_attributes_array;
$low_attributes["question_id"] = $low_attributes["question_id"]."_low";
$low_attributes["question_title"] = $low_attributes["question_title"]." - Greater than date";
//negate any requirement to answer for query
//$low_attributes["question_required"] = "no";
$low = new Display_date_question ($low_attributes, $form_answer, "");
$return_html .= "\n<td><table><tr>";
$return_html .= $low->f_html;
$return_html .= "";
$high_attributes = $question_attributes_array;
$high_attributes["question_id"] = $high_attributes["question_id"]."_high";
$high_attributes["question_title"] = $high_attributes["question_title"]." - Less than date";
$high = new Display_date_question ($high_attributes, $form_answer, "");
$return_html .= $high->f_html;
$return_html .= "</tr></table></td>";
return $return_html;
}
function validate_query_questions ($rpt_name, $form_answers)
{
//$this->question_elementsB = $question_elements;
$this->form_answer = $form_answers;
$this->vetted_form_answer["logic_type"] = $form_answers["logic_type"];
//Had to add settype because I was getting a warning that $questions was not an array, even though it returned the right output
//settype ($questions, "array");
foreach ($this->raw_quest[$rpt_name] AS $currnt_quest )
{
$modified_id = $rpt_name."_".$currnt_quest["question_id"];
//echo "Moded vettt id is: ".$modified_id."<p>";
$current_question = $this->quest[$modified_id];
$current_qu_elements = $this->quest_elements[$modified_id];
switch ($current_question["question_type"])
{
case "text":
//Do nothing, since you can't do aggregate queries on text
break;
case "password":
//Do nothing, since you can't do aggregate queries on text
break;
case "textarea":
//Do nothing, since you can't do aggregate queries on text
break;
case "table":
$this->validate_table_question_answer ($current_question,
$current_qu_elements);
break;
case "number":
$this->validate_number_question_answer ($current_question);
break;
case "checkboxes":
$this->validate_checkboxes_question_answer ($current_question,
$current_qu_elements);
break;
case "radio":
$this->validate_checkboxes_question_answer ($current_question,
$current_qu_elements);
break;
case "dropdown":
$this->validate_checkboxes_question_answer ($current_question,
$current_qu_elements);
break;
case "date":
$this->validate_date_question_answer ($current_question);
break;
default:
echo "I am default question validation switch on query quest validation<br>"; //exit;
}
}
}
function validate_number_question_answer ($question_attributes_array)
{
$question_id = $question_attributes_array["question_id"];
//echo "The question is is: ".$question_id."<br>";
//echo "The question form answer is: ".$this->form_answer[$question_id."range_bottom"]."<br>";
if(ereg("[^0-9]", $this->form_answer[$question_id]["range_bottom"]))
{
question_error_marking ($question_id, "Only Numbers Allowed. ");
}
$this->vetted_form_answer[$question_id]["range_bottom"] = trim($this->form_answer[$question_id]["range_bottom"]);
$this->vetted_form_answer[$question_id]["range_bottom"] = htmlspecialchars($this->vetted_form_answer[$question_id]["range_bottom"]);
if(strlen ($this->vetted_form_answer[$question_id]["range_bottom"]) > $question_attributes_array["question_field_size"])
{
question_error_marking ($question_id, "Overflow, Illegal Entry. ");
$this->vetted_form_answer[$question_id] = "";
}
$this->vetted_value[$question_id]["range_bottom"] = $this->vetted_form_answer[$question_id]["range_bottom"];
//Range top
if(ereg("[^0-9]", $this->form_answer[$question_id]["range_top"]))
{
question_error_marking ($question_id, "Only Numbers Allowed. ");
}
$this->vetted_form_answer[$question_id]["range_top"] = trim($this->form_answer[$question_id]["range_top"]);
$this->vetted_form_answer[$question_id]["range_top"] = htmlspecialchars($this->vetted_form_answer[$question_id]["range_top"]);
if(strlen ($this->vetted_form_answer[$question_id]["range_top"]) > $question_attributes_array["question_field_size"])
{
question_error_marking ($question_id, "Overflow, Illegal Entry. ");
$this->vetted_form_answer[$question_id] = "";
}
$this->vetted_value[$question_id]["range_top"] = $this->vetted_form_answer[$question_id]["range_top"];
}
function validate_table_question_answer ($question_attributes_array, $question_elements_array)
{
$question_id = $question_attributes_array["question_id"];
foreach ( $question_elements_array["x"] AS $current_x )
{
//echo "in an xxx";
if ( $current_x["axis_special_type"] != "text_axis" )
{
//echo "x is default<p>";
foreach ( $question_elements_array["y"] AS $current_y )
{
if ( $current_y["axis_special_type"] != "text_axis" )
{
$x_id = $current_x["axis_id"];
$y_id = $current_y["axis_id"];
//echo "The question is is: ".$question_id."<br>";
//echo "The question form answer is: ".$this->form_answer[$question_id][$x_id][$y_id]["range_bottom"]."<br>";
//If a query, check it
if ( $this->form_answer[$question_id][$x_id][$y_id]["range_bottom"] )
{
if(ereg("[^0-9]", $this->form_answer[$question_id][$x_id][$y_id]["range_bottom"]))
{
question_error_marking ($question_id, "Only Numbers Allowed. ");
}
$this->vetted_form_answer[$question_id][$x_id][$y_id]["range_bottom"] = trim($this->form_answer[$question_id][$x_id][$y_id]["range_bottom"]);
$this->vetted_form_answer[$question_id][$x_id][$y_id]["range_bottom"] =
htmlspecialchars($this->vetted_form_answer[$question_id][$x_id][$y_id]["range_bottom"]);
if(strlen ($this->vetted_form_answer[$question_id][$x_id][$y_id]["range_bottom"]) > $question_attributes_array["question_field_size"])
{
question_error_marking ($question_id, "Overflow, Illegal Entry. ");
$this->vetted_form_answer[$question_id] = "";
}
$this->vetted_value[$question_id][$x_id][$y_id]["range_bottom"] = $this->vetted_form_answer[$question_id][$x_id][$y_id]["range_bottom"];
//echo "question id is:".$question_id." x is: ".$x_id." y id is: ".$y_id."<br>";
}
//Range top
//If a query, check it
if ( $this->form_answer[$question_id][$x_id][$y_id]["range_top"] )
{
if(ereg("[^0-9]", $this->form_answer[$question_id][$x_id][$y_id]["range_top"]))
{
question_error_marking ($question_id, "Only Numbers Allowed. ");
}
$this->vetted_form_answer[$question_id][$x_id][$y_id]["range_top"] = trim($this->form_answer[$question_id][$x_id][$y_id]["range_top"]);
$this->vetted_form_answer[$question_id][$x_id][$y_id]["range_top"] = htmlspecialchars($this->vetted_form_answer[$question_id][$x_id][$y_id]["range_top"]);
if(strlen ($this->vetted_form_answer[$question_id][$x_id][$y_id]["range_top"]) > $question_attributes_array["question_field_size"])
{
question_error_marking ($question_id, "Overflow, Illegal Entry. ");
$this->vetted_form_answer[$question_id] = "";
}
$this->vetted_value[$question_id][$x_id][$y_id]["range_top"] = $this->vetted_form_answer[$question_id][$x_id][$y_id]["range_top"];
}
}
}
}
}
}
function validate_checkboxes_question_answer ($question_attributes_array, $question_elements_array)
{
$question_id = $question_attributes_array["question_id"];
foreach ($question_elements_array AS $element_key => $current_element)
{
//echo $question_id."--".$element_key."==".$this->form_answer[$question_id][$element_key]."<br>";
switch($this->form_answer[$question_id][$element_key])
{
case "":
$this->vetted_form_answer[$question_id][$element_key] = "";
break;
case "yes":
$this->vetted_form_answer[$question_id][$element_key] = "yes";
$this->vetted_value[$question_id][$element_key] = $current_element["question_element_value"];
break;
default:
question_error_marking ($question_id, "Illegal Entry for Question#".$question_id.". ");
}
}
//Validate the logic type
if($this->form_answer["logic_type"][$question_id] == "and")
{
$this->vetted_form_answer["logic_type"][$question_id] = "and";
$this->vetted_value["logic_type"][$question_id] = "and";
}
else
{
$this->vetted_form_answer["logic_type"][$question_id] = "or";
$this->vetted_value["logic_type"][$question_id] = "or";
}
}
function validate_date_question_answer ($q_attributes)
{
//Make a new set of question atrributes for the less than part of the query question
$low_attri = $q_attributes;
$low_attri["question_id"] = $low_attri["question_id"]."_low";
$low_attri["question_required"] = "no";
//Make an array set for the question validation, which will strip off the added array level....
$low_attributes[$low_attri["question_id"]] = $low_attri;
$low_validation = new Questions_answers_validation ($low_attributes, "", $this->form_answer);
//Merge the changes into our form answers and set the vetted value
$this->vetted_form_answer = array_merge ( $this->vetted_form_answer, $low_validation->vetted_form_answer );
$this->vetted_value[$q_attributes["question_id"]]["range_bottom"] = $low_validation->vetted_value[$low_attri["question_id"]];
//echo "low vetted value is: ".$this->vetted_value[$q_attributes["question_id"]]["range_bottom"]."<p>";
$high_attri = $q_attributes;
$high_attri["question_id"] = $high_attri["question_id"]."_high";
$high_attri["question_required"] = "no";
$high_attributes[$high_attri["question_id"]] = $high_attri;
$high_validation = new Questions_answers_validation ($high_attributes, "", $this->form_answer);
$this->vetted_form_answer = array_merge ( $this->vetted_form_answer, $high_validation->vetted_form_answer);
$this->vetted_value = array_merge ( $this->vetted_value, $high_validation->vetted_value );
$this->vetted_value[$q_attributes["question_id"]]["range_top"] = $high_validation->vetted_value[$high_attri["question_id"]];
//echo "high vetted value is: ".$this->vetted_value[$q_attributes["question_id"]]["range_top"]."<p>";
}
function count_clients_query ()
{
GLOBAL $debug;
$sql_begin = "SELECT DISTINCT(clients.client_id) FROM clients ";
$final_sql = $sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$this->sql_where_or;
//$this->sql_where_and_date.$this->sql_where_and_organizations.
//$debug .= "<p>Client Count SQL<br/>".$final_sql;
//$debug .= "<p>Client Count SQL Template<br/>".$sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_date_template. "{report_profile".$this->hh_profile_join_count."}{client_rpt_profile".$this->client_profile_join_count."}".$this->sql_where_or;
//echo "Final SQL is: ".$final_sql."<p>";
$this->client_ids_query_result[0] = run_query ($final_sql, "Running query SQL");
$this->client_ids_query_result[1] = num_rows ( $this->client_ids_query_result[0] );
//echo "Client count is: <b>".$client_count."</b><p>";
//echo "Clients selected in client count:<br>";
//for ( $w = 0; $w < $client_count; $w++ ) {
// $id = fetch_array ($result, "pull id", $w);
//echo $id["client_id"]." -is<br>";
//}
return $client_ids_query_result;
}
function count_clients ()
{
if ( !$this->client_ids_query_result )
{
$this->count_clients_query ();
}
return $this->client_ids_query_result[1];
/*
$sql_begin = "SELECT COUNT(clients.client_id) FROM clients ";
$final_sql = $sql_begin.$this->sql_from." WHERE
".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$this->sql_where_or;
$z = run_query ($final_sql, "Running query SQL");
$x = fetch_result ( $z, 'cli count' );
return $x;
*/
}
function clients_list ()
{
if ( !$this->client_ids_query_result ) {
$this->count_clients_query ();
}
$client_profile = new Client_profile();
$ht .= "<table><tr><td class=\"generictabletop\">List of clients matching query. A maximum of 50 clients will be shown.</td></tr></table>";
//If the list is too long, limit it to 30 (or why not 50).
if ( $this->client_ids_query_result[1] > 50 )
{
$list_length = 50;
}
else
{
$list_length = $this->client_ids_query_result[1];
}
for ( $i = 0; $i < $list_length; $i++ )
{
$client_id = fetch_array ( $this->client_ids_query_result[0], 'Client id list', $i );
$client_info[$i] = $client_profile->pull_client_profile_array ( $client_id[0] );
$rel = new Relationships ( $client_id[0] );
if ( $client_info[$i]['client_id'] > 0 )
{
$relationship = $rel->find_relationship_to_lead ( $client_info[$i]['client_id'], $client_id[0] );
$client_info[$i]['client_relationship_to_lead'] = $relationship;
}
else //No one in repomdent role, so make the new person the head of household
{
$client_info[$i]['client_relationship_to_lead'] = 'head of household'; //bugbug: need to make sure we're not overwriting to turn a child into a hoh...
}
// echo $i." ";
}
$ht .= $client_profile->display_client_profile_table ($client_info, '', 'queried_clients');
return $ht;
}
function count_surveys ()
{
GLOBAL $debug;
$sql_begin = "SELECT DISTINCT(client_rpt_profile1.client_rpt_id) FROM client_rpt_profile AS client_rpt_profile1 ";
//$final_sql = $sql_begin.$this->sql_from." WHERE
$final_sql = $sql_begin.$this->sql_from_survey." WHERE
".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$this->sql_where_or;
//$this->sql_where_and_date.$this->sql_where_and_organizations.
$debug .= "<p>Survey Count SQL<br/>".$final_sql;
$debug .= "<p>Survey Count SQL Template<br/>".$sql_begin.$this->sql_from." WHERE
".$this->sql_where_and.$this->sql_where_and_date_template.
"{report_profile".$this->hh_profile_join_count."}{client_rpt_profile".$this->client_profile_join_count."}".$this->sql_where_or;
//echo "Final SQL is: ".$final_sql."<p>";
$result = run_query ($final_sql, "Running query SQL");
$client_count = num_rows ( $result );
//echo "Client count is: <b>".$client_count."</b><p>";
//echo "Clients selected in client count:<br>";
//for ( $w = 0; $w < $client_count; $w++ ) {
// $id = fetch_array ($result, "pull id", $w);
//echo $id["client_id"]." -is<br>";
//}
return $client_count;
}
function count_single_clients ()
{
GLOBAL $debug;
//$sql_single = "AND (SELECT COUNT(hh_report_id) FROM client_id WHERE
//report_relationship.hh_report_id = report_relationship1.hh_report_id ) < //2 ";
$sql_single = "AND (SELECT COUNT(hh_report_id) FROM report_relationship WHERE report_relationship.hh_report_id = report_relationship1.hh_report_id ) < 2 ";
$sql_begin = "SELECT DISTINCT(clients.client_id) FROM clients ";
$final_sql = $sql_begin.$this->sql_from." WHERE
".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$this->sql_where_and_hh_count.$sql_single.$this->sql_where_or;
$debug .= "<p>Single Count SQL<br/>".$final_sql."<p>";
$debug .= "<p>Single Count SQL Template<br/>".$sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_date_template."{report_profile".$this->hh_profile_join_count."}{client_rpt_profile".$this->client_profile_join_count."}".$this->sql_where_and_hh_count.$sql_single.$this->sql_where_or;
//echo "Final household count SQL is: ".$final_sql."<p>";
$result = run_query ($final_sql, "Running query SQL");
$single_count = num_rows ( $result );
//echo "Household count is: <b>".$hh_count."</b><p>";
//echo "Clients selected in household count:<br>";
//for ( $w = 0; $w < $single_count; $w++ ) {
// $id = fetch_array ($result, "pull id", $w);
// echo $id["client_id"]." -is<br>";
//}
return $single_count;
}
function count_individuals_in_families ()
{
GLOBAL $debug;
$sql_individuals_in_families = "AND (SELECT COUNT(hh_report_id) FROM report_relationship WHERE
report_relationship.hh_report_id = report_relationship1.hh_report_id ) > 1 ";
$sql_begin = "SELECT DISTINCT(clients.client_id) FROM clients ";
$final_sql = $sql_begin.$this->sql_from." WHERE
".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$sql_individuals_in_families.$this->sql_where_or;
//$this->sql_where_and_date.$this->sql_where_and_organizations.
$debug .= "<p>Count clients in families SQL<br/>".$final_sql;
$debug .= "<p>Count clients in families SQL Template<br/>".$sql_begin.$this->sql_from." WHERE
".$this->sql_where_and.$this->sql_where_and_date_template.
"{report_profile".$this->hh_profile_join_count."}{client_rpt_profile".$this->client_profile_join_count."}".$sql_individuals_in_families.$this->sql_where_or;
//echo "Final SQL is: ".$final_sql."<p>";
$result = run_query ($final_sql, "Running query SQL");
$client_in_family_count = num_rows ( $result );
//echo "Client count is: <b>".$client_count."</b><p>";
//echo "Clients selected in client count:<br>";
for ( $w = 0; $w < $client_count; $w++ )
{
$id = fetch_array ($result, "pull id", $w);
//echo $id["client_id"]." -is<br>";
}
return $client_in_family_count;
}
//todo: many of these queries don't need to be run, rather we should just analyse the result set already returned...
function count_households ()
{
GLOBAL $debug;
$sql_begin = "SELECT DISTINCT(clients.client_id) FROM clients ";
//$sql_begin = "SELECT DISTINCT(report_answers1.hh_report_id) FROM clients ";
$final_sql = $sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$this->sql_where_and_hh_count.$this->sql_where_or;
$debug .= "<p>Household Count SQL<br/>".$final_sql."<p>";
$debug .= "<p>Household Count SQL Template<br/>".$sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_date_template."{report_profile".$this->hh_profile_join_count."}{client_rpt_profile".$this->client_profile_join_count."}".$this->sql_where_and_hh_count.$this->sql_where_or;
//echo "Final household count SQL is: ".$final_sql."<p>";
$result = run_query ($final_sql, "Running query SQL");
$hh_count = num_rows ( $result );
//echo "Household count is: <b>".$hh_count."</b><p>";
//echo "Clients selected in household count:<br>";
for ( $w = 0; $w < $hh_count; $w++ )
{
$id = fetch_array ($result, "pull id", $w);
//echo $id["client_id"]." -is<br>";
}
return $hh_count;
}
//todo bugbug: what is this function supposed to truly be returning as it is actually looking at all hh forms not just household_service forms...
function count_household_service_days ()
{
GLOBAL $debug;
$en = new Encryption();
$service_seconds = 0;
$current_join_count = 1;
//for ( $current_join_count = 1; $current_join_count <= $this->hh_profile_join_count; $current_join_count++ )
//{
//echo "Loop #".$current_join_count."<p/>";
//$sql_begin = "SELECT (clients.client_id), * FROM clients ";
//$sql_begin = "SELECT DISTINCT(report_profile".$current_join_count.".hh_report_id), report_profile".$current_join_count.".report_date_end, report_profile".$current_join_count.".report_date_begin FROM clients ";
//$final_sqlA = $sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$this->sql_where_and_hh_count.$this->sql_where_or;
$sql_begin = "SELECT DISTINCT(report_profile".$current_join_count.".hh_report_id), report_profile".$current_join_count.".report_date_end,report_profile".$current_join_count.".report_date_begin FROM clients ";
//$final_sqlA = $sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$this->sql_where_and_hh_count.$this->sql_where_or;
$final_sqlA = $sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$this->sql_where_or;
$debug .= "<p>HH Service Days Count SQL<br/>".$final_sqlA."</p>";
$debug .= "<p>HH Service Days Count SQL Template<br/>".
$sql_begin.$this->sql_from." WHERE ".
$this->sql_where_and.$this->sql_where_and_date_template."{report_profile".
$this->hh_profile_join_count."}{client_rpt_profile".$this->client_profile_join_count."}".
$this->sql_where_and_hh_count.$this->sql_where_or;
$resultA = run_query ($final_sqlA, "Running query SQL");
$row_count = num_rows ($resultA);
echo $final_sqlA;
echo "<br>The number of household reports is: ".$row_count."<p>";
for ( $i = 0; $i < $row_count; $i++ )
{
$report_id_array = fetch_array ( $resultA, "Fetching date sql", $i );
$final_sqlB = "SELECT DISTINCT(report_relationship.client_id) FROM report_relationship WHERE
report_relationship.hh_report_id = '".$report_id_array['hh_report_id']."' ";
$resultB = run_query ($final_sqlB, "Running query SQL");
$number_of_clients_in_this_hh = num_rows ($resultB);
$report_id_array['report_date_begin'] = date_encrypted_translate_index_to_unix_time ( $report_id_array['report_date_begin'] );
if ($report_id_array['report_date_end'] == 0)
$report_id_array['report_date_end'] = time();
else
$report_id_array['report_date_end'] = date_encrypted_translate_index_to_unix_time ( $report_id_array['report_date_end'] );
//echo '<br>Begin Date: '.$report_id_array['report_date_begin'];
//echo '<br>End Date: '.$report_id_array['report_date_end'];
$service_seconds_intermediate = ( $report_id_array['report_date_end'] - $report_id_array['report_date_begin'] );
$service_seconds = $service_seconds + $service_seconds_intermediate;
$service_days_intermediate = intval($service_seconds_intermediate / 86400) * $number_of_clients_in_this_hh;
$service_days = $service_days + $service_days_intermediate;
echo "The report number is: ".$report_id_array['hh_report_id']." (".$report_id_array['report_date_end']."-".$report_id_array['report_date_begin']."=".$service_seconds." or ".intval($service_seconds / 86400)." days)<p>";
//}
}
//echo "Service Seconds A is: ".$service_seconds."<p/>";
//echo "Service Seconds B is: ".$service_seconds."<p/>";
//Seconds in a day is: 86400
//Done above now... $service_days = intval($service_seconds / 86400);
echo "Household service seconds is: ".$service_seconds."<br>";
echo "Household service days is: ".$service_days."<br>";
return $service_days;
}
/*
function count_household_service_days () {
GLOBAL $debug;
for ( $current_join_count = 1; $current_join_count <= $this->hh_profile_join_count;
$current_join_count++ ) {
//echo "Loop #".$current_join_count."<p/>";
$sql_begin = "SELECT SUM(( report_profile".$current_join_count.".report_date_end -
report_profile".$current_join_count.".report_date_begin ) *
(SELECT COUNT( report_relationship.client_id ) FROM report_relationship WHERE report_relationship.hh_report_id =
report_profile".$current_join_count.".hh_report_id ))
FROM clients ";
//$final_sqlA = $sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$this->sql_where_and_hh_count.$this->sql_where_or;
$final_sqlA = $sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$this->sql_where_or;
//echo $final_sqlA."<p>";
$debug .= "<p>HH Service Days Count SQL<br/>".$final_sqlA."</p>";
$debug .= "<p>HH Service Days Count SQL Template<br/>".
$sql_begin.$this->sql_from." WHERE ". $this->sql_where_and.$this->sql_where_and_date_template."{report_profile".$this->hh_profile_join_count."}{client_rpt_profile".$this->client_profile_join_count."}". $this->sql_where_and_hh_count.$this->sql_where_or;
$resultB = run_query ($final_sqlA, "Running query SQL");
$resultB = run_query ($final_sqlA, 'Running query SQL H service days');
$service_seconds = fetch_result ( $resultB, 'Seconds of hh service' );
}
echo "Service Seconds B is: ".$service_seconds."<p/>";
//Seconds in a day is: 86400
$service_days = intval($service_seconds / 86400);
//echo "Household service seconds is: ".$service_days."<br>";
//echo "Household service days is: ".$service_days."<br>";
return $service_days;
}
*/
function table_averages ()
{
foreach ( $this->quest AS $cur_qst )
{
//echo $cur_qst["question_type"]."mmm<p/>";
if ( $cur_qst["question_type"] == "table" )
{
$sql_begin = "SELECT AVG(client_rpt_answers.client_rpt_answer_int) FROM client_rpt_answers INNER JOIN
client_rpt_profile AS client_rpt_profile1 ON (client_rpt_answers.client_rpt_id =
client_rpt_profile1.client_rpt_id) ";
$final_sql = $sql_begin.$this->sql_from_survey." WHERE
".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$this->sql_where_or;
$sql = "SELECT AVG(client_rpt_answer_int) FROM client_rpt_answers INNER JOIN
client_rpt_profile AS client_rpt_profile1 ON (client_rpt_answers.client_rpt_id =
client_rpt_profile1.client_rpt_id) ".$this->sql_where_and_date.$this->sql_where_and_organizations;
// $sql = $this->sql_from_survey."WHERE ".$this->sql_where_and.$this->sql_where_or.$this->sql_where_and_date.$this->sql_where_and_organizations;
//$sql["sql_from"] = $this->sql_from;
//$sql["sql_where_and"] =
$temp_class = new Display_table ($this->quest[ $cur_qst["question_id"] ], $this->quest_elements[ $cur_qst["question_id"] ], $this->vetted_form_answer,
"", "", $final_sql);
//display_value($this->vetted_form_answer[$question["question_id"]]);
$return_html .= $temp_class->final_html;
}
}
return $return_html;
}
function text_answers_listing ()
{
$final_html .= "<table>";
foreach ( $this->quest AS $cur_qst )
{
//echo $cur_qst["question_type"]."mmm<p/>";
if ( $cur_qst["question_type"] == "textarea" || $cur_qst["question_type"] == "text" )
{
preg_match_all( '/\d+/', $cur_qst["question_id"], $quest_id);
$question_id = $quest_id[0][0];
//display_value ($cur_qst);
for ($e = 0; $e < $this->client_answers_join_count; $e++)
{
if ($e != 0) {$select_text .= ", ";}
$counter = $this->client_answers_join_count + $e;
$select_text .= "client_rpt_answer".$counter;
$from_text .= "INNER JOIN client_rpt_answers AS client_rpt_answers".$counter." ON
(client_rpt_profile1.client_rpt_id = client_rpt_answers".$counter.".client_rpt_id)";
$where_and_text .= "AND client_rpt_answer".$counter.".client_rpt_question_id =
'".$question_id."'";
}
//$sql = "SELECT client_rpt_answer FROM client_rpt_answers, ".$this->sql_from_survey." INNER JOIN client_rpt_profile AS client_rpt_profile1 ON (client_rpt_answers.client_rpt_id = client_rpt_profile1.client_rpt_id) WHERE client_rpt_question_id = '".$question_id."' AND ".$this->sql_where_and.$this->sql_where_or.$this->sql_where_and_date.$this->sql_where_and_organizations;
$sql = "SELECT * FROM ".$this->sql_from_survey."
WHERE client_rpt_question_id = '".$question_id."' AND ".$this->sql_where_and.$this->sql_where_or.$this->sql_where_and_date.$this->sql_where_and_organizations;
echo $sql."<p>";
$final_html .= "<tr><td><b>".$cur_qst["question_title"]."</b></td></tr>";
//$text = $sql;
$result = run_query ($sql, "Run query to text answer" );
$response_array = run_query_return_array ($sql, "Pulling text answer" );
if ( is_array ( $response_array ) )
{
foreach ( $response_array AS $cur_rsp )
{
$final_html .= "<tr><td class=\"smaller75\">".$cur_rsp[0]."<br/>__________________<p/></td></tr>";
}
}
else
{
$final_html .= "<tr><td>NO RESPONSES</td></tr>";
}
}
}
$final_html .= "</table>";
return $final_html;
}
/*
function cccount_household_service_days () {
//echo $this->hh_service_days_count_select_field_sql."<p>";
//Pull out the ids of reports
$sql_begin = "SELECT ".$this->hh_service_days_count_select_field_sql." FROM clients ";
$final_sqlA = $sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_hh_count.$this->sql_where_or;
echo "Final household service days report count SQL is: <br> ".$final_sqlA."<p>";
$resultB = run_query ($final_sqlA, "Running query SQL");
$row_count = num_rows ( $resultB );
echo "count is: ".$report_num."<p>";
//Loop through the rows
for ( $w = 0; $w < $row_count; $w++ ) {
$relationship_sub_tables_array = fetch_array ($resultB, "Pulling the relations sub join table names", $w);
foreach ( $relationship_sub_tables_array AS $current_hh_report_id ) {
$report_num[$current_hh_report_id] = $current_hh_report_id;
//echo "Report id: ".$current_hh_report_id."<br>";
}
}
if (is_array ( $report_num ) ) {
foreach ( $report_num AS $hh_report_id ) {
if ($t > 0) {
$find_report_id_sql .= "OR ";
}
$find_report_id_sql .= "hh_report_id = '".$hh_report_id."' ";
$t++;
}
}
if ($find_report_id_sql) {
$where_sql = "WHERE ";
}
$sqlc = "SELECT DISTINCT(hh_report_id) FROM report_relationship ".$where_sql.$find_report_id_sql;
echo "The SQL that will pull out all the clients tht have relationship to reports:<br> ".$sqlc."<p>";
$resultc = run_query ($sqlc, "Pulling all the clients that have reports");
$client_count = num_rows($resultc);
//Loop through the relevant reports to pull out how many clients there are, then multiply the days calculation
for ( $wj = 0; $wj < $client_count; $wj++ ) {
$client_id_array = fetch_array ($resultc, "pull id", $wj);
//echo $hh_report_profile["report_relationship.client_id"]." -is<br>";
$sqlb = "SELECT hh_report_id, report_date_end, report_date_begin FROM report_profile
WHERE hh_report_id = '".$client_id_array["hh_report_id"]."'";
$date_range_result = run_query ($sqlb, "Pulling clients in count of household service days");
$date_range_array = fetch_array ( $date_range_result, "Pulling begin and end", 0 );
echo $date_range_array["report_date_end"]." -- ".$date_range_array["report_date_begin"]."<br>";
$service_seconds = $service_seconds + ( $date_range_array["report_date_end"] - $date_range_array["report_date_begin"] );
}
//Seconds in a day is: 86400
$service_days = $service_seconds / 86400;
//echo "Household service seconds is: ".$service_days."<br>";
//echo "Household service days is: ".$service_days."<br>";
return $service_days;
}
*/
function count_client_service_days () {
/*
//$this->client_profile_join_count
for ( $current_join_count = 1; $current_join_count <= $this->client_profile_join_count;
$current_join_count++ ) {
//$sql_begin = "SELECT (clients.client_id), * FROM clients ";
$sql_begin = "SELECT DISTINCT(client_rpt_profile".$current_join_count.".client_rpt_id), client_rpt_profile".$current_join_count.".report_date_endc, client_rpt_profile".$current_join_count.".report_date_beginc FROM clients ";
$final_sqlA = $sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_date.$this->sql_where_and_organizations.$this->sql_where_or;
//echo $final_sqlA."<p>";
$resultB = run_query ($final_sqlA, "Running query SQL");
$row_count = num_rows ($resultB);
for ( $i = 0; $i < $row_count; $i++ ) {
$date_range_array = fetch_array ( $resultB, "Fetching date sql", $i );
$sqlb = "SELECT * FROM client_rpt_profile WHERE client_rpt_id =
'".$date_range_array["client_rpt_id"]."'";
$cnt_count_result = run_query ($sqlb, "Count client report service days" );
$clnt_count = num_rows ($cnt_count_result);
$service_seconds = $service_seconds + ( $date_range_array["report_date_endc"] - $date_range_array["report_date_beginc"] ) ;
}
}
//Seconds in a day is: 86400
$service_days = intval($service_seconds / 86400);
//echo "Household service seconds is: ".$service_days."<br>";
//echo "Household service days is: ".$service_days."<br>";
*/
//$sql_begin = "SELECT DISTINCT(client_rpt_profile".$current_join_count.".client_rpt_id), client_rpt_profile".$current_join_count.".report_date_endc, client_rpt_profile".$current_join_count.".report_date_beginc FROM clients ";
return $service_days;
}
/*
function cccount_client_service_days () {
$sql_begin = "SELECT ".$this->client_service_days_count_select_field_sql." FROM clients ";
$final_sqlA = $sql_begin.$this->sql_from." WHERE ".$this->sql_where_and.$this->sql_where_and_client_count.$this->sql_where_or;
//echo $final_sqlA."<p>";
$resultB = run_query ($final_sqlA, "Running query SQL");
$row_count = num_rows ( $resultB );
//Loop through the rows
for ( $w = 0; $w < $row_count; $w++ ) {
$relationship_sub_tables_array = fetch_array ($resultB, "Pulling the relations sub join table names", $w);
foreach ( $relationship_sub_tables_array AS $current_hh_report_id ) {
$report_num[$current_hh_report_id] = $current_hh_report_id;
//echo "Report id: ".$current_hh_report_id."<br>";
}
}
if (is_array ( $report_num ) ) {
$v = count ($hh_report_id);
for ($n=0; $n < $v; $n++) {
//foreach ( $report_num AS $hh_report_id ) {
if ($n == 0) {
$find_report_id_sql .= "OR ";
}
$find_report_id_sql .= "(client_rpt_id = '".$report_num[$n]."') ";
}
}
if ($find_report_id_sql) {
$where_sql = "WHERE ";
}
$sqlk = "SELECT DISTINCT(client_rpt_id), report_date_endc, report_date_beginc FROM client_rpt_profile ".$where_sql.$find_report_id_sql.$find_report_id_sql;
$result = run_query ($sqlk, "Running query SQL");
echo "Client service days count sql: ".$sqlk."<br>";
$client_report_count = num_rows ( $result );
//echo "Client report count is: <b>".$client_report_count."</b><p>";
//echo "Household reports selected in service days count:<br>";
//Loop through the relevant reports to pull out how many clients there are, then multiply the days calculation
for ( $w = 0; $w < $client_report_count; $w++ ) {
$client_report_profile = fetch_array ($result, "pull dates", $w);
$service_seconds = $service_seconds + ( $client_report_profile["report_date_endc"]
- $client_report_profile["report_date_beginc"] );
//echo "Difference is: ";
//$tot = $client_report_profile["report_date_end"] - $client_report_profile["report_date_begin"];
//$service_seconds = $service_seconds + $tot;
//echo $tot."<p>";
}
//Seconds in a day is: 86400
$client_service_days = $service_seconds / 86400;
//echo "Client service seconds is: ".$service_seconds."<br>";
//echo "Client service days is: ".$client_service_days."<br>";
return $client_service_days;
}
*/
function build_household_sql ( $report_name, $begin_unix_date, $end_unix_date )
{
$en = new Encryption();
foreach ( $this->raw_quest[$report_name] AS $currnt_qu )
{
//echo "Modifed id is: ".$modified_id."<p>";
$modified_id = $report_name."_".$currnt_qu["question_id"];
$real_id = $currnt_qu['question_id'];
/*
//Find out if a returned array (such as a number range array) actaully contains an answer..
$temp_ans = $this->vetted_value[$modified_id];
//..by sorting the array, so that the largetes value is inindex "0"
if ( is_array ( $temp_ans ) ) {
rsort ( $temp_ans );
}
else {
$temp_ans[0] = 5;
}
//...if there is an answer, and index 0 contains a value
*/
//if( $this->vetted_value[$modified_id] && $temp_ans[0] ) {
if( $this->vetted_value[$modified_id] ) {
//If there is an answer, note it, so if there is none we can "build_hh_profile_join_and_date_sql" once later
//echo "There is an answer for: ".$modified_id." N/A for numbers of dates<p>";
$this->build_hh_profile_join_and_date_sql ($report_name, $begin_unix_date, $end_unix_date );
//Loop through the answers if a checkbox, radio answer, or dropdown
//if ( is_array($this->vetted_value[$modified_id]) && $currnt_qu["question_type"] != "table" ) {
if ( is_array($this->vetted_value[$modified_id] )
&& ( $currnt_qu["question_type"] == "checkboxes"
|| $currnt_qu["question_type"] == "radio"
|| $currnt_qu["question_type"] == "dropdown" ) )
{
$yes_an_answer++;
//display_value($this->vetted_value[$modified_id]);
//echo "It is an answer with an array<p>";
foreach( $this->vetted_value[$modified_id] AS $array_answer )
{
if ( $currnt_qu['question_encrypted'] == 1 )
{
$array_answer = addslashes( $en->encrypt_data( $array_answer ) );
}
//Define the logic type if using "AND" logic...
if($this->vetted_value["logic_type"][$modified_id] == "and")
{
$this->build_join_between_hh_answers_and_profile_sql ();
$this->build_hh_array_answer_and_sql ( $real_id, $array_answer );
}
//...or if we are using "OR" logic
else
{
//Do only one join if we are doing an OR query for a question
$rr++;
if ( $rr == 1 )
{
///$this->build_join_between_client_answers_and_profile_sql ();
$this->build_join_between_hh_answers_and_profile_sql ();
}
$this->build_hh_array_answer_or_sql ($real_id, $modified_id, $array_answer);
}
}
$rr = 0;
//If we are using OR logic, close out the statement with a bracket
if( $this->vetted_value["logic_type"][$modified_id] != "and")
{
$this->sql_where_or .= ") ) ";
}
}
///////////Number questions
elseif ( $currnt_qu["question_type"] == "number" || $currnt_qu["question_type"] == "date" )
{
////Join the hh answers to the hh profile, only if there is a actual answer in one of the two ranges
if ($this->vetted_value[$modified_id]["range_bottom"] || $this->vetted_value[$modified_id]["range_top"])
{
echo "A number or date question question<p>";
$this->build_join_between_hh_answers_and_profile_sql ();
$this->sql_where_and .= "AND report_answers".$this->hh_answers_join_count.".question_id = '".$real_id."' ";
}
if ($this->vetted_value[$modified_id]["range_bottom"])
{
$this->sql_where_and .= "AND report_answers".$this->hh_answers_join_count.".report_answer_int >
'".$this->vetted_value[$modified_id]["range_bottom"]."' ";
}
if ($this->vetted_value[$modified_id]["range_top"])
{
$this->sql_where_and .= "AND report_answers".$this->hh_answers_join_count.".report_answer_int <
'".$this->vetted_value[$modified_id]["range_top"]."' ";
}
}
elseif ( $currnt_qu["question_type"] == "table" )
{
//display_value ( $this->vetted_value[$modified_id] );
$yes_an_answer++;
foreach ( $this->vetted_value[$modified_id] AS $key_x => $array_answer_x )
{
//////////////////Loop through table answers
foreach ( $array_answer_x AS $key_y => $array_answer_range )
{
$this->build_join_between_hh_answers_and_profile_sql ();
//If a query on the total, send elsewhere
//echo "The quest element type is: ".$this->quest_elements[$modified_id]["x"][$key_x]["axis_special_type"]."<p>";
if ( $this->quest_elements[$modified_id]["y"][$key_y]["axis_special_type"] == "total" ) {
$this->build_hh_table_total_query_sql ($real_id, "x", $key_x, $array_answer_range);
}
elseif ( $this->quest_elements[$modified_id]["x"][$key_x]["axis_special_type"] == "total" )
{
$this->build_hh_table_total_query_sql ($real_id, "y", $key_y, $array_answer_range);
}
else
{
$this->sql_where_and .= "AND report_answers".$this->hh_answers_join_count.".question_id = '".$real_id."'
AND report_answers".$this->hh_answers_join_count.".axis_x = '".$key_x."'
AND report_answers".$this->hh_answers_join_count.".axis_y = '".$key_y."' ";
if ( $array_answer_range["range_bottom"] )
{
//echo " x: ".$key_x."- y:".$key_y." answer bottom= ".$array_answer_range["range_bottom"]."<br>";
$this->sql_where_and .= "AND report_answers".$this->hh_answers_join_count.".report_answer_int >
'".$array_answer_range["range_bottom"]."' ";
}
if ( $array_answer_range["range_top"] )
{
$this->sql_where_and .= "AND report_answers".$this->hh_answers_join_count.".report_answer_int <
'".$array_answer_range["range_top"]."' ";
}
}
}
}
}
}
}
//If there were no answered questions, make at least one join for this report
if ( !$yes_an_answer )
{
$this->build_hh_profile_join_and_date_sql ($report_name, $begin_unix_date, $end_unix_date );
}
}
function build_hh_table_total_query_sql ($question_id, $x_or_y, $axis_key, $array_answer_range) {
//code here to figure out table totals
if ( $array_answer_range["range_bottom"] && $array_answer_range["range_top"] )
{
$comp_sql = "BETWEEN '".$array_answer_range["range_bottom"]."' AND '".$array_answer_range["range_top"]."' ";
}
elseif ( $array_answer_range["range_bottom"] )
{
$comp_sql = " > '".$array_answer_range["range_bottom"]."' ";
}
elseif ( $array_answer_range["range_top"] )
{
$comp_sql = " < '".$array_answer_range["range_top"]."' ";
}
$this->sql_where_and .= "
AND (
SELECT SUM(report_answers.report_answer_int) FROM report_answers
WHERE report_answers.question_id = '".$question_id."'
AND report_answers.axis_".$x_or_y." = '".$axis_key."'
AND report_answers.hh_report_id = report_profile".$this->hh_profile_join_count.".hh_report_id
)
".$comp_sql." ";
}
function build_client_sql ( $report_name, $begin_unix_date, $end_unix_date )
{
$en = new Encryption();
$this->build_client_profile_join_and_date_sql ($report_name, $begin_unix_date, $end_unix_date );
foreach ( $this->raw_quest[$report_name] AS $currnt_qu )
{
//echo "Modifed id is: ".$modified_id."<p>";
$modified_id = $report_name."_".$currnt_qu["question_id"];
$real_id = $currnt_qu["question_id"];
//If the answer is answer
//if( $this->vetted_value[$modified_id] && $temp_ans[0] ) {
if ( $this->vetted_value[$modified_id] )
{
//Loop through the answers if a checkbox, radio answer, or dropdown
if ( $this->vetted_value[$modified_id] && ( $currnt_qu["question_type"] == "radio"
|| $currnt_qu["question_type"] == "checkboxes"
|| $currnt_qu["question_type"] == "dropdown" ) )
{
$yes_an_answer++;
foreach($this->vetted_value[$modified_id] AS $array_answer)
{
if ( $currnt_qu['question_encrypted'] == 1 )
{
$array_answer = addslashes( $en->encrypt_data( $array_answer ) );
}
//Define the logic type if using "AND" logic...
if ( $this->vetted_value["logic_type"][$modified_id] == 'and' )
{
$this->build_join_between_client_answers_and_profile_sql ();
$this->build_client_array_answer_and_sql ($real_id, $array_answer);
}
//...or if we are using "OR" logic
else
{
//Do only one join if we are doing an OR query for a question
$rr++;
if ( $rr == 1 ) {
$this->build_join_between_client_answers_and_profile_sql ();
}
$this->build_client_array_answer_or_sql ($real_id, $modified_id, $array_answer);
}
}
$rr = 0;
//If we are using OR logic, close out the statement with a bracket
if( $this->vetted_value["logic_type"][$modified_id] != "and")
{
$this->sql_where_or .= ") ) ";
}
}
///////////Number questions
elseif ( $currnt_qu["question_type"] == "number" || $currnt_qu["question_type"] == "date" )
{
if ( $this->vetted_value[$modified_id]["range_bottom"] || $this->vetted_value[$modified_id]["range_top"] )
{
//echo "A number question<p>";
$yes_an_answer++;
$this->build_join_between_client_answers_and_profile_sql ();
}
if ($this->vetted_value[$modified_id]["range_bottom"])
{
$this->sql_where_and .= "AND client_rpt_answers".$this->client_answers_join_count.".client_rpt_question_id = '".$real_id."'
AND client_rpt_answers".$this->client_answers_join_count.".client_rpt_answer_int >
'".$this->vetted_value[$modified_id]["range_bottom"]."' ";
//$this->sql_where_and .= " AND client_rpt_profile.client_rpt_id =
//client_rpt_answers".$this->client_answers_join_count.".client_rpt_id ";
}
if ($this->vetted_value[$modified_id]["range_top"])
{
$this->sql_where_and .= "AND client_rpt_answers".$this->client_answers_join_count.".client_rpt_question_id = '".$real_id."'
AND client_rpt_answers".$this->client_answers_join_count.".client_rpt_answer_int <
'".$this->vetted_value[$modified_id]["range_top"]."' ";
}
}
elseif ($currnt_qu["question_type"] == "table")
{
//echo "In client table sql section, doing something";
//display_value ( $this->vetted_value[$modified_id] );
$yes_an_answer++;
foreach ( $this->vetted_value[$modified_id] AS $key_x => $array_answer_x )
{
//////////////////Loop through table answers
foreach ( $array_answer_x AS $key_y => $array_answer_range )
{
//$this->build_join_between_hh_answers_and_profile_sql ();
$this->build_join_between_client_answers_and_profile_sql ();
//If a query on the total, send elsewhere
//echo "The quest element type is: ".$this->quest_elements[$modified_id]["x"][$key_x]["axis_special_type"]."<p>";
if ( $this->quest_elements[$modified_id]["y"][$key_y]["axis_special_type"] == "total" )
{
$this->build_client_table_total_query_sql ($real_id, "x", $key_x, $array_answer_range);
}
elseif ( $this->quest_elements[$modified_id]["x"][$key_x]["axis_special_type"] == "total" )
{
$this->build_client_table_total_query_sql ($real_id, "y", $key_y, $array_answer_range);
}
else
{
$this->sql_where_and .= "AND client_rpt_answers".$this->client_answers_join_count.".client_rpt_question_id = '".$real_id."'
AND client_rpt_answers".$this->client_answers_join_count.".axis_x = '".$key_x."'
AND client_rpt_answers".$this->client_answers_join_count.".axis_y = '".$key_y."' ";
if ( $array_answer_range["range_bottom"] )
{
//echo " x: ".$key_x."- y:".$key_y." answer bottom= ".$array_answer_range["range_bottom"]."<br>";
$this->sql_where_and .= "AND client_rpt_answers".$this->client_answers_join_count.".client_rpt_answer_int >
'".$array_answer_range["range_bottom"]."' ";
}
if ( $array_answer_range["range_top"] )
{
$this->sql_where_and .= "AND client_rpt_answers".$this->client_answers_join_count.".client_rpt_answer_int <
'".$array_answer_range["range_top"]."' ";
}
}
}
}
}
}
}
//If there were no answered questions, make at least one join for this report
if ( !$yes_an_answer )
{
//$this->build_client_profile_join_and_date_sql ($report_name, $begin_unix_date, $end_unix_date );
}
}
function build_client_table_total_query_sql ($question_id, $x_or_y, $axis_key, $array_answer_range)
{
//code here to figure out table totals
if ( $array_answer_range["range_bottom"] && $array_answer_range["range_top"] )
{
$comp_sql = "BETWEEN '".$array_answer_range["range_bottom"]."' AND '".$array_answer_range["range_top"]."' ";
}
elseif ( $array_answer_range["range_bottom"] )
{
$comp_sql = " > '".$array_answer_range["range_bottom"]."' ";
}
elseif ( $array_answer_range["range_top"] )
{
$comp_sql = " < '".$array_answer_range["range_top"]."' ";
}
$this->sql_where_and .= "
AND (
SELECT SUM(client_rpt_answers.client_rpt_answer_int) FROM client_rpt_answers
WHERE client_rpt_answers.client_rpt_question_id = '".$question_id."'
AND client_rpt_answers.axis_".$x_or_y." = '".$axis_key."'
AND client_rpt_answers.client_rpt_id = client_rpt_profile".$this->client_profile_join_count.".client_rpt_id
)
".$comp_sql." ";
}
function build_hh_profile_join_and_date_sql ($report_name, $begin_unix_date, $end_unix_date )
{
//Add a second to break ties with query (i.e., both exactly the same)
//$begin_unix_date++;
//$end_unix_date++;
//Convert unix date to date index value
$begin_unix_date = date_encrypted_find_index_unix_time ( $begin_unix_date );
$end_unix_date = date_encrypted_find_index_unix_time ( $end_unix_date );
$this->hh_relationship_join_count++;
$this->sql_from .= "INNER JOIN report_relationship AS report_relationship".$this->hh_relationship_join_count."
ON (clients.client_id = report_relationship".$this->hh_relationship_join_count.".client_id) ";
//$this->sql_from_survey = "";
//Build sql to modify the query so it only counts households by counting leads
$this->sql_where_and_hh_count .= " AND report_relationship".$this->hh_relationship_join_count.".client_relationship_to_lead
LIKE 'head of household' ";
//Collect all the report_relationship table designations for use later to find report ids
if ($this->hh_relationship_join_count > 1 )
{
$this->hh_service_days_count_select_field_sql .= ", ";
}
$this->hh_service_days_count_select_field_sql .=
"report_relationship".$this->hh_relationship_join_count.".hh_report_id";
//Join the hh report profile to the hh_relationship table
$this->hh_profile_join_count++;
$this->sql_from .= "INNER JOIN report_profile AS report_profile".$this->hh_profile_join_count."
ON (report_relationship".$this->hh_relationship_join_count.".hh_report_id =
report_profile".$this->hh_profile_join_count.".hh_report_id) ";
//Finish the org selection SQL
$tmp_and_sql = str_replace ("{org_field}", "report_profile".$this->hh_profile_join_count.".report_org_id", $this->sql_included_orgs);
$this->sql_where_and_organizations .= $tmp_and_sql;
//Build (or update) the organizations tag for SQL template; format is {[field name][total joins]}
//$this->sql_where_and_organizations_hh = "{report_profile".$this->hh_profile_join_count."}";
$this->and_count++;
if ($this->and_count > 1)
{
$this->sql_where_and .= 'AND ';
}
//Only accept reports that match the name we are looking for
$this->sql_where_and .= 'report_profile'.$this->hh_profile_join_count.".report_type LIKE '".$report_name."' ";
//$this->sql_where_and
$this->sql_where_and_date .= "AND report_profile".$this->hh_profile_join_count.".report_date_begin > '".$begin_unix_date."' ";
$this->sql_where_and_date_template .= "AND report_profile".$this->hh_profile_join_count.".report_date_begin > '{date_begin}' ";
$this->sql_where_and_date .= "AND report_profile".$this->hh_profile_join_count.".report_date_begin < '".$end_unix_date."' ";
$this->sql_where_and_date_template .= "AND report_profile".$this->hh_profile_join_count.".report_date_begin < '{date_end}' ";
//Only join reports that have not been succeeded
$this->sql_where_and .= "AND report_profile".$this->hh_profile_join_count.".succeeded_by IS NULL ";
}
function build_client_profile_join_and_date_sql ($report_name, $begin_unix_date, $end_unix_date )
{
//echo "<br>build_client_profile_join_and_date_sql ($report_name, $begin_unix_date, $end_unix_date )<br>";
//$this->client_relationship_join_count++;
//$this->sql_from .= "INNER JOIN report_relationship AS report_relationship".$this->client_relationship_join_count."
// ON (clients.client_id = report_relationship".$this->hh_relationship_join_count.".client_id) ";
//Add a second to break ties with query (i.e., both exactly the same)
//$begin_unix_date++;
//$end_unix_date++;
//Convert unix date to date index value
$begin_unix_date = date_encrypted_find_index_unix_time ( $begin_unix_date );
$end_unix_date = date_encrypted_find_index_unix_time ( $end_unix_date );
//Join the hh report profile to the hh_relationship table
$this->client_profile_join_count++;
$this->sql_from .= "INNER JOIN client_rpt_profile AS client_rpt_profile".$this->client_profile_join_count."
ON (clients.client_id = client_rpt_profile".$this->client_profile_join_count.".client_id) ";
//$this->sql_from_survey .= "client_rpt_profile AS client_rpt_profile".$this->client_profile_join_count." ";
//Collect all the report_relationship table designations for use later to find report ids
if ($this->client_profile_join_count > 1 )
{
$this->client_service_days_count_select_field_sql .= ", ";
//$this->sql_from_survey .= ",";
}
$this->client_service_days_count_select_field_sql .=
"client_rpt_profile".$this->client_profile_join_count.".client_rpt_id";
//Finish the org selection SQL
$tmp_and_sql = str_replace ("{org_field}", "client_rpt_profile".$this->client_profile_join_count.".report_org_id", $this->sql_included_orgs);
$this->sql_where_and_organizations .= $tmp_and_sql;
//Build (or update) the organizations tag for SQL template; format is {[field name][total joins]}
//$this->sql_where_and_organizations_client = "{client_rpt_profile".$this->client_profile_join_count."}";
$this->and_count++;
if ($this->and_count > 1){$this->sql_where_and .= "AND ";}
//Only accept reports that match the name we are looking for
$this->sql_where_and .= "client_rpt_profile".$this->client_profile_join_count.".report_type LIKE '".$report_name."' ";
$this->sql_where_and_date .= "AND client_rpt_profile".$this->client_profile_join_count.".report_date_beginc > '".$begin_unix_date."' ";
$this->sql_where_and_date_template .= "AND client_rpt_profile".$this->client_profile_join_count.".report_date_beginc >
'{date_begin}' ";
$this->sql_where_and_date .= "AND client_rpt_profile".$this->client_profile_join_count.".report_date_beginc < '".$end_unix_date."' ";
$this->sql_where_and_date_template .= "AND client_rpt_profile".$this->client_profile_join_count.".report_date_beginc <
'{date_end}' ";
//Only join reports that have not been succeeded
$this->sql_where_and .= "AND client_rpt_profile".$this->client_profile_join_count.".succeeded_by IS NULL ";
}
function build_join_between_hh_answers_and_profile_sql ()
{
$this->hh_answers_join_count++;
$this->sql_from .= "INNER JOIN report_answers AS report_answers".$this->hh_answers_join_count." ";
$this->sql_from .= "ON (report_profile".$this->hh_profile_join_count.".hh_report_id =
report_answers".$this->hh_answers_join_count.".hh_report_id) ";
}
function build_join_between_client_answers_and_profile_sql ()
{
//Join the client answers to the client profile
$this->client_answers_join_count++;
$this->sql_from .= "INNER JOIN client_rpt_answers AS client_rpt_answers".$this->client_answers_join_count." ";
$this->sql_from .= "ON (client_rpt_profile".$this->client_profile_join_count.".client_rpt_id =
client_rpt_answers".$this->client_answers_join_count.".client_rpt_id) ";
$this->sql_from_survey .= "INNER JOIN client_rpt_answers AS client_rpt_answers".$this->client_answers_join_count." ";
$this->sql_from_survey .= "ON (client_rpt_profile".$this->client_profile_join_count.".client_rpt_id =
client_rpt_answers".$this->client_answers_join_count.".client_rpt_id) ";
}
function build_hh_array_answer_and_sql ($real_id, $array_answer)
{
$this->sql_where_and .= " AND (report_answers".$this->hh_answers_join_count.".question_id = '".$real_id."'
AND report_answers".$this->hh_answers_join_count.".report_answer LIKE '".$array_answer."') ";
}
function build_client_array_answer_and_sql ($real_id, $array_answer)
{
$this->sql_where_and .= " AND (client_rpt_answers".$this->client_answers_join_count.".client_rpt_question_id = '".$real_id."'
AND client_rpt_answers".$this->client_answers_join_count.".client_rpt_answer LIKE '".$array_answer."') ";
}
function build_hh_array_answer_or_sql ($real_id, $modified_id, $array_answer)
{
//If we are looping through a single question, add the "OR"
$this->or_count[$modified_id]++;
if ( $this->or_count[$modified_id] > 1 ){ $this->sql_where_or .= "OR "; }
//If this is a new question, put in AND
if ($modified_id != $this->last_or_question_id) {
$this->sql_where_or .= "AND ( report_answers".$this->hh_answers_join_count.".question_id = '".$real_id."' AND ( ";
}
else {
//$this->sql_where_or .= "AND ";
}
//$this->sql_where_or .= "(report_answers".$this->hh_answers_join_count.".report_question_id = '".$real_id."'
//AND report_answers".$this->hh_answers_join_count.".report_answer LIKE '".$array_answer."') ";
$this->sql_where_or .= "report_answers".$this->hh_answers_join_count.".report_answer LIKE '".$array_answer."' ";
$this->last_or_question_id = $modified_id;
}
function build_client_array_answer_or_sql ($real_id, $modified_id, $array_answer)
{
//If we are looping through a single question, add the "OR"
$this->or_count[$modified_id]++;
if ( $this->or_count[$modified_id] > 1 ){ $this->sql_where_or .= "OR "; }
//If this is a new question, put in AND
if ($modified_id != $this->last_or_question_id) {
$this->sql_where_or .= "AND ( client_rpt_answers".$this->client_answers_join_count.".client_rpt_question_id = '".$real_id."'
AND (";
}
else
{
//$this->sql_where_or .= "AND ";
}
$this->sql_where_or .= " client_rpt_answers".$this->client_answers_join_count.".client_rpt_answer LIKE '".$array_answer."' ";
$this->last_or_question_id = $modified_id;
}
function build_included_orgs_query_sql ( $form_answer )
{
if( $this->related_orgs_array )
{
$or_count = 0;
foreach( $this->related_orgs_array AS $org_id => $org_name )
{
if($form_answer["included_orgs"][$org_id] == "yes" )
{
$or_count++;
if ($or_count > 1) {
$this->sql_included_orgs .= "OR";
}
else
{
$this->sql_included_orgs .= "AND ( ";
}
$this->sql_included_orgs .= " {org_field} = '".$org_id."' ";
}
}
if ($or_count > 0){$this->sql_included_orgs .= " ) ";}
}
}
}
function display_report_options ($report_type, $title)
{
$client_type_report = pull_names_of_reports ($report_type);
if ( is_array ( $client_type_report ) )
{
$ht = "<table><tr><td class=\"generictabletop\" colspan=\"2\">".$title."</td></tr>";
foreach ($client_type_report AS $current_clt_rpt )
{
$ht .= "<tr><td>".$current_clt_rpt['report_title']."</td>";
$ht .= "<td><input type=\"checkbox\" name=\"form_answer[".$current_clt_rpt['report_type_id']."]\" value=\"yes\"></td></tr>";
}
$ht .= "</table>";
}
return $ht;
}
function validate_report_selection ($report_type, $form_answer)
{
$client_type_report = pull_names_of_reports ($report_type);
if( $client_type_report )
{
foreach ($client_type_report AS $current_clt_rpt )
{
if ( $form_answer[$current_clt_rpt["report_type_id"]] == "yes" )
{
$vetted[$current_clt_rpt["report_type_id"]] = "yes";
}
}
}
//display_value($vetted);
return $vetted;
}
?>