08/08/2025 vw_look_addresses Old defination CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_look_addresses` AS SELECT `a`.`country_id` AS `country_id`, IFNULL(`a`.`con_name`, 'No Country') AS `con_name`, `a`.`con_full_name` AS `con_full_name`, IFNULL(`a`.`con_digit`, 'No Country Full Name') AS `con_full_nm`, `a`.`lk_design` AS `lk_design`, `a`.`lk_design_hex` AS `lk_design_hex`, IFNULL(`a`.`lk_design_hex`, ' - ') AS `lk_design_hex_nm`, `a`.`con_code` AS `con_code`, IFNULL(`a`.`con_code`, 'No Country Code') AS `con_code_nm`, `a`.`con_digit` AS `con_digit`, IFNULL(`a`.`con_digit`, 'No Country digit') AS `con_digit_nm`, `b`.`state_id` AS `state_id`, IFNULL(`b`.`state_name`, 'No State') AS `state_name`, `b`.`state_full_name` AS `state_full_name`, `c`.`city_id` AS `city_id`, IFNULL(`c`.`city_name`, 'No City') AS `city_name`, IFNULL(`d`.`postal_id`, 'No Postal Id') AS `postal_id`, IFNULL(`d`.`zip_code`, 'No Zip Code') AS `zip_code`, `e`.`look_street_id` AS `look_street_id`, IFNULL(`e`.`street_name`, 'No Street Name') AS `street_name`, `f`.`look_street_no_id` AS `look_street_no_id`, IFNULL(`f`.`look_street_no`, 'No Street') AS `look_street_no`, IFNULL(`e`.`street_type`, 'No Street Type') AS `street_type`, IFNULL(`g`.`lk_val`, 'No Street Type') AS `street_type_name`, CONCAT(IFNULL(`e`.`street_name`, 'No Street Name'), ' ', IFNULL(`g`.`lk_val`, 'No Street Type')) AS `street_name_type`, CONCAT(IFNULL(`f`.`look_street_no`, 'No Street '), ' ', IFNULL(`e`.`street_name`, 'No Street, '), ' ', IFNULL(`g`.`lk_val`, 'No Street Type'), ', ', IFNULL(`c`.`city_name`, 'No City, '), ', ', IFNULL(`b`.`state_name`, 'No State, '), ' ', IFNULL(`d`.`zip_code`, 'No Zip Code, '), ' ', '(', IFNULL(`a`.`con_name`, 'No Country'), ')') AS `addresses` FROM ((((((`look_country` `a` LEFT JOIN `look_state` `b` ON ((`a`.`country_id` = `b`.`country_id`))) LEFT JOIN `look_city` `c` ON ((`b`.`state_id` = `c`.`state_id`))) LEFT JOIN `look_postal` `d` ON ((`c`.`city_id` = `d`.`city_id`))) LEFT JOIN `look_street` `e` ON ((`d`.`postal_id` = `e`.`postal_id`))) LEFT JOIN `look_street_no` `f` ON ((`e`.`look_street_id` = `f`.`look_street_id`))) LEFT JOIN `feature_lk_val` `g` ON ((`e`.`street_type` = `g`.`idfeature_lk_val`))) 08/08/2025 vw_addresses_old Old defination. CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_addresses_old` AS SELECT `a`.`country_id` AS `country_id`, IFNULL(`a`.`con_name`, 'No Country') AS `con_name`, `a`.`con_full_name` AS `con_full_name`, `a`.`con_code` AS `con_code`, `a`.`lk_design` AS `lk_design`, `a`.`lk_design_hex` AS `lk_design_hex`, `a`.`con_digit` AS `con_digit`, `b`.`state_id` AS `state_id`, IFNULL(`b`.`state_name`, 'No State') AS `state_name`, `b`.`state_full_name` AS `state_full_name`, `c`.`city_id` AS `city_id`, IFNULL(`c`.`city_name`, 'No City') AS `city_name`, IFNULL(`d`.`postal_id`, 'No Postal Id') AS `postal_id`, IFNULL(`d`.`zip_code`, 'No Zip Code') AS `zip_code`, `e`.`look_street_id` AS `look_street_id`, IFNULL(`e`.`street_name`, 'No Street Name') AS `street_name`, `f`.`look_street_no_id` AS `look_street_no_id`, IFNULL(`f`.`look_street_no`, 'No Street') AS `look_street_no`, IFNULL(`e`.`street_type`, 'No Street Type') AS `street_type`, IFNULL(`g`.`lk_val`, 'No Street Type') AS `street_type_nm`, CONCAT(IFNULL(`e`.`street_name`, 'No Street Name'), ' ', IFNULL(`g`.`lk_val`, 'No Street Type')) AS `street_name_type`, CONCAT(IFNULL(`f`.`look_street_no`, 'No Street '), ' ', IFNULL(`e`.`street_name`, 'No Street, '), ' ', IFNULL(`g`.`lk_val`, 'No Street Type'), ', ', IFNULL(`c`.`city_name`, 'No City, '), ', ', IFNULL(`b`.`state_name`, 'No State, '), ' ', IFNULL(`d`.`zip_code`, 'No Zip Code, '), ' ', '(', IFNULL(`a`.`con_name`, 'No Country'), ')') AS `addresses` FROM ((((((`look_country` `a` LEFT JOIN `look_state` `b` ON ((`a`.`country_id` = `b`.`country_id`))) LEFT JOIN `look_city` `c` ON ((`b`.`state_id` = `c`.`state_id`))) LEFT JOIN `look_postal` `d` ON ((`c`.`city_id` = `d`.`city_id`))) LEFT JOIN `look_street` `e` ON ((`d`.`postal_id` = `e`.`postal_id`))) LEFT JOIN `look_street_no` `f` ON ((`e`.`look_street_id` = `f`.`look_street_id`))) LEFT JOIN `feature_lk_val` `g` ON ((`e`.`street_type` = `g`.`idfeature_lk_val`))) __________________________________________________________ 08/05/2025 vw_comp_address old defination CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_comp_address` AS SELECT `a`.`comp_add_id` AS `comp_add_id`, `a`.`comp_id` AS `comp_id`, `g`.`file_name` AS `comp_logo`, `b`.`companyname` AS `companyname`, `a`.`look_street_no_id` AS `look_street_no_id`, `c`.`addresses` AS `addresses`, `a`.`suite_no` AS `suite_no`, `a`.`house_apt_type` AS `house_apt_type`, `d`.`lk_val` AS `house_apt_nm`, `a`.`add_type` AS `add_type`, `a`.`from_dt` AS `from_dt`, DATE_FORMAT(`a`.`from_dt`, '%Y') AS `from_dt_yr`, DATE_FORMAT(`a`.`from_dt`, '%M') AS `from_dt_mon`, DATE_FORMAT(`a`.`from_dt`, '%m-%d-%y') AS `from_dt_fmt`, `a`.`to_dt` AS `to_dt`, DATE_FORMAT(`a`.`to_dt`, '%Y') AS `to_dt_yr`, DATE_FORMAT(`a`.`to_dt`, '%M') AS `to_dt_mon`, DATE_FORMAT(`a`.`to_dt`, '%m-%d-%y') AS `to_dt_fmt`, `a`.`from_to_dt` AS `from_to_dt`, TIMESTAMPDIFF(DAY, `a`.`from_dt`, IFNULL(`a`.`to_dt`, NOW())) AS `from_to_dt_vir`, `a`.`updater` AS `updater`, `f`.`file_name` AS `updater_fnm`, CONCAT(`e`.`firstname`, ' ', `e`.`lastname`) AS `updater_nm`, `a`.`updated` AS `updated`, `a`.`last_updated` AS `last_updated`, DATE_FORMAT(`a`.`last_updated`, _UTF8'%m-%d-%y %h:%i %p') AS `last_updated_fmt`, `a`.`updated_by` AS `updated_by`, `a`.`del_dt` AS `del_dt`, DATE_FORMAT(`a`.`del_dt`, '%m-%d-%y %h:%i %p') AS `del_dt_fmt`, IFNULL(CONVERT( DATE_FORMAT(`a`.`del_dt`, '%Y') USING UTF8MB4), 'Not Deleted') AS `del_dt_yr`, IFNULL(CONVERT( DATE_FORMAT(`a`.`del_dt`, '%M') USING UTF8MB4), 'Not Deleted') AS `del_dt_mon`, `a`.`del_by` AS `del_by`, `k`.`file_name` AS `del_by_fnm`, IF(ISNULL(`a`.`del_by`), 'Admin', CONCAT(`j`.`firstname`, ' ', `j`.`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(CONVERT( DATE_FORMAT(`a`.`archv_dt`, '%Y') USING UTF8MB4), 'Not Archived') AS `archv_dt_yr`, IFNULL(CONVERT( DATE_FORMAT(`a`.`archv_dt`, '%M') USING UTF8MB4), 'Not Archived') AS `archv_dt_mon`, `a`.`archv_by` AS `archv_by`, `h`.`file_name` AS `archv_by_fnm`, IF(ISNULL(`a`.`archv_by`), 'Admin', CONCAT(`i`.`firstname`, ' ', `i`.`lastname`)) AS `archv_by_nm` FROM ((((((((((`comp_address` `a` LEFT JOIN `company` `b` ON ((`b`.`comp_id` = `a`.`comp_id`))) LEFT JOIN `storages` `g` ON ((`g`.`idstorages` = `b`.`idstorages`))) LEFT JOIN `vw_look_addresses` `c` ON ((`c`.`look_street_no_id` = `a`.`look_street_no_id`))) LEFT JOIN `feature_lk_val` `d` ON ((`d`.`idfeature_lk_val` = `a`.`house_apt_type`))) LEFT JOIN `emp` `e` ON ((`e`.`emp_id` = `a`.`updater`))) LEFT JOIN `storages` `f` ON ((`f`.`idstorages` = `e`.`idstorages`))) LEFT JOIN `emp` `j` ON ((`a`.`del_by` = `j`.`emp_id`))) LEFT JOIN `storages` `k` ON ((`k`.`idstorages` = `j`.`idstorages`))) LEFT JOIN `emp` `i` ON ((`a`.`archv_by` = `i`.`emp_id`))) LEFT JOIN `storages` `h` ON ((`h`.`idstorages` = `e`.`idstorages`))) ______________________________________________________ 08/05/2025 vw_emp_org_hrs_days old defination CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_emp_org_hrs_days` AS SELECT `a`.`idemp_org_hrs_days` AS `idemp_org_hrs_days`, `b`.`emp_org_id` AS `emp_org_id`, `c`.`emp_id` AS `emp_id`, `e`.`file_name` AS `emp_fnm`, CONCAT(`d`.`firstname`, ' ', `d`.`lastname`) AS `emp_nm`, `c`.`comp_id` AS `comp_id`, `g`.`file_name` AS `comp_fnm`, `f`.`companyname` AS `comp_nm`, `c`.`org_start_dt` AS `org_start_dt`, IF(ISNULL(`c`.`org_start_dt`), 'Not Started', DATE_FORMAT(`c`.`org_start_dt`, '%m-%d-%y')) AS `org_start_dt_fmt`, DATE_FORMAT(`c`.`org_start_dt`, '%Y') AS `org_start_dt_yr`, DATE_FORMAT(`c`.`org_start_dt`, '%M') AS `org_start_dt_mon`, `c`.`org_end_dt` AS `org_end_dt`, IF(ISNULL(`c`.`org_end_dt`), 'Full Time', DATE_FORMAT(`c`.`org_end_dt`, '%m-%d-%y')) AS `org_end_dt_fmt`, DATE_FORMAT(`c`.`org_end_dt`, '%Y') AS `org_end_dt_yr`, DATE_FORMAT(`c`.`org_end_dt`, '%M') AS `org_end_dt_mon`, `c`.`no_of_days` AS `no_of_days`, IFNULL(`c`.`no_of_days`, (TO_DAYS(`c`.`tentative_en_dt`) - TO_DAYS(`c`.`org_start_dt`))) AS `no_of_days_vir`, `c`.`emp_type` AS `emp_type`, `h`.`lk_val` AS `emp_type_nm`, `c`.`run_script` AS `run_script`, `a`.`idemp_org_hrs` AS `idemp_org_hrs`, `b`.`from_dt` AS `from_dt`, DATE_FORMAT(`b`.`from_dt`, '%m-%d-%y') AS `from_dt_fmt`, DATE_FORMAT(`b`.`from_dt`, '%Y') AS `from_yr`, DATE_FORMAT(`b`.`from_dt`, '%M') AS `from_mon`, `b`.`to_dt` AS `to_dt`, DATE_FORMAT(`b`.`to_dt`, '%m-%d-%y') AS `to_dt_fmt`, DATE_FORMAT(`b`.`to_dt`, '%Y') AS `to_yr`, DATE_FORMAT(`b`.`to_dt`, '%M') AS `to_mon`, `b`.`frm_to_org_hrs_days` AS `frm_to_org_hrs_days`, IFNULL(`b`.`frm_to_org_hrs_days`, (TO_DAYS(`b`.`tentative_en_dt`) - TO_DAYS(`b`.`from_dt`))) AS `frm_to_org_hrs_days_fmt`, `b`.`tentative_en_dt` AS `tentative_en_dt`, DATE_FORMAT(`b`.`tentative_en_dt`, '%m-%d-%y') AS `tentative_en_dt_fmt`, `b`.`org_hrs_forecast` AS `org_hrs_forecast`, `b`.`org_hrs_actual` AS `org_hrs_actual`, `b`.`org_hrs_pcnt` AS `org_hrs_pcnt`, `b`.`emp_hrs_title` AS `emp_hrs_title`, `r`.`lk_val` AS `emp_hrs_title_nm`, `a`.`tm_zn` AS `tm_zn`, `a`.`start_tm` AS `start_tm`, TIME_FORMAT(`a`.`start_tm`, '%h:%i %p') AS `start_tm_fmt`, `a`.`end_tm` AS `end_tm`, TIME_FORMAT(`a`.`end_tm`, '%h:%i %p') AS `end_tm_fmt`, `a`.`hrs_tm` AS `hrs_tm`, `a`.`hrs_tm_nm` AS `hrs_tm_nm`, `a`.`off_time` AS `off_time`, `a`.`off_time_nm` AS `off_time_nm`, `a`.`days` AS `days`, (CASE WHEN (`a`.`days` = 1) THEN 'Sunday' WHEN (`a`.`days` = 2) THEN 'Monday' WHEN (`a`.`days` = 3) THEN 'Tuesday' WHEN (`a`.`days` = 4) THEN 'Wednesday' WHEN (`a`.`days` = 5) THEN 'Thursday' WHEN (`a`.`days` = 6) THEN 'Friday' WHEN (`a`.`days` = 7) THEN 'Saturday' WHEN (`a`.`days` = 8) THEN 'Mon To Fri' WHEN (`a`.`days` = 9) THEN 'Mon To Sat' WHEN (`a`.`days` = 10) THEN 'All Days' END) AS `days_nm`, `a`.`pay_amt` AS `pay_amt`, `a`.`look_street_no_id` AS `look_street_no_id`, `n`.`country_id` AS `country_id`, `n`.`con_name` AS `con_name`, `m`.`state_id` AS `state_id`, `m`.`state_name` AS `state_name`, `l`.`city_id` AS `city_id`, `l`.`city_name` AS `city_name`, `j`.`postal_id` AS `postal_id`, `k`.`zip_code` AS `zip_code`, `i`.`look_street_id` AS `look_street_id`, `i`.`look_street_no` AS `look_street_no`, `j`.`street_name` AS `street_name`, `j`.`street_type` AS `street_type`, `o`.`lk_val` AS `street_type_nm`, `a`.`house_apt_type` AS `house_apt_type`, `s`.`lk_val` AS `house_apt_type_nm`, `a`.`house_apt_no` AS `house_apt_no`, IF(ISNULL(`a`.`look_street_no_id`), 'No Address', CONCAT(`i`.`look_street_no`, ' ', `j`.`street_name`, ' ', `o`.`lk_val`, ', ', IF(ISNULL(`a`.`house_apt_type`), '', `s`.`lk_val`), ' ', IFNULL(`a`.`house_apt_no`, ''), ', ', `l`.`city_name`, ', ', `m`.`state_name`, ' ', `k`.`zip_code`, ' ', '(', `n`.`con_name`, ')')) AS `addresses`, `a`.`updater` AS `updater`, `q`.`file_name` AS `updater_fnm`, IF(ISNULL(`a`.`updater`), 'Admin', CONCAT(`p`.`firstname`, ' ', `p`.`lastname`)) AS `updater_nm`, `a`.`updated` AS `updated`, DATE_FORMAT(`a`.`updated`, '%m-%d-%y %h:%i %p') AS `updated_fmt`, `a`.`cy_days_st_dt` AS `cy_days_st_dt`, `a`.`hrs_day_cy_run` AS `hrs_day_cy_run` FROM ((((((((((((((((((`emp_org_hrs_days` `a` LEFT JOIN `emp_org_hrs` `b` ON ((`a`.`idemp_org_hrs` = `b`.`idemp_org_hrs`))) LEFT JOIN `emp_org` `c` ON ((`b`.`emp_org_id` = `c`.`emp_org_id`))) LEFT JOIN `emp` `d` ON ((`c`.`emp_id` = `d`.`emp_id`))) LEFT JOIN `storages` `e` ON ((`d`.`idstorages` = `e`.`idstorages`))) LEFT JOIN `company` `f` ON ((`c`.`comp_id` = `f`.`comp_id`))) LEFT JOIN `storages` `g` ON ((`f`.`idstorages` = `g`.`idstorages`))) LEFT JOIN `emp_lk_val` `h` ON ((`c`.`emp_type` = `h`.`idemp_lk_value`))) LEFT JOIN `look_street_no` `i` ON ((`a`.`look_street_no_id` = `i`.`look_street_no_id`))) LEFT JOIN `look_street` `j` ON ((`i`.`look_street_id` = `j`.`look_street_id`))) LEFT JOIN `look_postal` `k` ON ((`j`.`postal_id` = `k`.`postal_id`))) LEFT JOIN `look_city` `l` ON ((`k`.`city_id` = `l`.`city_id`))) LEFT JOIN `look_state` `m` ON ((`l`.`state_id` = `m`.`state_id`))) LEFT JOIN `look_country` `n` ON ((`m`.`country_id` = `n`.`country_id`))) LEFT JOIN `feature_lk_val` `o` ON ((`j`.`street_type` = `o`.`idfeature_lk_val`))) LEFT JOIN `emp` `p` ON ((`a`.`updater` = `p`.`emp_id`))) LEFT JOIN `storages` `q` ON ((`p`.`idstorages` = `q`.`idstorages`))) LEFT JOIN `emp_lk_val` `r` ON ((`b`.`emp_hrs_title` = `r`.`idemp_lk_value`))) LEFT JOIN `feature_lk_val` `s` ON ((`a`.`house_apt_type` = `s`.`idfeature_lk_val`))) _____________________________________________________________ 08/05/2025 vw_emp_address old defination CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_emp_address` AS SELECT `a`.`emp_add_id` AS `emp_add_id`, `a`.`emp_id` AS `emp_id`, `c`.`file_name` AS `emp_fnm`, CONCAT(TRIM(IFNULL(`b`.`firstname`, '')), ' ', TRIM(IFNULL(NULLIF(`b`.`middlename`, ''), '')), CONVERT( (CASE WHEN ((`b`.`middlename` IS NOT NULL) AND (`b`.`middlename` <> '')) THEN ' ' ELSE '' END) USING LATIN1), TRIM(IFNULL(`b`.`lastname`, ''))) AS `emp_nm`, `a`.`look_street_no_id` AS `look_street_no_id`, `d`.`look_street_id` AS `look_street_id`, IFNULL(`d`.`look_street_no`, 'No Street') AS `look_street_no`, IFNULL(`e`.`street_name`, 'No Street Name') AS `street_name`, IFNULL(`e`.`street_type`, 'No Street Type') AS `street_type`, IFNULL(`f`.`lk_val`, 'No Street Type') AS `street_type_name`, CONCAT(IFNULL(`e`.`street_name`, 'No Street Name'), ' ', IFNULL(`f`.`lk_val`, 'No Street Type')) AS `street_name_type`, IFNULL(`e`.`postal_id`, 'No Postal Id') AS `postal_id`, IFNULL(`g`.`zip_code`, 'No Zip Code') AS `zip_code`, `g`.`city_id` AS `city_id`, IFNULL(`h`.`city_name`, 'No City') AS `city_name`, IFNULL(`h`.`state_id`, 'No State') AS `state_id`, IFNULL(`i`.`state_name`, 'No State') AS `state_name`, IFNULL(`i`.`state_full_name`, 'No State Full Name') AS `state_full_name`, `i`.`country_id` AS `country_id`, IFNULL(`j`.`con_name`, 'No Country Name') AS `con_name`, IFNULL(`j`.`con_full_name`, 'No Country Full Name') AS `con_full_name`, IFNULL(`j`.`lk_design`, ' - ') AS `lk_design`, IFNULL(`j`.`con_code`, ' - ') AS `con_code`, IFNULL(`j`.`con_digit`, ' - ') AS `con_digit`, `a`.`house_apt_no` AS `house_apt_no`, `a`.`house_apt_type` AS `house_apt_type`, `k`.`lk_val` AS `house_apt_type_nm`, CONCAT(IFNULL(`d`.`look_street_no`, 'No Street'), ' ', IFNULL(`e`.`street_name`, 'No Street Name'), ' ', IFNULL(`f`.`lk_val`, 'No Street Type'), ' ', IFNULL(`h`.`city_name`, 'No City'), ', ', IFNULL(`i`.`state_name`, 'No State'), ' ', IFNULL(`g`.`zip_code`, 'No Zip Code'), ' ', '(', IFNULL(`j`.`con_name`, 'No Country'), ')') AS `addresses`, `a`.`from_dt` AS `from_dt`, DATE_FORMAT(`a`.`from_dt`, '%m-%d-%y') AS `from_dt_fmt`, DATE_FORMAT(`a`.`from_dt`, '%Y') AS `from_dt_yr`, DATE_FORMAT(`a`.`from_dt`, '%M') AS `from_dt_mon`, `a`.`to_dt` AS `to_dt`, DATE_FORMAT(`a`.`to_dt`, '%m-%d-%y') AS `to_dt_fmt`, DATE_FORMAT(`a`.`to_dt`, '%Y') AS `to_dt_yr`, DATE_FORMAT(`a`.`to_dt`, '%M') AS `to_dt_mon`, `a`.`no_of_days` AS `no_of_days`, IFNULL(`a`.`no_of_days`, TIMESTAMPDIFF(DAY, `a`.`from_dt`, NOW())) AS `no_of_days_vir`, `a`.`add_type` AS `add_type`, `l`.`lk_val` AS `add_type_nm`, `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`, `n`.`file_name` AS `updater_fnm`, IF(ISNULL(`a`.`updater`), 'Admin', CONCAT(TRIM(IFNULL(`m`.`firstname`, '')), ' ', TRIM(IFNULL(NULLIF(`m`.`middlename`, ''), '')), CONVERT( (CASE WHEN ((`m`.`middlename` IS NOT NULL) AND (`m`.`middlename` <> '')) THEN ' ' ELSE '' END) USING LATIN1), TRIM(IFNULL(`m`.`lastname`, '')))) AS `updater_nm`, `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`, `p`.`file_name` AS `del_by_fnm`, (CASE WHEN (ISNULL(`a`.`del_dt`) AND ISNULL(`a`.`del_by`)) THEN 'Not Deleted' WHEN (ISNULL(`a`.`del_by`) AND (`a`.`del_dt` IS NOT NULL)) THEN 'Admin' WHEN ((`a`.`del_dt` IS NOT NULL) AND (`a`.`del_by` IS NOT NULL)) THEN CONCAT(TRIM(IFNULL(`o`.`firstname`, '')), ' ', TRIM(IFNULL(NULLIF(`o`.`middlename`, ''), '')), CONVERT( (CASE WHEN ((`o`.`middlename` IS NOT NULL) AND (`o`.`middlename` <> '')) THEN ' ' ELSE '' END) USING LATIN1), TRIM(IFNULL(`o`.`lastname`, ''))) END) AS `del_by_nm`, `a`.`del_day` AS `del_day`, IF((`a`.`archv_dt` IS NOT NULL), 'Never', (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`, (CASE WHEN (ISNULL(`a`.`del_dt`) AND ISNULL(`a`.`archv_dt`)) THEN 'Not Archived' WHEN ISNULL(`a`.`del_dt`) THEN CONVERT( DATE_FORMAT(`a`.`archv_dt`, '%Y') USING UTF8MB4) WHEN ((`a`.`del_dt` IS NOT NULL) AND ISNULL(`a`.`archv_dt`)) THEN 'Deleted' END) AS `archv_dt_yr`, (CASE WHEN (ISNULL(`a`.`del_dt`) AND ISNULL(`a`.`archv_dt`)) THEN 'Not Archived' WHEN ISNULL(`a`.`del_dt`) THEN CONVERT( DATE_FORMAT(`a`.`archv_dt`, '%M') USING UTF8MB4) WHEN ((`a`.`del_dt` IS NOT NULL) AND ISNULL(`a`.`archv_dt`)) THEN 'Deleted' END) AS `archv_dt_mon`, `a`.`archv_by` AS `archv_by`, `r`.`file_name` AS `archv_by_fnm`, (CASE WHEN (ISNULL(`a`.`archv_dt`) AND ISNULL(`a`.`archv_by`)) THEN 'Not Archived' WHEN (ISNULL(`a`.`archv_by`) AND (`a`.`archv_dt` IS NOT NULL)) THEN 'Admin' WHEN ((`a`.`archv_dt` IS NOT NULL) AND (`a`.`archv_by` IS NOT NULL)) THEN CONCAT(TRIM(IFNULL(`q`.`firstname`, '')), ' ', TRIM(IFNULL(NULLIF(`q`.`middlename`, ''), '')), CONVERT( (CASE WHEN ((`q`.`middlename` IS NOT NULL) AND (`q`.`middlename` <> '')) THEN ' ' ELSE '' END) USING LATIN1), TRIM(IFNULL(`q`.`lastname`, ''))) END) AS `archv_by_nm` FROM (((((((((((((((((`emp_address` `a` LEFT JOIN `emp` `b` ON ((`a`.`emp_id` = `b`.`emp_id`))) LEFT JOIN `storages` `c` ON ((`b`.`idstorages` = `c`.`idstorages`))) LEFT JOIN `look_street_no` `d` ON ((`a`.`look_street_no_id` = `d`.`look_street_no_id`))) LEFT JOIN `look_street` `e` ON ((`d`.`look_street_id` = `e`.`look_street_id`))) LEFT JOIN `feature_lk_val` `f` ON ((`e`.`street_type` = `f`.`idfeature_lk_val`))) LEFT JOIN `look_postal` `g` ON ((`e`.`postal_id` = `g`.`postal_id`))) LEFT JOIN `look_city` `h` ON ((`g`.`city_id` = `h`.`city_id`))) LEFT JOIN `look_state` `i` ON ((`h`.`state_id` = `i`.`state_id`))) LEFT JOIN `look_country` `j` ON ((`i`.`country_id` = `j`.`country_id`))) LEFT JOIN `feature_lk_val` `k` ON ((`a`.`house_apt_type` = `k`.`idfeature_lk_val`))) LEFT JOIN `emp_lk_val` `l` ON ((`a`.`add_type` = `l`.`idemp_lk_value`))) LEFT JOIN `emp` `m` ON ((`a`.`updater` = `m`.`emp_id`))) LEFT JOIN `storages` `n` ON ((`m`.`idstorages` = `n`.`idstorages`))) LEFT JOIN `emp` `o` ON ((`a`.`del_by` = `o`.`emp_id`))) LEFT JOIN `storages` `p` ON ((`o`.`idstorages` = `p`.`idstorages`))) LEFT JOIN `emp` `q` ON ((`a`.`archv_by` = `q`.`emp_id`))) LEFT JOIN `storages` `r` ON ((`q`.`idstorages` = `r`.`idstorages`))) ____________________________________________________ 08_04_2025 vw_emp_org_hrs_days_cy (old_defination below) CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_emp_org_hrs_days_cy` AS SELECT `a`.`idemp_org_hrs_days_cy` AS `idemp_org_hrs_days_cy`, `c`.`emp_org_id` AS `emp_org_id`, `d`.`emp_id` AS `emp_id`, `f`.`file_name` AS `emp_fnm`, CONCAT(`e`.`firstname`, ' ', `e`.`lastname`) AS `emp_nm`, `d`.`comp_id` AS `comp_id`, `h`.`file_name` AS `comp_fnm`, `g`.`companyname` AS `comp_nm`, `d`.`org_start_dt` AS `org_start_dt`, IF(ISNULL(`d`.`org_start_dt`), 'Not Started', DATE_FORMAT(`d`.`org_start_dt`, '%m-%d-%y')) AS `org_start_dt_fmt`, `d`.`org_end_dt` AS `org_end_dt`, IF(ISNULL(`d`.`org_end_dt`), 'Full Time', DATE_FORMAT(`d`.`org_end_dt`, '%m-%d-%y')) AS `org_end_dt_fmt`, `d`.`no_of_days` AS `no_of_days`, IFNULL(`d`.`no_of_days`, (TO_DAYS(`d`.`tentative_en_dt`) - TO_DAYS(`d`.`org_start_dt`))) AS `no_of_days_fmt`, `d`.`emp_type` AS `emp_type`, IF(ISNULL(`d`.`emp_type`), 'No Type', `i`.`lk_val`) AS `emp_type_nm`, `b`.`idemp_org_hrs` AS `idemp_org_hrs`, `c`.`from_dt` AS `from_dt`, DATE_FORMAT(`c`.`from_dt`, '%m-%d-%y') AS `from_dt_fmt`, `c`.`to_dt` AS `to_dt`, DATE_FORMAT(`c`.`to_dt`, '%m-%d-%y') AS `to_dt_fmt`, `c`.`frm_to_org_hrs_days` AS `frm_to_org_hrs_days`, IFNULL(`c`.`frm_to_org_hrs_days`, (TO_DAYS(`c`.`tentative_en_dt`) - TO_DAYS(`c`.`from_dt`))) AS `frm_to_org_hrs_days_fmt`, `c`.`tentative_en_dt` AS `tentative_en_dt`, IF(ISNULL(`c`.`tentative_en_dt`), 'Ended', DATE_FORMAT(`c`.`tentative_en_dt`, '%m-%d-%y')) AS `tentative_en_dt_fmt`, `c`.`org_hrs_forecast` AS `org_hrs_forecast`, `c`.`org_hrs_actual` AS `org_hrs_actual`, `c`.`org_hrs_pcnt` AS `org_hrs_pcnt`, `c`.`emp_hrs_title` AS `emp_hrs_title`, IF(ISNULL(`c`.`emp_hrs_title`), 'No Title', `q`.`lk_val`) AS `emp_hrs_title_nm`, `a`.`idemp_org_hrs_days` AS `idemp_org_hrs_days`, `b`.`start_tm` AS `start_tm`, TIME_FORMAT(`b`.`start_tm`, '%h:%i %p') AS `start_tm_fmt`, `b`.`end_tm` AS `end_tm`, TIME_FORMAT(`b`.`end_tm`, '%h:%i %p') AS `end_tm_fmt`, `b`.`hrs_tm` AS `hrs_tm`, `b`.`off_time` AS `off_time`, `b`.`off_time_nm` AS `off_time_nm`, `b`.`days` AS `days`, `b`.`pay_amt` AS `pay_amt`, `b`.`look_street_no_id` AS `look_street_no_id`, `p`.`country_id` AS `country_id`, `p`.`con_name` AS `con_name`, `o`.`state_id` AS `state_id`, `o`.`state_name` AS `state_name`, `n`.`city_id` AS `city_id`, `n`.`city_name` AS `city_name`, `m`.`postal_id` AS `postal_id`, `m`.`zip_code` AS `zip_code`, `k`.`look_street_id` AS `look_street_id`, `j`.`look_street_no` AS `look_street_no`, `k`.`street_name` AS `street_name`, `k`.`street_type` AS `street_type`, `l`.`lk_val` AS `street_type_nm`, `b`.`house_apt_type` AS `house_apt_type`, `v`.`lk_val` AS `house_apt_type_nm`, `b`.`house_apt_no` AS `house_apt_no`, IF(ISNULL(`b`.`look_street_no_id`), 'No Address', CONCAT(`j`.`look_street_no`, ' ', `k`.`street_name`, ' ', `l`.`lk_val`, ', ', IF(ISNULL(`b`.`house_apt_type`), '', `v`.`lk_val`), ' ', IFNULL(`b`.`house_apt_no`, ''), ', ', `n`.`city_name`, ', ', `o`.`state_name`, ' ', `m`.`zip_code`, ' ', '(', `p`.`con_name`, ')')) AS `addresses`, `a`.`day_start_tm` AS `day_start_tm`, DATE_FORMAT(`a`.`day_start_tm`, '%m-%d-%y %h:%i %p') AS `day_start_tm_fmt`, DATE_FORMAT(`a`.`day_start_tm`, '%m-%d-%y') AS `day_start_tm_dt`, DATE_FORMAT(`a`.`day_start_tm`, '%h:%i %p') AS `day_start_tm_dt_tm`, DAYNAME(`a`.`day_start_tm`) AS `day_start_tm_day`, (CASE WHEN ISNULL(`a`.`day_start_tm`) THEN 'Unused' WHEN (DATE_FORMAT(`a`.`day_start_tm`, '%y-%m-%d') < DATE_FORMAT(NOW(), '%y-%m-%d')) THEN 'Past' WHEN (DATE_FORMAT(`a`.`day_start_tm`, '%y-%m-%d') > DATE_FORMAT(NOW(), '%y-%m-%d')) THEN 'Future' ELSE 'Today' END) AS `day_start_tm_vir`, DATE_FORMAT(`a`.`day_start_tm`, '%Y') AS `day_start_tm_yr`, DATE_FORMAT(`a`.`day_start_tm`, '%M') AS `day_start_tm_mon`, `a`.`day_end_tm` AS `day_end_tm`, DATE_FORMAT(`a`.`day_end_tm`, '%m-%d-%y %h:%i %p') AS `day_end_tm_fmt`, DATE_FORMAT(`a`.`day_end_tm`, '%h:%i %p') AS `day_end_tm_fmt_tm`, DAYNAME(`a`.`day_end_tm`) AS `day_end_tm_day`, DATE_FORMAT(`a`.`day_end_tm`, '%Y') AS `day_end_tm_yr`, DATE_FORMAT(`a`.`day_end_tm`, '%M') AS `day_end_tm_mon`, `a`.`day_hrs_forcast_tm` AS `day_hrs_forcast_tm`, `a`.`day_hrs_forcast_tm_nm` AS `day_hrs_forcast_tm_nm`, IFNULL(`a`.`day_off_forcast`, '0:00') AS `day_off_forcast`, IFNULL(`a`.`day_off_forcast_nm`, '0.00') AS `day_off_forcast_nm`, ROUND(`a`.`day_pay_forcast_amt`, 2) AS `day_pay_forcast_amt`, `a`.`day_hrs_actual_tm` AS `day_hrs_actual_tm`, `a`.`day_hrs_actual_tm_nm` AS `day_hrs_actual_tm_nm`, `a`.`day_off_actual` AS `day_off_actual`, `a`.`day_off_actual_nm` AS `day_off_actual_nm`, `a`.`day_pay_actual_amt` AS `day_pay_actual_amt`, CONCAT(CONVERT( IF(ISNULL(`a`.`day_start_tm`), 'Not Start Date', DATE_FORMAT(`a`.`day_start_tm`, '%m-%d-%y')) USING UTF8), ' ', CONVERT( IF(ISNULL(`a`.`day_start_tm`), 'Not Start Day', DAYNAME(`a`.`day_start_tm`)) USING UTF8), ' ', CONVERT( IF(ISNULL(`a`.`day_start_tm`), 'Not Start Time', DATE_FORMAT(`a`.`day_start_tm`, '%h:%i %p')) USING UTF8), ' To ', CONVERT( IF(ISNULL(`a`.`day_end_tm`), 'No End Time', DATE_FORMAT(`a`.`day_end_tm`, '%h:%i %p')) USING UTF8), ' ', CONVERT( IFNULL(`b`.`tm_zn`, 'No Time Zone') USING UTF8), ' ', ' - Break - ', IFNULL(CAST(`b`.`off_time_nm` AS CHAR CHARSET UTF8), ' None'), ' : Hours : ', IFNULL(CAST(`a`.`day_hrs_forcast_tm_nm` AS CHAR CHARSET UTF8), ' No Forecast Hours')) AS `days_schedule`, CONCAT('Break : ', IFNULL(`a`.`day_off_actual_nm`, 'None'), ' ', 'Worked :', IFNULL(`a`.`day_hrs_actual_tm_nm`, 'No Hours')) AS `days_actual`, IF(ISNULL(`a`.`day_start_tm`), 'Not Started', DATE_FORMAT(`a`.`day_start_tm`, '%Y - %b')) AS `day_start_yr_mon`, `b`.`updater` AS `hrs_days_updater`, `x`.`file_name` AS `hrs_days_updater_fnm`, IF(ISNULL(`b`.`updater`), 'Admin', CONCAT(`w`.`firstname`, ' ', `w`.`lastname`)) AS `hrs_days_updater_nm`, `b`.`updated` AS `hrs_days_updated`, DATE_FORMAT(`b`.`updated`, '%m-%d-%y %h:%i %p') AS `hrs_days_updated_fmt`, `c`.`del_dt` AS `hrs_del_dt`, DATE_FORMAT(`c`.`del_dt`, '%m-%d-%y %h:%i %p') AS `hrs_del_dt_fmt`, `c`.`del_by` AS `hrs_del_by`, `s`.`file_name` AS `hrs_del_by_fnm`, IF(ISNULL(`c`.`del_by`), 'Admin', CONCAT(`r`.`firstname`, ' ', `r`.`lastname`)) AS `hrs_del_by_nm`, `c`.`del_day` AS `hrs_del_day`, (TO_DAYS((DATE_FORMAT(`c`.`del_dt`, '%y-%m-%d') + INTERVAL `c`.`del_day` DAY)) - TO_DAYS(DATE_FORMAT(NOW(), '%y-%m-%d'))) AS `hrs_del_day_vir`, `c`.`archv_dt` AS `hrs_archv_dt`, DATE_FORMAT(`c`.`archv_dt`, '%m-%d-%y %h:%i %p') AS `hrs_archv_dt_fmt`, `c`.`archv_by` AS `hrs_archv_by`, `u`.`file_name` AS `hrs_archv_by_fnm`, IF(ISNULL(`c`.`archv_by`), 'Admin', CONCAT(`t`.`firstname`, ' ', `t`.`lastname`)) AS `hrs_archv_by_nm`, `b`.`hrs_day_cy_run` AS `hrs_day_cy_run` FROM (((((((((((((((((((((((`emp_org_hrs_days_cy` `a` LEFT JOIN `emp_org_hrs_days` `b` ON ((`a`.`idemp_org_hrs_days` = `b`.`idemp_org_hrs_days`))) LEFT JOIN `emp_org_hrs` `c` ON ((`b`.`idemp_org_hrs` = `c`.`idemp_org_hrs`))) LEFT JOIN `emp_org` `d` ON ((`c`.`emp_org_id` = `d`.`emp_org_id`))) LEFT JOIN `emp` `e` ON ((`d`.`emp_id` = `e`.`emp_id`))) LEFT JOIN `storages` `f` ON ((`e`.`idstorages` = `f`.`idstorages`))) LEFT JOIN `company` `g` ON ((`d`.`comp_id` = `g`.`comp_id`))) LEFT JOIN `storages` `h` ON ((`g`.`idstorages` = `h`.`idstorages`))) LEFT JOIN `emp_lk_val` `i` ON ((`d`.`emp_type` = `i`.`idemp_lk_value`))) LEFT JOIN `look_street_no` `j` ON ((`b`.`look_street_no_id` = `j`.`look_street_no_id`))) LEFT JOIN `look_street` `k` ON ((`j`.`look_street_id` = `k`.`look_street_id`))) LEFT JOIN `feature_lk_val` `l` ON ((`k`.`street_type` = `l`.`idfeature_lk_val`))) LEFT JOIN `look_postal` `m` ON ((`k`.`postal_id` = `m`.`postal_id`))) LEFT JOIN `look_city` `n` ON ((`m`.`city_id` = `n`.`city_id`))) LEFT JOIN `look_state` `o` ON ((`n`.`state_id` = `o`.`state_id`))) LEFT JOIN `look_country` `p` ON ((`o`.`country_id` = `p`.`country_id`))) LEFT JOIN `emp_lk_val` `q` ON ((`c`.`emp_hrs_title` = `q`.`idemp_lk_value`))) LEFT JOIN `emp` `r` ON ((`c`.`del_by` = `r`.`emp_id`))) LEFT JOIN `storages` `s` ON ((`r`.`idstorages` = `s`.`idstorages`))) LEFT JOIN `emp` `t` ON ((`c`.`archv_by` = `t`.`emp_id`))) LEFT JOIN `storages` `u` ON ((`t`.`idstorages` = `u`.`idstorages`))) LEFT JOIN `feature_lk_val` `v` ON ((`b`.`house_apt_type` = `v`.`idfeature_lk_val`))) LEFT JOIN `emp` `w` ON ((`b`.`updater` = `w`.`emp_id`))) LEFT JOIN `storages` `x` ON ((`w`.`idstorages` = `x`.`idstorages`))) _________________________________________________________________________________________________________ 03_03_2025 vw_emp_address CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_emp_address` AS SELECT `a`.`emp_add_id` AS `emp_add_id`, `a`.`emp_id` AS `emp_id`, `d`.`file_name` AS `emp_fnm`, CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) AS `emp_nm`, `a`.`house_apt_no` AS `house_apt_no`, `a`.`house_apt_type` AS `house_apt_type`, `l`.`lk_val` AS `house_apt_type_nm`, `a`.`look_street_no_id` AS `look_street_no_id`, `b`.`look_street_no` AS `look_street_no`, `b`.`street_name` AS `street_name`, `b`.`street_type` AS `street_type`, `b`.`street_type_name` AS `street_type_name`, `b`.`zip_code` AS `zip_code`, `b`.`city_name` AS `city_name`, `b`.`state_name` AS `state_name`, `b`.`con_name` AS `con_name`, `b`.`addresses` AS `addresses`, `a`.`add_type` AS `add_type`, `v`.`lk_val` AS `add_type_nm`, `a`.`from_dt` AS `from_dt`, DATE_FORMAT(`a`.`from_dt`, '%Y') AS `from_dt_yr`, DATE_FORMAT(`a`.`from_dt`, '%M') AS `from_dt_mon`, (CASE WHEN (`a`.`from_dt` <> _UTF8'0000-00-00') THEN DATE_FORMAT(`a`.`from_dt`, _UTF8'%m-%d-%y') ELSE '' END) AS `from_dt_fmt`, `a`.`to_dt` AS `to_dt`, DATE_FORMAT(`a`.`to_dt`, '%Y') AS `to_dt_yr`, DATE_FORMAT(`a`.`to_dt`, '%M') AS `to_dt_mon`, (CASE WHEN (`a`.`to_dt` <> _UTF8'0000-00-00') THEN DATE_FORMAT(`a`.`to_dt`, _UTF8'%m-%d-%y') ELSE '' END) AS `to_dt_fmt`, `a`.`no_of_days` AS `no_of_days`, TIMESTAMPDIFF(DAY, `a`.`from_dt`, IFNULL(`a`.`to_dt`, NOW())) AS `no_of_days_vir`, `a`.`updater` AS `updater`, `s`.`file_name` AS `updater_fnm`, IF(ISNULL(`a`.`updater`), 'Admin', CONCAT(`e`.`firstname`, ' ', `e`.`lastname`)) AS `updater_nm`, `a`.`last_updated` AS `last_updated`, CONCAT(CONVERT( DATE_FORMAT(`a`.`last_updated`, '%m-%d-%y %H:%i:%s %p') USING LATIN1), ' ') 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`, `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`, `a`.`archv_by` AS `archv_by`, `h`.`file_name` AS `archv_by_fnm`, IF(ISNULL(`a`.`archv_by`), 'Admin', CONCAT(`i`.`firstname`, ' ', `i`.`lastname`)) AS `archv_by_nm` FROM (((((((((((`emp_address` `a` LEFT JOIN `vw_look_addresses` `b` ON ((`a`.`look_street_no_id` = `b`.`look_street_no_id`))) LEFT JOIN `emp` `e` ON ((`a`.`updater` = `e`.`emp_id`))) LEFT JOIN `storages` `s` ON ((`e`.`idstorage` = `s`.`idstorage`))) LEFT JOIN `feature_lk_val` `l` ON ((`a`.`house_apt_type` = `l`.`idfeature_lk_val`))) LEFT JOIN `emp_lk_val` `v` ON ((`a`.`add_type` = `v`.`idemp_lk_value`))) LEFT JOIN `emp` `c` ON ((`c`.`emp_id` = `a`.`emp_id`))) LEFT JOIN `storages` `d` ON ((`d`.`idstorage` = `c`.`idstorage`))) LEFT JOIN `emp` `f` ON ((`a`.`del_by` = `f`.`emp_id`))) LEFT JOIN `storages` `g` ON ((`g`.`idstorage` = `f`.`idstorage`))) LEFT JOIN `emp` `i` ON ((`a`.`archv_by` = `i`.`emp_id`))) LEFT JOIN `storages` `h` ON ((`h`.`idstorage` = `e`.`idstorage`))) _____________________________________________________________________________________________________________ 10/11/2024 CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_storages` AS SELECT `a`.`idstorage` AS `idstorage`, `a`.`file_name` AS `file_name`, `a`.`sto_cloud` AS `sto_cloud`, IFNULL(`b`.`lk_val`, 'local') AS `sto_cloud_nm`, `a`.`file_size` AS `file_size`, (CASE WHEN (`a`.`file_size` < 1024) THEN CONCAT(`a`.`file_size`, ' B') WHEN ((`a`.`file_size` > 1024) AND (`a`.`file_size` < 1048576)) THEN CONCAT(ROUND((`a`.`file_size` / 1024), 2), ' KB') WHEN ((`a`.`file_size` > 1048576) AND (`a`.`file_size` < 1073741824)) THEN CONCAT(ROUND(((`a`.`file_size` / 1024) / 1024), 2), ' MB') WHEN (`a`.`file_size` > 1073741824) THEN CONCAT(ROUND((((`a`.`file_size` / 1024) / 1024) / 1024), 2), ' GB') END) AS `file_size_fmt`, `a`.`owner_id` AS `owner_id`, `d`.`file_name` AS `owner_fnm`, IF(ISNULL(`a`.`owner_id`), 'No Owner', CONCAT(`c`.`firstname`, ' ', `c`.`lastname`)) AS `owner_nm`, `a`.`create_dt` AS `create_dt`, DATE_FORMAT(`a`.`create_dt`, '%m-%d-%y %h:%i %p') AS `create_dt_fmt`, DATE_FORMAT(`a`.`create_dt`, '%Y') AS `create_dt_yr`, DATE_FORMAT(`a`.`create_dt`, '%M') AS `create_dt_mon`, `a`.`expire_dt` AS `expire_dt`, DATE_FORMAT(`a`.`expire_dt`, '%m-%d-%y') AS `expire_dt_fmt`, DATE_FORMAT(`a`.`expire_dt`, '%m %Y') AS `expire_dt_mon_yr`, `a`.`ext_type` AS `ext_type`, `a`.`public` AS `public`, `a`.`self_view` AS `self_view`, `a`.`smry` AS `smry`, `a`.`ref_emp_id` AS `ref_emp_id`, `g`.`file_name` AS `ref_emp_id_fnm`, CONCAT(`f`.`firstname`, ' ', `f`.`lastname`) AS `ref_emp_id_nm`, `a`.`updater` AS `updater`, `i`.`file_name` AS `updater_fnm`, IF(ISNULL(`a`.`updater`), 'admin', CONCAT(`h`.`firstname`, ' ', `h`.`lastname`)) AS `updater_nm`, `a`.`updated` AS `updated`, DATE_FORMAT(`a`.`updated`, '%m-%d-%y %h:%i %p') AS `updated_dt_fmt`, `a`.`usage` AS `usage`, `a`.`sto_tags` AS `sto_tags`, IFNULL(`a`.`sto_tags`, 'No Tags') AS `sto_tags_nm`, `a`.`del_dt` AS `del_dt`, DATE_FORMAT(`a`.`del_dt`, '%m-%d-%y %h:%i %p') AS `del_dt_fmt`, IFNULL(CONVERT( DATE_FORMAT(`a`.`del_dt`, '%Y') USING UTF8MB4), 'Not deleted') AS `del_dt_yr`, IFNULL(CONVERT( DATE_FORMAT(`a`.`del_dt`, '%M') USING UTF8MB4), 'Not Deleted') AS `del_dt_mon`, `a`.`del_by` AS `del_by`, `k`.`file_name` AS `del_by_fnm`, IF(ISNULL(`a`.`del_by`), 'admin', CONCAT(`j`.`firstname`, ' ', `j`.`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(CONVERT( DATE_FORMAT(`a`.`archv_dt`, '%Y') USING UTF8MB4), 'Not Archived') AS `archv_dt_yr`, IFNULL(CONVERT( DATE_FORMAT(`a`.`archv_dt`, '%M') USING UTF8MB4), 'Not Archived') AS `archv_dt_mon`, `a`.`archv_by` AS `archv_by`, `m`.`file_name` AS `archv_by_fnm`, IF(ISNULL(`a`.`archv_by`), 'admin', CONCAT(`l`.`firstname`, ' ', `l`.`lastname`)) AS `archv_by_nm`, `a`.`chk_del` AS `chk_del` FROM (((((((((((`storages` `a` LEFT JOIN `storages_lk_val` `b` ON ((`a`.`sto_cloud` = `b`.`idstorages_lk_val`))) LEFT JOIN `emp` `c` ON ((`a`.`owner_id` = `c`.`emp_id`))) LEFT JOIN `storages` `d` ON ((`c`.`idstorage` = `d`.`idstorage`))) LEFT JOIN `emp` `f` ON ((`a`.`ref_emp_id` = `f`.`emp_id`))) LEFT JOIN `storages` `g` ON ((`f`.`idstorage` = `g`.`idstorage`))) LEFT JOIN `emp` `h` ON ((`a`.`updater` = `h`.`emp_id`))) LEFT JOIN `storages` `i` ON ((`h`.`idstorage` = `i`.`idstorage`))) LEFT JOIN `emp` `j` ON ((`a`.`del_by` = `j`.`emp_id`))) LEFT JOIN `storages` `k` ON ((`j`.`idstorage` = `k`.`idstorage`))) LEFT JOIN `emp` `l` ON ((`a`.`archv_by` = `l`.`emp_id`))) LEFT JOIN `storages` `m` ON ((`l`.`idstorage` = `m`.`idstorage`))) ORDER BY `a`.`updated` DESC ___________________________________________________________________ (08/27/2024) CREATE ALGORITHM = UNDEFINED DEFINER = `aerion`@`localhost` SQL SECURITY DEFINER VIEW `vw_addresses` AS SELECT `a`.`country_id` AS `country_id`, `a`.`con_name` AS `con_name`, `a`.`con_full_name` AS `con_full_name`, `a`.`lk_design` AS `lk_design`, `a`.`con_code` AS `con_code`, `a`.`con_digit` AS `con_digit`, `b`.`state_id` AS `state_id`, `b`.`state_name` AS `state_name`, `b`.`state_full_name` AS `state_full_name`, `c`.`city_id` AS `city_id`, `c`.`city_name` AS `city_name`, `d`.`postal_id` AS `postal_id`, `d`.`zip_code` AS `zip_code`, `e`.`look_street_id` AS `look_street_id`, `e`.`street_name` AS `street_name`, `e`.`street_type` AS `street_type`, `j`.`lk_val` AS `street_type_nm`, `f`.`look_street_no_id` AS `look_street_no_id`, `f`.`look_street_no` AS `look_street_no` FROM ((((`look_postal` `d` LEFT JOIN (`look_city` `c` LEFT JOIN (`look_state` `b` LEFT JOIN `look_country` `a` ON ((`a`.`country_id` = `b`.`country_id`))) ON ((`b`.`state_id` = `c`.`state_id`))) ON ((`c`.`city_id` = `d`.`city_id`))) LEFT JOIN `look_street` `e` ON ((`d`.`postal_id` = `e`.`postal_id`))) LEFT JOIN `look_street_no` `f` ON ((`e`.`look_street_id` = `f`.`look_street_id`))) LEFT JOIN `feature_lk_val` `j` ON ((`j`.`idfeature_lk_val` = `e`.`street_type`))) ____________________________________________________________________________________________ (08/29/2024) 12:34 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`.`lastname` AS `lastname`, CONCAT(`a`.`firstname`, ' ', `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_in_use`, 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_in_use_pcnt`, CONCAT(ROUND(((`a`.`sto_remain` / `a`.`sto_limit`) * 100), 2), '%') AS `sto_remaining_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`, `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`, 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`.`emp_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` ___________________________________________________________________________________________________ (08/29/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_in_use`, 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_in_use_pcnt`, CONCAT(ROUND(((`a`.`sto_remain` / `a`.`sto_limit`) * 100), 2), '%') AS `sto_remaining_pcnt`, `a`.`taxid` AS `taxid`, IFNULL(`a`.`taxid`, 'No TAXID') AS `taxid_nm`, CONCAT((CASE WHEN ISNULL(`q`.`org_actual`) THEN '0.00' WHEN (`q`.`org_actual` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`q`.`org_actual` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`q`.`org_actual` < 1000) THEN `q`.`org_actual` END), ' of ', (CASE WHEN ISNULL(`q`.`org_forecast`) THEN '0.00' WHEN (`q`.`org_forecast` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`q`.`org_forecast` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`q`.`org_forecast` < 1000) THEN `q`.`org_forecast` END)) AS `org_actual_forecast`, IFNULL(`q`.`org_pcnt`, '0.00') AS `org_pcnt`, CONCAT((CASE WHEN ISNULL(`q`.`org_amt_actual`) THEN '0.00' WHEN (`q`.`org_amt_actual` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`q`.`org_amt_actual` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`q`.`org_amt_actual` < 1000) THEN `q`.`org_amt_actual` END), ' of ', (CASE WHEN ISNULL(`q`.`org_amt_forcast`) THEN '0.00' WHEN (`q`.`org_amt_forcast` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`q`.`org_amt_forcast` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`q`.`org_amt_forcast` < 1000) THEN `q`.`org_amt_forcast` END)) AS `org_amt_actual_forcast`, IFNULL(`q`.`org_amt_pcnt`, '0.00') AS `org_amt_pcnt`, CONCAT((CASE WHEN ISNULL(`q`.`org_vac_use`) THEN '0.00' WHEN (`q`.`org_vac_use` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`q`.`org_vac_use` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`q`.`org_vac_use` < 1000) THEN `q`.`org_vac_use` END), ' of ', (CASE WHEN ISNULL(`q`.`org_vac_earn`) THEN '0.00' WHEN (`q`.`org_vac_earn` > 999) THEN CONVERT( CONCAT((TRIM(ROUND((`q`.`org_vac_earn` / 1000), 2)) + 0), 'K') USING LATIN1) WHEN (`q`.`org_vac_earn` < 1000) THEN `q`.`org_vac_earn` END)) AS `org_vac_used_earned`, IFNULL(`q`.`org_vac_pcnt`, '0.00') AS `org_vac_pcnt`, IFNULL(`q`.`org_vac_bal`, '0.00') AS `org_vac_bal`, 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`, `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(`g`.`ip_address`, ' ', `g`.`domain_nm`, ' ', `g`.`ip_contry`, ' ', `g`.`ip_state`, ' ', `g`.`ip_city`, ' ', `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`.`emp_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`))) LEFT JOIN `emp_org` `q` ON ((`a`.`emp_id` = `q`.`emp_org_id`))) ORDER BY `a`.`last_updated`