#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::SaleRunMap;
use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::RSApp;
use Common::Assert;
use Common::Log;

use base 'Common::DB::Item';

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

use constant kStatusPaid                                => 'paid';
use constant kStatusNoStatRate                          => 'nosr';
use constant kStatusNoLicense                           => 'notl';
use constant kStatusBadProductID                        => 'prod';
use constant kStatusInactiveAlbum                       => 'ialb';
use constant kStatusNoProductTracks                     => 'ptrk';
use constant kStatusClientDoesntPayOvershareMechanicals => 'over';
use constant kStatusSkipped                             => 'skip';
use constant kStatusInactiveProduct                     => 'iprd';
use constant kStatusNoLicTrackOrProductID               => 'ltpi';
use constant kStatusNoMatchingIncomeSourceID            => 'incs';
use constant kStatusNoMatchingContracts                 => 'noct';
use constant kStatusNoProductPrice                      => 'ptpr';
use constant kStatusNoContractTerm                      => 'cttm';
use constant kStatusNoLicenseIncomeData                 => 'noli';
use constant kStatusNoDigital                           => 'dgtl';    #!!! Not sure we really need this?  Can't I just filter those out?

use constant kRunTypeMechanical    => 'MECH';
use constant kRunTypeUKMechanical  => 'UKMR';
use constant kRunTypeCAMechanical  => 'CAMR';
use constant kRunTypeArtistRoyalty => 'ARTR';
use constant kRunTypeLabelRoyalty  => 'LABR';

sub GetPaidCAMechanicalsWithTrackLicense {
    my ( $class, $trackLicenseID ) = @_;

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeCAMechanical)
      . " AND statement_item_id IN " . " ("
      . "  SELECT ca_mechanical_statement_item_id FROM ca_mechanical_statement_item WHERE ca_track_license_id = " . $dbo->DBQuote($trackLicenseID)
      . "  AND ca_mechanical_statement_id IN" . "   ("
      . "     SELECT ca_mechanical_statement_id FROM ca_mechanical_statement WHERE ca_mechanical_run_id IN"
      . "     ("
      . "       SELECT ca_mechanical_run_id FROM ca_mechanical_run WHERE status IN (2,5)"
      . "     )" . "   )" . " )";

    my $c = $class->GetAll($sql);
    return $c;
}

sub GetPaidUSMechanicalsWithTrackLicense {
    my ( $class, $trackLicenseID ) = @_;

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeMechanical)
      . " AND statement_item_id IN " . " ("
      . "  SELECT mechanical_statement_item_id FROM mechanical_statement_item WHERE track_license_id = " . $dbo->DBQuote($trackLicenseID)
      . "  AND mechanical_statement_id IN" . "   ("
      . "     SELECT mechanical_statement_id FROM mechanical_statement WHERE mechanical_run_id IN"
      . "     ("
      . "       SELECT mechanical_run_id FROM mechanical_run WHERE status IN (2,5)"
      . "     )" . "   )" . " )";

    my $c = $class->GetAll($sql);
    return $c;
}

sub HavePaidCAMechanicalsForSaleWithTrackLicense {
    my ( $class, $saleID, $trackLicenseID ) = @_;

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

    # Only look at statement items from statements that appear on _closed_ or _committed_ runs.
    # (That's what the magic status codes of 2 and 5 mean in the sub-sub-query)
    #
    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeCAMechanical)
      . " AND sale_id = " . $dbo->DBQuote($saleID)
      . " AND statement_item_id IN " . " ("
      . "  SELECT ca_mechanical_statement_item_id FROM ca_mechanical_statement_item WHERE ca_track_license_id = " . $dbo->DBQuote($trackLicenseID)
      . "  AND ca_mechanical_statement_id IN" . "   ("
      . "     SELECT ca_mechanical_statement_id FROM ca_mechanical_statement WHERE ca_mechanical_run_id IN"
      . "     ("
      . "       SELECT ca_mechanical_run_id FROM ca_mechanical_run WHERE status IN (2,5)"
      . "     )" . "   )" . " )";

    my $c = $class->GetAll($sql);
    return ( $c->size() > 0 );
}

