#!/usr/bin/perl
# Generate report showing Orchard sales integration status
#
# createOrchardSalesReport.pl --makeexcel [-o /path/to/file] [-u userID] [-d debuglevel]
#
# where
#
#   --makeexcel if a flag indicating to generate an Excel report.  If omitted then a text
#      report will be sent to STDOUT instead of a filename. ** optional but required if want
#      to generate an Excel report.
#   -o specifies an output target.  ** optional
#      The target can be directory path or the output filename which can optionally be prefixed
#      with a destionation path.  If no path is specified then the current directory is used.
#      If the target is a directory then a default filename will be generated with a timestamp.
#   -u userID specifies a userID.  ** optional
#      If present, we'll include the user's initials (or their email account name if no first/last
#      name are present) in the filename.  This only affects the default filename.
#      This may be useful if multiple people can generate a report to a shared directory and you
#      need to track who is generating a given report.
#   -d controls the amount of debug output.  By default we suppress most of the messages.
#
# Return values:
# 1    Report successfully generated
# 100  Output directory does not exist
# 200  Output directory can't be written to
# 300  Internal error: the report definition hash is misconfigured
# 500  Internal error: invalid arguments detected
#
use strict;
use Getopt::Long;
use Data::Dumper;
use Time::HiRes qw ( time );
use Excel::Writer::XLSX;
use File::Spec;

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

use lib '/app/tools/appuser/lib';
use AppUser::DB::Item::User;

# Parse the command-line.
#
my %options;
parseCommandLine( \%options );

my $gClientID       = $options{clientID};
my $path            = $options{path};     # output filename (can be explicit path or we'll use current directory)
my $gMakeExcel      = $options{makeexcel};  # optional: if not specified then text report will be sent to stdout
my $gVerbosityLevel = $options{debug} || 1;
my $userID          = $options{userID};  # if set, we'll use the user's initials in the default filename

my $appSingleton = Common::RSApp->new( clientID => 0 );
my $cdbo         = Common::RSApp::GetCommonDB();

my $initials = '';
if ( $userID ) {
    my $u = AppUser::DB::Item::User->Lookup( user_id => $userID );
    $initials = substr( $u->first_name, 0, 1) . substr( $u->last_name, 0, 1);
    if( '' eq $initials || length($initials) < 2 ) {
        my( $email, $domain ) = split('@', $u->email );
        $initials = $email;
    }
}

my( $vol, $dir, $filename) = File::Spec->splitpath($path);

my $reportName;

# if the path was a directory, then filename isn't actually the filename
# so we'll use a default filename instead
#
if ( -d $path ) {
    undef $filename;
    $dir = $path;  # make sure we have the directory name
    $dir .= '/' if ( $dir !~ /\/$/ );
}
$dir = './' if ( !defined $dir || '' eq $dir );

my $timestamp = getLoggingTime();

$reportName = ( !defined $filename or '' eq $filename ) ? "report_orchard_integration_$timestamp" : $filename;
$reportName .= "_$initials" if ( '' ne $initials );

my $reportExcel = $dir . $reportName;
$reportExcel .= '.xlsx' if ( $reportName !~ /.xlsx$/ );


# print "### path($path): vol($vol)  dir($dir)  filename($filename) --> outputfile($reportExcel)\n";

# Reminder: if you're calling this script from another script, use "$?" to read status code
# We'll return 1 if this script was successfully run.
#
if ( ! -e $dir ) { # directory doesn't exist
    print "ERROR: Directory '$dir' does not exist\n";
    exit(200);
}

if ( ! -w $dir ) { # directory not writable
    print "ERROR: Unable to write to directory '$dir'\n";
    exit(100);
}



# ------------------------------
# Following are used for custom reports
#
use constant kExcelFontSize => 10;

my %gColumnFormat;
my %gRowIndex;   # indexed by report type  (replaces individual row counters, above)
my %gWorksheet;  # indexed by report type
my %gShowHeader; # indexed by report type
my %gFormat;     # indexed by format type

