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. ###