<?php
//*Client Data System, Copyright (C) 2000, 2001 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_build
{
var $query_build_questions;
var $query_build_question_elements;
var $query_build_questionsB;
function define_query_build_questions ()
{
$this->query_build_questions["query_title"] = array (
"question_id" => "query_title", "question_title" => "Query name", "question_type" => "text",
"question_owner_group_id" => "1", "question_owner_org_id" => "1", "question_field_size" => "60",
"question_range_bottom" => "0", "question_range_top" => "0", "question_display_order" => "1019",
"question_required" => "yes", "question_default_value" => $vetted_form_answer["query_title"],
"question_visible" => "yes", "question_ecma" => "", "question_display_size" => "45");
//$this->query_build_questionsB["query_sql"] = array (
$this->query_build_questions["query_sql"] = array (
"question_id" => "query_sql", "question_title" => "Query SQL", "question_type" => "textarea",
"question_owner_group_id" => "1", "question_owner_org_id" => "1", "question_field_size" => "20",
"question_range_bottom" => "0", "question_range_top" => "0", "question_display_order" => "1019",
"question_required" => "yes", "question_default_value" => $vetted_form_answer["query_name"],
"question_visible" => "yes", "question_ecma" => "", "question_display_size" => "90",
"question_special_attribute" => "4000");
$this->query_build_questions["query_type"] = array (
"question_id" => "query_type", "question_title" => "Query Type", "question_type" => "radio",
"question_owner_group_id" => "1", "question_owner_org_id" => "1", "question_field_size" => "0",
"question_range_bottom" => "0", "question_range_top" => "0", "question_display_order" => "1019",
"question_required" => "yes", "question_default_value" => $vetted_form_answer["query_name"],
"question_visible" => "yes", "question_ecma" => "", "question_display_size" => "0");
$this->query_build_question_elements["query_type"]["count"] = array ( "question_id" =>
"query_type", "question_element_title" => "Count", "question_element_value" => "count" );
$this->query_build_question_elements["query_type"]["sum"] = array ( "question_id" =>
"query_type", "question_element_title" => "Sum", "question_element_value" => "sum" );
/*
$this->query_build_question_elements["query_type"]["sum_adults"] = array ( "question_id" =>
"query_type", "question_element_title" => "Sum adults", "question_element_value" => "sum_adults" );
$this->query_build_question_elements["query_type"]["sum_children"] = array ( "question_id" =>
"query_type", "question_element_title" => "Sum children", "question_element_value" => "sum_children" );
//$this->query_build_question_elements["query_type"]["household"] = array ( "question_id" =>
// "query_type", "question_element_title" => "Household Count", "question_element_value" => "household_count" );
*/
$this->query_build_question_elements["query_type"]["household_service_days"] = array ( "question_id" =>
"query_type", "question_element_title" => "Household Service Days", "question_element_value" => "household_service_days" );
$this->query_build_question_elements["query_type"]["client_service_days"] = array ( "question_id" =>
"query_type", "question_element_title" => "Client Service Days", "question_element_value" => "client_service_days");
$this->query_build_question_elements["query_type"]["client_list"] = array ( "question_id" =>
"query_type", "question_element_title" => "List of Clients", "question_element_value" => "client_list");
$this->query_build_question_elements["query_type"]["cross_query_calculation"] = array ( "question_id" =>
"query_type", "question_element_title" => "Cross query calculation", "question_element_value" => "cross_query_calculation");
$this->query_build_question_elements["query_type"]["hidden"] = array ( "question_id" =>
"query_type", "question_element_title" => "Hidden", "question_element_value" => "hidden");
$this->query_build_questions["client_list_elements_template"] = array (
"question_id" => "client_list_elements_template", "question_title" => "Template for client list (only applicable for List of
Clients questions)", "question_type" => "textarea",
"question_owner_group_id" => "1", "question_owner_org_id" => "1", "question_field_size" => "2",
"question_range_bottom" => "0", "question_range_top" => "0", "question_display_order" => "1019",
"question_required" => "no", "question_default_value" => '',
"question_visible" => "yes", "question_ecma" => "", "question_display_size" => "90");
}
function make_sql_build_form_html ($vetted_form_answer)
{
//display_value ($vetted_form_answer);
$ht = "<table><tr>";
$ht .= question_display ($this->query_build_questions["query_title"], "", $vetted_form_answer);
$ht .= "</tr></table><table>";
$ht .= "<tr><td>";
$ht .= question_display ($this->query_build_questions["query_type"], $this->query_build_question_elements["query_type"], $vetted_form_answer);
$ht .= "</td></tr></table>";
$ht .= "<table>";
$ht .= "<tr><td>";
$ht .= submit_button ( 'n' );
$ht .= "</td></tr>";
$ht .= "</table>";
$ht .= "<table>";
$ht .= "<tr><td>";
//$ht .= question_display ($this->query_build_questionsB["query_sql"], "", $vetted_form_answer);
$ht .= question_display ($this->query_build_questions["query_sql"], "", $vetted_form_answer);
//$ht .= question_display ($this->query_build_questions["query_sql"], "", $vetted_form_answer);
$ht .= "</td></tr>";
$ht .= "</table>";
$ht .= "<table>";
$ht .= "<tr><td>";
$ht .= question_display ($this->query_build_questions["client_list_elements_template"], "", $vetted_form_answer);
$ht .= "</td></tr></table>";
$ht .= "<table><tr><td>";
$ht .= form_end ("Submit Form", 1);
$ht .= "</td></tr>";
$ht .= "</table>";
return $ht;
}
//Insert the new or edited values for the pre built query
function insert_query_build_answers ($vetted_values)
{
$sql = "SELECT * FROM pre_built_queries WHERE query_title LIKE '".$vetted_values["query_title"]."'";
$result = run_query ( $sql, "Checking for existing pre-built query");
$count = num_rows ($result);
if ( $count > 0 ) {
$sql = "UPDATE pre_built_queries SET query_sql='".$vetted_values["query_sql"]."', query_type='".$vetted_values["query_type"]."', client_list_elements_template='".$vetted_values["client_list_elements_template"]."' WHERE query_title LIKE '".$vetted_values["query_title"]."'";
//echo '<p/><p/>Updating SQL<p/>'.$sql.'<p/>';
run_query ( $sql, "Updating pre built queries" );
}
else {
$sql = "INSERT INTO pre_built_queries (query_title, query_sql, query_type, client_list_elements_template) VALUES ('".$vetted_values["query_title"]."',
'".$vetted_values["query_sql"]."', '".$vetted_values["query_type"]."', '".$vetted_values["client_list_elements_template"]."' )";
//echo $sql;
run_query($sql, "Inserting pre-built query values");
}
}
function return_values_for_edit ( $edit_query_title )
{
$edit_query_title = addslashes ( $edit_query_title );
$sql = "SELECT * FROM pre_built_queries WHERE query_title LIKE '".$edit_query_title."'";
$return_this = run_query_return_single_row ( $sql, "Pulling query to edit" );
return $return_this;
}
}
//Return an html table listing existing pre-built queries
class Query_list
{
function query_list_table ()
{
GLOBAL $system_directory;
$sql = "SELECT * FROM pre_built_queries ORDER BY query_title";
$qlist = run_query_return_array ($sql, "Could not retrieve array of pre built queries");
$ht .= "<table><tr><td class=\"headlinesub\" colspan=\"2\">Existing Queries</td></tr>";
$ht .= "<tr><td class=\"headlinesub\" >Query title</td><td class=\"headlinesub\" >Query type</td></tr>";
if ($qlist)
{
foreach ( $qlist AS $curr_query )
{
$ht .= "<tr>";
$ht .= "<td><a href=\"".$system_directory."query_build.php?edit_query=".$curr_query["query_title"]."\">".$curr_query["query_title"]."</a></td>";
$ht .= "<td>".$curr_query["query_type"]."</td>";
$ht .= "</tr>\n";
}
$ht .= "</table>";
}
else
{
$ht = "No pre-built queries in database.";
}
return $ht;
}
}
?>