#!/usr/bin/perl -w

#####################################
#  There are two database tables this application uses.  One is
# for riders, the other for drivers.  I chose to put them in two
# tables because they talk about different things.  The only overlap
# is in the use of name and email.
#
#  Passengers know in which car they are, cars know how many passengers
# they have, but not who they are.
#
# Copyright 2001 Ben Hartshorne.  All rights reserved.
# Version 2.6
# $Id: skipool.cgi,v 1.10 2002/10/20 21:04:37 ben Exp $
#####################################


#use strict;	
use CGI;
use CGI::Carp 'fatalsToBrowser';
use CGI::Pretty;
use DBI;
use Mail::Sendmail;
use Date::Manip;
use Digest::SHA1;
use ski_db;


my $DEBUGGING = 0;
my $DATE = `date`;
my $posted = new CGI;    

my $action;

print $posted->header;
print $posted->start_html( -title=>'SkiPool',
			   -author=>'ben-skicode@hartshorne.net',
			   -meta=>{'generator'=>'emacs'},
			   -BGCOLOR=>'FFFFFF');

if($DEBUGGING){
    foreach $key ($posted->param()) {
	print $key . " => " . $posted->param("$key") . "<p>\n";
    }
}

$action = $posted->param('action');
print $posted->h1($action) if $DEBUGGING;
 SWITCH: {
     if($action eq 'display_items'){ &display_items($posted);last SWITCH; } 
     if($action eq 'sign_up_for_car'){ &sign_up_for_car($posted);last SWITCH; } 
     if($action eq 'delete_from_car'){ &delete_from_car($posted);last SWITCH; } 
     if($action eq 'delete_from_car2'){ &delete_from_car2($posted);last SWITCH; } 
     if($action eq 'sign_up_Submit'){ &sign_up_Submit($posted);last SWITCH; }
     if($action eq 'register_car_submit'){&register_car_submit($posted);last SWITCH; }
     if($action eq 'register_car_verified'){&register_car_verified($posted);last SWITCH; }
     &actionNotRecognized($posted);
 }

print $posted->hr;
# print $posted->dump;
print qq{\n<ADDRESS>Ben Hartshorne, <A HREF="mailto:skipool\@green.hartshorne.net">skipool\@green.hartshorne.net</A></ADDRESS><BR>\n};
print $posted->end_html;
############################## end program ###############################

sub actionNotRecognized {
    my $posted = shift;
    print $posted->h1("I'm sorry, but I've gotten lost in my code somewhere.  There's probably a typo.  <a href=\"mailto:skibug\@green.hartshorne.net\">Blame ben</a>, and then please return to <A HREF=\"http://skipool.hartshorne.net/\">the home page</A> and try again.");
}

