I have a script which currently "works" but is nearly 3600 lines of code and makes well over 50 database calls within a single script. From my experience, there is no way to really "loop" the script and minimize it because each call to the database is a subquery of the ones before based on referral ids.
Perhaps I can give a very simple example of what I am trying to accomplish and see if anybody has experience with something similar.
In my example, there are three tables:
Table 1 - Sellers
ID | Comm_level | Parent
-----------------------------------
1 | 4 | NULL
2 | 3 | 1
3 | 2 | 1
4 | 2 | 2
5 | 2 | 2
6 | 1 | 3
Where ID is the id of one of our sales agents, comm_level will determine what his commission percentage is for each product he sells, parent indicates the ID for whom recruited that particular agent.
In the example above, 1 is the top agent, he recruited two agents, 2 and 3. 2 recruited two agents, 4 and 5. 3 recruited one agent, 6. NOTE: An agent can NEVER recruit anybody equal to or higher than their own level.
Table 2 - Commissions
Level | Item 1 | Item 2 | Item 3
-----------------------------------------------------
4 | .5 | .4 | .3
3 | .45 | .35 | .25
2 | .4 | .3 | .2
1 | .35 | .25 | .15
This table lays out the commission percentages for each agent based on their actual comm_level (if an agent is at a level 4, he will receive 50% on every item 1 sold, 40% on every item 2, 30% on every item 3 and so on.
Table 3 - Items Sold
ID | Item
---------------------
4 | item_1
4 | item_2
1 | item_1
2 | item_3
6 | item_2
1 | item_3
This table pairs the actual item sold with the seller who sold the item.
When generating the commission report, calculating individual values is very simple. Calculating their commission based on their sub_sellers however is very difficult.
In this example, Seller ID 1 gets a piece of every single item sold. The commission percentages indicate individual sales or the height of their commission.
For example:
When seller ID 6 sold one of item_2 above, the tree for commissions will look like the following:
-ID 6 - 25% of cost(item_1)
-ID 3 - 5% of cost(item_1) - (30% is his comm - 25% comm of seller id 6)
-ID 1 - 10% of cost(item_1) - (40% is his comm - 30% of seller id 3)
This must be calculated for every agent in the system from the top down (hence the DB calls within while loops throughout my enormous script).
Anybody have a good suggestion or samples they may have used in the past?
