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

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::Expense;

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

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

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


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

    return
    {
        # Contract
        'artist_contract_title'     => {},
        'artist_name'               => {},
        'client_contract_id'        => {},
        'payor'                     => {},
        'payee'                     => {},
        'artist_client_account_id'  => {},
        'artist_payee_id'           => {},
        'date_issued'               => {},
        'term_start'                => {},
        'term_end'                  => {},
        'album_title'               => {},
        'catalog_number'            => {},
        'album_artist'              => {},
        'label_name'                => {},
        'track_title'               => {},
        'track_no'                  => {},
        'track_artist'              => {},
        'isrc'                      => {},
        'duration'                  => {},
        'prorate_track_count'       => {},
        'contract_status'           => {},
        'cross_collateralized'      => {},
        'deduction_type'            => {},
        'expense_type_name'         => {},
        'percent'                   => {},
        'amount'                    => {},
        'memo'                      => {},
        'processed'                 => {},
        'date_created'              => {},
        'created_by'                => {},
        'date_modified'             => {},
        'modified_by'               => {},
        'expense_id'                => {},
    };
}



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

    my $sql = $class->_AlbumContractSQL(%args) . ' UNION ' . $class->_TrackContractSQL(%args);

    $sql .= ' ORDER BY artist_contract_title, payee, album_title, catalog_number, track_no, date_created';
    Log->debug( "QUERY: $sql" );

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

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

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

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

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

    my @where;
    $class->_BuildAlbumWhere(\@where, %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);
    }

    return $sql;
}

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

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

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

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

    my @where;
    $class->_BuildTrackWhere(\@where, %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);
    }

    return $sql;
}

sub _BuildAlbumSelect
{
    my ($class, $select, %args) = @_;
    push @$select, 'new_artist_contract.title AS artist_contract_title';
    push @$select, 'artist_payee.name AS artist_name';
    push @$select, 'new_artist_contract.client_contract_id AS client_contract_id';
    push @$select, 'payor.name AS payor';
    push @$select, 'artist_payee.name AS payee';
    push @$select, 'artist_payee.client_account_id AS artist_client_account_id';
    push @$select, 'artist_payee.artist_payee_id AS artist_payee_id';
    push @$select, 'new_artist_contract.issue_date AS date_issued';
    push @$select, 'new_artist_contract.term_start AS term_start';
    push @$select, 'new_artist_contract.term_end AS term_end';
    push @$select, 'album.title AS album_title';
    push @$select, 'album.catalog_number AS catalog_number';
    push @$select, 'album_artist.name AS album_artist';
    push @$select, 'label.label_name AS label_name';
    push @$select, "'' AS track_title";
    push @$select, "'' AS track_no";
    push @$select, "'' AS track_artist";
    push @$select, "'' AS isrc";
    push @$select, "'' AS duration";
    push @$select, "'' AS prorate_track_count";
    push @$select, 'album_contract.status AS contract_status';
    push @$select, "IF(album_contract.cross_collateralized,'yes','no') AS cross_collateralized";
    push @$select, "IF(expense.pre_process,'Net Revenue Deduction','Album Expense') as deduction_type";
    push @$select, 'expense_name.name AS expense_type_name';
    push @$select, 'expense.percent AS percent';
    push @$select, 'expense.amount AS amount';
    push @$select, 'expense.memo AS memo';
    push @$select, "IF(expense.processed,'yes','no') as processed";
    push @$select, 'expense.date_created AS date_created';
    push @$select, 'expense.created_by AS created_by';
    push @$select, 'expense.date_modified AS date_modified';
    push @$select, 'expense.modified_by AS modified_by';
    push @$select, 'expense.expense_id AS expense_id';
}

