Apolena supports an application written by contractors many years ago. It tracks user activity for reporting purposes, as one does. They then want to report on this, because why else are you gathering this information?

The contractor supplied this query to do the work.

SELECT
                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                        su.social_network,'fb','Facebook'),
                        'custom','Custom Login'),'free','Free WiFi'),
                        'twitter','Twitter'),'linkedin','LinkedIn'),
                        'vkontakte','VKontakte'),'instagram','Instagram'),
                        'Twilio','SMS'),'messenger','Messenger'),
                        'password','Passcode') AS 'Login Type',
                st.return+1 as Visits,
                st.user_table_id,
                st.social_network,
                st.picture_url,
                st.email AS email,
                CONCAT(st.gender,if(st.age!='',concat(', ',st.age),'')) AS Gender,
                device,
                sum(sts.sessiontime) AS 'Session(min)',
                CONCAT(sum(sts.download),' | ', sum(sts.upload)) AS 'Down | Up (MB)'
            FROM 
                user_table AS st
            JOIN 
                user_table_sessions AS sts ON st.user_table_id = sts.stid
            LEFT JOIN 
                location AS l ON sr.location_id=l.id
            WHERE
                1=1

The REPLACE line has had whitespace added, as it was one giant line.

Which, that particular line is… one way to handle turning internal strings into display strings. It's the wrong way, but it's a way.

It's worth noting that the column names on the query are the names that are actually displayed on the report. Which makes the Gender column interesting, as if the user has a registered age, that becomes part of their gender. Imagine the doctor with the new parents: "Congratulations! It's a (boy,0)!"

As a final naming issue, Session(min) displays sum(sts.sessiontime) which is stored in seconds.

Regardless, the fix for this query is to REPLACE(REPLACE(REPLACE(REPLACE()))) it.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!