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

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();

# Most if not all of the Catalog group will have the same base group of columns.

sub _config
{
    my ($class) = @_;
    return
    {
        'payee'                => {},
        'client_account_no'    => {},
        'status'               => {},
        'address_1'              => {},
        'address_2'              => {},
        'address_3'              => {},
        'city'                 => {},
        'state'                => {},
        'postal_code'          => {},
        'country'              => {},
        'email'                => {},
        'phone'                => {},
        'fax'                  => {},
        'tax_id'               => {},
        'comments'             => {},
        'date_modified'         => {},
        'date_created'         => {},
        'created_by'         => {},
        'modified_by'         => {},
        'label_payee_id'         => {},

        # Transactions
        'total_no_licenses'  => {},
        'payor_name'         => {},
        'client_payor_no'    => {},
        'payor_status'       => {},
        'default_payor'      => {},
        'minimum_payment'    => {},
        'transaction_status' => {},
        'transaction_type'   => {},
        'amount'             => {},
        'transaction_date'   => {},
        'check_no'           => {},
        'memo'               => {},
        'balance'            => {},
    };

}



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

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

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

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

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

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

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


    my $sql;

    $sql .= 'SELECT ' . join(",\n", @select ) . "\nFROM " . join(',', @from);

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

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

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

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

    # SQL is much easier to debug when it has new lines, but it doesn't log well.
    $sql =~ s/\n/ /g;
    Log->debug( "QUERY: $sql" );
    
    #die $sql;

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



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

    push @$from, 'label_payee';
}


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

    push @$select, "label_payee.name AS payee";
    push @$select, "label_payee.client_account_id AS client_account_no";
    push @$select, "IF( label_payee.status = 0, 'inactive', " .
                       "IF( label_payee.status = 1, 'active', " .
                           "IF( label_payee.status = 2, 'on hold', 'unknown' ) ) ) AS status";
    push @$select, "label_payee.street_address AS address_1";
    push @$select, "label_payee.street_address_2 AS address_2";
    push @$select, "label_payee.street_address_3 AS address_3";
    push @$select, "label_payee.city AS city";
    push @$select, "label_payee.state_province AS state";
    push @$select, "label_payee.postal_code AS postal_code";
    push @$select, "label_payee.country_code AS country";
    push @$select, "label_payee.email AS email";
    push @$select, "label_payee.phone_number AS phone";
    push @$select, "label_payee.fax_number AS fax";
    push @$select, "label_payee.tax_id AS tax_id";
    push @$select, "label_payee.comments AS comments";

    push @$select, "label_payee.date_modified AS date_modified";
    push @$select, "label_payee.date_created AS date_created";
    push @$select, "label_payee.created_by AS created_by";
    push @$select, "label_payee.modified_by AS modified_by";
    push @$select, "label_payee.label_payee_id AS label_payee_id";

    # This is just for sorting purposes.
    push @$select, "IF(label_payee.client_account_id REGEXP '^[[:digit:]]+".'$'."',LPAD(label_payee.client_account_id,32,'0'),label_payee.client_account_id) as sort2";
}

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


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

}


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

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

    my $dateType = $args{dateType};
    my $startDate = $args{startDate};
    my $endDate = $args{endDate};
    
    if ($dateType)
    {
        if ($startDate)
        {
            push @$where, "TO_DAYS(label_payee.$dateType) >= TO_DAYS(" . $class->quote($startDate) . ")";
        }
        if ($endDate)
        {
            push @$where, "TO_DAYS(label_payee.$dateType) <= TO_DAYS(" . $class->quote($endDate) . ")";
        }
    }
}

sub _BuildOrderBy
{
    my ($class, $orderBy, %args) = @_;
    push @$orderBy, "payee";
    push @$orderBy, "sort2";
}

1;