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

use lib '/app/tools/common/lib';
use Common::Assert;

use base 'Common::DB::Item';

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

sub GetUnapproved {
    my ($class) = @_;

    my $sql = 'SELECT * FROM ' . kTable . " WHERE approved != 1";
    return $class->GetAll($sql);
}

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

    $fileID = $class->quote($fileID);
    my $sql = 'SELECT * FROM ' . kTable . " LEFT JOIN sale USING (sale_id) WHERE approved != 1 AND file_id = $fileID";
    return $class->GetAll($sql);
}

sub GetUnapprovedByServiceID {
    my ( $class, $serviceID ) = @_;
    assert($serviceID);

    $serviceID = $class->quote($serviceID);
    my $sql =
        'SELECT * FROM '
      . kTable
      . " LEFT JOIN sale USING (sale_id) LEFT JOIN file USING (file_id) WHERE approved != 1 AND file.service_id = $serviceID";
    return $class->GetAll($sql);
}

sub GetRelated {
    my ( $class, %args ) = @_;

    my $sale_id = $args{sale_id};
    my $file_id = $args{file_id};

    my $sale = BookPub::DB::Item::Sale->Lookup( sale_id => $sale_id );
    return if ( !$sale );
    return if ( $file_id && $sale->file_id != $file_id );

    my $file = BookPub::DB::Item::File->Lookup( file_id => $sale->file_id );
    return if ( !$file );

    my $exception = BookPub::DB::Item::SalePriceException->Lookup( sale_id => $sale_id );
    return if ( !$exception );

    my $sql =
        "SELECT sale_id FROM sale"
      . " INNER JOIN file using (file_id)"
      . " INNER JOIN sale_price_exception USING (sale_id)"
      . " WHERE file.service_id = "
      . $file->service_id
      . " AND date_begin = "
      . $class->quote( $sale->date_begin )
      . " AND date_end = "
      . $class->quote( $sale->date_end )
      . " AND product_id = "
      . $sale->product_id
      . " AND list_price = "
      . $sale->list_price
      . " AND sale.currency_code = "
      . $class->quote( $sale->currency_code )
      . " AND price_type_id = "
      . $sale->price_type_id
      . " AND country_code = "
      . $class->quote( $sale->country_code )
      . " AND price "
      . ( defined( $exception->price ) ? "= " . $exception->price : "IS NULL" )
      . " AND variance = "
      . $class->quote( $exception->variance )
      . " AND approved = "
      . $exception->approved;

    $sql .= " AND file_id = $file_id" if ($file_id);

    return $class->GetAll($sql);
}

1;