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

use strict;
use warnings;

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

use Common::DB::Item;
use base 'Common::DB::Item';

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

use constant kCycleQuarterly  => 1;
use constant kCycleSemiAnnual => 2;
use constant kCycleAnnual     => 3;

use constant kStatusInactive => 0;
use constant kStatusActive   => 1;
use constant kStatusOnHold   => 2;

use constant kStatementDistributionManual => 0;
use constant kStatementDistributionOnline => 1;


# Encrypted fields are sized to hold the encrypted/encoded data.  Encrypted data will
# be larger in size due the encoding; to avoid exceeding the database field size,
# only TEXT fields are supported.  Any other field types will be ignored.
#
use constant kMaxTaxID => 30;

sub _getEncrypted {  # define which fields (if any) are encrypted
    return (
        'comments', 'tax_id'
    );
}

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, "name < 'A'";
        } elsif ( 'Z' eq $alpha ) {
            push @where, "name >= 'Z'";
        } else {
            push @where, "name >= '" . uc($alpha) . "'";
            push @where, "name < '" . chr( ord( uc($alpha) ) + 1 ) . "'";
        }
    }

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

    my $sql = "SELECT * FROM " . kTable . " $whereClause ORDER BY name";
    return $class->GetAll($sql);
}

sub Search {
    my ( $class, $searchTerm ) = @_;

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE "
      . $class->SearchSyntax( field => "name",              value => $searchTerm ) . " OR "
      . $class->SearchSyntax( field => "client_payee_id",   value => $searchTerm ) . " OR "
      . $class->SearchSyntax( field => "client_account_id", value => $searchTerm )
      . " ORDER BY name";

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

sub PayeeSearch {

    # This is used by the autocomplete in the report section and has some extra logic built in.
    my ( $class, %args ) = @_;

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

    my $searchTerm = $args{searchTerm};
    my $limit      = $args{limit} && $args{limit} =~ /^\d+$/ ? $args{limit} : 0;
    my $runID      = $dbo->DBQuote( $args{runID} );

    my $searchName = $class->SearchSyntax( field => "name", value => $searchTerm );
    my $searchClientAccountID = $class->SearchSyntax( field => "client_account_id", value => $searchTerm );

    my $sql = qq/
        SELECT ap.*
        FROM artist_royalty_statement AS ars
        LEFT JOIN artist_payee AS ap ON ap.artist_payee_id = ars.payee_id
        WHERE 1
            AND ars.artist_royalty_run_id = $runID
            AND ap.status IN (0, 1, 2)
            AND ( $searchName OR $searchClientAccountID )
        ORDER BY NAME
        LIMIT $limit
    /;

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

sub Match {
    my ( $class, $lookupValue ) = @_;

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE client_account_id="
      . $dbo->DBQuote($lookupValue)
      . " OR client_payee_id="
      . $dbo->DBQuote($lookupValue)
      . " OR name="
      . $dbo->DBQuote($lookupValue);

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

sub GetInactiveIDsMap {
    my ($class) = @_;

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

    my %map;

    my $sql = "SELECT artist_payee_id FROM " . kTable . " WHERE status=" . kStatusInactive;
    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        $map{ $hr->{artist_payee_id} } = 1;
    }

    return \%map;
}

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE artist_payee_id IN (SELECT payee_id"
      . " FROM artist_royalty_statement"
      . " WHERE artist_royalty_run_id = " . $class->quote($runID) . " )"
      . " ORDER BY name";

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

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

    # We need all of the payees who are have contracts and/or finance accounts with the payor.
    #

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE (status=1 OR status=2)"
      . " AND (artist_payee_id IN ("
      . " select artist_payee_id"
      . " from new_artist_contract"
      . " where deleted = 0 AND payor_id = "
      . $class->quote($payorID)
      . " ) OR artist_payee_id IN (select artist_payee_id from artist_payee_account where payor_id = "
      . $class->quote($payorID)
      . " ))";

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

sub GetAllManual {
    my ($class) = @_;

    my $sql = "SELECT * FROM " . kTable . " WHERE distribution = " . kStatementDistributionManual . " ORDER BY name";

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

sub GetAllOnline {
    my ($class) = @_;

    my $sql = "SELECT * FROM " . kTable . " WHERE distribution = " . kStatementDistributionOnline . " ORDER BY name";

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

sub GetAllInvited {
    my ($class) = @_;

    my $sql =
      "SELECT * FROM " . kTable . " WHERE distribution = " . kStatementDistributionOnline . " AND date_invited IS NOT NULL ORDER BY name";

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

sub GetAllConfirmed {
    my ($class) = @_;

    my $sql =
      "SELECT * FROM " . kTable . " WHERE distribution = " . kStatementDistributionOnline . " AND date_confirmed IS NOT NULL ORDER BY name";

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

sub HasArtistContracts {
    my $class   = shift;
    my $artistPayeeID = shift;
    my $dbo = Common::RSApp::GetClientDB();

    assert($artistPayeeID);

    my $sql = "SELECT * FROM new_artist_contract WHERE deleted = 0 AND artist_payee_id = " . $dbo->DBQuote($artistPayeeID);

    Common::Log::Debug($sql);

    my $collection = $class->SUPER::GetAll($sql);
    return $collection->next() ? 1 : 0;
}

sub HasRunActivity {
    my $class   = shift;
    my $artistPayeeID = shift;
    my $dbo = Common::RSApp::GetClientDB();

    assert($artistPayeeID);

    my $sql = "SELECT 1 FROM artist_royalty_statement WHERE payee_id = " . $dbo->DBQuote($artistPayeeID);

    Common::Log::Debug($sql);

    my $collection = $class->SUPER::GetAll($sql);
    return $collection->next() ? 1 : 0;
}

sub IsNotManual {
    my $class   = shift;
    my $artistPayeeID = shift;
    my $dbo = Common::RSApp::GetClientDB();

    assert($artistPayeeID);

    my $sql = "SELECT * FROM " . kTable . " WHERE distribution != " . kStatementDistributionManual . " AND artist_payee_id = " . $dbo->DBQuote($artistPayeeID);

    Common::Log::Debug($sql);

    my $collection = $class->SUPER::GetAll($sql);
    return $collection->next() ? 1 : 0;
}

sub HasPendingTransactions {
    my $class   = shift;
    my $artistPayeeID = shift;
    my $dbo = Common::RSApp::GetClientDB();

    assert($artistPayeeID);
    my $sql = "SELECT 1 FROM artist_payee_account apa LEFT JOIN pending_transaction pt USING (finance_account_id) WHERE apa.artist_payee_id = " . $dbo->DBQuote($artistPayeeID) . "AND pt.amount IS NOT NULL";

    Common::Log::Debug($sql);

    my $collection = $class->SUPER::GetAll($sql);
    return $collection->next() ? 1 : 0;
}

sub HasTransactionHistory {
    my $class   = shift;
    my $artistPayeeID = shift;
    my $dbo = Common::RSApp::GetClientDB();

    assert($artistPayeeID);
    my $sql = "SELECT 1 FROM artist_payee_account apa LEFT JOIN finance_transaction ft USING (finance_account_id) WHERE apa.artist_payee_id = " . $dbo->DBQuote($artistPayeeID) . "AND ft.amount IS NOT NULL";

    Common::Log::Debug($sql);

    my $collection = $class->SUPER::GetAll($sql);
    return $collection->next() ? 1 : 0;
}

sub DeleteArtistPayee {
    my $class   = shift;
    my $artistPayeeID = shift;
    my $dbo = Common::RSApp::GetClientDB();

    assert($artistPayeeID);

    $dbo->DoCmd("DELETE FROM " . kTable . " WHERE artist_payee_id = " . $dbo->DBQuote($artistPayeeID));
}

1;