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

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 base 'RPS::DB::Item::ReportQueries';


sub _config
{
    return 
    {
        'label_name'        => {},
        'artist_name'       => {},
        'catalog_number'    => {},
        'album_title'       => {},
        'track_title'       => {},
        'track_order'       => {},
        'track_id'          => {},
        'album_id'          => {},
        'region_name'       => {},
        'track_license_id'  => {},
        'product_type_id'   => {},
        'share'             => {},
        'publisher'         => {},
        'administrator'     => {},
        'agency'            => {},
        'publisher_id'      => {},
        'agent_id'          => {},
        'admin_id'          => {},

    };
}


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

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

    # First we'll create a temp table to stage the data.
    #
    $dbo->DoCmd('DROP TABLE IF EXISTS zzinactive');
    $dbo->DoCmd('CREATE TEMPORARY TABLE zzinactive AS'
     . ' SELECT'
     . '   c.catalog_number,'
     . '   a.album_id,'
     . '   c.title as album_title,'
     . '   a.track_order,'
     . '   a.track_id,'
     . '   a.title as track_title,'
     . '   a.artist_id,'
     . '   c.label_id,'
     . '   b.share,'
     . '   b.publisher_id,'
     . '   b.product_type_id,'
     . '   p.admin_id,'
     . '   b.track_license_id,'
     . '   b.region_id,'
     . '   p.agent_id'
     . ' FROM track a, track_license b, album c, publisher p'
     . ' WHERE a.track_id = b.track_id'
     . ' AND a.album_id = c.album_id'
     . ' AND b.publisher_id = p.publisher_id'
     . ' AND b.inactive = 1'
    );


    # Now we can query the temp table, joining extra data
    #
    my @selectFields;
    push @selectFields, "label.label_name as label_name";
    push @selectFields, "artist.name as artist_name";
    push @selectFields, "zzinactive.catalog_number as catalog_number";
    push @selectFields, "zzinactive.album_title as album_title";
    push @selectFields, "zzinactive.track_title as track_title";
    push @selectFields, "zzinactive.track_order as track_order";
    push @selectFields, "zzinactive.track_id as track_id";
    push @selectFields, "zzinactive.album_id as album_id";
    push @selectFields, "zzinactive.share as share";
    push @selectFields, "zzinactive.track_license_id as track_license_id";
    push @selectFields, "zzinactive.publisher_id as publisher_id";
    push @selectFields, "zzinactive.agent_id as agent_id";
    push @selectFields, "zzinactive.admin_id as admin_id";
    push @selectFields, "region.name as region_name";
    push @selectFields, "p.publisher_name as publisher";
    push @selectFields, "pa.publisher_name as administrator";
    push @selectFields, "pag.publisher_name as agency";
    push @selectFields, "zzinactive.product_type_id as product_type_id";
    #push @selectFields, "CASE WHEN zzinactive.product_type_id is not null THEN product_type.description ELSE 'All' END AS 'product_type'";

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

    my $fromClause = "zzinactive";

    # Note the big nasty implicit join.
    #
    my $joinClause = 'LEFT OUTER JOIN artist ON zzinactive.artist_id = artist.artist_id';
    $joinClause .= ' LEFT OUTER JOIN label ON zzinactive.label_id = label.label_id';
    #$joinClause .= ' LEFT OUTER JOIN product_type ON zzinactive.product_type_id = product_type.product_type_id';
    $joinClause .= ' LEFT OUTER JOIN publisher p ON zzinactive.publisher_id = p.publisher_id';
    $joinClause .= ' LEFT OUTER JOIN publisher pa ON zzinactive.admin_id = pa.publisher_id';
    $joinClause .= ' LEFT OUTER JOIN region ON zzinactive.region_id = region.region_id';
    $joinClause .= ' LEFT OUTER JOIN publisher pag ON zzinactive.agent_id = pag.publisher_id';

    my $orderByClause = 'label_name, album_title, track_order, region_name';

    my $sql = "SELECT $selectClause FROM $fromClause $joinClause ORDER BY $orderByClause";
    
    return $class->SUPER::GetAll($sql);
}

1;