#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::ArtistRoyaltyStatement;
use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::Assert;
use Common::DB::Item;
use Common::DB::ItemCollection;
use base 'Common::DB::Item';

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

use constant kFilterByTotalLessThanZero => 1;
use constant kFilterByTotalGreaterThanZero => 2;
use constant kFilterByTotalGreaterThatMinPayment => 3;
use constant kFilterByOnHold => 4;
use constant kFilterByNone => 5;


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

    my @group_by;
    my @join;
    
    my @from;
    push @from, "artist_royalty_statement as s";

    my @where;
    if ($args{runID})
    {
        push @where, "s.artist_royalty_run_id = ".$args{runID};
    }
    if ($args{payorID})
    {
        push @where, "s.payor_id = ".$args{payorID};
    }

    if ($args{filterBy})
    {
        my $filter = $args{filterBy};
        if (kFilterByTotalLessThanZero == $filter)
        {
            push @where, "(balance <= 0 && amount_due <= 0)";
        }
        elsif (kFilterByTotalGreaterThanZero == $filter)
        {
            push @where, "(balance > 0 || amount_due > 0)";
        }
        elsif (kFilterByTotalGreaterThatMinPayment == $filter)
        {
            push @where, "(s.balance >= s.min_payment || s.amount_due >= s.min_payment)";
        }
        elsif (kFilterByOnHold == $filter)
        {
            push @where, "s.on_hold = 1";
        }
#
# jpk - saving this little code tidbit just in case we want a 'no activity' filter someday
#
#        elsif (kFilterByNoActivity == $filter)
#        {
#            push( @join, 'LEFT JOIN artist_royalty_album USING (artist_royalty_statement_id)' );
#            push( @join, 'LEFT JOIN artist_royalty_license_income_item USING (artist_royalty_statement_id)' );
#            push( @join, 'LEFT JOIN artist_royalty_transaction USING (artist_royalty_statement_id)' );
#        
#            push( @where, '(  artist_royalty_album.artist_royalty_statement_id IS NULL AND ' .
#                         'artist_royalty_license_income_item.artist_royalty_statement_id IS NULL AND ' .
#                         'artist_royalty_transaction.artist_royalty_statement_id IS NULL AND ' .
#                         's.previous_balance = 0 )' );
#        
#            push( @group_by, 's.artist_royalty_statement_id')
#        }
        #
        # else, there are no filters...
        # This is where we 'handle' kFilterByNone
        #
    }
    else {
        push( @join, 'LEFT JOIN artist_royalty_album USING (artist_royalty_statement_id)' );
        push( @join, 'LEFT JOIN artist_royalty_license_income_item USING (artist_royalty_statement_id)' );
        push( @join, 'LEFT JOIN artist_royalty_transaction USING (artist_royalty_statement_id)' );
        
        push( @where, '(  artist_royalty_album.artist_royalty_statement_id IS NOT NULL OR ' .
                         'artist_royalty_license_income_item.artist_royalty_statement_id IS NOT NULL OR ' .
                         'artist_royalty_transaction.artist_royalty_statement_id IS NOT NULL OR ' .
                         's.previous_balance <> 0 )' );
        
        push( @group_by, 's.artist_royalty_statement_id')
    }

    my $sortString;
    if ($args{sortByArtistPayeeName})
    {
        push( @join, 'INNER JOIN artist_payee ON (s.payee_id = artist_payee.artist_payee_id)' );
        $sortString = "ORDER BY artist_payee.name";
    }

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

    my $groupByString;
    if (scalar @group_by)
    {
        $groupByString = " GROUP BY " . join(',', @group_by);
    }

    my $fromString;
    if (scalar @from)
    {
        $fromString = " FROM " . join(',', @from) . ' ' . join( ' ', @join );
    }


    my $sql = "SELECT s.* $fromString $whereString $groupByString $sortString";

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


sub GetByArtistRoyaltyRunID
{
    my ($class, $runID, $payorID) = @_;
    assert($runID);
    
    my $dbo = Common::RSApp::GetClientDB();

    my $sql = "SELECT s.* " .
              "FROM artist_royalty_statement as s " .
                   "LEFT JOIN artist_royalty_album USING (artist_royalty_statement_id) " .
                   "LEFT JOIN artist_royalty_license_income_item USING (artist_royalty_statement_id) " .
                   "LEFT JOIN artist_royalty_transaction USING (artist_royalty_statement_id) " .
              "WHERE s.artist_royalty_run_id = " . $dbo->DBQuote($runID) . " AND " .
                    "( artist_royalty_album.artist_royalty_statement_id IS NOT NULL OR " .
                      "artist_royalty_license_income_item.artist_royalty_statement_id IS NOT NULL OR " .
                      "artist_royalty_transaction.artist_royalty_statement_id IS NOT NULL OR " .
                      "s.previous_balance <> 0 )";
                      
    if ($payorID)
    {
        $sql .= " AND payor_id=$payorID";
    }

    $sql .= " GROUP BY s.artist_royalty_statement_id";
    
    return $class->GetAll($sql);
}

sub GetCountByArtistRoyaltyRunID
{
    my ($class, $runID, $payorID) = @_;
    assert($runID);
    
    my $dbo = Common::RSApp::GetClientDB();

    # Not going to use the fancy-shmancy join logic from the 'full' query.  That doesn't seem necessary.
    #

    my $sql = "SELECT COUNT(*) as count FROM artist_royalty_statement WHERE artist_royalty_run_id=".$dbo->DBQuote($runID);

    if ($payorID)
    {
        $sql .= " AND payor_id=$payorID";
    }

    my $sth = $dbo->DoCmd($sql);
    my $hr = $sth->fetchrow_hashref();
    my $count = $hr->{count} || 0;
    return $count;
}


sub GetByArtistRoyaltyRunIDForDeletion
{
    my ($class, $runID) = @_;
    assert($runID);

    my $sql = "SELECT * FROM " . kTable
            . " WHERE artist_royalty_run_id=$runID";

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


sub GetDistinctPayorsByRunID
{
    my ($class, $runID) = @_;
    assert($runID);

    my @payorIDs;

    my $sql = "SELECT distinct payor_id from " . kTable . " where artist_royalty_run_id=$runID";
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    while (my $hr = $sth->fetchrow_hashref())
    {
        push @payorIDs, $hr->{payor_id};
    }

    return @payorIDs;
}




1;