#!/usr/bin/env perl
#===============================================================================
#
#         FILE: SQL2XLS.pl
#
#  DESCRIPTION: script de démo du module SQL2XLS.pm
#
#      OPTIONS: ---
# REQUIREMENTS: ---
#         BUGS: ---
#        NOTES: ---
#       AUTHOR: grouch'
# ORGANIZATION:
#      VERSION: 1.0
#      CREATED: 27/03/17
#   CHANGE_LOG:
#
#===============================================================================

use Carp;
use DBI;
use Data::Dumper;
use lib ('Z:/GRUSZKA/SQL2XLS');
use SQL2XLS;
use Modern::Perl;
use Spreadsheet::WriteExcel;

#-------------------------------------------------------------------------------
#  DATABASE
#-------------------------------------------------------------------------------
my $db = {
    user     => 'postgres'
   ,password => 'postgres'
   ,host     => 'localhost'
   ,port     => '5432'
   ,dbname   => 'TRIDIM_TEST01'
};

#-------------------------------------------------------------------------------
#  Requête SQL
#-------------------------------------------------------------------------------
my $prog = 'ATO045';
my $datedebut = '20000101000000';
#my $datedebut = '20170201000000';
my $datefin   = '20191231235959';

# une première requête
my $sql1 = qq{SELECT
        MPOS
        ,NOM
        ,MINI
        ,MAXI
        ,MES_BAR_BAR
        ,Pp
        ,Ppk
        ,NbPieces
        ,nb_total_occurences

        ,CRITICAL
        ,MAJOR
        ,ICD
        ,COMMENTAIRE_DCM


        FROM calcul_capa($datedebut, $datefin, '$prog')

        WHERE MINI notNULL AND MAXI notNULL
        --AND Pp > 1.33 AND Ppk > 1
        };

#une deuxième requête
my $sql2 = qq{SELECT * FROM DATA WHERE PROG = '$prog'};

#la sub prend une ref de hash en argument
# nom_de_l'onglet => requete_sql
my $requete = {"capa" => $sql1, "data" => $sql2};

#-------------------------------------------------------------------------------
#  APPEL SUB
#-------------------------------------------------------------------------------

SQL2XLS::SQL2XLS($db, $requete, 'toto.xlsx');