package Support::DataImport::RPSDataImport;

use strict;

use Sys::Hostname;

use Data::Dumper;
use POSIX qw/strftime/;

use lib '/app/tools/common/lib';
use Common::RSDB;
use Common::RSApp;
use Common::Util qw(normalize_upc clean_name);
use Common::Log;

use constant kFileNotIdentified => 'File not identified';
use constant kInvalidFileType   => 'Invalid filetype';

binmode STDOUT, ":utf8";

Log->init();

use Common::FormObject;
use base 'Common::FormObject';

use Spreadsheet::XLSX;

use constant kImportScript => '/app/tools/rps/bin/misc/run_import.pl';

use constant kImportRoot  => '/app/tools/support/lib/Support/DataImport/ImportTemplates';
use constant kReadOnlyLog => 'debug_ro.txt';
use constant kExecuteLog  => 'debug.txt';

use constant kArtistPayeeTemplate            => 1;
use constant kArtistPayeeExpenseTemplate     => 2;
use constant kArtistPayeeTransactionTemplate => 3;

use constant kArtistContractTermsTemplate    => 4;
use constant kArtistContractProductsTemplate => 5;
use constant kLicenseIncomeTypesTemplate     => 6;
use constant kLicensingIncomeTemplate        => 7;

use constant kUSPublisherPayeeTemplate       => 8;
use constant kUSPublisherPayeeTransactions   => 9;
use constant kUSMechanicalLicenseTemplate    => 10;

use constant kCAPublisherPayeeTemplate       => 11;
use constant kCAMechanicalLicenseTemplate    => 12;

use constant kLabelPayeeTransactions         => 13;
use constant kAddAddionalContractTerms       => 14;
use constant kArtistHistoricalReserves       => 15;

use constant kPriceTable                     => 16;
use constant kProductPrices                  => 17;
use constant kUSLicenseBalances              => 18;
use constant kUSMechanicalHistoricalReserves => 19;
use constant kArtistReserveAdjustments       => 20;

my %gImportType = (
    kArtistPayeeTemplate()            => 'Artist Payee',
    kArtistPayeeExpenseTemplate()     => 'Artist Payee Expense',
    kArtistPayeeTransactionTemplate() => 'Artist Payee Transaction',
    kArtistContractTermsTemplate()    => 'Artist Contract Terms',
    kArtistContractProductsTemplate() => 'Artist Contract Products',
    kLicenseIncomeTypesTemplate()     => 'License Income Types',
    kLicensingIncomeTemplate()        => 'Licensing Income',
    kUSPublisherPayeeTemplate()       => 'US Publisher Payee',
    kUSPublisherPayeeTransactions()   => 'US Publisher Payee Transactions',
    kUSMechanicalLicenseTemplate()    => 'US Mechanical License',
    kCAPublisherPayeeTemplate()       => 'CA Publisher Payee',
    kCAMechanicalLicenseTemplate()    => 'CA Mechanical License',
    kLabelPayeeTransactions()         => 'Label Payee Transactions',
    kAddAddionalContractTerms()       => 'Additional Contract Terms',
    kArtistHistoricalReserves()       => 'Artist Historical Reserves',
    kPriceTable()                     => 'Price Table',
    kProductPrices()                  => 'Product Prices',
    kUSLicenseBalances()              => 'US License Balances',
    kUSMechanicalHistoricalReserves() => 'US License Historical Reserves',
    kArtistReserveAdjustments()       => 'Artist Reserve Adjustments',
);

