package RPS::Import::Universal::v74; 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_N_Delta_YMD); use parent qw(RPS::Import::MapFaceBase RPS::Import::ImporterMixed); sub physical { 2 } sub _fieldMapExtended { { physical => { option1 => { # 'Physical Sales' labelName => 'B', countryCode => 'I', dateBegin => 'K20', # Statement Tab productType => 'M', artistName => 'C', upc => 'F', albumName => 'G', totalRevenue => 'R', }, }, digital => { option1 => { # 'Digital Sales' labelName => 'B', serviceID => 'K', countryCode => 'K', dateBegin => 'K20', # Statement Tab 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 => 'K20', # Statement Tab productType => 'H', formatType => 'M', artistName => 'C', upc => 'F', albumName => 'G', isrc => 'H', trackName => 'I', units => 'P', price => 'P', }, }, mixed => { option1 => { # 'Foreign Licensing' labelName => 'B', serviceID => 'H', countryCode => 'I', dateBegin => 'F', productType => 'O', formatType => 'R', artistName => 'D', upc => 'K', albumName => 'L', isrc => 'K', trackName => 'L', units => 'S', channel => 'Q', wholesalePrice => 'V', retailPrice => 'T', totalRevenue => 'AG', price => 'AG', mediaType => 'P', }, } }; } sub _unverifiedFieldMapExtended { { physical => { option1 => { # 'Physical Sales' _netUnits => 'U', }, }, digital => { option1 => { # 'Digital Sales' _exploitation_description => 'M', } }, mixed => { option1 => { # 'Foreign Licensing' _netUnits => 'S', }, }, } } 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' upc => 'UPC', }, }, mixed => { option1 => { # 'Foreign Licensing' upc => 'Article Number', }, }, }; 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 album 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 subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'breakage audio stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'breakage - video stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, '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 allocations downloads' => { format_type => RPS::File::Sale::FORMAT_DOWNLOAD, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, '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, }, 'digital online vid track permanent' => { format_type => RPS::File::Sale::FORMAT_DOWNLOAD, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'ffp on demand 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, }, 'ffp user generated content stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, '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, }, 'true up - audio stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'true up - video subscr' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'true up - video stream' => { format_type => RPS::File::Sale::FORMAT_STREAM, media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, '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 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, }, ); 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, } ); my %foreignLicensingMapping = ( 'internet dwnld(esngl/edbl)' => { media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'album' => { media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'internet dwnld(ealbums)' => { media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'internet dwnld(evideos)' => { media_type => RPS::DB::Item::MediaType::kMediaTypeVideo, }, 'compact disc' => { media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'internet dwnld(emulti)' => { media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'single 12 inch' => { media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'cd/dvd combo - com/com' => { media_type => RPS::DB::Item::MediaType::kMediaTypeAudio, }, ); my %sheetRules = ( 'physical sales' => { serviceID => Client::Service::DSP_UNIVERSAL, countryCode => sub { if ( $_[1] =~ /Universal Music (\w+)/i ) { return 'KR' if ($_[1] =~ /South Korea$/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 { if ( $_[1] =~ /^All Other Licensee?s$/i ) { my $expDesc = $_[0]->_getByFieldName('_exploitation_description') || ''; return $expDesc =~ /^D\-B2B\- SoundExchange Webcasting$/i ? Client::Service::DSP_SOUNDEXCHANGE : undef; } return; }, countryCode => sub { if ( $_[1] =~ /Universal Music (\w+)/i ) { return 'KR' if ($_[1] =~ /South Korea$/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' => { serviceID => sub { if ( $_[1] =~ /(?:LSY - Synchronization|LOC - 3rd Party Compilation)/i ) { $_[0]->fail("Unsupported service '$_[1]'"); } return; }, countryCode => sub { return $_[1] =~ /^US Caroline$/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 => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'foreign licensing' => { serviceID => sub { unless ( $_[0]->isDigitalProduct( $_[0]->productType ) ) { return Client::Service::DSP_UNIVERSAL; } return; }, countryCode => sub { return 'MK' if $_[1] =~ /^NORTH MACEDONIA$/i; return 'KN' if $_[1] =~ /^ST\. KITTS\-NEVIS$/i; my $oCountry = Common::Country->Get($_[1]); return $oCountry ? $oCountry->alpha2() : undef; }, productType => sub { return RPS::File::Sale::TYPE_LP if $_[1] =~ /^(?:12[LS]P33)$/i; return RPS::File::Sale::TYPE_CD if $_[1] =~ /^5CD$/i; return RPS::File::Sale::TYPE_DVD_CD_SET if $_[1] =~ /^MXCDDVD$/i; return RPS::File::Sale::TYPE_ALBUM if $_[1] =~ /^(?:EAAHD|ESPMT|ELP|ELPB)$/i; return RPS::File::Sale::TYPE_TRACK if $_[1] =~ /^(?:ETRACK|EVSP|EVIDEO)$/i; return; }, formatType => sub { return RPS::File::Sale::FORMAT_STREAM if $_[1] =~ /^DIGITAL-STREAMS$/i; return RPS::File::Sale::FORMAT_PERFORMANCE if $_[1] =~ /^BROADCASTING$/i; return RPS::File::Sale::TYPE_PHYSICAL if $_[1] =~ /^PHYSICAL$/i; # now we have to use an extra field in pair with 'DIGITAL-OTHER' return unless $_[1] =~ /^DIGITAL-OTHER$/i; my $channel = $_[0]->_getByFieldName('channel') || ''; return RPS::File::Sale::FORMAT_TETHERED if $channel =~ /^PERMANENT DOWNLOAD$/i; return RPS::File::Sale::FORMAT_DOWNLOAD if $channel =~ /^PORTABLE SUBSCRIPTIONS$/i; return RPS::File::Sale::FORMAT_DOWNLOAD if $channel =~ /^ALLOCATION\-DOWNLOAD$/i; return RPS::File::Sale::FORMAT_STREAM if $channel =~ /^ARTIST ENGAGEMENT\/SUPERCHAT$/i; return; }, upc => sub { return $_[1] if $_[0]->productType eq RPS::File::Sale::TYPE_ALBUM; return $_[1] unless $_[0]->isDigitalProduct( $_[0]->productType ); return ''; }, albumName => sub { return $_[1] if $_[0]->productType eq RPS::File::Sale::TYPE_ALBUM; return $_[1] unless $_[0]->isDigitalProduct( $_[0]->productType ); return ''; }, isrc => sub { return $_[1] if $_[0]->productType eq RPS::File::Sale::TYPE_TRACK; return ''; }, trackName => sub { return $_[1] if $_[0]->productType eq RPS::File::Sale::TYPE_TRACK; return ''; }, channel => sub { return RPS::File::Sale::CHANNEL_RETAIL if $_[1] =~ /^NORMAL RETAIL SALES$/i; return RPS::File::Sale::CHANNEL_RETAIL if $_[1] =~ /^TV ADVERTISED \- NORMAL RETAIL$/i; return RPS::File::Sale::CHANNEL_DIRECT if $_[1] =~ /^DIRECT TO CONSUMER ACTIVITY \(PHYSICAL D2C\)$/i; return; }, 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)$/i ) { return $self->{_dateBegin}, $self->{_dateEnd} if $self->{_dateBegin} && $self->{_dateEnd}; } elsif ( $self->sheetname =~ /Foreign Licensing/i ) { my $dateBegin = $self->_getByFieldName('dateBegin') || ''; if ( my ($y, $m) = $dateBegin =~ /^(\d{4})(\d{2})$/ ) { return $self->_getDates( date_begin => sprintf("%04d-%02d-%02d", $y, $m, 1) ); } } $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') || '' ); 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->handleError( "Unknown service: '$service'", RPS::DB::Item::SaleImportError::kErrorService ); } 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 'AI' if $country =~ /^ST. KITTS-NEVIS-ANGUILLA$/i; return 'VG' if $country =~ /^BRITISH VIRGIN ISLANDS$/i; return 'PS' if $country =~ /^PALESTINIAN TERRITORY, OCCUPIE$/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 ); 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 $units = $self->_getByFieldName('units') || 0; my $price = $self->_getByFieldName('price') || 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 netUnits { $_[0]->_getByFieldName('_netUnits') || 0 } sub totalRevenue { $_[0]->_getByFieldName('totalRevenue') || 0 } sub _isValidSaleRecord { my $self = shift; my $price = $self->_getByFieldName('price') || 0; my $sheetName = $self->_sheetName; if ( $sheetName eq 'physical sales' ) { return if $self->_getByFieldName('totalRevenue') =~ /(?:Physical Sales Detail|Overall Result)/i; } elsif ( $sheetName eq 'digital sales' ) { return if ( (!$self->artistName && !$self->upc) || $price =~ /(?:Digital Sales Detail|Overall Result)/i ); } elsif ( $sheetName eq 'domestic licensing' ) { return if $price =~ /(?:Domestic Licensing Income)/i; } elsif ( $sheetName eq 'foreign licensing' ) { return if ($price =~ /(?:Amount Due)/i || !$self->_getByFieldName('dateBegin')); } return 1; } # secondary subs my %sheetToHeaderMap = ( 'physical sales' => [ 'physical', 'option1' ], 'digital sales' => [ 'digital', 'option1' ], 'domestic licensing' => [ 'digital', 'option2' ], 'foreign licensing' => [ 'mixed', 'option1' ], ); 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} } }; } 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) = @_; $self->SUPER::_processHeader(%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 col. k20 of the 'Statement$' tab if ( $self->{sheetname} =~ /^Statement$/i ) { foreach my $aLine ( @$sheet[0..50] ) { next unless ref $aLine eq 'ARRAY'; my $date = $aLine->[10] || ''; last if $self->_getPeriod($date); } } return $self->SUPER::_processSheet( $sheet, $sheetnum ); } 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' ===> 2022-05-01 - 2022-05-31 my ($p, $m, $d, $y) = $period =~ /FOR (\d+) MONTHS? ENDING (\w+) (\d{1,2}), (\d{4})/i; return unless $p; $m = $months{ lc(substr($m, 0, 3)) }; $self->{_dateBegin} = sprintf("%04d-%02d-%02d", $y, $m, 1); $self->{_dateEnd} = sprintf("%04d-%02d-%02d", $y, $m, Days_in_Month($y, $m)); return ($self->{_dateBegin}, $self->{_dateEnd}); } sub _mapFaceServiceID { 'serviceID' } sub _mapFaceProductType { } sub _mapFaceCountryCode { } sub _mapFaceFormatType { } sub _mapFaceMediaType { } 1;