#!/usr/bin/perl
#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
use strict;

#use warnings;

use Spreadsheet::WriteExcel;
require Spreadsheet::WriteExcel::Big;

use File::Path;
use Data::Dumper;
use Getopt::Std;
use Carp;

use lib '/app/tools/common/lib';
use Common::Assert;
use Common::FormObject;
use Common::RSMath;
use Common::Locale;
use Common::Client;

use lib '/app/tools/rps/lib';
use RPS::Statement::Mechanical::StatementFull;
use RPS::Statement::Mechanical::StatementLicenseTransactionList;
use RPS::Payor::Payor;
use RPS::Publisher::Publisher;
use RPS::Mechanical::MechanicalRun;
use RPS::DB::Item::Label;
use RPS::DB::Item::ContractRateType;
use RPS::DB::Item::IncomeSource;
use RPS::DB::Item::Region;
use RPS::DB::Item::Channel;
use RPS::DB::Item::PriceLevel;
use RPS::DB::Item::ProductType;
use RPS::DB::Item::TrackLicense;
use RPS::DB::Item::MechanicalStatement;

use lib '/app/tools/rps/bin/statements';
use Formats;

$SIG{__DIE__} = \&Carp::confess;

# This determines how much output we spew forth to STDERR.
# The user can set this with the -V command-line argument.
#
my $gVerbosityLevel = 1;

# We'll store formatting info here, once we have a worbook object to work with.
#
my $formats;

# Parse the command-line, then create the PDF!
#
my %options;
parseCommandLine( \%options );

createJumboStatementExcel( $options{clientID}, $options{mechanicalRunID}, $options{outFilePath} );

# ----------------------------------------------------------------------------------------------

sub createJumboStatementExcel {
    my ( $clientID, $mechanicalRunID, $outFilePath ) = @_;

    print STDERR "starting process to create excel file \n";
    print STDERR "clientID: " . $clientID . " \n";
    print STDERR "mechanicalRunID: " . $mechanicalRunID . " \n";
    print STDERR "outFilePath: " . $outFilePath . " \n";

    # Instantiate the application singleton object.
    #
    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    my $outFileName = "mechanical_run_$mechanicalRunID.xls";

    # If an output file path was not specified, we'll just create the file right here.
    #
    if ( !$outFilePath ) {
        $outFilePath = $outFileName;
    } else {

        # Create the output path, if it isn't there already.
        #
        if ( '/' ne substr( $outFilePath, -1, 1 ) ) {
            $outFilePath .= '/';
        }
        if ( !-d $outFilePath ) {
            mkpath($outFilePath) or die "ERROR: Unable to create path $outFilePath: $!\n";
        }

        # Now that the directory is there, let's add the file name so that we have the complete path.
        #
        $outFilePath .= $outFileName;
    }

    print STDERR "creating excel file here: " . $outFilePath . " \n";

    # Instantiate the Excel object.
    #
    my $workbook = Spreadsheet::WriteExcel::Big->new($outFilePath);

    print STDERR "workbook created \n";

    # Set up the special formats.
    #
    ($workbook) = addFormats($workbook);

    # Add the first worksheet.
    #
    my $row;
    my $worksheet;
    ( $row, $workbook, $worksheet ) = addWorksheet( $row, $workbook, $worksheet );

    # Grab all statements in run.
    #

    my $statementList = RPS::DB::Item::MechanicalStatement->Get(
        runID               => $mechanicalRunID,
        sortByPublisherName => 1,
    );

    print STDERR "fetched statements \n";

    # Iterate through all statements in run.
    #
    while ( $statementList->hasNext() ) {
        my $statement   = $statementList->next();
        my $statementID = $statement->mechanical_statement_id;

        print STDERR "processing statement: " . $statementID . " \n";

        my $fullStatement =
          RPS::Statement::Mechanical::StatementFull->new( mechanicalStatementID => $statementID, focusPublisherID => 'all' );

        # Determine if this is an admin (or agent) or regular publisher.
        #
        if (   $fullStatement->Publisher()->IsAdmin() == 1
            || $fullStatement->Publisher()->IsAgency() == 1 ) {

            # Loop through all publishers for admins (and agents).
            #
            my $publisherList = $fullStatement->MechanicalStatementPublisherList()->getList();
            foreach my $publisher (@$publisherList) {
                ( $row, $workbook, $worksheet ) = addJumboDetails( $row, $publisher, $workbook, $worksheet, $formats );
            }
        } else {

            # Not an admin (or agent), so just do this once.
            ( $row, $workbook, $worksheet ) = addJumboDetails( $row, $fullStatement, $workbook, $worksheet, $formats );
        }
    }

    # All done, save and clean up.
    #
    $workbook->close() or die "Error closing file: $!";

    print STDERR "done! \n";

}

