-- =============================================

-- Create CheckEmail procedure

-- =============================================

-- Check if thers exists a same Email when user tries

-- to change his email address.

-- @ifexist return a integer number greater than 0

-- when there exists same Email, otherwise

-- return 0.

CREATE PROCEDURE CheckEmail

   @LoginName varchar(20),

   @Email varchar(50),

   @IfExists INT OUTPUT

AS

   SELECT @IfExists = 0

   SELECT @IfExists = COUNT(LoginName) FROM UserInfo WHERE LoginName <> @LoginName and Email = @Email

GO

-- =============================================

-- Create CheckLicense procedure

-- =============================================

-- Check if there the consumer provides valid license key

-- If license key is valid, then return an integer number greater

-- than 0, otherwise return 0.

CREATE PROCEDURE CheckLicense

   @LicenseNo varchar(50),

   @IfExists INT OUTPUT

AS

   SELECT @IfExists = 0

   SELECT @IfExists = COUNT(CompanyName) FROM License WHERE CAST(LicenseNo AS VARCHAR(50)) = @LicenseNo

GO

-- =============================================

-- Create DeleteCard procedure

-- =============================================

-- Delete credit card by given cardid

CREATE PROCEDURE DeleteCard

   @CardId int

AS

   DELETE CardInfo

   WHERE id = @CardId

GO

-- =============================================

-- Create DeleteUser procedure

-- =============================================

-- Delete a user from database by given user login name

CREATE PROCEDURE DeleteUser

   @LoginName varchar(20)

AS

   DELETE CardInfo

   WHERE (LoginName = @LoginName)

   DELETE UserInfo

   WHERE (LoginName = @LoginName)

GO

-- =============================================

-- Create procedure GetCreditCard

-- =============================================

-- Get a user's all credit card information by given the user' login name

CREATE PROCEDURE GetCreditCard

   @Loginname varchar(20)

AS

   SELECT CardInfo.id AS CardId, CardAG.fullcardtype AS CardType,

     CAST(MONTH(Expiration) AS VARCHAR(2)) + '/' + CAST(YEAR(Expiration) AS VARCHAR(4)) AS Expiration,

         CardInfo.CardNo, CardInfo.HolderName

   FROM CardInfo INNER JOIN

         CardAG ON CardInfo.CardType = CardAG.cardtype

   WHERE CardInfo.LoginName = @Loginname

GO

-- =============================================

-- Create GetLicense procedure

-- =============================================

-- Check if there exists a match with the consumer's name, return

-- empty string if exists the match. Otherwise insert the consumer's

-- name into database, generate and return an unique license key

-- to consumer

CREATE PROCEDURE GetLicense

   @CompanyName varchar(50),

   @LicenseNo varchar(50) OUTPUT

AS

BEGIN

   IF((SELECT COUNT(CompanyName) FROM License

   WHERE CompanyName = UPPER(@CompanyName)) = 0)

     BEGIN

     INSERT INTO License

     (CompanyName)

     VALUES (UPPER(@CompanyName))

     SELECT @LicenseNo = CAST(LicenseNo AS VARCHAR(50)) FROM License

     WHERE CompanyName = @CompanyName

     END

   ELSE

     SELECT @LicenseNo = ''

END

GO

-- =============================================

-- Create procedure GetUserInfo

-- =============================================

-- Get user's information includes user name, address and contact

-- information by given account name and password

CREATE PROCEDURE GetUserInfo

   @LoginName varchar(20),

   @LoginPass varchar(20)

AS

   SELECT Email, FirstName, MiddleName, LastName, Street, City, State,

   Zip, Phone, Mobile

   FROM UserInfo

   WHERE LoginName = @LoginName and LoginPass = @LoginPass

GO

-- =============================================

-- Create InsertCard procedure

-- =============================================

-- Insert a credit card's information into database

CREATE PROCEDURE InsertCard

   @LoginName varchar(20),

   @CardType char(1),

   @CardNo varchar(50),

   @Expiration datetime,

   @HolderName varchar(20)

AS

   INSERT INTO CardInfo (LoginName, CardType, CardNo, Expiration, HolderName)

   VALUES (@LoginName, @CardType, @CardNo, @Expiration, @HolderName)

GO

-- =============================================

-- Create InsertUser procedure

-- =============================================

-- Create an new user account and insert all user's information.

CREATE PROCEDURE InsertUser

   @LoginName varchar(20),

   @LoginPass varchar(20),

   @Email varchar(50),

   @FirstName varchar(20),

   @MiddleName varchar(20),

   @LastName varchar(20),

   @Street varchar(50),

   @City varchar(20),

   @State char(2),

   @Zip char(5),

   @Phone varchar(15),

   @Mobile varchar(15)

