############################################################################## # V A L I D A T E R E C O R D S # # by # # JPDeni # # Last Modified: 12 Jun 2000 # # # # sub validate_records subroutine re-wrote by shann123 and is based on # # JPDeni's original subroutine. # # All else is original by JPDeni and has not been changed. # ############################################################################## ############################################################################## # What it does: # # Allows you (as admin) to approve new (and, optionally, modified) records # # before they can be returned in a search. It also sends email to the # # owner of the record when the record is validated. You may also # # (optionally) be notified whenever a new record is added that requires # # validation. # ############################################################################## ############################################################################### # file: default.cfg # # # # somewhere in the authentication definitions # # add the following # ############################################################################### # Full path to sendmail on your system $mailprog = "|/usr/lib/sendmail -t -oeq"; # Fieldname that contains the email address of the user $db_email_field = 'Email'; # Your email address $admin_email = 'you@server.com'; ############################################################################### # file: default.cfg # # # # in the field definitions add # ############################################################################### Validated => [6, 'alpha', 0, 3, 1, 'No', 'Yes|No'], # Change the number of the field to match your database. You can name the field # anything you wish. # Also add the following: # Name of your validation field $db_validated_field = 'Validated'; ############################################################################### # file: default.cfg # # # # in the radio field definitions add # ############################################################################### %db_radio_fields = ( Validated => 'Yes,No' ); ############################################################################### #file: db.cgi # # sub main # # # # within the other "elsif" statements # # add the following # ############################################################################### elsif ($in{'validate_form'}) { if ($per_admin) { &html_validate_form; } else { &html_unauth; } } elsif ($in{'validate_records'}) { if ($per_admin) { &validate_records; } else { &html_unauth; } } ############################################################################### #file: db.cgi # # sub add_record # # # # after # # ($auth_user_field >= 0) and ($in{$db_cols[$auth_user_field]} = $db_userid);# # add the following # ############################################################################### (!$per_admin) and ($in{$db_validated_field} = "No"); ############################################################################### #file: db.cgi # # sub modify_record # # # # before # # $status = &validate_record; # # add the following # # # # Note: Use this only if you want to re-validate records after they are # # modified by the user. # ############################################################################### (!$per_admin) and ($in{$db_validated_field} = "No"); ############################################################################### #file: db.cgi # # sub view_records # # # # before # # my ($status, @hits) = &query("view"); # # add the following # ############################################################################### (!$per_admin) and ($in{$db_validated_field} = "Yes"); ############################################################################### #file: db.cgi # # new subroutine # # sub validate_records # # subroutine re-wrote for DBMan SQL version 1 by Shannon Geiger # # Original subroutine by JPDeni # # # ############################################################################### sub validate_records { # -------------------------------------------------------- # Validates or deletes a single or multiple records. my ($data, $auth_email_field, $query, $key, %delete_list, $rec_to_delete, %validate_list, $rec_to_validate, @lines, $line, @data, $errstr, $succstr, $output, $restricted, $found, $fieldnum); for ($i = 0; $i <= $#db_cols; $i++) { if ($db_cols[$i] eq $db_validated_field) { $fieldnum = $i; $found = 1; last; } } if (!$found) { &cgierr ("error in validate_records. No Validated field defined"); } $rec_to_delete = 0; $rec_to_validate = 0; foreach $key (keys %in) { if ($in{$key} eq "delete") { $delete_list{$key} = 1; $rec_to_delete = 1; } elsif ($in{$key} eq "validate") { $validate_list{$key} = 1; $rec_to_validate = 1; } } if ((!$rec_to_delete) && (!$rec_to_validate)) { &html_validate_form("no records specified."); return; } foreach $key (keys %delete_list) { $db_is_int{$db_key} ? ($key_q = int($key)) : ($key_q = $DBH->quote($key)); if ($in{$key} eq "delete") { # This query pulls any info you'd like to include in the email from the table. # change the number in this line: $email = $data[50] to match the email field # in the database. You can create any string from data you have the same way. # Example: $name = $data[1] could be used to add their name to the email # if their name is in that field of the table. $query = qq! SELECT * FROM $db_table WHERE $db_key = $key_q !; my $sth = $DBH->prepare($query); $sth->execute(); if ($sth->rows) { while (@data = $sth->fetchrow_array) { $email = $data[50]; } } open (MAIL, "$mailprog") or &cgierr("Can't start mail program"); print MAIL "To: $email\n"; print MAIL "BCC: $admin2_email\n"; print MAIL "From: $admin_email\n"; # you can change the subject line to whatever you want print MAIL "Subject: $html_title: Record deleted\n\n"; print MAIL "-" x 75 . "\n\n"; # Here's where you create your canned delete message. You can use the $rec{'fieldname'} variables # just like in sub html_record to include the values of any fields that you'd like to. # As you define your message, use carriage returns for a newline $email_message = qq| I'm sorry, but your record could not be added to $html_title. We appreciate your coming by and possibly we can be of assistance to you later. Sincerely, John Doe Webmaster $html_title |; # be sure to leave in the last |; to close off your quoted text. print MAIL $email_message; close (MAIL); } # deletes the record selected from the table after sending email. $query = qq! DELETE FROM $db_table WHERE $db_key = $key_q !; $rc = $DBH->do($query); # deletes associated files from your upload directory. if ($db_upload) { if (-e "$SAVE_DIRECTORY/$key/") { opendir (GRAPHIC, "$SAVE_DIRECTORY/$key/") or &cgierr("unable to open directory in delete records: $SAVE_DIRECTORY/$data[$db_key_pos]. Reason: $!"); @files = readdir(GRAPHIC); closedir (GRAPHIC); foreach $file (@files) { unlink ("$SAVE_DIRECTORY/$key/$file"); } rmdir "$SAVE_DIRECTORY/$key/"; } else { $output .= $line . "\n"; } } if ($rc) { $delete_list{$key} = 0; foreach (keys %db_indexed) { &delete_index ($key, $_); } } } foreach $key (keys %delete_list) { if ($delete_list{$key}) { # Check to see if any items weren't deleted $errstr .= "$key,"; # that should have been. } } foreach $key (keys %validate_list) { $db_is_int{$db_key} ? ($key_q = int($key)) : ($key_q = $DBH->quote($key)); if ($in{$key} eq "validate") { # This query pulls any info you'd like to include in the email from the table. # change the number in this line: $email = $data[50] to match the email field # in the database. You can create any string from data you have the same way. # Example: $name = $data[1] could be used to add their name to the email # if their name is in that field of the table. $query = qq! SELECT * FROM $db_table WHERE $db_key = $key_q !; my $sth = $DBH->prepare($query); $sth->execute(); if ($sth->rows) { while (@data = $sth->fetchrow_array) { $email = $data[50]; } } open (MAIL, "$mailprog") or &cgierr("unable to open mail program"); print MAIL "To: $email\n"; print MAIL "From: $admin_email\n"; # you can change the subject line to whatever you want print MAIL "Subject: $html_title: Record validated\n\n"; print MAIL "-" x 75 . "\n\n"; # Here's where you create your canned validate message. You can use the $rec{'fieldname'} variables # just like in sub html_record to include the values of any fields that you'd like to. # As you define your message, use carriage returns for a newline $email_message = qq| I'm pleased to say that your record has been added to $html_title. We look appreciate your addition to our database. Please let us know if there is anything we can do to assist you. Sincerely, John Doe Webmaster $html_title |; # be sure to leave in the last |; to close off your quoted text. print MAIL $email_message; close (MAIL); # changes the validated field to Yes so records can be viewed by everyone. $query = qq! UPDATE $db_table SET Validated = 'Yes' WHERE $db_key = $key_q !; $rc = $DBH->do($query); if ($rc) { $validate_list{$key} = 0; foreach (keys %db_indexed) { &delete_index ($key, $_); } } } else { $output .= $line . "\n" } } foreach $key (keys %delete_list) { $delete_list{$key} ? ($delerrstr .= "$key,") : ($delsuccstr .= "$key,"); } chop($delsuccstr); chop($delerrstr); foreach $key (keys %validate_list) { $validate_list{$key} ? ($valerrstr .= "$key,") : ($valsuccstr .= "$key,"); } chop($valsuccstr); chop($valerrstr); if ($delsuccstr) { $resultstr = "Records with the following IDs were deleted: $delsuccstr
"; } if ($delerrstr) { $resultstr .= "Records with the following IDs were not deleted: $delerrstr
"; } if ($valsuccstr) { $resultstr .= "Records with the following IDs were validated: $valsuccstr
"; } if ($valerrstr) { $resultstr .= "Records with the following IDs were not validated: $valerrstr"; } &auth_logging("deleted records: $delsuccstr") if ($auth_logging); &auth_logging("validated records: $valsuccstr") if ($auth_logging); &html_validate_success($resultstr); } ############################################################################### #file: html.pl # # sub html_footer # # # # Add # ############################################################################### print qq!| Validate ! if ($per_admin); ############################################################################### #file: html.pl # # sub html_record_form # # # # Add (probably near the bottom of the form) # ############################################################################### |; # to close off any previous print qq| statement if ($per_admin) { print qq|Validated |; print &build_radio_field($db_validated_field,$rec{$db_validated_field}); print ""; } else { print qq||; } print qq| ############################################################################### #file: html.pl # # additional lines # # sub html_add_success # # --optional-- # # add before # # &html_print_headers; # ############################################################################### %rec=&get_record($in{$db_key}); open (MAIL, "$mailprog") or &cgierr("unable to open mail program"); print MAIL "To: $admin_email\n"; print MAIL "From: $admin_email\n"; print MAIL "Subject: New Record at $html_title\n\n"; print MAIL "A new record was added at $html_title with the following information:\n\n"; foreach $column (@db_cols) { print MAIL "$column: $rec{$column}\n"; } print MAIL "\n\n"; close MAIL; ############################################################################### #file: html.pl # # new subroutine # # sub html_validate_form # ############################################################################### sub html_validate_form { # -------------------------------------------------------- # The user has searched the database for deletion and must now # pick which records to delete from the records returned. This page # should produce a checkbox with name=ID value=delete for each record. # We have to do a little work to convert the array @hits that contains # the search results to a hash for printing. $in{$db_validated_field} = "No"; my ($status, @hits) = &query("mod"); my ($numhits) = ($#hits+1) / ($#db_cols+1); my ($maxhits); $in{'mh'} ? ($maxhits = $in{'mh'}) : ($maxhits = $db_max_hits); $in{'nh'} ? ($nh = $in{'nh'}) : ($nh = 1); my (%tmp); &html_print_headers; print qq| $html_title: Validate Form
$html_title: Validate Record(s)

