#------------------------------------------------------------
# Copyright (C) 2009 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package BookPub::DB::Item::Sale;
use strict;
use warnings;

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

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

use constant kTable => 'sale';
use constant kDB    => Common::DB::Item::kClientDB;

# Product types.  i.e. the broad class of thing being sold.
#
use constant kProductTypePhysicalBook     => 'PBOK';
use constant kProductTypeEBook            => 'EBOK';
use constant kProductTypeMultiEBookBundle => 'BNDL';
use constant kProductTypeAudioBook        => 'AUDB';
use constant kProductTypeEChapter         => 'ECHP';
use constant kProductTypeAudioChapter     => 'AUDC';
use constant kProductTypeEJournal         => 'EJRL';

# Electronic formats
#
use constant kFormatTypeContentPackage   => 'cont';    # Onix 000
use constant kFormatTypeHTML             => 'html';    # Onix 001
use constant kFormatTypePDF              => 'pdf ';    # Onix 002
use constant kFormatTypePDFMerchant      => 'pdfm';    # Onix 003
use constant kFormatTypeAdobeEBook       => 'adeb';    # Onix 004
use constant kFormatTypeMSLITLvl1_3      => 'msl1';    # Onix 005
use constant kFormatTypeMSLITLvl5        => 'msl5';    # Onix 006
use constant kFormatTypeNetLibrary       => 'netl';    # Onix 007
use constant kFormatTypeMetaText         => 'meta';    # Onix 008
use constant kFormatTypeMightyWords      => 'migh';    # Onix 009
use constant kFormatTypeEReader          => 'pdb ';    # Onix 010
use constant kFormatTypeSoftbook         => 'sobk';    # Onix 011
use constant kFormatTypeRocketBook       => 'robk';    # Onix 012
use constant kFormatTypeGemStar1100      => 'gem1';    # Onix 013
use constant kFormatTypeGemStar1200      => 'gem2';    # Onix 014
use constant kFormatTypeFranklinEBookman => 'febk';    # Onix 015
use constant kFormatTypeBooks24x7        => 'ebk2';    # Onix 016
use constant kFormatTypeDigitalOwl       => 'dowl';    # Onix 017
use constant kFormatTypeHandheldmed      => 'hhmd';    # Onix 018
use constant kFormatTypeWizeUp           => 'wize';    # Onix 019
use constant kFormatTypeTK3              => 'tk3 ';    # Onix 020
use constant kFormatTypeLitraweb         => 'litr';    # Onix 021
use constant kFormatTypeMOBI             => 'mobi';    # Onix 022
use constant kFormatTypeOPF              => 'opf ';    # Onix 023
use constant kFormatTypeTownCompasData   => 'town';    # Onix 024
use constant kFormatTypePlainText        => 'text';    # Onix 025
use constant kFormatTypeExeBook          => 'exbk';    # Onix 026
use constant kFormatTypeSonyBBeB         => 'bbeb';    # Onix 027
use constant kFormatTypeVitalSource      => 'vits';    # Onix 028
use constant kFormatTypeEPub             => 'epub';    # Onix 029
use constant kFormatTypeMyiLibrary       => 'myil';    # Onix 030
use constant kFormatTypeKindle           => 'kndl';    # Onix 031
use constant kFormatTypeGoogle           => 'goog';    # Onix 032
use constant kFormatTypeVook             => 'vook';    # Onix 033
use constant kFormatTypeDXReader         => 'dxrd';    # Onix 034
use constant kFormatTypeEBL              => 'ebl ';    # Onix 035
use constant kFormatTypeEbrary           => 'ebra';    # Onix 036
use constant kFormatTypeISilo            => 'silo';    # Onix 037
use constant kFormatTypePlucker          => 'pluc';    # Onix 038
use constant kFormatTypeVitalBook        => 'vita';    # Onix 039
use constant kFormatTypeIOSApp           => 'iapp';    # Onix 040
use constant kFormatTypeAndroidApp       => 'aapp';    # Onix 041
use constant kFormatTypeOtherApp         => 'oapp';    # Onix 042
use constant kFormatTypeIBook            => 'ibok';    # Onix 044
use constant kFormatTypeEPIB             => 'epib';    # Onix 045
use constant kFormatTypeMultipleFormats  => 'mult';    # Onix 098
use constant kFormatTypeUnknownEBook     => '?ebk';    # Onix 099
use constant kFormatTypeTomeRaider       => 'tome';
use constant kFormatTypeArghos           => 'argh';
use constant kFormatTypeAppleMultimedia  => 'appl';
use constant kFormatTypeETI              => 'eti';
use constant kFormatTypeDNL              => 'dnl';

