#!/usr/bin/perl
use CGI qw(:standard);
use warnings;
use strict;
use DBI;
use DBD::mysql;
#Configurable settings:
my $dbuser = 'user';
my $dbpass = 'pass';
my $dbhost = 'localhost';
#End config
#Database setup
my $dsn = "dbi:mysql:webdb:$dbhost:3306";
my $dbh = DBI->connect($dsn, $dbuser, $dbpass)
or die "Can't connect to the DB: $DBI::errstr\n";
my $sth;
#Get day of week and date
(undef,undef,undef,my $dayofmonth,my $month,my $year,my $dayofweek,undef,undef) = localtime(time); $year += 1900; $month++;
my $date = "$dayofmonth-$month-$year";
#This converts current day to database format
my %days = (
"1" => "Mon",
"2" => "Tue",
"3" => "Wed",
"4" => "Thu",
"5" => "Fri",
"6" => "Sat",
"7" => "Sun",
);
my $dbday = $days{$dayofweek};
#Index of subroutine pages
my %pages = (
'Login To Sheet' => \&newsheet, #Action to perform if no current sheet is detected
'Go To Sheet' => \&loadsheet, #Action to perform if a current sheet was detected
'Proceed To Sheet' => \&loadsheet, #Loads the daily schedule
);
#Figur out which page to load
my $page = param(".Page");
if ($pages{$page}) {
$pages{$page}->();
}
else {
login();
}
#Generate html header and page name USAGE: print htmlhead("Page Name");
sub htmlhead {
return header(), start_html(shift);
}
#Login page
sub login {
$sth = $dbh->prepare("select Date from Sheets where Current = 1");
$sth->execute;
my @currentsheets;
while (my $a = $sth->fetchrow_array()) {
push(@currentsheets, $a);
}
if (@currentsheets) {
print htmlhead("Login Page"),
h1("Sheet Login - Current Sheet Already Available"),
h3("Select from list:"),
start_form(),
popup_menu(-NAME => ".SheetDate", -VALUES => \@currentsheets),
submit(-NAME => ".Page", -VALUE => "Go To Sheet"),
end_form(),
end_html;
}
else {
my %teammem;
$sth = $dbh->prepare("select Fname,Lname,TeamID from Team");
$sth->execute;
while (my @a = $sth->fetchrow_array()) {
$teammem{"$a[2]"} = "$a[0] $a[1]";
}
print htmlhead("Login Page"),
h1("Sheet Login $dbday"),
start_form(),
p("Enter TeamID:", popup_menu(-NAME => ".TeamID", -VALUES => \%teammem)),
submit(-NAME => ".Page", -VALUE => "Login To Sheet"),
end_form(),
end_html;
}
}
sub newsheet {
my $teamid = param(".TeamID");
$sth = $dbh->prepare("INSERT into Jobstatus (Date,JobName) SELECT CURDATE(),JobName FROM Jobs WHERE $dbday = 1");
$sth->execute;
$sth = $dbh->prepare("INSERT INTO Sheets (Date,Current,Locked,TeamID) VALUES(CURDATE(),1,1,$teamid)");
$sth->execute;
print htmlhead("test"),
h3("User $teamid created new sheet for $date($dbday)"),
hidden(-NAME => ".SheetDate", -DEFAULT => "$date"),
submit(-NAME => ".Page", -VALUE=> "Proceed To Sheet"),
end_html;
}
sub out {
print header(), start_html("output"),
h1("Output", param(".TeamID"));
}
sub loadsheet {
my $loadsheet = param(".SheetDate");
$sth = $dbh->prepare("select Jobs.JobName,Jobs.JobDesc,Jobs.JobHelp From Jobs,Jobstatus where Jobstatus.Date = DATE('$loadsheet') AND Jobs.JobName = Jobstatus.JobName");
$sth->execute;
my @tbldata;
while (my $a = $sth->fetchrow_hashref()) {
push(@tbldata, $a);
}
print htmlhead("Sheet");
foreach my $hash (@tbldata) {
print p(
#foreach ( keys %$hash ) {
# print h1("$_ $$hash{$_}");
#}
}
end_html;
}
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
by: FishMongerPosted on 2009-09-18 at 05:20:56ID: 25365088
I have not used it myself, but it looks like this module should do what you need.
HTMLTable - Create HTML table from DBI query output.
http://search.cpan.org/~rk