sub addJumboHeaders {
    my ( $row, $worksheet ) = @_;

    $worksheet->write( $row, 0,  'Publisher' );
    $worksheet->write( $row, 1,  'Admin' );
    $worksheet->write( $row, 2,  'Agent' );
    $worksheet->write( $row, 3,  'Label' );
    $worksheet->write( $row, 4,  'Album' );
    $worksheet->write( $row, 5,  'Track' );
    $worksheet->write( $row, 6,  'Catalog #' );
    $worksheet->write( $row, 7,  'UPC' );
    $worksheet->write( $row, 8,  'ISRC' );
    $worksheet->write( $row, 9,  'Config' );
    $worksheet->write( $row, 10, 'Region' );
    $worksheet->write( $row, 11, 'Rate Period' );
    $worksheet->write( $row, 12, 'Base Rate' );
    $worksheet->write( $row, 13, '% of Rate' );
    $worksheet->write( $row, 14, 'Share' );
    $worksheet->write( $row, 15, 'Net Rate' );
    $worksheet->write( $row, 16, '% of Sales' );
    $worksheet->write( $row, 17, 'Packaging' );
    $worksheet->write( $row, 18, 'Free Goods' );
    $worksheet->write( $row, 19, 'Misc.' );
    $worksheet->write( $row, 20, 'Sales' );
    $worksheet->write( $row, 21, 'Reserves' );
    $worksheet->write( $row, 22, 'Liquidations' );
    $worksheet->write( $row, 23, 'Returns' );
    $worksheet->write( $row, 24, 'Carryover' );
    $worksheet->write( $row, 25, 'Gross Units' );
    $worksheet->write( $row, 26, 'Total' );
    $worksheet->write( $row, 27, 'Accrual' );
    $worksheet->write( $row, 28, 'Net Units' );
    $worksheet->write( $row, 29, 'Amount Due' );
    $worksheet->write( $row, 30, 'License ID' );
    $worksheet->write( $row, 31, 'Previous Balance' );
    $worksheet->write( $row, 32, 'Adjustments' );
    $worksheet->write( $row, 33, 'Advances' );
    $worksheet->write( $row, 34, 'License Balance' );

    return ( $row, $worksheet );
}