# Physical formats
#
use constant kFormatTypeHardback           => 'hard';
use constant kFormatTypeTradeSoftback      => 'tsft';
use constant kFormatTypeMassMarketSoftback => 'msft';

# POD formats
# these are really just binding options which could also apply to phys books
# for now, this gives us a way to distinguish various POD "formats"
#
use constant kFormatTypeBindPerfect => 'perf';
use constant kFormatTypeBindCoil    => 'coil';
use constant kFormatTypeBindLoop    => 'loop';
use constant kFormatTypeBindSaddle  => 'sddl';
use constant kFormatTypeBindComb    => 'comb';
use constant kFormatTypeBindCase    => 'case';

# Audio formats
#
use constant kFormatTypeAudioUnknown => '?aud';
use constant kFormatTypeAudioMP3     => 'mp3 ';
use constant kFormatTypeAudioWAV     => 'wav ';
use constant kFormatTypeAudioAIFF    => 'aiff';
use constant kFormatTypeAudioWMA     => 'wma ';

# etc...

# Re-naming 'delivery_type' to 'purchase_type'.
#
use constant kPurchaseTypePhysicalRetail => 'phys';    # !!! is 'physical retail' redundant?
use constant kPurchaseTypeDownload       => 'down';
use constant kPurchaseTypeSubscriptionDL => 'subd';
use constant kPurchaseTypeStream         => 'strm';
use constant kPurchaseTypePrintOnDemand  => 'pod ';
use constant kPurchaseTypeInAppPurchase  => 'iapu';
use constant kPurchaseTypeLoan           => 'loan';

#use constant kPurchaseTypeLibrarySale       => 'lbsl';
use constant kPurchaseTypePromotional => 'prmo';

# !!! What 'purchase type' is suitable for a print-on-demand?
# !!! does kPurchaseTypePhysical work there?

use constant STATUS_MATCH         => 1;
use constant STATUS_NOMATCH       => 2;
use constant STATUS_MULTIMATCH    => 3;
use constant STATUS_MAPPED        => 4;
use constant STATUS_UNRECOVERABLE => 5;
use constant STATUS_AUTO_MAPPED   => 6;
use constant STATUS_BATCH_MAPPED  => 7;
use constant STATUS_DONT_MATCH    => 8;

my %gOnixProductCodes = (
    kProductTypeEBook() => [
        Common::DB::Item::OnixCode::kProductFormElectronicBookText, Common::DB::Item::OnixCode::kProductFormDigitalDelivery, Common::DB::Item::OnixCode::kProductFormDigitalDownload,
        Common::DB::Item::OnixCode::kProductFormOther
    ],
    kProductTypeAudioBook() => [
        Common::DB::Item::OnixCode::kProductFormAudio, Common::DB::Item::OnixCode::kProductFormAudioFile,
        Common::DB::Item::OnixCode::kProductFormCD,    Common::DB::Item::OnixCode::kProductFormOther
    ],
    kProductTypePhysicalBook() => [ Common::DB::Item::OnixCode::kProductFormSoftback, Common::DB::Item::OnixCode::kProductFormHardback ],
);

sub GetUnmatchedByFile {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    $fileID = $class->quote($fileID);
    my $sql = 'SELECT * FROM ' . kTable . " WHERE file_id = $fileID AND (product_id IS NULL OR product_id=0) ORDER BY sale_id";
    return $class->GetAll($sql);
}

sub GetAllByProductID {
    my ( $class, $productID ) = @_;
    assert($productID);

    $productID = $class->quote($productID);
    return $class->GetAll( "SELECT * FROM " . kTable . " WHERE product_id = $productID" );
}

sub GetAllByFileID {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    $fileID = $class->quote($fileID);
    return $class->GetAll( "SELECT * FROM " . kTable . " WHERE file_id = $fileID" );
}

sub DeleteByFileID {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    # Note that we are deleting the associated records in the sale, sale_price_exception
    # and r_native_list_price tables.

    my $dbo = Common::RSApp::GetClientDB();
    my @sqlParams = ($fileID);
    my $sql = "DELETE sale_price_exception FROM " . kTable . " JOIN sale_price_exception USING (sale_id)" . " WHERE sale.file_id = ?";
    $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);

    $sql = "DELETE r_native_list_price FROM " . kTable . " JOIN r_native_list_price USING (sale_id)" . " WHERE sale.file_id = ?";
    $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);

    $sql = "DELETE FROM  " . kTable . " WHERE sale.file_id = ?";
    $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
}

