package RPS::Import::UniversalSingleSheet; 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_CODE); use lib '/app/tools/data_classes/lib'; use lib '/app/tools/rps/lib'; use RPS::File::Sale; use RPS::Import::Importer; use base 'RPS::Import::Importer'; my %fieldMap = ( 7 => { outlet => 0, format => 1, product => 2, upc => 3, artist => 4, title => 5, start_date => 6, end_date => 7, label => 9, units => 11, price => 12, }, 8 => { outlet => 0, format => 1, product => 2, isrc => 3, upc => 4, artist => 5, title => 6, start_date => 7, end_date => 8, label => 10, units => 12, price => 13, }, ); my %product_map = ( 'COMPLETE ALBUM' => RPS::File::Sale::TYPE_ALBUM, 'ALBUM' => RPS::File::Sale::TYPE_ALBUM, 'TRACK' => RPS::File::Sale::TYPE_TRACK, ); my %format_map = ( 'ad supported streams' => RPS::File::Sale::FORMAT_STREAM, 'kiosk' => RPS::File::Sale::FORMAT_DOWNLOAD, 'permanent downloads' => RPS::File::Sale::FORMAT_DOWNLOAD, 'portable sub client plays' => RPS::File::Sale::FORMAT_TETHERED, 'portable sub device plays' => RPS::File::Sale::FORMAT_TETHERED, 'portable sub streams' => RPS::File::Sale::FORMAT_STREAM, 'programmed streaming' => RPS::File::Sale::FORMAT_STREAM, 'subscription mastertones' => RPS::File::Sale::FORMAT_RINGTONE, 'streams' => RPS::File::Sale::FORMAT_STREAM, 'tethered/conditional downloads' => RPS::File::Sale::FORMAT_TETHERED, 'standard mobile ringsounds' => RPS::File::Sale::FORMAT_RINGTONE, 'ota permanent downloads' => RPS::File::Sale::FORMAT_RINGTONE, 'user generated streams (umg audio / user video)' => RPS::File::Sale::FORMAT_STREAM, 'ringbacks' => RPS::File::Sale::FORMAT_RINGBACK, 'video streams' => RPS::File::Sale::FORMAT_STREAM, # was FORMAT_VIDEOSTREAM (FB1324) 'programmed video streaming' => RPS::File::Sale::FORMAT_STREAM, # was FORMAT_VIDEOSTREAM (FB1324) 'video download' => RPS::File::Sale::FORMAT_DOWNLOAD, # was FORMAT_VIDEO (FB1324) ); #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; # jpk - Looks like these files always default to US. # my $countryCode = 'US'; return undef unless ( defined $args{sheets} && defined $version ); my $offsets = $fieldMap{$version}; my $sheet_names = $args{sheet_names}; my $linenum = 1; my ( $dateBegin, $dateEnd ); foreach my $sheet ( @{ $args{sheets} } ) { $linenum = 1; if ( $sheet_names->[$sheet_count] !~ /summary/i ) { foreach my $line (@$sheet) { my $product = $product_map{ uc( $line->[ $offsets->{product} ] ) }; my $format = $format_map{ lc( $line->[ $offsets->{format} ] ) }; my $mediaType; my $artist = $line->[ $offsets->{artist} ]; my $isrc = $line->[ $offsets->{isrc} ] if ( defined $offsets->{isrc} ); my $upc = $line->[ $offsets->{upc} ] if ( defined $offsets->{upc} ); my $title = $line->[ $offsets->{title} ]; my $units = $line->[ $offsets->{units} ]; my $price = $line->[ $offsets->{price} ]; my $label = $line->[ $offsets->{label} ]; my $free = 0; $units =~ s/\,//g; $price =~ s/\,//g; $price =~ s/\(//; $price =~ s/\)//; $price /= $units if ( $units != 0 ); ( $dateBegin, $dateEnd ) = $self->_getDates( date_begin => $line->[ $offsets->{start_date} ], date_end => $line->[ $offsets->{end_date} ] ); # $dateBegin = _getDates($line->[$offsets->{start_date}]); # $dateEnd = _getDates($line->[$offsets->{end_date}]); $free = 1 if ( $price == 0 ); my $_format = $line->[ $offsets->{format} ]; if ( $_format =~ /video/i ) { $mediaType = RPS::DB::Item::MediaType::kMediaTypeVideo; } else { $mediaType = RPS::DB::Item::MediaType::kMediaTypeAudio; } if ( $product && $format && ( $price || $free == 1 ) && $title && $label ne "GL Super Label" ) { unless ( defined $dateBegin && defined $dateEnd ) { print STDERR "Couldn't get dates from: " . $sheet->[ $offsets->{start_date} ] . "\n"; $self->errstr( "couldn't get dates from: " . $sheet->[ $offsets->{end_date} ] ); return undef; } my $has_no_sale = 0; if ( $units == 0 && $price != 0 ) { print STDERR "Found revenue correction line: " . $linenum . "\n"; $has_no_sale = 1; $units = ( $price < 0 ) ? -1 : 1; $free = 0; } $price *= -1 if ( $price < 0 && $units < 0 ); my $sale = RPS::Import::SaleRec->new(); $sale->mediaType($mediaType); $sale->productType($product); $sale->dateBegin($dateBegin); $sale->dateEnd($dateEnd); $sale->isrc($isrc) if ( defined $offsets->{isrc} ); $sale->upc($upc); $sale->artistName($artist); $sale->trackName($title) if ( $product eq RPS::File::Sale::TYPE_TRACK ); $sale->albumName($title) if ( $product eq RPS::File::Sale::TYPE_ALBUM ); $sale->labelName($label); $sale->formatType($format); $sale->units($units); $sale->price($price); $sale->free($free); $sale->countryCode($countryCode); $sale->lineNum($linenum); $self->_insertSale( sale => $sale ); if ( $has_no_sale == 1 ) { my $sale = RPS::Import::SaleRec->new(); $sale->mediaType(2) if ( $format =~ /video/i ); $sale->productType($product); $sale->dateBegin($dateBegin); $sale->dateEnd($dateEnd); $sale->isrc($isrc) if ( defined $offsets->{isrc} ); $sale->upc($upc); $sale->artistName($artist); $sale->trackName($title) if ( $product eq RPS::File::Sale::TYPE_TRACK ); $sale->albumName($title) if ( $product eq RPS::File::Sale::TYPE_ALBUM ); $sale->labelName($label); $sale->formatType($format); $sale->units(-1) if ( $units == 1 ); $sale->units(1) if ( $units == -1 ); $sale->price(0); $sale->free($free); $sale->countryCode($countryCode); $sale->lineNum($linenum); print STDERR "|>REMAP revenue only: " . $linenum . "<|\n"; $self->_insertSale( sale => $sale ); } } #else { print STDERR "skip l:". $linenum . " - u:".$units." p:".$price." t:".$title." f:".$line->[$offsets->{format}]."\n"; } $linenum++; } } $sheet_count++; } return $linenum; } # # Private methods # sub _OLD_getDates { my ( $year, $month, $day, $monthName, $qtr, $passed_date, $dateBegin, $dateEnd ); $passed_date = shift; $passed_date =~ s/\s.*$//; if ( $passed_date =~ m/(\d{4})\D(\d+)\D(\d+)/ ) { $year = $1; $month = $2; $day = $3; } elsif ( $passed_date =~ m/(\d+)\D(\d+)\D(\d{4})/ ) { $year = $3; $month = $1; $day = $2; } elsif ( $passed_date =~ m/(\d{5})/ ) { ( $year, $month, $day ) = Add_Delta_Days( 1900, 1, 1, $passed_date - 2 ); } if ( $year && $month && $day ) { return ( $year . "-" . sprintf( "%02d", $month ) . "-" . sprintf( "%02d", $day ) ); } return undef; } ### 1; # Play nicely. ###