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, }, ); # 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}]); } my $product; if ($country && ($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; } } 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. ###