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

include "templates.util.date_time.date_time_formatting";

subroutine(export_table(
	string profile_name,
	bool is_log_detail,
	bool is_extended_log_detail,
	node base_query_header,
	table main_table,
	bool is_xref_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() \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;
		string final_display_format_type;
		
		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 (

								final_display_format_type = @column{"final_display_format_type"};
								
								if (final_display_format_type ne "") then (
									column_value = format(column_value, final_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
	#
	#

	node miscellaneous = profile . ".statistics.miscellaneous";
	string csv_delimiter = ",";
	
	if (miscellaneous?{"csv_delimiter"} and (@miscellaneous{"csv_delimiter"} ne "")) then (
	
		csv_delimiter = @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");

	#
	# Handle empty value (Added in 8.7.3)
	#

	bool use_custom_empty_value = false;
	string custom_empty_value = "";

	if (miscellaneous?{"use_custom_csv_empty_value"} and @miscellaneous{"use_custom_csv_empty_value"}) then (

		use_custom_empty_value = true;
		custom_empty_value = @miscellaneous{"custom_csv_empty_value"};
	);

	
	#
	#
	# 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 Windows we get an empty line between exported rows
#			if (_PLATFORM eq "Windows") 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;
	string display_format_type_export;
	string final_display_format_type;
	
	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"};
		display_format_type = @column{"display_format_type"};
		display_format_type_export = @column{"display_format_type_export"};
		
		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;
			);
		)
		else if (show_column) then (
		
			# text column
			csv_line .= escape_csv_value(column_label, csv_delimiter) . csv_delimiter;
		);


		#
		#
		# Check if we format the column value
		#
		#

		# KHP 27/June/2013 - Added display_format_type_export.
		# If display_format_type_export is defined then we use display_format_type_export.
		# Else use auto mode. Auto uses the regular display format type for all text fields
		# if no display_format_type_export. For aggregating fields only epoc_to_date_time
		# becomes formatted if no display_format_type_export is defined.

		final_display_format_type = "";

		if (display_format_type_export ne "") then (

			final_display_format_type = display_format_type_export;
		)
		else (

			# No display_format_type_export defined, use auto mode.

			if (is_aggregating_field) then (

				# Auto mode for aggregating fields.
				# Format fields if regular display format type is epoc_to_date_time.
				# Note, disabled formatting of float values in auto-mode for 8.6.3+
				# for clarity.

				if (display_format_type eq "epoc_to_date_time") then (

					final_display_format_type = display_format_type;
				);
			)
			else (

				# Auto mode for non-aggregating fields.
				# Format fields if regular display format type is not of type string.
				if (display_format_type ne "string") then (

					final_display_format_type = display_format_type;
				);
			);
		);

		set_subnode_value(column, "final_display_format_type", final_display_format_type);

		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");

	if (total_rows > 0) then (

		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;

		string database_field_number;
    	int item_number;

    	if (is_xref_table) then (

    		# Note, the xref table only contains the required rows, i.e. row 0-9,
            # though starting_row may be 11 and ending_row 20.
    		ssql_row_number = 0;
    		ssql_ending_row = ending_row - starting_row;
    	)
		else 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"};

#				debug_message("\n\n\n#### report_field_name: " . report_field_name . "\n");
#				debug_message("#### is_aggregating_field: " . is_aggregating_field . "\n");

				# 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.

				final_display_format_type = @column{"final_display_format_type"};

#				debug_message("#### final_display_format_type: " . final_display_format_type . "\n");

				show_column = @column{"show_column"};

#				debug_message("#### show_column: " . show_column . "\n");

				if (is_aggregating_field) then (

					show_percent_column = @column{"show_percent_column"};

					if (show_column) then (

						column_value = table_get_cell_value(main_table, ssql_row_number, ssql_column_number);

						if (final_display_format_type ne "") then (

							column_value = format(column_value, final_display_format_type);
#							debug_message("#### value after applying display_format_type: " . column_value . "\n");
						);

						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 (

						if (!is_xref_table) then (

							column_value = table_get_cell_string_value(main_table, ssql_row_number, ssql_column_number);
						)
						else (

							# This is a query from an xref table which does not contain string values.
							# Get first the item number, then the string for the item number.

							database_field_number = @column{"database_field_number"};
							item_number = table_get_cell_value(main_table, ssql_row_number, ssql_column_number);
							column_value = database_itemnum_to_item(database_field_number, item_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, 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("#### final_display_format_type: " . final_display_format_type . "\n");

					if (final_display_format_type ne "") then (

						if (final_display_format_type ne "date_time") then (

							column_value = format(column_value, final_display_format_type);
						)
						else (

							column_value = format_date_time(column_value);
						);

#						debug_message("#### column_value after format(): " . column_value . "\n\n\n");
					);

					if (use_custom_empty_value and (column_value eq "(empty)")) then (
						# Set custom empty value
						column_value = custom_empty_value;
					);

					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) 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 output 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);
	);
));