AS

   INSERT INTO UserInfo (LoginName, LoginPass, Email, RegDate, FirstName, MiddleName,

   LastName, Street, City, State, Zip, Phone, Mobile)

   VALUES (@LoginName, @LoginPass, @Email, GETDATE(), @FirstName, @MiddleName,

   @LastName, @Street, @City, @State, @Zip, @Phone, @Mobile)

GO

-- =============================================

-- Create InsertUserAccount procedure

-- =============================================

-- Create an user account

CREATE PROCEDURE InsertUserAccount

   @LoginName varchar(20),

   @LoginPass varchar(20)

AS

   INSERT INTO UserInfo (LoginName, LoginPass, RegDate)

   VALUES (@LoginName, @LoginPass, GETDATE())

GO

-- =============================================

-- Create UniqueEmail procedure

-- =============================================

-- Check if thers exists a same Email when user is creating an

-- new account.

-- Return an integer number greater than 0 when there exists

-- same Email address, otherwise return 0.

CREATE PROCEDURE UniqueEmail

   @Email varchar(50),

   @IfExists INT OUTPUT

AS

   SELECT @IfExists = 0

   SELECT @IfExists = COUNT(LoginName) FROM UserInfo WHERE Email = @Email

GO

-- =============================================

-- Create UniqueLoginName procedure

-- =============================================

-- Check if thers exists a same LoginName.

-- Return an integer number greater than 0 when find a match,

-- otherwise return 0.

CREATE PROCEDURE UniqueLoginName

   @LoginName varchar(20),

   @IfExists INT OUTPUT

AS

   SELECT @IfExists = 0

   SELECT @IfExists = COUNT(LoginName) FROM UserInfo WHERE LoginName = @LoginName

GO

-- =============================================

-- Create procedure InsertUserInfo

-- =============================================

-- Update an user's information

CREATE PROCEDURE InsertUserInfo

   @LoginName varchar(20),

   @Email varchar(50),

   @FirstName varchar(20),

   @MiddleName varchar(20),

   @LastName varchar(20),

   @Street varchar(50),

   @City varchar(20),

   @State char(2),

   @Zip char(5),

   @Phone varchar(15),

   @Mobile varchar(15)

AS

   UPDATE UserInfo

   SET Email = @Email, FirstName = @FirstName, MiddleName = @MiddleName,

   LastName = @LastName, Street = @Street, City = @City,

   State = UPPER(@State), Zip = @Zip, Phone = @Phone, Mobile = @Mobile

   WHERE LoginName = @LoginName

-- =============================================

-- Create procedure UpdateUserPass

-- =============================================

-- Change user password

CREATE PROCEDURE UpdateUserPass

   @LoginName varchar(20),

   @LoginPass varchar(20)

AS

   UPDATE UserInfo

   SET LoginPass = @LoginPass

   WHERE LoginName = @LoginName

GO

-- =============================================

-- Create VerifyAccount procedure

-- =============================================

-- Check if thers exists a same LoginName.

-- Return an integer number greater than 0

-- when find a match, otherwise return 0.

CREATE PROCEDURE VerifyAccount

   @LoginName varchar(20),

   @LoginPass varchar(20),

   @IfExists INT OUTPUT

AS

   SELECT @IfExists = 0

   SELECT @IfExists = COUNT(LoginName) FROM UserInfo WHERE LoginName = @LoginName and LoginPass = @LoginPass

GO

AddCreditCard Method:

Add a new credit card for a given account.

public string AddCreditCard(

   string licenseno,

   string loginname,

   string cardtype,

   string cardno,

   string expiration,

   string holdername

);

Parameters

licenseno

The specified license key to access UPI web method.

loginname

The user name of the account to add a credit card.

cardtype

The credit card type. Take "A" as "American Express", "D" as "Discovery", "M" as "Master", "V" as "Visa".

cardno

The credit card number.

expiration

The expiration date of the credit card. Take a formatted string as "MM/YY" or "MM/YYYY".

holdername

The credit card's holder name.

Return Value

A blank string if the new credit card added successfully, otherwise return a string contains the error message.

Example

The following example demonstrates how to add a credit card for the account whose user name is  "dragonfly". The credit card's card type is "Master", card number is "1234567890123456", expiration date is "Augest/2005", holder name is "Tom White". The license key is "09CEFFC3-7118-4E98-8F73-2B85C7D5F293".

UPIService.CreateUserAccount("09CEFFC3-7118-4E98-8F73-2B85C7D5F293", "dragonfly", "M", "1234567890123456","08/05","Tom White");

CreateUserAccount Method:

Create a new user account by given user name and password.

