package RPS::Import::Musiwave2; use strict; use Data::Dumper; use Date::Calc qw(Days_in_Month); use lib '/app/tools/common/lib'; use Common::Consts qw(%COUNTRY_TO_CODE); use Common::RSMath qw(round); use Common::Log; use lib '/app/tools/data_classes/lib'; use lib '/app/tools/rps/lib'; use RPS::File::Sale; use base 'RPS::Import::Importer'; my %field_map = ( 4 => { country => 2, title => 0, artist => 0, service_product_id => 1, label => 2, fee_percentage => 1, period => 1, min => 4, norm => 5, min_fee => 6, norm_fee => 7, total_revenue => 8, service_name => 1, service_country => 2, }, 7 => { country => 1, title => 4, artist => 3, units => 5, fee_percentage => 5, period => 1, norm => 3, norm_fee => 4, events => 5, total_revenue => 6, track_service => 1, service_name => 0, service_country => 1, service_type => 2, }, 8 => { country => 1, title => 2, artist => 3, label => 4, units => 5, fee_percentage => 5, period => 1, norm => 3, norm_fee => 4, events => 5, total_revenue => 6, track_service => 1, service_name => 0, service_country => 1, service_type => 2, }, 9 => { country => 1, title => 2, artist => 3, label => 4, units => 5, fee_percentage => 5, period => 1, norm => 3, norm_fee => 4, events => 5, total_revenue => 6, track_service => 0, service_name => 0, service_country => 1, service_type => 2, }, 10 => { country => 1, title => 4, artist => 5, label => 6, units => 7, service_product_id => 2, isrc => 3, fee_percentage => 5, period => 1, norm => 3, norm_fee => 4, events => 5, total_revenue => 6, track_service => 0, service_name => 0, service_country => 1, service_type => 2, }, 11 => { country => 1, title => 3, artist => 4, label => 5, units => 6, isrc => 2, fee_percentage => 5, period => 1, norm => 3, norm_fee => 4, events => 5, total_revenue => 6, track_service => 0, service_name => 0, service_country => 1, service_type => 2, }, ); my %countries = ( 'france' => 'FR', 'portugal' => 'PT', 'belgium' => 'BE', 'ireland' => 'IE', 'new zealand' => 'NZ', 'spain' => 'ES', 'germany' => 'DE', 'australia' => 'AU', 'switzerland' => 'CH', 'sweden' => 'SE', 'uk' => 'GB', 'united kingdom' => 'GB', 'hungary' => 'HU', 'italy' => 'IT', 'netherlands' => 'NL', 'south africa' => 'ZA', 'netherland' => 'NL', ); # # public methods # sub _importLines { my $self = shift; my %args = @_; my $lines = $args{lines}; return undef unless ($lines); my $version = $args{file}->VersionNum(); my $fileName = $args{file}->OrigFileName(); #my $version = $args{version}; # cmd-line testing #my $fileName = $args{OrigFileName}; # cmd-line testing my $sheet_names = $args{sheet_names}; my %buffer = (); my $linenum = 1; my $format = RPS::File::Sale::FORMAT_RINGTONE; my $sheet_count = 0; my $revenue_caught = 0; my $start_column = 0; my $column_count = 0; my $found_start = 0; my $last_artist; my $errors = ""; my ( @columns, $this_column, $album, $dateBegin, $dateEnd, $total_columns, $fee_percentage, $x, $y ); foreach my $sheet ( @{ $args{sheets} } ) { $linenum = 1; $linenum = 6 if ( $version > 8 && $sheet_count == 1 ); foreach my $line (@$sheet) { my $service = ""; if ( ( $sheet_count == 0 && $sheet_names->[$sheet_count] !~ /REPORTDEFS/ ) || ( $sheet_names->[$sheet_count] eq "Reporting" ) ) { $fee_percentage = $line->[ $field_map{$version}{fee_percentage} ] if ( $linenum == 9 && $version > 4 ); $fee_percentage = $line->[ $field_map{$version}{fee_percentage} ] if ( $linenum == 14 && $fee_percentage eq "" ); if ( $line->[0] =~ /Period/i ) { # ($dateBegin,$dateEnd) = _get_dates($line->[1]); ( $dateBegin, $dateEnd ) = $self->_getDates( date_begin => $line->[1] ); } elsif ( $line->[ $field_map{$version}{service_name} ] ne "" && $line->[ $field_map{$version}{service_name} ] !~ /(Period|services|country|label|currency|total)/i && $line->[ $field_map{$version}{service_name} ] !~ /\.(\d+)/ ) { my $service_name = uc( $line->[ $field_map{$version}{service_name} ] ); #$service_name =~ s/\.com//i; $service_name =~ s/(TELECOM|PLANET|FNAC|TODOXARTISTA|\.com)//ig; $service_name =~ s/\W//g; my $alt_service_name = $service_name . uc( $line->[ $field_map{$version}{service_type} ] ); my $tertiary_service_name = $service_name . uc( $line->[ $field_map{$version}{service_country} ] ); my $quarternary_service_name = $service_name . $countries{ lc $line->[ $field_map{$version}{service_country} ] }; $alt_service_name =~ s/\W//g; $tertiary_service_name =~ s/\W//g; $service_name =~ s/SAGEMBOUYGUESGALLERY/SAGEMFR/ if ( $version == 10 && $fileName !~ /2006\-08/ ); $service_name =~ s/SAGEMFRANCE/SAGEMFR/ if ( $version == 8 ); # $service_name =~ s/O2UK/O2/; # !!! Why? if ( $service_name ne "" && $revenue_caught == 0 ) { $buffer{$service_name}{'combined_revenue'} = $line->[ $field_map{$version}{total_revenue} ]; $buffer{$service_name}{'country'} = $countries{ lc $line->[ $field_map{$version}{country} ] }; $buffer{$service_name}{'unit_price'} = ( $line->[ $field_map{$version}{'norm'} ] * $fee_percentage ); $buffer{$alt_service_name}{'combined_revenue'} = $buffer{$service_name}{'combined_revenue'}; $buffer{$alt_service_name}{'country'} = $buffer{$service_name}{'country'}; $buffer{$alt_service_name}{'unit_price'} = $buffer{$service_name}{'unit_price'}; $buffer{$tertiary_service_name}{'combined_revenue'} = $line->[ $field_map{$version}{total_revenue} ]; $buffer{$tertiary_service_name}{'country'} = $buffer{$service_name}{'country'}; $buffer{$tertiary_service_name}{'unit_price'} = $buffer{$service_name}{'unit_price'}; $buffer{$quarternary_service_name}{'combined_revenue'} = $buffer{$service_name}{'combined_revenue'}; $buffer{$quarternary_service_name}{'country'} = $buffer{$service_name}{'country'}; $buffer{$quarternary_service_name}{'unit_price'} = $buffer{$service_name}{'unit_price'}; #print STDERR "|->".$service_name."<|>".$alt_service_name."<|>".$tertiary_service_name."<|>".$quarternary_service_name."<-|\n"; } } } elsif ( ( $sheet_count == 1 && ( $sheet_names->[$sheet_count] eq "MT" || $sheet_names->[$sheet_count] eq "Musitones" ) ) || ( $sheet_count == 2 && $sheet_names->[$sheet_count] eq "MT" ) || ( $sheet_count == 1 && $sheet_names->[$sheet_count] eq "Feuil2" ) ) { if ( $line->[0] !~ /(portail|portal|services)/i ) { my $artist = $line->[ $field_map{$version}{artist} ] if ( $version > 1 ); my $title = $line->[ $field_map{$version}{title} ] if ( $version > 1 ); my $service_product_id = $line->[ $field_map{$version}{service_product_id} ] if ( $field_map{$version}{service_product_id} ); my $customer_product_id = "" if ( !$field_map{$version}{service_product_id} ); my $label = $line->[ $field_map{$version}{label} ]; my $units = $line->[ $field_map{$version}{units} ]; my $found_service = uc( $line->[ $field_map{$version}{track_service} ] ); $found_service =~ s/\W//g; $found_service =~ s/(TELECOM|PLANET|FNAC|TODOXARTISTA)//g; $found_service =~ s/TELEFONICAIMODE/TELEFONICAWAP/ if ( $version == 7 && $dateBegin eq '2006-01-01' ); $found_service =~ s/O2IE/O2/; $found_service =~ s/ARTISTSITES//; my $matched_service = 0; my $possible_match = ""; if ( !$buffer{$found_service}{unit_price} ) { for my $keys ( keys %buffer ) { if ( $found_service eq $keys ) { $matched_service = 1; } elsif ( $found_service =~ /$keys/ && $matched_service == 0 ) { $buffer{$found_service}{unit_price} = $buffer{$keys}{unit_price}; $buffer{$found_service}{country} = $buffer{$keys}{country}; $buffer{$found_service}{combined_revenue} = $buffer{$keys}{combined_revenue}; $possible_match = $keys; } } } else { $matched_service = 1; } if ( $matched_service == 0 && $possible_match ne "" ) { #$found_service = $possible_match; $matched_service = 1; } my $price = $buffer{$found_service}{unit_price}; my $country = $buffer{$found_service}{country}; if ( $matched_service == 1 && $found_service ne "" && $price ne "" && $artist ne "" && $title ne "" ) { my $sale = RPS::Import::SaleRec->new(); $sale->productType(RPS::File::Sale::TYPE_TRACK); $sale->formatType($format); $sale->trackName($title); $sale->dateBegin($dateBegin); $sale->dateEnd($dateEnd); $sale->serviceProductID($service_product_id) if ($service_product_id); $sale->artistName($artist); $sale->labelName($label); $sale->price($price); $sale->units($units); $sale->currencyCode('EUR'); $sale->countryCode($country); $sale->lineNum($linenum); #$args{dumper}($sale); ## for cmd-line testing $self->_insertSale( sale => $sale ); } elsif ( $matched_service == 1 && $found_service ne "" && $artist ne "" && $title ne "" ) { # Throw an exception. # # $errors .= "Missing pricing for service: " . $found_service . " affecting " . $units . " unit(s)\n"; die "Missing pricing for service: " . $found_service . " affecting " . $units . " unit(s), line $linenum"; } # else { print STDERR "Skip-> s: " . $found_service . " u:" . $units . " a: " . $artist . " t: " . $title . " p: " . $price . "\n"; } } } $linenum++; } $sheet_count++; } $self->errstr($errors) if ( $errors ne "" ); return 1; } # # Private methods # sub _get_dates { my $passed_date = shift; return undef unless ( $passed_date =~ m/(\d{4})\D(\d+)\D(\d+)/ ); my ( $year, $month, $day ) = ( $1, $2, $3 ); return ( sprintf( "%04d-%02d-01", $year, $month ), sprintf( "%04d-%02d-%02d", $year, $month, Days_in_Month( $year, $month ) ) ); } ### 1; # Play nicely. ###