#!/usr/bin/perl

use lib '/app/tools/common/lib';
use lib '/app/tools/data_classes/lib';
use Data::Dumper;

use Common::RSDB;
use Client::Service;

use strict;
use warnings;

use DBI;
use Getopt::Long;

# database key of RSCOMMON database in RSDB.pm
my $rscommon = 0;

# database handle for RSCOMMON.
my $rscommonDBH;

# database handle for other customer tables.
my $dbh;

# variables for command options.

my $customerId;
my $customerName;

my $isPersist;
my $isPrintHistory;
my $reportDate;
my $fileLevel;

# stores previous and current revenue.

my %currentRevenue = ();

# stores revenue on service level.

my %serviceLevel = ();

# stores existing report.

my %existingReport = ();

# stores full customer names.

my %fullCustomerNames = ();

# program starts here.

# gets command options.
getOptions();

# gets db/host information of RSCOMMON.
my ( $host, $dbname, $user, $pwd ) = getRsCommonHostInfo();

# connects to RSCOMMON database.
$rscommonDBH = DBI->connect( "DBI:mysql:host=$host:database=$dbname", $user, $pwd, { 'RaiseError' => 1 } );

# gets all full customer names, rather then clean name in
# Common/Const.pm.
getFullCustomerNames();

# displays report history.
if ( defined $isPrintHistory ) {
    printHistory();
    exit;
}

# displays existing report by report date.
if ( defined $reportDate ) {
    # No longer supported
    #outputExistingReport($reportDate);
    exit;
}

# if specifies a customer by ID and name, dulipcate, tool stops.
if ( ( defined $customerId ) && ( defined $customerName ) ) {
    print "Please specify ID only or specify name only.\n";
    exit;
}

# if no customer specified, generates report against all customers.
elsif ( !( ( defined $customerId ) || ( defined $customerName ) ) ) {
    if ( ( defined $isPersist ) && isPersistedToday() ) {
        print "Can not persist twice in a given day.\nTo view report, remove \"-p\" option.\n";
        exit();
    }

    # prints report header.
    printReportHeaderByTab();

    foreach my $key ( ( sort sortIdByCustomerName ( keys %Common::RSDB::CLIENT_DB ) ) ) {
        if ( $key != 0 ) {
            next
              if ( $Common::RSDB::CLIENT_DB{$key}{db_name} !~ m/^C_/
                || $Common::RSDB::CLIENT_DB{$key}{db_name} =~ m/C_WARNER|C_SONY|C_DEMO/ );
            $customerId = $key;
            generateReport();
        }
    }
}

# else generates report for the specified customer.
else {
    synchCustomerNameAndId( $customerId, $customerName );

    if ( ( defined $isPersist ) && isPersistedToday() ) {
        print "Can not persist twice in a given day.\nTo view report, remove \"-p\" option.\n";
        exit();
    }

    # prints report header.
    printReportHeaderByTab();

    generateReport();
}

# disconnects from RSCOMMON.
$rscommonDBH->disconnect();

# Below are sub routines invoked above.

# gets command options.

sub getOptions {

    # p - persist to database.
    # i - specify customer by id.
    # n - specify customer by name.
    # d - specify report date to get existing report.
    # f - output report on file level.
    # a - output report history.
    GetOptions(
        "persist|p" => \$isPersist,
        "id|i=i"    => \$customerId,
        "name|n=s"  => \$customerName,
        "date|d:s"  => \$reportDate,
        "file|f"    => \$fileLevel,
        "all|a"     => \$isPrintHistory
    ) or exit;
}

# sorts customer ID by its customer name.

sub sortIdByCustomerName {

    # filters out RSCOMMON database.
    if ( $a == 0 || $b == 0 ) {
        return 1;
    }

    my $name1 = "";
    my $name2 = "";

    # makes sure all customer have a name.
    if ( defined $fullCustomerNames{$a} ) {
        $name1 = $fullCustomerNames{$a};
    }

    if ( defined $fullCustomerNames{$b} ) {
        $name2 = $fullCustomerNames{$b};
    }

    return $name1 cmp $name2;
}

# generates report.