#Display all the items in the database.
sub display_items {
    my $posted = shift;
    my ($dbh, $car_sth, $pass_sth, $need_ride_sth, $sql);
    my ($car_id, $driver, $car_desc, $num_spots, $etd, $ski_rack, $fourwd, $notes);
    my ($pass_id, $rider, $phone_number, $email);
    my ($rider_up, $rider_down, $skis, $board);
    my ($origin, $destination);
    my $passenger;
    my $gray = "#CCFFFF"; #boolean to alternate cars gray / white.  make like a ledger thing.
                          # alternate CCFFFF with FFFFFF.
    my $date; 
    $date = &ParseDate($DATE);
    $dbh = &Ski::DB::get_handle();
    
    ### print page intro
    print "<h2>Join a carpool! </h2>";
    print "<p>These are the cars going up and coming back from Tahoe. Click on one of the ";
    print "  cars below to join.<br>";
    print "If you're already listed in one of the cars, and you need to cancel, click ";
    print "  on your name and it will remove you from that car. <br>";
    print "If none of these cars meet your schedule, <a href=\"http://skipool.hartshorne.net/need_ride.php\">";
    print "  request a ride</a>.</p>";
    print "<p>&nbsp;</p>";
    
    print "<hr width=90% noshade size=10>";

    ### print people without cars
    print "<h3 align=center>People needing rides:</h3>";
    print(qq{<table width="730" border="0" cellspacing="1">});
    print(qq{<tr bgcolor="#CCCCCC">
	<td width="15%"><b>Name</b></td>
	<td width="15%"><b>Time Up</b></td>
	<td width="15%"><b>Time Down</b></td>
	<td width="10%"><b>Origin</b></td>
	<td width="10%"><b>Destination</b></td>
	<td width="10%"><b>Equip</b></td>
	<td width="15%"><b>Add to car</b></td>
	<td width="5%"><font size="-2">Delete</td>
	<td width="5%"><font size="-2">Update</td>
      </tr>});
    
    $sql = "SELECT pass_id, requested_time_up, requested_time_down, have_skis, have_board, origin, destination FROM pass_wo_cars ORDER BY requested_time_up";
    $need_ride_sth = $dbh->prepare($sql);
    $need_ride_sth->execute();
    $need_ride_sth->bind_columns(undef, \$pass_id, \$rider_up, \$rider_down, \$skis, \$board, \$origin, \$destination);
    while($need_ride_sth->fetch()){
	$sql = "SELECT rider FROM passengers WHERE pass_id = $pass_id";
	$pass_sth = $dbh->prepare($sql);
	$pass_sth->execute();
	$pass_sth->bind_columns(undef, \$rider);
	$pass_sth->fetch();
	if($gray eq "#AAFFAA"){
	    $gray = "#CCFFFF";
	}else{
	    $gray = "#AAFFAA";
	}
	$rider_up = &Date::Manip::UnixDate($rider_up, "%a %b %e, %I:%M %p");
	$rider_down = &Date::Manip::UnixDate($rider_down, "%a %b %e, %I:%M %p");

	print(qq{<tr bgcolor="$gray">});
	print("<td>&nbsp;&nbsp;$rider</td><td>$rider_up</td><td>$rider_down</td>");
	print("<td>$origin</td><td>$destination</td>");
	print("<td>");
	if($skis eq 'on'){
	    print "Skis ";
	}
	if($board eq 'on'){
	    print "Board";
	}
	print "&nbsp;";
	print "</td>";
	print(qq{<td><a href="get_rider.cgi?pass_id=$pass_id">Take me with you!</a></td>});
	print(qq{<td align="center"><a href="delete_rider.cgi?pass_id=$pass_id">(D)</a></td>});
	print(qq{<td align="center"><a href="update_rider.cgi?pass_id=$pass_id">(U)</a></td>});
	print "</tr>\n";
    }
    print "</table><P>\n";

    
    print "<hr width=90% noshade size=10>\n";

	
    ### print cars going up
    print "<h3 align=center>Cars going up:</h3>\n";

    $sql = "SELECT car_id, driver, email, car_type, open_spots_up, ski_rack, fourwd, chains, stops, music, notes, requested_time_departure, origin, destination FROM cars WHERE requested_time_departure > '$date' ORDER BY requested_time_departure";
    $car_sth = $dbh->prepare($sql);
    $car_sth->execute();
    $car_sth->bind_columns(undef, \$car_id, \$driver, \$email, \$car_desc, \$num_spots, \$ski_rack, \$fourwd, \$chains, \$stops, \$music, \$notes, \$etd, \$origin, \$destination);
    print "<P>bound vars for fetched car</P>\n" if $DEBUGGING;
    while ($car_sth->fetch()) {
	if($gray eq "#CCCCCC"){
	    $gray = "#AAFFAA";
	}else{
	    $gray = "#CCCCCC";
	}
      $etd = &Date::Manip::UnixDate($etd, "%a %b %e, %I:%M %p");
      print <<CAR_GOING_UP;
<hr width=50% align="left">
<table width="730" border="0" cellspacing="1">
  <tr>
      <td bgcolor="$gray" colspan=3>
        <b>Driver:</b> <a href="mailto:$email">$driver</a>
        <a href="carsearch.cgi?car_id=$car_id">(update your itinerary)</a>
      </td>
      <td bgcolor="$gray">&nbsp;</td>
  </tr>
  <tr> 
    <td width="20%" bgcolor="$gray"><b>Driving Up:</b><br>$etd</td>
    <td width="80%" colspan="2"> 
      <table width="100%" border="0" cellspacing="1" bgcolor="#FFFFAA">
        <tr> 
	  <td colspan="2"><b>Description:</b> $car_desc</td>
          <td><b>4WD:</b> $fourwd</td>
          <td><b>Chains:</b> $chains</td>
	  <td><b>ski rack:</b> $ski_rack</td>
        </tr>
        <tr>
          <td><b>Seats Remaining:</b> $num_spots</td>
          <td><b>Stops:</b> $stops</td>
          <td><b>Music:</b> $music</td>
          <td><b>Origin:</b> $origin</td>
          <td><b>Destination:</b> $destination</td>
	</tr>
      </table>
    </td>
  <td bgcolor="$gray">&nbsp;</td></tr>
  <tr> 
    <td width="16%" bgcolor="$gray"> 
      <div align="center"> 
        <h3><a href="skipool.cgi?action=sign_up_for_car&car_id=$car_id&trip=up">Join This <br>
          Car!</a></h3>
      </div>
    </td>
    <td width="170"> 
      <b>Passengers</b><br>
      <table border="0" cellspacing="1" bgcolor="#FFCCFF">
CAR_GOING_UP
  $sql = "SELECT pass_id, rider, phone_number, email, pass_notes FROM passengers WHERE car_id_up = $car_id";
      $pass_sth = $dbh->prepare($sql);
      $pass_sth->execute();
      $pass_sth->bind_columns(undef, \$pass_id, \$rider, \$phone_number, \$email, \$pass_notes);
      while ($pass_sth->fetch()) {
	print "<tr><td\><a href=\"mailto:$email\">$rider</a></td><td>$phone_number</td><td><a href=\"skipool.cgi?action=delete_from_car&car_id=$car_id&pass_id=$pass_id&trip=up\">Remove</a></td></tr>";
	if($pass_notes){ #only print notes if there's something there
	    $notes .= "<br>" . $rider . ": " . $pass_notes;
	}
    }
      print "</table></td>\n";#done printing passengers
      print "<td width=\"435\"><b>Notes</b>";
      print "<table border=\"0\" cellspacing=\"1\" bgcolor=\"#CCCCFF\"><tr><td>";
      print $notes;
      print "</td></tr></table></td>";
      print "<td bgcolor=\"$gray\">&nbsp;</td></tr><tr><td colspan=3 bgcolor=\"$gray\">&nbsp;</td><td bgcolor=\"$gray\">&nbsp;</td></tr>\n</table>";
    } #done with fetching all cars going up
    

    ### print cars going down
    print "<hr width=90% noshade size=10>";
    print "<h3 align=center>Cars going down:</h3>";

    $sql = "SELECT car_id, driver, email, car_type, open_spots_down, ski_rack, fourwd, chains, stops, music, notes, requested_time_return, origin, destination FROM cars WHERE requested_time_return > '$date' ORDER BY requested_time_return";
    $car_sth = $dbh->prepare($sql);
    $car_sth->execute();
    $car_sth->bind_columns(undef, \$car_id, \$driver, \$email, \$car_desc, \$num_spots, \$ski_rack, \$fourwd, \$chains, \$stops, \$music, \$notes, \$etd, \$origin, \$destination);
    print "<P>bound vars for fetched car</P>\n" if $DEBUGGING;
    while ($car_sth->fetch()) {
	if($gray eq "#CCFFFF"){
	    $gray = "#CCCCCC";
	}else{
	    $gray = "#CCFFFF";
	}
      $etd = &Date::Manip::UnixDate($etd, "%a %b %e, %I:%M %p");
      print <<CAR_GOING_DOWN;
<hr width=50% align="left">
<table width="730" border="0" cellspacing="1">
  <tr>
      <td bgcolor="$gray" colspan=3>
	  <b>Driver:</b> <a href="mailto:$email">$driver</a>
        <a href="carsearch.cgi?car_id=$car_id">(update your itinerary)</a>
      </td>
      <td bgcolor="$gray">&nbsp;</td>
  </tr>
  <tr> 
    <td width="20%" bgcolor="$gray"><b>Driving Down:</b><br>$etd</td>
    <td width="80%" colspan="2"> 
      <table width="100%" border="0" cellspacing="1" bgcolor="#FFFFAA">
        <tr> 
	  <td colspan="2"><b>Description:</b> $car_desc</td>
          <td><b>4WD:</b> $fourwd</td>
          <td><b>Chains:</b> $chains</td>
          <td><b>ski rack:</b> $ski_rack</td>
        </tr>
        <tr>
          <td><b>Seats Remaining:</b> $num_spots</td>
          <td><b>Stops:</b> $stops</td>
          <td><b>Music:</b> $music</td>
          <td><b>Origin:</b> $origin</td>
          <td><b>Destination:</b> $destination</td>
      </table>
    </td>
   <td bgcolor="$gray">&nbsp;</td></tr>
  <tr> 
    <td width="16%" bgcolor="$gray"> 
      <div align="center"> 
        <h3><a href="skipool.cgi?action=sign_up_for_car&car_id=$car_id&trip=down">Join This <br>
          Car!</a></h3>
      </div>
    </td>
    <td width="170"> 
      <b>Passengers</b><br>
      <table border="0" cellspacing="1" bgcolor="#FFCCFF">
CAR_GOING_DOWN
    $sql = "SELECT pass_id, rider, phone_number, email, pass_notes FROM passengers WHERE car_id_down = $car_id";
	$pass_sth = $dbh->prepare($sql);
	$pass_sth->execute();
	$pass_sth->bind_columns(undef, \$pass_id, \$rider, \$phone_number, \$email, \$pass_notes);
	while ($pass_sth->fetch()) {
	    print "<tr><td><a href=\"mailto:$email\">$rider</a></td><td>$phone_number</td><td><a href=\"skipool.cgi?action=delete_from_car&car_id=$car_id&pass_id=$pass_id&trip=down\">Remove</a></td></tr>";
	    if($pass_notes){ #only print notes if there's something there
		$notes .= "<br>" . $rider . ": " . $pass_notes;
	    }
	}
	print "</table></td>\n"; #done printing passengers
	print "<td width=\"435\"><b>Notes</b>";
	print "<table border=\"0\" cellspacing=\"1\" bgcolor=\"#CCCCFF\"><tr><td>";
	print $notes;
	print "</td></tr></table></td>";
	print "<td bgcolor=\"$gray\">&nbsp;</td></tr><tr><td colspan=3 bgcolor=\"$gray\">&nbsp;</td><td bgcolor=\"$gray\">&nbsp;</td></tr>\n</table>";
    } #done with fetching all cars going down
    &Ski::DB::return_handle($dbh);
}

