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

use constant kTable => 'ca_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 " . kTable . " WHERE ca_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 " . kTable . " WHERE product_id=$id";
    return $class->GetAll($sql);
}


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

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


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

    my $sql = "DELETE from " . kTable . " where original_statement_item_id in (select ca_mechanical_statement_item_id from ca_mechanical_statement_item where ca_mechanical_statement_id = $id)";
	my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmd($sql);

}

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

	my $sql = "SELECT * FROM " . kTable . " WHERE (original_statement_item_id = 0 OR original_statement_item_id in (select ca_mechanical_statement_item_id from ca_mechanical_statement_item where ca_mechanical_statement_id in (select ca_mechanical_statement_id from ca_mechanical_statement where ca_mechanical_run_id in (select ca_mechanical_run_id from ca_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 " . kTable . " WHERE ca_track_license_id=$trackLicenseID AND (original_statement_item_id = 0 OR original_statement_item_id in (select ca_mechanical_statement_item_id from ca_mechanical_statement_item where ca_mechanical_statement_id in (select ca_mechanical_statement_id from ca_mechanical_statement where ca_mechanical_run_id in (select ca_mechanical_run_id from ca_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 ca_mechanical_statement_item_id from ca_mechanical_statement_item where ca_mechanical_statement_id in (select ca_mechanical_statement_id from ca_mechanical_statement where ca_mechanical_run_id in (select ca_mechanical_run_id from ca_mechanical_run where ca_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 ca_track_license_id IN (SELECT ca_track_license_id FROM ca_track_license WHERE payor_id=$payorID))";

	$sql .= " OR ";

	# Non-historical reserves
	$sql .= "(original_statement_item_id in (select ca_mechanical_statement_item_id from ca_mechanical_statement_item where ca_mechanical_statement_id in (select ca_mechanical_statement_id from ca_mechanical_statement where ca_mechanical_run_id in (select ca_mechanical_run_id from ca_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 ca_mechanical_run_id != $runID";
	}

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

###
1;#
###