#------------------------------------------------------------ # Copyright (C) 2007 RoyaltyShare, Inc. All Rights Reserved # $Id$ #------------------------------------------------------------ package RPS::DB::Item::ReportQueries::BaseLicenseOverage; use strict; use warnings; use Data::Dumper; use lib '/app/tools/common/lib'; use Common::DB::Item; use Common::DB::ItemCollection; use Common::Assert; use lib '/app/tools/rps/lib'; use RPS::DB::Item::Product; use RPS::DB::Item::ReportQueries; use base 'RPS::DB::Item::ReportQueries'; sub _config { return { 'label_name' => {}, 'catalog_number' => {}, 'album_title' => {}, 'track_title' => {}, 'artist_name' => {}, 'track_order' => {}, 'track_id' => {}, 'album_id' => {}, 'product_type_id' => {}, 'region_name' => {}, 'overage' => {}, }; } sub _getLicenseTableName { my ($self) = @_; assert(0, 'override this'); } sub GetTracksWithLicenseOverage { my ($class) = @_; # This method uses database queries against the track license table # (the name of which must be defined in the derived class) to # determine if a track has a license overage. # my $licenseTable = $class->_getLicenseTableName(); # Get a list of all the distinct product type ids from the client's # set of track licenses. # my @distinctProductTypeIDs = $class->_GetDistinctProductTypeIDs($licenseTable); my $dbo = Common::RSApp::GetClientDB(); # A temporary table is used to keep track of tracks and their licenses. # # First, the table is pre-filled with the following types of licenses: # # * "All Products" (product_type_id = NULL) # * "All Physical Products" (product_type_id = 255) # * "All Digital Products" (product_type_id = 254) # # Note: see RPS/DB/Item/Product.pm for product and license type definitions. # # Also, notice that we're forcing the data type on the overage column to # DECIMAL when creating the table (if we don't do this it defaults to DOUBLE). # # If left as floating point, this can cause unexpected behavior when we try # to query for records having an overage <= 100, due to the overage not # being stored as an exact value. Changing the overage to DECIMAL results # in an exact value being stored, which allows us to successfully query for # records with an overage <= 100. # $dbo->DoCmd('DROP TABLE IF EXISTS zzoverage'); $dbo->DoCmd('CREATE TEMPORARY TABLE zzoverage (overage DECIMAL(8,4) NOT NULL) AS' . ' SELECT' . ' b.track_id,' . ' b.artist_id,' . ' b.album_id,' . ' a.product_type_id,' . ' a.region_id,' . ' sum(a.share) as overage' . ' FROM '. $licenseTable .' a, track b' . ' WHERE a.track_id = b.track_id' . ' AND ( a.product_type_id is NULL' . ' OR a.product_type_id IN (254, 255) ) ' . ' AND a.inactive = 0' . ' AND ( a.term_start IS NULL ' . ' OR ( a.term_start IS NOT NULL AND a.term_start <= now() ) ) ' . ' AND ( a.term_end IS NULL ' . ' OR ( a.term_end IS NOT NULL AND a.term_end >= now() ) ) ' . ' GROUP BY a.track_id, a.region_id, a.product_type_id '); # Next, we loop over all of the licensed product types and check for # any licensing overlap due to an ALL, ALL-D and/or ALL-P license. # my %shareMap; foreach my $productTypeID (@distinctProductTypeIDs) { my $sth1 = $dbo->DoCmd( ' SELECT' . ' b.track_id,' . ' b.artist_id,' . ' b.album_id,' . ' a.product_type_id,' . ' a.region_id,' . ' sum(a.share) as overage' . ' FROM '. $licenseTable .' a, track b' . ' WHERE a.track_id = b.track_id' . " AND a.product_type_id=$productTypeID" . ' AND a.inactive = 0' . ' AND ( a.term_start IS NULL ' . ' OR ( a.term_start IS NOT NULL AND a.term_start <= now() ) ) ' . ' AND ( a.term_end IS NULL ' . ' OR ( a.term_end IS NOT NULL AND a.term_end >= now() ) ) ' . ' GROUP BY a.track_id, a.region_id, a.product_type_id ' ); while (my $hr1 = $sth1->fetchrow_hashref()) { my $artistID = $hr1->{'artist_id'}; if (! $artistID) { # !!! Not all tracks _have_ an artist ID... # $artistID = 'NULL'; } my $productID = $hr1->{'product_type_id'}; # Store the share percentage for the license product type. If we've already # seen a similar license then we'll add onto the share value. # # Note: we don't include the share due to an all digital or physical license # since these were included in the overage table when we created it. # if( RPS::DB::Item::Product::kAllDigitalProducts != $productID && RPS::DB::Item::Product::kAllPhysicalProducts != $productID ) { $shareMap{$hr1->{'track_id'}}{$hr1->{'album_id'}}{$artistID}{$productTypeID}{$hr1->{'region_id'}} += $hr1->{'overage'}; } # Look for overages due to All Products (NULL), All Physical (255), # and/or All Digital (254) licenses. # # Note: for ease of maintenance, this section of code is shared by both the US # and CA overage reports, however the ringtone license exclusion only applies # to US Mechanicals since there are no RING licenses in CA Mechanicals. E.g., # for the CA report we're always going to be checking for overages with all licenses. # if( $productID && RPS::DB::Item::Product::kProductTypeRingtone != $productID ) { # If this is a digital license, grab the ALL DIGITAL (254) total, # if any, and add to our current value. # if( RPS::DB::Item::Product::kProductTypeDigital == $productID || RPS::DB::Item::Product::kProductTypeDigitalTrack == $productID ) { my $selectSql = 'SELECT overage from zzoverage where track_id='.$hr1->{'track_id'} .' AND album_id='.$hr1->{'album_id'} ." AND artist_id=$artistID" .' AND region_id='.$hr1->{'region_id'} .' AND product_type_id = ' . RPS::DB::Item::Product::kAllDigitalProducts; # 254 my $sth2 = $dbo->DoCmd($selectSql); my $hr2 = $sth2->fetchrow_hashref(); if( $hr2 ) { $shareMap{$hr1->{'track_id'}}{$hr1->{'album_id'}}{$artistID}{$productTypeID}{$hr1->{'region_id'}} += $hr2->{'overage'}; } } elsif( RPS::DB::Item::Product::kAllDigitalProducts != $productID && RPS::DB::Item::Product::kAllPhysicalProducts != $productID ) { # If the license isn't digital, and it's not ALL-D or ALL-P, # then it must be physical. # Grab the ALL PHYSICAL (255) total, if any, and add to our current value. # my $selectSql = 'SELECT overage from zzoverage where track_id='.$hr1->{'track_id'} .' AND album_id='.$hr1->{'album_id'} ." AND artist_id=$artistID" .' AND region_id='.$hr1->{'region_id'} .' AND product_type_id = ' . RPS::DB::Item::Product::kAllPhysicalProducts; # 255 my $sth2 = $dbo->DoCmd($selectSql); my $hr2 = $sth2->fetchrow_hashref(); if( $hr2 ) { $shareMap{$hr1->{'track_id'}}{$hr1->{'album_id'}}{$artistID}{$productTypeID}{$hr1->{'region_id'}} += $hr2->{'overage'}; } } # Grab the ALL PRODUCTS ('NULL') total, if any, and add to our current value. # my $selectSql = 'SELECT overage from zzoverage where track_id='.$hr1->{'track_id'} .' AND album_id='.$hr1->{'album_id'} ." AND artist_id=$artistID" .' AND region_id='.$hr1->{'region_id'} .' AND product_type_id IS NULL'; my $sth2 = $dbo->DoCmd($selectSql); my $hr2 = $sth2->fetchrow_hashref(); if( $hr2 ) { $shareMap{$hr1->{'track_id'}}{$hr1->{'album_id'}}{$artistID}{$productTypeID}{$hr1->{'region_id'}} += $hr2->{'overage'}; } } } } foreach my $trackID (keys %shareMap) { foreach my $albumID (keys %{$shareMap{$trackID}}) { foreach my $artistID (keys %{$shareMap{$trackID}{$albumID}}) { foreach my $productTypeID (keys %{$shareMap{$trackID}{$albumID}{$artistID}}) { foreach my $regionID (keys %{$shareMap{$trackID}{$albumID}{$artistID}{$productTypeID}}) { if (! $artistID) { $artistID = 'NULL'; } my $overage = $shareMap{$trackID}{$albumID}{$artistID}{$productTypeID}{$regionID}; $dbo->DoCmd('INSERT into zzoverage SET' . " track_id=$trackID," . " album_id=$albumID," . " artist_id=$artistID," . " product_type_id=$productTypeID," . " overage=$overage," . " region_id=$regionID" ); } } } } } $dbo->DoCmd('DELETE FROM zzoverage where overage <= 100'); # Whew! Ok, got the silly report table. Now we can render it. # $class->_ProduceReportCollection(); } sub _GetDistinctProductTypeIDs { my ($class, $tableName) = @_; my @productIDs; my $sql = 'SELECT DISTINCT product_type_id from '. $tableName; my $dbo = Common::RSApp::GetClientDB(); my $sth = $dbo->DoCmd($sql); while (my $hr= $sth->fetchrow_hashref()) { if (defined $hr->{product_type_id}) { push @productIDs, $hr->{product_type_id}; } } return @productIDs; } sub _ProduceReportCollection { my ($class) = @_; my @selectFields; push @selectFields, "label.label_name as label_name"; push @selectFields, "album.catalog_number as catalog_number"; push @selectFields, "album.title as album_title"; push @selectFields, "track.title as track_title"; push @selectFields, "artist.name as artist_name"; push @selectFields, "track.track_order as track_order"; push @selectFields, "zzoverage.track_id as track_id"; push @selectFields, "track.album_id as album_id"; push @selectFields, "region.name as region_name"; push @selectFields, "zzoverage.overage as overage"; push @selectFields, "zzoverage.product_type_id as product_type_id"; #push @selectFields, "CASE WHEN zzoverage.product_type_id is not null THEN product_type.description ELSE 'All' END AS 'product_type'"; my $selectClause = join(',', @selectFields); my $fromClause = "zzoverage"; # Note the big nasty implicit join. # my $joinClause = 'LEFT OUTER JOIN artist ON zzoverage.artist_id = artist.artist_id'; $joinClause .= ' LEFT OUTER JOIN album ON zzoverage.album_id = album.album_id'; $joinClause .= ' LEFT OUTER JOIN track ON zzoverage.track_id = track.track_id'; $joinClause .= ' LEFT OUTER JOIN label ON label.label_id = album.label_id'; $joinClause .= ' LEFT OUTER JOIN region ON zzoverage.region_id = region.region_id'; #$joinClause .= ' LEFT OUTER JOIN product_type ON zzoverage.product_type_id = product_type.product_type_id'; my $orderByClause = 'label_name, album_title, track_order, region_name'; my $sql = "SELECT $selectClause FROM $fromClause $joinClause ORDER BY $orderByClause"; return $class->SUPER::GetAll($sql); } 1;