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