#------------------------------------------------------------
# Copyright (C) 2011 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::DynamicReport::Transactions::PublisherBase;

# This is the base class for the ArtistProducer and Label reports.

use strict;
use warnings;

use lib '/app/tools/common/lib';
use lib '/app/tools/rps/lib';

use Common::Log;
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;

use RPS::DB::Item::FinanceTransaction;

use Data::Dumper;
use List::Compare;

use base 'RPS::DB::Item::DynamicReport';

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


sub _config
{
    my ($class) = @_;
    return
    {
        'publisher'             => {},
        'client_account_no'     => {},
        'publisher_id'          => {},
        'publisher_status'      => {},
        'publisher_type'        => {},
        'agent_name'            => {},
        'admin_name'            => {},
        'license_count'         => {},
        'payor_name'            => {},
        'payor_id'              => {},
        'client_payor_no'       => {},
        'payor_status'          => {},
        'default_payor'         => {},
        'min_payment'           => {},
        't_status'              => {},
        't_type'                => {},
        't_amount'              => {},
        't_date'                => {},
        't_check_no'            => {},
        't_memo'                => {},
        't_balance'             => {},
        't_date_created'        => {},
        't_date_modified'       => {},
        't_id'                  => {},
        'finance_account_id'    => {},
    };

}


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

    # Since we're possibly combinine the contents of two transaction tables, 
    # we've got to build two queries and possibly union them.
    #

    # And... we also need to take the 'publisher_indirect_balance_account' into account for
    # pending transactions that will end up being reported via an admin or agent.
    # I think that's going to be another set of queries unioned together.
    #

    my @queries;
    push @queries, $class->_BuildQuery('pending_transaction', %args) 
        if ($args{showUnprocessed});

    push @queries, $class->_BuildQuery('finance_transaction', %args) 
        if ($args{showProcessed});

    my @orderBy;
    $class->_BuildOrderBy(\@orderBy, %args);

    my $sql;
    if (scalar @queries > 1)
    {
        $sql = join(' UNION ', @queries);
        $sql = "SELECT * FROM ( $sql ) as subq ";
    }
    else
    {
        $sql = $queries[0];
    }

    if (scalar @orderBy) {
        $sql .= "\nORDER BY " . join(',', @orderBy);
    }


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


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

    return $class->_BuildQueryRegular($tType, %args) . ' UNION ' . $class->_BuildQueryIndirect($tType, %args);
}


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

    my @select;
    $class->_BuildSelect(\@select, $tType, %args);

    my @joins;
    $class->_BuildJoins(\@joins, $tType, %args);

    my @groupBy;
    $class->_BuildGroupBy(\@groupBy, $tType, %args);

    my @where;
    $class->_BuildWhere(\@where, $tType, %args);

    my @from;
    $class->_BuildFrom(\@from, $tType, %args);

    my $sql;

    $sql .= 'SELECT ' . join(",", @select ) . " FROM " . join(',', @from);

    if (scalar @joins) {
        $sql .= " " . join(" ", @joins);
    }

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

    if (scalar @groupBy) {
        $sql .= " GROUP BY " . join(',', @groupBy);
    }

    return $sql;
}

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

    my @select;
    $class->_BuildSelectIndirect(\@select, $tType, %args);

    my @joins;
    $class->_BuildJoinsIndirect(\@joins, $tType, %args);

    my @groupBy;
    $class->_BuildGroupBy(\@groupBy, $tType, %args);

    my @where;
    $class->_BuildWhere(\@where, $tType, %args);


    # Both the 'direct' and 'indirect' queries start with the same tables (the actual transaction table)
    #
    my @from;
    $class->_BuildFrom(\@from, $tType, %args);

    my $sql;

    $sql .= 'SELECT ' . join(",", @select ) . " FROM " . join(',', @from);

    if (scalar @joins) {
        $sql .= " " . join(" ", @joins);
    }

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

    if (scalar @groupBy) {
        $sql .= " GROUP BY " . join(',', @groupBy);
    }

    return $sql;
}



sub _BuildFrom
{
    my ($class, $from, $tType, %args) = @_;

    push @$from, $tType;
}


sub _BuildSelect
{
    my ($class, $select, $t, %args) = @_;

    return $class->_BuildSelectActual($select, $t, $class->_publisher(), $class->_publisherAccount());
}

sub _BuildSelectIndirect
{
    my ($class, $select, $t, %args) = @_;

    # Passing '0' for the publisher account means that we don't care about any of those fields.
    # Right now we just look there for 'min_payment', which the indirect balance account doesn't have.
    #
    return $class->_BuildSelectActual($select, $t, $class->_publisher(), 0);
}


