#!/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::Mechanical::US::StatementFull;
use RPS::Statement::Mechanical::US::StatementLicenseTransactionList;

use RPS::Payor::Payor;
use RPS::Publisher::US::Publisher;
use RPS::Mechanical::US::MechanicalRun;
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::Product;
use RPS::DB::Item::ProductType;
use RPS::DB::Item::TrackLicense;

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

processMechanicalRunCSV( $options{clientID}, $options{mechanicalRunID},
    $options{mechanicalStatementID}, $options{outputPath} );

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

sub processMechanicalRunCSV
{
    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_EXCEL_COMPLETE";
    unlink($semaphore);

    my $targetFile = $outFilePath . "mechanical_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::MechanicalStatement->GetByMechanicalRunID($runID);
    while (my $statement = $statements->next())
    {

        my $_stmtID = $statement->mechanical_statement_id;

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

        #processStatementCSV( $outFile,  $_stmtID );
        ($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;

}#processMechanicalRunCSV


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

    # Instantiate the MechanicalStatement object.
    # This object will contain all the info we need to output the statement document.
    #
    my $statement = RPS::Statement::Mechanical::US::StatementFull->new(
        mechanicalStatementID => $mechanicalStatementID,
        focusPublisherID      => 'all'
    );

    # Determine if this is an admin or regular publisher.
    if (  $statement->Publisher()->IsAdmin() == 1 || $statement->Publisher()->IsAgency() == 1 )
    {
        # Loop through all publishers for admins.
        my $publisherList = $statement->MechanicalStatementPublisherList()->getList();
        foreach my $publisher (@$publisherList)
        {
            ($row, $workbook, $worksheet) = addJumboDetails( $row, $publisher, $workbook, $worksheet, $formats );
        }
    }
    else
    {
        # Not an admin, so just do this once.
        ($row, $workbook, $worksheet) = addJumboDetails( $row, $statement, $workbook, $worksheet, $formats);
    }

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


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

    my %gExport; # will hold our royalty data export

    my $trackList = $statement->MechanicalStatementTrackList()->getList();
    foreach my $track (@$trackList)
    {

        my %licenseData; # will contain all license-centric report data for the current track


        # Go through the crossed license list
        #
        my $crossedLicenseList = $track->MechanicalStatementCrossedLicenseList()->getList();

        my $crossedAdvanceTotal    = 0;
        my $crossedAdjustmentTotal = 0;

        if ( @$crossedLicenseList > 0 )
        {

            my $balanceTotal = 0;

            # Add the transactions up into two buckets, adjustments and advances.
            #
            my $advanceTotal = 0;
            my $adjustmentTotal = 0;
 
            foreach my $license (@$crossedLicenseList) # Crossed licenses !!!
            {
                my $trackLicenseData = RPS::DB::Item::TrackLicense->Lookup(track_license_id => $license->TrackLicenseID());
                my $issuerLicenseID = $trackLicenseData->issuer_license_id;
                my $licenseID = $trackLicenseData->track_license_id;

                $licenseData{$licenseID}{cross_collateralized} = $trackLicenseData->cross_collateralized;

#
                $licenseData{$licenseID}{crossed_previous_advance_balance} = $track->CrossedPreviousAdvanceBalance();
                $licenseData{$licenseID}{crossed_subtotal}                 = $track->CrossedSubtotal();
                $licenseData{$licenseID}{crossed_adjusted_subtotal}        = $track->CrossedAdjustedSubtotal();
                $licenseData{$licenseID}{crossed_advance_balance}          = $track->CrossedAdvanceBalance();

                $licenseData{$licenseID}{adjusted_subtotal}        = $license->AdjustedSubtotal();
                $licenseData{$licenseID}{subtotal}                 = $license->Subtotal();

                # Get the license transactions tied to this license
                #
                my $xmlObj = $license->MechanicalStatementLicenseTransactionList();
                my $licenseTransactionList  = $xmlObj->getList();
                my $licenseTransactionCount = scalar(@$licenseTransactionList);

                if ( $licenseTransactionCount != 0 )
                {
                    foreach my $licenseTransaction (@$licenseTransactionList)
                    {
                        if ( $licenseTransaction->TypeCode == 3 )
                        {
                            $advanceTotal += $licenseTransaction->Amount();
                        }
                        else
                        {
                            $adjustmentTotal += $licenseTransaction->Amount();
                        }
                    }
                }

                $licenseData{$licenseID}{advance_total}    = $advanceTotal;
                $licenseData{$licenseID}{adjustment_total} = $adjustmentTotal;


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

                if ( $incomeItemListLength != 0 )
                {
                    # If there are income items, process them all and aggregate
                    # the following data:
                    #  - gross units
                    #  - reserves held
                    #  - reserves liquidated
                    #  - returns
                    #  - carryover
                    #  - net units
                    #  - cc adjustments (crossed adjustments)
                    #

                    foreach my $incomeItem (@$incomeItemList)
                    {
                        $licenseData{$licenseID}{net_units}   += $incomeItem->NetUnits();

                        #$licenseData{$licenseID}{gross_units} += $incomeItem->GrossUnits();
                        $licenseData{$licenseID}{gross_units} += $incomeItem->Sales();

                        $licenseData{$licenseID}{reserved}    += $incomeItem->Reserved();
                        $licenseData{$licenseID}{liquidated}  += $incomeItem->Liquidated();
                        $licenseData{$licenseID}{carryover}   += $incomeItem->Carryover();
                        $licenseData{$licenseID}{returns}     += $incomeItem->Returns();

                        $licenseData{$licenseID}{upc}  = $incomeItem->UPC() if ( $incomeItem->UPC() );
                        $licenseData{$licenseID}{product_id} = $incomeItem->ProductID() if ( $incomeItem->ProductID() );

                    }#income item loop

                }#income item list block


            }# license loop

        }#crossedLicenseList loop


        # Calculate the crossed advances and/or adjustments
        #
        # Note: these transactions are for ALL crossed licenses attached
        # to the track.
        #
        my $xmlObj = $track->MechanicalStatementCrossedLicenseTransactionList();
        my $licenseTransactionList  = $xmlObj->getList();
        my $licenseTransactionCount = scalar(@$licenseTransactionList);

        if ( $licenseTransactionCount != 0 )
        {
            foreach my $licenseTransaction (@$licenseTransactionList)
            {
                if ( $licenseTransaction->TypeCode == 3 )
                {
                    $crossedAdvanceTotal += $licenseTransaction->Amount();
                }
                else
                {
                    $crossedAdjustmentTotal += $licenseTransaction->Amount();
                }
            }
        }



        #-------------------------------------------------------
        #
        #  U N C R O S S E D    L I C E N S E    S E C T I O N
        #
        #-------------------------------------------------------


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

            my $licenseID = $license->TrackLicenseID;

            my $trackLicenseData = RPS::DB::Item::TrackLicense->Lookup(track_license_id => $license->TrackLicenseID());
            my $issuerLicenseID = $trackLicenseData->issuer_license_id;


            # Get the mechanical statement license totals for the license
            #

            $licenseData{$licenseID}{cross_collateralized} = $trackLicenseData->cross_collateralized;

            $licenseData{$licenseID}{previous_advance_balance} = $license->PreviousAdvanceBalance();
            $licenseData{$licenseID}{subtotal}                 = $license->Subtotal();
            $licenseData{$licenseID}{adjusted_subtotal}        = $license->AdjustedSubtotal();
            $licenseData{$licenseID}{advance_balance}          = $license->AdvanceBalance();


            # Get the income items associated with this license
            #
            my $incomeItemList       = $license->MechanicalStatementItemList()->getList();
            my $incomeItemListLength = scalar(@$incomeItemList);

            # If there are income items, aggregate some totals
            #
            if ( $incomeItemListLength != 0 )
            {

                # Process the mechanical statement items tied to this license,
                # aggregating totals along the way.
                #

                foreach my $incomeItem (@$incomeItemList)
                {
                    my $licenseID   = $incomeItem->TrackLicense->TrackLicenseID;
                    $licenseData{$licenseID}{net_units}   += $incomeItem->NetUnits();

                    #$licenseData{$licenseID}{gross_units} += $incomeItem->GrossUnits();
                    $licenseData{$licenseID}{gross_units} += $incomeItem->Sales();

                    $licenseData{$licenseID}{reserved}    += $incomeItem->Reserved();
                    $licenseData{$licenseID}{liquidated}  += $incomeItem->Liquidated();
                    $licenseData{$licenseID}{carryover}   += $incomeItem->Carryover();
                    $licenseData{$licenseID}{returns}     += $incomeItem->Returns();

                    $licenseData{$licenseID}{upc}  = $incomeItem->UPC() if ( $incomeItem->UPC() );
                    $licenseData{$licenseID}{product_id} = $incomeItem->ProductID() if ( $incomeItem->ProductID() );
                }
            }# income item list block


            my $xmlObj = $license->MechanicalStatementLicenseTransactionList();
            my $licenseTransactionList  = $xmlObj->getList();
            my $licenseTransactionCount = scalar(@$licenseTransactionList);

            # Add the transactions up into two buckets, adjustments and advances.
            #
            my $advanceTotal    = 0;
            my $adjustmentTotal = 0;

            if ( $licenseTransactionCount != 0 )
            {
                foreach my $licenseTransaction (@$licenseTransactionList)
                {
                    my $transactionType;
                    if ( $licenseTransaction->TypeCode == 3 )
                    {
                        $advanceTotal += $licenseTransaction->Amount();
                    }
                    else
                    {
                        $adjustmentTotal += $licenseTransaction->Amount();
                    }
                }
            }

            $licenseData{$licenseID}{advance_total}    = $advanceTotal;
            $licenseData{$licenseID}{adjustment_total} = $adjustmentTotal;


        }# uncrossed license list block


        # Output the report lines
        #
        foreach my $licenseID (keys %licenseData)
        {
            ($row, $workbook, $worksheet) = incrementRow($row, $workbook, $worksheet);

            my $licenseObj        = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $licenseID );
            my $productTypeID     = $licenseObj->product_type_id;
            my $share             = $licenseObj->share;
            my $regionID          = $licenseObj->region_id;
            my $dateModified      = $licenseObj->date_modified;
            my $percentageOfSales = $licenseObj->percentage_of_sales;
            my $freeGoods         = $licenseObj->free_goods || 0;
            my $miscDeduction     = $licenseObj->misc_deduction || 0;

            my $publisherObj = RPS::DB::Item::Publisher->Lookup( publisher_id => $licenseObj->publisher_id );

            my $publisherClientAccountID = $publisherObj->client_account_id;
            my $adminName                = _getAdminName( $publisherObj );
            my $agentName                = _getAgentName( $publisherObj );
            my $publisherType            = _getPublisherType( $publisherObj );

            my $cross_collateralized     = $licenseData{$licenseID}{cross_collateralized};

            my $previousAdvanceBalance;
            my $subtotal;
            my $adjustedSubtotal;
            my $licenseBalanceForward;

            if ( $cross_collateralized )
            {
                $previousAdvanceBalance  = $licenseData{$licenseID}{crossed_previous_advance_balance};

                my $crossedSubtotal          = $licenseData{$licenseID}{crossed_subtotal};
                my $crossedAdjustedSubtotal  = $licenseData{$licenseID}{crossed_adjusted_subtotal};

                $subtotal                    = $licenseData{$licenseID}{subtotal};

                $adjustedSubtotal            = $licenseData{$licenseID}{adjusted_subtotal};

                $licenseBalanceForward       = $licenseData{$licenseID}{crossed_advance_balance};

                if ( ($previousAdvanceBalance != 0) || ( $crossedSubtotal != $crossedAdjustedSubtotal ) )
                {
                    $adjustedSubtotal = $crossedAdjustedSubtotal;
                }
            }
            else
            {
                $previousAdvanceBalance  = $licenseData{$licenseID}{previous_advance_balance};
                $subtotal                = $licenseData{$licenseID}{subtotal};
                $adjustedSubtotal        = $licenseData{$licenseID}{adjusted_subtotal};
                $licenseBalanceForward   = $licenseData{$licenseID}{advance_balance};
            }

            my $advanceTotal             = $licenseData{$licenseID}{advance_total};
            my $adjustmentTotal          = $licenseData{$licenseID}{adjustment_total};

            my $licenseConfig = RPS::DB::Item::Product->ProductTypeIDToString( $productTypeID );
            
            my $albumTitle;
            my $productID = $licenseData{$licenseID}{product_id};
            if ($productID) 
            {
                my $product = RPS::DB::Item::Product->Lookup( product_id => $productID );
                $albumTitle = _getAlbumTitle($product, $track->AlbumName());
            } 
            else
            {
                $albumTitle = $track->AlbumName();    
            }             

            $worksheet->write_string($row, 0, $statement->Publisher()->PublisherName());  # A - publisher-name
            $worksheet->write_string($row, 1, $publisherClientAccountID);                 # B - client-no
            $worksheet->write_string($row, 2, $publisherType);                            # C - publisher-type
            $worksheet->write_string($row, 3, $adminName);                                # D - publisher-admin
            $worksheet->write_string($row, 4, $agentName);                                # E - publisher-agent
            $worksheet->write_string($row, 5, $track->SongTitle());                       # F - track-title
            $worksheet->write_string($row, 6, $track->ISRC());                            # G - isrc
            $worksheet->write_string($row, 7, $track->CatalogNumber());                   # H - catalog-no
            $worksheet->write_string($row, 8, $albumTitle);                               # I - album-title
            $worksheet->write_string($row, 9, $licenseData{$licenseID}{upc});             # J - upc
            $worksheet->write_string($row, 10, $licenseConfig);                           # K - license-config
            $worksheet->write_string($row, 11, regionIDToName( $regionID ));              # L - region
            $worksheet->write($row, 12, formatPercent( $share ), $formats->{right} );                  # M - share
            $worksheet->write($row, 13, $licenseData{$licenseID}{gross_units});    # N - total-gross-units
            $worksheet->write($row, 14, formatPercent( $percentageOfSales ));      # O - percent-of-sales
            $worksheet->write($row, 15, formatPercent( $freeGoods ));              # P - free-goods
            $worksheet->write($row, 16, formatPercent( $miscDeduction ));          # Q - misc
            $worksheet->write($row, 17, $licenseData{$licenseID}{reserved});       # R - total-reserves
            $worksheet->write($row, 18, $licenseData{$licenseID}{liquidated});     # S - total-liquidations
            $worksheet->write($row, 19, $licenseData{$licenseID}{returns});        # T - total-returns
            $worksheet->write($row, 20, $licenseData{$licenseID}{carryover});      # U - total-carryover
            $worksheet->write($row, 21, $licenseData{$licenseID}{net_units});      # V - total-net-units
            $worksheet->write($row, 22, $crossedAdjustmentTotal);                  # W - total-cc-adjustments (NB: we don't show crossedAdvanceTotal)
            $worksheet->write($row, 23, formatMoney( $subtotal ));                 # X - license-subtotal (current period license subtotal)
            $worksheet->write($row, 24, formatMoney( $adjustmentTotal ));          # Y - total-adjustments
            $worksheet->write($row, 25, formatMoney( $advanceTotal ));             # Z - total-advances
            $worksheet->write($row, 26, formatMoney( $previousAdvanceBalance ));   # AA - previous-license-balance
            $worksheet->write($row, 27, formatMoney( $licenseBalanceForward ));    # AB - license-balance-foward
            $worksheet->write($row, 28, formatMoney( $adjustedSubtotal ));         # AC - license-adjusted-subtotal
            $worksheet->write($row, 29, $licenseID);                               # AD - rs-license-id
            $worksheet->write_string($row, 30, $dateModified);                     # AE - date-modified

        }#license aggregated data loop


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

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);
}

