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;
}