#------------------------------------------------------------
# Copyright (C) 2007 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::ReportQueries::ArtistReservePipeline;

use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::ReportQueries;

use RPS::DB::Item::ArtistContractTermReserve;
use RPS::DB::Item::ArtistContractTermReserveRun;
use RPS::DB::Item::NewArtistContractTerm;
use RPS::DB::Item::NewArtistContract;
use RPS::DB::Item::ReserveLiquidation;
use RPS::DB::Item::Product;
use RPS::DB::Item::Track;
use RPS::DB::Item::Album;
use RPS::DB::Item::AlbumContract;
use RPS::DB::Item::TrackContract;

use base 'RPS::DB::Item::ReportQueries';

sub _config {
    return {
        'region_name'       => {},
        'region_id'         => {},
        'artist_payee_name' => {},
        'artist_payee_id'   => {},
        'client_payee_id'   => {},
        'client_account_id' => {},
        'label_name'        => {},
        'catalog_number'    => {},
        'album_id'          => {},
        'album_title'       => {},
        'total_revenue'     => {},
        'total_units'       => {},
        'revenue_0'         => {},
        'units_0'           => {},
        'revenue_1'         => {},
        'units_1'           => {},
        'revenue_2'         => {},
        'units_2'           => {},
        'revenue_3'         => {},
        'units_3'           => {},
        'revenue_4'         => {},
        'units_4'           => {},
        'revenue_5'         => {},
        'units_5'           => {},
        'revenue_6'         => {},
        'units_6'           => {},
        'revenue_7'         => {},
        'units_7'           => {},
        'revenue_8'         => {},
        'units_8'           => {},
        'revenue_9'         => {},
        'units_9'           => {},
    };
}

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

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmdWithPlaceholders("SELECT COUNT(*) from artist_reserve_pipeline where artist_royalty_run_id = ?", [$runID]);
    my $hr  = $sth->fetchrow_hashref();
    if ( $hr->{'COUNT(*)'} > 0 ) {
        return 1;
    }
    return 0;
}

sub GetByArtistRoyaltyRunID {
    my ( $class, $runID, $regionID ) = @_;

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

    my $regionFilter;

    if ( defined $regionID ) {
        $regionFilter = " AND region.region_id = " . $dbo->DBQuote($regionID);
    }

    my @selectFields;
    push @selectFields, "region.name as region_name";
    push @selectFields, "region.region_id";
    push @selectFields, "artist_payee.name as artist_payee_name";
    push @selectFields, "artist_payee.artist_payee_id";
    push @selectFields, "artist_payee.client_payee_id";
    push @selectFields, "artist_payee.client_account_id";
    push @selectFields, "label.label_name";
    push @selectFields, "album.catalog_number";
    push @selectFields, "album.album_id";
    push @selectFields, "album.title as album_title";
    push @selectFields, "artist_reserve_pipeline.total_revenue";
    push @selectFields, "artist_reserve_pipeline.total_units";
    push @selectFields, "artist_reserve_pipeline.revenue_0";
    push @selectFields, "artist_reserve_pipeline.units_0";
    push @selectFields, "artist_reserve_pipeline.revenue_1";
    push @selectFields, "artist_reserve_pipeline.units_1";
    push @selectFields, "artist_reserve_pipeline.revenue_2";
    push @selectFields, "artist_reserve_pipeline.units_2";
    push @selectFields, "artist_reserve_pipeline.revenue_3";
    push @selectFields, "artist_reserve_pipeline.units_3";
    push @selectFields, "artist_reserve_pipeline.revenue_4";
    push @selectFields, "artist_reserve_pipeline.units_4";
    push @selectFields, "artist_reserve_pipeline.revenue_5";
    push @selectFields, "artist_reserve_pipeline.units_5";
    push @selectFields, "artist_reserve_pipeline.revenue_6";
    push @selectFields, "artist_reserve_pipeline.units_6";
    push @selectFields, "artist_reserve_pipeline.revenue_7";
    push @selectFields, "artist_reserve_pipeline.units_7";
    push @selectFields, "artist_reserve_pipeline.revenue_8";
    push @selectFields, "artist_reserve_pipeline.units_8";
    push @selectFields, "artist_reserve_pipeline.revenue_9";
    push @selectFields, "artist_reserve_pipeline.units_9";

    my $selectClause = join( ',', @selectFields );

    my $sql =
        "SELECT $selectClause FROM region,artist_payee,label,album,artist_reserve_pipeline WHERE"
      . " artist_reserve_pipeline.artist_royalty_run_id = " . $dbo->DBQuote($runID)
      . " AND album.album_id = artist_reserve_pipeline.album_id"
      . " AND region.region_id = artist_reserve_pipeline.region_id"
      . " AND artist_payee.artist_payee_id = artist_reserve_pipeline.artist_payee_id"
      . " AND label.label_id = album.label_id"
      . $regionFilter

      #. " ORDER BY region_name,artist_payee_name,label_name,album_title";
      . " ORDER BY album_title,artist_payee_name,region_name,label_name";

    # Return the table as a collection
    #
    return $class->SUPER::GetAll($sql);
}

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

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

    $dbo->DoCmdWithPlaceholders("DELETE FROM artist_reserve_pipeline WHERE artist_royalty_run_id = ?", [$runID]);
}

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

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

    my @selectFields;
    push @selectFields, "region.name as region_name";
    push @selectFields, "region.region_id";

    my $selectClause = join( ',', @selectFields );

    my $sql =
        "SELECT DISTINCT $selectClause FROM region,artist_reserve_pipeline WHERE"
      . " artist_reserve_pipeline.artist_royalty_run_id = ". $dbo->DBQuote($runID)
      . " AND region.region_id = artist_reserve_pipeline.region_id"
      . " ORDER BY region_name";

    # Return the table as a collection
    #
    return $class->SUPER::GetAll($sql);
}

1;