package RPS::Import::NineSquaredVarTab;

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	=> 10;
use constant FIELD_ENDDATE		=> 17;

use constant FIELD_VENDORID		=> 1;
use constant FIELD_PRODUCT		=> 2;
use constant FIELD_PLATFORM		=> 5;
use constant FIELD_ISRC 		=> 12;
use constant FIELD_SERVICE 		=> 20;
use constant FIELD_ARTIST 		=> 24;
use constant FIELD_TITLE		=> 25;
use constant FIELD_SALEPRICE	=> 26;
use constant FIELD_UNITS		=> 27;
use constant FIELD_TOTALPRICE	=> 30;

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

#public methods

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

	#
	# Variable-tab 9 Squared file with amazingly consistant
    # headers in each tab.
	#
	my $retval = undef;
	my $sheets = $args{sheets};
    my $fileObj = $args{file};
    my $sheet_names = $args{sheet_names};

    if ($self->{clientID} == 21) # warner
    {
        $fileObj->Atomic(1);
        $fileObj->Save();
        print STDERR "set to atomic\n";
    }

    unless ($sheets) {
        print STDERR "sheet problem\n";
        return undef;
    }
    my $sheet_num = 0;

	foreach my $sheet (@$sheets)
	{
        #print STDERR "sheet is ".$sheet_names->[$sheet_num]."\n";
        if ($sheet_names->[$sheet_num] eq "Summary") {
            $sheet_num++;
            next;
        }

		my $dateBegin;
		my $dateEnd;
		my $header_matched = 0;

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

			if ($linenum == 2)
			{
				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;
				}
                #print STDERR "dates: $dateBegin and $dateEnd\n";

			} elsif ($dateBegin && $dateEnd && $header_matched) {
			    my $prodtype 	= RPS::File::Sale::TYPE_TRACK;
                my ($format, $priceType, $service_id);
                if ($self->{clientID} == 21) { # warner
                    next if ($line->[FIELD_PLATFORM] eq "" || $line->[FIELD_PRODUCT] eq "");
                    my $provider_type = $line->[FIELD_PLATFORM]." ".$line->[FIELD_PRODUCT];
                    $format = RPS::Import::WMG::Util::getWirelessFormatTypeID($provider_type);
                    $priceType = RPS::Import::WMG::Util::getWirelessPriceType($provider_type);
                    unless ($format && $priceType)
                    {
                        $self->errstr("unknown format: $provider_type");
                        return undef;
                    }
                    my $service_name = $line->[FIELD_SERVICE];
                    $service_name = "9 SQUARED" if ($service_name eq "test");
                    $service_name = "mTat2 Brew Motorola" if ($service_name eq "mTat2 Brew Motorola/Alltel");
                    $service_id = RPS::Import::Importer->getServiceID($service_name);
                    unless ($service_id) {
                        $self->errstr("unknown service: $service_name");
                        return undef;
                    }
                    my @billTo = RPS::Import::WMG::Util::getWirelessBillto($self->{fileServiceID});
                    #my @billTo = RPS::Import::WMG::Util::getWirelessBillto(20);
                    my @sellTo = RPS::Import::WMG::Util::getWirelessSellto($service_id, $billTo[0]);
                    unless ($billTo[0] && $sellTo[0]) {
                        $self->errstr("no billto/sellto mapping for ".$self->{fileServiceID}.": $service_name");
                        return undef;
                    }
                } else {
                    $format = RPS::File::Sale::FORMAT_RINGTONE;
                }

                ##next;  #### DEBUG TESTS ONLY

				my $vendorID	= $line->[FIELD_VENDORID];
				my $artist		= $line->[FIELD_ARTIST];
				my $track		= $line->[FIELD_TITLE];
				my $isrc		= $line->[FIELD_ISRC];
				my $units		= $line->[FIELD_UNITS];
				my $retail  	= $line->[FIELD_SALEPRICE];
				my $total_price	= $line->[FIELD_TOTALPRICE];
				my $price = $total_price / $units if($units > 0);
                my $ppdRate = (defined $retail && $retail != 0)?($price / $retail):'';

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

					$sale->productType($prodtype);
					$sale->formatType($format);
					$sale->dateBegin($dateBegin);
					$sale->dateEnd($dateEnd);
				    $sale->serviceProductID($vendorID);
		   		    $sale->isrc($isrc);
					$sale->artistName($artist);
					$sale->trackName($track);
					$sale->units($units);
					$sale->price($price);
					$sale->retail($retail) if ($self->{clientID} == 21);
					$sale->priceType($priceType) if ($self->{clientID} == 21);
                    $sale->wholesaleRate($ppdRate) if ($self->{clientID} == 21);
                    $sale->serviceID($service_id) if ($self->{clientID} == 21);
					$sale->lineNum($linenum);

					$self->_insertSale(sale => $sale);
				}
                #else { print STDERR "skipping u: $units , p: $price , a: $artist , t: $track\n"; }
			}
			$linenum++;
		} # foreach line
		$retval += $linenum;
        $sheet_num++;
	} # foreach sheet

	return $retval;
}

#
# Private methods
#

sub matchHeader
{
	my $line = shift;

	# verify the column headings are as we expect them
	my $artist	= $line->[FIELD_ARTIST];
	my $track	= $line->[FIELD_TITLE];
	my $units	= $line->[FIELD_UNITS];
	my $price	= $line->[FIELD_ISRC];

	if($artist =~ /author/i &&
	   $track  =~ /product/i &&
	   $units  =~ /units/i &&
	   $price  =~ /isrc/i)
	{
		return 1;
	}
	return 0;
}


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