sub generateReport {

    # gets db/host information.
    my ( $host, $dbname, $user, $pwd ) = getHostInfo( $customerId, $customerName );

    if ($host) {

        # connects to the database.
        $dbh = DBI->connect( "DBI:mysql:host=$host:database=$dbname", $user, $pwd ) or return;

        # produces report.
        produceReport();

        # disconnects from the database.
        $dbh->disconnect();
    }
}

# gets by db/host information by id or name,
# there must be one correct.
sub getHostInfo {
    synchCustomerNameAndId( $customerId, $customerName );

    if ( !( $customerId && $customerName ) ) {
        return undef;
    }

    return $Common::RSDB::CLIENT_DB{$customerId}{"server"},
      $Common::RSDB::CLIENT_DB{$customerId}{"db_name"},
      $Common::RSDB::CLIENT_DB{$customerId}{"username"},
      $Common::RSDB::CLIENT_DB{$customerId}{"password"};
}

# synch up customer id and its name from command options.
sub synchCustomerNameAndId {
    my $id   = shift;
    my $name = shift;

    # must have one of specified then goes ahead.
    if ( $id || $name ) {

        # searches by name if ID is not presented.
        if ( !$id ) {
            while ( my ( $tempId, $tempName ) = each %fullCustomerNames ) {
                if ( $tempName eq $name ) {
                    $id = $tempId;
                    last;
                }
            }
        }

        if ( !$id || !exists( $Common::RSDB::CLIENT_DB{$id} ) ) {
            die "Specified customer $name($id) does not exist.\n";
        }

        if ( $fullCustomerNames{$id} ) {
            $customerId   = $id;
            $customerName = $fullCustomerNames{$id};
        } else {
            warn "No client name for customer $id\n";
            $customerId   = $id;
            $customerName = undef;
        }
    }
}

# gets db/host information for RSCOMMON.
sub getRsCommonHostInfo {
    return $Common::RSDB::CLIENT_DB{$rscommon}{"server"},
      $Common::RSDB::CLIENT_DB{$rscommon}{"db_name"},
      $Common::RSDB::CLIENT_DB{$rscommon}{"username"},
      $Common::RSDB::CLIENT_DB{$rscommon}{"password"};
}

# prints out report history.

sub printHistory {
    synchCustomerNameAndId( $customerId, $customerName );

    my $sql = "select distinct report_date, date_created 
     from revenue_summary ";

    # filters by specify id, if there is.
    if ($customerId) {
        $sql = $sql . " where customer_id=$customerId";
    }
    my $sth = $rscommonDBH->prepare( $sql . " order by date_created desc;" )
      || die $DBI::err . ": " . $DIB::errstr;
    $sth->execute() || die $DBI::err . ": " . $DIB::errstr;

    # prints out result

    my $index  = 1;
    my $output = "";
    while ( my $ref = $sth->fetchrow_hashref() ) {
        $output = $output . $index . "\t" . $ref->{'report_date'} . "\t" . $ref->{'date_created'} . "\n";
        $index  = $index + 1;
    }

    $sth->finish();

    if ( $index == 1 ) {
        print "No history found.\n";
    } else {
        print "Seq.\tReport Date\tReport Created Time\n" . $output;
    }
}

# main sub routines to generate report.

sub produceReport {

    # gets out current revenue to %currentRevenue.
    getCurrentRevenue();

    # gets history data to update previous and billable.
    updateCurrentRevenue();

    # prints out report.
    outputReport();

    if ( defined $isPersist ) {
        persistReport();
    }

    # removes revenues of current customer.
    %currentRevenue = ();
}

# gets out current revenue.

sub getCurrentRevenue {

    # Process files that are either all physical or all digital sales.
    processNormalFiles();

    # Process files that could have both physical and digital sales combined.
    processCombinedFiles();

    # adds children files' reveneue to its top parent's.
    aggregateChildFile();   
}

# Processes files that are either all physical or all digital sales