sub HavePaidUSMechanicalsForSaleWithTrackLicense {
    my ( $class, $saleID, $trackLicenseID ) = @_;

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

    # Only look at statement items from statements that appear on _closed_ or _committed_ runs.
    # (That's what the magic status codes of 2 and 5 mean in the sub-sub-query)
    #
    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeMechanical)
      . " AND sale_id = " . $dbo->DBQuote($saleID)
      . " AND statement_item_id IN " . " ("
      . "  SELECT mechanical_statement_item_id FROM mechanical_statement_item WHERE track_license_id = " . $dbo->DBQuote($trackLicenseID)
      . "  AND mechanical_statement_id IN" . "   ("
      . "     SELECT mechanical_statement_id FROM mechanical_statement WHERE mechanical_run_id IN"
      . "     ("
      . "       SELECT mechanical_run_id FROM mechanical_run WHERE status IN (2,5)"
      . "     )" . "   )" . " )";

    my $c = $class->GetAll($sql);
    return ( $c->size() > 0 );
}

sub GetPaidUKMechanicalAP1 {
    my ($class) = @_;

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

    # !!! Status 2 == committed, 5 == closed.
    #
    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeUKMechanical)
      . " AND statement_item_id IN ( SELECT DISTINCT mcps_statement_item_id FROM mcps_statement_item WHERE mcps_statement_id IN "
      . " ( SELECT mcps_statement_id FROM mcps_statement WHERE mcps_scheme='AP1' AND uk_mechanical_run_id IN "
      . " ( SELECT uk_mechanical_run_id FROM uk_mechanical_run WHERE status IN (2, 5) ) ) )";
    return $class->GetAll($sql);
}

sub GetPaidUKMechanicalDVD1 {
    my ($class) = @_;

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

    # !!! Status 2 == committed, 5 == closed.
    #
    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeUKMechanical)
      . " AND statement_item_id IN ( SELECT DISTINCT mcps_statement_item_id FROM mcps_statement_item WHERE mcps_statement_id IN "
      . " ( SELECT mcps_statement_id FROM mcps_statement WHERE mcps_scheme='DVD1' AND uk_mechanical_run_id IN "
      . " ( SELECT uk_mechanical_run_id FROM uk_mechanical_run WHERE status IN (2, 5) ) ) )";
    return $class->GetAll($sql);
}

sub GetPaidUKMechanicalAVP {
    my ($class) = @_;

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

    # !!! Status 2 == committed, 5 == closed.
    #
    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeUKMechanical)
      . " AND statement_item_id IN ( SELECT DISTINCT mcps_statement_item_id FROM mcps_statement_item WHERE mcps_statement_id IN "
      . " ( SELECT mcps_statement_id FROM mcps_statement WHERE mcps_scheme='AVP' AND uk_mechanical_run_id IN "
      . " ( SELECT uk_mechanical_run_id FROM uk_mechanical_run WHERE status IN (2, 5) ) ) )";
    return $class->GetAll($sql);
}

# JPK - This may seem like a strange method.
# Here's the point:
#  - The sale_publisher_map is a 'temporary' table, built by the RunController to
#    map sales to the right publisher.
#  - This method will use that to filter out erroneous sales.
#
sub GetPaidMechanicalByPublisherIDRunID {
    my ( $class, $publisherID, $runID ) = @_;
    assert($publisherID);
    assert($runID);

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeMechanical)
      . " AND sale_id IN"
      . " (SELECT sale_id FROM sale_publisher_map WHERE run_type="
      . $dbo->DBQuote(kRunTypeMechanical)
      . " AND run_id = "
      . $dbo->DBQuote($runID)
      . " AND publisher_id = "
      . $dbo->DBQuote($publisherID)
      . ")";
    return $class->GetAll($sql);
}

