#!/usr/bin/perl
#---------------------------------------------------------------
# This script generates all CMRRA agent Excel statement reports.
# See Case 23 for additional information.
#
# Copyright (C) 2006-2012 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#---------------------------------------------------------------
use strict;

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

use Excel::Writer::XLSX;

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::Mechanical::CA::StatementFull;
use RPS::DB::Item::CATrackLicense;
use RPS::DB::Item::CAMechanicalRun;
use RPS::DB::Item::CAMechanicalStatement;
use RPS::DB::Item::CAMechanicalStatementItem;

use RPS::DB::Item::CAPublisher;
use RPS::DB::Item::CAPublisherAccount;
use RPS::DB::Item::ProductType;

$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;

my %gReportHash = ();

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

processCanadianAgent( $options{clientID}, $options{mechRunID}, $options{outputPath} );

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

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

    assert($clientID);
    assert($runID);

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

    # Create the output path if it isn't there already.  This report is
    # intended to be run after the statements have been generated, so
    # the output path will normally already exist.
    #
    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 if any
    #
    my $semaphore = $outFilePath . "CMRRA_AGENT_REPORT_COMPLETE";
    unlink($semaphore);

    # Get the set of agents; we'll generate a report each agent
    # with statement activity.
    #
    my $agents = RPS::DB::Item::CAPublisher->GetAllAgents();

    while ( $agents->hasNext() ) {
        my $agent     = $agents->next();
        my $agentID   = $agent->ca_publisher_id;
        my $agentName = $agent->publisher_name;

        # Instantiate the Excel object.  Each agent will have their own
        # CMRRA Summary report.
        #
        my $targetFile = $outFilePath . "cmrra_agent_report_run_$runID\_agent_$agentID.xlsx";    # This is the internal filename

        my $workbook;
        my $worksheet;

        my $run = RPS::DB::Item::CAMechanicalRun->Lookup( ca_mechanical_run_id => $runID );

        if ( !$run ) {
            die("ERROR: CA runID $runID is invalid!");
        }

        my $payorID   = $run->payor_id;
        my $startDate = $run->start_date;
        my $endDate   = $run->end_date;
        my $label     = $run->label;

        my $quarterName;

        my ( $year, $month, $day ) = split( "-", $endDate );

        # Determine the name of the quarter in YYQ[1-4] format
        #
        my $q;
        $q = 1 if ( $month >= 1 and $month <= 3 );
        $q = 2 if ( $month >= 4 and $month <= 6 );
        $q = 3 if ( $month >= 7 and $month <= 8 );
        $q = 4 if ( $month >= 9 and $month <= 12 );
        $quarterName = substr( $year, -2 ) . "Q" . $q;

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

            my $_stmtID      = $statement->ca_mechanical_statement_id;
            my $_publisherID = $statement->ca_publisher_id;

            my $pObj = RPS::DB::Item::CAPublisher->Lookup( ca_publisher_id => $_publisherID );

            if ( $pObj->is_agency ) {

                # This statement is for an agent; make sure it's for the _correct_ agent. (FB24)
                #
                next if ( $agentID != $pObj->ca_publisher_id );

                my $_name = $pObj->publisher_name;

                if ( !$workbook ) {
                    $workbook  = Excel::Writer::XLSX->new($targetFile);
                    $worksheet = $workbook->add_worksheet();
                }

                # Get the CMRRA manufacturer name/ID
                #
                my $pa = RPS::DB::Item::CAPublisherAccount->Lookup(
                    ca_publisher_id => $_publisherID,
                    payor_id        => $payorID
                );
                my $manufacturerName = $pa->manufacturer_name;
                my $manufacturerID   = $pa->manufacturer_id;

                # Get the collection of statement items.  These are not sorted, so
                # build up a report hash such that we can organize the output.
                #
                my $stmtItems = RPS::DB::Item::CAMechanicalStatementItem->GetByMechanicalStatementID($_stmtID);
                while ( my $stmtItem = $stmtItems->next() ) {

                    my $itemID     = $stmtItem->ca_mechanical_statement_item_id;
                    my $licenseID  = $stmtItem->ca_track_license_id;
                    my $netRate    = $stmtItem->net_rate;
                    my $amountPaid = $stmtItem->amount_paid;
                    my $netUnits   = $stmtItem->net_units;

                    #  Get license object and extract data
                    #
                    my $tlObj = RPS::DB::Item::CATrackLicense->Lookup( ca_track_license_id => $licenseID );

                    my $tlPublisherID   = $tlObj->ca_publisher_id;     # aka focus publisher
                    my $issuerLicenseID = $tlObj->issuer_license_id;
                    my $issuerSongID    = $tlObj->issuer_song_id;
                    my $share           = $tlObj->share;
                    my $trackID         = $tlObj->track_id;

                    my $productID   = $stmtItem->product_id;
                    my $pObj        = RPS::DB::Item::Product->Lookup( product_id => $productID );
                    my $productType = productTypeIDToName( $pObj->product_type_id );

                    # Get the composer info (need to get the track, master and song first, though)
                    #
                    my $trObj = RPS::DB::Item::Track->Lookup( track_id => $trackID );
                    my $mObj = RPS::DB::Item::Master->Lookup( master_id => $trObj->master_id );
                    my $cObj;
                    my $composer;

                    if ( $mObj->song_id ) {
                        my $sObj = RPS::DB::Item::Song->Lookup( song_id => $mObj->song_id );
                        if ( $sObj->composer_id ) {
                            $cObj = RPS::DB::Item::Composer->Lookup( composer_id => $sObj->composer_id );
                        }
                    }

                    my $trackName = $trObj->title;
                    $composer = ($cObj) ? $cObj->name : '';

                    my $aObj = RPS::DB::Item::Album->Lookup( album_id => $trObj->album_id );
                    my $catalogNumber = $aObj->catalog_number;

                    $gReportHash{$itemID}{publisher_id}      = $tlPublisherID;
                    $gReportHash{$itemID}{issuer_license_id} = $issuerLicenseID;
                    $gReportHash{$itemID}{issuer_song_id}    = $issuerSongID;
                    $gReportHash{$itemID}{share}             = $share;
                    $gReportHash{$itemID}{track_id}          = $trackID;
                    $gReportHash{$itemID}{title}             = $trackName;
                    $gReportHash{$itemID}{composer}          = $composer;
                    $gReportHash{$itemID}{net_rate}          = $netRate;
                    $gReportHash{$itemID}{net_units}         = $netUnits;
                    $gReportHash{$itemID}{amount_paid}       = $amountPaid;
                    $gReportHash{$itemID}{catalog_number}    = $catalogNumber;
                    $gReportHash{$itemID}{product_type}      = $productType;

                }    # statement item loop

                my %publisherMap;

                # For XLSX, the general syntax is write($row, $column, $token). Note that
                # row and column are zero indexed.
                #
                my $row = 0;
                my $col = 0;

                # Output header row 1
                #
                $worksheet->write( $row, 0, "CMRRA MANUFACTURER ID" );
                $worksheet->write( $row, 1, uc $manufacturerID );
                $row++;

                # Output header row 2
                #
                $worksheet->write( $row, 0, "MANUFACTURER NAME" );
                $worksheet->write( $row, 1, uc $manufacturerName );
                $row++;

                # Output header row 3
                #
                $worksheet->write( $row, $col++, "PUBLISHER NAME" );
                $worksheet->write( $row, $col++, "PUBLISHER #" );
                $worksheet->write( $row, $col++, "LICENSE # PREFIX" );
                $worksheet->write( $row, $col++, "LICENSE # SUFFIX" );
                $worksheet->write( $row, $col++, "SONG #" );
                $worksheet->write( $row, $col++, "SONG TITLE" );
                $worksheet->write( $row, $col++, "WRITER NAME(S)" );
                $worksheet->write( $row, $col++, "CATALOG #" );
                $worksheet->write( $row, $col++, "CONTRIVANCE" );
                $worksheet->write( $row, $col++, "ROYALTY RATE" );
                $worksheet->write( $row, $col++, "OWNERSHIP %" );
                $worksheet->write( $row, $col++, "UNITS" );
                $worksheet->write( $row, $col++, "ROYALTY AMOUNT OWNING \$" );
                $worksheet->write( $row, $col++, "QUARTER" );
                $worksheet->write( $row, $col++, "CMRRA MANUFACTURER ID" );
                $row++;

                # Output detail lines
                #
                foreach my $itemID ( sort _mysort keys %gReportHash ) {
                    my $publisherID     = $gReportHash{$itemID}{publisher_id};
                    my $issuerLicenseID = $gReportHash{$itemID}{issuer_license_id};
                    my $issuerSongID    = $gReportHash{$itemID}{issuer_song_id};
                    my $share           = $gReportHash{$itemID}{share};
                    my $trackID         = $gReportHash{$itemID}{track_id};
                    my $trackName       = $gReportHash{$itemID}{title};
                    my $composer        = $gReportHash{$itemID}{composer};
                    my $netRate         = $gReportHash{$itemID}{net_rate};
                    my $netUnits        = $gReportHash{$itemID}{net_units};
                    my $amountPaid      = $gReportHash{$itemID}{amount_paid};
                    my $catalogNumber   = $gReportHash{$itemID}{catalog_number};
                    my $productType     = $gReportHash{$itemID}{product_type};

                    if ( !exists $publisherMap{$publisherID} ) {
                        my $o = RPS::DB::Item::CAPublisher->Lookup( ca_publisher_id => $publisherID );
                        $publisherMap{$publisherID}{publisher_name}    = $o->publisher_name;
                        $publisherMap{$publisherID}{client_account_id} = $o->client_account_id;
                    }
                    my $publisherName   = $publisherMap{$publisherID}{publisher_name};
                    my $clientAccountID = $publisherMap{$publisherID}{client_account_id};

                    my $licensePrefix = $issuerLicenseID;
                    my $licenseSuffix;
                    if ( $issuerLicenseID =~ /(\d{1,6})\-?(\d+)/ ) {
                        $licensePrefix = $1;
                        $licenseSuffix = $2;
                    }

                    $col = 0;
                    $worksheet->write( $row, $col++, uc $publisherName );      # PUBLISHER NAME
                    $worksheet->write( $row, $col++, uc $clientAccountID );    # PUBLISHER #
                    $worksheet->write( $row, $col++, $licensePrefix );         # LICENSE # PREFIX
                    $worksheet->write( $row, $col++, $licenseSuffix );         # LICENSE # SUFFIX
                    $worksheet->write( $row, $col++, uc $issuerSongID );       # SONG #
                    $worksheet->write( $row, $col++, uc $trackName );          # SONG TITLE
                    $worksheet->write( $row, $col++, uc $composer );           # WRITER NAME(S)
                    $worksheet->write( $row, $col++, uc $catalogNumber );      # CATALOG #
                    $worksheet->write( $row, $col++, uc $productType );        # CONTRIVANCE
                    $worksheet->write( $row, $col++, uc $netRate );            # ROYALTY RATE
                    $worksheet->write( $row, $col++, $share );                 # OWNERSHIP %
                    $worksheet->write( $row, $col++, $netUnits );              # UNITS
                    $worksheet->write( $row, $col++, $amountPaid );            # ROYALTY AMOUNT OWNING $
                    $worksheet->write( $row, $col++, $quarterName );           # QUARTER
                    $worksheet->write( $row, $col++, uc $manufacturerID );     # CMRRA MANUFACTURER ID
                    $row++;
                }    # income item loop

                undef %gReportHash;

            }    # $pObj->is_agency

        }    # statement loop

      DONE:

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

    }    # agent loop

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

}    #processCanadianAgent

