CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `aerion`@`localhost` 
    SQL SECURITY DEFINER
VIEW `vw_dev_est` AS
    SELECT 
        `a`.`iddev_est` AS `iddev_est`,
        `a`.`attach` AS `attach`,
        `a`.`iddev_apps` AS `iddev_apps`,
        (CASE
            WHEN (`a`.`owner_ref_tab` = 'emp') THEN `s`.`file_name`
            WHEN (`a`.`owner_ref_tab` = 'comp') THEN `t`.`file_name`
        END) AS `owner_file_name`,
        (CASE
            WHEN (`a`.`est_ref_tab` = 'emp') THEN `u`.`file_name`
            WHEN (`a`.`est_ref_tab` = 'comp') THEN `v`.`file_name`
        END) AS `estimater_file_name`,
        `w`.`file_name` AS `updater_file_name`,
        `b`.`app_name` AS `app_name`,
        `a`.`dev_est_smry` AS `dev_est_smry`,
        `a`.`owner_ref_id` AS `owner_ref_id`,
        `a`.`owner_ref_tab` AS `owner_ref_tab`,
        `a`.`est_ref_id` AS `est_ref_id`,
        `a`.`est_ref_tab` AS `est_ref_tab`,
        `a`.`category` AS `category`,
        IFNULL(`a`.`category`, 'No Category') AS `category_name`,
        `a`.`priority` AS `priority`,
        IFNULL(`a`.`priority`, 'No Priority') AS `priority_name`,
        (CASE
            WHEN (`a`.`est_ref_tab` = 'emp') THEN CONCAT(`d`.`firstname`, ' ', `d`.`lastname`)
            WHEN (`a`.`est_ref_tab` = 'comp') THEN `j`.`companyname`
        END) AS `est_ref_name`,
        IFNULL(CONCAT(`d`.`firstname`, ' ', `d`.`lastname`),
                'No Estimater') AS `estimater_name`,
        (CASE
            WHEN (`a`.`owner_ref_tab` = 'emp') THEN CONCAT(`c`.`firstname`, ' ', `c`.`lastname`)
            WHEN (`a`.`owner_ref_tab` = 'comp') THEN `m`.`companyname`
        END) AS `owner_ref_name`,
        IFNULL(CONCAT(`c`.`firstname`, ' ', `c`.`lastname`),
                'No Owner') AS `owner_name`,
        `a`.`est_amt_pay` AS `est_amt_pay`,
        `a`.`est_amt_bal` AS `est_amt_bal`,
        `a`.`est_skill` AS `est_skill`,
        IFNULL(`a`.`est_gap_lvl`, 'No Gap') AS `est_gap_lvl`,
        IFNULL(`a`.`est_type`, 'Flex') AS `est_type`,
        DATE_FORMAT(`a`.`dev_st_dt`, _UTF8'%m-%d-%y') AS `dev_st_dt`,
        `a`.`dev_st_dt` AS `dev_st_dt_unformatted`,
        DATE_FORMAT(`a`.`dev_en_dt`, _UTF8'%m-%d-%y') AS `dev_en_dt`,
        `a`.`dev_en_dt` AS `dev_en_dt_unformatted`,
        DATE_FORMAT(`a`.`dev_st_dt`, '%m-%d-%y %h:%i %p') AS `dev_st_dt_time`,
        DATE_FORMAT(`a`.`dev_en_dt`, '%m-%d-%y %h:%i %p') AS `dev_en_dt_time`,
        `a`.`est_time` AS `est_time`,
        (CASE
            WHEN
                ISNULL(`a`.`dev_en_dt`)
            THEN
                ROUND(((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`a`.`dev_st_dt`)) / 60),
                        0)
            ELSE ROUND(((UNIX_TIMESTAMP(`a`.`dev_en_dt`) - UNIX_TIMESTAMP(`a`.`dev_st_dt`)) / 60),
                    0)
        END) AS `est_time2`,
        IF(ISNULL(`a`.`dev_en_dt`),
            CONCAT(ROUND(((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`a`.`dev_st_dt`)) / 3600),
                            0),
                    _UTF8':',
                    ROUND((ROUND(((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`a`.`dev_st_dt`)) % 3600),
                                    0) / 60),
                            0)),
            CONCAT(FLOOR((`a`.`est_time` / 60)),
                    _UTF8':',
                    ROUND((`a`.`est_time` % 60), 0))) AS `pseudo_est_time`,
        `a`.`est_amt` AS `est_amt`,
        `a`.`cnt_actual` AS `cnt_actual`,
        `a`.`cnt_est` AS `cnt_est`,
        `a`.`cnt_actual_est` AS `cnt_actual_est`,
        IFNULL(`a`.`cnt_pcnt`, '0%') AS `cnt_pcnt`,
        IFNULL(CONCAT(`e`.`firstname`, ' ', `e`.`lastname`),
                'Admin') AS `updater`,
        DATE_FORMAT(`a`.`last_updated`,
                _UTF8'%m-%d-%y %h:%i %p') AS `last_updated`,
        DATE_FORMAT(`a`.`last_updated`,
                _UTF8'%Y-%m-%d %h:%i %p') AS `mylast_updated`,
        DATE_FORMAT(`a`.`del_dt`, '%m-%d-%y %h:%i %p') AS `del_dt`,
        `a`.`del_dt` AS `del_dt_unformatted`,
        IFNULL(CONCAT(`e_del_by`.`firstname`,
                        ' ',
                        `e_del_by`.`lastname`),
                'Admin') AS `del_by`,
        `a`.`del_by` AS `del_by_id`,
        `o_del_by`.`file_name` AS `del_by_file_name`,
        `a`.`del_day` AS `del_day`,
        (TO_DAYS((DATE_FORMAT(`a`.`del_dt`, '%y-%m-%d') + INTERVAL `a`.`del_day` DAY)) - TO_DAYS(DATE_FORMAT(NOW(), '%y-%m-%d'))) AS `virtual_del_day`,
        IFNULL(CONCAT(`e_arch_by`.`firstname`,
                        ' ',
                        `e_arch_by`.`lastname`),
                'Admin') AS `arch_by`,
        `a`.`archv_by` AS `arch_by_id`,
        `o_arch_by`.`file_name` AS `arch_by_file_name`,
        DATE_FORMAT(`a`.`archv_dt`, '%m-%d-%y %h:%i %p') AS `arch_dt`,
        `a`.`archv_dt` AS `arch_dt_unformatted`
    FROM
        (((((((((((((((((`dev_est` `a`
        LEFT JOIN `dev_apps` `b` ON ((`a`.`iddev_apps` = `b`.`iddev_apps`)))
        LEFT JOIN `emp` `c` ON ((`a`.`owner_ref_id` = `c`.`emp_id`)))
        LEFT JOIN `emp` `e_del_by` ON ((`a`.`del_dt` = `e_del_by`.`emp_id`)))
        LEFT JOIN `emp` `e_arch_by` ON ((`a`.`archv_by` = `e_arch_by`.`emp_id`)))
        LEFT JOIN `storages` `s` ON ((`c`.`idstorage` = `s`.`idstorage`)))
        LEFT JOIN `company` `m` ON ((`a`.`owner_ref_id` = `m`.`comp_id`)))
        LEFT JOIN `storages` `t` ON ((`m`.`idstorage` = `t`.`idstorage`)))
        LEFT JOIN `emp` `d` ON ((`a`.`est_ref_id` = `d`.`emp_id`)))
        LEFT JOIN `storages` `u` ON ((`d`.`idstorage` = `u`.`idstorage`)))
        LEFT JOIN `company` `j` ON ((`a`.`est_ref_id` = `j`.`comp_id`)))
        LEFT JOIN `storages` `v` ON ((`j`.`idstorage` = `v`.`idstorage`)))
        LEFT JOIN `emp` `e` ON ((`a`.`updater` = `e`.`emp_id`)))
        LEFT JOIN `storages` `w` ON ((`e`.`idstorage` = `w`.`idstorage`)))
        LEFT JOIN `dev_look_value` `f` ON ((`a`.`category` = `f`.`iddev_look_value`)))
        LEFT JOIN `dev_look_value` `g` ON ((`a`.`priority` = `g`.`iddev_look_value`)))
        LEFT JOIN `storages` `o_del_by` ON ((`e_del_by`.`idstorage` = `o_del_by`.`idstorage`)))
        LEFT JOIN `storages` `o_arch_by` ON ((`e_arch_by`.`idstorage` = `o_arch_by`.`idstorage`)))
    GROUP BY `a`.`iddev_est`
    ORDER BY DATE_FORMAT(`a`.`last_updated`,
            _UTF8'%Y-%m-%d %h:%i %p') DESC