my %dispatch = (
    kArtistPayeeTemplate() => {
        base     => "artist_payees",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::ArtistPayeeTemplate',
    },
    kArtistPayeeExpenseTemplate() => {
        base     => "artist_payee_expenses",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::ExpenseTemplate',
    },
    kArtistPayeeTransactionTemplate() => {
        base     => "artist_payee_transactions",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::ArtistPayeeTransactionTemplate',
    },
    kArtistContractTermsTemplate() => {
        base     => "artist_contract_terms",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::ArtistContractTemplate',
    },
    kArtistContractProductsTemplate() => {
        base     => "artist_contract_products",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::ArtistContractTemplateTwo',
    },
    kLicenseIncomeTypesTemplate() => {
        base     => "license_income_types",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::LicenseIncomeTemplate',
    },
    kLicensingIncomeTemplate() => {
        base     => "licensing_income",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::LicensingIncomeTemplate',
    },
    kUSPublisherPayeeTemplate() => {
        base     => "us_publisher_payees",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::PublisherPayeeTemplate',
    },
    kUSPublisherPayeeTransactions() => {
        base     => "us_publisher_transactions",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::USPublisherPayeeTransaction',
    },
    kCAPublisherPayeeTemplate() => {
        base     => "ca_publisher_payees",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::CAPublisherPayeeTemplate',
    },
    kUSMechanicalLicenseTemplate() => {
        base     => "us_license_template",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::LicenseTemplate_v2',
    },
    kCAMechanicalLicenseTemplate() => {
        base     => "ca_license_template",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::CALicenseTemplate',
    },
    kLabelPayeeTransactions() => {
        base     => "label_transactions",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::LabelPayeeTransactionTemplate',
    },
    kAddAddionalContractTerms() => {
        base     => "additional_contract_terms",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::ArtistContractAddTerms',
    },
    kArtistHistoricalReserves() => {
        base     => "artist_reserves",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::ArtistReservesTemplate',
    },
    kPriceTable() => {
        base     => "price_table",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::PriceTemplate',
    },
    kProductPrices() => {
        base     => "product_prices",
        script   => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::ProductPriceTemplate',
    },
    kUSLicenseBalances() => {
        base => "us_license_balances",
        script => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::LicenseBalance',
    },  
    kUSMechanicalHistoricalReserves() => {
        base => "us_license_reserves",
        script => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::LicenseReservesTemplate',
    },  
    kArtistReserveAdjustments() => {
        base => "artist_reserve_adjustments",
        script => kImportScript,
        makefile => "makefile.in",
        codeName => 'Support::Implementation::LiquidationAdjustmentTemplate',
    }   
);

sub GetTypeString {
    my $self = shift;
    my $importType = shift;
    return $gImportType{$importType};
}

sub CodeName {
    my $self = shift;
    my $serviceID = shift;
  
    my $codeName = $dispatch{$serviceID}{codeName};
    return undef unless $codeName;
  
    return $codeName;
}

sub getDispatch {
    return %dispatch;
}

sub PreParse {
    my $self = shift;
    my $filename = shift;
    my %status;

    my $excel;
    if ( $filename =~ /.xlsx$/i ) {
        $excel = Spreadsheet::XLSX->new($filename);
    } elsif ( $filename =~ /.xls$/i ) {
        my $oExcel = new Spreadsheet::ParseExcel;
        $excel = $oExcel->Parse($filename);
    } else {
        print STDERR "ERROR - file($filename) Only Excel .xls/.xlsx templates are supported !!!\n";
        $status{errstr} = kInvalidFileType;
        return \%status;
    }

    # Keep it simple -- only process the first sheet

    my $importType;
    my $importVersion;
    foreach my $sheet (@{$excel->{Worksheet}})
    {
        ( $importType, $importVersion ) = $self->_identify( sheet => $sheet, filename => $filename, row => 0 );
        last if( $importType );
    }
    if ( $importType ) {
        $status{service_id}  = $importType;
        $status{version_num} = $importVersion;
        $status{service}     = $self->GetTypeString($importType);
    } else {
        $status{errstr} = kFileNotIdentified;
    }
    return \%status;

}# PreParse

###
### ---    Subroutines Below    ---
###

