use av_workshop2;
CREATE  or replace    ALGORITHM = UNDEFINED      DEFINER = `av_workshop2`@`localhost`      SQL SECURITY DEFINER VIEW `vw_emp` AS     SELECT          `a`.`emp_id` AS `emp_id`,         `a`.`idstorages` AS `idstorages`,         `e`.`file_name` AS `emp_fnm`,         `e`.`file_ser_name` AS `emp_fnm_ser`,         IF((`a`.`idstorages` IS NULL),             'No',             'Yes') AS `idstorages_stat`,         `a`.`firstname` AS `firstname`,         `a`.`middlename` AS `middlename`,         `a`.`lastname` AS `lastname`,         CONCAT(TRIM(IFNULL(`a`.`firstname`, '')),                 ' ',                 TRIM(IFNULL(NULLIF(`a`.`middlename`, ''), '')),                 CONVERT( (CASE                     WHEN                         ((`a`.`middlename` IS NOT NULL)                             AND (`a`.`middlename` <> ''))                     THEN                         ' '                     ELSE ''                 END) USING LATIN1),                 TRIM(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((`b`.`user_name` IS NULL),             'No',             'Yes') AS `user_name_cur_stat`,         `b`.`status` AS `status`,         `b`.`expire_tm` AS `expire_tm`,         `b`.`last_clicked` AS `last_clicked`,         TIMESTAMPDIFF(MINUTE,             `f`.`lastlogin`,             `b`.`last_clicked`) AS `last_clicked_tm`,         `a`.`all_contacts` AS `all_contacts`,         IF((`a`.`all_contacts` IS NULL),             'No',             'Yes') AS `all_contacts_nm`,         CONCAT(TRIM(IFNULL(`a`.`firstname`, '')),                 ' ',                 TRIM(IFNULL(NULLIF(`a`.`middlename`, ''), '')),                 CONVERT( (CASE                     WHEN                         ((`a`.`middlename` IS NOT NULL)                             AND (`a`.`middlename` <> ''))                     THEN                         ' '                     ELSE ''                 END) USING LATIN1),                 TRIM(IFNULL(`a`.`lastname`, '')),                 ' Contacts: ',                 IFNULL(`a`.`all_contacts`, 'No Contacts')) AS `emp_nm_all_contacts`,         `a`.`unique_id_type` AS `unique_id_type`,         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((`a`.`all_education` IS NULL),             'No',             'Yes') AS `all_education_nm`,         `a`.`education_cnt` AS `education_cnt`,         `a`.`education_yrs` AS `education_yrs`,         IF((`a`.`education_yrs` IS NULL),             'NA',             CONCAT(FLOOR((`a`.`education_yrs` / 365)),                     'Y ',                     FLOOR(((`a`.`education_yrs` % 365) / 30)),                     'M ')) AS `education_yrs_nm`,         CONCAT(IFNULL(`a`.`all_education`, 'NA'),                 ' Total: ',                 CONVERT( IF((`a`.`education_yrs` IS NULL),                     'NA',                     CONCAT(FLOOR((`a`.`education_yrs` / 365)),                             'Y ',                             FLOOR(((`a`.`education_yrs` % 365) / 30)),                             'M ')) USING LATIN1)) AS `all_education_yrs_nm`,         `a`.`all_emp_org` AS `all_emp_org`,         IF((`a`.`all_emp_org` IS NULL),             '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 (`a`.`hrs_actual` IS NULL) 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 (`a`.`hrs_forecast` IS NULL) 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 (`a`.`amt_actual` IS NULL) 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 (`a`.`amt_forcast` IS NULL) 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 (`a`.`vac_use` IS NULL) 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 (`a`.`vac_earn` IS NULL) 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`,         `l`.`file_ser_name` AS `updater_fnm_ser`,         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`,         `n`.`file_ser_name` AS `del_by_fnm_ser`,         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`,         DATE_FORMAT(`a`.`archv_dt`, '%Y') AS `archv_dt_yr`,         DATE_FORMAT(`a`.`archv_dt`, '%M') AS `archv_dt_mon`,         `a`.`archv_by` AS `archv_by`,         `p`.`file_name` AS `archv_by_fnm`,         `p`.`file_ser_name` AS `archv_by_fnm_ser`,         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`.`idstorages` = `e`.`idstorages`)))         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`.`idstorages` = `l`.`idstorages`)))         LEFT JOIN `emp` `m` ON ((`a`.`del_by` = `m`.`emp_id`)))         LEFT JOIN `storages` `n` ON ((`m`.`idstorages` = `n`.`idstorages`)))         LEFT JOIN `emp` `o` ON ((`a`.`archv_by` = `o`.`emp_id`)))         LEFT JOIN `storages` `p` ON ((`o`.`idstorages` = `p`.`idstorages`)))     ORDER BY `a`.`last_updated`