83 lines
2.1 KiB
Perl
83 lines
2.1 KiB
Perl
#!/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');
|