sub sign_up_for_car {
    my $posted = shift;
    my ($dbh, $sth, $sql);
    my $car_id = $posted->param('car_id');
    my $up_or_down = $posted->param('trip');
    my ($driver, $num_spots, $etd, $phone_number, $email);
    
    $dbh = &Ski::DB::get_handle();
    $sql = "SELECT driver, open_spots_" . $up_or_down . ", requested_time_departure, phone_number, email FROM cars WHERE car_id = $car_id";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    $sth->bind_columns(undef, \$driver, \$num_spots, \$etd, \$phone_number, \$email);
    $sth->fetch();
    print $posted->h1("Sign up!");
    print "<P>You have asked to join " . $driver . "'s car for the trip " . $up_or_down . ".  He/she has $num_spots open spots left, \n";
    print "and is leaving on " . &Date::Manip::UnixDate($etd, "%a %b %e, at %I:%M %p.");
    print "Your driver can be reached at $phone_number or $email.  You can also reach everyone in your car at tahoecar-" . $car_id . "\@skipool.hartshorne.net.  Make sure to call your driver to confirm.</P>\n";
    print $posted->start_form( -method=>'POST',
			       -action=>'skipool.cgi');
    $posted->param('action', 'sign_up_Submit');
    print $posted->hidden('action');
    $posted->param('car_id', $car_id);
    print $posted->hidden('car_id');
    print "<table width=80% border=0>\n";
    print "<tr><td width=50%><table>\n";
    print "<tr><td><P>Please tell me your name:<BR>\n";
    print $posted->textfield( -name => 'rider',
			     -size=>40 );
    print "</td></tr>\n";
    print "<tr><td><P>Please tell me your phone number:<BR>\n";
    print $posted->textfield( -name=>'phone_number',
			     -size=>40);
    print "</td></tr>\n";
    print "<tr><td><P>Please tell me your e-mail address:<BR>\n";
    print $posted->textfield( -name=>'email',
			      -size=>40);
    print "<tr><td><P>Please give me a password:<BR>\n";
    print $posted->password_field( -name=>'password',
				   -size=>40);
    print "</td></tr>\n";
    print "<tr><td><P>Notes (these will be added to the driver's notes):<BR>\n";
    print $posted->textarea( -name=>'pass_notes',
			     -cols=>80,
			     -rows=>3
			     -default=>"You can drive part time, you have an ipod, etc.");
    print "</td></tr>\n";
    print "<tr><td><P>Send me mail if:<BR>\n";
#    print $posted->checkbox( -name=>'car_change_mail',
#			     -label=>"");
#    print " any changes are made to the car<BR>\n";
    print $posted->checkbox( -name=>'new_car_mail',
			     -label=>"");
    print " any new cars register to drive (mailing list subscription)<br>\n";
    print $posted->checkbox( -name=>'blue_moon_mail',
			     -label=>"");
    print " it's a blue moon<br>\n";
    print "</td></tr></table></td>\n";
    print "<td><p align=\"center\"><table cellpadding=10>\n";
    print "<tr><td>&nbsp;</td><td>You are signing up for <br>this car for the trip</td><td>\n";
    print $posted->popup_menu( -name=>'trip',
    				-values=>['up', 'down'],
				-default=>"$up_or_down",
				-align=>"center");
    print "</td></tr></table></p></td></tr></table>\n";
    print "<P>\n";
    print "<EM>It may take a little time, but please press the submit button only once.</EM>\n";
    print $posted->submit(-name=>"Carpool!");
    print $posted->end_form;
    &Ski::DB::return_handle($dbh);
}

