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

use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::DB::Item;
use base 'Common::DB::Item';

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



sub GetByProductID
{
    my ($class, $productID) = @_;

	my $sql = "SELECT * FROM ".kTable." WHERE product_id=$productID";

    return $class->GetAll($sql);
}

sub DeleteByArtistRoyaltyRunID
{
    my ($class, $runID) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    my $sql = "DELETE artist_contract_term_reserve.* " .
	          "FROM artist_contract_term_reserve " .
                    "INNER JOIN artist_royalty_income_item ON ( original_statement_item_id = artist_royalty_income_item_id ) " .
					"INNER JOIN artist_royalty_album USING ( artist_royalty_album_id ) " .
					"INNER JOIN artist_royalty_statement USING ( artist_royalty_statement_id ) " .
			  "WHERE artist_royalty_run_id = " . $dbo->DBQuote($runID);
    $dbo->DoCmd($sql);
}

sub GetByArtistRoyaltyRunID
{
    my ($class, $runID) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    # Find all reserves attached to payable terms for the specified run.
    #
    my $sql = "SELECT * " .
              "FROM " . kTable . " " .
              "INNER JOIN new_artist_contract_term ct ON ( " .
                 " ct.artist_contract_term_id = artist_contract_term_reserve.artist_contract_term_id " .
                 " AND ct.contract_rate_type_id != 9 ) " .
              "INNER JOIN artist_royalty_income_item ON ( original_statement_item_id = artist_royalty_income_item_id ) " .
              "INNER JOIN artist_royalty_album USING ( artist_royalty_album_id ) " .
              "INNER JOIN artist_royalty_statement USING ( artist_royalty_statement_id ) " .
              "WHERE artist_royalty_run_id = " . $dbo->DBQuote($runID);

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

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

    my $sql = "SELECT * FROM " . kTable . " " .
              "INNER JOIN new_artist_contract_term ct ON ( " .
                 " ct.artist_contract_term_id = artist_contract_term_reserve.artist_contract_term_id " .
                 " AND ct.contract_rate_type_id != 9 ) " .
              " WHERE liquidated_run_id = $runID";
		return $class->GetAll($sql);
}

sub GetAllCommitted
{
	my ($class) = @_;

	my $sql = "SELECT * FROM " . kTable . " WHERE (original_statement_item_id = 0 OR original_statement_item_id in (select artist_royalty_income_item_id from artist_royalty_income_item where artist_royalty_album_id in (select artist_royalty_album_id from artist_royalty_album where artist_royalty_statement_id in (select artist_royalty_statement_id from artist_royalty_statement where artist_royalty_run_id in (select artist_royalty_run_id from artist_royalty_run where status in (2,5)))))) AND periods_remaining > 0";
	return $class->SUPER::GetAll($sql);
}

sub GetAllCommittedByPayorID
{
	my ($class, $payorID, $runID) = @_;

	my $sql = "SELECT * FROM " . kTable . " ".
              "INNER JOIN new_artist_contract_term ct ON ( " .
                 " ct.artist_contract_term_id = artist_contract_term_reserve.artist_contract_term_id " .
                 " AND ct.contract_rate_type_id != 9 ) " .
              " WHERE (";
	# Historical reserves
	$sql .= "(original_statement_item_id = 0 AND artist_contract_term_reserve.artist_contract_term_id IN (SELECT artist_contract_term_id FROM new_artist_contract_term WHERE artist_contract_id IN (SELECT artist_contract_id FROM new_artist_contract WHERE payor_id = $payorID)))";

	$sql .= " OR ";

    # Non-historical reserves
	$sql .= "(original_statement_item_id in (select artist_royalty_income_item_id from artist_royalty_income_item where artist_royalty_album_id in (select artist_royalty_album_id from artist_royalty_album where artist_royalty_statement_id in (select artist_royalty_statement_id from artist_royalty_statement where artist_royalty_run_id in (select artist_royalty_run_id from artist_royalty_run where status in (2,5) and payor_id = $payorID";

	# If a run id is passed in, we want to exclude that run from this list.
	# We need do that so that we can regenerate the artist reserve pipeline report
	# for a committed run.
	if ($runID)
	{
	    $sql .= " AND artist_royalty_run_id != $runID";
	}

	$sql .= ")))))) AND periods_remaining > 0";
	return $class->SUPER::GetAll($sql);
}

###
1;#
###