sub addJumboDetails {
    my ( $row, $statement, $workbook, $worksheet, $formats ) = @_;

    # Add the track details.
    #
    my $trackList = $statement->MechanicalStatementTrackList()->getList();
    foreach my $track (@$trackList) {

        # Look up the Label name.
        #
        my $trackID   = $track->TrackID();
        my $labelName = RPS::DB::Item::Label->GetLabelNameFromTrackID($trackID);

        # Go through the entire license list.
        #
        my $licenseList = $track->MechanicalStatementLicenseList()->getList();
        foreach my $license (@$licenseList) {

            #print STDERR "processing license: ".$license->TrackLicenseID()." \n";

            # Figure out what we're going to use for License Balance.
            #
            my $licenseBalance;
            if ( $license->AdjustedSubtotal != 0 ) {
                $licenseBalance = $license->AdjustedSubtotal;
            } else {
                $licenseBalance = $license->AdvanceBalance;
            }

            # Add up the transactions now for use later.
            #
            my $xmlObj                  = $license->MechanicalStatementLicenseTransactionList();
            my $licenseTransactionList  = $xmlObj->getList();
            my $licenseTransactionCount = scalar(@$licenseTransactionList);
            my $adjustments             = 0;
            my $advances                = 0;
            if ( $licenseTransactionCount != 0 ) {
                foreach my $licenseTransaction (@$licenseTransactionList) {

                    if ( $licenseTransaction->TypeCode == 3 ) {

                        # Advances
                        $advances += $licenseTransaction->Amount();
                    } else {

                        # Adjustments
                        $adjustments += $licenseTransaction->Amount();
                    }

                }

            }

            # One day we'll capture everything we need during the royalty run.
            # But until that day, we have to hit the live license for some things.
            #
            my $trackLicense = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $license->TrackLicenseID() );

            my $incomeItemList       = $license->MechanicalStatementItemList()->getList();
            my $incomeItemListLength = scalar(@$incomeItemList);

            if ( $incomeItemListLength != 0 ) {

                foreach my $incomeItem (@$incomeItemList) {

                    ( $row, $workbook, $worksheet ) = incrementRow( $row, $workbook, $worksheet );

                    $worksheet->write_string( $row, 0, $statement->Publisher()->PublisherName() );
                    if ( $statement->Publisher()->AdminID() ) {
                        $worksheet->write_string( $row, 1, $statement->Publisher()->AdminName() );
                    }
                    if ( $statement->Publisher()->AgentID() ) {
                        $worksheet->write_string( $row, 2, $statement->Publisher()->AgentName() );
                    }
                    $worksheet->write_string( $row, 3,  $labelName );
                    $worksheet->write_string( $row, 4,  $track->AlbumName() );
                    $worksheet->write_string( $row, 5,  $track->SongTitle() );
                    $worksheet->write_string( $row, 6,  $track->CatalogNumber(), $formats->{left} );
                    $worksheet->write_string( $row, 7,  $incomeItem->UPC() );
                    $worksheet->write_string( $row, 8,  $track->ISRC() );
                    $worksheet->write_string( $row, 9,  productCodeToName( $incomeItem->ProductTypeID() ) );
                    $worksheet->write_string( $row, 10, regionIDToName( $license->RegionID() ) );
                    $worksheet->write_string( $row, 11, $incomeItem->RatePeriod() );
                    $worksheet->write( $row, 12,
                        Common::Client::Current()->Locale()->currencyFormat()->symbol() . formatNumber( $incomeItem->BaseRate() ),
                        $formats->{right} );
                    $worksheet->write( $row, 13, formatPercent( $incomeItem->TrackLicense->RatePercentage() ), $formats->{right} );
                    $worksheet->write( $row, 14, formatPercent( $incomeItem->TrackLicense->Share() ),          $formats->{right} );
                    $worksheet->write( $row, 15,
                        Common::Client::Current()->Locale()->currencyFormat()->symbol() . formatNumber( $incomeItem->NetRate() ),
                        $formats->{right} );

                    # Pulling these values from the live license.
                    #
                    $worksheet->write( $row, 16, formatPercent( $trackLicense->percentage_of_sales ), $formats->{right} );
                    $worksheet->write( $row, 17, formatPercent( $trackLicense->packaging_deduction ), $formats->{right} );
                    $worksheet->write( $row, 18, formatPercent( $trackLicense->free_goods ),          $formats->{right} );
                    $worksheet->write( $row, 19, formatPercent( $trackLicense->misc_deduction ),      $formats->{right} );

                    $worksheet->write( $row, 20, formatNumber( $incomeItem->Sales() ),             $formats->{right} );
                    $worksheet->write( $row, 21, formatNumber( $incomeItem->Reserved() ),          $formats->{right} );
                    $worksheet->write( $row, 22, formatNumber( $incomeItem->Liquidated() ),        $formats->{right} );
                    $worksheet->write( $row, 23, formatNumber( $incomeItem->Returns() ),           $formats->{right} );
                    $worksheet->write( $row, 24, formatNumber( $incomeItem->PreviousCarryover() ), $formats->{right} );
                    $worksheet->write( $row, 25, formatNumber( $incomeItem->GrossUnits() ),        $formats->{right} );
                    $worksheet->write( $row, 26, formatMoney( $incomeItem->Total() ),              $formats->{right} );
                    $worksheet->write( $row, 27, formatNumber( $incomeItem->Carryover() ),         $formats->{right} );
                    $worksheet->write( $row, 28, formatNumber( $incomeItem->NetUnits() ),          $formats->{right} );
                    $worksheet->write( $row, 29, formatMoney( $incomeItem->AmountPaid() ),         $formats->{right} );
                    $worksheet->write_string( $row, 30, $license->TrackLicenseID(), $formats->{left} );
                    $worksheet->write( $row, 31, formatMoney( $license->PreviousAdvanceBalance() ), $formats->{right} );
                    $worksheet->write( $row, 32, formatMoney($adjustments),                         $formats->{right} );
                    $worksheet->write( $row, 33, formatMoney($advances),                            $formats->{right} );
                    $worksheet->write( $row, 34, formatMoney($licenseBalance),                      $formats->{right} );

                }

            }

            else {

                # Even if there are no income items, we still want to display the track info.
                #

                ( $row, $workbook, $worksheet ) = incrementRow( $row, $workbook, $worksheet );

                $worksheet->write_string( $row, 0, $statement->Publisher()->PublisherName() );
                if ( $statement->Publisher()->AdminID() ) {
                    $worksheet->write_string( $row, 1, $statement->Publisher()->AdminName() );
                }
                if ( $statement->Publisher()->AgentID() ) {
                    $worksheet->write_string( $row, 2, $statement->Publisher()->AgentName() );
                }
                $worksheet->write_string( $row, 3,  $labelName );
                $worksheet->write_string( $row, 4,  $track->AlbumName() );
                $worksheet->write_string( $row, 5,  $track->SongTitle() );
                $worksheet->write_string( $row, 6,  $track->CatalogNumber(), $formats->{left} );
                $worksheet->write_string( $row, 8,  $track->ISRC() );
                $worksheet->write_string( $row, 10, regionIDToName( $license->RegionID() ) );
                $worksheet->write_string( $row, 30, $license->TrackLicenseID(), $formats->{left} );
                $worksheet->write( $row, 31, formatMoney( $license->PreviousAdvanceBalance() ), $formats->{right} );
                $worksheet->write( $row, 32, formatMoney($adjustments),                         $formats->{right} );
                $worksheet->write( $row, 33, formatMoney($advances),                            $formats->{right} );
                $worksheet->write( $row, 34, formatMoney($licenseBalance),                      $formats->{right} );
            }

        }

    }

    return ( $row, $workbook, $worksheet );
}