# get the number of records processed while file is still being imported
sub GetImportingRecordCount {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) FROM " . kTable . " WHERE file_id = ?";
    my @sqlParams = ($fileID);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);

    return $sth ? $sth->fetchrow_arrayref()->[0] : undef;
}

sub GetFirstSaleError {
    my ( $claass, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT MIN(sale_id) as sale_id FROM "
      . kTable
      . " WHERE file_id = ?"
      . " AND map_id IS NULL"
      . " AND import_status IN (?, ?, ?)";
     my @sqlParams = ($fileID, STATUS_NOMATCH, STATUS_MULTIMATCH, STATUS_UNRECOVERABLE);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    }

    return undef;
}

sub GetFirstException {
    my ( $claass, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT MIN(sale_id) as sale_id FROM "
      . kTable
      . " WHERE file_id = ? AND product_id IS NULL"
      . " AND import_status != ?";
    my @sqlParams = ($fileID, STATUS_DONT_MATCH);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    }

    return undef;
}

sub GetPreviousErrorID {
    my ( $class, $saleID, $fileID ) = @_;
    assert($saleID);
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT max(sale_id) as sale_id FROM "
      . kTable
      . " WHERE file_id = ?"
      . " AND sale_id < ?"
      . " AND import_status IN (?, ?, ?)";
    my @sqlParams = ($fileID, $saleID, STATUS_NOMATCH, STATUS_MULTIMATCH, STATUS_UNRECOVERABLE);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    }

    return undef;
}

sub GetPreviousUnfixedID {
    my ( $class, $saleID, $fileID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT max(sale_id) as sale_id FROM "
      . kTable
      . " WHERE file_id = ?"
      . " AND sale_id < ?"
      . " AND import_status IN (?, ?, ?)"
      . " AND map_id IS NULL";
    my @sqlParams = ($fileID, $saleID, STATUS_NOMATCH, STATUS_MULTIMATCH, STATUS_UNRECOVERABLE);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    }

    return undef;
}

sub GetNextErrorID {
    my ( $class, $saleID, $fileID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT min(sale_id) as sale_id FROM "
      . kTable
      . " WHERE file_id = ?"
      . " AND sale_id > ?"
      . " AND import_status IN (?, ?, ?)";
    my @sqlParams = ($fileID, $saleID, STATUS_NOMATCH, STATUS_MULTIMATCH, STATUS_UNRECOVERABLE);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    }

    return undef;
}

sub GetNextUnfixedID {
    my ( $class, $saleID, $fileID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT min(sale_id) as sale_id FROM "
      . kTable
      . " WHERE file_id = ?"
      . " AND sale_id > ?"
      . " AND import_status IN (?, ?, ?)"
      . " AND map_id IS NULL";
     my @sqlParams = ($fileID, $saleID, STATUS_NOMATCH, STATUS_MULTIMATCH, STATUS_UNRECOVERABLE);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    }

    return undef;
}

sub CalculateFileSummaryInfo {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    $fileID = $class->quote($fileID);

    my $dbo = Common::RSApp::GetClientDB();

    my $revenueFormula = 'revenue';
    my $unitsFormula   = 'units';

    my $whereClauseA = " WHERE file_id = $fileID";

    my $sql = "SELECT A.total_units, A.records, A.revenue, B.total_exceptions, C.remaining_exceptions" . " FROM"

      # A - query
      . " ("
      . " SELECT ifnull(sum($unitsFormula),0) AS total_units,"
      . " count(*) AS records,"
      . " round(ifnull(sum($revenueFormula),0), 2) AS revenue"
      . " FROM sale"
      . $whereClauseA . " ) A,"

      # B - query
      . " ( "
      . " SELECT count(*) AS total_exceptions"
      . " FROM sale"
      . " WHERE file_id = $fileID"
      . " AND import_status!="
      . STATUS_MATCH
      . " AND import_status!="
      . STATUS_MAPPED
      . " AND import_status!="
      . STATUS_AUTO_MAPPED
      . " AND import_status!="
      . STATUS_BATCH_MAPPED
      . " AND import_status!="
      . STATUS_DONT_MATCH . " ) B,"

      # C - query
      . " ("
      . " SELECT count(*) AS remaining_exceptions"
      . " FROM sale"
      . " WHERE file_id = $fileID"
      . " AND import_status!="
      . STATUS_MATCH
      . " AND import_status!="
      . STATUS_MAPPED
      . " AND import_status!="
      . STATUS_AUTO_MAPPED
      . " AND import_status!="
      . STATUS_BATCH_MAPPED
      . " AND import_status!="
      . STATUS_DONT_MATCH
      . " AND map_id IS NULL" . " ) C";

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

    my ( $units, $records, $revenue, $totalExceptions, $remainingExceptions );
    if ( defined $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref();

        $units               = $href->{total_units};
        $records             = $href->{records};
        $revenue             = $href->{revenue};
        $totalExceptions     = $href->{total_exceptions};
        $remainingExceptions = $href->{remaining_exceptions};
    }

    my $currencies = $class->GetCurrenciesByFileID($fileID);
    my $currencyCode =
        !$currencies             ? undef
      : scalar(@$currencies) > 1 ? 'MUL'
      :                            $currencies->[0][0];

    return ( $units, $records, $revenue, $totalExceptions, $remainingExceptions, $currencyCode );
}

