#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::LicenseIncome;
use strict;
use warnings;

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

use constant kTable => 'license_income';
use constant kDB    => Common::DB::Item::kClientDB();

use constant kParentAlbumContract => 1;
use constant kParentTrackContract => 2;

sub GetByFileID {
    my ( $class, $id ) = @_;
    assert($id);

    my $sql = "SELECT * FROM " . kTable . " WHERE file_id=$id ORDER BY license_income_id";

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

sub GetByParent {
    my ( $class, $id, $type ) = @_;

    my $sql = "SELECT * FROM " . kTable . " WHERE parent_type=$type AND parent_id=$id";

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

sub GetBySource {
    my ( $class, $type ) = @_;

    my $sql = "SELECT * FROM " . kTable . " WHERE license_income_source_id=$type";

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

sub GetAllUnprocessed {
    my ($class) = @_;

    my $sql = "SELECT * FROM " . kTable . " WHERE processed=0";

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

sub GetByContractID {
    my ( $class, $contractID ) = @_;
    assert($contractID);

    my $sql = "SELECT * FROM " . kTable . " WHERE contract_id=$contractID";

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

sub GetPayableAuditReportByRunID {
    my ( $class, $runID ) = @_;
    assert($runID);

    my $sql = qq/
        SELECT t.*,
               (SELECT COUNT(*) + 1
                FROM license_income li2
                WHERE li2.file_id = t.file_id
                AND li2.license_income_id < t.source_license_income_id) as line_number
        FROM (
            SELECT arli.*,
                   li.file_id,
                   li.sale_id,
                   li.license_income_id as source_license_income_id,
                   f.orig_file_name AS file_name,
                   ap.artist_payee_id AS payee_id,
                   ap.name AS payee_name,
                   ap.client_account_id AS client_account_no,
                   ac.title AS contract_title,
                   ac.client_contract_id,
                   a.title AS album_title,
                   a.catalog_number,
                   a.client_album_id,
                   t.title AS track_title,
                   COALESCE(m.isrc, s.isrc) as isrc,
                   lit.name AS income_type_name,
                   a.custom_1 AS album_custom_1,
                   a.custom_2 AS album_custom_2,
                   a.custom_3 AS album_custom_3,
                   t.custom_1 AS track_custom_1,
                   t.custom_2 AS track_custom_2,
                   t.custom_3 AS track_custom_3,
                   p.name AS sales_period_name,
                   f.period_id AS sales_period_id,
                   p.start_date AS sales_period_start_date,
                   p.end_date AS sales_period_end_date
            FROM artist_royalty_license_income_item arli
            LEFT JOIN artist_royalty_statement ars ON arli.artist_royalty_statement_id = ars.artist_royalty_statement_id
            LEFT JOIN license_income li ON arli.license_income_id = li.license_income_id
            LEFT JOIN file f ON li.file_id = f.file_id
            LEFT JOIN period p ON f.period_id = p.period_id
            LEFT JOIN artist_payee ap ON ars.payee_id = ap.artist_payee_id
            LEFT JOIN new_artist_contract ac ON arli.artist_contract_id = ac.artist_contract_id
            LEFT JOIN album a ON arli.album_id = a.album_id
            LEFT JOIN track t ON arli.track_id = t.track_id
            LEFT JOIN master m ON t.master_id = m.master_id
            LEFT JOIN sale s ON li.sale_id = s.sale_id
            LEFT JOIN license_income_type lit ON arli.license_income_type_id = lit.license_income_type_id
            WHERE ars.artist_royalty_run_id = $runID
        ) t
        ORDER BY payee_name, contract_title, album_title, track_title, file_id, source_license_income_id
    /;

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

1;