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

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'       => {},
        'country_code'      => {},
        'product_type'      => {},
        'product_code'      => {},
        'price'             => {},
        'final_net_units'   => {},

    };
}


# This will return a list of products that have negative unit balance.
#

sub GetNegativeUnits    
{    
    my ($class, $runID) = @_;
    
    # First, create a temporary table for this report.
    #
    my $dbo = Common::RSApp::GetClientDB();

    $dbo->DoCmd('DROP TABLE IF EXISTS zzuk_mech_negative_units');
    $dbo->DoCmd('CREATE TEMPORARY TABLE zzuk_mech_negative_units ('
    . '`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),'
    . '`price` decimal(18,8),'
    . '`final_net_units` int(10) )');    
    
    my $statementItems = RPS::DB::Item::McpsStatementItem->GetItemsWithNegativeUnitsByRunID($runID);
 
 
	while ($statementItems->hasNext())
	{
		my $item = $statementItems->next();    
		
        my $productType = _idToProductType($item->product_type_id);

        $dbo->DoCmdWithPlaceholders('INSERT INTO zzuk_mech_negative_units SET'
        . " catalog_number= ?,"
        . " album_title= ?,"
        . " album_id= ?,"        
        . " artist_name= ?,"
        . " product_type= ?,"
        . " product_code= ?,"
        . " country_code= ?,"
        . " price= ?,"
        . " final_net_units= ?",
        [
            $item->catalog_number,
            $item->album_title,
            $item->album_id,
            $item->artist_name,
            $productType,
            $item->product_code,
            $item->country_code,
            $item->price,
            $item->final_net_units,
        ]
        );   
                         
    }    
    
    my $sql = "SELECT * FROM zzuk_mech_negative_units ORDER BY catalog_number, product_type, country_code";
    
    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;