#!/usr/bin/perl -w

###############################################################################
#
# A test for Spreadsheet::WriteExcel.
#
# Tests for the packed formula strings used in the Excel DV structure
# as part of data validation.
#
# reverse('©'), September 2008, John McNamara, jmcnamara@cpan.org
#


use strict;

use Spreadsheet::WriteExcel;
use Test::More tests => 12;


###############################################################################
#
# Tests setup
#
my $test_file           = "temp_test_file.xls";
my $workbook            = Spreadsheet::WriteExcel->new($test_file);
my $worksheet           = $workbook->add_worksheet();
my $worksheet2          = $workbook->add_worksheet();
my $target;
my $result;
my $caption;

my $formula;
my @bytes;



###############################################################################
#
# Test 1 Integer values.
#
$formula      = '10';

$caption    = " \tData validation: _pack_dv_formula('$formula')";
@bytes      = qw(
                    03 00 00 E0 1E 0A 00
                );

# Zero out Excel's random unused word to allow comparison.
$bytes[2]   = '00';
$bytes[3]   = '00';
$target     = join " ", @bytes;

$result     = unpack_record($worksheet->_pack_dv_formula($formula));
is($result, $target, $caption);


###############################################################################
#
# Test 2 Decimal values.
#
$formula      = '1.2345';

$caption    = " \tData validation: _pack_dv_formula('$formula')";
@bytes      = qw(
                    09 00 E0 3F 1F 8D 97 6E 12 83 C0 F3 3F
                );

# Zero out Excel's random unused word to allow comparison.
$bytes[2]   = '00';
$bytes[3]   = '00';
$target     = join " ", @bytes;

$result     = unpack_record($worksheet->_pack_dv_formula($formula));
is($result, $target, $caption);


###############################################################################
#
# Test 3 Date values..
#
$formula      = $worksheet->convert_date_time('2008-07-24T');

$caption    = " \tData validation: _pack_dv_formula('2008-07-24')";
@bytes      = qw(
                    03 00 E0 3F 1E E5 9A
                );

# Zero out Excel's random unused word to allow comparison.
$bytes[2]   = '00';
$bytes[3]   = '00';
$target     = join " ", @bytes;

$result     = unpack_record($worksheet->_pack_dv_formula($formula));
is($result, $target, $caption);


###############################################################################
#
# Test 4 Time values.
#
$formula      = $worksheet->convert_date_time('T12:00');

$caption    = " \tData validation: _pack_dv_formula('12:00')";
@bytes      = qw(
                    09 00 E0 3F 1F 00 00 00 00 00 00 E0 3F
                );

# Zero out Excel's random unused word to allow comparison.
$bytes[2]   = '00';
$bytes[3]   = '00';
$target     = join " ", @bytes;

$result     = unpack_record($worksheet->_pack_dv_formula($formula));
is($result, $target, $caption);


###############################################################################
#
# Test 5 Cell reference value.
#
$formula      = '=C9';

$caption    = " \tData validation: _pack_dv_formula('$formula')";
@bytes      = qw(
                    05 00 E0 3F 44 08 00 02 C0
                );

# Zero out Excel's random unused word to allow comparison.
$bytes[2]   = '00';
$bytes[3]   = '00';
$target     = join " ", @bytes;

$result     = unpack_record($worksheet->_pack_dv_formula($formula));
is($result, $target, $caption);


###############################################################################
#
# Test 6 Cell reference value.
#
$formula      = '=E3:E6';

$caption    = " \tData validation: _pack_dv_formula('$formula')";
@bytes      = qw(
                    09 00 0C 00 25 02 00 05 00 04 C0 04 C0
                );

# Zero out Excel's random unused word to allow comparison.
$bytes[2]   = '00';
$bytes[3]   = '00';
$target     = join " ", @bytes;

$result     = unpack_record($worksheet->_pack_dv_formula($formula));
is($result, $target, $caption);


###############################################################################
#
# Test 7 Cell reference value.
#
$formula      = '=$E$3:$E$6';

$caption    = " \tData validation: _pack_dv_formula('$formula')";
@bytes      = qw(
                    09 00 0C 00 25 02 00 05 00 04 00 04 00
                );

# Zero out Excel's random unused word to allow comparison.
$bytes[2]   = '00';
$bytes[3]   = '00';
$target     = join " ", @bytes;

$result     = unpack_record($worksheet->_pack_dv_formula($formula));
is($result, $target, $caption);


###############################################################################
#
# Test 8 Cell reference value.
#
$formula      = '=$E$3:$E$6';

$caption    = " \tData validation: _pack_dv_formula('$formula')";
@bytes      = qw(
                    09 00 0C 00 25 02 00 05 00 04 00 04 00
                );

# Zero out Excel's random unused word to allow comparison.
$bytes[2]   = '00';
$bytes[3]   = '00';
$target     = join " ", @bytes;

$result     = unpack_record($worksheet->_pack_dv_formula($formula));
is($result, $target, $caption);


###############################################################################
#
# Test 9 List values.
#
$formula      = ['a', 'bb', 'ccc'];

$caption    = " \tData validation: _pack_dv_formula(['a', 'bb', 'ccc'])";
@bytes      = qw(
                    0B 00 0C 00 17 08 00 61 00 62 62 00 63 63 63
                );

# Zero out Excel's random unused word to allow comparison.
$bytes[2]   = '00';
$bytes[3]   = '00';
$target     = join " ", @bytes;

$result     = unpack_record($worksheet->_pack_dv_formula($formula));
is($result, $target, $caption);


###############################################################################
#
# Test 10 Empty string.
#
$formula      = '';

$caption    = " \tData validation: _pack_dv_formula('')";
@bytes      = qw(
                    00 00 00
                );

# Zero out Excel's random unused word to allow comparison.
$bytes[2]   = '00';
$bytes[3]   = '00';
$target     = join " ", @bytes;

$result     = unpack_record($worksheet->_pack_dv_formula($formula));
is($result, $target, $caption);


###############################################################################
#
# Test 11 Undefined value.
#
$formula      = undef;

$caption    = " \tData validation: _pack_dv_formula(undef)";
@bytes      = qw(
                    00 00 00
                );

# Zero out Excel's random unused word to allow comparison.
$bytes[2]   = '00';
$bytes[3]   = '00';
$target     = join " ", @bytes;

$result     = unpack_record($worksheet->_pack_dv_formula($formula));
is($result, $target, $caption);




###############################################################################
#
# Test 10 List values (with a utf8 string).
#
SKIP: {

skip " \_pack_dv_string(). Test requires Perl 5.8 Unicode support.", 1
     if $] < 5.008;

my $euro    = chr 0x20Ac;
$formula    = ['a', 'bb', 'ccc', $euro];

$caption    = " \tData validation: _pack_dv_formula(['a', 'bb', 'ccc', utf8])";
@bytes      = qw(
                    17 00 0C 00 17 0A 01 61 00 00 00 62 00 62 00 00 00
                    63 00 63 00 63 00 00 00 AC 20
                );

# Zero out Excel's random unused word to allow comparison.
$bytes[2]   = '00';
$bytes[3]   = '00';
$target     = join " ", @bytes;

$result     = unpack_record($worksheet->_pack_dv_formula($formula));
is($result, $target, $caption);

}

# TODO
# Test failing reference to Sheet2!A1
# Test for formula string > 255 chars
# $formula      = ['a' x 256];




###############################################################################
#
# Unpack the binary data into a format suitable for printing in tests.
#
sub unpack_record {
    return join ' ', map {sprintf "%02X", $_} unpack "C*", $_[0];
}


# Cleanup
$workbook->close();
unlink $test_file;


__END__