So, I’m in the process of refactoring a website from phpBB to CI(wohoo) and I ran across this beauty of a sql statement that the previous developer left for me. Seriously this is about 40 lines more than the most complex sql statement I’ve ever written, although I did give a few bonus points for it being create table. Looks like it might be a long night.
CREATE TABLE un
(SELECT 'Votes' as source, userIDLink, IF( count(voteID)<101, count(voteID), 100 + FLOOR((count(voteID)-100)/10)) as points FROM lm_votes GROUP BY userIDLink)
UNION
(SELECT 'Winner' as source, winningentry as userIDLink, sum(200) as points FROM lm_contests WHERE winningentry>0 GROUP BY userIDLink)
UNION
(SELECT
'Comnts' as source,
userIDLink,
IF(points>100,100+ROUND((points-100)/10), points) as points
FROM
(
SELECT
userIDLink,
SUM(1) as points,
DATEDIFF(CURDATE(), dateEntered)
FROM
lm_messages
WHERE
DATEDIFF(CURDATE(), dateEntered)<31
GROUP BY
userIDLink
ORDER BY
userIDLink ASC
) as mc
)
UNION
(SELECT
'VisCom' as source,
userIDLink,
5* IF(count(attachedImageID) >20,20+ROUND((count(attachedImageID)-20)/10), count(attachedImageID)) as points
FROM lm_messages WHERE DATEDIFF(CURDATE(), dateEntered)<31 and attachedImageID >0 and userIDLink>0
GROUP BY userIDLink)
UNION
(
SELECT 'Wall' as source, userIDLink, ROUND((80*2)*(1-(1/(POW(2,count(noms)))))) as points
FROM
(
SELECT i.userIDLink, count(voteID) as noms
FROM
lm_votes as v LEFT OUTER JOIN
lm_images as i on v.entryIDLink = i.imageID
WHERE voteType=4 and score=1 and i.activeImg = 1
GROUP BY v.entryIDLink HAVING noms >= 2
) as ow
GROUP BY userIDLink
)
UNION
(SELECT 'Avatar' as source, userIDLink, 20 as points FROM lm_images WHERE imageTypeIDLink=5)
UNION
(SELECT 'Ckups' as source, userIDLink,
IF(count(critiqueID)<6,sum(20),100+ROUND((20*2)*(1-(1/(POW(2,(count(critiqueID)-6))))))) as points FROM lm_critiques WHERE critiqueType = 1 and active = 1 and DATEDIFF(CURDATE(), updated)<301 GROUP BY userIDLink)
UNION
(SELECT 'Entries' as source, userIDLink, sum(10) as points FROM lm_entries WHERE active = 1 GROUP BY userIDLink)
UNION
(SELECT 'Noms' as source, userIDLink, SUM(score)*5 as points FROM lm_votes WHERE voteType=4 GROUP BY userIDLink)
UNION
(SELECT 'Profile' as source, userIDLink, SUM(2) as points FROM lm_images WHERE imageTypeIDLink=4 AND activeImg=1 GROUP BY userIDLink)
UNION
(SELECT 'Port' as source, userIDLink, SUM(2) as points FROM lm_images WHERE imageTypeIDLink=3 AND activeImg=1 GROUP BY userIDLink)
UNION
(
SELECT
'Cmplete' as source,
userID as userIDLink,
user_active +
IF(countryCode <> 'XX',1,0) +
IF(stateCode <> 'XX',1,0) +
prodesigner +
proIndustry +
builder +
IF(autoClassTypeIDLink,1,0) +
IF(autoBrandTypeIDLink,1,0) +
IF(coolAboutMyCar <> '',1,0) +
student +
IF(aboutMe <> '',1,0) +
IF(education <> '',1,0) +
IF(experience <> '',1,0) +
IF(awards <> '',1,0) +
IF(languages <> '',1,0) +
IF(website <> '',1,0) +
IF(aim <> '',1,0) +
entryEmail +
checkupEmail +
portfolioEmail AS points
FROM lm_users u INNER JOIN phpbb_users pu ON u.userID=pu.user_id
);