Location: PHPKode > projects > Homeless Mangement Information System > hmis/include/query_build.inc
<?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;
	
	
	}


}



?>
Return current item: Homeless Mangement Information System