sub CountRemainingExceptions {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    $fileID = $class->quote($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT count(*) AS remaining_exceptions FROM sale WHERE file_id = $fileID"
      . " AND import_status!="
      . STATUS_MATCH
      . " AND import_status!="
      . STATUS_MAPPED
      . " AND import_status!="
      . STATUS_AUTO_MAPPED
      . " AND import_status!="
      . STATUS_BATCH_MAPPED
      . " AND import_status!="
      . STATUS_DONT_MATCH
      . " AND map_id IS NULL";

    my $sth = $dbo->DoCmd($sql);
    if ( defined $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref();
        return $href->{remaining_exceptions};
    }

    return 0;
}

sub GetSalesWithMapID {
    my ( $class, $mapID ) = @_;
    assert($mapID);

    $mapID = $class->quote($mapID);

    my $sql = "SELECT * FROM " . kTable . " WHERE map_id=$mapID";
    return $class->GetAll($sql);
}

sub SplitFile {
    my ( $class, $oldFileID, $newFileID ) = @_;
    assert($oldFileID);
    assert($newFileID);

    my $client = Common::Client::Current();

    my $includePriceExceptions = 0;
    if ( $client->isClientType(Common::Client::kSalePriceValidation) ) {
        $includePriceExceptions = 1;
    }

    my $dbo = Common::RSApp::GetClientDB();

    my $sql = "UPDATE sale ";

    if ( $includePriceExceptions == 1 ) {
        $sql .= "LEFT JOIN sale_price_exception USING (sale_id) ";
    }

    $sql .=
        "SET file_id = "
      . $class->quote($newFileID) . " "
      . "WHERE file_id = "
      . $class->quote($oldFileID)
      . " AND ( "
      . "( product_id IS NULL OR product_id = 0 ) ";
    if ( $includePriceExceptions == 1 ) {
        $sql .= "OR ( sale_price_exception.sale_id IS NOT NULL AND NOT(approved) ) ";
    }

    $sql .= " )";

    $dbo->DoCmd($sql);
}

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

    $periodID = $class->quote($periodID);

    my $sql =
        "SELECT "
      . kTable
      . ".* FROM "
      . kTable
      . " LEFT JOIN file USING (file_id)"
      . " WHERE file.period_id = $periodID AND file.file_status = 5 AND file.type_id != 5"
      . " AND sale.import_status != "
      . STATUS_DONT_MATCH();

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

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

    $periodID    = $class->quote($periodID);
    $productType = $class->quote($productType);

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE file_id IN (select file_id from file where period_id = $periodID)"
      . " AND product_type = '"
      . $productType . "'";

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

sub GetUnconsolidatedForReport {
    my ( $class, $periodID, $serviceID, $productType ) = @_;
    assert( defined $serviceID );
    assert( defined $periodID );

    $periodID  = $class->quote($periodID);
    $serviceID = $class->quote($serviceID);

    my $sql =
        "SELECT sale.*, book_product.isbn13 as r_isbn13"
      . " FROM "
      . kTable
      . " LEFT JOIN file ON sale.file_id = file.file_id"
      . " LEFT JOIN book_product ON sale.product_id = book_product.product_id"
      . " WHERE file.period_id = $periodID"
      . " AND sale.service_id = $serviceID"
      . " ORDER BY book_product.isbn13";

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

sub GetRevenueSummaryByFileID {
    my ( $class, %args ) = @_;
    my $file_id                  = $args{file_id};
    my $skip_free                = $args{skip_free};
    my $include_price_exceptions = $args{include_price_exceptions};

    assert($file_id);

    $file_id  = $class->quote($file_id);

    my $sql = "SELECT if(import_status = " . STATUS_DONT_MATCH() . ", 'dontmatch',if(product_id is null";

    if ($include_price_exceptions) {
        $sql .= " OR (sale_price_exception.sale_id IS NOT NULL AND approved <> 1)";
    }

    $sql .= ", 'unmatched', 'matched')) as stat, ";
    $sql .= " sum(sale.revenue * sale.conversion_rate), sum(sale.units), count(*)";
    $sql .= " FROM file INNER JOIN sale USING ( file_id )";

    if ($include_price_exceptions) {
        $sql .= " LEFT JOIN sale_price_exception USING (sale_id) ";
    }

    $sql .= " WHERE file.file_id=$file_id";

    $sql .= " AND sale.is_free='N' " if ($skip_free);

    $sql .= " GROUP BY sale.file_id,stat";

    my $dbo = Common::RSApp::GetClientDB();

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

    my ( %summary, %summed );
    while ( my $row = $sth->fetchrow_arrayref ) {
        if ( $row->[0] eq "dontmatch" ) {
            $summed{dontmatch}{revenue} += $row->[1];
            $summed{dontmatch}{units}   += $row->[2];
            $summed{dontmatch}{lines}   += $row->[3];
        } elsif ( $row->[0] eq "unmatched" ) {
            $summed{unmatched}{revenue} += $row->[1];
            $summed{unmatched}{units}   += $row->[2];
            $summed{unmatched}{lines}   += $row->[3];
        } elsif ( $row->[0] eq "matched" ) {
            $summed{matched}{revenue} += $row->[1];
            $summed{matched}{units}   += $row->[2];
            $summed{matched}{lines}   += $row->[3];
        }
    }
    if ( defined $summed{dontmatch}{lines} && $summed{dontmatch}{lines} > 0 ) {
        $summary{dontmatch} = {
            revenue => $summed{dontmatch}{revenue},
            units   => $summed{dontmatch}{units},
            lines   => $summed{dontmatch}{lines},
        };
    }
    if ( defined $summed{unmatched}{lines} && $summed{unmatched}{lines} > 0 ) {
        $summary{unmatched} = {
            revenue => $summed{unmatched}{revenue},
            units   => $summed{unmatched}{units},
            lines   => $summed{unmatched}{lines},
        };
    }
    if ( defined $summed{matched}{lines} && $summed{matched}{lines} > 0 ) {
        $summary{matched} = {
            revenue => $summed{matched}{revenue},
            units   => $summed{matched}{units},
            lines   => $summed{matched}{lines},
        };
    }

    $sql = "SELECT SUM(units) FROM sale WHERE file_id = $file_id AND is_free = 'Y'";
    $sth = $dbo->DoCmd($sql);

    while ( my $row = $sth->fetchrow_arrayref ) {
        $summary{free} = { units => $row->[0] };
    }

    return \%summary;
}

sub GetCurrencyRevenueSummaryByFileID {
    my ( $class, %args ) = @_;
    my $fileID   = $args{fileID};
    my $currency = $args{currency};
    assert($fileID);
    my $dbo = Common::RSApp::GetClientDB();

    my @args = ($fileID);
    my $sql  = 'SELECT currency_code, SUM(revenue) FROM ' . kTable . ' WHERE file_id = ?';
    if ($currency) {
        $sql .= ' AND currency_code = ?';
        push @args, $currency;
    }
    $sql .= ' GROUP BY currency_code';

    my $sth = $dbo->DoCmdWithPlaceholders( $sql, \@args );

    return undef unless $sth->rows();

    my %currencyHash = ();
    while ( my $row = $sth->fetchrow_arrayref() ) {
        $currencyHash{ $row->[0] } = $row->[1];
    }

    return \%currencyHash;
}

sub SetConversionRateByCurrency {
    my ( $class, %args ) = @_;
    my $fileID   = $args{fileID};
    my $currency = $args{currency};
    my $rate     = $args{rate};
    assert($fileID);
    assert($currency);

    my $dbo = Common::RSApp::GetClientDB();

    my $sql = 'UPDATE ' . kTable . ' SET conversion_rate = ? WHERE file_id = ? AND currency_code = ?';
    my $sth = $dbo->DoCmdWithPlaceholders( $sql, [ $rate, $fileID, $currency ] );

    return $sth->rows ? 1 : 0;
}

sub ConversionRatesAreSet {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = 'SELECT COUNT(*) AS count FROM ' . kTable . " WHERE file_id = ? AND conversion_rate > ? HAVING count > ?";
    my @sqlParams = ($fileID, 0, 0);
    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);

    return $sth->rows ? 1 : 0;
}

sub GetCurrenciesByFileID {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    # NOTE: $fileID has been already quoted in the CalculateFileSummaryInfo function
    my $sql = 'SELECT DISTINCT(currency_code) AS currency FROM ' . kTable . " WHERE file_id = $fileID ORDER BY currency";
    my $sth = $dbo->DoCmd($sql);

    return $sth->rows() ? $sth->fetchall_arrayref( [0] ) : undef;
}

sub GetDateRangeByFileID {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = 'SELECT MIN(date_begin), MAX(date_end) FROM sale WHERE file_id = ?';
    my @sqlParams = ($fileID);

    my $sth    = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    my $result = $sth->fetchrow_arrayref();

    return ( $result->[0], $result->[1] );
}

sub GetNormalizedBeginMonthByFileID {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = qq/
        SELECT
            COUNT(*)
        FROM sale
        WHERE file_id = ?
    /;
    my @sqlParams = ($fileID);

    my ($cnt) = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams)->fetchrow_array();

    $sql = qq/
        SELECT
            SUBSTR(date_begin, 1, 7),
            COUNT(*)
        FROM sale
        WHERE 1 = 1
            AND file_id = ?
        GROUP BY 1
        HAVING COUNT(*) > ( $cnt * .05 )
        ORDER BY 1
    /;

    my $sth    = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    my $result = $sth->fetchrow_arrayref();

    return ( $result->[0] );
}