sub _identify {
    my $self = shift;
    my ( %opt )  = @_;
    my $sheet    = $opt{sheet};
    my $row      = $opt{row};
    my $filename = $opt{filename};

    my $importType;
    my $importVersion;
    my $rules = [
        {
            import_type => kArtistPayeeTemplate,
            import_version => '1',
            header => [
                'artist-payee-name', 'client-account-no', 'status', 'address-1', 'address-2', 'address-3',
                'address-4', 'address-5', 'city', 'state-province', 'zip-postal', 'country', 'e[-]?mail',
                'phone', 'fax', 'tax-id', 'comments', 'statement-distribution', 'payor-name',
                'minimum[ ]?-payment', 'opening-balance',
            ],
        },
        {
            import_type => kArtistPayeeExpenseTemplate,
            import_version => '1',
            header => [ # columns A-N
                '\*Net Revenue \/ Expenses', '\*Expense Type', '\*Recoupable %',
                '\*Amount', 'Memo', 'RS Contract ID', '\*Contract Title', 'Payee Name',
                'RS Album ID', 'Catalog #', '\*Album Name', 'RS Track ID', 'ISRC',
                '\*Track Title',
            ],
        },
        {
            import_type => kArtistPayeeTransactionTemplate,
            import_version => '1',
            header => [
                '\*Artist Payee Name', 'Client Account #', 'RS Artist Payee ID', '\*Payor Name',
                'Client Payor #', 'RS Payor ID', '\*Type of Transaction', '\*Amount',
                '\*Transaction Date', 'Check #', 'Memo'
            ],
        },
        {
            import_type => kArtistContractTermsTemplate,
            import_version => '1',
            header => [
                'payee-name', 'contract-name', 'artist-name', 'payor-name', 'issue-date', 'contract-id',
                'term-start', 'term-end', 'reserve-percentage', 'p1', 'p2', 'p3', 'p4', 'p5', 'p6', 'p7',
                'p8', 'default-term', 'income-source', 'region', 'channel', 'price-tier', 'rate-type',
                'rate', 'rate-reduction', 'percent-of-sales', 'packaging', 'free-goods',
            ],
        },
        { # this is the exception report header, w/ optional columns
            import_type => kArtistContractTermsTemplate,
            import_version => '1',
            header => [
                'payee-client-account#',
                'payee-name', 'rs-payee-id', 'contract-name', 'artist-name', 'payor-name', 'issue-date', 'contract-id',
                'term-start', 'term-end', 'reserve-percentage', 'p1', 'p2', 'p3', 'p4', 'p5', 'p6', 'p7',
                'p8', 'Digital Reserves Taken', 'default-term', 'income-source', 'region', 'channel', 'price-tier', 'rate-type',
                'rate', 'rate-reduction', 'percent-of-sales', 'packaging', 'free-goods',
                'import-status', 'description'
            #    'payee-name', 'contract-name', 'artist-name', 'payor-name', 'issue-date', 'contract-id',
            #    'term-start', 'term-end', 'reserve-percentage', 'p1', 'p2', 'p3', 'p4', 'p5', 'p6', 'p7',
            #    'p8', 'default-term', 'income-source', 'region', 'channel', 'price-tier', 'rate-type',
            #    'rate', 'rate-reduction', 'percent-of-sales', 'packaging', 'free-goods',
            ],
        },
        {
            import_type => kArtistContractProductsTemplate,
            import_version => '1',
            header => [
                'contract-name', 'contract-id', 'rs-album-id', 'album-name', 'catalog-number',
                'album-crossed', 'rs-track-id', 'track-name', 'track-proration', 'track-crossed'
            ],
        },
        {
            import_type => kLicenseIncomeTypesTemplate,
            import_version => '1',
            header => [
                '\*Contract Title', 'Payee Name', 'Licensing Income Type', 'Licensing Income Net Revenue Rate %',
                'Recoupable Expense Type', 'Recoupable %'
            ],
        },
        {
            import_type => kLicensingIncomeTemplate,
            import_version => '1',
            header => [
                '\*Date', 'Units', '\*Amount', 'Catalog-#', '\*Album Name', 'ISRC Code', 'Track Name',
                'Client Contract ID', 'Contract Title', '\*Income Type', 'Memo',
            ],
        },
        {
            import_type => kCAPublisherPayeeTemplate,
            import_version => '1',
            filename => 'CAPublisher',
            header => [
                'publisher-name', 'client-account-no', 'status', 'type', 'admin', 'agent', 'address-1',
                'address-2', 'address-3', 'address-4', 'address-5', 'city', 'state-province',
                'zip-postal', 'country', 'e-mail', 'phone', 'fax', 'tax-id', 'comments', 'payor-name',
                'minimum-payment', 'opening-balance'
            ],
        },
        {
            import_type => kUSPublisherPayeeTemplate,
            import_version => '1',
            header => [
                'publisher-name', 'client-account-no', 'status', 'type', 'admin', 'agent', 'address-1',
                'address-2', 'address-3', 'address-4', 'address-5', 'city', 'state-province',
                'zip-postal', 'country', 'e-mail', 'phone', 'fax', 'tax-id', 'comments', 'payor-name',
                'minimum-payment', 'opening-balance'
            ],
        },
        {
            import_type => kUSPublisherPayeeTemplate,
            import_version => '2',  # XXX NEW - with affiliation column (see RSD-5048)
            header => [
                'publisher-name', 'client-account-no', 'status', 'type', 'admin', 'agent', 'address-1',
                'address-2', 'address-3', 'address-4', 'address-5', 'city', 'state-province',
                'zip-postal', 'country', 'e-mail', 'phone', 'fax', 'tax-id', 'affiliation',
                'comments', 'payor-name', 'minimum-payment', 'opening-balance'
            ],
        },
        {
            import_type => kUSPublisherPayeeTransactions,
            import_version => '1',
            header => [
                '\*US Publisher Name', 'Client Account #', 'RS US Publisher ID', 'US Admin Name',
                'US Agent Name', '\*Payor Name', 'Client Payor #', 'RS Payor ID', '\*Type of Transaction',
                '\*Amount', '\*Transaction Date', 'Check #', 'Memo',
            ],
        },
        { # 8/12/20 - issuer-song-id is CA-specific
            import_type => kCAMechanicalLicenseTemplate,
            import_version => '2',
            #filename => 'CA',
            header => [
                'album-name', 'catalog-#', 'track-name', 'isrc', 'track-mech-exempt', 'public-domain',
                'product-type', 'region', 'share-%', 'payor', 'royaltyshare-publisher-id#', 'publisher',
                'publisher-direct', 'issuer-license-id', 'issuer-song-id', 'status', 'crossed', 'license-date-sent',
                'license-date-received', 'license-date-issued', 'license-term-start', 'license-term-end',
                'rate-type', 'rate-%', 'rate-basis', 'lock-date', 'penny-rate', 'reserve-percent', 'p1',
                'p2', 'p3', 'p4', 'p5', 'p6', 'p7', 'p8', 'digital-reserves', 'percent-of-sales',
                'free-goods', 'misc-deduction', 'comments', 'license-opening-balance',
                'liquidated-units-sales-date', 'p0-liquidated-units', 'p1-liquidated-units',
                'p2-liquidated-units', 'p3-liquidated-units', 'p4-liquidated-units', 'p5-liquidated-units',
                'p6-liquidated-units', 'p7-liquidated-units'
            ],
        },
        {
            import_type => kUSMechanicalLicenseTemplate,
            import_version => '1',
            header => [
                'album-name', 'catalog-#', 'track-name', 'isrc', 'track-mech-exempt', 'public-domain',
                'product-type', 'region', 'share-%', 'payor', 'royaltyshare-publisher-id#', 'publisher',
                'publisher-direct', 'issuer-license-id', 'status', 'crossed', 'license-date-sent',
                'license-date-received', 'license-date-issued', 'license-term-start', 'license-term-end',
                'rate-type', 'rate-%', 'rate-basis', 'lock-date', 'penny-rate', 'reserve-percent', 'p1',
                'p2', 'p3', 'p4', 'p5', 'p6', 'p7', 'p8', 'digital-reserves', 'percent-of-sales',
                'free-goods', 'misc-deduction', 'comments', 'license-opening-balance',
                'liquidated-units-sales-date', 'p0-liquidated-units', 'p1-liquidated-units',
                'p2-liquidated-units', 'p3-liquidated-units', 'p4-liquidated-units', 'p5-liquidated-units',
                'p6-liquidated-units', 'p7-liquidated-units'
            ],
        },
        {
            import_type => kUSMechanicalLicenseTemplate,
            import_version => '1',
            header => [
                #'album-name', 'catalog-#', 'track-name', 'isrc', 'track-mech-exempt', 'public-domain',
                #'product-type', 'region', 'share-%', 'payor', 'royaltyshare-publisher-id#', 'publisher',
                #'publisher-direct', 'issuer-license-id', 'status', 'crossed', 'license-date-sent',
                #'license-date-received', 'license-date-issued', 'license-term-start', 'license-term-end',
                #'rate-type', 'rate-%', 'rate-basis', 'lock-date', 'penny-rate', 'reserve-percent', 'p1',
                #'p2', 'p3', 'p4', 'p5', 'p6', 'p7', 'p8', 'digital-reserves', 'percent-of-sales',
                #'free-goods', 'misc-deduction', 'comments', 'license-opening-balance',
                #'liquidated-units-sales-date', 'p0-liquidated-units', 'p1-liquidated-units',
                #'p2-liquidated-units', 'p3-liquidated-units', 'p4-liquidated-units', 'p5-liquidated-units',
                #'p6-liquidated-units', 'p7-liquidated-units',
                #'Error Code', 'import-status'

                'album-name', 'catalog-#', 'track-name', 'isrc', 'track-mech-exempt', 'public-domain',
                'product-type', 'region', 'share-%', 'payor', 'royaltyshare-publisher-id#', 'publisher',
                'publisher-direct', 'issuer-license-id', 'status', 'crossed', 'license-date-sent',
                'license-date-received', 'license-date-issued', 'license-term-start', 'license-term-end',
                'rate-type', 'rate-%', 'rate-basis', 'lock-date', 'penny-rate', 'reserve-percent', 'p1',
                'p2', 'p3', 'p4', 'p5', 'p6', 'p7', 'p8', 'digital-reserves', 'percent-of-sales',
                'free-goods', 'misc-deduction', 'comments', 'license-opening-balance',
                #'liquidated-units-sales-date', 'p0-liquidated-units', 'p1-liquidated-units',
                #'p2-liquidated-units', 'p3-liquidated-units', 'p4-liquidated-units', 'p5-liquidated-units',
                #'p6-liquidated-units', 'p7-liquidated-units', 'Error Code', 'import-status'
            ],
        },
        {
            import_type => kCAMechanicalLicenseTemplate,
            import_version => '1',
            header => [
                'album-name', 'catalog-#', 'track-name', 'isrc', 'track-mech-exempt', 'public-domain',
                'product-type', 'region', 'share-%', 'payor', 'royaltyshare-publisher-id#', 'publisher',
                'publisher-direct', 'issuer-license-id', 'issuer-song-ID', 'status', 'crossed',
                'license-date-sent',
                'license-date-received', 'license-date-issued', 'license-term-start', 'license-term-end',
                'rate-type', 'rate-%', 'rate-basis', 'lock-date', 'penny-rate', 'reserve-percent', 'p1',
                'p2', 'p3', 'p4', 'p5', 'p6', 'p7', 'p8', 'percent-of-sales',
                'free-goods', 'misc-deduction', 'comments', 'license-opening-balance',
            ],
        },
        {
            import_type => kLabelPayeeTransactions,
            import_version => '1',
            header => [
                '\*Payee Name', 'Client Account #', '\*Payor Name', '\*Type of Transaction',
                '\*Amount', 'Transaction Date', 'Check #', 'Memo'
            ],
        },
        {
            import_type => kAddAddionalContractTerms,
            import_version => '1',
            header => [
                '\*contract-title', 'contract-id', 'payee-client-account#', 'payee-name', 'rs-contract-id',
                '\*first-last-priority', 'source', 'region', 'channel', 'price-type', 'rate-type', 'rate',
                'rate-reduction', '%-of-sales', 'packaging', 'free-goods'
            ],
        },
        {
            import_type => kArtistHistoricalReserves,
            import_version => '1',
            header => [
                'album-title', 'catalog-number', 'contract-title', 'rs-contract-number', 'income-source',
                'channel', 'price-level', 'region', 'p0', 'p1', 'p2', 'p3', 'p4', 'p5', 'p6', 'p7'
            ],
        },
        {
            import_type => kPriceTable,
            import_version => '1',
            header => [
                'Price Name', 'Retail Price', 'Wholesale Price',
            ],
        },
        {
            import_type => kProductPrices,
            import_version => '1',
            header => [
                'album-id', 'album-title', 'album-artist', 'label-name', 'catalog-#', 'release-date', 'cd-upc', 'cd-full-price-name',
                'cd-midline-price-name', 'cd-budget-price-name', 'cd-default-price-point',
                'vinyl-upc', 'vinyl-full-price-name', 'vinyl-midline-price-name', 'vinyl-budget-price-name', 'vinyl-default-price-point',
                'dvd-upc', 'dvd-full-price-name', 'dvd-midline-price-name', 'dvd-budget-price-name', 'dvd-default-price-point',
                'dvdcd-upc', 'dvdcd-full-price-name', 'dvdcd-midline-price-name', 'dvdcd-budget-price-name', 'dvdcd-default-price-point'
            ],
        },
        {
            import_type => kUSLicenseBalances,
            import_version => '1',
            header => [
                'album-title', 'album-title-version', 'catalog-no', 'album-artist', 'label-name', 'track-title',
                'track-number', 'track-artist', 'track-minutes', 'track-seconds', 'isrc', 'license-type', 'product-type',
                'region', 'share', 'payor', 'publisher', 'rs-publisher-id', 'client-account-no', 'admin', 'agent',
                'publisher-direct', 'issuer-license-id', 'crossed', 'status', 'mechanical-exempt', 'date-sent',
                'date-received', 'date-issued', 'term-start', 'term-end', 'contract-title', 'track-cap', 'rate-type',
                'rate-percent', 'rate-basis', 'lock-date', 'penny-rate', 'reserve-percent', 'digital-reserves',
                'reserve-period-1', 'reserve-period-2', 'reserve-period-3', 'reserve-period-4', 'reserve-period-5',
                'reserve-period-6', 'reserve-period-7', 'reserve-period-8', 'percent-of-sales', 'free-goods',
                'misc-deduction', 'comments', 'license-date-created', 'license-created-by', 'license-date-modified',
                'license-modified-by', 'rs-license-id', 'Import to Current License Balance', 'Current License Balance Memo',
                'Import to Crossed License Balance', 'Crossed License Balance Memo'
            ],
        },
        {
            import_type => kUSMechanicalHistoricalReserves,
            import_version => '1',
            header => [
                'album-name', 'catalog-#', 'royaltyshare-album-id#', 'track-name', 'isrc', 'royaltyshare-track-id#',
                'royaltyshare-publisher-id#', 'publisher', 'royaltyshare-license-id#', 'license-product-type',
                'reserve-configuration', 'region', 'sales-date', 'p0', 'p1', 'p2', 'p3', 'p4', 'p5', 'p6', 'p7'
            ],
        },
        { # XXX you are here
            import_type => kArtistReserveAdjustments,
            import_version => '1',
            header => [
                'current-period', 'payor', 'rs-contract-id', 'contract-title', 'rs-album-id', 'album-name', 'rs-track-id',
                'track-name', 'source', 'current', 'P1', 'P2', 'P3', 'P4', 'P5', 'P6', 'P7', 'P8'
            ],
        },


    ];


    foreach my $rule ( @$rules ) {
        my $header = $rule->{header};
        my $itype  = $rule->{import_type};
        my $iname  = $gImportType{$itype};
        my $version = $rule->{import_version};

        print STDERR "## Examining rule for '$iname' version $version\n";

        if ( exists $rule->{filename} ) {
            # ignore rule if the filename must contain a specific pattern
            my $fpattern = $rule->{filename};
            print STDERR "D: filename($filename) must contain '$fpattern'\n";

            if ( $filename !~ /$fpattern/i ) {
                next;
            }

        }

        my $match=1;
        my $hcol=0;
        foreach my $hval (@$header) {

            my $cell = $sheet->{Cells}[$row][$hcol];
            my $val  = $cell->{Val};
            $val =~ s/\0//g; # shouldn't happen, but extended byte strings can be wonky

            Log->debug("RPSDataImport - D:[$hcol] Comparing '$val' to '$hval'");
            if ( $val !~ /$hval/ ) {
                $match = 0;
                Log->debug("  RPSDataImport:   NO_MATCH: '$val'  !=  '$hval'");
                last;
            }

            $hcol++;
        }

        if ( $match ) {
            $importType    = $rule->{import_type};
            $importVersion = $rule->{import_version};
            last;
        }

    }

    return ( $importType, $importVersion );

} # _identify


