#------------------------------------------------------------
# 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=$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=$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=$saleID"
     . " AND statement_item_id IN "
     . " ("
     . "  SELECT ca_mechanical_statement_item_id FROM ca_mechanical_statement_item WHERE ca_track_license_id=$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=$saleID"
     . " AND statement_item_id IN "
     . " ("
     . "  SELECT mechanical_statement_item_id FROM mechanical_statement_item WHERE track_license_id=$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=$runID and publisher_id=$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=$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=$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=$saleID AND run_id=$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=$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=$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=$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=$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=$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=$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=$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=$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=$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=$statementID)";
    return $class->GetAll($sql);

}
1;