#!/usr/bin/perl #------------------------------------------------------------ # Copyright (C) 2006 RoyaltyShare, Inc. All Rights Reserved #------------------------------------------------------------ use strict; use File::Path; 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::RSApp; use Common::RSMath; use Common::TextProgressBar; use Common::Assert; use Common::Log; use Common::DB::Item::Client; use Common::Locale; use Common::Client; use lib '/app/tools/raptor/lib'; use Raptor::DB::Item::Sale; use Raptor::DB::Item::File; use lib '/app/tools/rps/lib'; use RPS::DB::Item::LabelRoyaltyStatementSale; use RPS::LabelRoyalty::Job::CreatePDFStatements; use RPS::LabelRoyalty::LabelRoyaltyRun; use RPS::RoyaltyRun::Status; use RPS::Statement::Label::Report; use RPS::Statement::Label::PDF; use RPS::DB::Item::Track; use RPS::DB::Item::Master; use RPS::DB::Item::ProductTrack; use RPS::DB::Item::Album; use RPS::DB::Item::Artist; use RPS::DB::Item::Label; use RPS::DB::Item::Service; use RPS::DB::Item::Product; use RPS::DB::Item::LabelRoyaltyRun; use RPS::DB::Item::SaleRunMap; #use RPS::DB::Item::SaleLabelRoyaltyRunMap; use RPS::DB::Item::LabelContract; use RPS::DB::Item::LabelContractTerm; use RPS::DB::Item::LabelPayee; use RPS::DB::Item::LabelPayeeAccount; use RPS::DB::Item::FinanceAccount; use RPS::DB::Item::PendingTransaction; use RPS::DB::Item::LabelRoyaltyLabelItem; use RPS::DB::Item::LabelRoyaltyTransactionItem; use RPS::DB::Item::LabelRoyaltyStatement; use RPS::DB::Item::DistributionFee; use constant kCR => "\r\n"; # Allow tuning of the verbosity of our output. # my $gVerbosityLevel = 1; # Parse the command-line options. # my %options; _parseCommandLine(\%options); # JPK - This is not my usual style, but... # Set up some global data structures that we'll fill in as we process sales. # #my %gProcessedSales; # !!! Need to combine these two hashes # #my %gLabelSummary; my %gData; # Get the hostname we are running on. # my $hostname = hostname(); my $gRunID = $options{runID}; # 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 # local $SIG{CHLD} = \&REAPER; my $gChildIsRunning = 1; my $gChildExitCode; 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}); # There should already be an entry in the run table, with a status of kWaitingToRun. # my $obj = RPS::DB::Item::LabelRoyaltyRun->Lookup ( label_royalty_run_id => $gRunID, ); if (! $obj) { die "ERROR: run_id $gRunID is not valid\n"; } # Sanity check - make sure this run is in the correct state. # if (RPS::RoyaltyRun::Status::kWaitingToRun != $obj->status()) { die "ERROR: run_id $gRunID is not in the 'waiting to run' state\n"; } # Set the start time and pid fields # $obj->start_time(Common::DB::Item::kDateTimeNow); $obj->pid($$); $obj->status(RPS::RoyaltyRun::Status::kRunning); $obj->save(); my $payors = [ $obj->payor_id ]; # Now... do the run! # _report("*** Label Royalty Run $gRunID for client id " . $options{clientID} . " for period " . $options{label}); _run_royalties($payors); _report("*** done - Child exiting"); } sub _parent { # Ctrl-c catching is rather weird with forked processes. # I want to ignore this signal in the parent, so that the # child catches it. # Using 'local' so that only the parent ignores the signal. # local $SIG{INT} = 'IGNORE'; my $runID = $options{runID}; # Wait for the child process to complete # while ($gChildIsRunning) { sleep(1); } _report("PARENT: child exited - child exit code = $gChildExitCode"); my $status; if (0 == $gChildExitCode) { $status = RPS::RoyaltyRun::Status::kComplete; } elsif (1 == $gChildExitCode) { $status = RPS::RoyaltyRun::Status::kAborted; } else { $status = RPS::RoyaltyRun::Status::kError; } # Mark the run as complete # my $appSingleton = Common::RSApp->new(clientID => $options{clientID}); my $obj = RPS::DB::Item::LabelRoyaltyRun->Lookup(label_royalty_run_id => $runID); $obj->status($status); $obj->end_time(Common::DB::Item::kDateTimeNow); $obj->save(); _report("PARENT: exiting"); } 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_royalties { my ($payors) = @_; # # --- Process Sales # _report("calling _processSales"); _processSales($payors); # # --- Generate the statements. # _report("calling _generateStatements"); _generateStatements($payors); # Go forth and create pdf files, I Command You! # !!! This is using a lot of CPU... Let's nice the process. # _report("going to create job for PDFs"); my $statementsPath = RPS::Statement::Label::PDF::StatementPathFromRunID($gRunID, $options{clientID}); if (! -d $statementsPath) { mkpath($statementsPath) or die "ERROR: Unable to create path $statementsPath: $!\n"; } my $jobArgs = RPS::LabelRoyalty::Job::CreatePDFStatements->new ( runID => $gRunID, clientID => $options{clientID}, filePath => $statementsPath, ); my $job = $jobArgs->enqueue(); # Clean up - We can get rid of the entries in the LabelRoyaltyStatementSale table # RPS::DB::Item::LabelRoyaltyStatementSale->DeleteAllByLabelRoyaltyRunID($gRunID); } sub _processSales { my ($payors) = @_; my %payorIDMap; foreach my $payorID (@$payors) { $payorIDMap{$payorID} = 1; } my $run = new RPS::LabelRoyalty::LabelRoyaltyRun( labelRoyaltyRunID => $gRunID ); _report("--- processing sales"); my $sales = Raptor::DB::Item::Sale->GetUnprocessedLabelSales(ending_sale_date => $run->EndingSaleDate()); _report("unprocess sales count: " . $sales->size, 2); my %badProducts; my $count = 0; while (my $sale = $sales->next()) { _report("SALE: " . Dumper($sale), 4); # !!! So, how does this work? # !!! Seems like I have to map this sale to the label_id of a label contract term. # !!! I go from product->album->label->term # # !!! I will then need to calculate for each sale the 'gross sales' amount. # !!! This will probably use the same basic logic we use for artist royalties: # !!! - there will be different logic for unit-based sales vs net revenue sales. # # --> Basically, I will be outputting a line for each sale. # --> In addition, I need to accumulate gross sales, dist fee, and net sales # by label id. # I think I'll use a table for the summary, but just a simple file for the # output file. # ... No, I will use some tables for both. I need to be able to re-generate the # output file if necessary. #my %gLabelSummary; #my %gProcessedSales; my $productID = $sale->product_id; # We need the album_id, and the track_id if this is a track sale. # --> This is mostly just to make sure everything is _active_. # my $albumID; my $trackID; my $missingProductPriceFlag = 0; my $product; if ($productID) { $product = RPS::DB::Item::Product->Lookup(product_id => $productID); if (! $product) { _report("skipping sale_id " . $sale->sale_id . " : product $productID cannot be found"); my $mi = RPS::DB::Item::SaleRunMap->Create ( sale_id => $sale->sale_id, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeLabelRoyalty, status => RPS::DB::Item::SaleRunMap::kStatusBadProductID, ); $mi->save(); next; } # Skip inactive products. # if (RPS::DB::Item::Product::kProductStatusActive != $product->product_status_id) { _report("skipping sale_id " . $sale->sale_id . " : product $productID is not active"); my $mi = RPS::DB::Item::SaleRunMap->Create ( sale_id => $sale->sale_id, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeLabelRoyalty, status => RPS::DB::Item::SaleRunMap::kStatusInactiveProduct, ); $mi->save(); next; } ($albumID, $trackID) = _getAlbumTrackFromProduct($product); } else { _report("skipping sale_id " . $sale->sale_id . " : no product id "); my $mi = RPS::DB::Item::SaleRunMap->Create ( sale_id => $sale->sale_id, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeLabelRoyalty, status => RPS::DB::Item::SaleRunMap::kStatusBadProductID, ); $mi->save(); next; } # Check the album's 'inactive' flag. # my $albumData = RPS::DB::Item::Album->Lookup(album_id => $albumID); if ($albumData->inactive() || $albumData->status() == 0) { _report("skipping sale_id " . $sale->sale_id . " : album $albumID is inactive"); my $mi = RPS::DB::Item::SaleRunMap->Create ( sale_id => $sale->sale_id, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeLabelRoyalty, status => RPS::DB::Item::SaleRunMap::kStatusInactiveAlbum, ); $mi->save(); next; } # We'll need to check for the distributor name (if there is one)... # # This gets a little complicated, so I'll try to explain it. # # If there is a service_id in the sale, this file came from a distributor. # That means the service_id in the sale is for the service and the service_id in the file is for the distributor. # # If there is _not_ a service_id in the sale, that means the file came from the service. # In that case, there is no distributor and the service_id in the file is for the service. # First let's see if the sale has a service_id. my $saleServiceID = $sale->service_id; my $fileData = Raptor::DB::Item::File->Lookup(file_id => $sale->file_id); my $distributor; my $distributorName; my $service; if ($saleServiceID) { # If the sale has a service_id, then we'll use that to find the service # and we'll use the file's service_id to find the distributor. $service = RPS::DB::Item::Service->Lookup(service_id => $saleServiceID); my $distributorID = $fileData->service_id; die "ERROR - could not determine service id from file " . $sale->file_id unless $distributorID; $distributor = RPS::DB::Item::Service->Lookup(service_id => $distributorID); $distributorName = $distributor->service_name; } else { # If there is not a service_id in sale, there is no distributor. # So, we just need to figure out the service. my $serviceID = $fileData->service_id; die "ERROR - could not determine service id from file " . $sale->file_id unless $serviceID; $service = RPS::DB::Item::Service->Lookup(service_id => $serviceID); } # We've got the product ID, now determine the label id # my $labelID = $albumData->label_id; my $label = RPS::DB::Item::Label->Lookup(label_id => $labelID); # The details on how we handle physical vs. digital sales will differ. # !!! For now, we're going to skip physical sales... # my $units; my $price; my $grossSales; my $productTypeID = $product->product_type_id; if (RPS::DB::Item::Product::kProductTypeDigital == $productTypeID || RPS::DB::Item::Product::kProductTypeDigitalTrack == $productTypeID) { # Digital # $units = $sale->units; $price = $sale->price; # !!! If the _file_ has a dist fee, that reduces the $price... # my $dist_fee = RPS::DB::Item::DistributionFee->GetDistFeeBySale(file_id => $sale->file_id,sale_id => $sale->sale_id); if ($dist_fee > 1) { $price = Common::RSMath::round($price * ((100 - $dist_fee) / 100), 8); } $grossSales = Common::RSMath::round($units * $price, 8); } else { _report("skipping sale_id " . $sale->sale_id . " : not a digital product"); } # Some last formatting stuff. # Our spec only specifies yyyy-mm, for some reason. # my $dateStart = _formatOutputDate($sale->date_begin); my $dateEnd = _formatOutputDate($sale->date_end); my $artist = RPS::DB::Item::Artist->Lookup(artist_id => $albumData->artist_id); my $isrc; my $clientTrackID; my $discNo; my $trackNo; my $trackName; my $trackArtist; my $trackCustom1; my $trackCustom2; my $trackCustom3; my $upc = $product->upc_ean; my $releaseDate = $product->release_date; if ($trackID) { my $track = RPS::DB::Item::Track->Lookup(track_id => $trackID); $clientTrackID = $track->client_track_id; $trackName = $track->title; $trackCustom1 = $track->custom_1; $trackCustom2 = $track->custom_2; $trackCustom3 = $track->custom_3; my $master = RPS::DB::Item::Master->Lookup(master_id => $track->master_id); $isrc = $master->isrc; my $tArtist = RPS::DB::Item::Artist->Lookup(artist_id => $track->artist_id); $trackArtist = $tArtist->name; # So, there won't _be_ a product_track entry for a digital track product. # In order to fill in the somewhat pointless and misleading disc-no and track-no fields, # we'll have to look up the digital album product that matches the album this digital # track product appears on. # wee # my $productTrack; if (RPS::DB::Item::Product::kProductTypeDigitalTrack == $productTypeID) { my $albumProduct = RPS::DB::Item::Product->Lookup(asset_id => $albumData->album_id, product_type_id => RPS::DB::Item::Product::kProductTypeDigital); if ($albumProduct) { $productTrack = RPS::DB::Item::ProductTrack->Lookup(product_id => $albumProduct->product_id, track_id => $trackID); # Get the album product's upc and release date if necessary # $upc = $albumProduct->upc_ean unless $upc; $releaseDate = $albumProduct->release_date unless $releaseDate; } } else { $productTrack = RPS::DB::Item::ProductTrack->Lookup(product_id => $productID, track_id => $trackID); } if($productTrack) { $discNo = $productTrack->disc_number; $trackNo = $productTrack->disc_track; } else { _report("WARNING: no product_track entry for product_id $productID", 3); } } # Fetch the contract terms that match this label. # my $termsMatched = 0; my $terms = RPS::DB::Item::LabelContractTerm->GetByLabelID($labelID); while (my $term = $terms->next()) { # Fetch the contract, and from that the payee. # my $contract = RPS::DB::Item::LabelContract->Lookup(label_contract_id => $term->label_contract_id); my $payeeID = $contract->label_payee_id; my $payee = RPS::DB::Item::LabelPayee->Lookup(label_payee_id => $payeeID); my $payorID = $contract->payor_id; # Skip this term if the payor doesn't match... # if (! $payorIDMap{$contract->payor_id}) { next; } # So... have more or less all we need now to populate both the sale hash and the payee summary hash. # my $feePercent = $term->distribution_fee; my $fee = Common::RSMath::round($grossSales * ($feePercent / 100), 8); my $netSales = $grossSales - $fee; my $basePrice = Common::RSMath::round($price * $sale->conversion_rate, 8); my $baseGrossSales = Common::RSMath::round($grossSales * $sale->conversion_rate, 8); my $baseNetSales = Common::RSMath::round($netSales * $sale->conversion_rate, 8); my $baseFee = Common::RSMath::round($fee* $sale->conversion_rate, 8); _report(" Creating gData record: payorID $payorID, payeeID $payeeID, labelID $labelID, grossSales $baseGrossSales, fee $baseFee, feePct $feePercent, netSales $baseNetSales", 3); # !!! No reason to store this stuff in a hash. # Lookup or create a label_royalty_label_item here instead. # my $labelRoyaltyLabelItem = _getLabelRoyaltyLabelItem($payorID, $payeeID, $labelID); $labelRoyaltyLabelItem->gross_sales( $labelRoyaltyLabelItem->gross_sales() + $baseGrossSales ); $labelRoyaltyLabelItem->net_sales( $labelRoyaltyLabelItem->net_sales() + $baseNetSales ); $labelRoyaltyLabelItem->distribution_fee($feePercent); $labelRoyaltyLabelItem->fee( $labelRoyaltyLabelItem->fee() + $baseFee); if ($labelRoyaltyLabelItem->label_contract_id() && $labelRoyaltyLabelItem->label_contract_id() != $term->label_contract_id) { die "ERROR - label $labelID - contract id " . $term->label_contract_id . " != " . $gData{$payorID}{$payeeID}{summary}{$labelID}{contractID}; } $labelRoyaltyLabelItem->label_contract_id($term->label_contract_id); $labelRoyaltyLabelItem->save(); # Make the SaleRunMap entry here, since we have a label item id and a sale id... # my $mi = RPS::DB::Item::SaleRunMap->Create ( sale_id => $sale->sale_id, run_id => $gRunID, statement_item_id => $labelRoyaltyLabelItem->label_royalty_label_item_id, run_type => RPS::DB::Item::SaleRunMap::kRunTypeLabelRoyalty, status => RPS::DB::Item::SaleRunMap::kStatusPaid, ); $mi->save(); # !!! No reason to track these elsewhere - Just create a row in the label_royalty_run_sale table. # $gData{$payorID}{$payeeID}{summary}{$labelID}{saleIDs}{$sale->sale_id} = 1; my $labelRoyaltyStatement = _getLabelRoyaltyStatement($payorID, $payeeID); my $statementSale = RPS::DB::Item::LabelRoyaltyStatementSale->Create ( label_royalty_statement_id => $labelRoyaltyStatement->label_royalty_statement_id, # superceeds payor and payee ids. sale_id => $sale->sale_id, label_name => $label->label_name, service_id => $service->service_id, service_name => $service->service_name, distributor => $distributorName, territory => $sale->country_code, period_begin => $dateStart, period_end => $dateEnd, product_type => $sale->product_type, product_format => $sale->format_type, catalog_id => $albumData->catalog_number, upc => $upc, release_date => $releaseDate, album_id => $albumData->client_album_id, album_name => $albumData->title, album_artist => $artist->name, upc_alt => $product->upc_alt, album_custom_1 => $albumData->custom_1, album_custom_2 => $albumData->custom_2, album_custom_3 => $albumData->custom_3, isrc => $isrc, track_id => $clientTrackID, disc_no => $discNo, track_no => $trackNo, track_name => $trackName, track_artist => $trackArtist, track_custom_1 => $trackCustom1, track_custom_2 => $trackCustom2, track_custom_3 => $trackCustom3, units => $units, unit_price => $price, ext_price => $grossSales, currency_code => $sale->currency_code, currency_conv => $sale->conversion_rate, base_unit_price => $basePrice, base_ext_price => $baseGrossSales, free => $sale->free, dist_fee => $feePercent / 100, base_net_price => $baseNetSales, media_type => $sale->media_type, ); $statementSale->save(); # push @{$gData{$payorID}{$payeeID}{sales}}, \%row; } # ... on to the next sale... # if (0 == $termsMatched) { my $mi = RPS::DB::Item::SaleRunMap->Create ( sale_id => $sale->sale_id, run_id => $gRunID, run_type => RPS::DB::Item::SaleRunMap::kRunTypeLabelRoyalty, status => RPS::DB::Item::SaleRunMap::kStatusNoContractTerm, ); $mi->save(); } } } sub _generateStatements { my ($payorList) = @_; my %payorIDMap; foreach my $payorID (@$payorList) { $payorIDMap{$payorID} = 1; } _report("--- generating statements"); #push @{$gReportLines{$payorID}{$payeeID}}, \%row; # Need to fetch balances, apply transactions, etc... # # Fetch the current balances for _all_ label payees, then add blank entries to the main # data structure for those artists that aren't currently in there. # This will result in our producing statements for payees that have balances but no sales. # (which is what we want). # # !!! So, we've already _created_ statements for everybody else. # !!! Therefore we should just create blank statements for these guys, too. # _report("--- fetching label payee balances", 2); my $payeePreviousBalanceHash = _getLabelPayeeBalances(); foreach my $payorID (keys %$payeePreviousBalanceHash) { next unless ($payorIDMap{$payorID}); my $payeeIDHash = $payeePreviousBalanceHash->{$payorID}; foreach my $payeeID (keys %$payeeIDHash) { # Statement has already been saved. This call will create a new one if necessary. # But there is no reason to call 'save' here. # my $statement = _getLabelRoyaltyStatement($payorID, $payeeID); # if (! exists $gData{$payorID}{$payeeID}) # { # _report("!! creating an 'empty' spot for payorID $payorID, payeeID $payeeID, due to a previous balance", 3); # $gData{$payorID}{$payeeID}{summary} = {}; # } } } # Now, we can create both the summary data _and_ the sales report # _report("--- creating summary data", 2); # So rather than looping through payor and payee, we'll iterate over all the Statements. # my $allStatements = RPS::DB::Item::LabelRoyaltyStatement->GetByLabelRoyaltyRunID($gRunID); while (my $statement = $allStatements->next()) { my $statementID = $statement->label_royalty_statement_id(); my $payorID = $statement->payor_id(); my $payeeID = $statement->payee_id(); my $payeeData = RPS::DB::Item::LabelPayee->Lookup(label_payee_id => $payeeID); # If the payee doesn't exist anymore, or is inactive, we'll delete their statement. # !!! Don't need to delete the 'sales' lines - We'll blow all of those away at once at the end. # if (! $payeeData || RPS::DB::Item::LabelPayee::kStatusInactive == $payeeData->status) { _report("label payee $payeeID is inactive or deleted - skipping", 3); $statement->delete(); next; } my $payeePreviousBalance = $payeePreviousBalanceHash->{$payorID}{$payeeID}{balance}; my $payeePendingTransactions = $payeePreviousBalanceHash->{$payorID}{$payeeID}{pending}; my $minPayment = $payeePreviousBalanceHash->{$payorID}{$payeeID}{minPayment}; # Sum up all the seperate Label sections. # my $statementSubtotal = 0; my $labelItems = RPS::DB::Item::LabelRoyaltyLabelItem->GetByLabelRoyaltyStatementID($statementID); while (my $labelItem = $labelItems->next()) { $statementSubtotal += $labelItem->net_sales(); } # Add up the transactions. # my $transactionSubtotal = 0; if ($payeePendingTransactions) { while (my $transaction = $payeePendingTransactions->next()) { $transactionSubtotal += $transaction->amount; my $transactionItem = RPS::DB::Item::LabelRoyaltyTransactionItem->Create ( label_royalty_statement_id => $statementID, amount => $transaction->amount, memo => $transaction->memo, check_number => $transaction->check_number, pending_transaction_id => $transaction->pending_transaction_id, transaction_date => $transaction->transaction_date, type_code => $transaction->type_code, ); $transactionItem->save(); } } # !!! Here, at the last moment, we'll round to the nearest penny. # $minPayment = Common::RSMath::round($minPayment, 2); $payeePreviousBalance = Common::RSMath::round($payeePreviousBalance, 2); $statementSubtotal = Common::RSMath::round($statementSubtotal, 2); $transactionSubtotal = Common::RSMath::round($transactionSubtotal, 2); # Finalize the statement # my $payeeBalance = $payeePreviousBalance + $statementSubtotal + $transactionSubtotal; # see if the minimum payment threshold has been breached. # my $amountDue = 0; if ($payeeBalance >= $minPayment) { $amountDue = $payeeBalance; } # If this payee is 'on_hold', then we tag the statement accordingly, and # set amount_due to 0. # my $onHold = 0; if (RPS::DB::Item::LabelPayee::kStatusOnHold == $payeeData->status) { $onHold = 1; $amountDue = 0; } # Save the grand total # $statement->previous_balance($payeePreviousBalance); $statement->min_payment($minPayment); $statement->balance($payeeBalance); $statement->total($statementSubtotal); $statement->amount_due($amountDue); $statement->on_hold($onHold); $statement->transaction_subtotal($transactionSubtotal); $statement->save(); my $locale = Common::Client::Current()->Locale(); my $currencyCodeLC = lc($locale->currencyFormat()->currencyCode()); _report(" creating the sales report file", 2); my $salesLines = RPS::DB::Item::LabelRoyaltyStatementSale->GetAllByLabelRoyaltyStatementID($statementID); # my $salesLines = $payeeIDHash->{$payeeID}{sales}; my $outFilePath = RPS::Statement::Label::Report::ReportPathFromRunID($gRunID); if (! -d $outFilePath) { mkpath($outFilePath) or die "ERROR: Unable to create path $outFilePath: $!\n"; } my $fileName = RPS::Statement::Label::Report::ReportFileNameFromID($statementID); my $filePath = $outFilePath . $fileName; open(REPORT_FILE, "> $filePath") or die "ERROR - unable to open report file $filePath: $!"; binmode( REPORT_FILE, ':utf8'); # Output a header line # print REPORT_FILE 'label-name' . "\t" . 'service-id' . "\t" . 'service-name' . "\t" . 'distributor' . "\t" . 'territory' . "\t" . 'period-begin' . "\t" . 'period-end' . "\t" . 'product-type' . "\t" . 'product-format' . "\t" . 'catalog-id' . "\t" . 'upc' . "\t" . 'album-id' . "\t" . 'album-name' . "\t" . 'album-artist' . "\t" . 'release-date' . "\t" . 'upc-alt' . "\t" . 'album-custom-1' . "\t" . 'album-custom-2' . "\t" . 'album-custom-3' . "\t" . 'isrc' . "\t" . 'track-id' . "\t" . 'disc-no' . "\t" . 'track-no' . "\t" . 'track-name' . "\t" . 'track-artist' . "\t" . 'track-custom-1' . "\t" . 'track-custom-2' . "\t" . 'track-custom-3' . "\t" . 'units' . "\t" . 'unit-price' . "\t" . 'ext-price' . "\t" . 'currency-code' . "\t" . 'currency-conv' . "\t" . $currencyCodeLC.'-unit-price' . "\t" . $currencyCodeLC.'-ext-price' . "\t" . 'free' . "\t" . 'dist-fee' . "\t" . $currencyCodeLC.'-net-price' . "\t" . 'media_type' . kCR; if ($salesLines && $salesLines->size() > 0) { while (my $salesLine = $salesLines->next()) { # Need to display 'free' as Y or N # my $free; if ($salesLine->{'free'} == 0) { $free = 'N'; } else { $free = 'Y'; } print REPORT_FILE $salesLine->label_name . "\t" . $salesLine->service_id . "\t" . $salesLine->service_name . "\t" . $salesLine->distributor . "\t" . $salesLine->territory . "\t" . $salesLine->period_begin . "\t" . $salesLine->period_end . "\t" . $salesLine->product_type . "\t" . $salesLine->product_format . "\t" . $salesLine->catalog_id . "\t" . $salesLine->upc . "\t" . $salesLine->album_id . "\t" . $salesLine->album_name . "\t" . $salesLine->album_artist . "\t" . $salesLine->release_date . "\t" . $salesLine->upc_alt . "\t" . $salesLine->album_custom_1 . "\t" . $salesLine->album_custom_2 . "\t" . $salesLine->album_custom_3 . "\t" . $salesLine->isrc . "\t" . $salesLine->track_id . "\t" . $salesLine->disc_no . "\t" . $salesLine->track_no . "\t" . $salesLine->track_name . "\t" . $salesLine->track_artist . "\t" . $salesLine->track_custom_1 . "\t" . $salesLine->track_custom_2 . "\t" . $salesLine->track_custom_3 . "\t" . $salesLine->units . "\t" . $salesLine->unit_price . "\t" . $salesLine->ext_price . "\t" . $salesLine->currency_code . "\t" . $salesLine->currency_conv . "\t" . $salesLine->base_unit_price . "\t" . $salesLine->base_ext_price . "\t" . $free . "\t" . $salesLine->dist_fee . "\t" . $salesLine->base_net_price . "\t" . $salesLine->media_type . kCR; } } else { print REPORT_FILE "No Sales this period" . kCR; } close REPORT_FILE; } _report("--- done generating statements", 2); } # 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 -r run_id [-V n]\n"; print "\n"; print "Arguments:\n"; print "\t-r \t\tThe royalty_run id to run.\n"; print "\t-c \t\tThe client_id this run belongs to.\n"; print "\t-V \t\t\tVerbosity level - 0 means no output\n"; } sub _report { my ($string, $verbosity) = @_; $verbosity = 1 unless defined $verbosity; if ($gVerbosityLevel >= $verbosity) { Common::Log::Print($string); } } sub _getAlbumTrackFromProduct { my ($product) = @_; my ($albumID, $trackID); if (RPS::DB::Item::Product::kProductTypeDigitalTrack == $product->product_type_id) { $trackID = $product->asset_id; my $trackData = RPS::DB::Item::Track->Lookup(track_id => $trackID); $albumID = $trackData->album_id; } else { $albumID = $product->asset_id; } return ($albumID, $trackID); } sub _formatOutputDate { my ($inDate) = @_; my @bits = split('-', $inDate); return $bits[0] . '-' . $bits[1]; } sub _formatNumber { my ($value) = @_; $value = Common::Client::Current()->Locale()->formatNumber($value); return $value; } sub _getLabelPayeeBalances { my %resultHash; my $allAccountMappings = RPS::DB::Item::LabelPayeeAccount->GetAll(); while (my $accountMap = $allAccountMappings->next()) { # Get the current balance for this account. # !!! I'd like to make an entry in this hash if this guy has _pending transactions_, too. # my $currentBalance; my $hasPendingTransactions; my $pendingTransactions; if ($accountMap->finance_account_id) { $currentBalance = RPS::DB::Item::FinanceAccount->CurrentBalance($accountMap->finance_account_id); $pendingTransactions = RPS::DB::Item::PendingTransaction->GetAccountTransactions(finance_account_id => $accountMap->finance_account_id); } if ($currentBalance != 0 || ($pendingTransactions && $pendingTransactions->size() > 0)) { $resultHash{$accountMap->payor_id}{$accountMap->label_payee_id}{balance} = $currentBalance; if ($pendingTransactions && $pendingTransactions->size() > 0) { $resultHash{$accountMap->payor_id}{$accountMap->label_payee_id}{pending} = $pendingTransactions; } } $resultHash{$accountMap->payor_id}{$accountMap->label_payee_id}{minPayment} = $accountMap->min_payment; } # _report("_getLabelPayeeBalances: " . Dumper(\%resultHash), 4); return \%resultHash; } sub _uberSort { my $result = uc($a->{'label-name'}) cmp uc($b->{'label-name'}); if (! $result) { $result = $a->{'service-id'} <=> $b->{'service-id'}; } if (! $result) { $result = uc($a->{'territory'}) cmp uc($b->{'territory'}); } if (! $result) { $result = uc($a->{'product-format'}) cmp uc($b->{'product-format'}); } if (! $result) { $result = uc($a->{'album-name'}) cmp uc($b->{'album-name'}); } if (! $result) { $result = uc($a->{'disc-no'}) cmp uc($b->{'disc-no'}); } if (! $result) { $result = uc($a->{'track-no'}) cmp uc($b->{'track-no'}); } return $result; } sub _getLabelRoyaltyStatement { my ($payorID, $payeeID) = @_; my $statement = RPS::DB::Item::LabelRoyaltyStatement->Lookup ( payee_id => $payeeID, payor_id => $payorID, label_royalty_run_id => $gRunID ); if (! $statement) { $statement = RPS::DB::Item::LabelRoyaltyStatement->Create ( payee_id => $payeeID, payor_id => $payorID, label_royalty_run_id => $gRunID, ); $statement->save(); } return $statement; } sub _getLabelRoyaltyLabelItem { my ($payorID, $payeeID, $labelID) = @_; # Fetch the statement associated with this stuff. # my $statement = _getLabelRoyaltyStatement($payorID, $payeeID); my $statementID = $statement->label_royalty_statement_id; my $labelItem = RPS::DB::Item::LabelRoyaltyLabelItem->Lookup ( label_royalty_statement_id => $statementID, label_id => $labelID, ); if (! $labelItem) { $labelItem = RPS::DB::Item::LabelRoyaltyLabelItem->Create ( label_royalty_statement_id => $statementID, label_id => $labelID, ); $labelItem->save(); } return $labelItem; }