#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::McpsStatementItem;
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 => 'mcps_statement_item';
use constant kDB    => Common::DB::Item::kClientDB();

# These 'price types' are enums defined in the table.
#
use constant kPriceTypeDealer        => 'D';
use constant kPriceTypeRetail        => 'R';
use constant kPriceTypeSalesDocument => 'L';
use constant kPriceTypeDeleted       => 'I';

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE mcps_statement_id = " . $class->quote($id)
      . " ORDER BY album_title,product_type_id,adjustment,deleted,price, IF (country_code='GB','AA',country_code)";
    return $class->GetAll($sql);
}

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

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

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

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "DELETE from " . kTable . " WHERE mcps_statement_id = ?";
    $dbo->DoCmdWithPlaceholders($sql, [$id]);
}

sub GetByRunID {
    my ( $class, $runID ) = @_;
    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE mcps_statement_id IN (SELECT mcps_statement_id FROM mcps_statement WHERE uk_mechanical_run_id = " . $class->quote($runID) . ")";
    return $class->GetAll($sql);
}

sub GetItemsWithNegativeUnitsByRunID {
    my ( $class, $runID ) = @_;
    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE mcps_statement_id IN (SELECT mcps_statement_id FROM mcps_statement WHERE uk_mechanical_run_id = ". $class->quote($runID) . ") AND final_net_units < 0";
    return $class->GetAll($sql);
}

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

    my $sql = "SELECT COUNT(*) FROM mcps_statement_item WHERE mcps_statement_id = ?";
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$id]);

    my $hashref = $sth->fetchrow_hashref;

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

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

    my $sql =
        "SELECT DISTINCT country_code FROM "
      . kTable . " "
      . "LEFT JOIN mcps_statement ON " . "("
      . kTable
      . ".mcps_statement_id = mcps_statement.mcps_statement_id)"
      . " WHERE mcps_statement.uk_mechanical_run_id = "
      . $class->quote($runID )
      . " ORDER BY country_code";

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

}

sub GetSalesByStatementAndLicense {
    my ( $class, $statementID, $mcpsLicenseID ) = @_;
    my $sql =
        "SELECT * FROM "
      . kTable . " "
      . "WHERE mcps_statement_id = " . $class->quote($statementID)
      . "AND mcps_license_id = " . $class->quote($mcpsLicenseID)
      . "AND deleted = 0 "
      . "AND adjustment = 0";

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

sub GetAdjustmentsByStatement {
    my ( $class, $statementID ) = @_;
    my $sql =
        "SELECT * FROM "
      . kTable . " "
      . "WHERE mcps_statement_id = " . $class->quote($statementID)
      . "AND deleted = 0 "
      . "AND adjustment = 1 "
      . "ORDER BY catalog_number";

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

sub GetDeletionsByStatement {
    my ( $class, $statementID ) = @_;
    my $sql =
        "SELECT * FROM "
      . kTable . " "
      . "WHERE mcps_statement_id = " . $class->quote($statementID)
      . "AND deleted = 1 "
      . "AND adjustment = 0 "
      . "ORDER BY catalog_number";

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

sub GetDeletionAdjustmentsByStatement {
    my ( $class, $statementID ) = @_;
    my $sql =
        "SELECT * FROM "
      . kTable . " "
      . "WHERE mcps_statement_id = " . $class->quote($statementID)
      . "AND deleted = 1 "
      . "AND adjustment = 1 "
      . "ORDER BY catalog_number";

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

sub GetRetentionsByLicenseAndRun {
    my ( $class, $mcpsLicenseID, $runID, $tvAdvertised ) = @_;

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

    my $retentions = "retentions";

    if ( $tvAdvertised == 1 ) {
        $retentions = "tv_retentions";
    }

    my $sql =
        "SELECT SUM($retentions) FROM "
      . kTable
      . " WHERE mcps_license_id = ?"
      . " AND mcps_statement_id IN (SELECT mcps_statement_id FROM mcps_statement WHERE uk_mechanical_run_id = ? )";

    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$mcpsLicenseID, $runID]);
    my ($sum) = $sth->fetchrow_array();
    $sum += 0;

    return $sum;
}

1;