QFQ

DELIMITER $$

CREATE DEFINER=`medtool_dev`@`localhost` FUNCTION `build_dynamic_columns`(`columnList` TEXT, `tableName` VARCHAR(128), `dbName` VARCHAR(128), `outputType` VARCHAR(128), `stringIdVariable` VARCHAR(128)) RETURNS text CHARSET utf8mb4 COLLATE utf8mb4_general_ci

    DETERMINISTIC

BEGIN

    DECLARE finished INTEGER DEFAULT 0;

    DECLARE columnName TEXT DEFAULT '';

    DECLARE constructedString TEXT DEFAULT '';

    DECLARE delim TEXT DEFAULT ',';

    DECLARE columnListCursor TEXT DEFAULT columnList;

    DECLARE tableName1 VARCHAR(128) DEFAULT tableName;

    DECLARE tableSchema VARCHAR(128) DEFAULT dbName;

    DECLARE currentPosition INT DEFAULT 1;

    DECLARE nextCommaPosition INT;

    DECLARE conditionValue VARCHAR(128) DEFAULT outputType;

    DECLARE idVariable VARCHAR(128) DEFAULT stringIdVariable;

    DECLARE columnNames TEXT DEFAULT '';

    DECLARE cur CURSOR FOR SELECT COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME = tableName1 AND TABLE_SCHEMA = tableSchema;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    IF columnListCursor = '*' THEN

        OPEN cur;

        read_loop: LOOP

            FETCH cur INTO columnName;

            IF finished THEN

                LEAVE read_loop;

            END IF;

            SET columnNames = CONCAT(columnNames, columnName, ',');

        END LOOP;

        CLOSE cur;

        SET columnListCursor = TRIM(TRAILING ',' FROM columnNames);

        SET finished = 0;

    END IF;

    -- Handling the list without whitespaces

    SET columnListCursor = REPLACE(columnListCursor, ' ', '');

    -- Loop through the comma-separated list

    WHILE NOT finished DO

        -- Find the next comma (or end of string if no more commas)

        SET nextCommaPosition = LOCATE(delim, columnListCursor, currentPosition);

        -- Extract the next column name

        IF nextCommaPosition = 0 THEN

            SET columnName = SUBSTRING(columnListCursor, currentPosition);

            SET finished = 1;

        ELSE

            SET columnName = SUBSTRING(columnListCursor, currentPosition, nextCommaPosition - currentPosition);

            SET currentPosition = nextCommaPosition + 1;

        END IF;

        -- Build the output string

        IF LENGTH(columnName) > 0 THEN

            IF LENGTH(conditionValue) > 0 AND conditionValue = 'columnSearch' THEN

                SET constructedString = CONCAT(constructedString, "IF(", columnName, "='{{", idVariable, "}}', CONCAT('", columnName, "', ','), ''), ");

            ELSEIF LENGTH(conditionValue) > 0 AND conditionValue = 'setQuery' THEN

                SET constructedString = CONCAT(constructedString, "`", columnName, "`='{{primaryId:S0}}', ");

            ELSE

                SET constructedString = CONCAT(constructedString, "'<b>", columnName, "</b>: ', IF(ISNULL(`", columnName, "`), '', IF(LENGTH(`", columnName, "`) > 100, QMORE(strip_tags(`", columnName, "`), 100), `", columnName, "`)), '<br>', ");

            END IF;

        END IF;

    END WHILE;

    -- Remove the last comma and space and "<br>"

    IF LENGTH(conditionValue) > 0 AND (conditionValue = 'columnSearch' OR conditionValue = 'setQuery') THEN

        SET constructedString = TRIM(TRAILING ', ' FROM constructedString);

    ELSE

        SET constructedString = TRIM(TRAILING ", '<br>', " FROM constructedString);

    END IF;

    IF LENGTH(conditionValue) > 0 AND conditionValue = 'columnSearch' THEN

        SET constructedString = CONCAT("TRIM(TRAILING ',' FROM CONCAT(", constructedString, "))");

    END IF;

    RETURN constructedString;

END$$

DELIMITER ;