sub GetPaidMechanicalBySaleID {
    my ( $class, $saleID ) = @_;
    assert($saleID);

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status = "
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type = "
      . $dbo->DBQuote(kRunTypeMechanical)
      . " AND sale_id = "
      . $dbo->DBQuote($saleID);
    return $class->GetAll($sql);
}

sub GetPaidArtistRoyaltyBySaleID {
    my ( $class, $saleID ) = @_;
    assert($saleID);

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeArtistRoyalty)
      . " AND sale_id = "
      . $dbo->DBQuote($saleID);
    $sql .= " AND run_id in (select artist_royalty_run_id from artist_royalty_run where status in (2,5))";

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

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

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeArtistRoyalty)
      . " AND sale_id = "
      . $dbo->DBQuote($saleID)
      . " AND run_id = "
      . $dbo->DBQuote($runID);

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

sub GetPaidLicenseIncomeArtistRoyaltyBySaleID {
    my ( $class, $saleID ) = @_;
    assert($saleID);

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeArtistRoyalty)
      . " AND sale_id = "
      . $dbo->DBQuote($saleID);
    $sql .= " AND run_id in (select artist_royalty_run_id from artist_royalty_run where status in (2,5))";

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

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

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

    my $sql = "DELETE FROM " . kTable . " WHERE run_type=" . $dbo->DBQuote(kRunTypeCAMechanical) . " AND run_id = " . $dbo->DBQuote($runID);
    $dbo->DoCmd($sql);
}

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

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

    my $sql = "DELETE FROM " . kTable . " WHERE run_type=" . $dbo->DBQuote(kRunTypeUKMechanical) . " AND run_id = " . $dbo->DBQuote($runID);
    $dbo->DoCmd($sql);
}

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

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

    my $sql = "DELETE FROM " . kTable . " WHERE run_type=" . $dbo->DBQuote(kRunTypeMechanical) . " AND run_id = " . $dbo->DBQuote($runID);
    $dbo->DoCmd($sql);
}

sub GetByMechanicalStatementItemID {
    my ( $class, $statementItemID ) = @_;
    assert($statementItemID);

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

    my $sql =
      "SELECT * FROM " . kTable . " WHERE run_type=" . $dbo->DBQuote(kRunTypeMechanical) . " AND statement_item_id = " . $dbo->DBQuote($statementItemID);

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

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

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

    my $sql = "DELETE FROM " . kTable . " WHERE run_type=" . $dbo->DBQuote(kRunTypeArtistRoyalty) . " AND run_id = " . $dbo->DBQuote($runID);
    $dbo->DoCmd($sql);
}

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

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE run_type="
      . $dbo->DBQuote(kRunTypeArtistRoyalty)
      . " AND status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_id = "
      . $dbo->DBQuote($runID);
    return $class->GetAll($sql);
}

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

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

    my $sql = "DELETE FROM " . kTable . " WHERE run_type=" . $dbo->DBQuote(kRunTypeLabelRoyalty) . " AND run_id = " . $dbo->DBQuote($runID);
    $dbo->DoCmd($sql);
}

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

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeLabelRoyalty)
      . " AND run_id = "
      . $dbo->DBQuote($runID);
    return $class->GetAll($sql);

}

sub GetPaidByLabelRoyaltyStatementID {
    my ( $class, $statementID ) = @_;
    assert($statementID);

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE status="
      . $dbo->DBQuote(kStatusPaid)
      . " AND run_type="
      . $dbo->DBQuote(kRunTypeLabelRoyalty)
      . " AND statement_item_id IN (SELECT label_royalty_label_item_id FROM label_royalty_label_item WHERE label_royalty_statement_id = " . $dbo->DBQuote($statementID) . ")";
    return $class->GetAll($sql);

}


1;