#!/usr/bin/perl ################################################################################### # # # PerlDesk - Customer Help Desk Software # # # ################################################################################### # # # Author: John Bennett # # Email: j.bennett@perldesk.com # # Web: http://www.perldesk.com # # Filename: staff_subs.cgi # # Details: The staff areas # # Release: 1.5.5 # # # ################################################################################### # Please direct bug reports,suggestions or feedback to the perldesk forums. # # www.perldesk.com/board # # # # PerlDesk is free for both commercial and non-commercial use providing that the # # copyright headers remain intact and the links remain on the html pages. # # Re-distribution of this script without prior consent is strictly prohibited. # # # ################################################################################### # Please see the README/INSTALL files if you have any problems with this software # ################################################################################### ############################################## # Dati di accesso al database del groupware. # ############################################## $dbhost2 = "localhost"; $dbname2 = "agenda"; $dbuser2 = 'agenda'; $dbpass2 = 'gnd658'; ############################################## my $spass = $q->cookie('spass'); my $sname = $q->cookie('staff'); %Cookies = ( staff => $sname, spass => $spass ); sub check_user { if ((! $Cookies{'staff'}) || ($Cookies{'staff'} eq "")) { $ignore = 1; section("login"); } my $statement = qq|SELECT * FROM staff WHERE username = "$Cookies{'staff'}"|; my $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $uid = $ref->{'sid'}; $username = $ref->{'username'}; $password = $ref->{'password'}; $name = $ref->{'name'}; $accesslevel = $ref->{'access'}; $email = $ref->{'email'}; $rkey = $ref->{'rkey'}; $template{'ucname'} = $username; $template{'name'} = $name; $template{'email'} = $email; } my $md5 = new Digest::MD5 ; $md5->reset ; my $yday = (localtime)[7]; my @ipa = split(/\./,$ENV{'REMOTE_ADDR'}); my $startip = $ipa[0] . $ipa[1]; my $certif = $Cookies{'staff'} . "pd-$rkey" . $ENV{'HTTP_USER_AGENT'} . $startip ; $md5->add($certif); my $enc_cert = $md5->hexdigest(); if($enc_cert eq $Cookies{'spass'}) { $loggedin = 1; # we're logged in !! :) } else { $ignore =1; section("login"); } my $current_time = time(); $statement = 'UPDATE staffactive SET date = "' . "$current_time" . '" WHERE username = "' . "$Cookies{'staff'}" . '";'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $statement = 'SELECT level FROM departments'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if (($accesslevel =~ /$ref->{'level'}::/) || ($accesslevel =~ /GLOB::/)) { my $th = $dbh->prepare( "SELECT COUNT(*) FROM calls WHERE category = ?" ) or die DBI->errstr; $th->execute("$ref->{'level'}") or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $total = $th->fetchrow_array(); $template{'depview'} .= qq|
$ref->{'level'} ($total)
|; } } } sub lang_parse { if ($_ =~ /\%*\%/) { s/\%(\S+)\%/$LANG{$1}/g; } } sub section { $section = "@_"; if ($section eq "login") { if (!$ignore) { check_user(); print "Location: $global{'baseurl'}/staff.cgi?do=main\n\n" if $loggedin == "1"; } print "Content-type: text/html\n\n"; print qq|PerlDesk Staff Login

 


Remote IP: $ENV{'REMOTE_ADDR'}
  
Username
Password
 
Salvare password?

  
|; } if ($section eq "pro_login") { $user = $q->param('username'); $pass = $q->param('password'); # by Ago &identity_control($user); # fine by Ago $statement = "SELECT * FROM staff WHERE username = ?"; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute("$user") or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $salt = $ref->{'rkey'}; $cpass = crypt($pass, $salt); if ((!$user) || ($user eq "admin") || ($cpass ne $ref->{'password'})) { $error = 1; } else { $username = $ref->{'username'}; $password = $ref->{'password'}; $name = $ref->{'name'}; $email = $ref->{'email'}; $accesslevel = $ref->{'access'}; } } $error = 1 if !$username; die_nice("Username o password Errate
Torna alla Pagina di Login") if $error; if (@errors) { print "Content-type: text/html\n\n"; @content = @errors; print @content; $dbh->disconnect; exit; } $statement = 'UPDATE stafflogin SET date = "' . "$hdtime" . '" WHERE username = "' . "$user" . '";'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $md5 = new Digest::MD5 ; $md5->reset ; my $yday = (localtime)[7]; my @ipa = split(/\./,$ENV{'REMOTE_ADDR'}); my $startip = $ipa[0] . $ipa[1]; my $certif = $user . "pd-$salt" . $ENV{'HTTP_USER_AGENT'} . $startip ; $md5->add($certif); my $enc_cert = $md5->hexdigest() ; if ($q->param('remember') eq "yes") { $cookie1 = $q->cookie(-name=>'staff', -value=>$user, -domain =>'', -expires=>'+1y', -path => '/'); $cookie2 = $q->cookie(-name=>'spass', -value=>$enc_cert, -expires=>'+1y', -domain =>'', -path => '/'); } else { $cookie1 = $q->cookie(-name=>'staff', -value=>$user, -path => '/', -domain =>''); $cookie2 = $q->cookie(-name=>'spass', -value=>$enc_cert, -path => '/', -domain =>''); } print $q->header(-cookie=>[$cookie1,$cookie2]); print qq|

 

 

Login, stai entrando nell'area Staff.

clicca qui se non riesci ad entrare automaticamente

|; exit; } if ($section eq "search") { check_user(); print "Content-type: text/html\n\n"; # by Ago $template{'staff_calls'} = 'Ticket membri staff:

'; $statement = 'SELECT username FROM staff ORDER BY username ASC'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $template{'staff_calls'} .= '{'username'}.'\'>'.$ref->{'username'}.'
'."\n"; } $template{'staff_calls'} .= ''; # fine by Ago print parse("$global{'data'}/include/tpl/staff/search"); } if ($section eq "kb") { check_user(); print "Content-type: text/html\n\n"; $goto = $q->param('goto'); if ($goto eq "add") { $statement = 'SELECT category FROM kb_cat'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $option .= ""; } $sth->finish; $template{'category'} = $option; print parse("$global{'data'}/include/tpl/staff/add_kb"); } if ($goto eq "search") { $select = $q->param('select'); $query = $q->param('query'); my $field = $query; $feld = $q->param('field'); $query =~ s/_/ /g; if (!$feld) { $feld = $select; } $sth = $dbh->prepare( 'SELECT COUNT(*) FROM kb_entries WHERE ' . "description" . ' LIKE "%' . "$query" . '%" AND category = "' . "$select" . '"' ) or die DBI->errstr; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $count = $sth->fetchrow_array(); $sth->finish; if ($ENV{'QUERY_STRING'} !~ /show/) { $pae = 0; } if (($count > 30) || ($ENV{'QUERY_STRING'} =~ /show/)) { $showbar = "1"; } if ($showbar) { if ($ENV{'QUERY_STRING'} =~ /show/) { my $string = "$ENV{'QUERY_STRING'}"; @values = split(/\&/,$ENV{'QUERY_STRING'}); foreach $i (@values) { if ($i =~/show/) { ($action, $pae) = split(/=/,$i); } } if ($pae eq "0") { $prevlink = '« prev'; } else { $link = $query; $link =~ s/ /_/g; $prevpage = $pae-1; $prevlink = "" . '« prev' . ""; } } else { $prevlink = '« prev'; } $res = $pae+1; $total = (30*$res); if ($count > $total){ $nextpage = $pae+1; $link = $query; $link =~ s/\s/_/g; $nextlink = "" . 'next »' . ""; } else { $nextlink = 'next »'; } $bar = '
' . "$prevlink" . '
' . "$nextlink" . '
'; } $showp = $pae*30; $bar = '' if !$bar; $statement = 'SELECT * FROM kb_entries WHERE description LIKE "%' . "$query" . '%" AND category = "' . "$feld" . '" ORDER BY id LIMIT ' . "$showp" . ',30'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if ($ref->{'read'} > 50) { $pop = ""; } else { $pop = ' '; } $opencalls .= '
 ' . "{'id'}>$ref->{'subject'}" . ' ' . "$ref->{'category'}" . ' ' . "$pop" . '
'; } $sth->finish; if (!$opencalls) { $opencalls = 'No Results'; } $template{'results'} = $opencalls; $template{'bar'} = $bar; my $output = parse("$global{'data'}/include/tpl/staff/kb_results"); print $output; } if ($goto eq "cat") { $response .= '
[ KB PRINCIPALE | AGGIUNGI CATEGORIA ]


'; $statement = 'SELECT * FROM kb_cat'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $link = "$ref->{'category'}"; chomp($link); $link =~ s/ /_/g; $response .= '
Modifica KB ' . "$ref->{'category'}" . '[ MODIFICA | ELIMINA ]
'; } $sth->finish; $template{'response'} = $response; print parse("$global{'data'}/include/tpl/staff/general"); } if ($goto eq "delcat") { $category = $q->param('cat'); $link = $category; $category =~ s/_/ /g; $response = 'Are you sure you want to remove this category? Doing so will affect all articles in it.

Yes, Delete'; $template{'response'} = $response; print parse("$global{'data'}/include/tpl/staff/general"); } if ($goto eq "ccat") { $category = $q->param('cat'); $category =~ s/_/ /g; $statementd = 'DELETE FROM kb_cat WHERE category = "' . "$category" . '";'; $sth = $dbh->prepare($statementd) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $sth->finish; $response = 'Categoria Eliminata Ritorna all\'Amministrazione Categorie'; $template{'response'} = $response; print parse("$global{'data'}/include/tpl/staff/general"); } if ($goto eq "editcat") { $category = $q->param('cat'); chomp($category); $category =~ s/_/ /g; $response = '
   
Modifica Categoria
 
'; $template{'response'} = $response; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; } if ($goto eq "upcat") { $department = $q->param('department'); $old = $q->param('old'); $statementd = 'UPDATE kb_cat SET category = "' . "$department" . '" WHERE category = "' . "$old" . '";'; $sth = $dbh->prepare($statementd) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $sth->finish; @response = 'Saved Back to Categories'; open (IN, "$global{'data'}/include/tpl/staff/general.tpl") || die "Unable to open: $!"; while () { if ($_ =~ /\{*\}/i) { s/\{response\}/@response/i; } push (@content, $_); } close (IN); parse_template(); } if ($goto eq "addcat") { $response = '
KB Categoria
'; $template{'response'} = $response; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; } if ($goto eq "savecat") { my $category = $q->param('adddepartment'); my $rv = $dbh->do(qq{INSERT INTO kb_cat values ( "$category")} ); $response = 'Categoria Aggiunta Ritorna al KB'; $template{'response'} = $response; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; } if ($goto eq "view") { my $id = $q->param('kid'); $statement = 'SELECT * FROM kb_entries WHERE id = ' . "$id"; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $read = $ref->{'views'}; $author = $ref->{'author'}; $subject = $ref->{'subject'}; $description = $ref->{'description'}; $id = $ref->{'id'}; } $template{'subject'} = $subject; $template{'author'} = $author; $template{'article'} = $description; $template{'read'} = $read; $template{'id'} = $id; $template{'response'} = $response; print parse("$global{'data'}/include/tpl/staff/kb_article"); } if ($goto eq "addsave") { $subject = $q->param('subject'); $description = $q->param('description'); $category = $q->param('select'); $description =~ s/\"/\\"/g; $description =~ s/\n/
/g; my $rv = $dbh->do(qq{INSERT INTO kb_entries values ( "NULL", "$category", "$name", "$subject", "$description", "0")} ); $response = 'Inserimento eseguito. KB PINCIPALE'; $template{'response'} = $response; print parse("$global{'data'}/include/tpl/staff/general"); } if ($goto eq "delete") { my $id = $q->param('kid'); $response = 'Sei sicuro di voler rimuovere questo articolo?

Yes, Delete'; $template{'response'} = $response; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; } if ($goto eq "cdel") { my $id = $q->param('kid'); $statementd = 'DELETE FROM kb_entries WHERE id = "' . "$id" . '";'; $sth = $dbh->prepare($statementd) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $sth->finish; $response = 'Articolo Rimosso Ritorna al Knowledge Base'; $template{'response'} = $response; print parse("$global{'data'}/include/tpl/staff/general"); } if ($goto eq "editart") { my $id = $q->param('kid'); $statement = 'SELECT * FROM kb_entries WHERE id = ' . "$id"; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $read = $ref->{'read'}; $author = $ref->{'author'}; $subject = $ref->{'subject'}; $description = $ref->{'description'}; $id = $ref->{'id'}; } $template{'subject'} = $subject; $template{'author'} = $author; $template{'article'} = $description; $template{'response'} = $response; $template{'id'} = $id; print parse("$global{'data'}/include/tpl/staff/kb_edit"); } if ($goto eq "editsave") { my $id = $q->param('kid'); $department = $q->param('article'); $department =~ s/\"/\\"/g; $statementd = 'UPDATE kb_entries SET description = "' . "$department" . '" WHERE id = "' . "$id" . '";'; $sth = $dbh->prepare($statementd) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $sth->finish; $response = 'Saved Back to Knowledge Base'; $template{'response'} = $response; print parse("$global{'data'}/include/tpl/staff/general"); } if ((! $goto) || ($goto eq "main")) { $num = 1; $statement = 'SELECT * FROM kb_entries ORDER BY id DESC LIMIT 5'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if ($ref->{'views'} > 50) { $pop = ""; } else { $pop = ' '; } if ($num eq "2") { $bgcol = '#FFFFFF'; $num = 0; } else { $bgcol = '#F0F0F0'; } $kb .= '
' . "{'id'}>$ref->{'subject'}" . ' ' . "$ref->{'category'}" . ' '; $num++; } $statement = 'SELECT category FROM kb_cat'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $list .= ""; } $sth->finish; $template{'category'} = $list; $template{'list'} = $kb; print parse("$global{'data'}/include/tpl/staff/kb"); } } if ($section eq "sresults") { check_user(); print "Content-type: text/html\n\n"; $select = $q->param('select'); $query = $q->param('query'); $cliente = $q->param('cliente'); $progetto_id = $q->param('progetto_id'); $closed = $q->param('closed'); if ($select eq "owner") { $field = "ownership"; } if ($select eq "cat") { $field = "category"; } if ($select eq "id") { $field = "id"; } # if ($select eq "Username") { $field = "username"; } if ($select eq "subject") { $field = "subject"; } if ($select eq "domain") { $field = "url"; } if ($select eq "priority") { $field = "priority"; } if ($select eq "description") { $field = "description"; } if ($ENV{'QUERY_STRING'} =~ /query/) { $query = $q->param('query'); $feld = $q->param('field'); } $query =~ s/_/\_/g; if (!$feld) { $feld = $field; } # Modifica by Ago if ($q->param('operatore') eq "INT") { if ($feld eq "description") { # Nel caso della ricerca del testo, cerca anche nelle risposte: $query_totale .= $operatore . "WHERE (description LIKE '%$query%' OR EXISTS (SELECT id FROM notes AS n WHERE n.call = c.id AND MATCH (n.comment) AGAINST ('\"$query\"')) "; } else { $query_totale .= $operatore . "WHERE $feld LIKE '%$query%'"; } } else { if ($q->param('operatore') ne "AND") { $operatore = "OR"; $query_totale = "WHERE (1=0 "; } else { $operatore = "AND"; $query_totale = "WHERE (1=1 "; } @qw = split(/\s/, $query); $cont = 0; foreach $qwp (@qw) { $cont++; if ($feld eq "description") { # Nel caso della ricerca del testo, cerca anche nelle risposte: $query_totale .= $operatore . " (description LIKE '%$qwp%' OR EXISTS (SELECT id FROM notes AS n" . $cont . " WHERE n" . $cont . ".call = c.id AND MATCH (n" . $cont . ".comment) AGAINST ('$qwp'))) "; } else { $query_totale .= $operatore . " c.$feld LIKE '%$qwp%' "; } } } $query_totale .= ") "; if ($cliente ne "") { $query_totale .= " AND c.username LIKE '%" . $cliente . "%' "; } if ($progetto_id ne "") { $query_totale .= " AND pa.project_ID = '" . $progetto_id . "' "; } # by Ago # Modifica by Ago - per la ricerca sui ticket aperti e/o chiusi: if ($closed eq "1") { $__opened__ = ""; } else { $__opened__ = 'AND status != "CLOSED"'; } if ($accesslevel =~ /GLOB::/) { # rob mod if ($feld =~ /ownership/) { # $statement = qq|SELECT COUNT(*) FROM calls WHERE $feld LIKE "%$query%" AND status != "CLOSED"|; $statement = qq|SELECT COUNT(*) FROM calls AS c LEFT JOIN project_ass AS pa ON (pa.ID = c.id) $query_totale AND status != "CLOSED"|; } elsif ($feld =~ /category/) { # $statement = qq|SELECT COUNT(*) FROM calls WHERE $feld LIKE "%$query%" AND status != "CLOSED"|; $statement = qq|SELECT COUNT(*) FROM calls AS c LEFT JOIN project_ass AS pa ON (pa.ID = c.id) $query_totale AND status != "CLOSED"|; } else { # $statement = qq|SELECT COUNT(*) FROM calls WHERE $feld LIKE "%$query%"|; $statement = qq|SELECT COUNT(*) FROM calls AS c LEFT JOIN project_ass AS pa ON (pa.ID = c.id) $query_totale $__opened__|; } # rob fine mod # $statement = qq|SELECT COUNT(*) FROM calls WHERE $feld LIKE "%$query%"|; } else { @access = split(/::/, $accesslevel); $ac = 0; foreach $dep (@access) { $ac++; } # $statement = qq|SELECT COUNT(*) FROM calls WHERE $feld LIKE "%$query%" AND |; $statement = qq|SELECT COUNT(*) FROM calls AS c LEFT JOIN project_ass AS pa ON (pa.ID = c.id) $query_totale $__opened__ AND |; $i = 1; foreach $dep (@access) { $dep =~ s/^\s+//g; $statement .= qq|category = "$dep" |; if ($i < $ac) { $statement .= 'OR '; } $i++; } } $sth = $dbh->prepare($statement) or die DBI->errstr; $sth->execute() or die "Couldn't execute $statement: $DBI::errstr; stopped"; $count = $sth->fetchrow_array(); $sth->finish; $total = $count; $limit = $q->param('pae') || "20"; # Results per page my $pages = ($total/$limit); $pages = ($pages+0.5); my $nume = sprintf("%.0f",$pages); my $page = $q->param('page') || "0"; $nume = "1" if !$nume; $to = ($limit * $page) if $page; $to = "0" if !$page; $template{'path'} = "staff.cgi" . '?' . $ENV{'QUERY_STRING'} . "&page=$page"; $template{'path'} =~ s/&sort=(.*)//; $template{'path'} =~ s/&method=(.*)//; # by Ago $query_string_page = ""; foreach my $name ($q->param) { if ($name ne "page") { $query_string_page .= $name . "=" . $q->param($name) . "&"; } } # fine by Ago foreach (1..$nume) { my $nu = $_ -1; if ($nu eq $page) { $link = "[$_]"; } else { $link = "$_"; } my $string = $ENV{'QUERY_STRING'}; $string =~ s/&page=(.*)//g; # $nav .= qq|$link |; $nav .= qq|$link |; # fine by Ago } $template{'nav'} = $nav; my $show = $limit * $page; # by Ago per l'ordinamento $order = $q->param('order'); $dir = $q->param('dir'); # Ago 19/11/2006 - ORDINA pure per lo stato, # separando sempre gli HOLD dagli OPEN... $query_order = "ORDER BY status DESC, "; # fine mods if ($order ne "") { $query_order .= $order; if ($dir eq "ASC") { $query_order .= " ASC"; } else { $query_order .= " DESC"; } } else { $query_order .= "c.id ASC"; } # rob mod if ($feld =~ /ownership/) { # $statement = qq|SELECT * FROM calls WHERE $feld LIKE "%$query%" AND status != "CLOSED" LIMIT $show,$limit|; $statement = qq|SELECT * FROM calls AS c LEFT JOIN project_ass AS pa ON (pa.ID = c.id) $query_totale AND status != "CLOSED" $query_order LIMIT $show,$limit|; } elsif ($feld =~ /category/) { # $statement = qq|SELECT * FROM calls WHERE $feld LIKE "%$query%" AND status != "CLOSED" LIMIT $show,$limit|; $statement = qq|SELECT * FROM calls AS c LEFT JOIN project_ass AS pa ON (pa.ID = c.id) $query_totale AND status != "CLOSED" $query_order LIMIT $show,$limit|; } else { # $statement = qq|SELECT * FROM calls WHERE $feld LIKE "%$query%" LIMIT $show,$limit|; $statement = qq|SELECT * FROM calls AS c LEFT JOIN project_ass AS pa ON (pa.ID = c.id) $query_totale $__opened__ $query_order LIMIT $show,$limit|; } # rob fine mod # $statement = qq|SELECT * FROM calls WHERE $feld LIKE "%$query%" ORDER BY id LIMIT $show,$limit|; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; $number=0; # by Ago $query_string = ""; foreach my $name ($q->param) { if (($name ne "order") && ($name ne "dir")) { $query_string .= $name . "=" . $q->param($name) . "&"; } } $template{'results'} .= 'Ordina per [ Oggetto ] '; $template{'results'} .= '/ [ Priorità ] '; $template{'results'} .= '/ [ Ultimo Accesso ]
'; $stato_a = ""; # fine by Ago while(my $ref = $sth->fetchrow_hashref()) { my $cat = $ref->{'category'}; $cat .= '::'; if (($accesslevel =~ /$cat/) || ($accesslevel =~ /GLOB::/)) { $number++; if ($ref->{'priority'} eq 1) { $font = '#990000'; } else { $font = '#000000'; } $subject = "$ref->{'subject'}"; $subject = substr($subject,0,50).'..' if length($subject) > 52; # by Ago ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime($ref->{'track'}); $tempo = sprintf("%02d-%02d-%4d %02d:%02d",$mday,$mon+1,$year+1900,$hour,$min); # Introduce un separatore tra ticket di # stato diverso. if ($ref->{'status'} ne $stato_a) { if ($stato_a ne "") { $template{'results'} .= "
 "; } $stato_a = $ref->{'status'}; } # by Ago $progetto_associato = get_nome_progetto($ref->{'id'}); $url_progetto_associato = get_url_progetto($ref->{'id'}); $template{'results'} .= '
{'id'}\">$subject" . ' ' . $progetto_associato . ' {'ownership'})" . '
'; # '; # by Ago } } $sth->finish; $template{'results'} = qq|Non è stato trovato nessun risultato| if !$template{'results'}; $template{'noresults'} = $total; print parse("$global{'data'}/include/tpl/staff/search_results"); } if ($section eq "messanger") { check_user(); print "Content-type: text/html\n\n"; $too = $q->param('to'); if ($too eq "reply") { $msg = $q->param('id'); $statemente = 'SELECT * FROM messages WHERE id = ?'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($msg) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $from = $ref->{'sender'}; $subject = $ref->{'subject'}; } open (IN, "$global{'data'}/include/tpl/staff/msgreply.tpl") || die "Unable to open: $!"; while () { if ($_ =~ /\{*\}/i) { s/\{username\}/$Cookies{'staff'}/i; s/\{touser\}/$from/ig; } print $_; } close (IN); } if ($too eq "delete") { $msg = $q->param('id'); ######################### DELETE MESSAGE ############### $statemente = 'SELECT * FROM messages WHERE id = "' . "$msg" . '"'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if ($ref->{'touser'} ne $Cookies{'staff'}) { print 'This is not your message to delete'; exit; } } $statement = 'DELETE FROM messages WHERE id = "' . "$msg" . '"'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; ######################### CHECK THIS USERS INBOX ####### $statemente = "SELECT * FROM messages WHERE touser = \"$Cookies{'staff'}\" ORDER BY id DESC"; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $message = '' . "$ref->{'id'}" . ' ' . "$ref->{'sender'}" . ' ' . "$ref->{'date'}" . '' . "{'id'}\">$ref->{'subject'}" . ''; push @messages, $message; } ######################### PRINT OUT THE MAIN PAGE ##### open (IN, "$global{'data'}/include/tpl/staff/inbox.tpl") || die "Unable to open: $!"; while () { if ($_ =~ /\{*\}/i) { s/\{messages\}/@messages/i; } print $_; } $dbh->disconnect; exit; } if ($too eq "inbox") { $statemente = "SELECT * FROM messages WHERE touser = \"$Cookies{'staff'}\" ORDER BY id DESC"; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $message = '' . "$ref->{'id'}" . ' ' . "$ref->{'sender'}" . ' ' . "$ref->{'date'}" . '' . "{'id'}\">$ref->{'subject'}" . ''; push @messages, $message; } $current_time = time(); $statement = 'UPDATE staffread SET date = "' . "$current_time" . '" WHERE username = "' . "$Cookies{'staff'}" . '";'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; open (IN, "$global{'data'}/include/tpl/staff/inbox.tpl") || die "Unable to open: $!"; while () { if ($_ =~ /\{*\}/i) { s/\{messages\}/@messages/i; } print $_; } close (IN); } if ($too eq "view") { $msg = $q->param('id'); $statemente = 'SELECT * FROM messages WHERE id = "' . "$msg" . '"'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $from = $ref->{'sender'}; $to = $ref->{'touser'}; $date = $ref->{'date'}; $subject = $ref->{'subject'}; $message = $ref->{'message'}; } if ($to ne $Cookies{'staff'}) { print 'This is not your message'; exit; } open (IN, "$global{'data'}/include/tpl/staff/msgview.tpl") || die "Unable to open: $!"; while () { if ($_ =~ /\{*\}/i) { s/\{subject\}/$subject/i; s/\{from\}/$from/i; s/\{sent\}/$date/i; s/\{id\}/$msg/ig; s/\{message\}/$message/i; } print $_; } close (IN); $dbh->disconnect; exit; } if ($too eq "compose") { $statemente = 'SELECT username FROM staff WHERE username != "' . "$Cookies{'staff'}" . '" AND username != "admin"'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $ddopt = ""; push @dd, $ddopt; } if (not @dd) { @dd = ''; } open (IN, "$global{'data'}/include/tpl/staff/msgcompose.tpl") || die "Unable to open: $!"; while () { if ($_ =~ /\{*\}/i) { s/\{ddlist\}/@dd/i; s/\{username\}/$Cookies{'staff'}/i; } print $_; } close (IN); } if ($too eq "savenote") { $from = $Cookies{'staff'}; $to = $q->param('user'); $subject = $q->param('subject'); $message = $q->param('message'); $statemente = 'SELECT * FROM staff WHERE username = "' . "$to" . '"'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $valid = $ref->{'name'}; } if (!$valid) { print "

Spiacente, utente inesistente Back
"; $dbh->disconnect; exit; } $current_time = time(); $message =~ s/\"/\\"/g; my $rv = $dbh->do(qq{INSERT INTO messages values ( "NULL", "$to", "$from", "$hdtime", "$subject", "$message", "$current_time")} ); print "

Message has been sent to \'$to\' - Back to Inbox
"; $dbh->disconnect; exit; } } if ($section eq "delete") { check_user(); print "Content-type: text/html\n\n"; $callid = $q->param('cid'); $statemente = 'SELECT * FROM settings WHERE setting = "sdelete"'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if (!$ref->{'value'}) { print "
Spiacente. L'Amministratore ha vietato la cancellazione delle richieste da parte dello Staff
"; exit; } } $response = "Delete Request $callid?

Yes, delete."; $template{'response'} = $response; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; } if ($section eq "profile") { $goto = $q->param('goto'); if (! $goto) { check_user(); print "Content-type: text/html\n\n"; $statement = 'SELECT * FROM staff WHERE username = "' . "$Cookies{'staff'}" . '";'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $sig = $ref->{'signature'} } $statement = 'SELECT * FROM preans WHERE author = "' . "$Cookies{'staff'}" . '";'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $ddmenu .= ' '; } $template{'preans'} = $ddmenu; $template{'sig'} = $sig; my $output = parse("$global{'data'}/include/tpl/staff/profile"); print $output; } if ($goto eq "updateprofile") { check_user(); print "Content-type: text/html\n\n"; $name = $q->param('name'); $email = $q->param('email'); $pass1 = $q->param('pass1'); $pass2 = $q->param('pass2'); $notify = $q->param('notify'); if ($notify eq "yes") { $notif = 1; } else { $notif = 0; } if (!$name) { $error = "Inserisci il Nome"; push (@errors, $error); } elsif (!$email) { $error = "Inserisci l\'email"; push(@errors,$error); } elsif ($pass1 ne $pass2) { $error = "Le password non corrispondono"; push(@errors,$error); } if (@errors) { print "Content-type: text/html\n\n"; print @errors; $dbh->disconnect; exit; } my @chars=("a..z","A..Z","0..9",'.','/'); my $salt= $chars[rand(@chars)] . $chars[rand(@chars)]; $cpass = crypt($pass1, $salt); $sig = $q->param('sig'); $statemente = 'SELECT * FROM staff WHERE username = "' . "$Cookies{'staff'}" . '";'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if ($ref->{'password'} ne $cpass) { if ($pass1 && $pass2) { if ($pass1 ne $pass2) { $error = "Errore, le password non corrispondono"; push (@errors, $error); } else { $statement = 'UPDATE staff SET name = "' . "$name" . '", email = "' . "$email" . '", password = "' . "$cpass" . '", rkey = "' . "$salt" . '", notify = "' . "$notif" . '", signature = "' . "$sig" . '" WHERE username = "' . "$Cookies{'staff'}" . '";'; } } else { $statement = 'UPDATE staff SET name = "' . "$name" . '", email = "' . "$email" . '", notify = "' . "$notif" . '", signature = "' . "$sig" . '" WHERE username = "' . "$Cookies{'staff'}" . '";'; } } } $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $response = "

Il tuo profilo è stato aggiornato.

Back to profile"; $template{'response'} = $response; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; } if ($goto eq "add_pre") { check_user(); print "Content-type: text/html\n\n"; my $output = parse("$global{'data'}/include/tpl/staff/predef"); print $output; } if ($goto eq "addpredef") { check_user(); print "Content-type: text/html\n\n"; if ((defined $q->param('subject')) && (defined $q->param('content'))) { $subject = $q->param('subject'); $comments = $q->param('content'); $subject =~ s/\"/\\"/g; $comments =~ s/\"/\\"/g; my $rv = $dbh->do(qq{INSERT INTO preans values ( "NULL", "$Cookies{'staff'}", "$subject", "$comments", "$hdtime")} ); } else { print 'Please enter all the fields'; exit; } $response = "

Il tuo modello di risposta è stato aggiunto"; $template{'response'} = $response; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; } if ($goto eq "editpre") { check_user(); print "Content-type: text/html\n\n"; $id = $q->param('value'); $statemente = 'SELECT * FROM preans WHERE id = "' . "$id" . '";'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $subject = $ref->{'subject'}; $content = $ref->{'text'}; } $template{'id'} = $id; $template{'subject'} = $subject; $template{'content'} = $content; my $output = parse("$global{'data'}/include/tpl/staff/editpre"); print $output; } if ($goto eq "editsave") { check_user(); print "Content-type: text/html\n\n"; $id = $q->param('id'); $subject = $q->param('subject'); $content = $q->param('content'); $subject =~ s/\"/\\"/g; $content =~ s/\"/\\"/g; $statement = 'UPDATE preans SET subject = "' . "$subject" . '", text = "' . "$content" . '" WHERE id = "' . "$id" . '";'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $response = "

Il tuo modello di risposta è stato salvato."; $template{'response'} = $response; print parse("$global{'data'}/include/tpl/staff/general"); } } if ($section eq "confirmdel") { check_user(); print "Content-type: text/html\n\n"; $callid = $q->param('cid'); if ($accesslevel !~ /GLOB::/) { $statemente = 'SELECT category FROM calls WHERE id = ?'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($callid) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if ($accesslevel =~ /$ref->{'category'}::/) { # OK } else { print "Non hai il permesso per cancellare questa richiesta."; exit; } } } $statemente = 'SELECT * FROM settings WHERE setting = "sdelete"'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if (!$ref->{'value'}) { print "Non hai il permesso per cancellare questa richiesta."; exit; } } $statement = 'DELETE FROM calls WHERE id = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($callid) or die print "Couldn't execute statement: $DBI::errstr; stopped"; $statement = 'DELETE FROM notes WHERE `call` = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($callid) or die print "Couldn't execute statement: $DBI::errstr; stopped"; # by Ago # aggiornadb($callid); $statement = 'DELETE FROM project_ass WHERE ID = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($callid) or die print "Couldn't execute statement: $DBI::errstr; stopped"; ########## $response = "Richiesta Eliminata Ritorna alle Richieste Aperte"; $template{'response'} = $response; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; } if ($section eq "own") { check_user(); print "Content-type: text/html\n\n"; $callid = $q->param('cid'); if ($accesslevel !~ /GLOB::/) { $statemente = 'SELECT category FROM calls WHERE id = ?'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($callid) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if ($accesslevel !~ /$ref->{'category'}::/) { print "Non hai i permessi per visualizzare questa richiesta."; exit; } } } $response = "
ID della Richiesta $callid?
Lo Staff sarà informato che sei diventato il responsabile della richiesta

Acquisisci Richiesta
"; $template{'response'} = $response; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; } if ($section eq "claim") { check_user(); print "Content-type: text/html\n\n"; $cid = $q->param('cid'); $statement = 'UPDATE calls SET ownership = "' . "$Cookies{'staff'}" . '" WHERE id = "' . "$cid" . '";'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $response = "Sei diventato il responsabile della richiesta. Ritorna alla Richiesta"; $template{'response'} = $response; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; } if ($section eq "ticket") { check_user(); print "Content-type: text/html\n\n"; my $trackedcall=$q->param('cid'); $statement = 'SELECT * FROM calls WHERE id = ? ORDER BY id'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackedcall) or die print "Couldn't execute statement: $DBI::errstr; stopped"; $number=0; while(my $ref = $sth->fetchrow_hashref()) { if (($accesslevel =~ /$ref->{'category'}::/) || ($accesslevel =~ /GLOB::/)) { $template{'trackno'} = $ref->{'id'}; $template{'date'} = $ref->{'time'}; $template{'uname'} = $ref->{'username'}; $template{'uname'} = $ref->{'username'}; $template{'username'} = $ref->{'username'}; $template{'priority'} = $ref->{'priority'}; $template{'status'} = $ref->{'status'}; $template{'subject'} = $ref->{'subject'}; $template{'category'} = $ref->{'category'}; $template{'description'} = $ref->{'description'}; $template{'owned'} = $ref->{'ownership'}; $template{'number'} = 0; $template{'email'} = $ref->{'email'}; $template{'url'} = $ref->{'url'}; $template{'name'} = $ref->{'name'}; } else { $error= 'Non hai i permessi per visualizzare questa richiesta'; push @errors, $error; } } if (@errors) { @content = @errors; print @content; $dbh->disconnect; exit; } # by Ago $statement = 'SELECT project_ID FROM project_ass WHERE ID = ? ORDER BY id'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackedcall) or die print "Couldn't execute statement: $DBI::errstr; stopped"; $ref = $sth->fetchrow_hashref(); $template{'projekte'} = " "; if (($ref->{'project_ID'} ne "") || ($ref->{'project_ID'} ne "0")) { $dbh2 = DBI->connect("DBI:mysql:$dbname2:$dbhost2","$dbuser2","$dbpass2") or script_error("Could not connect to the project database"); $sth2 = $dbh2->prepare("SELECT name FROM projekte WHERE ID = ?") or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth2->execute($ref->{'project_ID'}) or die print "Couldn't execute statement: $DBI::errstr; stopped"; $ref2 = $sth2->fetchrow_hashref(); $template{'projekte'} = ($ref2->{'name'}); } # fine my $statement = 'SELECT * FROM ticket_fields ORDER BY dorder'; my $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { my $tatement = qq|SELECT * FROM call_fields WHERE cid = "$trackedcall" AND fid = "$ref->{'id'}"|; my $th = $dbh->prepare($tatement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $th->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ef = $th->fetchrow_hashref()) { my $value = $ef->{'value'}; $value = ' ' if !$value; $template{'fields'} .= qq| $ref->{'name'} $value |; } } $sth->finish; $template{'fields'} = '' if !$template{'fields'}; my $statement = 'SELECT * FROM settings WHERE setting = "allowhtml"'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { my $htmlcode = "$ref->{'value'}"; } $statemente = 'SELECT * FROM notes WHERE `call` = ? ORDER BY id;'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackedcall) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $body = $ref->{'comment'}; if ($body =~ /^/g; } if ($ref->{'owner'} eq 0) { $notes .= ' ' . "$ref->{'author'}" . '
' . "$ref->{'time'}" . '

{'id'}" . '">MODIFICA ' . "$body" . ''; } if ($ref->{'owner'} eq 3) { $notes .= ' ' . "$ref->{'author'}" . '
' . "$ref->{'time'}" . '

 ' . "STAFF ACTION: $ref->{'action'}
$body" . ''; } if ($ref->{'owner'} eq 1) { if ($ref->{'visible'} eq 0) { $notice = 'NOTE PRIVATE STAFF
'; } else { $notice = ''; } $notes .= ' ' . "$ref->{'author'}" . '
' . "$ref->{'time'}" . '

{'id'}" . '">MODIFICA ' . "$notice $body" . ''; } } if (!$notes) { $notes = 'Non ci sono risposte.'; } if ($template{'description'} =~ /^/g; } $template{'notes'} = $notes; my $output = parse("$global{'data'}/include/tpl/staff/viewticket"); print $output; } if ($section eq "listbyuser") { check_user(); print "Content-type: text/html\n\n"; $userview = $q->param('user'); $statususer = $q->param('stato'); # by Ago $project_ID = $q->param('pID'); if (($project_ID ne "") && ($project_ID ne "0")) { $qr = "SELECT ID FROM project_ass WHERE project_ID = '".$project_ID."'"; $sth = $dbh->prepare($qr) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $where_call = "("; while(my $ref = $sth->fetchrow_hashref()) { $callid = $ref->{'ID'}; $where_call .= "id = '".$callid."' OR "; } $where_call .= "id = 0)"; if ($accesslevel =~ /GLOB::/) { $statement = qq|SELECT * FROM calls WHERE $where_call|; } else { @access = split(/::/, $accesslevel); $ac = 0; foreach $dep (@access) { $ac++; } $statement = qq|SELECT * FROM calls WHERE $where_call|; $i = 1; foreach $dep (@access) { $dep =~ s/^\s+//g; $statement .= qq|category = "$dep" |; if ($i < $ac) { $statement .= 'OR '; } $i++; } # $statement .= qq| ORDER BY id|; } } else { if ($accesslevel =~ /GLOB::/) { # $statement = qq|SELECT * FROM calls WHERE username = "$userview" ORDER BY id|; $statement = qq|SELECT * FROM calls WHERE username = "$userview"|; } else { @access = split(/::/, $accesslevel); $ac = 0; foreach $dep (@access) { $ac++; } $statement = qq|SELECT * FROM calls WHERE username = "$userview" AND |; $i = 1; foreach $dep (@access) { $dep =~ s/^\s+//g; $statement .= qq|category = "$dep" |; if ($i < $ac) { $statement .= 'OR '; } $i++; } # $statement .= qq| ORDER BY id|; } } # fine by Ago if ($statususer ne "") { # Modifica by Ago - 19/11/2006 if ($statususer ne "CLOSED") { $statement .= qq| AND status != "CLOSED"|; } else { $statement .= qq| AND status = "CLOSED"|; } # $statement .= qq| AND status = "$statususer"|; } # by Ago per l'ordinamento $order = $q->param('order'); $dir = $q->param('dir'); $query_order = "ORDER BY status DESC, "; if ($order ne "") { $query_order .= $order; if ($dir eq "ASC") { $query_order .= " ASC"; } else { $query_order .= " DESC"; } } else { $query_order .= "id ASC"; } $statement .= " $query_order"; $stato_a = ""; # $statement .= qq| ORDER BY id|; # fine by Ago $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $number=0; while(my $ref = $sth->fetchrow_hashref()) { if (($accesslevel =~ /$ref->{'category'}::/) || ($accesslevel =~ /GLOB::/)) { $number++; $subject="$ref->{'subject'}"; $subject=substr($subject,0,50).'..' if length($subject) > 52; if ($ref->{'priority'} eq 1) { $font = '#000000'; } else { $font = '#000000'; } # by Ago 19/11/2006 - separa i ticket per # stato... if ($stato_a ne $ref->{'status'}) { if ($stato_a ne "") { $call .= "
 "; } $stato_a = $ref->{'status'}; } # by Ago - 12/03/2007 @time = split(/-/, $ref->{'time'}); $tempo = $time[0]."-".$time[1]."-".$time[2]."
".$time[3]; $progetto_associato = get_nome_progetto($ref->{'id'}); $url_progetto_associato = get_url_progetto($ref->{'id'}); $call .= '
 {'id'}\">$subject" . ' ' . $progetto_associato . ' (' . "$ref->{ownership}" . ')
'; } } # by Ago $query_string = ""; foreach my $name ($q->param) { if (($name ne "order") && ($name ne "dir")) { $query_string .= $name . "=" . $q->param($name) . "&"; } } $template{'listcalls'} .= 'Ordina per [ Oggetto ] '; $template{'listcalls'} .= '/ [ Priorità ] '; $template{'listcalls'} .= '/ [ Ultimo Accesso ]
'; $template{'listcalls'} .= $call; $template{'customer'} = $userview; print parse("$global{'data'}/include/tpl/staff/viewbyuser"); } if ($section eq "log") { check_user(); print "Content-type: text/html\n\n"; $template{'form'} = ""; my $statement = 'SELECT * FROM ticket_fields'; my $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $template{'form'} .= qq|$ref->{'name'} |; } $sth->finish; $statement = 'SELECT level FROM departments'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $list .= ""; } $sth->finish; $template{'username'} = $q->param('user'); $template{'category'} = $list; my $progetto_id = $q->param('progetto_id'); # by Ago $dbh2 = DBI->connect("DBI:mysql:$dbname2:$dbhost2","$dbuser2","$dbpass2") or script_error("Could not connect to the project database"); $temp = ""; $dbh2->disconnect; $template{'progetti'} = $temp; # fine my $output = parse("$global{'data'}/include/tpl/staff/newcall"); print $output; } if ($section eq "logsave") { check_user(); print "Content-type: text/html\n\n"; if (defined $q->param('username') && $q->param('username') ne "") { $username = $q->param('username'); $statemente = 'SELECT * FROM users WHERE username = ?'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($username) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $name = $ref->{'name'}; $email = $ref->{'email'}; $url = $ref->{'url'}; } } else { $statement = 'SELECT * FROM ticket_fields WHERE (name = "email" OR name = "e-mail" OR name = "e mail") LIMIT 1'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $email = $q->param($ref->{'id'}); } $statement = 'SELECT * FROM ticket_fields WHERE name = "name" LIMIT 1'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $name = $q->param($ref->{'id'}); } $url = $q->param('url'); $username = "Call-Center"; } $subject = $q->param('subject'); $description = $q->param('description'); $priority = $q->param('priority'); $category = $q->param('category'); if ($q->param('subject')) { $current_time = time(); my $sth = $dbh->prepare( "INSERT INTO calls VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" ); $sth->execute( "NULL", "OPEN", $username, $email, $priority, $category, $subject, $description, $hdtime, "Unowned", "NULL", "cc", $current_time, $current_time, "1", "0" ); $callid = $dbh->{'mysql_insertid'}; my $statement = 'SELECT * FROM ticket_fields ORDER BY dorder'; my $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { my $fid = $ref->{'id'}; my $sth = $dbh->prepare( "INSERT INTO call_fields VALUES ( ?, ?, ?, ? )" ) or die $DBI->errstr; $sth->execute( "NULL", $callid, "$fid", $q->param($fid) ) or die $DBI->errstr; } $sth->finish; # by Ago if ($q->param('project_ID') ne "") { my $sth = $dbh->prepare( "INSERT INTO project_ass VALUES ( ?, ?, ?)" ); $sth->execute( "NULL", $callid, $q->param('project_ID') ); } # fine by Ago if ($enablemail && $q->param('email_user') == "1") { my $body; open (MAILNEWTPL,"$global{'data'}/include/tpl/newticket.txt"); while () { lang_parse() if $_ =~ /%*%/; if ($_ =~ /\{*\}/i) { s/\{baseurl\}/$global{'baseurl'}/g; s/\{name\}/$name/g; s/\{subject\}/$subject/g; s/\{mainfile\}/$template{'mainfile'}/g; s/\{description\}/$description/g; s/\{lang\}/$language/g; s/\{date\}/$hdtime/g; } $body .= "$_"; } close(MAILNEWTPL); my $subject = "\{$global{'epre'}-$callid\} Help Desk Submission"; email ( To => "$email", From => "$global{'adminemail'}", Subject => "$subject", Body => "$body" ); } } else { $response = 'Inserisci l\'oggetto della Richiesta.'; $template{'response'} = $response; print parse("$global{'data'}/include/tpl/staff/general"); exit; } # by Ago # $response = "La tua Richiesta è stata inserita e va ora assegnata."; # $template{'response'} = $response; # print parse("$global{'data'}/include/tpl/staff/general"); $template{'response'} = $callid; print parse("$global{'data'}/include/tpl/staff/general_ago"); # fine by Ago } if ($section eq "lookup") { check_user(); print "Content-type: text/html\n\n"; my $output = parse("$global{'data'}/include/tpl/staff/lookup"); print $output; } if ($section eq "lookupresults") { check_user(); print "Content-type: text/html\n\n"; $field = $q->param('select'); $query = $q->param('query'); push @html, "
USER LOOKUP

"; $dbh2 = DBI->connect("DBI:mysql:$dbname2:$dbhost2","$dbuser2","$dbpass2") or script_error("Could not connect to the project database"); $statemente = 'SELECT * FROM users WHERE ' . "$field" . ' LIKE "%' . "$query" . '%"'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $user .= ''; $user .= ''; # Sottoprogetti associati all'utente: $qr = "SELECT ID, name FROM `projekte` WHERE kategorie<>'4' AND parent='0'"; $qr .= " AND name LIKE '%".$ref->{'username'}."%'"; $qr .= " ORDER BY name ASC"; $sth2 = $dbh2->prepare($qr) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth2->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref3 = $sth2->fetchrow_hashref()) { $IDp = $ref3->{'ID'}; if (($IDp ne "") && ($IDp ne "0")) { $qr2 = "SELECT ID, name FROM `projekte` WHERE kategorie<>'4'"; $qr2 .= " AND parent = '" . $IDp . "'"; $qr2 .= " ORDER BY name ASC"; $sth3 = $dbh2->prepare($qr2) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth3->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref2 = $sth3->fetchrow_hashref()) { $user .= ''; } } } $user .= '
' . "$ref->{'name'}" . ' [ {'username'}" . '">Nuovo ticket] [ {'username'}" . '">Ticket utente] [ {'username'}" . '&stato=OPEN">Ticket aperti utente]
  * '.$ref2->{'name'}.' [ Nuovo ticket] [ Ticket prog.] [ Ticket aperti prog.]
'; } if (!$user) { $user = '
Spiacente, la query non ha dato nessun risultato.
'; } $template{'response'} = $user; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; } if ($section eq "editnote") { check_user(); print "Content-type: text/html\n\n"; $noteid = $q->param('nid'); $statemente = 'SELECT * FROM notes WHERE id = "' . "$noteid" . '";'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $response= '|; } $tnum++; } $sth->finish; $template{'depstats'} = "" if !$template{'depstats'}; @types = ("em","new","open"); foreach $type (@types) { chomp($type); if ($accesslevel =~ /GLOB::/) { $statement = qq|SELECT * FROM calls WHERE status != "CLOSED"| if $type eq "open"; $statement = qq|SELECT * FROM calls WHERE (status != "CLOSED" AND priority = "1")| if $type eq "em"; $statement = qq|SELECT * FROM calls WHERE status != "CLOSED"| if $type eq "new"; if (%ticket) { $statement .= qq| AND (|; $num=0; foreach my $key (keys %ticket) { $statement .= qq|id != "$key" | if $ticket{$key} == "1" && $num == "0"; $statement .= qq|AND id != "$key" | if $ticket{$key} == "1" && $num != "0"; $num++; } $statement .= qq|)|; } $statement .= qq| ORDER BY id DESC LIMIT 0, 5|; } else { @access = split(/::/,$accesslevel); $ac = 0; foreach $ddep (@access) { $ac++; } $statement = 'SELECT * FROM calls WHERE status != "CLOSED" AND priority = "1" AND ( ' if $type eq "em"; $statement = 'SELECT * FROM calls WHERE status != "CLOSED" AND ( ' if $type ne "em"; $i = 1; foreach $dep (@access) { $dep =~ s/^\s+//g; $statement .= "category = \"$dep\" "; if ($i < $ac) { $statement .= 'OR '; } $i++; } if (%ticket) { $statement .= qq|) AND (|; $num=0; foreach my $key (keys %ticket) { $statement .= qq|id != "$key" | if $ticket{$key} == "1" && $num == "0"; $statement .= qq|AND id != "$key" | if $ticket{$key} == "1" && $num != "0"; $num++; } } $statement .= ") ORDER BY ID DESC LIMIT 5"; } $sth = $dbh->prepare($statement) or die "Couldn't prepare statement : $DBI::errstr; stopped"; $sth->execute() or die "Couldn't execute statement . $accesslevel . ($statement): $DBI::errstr; stopped"; $number = 0; while($ref = $sth->fetchrow_hashref()) { $id = $ref->{'id'}; next if $ticket{$id} == "1"; if ($ref->{'status'} ne "CLOSED") { $number++; $font = '#000000'; $subject = $ref->{'subject'}; $subject =~ s/^\'(.*)\'$/$1/; $subject = substr($subject,0,50).'..' if length($subject) > 52; if ($ref->{'method'} eq "cc" ) { $img = "phone.gif"; } elsif ($ref->{'method'} eq "em") { $img = "mail.gif"; } else { $img = "ticket.gif"; } if ($subject =~ /^\'/) { $subject = $1 if $subject =~ /\'(\S+)\'/; } if ($ref->{'priority'} eq "1") { $color = $pri1; } if ($ref->{'priority'} eq "2") { $color = $pri2; } if ($ref->{'priority'} eq "3") { $color = $pri3; } if ($ref->{'priority'} eq "4") { $color = $pri4; } if ($ref->{'priority'} eq "5") { $color = $pri5; } if ($ref->{'aw'} eq "1") { $awt = ""; } else { $awt = ""; } $current_time = time(); $difference = $current_time - $ref->{'track'}; if ($difference >= 86400) { $period = "day"; $count = $difference / 86400; } elsif ($difference >= 3600) { $period = "hour"; $count = $difference / 3600; } elsif ($difference >= 60) { $period = "minute"; $count = $difference / 60; } else { $period = "second"; $count = $difference; } $count = sprintf("%.0f", $count); if ($count != 1) { $period .= "s"; } $period .= " Ago"; if ($ref->{'ownership'} eq $Cookies{'staff'}) { $otag = ""; $ctag = ""; } else { $otag = ''; $ctag=''; } $tcall = $type; $tcall .= "_call"; $template{$tcall} .= display_ticket($ref->{'id'}, $ref->{'username'}, $ref->{'category'}, $ref->{'status'}); $ticket{$id} = 1; } } $tcall = $type; $tcall .= "_call"; $template{$tcall} = 'Nessuna Richiesta' if !$template{$tcall}; } $current_time = time(); $statemente = 'SELECT * FROM staffread WHERE username = ?'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($smember) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $lastactive = $ref->{'date'}; } $unread = 0; $statemente = 'SELECT stamp FROM messages WHERE touser = ?'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($smember) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if ($ref->{'stamp'} > $lastactive) { $unread++; } } $inbox = "
Hai " . '$unread messaggi non letti
"; $template{'announcement'} = $notice; $template{'open'} = $owned; $template{'pm'} = $inbox; $template{'queue'} = $queue; $template{'user'} = $Cookies{'staff'}; $template{'deplist'} = $depq; $output = parse("$global{'data'}/include/tpl/staff/staffmain"); print $output; } if ($section eq "logout") { check_user(); $statement = 'UPDATE staffactive SET date = "0" WHERE username = "' . "$Cookies{'staff'}" . '";'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $sth->finish; $cookie1 = $q->cookie(-name =>'staff', -value =>'', -path =>'/', -domain=>''); $cookie2 = $q->cookie(-name =>'spass', -value =>'', -path =>'/', -domain=>''); print $q->header(-cookie=>[$cookie1,$cookie2]); $response = "Ora sei disconnesso."; $template{'response'} = $response; print parse("$global{'data'}/include/tpl/staff/general"); } if ($section eq "notice") { check_user(); print "Content-type: text/html\n\n"; $notic = $q->param('id'); $statement = 'SELECT * FROM announce WHERE staff = "1" AND id = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($notic) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $notice .= '
Modifica [ID Richiesta: ' . "$ref->{'call'}" . ']

MODIFICA RICHIESTA
{'call'}" . '">{'id'}" . '">
'; } $template{'response'} = $response; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; } if ($section eq "update_list_calls") { check_user(); my $status = $q->param('status'); my $action = $q->param('action_call'); if ($action eq "delete") { @checkbx = $q->param('ticket_check'); for ( @checkbx ) { die_nice("L\'Amministratore impedisce allo Staff di eliminare le richieste degli utenti") if $global{'sdelete'} == "0"; $dbh->do(qq|DELETE FROM calls WHERE id = "$_"|); # by Ago # aggiornadb($_); $statement = 'DELETE FROM project_ass WHERE ID = "$_"'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; ########## } print "Content-type: text/html\n\n"; print qq|

 

 

Lista Richiesta Aggiornata, you are now being taken back to the listings.

clicca qui se non riesci ad entrare automaticamente

|; } if ($action eq "respond") { @checkbx = $q->param('ticket_check'); for ( @checkbx ) { $template{'hidden'} .= qq|\n|; } print "Content-type: text/html\n\n"; my $output = parse("$global{'data'}/include/tpl/staff/mass_respond"); print $output; } } if ($section eq "refresh_calls") { check_user(); print "Content-type: text/html\n\n"; my $status = $q->param('status'); my $comments = $q->param('comments'); $mstatus = "open" if $status eq "OPEN"; $mstatus = "closed" if $status eq "CLOSED"; @checkbx = $q->param('ticket_check'); for ( @checkbx ) { $dbh->do(qq|UPDATE calls SET status = "$status", closedby = "$Cookies{'staff'}" WHERE id = "$_"|) if $status eq "CLOSED"; $dbh->do(qq|UPDATE calls SET status = "$status" WHERE id = "$_"|) if $status ne "CLOSED"; $rv = $dbh->do(qq{INSERT INTO notes values ( "NULL", "1", "1", "$status", "$_", "$name", "$hdtime", "$comments")} ); } # by Ago aggiorna_tempo($_); # fine by Ago print qq|

 

 

Ticket List Updated, you are now being taken back to the listings.

click here if you are not automatically forwarded

|; } if ($section eq "editnotesave") { check_user(); print "Content-type: text/html\n\n"; my $comment = $q->param('comment'); my $ticket = $q->param('ticket'); my $note = $q->param('note'); $comment =~ s/\"/\\"/g; $statement = 'UPDATE notes SET comment = "' . "$comment" . '" WHERE id = "' . "$note" . '";'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; # by Ago aggiorna_tempo($trackno); # fine by Ago print qq|

 

 

Grazie, la richiesta è stata modificata.

clicca qui se non riesci ad entrare automaticamente

|; } if ($section eq "main") { check_user(); print "Content-type: text/html\n\n"; my ($statement); if (defined $q->param('sort')) { $sort = $q->param('sort'); } else { $sort = "id"; } if (defined $q->param('method')) { if ($q->param('method') eq "asc") { $method = "ASC"; } else { $method = "DESC"; } } else { $method = "ASC"; } $sth = $dbh->prepare( "SELECT COUNT(*) FROM calls WHERE ownership = \"$Cookies{'staff'}\" AND status != \"CLOSED\"" ) or die DBI->errstr; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $owned = $sth->fetchrow_array(); $statemet = 'SELECT * FROM announce WHERE staff = "1"'; $sth = $dbh->prepare($statemet) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $notice .= '
{'id'}\">$ref->{'subject'}" . '' . "$ref->{'time'}" . '
'; } $notice = '0 Annunci' if !$notice; my $pri1 = $global{'pri1'}; my $pri2 = $global{'pri2'}; my $pri3 = $global{'pri3'}; my $pri4 = $global{'pri4'}; my $pri5 = $global{'pri5'}; $tnum = 1; $statemen = 'SELECT level FROM departments'; $sth = $dbh->prepare($statemen) or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if (($accesslevel =~ /$ref->{'level'}::/) || ($accesslevel =~ /GLOB::/)) { $ssth = $dbh->prepare( "SELECT COUNT(*) FROM calls WHERE category = ? AND status != \"CLOSED\"" ) or die DBI->errstr; $ssth->execute( $ref->{'level'} ) or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $queue = $ssth->fetchrow_array(); $ssth->finish; $ssth = $dbh->prepare( "SELECT COUNT(*) FROM calls WHERE (category = ? AND status != \"CLOSED\" AND aw = \"1\")" ) or die DBI->errstr; $ssth->execute( $ref->{'level'} ) or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $staff = $ssth->fetchrow_array(); $ssth->finish; $ssth = $dbh->prepare( "SELECT COUNT(*) FROM calls WHERE (category = ? AND status != \"CLOSED\" AND aw = \"0\")" ) or die DBI->errstr; $ssth->execute( $ref->{'level'} ) or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $user = $ssth->fetchrow_array(); $ssth->finish; $template{'dep_stats'} .= qq|
$ref->{'level'}
$queue
$staff
$user
Author:' . "$ref->{'author'}" . '
Published:' . "$ref->{'time'}" . '
Subject:' . "$ref->{'subject'}" . '

Announcement:' . "$ref->{'message'}" . '
'; } $template{'announcement'} = $notice; my $output = parse("$global{'data'}/include/tpl/staff/announcement"); print $output; } if ($section eq "myperformance") { check_user(); print "Content-type: text/html\n\n"; $sth = $dbh->prepare(qq|SELECT COUNT(*) FROM calls WHERE status = "CLOSED" AND closedby = "$Cookies{'staff'}"|) or die DBI->errstr; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $closed = $sth->fetchrow_array(); $sth->finish; $sth = $dbh->prepare('SELECT COUNT(*) FROM calls WHERE status = "CLOSED"' ) or die DBI->errstr; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $total = $sth->fetchrow_array(); $sth->finish; $sth = $dbh->prepare('SELECT COUNT(*) FROM calls') or die DBI->errstr; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $count = $sth->fetchrow_array(); $sth->finish; if ($closed > 0) { $per = ($closed/$total)*100; $perc = sprintf("%.2f",$per); } else { $perc = "0" } $statement = 'SELECT responsetime FROM staff WHERE username = "' . "$Cookies{'staff'}" . '"'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $tsec = $ref->{'responsetime'}; } if ($closed > 0) { $sec = ($tsec/$closed); $days = int($sec/(24*60*60)); $hours = ($sec/(60*60))%24; $mins = ($sec/60)%60; $secs = $sec%60; $restime = "$hours hr(s) $mins min(s) $secs seconds"; } else { $restime = "Nessuna richiesta chiusa"; } $template{'avgresp'} = $restime; $template{'perc'} = $perc; $template{'closed'} = $closed; my $output = parse("$global{'data'}/include/tpl/staff/perf"); print $output; } # by Ago if ($section eq "listclientscalls") { check_user(); print "Content-type: text/html\n\n"; $template{'nav'} = ""; # da usare per la barra di navigazione, cioè # per spezzare l'output in più pagine. $opencalls = "Cliente"; $sth2 = $dbh->prepare( "SELECT COUNT(*) AS totale FROM `departments`" ) or die DBI->errstr; $sth2->execute() or die print "Couldn't execute statement $stat: $DBI::errstr; stopped"; $x = $sth2->fetchrow_hashref(); $divp = 55 / ($x->{'totale'}); $template{'ncol'} = ($x->{'totale'}) + 2; $sth2 = $dbh->prepare( "SELECT * FROM `departments`" ) or die DBI->errstr; $sth2->execute() or die print "Couldn't execute statement $stat: $DBI::errstr; stopped"; while ($level = $sth2->fetchrow_hashref()) { $opencalls .= "".$level->{'level'}.""; } $opencalls .= "Totale"; $sth = $dbh->prepare( "SELECT DISTINCT username FROM `calls` WHERE status = 'OPEN' ORDER BY `username` ASC" ) or die DBI->errstr; $sth->execute() or die print "Couldn't execute statement $stat: $DBI::errstr; stopped"; $cont = 0; while($ref = $sth->fetchrow_hashref()) { if ($cont % 2 == 0) { $color = "#E8E8E8"; } else { $color = "#f2f2f2" }; $opencalls .= "".$ref->{'username'}.""; $tot = 0; $cont++; $sth2 = $dbh->prepare( "SELECT * FROM `departments`" ) or die DBI->errstr; $sth2->execute() or die print "Couldn't execute statement $stat: $DBI::errstr; stopped"; while($level = $sth2->fetchrow_hashref()) { $sth3 = $dbh->prepare( "SELECT COUNT(*) AS totale FROM `calls` WHERE status='OPEN' AND username = '".$ref->{'username'}."' AND category = '".$level->{'level'}."'" ) or die DBI->errstr; $sth3->execute() or die print "Couldn't execute statement $stat: $DBI::errstr; stopped"; $num = $sth3->fetchrow_hashref(); $opencalls .= "".$num->{'totale'}.""; } $sth3 = $dbh->prepare( "SELECT COUNT(*) AS totale FROM `calls` WHERE status='OPEN' AND username = '".$ref->{'username'}."'" ) or die DBI->errstr; $sth3->execute() or die print "Couldn't execute statement $stat: $DBI::errstr; stopped"; $num = $sth3->fetchrow_hashref(); $opencalls .= "".$num->{'totale'}.""; # $opencalls .= "".$tot.""; } $template{'listcalls'} = $opencalls; print parse("$global{'data'}/include/tpl/staff/listclientscalls"); } if ($section eq "listcalls") { check_user(); print "Content-type: text/html\n\n"; $status = $q->param('status'); $type = $status; $template{'status'} = $status; if (defined $q->param('sort')) { $sort = $q->param('sort'); } else { $sort = "id"; } if (defined $q->param('method')) { if ($q->param('method') eq "asc") { $method = "ASC"; } else { $method = "DESC"; } } else { $method = "ASC"; } if ($status eq "open") { if ($accesslevel =~ /GLOB::/) { $sth = $dbh->prepare( "SELECT COUNT(*) FROM calls WHERE status != \"CLOSED\"" ) or die DBI->errstr; } else { @access = split(/::/, $accesslevel); $ac = 0; foreach $dep (@access) { $ac++; } $statement = 'SELECT COUNT(*) FROM calls WHERE status != "CLOSED" AND '; $i = 1; foreach $dep (@access) { $dep=~s/^\s+//g; $statement .= 'category = "' . "$dep" . '" '; if ($i < $ac) { $statement .= 'OR '; } $i++; } $sth = $dbh->prepare($statement) or die DBI->errstr; } } else { $sth = $dbh->prepare( "SELECT COUNT(*) FROM calls WHERE status = \"CLOSED\" AND closedby = \"$Cookies{'staff'}\"" ) or die DBI->errstr; } $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my ( $total ) = $sth->fetchrow_array(); $limit = "30"; # Results per page my $pages = ($total/$limit); $pages = ($pages+0.5); my $nume = sprintf("%.0f",$pages); $page = $q->param('page') || "0"; $nume = "1" if !$nume; $to = ($limit * $page) if $page; $to = "0" if !$page; $tpage = $page; $tpage = $tpage + 1; $location = $page; $location = $location + 1; $lboundary = "1" if $location < 4; $lboundary = $location - 3 if !$lboundary; $nav .= qq|$nume Pagina(e): |; if ($location + 2 < $nume) { $tboundary = 3 + $location; } else { $tboundary = $nume; } $string = $ENV{'QUERY_STRING'}; $string =~ s/&page=(.*)//g; $prev = $page; $prev = $prev - 1; if ($nume > 1 && $tpage > 1 ) { $nav .= qq|« ‹ |; } foreach ($lboundary..$tboundary) { my $nu = $_ -1; if ($nu eq $page) { $link = "[$_]"; } else { $link = "$_"; } $nav .= qq|$link |; } $next = $page; $next = $next + 1; $last = $nume; $last = $last - 1; if ($tpage < $nume && $tpage >= 1) { $nav .= qq|› »|; } $template{'nav'} = $nav; my $show = $limit * $page; if ($status eq "open") { $template{'cc'} = ""; if ($accesslevel =~ /GLOB::/) { $statement = qq|SELECT * FROM calls WHERE status != "CLOSED" ORDER BY $sort $method LIMIT $show, $limit|; } else { @access = split(/::/,$accesslevel); $ac = 0; foreach $ddep (@access) { $ac++; } $statement = 'SELECT * FROM calls WHERE ('; $i = 1; foreach $dep (@access) { $dep=~s/^\s+//g; $statement .= "category = \"$dep\" "; if ($i < $ac) { $statement .= 'OR '; } else { $statement .= ")"; } $i++; } $statement .= " AND status != \"CLOSED\" ORDER BY $sort $method LIMIT $show, $limit"; } } else { $template{'cc'} = qq|
FILTRO: ( CHIUSE DA ME / CHIUSE DA TUTTI )|; if ($q->param('filter') == "1") { if ($accesslevel =~ /GLOB::/) { $statement = qq|SELECT * FROM calls WHERE status = "CLOSED" ORDER BY $sort $method LIMIT $show, $limit|; } else { @access = split(/::/,$accesslevel); $ac = 0; foreach $ddep (@access) { $ac++; } $statement = 'SELECT * FROM calls WHERE ('; $i = 1; foreach $dep (@access) { $dep=~s/^\s+//g; $statement .= "category = \"$dep\" "; if ($i < $ac) { $statement .= 'OR '; } else { $statement .= ")"; } $i++; } $statement .= " AND status = \"CLOSED\" ORDER BY $sort $method LIMIT $show, $limit"; } } else { $statement = "SELECT * FROM calls WHERE status = \"CLOSED\" AND closedby = \"$Cookies{'staff'}\" ORDER BY $sort $method LIMIT $show,$limit"; } } $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement $stat: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement $stat: $DBI::errstr; stopped"; $number=0; while($ref = $sth->fetchrow_hashref()) { $number++; $subject="$ref->{'subject'}"; $subject=substr($subject,0,50).'..' if length($subject) > 52; $font = '#000000'; if ($ref->{'method'} eq "cc") { $img = "phone.gif"; } elsif ($ref->{'method'} eq "em") { $img = "mail.gif"; } else { $img = "ticket.gif"; } if ($ref->{'priority'} eq "1") { $color = $global{'pri1'}; } if ($ref->{'priority'} eq "2") { $color = $global{'pri2'}; } if ($ref->{'priority'} eq "3") { $color = $global{'pri3'}; } if ($ref->{'priority'} eq "4") { $color = $global{'pri4'}; } if ($ref->{'priority'} eq "5") { $color = $global{'pri5'}; } if ($ref->{'aw'} eq "1") { $awt = ""; } else { $awt = ""; } $current_time = time(); $difference = $current_time - $ref->{'track'}; if ($difference >= 86400) { $period = "day"; $count = $difference / 86400; } elsif ($difference >= 3600) { $period = "hour"; $count = $difference / 3600; } elsif ($difference >= 60) { $period = "minute"; $count = $difference / 60; } else { $period = "second"; $count = $difference; } $count = sprintf("%.0f", $count); if ($count != 1) { $period .= "s"; } $period .= " Ago"; if ($ref->{'ownership'} eq $Cookies{'staff'}) { $otag = ""; $ctag = ""; } else { $otag = ''; $ctag=''; } # mod rob $nstatus = $ref->{'status'}."(".$ref->{'ownership'}.")"; # $opencalls .= display_ticket($ref->{'id'}, $ref->{'username'}, $ref->{'category'}, $ref->{'status'}); $opencalls .= display_ticket($ref->{'id'}, $ref->{'username'}, $ref->{'category'}, $nstatus); } $opencalls = '0 Richieste' if !$opencalls; $template{'listcalls'} = $opencalls; $template{'type'} = $status; $template{'path'} = "staff.cgi" . '?' . $ENV{'QUERY_STRING'}; $template{'path'} =~ s/&sort=(.*)//; $template{'path'} =~ s/&method=(.*)//; print parse("$global{'data'}/include/tpl/staff/listcalls"); } exit; } sub function { $action = "@_"; if ($action eq "history") { $trackedcall = $q->param('callid'); $statement = 'SELECT * FROM calls WHERE id = ? ORDER BY id'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackedcall) or die print "Couldn't execute statement: $DBI::errstr; stopped"; $number=0; while(my $ref = $sth->fetchrow_hashref()) { print qq|PerlDesk
Subject $ref->{'subject'} 
$ref->{description}
 
Responses
|; } $statemente = 'SELECT * FROM notes WHERE `call` = ? ORDER BY id;'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackedcall) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $body = $ref->{'comment'}; if ($body =~ /^/g; } if ($ref->{'owner'} eq 0) { print '
' . "$ref->{'author'}" . '
' . "$ref->{'time'}" . '

 ' . "$body" . '
'; } if ($ref->{'owner'} eq 3) { print '
' . "$ref->{'author'}" . '
' . "$ref->{'time'}" . '

 ' . "STAFF ACTION: $ref->{'action'}
$body" . '
'; } if ($ref->{'owner'} eq 1) { if ($ref->{'visible'} eq 0) { $notice = 'PRIVATE STAFF NOTE
'; } else { $notice = ''; } print '
' . "$ref->{'author'}" . '
' . "$ref->{'time'}" . '

 ' . "$notice $body" . '
'; } } print qq|
|; exit; } if ($action eq "print") { if (defined $q->param('callid')) { $trackedcall = $q->param('callid'); print qq|$global{'title'}: Print Request

|; $statement = 'SELECT * FROM calls WHERE id = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackedcall) or die print "Couldn't execute statement: $DBI::errstr; stopped"; $number=0; while(my $ref = $sth->fetchrow_hashref()) { if (($accesslevel =~ /$ref->{'category'}::/) || ($accesslevel =~ /GLOB::/)) { $trackno = $ref->{'id'}; $date = $ref->{'time'}; $username = $ref->{'username'}; $priority = $ref->{'priority'}; $status = $ref->{'status'}; $subject = $ref->{'subject'}; $category = $ref->{'category'}; $description = $ref->{'description'}; $owner = $ref->{'ownership'}; $number = 0; $email = $ref->{'email'}; $url = $ref->{'url'}; $name = $ref->{'name'}; } else { $error = 'Non hai i permessi per visualizzare questa richiesta'; push @errors, $error; } } if (@errors) { @content = @errors; print @content; exit; } print qq|
 
ID RICHIESTA $trackedcall
INSERITA IL: $date
DA:
 
 
$subject
$description
  
 
RISPOSTE
|; $statemente = 'SELECT * FROM notes WHERE `call` = ? ORDER BY id;'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackedcall) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $body = $ref->{'comment'}; if ($body =~ /^/g; } if (($ref->{'owner'} eq 0) || ($ref->{'owner'} eq 1)) { print qq|

$ref->{'author'} $ref->{'time'}
$body

|; } if ($ref->{'owner'} eq 3) { print '
' . "$ref->{'author'}" . '
' . "$ref->{'time'}" . '

 ' . "STAFF ACTION: $ref->{'action'}
$body" . '

'; } } print qq|
|; } else { print "No call defined!"; exit; } exit; } if ($action eq "movedep") { $callid = $q->param('callid'); # modifica Agostino: $commentsmail = $q->param('comment'); $comments = quotemeta($commentsmail); $titolo = $q->param('titolo'); $cliente = $q->param('cliente'); $priority = $q->param('priority'); $newstatus = $q->param('newstatus'); $pID = $q->param('project_ID'); if ($newstatus eq "Unresolved") { $status = OPEN; } if ($newstatus eq "Resolved") { $status = CLOSED; } if ($newstatus eq "Hold") { $status = HOLD; } if ($pID ne "") { $sth = $dbh->prepare("SELECT COUNT(*) AS totale, project_ID FROM project_ass WHERE ID = '$callid' GROUP by project_ID") or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $ref2 = $sth->fetchrow_hashref(); if ($ref2->{'totale'} > 0) { $qr = "UPDATE project_ass SET project_ID = '$pID' WHERE ID = '$callid'"; } else { $qr = "INSERT INTO project_ass VALUES ( NULL, '$callid', '$pID')"; } $sth = $dbh->prepare($qr); $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; aggiornadb($ref2->{'project_ID'}); aggiornadb($pID); } # codice originale: # $comments = $q->param('comment'); # $dep = $q->param('select'); $tech = $q->param('assignstaff'); my $rv = $dbh->do(qq{INSERT INTO notes values ( "NULL", "3", "1", "ASSIGN", "$callid", "$Cookies{'staff'}", "$hdtime", "$comments")} ); $statement = 'UPDATE calls SET category = "' . "$dep" . '", ownership = "' . "$tech" . '", priority = "' . "$priority" . '", status = "' . "$status" . '" WHERE id = "' . "$callid" . '"'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; # by Ago aggiorna_tempo($callid); # fine by Ago $response = "
Grazie. La richiesta con ID $callid è stata assegnata a $tech, $dep
"; $template{'response'} = $response; #mod rob invia email al nuovo assegnatario $techmail = "$tech"."\@neikos.eu"; open(MAIL, "|$global{'sendmail'} -t") || print "Unable to send mail: $!"; print MAIL "To: $techmail \n"; print MAIL "From: $global{'adminemail'}\n"; print MAIL "Subject: Neikos Help Desk: Assegnazione Richiesta \n\n"; print MAIL "Assegnazione interna: http://agenda.neikos.it/cgi-bin/desk/staff.cgi?do=ticket&cid=$callid\n"; print MAIL "\nDettagli Richiesta\n"; print MAIL "---------------------------------------\n"; # aggiunta codice by Agostino: print MAIL "\t Cliente..............: $cliente\n"; print MAIL "\t Titolo................: $titolo\n"; # fine modifica. print MAIL "\t Richiesta...........: $callid\n"; print MAIL "\t Commenti.........: $commentsmail\n"; print MAIL "---------------------------------------\n"; print MAIL "\n\nGrazie."; close(MAIL); #fine aggiornadb($callid); my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; exit; } if ($action eq "assign") { $callid = $q->param('callid'); # Aggiunta codice by Agostino # Dati della richiesta: $statement = 'SELECT category, priority, subject, ownership, username FROM calls WHERE id = \'' . $callid . '\''; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while (my $ref = $sth->fetchrow_hashref()) { $cliente = $ref->{'username'}; $titolo = $ref->{'subject'}; $area = $ref->{'category'}; $membro = $ref->{'ownership'}; } $sth->finish; # fine aggiunta $statement = 'SELECT level FROM departments'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { # Aggiunta codice by Agostino $testo = ""; if ($area eq ($ref->{'level'})) { $testo = "selected"; } # fine aggiunta $option = ""; # $option = ""; # originale. push @list, $option; } $sth->finish; push @slist, ""; $statement = 'SELECT username, name FROM staff'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { # Aggiunta codice by Agostino $testo = ""; if ($membro eq ($ref->{'username'})) { $testo = "selected"; } # fine aggiunta $option = ""; # $option = ""; push @slist, $option; } $sth->finish; # Aggiunta by Ago $statement = 'SELECT priority, status FROM calls WHERE id = \'' . $callid . '\''; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while (my $ref = $sth->fetchrow_hashref()) { $priority = $ref->{'priority'}; $status = $ref->{'status'}; } $sth->finish; $sel1 = ""; $sel2 = ""; $sel3 = ""; $sel4 = ""; $sel5 = ""; eval('$sel'.$priority.'="selected";'); # fine # Per l'associazione ai progetti by Ago $sth = $dbh->prepare("SELECT project_ID FROM project_ass WHERE ID='$callid'") or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $ref = $sth->fetchrow_hashref(); $pID = $ref->{'project_ID'}; $dbh2 = DBI->connect("DBI:mysql:$dbname2:$dbhost2","$dbuser2","$dbpass2") or script_error("Could not connect to the project database"); $temp = ""; @check = ("", "", ""); if ($status eq "HOLD") { $check[2] = "selected"; } elsif ($status eq "CLOSED") { $check[0] = "selected"; } else { $check[1] = "selected"; } # fine by Ago $response = '
Assegnazione Interna
Puoi assegnare questa richiesta ad un altra Area.
   
 Area
Membro Staff
 Associazione ' . $temp . '
 Stato
 Priorità
 Commenti
'; $template{'response'} = $response; my $output = parse("$global{'data'}/include/tpl/staff/general"); print $output; exit; } if ($action eq 'addresponse') { if (defined $q->param('callid')) { $trackno = $q->param('callid'); } else { $trackno = $q->param('ticket'); } $statement = 'SELECT * FROM staff WHERE username = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute("$Cookies{'staff'}") or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if ($ref->{'signature'}) { $line = "\n\n\n\n-------------\n"; push @signature, $line; push @signature, "$ref->{'signature'}"; } } if (!@signature) { @signature = ''; } foreach $line (@signature) { $sig .= $line; } $statement = 'SELECT * FROM preans WHERE author = ? OR author = "admin"'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute("$Cookies{'staff'}") or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if ($ref->{'author'} eq "$Cookies{'staff'}") { $ddmenu .= ' '; } if ($ref->{'author'} eq "admin") { $dmenu .= ' '; } } if (defined $q->param('pretext')) { $id = $q->param('pretext'); $statement = 'SELECT * FROM preans WHERE id = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($id) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $comments = $ref->{'text'}; } } else { $comments = ''; } $statement = 'SELECT description FROM calls WHERE id = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackno) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $template{'body'} = $ref->{'description'}; } $template{'ifpre'} = $comments; $template{'preans'} = $ddmenu; $template{'apreans'} = $dmenu; $template{'sig'} = $sig; $template{'trackno'} = $trackno; my $output = parse("$global{'data'}/include/tpl/staff/staffnote"); print $output; $dbh->disconnect; exit; } if ($action eq "editticket") { my $trackno = $q->param('ticket'); $statement = 'SELECT * FROM calls WHERE id = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackno) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $template{'call'} = $ref->{'description'}; } $template{'trackno'} = $trackno; my $output = parse("$global{'data'}/include/tpl/staff/editcall"); print $output; $dbh->disconnect; exit; } if ($action eq "save_edit_call") { $trackno = $q->param('ticket'); $text = quotemeta($q->param('comments')); $text .= "\n\n"; $text .= "[ Call last edited by $Cookies{'staff'}: $hdtime ]"; $dbh->do(qq|UPDATE calls SET description = "$text" WHERE id = "$trackno"|); # by Ago aggiorna_tempo($trackno); # fine by Ago $template{'trackno'} = $trackno; $dbh->disconnect; print qq|

 

 

Grazie, la richiesta è stata modificata.

clicca qui se non riesci ad entrare automaticamente

|; exit; } if ($action eq 'submitnote') { $trackno = $q->param('ticket'); $snewstatus = $q->param('newstatus'); $private = $q->param('private'); $comments = $q->param('comments'); $notific = $q->param('notify'); $dbh->do(qq|UPDATE calls SET ownership = "$Cookies{'staff'}" WHERE id = "$trackno"|) if $q->param('own') == "1"; # by Ago aggiorna_tempo($trackno); # fine by Ago if ($snewstatus eq "Resolved") { $action = "CLOSED"; } elsif ($snewstatus eq "Unresolved") { $action = "OPEN"; } elsif ($snewstatus eq "Hold") { $action = "HOLD"; } $ecomments = $comments; $comments =~ s/\"/\\"/g; $statement = 'UPDATE calls SET aw = "0" WHERE id = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackno) or die print "Couldn't execute statement: $DBI::errstr; stopped"; if ($private eq "Yes") { $rv = $dbh->do(qq{INSERT INTO notes values ( "NULL", "1", "0", "$action", "$trackno", "$name", "$hdtime", "$comments")} ); # by Ago aggiorna_tempo($trackno); # fine by Ago } elsif ($private eq "No") { $rv = $dbh->do(qq{INSERT INTO notes values ( "NULL", "1", "1", "$action", "$trackno", "$name", "$hdtime", "$comments")} ); # by Ago aggiorna_tempo($trackno); # fine by Ago if ($snewstatus eq "Resolved") { $statement = 'SELECT * FROM calls WHERE id = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackno) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $logtime = $ref->{'track'}; $pretrack = $ref->{'an'}; $problem = $ref->{'description'}; } if (!$pretrack) { $statemente = 'SELECT * FROM settings WHERE setting = "ssi_closed"'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $totalcalls = $ref->{'value'}; } $totalcalls++; $statement = 'UPDATE settings SET value = ? WHERE setting = "ssi_closed"'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($totalcalls) or die print "Couldn't execute statement: $DBI::errstr; stopped"; $sth->finish; $statement = 'SELECT value FROM settings WHERE setting = "avgtime"'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $system_time = $ref->{'value'}; } $sth->finish; $current_time = time(); $end_time = $current_time-$logtime; $system_newtime = $end_time+$system_time; $statement = 'UPDATE settings SET value = ? WHERE setting = "avgtime"'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($system_newtime) or die print "Couldn't execute statement: $DBI::errstr; stopped"; $sth->finish; $statement = 'SELECT responsetime FROM staff WHERE username = "' . "$Cookies{'staff'}" . '"'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $stime = $ref->{'responsetime'}; } $sth->finish; $res_time = $current_time - $logtime; $newtime = $res_time+$stime; $statement = 'UPDATE staff SET responsetime = "' . "$newtime" . '" WHERE username = "' . "$Cookies{'staff'}" . '";'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $sth->finish; $statement = 'UPDATE calls SET an = "1" WHERE id = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackno) or die print "Couldn't execute statement: $DBI::errstr; stopped"; $sth->finish; } } } $statement = 'SELECT category FROM calls WHERE id = ?'; $sth = $dbh->prepare($statement)or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackno) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $cat = $ref->{'category'}; } if ($private eq "Yes") { $statement = 'SELECT * FROM staff WHERE access LIKE "%' . "$cat" . '::%" OR access LIKE "%GLOB::%" OR access="admin" AND username != "' . "$Cookies{'staff'}" . '"'; $sth = $dbh->prepare($statement)or die print "Couldnt prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldnt execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if ($ref->{'notify'}) { if ($enablemail) { open(MAIL, "|$global{'sendmail'} -t") || print "Unable to send mail: $!"; print MAIL "A: $ref->{'email'}\n"; print MAIL "Da: $global{'adminemail'}\n"; print MAIL "Oggetto: New Help Desk Response (STAFF MESSAGE)\n\n"; print MAIL "C'\è una nuova risposta dello Staff:\n"; print MAIL "\nDettagli Richiesta\n"; print MAIL "---------------------------------------\n"; print MAIL "\t Risposta di......: $name\n"; print MAIL "\t Data.............: $hdtime\n"; print MAIL "\t Richiesta...........: $trackno\n"; print MAIL "\t Commenti.........: $comments\n"; print MAIL "---------------------------------------\n"; print MAIL "\n\nGrazie."; close(MAIL); }} $sth->finish; } } # mod rob per non inviare mai questa email #if ($notific eq "Yes" && $private eq "No") { if ($notific eq "Yes" && $private eq "MAI") { $statement = 'SELECT * FROM calls WHERE id = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackno) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if ($enablemail) { open(MAIL, "|$global{'sendmail'} -t") || print "Unable to send mail: $!"; print MAIL "To: $ref->{'email'}\n"; print MAIL "From: $ticketad\n"; print MAIL "Subject: \{$global{'epre'}-$trackno\} Help Desk Response\n\n"; open (MAILNEWTPL, "$global{'data'}/include/tpl/response.txt"); while () { lang_parse(); if ($_ =~ /\{*\}/i) { s/\{name\}/$ref->{'name'}/g; s/\{tech\}/$name/g; s/\{response\}/$ecomments/g; s/\{time\}/$hdtime/g; s/\{baseurl\}/$global{'baseurl'}/g; s/\{mainfile\}/pdesk\.cgi/g; } print MAIL "$_"; } close(MAILNEWTPL); close(MAIL); } } } elsif ($notific eq "No") { # DO NOTHING } if ($snewstatus eq "Resolved") { $statemente = 'SELECT * FROM staff WHERE username = ?'; $sth = $dbh->prepare($statemente) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute("$Cookies{'staff'}") or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $callsclosed = $ref->{'callsclosed'}; } $callsclosed++; $statement = 'UPDATE staff SET callsclosed = "' . "$callsclosed" . '" WHERE username = "' . "$Cookies{'staff'}" . '";'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; } if ($snewstatus eq "Unresolved") { $newstatus = OPEN; } if ($snewstatus eq "Resolved") { $newstatus = CLOSED; } if ($snewstatus eq "Hold") { $newstatus = HOLD; } if ($snewstatus eq "Resolved") { $statement = 'UPDATE calls SET status = "' . "$newstatus" . '", closedby = "' . "$Cookies{'staff'}" . '" WHERE id = "' . "$trackno" . '";'; } else { $statement = 'UPDATE calls SET status = "' . "$newstatus" . '" WHERE id = "' . "$trackno" . '";'; } my $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; # by Ago aggiornadb($trackno); aggiorna_tempo($trackno); # fine by Ago if ($q->param('addtokb') eq "1") { $statement = 'SELECT * FROM calls WHERE id = ?'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($trackno) or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $problem = $ref->{'description'}; } $sth->finish; $statement = 'SELECT category FROM kb_cat'; $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $list .= ""; } $sth->finish; $template{'q'} = $problem; $template{'a'} = $comments; $template{'trackno'} = $trackno; $template{'category'} = $list; my $output = parse("$global{'data'}/include/tpl/staff/addtokb"); print $output; $dbh->disconnect; exit; } } if ($action eq "addtokb") { $category = $q->param('select'); $article = $q->param('article'); $subject = $q->param('subject'); $article =~ s/\"/\\"/g; $article =~ s/\n/
/g; $subject =~ s/\"/\\"/g; $trackno = $q->param('ticket'); if ($subject ne "") { my $rv = $dbh->do(qq{INSERT INTO kb_entries values ( "NULL", "$category", "$name", "$subject", "$article", "0")} ); } } print qq|

 

 

Grazie, la richiesta è stata modificata.

clicca qui se non riesci ad entrare automaticamente

|; } sub display_ticket { my ($id, $username, $category, $status) = @_; my $ticket = qq|
$otag
$id
$ctag		 $otag $username $ctag$otag $subject $ctag $otag $category $ctag$otag
$status
$ctag		$otag $count $period $ctag $awt
|; return $ticket; } # Aggiorna i progetti in base ai ticket sub aggiornadb() { my ($callid) = @_; $qr = "SELECT project_ID FROM project_ass WHERE ID = '".$callid."'"; $sth = $dbh->prepare($qr) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $ref = $sth->fetchrow_hashref(); $project_ID = $ref->{'project_ID'}; if (($project_ID ne "") && ($project_ID ne "0")) { $qr = "SELECT username, subject FROM calls WHERE id = '".$callid."'"; $sth = $dbh->prepare($qr) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $ref = $sth->fetchrow_hashref(); $nome_cliente = $ref->{'username'}; $oggetto_ticket = $ref->{'subject'}; $dbh2 = DBI->connect("DBI:mysql:$dbname2:$dbhost2","$dbuser2","$dbpass2") or script_error("Could not connect to the project database"); $qr = "SELECT kategorie, name, chef FROM projekte WHERE ID = '".$project_ID."'"; $sth2 = $dbh2->prepare($qr) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth2->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $dbh2->disconnect(); my $ref = $sth2->fetchrow_hashref(); $categoria = $ref->{'kategorie'}; # categoria del progetto. $nome_progetto = $ref->{'name'}; # nome del progetto. $responsabile = $ref->{'chef'}; # responsabile del progetto. # Aggiorna lo stato del progetto solo se il progetto # non è finito! if ($categoria != 4) { $qr = "SELECT COUNT(*) AS totale FROM project_ass AS p, calls AS r WHERE p.project_ID = '". $project_ID ."' AND p.ID = r.ID AND (r.category = 'Area Tecnica' OR r.category = 'Area Design' OR r.category = 'Area WebPromotion') AND r.status='OPEN'"; $sth = $dbh->prepare($qr) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $ref = $sth->fetchrow_hashref(); $totale = $ref->{'totale'}; # ticket aperti per progetto. $dbh2 = DBI->connect("DBI:mysql:$dbname2:$dbhost2","$dbuser2","$dbpass2") or script_error("Could not connect to the project database"); # Si tratta di un 'Contratto' o di un 'Progetto'? $sth2 = $dbh2->prepare("SELECT COUNT(*) AS cont FROM contratti WHERE projekte_ID = '".$project_ID."'") or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth2->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $ref2 = $sth2->fetchrow_hashref(); if ($ref2->{'cont'}>0) { # Contratto: $new_cat = 1 } else { $new_cat = 2 } if ($totale > 0) { $qr2 = "UPDATE projekte SET kategorie='3' WHERE ID = '".$project_ID."'"; } else { $qr2 = "UPDATE projekte SET kategorie='".$new_cat."' WHERE ID = '".$project_ID."'"; } $sth2 = $dbh2->prepare($qr2) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth2->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $dbh2->disconnect(); } $dbh2 = DBI->connect("DBI:mysql:$dbname2:$dbhost2","$dbuser2","$dbpass2") or script_error("Could not connect to the project database"); $qr = "SELECT email FROM users WHERE kurz = '".$responsabile."'"; $sth2 = $dbh2->prepare($qr) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth2->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; $dbh2->disconnect(); # $email = "a.pagnozzi\@neikos.it"; my $ref = $sth2->fetchrow_hashref(); $email = $ref->{'email'}; if ($email ne "") { open(MAIL, "|$global{'sendmail'} -t") || print "Unable to send mail: $!"; print MAIL "To: $email\n"; print MAIL "From: $global{'adminemail'}\n"; print MAIL "Subject: Modifiche TICKET $nome_cliente ($oggetto_ticket)\n\n"; print MAIL "C'\è una nuova modifica al ticket $callid associato \nal progetto $nome_progetto:\n"; print MAIL "---------------------------------------------------------------------------------------\n"; print MAIL "Ticket: http://agenda.neikos.it/cgi-bin/desk/staff.cgi?do=ticket&cid=$callid\n"; print MAIL "Progetto: http://agenda.neikos.it/index.php?module=projects&mode=forms&ID=$project_ID\n"; print MAIL "---------------------------------------------------------------------------------------\n"; print MAIL "\n\nGrazie."; close(MAIL); } } return 1; } sub aggiorna_tempo() { my ($callid) = @_; $current_time = time(); $qr = 'UPDATE calls SET track = "' . $current_time. '" WHERE id = "' . "$callid" . '"'; $sth = $dbh->prepare($qr) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; } # fine aggiorna # by Ago - 08/03/2007 sub get_nome_progetto() { my ($callid) = @_; $qr = "SELECT project_ID FROM project_ass WHERE ID = '".$callid."'"; my $sth = $dbh->prepare($qr) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $ref = $sth->fetchrow_hashref(); $project_ID = $ref->{'project_ID'}; if ($project_ID > 0) { $dbh2 = DBI->connect("DBI:mysql:$dbname2:$dbhost2","$dbuser2","$dbpass2") or script_error("Could not connect to the project database"); my $sth2 = $dbh2->prepare("SELECT name FROM `projekte` WHERE ID='" . $project_ID . "'") or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth2->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $ref2 = $sth2->fetchrow_hashref(); $nome_progetto = $ref2->{'name'}; } else { $nome_progetto = ""; } return $nome_progetto; } # by Ago - 15/09/2011 sub get_url_progetto() { my ($callid) = @_; $qr = "SELECT project_ID FROM project_ass WHERE ID = '".$callid."'"; my $sth = $dbh->prepare($qr) or die print "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped"; my $ref = $sth->fetchrow_hashref(); $project_ID = $ref->{'project_ID'}; $url_progetto = ""; if ($project_ID > 0) { $url_progetto = "/cgi-bin/desk/staff.cgi?do=sresults&progetto_id=" . $project_ID . "&pae=10&operatore=AND&closed=1"; } return $url_progetto; } # fine by Ago. 1;