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.
###