#------------------------------------------------------------
# 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 RPS::DB::Item::ArtistPayee;

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 = " . $class->quote( $args{runID} );
    }
    if ( $args{payorID} ) {
        push @where, "s.payor_id = " . $class->quote( $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 HasActivity {
    my ( $class, $statementID ) = @_;
    assert($statementID);

    # Returns 1 if statement has activity or a previous balance.

    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_statement_id = "
      . $dbo->DBQuote($statementID) . " 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 )";

    my $sth = $dbo->DoCmd($sql);
    my $hr  = $sth->fetchrow_hashref();

    if ($hr) {
        return 1;
    }

    return;
}

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 = " . $dbo->DBQuote($payorID);
    }

    $sql .= " GROUP BY s.artist_royalty_statement_id";

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

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

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

    $runID = $dbo->DBQuote($runID);

    my $sql = qq/
        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)
        LEFT JOIN artist_payee                       AS ap ON ap.artist_payee_id = s.payee_id
        LEFT JOIN album                              AS a ON artist_royalty_album.album_id = a.album_id
        WHERE s.artist_royalty_run_id = $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
            )
        GROUP BY s.artist_royalty_statement_id
        ORDER BY ap.name, ap.client_account_id, a.title, a.catalog_number
    /;

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

sub GetManualByArtistRoyaltyRunID {
    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 ) AND "
      . "s.distribution = "
      . RPS::DB::Item::ArtistPayee::kStatementDistributionManual;

    if ($payorID) {
        $sql .= " AND payor_id = " . $dbo->DBQuote($payorID);
    }

    $sql .= " GROUP BY s.artist_royalty_statement_id";

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

sub GetOnlineByArtistRoyaltyRunID {
    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 ) AND "
      . "s.distribution = "
      . RPS::DB::Item::ArtistPayee::kStatementDistributionOnline;

    if ($payorID) {
        $sql .= " AND payor_id = " . $dbo->DBQuote($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 = " . $dbo->DBQuote($payorID);
    }

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

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

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

    my $runID = $args{runID};
    assert($runID);

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

    if ( $args{payorID} ) {
        $sql .= " AND payor_id = " . $dbo->DBQuote( $args{payorID} );
    }

    if ( $args{filterBy} ) {
        my $filter = $args{filterBy};
        if ( kFilterByTotalLessThanZero == $filter ) {
            $sql .= " AND (balance <= 0 && amount_due <= 0)";
        } elsif ( kFilterByTotalGreaterThanZero == $filter ) {
            $sql .= " AND  (balance > 0 || amount_due > 0)";
        } elsif ( kFilterByTotalGreaterThatMinPayment == $filter ) {
            $sql .= " AND (balance >= min_payment || amount_due >= min_payment)";
        } elsif ( kFilterByOnHold == $filter ) {
            $sql .= " AND on_hold = 1";
        }
    }

    $sql .= " AND date_sent IS NOT NULL";

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

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

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

    my $runID = $args{runID};
    assert($runID);

    my $sql = "SELECT COUNT(*) as count FROM " . kTable . " LEFT JOIN artist_payee ON (payee_id = artist_payee_id)";
    $sql .= " WHERE artist_royalty_run_id = " . $dbo->DBQuote($runID);

    if ( $args{payorID} ) {
        $sql .= " AND payor_id=" . $dbo->DBQuote( $args{payorID} );
    }

    my $filter = $args{filterBy};
    if ( $filter ) {
        if ( kFilterByTotalLessThanZero == $filter ) {
            $sql .= " AND (balance <= 0 && amount_due <= 0)";
        } elsif ( kFilterByTotalGreaterThanZero == $filter ) {
            $sql .= " AND  (balance > 0 || amount_due > 0)";
        } elsif ( kFilterByTotalGreaterThatMinPayment == $filter ) {
            $sql .= " AND (balance >= min_payment || amount_due >= min_payment)";
        } elsif ( kFilterByOnHold == $filter ) {
            $sql .= " AND on_hold = 1";
        }
    }

    if ( kFilterByOnHold != $filter ) {
        $sql .= " AND on_hold != 1";
    }
    $sql .= " AND artist_payee.distribution = 1";

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

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

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

    my $runID = $args{runID};
    assert($runID);

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

    if ( $args{payorID} ) {
        $sql .= " AND payor_id = " . $dbo->DBQuote( $args{payorID} );
    }

    if ( my $filter = $args{filterBy} ) {
        if ( kFilterByTotalLessThanZero == $filter ) {
            $sql .= " AND (balance <= 0 && amount_due <= 0)";
        } elsif ( kFilterByTotalGreaterThanZero == $filter ) {
            $sql .= " AND  (balance > 0 || amount_due > 0)";
        } elsif ( kFilterByTotalGreaterThatMinPayment == $filter ) {
            $sql .= " AND (balance >= min_payment || amount_due >= min_payment)";
        } elsif ( kFilterByOnHold == $filter ) {
            $sql .= " AND on_hold = 1";
        }
    }

    $sql .= " AND on_hold = 1";

    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 = " . $class->quote($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 = ?";
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$runID]);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        push @payorIDs, $hr->{payor_id};
    }

    return @payorIDs;
}

1;