#------------------------------------------------------------
# Copyright (C) 2012 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::ReportQueries::EMIPulse;

use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::ReportQueries;

use base 'RPS::DB::Item::ReportQueries';


use constant kTable => 'sale';

# I'll probably override this later.
# But for now I'll leverage the built-in stuff to make my initial testing easier.
#
sub _config
{
    return 
    {
        'digital_icpn'      => {},
        'digital_isrc'      => {},   # <- I think this is actually their DTI?
        'date_end'          => {},
        'service_id'        => {},
        'product_type'      => {},
        'format_type'       => {},
        'media_type'        => {},
        'track_title'       => {},
        'album_title'       => {},
        'track_artist'      => {},
        'album_artist'      => {},
        'units'             => {},
        'price'             => {},
        'currency_code'     => {},
        'country_code'      => {},
        'filename'          => {},
    };
}


sub GetSalesForPeriod
{
    my ($class, $periodID) = @_;
    assert(defined $periodID);

    my @select;
    push @select, "sale.units as units";
    push @select, "sale.price as price";
    push @select, "sale.product_type as product_type";
    push @select, "sale.format_type as format_type";
    push @select, "sale.date_end as date_end";
    push @select, "product.upc_ean as digital_icpn";
    push @select, "track.custom_1 as digital_isrc";
    push @select, "file.orig_file_name as filename";
    push @select, "file.service_id as service_id";
    push @select, "track.title as track_title";
    push @select, "track_artist.name as track_artist";
    push @select, "album.title as album_title";
    push @select, "album_artist.name as album_artist";


    my @join;
    push @join, "LEFT JOIN product on (sale.product_id = product.product_id)";
    push @join, "LEFT JOIN file on (sale.file_id = file.file_id)";
    push @join, "LEFT JOIN track on (product.product_type_id=4 AND product.asset_id = track.track_id)";
    push @join, "LEFT JOIN album on (product.product_type_id=3 AND product.asset_id = album.album_id)";
    push @join, "LEFT JOIN artist AS track_artist on (product.product_type_id=4 AND track.artist_id = track_artist.artist_id)";
    push @join, "LEFT JOIN artist AS album_artist on (product.product_type_id=3 AND album.artist_id = album_artist.artist_id)";


    my $sql = "SELECT " . join(',', @select)
    . " FROM sale "
    . join(' ', @join)
    . " WHERE file.period_id = $periodID"
    . " AND sale.product_id IS NOT NULL";


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



1;