package RPS::Import::Kontor; use strict; use Date::Calc qw(Days_in_Month Add_Delta_Days); use lib '/app/tools/common/lib'; use Common::Consts qw(%COUNTRY_TO_CODE); use lib '/app/tools/data_classes/lib'; use lib '/app/tools/rps/lib'; use RPS::File::Sale; use base 'RPS::Import::Importer'; # map version num to the field order my %fieldMap = ( 1 => { 'artist' => 3, 'title' => 4, 'distributor' => 5, 'format' => 6, 'country' => 7, 'isrc' => 8, 'upc' => 8, 'date' => 9, 'units' => 10, 'price' => 19, }, 2 => { 'artist' => 3, 'title' => 4, 'distributor' => 5, 'format' => 6, 'country' => 7, 'isrc' => 8, 'upc' => 8, 'date' => 9, 'units' => 10, 'price' => 19, 'label' => 20, }, 3 => { 'artist' => 3, 'title' => 4, 'distributor' => 5, 'format' => 6, 'country' => 7, 'isrc' => 8, 'upc' => 8, 'date' => 9, 'units' => 10, 'price' => 19, 'label' => 20, }, 4 => { 'label' => 1, 'upc' => 3, 'artist' => 4, 'title' => 5, 'distributor' => 7, 'format' => 9, 'country' => 11, 'isrc' => 2, 'date' => 12, 'units' => 16, 'price' => 31, }, 5 => { 'label' => 1, 'upc' => 3, 'artist' => 4, 'title' => 5, 'distributor' => 7, 'format' => 9, 'country' => 11, 'isrc' => 2, 'date' => 12, 'units' => 16, 'price' => 31, }, ); my %service_map = ( 'iTUNESSARL' => 11, 'AOLEUROPESERVICESSARL' => 17, 'T-ONLINEINTERNATIONALAG' => 42, ); my %formats = ( 'download' => RPS::File::Sale::FORMAT_DOWNLOAD, 'ringbacktones' => RPS::File::Sale::FORMAT_RINGBACK, 'ringtones' => RPS::File::Sale::FORMAT_RINGTONE, 'fulllengthdownload' => RPS::File::Sale::FORMAT_DOWNLOAD, 'alacarte' => RPS::File::Sale::FORMAT_STREAM, 'ringuptones' => RPS::File::Sale::FORMAT_RINGTONE, #'soundlogo' => RPS::File::Sale::FORMAT_GRAPHIC, # deleted (FB1324) 'bundlea' => RPS::File::Sale::FORMAT_DOWNLOAD, 'bundlec' => RPS::File::Sale::FORMAT_DOWNLOAD, 'bundlem' => RPS::File::Sale::FORMAT_DOWNLOAD, 'mono/poly' => RPS::File::Sale::FORMAT_RINGTONE, 'ringback' => RPS::File::Sale::FORMAT_RINGBACK, 'ringtonem' => RPS::File::Sale::FORMAT_RINGTONE, 'track' => RPS::File::Sale::FORMAT_DOWNLOAD, 'trackm' => RPS::File::Sale::FORMAT_DOWNLOAD, 'fulllengthdl' => RPS::File::Sale::FORMAT_DOWNLOAD, 'burnondemand' => RPS::File::Sale::FORMAT_DOWNLOAD, 'trackmobil' => RPS::File::Sale::FORMAT_DOWNLOAD, 'trackmobile' => RPS::File::Sale::FORMAT_DOWNLOAD, ); #public methods sub _importLines { my $self = shift; my %args = @_; my $fileObj = $args{file}; 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_count = 0; my $offsets = $fieldMap{$version}; my $sheet_names = $args{sheet_names}; my ($prodtype,$linenum,$label); return undef unless (defined $args{sheets} && defined $version); foreach my $sheet(@{ $args{sheets} }) { if(($version==1)||($version>3)||(($version>=2 || $version==3) && $sheet_names->[$sheet_count] eq "Details")) { $linenum = 1; foreach my $line (@$sheet) { my $free = 0; my $currency = "EUR"; $label = $line->[$offsets->{label}] if exists $offsets->{label}; my $upc = $line->[$offsets->{upc}]; my $artist = $line->[$offsets->{artist}]; my $isrc = $line->[$offsets->{isrc}]; my $title = $line->[$offsets->{title}]; my $album = $line->[$offsets->{album}] if exists $offsets->{album}; my ($units) = $line->[$offsets->{units}] =~ m/^(-?\d+)$/; my $price = $line->[$offsets->{price}]; my $format = $line->[$offsets->{format}]; my $country = $line->[$offsets->{country}]; my $svcName = $line->[$offsets->{distributor}]; $svcName =~ s/\s//g; $format =~ s/\s//g; $price =~ s/\,/\./g; $price = ($units != 0)?($price/$units):0; $country = $Common::Consts::COUNTRY_CODE{lc $country} if (length($country)>2); $prodtype = RPS::File::Sale::TYPE_TRACK; if ($offsets->{isrc} != $offsets->{upc} && $line->[$offsets->{upc}] ne "" && $line->[$offsets->{isrc}] eq "") { $album = $title; $prodtype = RPS::File::Sale::TYPE_ALBUM; $title = ""; } elsif(($offsets->{isrc} == $offsets->{upc})&& ($line->[$offsets->{isrc}] =~ /^(\d)/)) { $album = $title; $title = ""; $prodtype = RPS::File::Sale::TYPE_ALBUM; } if ($units && $artist && $format && $title ne "Title" && $title ne "Produkttitel" && $title ne "Producttitle") { my ($dateBegin, $dateEnd) = $self->_getDates($line->[$offsets->{date}]); unless (defined $dateBegin && defined $dateEnd) { $self->errstr("couldn't get dates from ".$line->[$offsets->{date}]); } my $serviceID = $self->getServiceID($svcName) || $service_map{$svcName}; #$self->errstr("Can't find service: " . $svcName) if ($serviceID eq ""); $free = 1 if($price==0 || $price eq ""); my $sale = RPS::Import::SaleRec->new(); $sale->productType($prodtype); $sale->dateBegin($dateBegin); $sale->dateEnd($dateEnd); $sale->upc($upc) if ($upc ne "" && $upc =~ /^(\d)/); $sale->isrc($isrc) if ($isrc ne "" && $isrc !~ /^(\d)/); $sale->labelName($label) if ($label ne ""); $sale->artistName($artist); $sale->trackName($title) if ($prodtype eq RPS::File::Sale::TYPE_TRACK); $sale->albumName($album) if ($prodtype eq RPS::File::Sale::TYPE_ALBUM); $sale->serviceID($serviceID) if ($serviceID ne ""); $sale->formatType($formats{lc $format}); $sale->units($units); $sale->price($price); $sale->free($free) if($free==1); $sale->countryCode($country); $sale->currencyCode($currency); $sale->lineNum($linenum); $self->_insertSale(sale => $sale); } #else { print STDERR "Skipping s:" . $sheet_names->[$sheet_count] . " l:".$linenum." u:".$units." p:".$price." t:".$title." a:".$artist." f:".$format."\n"; } $linenum++; } } $sheet_count++; } return $linenum; } # # Private methods # sub _getDates { my $self = shift; my $passed_date = shift; my ($dateBegin,$dateEnd); if($passed_date =~ /(\d{4})-(\d+)/) { my ($year,$month) = $passed_date =~ m/(\d{4})-(\d+)/; $dateBegin = sprintf("%04d-%02d-%02d", $year, $month, 1); $dateEnd = sprintf("%04d-%02d-%02d", $year, $month, Days_in_Month($year, $month)); } elsif($passed_date =~ /(\d{4})Q(\d+)/) { my ($year,$qtr) = split("Q",$passed_date); 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); return undef; } ### 1;# Play nicely. ###