sub sign_up_Submit {
    my $posted = shift;
    my ($dbh, $car_sth, $car_sql, $pass_sth, $pass_sql);
    my ($car_open_spots, $driver, $driver_phone_number, $driver_email, $pass_exist);
    my $car_id = $posted->param('car_id');
    my $up_or_down = $posted->param('trip');
    my $rider = $posted->param('rider');
    my $phone_number = $posted->param('phone_number');
    my $email = $posted->param('email');
    my $password = $posted->param('password');
    my ($encrypted_pass, $database_pass);
    my $pass_notes = $posted->param('pass_notes');
    my $car_change_mail = $posted->param('car_change_mail');
    my $new_car_mail = $posted->param('new_car_mail');
    my $blue_moon_mail = $posted->param('blue_moon_mail');
    my %mail; #to send email to the driver

    
    $dbh = &Ski::DB::get_handle();
    $car_sql = "SELECT open_spots_" . $up_or_down . ", driver, phone_number, email FROM cars WHERE car_id = $car_id";
    $car_sth = $dbh->prepare($car_sql);
    $car_sth->execute();
    $car_sth->bind_columns(undef, \$car_open_spots, \$driver, \$driver_phone_number, \$driver_email);
    $car_sth->fetch();
    if($car_open_spots < 1){
	print $posted->h2("I'm Sorry.");
	print "<P>The car you chose has fewer seats than you said you need.  \n";
	print "I don't believe in crowded cars, you'll have to go back and choose\n";
	print "another car.  Sorry!<P>\n";
	return;
    }
    $pass_sql = "SELECT rider FROM passengers WHERE car_id_" . $up_or_down . " = $car_id";
    $pass_sth = $dbh->prepare($pass_sql);
    $pass_sth->execute();
    $pass_sth->bind_columns(undef, \$pass_exist);
    ############### check and see if they already are in the car, and warn them.
    while ($pass_sth->fetch()){
	if($pass_exist eq $rider){ #make this test better
	    print "<h2><font color=red>Warning!</font></h2>";
	    print "<p><b>You're already in the car, but I'm adding you a second time.</b>  If this isn't\n";
	    print "what you want, go to the main page and delete the second occurence of you \n";
	    print "from the car.</p>";
	    print "<p>pass_exist = $pass_exist, rider = $rider</p>" if $DEBUGGING;
	    last;
	}
    }
    
    print "<P>Tests passed ok, entering rider in database</P>\n" if $DEBUGGING;
    print "<P>car open spots = $car_open_spots before resetting</P>\n" if $DEBUGGING;
    $car_open_spots -= 1;
    print "<P>car open spots = $car_open_spots after resetting</P>\n" if $DEBUGGING;
    $encrypted_pass = Digest::SHA1::sha1_base64($password);
    $pass_sql = "INSERT INTO passengers (rider, car_id_" . $up_or_down . ", phone_number, email, password, car_change_mail, new_car_mail, pass_notes) VALUES (" . 
	$dbh->quote($rider) . ", " . 
	$dbh->quote($car_id) . ", " . 
	$dbh->quote($phone_number) . ", " . 
	$dbh->quote($email) . ", " . 
	$dbh->quote($encrypted_pass) . ", " . 
	$dbh->quote($car_change_mail) . ", " . 
	$dbh->quote($new_car_mail) . ", " . 
#	$dbh->quote($blue_moon_mail) . ", " . 
	$dbh->quote($pass_notes) . ")";
    $car_sql = "UPDATE cars SET open_spots_" . $up_or_down . " = $car_open_spots WHERE car_id = $car_id";
    $car_sth = $dbh->prepare($car_sql);
    $pass_sth = $dbh->prepare($pass_sql);
    print "<P>about to enter passengers</P>\n" if $DEBUGGING;
    print "<P>pass_sql = >>> $pass_sql <<<</P>\n" if $DEBUGGING;
    print "<P>car_sql = >>> $car_sql <<<</P>\n" if $DEBUGGING;
    $pass_sth->execute(); #this order is actually important, cuz if the pass insert fails, 
    $car_sth->execute();  # you don't want to decrement the car's open_spots number.


    ########### send driver an email letting them know they have another passenger
    %mail = (
	    To		=> "$driver <$driver_email>",
	    From	=> "Skipool <skipool\@green.hartshorne.net>",
	    Subject	=> "New passenger: $rider",
	    Body	=> "Hi $driver,\n\t$rider has requested a seat in your car for the trip $up_or_down.  He or she can be reached\nat $email or $phone_number.  \n\nYour current list of riders is:\n" . &get_formatted_rider_list($dbh, $car_id, $up_or_down) . "\nYou can send mail to everyone in your car at tahoecar-" . $car_id . "\@skipool.hartshorne.net.  Head back to http://skipool.hartshorne.net/ to verify your list if you want.\n\nThanks for playing!\n\n-ben\n"
    );
    print $posted->h2("Thanks!");
    print "<P>You have been registered with $driver.  Remember to call them at $driver_phone_number\n";
    print "to confirm.  ";
    if (sendmail(%mail)){
	    print "I have sent a message to $driver announcing you as a passenger, but you should talk\n";
	    print "to him or her directly anyways.</P>\n";
    }else{
	    print "I tried to send $driver an email announcing you as a passenger, but something went wrong.\n";
	    print "Would you take care of that for me?  You can reach $driver at $driver_email.  Thanks!</p>\n";
    }
    %mail = (
	    To		=> "$rider <$email>",
	    From	=> "Skipool <skipool\@green.hartshorne.net>",
	    Subject	=> "Tahoe Trip with $driver",
	    Body	=> "Hi $rider,\n\tThis message is confirmation that you have signed up for a ride with $driver.  He or she can be reached\nat $driver_email or $driver_phone_number.  You can send mail to everyone in this car at tahoecar-" . $car_id . "\@skipool.hartshorne.net.  \n\nHead back to http://skipool.hartshorne.net/ to verify your ride if you want.\n\nThanks for playing!\n\n-ben\n"
    );

    sendmail(%mail);
    print "I have sent you a message confirming your ride.  If you don't see it in your mailbox soon, you should come back here and make sure that everything's kosher.  Technology aside though, your best bet is still individual communication with your driver.</P>\n";
    print "<p>If you want to contact everyone in this car, you can send mail to tahoecar-$car_id\@skipool.hartshorne.net.</p>\n";
    print "<p>See you up there!</P>\n"; 
    #### subscribe to newcar if they asked us to
    if($new_car_mail eq "on"){
	%mail = (
		 To      => "newcar-request\@hartshorne.net",
		 From    => "$email",
		 Subject => "subscribe"
		 );
	if(sendmail(%mail)){
	    print "<P>I have sent a subscription request to newcar\@hartshorne.net, the
mailing list that announces when new cars sign up.  You should get a confirmation
in the mail soon.  You must reply to this confirmation before your subscription
will be activated.  Visit <a href=\"http://skipool.hartshorne.net/cgi-bin/mailman/listinfo/newcar\">
http://skipool.hartshorne.net/cgi-bin/mailman/listinfo/newcar</a>
for more information about the list (or to unsubscribe).</p>";
	}else{
	    print "<P>I tried to send mail to newcar-request\@hartshorne.net to subscribe you to
the new car announcement mailing list, but I failed.  You're going to have to do it yourself.
Please go to <a href=\"http://skipool.hartshorne.net/cgi-bin/mailman/listinfo/newcar\">
http://skipool.hartshorne.net/cgi-bin/mailman/listinfo/newcar</a> and subscribe.  Thanks!</p>";
	}
    }
    print "<P>You can <a href=\"skipool.cgi?action=display_items\">verify you were added</a> or <A HREF=\"http://skipool.hartshorne.net/\">Go Back Home</A></P>";
    &Ski::DB::return_handle($dbh);
    
}

