#!/usr/bin/perl
#------------------------------------------------------------
# This script generates a CMRRA Publisher Summary Report as
# defined in Case 17343.
#
# 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;
use RPS::DB::Item::CAMechanicalStatementPublisher;

$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_SUMMARY_COMPLETE";
    unlink($semaphore);


    # Get the set of agents; we'll generate a report for 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;


        my $targetFile = $outFilePath . "cmrra_agent_summary_run_$runID\_agent_$agentID.xlsx"; # This is the internal filename

        # Instantiate the Excel object.
        #
        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 = $startDate . "_" . $endDate;
        $quarterName =~ s/-/_/g;


        # 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. (FB23)
                #
                next if( $agentID != $pObj->ca_publisher_id );

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

                my $_name = $pObj->publisher_name;

                # 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 amount due to agent
                #
                my $expectedAmount = $statement->amount_due;


                my $publisherList = RPS::DB::Item::CAMechanicalStatementPublisher->GetSortedByMechanicalStatementID( $_stmtID );
                while (my $statement = $publisherList->next())
                {
                    $gReportHash{ $statement->ca_publisher_id } = $statement->amount_due;
                }

                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, $manufacturerID );
                $row++;

                # Output header row 2
                #
                $worksheet->write( $row, 0, "Manufacturer Name" );
                $worksheet->write( $row, 1, $manufacturerName );
                $row++;

                # Output header row 3
                #
                $worksheet->write( $row, 0, "Quarterly Period" );
                $worksheet->write( $row, 1, $quarterName );
                $row++;

                # Output header row 4
                #
                $worksheet->write( $row, $col++, "Publisher Name" );
                $worksheet->write( $row, $col++, "Publisher #" );
                $worksheet->write( $row, $col++, "Total Payable" );
                $row++;

                my $grandTotal = 0;

                # Output detail lines
                #
                foreach my $publisherID ( sort _mysort keys %gReportHash )
                {
                    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 $amountDue       = $gReportHash{$publisherID};
                    $grandTotal += $amountDue;

                    $col = 0;
                    $worksheet->write( $row, $col++, $publisherName );   # Publisher Name
                    $worksheet->write( $row, $col++, $clientAccountID ); # Publisher #
                    $worksheet->write( $row, $col++, $amountDue );       # Total Payable
                    $row++;
                }

    #            print STDERR "D: expectedAmount($expectedAmount)  actualAmount($grandTotal)\n";


                $row++;
                $worksheet->write( $row, 1, "Grand CMRRA Total" );
                $worksheet->write( $row, 2, $grandTotal );

                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
#
sub _mysort {
    my $p_a = RPS::DB::Item::CAPublisher->Lookup( ca_publisher_id => $a );
    my $p_b = RPS::DB::Item::CAPublisher->Lookup( ca_publisher_id => $b );
    return $p_a->publisher_name cmp $p_b->publisher_name;
}

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