sub addWorksheet {
    my ( $row, $workbook, $worksheet ) = @_;

    $worksheet = $workbook->add_worksheet();

    print STDERR "worksheet added \n";

    # Set up the row variable.
    #
    $row = 0;

    # Add the column headers
    #
    ( $row, $worksheet ) = addJumboHeaders( $row, $worksheet );

    # Not needed at the moment...
    # Specify widths for some columns.
    #

    # Amount column for summary table
    #$worksheet->set_column(3, 3, 12);

    # UPC column for detail table
    #$worksheet->set_column(8, 8, 12);

    # Region column for detail table
    #$worksheet->set_column(10, 10, 12);

    # Amount Due column for detail table
    #$worksheet->set_column(15, 15, 12);

    return ( $row, $workbook, $worksheet );
}

sub addFormats {
    my ($workbook) = @_;

    my $formatB = $workbook->add_format();
    $formatB->set_bold();

    my $formatRB = $workbook->add_format();
    $formatRB->set_bold();
    $formatRB->set_align('right');

    my $formatR = $workbook->add_format();
    $formatR->set_align('right');

    my $formatL = $workbook->add_format();
    $formatL->set_align('left');

    my $formatUB = $workbook->add_format();
    $formatUB->set_bold();
    $formatUB->set_bottom(1);

    my $formatUR = $workbook->add_format();
    $formatUR->set_align('right');
    $formatUR->set_bottom(1);

    my $formatU = $workbook->add_format();
    $formatU->set_bottom(1);

    $formats = {
        bold            => $formatB,
        right_bold      => $formatRB,
        right           => $formatR,
        left            => $formatL,
        underline_bold  => $formatUB,
        right_underline => $formatUR,
        underline       => $formatU,
    };

    return ($workbook);
}

