#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::McpsStatementItem;
use strict;
use warnings;
use Data::Dumper;
use lib '/app/tools/common/lib';
use Common::Assert;
use Common::DB::Item;
use Common::DB::ItemCollection;
use base 'Common::DB::Item';

use constant kTable => 'mcps_statement_item';
use constant kDB => Common::DB::Item::kClientDB();


# These 'price types' are enums defined in the table.
#
use constant kPriceTypeDealer => 'D';
use constant kPriceTypeRetail => 'R';
use constant kPriceTypeSalesDocument => 'L';
use constant kPriceTypeDeleted => 'I';

sub GetByMCPSStatementIDSorted
{
    my ($class, $id) = @_;

	my $sql = "SELECT * FROM " . kTable . " WHERE mcps_statement_id=$id"
     . " ORDER BY album_title,product_type_id,adjustment,deleted,price, IF (country_code='GB','AA',country_code)";
    return $class->GetAll($sql);
}


sub GetByMCPSStatementID
{
    my ($class, $id) = @_;

	my $sql = "SELECT * FROM " . kTable . " WHERE mcps_statement_id=$id";
    return $class->GetAll($sql);
}


sub DeleteByMCPSStatementID
{
    my ($class, $id) = @_;

	  my $sql = "DELETE from " . kTable . " WHERE mcps_statement_id=$id";
	  my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmd($sql);
}


sub GetByRunID
{
    my ($class, $runID) = @_;
    my $sql = "SELECT * FROM " . kTable . " WHERE mcps_statement_id IN (SELECT mcps_statement_id FROM mcps_statement WHERE uk_mechanical_run_id = $runID)";
    return $class->GetAll($sql);
}

sub GetItemsWithNegativeUnitsByRunID
{
    my ($class, $runID) = @_;
    my $sql = "SELECT * FROM " . kTable . " WHERE mcps_statement_id IN (SELECT mcps_statement_id FROM mcps_statement WHERE uk_mechanical_run_id = $runID) AND final_net_units < 0";
    return $class->GetAll($sql);
}


sub GetNumItemsByStatementID
{
    my ($class, $id) = @_;

    my $sql = "SELECT COUNT(*) FROM mcps_statement_item WHERE mcps_statement_id=$id";
	  my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);

    my $hashref = $sth->fetchrow_hashref;

    return $hashref->{'COUNT(*)'};
}


sub GetUniqueCountriesByRun
{
    my ($class, $runID) = @_;
    assert($runID);

    my $sql = "SELECT DISTINCT country_code FROM " . kTable . " " .
              "LEFT JOIN mcps_statement ON " .
              "(" . kTable . ".mcps_statement_id = mcps_statement.mcps_statement_id) " . 
              "WHERE mcps_statement.uk_mechanical_run_id = " . $runID . " " .
              "ORDER BY country_code";
    
    return $class->SUPER::GetAll($sql);    
    
}


sub GetSalesByStatementAndLicense
{
	my ($class, $statementID, $mcpsLicenseID) = @_;
    my $sql = "SELECT * FROM " . kTable . " " .
              "WHERE mcps_statement_id = $statementID " .
              "AND mcps_license_id = $mcpsLicenseID " .
              "AND deleted = 0 " .
              "AND adjustment = 0";
    
    return $class->GetAll($sql);
}


sub GetAdjustmentsByStatement
{
	my ($class, $statementID) = @_;
    my $sql = "SELECT * FROM " . kTable . " " .
              "WHERE mcps_statement_id = $statementID " .
              "AND deleted = 0 " .
              "AND adjustment = 1 " .
              "ORDER BY catalog_number";
    
    return $class->GetAll($sql);
}


sub GetDeletionsByStatement
{
	my ($class, $statementID) = @_;
    my $sql = "SELECT * FROM " . kTable . " " .
              "WHERE mcps_statement_id = $statementID " .
              "AND deleted = 1 " .
              "AND adjustment = 0 " .
              "ORDER BY catalog_number";
    
    return $class->GetAll($sql);
}


sub GetDeletionAdjustmentsByStatement
{
	my ($class, $statementID) = @_;
    my $sql = "SELECT * FROM " . kTable . " " .
              "WHERE mcps_statement_id = $statementID " .
              "AND deleted = 1 " .
              "AND adjustment = 1 " .
              "ORDER BY catalog_number";
    
    return $class->GetAll($sql);
}


sub GetRetentionsByLicenseAndRun
{
    my ($class, $mcpsLicenseID, $runID, $tvAdvertised) = @_;
    
    my $dbo = Common::RSApp::GetClientDB();
    
    my $retentions = "retentions";
    
    if ($tvAdvertised == 1)
    {
        $retentions = "tv_retentions";
    }
    
    my $sql = "SELECT SUM($retentions) FROM " . kTable . " WHERE mcps_license_id = $mcpsLicenseID AND mcps_statement_id IN (SELECT mcps_statement_id FROM mcps_statement WHERE uk_mechanical_run_id = $runID)";

    my $sth = $dbo->DoCmd( $sql );
    
    my ($sum) = $sth->fetchrow_array();    
    
    $sum += 0;
    
    return $sum;
}


1;