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

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

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

use constant kTable => '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 mechanical_run_id<>$runID AND payor_id=(select payor_id from ".kTable." where mechanical_run_id=$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 mechanical_run_id IN (SELECT mechanical_run_id FROM mechanical_statement WHERE publisher_id = $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 mechanical_run_id IN (SELECT mechanical_run_id FROM mechanical_statement WHERE mechanical_statement_id IN (SELECT mechanical_statement_id from mechanical_statement_license WHERE region_id = $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 mechanical_run_id IN (SELECT mechanical_run_id FROM mechanical_statement WHERE mechanical_statement_id IN (SELECT mechanical_statement_id from mechanical_statement_track WHERE track_id = $trackID) )";

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

    return $count;
}

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

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

    $sql .= " AND mechanical_run_id IN (SELECT mechanical_run_id FROM mechanical_statement WHERE mechanical_statement_id IN (SELECT mechanical_statement_id from mechanical_statement_item WHERE product_id = $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 mechanical_run_id IN (SELECT mechanical_run_id FROM mechanical_statement WHERE mechanical_statement_id IN (SELECT mechanical_statement_id from mechanical_statement_item WHERE track_license_id IN (SELECT track_license_id FROM track_license WHERE controlled_composition_id = $ccID )) )";

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

    return $count;
}