#!/usr/bin/perl

use strict;
use Getopt::Std;
use IO::File;
use Date::Calc;
use Data::Dumper;

use lib '/app/tools/common/lib';
use Common::Util qw(decodeDateMysql trimspaces clean normalize_upc normalize_isrc);
use Common::RSDB;
use Common::RSApp;
use Common::Assert;
use Common::Consts qw(%COUNTRY_CODE);
use Common::RSMath;

use lib '/app/tools/rps/lib';
use RPS::XMLObject;
use RPS::DB::Item::Track;
use RPS::DB::Item::Album;
use RPS::DB::Item::Product;
use RPS::DB::Item::Master;
use RPS::DB::Item::Publisher;
use RPS::DB::Item::TrackLicense;
use RPS::DB::Item::StatRate;
use RPS::DB::Item::MechanicalCarryover;

# reporting flags and variable
#
use constant kQuiet  => 0;
use constant kNormal => 1;
use constant kDebug  => 3;
my $gReportLevel = kNormal;

use constant kProgressQuanta => 200;

# if two tracks on the same album have the same title,
# duration is the only distinguishing property. In this
# case the import data might be slightly different from the
# database data so we'll allow a +/- tolerance in matching.
use constant kDurationTolerance => 5;

# we're skipping some publishers (DIGITAL ONLY)
#   - ABKCO MUSIC INC (P11300)
#   - ABKCO MUSIC INC PETE TOWNSHEND CATALOG (P20061)
my @skipPublishers = qw();

# Parse the command-line.
#
my ( $clientID, $inputFile ) = parseCommandLine();

$| = 1;

# Instantiate the application singleton object.
#
my $appSingleton = Common::RSApp->new( clientID => $clientID );

# Scan the input file, and build the data structure of updates.
#
my %updates;
scanInputFileForUpdates( $inputFile, \%updates );

# Commit any necessary updates.
#
processUpdates( \%updates );

#
# --- Subroutines
#

sub parseCommandLine {
    my %opt;
    getopts( 'c:f:p', \%opt );

    my $inputFile = $opt{f};
    my $clientID  = $opt{c};

    unless ( $clientID =~ /^\d+$/ && $clientID > 0 ) {
        die usage("You must specify a valid client ID");
    }

    unless ($inputFile) {
        die usage("You must specify a catalog file to import");
    }

    unless ( -e $inputFile ) {
        die usage("The specified catalog file does not exist");
    }

    return ( $clientID, $inputFile );
}

sub usage {
    my $errstr = shift;
    my $text = ($errstr) ? "ERROR: $errstr\n" : '';
    $text .= "Usage: $0 -c <clientID> -f <inputFile>\n";
    return $text;
}

sub scanInputFileForUpdates {
    my ( $inputFilePath, $updates ) = @_;
    assert($inputFilePath);
    assert($updates);

    # Open the input file for reading.
    #
    my $inputFile = IO::File->new($inputFile) || die "Can't open input file '$inputFile': $!\n";

    readTabInputFile( $inputFile, $updates );

    # Done with the input file, close it.
    #
    $inputFile->close();
}

