#------------------------------------------------------------
# Copyright (C) 2011 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::DynamicReport::Contract::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 base 'RPS::DB::Item::DynamicReport';

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

sub _config {
    my ($class) = @_;

    return {
        'title'              => {},
        'client_contract_id' => {},
        'payor'              => {},
        'payee'              => {},
        'payee_id'           => {},
        'client_account_no'  => {},
        'issue_date'         => {},
        'label_name'         => {},
        'service_name'       => {},
        'country_code'       => {},
        'distribution_fee'   => {},
        'date_created'       => {},
        'date_modified'      => {},
        'created_by'         => {},
        'modified_by'        => {},
        'rs_contract_id'     => {},
        'sort2'              => {},
        'sort3'              => {},
        'sort4'              => {},
    };

}

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

    # We need to combine the results from two different tables for this, since we have the group terms
    # and the exceptions to deal with.

    my @tables = ( 'label_contract_group_term_label', 'label_contract_group_term_exception' );

    my $sql;

    foreach my $table (@tables) {
        $args{table} = $table;
        $sql .= " UNION " if ($sql);

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

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

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

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

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

        $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 );
        }

    }

    #die $sql;

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

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

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

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

    push @$from, $table;
}

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

    push @$select, "label_contract.title AS title";
    push @$select, "label_contract.label_contract_id as rs_contract_id";
    push @$select, "label_contract.client_contract_id as client_contract_id";
    push @$select, "payor.name AS payor";
    push @$select, "label_payee.name AS payee";
    push @$select, "label_payee.label_payee_id AS payee_id";
    push @$select, "label_payee.client_account_id AS client_account_no";
    push @$select, "label_contract.issue_date as issue_date";
    push @$select, "label.label_name as label_name";
    push @$select, "label_contract.date_created as date_created";
    push @$select, "label_contract.date_modified as date_modified";
    push @$select, "label_contract.created_by as created_by";
    push @$select, "label_contract.modified_by as modified_by";

    if ( $table eq 'label_contract_group_term_label' ) {
        push @$select, "'All' as service_name";
        push @$select, "'1111' as sort3";
        push @$select, "'All' as country_code";
        push @$select, "'1111' as sort4";
        push @$select, "label_contract_group_term.distribution_fee as distribution_fee";
    } else {
        push @$select, "IF(label_contract_group_term_exception.service_id = 0, 'All', service.service_name) as service_name";
        push @$select, "IF(label_contract_group_term_exception.service_id = 0, '1111', service.service_name) as sort3";
        push @$select,
"IF(label_contract_group_term_exception.country_code = '0', 'All', label_contract_group_term_exception.country_code) as country_code";
        push @$select,
          "IF(label_contract_group_term_exception.country_code = '0', '1111', label_contract_group_term_exception.country_code) as sort4";
        push @$select, "label_contract_group_term_exception.distribution_fee as distribution_fee";
    }

    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 _BuildJoins {
    my ( $class, $joins, %args ) = @_;
    my $table = $args{table};

    if ( $table eq 'label_contract_group_term_label' ) {
        push @$joins,
"LEFT JOIN label_contract_group_term ON (label_contract_group_term_label.label_contract_group_term_id = label_contract_group_term.label_contract_group_term_id)";
    } else {
        push @$joins,
"LEFT JOIN label_contract_group_term ON (label_contract_group_term_exception.label_contract_group_term_id = label_contract_group_term.label_contract_group_term_id)";
        push @$joins,
"LEFT JOIN label_contract_group_term_label ON (label_contract_group_term_label.label_contract_group_term_id = label_contract_group_term.label_contract_group_term_id)";
        push @$joins, "LEFT JOIN service ON (label_contract_group_term_exception.service_id = service.service_id)";
    }

    push @$joins, "LEFT JOIN label_contract ON (label_contract_group_term.label_contract_id = label_contract.label_contract_id)";
    push @$joins, "LEFT JOIN label ON (label_contract_group_term_label.label_id = label.label_id)";
    push @$joins, "LEFT JOIN label_payee ON (label_payee.label_payee_id = label_contract.label_payee_id)";
    push @$joins, "LEFT JOIN payor ON (label_contract.payor_id = payor.payor_id)";
}

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_contract.$dateType) >= TO_DAYS(" . $class->quote($startDate) . ")";
        }
        if ($endDate) {
            push @$where, "TO_DAYS(label_contract.$dateType) <= TO_DAYS(" . $class->quote($endDate) . ")";
        }
    }
}

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

1;