sub processNormalFiles {
    # notices the business logic.
    #
    # if file type is physical, the revenue is aggregated
    # by sale.total_revenue.
    # else it is aggregated by sale.units * sale.price.
    #
    # !!! file.physical = 2 means the file can contain both
    # digital and physical sales.
    # We'll have to deal with those separately.
    #
    # both of them need multiple sale.conversion_rate and
    # dist_fee_pct.
    #
    # And more only "Matched Sales" and "Permannent Exceptions"
    # are aggregated above.

    my $sql = "select file.file_id, file.parent_file_id,
             file.period_id, ifnull(file.service_id, 0) as service_id, 
             ifnull(file.orig_file_name, '') as file_name, 
             if (file.type_id=5, 2, ifnull(file.physical, 0)) as physical,
             ifnull(
               sum(
                 if (file.physical=1 || file.type_id=5, 
                   sale.total_revenue, 
                   sale.units * sale.price 
                 )
                 * sale.conversion_rate 
                 * if(dist.dist_fee_pct is not null, 
                  (100-dist.dist_fee_pct)/100, 1)
               ), 
               0
             ) as `current`
             from file file
             left join sale sale
               on file.file_id=sale.file_id
               and (
                 (sale.product_id is not null 
                   and sale.product_id!=0
                 ) 
                 or sale.import_status=8
                 or file.type_id=5
               )
             left join user_input_dist_fee dist 
               on sale.file_id=dist.file_id 
               and sale.format_type=dist.associated_format
             where file.file_status in (4,5) 
             AND file.physical != 2
             group by file.file_id;";

    my $sth = $dbh->prepare($sql)
      || die $DBI::err . ": " . $DIB::errstr;
    $sth->execute() || die $DBI::err . ": " . $DIB::errstr;

    # stores current revenue.
    while ( my $ref = $sth->fetchrow_hashref() ) {        
        storeCurrentRevenue($ref);
    }
    $sth->finish();
}

# Process files that could have both physical and digital sales combined.

sub processCombinedFiles {
    # First let's process the physical sales.
    my $sql = "select file.file_id, file.parent_file_id,
             file.period_id, ifnull(file.service_id, 0) as service_id, 
             ifnull(file.orig_file_name, '') as file_name, 
             1 as physical,
             ifnull(
               sum(
                 sale.total_revenue
                 * sale.conversion_rate 
                 * if(dist.dist_fee_pct is not null, 
                  (100-dist.dist_fee_pct)/100, 1)
               ), 
               0
             ) as `current`
             from file file
             left join sale sale
               on file.file_id=sale.file_id
               and (
                 (sale.product_id is not null 
                   and sale.product_id!=0
                 ) 
                 or sale.import_status=8
               )
             left join user_input_dist_fee dist 
               on sale.file_id=dist.file_id 
               and sale.format_type=dist.associated_format
             where file.file_status in (4,5) 
             AND file.physical = 2
             AND sale.product_type NOT IN ('T','A')
             group by file.file_id;";

    my $sth = $dbh->prepare($sql)
      || die $DBI::err . ": " . $DIB::errstr;
    $sth->execute() || die $DBI::err . ": " . $DIB::errstr;

    # stores current revenue.
    while ( my $ref = $sth->fetchrow_hashref() ) {        
        storeCurrentRevenue($ref);
    }
    
    # Now let's process the digital sales.
    $sql = "select file.file_id, file.parent_file_id,
             file.period_id, ifnull(file.service_id, 0) as service_id, 
             ifnull(file.orig_file_name, '') as file_name, 
             0 as physical,
             ifnull(
               sum(
                 sale.units * sale.price
                 * sale.conversion_rate 
                 * if(dist.dist_fee_pct is not null, 
                  (100-dist.dist_fee_pct)/100, 1)
               ), 
               0
             ) as `current`
             from file file
             left join sale sale
               on file.file_id=sale.file_id
               and (
                 (sale.product_id is not null 
                   and sale.product_id!=0
                 ) 
                 or sale.import_status=8
               )
             left join user_input_dist_fee dist 
               on sale.file_id=dist.file_id 
               and sale.format_type=dist.associated_format
             where file.file_status in (4,5) 
             AND file.physical = 2
             AND sale.product_type IN ('T','A')
             group by file.file_id;";

    $sth = $dbh->prepare($sql)
      || die $DBI::err . ": " . $DIB::errstr;
    $sth->execute() || die $DBI::err . ": " . $DIB::errstr;

    # stores current revenue.
    while ( my $ref = $sth->fetchrow_hashref() ) {        
        storeCurrentRevenue($ref);
    }
        
    $sth->finish();
}