my %gReportDefinition = ( # Excel report defintions
    'orchard_integration' => { # TAB1
        'header' => [
            'client-id', 'client-name', 'account-id', 'contract-id', 'rps-filename', 'file-id', 'currency-code', 'revenue', 'units', 'records', 'date-created', 'error', 'import-notes', # 'catalog-integration'
         ],
        'format' => [
            'number',    'text',        'text',       'text',         'text',        'number',  'text',          'number',  'number', 'number', 'text',         'text',  'text'
         ]
    },
);

# From /app/tools/data_classes/File/File.pm
# use constant STATUS_NEW        => 1;
# use constant STATUS_PROCESSING => 2;
# use constant STATUS_INVALID    => 3;
# use constant STATUS_OPEN       => 4;
# use constant STATUS_CLOSED     => 5;
# use constant STATUS_IN_QUEUE   => 6;
# use constant STATUS_ON_HOLD    => 7;
my %fileStatusMap = (
    1 => 'NEW', 2 => 'PROCESSING', 3 => 'INVALID', 4 => 'OPEN', 5 => 'CLOSED', 6 => 'IN_QUEUE', 7 => 'ON_HOLD'
);

my %periodMap; # map of Orchard periodIDs to quarter and month 
my $sql = "SELECT period_id, year, quarter, month FROM orchard_period";
my $sth = $cdbo->DoCmd($sql);
while( my($id, $year, $quarter, $month) = $sth->fetchrow_array() ) {
    $periodMap{$id}{quarter} = $quarter;
    $periodMap{$id}{month}   = $month;
    $periodMap{$id}{year}    = $year;
}           
            
my %numberToMonth = (
    1 => 'Jan', 2 => 'Feb', 3 => 'Mar', 4 => 'Apr', 5 => 'May', 6 => 'Jun',
    7 => 'Jul', 8 => 'Aug', 9 => 'Sep', 10 => 'Oct', 11 => 'Nov', 12 => 'Dec'
);              

# ------------------------------

exit( _generateReport( filename => $reportExcel ) );

sub _getDatabases {
    my ($a) = @_;
    my $appSingleton = Common::RSApp->new_temporary( clientID => 202 );
    my $dbo          = Common::RSApp::GetClientDB();

    # Check which databases are present.  A non-production environment will
    # usually have C_RSTEST at a mininum along with an assortment of client
    # databases.  A production environment will have everything.
    #
    my $sql = "SHOW DATABASES";
    my $sth = $dbo->DoCmd($sql);
    while( my($name) = $sth->fetchrow_array() ) {
        $a->{$name} = undef;
    }
}

