10/31/2024 CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_net_ser` AS SELECT `a`.`idnet_ser` AS `idnet_ser`, `a`.`hostnm` AS `hostnm`, `a`.`hosted` AS `hosted`, `a`.`hostdm` AS `hostdm`, `a`.`hostdm_multi` AS `hostdm_multi`, `a`.`host_type` AS `host_type`, `a`.`host_mst` AS `host_mst`, CONCAT('LAN: ', IFNULL(`a`.`ip_lan`, 'No LAN '), ' ', 'WAN: ', IFNULL(`a`.`ip_wan`, 'No WAN '), ' ', 'User: ', IFNULL(`a`.`ip_usr`, 'No User'), ' ', 'Gateway: ', IFNULL(`a`.`gateway`, 'No Gateway')) AS `ips`, `a`.`updater` AS `updater`, `d`.`file_name` AS `updater_fnm`, IF(ISNULL(`a`.`updater`), 'Admin', CONCAT(`c`.`firstname`, ' ', `c`.`lastname`)) AS `updater_nm`, `a`.`updated_on` AS `updated_on`, IF(ISNULL(`a`.`updated_on`), DATE_FORMAT(NOW(), '%m-%d-%y %h:%i %p'), DATE_FORMAT(`a`.`updated_on`, '%m-%d-%y %h:%i %p')) AS `updated_on_fmt`, CONCAT('Server: ', IFNULL(`a`.`cnt_ser_blk`, 'No Block '), ' ', 'DB: ', IFNULL(`a`.`cnt_db_blk`, 'No Block ')) AS `blocks`, `a`.`cnt_blk_updated` AS `cnt_blk_updated`, DATE_FORMAT(`a`.`cnt_blk_updated`, '%m-%d-%y %h:%i %p') AS `cnt_blk_updated_fmt`, `a`.`dis_tot` AS `dis_tot`, `a`.`dis_tot` AS `dis_tot_fmt`, `a`.`dis_use` AS `dis_use`, `a`.`dis_use` AS `dis_use_fmt`, `a`.`dis_free` AS `dis_free`, `a`.`dis_free` AS `dis_free_fmt`, `a`.`dis_pcnt` AS `dis_pcnt` FROM (((`net_ser` `a` LEFT JOIN `net_ser` `b` ON ((`a`.`host_mst` = `b`.`hostnm`))) LEFT JOIN `emp` `c` ON ((`a`.`updater` = `c`.`emp_id`))) LEFT JOIN `storages` `d` ON ((`c`.`idstorage` = `d`.`idstorage`))) ________________________________________________________________________________ dt10/29/2024 vw_dev_est: 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`, `b`.`app_name` AS `app_name`, `b`.`app_st_dt` AS `app_st_dt`, IF(ISNULL(`b`.`app_st_dt`), 'Not Started', DATE_FORMAT(`b`.`app_st_dt`, '%m-%d-%y %h:%i %p')) AS `app_st_dt_fmt`, `b`.`app_e_dt` AS `app_e_dt`, (CASE WHEN (`b`.`app_e_dt` IS NOT NULL) THEN DATE_FORMAT(`b`.`app_e_dt`, '%m-%d-%y %h:%i %p') WHEN (ISNULL(`b`.`app_e_dt`) AND ISNULL(`b`.`app_st_dt`)) THEN 'Not Started' WHEN (ISNULL(`b`.`app_e_dt`) AND (`b`.`app_st_dt` IS NOT NULL)) THEN 'In Progress' END) AS `app_e_dt_fmt`, IF(ISNULL(`b`.`app_e_dt`), 'Open', 'Closed') AS `dev_apps_cur_stat`, `b`.`comp_id` AS `comp_id`, `d`.`file_name` AS `comp_logo`, IFNULL(`c`.`companyname`, 'No Company') AS `companyname`, CONCAT((CASE WHEN (`b`.`app_est_cnt` > 999) THEN CONCAT((TRIM(ROUND((`b`.`app_est_cnt` / 1000), 2)) + 0), 'K') WHEN (`b`.`app_est_cnt` < 1000) THEN `b`.`app_est_cnt` END), ' of ', (CASE WHEN (`b`.`app_est_tol` > 999) THEN CONCAT((TRIM(ROUND((`b`.`app_est_tol` / 1000), 2)) + 0), 'K') WHEN (`b`.`app_est_tol` < 1000) THEN `b`.`app_est_tol` END)) AS `app_est_cnt_tol`, IFNULL(`b`.`app_est_pcnt`, '0%') AS `app_est_pcnt`, `b`.`app_bud_amt` AS `app_bud_amt`, `b`.`app_use_amt` AS `app_use_amt`, CONCAT((CASE WHEN ((`b`.`app_use_amt` > 999) AND (`b`.`app_use_amt` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`b`.`app_use_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`b`.`app_use_amt` < 1000) THEN `b`.`app_use_amt` WHEN ((`b`.`app_use_amt` > 999999) AND (`b`.`app_use_amt` < 1000000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`b`.`app_use_amt` / 1000000), 2)) + 0), 'M') USING LATIN1) WHEN (`b`.`app_use_amt` > 999999999) THEN CONVERT( CONCAT((TRIM(ROUND((`b`.`app_use_amt` / 1000000000), 2)) + 0), 'B') USING LATIN1) END), ' of ', (CASE WHEN ((`b`.`app_bud_amt` > 999) AND (`b`.`app_bud_amt` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`b`.`app_bud_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`b`.`app_bud_amt` < 1000) THEN `b`.`app_bud_amt` WHEN ((`b`.`app_bud_amt` > 999999) AND (`b`.`app_bud_amt` < 1000000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`b`.`app_bud_amt` / 1000000), 2)) + 0), 'M') USING LATIN1) WHEN (`b`.`app_bud_amt` > 999999999) THEN CONVERT( CONCAT((TRIM(ROUND((`b`.`app_bud_amt` / 1000000000), 2)) + 0), 'B') USING LATIN1) END)) AS `app_bud_use_amt`, `b`.`app_use_pcnt` AS `app_use_pcnt`, `b`.`app_bud_bal` AS `app_bud_bal`, `b`.`app_bud_pcnt` AS `app_bud_pcnt`, `b`.`app_pay_amt` AS `app_pay_amt`, CONCAT((CASE WHEN ((`b`.`app_pay_amt` > 999) AND (`b`.`app_pay_amt` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`b`.`app_pay_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`b`.`app_pay_amt` < 1000) THEN `b`.`app_pay_amt` WHEN ((`b`.`app_pay_amt` > 999999) AND (`b`.`app_pay_amt` < 1000000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`b`.`app_pay_amt` / 1000000), 2)) + 0), 'M') USING LATIN1) WHEN (`b`.`app_pay_amt` > 999999999) THEN CONVERT( CONCAT((TRIM(ROUND((`b`.`app_pay_amt` / 1000000000), 2)) + 0), 'B') USING LATIN1) END), ' of ', (CASE WHEN ((`b`.`app_use_amt` > 999) AND (`b`.`app_use_amt` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`b`.`app_use_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`b`.`app_use_amt` < 1000) THEN `b`.`app_use_amt` WHEN ((`b`.`app_use_amt` > 999999) AND (`b`.`app_use_amt` < 1000000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`b`.`app_use_amt` / 1000000), 2)) + 0), 'M') USING LATIN1) WHEN (`b`.`app_use_amt` > 999999999) THEN CONVERT( CONCAT((TRIM(ROUND((`b`.`app_use_amt` / 1000000000), 2)) + 0), 'B') USING LATIN1) END)) AS `app_pay_bal`, `b`.`app_pay_pcnt` AS `app_pay_pcnt`, `b`.`app_bal_amt` AS `app_bal_amt`, `b`.`app_bal_pcnt` AS `app_bal_pcnt`, `a`.`est_st_dt` AS `est_st_dt`, IF(ISNULL(`a`.`est_st_dt`), 'Not Started', DATE_FORMAT(`a`.`est_st_dt`, '%m-%d-%y %h:%i %p')) AS `est_st_dt_fmt`, IFNULL(DATE_FORMAT(`a`.`est_st_dt`, '%Y'), 'Not Started') AS `est_st_dt_yr`, IFNULL(DATE_FORMAT(`a`.`est_st_dt`, '%M'), 'Not Started') AS `est_st_dt_mon`, `a`.`dev_est_smry` AS `dev_est_smry`, `a`.`est_e_dt` AS `est_e_dt`, (CASE WHEN (`a`.`est_e_dt` IS NOT NULL) THEN DATE_FORMAT(`a`.`est_e_dt`, '%m-%d-%y %h:%i %p') WHEN (ISNULL(`a`.`est_e_dt`) AND ISNULL(`a`.`est_st_dt`)) THEN 'Not Started' WHEN (ISNULL(`a`.`est_e_dt`) AND (`a`.`est_st_dt` IS NOT NULL)) THEN 'In Progress' END) AS `est_e_dt_fmt`, IF(ISNULL(`a`.`est_e_dt`), 'Open', 'Closed') AS `dev_est_cur_stat`, (CASE WHEN (`a`.`est_e_dt` IS NOT NULL) THEN DATE_FORMAT(`a`.`est_e_dt`, '%Y') WHEN (ISNULL(`a`.`est_e_dt`) AND ISNULL(`a`.`est_st_dt`)) THEN 'Not Started' WHEN (ISNULL(`a`.`est_e_dt`) AND (`a`.`est_st_dt` IS NOT NULL)) THEN 'In Progress' END) AS `est_e_dt_yr`, (CASE WHEN (`a`.`est_e_dt` IS NOT NULL) THEN DATE_FORMAT(`a`.`est_e_dt`, '%M') WHEN (ISNULL(`a`.`est_e_dt`) AND ISNULL(`a`.`est_st_dt`)) THEN 'Not Started' WHEN (ISNULL(`a`.`est_e_dt`) AND (`a`.`est_st_dt` IS NOT NULL)) THEN 'In Progress' END) AS `est_e_dt_mon`, `a`.`est_time` AS `est_time`, (CASE WHEN ISNULL(`a`.`est_e_dt`) THEN ROUND(((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`a`.`est_st_dt`)) / 60), 0) ELSE ROUND(((UNIX_TIMESTAMP(`a`.`est_e_dt`) - UNIX_TIMESTAMP(`a`.`est_st_dt`)) / 60), 0) END) AS `est_time_vir`, `a`.`est_lag_tm` AS `est_lag_tm`, IFNULL(`a`.`est_lag_tm`, '0') AS `est_lag_tm_vir`, `a`.`owner_ref_tab` AS `owner_ref_tab`, `a`.`owner_ref_id` AS `owner_ref_id`, (CASE WHEN (`a`.`owner_ref_tab` LIKE 'emp') THEN `f`.`file_name` WHEN (`a`.`owner_ref_tab` LIKE 'comp') THEN `h`.`file_name` END) AS `owner_fnm`, IFNULL((CASE WHEN (`a`.`owner_ref_tab` LIKE 'emp') THEN CONCAT(`e`.`firstname`, ' ', `e`.`lastname`) WHEN (`a`.`owner_ref_tab` LIKE 'comp') THEN `g`.`companyname` END), 'No Owner') AS `owner_ref_nm`, `a`.`est_ref_tab` AS `est_ref_tab`, `a`.`est_ref_id` AS `est_ref_id`, (CASE WHEN (`a`.`est_ref_tab` LIKE 'emp') THEN `j`.`file_name` WHEN (`a`.`est_ref_id` LIKE 'comp') THEN `l`.`file_name` END) AS `est_fnm`, IFNULL((CASE WHEN (`a`.`est_ref_tab` LIKE 'emp') THEN CONCAT(`i`.`firstname`, ' ', `i`.`lastname`) WHEN (`a`.`est_ref_tab` LIKE 'comp') THEN `k`.`companyname` END), 'No Estimater') AS `est_ref_nm`, `a`.`priority` AS `priority`, IFNULL(`m`.`look_value`, 'No Section') AS `priority_nm`, `a`.`category` AS `category`, IFNULL(`n`.`look_value`, 'No Category') AS `category_nm`, IFNULL(`a`.`est_skill`, '0.0') AS `est_skill`, IFNULL(`a`.`est_gap_lvl`, '0.0') AS `est_gap_lvl`, IFNULL(`a`.`est_type`, 'Flex') AS `est_type`, `a`.`est_req_cnt` AS `est_req_cnt`, `a`.`est_req_tol` AS `est_req_tol`, CONCAT((CASE WHEN (`a`.`est_req_cnt` > 999) THEN CONCAT((TRIM(ROUND((`a`.`est_req_cnt` / 1000), 2)) + 0), 'K') WHEN (`a`.`est_req_cnt` < 1000) THEN `a`.`est_req_cnt` END), ' of ', (CASE WHEN (`a`.`est_req_tol` > 999) THEN CONCAT((TRIM(ROUND((`a`.`est_req_tol` / 1000), 2)) + 0), 'K') WHEN (`a`.`est_req_tol` < 1000) THEN `a`.`est_req_tol` END)) AS `est_req_cnt_tol`, `a`.`est_req_pcnt` AS `est_req_pcnt`, `a`.`est_req_stat_cnt` AS `est_req_stat_cnt`, `a`.`est_req_stat_tol` AS `est_req_stat_tol`, CONCAT((CASE WHEN (`a`.`est_req_stat_cnt` > 999) THEN CONCAT((TRIM(ROUND((`a`.`est_req_stat_cnt` / 1000), 2)) + 0), 'K') WHEN (`a`.`est_req_stat_cnt` < 1000) THEN `a`.`est_req_stat_cnt` END), ' of ', (CASE WHEN (`a`.`est_req_stat_tol` > 999) THEN CONCAT((TRIM(ROUND((`a`.`est_req_stat_tol` / 1000), 2)) + 0), 'K') WHEN (`a`.`est_req_stat_tol` < 1000) THEN `a`.`est_req_stat_tol` END)) AS `est_req_stat_cnt_tol`, `a`.`est_req_stat_pcnt` AS `est_req_stat_pcnt`, `a`.`est_case_cnt` AS `est_case_cnt`, `a`.`est_case_tol` AS `est_case_tol`, CONCAT((CASE WHEN (`a`.`est_case_cnt` > 999) THEN CONCAT((TRIM(ROUND((`a`.`est_case_cnt` / 1000), 2)) + 0), 'K') WHEN (`a`.`est_case_cnt` < 1000) THEN `a`.`est_case_cnt` END), ' of ', (CASE WHEN (`a`.`est_case_tol` > 999) THEN CONCAT((TRIM(ROUND((`a`.`est_case_tol` / 1000), 2)) + 0), 'K') WHEN (`a`.`est_case_tol` < 1000) THEN `a`.`est_case_tol` END)) AS `est_case_cnt_tol`, `a`.`est_case_pcnt` AS `est_case_pcnt`, `a`.`est_case_atmpt` AS `est_case_atmpt`, `a`.`est_bud_amt` AS `est_bud_amt`, `a`.`est_use_amt` AS `est_use_amt`, CONCAT((CASE WHEN ((`a`.`est_use_amt` > 999) AND (`a`.`est_use_amt` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`est_use_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`est_use_amt` < 1000) THEN `a`.`est_use_amt` WHEN (`a`.`est_use_amt` > 999999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`est_use_amt` / 1000000), 2)) + 0), 'M') USING LATIN1) END), ' of ', (CASE WHEN ((`a`.`est_bud_amt` > 999) AND (`a`.`est_bud_amt` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`est_bud_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`est_bud_amt` < 1000) THEN `a`.`est_bud_amt` WHEN (`a`.`est_bud_amt` > 999999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`est_bud_amt` / 1000000), 2)) + 0), 'M') USING LATIN1) END)) AS `est_bud_use_amt`, `a`.`est_use_pcnt` AS `est_use_pcnt`, `a`.`est_bud_bal` AS `est_bud_bal`, `a`.`est_bud_pcnt` AS `est_bud_pcnt`, `a`.`est_pay_amt` AS `est_pay_amt`, CONCAT((CASE WHEN ((`a`.`est_pay_amt` > 999) AND (`a`.`est_pay_amt` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`est_pay_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`est_pay_amt` < 1000) THEN `a`.`est_pay_amt` WHEN (`a`.`est_pay_amt` > 999999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`est_pay_amt` / 1000000), 2)) + 0), 'M') USING LATIN1) END), ' of ', (CASE WHEN ((`a`.`est_use_amt` > 999) AND (`a`.`est_use_amt` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`est_use_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`est_use_amt` < 1000) THEN `a`.`est_use_amt` WHEN (`a`.`est_use_amt` > 999999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`est_use_amt` / 1000000), 2)) + 0), 'M') USING LATIN1) END)) AS `est_pay_bal`, `a`.`est_pay_pcnt` AS `est_pay_pcnt`, `a`.`est_bal_amt` AS `est_bal_amt`, `a`.`est_bal_pcnt` AS `est_bal_pcnt`, `a`.`updater` AS `updater`, `p`.`file_name` AS `updater_fnm`, IFNULL(CONCAT(`o`.`firstname`, ' ', `o`.`lastname`), 'Admin') AS `updater_nm`, `a`.`last_updated` AS `last_updated`, DATE_FORMAT(`a`.`last_updated`, '%m-%d-%y %h:%i %p') AS `last_updated_fmt`, IFNULL(DATE_FORMAT(`a`.`last_updated`, '%Y'), 'Never') AS `last_updated_yr`, IFNULL(DATE_FORMAT(`a`.`last_updated`, '%M'), 'Never') AS `last_updated_mon`, `a`.`del_dt` AS `del_dt`, DATE_FORMAT(`a`.`del_dt`, '%m-%d-%y %h:%i %p') AS `del_dt_fmt`, IFNULL(DATE_FORMAT(`a`.`del_dt`, '%Y'), 'Not Deleted') AS `del_dt_yr`, IFNULL(DATE_FORMAT(`a`.`del_dt`, '%M'), 'Not Deleted') AS `del_dt_mon`, `a`.`del_by` AS `del_by`, `r`.`file_name` AS `del_by_fnm`, IFNULL(CONCAT(`q`.`firstname`, ' ', `q`.`lastname`), 'Admin') AS `del_by_nm`, `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 `del_day_vir`, `a`.`archv_dt` AS `archv_dt`, DATE_FORMAT(`a`.`archv_dt`, '%m-%d-%y %h:%i %p') AS `archv_dt_fmt`, IFNULL(DATE_FORMAT(`a`.`archv_dt`, '%Y'), 'Not Archived') AS `archv_dt_yr`, IFNULL(DATE_FORMAT(`a`.`archv_dt`, '%M'), 'Not Archived') AS `archv_dt_mon`, `a`.`archv_by` AS `archv_by`, `t`.`file_name` AS `archv_by_fnm`, IFNULL(CONCAT(`s`.`firstname`, ' ', `s`.`lastname`), 'Admin') AS `archv_by_nm`, `a`.`old_est_dt` AS `old_est_dt`, `a`.`old_req_dt` AS `old_req_dt`, `a`.`run_func` AS `run_func` FROM (((((((((((((((((((`dev_est` `a` LEFT JOIN `dev_apps` `b` ON ((`a`.`iddev_apps` = `b`.`iddev_apps`))) LEFT JOIN `company` `c` ON ((`b`.`comp_id` = `c`.`comp_id`))) LEFT JOIN `storages` `d` ON ((`c`.`idstorage` = `d`.`idstorage`))) LEFT JOIN `emp` `e` ON ((`a`.`owner_ref_id` = `e`.`emp_id`))) LEFT JOIN `storages` `f` ON ((`e`.`idstorage` = `f`.`idstorage`))) LEFT JOIN `company` `g` ON ((`a`.`owner_ref_id` = `g`.`comp_id`))) LEFT JOIN `storages` `h` ON ((`g`.`idstorage` = `h`.`idstorage`))) LEFT JOIN `emp` `i` ON ((`a`.`est_ref_id` = `i`.`emp_id`))) LEFT JOIN `storages` `j` ON ((`i`.`idstorage` = `j`.`idstorage`))) LEFT JOIN `company` `k` ON ((`a`.`est_ref_id` = `k`.`comp_id`))) LEFT JOIN `storages` `l` ON ((`k`.`idstorage` = `l`.`idstorage`))) LEFT JOIN `dev_look_value` `m` ON ((`a`.`priority` = `m`.`iddev_look_value`))) LEFT JOIN `dev_look_value` `n` ON ((`a`.`category` = `n`.`iddev_look_value`))) LEFT JOIN `emp` `o` ON ((`a`.`updater` = `o`.`emp_id`))) LEFT JOIN `storages` `p` ON ((`o`.`idstorage` = `p`.`idstorage`))) LEFT JOIN `emp` `q` ON ((`a`.`del_by` = `q`.`emp_id`))) LEFT JOIN `storages` `r` ON ((`q`.`idstorage` = `r`.`idstorage`))) LEFT JOIN `emp` `s` ON ((`a`.`archv_by` = `s`.`emp_id`))) LEFT JOIN `storages` `t` ON ((`s`.`idstorage` = `t`.`idstorage`))) ORDER BY DATE_FORMAT(`a`.`last_updated`, '%Y-%m-%d %h:%i %p') DESC ______________________________________________________________________________________________________ dt10/29/2024 vw_dev_est_reqs: CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_dev_est_reqs` AS SELECT `a`.`iddev_est_reqs` AS `iddev_est_reqs`, `c`.`iddev_apps` AS `iddev_apps`, `c`.`app_name` AS `app_name`, `c`.`app_st_dt` AS `app_st_dt`, IF(ISNULL(`c`.`app_st_dt`), 'Not Started', DATE_FORMAT(`c`.`app_st_dt`, '%m-%d-%y %h:%i %p')) AS `app_st_dt_fmt`, `c`.`app_e_dt` AS `app_e_dt`, (CASE WHEN (`c`.`app_e_dt` IS NOT NULL) THEN DATE_FORMAT(`c`.`app_e_dt`, '%m-%d-%y %h:%i %p') WHEN (ISNULL(`c`.`app_e_dt`) AND ISNULL(`c`.`app_st_dt`)) THEN 'Not Started' WHEN (ISNULL(`c`.`app_e_dt`) AND (`c`.`app_st_dt` IS NOT NULL)) THEN 'In Progress' END) AS `app_e_dt_fmt`, IF(ISNULL(`c`.`app_e_dt`), 'Open', 'Closed') AS `dev_apps_cur_stat`, `a`.`iddev_est` AS `iddev_est`, `b`.`dev_est_smry` AS `dev_est_smry`, `b`.`est_st_dt` AS `est_st_dt`, IF(ISNULL(`b`.`est_st_dt`), 'Not Started', DATE_FORMAT(`b`.`est_st_dt`, '%m-%d-%y %h:%i %p')) AS `est_st_dt_fmt`, `b`.`est_e_dt` AS `est_e_dt`, (CASE WHEN (`b`.`est_e_dt` IS NOT NULL) THEN DATE_FORMAT(`b`.`est_e_dt`, '%m-%d-%y %h:%i %p') WHEN (ISNULL(`b`.`est_e_dt`) AND ISNULL(`b`.`est_st_dt`)) THEN 'Not Started' WHEN (ISNULL(`b`.`est_e_dt`) AND (`b`.`est_st_dt` IS NOT NULL)) THEN 'In Progress' END) AS `est_e_dt_fmt`, IF(ISNULL(`b`.`est_e_dt`), 'Open', 'Closed') AS `dev_est_cur_stat`, `b`.`est_time` AS `est_time`, (CASE WHEN ISNULL(`b`.`est_e_dt`) THEN ROUND(((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`b`.`est_st_dt`)) / 60), 0) ELSE ROUND(((UNIX_TIMESTAMP(`b`.`est_e_dt`) - UNIX_TIMESTAMP(`b`.`est_st_dt`)) / 60), 0) END) AS `est_time_vir`, `b`.`owner_ref_tab` AS `owner_ref_tab`, `b`.`owner_ref_id` AS `owner_ref_id`, (CASE WHEN (`b`.`owner_ref_tab` LIKE 'emp') THEN `g`.`file_name` WHEN (`b`.`owner_ref_tab` LIKE 'comp') THEN `i`.`file_name` END) AS `owner_fnm`, IFNULL((CASE WHEN (`b`.`owner_ref_tab` LIKE 'comp') THEN `h`.`companyname` WHEN (`b`.`owner_ref_tab` LIKE 'emp') THEN CONCAT(`f`.`firstname`, ' ', `f`.`lastname`) END), 'No Owner') AS `owner_ref_nm`, `b`.`est_ref_tab` AS `est_ref_tab`, `b`.`est_ref_id` AS `est_ref_id`, (CASE WHEN (`b`.`est_ref_tab` LIKE 'emp') THEN `k`.`file_name` WHEN (`b`.`est_ref_tab` LIKE 'comp') THEN `m`.`file_name` END) AS `est_fnm`, IFNULL((CASE WHEN (`b`.`est_ref_tab` LIKE 'emp') THEN CONCAT(`j`.`firstname`, ' ', `j`.`lastname`) WHEN (`b`.`est_ref_tab` LIKE 'comp') THEN `l`.`companyname` END), 'No Estimater') AS `est_ref_nm`, `b`.`est_type` AS `est_type`, `b`.`priority` AS `priority`, IFNULL(`n`.`look_value`, 'No Section') AS `priority_nm`, `b`.`category` AS `category`, IFNULL(`o`.`look_value`, 'No Category') AS `category_nm`, `b`.`est_req_cnt` AS `est_req_cnt`, `b`.`est_req_tol` AS `est_req_tol`, CONCAT((CASE WHEN (`b`.`est_req_cnt` > 999) THEN CONCAT((TRIM(ROUND((`b`.`est_req_cnt` / 1000), 2)) + 0), 'K') WHEN (`b`.`est_req_cnt` < 1000) THEN `b`.`est_req_cnt` END), ' of ', (CASE WHEN (`b`.`est_req_tol` > 999) THEN CONCAT((TRIM(ROUND((`b`.`est_req_tol` / 1000), 2)) + 0), 'K') WHEN (`b`.`est_req_tol` < 1000) THEN `b`.`est_req_tol` END)) AS `est_req_cnt_tol`, `b`.`est_req_pcnt` AS `est_req_pcnt`, `a`.`req_st_dt` AS `req_st_dt`, IF(ISNULL(`a`.`req_st_dt`), 'Not Started', DATE_FORMAT(`a`.`req_st_dt`, '%m-%d-%y %h:%i %p')) AS `req_st_dt_fmt`, DATE_FORMAT(`a`.`req_st_dt`, '%Y') AS `req_st_dt_yr`, DATE_FORMAT(`a`.`req_st_dt`, '%M') AS `req_st_dt_mon`, `a`.`req_e_dt` AS `req_e_dt`, (CASE WHEN (`a`.`req_e_dt` IS NOT NULL) THEN DATE_FORMAT(`a`.`req_e_dt`, '%m-%d-%y %h:%i %p') WHEN (ISNULL(`a`.`req_e_dt`) AND ISNULL(`a`.`req_st_dt`)) THEN 'Not Started' WHEN (ISNULL(`a`.`req_e_dt`) AND (`a`.`req_st_dt` IS NOT NULL)) THEN 'In Progress' END) AS `req_e_dt_fmt`, IF(ISNULL(`a`.`req_e_dt`), 'Open', 'Closed') AS `dev_est_reqs_cur_stat`, DATE_FORMAT(`a`.`req_e_dt`, '%Y') AS `req_e_dt_yr`, DATE_FORMAT(`a`.`req_e_dt`, '%M') AS `req_e_dt_mon`, `a`.`req_time` AS `req_time`, (CASE WHEN (ISNULL(`a`.`req_time`) AND (`a`.`req_st_dt` IS NOT NULL)) THEN ROUND(((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`a`.`req_st_dt`)) / 60), 0) ELSE `a`.`req_time` END) AS `req_time_vir`, `a`.`req_lag_tm` AS `req_lag_tm`, IFNULL(`a`.`req_lag_tm`, '0') AS `req_lag_tm_vir`, `a`.`disp_order` AS `disp_order`, `a`.`req_desc` AS `req_desc`, `a`.`skill_lvl` AS `skill_lvl`, IFNULL(`a`.`skill_lvl`, 'No Skill') AS `skill_lvl_nm`, `a`.`gap_lvl` AS `gap_lvl`, IFNULL(`a`.`gap_lvl`, 'No Gap') AS `gap_lvl_nm`, `a`.`req_type` AS `req_type`, IFNULL(`p`.`look_value`, 'None') AS `req_type_nm`, `a`.`iddev_lk_wrk_flow` AS `iddev_lk_wrk_flow`, IFNULL(`q`.`wrk_flow_nm`, 'No Workflow') AS `wrk_flow_nm`, `q`.`wrk_flow_desc` AS `wrk_flow_desc`, `a`.`req_status` AS `req_status`, IFNULL(`r`.`look_value`, 'No Status') AS `req_status_nm`, `a`.`idlook_stat_order` AS `idlook_stat_order`, `a`.`req_stat_st_dt` AS `req_stat_st_dt`, IF(ISNULL(`a`.`req_stat_st_dt`), 'Not Started', DATE_FORMAT(`a`.`req_stat_st_dt`, '%m-%d-%y %h:%i %p')) AS `req_stat_st_dt_fmt`, `a`.`req_stat_e_dt` AS `req_stat_e_dt`, (CASE WHEN (`a`.`req_stat_e_dt` IS NOT NULL) THEN DATE_FORMAT(`a`.`req_stat_e_dt`, '%m-%d-%y %h:%i %p') WHEN (ISNULL(`a`.`req_stat_e_dt`) AND ISNULL(`a`.`req_stat_st_dt`)) THEN 'Not Started' WHEN (ISNULL(`a`.`req_stat_e_dt`) AND (`a`.`req_stat_st_dt` IS NOT NULL)) THEN 'In Progress' END) AS `req_stat_e_dt_fmt`, `a`.`req_stat_time` AS `req_stat_time`, (CASE WHEN (ISNULL(`a`.`req_stat_time`) AND (`a`.`req_stat_st_dt` IS NOT NULL)) THEN ROUND(((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`a`.`req_stat_st_dt`)) / 60), 0) ELSE `a`.`req_stat_time` END) AS `req_stat_time_vir`, `a`.`req_stat_e_dt_pre` AS `req_stat_e_dt_pre`, DATE_FORMAT(`a`.`req_stat_e_dt_pre`, '%m-%d-%y %h:%i %p') AS `req_stat_e_dt_pre_fmt`, `a`.`req_stat_lag_tm` AS `req_stat_lag_tm`, IFNULL(`a`.`req_stat_lag_tm`, '0') AS `req_stat_lag_tm_vir`, `a`.`assigned_to` AS `assigned_to`, `t`.`file_name` AS `assigned_to_fnm`, IFNULL(CONCAT(`s`.`firstname`, ' ', `s`.`lastname`), 'No Assignee') AS `assigned_to_nm`, `a`.`req_stat_cnt` AS `req_stat_cnt`, `a`.`req_stat_tol` AS `req_stat_tol`, CONCAT(`a`.`req_stat_cnt`, ' of ', `a`.`req_stat_tol`) AS `req_stat_cnt_tol`, `a`.`req_stat_pcnt` AS `req_stat_pcnt`, `a`.`req_case_cnt` AS `req_case_cnt`, `a`.`req_case_tol` AS `req_case_tol`, CONCAT(`a`.`req_case_cnt`, ' of ', `a`.`req_case_tol`) AS `req_case_cnt_tol`, `a`.`req_case_pcnt` AS `req_case_pcnt`, `a`.`req_cases` AS `req_cases`, `a`.`req_case_atmpt` AS `req_case_atmpt`, `a`.`req_bud_amt` AS `req_bud_amt`, IFNULL(`a`.`req_use_amt`, '0.00') AS `req_use_amt`, CONCAT((CASE WHEN ISNULL(`a`.`req_use_amt`) THEN '0.00' WHEN (`a`.`req_use_amt` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`req_use_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`req_use_amt` < 1000) THEN `a`.`req_use_amt` END), ' of ', (CASE WHEN ISNULL(`a`.`req_bud_amt`) THEN '0.00' WHEN (`a`.`req_bud_amt` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`req_bud_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`req_bud_amt` < 1000) THEN `a`.`req_bud_amt` END)) AS `req_bud_use_amt`, `a`.`req_use_pcnt` AS `req_use_pcnt`, `a`.`req_bud_bal` AS `req_bud_bal`, `a`.`req_bud_pcnt` AS `req_bud_pcnt`, `a`.`req_pay_amt` AS `req_pay_amt`, CONCAT((CASE WHEN (`a`.`req_pay_amt` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`req_pay_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`req_pay_amt` < 1000) THEN `a`.`req_pay_amt` END), ' of ', (CASE WHEN (`a`.`req_use_amt` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`req_use_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`req_use_amt` < 1000) THEN `a`.`req_use_amt` END)) AS `req_pay_bal`, `a`.`req_pay_pcnt` AS `req_pay_pcnt`, `a`.`req_bal_amt` AS `req_bal_amt`, `a`.`req_bal_pcnt` AS `req_bal_pcnt`, `a`.`updater` AS `updater`, `v`.`file_name` AS `updater_fnm`, IFNULL(CONCAT(`u`.`firstname`, ' ', `u`.`lastname`), 'Admin') AS `updater_nm`, `a`.`last_updated` AS `last_updated`, DATE_FORMAT(`a`.`last_updated`, '%m-%d-%y %h:%i %p') AS `last_updated_fmt`, `a`.`del_dt` AS `del_dt`, DATE_FORMAT(`a`.`del_dt`, '%m-%d-%y %h:%i %p') AS `del_dt_fmt`, IFNULL(DATE_FORMAT(`a`.`del_dt`, '%Y'), 'Not Deleted') AS `del_dt_yr`, IFNULL(DATE_FORMAT(`a`.`del_dt`, '%M'), 'Not Deleted') AS `del_dt_mon`, `a`.`del_by` AS `del_by`, `x`.`file_name` AS `del_by_fnm`, IFNULL(CONCAT(`w`.`firstname`, ' ', `w`.`lastname`), 'Admin') AS `del_by_nm`, `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 `del_day_vir`, `a`.`archv_dt` AS `archv_dt`, DATE_FORMAT(`a`.`archv_dt`, '%m-%d-%y %h:%i %p') AS `archv_dt_fmt`, IFNULL(DATE_FORMAT(`a`.`archv_dt`, '%Y'), 'Not Archived') AS `archv_dt_yr`, IFNULL(DATE_FORMAT(`a`.`archv_dt`, '%M'), 'Not Archived') AS `archv_dt_mon`, `a`.`archv_by` AS `archv_by`, `z`.`file_name` AS `archv_by_fnm`, IFNULL(CONCAT(`y`.`firstname`, ' ', `y`.`lastname`), 'Admin') AS `archv_by_nm`, `a`.`rpt_req_stat_by` AS `rpt_req_stat_by`, `a`.`rpt_req_stat_by_nm` AS `rpt_req_stat_by_nm`, `a`.`rpt_req_stat_id` AS `rpt_req_stat_id`, `a`.`rpt_req_stat_id_nm` AS `rpt_req_stat_id_nm`, `a`.`rpt_req_stat_lag_tm` AS `rpt_req_stat_lag_tm`, `a`.`rpt_req_stat_time` AS `rpt_req_stat_time` FROM (((((((((((((((((((((((`dev_est_reqs` `a` LEFT JOIN `dev_est` `b` ON ((`a`.`iddev_est` = `b`.`iddev_est`))) LEFT JOIN `dev_apps` `c` ON ((`b`.`iddev_apps` = `c`.`iddev_apps`))) LEFT JOIN `emp` `f` ON ((`b`.`owner_ref_id` = `f`.`emp_id`))) LEFT JOIN `storages` `g` ON ((`f`.`idstorage` = `g`.`idstorage`))) LEFT JOIN `company` `h` ON ((`b`.`owner_ref_id` = `h`.`comp_id`))) LEFT JOIN `storages` `i` ON ((`h`.`idstorage` = `i`.`idstorage`))) LEFT JOIN `emp` `j` ON ((`b`.`est_ref_id` = `j`.`emp_id`))) LEFT JOIN `storages` `k` ON ((`j`.`idstorage` = `k`.`idstorage`))) LEFT JOIN `company` `l` ON ((`b`.`est_ref_id` = `l`.`comp_id`))) LEFT JOIN `storages` `m` ON ((`l`.`idstorage` = `m`.`idstorage`))) LEFT JOIN `dev_look_value` `n` ON ((`b`.`priority` = `n`.`iddev_look_value`))) LEFT JOIN `dev_look_value` `o` ON ((`b`.`category` = `o`.`iddev_look_value`))) LEFT JOIN `dev_look_value` `p` ON ((`a`.`req_type` = `p`.`iddev_look_value`))) LEFT JOIN `dev_lk_wrk_flow` `q` ON ((`a`.`iddev_lk_wrk_flow` = `q`.`iddev_lk_wrk_flow`))) LEFT JOIN `dev_look_value` `r` ON ((`a`.`req_status` = `r`.`iddev_look_value`))) LEFT JOIN `emp` `s` ON ((`a`.`assigned_to` = `s`.`emp_id`))) LEFT JOIN `storages` `t` ON ((`t`.`idstorage` = `s`.`idstorage`))) LEFT JOIN `emp` `u` ON ((`a`.`updater` = `u`.`emp_id`))) LEFT JOIN `storages` `v` ON ((`u`.`idstorage` = `v`.`idstorage`))) LEFT JOIN `emp` `w` ON ((`a`.`del_by` = `w`.`emp_id`))) LEFT JOIN `storages` `x` ON ((`w`.`idstorage` = `x`.`idstorage`))) LEFT JOIN `emp` `y` ON ((`a`.`archv_by` = `y`.`emp_id`))) LEFT JOIN `storages` `z` ON ((`y`.`idstorage` = `z`.`idstorage`))) ORDER BY `a`.`last_updated` DESC ___________________________________________________________________________________ dt: 10/21/2024 CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_emp` AS SELECT `a`.`emp_id` AS `emp_id`, `a`.`idstorage` AS `idstorage`, `e`.`file_name` AS `emp_fnm`, `a`.`firstname` AS `firstname`, `a`.`middlename` AS `middlename`, `a`.`lastname` AS `lastname`, CONCAT(IFNULL(`a`.`firstname`, ''), ' ', IFNULL(`a`.`middlename`, ''), ' ', IFNULL(`a`.`lastname`, '')) AS `fullname`, `a`.`nickname` AS `nickname`, `b`.`user_name` AS `user_name`, IFNULL(`b`.`user_name`, 'No Login') AS `user_name_nm`, IF(ISNULL(`b`.`user_name`), 'No', 'Yes') AS `user_name_cur_stat`, `b`.`status` AS `status`, `b`.`expire_tm` AS `expire_tm`, `b`.`last_clicked` AS `last_clicked`, `b`.`url_fwd` AS `url_fwd`, TIMESTAMPDIFF(MINUTE, `f`.`lastlogin`, `b`.`last_clicked`) AS `last_clicked_tm`, `a`.`all_contacts` AS `all_contacts`, IF(ISNULL(`a`.`all_contacts`), 'No', 'Yes') AS `all_contacts_nm`, `a`.`unique_id_type` AS `unique_id`, IFNULL(`c`.`lk_val`, 'NA') AS `unique_id_nm`, `a`.`ssn` AS `ssn`, IFNULL(`a`.`ssn`, 'No SSN') AS `ssn_nm`, `a`.`dob` AS `dob`, IFNULL(DATE_FORMAT(`a`.`dob`, '%m-%d-%y'), 'No DOB') AS `dob_fmt`, IFNULL(TIMESTAMPDIFF(YEAR, `a`.`dob`, CURDATE()), 'No DOB') AS `age`, `a`.`gender` AS `gender_id`, IFNULL(`d`.`lk_val`, 'NA') AS `gender_nm`, `a`.`all_features` AS `all_features`, `a`.`cnt_feature` AS `cnt_feature`, `a`.`all_education` AS `all_education`, IF(ISNULL(`a`.`all_education`), 'No', 'Yes') AS `all_education_nm`, `a`.`education_cnt` AS `education_cnt`, `a`.`all_emp_org` AS `all_emp_org`, IF(ISNULL(`a`.`all_emp_org`), 'No', 'Yes') AS `all_emp_org_nm`, `a`.`all_addresses` AS `all_addresses`, `a`.`sto_limit` AS `sto_limit`, (CASE WHEN (`a`.`sto_limit` < 1024) THEN CONCAT(`a`.`sto_limit`, ' B') WHEN ((`a`.`sto_limit` >= 1024) AND (`a`.`sto_limit` < 1048576)) THEN CONVERT( CONCAT(ROUND((`a`.`sto_limit` / 1024), 2), ' KB') USING LATIN1) WHEN ((`a`.`sto_limit` >= 1048576) AND (`a`.`sto_limit` < 1073741824)) THEN CONVERT( CONCAT(ROUND(((`a`.`sto_limit` / 1024) / 1024), 2), ' MB') USING LATIN1) WHEN (`a`.`sto_limit` >= 1073741824) THEN CONVERT( CONCAT(ROUND((((`a`.`sto_limit` / 1024) / 1024) / 1024), 2), ' GB') USING LATIN1) END) AS `sto_limit_fmt`, `a`.`sto_usage` AS `sto_usage`, (CASE WHEN (`a`.`sto_usage` < 1024) THEN CONCAT(`a`.`sto_usage`, ' B') WHEN ((`a`.`sto_usage` >= 1024) AND (`a`.`sto_usage` < 1048576)) THEN CONVERT( CONCAT(ROUND((`a`.`sto_usage` / 1024), 2), ' KB') USING LATIN1) WHEN ((`a`.`sto_usage` >= 1048576) AND (`a`.`sto_usage` < 1073741824)) THEN CONVERT( CONCAT(ROUND(((`a`.`sto_usage` / 1024) / 1024), 2), ' MB') USING LATIN1) WHEN (`a`.`sto_usage` >= 1073741824) THEN CONVERT( CONCAT(ROUND((((`a`.`sto_usage` / 1024) / 1024) / 1024), 2), ' GB') USING LATIN1) END) AS `sto_usage_fmt`, `a`.`sto_remain` AS `sto_remain`, (CASE WHEN (`a`.`sto_remain` < 1024) THEN CONCAT(`a`.`sto_remain`, ' B') WHEN ((`a`.`sto_remain` >= 1024) AND (`a`.`sto_remain` < 1048576)) THEN CONVERT( CONCAT(ROUND((`a`.`sto_remain` / 1024), 2), ' KB') USING LATIN1) WHEN ((`a`.`sto_remain` >= 1048576) AND (`a`.`sto_remain` < 1073741824)) THEN CONVERT( CONCAT(ROUND(((`a`.`sto_remain` / 1024) / 1024), 2), ' MB') USING LATIN1) WHEN (`a`.`sto_remain` >= 1073741824) THEN CONVERT( CONCAT(ROUND((((`a`.`sto_remain` / 1024) / 1024) / 1024), 2), ' GB') USING LATIN1) END) AS `sto_remain_fmt`, CONCAT((CASE WHEN (`a`.`sto_usage` < 1024) THEN CONCAT(`a`.`sto_usage`, ' B') WHEN ((`a`.`sto_usage` >= 1024) AND (`a`.`sto_usage` < 1048576)) THEN CONVERT( CONCAT(ROUND((`a`.`sto_usage` / 1024), 2), ' KB') USING LATIN1) WHEN ((`a`.`sto_usage` >= 1048576) AND (`a`.`sto_usage` < 1073741824)) THEN CONVERT( CONCAT(ROUND(((`a`.`sto_usage` / 1024) / 1024), 2), ' MB') USING LATIN1) WHEN (`a`.`sto_usage` >= 1073741824) THEN CONVERT( CONCAT(ROUND((((`a`.`sto_usage` / 1024) / 1024) / 1024), 2), ' GB') USING LATIN1) END), ' of ', (CASE WHEN (`a`.`sto_limit` < 1024) THEN CONCAT(`a`.`sto_limit`, ' B') WHEN ((`a`.`sto_limit` >= 1024) AND (`a`.`sto_limit` < 1048576)) THEN CONVERT( CONCAT(ROUND((`a`.`sto_limit` / 1024), 2), ' KB') USING LATIN1) WHEN ((`a`.`sto_limit` >= 1048576) AND (`a`.`sto_limit` < 1073741824)) THEN CONVERT( CONCAT(ROUND(((`a`.`sto_limit` / 1024) / 1024), 2), ' MB') USING LATIN1) WHEN (`a`.`sto_limit` >= 1073741824) THEN CONVERT( CONCAT(ROUND((((`a`.`sto_limit` / 1024) / 1024) / 1024), 2), ' GB') USING LATIN1) END)) AS `sto_usage_nm`, CONCAT((CASE WHEN (`a`.`sto_remain` < 1024) THEN CONCAT(`a`.`sto_remain`, ' B') WHEN ((`a`.`sto_remain` >= 1024) AND (`a`.`sto_remain` < 1048576)) THEN CONVERT( CONCAT(ROUND((`a`.`sto_remain` / 1024), 2), ' KB') USING LATIN1) WHEN ((`a`.`sto_remain` >= 1048576) AND (`a`.`sto_remain` < 1073741824)) THEN CONVERT( CONCAT(ROUND(((`a`.`sto_remain` / 1024) / 1024), 2), ' MB') USING LATIN1) WHEN (`a`.`sto_remain` >= 1073741824) THEN CONVERT( CONCAT(ROUND((((`a`.`sto_remain` / 1024) / 1024) / 1024), 2), ' GB') USING LATIN1) END), ' of ', (CASE WHEN (`a`.`sto_limit` < 1024) THEN CONCAT(`a`.`sto_limit`, ' B') WHEN ((`a`.`sto_limit` >= 1024) AND (`a`.`sto_limit` < 1048576)) THEN CONVERT( CONCAT(ROUND((`a`.`sto_limit` / 1024), 2), ' KB') USING LATIN1) WHEN ((`a`.`sto_limit` >= 1048576) AND (`a`.`sto_limit` < 1073741824)) THEN CONVERT( CONCAT(ROUND(((`a`.`sto_limit` / 1024) / 1024), 2), ' MB') USING LATIN1) WHEN (`a`.`sto_limit` >= 1073741824) THEN CONVERT( CONCAT(ROUND((((`a`.`sto_limit` / 1024) / 1024) / 1024), 2), ' GB') USING LATIN1) END)) AS `sto_remaining`, CONCAT(ROUND(((`a`.`sto_usage` / `a`.`sto_limit`) * 100), 2), '%') AS `sto_usage_nm_pcnt`, CONCAT(ROUND(((`a`.`sto_remain` / `a`.`sto_limit`) * 100), 2), '%') AS `sto_remain_nm_pcnt`, `a`.`taxid` AS `taxid`, IFNULL(`a`.`taxid`, 'No TAXID') AS `taxid_nm`, IFNULL(`a`.`hrs_forecast`, '0.00') AS `hrs_forecast`, IFNULL(`a`.`hrs_actual`, '0.00') AS `hrs_actual`, CONCAT((CASE WHEN ISNULL(`a`.`hrs_actual`) THEN '0.00' WHEN (`a`.`hrs_actual` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`hrs_actual` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`hrs_actual` < 1000) THEN `a`.`hrs_actual` END), ' of ', (CASE WHEN ISNULL(`a`.`hrs_forecast`) THEN '0.00' WHEN (`a`.`hrs_forecast` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`hrs_forecast` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`hrs_forecast` < 1000) THEN `a`.`hrs_forecast` END)) AS `hrs_actual_forecast`, IFNULL(`a`.`fore_act_pcnt`, '0.00') AS `fore_act_pcnt`, IFNULL(`a`.`amt_forcast`, '0.00') AS `amt_forcast`, IFNULL(`a`.`amt_actual`, '0.00') AS `amt_actual`, CONCAT((CASE WHEN ISNULL(`a`.`amt_actual`) THEN '0.00' WHEN (`a`.`amt_actual` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`amt_actual` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`amt_actual` < 1000) THEN `a`.`amt_actual` END), ' of ', (CASE WHEN ISNULL(`a`.`amt_forcast`) THEN '0.00' WHEN (`a`.`amt_forcast` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`amt_forcast` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`amt_forcast` < 1000) THEN `a`.`amt_forcast` END)) AS `amt_actual_forcast`, IFNULL(`a`.`amt_pcnt`, '0.00') AS `amt_pcnt`, IFNULL(`a`.`vac_earn`, '0.00') AS `vac_earned`, IFNULL(`a`.`vac_use`, '0.00') AS `vac_used`, CONCAT((CASE WHEN ISNULL(`a`.`vac_use`) THEN '0.00' WHEN (`a`.`vac_use` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`vac_use` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`vac_use` < 1000) THEN `a`.`vac_use` END), ' of ', (CASE WHEN ISNULL(`a`.`vac_earn`) THEN '0.00' WHEN (`a`.`vac_earn` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`vac_earn` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`vac_earn` < 1000) THEN `a`.`vac_earn` END)) AS `vac_used_earned`, IFNULL(`a`.`vac_pcnt`, '0.00') AS `vac_pcnt`, IFNULL(`a`.`vac_bal`, '0.00') AS `vac_bal`, `a`.`login_id` AS `login_id`, `g`.`domain_nm` AS `domain_name`, `g`.`ip_contry` AS `contry`, `g`.`ip_state` AS `ip_state`, `g`.`ip_city` AS `ip_city`, `g`.`ip_post_code` AS `ip_post_code`, `f`.`lastlogin` AS `lastlogin`, DATE_FORMAT(`f`.`lastlogin`, '%m-%d-%y %h:%i %p') AS `lastlogin_fmt`, `f`.`lastlogout` AS `lastlogout`, DATE_FORMAT(`f`.`lastlogout`, '%m-%d-%y %h:%i %p') AS `lastlogout_fmt`, CONCAT(IFNULL(`g`.`ip_address`, ''), ' ', IFNULL(`g`.`domain_nm`, ''), ' ', IFNULL(`g`.`ip_contry`, ''), ' ', IFNULL(`g`.`ip_state`, ''), ' ', IFNULL(`g`.`ip_city`, ''), ' ', IFNULL(`g`.`ip_post_code`, '')) AS `from_fmt`, IFNULL(CONCAT(ROUND(((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`f`.`lastlogout`)) / 3600), 0), ':', ROUND((ROUND(((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`f`.`lastlogout`)) % 3600), 0) / 60), 0)), 'Live') AS `loggged_out`, `f`.`logged_in` AS `logged_in`, TIMESTAMPDIFF(MINUTE, `f`.`lastlogin`, `f`.`lastlogout`) AS `logged_in_tm`, `h`.`idlook_menu_pvt_lvl1` AS `idlook_menu_pvt_lvl1`, `i`.`lk_values` AS `main_menu`, `h`.`idlook_menu_pvt_lvl2` AS `idlook_menu_pvt_lvl2`, `j`.`lk_values` AS `sub_menu`, `a`.`last_updated` AS `last_updated`, DATE_FORMAT(`a`.`last_updated`, '%m-%d-%y %h:%i %p') AS `last_updated_fmt`, `a`.`updater` AS `updater`, `l`.`file_name` AS `updater_fnm`, IFNULL(CONCAT(`k`.`firstname`, ' ', `k`.`lastname`), 'Admin') AS `updater_nm`, `a`.`del_dt` AS `del_dt`, DATE_FORMAT(`a`.`del_dt`, '%m-%d-%y %h:%i %p') AS `del_dt_fmt`, `a`.`del_by` AS `del_by`, `n`.`file_name` AS `del_by_fnm`, IFNULL(CONCAT(`m`.`firstname`, ' ', `m`.`lastname`), 'Admin') AS `del_by_nm`, `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 `del_day_vir`, `a`.`archv_dt` AS `archv_dt`, DATE_FORMAT(`a`.`archv_dt`, '%m-%d-%y %h:%i %p') AS `archv_dt_fmt`, `a`.`archv_by` AS `archv_by`, `p`.`file_name` AS `archv_by_fnm`, IFNULL(CONCAT(`o`.`firstname`, ' ', `o`.`lastname`), 'Admin') AS `archv_by_nm` FROM (((((((((((((((`emp` `a` LEFT JOIN `all_users` `b` ON ((`a`.`emp_id` = `b`.`uid_id`))) LEFT JOIN `emp_lk_val` `c` ON ((`a`.`unique_id_type` = `c`.`idemp_lk_value`))) LEFT JOIN `emp_lk_val` `d` ON ((`a`.`gender` = `d`.`idemp_lk_value`))) LEFT JOIN `storages` `e` ON ((`a`.`idstorage` = `e`.`idstorage`))) LEFT JOIN `all_usr_login` `f` ON ((`a`.`login_id` = `f`.`idall_usr_login`))) LEFT JOIN `look_ip_addr` `g` ON ((`f`.`idlook_ip_addr` = `g`.`idlook_ip_addr`))) LEFT JOIN `all_usr_login` `h` ON ((`a`.`login_id` = `h`.`idall_usr_login`))) LEFT JOIN `look_menu_pvt` `i` ON ((`h`.`idlook_menu_pvt_lvl1` = `i`.`idlook_menu_pvt`))) LEFT JOIN `look_menu_pvt` `j` ON ((`h`.`idlook_menu_pvt_lvl2` = `j`.`idlook_menu_pvt`))) LEFT JOIN `emp` `k` ON ((`a`.`updater` = `k`.`emp_id`))) LEFT JOIN `storages` `l` ON ((`k`.`idstorage` = `l`.`idstorage`))) LEFT JOIN `emp` `m` ON ((`a`.`del_by` = `m`.`emp_id`))) LEFT JOIN `storages` `n` ON ((`m`.`idstorage` = `n`.`idstorage`))) LEFT JOIN `emp` `o` ON ((`a`.`archv_by` = `o`.`emp_id`))) LEFT JOIN `storages` `p` ON ((`o`.`idstorage` = `p`.`idstorage`))) ORDER BY `a`.`last_updated` _______________________________________________________________________ 09/27/2024 CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_net_ser` AS SELECT `a`.`idnet_ser` AS `idnet_ser`, `a`.`hostnm` AS `hostnm`, `a`.`dep_usr` AS `dep_usr`, `a`.`host_conf` AS `host_conf`, `a`.`hosted` AS `hosted`, `a`.`hostdm` AS `hostdm`, `a`.`hostdm_multi` AS `hostdm_multi`, CONCAT(IFNULL(`a`.`hostdm`, ' '), ' ', IFNULL(`a`.`hostdm_multi`, ' ')) AS `etc_hosts`, `a`.`host_type` AS `host_type`, `a`.`host_mst` AS `host_mst`, `a`.`ip_lan` AS `ip_lan`, `a`.`ip_wan` AS `ip_wan`, `a`.`ip_usr` AS `ip_usr`, `a`.`gateway` AS `gateway`, `a`.`updater` AS `updater`, `a`.`updated_on` AS `updated_on`, `b`.`hostnm` AS `mst_hostnm`, `b`.`hostdm` AS `mst_hostdm`, `b`.`ip_lan` AS `mst_ip_lan`, `b`.`ip_wan` AS `mst_ip_wan`, `a`.`cnt_ser_blk` AS `cnt_ser_blk`, `a`.`cnt_db_blk` AS `cnt_db_blk`, `a`.`dis_tot` AS `dis_tot`, `a`.`dis_use` AS `dis_use`, `a`.`dis_free` AS `dis_free`, `a`.`dis_pcnt` AS `dis_pcnt` FROM (`net_ser` `a` LEFT JOIN `net_ser` `b` ON ((`a`.`host_mst` = `b`.`hostnm`))) ________________________________________________________________________________________________________ SELECT `a`.`idemp_lk_value` AS `idemp_lk_value`, NULL AS `idemp_lk`, a.lk_val_par, `a`.`lk_val` AS `emp_value`, null as child_lvl, `a`.`lk_val_desc` AS `emp_value_desc`, `a`.`disp_order` AS `disp_order`, `a`.`idemp_lk` AS `parent_id`, `a`.`updater` AS `updater`, `d`.`file_name` AS `updater_fnm`, IFNULL(CONCAT(`b`.`firstname`, ' ', `b`.`lastname`), 'Admin') AS `updater_fmt`, `a`.`updated` AS `updated`, IF(ISNULL(`a`.`updated`), 'Never', DATE_FORMAT(`a`.`updated`, '%m-%d-%y %h:%i %p')) AS `updated_fmt`, CONCAT(IFNULL(CONCAT(`b`.`firstname`, ' ', `b`.`lastname`), 'Admin'), ' ', CONVERT( IF(ISNULL(`a`.`updated`), 'Never', DATE_FORMAT(`a`.`updated`, '%m-%d-%y %h:%i %p')) USING LATIN1)) AS `updated_by` FROM (((`emp_lk_val` `a` LEFT JOIN `emp` `b` ON ((`a`.`updater` = `b`.`emp_id`))) LEFT JOIN `storages` `d` ON ((`d`.`idstorage` = `b`.`idstorage`))) LEFT JOIN `emp_lk` `c` ON ((`a`.`idemp_lk` = `c`.`idemp_lk`))) UNION ALL SELECT NULL AS `NULL`, `a`.`idemp_lk` AS `idemp_lk`, null as lk_val_par, `a`.`lk_nm` AS `lk_nm`, IF((a.child_lvl = 0), 1, IFNULL(a.child_lvl, 1)) AS child_lvl, `a`.`lk_nm_desc` AS `lk_nm_desc`, `a`.`disp_order` AS `disp_order`, NULL AS `NULL`, `a`.`updater` AS `updater`, `d`.`file_name` AS `updater_fnm`, IFNULL(CONCAT(`b`.`firstname`, ' ', `b`.`lastname`), 'Admin') AS `updater_fmt`, `a`.`last_updated` AS `last_updated`, IF(ISNULL(`a`.`last_updated`), 'Never', DATE_FORMAT(`a`.`last_updated`, '%m-%d-%y %h:%i %p')) AS `last_updated_fmt`, CONCAT(IFNULL(CONCAT(`b`.`firstname`, ' ', `b`.`lastname`), 'Admin'), ' ', CONVERT( IF(ISNULL(`a`.`last_updated`), 'Never', DATE_FORMAT(`a`.`last_updated`, '%m-%d-%y %h:%i %p')) USING LATIN1)) AS `last_updated_by` FROM ((`emp_lk` `a` LEFT JOIN `emp` `b` ON ((`a`.`updater` = `b`.`emp_id`))) LEFT JOIN `storages` `d` ON ((`d`.`idstorage` = `b`.`idstorage`))) ____________________________________________________________________________________________ CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_dev_est_reqs_status_forcast` AS SELECT `a`.`iddev_est_reqs_status_forcast` AS `iddev_est_reqs_status_forcast`, `e`.`iddev_apps` AS `iddev_apps`, `e`.`app_name` AS `app_name`, `e`.`iddev_est` AS `iddev_est`, `e`.`est_type` AS `est_type`, `e`.`category_nm` AS `category_nm`, `e`.`est_ref_id` AS `est_ref_id`, `e`.`est_fnm` AS `est_fnm`, `e`.`est_ref_nm` AS `est_ref_nm`, `e`.`owner_ref_id` AS `owner_ref_id`, `e`.`owner_fnm` AS `owner_fnm`, `e`.`owner_ref_nm` AS `owner_ref_nm`, `e`.`dev_est_smry` AS `dev_est_smry`, `a`.`iddev_est_reqs` AS `iddev_est_reqs`, `e`.`req_desc` AS `req_desc`, `e`.`req_st_dt` AS `req_st_dt`, `e`.`req_st_dt_fmt` AS `req_st_dt_fmt`, `e`.`req_e_dt` AS `req_e_dt`, `e`.`req_e_dt_fmt` AS `req_e_dt_fmt`, `e`.`req_time` AS `req_time`, `e`.`req_time_vir` AS `req_time_vir`, `e`.`req_type` AS `req_type`, `e`.`req_type_nm` AS `req_type_nm`, `e`.`skill_lvl` AS `skill_lvl`, `e`.`skill_lvl_nm` AS `skill_lvl_nm`, `a`.`iddev_est_reqs_status` AS `iddev_est_reqs_status`, IF(ISNULL(`a`.`iddev_est_reqs_status`), 'Pending', 'Started') AS `iddev_est_reqs_status_nm`, `a`.`req_status` AS `req_status`, `b`.`look_value` AS `req_status_nm`, `a`.`idlook_stat_order` AS `idlook_stat_order`, `a`.`for_stat_e_dt_pre` AS `for_stat_e_dt_pre`, DATE_FORMAT(`a`.`for_stat_e_dt_pre`, '%m-%d-%y %h:%i %p') AS `for_stat_e_dt_pre_fmt`, `a`.`for_stat_lag_time` AS `for_lag_time`, IFNULL(`a`.`for_stat_lag_time`, '0') AS `for_lag_time_vir`, `a`.`for_stat_st_dt` AS `for_stat_st_dt`, IF(ISNULL(`a`.`for_stat_st_dt`), 'Not Started', DATE_FORMAT(`a`.`for_stat_st_dt`, '%m-%d-%y %h:%i %p')) AS `for_stat_st_dt_fmt`, `a`.`for_stat_e_dt` AS `for_stat_e_dt`, (CASE WHEN (`a`.`for_stat_e_dt` IS NOT NULL) THEN DATE_FORMAT(`a`.`for_stat_e_dt`, '%m-%d-%y %h:%i %p') WHEN (ISNULL(`a`.`for_stat_e_dt`) AND ISNULL(`a`.`for_stat_st_dt`)) THEN 'Not Started' WHEN (ISNULL(`a`.`for_stat_e_dt`) AND (`a`.`for_stat_st_dt` IS NOT NULL)) THEN 'In Progress' END) AS `for_stat_e_dt_fmt`, `a`.`for_stat_time` AS `for_stat_time`, IFNULL(`a`.`for_stat_time`, TIMESTAMPDIFF(MINUTE, `a`.`for_stat_st_dt`, NOW())) AS `for_stat_time_vir`, `a`.`for_req_bud_amt` AS `for_req_bud_amt`, IF(ISNULL(`a`.`for_req_bud_amt`), 'No', 'Yes') AS `for_req_bud_amt_nm`, `j`.`req_stat_amt` AS `req_stat_amt`, IF(ISNULL(`j`.`req_stat_amt`), 'No', 'Yes') AS `req_stat_amt_nm`, CONCAT(IF(ISNULL(`j`.`req_stat_amt`), '0.00', (CASE WHEN ((`j`.`req_stat_amt` > 999) AND (`j`.`req_stat_amt` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`j`.`req_stat_amt` < 1000) THEN `j`.`req_stat_amt` WHEN ((`j`.`req_stat_amt` > 999999) AND (`j`.`req_stat_amt` < 1000000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_amt` / 1000000), 2)) + 0), 'M') USING LATIN1) WHEN (`j`.`req_stat_amt` > 999999999) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_amt` / 1000000000), 2)) + 0), 'B') USING LATIN1) END)), ' of ', IF(ISNULL(`a`.`for_req_bud_amt`), '0.00', (CASE WHEN ((`a`.`for_req_bud_amt` > 999) AND (`a`.`for_req_bud_amt` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`for_req_bud_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`a`.`for_req_bud_amt` < 1000) THEN `a`.`for_req_bud_amt` WHEN ((`a`.`for_req_bud_amt` > 999999) AND (`a`.`for_req_bud_amt` < 1000000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`for_req_bud_amt` / 1000000), 2)) + 0), 'M') USING LATIN1) WHEN (`a`.`for_req_bud_amt` > 999999999) THEN CONVERT( CONCAT((TRIM(ROUND((`a`.`for_req_bud_amt` / 1000000000), 2)) + 0), 'B') USING LATIN1) END))) AS `for_req_bud_stat_amt`, IFNULL(CONCAT(ROUND(((`j`.`req_stat_amt` * 100) / `a`.`for_req_bud_amt`), 2), ' ', '%'), '0%') AS `for_req_bud_stat_amt_pcnt`, `j`.`req_stat_paid` AS `req_stat_paid`, IF(ISNULL(`j`.`req_stat_paid`), 'No', 'Yes') AS `req_stat_paid_nm`, CONCAT(IF(ISNULL(`j`.`req_stat_paid`), '0.00', (CASE WHEN ((`j`.`req_stat_paid` > 999) AND (`j`.`req_stat_paid` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_paid` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`j`.`req_stat_paid` < 1000) THEN `j`.`req_stat_paid` WHEN ((`j`.`req_stat_paid` > 999999) AND (`j`.`req_stat_paid` < 1000000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_paid` / 1000000), 2)) + 0), 'M') USING LATIN1) WHEN (`j`.`req_stat_paid` > 999999999) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_paid` / 1000000000), 2)) + 0), 'B') USING LATIN1) END)), ' of ', IF(ISNULL(`j`.`req_stat_amt`), '0.00', (CASE WHEN ((`j`.`req_stat_amt` > 999) AND (`j`.`req_stat_amt` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`j`.`req_stat_amt` < 1000) THEN `j`.`req_stat_amt` WHEN ((`j`.`req_stat_amt` > 999999) AND (`j`.`req_stat_amt` < 1000000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_amt` / 1000000), 2)) + 0), 'M') USING LATIN1) WHEN (`j`.`req_stat_amt` > 999999999) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_amt` / 1000000000), 2)) + 0), 'B') USING LATIN1) END))) AS `req_stat_paid_amt`, IFNULL(CONCAT(ROUND(((`j`.`req_stat_paid` * 100) / `j`.`req_stat_amt`), 2), ' ', '%'), '0%') AS `req_stat_paid_amt_pcnt`, `j`.`req_stat_bal` AS `req_stat_bal`, IF(ISNULL(`j`.`req_stat_bal`), 'No', 'Yes') AS `req_stat_bal_nm`, CONCAT(IF(ISNULL(`j`.`req_stat_bal`), '0.00', (CASE WHEN ((`j`.`req_stat_bal` > 999) AND (`j`.`req_stat_bal` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_bal` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`j`.`req_stat_bal` < 1000) THEN `j`.`req_stat_bal` WHEN ((`j`.`req_stat_bal` > 999999) AND (`j`.`req_stat_bal` < 1000000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_bal` / 1000000), 2)) + 0), 'M') USING LATIN1) WHEN (`j`.`req_stat_bal` > 999999999) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_bal` / 1000000000), 2)) + 0), 'B') USING LATIN1) END)), ' of ', IF(ISNULL(`j`.`req_stat_amt`), '0.00', (CASE WHEN ((`j`.`req_stat_amt` > 999) AND (`j`.`req_stat_amt` < 1000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_amt` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`j`.`req_stat_amt` < 1000) THEN `j`.`req_stat_amt` WHEN ((`j`.`req_stat_amt` > 999999) AND (`j`.`req_stat_amt` < 1000000000)) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_amt` / 1000000), 2)) + 0), 'M') USING LATIN1) WHEN (`j`.`req_stat_amt` > 999999999) THEN CONVERT( CONCAT((TRIM(ROUND((`j`.`req_stat_amt` / 1000000000), 2)) + 0), 'B') USING LATIN1) END))) AS `req_stat_bal_amt`, IFNULL(CONCAT(ROUND(((`j`.`req_stat_bal` * 100) / `j`.`req_stat_amt`), 2), ' ', '%'), '0%') AS `req_stat_bal_amt_pcnt`, `a`.`hist_cnt` AS `hist_cnt`, `a`.`assigned_to` AS `assigned_to`, `y`.`file_name` AS `assigned_to_fnm`, IFNULL(CONCAT(`d`.`firstname`, ' ', `d`.`lastname`), 'No Assignee') AS `assigned_to_nm`, `a`.`updater` AS `updater`, `z`.`file_name` AS `updater_fnm`, IFNULL(CONCAT(`c`.`firstname`, ' ', `c`.`lastname`), 'Admin') AS `updater_nm`, `a`.`last_updated` AS `last_updated`, DATE_FORMAT(`a`.`last_updated`, '%m-%d-%y %h:%i %p') AS `last_updated_fmt`, `a`.`del_dt` AS `del_dt`, DATE_FORMAT(`a`.`del_dt`, '%m-%d-%y %h:%i %p') AS `del_dt_fmt`, IFNULL(DATE_FORMAT(`a`.`del_dt`, '%Y'), 'Not Deleted') AS `del_dt_yr`, IFNULL(DATE_FORMAT(`a`.`del_dt`, '%M'), 'Not Deleted') AS `del_dt_mon`, `a`.`del_by` AS `del_by`, `g`.`file_name` AS `del_by_fnm`, IF(ISNULL(`a`.`del_by`), 'Admin', CONCAT(`f`.`firstname`, ' ', `f`.`lastname`)) AS `del_by_nm`, `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 `del_day_vir`, `a`.`archv_dt` AS `archv_dt`, DATE_FORMAT(`a`.`archv_dt`, '%m-%d-%y %h:%i %p') AS `archv_dt_fmt`, IFNULL(DATE_FORMAT(`a`.`archv_dt`, '%Y'), 'Archived') AS `archv_dt_yr`, IFNULL(DATE_FORMAT(`a`.`archv_dt`, '%M'), 'Not Archived') AS `archv_dt_mon`, `a`.`archv_by` AS `archv_by`, `i`.`file_name` AS `archv_by_fnm`, IF(ISNULL(`a`.`archv_by`), 'Admin', CONCAT(`h`.`firstname`, ' ', `h`.`lastname`)) AS `archv_by_nm` FROM (((((((((((`dev_est_reqs_status_forcast` `a` LEFT JOIN `dev_look_value` `b` ON ((`a`.`req_status` = `b`.`iddev_look_value`))) LEFT JOIN `emp` `c` ON ((`a`.`updater` = `c`.`emp_id`))) LEFT JOIN `storages` `z` ON ((`z`.`idstorage` = `c`.`idstorage`))) LEFT JOIN `emp` `d` ON ((`a`.`assigned_to` = `d`.`emp_id`))) LEFT JOIN `storages` `y` ON ((`y`.`idstorage` = `d`.`idstorage`))) LEFT JOIN `vw_dev_est_reqs` `e` ON ((`a`.`iddev_est_reqs` = `e`.`iddev_est_reqs`))) LEFT JOIN `emp` `f` ON ((`a`.`del_by` = `f`.`emp_id`))) LEFT JOIN `storages` `g` ON ((`f`.`idstorage` = `g`.`idstorage`))) LEFT JOIN `emp` `h` ON ((`a`.`archv_by` = `h`.`emp_id`))) LEFT JOIN `storages` `i` ON ((`h`.`idstorage` = `i`.`idstorage`))) LEFT JOIN `dev_est_reqs_status` `j` ON ((`a`.`iddev_est_reqs_status` = `j`.`iddev_est_reqs_status`))) ____________________________________________________________________________________________