sub GetNormalizedEndMonthByFileID {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = qq/
        SELECT
            COUNT(*)
        FROM sale
        WHERE file_id = ?
    /;
    my @sqlParams = ($fileID);
    my ($cnt) = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams)->fetchrow_array();

    $sql = qq/
        SELECT
            SUBSTR(date_end, 1, 7),
            COUNT(*)
        FROM sale
        WHERE 1 = 1
            AND file_id = ?
        GROUP BY 1
        HAVING COUNT(*) > ( $cnt * .05 )
        ORDER BY 1 DESC
    /;

    my $sth    = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    my $result = $sth->fetchrow_arrayref();

    return ( $result->[0] );
}

# JPK - Going to add some convenient interfaces to help distinguish broad categories.
#
sub ProductTypeIsBook {
    my ( $class, $productType ) = @_;
    assert($productType);

    if (   BookPub::DB::Item::Sale::kProductTypePhysicalBook eq $productType
        || BookPub::DB::Item::Sale::kProductTypeEBook eq $productType
        || BookPub::DB::Item::Sale::kProductTypeMultiEBookBundle eq $productType
        || BookPub::DB::Item::Sale::kProductTypeAudioBook eq $productType ) {
        return 1;
    }
    return 0;
}

sub ProductTypeIsChapter {
    my ( $class, $productType ) = @_;
    assert($productType);

    if (   BookPub::DB::Item::Sale::kProductTypeEChapter eq $productType
        || BookPub::DB::Item::Sale::kProductTypeAudioChapter eq $productType ) {
        return 1;
    }
    return 0;
}

