#
#
# export_table()
#
# This subroutine exports a single table. 
# The provided table is already updated and sorted.
#
#

subroutine(export_table(
	string profile_name,
	bool is_log_detail,
	bool is_extended_log_detail,
	node base_query_header,
	table main_table,
	int starting_row,
	int ending_row,
	int total_rows,
	bool export_average,
	bool export_min,
	bool export_max,
	bool export_total,
	string end_of_line,
	string csv_file), (

	debug_message("\n\n#### export_table() START \n\n\n");
	
	# debug_message("\n\n" . node_as_string(base_query_header) . "\n\n");
	
	node profile = "profiles." . profile_name;
	
	#
	#
	#
	# Utilities
	#
	#
	#
	
	subroutine(escape_csv_value(string s, string csv_delimiter), (
	
		if (contains(s, csv_delimiter) or contains(s, '"')) then (
				s = '"' . s . '"';
		);
	
		s;
	));
	
	subroutine(export_table_add_aggregation_row(
		node base_query_header,
		string aggregation_type,
		string aggregation_label,
		string csv_delimiter,
		string end_of_line,
		bool export_to_file,
		int file_handle,
		bool convert_export_charset,
		string convert_export_from_charset,
		string convert_export_to_charset), (
		
		# debug_message("\n\n#### export_table_add_aggregation_row() START \n");
		# debug_message("\n" . node_as_string(base_query_header) . "\n");
		
		node column;
		bool show_column;
		bool show_percent_column;
		bool is_aggregating_field;
		bool show_aggregation;
		bool format_aggregating_value;
		
		string column_value;
		float percentage_value;
		
		bool label_has_been_set = false;
		string csv_value;
		string csv_line;
		
		foreach column base_query_header (
		
			if (label_has_been_set) then (
			
				is_aggregating_field = @column{"is_aggregating_field"};
				
				if (is_aggregating_field) then (
				
					show_aggregation = @column{("show_" . aggregation_type . "_value")};
				
					show_column = @column{"show_column"};
					show_percent_column = @column{"show_percent_column"};
					
					if (show_column) then (
					
						if (show_aggregation) then (
					
							column_value = @column{(aggregation_type . "_value")};
							
							if (aggregation_type ne "average") then (
						
								format_aggregating_value = @column{"format_aggregating_value"};
								
								if (format_aggregating_value) then (
									column_value = format(column_value, @column{"display_format_type"});
								);
							)
							else (
							
								# format average
								column_value = format(column_value, "float");
							);
							
							csv_line .= escape_csv_value(column_value, csv_delimiter) . csv_delimiter;
						)
						else (
						
							csv_line .= "" . csv_delimiter;
						);
					);
					
					if (show_percent_column) then (
					
						if (show_aggregation) then (
						
							if (aggregation_type eq "total") then (
								percentage_value = 100.00;
							)
							else (
								percentage_value = (100 / @column{"total_value"}) * @column{(aggregation_type . "_value")};
							);
					
							column_value = format(percentage_value, "%.1f");
						
	     					csv_line .= escape_csv_value(column_value, csv_delimiter) . csv_delimiter;
	     				)
	     				else (
	     				
	     					csv_line .= "" . csv_delimiter;
	     				);
					);
				)
				else (
				
					csv_line .= "" . csv_delimiter;
				);
			)
			else (
				# Set the label
			
				csv_line = escape_csv_value(aggregation_label, csv_delimiter) . csv_delimiter;
				label_has_been_set = true;
			);
		);
	
		csv_line = replace_last(csv_line, csv_delimiter, end_of_line);
		
		if (convert_export_charset) then (
			csv_line = convert_charset(csv_line, convert_export_from_charset, convert_export_to_charset);
		);
	
		if (export_to_file) then (
			
			write_string_to_file(file_handle, csv_line);
		)
		else (
		
			echo(csv_line);
		);
	));
	
	#
	#
	# Check if we export to a file
	#
	#
	
	bool export_to_file = if (csv_file ne "") then (true) else (false);
	int file_handle;
	
	if (export_to_file) then (
		
		write_file(csv_file, "");
		file_handle = open_file(csv_file, "w");
	);


	#
	#
	# Get delimiter
	#
	#

	string csv_delimiter = ",";
	
	if (?(profile . ".statistics.miscellaneous.csv_delimiter") and (@(profile . ".statistics.miscellaneous.csv_delimiter") ne "")) then (
	
		csv_delimiter = @(profile . ".statistics.miscellaneous.csv_delimiter");
	);
	
	# KHP 15/Feb/2011 - Handle horizontal tab "\t"
	# Allow multiple instances of \t, i.e. \t\t or \t-\t
	if (contains(csv_delimiter, "\t")) then (
		csv_delimiter = replace_all(csv_delimiter, "\t", ascii_to_char(9));
	);
	
	# debug_message("#### csv_delimiter: " . csv_delimiter . "\n");
	
	#
	#
	# Check end_of_line (if not specified in command line)
	#
	#
	# Note, echo() does not require to set an end_of_line
	#
	
	if (export_to_file) then (
	
		if (end_of_line eq "") then (
	
			end_of_line = "\n";
		
			#*
				r7-1 code DISABLED
				 because when using "\r\n" for Win32 we get an empty line between exported rows
				if (_PLATFORM eq "Win32") then (
					end_of_line = "\r\n";
				)
				else (
					end_of_line = "\n";
				);
			*#
		)
		else (
		
			# end_of_line has been defined in command line
			end_of_line = prepare_for_final_output(end_of_line);
		);
	);
	
	
	#
	#
	# Check charset conversion
	#
	#
		
	bool convert_export_charset = if (?(profile . ".output.convert_export_charset")) then (@(profile . ".output.convert_export_charset")) else (false);
	string convert_export_from_charset;
	string convert_export_to_charset;
	
	if (convert_export_charset) then (
	
		convert_export_from_charset = @(profile . ".output.convert_export_from_charset");
		convert_export_to_charset = @(profile . ".output.convert_export_to_charset");
	);
		
	debug_message("#### convert_export_charset: " . convert_export_charset . "\n");
	debug_message("#### convert_export_from_charset: " . convert_export_from_charset . "\n");
	debug_message("#### convert_export_to_charset: " . convert_export_to_charset . "\n");
	
	
	#		
	#
	#
	# Export the table
	#
	#
	#
	
	
	#
	# 
	# CSV header
	#
	#
	
	string csv_line;
	
	int header_column_number = 0;
	node column;
	string column_label;
	bool is_aggregating_field;
	bool show_column;
	bool show_percent_column;
	string display_format_type;
	bool format_aggregating_value;
	
	foreach column base_query_header (

		column_label = get_expanded_label(@column{"column_label"});
		is_aggregating_field = @column{"is_aggregating_field"};
		show_column = @column{"show_column"};
		
		if (is_aggregating_field) then (
		
			show_percent_column = @column{"show_percent_column"};
			
			if (show_column) then (
			
				csv_line .= escape_csv_value(column_label, csv_delimiter) . csv_delimiter;
			);
			
			if (show_percent_column) then (
			
				csv_line .= escape_csv_value(column_label . " (%)", csv_delimiter) . csv_delimiter;
			);
			
			
			#
			#
			# Check if we format the aggregating value and set a 
			# format_aggregating_value node so that we don't need
			# to recheck it for each row later.
			#
			#
			
			# Note, we format only float values for now
			
			display_format_type = @column{"display_format_type"};
			
			if (display_format_type eq "float") then (
				
				format_aggregating_value = true;
			)
			else (
			
				format_aggregating_value = false;
			);
			
			set_subnode_value(column, "format_aggregating_value", format_aggregating_value);
		)
		else if (show_column) then (
		
			# text column
		
			csv_line .= escape_csv_value(column_label, csv_delimiter) . csv_delimiter;
		);
		
		header_column_number++;

	); # foreach column base_query_header
	
	csv_line = replace_last(csv_line, csv_delimiter, end_of_line);
	
	if (convert_export_charset) then (
		csv_line = convert_charset(csv_line, convert_export_from_charset, convert_export_to_charset);
	);
	
	if (export_to_file) then (
		
		write_string_to_file(file_handle, csv_line);
	)
	else (
	
		echo(csv_line);
	);
	
	
	#
	#
	# CSV rows
	#
	#
	
	
	# debug_message("\n CSV rows - base_query_header" . node_as_string(base_query_header) . "\n\n");
	
	debug_message("#### export_table() - starting_row: " . starting_row . "\n");
	debug_message("#### export_table() - ending_row: " . ending_row . "\n");
	debug_message("#### export_table() - total_rows: " . total_rows . "\n");
	
	# debug_message("#### export_table() - actual number of rows in table: " . table_get_num_rows(main_table) . "\n");
	

	node column;
	int ssql_column_number;
	string report_field_name;
	string table_field_type;
	string category;
	
	string column_value;
	
	float percentage_value;
	
	int ssql_row_number = 0;
	int ssql_ending_row = 0;
	bool is_date_time_in_epoc;
	
	if (total_rows > 0) then (
	
		if (!is_log_detail or is_extended_log_detail) then (
		
			ssql_row_number = starting_row - 1;
			ssql_ending_row = ending_row - 1;
		)
		else (
		
			# log detail table with limited number or rows, i.e:
			# starting_row = 101
			# ending_row = 200
			# In this case the table contains 100 rows, 0 - 99
			
			ssql_row_number = 0;
			ssql_ending_row = ending_row - starting_row;
		);
	);
	
	# debug_message("\n#### export_table() - ssql_row_number: " . ssql_row_number . "\n");
	# debug_message("#### export_table() - ssql_ending_row: " . ssql_ending_row . "\n\n");
	
	for (ssql_row_number; ssql_row_number <= ssql_ending_row; ssql_row_number++) (
	
		# debug_message("#### export_table() - exporting row: " . ssql_row_number . "\n");
	
		csv_line = "";
	
		foreach column base_query_header (
		
			ssql_column_number = node_name(column);
			report_field_name = @column{"report_field_name"};
			table_field_type = @column{"table_field_type"};
			
			is_aggregating_field = @column{"is_aggregating_field"};
			
			# Note, we have to apply the display_format_type to
			# all report fields because the display_format_type is also
			# used for hidden field values in RBAC.
			
			display_format_type = @column{"display_format_type"};
			
			show_column = @column{"show_column"};
			
			if (is_aggregating_field) then (
						
				show_percent_column = @column{"show_percent_column"};
				format_aggregating_value = @column{"format_aggregating_value"};
				
				if (show_column) then (
				
					is_date_time_in_epoc = @column{"is_date_time_in_epoc"};
					
					if (!is_date_time_in_epoc) then (
				
						column_value = table_get_cell_value(main_table, ssql_row_number, ssql_column_number);
					)
					else (
						column_value = epoc_to_date_time(table_get_cell_value(main_table, ssql_row_number, ssql_column_number));
						format_aggregating_value = true;
					);
					
					if (format_aggregating_value) then (
						
						column_value = format(column_value, display_format_type);
					);
					
					csv_line .= escape_csv_value(column_value, csv_delimiter) . csv_delimiter;
				);
				
				if (show_percent_column) then (
				
					percentage_value = (100 / @column{"total_value"}) *  table_get_cell_value(main_table, ssql_row_number, ssql_column_number);
					
					# debug_message("\n\n#### percentage_value: " . percentage_value . "\n\n");
					
					column_value = format(percentage_value, "%.1f");
										
					csv_line .= escape_csv_value(column_value, csv_delimiter) . csv_delimiter;
				);
			)
			else if (show_column) then (
			
				# text column
			
				if (table_field_type eq "database_itemnum" or table_field_type eq "custom_itemnum") then (
				
					column_value = table_get_cell_string_value(main_table, ssql_row_number, ssql_column_number);
				)
				else (
				
					# This is a numerical database field, get value with table_get_cell_value()
					category = @column{"category"};
				
					if (category ne "date_time") then (
					
						column_value = table_get_cell_value(main_table, ssql_row_number, ssql_column_number);
					)
					else (
					
						# This must be a date_time timestamp (log_detail report element) which we have
						# to convert from epoc (int) to date_time
				
						column_value = epoc_to_date_time(table_get_cell_value(main_table, ssql_row_number, ssql_column_number));
					);
				);
				
				# debug_message("\n\n\n#### report_field_name: " . report_field_name . "\n");
				# debug_message("\n\n#### column_value: " . column_value . "\n");
				# debug_message("#### display_format_type: " . display_format_type . "\n");
				
				if (display_format_type ne "" and display_format_type ne "string") then (
					# debug_message("#### apply display_format_type: YES \n");
					column_value = format(column_value, display_format_type);
					# debug_message("#### column_value after format(): " . column_value . "\n\n\n");
				);
				
				csv_line .= escape_csv_value(column_value, csv_delimiter) . csv_delimiter;
			);
		);
		
		
		csv_line = replace_last(csv_line, csv_delimiter, end_of_line);
		
		if (convert_export_charset) then (
			csv_line = convert_charset(csv_line, convert_export_from_charset, convert_export_to_charset);
		);
		
		if (export_to_file) then (
		
			write_string_to_file(file_handle, csv_line);
		)
		else (
		
			echo(csv_line);
		);
	);
	
	#
	#
	# CSV aggregation values
	#
	#
	
	if (!is_log_detail and (total_rows > 0)) then (
	
		if (export_average) then (
		
			export_table_add_aggregation_row(
				base_query_header,
				"average",
				lang_stats.export.average,
				csv_delimiter,
				end_of_line,
				export_to_file,
				file_handle,
				convert_export_charset,
				convert_export_from_charset,
				convert_export_to_charset
			);
		);
		
		if (export_min) then (
		
			export_table_add_aggregation_row(
				base_query_header,
				"min",
				lang_stats.export.min,
				csv_delimiter,
				end_of_line,
				export_to_file,
				file_handle,
				convert_export_charset,
				convert_export_from_charset,
				convert_export_to_charset
			);
		);
		
		if (export_max) then (
		
			export_table_add_aggregation_row(
				base_query_header,
				"max",
				lang_stats.export.max,
				csv_delimiter,
				end_of_line,
				export_to_file,
				file_handle,
				convert_export_charset,
				convert_export_from_charset,
				convert_export_to_charset
			);
		);
		
		if (export_total) then (
		
			export_table_add_aggregation_row(
				base_query_header,
				"total",
				lang_stats.export.total,
				csv_delimiter,
				end_of_line,
				export_to_file,
				file_handle,
				convert_export_charset,
				convert_export_from_charset,
				convert_export_to_charset
			);
		);
	);
	
	# Don't output any data other then CVS lines because the ouput stream could be i.e. piped into a script.
	# So the line below need to remain disabled, respectively has been changed to debug output only!
	# echo("\nExported row " . starting_row . "-" . ending_row . " of " . total_rows . "\n");
	debug_message("\n#### Exported row " . starting_row . "-" . ending_row . " of " . total_rows . "\n");
	
	if (export_to_file) then (
	
		close_file(file_handle);
	);
));