package RPS::Import::RedDigital; use strict; use Date::Calc qw(Days_in_Month Add_Delta_Days Decode_Month); use lib '/app/tools/common/lib'; use Common::Consts qw(%COUNTRY_TO_CODE); use lib '/app/tools/rps/lib'; use RPS::File::Sale; use RPS::Import::ServiceMap; use RPS::Import::Importer; use base 'RPS::Import::Importer'; my %fieldMap = ( 1 => { label => 0, config => 1, artist => 2, track => 3, catalog => 4, isrc => 5, downloads => 6, downloadsp => 7, subscriptions => 8, subscriptionsp => 9, mobiledownloads => 10, mobiledownloadsp => 11, mobilesubscriptions => 12, mobilesubscriptionsp => 13, multidownloads => 14, multidownloadsp => 15, multisubscriptions => 16, multisubscriptionsp => 17, kiosk => 18, kioskp => 19, embed => 20, embedp => 21, other => 22, otherp => 23 }, 2 => { label => 0, artist => 1, album => 2, config => 3, track => 5, isrc => 6, catalog => 7, downloads => 11, downloadsp => 12, subscriptions => 13, subscriptionsp => 14, mobiledownloads => 15, mobiledownloadsp => 16, mobilesubscriptions => 17, mobilesubscriptionsp => 18, multidownloads => 19, multidownloadsp => 20, multisubscriptions => 21, multisubscriptionsp => 22, kiosk => 23, kioskp => 24, embed => 25, embedp => 26, other => 27, otherp => 28, date_col => 2, }, 3 => { provider => 0, label => 1, artist => 2, album => 3, config => 4, track => 6, isrc => 7, catalog => 8, downloads => 12, downloadsp => 13, subscriptions => 14, subscriptionsp => 15, mobiledownloads => 16, mobiledownloadsp => 17, mobilesubscriptions => 18, mobilesubscriptionsp => 19, multidownloads => 20, multidownloadsp => 21, multisubscriptions => 22, multisubscriptionsp => 23, kiosk => 24, kioskp => 25, embed => 26, embedp => 27, other => 28, otherp => 29, date_col => 2, }, 4 => { label => 0, artist => 1, album => 2, config => 3, track => 5, isrc => 6, catalog => 7, upc => 9, downloads => 12, downloadsp => 13, subscriptions => 14, subscriptionsp => 15, mobiledownloads => 16, mobiledownloadsp => 17, mobilesubscriptions => 18, mobilesubscriptionsp => 19, multidownloads => 20, multidownloadsp => 21, multisubscriptions => 22, multisubscriptionsp => 23, kiosk => 24, kioskp => 25, embed => 26, embedp => 27, other => 28, otherp => 29, date_col => 2, }, 5 => { provider => 0, label => 1, artist => 2, album => 3, config => 4, track => 6, isrc => 7, catalog => 8, date_begin => 10, downloads => 13, downloadsp => 14, subscriptions => 15, subscriptionsp => 16, mobiledownloads => 17, mobiledownloadsp => 18, mobilesubscriptions => 19, mobilesubscriptionsp => 20, multidownloads => 21, multidownloadsp => 22, multisubscriptions => 23, multisubscriptionsp => 24, kiosk => 25, kioskp => 26, embed => 27, embedp => 28, other => 29, otherp => 30, date_col => 2, }, 6 => { provider => 0, label => 1, artist => 2, album => 3, config => 4, track => 6, isrc => 7, catalog => 8, date_begin => 10, downloads => 13, downloadsp => 14, subscriptions => 15, subscriptionsp => 16, mobiledownloads => 17, mobiledownloadsp => 18, mobilesubscriptions => 19, mobilesubscriptionsp => 20, multidownloads => 21, multidownloadsp => 22, multisubscriptions => 23, multisubscriptionsp => 24, kiosk => 25, kioskp => 26, embed => 27, embedp => 28, other => 29, otherp => 30, date_col => 2, }, 7 => { provider => 0, label => 1, artist => 2, album => 3, config => 4, track => 6, isrc => 7, catalog => 8, downloads => 12, downloadsp => 13, subscriptions => 14, subscriptionsp => 15, mobiledownloads => 16, mobiledownloadsp => 17, mobilesubscriptions => 18, mobilesubscriptionsp => 19, multidownloads => 20, multidownloadsp => 21, multisubscriptions => 22, multisubscriptionsp => 23, kiosk => 24, kioskp => 25, embed => 26, embedp => 27, other => 28, otherp => 29, #footer date_col => 2, }, 8 => { #provider => 0, label => 1, artist => 2, album => 3, config => 4, track => 6, isrc => 7, #catalog => 8, downloads => 10, downloadsp => 11, subscriptions => 12, subscriptionsp => 13, mobiledownloads => 14, mobiledownloadsp => 15, mobilesubscriptions => 16, mobilesubscriptionsp => 17, multidownloads => 18, multidownloadsp => 19, multisubscriptions => 20, multisubscriptionsp => 21, kiosk => 22, kioskp => 23, embed => 24, embedp => 25, other => 26, otherp => 27, date_col => 0, }, 9 => { label => 1, artist => 2, album => 3, config => 4, track => 5, isrc => 6, downloads => 9, downloadsp => 10, subscriptions => 11, subscriptionsp => 12, mobiledownloads => 13, mobiledownloadsp => 14, mobilesubscriptions => 15, mobilesubscriptionsp => 16, multidownloads => 17, multidownloadsp => 18, multisubscriptions => 19, multisubscriptionsp => 20, kiosk => 21, kioskp => 22, embed => 23, embedp => 24, other => 25, otherp => 26, date_col => 0, }, 10 => { label => 0, album_artist => 1, artist => 4, album => 2, config => 3, track => 5, isrc => 6, catalog => 7, upc => 9, downloads => 12, downloadsp => 13, subscriptions => 14, subscriptionsp => 15, mobiledownloads => 16, mobiledownloadsp => 17, mobilesubscriptions => 18, mobilesubscriptionsp => 19, multidownloads => 20, multidownloadsp => 21, multisubscriptions => 22, multisubscriptionsp => 23, kiosk => 24, kioskp => 25, embed => 26, embedp => 27, other => 28, otherp => 29, date_col => 2, }, 11 => { label => 1, album_artist => 2, artist => 5, album => 3, config => 4, track => 6, isrc => 7, catalog => 8, #upc => , downloads => 11, downloadsp => 12, subscriptions => 13, subscriptionsp => 14, mobiledownloads => 15, mobiledownloadsp => 16, mobilesubscriptions => 17, mobilesubscriptionsp => 18, multidownloads => 19, multidownloadsp => 20, multisubscriptions => 21, multisubscriptionsp => 22, kiosk => 23, kioskp => 24, embed => 25, embedp => 26, other => 27, otherp => 28, date_col => 2, }, 12 => { label => 1, album_artist => 2, artist => 5, album => 3, config => 4, track => 6, isrc => 7, catalog => 8, #upc => , downloads => 13, downloadsp => 14, subscriptions => 15, subscriptionsp => 16, mobiledownloads => 17, mobiledownloadsp => 18, mobilesubscriptions => 19, mobilesubscriptionsp => 20, multidownloads => 21, multidownloadsp => 22, multisubscriptions => 23, multisubscriptionsp => 24, kiosk => 25, kioskp => 26, embed => 27, embedp => 28, other => 29, otherp => 30, date_col => 2, }, 14 => { label => 0, album_artist => 1, artist => 4, album => 2, config => 3, track => 5, isrc => 6, catalog => 7, upc => 9, territory => 10, downloads => 13, downloadsp => 14, subscriptions => 15, subscriptionsp => 16, mobiledownloads => 17, mobiledownloadsp => 18, mobilesubscriptions => 19, mobilesubscriptionsp => 20, multidownloads => 21, multidownloadsp => 22, multisubscriptions => 23, multisubscriptionsp => 24, kiosk => 25, kioskp => 26, embed => 27, embedp => 28, other => 29, otherp => 30, date_col => 2, }, 16 => { label => 0, album_artist => 1, artist => 4, album => 2, config => 3, track => 5, isrc => 6, catalog => 7, upc => 9, territory => 10, downloads => 12, downloadsp => 13, subscriptions => 14, subscriptionsp => 15, mobilesubscriptions => 16, mobilesubscriptionsp => 17, embed => 18, embedp => 19, other => 20, otherp => 21, date_col => 2, }, 17 => { provider => 0, label => 1, album_artist => 2, artist => 5, album => 3, config => 4, track => 6, isrc => 7, catalog => 8, territory => 10, downloads => 13, # DOWNLOAD QT downloadsp => 14, # DOWNLOAD AM streampremium => 15, # STREAM PREMIUM QT streampremiump => 16, # STREAM PREMIUM AM streamadsupported => 17, # STREAM AD SUPPORTED QT streamadsupportedp => 18, # STREAM AD SUPPORTED M embed => 19, # LOCKER QT embedp => 20, # LOCKER AM other => 21, # OTHER QT otherp => 22, # OTHER AM date_col => 2, }, 18 => { reporting_date => 0, label => 1, album_artist => 2, artist => 5, album => 3, config => 4, track => 6, isrc => 7, catalog => 8, upc => 10, downloads => 12, # DOWNLOAD QT downloadsp => 13, # DOWNLOAD AM streampremium => 14, # STREAM PREMIUM QT streampremiump => 15, # STREAM PREMIUM AM streamadsupported => 16, # STREAM AD SUPPORTED QT streamadsupportedp => 17, # STREAM AD SUPPORTED M embed => 18, # LOCKER QT embedp => 19, # LOCKER AM other => 20, # OTHER QT otherp => 21, # OTHER AM date_col => 2, }, ); # The keys in format_map are used to map Red Digital product types to # RPS format types. They're generally named something similar to what's # in the sales file (e.g., "downloads" = "ONLINE DWN"), and are used in # conjunction with the fieldMap above to determine the actual column # positions (one column for units, one for revenue). Not all importer # versions will have all of the columns listed in format_map. my %format_map = ( 'downloads' => RPS::File::Sale::FORMAT_DOWNLOAD, # ONLINE DWN 'subscriptions' => RPS::File::Sale::FORMAT_STREAM, # ONLINE SUB 'mobiledownloads' => RPS::File::Sale::FORMAT_DOWNLOAD, # MOBILE DWN 'mobilesubscriptions' => RPS::File::Sale::FORMAT_STREAM, # MOBILE SUB 'multidownloads' => RPS::File::Sale::FORMAT_DUALDOWNLOAD, # MULTIP DWN 'multisubscriptions' => RPS::File::Sale::FORMAT_STREAM, # MULTIP SUB 'kiosk' => RPS::File::Sale::FORMAT_JUKEBOX, # KIOSK 'embed' => RPS::File::Sale::FORMAT_STREAM, # LOCKER 'other' => RPS::File::Sale::FORMAT_STREAM, # OTHER 'streampremium' => RPS::File::Sale::FORMAT_STREAM, # STREAM PREMIUM 'streamadsupported' => RPS::File::Sale::FORMAT_STREAM, # STREAM AD SUPPORTED ); #public methods sub _importLines { my $self = shift; my %args = @_; my $fileObj = $args{file}; my $version = $args{file}->VersionNum(); my $fileName = $args{file}->OrigFileName(); my $serviceID = $args{file}->ServiceID; my $sheet_count = 0; return undef unless ( defined $args{sheets} && defined $version ); my $offsets = $fieldMap{$version}; my $sheet_names = $args{sheet_names}; my $header_found = 0; my $linenum = 1; my ( $dateBegin, $dateEnd ); # For version 1, get the date information from the filename # if ( $version == 1 && !( $dateBegin && $dateEnd ) ) { my $year; if ( $fileName =~ m/(\d{4})/ ) { $year = $1; } elsif ( $fileName =~ m/(\d{2})/ ) { $year = 2000 + $1; } my $month_selector = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)"; if ( lc($fileName) =~ /$month_selector/ ) { ( $dateBegin, $dateEnd ) = $self->_getDates( date_begin => $self->_formatDate( 1, $1, $year ), date_end => $self->_formatDate( 28, $1, $year ) ); } } foreach my $sheet ( @{ $args{sheets} } ) { $linenum = 1; if ( $sheet_names->[$sheet_count] !~ /summary/i ) { # For some versions, we need to look for the date information in # the header or footer area of the sales detail sheet. # if ( $version == 2 || $version == 3 || $version == 4 || $version == 7 || $version == 8 || $version == 9 || $version == 10 || $version == 11 || $version == 12 || $version == 14 || $version == 16 || $version == 17 ) { ( $dateBegin, $dateEnd ) = $self->_getDatesFromSheet( $sheet, $offsets ); } foreach my $line (@$sheet) { my $country = "US"; my $currency = "USD"; my $config = $line->[ $offsets->{config} ] if exists( $offsets->{config} ); my $artist = $line->[ $offsets->{artist} ] if exists( $offsets->{artist} ); my $catalog = $line->[ $offsets->{catalog} ] if exists( $offsets->{catalog} ); my $track = $line->[ $offsets->{track} ] if exists( $offsets->{track} ); my $album = $line->[ $offsets->{album} ] if exists( $offsets->{album} ); my $isrc = $line->[ $offsets->{isrc} ] if exists( $offsets->{isrc} ); my $upc = $line->[ $offsets->{upc} ] if exists( $offsets->{upc} ); my $label = $line->[ $offsets->{label} ] if exists( $offsets->{label} ); my $albumArtist = $line->[ $offsets->{album_artist} ] if exists( $offsets->{album_artist} ); $country = $line->[ $offsets->{territory} ] if exists( $offsets->{territory} ); # For some versions we just get the dates from each line # if ( $version == 5 || $version == 6 ) { ( $dateBegin, $dateEnd ) = $self->_getDates( date_begin => $line->[ $offsets->{date_begin} ] ); } elsif ( $version == 18 ) { if ( $line->[ $offsets->{date_begin} ] =~ /^(\d{4}-\d{2})$/ ) { ( $dateBegin, $dateEnd ) = $self->_getDates( date_begin => $1 . '-01' ); } } my $product; if ( $country && $dateBegin && $dateEnd && ( $artist || $albumArtist ) && ( $track || $album ) && ( $track ne "Title" ) && ( $track ne "TRACK TITLE" ) ) { # Set the media type # my $mediaType = RPS::DB::Item::MediaType::kMediaTypeAudio; if ( $config =~ /digital video longform/i || $config =~ /digital video track/i || $config =~ /vid/i ) # catch-all -- if it looks like video... { $mediaType = RPS::DB::Item::MediaType::kMediaTypeVideo; } # Set the product type # if ( $config =~ /digital audio bundle/i || $config =~ /digital audio longplay/i || $config =~ /digital audio extended play/i || $config =~ /digital combo longplay/i || $config =~ /digital combo ep/i || $config =~ /digital video bundle/i || $config =~ /digital video longform/i ) { $product = RPS::File::Sale::TYPE_ALBUM; $artist = $albumArtist if ($albumArtist); } elsif ( $config =~ /digital audio track/i || $config =~ /digital combo \(multiple media\)/i || $config =~ /digital video track/i || $config =~ /mastertone/i || $config =~ /digital combo bundle/i || $config =~ /ringback tone/i ) { $product = RPS::File::Sale::TYPE_TRACK; } else { $self->errstr("Unknown product configuration: '$config'\n"); return undef; } if ( $version == 1 ) { if ( $product eq RPS::File::Sale::TYPE_ALBUM ) { $album = $line->[ $offsets->{track} ]; $track = undef; } unless ( defined $dateBegin && defined $dateEnd ) { print STDERR "Couldn't get dates from: " . $fileName . "\n"; $self->errstr( "couldn't get dates from: " . $fileName ); return undef; } } else { unless ( defined $dateBegin && defined $dateEnd ) { my $sheetName = $sheet_names->[$sheet_count]; print STDERR "Couldn't get dates from sheet $sheetName\n"; $self->errstr("Couldn't get dates from sheet $sheetName\n"); return undef; } } my $serviceID; my $providerName = $line->[ $offsets->{provider} ] if ( exists $offsets->{provider} ); if ($providerName) { $serviceID = $self->getServiceID($providerName) || $RPS::Import::ServiceMap::SERVICE_ALIASES{ lc $providerName }; unless ($serviceID) { $self->errstr("Unknown service on line $linenum: '$providerName'"); return undef; } } else { $serviceID = $self->getServiceID('RedDigital'); } foreach my $format_column ( keys(%format_map) ) { if ( exists $offsets->{$format_column} && ( $line->[ $offsets->{$format_column} ] != 0 || $line->[ $offsets->{ $format_column . "p" } ] != 0 ) ) { my $units = $line->[ $offsets->{$format_column} ]; my $price = $line->[ $offsets->{ $format_column . "p" } ]; my $format = $format_map{$format_column}; # Override format for ONLINE DWN or MOBILE DWN if the config # is 'ringback tone' or 'mastertone' (FB16829). # if ( $format_column =~ /^(downloads|mobiledownloads)$/ && $config =~ /^(ringback tone|mastertone)$/i ) { $format = RPS::File::Sale::FORMAT_RINGTONE; } # Override format for soundexchange # if ( $serviceID == Client::Service::DSP_SOUNDEXCHANGE ) { $format = RPS::File::Sale::FORMAT_PERFORMANCE; } if ( !$units && $price != 0 ) { $units = 1; } $price /= $units if ( $price != 0 ); if ( $units > 0 && $price < 0 ) { $units *= -1; $price *= -1; } elsif ( $units < 0 && $price < 0 ) { $price *= -1; } my $sale = RPS::Import::SaleRec->new(); $sale->serviceID($serviceID) if ($serviceID); $sale->mediaType($mediaType); $sale->productType($product); $sale->dateBegin($dateBegin); $sale->dateEnd($dateEnd); $sale->isrc($isrc); $sale->upc($upc); $sale->serviceProductID($catalog); $sale->artistName($artist); $sale->albumName($album) if ($album); $sale->trackName($track) if ( $track && $product eq RPS::File::Sale::TYPE_TRACK ); $sale->labelName($label); $sale->formatType($format); $sale->units($units); $sale->price($price); $sale->countryCode($country); $sale->currencyCode($currency); $sale->lineNum($linenum); $self->_insertSale( sale => $sale ); } } } else { print STDERR "skipping line - sh: " . $sheet_names->[$sheet_count] . ": l: " . $linenum . " - a:" . $artist . " c:" . $country . " t:" . $track . " f:" . $config . " albumArtist:$albumArtist\n"; } $linenum++; } } $sheet_count++; } return $linenum; } # # Private methods # #----------------------------------------------------------------------- # Name: _getDatesFromSheet # # This method will find dates in column 2 (versions 2/3/4/7/10) or # column 0 (versions 8/9). # # In versions 2, 3, 4, 7, 10 and 14, the date information is located in the # footer area below the detail lines: # # Col 0 Col 1 Col 2 # -------------------- ----- -------- # REPORTING DT (Start) APR-2012 # REPORTING DT (End) APR-2012 # # In versions 8 and 9, the date is above the detail lines: # # Col 0 # ---------------- # MAY 2012 DIGITAL # #----------------------------------------------------------------------- sub _getDatesFromSheet { my ( $self, $sheet, $offsets ) = @_; my ( $_begin, $_end ); my ( $dateBegin, $dateEnd ); my $sheetHasData; my $_linenum = 1; foreach my $line (@$sheet) { # used for detecting blank sheets # $sheetHasData = 1 if ( '' ne $line->[ $offsets->{label} ] || '' ne $line->[ $offsets->{artist} ] ); if ( !$dateBegin && !$dateEnd ) { # Look for a date in first column (this would be the case for version 8 or 9) # my $c0 = $line->[0]; if ( !$_begin && $c0 && $c0 =~ m/(sep|oct|nov|dec|jan|feb|mar|apr|may|jun|jul|aug)\w* (\d{4}) digital/i ) { $_begin = $1 . "-" . $2; # MMM-YYYY $_end = $1 . "-" . $2; # MMM-YYYY } if ( $c0 && $c0 =~ m/^REPORTING DT \(Start\)$/i ) { $_begin = $line->[ $offsets->{date_col} ]; } if ( $c0 && $c0 =~ m/^REPORTING DT \(End\)$/i ) { $_end = $line->[ $offsets->{date_col} ]; } } if ( $_begin && $_end ) { # Convert to YYYY-MM-DD # ( $dateBegin, $dateEnd ) = $self->_convertDate( $_begin, $_end ); last; # dates found } } if ( !$dateBegin && !$dateEnd && $sheetHasData ) { print STDERR "Date information not found\n"; $self->errstr("Date information not found"); return undef; } return ( $dateBegin, $dateEnd ); } #_getDatesFromSheet sub _convertDate { my ( $self, $_begin, $_end ) = @_; my $dateBegin; my $dateEnd; my $beginYear; my $beginMonth; my $beginDay = 1; my $endYear; my $endMonth; my $endDay; if ( $_begin =~ m/(\w{3})-(\d{4})/ ) { $beginMonth = lc($1); $beginYear = $2; } if ( $_end =~ m/(\w{3})-(\d{4})/ ) { $endMonth = lc($1); $endYear = $2; } 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 ); my $startDate = sprintf( "%04s-%02s-%02s", $beginYear, $months{$beginMonth}, $beginDay ); $endDay = Days_in_Month( $endYear, $months{$endMonth} ); my $endDate = sprintf( "%04s-%02s-%02s", $endYear, $months{$endMonth}, $endDay ); ( $dateBegin, $dateEnd ) = $self->_getDates( date_begin => $startDate, date_end => $endDate, type => 'iso' ); return ( $dateBegin, $dateEnd ); } sub _formatDate { my $self = shift; my $day = shift; my $month = lc(shift); my $year = shift; 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 ); my $date = sprintf( "%04s-%02s-%02s", $year, $months{$month}, $day ); return $date; } ### 1; # Play nicely. ###