#!/usr/local/bin/perl

# This file contains perl subroutines that retrieve data
# for the CGI calls from the club 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";
require "member.pl";


# Takes a club netID and DBI handle. Returns full
# club name.

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

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

	my $netid = $_[0];
	
	# query club table for name
	$query = "SELECT CLUB FROM Clubs WHERE NETID='$netid'";

	$sth = $dbh->prepare($query);
	$sth->execute();
	
	# assign fields to variables
	$sth->bind_columns(\$club);
	
	$sth->fetch();
	$sth->finish();
	
	handleMySQLDisconnect($dbh, $dis);

	return $club;
}


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

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

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

	my $netid = $_[0];
	
	# query club table for name
	$query = "SELECT GMEALS, BPRICE, LPRICE, DPRICE, EMAIL FROM Clubs 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 club options and DBI
# handle and updates the MySQL database.

sub putClubOptions
{
	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 Clubs SET GMEALS='$params{\"gmeals\"}', BPRICE='$params{\"bprice\"}', LPRICE='$params{\"lprice\"}', DPRICE='$params{\"dprice\"}', EMAIL='$params{\"email\"}' WHERE NETID='$params{\"netid\"}'";
	
	$sth = $dbh->prepare($query);
	$sth->execute();
	$sth->finish();
	
	handleMySQLDisconnect($dbh, $dis);
}


# Takes a club netID and DBI handle. Returns array of
# arrays of club member records for user netID.

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

	($dbh, $dis) = handleMySQLConnect($_[$#_]);
	
	my $club = getClubName($_[0], undef);
	
	# query club table for member list
	$query = "SELECT NETID, LNAME, FNAME, MI, YEAR FROM Members WHERE ClUB='$club' ORDER BY YEAR, LNAME";
	$sth = $dbh->prepare($query);
	$sth->execute();

	my $members = $sth->fetchall_arrayref();

    foreach my $row (@$members) {
        if ($row->[3] eq "") {
        	$row->[3] = "_";	
    	}
    }

	$sth->finish();

	handleMySQLDisconnect($dbh, $dis);
	
	return $members;
}


# Takes a hash of CGI variables for new club members and
# DBI handle and updates the MySQL database. Returns array
# of arrays including member records that could not be
# inserted due to netID duplications.

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

	($dbh, $dis) = handleMySQLConnect($_[$#_]);
	
	my $ref = $_[0];
	my %params = %$ref;
	
	my $club = getClubName($params{"netid"}, undef);

    my $time = getTime();
	
	$query = "INSERT INTO Members VALUES ";
	
	for $i (0...keys(%params)/5-1){
		if ($i == 0) {
			$query .= "('$params{\"lname$i\"}','$params{\"fname$i\"}','$params{\"mi$i\"}','$params{\"netid$i\"}','$club',$params{\"year$i\"},0,'Y','Y','$time')";
		} else {
			$query .= ", ('$params{\"lname$i\"}','$params{\"fname$i\"}','$params{\"mi$i\"}','$params{\"netid$i\"}','$club',$params{\"year$i\"},0,'Y','Y','$time')";
		}
	}

	$sth = $dbh->prepare($query);
	$sth->execute();
	$sth->finish();
	
	@del = cleanseMySQLMembers($dbh);
		
	handleMySQLDisconnect($dbh, $dis);
	
	return @del;
}


# Takes a club netid and DBI handle. Returns array of
# arrays including club name and unbalanced club member
# exchange records.

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

	($dbh, $dis) = handleMySQLConnect($_[$#_]);
	
	my $club = getClubName($_[0], undef);
	
	# query club table for member list
	$query = "SELECT NETID FROM Members WHERE ClUB='$club' ORDER BY LNAME";
	$sth = $dbh->prepare($query);
	$sth->execute();

	my $mxch;
	my $minfo;
	my @netid;
	my @member;
	my @uxch;
	my $n;
	my $m;
	my $j = 0;
	my $count;

	# retrieve member exchanges
	while(@netid = $sth->fetchrow_array()) {
		@member = getMemberBalance($netid[0], $dbh);
		$minfo = $member[0];
		$mxch = $member[1];
		
		for $rec ( @$mxch ) {			
			#skip if not hosted at club
			next if ($rec->[0] ne $netid[0]);
			
			#append host and guest info to record
			@$rec = (@$rec[0...3], @$minfo[0...2], @$rec[5...$#$rec]);
			$uxch[$j++] = [ @$rec ];
		}
	}	
	
	$sth->finish();

	handleMySQLDisconnect($dbh, $dis);
	
	my @xch;
	$xch[0] = $club;
	$xch[1] = [ @uxch ];
	
	return @xch;
}


# Takes a hash of CGI variables for new club exchanges and
# DBI handle and updates the MySQL database. Returns array
# of arrays including exchange records that could not be
# inserted due to conflicts.

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

	($dbh, $dis) = handleMySQLConnect($_[$#_]);
	
	my $ref = $_[0];
	my %params = %$ref;	
	
	$query = "INSERT INTO Exchanges VALUES ";
	
	my $time = getTime();
	
	for $i (0...keys(%params)/5-1){
		if ($i == 0) {
			$query .= "('$params{\"hostid$i\"}','$params{\"guestid$i\"}','$params{\"date$i\"}','$params{\"type$i\"}','$params{\"meal$i\"}','$time')";
		} else {
			$query .= ", ('$params{\"hostid$i\"}','$params{\"guestid$i\"}','$params{\"date$i\"}','$params{\"type$i\"}','$params{\"meal$i\"}','$time')";
		}
	}
	
	$sth = $dbh->prepare($query);
	$sth->execute();
	$sth->finish();
	
	@del = cleanseMySQLExchanges($dbh);
	
	handleMySQLDisconnect($dbh, $dis);
	
	return @del;
}

1;