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

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 => 'label_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;

sub _getEncrypted {  # define which fields (if any) are encrypted                                                                                                     [0/3046]
    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->SUPER::GetAll($sql);
}

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

    my $sql = 'SELECT * FROM ' . kTable . ' ORDER BY name';
    return $class->SUPER::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->SUPER::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 label_royalty_statement AS ars
        LEFT JOIN label_payee AS ap ON ap.label_payee_id = ars.payee_id
        WHERE 1
            AND ars.label_royalty_run_id = $runID
            AND ap.status IN (0, 1, 2)
            AND ( $searchName OR $searchClientAccountID )
        ORDER BY NAME
        LIMIT $limit
    /;

    return $class->SUPER::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->SUPER::GetAll($sql);
}

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

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

    my %map;

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

    return \%map;
}

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

    my $sql =
      "SELECT * FROM " . kTable . " WHERE label_payee_id IN (SELECT payee_id FROM distribution_statement WHERE distribution_run_id = " . $class->quote($runID) . ")";

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

1;