#!/usr/bin/perl
use strict;

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

gen_exception_report();

sub gen_exception_report {
    my $clientID = $ARGV[0] || return usage("missing client id");
    my $periodID = $ARGV[1] || return usage("missing period id");
    my $fileName = $ARGV[2];    ## optional

    ## output to file
    if ( $fileName ne "" ) {
        open( STDOUT, ">" . $fileName . "-tmp" );
    }

    my $db = Common::RSDB->new( client_id => $clientID );
    my $dbh = $db->DBH;

    my $sql =
        "SELECT file.orig_file_name, service.service_name, sale.* FROM file, sale, service "
      . "WHERE file.file_id=sale.file_id AND file.period_id = $periodID AND file.service_id=service.service_id AND sale.import_status = 8 "
      . "ORDER BY file.service_id, sale.file_id, sale.line_num";

    my $sth  = $dbh->prepare($sql);
    my $rows = $sth->execute();

    my $header = join(
        "\t", qw(file_name
          service
          sale_id
          file_id
          product_id
          product_type
          format_type
          media_type
          date_begin
          date_end
          service_product_id
          client_product_id
          upc
          isrc
          artist_name
          album_name
          track_name
          label_name
          track_num
          units
          price
          currency_code
          conversion_rate
          country_code
          line_num
          import_status
          usd_ext_price
          album_id
          track_id)
    );

    print $header . "\n";

    while ( my $row = $sth->fetchrow_hashref() ) {
        print $row->{orig_file_name} . "\t"
          . $row->{service_name} . "\t"
          . $row->{sale_id} . "\t"
          . $row->{file_id} . "\t"
          . $row->{product_id} . "\t"
          . $row->{product_type} . "\t"
          . $row->{format_type} . "\t"
          . $row->{media_type} . "\t"
          . $row->{date_begin} . "\t"
          . $row->{date_end} . "\t"
          . $row->{service_product_id} . "\t"
          . $row->{client_product_id} . "\t"
          . $row->{upc} . "\t"
          . $row->{isrc} . "\t"
          . $row->{artist_name} . "\t"
          . $row->{album_name} . "\t"
          . $row->{track_name} . "\t"
          . $row->{label_name} . "\t"
          . $row->{track_num} . "\t"
          . $row->{units} . "\t"
          . $row->{price} . "\t"
          . $row->{currency_code} . "\t"
          . $row->{conversion_rate} . "\t"
          . $row->{country_code} . "\t"
          . $row->{line_num} . "\t"
          . $row->{import_status} . "\t"
          . ( $row->{units} * $row->{price} * $row->{conversion_rate} ) . "\n";
    }

    ## output to file
    if ( $fileName ne "" ) {
        close STDOUT;
        system("mv ${fileName}-tmp $fileName");
    }
}

sub usage {
    my $err = shift;

    print "ERROR: $err\n";
    print "Usage: $0 CLIENT_ID\n";

    return 1;
}