package RPS::Import::ClassicalWorld; 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/rps/lib'; use RPS::File::Sale; use base 'RPS::Import::Importer'; my %fieldMap = ( 1 => { date_row => 0, date_col => 2, streams => { catalog => 1, album => 2, track => 3, cpid => 4, units => 7, price => 8, }, downloads => { catalog => 1, album => 2, track => 3, cpid => 4, units => 7, price => 9, }, }, 2 => { date_row => 4, date_col => 3, 'purchase downloads' => { catalog => 1, isrc => 2, album => 3, track => 4, artist => 6, cpid => 7, price => 12, }, 'subscription downloads' => { catalog => 1, isrc => 2, album => 3, track => 4, artist => 6, cpid => 7, price => 11, }, streams => { catalog => 1, isrc => 2, album => 3, track => 4, artist => 6, cpid => 7, units => 10, price => 11, }, albums => { catalog => 1, album => 2, currency => 3, rate => 4, price => 8, cpid => 7, }, }, 3 => { date_row => 2, date_col => 1, streams => { catalog => 1, album => 2, track => 3, cpid => 4, units => 6, price => 7, }, downloads => { catalog => 1, album => 2, track => 3, cpid => 4, units => 6, price => 10, }, }, ); my %format_map = ( albums => RPS::File::Sale::FORMAT_DOWNLOAD, downloads => RPS::File::Sale::FORMAT_DOWNLOAD, streams => RPS::File::Sale::FORMAT_STREAM, 'purchase downloads' => RPS::File::Sale::FORMAT_DOWNLOAD, 'subscription downloads'=> RPS::File::Sale::FORMAT_TETHERED, ); 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; my $sheet_names = $args{sheet_names}; my $header_found = 0; my $linenum = 1; my ($dateBegin,$dateEnd); foreach my $sheet(@{ $args{sheets} }) { $linenum = 1; $header_found = 0; if($sheet_names->[$sheet_count] =~ /summary/i) { ($dateBegin,$dateEnd) = _getDates($sheet->[$fieldMap{$version}{date_row}]->[$fieldMap{$version}{date_col}]); unless (defined $dateBegin && defined $dateEnd) { print STDERR "Couldn't get dates from: ". $sheet->[$fieldMap{$version}{date_row}]->[$fieldMap{$version}{date_col}] . "\n"; $self->errstr("couldn't get dates from: ". $sheet->[$fieldMap{$version}{date_row}]->[$fieldMap{$version}{date_col}]); return undef; } } elsif($sheet_names->[$sheet_count] !~ /summary/i) { my $sheet_type; $offsets = $fieldMap{$version}{$sheet_names->[$sheet_count]}; foreach my $line (@$sheet) { # Header checking for version 3 if( $version == 3 && ! $header_found ) { $header_found = 1 if( $line->[0] eq 'CD ID' ); next; } my $counter_fee = 0; my $country = "GB"; my $currency = "USD"; my $product = RPS::File::Sale::TYPE_TRACK; my $format = $format_map{$sheet_names->[$sheet_count]}; my $catalog = $line->[$offsets->{catalog}]; my $cpid = $line->[$offsets->{cpid}]; my $track = $line->[$offsets->{track}]; my $album = $line->[$offsets->{album}]; my $price = $line->[$offsets->{price}]; my $units = $line->[$offsets->{units}]; my $artist = $line->[$offsets->{artist}]; my $free = 0; my $rate = $line->[$offsets->{rate}] if(defined $offsets->{rate}); $currency = "USD" if($version == 2); $currency = $line->[$offsets->{currency}] if(defined $offsets->{currency}); $units = 1 if(!defined $offsets->{units}); $units = 1 if(!defined $offsets->{units}); $price /= $units if($units != 0); $product = RPS::File::Sale::TYPE_ALBUM if($sheet_names->[$sheet_count] =~ /album/i); if(defined $offsets->{rate} && $currency ne "USD") { $currency = "USD"; } if ($country && $format && ($album || $track) && $units && $price && $track ne "trackname") { my $sale = RPS::Import::SaleRec->new(); $sale->mediaType(2) if($format =~ /video/i); $sale->productType($product); $sale->dateBegin($dateBegin); $sale->dateEnd($dateEnd); $sale->artistName($artist) if(defined $offsets->{artist}); $sale->trackName($track) if(defined $offsets->{track}); $sale->albumName($album); $sale->formatType($format); $sale->serviceProductID($catalog); $sale->clientProductID($cpid); $sale->units($units); $sale->price($price); $sale->countryCode($country); $sale->currencyCode($currency); $sale->lineNum($linenum); $self->_insertSale(sale => $sale); } #else { print STDERR "skip s:".$sheet_names->[$sheet_count]." l:" . $linenum . " - u:".$units." p:".$price." t:".$track." a: ".$album." c:".$catalog ." f:".$format."\n"; } $linenum++; } } $sheet_count++; } return $linenum; } # # Private methods # sub _getDates { my ($passed_date,$year,$qtr,$dateBegin,$dateEnd); $passed_date = shift; $passed_date = _quarterFromString( $passed_date ) if( $passed_date =~ /to/ ); if($passed_date =~ /Q(\d) (\d{4})/) { $passed_date =~ m/Q(\d) (\d{4})/; $year = $2; $qtr = $1; } elsif($passed_date =~ /(\d{4})\, Q(\d)/) { $passed_date =~ m/(\d{4})\, Q(\d)/; $year = $1; $qtr = $2; } if ($qtr == 1) { $dateBegin = $year."-01-01"; $dateEnd = $year."-03-".Days_in_Month($year, 3); } elsif ($qtr == 2) { $dateBegin = $year."-04-01"; $dateEnd = $year."-06-".Days_in_Month($year, 6); } elsif ($qtr == 3) { $dateBegin = $year."-07-01"; $dateEnd = $year."-09-".Days_in_Month($year, 9); } elsif ($qtr == 4) { $dateBegin = $year."-10-01"; $dateEnd = $year."-12-".Days_in_Month($year, 12); } else { return undef; } return ($dateBegin, $dateEnd); } sub _quarterFromString { my $date = shift; my ($begin) = split( / to /, $date ); my ($day, $month, $year ) = split( /\s+/, $begin ); my $quarter; return unless( $year ); if( $begin =~ /jan/i) { $quarter = 'Q1'; } elsif( $begin =~ /apr/i) { $quarter = 'Q2'; } elsif( $begin =~ /jul/i) { $quarter = 'Q3'; } elsif( $begin =~ /oct/) { $quarter = 'Q4'; } else { return; } return "$year, $quarter"; } ### 1;# Play nicely. ###