sub incrementRow {
    my ( $row, $workbook, $worksheet ) = @_;

    # Have we reached the max row for excel?
    #
    if ( $row >= 65534 ) {

        # Add a new worksheet
        #
        ( $row, $workbook, $worksheet ) = addWorksheet( $row, $workbook, $worksheet );
    }

    $row++;

    return ( $row, $workbook, $worksheet );
}

sub formatNumber {
    my ($value) = @_;

    $value = Formats::formatNumber($value);

    return $value;
}

sub formatMoney {
    my ($value) = @_;

    $value = Formats::formatMoney($value);

    return $value;
}

sub formatPercent {
    my ($value) = @_;

    $value = Formats::formatPercent($value);

    return $value;
}

# These functions map ids to their text representations.
# Basically, the first time we ask for a particular id->string mapping, we
# will query the correct database, and build a hash.  Subsequent calls just
# hit the hash.
#
# Because these tables are so similar, I abstracted the guts into the _genericMapAccessor
# method (to save myself some typing).
#
my %idMaps;

sub _genericMapAccessor {
    my ( $collectionAccessor, $idName, $id ) = @_;

    if ( !defined $idMaps{$collectionAccessor} ) {
        $idMaps{$collectionAccessor} = {};

        # This is a very naughty thing to do, but it works great in this context.
        #
        #        no strict 'refs';
        #        my $c = &$collectionAccessor();
        #        use strict 'refs';
        my $c = $collectionAccessor->GetAll();

        while ( my $item = $c->next() ) {
            if ( $idName ne 'product_type_id' ) {
                $idMaps{$collectionAccessor}{ $item->$idName() } = $item->name;
            } else {
                $idMaps{$collectionAccessor}{ $item->$idName() } = $item->description;
            }
        }
    }

    return $idMaps{$collectionAccessor}{$id};
}

sub incomeSourceIDToName {
    my ($id) = @_;

    return _genericMapAccessor( 'RPS::DB::Item::IncomeSource', 'income_source_id', $id );
}

sub regionIDToName {
    my ($id) = @_;

    # we don't want to show Rest of World here
    if ( $id != 0 ) {
        return _genericMapAccessor( 'RPS::DB::Item::Region', 'region_id', $id );
    } else {
        return ' ';
    }
}

sub channelIDToName {
    my ($id) = @_;
    return _genericMapAccessor( 'RPS::DB::Item::Channel', 'channel_id', $id );
}

sub priceLevelIDToName {
    my ($id) = @_;
    return _genericMapAccessor( 'RPS::DB::Item::PriceLevel', 'price_level_id', $id );
}

sub contractRateTypeIDToName {
    my ($id) = @_;
    return _genericMapAccessor( 'RPS::DB::Item::ContractRateType', 'contract_rate_type_id', $id );
}

sub productCodeToName {
    my ($id) = @_;
    return _genericMapAccessor( 'RPS::DB::Item::ProductType', 'product_type_id', $id );
}

#
# Boring script stuff below...
#

sub parseCommandLine {
    my ($settings) = @_;

    my %opt;
    getopts( 'r:p:c:V:', \%opt );

    if ( !$opt{r} || !$opt{c} ) {
        usage();
        exit(1);
    }
    $settings->{mechanicalRunID} = $opt{r};
    $settings->{clientID}        = $opt{c};
    $settings->{outFilePath}     = $opt{p};

    if ( defined $opt{V} ) {
        $gVerbosityLevel = $opt{V};
    }
}

sub usage {
    print STDERR "\nusage: $0 -c <client_id> -r <mechanical_run_id> [-p <output file>]\n";
    print STDERR "\n";
    print STDERR "Arguments:\n";
    print STDERR "\t-c <client_id>\t\t\tThe client_id of the client to process\n";
    print STDERR "\t-r <mechanical_run_id>\tThe id of the mechanical run to convert to excel\n";
    print STDERR "\t-p <output file>\tThe output file path. Optional. If not provided, we'll make one up\n";
}

sub report {
    my ( $string, $verbosity ) = @_;
    $verbosity = 1 unless defined $verbosity;

    if ( $gVerbosityLevel >= $verbosity ) {
        print STDERR $string . "\n";
    }
}