#!/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};
}