import.pl

#!/usr/bin/perl

=pot
VERSION 1.1
MODIFIED TO WORK WITH SHITTY WINDOWS

REQUIRES:
    Spreadsheet::XLSX
    Text::CSV::Encoded
    Email::Valid
    Time::Piece

NOTE:
    Even thouth the output encoding is iso-8859-1 Windows reads it as UTF-8 no BOM; I don't understand the world no more.

=cut

use warnings;
use strict;
use Cwd qw(abs_path);
use File::Basename qw(dirname);
use feature qw(switch);
use Spreadsheet::XLSX;
use Text::CSV::Encoded;
use Email::Valid;
use Time::Piece;
use Encode qw(decode encode);
use utf8;
binmode(STDOUT, ":utf8");

sub trim {
    (my $s = $_[0]) =~ s/^\s+|\s+$//g;
    return $s;
}

my $dirname = dirname(abs_path($0));

my $excel = Spreadsheet::XLSX->new($dirname . '/import.xlsx');

my $csv_file = $dirname . '/import.csv';
open(my $csv_FH, '>:encoding(iso-8859-1)', $csv_file) or die $!;
my $csv = Text::CSV::Encoded->new({
    eol => $/,
    sep_char => ';',
    encoding_out => 'iso-8859-1'
});
my @column_names = qw(email datum_sodelovanja(YYYY-MM-DD) ime_podjetja ime priimek spol1=m,2=z,3=nedefiniran vir_pridobitve datum_rojstva(YYYY-MM-DD) telefon naslov kraj postna_stevilka opombe);
$csv->column_names(@column_names);
$csv->print($csv_FH, [@column_names]);

foreach my $sheet (@{$excel->{Worksheet}}) {
    $sheet->{MaxRow} ||= $sheet->{MinRow};

    # the rows
    foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
    $sheet->{MaxCol} ||= $sheet->{MinCol};

    my %row_data;
    my $email = 0;

    # the columns of a single row
    foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
        my $cell = $sheet->{Cells}[$row][$col];

        # the cell in the column
        if ($cell) {
        given ($col) {
            when (0) { $row_data{'ime'} = ucfirst(decode('iso-8859-1', $cell->{Val})); }
            when (1) { $row_data{'priimek'} = ucfirst(decode('iso-8859-1', $cell->{Val})); }
            when (2) {
            if (Email::Valid->address($cell->{Val})) {
                $row_data{'email'} = $cell->{Val};
                $email = 1; # email checker variable
            }
            }
            when (3) {
                        if (trim($cell->{Val}) ne '') {
                            my $post_num = $cell->{Val};
                            my $post_name = $cell->{Val};
                            $post_num =~ s/\D+//;
                            $row_data{'postna_stevilka'} = $post_num;
                            $post_name =~ s/\d+//;
                            $row_data{'kraj'} = ucfirst(trim(decode('iso-8859-1', $post_name)));
                            undef $post_num;
                            undef $post_name;
                        }
            }
            when (4) {
            given ($cell->{Val}) {
                when ('m') { $row_data{'spol'} = 1; }
                when ('w') { $row_data{'spol'} = 2; }
                default { $row_data{'spol'} = 3; }
            }
            }
            when (8) {
                        if (trim($cell->{Val}) ne '') {
                            my $participation_date = Time::Piece->strptime($cell->{Val}, '%Y-%m-%d %H:%M:%S');
                            $row_data{'datum_sodelovanja'} = $participation_date->strftime('%Y-%m-%d');
                            undef $participation_date;
                        }
            }
        }
        }
    }

    if ($email != 0) {
        # only write it if the email is valid
        $csv->print($csv_FH, [
        $row_data{'email'},
        $row_data{'datum_sodelovanja'},
        '', # ime podjetja
        $row_data{'ime'},
        $row_data{'priimek'},
        $row_data{'spol'},
        '', # vir vpišemo ročno!
        '', # datum rojstva
        '', # telefon
        '', # naslov
        $row_data{'kraj'},
        $row_data{'postna_stevilka'},
        '' # opombe
        ]);
    }

        undef %row_data;
        undef $email;
    }
}

close($csv_FH);
exit;