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

use strict;
use warnings;

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

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

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

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

    my $sql = "SELECT * FROM " . kTable . " ORDER BY service_name";
    return $class->GetAll($sql);
}

sub GetAllInPeriodSortedByName {
    my ( $class, $periodID ) = @_;
    assert( defined($periodID), "ERROR - periodID $periodID is required" );

    my $sql =
        "SELECT DISTINCT(service_name),service.service_id FROM "
      . kTable
      . " JOIN file ON file.service_id = service.service_id WHERE file_status = 4 and period_id = "
      . $class->quote($periodID)
      . " ORDER BY service_name";
    return $class->GetAll($sql);
}

sub GetAllInPeriodWithRevenue {
    my ( $class, $periodID ) = @_;
    assert( defined($periodID), "ERROR - periodID $periodID is required" );

    my $sql =
        "SELECT service.service_name AS service_name,ROUND(SUM(sale.revenue * sale.conversion_rate),2) AS revenue FROM "
      . kTable
      . " JOIN file ON file.service_id = service.service_id JOIN sale on sale.file_id = file.file_id WHERE period_id = "
      . $class->quote($periodID)
      . " GROUP BY file.service_id ORDER BY service_name";
    return $class->GetAll($sql);
}

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

    my $sql =
      'SELECT DISTINCT service.service_id, service.service_name FROM '
      . kTable
      . ' JOIN file ON file.service_id = service.service_id'
      . ' ORDER BY service.service_name';

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

1;