# # # # expression_handling.cfv # # cell_by_name() --> used in report field expressions or in row visibility expressions # cell_by_row_number_and_name() --> used in report field expressions or in row visibility expressions # cell_by_number() --> DISABLED --> used in report field expressions or in row visibility expressions # # expression_handling_check_sort_by_before_expression_evaluation() # create_row_visibility_cutoff_filter_expression() # expression_handling_create_expression_headers() # expression_handling_update_aggregation_values_in_query_header() # expression_handling_update_ssql_table() # # # # # # # # # # cell_by_name() (used in report field expressions or in row visibility expressions) # # # This is a utility function which can be called from a report field expression or # table filter expressions. cell_by_name() returns the value of the given column_name # for the active row_number. # # KHP 12/Feb/2008, removed the row_number argument in cell_by_name() and added # a different subroutine for row_number support, which is cell_by_row_number_and_name(). # This simplify epressions written by users, especially as the row_number argument is not # required very often. The row_number is only useful if it becomes manipulated within the expression. # # # # 2012-01-25 - GMF - Removed this and made it into a Salang built-in function, for better performance #subroutine(cell_by_name(string column_name), ( # # if (v.temp_dat.is_ssql_table) then ( # # int row_number = v.temp_dat.row_number; # # # debug_message("#### cell_by_name() - row_number: " . row_number . "\n"); # # # # # Return ssql table value # # # # bool is_aggregating_field = @("v.temp_dat.base_query_header." . column_name . ".is_aggregating_field"); # int ssql_column_number = @("v.temp_dat.base_query_header." . column_name . ".ssql_column_number"); # # if (is_aggregating_field) then ( # # table_get_cell_value(v.temp_dat.global_root_table, row_number, ssql_column_number); # # # debug_message("#### cell_by_name() returned int/float: " . table_get_cell_value(v.temp_dat.global_root_table, row_number, ssql_column_number) . "\n"); # ) # else ( # # table_get_cell_string_value(v.temp_dat.global_root_table, row_number, ssql_column_number); # # # debug_message("#### cell_by_name() returned string: " . table_get_cell_string_value(v.temp_dat.global_root_table, row_number, ssql_column_number) . "\n")#; # ); # ) # else ( # # # # # Return aggregation value of header # # There are no rows, we get the value from the query header node, i.e. the total_value # # # # string processing_node_name = v.temp_dat.processing_node_name; # # # processing_node_name is: remainder_value | average_value | min_value | max_value | total_value # # @("v.temp_dat.base_query_header." . column_name . "." . processing_node_name); # ); #)); # # # cell_by_row_number_and_name() (used in report field expressions or in row visibility expressions) # # # This is a utility function which can be called from a report field expression or # table filter expression. cell_by_row_number_and_name() returns the value of the given # row_number and column_name. # This subroutine works as cell_by_name() but includes the row_number argument in case that # the row_number should be manipulated within the expression. # # # 2012-01-25 - GMF - Removed this and made it into a Salang built-in function, for better performance #subroutine(cell_by_row_number_and_name(int row_number, string column_name), ( # # if (v.temp_dat.is_ssql_table) then ( # # # # # Return ssql table value # # # # bool is_aggregating_field = @("v.temp_dat.base_query_header." . column_name . ".is_aggregating_field"); # int ssql_column_number = @("v.temp_dat.base_query_header." . column_name . ".ssql_column_number"); # # if (is_aggregating_field) then ( # # table_get_cell_value(v.temp_dat.global_root_table, row_number, ssql_column_number); # # debug_message("#### cell_by_name() returned int/float: " . table_get_cell_value(v.temp_dat.global_root_table, row_number, ssql_column_number) . "\n"); # ) # else ( # # table_get_cell_string_value(v.temp_dat.global_root_table, row_number, ssql_column_number); # # debug_message("#### cell_by_name() returned string: " . table_get_cell_string_value(v.temp_dat.global_root_table, row_number, ssql_column_number) . "\n"); # ); # ) # else ( # # # # # Return aggregation value of header # # There are no rows, we get the value from the query header node, i.e. the total_value # # # # string processing_node_name = v.temp_dat.processing_node_name; # # # processing_node_name is: remainder_value | average_value | min_value | max_value | total_value # # @("v.temp_dat.base_query_header." . column_name . "." . processing_node_name); # ); #)); # # # # cell_by_number() (used in report field expressions or in row visibility expressions) # # # # This is a utility function which can be called from a report field expression to get the value of a table cell. # cell_by_number() is not yet supported in v8. There is the question if it should be supported at all because # it is quite complicated to track the actual columns by number as s cell_by_number() expression may be out of # date as soon as columns changes via show/hide columns. # # subroutine(cell_by_number(int row_number, int column_number), ( # node_value(subnode_by_number(subnode_by_number("volatile.query_result.data", row_number), column_number)); # )); # # # # # expression_handling_check_sort_by_before_expression_evaluation # # # # # #subroutine(expression_handling_check_sort_by_before_expression_evaluation( # node base_query_header, # string root_table_id), ( # # debug_message("\n\n#### expression_handling_check_sort_by_before_expression_evaluation() \n"); # # # This subroutine sorts the root table if the query header contains a # # report field with a sort_by_before_expression_evaluation value. # # If we find 0 sort_by_before_expression_evaluation value, then do nothing # # If we find 1 sort_by_before_expression_evaluation value, then sort the root table # # If we find >= 2 sort_by_before_expression_evaluation values then throw an error # # because multiple sort_by_before_expression_evaluation are not supported (this would require # # to refactor expression_handling_update_ssql_table() so that it runs multiple passes, each with its # # own sort_by definition). # # node item; # string sort_by; # string sort_direction; # string sort_by_id; # # # Collect sort_by requirements in a node so that two or more equal # # sorts create a single sort_by requirement. # node required_sort_by = new_node(); # # foreach item base_query_header ( # # if (@item{"is_expression"} and # item?{"sort_by_before_expression_evaluation"} and # (@item{"sort_by_before_expression_evaluation"} ne "")) then ( # # sort_by = @item{"sort_by_before_expression_evaluation"}; # sort_direction = @item{"sort_direction_before_expression_evaluation"}; # sort_by_id = sort_by . "_" . sort_direction; # # required_sort_by{sort_by_id} = true; # ); # ); # # # # debug_message("#### required_sort_by:\n" . node_as_string(required_sort_by) . "\n"); # # int number_of_required_sort_by = num_subnodes(required_sort_by); # # # debug_message("#### number_of_required_sort_by: " . number_of_required_sort_by . "\n"); # # if (number_of_required_sort_by > 0) then ( # # if (number_of_required_sort_by == 1) then ( # # # # # # # Create a temp sorted table which will become the final root_table # # # # # # string temp_table_id = root_table_id . "_" . now(); # # string qs = "CREATE TABLE " . temp_table_id . " "; # qs .= "SELECT * "; # qs .= "FROM " . root_table_id . " "; # qs .= "ORDER BY " . sort_by; # # if (sort_direction eq "descending") then ( # qs .= " DESC"; # ); # # debug_message("#### qs: " . qs . "\n"); # # ssql_query(qs); # # # # # # # Drop the original unsorted root table # # # # # # ssql_query("DROP TABLE " . root_table_id); # # # # # # Make the just sorted table the new root_table # # # # qs = "CREATE TABLE " . root_table_id . " "; # qs .= "SELECT * "; # qs .= "FROM " . temp_table_id; # # ssql_query(qs); # # # # # Drop the sorted table with temp_table_id # # # # ssql_query("DROP TABLE " . temp_table_id); # ) # else ( # # # Throw an error # string error_msg = "Error in expression_handling_check_sort_by_before_expression_evaluation(). # The base query header contains more than one report field with a sort_by_before_expression_evaluation requirement. # Multiple sort_by in expression_handling are not supported"; # error(error_msg); # ); # ); #)); # # # # # create_row_visibility_cutoff_filter_expression() # # # # subroutine(create_row_visibility_cutoff_filter_expression( node base_query_header, bool is_visible_rows_table, table cutoff_table, int number_of_cutoff_table_rows), ( # This subroutine creates the filter expression for the overview report. # The cutoff table may contain the visible rows or the hidden rows, defined by is_visible_rows_table. # If is_visible_rows_table then we create a normal filter expression, # else we create a NOT filter expression. debug_message("\n\n#### create_row_visibility_cutoff_filter_expression() START \n\n"); # debug_message("#### base_query_header 1:\n" . node_as_string(base_query_header) . "\n"); debug_message("#### create_row_visibility_cutoff_filter_expression() - number_of_cutoff_table_rows: " . number_of_cutoff_table_rows . "\n"); # # # Create a new query header which only contains the relvant columns # We also use the query header to track the expressions per non-aggregating field # # int ssql_column_number; int row_visibility_helper_ssql_column_number; # node query_header = new_node(); This DOES'NT WORK, WE CAN'T CLONE to an unrooted node? delete_node("v.row_visibility_cutoff_filter_query_header"); v.row_visibility_cutoff_filter_query_header = ""; clone_node(base_query_header, "v.row_visibility_cutoff_filter_query_header"); node query_header = "v.row_visibility_cutoff_filter_query_header"; # debug_message("#### new query_header (cloned base_query_header to query_header):\n" . node_as_string(query_header) . "\n"); node column; foreach column query_header ( if (@column{"is_aggregating_field"}) then ( if (@column{"report_field_name"} eq "internal_table_filter_expression_helper_column") then ( row_visibility_helper_ssql_column_number = node_name(column); ); delete_node(column); ) else ( ssql_column_number = node_name(column); set_subnode_value(column, "ssql_column_number", ssql_column_number); set_subnode_value(column, "subfilter_expression", ""); ); ); debug_message("\n\n#### create_row_visibility_cutoff_filter_expression() - query_header 2:\n" . node_as_string(query_header) . "\n"); # # # Collect the filter items # # string filter_expression; if (num_subnodes(query_header) > 0) then ( string database_field_name; string column_value; string subfilter_expression; for (int row_number = 0; row_number < number_of_cutoff_table_rows; row_number++) ( # # Add item to field_expression # foreach column query_header ( ssql_column_number = @column{"ssql_column_number"}; database_field_name = @column{"database_field_name"}; column_value = table_get_cell_string_value(cutoff_table, row_number, ssql_column_number); subfilter_expression = @column{"subfilter_expression"} . "(" . database_field_name . " within '" . column_value . "') or "; set_subnode_value(column, "subfilter_expression", subfilter_expression); ); ); debug_message("\n\n#### create_row_visibility_cutoff_filter_expression() - query_header 2:\n" . node_as_string(query_header) . "\n"); # # # Create final filter expression # # foreach column query_header ( subfilter_expression = @column{"subfilter_expression"}; subfilter_expression = "(" . replace_last(subfilter_expression, " or ", ")"); if (!is_visible_rows_table) then ( subfilter_expression = "(not" . subfilter_expression . ")"; ); filter_expression .= subfilter_expression . " and "; ); filter_expression = "(" . replace_last(filter_expression, " and ", ")"); # # Clear the query header node # delete_node(query_header); ); debug_message("#### row_visibility_cutoff_filter_expression: " . filter_expression . "\n"); filter_expression; debug_message("#### create_row_visibility_cutoff_filter_expression() END \n\n"); )); # # # # expression_handling_create_expression_headers() # # # # subroutine(expression_handling_create_expression_headers( node base_query_header, node global_temp_dat), ( # # # Creates a base query header and expression query header where we can access the columns by report field name # # node column; int ssql_column_number = 0; string report_field_name; # Set temp base_query_header node # Contains all report fields of the table so that cell_by_name() can refer to any of it. set_subnode_value(global_temp_dat, "base_query_header", ""); # Set temp expression_query_header node # Only contains report fields with an expression, it is used to set the values in the ssql table or query_header set_subnode_value(global_temp_dat, "expression_query_header", ""); # v.temp_dat.base_query_header = ""; # v.temp_dat.expression_query_header = ""; foreach column base_query_header ( # Don't use column number of base_query_header node names because the # node name may be the report field name, i.e. in overview query header. # ssql_column_number = node_name(column); report_field_name = @column{"report_field_name"}; clone_node(column, global_temp_dat . ".base_query_header." . report_field_name); global_temp_dat . ".base_query_header." . report_field_name . ".ssql_column_number" = ssql_column_number; if (@column{"is_expression"}) then ( clone_node(column, global_temp_dat . ".expression_query_header." . report_field_name); global_temp_dat . ".expression_query_header." . report_field_name . ".ssql_column_number" = ssql_column_number; ); ssql_column_number++; ); # # # # Handle the expression_query_header ("v.temp_dat.expression_query_header") # # # # # Note, any expression is part of a subroutine which is created here at runtime. # Variables which are available to any expression are: # 1. All global variables in "v.temp_dat" (table and headers) # 2. int row_number # 3. int number_of_rows # 4. int column_number, # DISABLED --> column_number should not be used in expressions because it is out of user control! # 5. int number_of_days # So the subroutine which is called for each expression has the format: # # subroutine(expr__N(int row_number, int number_of_rows, int number_of_days), ( # >>>> the expression of the report field <<< # )); # # # Set subroutine(s) and subroutine call(s) # # string expr_subroutine; string expr_subroutine_name; node expr_subroutine_node; string expr_subroutine_call; foreach column "v.temp_dat.expression_query_header" ( # # Create, compile and evaluate the subroutine definition # expr_subroutine_name = "expr__" . node_name(column); expr_subroutine = "subroutine(" . expr_subroutine_name . "("; expr_subroutine .= "int row_number,"; expr_subroutine .= "int number_of_rows,"; # expr_subroutine .= "int ssql_column_number,"; --> DISABLED, ONLY REQUIRED IF WE ENABLE cell_by_number() expr_subroutine .= "int number_of_days"; expr_subroutine .= "), ("; expr_subroutine .= @column{"expression"}; expr_subroutine .= "));"; debug_message("\n#### expr_subroutine string:\n" . expr_subroutine . "\n"); set_subnode_value(column, "compiled_expr_subroutine", compile(expr_subroutine)); # Note, in case of expressions the node "expr_subroutine_node" requires to be # the node_value and not just a node reference! expr_subroutine_node = node_value(subnode_by_name(column, "compiled_expr_subroutine")); evaluate(expr_subroutine_node); # # Create and compile the subroutine call # int row_number = 0; # Required by compile() int number_of_rows = 0; # Required by compile() int number_of_days = 0; # Required by compile() expr_subroutine_call = expr_subroutine_name . "(row_number, number_of_rows, number_of_days);"; set_subnode_value(column, "compiled_expr_subroutine_call", compile(expr_subroutine_call)); ); # debug_message("\n\n expression header with compiled expressions:\n" . node_as_string("v.temp_dat.expression_query_header") . "\n\n"); )); # # # # expression_handling_update_aggregation_values_in_query_header() # # # This subroutines evaluates expressions in the base_query_header, # the values to evaluate are already part of the base_query_header, # they are remainder_value, average_value, min_value, max_value and total_value # # # KHP 15/Feb/2012 - not anymore in use. Remainder, sum, min and max are now always # done by the SSQL query, this doesn't require nor should it use expression evaluation. # Expression evaluation is only done for the sum if use_overview_for_total is true, this # is done automatically when applying query_db_for_report(). #subroutine(expression_handling_update_aggregation_values_in_query_header( # node base_query_header, # string processing_node_name, # int number_of_days), ( # # # processing_node_name is: remainder_value | average_value | min_value | max_value | total_value # # debug_message("\n\n#### expression_handling_update_aggregation_values_in_query_header() START \n\n"); # # # debug_message("\n" . node_as_string(base_query_header) . "\n"); # # node column; # # string report_field_name; # int ssql_column_number; # # # float column_value; # # # # # # # Create a global "v.temp_dat" node from which we can access all relevant data # # # # # # delete_node("v.temp_dat"); # v.temp_dat = ""; # # v.temp_dat.is_ssql_table = false; # v.temp_dat.row_number = 0; # v.temp_dat.processing_node_name = processing_node_name; # Required in cell_by_name() # # # # # Set base_query_header as global object # # # # v.temp_dat.global_base_query_header = ""; # set_node_type("v.temp_dat.global_base_query_header", "node"); # v.temp_dat.global_base_query_header = base_query_header; # # # # # # # # Create a base query header and expression query header where we can access the columns by report field name # # # # # # # expression_handling_create_expression_headers(base_query_header, "v.temp_dat"); # # # debug_message("\n" . node_as_string("v.temp_dat") . "\n"); # # # # # # # # Set variables which are available in the expression(s) subroutine(s) # # # # # # int row_number = 0; # The value is not relevant here, though the variable is required! # int number_of_rows = 0; # # int number_of_days = 0; # This is set as argument # # foreach column "v.temp_dat.expression_query_header" ( # # # Note, ignore the row visibility helper column because it is not relevant for aggregation values! # # # debug_message("\n" . node_as_string(column) . "\n"); # # if (@column{"is_aggregating_field"} and (node_name(column) ne "internal_table_filter_expression_helper_column")) then ( # # ssql_column_number = @column{"ssql_column_number"}; # # # debug_message("#### ssql_column_number: " . ssql_column_number . "\n"); # # debug_message("#### processing_node_name: " . processing_node_name . "\n"); # # # Set evaluated value in base_query_header # base_query_header . "." . ssql_column_number . "." . processing_node_name = evaluate(@column{"compiled_expr_subroutine_call"}); # # ); # ); # # debug_message("\n\n#### expression_handling_update_aggregation_values_in_query_header() END \n\n"); #)); # # # # expression_handling_apply_table_filter_expression() # # # # KHP 30/Jan/2012 - This replaces expression_handling_update_ssql_table(), it only handles the table filter expression subroutine(expression_handling_apply_table_filter_expression( string profile_name, node base_query_header, string root_table_id, bool use_overview_for_totals, int number_of_days, bool is_handle_progress, # is_handle_progress will be false in export where we don't show any progress and hence shouldn't set any progress! node progress_substep_info), ( debug_message("\n\n#### expression_handling_apply_table_filter_expression() \n"); # This subroutine updates the ssql table for the given table filter expression. # The report field of the table filter expression has already been evaluated by the C++ # in the root table. This part gets the visible rows and generates the filter expression # which is required if use_overview_for_totals is true. # # # Check if there are any rows to evaluate # # table root_table = load_table(root_table_id); int total_rows = table_get_num_rows(root_table); # Unload root_table unload_table(root_table); int final_total_rows = 0; string qs; string row_visibility_cutoff_filter_expression; debug_message("#### total_rows of root_table: " . total_rows . "\n"); if (total_rows > 0) then ( # # Create a temp table where we get the visible rows, this table becomes then the final root_table # string visible_rows_table_id = root_table_id . "_temp_visible_rows"; qs = "CREATE TABLE " . visible_rows_table_id . " "; qs .= "SELECT * "; qs .= "FROM " . root_table_id . " "; qs .= "WHERE internal_table_filter_expression_helper_column = 1"; ssql_query(qs); # # Get the number of visible rows # table visible_rows_table = load_table(visible_rows_table_id); int number_of_visible_rows = table_get_num_rows(visible_rows_table); final_total_rows = number_of_visible_rows; table cutoff_table; debug_message("#### number_of_visible_rows: " . number_of_visible_rows . "\n"); if (total_rows != number_of_visible_rows) then ( # # Handle row visibility cutoff filter expression if we require totals from an overview report # if (use_overview_for_totals and number_of_visible_rows > 0) then ( # We will use a table with the less rows to get the cutoff filter items, so # if there are less rows in the visible rows table we use the table of visible_rows_table_id # if there are less hidden rows than visible rows than we create a new query for the hidden rows # and use the hidden rows table to collect the filter items. int number_of_hidden_rows = total_rows - number_of_visible_rows; bool is_visible_rows_table = (number_of_visible_rows <= number_of_hidden_rows); string cutoff_table_id; int number_of_cutoff_table_rows; if (is_visible_rows_table) then ( number_of_cutoff_table_rows = number_of_visible_rows; # The table already exists cutoff_table_id = visible_rows_table_id; cutoff_table = visible_rows_table; ) else ( # We need to create a new query for the hidden rows number_of_cutoff_table_rows = number_of_hidden_rows; cutoff_table_id = root_table_id . "_temp_hidden_rows"; qs = "CREATE TABLE " . cutoff_table_id . " "; qs .= "SELECT * "; qs .= "FROM " . root_table_id . " "; qs .= "WHERE internal_table_filter_expression_helper_column = 0"; ssql_query(qs); cutoff_table = load_table(cutoff_table_id); ); row_visibility_cutoff_filter_expression = create_row_visibility_cutoff_filter_expression( base_query_header, is_visible_rows_table, cutoff_table, number_of_cutoff_table_rows ); # # Unload cutoff_table, we don't need it anymore # unload_table(cutoff_table); if (!is_visible_rows_table) then ( # Drop the hidden rows table now (the visible rows table is dropped anyway later) ssql_query("DROP TABLE " . cutoff_table_id); ); ) else ( unload_table(visible_rows_table); ); # # Drop the original root table # ssql_query("DROP TABLE " . root_table_id); # # Make the visible_rows_table the new root_table # qs = "CREATE TABLE " . root_table_id . " "; qs .= "SELECT * "; qs .= "FROM " . visible_rows_table_id; ssql_query(qs); # # Drop the visible_rows_table # ssql_query("DROP TABLE " . visible_rows_table_id); ) else ( unload_table(visible_rows_table); ); ); # # # Save row_visibility_cutoff_filter_expression in profiles_cache. # We save the row_visibility_cutoff_filter_expression because it is possible # that we require it to get a new or cached overview query result of an already # cached ssql root table. If a ssql table is already cached we would not be able # to get the row_visibility_cutoff_filter_expression, hence we save it with the # root_table_id. # We also save empty expressions, i.e. if we have 0 rows, to avoid any side effects. # string expression_path = "profiles_cache." . profile_name . ".row_visibility_cutoff_filter_expressions." . root_table_id; expression_path . ".filter_expression" = row_visibility_cutoff_filter_expression; save_node(expression_path); debug_message("#### final_total_rows: " . final_total_rows . "\n"); debug_message("\n\n#### expression_handling_apply_table_filter_expression() END\n"); # Return final_total_rows; )); # KHP 24/Jan/2012 - DISABLED, not anymore in use #subroutine(expression_handling_update_ssql_table( # string profile_name, # node base_query_header, # string root_table_id, # bool use_overview_for_totals, # bool is_report_field_expression, # bool is_table_filter_expression, # int number_of_days, # bool is_handle_progress, # is_handle_progress will be false in export where we don't show any progress and hence shouldn't set any progress! # node progress_substep_info), ( # # debug_message("\n\n#### update_ssql_table() START \n"); # # # Note, load and unload the root_table within this subroutine. # # # # # # # Handle any report field with a sort_by_before_expression_evaluation # # (before we load the root table) # # # # # # expression_handling_check_sort_by_before_expression_evaluation(base_query_header, root_table_id); # # # # # # # Create an output node in which we return the final_total_rows and any row_visibility_cutoff_filter_expression # # # # # # int final_total_rows; # string row_visibility_cutoff_filter_expression; # # # # # # # # # # # # Check if there are any rows to evaluate # # # # # # # # # # table root_table = load_table(root_table_id); # int total_rows = table_get_num_rows(root_table); # # debug_message("#### total_rows of root_table to update: " . total_rows . "\n"); # # # # # # # # # # # If total_rows > 0 # # # # # # # # # # # 2008-12-05 - GMF - Moved this outside the "if", because it is used below the "if", and generates an error if the "if" is false. # int number_of_visible_rows = 0; # # if (total_rows > 0) then ( # # node column; # int ssql_column_number; # string report_field_name; # # # # # # # PROGRESS_SETTER # # # # # # if (is_handle_progress) then ( # prepare_increment_iterative_progress_substep(progress_substep_info); # Required before we use increment_iterative_progress_substep in loop! # ); # # # # # # # # Set varibales which are availabe to the expression(s) subroutine(s) # # # # # # int row_number = 0; # int number_of_rows = total_rows; # # int number_of_days = 0; Already set as argument # # # # # # # # # # Create a global "v.temp_dat" node from which we can access all relevant data # # # # # # # # delete_node("v.temp_dat"); # v.temp_dat = ""; # # v.temp_dat.is_ssql_table = true; # v.temp_dat.row_number = 0; # # # # # Set table as global object # # # # v.temp_dat.global_root_table = ""; # set_node_type("v.temp_dat.global_root_table", "table"); # v.temp_dat.global_root_table = root_table; # # # # # # # Create a base query header and expression query header where we can access the columns by report field name # # # # # # expression_handling_create_expression_headers(base_query_header, "v.temp_dat"); # # # # # # # # # # Set values in the ssql table # # # # # # # # int table_filter_expression_int_value; # bool table_filter_expression_bool_value; # # # Temp TEST # # float cell_value_ori; # # float cell_value_after_expression_evaluation; # # for (row_number = 0; row_number < total_rows; row_number++) ( # # debug_message("\n#### update_ssql_table() - processing row: " . row_number . " of " . total_rows . "\n"); # # # PROGRESS_SETTER # # Handle progress, we set progress for every 25 rows # if (is_handle_progress and (total_rows > 25) and ((row_number % 25) == 0)) then ( # increment_iterative_progress_substep(progress_substep_info, row_number, total_rows); # ); # # # # Set global row_number for cell_by_name()! # v.temp_dat.row_number = row_number; # # foreach column "v.temp_dat.expression_query_header" ( # # ssql_column_number = @column{"ssql_column_number"}; # # debug_message("#### update_ssql_table() - report_field_name: " . @column{"report_field_name"} . "\n"); # # # if (is_table_filter_expression and (node_name(column) eq "internal_table_filter_expression_helper_column")) then ( # # # # # If row visibility expression # # # # # Note, table_filter_expression_int_value does not return 1 although the expression evaluates to TRUE, # # that's why we added table_filter_expression_bool_value! # # table_filter_expression_bool_value = evaluate(@column{"compiled_expr_subroutine_call"}); # # table_filter_expression_int_value = if (table_filter_expression_bool_value) then (1) else (0); # # debug_message("#### table_filter_expression_int_value: " . table_filter_expression_int_value . "\n"); # debug_message("#### table_filter_expression_bool_value: " . table_filter_expression_bool_value . "\n"); # # table_set_cell_value(root_table, row_number, ssql_column_number, table_filter_expression_int_value); # # # Count the visible rows so that we know if there are any rows to hide # # if (table_filter_expression_int_value == 1) then ( # number_of_visible_rows++; # ); # ) # else ( # # # # # If report field expression # # # # if (@column{"is_aggregating_field"}) then ( # # # debug_message("#### update_ssql_table() - evaluated column value (" . node_name(column) . "): " . table_get_cell_value(root_table, row_number, ssql_column_number) . "\n"); # # # cell_value_ori = table_get_cell_value(root_table, row_number, ssql_column_number); # # # debug_message("#### update_ssql_table() - evaluation result: " . evaluate(@column{"compiled_expr_subroutine_call"}) . "\n"); # # table_set_cell_value(root_table, row_number, ssql_column_number, evaluate(@column{"compiled_expr_subroutine_call"})); # # # cell_value_after_expression_evaluation = table_get_cell_value(root_table, row_number, ssql_column_number); # # debug_message("#### update_ssql_table() - cell_value_ori: " . cell_value_ori . "\n"); # # debug_message("#### update_ssql_table() - cell_value_after_expression_evaluation: " . cell_value_after_expression_evaluation . "\n"); # ) # else ( # # table_set_cell_string_value(root_table, row_number, ssql_column_number, evaluate(@column{"compiled_expr_subroutine_call"})); # ); # ); # ); # ); # # # # # # # Clean up # # # # # # delete_node("v.temp_dat"); # # ); # # debug_message("\n\n#### update_ssql_table() - is_table_filter_expression: " . is_table_filter_expression . "\n"); # debug_message("#### update_ssql_table() - total_rows: " . total_rows . "\n"); # debug_message("#### update_ssql_table() - number_of_visible_rows: " . number_of_visible_rows . "\n"); # # # # # # # # # # Handle row_visibility # # # # # # # # string qs; # # # if (is_table_filter_expression and (total_rows != number_of_visible_rows) and (number_of_visible_rows > 0)) then ( # # KHP 25/Jan/2011 - if the number_of_visible_rows is 0 and is different from the total_rows # # then we must still save this as a new table, otherwise we get an unmodified root_table which indicates more # # than 0 rows although there aren't any. # # if (is_table_filter_expression and (total_rows != number_of_visible_rows)) then ( # # debug_message("#### Handle row_visibility is TRUE \n"); # # # # # # # Unload root_table # # # # # # unload_table(root_table); # # # # # # # Create a temp table where we get the visible rows, this table becomes then the final root_table # # # # # # string visible_rows_table_id = root_table_id . "_temp_visible_rows"; # # qs = "CREATE TABLE " . visible_rows_table_id . " "; # qs .= "SELECT * "; # qs .= "FROM " . root_table_id . " "; # qs .= "WHERE internal_table_filter_expression_helper_column = 1"; # # ssql_query(qs); # # # # # # # # # # Handle row visibility cutoff filter expression if we require totals from an overview report # # # # # # # # if (use_overview_for_totals) then ( # # # We will use a table with the less rows to get the cutoff filter items, so # # if there are less rows in the visible rows table we use the table of visible_rows_table_id # # if there are less hidden rows than visible rows than we create a new query for the hidden rows # # and use the hidden rows table to collect the filter items. # # int number_of_hidden_rows = total_rows - number_of_visible_rows; # bool is_visible_rows_table = (number_of_visible_rows <= number_of_hidden_rows); # # string cutoff_table_id; # int number_of_cutoff_table_rows; # # if (is_visible_rows_table) then ( # # number_of_cutoff_table_rows = number_of_visible_rows; # # # The table already exists # cutoff_table_id = visible_rows_table_id; # ) # else ( # # # We need to create a new query for the hidden rows # # number_of_cutoff_table_rows = number_of_hidden_rows; # # cutoff_table_id = root_table_id . "_temp_hidden_rows"; # # qs = "CREATE TABLE " . cutoff_table_id . " "; # qs .= "SELECT * "; # qs .= "FROM " . root_table_id . " "; # qs .= "WHERE internal_table_filter_expression_helper_column = 0"; # # ssql_query(qs); # ); # # table cutoff_table = load_table(cutoff_table_id); # # row_visibility_cutoff_filter_expression = create_row_visibility_cutoff_filter_expression( # base_query_header, # is_visible_rows_table, # cutoff_table, # number_of_cutoff_table_rows # ); # # # # # # Unload cutoff_table, we don't need it anymore # # # # unload_table(cutoff_table); # # if (!is_visible_rows_table) then ( # # # Drop the hidden rows table now (the visible rows table is dropped anyway later) # ssql_query("DROP TABLE " . cutoff_table_id); # ); # ); # # # # # # # Drop the original root table # # # # # # ssql_query("DROP TABLE " . root_table_id); # # # # # # Make the visible_rows_table the new root_table # # # # qs = "CREATE TABLE " . root_table_id . " "; # qs .= "SELECT * "; # qs .= "FROM " . visible_rows_table_id; # # ssql_query(qs); # # # # # Drop the visible_rows_table # # # # ssql_query("DROP TABLE " . visible_rows_table_id); # ) # else ( # # # # # # # Unload root_table # # # # # # unload_table(root_table); # # debug_message("#### Handle row_visibility is FALSE \n"); # ); # # # # # # # # Get final total_rows # # # # # # if (!is_table_filter_expression) then ( # final_total_rows = total_rows; # ) # else ( # final_total_rows = number_of_visible_rows; # ); # # # # # # # # Save row_visibility_cutoff_filter_expression if is_table_filter_expression is true # # We save the row_visibility_cutoff_filter_expression because it is possible # # that we require it to get a new or cached overview query result of an already # # cached ssql root table. If a ssql table is already cached we would not be able # # to get the row_visibility_cutoff_filter_expression, hence we save it with the # # root_table_id. # # We also save empty expressions, i.e. if we have 0 rows, to avoid any side effects, # # respectively if is_table_filter_expression then there must be an expression # # in profiles_cache. # # # # # # if (is_table_filter_expression) then ( # # string expression_path = "profiles_cache." . profile_name . ".row_visibility_cutoff_filter_expressions." . root_table_id; # # expression_path . ".filter_expression" = row_visibility_cutoff_filter_expression; # save_node(expression_path); # ); # # # # # # # Return final_total_rows # # # # # # final_total_rows; # # debug_message("#### update_ssql_table END \n"); #)); # # # # # Info for a better understanding of how expressions are evaluated # # subroutine model (one subroutine per report field with expression) # # subroutine(expr__N), ( # int row_number, # int number_of_rows, # int number_of_days), ( # # # The variables row_number and number_of_days are used by the cell_by_name() subroutine # # Return value is the value of the expression in the report_field. # # cell_by_name(row_number, 'hits') / 2; # )); #