package Raptor::DB::Item::Period;
use strict;
use warnings;
use Data::Dumper;

use lib '/app/tools/common/lib';
use Common::DB::Item;
use base 'Common::DB::Item';

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

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

    return $class->GetAll( 'SELECT * FROM ' . kTable . ' ORDER BY period_id' );
}

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

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT * FROM " . kTable . " WHERE period_id > 0 ";

    if ( $args{start_date} && $args{end_date} ) {
        $sql .= " AND end_date >= " . $dbo->DBQuote( $args{start_date} );
        $sql .= " AND end_date <= " . $dbo->DBQuote( $args{end_date} );
    }

    $sql .= " ORDER BY period_id ";

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

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

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT * FROM " . kTable . " WHERE period_id > 0 ";

    if ( $args{query} ) {
        $sql .= " AND end_date > " . $dbo->DBQuote( $args{query} );
    }

    $sql .= " ORDER BY period_id ";

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

# Search for all closed periods closed on or before supplied date.  Also no not
# report any if no periods have ever been closed.
sub SearchByDateNotClosed {
    my ( $class, %args ) = @_;

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

    my $sql = "SELECT * FROM (SELECT count(*) as total, ";

    if ( $args{query} ) {
        $sql .= ' count(if( end_date >= ' . $dbo->DBQuote( $args{query} ) . ', 1, \N )) as count';
    } else {
        $sql .= " count(*) as count ";
    }

    $sql .= " FROM " . kTable;
    $sql .= ") as subq WHERE count = 0 AND total > 0";

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

sub GetLatestPeriodDate {
    my $class = shift;
    my $sql   = "SELECT date_created FROM period WHERE period_id = (SELECT MAX(period_id) FROM period)";

    my $collection = $class->SUPER::GetAll($sql);

    my $row = $collection->next();

    return $row ? ( $row->{date_created} ) : ();
}

1;