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

# 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 lib '/app/tools/bookpub/lib';
use Common::Assert;
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 },
        sale_start_date => { _readOnly => 1 },
        sale_end_date   => { _readOnly => 1 },
        sale_service_id => { _readOnly => 1 },
        file_service_id => { _readOnly => 1 },
        orig_file_name  => { _readOnly => 1 },
        isbn13          => { _readOnly => 1 },
        isbn10          => { _readOnly => 1 },
        title           => { _readOnly => 1 },
        subtitle        => { _readOnly => 1 },
        publisher       => { _readOnly => 1 },
        imprint         => { _readOnly => 1 },
        book_format_id  => { _readOnly => 1 },
        country         => { _readOnly => 1 },
        units           => { _readOnly => 1 },
        price_type      => { _readOnly => 1 },
        list_price      => { _readOnly => 1 },
        currency        => { _readOnly => 1 },
        discount        => { _readOnly => 1 },
        net_price       => { _readOnly => 1 },
        conversion_rate => { _readOnly => 1 },
        revenue         => { _readOnly => 1 },
        fee             => { _readOnly => 1 },
    };
}

sub GetReportData {
    my ( $class, $periodID ) = @_;

    my @select = (
        'sale.product_id AS product_id',
        'sale.file_id AS file_id',
        'book.book_id AS book_id',
        'sale.date_begin AS sale_start_date',
        'sale.date_end AS sale_end_date',
        'sale.service_id as sale_service_id',
        'file.service_id as file_service_id',
        'file.orig_file_name AS orig_file_name',
        'book_product.isbn13 AS isbn13',
        'book_product.isbn10 AS isbn10',
        'book.title AS title',
        'book.subtitle AS subtitle',
        'imprint.name AS imprint',
        'publisher.name AS publisher',
        'book_product.book_format_id AS book_format_id',
        'sale.country_code AS country',
        'SUM(sale.units) AS units',
"CASE sale.price_type_id WHEN 1 THEN 'RRP' WHEN 2 THEN 'RRPPlusTax' WHEN 41 THEN 'Agency' WHEN 42 THEN 'AgencyPlusTax' ELSE NULL END as price_type",
        'sale.list_price AS list_price',
        'IF( sale.r_list_price_currency IS NULL, sale.currency_code, sale.r_list_price_currency ) AS currency',
        'ROUND(sale.discount * 100, 0) AS discount',
        'sale.unit_price AS net_price',
        'sale.conversion_rate as conversion_rate',
        'SUM(sale.revenue) AS revenue',
        'IF(sale.service_id,sale.service_id,file.service_id) AS sort_service_id',
        'sale.r_fee AS fee',
    );

    my @from = (
        'book_product',
        'JOIN sale USING(product_id)',
        'JOIN book USING(book_id)',
        'LEFT JOIN imprint USING(imprint_id)',
        'LEFT JOIN publisher USING(publisher_id)',
        'JOIN file USING(file_id)',
    );

    my @groupBy = (
        'sale_start_date', 'sale_end_date', 'currency',   'country',   'file_service_id', 'sale_service_id',
        'product_id',      'price_type',    'list_price', 'net_price', 'discount',        'conversion_rate',
    );

    my @orderBy = ( 'sort_service_id', 'title', 'subtitle', );

    my @where;
    push @where, "sale.file_id IN (select file_id from file where period_id=$periodID AND file_status=5)";

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

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

1;