#!/usr/bin/perl #------------------------------------------------------------ # Copyright (C) 2007 RoyaltyShare, Inc. All Rights Reserved #------------------------------------------------------------ use strict; use Data::Dumper; use Getopt::Std; use POSIX qw(ceil); use POSIX qw(:sys_wait_h :signal_h :errno_h); use Sys::Hostname; use Carp; use IO::Handle; use lib '/app/tools/common/lib'; use Common::DB::Item; use Common::Assert; use Common::TextProgressBar; use Common::DB::Item::Client; use Common::RSMath; #use lib '/app/tools/data_classes/lib'; #use File::Sale; use lib '/app/tools/raptor/lib'; #use Raptor::Sale::Sale; use Raptor::DB::Item::Sale; use Raptor::DB::Item::File; use lib '/app/tools/rps/lib'; use RPS::DB::Item::Service; use RPS::Statement::Mechanical::PDF; use RPS::Statement::Mechanical::Text; use RPS::DB::Item::TrackLicense; use RPS::DB::Item::MechanicalCarryover; use RPS::DB::Item::MechanicalRunCarryover; use RPS::DB::Item::Track; use RPS::DB::Item::Master; use RPS::DB::Item::Label; use RPS::DB::Item::Artist; #use RPS::DB::Item::License; use RPS::DB::Item::LicenseReserve; use RPS::DB::Item::LicenseReserveRun; use RPS::DB::Item::ReserveLiquidation; use RPS::DB::Item::StatRate; use RPS::DB::Item::ProductTrack; use RPS::DB::Item::Song; use RPS::DB::Item::PendingTransaction; use RPS::DB::Item::MechanicalRunUnlicensedTrackLog; use RPS::DB::Item::PublisherTrackCrossedLicenseAccount; use RPS::DB::Item::MechanicalStatementCrossedLicenseTransaction; use RPS::DB::Item::Publisher; use RPS::DB::Item::Product; use RPS::DB::Item::ProductType; #use RPS::DB::Item::ControlledComposition; use RPS::DB::Item::AlbumContract; use RPS::DB::Item::BonusTrack; use RPS::DB::Item::MechanicalRun; use RPS::DB::Item::MechanicalStatement; use RPS::DB::Item::MechanicalStatementPublisher; use RPS::DB::Item::MechanicalStatementItem; use RPS::DB::Item::MechanicalStatementItem; use RPS::DB::Item::MechanicalStatementTrack; use RPS::DB::Item::MechanicalStatementLicense; use RPS::DB::Item::MechanicalStatementAdjustmentItem; use RPS::DB::Item::SaleMechanicalStatementItemMap; use RPS::DB::Item::PublisherAdvance; use RPS::DB::Item::Payor; use RPS::DB::Item::PublisherAccount; use RPS::DB::Item::MechanicalStatementLicenseTransaction; use RPS::DB::Item::SaleRunMap; use RPS::License::TrackLicense; use RPS::License::TrackLicenseList; use RPS::Statement::Mechanical::StatementTransaction; use RPS::Statement::Mechanical::StatementDisplay; use RPS::Mechanical::US::Job::CreatePDFStatements; use RPS::Mechanical::US::Job::CreateTextStatements; use RPS::RoyaltyRun::Status; use RPS::XMLCache; # This constant tunes how often we spit out a 'processed X sales' message # use constant kSaleProgressQuanta => 20; # Caches # my $gTrackLicenseReturnsMap; my $gPublisherStatementMap = {}; my $gPublisherMap; my $gSongMap; my $gMasterMap; my $gTrackMap; my $gAlbumMap; my $gLabelMap; my $gArtistMap; my $gProductMap; my $gProductTypeMap; my $gTrackLicenseMap; my $gStatRateTable; my $gStatRateByDate; my %gPendingTransPubMap; # Allow tuning of the verbosity of our output. # my $gVerbosityLevel = 1; my $gProgress = 0; my $gProfile = 0; # Parse the command-line options. # my %options; _parseCommandLine( \%options ); # Instantiate the application singleton object. # #my $appSingleton = Common::RSApp->new(clientID => $options{clientID}); my $payorsToRun; # Get the hostname we are running on. # my $hostname = hostname(); my $gRunID = $options{runID}; my $gClient; # Now, we're going to fork off a child process. # This way I can tell (and therefore log) whether we exited cleanly or because # of some exception # my $gChildPID = fork(); if ($gChildPID) { _parent(); } else { _child(); } sub _child { # Install a signal handler to catch ctrl-c # $SIG{INT} = \&CTRL_C; # Instantiate the application singleton object. # my $appSingleton = Common::RSApp->new( clientID => $options{clientID} ); # Get the client data # $gClient = Common::DB::Item::Client->Lookup( client_id => $options{clientID} ); # Read in the run data. # my $obj = RPS::DB::Item::MechanicalRun->Lookup( mechanical_run_id => $gRunID ); if ( !$obj ) { die "ERROR: run_id $gRunID is not valid\n"; } # sanity check - make sure this run is in the 'waiting' state # if ( RPS::RoyaltyRun::Status::kWaitingToRun != $obj->status() ) { die "ERROR: run_id $gRunID is not in the 'waiting to run' state\n"; } $obj->start_time(Common::DB::Item::kDateTimeNow); $obj->pid($$); $obj->hostname($hostname); $obj->status(RPS::RoyaltyRun::Status::kRunning); $obj->save(); my $startDate = $obj->start_date; my $endDate = $obj->end_date; my $payorsToRun = [ $obj->payor_id ]; # We want to know the accrued unpaid balance... # my $gUnallocatedBalance; # Now... do the run! # _report( "*** Mechanical Royalty Run $gRunID for client id " . $options{clientID} . " for period $startDate - $endDate" ); _run_mechanicals( $startDate, $endDate, $payorsToRun ); # Save the accured unpaid balance. # $obj->unallocated_balance($gUnallocatedBalance); $obj->save(); # As an optimization, let's pre-populate the XMLCache with these statements. # That will make it nice and snappy. # _populateCache( $obj->mechanical_run_id ); # Go forth and create pdf files, I Command You! # # Create the new job, and put it in the queue. # _report("*** adding PDF statements job to queue"); my $statementsPath = RPS::Statement::Mechanical::PDF::StatementPathFromRunID( $gRunID, $options{clientID} ); my $jobArgs = RPS::Mechanical::US::Job::CreatePDFStatements->new( runID => $gRunID, clientID => $options{clientID}, filePath => $statementsPath, ); my $job = $jobArgs->enqueue(); _report("*** done adding PDF statements job to queue!"); # And finally, let's create some text versions of the statements. # # Create the new job, and put it in the queue. # _report("*** adding Text statements job to queue"); my $statementsPath = RPS::Statement::Mechanical::Text::StatementPathFromRunID( $gRunID, $options{clientID} ); my $jobArgs = RPS::Mechanical::US::Job::CreateTextStatements->new( runID => $gRunID, clientID => $options{clientID}, filePath => $statementsPath, ); my $job = $jobArgs->enqueue(); _report("*** done adding Text statements job to queue!"); # Mark the run as complete # # $obj->status(RPS::RoyaltyRun::Status::kComplete); # $obj->end_time(Common::DB::Item::kDateTimeNow); # $obj->save(); } my $gChildExitCode; my $gChildIsRunning; sub _parent { local $SIG{CHLD} = \&REAPER; local $SIG{INT} = 'IGNORE'; $gChildIsRunning = 1; while ($gChildIsRunning) { sleep(1); } my $status; if ( 0 == $gChildExitCode ) { $status = RPS::RoyaltyRun::Status::kComplete; } elsif ( 1 == $gChildExitCode ) { $status = RPS::RoyaltyRun::Status::kAborted; } else { $status = RPS::RoyaltyRun::Status::kError; } my $appSingleton = Common::RSApp->new( clientID => $options{clientID} ); my $obj = RPS::DB::Item::MechanicalRun->Lookup( mechanical_run_id => $gRunID ); $obj->status($status); $obj->end_time(Common::DB::Item::kDateTimeNow); $obj->save(); exit(0); } sub REAPER { my $deadChildPID = waitpid( -1, &WNOHANG ); if ( -1 == $deadChildPID ) { # no child - ignore this } elsif ( WIFEXITED($?) ) { # The child really exited. # Let's find out why. # $gChildExitCode = $? >> 8; $gChildIsRunning = 0; } $SIG{CHLD} = \&REAPER; } sub CTRL_C { _report("caught a ctrl-c, aborting"); exit(1); } sub _run_mechanicals { my ( $startDate, $endDate, $payorIDs ) = @_; # Transform the payorIDs array into an identity hash. # my %payorIDMap; foreach my $payorID (@$payorIDs) { $payorIDMap{$payorID} = 1; } my %adjustmentState; my %controlledCompState; # # --- Discover new ccomp-related licenses. # _report("--- finding new licenses"); my $ccompTrackLicensesToAdjust = _findLicensesToAdjust( $startDate, $endDate, \%payorIDMap ); # _report(Dumper($ccompTrackLicensesToAdjust), 4); # # --- Process Sales # _report("--- calculating license royalties"); _processSales( $ccompTrackLicensesToAdjust, \%adjustmentState, \%controlledCompState, $startDate, $endDate, \%payorIDMap ); _report( "adjustment state: " . Dumper( \%adjustmentState ), 4 ); _report( "controlled comp state: " . Dumper( \%controlledCompState ), 4 ); # # --- Pay royalties on Controlled Composition Clauses # _report("--- calculating controlled composition royalties"); _processControlledComp( \%controlledCompState, $startDate, $endDate ); # # --- Process 'adjustments' on Controlled Comps # # skip this for Dualtone right now... if ( $options{clientID} == 14 ) { _report("--- skipping controlled composition adjustments"); } else { _report("--- calculating controlled composition adjustments"); _processControlledCompAdjustments( \%adjustmentState, $startDate, $endDate ); } # # --- Figure in License Advances and calculate per-track subtotals # _report("--- calculating advances, aggregating subtotals by song"); _finalizeStatements( $startDate, $endDate ); # # --- Flag the statements as 'pending', and calculate total owed. # _report("--- marking statements as pending"); _markStatementsAsPending(); } # Pass dates in 'yyyy-mm-dd' format. # sub _parseCommandLine { my ($settings) = @_; my %opt; getopts( 'r:c:V:', \%opt ); if ( !$opt{r} || !$opt{c} ) { _usage(); exit(1); } $settings->{runID} = $opt{r}; $settings->{clientID} = $opt{c}; if ( defined $opt{V} ) { $gVerbosityLevel = $opt{V}; } } sub _usage { print "\nusage: $0 -c client_id -s start_date -e end_date [-V n]\n"; print "\n"; print "Arguments:\n"; print "\t-c \t\tThe client_id of the client to process\n"; print "\t-r \t\tThe id of the mechanical run to process\n"; print "\t-V \t\t\tVerbosity level - 0 means no output\n"; } sub _processSales { my ( $adjustedTrackLicenses, $adjustmentState, $controlledCompState, $startDate, $endDate, $payorIDMap ) = @_; my $sales = Raptor::DB::Item::Sale->GetDualtoneUnprocessedMechanicalSales( endDate => $endDate, # countryCode => 'US', skipFree => 1, ); my $progressBar = Common::TextProgressBar->new( $sales->size, kSaleProgressQuanta ); if ($gProgress) { $| = 1; $progressBar->display( \*STDOUT ); } # I want to speed up the product-track matching. # I figure we'll be asking for the same product ids multiple times, so why not # cache that here? # (But, I don't want to cache this globally - I'm using too much ram as it is...) # my %productTrackCache; my %productUnitMap; my %overshareMap; my %missingShareMap; _report( "NUMBER OF SALES: " . $sales->size, 2 ); while ( my $sale = $sales->next() ) { # !!! Why do we need both? # !!! Doesn't it make more sense to simply use the _applicable_ stat rate id? # !!! A reasonably major change, but a worth-while one, I'd wager. # # !!! In the short term, though, I can find everyplace where I refer to issueStatRateID # !!! and set it to 0. At this moment, I know that we _never_ actually use issueStatRateID anyway. # !!! I can alter the tables later to convert them to applied_stat_rate_id # We'll need to refer to both the stat rate that applied at the time the product # was released, and the stat rate that applied at the time of the sale. # # Alas, we don't actually have a firm date for these sales - All we have is # a date range, which corresponds to the reporting period from which these # sales were aggregated. This means that there is the possibility that # the units in this sales record fall on opposite sides of a stat rate bump! # At this point, there is nothing we can do to get the 'real' date of sale, so # it would seem the safest thing to do is err on the side of the later date. # my $saleStatRateID = _getStatRateID( $sale->date_end ); # !!! Skipping these for now. # if ( !$saleStatRateID ) { my $mi = RPS::DB::Item::SaleRunMap->Create( sale_id => $sale->sale_id, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeMechanical, status => RPS::DB::Item::SaleRunMap::kStatusNoStatRate, ); $mi->save(); next; } assert( $saleStatRateID, "ERROR - can't match stat rate for sale: " . Dumper($sale) ); my $productID = $sale->product_id; if ( !$productID ) { _report( " !!! skipping sale - missing product id: " . Dumper($sale) ); my $mi = RPS::DB::Item::SaleRunMap->Create( sale_id => $sale->sale_id, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeMechanical, status => RPS::DB::Item::SaleRunMap::kStatusBadProductID, ); $mi->save(); next; } # (XXX) Optimize product lookups # my $product = _getProduct($productID); if ( !$product ) { _report( " !!! skipping sale - invalid product id: " . Dumper($sale) ); my $mi = RPS::DB::Item::SaleRunMap->Create( sale_id => $sale->sale_id, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeMechanical, status => RPS::DB::Item::SaleRunMap::kStatusBadProductID, ); $mi->save(); next; } # We want to skip sales that refer to 'inactive' albums. # if ( _productAlbumIsInactive($product) ) { _report(" !!! skipping sale - album is inactive"); my $mi = RPS::DB::Item::SaleRunMap->Create( sale_id => $sale->sale_id, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeMechanical, status => RPS::DB::Item::SaleRunMap::kStatusInactiveAlbum, ); $mi->save(); next; } # (XXX) Product type id is nice and all, but what I am probably going to want # is to know whether this is digital or not, so I can find the correct license. # my $productTypeID = $product->product_type_id; # !!! This is a bit of a hack - if we don't have a release date, just # use the date_end. # my $releaseDate = $product->release_date; $releaseDate = $sale->date_end unless $releaseDate; # my $issueStatRateID = _getStatRateID($releaseDate); my $issueStatRateID = 16; # Build a data structure that allows us to determine whether # we've already paid mechanicals for a given track_license, # and what the %share was. # This will enable us to detect when a new license has been # added, and we therefore need to re-visit a sale. # -- !!! How about controlled comp? If we discover a new # -- license has been added, we will need to re-calculate what # is owed to the controlled comp... # # -- Primary key will be track_license_id # my $saleID = $sale->sale_id; my $previousSaleStatementItems = _getPreviousSaleStatementItems( $saleID, $issueStatRateID, $saleStatRateID ); _report( "\n\n-----------------------------------------------\nsale: " . Dumper($sale), 4 ); _report( "previous sale statement items: " . Dumper($previousSaleStatementItems), 4 ); # Dereference the number of units in this sale. # # jpk - Now it gets complicated... # For _digital_ sales, the units are the thing. # For _physical_ sales, we have the Sales column and the Returns column. # and units == sales - returns. # But, we do want to track all this for everybody. whee! # my $units = $sale->units; my $numSales = $sale->sales; my $numReturns = $sale->returns; if ( _productIsDigital($productID) ) { if ( $units < 0 ) { $numReturns = ( -1 * $units ); } else { $numSales = $units; } } else { $units = $numSales - $numReturns; _report( "---physical sale: units = $units", 3 ); } # Fetch all the tracks associated with this sale. # jpk - going to update this accessor function to skip tracks that # do not have a valid duration. # if ( !$productTrackCache{ $product->product_id } ) { $productTrackCache{ $product->product_id } = _getTracksFromProduct($product); } my $productTracks = $productTrackCache{ $product->product_id }; _report( " track count: " . scalar @$productTracks, 3 ); # If there aren't any tracks, skip this sale. # (I don't think I want to mark this sale as pending in this case.) # if ( 0 == scalar @$productTracks ) { _report( " no product tracks, skipping", 3 ); my $mi = RPS::DB::Item::SaleRunMap->Create( sale_id => $sale->sale_id, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeMechanical, status => RPS::DB::Item::SaleRunMap::kStatusNoProductTracks, ); $mi->save(); next; } # Determine the service id, if possible. # my $serviceID = _fileIDToService( $sale->file_id ); my $paidLicenses; foreach my $productTrack (@$productTracks) { my $trackID = $productTrack->track_id; # Get the track data. # my $track = _getTrack( $productTrack->track_id ); # !!! When do I take bonus tracks into account? # !!! Perhaps... right here? # if ( RPS::DB::Item::ProductTrack->TrackIsBonusTrack($trackID) ) { _report( " track $trackID is a bonus track", 3 ); # Does this bonus track appear on this product for this service? # my $bonusTrackEntry = RPS::DB::Item::BonusTrack->Lookup( track_id => $trackID, product_id => $productID, service_id => $serviceID, ); if ( !$bonusTrackEntry ) { _report( " not a bonus track for service $serviceID, skipping", 3 ); next; } } # get the appropriate track-licenses for this track. # !!! So, payor ids... I only want to generate statements for the payor ids I was given. # !!! HOWEVER, I don't want to screw up controlled comp calculations by skipping # !!! licenses that don't belong to that set of payors. # !!! This seems to mean, then, that I still have to get every track license... payor_id # !!! will have to be considered later. # # !!! Use the start and end date from the _sale_. # my $trackLicenseList = _getTrackLicenses( $track->track_id(), $sale->date_begin, $sale->date_end, $productTypeID, $sale->country_code ); # my $trackLicenseList = _getTrackLicenses($track->track_id(), $startDate, $endDate, $productTypeID); # Keep track of the share amount - if this exceeds 100%, that's a problem and we will # want to report that (and not pay on those licenses) # jpk - going to have to do this in a seperate loop. It's a lot simpler than trying # to remove all the stuff I've added to the productUnitMap after the fact... # my $shareSum = 0; my @licenseIDList; _report( " num track licenses : " . scalar @$trackLicenseList, 4 ); foreach my $trackLicense (@$trackLicenseList) { my $trackLicenseID = $trackLicense->track_license_id; push @licenseIDList, $trackLicenseID; my $share = $trackLicense->share; $shareSum += $share; } # !!! May want to accumulate this and output it all at once... # !!! Otherwise we'll get a lot of repeated lines. # _report( " shareSum = $shareSum", 4 ); if ( $shareSum > 100 ) { my $printableList = join( ",", @licenseIDList ); $overshareMap{$productID}{$trackID}{list} = $printableList; $overshareMap{$productID}{$trackID}{share} = $shareSum; # !!! We're going to go ahead and pay on these now. # # !!! Except for select client ids. # my $clientID = Common::RSApp::GetClientID(); if ( 31 == $clientID # sanctuary || 47 == $clientID # madacy ) { _report( " skipping sale - we don't pay mechanicals for client $clientID", 3 ); my $mi = RPS::DB::Item::SaleRunMap->Create( sale_id => $sale->sale_id, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeMechanical, status => RPS::DB::Item::SaleRunMap::kStatusClientDoesntPayOvershareMechanicals, ); $mi->save(); next; } # next; } # !!! They want an 'undershare' report, too. # # Only including US sales for now. # if ( ( $shareSum < 99 ) && ( $sale->country_code eq 'US' ) ) { my $missingShare = 100 - $shareSum; #_report(" adding entry to missing share map - product=".$productID.",track=".$trackID.",share=".$missingShare.",sale_country=".$sale->country_code); $missingShareMap{$trackID}{$productID}{$saleStatRateID}{share} = $missingShare; $missingShareMap{$trackID}{$productID}{$saleStatRateID}{units} += $units; } # !!! jpk - Can I skip track licenses that don't have the requested payor id _here_? # foreach my $trackLicense (@$trackLicenseList) { my $trackLicenseID = $trackLicense->track_license_id; _report( " +++ processing sale with track license $trackLicenseID ", 3 ); # Have we already paid out this track license in a previous lifetime? # If so, we may need to make adjustments to the controlled comp account. # # In other words, if we've added a new license since the last time we # processed this sale, and there was some $ given to the unknown publisher, # we are probably going to owe the ccomp publisher some money... # if ( $previousSaleStatementItems->{$trackLicenseID} ) { my $previousStatementItem = $previousSaleStatementItems->{$trackLicenseID}; _report( " -- detected a previous sale - looking for adjusted license with id $trackLicenseID", 3 ); if ( $adjustedTrackLicenses->{$trackLicenseID} ) { # This statement item will need adjusting. # !!! Sigh... This is going to be a wee bit irritating. Maybe I can just # handle this inline, right here? # _report( " ++ found adjusted license, adding to adjustment state", 3 ); push @{ $adjustmentState->{$trackLicenseID} }, $previousStatementItem; } # Skip this license # After all, we've already _paid_ this license holder for this sale. # The only point to this exercise is to gather some state to allow us # to calculate adjustments later... # _report( " skipping this! already paid", 3 ); next; } # Skip the license if it isn't valid. # if ( !_licenseIsValid($trackLicense) ) { next; } if ( !_productIsDigital($productID) ) { _report( "loop 1 : looking at a physical product! productID $productID units $units numSales $numSales numReturns $numReturns", 2 ); } # If the license is a penny rate, then we don't want to sort sales by stat rate id - # the stat rate id is irrelevant, and we want all sales, returns, etc. to settle into # a single bucket. # We'll do that by forcing the ids to '0'. # !!! Need to remember to do this same thing when dealing with carryover, liquidations, etc. # my $tempIssueStatRateID = $issueStatRateID; my $tempSaleStatRateID = $saleStatRateID; if ( RPS::DB::Item::TrackLicense::kRateTypePenny == $trackLicense->rate_type ) { _report( "+++ sale TrackLicense has a penny rate, setting rate ids to 0: " . Dumper($trackLicense), 3 ); # $issueStatRateID = $saleStatRateID = 0; $tempIssueStatRateID = $tempSaleStatRateID = 0; } # !!! Ok, here is where I can insert my refactoring. # Accumulate all the unit totals from the sales table. # # $productUnitMap{$trackLicenseID}{$productID}{$issueStatRateID}{$saleStatRateID}{units} += $units; $productUnitMap{$trackLicenseID}{$productID}{$tempIssueStatRateID}{$tempSaleStatRateID}{sales} += $numSales; $productUnitMap{$trackLicenseID}{$productID}{$tempIssueStatRateID}{$tempSaleStatRateID}{returns} += $numReturns; push @{ $productUnitMap{$trackLicenseID}{$productID}{$tempIssueStatRateID}{$tempSaleStatRateID}{saleIDs} }, $sale->sale_id; $paidLicenses++; } } if ( !$paidLicenses ) { # We want to report that we looked at this sale, but couldn't find any new licenses # to pay on. # my $mi = RPS::DB::Item::SaleRunMap->Create( sale_id => $sale->sale_id, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeMechanical, status => RPS::DB::Item::SaleRunMap::kStatusNoLicense, ); $mi->save(); } # Flag the sale as 'pending' - This will allow us to roll back later if necessary. # # !!! Really, why do I do this here? # !!! Seems like we ought not to bother setting any status codes until we commit. # !!! Not to mention that this sort of 'pending' status is really not that useful. $sale->mechanical_royalty_status(Raptor::DB::Item::Sale::kPending); $sale->save(); $progressBar->increment(); if ($gProgress) { $| = 1; $progressBar->display( \*STDOUT ); } } _report( " 1) productUnitMap : " . Dumper(%productUnitMap), 4 ); # Now, run through the LicenseReserve table. # There may be units waiting to be liquidated that do not correlate with any sales. # --- skip reserves that don't map to our payorIDMap # my $reserves = RPS::DB::Item::LicenseReserve->GetAllCommitted(); while ( my $reserve = $reserves->next() ) { my $trackLicenseID = $reserve->track_license_id; my $productID = $reserve->product_id; # my $issueStatRateID = $reserve->issue_stat_rate_id; my $issueStatRateID = 16; my $saleStatRateID = $reserve->sale_stat_rate_id; # assert($issueStatRateID); # assert($saleStatRateID); # Now we iterate over the reserve list, adding units that need to be liquidated. # !!! Note - I'm changing this around now - We won't be saving any changes to the # liquidation table in this script (except to add new ones). Adjusting the table # will happen in the 'commit' script. # my $totalUnits = 0; my $period = $reserve->periods_remaining; # !!! So, here's a point where we fetch the track license. # Would it be possible to just not do this at all? # What breaks if I comment this line out? # my $trackLicense = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $trackLicenseID ); # Skip the license if it isn't valid. # if ( !_licenseIsValid($trackLicense) ) { next; } # Skip this reserve if it doesn't belong to the payors we are running. # if ( !$payorIDMap->{ $trackLicense->payor_id } ) { _report( "skipping reserve " . $reserve->license_reserve_id . " - not running that payor", 3 ); next; } # If this track license is inactive, or the _publisher_ who owns it is inactive, then skip this reserve. # OR if the album the track is found on is inactive... # if ( $trackLicense->inactive() ) { _report( "skipping reserve " . $reserve->license_reserve_id . " - track license is inactive", 3 ); next; } my $pubData = _getPublisher( $trackLicense->publisher_id ); if ( $pubData && RPS::DB::Item::Publisher::kStatusInactive == $pubData->status() ) { _report( "skipping reserve " . $reserve->license_reserve_id . " - publisher is inactive", 3 ); next; } my $track = RPS::DB::Item::Track->Lookup( track_id => $trackLicense->track_id ); my $album = RPS::DB::Item::Album->Lookup( album_id => $track->album_id ); if ( $album->inactive() || $album->status() == 0 ) { _report( "skipping reserve " . $reserve->license_reserve_id . " - album is inactive", 3 ); next; } # If the license is a penny rate, then we don't want to sort sales by stat rate id - # the stat rate id is irrelevant, and we want all sales, returns, etc. to settle into # a single bucket. # We'll do that by forcing the ids to '0'. # !!! Need to remember to do this same thing when dealing with carryover, liquidations, etc. # if ( RPS::DB::Item::TrackLicense::kRateTypePenny == $trackLicense->rate_type ) { _report( "+++ reserve TrackLicense has a penny rate, setting rate ids to 0: " . Dumper($trackLicense), 3 ); $issueStatRateID = $saleStatRateID = 0; } # If period == 1, then we're liquidating now. # if ( 1 == $period ) { # Determine the number of reserved units to liquidate. # my $units = $reserve->units; $productUnitMap{$trackLicenseID}{$productID}{$issueStatRateID}{$saleStatRateID}{liquidated} += $units; } # Associate this reserve with this run, so we can increment it's periods_remaining someday. # my $runMapItem = RPS::DB::Item::LicenseReserveRun->Create( license_reserve_id => $reserve->license_reserve_id, mechanical_run_id => $gRunID, ); $runMapItem->save(); } # Do vitually the same thing with carryover units. # However, there isn't a fancy liquidation schedule - We look at # all carryovers every period. # my $carryovers = RPS::DB::Item::MechanicalCarryover->GetAll(); while ( my $carryover = $carryovers->next() ) { my $trackLicenseID = $carryover->track_license_id; my $carryoverTrackLicense = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $trackLicenseID ); my $carryoverPub = _getPublisher( $carryoverTrackLicense->publisher_id ); if ( $carryoverPub && RPS::DB::Item::Publisher::kStatusInactive == $carryoverPub->status() ) { _report( "skipping carryover " . $carryover->mechanical_carryover_id . " : publisher is inactive", 2 ); next; } # Skip this carryover if it doesn't belong to the payors we are running. # if ( !$payorIDMap->{ $carryoverTrackLicense->payor_id } ) { _report( "skipping carryover" . $carryover->mechanical_carryover_id . " - not running that payor", 2 ); next; } # If this track license is inactive, or the _publisher_ who owns it is inactive, then skip this reserve. # if ( $carryoverTrackLicense->inactive() ) { _report( "skipping carryover" . $carryover->mechanical_carryover_id . " - track license is inactive", 2 ); next; } my $track = RPS::DB::Item::Track->Lookup( track_id => $carryoverTrackLicense->track_id ); my $album = RPS::DB::Item::Album->Lookup( album_id => $track->album_id ); if ( $album->inactive() || $album->status() == 0 ) { _report( "skipping carryover " . $carryover->mechanical_carryover_id . " - album is inactive", 3 ); next; } # Skip the license if it isn't valid. # if ( !_licenseIsValid($carryoverTrackLicense) ) { next; } # We're going to liquidate this carryover this run, so make a note of that. # my $mechanicalRunCarryoverItem = RPS::DB::Item::MechanicalRunCarryover->Create( mechanical_carryover_id => $carryover->mechanical_carryover_id, mechanical_run_id => $gRunID, ); $mechanicalRunCarryoverItem->save(); my $productID = $carryover->product_id; # my $issueStatRateID = $carryover->issue_stat_rate_id; my $issueStatRateID = 16; my $saleStatRateID = $carryover->sale_stat_rate_id; my $units = $carryover->units; # If the license is a penny rate, then we don't want to sort sales by stat rate id - # the stat rate id is irrelevant, and we want all sales, returns, etc. to settle into # a single bucket. # We'll do that by forcing the ids to '0'. # !!! Need to remember to do this same thing when dealing with carryover, liquidations, etc. # if ( RPS::DB::Item::TrackLicense::kRateTypePenny == $carryoverTrackLicense->rate_type ) { _report( "+++ carryoverTrackLicense has a penny rate, setting rate ids to 0: " . Dumper($carryoverTrackLicense), 3 ); $issueStatRateID = $saleStatRateID = 0; } # We could now have multiple carryover entries that hash to the same slot. # Hence the +=... # $productUnitMap{$trackLicenseID}{$productID}{$issueStatRateID}{$saleStatRateID}{carryover} += $units; } # (XXX) I think this is the correct place to deal with accrued units. # Replaces the _getReturns call. # Instead, I will need to organize the productUnitMap by publisher_id/track_id # Then find all the accrued units $progressBar = Common::TextProgressBar->new( scalar keys %productUnitMap, kSaleProgressQuanta ); if ($gProgress) { $| = 1; $progressBar->display( \*STDOUT ); } # Now, we have all the units, sales, and returns (as told to us by the sales table) accumulated. # This means that we're going to now be assured of visiting each potential statement item once. # _report( "productUnitMap: " . Dumper( \%productUnitMap ), 2 ); foreach my $trackLicenseID ( keys %productUnitMap ) { my $trackLicense = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $trackLicenseID ); # Are we really dealing with this license's payor this time around? # my $payorID = $trackLicense->payor_id; if ( !$payorIDMap->{$payorID} ) { _report( " -- skipping track_license $trackLicenseID - its payor_id of $payorID is not on our list", 2 ); next; } # If the track license is inactive, skip it # if ( $trackLicense->inactive() ) { _report( " -- skipping carryover for track_license $trackLicenseID - inactive", 2 ); next; } my $productHash = $productUnitMap{$trackLicenseID}; foreach my $productID ( keys %$productHash ) { my $product = _getProduct($productID); if ( !_productIsDigital($productID) ) { _report( "loop 2 : looking at a physical product! productID $productID", 2 ); } my $issueStatRateHash = $productHash->{$productID}; foreach my $issueStatRateID ( keys %$issueStatRateHash ) { my $saleStatRateHash = $issueStatRateHash->{$issueStatRateID}; foreach my $saleStatRateID ( keys %$saleStatRateHash ) { my $record = $productUnitMap{$trackLicenseID}{$productID}{$issueStatRateID}{$saleStatRateID}; my $numSales = $record->{sales}; my $numReturns = $record->{returns}; my $saleIDs = $record->{saleIDs}; my $liquidated = $record->{liquidated}; my $previousCarryover = $record->{carryover}; my $trackID = $trackLicense->track_id; my $track = _getTrack($trackID); # We'll need the track duration for stat rate calulcations. # my $masterID = $track->master_id; die "!!! Track has no master, cannot determine duration" unless $masterID; my $masterData = _getMaster($masterID); my $duration = $masterData->duration; _report( " trackLicenseID: $trackLicenseID", 3 ); # Skip this license if it's the controlled composition clause. # (But first, we'll stash the controlled comp away for later perusal) # if ( RPS::DB::Item::TrackLicense::kControlledComposition eq $trackLicense->type ) { my $albumID = $track->album_id; if ($albumID) { # !!! # We are going to need the issue stat rate later. # We don't store this in the controlled comp table. # In theory, I ought to be able to use the value we have right # now - If our data is good, then the release date for all the # tracks on a given album should be the same.... # my $ccID = _getControlledCompIDFromAlbumID($albumID); if ( !$ccID ) { _report( "!!! ERROR !!! trackLicense $trackLicenseID is type controlled composition, but controlled_composition_id is invalid! SKIPPING" ); next; } my $share = $trackLicense->share; _report( " license is controlled comp - share = $share", 3 ); my %newRecord; $controlledCompState->{$albumID}{$ccID}{$trackLicenseID}{$productID}{$issueStatRateID}{$saleStatRateID} {numSales} = $numSales; $controlledCompState->{$albumID}{$ccID}{$trackLicenseID}{$productID}{$issueStatRateID}{$saleStatRateID} {numReturns} = $numReturns; $controlledCompState->{$albumID}{$ccID}{$trackLicenseID}{$productID}{$issueStatRateID}{$saleStatRateID}{saleIDs} = $saleIDs; $controlledCompState->{$albumID}{$ccID}{$trackLicenseID}{$productID}{$issueStatRateID}{$saleStatRateID} {liquidated} = $liquidated; $controlledCompState->{$albumID}{$ccID}{$trackLicenseID}{$productID}{$issueStatRateID}{$saleStatRateID} {carryover} = $previousCarryover; } else { croak "!!! We have a controlled comp on a track with no album?"; } next; } # If this is a public domain share, make a note of the share, then skip. # if ( RPS::DB::Item::TrackLicense::kPublicDomain eq $trackLicense->type ) { my $share = $trackLicense->share; _report( " license is public domain - share = $share", 3 ); next; } my $publisherID = _getCorrectPublisherID($trackLicense); _report( " calculating royalties for trackLicense " . $trackLicense->track_license_id, 2 ); _report( " license: " . Dumper($trackLicense), 3 ); my $share = $trackLicense->share; _report( " share % = $share", 2 ); # Calculate the effective units to pay on. # # my $grossUnits = $units; # my $grossUnits = $numSales - $numReturns; my $effectiveSales = $numSales; # Calculate the effective rate # my ( $effectiveRate, $baseRate, $appliedStatRateID ) = _calculateEffectiveRate( $issueStatRateID, $saleStatRateID, $trackLicense->rate_basis, $trackLicense->rate_type, $trackLicense->penny_rate, $trackLicense->rate_percentage, $duration, $share, $trackLicense->lock_date ); _report( " effectiveRate = $effectiveRate", 2 ); # If there is a reserve specified, take it now (after other deductions) # # !!! However, if we're looking at _negative_ units, we can't take reserves from that! # my $amountReserved = 0; my $unitsAfterReserve; if ( $effectiveSales > 0 && !_productIsDigital($productID) ) { $unitsAfterReserve = _applyDeduction( $effectiveSales, $trackLicense->reserve_percentage ); if ( $unitsAfterReserve < $effectiveSales ) { $amountReserved = $effectiveSales - $unitsAfterReserve; $effectiveSales = $unitsAfterReserve; } _report( " effectiveSales after reserve % of " . $trackLicense->reserve_percentage . " : $effectiveSales", 2 ); } else { _report( " effective sales is $effectiveSales - no reserves taken", 2 ); } # Now we can apply the liquidated units. # $effectiveSales += $liquidated; _report( " after applying liquidated units of $liquidated, effectiveSales = $effectiveSales", 2 ); if ( !$gClient->use_net_for_mechanical_deductions && $effectiveSales > 0 ) { _report( " NOT using net for mechanical deductions. effectiveSales before deductions: $effectiveSales", 2 ); $effectiveSales = _standardDeductions( $effectiveSales, $trackLicense ); _report( " after deductions: $effectiveSales", 2 ); } my $effectiveUnits = $effectiveSales - $numReturns; _report( " effectiveUnits = sale $effectiveSales - returns $numReturns = $effectiveUnits", 2 ); # See if there were any returned units held over from a previous run. # (XXX) Going to just add these to the 'returns' column now. # # --- In the new cross-collateralization scheme, previousReturns # --- will equate to the 'carryover' column. # if ($previousCarryover) { $effectiveUnits += $previousCarryover; _report( " after applying previousCarryover of $previousCarryover, effectiveUnits = $effectiveUnits", 2 ); } # !!! This seems totally wrong to me. # !!! AND, nobody has that flag set anymore anyway... # if ( $gClient->use_net_for_mechanical_deductions && $effectiveUnits > 0 ) { _report( " using net for mechanical deductions. effectiveUnits before deductions: $effectiveUnits", 2 ); $effectiveUnits = _standardDeductions( $effectiveUnits, $trackLicense ); _report( " after deductions: $effectiveUnits", 2 ); } my $grossUnits = $effectiveUnits; my $totalOwed; my $amountPaid; my $returnsReserved; # Ok - back to where we were before. # If the total owed is going to be less than 0, make it 0. # We don't want to have negative dollar amounts. # However, we are still going to to keep track of these negative # units, which will be accounted for in later statements. # $totalOwed = Common::RSMath::round( $effectiveUnits * $effectiveRate, 2 ); $amountPaid = $totalOwed; # (XXX) DISABLING CARRYOVER # if ($amountPaid < 0) # { # $amountPaid = 0; # $effectiveUnits = 0; # } _report( " totalOwed: $totalOwed amountPaid: $amountPaid", 2 ); # (XXX) This is going to be the new carryover balance. # my $newCarryover = 0; # (XXX) DISABLING CARRYOVER # if ($grossUnits < 0) # { # $newCarryover = $grossUnits; # } # Fetch the Statement. # !!! _this_ needs to take payor_id # my $statement = _getStatement( $payorID, $publisherID, $startDate, $endDate ); # Create a statement item if one doesn't exist yet. # my $statementID = $statement->mechanical_statement_id; my $statementItem = _getStatementItem( $statementID, $productID, $issueStatRateID, $saleStatRateID, $trackLicense->track_license_id, $saleIDs ); # Determine the HFA reporting period value. # my $hfaReportingPeriod = _calculateHFAReportingPeriod( $startDate, $endDate, $issueStatRateID, $saleStatRateID, $trackLicense ); assert( $effectiveRate > 0 ); # Update the statement item # $statementItem->gross_units($grossUnits); $statementItem->net_units($effectiveUnits); $statementItem->net_rate($effectiveRate); $statementItem->amount_paid($amountPaid); $statementItem->total($totalOwed); $statementItem->reserved($amountReserved); $statementItem->hfa_reporting_period($hfaReportingPeriod); $statementItem->applied_stat_rate_id($appliedStatRateID); $statementItem->base_rate($baseRate); $statementItem->sales($numSales); $statementItem->returns($numReturns); $statementItem->carryover($newCarryover); $statementItem->liquidated($liquidated); $statementItem->previous_carryover($previousCarryover); $statementItem->save(); _report( " created statement item\n", 2 ); # Save the reserved units someplace. # if ($amountReserved) { my $statementItemID = $statementItem->mechanical_statement_item_id; # Need to create all the reserve buckets at once. # my %scheduleTable; my $schedule = RPS::DB::Item::ReserveLiquidation->GetByIDType( $trackLicenseID, RPS::DB::Item::ReserveLiquidation::kTypePublishingLicense ); while ( $schedule->hasNext() ) { my $scheduleEntry = $schedule->next(); $scheduleTable{ $scheduleEntry->period } = $scheduleEntry->percent; } my $unitsReserved = $amountReserved; my $initialUnits = $unitsReserved; for ( my $i = 1 ; $unitsReserved > 0 && $i <= 8 ; $i++ ) { my $p = $scheduleTable{$i}; next unless $p; my $periodUnits = ceil( $initialUnits * ( $p / 100 ) ); $periodUnits = $unitsReserved unless $periodUnits < $unitsReserved; $unitsReserved -= $periodUnits; my $newReserve = RPS::DB::Item::LicenseReserve->Create( track_license_id => $trackLicenseID, original_statement_item_id => $statementItemID, product_id => $productID, sale_stat_rate_id => $saleStatRateID, issue_stat_rate_id => $issueStatRateID, units => $periodUnits, effective_rate => $effectiveRate, periods_remaining => $i + 1, ); $newReserve->save(); # Associate this reserve with this run, so we can increment it's next period someday. # my $runMapItem = RPS::DB::Item::LicenseReserveRun->Create( license_reserve_id => $newReserve->license_reserve_id, mechanical_run_id => $gRunID, ); $runMapItem->save(); } } # JPK - I suppose this is the correct place to make note of the return balance? # - No - Let's do that at another time, when we commit the statements. # if ($newReturnBalance || $previousReturns) # { # _saveReturns($productID, $trackLicenseID, $newReturnBalance); # } } } } $progressBar->increment(); if ($gProgress) { $| = 1; $progressBar->display( \*STDOUT ); } } # Grab all the distinct track licenses that have pending transactions. # We want to make sure we generate statements for these, even if there are no sales! # # We may end up with publishers that only have pending transactions, but no sales, reserves, adjustments, etc. # This means that they won't have any statement items by the time we get to the finalizeStatements function, # which means that we won't create a decent statement for them. # As a hack to get around that, I will keep track of the publisher/sub-publishers that have pending transactions # to make sure they get processed. # my $licensesWithPendingTransactions = RPS::DB::Item::TrackLicense->GetLicensesWithPendingTransactions(); while ( my $tl = $licensesWithPendingTransactions->next() ) { # !!! We don't want to process transactions for inactive licenses... # next if ( $tl->inactive() ); my $payorID = $tl->payor_id; next unless ( $payorIDMap->{$payorID} ); # !!! This _might_ be enough - just force this publisher to have a statement. # my $publisherID = _getCorrectPublisherID($tl); _report( "Forcing a statement for publisher $publisherID because it has pending transactions", 2 ); my $statement = _getStatement( $payorID, $publisherID, $startDate, $endDate ); $gPendingTransPubMap{$payorID}{ $tl->publisher_id }{$publisherID} = 1; } # We need to do essentially the same thing for publishers that have publisher-level pending transactions, but # nothing else. # my $pubAccounts = RPS::DB::Item::PublisherAccount->GetAllWithPendingTransactions(); while ( my $pa = $pubAccounts->next() ) { my $payorID = $pa->payor_id; next unless ( $payorIDMap->{$payorID} ); my $paPublisher = $pa->publisher_id; _report( "fetching statement for publisher with pending transactions", 4 ); my $statement = _getStatement( $payorID, $paPublisher, $startDate, $endDate ); # !!! This is the part I don't understand. Why do I do this? # !!! sub-publishers are not relevant. # !!! All I should _have_ to do is get the statement... # # $gPendingTransPubMap{$payorID}{$paPublisher}{$paPublisher} = 1; } # Output the report of overages. # foreach my $overageProductID ( keys %overshareMap ) { my $trackHash = $overshareMap{$overageProductID}; foreach my $overageTrackID ( keys %$trackHash ) { my $printableList = $trackHash->{$overageTrackID}{list}; my $share = $trackHash->{$overageTrackID}{share}; _report( "SHARE OVERAGE: product_id:$overageProductID track_id:$overageTrackID shares: $share license_id list:$printableList"); } } #_report("SKIPPING THE LICENSE ACCRUAL REPORT"); #return; # Create the missing license accrual report # _report( "DEBUG: MISSING SHARE MAP:" . Dumper( \%missingShareMap ) ); #return; foreach my $trackID ( keys %missingShareMap ) { my $track = _getTrack($trackID); if ( defined($track) ) { my $album = _getAlbum( $track->album_id ); my $label = _getLabel( $album->label_id ); my $master = _getMaster( $track->master_id ); my $trackArtist = _getArtist( $track->artist_id ); my $duration = $master->duration; # !!! Can't assume that there _is_ a track artist, oddly. # my $trackArtistName = ''; if ( defined($trackArtist) ) { $trackArtistName = $trackArtist->name; } my $totalOwed; my $totalShare; my $productIDHash = $missingShareMap{$trackID}; foreach my $productID ( keys %$productIDHash ) { my ( $upc, $isrc ) = _getUPCAndISRCFromProductID($productID); my $product = _getProduct($productID); my $productTypeID; my $productType; if ($product) { $productTypeID = $product->product_type_id; $productType = _getProductType($productTypeID); $productType = $productType->description; } my $saleStatRateIDHash = $missingShareMap{$trackID}{$productID}; foreach my $saleStatRateID ( keys %$saleStatRateIDHash ) { my $data = $saleStatRateIDHash->{$saleStatRateID}; my $units = $data->{units}; my $share = $data->{share}; my ($rate) = _calculateEffectiveRate( 16, $saleStatRateID, RPS::DB::Item::TrackLicense::kRateBasisSale, RPS::DB::Item::TrackLicense::kRateTypeFull, 0, 100, $duration, $share, 0 ); _report("DEBUG: track $trackID stat rate $saleStatRateID share $share UNITS: $units Rate returned: $rate"); $totalOwed += Common::RSMath::round( $units * $rate, 2 ); # Total share should not actually CHANGE # $totalShare = $share; } my $log = RPS::DB::Item::MechanicalRunUnlicensedTrackLog->Create( mechanical_run_id => $gRunID, label_id => $album->label_id, label_name => $label->label_name, catalog_number => $album->catalog_number, track_id => $track->track_id, album_id => $album->album_id, album_title => $album->title, #track_order => $track->track_order, track_artist_name => $trackArtistName, track_title => $track->title, #track_length => $duration, isrc => $isrc, upc_ean => $upc, product_type => $productType, unlicensed_share => $totalShare, accrual => $totalOwed ); $log->save(); } } } } sub _processControlledComp { my ( $controlledCompState, $startDate, $endDate ) = @_; my $progressBar = Common::TextProgressBar->new( scalar keys %$controlledCompState, kSaleProgressQuanta ); if ($gProgress) { $| = 1; $progressBar->display( \*STDOUT ); } foreach my $albumID ( keys %$controlledCompState ) { _report( "Processing controlled comps for album $albumID", 2 ); my $ccIDHash = $controlledCompState->{$albumID}; foreach my $ccID ( keys %$ccIDHash ) { _report( " Processing controlled comp $ccID", 2 ); # (XXX) Where are these ids coming from? # my $controlledComp = RPS::DB::Item::ControlledComposition->Lookup( controlled_composition_id => $ccID ); my $trackLicenseIDHash = $ccIDHash->{$ccID}; foreach my $trackLicenseID ( keys %$trackLicenseIDHash ) { # Get the track license data, and then get the %share and publisher id from that. # my $trackLicense = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $trackLicenseID ); my $share = $trackLicense->share; my $publisherID = $trackLicense->publisher_id; my $payorID = $trackLicense->payor_id; _report( " publisher $publisherID", 2 ); # If this track_license is not _direct_, we need to report this # through an agent or admin. # if ( !$trackLicense->publisher_direct ) { my $publisherData = _getPublisher($publisherID); # !!! This may be redundant, but better safe than sorry. # if ( $publisherData && RPS::DB::Item::Publisher::kStatusInactive == $publisherData->status() ) { _report( " this publisher is inactive, skipping", 3 ); next; } my $agentID = $publisherData->agent_id; my $adminID = $publisherData->admin_id; if ($agentID) { $publisherID = $agentID; _report( " reporting controlled comp on agent $publisherID statement", 2 ); } elsif ($adminID) { $publisherID = $adminID; _report( " reporting controlled comp on admin $publisherID statement", 2 ); } # JPK - Note that if the agentID was not set, then we essentially # ignore the publisherDirect flag (and treat it automatically as publisherDirect) # } my $productIDHash = $trackLicenseIDHash->{$trackLicenseID}; foreach my $productID ( keys %$productIDHash ) { my $issueRateHash = $productIDHash->{$productID}; foreach my $issueStatRateID ( keys %$issueRateHash ) { my $saleRateHash = $issueRateHash->{$issueStatRateID}; foreach my $saleStatRateID ( keys %$saleRateHash ) { _report( " productID: $productID issueStatRate: $issueStatRateID saleStatRate: $saleStatRateID", 2 ); # calculate effective album rate. # my $baseRate = _calculateEffectivePerTrackRate( $albumID, $issueStatRateID, $saleStatRateID, $controlledComp, $trackLicense ); _report( " effective per track rate: $baseRate", 2 ); # now calculate actual amount, and save as a statement item # next unless $share > 0; # my $units = $saleRateHash->{$saleStatRateID}{units}; my $numSales = $saleRateHash->{$saleStatRateID}{numSales}; my $numReturns = $saleRateHash->{$saleStatRateID}{numReturns}; my $units = $numSales - $numReturns; my $saleIDs = $saleRateHash->{$saleStatRateID}{saleIDs}; my $liquidated = $saleRateHash->{$saleStatRateID}{liquidated}; my $previousCarryover = $saleRateHash->{$saleStatRateID}{carryover}; my $netSales = $numSales; # Take the reserve percentage into account. # If a reserve percentage was specified, we'll have to 'set aside' # units to be payed out later. # !!! Don't take reserves on digital sales... # my $amountReserved = 0; if ( !_productIsDigital($productID) && $netSales > 0 ) { my $unitsAfterReserve = _applyDeduction( $netSales, $controlledComp->reserve_percentage ); if ( $unitsAfterReserve < $netSales ) { $amountReserved = $netSales - $unitsAfterReserve; $netSales = $unitsAfterReserve; } _report( " effective sales after ccomp reserve % of " . $controlledComp->reserve_percentage . " : $netSales", 2 ); } my $netUnits = $netSales - $numReturns; # See if there were any units held over from a previous run. # if ($previousCarryover) { $netUnits += $previousCarryover; } my $effectiveRate = _roundRate( $baseRate * ( $share / 100 ) ); # !!! If the rate is < 0, then we'll set the effective rate to 0 # if ( $effectiveRate < 0 ) { _report( " ======= rate is < 0: album_id $albumID track_license_id $trackLicenseID", 2 ); $effectiveRate = 0; } # Any reserves to be liquidated? # $netUnits += $liquidated; my $grossUnits = $netUnits; my $total = Common::RSMath::round( $netUnits * $effectiveRate, 2 ); my $amountPaid = $total; # (XXX) DISABLING CARRYOVER # if ($total < 0) # { # $amountPaid = 0; # $netUnits = 0; # } # !!! I don't see any accounting for the possibility of _negative_ rate, therefore carryover of positive units !!! my $newCarryover = 0; # (XXX) DISABLING CARRYOVER # if ($grossUnits < 0) # { # $newCarryover = $grossUnits; # } _report( " gross units: $grossUnits netUnits: $netUnits share: $share new carryover: $newCarryover total: $total paid: $amountPaid", 2 ); my $hfaReportingPeriod = _calculateHFAReportingPeriod( $startDate, $endDate, $issueStatRateID, $saleStatRateID, $trackLicense ); # Create statement_item # my $statement = _getStatement( $payorID, $publisherID, $startDate, $endDate ); my $statementItem = _getStatementItem( $statement->mechanical_statement_id, $productID, $issueStatRateID, $saleStatRateID, $trackLicenseID, $saleIDs ); _report( " +++++ creating statement item for CComp trackLicenseID = $trackLicenseID", 3 ); # assert($effectiveRate > 0); # Update the statement item # $statementItem->net_units($netUnits); $statementItem->gross_units($grossUnits); $statementItem->net_rate($effectiveRate); # !!! Might be good to sanity-check this. $statementItem->total($total); $statementItem->amount_paid($amountPaid); $statementItem->base_rate($baseRate); # jpk - not sure this really means anything $statementItem->reserved($amountReserved); $statementItem->sales($numSales); $statementItem->returns($numReturns); $statementItem->carryover($newCarryover); $statementItem->liquidated($liquidated); $statementItem->previous_carryover($previousCarryover); $statementItem->hfa_reporting_period($hfaReportingPeriod); $statementItem->save(); # Keep track of any reserved units. # if ($amountReserved) { my $statementItemID = $statementItem->mechanical_statement_item_id; # Need to create all the reserve buckets at once. # my %scheduleTable; my $schedule = RPS::DB::Item::ReserveLiquidation->GetByIDType( $ccID, RPS::DB::Item::ReserveLiquidation::kTypeControlledComposition ); while ( $schedule->hasNext() ) { my $scheduleEntry = $schedule->next(); $scheduleTable{ $scheduleEntry->period } = $scheduleEntry->percent; } my $unitsReserved = $amountReserved; my $initialUnits = $unitsReserved; for ( my $i = 1 ; $unitsReserved > 0 && $i <= 8 ; $i++ ) { my $p = $scheduleTable{$i}; next unless $p; my $periodUnits = ceil( $initialUnits * ( $p / 100 ) ); $periodUnits = $unitsReserved unless $periodUnits < $unitsReserved; $unitsReserved -= $periodUnits; my $newReserve = RPS::DB::Item::LicenseReserve->Create( track_license_id => $trackLicenseID, original_statement_item_id => $statementItemID, product_id => $productID, sale_stat_rate_id => $saleStatRateID, issue_stat_rate_id => $issueStatRateID, units => $periodUnits, effective_rate => $effectiveRate, periods_remaining => $i + 1, ); $newReserve->save(); # Associate this reserve with this run, so we can increment it's next period someday. # my $runMapItem = RPS::DB::Item::LicenseReserveRun->Create( license_reserve_id => $newReserve->license_reserve_id, mechanical_run_id => $gRunID, ); $runMapItem->save(); } } # JPK - I suppose this is the correct place to make note of the return balance? # # if ($newReturnBalance || $previousReturns) # { # _saveReturns($productID, $trackLicenseID, $newReturnBalance); # } } } } } } $progressBar->increment(); if ($gProgress) { $| = 1; $progressBar->display( \*STDOUT ); } } } sub _finalizeStatements { my ( $startDate, $endDate ) = @_; _report( "Processing Track Subtotals", 2 ); # Gather up the Statements that were just generated. # (This is kind of a hack, using a global like this...) # foreach my $payorID ( keys %$gPublisherStatementMap ) { my $publisherIDStatementHash = $gPublisherStatementMap->{$payorID}; foreach my $publisherID ( keys %$publisherIDStatementHash ) { _report( " Publisher id $publisherID", 2 ); my $publisherData = _getPublisher($publisherID); if ( $publisherData && RPS::DB::Item::Publisher::kStatusInactive == $publisherData->status() ) { _report( " This publisher is inactive, skipping", 3 ); next; } # !!! This is probably redundant, but skip inactive publishers # my $publisherSubtotal = 0; my $digitalSubtotal = 0; my $physicalSubtotal = 0; my $statement = $publisherIDStatementHash->{$publisherID}; my $statementID = $statement->mechanical_statement_id; # So... Need to make some more changes to account for the multi-publisher # statements. # Going to refer to 'subPublishers' to handle that. # Need to determine all the publishers who are reporting through a statement. # my @subPublishers = RPS::DB::Item::MechanicalStatement->GetPublishers($statementID); # !!! If we are here because of pending transactions, but no sales, then there won't be # !!! any publishers in the 'subPublishers' array. Which is annoying. # # So, we'll use this hash table hack to make sure they get looked at. # # $gPendingTransPubMap{$payorID}{$tl->publisher_id}{$publisherID} = 1; if ( $gPendingTransPubMap{$payorID}{$publisherID} ) { foreach my $pendingSubPubID ( keys %{ $gPendingTransPubMap{$payorID}{$publisherID} } ) { my $addFlag = 1; foreach my $testSub (@subPublishers) { if ( $testSub == $pendingSubPubID ) { $addFlag = 0; last; } } if ($addFlag) { _report( "***** adding publisher $pendingSubPubID to the subPublishers array", 2 ); push @subPublishers, $pendingSubPubID; } } } foreach my $subPublisherID (@subPublishers) { my %trackLicenseHash; my $subPublisherSubtotal; # See if there is a crossed-license account balance. # If there is, we want to make sure that gets processed even if there # are no active licenses on this track... # my $allCrossedAccountMaps = RPS::DB::Item::PublisherTrackCrossedLicenseAccount->GetForPublisherPayor( $subPublisherID, $payorID ); while ( my $map = $allCrossedAccountMaps->next() ) { $trackLicenseHash{ $map->track_id } = {}; } # Gather up the line items on this statement, and accumulate the totals by license. # (XXX) I am going to have to do this _twice_ for the cross-collateralization. # (XXX) Because, I need to make adjustments to all the statement items associated # (XXX) with a track _before_ I sum everything up... # _report( " Accumulating totals", 2 ); my $statementItems = RPS::DB::Item::MechanicalStatementItem->GetByMechanicalStatementIDPublisherID( $statementID, $subPublisherID ); while ( $statementItems->hasNext() ) { my $statementItem = $statementItems->next(); # Fetch the license if we haven't already. # my $trackLicenseID = $statementItem->track_license_id; my $trackLicense = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $trackLicenseID ); my $trackID = $trackLicense->track_id; if ( !defined $trackLicenseHash{$trackID}{$trackLicenseID} ) { $trackLicenseHash{$trackID}{$trackLicenseID}{license} = $trackLicense; } # We need to report the subtotals of physical and digital revenue seperately. # (XXX) amount_paid, or total? I believe it's amount_paid, but I could be wrong... # if ( _productIsDigital( $statementItem->product_id ) ) { $digitalSubtotal += Common::RSMath::round( $statementItem->amount_paid, 2 ); } else { $physicalSubtotal += Common::RSMath::round( $statementItem->amount_paid, 2 ); } # Fetch the total that appears on this line item. # $trackLicenseHash{$trackID}{$trackLicenseID}{amount} += Common::RSMath::round( $statementItem->amount_paid, 2 ); _report( " for trackLicenseHash $trackID $trackLicenseID : amount = " . $statementItem->amount_paid . " , current total = " . $trackLicenseHash{$trackID}{$trackLicenseID}{amount}, 2 ); } # Now grab all the mechanical statement adjustments, so we can include their amounts in our subtotals. # _report( " Applying adjustments", 2 ); my $adjustmentItems = RPS::DB::Item::MechanicalStatementAdjustmentItem->GetByMechanicalStatementIDPublisherID( $statementID, $subPublisherID ); _report( " number of adjustment items: " . $adjustmentItems->size(), 2 ); while ( $adjustmentItems->hasNext() ) { my $adjustmentItem = $adjustmentItems->next(); my $trackLicenseID = $adjustmentItem->track_license_id; my $trackLicense = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $trackLicenseID ); my $trackID = $trackLicense->track_id; _report( " trackLicenseID : $trackLicenseID trackID : $trackID", 3 ); if ( !defined $trackLicenseHash{$trackID}{$trackLicenseID} ) { $trackLicenseHash{$trackID}{$trackLicenseID}{license} = $trackLicense; } # Increment the total owed on this license. # _report( " adding " . $adjustmentItem->amount . " to licenseHash amount", 2 ); $trackLicenseHash{$trackID}{$trackLicenseID}{amount} += Common::RSMath::round( $adjustmentItem->amount, 2 ); } # We might be here because this publisher had a track license with a pending transaction, # but no sales. # So, if that's the case, we'll need to 'seed' the trackLicenseHash # # $payorID, $subPublisherID my $tlsWithPending = RPS::DB::Item::TrackLicense->GetLicensesWithPendingTransactions(); while ( my $tl = $tlsWithPending->next() ) { next if ( $tl->inactive() ); # !!! Yes, this is gross and inefficient... # if ( $tl->payor_id == $payorID && $tl->publisher_id == $subPublisherID ) # && $tl->publisher_id == $publisherID) { if ( !defined $trackLicenseHash{ $tl->track_id }{ $tl->track_license_id } ) { _report( "++++++ adding track " . $tl->track_id . " : license " . $tl->track_license_id . " to hash to catch pending transactions", 2 ); $trackLicenseHash{ $tl->track_id }{ $tl->track_license_id }{license} = $tl; } } } # Now we can iterate over all the affected licenses, and process totals and advances for each. # _report( " Processing totals", 2 ); foreach my $trackID ( keys %trackLicenseHash ) { my $trackTotal = 0; my $licenseHash = $trackLicenseHash{$trackID}; my $crossedTransactionSubtotal; my $crossedSubtotal; my $crossedPreviousAdvanceBalance; my $crossedAppliedToAdvance; my $crossedAdvanceBalance; my $crossedAdjustedSubtotal; my $crossedTotal; _report( " track $trackID", 2 ); foreach my $trackLicenseID ( keys %$licenseHash ) { my $license = $licenseHash->{$trackLicenseID}{license}; next if ( $license->inactive() ); my $subtotal = Common::RSMath::round( $licenseHash->{$trackLicenseID}{amount}, 2 ); my $total = 0; my $previousAdvanceBalance = 0; my $pendingTransactionSubtotal = 0; my $advanceBalance = 0; my $appliedToAdvance = 0; my $isCrossed = ( $license ? $license->cross_collateralized : 0 ); _report( " subtotal for trackLicenseID $trackLicenseID = $subtotal", 2 ); # Create and save the license item. We'll be filling it in with more # data later, but I (might) need the new ID in the meanwhile... # my $newLine = RPS::DB::Item::MechanicalStatementLicense->Create( mechanical_statement_id => $statementID, track_license_id => $trackLicenseID, ); $newLine->save(); my $newLicenseItemID = $newLine->mechanical_statement_license_id; if ( !$license ) { # Nothing fancy with controlled comp (or unknown publisher) - The total is the subtotal. # $total = $subtotal; _report( " controlled comp or unknown publisher : total = $total", 2 ); } else { # Fetch the initial advance amount from the license, then try and fetch # the license's advance account. If we haven't set up that account yet, # then the advance amount will just be the initial advance. # (We won't actually create the account or post any transactions yet - That # will happen at some future date when the statement has been accepted.) # my $accountID = $license->finance_account_id(); my $pendingTransactions; $previousAdvanceBalance = 0; if ($accountID) { # Get the current advance account balance. # $previousAdvanceBalance = RPS::DB::Item::FinanceAccount->CurrentBalance($accountID); $pendingTransactions = RPS::DB::Item::PendingTransaction->GetAccountTransactions( finance_account_id => $accountID ); _report( " advance account current balance = $previousAdvanceBalance", 2 ); } else { _report( " no advance account established . advance amount = $previousAdvanceBalance", 2 ); } # Does this account have pending transactions? If so, slurp them up. # if ( $pendingTransactions && $pendingTransactions->size() > 0 ) { _report( "++++++ we have pending transactions", 2 ); while ( my $pendingTrans = $pendingTransactions->next() ) { $pendingTransactionSubtotal += $pendingTrans->amount; my $licenseTransItem = RPS::DB::Item::MechanicalStatementLicenseTransaction->Create( 'mechanical_statement_license_id' => $newLicenseItemID, 'amount' => $pendingTrans->amount, 'memo' => $pendingTrans->memo, 'check_number' => $pendingTrans->check_number, 'pending_transaction_id' => $pendingTrans->pending_transaction_id, 'transaction_date' => $pendingTrans->transaction_date, 'type_code' => $pendingTrans->type_code, ); $licenseTransItem->save(); } } # !!! Need to modify this logic. This account is no longer just the 'advance'. # !!! It's whatever the running total might happen to be. # !!! So... we're going to have to touch this again when we re-do the # !!! carryover code. # # subtotal - amount due before account factored in. # my $adjustedSubtotal = $subtotal + $previousAdvanceBalance + $pendingTransactionSubtotal; # Here we diverge for crossed licenses. # if ($isCrossed) { # For crossed licenses, we don't carry the balance forward at the license level. # Instead that will pour into the crossed bucket. # But we do need to 'zero out' any pre-existing balance. # $appliedToAdvance = ( -1 * $previousAdvanceBalance ); $advanceBalance = 0; $total = $adjustedSubtotal; $crossedSubtotal += $total; } else { # If the adjusted subtotal <=0, then we are not going to be paying anything # this time around. We'll instead be carrying forward that amount as a balance. # if ( $adjustedSubtotal > 0 ) { $advanceBalance = 0; $total = $adjustedSubtotal; } else { $advanceBalance += $adjustedSubtotal; $total = 0; } $appliedToAdvance = $advanceBalance - $previousAdvanceBalance; _report( " prev advance: $previousAdvanceBalance - new advance balance: $advanceBalance subtotal: $subtotal : adjusted subtotal $adjustedSubtotal total $total", 2 ); } } # Save the results of all this calculating... # $newLine->previous_advance_balance($previousAdvanceBalance); $newLine->advance_balance($advanceBalance); $newLine->applied_to_advance($appliedToAdvance); $newLine->transaction_subtotal($pendingTransactionSubtotal); $newLine->adjusted_subtotal($total); $newLine->subtotal($subtotal); $newLine->crossed($isCrossed); # Get the region id from track license # !!! We may want to expand the stuff that we store in the mechanical_statement_license, # !!! to avoid backreferencing the track license when we render the statements. # _report("!!!!!!!!! about to fetch trackLicenseID $trackLicenseID"); my $trackLicense = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $trackLicenseID ); if ( $trackLicense->region_id ) { _report( "!!!!!!!!! setting region_id to " . $trackLicense->region_id ); $newLine->region_id( $trackLicense->region_id ); } $newLine->save(); # We'll account for the crossed money later. # if ( !$isCrossed ) { $trackTotal += $total; } _report( " total $total trackTotal $trackTotal", 2 ); } # Now see if there is a crossed balance account. # my $crossedAccountMap = RPS::DB::Item::PublisherTrackCrossedLicenseAccount->Lookup( publisher_id => $subPublisherID, track_id => $trackID, payor_id => $payorID, ); if ($crossedAccountMap) { my $accountID = $crossedAccountMap->finance_account_id; my $pendingTransactions; $crossedPreviousAdvanceBalance = 0; if ($accountID) { # Get the current advance account balance. # $crossedPreviousAdvanceBalance = RPS::DB::Item::FinanceAccount->CurrentBalance($accountID); $pendingTransactions = RPS::DB::Item::PendingTransaction->GetAccountTransactions( finance_account_id => $accountID ); } # Does this account have pending transactions? If so, slurp them up. # if ( $pendingTransactions && $pendingTransactions->size() > 0 ) { while ( my $pendingTrans = $pendingTransactions->next() ) { $crossedTransactionSubtotal += $pendingTrans->amount; my $licenseTransItem = RPS::DB::Item::MechanicalStatementCrossedLicenseTransaction->Create( # 'mechanical_statement_license_id' => $newLicenseItemID, 'mechanical_statement_id' => $statementID, 'publisher_id' => $subPublisherID, 'track_id' => $trackID, 'amount' => $pendingTrans->amount, 'memo' => $pendingTrans->memo, 'check_number' => $pendingTrans->check_number, 'pending_transaction_id' => $pendingTrans->pending_transaction_id, 'transaction_date' => $pendingTrans->transaction_date, 'type_code' => $pendingTrans->type_code, ); $licenseTransItem->save(); } } } $crossedAdjustedSubtotal = $crossedSubtotal + $crossedPreviousAdvanceBalance + $crossedTransactionSubtotal; if ( $crossedAdjustedSubtotal > 0 ) { $crossedAdvanceBalance = 0; $crossedTotal = $crossedAdjustedSubtotal; } else { $crossedAdvanceBalance += $crossedAdjustedSubtotal; $crossedTotal = 0; } $crossedAppliedToAdvance = $crossedAdvanceBalance - $crossedPreviousAdvanceBalance; $trackTotal += $crossedTotal; # Now save the totals for this track in yet another db table. # # In order to save time during rendering, we'll fetch the song title # here and save it in the mechanical_statement_track table. # # Going to also fetch the album_id. (Maybe the album name would be more useful?) # my $albumID = 0; my $albumData = RPS::DB::Item::Album->GetByTrackID($trackID); if ($albumData) { $albumID = $albumData->album_id; } my $songData = _getSongByTrackID($trackID); if ( !$songData ) { die "ERROR!!!! could not get any song data for track $trackID"; } _report( " creating track entry : track id = $trackID album id = $albumID track total = $trackTotal", 2 ); my $trackData = RPS::DB::Item::MechanicalStatementTrack->Create( mechanical_statement_id => $statementID, publisher_id => $subPublisherID, track_id => $trackID, album_id => $albumID, subtotal => $trackTotal, song_title => $songData->title, crossed_transaction_subtotal => $crossedTransactionSubtotal, crossed_subtotal => $crossedSubtotal, crossed_previous_advance_balance => $crossedPreviousAdvanceBalance, crossed_applied_to_advance => $crossedAppliedToAdvance, crossed_advance_balance => $crossedAdvanceBalance, crossed_adjusted_subtotal => $crossedAdjustedSubtotal, ); $trackData->save(); $publisherSubtotal += $trackTotal; $subPublisherSubtotal += $trackTotal; } # Create the publisher entry here. # my $subPublisherItem = RPS::DB::Item::MechanicalStatementPublisher->Create( mechanical_statement_id => $statementID, publisher_id => $subPublisherID, subtotal => $subPublisherSubtotal, ); $subPublisherItem->save(); } # Now we _almost_ have the grand total. # We need to now apply the publisher advance, and take the # running balance into account. # my $publisherPreviousBalance = 0; my $publisherBalance = 0; my $publisherAppliedToBalance = 0; my $minPayment = 0; my $publisherTotal = 0; my $transactionSubtotal = 0; # JPK - I'm combining the advance account and the running balance account, so # some of this state is going to go away. # my $publisherAccountMap = RPS::DB::Item::PublisherAccount->Lookup( publisher_id => $publisherID, payor_id => $payorID, ); if ($publisherAccountMap) { $minPayment = $publisherAccountMap->min_payment; my $publisherAccountID = $publisherAccountMap->finance_account_id; my $pendingTransactions; if ($publisherAccountID) { $publisherPreviousBalance = RPS::DB::Item::FinanceAccount->CurrentBalance($publisherAccountID); $pendingTransactions = RPS::DB::Item::PendingTransaction->GetAccountTransactions( finance_account_id => $publisherAccountID ); } # Does this account have pending transactions? If so, slurp them up. # if ( $pendingTransactions && $pendingTransactions->size() > 0 ) { while ( my $pendingTrans = $pendingTransactions->next() ) { # !!! Are these positive or negative? # !!! They are what they are - any sign flipping is handled when the records are created. # $transactionSubtotal += $pendingTrans->amount; my $statementTransItem = RPS::DB::Item::MechanicalStatementTransaction->Create( 'mechanical_statement_id' => $statementID, 'amount' => $pendingTrans->amount, 'memo' => $pendingTrans->memo, 'check_number' => $pendingTrans->check_number, 'pending_transaction_id' => $pendingTrans->pending_transaction_id, 'transaction_date' => $pendingTrans->transaction_date, 'type_code' => $pendingTrans->type_code, ); $statementTransItem->save(); } } } _report( "\n****** publisher_id $publisherID", 3 ); $publisherTotal = $publisherSubtotal + $publisherPreviousBalance + $transactionSubtotal; _report( " pubTotal $publisherTotal = pubSubtotal $publisherSubtotal + prev $publisherPreviousBalance + pending $transactionSubtotal", 3 ); my $statementTotal = $publisherTotal; _report( " statementTotal: $statementTotal", 3 ); my $amountDue = $statementTotal; if ( $amountDue < $minPayment ) { $amountDue = 0; } my $onHold = 0; if ( RPS::DB::Item::Publisher::kStatusOnHold == $publisherData->status() ) { $onHold = 1; $amountDue = 0; } _report( " SUMMARY: subtotal $publisherSubtotal prev bal: $publisherPreviousBalance adv bal: $publisherBalance applied: $publisherAppliedToBalance statement total $statementTotal minPayment $minPayment due $amountDue", 3 ); $statement->subtotal($publisherSubtotal); $statement->physical_subtotal($physicalSubtotal); $statement->digital_subtotal($digitalSubtotal); $statement->transaction_subtotal($transactionSubtotal); # $statement->previous_advance_balance($publisherPreviousAdvanceBalance); # $statement->advance_balance($publisherAdvanceBalance); # $statement->applied_to_advance($publisherAppliedToAdvance); $statement->min_payment($minPayment); $statement->previous_balance($publisherPreviousBalance); $statement->balance($publisherBalance); $statement->applied_to_balance($publisherAppliedToBalance); $statement->statement_total($statementTotal); $statement->amount_due($amountDue); $statement->on_hold($onHold); $statement->save(); } } } sub _markStatementsAsPending() { foreach my $payorID ( keys %$gPublisherStatementMap ) { my $pubIDMap = $gPublisherStatementMap->{$payorID}; foreach my $publisherID ( keys %$pubIDMap ) { my $statement = $gPublisherStatementMap->{$payorID}{$publisherID}; $statement->status(RPS::DB::Item::MechanicalStatement::kStatusPending); _report( " statement id " . $statement->mechanical_statement_id . " is pending" ); $statement->save(); } } } # Assume deduction is a percentage: i.e. 10.0 == 10% # sub _applyDeduction { my ( $units, $deduction ) = @_; return $units if !$deduction; # calculate the deduction amount. # my $amountToDeduct = Common::RSMath::round( $units * ( $deduction / 100 ), 0 ); $units -= $amountToDeduct; return $units; } # It's dumb to hit the database for these stat rate tables. # Better to read the whole table into memory first, then # read from ram. sub _getStatRateTable { if ( !$gStatRateTable ) { $gStatRateTable = {}; my $statRates = RPS::DB::Item::StatRate->GetAll(); while ( my $statRate = $statRates->next() ) { $gStatRateTable->{ $statRate->stat_rate_id } = $statRate; } } return $gStatRateTable; } # Generally we are going to be asking for the same stat rate for the same # date over and over and over and over... so it makes sense to cache it. # sub _getStatRateID { my $endDate = shift; if ( !$gStatRateByDate->{$endDate} ) { my $table = _getStatRateTable(); # get the stat rates, sorted by date # my @sortedRateIDs = sort { $table->{$b}->date_effective <=> $table->{$a}->date_effective } keys %$table; foreach my $testRateID (@sortedRateIDs) { if ( $table->{$testRateID}->date_effective <= $endDate ) { $gStatRateByDate->{$endDate} = $table->{$testRateID}->stat_rate_id; last; } } } return $gStatRateByDate->{$endDate}; } sub _getStatRate { my $statRateID = shift; my $table = _getStatRateTable(); my $statRate = $table->{$statRateID}; assert($statRate); return ( $statRate->rate, $statRate->minute_rate ); } sub _calculateEffectiveRate { my ( $issueStatRateID, $saleStatRateID, $rateBasis, $rateType, $pennyRate, $percentOfRate, $duration, $share, $lockDate ) = @_; _report( "_calculateEffectiveRate : issueStatRateID = $issueStatRateID, saleStatRateID = $saleStatRateID, rateBasis = $rateBasis, pennyRate = $pennyRate, percentOfRate = $percentOfRate, duration = $duration, share = $share", 3 ); # Calculate the effective rate per unit to pay. # rate basis - either sale or release # rate term - full, partial, or penny # my $appliedStatRateID; my ( $baseStatRate, $minuteRate ); my $effectiveRate; if ( RPS::DB::Item::TrackLicense::kRateTypePenny == $rateType ) { $effectiveRate = $pennyRate; $appliedStatRateID = $saleStatRateID; _report( "penny rate : minimum, pennyRate = $pennyRate", 3 ); } else { if ( RPS::DB::Item::TrackLicense::kRateBasisSale == $rateBasis ) { ( $baseStatRate, $minuteRate ) = _getStatRate($saleStatRateID); _report( "rate basis = sale, baseRate = $baseStatRate, minute rate = $minuteRate", 3 ); $appliedStatRateID = $saleStatRateID; } # !!! For this to convert to 'lock date', I'm going to _NEED_ to know the lock date. duh! elsif ( RPS::DB::Item::TrackLicense::kRateBasisLock == $rateBasis ) { my $lockStatRateID = _getStatRateID($lockDate); ( $baseStatRate, $minuteRate ) = _getStatRate($lockStatRateID); _report( "rate basis = lock, baseRate = $baseStatRate, minute rate = $minuteRate", 3 ); $appliedStatRateID = $lockStatRateID; } else { croak "!!! Unknown rate basis: " . $rateBasis; } if ( RPS::DB::Item::TrackLicense::kRateTypeFull == $rateType ) { # Use the base stat rate for songs of 5 minutes or less. # Use the minute rate for longer songs. # $effectiveRate = $baseStatRate; if ( $duration > 300 ) { my $minutes = ceil( $duration / 60 ); my $minBasedRate = ( $minutes * $minuteRate ); _report( "full rate. minutes = $minutes, duration = $duration, minute based rate = $minBasedRate", 3 ); $effectiveRate = ( $minBasedRate > $effectiveRate ? $minBasedRate : $effectiveRate ); } } elsif ( RPS::DB::Item::TrackLicense::kRateTypeMinimum == $rateType ) { $effectiveRate = $baseStatRate; _report( "min rate : minimum, baseStatRate = $baseStatRate", 3 ); } else { croak "!!! Unknown rate type: " . $rateType; } } my $baseRate = $effectiveRate; # 0 % is not an option... # if ( $percentOfRate > 0 && RPS::DB::Item::TrackLicense::kRateTypePenny != $rateType ) { $effectiveRate = $effectiveRate * ( $percentOfRate / 100 ); } assert( $effectiveRate > 0 ); # Now that we have the effective rate, cut it according to the share percentage. # if ( RPS::DB::Item::TrackLicense::kRateTypePenny != $rateType ) { $effectiveRate = $effectiveRate * ( $share / 100 ); } # For my final trick - round these down to 4 places right of the decimal point. # $effectiveRate = _roundRate($effectiveRate); $baseRate = _roundRate($baseRate); _report( "returning effective rate = $effectiveRate", 3 ); return ( $effectiveRate, $baseRate, $appliedStatRateID ); } sub _getStatement { my ( $payorID, $publisherID, $startDate, $endDate ) = @_; _report( "retrieving statement for publisher $publisherID payor $payorID", 4 ); my $statement = $gPublisherStatementMap->{$payorID}{$publisherID}; if ( !$statement ) { $statement = RPS::DB::Item::MechanicalStatement->Lookup( payor_id => $payorID, publisher_id => $publisherID, start_date => $startDate, end_date => $endDate, mechanical_run_id => $gRunID, ); if ( !$statement ) { $statement = RPS::DB::Item::MechanicalStatement->Create( payor_id => $payorID, publisher_id => $publisherID, mechanical_run_id => $gRunID, status => RPS::DB::Item::MechanicalStatement::kStatusCreating, start_date => $startDate, version => 2, end_date => $endDate ); $statement->save(); } else { # !!! Not sure what status are actually acceptable. # It occurs to me that even if we set the state to kStatusCreating, # we might still end up duplicating line items. # However, let's go ahead and allow kStatusCreating to be re-run, and assume # that we will have some way of catching dups at the Sale level. # if ( RPS::DB::Item::MechanicalStatement::kStatusCreating != $statement->status ) { # Maybe _dying_ is a bit drastic... # die "Error - You have already created a statement for payor $payorID publisher $publisherID for this period"; } } $gPublisherStatementMap->{$payorID}{$publisherID} = $statement; } return $statement; } sub _getStatementItem { my ( $mechanicalStatementID, $productID, $issueStatRateID, $saleStatRateID, $trackLicenseID, $saleIDs ) = @_; my $statementItem = RPS::DB::Item::MechanicalStatementItem->Lookup( mechanical_statement_id => $mechanicalStatementID, track_license_id => $trackLicenseID, product_id => $productID, issue_stat_rate_id => $issueStatRateID, sale_stat_rate_id => $saleStatRateID ); if ( !$statementItem ) { # jpk - we need to report upc. # This is stored in the product table. # I really don't want to have to query the product table when # we 'render' this statement item. # # So, I'm going to make upc a field in MechanicalStatementItem, and look up # the value here. # # Also need the ISRC value. # This is stored in the Master table... # my ( $upc, $isrc ) = _getUPCAndISRCFromProductID($productID); my $service; # (XXX) # This is poorly thought out. And, it looks like service_id is _always_ NULL. So punt. # In the future, we should probably have service_id be a unique key into MechanicalStatementItem. # It seems that we'd want a seperate line item for each service (to satisy HFA, mostly). # # if ($saleID) # { # my $sale = Raptor::DB::Item::Sale->Lookup(sale_id => $saleID); # my $serviceID = $sale->service_id; # if (! $serviceID) # { # $serviceID = _fileIDToService($sale->file_id); # } # $service = _serviceIDToName($serviceID); # } $statementItem = RPS::DB::Item::MechanicalStatementItem->Create( mechanical_statement_id => $mechanicalStatementID, track_license_id => $trackLicenseID, product_id => $productID, issue_stat_rate_id => $issueStatRateID, upc => $upc, # service => $service, isrc => $isrc, sale_stat_rate_id => $saleStatRateID ); # Have to save it now, so we can get the id # $statementItem->save(); } # create entry in stmt map table # - But, skip this step for the 'unknown publisher'. # !!! Why? Seems like I _want_ to make entries for pub0, too. # if ($saleIDs) { foreach my $saleID (@$saleIDs) { my $mi = RPS::DB::Item::SaleRunMap->Lookup( sale_id => $saleID, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeMechanical, statement_item_id => $statementItem->mechanical_statement_item_id, status => RPS::DB::Item::SaleRunMap::kStatusPaid, ); if ( !$mi ) { my $mi = RPS::DB::Item::SaleRunMap->Create( sale_id => $saleID, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeMechanical, statement_item_id => $statementItem->mechanical_statement_item_id, status => RPS::DB::Item::SaleRunMap::kStatusPaid, ); $mi->save(); } } } return $statementItem; } sub _calculateEffectivePerTrackRate { my ( $albumID, $issueStatRateID, $saleStatRateID, $controlledComp, $ccTrackLicense ) = @_; assert($controlledComp); my $regionID = $ccTrackLicense->region_id; _report( "\n\n<_calculateEffectivePerTrackRate>_calculateEffectivePerTrackRate: albumID $albumID regionID $regionID issueStatRateID $issueStatRateID saleStatRateID $saleStatRateID controlledComp " . Dumper($controlledComp), 3 ); # !!! Use the database, not the xml classes. # my $tracks = RPS::DB::Item::Track->GetTracksByAlbumID($albumID); # (XXX) Prune out the bonus tracks - They are ignored in this context. # # my $numTracks = $tracks->size(); # _report(" numTracks $numTracks",2); my @trackData; my $totalControlledShare = 0; my $sum = 0; my $outsideRate = 0; my $numTracks; while ( $tracks->hasNext ) { my $track = $tracks->next(); _report( " looking at track :" . $track->track_id . " : " . $track->title, 3 ); # (XXX) This will be a bit of a hack, but... # Need to skip bonus tracks. # I don't know, just by looking at the track, whether it's a bonus track. # Even if I had the product_id, I still would not really know, 'cause the # product might be a track product. # # So, this is a little goofy, but I am going to call a track a bonus track if # it _ever_ appears as a bonus track in the product_track table. # if ( RPS::DB::Item::ProductTrack->TrackIsBonusTrack( $track->track_id ) ) { _report( " track is a bonus track, skipping", 3 ); next; } # Need to get the duration of each track, just in case the rate basis is full... # my $masterID = $track->master_id; croak "!!! Track has no master, cannot determine duration" unless $masterID; my $masterData = RPS::DB::Item::Master->Lookup( master_id => $masterID ); my $duration = $masterData->duration; # Get the theoretical rate for this track # # JPK - Note that I am calculating this with a 100% share. # I will apply the real percent later. # # This value is added to the 'albumPool' # my ($trackRate) = _calculateEffectiveRate( $issueStatRateID, $saleStatRateID, $controlledComp->rate_basis, $controlledComp->rate_type, $controlledComp->penny_rate, $controlledComp->rate_percentage, $duration, 100, $controlledComp->lock_date ); _report( " calculated max trackRate (with duration of $duration) = $trackRate", 3 ); my $hasPublicDomainFlag = 0; my $trackShare = 0; # !!! I believe that _this_ is where I need to take region into account # my $ccStartDate = $ccTrackLicense->term_start; my $ccEndDate = $ccTrackLicense->term_end; my $trackLicenseList; if ( $ccStartDate && $ccEndDate ) { $trackLicenseList = RPS::DB::Item::TrackLicense->GetLicensesByTrackIDAndDate( $track->track_id, $ccStartDate, $ccEndDate ); } else { $trackLicenseList = RPS::DB::Item::TrackLicense->GetLicensesByTrackID( $track->track_id ); } while ( my $trackLicense = $trackLicenseList->next() ) { # Skip inactive licenses. # if ( $trackLicense->inactive() ) { _report( " ... skipping trackLicense " . $trackLicense->track_license_id . " : inactive", 2 ); next; } # Skip public domain licenses. # We _might_ skip the whole track, so we set a flag to remind # us we saw a public domain license. # if ( RPS::DB::Item::TrackLicense::kPublicDomain eq $trackLicense->type ) { _report( " -- this is a public domain license, not going to calculate rate", 2 ); $hasPublicDomainFlag = 1; next; } # !!! So, we'll try simply skipping over track licenses that don't fall in exactly the same # !!! region. Not sure if that is the correct logic or not. # if ( $trackLicense->region_id != $regionID ) { _report( " ... skipping trackLicense " . $trackLicense->track_license_id . " : not in region $regionID", 2 ); next; } $trackShare += $trackLicense->share; _report( " trackShare for track license " . $trackLicense->track_license_id . " = $trackShare", 2 ); # If this is a controlled comp, we just need to note the share %. # Don't need to calculate a rate - it gets a share of what's left over. # if ( RPS::DB::Item::TrackLicense::kControlledComposition eq $trackLicense->type ) { _report( " -- this is a controll comp license, adding share of " . $trackLicense->share . " to controlled share", 2 ); $totalControlledShare += $trackLicense->share; next; } # It's a normal track license, so calculate it's rate. # This rate will end up getting subtracted from the album rate. # my ($tempRate) = _calculateEffectiveRate( $issueStatRateID, $saleStatRateID, $trackLicense->rate_basis, $trackLicense->rate_type, $trackLicense->penny_rate, $trackLicense->rate_percentage, $duration, $trackLicense->share, $trackLicense->lock_date, ); $outsideRate += $tempRate; _report( " effective rate of $outsideRate for this track license: " . Dumper($trackLicense) . " outsideRate = $outsideRate", 2 ); } if ( $trackShare >= 100 && $hasPublicDomainFlag ) { _report( " track is 100% public domain, skipping it", 2 ); next; } # Now that we've looked at all the track licenses, we know whether we're # going to 'count' this track. # $sum += $trackRate; $numTracks++; _report( "using this track in ccomp calulations. sum=$sum, trackRate=$trackRate, numTracks=$numTracks", 3 ); # Any unallocated shares are 'assigned' to the unknown publisher, and will # have their rate calculated at the maximum possible rate. # _report( " trackShare $trackShare", 2 ); if ( $trackShare < 100 ) { my $remainingShare = 100 - $trackShare; my ($tempRate) = _calculateEffectiveRate( $issueStatRateID, $saleStatRateID, RPS::DB::Item::TrackLicense::kRateBasisSale, RPS::DB::Item::TrackLicense::kRateTypeFull, 0, 100, $duration, $remainingShare, 0 ); $outsideRate += $tempRate; _report( " remainingShare = $remainingShare. outsideRate = $outsideRate", 2 ); } } # Do the final calculations. # my $averagePerTrackRate = ( $sum / $numTracks ); _report( "averagePerTrackRate $averagePerTrackRate = albumPool $sum / numTracks $numTracks", 3 ); my $albumRateNumTracks = ( $controlledComp->track_cap < $numTracks ? $controlledComp->track_cap : $numTracks ); _report( "using $albumRateNumTracks as num tracks", 3 ); my $nominalAlbumRate = $averagePerTrackRate * $albumRateNumTracks; my $effectiveAlbumRate = $nominalAlbumRate - $outsideRate; _report( "effective rate $effectiveAlbumRate = nominalRate $nominalAlbumRate - outsideRate $outsideRate", 3 ); _report( "totalControlledShare: $totalControlledShare", 3 ); my $returnRate = ( $effectiveAlbumRate / ( $totalControlledShare / 100 ) ); _report( "Returning rate $returnRate", 3 ); return $returnRate; } sub _getTracksFromProduct { my ($product) = @_; my @tracks; if ( RPS::DB::Item::Product::kProductTypeDigitalTrack == $product->product_type_id ) { my $trackDBItem = RPS::DB::Item::Track->Lookup( track_id => $product->asset_id ); my $track = _getTrack( $trackDBItem->track_id ); if ( !$track->mechanical_exempt ) { push @tracks, $trackDBItem; } } else { # (XXX) Make sure this interface takes bonus tracks into account. # jpk - seems to. # my $collection = RPS::DB::Item::ProductTrack->GetTracksByProductID( $product->product_id ); while ( $collection->hasNext() ) { my $trackDBItem = $collection->next(); my $track = _getTrack( $trackDBItem->track_id ); if ( !$track ) { _report( "ERROR - bad track_id in product_track record: " . Dumper($trackDBItem) ); next; } if ( !$track->mechanical_exempt ) { push @tracks, $trackDBItem; } } } return \@tracks; } sub _report { my ( $string, $verbosity ) = @_; $verbosity = 1 unless defined $verbosity; if ( $gVerbosityLevel >= $verbosity ) { print STDERR $string . "\n"; } } sub _getPreviousSaleStatementItems { my ( $saleID, $saleStatRateID, $issueStatRateID ) = @_; my $hashRef = {}; my $collection = RPS::DB::Item::SaleRunMap->GetPaidMechanicalBySaleID($saleID); # my $collection = RPS::DB::Item::SaleMechanicalStatementItemMap->GetBySaleID($saleID); while ( $collection->hasNext() ) { my $mapEntry = $collection->next(); my $statementItem = RPS::DB::Item::MechanicalStatementItem->Lookup( mechanical_statement_item_id => $mapEntry->statement_item_id ); next unless $statementItem; # Only consider statement items for committed or closed runs. # This will allow us to have more than 1 uncommited run at a time, which # is good for auditing purposes. # next if ( !_statementIsOnCommittedRun( $statementItem->mechanical_statement_id ) ); my $trackLicenseID = $statementItem->track_license_id; # !!! Just for kicks, let's try skipping sales associated with pub0. # my $trackLicense = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $trackLicenseID ); next if ( 0 == $trackLicense->publisher_id ); if ( $hashRef->{$trackLicenseID} ) { _report( "ERROR - unexpectedly encountered more that 1 statement for " . "sale_id $saleID, track_license_id $trackLicenseID" ); } $hashRef->{ $statementItem->track_license_id } = $statementItem; } return $hashRef; } my $gCommittedStatements; sub _statementIsOnCommittedRun { my ($mechanicalStatementID) = @_; if ( !$gCommittedStatements ) { _report( "building a hash of committed statement ids", 3 ); $gCommittedStatements = RPS::DB::Item::MechanicalStatement->GetIDSFromCommittedRuns(); } return $gCommittedStatements->{$mechanicalStatementID}; } sub _processControlledCompAdjustments { my ( $adjustmentState, $startDate, $endDate ) = @_; # !!! So, I now have a structure that contains all the statement items # that have been affected by the addition of a new license. # # We have also already gone ahead and created statements for controlled comps. # However, those statements were for 'new' sales. # # There is probably a way to combine the two processes, but for now I'm going # to just use brute force. _report( Dumper($adjustmentState), 3 ); foreach my $trackLicenseID ( keys %$adjustmentState ) { _report( " CComp adjustment for trackLicense $trackLicenseID", 2 ); my $statementItemList = $adjustmentState->{$trackLicenseID}; my $trackLicense = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $trackLicenseID ); my $payorID = $trackLicense->payor_id; my $publisherID = $trackLicense->publisher_id; my $publisherData = _getPublisher($publisherID); if ( $publisherData && RPS::DB::Item::Publisher::kStatusInactive == $publisherData->status() ) { _report( " this publisher is inactive, skipping", 3 ); next; } # !!! Sanity check # die "ERROR - expected a controlled comp license" unless ( RPS::DB::Item::TrackLicense::kControlledComposition == $trackLicense->type ); my $trackID = $trackLicense->track_id; my $album = RPS::DB::Item::Album->GetByTrackID($trackID); my $albumID = $album->album_id; my $controlledComp = RPS::DB::Item::ControlledComposition->Lookup( controlled_composition_id => $trackLicense->controlled_composition_id ); foreach my $statementItem (@$statementItemList) { # Based on this data, I should be able to create an adjustment item. # # First, though, I need to see if there are any previous adjustments to # this statement item. # # !!! - Should this be total, or amount_paid? # # my $previousTotal = $statementItem->total; my $previousTotal = $statementItem->amount_paid; my $statementItemID = $statementItem->mechanical_statement_item_id; _report( " adjusting statement item $statementItemID", 2 ); my $lastAdjustmentID = 0; my $lastAdjustment = RPS::DB::Item::MechanicalStatementAdjustmentItem->GetLastStatementAdjustment($statementItemID); if ($lastAdjustment) { $previousTotal = $lastAdjustment->new_total; $lastAdjustmentID = $lastAdjustment->mechanical_statement_adjustment_item_id; _report( " previous adjustment id: $lastAdjustmentID", 2 ); } # Why bother adjusting $0.00 amounts? # next unless $previousTotal > 0; # Re-calculate the new effective rate. # # my $issueStatRateID = $statementItem->issue_stat_rate_id; my $issueStatRateID = 16; my $saleStatRateID = $statementItem->sale_stat_rate_id; my $effectivePerTrackRate = _calculateEffectivePerTrackRate( $albumID, $issueStatRateID, $saleStatRateID, $controlledComp, $trackLicense ); # Calculate the new total. # my $share = $trackLicense->share; my $units = $statementItem->net_units; # # net_units are the units after reserves were taken. my $effectiveRate = ( $effectivePerTrackRate * ( $share / 100 ) ); # !!! Let's try leveraging the sprintf to round for us. # !!! We currently are accurate to 4 decimal places. # # my $total = $units * $effectiveRate; # my $total = sprintf("%.4f", ($units * $effectiveRate)); my $total = Common::RSMath::round( $units * $effectiveRate, 4 ); # Calculate the adjustment amount. # !!! Is there _ever_ a reason why the adjustmentAmount should be < 0? # # !!! This math is suspect # - it's possible for results that should yield $adjustmentAmount == 0 to # instead give _very very small_ values. # _report( " calculation factors: share = $share units = $units perTrackRate = $effectivePerTrackRate", 3 ); _report( " effectiveRate = effectivePerTrackRate * (share / 100) == $effectiveRate", 3 ); my $adjustmentAmount = $total - $previousTotal; _report( " old total: $previousTotal new total: $total", 2 ); if ( $adjustmentAmount < 0.01 ) { # Having the adjustment == 0 is not _that_ odd of an occurance. # I believe the very first time we run mechanicals we should see this a lot. # # Going to extend this to limit adjustments to at least 1 cent. # warn "adjustment on track license $trackLicenseID would yield amount of $adjustmentAmount - skipping!"; next; } # Get the statement that this adjustment will appear on. # (This is _not_ the original statement for the original statement item). # # !!! Hey now - if this is not a direct license, I need to use the admin/agent publisherID # if ( !$trackLicense->publisher_direct ) { my $agentID = $publisherData->agent_id; my $adminID = $publisherData->admin_id; if ($agentID) { $publisherID = $agentID; _report( " reporting on agent $publisherID statement", 2 ); } elsif ($adminID) { $publisherID = $adminID; _report( " reporting on admin $publisherID statement", 2 ); } } my $newStatement = _getStatement( $payorID, $publisherID, $startDate, $endDate ); # Now we can create the adjustment item. # my $newStatementID = $newStatement->mechanical_statement_id; my $adjustmentItem = RPS::DB::Item::MechanicalStatementAdjustmentItem->Create( mechanical_statement_id => $newStatementID, track_license_id => $trackLicenseID, original_mechanical_statement_item_id => $statementItemID, previous_mechanical_statement_adjustment_item_id => $lastAdjustmentID, amount => $adjustmentAmount, new_total => $total, adjustment_type_code => RPS::DB::Item::MechanicalStatementAdjustmentItem::kAdjustmentTypeCCompNewLicense, ); $adjustmentItem->save(); _report( "NEW ADJUSTMENT ITEM ID: " . $adjustmentItem->mechanical_statement_adjustment_item_id, 3 ); } } } sub _findLicensesToAdjust { my ( $startDate, $endDate, $payorIDMap ) = @_; # This means find all new track_license entries. # Fetch the album associated with that track. # See if there is a controlled comp associated with it. # If there is, we want to make a list of affected track (or track_licenses), # so that we can flag sales associated with them as needing adjustment. my %licenseMap; my $newTrackLicenses = RPS::DB::Item::TrackLicense->GetNewTrackLicenses(); while ( $newTrackLicenses->hasNext() ) { my $trackLicense = $newTrackLicenses->next(); next if $trackLicense->inactive(); # Skip track licenses that belongs to inactive publishers # my $publisher = _getPublisher( $trackLicense->publisher_id ); next if ( $publisher && RPS::DB::Item::Publisher::kStatusInactive == $publisher->status() ); # Skip track licenses on inactive albums # my $track = RPS::DB::Item::Track->Lookup( track_id => $trackLicense->track_id ); next unless $track; my $trackID = $trackLicense->track_id; my $albumID = $track->album_id; next unless $albumID; my $album = RPS::DB::Item::Album->Lookup( album_id => $albumID ); if ( $album->inactive() || $album->status() == 0 ) { next; } # I think I can skip controlled comp licenses. # (XXX) - Can I? Can additional controlled comps be added after a run? # What does that really mean, if anything? # next if ( RPS::DB::Item::TrackLicense::kControlledComposition == $trackLicense->type ); # (XXX) ccomp can be associated with an individual track, too, not just # a whole album. # We want to get all the controlled comp trackLicense entries for this album. # (if any) # my $affectedLicenses = RPS::DB::Item::TrackLicense->GetCCompTrackLicensesForAlbum($albumID); while ( $affectedLicenses->hasNext() ) { my $affectedTrackLicense = $affectedLicenses->next(); # Skip licenses that don't belong to the request payor ids # my $payorID = $affectedTrackLicense->payor_id; if ( !$payorIDMap->{$payorID} ) { next; } $licenseMap{ $affectedTrackLicense->track_license_id } = $affectedTrackLicense; } } return \%licenseMap; } sub _populateCache { my ($id) = @_; _report("populating XML Cache"); # Get the statements associated with this run. # my $statementList = RPS::DB::Item::MechanicalStatement->GetByMechanicalRunID($id); while ( $statementList->hasNext() ) { my $statement = $statementList->next(); my $statementID = $statement->mechanical_statement_id; my $publisher = RPS::DB::Item::Publisher->Lookup( publisher_id => $statement->publisher_id ); my $pubNameLC = lc( $publisher->publisher_name ); next unless $pubNameLC =~ /harry fox/; # next unless ('harry fox agency' eq lc($publisher->publisher_name)); #next unless (1 == $publisher->publisher_id); print " populating cache for statement $statementID\n"; my $statementXML = RPS::Statement::Mechanical::StatementDisplay->new( mechanicalStatementID => $statementID ); RPS::XMLCache::Store( $statementXML, 'RPS', 'Statement', 'Command', 'ShowMechanical', $statementID ); # Cache the hfa statement if this publisher is an agent. # if ( $publisher->is_agency || $publisher->is_admin ) { print " publisher is admin/agent, populating additional files\n"; # Get a list of all the publisher ids that appear on this statement. # # my $publisherItems = RPS::DB::Item::MechanicalStatementPublisher->GetByMechanicalStatementID($statementID); # while (my $pubItem = $publisherItems->next()) # { # my $focusID = $pubItem->publisher_id; # $statementXML = RPS::Statement::Mechanical::StatementDisplay->new(mechanicalStatementID => $statementID, # focusPublisherID => $focusID); # RPS::XMLCache::Store($statementXML, 'RPS','Statement','Command','ShowMechanical_focusPublisherID_' . $focusID, $statementID); # } # # $statementXML = RPS::Statement::Mechanical::StatementDisplay->new( mechanicalStatementID => $statementID, hfa => 1 ); RPS::XMLCache::Store( $statementXML, 'RPS', 'Statement', 'Command', 'ShowMechanical_hfa', $statementID ); } } } # Going to fetch _all_ track licenses in the requested date range at once. # Then we'll arrange them by track id and product type # !!! This caching? Probably not such a great idea - start and end date are going to # !!! be variable now (tied to the sale record, not the command-line). # sub new_getTrackLicenses { my ( $trackID, $startDate, $endDate, $productTypeID ) = @_; # jpk - I am 'collapsing' the possible range of product type ids. # 1 and 2 will be equated with 1, 3 and 4 with 3. # $productTypeID = 1 if ( $productTypeID == 2 ); $productTypeID = 3 if ( $productTypeID == 4 ); if ( !$gTrackLicenseMap ) { _report( "Caching track licenses..", 2 ); $gTrackLicenseMap = {}; # Fetch all track_licenses in the requested date range. # my $c = RPS::DB::Item::TrackLicense->GetIssuedLicensesByDate( $startDate, $endDate ); my %trackPubTypeMap; while ( my $tl = $c->next() ) { my $tlTrackID = $tl->track_id; # _report(" track_id $tlTrackID", 2); # We want to arrange them by product type. # foreach my $sortProductTypeID ( 0, 1, 3 ) { next if ( !_productTypeMatches( $sortProductTypeID, $tl->product_type_id ) ); # The goal is to end up with one license per publisher per track. # If we have 2 or more, then 1 of them must go. # If we can't unambiguously determine which is a better fit... # well, then we freak out. # my $pubID = $tl->publisher_id; if ( exists $trackPubTypeMap{$tlTrackID}{$pubID}{$sortProductTypeID} ) { _report( " --- more than one? ", 2 ); my $otherTLArray = $trackPubTypeMap{$tlTrackID}{$pubID}{$sortProductTypeID}; # Don't need to look at everything - just the first one. my $otherTL = $otherTLArray->[0]; if ( $otherTL->product_type_id == $tl->product_type_id ) { _report( " --- type is the same ", 2 ); # Add this track license to the ones we've seen before. # push @{ $trackPubTypeMap{$tlTrackID}{$pubID}{$sortProductTypeID} }, $tl; } elsif ( $otherTL->product_type_id < $tl->product_type_id ) { _report( " --- new one more specific, replacing", 2 ); # This track license is a better match than the ones we seen # before, so replace them. # $trackPubTypeMap{$tlTrackID}{$pubID}{$sortProductTypeID} = [$tl]; } else { _report( " --- old ones more specific, skipping", 2 ); # The track licenses we've seen before are a better match, we # can skip this one. # next; } } else { $trackPubTypeMap{$tlTrackID}{$pubID}{$sortProductTypeID} = [$tl]; } } } # Now convert this big ole' hash into a set of arrays. # _report( " converting to arrays", 2 ); foreach my $hashTrackID ( keys %trackPubTypeMap ) { my $pubHash = $trackPubTypeMap{$hashTrackID}; foreach my $hashPubID ( keys %$pubHash ) { my $typeHash = $pubHash->{$hashPubID}; foreach my $hashTypeID ( keys %$typeHash ) { my $hashTLArray = $typeHash->{$hashTypeID}; foreach my $hashTL (@$hashTLArray) { push @{ $gTrackLicenseMap->{$hashTrackID}{$hashTypeID} }, $hashTL; } } } } } _report( " returning track licenses", 2 ); _report( " track licenses for trackID $trackID productTypeID $productTypeID = " . Dumper( $gTrackLicenseMap->{$trackID}{$productTypeID} ), 3 ); return $gTrackLicenseMap->{$trackID}{$productTypeID}; } sub _getTrackLicenses { my ( $trackID, $startDate, $endDate, $productTypeID, $countryCode ) = @_; _report( "_getTrackLicenses: trackID $trackID startDate $startDate endDate $endDate productTypeID $productTypeID", 4 ); # (XXX) one 'last' schema change - now everythng is flat, and simply stored # (XXX) in the track license table. # Get the track_licenses for this track that fit the date range. # my $c = RPS::DB::Item::TrackLicense->GetLicensesByTrackIDAndDate( $trackID, $startDate, $endDate ); # Iterate over the track_licenses, and keep those that fit the requested product_type best. # jpk - at this point, we have limited choices here. # All Product == NULL # LP RPS::DB::Item::Product::kProductTypeLP # CD kProductTypeCD # D kProductTypeDigital # DT kProductTypeDigital (in other words, incoming types of 3 or 4 match track licenses of type '3'); my %pubIDMap; while ( my $tl = $c->next() ) { _report( " evaluating track license " . Dumper($tl), 4 ); if ( $tl->inactive() ) { _report( " skipping -- inactive", 3 ); next; } my $regionID = $tl->region_id; # Skip this track_license if it doesn't match at all. # if ( !_productTypeMatches( $productTypeID, $tl->product_type_id ) ) { _report( " skipping because productTypeMatches failed - passed in product type id $productTypeID", 3 ); next; } # Skip this license if the region doesn't match the country code. # if ( RPS::DB::Item::TrackLicense::kPublicDomain ne $tl->type ) { if ( !_regionMatchesCountryCode( $countryCode, $regionID ) ) { _report( " skipping because countryCode $countryCode did not match region $regionID", 3 ); next; } } # The goal is to end up with one license per publisher. # (XXX) Per _region_... # # If we have 2 or more, then 1 of them must go. # If we can't unambiguously determine which is a better fit... # well, then we freak out. # my $pubID = $tl->publisher_id; # Skip inactive publisher licenses # my $pub = _getPublisher($pubID); if ( $pub && RPS::DB::Item::Publisher::kStatusInactive == $pub->status() ) { _report( "this track licenses belongs to an inactive publisher, skipping", 3 ); next; } # Skip licenses on inactive albums # my $track = RPS::DB::Item::Track->Lookup( track_id => $tl->track_id ); my $album = RPS::DB::Item::Album->Lookup( album_id => $track->album_id ); if ( $album->inactive() || $album->status() == 0 ) { _report( "track license " . $tl->track_license_id . " is on an inactive album, skipping", 3 ); next; } if ( exists $pubIDMap{$pubID} ) { my $otherTL = $pubIDMap{$pubID}; # sanity check - this ought not to happen... # (!!!) Going to keep track of 'ambiguous' warnings, to avoid repeating myself. # if ( $tl->product_type_id == $otherTL->product_type_id ) { # die("ERROR! track_licenses " . $tl->track_license_id . " and " . $otherTL->track_license_id . " are ambiguous"); _report( "ERROR! track_licenses " . $tl->track_license_id . " and " . $otherTL->track_license_id . " are ambiguous" ); next; } # more specific beats less specific. # jpk - at this point, the type will either be undef, or a specific # value. So this is currently an easy determination to make... this may # get more complicated if we change the product type encoding scheme. # # !!! Now with the 'all physical' license type with an id of 255 if ( $otherTL->product_type_id > 0 && $otherTL->product_type_id != 255 ) { # We've already got a 'specific' type, so it wins. # next; } elsif ( $otherTL->product_type_id == 255 && !$tl->product_type_id ) { # We've got the all physical products license, so the 'all products' license loses... # next; } } $pubIDMap{$pubID} = $tl; } # Collapse the map into a single array. # my @outList; foreach my $pubIDKey ( keys %pubIDMap ) { push @outList, $pubIDMap{$pubIDKey}; } return \@outList; } # (XXX) I dislike this. # sub _productTypeMatches { my ( $productTypeID, $licenseProductTypeID ) = @_; return 1 if !$licenseProductTypeID; return 1 if !$productTypeID; return 1 if ( 255 == $licenseProductTypeID && ( $productTypeID != RPS::DB::Item::Product::kProductTypeDigital && $productTypeID != RPS::DB::Item::Product::kProductTypeDigitalTrack ) ); # Apply cd licenses to lps # return 1 if ( $productTypeID == 1 && $licenseProductTypeID == 2 ); # Apply digital album licenses to digital tracks # return 1 if ( $productTypeID == 4 && $licenseProductTypeID == 3 ); return ( $productTypeID == $licenseProductTypeID ); } sub _getProduct { my ($productID) = @_; if ( !$gProductMap ) { _report( "Caching all products...", 2 ); $gProductMap = {}; my $productList = RPS::DB::Item::Product->GetAll(); while ( my $product = $productList->next() ) { $gProductMap->{ $product->product_id } = $product; } } return $gProductMap->{$productID}; } sub _getProductType { my ($productTypeID) = @_; if ( !$gProductTypeMap ) { _report( "Caching all product types...", 2 ); $gProductTypeMap = {}; my $productTypeList = RPS::DB::Item::ProductType->GetAll(); while ( my $productType = $productTypeList->next() ) { $gProductTypeMap->{ $productType->product_type_id } = $productType; } } return $gProductTypeMap->{$productTypeID}; } sub _getTrack { my ($trackID) = @_; if ( !$gTrackMap ) { _report( "Caching all tracks...", 2 ); $gTrackMap = {}; my $trackList = RPS::DB::Item::Track->GetAll(); while ( my $track = $trackList->next() ) { $gTrackMap->{ $track->track_id } = $track; } } return $gTrackMap->{$trackID}; } sub _getAlbum { my ($albumID) = @_; if ( !$gAlbumMap ) { _report( "Caching all albums...", 2 ); $gAlbumMap = {}; my $albumList = RPS::DB::Item::Album->Get(); while ( my $album = $albumList->next() ) { $gAlbumMap->{ $album->album_id } = $album; } } return $gAlbumMap->{$albumID}; } sub _getLabel { my ($labelID) = @_; if ( !$gLabelMap ) { _report( "Caching all labels...", 2 ); $gLabelMap = {}; my $labelList = RPS::DB::Item::Label->GetAll(); while ( my $label = $labelList->next() ) { $gLabelMap->{ $label->label_id } = $label; } } return $gLabelMap->{$labelID}; } sub _getArtist { my ($artistID) = @_; if ( !$gArtistMap ) { _report( "Caching all artist...", 2 ); $gArtistMap = {}; my $artistList = RPS::DB::Item::Artist->GetAll(); while ( my $artist = $artistList->next() ) { $gArtistMap->{ $artist->artist_id } = $artist; } } return $gArtistMap->{$artistID}; } sub _getMaster { my ($masterID) = @_; if ( !$gMasterMap ) { _report( "Caching all masters...", 2 ); $gMasterMap = {}; my $masterList = RPS::DB::Item::Master->Get(); while ( my $master = $masterList->next() ) { $gMasterMap->{ $master->master_id } = $master; } } return $gMasterMap->{$masterID}; } sub _getSong { my ($songID) = @_; if ( !$gSongMap ) { _report( "Caching all songs...", 2 ); $gSongMap = {}; my $songList = RPS::DB::Item::Song->GetAll(); while ( my $song = $songList->next() ) { $gSongMap->{ $song->song_id } = $song; } } return $gSongMap->{$songID}; } sub _getSongByTrackID { my ($trackID) = @_; my $track = _getTrack($trackID); return undef unless $track; my $master = _getMaster( $track->master_id ); return undef unless $master; return _getSong( $master->song_id ); } sub _getPublisher { my ($publisherID) = @_; if ( !$gPublisherMap ) { _report( "Caching all publishers...", 2 ); my $publisherList = RPS::DB::Item::Publisher->GetAll(); while ( my $publisher = $publisherList->next() ) { $gPublisherMap->{ $publisher->publisher_id } = $publisher; } } return $gPublisherMap->{$publisherID}; } my $gServiceMap; sub _serviceIDToName { my ($serviceID) = @_; if ( !$gServiceMap ) { _report( "Caching services...", 2 ); my $serviceList = RPS::DB::Item::Service->GetAll(); while ( my $service = $serviceList->next() ) { $gServiceMap->{ $service->service_id } = uc( $service->service_name_clean ); } } # return $gServiceMap->{$serviceID}; # JPK - Supressing this for now... return ""; } my $gFileServiceMap; sub _fileIDToService { my ($fileID) = @_; if ( !$gFileServiceMap ) { _report( "Caching file service ids...", 2 ); my $fileList = Raptor::DB::Item::File->GetAll(); while ( my $file = $fileList->next() ) { $gFileServiceMap->{ $file->file_id } = $file->service_id; } } return $gFileServiceMap->{$fileID}; } sub _getUPCAndISRCFromProductID { my ($productID) = @_; my $upc; my $isrc; # Fetch the product first. # my $product = _getProduct($productID); if ($product) { $upc = $product->upc_ean; if ( RPS::DB::Item::Product::kProductTypeDigitalTrack == $product->product_type_id ) { # We don't have UPCs for track products. # So we will have to fetch it from the album. # ??? Will digital album products have different UPC from physical track products? # my $track = _getTrack( $product->asset_id ); my $album = _getAlbum( $track->album_id ); if ( $album && !$upc ) { my $products = RPS::DB::Item::Product->GetProductsByAlbumID( $album->album_id, RPS::DB::Item::Product::kProductTypeDigital ); my $albumProduct = $products->next(); if ($albumProduct) { $upc = $albumProduct->upc_ean; } } my $master = _getMaster( $track->master_id ); if ($master) { $isrc = $master->isrc; } } return ( $upc, $isrc ); } } sub _calculateHFAReportingPeriod { my ( $startDate, $endDate, $issueStatRateID, $saleStatRateID, $trackLicense ) = @_; # The default we'll use is just the provided startDate and endDate # if ( $issueStatRateID && $saleStatRateID ) { my $statRates = _getStatRateTable(); my $appliedRateID = $saleStatRateID; # use the stat date's effective date as our start range if it is later # that our run's start date. jpk - Remember that we've already # taken the sale date into account before choosing the stat rate id. # JPK - turn this logic around... # if ( $statRates->{$appliedRateID}->date_effective lt $startDate ) { $startDate = $statRates->{$appliedRateID}->date_effective; _report( " using $startDate for startDate when calculating HFA reporting period - stat rate id $appliedRateID", 2 ); } # See if there is another stat rate after this one in the table. # If there is, then we may need to use it's effective date to cap our # reporting period. # if ( exists $statRates->{ $appliedRateID + 1 } ) { if ( $statRates->{ $appliedRateID + 1 }->date_effective lt $endDate ) { $endDate = $statRates->{ $appliedRateID + 1 }->date_effective; # Need to subtract 1 day, so that the endDate is in the previous month. # my ( $yy, $mm, $dd ) = split( '-', $endDate ); my ( $ny, $nm, $nd ) = Date::Calc::Add_Delta_Days( $yy, $mm, $dd, -1 ); $endDate = join( '-', $ny, $nm, $nd ); _report( " using $endDate for endDate when calculating HFA reporting period -stat rate id " . ( $appliedRateID + 1 ), 2 ); } } } # jpk - At some point we'll have to contemplate license term_start and term_end, too. # _report( " HFA reporting period startDate $startDate endDate $endDate", 2 ); my ( $startYear, $startMonth, $startDay ) = split( '-', $startDate ); my ( $endYear, $endMonth, $endDay ) = split( '-', $endDate ); my ( $dy, $dm, $dd ) = Date::Calc::Delta_YMD( $startYear, $startMonth, $startDay, $endYear, $endMonth, $endDay ); my $months = ( 12 * $dy ) + $dm; if ( $dd > 0 ) { $months++; } # Good ole' harry fox assumes the year is >= 2000. # my $period = sprintf( "%02d%02d%02d", $months, $endMonth, ( $endYear - 2000 ) ); return $period; } sub _productIsDigital { my ($productID) = @_; my $product = _getProduct($productID); my $typeID = $product->product_type_id; if ( RPS::DB::Item::Product::kProductTypeDigital == $typeID || RPS::DB::Item::Product::kProductTypeDigitalTrack == $typeID ) { return 1; } return 0; } sub _getControlledCompIDFromAlbumID { my ($albumID) = @_; my $albumContract = RPS::DB::Item::AlbumContract->GetCComp($albumID); return undef unless $albumContract; my $cc = RPS::DB::Item::ControlledComposition->GetByContractID( $albumContract->artist_contract_id ); return $cc->controlled_composition_id; } sub _standardDeductions { my ( $units, $trackLicense ) = @_; # $units = ceil($units * ($trackLicense->percentage_of_sales / 100 )); # _report(" units after applying PercentageOfSales of " . $trackLicense->percentage_of_sales . " = $units", 2); my $percentOfSalesDeduction = 100 - $trackLicense->percentage_of_sales; $units = _applyDeduction( $units, $percentOfSalesDeduction ); _report( " units after applying PercentageOfSales deduction of " . $percentOfSalesDeduction . " = $units", 2 ); $units = _applyDeduction( $units, $trackLicense->packaging_deduction ); _report( " units after applying PackagingDeduction of " . $trackLicense->packaging_deduction . " = $units", 2 ); $units = _applyDeduction( $units, $trackLicense->free_goods ); _report( " units after applying FreeGoods of " . $trackLicense->free_goods . " = $units", 2 ); $units = _applyDeduction( $units, $trackLicense->misc_deduction ); _report( " units after applying MiscDeduction of " . $trackLicense->misc_deduction . " = $units", 2 ); return $units; } sub _roundRate { my ($rate) = @_; my $newRate = Common::RSMath::round( $rate, 4 ); return $newRate; } sub _getAllPayorIDs { my @ids; my $payors = RPS::DB::Item::Payor->GetAll(); while ( my $payor = $payors->next() ) { push @ids, $payor->payor_id; } return \@ids; } sub _productAlbumIsInactive { my ($product) = @_; my $albumID = $product->asset_id; if ( RPS::DB::Item::Product::kProductTypeDigitalTrack == $product->product_type_id ) { my $trackData = _getTrack( $product->asset_id ); $albumID = $trackData->album_id; } my $album = _getAlbum($albumID); return $album->inactive; } sub _getControlledCompIDFromTrackID { my ($track_id) = @_; my $track = RPS::DB::Item::Track->Lookup( track_id => $track_id ); my $albumID = $track->album_id; my $albumContract = RPS::DB::Item::AlbumContract->GetCComp($albumID); return undef unless $albumContract; my $cc = RPS::DB::Item::ControlledComposition->GetByContractID( $albumContract->artist_contract_id ); return $cc->controlled_composition_id; } sub _regionMatchesCountryCode { my ( $countryCode, $regionID ) = @_; # region_id of 0? that's a problem. We don't allow # a '0' region in track licenses. # assert($regionID); # Pretty straight-forward - Just use the RegionCountryMap table # my $dbItem = RPS::DB::Item::RegionCountryMap->Lookup( country_code => $countryCode, region_id => $regionID ); return ( defined $dbItem ); } sub _getCorrectPublisherID { my ($trackLicense) = @_; my $publisherID = $trackLicense->publisher_id; # Check to see whether this track_license is tagged as 'publisher_direct'. # If it _is_, then we proceed as usual. # If _not_, then activity on this license is not reported on the publisher's # statement - instead, it gets reported on the Agent's statement. # if ( !$trackLicense->publisher_direct ) { my $publisherData = _getPublisher($publisherID); my $agentID = $publisherData->agent_id; my $adminID = $publisherData->admin_id; if ($agentID) { $publisherID = $agentID; _report( " reporting on agent $publisherID statement", 2 ); } elsif ($adminID) { $publisherID = $adminID; _report( " reporting on admin $publisherID statement", 2 ); } # JPK - Note that if the agentID was not set, then we essentially # ignore the publisherDirect flag (and treat it automatically as publisherDirect) # } return $publisherID; } sub _licenseIsValid { my ($trackLicense) = @_; # If this is a full-stat-rate license, but we don't have a duration, then # this is NOT a valid license. Skip it. # if ( RPS::DB::Item::TrackLicense::kRateTypeFull == $trackLicense->rate_type ) { my $track = _getTrack( $trackLicense->track_id ); my $masterID = $track->master_id; die "!!! Track has no master, cannot determine duration" unless $masterID; my $masterData = _getMaster($masterID); my $duration = $masterData->duration; if ( !$duration ) { _report( "WARNING: TrackLicenseID " . $trackLicense->track_license_id . " is a full stat rate with no duration, skipping" ); return 0; } } return 1; } # Putting a bit of abstraction around looking up individual track licenses. # We need to be able to create licenses with 'pseudo-ids', so we'll be able to render # licenses that have somehow changed or been deleted. # sub _getTrackLicense { my ($trackLicenseID) = @_; return RPS::DB::Item::TrackLicense->Lookup( track_license_id => $trackLicenseID ); }