# This subroutine controls how the output is sorted; for now,
# we'll sort by publisherName and trackID
#
sub _mysort {
    if ( $gReportHash{$a}{publisher_id} != $gReportHash{$b}{publisher_id} ) {
        my $p_a = RPS::DB::Item::CAPublisher->Lookup( ca_publisher_id => $gReportHash{$a}{publisher_id} );
        my $p_b = RPS::DB::Item::CAPublisher->Lookup( ca_publisher_id => $gReportHash{$b}{publisher_id} );

        return $p_a->publisher_name cmp $p_b->publisher_name;
    }

    return $gReportHash{$a}{title} cmp $gReportHash{$b}{title};
}

# 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 productTypeIDToName {
    my ($id) = @_;

    if ( !$id ) {
        return "All Products";
    } elsif ( $id == 254 ) {
        return "All Digital";
    } elsif ( $id == 255 ) {
        return "All Physical";
    }

    return _genericMapAccessor( 'RPS::DB::Item::ProductType', 'product_type_id', $id );
}

#
# Boring script stuff below...
#

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

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

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

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

sub usage {
    print STDERR "\nusage: $0 -c <client_id> -r <ca_mech_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 <ca_mech_run_run_id>\tThe id of the CA mechanical run to generate an agent report 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";
    }
}