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