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

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 => 'payor';
use constant kDB    => Common::DB::Item::kClientDB();

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

sub _getEncrypted {  # define which fields (if any) are encrypted
    return (
        'comments', 'tax_id'
    );
}
sub GetDefault {
    my ($class) = @_;
    return RPS::DB::Item::Payor->Lookup( is_default => 1 );
}

sub GetFromIDList {
    my ( $class, $listRef ) = @_;

    my $sql = "SELECT * FROM " . kTable . " WHERE payor_id IN (" . join( ',', @$listRef ) . ")";
    return $class->SUPER::GetAll($sql);
}

sub GetAll {
    my ($class) = @_;
    assert($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_payor_id", value => $searchTerm )
      . " ORDER BY name";

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

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

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

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

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

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

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

    $payorID ? $payorID = $dbo->DBQuote($payorID) : return undef;

    my $sql = <<ENDSQL;
        SELECT artist_payee_id
        FROM
            artist_payee
        WHERE
            artist_payee_id NOT IN (
                SELECT artist_payee_id FROM artist_payee_account WHERE payor_id = $payorID AND min_payment <> 0
            )
        ORDER BY artist_payee_id;
ENDSQL
    return $class->SUPER::GetAll($sql);
}

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

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

    $payorID ? $payorID = $dbo->DBQuote($payorID) : return undef;

    my $sql = <<ENDSQL;
        SELECT label_payee_id
        FROM
            label_payee
        WHERE
            label_payee_id NOT IN (
                SELECT label_payee_id FROM label_payee_account WHERE payor_id = $payorID AND min_payment <> 0

            )
        ORDER BY label_payee_id;
ENDSQL
    return $class->SUPER::GetAll($sql);
}

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

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

    $payorID ? $payorID = $dbo->DBQuote($payorID) : return undef;

    my $sql = <<ENDSQL;
        SELECT publisher_id
        FROM
            publisher
        WHERE
            publisher_id NOT IN (
                SELECT publisher_id FROM publisher_account WHERE payor_id = $payorID AND min_payment <> 0

            )
        ORDER BY publisher_id;
ENDSQL
    return $class->SUPER::GetAll($sql);
}

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

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

    $payorID ? $payorID = $dbo->DBQuote($payorID) : return undef;

    my $sql = <<ENDSQL;
        SELECT ca_publisher_id
        FROM
            ca_publisher
        WHERE
            ca_publisher_id NOT IN (
                SELECT ca_publisher_id FROM ca_publisher_account WHERE payor_id = $payorID AND min_payment <> 0

            )
        ORDER BY ca_publisher_id;
ENDSQL
    return $class->SUPER::GetAll($sql);
}

sub GetPayorsWithArtistPayeeMinPayment {
    my $class = shift;

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

    my $sql = <<ENDSQL;
        SELECT payor_id, artist_payee_min_payment
        FROM
           @{[ kTable ]}
        WHERE
            artist_payee_min_payment > 0;
ENDSQL
    return $class->SUPER::GetAll($sql);
}

sub GetPayorsWithLabelPayeeMinPayment {
    my $class = shift;

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

    my $sql = <<ENDSQL;
        SELECT payor_id, label_payee_min_payment
        FROM
           @{[ kTable ]}
        WHERE
            label_payee_min_payment > 0;
ENDSQL
    return $class->SUPER::GetAll($sql);
}

sub GetPayorsWithPublisherMinPayment {
    my $class = shift;

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

    my $sql = <<ENDSQL;
        SELECT payor_id, us_publisher_min_payment
        FROM
           @{[ kTable ]}
        WHERE
            us_publisher_min_payment > 0;
ENDSQL
    return $class->SUPER::GetAll($sql);
}

sub GetPayorsWithCAPublisherMinPayment {
    my $class = shift;

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

    my $sql = <<ENDSQL;
        SELECT payor_id, ca_publisher_min_payment
        FROM
           @{[ kTable ]}
        WHERE
            ca_publisher_min_payment > 0;
ENDSQL
    return $class->SUPER::GetAll($sql);
}

1;