package RPS::Import::ADA::v15; use strict; use warnings; use POSIX; use lib '/app/tools/rps/lib'; use RPS::Import::ServiceMap; use Date::Calc qw(Days_in_Month Add_Delta_Days Decode_Month); use RPS::DB::Item::SaleImportError; use parent qw/RPS::Import::MapFaceBase RPS::Import::ImporterMixed/; sub physical { my $self = shift; if ( $self->dataType eq 'digital' ) { return 0; } else { return 2; } } my %sheetToHeaderMap = ( 'digital' => [ 'digital', 'option1' ], 'streams' => [ 'digital', 'option2' ], 'wmi' => [ 'digital', 'option3' ], 'wireless' => [ 'digital', 'option4' ], 'downloads' => [ 'digital', 'option5' ], 'international digital' => [ 'digital', 'option6' ], 'sales' => [ 'physical', 'option1' ], 'returns' => [ 'physical', 'option2' ], 'd2c' => [ 'physical', 'option3' ], 'international physical sales' => [ 'physical', 'option4' ], 'international physical returns' => [ 'physical', 'option5' ], 'd2c physical' => [ 'physical', 'option6' ], 'sales_alternative' => [ 'physical', 'option7' ], 'sales_alternative2' => [ 'physical', 'option8' ], ); sub _fieldMapExtended { { digital => { option1 => { # Digital labelName => 'B', serviceID => 'E', productType => 'D', formatType => 'D', artistName => 'I', upc => 'F', albumName => 'P', isrc => 'G', trackName => 'J', units => 'M', price => 'O', mediaType => 'D', }, option2 => { # Streams labelName => 'B', serviceID => 'E', productType => 'H', formatType => 'D', artistName => 'I', albumName => 'O', upc => 'F', isrc => 'G', trackName => 'J', units => 'L', price => 'N', mediaType => 'D', }, option3 => { # WMI labelName => 'B', serviceID => 'E', countryCode => 'F', productType => 'D', formatType => 'D', artistName => 'G', albumName => 'H', trackName => 'H', units => 'N', price => 'P', mediaType => 'D', }, option4 => { # Wireless labelName => 'B', serviceID => 'E', productType => 'D', formatType => 'D', artistName => 'I', upc => 'F', isrc => 'G', trackName => 'J', units => 'M', price => 'O', }, option5 => { # downloads labelName => 'B', serviceID => 'E', productType => 'D', formatType => 'D', artistName => 'I', upc => 'F', albumName => 'P', isrc => 'G', trackName => 'J', units => 'M', price => 'O', mediaType => 'D', }, option6 => { # international digital labelName => 'B', serviceID => 'S', countryCode => 'F', productType => 'D', formatType => 'D', artistName => 'G', upc => 'I', albumName => 'Q', isrc => 'J', trackName => 'H', units => 'N', price => 'P', mediaType => 'D', }, }, physical => { option1 => { # Sales labelName => 'B', productType => 'D', clientProductID => 'H', artistName => 'I', upc => 'G', albumName => 'J', wholesalePrice => 'R', retailPrice => 'Q', sales => 'L', salesRevenue => 'M', totalRevenue => 'N', mediaType => 'D', }, option2 => { # Returns labelName => 'B', productType => 'D', clientProductID => 'F', artistName => 'G', upc => 'E', albumName => 'H', wholesalePrice => 'P', retailPrice => 'O', returns => 'J', returnsRevenue => 'K', totalRevenue => 'L', mediaType => 'D', }, option3 => { # D2C productType => 'C', artistName => 'I', upc => 'E', albumName => 'J', retailPrice => 'L', salesRevenue => 'N', totalRevenue => 'O', mediaType => 'C', }, option4 => { # International Physical Sales labelName => 'B', countryCode => 'E', productType => 'F', artistName => 'H', upc => 'G', albumName => 'I', totalRevenue => 'M', mediaType => 'F', }, option5 => { # International Physical Returns labelName => 'B', countryCode => 'E', productType => 'F', artistName => 'H', upc => 'G', albumName => 'I', totalRevenue => 'M', mediaType => 'F', }, option6 => { # D2C Physical labelName => 'B', productType => 'D', artistName => 'J', upc => 'F', albumName => 'K', retailPrice => 'P', totalRevenue => 'O', mediaType => 'D', }, option7 => { # Sales (with alternative header) labelName => 'B', productType => 'D', artistName => 'G', upc => 'F', albumName => 'H', wholesalePrice => 'P', retailPrice => 'O', sales => 'J', salesRevenue => 'L', totalRevenue => 'L', mediaType => 'D', }, option8 => { # Sales (with another alternative header) labelName => 'B', productType => 'D', artistName => 'H', upc => 'G', albumName => 'I', wholesalePrice => 'Q', retailPrice => 'P', sales => 'K', salesRevenue => 'M', totalRevenue => 'M', mediaType => 'D', }, }, }; } sub _unverifiedFieldMapExtended { { digital => { option1 => { _productIDTypeCode => 'H', }, option3 => { _firstRelUPC => 'I', _productIdentifier => 'J', _productIDTypeCode => 'K', }, option5 => { _productIDTypeCode => 'H', }, option6 => { _productIDTypeCode => 'K', }, }, physical => { option1 => { _netUnits => 'L', }, option2 => { _netUnits => 'J', }, option3 => { _netUnits => 'M', }, option4 => { _netUnits => 'K', }, option5 => { _netUnits => 'K', }, option6 => { _netUnits => 'M', }, option7 => { _netUnits => 'J', }, option8 => { _netUnits => 'K', }, }, }; } sub _headerIdentifierExtended { my $self = shift; my $hHeader = { digital => { option1 => { # Digital labelName => 'Label', }, option2 => { # Streams labelName => 'Label', }, option3 => { # WMI labelName => 'Label', }, option4 => { # Wireless labelName => 'Label', }, option5 => { # downloads labelName => 'Label', }, option6 => { # international digital labelName => 'Label', }, }, physical => { option1 => { # Sales artistName => 'Artist', }, option2 => { # Returns artistName => 'Artist', }, option3 => { # D2C artistName => 'Artist', }, option4 => { # International Physical Sales artistName => 'Artist', }, option5 => { # International Physical Returns artistName => 'Artist', }, option6 => { # D2C Physical artistName => 'Artist', }, option7 => { # Sales (alternative header without two columns) artistName => 'Artist', }, option8 => { # Sales (alternative header without one column) artistName => 'Artist', } }, }; my $sheetName = $self->_sheetName; # for the structure validation return $hHeader unless $sheetName; # didn't find a sheetname return {} unless exists $sheetToHeaderMap{$sheetName}; # the alternative header for the 'Sales' tab if ( $sheetName =~ /^sales$/i && $self->{_alt_sales_tab} ) { $sheetName = 'sales_alternative'; } elsif ( $sheetName =~ /^sales$/i && $self->{_alt_sales_tab2} ) { $sheetName = 'sales_alternative2'; } if ( exists $sheetToHeaderMap{$sheetName} ) { my $type = $sheetToHeaderMap{$sheetName}[0]; my $option = $sheetToHeaderMap{$sheetName}[1]; return { $type => { $option => $hHeader->{$type}{$option} } }; } } my %sheetRules = ( # digital 'digital' => { countryCode => 'US', productType => sub { return RPS::File::Sale::TYPE_TRACK if $_[1] =~ /^EMDTrack$/i; return RPS::File::Sale::TYPE_ALBUM if $_[1] =~ /^EMDAlbum$/i; # RSD-2839, additional rules, order is important if ( $_[1] =~ /^EVD$/i ) { my $productIdTypeCode = $_[0]->_getByFieldName('_productIDTypeCode') || ''; return RPS::File::Sale::TYPE_TRACK if $productIdTypeCode =~ /isrc/i; return RPS::File::Sale::TYPE_ALBUM if $productIdTypeCode =~ /upc/i; } }, formatType => sub { return RPS::File::Sale::FORMAT_DOWNLOAD if $_[1] =~ /^(?:EMDTrack|EMDAlbum|EVD)$/i; }, albumName => sub { return $_[0]->_getByFieldName('albumName'); }, isrc => sub { return $_[0]->_getByFieldName('isrc') if $_[0]->productType && $_[0]->productType eq RPS::File::Sale::TYPE_TRACK; }, trackName => sub { return $_[0]->_getByFieldName('trackName') if $_[0]->productType && $_[0]->productType eq RPS::File::Sale::TYPE_TRACK; }, free => sub { return 1 if !$_[0]->price && $_[0]->units; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ /^(?:EMDTrack|EMDAlbum)$/i; return RPS::DB::Item::MediaType::kMediaTypeVideo if $_[1] =~ /^EVD$/i; }, }, 'streams' => { countryCode => 'US', productType => sub { return RPS::File::Sale::TYPE_TRACK if $_[1] =~ qr/^ISRC$/i; return RPS::File::Sale::TYPE_ALBUM if $_[1] =~ qr/^UPC$/i; }, formatType => sub { return RPS::File::Sale::FORMAT_STREAM if $_[1] =~ qr/^(?:EMDTrack|EVD)Streaming$/i; }, albumName => sub { return $_[0]->_getByFieldName('albumName'); }, free => sub { return 1 if !$_[0]->price && $_[0]->units; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ qr/^EMDTrackStreaming$/i; return RPS::DB::Item::MediaType::kMediaTypeVideo if $_[1] =~ qr/^EVDStreaming$/i; }, }, 'wmi' => { productType => sub { return RPS::File::Sale::TYPE_ALBUM if $_[1] =~ qr/^(?:EMDAlbum|Emdalbumstreaming)$/i; return RPS::File::Sale::TYPE_TRACK if $_[1] =~ qr/^(?:eMastertone|EMDTrack|EMDTrackStreaming|eRingback|EVDStreaming)$/i; # RSD-2839, additional rules, order is important if ( $_[1] =~ /^EVD$/i ) { my $productIdTypeCode = $_[0]->_getByFieldName('_productIDTypeCode') || ''; return RPS::File::Sale::TYPE_TRACK if $productIdTypeCode =~ /isrc/i; return RPS::File::Sale::TYPE_ALBUM if $productIdTypeCode =~ /upc/i; } }, formatType => sub { return RPS::File::Sale::FORMAT_DOWNLOAD if $_[1] =~ qr/^(?:EVD|EMD(?:Album|Track))$/i; return RPS::File::Sale::FORMAT_STREAM if $_[1] =~ qr/^(?:EVDStreaming|Emdalbumstreaming|EMDTrackStreaming)$/i; return RPS::File::Sale::FORMAT_RINGTONE if $_[1] =~ qr/^(?:eRingback|eMastertone)$/i; }, upc => sub { my $_firstRelUPC = $_[0]->_getByFieldName('_firstRelUPC') || ''; my $_productIdentifier = $_[0]->_getByFieldName('_productIdentifier') || ''; my $_productIDTypeCode = $_[0]->_getByFieldName('_productIDTypeCode') || ''; return $_productIDTypeCode =~ qr/^UPC$/i ? $_productIdentifier : $_firstRelUPC; }, albumName => sub { return $_[0]->_getByFieldName('albumName') if $_[0]->productType && $_[0]->productType eq RPS::File::Sale::TYPE_ALBUM; }, isrc => sub { my $_productIdentifier = $_[0]->_getByFieldName('_productIdentifier') || ''; my $_productIDTypeCode = $_[0]->_getByFieldName('_productIDTypeCode') || ''; return $_productIdentifier if $_productIDTypeCode =~ qr/^ISRC$/i; }, trackName => sub { return $_[0]->_getByFieldName('trackName') if $_[0]->productType && $_[0]->productType eq RPS::File::Sale::TYPE_TRACK; }, free => sub { return 1 if !$_[0]->price && $_[0]->units; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ qr/^(?:eMastertone|EMD(?:Album|Track)(?:Streaming)?|eRingback)$/i; return RPS::DB::Item::MediaType::kMediaTypeVideo if $_[1] =~ qr/^EVD(?:Streaming)?$/i; }, }, 'wireless' => { countryCode => 'US', productType => sub { return RPS::File::Sale::TYPE_TRACK if $_[1] =~ qr/^(?:eMastertone|eRingback|Usergenmastertone)$/i; }, formatType => sub { return RPS::File::Sale::FORMAT_RINGTONE if $_[1] =~ qr/^(?:eMastertone|eRingback|Usergenmastertone)$/i; }, albumName => sub { return ''; }, mediaType => RPS::DB::Item::MediaType::kMediaTypeAudio, }, 'downloads' => { countryCode => 'US', productType => sub { return RPS::File::Sale::TYPE_TRACK if $_[1] =~ /^EMDTrack$/i; return RPS::File::Sale::TYPE_ALBUM if $_[1] =~ /^EMDAlbum$/i; if ( $_[1] =~ /^EVD$/i ) { my $productIdTypeCode = $_[0]->_getByFieldName('_productIDTypeCode') || ''; return RPS::File::Sale::TYPE_TRACK if $productIdTypeCode =~ /isrc/i; return RPS::File::Sale::TYPE_ALBUM if $productIdTypeCode =~ /upc/i; } }, formatType => sub { return RPS::File::Sale::FORMAT_DOWNLOAD if $_[1] =~ /^(?:EMDTrack|EMDAlbum|EVD)$/i; }, albumName => sub { return $_[0]->_getByFieldName('albumName'); }, isrc => sub { return $_[0]->_getByFieldName('isrc') if $_[0]->productType eq RPS::File::Sale::TYPE_TRACK; }, trackName => sub { return $_[0]->_getByFieldName('trackName') if $_[0]->productType && $_[0]->productType eq RPS::File::Sale::TYPE_TRACK; }, free => sub { return 1 if !$_[0]->price && $_[0]->units; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ /^(?:EMDTrack|EMDAlbum)$/i; return RPS::DB::Item::MediaType::kMediaTypeVideo if $_[1] =~ /^EVD$/i; }, }, 'international digital' => { productType => sub { return RPS::File::Sale::TYPE_TRACK if $_[1] =~ /^(?:EMDTrack|EMDTrackStreaming)$/i; return RPS::File::Sale::TYPE_ALBUM if $_[1] =~ /^EMDAlbum$/i; my $productIdTypeCode = $_[0]->_getByFieldName('_productIDTypeCode') || ''; if ( $_[1] =~ /^(?:EVD|EVDStreaming|Emdsinglestreaming)$/i ) { return RPS::File::Sale::TYPE_TRACK if $productIdTypeCode =~ /isrc/i; return RPS::File::Sale::TYPE_ALBUM if $productIdTypeCode =~ /upc/i; } }, formatType => sub { return RPS::File::Sale::FORMAT_DOWNLOAD if $_[1] =~ /^(?:EMDTrack|EMDAlbum|EVD)$/i; return RPS::File::Sale::FORMAT_STREAM if $_[1] =~ /^(?:EMDTrackStreaming|EVDStreaming|Emdsinglestreaming)$/i; }, albumName => sub { return $_[0]->_getByFieldName('albumName'); }, isrc => sub { return $_[0]->_getByFieldName('isrc') if $_[0]->productType eq RPS::File::Sale::TYPE_TRACK; }, trackName => sub { return $_[0]->_getByFieldName('trackName') if $_[0]->productType && $_[0]->productType eq RPS::File::Sale::TYPE_TRACK; }, free => sub { return 1 if !$_[0]->price && $_[0]->units; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ /^(?:EMDTrack|EMDAlbum|EMDTrackStreaming|Emdsinglestreaming)$/i; return RPS::DB::Item::MediaType::kMediaTypeVideo if $_[1] =~ /^(?:EVD|EVDStreaming)$/i; }, }, # physical 'sales' => { serviceID => Client::Service::DSP_ADA, countryCode => 'US', productType => sub { return RPS::File::Sale::TYPE_LP if $_[1] =~ qr/^(?:A|(?:MAXI|LP) VINYL)$/i; return RPS::File::Sale::TYPE_BLURAY if $_[1] =~ qr/^BD$/i; return RPS::File::Sale::TYPE_CD_SIN if $_[1] =~ qr/^C3$/i; return RPS::File::Sale::TYPE_CD if $_[1] =~ qr/^CD$/i; return RPS::File::Sale::TYPE_EP if $_[1] =~ qr/^CM$/i; return RPS::File::Sale::TYPE_CAS_SIN if $_[1] =~ qr/^CS$/i; return RPS::File::Sale::TYPE_DVD if $_[1] =~ qr/^DVD?$/i; return RPS::File::Sale::TYPE_DVD_CD_SET if $_[1] =~ qr/^(?:CX|DX|BX)$/i; return RPS::File::Sale::TYPE_LP5 if $_[1] =~ qr/^(?:S|MS)$/i; return RPS::File::Sale::TYPE_CASS if $_[1] =~ qr/^(?:TC||MC \/ TC)$/i; }, isrc => sub { return ''; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ qr/^(?:A|BD|C(?:3|D|M|S|X)|MS|S|TC|(?:MAXI|LP) VINYL|MC \/ TC)$/i; return RPS::DB::Item::MediaType::kMediaTypeVideo if $_[1] =~ qr/^(?:DV|DVD|DX|BX)$/i; }, }, 'sales_alternative' => { serviceID => Client::Service::DSP_ADA, countryCode => 'US', productType => sub { return RPS::File::Sale::TYPE_LP if $_[1] =~ qr/^(?:A|LP VINYL)$/i; return RPS::File::Sale::TYPE_BLURAY if $_[1] =~ qr/^BD$/i; return RPS::File::Sale::TYPE_CD_SIN if $_[1] =~ qr/^C3$/i; return RPS::File::Sale::TYPE_CD if $_[1] =~ qr/^CD$/i; return RPS::File::Sale::TYPE_EP if $_[1] =~ qr/^CM$/i; return RPS::File::Sale::TYPE_CAS_SIN if $_[1] =~ qr/^CS$/i; return RPS::File::Sale::TYPE_DVD if $_[1] =~ qr/^DVD?$/i; return RPS::File::Sale::TYPE_DVD_CD_SET if $_[1] =~ qr/^(?:CX|DX|BX)$/i; return RPS::File::Sale::TYPE_LP5 if $_[1] =~ qr/^(?:S|MS)$/i; return RPS::File::Sale::TYPE_CASS if $_[1] =~ qr/^(?:TC||MC \/ TC)$/i; }, isrc => sub { return ''; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ qr/^(?:A|BD|C(?:3|D|M|S|X)|MS|S|TC|LP VINYL|MC \/ TC)$/i; return RPS::DB::Item::MediaType::kMediaTypeVideo if $_[1] =~ qr/^(?:DV|DVD|DX|BX)$/i; }, }, 'sales_alternative2' => { serviceID => Client::Service::DSP_ADA, countryCode => 'US', productType => sub { return RPS::File::Sale::TYPE_LP if $_[1] =~ qr/^(?:A|LP VINYL)$/i; return RPS::File::Sale::TYPE_BLURAY if $_[1] =~ qr/^BD$/i; return RPS::File::Sale::TYPE_CD_SIN if $_[1] =~ qr/^C3$/i; return RPS::File::Sale::TYPE_CD if $_[1] =~ qr/^CD$/i; return RPS::File::Sale::TYPE_EP if $_[1] =~ qr/^CM$/i; return RPS::File::Sale::TYPE_CAS_SIN if $_[1] =~ qr/^CS$/i; return RPS::File::Sale::TYPE_DVD if $_[1] =~ qr/^DVD?$/i; return RPS::File::Sale::TYPE_DVD_CD_SET if $_[1] =~ qr/^(?:CX|DX|BX)$/i; return RPS::File::Sale::TYPE_LP5 if $_[1] =~ qr/^(?:S|MS)$/i; return RPS::File::Sale::TYPE_CASS if $_[1] =~ qr/^(?:TC||MC \/ TC)$/i; }, isrc => sub { return ''; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ qr/^(?:A|BD|C(?:3|D|M|S|X)|MS|S|TC|LP VINYL|MC \/ TC)$/i; return RPS::DB::Item::MediaType::kMediaTypeVideo if $_[1] =~ qr/^(?:DV|DVD|DX|BX)$/i; }, }, 'returns' => { serviceID => Client::Service::DSP_ADA, countryCode => 'US', productType => sub { return RPS::File::Sale::TYPE_LP if $_[1] =~ qr/^A$/i; return RPS::File::Sale::TYPE_BLURAY if $_[1] =~ qr/^BD$/i; return RPS::File::Sale::TYPE_CD_SIN if $_[1] =~ qr/^C3$/i; return RPS::File::Sale::TYPE_CD if $_[1] =~ qr/^CD$/i; return RPS::File::Sale::TYPE_EP if $_[1] =~ qr/^CM$/i; return RPS::File::Sale::TYPE_CAS_SIN if $_[1] =~ qr/^CS$/i; return RPS::File::Sale::TYPE_DVD if $_[1] =~ qr/^DV$/i; return RPS::File::Sale::TYPE_DVD_CD_SET if $_[1] =~ qr/^(?:CX|DX|BX)$/i; return RPS::File::Sale::TYPE_LP5 if $_[1] =~ qr/^(?:S|MS)$/i; return RPS::File::Sale::TYPE_CASS if $_[1] =~ qr/^TC$/i; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ qr/^(?:A|BD|C(?:3|D|M|S|X)|MS|S|TC)$/i; return RPS::DB::Item::MediaType::kMediaTypeVideo if $_[1] =~ qr/^(?:DV|DX|BX)$/i; }, }, 'd2c' => { serviceID => Client::Service::DSP_ADA, countryCode => 'US', productType => sub { return RPS::File::Sale::TYPE_LP if $_[1] =~ qr/^Vinyl$/i; return RPS::File::Sale::TYPE_CD if $_[1] =~ qr/^Cd$/i; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ qr/^(?:Vinyl|Cd)$/i; }, }, 'international physical sales' => { serviceID => Client::Service::DSP_ADA, productType => sub { return RPS::File::Sale::TYPE_CD if $_[1] =~ qr/^CD$/i; return RPS::File::Sale::TYPE_CD_SIN if $_[1] =~ qr/^CD 5-inch$/i; return RPS::File::Sale::TYPE_LP if $_[1] =~ qr/^Vinyl\s?(?:7"|12")?$/i; return RPS::File::Sale::TYPE_CASS if $_[1] =~ qr/^Tape Cassette$/i; return RPS::File::Sale::TYPE_BLURAY if $_[1] =~ qr/^(?:Blu-ray|DVDVideoBluray)$/i; return RPS::File::Sale::TYPE_DVD if $_[1] =~ qr/^DVD[- ]Video$/i; return RPS::File::Sale::TYPE_DVD_CD_SET if $_[1] =~ qr/^(?:CD with DVD|Blu-?ray ?with ?CD|DVD with CD \(A\))$/i; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ qr/^(?:CD ?(?:5-inch|with DVD)?|Blu-ray|Vinyl ?(?:7"|12")?|Tape Cassette)$/i; return RPS::DB::Item::MediaType::kMediaTypeVideo if $_[1] =~ qr/^(?:Blu-?ray ?with ?CD|DVD[- ]Video|DVDVideoBluray|DVD with CD \(A\))$/i; }, }, 'international physical returns' => { serviceID => Client::Service::DSP_ADA, productType => sub { return RPS::File::Sale::TYPE_CD if $_[1] =~ qr/^CD$/i; return RPS::File::Sale::TYPE_CD_SIN if $_[1] =~ qr/^CD 5-inch$/i; return RPS::File::Sale::TYPE_LP if $_[1] =~ qr/^(?:Vinyl|Vinyl (?:7"|12"))$/i; return RPS::File::Sale::TYPE_CASS if $_[1] =~ qr/^Tape Cassette$/i; return RPS::File::Sale::TYPE_BLURAY if $_[1] =~ qr/^(?:Blu-ray|DVDVideoBluray)$/i; return RPS::File::Sale::TYPE_DVD if $_[1] =~ qr/^DVD[- ]Video$/i; return RPS::File::Sale::TYPE_DVD_CD_SET if $_[1] =~ qr/^(?:CD with DVD|Blu-?ray ?with ?CD|DVD with CD \(A\))$/i; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ qr/^(?:CD ?(?:5-inch|with DVD)?|Blu-ray|Vinyl|Vinyl (?:7"|12")|Tape Cassette)$/i; return RPS::DB::Item::MediaType::kMediaTypeVideo if $_[1] =~ qr/^(?:Blu-?ray ?with ?CD|DVD[- ]Video|DVDVideoBluray|DVD with CD \(A\))$/i; }, }, 'd2c physical' => { serviceID => Client::Service::DSP_ADA, countryCode => 'US', productType => sub { return RPS::File::Sale::TYPE_LP if $_[1] =~ qr/^Vinyl$/i; return RPS::File::Sale::TYPE_CD if $_[1] =~ qr/^Cd$/i; }, mediaType => sub { return RPS::DB::Item::MediaType::kMediaTypeAudio if $_[1] =~ qr/^(?:Vinyl|Cd)$/i; }, } ); 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; $name =~ s/(.*)/\L$1/; return $name; } 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 currencyCode { 'USD' } sub priceLevel { RPS::File::Sale::PLEVEL_FULL } sub channel { RPS::File::Sale::CHANNEL_RETAIL } sub _getDateBegin { shift->{_dateBegin} } sub free { shift->_ruleException } sub _processSheet { my ( $self, $sheet, $sheetnum ) = @_; delete $self->{_dateBegin}; if ( exists $sheetToHeaderMap{ $self->_sheetName } ) { my $headerFound; foreach my $aLine ( @$sheet[ 0 .. 10 ] ) { next unless ref $aLine eq 'ARRAY'; my $dateHeader = $aLine->[0] || ''; $dateHeader =~ s/[^\w\s]//g; # determine date if ( $dateHeader =~ /^(\w*) (\d{4}).*$/ ) { $self->{_dateBegin} = "$1 $2" unless $self->{_dateBegin}; } # check for the alternative sales tab header my $firstCol = $dateHeader; if ( $self->_sheetName =~ /^sales$/i && $firstCol =~ /^Company$/i ) { next if ( $self->{_alt_sales_tab} || $self->{_alt_sales_tab2} ); $self->{_alt_sales_tab} = $aLine->[5] =~ /^SPG Code$/i ? 0 : 1; if ( $self->{_alt_sales_tab} && $aLine->[7] =~ /^Artist$/i ) { $self->{_alt_sales_tab} = 0; $self->{_alt_sales_tab2} = 1; } } } $headerFound = $self->SUPER::_processSheet( $sheet, $sheetnum ); if ( !$headerFound ) { $self->fail( "Unable to find header for '" . $self->{sheetname} . "' sheet" ); } return; } my @sheetsToSkip = ("Excess Storage","Label Order","Ex-US Cash Disc", "Inventory","Ex-US Expenses","Mfg","Divestment Detail", "Amazon Damage Allowance","Advertising","Return Proc", "Amazon Vinyl Packaging", "OMNIVORE", "Inbound Freight", "Amazon Freight Allowance","Physical FSMF - ex-US", "Digital FSMF - ex-US", "Spec Hdlg", "Inbound Processing"); return if grep { $self->{sheetname} =~ /^$_$/i } @sheetsToSkip; # Error out if we don't recognize the sheetname # We'll probably want to add some exceptions to this, so that # we can ignore specific sheets without throwing an error. $self->fail( "Encountered unrecognized sheet '" . $self->{sheetname} . "'" ); return; } sub serviceID { my $self = shift; my $result = $self->_ruleException(); return $result if $result; my $service = lc( $self->_getByFieldName('serviceID') || '' ); # RSD-4275 unless ($service) { $self->handleError( "Service name is blank.", RPS::DB::Item::SaleImportError::kErrorNonqualifying ); } my $serviceID = $self->getServiceID($service) || $RPS::Import::ServiceMap::SERVICE_ALIASES{$service}; return $serviceID if $serviceID; # Trying to get the Korean names to match up... ( my $serviceCopy = $service ) =~ s/(.|\n)/sprintf("%02lx", ord $1)/eg; $serviceID = $RPS::Import::ServiceMap::SERVICE_ALIASES{$serviceCopy}; return $serviceID if $serviceID; # Case 15436 - defaulting service names with incompatible characters to ADA return Client::Service::DSP_ADA if $service =~ /[[:^ascii:]]/; # RSD-3846 return Client::Service::DSP_SPOTIFY if $service =~ /^SPOTIFY/i; return Client::Service::DSP_YOUTUBE if $service =~ /^YOU\s?TUBE/i; return Client::Service::DSP_DEEZER if $service =~ /^DEEZER/i; return Client::Service::DSP_GOOGLE if $service =~ /^GOOGLE/i; return Client::Service::DSP_WIMP if $service =~ /^WIMP/i; return Client::Service::DSP_AMAZON if $service =~ /^AMAZON/i; return Client::Service::DSP_TIDAL if $service =~ /^TIDAL/i; return Client::Service::DSP_QOBUZ if $service =~ /^QOBUZ/i; return Client::Service::DSP_TENCENT_TECHNOLOGY if $service =~ /^TENCENT/i; return Client::Service::DSP_HIFI_GLOBAL if $service =~ /^HIFI\s?GLOBAL/i; return Client::Service::DSP_APPLE_MUSIC if $service =~ /^APPLE/i; return Client::Service::DSP_FRESHPLANET if $service =~ /^FRESH\s?PLANET/i; return Client::Service::DSP_VEVO if $service =~ /^VEVO/i; return Client::Service::DSP_NETEASE if $service =~ /^NETEASE/i; return Client::Service::DSP_LAGARDEREACTIVE if $service =~ /^LAGARDERE\s?ACTIVE/i; return Client::Service::DSP_REAL if $service =~ /^RHAPSODY/i; return Client::Service::DSP_UMA if $service =~ /^UMA/i; return Client::Service::DSP_IMUSICA if $service =~ /^IMUSICA/i; return Client::Service::DSP_MUSICME if $service =~ /^MUSICME/i; return $self->dataType =~ /digital/i ? $self->handleError( "Unknown service: '$service'", RPS::DB::Item::SaleImportError::kErrorService ) : $self->fail("Unable to determine service from: '$service' "); } sub countryCode { my $self = shift; my $result = $self->_ruleException(); return $result if $result; my $name = $self->_getByFieldName('countryCode') || ''; $name =~ s/^\s*|\s*$//g; my $oCountry = Common::Country->Get($name); return $oCountry->alpha2() if $oCountry; return $self->dataType =~ /digital/i ? $self->handleError( "Unknown country: '$name'.", RPS::DB::Item::SaleImportError::kErrorCountry ) : $self->fail("Unable to determine country from: '$name' "); } sub productType { my $self = shift; my $type = $self->_getByFieldName('productType') || ''; my $result = $self->_ruleException($type); return $result if $result; return $self->dataType =~ /digital/i ? $self->handleError( "Unknown product_type: '$type'.", RPS::DB::Item::SaleImportError::kErrorProductType ) : $self->fail("Unable to determine product_type from: '$type'."); } sub formatType { my $self = shift; my $type = $self->_getByFieldName('formatType') || ''; my $result = $self->_ruleException($type); return $result if $result; return $self->dataType =~ /digital/i ? $self->handleError( "Unknown format_type: '$type'.", RPS::DB::Item::SaleImportError::kErrorFormatType ) : $self->fail("Unable to determine format_type from: '$type'."); } sub upc { my $self = shift; my $result = $self->_ruleException(); return $result if defined $result; return $self->_getByFieldName('upc') || ''; } sub isrc { my $self = shift; my $result = $self->_ruleException(); return $result if defined $result; return $self->_getByFieldName('isrc') || ''; } sub trackName { my $self = shift; my $result = $self->_ruleException(); return $result if defined $result; return $self->_getByFieldName('trackName') || ''; } sub albumName { my $self = shift; my $result = $self->_ruleException(); return $result if defined $result; return $self->_getByFieldName('albumName') || ''; } sub mediaType { my $self = shift; my $type = $self->_getByFieldName('mediaType') || ''; my $result = $self->_ruleException($type); return $result if $result; return $self->dataType =~ /digital/i ? $self->handleError( "Unknown media_type: '$type'.", RPS::DB::Item::SaleImportError::kErrorMediaType ) : $self->fail("Unable to determine media_type from: '$type' "); } # digital sub price { abs( shift->_getByFieldName('price') || 0 ) } sub unitPrice { abs( shift->SUPER::unitPrice ) } sub units { my $self = shift; my $units = ceil( abs( $self->_getByFieldName('units') || 0 ) ); my $price = $self->_getByFieldName('price') || 0; if ( $price > 0 ) { $units = $units ? $units : 1; } elsif ( $price < 0 ) { $units = $units ? -1 * $units : -1; } return $units; } # physical sub netUnits { my $self = shift; my $netUnits = $self->_getByFieldName('_netUnits') || 0; return $self->totalRevenue >= 0 ? abs $netUnits : abs($netUnits) * -1; } sub totalRevenue { $_[0]->_getByFieldName('totalRevenue') || 0 } sub sales { $_[0]->netUnits > 0 ? ceil( $_[0]->netUnits ) : 0 } sub salesRevenue { $_[0]->totalRevenue > 0 ? $_[0]->totalRevenue : 0 } sub returns { $_[0]->netUnits < 0 ? abs( floor( $_[0]->netUnits ) ) : 0 } sub returnsRevenue { $_[0]->totalRevenue < 0 ? abs $_[0]->totalRevenue : 0 } # mapFace sub _mapFaceServiceID { 'serviceID' } sub _mapFaceCountryCode { 'countryCode' } sub _mapFaceProductType { 'productType' } sub _mapFaceFormatType { 'formatType' } sub _mapFaceMediaType { 'mediaType' } sub _isValidSaleRecord { # RSD-3532 if ( $_[0]->dataType =~ /physical/i ) { return if $_[0]->netUnits && !$_[0]->totalRevenue; } return ( $_[0]->_getByFieldName('trackName') || $_[0]->_getByFieldName('albumName') || $_[0]->_getByFieldName('artistName') ) && ( $_[0]->price || $_[0]->netUnits || $_[0]->units || $_[0]->totalRevenue ); } 1;