sub readTabInputFile {
    my ( $file, $updates ) = @_;
    assert($file);
    assert($updates);

    report("\nReading input file...");

    my $recordCount = 0;
    my $skipCount   = 0;

    # skip first line (headers)
    <$file>;

    # Process the file line by line
    #
    while ( my $record = <$file> ) {
        my @fields;
        my $accountNumber;
        my $licenseType;
        my $albumTitle;
        my $catalogNumber;
        my $songTitle;
        my $dateSent;
        my $dateSigned;
        my $topPublisherName;
        my $publisherName;
        my $publisherShare;
        my $rate;
        my $address1;
        my $address2;
        my $city;
        my $state;
        my $province;
        my $zip;
        my $country;
        my $taxID;
        my $actualShare;
        my $estimatedShare;
        my $negUnits;

        $recordCount++;

        if ( 0 == $recordCount % kProgressQuanta ) {
            report("  record $recordCount ... ");
        }

        # Split the line up into fields.
        #
        chomp $record;
        @fields = split( "\t", $record );
        @fields = trimquotes(@fields);
        @fields = escapequotes(@fields);

        (
            $accountNumber, undef,           $catalogNumber,    $songTitle, $publisherName, undef,
            $rate,          undef,           $topPublisherName, $address1,  $address2,      $city,
            $state,         $zip,            $province,         $country,   undef,          $taxID,
            $actualShare,   $estimatedShare, $licenseType,      undef,      $negUnits,      $albumTitle
        ) = undefspaces(@fields);

        $publisherShare = $actualShare || $estimatedShare;
        $publisherShare =~ s/%//;

        $country =~ s/^\s+//;
        $country =~ s/\s+$//;

        # we aren't saving licenses with share of 0.00%.
        #
        if ( $rate =~ m/\d+/ ) {
            my $newLicense = {};
            $newLicense->{trackLicenseID}   = undef;
            $newLicense->{lineNum}          = $recordCount + 1;    # the original excel file started on row 2, adjust here
            $newLicense->{catalogNumber}    = $catalogNumber;
            $newLicense->{songTitle}        = $songTitle;
            $newLicense->{licenseType}      = $licenseType;
            $newLicense->{albumTitle}       = $albumTitle;
            $newLicense->{topPublisherName} = $topPublisherName;
            $newLicense->{publisherName}    = $publisherName;
            $newLicense->{publisherShare}   = $publisherShare;
            $newLicense->{rate}             = $rate;
            $newLicense->{negativeUnits}    = $negUnits;

            push @{ $updates->{licenses} }, $newLicense;

            # -jff- I forget why this line is here
            #
            # report(join('\t', sort keys %$newLicense)) if($recordCount == 2);
        } else {
            report( "!Warning (line " . $recordCount . "): Incomplete data for song=$songTitle, publisher=$publisherName" );
            $skipCount++;
        }

        # regular publisher
        #
        my %pub = (
            address => join( ' ', $address1, $address2 ),
            city    => $city,
            state => $state || $province,
            zip   => $zip,
            taxID => $taxID,
            acctNum => $accountNumber,
            country => $COUNTRY_CODE{ lc($country) } || $country,
        );

        if ($publisherName) {
            if ( !exists( $updates->{publishers}->{$publisherName} ) ) {
                $updates->{publishers}->{$publisherName}{name} = $publisherName;
            }
        } else {
            report( "!Warning (line " . $recordCount . "): Incomplete publisher", kDebug );
        }

        # top (admin) publisher
        #
        if ($topPublisherName) {
            if ( !exists( $updates->{publishers}->{$topPublisherName} ) ) {
                map { $updates->{publishers}->{$topPublisherName}->{$_} ||= $pub{$_} } keys %pub;
                $updates->{publishers}->{$topPublisherName}{name} = $topPublisherName;
            }

            if ( $publisherName && ( $publisherName ne $topPublisherName ) ) {

                # mark this publisher as an admin
                #
                $updates->{publishers}->{$topPublisherName}->{isAdmin} = 1;

                # set the regular publisher's admin as this publisher
                #
                $updates->{publishers}->{$publisherName}->{adminName} = $topPublisherName;
            }
        } else {
            map { $updates->{publishers}->{$publisherName}->{$_} ||= $pub{$_} } keys %pub;

            #report("!Warning (line ".$recordCount."): Incomplete top publisher", kDebug);
        }

    }
    report("  Records processed : $recordCount");
    report("  Records skipped   : $skipCount");
}

