if ($section eq "submit_req")
{
undef $error;
if ($q->param('authoritylist') eq 'Select Authority') {
$error = "
Please select an Authority
";
}
if(($error)){
$errorString = qq|The following fields were missing or invalid:
$error
| ;
$errorSection = qq|
$errorString
|;
$template{'error'} = $errorSection ;
section('next_submit1');
exit;
}
undef $error;
# die_nice("I have reached here ");
if (get_setting_2("access_submit")){
check_user();
}
else { $ignorenav = 1; }
my $subject = $q->param('subject');
my $description = $q->param('description');
my $username = $q->param('username');
my $priority = $q->param('priority');
my $category = $q->param('category');
my $subcategory = $q->param('subcategory');
my $file = $q->param('file');
my $cfile = $q->param('file');
my $email = $q->param('email');
my $cc = $q->param('cc_email');
my $extn = $q->param('extn');
my $reqForApproval = $q->param('request_for_approval');
my $projectIn = $q->param('project');
#foreach(qw/1 extn/) { $error .= qq|
$_
| if $q->param($_) eq ""; }
my $sql = "select description from perlDesk_sub_categories where sub_cat_id=$subcategory";
my $st = $dbh->prepare($sql);
my $rt = $st->execute();
while(my $ref = $st->fetchrow_hashref()){
$subject =$ref->{'description'};
}
if ($q-param('email') ne "" && $q->param('email') !~ /\./ && $q->param('email') !~ /\@/){
$error .= "
Invalid email address
";
}
if (defined $error) { ## if 1
if (get_setting_2("access_submit")){
check_user();
}
else { $ignorenav = 1; }
print "Content-type: text/html\n\n";
my $statement = 'SELECT * FROM perlDesk_ticket_fields ORDER BY dorder';
my $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped";
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";
while(my $ref = $sth->fetchrow_hashref()){
$value = $q->param($ref->{'id'});
$template{'form'} .= qq|
$ref->{'name'}
|;
}
$sth->finish;
my $statement = 'SELECT level FROM perlDesk_departments ORDER BY level';
my $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped";
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";
while(my $ref = $sth->fetchrow_hashref()){
$template{'category'} .= "";
}
$sth->finish;
$template{'uname'} = $Cookies{'id'} || "Unregistered";
$template{'email'} = $q->param('email');
$template{'subject'} = $q->param('subject');
$template{'description'} = $q->param('desc');
$template{'error'} = qq|The following fields were missing or invalid:
$error
|;
parse("$global{'data'}/include/tpl/submitreq");
}## end if 1
else {
#~~
# No Error: Continue Processing Submission
#~~
$current_time = time();
if ($username ne "Unregistered"){
$statement = qq|SELECT lcall FROM perlDesk_users WHERE username = "$Cookies{'id'}"|;
$sth = $dbh->prepare($statement)or die "Couldn't prepare statement: $DBI::errstr; stopped";
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";
while(my $ref = $sth->fetchrow_hashref()){
my $newtime = $ref->{'lcall'};
$newtime = $newtime + $global{'floodwait'};
die_nice("You can only log a call every $global{'floodwait'} seconds") if $newtime > $current_time;
}
}
my $edescription = $description;
$description =~ s//>/g;
## If this request is to be sent for approval, store username:email in ownership column and insert that of the approver
## in the call. Change status to 'APPROVE' and send mail to the approver and the user only.
my $reqStatus = "OPEN";
my $reqOwnerShip = "Unowned";
my $reqUserName = $username;
my $reqEmail = $email;
if($reqForApproval) {
$reqStatus = "APPROVE";
$reqOwnerShip = qq|$username:$email|;
$userIdToApprove = $q->param('authoritylist');
$statement = qq|SELECT username,email FROM perlDesk_users WHERE id = $userIdToApprove|;
$sth = $dbh->prepare($statement)or die "Couldn't prepare statement: $DBI::errstr; stopped";
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";
while(my $ref = $sth->fetchrow_hashref()){
$reqUserName = $ref->{'username'};
$reqEmail = $ref->{'email'};
}
$sth->finish;
}
my @chars = (A..Z,0..9,a..z);
$key = $chars[rand(@chars)] . $chars[rand(@chars)] . $chars[rand(@chars)] . $chars[rand(@chars)] . $chars[rand(@chars)] . $chars[rand(@chars)] . $chars[rand(@chars)] . $chars[rand(@chars)] . $chars[rand(@chars)] . $chars[rand(@chars)];
# added by kamesh to fix bug-1
#my $stat3 = qq;
#my $sth1 = $dbh->prepare($stat3) or die "Couldn't prepare statement: $DBI::errstr; stopped";
#my $description1 = $sth1->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";
#problem is here
my $stat1 = q/INSERT INTO perlDesk_calls VALUES ( ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)/;
my $dsth = $dbh->prepare($stat1) or die "Couldn't prepare statement: $DBI::errstr; stopped";
$dsth->execute("NULL",$reqStatus, $reqUserName, $reqEmail, $priority, $category, $subcategory, $subject, $description, $hdtime, $reqOwnerShip,"Unowned", "hd", $current_time, $current_time, "1", "0", "0", "0", $key) or die "Couldn't execute statement: $DBI::errstr; stopped";;
$callid = $dbh->{'mysql_insertid'};
my $sdsth = $dbh->prepare( "INSERT INTO perlDesk_activitylog VALUES ( ?, ?, ?, ?, ?)" );
$sdsth->execute( "NULL", $callid, $hdtime, "User", "Request Logged" ) or die "Couldn't execute statement: $DBI::errstr; stopped";
## Insert into perlDesk_users table - extn.
$statement = qq|UPDATE perlDesk_users set ext=$extn, project_in="$projectIn" WHERE username = "$Cookies{'id'}"|;
$sth = $dbh->prepare($statement)or die "Couldn't prepare statement: $DBI::errstr; stopped";
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";
$sth->finish;
## Insert into perlDesk_user_response table.
my $stmtQsByCatId = $dbh->prepare("select sub_cat_qs_id from perlDesk_sub_cat_questions where sub_cat_id=?");
$stmtQsByCatId->execute($subcategory) or die "Couldn't execute statement: $DBI::errstr; stopped";
while(my $ref = $stmtQsByCatId->fetchrow_hashref()){
my $nQsId = $ref->{'sub_cat_qs_id'};
# die_nice($nQsId);
my $strAns = $q->param($nQsId);
# die_nice($strAns);
my $statm = qq|INSERT INTO perlDesk_user_response VALUES (?,?,?)|;
my $stmtInsert = $dbh->prepare($statm)or die "Couldn't prepare statement: $DBI::errstr; stopped";
$stmtInsert->execute($callid,$nQsId,$strAns) or die "Couldn't execute statement: $DBI::errstr; stopped";
}
## Check for File
if (defined $file && $file ne ""){
my $path2 = get_setting_2(qq|file_path|);
my $file_name = getFileNameFromFilePath($file);
# Copy file from the path2/temp folder to the path2 folder
#system ('cp $path2/temp/$file_name $path2/') or print STDERR "couldn't exec mv folder: $!";
if(-e (qq|$path2/$file_name|)) {
execute_sql(qq|INSERT INTO perlDesk_files VALUES ("", "$callid", "$callid", "$file_name", "$path2/$file_name")|);
}
}
$lby = $Cookies{'id'} || "Unregistered User";
my $statement = 'SELECT * FROM perlDesk_ticket_fields ORDER BY dorder';
my $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped";
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";
while(my $ref = $sth->fetchrow_hashref()){
my $fid = $ref->{'id'};
my $sth = $dbh->prepare( "INSERT INTO perlDesk_call_fields VALUES ( ?, ?, ?, ? )" ) or die $DBI->errstr;
$sth->execute( "NULL", $callid, "$fid", "$q->param($fid)" ) or die $DBI->errstr;
}
$sth->finish;
$dbh->do(qq|UPDATE perlDesk_users SET lcall = "$current_time" WHERE username = "$Cookies{'id'}"|);
my $email1= "";
if($reqForApproval){
# The followin two lines changed as per the database.
# $body =~ s/\{SUBMITEDBY\}/$username/g;
# $body =~ s/\{PROBLEM\}/$subject/;
if ($enablemail) {
my $sth = $dbh->prepare("SELECT name FROM perlDesk_users WHERE username = ?") or die "Couldn't prepare statement: $DBI::errstr; stopped";
$sth->execute($username) or die "Couldn't execute statement: $DBI::errstr; stopped";
my $name = ($sth->fetchrow_array())[0];
$sth->execute($reqUserName) or die "Couldn't execute statement: $DBI::errstr; stopped";
my $approvername = ($sth->fetchrow_array())[0];
$sth->finish;
my $to = $reqEmail;
my $from = $email;
my $esubject = "Request for Authorization: Ticket Id #".$callid.": ".$category.": ".$subject;
my $url = qq|$global{'baseurl'}/staff.cgi|;
my $body = get_setting_2(qq|approval_mail_msg|);
$body =~ s/\{TIME\}/$hdtime/;
$body =~ s/\{USERNAME\}/ $name/g;
$body =~ s/\{APPROVERNAME\}/$approvername/g;
$body =~ s/\{PROJECT\}/ $projectIn/;
$body =~ s/\{EXTN\}/ $extn/;
$body =~ s/\{CATEGORY\}/$category/;
$body =~ s/\{SUBJECT\}/$subject/;
$body =~ s/\{TICKETID\}/$callid/g;
my $url = qq|URL: $global{'baseurl'}/pdesk.cgi|;
$body =~ s/\{URL\}/$url/;
email ( To => "$to", From => "$from", Subject => "$esubject", Body => "$body" , Cc => "$cc");
}
}
else {
$statement = 'SELECT * FROM perlDesk_staff WHERE access LIKE "%' . "$category" . '::%" OR access LIKE "%GLOB::%" OR access = "admin";';
$sth = $dbh->prepare($statement)or die "Couldn't prepare statement: $DBI::errstr; stopped";
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";
while(my $ref = $sth->fetchrow_hashref()) {
$email1 .= $ref->{'email'};
if ($ref->{'notify'} == "1") {
if ($enablemail) {
my $to = $ref->{'email'};
my $from = $global{'adminemail'};
my $esubject = "New HD Request: ".$category;
my $url = qq|$global{'baseurl'}/staff.cgi|;
my $msg = qq|
There is a new help desk submission
Ticket Details
------------------------------------------
Ticket ID.............: $callid
Logged by.............: $lby
Category..............: $category
Subject...............: $subject
$edescription
------------------------------------------
URL: $url
Thank You
|;
email ( To => "$to", From => "$from", Subject => "$esubject", Body => "$msg" , Cc => "$m_cc");
$m_cc = "";
} ## if enable mail
} ## if notify
} # end while
}# end else
$sth->finish;
close (MAILNEWTPL);
#Added by kamesh to fix bug-12
if($reqForApproval){
$content = qq|
