#------------------------------------------------------------
# Copyright (C) 2009 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package BookPub::DB::Item::SaleRevenue;
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 => 'sale';
use constant kDB    => Common::DB::Item::kClientDB;

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

    my $config = {};

    $config->{service_id} = { _readOnly => 1 };
    $config->{date}       = { _readOnly => 1 };
    $config->{revenue}    = { _readOnly => 1 };

    return $config;
}

sub ReportQuery {
    my ( $class, $productID, $serviceID, $dateStart, $dateEnd ) = @_;
    assert($productID);
    assert($serviceID);
    assert($dateStart);
    assert($dateEnd);

    # productID can be either a single id, or a reference to an array of ids.
    # We'll convert the single ID to an array of one
    #
    my @quotedProductIDs;
    if ( 'ARRAY' ne ref($productID) ) {
        push @quotedProductIDs, $class->quote($productID);
    } else {
        foreach my $id (@$productID) {
            push @quotedProductIDs, $class->quote($id);
        }
    }
    my $productIDString = join( ',', @quotedProductIDs );

    # JPK - Using 'date_end' as the cannonical date for sale...
    #
    my $sql =
        "SELECT file.service_id AS service_id, sale.date_end AS date, SUM(sale.revenue) AS revenue"
      . " FROM sale, file"
      . " WHERE sale.file_id = file.file_id"
      . " AND product_id IN ( $productIDString )"
      . " AND file.service_id=$serviceID"
      . " AND sale.date_end >= "
      . $class->quote($dateStart)
      . " AND sale.date_end <= "
      . $class->quote($dateEnd)
      . " GROUP BY 2 ORDER BY 2";

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

1;