sub processUpdates {
    my ($updates) = @_;

    my $publishers = $updates->{publishers};

    # Create publisher entries
    #
    report("\nCreating Publishers...");
    my $count = 0;

    # we need to create an entry for Harry Fox first
    #
    # my $hfa = RPS::DB::Item::Publisher->Create(publisher_name => "HARRY FOX AGENCY",
    # is_agency => 1,
    # is_admin => 0);
    # $hfa->save();
    # my $hfaID = $hfa->publisher_id;

    # HFA has publisher_id=1, we don't need to create it again. -jff-
    #
    my $hfaID = 0;

    my $pubCreateCount = 0;
    my $pubMatchCount  = 0;
    foreach my $publisherName ( sort { $publishers->{$b}->{isAdmin} <=> $publishers->{$a}->{isAdmin} } keys %$publishers ) {
        $count++;
        if ( 0 == $count % kProgressQuanta ) {
            report("  publisher $count ...");
        }

        my $publisher = $publishers->{$publisherName};

        # have we already created/matched this publisher?
        #
        if ( !$publisher->{publisherID} ) {

            # Does this publisher already exist?
            #
            #my $coll = RPS::DB::Item::Publisher->Match($publisher->{publisher_name});
            my $coll = '';

            # ignore any existing (for this import)
            if ( 0 && defined $coll && $coll->hasNext() ) {
                my $dbItem = $coll->next();
                $publisher->{publisherID} = $dbItem->publisher_id();
                $pubMatchCount++;

                # is this a new admin?
                #
                if ( $publisher->{isAdmin} == 1 && !$dbItem->is_admin() ) {
                    $dbItem->is_admin(1);
                    $dbItem->save();
                    report("!Update (Pub $publisherName): publisher set to Admin");
                }

                next;
            }

            my %pubParams = (
                publisher_name    => $publisher->{name},
                street_address    => $publisher->{address},
                city              => $publisher->{city},
                state_province    => $publisher->{state},
                postal_code       => $publisher->{zip},
                tax_id            => $publisher->{taxID},
                country_code      => $publisher->{country},
                is_agency         => 0,
                is_admin          => 0,
                agent_id          => $hfaID,
                client_account_id => $publisher->{acctNum},
            );

            if ( $publisher->{adminName} ) {

                # does this publisher have an admin?
                #
                $pubParams{admin_id} = $publishers->{ $publisher->{adminName} }->{publisherID};
            } elsif ( $publisher->{isAdmin} == 1 ) {

                # is this an admin?
                #
                $pubParams{is_admin} = 1;
            }

            report( "Creating publisher: " . Dumper( \%pubParams ), kDebug );

            my $dbItem = RPS::DB::Item::Publisher->Create(%pubParams);
            $dbItem->save();
            $publisher->{publisherID} = $dbItem->publisher_id;
            $pubCreateCount++;
        } else {
            print "exists: $publisherName : $publisher->{publisherID}\n";
        }

    }    # END - publishers loop

    report("  Publishers created : $pubCreateCount");
    report("  Publishers matched : $pubMatchCount");

    # Create license entries
    #
    my $tracks      = {};
    my $licenses    = $updates->{licenses};
    my %seenLicense = ();

    report("\nCreating Licenses...");

    my %product;
    my $matchCount = 0;
    $count = 0;
    foreach my $license (@$licenses) {
        $count++;
        if ( 0 == $count % kProgressQuanta ) {
            report("  license $count ...");
        }

        # SKIP these publishers:
        #
        my $publisherNumber    = $license->{publisherNumber};
        my $topPublisherNumber = $license->{topPublisherNumber};
        if ( grep( /^$publisherNumber$/, @skipPublishers ) || grep( /^$topPublisherNumber$/, @skipPublishers ) ) {
            report( "!Warning (line " . $license->{lineNum} . "): Skipping publisher=$publisherNumber (admin: $topPublisherNumber)" );
            next;
        }

        # did we already create this license?
        #
        if ( !$license->{trackLicenseID} ) {
            #
            # We need to identify this track before we can assign
            # a license to it.
            #
            my ( $albumID, $trackID );
            my $trackKey = join( '|', $license->{albumTitle}, $license->{catalogNumber}, $license->{songTitle}, );

            if ( !exists( $tracks->{$trackKey} ) ) {
                ( $albumID, $trackID ) = _deriveAlbumTrackFromLicense($license);
            } else {
                $albumID = $tracks->{$trackKey}{albumID};
                $trackID = $tracks->{$trackKey}{trackID};
                report( "TrackID already found for trackKey = $trackKey", kDebug );
            }

            if ($trackID) {
                report( "Song matched!", kDebug );

                # store this for future reference
                #
                $tracks->{$trackKey}{albumID} = $albumID;
                $tracks->{$trackKey}{trackID} = $trackID;

                my %licenseParams = (
                    track_id      => $trackID,
                    date_sent     => $license->{dateSent},
                    date_received => $license->{licenseDate},
                    date_issued   => $license->{dateSigned},
                    type          => 1,
                    rate_basis    => 1,
                    free_goods    => 15,                        # Sanctuary physical license specific
                );

                # all the balances are negative
                #
                if ( $license->{advance_amount} < 0 ) {
                    $licenseParams{advance_amount} = $license->{advance_amount} * -1;
                }

                my $rateType = _deriveRateType( $license->{licenseType} );
                if ($rateType) {
                    $licenseParams{rate_type}       = $rateType;
                    $licenseParams{rate_percentage} = _deriveRatePercentage( $license->{licenseType} );
                } else {
                    report( "!Warning (line "
                          . $license->{lineNum}
                          . "): unknown rate basis ("
                          . $license->{rate}
                          . ") for song = "
                          . $license->{songTitle} );
                    next;
                }

                if ( $rateType == RPS::DB::Item::TrackLicense::kRateTypePenny() ) {

                    # just assign the penny rate
                    #
                    # $licenseParams{penny_rate} = $license->{rate};
                    $licenseParams{penny_rate} = Common::RSMath::round( $license->{rate}, 4 );
                    $licenseParams{rate_basis} = undef;
                }

                my ($productTypeID) = $license->{catalogNumber} =~ m/-(\d)$/;
                if ($productTypeID) {
                    $licenseParams{product_type_id} = $productTypeID;
                } else {
                    report( "!Warning (line "
                          . $license->{lineNum}
                          . "): unknown license type ("
                          . $license->{catalogNumber}
                          . ") for song = "
                          . $license->{songTitle} );
                    next;
                }

                my $share;
                if ( $license->{publisherShare} ) {
                    $licenseParams{share} = $license->{publisherShare};
                } else {
                    report( "!Warning (line " . $license->{lineNum} . "): missing share for song = " . $license->{songTitle} );
                    next;
                }

                my $publisherID;
                my $pubNum = $license->{publisherName};
                if ( $pubNum && $publishers->{$pubNum} && $publishers->{$pubNum}->{publisherID} ) {
                    $licenseParams{publisher_id} = $publishers->{$pubNum}->{publisherID};
                } else {
                    report( "!Warning (line " . $license->{lineNum} . "): missing publisher for song = " . $license->{songTitle} );
                    next;
                }

             # let's make sure we didn't already import this license
             #
             # my $licenseKey = join('-', $trackID, $licenseParams{publisher_id}, $licenseParams{share}, $licenseParams{issuer_license_id});
                my $licenseKey = join( '-', $trackID, $licenseParams{publisher_id}, $licenseParams{product_type_id} );
                if ( !$seenLicense{$licenseKey} ) {
                    $seenLicense{$licenseKey} = 1;

                    # check for duplicate in the system
                    #
                    my $coll = RPS::DB::Item::TrackLicense->GetByTrackPublisherProductType(
                        $trackID,
                        $licenseParams{publisher_id},
                        $licenseParams{product_type_id}
                    );
                    if ( defined $coll && $coll->size > 0 ) {
                        report( "!Warning (line "
                              . $license->{lineNum}
                              . "): License already exists: track_id=$trackID, song = "
                              . $license->{songTitle} );
                        next;
                    }

                    #report("Creating track_license with: ".Dumper(\%licenseParams), kDebug);

                    my $newLicense = RPS::DB::Item::TrackLicense->Create(%licenseParams);
                    $newLicense->save();
                    $license->{trackLicenseID} = $newLicense->track_license_id;
                    $matchCount++;

                    # store any associated negative units
                    #
                    if ( $license->{negativeUnits} < 0 ) {
                        unless ( $product{$trackID} ) {
                            unless ( $product{$trackID} = _derivePhysicalProduct($albumID) ) {
                                report("!Warning (line $license->{lineNum}: can't locate physical product for track id: $trackID");
                                next;
                            }
                        }
                        my $saleDate    = '2006-01-01';                         # (for this import)
                        my $releaseDate = $product{$trackID}->release_date();
                        $releaseDate = $saleDate unless $releaseDate;
                        my $issueStatRateID = _getStatRateID($releaseDate);
                        my $saleStatRateID  = _getStatRateID($saleDate);
                        my $co              = RPS::DB::Item::MechanicalCarryover->Create(
                            track_license_id   => $license->{trackLicenseID},
                            sale_stat_rate_id  => $saleStatRateID,
                            issue_stat_rate_id => $issueStatRateID,
                            product_id         => $product{$trackID}->product_id(),
                            units              => $license->{negativeUnits},
                        );
                        $co->save();

                    }
                } else {
                    report( "!Warning (line " . $license->{lineNum} . "): License already created: song = " . $license->{songTitle} );
                }
            } else {
                report( "!Warning (line " . $license->{lineNum} . "): Song not found: song = " . $license->{songTitle} );
            }
        } else {
            report( "!Warning (line " . $license->{lineNum} . "): I guess we already processed this license?" );
        }

    }    # END - licenses loop

    report("  Total licenses processed : $count");
    report("  Licenses created         : $matchCount");
}

