package RPS::Import::Universal::v75; use strict; use warnings; use lib '/app/tools/rps/lib'; use RPS::Import::ServiceMap; use Date::Calc qw(Days_in_Month Add_Delta_Days Decode_Month Add_Delta_YMD); use parent 'RPS::Import::ImporterMixed'; sub physical { 2 } sub _fieldMapExtended { { physical => { option1 => { # 'Physical Sales' labelName => 'B', countryCode => 'I', dateBegin => 'K12', # BW Statement Tab, we'll use B9 or A8 as an alternative cell productType => 'M', artistName => 'C', upc => 'F', albumName => 'G', totalRevenue => 'R', }, }, digital => { option1 => { # 'Digital Sales' labelName => 'B', serviceID => 'K', countryCode => 'K', dateBegin => 'K12', # BW Statement Tab, we'll use B9 or A8 as an alternative cell productType => 'H', formatType => 'O', artistName => 'C', upc => 'F', albumName => 'G', isrc => 'H', trackName => 'I', units => 'T', price => 'R', mediaType => 'O', }, option2 => { # 'Domestic Licensing' labelName => 'B', serviceID => 'K', countryCode => 'A', dateBegin => 'K12', # BW Statement Tab, we'll use B9 or A8 as an alternative cell productType => 'H', formatType => 'M', artistName => 'C', upc => 'F', albumName => 'G', isrc => 'H', trackName => 'I', units => 'O', price => 'O', mediaType => 'M', }, option3 => { # 'Domestic Licensing', alternative tab with extra column labelName => 'B', serviceID => 'K', countryCode => 'A', dateBegin => 'K12', # BW Statement Tab, we'll use B9 or A8 as an alternative cell productType => 'H', formatType => 'M', artistName => 'C', upc => 'F', albumName => 'G', isrc => 'H', trackName => 'I', units => 'P', price => 'P', mediaType => 'M', }, }, mixed => { option1 => { # 'Foreign Licensing' labelName => 'B', countryCode => 'O', dateBegin => 'K12', # BW Statement Tab, we'll use B9 or A8 as an alternative cell formatType => 'M', artistName => 'C', upc => 'F', albumName => 'G', isrc => 'H', trackName => 'I', units => 'R', totalRevenue => 'P', price => 'P', mediaType => 'M', }, option2 => { # 'Foreign Licensing', alternative tab with extra column labelName => 'B', countryCode => 'M', dateBegin => 'K12', # BW Statement Tab, we'll use B9 or A8 as an alternative cell formatType => 'L', artistName => 'C', upc => 'E', albumName => 'F', isrc => 'G', trackName => 'H', units => 'O', totalRevenue => 'N', price => 'N', mediaType => 'L', }, option3 => { # 'Foreign Licensing', alternative tab with extra column #3 labelName => 'B', countryCode => 'N', dateBegin => 'K12', # BW Statement Tab, we'll use B9 or A8 as an alternative cell formatType => 'M', artistName => 'C', upc => 'F', albumName => 'G', isrc => 'H', trackName => 'I', units => 'O', totalRevenue => 'O', price => 'O', mediaType => 'M', }, } }; } sub _unverifiedFieldMapExtended { { physical => { option1 => { # 'Physical Sales' _netUnits => 'U', }, }, digital => { option1 => { # 'Digital Sales' _exploitation_description => 'M', }, }, mixed => { option1 => { # 'Foreign Licensing' _productTypePhys => 'M', _productTypeDig => 'H', _netUnits => 'R', _segment => 'A', }, option2 => { # 'Foreign Licensing', alternative tab with extra column _productTypePhys => 'L', _productTypeDig => 'G', _netUnits => 'O', _segment => 'A', }, option3 => { # 'Foreign Licensing', alternative tab with extra column #3 _productTypePhys => 'M', _productTypeDig => 'H', _netUnits => 'O', _segment => 'A', }, }, } } sub _headerIdentifierExtended { my $self = shift; # These tabs do not have unique column names to distinguish between sheets. # Here we will determine a header by the sheet name using _chooseHeaderBlock() my $hHeader = { physical => { option1 => { # 'Physical Sales' upc => 'UPC', }, }, digital => { option1 => { # 'Digital Sales' upc => 'UPC', }, option2 => { # 'Domestic Licensing' price => 'Domestic Licensing Income', }, option3 => { # 'Domestic Licensing' alternative tab price => 'Domestic Licensing Income', }, }, mixed => { option1 => { # 'Foreign Licensing' units => 'Gross Quantity - Interco', }, option2 => { # 'Foreign Licensing' alternative tab units => 'TOTAL NET QUANTITY', }, option3 => { # 'Foreign Licensing' alternative tab #3 units => 'Foreign Licensing Income', } }, }; return $self->_chooseHeaderBlock($hHeader); } # mappings my %digitalSalesMapping = ( 'audio cloud services - subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'audio stream true up' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'audio subscription true up' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'audio track perm. kiosk' => { format_type => RPS::File::Sale::FORMAT_DOWNLOAD, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'audio track stream portable' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'audio track stream portable - subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'audio track stream webcast' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'audio track stream webcast - subscr' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'audio track stream webcast non-royalty' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'audio track stream webcast portable' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'audio track stream webcast portable - su' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'breakage audio stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital allocations downloads' => { format_type => RPS::File::Sale::FORMAT_DOWNLOAD, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital allocations streams (audio)' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital allocations streams (video)' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'digital aud track stream -subscr ltdcat' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital online aud album permanent' => { format_type => RPS::File::Sale::FORMAT_DOWNLOAD, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital online aud track permanent' => { format_type => RPS::File::Sale::FORMAT_DOWNLOAD, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital online aud track stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital online aud track stream - subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital online vid track stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'digital online vid track stream - subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'fitness business use / gym use' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'fitness in app use' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'fitness on device / hardware use' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'longform video - permanent' => { format_type => RPS::File::Sale::FORMAT_DOWNLOAD, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'telco breakage - audio subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'user generated content stream - subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'user generated content stream track' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'user generated content stream vid subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'user generated content stream video' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'video cloud services - subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'video stream video portable - subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'video stream video portable' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'video stream webcasting' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'video stream webcasting - subscr' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'dig mob aud track perm mast (real) tones' => { format_type => RPS::File::Sale::FORMAT_RINGTONE, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'video stream sponsorship' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'digital online vid track permanent' => { format_type => RPS::File::Sale::FORMAT_DOWNLOAD, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'dig mob aud track perm callrgs/rgbacks' => { format_type => RPS::File::Sale::FORMAT_RINGTONE, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'breakage - audio subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'ffp on demand stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'ffp user generated content stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'true up - audio stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'ffp short form videos' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, ); my %domesticLicensingMapping = ( 'business establishment services' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, }, 'cable radio & subscription services' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, }, 'cabsat ephemeral' => { format_type => RPS::File::Sale::FORMAT_EPHEMERAL, }, 'royalties' => { format_type => undef, # error out }, 'satellite radio' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, }, 'sdars ephemeral' => { format_type => RPS::File::Sale::FORMAT_EPHEMERAL, }, 'television promo spot' => { format_type => undef, # error out }, 'preexisting services' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, }, 'video' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, }, 'audio track stream webcast' => { format_type => RPS::File::Sale::FORMAT_PERFORMANCE, } ); my %foreignLicensingMapping = ( 'audio cloud services' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'cd album' => { format_type => RPS::File::Sale::TYPE_PHYSICAL, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital allocations streams (audio)' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital online aud album permanent' => { format_type => RPS::File::Sale::FORMAT_DOWNLOAD, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital online aud track permanent' => { format_type => RPS::File::Sale::FORMAT_DOWNLOAD, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital online aud track stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital online aud track stream - subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital online aud track t.out download' => { format_type => RPS::File::Sale::FORMAT_DOWNLOAD, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'licence - other' => { format_type => undef, # error out media_type => undef, # error out }, 'mc album' => { format_type => RPS::File::Sale::TYPE_PHYSICAL, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'user generated content stream - subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'user generated content stream track' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'vinyl album 12" 33 rpm' => { format_type => RPS::File::Sale::TYPE_PHYSICAL, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'user generated content stream vid subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'user generated content stream video' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'digital online vid track stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'digital online vid track stream - subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'video' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'dig mob aud track perm mast (real) tones' => { format_type => RPS::File::Sale::FORMAT_RINGTONE, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital allocations streams (video)' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'digital online vid track permanent' => { format_type => RPS::File::Sale::FORMAT_DOWNLOAD, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, } ); my %sheetRules = ( 'physical sales' => { serviceID => Client::Service::DSP_UNIVERSAL, countryCode => sub { if ( $_[1] =~ /Universal Music (\w+)/i ) { my $oCountry = Common::Country->Get($1); return $oCountry ? $oCountry->alpha2() : undef; } return 'US'; }, productType => sub { return RPS::File::Sale::TYPE_LP if $_[1] =~ /^vinyl album 12" 33 rpm$/i; return RPS::File::Sale::TYPE_LP if $_[1] =~ /^vinyl album 12" 45 rpm$/i; return RPS::File::Sale::TYPE_CD if $_[1] =~ /^CD album$/i; return RPS::File::Sale::TYPE_CASS if $_[1] =~ /^MC album$/i; return; }, channel => RPS::File::Sale::CHANNEL_RETAIL, priceLevel => RPS::File::Sale::PLEVEL_UNKNOWN, currencyCode => 'USD', mediaType => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'digital sales' => { serviceID => sub { my $expDesc = $_[0]->_getByFieldName('_exploitation_description') || ''; if ( $_[1] =~ /^All Other Licensee?s$/i ) { return Client::Service::DSP_UNIVERSAL if $expDesc =~ /^D\-B2B \- Product Regular$/i; return Client::Service::DSP_SOUNDEXCHANGE if $expDesc =~ /^D\-B2B\- SoundExchange Webcasting$/i; } elsif ( $_[1] =~ /^#$/i ) { return Client::Service::DSP_UNIVERSAL if $expDesc =~ /^D\-B2B\s*\-\s*(?:Product Regular)$/i; return Client::Service::DSP_UNIVERSAL if $expDesc =~ /^D\-B2B\s*\-\s*(?:Alloc \- Advance)$/i; return Client::Service::DSP_UNIVERSAL if $expDesc =~ /^D\-B2B\s*\-\s*(?:Product Flat Fees Payments)$/i; return Client::Service::DSP_SOUNDEXCHANGE if $expDesc =~ /^D\-B2B\s*\-\s*(?:SoundExchange Webcasting)$/i; } return; }, countryCode => sub { if ( $_[1] =~ /Universal Music (\w+)/i ) { my $oCountry = Common::Country->Get($1); return $oCountry ? $oCountry->alpha2() : undef; } return 'US'; }, productType => sub { return $_[1] =~ /^#$/ ? RPS::File::Sale::TYPE_ALBUM : RPS::File::Sale::TYPE_TRACK; }, formatType => sub { return exists $digitalSalesMapping{$_[1]} ? $digitalSalesMapping{$_[1]}{format_type} : undef; }, currencyCode => 'USD', mediaType => sub { return exists $digitalSalesMapping{$_[1]} ? $digitalSalesMapping{$_[1]}{media_type} : undef; }, }, 'domestic licensing' => { countryCode => sub { return $_[1] =~ /^US\s?\-\s?.*$/i ? 'US' : undef; }, productType => sub { return $_[1] =~ /^#$/ ? RPS::File::Sale::TYPE_ALBUM : RPS::File::Sale::TYPE_TRACK; }, formatType => sub { return exists $domesticLicensingMapping{$_[1]} ? $domesticLicensingMapping{$_[1]}{format_type} : undef; }, units => sub { return $_[2] <=> 0; }, currencyCode => 'USD', mediaType => sub { return $_[1] =~ /^'?Video$/i ? RPS::DB::Item::MediaType::kMediaTypeVideo : RPS::DB::Item::MediaType::kMediaTypeAudio; }, }, 'foreign licensing' => { serviceID => Client::Service::DSP_DEF_JAM, countryCode => sub { my $oCountry = Common::Country->Get($_[1]); return $oCountry ? $oCountry->alpha2() : undef; }, productType => sub { # physical my $typePhys = $_[0]->_getByFieldName('_productTypePhys') || ''; return RPS::File::Sale::TYPE_CD if $typePhys =~ /^CD album$/i; return RPS::File::Sale::TYPE_LP if $typePhys =~ /^vinyl album 12" 33 rpm$/i; return RPS::File::Sale::TYPE_CASS if $typePhys =~ /^MC album$/i; $_[0]->fail("Unsupported product type '$typePhys'") if $typePhys =~ /^Licence \- Other$/i; # digital my $typeDig = $_[0]->_getByFieldName('_productTypeDig') || ''; return $typeDig =~ /^#$/ ? RPS::File::Sale::TYPE_ALBUM : RPS::File::Sale::TYPE_TRACK; }, formatType => sub { return exists $foreignLicensingMapping{$_[1]} ? $foreignLicensingMapping{$_[1]}{format_type} : undef; }, channel => RPS::File::Sale::CHANNEL_RETAIL, priceLevel => RPS::File::Sale::PLEVEL_UNKNOWN, currencyCode => 'USD', mediaType => sub { return exists $foreignLicensingMapping{$_[1]} ? $foreignLicensingMapping{$_[1]}{media_type} : undef; }, }, ); sub saleDates { my $self = shift; if ( $self->sheetname =~ /^(?:(?:Physical|Digital) Sales|Domestic Licensing|Foreign Licensing)$/i ) { return $self->{_dateBegin}, $self->{_dateEnd} if $self->{_dateBegin} && $self->{_dateEnd}; } $self->fail("Unable to determine sale period for sheetname: " . $self->sheetname); } sub labelName { my $self = shift; my $name = $self->_getByFieldName('labelName') || ''; my $result = $self->_ruleException($name); return defined $result ? $result : $name; } sub serviceID { my $self = shift; my $service = lc( $self->_getByFieldName('serviceID') || '' ); return Client::Service::DSP_UNIVERSAL if $service =~ /^loc . compilation . non royalty bearing$/i; my $result = $self->_ruleException($service); return $result if defined $result; my $serviceID = $self->getServiceID($service) || $RPS::Import::ServiceMap::SERVICE_ALIASES{$service}; return $serviceID if $serviceID; $self->fail("Unable to determine serviceID from '$service'"); } sub countryCode { my $self = shift; my $country = $self->_getByFieldName('countryCode') || ''; my $result = $self->_ruleException($country); return $result if defined $result; return 'CI' if $country =~ /^IVORY COAST$/i; return 'VI' if $country =~ /^VIRGIN ISLANDS UNITED STATES$/i; return 'PS' if $country =~ /^PALESTINIAN TERRITORY, OCCUPI$/i; return 'MV' if $country =~ /^MALIDIVES$/i; return 'VC' if $country =~ /^SAINT VINCENT AND THE GRENADIN$/i; return 'LA' if $country =~ /^LAOS$/i; return 'KN' if $country =~ /^St\. Kitts-Nevis$/i; return 'KR' if $country =~ /SOUTH KOREA/i; return 'TC' if $country =~ /Turks and Caicos Isl/i; $self->fail("Unable to determine countryCode from '$country'"); } sub currencyCode { my $self = shift; my $currency = $self->_getByFieldName('currencyCode') || ''; my $result = $self->_ruleException($currency); return $result if defined $result; $self->fail("Unable to determine currencyCode from '$currency'"); } sub productType { my $self = shift; my $type = $self->_getByFieldName('productType') || ''; my $result = $self->_ruleException($type); return $result if defined $result; $self->fail("Unable to determine productType from '$type'"); } sub formatType { my $self = shift; my $type = $self->_getByFieldName('formatType') || ''; my $result = $self->_ruleException( lc $type ); return $result if defined $result; $self->fail("Unable to determine formatType from '$type'"); } sub channel { my $self = shift; my $channel = $self->_getByFieldName('channel') || ''; my $result = $self->_ruleException($channel); return $result if defined $result; $self->fail("Unable to determine channel from '$channel'"); } sub priceLevel { my $self = shift; my $priceLevel = $self->_getByFieldName('priceLevel') || ''; my $result = $self->_ruleException($priceLevel); return $result if defined $result; $self->fail("Unable to determine priceLevel from '$priceLevel'"); } sub mediaType { my $self = shift; my $type = $self->_getByFieldName('mediaType') || ''; my $result = $self->_ruleException( lc $type ); return $result if defined $result; $self->fail("Unable to determine mediaType from '$type'"); } sub upc { my $self = shift; my $upc = $self->_getByFieldName('upc') || ''; my $result = $self->_ruleException($upc); return $result if defined $result; return $upc; } sub albumName { my $self = shift; my $name = $self->_getByFieldName('albumName') || ''; my $result = $self->_ruleException($name); return $result if defined $result; return $name; } sub isrc { my $self = shift; my $isrc = $self->_getByFieldName('isrc') || ''; my $result = $self->_ruleException($isrc); return $result if defined $result; return $isrc; } sub trackName { my $self = shift; my $name = $self->_getByFieldName('trackName') || ''; my $result = $self->_ruleException($name); return $result if defined $result; return $name; } # digital sub price { my $self = shift; my $price = abs( $self->_getByFieldName('price') || 0 ); ($price) = $price =~ /([+-]?\d+(?:\.\d+)?(?:[eE][+-]?\d+)?)/; my $result = $self->_ruleException($price); return $result if defined $result; return sprintf "%0.8f", $price; } sub unitPrice { abs shift->SUPER::unitPrice } sub units { my $self = shift; my $price = $self->_getByFieldName('price') || 0; my $units = $self->_getByFieldName('units') || 0; ($price) = $price =~ /([+-]?\d+(?:\.\d+)?(?:[eE][+-]?\d+)?)/; ($units) = $units =~ /([+-]?\d+(?:\.\d+)?(?:[eE][+-]?\d+)?)/; # 'Foreign Licensing' alternative tab #3 if ( $self->dataType eq 'mixed' && $self->dataTypeOption eq 'option3' ) { return $units <=> 0; } my $result = $self->_ruleException($units, $price); return $result if defined $result; if ( $price > 0 ) { $units = $units ? abs($units) : 1; } elsif ( $price < 0 ) { $units = $units ? -1 * abs($units) : -1; } return $units; } # physical sub sales { $_[0]->netUnits >= 0 ? $_[0]->netUnits : 0 } sub salesRevenue { $_[0]->totalRevenue > 0 ? $_[0]->totalRevenue : 0 } sub returns { $_[0]->netUnits < 0 ? abs $_[0]->netUnits : 0 } sub returnsRevenue { $_[0]->totalRevenue < 0 ? abs $_[0]->totalRevenue : 0 } sub totalRevenue { $_[0]->_getByFieldName('totalRevenue') || 0 } sub netUnits { my $self = shift; my $netUnits = $self->_getByFieldName('_netUnits') || 0; # 'Foreign Licensing' alternative tab #3 if ( $self->dataType eq 'mixed' && $self->dataTypeOption eq 'option3' ) { return $netUnits <=> 0; } return $netUnits; } sub _isValidSaleRecord { my $self = shift; if ( $self->_sheetName eq 'physical sales' ) { return if $self->_getByFieldName('totalRevenue') =~ /(?:Physical Sales Detail|Overall Result)/i; return if !$self->artistName && !$self->albumName; } elsif ( $self->_sheetName eq 'digital sales' ) { return if !$self->_getByFieldName('price') && !$self->_getByFieldName('units'); return if $self->_getByFieldName('price') =~ /(?:Digital Sales Detail|Overall Result)/i; return if !$self->artistName && !$self->albumName && !$self->trackName; } elsif ( $self->_sheetName eq 'domestic licensing' ) { return if $self->_getByFieldName('price') =~ /(?:Domestic Licensing Income)/i; return if $self->_getByFieldName('countryCode') =~ /^Overall Result$/i; return if !($self->units && ($self->albumName || $self->trackName)); } elsif ( $self->_sheetName eq 'foreign licensing' ) { return if $self->_getByFieldName('price') =~ /(?:Foreign Income|Intercompany Income - Foreign|Foreign Licensing (?:Expense|Income))/i; if ( my $countryCode = $self->_getByFieldName('countryCode') || '' ) { return if $countryCode =~ /^Overall Result|Country of Sales$/i; } if ( my $_segment = $self->_getByFieldName('_segment') || '' ) { return if $_segment =~ /^(?:Overall Results?|Segment)$/i; } # skip lines without price and without units (same columns are used for digital products) return if !$self->netUnits && !$self->totalRevenue; # throw an error if there is a value in one of the two fields, see RSD-8232 $self->fail("Units without revenue") if $self->netUnits && !$self->totalRevenue; $self->fail("Revenue without units") if $self->totalRevenue && !$self->netUnits; } return 1; } # secondary subs my %sheetToHeaderMap = ( 'physical sales' => [ 'physical', 'option1' ], 'digital sales' => [ 'digital', 'option1' ], 'domestic licensing' => [ 'digital', 'option2' ], # 'domestic licensing' => [ 'digital', 'option3' ], FYI, it is an alternative tab, not a comment 'foreign licensing' => [ 'mixed', 'option1' ], # 'foreign licensing' => [ 'mixed', 'option2' ], FYI, it is an alternative tab, not a comment # 'foreign licensing' => [ 'mixed', 'option3' ], FYI, it is an alternative tab, not a comment ); sub _chooseHeaderBlock { my ($self, $hHeader) = @_; my $hResult = {}; my $sheetName = $self->_sheetName || return $hHeader; if ( exists $sheetToHeaderMap{$sheetName} && @{ $sheetToHeaderMap{$sheetName} } ) { my $type = $sheetToHeaderMap{$sheetName}[0]; my $option = $sheetToHeaderMap{$sheetName}[1]; $hResult = { $type => { $option => $hHeader->{$type}{$option} } }; # add an alternative tab to the result hash ref. if ( $sheetName eq 'domestic licensing' ) { $hResult->{$type}{option3} = $hHeader->{$type}{option3}; } if ( $sheetName eq 'foreign licensing' ) { $hResult->{$type}{option2} = $hHeader->{$type}{option2}; $hResult->{$type}{option3} = $hHeader->{$type}{option3}; } } return $hResult; } sub _ruleException { my ( $self, @args ) = @_; my $sheet = $self->_sheetName; my ($method) = ( caller(1) )[3] =~ /.*::(.+)$/; return unless exists $sheetRules{$sheet} && exists $sheetRules{$sheet}{$method}; my $rule = $sheetRules{$sheet}{$method}; return ref $rule eq 'CODE' ? &$rule( $self, @args ) : $rule; } sub _sheetName { my $self = shift; my $name = $self->sheetname() || ''; $name =~ s/^\s+|\s+$//g; $name =~ s/[^0-9a-zA-Z\s]//g; $name =~ s/\s+/ /g; return lc $name; } sub _processHeader { my ($self, %args) = @_; if ( $self->{_dateBegin} && $self->{_dateEnd} ) { return $self->{_dateBegin}, $self->{_dateEnd}; } ($self->{_dateBegin}, $self->{_dateEnd}) = $self->_getPeriod(); return 1; } sub _processSheet { my ($self, $sheet, $sheetnum) = @_; # get date period from columns A8 or B9 of the 'BW Statement' tab if ( $self->{sheetname} =~ /^BW Statement$/i ) { foreach my $aLine ( @$sheet[0..50] ) { # read 50 first lines foreach my $col ( 0..1 ) { # for col. A and col. B next unless ref $aLine eq 'ARRAY'; my $date = $aLine->[$col] || ''; last if $self->_getPeriod($date); } } } # skip these tabs if ( $self->{sheetname} =~ /^(?:Marketing|A&R|A&R)$/i ) { return $self->SUPER::_processSheet( $sheet, $sheetnum ); } # throw an error if a tab is not in the allowed list if ( $self->{sheetname} !~ /_com.sap.ip.bi.xl.hiddensheet/i && $self->{sheetname} !~ /^BW Statement$/i && !exists $sheetToHeaderMap{ $self->_sheetName } ) { $self->fail("Unsupported sheet '$self->{sheetname}'"); } # Checking the sheet header for correctness my $headerFound = $self->SUPER::_processSheet( $sheet, $sheetnum ); if ( !$headerFound && $self->sheetname =~ /^Foreign Licensing$/i ) { $self->fail( "Malformed header of the '$self->{sheetname}' sheet" ); } return $headerFound; } my %months = ( jan => 1, feb => 2, mar => 3, apr => 4, may => 5, jun => 6, jul => 7, aug => 8, sep => 9, oct => 10, nov => 11, dec => 12 ); sub _getPeriod { my ($self, $period) = @_; $period ||= ''; # 'FOR 1 MONTH ENDING MAY 31, 2022' ===> 05/01/2022 - 05/31/2022 my ($p, $em, $ed, $ey) = $period =~ /FOR (\d+) MONTHS? ENDING (\w+) (\d{1,2}), (\d{4})/i; return unless $p; $p--; $em = $months{ lc(substr($em, 0, 3)) }; $self->{_dateEnd} = sprintf("%04d-%02d-%02d", $ey, $em, Days_in_Month($ey, $em)); if ( !$period ) { $self->{_dateBegin} = sprintf("%04d-%02d-%02d", $ey, $em, 1); } else { my ($by, $bm, $bd) = Add_Delta_YMD($ey, $em, 1, 0, -$p, 0); $self->{_dateBegin} = sprintf("%04d-%02d-%02d", $by, $bm, 1); } return ($self->{_dateBegin}, $self->{_dateEnd}); } 1;