#------------------------------------------------------------
# Copyright (C) 2009 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package BookPub::DB::Item::POSSale;
use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::Assert;
use Common::Client;
use Common::DB::Item::OnixCode;
use base 'Common::DB::Item';

use constant kTable => 'pos_sale';
use constant kDB    => Common::DB::Item::kClientDB;

use constant STATUS_MATCH         => 1;
use constant STATUS_NOMATCH       => 2;
use constant STATUS_MULTIMATCH    => 3;
use constant STATUS_MAPPED        => 4;
use constant STATUS_UNRECOVERABLE => 5;
use constant STATUS_AUTO_MAPPED   => 6;
use constant STATUS_BATCH_MAPPED  => 7;
use constant STATUS_DONT_MATCH    => 8;

sub GetSalesWithMapID {
    my ( $class, $mapID ) = @_;
    assert($mapID);

    $mapID = $class->quote($mapID);

    my $sql = "SELECT * FROM " . kTable . " WHERE map_id = $mapID";
    return $class->GetAll($sql);
}

sub GetFirstSaleError {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT MIN(pos_sale_id) as pos_sale_id FROM "
      . kTable
      . " WHERE pos_file_id = ?"
      . " AND map_id IS NULL"
      . " AND import_status IN (?, ?, ?)";

    my @sqlParams = ($fileID, STATUS_NOMATCH, STATUS_MULTIMATCH, STATUS_UNRECOVERABLE);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{pos_sale_id};
    }

    return undef;
}

sub GetFirstException {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT MIN(pos_sale_id) as pos_sale_id FROM "
      . kTable
      . " WHERE pos_file_id = ? AND product_id IS NULL"
      . " AND import_status != ?";

    my @sqlParams = ($fileID, STATUS_DONT_MATCH);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{pos_sale_id};
    }

    return undef;
}

sub GetPreviousErrorID {
    my ( $class, $saleID, $fileID ) = @_;
    assert($saleID);
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT max(pos_sale_id) as pos_sale_id FROM "
      . kTable
      . " WHERE pos_file_id = ?"
      . " AND pos_sale_id < ?"
      . " AND import_status IN (?, ?, ?)";

    my @sqlParams = ($fileID, $saleID, STATUS_NOMATCH, STATUS_MULTIMATCH, STATUS_UNRECOVERABLE);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{pos_sale_id};
    }

    return undef;
}

sub GetPreviousUnfixedID {
    my ( $class, $saleID, $fileID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT max(pos_sale_id) as pos_sale_id FROM "
      . kTable
      . " WHERE pos_file_id = ?"
      . " AND pos_sale_id < ?"
      . " AND import_status IN (?, ?, ?)"
      . " AND map_id IS NULL";

    my @sqlParams = ($fileID, $saleID, STATUS_NOMATCH, STATUS_MULTIMATCH, STATUS_UNRECOVERABLE);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{pos_sale_id};
    }

    return undef;
}

sub GetNextErrorID {
    my ( $class, $saleID, $fileID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT min(pos_sale_id) as pos_sale_id FROM "
      . kTable
      . " WHERE pos_file_id = ?"
      . " AND pos_sale_id > ?"
      . " AND import_status IN (?, ?, ?)";

    my @sqlParams = ($fileID, $saleID, STATUS_NOMATCH, STATUS_MULTIMATCH, STATUS_UNRECOVERABLE);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{pos_sale_id};
    }

    return undef;
}

sub GetNextUnfixedID {
    my ( $class, $saleID, $fileID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT min(pos_sale_id) as pos_sale_id FROM "
      . kTable
      . " WHERE pos_file_id = ?"
      . " AND pos_sale_id > ?"
      . " AND import_status IN (?, ?, ?)"
      . " AND map_id IS NULL";

    my @sqlParams = ($fileID, $saleID, STATUS_NOMATCH, STATUS_MULTIMATCH, STATUS_UNRECOVERABLE);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{pos_sale_id};
    }

    return undef;
}

