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

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'        => {},
        'catalog_number'    => {},
        'album_title'       => {},
        'album_artist_name'       => {},
        'album_id'          => {},
        'track_title'       => {},
        'track_artist_name' => {},
        'track_id'          => {},
        'track_order'       => {},

    };
}


# This will return a list of albums that are not fully covered by contracts.
# This means that it does not have an album level contract, and there is at least
# one track that is not covered by a contract.
#
sub GetAlbumsWithoutContracts
{
    my ($class) = @_;
    

    my $sql = "select album.album_id as album_id,"
     . "album.catalog_number as catalog_number,"
     . "album.title as album_title,"
     . "album_artist.name as album_artist_name,"
     . "label.label_name as label_name,"
     . "track.track_id as track_id,"
     . "track.title as track_title,"
     . "track_artist.name as track_artist_name,"
     . "track.track_order as track_order"
     . " from album"
     . " left join label on (label.label_id=album.label_id)"
     . " left join artist as album_artist on (album_artist.artist_id = album.artist_id)"
     . " left join track on ( track.album_id = album.album_id and track_id NOT IN (select track_id from track_contract) AND track.album_id in (select album_id from track where track_id in (select track_id from track_contract)))"
     . " left join artist as track_artist on (track.artist_id = track_artist.artist_id)"
     # JPK - First part of this where clause limits the query to albums with no album_contracts.
     # The second part limits the query to albums that have tracks which don't have track_contracts.
     # So, an album without an album_contract, but with a track_contract on every track will not be returned.
     #
     . " where (album.album_id NOT IN (select album_id from album_contract)) AND album.album_id IN (select album_id from track where track_id NOT IN (select track_id from track_contract))"
     . " order by label_name,album_title,track_order";

    return $class->SUPER::GetAll($sql);
}



1;