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

use strict;
use warnings;

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

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

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

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

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

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

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

    assert($searchTerm);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE "
      . $class->SearchSyntax( field => "publisher_name", value => $searchTerm ) . " OR "
      . $class->SearchSyntax( field => "client_account_id",   value => $searchTerm )
      . " ORDER BY publisher_name";

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

sub PublisherSearch {
    # 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 => "publisher_name", value => $searchTerm );
    my $searchClientAccountID = $class->SearchSyntax( field => "client_account_id", value => $searchTerm );

    my $sql = qq/
        SELECT p.*
        FROM mechanical_statement AS ms
        LEFT JOIN publisher AS p ON p.publisher_id = ms.publisher_id
        WHERE 1
            AND ms.mechanical_run_id = $runID
            AND p.status IN (0, 1, 2)
            AND ( $searchName OR $searchClientAccountID )
        ORDER BY publisher_name
        LIMIT $limit
    /;

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

sub Match {
    my ( $class, $name ) = @_;
    return undef if ( !defined $name || $name eq '' );

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT * FROM " . kTable . " WHERE publisher_name = " . $dbo->DBQuote($name);
    return $class->SUPER::GetAll($sql);
}

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

    if ( $args{directOnly} ) {
        push @where, "is_admin = 0";
        push @where, "is_agency = 0";
        push @where, "(agent_id IS NULL || agent_id = 0)";
        push @where, "(admin_id IS NULL || admin_id = 0)";
    }

    if ( $args{adminOnly} ) {
        push @where, "is_admin = 1";
    }

    if ( $args{agentOnly} ) {
        push @where, "is_agency = 1";
    }

    if ( $args{withAdmin} ) {
        push @where, "(admin_id IS NOT NULL && admin_id <> 0)";
        push @where, "(agent_id IS NULL || agent_id = 0)";
    }

    if ( $args{withAgent} ) {
        push @where, "(agent_id IS NOT NULL && agent_id <> 0)";
    }

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

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

sub GetAll {
    my ($class) = @_;
    my $sql = "SELECT * FROM " . kTable . " ORDER BY publisher_name";
    return $class->SUPER::GetAll($sql);
}

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

    my $sql = "SELECT * FROM " . kTable . " WHERE status=1 OR status=2";
    return $class->SUPER::GetAll($sql);
}

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

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

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

sub GetAllWithLicenses {
    my ( $class, $publisherID ) = @_;

    my $sql =
          "SELECT publisher.* FROM "
        . kTable
        . " INNER JOIN track_license AS l ON publisher.publisher_id = l.publisher_id"
        . " WHERE l.publisher_id = "
        . $class->quote($publisherID);

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

sub GetAllAgents {
    my ($class) = @_;
    my $sql = "SELECT * FROM " . kTable . " WHERE is_agency=1 ORDER BY publisher_name";
    return $class->SUPER::GetAll($sql);
}

sub GetAllAdmins {
    my ($class) = @_;
    my $sql = "SELECT * FROM " . kTable . " WHERE is_admin=1 ORDER BY publisher_name";
    return $class->SUPER::GetAll($sql);
}

sub GetByAdminID {
    my ( $class, $adminID ) = @_;
    return undef if ( !$adminID );

    my $sql = "SELECT * FROM " . kTable . " WHERE admin_id = " . $class->quote($adminID) . " ORDER BY publisher_name";
    return $class->SUPER::GetAll($sql);
}

sub GetByAgentID {
    my ( $class, $agentID ) = @_;
    return undef unless ( defined $agentID and $agentID =~ /^\d+$/ );

    my $sql = "SELECT * FROM " . kTable . " WHERE agent_id = " . $class->quote($agentID) . " ORDER BY publisher_name";
    return $class->SUPER::GetAll($sql);
}

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

    my $sql =
        "SELECT *"
      . " FROM "
      . kTable
      . " WHERE publisher_id IN"
      . " (SELECT publisher_id FROM mechanical_statement WHERE mechanical_run_id = "
      . $class->quote($runID)
      . ")";

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

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

    my $sql =
        "SELECT *"
      . " FROM "
      . kTable
      . " WHERE publisher_id IN"
      . " (SELECT publisher_id FROM mechanical_statement WHERE mechanical_run_id = "
      . $class->quote($runID)
      . " AND (statement_total <> 0 OR previous_balance <> 0 OR applied_to_advance <> 0))"
      . " ORDER BY publisher_name";

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

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

    $payorID = $class->quote($payorID);
    my $sql = qq/
        SELECT p.*
        FROM publisher                 AS p
        INNER JOIN publisher_account   AS pa  ON p.publisher_id = pa.publisher_id
        INNER JOIN finance_transaction AS fta ON pa.finance_account_id = fta.finance_account_id
        LEFT JOIN  finance_transaction AS ftb ON fta.finance_account_id = ftb.finance_account_id AND fta.finance_transaction_id < ftb.finance_transaction_id
        WHERE 1 = 1
            AND ftb.finance_transaction_id is null    -- get the latest finance_transaction_id
            AND fta.balance != 0                      -- with balance
            AND p.status != 0
            AND pa.payor_id = $payorID
    /;

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

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

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

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

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

    my $sql = "SELECT * FROM " . kTable . " WHERE distribution = " . kStatementDistributionOnline . " ORDER BY publisher_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 publisher_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 publisher_name";

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

sub IsNotManual {
    my ($class, $publisherID) = @_;

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

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

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

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

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

    assert($publisherID);

    my $sql = "SELECT 1 FROM mechanical_statement WHERE publisher_id = " . $dbo->DBQuote($publisherID);

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

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

    # We also need to see if this publisher appeared
    # on an admin or agent statement.
    if ($hasActivity == 0) {
        $sql = "SELECT 1 FROM mechanical_statement_item WHERE publisher_id = " . $dbo->DBQuote($publisherID);

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

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

    return $hasActivity;
}


1;