SQL query For client Credits

[kaspana]

Is there any easy way thru SQL query to mass add credits to clients based on product? i mean if a client have a product named "host1" the credits will be 10 euros.
 

[brian!]

On 22/08/2020 at 17:47, kaspana said:

Is there any easy way thru SQL query to mass add credits to clients based on product? i mean if a client have a product named "host1" the credits will be 10 euros.
 

what happens if they have more than one "host1" product - is it €10 credit per client or per product ?

also, if you're using multiple currencies, then the query will have to use other tables to get the current exchange rate to calculate the equivalent of €10 - unless you're going to run the query multiple times for each currency.

ultimately, you're just adding a value to the 'credit' column value in the tblclients table, and you'd probably have to use a product ID (number) rather than a name because you could in theory have products with the same name.

[Kian]

And you're also creating money out of nothing. What about accounting?

[brian!]

Just now, Kian said:

And you're also creating money out of nothing. What about accounting?

I could literally hear you saying that in my head as I was typing the reply yesterday. 😀

[kaspana]

On 24/08/2020 at 5:42 PM, brian! said:

what happens if they have more than one "host1" product - is it €10 credit per client or per product ?

also, if you're using multiple currencies, then the query will have to use other tables to get the current exchange rate to calculate the equivalent of €10 - unless you're going to run the query multiple times for each currency.

ultimately, you're just adding a value to the 'credit' column value in the tblclients table, and you'd probably have to use a product ID (number) rather than a name because you could in theory have products with the same name.

It is €10 credit per product name "host1" per client ( 5 products named "host1" €50 for the client ), i have one currencies

16 hours ago, Kian said:

And you're also creating money out of nothing. What about accounting?

It is for downtime Credits.

[baymax]

Isn't easier to create and provide a coupon code to the clients that experienced the downtime?

Anyway, the below might help. The other more advanced users may do it differently though.

To find out the total of a specific product each Client has:

select userid as `User ID`, packageid as `Package ID`, count(tblhosting.packageid) as `Total #` from tblhosting where packageid='1' group by tblhosting.userid

Once you have a list of how many of a specific package each user has, you can go to tblcredit table and add the credits in manually (assuming you don't have TOO many). If there's a lot, hopefully someone else can chime in...