Having Admin wallet display when user logs in

Question

User-1994446809 posted

I have two tables in my project - Signup and User wallet tables; the sign up table contains login data and the user wallet contains monetary data of the Admin. There are two users who will be granted access to the site (Admin and Users). The Admin is created from the start, the “user” is being created by the Admin. After Admin Registers and logs in, the Admin ID and wallet balance is displayed on the web forms of the site. Then Admin will then proceed to the dashboard to create new users the team; after new user is created it displays in the gridview based on the Admin who created the user(s).

Now I want a situation where a user will log in and since the user is linked to an Admin, the Admin ID and wallet balance who created that user, will be displayed except in the user’s case, the user will not be able to see the link that navigates to the dashboard and will also not be able to see Deposit button which is on the navbar, only an Admin can see those. However, the user can use paid services or perform activities under the Admin who created the user, thereby effecting changes in the admin’s wallet balance. Here is the structure of my tables:

1. SIGN UP TABLE

Key: UserType refers to the type of user on the site (A = Admin and U = User)

USERWALLET TABLE

How should I code this please? That will call Admin “userwallet” when user logs in based on the admin who created the user. 

Thank you

Answer 1

User-939850651 posted

Hi georgeakpan233,

When the user logs in, you could check whether the user is linked to an Admin.

If linked, the page will show the panel including the Admin ID and wallet balance,etc.

If not linked, then the role is an Admin, the page will show the panel including the link that navigates to the dashboard and other content.

You could use the Visible property to control whether to display the panel.

Best regards,

Xudong Peng

Answer 2

User-1994446809 posted

If linked, the page will show the panel including the Admin ID and wallet balance,etc.

This is where I have an issue; how do I link the user to the wallet Table where the Admin ID and wallet is?

Answer 3

User475983607 posted

This is where I have an issue; how do I link the user to the wallet Table where the Admin ID and wallet is?

The data shown above is a simple list of records with Admin and Users types.  A one-to-many relationship is required to relate User to Admin.  One solution is to add another column, "Admin", to the table.  Place the AdminId in each User record to join the User to the Admin. The admin will have a null value.  

Answer 4

User-1994446809 posted

Hi mgebhard,

mgebhard

One solution is to add another column, "Admin", to the table.  Place the AdminId in each User record to join the User to the Admin. The admin will have a null value.  

If you checked the first image table there is a column named "CreatedBy" that is where the AdminId is and each user has AdminId related to it. But where the AdminID and wallet balance is displayed from, is from another table named "UserWallet", so is there any code that will be written to link user and Admin ID and its wallet balance ? such that when a user logs in the wallet account of the Admin (which is in another table) will be linked to the user who logs in, and will be displayed.

Answer 5

User475983607 posted

I'm confused.  Your question amounts to how to get the CreatedBy value from the SignUp table?  Get the value when the user logs in.  Use the email get the value from the UserWallet table.

SELECT CreatedBy
FROM SignUp
WHERE email = @email and pass = @pass

Another option is getting the amount from the UserWallet table using a join and the user's email address.  

SELECT amount
FROM UserWallet AS w
    INNER JOIN SignUp AS s ON w.email = s.CreatedBy
WHERE s.email = @email

IMHO, the design is very confusing.  

Answer 6

User-1994446809 posted

Thanks mgebhard,

IMHO, the design is very confusing.  

I dont know, maybe this explanation will be clearer:

For example, if an Admin ([e-mail]) registers as new Admin and logs in, the ID and wallet balance of Admin ([e-mail]) will display. Then Admin ([e-mail]) proceeds to dashboard and creates new user ([e-mail]). The user ([e-mail]) will be under the Admin ([e-mail]).

So what I want achieve is when the user ([e-mail]) logs in, the ID and wallet balance of Admin ([e-mail]) will be displayed and the user will not be able to do some of the things the Admin does, which are see deposit button to make deposit or see dashboard link too. But user can be able to use funds in the Admin's wallet and Admin can know if funds has been used.

Please what does the "w", "s", "w.email" and "s.CreatedBy"  mean? do I have to declare them?

Answer 7

User379720387 posted

Abbreviations

FROM UserWallet AS w 

or you can type

FROM UserWallet w

Now w = UserWallet

Answer 8

User379720387 posted

I would advise against using email addresses as primary keys.

your SignUp table should look like this

userId int PK (primary key)

email

pass 

con-pass

