Mercurial > ~darius > hgwebdir.cgi > SCS_DB
changeset 1:d95e74cd12f4 RELENG_1_0
Initial commit
| author | darius |
|---|---|
| date | Wed, 06 May 1998 14:33:31 +0000 |
| parents | 019f8230ae37 |
| children | 791e87929f83 |
| files | adduser.html adduser.pl edit.html edit.pl mailout.pl members.txt query.html query.pl schema.sql test_data.sql |
| diffstat | 10 files changed, 704 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/adduser.html Wed May 06 14:33:31 1998 +0000 @@ -0,0 +1,55 @@ +<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN"> +<HTML> + <HEAD> + <TITLE>Add a user to the SCS Member Database</TITLE> + </HEAD> + + <BODY BGCOLOR="#00000" TEXT="#CCCCCC" LINK="#00EE20" VLINK="#55FF8B" ALINK="#FFFF00"> + <H1>Add a user to the SCS Member Database</H1> + <FORM METHOD=GET ACTION="/cgi-bin/scs/adduser.pl"> + <TABLE WIDTH="100%"> + <TR><TD ALIGN=RIGHT>First Name<TD><INPUT TYPE=TEXT + NAME=fname SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Last Name<TD><INPUT TYPE=TEXT + NAME=lname SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Nick Name<TD><INPUT TYPE=TEXT + NAME=nick SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Password<TD><INPUT TYPE=TEXT + NAME=pwd1 SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Password Again<TD><INPUT TYPE=TEXT + NAME=pwd2 SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Age<TD><INPUT TYPE=TEXT + NAME=age SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Phone 1<TD><INPUT TYPE=TEXT + NAME=phone1 SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Public Number<TD><INPUT TYPE=CHECKBOX + NAME=ph1_pub SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Phone 2<TD><INPUT TYPE=TEXT + NAME=phone2 SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Public Number<TD><INPUT TYPE=CHECKBOX + NAME=ph2_pub SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Email<TD><INPUT TYPE=TEXT + NAME=email SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Public Email<TD><INPUT TYPE=CHECKBOX + NAME=email_pub SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Address Line 1<TD><INPUT TYPE=TEXT + NAME=addy1 SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Address Line 2<TD><INPUT TYPE=TEXT + NAME=addy2 SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Address Line 3<TD><INPUT TYPE=TEXT + NAME=addy3 SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Public Address<TD><INPUT TYPE=CHECKBOX + NAME=addy_pub SIZE="50%"></TR> + <TR><TD ALIGN=RIGHT>Comments<TD><INPUT TYPE=TEXT + NAME=comments SIZE="50%"></TR> + </TABLE> + <INPUT TYPE=SUBMIT VALUE="Add User"> + + <HR> + <ADDRESS><A HREF="mailto:darius@holly.dons.net.au">Daniel J. O'Connor</A></ADDRESS> +<!-- Created: Wed Apr 29 20:52:29 CST 1998 --> +<!-- hhmts start --> +Last modified: Wed May 6 22:43:45 CST +<!-- hhmts end --> + </BODY> +</HTML>
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/adduser.pl Wed May 06 14:33:31 1998 +0000 @@ -0,0 +1,74 @@ +#!/usr/local/bin/perl + +require "cgi-lib.pl"; +use DBI; + +$user = ""; +$passwd = ""; +$dbname = "scs"; + +MAIN: +{ +# Read in all the variables set by the form + &ReadParse(*input); + + print &PrintHeader; + print "<BODY BGCOLOR=\"#00000\" TEXT=\"#CCCCCC\" LINK=\"#00EE20\""; + print " VLINK=\"#55FF8B\" ALINK=\"#FFFF00\">"; + print "<META HTTP-EQUIV=\"Pragma\" content=\"no-cache\">\n"; + print "<TITLE>Add a user to the SCS Database</TITLE>"; + print "<H2>Add a user to the SCS Database</H2>"; + +# Connect to the Database + $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $user, $passwd) || bad_exit($sth->errstr); + + $fname = san_str($input{'fname'}); + $lname = san_str($input{'lname'}); + $nick = san_str($input{'nick'}); + $pwd1 = san_str($input{'pwd1'}); + $pwd2 = san_str($input{'pwd2'}); + $age = san_num($input{'age'}); + $phone1 = san_str($input{'phone1'}); + $ph1_pub = (san_str($input{'ph1_pub'}) eq 'on') ? 't' : 'f'; + $phone2 = san_str($input{'phone2'}); + $ph2_pub = (san_str($input{'ph2_pub'}) eq 'on') ? 't' : 'f'; + $email = san_str($input{'email'}); + $email_pub = (san_str($input{'email_pub'}) eq 'on') ? 't' : 'f'; + $address1 = san_str($input{'addy1'}); + $address2 = san_str($input{'addy2'}); + $address3 = san_str($input{'addy3'}); + $addy_pub = (san_str($input{'addy_pub'}) eq 'on') ? 't' : 'f'; + $comments = san_str($input{'comments'}); +} + +sub bad_exit +{ + print "<H2>An internal error has occurred</H2><BR>"; + print "Please mail <A HREF=\"mailto:darius\@dons.net.au\">The Administrator</A> and\n"; + print "say the following error occured - $_[0]<P>\n"; + print "<A HREF=\"/scs/games/adduser.html\">Back to the Add User Page</A>\n"; + + print &HtmlBot; + + exit(0); +} + +sub dtrail +{ + $_[0] =~ s/(\ *)$//g; + return $_[0]; +} + +sub san_str +{ + $_[0] =~ s/\\/\\\\/g; + $_[0] =~ s/'/\\'/g; + $_[0] =~ s/"/\\"/g; + return $_[0]; +} + +sub san_num +{ +# $_[0] =~ s/'/\\'/g; + return $_[0]; +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/edit.html Wed May 06 14:33:31 1998 +0000 @@ -0,0 +1,29 @@ +<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN"> +<HTML> + <HEAD> + <TITLE>Edit the SCS Member Database</TITLE> + </HEAD> + + <BODY BGCOLOR="#00000" TEXT="#CCCCCC" LINK="#00EE20" VLINK="#55FF8B" ALINK="#FFFF00"> + <H1>Edit the SCS Member Database</H1> + <FORM METHOD=GET ACTION="/cgi-bin/scs/edit.pl"> + <TABLE WIDTH="100%"> + <TR> + <TD ALIGN=RIGHT>SCS Membership number<TD> + <INPUT TYPE="text" NAME="id" VALUE="" size="50%"></TR> + <TR><TD ALIGN=RIGHT>Password<TD> + <INPUT TYPE="password" NAME="passwd" VALUE=""></TR> + </TABLE> + <INPUT TYPE=HIDDEN NAME="type" VALUE="edit"> + + <INPUT TYPE=SUBMIT VALUE="Do Edit"> + <HR> + Back to the <A HREF="/scs/">SCS page</A> + <HR> + <ADDRESS><A HREF="mailto:darius@dons.net.au">Daniel J. O'Connor</A></ADDRESS> +<!-- Created: Wed Apr 29 20:29:35 CST 1998 --> +<!-- hhmts start --> +Last modified: Wed May 6 17:01:11 CST +<!-- hhmts end --> + </BODY> +</HTML>
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/edit.pl Wed May 06 14:33:31 1998 +0000 @@ -0,0 +1,242 @@ +#!/usr/local/bin/perl + +require "cgi-lib.pl"; +use DBI; + +$user = ""; +$passwd = ""; +$dbname = "scs"; + +MAIN: +{ +# Read in all the variables set by the form + &ReadParse(*input); + + print &PrintHeader; + print "<BODY BGCOLOR=\"#00000\" TEXT=\"#CCCCCC\" LINK=\"#00EE20\" VLINK=\"#55FF8B\" ALINK=\"#FFFF00\">"; + print "<META HTTP-EQUIV=\"Pragma\" content=\"no-cache\">\n"; + print "<TITLE>Edit the SCS Database</TITLE>"; + print "<H2>Edit the SCS Database</H2>"; + +# print &HtmlTop ("Edit the SCS Database"); + + $id = $input{'id'}; + $type = $input{'type'}; + +# Is this a query? + if ($type eq "edit") { + +# Connect to the Database + $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $user, $passwd) || bad_exit($sth->errstr); + +# Prepare the select statement + $sth = $dbh->prepare("SELECT * FROM members WHERE memberid = $id") || bad_exit($sth->errstr); + +# Execute it + $numrows = $sth->execute || bad_exit($sth->errstr); + +# Get one row. Only one.. if there is more than one, bad things have happened :) + if (@array = $sth->fetchrow_array) { + +# Check their password + if (&dtrail(@array[4]) ne &dtrail($input{'passwd'})) { + print "Bad password for Member ID $id<P>\n"; + print "<A HREF=\"/scs/games/edit.html\">Try again</A>\n"; + } else { +# Print out a form which allows the user to change fields + $memberid = dtrail(@array[0]); + $firstname = dtrail(@array[1]); + $lastname = dtrail(@array[2]); + $nickname = dtrail(@array[3]); + $pin = dtrail(@array[4]); + $age = dtrail(@array[5]); + $phone1 = dtrail(@array[6]); + $ph1_pub = dtrail(@array[7]); + $phone2 = dtrail(@array[8]); + $ph2_pub = dtrail(@array[9]); + $email = dtrail(@array[10]); + $email_pub = dtrail(@array[11]); + $address1 = dtrail(@array[12]); + $address2 = dtrail(@array[13]); + $address3 = dtrail(@array[14]); + $addy_pub = dtrail(@array[15]); + $comments = dtrail(@array[16]); + $joined = dtrail(@array[17]); + $lstmemfee = dtrail(@array[18]); + $lstpddate = dtrail(@array[19]); + + print "<FORM METHOD=GET ACTION=\"/cgi-bin/scs/edit.pl\">\n"; + print "<TABLE WIDTH=\"100%\">\n"; + print "<TR><TD ALIGN=RIGHT>First Name<TD>"; + printf("<INPUT TYPE=TEXT NAME=fname VALUE=\"%s\" SIZE=\"50%\"></TR>\n", + $firstname); + print "<TR><TD ALIGN=RIGHT>Last Name<TD>"; + printf("<INPUT TYPE=TEXT NAME=lname VALUE=\"%s\" SIZE=\"50%\"></TR>\n", + $lastname); + print "<TR><TD ALIGN=RIGHT>Member ID<TD>$id"; + printf("<INPUT TYPE=HIDDEN NAME=id VALUE=\"%d\"></TR>\n", $id); + printf("<TR><TD ALIGN=RIGHT>Nickname<TD>"); + printf("<INPUT TYPE=TEXT NAME=nick VALUE=\"%s\" SIZE=\"50%\"></TR>\n", + $nickname); + print "<TR><TD ALIGN=RIGHT>Password<TD>"; + printf("<INPUT TYPE=PASSWORD NAME=pwd1 VALUE=\"%s\" SIZE=\"50%\">", $pin); + printf("<INPUT TYPE=HIDDEN NAME=passwd VALUE=\"%s\"</TR>\n", $pin); + print "<TR><TD ALIGN=RIGHT>And Again<TD>"; + printf("<INPUT TYPE=PASSWORD NAME=pwd2 VALUE=\"%s\" SIZE=\"50%\"></TR>\n", + $pin); + print "<TR><TD ALIGN=RIGHT>Age<TD>"; + printf("<INPUT TYPE=TEXT NAME=age VALUE=\"%d\" SIZE=\"50%\"></TR>\n", + $age); + print "<TR><TD ALIGN=RIGHT>Phone 1<TD>"; + printf("<INPUT TYPE=TEXT NAME=phone1 VALUE=\"%s\" SIZE=\"50%\"></TR>\n", + $phone1); + print "<TR><TD ALIGN=RIGHT>Public number<TD>"; + printf("<INPUT TYPE=CHECKBOX NAME=ph1_pub %s SIZE=\"50%\"></TR>\n", + (($ph1_pub eq "0") ? '' : 'CHECKED')); + print "<TR><TD ALIGN=RIGHT>Phone 2<TD>"; + printf("<INPUT TYPE=TEXT NAME=phone2 VALUE=\"%s\" SIZE=\"50%\"></TR>\n", + $phone2); + print "<TR><TD ALIGN=RIGHT>Public number<TD>"; + printf("<INPUT TYPE=CHECKBOX NAME=ph2_pub %s SIZE=\"50%\"></TR>\n", + (($ph2_pub eq "0") ? '' : 'CHECKED')); + print "<TR><TD ALIGN=RIGHT>Email<TD>"; + printf("<INPUT TYPE=TEXT NAME=email VALUE=\"%s\" SIZE=\"50%\"></TR>\n", + $email); + print "<TR><TD ALIGN=RIGHT>Public Email<TD>"; + printf("<INPUT TYPE=CHECKBOX NAME=email_pub %s SIZE=\"50%\"></TR>\n", + (($email_pub eq "0") ? '' : 'CHECKED')); + print "<TR><TD ALIGN=RIGHT>Address 1<TD>"; + printf("<INPUT TYPE=TEXT NAME=addy1 VALUE=\"%s\" SIZE=\"50%\"></TR>\n", + $address1); + print "<TR><TD ALIGN=RIGHT>Address 2<TD>"; + printf("<INPUT TYPE=TEXT NAME=addy2 VALUE=\"%s\" SIZE=\"50%\"></TR>\n", + $address2); + print "<TR><TD ALIGN=RIGHT>Address 3<TD>"; + printf("<INPUT TYPE=TEXT NAME=addy3 VALUE=\"%s\" SIZE=\"50%\"></TR>\n", + $address3); + print "<TR><TD ALIGN=RIGHT>Public Address<TD>"; + printf("<INPUT TYPE=CHECKBOX NAME=addy_pub %s SIZE=\"50%\"></TR>\n", + (($addy_pub eq "0") ? '' : 'CHECKED')); + print "<TR><TD ALIGN=RIGHT>Comments<TD>"; + printf("<INPUT TYPE=TEXT NAME=comments VALUE=\"%s\" SIZE=\"50%\"></TR>\n", + $comments); + printf("<TR><TD ALIGN=RIGHT>Joined on<TD>@array[13]</TR>\n", $joined); + printf("<TR><TD ALIGN=RIGHT>Last Membership paid<TD>%s</TR>\n", $lstmemfee); + printf("<TR><TD ALIGN=RIGHT>Last Membership date<TD>%s</TR>\n", $lstpddate); + print "</TABLE>\n"; + print "<INPUT TYPE=HIDDEN NAME=\"type\" VALUE=\"adjust\">\n"; + print "<INPUT TYPE=SUBMIT VALUE=\"Update information\"><P>\n"; + print "Or <A HREF=\"/scs/games/edit.html\">Go back to the Edit page</A>\n"; + print "<P>"; + } + } else { +# Couldn't find the member ID given + print "No such member ID $id<P>\n"; + print "<A HREF=\"/scs/games/edit.html\">Try again</A>\n"; + } + +# Close down DB stuff + $sth->finish || bad_exit($sth->errstr); + + $dbh->disconnect || bad_exit($sth->errstr); + +# We are doing an adjust + } elsif ($type eq "adjust") { +# Connect to the Database + $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $user, $passwd) || bad_exit($sth->errstr); + +# Prepare the select statement + $sth = $dbh->prepare("SELECT * FROM members WHERE memberid = $id") || bad_exit($sth->errstr); + +# Execute it + $numrows = $sth->execute || bad_exit($sth->errstr); + +# Get one row. Only one.. if there is more than one, bad things have happened :) + if (@array = $sth->fetchrow_array) { + +# Check their password + if (&dtrail(@array[4]) ne &dtrail($input{'passwd'})) { + print "Bad password for Member ID $id<P>\n"; + print "Please report this error to <A HREF=\"mailto:darius\@dons.net.au\">\n"; + print "the administrator</A><P>\n"; + print "<A HREF=\"/scs/games/edit.html\">Try again</A>\n"; + } else { + $fname = san_str($input{'fname'}); + $lname = san_str($input{'lname'}); + $nick = san_str($input{'nick'}); + $pwd1 = san_str($input{'pwd1'}); + $pwd2 = san_str($input{'pwd2'}); + $age = san_num($input{'age'}); + $phone1 = san_str($input{'phone1'}); + $ph1_pub = (san_str($input{'ph1_pub'}) eq 'on') ? 't' : 'f'; + $phone2 = san_str($input{'phone2'}); + $ph2_pub = (san_str($input{'ph2_pub'}) eq 'on') ? 't' : 'f'; + $email = san_str($input{'email'}); + $email_pub = (san_str($input{'email_pub'}) eq 'on') ? 't' : 'f'; + $address1 = san_str($input{'addy1'}); + $address2 = san_str($input{'addy2'}); + $address3 = san_str($input{'addy3'}); + $addy_pub = (san_str($input{'addy_pub'}) eq 'on') ? 't' : 'f'; + $comments = san_str($input{'comments'}); + + if ($pwd1 ne $pwd2) { + print "New password mismatch\n"; + print "<A HREF=\"/scs/games/edit.html\">Try again</A>\n"; + } else { + $dbh->do("UPDATE members SET firstname=\'$fname\', lastname=\'$lname\', nickname=\'$nick\', pin=\'$pwd1\', age=\'$age\', phone1=\'$phone1\', ph1_pub=\'$ph1_pub', phone2=\'$phone2\', ph2_pub=\'$ph2_pub\', email=\'$email\', email_pub=\'$email_pub\', address1=\'$address1\', address2=\'$address2\', address3=\'$address3\', addy_pub=\'$addy_pub\', comments=\'$comments\' WHERE memberid=$id;") || bad_exit($sth->errstr); + print "Update finished!<P>\n"; + print "Go back to the <A HREF=\"/games/scs/edit.html\">Edit Page</A>"; + } + } + } else { +# Couldn't find the member ID given + print "No such member ID $id<P>\n"; + print "<A HREF=\"/scs/games/edit.html\">Try again</A>\n"; + } + +# Close down DB stuff + $sth->finish || bad_exit($sth->errstr); + + $dbh->disconnect || bad_exit($sth->errstr); + +# Something weird happened here + } else { + print "Unsupported action!<P>\n"; + print "Please email <A HREF=\"mailto:darius\@dons.net.au\">The Administrator</A> and<BR>\n"; + print "give a problem report. Thanks!<P>"; + } + + print &HtmlBot; +} + +sub bad_exit +{ + print "<H2>An internal error has occurred</H2><BR>"; + print "Please mail <A HREF=\"mailto:darius\@dons.net.au\">The Administrator</A> and\n"; + print "say the following error occured - $_[0]<P>"; + print "<A HREF=\"/scs/games/edit.html\">Back to the Edit Page</A>\n"; + + print &HtmlBot; + + exit(0); +} + +sub dtrail +{ + $_[0] =~ s/(\ *)$//g; + return $_[0]; +} + +sub san_str +{ + $_[0] =~ s/\\/\\\\/g; + $_[0] =~ s/'/\\'/g; + $_[0] =~ s/"/\\"/g; + return $_[0]; +} + +sub san_num +{ +# $_[0] =~ s/'/\\'/g; + return $_[0]; +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/mailout.pl Wed May 06 14:33:31 1998 +0000 @@ -0,0 +1,59 @@ +#!/usr/local/bin/perl + +use DBI; + +$user = ""; +$passwd = ""; +$dbname = "scs"; + +MAIN: +{ + # Work out the date 7 days ahead + ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime(time + (24 * 60 * 60 * 7)); + + $mon += 1; #Months start from 0!! + + $date = "$year-$mon-$mday"; + +# Connect to the Database + $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $user, $passwd) || bad_exit("Couldn\'t open DB - $sth->errstr"); + +# Prepare the select statement + $sth1 = $dbh->prepare("SELECT eventid, name, description, when, duration, cost, contact FROM events WHERE when > \'$date\' and when < (\'$date\'::datetime + \'24 hours\'::timespan)") || bad_exit("Prepare for description failed - $sth1->errstr"); + +# Execute it + $numrows1 = $sth1->execute || bad_exit("Execute for description failed - $sth1->errstr"); + + while (@array = $sth1->fetchrow_array) { + $eventid = @array[0]; + $name = @array[1]; + $text = @array[2]; + $when = @array[3]; + $length = @array[4]; + $cost = @array[5]; + $contact = @array[6]; + + $mailtxt = sprintf("Hi,\nThis is an automated letter reminding you of an\nevent on %s.\nIt should go for about %s.\n\n%s\n\nIt will cost %s.\n\nIf you want more information contact\n%s.\n", $when, $length, $text, $cost, $contact); + +# Prepare the select statement + $sth2 = $dbh->prepare("SELECT mailto(\'$eventid\')") || bad_exit("Couldn\'t prepare ppl list - $sth->errstr"); + + $numrows2 = $sth2->execute || bad_exit("Couldn\'t execute ppl list - $sth->errstr"); + + while (@array = $sth2->fetchrow_array) + { + print "Email this\n---\n$mailtxt\n---\nTo this person @array[0]\n"; + } + + $sth2->finish || bad_exit("Couldn\'t finish for ppl list - $sth->errstr"); + } + + $sth1->finish || bad_exit("Couldn\'t finish for mailtxt - $sth->errstr"); +} + +sub bad_exit +{ + print "The following error occured - $_[0]"; + + exit(0); +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/members.txt Wed May 06 14:33:31 1998 +0000 @@ -0,0 +1,1 @@ +Daniel O'Connor Darius 1 1234 21 +6141835247 +61882972544 darius@dons.net.au 34\ Hill\ Ave Cumberland\ Park SA\ 5041 SCS\ President 1998-04-07 3.00 1998-04-07
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/query.html Wed May 06 14:33:31 1998 +0000 @@ -0,0 +1,24 @@ +<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN"> +<HTML> + <HEAD> + <TITLE>Query the SCS Member Database</TITLE> + </HEAD> + + <BODY BGCOLOR="#00000" TEXT="#CCCCCC" LINK="#00EE20" VLINK="#55FF8B" ALINK="#FFFF00"> + <H1>Query the SCS Member Database</H1> + <FORM METHOD=GET ACTION="/cgi-bin/scs/query.pl"> + <TABLE WIDTH="100%"> + <TR> + <TD ALIGN=RIGHT>SCS Membership number<TD> + <INPUT TYPE="text" NAME="id" VALUE="" size="50%"></TR> + </TABLE> + + <INPUT TYPE=SUBMIT VALUE="Do Query"> + <HR> + <ADDRESS><A HREF="mailto:darius@dons.net.au">Daniel J. O'Connor</A></ADDRESS> + <!-- Created: Wed Apr 29 20:29:35 CST 1998 --> + <!-- hhmts start --> +Last modified: Wed May 6 22:46:00 CST +<!-- hhmts end --> + </BODY> +</HTML>
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/query.pl Wed May 06 14:33:31 1998 +0000 @@ -0,0 +1,127 @@ +#!/usr/local/bin/perl + +require "cgi-lib.pl"; +use DBI; + +$user = ""; +$passwd = ""; +$dbname = "scs"; + +MAIN: +{ +# Read in all the variables set by the form + &ReadParse(*input); + + print &PrintHeader; + print "<BODY BGCOLOR=\"#00000\" TEXT=\"#CCCCCC\" LINK=\"#00EE20\""; + print " VLINK=\"#55FF8B\" ALINK=\"#FFFF00\">"; + print "<META HTTP-EQUIV=\"Pragma\" content=\"no-cache\">\n"; + print "<TITLE>Query the SCS Database</TITLE>"; + print "<H2>Query the SCS Database</H2>"; + + $id = $input{'id'}; + +# Connect to the Database + $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $user, $passwd) || bad_exit($sth->errstr); + +# Prepare the select statement + $sth = $dbh->prepare("SELECT * FROM members WHERE memberid = $id") || bad_exit($sth->errstr); + +# Execute it + $numrows = $sth->execute || bad_exit($sth->errstr); + +# Get one row. Only one.. if there is more than one, bad things have happened :) + if (@array = $sth->fetchrow_array) { + $memberid = dtrail(@array[0]); + $firstname = dtrail(@array[1]); + $lastname = dtrail(@array[2]); + $nickname = dtrail(@array[3]); + $pin = dtrail(@array[4]); + $age = dtrail(@array[5]); + $phone1 = dtrail(@array[6]); + $ph1_pub = dtrail(@array[7]); + $phone2 = dtrail(@array[8]); + $ph2_pub = dtrail(@array[9]); + $email = dtrail(@array[10]); + $email_pub = dtrail(@array[11]); + $address1 = dtrail(@array[12]); + $address2 = dtrail(@array[13]); + $address3 = dtrail(@array[14]); + $addy_pub = dtrail(@array[15]); + $comments = dtrail(@array[16]); + $joined = dtrail(@array[17]); + $lstmemfee = dtrail(@array[18]); + $lstpddate = dtrail(@array[19]); + + print "<TABLE WIDTH=\"100%\">\n"; + printf "<TR><TD ALIGN=RIGHT><B>First Name</B><TD>%s</TR>\n", $firstname; + printf "<TR><TD ALIGN=RIGHT><B>Last Name</B><TD>%s</TR>\n", $lastname; + printf "<TR><TD ALIGN=RIGHT><B>Nick Name</B><TD>%s</TR>\n", $nickname; + printf "<TR><TD ALIGN=RIGHT><B>Member ID</B><TD>%s</TR>\n", $memberid; + printf "<TR><TD ALIGN=RIGHT><B>Age</B><TD>%s</TR>\n", $age; + + if ($ph1_pub eq '1') { + printf "<TR><TD ALIGN=RIGHT><B>Phone 1</B><TD>%s</TR>\n", $phone1; + } + if ($ph2_pub eq '1') { + printf "<TR><TD ALIGN=RIGHT><B>Phone 2</B><TD>%s</TR>\n", $phone2; + } + if ($email_pub eq '1') { + printf "<TR><TD ALIGN=RIGHT><B>Email</B><TD>%s</TR>\n", $email; + } + if ($addy_pub eq '1') { + printf "<TR><TD ALIGN=RIGHT><B>Address 1</B><TD>%s</TR>\n", $address1; + printf "<TR><TD ALIGN=RIGHT><B>Address 2</B><TD>%s</TR>\n", $address2; + printf "<TR><TD ALIGN=RIGHT><B>Address 3</B><TD>%s</TR>\n", $address3; + } + printf "<TR><TD ALIGN=RIGHT><B>Comments</B><TD>%s</TR>\n", $comments; + printf "<TR><TD ALIGN=RIGHT><B>Joined</B><TD>%s</TR>\n", $joined; + printf "<TR><TD ALIGN=RIGHT><B>Last Membership Fee</B><TD>%s</TR>\n", $lstmemfee; + printf "<TR><TD ALIGN=RIGHT><B>Paid last membership on</B><TD>%s</TR>\n", $lstpddate; + print "</TABLE>\n"; + print "<A HREF=\"/scs/games/query.html\">Go back to the Query page</A>\n"; + print "<P>\n"; + } else { +# Couldn't find the member ID given + print "No such member ID $id<P>\n"; + print "<A HREF=\"/scs/games/query.html\">Try again</A>\n"; + } + +# Close down DB stuff + $sth->finish || bad_exit($sth->errstr); + + $dbh->disconnect || bad_exit($sth->errstr); +} + + +sub bad_exit +{ + print "<H2>An internal error has occurred</H2><BR>\n"; + print "Please mail <A HREF=\"mailto:darius\@dons.net.au\">The Administrator</A> and<BR>\n"; + print "say the following error occured - $_[0]<P>\n"; + print "<A HREF=\"/scs/games/query.html\">Back to the Query Page</A>\n"; + + print &HtmlBot; + + exit(0); +} + +sub dtrail +{ + $_[0] =~ s/(\ *)$//g; + return $_[0]; +} + +sub san_str +{ + $_[0] =~ s/\\/\\\\/g; + $_[0] =~ s/'/\\'/g; + $_[0] =~ s/"/\\"/g; + return $_[0]; +} + +sub san_num +{ +# $_[0] =~ s/'/\\'/g; + return $_[0]; +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema.sql Wed May 06 14:33:31 1998 +0000 @@ -0,0 +1,83 @@ +-- Create schema for the SCS member and event database. + +-- Drop tables and sequences used +DROP FUNCTION mailto(int4); +DROP TABLE mem_ev_lnk; +DROP TABLE events; +DROP SEQUENCE event_id; +DROP TABLE members; +DROP SEQUENCE member_id; + +-- Sequence for Member ID's +CREATE SEQUENCE member_id INCREMENT 1 START 1; + +-- Member table. +CREATE TABLE members ( + memberid int4 NOT NULL, + firstname char(25) NOT NULL, + lastname char(25) NOT NULL, + nickname char(20), + pin char(10), + age int2, + phone1 char(20), + ph1_pub bool, + phone2 char(20), + ph2_pub bool, + email char(70) NOT NULL, + email_pub bool, + address1 char(30), + address2 char(30), + address3 char(30), + addy_pub bool, + comments text, + joined date, + lastmembfee money, + lastpaiddate date, + + PRIMARY KEY (memberid), + + CONSTRAINT memcon CHECK (firstname <> ''), CHECK (lastname <> ''), CHECK (nickname <> ''), + CHECK (memberid > 0) + ); + +-- Sequence for Event ID's +CREATE SEQUENCE event_id INCREMENT 1 START 1; + +-- Event table. +CREATE TABLE events ( + eventid int4 NOT NULL, + name text NOT NULL, + description text NOT NULL, + when datetime NOT NULL, + duration timespan, + cost text NOT NULL, + contact text NOT NULL, + + PRIMARY KEY (eventid), + + CONSTRAINT evcon CHECK (eventid > 0), CHECK (name <> ''), CHECK (description <> ''), + CHECK (cost <> ''), CHECK (contact <> '') + ); + +-- Table to link the Event table and the Member ID table +CREATE TABLE mem_ev_lnk ( + memberid int4, + eventid int4, + contact bool, + + FOREIGN KEY (memberid) REFERENCES members, + FOREIGN KEY (eventid) REFERENCES events, + + CONSTRAINT lnkcon CHECK (memberid > 0), CHECK (eventid > 0) + ); + +-- Function which returns a list of email addresses to mail about a +-- given eventid +CREATE FUNCTION mailto(int4) RETURNS SETOF bpchar AS + 'SELECT email FROM members WHERE memberid IN + (SELECT memberid FROM mem_ev_lnk WHERE eventid = $1 AND contact = \'yes\')' + LANGUAGE 'sql'; + +GRANT SELECT, INSERT, UPDATE ON members, events, mem_ev_lnk TO www; +GRANT SELECT, INSERT, UPDATE ON members, events, mem_ev_lnk TO darius; +
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/test_data.sql Wed May 06 14:33:31 1998 +0000 @@ -0,0 +1,10 @@ +insert into members values (nextval('member_id'), 'Daniel', 'O\'Connor', 'Darius', '1234', 21, '+61414835247', 'yes', '+61882972544', 'yes', 'darius@dons.net.au', 'yes', '34 Hill Ave', 'Cumberland Park', 'SA 5041', 'yes', 'SCS President', '1998-04-29', '3.00', '1998-04-29'); + +insert into members values (nextval('member_id'), 'Joe', 'Bloggs', +'Gryphen', '5678', 23, '+12345566', 'yes', '+9982312', 'no', +'joe@foo.net', 'yes', '18 Flibble St', 'Flub Park', 'XYZ 9999', 'no', 'Pleb', '1998-04-29', '2.00', '1998-04-29'); + +insert into events values (nextval('event_id'), 'Games Party', 'Come along to Anchor court at Flinders University and play network games\nsuch as Quake 2, Star Craft, and Total Anihiliation', '1998-05-30 13:00', '16 hours', '$5 for members, $8 for non-members', 'Daniel O\'Connor - darius@dons.net.au, 0414 835 247'); + +insert into mem_ev_lnk values (1, 1, 'yes'); +insert into mem_ev_lnk values (2, 1, 'no'); \ No newline at end of file