sub _deriveRateType {
    my $type = shift;

    # full  = 1
    # min   = 2
    # penny = 3
    #
    my $rateType;
    if ( $type =~ /\d\d%|STATUTORY/i ) {
        $rateType = RPS::DB::Item::TrackLicense::kRateTypeFull;
    } else {
        $rateType = RPS::DB::Item::TrackLicense::kRateTypePenny;
    }

    return $rateType;
}

sub _deriveRatePercentage {
    my $type = shift;

    return $1 if ( $type =~ m/^(\d+)%/ and $1 < 100 );
    return 100;
}

sub _deriveRate {
    my $rateType = shift;

    my $rate;

    if ( $rateType =~ /(\d\d)%/ ) {
        $rate = $1;
    } else {
        $rate = 100;
    }

    return $rate;
}

sub _deriveProductType {
    my $config = shift;

    my $id;

    if ( $config =~ /(DIGITAL|DOWNLOAD)/i ) {

        # digital
        #
        $id = 3;
    } elsif ( $config =~ /COMPACT DISC/i ) {

        # physical - CD
        #
        $id = 2;
    }

    return $id;
}

sub _deriveAlbumTrackFromLicense {
    my $license = shift;

    my ( $albumID, $trackID );

    # The Sanctuary matching rule is this:
    # 1. We must be able to match the catalog number or UPC to a release in our catalog.
    #
    if ( $license->{catalogNumber} || $license->{upc} ) {
        ( $albumID, $trackID ) = _getAlbumTrackIDFromCatalogNumber($license);
    }

    return ( $albumID, $trackID );
}

