#------------------------------------------------------------
# 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<>$runID AND payor_id=(select payor_id from ".kTable." where ca_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 ca_mechanical_run_id IN (SELECT ca_mechanical_run_id FROM ca_mechanical_statement WHERE ca_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 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 = $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 = $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 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 = $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 = $ccID )) )";

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

    return $count;
}


1;