# takes a dbh and car_id
# returns a string of the passengers, formatted for text
sub get_formatted_rider_list(){
    my $dbh = shift;
    my ($sth, $sql);
    my $car_id = shift;
    my $up_or_down = shift;
    my ($passenger, $pass_email, $pass_phone);
    my $rider_list;

    $sql = "SELECT  rider, email, phone_number FROM passengers WHERE car_id_" . $up_or_down . " = $car_id";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    $sth->bind_columns(undef, \$passenger, \$pass_email, \$pass_phone);
    while($sth->fetch){
	$rider_list .= "\t$passenger ($pass_phone) <$pass_email>\n";
    }
    return $rider_list;
}

sub delete_from_car {
    my $posted = shift;
    my $car_id = $posted->param('car_id');
    my $pass_id = $posted->param('pass_id');
    my $up_or_down = $posted->param('trip');
    
    
    print $posted->h1("Are You Sure?");
    print "<P>Are you sure you want to remove yourself from this car?  If not, press the back button now.  click 'continue.' if you are sure.</P>";
    print $posted->start_form( -method=>'POST',
			       -action=>'skipool.cgi');
    $posted->param('action', 'delete_from_car2');
    print $posted->hidden('action');
    $posted->param('car_id', $car_id);
    print $posted->hidden('car_id');
    $posted->param('pass_id', $pass_id);
    print $posted->hidden('pass_id');
    $posted->param('trip', $up_or_down);
    print $posted->hidden('trip');
    print "<P>Please give me a password: \n";
    print $posted->password_field( -name=>'password',
			      -size=>40);
    print "<P><EM>It may take a little time, but please press the continue button only once.</EM>";
    print $posted->submit('button', 'Continue');
    print $posted->end_form;
}

