#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::NewArtistContract;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::Util qw(escape_mysql_regexp);
use Common::DB::ItemCollection;
use Common::DB::Item;
use Common::Assert;
use base 'Common::DB::Item';

use RPS::DB::Item::ReserveLiquidation;

use constant kTable => 'new_artist_contract';
use constant kDB => Common::DB::Item::kClientDB();



sub Get
{
    my ($class, %args) = @_;

    my @where;

    # Are we paging alphanumerically?
    #
    my $alpha = $args{alpha};
    if (defined $alpha)
    {
        if ('0' eq uc($alpha))
        {
            push @where, "title < 'A'";
        }
        elsif ('Z' eq $alpha)
        {
            push @where, "title >= 'Z'";
        }
        else
        {
            push @where, "title >= '".uc($alpha)."'";
            push @where, "title < '".chr( ord(uc($alpha)) + 1)."'";
        }
    }


    if ($args{payor_id})
    {
        push @where, "payor_id = ".$args{payor_id};
    }

    my $whereClause;
    if (scalar @where)
    {
        $whereClause = "WHERE " . join(' AND ', @where);
    }


	my $sql = "SELECT * FROM ".kTable." $whereClause ORDER BY title";

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



sub IsAlbumOnContract
{
    my ($class, $albumID, $contractID) = @_;

    my $sql = "SELECT * FROM album_contract WHERE artist_contract_id=$contractID AND album_id=$albumID";
    my $c = $class->GetAll($sql);
    return 1 if ($c->size() > 0);

    $sql = "SELECT * FROM track_contract WHERE artist_contract_id=$contractID AND track_id IN (SELECT track_id FROM track WHERE album_id=$albumID)";
    $c = $class->GetAll($sql);
    return ($c->size() > 0);
}



sub IsTrackOnContract
{
    my ($class, $trackID, $contractID) = @_;

    my $sql = "SELECT * FROM track_contract WHERE artist_contract_id=$contractID AND track_id=$trackID";
    my $c = $class->GetAll($sql);
    return 1 if ($c->size() > 0);

    $sql = "SELECT * FROM album_contract WHERE artist_contract_id=$contractID AND album_id IN (SELECT album_id FROM track WHERE track_id=$trackID)";
    $c = $class->GetAll($sql);
    return ($c->size() > 0);
}




sub GetByPayee
{
	my ($class, $payeeID) = @_;
    assert($payeeID);

	my $sql = "SELECT * FROM ".kTable." WHERE artist_payee_id=$payeeID ORDER BY title";
    return $class->GetAll($sql);
}


sub GetByPayor
{
	my ($class, $payorID) = @_;
    assert($payorID);

	my $sql = "SELECT * FROM ".kTable." WHERE payor_id=$payorID ORDER BY title";
    return $class->GetAll($sql);
}

sub GetCountByPayor
{
	my ($class, $payorID) = @_;

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

	my $sql = "SELECT COUNT(*) as count FROM ".kTable." WHERE payor_id=$payorID";
    my $sth = $dbo->DoCmd($sql);
    my $hr = $sth->fetchrow_hashref();
    return $hr->{count};
}



sub SearchTitleAndClientContractID
{
    my ($class, %args) = @_;

    my $searchTerm = $args{searchTerm};
    my $limit = $args{limit};
    my $trackID = $args{trackID};
    my $albumID = $args{albumID};

    my $where;

    if ($searchTerm)
    {
        $where = "(" . $class->SearchSyntax(field => 'title', value => $searchTerm)
        . " OR ". $class->SearchSyntax(field => "client_contract_id", value => $searchTerm) . ")";

        if ($albumID && $trackID)
		    {
		        $where .= " AND (artist_contract_id IN (SELECT artist_contract_id FROM album_contract WHERE album_id = " . $albumID . ") OR artist_contract_id IN (SELECT artist_contract_id FROM track_contract WHERE track_id = " . $trackID . "))";
		    }
        elsif ($albumID)
		    {
		        $where .= " AND artist_contract_id IN (SELECT artist_contract_id FROM album_contract WHERE album_id = " . $albumID . ")";
		    }
        elsif ($trackID)
		    {
		        $where .= " AND artist_contract_id IN (SELECT artist_contract_id FROM track_contract WHERE track_id = " . $trackID . ")";
		    }


    }

    my $sql = 'SELECT * FROM ' . kTable;
    if ($where)
    {
        $sql .= " WHERE $where";
    }

    $sql .= ' ORDER BY title';

    if ($limit)
    {
        $sql .= " LIMIT $limit";
    }


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


# !!! Odd that this is not referenced anyplace - might be something funny going on.
#
sub Search
{
	my ($class, $searchTerm) = @_;
    assert($searchTerm, "no valid param passed to artist_contract search");

	my $where = $class->SearchSyntax(field => "artist_payee.name", value => $searchTerm)
			. " OR " . $class->SearchSyntax(field => "ac.client_contract_id", value => $searchTerm)
			. " OR " . $class->SearchSyntax(field => "ac.title", value => $searchTerm);

	my $order = "ac.title, artist_payee.name";

	my $sql = "SELECT ac.* FROM ".kTable." ac"
		. " LEFT JOIN artist_payee ON artist_payee.artist_payee_id=ac.artist_payee_id"
		. " WHERE $where"
		. " ORDER BY $order";

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


sub GetByPayorAlbumTrack
{
    my ($class, $payorID, $albumID, $trackID) = @_;
    assert($payorID);
    assert($albumID);

    # !!! Track is optional;
    #
    my $sql = "SELECT * FROM ". kTable . " WHERE payor_id=$payorID"
     . " AND ("
     . "(artist_contract_id IN (select artist_contract_id from album_contract where album_id=$albumID))";

    # If we have a track id, then we only want to match track contracts for that track.
    # Otherwise, any track contract for that album would match.
    #
    if ($trackID)
    {
        $sql .= " OR (artist_contract_id IN (select artist_contract_id from track_contract where track_id=$trackID))";
    }
    else
    {
        $sql .= " OR (artist_contract_id IN (select artist_contract_id from track_contract where track_id IN (select track_id from track where album_id=$albumID)))";
    }
    $sql .= ")";

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


sub GetByAlbumTrack
{
    my ($class, $albumID, $trackID) = @_;
    assert($albumID);

    # !!! Track is optional;
    #
    my $sql = "SELECT * FROM ". kTable
     . " WHERE ("
     . "(artist_contract_id IN (select artist_contract_id from album_contract where album_id=$albumID))";

    # If we have a track id, then we only want to match track contracts for that track.
    # Otherwise, any track contract for that album would match.
    #
    if ($trackID)
    {
        $sql .= " OR (artist_contract_id IN (select artist_contract_id from track_contract where track_id=$trackID))";
    }
    else
    {
        $sql .= " OR (artist_contract_id IN (select artist_contract_id from track_contract where track_id IN (select track_id from track where album_id=$albumID)))";
    }
    $sql .= ")";

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

sub GetAllWithoutReserveLiquidation {
    my $class = shift;

	my $types = join( ',', ( RPS::DB::Item::ReserveLiquidation::kTypeContractUnitBased,
							 RPS::DB::Item::ReserveLiquidation::kTypeContractNetBased,
							 RPS::DB::Item::ReserveLiquidation::kTypeArtistContract ) );
	my $table = kTable;

	my $sql = "SELECT *
               FROM $table
			      LEFT JOIN track_contract USING ( artist_contract_id )
				  LEFT JOIN album_contract USING ( artist_contract_id )
                  LEFT JOIN reserve_liquidation ON ( new_artist_contract.artist_contract_id = reserve_liquidation.id AND type IN ($types) )
               WHERE reserve_rate > 0 AND reserve_liquidation.id IS NULL AND ( track_contract.status = 1 OR album_contract.status = 1 )";

    Common::Log::Debug( "QUERY: $sql" );
	return $class->SUPER::GetAll($sql);
}



1;