# 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 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 _getAlbumTitle 
{
    my ($product, $albumTitle) = @_;
    
    my $title;
    
    # If this is a track product, we need to get the title from the parent.
    if ($product->product_type_id == RPS::DB::Item::Product::kProductTypeDigitalTrack && $product->parent_product_id)
    {
        my $parentProduct = RPS::DB::Item::Product->Lookup(product_id => $product->parent_product_id);
        $title = $parentProduct->title;
    }
    else 
    {
        $title = $product->title;
    }	
    
    # Just in case we don't find a product title, let's fall back to the album title.
    if (!$title) 
    {
        $title = $albumTitle;
    }
    
    return $title;	       
}

sub _getPublisherType
{
    my($publisher) = @_;
    assert($publisher);

    if( $publisher->is_agency )
    {
       return "Agent";
    }
    elsif( $publisher->is_admin )
    {
       return "Admin";
    }
    else
    {
       return "Standard";
    }
}

sub _getAdminName
{
    my($publisher) = @_;
    assert($publisher);
    my $name;
    if( $publisher->admin_id )
    {
       my $o = RPS::DB::Item::Publisher->Lookup(
           publisher_id => $publisher->admin_id,
       );
       $name = $o->publisher_name;
    }
    return $name;
}

sub _getAgentName
{
    my($publisher) = @_;
    assert($publisher);
    my $name;
    if( $publisher->agent_id )
    {
       my $o = RPS::DB::Item::Publisher->Lookup(
           publisher_id => $publisher->agent_id,
       );
       $name = $o->publisher_name;
    }
    return $name;
}

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

    $worksheet->freeze_panes(1,0);
    $worksheet->write($row, 0, 'publisher-name', $formats->{header});             # A
    $worksheet->write($row, 1, 'client-no', $formats->{header});                  # B
    $worksheet->write($row, 2, 'publisher-type', $formats->{header});             # C
    $worksheet->write($row, 3, 'publisher-admin', $formats->{header});            # D
    $worksheet->write($row, 4, 'publisher-agent', $formats->{header});            # E
    $worksheet->write($row, 5, 'track-title', $formats->{header});                # F
    $worksheet->write($row, 6, 'ISRC', $formats->{header});                       # G
    $worksheet->write($row, 7, 'catalog-no', $formats->{header});                 # H
    $worksheet->write($row, 8, 'album-title', $formats->{header});                # I
    $worksheet->write($row, 9, 'upc', $formats->{header});                        # J
    $worksheet->write($row, 10, 'license-config', $formats->{header});            # K
    $worksheet->write($row, 11, 'region', $formats->{header});                    # L
    $worksheet->write($row, 12, 'share', $formats->{header});                     # M
    $worksheet->write($row, 13, 'total-gross-units', $formats->{header});         # N
    $worksheet->write($row, 14, 'percent-of-sales', $formats->{header});          # O
    $worksheet->write($row, 15, 'free-goods', $formats->{header});                # P
    $worksheet->write($row, 16, 'misc', $formats->{header});                      # Q
    $worksheet->write($row, 17, 'total-reserves', $formats->{header});            # R
    $worksheet->write($row, 18, 'total-liquidations', $formats->{header});        # S
    $worksheet->write($row, 19, 'total-returns', $formats->{header});             # T
    $worksheet->write($row, 20, 'total-carryover', $formats->{header});           # U
    $worksheet->write($row, 21, 'total-net-units', $formats->{header});           # V
    $worksheet->write($row, 22, 'total-cc-adjustments', $formats->{header});      # W
    $worksheet->write($row, 23, 'license-subtotal', $formats->{header});          # X
    $worksheet->write($row, 24, 'total-adjustments', $formats->{header});         # Y
    $worksheet->write($row, 25, 'total-advances', $formats->{header});            # Z
    $worksheet->write($row, 26, 'previous-license-balance', $formats->{header});  # AA
    $worksheet->write($row, 27, 'license-balance-forward', $formats->{header});   # AB
    $worksheet->write($row, 28, 'license-adjusted-subtotal', $formats->{header}); # AC
    $worksheet->write($row, 29, 'rs-license-id', $formats->{header});             # AD
    $worksheet->write($row, 30, 'license-date-modified', $formats->{header});     # AE

    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->{mechanicalRunID} = $opt{r};
    $settings->{clientID}        = $opt{c};
    $settings->{outputPath}      = $opt{p};
    $settings->{mechanicalStatementID} = $opt{s};

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

sub usage
{
    print STDERR
"\nusage: $0 -c <client_id> -r <mechanical_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 <mechanical_run_id>\tThe id of the mechanical 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";
    }
}