sub _generateReport {
    my (%args) = @_;
    my $reportExcel = $args{filename};
    my $outputdir   = $args{path} || '.';

    my $workbook;

    if ( $gMakeExcel ) {
        $workbook  = Excel::Writer::XLSX->new($reportExcel);

        # Setup the worksheet(s)
        #
        $gWorksheet{orchard_integration} = $workbook->add_worksheet('orchard integration');
        $gWorksheet{orchard_integration}->freeze_panes(1, 0);

        # Setup the global formats (used for formatting the header pane)
        # Any worksheet-specific formats will be created later within
        # each worksheet.
        #
        $gFormat{header} = $workbook->add_format();
        my $headerFillColor = $workbook->set_custom_color(23,216,216,216);
        $gFormat{header}->set_bg_color($headerFillColor);
        $gFormat{header}->set_bold();
        $gFormat{header}->set_size(kExcelFontSize);
        $gFormat{header}->set_text_wrap();
        $gFormat{header}->set_bottom(2); # continuous (weight 2)
        $gFormat{header}->set_left(1);   # continuous (weight 1)
        $gFormat{header}->set_right(1);  # continuous (weight 1)
        $gFormat{header}->set_top(1);    # continuous (weight 1)

        $gFormat{bold} = $workbook->add_format();
        $gFormat{bold}->set_bold();
        $gFormat{bold}->set_size(kExcelFontSize);
        #$gFormat{bold}->set_text_wrap();

        $gFormat{plain} = $workbook->add_format();
        $gFormat{plain}->set_size(kExcelFontSize);
        #$plainFormat->set_text_wrap();
    }

    # Before we start scanning clients, make sure we have their database.
    # This is primarily for non-production environments where databases come
    # and go.
    # Note: we could query each DB individually:
    #   SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'DBName'
    # but that would be a database query for each client.  Using 'show databases' allows
    # us to get what we need in one query.
    #
    my %localDatabases;
    _getDatabases( \%localDatabases );

    #
    # Workbook and worksheet created, now output data...
    #

    # Grab the active sales integration
    #
    my $sql = "SELECT client_id, account_id, user_id, status, "
        . "scan_schedule, start_period, ddex_source, ddex_dest "
        . "FROM orchard_client WHERE status=2 ";

    $sql .= "AND client_id=$gClientID " if ( $gClientID );

    $sql .= "ORDER BY client_id";

    my $sth = $cdbo->DoCmd($sql);
    while( my($clientID, $accountID, $userID, $status,
        $scanSchedule, $startPeriod, $ddexSource, $ddexDest ) = $sth->fetchrow_array() ) {

        # Skip the accountID 0 lines -- these are placeholders for Vector
        # integration and are not needed for the sales report status
        #
        next if ( !$accountID );

        # Sanity check - make sure we actually have the client database, otherwise
        # the Client->Lookup will hang things up while it tries to connect.  This
        # shouldn't happen in a production environment.
        #
        my $dbname = $Common::RSDB::CLIENT_DB{$clientID}{db_name};
        if ( !exists $localDatabases{$dbname} ) {
            _report( "WARNING: database '$dbname' not found - skipping", 2);
            next;
        }

        my $client     = Common::DB::Item::Client->Lookup( client_id => $clientID );
        my $clientName = $client->client_name;

        my $appSingleton = Common::RSApp->new_temporary( clientID => $clientID );
        my $cdbo         = Common::RSApp::GetCommonDB();
        my $dbo          = Common::RSApp::GetClientDB();

        my $reportType = 'orchard_integration';
        my @rowData;

        # the order of elements must match gReportDefinition

        push @rowData, $clientID;
        push @rowData, $clientName;
        push @rowData, $accountID;

        # Gather the integration status.  Provide the name of the most recent import, or an error message
        # (usually "invalid accounting period") if a file isn't ready yet.

        my $rpsFilename   = '';
        my $rpsFileID     = '';
        my $currencyCode  = '';
        my $revenue       = '';
        my $units         = '';
        my $records       = '';
        my $dateCreated   = '';
        my $dateProcessed = '';
        my $dateFinished  = '';
        my $rpsFetchNotes = '';
        my $contractID    = '';
        my $error         = 'NO';  # if a fileID is not available then surface this on the report

        # Note(s):
        # fileID 0 means that we haven't downloaded anything yet for the specified Orchard period, in which case
        # we'll show the orchard_report.notes for that period (usually the period isn't finalized in Workstation yet).
        # Otherwise we'll show the RPS file information in the report.
        #    RPS fileName (the filename _should_ include the Orchard period info in it)
        #    Fetcher notes (will include the RPS fileID and status if imported, or a message with current status)
        #
        my $sql = "SELECT report_id, period, name, file_id, notes FROM orchard_report WHERE client_id=$clientID AND account_id=$accountID ORDER BY period DESC LIMIT 1";
        my $sth = $cdbo->DoCmd($sql);
        while( my( $reportID, $period, $oName, $fileID, $notes ) = $sth->fetchrow_array() ) { 
            my $periodName;
            if ( $period =~ /^(\d+),/ ) { # comma separated list means quarterly report
                $periodName = 'Q' . $periodMap{$1}{quarter} . ' ' . $periodMap{$1}{year};
            } else {
                my $month = $periodMap{$period}{month};
                $periodName = $numberToMonth{ $month } . ' ' . $periodMap{$period}{year};
            }

            # Use the fileID (if defined) to gather more file information
            # NOTE: We're querying more info than what's being exposed in the Excel report ...
            #
            if ( $fileID ) { 
                my $sql = "SELECT orig_file_name, period_id, date_created, date_processed, date_finished, file_status,";
                $sql .= " currency_code, revenue, units, records";
                $sql .= " FROM file WHERE file_id=$fileID";
                my $sth = $dbo->DoCmd($sql);
                my($fileName, $periodID, $_dateCreated, $_dateProcessed, $_dateFinished, $_fileStatus, $_currencyCode, $_revenue, $_units, $_records) = $sth->fetchrow_array();
                my $fileStatus = $fileStatusMap{$_fileStatus};

                $rpsFilename   = $fileName;
                $rpsFileID     = $fileID;
                $currencyCode  = $_currencyCode;
                $revenue       = $_revenue;
                $units         = $_units;
                $records       = $_records;
                $dateCreated   = $_dateCreated;
                $dateFinished  = $_dateFinished;
                $rpsFetchNotes = "fileID $fileID is currently $fileStatus in period $periodID"; # override the orchard_report notes
            } else {
                $rpsFetchNotes = "($periodName) $notes";  # show the orchard_report notes pre-pended with Orchard period
                $error         = "YES";
            }
        }

        if ($rpsFilename =~ /_(\d+)\.tsv$/) {
            $contractID = $1;
        }

        push @rowData, $contractID;
        push @rowData, $rpsFilename;
        push @rowData, $rpsFileID;
        push @rowData, $currencyCode;
        push @rowData, $revenue;
        push @rowData, $units;
        push @rowData, $records;
        push @rowData, $dateCreated;
        push @rowData, $error;
        push @rowData, $rpsFetchNotes;

        $gRowIndex{$reportType} = outputData(
            report_type => $reportType,
            data        => \@rowData,
            workbook    => $workbook,
            # worksheet   => $worksheet,header_format => $headerFormat,
            # bold_format => $boldFormat, plain_format => $plainFormat,
        );


    }

    $workbook->close() if ( $workbook );

    undef %gFormat;
    undef %gRowIndex;
    undef %gColumnFormat;

    return 1;  # success
} # _generateReport

