#------------------------------------------------------------
# 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 lib '/app/tools/rps/lib';

use RPS::RoyaltyRun::Status;
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 = "
        . $class->quote($licenseID)
        ." AND product_id = "
        . $class->quote($productID)
        . " ORDER BY date_created";

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

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

    my $sql = "SELECT * FROM " . kTable . " WHERE product_id = " . $class->quote($id);
    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 CountByTrackLicenseID {
    my ( $class, $id ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) FROM " . kTable . " WHERE ca_track_license_id = ?";
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$id]);
    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 = ?)";
    my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmdWithPlaceholders($sql, [$id]);

}

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 = "
      . $class->quote($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 $run     = RPS::DB::Item::CAMechanicalRun->Lookup( ca_mechanical_run_id => $runID );
    my $payorID = $run->payor_id;

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

    # Historical reserves for the same payor
    $sql .= "(original_statement_item_id = 0 AND ca_track_license_id IN (SELECT ca_track_license_id FROM ca_track_license WHERE payor_id = " . $class->quote($payorID) . "))";

    $sql .= " OR ";

    # Non-historical reserves for the run
    $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 = " . $class->quote($runID)
          .")))) AND periods_remaining > 0";

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

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

    my $sql = "SELECT * FROM " . kTable . " WHERE liquidated_run_id = ". $class->quote($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 = " . $class->quote($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 = " . $class->quote($payorID) . " AND status != " . RPS::RoyaltyRun::Status::kInvalidated;

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

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

###
1;    #
###