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

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

# returns true if this track_license appears in _any_ mechanical statement item
#
sub CountByTrackLicenseID {
    my ( $class, $trackLicenseID ) = @_;

    my $sql = "SELECT COUNT(*) from " . kTable . " where ca_track_license_id=$trackLicenseID";
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);

    my $hashref = $sth->fetchrow_hashref;
    return $hashref->{"COUNT(*)"};
}

sub GetByStatementAndTrack {
    my ( $class, $stmtID, $trackID ) = @_;
    assert($stmtID);
    assert($trackID);

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE ca_mechanical_statement_id=$stmtID AND ca_track_license_id in (select ca_track_license_id from ca_track_license where track_id=$trackID)";

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

sub GetByStatementAndTrackLicense {
    my ( $class, $stmtID, $trackLicenseID ) = @_;
    assert($stmtID);
    assert($trackLicenseID);

    my $sql = "SELECT * FROM " . kTable . " WHERE ca_mechanical_statement_id=$stmtID AND ca_track_license_id=$trackLicenseID";
    return $class->GetAll($sql);
}

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

    my $sql = "SELECT * FROM " . kTable . " WHERE ca_mechanical_statement_id=$id";
    return $class->GetAll($sql);
}

sub GetByMechanicalStatementIDPublisherID {
    my ( $class, $stmtID, $pubID ) = @_;

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE ca_mechanical_statement_id=$stmtID"
      . " AND ca_track_license_id in (select ca_track_license_id from ca_track_license where ca_publisher_id=$pubID)";
    return $class->GetAll($sql);

}

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

    my $sql = "DELETE from " . kTable . " WHERE ca_mechanical_statement_id=$id";
    my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmd($sql);
}

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

    my $sql = "SELECT COUNT(*) FROM " . kTable . " WHERE ca_mechanical_statement_id=$id";
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);

    my $hashref = $sth->fetchrow_hashref;

    return $hashref->{'COUNT(*)'};
}

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

    my $sql = "SELECT COUNT(*) FROM " . kTable . " WHERE ca_mechanical_statement_id=$id AND amount_paid>0";
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);

    my $hashref = $sth->fetchrow_hashref;

    return $hashref->{'COUNT(*)'};
}

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

    my $sql = "SELECT * FROM " . kTable . " WHERE product_id=$productID";
    return $class->GetAll($sql);
}

1;