sub ProductTypeIsEJournal {
    my ( $class, $productType ) = @_;
    assert($productType);

    return (BookPub::DB::Item::Sale::kProductTypeEJournal eq $productType ? 1 : 0 );
}

sub GetFormatString {
    my $class = shift;
    my $type  = shift;

    # we don't want to populate format if it is an e-journal product
    return undef if ($type eq kProductTypeEJournal);

    my $method =
      ( $type eq kProductTypePhysicalBook or $type eq kProductTypeEChapter ) ? 'GetPhysicalFormatString'
      : (    $type eq kProductTypeEBook
          or $type eq kProductTypeMultiEBookBundle
          or $type eq kProductTypeEChapter ) ? 'GetElectronicFormatString'
      : ( $type eq kProductTypeAudioBook or $type eq kProductTypeAudioChapter ) ? 'GetAudioFormatString'
      :                                                                           undef;

    return 'Unknown' unless $method;
    return $class->$method(@_);

}

sub GetPhysicalFormatString {
    my ( $class, $fmt ) = @_;

    return
        $fmt eq kFormatTypeHardback           ? 'Hardback'
      : $fmt eq kFormatTypeTradeSoftback      ? 'Trade Softback'
      : $fmt eq kFormatTypeMassMarketSoftback ? 'Mass Market Softback'
      :

      # the following are POD binding types but they need to be here as the product type is phys
        $fmt eq kFormatTypeBindPerfect ? 'Perfect'
      : $fmt eq kFormatTypeBindCoil    ? 'Coil'
      : $fmt eq kFormatTypeBindLoop    ? 'Loop'
      : $fmt eq kFormatTypeBindSaddle  ? 'Saddle'
      : $fmt eq kFormatTypeBindComb    ? 'Comb'
      : $fmt eq kFormatTypeBindCase    ? 'Case/Hard'
      :                                  'Unknown';
}

