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

#use warnings;

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

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

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

use lib '/app/tools/rps/lib';
use RPS::Statement::Artist::StatementFull;

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::NewArtistContract;

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 CSV!
#
my %options;
parseCommandLine( \%options );

processArtistRunExcel( $options{clientID}, $options{artistRunID},
    $options{artistStatementID}, $options{outputPath} );

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

sub processArtistRunExcel
{
    my($clientID, $runID, $statementID, $outFilePath) = @_;

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

    # This report is intended to be run after the statements have been
    # generated, so the output path should already exist.

    # 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";
    }


    # Get rid of the semaphore file
    my $semaphore = $outFilePath . "EXPORT_AB_EXCEL_COMPLETE";
    unlink($semaphore);

    my $targetFile = $outFilePath . "ab_export_run_$runID.xls";

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

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

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


    # Get the collection of statements for this run.
    #
    my $statements = RPS::DB::Item::ArtistRoyaltyStatement->GetByArtistRoyaltyRunID($runID);
    while (my $statement = $statements->next())
    {

        my $_stmtID = $statement->artist_royalty_statement_id;

        next if ( $statementID && $_stmtID != $statementID );

        ($row, $workbook, $worksheet) = processStatementExcel( $row,  $_stmtID, $workbook, $worksheet, $formats );
    }


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

    # Create the semaphore
    #
    open SEMAPHORE, "> $semaphore";
    print SEMAPHORE "done\n";
    close SEMAPHORE;

}#processArtistRunExcel


