#!/usr/bin/perl
use DBI;
use strict;
use warnings;
use Spreadsheet::ParseXLSX;
use POSIX qw(strftime);

my $NOWTIME = strftime("%Y-%m-%d %H:%M:%S", localtime);
my $driver = "Pg";
my $database = "db_cdrs";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "user_db";
my $password = "db2024";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
    or die $DBI::errstr;

my $stmt = qq(SELECT * from importations where traitement=0 order by id_import asc;);
my $sth = $dbh->prepare($stmt);
my $rv = $sth->execute() or die $DBI::errstr;
if ($rv < 0) {
    print $DBI::errstr;
}
while (my @row = $sth->fetchrow_array()) {
    print "ID = " . $row[0] . "\n";
    print "TYPE = " . $row[1] . "\n";
    print "fileName = " . $row[2] . "\n";
    print "Operateur =  " . $row[3] . "\n\n";

    my $nbr_lines = 0;
    my $id_import = $row[0];
    my $type_import = $row[1];
    my $fileName = $row[2];
    my $operateur = $row[3];

    $id_import =~ s/\s*$//;
    $type_import =~ s/\s*$//;
    $fileName =~ s/\s*$//;
    $operateur =~ s/\s*$//;

    if ($type_import eq 'cells') {
        print "----EXECUTER SQL---------\n\n";

        my $update = $dbh->prepare("UPDATE importations SET en_cours=1 WHERE id_import = ?");
        $update->execute($id_import);

        my $parser = Spreadsheet::ParseXLSX->new;
        my $workbook = $parser->parse($fileName);

        for my $worksheet ($workbook->worksheets()) {
            my ($row_min, $row_max) = $worksheet->row_range();

            for my $row ($row_min .. $row_max) {
                if ($row == 0) {
                    next;
                }

                if ($operateur eq 'IAM') {
                    my $technologie = $worksheet->get_name();
                    if ($technologie eq '2G') {
                        my $cellName = $worksheet->get_cell($row, 3)->value();
                        my $cellId = $worksheet->get_cell($row, 6)->value();
                        my $lac = $worksheet->get_cell($row, 5)->value();
                        my $cellIDLAC = $lac . $cellId;
                        my $longitude = $worksheet->get_cell($row, 11)->value();
                        my $latitude = $worksheet->get_cell($row, 10)->value();
                        my $azimuth = $worksheet->get_cell($row, 12)->value();
                        my $date_insert = $NOWTIME;

                        my @tmp = ($cellName, $cellId, $lac, $cellIDLAC, $longitude, $latitude, $azimuth, $date_insert, $operateur, $technologie);
                        my $insert = $dbh->prepare("INSERT INTO cells (cellname,cellid,lac,cellidlac,longitude,latitude,azimuth,date_insert,operateur,technologie) VALUES (?,?,?,?,?,?,?,?,?,?) ON CONFLICT DO NOTHING");
                        $insert->execute(@tmp);
                        print "@tmp\n";
                        $nbr_lines++;

                    }
                    if ($technologie eq '3G') {
                        my $cellName = $worksheet->get_cell($row, 4)->value();
                        my $cellId = $worksheet->get_cell($row, 7)->value();
                        my $lac = $worksheet->get_cell($row, 6)->value();
                        my $cellIDLAC = $lac . $cellId;
                        my $longitude = $worksheet->get_cell($row, 12)->value();
                        my $latitude = $worksheet->get_cell($row, 11)->value();
                        my $azimuth = $worksheet->get_cell($row, 13)->value();
                        my $date_insert = $NOWTIME;

                        my @tmp = ($cellName, $cellId, $lac, $cellIDLAC, $longitude, $latitude, $azimuth, $date_insert, $operateur, $technologie);
                        my $insert = $dbh->prepare("INSERT INTO cells (cellname,cellid,lac,cellidlac,longitude,latitude,azimuth,date_insert,operateur,technologie) VALUES (?,?,?,?,?,?,?,?,?,?) ON CONFLICT DO NOTHING");
                        $insert->execute(@tmp);
                        print "@tmp\n";
                        $nbr_lines++;

                    }
                    if ($technologie eq '4G') {
                        my $cellName = $worksheet->get_cell($row, 3)->value();
                        my $enodebid = $worksheet->get_cell($row, 1)->value();
                        my $cellId = $worksheet->get_cell($row, 4)->value();
                        my $cellIDLAC = $enodebid . $cellId;
                        my $longitude = $worksheet->get_cell($row, 10)->value();
                        my $latitude = $worksheet->get_cell($row, 9)->value();
                        my $azimuth = $worksheet->get_cell($row, 11)->value();
                        my $date_insert = $NOWTIME;

                        my @tmp = ($enodebid, $cellIDLAC, $cellName, $cellId, $longitude, $latitude, $azimuth, $date_insert, $operateur, $technologie);
                        my $insert = $dbh->prepare("INSERT INTO cells (enodebid,cellidlac,cellname,cellid,longitude,latitude,azimuth,date_insert,operateur,technologie) VALUES (?,?,?,?,?,?,?,?,?,?) ON CONFLICT DO NOTHING");
                        $insert->execute(@tmp);
                        print "@tmp\n";
                        $nbr_lines++;

                    }

                }
                elsif ($operateur eq 'ORANGE') {
                    my $cellName = $worksheet->get_cell($row, 4)->value();
                    my $cellId = $worksheet->get_cell($row, 5)->value();
                    if ($cellId eq '') {
                        $cellId = undef;
                    }
                    my $lac = $worksheet->get_cell($row, 6)->value();
                    my $cellIDLAC = $cellId;
                    if ($lac eq '') {
                        $lac = undef;
                        $cellIDLAC = $cellId;
                    }
                    else {
                        $cellIDLAC = $lac . $cellId;
                    }
                    my $technologie = $worksheet->get_cell($row, 9)->value();
                    my $enodebid = $worksheet->get_cell($row, 2)->value();
                    if ($enodebid eq '') {
                        $enodebid = undef;
                    }
                    if ($technologie eq '4G') {
                        $cellIDLAC = $enodebid . $cellId;
                    }

                    my $enodebid = $worksheet->get_cell($row, 2)->value();
                    if ($enodebid eq '') {
                        $enodebid = undef;
                    }
                    my $province_cell = $worksheet->get_cell($row, 12);
                    my $province_site = $province_cell ? $province_cell->value : undef;

                    my $adresse_site = $worksheet->get_cell($row, 14)->value();
                    my $ville_site = $worksheet->get_cell($row, 15)->value();
                    my $latitude = $worksheet->get_cell($row, 16)->value();
                    my $longitude = $worksheet->get_cell($row, 17)->value();
                    my $azimuth = $worksheet->get_cell($row, 18)->value();
                    if ($azimuth eq '') {
                        $azimuth = undef;
                    }
                    my $date_insert = $NOWTIME;

                    my @tmp = ($cellName, $cellId, $lac, $technologie, $cellIDLAC, $enodebid, $province_site, $adresse_site, $ville_site, $latitude, $longitude, $azimuth, $date_insert, $operateur);
                    my $insert = $dbh->prepare("INSERT INTO cells (cellname,cellid,lac,technologie,cellidlac,enodebid,province_site,adresse_site,ville_site,latitude,longitude,azimuth,date_insert,operateur) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT DO NOTHING");
                    $insert->execute(@tmp);
                    print "@tmp\n";
                    $nbr_lines++;
                }
                elsif ($operateur eq 'INWI') {
                    my $technologie = $row[4];
                    $technologie =~ s/\s*$//;

                    if ($technologie eq '2G') {
                        my $cellName = $worksheet->get_cell($row, 0)->value();
                        my $cellId = $worksheet->get_cell($row, 5)->value();
                        my $lac = $worksheet->get_cell($row, 4)->value();
                        my $cellIDLAC = $lac . $cellId;
                        my $latitude = $worksheet->get_cell($row, 1)->value();
                        if ($latitude eq 'Mobile') {
                            $latitude = undef;
                        }
                        my $longitude = $worksheet->get_cell($row, 2)->value();
                        if ($longitude eq 'Mobile') {
                            $longitude = undef;
                        }
                        my $azimuth = $worksheet->get_cell($row, 13)->value();
                        if ($azimuth eq 'Indoor') {
                            $azimuth = undef;
                        }
                        if ($azimuth eq 'Omni') {
                            $azimuth = undef;
                        }
                        if ($azimuth eq '-') {
                            $azimuth = undef;
                        }
                        my $province_site = $worksheet->get_cell($row, 19)->value();
                        my $adresse_site = $worksheet->get_cell($row, 22)->value();
                        my $ville_site = $worksheet->get_cell($row, 20)->value();
                        my $date_insert = $NOWTIME;

                        my @tmp = ($cellName, $cellId, $lac, $cellIDLAC, $longitude, $latitude, $azimuth, $province_site, $adresse_site, $ville_site, $date_insert, $operateur, $technologie);
                        my $insert = $dbh->prepare("INSERT INTO cells (cellname, cellid, lac, cellidlac, longitude, latitude, azimuth, province_site, adresse_site, ville_site, date_insert, operateur, technologie) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT DO NOTHING");
                        $insert->execute(@tmp);
                        print "@tmp\n";
                        $nbr_lines++;
                    }
                    if ($technologie eq '3G') {
                        my $cellName = $worksheet->get_cell($row, 4)->value();
                        my $cellId = $worksheet->get_cell($row, 5)->value();
                        my $lac = $worksheet->get_cell($row, 9)->value();
                        my $cellIDLAC = $lac . $cellId;
                        my $latitude = $worksheet->get_cell($row, 2)->value();
                        if ($latitude eq 'Mobile') {
                            $latitude = undef;
                        }
                        my $longitude = $worksheet->get_cell($row, 3)->value();
                        if ($longitude eq 'Mobile') {
                            $longitude = undef;
                        }
                        my $azimuth = $worksheet->get_cell($row, 10)->value();
                        if ($azimuth eq 'Indoor') {
                            $azimuth = undef;
                        }
                        if ($azimuth eq 'Omni') {
                            $azimuth = undef;
                        }
                        if ($azimuth eq '-') {
                            $azimuth = undef;
                        }
                        my $province_site = $worksheet->get_cell($row, 15)->value();
                        my $adresse_site = $worksheet->get_cell($row, 18)->value();
                        my $ville_site = $worksheet->get_cell($row, 16)->value();
                        my $date_insert = $NOWTIME;

                        my @tmp = ($cellName, $cellId, $lac, $cellIDLAC, $longitude, $latitude, $azimuth, $province_site, $adresse_site, $ville_site, $date_insert, $operateur, $technologie);
                        my $insert = $dbh->prepare("INSERT INTO cells (cellname, cellid, lac, cellidlac, longitude, latitude, azimuth, province_site, adresse_site, ville_site, date_insert, operateur, technologie) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT DO NOTHING");
                        $insert->execute(@tmp);
                        print "@tmp\n";
                        $nbr_lines++;
                    }
                    if ($technologie eq '4G') {
                        my $cellName = $worksheet->get_cell($row, 1)->value();
                        my $cellId = $worksheet->get_cell($row, 5)->value();
                        my $enodebid = $worksheet->get_cell($row, 4)->value();
                        my $cellIDLAC = $enodebid . $cellId;
                        my $latitude = $worksheet->get_cell($row, 2)->value();
                        if ($latitude eq 'Mobile') {
                            $latitude = undef;
                        }
                        my $longitude = $worksheet->get_cell($row, 3)->value();
                        if ($longitude eq 'Mobile') {
                            $longitude = undef;
                        }
                        my $azimuth = $worksheet->get_cell($row, 9)->value();
                        if ($azimuth eq 'Indoor') {
                            $azimuth = undef;
                        }
                        if ($azimuth eq 'Omni') {
                            $azimuth = undef;
                        }
                        if ($azimuth eq '-') {
                            $azimuth = undef;
                        }
                        my $province_site = $worksheet->get_cell($row, 14)->value();
                        my $adresse_site = $worksheet->get_cell($row, 17)->value();
                        my $ville_site = $worksheet->get_cell($row, 15)->value();
                        my $date_insert = $NOWTIME;

                        my @tmp = ($cellName, $cellId, $enodebid, $cellIDLAC, $longitude, $latitude, $azimuth, $province_site, $adresse_site, $ville_site, $date_insert, $operateur, $technologie);
                        my $insert = $dbh->prepare("INSERT INTO cells (cellname, cellid, enodebid, cellidlac, longitude, latitude, azimuth, province_site, adresse_site, ville_site, date_insert, operateur, technologie) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT DO NOTHING");
                        $insert->execute(@tmp);
                        print "@tmp\n";
                        $nbr_lines++;
                    }
                }
            }
        }
        $update = $dbh->prepare("UPDATE importations SET traitement=1, date_traitement = ?, nbr_lines = ? WHERE id_import = ?");
        $update->execute($NOWTIME, $nbr_lines, $id_import);
    }
}
$sth->finish;
$dbh->disconnect;