#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::LicenseReserve;
use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::DB::Item;
use base 'Common::DB::Item';

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


# !!! Is this a deprecated class?

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

	my $dbo = Common::RSApp::GetClientDB();

	my $sql = "SELECT * FROM license_reserve WHERE track_license_id=$licenseID AND product_id = $productID ORDER BY date_created";

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


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

	my $sql = "SELECT * FROM license_reserve WHERE product_id=$id";
    return $class->GetAll($sql);
}


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

	my $sql = "SELECT * FROM license_reserve WHERE original_statement_item_id=$id";
    return $class->GetAll($sql);
}


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

	my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) FROM " . kTable . " WHERE track_license_id=$id";
    my $sth = $dbo->DoCmd($sql);
    my $hr = $sth->fetchrow_hashref();
    return $hr->{'COUNT(*)'};
}


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

	my $dbo = Common::RSApp::GetClientDB();

    # !!! This old query looks inoccuous, but it's actually quite slow.
    # !!! So instead, we'll do this in a loop.
    #my $sql = "DELETE from license_reserve where original_statement_item_id in (select mechanical_statement_item_id from mechanical_statement_item where mechanical_statement_id = $id)";
   #$dbo->DoCmd($sql);

    my $allItems = RPS::DB::Item::MechanicalStatementItem->GetByMechanicalStatementID($id);
    while (my $item = $allItems->next())
    {
        my $itemID = $item->mechanical_statement_item_id;
        my $sql = "DELETE FROM license_reserve WHERE original_statement_item_id=$itemID";
        $dbo->DoCmd($sql);
    }

}

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

	my $sql = "SELECT * FROM license_reserve WHERE (original_statement_item_id = 0 OR original_statement_item_id in (select mechanical_statement_item_id from mechanical_statement_item where mechanical_statement_id in (select mechanical_statement_id from mechanical_statement where mechanical_run_id in (select mechanical_run_id from mechanical_run where status in (2,5))))) AND periods_remaining > 0";
	return $class->SUPER::GetAll($sql);
}


sub GetAllCommittedByTrackLicenseID
{
	my ($class, $trackLicenseID) = @_;

	my $sql = "SELECT * FROM license_reserve WHERE track_license_id=$trackLicenseID AND (original_statement_item_id = 0 OR original_statement_item_id in (select mechanical_statement_item_id from mechanical_statement_item where mechanical_statement_id in (select mechanical_statement_id from mechanical_statement where mechanical_run_id in (select mechanical_run_id from mechanical_run where status in (2,5))))) AND periods_remaining > 0";
	return $class->SUPER::GetAll($sql);
}

# NEW METHODS BELOW PER FB15315.
#
sub GetByMechanicalRunID
{
	my ($class, $runID) = @_;

	my $sql = "SELECT * FROM " . kTable . " WHERE (original_statement_item_id = 0 OR original_statement_item_id in (select mechanical_statement_item_id from mechanical_statement_item where mechanical_statement_id in (select mechanical_statement_id from mechanical_statement where mechanical_run_id in (select mechanical_run_id from mechanical_run where mechanical_run_id=$runID)))) AND periods_remaining > 0";
	return $class->SUPER::GetAll($sql);
}

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

	my $sql = "SELECT * FROM " . kTable . " WHERE liquidated_run_id = $runID";
	return $class->SUPER::GetAll($sql);
}

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

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

	# Historical reserves
	$sql .= "(original_statement_item_id = 0 AND track_license_id IN (SELECT track_license_id FROM track_license WHERE payor_id=$payorID))";

	$sql .= " OR ";

	# Non-historical reserves
	$sql .= "(original_statement_item_id in (select mechanical_statement_item_id from mechanical_statement_item where mechanical_statement_id in (select mechanical_statement_id from mechanical_statement where mechanical_run_id in (select mechanical_run_id from mechanical_run where 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 mechanical reserve pipeline
	# report for a committed run.
	if( $runID )
	{
		$sql .= " AND mechanical_run_id != $runID";
	}

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

###
1;#
###