#
# Boring script stuff below...
#

# output data row, prepend header (once)
sub outputData {
    my ( %args ) = @_;

    my $reportType    = $args{report_type};
    my $rowDataRef    = $args{data};
    my $workbook      = $args{workbook};
    #my $worksheet     = $args{worksheet};
    my $worksheet     = $gWorksheet{$reportType};
    my $boldFormat    = $args{bold_format};
    my $plainFormat   = $args{plain_format};
    my $headerFormat  = $args{header_format};

    my $rowIndex      = $gRowIndex{$reportType};

    if ( !$gMakeExcel ) {

        # The non-Excel report is a tab-delimited file, with the first column
        # consisting of a report tag.  By saving all of the output to a text file,
        # you can then grep out the report tag to extract the desired report.
        # E.g.,  grep REPORT_BYSERVICE debug.txt | \
        #           sed -e 's/REPORT_BY_SERVICE:\t//' > report_earnings_by_service.txt
        #
        my $reportTag = uc $reportType;
        $reportTag =~ s/EARNINGS/REPORT_/;

        my $header = \@{$gReportDefinition{$reportType}{header}};

        print join("\t", $reportTag . ':', @$header) . "\n" if ( !$gShowHeader{$reportType}++ );

        print join("\t", $reportTag . ':', @$rowDataRef) . "\n";

    } else {

        if ( !$gShowHeader{$reportType}++ ) {

            $gRowIndex{$reportType} = 0;

            _report( "D: rt($reportType) calling printExcelHEader...", 2 );

            $gRowIndex{$reportType} = printExcelHeader(
                report_type => $reportType,
            );
        }


        $gRowIndex{$reportType} = printExcelRowData(
            report_type => $reportType,
            data        => $rowDataRef,
            workbook    => $workbook,
            worksheet   => $worksheet,
        );
    }

} # outputData

