37 lines
1.3 KiB
SQL
37 lines
1.3 KiB
SQL
-- تعداد دعوتهای هر معرف — AuthController::register → increment('total_referrals')
|
|
-- یک بار روی سرور: USE دیتابیس + بکاپ
|
|
|
|
START TRANSACTION;
|
|
|
|
SET @db = DATABASE();
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'customers' AND COLUMN_NAME = 'total_referrals') = 0,
|
|
IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'customers' AND COLUMN_NAME = 'referral_to_id') > 0,
|
|
'ALTER TABLE `customers` ADD COLUMN `total_referrals` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `referral_to_id`',
|
|
'ALTER TABLE `customers` ADD COLUMN `total_referrals` INT UNSIGNED NOT NULL DEFAULT 0'
|
|
),
|
|
'SELECT ''skip customers.total_referrals'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- همتراز با تعداد ثبتنامهای واقعی (اختیاری ولی توصیه میشود)
|
|
UPDATE `customers` AS c
|
|
SET c.`total_referrals` = (
|
|
SELECT COUNT(*)
|
|
FROM (
|
|
SELECT `id`
|
|
FROM `customers`
|
|
WHERE `referral_to_id` = c.`referral_id`
|
|
AND `deleted_at` IS NULL
|
|
) AS r
|
|
)
|
|
WHERE c.`referral_id` IS NOT NULL
|
|
AND TRIM(c.`referral_id`) <> '';
|
|
|
|
COMMIT;
|