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

use lib '/app/tools/rps/lib';
use RPS::DB::Item::Publisher;

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

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

use constant kStatusCreating => 1;    # Initial state
use constant kStatusPending  => 2;    # Run finished, awaiting user acceptance
use constant kStatusAccepted => 3;    # Run finished and accepted

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 = "("
      . " mechanical_statement_id IN (select mechanical_statement_id FROM mechanical_statement WHERE mechanical_run_id=$runID )"
      . " AND " . "("
      . " (mechanical_statement_id IN (select mechanical_statement_id FROM mechanical_statement_adjustment_item))" . " OR "
      . " (mechanical_statement_id IN (select mechanical_statement_id FROM mechanical_statement_crossed_license_transaction))" . " OR "
      . " (mechanical_statement_id IN (select mechanical_statement_id FROM mechanical_statement_item))" . " OR "
      . " (mechanical_statement_id IN (select mechanical_statement_id FROM mechanical_statement_license))" . " OR "
      . " (mechanical_statement_id IN (select mechanical_statement_id FROM mechanical_statement_transaction))" . " OR "
      . " (mechanical_statement_id IN (select mechanical_statement_id FROM mechanical_statement_publisher_transaction))" . " OR "
      . " (mechanical_statement_id IN (select mechanical_statement_id FROM mechanical_statement_track))" . " OR "
      . " (mechanical_statement_id IN (select mechanical_statement_id FROM mechanical_statement WHERE previous_balance <> 0))" . ")" . ")";
    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, "mechanical_statement as s";

    my @where;
    if ( $args{runID} ) {
        push @where, "s.mechanical_run_id = " . $class->quote( $args{runID} );
    }
    if ( $args{payorID} ) {
        push @where, "s.payor_id = " . $class->quote( $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 filter
    } else {

        # This is the default - Only show statements that have _something_ on them.
        #
        push @where, _activeStatementClause( $class->quote( $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,  "publisher as p";
        push @where, "s.publisher_id = p.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 =
      "mechanical_statement_id IN (select mechanical_statement_id from mechanical_statement where mechanical_run_id = " . $class->quote($runID) . ")";

    my $sql = "SELECT * from mechanical_statement WHERE mechanical_run_id = " . $class->quote($runID) . " AND " . _activeStatementClause( $class->quote($runID) );

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

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

    my $sql = "SELECT * from mechanical_statement WHERE mechanical_run_id = ". $class->quote($runID);
    return $class->GetAll($sql);
}

sub GetActiveCountByMechanicalRunID {
    my ( $class, $runID, $payorID ) = @_;
    assert($runID);
    my $sql = "SELECT COUNT(*) from mechanical_statement where mechanical_run_id = " . $class->quote($runID) . " AND " . _activeStatementClause( $class->quote($runID) );
    if ($payorID) {
        $sql .= " and payor_id = " . $class->quote($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 mechanical_statement where mechanical_run_id = " . $class->quote($runID);
    if ($payorID) {
        $sql .= " and payor_id = " . $class->quote($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 mechanical_statement where mechanical_run_id = " . $class->quote($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 publisher_id from track_license where track_license_id in (select track_license_id from mechanical_statement_item where mechanical_statement_id = ?)";

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$id]);
    while ( my $href = $sth->fetchrow_hashref() ) {
        $idHash{ $href->{publisher_id} } = 1;
    }

    $sql =
"SELECT distinct publisher_id from track_license where track_license_id in (select track_license_id from mechanical_statement_adjustment_item where mechanical_statement_id = $?)";

    $dbo = Common::RSApp::GetClientDB();
    $sth = $dbo->DoCmdWithPlaceholders($sql, [$id]);
    while ( my $href = $sth->fetchrow_hashref() ) {
        $idHash{ $href->{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 mechanical_statement_id from mechanical_statement where mechanical_run_id in (select mechanical_run_id from 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->{mechanical_statement_id} } = 1;
    }

    return \%idHash;
}

sub GetOnHoldCount {
    my ( $class, %args ) = @_;

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

    my $runID = $args{runID};
    assert($runID);

    my $sql = "SELECT COUNT(*) as count FROM " . kTable;
    $sql .= " WHERE mechanical_run_id=" . $dbo->DBQuote($runID);

    if ( $args{payorID} ) {
        $sql .= " AND payor_id=" . $dbo->DBQuote( $args{payorID} );
    }

    if ( $args{filterBy} ) {
        my $filter = $args{filterBy};
        if ( kFilterByTotalLessThanZero == $filter ) {
            $sql .= " AND statement_total <= 0";
        } elsif ( kFilterByTotalGreaterThanZero == $filter ) {
            $sql .= " AND statement_total > 0";
        } elsif ( kFilterByTotalGreaterThatMinPayment == $filter ) {
            $sql .= " AND statement_total >= min_payment";
        } elsif ( kFilterByOnHold == $filter ) {
            $sql .= " AND on_hold = 1";
        }
    }

    $sql .= " AND on_hold = 1";

    my $sth   = $dbo->DoCmd($sql);
    my $hr    = $sth->fetchrow_hashref();
    my $count = $hr->{count} || 0;
    return $count;
}

sub GetDistributedCount {
    my ( $class, %args ) = @_;

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

    my $runID = $args{runID};
    assert($runID);

    my $sql = "SELECT COUNT(*) as count FROM " . kTable . " WHERE mechanical_run_id=" . $dbo->DBQuote($runID);

    if ( $args{payorID} ) {
        $sql .= " AND payor_id = " . $dbo->DBQuote( $args{payorID} );
    }

    if ( $args{filterBy} ) {
        my $filter = $args{filterBy};
        if ( kFilterByTotalLessThanZero == $filter ) {
            $sql .= " AND (balance <= 0 && amount_due <= 0)";
        } elsif ( kFilterByTotalGreaterThanZero == $filter ) {
            $sql .= " AND  (balance > 0 || amount_due > 0)";
        } elsif ( kFilterByTotalGreaterThatMinPayment == $filter ) {
            $sql .= " AND (balance >= min_payment || amount_due >= min_payment)";
        } elsif ( kFilterByOnHold == $filter ) {
            $sql .= " AND on_hold = 1";
        }
    }

    $sql .= " AND date_sent IS NOT NULL";

    my $sth   = $dbo->DoCmd($sql);
    my $hr    = $sth->fetchrow_hashref();
    my $count = $hr->{count} || 0;
    return $count;
}

sub GetEligibleCount {
    my ( $class, %args ) = @_;

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

    my $runID = $args{runID};
    assert($runID);

    my $sql = "SELECT COUNT(*) as count FROM " . kTable . " LEFT JOIN publisher USING (publisher_id)";
    $sql .= " WHERE mechanical_run_id = " . $dbo->DBQuote($runID);

    if ( $args{payorID} ) {
        $sql .= " AND payor_id=" . $dbo->DBQuote( $args{payorID} );
    }

    my $filter = $args{filterBy};
    if ( $filter ) {
        if ( kFilterByTotalLessThanZero == $filter ) {
            $sql .= " AND (balance <= 0 && amount_due <= 0)";
        } elsif ( kFilterByTotalGreaterThanZero == $filter ) {
            $sql .= " AND  (balance > 0 || amount_due > 0)";
        } elsif ( kFilterByTotalGreaterThatMinPayment == $filter ) {
            $sql .= " AND (balance >= mechanical_statement.min_payment || amount_due >= mechanical_statement.min_payment)";
        } elsif ( kFilterByOnHold == $filter ) {
            $sql .= " AND on_hold = 1";
        }
    }

    if ( kFilterByOnHold != $filter ) {
        $sql .= " AND on_hold != 1";
    }
    $sql .= " AND publisher.distribution = 1";

    my $sth   = $dbo->DoCmd($sql);
    my $hr    = $sth->fetchrow_hashref();
    my $count = $hr->{count} || 0;
    return $count;
}

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

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

    $runID = $dbo->DBQuote($runID);
    my $distribution = RPS::DB::Item::Publisher::kStatementDistributionManual;

    my $sql = qq/
        SELECT ms.*
        FROM mechanical_statement AS ms
        INNER JOIN publisher AS p ON ms.publisher_id = p.publisher_id AND p.distribution = $distribution
        WHERE 1
            AND ms.mechanical_run_id = $runID
        GROUP BY ms.mechanical_statement_id
    /;

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


1;