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

use strict;
use warnings;
use Data::Dumper;

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::UKMechanicalRun;
use RPS::DB::Item::McpsLicenseRun;
use RPS::DB::Item::McpsLicense;
use RPS::DB::Item::McpsLicenseRetention;
use RPS::DB::Item::ReportQueries;
use RPS::DB::Item::ProductType;
use RPS::DB::Item::Artist;

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


sub _config
{
    return 
    {
        'catalog_number'    => {},
        'album_title'       => {},
        'album_id'          => {},
        'artist_name'       => {},
        'product_type'      => {},
        'release_date'      => {},
        'tv_advertised'     => {},        
        'current_period'    => {},
        'p1_held'           => {},
        'p2_held'           => {},
        'p3_held'           => {},
        'p4_held'           => {},        
        'total_held'        => {},

    };
}


# This will return a list of retentions held and released as of now (br product).
#
sub GetRetentionSummary
{
    my ($class, $runID) = @_;
    
    # First, create a temporary table for this report.
    #
    my $dbo = Common::RSApp::GetClientDB();

    $dbo->DoCmd('DROP TABLE IF EXISTS zzuk_mech_retention_summary');
    $dbo->DoCmd('CREATE TEMPORARY TABLE zzuk_mech_retention_summary ('
    . '`catalog_number` varchar(255),'
    . '`album_title` varchar(255),'
    . '`album_id` int(10) unsigned,' 
    . '`artist_name` varchar(255),'    
    . '`product_type` varchar(25),'
    . '`release_date` date,'
    . '`tv_advertised` tinyint(1),'    
    . '`current_period` tinyint(1),'
    . '`p1_held` int(10),'   
    . '`p2_held` int(10),' 
    . '`p3_held` int(10),'   
    . '`p4_held` int(10),'     
    . '`total_held` int(10) )');     
    

    my $maxPeriods = 5;
    
    my $licenses = RPS::DB::Item::McpsLicense->GetAllWithActiveRetentions($maxPeriods);

    # Okay, now we've got all the mcps licenses that are still in the first 5 periods.


 
	while (defined $licenses && $licenses->hasNext())
	{

		my $license = $licenses->next();    
		my $licenseID = $license->mcps_license_id;
		
	    my $initialPeriods = $license->initial_periods_completed;
		my $committedPeriods = RPS::DB::Item::McpsLicenseRun->NumCommittedRuns($licenseID);
		
		my $currentPeriod = $initialPeriods + $committedPeriods;
	
		my $productID = $license->product_id;
		my $product = RPS::DB::Item::Product->Lookup(product_id => $productID);		
        my $productType = _idToProductType($product->product_type_id);
        my $releaseDate = $product->release_date();
        
        my $albumID = $product->asset_id();
        my $album = RPS::DB::Item::Album->Lookup(album_id => $albumID);
        my $catalogNumber = $album->catalog_number();
        my $albumTitle = $album->title();
        my $albumID = $album->album_id();
        my $artistID = $album->artist_id();
        my $artist = RPS::DB::Item::Artist->Lookup(artist_id => $artistID);
        my $artistName = $artist->name();      
            
        my $tvAdvertised = 0;
        my $retentionCategory = $license->retention_category();
        if ($retentionCategory eq 'T')
        {
            $tvAdvertised = 1;   
        }         
        
        my $p1Held = 0;
        my $p2Held = 0;
        my $p3Held = 0;
        my $p4Held = 0;

        # So here's the fun part.  The units could be stored in the mcps_license_retention table 
        # or the mcps_statement_item table. It depends on the current period AND the run status.
    
        if ($currentPeriod > 3)
        {
            $p4Held = RPS::DB::Item::McpsLicenseRetention->GetUnitsByLicenseAndPeriod($licenseID, 4);
        }
 
        if ($currentPeriod > 2)
        {
            $p3Held = RPS::DB::Item::McpsLicenseRetention->GetUnitsByLicenseAndPeriod($licenseID, 3);
        }           
                
        if ($currentPeriod > 1)
        {
            $p2Held = RPS::DB::Item::McpsLicenseRetention->GetUnitsByLicenseAndPeriod($licenseID, 2);
        }      

        if ($currentPeriod > 0)
        {
            $p1Held = RPS::DB::Item::McpsLicenseRetention->GetUnitsByLicenseAndPeriod($licenseID, 1);
        }  
        

        my $totalHeld = $p1Held + $p2Held + $p3Held + $p4Held;

        $dbo->DoCmdWithPlaceholders('INSERT INTO zzuk_mech_retention_summary SET'
        . " catalog_number= ?,"
        . " album_title= ?,"
        . " album_id= ?,"        
        . " artist_name= ?,"
        . " product_type= ?,"
        . " release_date= ?,"
        . " tv_advertised= ?,"             
        . " current_period= ?,"
        . " p1_held= ?,"
        . " p2_held= ?,"     
        . " p3_held= ?,"
        . " p4_held= ?,"          
        . " total_held= ?",
        [
            $catalogNumber,
            $albumTitle,
            $albumID,
            $artistName,
            $productType,
            $releaseDate,
            $tvAdvertised,
            $currentPeriod,
            $p1Held,
            $p2Held,
            $p3Held,
            $p4Held,            
            $totalHeld,
        ]
        ); 
                         
    }    
    
    my $sql = "SELECT * FROM zzuk_mech_retention_summary ORDER BY catalog_number, product_type";
    
    return $class->SUPER::GetAll($sql);
    
}


sub _idToProductType
{
    my ($id) = @_;

    # We have a table for this, might as well use it.
    #
    my $productTypeEntry = RPS::DB::Item::ProductType->Lookup(product_type_id => $id);
    if (! $productTypeEntry)
    {
        return '???';
    }
    return $productTypeEntry->description();
}


1;