#------------------------------------------------------------
# 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 = " . $class->quote($productID);

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

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

    my $productID = join ',', map { $class->quote($_) } grep {defined $_} @productID;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) FROM " . kTable . " WHERE product_id IN($productID) and periods_remaining > 0";
    my $sth = $dbo->DoCmd($sql);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{'COUNT(*)'};
}

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->DoCmdWithPlaceholders($sql, [$runID]);
}

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 = "
      . $class->quote($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 ) = @_;

    $payorID = $class->quote($payorID);
    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 deleted = 0 AND 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) {
        $runID = $class->quote($runID);
        $sql .= " AND artist_royalty_run_id != $runID";
    }

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

###
1;    #
###