#------------------------------------------------------------
# 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'          => {},
        'album_status'      => {},
        'track_title'       => {},
        'track_artist_name' => {},
        'track_id'          => {},
        'track_order'       => {},
        'track_isrc'        => {},

    };
}

# 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.status as album_status,"
      . "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,"
      . "master.isrc as track_isrc"
      . " 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 master on (track.master_id = master.master_id)"
      . " 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;