sub processStatementExcel
{
    my ( $row, $artistStatementID, $workbook, $worksheet, $formats) = @_;

    # Instantiate the Artist Statement object.
    # This object will contain all (most of) the info we need to output the statement document.
    #
    my $statement = RPS::Statement::Artist::StatementFull->new(
        artistRoyaltyStatementID => $artistStatementID,
    );

    ($row, $workbook, $worksheet) = addJumboDetails( $row, $statement, $workbook, $worksheet, $formats );

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

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

    my $payee = $statement->ArtistPayee();

    my $albumList = $statement->ArtistStatementAlbumList()->getList();
    foreach my $album (@$albumList)
    {

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

        # We need to skip the albums whose income items use the non-payable rate type exclusively.
        #
        my $incomeItemList = $album->IncomeItemList()->getList();
        my $skipAlbum = 1;
        foreach my $incomeItem (@$incomeItemList)
        {
            if ($incomeItem->ContractRateTypeID() != RPS::DB::Item::ContractRateType::kRateTypeNonPayable)
            {
                $skipAlbum = 0;
                last;
            }
        }

        # If there are no sales, then we're dealing with license income
        # and/or expenses, which should be included on the statement.
        #
        if ( @$incomeItemList == 0 ) {
           $skipAlbum = 0;
        }

        if ($skipAlbum == 1)
        {
            next;
        }


        my $rsContractID = $album->ArtistContractID();
        my $contract = RPS::DB::Item::NewArtistContract->Lookup( artist_contract_id => $rsContractID );
        my $contractDateModified = $contract->date_modified;

        #----------------------------------------
        # Generate report line with album info
        #----------------------------------------
        $worksheet->write_string($row, 0, $statement->ArtistPayee()->Name()),      # A - payee-name
        $worksheet->write_string($row, 1, $statement->ArtistPayee()->ClientAccountID()),  # B - client-no
        $worksheet->write_string($row, 2, $album->Album()->Title()),               # C - album-title
        $worksheet->write_string($row, 3, $album->Album()->ArtistName()),          # D - album-artist
        $worksheet->write_string($row, 4, $album->Album()->CatalogNumber()),       # E - catalog-no
        $worksheet->write_string($row, 5, $album->ContractName()),                 # F - contract-name
        $worksheet->write($row, 6, $album->UnitLevelIncome()),                     # G - unit-level-income
        $worksheet->write($row, 7, $album->NetRevenueIncome()),                    # H - net-revenue-total
        $worksheet->write($row, 8, $album->LicenseIncomeSubtotal()),               # I - license-income-total
        $worksheet->write($row, 9, $album->RecoupableExpenses()),                  # J - recoupable-expenses-total
        $worksheet->write($row, 10, $album->PreviousBalance()),                    # K - album-previous-balance
        $worksheet->write($row, 11, $album->Total()),                              # L - album-total
        $worksheet->write($row, 12, $album->ArtistContractID),                     # M - rs-contract-id
        $worksheet->write_string($row, 13, $contractDateModified),                 # N - contract-date-modified

    }#album loop

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


# For now, we are not realy going to be doing any formatting of numbers.
#

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 channelIDToName
{
    my ($id) = @_;
    #return "All" if( 0 == $id );
    return _genericMapAccessor('RPS::DB::Item::Channel', 'channel_id', $id);
}

sub priceLevelIDToName
{
    my ($id) = @_;
    #return "All" if( 0 == $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 regionIDToName
{
    my ($id, $defaultRate) = @_;

    if ( $id == 0 && $defaultRate == 1 )
    {
        return 'All';
    }
    else
    {
        return _genericMapAccessor( 'RPS::DB::Item::Region', 'region_id', $id );
    }
}

# Excel support subroutines
#
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 addJumboHeaders
{
    my ($row, $worksheet) = @_;

    $worksheet->freeze_panes(1,0);
    $worksheet->write($row, 0, 'payee-name', $formats->{header});                # A
    $worksheet->write($row, 1, 'client-no', $formats->{header});                 # B
    $worksheet->write($row, 2, 'album-title', $formats->{header});               # C
    $worksheet->write($row, 3, 'album-artist', $formats->{header});              # D
    $worksheet->write($row, 4, 'catalog-no', $formats->{header});                # E
    $worksheet->write($row, 5, 'contract-name', $formats->{header});             # F
    $worksheet->write($row, 6, 'unit-level-income', $formats->{header});         # G
    $worksheet->write($row, 7, 'net-revenue-total', $formats->{header});         # H
    $worksheet->write($row, 8, 'license-income-total', $formats->{header});      # I
    $worksheet->write($row, 9, 'recoupable-expenses-total', $formats->{header}); # J
    $worksheet->write($row, 10, 'album-previous-balance', $formats->{header});   # K
    $worksheet->write($row, 11, 'album-total', $formats->{header});              # L
    $worksheet->write($row, 12, 'rs-contract-id', $formats->{header});           # M
    $worksheet->write($row, 13, 'contract-date-modified', $formats->{header});   # N

    return ($row, $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);

    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);
    my $formatHeader = $workbook->add_format();
    my $headerFillColor = $workbook->set_custom_color(23,216,216,216);
    $formatHeader->set_bg_color($headerFillColor);
    $formatHeader->set_bottom(2); # continuous (weight 2)
    $formatHeader->set_left(1);   # continuous (weight 1)
    $formatHeader->set_right(1);  # continuous (weight 1)
    $formatHeader->set_top(1);    # continuous (weight 1)
    $formatHeader->set_bold();
    #$formatHeader->set_size(8);
    $formatHeader->set_text_wrap();

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

    return ($workbook);
}

#
# Boring script stuff below...
#

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

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

    if ( !$opt{r} || !$opt{c} )
    {
        usage();
        exit(1);
    }
    $settings->{artistRunID} = $opt{r};
    $settings->{clientID}        = $opt{c};
    $settings->{outputPath}      = $opt{p};
    $settings->{artistStatementID} = $opt{s};

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

sub usage
{
    print STDERR
"\nusage: $0 -c <client_id> -r <artist_run_id> [-p <output path>]\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 <artist_royalty_run_id>\tThe id of the artist run to generate export data for\n";
    print STDERR
"\t-p <output path>\tThe output path. Optional. If not provided, use current directory.\n";
}

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

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