#---------------------------------------------------------------
# ____                   _ _         ____  _
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/
#
# Copyright (C) 2010 RoyaltyShare, Inc.   All Rights Reserved
#---------------------------------------------------------------
package BookPub::DB::Item::RoyaltyReport::Hachette;

# This is a 'virtual' DB::Item class - There isn't a single table associated with it.
# Instead we will join some tables together.

use strict;
use warnings;

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

use BookPub::DB::Item::Sale;

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

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

    return {
        product_id      => { _readOnly => 1 },
        file_id         => { _readOnly => 1 },
        book_id         => { _readOnly => 1 },
        period          => { _readOnly => 1 },
        date_begin      => { _readOnly => 1 },
        date_end        => { _readOnly => 1 },
        isbn            => { _readOnly => 1 },
        units           => { _readOnly => 1 },
        unit_price      => { _readOnly => 1 },
        revenue         => { _readOnly => 1 },
        purchase_price  => { _readOnly => 1 },
        list_price      => { _readOnly => 1 },
        discount        => { _readOnly => 1 },
        onix_prod_code  => { _readOnly => 1 },
        price_type_id   => { _readOnly => 1 },
        free            => { _readOnly => 1 },
        country         => { _readOnly => 1 },
        state           => { _readOnly => 1 },
        postal_code     => { _readOnly => 1 },
        currency        => { _readOnly => 1 },
        title           => { _readOnly => 1 },
        subtitle        => { _readOnly => 1 },
        imprint_id      => { _readOnly => 1 },
        conversion_rate => { _readOnly => 1 },
        acct_currency   => { _readOnly => 1 },
    };
}

sub GetReportData {
    my ( $class, %args ) = @_;
    my $condition    = $args{condition};
    my $serviceID    = $args{serviceID};
    my $fileIDs      = $args{fileIDs};
    my $prodType     = $args{productType};
    my $currencyCode = $args{currencyCode};
    my $feedID       = $args{feedId};

    # !!! Hachette has added client service IDs that are for Canada sales only. !!!
    # Since we don't have a country_code column in the client_service table (not yet anyway),
    # we're going to have to use feed_id to store that info.
    #
    # The default value for feed_id is "ANY", so we don't need to do anything with it if
    # it's set to that.

    my $countryCodeCriteria;

    # We have a SAN for this specific feed, so we want to make sure that we don't pull it in other wise.
    #
    my $feedIDCriteria = "file.orig_file_name NOT LIKE 'LTRQQ%'";

    if ( $feedID eq 'CA' ) {
        $countryCodeCriteria = "sale.country_code = 'CA'";
    } elsif ( $feedID eq 'NOT CA' ) {
        $countryCodeCriteria = "sale.country_code != 'CA'";
    } elsif ( $feedID eq 'LTRQQ' ) {
        $feedIDCriteria = "file.orig_file_name LIKE 'LTRQQ%'";
    }

    my @select = (
        'sale.product_id AS product_id',
        'sale.file_id AS file_id',
        'book.book_id AS book_id',
        'EXTRACT(YEAR_MONTH FROM ADDDATE(sale.date_begin, DATEDIFF(sale.date_end, sale.date_begin) / 2)) as period',
        'sale.date_begin as date_begin',
        'sale.date_end as date_end',
        'IFNULL(book_product.isbn13, book_product.isbn10) AS isbn',
        'sale.units AS units',
        'sale.unit_price AS unit_price',
        'sale.revenue AS revenue',
        'sale.r_purchase_price AS purchase_price',
        'sale.list_price AS list_price',
        'ROUND(sale.discount * 100, 0) AS discount',
        'onix_code.code_value AS onix_prod_code',
        'sale.price_type_id AS price_type_id',
        'sale.is_free AS free',
        'sale.country_code AS country',
        'IF(file.service_id = 8, NULL, sale.state) AS state',
        'IF(file.service_id = 8, NULL, sale.postal_code) AS postal_code',
        'sale.currency_code AS currency',
        'book.title AS title',
        'book.subtitle AS subtitle',
        'book_product.imprint_id AS imprint_id',
        'conversion_rate AS conversion_rate',
        'IF(file.service_id = 9 AND LOCATE("85133615_", orig_file_name), "CAD", "USD") AS acct_currency',
    );

    my @from = (
        'book_product',
        'JOIN sale USING(product_id)',
        'JOIN file USING(file_id)',
        'JOIN book USING(book_id)',
        'JOIN book_format USING(book_format_id)',
        'JOIN book_format_type USING(book_format_type_id)',
        'JOIN onix_code USING(onix_code_id)',
    );

    my @orderBy = (
        'date_end',    'period', 'price_type_id',   'free',       'country',        'state',
        'postal_code', 'isbn',   'conversion_rate', 'unit_price', 'purchase_price', 'list_price',
        'discount',    'date_begin',
    );

    my @where;
    push @where, 'sale.file_id IN (' . join( ',', @$fileIDs ) . ')';
    push @where, "sale.revenue $condition 0";
    push @where, $feedIDCriteria;

    if ($countryCodeCriteria) {
        push @where, $countryCodeCriteria;
    }

    if ( $prodType ne 'ANY' ) {
        my $onixProdFormList = BookPub::DB::Item::Sale->MapProductTypeToOnixCodes($prodType);
        if ($onixProdFormList) {
            my $inList = join( "','", @$onixProdFormList );
            push @where, "code_value IN ('$inList')";
        }
    }

    my @having;
    push( @having, 'acct_currency = "' . $currencyCode . '"' );

    my $sql = "SELECT " . join( ',', @select ) . " FROM " . join( ' ', @from ) . " WHERE " . join( ' AND ', @where );

    $sql .= " HAVING " . join( ' AND ', @having );
    $sql .= " ORDER BY " . join( ',', @orderBy );

    #Common::Log::Print("Query: " . $sql);
    return $class->GetAll($sql);
}

1;