#!/usr/bin/perl

use strict;
use warnings;
use open ':std', ':encoding(UTF-8)';

use JSON::XS;
use FileHandle;
use Text::CSV::Easy(qw/csv_build csv_parse/);

use lib "$ENV{'HOME'}/sandbox/collab/jgetter/perllib";

use Orchard::DB::Connect;
my $dbh = Orchard::DB::Connect->connect('orchrpt');
my $lfh = FileHandle->new( 'ACC1988-labels.txt', 'r' );
my $labels;
while (<$lfh>) {
    chomp;
    $labels->{$_}->{'vendor'} = $_;
}
$lfh->close;

my $sth = mysth($dbh);
$sth->execute();
if ( $sth->rows ) {
    while ( my $r = $sth->fetchrow_arrayref() ) {
        my ( $l, $p, $c, $f, $e ) = @{$r};
        $labels->{$l}->{'currency'}     = $c if $c ne 'USD';
        $labels->{$l}->{'period'}->{$p} = $e;
        $labels->{$l}->{'forex'}->{$p}  = $f;
        $labels->{$l}->{'total'}   += $e;
        $labels->{$l}->{'fxtotal'} += $f;
    }
}

my $lines;
foreach my $k ( sort { $a <=> $b } keys %{$labels} ) {
    my $href = $labels->{$k};
    next if not exists $href->{'total'} or $href->{'total'} < .1;

    my $sum   = sprintf( "%.2f", $href->{'total'} );
    my $fxsum = sprintf( "%.2f", $href->{'fxtotal'} );

    my $usd = [ map { exists $href->{'period'}->{$_} ? $href->{'period'}->{$_} : 0 } ( 235 ... 245 ) ];
    my $frx = [ map { exists $href->{'forex'}->{$_}  ? $href->{'forex'}->{$_}  : 0 } ( 235 ... 245 ) ];
    $usd = csv_build( ( 'USD', @{$usd}, $sum ) );
    $frx = csv_build( ( $href->{'currency'}, @{$frx}, $fxsum ) );
    push @{ $lines->{$sum} }, "$k,$usd\n,$frx";
}

print "VendorId,Currency,235,236,237,238,239,240,241,242,243,244,245,Total\n";
foreach my $dk ( sort {$b <=> $a} keys %{$lines} ) {
	map { print "$_\n", } @{$lines->{$dk}};
}

sub mysth {
    my $dbh = shift;
    my $sql = (
        qq{
SELECT
    s.vendor_id,
    s.period_id,
    s.curcode,
    ROUND(( s.ma_sum_amount / usdXR )-
    (s.expenses+s.adjustments),2), 
    ROUND(s.ma_sum_amount - ((s.expenses+s.adjustments)*s.usdXR),2) usdDelta 
FROM (
    SELECT
        vendor.vendor_id,
        vendor.period_id,
        cur.ISO_4217_code curcode,
        ROUND(IFNULL((
            SELECT
                SUM(rma.amount*IFNULL((
                    SELECT xr.exchange_rate
                    FROM currency_exchange_rates xr
                    WHERE 1
                    AND xr.period_id=expense_ma.apply_to_period_id
                    AND xr.currency_from_id=rma.currencies_id
                    AND xr.currency_to_id=vendor.currency_id
                ),1))
            FROM
                manual_adjustment expense_ma
            JOIN
                release_manual_adjustment rma ON rma.vendor_manual_adjustment_id=expense_ma.id
            WHERE 1 
                AND expense_ma.category_id=65
                AND expense_ma.parent_id=vendor.vendor_id       
                AND expense_ma.apply_to_period_id=vendor.period_id
        ),0),2) expenses,
        ROUND(IFNULL((
            SELECT
                SUM(adjustment_ma.amount_in_original_currency*IFNULL((        
                    SELECT xr.exchange_rate
                    FROM currency_exchange_rates xr
                    WHERE 1
                    AND xr.period_id=adjustment_ma.apply_to_period_id
                    AND xr.currency_from_id=adjustment_ma.currencies_id
                    AND xr.currency_to_id=vendor.currency_id
                    ),1)
                )
            FROM
                manual_adjustment adjustment_ma
            WHERE 1
                AND adjustment_ma.category_id!=65
                AND adjustment_ma.parent_id=vendor.vendor_id
                AND adjustment_ma.apply_to_period_id=vendor.period_id
        ),0),2) adjustments,
        usd.exchange_rate usdXR,
        IFNULL(SUM(ma.amount),0) ma_sum_amount,
        IFNULL(SUM(ma.amount_in_original_currency),0) ma_fx_sum
    FROM
        booked_vendor_contract_snapshot vendor
    JOIN
    	currencies cur ON cur.id=vendor.currency_id
    JOIN
        currency_exchange_rates usd ON usd.period_id=vendor.period_id
        AND usd.currency_from_id=vendor.currency_id
        AND usd.currency_to_id=1
    LEFT JOIN
        manual_adjustment ma ON ma.parent_id=vendor.vendor_id
        AND ma.apply_to_period_id=vendor.period_id
    WHERE 1
        #AND vendor.vendor_id IN ( 26561,25954,28533 )
        AND vendor.period_id > 234
        AND vendor.currency_id<>1
    GROUP BY 
        vendor.vendor_id,vendor.period_id
) s
}
    );
    $dbh->prepare($sql);
}