#------------------------------------------------------------
# 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->DoCmd("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 = ".$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=$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->DoCmd("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=$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;