sub _derivePhysicalProduct {
    my $albumID = shift;

    my $coll = RPS::DB::Item::Product->GetProductsByAlbumID( $albumID, RPS::DB::Item::Product::kProductTypeCD );

    return $coll->next() if ( $coll->hasNext() );
    return undef;
}

sub lookupPublisher {
    my ($name) = @_;

    my $publisherID;

    # Does this publisher already exist?
    #
    my $coll = RPS::DB::Item::Publisher->Match($name);
    if ( defined $coll && $coll->size == 1 ) {
        my $dbItem = $coll->next();
        $publisherID = $dbItem->publisher_id;
    }

    return $publisherID;
}

sub _normalizeCatalogNumber {
    my $catalogNumber = shift;

    #$catalogNumber =~ s/\D//g;
    if ( length($catalogNumber) >= 10 ) {
        if ( length($catalogNumber) == 10 ) {
            if ( $catalogNumber !~ /2$/ ) {
                $catalogNumber = $catalogNumber . "2";
            } elsif ( $catalogNumber !~ /^0/ ) {
                $catalogNumber = "0" . $catalogNumber;
            }
        }

        $catalogNumber = substr( $catalogNumber, 5, 10 );
        if ( length($catalogNumber) == 5 ) {
            $catalogNumber .= "-2";
        } else {
            $catalogNumber =~ s/2$/-2/;
        }
    } elsif ( length($catalogNumber) == 5 ) {
        $catalogNumber .= "-2";
    }

    return $catalogNumber;
}

