I am working on a Joomla website where I have been using the Fabrik extension to create a mySQL database. The database consists of different users and different contracts that pays a daily fixed amount per contract. Each user can own several contracts and I need a script to update their balance according to how many contracts they own and what that contract pays. I also need a log where each payment is saved.
I have four tables:
-Usertable, consisting of the following columns:
id - date_time - address - userid (links the user to the Joomla user) - balance
-Contracts, consisting of the following columns:
id - date_time - contract - dailypayout
-UserContract, consisting of the following columns:
id - date_time - contractlink (linking to contract in the contracts table) - userlink (linking to user in the usertable) - number_of_contracts
-Log, consisting of the following columns:
id - date_time - userid - contract - payout
Basically, the UserContract table tells how many of each contract each user owns. How much each contract pays can be found in the contracts table.
What I need is a script that can run through the UserContract table. For each row it has to update the balance in the usertable according to how many of a contract the user owns and what that contract pays. In addition there needs to be created an entry in the Log table, with the userid, what contract was paid and how much was paid in total for this contract.
I will try to make this clearer with an example:
Contract A pays 5 per contract
Contract B pays 2 per contract
User 63 owns 3 contract A
User 63 owns 10 contract B
User 64 owns 10 contract A
When the script runs it needs to:
Add 15 to user 63's balance (for owning 3 contract A that pays 5)
Create a row in the log table with: user: 63, contract A, +15
Add 20 to user 63's balance (for owning 10 contract B that pays 2)
Create a row in the log table with: user: 63, contract B, +20
Add 50 to user 64's balance (for owning 10 contract A that pays 5)
Create a row in the log table with: user: 64, contract B, +50
Hope I have explained what I need in a decent way. As I said I will be using Joomla to design the site so it would be best if you could write this script so it can be used in the Fabrik extension or as a joomla extension. PM me if you think you can help me out.