use av_ronak;
CREATE  or replace    ALGORITHM = UNDEFINED      DEFINER = `av_ronak`@`localhost`      SQL SECURITY DEFINER VIEW `av_ronak`.`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         (((((((((((((((((((((((`av_ronak`.`emp_org_hrs_days_cy` `a`         LEFT JOIN `av_ronak`.`emp_org_hrs_days` `b` ON ((`a`.`idemp_org_hrs_days` = `b`.`idemp_org_hrs_days`)))         LEFT JOIN `av_ronak`.`emp_org_hrs` `c` ON ((`b`.`idemp_org_hrs` = `c`.`idemp_org_hrs`)))         LEFT JOIN `av_ronak`.`emp_org` `d` ON ((`c`.`emp_org_id` = `d`.`emp_org_id`)))         LEFT JOIN `av_ronak`.`emp` `e` ON ((`d`.`emp_id` = `e`.`emp_id`)))         LEFT JOIN `av_ronak`.`storages` `f` ON ((`e`.`idstorage` = `f`.`idstorage`)))         LEFT JOIN `av_ronak`.`company` `g` ON ((`d`.`comp_id` = `g`.`comp_id`)))         LEFT JOIN `av_ronak`.`storages` `h` ON ((`g`.`idstorage` = `h`.`idstorage`)))         LEFT JOIN `av_ronak`.`emp_lk_val` `i` ON ((`d`.`emp_type` = `i`.`idemp_lk_value`)))         LEFT JOIN `av_ronak`.`look_street_no` `j` ON ((`b`.`look_street_no_id` = `j`.`look_street_no_id`)))         LEFT JOIN `av_ronak`.`look_street` `k` ON ((`j`.`look_street_id` = `k`.`look_street_id`)))         LEFT JOIN `av_ronak`.`feature_lk_val` `l` ON ((`k`.`street_type` = `l`.`idfeature_lk_val`)))         LEFT JOIN `av_ronak`.`look_postal` `m` ON ((`k`.`postal_id` = `m`.`postal_id`)))         LEFT JOIN `av_ronak`.`look_city` `n` ON ((`m`.`city_id` = `n`.`city_id`)))         LEFT JOIN `av_ronak`.`look_state` `o` ON ((`n`.`state_id` = `o`.`state_id`)))         LEFT JOIN `av_ronak`.`look_country` `p` ON ((`o`.`country_id` = `p`.`country_id`)))         LEFT JOIN `av_ronak`.`emp_lk_val` `q` ON ((`c`.`emp_hrs_title` = `q`.`idemp_lk_value`)))         LEFT JOIN `av_ronak`.`emp` `r` ON ((`c`.`del_by` = `r`.`emp_id`)))         LEFT JOIN `av_ronak`.`storages` `s` ON ((`r`.`idstorage` = `s`.`idstorage`)))         LEFT JOIN `av_ronak`.`emp` `t` ON ((`c`.`archv_by` = `t`.`emp_id`)))         LEFT JOIN `av_ronak`.`storages` `u` ON ((`t`.`idstorage` = `u`.`idstorage`)))         LEFT JOIN `av_ronak`.`feature_lk_val` `v` ON ((`b`.`house_apt_type` = `v`.`idfeature_lk_val`)))         LEFT JOIN `av_ronak`.`emp` `w` ON ((`b`.`updater` = `w`.`emp_id`)))         LEFT JOIN `av_ronak`.`storages` `x` ON ((`w`.`idstorage` = `x`.`idstorage`)))