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

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'         => {},
        'label_id'           => {},
        'contract_title'     => {},
        'label_contract_id'  => {},
        'client_contract_id' => {},
        'payor_name'         => {},
        'payee_name'         => {},
        'payee_id'           => {},
        'distribution_fee'   => {},        
    };
}


# This will return a list of labels that are attached to multiple label contracts
#
sub GetLabelsWithMultipleContracts
{
    my ($class) = @_;
    
    my $sql = "SELECT label.label_id AS label_id, label.label_name AS label_name, label_contract.title AS contract_title, label_contract.label_contract_id AS label_contract_id, label_contract.client_contract_id AS client_contract_id, payor.name AS payor_name, label_payee.name AS payee_name, label_payee.label_payee_id AS payee_id, label_contract_group_term.distribution_fee AS distribution_fee FROM label RIGHT JOIN (SELECT label_id, COUNT(*) c FROM label_contract_group_term_label GROUP BY label_id HAVING c > 1) subquery ON (label.label_id = subquery.label_id) LEFT JOIN label_contract_group_term_label ON (label.label_id = label_contract_group_term_label.label_id) LEFT JOIN label_contract_group_term ON (label_contract_group_term.label_contract_group_term_id = label_contract_group_term_label.label_contract_group_term_id) LEFT JOIN label_contract ON (label_contract_group_term.label_contract_id = label_contract.label_contract_id) LEFT JOIN label_payee ON (label_contract.label_payee_id = label_payee.label_payee_id) LEFT JOIN payor ON (label_contract.payor_id = payor.payor_id)  ORDER BY label_name, contract_title";

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



1;