#!/usr/bin/perl # Custom breakdown report # # ./showstatement.pl -c clientID -s stmtID -a albumID -t trackID -r regionID -o contractID -e termID # # There are seven types of reports needed here: # # TAB1: Earnings by Service: # service-name net-units net-earnings (cur) % of total # # TAB2: Earnings by Country: # country net-units net-earnings (cur) % of total # # TAB3: Earnings by Format: # product-format net-units net-earnings (cur) % of total # # TAB4: Earnings by Album by Service # album-name album-artist catalog-id service-name net-units net-earnings (cur) # # TAB5: Earnings by Album by Country # album-name album-artist catalog-id country net-units net-earnings (cur) # # TAB6: Earnings by Track by Service # track-name track-artist isrc service-name net-units net-earnings (cur) # # TAB7: Earnings by Track by Country # track-name track-artist isrc country net-units net-earnings (cur) # # NOTE(S): # For physical sales, the statement aggregates net unit information from sales as full # numbers (no rounding), but rounds the net units before multiplying it by the net rate # (% wholesale document terms) -- this is what gets stored in the income item record. # # In the raw sales report, the net units are displayed as full numbers and this is what # is used for calculating the net earnings. If you're trying to reconcile the net units # and earnings for a statement line, you may see some slight differences due to rounding # between the relevant sale(s) in the raw sales report and the statement line. # # In the custom report, we aggregate the net units _and_ earnings while aggregating the sales, # rather than after rounding the net units and calculating earnings after aggregation as # seen on the statement. Hence any custom aggregation may not match the statement 100% # (it will however match the raw sales report). # # Although the custom report factors reserves (if any) in the net unit and reserve calculations, # it does not display the reserve amounts. use strict; use Getopt::Long; use Data::Dumper; use Time::HiRes qw ( time ); use Excel::Writer::XLSX; use Math::Round; use lib '/app/tools/common/lib'; use Common::RSApp; use Common::RSMath; use Common::CurrencyFormat; use lib '/app/tools/rps/lib'; use RPS::DB::Item::ArtistPayee; use RPS::DB::Item::NewArtistContract; use RPS::DB::Item::NewArtistContractTerm; use RPS::DB::Item::Payor; use RPS::DB::Item::ArtistRoyaltyRun; use RPS::DB::Item::ArtistRoyaltyStatement; use RPS::DB::Item::ArtistRoyaltyAlbum; use RPS::DB::Item::ArtistRoyaltyIncomeItem; use RPS::DB::Item::Artist; use RPS::DB::Item::Album; use RPS::DB::Item::Track; use RPS::DB::Item::Master; use RPS::DB::Item::IncomeSource; use RPS::DB::Item::Region; use RPS::DB::Item::Product; use RPS::File::Sale; use RPS::Statement::Artist::Excel; # Allow tuning of the verbosity of our output. # #my $gVerbosityLevel = 1; my %contractMap; my %albumMap; my %trackArtistMap; my %sourceMap; # Parse the command-line. # my %options; parseCommandLine( \%options ); my $clientID = $options{clientID}; #my $termID = $options{termID}; my $stmtID = $options{stmtID}; my $filterAlbumID = $options{albumID}; my $summary = $options{summary}; my $_verbose = $options{verbose}; my $_analyze = $options{analyze}; my $gRunID = $options{runID}; my $gRawSales = $options{rawsales}; # if set, generate raw sales report my $gMakeExcel = $options{makeexcel}; my $gEarningsByService = $options{earningsbyservice}; # if set, generate earnings by service report (RSD-6381) my $gEarningsByCountry = $options{earningsbycountry}; my $gEarningsByFormat = $options{earningsbyformat}; my $gEarningsByAlbumByService = $options{earningsbyalbumbyservice}; my $gEarningsByAlbumByCountry = $options{earningsbyalbumbycountry}; my $gEarningsByTrackByService = $options{earningsbytrackbyservice}; my $gEarningsByTrackByCountry = $options{earningsbytrackbycountry}; my %albumTitleMap; # used for sorting by album my %trackTitleMap; # used for sorting by track #my $trackID = $options{trackID}; #my $regionID = $options{regionID}; #my $contractID = $options{contractID}; my $filterReserves= $options{reserves}; # limit output to lines where reserves were held my $appSingleton = Common::RSApp->new( clientID => $clientID ); my $dbo = Common::RSApp::GetClientDB(); my $cdbo = Common::RSApp::GetCommonDB(); my $dbh = $dbo->DBH; my $currencyCode = Common::Client::Current()->Locale()->currencyFormat()->currencyCode(); my $currencySymbol = Common::Client::Current()->Locale()->currencyFormat()->symbol(); my $analyze = (scalar @$_analyze); my $verbose = (scalar @$_verbose); my $gVerbosityLevel = ($verbose > 0) ? $verbose : 1; my @statements; if ( $gRunID ) { my $sql = "SELECT artist_royalty_statement_id FROM artist_royalty_statement WHERE artist_royalty_run_id=$gRunID"; my $sth = $dbo->DoCmd($sql); while( my( $id ) = $sth->fetchrow_array() ) { push @statements, $id; } } else { push @statements, $stmtID; } my $gPayorName; my $gRunName; my $rawSalesWorkbook; my $rawSalesWorksheet; my $rawSalesHeaderFormat; my $rawSalesPlainFormat; my $rawSalesRow; my $showRawSalesHeader; my %incomeSourceMap; my $sql = "SELECT income_source_id, name FROM income_source"; my $sth = $cdbo->DoCmd($sql); # RSCOMMON while( my($id, $name) = $sth->fetchrow_array() ) { $incomeSourceMap{$id} = $name; } my %regionMap; $sql = "SELECT region_id, name FROM region"; $sth = $dbo->DoCmd($sql); while( my($id, $name) = $sth->fetchrow_array() ) { $regionMap{$id} = $name; } my %rateTypeMap; $sql = "SELECT contract_rate_type_id, name FROM contract_rate_type"; $sth = $cdbo->DoCmd($sql); # RSCOMMON while( my($id, $name) = $sth->fetchrow_array() ) { $rateTypeMap{$id} = $name; } my %channelMap; $sql = "SELECT channel_id, name FROM channel"; $sth = $dbo->DoCmd($sql); while( my($id, $name) = $sth->fetchrow_array() ) { $channelMap{$id} = $name; } my %priceLevelMap; $sql = "SELECT price_level_id, name FROM price_level"; $sth = $cdbo->DoCmd($sql); # RSCOMMON while( my($id, $name) = $sth->fetchrow_array() ) { $priceLevelMap{$id} = $name; } my %productTypeMap; # cache of product types by productID my %productTypeNameMap; # cache of sale product_type to human-readable string # ------------------------------ # Following are used for custom reports # use constant kExcelFontSize => 10; my $gShowEarningsByServiceHeader; my $gShowEarningsByCountryHeader; my $gShowEarningsByFormatHeader; my $gShowEarningsByAlbumByServiceHeader; my $gShowEarningsByAlbumByCountryHeader; my $gShowEarningsByTrackByServiceHeader; my $gShowEarningsByTrackByCountryHeader; my %serviceMap; # used for earnings by service report my %serviceNameMap; # hash of serviceID to name my %countryNameMap; # hash of countryCode to name my %formatNameMap; # hash of formatType to name my %trackMap; # hash of id to name and artist my %earningsByCountry; # used for Earnings by Country report my %earningsByFormat; # used for Earnings by Format report my %earningsByAlbumByService; # used for Earnings by Album by Service report my %earningsByAlbumByCountry; # used for Earnings by Album by Country report my %earningsByTrackByService; # used for Earnings by Track by Service report my %earningsByTrackByCountry; # used for Earnings by Track by Country report my %gColumnFormat; my $gEarningsByServiceRow = 0; # Excel row counter my $gEarningsByCountryRow = 0; # Excel row counter my $gEarningsByFormatRow = 0; # Excel row counter my $gEarningsByAlbumByServiceRow = 0; # Excel row counter my $gEarningsByAlbumByCountryRow = 0; # Excel row counter my $gEarningsByTrackByServiceRow = 0; # Excel row counter my $gEarningsByTrackByCountryRow = 0; # Excel row counter my %gRowIndex; # indexed by report type (replaces individual row counters, above) my %gWorksheet; # indexed by report type my %gShowHeader; # indexed by report type my %gFormat; # indexed by report type use constant kPercentFormat => 'number:#0.0%'; use constant kEarningsFormat => 'number:#,##0.000000'; use constant kUnitsFormat => 'number:#,###'; # TODO TODO TODO TODO TODO # _x_ Need to use the constants for the rest of the tabs (only 1st tab right now..) # _x_ Need to implement "1st ten rows by album" feature # _x_ Album By Service # _x_ Album By Country # _x_ Track By Service # _x_ Track By Country # _x_ Need to underline row between each album on the "..by service" # _x_ Album By Service # _x_ Album By Country # _x_ Track By Service # _x_ Track By Country my %gReportDefinition = ( # Excel report defintions 'earningsByService' => { # TAB1 'header' => [ 'service-name', 'net-units', "net-earnings ($currencyCode)", '% of total' ], 'format' => [ # 'text', 'number:#,###', 'number:#,##0.000000', 'number:#0.0%' 'text', kUnitsFormat, kEarningsFormat, kPercentFormat ] }, 'earningsByCountry' => { # TAB2 'header' => [ 'country', 'net-units', "net-earnings ($currencyCode)", '% of total' ], 'format' => [ # 'text', 'number:#,###', 'number:#,##0.000000', 'number:#0.0%' 'text', kUnitsFormat, kEarningsFormat, kPercentFormat ] }, 'earningsByFormat' => { # TAB3 'header' => [ 'product-format', 'net-units', "net-earnings ($currencyCode)", '% of total' ], 'format' => [ # 'text', 'number:#,###', 'number:#,##0.000000', 'number:#0.0%' 'text', kUnitsFormat, kEarningsFormat, kPercentFormat ] }, 'earningsByAlbumByService' => { # TAB4 'header' => [ 'album-name', 'album-artist', 'catalog-id', 'service-name', 'net-units', "net-earnings ($currencyCode)" ], 'format' => [ # 'text', 'text', 'text', 'text', 'number:#,###', 'number:#,##0.000000' 'text', 'text', 'text', 'text', kUnitsFormat, kEarningsFormat ] }, 'earningsByAlbumByCountry' => { # TAB5 'header' => [ 'album-name', 'album-artist', 'catalog-id', 'country', 'net-units', "net-earnings ($currencyCode)" ], 'format' => [ # 'text', 'text', 'text', 'text', 'number:#,###', 'number:#,##0.000000' 'text', 'text', 'text', 'text', kUnitsFormat, kEarningsFormat ] }, 'earningsByTrackByService' => { # TAB6 'header' => [ 'track-name', 'track-artist', 'isrc', 'service-name', 'net-units', "net-earnings ($currencyCode)" ], 'format' => [ # 'text', 'text', 'text', 'text', 'number:#,###', 'number:#,##0.000000' 'text', 'text', 'text', 'text', kUnitsFormat, kEarningsFormat ] }, 'earningsByTrackByCountry' => { # TAB7 'header' => [ 'track-name', 'track-artist', 'isrc', 'country', 'net-units', "net-earnings ($currencyCode)" ], 'format' => [ # 'text', 'text', 'text', 'text', 'number:#,###', 'number:#,##0.000000' 'text', 'text', 'text', 'text', kUnitsFormat, kEarningsFormat ] }, ); # ------------------------------ #die("reportDefinition: ". Dumper(\%gReportDefinition) ); my $gTotalSaleCount=0; my $gHighSaleCount=0; if ( $analyze ) { my $start = Time::HiRes::gettimeofday(); if ( !$gRunID ) { my $id = $statements[0]; my $sql = "SELECT artist_royalty_run_id FROM artist_royalty_statement WHERE artist_royalty_statement_id=$id"; my $sth = $dbo->DoCmd($sql); ($gRunID) = $sth->fetchrow_array(); } print STDERR "### Showing sale stats for run $gRunID\n"; foreach my $stmtID (@statements) { _showSaleStats( statement_id => $stmtID ); } my $end = Time::HiRes::gettimeofday(); my $totalTime = $end - $start; my $numStatements = (scalar @statements); # printf STDERR ( "### Analyzed %d ". (scalar @statements) . " statement(s) with $gTotalSaleCount sale(s)\n"; printf STDERR ( "### Analyzed %d statement(s) with %d sale(s) in %.2f seconds\n", $numStatements, $gTotalSaleCount, $totalTime ); printf STDERR "Maximum statement sales: $gHighSaleCount\n"; exit; } else { # Generate the custom report(s) (one per statement) foreach my $stmtID (@statements) { _processStatement( statement_id => $stmtID, filter_album_id => $filterAlbumID, summary => $summary, verbose => $verbose ); } } #my $term = RPS::DB::Item::NewArtistContractTerm->Lookup( artist_contract_term_id => $termID ); #die("INVALID TERM !!!") if ( !$term ); # #my $contractID = $term->artist_contract_id; #my $contract = RPS::DB::Item::NewArtistContract->Lookup( artist_contract_id => $contractID ); #my $cTitle = $contract->title; # # #my $payeeID = $contract->artist_payee_id; #my $payee = RPS::DB::Item::ArtistPayee->Lookup( artist_payee_id => $payeeID ); #my $pName = $payee->name; #print "\n"; #print "Payee '$pName' (id=$payeeID)\n"; #print " Contract '$cTitle' (id=$contractID)\n"; #print "\n"; sub _showSaleStats { my (%args) = @_; my $stmtID = $args{statement_id}; my $totalSales=0; # total number of sales for this statement my $sql = "SELECT artist_royalty_album_id FROM artist_royalty_album WHERE artist_royalty_statement_id=$stmtID"; my $sth = $dbo->DoCmd($sql); while( my( $raID ) = $sth->fetchrow_array() ) { my $sql2 = "SELECT artist_royalty_income_item_id FROM artist_royalty_income_item " . "WHERE artist_royalty_album_id=$raID"; my $sth2 = $dbo->DoCmd($sql2); while( my( $itemID ) = $sth2->fetchrow_array() ) { my $sql3 = "SELECT COUNT(*) FROM sale_run_map WHERE run_type='artr' AND statement_item_id=$itemID " . "AND status='paid'"; # $sql3 .= " LIMIT 10"; # XXX XXX XXX my $sth3 = $dbo->DoCmd($sql3); my($nsales) = $sth3->fetchrow_array(); _report( "ra($raID) item($itemID) nsales($nsales)", 2); # XXX $totalSales += $nsales; $gHighSaleCount = $nsales if ( $nsales > $gHighSaleCount ); } } _report( "stmt($stmtID) #sales($totalSales)", 1); # XXX $gTotalSaleCount += $totalSales; # running total across all statements } # _showSaleStats sub _processStatement { my (%args) = @_; my $stmtID = $args{statement_id}; my $filterAlbumID = $args{filter_album_id}; my $summary = $args{summary}; # Excel-related variables used for raw sales output; use a separate report since # it's only for debugging. # $rawSalesWorkbook = undef; $rawSalesWorksheet = undef; $rawSalesHeaderFormat = undef; $rawSalesPlainFormat = undef; $rawSalesRow = 0; $showRawSalesHeader = 0; print "### Analyzing statementID $stmtID ...\n"; my $statement = RPS::DB::Item::ArtistRoyaltyStatement->Lookup( artist_royalty_statement_id => $stmtID ); die("ERROR: statement $stmtID not found !!!") if ( !$statement ); my $runID = $statement->artist_royalty_run_id; my $payeeID = $statement->payee_id; my $payee = RPS::DB::Item::ArtistPayee->Lookup( artist_payee_id => $payeeID ); my $payeeName = $payee->name; my $payeeClientID = $payee->client_account_id; if ( !$gPayorName ) { my $run = RPS::DB::Item::ArtistRoyaltyRun->Lookup( artist_royalty_run_id => $runID ); my $payor = RPS::DB::Item::Payor->Lookup( payor_id => $run->payor_id ); $gPayorName = $payor->name; $gRunName = $run->label; } my $albumColl = RPS::DB::Item::ArtistRoyaltyAlbum->GetByArtistRoyaltyStatementID( $stmtID ); print "Found " . $albumColl->size . " royalty album(s)\n"; return if ( $albumColl->size == 0 ); # To get the sales that fed into the statement, we need to get the set # of artist_royalty_album entries. This will allow us to find the # income items, and from there we can get the sale_id's by looking in # sale_run_map. my @saleMap; # used for earnings by service report my $netUnits=0; my $netPrice=0; my $totalRevenue=0; while ( $albumColl->hasNext ) { my $royaltyAlbum = $albumColl->next; my $raID = $royaltyAlbum->artist_royalty_album_id; my $raAlbumID = $royaltyAlbum->album_id; my $raContractID = $royaltyAlbum->artist_contract_id; my $raUnitLevelIncome = $royaltyAlbum->unit_level_income; my $raNetRevenueIncome = $royaltyAlbum->net_revenue_income; if ( $filterAlbumID && $filterAlbumID != $raAlbumID ) { print ">> skipping albumID $raAlbumID ...\n"; next; } print "Looking at raAlbumID $raID ...\n"; my $aRef = _getAlbumInfo($raAlbumID); my $albumName = $aRef->{title}; my $catalogNo = $aRef->{catalog_number}; my $contract = _getContract($raContractID); my $cTitle = $contract->title; $albumTitleMap{$raAlbumID} = $albumName if ( !exists $albumTitleMap{$raAlbumID} ); # save this so we can sort later on my $itemColl = RPS::DB::Item::ArtistRoyaltyIncomeItem->GetByArtistRoyaltyAlbumID( $raID ); # Find the sale(s) that were aggregated into each statement line # my @itemLines; while ( $itemColl->hasNext ) { my $item = $itemColl->next; my $itemID = $item->artist_royalty_income_item_id; my $itemTrackID = $item->track_id; my $itemTotal = $item->total; my $usesDefaultNetRate = $item->uses_default_net_rate; my $rateTypeID = $item->contract_rate_type_id; my $netRate = $item->net_rate; my $reserveRate = $contract->reserve_rate; my $rateType = $rateTypeMap{$rateTypeID}; my $incomeSource = $incomeSourceMap{$item->income_source_id}; # Not sure why, but region_id stored with the income item will be zero (usually ROW) # if the associated contract term has NULL (All). We want the report to show the # term's region so that it matches the statement. my $termID = $item->artist_contract_term_id; my $term = RPS::DB::Item::NewArtistContractTerm->Lookup( artist_contract_term_id => $termID ); my $regionID = $term->region_id; my $region = (defined $regionID) ? $regionMap{$term->region_id} : 'All'; my $channel = $channelMap{$item->channel_id} || ''; my $priceLevel = $priceLevelMap{$item->price_level_id}; my $revenueLiquidated = $item->revenue_liquidated; my $trackName; my $isrc; if ( $itemTrackID ) { my $tRef = _getTrackInfo($itemTrackID); $trackName = $tRef->{title}; $isrc = $tRef->{isrc}; $trackTitleMap{$itemTrackID} = $trackName if ( ! exists $trackTitleMap{$itemTrackID} ); } # TODO TODO TODO TODO TODO TODO # # As an optimization, we should query multiple sales # at a time rather than a single sale at a time # # TODO TODO TODO TODO TODO TODO #print " Looking at itemID $itemID usesDefaultNetRate($usesDefaultNetRate) ... "; print " [$itemID] $incomeSource rg($region) ch($channel) pl($priceLevel) type($rateType) ...\n"; my $sql = "SELECT DISTINCT sale_id FROM sale_run_map WHERE run_type='artr' " . "AND run_id=$runID AND statement_item_id=$itemID AND status='paid'"; # $sql .= " LIMIT 1"; # XXX XXX XXX XXX my $sth = $dbo->DoCmd($sql); my $nSales = $sth->rows; print "found $nSales sale(s)\n"; my %seenServiceID; # keep track of unique country codes seen # When scanning the sales that feed into an income item, for digital net revenue sales we'll # calculate the net earnings on the individual sales and aggregate as we go along. For unit-based # physical, we can't easily calculate the individual net earnings on each sale due to reserves # and any associated rounding (e.g., we lose precision when calculating the earnings per sale # and then aggregating the earnings only exacerbates the situation). To account for the this, # we still round the reserve units per sale and then calculate an initial net earnings for that # sale. However, the final net earnings for that sale must be adjusted in relation to the other # sales feeding info the same income item. # # For example, with Cleo's income item 32814824, the income item total is 20.25. There are 27 # sales feeding into that item, and the net earnings for each sale is 1.0125. If we sum those up # we end up with 27.3375, which does not equal the item total. # Now, if we divide the item total by the sum of those net earnings, we can use that ratio to # adjust the individual net earnings such that the sum will indeed match the item total: # # 20.25 / 27.3375 = .7407 (ratio) # 1.0125 * .7407 = 0.75 (adjusted net earnings) # # The adjusted net earnings for each sale is then 0.75, and 27 * 0.75 = 20.25. # # Hence, for unit-based calculations we need to process the income item sales in two passes: # 1) Calculate the net earnings for each sale feeding into the income item, and keep a running # total of the sum of those net earnings (salesRevenue). Save the sale information along # with the net units and earnings into the 'saleArray' array. Unlike net revenue sales, # do NOT aggregate the sales into our custom report hashes; # # 2) Using the net earnings sum from the first pass: # - Calculate an adjustment by dividing the income item total by the sum of the net earnings. # - For each sale that we saved in the 1st pass, adjust the net earning of the sale by # multiplying the sale's net earning (calculated in the 1st pass) by the adjustment ratio. # - Aggregate the sale into our custom report hashes using the adjusted net earnings data. # # @saleArray is used for physical sale processing w/ unit-based royalties: # $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentWholesale # $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentRetail # $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentWholesale # $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRetail # $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypeFixed # my @saleArray; # $salesRevenue holds the sum of the physical item sales unadjusted net earnings (e.g., this value # might equal the income item total, however due to rounding issues it may be higher than the # income item total). # my $salesRevenue; while( my($saleID) = $sth->fetchrow_array() ) { my $sql = "SELECT file_id, IFNULL(sale.service_id, file.service_id) AS service_id, " . "product_type, format_type, album_name, upc, track_name, isrc, " . "sale.units, price, conversion_rate, sale.currency_code, country_code, product_id, " . "wholesale_price, retail_price, " . "sales, sales_revenue, returns, returns_revenue, sale.gross_revenue FROM sale " . "LEFT JOIN file USING (file_id) WHERE sale_id=$saleID"; my $sth = $dbo->DoCmd($sql); my @sArray = $sth->fetchrow_array(); my( $fileID, $serviceID, $pType, $formatType, $_albumName, $upc, $_trackName, $_isrc, $units, $price, $conversionRate, $currencyCode, $countryCode, $productID, $wholesalePrice, $retailPrice, $sales, $sRevenue, $returns, $rRevenue, $saleGrossRevenue ) = @sArray; _report("$saleID : s $serviceID u $units price $price -- sales $sales ($sRevenue) returns $returns ($rRevenue)", 2) if ( $verbose ); $seenServiceID{$serviceID} = 1; # FYI: # dbmaster\RSCOMMON>select * from contract_rate_type; # +-----------------------+----------------------+--------------------------------------+-------+---------------+ # | contract_rate_type_id | name | description | fixed | display_order | # +-----------------------+----------------------+--------------------------------------+-------+---------------+ # | 1 | % Retail | Percent of retail price | 0 | 1 | # | 2 | % Wholesale | Percent of wholesale price | 0 | 2 | # | 3 | % PPD | Percent of published price to dealer | 0 | 3 | # | 4 | % Avg | Percent of average price | 0 | 4 | # | 5 | % Net Revenue | Percent of net revenue | 0 | 6 | # | 6 | Fixed | Fixed price per unit | 1 | 7 | # | 7 | % Retail Document | Percent of retail document price | 0 | 8 | # | 8 | % Wholesale Document | Percent of wholesale document price | 0 | 9 | # | 9 | Non-payable | Not eligible for payment | 0 | 10 | # | 10 | % Gross Revenue | Percent of gross revenue | 0 | 5 | # +-----------------------+----------------------+--------------------------------------+-------+---------------+ # 10 rows in set (0.00 sec) # # use constant kRateTypePercentRetail => 1; # use constant kRateTypePercentWholesale => 2; # use constant kRateTypePercentPPD => 3; DEPRECATED # use constant kRateTypePercentAverage => 4; DEPRECATED # use constant kRateTypePercentRevenue => 5; # use constant kRateTypeFixed => 6; # use constant kRateTypePercentDocumentRetail => 7; # use constant kRateTypePercentDocumentWholesale => 8; # use constant kRateTypeNonPayable => 9; # use constant kRateTypePercentGrossRevenue => 10; # # Calculate revenue for this sale... # my $_units; # gross units my $_revenue; my $reserved; # for unit reserves my $reserveRevenue; # for net rev reserves my $netUnits; my $grossRevenue; my $netEarnings; if ( $pType =~ /(A|T)/ ) { # digital sales $netUnits = $units; if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentGrossRevenue ) { $grossRevenue = $saleGrossRevenue * $conversionRate; } else { $grossRevenue = $units * $price * $conversionRate; } $netEarnings = $grossRevenue; # adjust _revenue to reflect the applicable statement item $netEarnings *= ( $netRate / 100 ); } else { # physical sales # XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX # # 9/16/21 - For physical sales in a unit-based income item, we need to defer # the final revenue calculation for each sale until we've seen all physical # sales that will apply to the income item. # # XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX my $needToAdjustRevenue; # FLAG: if set, use 2nd pass to adjust revenue before custom aggregation $_units = $sales - $returns; $netUnits = $_units; if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentGrossRevenue ) { $grossRevenue = $saleGrossRevenue * $conversionRate; } else { $grossRevenue = ($sRevenue - $rRevenue) * $conversionRate; } # The net earnings will be unit or revenue-based, and has to # be calculated for the sale based on the information in the # artist_royalty_income_item that the sale aggregated into. # if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentWholesale || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentRetail || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentWholesale || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRetail || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypeFixed ) { # XXX unit-based royalties XXX # For these, the net rate has the price, percentage of sales, pkg deduction. # already factored in. # Take the gross units and substract reserves $netUnits = $sales; if ( $sales > 0 ) { # The reserved units are calculated per sale and rounded here. This is # different than the statement income item -- on the statement, we aggregate # the sales into the income item and then calculate reserve units. Because # this report is aggregating sales on a custom basis, we need to calculate # reserves per sale. However, by calculating individual sale reserves you # can run into issues when trying to manually verify the income item totals # using the revenue information shown on a raw sales report. We get around # this by adjusting each sale's revenue information using a two-pass approach # as outlined above. # $reserved = ($reserveRate/100) * $sales; $reserved = Common::RSMath::round( $reserved, 0 ); $netUnits -= $reserved; } $netUnits -= $returns; $netEarnings = $netUnits * $netRate; $needToAdjustRevenue = 1; } elsif ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRevenue || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentGrossRevenue ) { # take reserves off revenue $reserveRevenue = ($reserveRate/100) * $grossRevenue; $netEarnings = $grossRevenue - $reserveRevenue; $netEarnings *= ( $netRate / 100 ); # Need to account for liquidations, so defer the aggregation to the 2nd pass $needToAdjustRevenue = 1; } elsif ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypeNonPayable ) { print STDERR "detected physical sale with nonpayable rateTypeID $rateTypeID -- skipping !!!\n"; # XXX next; } else { die("detected physical sale with unexpected rateTypeID $rateTypeID !!!"); # XXX TODO } if ( $needToAdjustRevenue ) { $salesRevenue += $netEarnings; push @sArray, $saleID; push @sArray, $netUnits; push @sArray, $netEarnings; push @sArray, $grossRevenue; push @sArray, $_units; # gross units push @sArray, $reserved; # units reserved push @sArray, $reserveRevenue; push @saleArray, \@sArray; next; # 9/16/21 - skip (defer) sale reporting and aggregation to 2nd pass below } } # The following code is processed for digital income items or physical net revenue income # items. We do something similar for unit-based physical income items, but only after # adjusting the sale revenue as needed. # _printRawSales( sale_data => \@sArray, sale_id => $saleID, artist_royalty_statement_id => $stmtID, artist_royalty_album_id => $raID, artist_royalty_income_item_id => $itemID, album_name => $albumName, catalog_number => $catalogNo, track_name => $trackName, isrc => $isrc, contract_title => $cTitle, item_total => $itemTotal, #gross_revenue => $_revenue, gross_revenue => $grossRevenue, gross_sales => $_units, # gross units reserved => $reserved, net_units => $netUnits, rate_type_id => $rateTypeID, net_rate => $netRate, # total => $totalRevenue, #rate_type => $rateType, income_source => $incomeSource, region => $region, channel => $channel, price_level => $priceLevel, reserve_units => $reserved, reserve_rate => $reserveRate, reserve_revenue => $reserveRevenue, total_revenue => $netEarnings, # XXX CHARLIE ) if ( $gRawSales ); # Aggregate data for Earnings by Service report # die("Missing service_id for sale $saleID !!!") if ( !$serviceID ); # TODO this may come from file _or_ sale $serviceMap{$serviceID}{net_units} += $netUnits; $serviceMap{$serviceID}{net_earnings} += $netEarnings; # Aggregate data for Earnings by Country report # $earningsByCountry{$countryCode}{net_units} += $netUnits; $earningsByCountry{$countryCode}{net_earnings} += $netEarnings; # Aggregate data for Earnings by Format report # # NOTE: we use a combo product/format key to allow # breakdown of sales by physical _and_ digital formats. # my $pfKey = join("\t", $pType, $formatType); # $earningsByFormat{$pfKey}{net_units} += $_units; # $earningsByFormat{$pfKey}{net_earnings} += $_revenue; my $formatname; # digital format _or_ physical product type if ( $pType !~ /^(a|t)$/i ) { $formatname = RPS::File::Sale::GetProductType($pType); } else { #$formatname = _getFormatName($formatType);; $formatname = RPS::File::Sale::GetFormatType($formatType); } $earningsByFormat{$formatname}{net_units} += $netUnits; $earningsByFormat{$formatname}{net_earnings} += $netEarnings; # Aggregate data for Earnings by Album by Service (and Country) reports # Note: these will include track units/revenue, if available # $earningsByAlbumByService{$raAlbumID}{$serviceID}{net_units} += $netUnits; $earningsByAlbumByService{$raAlbumID}{$serviceID}{net_earnings} += $netEarnings; $earningsByAlbumByCountry{$raAlbumID}{$countryCode}{net_units} += $netUnits; $earningsByAlbumByCountry{$raAlbumID}{$countryCode}{net_earnings} += $netEarnings; if ( $itemTrackID ) { $earningsByTrackByService{$itemTrackID}{$serviceID}{net_units} += $netUnits; $earningsByTrackByService{$itemTrackID}{$serviceID}{net_earnings} += $netEarnings; $earningsByTrackByCountry{$itemTrackID}{$countryCode}{net_units} += $netUnits; $earningsByTrackByCountry{$itemTrackID}{$countryCode}{net_earnings} += $netEarnings; } $totalRevenue += $netEarnings; } # sale loop if ( @saleArray ) { # process physical sales for unit-based royalties ... print "Processing physical sales for unit-based royalties ...\n"; # For unit-based calculations # if no sales revenue then we don't need to be adjusting anything my $coeff = ($salesRevenue) ? ($itemTotal / $salesRevenue) : 1; # For revenue-based calculations my $netRevLiquidated = $revenueLiquidated / (scalar @saleArray); if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentWholesale || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentRetail || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentWholesale || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRetail || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypeFixed ) { print "PASS2 (unit-based): itemID $itemID itemTotal($itemTotal) / salesRevenue($salesRevenue) = coeef($coeff)\n"; # XXX } elsif( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRevenue || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentGrossRevenue ) { my $nSales = scalar @saleArray; print "PASS2 (revenue-based): itemID $itemID revenueLiquidated($revenueLiquidated) / " . "nSales($nSales) = netRevLiquidated($netRevLiquidated)\n"; # XXX } foreach my $sArray (@saleArray) { my( $fileID, $serviceID, $pType, $formatType, $_albumName, $upc, $_trackName, $_isrc, $units, $price, $conversionRate, $currencyCode, $countryCode, $productID, $wholesalePrice, $retailPrice, $sales, $sRevenue, $returns, $rRevenue, $saleGrossRevenue, $saleID, $netUnits, $_netEarnings, $grossRevenue, $_units, $reserved, $reserveRevenue ) = @$sArray; #print "PASS2: D: sArray = ". Dumper(@$sArray) . "\n"; my $netEarnings; if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentWholesale || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentRetail || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentWholesale || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRetail || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypeFixed ) { $netEarnings = $_netEarnings * $coeff; # adjust based on sum of all sales net earnings w.r.t. item total } elsif( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRevenue || $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentGrossRevenue ) { $netEarnings = $grossRevenue - $reserveRevenue + $netRevLiquidated; $netEarnings *= ( $netRate / 100 ); } # _report(" PASS2: $saleID : netEarnings($_netEarnings) -> adjEarnings($netEarnings) " # . "s $serviceID u $units price $price -- sales $sales ($sRevenue) returns $returns ($rRevenue)"); # Aggregate sales ... _printRawSales( #sale_data => \@sArray, sale_data => $sArray, # this is why we appended to sArray.. sale_id => $saleID, artist_royalty_statement_id => $stmtID, artist_royalty_album_id => $raID, artist_royalty_income_item_id => $itemID, album_name => $albumName, catalog_number => $catalogNo, track_name => $trackName, isrc => $isrc, contract_title => $cTitle, item_total => $itemTotal, #gross_revenue => $_revenue, gross_revenue => $grossRevenue, gross_sales => $_units, # gross units reserved => $reserved, net_units => $netUnits, rate_type_id => $rateTypeID, net_rate => $netRate, # total => $totalRevenue, #rate_type => $rateType, income_source => $incomeSource, region => $region, channel => $channel, price_level => $priceLevel, reserve_units => $reserved, reserve_rate => $reserveRate, reserve_revenue => $reserveRevenue, total_revenue => $netEarnings, ) if ( $gRawSales ); # Aggregate data for Earnings by Service report # die("Missing service_id !!!") if ( !$serviceID ); # TODO this may come from file _or_ sale $serviceMap{$serviceID}{net_units} += $netUnits; $serviceMap{$serviceID}{net_earnings} += $netEarnings; # Aggregate data for Earnings by Country report # $earningsByCountry{$countryCode}{net_units} += $netUnits; $earningsByCountry{$countryCode}{net_earnings} += $netEarnings; # Aggregate data for Earnings by Format report # my $formatname; # digital format _or_ physical product type if ( $pType !~ /^(a|t)$/i ) { $formatname = RPS::File::Sale::GetProductType($pType); } else { #$formatname = _getFormatName($formatType);; $formatname = RPS::File::Sale::GetFormatType($formatType); } $earningsByFormat{$formatname}{net_units} += $netUnits; $earningsByFormat{$formatname}{net_earnings} += $netEarnings; # Aggregate data for Earnings by Album by Service (and Country) reports # Note: these will include track units/revenue, if available # $earningsByAlbumByService{$raAlbumID}{$serviceID}{net_units} += $netUnits; $earningsByAlbumByService{$raAlbumID}{$serviceID}{net_earnings} += $netEarnings; $earningsByAlbumByCountry{$raAlbumID}{$countryCode}{net_units} += $netUnits; $earningsByAlbumByCountry{$raAlbumID}{$countryCode}{net_earnings} += $netEarnings; if ( $itemTrackID ) { $earningsByTrackByService{$itemTrackID}{$serviceID}{net_units} += $netUnits; $earningsByTrackByService{$itemTrackID}{$serviceID}{net_earnings} += $netEarnings; $earningsByTrackByCountry{$itemTrackID}{$countryCode}{net_units} += $netUnits; $earningsByTrackByCountry{$itemTrackID}{$countryCode}{net_earnings} += $netEarnings; } $totalRevenue += $netEarnings; } # physical saleArray loop } # 2nd pass for physical unit-based royalties # load up serviceNameMap, if not already done (if _printRawSales was # called then this is redundant) foreach my $serviceID ( keys %seenServiceID ) { my $sname = _getServiceName($serviceID); } undef @saleArray; undef $salesRevenue; } # artist_royalty_income_item loop # XXX If you want to display the raw sales feeding into a statement line, this would # XXX be a good place to do so. Otherwise we can examine the full saleMap after looking # XXX at all of the artist_royalty_album / artist_royalty_income_item entries. print "\n"; undef $itemColl; }# artist_royalty_album loop if ( $rawSalesWorkbook ) { $rawSalesWorkbook->close(); } my $statementData = RPS::DB::Item::ArtistRoyaltyStatement->Lookup( artist_royalty_statement_id => $stmtID ); my $prettyFileName = RPS::Statement::Artist::Excel::PrettyStatementFileNameFromDBItem($statementData); my $reportExcel = 'Earnings__' . $prettyFileName; my $workbook; if ( $gMakeExcel ) { $workbook = Excel::Writer::XLSX->new($reportExcel); # Setup the worksheet(s) # if ( $gEarningsByService ) { $gWorksheet{earningsByService} = $workbook->add_worksheet('Earnings by Service'); $gWorksheet{earningsByService}->freeze_panes(6, 0); } if ( $gEarningsByCountry ) { $gWorksheet{earningsByCountry} = $workbook->add_worksheet('Earnings by Country'); $gWorksheet{earningsByCountry}->freeze_panes(6, 0); } if ( $gEarningsByFormat ) { $gWorksheet{earningsByFormat} = $workbook->add_worksheet('Earnings by Format'); $gWorksheet{earningsByFormat}->freeze_panes(6, 0); } if ( $gEarningsByAlbumByService ) { $gWorksheet{earningsByAlbumByService} = $workbook->add_worksheet('Earnings by Album By Service'); $gWorksheet{earningsByAlbumByService}->freeze_panes(6, 0); } if ( $gEarningsByAlbumByCountry ) { $gWorksheet{earningsByAlbumByCountry} = $workbook->add_worksheet('Earnings by Album By Country'); $gWorksheet{earningsByAlbumByCountry}->freeze_panes(6, 0); } if ( $gEarningsByTrackByService ) { $gWorksheet{earningsByTrackByService} = $workbook->add_worksheet('Earnings by Track By Service'); $gWorksheet{earningsByTrackByService}->freeze_panes(6, 0); } if ( $gEarningsByTrackByCountry ) { $gWorksheet{earningsByTrackByCountry} = $workbook->add_worksheet('Earnings by Track By Country'); $gWorksheet{earningsByTrackByCountry}->freeze_panes(6, 0); } # Setup the global formats (used for formatting the header pane) # Any worksheet-specific formats will be created later within # each worksheet. # $gFormat{header} = $workbook->add_format(); my $headerFillColor = $workbook->set_custom_color(23,216,216,216); $gFormat{header}->set_bg_color($headerFillColor); $gFormat{header}->set_bold(); $gFormat{header}->set_size(kExcelFontSize); $gFormat{header}->set_text_wrap(); $gFormat{header}->set_bottom(2); # continuous (weight 2) $gFormat{header}->set_left(1); # continuous (weight 1) $gFormat{header}->set_right(1); # continuous (weight 1) $gFormat{header}->set_top(1); # continuous (weight 1) $gFormat{bold} = $workbook->add_format(); $gFormat{bold}->set_bold(); $gFormat{bold}->set_size(kExcelFontSize); #$gFormat{bold}->set_text_wrap(); $gFormat{plain} = $workbook->add_format(); $gFormat{plain}->set_size(kExcelFontSize); #$plainFormat->set_text_wrap(); } if ( $gEarningsByService ) { my $reportType = 'earningsByService'; $gRowIndex{$reportType} = 0; # Calculate the calculate the % of total for each service # foreach my $serviceID ( keys { %serviceMap } ) { my $serviceRevenue = $serviceMap{$serviceID}{net_earnings}; $serviceMap{$serviceID}{percent_of_total} = ($serviceRevenue / $totalRevenue) * 100; my $serviceName = _getServiceName($serviceID);; $serviceMap{$serviceID}{service_name} = $serviceName; } # Generate report showing earnings by service # foreach my $serviceID ( sort _serviceSort keys { %serviceMap } ) { my $serviceName = $serviceMap{$serviceID}{service_name}; my $netUnits = $serviceMap{$serviceID}{net_units}; my $netEarnings = $serviceMap{$serviceID}{net_earnings}; my $percentTotal = $serviceMap{$serviceID}{percent_of_total}; $percentTotal /= 100; # this will be formatted as percentage, which will multiply this by 100... my @rowData = ( # data order _must_ match gRowDefinition $serviceName, # "service-name", # A $netUnits, # "net-units", # B $netEarnings, # "net-earnings", # C $percentTotal, # "% of total", # D ); # Depending on value of gMakeExcel, outputData will output tab-delimited or Excel # $gRowIndex{$reportType} = outputData( report_type => $reportType, data => \@rowData, workbook => $workbook, payee_name => $payeeName, payee_client_id => $payeeClientID, # worksheet => $worksheet,header_format => $headerFormat, # bold_format => $boldFormat, plain_format => $plainFormat, ); } # serviceMap loop undef %serviceMap; } # earningsByService report if ( $gEarningsByCountry ) { my $reportType = 'earningsByCountry'; $gRowIndex{$reportType} = 0; # Calculate the calculate the % of total for each country # foreach my $country ( keys { %earningsByCountry } ) { # note: keys are country codes my $countryRevenue = $earningsByCountry{$country}{net_earnings}; $earningsByCountry{$country}{percent_of_total} = ($countryRevenue / $totalRevenue) * 100; my $countryName = _getCountryName($country);; $earningsByCountry{$country}{country_name} = $countryName; } foreach my $country ( sort _earningsByCountrySort keys { %earningsByCountry } ) { my $countryName = $earningsByCountry{$country}{country_name}; my $netUnits = $earningsByCountry{$country}{net_units}; my $netEarnings = $earningsByCountry{$country}{net_earnings}; my $percentTotal = $earningsByCountry{$country}{percent_of_total}; $percentTotal /= 100; # this will be formatted as percentage, which will multiply this by 100... my @rowData = ( # data order _must_ match gRowDefinition $countryName, # "country", # A $netUnits, # "net-units", # B $netEarnings, # "net-earnings", # C $percentTotal, # "% of total", # D ); # Depending on value of gMakeExcel, outputData will output tab-delimited or Excel # $gRowIndex{$reportType} = outputData( report_type => $reportType, data => \@rowData, workbook => $workbook, payee_name => $payeeName, payee_client_id => $payeeClientID, ); } # earningsByCountry loop undef %earningsByCountry; } # earningsByCountry report if ( $gEarningsByFormat ) { my $reportType = 'earningsByFormat'; $gRowIndex{$reportType} = 0; # 1st pass: Calculate the calculate the % of total for each country # foreach my $formatType ( keys { %earningsByFormat } ) { my $formatRevenue = $earningsByFormat{$formatType}{net_earnings}; $earningsByFormat{$formatType}{percent_of_total} = ($formatRevenue / $totalRevenue) * 100; } # 2nd pass: Generate report showing earnings by format # foreach my $formatType ( sort _earningsByFormatSort keys { %earningsByFormat } ) { my $formatName = $earningsByFormat{$formatType}{format_name}; my $netUnits = $earningsByFormat{$formatType}{net_units}; my $netEarnings = $earningsByFormat{$formatType}{net_earnings}; my $percentTotal = $earningsByFormat{$formatType}{percent_of_total}; $percentTotal /= 100; # this will be formatted as percentage, which will multiply this by 100... my @rowData = ( # data order _must_ match gRowDefinition $formatType, # "product-format", # A $netUnits, # "net-units", # B $netEarnings, # "net-earnings", # C $percentTotal, # "% of total", # D ); # Depending on value of gMakeExcel, outputData will output tab-delimited or Excel # $gRowIndex{$reportType} = outputData( report_type => $reportType, data => \@rowData, workbook => $workbook, payee_name => $payeeName, payee_client_id => $payeeClientID, ); } # earningsByFormat loop undef %earningsByFormat; } # earningsByFormat report if ( $gEarningsByAlbumByService ) { my $reportType = 'earningsByAlbumByService'; $gRowIndex{$reportType} = 0; print "earningsByAlbumByServiceMap: ". Dumper(\%earningsByAlbumByService) . "\n"; # XXX # Generate report showing earnings by album by service foreach my $albumID ( sort _albumTitleSort keys { %earningsByAlbumByService } ) { my $aRef = _getAlbumInfo($albumID); my $albumName = $aRef->{title}; my $albumArtist = $aRef->{artist}; my $catalogNo = $aRef->{catalog_number}; my $albumServiceMap = $earningsByAlbumByService{$albumID}; # For each album, we only want to list the top 10 services, # followed by an 'other' category containing a summary of the remaining # non-top-10 services. my $serviceCount=1; my %otherData; my $numServices = keys %$albumServiceMap; print "looking at album $albumID ...\n"; # XXX foreach my $serviceID ( sort { _revenueSort($a, $b, \%$albumServiceMap) } keys %$albumServiceMap ) { my $serviceName = _getServiceName($serviceID);; my $netUnits = $albumServiceMap->{$serviceID}{net_units}; my $netEarnings = $albumServiceMap->{$serviceID}{net_earnings}; if ( $serviceCount <= 10 ) { my @rowData = ( # data order _must_ match gRowDefinition $albumName, $albumArtist, $catalogNo, $serviceName, $netUnits, $netEarnings, ); # Underline the last row for the album if there are 10 or less services, # otherwise we'll underline after the "Other" summary row my $underline = 1 if ( $serviceCount == $numServices ); # Depending on value of gMakeExcel, outputData will output tab-delimited or Excel # $gRowIndex{$reportType} = outputData( report_type => $reportType, data => \@rowData, workbook => $workbook, payee_name => $payeeName, payee_client_id => $payeeClientID, underline => $underline ); } else { $otherData{net_units} += $netUnits; $otherData{net_earnings} += $netEarnings; } ++$serviceCount; } # service loop if ( %otherData ) { my @rowData = ( # data order _must_ match gRowDefinition $albumName, $albumArtist, $catalogNo, "Other", # $serviceName, $otherData{net_units}, # $netUnits, $otherData{net_earnings}, # $netEarnings, ); $gRowIndex{$reportType} = outputData( report_type => $reportType, data => \@rowData, workbook => $workbook, payee_name => $payeeName, payee_client_id => $payeeClientID, underline => 1, # XXX NEW ); } } # earningsByAlbumByService loop undef %earningsByAlbumByService; } # earningsByAlbumByService report if ( $gEarningsByAlbumByCountry ) { my $reportType = 'earningsByAlbumByCountry'; $gRowIndex{$reportType} = 0; print "earningsByAlbumByCountryMap: ". Dumper(\%earningsByAlbumByCountry) . "\n"; # XXX # Generate report showing earnings by track by country foreach my $albumID ( sort _albumTitleSort keys { %earningsByAlbumByCountry } ) { my $aRef = _getAlbumInfo($albumID); my $albumName = $aRef->{title}; my $albumArtist = $aRef->{artist}; my $catalogNo = $aRef->{catalog_number}; my $albumCountryMap = $earningsByAlbumByCountry{$albumID}; # For each album, we only want to list the top 10 countries, followed # by an 'other' category containing a summary of the remaining countries. my $countryCount=1; my %otherData; my $numCountries = keys %$albumCountryMap; print "looking at albumID $albumID title($albumName) artist($albumArtist) catno($catalogNo)...\n"; # XXX foreach my $countryCode ( sort { _revenueSort($a, $b, \%$albumCountryMap) } keys %$albumCountryMap ) { my $countryName = _getCountryName($countryCode); my $netUnits = $albumCountryMap->{$countryCode}{net_units}; my $netEarnings = $albumCountryMap->{$countryCode}{net_earnings}; print " looking at country $countryName ...\n"; # XXX if ( $countryCount < 10 ) { my @rowData = ( # data order _must_ match gRowDefinition $albumName, $albumArtist, $catalogNo, $countryName, $netUnits, $netEarnings, ); # Underline the last row for the album if there are 10 or less services, # otherwise we'll underline after the "Other" summary row my $underline = 1 if ( $countryCount == $numCountries ); # Depending on value of gMakeExcel, outputData will output tab-delimited or Excel # $gRowIndex{$reportType} = outputData( report_type => $reportType, data => \@rowData, workbook => $workbook, payee_name => $payeeName, payee_client_id => $payeeClientID, underline => $underline ); } else { $otherData{net_units} += $netUnits; $otherData{net_earnings} += $netEarnings; } ++$countryCount; } # country loop if ( %otherData ) { my @rowData = ( # data order _must_ match gRowDefinition $albumName, $albumArtist, $catalogNo, "Other", # $countryName, $otherData{net_units}, $otherData{net_earnings}, ); # Depending on value of gMakeExcel, outputData will output tab-delimited or Excel # $gRowIndex{$reportType} = outputData( report_type => $reportType, data => \@rowData, workbook => $workbook, payee_name => $payeeName, payee_client_id => $payeeClientID, underline => 1 ); } } # earningsByAlbumByCountry loop undef %earningsByAlbumByCountry; } # earningsByAlbumByCountry report if ( $gEarningsByTrackByService ) { my $reportType = 'earningsByTrackByService'; $gRowIndex{$reportType} = 0; print "earningsByTrackByServiceMap: ". Dumper(\%earningsByTrackByService) . "\n"; # XXX # Generate report showing earnings by track by service foreach my $trackID ( sort _trackTitleSort keys { %earningsByTrackByService } ) { my $tRef = _getTrackInfo($trackID); my $trackName = $tRef->{title}; my $trackArtist = $tRef->{artist}; my $isrc = $tRef->{isrc}; my $trackServiceMap = $earningsByTrackByService{$trackID}; # For each track, we only want to list the top 10 services, # followed by an 'other' category containing a summary of the remaining # non-top-10 services. my $serviceCount=1; my %otherData; my $numServices = keys %$trackServiceMap; print "looking at trackID $trackID title($trackName) artist($trackArtist) isrc($isrc)...\n"; # XXX foreach my $serviceID ( sort { _revenueSort($a, $b, \%$trackServiceMap) } keys %$trackServiceMap ) { my $serviceName = _getServiceName($serviceID);; my $netUnits = $trackServiceMap->{$serviceID}{net_units}; my $netEarnings = $trackServiceMap->{$serviceID}{net_earnings}; print " looking at service $serviceName [$serviceID] ...\n"; # XXX if ( $serviceCount <= 10 ) { my @rowData = ( # data order _must_ match gRowDefinition $trackName, $trackArtist, $isrc, $serviceName, $netUnits, $netEarnings, ); # Underline the last row for the album if there are 10 or less services, # otherwise we'll underline after the "Other" summary row my $underline = 1 if ( $serviceCount == $numServices ); # Depending on value of gMakeExcel, outputData will output tab-delimited or Excel # $gRowIndex{$reportType} = outputData( report_type => $reportType, data => \@rowData, workbook => $workbook, payee_name => $payeeName, payee_client_id => $payeeClientID, underline => $underline ); } else { $otherData{net_units} += $netUnits; $otherData{net_earnings} += $netEarnings; } ++$serviceCount; } # service loop if ( %otherData ) { my @rowData = ( # data order _must_ match gRowDefinition $trackName, $trackArtist, $isrc, "Other", # $serviceName, $otherData{net_units}, # $netUnits, $otherData{net_earnings}, # $netEarnings, ); $gRowIndex{$reportType} = outputData( report_type => $reportType, data => \@rowData, workbook => $workbook, payee_name => $payeeName, payee_client_id => $payeeClientID, underline => 1 ); } } # earningsByTrackByService loop undef %earningsByTrackByService; } # earningsByTrackByService report if ( $gEarningsByTrackByCountry ) { my $reportType = 'earningsByTrackByCountry'; $gRowIndex{$reportType} = 0; print "earningsByTrackByCountryMap: ". Dumper(\%earningsByTrackByCountry) . "\n"; # XXX # Generate report showing earnings by track by service foreach my $trackID ( sort _trackTitleSort keys { %earningsByTrackByCountry } ) { my $tRef = _getTrackInfo($trackID); my $trackName = $tRef->{title}; my $trackArtist = $tRef->{artist}; my $isrc = $tRef->{isrc}; my $trackCountryMap = $earningsByTrackByCountry{$trackID}; # For each track, we only want to list the top 10 countries, followed # by an 'other' category containing a summary of the remaining countries. my $countryCount=1; my %otherData; my $numCountries = keys %$trackCountryMap; print "looking at trackID $trackID title($trackName) artist($trackArtist) isrc($isrc)...\n"; # XXX # foreach my $countryCode ( sort { $a cmp $b } keys %$countryMap ) { foreach my $countryCode ( sort { _revenueSort($a, $b, \%$trackCountryMap) } keys %$trackCountryMap ) { my $countryName = _getCountryName($countryCode); my $netUnits = $trackCountryMap->{$countryCode}{net_units}; my $netEarnings = $trackCountryMap->{$countryCode}{net_earnings}; if ( $countryCount < 10 ) { my @rowData = ( # data order _must_ match gRowDefinition $trackName, $trackArtist, $isrc, $countryName, $netUnits, $netEarnings, ); # Underline the last row for the album if there are 10 or less services, # otherwise we'll underline after the "Other" summary row my $underline = 1 if ( $countryCount == $numCountries ); # Depending on value of gMakeExcel, outputData will output tab-delimited or Excel # $gRowIndex{$reportType} = outputData( report_type => $reportType, data => \@rowData, workbook => $workbook, payee_name => $payeeName, payee_client_id => $payeeClientID, underline => $underline ); } else { $otherData{net_units} += $netUnits; $otherData{net_earnings} += $netEarnings; } ++$countryCount; } # country loop if ( %otherData ) { my @rowData = ( # data order _must_ match gRowDefinition $trackName, $trackArtist, $isrc, "Other", # $countryName, $otherData{net_units}, # $netUnits, $otherData{net_earnings}, # $netEarnings, ); # Depending on value of gMakeExcel, outputData will output tab-delimited or Excel # $gRowIndex{$reportType} = outputData( report_type => $reportType, data => \@rowData, workbook => $workbook, payee_name => $payeeName, payee_client_id => $payeeClientID, underline => 1 ); } } # earningsByTrackByCountry loop undef %earningsByTrackByCountry; } # earningsByTrackByCountry report $workbook->close() if ( $workbook ); undef %gFormat; undef %gRowIndex; undef %gColumnFormat; } # _processStatement # # Boring script stuff below... # # output data row, prepend header (once) sub outputData { my ( %args ) = @_; my $reportType = $args{report_type}; my $rowDataRef = $args{data}; my $workbook = $args{workbook}; my $worksheet = $gWorksheet{$reportType}; my $boldFormat = $args{bold_format}; my $plainFormat = $args{plain_format}; my $headerFormat = $args{header_format}; my $payeeName = $args{payee_name}; my $payeeClientID = $args{payee_client_id}; my $underline = $args{underline}; my $rowIndex = $gRowIndex{$reportType}; if ( !$gMakeExcel ) { # The non-Excel report is a tab-delimited file, with the first column # consisting of a report tag. By saving all of the output to a text file, # you can then grep out the report tag to extract the desired report. # E.g., grep REPORT_BYSERVICE debug.txt | \ # sed -e 's/REPORT_BY_SERVICE:\t//' > report_earnings_by_service.txt # my $reportTag = uc $reportType; $reportTag =~ s/EARNINGS/REPORT_/; my $header = \@{$gReportDefinition{$reportType}{header}}; print join("\t", $reportTag . ':', @$header) . "\n" if ( !$gShowHeader{$reportType}++ ); print join("\t", $reportTag . ':', @$rowDataRef) . "\n"; } else { if ( !$gShowHeader{$reportType}++ ) { # print "D: before printExcelHeaderCommon: row = $gRowIndex{$reportType}\n"; $gRowIndex{$reportType} = printExcelHeaderCommon( payee_name => $payeeName, payee_client_id => $payeeClientID, report_type => $reportType, # row_index => $gRowIndex{$reportType}, ); # print "D: after printExcelHeaderCommon: row = $gRowIndex{$reportType}\n"; $gRowIndex{$reportType}++; # add a blank row $gRowIndex{$reportType} = printExcelHeader( report_type => $reportType, # row_index => $gRowIndex{$reportType}, ); } $gRowIndex{$reportType} = printExcelRowData( report_type => $reportType, data => $rowDataRef, workbook => $workbook, worksheet => $worksheet, underline => $underline, #row_index => $gRowIndex{$reportType}, # XXX modify printRowData to access gRowIndex directly ); } } # outputData sub printExcelRowData { my ( %args ) = @_; my $reportType = $args{report_type}; my $rowArray = $args{data}; my $workbook = $args{workbook}; my $underline = $args{underline}; # my $worksheet = $args{worksheet}; # my $rowIndex = $args{row_index}; die("ERROR: invalid report type '$reportType' !!!!") if ( !exists $gReportDefinition{$reportType} ); my $worksheet = $gWorksheet{$reportType}; my $rowFormat = \@{$gReportDefinition{$reportType}{format}}; my $rowIndex = $gRowIndex{$reportType}; my $col = 0; foreach my $fmt ( @$rowFormat ) { my $rowCell = @$rowArray[$col]; my $cellType = ($fmt eq 'text') ? $fmt : 'number'; # formatName is just our internal key for the desired Excel format. # By default it's just the format code from the report definition hash # but we can augment it as needed. my $formatName = $fmt; $formatName .= "_underline" if ( $underline ); if ( !exists($gColumnFormat{$reportType}{$col}{$formatName}) ) { $gColumnFormat{$reportType}{$col}{$formatName} = $workbook->add_format(); $gColumnFormat{$reportType}{$col}{$formatName}->set_size(kExcelFontSize); if ( $fmt =~ /^number:([#0,\.%]+)/ ) { # print STDERR "col($col) Detected fmt '$fmt'\n"; # XXX my $format = $1; $gColumnFormat{$reportType}{$col}{$formatName}->set_num_format( $format ); # XXX } if ( $underline ) { $gColumnFormat{$reportType}{$col}{$formatName}->set_bottom(1); # single bottom border } } printField( row => $rowIndex, column => $col, cell_data => $rowCell, cell_type => $cellType, format => $gColumnFormat{$reportType}{$col}{$formatName}, worksheet => $worksheet ); $col++; } # column loop $rowIndex++; return $rowIndex; } # printRowData sub printExcelHeaderCommon { my ( %args ) = @_; my $payeeName = $args{payee_name}; my $payeeClientID = $args{payee_client_id}; my $reportType = $args{report_type}; die("ERROR: invalid report type '$reportType' !!!!") if ( !exists $gReportDefinition{$reportType} ); # my $plainFormat = $args{plain_format}; # my $boldFormat = $args{bold_format}; # my $worksheet = $args{worksheet}; # my $rowIndex = $args{row_index}; my $plainFormat = $gFormat{plain}; my $boldFormat = $gFormat{bold}; my $worksheet = $gWorksheet{$reportType}; my $rowIndex = $gRowIndex{$reportType}; # output the pre-header containing the run/payee info printField( row => $rowIndex, column => 0, cell_data => 'Payor:', cell_type => 'text', format => $boldFormat, worksheet => $worksheet ); printField( row => $rowIndex, column => 1, cell_data => $gPayorName, cell_type => 'text', format => $plainFormat, worksheet => $worksheet ); $rowIndex++; printField( row => $rowIndex, column => 0, cell_data => 'Royalty Run Name:', cell_type => 'text', format => $boldFormat, worksheet => $worksheet ); printField( row => $rowIndex, column => 1, cell_data => $gRunName, cell_type => 'text', format => $plainFormat, worksheet => $worksheet ); $rowIndex++; printField( row => $rowIndex, column => 0, cell_data => 'Payee Name:', cell_type => 'text', format => $boldFormat, worksheet => $worksheet ); printField( row => $rowIndex, column => 1, cell_data => $payeeName, cell_type => 'text', format => $plainFormat, worksheet => $worksheet ); $rowIndex++; printField( row => $rowIndex, column => 0, cell_data => 'Client #:', cell_type => 'text', format => $boldFormat, worksheet => $worksheet ); printField( row => $rowIndex, column => 1, cell_data => $payeeClientID, cell_type => 'text', format => $plainFormat, worksheet => $worksheet ); $rowIndex++; return $rowIndex; } # printExcelHeaderCommon sub printExcelHeader { my ( %args ) = @_; my $reportType = $args{report_type}; # my $headerFormat = $args{header_format}; # my $worksheet = $args{worksheet}; # my $rowIndex = $args{row_index}; die("ERROR: invalid report type '$reportType' !!!!") if ( !exists $gReportDefinition{$reportType} ); my $headerFormat = $gFormat{header}; my $worksheet = $gWorksheet{$reportType}; my $rowIndex = $gRowIndex{$reportType}; my $header = \@{$gReportDefinition{$reportType}{header}}; # print "header = ". Dumper( $header ) . "\n"; my $col=0; foreach my $h ( @$header ) { printField( row => $rowIndex, column => $col, cell_data => $h, cell_type => 'text', format => $headerFormat, worksheet => $worksheet ); $col++; } $rowIndex++; return $rowIndex; } # printExcelHeader #---------------------------------------------- # printField - output a cell to a spreadsheet #---------------------------------------------- sub printField{ my( %args ) = @_; my $row = $args{row}; my $column = $args{column}; my $data = $args{cell_data}; my $dataType = $args{cell_type}; my $format = $args{format}; my $worksheet = $args{worksheet}; $data =~ s/\s*$//; # Output either text or a numerical value # Note: if a value is missing and the desired format is # number, we actually output it as text s.t. the cell appears # with an empty field instead of a zero. if ( defined $data ) { if( $data =~ m/(\D*)(\d+)(\D*)(\d*)/ and $dataType and $dataType eq 'number') { $worksheet->write_number($row, $column, $data, $format); } else { $data = Common::UTF8::Encode($data); $worksheet->write_string($row, $column, $data, $format); } } else { $data = Common::UTF8::Encode($data); $worksheet->write_string($row, $column, $data, $format); } }#printField sub _printRawSales { my( %args ) = @_; my $sArray = $args{sale_data}; my $saleID = $args{sale_id}; my $raID = $args{artist_royalty_album_id}; my $itemID = $args{artist_royalty_income_item_id}; my $stmtID = $args{artist_royalty_statement_id}; my $itemTotal = $args{item_total}; my $grossSales = $args{gross_sales}; my $grossRevenue = $args{gross_revenue}; my $netUnits = $args{net_units}; my $albumName = $args{album_name}; my $catalogNo = $args{catalog_number}; my $trackName = $args{track_name}; my $isrc = $args{isrc}; my $contractTitle = $args{contract_title}; my $rateTypeID = $args{rate_type_id}; my $netRate = $args{net_rate}; my $incomeSource = $args{income_source}; my $region = $args{region}; my $channel = $args{channel}; my $priceLevel = $args{price_level}; my $reserveRevenue = $args{reserve_revenue}; my $reserveRate = $args{reserve_rate}; my $reserveUnits = $args{reserve_units}; my $totalRevenue = $args{total_revenue}; # XXX possible dupe of gross revenue my $rateType = $rateTypeMap{$rateTypeID}; my( $fileID, $serviceID, $pType, $fType, $_albumName, $_upc, $_trackName, $_isrc, $units, $price, $conversionRate, $currencyCode, $countryCode, $productID, $wholesalePrice, $retailPrice, $sales, $sRevenue, $returns, $rRevenue ) = @$sArray; my $showStatementID = (scalar @statements) > 0; if ( $gMakeExcel && !$rawSalesWorkbook ) { my $reportExcel = 'report_rawsales_stmt_' . $stmtID . '.xlsx'; $rawSalesWorkbook = Excel::Writer::XLSX->new($reportExcel); $rawSalesWorksheet = $rawSalesWorkbook->add_worksheet('Statement '. $stmtID); $rawSalesWorksheet->freeze_panes(1,0); # freeze top row $rawSalesHeaderFormat = $rawSalesWorkbook->add_format(); #$gray = $rawSalesWorkbook->set_custom_color(23,200,200,200); my $headerFillColor = $rawSalesWorkbook->set_custom_color(23,216,216,216); $rawSalesHeaderFormat->set_bg_color($headerFillColor); $rawSalesHeaderFormat->set_bold(); $rawSalesHeaderFormat->set_size(kExcelFontSize); $rawSalesHeaderFormat->set_text_wrap(); $rawSalesHeaderFormat->set_bottom(2); # continuous (weight 2) $rawSalesHeaderFormat->set_left(1); # continuous (weight 1) $rawSalesHeaderFormat->set_right(1); # continuous (weight 1) $rawSalesHeaderFormat->set_top(1); # continuous (weight 1) $rawSalesPlainFormat = $rawSalesWorkbook->add_format(); $rawSalesPlainFormat->set_size(kExcelFontSize); } my @headerFormat = ( # combo header definition + data row formatting [ "stmt-id", "number" ], [ "item-id", "number" ], [ "source", "text" ], [ "region", "text" ], [ "channel", "text" ], [ "price-tier", "text" ], [ "rate-type", "text" ], [ "contract-title", "text" ], [ "album (cat)", "text" ], [ "track", "text" ], [ "isrc", "text" ], [ "sale-id", "number" ], # A [ "file-id", "number" ], # B [ "service-id", "number" ], # C [ "service-name", "text" ],# D [ "product_type", "text" ],# E [ "format-type", "text" ],# F #[ "album", "text" ],# G #[ "upc", "text" ],# H #[ "track", "text" ],# I #[ "isrc", "text" ],# J [ "units", "number" ],# K [ "price", "number" ],# L [ "wholesale-price", "number" ],# L [ "sales", "number" ], [ "sales-revenue", "number" ], [ "returns", "number" ], [ "returns-revenue", "number" ], [ "gross-sales", "number" ], [ "reserve-rate", "number" ], [ "reserved", "number" ], [ "net-units", "number" ], [ "conversion-rate", "number" ],# M [ "gross-revenue", "number" ], [ "reserve-revenue", "number" ], [ "currency", "text" ],# N [ "country", "text" ],# O # [ "rate-type-id", "number" ], # XXX remove [ "net-rate", "number" ], [ "revenue", "number" ],# P [ "product-id", "number" ],# Q [ "item-total", "number" ], ); # unshift @headerFormat, ['stmt-ID','text'] if ( $showStatementID ); if ( ! exists $productTypeMap{$productID} ) { my $product = RPS::DB::Item::Product->Lookup( product_id => $productID ); $productTypeMap{$productID} = $product->product_type_id; } $pType = $productTypeMap{$productID}; # show the matched product's type, not the sale's product type if ( !$showRawSalesHeader++ ) { if ( !$gMakeExcel ) { my @header = map { $_[0] } @headerFormat; print join("\t", "REPORT_RAW:", @header) . "\n"; } else { my $col=0; foreach my $h ( @headerFormat ) { my $label = @$h[0]; my $cellType = @$h[1]; printField( row => $rawSalesRow, column => $col, cell_data => $label, cell_type => 'text', format => $rawSalesHeaderFormat, worksheet => $rawSalesWorksheet ); $col++; } $rawSalesRow++; } } my $serviceName = _getServiceName( $serviceID ); # my $_revenue = $units * $price * $conversionRate; # XXX valid for digital only my $_revenue = $grossRevenue - $reserveRevenue; my @rowdata = ( $stmtID, $itemID, $incomeSource, $region, $channel, $priceLevel, $rateType, $contractTitle, "$albumName ($catalogNo)", $trackName, $isrc, # $saleID, # "sale-id", # A $fileID, # "file-id", # B $serviceID, # "service-id", # C $serviceName, # "service-name", # D $pType, # "product_type", # E $fType, # "format-type", # F #$albumName, # "album", # G #$upc, # "upc", # H #$trackName, # "track", # I #$isrc, # "isrc", # J $units, # "units", # K $price, # "price", # L $wholesalePrice, $sales, $sRevenue, $returns, $rRevenue, $grossSales, $reserveRate, $reserveUnits, $netUnits, $conversionRate, # "conversion-rate", # M $grossRevenue, $reserveRevenue, $currencyCode, # "currency", # N $countryCode, # "country", # O # $rateTypeID, $netRate, # $_revenue, # "revenue", # P $totalRevenue, $productID, # "product-id" # Q $itemTotal, ); # unshift @rowdata, $stmtID if ( $showStatementID ); if ( !$gMakeExcel ) { print join("\t", "REPORT_RAW:", @rowdata) . "\n"; } else { my $col=0; foreach my $c ( @rowdata ) { my @hArray = @{ $headerFormat[$col] }; my $cellType = $hArray[1]; # my $label = @hArray[0]; # print "$label: c[$c] val($c) type[$cellType]\n"; printField( row => $rawSalesRow, column => $col, cell_data => $c, cell_type => $cellType, format => $rawSalesPlainFormat, worksheet => $rawSalesWorksheet ); $col++; } $rawSalesRow++; } } # _printRawSales sub _getServiceName { my $serviceID = shift; # print "D: _getServiceName serviceID = $serviceID\n"; if ( !exists $serviceNameMap{$serviceID} ) { my $sql = "SELECT service_name FROM service WHERE service_id=$serviceID"; my $sth = $dbo->DoCmd($sql); my( $serviceName ) = $sth->fetchrow_array(); $serviceNameMap{$serviceID} = $serviceName; } return $serviceNameMap{$serviceID}; } sub _getCountryName { my $countryCode = shift; # print "D: _getCountryName countryCode = $countryCode\n"; if ( !exists $countryNameMap{$countryCode} ) { my $sql = "SELECT name FROM country WHERE alpha2=" . $dbo->DBQuote($countryCode); my $sth = $cdbo->DoCmd($sql); # RSCOMMON my( $countryName ) = $sth->fetchrow_array(); $countryNameMap{$countryCode} = $countryName; } return $countryNameMap{$countryCode}; } sub _getFormatName { my $formatType = shift; return RPS::File::Sale::GetFormatType( $formatType ); } sub _serviceSort { # sorting for Earnings By Service tab my $aVal = lc $serviceMap{$a}{percent_of_total}; my $bVal = lc $serviceMap{$b}{percent_of_total}; return $bVal <=> $aVal; # sort from highest to lowest .. } sub _earningsByCountrySort { # sorting for Earnings By Country tab my $aVal = lc $earningsByCountry{$a}{percent_of_total}; my $bVal = lc $earningsByCountry{$b}{percent_of_total}; return $bVal <=> $aVal; # sort from highest to lowest .. } sub _earningsByFormatSort { # sorting for Earnings By Format tab my $aVal = lc $earningsByFormat{$a}{percent_of_total}; my $bVal = lc $earningsByFormat{$b}{percent_of_total}; return $bVal <=> $aVal; # sort from highest to lowest .. } sub _revenueSort { # sorting by Earnings my ($a, $b, $href ) = @_; my $aVal = lc $href->{$a}{net_earnings}; my $bVal = lc $href->{$b}{net_earnings}; return $bVal <=> $aVal; # sort from highest to lowest .. } sub _albumTitleSort { my $aVal = $albumTitleMap{$a}; my $bVal = $albumTitleMap{$b}; return $aVal cmp $bVal; } sub _trackTitleSort { my $aVal = $trackTitleMap{$a}; my $bVal = $trackTitleMap{$b}; return $aVal cmp $bVal; } sub _formatSort { my $aName = lc $earningsByFormat{$a}{format_name}; my $bName = lc $earningsByFormat{$b}{format_name}; return $aName cmp $bName; } sub parseCommandLine { my ($a) = @_; my $stmtID; my $albumID; my $trackID; my $regionID; my $contractID; my $termID; my $reserves; my $summary; # display sale count summary data my $rawSales; # generate raw sales report my @analyze; # show sale stats my $runID; my $earningsByService; # generate earnings by service report my $earningsByCountry; my $earningsByFormat; my $earningsByAlbumByService; my $earningsByAlbumByCountry; my $earningsByTrackByService; my $earningsByTrackByCountry; my @verbose; my $makeexcel; if ( ! GetOptions( 'c=i' => \$clientID, 's|stmt=i' => \$stmtID, 'a|album=i' => \$albumID, 't|track=i' => \$trackID, 'r|region=i' => \$regionID, 'contract=i' => \$contractID, 'term=i' => \$termID, 'reserves' => \$reserves, 'summary' => \$summary, 'verbose' => \@verbose, 'rawsales' => \$rawSales, 'analyze' => \@analyze, 'run=i' => \$runID, 'earningsbyservice' => \$earningsByService, 'earningsbycountry' => \$earningsByCountry, 'earningsbyformat' => \$earningsByFormat, 'earningsbyalbumbyservice' => \$earningsByAlbumByService, 'earningsbyalbumbycountry' => \$earningsByAlbumByCountry, 'earningsbytrackbyservice' => \$earningsByTrackByService, 'earningsbytrackbycountry' => \$earningsByTrackByCountry, 'makeexcel' => \$makeexcel, )) { die("An error has occurred while parsing arguments, aborting\n"); } $a->{clientID} = $clientID; $a->{stmtID} = $stmtID; $a->{albumID} = $albumID; $a->{trackID} = $trackID; $a->{regionID} = $regionID; $a->{contractID} = $contractID; $a->{termID} = $termID; $a->{reserves} = $reserves; $a->{summary} = $summary; $a->{verbose} = \@verbose; $a->{rawsales} = $rawSales; $a->{analyze} = \@analyze; $a->{runID} = $runID; $a->{makeexcel} = $makeexcel; $a->{earningsbyservice} = $earningsByService; $a->{earningsbycountry} = $earningsByCountry; $a->{earningsbyformat} = $earningsByFormat; $a->{earningsbyalbumbyservice} = $earningsByAlbumByService; $a->{earningsbyalbumbycountry} = $earningsByAlbumByCountry; $a->{earningsbytrackbyservice} = $earningsByTrackByService; $a->{earningsbytrackbycountry} = $earningsByTrackByCountry; } sub usage { print STDERR "\nusage: $0 -c clientID -s stmtID -a albumID -t trackID -r regionID --contract contractID --term termID --reserves --summary\n"; } sub _report { my ( $string, $verbosity ) = @_; $verbosity = 1 unless defined $verbosity; if ( $gVerbosityLevel >= $verbosity ) { print STDERR $string . "\n"; } } sub _getAlbumInfo { # returns hash with album info my $albumID = shift; if ( !exists $albumMap{$albumID} ) { my $album = RPS::DB::Item::Album->Lookup( album_id => $albumID ); my $artist = RPS::DB::Item::Artist->Lookup( artist_id => $album->artist_id ); $albumMap{$albumID}{title} = $album->title; $albumMap{$albumID}{catalog_number} = $album->catalog_number; $albumMap{$albumID}{artist} = $artist->name; } return $albumMap{$albumID}; } sub _getTrackInfo { # returns hash with track info my $trackID = shift; if ( !exists $trackMap{$trackID} ) { my $track = RPS::DB::Item::Track->Lookup( track_id => $trackID ); die("invalid trackID $trackID !!!") if ( !$track ); my $master = RPS::DB::Item::Master->Lookup( master_id => $track->master_id ); my $artist = RPS::DB::Item::Artist->Lookup( artist_id => $track->artist_id ); $trackMap{$trackID}{title} = $track->title; $trackMap{$trackID}{artist} = $artist->name; $trackMap{$trackID}{isrc} = $master->isrc; } return $trackMap{$trackID}; } sub _getRegion { my $regionID = shift; if ( !exists $regionMap{$regionID} ) { my $region = RPS::DB::Item::Region->Lookup( region_id => $regionID ); print "D: _getRegion - storing $regionID \n"; # XXX $regionMap{$regionID} = $region; } return $regionMap{$regionID}; } sub _getSource { my $sourceID = shift; if ( !exists $sourceMap{$sourceID} ) { my $source = RPS::DB::Item::IncomeSource->Lookup( income_source_id => $sourceID ); $sourceMap{$sourceID} = $source; } return $sourceMap{$sourceID}; } sub _getContract { my $contractID = shift; if ( !exists $contractMap{$contractID} ) { my $contract = RPS::DB::Item::NewArtistContract->Lookup( artist_contract_id => $contractID ); $contractMap{$contractID} = $contract; } return $contractMap{$contractID}; }