# Adds revenue to %currentRevenue

sub storeCurrentRevenue {
    my $ref = shift;
    
    my $parentFileKey;
    if (defined $ref->{'parent_file_id'}) {
        $parentFileKey = $ref->{'parent_file_id'}."-".$ref->{'physical'};
    }
    
    $currentRevenue{ $ref->{'file_id'}."-".$ref->{'physical'} } = [
        $ref->{'file_id'},  $parentFileKey, $ref->{'period_id'},
        $ref->{'physical'}, $ref->{'service_id'},     $ref->{'file_name'},

        # following two elements are place holder for previous and
        # billable. They will be updated later.
        0, $ref->{'current'}, $ref->{'current'}
    ];    
}

# Removes child files and adds their revenue to its top parent's

sub aggregateChildFile {

    # stores chidren file's file ID, will be deleted later.
    my @childs = ();

    foreach my $key ( keys %currentRevenue ) {

        # gets parent file ID.
        my $parent = $currentRevenue{$key}->[1];
        my $top;

        # gets valid top parent file ID in a loop.
        while ( $parent && exists( $currentRevenue{$parent} ) ) {
            $top    = $parent;
            $parent = $currentRevenue{$parent}->[1];
        }

        # adds revenue to its top parent ID,
        # then marks to be deleted itself.
        if ($top) {

            # current column.
            $currentRevenue{$top}->[7] =
              $currentRevenue{$top}->[7] + $currentRevenue{$key}->[7];

            # billable column.
            $currentRevenue{$top}->[8] =
              $currentRevenue{$top}->[8] + $currentRevenue{$key}->[8];

            push( @childs, $key );
        }
    }

    # deletes files marked to be deleted.
    for ( my $i = 0 ; $i < @childs ; $i++ ) {
        delete $currentRevenue{ $childs[$i] };
    }
}

#  updates previous revenue and billable with history data.

sub updateCurrentRevenue {

    # gets most latest current revenue as previous
    # revenue. max(summary_id) selects out most latest one.
    # Notes if current is zero. That means the file is
    # deleted when last report run. It is filtered out.
    my $sql = "select fs.file_id, fs.period_id,
             fs.service_id, 
             fs.physical, 
             fs.file_name,
             fs.current as previous
             from 
             (select file_id, 
               max(summary_id) as summary_id 
               from revenue_summary
               where customer_id=$customerId
               group by file_id, physical
             ) as temp
             left join revenue_summary fs 
               on temp.summary_id=fs.summary_id
             where fs.current != 0 ";

    my $sth = $rscommonDBH->prepare($sql)
      || die $DBI::err . ": " . $DIB::errstr;
    $sth->execute() || die $DBI::err . ": " . $DIB::errstr;

    # update previous revenue to current revenue.
    while ( my $ref = $sth->fetchrow_hashref() ) {

        my $fileKey;
        if (defined $ref->{'file_id'}) {
            $fileKey = $ref->{'file_id'} . "-";
            
            if (defined $ref->{'physical'}) {
                $fileKey .= $ref->{'physical'};
            } else {
                $fileKey .= "0";
            }
        }
        
        # if file exists, updated previous revenue and billable.
        if ( exists( $currentRevenue{ $fileKey } ) ) {
            $currentRevenue{ $fileKey }->[6] = $ref->{'previous'};
            $currentRevenue{ $fileKey }->[8] =
            $currentRevenue{ $fileKey }->[7] - $currentRevenue{ $fileKey }->[6];
        }

        # not exists means the file is deleted. Adds to current
        # with zero current revenue.
        else {
            $currentRevenue{ $fileKey } = [
                $ref->{'file_id'},

                # parent file id sets to 0.
                0,                    $ref->{'period_id'}, $ref->{'physical'},
                $ref->{'service_id'}, $ref->{'file_name'},

                # current revenue is zero.
                $ref->{'previous'}, 0, 0 - $ref->{'previous'}
            ];
        }
    }
    $sth->finish();
}

