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