sub _getAlbumTrackIDFromCatalogNumber {
    my ($license) = @_;

    my ( $albumID, $trackID );
    my $matchedTracks;

    # the order here is important, we always want to use the catalogNumber if we can.
    #
    my $catalogNumber = _normalizeCatalogNumber( $license->{catalogNumber} ) || _normalizeCatalogNumber( $license->{upc} );
    report( "Looking up CatalogNumber = $catalogNumber", kDebug );
    if ($catalogNumber) {
        my $coll = RPS::DB::Item::Album->GetByCatalogNumber($catalogNumber);

        if ( $coll->hasNext() ) {
            my $albumItem = $coll->next();

            $albumID       = $albumItem->album_id;
            $matchedTracks = MatchTracksFromAlbumID( $albumID, $license );
            $trackID       = FindTrackFromMatchedTracks( $matchedTracks, $license );
        } else {
            report( "!Warning: CatalogNumber not found", kDebug );
        }
    }

    return ( $albumID, $trackID );
}

sub FindTrackFromMatchedTracks {
    my ( $matchedTracks, $license ) = @_;

    my $trackID;

    if ( @$matchedTracks == 1 ) {
        $trackID = $matchedTracks->[0]->track_id;
        report( "!Notice (line " . $license->{lineNum} . "): MATCH found! trackID = " . $trackID, kDebug );
    } elsif ( @$matchedTracks > 1 && $license->{duration} =~ /\d?\d?:\d\d/ ) {
        report( "Checking duration...", kDebug );
        foreach my $track (@$matchedTracks) {

            # let's compare duration
            #
            my $licDuration = RPS::XMLObject::_MMSSToSeconds( $license->{duration} );
            my $masterItem = RPS::DB::Item::Master->Lookup( master_id => $track->master_id );

            if ( $licDuration == $masterItem->duration || abs( $licDuration - $masterItem->duration ) <= kDurationTolerance ) {
                $trackID = $track->track_id;
            }
        }

        if ( !$trackID ) {
            report( "!Warning (line " . $license->{lineNum} . "): duplicate song, no match on duration = " . $license->{songTitle},
                kDebug );
        }
    } else {
        report( "!Warning (line " . $license->{lineNum} . "): duplicate song, no match on duration = " . $license->{songTitle}, kDebug );
    }

    return $trackID;
}