<$font_title> >Validate Record(s)

<$font> |; if ($status ne "ok") { # There was an error searching! print qq|

<$font_error>Error: $status

|; } else { print qq|
|; # Go through each hit and convert the array to hash and send to # html_record for printing. Also add a checkbox with name=key and value=delete. print qq|

<$font> Check which records you wish to validate or delete and then press "Validate Records":
Your search returned $db_total_hits matches. |; if ($db_next_hits) { print "
<$font>Pages: $db_next_hits"; } print ""; for (0 .. $numhits - 1) { %tmp = &array_to_hash($_, @hits); print qq|"; } print "
Validate
Delete
Modify		|; &html_record (%tmp); print "
"; if ($db_next_hits) { print "
<$font>Pages: $db_next_hits"; } } print qq|

|; &html_footer; print qq|
|; } ############################################################################### #file: html.pl # # new subroutine # # sub html_validate_success # ############################################################################### sub html_validate_success { # -------------------------------------------------------- # This page let's the user know that the records were successfully # validated. my $message = shift; $page_title = "Record(s) Validated"; &html_print_headers; print qq| $html_title: Error: Record(s) Not Deleted.
$html_title: Record(s) Validated

<$font_title> Record(s) Validated

|; # < -- Start page text -- > print qq| <$font>This is the result of your validation:
'$message'.

|; # < -- End page text --> &html_footer; print qq|
|; }