# # # build_raw_graphs() # # Builds a non-chrono raw graph or pie chart node. # Note, each report field becomes its own raw graph node, the subroutine builds all # graph fields of the active base query header at once. # # include "templates.statistics.raw_graphs.raw_graphs_utilities"; subroutine(build_raw_graphs( string session_id, string profile_name, string database_last_modification_time, node base_query_header, string alpha_root_table_id, string root_table_id, int total_rows, string combined_filter_expression, bool is_table_filter_expression, bool use_overview_for_totals, string graph_type, bool is_3d, int max_variables, bool show_remainder, bool show_legend, int max_legend_rows, int y_height, int x_length, bool sort_all_descending, string report_element_sort_by, string report_element_sort_direction, int number_of_days, node progress_substep_info, node collected_whitelist_objects), ( debug_message("\n\n#### build_raw_graphs() START \n\n"); # debug_message("\n\n" . node_as_string(base_query_header) . "\n\n"); bool is_pie_chart = if (graph_type eq "pie") then (true) else (false); # debug_message("#### total_rows: " . total_rows . "\n"); subroutine(get_sorted_report_field_label_in_build_raw_graphs( node base_query_header, string sort_by_report_field_name), ( node column; string sorted_report_field_label; foreach column base_query_header ( if (@column{"report_field_name"} eq sort_by_report_field_name) then ( sorted_report_field_label = @column{"column_label"}; last; ); ); sorted_report_field_label; )); # # # # Check if we need to calculate a remainder # # # # KHP-RC, we shouldn't calculate a remainder where aggregation_method="unique" # because the remainder value is wrong due an entirely different calculated # sum value (sum is derived from overview report). In this case we should # probably leave the remainder blank! Though what if we have a pie-chart? # Re-check this problem! # # A pie-chart always shows the remainder # # bool is_remainder_variable = false; int number_of_remainder_items = 0; if ((show_remainder or is_pie_chart) and (total_rows > max_variables)) then ( number_of_remainder_items = total_rows - max_variables; if (number_of_remainder_items > 1) then ( is_remainder_variable = true; ) else ( # There is only 1 remainder item for which we don't show a remainder # but simply increase the max_variables number_of_remainder_items = 0; max_variables++; ); ); int number_of_variables = if (total_rows > max_variables) then (max_variables) else (total_rows); # # # Set total in query header # # set_sum_and_total_in_query_header( session_id, profile_name, database_last_modification_time, alpha_root_table_id, root_table_id, base_query_header, combined_filter_expression, is_table_filter_expression, use_overview_for_totals, number_of_days, collected_whitelist_objects ); # # # # Check the sort modus (use_report_element_sort_by | all_descending | all_ascending) # and is_single_working_table # # # bool is_single_working_table = if (!sort_all_descending or (total_rows == 1)) then (true) else (false); string sort_direction; string sorted_table_id; table working_table; string qs; bool is_sort_by_report_field_in_query_header = false; if (is_single_working_table) then ( # # # Verify sort and load the working table # # sorted_table_id = root_table_id; # is overriden below if valid sort parameters sort_direction = if (report_element_sort_direction ne "") then (report_element_sort_direction) else ("descending"); if ((total_rows > 1) and (report_element_sort_by ne "")) then ( is_sort_by_report_field_in_query_header = get_is_sort_by_report_field_in_query_header(base_query_header, report_element_sort_by); if (is_sort_by_report_field_in_query_header) then ( sorted_table_id = get_sorted_table_id(root_table_id, report_element_sort_by, sort_direction); # # # Check/create sorted table # # if (!get_table_exists(profile_name, sorted_table_id, collected_whitelist_objects)) then ( qs = "CREATE TABLE " . sorted_table_id . " "; qs .= "SELECT * "; qs .= "FROM " . root_table_id . " "; qs .= "ORDER BY " . report_element_sort_by; if (sort_direction eq "descending") then ( qs .= " DESC"; ); ssql_query(qs); # Add new table to collected_whitelist_objects @collected_whitelist_objects{"tables"}{sorted_table_id} = true; ); ); ); # # # Load the single working table # # working_table = load_table(sorted_table_id); ) else ( # # Sort per aggregating report field # sort_direction = "descending"; ); # debug_message("#### sort_all_descending: " . sort_all_descending . "\n"); # debug_message("#### sort_direction: " . sort_direction . "\n"); # debug_message("#### is_sort_by_report_field_in_query_header: " . is_sort_by_report_field_in_query_header . "\n"); # debug_message("#### is_single_working_table: " . is_single_working_table . "\n"); # # # Handle sort_info # # string sort_info; if (!sort_all_descending and is_sort_by_report_field_in_query_header) then ( string sorted_report_field_label = get_sorted_report_field_label_in_build_raw_graphs(base_query_header, report_element_sort_by); sort_info = "{=lang_stats.graphs.sorted_by=} " . sorted_report_field_label . ", {=lang_stats.general." . sort_direction . "=}"; ); # # # # Create the x_data_info. # The x data are identical for all graph fields in the base query header, # so we create it once and then clone it to each raw graph. # # # node x_data_info; if (!is_pie_chart) then ( x_data_info = get_raw_graph_x_data_info(x_length, number_of_variables, is_remainder_variable); ); # # # # Create the legend header # # # node legend_query_header; int number_of_legend_rows = max_legend_rows; if (show_legend) then ( legend_query_header = get_raw_graph_legend_query_header(base_query_header); if (number_of_legend_rows > number_of_variables) then ( number_of_legend_rows = number_of_variables; ); ); # PROGRESS_SETTER increment_progress_substep(progress_substep_info); # # # Compute x- and y-variable values # # node column; node graph_column; node graph_query_header; string graph_label; string report_field_name; string display_format_type; string aggregation_method; string graph_id; node raw_graph; int row_number; float total_value; float _100_divided_by_total; float cutoff_sum; float remainder_sum; float variable_value; float max_variable_value; # The max value of all displayed graph variables node table_column; node raw_variables; int ssql_column_number; node y_data; node raw_legend; foreach column base_query_header ( if (@column{"create_graph"}) then ( # debug_message("#### build_raw_graphs() - create_graph exists and is true for: " . @column{"graph_id"} . "\n"); graph_id = @column{"graph_id"}; graph_label = @column{"column_label"}; report_field_name = @column{"report_field_name"}; display_format_type = @column{"display_format_type"}; total_value = @column{"total_value"}; _100_divided_by_total = @column{"_100_divided_by_total"}; aggregation_method = @column{"aggregation_method"}; # # Handle sort info # if (sort_all_descending) then ( sort_info = "{=lang_stats.general." . sort_direction . "=}"; ); # # Write all data to "v.raw_graph" which becomes saved in raw_graphs # delete_node("v.raw_graph"); v.raw_graph = ""; raw_graph = "v.raw_graph"; set_subnode_value(raw_graph, "graph_id", graph_id); set_subnode_value(raw_graph, "graph_label", graph_label); set_subnode_value(raw_graph, "sort_info", sort_info); set_subnode_value(raw_graph, "report_field_name", report_field_name); # set_subnode_value(raw_graph, "display_format_type", display_format_type); set_subnode_value(raw_graph, "graph_type", graph_type); set_subnode_value(raw_graph, "is_3d", is_3d); set_subnode_value(raw_graph, "is_chrono_graph", false); set_subnode_value(raw_graph, "sort_all_descending", sort_all_descending); set_subnode_value(raw_graph, "last_variable_is_remainder", is_remainder_variable); set_subnode_value(raw_graph, "number_of_remainder_items", number_of_remainder_items); set_subnode_value(raw_graph, "show_legend", show_legend); set_subnode_value(raw_graph, "total_value", total_value); # required when generating a pie chart if (is_pie_chart) then ( # Set pie chart size in main raw graph node set_subnode_value(raw_graph, "y_height", y_height); set_subnode_value(raw_graph, "x_length", x_length); # Add the aggregation method so that we know if we have # to calculate a non-unique sum in the image code # if aggregation method is unique set_subnode_value(raw_graph, "aggregation_method", aggregation_method); ); # # Set default data which are set when the graph image is created # (such as image width and height, which we don't know yet) # set_subnode_value(raw_graph, "image_width", 0); set_subnode_value(raw_graph, "image_height", 0); set_subnode_value(raw_graph, "variables", ""); raw_variables = raw_graph{"variables"}; # # # Sort the table # # if (!is_single_working_table) then ( sorted_table_id = get_sorted_table_id(root_table_id, report_field_name, sort_direction); if (!get_table_exists(profile_name, sorted_table_id, collected_whitelist_objects)) then ( qs = "CREATE TABLE " . sorted_table_id . " "; qs .= "SELECT * "; qs .= "FROM " . root_table_id . " "; qs .= "ORDER BY " . @column{"table_field_name"}; if (sort_direction eq "descending") then ( qs .= " DESC"; ); # debug_message("\n\n#### qs for sorted graph table: " . qs . "\n\n"); ssql_query(qs); # Add new table to collected_whitelist_objects @collected_whitelist_objects{"tables"}{sorted_table_id} = true; ); # debug_message("\n\n#### load working table \n\n"); working_table = load_table(sorted_table_id); ); # # # Create a graph query_header which only contains the # aggregating field of the current report field # and all non-aggregating fields if we need the legend values # # graph_query_header = get_raw_graph_query_header(base_query_header, report_field_name, show_legend); # # # Set variables amd legend values # # cutoff_sum = 0.0; remainder_sum = 0.0; max_variable_value = 0.0; for (row_number = 0; row_number < number_of_variables; row_number++) ( foreach graph_column graph_query_header ( if (@graph_column{"is_aggregating_field"}) then ( # The graph_query_header contains only a single relevant aggregating field, # which is this field. ssql_column_number = node_name(graph_column); variable_value = table_get_cell_value(working_table, row_number, ssql_column_number); # debug_message("#### get_variable_value from report_field OK: " . variable_value . "\n\n"); cutoff_sum += variable_value; set_subnode_value(raw_variables, row_number, variable_value); # # Set actual display_format_type # # display_format_type = @graph_column{"display_format_type"}; # set_subnode_value(raw_graph, "display_format_type", display_format_type); # # Check max_variable_value # if (max_variable_value < variable_value) then ( max_variable_value = variable_value; ); ); ); ); # for row # # # Handle remainder # # if (is_remainder_variable) then ( # # Set the remainder_sum to 0 if aggregation_method is equal unqiue. # In this case we don't know to true remainder value because the sum # is created from the overview report # if (aggregation_method ne "unique") then ( remainder_sum = total_value - cutoff_sum; ) else ( remainder_sum = 0; ); # Add remainder as last variable set_subnode_value(raw_variables, num_subnodes(raw_variables), remainder_sum); if (remainder_sum > max_variable_value) then ( max_variable_value = remainder_sum; ); ); if (!is_pie_chart) then ( # # # Set raw y_data # # set_subnode_value(raw_graph, "y_data", ""); y_data = raw_graph{"y_data"}; set_raw_graph_y_data( y_data, y_height, max_variable_value, _100_divided_by_total, display_format_type ); # # # Set raw x_data # # clone_node(x_data_info, raw_graph . ".x_data"); ); # # # Handle legend # # if (show_legend) then ( set_subnode_value(raw_graph, "legend", ""); raw_legend = raw_graph{"legend"}; set_raw_graph_legend( legend_query_header, raw_legend, working_table, number_of_legend_rows ); ); # # # Unload working_table # # if (!is_single_working_table) then ( unload_table(working_table); ); # # # Save the raw graph # # # debug_message("#### build_raw_graphs() - CHECK NODE VALUE OF RAW_GRAPH:\n" . node_as_string(raw_graph) . "\n"); # debug_message("#### build_raw_graphs() - profile_name: " . profile_name . "\n"); # debug_message("#### build_raw_graphs() - graph_id: " . graph_id . "\n"); #debug_message("0 exists: " . node_exists("profiles_cache." . profile_name . ".raw_graphs" . graph_id) . "\n"); ("profiles_cache." . profile_name . ".raw_graphs" . graph_id) = ""; #debug_message("1 exists: " . node_exists("profiles_cache." . profile_name . ".raw_graphs" . graph_id) . "\n"); clone_node(raw_graph, "profiles_cache." . profile_name . ".raw_graphs." . graph_id); #debug_message("2 exists: " . node_exists("profiles_cache." . profile_name . ".raw_graphs" . graph_id) . "\n"); #debug_message("0 Node is: " . node_as_string("profiles_cache." . profile_name . ".raw_graphs." . graph_id) . "\n"); save_node("profiles_cache." . profile_name . ".raw_graphs." . graph_id); debug_message("Saving node: " . "profiles_cache." . profile_name . ".raw_graphs." . graph_id . "\n"); #debug_message("Node is: " . node_as_string("profiles_cache." . profile_name . ".raw_graphs." . graph_id) . "\n"); # Add raw_graph to profiles_cache whitelist @collected_whitelist_objects{"raw_graphs"}{graph_id} = true; # debug_message("added raw graph to whitelist: " . collected_whitelist_objects{"raw_graphs"}{graph_id}); ); ); # foreach column base_query_header # # # Unload working_table # # if (is_single_working_table) then ( unload_table(working_table); ); ));