package RPS::Import::NineSquaredNew;

use strict;


use Date::Calc qw(Days_in_Month);
use Spreadsheet::ParseExcel;

use lib '/app/tools/common/lib';
use Common::Util qw(normalize_date);
use Common::Consts;

use lib '/app/tools/data_classes/lib';

#private constants
use constant FIELD_STARTDATE	=> 13;
use constant FIELD_ENDDATE		=> 19;

use constant FIELD_VENDORID		=> 1;
use constant FIELD_ARTIST		=> 14;
use constant FIELD_TITLE		=> 17;
use constant FIELD_UNITS		=> 29;
use constant FIELD_TOTALPRICE	=> 32;

use constant MOTRICITY_TITLE    => 2;
use constant MOTRICITY_UNITS    => 4;
use constant MOTRICITY_PRICE    => 6;

use lib '/app/tools/rps/lib';
use RPS::File::Sale;
use RPS::Import::Importer;
use base 'RPS::Import::Importer';

#public methods

sub _importLines
{
	my $self = shift;
	my %args = @_;

	#
	# The NineSquared report comes in 3 sheets of data.
	# Each sheet is for a particular month in a quarter.
	# So, get our data from the 'sheets' param, not the 
	# 'lines' param.
	#
	my $retval = undef;
    my $fileObj = $args{file};
	my $sheets = $args{sheets};

    my $fileName = $fileObj->OrigFileName;
    ##my $fileName = $args{OrigFileName}; # for cmd-line testing

	if($sheets)
	{
		foreach my $sheet (@$sheets)
		{
			my $dateBegin;
			my $dateEnd;
			my $header_matched = 0;

			my $linenum = 1;
			foreach my $line (@$sheet)
			{
				$header_matched = matchHeader($line) if(!$header_matched);

				if ($linenum == 2 && $header_matched != 2) {

					# these values are in a custom format, so we have to convert them.
					# use normalize to verify
					# Dates are usually 7-1-05  to 8-1-05,
					# but we want the end date to be 7-31-05, so decrement it.
					my $start_date_raw = Spreadsheet::ParseExcel::Utility::ExcelFmt("yyyy-mm-dd", $line->[FIELD_STARTDATE]);
					my $end_date_raw   = Spreadsheet::ParseExcel::Utility::ExcelFmt("yyyy-mm-dd", $line->[FIELD_ENDDATE]-1);

					if($start_date_raw =~ /^\d\d\d\d-\d\d-\d\d$/ && $end_date_raw =~ /^\d\d\d\d-\d\d-\d\d$/)
					{
						$dateBegin = $start_date_raw;
						$dateEnd = $end_date_raw;
					}
				}
                elsif ($linenum == 1 && $header_matched == 2) {
                    ## motricity report, get dates from filename
                    if ($fileName =~ m/Q([1234])\-(\d{4})/) {
                        my $qtr  = $1;
                        my $year = $2;
                        if ($qtr == 1) {
                            $dateBegin = $year."-01-01";
                            $dateEnd   = $year."-03-".Days_in_Month($year, 3);
                        } elsif ($qtr == 2) {
                            $dateBegin = $year."-04-01";
                            $dateEnd   = $year."-06-".Days_in_Month($year, 6);
                        } elsif ($qtr == 3) {
                            $dateBegin = $year."-07-01";
                            $dateEnd   = $year."-09-".Days_in_Month($year, 9);
                        } elsif ($qtr == 4) {
                            $dateBegin = $year."-10-01";
                            $dateEnd   = $year."-12-".Days_in_Month($year, 12);
                        }
                    }
                }
				elsif ($dateBegin && $dateEnd)
				{
					my $prodtype 	= RPS::File::Sale::TYPE_TRACK;
					my $format		= RPS::File::Sale::FORMAT_RINGTONE;
                    my ($vendorID, $artist, $track, $units, $total_price);
                    if ($header_matched == 1) {
					    $vendorID   	= $line->[FIELD_VENDORID];
					    $artist	    	= $line->[FIELD_ARTIST];
					    $track	    	= $line->[FIELD_TITLE];
					    $units	    	= $line->[FIELD_UNITS];
					    $total_price	= $line->[FIELD_TOTALPRICE];
                    } elsif ($header_matched == 2) {
					    $track	    	= $line->[MOTRICITY_TITLE];
					    $units	    	= -1 * $line->[MOTRICITY_UNITS];
					    $total_price	= $line->[MOTRICITY_PRICE];
                    }
					my $price = $total_price / $units if($units != 0);

					if ($units && $price && ($artist || $header_matched == 2) && $track)
					{
						my $sale = RPS::Import::SaleRec->new();

						$sale->productType($prodtype);
						$sale->formatType($format);
						$sale->dateBegin($dateBegin);
						$sale->dateEnd($dateEnd);
						$sale->artistName($artist) unless ($header_matched == 2);
						$sale->trackName($track);
						$sale->units($units);
						$sale->price($price);
						$sale->lineNum($linenum);
						$sale->countryCode('US');

						$self->_insertSale(sale => $sale);
                        ##$args{dumper}($sale);  ## for cmd-line testing
					}
				}
				$linenum++;
			} # foreach line
			$retval += $linenum;
		} # foreach sheet
	}

	return $retval;
}

#
# Private methods
#

sub matchHeader
{
	my $line = shift;

	# verify the column headings are as we expect them

    ## standard report
	my $artist	= $line->[FIELD_ARTIST];
	my $track	= $line->[FIELD_TITLE];
	my $units	= $line->[FIELD_UNITS];
	my $price	= $line->[FIELD_TOTALPRICE];

	return 1 if($artist =~ m/author/i &&
	   $track  =~ m/product/i &&
	   $units  =~ m/totalsent/i && # not a typo
	   $price  =~ m/payment/i);

    ## motricity
    $track = $line->[MOTRICITY_TITLE];
    $units = $line->[MOTRICITY_UNITS];
    $price = $line->[MOTRICITY_PRICE];

    return 2 if ($track =~ m/product/i &&
                 $units =~ m/product count/i &&
                 $price =~ m/royalty amount/i);

    ## otherwise
	return 0;
}


###
1;# Play nicely.
###