print "Content-type: text/html\n\n";

my $dbh=DBI->connect('DBI:Oracle:METAD1', 'ei_arf_webform_select', 'xxxx') or die "Cannot connect to DB: " . DBI->errstr;
     my $sql = "SELECT org_name FROM mv_org_structure_list WHERE ORG_TYPE='D'";
     my $sth = $dbh->prepare($sql) || print "Cannot prepare";
     $sth->execute              || print "Cannot execute";
     my @result;
     my @dept;
     while (@result = $sth->fetchrow_array()) {
	push(@dept, @result);
      }

 print "<select name='dept'>";      
 foreach my $dept(@dept) {
     print "<option>$dept</option>";
     }
 print "</select>";

##</code><code>##

#!/bin/perl
#use strict;
use CGI;
use DBI;
use Mail::Sendmail;
use File::Basename;
use HTML::Template;

print "Content-type: text/html\n\n";

my $userid = getUserID();				# get user id
my $from = getEmail($userid);				# get email address

#-----------------
# set email header
#-----------------

my $smtp = 'xxxxx';	# stmp server name

my $to='bryson.connolly@ottawa.ca';			
my $cc='bryson.connolly@ottawa.ca';		

my $subject_eds = 'EDS Action';
my $subject_telecom = 'Telecom';

my $mailbox = 'no';
my $existing_emp = 'no'; 
my $assignedphone = 'no';
my $voicemail = 'no';
my $acd_group = 'no';

my $path = getFormPath();			        # get the path that holds the form template
my $FORM_TMPL_FILE = "$path/form.tmpl";			# set form file
my $DISPLAY_TMPL_FILE = "$path/display.tmpl";		# set the result file
my $RESULTS_TMPL_FILE = "$path/body.tmpl";		# set the result file

if (!-e "$RESULTS_TMPL_FILE") {
   print "Error: $RESULTS_TMPL_FILE not found.";
   exit;
}

# set the form parameters
%formparams = 
	(
		LOGO 	=> "/images/logobw.gif",
		TITLE	=> "ARF Exempt Request (Network and/or E-Mail Account, Voicemail, Telephone Re-assignment)",
		PATH 	=> $ENV{SCRIPT_NAME},

	);
	
# create the form for the first time or submit the form for the second time
my $query = new CGI;
if ( !($query->param) ) 
{
	# create the form using the form template 
	$formTemplate = new HTML::Template( filename => $FORM_TMPL_FILE );
	
	for my $key ( keys %formparams ) 
	{
		$formTemplate->param($key => $formparams{"$key"});
	}

	createForm($formTemplate); 
}
else 
{ 
	# submit and process the form (including send email)
	processForm(); 
}

exit;

#===================================================
#
# retrive user id of the person submitting the form
#
#===================================================
sub getUserID
{
	my  $userid=lc($ENV{'LOGON_USER'});
	if (!$userid){$userid=lc($ENV{'REMOTE_USER'}) ;}

	# remove the domain name (city or rmoc) from the user id
	$userid=~ s|city\W||g;

	if(!$userid)
	{
		print "Your are not on the intranet of City of Ottawa!<br>";
		exit;
	}
	return $userid;
}


#===============================================================
#
# retrive user email address of the person submitting the form 
#
#===============================================================
sub getEmail
{
	my ($userid) = @_;
	# get base directory and employee information file path
	my ($BASEDIR) = dirname($0 );
	my $DATAFILE="$BASEDIR/../employee.dat";

	# get employee information
	open (INFILE, "$DATAFILE");
		my @empinfo=grep /$userid\b/i, <INFILE>;

	close INFILE;

	# get the user email address
	my @empinfoarray=split ("\t", $empinfo[0]);
	my $from = $empinfoarray[3];

	if(!$from)
	{
		print "You don't have an email account of City of Ottawa!<br>\n";
		exit;
	}
	# print $from; #Used for debug purposes
	return $from;
}


#==================================================
#
# Location of template directory and template name
#
#==================================================
sub getFormPath
{
	# get the script path
	my $path = dirname($ENV{PATH_TRANSLATED});
	chomp ($path);

	# get the script file name without extension
	($script_name, $dir, $type) = fileparse($ENV{'SCRIPT_NAME'}, "\.pl");
	chomp ($script_name);

	# get the form path
	$path =  $path . "/" . $script_name;
}


#=====================================
#
# create new form from html template
#
#=====================================
sub createForm
{
	my ($formTemplate) = @_;

	print $formTemplate->output;

	return;
}


#=====================================
#
# process submit request
#
#=====================================
sub processForm
{
	my $body;

	$body = getbody();
	
	# subject = "EDS Action"
	&sendMail ($smtp, $to, $cc, $from, $subject_eds, $body);
	
	sleep 3;

	# subject = "Telecom"
	if($subject_telecom eq 'Telecom')
	{
		&sendMail ($smtp, $to, $cc, $from, $subject_telecom, $body);
	}

	print "<br>";
	print "Thank you! <br/>";
	print "<br>";
	print "Your request has been submitted.<br><br>";
	# Used to create a link to close the form
	print "<a href=\"javascript:window.close();\">Close Window</a>\n";	
	return;
}