sub delete_from_car2 {    
    my $posted = shift;
    my $car_id = $posted->param('car_id');
    my $pass_id = $posted->param('pass_id');
    my $up_or_down = $posted->param('trip');
    my $password = $posted->param('password');
    my ($sql, $sth, $dbh);
    my ($encrypted_pass, $passenger_database_pass, $driver_database_pass);
    my $open_spots;
    my ($passenger, $passenger_email, $driver, $driver_email, $driver_phone_number);
    my %mail;
    my ($demail_retval, $pemail_retval);
    
    $dbh = &Ski::DB::get_handle();
    $sql = "SELECT open_spots_" . $up_or_down . ", driver, email, password, phone_number FROM cars WHERE car_id = $car_id";
    print "<P>My sql statement is \"$sql\"</P>" if $DEBUGGING;
    $sth = $dbh->prepare($sql);
    $sth->execute();
    $sth->bind_columns(undef, \$open_spots, \$driver, \$driver_email, \$driver_database_pass, \$driver_phone_number);
    $sth->fetch();
    $sql = "SELECT rider, email, password FROM passengers WHERE pass_id = $pass_id";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    $sth->bind_columns(undef, \$passenger, \$passenger_email, \$passenger_database_pass);
    $sth->fetch();
    $open_spots++;

    ### verify password
    $encrypted_pass = &Digest::SHA1::sha1_base64($password);
    if(($encrypted_pass ne $passenger_database_pass) and ($encrypted_pass ne $driver_database_pass)){
	#password failed.
	print "<h1><font color=\"#FF0000\">Bad Password</font></h1>\n";
	print "<p>I'm sorry, but the password you entered does not belong to
either the driver or the passenger.  Passengers can only remove themselves,
and drivers can only remove passengers in their own car.</p>";
	print "<a href=\"http://skipool.hartshorne.net/reset_password.html\">Forgot your password?</a>\n";
	return;
    }
    $sql = "UPDATE cars SET open_spots_" . $up_or_down . " = $open_spots WHERE car_id = $car_id";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    $sql = "UPDATE passengers SET car_id_" . $up_or_down . " = '' WHERE pass_id = $pass_id";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    
    print $posted->h2("Removed");
    print "<P>$passenger has been removed from " . $driver . "'s car for the trip " . $up_or_down . ".\n</p>";
    # send an email to driver to tell them
    %mail = (
	    To		=> "$driver <$driver_email>",
	    From	=> "Skipool <skipool\@green.hartshorne.net>",
	    Subject	=> "Passenger removed: $passenger",
	    Body	=> "Hi $driver,\n\t$passenger has been removed from your car for the trip " . $up_or_down . ".  Your current list of passengers is:\n" . &get_formatted_rider_list($dbh, $car_id, $up_or_down) . " \nYou can send mail to everyone in your car at tahoecar-" . $car_id . "\@skipool.hartshorne.net.  Check out \nhttp://skipool.hartshorne.net/ if you have any questions.  You now \nhave $open_spots seats available.\n\nThanks for playing!\n\n-ben\n"
    );
    $demail_retval = sendmail(%mail);
    if($demail_retval){
	    print "I have send $driver an email saying that you have decided not to ride with him or her.<p>\n";
    }else{
	    print "I tried to send $driver an email saying that you have decided not to ride with him or \n";
	    print "her, but I failed.  Would you take care of that for me?  $driver can be reached at \n";
	    print "$driver_email or $driver_phone_number.\n</p>";
    }
    %mail = (
	    To		=> "$passenger <$passenger_email>",
	    From	=> "Skipool <skipool\@green.hartshorne.net>",
	    Subject	=> "Passenger removed: $passenger",
	    Body	=> "Hi $passenger,\n\tYou have been removed from " . $driver . "'s car.  Check out \nhttp://skipool.hartshorne.net/ if you have any questions.\n\nThanks for playing!\n\n-ben\n"
    );
    $pemail_retval = sendmail(%mail);
    if($pemail_retval){
	print "I have send $passenger an email saying that you have decided not to ride with $driver.<p>\n";
    }else{
	print "I have failed to send $passenger an email confirming this removal.  Sorry!<p>\n";
    }
    
    print "<p>I'm sorry you won't be joining us this time, but thanks for playing!</P>\n";
    print "<P><A HREF=\"http://skipool.hartshorne.net/\">Go Back Home</A></P>";
    &Ski::DB::return_handle($dbh);
}

