#------------------------------------------------------------
# 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;