More info about fixing wrong users' number of items in administration

[marius-ciclistu]

Teseo gave a query here:
https://forums.osclass.org/development/number-of-items/msg131428/#msg131428

I gave an alternative solution to dev101's plugin (from the above post) here:
https://forums.osclass.org/development/updating-dt_mod_date-together-with-dt_expiration/msg155870/#msg155870

I had an issue with my cron once. It did not ran for some days.... that messed up the stats.

My solution was to execute 2 queries in phpMyAdmin:


Select the table prefix_t_user (replace the 'prefix' from below queries with your own prefix!!!).

1st query sets all i_items to 0 for all users.

Code: [Select]

UPDATE prefix_t_user SET i_items = 0
2nd query calculates the stats.

Code: [Select]

UPDATE prefix_t_user u
SET i_items = (
    SELECT COUNT(*) as count
FROM prefix_t_item
    WHERE b_enabled = 1 AND b_active = 1 AND b_spam = 0 AND dt_expiration >= NOW()
AND fk_i_user_id = u.pk_i_id
)
Any opinions on this? Would this have a negative impact if it would be inserted into a weekly cron?

[dev101]

Counting function is 'stupid' on purpose, it saves huge amount of resources on a task which is not really that critical. It's best to run it as less frequently as possible, e.g. once in a while (at best, once a month) should be enough, but if the code is done right inside plugins, you won't even need to run this fix at all, actually, 'dumb' counting functions should be ok.

[marius-ciclistu]

Thank you dev101.
Now I look at the code from cron.php
It sets each time it runs a next execution time.

I could run these 2 queries via cron ONLY IF the cron hasn't run in the last hour.

Code: [Select]


$shift_seconds = 60;
    $d_now = date('Y-m-d H:i:s');
    $i_now = strtotime($d_now);
    $i_now_truncated = strtotime(date('Y-m-d H:i:00'));
    if ( ! defined('CLI')) {
        define('CLI', (PHP_SAPI==='cli'));
    }

    // Hourly crons
    $cron = Cron::newInstance()->getCronByType('HOURLY');
    if( is_array($cron) ) {
        $i_next = strtotime($cron['d_next_exec']);

        if( (CLI && (Params::getParam('cron-type') === 'hourly')) || ((($i_now - $i_next + $shift_seconds) >= 0) && !CLI) ) {
            if ($i_now_truncated > $i_next) {
                $aUsers = User::newInstance();

                 $update_user_items = sprintf("UPDATE %st_user SET i_items = 0", DB_TABLE_PREFIX);
                 $aUsers->dao->query($update_user_items);

                 $update_user_items = sprintf("UPDATE %st_user u
                 SET i_items = (SELECT COUNT(*) as count
                 FROM %st_item
                 WHERE b_enabled = 1 AND b_active = 1 AND b_spam = 0 AND dt_expiration >= NOW()
                 AND fk_i_user_id = u.pk_i_id)", DB_TABLE_PREFIX, DB_TABLE_PREFIX );

                 $aUsers->dao->query($update_user_items);
            }



in relation with this solution: https://forums.osclass.org/development/updating-dt_mod_date-together-with-dt_expiration/msg155870/#msg155870