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

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

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

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

use lib '/app/tools/rps/lib';
use RPS::Statement::Mechanical::StatementFull;
use RPS::Statement::Mechanical::StatementLicenseTransactionList;
use RPS::Payor::Payor;
use RPS::Publisher::Publisher;
use RPS::Mechanical::MechanicalRun;
use RPS::DB::Item::Label;
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::ProductType;
use RPS::DB::Item::TrackLicense;
use RPS::DB::Item::MechanicalStatement;

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

createJumboStatementExcel($options{clientID}, $options{mechanicalRunID}, $options{outFilePath});


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


sub createJumboStatementExcel
{
    my ($clientID, $mechanicalRunID, $outFilePath) = @_;
    
    print STDERR "starting process to create excel file \n";
    print STDERR "clientID: ".$clientID." \n";
		print STDERR "mechanicalRunID: ".$mechanicalRunID." \n";
		print STDERR "outFilePath: ".$outFilePath." \n";
    
    # Instantiate the application singleton object.
    #
    my $appSingleton = Common::RSApp->new(clientID => $clientID);
    
    my $outFileName = "mechanical_run_$mechanicalRunID.xls";    

    # If an output file path was not specified, we'll just create the file right here.
    #
    if (! $outFilePath)
    {
        $outFilePath = $outFileName;
    }
    else 
    {
		    # 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";
		    }
		    # Now that the directory is there, let's add the file name so that we have the complete path.
		    #
		    $outFilePath .=  $outFileName;	
    }
    
    print STDERR "creating excel file here: ".$outFilePath." \n";
    

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


		print STDERR "workbook created \n";

		# Set up the special formats.   
		#		
		($workbook) = addFormats($workbook);
		
    
    # Add the first worksheet.
    #
    my $row;
    my $worksheet;
		($row, $workbook, $worksheet) = addWorksheet($row, $workbook, $worksheet);    


		# Grab all statements in run.
		#      
            		
    my $statementList = RPS::DB::Item::MechanicalStatement->Get
    (
        runID => $mechanicalRunID,
        sortByPublisherName => 1,
    );   
    
    print STDERR "fetched statements \n";
    

		# Iterate through all statements in run.
		#
    while ($statementList->hasNext())
    {
      my $statement = $statementList->next();
      my $statementID = $statement->mechanical_statement_id;	
      
      print STDERR "processing statement: ".$statementID." \n";	

			my $fullStatement = RPS::Statement::Mechanical::StatementFull->new(mechanicalStatementID => $statementID, focusPublisherID => 'all');
	
	    # Determine if this is an admin (or agent) or regular publisher. 
	    #
	    if ($fullStatement->Publisher()->IsAdmin() == 1
	     || $fullStatement->Publisher()->IsAgency() == 1)
	    {

	    	# Loop through all publishers for admins (and agents).
	    	#
	    	my $publisherList = $fullStatement->MechanicalStatementPublisherList()->getList();
				foreach my $publisher (@$publisherList)
				{
					($row, $workbook, $worksheet) = addJumboDetails($row, $publisher, $workbook, $worksheet, $formats);	
				}
	    } 
	    else 
	    {
	 			
	    	# Not an admin (or agent), so just do this once.
	    	($row, $workbook, $worksheet) = addJumboDetails($row, $fullStatement, $workbook, $worksheet, $formats);
	    }
		}

    # All done, save and clean up.
    #
    $workbook->close() or die "Error closing file: $!";
    
    print STDERR "done! \n";
    
}