sub _BuildSelectActual
{
    my ($class, $select, $t, $publisher, $publisherAccount) = @_;

    push @$select, (
        "$publisher.publisher_name as publisher",
        "$publisher.client_account_id as client_account_no",
        "$publisher.$publisher" . '_id as publisher_id',
        "IF( $publisher.status = 0, 'inactive', " .
            "IF( $publisher.status = 1, 'active', " .
                "IF( $publisher.status = 2, 'on hold', 'unknown' ) ) ) AS publisher_status",

        "IF( $publisher.is_agency = 1, 'agent', " .
            "IF( $publisher.is_admin = 1, 'admin', 'standard' ) ) as publisher_type",

        'payor.name as payor_name',
        'payor.payor_id as payor_id',
        "IF( payor.inactive, 'inactive', 'active' ) as payor_status",

        'payor.client_payor_id as client_payor_no',
        "IF( payor.is_default, 'yes', 'no' ) as default_payor",
        $publisherAccount ?  "$publisherAccount.min_payment as min_payment" : 
          "IF( $publisher.agent_id > 0, 'See Agent', IF( $publisher.admin_id > 0, 'See Admin', '')) as min_payment",
        ($t eq 'finance_transaction' ? "'Processed'" : "'Pending'") . " as t_status",

        $class->_transactionTypeSQL($t) . ' as t_type',
        "$t.amount as t_amount",
        "$t.transaction_date as t_date",
        "$t.check_number as t_check_no",
        "$t.memo as t_memo",
        ($t eq 'finance_transaction' ? "$t.balance" : "''") . " as t_balance",
        "$t.date_modified as t_date_created",
        "$t.date as t_date_modified",
        "$t.".$t."_id as t_id",
        "$t.finance_account_id as finance_account_id",
        "IF( $publisher.agent_id, agent.publisher_name, '') as agent_name",
        "IF( $publisher.admin_id, admin.publisher_name, '') as admin_name",
    );

    # This bit of wackiness is to get a non-numeric field to sort numerically if it just contains digits, and
    # alphabetically otherwise.
    #
    push @$select, "IF($publisher.client_account_id REGEXP '^[[:digit:]]+".'$'."',LPAD($publisher.client_account_id,32,'0'),$publisher.client_account_id) as sort2";

    # JPK - Note the wacky mapping of dates.  That's just the way it is, sadly.
}

sub _transactionTypeSQL {
    my ($class, $t) = @_;

    return " IF( $t.type_code = " . RPS::DB::Item::FinanceTransaction::kTypeOpeningBalance . ", 'opening balance', " .
           " IF( $t.type_code = " . RPS::DB::Item::FinanceTransaction::kTypeAdjustment . ", 'adjustment', " .
           " IF( $t.type_code = " . RPS::DB::Item::FinanceTransaction::kTypeAdvance . ", 'advance', " .
           " IF( $t.type_code = " . RPS::DB::Item::FinanceTransaction::kTypePayment . ", 'payment', " .
           " IF( $t.type_code = " . RPS::DB::Item::FinanceTransaction::kTypeClosingBalance . ", 'closing balance', " .
           " IF( $t.type_code IS NULL, NULL, " .
           " 'unknown' ) ) ) ) ) ) ";
}

sub _BuildJoins
{
    my ($class, $joins, $t, %args) = @_;

    push @$joins, 'JOIN '.$class->_publisherAccount().' USING (finance_account_id)';
    push @$joins, 'JOIN ' . $class->_publisher() . ' USING ('.$class->_publisher().'_id)';
    push @$joins, 'JOIN payor USING (payor_id)';
    push @$joins, 'LEFT JOIN '.$class->_publisher() . ' AS agent ON ('.$class->_publisher().'.agent_id = agent.'.$class->_publisher().'_id)';
    push @$joins, 'LEFT JOIN '.$class->_publisher() . ' AS admin ON ('.$class->_publisher().'.admin_id = admin.'.$class->_publisher().'_id)';
}

sub _BuildJoinsIndirect
{
    my ($class, $joins, $t, %args) = @_;

    push @$joins, 'JOIN '.$class->_publisherAccountIndirect().' USING (finance_account_id)';
    push @$joins, 'JOIN ' . $class->_publisher() . ' USING ('.$class->_publisher().'_id)';
    push @$joins, 'JOIN payor USING (payor_id)';
    push @$joins, 'LEFT JOIN '.$class->_publisher() . ' AS agent ON ('.$class->_publisher().'.agent_id = agent.'.$class->_publisher().'_id)';
    push @$joins, 'LEFT JOIN '.$class->_publisher() . ' AS admin ON ('.$class->_publisher().'.admin_id = admin.'.$class->_publisher().'_id)';
}


sub _BuildGroupBy
{
    my ($class, $groupBy, $t, %args) = @_;
}

sub _BuildWhere
{
    my ($class, $where, $t, %args) = @_;

    my $dateType = $args{dateType};
    my $startDate = $args{startDate};
    my $endDate = $args{endDate};

    
    if ($dateType)
    {
        # Need to map the dateType to the actual field names.
        #
        my $dateField;
        if ('date_modified' eq $dateType)
        {
            $dateField = 'date';
        }
        elsif ('date_created' eq $dateType)
        {
            $dateField = 'date_modified';
        }
        else
        {
            die "ERROR : Invalid dateType $dateType";
        }

        if ($startDate)
        {
            push @$where, "TO_DAYS($t.$dateField) >= TO_DAYS(" . $class->quote($startDate) . ")";
        }
        if ($endDate)
        {
            push @$where, "TO_DAYS($t. $dateField) <= TO_DAYS(" . $class->quote($endDate) . ")";
        }                        
    }
}

sub _BuildOrderBy
{
    my ($class, $orderBy, %args) = @_;
    push @$orderBy, "publisher";
#    push @$orderBy, "client_account_no";   
    push @$orderBy, "sort2";
    push @$orderBy, "finance_account_id";
    push @$orderBy, "payor_name";
    push @$orderBy, "t_status";
    push @$orderBy, "t_date_created";
}

1;