Name

UserType

CreatedBy int 

CreatedDate

then you Wallet table like this:

walletId int PK

userId int FK (foreign key)

Balance

The relationship is:

If the user is of Type A then there is 1 wallet

If the user is of Type U then there is no wallet.

A wallet (Type A user) can be seen by multiple Type U users

The query to get the wallet for a Type U user is then:

SELECT walletId, Balance, email, UserType, CreatedBy

FROM Wallet w

JOIN SignUp s ON w.userId = s.CreatedBy

WHERE email = 'the login email from the Type U user'

Answer 9

User475983607 posted

So what I want achieve is when the user ([e-mail]) logs in, the ID and wallet balance of Admin ([e-mail]) will be displayed

I provided a query that gets the admin wallet amount using a user's email address.  Of course this only works for a User account because the CreatedBy is null on admin accounts.

SELECT amount
FROM #UserWallet AS w
    INNER JOIN #Signup AS s ON w.email = s.CreatedBy
WHERE s.email = '[e-mail]'

With your current configuration, if the login is an Admin then you'll get the amount from the WalletTable. 

DECLARE @userType VARCHAR(1)
DECLARE @email VARCHAR(64) = '[e-mail]'
SELECT @userType = s.UserType FROM #Signup AS s WHERE s.email = @email

IF(@userType = 'U')
BEGIN
	SELECT amount
	FROM #UserWallet AS w
		INNER JOIN #Signup AS s ON w.email = s.CreatedBy
	WHERE s.email = @email
END
ELSE
BEGIN
	SELECT amount FROM #UserWallet AS w WHERE w.email = @email
END

Another option is to set the CreatedBy to the admin's email rather than NULL

and the user will not be able to do some of the things the Admin does, which are see deposit button to make deposit or see dashboard link too. But user can be able to use funds in the Admin's wallet and Admin can know if funds has been used.

This is code that you have to write.  The community cannot comment on a "good" approach because we have no idea how you custom security works.  It's confusing why you are unable to identity a user or admin.

IMHO, the design presented is very poor and why you are having trouble.  I recommend using the security API, Identity, that comes with ASP.NET rather than building your own if you do not have the experience.

Please what does the "w", "s", "w.email" and "s.CreatedBy"  mean? do I have to declare them?

They are standard SQL aliases so you are not forced to type the entire table name.  

Answer 10

User-1994446809 posted
Hi mgebhard,
What's the best design? How would you design yours, not to make it look poor?

Answer 11

User-1994446809 posted

then you Wallet table like this:

walletId int PK

userId int FK (foreign key)

Balance

If I dont have email column in wallet table, how will be able to display email along with wallet balance ? 

Answer 12

User379720387 posted

email comes from SignUp table.

I could have written s.email, but since there is only one email I do not have to use "s."

Answer 13

User-1994446809 posted

your SignUp table should look like this

userId int PK (primary key)

email

pass 

con-pass

Name

UserType

CreatedBy int 

CreatedDate

then you Wallet table like this:

walletId int PK

userId int FK (foreign key)

Balance

How will you insert userId into another column of the same table. The column is highlighted in yellow area, and also insert it into wallet table (also highlighetd in yellow)?

Answer 14

User379720387 posted

When you create the SignUp record you ask for the userId that was just created

then:

if (usertype == "A")
{
    int newuserid = db.GetLastInsertId();

    db.Execute("INSERT INTO Wallet (UserId, Wallet) VALUES (@0, @1)", newuserid, wallet);
}

Also, make sure all your tables have a primary key, and set it to identity.

https://www.youtube.com/watch?v=HbjcoC7gLEU

Answer 15

User-1994446809 posted
This is for wallet; how about the column "CreatedBy" in Sign up Table?
Besides, those parameters (@0, @1), newuserid, wallet).can you explain better?

Answer 16

User379720387 posted

I answered the first question in a different thread. My suggestion was that you call it primaryUserId. Look at that again.

Those parameters are necessary to prevent SQL injection attacks. You can look up that term and get it better explained than I can.

Answer 17

User-1994446809 posted
Which thread please?

Answer 18

User379720387 posted

I answered that in one of your threads....

Answer 19

User-1994446809 posted
Honestly, this is the first time I am seeing this. I have not seen a situation where userId is inserted into two separate columns of the same table (UserId, CreatedBy)

Answer 20

User379720387 posted

Look at the last one in this thread.