sub addJumboHeaders 
{
		my ($row, $worksheet) = @_;
		
		$worksheet->write($row, 0, 'Publisher');
		$worksheet->write($row, 1, 'Admin');
		$worksheet->write($row, 2, 'Agent');
		$worksheet->write($row, 3, 'Label');
		$worksheet->write($row, 4, 'Album');
		$worksheet->write($row, 5, 'Track');
		$worksheet->write($row, 6, 'Catalog #');
		$worksheet->write($row, 7, 'UPC');
		$worksheet->write($row, 8, 'ISRC');
		$worksheet->write($row, 9, 'Config');
		$worksheet->write($row, 10, 'Region');
		$worksheet->write($row, 11, 'Rate Period');
		$worksheet->write($row, 12, 'Base Rate');
		$worksheet->write($row, 13, '% of Rate');
		$worksheet->write($row, 14, 'Share');
		$worksheet->write($row, 15, 'Net Rate');
		$worksheet->write($row, 16, '% of Sales');
		$worksheet->write($row, 17, 'Packaging');
		$worksheet->write($row, 18, 'Free Goods');
		$worksheet->write($row, 19, 'Misc.');
		$worksheet->write($row, 20, 'Sales');
		$worksheet->write($row, 21, 'Reserves');
		$worksheet->write($row, 22, 'Liquidations');
		$worksheet->write($row, 23, 'Returns');
		$worksheet->write($row, 24, 'Carryover');
		$worksheet->write($row, 25, 'Gross Units');
		$worksheet->write($row, 26, 'Total');
		$worksheet->write($row, 27, 'Accrual');
		$worksheet->write($row, 28, 'Net Units');
		$worksheet->write($row, 29, 'Amount Due');
		$worksheet->write($row, 30, 'License ID');
		$worksheet->write($row, 31, 'Previous Balance');
		$worksheet->write($row, 32, 'Adjustments');
		$worksheet->write($row, 33, 'Advances');
		$worksheet->write($row, 34, 'License Balance');	
		
		return ($row, $worksheet);
}	


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

    # Add the track details.
    #
    my $trackList = $statement->MechanicalStatementTrackList()->getList();
    foreach my $track (@$trackList)
    {
    	# Look up the Label name.
    	#
    	my $trackID = $track->TrackID();
    	my $labelName = RPS::DB::Item::Label->GetLabelNameFromTrackID($trackID);

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

				#print STDERR "processing license: ".$license->TrackLicenseID()." \n";
			
    		# Figure out what we're going to use for License Balance.
    		#
		    my $licenseBalance;
		    if ($license->AdjustedSubtotal != 0) 
		    {
		    	$licenseBalance = $license->AdjustedSubtotal;
		    }
		    else 
		    {
		    	$licenseBalance = $license->AdvanceBalance;
		    }		    	

				# Add up the transactions now for use later.
				#
	      my $xmlObj = $license->MechanicalStatementLicenseTransactionList();
	    	my $licenseTransactionList = $xmlObj->getList();
	    	my $licenseTransactionCount = scalar(@$licenseTransactionList);						
				my $adjustments = 0;
				my $advances = 0;
				if ($licenseTransactionCount != 0)
				{
		    	foreach my $licenseTransaction (@$licenseTransactionList)
					{
		
		  			if ($licenseTransaction->TypeCode == 3) 
		  			{
		  				# Advances
		  				$advances += $licenseTransaction->Amount();
		  			} 
		  			else
		  			{
		  				# Adjustments
		  				$adjustments += $licenseTransaction->Amount();
		  			}  		    	  
					
					}
						
				}		    	
	    	

				# One day we'll capture everything we need during the royalty run.
				# But until that day, we have to hit the live license for some things.
				#
	    	my $trackLicense = RPS::DB::Item::TrackLicense->Lookup(track_license_id => $license->TrackLicenseID());


	    	my $incomeItemList = $license->MechanicalStatementItemList()->getList();
	    	my $incomeItemListLength = scalar(@$incomeItemList);	    	
	    	
	    	if ($incomeItemListLength != 0)
	    	{
	    	
		    	foreach my $incomeItem (@$incomeItemList)
		    	{    	  		
	
						($row, $workbook, $worksheet) = incrementRow($row, $workbook, $worksheet);	
						
						$worksheet->write_string($row, 0, $statement->Publisher()->PublisherName());
						if ($statement->Publisher()->AdminID())
						{
							$worksheet->write_string($row, 1, $statement->Publisher()->AdminName());
						}
						if ($statement->Publisher()->AgentID())
						{
							$worksheet->write_string($row, 2, $statement->Publisher()->AgentName());
						}
						$worksheet->write_string($row, 3, $labelName);
						$worksheet->write_string($row, 4, $track->AlbumName());				
						$worksheet->write_string($row, 5, $track->SongTitle());
						$worksheet->write_string($row, 6, $track->CatalogNumber() , $formats->{left});										
						$worksheet->write_string($row, 7, $incomeItem->UPC());
						$worksheet->write_string($row, 8, $track->ISRC());										
						$worksheet->write_string($row, 9, productCodeToName($incomeItem->ProductTypeID()));
						$worksheet->write_string($row, 10, regionIDToName($license->RegionID()));
						$worksheet->write_string($row, 11, $incomeItem->RatePeriod());
						$worksheet->write($row, 12, Common::Client::Current()->Locale()->currencyFormat()->symbol().formatNumber($incomeItem->BaseRate()) , $formats->{right});
						$worksheet->write($row, 13, formatPercent($incomeItem->TrackLicense->RatePercentage()) , $formats->{right});				
						$worksheet->write($row, 14, formatPercent($incomeItem->TrackLicense->Share()) , $formats->{right});						
						$worksheet->write($row, 15, Common::Client::Current()->Locale()->currencyFormat()->symbol().formatNumber($incomeItem->NetRate()) , $formats->{right});
						
						# Pulling these values from the live license.
						#
						$worksheet->write($row, 16, formatPercent($trackLicense->percentage_of_sales) , $formats->{right});
						$worksheet->write($row, 17, formatPercent($trackLicense->packaging_deduction) , $formats->{right});
						$worksheet->write($row, 18, formatPercent($trackLicense->free_goods) ,$formats->{right});
						$worksheet->write($row, 19, formatPercent($trackLicense->misc_deduction) , $formats->{right});
						
						$worksheet->write($row, 20, formatNumber($incomeItem->Sales()) , $formats->{right});
						$worksheet->write($row, 21, formatNumber($incomeItem->Reserved()) , $formats->{right});
						$worksheet->write($row, 22, formatNumber($incomeItem->Liquidated()) , $formats->{right});
						$worksheet->write($row, 23, formatNumber($incomeItem->Returns()) , $formats->{right});
						$worksheet->write($row, 24, formatNumber($incomeItem->PreviousCarryover()) , $formats->{right});
						$worksheet->write($row, 25, formatNumber($incomeItem->GrossUnits()) , $formats->{right});
						$worksheet->write($row, 26, formatMoney($incomeItem->Total()) , $formats->{right});					
						$worksheet->write($row, 27, formatNumber($incomeItem->Carryover()) , $formats->{right});					
						$worksheet->write($row, 28, formatNumber($incomeItem->NetUnits()) , $formats->{right});
						$worksheet->write($row, 29, formatMoney($incomeItem->AmountPaid()), $formats->{right});
						$worksheet->write_string($row, 30, $license->TrackLicenseID() , $formats->{left});
						$worksheet->write($row, 31, formatMoney($license->PreviousAdvanceBalance()) , $formats->{right});
						$worksheet->write($row, 32, formatMoney($adjustments) , $formats->{right});
						$worksheet->write($row, 33, formatMoney($advances) , $formats->{right});
						$worksheet->write($row, 34, formatMoney($licenseBalance) , $formats->{right});
						
		      }
		      
		    }
	
		    else 
		    {
		    	
	    		# Even if there are no income items, we still want to display the track info.
	    		#
	    		
	    		($row, $workbook, $worksheet) = incrementRow($row, $workbook, $worksheet);
	    		
					$worksheet->write_string($row, 0, $statement->Publisher()->PublisherName());
					if ($statement->Publisher()->AdminID())
					{
						$worksheet->write_string($row, 1, $statement->Publisher()->AdminName());
					}
					if ($statement->Publisher()->AgentID())
					{
						$worksheet->write_string($row, 2, $statement->Publisher()->AgentName());
					}
					$worksheet->write_string($row, 3, $labelName);
					$worksheet->write_string($row, 4, $track->AlbumName());				
					$worksheet->write_string($row, 5, $track->SongTitle());
					$worksheet->write_string($row, 6, $track->CatalogNumber() , $formats->{left});										
					$worksheet->write_string($row, 8, $track->ISRC());										
					$worksheet->write_string($row, 10, regionIDToName($license->RegionID()));
					$worksheet->write_string($row, 30, $license->TrackLicenseID() , $formats->{left});
					$worksheet->write($row, 31, formatMoney($license->PreviousAdvanceBalance()) , $formats->{right});
					$worksheet->write($row, 32, formatMoney($adjustments) , $formats->{right});
					$worksheet->write($row, 33, formatMoney($advances) , $formats->{right});
					$worksheet->write($row, 34, formatMoney($licenseBalance) , $formats->{right});				
		    } 
								       
	    }

    }

    return ($row, $workbook, $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);
    
    # Not needed at the moment...
    # Specify widths for some columns.
    #
    
    # Amount column for summary table
		#$worksheet->set_column(3, 3, 12);
		
		# UPC column for detail table
		#$worksheet->set_column(8, 8, 12);
		
		# Region column for detail table
		#$worksheet->set_column(10, 10, 12);		
		
		# Amount Due column for detail table
		#$worksheet->set_column(15, 15, 12);				
    
    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);	
		
		$formats = 
    {
        bold 							=> $formatB,
        right_bold 				=> $formatRB,
        right 						=> $formatR,
        left 							=> $formatL,
        underline_bold 		=> $formatUB,
        right_underline 	=> $formatUR,
        underline 				=> $formatU,
    };	
    
    return ($workbook);   	
}

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

sub priceLevelIDToName
{
    my ($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 productCodeToName
{
    my ($id) = @_;
    return _genericMapAccessor('RPS::DB::Item::ProductType', 'product_type_id', $id);
}



#
# Boring script stuff below...
#

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

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

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

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


sub usage
{
    print STDERR "\nusage: $0 -c <client_id> -r <mechanical_run_id> [-p <output file>]\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 convert to excel\n";       
    print STDERR "\t-p <output file>\tThe output file path. Optional. If not provided, we'll make one up\n";
}


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

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