package RPS::Import::IODAComm; use strict; use Date::Calc qw(Days_in_Month Decode_Month); use lib '/app/tools/common/lib'; use Common::Util qw(clean); use Common::Country; use lib '/app/tools/data_classes/lib'; use Client::Service; use lib '/app/tools/rps/lib'; use RPS::File::Sale; use RPS::Import::Importer; use base 'RPS::Import::Importer'; my %service_map = ( 'Calabash Music' => Client::Service::DSP_CALABASH, 'Dancetracks Digital' => Client::Service::DSP_DANCETRACKS, 'DestraMusic.com' => Client::Service::DSP_DESTRAMUSIC, 'iTunes Music Store' => Client::Service::DSP_ITUNES, 'Mix and Burn' => Client::Service::DSP_MIXANDBURN, 'm-Qube' => Client::Service::DSP_MQUBE, 'MSN Music Service' => Client::Service::DSP_MSNMUSIC, 'RealNetworks / Rhapsody' => Client::Service::DSP_REAL, 'T-Online' => Client::Service::DSP_TONLINE, 'Virtual Music Stores' => Client::Service::DSP_VIRTUALMUSICSTORE, 'Virginmega.fr' => Client::Service::DSP_VIRGINMEGA, 'Snocap/Myspace' => Client::Service::DSP_SNOCAP, 'Napster - Streams' => Client::Service::DSP_NAPSTER, 'Rhapsody' => Client::Service::DSP_REAL, 'FNAC' => Client::Service::DSP_FNAC, 'Starbucks' => Client::Service::DSP_STARBUCKS, 'My Store' => Client::Service::DSP_MYSTORE, 'Audio Lunchbox - Streams' => Client::Service::DSP_AUDIOLUNCHBOX, 'eMusic' => Client::Service::DSP_EMUSIC, 'Sony Connect' => Client::Service::DSP_SONY, 'Fun Mobility' => Client::Service::DSP_FUNMOBILITY, 'Zingy Mobile' => Client::Service::DSP_ZINGY, 'Beatport.com' => Client::Service::DSP_BEATPORT, 'Other Music' => Client::Service::DSP_OTHERMUSIC, 'Verizon' => Client::Service::DSP_VERIZON, 'MediaNet Digital' => Client::Service::DSP_MEDIANET, 'OD2 (Loudeye)' => Client::Service::DSP_OD2, 'SecuryCast Download' => Client::Service::DSP_SECURYCAST, 'Thumbplay - **updated' => Client::Service::DSP_THUMBPLAY, 'Bell Mobility Full Track' => Client::Service::DSP_BELLMOBILITY, 'Hudson Entertainment (US)' => Client::Service::DSP_HUDSON, 'Pure Tracks' => Client::Service::DSP_PURETRACKS, 'Jamba/Jamster' => Client::Service::DSP_JAMSTER, 'Navio / gBox' => Client::Service::DSP_NAVIO, 'Bell Mobility Ringtone' => Client::Service::DSP_BELLMOBILITY, 'Mbop Digital' => Client::Service::DSP_MBOPDIGITAL, 'Slacker' => Client::Service::DSP_SLACKERINC, 'Livewire Mobile / Groove Mobile' => Client::Service::DSP_GROOVEMOBILE, 'Amazon MP3' => Client::Service::DSP_AMAZON, 'Rogers Wireless Ringtone' => Client::Service::DSP_ROGERSWIRELESS, 'm-Qube / VeriSign' => Client::Service::DSP_MQUBE, 'LiveWire / Groove Mobile' => Client::Service::DSP_LIVEWIRE, 'T-Online / Musicload' => Client::Service::DSP_TONLINE, 'Limewire A La Carte' => Client::Service::DSP_LIMEWIRE, 'Limewire Subscription' => Client::Service::DSP_LIMEWIRE, 'Limewire' => Client::Service::DSP_LIMEWIRE, 'Jamster / Jamba' => Client::Service::DSP_JAMSTER, 'Hudson Entertainment' => Client::Service::DSP_HUDSON, 'Send Me Mobile' => Client::Service::DSP_SENDME, 'Virgin Mobile USA' => Client::Service::DSP_VIRGINMOBILE, 'Snocap / MySpace' => Client::Service::DSP_SNOCAP, '9Squared / Zed' => Client::Service::DSP_NINESQUARED, '24-7 Entertainment' => Client::Service::DSP_24_7_MUSICSHOP, 'Beet / NECODO' => Client::Service::DSP_NECODO, 'Nokia (OD2/Loudeye)' => Client::Service::DSP_OD2, 'Last.fm Radio' => Client::Service::DSP_LASTFM, 'Thrill Jockey / Fina' => Client::Service::DSP_FINA, 'VidZone Digital Media' => Client::Service::DSP_VIDZONEDIGITAL, ); my %ioda_countries = ( 'all' => 'US', 'fxx' => 'FR', 'world' => 'US', 'united kingdom of great britain' => 'GB', 'americas' => 'US', 'republic of korea' => 'KR', 'cote divoire' => 'CI', 'faeroe islands' => 'FO', 'iran (islamic republic of)' => 'IR', 'grenada' => 'GD', 'british virgin islands' => 'VG', 'saint lucia' => 'LC', 'runion' => 'RE', # Reunion 'madagascar' => 'MG', 'united states virgin islands' => 'VI', 'laos (pdr)' => 'LA', 'occupied palestinian territory' => 'PS', 'haiti' => 'HT', 'united republic of tanzania' => 'TZ', 'congo (dr)' => 'CD', ); my %ioda_formats = ( 'Permanent Restricted Download' => RPS::File::Sale::FORMAT_DOWNLOAD, 'Unrestricted Dual Download' => RPS::File::Sale::FORMAT_DUALDOWNLOAD, 'Controlled Download' => RPS::File::Sale::FORMAT_TETHERED, 'Permanent Unrestricted Download' => RPS::File::Sale::FORMAT_DOWNLOAD, 'On-Demand Stream' => RPS::File::Sale::FORMAT_STREAM, 'CD Burn' => RPS::File::Sale::FORMAT_DOWNLOAD, 'Download' => RPS::File::Sale::FORMAT_DOWNLOAD, 'Promotional Use' => RPS::File::Sale::FORMAT_STREAM, 'Non-interactive Radio Stream' => RPS::File::Sale::FORMAT_STREAM, 'Interactive Radio Stream' => RPS::File::Sale::FORMAT_STREAM, 'Bonus Dispersal' => RPS::File::Sale::FORMAT_STREAM, 'Bonus Disbursal' => RPS::File::Sale::FORMAT_STREAM, 'On-Demand Stream - Ad Supported' => RPS::File::Sale::FORMAT_STREAM, 'On-Demand Stream - Free Limited' => RPS::File::Sale::FORMAT_STREAM, 'On-Demand Stream - Premium Subsc' => RPS::File::Sale::FORMAT_STREAM, 'On-Demand Stream - Premium Subscription' => RPS::File::Sale::FORMAT_STREAM, 'On-Demand Stream - Subscription' => RPS::File::Sale::FORMAT_STREAM, 'Promotional Use' => RPS::File::Sale::FORMAT_STREAM, 'Cloud Match' => RPS::File::Sale::FORMAT_STREAM, 'Stream' => RPS::File::Sale::FORMAT_STREAM, 'Ringtone' => RPS::File::Sale::FORMAT_RINGTONE, 'Ringback' => RPS::File::Sale::FORMAT_RINGTONE, ); my %field_map = ( 2 => { service => 0, country => 1, year => 2, month => 3, label => 4, artist => 5, album => 7, upc => 8, track => 9, isrc => 10, type => 11, format => 12, units => 13, price => 16, }, 3 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 5, artist => 6, album => 8, upc => 9, track => 10, isrc => 11, type => 12, format => 13, units => 14, price => 17, }, 4 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 5, track_artist => 6, artist => 7, album => 9, upc => 10, track => 11, isrc => 12, type => 13, format => 14, units => 15, price => 18, }, 5 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 5, track_artist=>6, artist => 7, svcpid => 8, album => 9, upc => 10, track => 11, isrc => 12, type => 13, format => 14, units => 16, currency=> 26, price => 30, }, 6 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 5, track_artist => 6, artist => 7, album => 9, upc => 10, track => 11, isrc => 12, type => 13, format => 14, units => 16, currency=> 25, price => 28, }, 7 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 5, track_artist=>6, artist => 7, svcpid => 8, album => 9, upc => 10, track => 11, isrc => 12, type => 13, format => 14, units => 16, currency=> 27, price => 32, }, 8 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 5, track_artist => 6, artist => 7, svcpid => 8, album => 9, upc => 10, track => 11, isrc => 12, type => 13, format => 15, units => 17, currency=> 27, price => 31, }, 9 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 5, track_artist=>6, artist => 7, svcpid => 8, album => 9, upc => 10, track => 11, isrc => 12, type => 13, format => 15, units => 17, currency=> 28, price => 33, }, 10 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 5, track_artist=>6, artist => 7, svcpid => 8, album => 9, upc => 10, track => 11, isrc => 12, type => 13, format => 15, units => 16, currency=> 23, price => 27, }, 11 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 5, track_artist=>6, artist => 7, svcpid => 8, album => 9, upc => 10, track => 11, isrc => 12, type => 13, format => 14, units => 15, currency=> 22, price => 26, }, 12 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 5, track_artist=>6, artist => 7, album => 9, upc => 10, track => 11, isrc => 12, type => 13, format => 15, units => 16, price => 26, }, 13 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 5, track_artist=>6, artist => 7, svcpid => 8, album => 9, upc => 10, track => 11, isrc => 12, type => 13, format => 15, units => 17, price => 39, }, 14 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 5, track_artist => 6, artist => 7, video_artist => 8, svcpid => 9, album => 10, upc => 11, track => 12, isrc => 13, video_track => 14, type => 17, format => 19, units => 21, currency => 36, price => 43, }, 15 => { service => 0, # A country => 2, # C year => 3, # D month => 4, # E label => 6, # G track_artist => 7, # H artist => 8, # I video_artist => 9, # J album => 11, # L upc => 12, # M track => 13, # N isrc => 14, # O video_track => 15, # P media_type => 16, # Q type => 18, # S format => 20, # U units => 21, # V currency => 33, # AH price => 40, # AO }, 16 => { service => 0, #region => 1, country => 2, year => 3, month => 4, label => 6, track_artist => 7, artist => 8, video_artist => 9, #svcpid => 9, album => 11, upc => 12, track => 13, isrc => 14, video_track => 15, type => 18, format => 20, units => 21, credits => 22, currency => 26, price => 27, }, 17 => { service => 0, # A country => 2, # C year => 3, # D month => 4, # E label => 6, # F track_artist => 7, # H artist => 8, # I album => 10, # K upc => 11, # L track => 12, # M isrc => 13, # N video_track => 14, # O media_type => 15, # P type => 17, # R format => 19, # T units => 20, # U credits => 21, # V currency => 32, # AG price => 39, # AN }, ); #public methods sub _importLines { my $self = shift; my %args = @_; my $lines = $args{lines}; my $version = $args{file}->VersionNum(); my $fileName = $args{file}->OrigFileName(); my @sheets = @{ $args{sheets} }; return undef unless ($lines); my $gross_total = 0; my $comm_total = 0; my $commission_rate = 0; my $linenum = 1; my $done_with_head = 0; my $done_with_comm = 0; my $canadian_dollar = 0; my $offsets = $field_map{$version}; my %errors = (); $canadian_dollar = 1 if($fileName =~ /^CAD_/); foreach my $sheet (@sheets){ $linenum = 1; foreach my $line(@$sheet) { #print STDERR "Processing line: $linenum\n"; if($done_with_head == 1) { my $serviceName = $line->[ $offsets->{service} ]; $serviceName =~ s/\s-\s\w+.*$//; $serviceName =~ s/\ssubscription.*$//i; $serviceName =~ s/\sdownload.*$//i; my $serviceID = $self->getServiceID($serviceName) || $service_map{$serviceName}; my $strippedServiceName = $serviceName =~ s/\s/_/g; $serviceID = $self->getServiceID($strippedServiceName) || $service_map{$strippedServiceName} if($serviceID eq ""); my $svcpid = $line->[$offsets->{svcpid}] if(defined $offsets->{svcpid}); my $artist = $line->[ $offsets->{artist} ]; if(!$artist && (defined $offsets->{track_artist})){ $artist = $line->[$offsets->{track_artist}]; } my $album = $line->[ $offsets->{album} ]; my $track = $line->[ $offsets->{track} ]; my $label = $line->[ $offsets->{label} ]; my $upc = Common::Util::normalize_upc($line->[ $offsets->{upc} ]); my $isrc = Common::Util::normalize_isrc($line->[ $offsets->{isrc} ]); my ($units) = $line->[ $offsets->{units} ]; my ($total_price) = $line->[ $offsets->{price} ]; $total_price =~ s/(\-?\$?\,)//g; ## figuring the commission here (with the larger number) to avoid rounding errors $total_price -= ($total_price * $commission_rate); my $price; if (defined $total_price && defined $units && $units != 0) { $price = sprintf("%.8f", $total_price / $units); } my $type; if ('t' eq lc $line->[ $offsets->{type} ]) { $type = RPS::File::Sale::TYPE_TRACK; } elsif ('a' eq lc $line->[ $offsets->{type} ]) { $type = RPS::File::Sale::TYPE_ALBUM; } elsif ('v' eq lc $line->[ $offsets->{type} ]) { $type = RPS::File::Sale::TYPE_ALBUM; } my $formatName = $line->[ $offsets->{format} ]; my $format = $ioda_formats{$formatName}; my $free = ($formatName =~ m/^promotional use$/i) ? 1 : 0; if(!$artist && ('v' eq lc $line->[ $offsets->{type} ]) && (defined $offsets->{video_artist})){ $artist = $line->[ $offsets->{video_artist} ]; } if(!$track && ('v' eq lc $line->[ $offsets->{type} ]) && (defined $offsets->{video_track})){ $track = $line->[ $offsets->{video_track} ]; $album = $line->[ $offsets->{video_track} ]; # FB16265 } # For version 16 # We're not sure what this is for, so let's error out if anything besides 0 shows up if (defined $offsets->{credits}) { my $credits = $line->[ $offsets->{credits} ]; if ($credits != 0) { $self->errstr("Credit count for line $linenum has a non-zero value"); return undef; } } my $mediaType = RPS::DB::Item::MediaType::kMediaTypeAudio; # default if (defined $offsets->{media_type}) { my $_mediaType = $line->[ $offsets->{media_type} ]; if( $_mediaType =~ /filmlongform/i ) { $mediaType = RPS::DB::Item::MediaType::kMediaTypeVideo; } } else { $mediaType = RPS::DB::Item::MediaType::kMediaTypeVideo if('v' eq lc $line->[ $offsets->{type} ]); } if ($units && ($track || $album) && defined $price && $type) { my $countryName = lc $line->[ $offsets->{country} ]; if ( !$countryName ) { $countryName = lc $line->[$offsets->{country}-1]; # Look in other column.. } $countryName = $ioda_countries{$countryName} || $countryName; unless ( defined $countryName && '' ne $countryName) { $self->errstr("Missing country on line $linenum"); return undef; } my $countryObj = Common::Country->Get( $countryName ); my $countryCode = $countryObj->alpha2 if ( $countryObj ); unless ( defined $countryCode ) { $self->errstr("Unrecognized country on line $linenum: $countryName"); return undef; } # JPK - I'd like to use the inherited _getDates method, intelligently. my $dateString = $line->[$offsets->{month}] . ' ' . $line->[ $offsets->{year}]; my ($dateBegin, $dateEnd); if ($dateString =~ m/^q/) { ($dateBegin, $dateEnd) = $self->_getDates(date_begin => $dateString, type => 'quarter') } else { ($dateBegin, $dateEnd) = $self->_getDates(date_begin => $dateString, type => 'text') } if($price < 0){ $price *= -1; $units = (-1) * (abs $units); } my $sale = RPS::Import::SaleRec->new(); $sale->serviceID($serviceID); $sale->mediaType($mediaType); $sale->serviceProductID($svcpid) if(defined $svcpid && $svcpid ne ""); $sale->productType($type); $sale->formatType($format); $sale->dateBegin($dateBegin); $sale->dateEnd($dateEnd); $sale->upc($upc); $sale->isrc($isrc); $sale->artistName($artist); $sale->albumName($album); $sale->trackName($track); $sale->labelName($label); $sale->units($units); $sale->price($price); $sale->lineNum($linenum); $sale->countryCode($countryCode); if (1 == $canadian_dollar) { $sale->currencyCode('CAD'); } else { $sale->currencyCode('USD'); } if(defined $offsets->{currency} && $line->[$offsets->{currency}] ne "") { $sale->currencyCode(uc($line->[$offsets->{currency}])); } # # !!! We'll let the inherited code decide how to set the conversion_rate. $sale->free($free); #$args{dumper}($sale); ## for cmd-line testing $self->_insertSale(sale => $sale); } #else { # print STDERR "skip: l:".$linenum." servName:".$serviceName. # " servID:". $serviceID." u:".$units." p:".$price."\n"; # print STDERR "units: $units track: $track album: $album price: $price type: $type artist: $artist\n"; # print STDERR " t: ".$track." al:".$album." ar:".$artist; # print STDERR " f:".$format." t:".$type . " cc: " . $countryCode; # print STDERR " db:".$dateBegin." de:".$dateEnd; # print STDERR "\n"; #} } if($done_with_head == 0 && $line->[0] =~ /Service Name/) { $done_with_head = 1; $done_with_comm = 1; $commission_rate = 0; $gross_total += $line->[@$line]; $comm_total += $line->[@$line]; } $linenum++; } } return $linenum; } # # Private methods # sub __getDates { my $self = shift; my $year = shift; my $month = shift; return undef if ($year eq '' || $month eq '' || $year > 3000 || $year < 2000); my ($dateBegin, $dateEnd); if ($month =~ m/^q([1234])$/) { my $qtr = $1; if ($qtr == 1) { $dateBegin = sprintf("%04d-%02d-%02d", $year, 1, 1); $dateEnd = sprintf("%04d-%02d-%02d", $year, 3, Days_in_Month($year, 3)); } elsif ($qtr == 2) { $dateBegin = sprintf("%04d-%02d-%02d", $year, 4, 1); $dateEnd = sprintf("%04d-%02d-%02d", $year, 6, Days_in_Month($year, 6)); } elsif ($qtr == 3) { $dateBegin = sprintf("%04d-%02d-%02d", $year, 7, 1); $dateEnd = sprintf("%04d-%02d-%02d", $year, 9, Days_in_Month($year, 9)); } else { $dateBegin = sprintf("%04d-%02d-%02d", $year, 10, 1); $dateEnd = sprintf("%04d-%02d-%02d", $year, 12, Days_in_Month($year, 12)); } return ($dateBegin, $dateEnd); } else { $month = Decode_Month($month); $dateBegin = sprintf("%04d-%02d-%02d", $year, $month, 1); $dateEnd = sprintf("%04d-%02d-%02d", $year, $month, Days_in_Month($year, $month)); return ($dateBegin, $dateEnd); } return undef; } ### 1;# Play nicely. ###