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

use strict;
use warnings;

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

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

use constant kTable => 'sale';
use constant kDB    => Common::DB::Item::kClientDB;

sub GetAllMetrics {
    my $self = shift;

    my $sql = "SELECT 
           file.file_id                                     AS file_id,
           IFNULL(file.parent_file_id, 0)                   AS parent_file_id,
           file.period_id                                   AS period_id,
           file.orig_file_name                              AS file_name, 
           service.service_id                               AS service_id, 
           service.service_name                             AS service_name, 
           IFNULL(MAX(sale.date_end), 'N/A')                AS end_date, 
           IFNULL(file.date_created, 'N/A')                 AS date_received,
           IFNULL(period.name, 'N/A')                       AS period_name,
           IFNULL(period.end_date, 'N/A')                   AS period_end_date,
           IFNULL(SUM(sale.units), 0)                       AS current_units,
           IFNULL(ROUND(SUM(sale.revenue * 
                            sale.conversion_rate),2), 0)    AS current,
           curdate()                                        AS today
       FROM file
       JOIN sale ON file.file_id = sale.file_id 
       JOIN service ON file.service_id = service.service_id 
       JOIN period ON file.period_id = period.period_id 
       WHERE sale.revenue != 0
       AND file.file_status in (4,5)
       GROUP BY service.service_name, file.file_id";

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

}

sub FileIsDeleted {
    my $self = shift;
    my $id   = shift;

    my $sql = "SELECT file_id FROM file WHERE file_id = $id";

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

    # if size is 0, file has been deleted, so return true, else return false
    return $collection->size() == 0 ? 1 : 0;

}

sub PutSummaryUpdate {
    my (
        $self,          $clientID,       $fileID,           $periodID,        $serviceID,        $fileName, $previous_units,
        $current_units, $billable_units, $previous_revenue, $current_revenue, $billable_revenue, $credited
    ) = @_;

    my $adjustedFileName = $self->quote($fileName);

    $previous_revenue = defined $previous_revenue ? $previous_revenue : 0;
    $current_revenue  = defined $current_revenue  ? $current_revenue  : 0;
    $billable_revenue = defined $billable_revenue ? $billable_revenue : 0;

    my $sql = "INSERT INTO bp_revenue_summary 
            ( customer_id,
              file_id, 
              period_id, 
              service_id,
              file_name, 
              previous_units, 
              current_units, 
              billable_units, 
              previous, 
              current, 
              billable,
              date_created,
              report_date,
              credited
            )
         VALUES ( $clientID,
                  $fileID,
                  $periodID,
                  $serviceID,
                  $adjustedFileName,
                  $previous_units,
                  $current_units,
                  $billable_units,
                  $previous_revenue,
                  $current_revenue,
                  $billable_revenue,
                  curdate(),
                  curdate(),
                  $credited
                )";

    Common::Log::Debug("QUERY: $sql");

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

    $dbo->DoCmd($sql);

    return;

}

1;