sub MatchTracksFromAlbumID {
    my ( $albumID, $license ) = @_;

    my @matchedTracks = ();

    # normalize the song title we are trying to match
    #
    my $licenseTrack = lc( $license->{songTitle} );
    $licenseTrack =~ s/\s+//g;
    my $licenseTrackClean = clean($licenseTrack);

    my $trackColl = RPS::DB::Item::Track->GetTracksByAlbumID($albumID);
    while ( $trackColl->hasNext() ) {
        my $trackItem = $trackColl->next();

        if ( $trackItem->title ) {

            # normalize the song title from the database
            #
            my $prodTrack = lc( $trackItem->title );
            $prodTrack =~ s/\s+//g;
            my $prodTrackClean = clean($prodTrack);
            $prodTrackClean =~ s/_//g;

            report( "!Notice (line " . $license->{lineNum} . "): comparing ('" . $prodTrack . "' cmp '" . $licenseTrack . "')", kDebug );

            ##########################
            ########################## We are restricting matches to ONLY EXACT matches
            ##########################

            # match a short title
            #			if (length($prodTrack) < 3)
            #			{
            if ( $licenseTrack eq $prodTrack || $licenseTrackClean eq $prodTrackClean ) {
                report( "!Notice (line " . $license->{lineNum} . "): MATCH ('" . $prodTrack . "' cmp '" . $licenseTrack . "')", kDebug );
                push @matchedTracks, $trackItem;

                # $trackID = $trackItem->track_id;
            }

            #			}
            #			# does the dbTrack contain the title? or the other way around?
            #			# This works because we've already narrowed it down to the
            #			# correct album.
            #			elsif ($licenseTrack eq $prodTrack ||
            #				   index($licenseTrack, $prodTrack) > -1 ||
            #				   index($prodTrack, $licenseTrack) > -1 ||
            #				   $licenseTrackClean eq $prodTrackClean ||
            #				   index($licenseTrackClean, $prodTrackClean) > -1 ||
            #				   index($prodTrackClean, $licenseTrackClean) > -1
            #				   )
            #			{
            #				report("!Notice (line ".$license->{lineNum}."): MATCH ('".$prodTrack."' cmp '".$licenseTrack."')", kDebug);
            #				push @matchedTracks, $trackItem;
            #				# $trackID = $trackItem->track_id;
            #			}
        }
    }

    return \@matchedTracks;
}

sub escapequotes {
    my @string = @_;

    for (@string) {
        s/\"\"/"/g;
    }

    return wantarray ? @string : $string[0];
}

sub trimquotes {
    my @string = trimspaces(@_);

    for (@string) {
        if (m/^\".*\"$/) {
            s/^\"//;
            s/\"$//;
        }
    }

    return wantarray ? @string : $string[0];
}

sub undefspaces {
    my @string = trimspaces(@_);

    for (@string) {
        if ( $_ eq '' ) {
            $_ = undef;
        }
    }

    return wantarray ? @string : $string[0];
}

sub report {
    my ( $text, $level ) = @_;

    $level = kNormal unless $level;

    if ( $level <= $gReportLevel ) {
        print $text . "\n";
    }
}

sub _convertDate {
    my $date = shift;
    my $newValue;

    my ( $year, $month, $day );
    if (   ( ( $year, $month, $day ) = Date::Calc::Decode_Date_US($date) )
        || ( ( $year, $month, $day ) = Date::Calc::Decode_Date_EU($date) )
        || ( ( $year, $month, $day ) = decodeDateMysql($date) ) ) {
        $newValue = sprintf( "%4d-%02d-%02d", $year, $month, $day );
    }

    return $newValue;
}

sub _myNormalizeUPC {
    my $upc = shift;

    if ( length($upc) > 10 ) {
        $upc =~ s/^0+//;
    }

    if ( length($upc) == 10 ) {

        # assume it needs the "format" digit on the end (2)
        $upc = $upc . "2";
    }

    return normalize_upc($upc);
}

my $gStatRateTable;
my $gStatRateByDate;

sub _getStatRateTable {
    if ( !$gStatRateTable ) {
        $gStatRateTable = {};

        my $statRates = RPS::DB::Item::StatRate->GetAll();
        while ( my $statRate = $statRates->next() ) {
            $gStatRateTable->{ $statRate->stat_rate_id } = $statRate;
        }
    }

    return $gStatRateTable;
}

# Generally we are going to be asking for the same stat rate for the same # date over and over and over and over... so it makes sense to cache it.
sub _getStatRateID {
    my $endDate = shift;
    if ( !$gStatRateByDate->{$endDate} ) {
        my $table = _getStatRateTable();

        # get the stat rates, sorted by date
        my @sortedRateIDs = sort { $table->{$b}->date_effective <=> $table->{$a}->date_effective } keys %$table;
        foreach my $testRateID (@sortedRateIDs) {
            if ( $table->{$testRateID}->date_effective <= $endDate ) {
                $gStatRateByDate->{$endDate} = $table->{$testRateID}->stat_rate_id;
                last;
            }
        }
    }
    return $gStatRateByDate->{$endDate};
}