UserType A:

only has the UserId

UserType U:

has its own UserId

AND

the UserId of the Admin who invited this user

Answer 21

User475983607 posted

Honestly, this is the first time I am seeing this. I have not seen a situation where userId is inserted into two separate columns of the same table (UserId, CreatedBy)

I recommend using ASP.NET Identity to handle user account management.   Assign Users and Administrators roles.   Use claims to designate the User's Wallet Id.  Craft a table or update the user table to store the admin Id.

All this information will follow the user after a successful login which should make your business logic much easier to implement.

https://docs.microsoft.com/en-us/aspnet/identity/overview/getting-started/introduction-to-aspnet-identity

https://docs.microsoft.com/en-us/aspnet/core/security/authentication/identity?view=aspnetcore-3.1&tabs=visual-studio

Answer 22

User-1994446809 posted
Hi wavemaster,
I checked the thread and found nothing on how to insert data into the "CreatedBy" column in the Sign up table. What I found was how query on how get data from wallet table and display. You states that when Admin (user A) signs up, the A user has only UserId and when user (user U) is created the user has its own UserId... Allnthus is about the wallet table, how about "CreatedBy" column of the Sign up table?

Answer 23

User379720387 posted

Let me clarify

UserType A:

has one record in SignUp, Created is NULL

AND

has one record in Wallet, with UserId set to the UserId from SignUp

UserType U:

has one record in SignUp, Created is set to the UserId of the Type A user who invited that user

this UserType has no Wallet record of its own

SignUp:
userId	usertype	created
1	A	null
2	U	1
3	U	1
4	A	null
5	U	4
Wallet
walletId	userId	balance
1	1	200
2	4	500

Answer 24

User-1994446809 posted
Understood. Thanks.
I still have two related issues;
1. There's a red line error in "db.GetLastInsertId" and "db.Execute".

2. When creating Usertype U, how will the parameter for Usertype A who invited the user be written?
Or will I have to create a textbox to input UserId for user A when creating user U?

Answer 25

User379720387 posted

Has "db" been defined in your application?

The userId is generated when you entered a record in SignUp, provided you set that column to "identity". That way SQL will generate a unique userId.

When user logs in you query the db to see if there is a value in Created.

If there is, you know:

1. this is a type U user
2. what the primaryUserId is (i.e. Created) 
3. the walletId 

That primaryUserId needs to travel with a type U user throughout your code, and it will drive the logic of what you are going to show where and what you are going to do at that point.

I would set a flag, for instance isAdmin (true is type A user) (false is type U user). This works as long as you have two types of users.

Early on in your threads you mentioned UserType A would invite "U" type users.

Answer 26

User-1994446809 posted
Defined? As int?
Or how is it defined?

Answer 27

User475983607 posted

The design does not meet the requirements.  Also the requirements are not well defined which adds to the confusion.  

IMHO, you should separate account management with the Wallet.  Use ASP.NET Identity to manage account as recommended above.  This will solve a lot of logical design issues you are currently facing.

It is not clear how an Admin is selected but I'll assume that at least one admin account exists in the database.  The admin adds user email addresses associated with the admin to a table with AdminId, Email, and UserId columns.  The user registration process checks this table for the email address.  If the email address exits then the record is updated with the userId created in the registration process.  This joins the User and Admin accounts.  

Answer 28

User379720387 posted

Is your application connected to the database?

Something like:

var db = Database.Open("yourdbname")

Answer 29

User-1994446809 posted

Mgebhard,<br>
In a design, the designer knows what is required to achieve the desired goal; so you cannot possibly know if the does not meet the requirements. there's room to think out of the box in order to achieve the goal; to do or try out what maybe has not been done
before. So I tend to ask these questions in order to use the lines of codes to achieve my goal regarding what I'm building. I guess if you see the diagram in this regard, you will know what I mean.
I'm not trying to state here that you're wrong..on the contrary your contributions are standard practice and very very knowledgeable. But I'm trying to achieve something I drafted down

Answer 30

User-1994446809 posted
My application is connected to the database but still got red error line under "db"

Answer 31

User379720387 posted

How do you know it is connected and how did you initialize the connection in your app?

Answer 32

User-1994446809 posted

The connection is initialized through its connection string. Besides, without it being connected, an error would have been shown when trying to insert a record

Answer 33

User379720387 posted

Please share the code so we do not have to speculate about what you are doing.