sub register_car_submit {
    my $posted = shift;
    my ($car_id, $driver, $phone_number, $email, $password, $car_type, $open_spots, $rtd, $rtr, $ski_rack, $fourwd, $chains, $stops, $music, $notes, $origin, $destination);
    my ($new_pass, $encrypted_pass, $database_pass);
    my ($dbh, $sql, $sth);
    my $form_ok = 'true';
    $car_id = $posted->param('car_id');
    $driver = $posted->param('driver');
    $phone_number = $posted->param('phone_number');
    print "snagged phone: is >> $phone_number << <br>\n" if $DEBUGGING;
    $email = $posted->param('email');
    $password = $posted->param('password');
    $new_pass = $posted->param('change_password');
    $car_type = $posted->param('car_type');
    $open_spots = $posted->param('open_spots');
    $ski_rack = $posted->param('ski_rack');
    $fourwd = $posted->param('fourwd');
    $chains = $posted->param('chains');
    $stops = $posted->param('stops');
    $music = $posted->param('music');
    $notes = $posted->param('notes');
    $rtd = $posted->param('rtd');
    $rtr = $posted->param('rtr');
    $origin = $posted->param('origin');
    $destination = $posted->param('destination');
    $submit_button = '<input type="submit" name="Submit" value="This is correct">';

    $rtd = &ParseDate($rtd); #to get it to a nicely computer-understood format
    $rtr = &ParseDate($rtr); #this is actually the way it will be stored...
    print "interpreted  rtd is >> $rtd << <br>\n" if $DEBUGGING;
    print "interpreted  rtr is >> $rtr << <br>\n" if $DEBUGGING;

    $rtd = &Date::Manip::UnixDate($rtd, '%a %b %e %Y, at %I:%M %p.'); #nicely human-understood format
    $rtr = &Date::Manip::UnixDate($rtr, '%a %b %e %Y, at %I:%M %p.'); # just for printing
    print "printing  rtd is >> $rtd << <br>\n" if $DEBUGGING;
    print "printing  rtr is >> $rtr << <br>\n" if $DEBUGGING;

    print $posted->h2("Driver Confirmation");

    if($car_id){ #if this is updating a car, car_id is defined
	$dbh = &Ski::DB::get_handle();
	$sql = "SELECT password FROM cars WHERE car_id = $car_id";
	print "sql statament: >>> $sql <<< <br>" if $DEBUGGING;
	$sth = $dbh->prepare($sql);
	$sth->execute();
	$sth->bind_columns(undef, \$database_pass);
	$sth->fetch();
	&Ski::DB::return_handle($dbh);
	$encrypted_pass = &Digest::SHA1::sha1_base64($password);
	unless($encrypted_pass eq $database_pass){
	    print "<h2 align=\"center\"><font color=\"#ff0000\">I'm sorry your password was not valid.  Please go back and try again.</font></h2>";
	    print "password hashed is >> $encrypted_pass << database has >> $database_pass <<<br>\n" if $DEBUGGING;
	    print qq{<p><a href="reset_password.html">Forgot your password?</a></p>};
	    print $posted->hr({-width=>"90%", -height=>"5"});
	}
    }
		
    
    if(!$rtr){
	    $rtr = qq{<font color="#FF0000">I'm sorry, your date could not be understood.  Try adding the year.</font>};
	    $form_ok = 'false';
	    $submit_button="<h1>Go Back!</h1>";
    }
    if(!$rtd){
	    $rtd = qq{<font color="#FF0000">I'm sorry, your date could not be understood.  Try adding the year.</font>};
	    $submit_button="<h1>Go Back!</h1>";
    }
    if($car_type =~ "\""){
	    $car_type = qq{<font color="#FF0000">I'm sorry, but double quotes ("") are not allowed.  Please remove them and resubmit.</font>};
	    $submit_button = "<h1>Go Back!</h1>";
    }
    if($notes =~ "\""){
	    $notes = qq{<font color="#FF0000">I'm sorry, but double quotes ("") are not allowed.  Please remove them and resubmit.</font>};
	    $submit_button = "<h1>Go Back!</h1>";
    }
    
    # insert test to see if origin and destination are kosher

    
    print <<END_DRIVE_CONF_A;
<p>Is all this information correct?</p>
<h3>You:</h3>
<p>Name: <b>$driver</b><br>
  Phone: <b>$phone_number</b><br>
  Email: <b>$email</b><br>
  Password: <b>xxxxxxx</b><br>
END_DRIVE_CONF_A
    if($new_pass){
	print(qq{<font color="#FF0000">Your old and new passwords are accepted; when you click submit, your password will be changed.</font><br>});
    }
    print <<END_DRIVE_CONF_B;
<p>
<h3>Your Car:</h3>
  
<p>Description: <b>$car_type</b><br>
  Passengers: <b>$open_spots</b><br>
  Ski Rack: <b>$ski_rack</b><br>
  4WD: <b>$fourwd</b><br>
  Chains: <b>$chains</b><br>
  Stops: <b>$stops</b><br>
  Music: <b>$music</b><br>
  Notes: <b>$notes</b><br>
  Origin: <b>$origin</b><br>
  Destination: <b>$destination</b><br>
</p>
<h3>Timing:</h3>
<p>You plan on leaving the bay area: <b>$rtd</b><br>
  You plan on coming back: <b>$rtr</b></p>
END_DRIVE_CONF_B
    if($form_ok eq "true"){
	 print <<END_DRIVE_CONF2;
<p>If any of this is incorrect, please click your browsers Back button and resubmit.</p>
<form name="form1" method="post" action="skipool.cgi">
  <input type="hidden" name="action" value="register_car_verified">
  <input type="hidden" name="car_id" value="$car_id">
  <input type="hidden" name="driver" value="$driver">
  <input type="hidden" name="phone_number" value="$phone_number">
  <input type="hidden" name="email" value="$email">
  <input type="hidden" name="password" value="$password">
  <input type="hidden" name="change_password" value="$new_pass">
  <input type="hidden" name="car_type" value="$car_type">
  <input type="hidden" name="open_spots" value="$open_spots">
  <input type="hidden" name="ski_rack" value="$ski_rack">
  <input type="hidden" name="fourwd" value="$fourwd">
  <input type="hidden" name="chains" value="$chains">
  <input type="hidden" name="stops" value="$stops">
  <input type="hidden" name="music" value="$music">
  <input type="hidden" name="notes" value="$notes">
  <input type="hidden" name="rtd" value="$rtd">
  <input type="hidden" name="rtr" value="$rtr">
  <input type="hidden" name="origin" value="$origin">
  <input type="hidden" name="destination" value="$destination">
  $submit_button
END_DRIVE_CONF2
    }else { print "<hr><h3>Some of this cannot be entered as it is.</h3>  <p>Please click your browser's Back button and resubmit.</p>"; }
    print <<END_DRIVE_CONF3;
</form>
<p>&nbsp;</p>
END_DRIVE_CONF3
}

sub register_car_verified {
    my $posted = shift;
    my $car_id;
    my ($driver, $phone_number, $email, $password, $new_pass, $car_type, $open_spots, $rtd, $rtr, $ski_rack, $fourwd, $chains, $stops, $music, $notes, $origin, $destination);
    my ($encrypted_pass, $database_pass, $encrypted_new_pass);
    my ($dbh, $sql, $sth);
    my %email;
    my $retval;
    
    $car_id = $posted->param('car_id');
    $driver = $posted->param('driver');
    $phone_number = $posted->param('phone_number');
    $email = $posted->param('email');
    $password = $posted->param('password');
    $new_pass = $posted->param('change_password');
    $car_type = $posted->param('car_type');
    $open_spots = $posted->param('open_spots');
    $ski_rack = $posted->param('ski_rack');
    $fourwd = $posted->param('fourwd');
    $chains = $posted->param('chains');
    $stops = $posted->param('stops');
    $music = $posted->param('music');
    $notes = $posted->param('notes');
    $rtd = $posted->param('rtd');
    $rtr = $posted->param('rtr');
    $origin = $posted->param('origin');
    $destination = $posted->param('destination');
    $rtd = &Date::Manip::ParseDate($rtd);
    $rtr = &Date::Manip::ParseDate($rtr);
    $encrypted_pass = &Digest::SHA1::sha1_base64($password);
    print "encrypted_pass = $encrypted_pass\n" if $DEBUGGING;
    
    $dbh = &Ski::DB::get_handle();
    if($car_id){
	$sql = "SELECT password FROM cars WHERE car_id = $car_id";
	print "sql statament: >>> $sql <<< <br>" if $DEBUGGING;
	$sth = $dbh->prepare($sql);
	$sth->execute();
	$sth->bind_columns(undef, \$database_pass);
	$sth->fetch();
	unless($encrypted_pass eq $database_pass){
	    #bad password
	    #redirect to bad password page
	    return;
	}
	if($new_pass){
	    #if the user wants to changed their password
	    $encrypted_new_pass = &Digest::SHA1::sha1_base64($new_pass);
	    $pass_string = "password = " . $dbh->quote($encrypted_new_pass) . "," ;
	}else{
	    $pass_string = "";
	}
	$sql = "UPDATE cars SET driver = " . $dbh->quote($driver) . ", " .
	  "phone_number = " . $dbh->quote($phone_number) . ',' . 
	  "email = " . $dbh->quote($email) . ',' . 
	  $pass_string .
	  "car_type = " .  $dbh->quote($car_type) . ',' . 
	  "open_spots_up = " . $dbh->quote($open_spots) . ',' . 
	  "open_spots_down = " . $dbh->quote($open_spots) . ',' . 
	  "open_spots_capacity = " . $dbh->quote($open_spots) . ',' . 
	  "ski_rack = " . $dbh->quote($ski_rack) . ',' . 
	  "fourwd = " . $dbh->quote($fourwd) . ',' . 
	  "chains = " . $dbh->quote($chains) . ',' . 
	  "stops = " . $dbh->quote($stops) . ',' . 
	  "music = " . $dbh->quote($music) . ',' . 
	  "notes = " . $dbh->quote($notes) . ',' . 
	  "requested_time_departure = " . $dbh->quote($rtd) . ',' . 
	  "requested_time_return = " . $dbh->quote($rtr) . ',' .
	  "origin = " . $dbh->quote($origin) . ',' . 
	  "destination = " . $dbh->quote($destination) . ' ' . 
	  "WHERE car_id = " . $dbh->quote($car_id);
    }else{
      $sql = "INSERT INTO cars (driver, phone_number, email, password, car_type, open_spots_up, open_spots_down, open_spots_capacity,  ski_rack, fourwd, chains, stops, music, notes, requested_time_departure, requested_time_return, origin, destination) VALUES (" . 
  	$dbh->quote($driver) . ',' . 
	$dbh->quote($phone_number) . ',' . 
	$dbh->quote($email) . ',' . 
	$dbh->quote($encrypted_pass) . ',' . 
	$dbh->quote($car_type) . ',' . 
	$dbh->quote($open_spots) . ',' . 
	$dbh->quote($open_spots) . ',' . 
	$dbh->quote($open_spots) . ',' . 
	$dbh->quote($ski_rack) . ',' . 
	$dbh->quote($fourwd) . ',' . 
	$dbh->quote($chains) . ',' . 
	$dbh->quote($stops) . ',' . 
	$dbh->quote($music) . ',' . 
	$dbh->quote($notes) . ',' . 
	$dbh->quote($rtd) . ',' . 
	$dbh->quote($rtr) . ',' . 
	$dbh->quote($origin) . ',' . 
	$dbh->quote($destination) . ')';
  }
    print "sql statament: >>> $sql <<< <br>" if $DEBUGGING;
    $sth = $dbh->prepare($sql);
    $sth->execute();
    $sql = "SELECT car_id FROM cars WHERE phone_number = " . $dbh->quote($phone_number) . " AND password = " . $dbh->quote($encrypted_password);
    $sth = $dbh->prepare($sql);  
    $sth->execute();
    $sth->bind_columns(undef, \$car_id);
    $sth->fetch();
    &Ski::DB::return_handle($dbh);

  $rtd = &Date::Manip::UnixDate($rtd, '%a %b %e at %I:%M %p'); #nicely human-understood format
  $rtr = &Date::Manip::UnixDate($rtr, '%a %b %e at %I:%M %p'); # just for printing
  %mail = (
	   To      => "newcar\@hartshorne.net",
	   From    => "Skipool <skipool\@green.hartshorne.net>",
	   Subject => $driver . "'s driving up to Tahoe!",
	   Body    => "Hey you environmentally conscious snow addict!

I thought you might be interested that $driver is driving up to Tahoe
on $rtd, and coming back on $rtr.
Head over to http://skipool.hartshorne.net if you want to join this
noble endeavor!

Details:
driver:      $driver
car:         $car_type
seats:       $open_spots
skirack:     $ski_rack
fourwd:      $fourwd
leaving:     $rtd
returning:   $rtr
origin:      $origin
destination: $destination
notes:       $notes



Send mail to skipool\@green.hartshorne.net if you have any questions
(or, for some strange reason, don't want to get this email anymore...)

-ben\n"
	   );
  $retval = sendmail(%mail);

  print "<h2>Thanks for registering!</h2>";
  print "<p>retval from sendmail = $retval error =  $Mail::Sendmail::error log =  $Mail::Sendmail::log  \n</p>" if $DEBUGGING;
  print "<P>If you need to contact your passengers, you can send mail to tahoecar-" . $car_id . "\@skipool.hartshorne.net and it will be sent to everyone in your car (and you).</p>\n";
  print 'You can <a href="skipool.cgi?action=display_items">verify your car was added</a> or go back to <a href="http://skipool.hartshorne.net">the front page</a>.';
}