# Copyright (C) 2006 RoyaltyShare, Inc. All Rights Reserved # $Id$ use strict; use warnings; use lib '/app/tools/common/lib'; use Common::RSApp; use lib '/app/tools/rps/lib'; use RPS::DB::Item::TrackLicense; sub new { my ($class, %args) = @_; my $self = bless {}, $class; return $self->_init(%args); } sub _init { my ($self, %args) = @_; $self->{clientID} = $args{clientID}; $self->{startDate} = $args{startDate}; $self->{endDate} = $args{endDate}; $self->{dryRun} = $args{dryRun}; $self->{verbosity} = $args{verbosity}; $self->{showProgress} = $args{showProgress}; $self->{outputFileHandle} = $args{outputFileHandle}; return $self; } sub calculateMechanicals { my ($self) = @_; $self->_findLicensesToAdjust(); $self->_processSales(); } sub commitToDB { my ($self) = @_; } sub writeCache { my ($self) = @_; } # # _processSales # sub _processSales { my ($self) = @_; my $sales = Raptor::DB::Item::Sale::GetUnprocessedMechanicalSales ( endDate => $self->{endDate}, period => 1, # <-- This is far from being generic... countryCode => 'US', formatType => 'D', skipFree => 1, ); while (my $sale = $sales->next()) { # 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); my $productID = $sale->product_id; if (! $productID) { _report(" !!! skipping sale - missing product id: " . Dumper($sale), 2 ); next; } # Fetch the product's data, then dereference some of the fields for later. # my $product = _getProduct($productID); 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; # Get the stat rate that corresponds to the release date. # my $issueStatRateID = _getStatRateID($releaseDate); # 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 $previousSaleStatementItems = _getPreviousSaleStatementItems($sale, $issueStatRateID, $saleStatRateID); _report("\n\n-----------------------------------------------\nsale: " . Dumper($sale), 3); _report("previous sale statement items: " . Dumper($previousSaleStatementItems), 3); # Dereference the number of units in this sale. # my $units = $sale->units; # Fetch all the tracks associated with this sale. # my $productTracks = _getTracksFromProduct($product); _report(" track count: " . scalar @$productTracks, 3); # Process each track # foreach my $productTrack (@$productTracks) { $self->_processProductTrack($productTrack, $sale, $previousSaleStatementItems, $productTypeID, $issueStatRateID, $saleStatRateID); } } } sub _processProductTrack { my ($self, $productTrack, $sale, $previousSaleStatementItems, $productTypeID, $issueStatRateID, $saleStatRateID) = @_; my $trackID = $productTrack->track_id; # Get the track data. # my $track = _getTrack($productTrack->track_id); # get the appropriate track-licenses for this track. # my $trackLicenseList = _getTrackLicenses($track->track_id(), $self->{startDate}, $self->{endDate}, $productTypeID); # We'll want to know whether this track's ownership is 100% accounted for. # my $shareSum = 0; # 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; foreach my $trackLicense (@$trackLicenseList) { my $share = $self->_processTrackLicense($track, $trackLicense, $sale, $duration, $previousSaleStatementItems, $issueStatRateID, $saleStatRateID); $shareSum += $share; } _report(" track share% attributed to licenses: $shareSum", 2); # Figure out how much remains unallocated # if (100 > $shareSum) { my $unallocatedShare = (100 - $shareSum); my $units = $sale->units; # Calculate the _maximum_ applicable rate. # my $effectiveRate = _calculateEffectiveRate($issueStatRateID, $saleStatRateID, RPS::DB::Item::TrackLicense::kRateBasisSale, RPS::DB::Item::TrackLicense::kRateTypeFull, 0, $duration, $unallocatedShare); my $totalOwed = $units * $effectiveRate; _report(" unallocated shares: $unallocatedShare rate: $effectiveRate totalOwed: $totalOwed", 2); $self->{unallocatedBalance} += $totalOwed; } } sub _processTrackLicense { my ($self, $track, $trackLicense, $sale, $duration, $previousSaleStatementItems, $issueStatRateID, $saleStatRateID) = @_; my $productID = $sale->productID; my $units = $sale->units; my $share = 0; my $trackLicenseID = $trackLicense->track_license_id; my $publisherID = $trackLicense->publisher_id; _report(" trackLicenseID: $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 ($self->{licensesToAdjust}{$trackLicenseID}) { # This statement item will need adjusting. # _report(" ++ found adjusted license, adding to adjustment state", 3); push @{$self->{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... # return $trackLicense->share; } # Skip this license if it's the controlled composition clause. # (But first, we'll stash the controlled comp away for later perusal) # # !!! - There can be MORE THAN ONE controlled comp track-license per track. # The _license_ should be the same, but the publisher (and share) may vary. # # jpk - Well, not quite - the _clause_ is the same, but the ccomp_license will vary. # So, all the terms will be shared (and therefore, the effective rate). # |-> This might help simplifying things, espescially calculating adjustments. # # So I need to store all the track-licenses. # if (RPS::DB::Item::TrackLicense::kControlledComposition eq $trackLicense->type) { my $albumID = $track->album_id; if (! $albumID) { croak("??? We have a controlled composition on a track with no album? - " . Dumper($trackLicense)); } my $ccID = $trackLicense->controlled_composition_id; if (! $ccID) { _report("!!! ERROR !!! trackLicense is type controlled composition, but controlled_composition_id is invalid! SKIPPING"); next; } $share = $trackLicense->share; _report(" license is controlled comp - share = $share", 3); # (XXX) It may be helpful to wrap some more abstraction around this gnarly data structure.. my %newRecord; $newRecord{units} = $units; $newRecord{saleID} = $sale->sale_id; $newRecord{share} = $trackLicense->share; my $trackID = $track->track_id; push @{$self->{ccompState}{$albumID}{$ccID}{$trackLicenseID}{$productID}{$issueStatRateID}{$saleStatRateID}{$trackID}}, \%newRecord; return $share; } # If this is a public domain share, make a note of the share, then skip. # if (RPS::DB::Item::TrackLicense::kPublicDomain eq $trackLicense->type) { $share = $trackLicense->share; _report(" license is public domain - share = $share", 3); return $share; } # 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; if ( $agentID) { $publisherID = $agentID; _report(" reporting on agent $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) # } # Calculate the royalties! # $share = $trackLicense->share; _report(" share % = $share", 2); # Calculate the effective units to pay on. # my $effectiveUnits = $units; _report(" gross units = $units", 2); $effectiveUnits = ceil($effectiveUnits * ($trackLicense->percentage_of_sales / 100 )); _report(" units after applying PercentageOfSales of " . $trackLicense->percentage_of_sales . " = $effectiveUnits", 2); $effectiveUnits = _applyDeduction($effectiveUnits, $trackLicense->packaging_deduction); _report(" units after applying PackagingDeduction of " . $trackLicense->packaging_deduction . " = $effectiveUnits", 2); $effectiveUnits = _applyDeduction($effectiveUnits, $trackLicense->free_goods); _report(" units after applying FreeGoods of " . $trackLicense->free_goods . " = $effectiveUnits", 2); $effectiveUnits = _applyDeduction($effectiveUnits, $trackLicense->misc_deduction); _report(" units after applying MiscDeduction of " . $trackLicense->misc_deduction . " = $effectiveUnits", 2); # Calculate the effective rate # my $effectiveRate = _calculateEffectiveRate($issueStatRateID, $saleStatRateID, $trackLicense->rate_basis, $trackLicense->rate_type, $trackLicense->penny_rate, $duration, $share); _report(" effectiveRate = $effectiveRate", 2); # If there is a reserve specified, take it now (after other deductions) # my $amountReserved = 0; my $unitsAfterReserve = _applyDeduction($effectiveUnits, $trackLicense->reserve_percentage); if ($unitsAfterReserve < $effectiveUnits) { $amountReserved = $effectiveUnits - $unitsAfterReserve; $effectiveUnits = $unitsAfterReserve; } _report(" effectiveUnits after reserve % of " . $trackLicense->reserve_percentage . " : $effectiveUnits", 2); my $totalOwed = $effectiveUnits * $effectiveRate; _report(" totalOwed: $totalOwed", 2); # Fetch the Statement. # my $statement = $self->_getStatement($publisherID, $self->{startDate}, $self->{endDate}); # Create a statement item if one doesn't exist yet. # my $statementID = $statement->mechanical_statement_id; my $statementItem = $self->_getStatementItem($statementID, $productID, $issueStatRateID, $saleStatRateID, $trackLicense->track_license_id, $sale->sale_id); # Update the statement item # $statementItem->gross_units( $statementItem->gross_units() + $units); $statementItem->net_units( $statementItem->net_units() + $effectiveUnits ); $statementItem->net_rate( $effectiveRate ); # !!! Might be good to sanity-check this. $statementItem->amount( $statementItem->amount() + $totalOwed ); $statementItem->reserved( $statementItem->reserved() + $amountReserved); $statementItem->save(); _report(" created statement item\n", 2); # Save the reserved units someplace. # (Need to stash these away for post-processing) # if ($amountReserved) { my $statementItemID = $statementItem->mechanical_statement_item_id; $self->{reserves}{$trackLicenseID}{$productID}{$issueStatRateID}{$saleStatRateID}{$statementItemID}{units} += $amountReserved; $self->{reserves}{$trackLicenseID}{$productID}{$issueStatRateID}{$saleStatRateID}{$statementItemID}{rate} = $effectiveRate; } return $share; } # # _findLicensesToAdjust # # Find all 'new' track_license entries. # Determine whether these new track_licenses have a controlled composition # association. If they do, we will make a list of affected track_licenses, # so we can flag sales associated with those as possibly needing adjusting. # sub _findLicensesToAdjust { my ($self) = @_; my %licenseMap; my $newTrackLicenses = RPS::DB::Item::TrackLicense::GetNewTrackLicenses(); while ($newTrackLicenses->hasNext()) { my $trackLicense = $newTrackLicenses->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. # Get the album associated with this track. # my $trackID = $trackLicense->track_id; my $track = RPS::DB::Item::Track->Lookup(track_id => $trackID); my $albumID = $track->album_id; next unless $albumID; # 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(); $licenseMap{$affectedTrackLicense->track_license_id} = $affectedTrackLicense; } } $self->{licensesToAdjust} = \%licenseMap; } sub _getStatement { my ($self, $publisherID, $startDate, $endDate) = @_; my $timer = Common::Timer->new(); my $statement = $self->{statements}{$publisherID}; if (! $statement) { $statement = RPS::DB::Item::MechanicalStatement->Lookup ( publisher_id => $publisherID, start_date => $startDate, end_date => $endDate ); if (! $statement) { $statement = RPS::DB::Item::MechanicalStatement->Create ( publisher_id => $publisherID, mechanical_run_id => $gRunID, status => RPS::DB::Item::MechanicalStatement::kStatusCreating, start_date => $startDate, 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 publisher $publisherID for this period"; } } $self->{statements}{$publisherID} = $statement; } return $statement; } sub _getStatementItem { my ($mechanicalStatementID, $productID, $issueStatRateID, $saleStatRateID, $trackLicenseID, $saleID) = @_; my $timer = Common::Timer->new(); 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. # my $upc = _getUPCFromProductID($productID); my $service; 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, sale_stat_rate_id => $saleStatRateID ); # Have to save it now, so we can get the id # $statementItem->save(); # create entry in stmt map table # if ($saleID) { my $mechSaleMap = RPS::DB::Item::SaleMechanicalStatementItemMap->Create ( sale_id => $saleID, mechanical_statement_item_id => $statementItem->mechanical_statement_item_id(), ); $mechSaleMap->save(); } } return $statementItem; } sub _report { my ($self, $string, $verbosity) = @_; $verbosity = 1 unless defined $verbosity; if ($self->{verbosity} >= $verbosity) { my $fh = $self->{outputFileHandle}; print $fh $string . "\n"; } } ### 1;# ###