sub GetElectronicFormatString {
    my ( $class, $fmt ) = @_;

    return
        $fmt eq kFormatTypeUnknownEBook    ? 'Unknown Ebook'
      : $fmt eq kFormatTypePlainText       ? 'Plain Text'
      : $fmt eq kFormatTypeHTML            ? 'HTML'
      : $fmt eq kFormatTypeKindle          ? 'Kindle'
      : $fmt eq kFormatTypeOPF             ? 'OPF'
      : $fmt eq kFormatTypeTomeRaider      ? 'Tome Raider'
      : $fmt eq kFormatTypeArghos          ? 'Arghos'
      : $fmt eq kFormatTypeMOBI            ? 'Mobi'
      : $fmt eq kFormatTypePDF             ? 'PDF'
      : $fmt eq kFormatTypeEPub            ? 'EPub'
      : $fmt eq kFormatTypeEPIB            ? 'ePIB'
      : $fmt eq kFormatTypeMSLITLvl1_3     ? 'MS-Lit Level 1-3'
      : $fmt eq kFormatTypeMSLITLvl5       ? 'MS-Lit Level 5'
      : $fmt eq kFormatTypeEReader         ? 'EReader'
      : $fmt eq kFormatTypeAppleMultimedia ? 'Multimedia'
      : $fmt eq kFormatTypeIBook           ? 'iBook'
      : $fmt eq kFormatTypeETI             ? 'ETI'
      : $fmt eq kFormatTypeDNL             ? 'DNL'
      : $fmt eq kFormatTypeIOSApp          ? 'iOS App'
      : $fmt eq kFormatTypeAndroidApp      ? 'Android App'
      : $fmt eq kFormatTypeOtherApp        ? 'Other App'
      :                                      'Unknown';
}

sub GetAudioFormatString {
    my ( $class, $fmt ) = @_;

    return
        $fmt eq kFormatTypeAudioUnknown ? 'Unknown'
      : $fmt eq kFormatTypeAudioMP3     ? 'mp3'
      : $fmt eq kFormatTypeAudioWAV     ? 'wav'
      : $fmt eq kFormatTypeAudioAIFF    ? 'aiff'
      : $fmt eq kFormatTypeAudioWMA     ? 'wma'
      :                                   'Unknown';
}

sub MapProductTypeToOnixCodes {
    my ( $class, $prodType ) = @_;

    return defined $gOnixProductCodes{$prodType} ? $gOnixProductCodes{$prodType} : undef;
}

sub GetProductTypeString {
    my ( $class, $type ) = @_;
    return
        $type eq kProductTypePhysicalBook     ? 'Physical Book'
      : $type eq kProductTypeEBook            ? 'eBook'
      : $type eq kProductTypeMultiEBookBundle ? 'eBook Bundle'
      : $type eq kProductTypeAudioBook        ? 'Audio Book'
      : $type eq kProductTypeEChapter         ? 'eChapter'
      : $type eq kProductTypeAudioChapter     ? 'Audio Chapter'
      : $type eq kProductTypeEJournal         ? 'e-journal'
      :                                         'Unknown';
}

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

    # Small tweak to make this easier to match, since these are all set up with four characters.
    if ( length($type) == 3 ) {
        $type .= ' ';
    }

    return
        $type eq kPurchaseTypePhysicalRetail ? 'Physical Retail'
      : $type eq kPurchaseTypeDownload       ? 'Download'
      : $type eq kPurchaseTypeSubscriptionDL ? 'Subscription'
      : $type eq kPurchaseTypeStream         ? 'Stream'
      : $type eq kPurchaseTypePrintOnDemand  ? 'POD'
      : $type eq kPurchaseTypeInAppPurchase  ? 'In App Purchase'
      : $type eq kPurchaseTypeLoan           ? 'Loan'
      :

      #$type eq kPurchaseTypeLibrarySale       ? 'Library' :
      $type eq kPurchaseTypePromotional
      ? 'Promotional'
      : 'Unknown';
}

