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