Php monthly payouts for referral program?

Question

PHP

There is a referral program with ranks. When a referral buys a package, it is necessary to make percentage payments to the inviter. Now I do it right after the purchase, but I need to do it, for example, on the 7th of every month. I know there is a crown scheduler, but there will be quite a lot of payouts and rank recalculations. What is the best way to do this?

if ($dataRef > 0) {
    $refSum = round((($data['amount'] * $percent) / 100), 2);
    $params = [
      'sum' => $refSum,
      'id' => $dataRef,
    ];
    $this->db->query('UPDATE accounts SET refBalance = refBalance + :sum WHERE id = :id', $params);
  }

Answer 1

Как это лучше сделать?

Сделать таблицу выплат: код учётной записи, дата, сумма. Баланс средств на учётной записи — это сумма всех выплат.

Answer 2

that's why you're afraid of the query
select sum(amount) as refBalance from balance_history where partnerId = :?
{id,createdDate,amount,partnerId,reason}
and conditionally
amount + 0.07 for that ... 1000 times
amount -1000 paid to the card

Answer 3

Make a query and count all kinds of data in the database
UPDATE accounts SET refBalance = (SELECT sum FROM referals WHERE referrer_id = : referrer_id)
With 100k records, the process will take less than one minute.
The main thing is to optimally build indexes for the internal SELECT