public string CreateUserAccount(

   string licenseno,

   string loginname,

   string loginpass 

);

Parameters

licenseno

The specified license key to access UPI web method.

loginname

The user name of the account to be created.

loginpass

The password of the account to be created.

Return Value

A blank string if the new account created successfully, otherwise return a string contains the error message.

Example

The following example demonstrates how to add a new user account which takes "dragonfly" as user name and "time2000" as password. The license key is "09CEFFC3-7118-4E98-8F73-2B85C7D5F293".

UPIService.CreateUserAccount("09CEFFC3-7118-4E98-8F73-2B85C7D5F293", "dragonfly", "time2000");

DeleteCreditCard Method:

Delete a specified credit card from database.

public string DeleteCreditCard(

   string licenseno,

   string cardid 

);

Parameters

licenseno

The specified license key to access UPI web method.

cardid

The id number corresponding to the credit card to be deleted.

Return Value

A blank string if the credit card deleted successfulyl, otherwise return a string contains the error message.

Remark

Use GetCreditCart method to get a user's credit card id.

Example

The following example demonstrates how to delete a credit card from database. In this sample, the credit card id is "30". The license key is "09CEFFC3-7118-4E98-8F73-2B85C7D5F293".

UPIService.CreateUserAccount("09CEFFC3-7118-4E98-8F73-2B85C7D5F293", "30");

DeleteUserAccount Method:

Delete an user account by given user name and password.

public string DeleteUserAccount(

   string licenseno,

   string loginname,

   string loginpass 

);

Parameters

licenseno

The specified license key to access UPI web method.

loginname

The user name of the account to be deleted.

loginpass

The password of the account to be deleted.

Return Value

A blank string if the account deleted successfully, otherwise return a string contains the error message, .

Example

The following example demonstrates how to delete an user account which takes "dragonfly" as user name and "time2000" as password. The license key is "09CEFFC3-7118-4E98-8F73-2B85C7D5F293".

UPIService.DeleteUserAccount("09CEFFC3-7118-4E98-8F73-2B85C7D5F293", "dragonfly", "time2000");

GetCreditCard Method:

Get all credit cards information of given user.

public DataSet GetCreditCard(

   string licenseno,

   string loginname 

);

Parameters

licenseno

The specified license key to access UPI web method.

loginname

The given user name to get his or her credit cards information. 

Return Value

A DataSet which contains all credit cards information of the given user.

Remark

The datafields in the dataset are "Card id", "Card Type", "Card Number", "Expiration Date", "Holder Name" in sequence.  In C#, use following code to get each field's content:

DataRow row1 = dataset1.Tables[0].Rows[0];

string cardid = row1["id"].ToString();

string cardtype = row1["CardType"].ToString();

string cardnumber = row1["CardNo"].ToString();

string expiration = row1["Expiration"].ToString();

string holdername = row1["HolderName"].ToString();