# output report.

sub outputReport {

    # aggregates to service level or output in file level.
    if ( !defined $fileLevel ) {
        %serviceLevel = fileLevelToServiceLevel();
        foreach my $key ( sort sortReportonServiceLevel ( keys %serviceLevel ) ) {
            my $type;
            if ( 2 == $serviceLevel{$key}->[0] ) {
                $type = 'License Income';
            } elsif ( 1 == $serviceLevel{$key}->[0] ) {
                $type = 'Physical';
            } else {
                $type = 'Digital';
            }

            printReportByTab(
                $customerName, $type,
                Client::Service::GetDefaultServiceName( service_id => $serviceLevel{$key}->[1] ),
                $serviceLevel{$key}->[2],
                $serviceLevel{$key}->[3],
                $serviceLevel{$key}->[4],
                $serviceLevel{$key}->[5]
            );
        }
    } else {

        # outputs in a loop.
        foreach my $key ( sort sortReportonFileLevel ( keys %currentRevenue ) ) {
            my $type;
            if ( 2 == $currentRevenue{$key}->[3] ) {
                $type = 'License Income';
            } elsif ( 1 == $currentRevenue{$key}->[3] ) {
                $type = 'Physical';
            } else {
                $type = 'Digital';
            }

            printReportByTab(
                $customerName, $type,
                Client::Service::GetDefaultServiceName( service_id => $currentRevenue{$key}->[4] ),
                $currentRevenue{$key}->[5],
                $currentRevenue{$key}->[6],
                $currentRevenue{$key}->[7],
                $currentRevenue{$key}->[8]
            );
        }
    }
}

# sorts report output in file level.
sub sortReportonServiceLevel {

    # by type.
    if ( $serviceLevel{$a}->[0] == $serviceLevel{$b}->[0] ) {

        # by service name.
        if ( $serviceLevel{$a}->[1] == $serviceLevel{$b}->[1] ) {

            # by file name.
            return $serviceLevel{$a}->[2] cmp $serviceLevel{$b}->[2];
        } else {
            my $temp1 = "";
            my $temp2 = "";

            if ( defined Client::Service::GetDefaultServiceName( service_id => $serviceLevel{$a}->[1] ) ) {
                $temp1 = Client::Service::GetDefaultServiceName( service_id => $serviceLevel{$a}->[1] );
            }

            if ( defined Client::Service::GetDefaultServiceName( service_id => $serviceLevel{$b}->[1] ) ) {
                $temp2 = Client::Service::GetDefaultServiceName( service_id => $serviceLevel{$b}->[1] );
            }

            return $temp1 cmp $temp2;
        }
    } else {
        return $serviceLevel{$a}->[0] cmp $serviceLevel{$b}->[0];
    }
}

# sorts report output in service level.
sub sortReportonFileLevel {

    # by type.
    if ( $currentRevenue{$a}->[3] == $currentRevenue{$b}->[3] ) {

        # by service name.
        if ( $currentRevenue{$a}->[4] == $currentRevenue{$b}->[4] ) {

            # by file name.
            return $currentRevenue{$a}->[5] cmp $currentRevenue{$b}->[5];
        } else {
            my $temp1 = "";
            my $temp2 = "";

            if ( defined Client::Service::GetDefaultServiceName( service_id => $currentRevenue{$a}->[4] ) ) {
                $temp1 = Client::Service::GetDefaultServiceName( service_id => $currentRevenue{$a}->[4] );
            }

            if ( defined Client::Service::GetDefaultServiceName( service_id => $currentRevenue{$b}->[4] ) ) {
                $temp2 = Client::Service::GetDefaultServiceName( service_id => $currentRevenue{$b}->[4] );
            }

            return $temp1 cmp $temp2;
        }
    } else {
        return $currentRevenue{$a}->[3] cmp $currentRevenue{$b}->[3];
    }
}

# sorts existing report.

