how to credit commisions to all the parents of a child php mysql

[mrcgoud]

i am implementing chain system with auto bidding. when one user referred by parent(referral) then direct referral account have to credited with 10% of the user package amount and all parents of the direct referral credited 5% of the amount. how can i implement it. please tell me in basics not in oops.

 

[Check this image][1]

 

https://i.stack.imgur.com/tkU4S.jpg

 

----------------------------------------------------

 

<!-- begin snippet: js hide: false console: true babel: false -->

 

<!-- language: lang-html -->

 

 

    plan_id                  plan_amount                daily_commision

    -----------------------------------------------------

    p1                          $100                       0.7%

 

    p2                          $250                       1.0%

 

    p3                          $500                       1.5%

 

    p4                          $1000                       2%

 

 

    user_id password plan plan_amount referred_by income status 

    ---------------------------------------------------------

    usr0    22222    p1     $100      Global       $57.5     1

    usr1    12345    p1     $100       usr0        $72.5     1

    user2   123sad   p1     $100       usr1        $135      1

    user3   asdf4f   p2     $250       user2       $10       1

    user4   321423   p4     $1000      user2       0         1  

 

<!-- end snippet -->

 

 

here is users table user4 referred by user2 then 10% commision credited to user2 and 5% commision added to indirect referral usr1 and usr0.

 

 

then user gets daily commision according to their package. daily commision is added to user only not referral. every user got their daily commission every day.

 

please help me and save my job thanking you

 

  

 

 

 

  [1]: https://i.stack.imgur.com/tkU4S.jpg

[Barand]

My advice is to remove those amounts from the user table.

• The plan_amount should only be in the "plan" table, not duplicated for every user with that plan.
• There should be a separate "income" table (user, date, amount) to which you add a record each time commission is paid.

[Barand]

This should get you on your way. If your tables are like these

mysql> SELECT * FROM plan;
+---------+--------+------------------+
| plan_id | amount | daily_commission |
+---------+--------+------------------+
|       1 |    100 |             0.70 |
|       2 |    250 |             1.00 |
|       3 |    500 |             1.50 |
|       4 |   1000 |             2.00 |
+---------+--------+------------------+

mysql> select * from user;
+---------+----------+---------+-------------+--------+
| user_id | password | plan_id | referred_by | status |
+---------+----------+---------+-------------+--------+
|       1 | 22222    |       1 |        NULL |      1 |
|       2 | 12345    |       1 |           1 |      1 |
|       3 | 123sad   |       1 |           2 |      1 |
|       4 | asdf4f   |       2 |           3 |      1 |
|       5 | 321423   |       4 |           2 |      1 |
+---------+----------+---------+-------------+--------+

CREATE TABLE `income` (
  `inc_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `amount` decimal(8,2) DEFAULT NULL,
  `income_date` date NOT NULL,
  `income_type` char(1) NOT NULL DEFAULT 'D' COMMENT '(D)aily or (R)eferral',
  PRIMARY KEY (`inc_id`)
) ;

A query like this one, run daily, will create the daily commission income records

INSERT INTO income (user_id,income_date,amount,income_type)
SELECT u.user_id
 , CURDATE()
 , p.amount * p.daily_commission / 100
 , 'D'
FROM user u 
 INNER JOIN
 plan p USING (plan_id);

To calculate the referral commissions you will need a recursive function to climb the parent tree. Here's an example

$db = pdoConnect($db1);

/*********************************************************************
* USER TEST DATA
**********************************************************************/
$referrer = '6';
$plan = 1;
$password = 'secret';


/*********************************************************************
* ADD USER 
**********************************************************************/
$pass_hash = password_hash($password, PASSWORD_BCRYPT);
$usersql = "INSERT INTO user (password, plan_id, referred_by)
            VALUES (?,?,?)";
$userstmt = $db->prepare($usersql);
$userstmt->execute( [ $pass_hash, $plan, $referrer ] );

/*********************************************************************
* GET THE PLAN AMOUNT 10% COMMISSION
**********************************************************************/
$plansql = "SELECT amount
            FROM plan
            WHERE plan_id = ?";
$planstmt = $db->prepare($plansql);
$planstmt->execute( [$plan] );
$row = $planstmt->fetch();
$commission = $row['amount'] * 0.1;            // 10% commission

/*********************************************************************
* ADD 10% COMMISSION TO REFERRER INCOME
**********************************************************************/
$incomesql = "INSERT INTO income (user_id, amount, income_date, income_type)
              VALUES (?,?,CURDATE(),'R')";
$incomestmt = $db->prepare($incomesql);
$incomestmt->execute( [ $referrer, $commission ] );

/*********************************************************************
* ADD 5% COMMISSION TO REFERRER'S REFERRERS' INCOMEs
**********************************************************************/
$refsql = "SELECT referred_by
           FROM user
           WHERE user_id = ?";
$refstmt = $db->prepare($refsql);


$commission /= 2;                              // 5% commission
addCommission ($referrer, $commission, $refstmt, $incomestmt);


/*********************************************************************
* RECURSIVE FUNCTION TO FIND ALL REFERRERS AND ADD COMMISSION
**********************************************************************/

        function addCommission ($referrer, $commission, $refstmt, $incomestmt)
        {
            $refstmt->execute( [$referrer] );
            $row = $refstmt->fetch();
            if ($row && $row['referred_by'] != null) {
                $incomestmt->execute( [ $row['referred_by'], $commission ] );
                addCommission ($row['referred_by'], $commission, $refstmt, $incomestmt);
            }
            
        }

Don't store passwords as plain text. See the use of password_hash() in the above example. You would verify the password with password_verify()

Edited November 11, 2016 by Barand