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

# 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 lib '/app/tools/bookpub/lib';
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};

    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',
        'sale.state AS state',
        '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("85086467", orig_file_name) AND sale.currency_code="CAD", "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";

    # Leaving this in for historical reasons.  This concept of a feedId does not
    # work, so if you pass anything in besides ANY as a feedId, you will not get
    # your expected results.  SB
    if ( $feedId ne 'ANY' ) {
        push @where, "file.orig_file_name LIKE '$feedId%'";
    }

    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;