How to calculate commision based on referred memebrs?

Question

Hello everybody, I am developing a small software in C# WPF for a consultancy which does chain system business. I have coded tree structure to show who referred whom. Now it has commission depending on level. If 1 referred 2 & 3 then 1 will get level-1 commission. If 2 referred 4, 5 & 3 referred 6, 7 then 1 will receive level-2 commission. This chain will go on to certain total number. My problem is how I would implement this logic; I am able to calculate who has referred how many members via UDF written for adding TreeViewItem to TreeView. Or tell me how I can count items in treeview in certain level? Node adding UDF:

I am using MySQL and it has table members which consists columns- id, fullname, refercode.

public void AddNodes(int uid, TreeViewItem tSubNode)
    {
        string query = "select fullname, id from members where refCode=" + uid + ";";
        MySqlCommand cmd = new MySqlCommand(query, db.conn);
        MySqlDataAdapter _DA = new MySqlDataAdapter(cmd);
        DataTable _DT = new DataTable();
        tSubNode.IsExpanded = true;
        _DA.Fill(_DT);

        foreach (DataRow _dr in _DT.Rows)
        {
            TreeViewItem tNode = new TreeViewItem();                
            tNode.Header = _dr["fullname"].ToString()+" ("+_dr["id"].ToString()+")";                
            tSubNode.Items.Add(tNode);

            if (db.HasMembers(Convert.ToInt32(_dr["id"].ToString())))
            {
                AddNodes(Convert.ToInt32(_dr["id"]), tNode);
            }
        }
        //This line tracks who has referred how many members
        Console.WriteLine("Tree node Count : "+tSubNode.Items.Count.ToString()+", UID "+uid);                       
    }

Help me PLEASE!!!!

Answer 1

Calculating this sort of thing by counting nodes in a treeview is pretty silly, you will be doing the calculation after binding it to a UI element.

In this particular case, i'm also not a fan of doing it using LINQ, while LINQ will do it very well, it has the following drawbacks:

• you should let the database server do the hard work where possible, it is what it is for
• if levels are added or altered at some stage in the future, it means you have several more locations that need refactoring
• it can be easier to roll out a change to a database stored proc than a change to compiled code

You haven't specified what database you are using, i am going to assume SQL. You should easily be able to calculate this if you have the names of the people and a key to who referred them in a self referencing table in the database.

Your table will look like this:

int             RecordID
varchar(100)    Name
int             ReferrerID

Then for the data it will look like this (using your examples from above):

_______________________________________________
| RecordID |  Name              |  ReferrerID |
_______________________________________________
|        1 | Person 1           |       null  | 
|        2 | Person 2           |          1  |
|        3 | Person 3           |          1  | 
|        4 | Person 4           |          2  |
|        5 | Person 5           |          2  |
|        6 | Person 6           |          3  |
|        7 | Person 7           |          3  |
_______________________________________________

I am also going to use a table called Levels containing the commission amount for each level. In this table i am assigning 0.50 for each Level 1 referral, and 0.25 for each Level 2 referral:

int    LevelID
money  LevelAmount  

Now using your self referencing table, you can calculate what you need for level 1 and level 2 by joining it on itself twice. This sql will give you the initial table:

SELECT   p.RecordID
        ,p.PersonName
        ,Level1Referral.PersonName
        ,Level1Commission.LevelAmount           
        ,Level2Referral.PersonName
        ,Level2Commission.LevelAmount
FROM People p
    LEFT JOIN People Level1Referral
    ON Level1Referral.ReferrerID = p.RecordID
    LEFT JOIN Levels Level1Commission
        ON Level1Referral.ReferrerID IS NOT NULL 
                AND Level1Commission.LevelID = 1
    LEFT JOIN People Level2Referral
    ON Level2Referral.ReferrerID = Level1Referral.RecordID 
    LEFT JOIN Levels Level2Commission
        ON Level2Referral.ReferrerID IS NOT NULL 
                AND Level2Commission.LevelID = 2

All i do is join the table on itself for each possible commission level, if you were to introduce a commission for 3rd level referrals then you would just add another join.

If you run this statement, you will notice though how the Level1Referral.PersonName has a duplicated entry for each Level 2 referral, this is not optimal and is not easy to factor out with grouping. What we can do however is have a surrounding SELECT statement and apply some grouping to that, and in the process calculate the amount of commission for each level. Here is the final SQL statement that does what you need:

SELECT CommissionEarnerID
    ,CommissionEarnerName
    ,COUNT(DISTINCT L1Referral)         AS [Number Of L1 Referrals]
    ,COUNT(DISTINCT L1Referral) 
        * (SELECT LevelAmount 
           FROM   Levels 
           WHERE LevelID = 1)           AS [Level 1 Commission]
    ,COUNT(DISTINCT L2Referral)         AS [Number Of L2 Referrals]
    ,COUNT(DISTINCT L2Referral)
        * (SELECT LevelAmount 
           FROM Levels 
           WHERE LevelID = 2)           AS [Level 2 Commission]
FROM (

    SELECT   p.RecordID                 AS CommissionEarnerID
            ,p.PersonName               AS CommissionEarnerName
            ,Level1Referral.RecordID    AS L1Referral
            ,Level1Referral.PersonName  AS L1ReferralName
            ,Level2Referral.RecordID    AS L2Referral
            ,Level2Referral.PersonName  AS L2ReferralName
    FROM People p
        LEFT JOIN People Level1Referral
            ON Level1Referral.ReferrerID = p.RecordID

        LEFT JOIN People Level2Referral
            ON Level2Referral.ReferrerID = Level1Referral.RecordID 
      ) x 
GROUP BY     CommissionEarnerID
            ,CommissionEarnerName             

I tidied the statement up a little, and put in a few column names. This produces a resultset that looks like this:

_____________________________________________________________________________
| Commission | Commission | Number    | Level 1    | Number    | Level 2    |
| Earner ID  | Earner     | of L1     | Commission | of L2     | Commission |
|            | Name       | Referrals |            | Referrals |            |
_____________________________________________________________________________
|  1         | Person 1   |  2        | 1.00       |  4        | 1.00       |
|  2         | Person 2   |  2        | 1.00       |  0        | 0.00       |
|  3         | Person 3   |  2        | 1.00       |  0        | 0.00       |
|  4         | Person 4   |  0        | 0.00       |  0        | 0.00       |
|  5         | Person 5   |  0        | 0.00       |  0        | 0.00       |
|  6         | Person 6   |  0        | 0.00       |  0        | 0.00       |
|  7         | Person 7   |  0        | 0.00       |  0        | 0.00       |
_____________________________________________________________________________