UDB.pm: User Database API

package UDB;

require "/usr/local/bfd/lib/SQL.pl";

$ENV{'SYBASE'} = "/usr/local/sybase";

my $data_source = "dbi:Sybase:server=UDB;database=UDB";
my $database_username = "udb";
my $database_password = "udbpass";

# Initialize DBI
#
sub BEGIN
{
 use DBI;
}

# Log in to database
#
sub Login
{
 &login($data_source,$database_username,$database_password);
}

# Log out of database
#
sub Logout
{
 &logout;
}

# Add a new user
#
sub AddUser
{
 my($package,$username,$realname,$phone,$plan) = @_;

 # Insert the information into the Users table.
 my $sql = "
	insert Users (UserName,RealName,PhoneNumber,BillingPlan)
	values ('$username','$realname','$phone,$plan)
	";
 my $UID = &sql_identity($sql);

 if($UID == 0)
 {
  # Failed for some reason.
  return 0;
 }

 # Log that we created this user.
 $package->Log($UID,'Account Created',0);

 # Return the new ID.
 return $UID;
}
 
# Get list of users
#
sub GetUsers
{
 my $sql = "select UserName from Users";

 return &sql_select($sql);
}

# Get the information on a user
#
sub GetUserInfo
{
 my($package,$username) = @_;

 my $sql = "
	select RealName,PhoneNumber,BillingPlan,AccountBalance,Hours
	from Users where UserName = '$username'
	";

 my $result = &sql_select($sql);
 my ($realname,$phone,$plan,$balance) = &sql_split($result);
 return ($username,$realname,$phone,$plan,$balance);
}

# Get the UID for a username
#
sub GetUID
{
 my($package,$username) = @_;

 my $sql = "
	select UID from Users where UserName = '$username'
	";
 return &sql_select($sql);
}

# Modify a user.
#
sub ModifyUser
{
 my($package,$username,$realname,$phone,$plan) = @_;
 my $sql = "
	update	Users
	set	RealName = '$realname',
		PhoneNumber = '$phone',
		BillingPlan = $plan
	where	UserName = '$username'
	";
 my $status = &sql_update($sql);
 return $status;
}

# Delete a user.  Note that this removes all traces of them from the
# database.  Usually you will use SuspendUser or something like that
# to deactivate an account.
#
sub DeleteUser
{
 my($package,$username) = @_;
 my $status = 0;

 my $sql = "
	select ID from Users where UserName = '$username'
	";
 my $UID = &sql_select($sql);

 if($UID == 0)
 {
  # Can't find this username.
  return 0;
 }

 # Error checking?  Transactions?  Roll-backs?
 $sql = "
	delete from History where UID = $UID
	";
 $status = &sql_update($sql);

 $sql = "
	delete from Users where UID = $UID
	";
 $status = &sql_update($sql);

 return $status;
}
 
# Add a new Billing Plan
#
sub AddBillingPlan
{
 my($package,$name,$baserate,$basehours,$hourlyrate) = @_;

 my $sql = "
	insert BillingPlans (Name,BaseRate,BaseHours,HourlyRate)
	values ('$name',$baserate,$basehours,$hourlyrate)
	";
 my $BID = &sql_identity($sql);

 return $BID;
}

# Get the particulars of a Billing Plan
#
sub GetBillingPlanDetails
{
 my($package,$name) = @_;

 my $sql = "
	select BaseRate,BaseHours,HourlyRate
	from BillingPlans
	where Name = '$name'
	";
 return &split(&sql_select($sql));
}

# Update an existing billing plan
#
sub ModifyBillingPlan
{
 my($package,$name,$baserate,$basehours,$hourlyrate) = @_;

 my $sql = "
	update BillingPlans
	set BaseRate = $baserate,
	    BaseHours = $basehours,
	    HourlyRate = $hourlyrate
	where Name = '$name'
	";
 my $status = &sql_update($sql);
 return $status;
}

# Change the name of an existing billing plan
#
sub ChangeBillingPlanName
{
 my($package,$oldname,$newname) = @_;

 my $sql = "
	update BillingPlans
	set Name = '$newname'
	where Name = '$oldname'
	";
 return &sql_update($sql);
}

# List Billing Plans
#
sub GetBillingPlans
{
 my $sql = "select Name from BillingPlans";
 return &sql_select($sql);
}

# Apply a payment to an account
#
sub ApplyCredit
{
 my($package,$user,$description,$amount) = @_;

 if($amount < 0)
 {
  # Whoa, Nelly!  This is set up to only take positive numbers to avoid
  # confusion.
  return 0;
 }
 $package->InsertTransaction($user,$description,$amount);
}

# Apply a bill to an account
#
sub ApplyDebit
{
 my($package,$user,$description,$amount) = @_;
 
 if($amount < 0)
 {
  # Whoa, Nelly!  This is set up to only take positive numbers to avoid
  # confusion.
  return 0;
 }
 $package->InsertTransaction($user,$description,-$amount);
}

# Insert a transaction.  Shouldn't really be called directly...
#
sub InsertTransaction
{
 my($package,$user,$description,$amount) = @_;

 my $UID = $package->GetUID($user);

 my $sql = "
	update Users set AccountBalance = AccountBalance - $amount
	where UID = $UID
	";
 my $status = &sql_update($sql);

 return 0 unless $status;

 # Save a record of this in the history.
 $package->Log($UID,$description,$amount);

 # Successful!
 return 1;
}
 
# Create a log entry in the History table
#
sub Log
{
 my($package,$UID,$transaction,$amount) = @_;

 my $sql = "
	insert History (UID,Date,Transaction,Amount)
	values ($UID,getdate(),'$transaction',$amount)
	";
 my $ID = &sql_identity($sql);

 # Return the new ID.
 return $ID;
}