sub GetAllWithoutPriceValidation {
    my $class  = shift;
    my %args   = @_;
    my $fileID = $args{file_id};
    my $saleID = $args{sale_id};

    my $sql =
        "SELECT sale.*, file.service_id, version_num FROM "
      . kTable
      . " INNER JOIN file USING (file_id) "
      . " WHERE (date_price_validated IS NULL OR date_price_validated = 0) "
      . " AND ( product_id IS NOT NULL OR product_id > 0 ) ";

    # Adding this exception so that we can validate a new file before changing its status to OPEN.
    $sql .= " AND file_status = " . BookPub::DB::Item::File::STATUS_OPEN() if !($fileID);

    $sql .= " AND file_id = " . $class->quote($fileID) if ($fileID);
    $sql .= " AND sale_id = " . $class->quote($saleID) if ($saleID);

    Common::Log::Debug("QUERY: $sql");
    return $class->SUPER::GetAll($sql);
}

sub GetAllWithPriceValidation {
    my $class           = shift;
    my %args            = @_;
    my $fileID          = $args{file_id};
    my $saleID          = $args{sale_id};
    my $priceTypeID     = $args{price_type_id};
    my $tolerance       = $args{tolerance};
    my $catalogImportID = $args{catalog_import_id};

    my $sql =
        "SELECT "
      . kTable
      . ".* FROM "
      . kTable
      . " INNER JOIN file USING (file_id) "
      . " LEFT JOIN sale_price_exception USING (sale_id) "
      . " WHERE date_price_validated > 0 "
      . " AND file_status = "
      . BookPub::DB::Item::File::STATUS_OPEN();

    $sql .= " AND file_id = " . $class->quote($fileID)            if ($fileID);
    $sql .= " AND sale_id = " . $class->quote($saleID)            if ($saleID);
    $sql .= " AND price_type_id = " . $class->quote($priceTypeID) if ($priceTypeID);
    $sql .= " AND variance < " . $class->quote($tolerance)        if ($tolerance);
    $sql .=
        " AND product_id IN (SELECT DISTINCT product_id FROM product_market "
      . " LEFT JOIN product_market_price USING (product_market_id) "
      . " WHERE product_market_price.catalog_import_id = "
      . $class->quote($catalogImportID) . ')'
      if ($catalogImportID);

    Common::Log::Debug("QUERY: $sql");
    return $class->SUPER::GetAll($sql);
}

sub LookupWithPriceException {
    my $class = shift;
    my $id    = shift;

    assert($id);

    my $sql = "SELECT * FROM " . kTable . " INNER JOIN sale_price_exception USING (sale_id) " . "WHERE sale_id = " . $class->quote($id);

    Common::Log::Debug("QUERY: $sql");

    my $collection = $class->SUPER::GetAll($sql);
    return $collection->next();
}

sub GetWithPriceExceptionByFileID {
    my ( $class, $fileID ) = @_;

    my $sql =
        "SELECT sale.revenue, sale.units, sale.conversion_rate FROM sale"
      . " INNER JOIN sale_price_exception USING (sale_id)"
      . " WHERE sale_price_exception.approved != 1"
      . " AND sale.file_id = " . $class->quote($fileID);

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

sub GetMinDate {
    my ( $class, $serviceID, $productID ) = @_;
    return undef if ( !$serviceID );

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT MIN(date_end) from " . kTable . " WHERE service_id = ?";
    my @sqlParams = ($serviceID);

    if ($productID) {
        $sql .= " AND product_id = ?";
        push @sqlParams, $productID;
    }

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    my ($date) = $sth->fetchrow_array();
    return $date;
}

# This is only used for a custom report for HBG
sub GetEbookCASales {
    my ( $class, $serviceID ) = @_;

    $serviceID = $class->quote($serviceID);
    my $sql =
        "SELECT sale.* FROM "
      . kTable
      . " LEFT JOIN book_product using (product_id)"
      . " WHERE sale.country_code = 'CA'"
      . " AND sale.service_id = $serviceID"
      . " AND imprint_id != 83"
      . " AND imprint_id != 31"
      . " AND sale.product_id IS NOT NULL"
      . " AND sale.is_free = \"N\""
      . " AND sale.revenue != 0"
      . " AND sale.date_end >= \"2010-05-01\""
      . " AND sale.date_end <  \"2012-12-01\""
      . " AND sale.purchase_type != 'pod'"
      . " AND product_type = 'EBOK'";

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

1;