\n"; + print "Back to the Add User Page\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]; +} diff -r 019f8230ae37 -r d95e74cd12f4 edit.html --- /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 @@ + + +
\n"; + print "Try again\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 "
\n"; + print "Or Go back to the Edit page\n"; + print "
"; + } + } else { +# Couldn't find the member ID given + print "No such member ID $id
\n"; + print "Try again\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
\n"; + print "Please report this error to \n"; + print "the administrator
\n"; + print "Try again\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 "Try again\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!
\n"; + print "Go back to the Edit Page"; + } + } + } else { +# Couldn't find the member ID given + print "No such member ID $id
\n"; + print "Try again\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!
\n"; + print "Please email The Administrator and\n"; + print "give a problem report. Thanks!
"; + } + + print &HtmlBot; +} + +sub bad_exit +{ + print "
"; + print "Back to the Edit Page\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]; +} diff -r 019f8230ae37 -r d95e74cd12f4 mailout.pl --- /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); +} diff -r 019f8230ae37 -r d95e74cd12f4 members.txt --- /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 diff -r 019f8230ae37 -r d95e74cd12f4 query.html --- /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 @@ + + +
\n"; + } else { +# Couldn't find the member ID given + print "No such member ID $id
\n"; + print "Try again\n"; + } + +# Close down DB stuff + $sth->finish || bad_exit($sth->errstr); + + $dbh->disconnect || bad_exit($sth->errstr); +} + + +sub bad_exit +{ + print "
\n"; + print "Back to the Query Page\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]; +} diff -r 019f8230ae37 -r d95e74cd12f4 schema.sql --- /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; + diff -r 019f8230ae37 -r d95e74cd12f4 test_data.sql --- /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