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

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::LicenseReserve;
use RPS::DB::Item::TrackLicense;

use RPS::DB::Item::ReserveLiquidation;
use RPS::DB::Item::Product;
use RPS::DB::Item::Track;
use RPS::DB::Item::Album;


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


# _config: returns an empty hash with the fields that would be
# returned if we could get this data from a single database table
# 
sub _config
{
    return 
    {
        'track_id'            => {},
        'track_title'         => {},
        'album_id'            => {},
        'album_title'         => {},
        'catalog_number'      => {},
        'upc'                 => {},
        'publisher_name'      => {},
        'publisher_id'        => {},
        'client_account_id'   => {},
        'region_name'         => {},
        'region_id'           => {},

        '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 mechanical_reserve_pipeline where mechanical_run_id=$runID");
    my $hr = $sth->fetchrow_hashref();
    if ($hr->{'COUNT(*)'} > 0)
    {
        return 1;
    }
    return 0;
}

sub GetByMechanicalRunID
{
    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, "publisher.publisher_name as publisher_name";
    push @selectFields, "publisher.publisher_id";
    push @selectFields, "publisher.client_account_id";

    push @selectFields, "mechanical_reserve_pipeline.upc";

    push @selectFields, "album.catalog_number";
    push @selectFields, "album.album_id";
    push @selectFields, "album.title as album_title";

    push @selectFields, "track.track_id";
    push @selectFields, "track.title as track_title";

    push @selectFields, "mechanical_reserve_pipeline.total_revenue";
    push @selectFields, "mechanical_reserve_pipeline.total_units";
    push @selectFields, "mechanical_reserve_pipeline.revenue_0";
    push @selectFields, "mechanical_reserve_pipeline.units_0";
    push @selectFields, "mechanical_reserve_pipeline.revenue_1";
    push @selectFields, "mechanical_reserve_pipeline.units_1";
    push @selectFields, "mechanical_reserve_pipeline.revenue_2";
    push @selectFields, "mechanical_reserve_pipeline.units_2";
    push @selectFields, "mechanical_reserve_pipeline.revenue_3";
    push @selectFields, "mechanical_reserve_pipeline.units_3";
    push @selectFields, "mechanical_reserve_pipeline.revenue_4";
    push @selectFields, "mechanical_reserve_pipeline.units_4";
    push @selectFields, "mechanical_reserve_pipeline.revenue_5";
    push @selectFields, "mechanical_reserve_pipeline.units_5";
    push @selectFields, "mechanical_reserve_pipeline.revenue_6";
    push @selectFields, "mechanical_reserve_pipeline.units_6";
    push @selectFields, "mechanical_reserve_pipeline.revenue_7";
    push @selectFields, "mechanical_reserve_pipeline.units_7";
    push @selectFields, "mechanical_reserve_pipeline.revenue_8";
    push @selectFields, "mechanical_reserve_pipeline.units_8";
    push @selectFields, "mechanical_reserve_pipeline.revenue_9";
    push @selectFields, "mechanical_reserve_pipeline.units_9";    

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

    my $sql =
        "SELECT $selectClause "
      . "FROM region, publisher, track, album, mechanical_reserve_pipeline "
      . "WHERE "
      . "mechanical_reserve_pipeline.mechanical_run_id=$runID "
      . "AND album.album_id         = mechanical_reserve_pipeline.album_id "
      . "AND region.region_id       = mechanical_reserve_pipeline.region_id "
      . "AND publisher.publisher_id = mechanical_reserve_pipeline.publisher_id "
      . "AND track.track_id         = mechanical_reserve_pipeline.track_id "
      . $regionFilter
      . " ORDER BY track_title, album_title, publisher_name, region_name";


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

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

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

    $dbo->DoCmd("DELETE FROM mechanical_reserve_pipeline WHERE mechanical_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, mechanical_reserve_pipeline "
     . " WHERE mechanical_reserve_pipeline.mechanical_run_id=$runID"
     . " AND region.region_id = mechanical_reserve_pipeline.region_id"
     . " ORDER BY region_name";

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


1;