sub CalculateFileSummaryInfo {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();

    my $revenueFormula = 'revenue';
    my $unitsFormula   = 'units';

    my $whereClauseA = " WHERE pos_file_id = " . $class->quote($fileID);

    my $sql = "SELECT A.total_units, A.records, A.revenue, B.total_exceptions, C.remaining_exceptions" . " FROM"

      # A - query
      . " ("
      . " SELECT ifnull(sum($unitsFormula),0) AS total_units,"
      . " count(*) AS records,"
      . " round(ifnull(sum($revenueFormula),0), 2) AS revenue"
      . " FROM pos_sale"
      . $whereClauseA . " ) A,"

      # B - query
      . " ( "
      . " SELECT count(*) AS total_exceptions"
      . " FROM pos_sale"
      . " WHERE pos_file_id = " . $class->quote($fileID0
      . " AND import_status!="
      . STATUS_MATCH
      . " AND import_status!="
      . STATUS_MAPPED
      . " AND import_status!="
      . STATUS_AUTO_MAPPED
      . " AND import_status!="
      . STATUS_BATCH_MAPPED
      . " AND import_status!="
      . STATUS_DONT_MATCH . " ) B,"

      # C - query
      . " ("
      . " SELECT count(*) AS remaining_exceptions"
      . " FROM pos_sale"
      . " WHERE pos_file_id = " . $class->quote($fileID)
      . " AND import_status!="
      . STATUS_MATCH
      . " AND import_status!="
      . STATUS_MAPPED
      . " AND import_status!="
      . STATUS_AUTO_MAPPED
      . " AND import_status!="
      . STATUS_BATCH_MAPPED
      . " AND import_status!="
      . STATUS_DONT_MATCH
      . " AND map_id IS NULL" . " ) C";

    my $sth = $dbo->DoCmd($sql);

    my ( $units, $records, $revenue, $totalExceptions, $remainingExceptions );
    if ( defined $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref();

        $units               = $href->{total_units};
        $records             = $href->{records};
        $revenue             = $href->{revenue};
        $totalExceptions     = $href->{total_exceptions};
        $remainingExceptions = $href->{remaining_exceptions};
    }

    my $currencies = $class->GetCurrenciesByFileID($fileID);
    my $currencyCode =
        !$currencies             ? undef
      : scalar(@$currencies) > 1 ? 'MUL'
      :                            $currencies->[0][0];

#    Common::Log::Print("!!! units $units  records $records  revenue $revenue  totalExceptions $totalExceptions  remainingExceptions $remainingExceptions  currencyCode $currencyCode");

    return ( $units, $records, $revenue, $totalExceptions, $remainingExceptions, $currencyCode );
}

sub GetCurrenciesByFileID {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = 'SELECT DISTINCT(currency_code) AS currency FROM ' . kTable . " WHERE pos_file_id = ? ORDER BY currency";
    my @sqlParams = ($fileID);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);

    return $sth->rows() ? $sth->fetchall_arrayref( [0] ) : undef;
}

sub CountRemainingExceptions {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT count(*) AS remaining_exceptions FROM pos_sale WHERE pos_file_id = ?"
      . " AND import_status != ?"
      . " AND import_status != ?"
      . " AND import_status != ?"
      . " AND import_status != ?"
      . " AND import_status != ?"
      . " AND map_id IS NULL";

    my @sqlParams = ($fileID, STATUS_MATCH, STATUS_MAPPED, STATUS_AUTO_MAPPED, STATUS_BATCH_MAPPED, STATUS_DONT_MATCH);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( defined $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref();
        return $href->{remaining_exceptions};
    }

    return 0;
}

sub GetMinDate {
    my ( $class, $serviceID, $productID ) = @_;
    return undef if ( !$serviceID );

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT MIN(date_end) from " . kTable . " WHERE service_id = ?";
    my @sqlParams = ($serviceID);

    if ($productID) {
        $sql .= " AND product_id = ? ";
        push @sqlParams, $productID;
    }

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    my ($date) = $sth->fetchrow_array();
    return $date;
}


1;