sub _BuildTrackSelect
{
    my ($class, $select, %args) = @_;
    push @$select, "new_artist_contract.title AS artist_contract_title";
    push @$select, "artist_payee.name AS artist_name";
    push @$select, "new_artist_contract.client_contract_id AS client_contract_id";
    push @$select, "payor.name AS payor";
    push @$select, "artist_payee.name AS payee";
    push @$select, "artist_payee.client_account_id AS artist_client_account_id";
    push @$select, "artist_payee.artist_payee_id AS artist_payee_id";
    push @$select, "new_artist_contract.issue_date AS date_issued";
    push @$select, "new_artist_contract.term_start AS term_start";
    push @$select, "new_artist_contract.term_end AS term_end";
    push @$select, "album.title AS album_title";
    push @$select, "album.catalog_number AS catalog_number";
    push @$select, "album_artist.name AS album_artist";
    push @$select, "label.label_name AS label_name";
    push @$select, "track.title AS track_title";
    push @$select, "track.track_order AS track_no";
    push @$select, "track_artist.name AS track_artist";
    push @$select, "master.isrc AS isrc";
    push @$select, "master.duration AS duration";
    push @$select, "track_contract.prorate_track_count AS prorate_track_count";
    push @$select, "track_contract.status AS contract_status";
    push @$select, "IF(track_contract.cross_collateralized,'yes','no') AS cross_collateralized";
    push @$select, "IF(expense.pre_process,'Net Revenue Deduction','Track Expense') as deduction_type";
    push @$select, "expense_name.name AS expense_type_name";
    push @$select, "expense.percent AS percent";
    push @$select, "expense.amount AS amount";
    push @$select, "expense.memo AS memo";
    push @$select, "IF(expense.processed,'yes','no') as processed";
    push @$select, "expense.date_created AS date_created";
    push @$select, "expense.created_by AS created_by";
    push @$select, "expense.date_modified AS date_modified";
    push @$select, "expense.modified_by AS modified_by";
    push @$select, "expense.expense_id AS expense_id";
}


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

    push @$from, 'expense';
}

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

    push @$from, 'expense';
}

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

    push @$joins, "JOIN album_contract ON (expense.parent_id = album_contract.album_contract_id)";
    push @$joins, "JOIN new_artist_contract ON (album_contract.artist_contract_id = new_artist_contract.artist_contract_id)";
    push @$joins, "JOIN album ON (album_contract.album_id = album.album_id)";
    push @$joins, "JOIN label on (album.label_id = label.label_id)";
    push @$joins, "JOIN artist_payee ON (artist_payee.artist_payee_id = new_artist_contract.artist_payee_id)";
    push @$joins, "JOIN artist AS album_artist ON (album.artist_id = album_artist.artist_id)";
    push @$joins, "JOIN expense_type ON (expense_type.expense_type_id = expense.expense_type_id)";
    push @$joins, "JOIN expense_name ON (expense_name.expense_name_id = expense_type.expense_name_id)";
    push @$joins, "JOIN payor ON (new_artist_contract.payor_id = payor.payor_id)";
}


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

    push @$joins, "JOIN track_contract ON (expense.parent_id = track_contract.track_contract_id)";
    push @$joins, "JOIN new_artist_contract ON (track_contract.artist_contract_id = new_artist_contract.artist_contract_id)";
    push @$joins, "JOIN track ON (track_contract.track_id = track.track_id)";
    push @$joins, "JOIN album ON (album.album_id = track.album_id)";
    push @$joins, "JOIN label on (album.label_id = label.label_id)";
    push @$joins, "JOIN master ON (track.master_id = master.master_id)";
    push @$joins, "JOIN artist_payee ON (artist_payee.artist_payee_id = new_artist_contract.artist_payee_id)";
    push @$joins, "JOIN artist AS album_artist ON (album.artist_id = album_artist.artist_id)";
    push @$joins, "JOIN artist AS track_artist ON (track.artist_id = track_artist.artist_id)";
    push @$joins, "JOIN expense_type ON (expense_type.expense_type_id = expense.expense_type_id)";
    push @$joins, "JOIN expense_name ON (expense_name.expense_name_id = expense_type.expense_name_id)";
    push @$joins, "JOIN payor ON (new_artist_contract.payor_id = payor.payor_id)";
}

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

    my $type = $args{reportType};

    push @$where, "expense.parent_type=1";
    push @$where, "expense.processed=0" if 'Pending' eq $type;
    push @$where, "expense.processed=1" if 'Processed' eq $type;

    $class->_BuildDateWhere($where, %args);
}

sub _BuildDateWhere
{
    my($class, $where, %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.
        #
        if ($startDate)
        {
            push @$where, "TO_DAYS(expense.$dateType) >= TO_DAYS(" . $class->quote($startDate) . ")";
        }
        if ($endDate)
        {
            push @$where, "TO_DAYS(expense. $dateType) <= TO_DAYS(" . $class->quote($endDate) . ")";
        }                        
    }
}

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

    my $type = $args{reportType};
    push @$where, "expense.parent_type=2";
    push @$where, "expense.processed=0" if 'Pending' eq $type;
    push @$where, "expense.processed=1" if 'Processed' eq $type;

    $class->_BuildDateWhere($where, %args);
}

1;