# # # 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.util.get_table_exists"; include "templates.statistics.raw_graphs.raw_graphs_table_util"; include "templates.statistics.raw_graphs.raw_graphs_utilities"; subroutine(build_raw_graphs( string session_id, string profile_name, node query_fields, node report_job, string database_last_modification_time, string report_element_type, node base_query_header, node used_database_fields, string alpha_root_table_id, string root_table_id, string combined_filter_expression, bool is_report_field_expression, bool is_table_filter_expression, bool is_sort_by_before_expression_evaluation, string sort_by_before_expression_evaluation, string sort_direction_before_expression_evaluation, bool use_overview_for_totals, string graph_type, bool is_3d, int max_variables, bool show_remainder, bool show_legend, bool show_values_in_legend, bool show_percent_in_legend, int max_legend_rows, int y_height, int x_length, bool show_percent_on_y_axis, bool sort_all_descending, int first_weekday, int number_of_days, string report_element_sort_by, string report_element_sort_direction, node progress_substep_info, node collected_whitelist_objects), ( debug_message("\n\n#### build_raw_graphs() \n"); # debug_message("\n\n" . node_as_string(base_query_header) . "\n\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; )); # # # Get sort_by and sort_direction of the first required table # # int total_rows; # bool is_single_working_table = if (!sort_all_descending or (total_rows == 1)) then (true) else (false); bool is_single_working_table = !sort_all_descending; string sort_by_report_field_name; string sort_direction; string sorted_table_id; table working_table; string qs; bool is_sort_by_report_field_in_query_header = false; bool is_xref_table = false; string xref_source_table_id = ""; node raw_graph_table_info; node column; # # # Check the sort modus (use_report_element_sort_by or all_descending) # # if (is_single_working_table) then ( # # # Verify sort and load the working table # # sort_by_report_field_name = ""; sort_direction = report_element_sort_direction; if (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 ( sort_by_report_field_name = report_element_sort_by; # sort_by_database_field_name = @query_fields{report_element_sort_by}{"database_field_name"}; # sorted_table_id = get_sorted_table_id(root_table_id, report_element_sort_by, sort_direction, first_weekday); ); ); raw_graph_table_info = get_raw_graph_table_info( session_id, profile_name, query_fields, report_job, report_element_type, base_query_header, used_database_fields, is_report_field_expression, is_table_filter_expression, is_sort_by_before_expression_evaluation, sort_by_before_expression_evaluation, sort_direction_before_expression_evaluation, use_overview_for_totals, root_table_id, combined_filter_expression, sort_by_report_field_name, sort_direction, max_variables, first_weekday, number_of_days, true, # allow_xref_table true, # get_total_rows progress_substep_info, collected_whitelist_objects); is_xref_table = @raw_graph_table_info{"is_xref_table"}; xref_source_table_id = @raw_graph_table_info{"xref_source_table_id"}; sorted_table_id = @raw_graph_table_info{"sorted_table_id"}; total_rows = @raw_graph_table_info{"total_rows"}; # # Load the single working table # working_table = load_table(sorted_table_id); ) else ( # # Sort per aggregating report field # sort_direction = "descending"; foreach column base_query_header ( if (@column{"create_graph"}) then ( sort_by_report_field_name = @column{"report_field_name"}; raw_graph_table_info = get_raw_graph_table_info( session_id, profile_name, query_fields, report_job, report_element_type, base_query_header, used_database_fields, is_report_field_expression, is_table_filter_expression, is_sort_by_before_expression_evaluation, sort_by_before_expression_evaluation, sort_direction_before_expression_evaluation, use_overview_for_totals, root_table_id, combined_filter_expression, sort_by_report_field_name, sort_direction, max_variables, first_weekday, number_of_days, true, # allow_xref_table true, # get_total_rows progress_substep_info, collected_whitelist_objects); # KHP 07/March/2013 - track is_xref_table and other xref related properties, # even if properties change with each iteration, we simply use the last one # and will catch is_xref_table true or false. # ToDo - Revise this part. The problem here is that if there is more than one graph # it is possible that one graph matches the criteria to use an xref table but # the next table doesn't match it. So in the end, the system will use xref tables and # non-xref tables which doesn't sound to be ideal. # At the moment the last iteration of this loop defines where to get the total values # depending on is_xref_table. is_xref_table = @raw_graph_table_info{"is_xref_table"}; xref_source_table_id = @raw_graph_table_info{"xref_source_table_id"}; sorted_table_id = @raw_graph_table_info{"sorted_table_id"}; total_rows = @raw_graph_table_info{"total_rows"}; ); ); ); debug_message("#### total_rows: " . total_rows . "\n"); debug_message("#### report_element_sort_direction: " . report_element_sort_direction . "\n"); 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"); if (total_rows > 0) then ( # # # 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); debug_message("#### sorted_report_field_label: " . sorted_report_field_label . "\n"); sort_info = "{=lang_stats.graphs.sorted_by=} " . sorted_report_field_label . ", {=lang_stats.general." . sort_direction . "=}"; ); # # # # Check if we need to calculate a remainder # # # Note, the remainder value is set to 0 for database fields where # aggregation_method="unique" because its value can't be calculated, # even if "use_overview_for_totals" is active. # # A pie-chart always shows the remainder # # bool is_pie_chart = (graph_type eq "pie"); 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, query_fields, database_last_modification_time, alpha_root_table_id, root_table_id, is_xref_table, xref_source_table_id, base_query_header, combined_filter_expression, is_table_filter_expression, use_overview_for_totals, number_of_days, collected_whitelist_objects ); # # # # 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 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 variables_in_percent; # Used in legend 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, "show_percent_on_y_axis", show_percent_on_y_axis); 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, "show_values_in_legend", show_values_in_legend); set_subnode_value(raw_graph, "show_percent_in_legend", show_percent_in_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, first_weekday); # # 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; # ); raw_graph_table_info = get_raw_graph_table_info( session_id, profile_name, query_fields, report_job, report_element_type, base_query_header, used_database_fields, is_report_field_expression, is_table_filter_expression, is_sort_by_before_expression_evaluation, sort_by_before_expression_evaluation, sort_direction_before_expression_evaluation, use_overview_for_totals, root_table_id, combined_filter_expression, report_field_name, # sort by active report_field sort_direction, max_variables, first_weekday, number_of_days, true, # allow_xref_table false, # get_total_rows, we don't need any total here progress_substep_info, collected_whitelist_objects); # debug_message("#### raw_graph_table_info:\n" . node_as_string(raw_graph_table_info) . "\n"); is_xref_table = @raw_graph_table_info{"is_xref_table"}; debug_message("#### is_xref_table: " . is_xref_table . "\n"); xref_source_table_id = @raw_graph_table_info{"xref_source_table_id"}; sorted_table_id = @raw_graph_table_info{"sorted_table_id"}; # 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 and 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); # # 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 unique, # regardless if have the sum from ssql tables or the overview report # because the remainder will be always wrong if aggregation_method=unique. # # KHP 07/Dec/2012 - Above is not always true. A pie chart always requires # the remainder value, even if its not correct. Further, a unique value such # as visitors be not be unique in every context, i.e. such as web browsers. # For a web browsers or operating systems visitors can be treated as any other # sum value. For now, let's always set the remainder. # ToDO - recheck this case with Product Manager # if (aggregation_method ne "unique") then ( # # remainder_sum = total_value - cutoff_sum; # ) # else ( # remainder_sum = 0; # ); # Always set remainder in raw graph remainder_sum = total_value - cutoff_sum; # 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, "variables_in_percent", ""); set_subnode_value(raw_graph, "legend", ""); variables_in_percent = raw_graph{"variables_in_percent"}; raw_legend = raw_graph{"legend"}; set_raw_graph_legend( raw_variables, variables_in_percent, _100_divided_by_total, is_remainder_variable, legend_query_header, raw_legend, working_table, show_percent_in_legend, number_of_legend_rows, is_xref_table ); ); # # # 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"); ("profiles_cache." . profile_name . ".raw_graphs" . graph_id) = ""; clone_node(raw_graph, "profiles_cache." . profile_name . ".raw_graphs." . graph_id); save_node("profiles_cache." . profile_name . ".raw_graphs." . graph_id); # Add raw_graph to profiles_cache whitelist @collected_whitelist_objects{"raw_graphs"}{graph_id} = true; ); ); # foreach column base_query_header # # # Unload working_table # # if (is_single_working_table) then ( unload_table(working_table); ); ); # Return total_rows; ));