my %ave12_cost;

while (my @row = $sth_2->fetchrow_array) {
	
	# @row has 2 items - item AND average cost for the item
	
	# remove trailing spaces	
	s/\s+$// for @row;

	# save item and average cost in a hash whose key is 'item'
	# 123.4 becomes 123.40 AND 456 becomes 456.00
	$ave12_cost{ $row[0] } = sprintf "%.2f", $row[1];
}

$sth_2->finish();

my @col = qw(
agc
market
mkt_1
analysis_code
item
customer
key_val2
key_val3
terms_code
audit_seq
aud_date
aud_time
opr_id
action
unit_price
profit
percent_profit
cur_per_month
);

my @data = [ @col ];

while (my $href = $sth_3->fetchrow_hashref) { 

	# remove trailing spaces	
	s/\s+$// for values %$href;
	
	# do calculations	
	$href->{mkt_1} = substr($href->{market}, 0, 1) == 1 ? "A&D" : "MCP";
	$href->{profit} = $href->{unit_price} - $ave12_cost{$href->{item}};
	$href->{percent_profit} = sprintf "%.1f", ($href->{profit} / $href->{unit_price}) * 100;
	. . . .
	
	# add to an array for output to Excel
	# (Data added in order of '@col' array)
	push @data, [ @$href{ @col } ];
}

$sth_3->finish();

my @transposed = transpose( @data );

my $file = 'sales.csv';
open my $out, ">", $file or die "Unable to create $file. $!";

# print to CSV file
for my $row (@transposed) {
	print $out join(",", @$row), "\n";
}

close $out or die "Unable to close $file. $!";


sub transpose {
	my (@data, @trans) = @_;
	for my $r (0 .. $#data) {
		for my $c (0 .. $#{ $data[$r] }) {
			$trans[$c][$r] = $data[$r][$c];
		}
	}
	return @trans;
}