You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
22 lines
776 B
22 lines
776 B
ALTER TABLE leads
|
|
ADD COLUMN referral_id VARCHAR(40) NULL AFTER id,
|
|
ADD COLUMN invitee_id VARCHAR(40) NULL AFTER referral_id,
|
|
ADD INDEX idx_leads_referral (referral_id),
|
|
ADD INDEX idx_leads_invitee (invitee_id);
|
|
|
|
UPDATE leads l
|
|
JOIN referrals r
|
|
JOIN users inviter ON inviter.id = r.inviter_id
|
|
LEFT JOIN users invitee ON invitee.id = r.invitee_id
|
|
SET l.referral_id = r.id,
|
|
l.invitee_id = r.invitee_id,
|
|
l.parent = COALESCE(invitee.name, r.name)
|
|
WHERE l.referral_id IS NULL
|
|
AND (
|
|
l.parent = r.name
|
|
OR l.parent = COALESCE(invitee.name, r.name)
|
|
OR l.parent = CONCAT('家长', RIGHT(r.phone, 4))
|
|
)
|
|
AND (l.source = inviter.name OR l.source = inviter.id OR l.source = inviter.phone)
|
|
AND (l.channel = r.channel OR l.channel = '' OR r.channel = '');
|