#------------------------------------------------------------
# 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;