Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/bin/perl
- use strict;
- use utf8;
- use CGI qw/:standard/;
- use CGI::Carp 'fatalsToBrowser';
- use DBI;
- use Math::Combinatorics;
- use GD::Graph::bars;
- #======== CONFIGURE ========
- my %data = (
- user => 'root',
- passwd => 'xxx',
- database => 'meccaniche',
- tabella => 'monotab',
- host => 'localhost',
- dbfile => 'ateneo',
- script => 'ate4.pl',
- yes_color => 'green',
- no_color => 'red',
- form_color => 'blue',
- cell_color => 'white',
- script => 'ate4.pl',
- manoscr => [ qw(B1 C D E1 E2 F F2 H1 H2 K L1 L2 L3 L7 L8 L9 M N N1 O2 P P2 P3 P4 P5 P7 P8 P9 P10 P11 R Th V V1 V2 V4) ],
- );
- #======== MAIN ========
- if (param('runmode') eq 'gif') {gif();exit;}
- #http_activate();
- db_connect();
- start_html();
- if (param('runmode') eq 'combo' && param('2o3') eq 'coppie') {combo(2);}
- elsif (param('runmode') eq 'combo' && param('2o3') eq 'terne') {combo(3);}
- elsif (param('runmode') eq 'compara') {compara();}
- elsif (param('runmode') eq 'cerca') {cerca_stringa();}
- else {body_html();}
- if (param('runmode') ne '') {
- svolgi_query();
- tabella();
- prepara_dati_gif();
- stampa_gif();}
- end_html();
- #======== SUB ========
- ####################################################################
- # #
- # Sub per l'attivazione dei server #
- # #
- ####################################################################
- sub db_connect {
- my $db_source = "DBI:mysql:database=$data{database};host=$data{host}";
- $data{dbh} = DBI->connect( $db_source, $data{user}, $data{passwd} ) or die "Database connection not made: $DBI::errstr";
- }
- ####################################################################
- # #
- # Le Sub per la generazione della query #
- # #
- ####################################################################
- sub compara {
- my (@crit_m, @crit_c, @crit_t, @crit_0);
- my $orig = "compara";
- $data{orig} =$orig;
- my @values = @{ $data{manoscr} };
- my %hash = map { ;"$_" => scalar(param("s$_")) } @values;
- foreach my $val (keys %hash) {
- if ($hash{$val} eq '') {
- delete $hash{$val};
- } else {
- }
- }
- my @ar;
- my @ar_cri;
- foreach my $chiave (sort keys %hash) {
- push (@ar, "$chiave = '$hash{$chiave}' AND\n");
- if ($hash{$chiave} eq "t") {push (@crit_t, $chiave);}
- elsif ($hash{$chiave} eq "m") {push (@crit_m, $chiave);}
- elsif ($hash{$chiave} eq "c") {push (@crit_c, $chiave);}
- elsif ($hash{$chiave} eq "0") {push (@crit_0, $chiave);}
- }
- ########## Formattazione del criterio di ricerca ############
- $data{crit_t} = join(', ', @crit_t);
- $data{crit_m} = join(', ', @crit_m);
- $data{crit_c} = join(', ', @crit_c);
- $data{crit_0} = join(', ', @crit_0);
- my $criterio = "<br><b><i>Si\':</b><font color=$data{yes_color}>$data{crit_t}</font><br><b>No:</b><font color=$data{no_color}>$data{crit_0}</font></i>";
- ########## Aggiunta alla query dell'opzione lezione esatta/errata ############
- my $cerca = join(' ', @ar);
- my $esaerra;
- my $esa = param('esatta');
- my $erra = param('errata');
- my $query_p3;
- $cerca=~ s/ AND$//;
- my $query_p1= "SELECT distinct id_pc FROM $data{tabella}\n WHERE ";
- if ($cerca && ($esa eq 'esa') && ($erra eq 'err')) {
- $query_p3=' AND esaerra >= 0';
- $esaerra="esaerra";
- } elsif ($cerca && ($esa eq 'esa')) {
- $query_p3=' AND esaerra = 0';
- $esaerra="esa";
- } elsif ($cerca && ($erra eq 'err')) {
- $query_p3=' AND esaerra > 0';
- $esaerra="erra";
- } else {
- }
- $data{esaerra} = $esaerra;
- $data{cerca} = $cerca;
- $data{query} = $query_p1.$cerca.$query_p3;
- $data{criterio} = $criterio;
- }
- sub cerca_stringa {
- my $orig = "cerca";
- $data{orig} =$orig;
- my $criterio = param('cerca');
- my $pre= "\n\nSELECT distinct id_pc FROM $data{tabella} WHERE \n";
- my $luo="(id_pc ='". $criterio ."'\nOR\n";
- my $lin="posizione ='". $criterio ."'\nOR\n";
- my $forma="forma LIKE '%". $criterio ."%'\nOR\n";
- my $tipo="tipo LIKE '%". $criterio ."%'\)\n";
- my $cerca = $luo . $lin . $forma .$tipo;
- my $query= $pre .$cerca;
- $data{cerca} = $cerca;
- $data{query} = $query;
- $data{criterio} = $criterio;
- }
- sub combo {
- my $orig = "combo";
- $data{orig} =$orig;
- my $count = shift;
- my @m = @{ $data{manoscr} };
- my $combinat = Math::Combinatorics->new(count => $count, data => [@m] );
- while (my @combo = $combinat->next_combination) {
- $data{combo} = \@combo;
- crea_combo_queries();
- }
- prepara_dati_gif();
- stampa_gif();
- end_html();
- exit;
- }
- sub crea_combo_queries {
- my $pass2;
- my $cerca = param('menu');
- my @combo = @{ $data{combo} };
- my @m = @{ $data{manoscr} };
- if((grep {$_ eq $cerca} @combo) || $cerca eq 'tutte') {
- my $criterio = join(' ', @combo);
- my @crit_t=();
- my @crit_0=();
- my @ar=();
- my $query;
- my %combina=();
- foreach my $m (@m) {
- if (grep {$m eq $_} @combo) {
- $combina{$m}="t";
- } else {
- $combina{$m}="0";
- }
- }
- my @keys = sort_alfanum(keys %combina);
- foreach my $key (@keys) {
- push (@ar, "$key = \'$combina{$key}\' AND \n");
- }
- $data{cerca} = join(' ', @ar);
- my $pre= "SELECT distinct id_pc FROM $data{tabella} WHERE \n";
- $data{cerca}=~ s/ AND\s*$/ /;
- $query = $pre .$data{cerca};
- $data{query} = $query;
- $data{criterio} = join(', ', @combo);
- svolgi_query();
- tabella();
- } else {
- }
- }
- ####################################################################
- # #
- # Le Sub per la generazione dei dati da passare al grafico #
- # #
- ####################################################################
- sub prepara_dati_gif {
- my $serba = $data{tutte_colored};
- my @ariserba2 = split(/, /, $serba);
- foreach my $value (@ariserba2) {
- $value =~ s/^<b><font.+?>(.+?)<\/font><\/b>$/$1/;
- $value =~ s/[cm]$//;
- }
- my $query_gif;
- my %hash_data;
- $hash_data{$_}++ foreach @ariserba2;
- while (my ($key, $value) = each (%hash_data)) {
- my $elem="$key=$value&";
- $query_gif = $query_gif.$elem;
- }
- $data{query_gif} = $query_gif;
- }
- ####################################################################
- # #
- # Le Sub per la generazione delle risposte: tabella e grafico #
- # #
- ####################################################################
- sub svolgi_query {
- my $query = $data{query};
- my $numero_match;
- my @tab_array;
- my $conta;
- my $pc_id_ref;
- my ($query_match, $match_ref, $query_esatta, $esatta_ref, $query_altrirec, $altrirec_ref);
- $data{numero_occ}=0;
- $data{dbh}->do("SET NAMES 'utf8'");
- $pc_id_ref = $data{dbh}->selectall_arrayref($query) or die "$DBI::errstr";
- my $max = calcola_max_col($pc_id_ref);
- if ($max>=0) {
- foreach my $riga_ref (@$pc_id_ref) {
- my $query_match = "SELECT * FROM $data{tabella} WHERE ".$data{cerca} ."AND id_pc = ".$riga_ref->[0];
- my $match_ref = $data{dbh}->selectall_arrayref($query_match) or die "$DBI::errstr";
- my $query_esatta = "SELECT * FROM $data{tabella} WHERE id_pc = ".$riga_ref->[0] ." AND id_varianti !=" .$match_ref->[0][2] ." AND esaerra =0";
- my $esatta_ref = $data{dbh}->selectall_arrayref($query_esatta) or die "$DBI::errstr";
- my $query_altrirec = "SELECT * FROM $data{tabella} WHERE id_pc = ".$riga_ref->[0] ." AND id_varianti !=" .$match_ref->[0][2] ." AND esaerra =1";
- my $altrirec_ref = $data{dbh}->selectall_arrayref($query_altrirec) or die "$DBI::errstr";
- my ($match_mss_list) =@$match_ref;
- my $colored_list = stampa_mss(@$match_mss_list);
- $data{tutte_colored} = $data{tutte_colored} .", " .$colored_list;
- if ($match_ref->[0][3] eq "0") {
- push (@tab_array, "<tr><td>$match_ref->[0][0]</td><td>$match_ref->[0][1]</td><td bgcolor=$data{cell_color}><font color=$data{form_color}>$match_ref->[0][4]</font> $colored_list</td>");
- } else {
- my ($esa_mss_list) =@$esatta_ref;
- my $list = stampa_mss(@$esa_mss_list);
- push (@tab_array, "<tr><td>$esatta_ref->[0][0]</td><td>$esatta_ref->[0][1]</td><td>$esatta_ref->[0][4] $list</td>");
- $conta++;
- push (@tab_array, "<td bgcolor=$data{cell_color}><font color=$data{form_color}>$match_ref->[0][4]</font> $colored_list</td>");
- }
- foreach my $riga2_ref (@$altrirec_ref) {
- my $list = stampa_mss(@$riga2_ref);
- push (@tab_array, "<td>$riga2_ref->[4] $list</td>");
- $conta++;
- }
- my $completa_riga = "<td> </td>" x ($max-$conta-1);
- $conta=0;
- push (@tab_array, "$completa_riga</tr>\n");
- $data{numero_occ} = scalar (@$pc_id_ref);
- $data{tab_array} = \@tab_array;
- }
- } else {
- no_results();
- }
- }
- sub calcola_max_col {
- my $pc_id_ref = shift;
- my $max=0;
- foreach my $riga_ref (@$pc_id_ref) {
- my $ary_ref = $data{dbh}->selectrow_arrayref("SELECT count(*) FROM monotab WHERE id_pc =" .$riga_ref->[0]) or die "$DBI::errstr";
- if ($ary_ref->[0] > $max) {
- $max = $ary_ref->[0];
- }
- $data{max} = $max-1;
- }
- return $max;
- }
- sub sort_alfanum{
- my @confused = @_;
- my @sorted =
- map { $_->[0].$_->[1] }
- sort { $a->[0] cmp $b->[0] or $a->[1] <=> $b->[1] }
- map { [/(\D*)(\d*)/] }
- @confused;
- return @sorted;
- }
- sub stampa_mss{
- my @values = @_;
- my @manoscr_valori = @values[6..42];
- my %mscr;
- my @list;
- my $joined;
- for (my $i=0; $i<36; $i++) {
- $mscr{$data{manoscr}->[$i]} = $manoscr_valori[$i] if $manoscr_valori[$i] ne "0";
- }
- foreach my $key (sort keys %mscr) {
- if ($mscr{$key} eq "t") {push (@list, $key);}
- elsif ($mscr{$key} eq "m" || $mscr{$key} eq "c") {push (@list, "$key$mscr{$key}");}
- }
- @list =sort_alfanum(@list);
- my @crit_t = split ( /, /, $data{crit_t});
- my @crit_0 = split ( /, /, $data{crit_0});
- my %green_colored = map { $_ => "<b><font color=$data{yes_color}>$_</font></b>" } @crit_t;
- my %red_colored = map { $_ => "<font color=$data{no_color}>$_</font>" } @crit_0;
- foreach my $list (@list) {
- $list = $green_colored{$list} if exists $green_colored{$list};
- $list = $red_colored{$list} if exists $red_colored{$list};
- }
- $joined =join (', ', @list);
- return $joined;
- }
- sub stampa_gif {
- my $query_gif = $data{query_gif};
- my $script=$data{script};
- if ($query_gif) {
- $script.="?runmode=gif&$query_gif";
- $script=~s/ /%20/g;
- print qq[<br><center>
- <img src="$script" alt="istogramma">
- </center>];
- }
- }
- sub gif {
- my @array_data;
- my @array_nomi;
- my @values= @{ $data{manoscr} };
- my %hash = map { ;"$_" => scalar(param($_)) } @values;
- my $max_y=0;
- my $val;
- $max_y=0;
- my @keys = sort_alfanum(keys %hash);
- foreach my $key (@keys) {
- my $val=$hash{$key};
- $max_y=$val if ($val > $max_y);
- push @array_nomi,$key;
- push @array_data,$val;
- }
- my @data;
- $data[0]=\@array_nomi;
- $data[1]=\@array_data;
- my @dim = (800,600);
- my $my_graph=GD::Graph::bars->new(@dim);
- $my_graph->set(
- x_label => 'Manoscritti',
- y_label => 'Occorrenze',
- title => 'Grafico ad istogrammi',
- y_max_value => $max_y,
- y_tick_number => 8,
- y_label_skip => 2,
- bar_spacing => 2,
- shadow_depth => 3,
- bargroup_spacing => 4,
- accent_treshold => 200,
- transparent => 0,
- );
- $my_graph->plot(\@data);
- my $ext = $my_graph->export_format;
- print "Content-Type: image/gif;\n\n";
- binmode STDOUT;
- print STDOUT $my_graph->gd->$ext();
- close STDOUT;
- }
- ####################################################################
- # #
- # Le Sub per la costruzione di codice html #
- # #
- ####################################################################
- sub tabella{
- #my $array_ref = shift;
- #my @tab_array = @$array_ref;
- if ($data{numero_occ}>0) {
- my $query=$data{query};
- my $query_commento=$query;
- $query_commento=~s/>/>/g;
- my $stampaquery = "<!- $query_commento ->\n\n";
- my $testatab = qq(
- <table border=1 width=100%>
- <tr>
- <td><b>Luogo</b></td>
- <td><b>Linea</b></td>
- <td><b>Lezione esatta</b></td>
- <td align=center colspan=$data{max}><b>Altre Varianti</b></td>
- </tr>
- );
- print "\n\n$stampaquery\n\n<br><br><br><center><h1>Criterio di ricerca: \"$data{criterio}\"</h1></center> Numero occorrenze: ";
- print "$data{numero_occ}\n\n$testatab\n";
- foreach my $riga (@{$data{tab_array}}) {
- print $riga;
- }
- print "</table>";
- }
- }
- sub start_html {
- print "Content-type: text/html\n\n";
- print qq(<html>
- <head>
- <title>tavola</title>
- <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
- <style type="text/css">
- body{background-color: #FFE4B5;}
- { font-family: Verdana, Arial, Helvetica, Trebuchet MS, sans-serif; font-size: 16; color: #000000; text-decoration: none; }
- table { font-family: Verdana, Arial, Helvetica, Trebuchet MS, sans-serif; font-size: 11; color: #000000; text-decoration: none; }
- a { color: green; text-decoration: none; }
- A:active { color: #A04415; text-decoration: none; }
- a:hover { color: red; text-decoration: none; }
- .grande { font-size: 20pt; color:#222333; text-decoration: none; text-align:center; vertical-align:top; line-height: 10px; }
- .LightText { font-size: 16pt; color: #000000; text-decoration: none; align: justified; line-height: 30px; }
- </style>
- <SCRIPT language="JavaScript">
- <!-- //
- function indifferente () {
- for (var counter=1;counter<=36;counter++) {
- document.compara.elements[counter].selectedIndex=0;
- }
- }
- function tuttisi () {
- for (var counter=1;counter<=36;counter++) {
- document.compara.elements[counter].selectedIndex=1;
- }
- }
- function tuttino () {
- for (var counter=1;counter<=36;counter++) {
- document.compara.elements[counter].selectedIndex=2;
- }
- }
- // -->
- </SCRIPT>
- </head>
- <body>
- <p><a href=$data{script}>Nuova ricerca</a></p>);
- }
- sub end_html {
- print q(
- </body>
- </html>);
- }
- sub no_results {
- print "<center><h1>Nessuna occorrenza trovata</h1><hr><br><br>Criterio di ricerca: <b>"<i>$data{criterio}</i>"</b></center>\n";
- end_html();
- }
- sub body_html {
- my @values = @{ $data{manoscr} };
- my $l=0;
- print qq(
- <table border=1 width=100%>
- <form name="compara" action=$data{script} method=get>
- <input type="hidden" name="runmode" value="compara">
- <tr>
- );
- foreach my $value (@values) {
- print qq(
- <td><h1>$value</h1>
- <select name="s$value">
- <option value="">indifferente</option>
- <option value="t">SI'</option>
- <option value="0">NO</option>
- </select></td>
- );
- $l++;
- if ($l % 8 == 0) {print "</tr>\n<tr>"};
- }
- for (my $m=$l; $m<40; $m++) {
- if ($m % 8 == 0) {print "</tr>\n<tr>"};
- print "<td><br><br><br></td>\n";
- }
- print qq(
- </tr><tr>
- <td><br><br></td>
- <td><br><br></td>
- <td><br><br></td>
- <td><br><br></td>
- <td><br><br></td>
- <td>
- <input type="checkbox" name="esatta" value="esa" checked>in lezione esatta<br>
- <input type="checkbox" name="errata" value="err" checked>in lezione errata<br></td>
- <td><input type="submit" value="compara"></td>
- <td><input type="reset" value="Annulla"></td>
- </tr>
- </form>
- <tr>
- <td>
- <input type="button" value="tutti si" onClick="tuttisi();">
- </td>
- <td>
- <input type="button" value="tutti no" onClick="tuttino();">
- </td>
- <td>
- <input type="button" value="tutti indifferente" onClick="indifferente();">
- </td>
- <td><br><br></td>
- <td><br><br></td>
- <td><br><br></td>
- <td><br><br></td>
- <td><br><br></td>
- </tr>
- </table>
- <br><br>
- <table border=0 width=100%>
- <tr>
- <td bgcolor=a98dc3>
- <form name=cerca action=$data{script} method=get>
- <input type="hidden" name="runmode" value="cerca">
- <input type="text" name="cerca">
- <input type=submit value=cerca>
- <input type="reset" value="Annulla"></form></td>
- <td><br><br></td>
- <td align=right bgcolor=c98d67>
- manoscritto<br>
- <form name=combo action=$data{script} method=get>
- <input type="hidden" name="runmode" value="combo">
- <select name="menu">
- <option value="tutte">tutti</option>);
- foreach my $value (@values) {
- print "<option value=\"$value\">$value</option>\n";
- }
- print qq(</select></td>
- <td bgcolor=c98d67>
- <input type="radio" name="2o3" value="coppie" checked="checked">Coppie<br>
- <input type="radio" name="2o3" value="terne">Terne
- </td>
- <td bgcolor=c98d67>
- <input type=submit value=Combina></form></td>
- <td><br><br></td>
- </tr>
- <tr>
- </table>
- );
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement