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