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

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


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




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)
		. " ORDER BY publisher_name";

    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 GetAllActiveOrOnHoldWithLicensesForPayor
{
    my ($class, $payorID) = @_;

	my $sql = "SELECT * FROM ".kTable." WHERE (status=1 OR status=2)"
     . " AND ca_publisher_id IN (select ca_publisher_id from ca_track_license where payor_id=$payorID and inactive=0)";
    Common::Log::Print("CAPublisher::GetAllActiveOrOnHoldWithLicensesForPayor sql=$sql");

    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 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=$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=$agentID ORDER BY publisher_name";
    return $class->SUPER::GetAll($sql);
}


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

    my $sql = "SELECT *"
     . " FROM ".kTable
     . " WHERE ca_publisher_id IN"
     . " (SELECT ca_publisher_id FROM ca_mechanical_statement WHERE ca_mechanical_run_id=$runID)";

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


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

    my $sql = "SELECT *"
     . " FROM ".kTable
     . " WHERE ca_publisher_id IN"
     . " (SELECT ca_publisher_id FROM ca_mechanical_statement WHERE ca_mechanical_run_id=$runID"
     . " AND (statement_total <> 0 OR previous_balance <> 0 OR applied_to_advance <> 0))"
     . " ORDER BY publisher_name";     

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


1;