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

# 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
    {
        'payee'                 => {},
        'client_account_no'     => {},
        'payee_id'              => {},
        'payee_status'          => {},
        'payor_name'            => {},
        '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.
    #

    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) = @_;

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

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

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

#    my @orderBy;
#    $class->_BuildOrderBy(\@orderBy, $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);
    }

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

    return $sql;
}



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

    push @$from, $tType;
}


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

    push @$select, (
        $class->_payee() . '.name as payee',
        $class->_payee() . '.client_account_id as client_account_no',
        $class->_payee() . '.'.$class->_payee() . '_id as payee_id',
        "IF( ".$class->_payee().".status = 0, 'inactive', " .
            "IF( ".$class->_payee().".status = 1, 'active', " .
                "IF( ".$class->_payee().".status = 2, 'on hold', 'unknown' ) ) ) AS payee_status",

        'payor.name as payor_name',
        "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",
        $class->_payeeAccount().".min_payment 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",
    );

    # 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(".$class->_payee().".client_account_id REGEXP '^[[:digit:]]+".'$'."',LPAD(".$class->_payee().".client_account_id,32,'0'),".$class->_payee().".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->_payeeAccount().' USING (finance_account_id)';
    push @$joins, 'JOIN ' . $class->_payee() . ' USING ('.$class->_payee().'_id)';
    push @$joins, 'JOIN payor USING (payor_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, "payee";
#    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;