sub printExcelRowData {
    my ( %args ) = @_;
    my $reportType = $args{report_type};
    my $rowArray   = $args{data};
    my $workbook   = $args{workbook};
    if ( !exists $gReportDefinition{$reportType} ) {
        _report( "ERROR: invalid report type '$reportType' !!!!", 1 );
        exit(300);
    }

    my $worksheet  = $gWorksheet{$reportType};
    my $rowFormat = \@{$gReportDefinition{$reportType}{format}};
    my $rowIndex   = $gRowIndex{$reportType};

    my $col = 0;
    foreach my $fmt ( @$rowFormat ) {
        my $rowCell = @$rowArray[$col];

        my $cellType = ($fmt eq 'text') ? $fmt : 'number';

        if ( !exists($gColumnFormat{$reportType}{$col}) ) {
            $gColumnFormat{$reportType}{$col} = $workbook->add_format();
            $gColumnFormat{$reportType}{$col}->set_size(kExcelFontSize);

            if ( $fmt =~ /^number:(\w+)/ ) {
                my $format = $1;
                $gColumnFormat{$reportType}{$col}->set_num_format( $format );
            }
        }

        printField( row => $rowIndex, column => $col,
                    cell_data => $rowCell, cell_type => $cellType,
                    format => $gColumnFormat{$reportType}{$col}, worksheet => $worksheet );
        $col++;
    } # column loop
    $rowIndex++;
    return $rowIndex;
} # printRowData

sub printExcelHeader {
    my ( %args ) = @_;
    my $reportType   = $args{report_type};

    if ( !exists $gReportDefinition{$reportType} ) {
        _report( "ERROR: invalid report type '$reportType' !!!!", 1 );
        exit(300);
    }

    my $headerFormat  = $gFormat{header};
    my $worksheet     = $gWorksheet{$reportType};
    my $rowIndex      = $gRowIndex{$reportType};


    my $header = \@{$gReportDefinition{$reportType}{header}};
    _report("header = ". Dumper( $header ), 2 );
    my $col=0;
    foreach my $h ( @$header ) {
        printField( row => $rowIndex, column => $col,
                    cell_data => $h, cell_type => 'text',
                    format => $headerFormat, worksheet => $worksheet );
        $col++;
    }
    $rowIndex++;
    _report("  Done with header...", 2 );
    return $rowIndex;

} # printExcelHeader

#----------------------------------------------
# printField - output a cell to a spreadsheet
#----------------------------------------------

sub printField{
    my( %args ) = @_;

    my $row       = $args{row};
    my $column    = $args{column};
    my $data      = $args{cell_data};
    my $dataType  = $args{cell_type};
    my $format    = $args{format};
    my $worksheet = $args{worksheet};

    $data =~ s/\s*$//;

    # Output either text or a numerical value
    # Note: if a value is missing and the desired format is
    # number, we actually output it as text s.t. the cell appears
    # with an empty field instead of a zero.

    _report("  D: printField[$row, $column], data($data) dataType($dataType}",2);
    if ( defined $data ) {
        if( $data =~ m/(\D*)(\d+)(\D*)(\d*)/ and $dataType and $dataType eq 'number') {
            $worksheet->write_number($row, $column, $data, $format);
        } else {
            $data = Common::UTF8::Encode($data);
            $worksheet->write_string($row, $column, $data, $format);
        }
    } else {
        $data = Common::UTF8::Encode($data);
        $worksheet->write_string($row, $column, $data, $format);
    }

}#printField


sub parseCommandLine {
    my ($a) = @_;

    my $clientID;
    my $filename;
    my $makeexcel;
    my $userID;
    my $debug;

    if ( ! GetOptions(
        'o=s'        => \$filename, # for excel files
        'c=i'        => \$clientID,
        'debug|d=i'  => \$debug,
        'makeexcel'  => \$makeexcel,
        'userid|u=i' => \$userID,
    )) {
        _report( "An error has occurred while parsing arguments, aborting", 1 );
        exit(500);
    }

    $a->{clientID}  = $clientID;
    $a->{path}      = $filename;
    $a->{debug}     = $debug;
    $a->{makeexcel} = $makeexcel;
    $a->{userID}    = $userID;
}

sub getLoggingTime {

    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
    my $nice_timestamp = sprintf ( "%04d%02d%02d_%02d%02d%02d", $year+1900,$mon+1,$mday,$hour,$min,$sec);
    return $nice_timestamp;
}

sub usage {
    print STDERR "\nusage: $0 --makeexcel [--path /path/to/filename]\n";
}

sub _report {
    my ( $string, $verbosity ) = @_;
    $verbosity = 1 unless defined $verbosity;

    if ( $gVerbosityLevel >= $verbosity ) {
        print STDERR $string . "\n";
    }
}