#------------------------------------------------------------
# Copyright (C) 2007 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::ReportQueries::UKMechUnitSummary;

use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::McpsStatementItem;
use RPS::DB::Item::ReportQueries;
use RPS::DB::Item::ProductType;

use base 'RPS::DB::Item::ReportQueries';

sub _config {
    return {
        'catalog_number'  => {},
        'album_title'     => {},
        'album_id'        => {},
        'artist_name'     => {},
        'product_type'    => {},
        'product_code'    => {},
        'country_code'    => {},
        'final_net_units' => {},

    };
}

# This will return a list of products that are included in the run.
#
sub GetUnitSummary {
    my ( $class, $runID, $download ) = @_;

    # First, create a temporary table for this report.
    #
    my $dbo = Common::RSApp::GetClientDB();

    $dbo->DoCmd('DROP TABLE IF EXISTS zzuk_mech_unit_summary');
    $dbo->DoCmd( 'CREATE TEMPORARY TABLE zzuk_mech_unit_summary ('
          . '`catalog_number` varchar(255),'
          . '`album_title` varchar(255),'
          . '`album_id` int(10) unsigned,'
          . '`artist_name` varchar(255),'
          . '`product_type` varchar(25),'
          . '`product_code` varchar(25),'
          . '`country_code` varchar(2),'
          . '`units` int(10) )' );

    my $statementItems = RPS::DB::Item::McpsStatementItem->GetByRunID($runID);

    while ( $statementItems->hasNext() ) {
        my $item = $statementItems->next();

        my $productType = _idToProductType( $item->product_type_id );

        $dbo->DoCmdWithPlaceholders(
            'INSERT INTO zzuk_mech_unit_summary SET'
              . " catalog_number= ?,"
              . " album_title= ?,"
              . " album_id= ?,"
              . " artist_name= ?,"
              . " product_type= ?,"
              . " product_code= ?,"
              . " country_code= ?,"
              . " units= ?",
            [
                $item->catalog_number, $item->album_title,  $item->album_id,     $item->artist_name,
                $productType,          $item->product_code, $item->country_code, $item->final_net_units,
            ]
        );

    }

    my $sql;

    # If we are serving up the downloadable version, we'll do unit totals by product/country
    # If not, we'll do unit totals by product (compressing countries together).

    if ($download) {
        $sql =
"SELECT catalog_number, album_title, album_id, artist_name, product_type, product_code, country_code, SUM(units) as final_net_units FROM zzuk_mech_unit_summary GROUP BY album_id, product_type, country_code ORDER BY catalog_number, product_type, country_code";
    } else {
        $sql =
"SELECT catalog_number, album_title, album_id, artist_name, product_type, product_code, SUM(units) as final_net_units FROM zzuk_mech_unit_summary GROUP BY album_id, product_type ORDER BY catalog_number, product_type";
    }

    return $class->SUPER::GetAll($sql);

}

sub _idToProductType {
    my ($id) = @_;

    # We have a table for this, might as well use it.
    #
    my $productTypeEntry = RPS::DB::Item::ProductType->Lookup( product_type_id => $id );
    if ( !$productTypeEntry ) {
        return '???';
    }
    return $productTypeEntry->description();
}

1;