#------------------------------------------------------------
# Copyright (C) 2010 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package Common::DB::Item::Billing;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::Assert;
use Common::Client;
use Common::Log;

use base 'Common::DB::Item';

use constant kTable => 'bp_revenue_summary';
use constant kDB    => Common::DB::Item::kCommonDB;

sub GetMetricsUpdate {
    my ( $self, $clientID ) = @_;

    my $sql = "SELECT bp_revenue_summary.file_id,
                bp_revenue_summary.period_id,
                bp_revenue_summary.service_id,
                bp_revenue_summary.file_name,
                IFNULL(bp_revenue_summary.current, 0) AS previous,
                IFNULL(bp_revenue_summary.current_units, 0) AS previous_units
        FROM
            (SELECT file_id,
                    max(summary_id) AS summary_id
             FROM bp_revenue_summary
             WHERE customer_id = $clientID
             AND credited = 0
             GROUP BY file_id
            ) AS temp
        LEFT JOIN bp_revenue_summary
        ON temp.summary_id = bp_revenue_summary.summary_id
        WHERE bp_revenue_summary.current_units != 0";

    my $coll = $self->GetAll($sql);
    return $coll;

}

sub CheckFilePresence {
    my ( $self, $fileID ) = @_;
    my $clientID = Common::RSApp::GetClientID();

    my $sql = "SELECT COUNT(*) FROM bp_revenue_summary 
                        WHERE file_id = $fileID
                        AND customer_id = $clientID
                        AND credited = 0";

    my $dbo = Common::RSApp::GetCommonDB();

    my $sth   = $dbo->DoCmd($sql);
    my $count = $sth->fetchrow();

    return $count;

}

sub GetPersistedTodayCount {
    my ( $self, $clientID ) = @_;

    my $sql = "SELECT COUNT(*) FROM bp_revenue_summary 
               WHERE report_date = curdate() ";

    if ($clientID) {

        $sql .= "AND customer_id = $clientID ";
    }

    my $dbo = Common::RSApp::GetCommonDB();

    my $sth = $dbo->DoCmd($sql);

    my $count = $sth->fetchrow();

    return $count;

}

sub GetReportHistory {
    my ( $self, $clientID ) = @_;
    assert( defined $clientID, "File ID required" );

    my $sql = "SELECT DISTINCT report_date FROM " . kTable . " WHERE customer_id = $clientID AND credited = 0";

    my $collection = $self->GetAll($sql);

    return $collection;

}

sub GetSingleFileData {
    my ( $self, $fileID ) = @_;
    assert( defined $fileID, "File ID required" );

    my $clientID = Common::RSApp::GetClientID();

    my $sql = "SELECT * FROM " . kTable . " WHERE customer_id = $clientID AND file_id = $fileID AND credited = 0";

    my $collection = $self->GetAll($sql);

    return $collection;

}

sub GetExistingReportData {
    my ( $self, $reportDate, $clientID ) = @_;

    assert( defined $reportDate, "Report date required" );
    assert( defined $clientID,   "Client ID required" );

    my $sql = "SELECT client.client_name                   AS client_name,
                summary.service_id                   AS service_id, 
                summary.period_id                    AS period_id, 
                summary.file_id                      AS file_id, 
                summary.file_name                    AS file_name, 
                IFNULL(summary.current_units, 0)     AS current_units, 
                IFNULL(ROUND(summary.current, 2), 0) AS current
        
          FROM 
                (SELECT MAX(summary_id)         AS summary_id
                    FROM bp_revenue_summary summary
                WHERE 1 = 1
                AND summary.customer_id = $clientID
                AND report_date = '$reportDate'
                AND credited = 0
                GROUP BY summary.service_id, summary.file_id) latest

          LEFT JOIN bp_revenue_summary summary
              ON latest.summary_id = summary.summary_id
          LEFT JOIN client client
              ON client.client_id = summary.customer_id";

    my $collection = $self->GetAll($sql);

    return $collection;

}

sub GetFileIDArray {
    my ($self) = @_;
    my $clientID = Common::RSApp::GetClientID();

    my $sql = "SELECT file_id
         FROM bp_revenue_summary
         WHERE customer_id = $clientID
         AND credited = 0";

    my $collection = $self->GetAll($sql);
    my @outArray;
    while ( my $row = $collection->next() ) {
        push @outArray, $row->{file_id};
    }

    return @outArray;
}

sub CreditDeletedFile {
    my ( $self, $fileID ) = @_;
    my $dbo      = Common::RSApp::GetCommonDB();
    my $clientID = Common::RSApp::GetClientID();

    my $sql = "UPDATE " . kTable . " SET credited = 1 WHERE file_id = $fileID ";
    $sql .= "AND customer_id = $clientID";

    $dbo->DoCmd($sql);

}

1;