#!/usr/local/bin/perl

# This file contains perl subroutines that retrieve data
# for the CGI calls from the member user sections of the 
# ProMEM/MealSaver website.
#
# All subroutines take a DBI handle for their last
# argument if one is open. Otherwise pass undef.
#
# All code written and created by Dan Strife.

use DBI;
require "mysql.pl";


# Takes a member netID and DBI handle. Returns array of
# member info for user netID.

sub getMemberInfo
{
	my $dbh;
	my $sth;
	my $query;
	my $dis;

	($dbh, $dis) = handleMySQLConnect($_[$#_]);

	my $netid = $_[0];
		
	# query club tables for netid
	$query = "SELECT LNAME, FNAME, MI, CLUB FROM Members WHERE (NETID='$netid')";
	$sth = $dbh->prepare($query);
	$sth->execute();
	
	my @info = $sth->fetchrow_array();
	if ($info[2] eq "") {
	    $info[2] = "_";
	}
	
	$sth->finish();
	
	handleMySQLDisconnect($dbh, $dis);

	return @info;
}


# Takes a member netID and DBI handle. Returns array of
# member options for user netID. 

sub getMemberOptions
{
	my $dbh;
	my $sth;
	my $query;
	my $dis;

	($dbh, $dis) = handleMySQLConnect($_[$#_]);
	
	my $netid = $_[0];
	
	# query club table for name
	$query = "SELECT REMAIL, BEMAIL FROM Members WHERE NETID='$netid'";
	
	$sth = $dbh->prepare($query);
	$sth->execute();	
	my @opts = $sth->fetchrow_array();
	$sth->finish();
	
	handleMySQLDisconnect($dbh, $dis);
	
	return @opts;
}


# Takes a hash of CGI variables for member options and DBI
# handle and updates the MySQL database.

sub putMemberOptions
{
	my $dbh;
	my $sth;
	my $query;
	my $dis;

	($dbh, $dis) = handleMySQLConnect($_[$#_]);
		
	# pull hash from reference argument
	my $ref = $_[0];
	my %params = %$ref;	

	# update club table with new option values
	$query = "UPDATE Members SET REMAIL='$params{\"remail\"}', BEMAIL='$params{\"bemail\"}' WHERE NETID='$params{\"netid\"}'";
	
	$sth = $dbh->prepare($query);
	$sth->execute();
	$sth->finish();
	
	handleMySQLDisconnect($dbh, $dis);
}


# Takes a member netid and DBI handle. Returns array of
# arrays including user info and member exchange records 
# grouped by unbalanced exchages, balanced exchanges,
# and guest meals.

sub getMemberBalance
{
	my $dbh;
	my $hsth;
	my $gsth;
	my $query;
	my $dis;

	($dbh, $dis) = handleMySQLConnect($_[$#_]);
		
	my $netid = $_[0];
		
	# query exchanges where member was host
	$query = "SELECT HOSTID, GUESTID, DATE, TYPE, MEAL FROM Exchanges WHERE (HOSTID='$netid') AND (TYPE='exchange') ORDER BY GUESTID, MEAL ASC";
	$hsth = $dbh->prepare($query);
	$hsth->execute();

	# query exchanges where member was guest
	$query = "SELECT HOSTID, GUESTID, DATE, TYPE, MEAL FROM Exchanges WHERE (GUESTID='$netid') AND (TYPE='exchange') ORDER BY HOSTID, MEAL ASC";
	$gsth = $dbh->prepare($query);
	$gsth->execute();

	my @grec = $gsth->fetchrow_array();
	my @hrec = $hsth->fetchrow_array();
	
	# build array of exchange records
	my @uxch;
	my @bxch;
	my @gxch;
	my @info;
	my $ucount = 0;
	my $bcount = 0;
	my $gcount = 0;
	while (@grec && @hrec) {
		if ($#grec == 4) {
			@info = getMemberInfo($grec[0], $dbh);
			@grec = (@grec, @info);
		}
		if ($#hrec == 4) {
			@info = getMemberInfo($hrec[1], $dbh);
			@hrec = (@hrec, @info);
		}
		if ($grec[3] ne 'exchange') {
			$gxch[$gcount++] = [ @grec ];
			@grec = $gsth->fetchrow_array();
		} elsif ($hrec[3] ne 'exchange') {
			$gxch[$gcount++] = [ @hrec ];
			@hrec = $hsth->fetchrow_array();
		} elsif ($grec[0] lt $hrec[1]) {
			$uxch[$ucount++] = [ @grec ];
			@grec = $gsth->fetchrow_array();
		} elsif ($hrec[1] lt $grec[0]) {
			$uxch[$ucount++] = [ @hrec ];
			@hrec = $hsth->fetchrow_array();
		} else {
			if ($grec[3] lt $hrec[3]) {
				$uxch[$ucount++] = [ @grec ];
				@grec = $gsth->fetchrow_array();
			} elsif ($hrec[3] lt $grec[3]) {
				$uxch[$ucount++] = [ @hrec ];
				@hrec = $hsth->fetchrow_array();				
			} else {
				$bxch[$bcount++] = [ @grec ];
				$bxch[$bcount++] = [ @hrec ];
				@grec = $gsth->fetchrow_array();
				@hrec = $hsth->fetchrow_array();
			}
		}
	}
	while (@grec) {
		if ($#grec == 4) {
			@info = getMemberInfo($grec[0], $dbh);
			@grec = (@grec, @info);
		}
		if ($grec[3] ne 'exchange') {
			$gxch[$gcount++] = [ @grec ];
		} else {
			$uxch[$ucount++] = [ @grec ];
		}
		@grec = $gsth->fetchrow_array();
	}
	while (@hrec) {
		if ($#hrec == 4) {
			@info = getMemberInfo($hrec[1], $dbh);
			@hrec = (@hrec, @info);
		}
		if ($hrec[3] ne 'exchange') {
			$gxch[$gcount++] = [ @hrec ];
		} else {
			$uxch[$ucount++] = [ @hrec ];
		}
		@hrec = $hsth->fetchrow_array();
	}	
	
	@info = getMemberInfo($netid, $dbh);
	
	$hsth->finish();
	$gsth->finish();
	
	handleMySQLDisconnect($dbh, $dis);
	
	my @xch;
	$xch[0] = [ @info ];
	$xch[1] = [ @uxch ];
	$xch[2] = [ @bxch ];
	$xch[3] = [ @gxch ];

	return @xch;
}

1;