#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::CAMechanicalStatement;
use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::Assert;
use Common::DB::Item;
use Common::DB::ItemCollection;
use base 'Common::DB::Item';


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

use constant kStatusNotQueued => 0;
use constant kStatusInQueue => 1;
use constant kStatusRunning => 2;   
use constant kStatusComplete => 3; 
use constant kStatusError => 4; 
use constant kStatusAborted => 5; 


use constant kFilterByHasActivity => 0;
use constant kFilterByTotalLessThanZero => 1;
use constant kFilterByTotalGreaterThanZero => 2;
use constant kFilterByTotalGreaterThatMinPayment => 3;
use constant kFilterByOnHold => 4;
use constant kFilterByNone => 5;


sub _activeStatementClause
{
    my ($runID) = @_;

    my $sql = "("
     . " ca_mechanical_statement_id IN (select ca_mechanical_statement_id FROM ca_mechanical_statement WHERE ca_mechanical_run_id=$runID )"
    . " AND "
    . "("
     . " (ca_mechanical_statement_id IN (select ca_mechanical_statement_id FROM ca_mechanical_statement_adjustment_item))"
     . " OR "
     . " (ca_mechanical_statement_id IN (select ca_mechanical_statement_id FROM ca_mechanical_statement_crossed_license_transaction))"
     . " OR "
     . " (ca_mechanical_statement_id IN (select ca_mechanical_statement_id FROM ca_mechanical_statement_item))"
     . " OR "
     . " (ca_mechanical_statement_id IN (select ca_mechanical_statement_id FROM ca_mechanical_statement_license))"
     . " OR "
     . " (ca_mechanical_statement_id IN (select ca_mechanical_statement_id FROM ca_mechanical_statement_transaction))"
     . " OR "
     . " (ca_mechanical_statement_id IN (select ca_mechanical_statement_id FROM ca_mechanical_statement_publisher_transaction))"     
     . " OR "
     . " (ca_mechanical_statement_id IN (select ca_mechanical_statement_id FROM ca_mechanical_statement_track))"
    . ")"
    . ")";
    return $sql;
}

# The number of accessors was starting to multiply exponentially...
# So I am going to replace them with a more generic version that can
# take multiple arguments.
#
# One wrinkle here is that we need to be able to sort by publisher_name, which
# means we need to do a simple join.
#
sub Get
{
    my ($class, %args) = @_;


    my @from;
    push @from, "ca_mechanical_statement as s";

    my @where;
    if ($args{runID})
    {
        push @where, "s.ca_mechanical_run_id = ".$args{runID};
    }
    if ($args{payorID})
    {
        push @where, "s.payor_id = ".$args{payorID};
    }

    if ($args{filterBy})
    {
        my $filter = $args{filterBy};
        if (kFilterByTotalLessThanZero == $filter)
        {
            push @where, "statement_total <= 0";
        }
        elsif (kFilterByTotalGreaterThanZero == $filter)
        {
            push @where, "statement_total > 0";
        }
        elsif (kFilterByTotalGreaterThatMinPayment == $filter)
        {
            push @where, "s.statement_total >= s.min_payment";
        }
        elsif (kFilterByOnHold == $filter)
        {
            push @where, "s.on_hold = 1";
        }
        #
        # else...  kFilterByNone means no filters.
    }
    else
    {
        # This is the default - Only show statements that have _something_ on them.
        #
        push @where, _activeStatementClause($args{runID});
        #push @where, "(s.statement_total <> 0 OR s.previous_balance <> 0 OR s.applied_to_advance <> 0)";        
    }


    my $sortString;
    if ($args{sortByPublisherName})
    {
        push @from, "ca_publisher as p";
        push @where, "s.ca_publisher_id = p.ca_publisher_id";
        $sortString = "ORDER BY p.publisher_name";
    }
    my $whereString;
    if (scalar @where)
    {
        $whereString = "WHERE " . join(' AND ', @where);
    }

    my $fromString;
    if (scalar @from)
    {
        $fromString = " FROM " . join(',', @from);
    }


    my $sql = "SELECT s.* $fromString $whereString $sortString";

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

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

    my $idSubqueryClause = "ca_mechanical_statement_id IN (select ca_mechanical_statement_id from ca_mechanical_statement where ca_mechanical_run_id=$runID)";

    my $sql = "SELECT * from ca_mechanical_statement WHERE ca_mechanical_run_id=$runID"
     . " AND " . _activeStatementClause($runID);

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

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

    my $sql = "SELECT * from ca_mechanical_statement WHERE ca_mechanical_run_id=$runID";
    return $class->GetAll($sql);
}

sub GetActiveCountByMechanicalRunID
{
    my ($class, $runID, $payorID) = @_;
    assert($runID);
    my $sql = "SELECT COUNT(*) from ca_mechanical_statement where ca_mechanical_run_id=$runID"
     . " AND " . _activeStatementClause($runID);
    if ($payorID)
    {
        $sql .=" and payor_id=$payorID";
    }
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    my $hr = $sth->fetchrow_hashref();
    return $hr->{'COUNT(*)'};
}


sub GetCountByMechanicalRunID
{
    my ($class, $runID, $payorID) = @_;
    assert($runID);
    my $sql = "SELECT COUNT(*) from ca_mechanical_statement where ca_mechanical_run_id=$runID";
    if ($payorID)
    {
        $sql .=" and payor_id=$payorID";
    }
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    my $hr = $sth->fetchrow_hashref();
    return $hr->{'COUNT(*)'};
}


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

    my @payorIDs;

    my $sql = "SELECT distinct payor_id from ca_mechanical_statement where ca_mechanical_run_id=$runID";
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    while (my $hr = $sth->fetchrow_hashref())
    {
        push @payorIDs, $hr->{payor_id};
    }

    return @payorIDs;
}




# Not certain this belongs here... This call spans several tables.
#
sub GetPublishers
{
    my ($class, $id) = @_;

    # Need to query two tables - mechanical_statement_item and mechanical_statement_adjustment_item
    my %idHash;
    my $sql = "SELECT distinct ca_publisher_id from ca_track_license where ca_track_license_id in (select ca_track_license_id from ca_mechanical_statement_item where ca_mechanical_statement_id=$id)";

	my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    while (my $href = $sth->fetchrow_hashref())
    {
        $idHash{$href->{ca_publisher_id}} = 1;
    }

    $sql = "SELECT distinct ca_publisher_id from ca_track_license where ca_track_license_id in (select ca_track_license_id from ca_mechanical_statement_adjustment_item where ca_mechanical_statement_id=$id)";

	$dbo = Common::RSApp::GetClientDB();
    $sth = $dbo->DoCmd($sql);
    while (my $href = $sth->fetchrow_hashref())
    {
        $idHash{$href->{ca_publisher_id}} = 1;
    }

    return keys %idHash;
}


# This is a special-case accessor used by the mechanical_run script
#
sub GetIDSFromCommittedRuns
{
    my ($class) = @_;

    my $sql = "SELECT distinct ca_mechanical_statement_id from ca_mechanical_statement where ca_mechanical_run_id in (select ca_mechanical_run_id from ca_mechanical_run where status=2 or status=5)";

    my %idHash;
	my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    while (my $href = $sth->fetchrow_hashref())
    {
        $idHash{$href->{ca_mechanical_statement_id}} = 1;
    }

    return \%idHash;
}



1;