sub sortExitingReport {

    # by customer.
    if ( $existingReport{$a}->[0] eq $existingReport{$b}->[0] ) {

        # by type.
        if ( $existingReport{$a}->[1] eq $existingReport{$b}->[1] ) {

            # by service name.
            my $temp1 = "";
            my $temp2 = "";

            if ( defined $existingReport{$a}->[2] ) {
                $temp1 = $existingReport{$a}->[2];
            }

            if ( defined $existingReport{$b}->[2] ) {
                $temp2 = $existingReport{$b}->[2];
            }

            # by file name.
            if ( $temp1 eq $temp2 ) {
                return $existingReport{$a}->[3] cmp $existingReport{$b}->[3];
            } else {
                return $temp1 cmp $temp2;
            }
        } else {
            return $existingReport{$a}->[1] cmp $existingReport{$b}->[1];
        }
    } else {
        return $existingReport{$a}->[0] cmp $existingReport{$b}->[0];
    }
}

# aggregates from file level to service level.

sub fileLevelToServiceLevel {

    # cleare serviceLevel hash.
    %serviceLevel = ();

    # adds revenue together group by service id.
    my $serviceId;
    my $physical;
    foreach my $key ( keys %currentRevenue ) {
        
        # We need to take 'physical' into account here.
        $serviceId = $currentRevenue{$key}->[4];
        $physical  = $currentRevenue{$key}->[3];
        if (!$physical) {
            $physical = "0";
        }
        
        my $serviceKey = $serviceId . "-" . $physical;

        # if exists in serviceLevel hash, adds to it.
        if ( exists( $serviceLevel{$serviceKey} ) ) {
            $serviceLevel{$serviceKey}->[3] =
              $serviceLevel{$serviceKey}->[3] + $currentRevenue{$key}->[6];
            $serviceLevel{$serviceKey}->[4] =
              $serviceLevel{$serviceKey}->[4] + $currentRevenue{$key}->[7];
            $serviceLevel{$serviceKey}->[5] =
              $serviceLevel{$serviceKey}->[5] + $currentRevenue{$key}->[8];
        }

        # adds a new key/value.
        else {
            $serviceLevel{$serviceKey} = [
                $currentRevenue{$key}->[3], $serviceId,                 " ",
                $currentRevenue{$key}->[6], $currentRevenue{$key}->[7], $currentRevenue{$key}->[8]
            ];
        }
    }

    return %serviceLevel;
}

# persist reports to RSCOMMON database.

sub persistReport {
    my $sql;
    my $timestamp = "";

    # gets out timestamp first.
    my $sth = $rscommonDBH->prepare("select now() as now;");
    $sth->execute();

    # update previous revenue to current revenue.
    while ( my $ref = $sth->fetchrow_hashref() ) {
        $timestamp = $rscommonDBH->quote( $ref->{"now"} );
    }

    # persists to file_summary table.
    my $fileName;
    foreach my $key ( keys %currentRevenue ) {
        $fileName = $rscommonDBH->quote( $currentRevenue{$key}->[5] );

        $sql = "insert into revenue_summary (customer_id, 
                 file_id, period_id, physical, service_id, 
                 file_name, previous, current, billable, 
                 date_created, report_date)           
               values($customerId, $currentRevenue{$key}->[0],
                 $currentRevenue{$key}->[2],
                 $currentRevenue{$key}->[3],
                 $currentRevenue{$key}->[4],
                 $fileName,
                 $currentRevenue{$key}->[6],
                 $currentRevenue{$key}->[7],
                 $currentRevenue{$key}->[8],
                 $timestamp, curdate());";

        $rscommonDBH->do($sql);
    }
}

# checks if the report is persisted today.

sub isPersistedToday {
    my $sql = "select count(*) as count from revenue_summary 
                                   where report_date=curdate() ";

    # if customer id is specified, adds to as condition.
    if ($customerId) {
        $sql = $sql . " and customer_id=$customerId ";
    }

    # gets out timestamp first.
    my $sth = $rscommonDBH->prepare($sql);

    $sth->execute();

    # update previous revenue to current revenue.
    while ( my $ref = $sth->fetchrow_hashref() ) {
        if ( $ref->{"count"} > 0 ) {
            return 1;
        }
    }

    return 0;
}

