#!/usr/bin/perl -w

###
###
use strict;

Report->start();

package Report;

use Data::Dumper;

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

use lib '/app/tools/bookpub/lib';
use BookPub::Tracker::Service;
use BookPub::DB::Item::File;
use BookPub::DB::Item::Sale;

use lib '/app/tools/bookpub/lib';
use base 'BookPub::Script';

sub _options {
    {
        client_id => {
            short       => 'c',
            description => 'Client ID',
            parameter   => 'i',
            required    => 1
        },

    };
}

sub _process {
    my $self = shift;

    my %serviceHash;
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT DISTINCT 
               service.service_id AS id, 
               service.service_name AS name 
               FROM sale 
               JOIN service 
               ON sale.service_id = service.service_id 
               WHERE date_begin >= '2010-01-01' AND date_begin <= '2015-12-31'";

    my $sth = $dbo->DoCmd($sql);

    while ( my $href = $sth->fetchrow_hashref() ) {
        $serviceHash{ $href->{name} }{id} = $href->{id};
    }

    # Header
    print STDOUT "\"Distributor/Retailer\",\"Country\",\"eBook Language\",\"Units\",\"Native Revenue\",\"Currency Code\",\"Year\"\n";

    foreach my $serviceName ( sort keys %serviceHash ) {
        my $serviceID = $serviceHash{$serviceName}{id};
        foreach my $countryCode (
            'AT', 'BE', 'BG', 'CH', 'CY', 'CZ', 'DE', 'DK', 'EE', 'ES', 'FI', 'FR', 'GB', 'GR', 'HR', 'HU',
            'IE', 'IS', 'IT', 'LI', 'LT', 'LU', 'LV', 'MT', 'NL', 'NO', 'PL', 'PT', 'RO', 'SE', 'SI', 'SK'
          ) {

            foreach my $year ( '2015', '2014', '2013', '2012', '2011', '2010' ) {
                $sql = "SELECT 
                     SUM(units) AS units,
                     ROUND(SUM(revenue),2) AS revenue,
                     currency_code
                     FROM sale 
                     WHERE date_begin >= '" . $year . "-01-01' AND date_begin <= '" . $year . "-12-31' 
                     AND service_id = " . $serviceID . "
                     AND country_code = '" . $countryCode . "'
                     AND product_type = 'EBOK'
                     GROUP BY currency_code";

                $sth = $dbo->DoCmd($sql);

                if ( defined $sth && $sth->rows > 0 ) {
                    while ( my $href = $sth->fetchrow_hashref() ) {
                        print STDOUT "\""
                          . $serviceName . "\",\""
                          . $countryCode . "\",\""
                          . "English" . "\",\""
                          . $href->{units} . "\",\""
                          . $href->{revenue} . "\",\""
                          . $href->{currency_code} . "\",\""
                          . $year . "\"\n";
                    }
                } else {
                    print STDOUT "\""
                      . $serviceName . "\",\""
                      . $countryCode . "\",\""
                      . "English" . "\",\"" . "0" . "\",\"" . "0" . "\",\"" . "USD" . "\",\""
                      . $year . "\"\n";
                }
            }
        }
    }
}