#=====================================
#
# get email body
#
#	set for specific body requirement
#=====================================
sub getbody()
{
	# get params;
	my $params = getdata();
	my $resulttemplate = new HTML::Template( filename => $RESULTS_TMPL_FILE );
	my $displaytemplate = new HTML::Template( filename => $DISPLAY_TMPL_FILE );
	

	$resulttemplate->param(TITLE => $formparams{"TITLE"});
	$resulttemplate->param(FIRSTNAME => $params->{'firstname'});
	$resulttemplate->param(LASTNAME => $params->{'lastname'});
	$resulttemplate->param(MIDDLE => $params->{'minit'});
	$resulttemplate->param(TTITLE => $params->{'ttitle'});
	$resulttemplate->param(SUPERVISOR => $params->{'autho'});
	$resulttemplate->param(DEPARTMENT => $params->{'dept'});
	$resulttemplate->param(BRANCH => $params->{'branch'});
	$resulttemplate->param(DIVISION => $params->{'division'});
	$resulttemplate->param(UNIT => $params->{'unit'});
	$resulttemplate->param(SECTION => $params->{'section'});
	$resulttemplate->param(GROUP => $params->{'group'});
	
	$mystartdate = $params->{'s_months'} . " " . $params->{'s_days'} . ", " .  $params->{'s_years'};
	$resulttemplate->param(STARTDATE => $mystartdate);
	
	$resulttemplate->param(NEWMAILBOX => $params->{'r_newmailbox'});
	$mailbox = $params->{'r_newmailbox'};

	$resulttemplate->param(EXISTEMP => $params->{'r_exist_emp'});
        $existing_emp = $params->{'r_exist_emp'};                      # sends telecom an email if yes
	$resulttemplate->param(OLDX => $params->{'txtoldx'});

	$resulttemplate->param(ASSIGNED => $params->{'r_assigned'});  
	$assignedphone = $params->{'r_assigned'};		       # sends telecom an email if yes
	$resulttemplate->param(REASSIGN => $params->{'txtreassign'});	
	
	$resulttemplate->param(NEWPHONE => $params->{'r_newphone'});  
	$voicemail = $params->{'r_newphone'};			       # sends telecom an email if yes
	$resulttemplate->param(ACDGROUP => $params->{'r_group'}); 
   	  $acd_group = $params->{'r_group'}; 				     # sends telecom an email if yes
	$resulttemplate->param(TXTGROUP => $params->{'txtgroup'});
        $resulttemplate->param(ACCESSNUM => $params->{'txtaccess_num'});  
	$resulttemplate->param(REMAINGROUP => $params->{'r_remain_group'}); 
	$resulttemplate->param(REQUIRECODE => $params->{'r_new_code'}); 
	$resulttemplate->param(NEWCODE => $params->{'txtnew_code'}); 

        # Don't send email with subject "Telecom"
	if (($existing_emp eq "no") 
        and ($assignedphone eq "no")
        and ($voicemail eq "no")
        and ($acd_group eq "no"))   { $subject_telecom = ""; }
	##########################################################
	
	$resulttemplate->param(PERMISSION => $params->{'txtPermission'});
	$resulttemplate->param(LOCATION => $params->{'txtlocation'});

	my $myr_status = $params->{'r_status'};
	my $mytype;
	my $myexpirydate;
	
	if($myr_status eq "full") {
         $mytype = "Employee - Paid by SAP";
	     } else {
	     $mytype = "ARF Exempt type: " .  $params->{'s_Type'};
	     $myexpirydate = "Account Expiry Date: ";
	     $myexpirydate = $myexpirydate . $params->{'s_month'} . " " . $params->{'s_day'} . ", " .  $params->{'s_year'};
	     }
	$resulttemplate->param(USERTYPE => $mytype);
	$resulttemplate->param(EXPIRYDATE => $myexpirydate);
	$resulttemplate->param(COMMENTS => $params->{'txtcomment'});
	
	my $emailtitle = "ARF Exempt Request (";
	
	$emailtitle = $emailtitle . "Network";
	
	if ($mailbox eq 'yes') { $emailtitle = $emailtitle . " and/or E-Mail Account";	}
	if ($voicemail eq 'yes') { $emailtitle = $emailtitle . ", Voicemail"; }
	if ($assignedphone eq 'yes') { $emailtitle = $emailtitle . ", Telephone Re-assignment";	}
	
	$emailtitle = $emailtitle . ")";

	$resulttemplate->param(TITLE => $emailtitle);
	
	# To return a list of parameters in the template of $displaytemplate (keys)
	my @parameter_names = $displaytemplate->param(); 
	
	foreach my $name (@parameter_names)
	{
		# Assign the value of $resulttemplate to the key of $displaytemplate
		# Since the keys have the same name in both templates.
		$displaytemplate->param($name => $resulttemplate->param($name));
	}

	$body = $resulttemplate->output;
	if ($body)
	{
		print $displaytemplate->output; 	# Print out form data to screen
		return $body;                  		# Returns form data to be sent by email
	}
	else
	{
		print "Error: Cannot send email body to send<br>";
		exit;
	}
	

	
}


#=======================================
#
# Get CGI data from the form submitting
#
#=======================================
sub getdata()
{
	# get params;
	my %params;
	my $query = new CGI;
	foreach my $key ($query->param)
	{
		$params{$key} = $query->param($key);
	}
	return \%params;
}


#=====================================
#
# send email
#
#=====================================
sub sendMail
{
	my %mail;
	my ($smtp, $to, $cc, $from, $subject, $body) = @_;
	%mail=(
		SMTP    => $smtp,
		To      => $to,
		Cc      => $cc,
		From    => $from,
		subject => "$subject",
		'Content-type'=>'text/plain',
		Message => "$body\n"
		);
  	if (!sendmail(%mail) )	
  	{	
		print "$Mail::Sendmail::error;";
		exit;
  }

	return;
}