Example

 [Visual Basic, C#]The following example demonstrates how to get all credit cards information of an user whose user name is "dragonfly". The license key is "09CEFFC3-7118-4E98-8F73-2B85C7D5F293".

UPIService.CreateUserAccount("09CEFFC3-7118-4E98-8F73-2B85C7D5F293", "dragonfly");

GetUserInfo Method:

Create a user's infomation by given user name and password.

public DataSet GetUserInfo(

   string licenseno,

   string loginname,

   string loginpass 

);

Parameters

licenseno

The specified license key to access UPI web method.

loginname

The user name of the user to get his/her information.

loginpass

The password of the user to get his/her information.

Return Value

A DataSet contains user's information.

Remark

The datafields in the dataset are "E-mail address", "First Name", "Middle Name", "Last Name", "Street", "City", "State", "Zip", "Phone Number", "Mobile Number" in sequence.  In C#, use following code to get each field's content:

DataRow row1 = dataset1.Tables[0].Rows[0];

string email = row1["Email"].ToString();

string firstname = row1["FirstName"].ToString();

string lastname = row1["LastName"].ToString();

string middlename = row1["MiddleName"].ToString();

string street = row1["Street"].ToString();

string city = row1["City"].ToString();

string state = row1["State"].ToString();

string zip = row1["Zip"].ToString();

string phone = row1["Phone"].ToString();

string mobile = row1["Mobile"].ToString();

Example

The following example demonstrates how to add a new user account which takes "dragonfly" as user name and "time2000" as password. The license key is "09CEFFC3-7118-4E98-8F73-2B85C7D5F293".

UPIService.CreateUserAccount("09CEFFC3-7118-4E98-8F73-2B85C7D5F293", "dragonfly", "time2000");

UpdatePass Method:

Update a user's password.

public string UpdatePass(

   string licenseno,

   string loginname,

   string loginpass 

);

Parameters

licenseno

The specified license key to access UPI web method.

loginname

The user name of the account to be updated password.

loginpass

The password string to be updated.

Return Value

A blank string if the new password updated successfully, otherwise return a string contains the error message.

Example

The following example demonstrates how to update a new user's password. In this example the user name is "dragonfly" and new password is "time2000". The license key is "09CEFFC3-7118-4E98-8F73-2B85C7D5F293".

UPIService.CreateUserAccount("09CEFFC3-7118-4E98-8F73-2B85C7D5F293", "dragonfly", "time2000");

UpdateUserInfo Method:

Update a user's information.

public string UpdateUserInfoCard(

   string licenseno,

   string loginname,

   string email,

   string firstname,

   string middlename,

   string lastname,

   string street,

   string city,

   string state,

   string zip,

   string phone,

   string mobile

);

Parameters

licenseno

The specified license key to access UPI web method.

loginname

The user name of the account to be updated his/her information.

email

E-mail address.

firstname

The user's first name.

middlename

The user's middle name.

lastname

The user's last name.

street

Street of user' address.

city

City of user's address.

state

State of user's address, only abbreviation accepted. For example, only accept "CT" for "Connecticut".

zip

Zip code of user's address.

phone

User' phone number.

mobile

User's mobile number.

Return Value

A blank string if the user's information updated successfully, otherwise return a string contains the error message.

Example

The following example demonstrates how to update a user's information whose user name is "dragonfly", email address is "dragonfly@msn.com", real name is "Tom White", address is "682 Atlantic street, Bridgeport, CT, 06604", phone number is "203-3326180". The license key is "09CEFFC3-7118-4E98-8F73-2B85C7D5F293".

UPIService.UpdateUserInfo("09CEFFC3-7118-4E98-8F73-2B85C7D5F293", "dragonfly", "dragonfly@msn.com", "Tom", "", "White", "682 Atlantic street","Bridgeport","CT","06604","2033326180");

Name:

register.ascx:

used for to create an new user account.

Screenshot:

Arguments:

AddUserInfoUrl:

The relative URL of the page which contains the userinfo user control. After user clicked the "Submit" button, the page contains the register user control will be redirected to the URL which specified by the AddUserInfoUrl string.

Name:

login.ascx

used for an personal user login to access his or her information.

Screenshot:

Arguments:

ModifyUserInfoUrl:

The relative URL of the page which contains the userinfo user control. After user clicked the "Submit" button, the page contains the login user control will be redirected to the URL which specified by the ModifyUserInfoUrl string.

AddNewUserUrl:

The relative URL of the page which contains the register user control. After user clicked the "New User? " link, the page contains the login user control will be redirected to the URL which specified by the AddNewUserUrl string.

Name:

userinfo.ascx

used for personal user modifying his/her information.

Screenshot:

Arguments:

ChangeCreditcardUrl:

The relative URL of the page which contains the changecreditcard user control. After user clicked the "Add/Remove Credit Card" link, the page contains the userinfo user control will be redirected to the URL which specified by the ChangeCreditcardUrl string.

ChangePassUrl:

The relative URL of the page which contains the changepassword user control. After user clicked the "Change Password" link, the page contains the userinfo user control will be redirected to the URL which specified by the ChangePassUrl string.

SignoutUrl:

The relative URL of the page which will be shown after user sign out. After user clicked the "Sign Out" button, the page contains the userinfo user control will be redirected to the URL which specified by the SignoutUrl string.

Name:

changecreditcard.ascx

used for personal user add or delete his/her credit card information

Screenshot:

Arguments:

ChangeUserInfoUrl:

The relative URL of the page which contains the userinfo user control. After user clicked the "Change user information" link, the page contains the changecreditcard user control will be redirected to the URL which specified by the ChangeUserInfoUrl string.

SignoutUrl:

The relative URL of the page which will be shown after user sign out. After user clicked the "Sign Out" button, the page contains the changecreditcard user control will be redirected to the URL which specified by the SignoutUrl string.

Name:

changepassword.ascx

allow user change his/her login password.

Screenshot:

Arguments:

ChangeUserInfoUrl:

The relative URL of the page which contains the userinfo user control. After user clicked the "Change user information" link, the page contains the changepassword user control will be redirected to the URL which specified by the ChangeUserInfoUrl string.

SignoutUrl:

The relative URL of the page which will be shown after user sign out. After user clicked the "Sign Out" button, the page contains the changepassword user control will be redirected to the URL which specified by the SignoutUrl string.

Name:

deleteuser.ascx

allow user cancel their UPI account.

Screenshot:

Arguments:

(None)