# outputs existing report by date or by time stamp.

sub outputExistingReport {
    my $dateTime = shift;

    my $sql = "select client.client_name as Customer, 
             if(summary.physical=1, 'Physical', if(summary.physical=2, 'License Income', 'Digital'))
               as Type, 
             summary.service_id as Service, ";

    # if not specified file level in command option,
    # needs group by service id.
    if ( defined $fileLevel ) {
        $sql = $sql . "summary.file_name as Filename, 
             ifnull(summary.previous, 0) as Previous,
             summary.current as Current,
             summary.billable as Billable ";
    } else {
        $sql = $sql . "' ' as Filename, 
             sum(ifnull(summary.previous, 0)) as Previous,
             sum(summary.current) as Current,
             sum(summary.billable) as Billable ";
    }

    $sql = $sql . " from (select max(summary_id) as summary_id
                      from revenue_summary summary where 1=1 ";

    synchCustomerNameAndId( $customerId, $customerName );

    # adds customer id as condition if there is.
    # if date is not specified prints out latest report.
    # Actually use date_created column to select out latest
    # report since report may run more than once a day.
    if ( $dateTime eq '' ) {
        if ($customerId) {
            $sql = $sql . " and summary.customer_id=$customerId ";
        }
    } else {
        if ($customerId) {
            $sql = $sql . " and summary.customer_id=$customerId
                    and report_date='$dateTime'  ";
        }
    }

    $sql = $sql . " group by file_id, customer_id) latest
                left join revenue_summary summary
                  on latest.summary_id=summary.summary_id
                left join client client
                  on client.client_id=summary.customer_id ";

    if ( !defined $fileLevel ) {
        $sql = $sql . " group by summary.service_id, client.client_id";
    }

    getReport($sql);
}

# gets existing report and outputs.
sub getReport {
    my $sql = shift;

    my $sth = $rscommonDBH->prepare($sql)
      || die $DBI::err . ": " . $DIB::errstr;
    $sth->execute() || die $DBI::err . ": " . $DIB::errstr;

    # prints out result

    printReportHeaderByTab();

    my $index = 1;
    while ( my $ref = $sth->fetchrow_hashref() ) {
        $existingReport{$index} = [
            $ref->{'Customer'}, $ref->{'Type'}, Client::Service::GetDefaultServiceName( service_id => $ref->{'Service'} ),
            $ref->{'Filename'}, $ref->{'Previous'}, $ref->{'Current'}, $ref->{'Billable'}
        ];

        $index = $index + 1;
    }
    $sth->finish();

    if ( $index == 1 ) {
        print "No report found.\n";
    } else {

        # outputs in a loop.
        foreach my $key ( sort sortExitingReport ( keys %existingReport ) ) {
            printReportByTab(
                $existingReport{$key}->[0], $existingReport{$key}->[1], $existingReport{$key}->[2], $existingReport{$key}->[3],
                $existingReport{$key}->[4], $existingReport{$key}->[5], $existingReport{$key}->[6]
            );
        }
    }
}

# gets full customer names.
sub getFullCustomerNames {
    my $sql = "select client_id, client_name from client";

    my $sth = $rscommonDBH->prepare($sql)
      || die $DBI::err . ": " . $DIB::errstr;
    $sth->execute() || die $DBI::err . ": " . $DIB::errstr;

    while ( my $ref = $sth->fetchrow_hashref() ) {
        $fullCustomerNames{ $ref->{'client_id'} } = $ref->{'client_name'};
    }
    $sth->finish();
}

# prints out hearder.

sub printReportHeaderByTab {
    print "Customer\tType\tService\tRS Customer ID\tPrevious\tCurrent\tBillable\n";
}

# prints reports as tab seperated.

sub printReportByTab {
    my $name     = shift;
    my $type     = shift;
    my $service  = shift;
    my $filename = shift;
    my $previous = sprintf( "%.2f", shift );
    my $current  = sprintf( "%.2f", shift );
    my $billable = sprintf( "%.2f", shift );

    if ( !defined $service ) {
        $service = "";
    }
    print "$name\t$type\t$service\t$customerId\t$previous\t$current\t$billable\n";
}