sub _init {
    my ( $self, %args ) = @_;
    $self->SUPER::_init(%args);

    my %properties;
    if ( $args{_dbItem} ) {
        $self->_propertiesFromDBItem( \%properties, $args{_dbItem} );
    } elsif( $args{importID} ) {
        $self->_propertiesFromDB( \%properties, $args{importID} );
    } else {
        $self->_propertiesFromDefaults( \%properties );
    }

    $self->_initProperties( \%properties );
    $self->_initSubs( \%properties );

}

sub _propertiesFromDB {
    my ( $self, $hrProperties, $id ) = @_;
    my $dbItem = Support::DB::Item::DataImport->Lookup( import_id => $id );
    $self->_propertiesFromDBItem( $hrProperties, $dbItem );
}

sub _propertiesFromDBItem {
    my ( $self, $hrProperties, $dbItem ) = @_;
    $hrProperties->{import_id} = $dbItem->import_id;
    $hrProperties->{case_id} = $dbItem->case_id;
    $hrProperties->{service_id} = $dbItem->service_id;
    $hrProperties->{version_num} = $dbItem->version_num;
    $hrProperties->{file_dir} = $dbItem->file_dir;
    $hrProperties->{file_name} = $dbItem->file_name;
    $hrProperties->{file_md5sum} = $dbItem->file_md5sum;
    $hrProperties->{file_status} = $dbItem->file_status;
    $hrProperties->{parent_import_id} = $dbItem->parent_import_id;
    $hrProperties->{records} = $dbItem->records;
    $hrProperties->{total_exceptions} = $dbItem->total_exceptions;
    $hrProperties->{notes} = $dbItem->notes;
    $hrProperties->{date_created} = $dbItem->date_created;
    $hrProperties->{date_modified} = $dbItem->date_modified;
}

sub _propertiesFromDefaults {
    my ( $self, $hrProperties ) = @_;
}

sub _initSubs {
    my ( $self, $hrProperties ) = @_;
}

sub _initProperties {
    my ( $self, $props ) = @_;

    $self->{ImportID} = Common::FormObject::Scalar->new( value => $props->{import_id}, readOnly => 1 );
    $self->{CaseID} = Common::FormObject::Scalar::String->new( value => $props->{case_id}, required => 1 );
}

sub _dumpRow {

    my ( %opt ) = @_;
    my $sheet = $opt{sheet};
    my $row   = $opt{row};

    my @values;
    foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol})
    {
        my $cell = $sheet->{Cells}[$row][$col];
        my $val  = $cell->{Val};
        print STDERR "D: r($row) c($col): val($val)\n";
        push @values, $val;
    }
#    print STDERR "\n" . join(', ', map { "'" . $_ . "'" } @values ) . "\n";

}# _dumpRow

sub usage {
    my $e = shift;
    return "ERROR: $e\nUsage: rps_dataimport.pl -c clientID -f templateToImport\n";
}

1;