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

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

use lib '/app/tools/rps/lib';
use RPS::RoyaltyRun::Status;

use base 'RPS::DB::RoyaltyRunItem';

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

sub _table { kTable }

# Setting this here so it can be used by multiple functions below
#
my $openStatusTest =
    "(status = "
  . RPS::RoyaltyRun::Status::kComplete
  . " OR status = "
  . RPS::RoyaltyRun::Status::kRunning
  . " OR status = "
  . RPS::RoyaltyRun::Status::kWaitingToRun
  . " OR status = "
  . RPS::RoyaltyRun::Status::kWaitingToCommit . ")";

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . RPS::RoyaltyRun::Status::kCommitted
      . " AND ca_mechanical_run_id <> "
      . $class->quote($runID)
      . " AND payor_id=(select payor_id from "
      . kTable
      . " where ca_mechanical_run_id = "
      . $class->quote($runID)
      . ")";
    return $class->SUPER::GetAllLegacy($sql);
}

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

    my $sql = "SELECT count(*) from " . kTable . " WHERE " . $openStatusTest;

    $sql .= " AND ca_mechanical_run_id IN (SELECT ca_mechanical_run_id FROM ca_mechanical_statement WHERE ca_publisher_id = " . $dbo->DBQuote($publisherID) . " )";

    my $sth = $dbo->DoCmd($sql);
    my ($count) = $sth->fetchrow_array();

    return $count;
}

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

    my $status = join ', ', (RPS::RoyaltyRun::Status::kCommitted, RPS::RoyaltyRun::Status::kClosed);
    my $sql = "SELECT count(*) from " . kTable . " WHERE status IN ($status)";

    $sql .= " AND ca_mechanical_run_id IN (SELECT ca_mechanical_run_id FROM ca_mechanical_statement WHERE ca_publisher_id = " . $dbo->DBQuote($publisherID) . " )";

    my $sth = $dbo->DoCmd($sql);
    my ($count) = $sth->fetchrow_array();

    return $count;
}

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

    my $sql = "SELECT count(*) from " . kTable . " WHERE " . $openStatusTest;

    $sql .=
" AND ca_mechanical_run_id IN (SELECT ca_mechanical_run_id FROM ca_mechanical_statement WHERE ca_mechanical_statement_id IN (SELECT ca_mechanical_statement_id from ca_mechanical_statement_license WHERE region_id = " . $dbo->DBQuote($regionID) . ") )";

    my $sth = $dbo->DoCmd($sql);
    my ($count) = $sth->fetchrow_array();

    return $count;
}

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

    my $sql = "SELECT count(*) from " . kTable . " WHERE " . $openStatusTest;

    $sql .=
" AND ca_mechanical_run_id IN (SELECT ca_mechanical_run_id FROM ca_mechanical_statement WHERE ca_mechanical_statement_id IN (SELECT ca_mechanical_statement_id from ca_mechanical_statement_track WHERE track_id = " . $dbo->DBQuote($trackID) . ") )";

    my $sth = $dbo->DoCmd($sql);
    my ($count) = $sth->fetchrow_array();

    return $count;
}

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

    my $productID = join ',', grep {defined $_} @productID;
    my $dbo = Common::RSApp::GetClientDB();

    my $sql = "SELECT count(*) from " . kTable . " WHERE " . $openStatusTest;

    $sql .=
" AND ca_mechanical_run_id IN (SELECT ca_mechanical_run_id FROM ca_mechanical_statement WHERE ca_mechanical_statement_id IN (SELECT ca_mechanical_statement_id from ca_mechanical_statement_item WHERE product_id IN(" . $dbo->DBQuote($productID) . ") ) )";

    my $sth = $dbo->DoCmd($sql);
    my ($count) = $sth->fetchrow_array();

    return $count;
}

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

    my $sql = "SELECT count(*) from " . kTable . " WHERE " . $openStatusTest;

    $sql .=
" AND ca_mechanical_run_id IN (SELECT ca_mechanical_run_id FROM ca_mechanical_statement WHERE ca_mechanical_statement_id IN (SELECT ca_mechanical_statement_id from ca_mechanical_statement_item WHERE ca_track_license_id IN (SELECT ca_track_license_id FROM ca_track_license WHERE controlled_composition_id = " . $dbo->DBQuote($ccID) . " )) )";

    my $sth = $dbo->DoCmd($sql);
    my ($count) = $sth->fetchrow_array();

    return $count;
}

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

    my $sql =
        "SELECT max(end_time) as end_time FROM "
      . kTable
      . " WHERE status IN ("
      . RPS::RoyaltyRun::Status::kCommitted . ","
      . RPS::RoyaltyRun::Status::kWaitingToCommit . ")"
      . " AND payor_id = " . $class->quote($payorID);

    my $collection = $class->SUPER::GetAllLegacy($sql);

    my $row = $collection->next();

    return $row ? ( $row->{end_time} ) : ();
}

sub GetPayorIDs {
    my ( $class, %args ) = @_;

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

    my $sql = "SELECT distinct payor_id FROM " . kTable;

    my $sth = $dbo->DoCmd($sql);

    my @payeeIDs;
    while ( my $hr = $sth->fetchrow_hashref() ) {
        push( @payeeIDs, $hr->{payor_id} );
    }

    return \@payeeIDs;
}

sub GetAllExtended {
    my ( $class, %args ) = @_;

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

    my $implementDateSql = q/SELECT UNIX_TIMESTAMP(finished) AS TIMESTAMP FROM db_changelog WHERE VERSION REGEXP 'rsd-5122$'/;
    my $sth1 = $dbo->DoCmd($implementDateSql);
    my ($timestamp) = $sth1->fetchrow_array();

    my $sql = qq/
        SELECT
            IF( UNIX_TIMESTAMP(end_time) > ?,
                CONCAT('new_', ca_mechanical_run_id),
                CONCAT('old_', ca_mechanical_run_id)
            ) AS ca_mechanical_run_id,
            label,
            status,
            payor_id,
            start_date,
            end_date
        FROM ca_mechanical_run
    /;

    my $sth2 = $dbo->DoCmdWithPlaceholders($sql, [$timestamp]);
    my $aData = $sth2->fetchall_arrayref({});

    return $aData;
}

1;