package Support::Implementation::CCLicenseTemplate; # # 2/9/11 - Initial release. # # TODO: Need to normalize the album/track lookup code use strict; use warnings; use lib '/app/tools/common/lib'; use lib '/app/tools/rps/lib'; use IO::File; use Data::Dumper; use Date::Calc qw(Add_Delta_Days Days_in_Month); use Spreadsheet::ParseExcel; use Common::Assert; use Common::UTF8; use Common::RSApp; use Common::Util qw( clean trimspaces); use RPS::License::BaseTrackLicense; use RPS::License::US::PublicDomain; use RPS::License::US::TrackLicense; use RPS::License::US::RingtoneLicense; use RPS::License::US::ControlledComposition; use RPS::License::US::TrackLicenseWithLiquidationSchedule; use RPS::DB::Item::Album; use RPS::DB::Item::Track; use RPS::DB::Item::Master; use RPS::DB::Item::TrackLicense; use RPS::DB::Item::Publisher; use RPS::DB::Item::Payor; use RPS::DB::Item::Region; use RPS::DB::Item::RegionCountryMap; use RPS::DB::Item::Product; use RPS::DB::Item::ProductType; use RPS::DB::Item::PendingTransaction; use RPS::Finance::PendingTransaction; use RPS::DB::Item::FinanceAccount; use RPS::Finance::Account; use RPS::DB::Item::RegionCountryMap; use Support::Implementation::ExcelReader; use Support::Implementation::TabDelimitedReader; #use Support::Implementation::SearchUtil; use base 'Support::Implementation::Template'; use constant kQuiet => 0; use constant kNormal => 1; use constant kDebug => 3; my $gReportLevel = kNormal; use constant kAlbumNotFound => 100; use constant kTrackNotFound => 101; use constant kPublisherNotFound => 102; use constant kNonUniqueAlbumName => 103; use constant kDuplicateTrackTitle => 104; use constant kProductTypeNotFound => 99999; binmode STDOUT, ":utf8"; #----------------------------------------------------------------------- # templateHeader maps column names to their default column number. # The actual column number-header name map is stored in columnMap. # Note(s): # 1) Column names are case sensitive # 2) Spaces are ignored; you must manually remove spaces from the names # stored in templateHeader #----------------------------------------------------------------------- my %gTemplateHeader = ( "payee-client-account#" => 0, # A "payee-name" => 1, # B "*contract-name" => 2, # C "contract-id" => 3, # D "*album-name" => 4, # E "catalog-#" => 5, # F "*track-name" => 6, # G "isrc" => 7, # H "*cc-region" => 8, # I "*cc-share-%" => 9, # J "*payor" => 10, # K "royaltyshare-publisher-id#" => 11, # L "*publisher" => 12, # M "publisher-direct" => 13, # N "issuer-license-id" => 14, # O "status" => 15, # P "license-date-sent" => 16, # Q "license-date-received" => 17, # R "license-date-issued" => 18, # S "license-term-start" => 19, # T "license-term-end" => 20, # U "royaltyshare-track-id" => 21, # V "royaltyshare-album-id" => 22, # W ); #----------------------------------------------------------------------- # gOptionalColumns -- these columns are optional (e.g., they can be left # off the template) #----------------------------------------------------------------------- my %gOptionalColumns = ( "royaltyshare-track-id" => 1, "royaltyshare-album-id" => 1, ); #-------------------------------------------------------------- # Maps column #'s to a unique key (headername). The reverse of # templateHeader, but with actual column #. #-------------------------------------------------------------- my %gColumnMap = (); #-------------------------------------------------------- # gHeaderDisplayed is a flag that we set if we've already # displayed the template header during an exceptions dump #-------------------------------------------------------- my $gHeaderDisplayed; # Reference to SearchUtil object #my $gSearchObj; my $clientID; my $execMode; my $dbo; sub new { my ($class, %args) = @_; my $self = bless {}, $class; return $self->_init(%args); } sub _init { my( $self, %args ) = @_; report("CCLicenseTemplate::_init -- args = ". Dumper(\%args)); $self->SUPER::_init(%args); $execMode = $self->exec_mode if ( $self->exec_mode ); return $self; } sub parseHeader { my $self = shift; } #-------------------------------------------------------------- # Read-in all of the template data into memory. Once it's been # read, try to parse it and create track licenses. #-------------------------------------------------------------- sub loadMemory { my $self = shift; $clientID = $self->client_id; my $app = Common::RSApp->new(clientID => $clientID); $dbo = Common::RSApp::GetClientDB(); #$gSearchObj = Support::Implementation::SearchUtil->new( # clientID => $clientID, #); my $fileName = $self->name; if( $self->isExcel2003( $fileName ) ) { print("CCLicenseTemplate::loadMemory -- loading Excel2k3 $fileName into memory\n"); # Read in the header my %data; my $reader = Support::Implementation::ExcelReader->new( filename => $fileName, data => \%data, header => \%gTemplateHeader, columnmap => \%gColumnMap ); $reader->scanExcelFile; die("Unable to import license template") if (! _validateHeader() ); #die("loadMemory: ". Dumper(\%gColumnMap) ); # Try and parse it... _processData(\%data); } elsif( $self->isTabDelimited( $fileName ) ) { report("CCLicenseTemplate::loadMemory -- processing tab-delimited file"); my %data; my $reader = Support::Implementation::TabDelimitedReader->new( filename => $fileName, data => \%data, header => \%gTemplateHeader, columnmap => \%gColumnMap ); $reader->scanTabbedFile; _processData(\%data); } } #----------------------------------------------------------------------- # _validateHeader checks for any missing columns required by the license # template (we allow 'extra' columns, but the all of the core template # columns must be present in the header. # Returns 1 if the header is valid, undef otherwise #----------------------------------------------------------------------- sub _validateHeader { my $hasErrors; #---------------------------------------------------------------------------- # actualColumns will hold the list of header columns that we actually read in #---------------------------------------------------------------------------- my %actualColumns; foreach my $k (keys %gColumnMap) { my $columnName = $gColumnMap{$k}; ++$actualColumns{$columnName}; } foreach my $col (keys %gTemplateHeader) { next if ( exists $gOptionalColumns{$col} ); if ( not exists $actualColumns{$col} ) { report("CCLicenseTemplate:_validateHeader: Missing column '$col'"); $hasErrors = 1; } } return ($hasErrors) ? undef : 1; }#_validateHeader #------------------------------------------------------------------- # _processData is where the real work is done. It takes the generic # information stored in the supplied array of hashes and decodes it. # In this case, it assumes that the supplied data contains license # data. #------------------------------------------------------------------- sub _processData { my($data) = @_; my $rows = $data->{rows}; #my @errorList = (); my $numNewLicenses = 0; my $numExceptions = 0; my $numTrackExceptions = 0; my $numAlbumExceptions = 0; my $numLockDateExceptions = 0; my $numPublisherNotFoundExceptions = 0; my $numPublisherMismatchExceptions = 0; my $numRateTypeExceptions = 0; my $numPayorExceptions = 0; my $numProductTypeExceptions = 0; my $numRateBasisExceptions = 0; my $numRegionExceptions = 0; my $numZeroShareExceptions = 0; #---------------------------------- # excount keeps track of exceptions #---------------------------------- my %excount = ( license_exists => 0, track_not_found => 0, album_not_found => 0, zero_share => 0, share_warning => 0, publisher_name_mismatch => 0, publisher_not_found => 0, bad_publisherid => 0, payor_not_found => 0, missing_rate_type => 0, missing_product_type => 0, missing_rate_basis => 0, missing_lock_date => 0, region_not_found => 0, bad_date_format => 0, invalid_digital_region => 0, mechanical_exempt => 0, isrc_too_long => 0, isrc_too_short => 0, missing_publisher_name => 0, ); #-------------------------------------- # count keeps track of entities created #-------------------------------------- my %count = ( new_licenses => 0, ); #----------------------------- # Loop over each row (license) #----------------------------- foreach my $row (@$rows) { #----------------------------------- # Get all of the template variables. #----------------------------------- my $rowid = $row->{rowid}; my $clientContractID = $row->{'contract-id'}; # this is _not_ the RS artist_contract_id my $contractName = $row->{'*contract-name'}; my $payeeClientAccountID = $row->{'payee-client-account#'}; my $payeeName = $row->{'payee-name'}; # my $ccRegion = $row->{'*cc-region'}; # my $ccShare = $row->{'*cc-share'}; my $albumName = $row->{'*album-name'}; my $catalogNumber = $row->{'catalog-#'}; my $trackName = $row->{'*track-name'}; my $isrc = $row->{'isrc'}; my $trackMechExempt = $row->{'track-mech-exempt'}; my $publicDomain = $row->{'public-domain'}; my $productType = $row->{'product-type'}; my $region = $row->{'*cc-region'}; my $share = $row->{'*cc-share-%'}; my $payor = $row->{'*payor'}; my $rsPublisherID = $row->{'royaltyshare-publisher-id#'}; my $publisherName = $row->{'*publisher'} || $row->{'publisher'}; my $publisherDirect = $row->{'publisher-direct'}; my $issuerLicenseID = $row->{'issuer-license-id'}; my $status = $row->{'status'}; my $crossed = $row->{'crossed'}; my $licenseDateSent = $row->{'license-date-sent'}; my $licenseDateReceived = $row->{'license-date-received'}; my $licenseDateIssued = $row->{'license-date-issued'}; my $licenseTermStart = $row->{'license-term-start'}; my $licenseTermEnd = $row->{'license-term-end'}; my $rateType = $row->{'rate-type'}; my $ratePercentage = $row->{'rate-%'}; my $rateBasis = $row->{'rate-basis'}; my $lockDate = $row->{'lock-date'}; my $pennyRate = $row->{'penny-rate'}; my $reservePercent = $row->{'reserve-percent'}; my $p1 = $row->{'p1'}; my $p2 = $row->{'p2'}; my $p3 = $row->{'p3'}; my $p4 = $row->{'p4'}; my $p5 = $row->{'p5'}; my $p6 = $row->{'p6'}; my $p7 = $row->{'p7'}; my $p8 = $row->{'p8'}; my $percentOfSales = $row->{'percent-of-sales'}; my $packaging = $row->{'packaging'}; my $freeGoods = $row->{'free-goods'}; my $miscDeduction = $row->{'misc-deduction'}; my $comments = $row->{'comments'}; my $balance = $row->{'license-opening-balance'}; my $rsTrackID = $row->{'royaltyshare-track-id'}; my $rsAlbumID = $row->{'royaltyshare-album-id'}; #die("STOP: row : ". Dumper(\%$row)); # Check for obsolete column names my $altRSTrackID = $row->{'rs-track-id'}; die("ERROR: found column named 'rs-track-id', expecting 'royaltyshare-track-id'") if ( $altRSTrackID ); report("#### row($rowid) ".Dumper(\%$row)); $publisherName =~ s/\s*$// if ( $publisherName ); $publisherName =~ s/^\s*// if ( $publisherName ); # strip leading/trailing spaces $albumName =~ s/\s*$// if ( $albumName ); $albumName =~ s/^\s*// if ( $albumName ); $trackName =~ s/\s*$// if ( $trackName ); $trackName =~ s/^\s*// if ( $trackName ); $contractName =~ s/\s*$// if ( $contractName ); $contractName =~ s/^\s*// if ( $contractName ); $isrc =~ s/\s*$// if ( $isrc ); $isrc =~ s/^\s*// if ( $isrc ); $catalogNumber =~ s/\s*$// if ( $catalogNumber ); $catalogNumber =~ s/^\s*// if ( $catalogNumber ); $rateBasis =~ s/^\s*// if ( $rateBasis ); $lockDate =~ s/^\s*// if ( $lockDate ); $pennyRate =~ s/^\s*// if ( $pennyRate ); $ratePercentage =~ s/^\s*// if ( $ratePercentage ); $licenseDateSent =~ s/^\s*// if ( $licenseDateSent ); $licenseDateReceived =~ s/^\s*// if ( $licenseDateReceived ); $licenseDateIssued =~ s/^\s*// if ( $licenseDateIssued ); $licenseTermStart =~ s/^\s*// if ( $licenseTermStart ); $licenseTermEnd =~ s/^\s*// if ( $licenseTermEnd ); if ( !$albumName && !$trackName && !$contractName ) { report(" >>> Skipping line $rowid"); next; } # Controlled comp defaults # $ratePercentage = 100; #------------------------------------------------ # errorCode will hold zero or more error messages #------------------------------------------------ my $errorCode; #-------------------------------------------------- # errorCode will hold zero or more warning messages #-------------------------------------------------- my $warningCode; #------------------ # Find the contract #------------------ # ..Do we have payee data? my $payeeID; if ( $payeeName || $payeeClientAccountID ) { my $sql = "SELECT artist_payee_id FROM artist_payee WHERE "; $sql .= " name = " . $dbo->DBQuote($payeeName) if ( $payeeName ); if ( $payeeClientAccountID ) { $sql .= " AND " if ( $payeeName ); $sql .= " client_account_id = " . $dbo->DBQuote($payeeClientAccountID); } my $sth = $dbo->DoCmd($sql); if ( $sth->rows > 1 ) { report("ERROR: Duplicate payee found on row $rowid"); _appendString( $errorCode, "Non-unique payee"); ++$excount{non_unique_payee}; } elsif( $sth->rows == 0 ) { report("ERROR: payee not found on row $rowid"); _appendString( $errorCode, "Payee not found"); ++$excount{payee_not_found}; } else { ($payeeID) = $sth->fetchrow_array(); report("FYI: row $rowid -- found payeeID $payeeID"); } } my $rsContractID; if ( $contractName ) { my $sql = "SELECT artist_contract_id FROM new_artist_contract " ."WHERE title = " . $dbo->DBQuote($contractName); $sql .= " AND artist_payee_id = $payeeID" if ( $payeeID ); $sql .= " AND client_contract_id = " . $dbo->DBQuote($clientContractID) if ( $clientContractID ); my $sth = $dbo->DoCmd($sql); if ( $sth->rows > 1 ) { report("ERROR: Duplicate contract found on row $rowid"); _appendString( $errorCode, "Non-unique contract"); ++$excount{non_unique_contract}; } elsif( $sth->rows == 0 ) { report("ERROR: contract not found on row $rowid"); _appendString( $errorCode, "Contract not found"); ++$excount{contract_not_found}; } else { ($rsContractID) = $sth->fetchrow_array(); report("FYI: row $rowid -- found contractID $rsContractID"); } } #-------------------------------- # Find the controlled composition clause #-------------------------------- my $controlledCompositionID; if ( $rsContractID ) { my $sql = "SELECT controlled_composition_id FROM controlled_composition " . "WHERE artist_contract_id=$rsContractID"; my $sth = $dbo->DoCmd($sql); if ( $sth->rows > 1 ) { die("oops..."); } elsif( $sth->rows == 0 ) { report("ERROR: controlled composition not found on row $rowid for contractID($rsContractID)"); _appendString( $errorCode, "Controlled comp not setup not found"); ++$excount{ccomp_not_setup}; } else { ($controlledCompositionID) = $sth->fetchrow_array(); report("FYI: row $rowid -- found ccompID($controlledCompositionID) on contractID $rsContractID"); } } if ( !$controlledCompositionID ) { report("ERROR: No controlled comp clause for contract '$contractName'"); _appendString( $errorCode, "No controlled composition clause"); ++$excount{missing_controlled_comp_clause}; } #--------------- # Find the track #--------------- my $trackID; my $errFlag; if ( $rsTrackID ) { # TODO: Check the track title? $trackID = $rsTrackID; } else { # TODO: Add rsAlbumID support to _findTrack ($trackID,$errFlag) = _findTrack( catalog_number => $catalogNumber, album_name => $albumName, track_name => $trackName, isrc => $isrc, ); } if ( $trackID ) { report("Found track $trackID"); #------------------------------------------------------------- # If license is marked track mechanical exempt, then the track # will be set to not pay or accrue mechanical royalties, and # no licenses will be created or attached. #------------------------------------------------------------- if ( $trackMechExempt && $trackMechExempt =~ /^y/i ) { my $tObj = RPS::DB::Item::Track->Lookup( track_id => $trackID ); if ( $execMode ) { $tObj->mechanical_exempt(1); $tObj->save(); report("Debug: set track $trackID to mechanical_exempt"); _appendString( $errorCode, "Mechanical Exempt"); ++$excount{mechanical_exempt}; } } } else { report("_EXCEPTION: Track not found, errFlag($errFlag)"); #push @errorList, $row; if ( $isrc ) { if ( length($isrc) > 12 ) { _appendString( $errorCode, "ISRC too long"); ++$excount{isrc_too_long}; } if ( length($isrc)>0 && length($isrc) < 12 ) { _appendString( $errorCode, "ISRC too short"); ++$excount{isrc_too_short}; } } if ( $errFlag == kTrackNotFound ) { _appendString( $errorCode, "Track not found"); ++$excount{track_not_found}; } if ( $errFlag == kAlbumNotFound ) { _appendString( $errorCode, "Album not found"); ++$excount{album_not_found}; } if ( $errFlag == kDuplicateTrackTitle ) { _appendString( $errorCode, "Duplicate track title"); ++$excount{duplicate_track_title}; } } #------------------------------------------------------------------------- # A note about error handling... # # The TrackLicense object can have errors associated with the form # fields; this is to allow errors to be propagated back to the user via # the UI. From an importer standpoint, it would be nice to have this # same ability with a cell on a given row. In the past, a single column # (named "error-code") would contain a brief message indicating why a # given row could not be imported. # # The metadata importer does something pretty cool -- it can generate # a spreadsheet of the rows containing errors, and then insert an # Excel comment on the cell. Thus, cells with errors can be readily # identified -- they'll have commments and may have cell coloring to # indicate error severity. The comment itself will indicate why the cell # is causing an error. # # To enable such functionality in the License importer, we'll need a way # to treat each cell as an object with an 'error' property. For now, this # is TBD and we'll use the "error-code" column. #------------------------------------------------------------------------- my $publisherID; my $payorID; my $regionID; my $rateTypeID; my $productTypeID; my $rateBasisID; my $inactiveID; my $crossedID; my $publisherDirectID; my $isRingtone; # set for ringtone licenses #-------------------------------------------------------------------------- # We need to make sure that publicDomain is really set (i.e. is set to "Y") #-------------------------------------------------------------------------- #$publicDomain = "" if ( $publicDomain =~ /n/i ); #$publicDomain = "" if ( $publicDomain && $publicDomain !~ /y/i ); $publicDomain = undef if ( $publicDomain && $publicDomain !~ /y/i ); if ( !$publicDomain && ( !$trackMechExempt || ($trackMechExempt && $trackMechExempt !~ m/y/i )) ) { #--------------------------------------------------------- # Check for zero share -- the TrackLicense validate method # isn't picking this up properly #--------------------------------------------------------- if ( !$share or $share == 0 ) { _appendString( $errorCode, "Zero share"); ++$excount{zero_share}; } #---------------------------------- # Make sure the share is an integer #---------------------------------- # if ( $share && ! _isInteger($share) ) { # _appendString( $errorCode, "Share must be integer"); # ++$excount{invalid_share}; # } #----------------------------- # Make sure the share is range #----------------------------- if ( $share && ( $share < 0.01 || $share > 9999 ) ) { print "SHARE_EXCEPTION: Share must be between 0.01 and 9999\n"; _appendString( $errorCode, "Share must be between 0.01 and 9999"); ++$excount{invalid_share}; } #------------------- # find the publisher #------------------- my $actualName; if ( $rsPublisherID ) { if ( $rsPublisherID !~ /^\d+$/ || $rsPublisherID == 0 ) { _appendString( $errorCode, "Bad publisherID"); ++$excount{bad_publisherid}; } else { my $e; my $pObj = RPS::DB::Item::Publisher->Lookup( publisher_id => $rsPublisherID ); if ( !$pObj ) { report("EXCEPTION: publisherID($rsPublisherID) is invalid!"); _appendString( $errorCode, "PublisherID invalid"); ++$excount{publisherid_invalid}; ++$e; } else { $actualName = $pObj->publisher_name; } #---------------------------------------------------------- # _if_ a publisher name was specified, make sure it matches #---------------------------------------------------------- if ( $publisherName && $actualName && ( (lc $actualName) ne (lc $publisherName) ) ) { report("_EXCEPTION: name_mismatch! id($rsPublisherID) " . "template($publisherName) --> actual($actualName)"); _appendString( $errorCode, "PublisherID name mismatch"); ++$excount{publisher_name_mismatch}; ++$e; } $publisherID = $rsPublisherID if ( !$e ); } } else { if ( !$publisherName ) { _appendString( $errorCode, "Missing publisher name"); ++$excount{missing_publisher_name}; } else { $publisherID = _findPublisher( publisher_name => $publisherName ); } } if ( (!$publisherID) or 0 == $publisherID ) { report("DEBUG: publisherID is zero, publisherName($publisherName)"); if ( $publisherName ) { report("_EXCEPTION: publisher '$publisherName' not found"); } else { report("_EXCEPTION: publisher not defined"); } _appendString( $errorCode, "Publisher not found"); ++$excount{publisher_not_found}; } #--------------------------------------------------------------------- # Get the payor. Note: from a TrackLicense perspective, if the # payor is blank then the default payor (a payor with is_default set) # will be used. However, the license template (1.0) specifies that # a payor must be specified in the template and that it must be valid. #--------------------------------------------------------------------- $payorID = _findPayor( payor_name => $payor ) if ( $payor ); if ( ! $payorID ) { report("_EXCEPTION: payor '$payor' not found"); _appendString( $errorCode, "Payor not found"); ++$excount{payor_not_found}; if ( $clientID == 149 ) { # XXX 149 = DAYWIND $payorID = 1; # Force payor to default } } #--------------- # Get the region #--------------- if ( $region && '' ne $region ) { $regionID = _findRegion( name => $region ); if ( !$regionID ) { #-------------------------------------------------------------- # NB: the UI will default to the region with # "is_default_mechanical_license_region". The license template # however, requires a region to be defined. #-------------------------------------------------------------- _appendString( $errorCode, "Region not found"); ++$excount{region_not_found}; } } else { _appendString( $errorCode, "Region missing"); ++$excount{region_missing}; } #------------------- # Get the rateTypeID #------------------- $rateTypeID = _getRateType( rate_type => $rateType ); if ( !$rateTypeID ) { _appendString( $errorCode, "Missing rate type"); ++$excount{missing_rate_type}; } else { #----------------------------------------------------------- # For full (us ring) or min stat licenses, a rate percentage # is required. #----------------------------------------------------------- if ( !$ratePercentage and ($rateTypeID == RPS::DB::Item::TrackLicense::kRateTypeFull or $rateTypeID == RPS::DB::Item::TrackLicense::kRateTypeMinimum) ) { _appendString( $errorCode, "Missing rate percentage"); ++$excount{missing_rate_percentage}; } #---------------------------------------------------- # For penny rate licenses, you must have a penny rate #---------------------------------------------------- if ( !$pennyRate and $rateTypeID == RPS::DB::Item::TrackLicense::kRateTypePenny ) { _appendString( $errorCode, "Missing penny rate"); ++$excount{missing_penny_rate}; } } #---------------------- # Get the productTypeID #---------------------- #$productTypeID = _getProductTypeID( product_type => $productType ); # Leave unset, this is equivalent to 'all', which is OK for controlled comp licenses (FB13263) if ( $productTypeID && kProductTypeNotFound == $productTypeID ) { _appendString( $errorCode, "Missing product type"); ++$excount{missing_product_type}; } #--------------------------------------------------------------- # Sanity check -- if the license is ALL or D, then make sure the # region is US or CA only #--------------------------------------------------------------- if ( !$productTypeID || RPS::DB::Item::Product::kProductTypeDigital == $productTypeID ) { if ( ! _validDigitalRegion($regionID) ) { _appendString( $errorCode, "Invalid digital region"); ++$excount{invalid_digital_region}; } } #------------------- # Get the rate basis #------------------- $rateBasisID = _getRateBasis( rate_basis => $rateBasis ); if ( !$rateBasisID ) { _appendString( $errorCode, "Unknown rate basis"); ++$excount{unknown_rate_basis}; } #------------------------------------------------------------ # Make sure we have a lock date for lock-date based licenses, # and that the lock date is valid. #------------------------------------------------------------ if ( $rateBasisID and RPS::DB::Item::TrackLicense::kRateBasisLock == $rateBasisID and !$lockDate ) { _appendString( $errorCode, "Missing Lock Date"); ++$excount{missing_lock_date}; } if ( $lockDate ) { #if ( $lockDate =~ m/^(\d+)$/ ) { # my $_lockDate = numericToDate( $lockDate ); # report(" Converted msft date $lockDate to $_lockDate"); # $lockDate = $_lockDate; #} $lockDate = _normalizeDate($lockDate); if ( ! _isValidDateFmt($lockDate)) { _appendString( $errorCode, "Bad lock date format"); ++$excount{bad_date_format}; } } #------------------------- # Get the publisher direct #------------------------- $publisherDirectID = _getPublisherDirect( publisher_direct => $publisherDirect ); #------------------------ # Get the inactive status #------------------------ $inactiveID = _getStatusID( inactive => $status ); #----------------- # Get the cross ID #----------------- $crossedID = _getCrossedID( crossed => $crossed ); #------------------------------------# # # # Special validation for ringtones # # # #------------------------------------# #if ( !$productTypeID || # RPS::DB::Item::Product::kProductTypeRingtone == $productTypeID ) if ( $productTypeID && RPS::DB::Item::Product::kProductTypeRingtone == $productTypeID ) { $isRingtone = 1; #---------------------------------- # Make sure rate type is 'R' or 'P' #---------------------------------- if ( $rateType !~ m/p/i and $rateType !~ m/r/i ) { _appendString( $errorCode, "Invalid ringtone rate type"); ++$excount{invalid_ringtone_rate_type}; } #------------------------------------- # Ringtones are valid only in US or CA #------------------------------------- if ( ! _validDigitalRegion($regionID) ) { _appendString( $errorCode, "Invalid ringtone region"); ++$excount{invalid_ringtone_region}; } #---------------------------------------------------- # Validate rate type (note: full stat = us ring rate) #---------------------------------------------------- # if ( $rateTypeID != RPS::DB::Item::TrackLicense::kRateTypePenny and # $rateTypeID != RPS::DB::Item::TrackLicense::kRateTypeFull ) { # # _appendString( $errorCode, "Invalid ringtone rate type"); # ++$excount{invalid_ringtone_ratetype}; # } #----------------------------------- # Ringtone licenses can't be crossed #----------------------------------- if ( $crossedID ) { _appendString( $errorCode, "Can't cross ringtone license"); ++$excount{cant_cross_ringtone}; } #---------------------------------- # Reserves not allowed on ringtones #---------------------------------- if ( $reservePercent || $p1 || $p2 || $p3 || $p4 || $p5 || $p6 || $p7 || $p8 ) { _appendString( $errorCode, "No reserves on ringtones"); ++$excount{no_reserves_on_ringtones}; } } #-------------------------------------------------- # Make sure that any dates are in the proper format #-------------------------------------------------- if ( $licenseDateSent ) { $licenseDateSent = _normalizeDate($licenseDateSent); if ( ! _isValidDateFmt($licenseDateSent)) { _appendString( $errorCode, "Bad date sent format"); ++$excount{bad_date_format}; } } if ( $licenseDateReceived ) { $licenseDateReceived = _normalizeDate($licenseDateReceived); if ( ! _isValidDateFmt($licenseDateReceived)) { _appendString( $errorCode, "Bad date received format"); ++$excount{bad_date_format}; } } if ( $licenseDateIssued ) { $licenseDateIssued = _normalizeDate($licenseDateIssued); if ( ! _isValidDateFmt($licenseDateIssued)) { _appendString( $errorCode, "Bad date issued format"); ++$excount{bad_date_format}; } } if ( $licenseTermStart ) { $licenseTermStart = _normalizeDate($licenseTermStart); if ( ! _isValidDateFmt($licenseTermStart)) { _appendString( $errorCode, "Bad term start format"); ++$excount{bad_date_format}; } } if ( $licenseTermEnd ) { $licenseTermEnd = _normalizeDate($licenseTermEnd); if ( ! _isValidDateFmt($licenseTermEnd)) { _appendString( $errorCode, "Bad term end format"); ++$excount{bad_date_format}; } } }# non-PD specific fields #------------------------------------------------------- # For PD licenses, the share can't be zero otherwise the # form validator will complain. #------------------------------------------------------- if ( $publicDomain ) { if ( !$share || $share == 0 ) { _appendString( $errorCode, "Zero PD share"); ++$excount{zero_pd_share}; die("zero PD share"); } } #------------------------------------------------------------ # If we have any errors at this point then set the errorCode. #------------------------------------------------------------ $row->{'error-code'} = $errorCode; #------------------------------------------------------------------- # Are there any exceptions that the form validation won't pickup? # If so, stop processing this license. # # * If we don't have a trackID then we're done (the form validator # requires a trackID) # * If this is a regular publishing license and we don't have a # publisherID then we're done. # * If this is a regular publishing license and we don't have a # share then we're done. # * If this is a regular publishing license and we don't have a # valid product type then we're done. #------------------------------------------------------------------- if ( !$trackID || (!$publisherID && (!$publicDomain)) || (!$publicDomain && (!$share or $share == 0) ) || #(!$publicDomain && $share && ! _isInteger($share)) || (!$publicDomain && ($share && ( $share < 0.01 || $share > 9999 ) )) || (!$publicDomain && !$regionID ) || (!$publicDomain && !$payorID ) || (!$publicDomain && !$rateTypeID ) || (!$publicDomain && $rateTypeID && RPS::DB::Item::TrackLicense::kRateTypePenny == $rateTypeID && !$pennyRate ) || (!$publicDomain && $productTypeID && ($productTypeID == kProductTypeNotFound ) ) ) { next; }; #die("ERROR: uncaught error condition exists! errorCode: $errorCode") if ( $errorCode ); # XXX 1/12/11 if ( $errorCode ) { report("WARNING: uncaught error condition exists! errorCode: $errorCode"); next; } #=========================================== # Ok, go ahead and create the license object #=========================================== #---------------------------------------------------------- # Check if the license already exists. #---------------------------------------------------------- my %tlArgs = ( track_id => $trackID ); # # The region from the template may refer to multiple regions # as defined by region_country_map; thus when checking for # existing licenses we need to check for licenses on countries # defined by region_country_map. -ES 8/13/10 # # get the country codes related to the license we're trying to import # my $myCountryCodeMap = RPS::DB::Item::RegionCountryMap->GetByRegionID( $regionID ); #TODO: You are here. See RPS/License for validation logic !!! # my @regionMap; # push @regionMap, $regionID; if ( $publicDomain ) { $tlArgs{type} = RPS::DB::Item::TrackLicense::kPublicDomain; } else { #if ( $isRingtone ) { # $tlArgs{type} = RPS::DB::Item::TrackLicense::kRingtone; #} else { # $tlArgs{type} = RPS::DB::Item::TrackLicense::kPublishingLicense; #} $tlArgs{type} = RPS::DB::Item::TrackLicense::kControlledComposition; # FB13263 $tlArgs{publisher_id} = $publisherID; $tlArgs{region_id} = $regionID; $tlArgs{product_type_id} = $productTypeID if ( $productTypeID ); #$tlArgs{payor_id} = $payorID; } my $tlObj = RPS::DB::Item::TrackLicense->Lookup( %tlArgs ); if ( $tlObj ) { my $licID = $tlObj->track_license_id; report(" WARNING: license $licID exists: ". Dumper(\%tlArgs)); ++$excount{license_exists}; _appendString( $errorCode, "License Exists"); $row->{'error-code'} = $errorCode; next; } #-------------------------------------------------------------- # Create a RPS::License::TrackLicense object so we can validate # the license information using the form validation logic #-------------------------------------------------------------- my %licArgs = ( _loadSubs => 1, trackID => $trackID, controlledComposition => 1, # FB13263 ); if ( $publicDomain ) { die("PD not here..."); report("PD detected"); $licArgs{publicDomain} = 1; report("PD debug: " . Dumper(\%licArgs)); } #........................................................... # Note: the object will default to kPublishingLicense if the # publicDomain flag is omitted during instantiation #........................................................... my $newLicense; my $financeAccount; # set only if there's a balance my $validPendingTransaction = 0; # true if PendingTransaction is valid my $validPendingTransactionList = 0; # true if PendingTransactionList is valid my $validFinanceAccount = 0; # true if Account is valid my $validReserveLiquidation = 0; # true if ReserveLiquidation is valid if ( ! $publicDomain ) { $newLicense = RPS::License::US::ControlledComposition->new( %licArgs ); # XXX XXX XXX # if ( $isRingtone ) { # $newLicense = RPS::License::US::RingtoneLicense->new( %licArgs ); # } else { # $newLicense = RPS::License::US::TrackLicenseWithLiquidationSchedule->new( %licArgs ); # } #$newLicense = RPS::License::US::TrackLicenseWithLiquidationSchedule->new( %licArgs ); assert($controlledCompositionID); # FB13263 $newLicense->ControlledCompositionID($controlledCompositionID); $newLicense->Inactive($inactiveID); $newLicense->PublisherID($publisherID) if $publisherID; $newLicense->Share($share) if $share; $newLicense->PayorID($payorID) if $payorID; $newLicense->RegionID($regionID) if $regionID; # $newLicense->RateType($rateTypeID) if $rateTypeID; $newLicense->PennyRate($pennyRate) if ( RPS::DB::Item::TrackLicense::kRateTypePenny == $rateTypeID ); $newLicense->RatePercentage($ratePercentage) if ( RPS::DB::Item::TrackLicense::kRateTypePenny != $rateTypeID && $ratePercentage); # $newLicense->ProductTypeID($productTypeID); # $newLicense->RateBasis($rateBasisID); $newLicense->ReservePercentage($reservePercent) if ( $reservePercent ); if ( $rateBasisID == RPS::DB::Item::TrackLicense::kRateBasisLock ) { $newLicense->LockDate($lockDate); } $newLicense->IssuerLicenseID($issuerLicenseID); $newLicense->Comments($comments); # $newLicense->MiscDeduction($miscDeduction); # $newLicense->FreeGoods($freeGoods); $newLicense->DateSent($licenseDateSent) if ($licenseDateSent ); $newLicense->DateReceived($licenseDateReceived) if ($licenseDateReceived ); $newLicense->DateIssued($licenseDateIssued) if ($licenseDateIssued ); $newLicense->TermStart($licenseTermStart) if ($licenseTermStart ); $newLicense->TermEnd($licenseTermEnd) if ($licenseTermEnd ); # $newLicense->CrossCollateralized($crossedID); $newLicense->PublisherDirect($publisherDirectID); #------------------------- # Set the reserve schedule #------------------------- # if ( !$isRingtone ) { # my $reserveLiquidationList = $newLicense->{ReserveLiquidationList}; # my $reserveArray = $reserveLiquidationList->getList(); # # _setReserve(1,$p1,\%{$reserveArray->[0]} ); # _setReserve(2,$p2,\%{$reserveArray->[1]} ); # _setReserve(3,$p3,\%{$reserveArray->[2]} ); # _setReserve(4,$p4,\%{$reserveArray->[3]} ); # _setReserve(5,$p5,\%{$reserveArray->[4]} ); # _setReserve(6,$p6,\%{$reserveArray->[5]} ); # _setReserve(7,$p7,\%{$reserveArray->[6]} ); # _setReserve(8,$p8,\%{$reserveArray->[7]} ); # $validReserveLiquidation = $reserveLiquidationList->validate(); # } #----------------------------------------------------------- # If there's a balance, we need to setup the finance account #----------------------------------------------------------- if ( $balance ) { $financeAccount = $newLicense->{FinanceAccount}; $financeAccount->TypeCode( RPS::DB::Item::FinanceAccount::kAccountTypeHoldover ); $financeAccount->CurrencyCode('USD'); my $ptList = $financeAccount->{PendingTransactionList}; my $ptArray = $ptList->getPendingTransactionArray(); report("ptArray is empty") if ( not defined $ptArray->[0] ); #.................................................................. # Note: passing the args as shown below has no effect. The 'amount' # and 'type_code' must be set after instantiation. # # Meanwhile, the following "new" call will generate a warning in # RPS::Finance::PendingTransaction.pm due to type_code not being # set. We manually set values immediately after instantiation so # it should all be good. #.................................................................. my $ptObj = RPS::Finance::PendingTransaction->new( amount => $balance, type_code => RPS::DB::Item::PendingTransaction::kTypeAdjustment, ); $ptObj->Amount($balance); $ptObj->TypeCode( RPS::DB::Item::PendingTransaction::kTypeAdjustment ); $ptObj->CurrencyCode('USD'); $ptObj->Memo("Opening Balance"); push @{$ptArray}, $ptObj; #----------------------------- # Validate the finance objects #----------------------------- # TODO: if an object won't validate, we need to find the error # and then add it into the errorCode variable $validFinanceAccount = $financeAccount->validate(); $validPendingTransactionList = $ptList->validate(); $validPendingTransaction = $ptObj->validate(); report("DEBUG: Checking if ptArray is defined..."); if ( not defined $ptArray->[0] ) { report("ptArray is empty"); } else { my $num = @$ptArray; report("ptArray is not empty, has $num element(s) "); foreach my $p (@{$ptArray}) { die(" amount not defined? ". ref $p ) if ( not defined $p->Amount() ); # XXX my $amt = $p->Amount(); report(" $amt"); } } } else { #----------------------------------------------------------- # This license doesn't have a balance, but we need to set # the following validation flags so that we don't mistakenly # think the license isn't valid #----------------------------------------------------------- $validFinanceAccount = 1; $validPendingTransactionList = 1; $validPendingTransaction = 1; } # } elsif( $isRingtone ) { # # $newLicense = RPS::License::US::RingtoneLicense->new( %licArgs ); # assert($share); # $newLicense->Share($share); # # $validFinanceAccount = 1; # $validPendingTransactionList = 1; # $validPendingTransaction = 1; } else { #............................................................... # This doesn't work -- the publicDomain flag _must_ be set when # creating the TrackLicense object in order for it to be flagged # as PD. If we leave out the publicDomain flag at creation and # then try to use the "Type" modifier to set the license as PD, # the TrackLicense validator tries to use the non-PD validation # logic to examine the license and of course it fails since the # PD license is missing a bunch of required fields. #............................................................... # $newLicense->Type(RPS::DB::Item::TrackLicense::kPublicDomain); $newLicense = RPS::License::US::PublicDomain->new( %licArgs ); # Set the share. It must be defined and > 0. assert($share); $newLicense->Share($share); #---------------------------------------------------------------- # Even though this is a PD license, we need to set the following # validation flags so that we don't mistakenly flag the license # as invalid #---------------------------------------------------------------- $validFinanceAccount = 1; $validPendingTransactionList = 1; $validPendingTransaction = 1; } #-------------------------------------------------------------------- # In order for the license to validate properly, any sub-objects must # also validate successfully. #-------------------------------------------------------------------- report("DEBUG: ReserveLiquidation validation: $validReserveLiquidation"); report("DEBUG: finance validation: account($validFinanceAccount) " . "ptList($validPendingTransactionList) pt($validPendingTransaction)"); my $validLicense = $newLicense->validate(); #----------------------------------------------------------------------- # If the license didn't validate, see if we can get a meaningful message # from the form element. # If the _only_ error is a share exception, then we'll let the license in # _if_ the share is over 100%. Per CD, this is to accomodate licenses # where the client intentionally wants to license over a 100%. #----------------------------------------------------------------------- my %errorMap; _getError(\%$newLicense, \%errorMap); if ( (keys %errorMap) == 1 ) { report("Share error: ". $newLicense->Share() ) if (exists $errorMap{Share}); if (exists $errorMap{Share} && $newLicense->Share() > 100 ) { $validLicense = 1; # overlook this error... } } if ( $validLicense && $validFinanceAccount && $validPendingTransactionList && $validPendingTransaction ) { report("#### VALID ####"); if ( $execMode ) { #---------------------------------------------------------- # Everything is valid, so go ahead and save off the objects #---------------------------------------------------------- if ( $financeAccount ) { $financeAccount->save(); # saves finance_account & pending_transaction my $financeAccountID = $financeAccount->AccountID(); $newLicense->FinanceAccountID($financeAccountID); } $newLicense->save(); # saves track_license and reserve_liquidation ++$count{new_licenses}; ++$numNewLicenses; my $trackLicenseID = $newLicense->TrackLicenseID(); #------------------------------------------------------------- # If we let this license in with a share exception, flag it so # it's visible in the import report. #------------------------------------------------------------- if ( exists $errorMap{Share} ) { _appendString( $warningCode, "Share warning"); ++$excount{share_warning}; $row->{'warning-code'} = $warningCode; } #---------------------------------------------------------------- # The finance account (if any) and track license both know about # each other. # # After creating the license with the financeAccountID info, we # need to update the financeAccount object with the license info. #---------------------------------------------------------------- if ( $financeAccount ) { $financeAccount->Description("advance account for license $trackLicenseID"); $financeAccount->save(); } $row->{'rs-license-id'} = $trackLicenseID; report(" rowid($rowid): Created license $trackLicenseID"); } } else { #------------------------------------------- # This whole section needs to be revamped... #------------------------------------------- #report("INVALID"); report("INVALID, regionID($regionID) pubID($publisherID) ptype($productTypeID): ". Dumper(\%errorMap) ); if ( $errorMap{RegionID} ) { report("INVALID, region error with license!"); _appendString( $errorCode, "Region error"); ++$excount{region_error}; $row->{'error-code'} = $errorCode; } else { # If you get here, figure out what went wrong die("ERROR: RPS license validation failed but license importer didn't recognize it"); } } }# row loop #------------------------------------------------------------ # Dump out the errors # TODO: Need to properly propagate the errors back to the user #------------------------------------------------------------ _showExceptions( $rows ); #----------------------- # Show import statistics #----------------------- report("##### S U M M A R Y #####"); my $totalExceptions = 0; my $totalRows = (scalar @$rows); foreach my $c (keys %excount) { my $v = $excount{$c}; printf("%30s %6d\n", $c, $v); $totalExceptions += $v; } printf("%30s %s\n", " ", "-------" ); printf("%30s %6d\n", "Total Exceptions", $totalExceptions ); printf("%30s %6d\n", "Total Rows", $totalRows ); report(" "); foreach my $c (keys %count) { my $v = $count{$c}; printf("%30s %d\n", $c, $v); } report("sanity check: total licenses created = $numNewLicenses"); }#_processData #-------------------------------------------------------------------------- # _showExceptions was originally intended to _just_ show the template lines # that exceptioned out. It's been modified to output both exception and # non-exception lines. Exception message(s) will be placed in the error # code column. If a line is imported successfully, then the resulting # licenseID will be stored in the import status column ("licenseID(###)"), # otherwise this column will contain the string "__FAILED__". #-------------------------------------------------------------------------- sub _showExceptions { my ( $rows ) = @_; #--------------------------- # Build the exception header #--------------------------- my @header; for( my $i = 0; $i < (keys %gColumnMap); $i++ ) { # Get the key at the specified column my $v = $gColumnMap{$i}; next if ( !$v ); # strip blank columns #---------------------------------------------------------- # Do _not_ push the "Error Code" or "import-status" columns # if they were in the original template. We'll re-create # them on the fly. #---------------------------------------------------------- next if ( ("Error Code" eq $v) || ("import-status") eq $v ); push @header, $v; } report("EXCEPTION:\t".join("\t", @header, "Error Code", "import-status")); #-------------------------------------- # Now dump out the rows that had errors #-------------------------------------- foreach my $row (@$rows) { my $rowid = $row->{rowid}; my $errorCode = $row->{'error-code'}; my $warningCode = $row->{'warning-code'}; my $licenseID = $row->{'rs-license-id'} || '---'; #---------------------------------------- # Output the row data in the proper order #---------------------------------------- my @obuf; for( my $i = 0; $i < (keys %gColumnMap); $i++ ) { # Get the key at the specified column my $v = $gColumnMap{$i}; next if ( !$v ); # strip blank columns my $val = ($row->{$v}) ? $row->{$v} : ''; #---------------------------------------------------------- # Do _not_ push the "Error Code" or "import-status" columns # if they were in the original template. We'll re-create # these columns below. #---------------------------------------------------------- next if ( ("Error Code" eq $v) || ("import-status") eq $v ); push @obuf, $val; } my $importStatus; my $ecString; if ( $errorCode ) { $importStatus = "__FAIL__"; $ecString = ($errorCode) ? $errorCode : ''; } else { $importStatus = "license($licenseID)"; $ecString = ($warningCode) ? $warningCode : ''; } my $zzbuf = join("\t", "EXCEPTION:", @obuf, $ecString, $importStatus); report($zzbuf); } } #------------------------------------------------------------- # _setReserve -- defines a ReserveLiquidation item if there is # any percentage to liquidate #------------------------------------------------------------- sub _setReserve { my($period,$percent,$a) = @_; $percent = 0 if ( !$percent ); $a->Period($period); $a->Percent($percent); } #------------------------------------------------------------- # _dumpError -- go through all of the TrackLicense properties # and display the current value (if any), underlying datatype, # and error information #------------------------------------------------------------- sub _dumpError { my($leader, $license) = @_; #------------------------------------------------------- # The following will show _all_ properties (not just the # usual track_license info) #------------------------------------------------------- foreach my $k (sort { $a cmp $b } keys %$license ) { my $v = $license->{$k}; my $typeOf = ref $v; my $hasError = ''; if ( (ref $v) =~ /Common::FormObject::Scalar/ || ref $v eq "Common::FormObject::DateTime" ) { my $sv = (defined $v->_getValue()) ? $v->_getValue() : '__undef__'; $hasError = $v->_hasError(); my $errFlag = $hasError ? "*** ERROR ***" : ''; printf("%s %20s %20s %s %s\n", $leader, $k, $sv, $typeOf, $errFlag); } elsif( (ref $v) =~ /RPS::Finance::PendingTransactionList/ ) { my $pArray = $v->getPendingTransactionArray(); my $pType = ref $pArray; $hasError = $v->_hasError(); my $errFlag = $hasError ? "*** ERROR ***" : '>> OK <<'; printf("%s %s %s\n", $leader, $typeOf, $errFlag ); foreach my $pt (@{$pArray}) { my $amt = $pt->Amount(); my $typeCode = $pt->TypeCode(); $pType = ref $pt; $hasError = $pt->_hasError(); my $errFlag = $hasError ? "*** ERROR ***" : ' >> OK <<'; report("$leader $pType: amount($amt) typeCode($typeCode) $errFlag"); } } elsif( (ref $v) =~ /RPS::Finance::Account/ ) { $hasError = $v->_hasError(); my $errFlag = $hasError ? "*** ERROR ***" : '>> OK <<'; printf("%s %20s %20s %s %s\n", $leader, $k, " ", $typeOf, $errFlag); my $financeAccountID = $v->AccountID(); _dumpError( $leader . " ",\%$v ); } elsif( (ref $v) =~ /RPS::License::ReserveLiquidationList/ ) { my $reserveArray = $v->getList(); printf("%s %20s %s\n%20s", $leader, $k, "@", $typeOf); for( my $i=0; $i<8; $i++ ) { printf("%s [%d] %d,", $leader, $i, $reserveArray->[$i]->Percent() ); } print"\n"; } else { my $errFlag = ''; $v = "---" if ( !$v ); printf("%s %20s %20s %s %s\n", $leader, $k, $v, $typeOf, $errFlag); } } }# _dumpError #--------------------------------------------------- # _isValidDateFmt - return true if the supplied date # is in the format YYYY-MM-DD, false otherwise #--------------------------------------------------- sub _isValidDateFmt { my($dstr) = @_; my($yr,$mo,$dy) = split("-",$dstr); #report("_isValidDateFmt: date $dstr --> yr($yr) mo($mo) dy($dy)"); my $st = 1; # valid unless we detect otherwise $st = 0 if ( !$yr || !$mo || !$dy ); $st = 0 if ( $mo && ( $mo !~ /^\d+$/ )); $st = 0 if ( $yr && ( $yr !~ /^\d+$/ )); $st = 0 if ( $dy && ( $dy !~ /^\d+$/ )); $st = 0 if ( length($yr) != 4 ); return $st; } sub _normalizeDate { my($dstr) = @_; if ( $dstr =~ m/^(\d+)$/ ) { my $_dt = numericToDate( $dstr ); report("_normalizeDate: Converted msft date $dstr to $_dt"); $dstr = $_dt; } return $dstr; } sub _isInteger { my($v) = @_; return ( $v and $v =~ m/^\d*$/ ) ? 1 : undef; } sub _findTrack { my(%args) = @_; my $trackID; my ($albumID,$errflag) = _findAlbum(%args); if ( ! $albumID ) { my $albumName = $args{album_name}; my $catNo = $args{catalog_number} || ""; report(" _findTrack: album($albumName) cat#($catNo) not found, skipping track"); $errflag = kAlbumNotFound; } else { my $trackName = $args{track_name}; $trackName =~ s/^\s+//; # remove leading spaces $trackName =~ s/\s+$//; # remove trailing spaces my $isrc = (defined $args{isrc}) ? $args{isrc} : ""; report(" _findTrack: looking for track($trackName) isrc($isrc) on albumID($albumID)"); my $duplicate; # set if track title is not unique if ( !$isrc ) { # No ISRC; see if track title is a duplicate my $cleanTitle = clean($trackName); my $sql = "SELECT track_id FROM track " . "WHERE album_id = $albumID AND " . "title_clean='$cleanTitle'"; my $sth = $dbo->DoCmd($sql); if ( $sth->rows > 1 ) { report("_findTrack:DEBUG: duplicate title detected: sql = $sql"); $errflag = kDuplicateTrackTitle; $duplicate = 1; } } if ( ! $duplicate ) { my $tColl = RPS::DB::Item::Track->GetTracksByAlbumID($albumID); while( $tColl->hasNext() ) { my $trackObj = $tColl->next(); if ( $isrc ) { $isrc = trimspaces($isrc); my $masterID = $trackObj->master_id; my $mObj = RPS::DB::Item::Master->Lookup( master_id => $masterID ); my $zzIsrc = $mObj->isrc; #if ( '' ne $isrc && $isrc eq $zzIsrc ) my $t1 = lc clean($trackName); my $t2 = lc $trackObj->title_clean; # kludge for New World $t2 =~ s/__/_/g if ( $clientID == 138 ); $t2 =~ s/^_//g if ( $clientID == 290 ); # fearless # kludge for Nettwerk; remove trailing "_" from clean title $t2 =~ s/_$// if ( $clientID == 51 ); # kludge for EGR; remove trailing "_" from clean title $t2 =~ s/_$// if ( $clientID == 183 ); # kludge for VP Records; remove trailing "_" from clean title (FB13163) $t2 =~ s/_$// if ( $clientID == 306 ); # The ISRC and title must match if ( $isrc && $zzIsrc && '' ne $isrc && ($isrc eq $zzIsrc) && ($t1 eq $t2) ) { $trackID = $trackObj->track_id; last; } } else { my $t1 = lc clean($trackName); my $t2 = lc $trackObj->title_clean; # kludge for New World $t2 =~ s/__/_/g if ( $clientID == 138 ); $t2 =~ s/^_//g if ( $clientID == 290 ); # fearless # kludge for Nettwerk; remove trailing "_" from clean title $t2 =~ s/_$//; # kludge for VP Records; remove trailing "_" from clean title (FB13163) $t2 =~ s/_$// if ( $clientID == 306 ); report("_findTrack: comparing [$t1] to [$t2]"); if ( $t1 eq $t2 ) { $trackID = $trackObj->track_id; last; } } }#track loop $errflag = kTrackNotFound if( !$trackID ); } } return ($trackID, $errflag); }# _findTrack sub _findAlbum { my(%args) = @_; my $catNo = $args{catalog_number} || ""; my $albumName = $args{album_name}; my $errflag; my $albumID; if ( $albumName ) { my %a = ( title => $albumName, ); $a{catalog_number} = $catNo if ( $catNo ); #report(" _findAlbum: looking for CAT#($catNo) title($albumName)"); my $cleanAlbumTitle = clean($albumName); report(" _findAlbum: looking for CAT#($catNo) title($albumName) titleClean($cleanAlbumTitle)"); #------------------------------------------------------------------- # Warning: if a title has a trailing space in the database, then the # clean version will have a trailing underscore. We use LIKE to try # and prevent this from not finding the album. #------------------------------------------------------------------- #my $sql = "SELECT album.* FROM album WHERE title_clean=". $dbo->DBQuote($cleanAlbumTitle); #$sql .= " AND catalog_number = " . $dbo->DBQuote($catNo) if ( $catNo ); my $sql = "SELECT album.* FROM album WHERE title_clean LIKE '$cleanAlbumTitle%' "; $sql .= " AND catalog_number = " . $dbo->DBQuote($catNo) if ( $catNo ); my $albumColl = RPS::DB::Item::Album->GetAll( $sql ); $errflag = kAlbumNotFound if ( $albumColl->size() == 0 ); $errflag = kNonUniqueAlbumName if ( $albumColl->size() > 1 ); if ( !$errflag ) { my $aObj = $albumColl->next(); $albumID = $aObj->album_id; report(" _findAlbum: found albumID($albumID)"); } } return ($albumID, $errflag); }# _findAlbum sub _findPublisher { my(%args) = @_; my $publisherID; my $name = $args{publisher_name}; $name =~ s/\s*$//g; $name =~ s/^\s*//g; my $pObj = RPS::DB::Item::Publisher->Lookup( #publisher_name => $args{publisher_name}, publisher_name => $name, ); # TODO: Should check for duplicate publisher names.. if ( $pObj ) { $publisherID = $pObj->publisher_id; } return $publisherID; } sub _findPayor { my(%args) = @_; my $payorID; my $pObj = RPS::DB::Item::Payor->Lookup( name => $args{payor_name}, ); if ( $pObj ) { $payorID = $pObj->payor_id; } return $payorID; } sub _findRegion { my(%args) = @_; my $regionID; my $regionName = $args{name}; my $rObj = RPS::DB::Item::Region->Lookup( name => $regionName, ); if ( $rObj ) { $regionID = $rObj->region_id; } return $regionID; } #-------------------------------------------------------- # For 'ALL' or 'D' licenses, the region must be US or CA. # This routine checks the specified region and sees if it # contains any other countries besides US and/or CA #-------------------------------------------------------- sub _validDigitalRegion { my($regionID) = @_; my $valid = 1; my $coll = RPS::DB::Item::RegionCountryMap->GetByRegionID($regionID); while($valid && $coll->hasNext() ) { my $rcObj = $coll->next(); my $cc = uc $rcObj->country_code; $valid = 0 if ( $cc ne 'US' && $cc ne 'CA' ); } return $valid; } #----------------------------------------------------------- # _getCrossedID -- returns a value that can be used to set a # license's cross_collateralized field #----------------------------------------------------------- sub _getCrossedID { my(%args) = @_; my $crossed = $args{crossed}; my $crossedID = 0; # no - default if ( $crossed && $crossed =~ /^y/i ) { $crossedID = 1; } return $crossedID; } #---------------------------------------------------------- # _getStatusID -- returns a value that can be used to set a # license's 'inactive' field #---------------------------------------------------------- sub _getStatusID { my(%args) = @_; my $status = $args{inactive}; my $statusID = 0; # active - default if ( $status && $status =~ /^i/i ) { $statusID = 1; } return $statusID; } #---------------------------------------------------------- # _getPublisherDirect -- returns a value that can be used # to set a license's 'publisher_direct' field. #---------------------------------------------------------- sub _getPublisherDirect { my(%args) = @_; my $pdflag = $args{publisher_direct}; my $statusID = 0; # default is "not publisher direct" if ( $pdflag && $pdflag =~ /^y/i ) { $statusID = 1; # publisher direct } return $statusID; } #-------------------------------------------------------------- # _getRateBasis -- returns a rate basis identifier based on the # supplied text string #-------------------------------------------------------------- sub _getRateBasis { my(%args) = @_; my $rateBasis = $args{rate_basis}; my $rateBasisID; if ( !$rateBasis || ($rateBasis && $rateBasis =~ /^S/i ) ) { $rateBasisID = RPS::DB::Item::TrackLicense::kRateBasisSale; # Default } elsif ( $rateBasis && $rateBasis =~ /^L/i ) { $rateBasisID = RPS::DB::Item::TrackLicense::kRateBasisLock; } return $rateBasisID; } #----------------------------------------------------------------- # _getRateType -- returns an RPS rate type identifier based on the # supplied rate type string. # If a rate type isn't found, then an undefined rate type ID will # be returned to the caller. #----------------------------------------------------------------- sub _getRateType { my(%args) =@_; my $rateType = $args{rate_type}; my $rateTypeID = undef; # Note: the 1.0 spec defines S, M and P as valid rate types # I'm including F(ULL), M(IN), P(ENNY) for now. if ( $rateType && '' ne $rateType ) { if ( $rateType =~ /^p/i ) { $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypePenny; } elsif( ($rateType =~ /^s/i) || ($rateType =~ /^f/i) || ($rateType =~ /ring/i) || ($rateType =~ /r/i ) ) { $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypeFull; } elsif( $rateType =~ /^m/i ) { $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypeMinimum; } } else { $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypeFull; # Default } return $rateTypeID; } sub _getProductTypeID { my(%args) = @_; my $cfg = $args{product_type}; #return RPS::DB::Item::ProductType::kAllProducts if ( $cfg =~ /^all$/i ); return kProductTypeNotFound if ( !$cfg ); return undef if ( $cfg =~ /^all$/i ); return 254 if ( ($cfg =~ /^alld$/i) || ($cfg =~ /^all-d$/i) || ($cfg =~ /^all digital$/i) || ($cfg =~ /^all-digital$/i) ); # All digital return RPS::DB::Item::ProductType::kAllPhysicalProducts if ( ($cfg =~ /^allp$/i) || ($cfg =~ /^all-p$/i) || ($cfg =~ /^all physical$/i ) ); return RPS::DB::Item::Product::kProductTypeLP if ( $cfg =~ /lp/i ); return RPS::DB::Item::Product::kProductTypeCD if ( $cfg =~ /cd/i ); return RPS::DB::Item::Product::kProductTypeDigital if ( $cfg =~ /^da$/i ); return RPS::DB::Item::Product::kProductTypeDigitalTrack if ( $cfg =~ /^dt$/i ); return RPS::DB::Item::Product::kProductTypeVHS if ( $cfg =~ /^vhs$/i ); return RPS::DB::Item::Product::kProductTypeCass if ( $cfg =~ /^cass$/i ); return RPS::DB::Item::Product::kProductTypeEP if ( $cfg =~ /^ep$/i ); return RPS::DB::Item::Product::kProductTypeDVD if ( $cfg =~ /^dvd$/i ); return RPS::DB::Item::Product::kProductTypeCDSingle if ( $cfg =~ /^cd5$/i ); return RPS::DB::Item::Product::kProductTypeCassSingle if ( $cfg =~ /^cas5$/i ); return RPS::DB::Item::Product::kProductTypeDVDCDSet if ( $cfg =~ /^dvdcd$/i ); return RPS::DB::Item::Product::kProductTypeDblCD if ( $cfg =~ /^cd2$/i ); return RPS::DB::Item::Product::kProductTypeRingtone if ( $cfg =~ /ring/i ); return kProductTypeNotFound; } sub _appendString { my($str,$v) = @_; if ( $str ) { my $cur = $str; my $newstring = "$cur; $v"; $_[0] = $newstring; } else { $_[0] = $v; } return; die("str($str) v($v)") if ( !$str ); my $cur = $str; my $prefix = "$cur; $v"; $_[0] = ($str) ? $prefix : $v; }# _appendString sub _getError { my($licensehash, $errormap) = @_; #------------------------------------------------------- # The supplied hash is assumed to be a hash of form # elements. Check each one for an error... #------------------------------------------------------- foreach my $k (sort { $a cmp $b } keys %$licensehash ) { my $v = $licensehash->{$k}; my $typeOf = ref $v; my $hasError = ''; if ( (ref $v) =~ /Common::FormObject::Scalar/ || (ref $v) eq "Common::FormObject::DateTime" ) { if ( $v->_hasError() ) { my %xmlParams = $v->getXMLParams(); my $msg = defined $xmlParams{emsg} ? $xmlParams{emsg} : "MSG_NOT_AVAILABLE"; my $e = $xmlParams{e}; $errormap->{$k} = $msg; } } } }# _getError2 sub _reportError { my ($name, $obj) = @_; report(" _reportError: checking '$name' for errors"); if ($obj && $obj->_hasError()) { my %xmlParams = $obj->getXMLParams(); my $msg = defined $xmlParams{emsg} ? $xmlParams{emsg} : "MSG_NOT_AVAILABLE"; my $e = $xmlParams{e}; print "$name has an error:: $e :: $msg\n"; return 1; } return undef; } sub report { my($text, $level) = @_; $level = kNormal unless $level; if ( $level <= $gReportLevel ) { print $text . "\n"; } } sub numericToDate { #my $self = shift; my( $ndays ) = @_; ## Excel date is (supposedly) days since 01 Jan 1900, but have to subtract 2 ## from that number because (1) 0/1 index issue, and (2) MS wants to pretend ## that 1900 was a leap year. my @date = Add_Delta_Days(1900, 1, 1, $ndays - 2); return wantarray ? @date : sprintf("%d-%02d-%02d", @date); } 1;