CREATE OR REPLACE VIEW EMPMILL12.allwindingdata AS select `g`.`company_id` AS `company_id`,`g`.`tran_date` AS `tran_date`,`g`.`spell` AS `spell`,`g`.`shift` AS `shift`,`g`.`WND_MC_ID` AS `WND_MC_ID`,`g`.`prod` AS `prod`,`g`.`prdrem` AS `prdrem`,`g`.`quality_id` AS `quality_id`,`g`.`wnd_q_code` AS `wnd_q_code`,`g`.`quality` AS `quality`,`g`.`no_of_spindle` AS `no_of_spindle`,`g`.`mechine_name` AS `mechine_name`,`g`.`mech_code` AS `mech_code`,`g`.`eb_no` AS `eb_no`,`g`.`wrkhrs` AS `wrkhrs`,`g`.`empname` AS `empname` from (select `prd`.`company_id` AS `company_id`,`prd`.`tran_date` AS `tran_date`,`prd`.`spell` AS `spell`,`prd`.`shift` AS `shift`,`prd`.`WND_MC_ID` AS `WND_MC_ID`,`prd`.`prod` AS `prod`,`prd`.`prdrem` AS `prdrem`,`wdse`.`quality_id` AS `quality_id`,`wqm`.`WND_Q_CODE` AS `wnd_q_code`,`wqm`.`QUALITY` AS `quality`,`wdse`.`no_of_spindle` AS `no_of_spindle`,`vm`.`mechine_name` AS `mechine_name`,`vm`.`mech_code` AS `mech_code`,`dea`.`eb_no` AS `eb_no`,(`da`.`working_hours` - `da`.`idle_hours`) AS `wrkhrs`,concat(`thepd`.`first_name`,ifnull(`thepd`.`middle_name`,''),ifnull(`thepd`.`last_name`,'')) AS `empname` from (((((((select `wsepq`.`company_id` AS `company_id`,`wsepq`.`rec_date` AS `tran_date`,`wsepq`.`spell` AS `spell`,substr(`wsepq`.`spell`,1,1) AS `shift`,`wsepq`.`wnd_mc_id` AS `WND_MC_ID`,`wsepq`.`production` AS `prod`,'1' AS `prdrem` from `EMPMILL12`.`WINDING_SPELL_EB_PROD_QLTY` `wsepq` where (`wsepq`.`is_active` = 1) union all select `wje`.`company_id` AS `company_id`,`wje`.`TRAN_DATE` AS `tran_date`,`wje`.`SPELL` AS `spell`,substr(`wje`.`SPELL`,1,1) AS `shift`,`wje`.`WND_MC_ID` AS `WND_MC_ID`,(0 - `wje`.`WEIGHT`) AS `prod`,'2' AS `prdrem` from `EMPMILL12`.`WINDING_JUGAR_ENTRY` `wje` where ((`wje`.`is_active` = 1) and (`wje`.`OPEN_CLOSE` = 'O')) union all select `wje1`.`company_id` AS `company_id`,`wje1`.`TRAN_DATE` AS `tran_date`,`wje1`.`SPELL` AS `spell`,substr(`wje1`.`SPELL`,1,1) AS `shift`,`wje1`.`WND_MC_ID` AS `WND_MC_ID`,`wje1`.`WEIGHT` AS `prod`,'3' AS `prdrem` from `EMPMILL12`.`WINDING_JUGAR_ENTRY` `wje1` where ((`wje1`.`is_active` = 1) and (`wje1`.`OPEN_CLOSE` = 'C'))) `prd` left join (select `wdse`.`auto_id` AS `auto_id`,`wdse`.`tran_date` AS `tran_date`,`wdse`.`spell` AS `spell`,`wdse`.`update_by` AS `update_by`,`wdse`.`update_date_time` AS `update_date_time`,`wdse`.`created_by` AS `created_by`,`wdse`.`created_date_time` AS `created_date_time`,`wdse`.`update_ip` AS `update_ip`,`wdse`.`quality_id` AS `quality_id`,`wdse`.`wnd_mc_id` AS `wnd_mc_id`,`wdse`.`no_of_spindle` AS `no_of_spindle`,`wdse`.`company_id` AS `company_id`,`wdse`.`branch_id` AS `branch_id`,`wdse`.`is_active` AS `is_active` from `EMPMILL12`.`WINDING_DAILY_SPELL_EB` `wdse` where (`wdse`.`is_active` = 1)) `wdse` on(((`prd`.`company_id` = `wdse`.`company_id`) and (`wdse`.`tran_date` = `prd`.`tran_date`) and (`wdse`.`spell` = `prd`.`spell`) and (`wdse`.`wnd_mc_id` = `prd`.`WND_MC_ID`)))) left join `EMPMILL12`.`WINDING_QUALITY_MASTER` `wqm` on((`wdse`.`quality_id` = `wqm`.`wnd_quality_id`))) left join `vowsls`.`mechine_master` `vm` on((`prd`.`WND_MC_ID` = `vm`.`mechine_id`))) left join (select `dea`.`dtl_rec_id` AS `dtl_rec_id`,`dea`.`attendace_date` AS `attendace_date`,`dea`.`company_id` AS `company_id`,`dea`.`created_by` AS `created_by`,`dea`.`daily_atten_id` AS `daily_atten_id`,`dea`.`eb_code` AS `eb_code`,`dea`.`eb_id` AS `eb_id`,`dea`.`eb_no` AS `eb_no`,`dea`.`is_active` AS `is_active`,`dea`.`mc_id` AS `mc_id`,`dea`.`spell` AS `spell`,`dea`.`updated_date_time` AS `updated_date_time`,`dea`.`updated_by` AS `updated_by`,`dea`.`designation_id` AS `designation_id`,`dea`.`created_date_time` AS `created_date_time`,`dea`.`or_mc_id` AS `or_mc_id`,`dea`.`mc_stoppage_hours` AS `mc_stoppage_hours` from `vowsls`.`daily_ebmc_attendance` `dea` where (`dea`.`is_active` = 1)) `dea` on(((`prd`.`tran_date` = `dea`.`attendace_date`) and (`prd`.`spell` = `dea`.`spell`) and (`prd`.`WND_MC_ID` = `dea`.`mc_id`) and (`prd`.`company_id` = `dea`.`company_id`)))) left join (select `da`.`daily_atten_id` AS `daily_atten_id`,`da`.`attendance_date` AS `attendance_date`,`da`.`attendance_mark` AS `attendance_mark`,`da`.`attendance_source` AS `attendance_source`,`da`.`attendance_type` AS `attendance_type`,`da`.`company_id` AS `company_id`,`da`.`created_by` AS `created_by`,`da`.`created_date_time` AS `created_date_time`,`da`.`device_id` AS `device_id`,`da`.`eb_code` AS `eb_code`,`da`.`eb_id` AS `eb_id`,`da`.`eb_no` AS `eb_no`,`da`.`entry_time` AS `entry_time`,`da`.`exit_time` AS `exit_time`,`da`.`idle_hours` AS `idle_hours`,`da`.`is_active` AS `is_active`,`da`.`remarks` AS `remarks`,`da`.`spell` AS `spell`,`da`.`spell_hours` AS `spell_hours`,`da`.`status_id` AS `status_id`,`da`.`update_date_time` AS `update_date_time`,`da`.`updated_by` AS `updated_by`,`da`.`worked_department_id` AS `worked_department_id`,`da`.`worked_designation_id` AS `worked_designation_id`,`da`.`working_hours` AS `working_hours` from `vowsls`.`daily_attendance` `da` where (`da`.`is_active` = 1)) `da` on((`da`.`daily_atten_id` = `dea`.`daily_atten_id`))) left join `vowsls`.`tbl_hrms_ed_personal_details` `thepd` on((`dea`.`eb_id` = `thepd`.`eb_id`)))) `g`;;
CREATE OR REPLACE VIEW EMPMILL12.dailyattview AS select `a`.`daily_atten_id` AS `att_tran_id`,`a`.`attendance_date` AS `attandance_date`,`a`.`spell` AS `spell`,`a`.`worked_department_id` AS `dept_id`,`a`.`spell_hours` AS `dept_working_hrs`,`a`.`idle_hours` AS `dept_idle_hrs`,`b`.`eb_id` AS `eb_id`,`a`.`eb_no` AS `eb_no`,(`a`.`working_hours` - `a`.`idle_hours`) AS `working_hrs`,`a`.`idle_hours` AS `idle_hrs`,`a`.`worked_designation_id` AS `occu_id`,`a`.`attendance_type` AS `regular_ot`,`a`.`created_by` AS `user_id`,`a`.`created_date_time` AS `entry_date`,concat(`b`.`worker_name`,' ',ifnull(`b`.`middle_name`,''),' ',ifnull(`b`.`last_name`,' ')) AS `WRK_NAME`,`c`.`dept_desc` AS `dept_desc`,`d`.`desig` AS `occu_desc`,`d`.`time_piece` AS `t_p`,`e`.`cata_id` AS `cata_id`,`e`.`cata_desc` AS `cata_desc`,'' AS `hoccu_code`,`d`.`time_piece` AS `time_piece`,substr(`a`.`spell`,1,1) AS `shift`,`c`.`dept_code` AS `dept_code`,'' AS `offday`,`a`.`worked_department_id` AS `hands_dept_code`,`f`.`comp_id` AS `comp_id`,(case when ((`a`.`spell` = 'C') and (`a`.`attendance_type` = 'R') and ((`a`.`working_hours` - `a`.`idle_hours`) = 7.5)) then 0.5 else 0 end) AS `nhrs` from (((((`vowsls`.`daily_attendance` `a` join `vowsls`.`worker_master` `b`) join `vowsls`.`department_master` `c`) join `vowsls`.`designation` `d`) join `vowsls`.`category_master` `e`) join `vowsls`.`company_master` `f`) where ((`a`.`eb_id` = `b`.`eb_id`) and (`a`.`worked_department_id` = `c`.`dept_id`) and (`d`.`id` = `a`.`worked_designation_id`) and (`e`.`cata_id` = `b`.`cata_id`) and (`a`.`company_id` = `f`.`comp_id`) and (`a`.`eb_no` <> 'C0000') and (`a`.`working_hours` > 0) and (`a`.`is_active` = 1));;
CREATE OR REPLACE VIEW EMPMILL12.dailycatasumm AS select `h`.`comp_id` AS `comp_id`,`h`.`ATTANDANCE_DATE` AS `ATTANDANCE_DATE`,`h`.`cata_id` AS `cata_id`,`h`.`cata_desc` AS `cata_desc`,`h`.`DEPT_ID` AS `DEPT_ID`,`h`.`DEPT_code` AS `DEPT_code`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'R')) then `h`.`noofemp` else 0 end)) AS `ASFT`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'R')) then `h`.`noofemp` else 0 end)) AS `BSFT`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'R')) then `h`.`noofemp` else 0 end)) AS `CSFT`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'R')) then `h`.`wrkhrs` else 0 end)) AS `AWH`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'R')) then `h`.`wrkhrs` else 0 end)) AS `BWH`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'R')) then `h`.`wrkhrs` else 0 end)) AS `CWH`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'O')) then `h`.`noofemp` else 0 end)) AS `OSFTA`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'O')) then `h`.`noofemp` else 0 end)) AS `OSFTB`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'O')) then `h`.`noofemp` else 0 end)) AS `OSFTC`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'O')) then `h`.`wrkhrs` else 0 end)) AS `OAWH`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'O')) then `h`.`wrkhrs` else 0 end)) AS `OBWH`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'O')) then `h`.`wrkhrs` else 0 end)) AS `OCWH`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'C')) then `h`.`noofemp` else 0 end)) AS `CSFTA`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'C')) then `h`.`noofemp` else 0 end)) AS `CSFTB`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'C')) then `h`.`noofemp` else 0 end)) AS `CSFTC`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'C')) then `h`.`wrkhrs` else 0 end)) AS `CAWH`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'C')) then `h`.`wrkhrs` else 0 end)) AS `CBWH`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'C')) then `h`.`wrkhrs` else 0 end)) AS `CCWH`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'R')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `AHND`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'R')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `BHND`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'R')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `CHND`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'O')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `OAHND`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'O')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `OBHND`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'O')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `OCHND`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'C')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `CAHND`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'C')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `CBHND`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'C')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `CCHND` from (select `g`.`comp_id` AS `comp_id`,`g`.`ATTANDANCE_DATE` AS `ATTANDANCE_DATE`,`g`.`cata_id` AS `cata_id`,`g`.`cata_desc` AS `cata_desc`,`g`.`DEPT_ID` AS `DEPT_ID`,`g`.`DEPT_code` AS `DEPT_code`,`g`.`shift` AS `shift`,`g`.`regular_ot` AS `regular_ot`,count(0) AS `noofemp`,sum(`g`.`wrkhrs`) AS `wrkhrs` from (select `EMPMILL12`.`dailyattview`.`comp_id` AS `comp_id`,`EMPMILL12`.`dailyattview`.`attandance_date` AS `ATTANDANCE_DATE`,`EMPMILL12`.`dailyattview`.`cata_id` AS `cata_id`,`EMPMILL12`.`dailyattview`.`cata_desc` AS `cata_desc`,`EMPMILL12`.`dailyattview`.`dept_id` AS `DEPT_ID`,`EMPMILL12`.`dailyattview`.`dept_code` AS `DEPT_code`,`EMPMILL12`.`dailyattview`.`shift` AS `shift`,`EMPMILL12`.`dailyattview`.`regular_ot` AS `regular_ot`,(case when ((`EMPMILL12`.`dailyattview`.`shift` = 'C') and (sum(`EMPMILL12`.`dailyattview`.`working_hrs`) = 7.5) and (`EMPMILL12`.`dailyattview`.`regular_ot` = 'R')) then (sum(`EMPMILL12`.`dailyattview`.`working_hrs`) + 0.5) else sum(`EMPMILL12`.`dailyattview`.`working_hrs`) end) AS `wrkhrs` from `EMPMILL12`.`dailyattview` group by `EMPMILL12`.`dailyattview`.`comp_id`,`EMPMILL12`.`dailyattview`.`attandance_date`,`EMPMILL12`.`dailyattview`.`cata_id`,`EMPMILL12`.`dailyattview`.`cata_desc`,`EMPMILL12`.`dailyattview`.`dept_id`,`EMPMILL12`.`dailyattview`.`dept_code`,`EMPMILL12`.`dailyattview`.`shift`,`EMPMILL12`.`dailyattview`.`regular_ot`) `g` group by `g`.`comp_id`,`g`.`ATTANDANCE_DATE`,`g`.`cata_id`,`g`.`cata_desc`,`g`.`DEPT_ID`,`g`.`DEPT_code`,`g`.`shift`,`g`.`regular_ot`) `h` group by `h`.`comp_id`,`h`.`ATTANDANCE_DATE`,`h`.`cata_id`,`h`.`cata_desc`,`h`.`DEPT_ID`,`h`.`DEPT_code`;;
CREATE OR REPLACE VIEW EMPMILL12.dailycattview AS select `a`.`daily_atten_id` AS `att_tran_id`,`a`.`attendance_date` AS `attandance_date`,`a`.`spell` AS `spell`,`a`.`worked_department_id` AS `dept_id`,`a`.`spell_hours` AS `dept_working_hrs`,`a`.`idle_hours` AS `dept_idle_hrs`,`a`.`eb_no` AS `eb_no`,(`a`.`working_hours` - `a`.`idle_hours`) AS `working_hrs`,`a`.`idle_hours` AS `idle_hrs`,`a`.`worked_designation_id` AS `occu_id`,`a`.`attendance_type` AS `regular_ot`,`a`.`created_by` AS `user_id`,`a`.`created_date_time` AS `entry_date`,concat(`b`.`worker_name`,' ',ifnull(`b`.`last_name`,' ')) AS `WRK_NAME`,`c`.`dept_desc` AS `dept_desc`,`d`.`desig` AS `occu_desc`,`e`.`cata_desc` AS `cata_desc`,'' AS `hoccu_code`,`d`.`time_piece` AS `time_piece`,substr(`a`.`spell`,1,1) AS `shift`,`a`.`worked_department_id` AS `dept_code`,'' AS `offday`,`a`.`worked_department_id` AS `hands_dept_code`,`f`.`comp_id` AS `comp_id` from `vowsls`.`daily_attendance` `a` join `vowsls`.`worker_master` `b` join `vowsls`.`department_master` `c` join `vowsls`.`designation` `d` join `vowsls`.`category_master` `e` join `vowsls`.`company_master` `f` where ((`a`.`eb_id` = `b`.`eb_id`) and (`a`.`worked_department_id` = `c`.`dept_id`) and (`d`.`id` = `a`.`worked_designation_id`) and (`e`.`cata_id` = `b`.`cata_id`) and (`a`.`company_id` = `f`.`comp_id`) and (`a`.`eb_no` = 'C0000') and (`a`.`working_hours` > 0) and (`a`.`is_active` = 1));;
CREATE OR REPLACE VIEW EMPMILL12.dailyhandcomp AS select `h`.`comp_id` AS `comp_id`,`h`.`ATTANDANCE_DATE` AS `ATTANDANCE_DATE`,`h`.`OCCU_ID` AS `OCCU_ID`,`h`.`HOCCU_CODE` AS `HOCCU_CODE`,`h`.`OCCU_DESC` AS `OCCU_DESC`,`h`.`DEPT_ID` AS `DEPT_ID`,`h`.`dept_code` AS `dept_code`,`h`.`dept_desc` AS `dept_desc`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'R')) then `h`.`noofemp` else 0 end)) AS `ASFT`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'R')) then `h`.`noofemp` else 0 end)) AS `BSFT`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'R')) then `h`.`noofemp` else 0 end)) AS `CSFT`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'R')) then `h`.`wrkhrs` else 0 end)) AS `AWH`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'R')) then `h`.`wrkhrs` else 0 end)) AS `BWH`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'R')) then `h`.`wrkhrs` else 0 end)) AS `CWH`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'O')) then `h`.`noofemp` else 0 end)) AS `OSFTA`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'O')) then `h`.`noofemp` else 0 end)) AS `OSFTB`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'O')) then `h`.`noofemp` else 0 end)) AS `OSFTC`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'O')) then `h`.`wrkhrs` else 0 end)) AS `OAWH`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'O')) then `h`.`wrkhrs` else 0 end)) AS `OBWH`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'O')) then `h`.`wrkhrs` else 0 end)) AS `OCWH`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'C')) then `h`.`noofemp` else 0 end)) AS `CSFTA`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'C')) then `h`.`noofemp` else 0 end)) AS `CSFTB`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'C')) then `h`.`noofemp` else 0 end)) AS `CSFTC`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'C')) then `h`.`wrkhrs` else 0 end)) AS `CAWH`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'C')) then `h`.`wrkhrs` else 0 end)) AS `CBWH`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'C')) then `h`.`wrkhrs` else 0 end)) AS `CCWH`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'R')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `AHND`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'R')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `BHND`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'R')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `CHND`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'O')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `OAHND`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'O')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `OBHND`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'O')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `OCHND`,max((case when ((`h`.`shift` = 'A') and (`h`.`regular_ot` = 'C')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `CAHND`,max((case when ((`h`.`shift` = 'B') and (`h`.`regular_ot` = 'C')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `CBHND`,max((case when ((`h`.`shift` = 'C') and (`h`.`regular_ot` = 'C')) then (`h`.`wrkhrs` / 8) else 0 end)) AS `CCHND` from (select `g`.`comp_id` AS `comp_id`,`g`.`ATTANDANCE_DATE` AS `ATTANDANCE_DATE`,`g`.`OCCU_ID` AS `OCCU_ID`,`g`.`HOCCU_CODE` AS `HOCCU_CODE`,`g`.`OCCU_DESC` AS `OCCU_DESC`,`g`.`DEPT_ID` AS `DEPT_ID`,`g`.`dept_code` AS `dept_code`,`g`.`dept_desc` AS `dept_desc`,`g`.`shift` AS `shift`,`g`.`regular_ot` AS `regular_ot`,count(0) AS `noofemp`,sum(`g`.`wrkhrs`) AS `wrkhrs` from (select `EMPMILL12`.`dailyattview`.`comp_id` AS `comp_id`,`EMPMILL12`.`dailyattview`.`attandance_date` AS `ATTANDANCE_DATE`,`EMPMILL12`.`dailyattview`.`occu_id` AS `OCCU_ID`,`EMPMILL12`.`dailyattview`.`hoccu_code` AS `HOCCU_CODE`,`EMPMILL12`.`dailyattview`.`occu_desc` AS `OCCU_DESC`,`EMPMILL12`.`dailyattview`.`dept_id` AS `DEPT_ID`,`EMPMILL12`.`dailyattview`.`dept_code` AS `dept_code`,`EMPMILL12`.`dailyattview`.`dept_desc` AS `dept_desc`,`EMPMILL12`.`dailyattview`.`shift` AS `shift`,`EMPMILL12`.`dailyattview`.`regular_ot` AS `regular_ot`,`EMPMILL12`.`dailyattview`.`eb_no` AS `eb_no`,(case when ((`EMPMILL12`.`dailyattview`.`shift` = 'C') and (sum(`EMPMILL12`.`dailyattview`.`working_hrs`) = 7.5)) then (sum(`EMPMILL12`.`dailyattview`.`working_hrs`) + 0.5) else sum(`EMPMILL12`.`dailyattview`.`working_hrs`) end) AS `wrkhrs` from `EMPMILL12`.`dailyattview` group by `EMPMILL12`.`dailyattview`.`comp_id`,`EMPMILL12`.`dailyattview`.`attandance_date`,`EMPMILL12`.`dailyattview`.`occu_id`,`EMPMILL12`.`dailyattview`.`hoccu_code`,`EMPMILL12`.`dailyattview`.`occu_desc`,`EMPMILL12`.`dailyattview`.`dept_id`,`EMPMILL12`.`dailyattview`.`dept_code`,`EMPMILL12`.`dailyattview`.`dept_desc`,`EMPMILL12`.`dailyattview`.`shift`,`EMPMILL12`.`dailyattview`.`regular_ot`,`EMPMILL12`.`dailyattview`.`eb_no`) `g` group by `g`.`comp_id`,`g`.`ATTANDANCE_DATE`,`g`.`OCCU_ID`,`g`.`HOCCU_CODE`,`g`.`OCCU_DESC`,`g`.`DEPT_ID`,`g`.`dept_code`,`g`.`dept_desc`,`g`.`shift`,`g`.`regular_ot`) `h` group by `h`.`comp_id`,`h`.`ATTANDANCE_DATE`,`h`.`OCCU_ID`,`h`.`HOCCU_CODE`,`h`.`OCCU_DESC`,`h`.`DEPT_ID`,`h`.`dept_code`,`h`.`dept_desc`;;
CREATE OR REPLACE VIEW EMPMILL12.frmlastdetails AS select `g`.`mechine_id` AS `mechine_id`,`g`.`trollyid` AS `trollyid` from ((select `d`.`auto_id` AS `auto_id`,`d`.`frameno` AS `frameno`,`mm`.`mechine_id` AS `mechine_id`,`t`.`trollyid` AS `trollyid`,`d`.`trollyno` AS `trollyno` from ((`vowsls`.`dofftable` `d` left join `vowsls`.`mechine_master` `mm` on(((`d`.`frameno` = `mm`.`frame_no`) and (`d`.`company_id` = `mm`.`company_id`)))) left join `vowsls`.`trollymst` `t` on(((`d`.`trollyno` = `t`.`trollyno`) and (`d`.`company_id` = `t`.`company_id`)))) where ((`d`.`is_active` = 1) and (`mm`.`mechine_id` is not null) and (`t`.`process_type` = 2))) `g` join (select `d`.`frameno` AS `frameno`,max(`d`.`auto_id`) AS `mxid` from `vowsls`.`dofftable` `d` where ((`d`.`company_id` = 2) and (`d`.`is_active` = 1)) group by `d`.`frameno`) `v` on((`g`.`auto_id` = `v`.`mxid`)));;
CREATE OR REPLACE VIEW EMPMILL12.half_day_data AS select `spl`.`eb_id` AS `eb_id`,`spl`.`attendance_date` AS `attendance_date`,`spl`.`shift` AS `shift`,`spl`.`spl1wrkhrs` AS `spl1wrkhrs`,`spl`.`spl2wrkhrs` AS `spl2wrkhrs`,`allsft`.`whrs` AS `whrs` from ((select `g`.`eb_id` AS `eb_id`,`g`.`attendance_date` AS `attendance_date`,`g`.`shift` AS `shift`,sum(`g`.`spl1wrkhrs`) AS `spl1wrkhrs`,sum(`g`.`spl2wrkhrs`) AS `spl2wrkhrs` from (select `da`.`company_id` AS `company_id`,`da`.`eb_id` AS `eb_id`,`da`.`attendance_date` AS `attendance_date`,substr(`da`.`spell`,1,1) AS `shift`,`da`.`working_hours` AS `spl1wrkhrs`,0 AS `spl2wrkhrs` from `vowsls`.`daily_attendance` `da` where ((`da`.`attendance_type` = 'R') and (`da`.`spell` = 'A1') and (`da`.`is_active` = 1)) union all select `da`.`company_id` AS `company_id`,`da`.`eb_id` AS `eb_id`,`da`.`attendance_date` AS `attendance_date`,substr(`da`.`spell`,1,1) AS `shift`,0 AS `spl1wrkhrs`,`da`.`working_hours` AS `spl2wrkhrs` from `vowsls`.`daily_attendance` `da` where ((`da`.`attendance_type` = 'R') and (`da`.`spell` = 'A2') and (`da`.`is_active` = 1)) union all select `da`.`company_id` AS `company_id`,`da`.`eb_id` AS `eb_id`,`da`.`attendance_date` AS `attendance_date`,substr(`da`.`spell`,1,1) AS `shift`,`da`.`working_hours` AS `spl1wrkhrs`,0 AS `spl2wrkhrs` from `vowsls`.`daily_attendance` `da` where ((`da`.`attendance_type` = 'R') and (`da`.`spell` = 'B1') and (`da`.`is_active` = 1)) union all select `da`.`company_id` AS `company_id`,`da`.`eb_id` AS `eb_id`,`da`.`attendance_date` AS `attendance_date`,substr(`da`.`spell`,1,1) AS `shift`,0 AS `spl1wrkhrs`,(`da`.`working_hours` - `da`.`idle_hours`) AS `spl2wrkhrs` from `vowsls`.`daily_attendance` `da` where ((`da`.`attendance_type` = 'R') and (`da`.`spell` = 'B2') and (`da`.`is_active` = 1))) `g` group by `g`.`eb_id`,`g`.`attendance_date`,`g`.`shift`) `spl` join (select `da`.`eb_id` AS `eb_id`,`da`.`attendance_date` AS `attendance_date`,sum(`da`.`working_hours`) AS `whrs` from `vowsls`.`daily_attendance` `da` where ((`da`.`attendance_type` = 'R') and (`da`.`is_active` = 1)) group by `da`.`eb_id`,`da`.`attendance_date`) `allsft` on(((`spl`.`eb_id` = `allsft`.`eb_id`) and (`spl`.`attendance_date` = `allsft`.`attendance_date`) and (`spl`.`spl1wrkhrs` > 0) and (`spl`.`spl2wrkhrs` = 0) and (`allsft`.`whrs` <> 8))));;
CREATE OR REPLACE VIEW EMPMILL12.spellwindingdata AS select `prd`.`company_id` AS `company_id`,`prd`.`tran_date` AS `tran_date`,`prd`.`spell` AS `spell`,`prd`.`shift` AS `shift`,`prd`.`WND_MC_ID` AS `WND_MC_ID`,sum(`prd`.`prod`) AS `prod`,`wdse`.`quality_id` AS `quality_id`,`wqm`.`WND_Q_CODE` AS `wnd_q_code`,`wqm`.`QUALITY` AS `quality`,`wqm`.`OCCU_CODE` AS `wqoc_code`,`wqm`.`TARGET_PROD` AS `TARGET_PROD`,`wgm2`.`WND_GROUP_NAME` AS `WND_GROUP_NAME`,`wdse`.`no_of_spindle` AS `no_of_spindle`,`mm`.`mechine_name` AS `mechine_name`,`mm`.`mech_code` AS `mech_code` from ((((((select `wsepq`.`company_id` AS `company_id`,`wsepq`.`rec_date` AS `tran_date`,`wsepq`.`spell` AS `spell`,substr(`wsepq`.`spell`,1,1) AS `shift`,`wsepq`.`wnd_mc_id` AS `WND_MC_ID`,`wsepq`.`production` AS `prod`,'1' AS `prdrem` from `EMPMILL12`.`WINDING_SPELL_EB_PROD_QLTY` `wsepq` where (`wsepq`.`is_active` = 1) union all select `wje`.`company_id` AS `company_id`,`wje`.`TRAN_DATE` AS `tran_date`,`wje`.`SPELL` AS `spell`,substr(`wje`.`SPELL`,1,1) AS `shift`,`wje`.`WND_MC_ID` AS `WND_MC_ID`,(0 - `wje`.`WEIGHT`) AS `prod`,'2' AS `prdrem` from `EMPMILL12`.`WINDING_JUGAR_ENTRY` `wje` where ((`wje`.`is_active` = 1) and (`wje`.`OPEN_CLOSE` = 'O')) union all select `wje1`.`company_id` AS `company_id`,`wje1`.`TRAN_DATE` AS `tran_date`,`wje1`.`SPELL` AS `spell`,substr(`wje1`.`SPELL`,1,1) AS `shift`,`wje1`.`WND_MC_ID` AS `WND_MC_ID`,`wje1`.`WEIGHT` AS `prod`,'3' AS `prdrem` from `EMPMILL12`.`WINDING_JUGAR_ENTRY` `wje1` where ((`wje1`.`is_active` = 1) and (`wje1`.`OPEN_CLOSE` = 'C'))) `prd` left join (select `wdse`.`auto_id` AS `auto_id`,`wdse`.`tran_date` AS `tran_date`,`wdse`.`spell` AS `spell`,`wdse`.`update_by` AS `update_by`,`wdse`.`update_date_time` AS `update_date_time`,`wdse`.`created_by` AS `created_by`,`wdse`.`created_date_time` AS `created_date_time`,`wdse`.`update_ip` AS `update_ip`,`wdse`.`quality_id` AS `quality_id`,`wdse`.`wnd_mc_id` AS `wnd_mc_id`,`wdse`.`no_of_spindle` AS `no_of_spindle`,`wdse`.`company_id` AS `company_id`,`wdse`.`branch_id` AS `branch_id`,`wdse`.`is_active` AS `is_active` from `EMPMILL12`.`WINDING_DAILY_SPELL_EB` `wdse` where (`wdse`.`is_active` = 1)) `wdse` on(((`prd`.`company_id` = `wdse`.`company_id`) and (`wdse`.`tran_date` = `prd`.`tran_date`) and (`wdse`.`spell` = `prd`.`spell`) and (`wdse`.`wnd_mc_id` = `prd`.`WND_MC_ID`)))) left join `EMPMILL12`.`WINDING_QUALITY_MASTER` `wqm` on((`wdse`.`quality_id` = `wqm`.`wnd_quality_id`))) left join `EMPMILL12`.`WINDING_GROUP_MASTER` `wgm` on((`wqm`.`WND_GR_CODE` = `wgm`.`WND_GR_CODE`))) left join `EMPMILL12`.`WINDING_GROUP_MASTER` `wgm2` on((`wgm`.`WND_MAIN_GR_CODE` = `wgm2`.`WND_GR_CODE`))) left join `vowsls`.`mechine_master` `mm` on((`prd`.`WND_MC_ID` = `mm`.`mechine_id`))) group by `prd`.`company_id`,`prd`.`tran_date`,`prd`.`spell`,`prd`.`shift`,`prd`.`WND_MC_ID`,`wdse`.`quality_id`,`wqm`.`WND_Q_CODE`,`wqm`.`QUALITY`,`wdse`.`no_of_spindle`,`mm`.`mechine_name`,`mm`.`mech_code`,`wgm2`.`WND_GROUP_NAME`,`wqm`.`OCCU_CODE`,`wqm`.`TARGET_PROD`;;
CREATE OR REPLACE VIEW EMPMILL12.view_proc_att_leave_holiday AS select `da`.`eb_id` AS `eb_id`,`da`.`eb_no` AS `eb_no`,`da`.`attendance_date` AS `attdate`,`da`.`attendance_type` AS `attendance_type`,`da`.`company_id` AS `company_id`,`da`.`worked_department_id` AS `dept_id`,`da`.`worked_designation_id` AS `desg_id`,`da`.`spell` AS `spell`,(case when (((`da`.`working_hours` - `da`.`idle_hours`) = 7.5) and (`da`.`spell` = 'C')) then 7.5 else (`da`.`working_hours` - `da`.`idle_hours`) end) AS `whrs`,(case when (((`da`.`working_hours` - `da`.`idle_hours`) = 7.5) and (`da`.`spell` = 'C')) then 0.5 else 0 end) AS `nhrs` from `vowsls`.`daily_attendance` `da` where (`da`.`is_active` = 1) union all select `thht`.`eb_id` AS `eb_id`,`wm`.`eb_no` AS `eb_no`,`hm`.`holiday_date` AS `attdate`,'H' AS `attendance_type`,`hm`.`company_id` AS `company_id`,`wm`.`dept_id` AS `dept_id`,`wm`.`desg_id` AS `desg_id`,' ' AS `spell`,8 AS `whrs`,0 AS `nhsr` from ((`vowsls`.`tbl_hrms_holiday_transactions` `thht` join `vowsls`.`holiday_master` `hm` on((`thht`.`holiday_id` = `hm`.`id`))) join `vowsls`.`worker_master` `wm` on((`thht`.`eb_id` = `wm`.`eb_id`))) where (`thht`.`is_active` = 1) union all select `lt`.`eb_id` AS `eb_id`,`wm`.`eb_no` AS `eb_no`,`ltd`.`leave_date` AS `attdate`,`ltp`.`leave_type_code` AS `atttype`,`lt`.`company_id` AS `company_id`,`wm`.`dept_id` AS `dept_id`,`wm`.`desg_id` AS `desg_id`,' ' AS `spell`,8 AS `whrs`,0 AS `nhrs` from (((`vowsls`.`leave_tran_details` `ltd` join `vowsls`.`leave_transactions` `lt` on((`ltd`.`ltran_id` = `lt`.`leave_transaction_id`))) join `vowsls`.`leave_types` `ltp` on((`ltp`.`leave_type_id` = `lt`.`leave_type_id`))) join `vowsls`.`worker_master` `wm` on((`wm`.`eb_id` = `lt`.`eb_id`))) where (`ltd`.`is_active` = 1);;
CREATE OR REPLACE VIEW EMPMILL12.view_proc_daily_doff_details AS select `dprd`.`compid` AS `compid`,`dprd`.`doffdate` AS `doffdate`,`dprd`.`spell` AS `spell`,`dprd`.`ebno` AS `ebno`,`dprd`.`frameno` AS `frameno`,`dprd`.`mechine_id` AS `mechine_id`,`dprd`.`loom_type_id` AS `loom_type_id`,`dprd`.`no_of_doff` AS `no_of_doff`,`dprd`.`prod` AS `prod`,`dprd`.`prod100` AS `prod100`,`dprd`.`stdodffwt` AS `stdodffwt`,`dprd`.`mcwhrs` AS `mcwhrs`,`attn`.`atthrs` AS `atthrs`,`attn`.`eb_id` AS `eb_id`,`attn`.`worked_designation_id` AS `worked_designation_id` from ((select `prod`.`compid` AS `compid`,`prod`.`doffdate` AS `doffdate`,`prod`.`spell` AS `spell`,`prod`.`ebno` AS `ebno`,`prod`.`frameno` AS `frameno`,`prod`.`mechine_id` AS `mechine_id`,`prod`.`loom_type_id` AS `loom_type_id`,sum(`prod`.`no_of_doff`) AS `no_of_doff`,sum(`prod`.`prod`) AS `prod`,sum(`prod`.`prod100`) AS `prod100`,(sum((`prod`.`std_doff_wt` * `prod`.`mcwhrs`)) / sum(`prod`.`mcwhrs`)) AS `stdodffwt`,sum(`prod`.`mcwhrs`) AS `mcwhrs` from (select `doff`.`doffdate` AS `doffdate`,`doff`.`compid` AS `compid`,`doff`.`mechine_id` AS `mechine_id`,`doff`.`frameno` AS `frameno`,`doff`.`ebno` AS `ebno`,`doff`.`spell` AS `spell`,`doff`.`Shift` AS `Shift`,`doff`.`qcode` AS `qcode`,`doff`.`loom_type_id` AS `loom_type_id`,`doff`.`prod` AS `prod`,`doff`.`no_of_doff` AS `no_of_doff`,`att`.`attendance_date` AS `attendance_date`,`att`.`attspell` AS `attspell`,`att`.`mc_id` AS `mc_id`,`att`.`mcwhrs` AS `mcwhrs`,`wqm`.`tpi` AS `tpi`,`wqm`.`std_doff_wt` AS `std_doff_wt`,`wqm`.`speed` AS `speed`,`wqm`.`spindle_count` AS `spindle_count`,`tpwqm`.`spg_actual_count` AS `spg_actual_count`,round((((((`wqm`.`speed` * 60) * `att`.`mcwhrs`) * `tpwqm`.`spg_actual_count`) * `wqm`.`spindle_count`) / (((`wqm`.`tpi` * 14400) * 2.2046) * 36)),3) AS `prod100` from ((((select `dftbl`.`doffdate` AS `doffdate`,`dftbl`.`company_id` AS `compid`,`mm`.`mechine_id` AS `mechine_id`,`dftbl`.`frameno` AS `frameno`,`dftbl`.`ebno` AS `ebno`,`dftbl`.`spell` AS `spell`,substr(`dftbl`.`spell`,1,1) AS `Shift`,`dftbl`.`q_code` AS `qcode`,`tlmod`.`loom_type_id` AS `loom_type_id`,round(sum(`dftbl`.`netwt`),3) AS `prod`,count(0) AS `no_of_doff` from (((`vowsls`.`dofftable` `dftbl` left join `vowsls`.`mechine_master` `mm` on(((`dftbl`.`frameno` = `mm`.`frame_no`) and (`dftbl`.`company_id` = `mm`.`company_id`)))) left join `EMPMILL12`.`tbl_loom_master_other_details` `tlmod` on((`mm`.`mechine_id` = `tlmod`.`mechine_id`))) left join `EMPMILL12`.`tbl_loom_type` `tlt` on((`tlmod`.`loom_type_id` = `tlt`.`loom_type_id`))) where ((`dftbl`.`netwt` > 0) and (`tlt`.`dept_type` = 'Spg') and (`dftbl`.`is_active` = 1)) group by `dftbl`.`doffdate`,`dftbl`.`company_id`,`mm`.`mechine_id`,`dftbl`.`frameno`,`dftbl`.`ebno`,`dftbl`.`spell`,substr(`dftbl`.`spell`,1,1),`dftbl`.`q_code`,`tlmod`.`loom_type_id`) `doff` left join (select `tewh`.`atten_date` AS `attendance_date`,`tewh`.`att_spell` AS `attspell`,`tewh`.`att_mc_id` AS `mc_id`,((`tewh`.`att_working_hours` - `tewh`.`att_idle_hours`) - ifnull(`tewh`.`att_mc_stoppage_hours`,0)) AS `mcwhrs` from `EMPMILL12`.`tbl_emp_working_hours` `tewh` where (`tewh`.`att_desig_id` in (50,195,213,241,242,252,928,929,1132,1143,1308,1309))) `att` on(((`doff`.`doffdate` = `att`.`attendance_date`) and (`doff`.`spell` = `att`.`attspell`) and (`doff`.`mechine_id` = `att`.`mc_id`)))) left join `vowsls`.`weaving_quality_master` `wqm` on(((`doff`.`qcode` = `wqm`.`quality_code`) and (`doff`.`compid` = `wqm`.`company_id`)))) left join `vowsls`.`tbl_prod_weaving_quality_mapping` `tpwqm` on(((`wqm`.`quality_id` = `tpwqm`.`quality_id`) and (`tpwqm`.`mapping_date` = `doff`.`doffdate`))))) `prod` group by `prod`.`doffdate`,`prod`.`ebno`,`prod`.`spell`,`prod`.`frameno`,`prod`.`compid`,`prod`.`mechine_id`,`prod`.`loom_type_id`) `dprd` left join (select `da`.`company_id` AS `company_id`,`da`.`eb_id` AS `eb_id`,`da`.`eb_no` AS `eb_no`,`da`.`attendance_date` AS `attendance_date`,`da`.`spell` AS `spell`,`da`.`worked_designation_id` AS `worked_designation_id`,(`da`.`working_hours` - `da`.`idle_hours`) AS `atthrs` from `vowsls`.`daily_attendance` `da` where ((`da`.`is_active` = 1) and (`da`.`worked_designation_id` in (50,195,213,241,242,252,928,929,1132,1143,1308,1309)))) `attn` on(((`dprd`.`doffdate` = `attn`.`attendance_date`) and (`dprd`.`ebno` = `attn`.`eb_no`) and (`dprd`.`spell` = `attn`.`spell`) and (`dprd`.`compid` = `attn`.`company_id`))));;
CREATE OR REPLACE VIEW EMPMILL12.view_proc_njm_loom_data_for_wages AS select `wm`.`eb_id` AS `eb_id`,`mm`.`mechine_name` AS `mechine_name`,`l`.`loom_date` AS `loom_date`,`l`.`shift` AS `shift`,`l`.`loom_id` AS `loom_id`,`l`.`diffm` AS `diffm`,`l`.`qcod` AS `qcod`,`l`.`tot_hrs` AS `tot_hrs`,`l`.`tktno` AS `tktno`,`l`.`wdept_id` AS `wdept_id`,`l`.`ggp` AS `ggp`,`l`.`working_hours` AS `working_hours`,`l`.`company_id` AS `company_id`,`wqm`.`lrs` AS `reed_sp`,`wdt`.`ashots` AS `ashots`,`tlmod`.`sardar_line_id` AS `sardar_line_id`,`tlmod`.`helper_line_id` AS `helper_line_id`,`mm`.`reed_speed` AS `reed_speed`,(case when (`mm`.`reed_speed` < 34) then 168 when ((`mm`.`reed_speed` >= 34) and (`mm`.`reed_speed` < 50)) then 162.5 when (`mm`.`reed_speed` >= 50) then 144 else 0 end) AS `speed`,(case when (`mm`.`reed_speed` < 34) then ((((((168 * `l`.`tot_hrs`) * 60) / (`wdt`.`ashots` * 36)) * 65) / 100) * 2) when ((`mm`.`reed_speed` >= 34) and (`mm`.`reed_speed` < 50)) then ((((((162.5 * `l`.`tot_hrs`) * 60) / (`wdt`.`ashots` * 36)) * 65) / 100) * 2) when (`mm`.`reed_speed` >= 50) then ((((((144 * `l`.`tot_hrs`) * 60) / (`wdt`.`ashots` * 36)) * 65) / 100) * 2) else 0 end) AS `tgprd`,(case when (`mm`.`reed_speed` < 34) then ((((1200 / 26) / 8) * `l`.`tot_hrs`) / ((((((168 * `l`.`tot_hrs`) * 60) / (`wdt`.`ashots` * 36)) * 65) / 100) * 2)) when ((`mm`.`reed_speed` >= 34) and (`mm`.`reed_speed` < 50)) then ((((1200 / 26) / 8) * `l`.`tot_hrs`) / ((((((162.5 * `l`.`tot_hrs`) * 60) / (`wdt`.`ashots` * 36)) * 65) / 100) * 2)) when (`mm`.`reed_speed` >= 50) then ((((1200 / 26) / 8) * `l`.`tot_hrs`) / ((((((144 * `l`.`tot_hrs`) * 60) / (`wdt`.`ashots` * 36)) * 65) / 100) * 2)) else 0 end) AS `rts`,`mm1`.`mach_shr_code` AS `sardline`,`mm2`.`mach_shr_code` AS `helpline` from ((((((((select `g`.`loom_date` AS `loom_date`,`g`.`shift` AS `shift`,`g`.`loom_id` AS `loom_id`,round(`g`.`diffm`,2) AS `diffm`,`g`.`qcod` AS `qcod`,`g`.`tot_hrs` AS `tot_hrs`,`g`.`tktnoc` AS `tktno`,`g`.`wdept_id` AS `wdept_id`,replace(right(`dm`.`dept_desc`,3),' ','') AS `ggp`,`g`.`working_hours` AS `working_hours`,1 AS `company_id` from ((select `a`.`loom_date` AS `loom_date`,`a`.`shift` AS `shift`,`a`.`loom_id` AS `loom_id`,`a`.`diffm` AS `diffm`,`a`.`qcod` AS `qcod`,`a`.`tot_hrs` AS `tot_hrs`,`a`.`tktnoc` AS `tktnoc`,`a`.`tktno` AS `tktno`,`b`.`working_hours` AS `working_hours`,`b`.`wdept_id` AS `wdept_id` from ((select `a`.`loom_date` AS `loom_date`,`a`.`shift` AS `shift`,`a`.`loom_id` AS `loom_id`,if(((`a`.`diffm` < 120) or (`a`.`diffm` is null)),120,`a`.`diffm`) AS `diffm`,`a`.`qcod` AS `qcod`,ifnull(`a`.`tot_hrs`,8) AS `tot_hrs`,if(((`a`.`tktno` like '%C%') or (`a`.`tktno` is null) or (`a`.`tktno` like '%O%')),999,`a`.`tktno`) AS `tktnoc`,`a`.`tktno` AS `tktno` from (select `cjb`.`loom_date` AS `loom_date`,'A1' AS `shift`,`cjb`.`loom_id` AS `loom_id`,`cjb`.`production_a1` AS `diffm`,`cjb`.`quality_code_a1` AS `qcod`,`cjb`.`working_hrs_a1` AS `tot_hrs`,`cjb`.`ticket_no_a1` AS `tktno` from `vowsls`.`cuts_jugar_buff_1` `cjb` where (`cjb`.`company_id` = 1) union select `cjb`.`loom_date` AS `loom_date`,'A2' AS `shift`,`cjb`.`loom_id` AS `loom_id`,`cjb`.`production_a2` AS `diffm`,`cjb`.`quality_code_a2` AS `qcod`,`cjb`.`working_hrs_a2` AS `tot_hrs`,`cjb`.`ticket_no_a2` AS `tktno` from `vowsls`.`cuts_jugar_buff_1` `cjb` where (`cjb`.`company_id` = 1) union select `cjb`.`loom_date` AS `loom_date`,'B1' AS `shift`,`cjb`.`loom_id` AS `loom_id`,`cjb`.`production_b1` AS `diffm`,`cjb`.`quality_code_b1` AS `qcod`,`cjb`.`working_hrs_b1` AS `tot_hrs`,`cjb`.`ticket_no_b1` AS `tktno` from `vowsls`.`cuts_jugar_buff_1` `cjb` where (`cjb`.`company_id` = 1) union select `cjb`.`loom_date` AS `loom_date`,'B2' AS `shift`,`cjb`.`loom_id` AS `loom_id`,`cjb`.`production_b2` AS `diffm`,`cjb`.`quality_code_b2` AS `qcod`,`cjb`.`working_hrs_b2` AS `tot_hrs`,`cjb`.`ticket_no_b2` AS `tktno` from `vowsls`.`cuts_jugar_buff_1` `cjb` where (`cjb`.`company_id` = 1) union select `cjb`.`loom_date` AS `loom_date`,'C' AS `shift`,`cjb`.`loom_id` AS `loom_id`,`cjb`.`production_c` AS `diffm`,`cjb`.`quality_code_c` AS `qcod`,`cjb`.`working_hrs_c` AS `tot_hrs`,`cjb`.`ticket_no_c` AS `tktno` from `vowsls`.`cuts_jugar_buff_1` `cjb` where (`cjb`.`company_id` = 1)) `a` where ((`a`.`tktno` is not null) or (`a`.`diffm` is not null))) `a` left join (select `da`.`eb_no` AS `eb_no`,`da`.`attendance_date` AS `attendance_date`,`da`.`spell` AS `spell`,`da`.`working_hours` AS `working_hours`,`da`.`worked_department_id` AS `wdept_id` from `vowsls`.`daily_attendance` `da` where ((`da`.`is_active` = 1) and (`da`.`worked_designation_id` = 1113) and (`da`.`attendance_type` <> 'C'))) `b` on(((`a`.`loom_date` = `b`.`attendance_date`) and (`a`.`shift` = `b`.`spell`) and (`b`.`eb_no` = `a`.`tktno`))))) `g` left join `vowsls`.`department_master` `dm` on((`g`.`wdept_id` = `dm`.`dept_id`))) where (`g`.`working_hours` is not null)) `l` left join `vowsls`.`weaving_quality_master` `wqm` on(((`l`.`qcod` = `wqm`.`quality_code`) and (`l`.`company_id` = `wqm`.`company_id`)))) left join `EMPMILL12`.`weaving_daily_transaction` `wdt` on(((`l`.`qcod` = `wdt`.`q_code`) and (`l`.`loom_date` = `wdt`.`tran_date`) and (`l`.`company_id` = `wdt`.`company_id`)))) left join `EMPMILL12`.`tbl_loom_master_other_details` `tlmod` on((`l`.`loom_id` = `tlmod`.`mechine_id`))) left join `vowsls`.`mechine_master` `mm` on((`mm`.`mechine_id` = `l`.`loom_id`))) left join `vowsls`.`mechine_master` `mm1` on((`mm1`.`mechine_id` = `tlmod`.`sardar_line_id`))) left join `vowsls`.`mechine_master` `mm2` on((`mm2`.`mechine_id` = `tlmod`.`helper_line_id`))) left join `vowsls`.`worker_master` `wm` on(((`l`.`tktno` = `wm`.`eb_no`) and (`l`.`company_id` = `wm`.`company_id`))));;
CREATE OR REPLACE VIEW EMPMILL12.view_proc_spellwindingdata AS select `prd`.`company_id` AS `company_id`,`prd`.`tran_date` AS `tran_date`,`prd`.`spell` AS `spell`,`prd`.`shift` AS `shift`,`prd`.`wnd_mc_id` AS `wnd_mc_id`,`prd`.`prod` AS `prod`,`wdse`.`quality_id` AS `quality_id`,`wqm`.`WND_Q_CODE` AS `wnd_q_code`,`wqm`.`QUALITY` AS `quality`,`wqm`.`OCCU_CODE` AS `wqoc_code`,`wqm`.`TARGET_PROD` AS `target_prod`,`wgm2`.`WND_GROUP_NAME` AS `wnd_group_name`,`wdse`.`no_of_spindle` AS `no_of_spindle`,`mm`.`mechine_name` AS `mechine_name`,`mm`.`mech_code` AS `mech_code`,`tewh`.`mcwhrs` AS `mcwhrs`,`attn`.`eb_no` AS `eb_no`,`attn`.`eb_id` AS `eb_id`,`attn`.`atthrs` AS `atthrs`,`wgm2`.`SPG_GROUP` AS `spg_group`,`wgm2`.`WND_WARP_WEFT` AS `wnd_warp_weft`,`wqm`.`QUALITY` AS `quality_name`,`wqm`.`Q_COUNT` AS `qcount`,`wdse`.`branch_id` AS `branch_id` from ((((((((select `prd`.`company_id` AS `company_id`,`prd`.`tran_date` AS `tran_date`,`prd`.`spell` AS `spell`,`prd`.`shift` AS `shift`,`prd`.`wnd_mc_id` AS `wnd_mc_id`,sum(`prd`.`prod`) AS `prod` from (select `wsepq`.`company_id` AS `company_id`,`wsepq`.`rec_date` AS `tran_date`,`wsepq`.`spell` AS `spell`,substr(`wsepq`.`spell`,1,1) AS `shift`,`wsepq`.`wnd_mc_id` AS `wnd_mc_id`,`wsepq`.`production` AS `prod`,'1' AS `prdrem` from `EMPMILL12`.`WINDING_SPELL_EB_PROD_QLTY` `wsepq` where (`wsepq`.`is_active` = 1) union all select `wje`.`company_id` AS `company_id`,`wje`.`TRAN_DATE` AS `tran_date`,`wje`.`SPELL` AS `spell`,substr(`wje`.`SPELL`,1,1) AS `shift`,`wje`.`WND_MC_ID` AS `WND_MC_ID`,(0 - `wje`.`WEIGHT`) AS `prod`,'2' AS `prdrem` from `EMPMILL12`.`WINDING_JUGAR_ENTRY` `wje` where ((`wje`.`is_active` = 1) and (`wje`.`OPEN_CLOSE` = 'O')) union all select `wje1`.`company_id` AS `company_id`,`wje1`.`TRAN_DATE` AS `tran_date`,`wje1`.`SPELL` AS `spell`,substr(`wje1`.`SPELL`,1,1) AS `shift`,`wje1`.`WND_MC_ID` AS `WND_MC_ID`,`wje1`.`WEIGHT` AS `prod`,'3' AS `prdrem` from `EMPMILL12`.`WINDING_JUGAR_ENTRY` `wje1` where ((`wje1`.`is_active` = 1) and (`wje1`.`OPEN_CLOSE` = 'C'))) `prd` group by `prd`.`company_id`,`prd`.`tran_date`,`prd`.`spell`,`prd`.`shift`,`prd`.`wnd_mc_id`) `prd` left join (select `wdse`.`tran_date` AS `tran_date`,`wdse`.`spell` AS `spell`,`wdse`.`quality_id` AS `quality_id`,`wdse`.`wnd_mc_id` AS `wnd_mc_id`,`wdse`.`no_of_spindle` AS `no_of_spindle`,`wdse`.`company_id` AS `company_id`,`wdse`.`branch_id` AS `branch_id`,`wdse`.`is_active` AS `is_active` from `EMPMILL12`.`WINDING_DAILY_SPELL_EB` `wdse` where (`wdse`.`is_active` = 1)) `wdse` on(((`prd`.`company_id` = `wdse`.`company_id`) and (`wdse`.`tran_date` = `prd`.`tran_date`) and (`wdse`.`spell` = `prd`.`spell`) and (`wdse`.`wnd_mc_id` = `prd`.`wnd_mc_id`)))) left join `EMPMILL12`.`WINDING_QUALITY_MASTER` `wqm` on((`wdse`.`quality_id` = `wqm`.`wnd_quality_id`))) left join `EMPMILL12`.`WINDING_GROUP_MASTER` `wgm` on((`wqm`.`WND_GR_CODE` = `wgm`.`WND_GR_CODE`))) left join `EMPMILL12`.`WINDING_GROUP_MASTER` `wgm2` on((`wgm`.`WND_MAIN_GR_CODE` = `wgm2`.`WND_GR_CODE`))) left join `vowsls`.`mechine_master` `mm` on((`prd`.`wnd_mc_id` = `mm`.`mechine_id`))) left join (select `tewh`.`company_id` AS `company_id`,`tewh`.`atten_date` AS `atten_date`,`tewh`.`att_spell` AS `att_spell`,`tewh`.`att_mc_id` AS `att_mc_id`,((`tewh`.`att_working_hours` - `tewh`.`att_idle_hours`) - ifnull(`tewh`.`att_mc_stoppage_hours`,0)) AS `mcwhrs`,`tewh`.`att_desig_id` AS `att_desig_id`,`tewh`.`eb_no` AS `eb_no`,`tewh`.`eb_id` AS `eb_id` from `EMPMILL12`.`tbl_emp_working_hours` `tewh` where (`tewh`.`att_desig_id` in (199,200,230,330,510,201,202,1124,1127))) `tewh` on(((`prd`.`tran_date` = `tewh`.`atten_date`) and (`prd`.`spell` = `tewh`.`att_spell`) and (`prd`.`wnd_mc_id` = `tewh`.`att_mc_id`) and (`prd`.`company_id` = `tewh`.`company_id`)))) left join (select `da`.`company_id` AS `company_id`,`da`.`eb_id` AS `eb_id`,`da`.`eb_no` AS `eb_no`,`da`.`attendance_date` AS `attendance_date`,`da`.`spell` AS `spell`,`da`.`worked_designation_id` AS `worked_designation_id`,(`da`.`working_hours` - `da`.`idle_hours`) AS `atthrs` from `vowsls`.`daily_attendance` `da` where ((`da`.`is_active` = 1) and (`da`.`worked_designation_id` in (199,200,230,330,510,201,202,1124,1127)))) `attn` on(((`prd`.`tran_date` = `attn`.`attendance_date`) and (`prd`.`spell` = `attn`.`spell`) and (`tewh`.`eb_id` = `attn`.`eb_id`) and (`prd`.`company_id` = `attn`.`company_id`))));;
CREATE OR REPLACE VIEW EMPMILL12.view_winding_all_data AS select `g`.`company_id` AS `company_id`,`g`.`rec_date` AS `rec_date`,`g`.`shift` AS `shift`,`g`.`eb_no` AS `eb_no`,`g`.`WND_Q_CODE` AS `wnd_q_code`,`g`.`mech_code` AS `mech_code`,`g`.`no_of_spindle` AS `no_of_spindle`,`g`.`UOM` AS `UOM`,sum(((`g`.`prod` - `g`.`opwt`) + `g`.`clwt`)) AS `production`,sum(((`g`.`prod` - `g`.`opwt`) + `g`.`clwt`)) AS `prodkgs`,sum(`g`.`prod`) AS `mprod`,sum(`g`.`opwt`) AS `mopwt`,sum(`g`.`clwt`) AS `mclwt`,sum((ifnull(`g`.`working_hours`,0) - ifnull(`g`.`idle_hours`,0))) AS `whrs`,0 AS `idlehrs`,sum(`g`.`targetprd`) AS `targetprd`,`g`.`QUALITY` AS `QUALITY`,`g`.`WND_GR_CODE` AS `WND_GR_CODE` from (select `h`.`company_id` AS `company_id`,`h`.`rec_date` AS `rec_date`,`h`.`spell` AS `spell`,`h`.`shift` AS `shift`,`h`.`WND_MC_ID` AS `WND_MC_ID`,`h`.`quality_id` AS `quality_id`,`h`.`prod` AS `prod`,ifnull(`h`.`opwt`,0) AS `opwt`,ifnull(`h`.`clwt`,0) AS `clwt`,`h`.`eb_no` AS `eb_no`,`h`.`mech_code` AS `mech_code`,`h`.`working_hours` AS `working_hours`,`h`.`idle_hours` AS `idle_hours`,`h`.`no_of_spindle` AS `no_of_spindle`,`h`.`WND_Q_CODE` AS `WND_Q_CODE`,`h`.`targetprd` AS `targetprd`,`h`.`UOM` AS `UOM`,`h`.`QUALITY` AS `QUALITY`,`h`.`WND_GR_CODE` AS `WND_GR_CODE` from (select `wsepq`.`company_id` AS `company_id`,`wsepq`.`rec_date` AS `rec_date`,`wsepq`.`spell` AS `spell`,substr(`wsepq`.`spell`,1,1) AS `shift`,`wsepq`.`WND_MC_ID` AS `WND_MC_ID`,`wsepq`.`prod` AS `prod`,`wje`.`tran_date` AS `tran_date`,ifnull(`wje`.`opwt`,0) AS `opwt`,ifnull(`wje`.`clwt`,0) AS `clwt`,`dea`.`eb_no` AS `eb_no`,`da`.`working_hours` AS `working_hours`,`da`.`idle_hours` AS `idle_hours`,`wdse`.`no_of_spindle` AS `no_of_spindle`,`wdse`.`quality_id` AS `quality_id`,`mm`.`mech_code` AS `mech_code`,`wqm`.`WND_Q_CODE` AS `WND_Q_CODE`,round(((`wqm`.`TARGET_PROD` / 8) * (`da`.`working_hours` - `da`.`idle_hours`)),2) AS `targetprd`,`wqm`.`UOM` AS `UOM`,`wqm`.`QUALITY` AS `QUALITY`,`wqm`.`WND_GR_CODE` AS `WND_GR_CODE` from (((((((select `wsepq`.`company_id` AS `company_id`,`wsepq`.`rec_date` AS `rec_date`,`wsepq`.`spell` AS `spell`,`wsepq`.`wnd_mc_id` AS `WND_MC_ID`,sum(`wsepq`.`production`) AS `prod` from `EMPMILL12`.`WINDING_SPELL_EB_PROD_QLTY` `wsepq` where (`wsepq`.`is_active` = 1) group by `wsepq`.`company_id`,`wsepq`.`rec_date`,`wsepq`.`spell`,`wsepq`.`wnd_mc_id`) `wsepq` left join (select `wje`.`TRAN_DATE` AS `tran_date`,`wje`.`SPELL` AS `spell`,`wje`.`WND_MC_ID` AS `WND_MC_ID`,max((case when (`wje`.`OPEN_CLOSE` = 'O') then `wje`.`WEIGHT` else 0 end)) AS `opwt`,max((case when (`wje`.`OPEN_CLOSE` = 'C') then `wje`.`WEIGHT` else 0 end)) AS `clwt` from `EMPMILL12`.`WINDING_JUGAR_ENTRY` `wje` where (`wje`.`is_active` = 1) group by `wje`.`TRAN_DATE`,`wje`.`SPELL`,`wje`.`WND_MC_ID`) `wje` on(((`wje`.`tran_date` = `wsepq`.`rec_date`) and (`wje`.`spell` = `wsepq`.`spell`) and (`wje`.`WND_MC_ID` = `wsepq`.`WND_MC_ID`)))) left join (select `dea`.`daily_atten_id` AS `daily_atten_id`,`dea`.`attendace_date` AS `attendace_date`,`dea`.`company_id` AS `company_id`,`dea`.`eb_id` AS `eb_id`,`dea`.`eb_no` AS `eb_no`,`dea`.`is_active` AS `is_active`,`dea`.`mc_id` AS `mc_id`,`dea`.`spell` AS `spell`,`dea`.`designation_id` AS `designation_id`,`dea`.`or_mc_id` AS `or_mc_id`,`dea`.`mc_stoppage_hours` AS `mc_stoppage_hours` from `vowsls`.`daily_ebmc_attendance` `dea` where (`dea`.`is_active` = 1)) `dea` on(((`wsepq`.`rec_date` = `dea`.`attendace_date`) and (`wsepq`.`spell` = `dea`.`spell`) and (`wsepq`.`WND_MC_ID` = `dea`.`mc_id`) and (`wsepq`.`company_id` = `dea`.`company_id`)))) left join (select `da`.`daily_atten_id` AS `daily_atten_id`,`da`.`attendance_date` AS `attendance_date`,`da`.`company_id` AS `company_id`,`da`.`eb_id` AS `eb_id`,`da`.`eb_no` AS `eb_no`,`da`.`idle_hours` AS `idle_hours`,`da`.`is_active` AS `is_active`,`da`.`remarks` AS `remarks`,`da`.`spell` AS `spell`,`da`.`spell_hours` AS `spell_hours`,`da`.`status_id` AS `status_id`,`da`.`worked_department_id` AS `worked_department_id`,`da`.`worked_designation_id` AS `worked_designation_id`,`da`.`working_hours` AS `working_hours` from `vowsls`.`daily_attendance` `da` where (`da`.`is_active` = 1)) `da` on((`da`.`daily_atten_id` = `dea`.`daily_atten_id`))) left join (select `wdse`.`auto_id` AS `auto_id`,`wdse`.`tran_date` AS `tran_date`,`wdse`.`spell` AS `spell`,`wdse`.`update_by` AS `update_by`,`wdse`.`update_date_time` AS `update_date_time`,`wdse`.`created_by` AS `created_by`,`wdse`.`created_date_time` AS `created_date_time`,`wdse`.`update_ip` AS `update_ip`,`wdse`.`quality_id` AS `quality_id`,`wdse`.`wnd_mc_id` AS `wnd_mc_id`,`wdse`.`no_of_spindle` AS `no_of_spindle`,`wdse`.`company_id` AS `company_id`,`wdse`.`branch_id` AS `branch_id`,`wdse`.`is_active` AS `is_active` from `EMPMILL12`.`WINDING_DAILY_SPELL_EB` `wdse` where (`wdse`.`is_active` = 1)) `wdse` on(((`wdse`.`tran_date` = `wsepq`.`rec_date`) and (`wdse`.`spell` = `wsepq`.`spell`) and (`wdse`.`wnd_mc_id` = `wsepq`.`WND_MC_ID`) and (`wsepq`.`company_id` = `wdse`.`company_id`)))) left join `vowsls`.`mechine_master` `mm` on((`wsepq`.`WND_MC_ID` = `mm`.`mechine_id`))) left join `EMPMILL12`.`WINDING_QUALITY_MASTER` `wqm` on((`wdse`.`quality_id` = `wqm`.`wnd_quality_id`)))) `h`) `g` where (`g`.`prod` > 0) group by `g`.`company_id`,`g`.`rec_date`,`g`.`shift`,`g`.`eb_no`,`g`.`WND_Q_CODE`,`g`.`mech_code`,`g`.`no_of_spindle`,`g`.`UOM`,`g`.`QUALITY`,`g`.`WND_GR_CODE`;;
CREATE OR REPLACE VIEW EMPMILL12.view_winding_qualitywise_data AS select `g`.`rec_date` AS `rec_date`,`g`.`company_id` AS `company_id`,`g`.`wnd_q_code` AS `wnd_q_code`,`g`.`empla` AS `empla`,`g`.`emplb` AS `emplb`,`g`.`emplc` AS `emplc`,`g`.`topdka` AS `topdka`,`g`.`topdkb` AS `topdkb`,`g`.`topdkc` AS `topdkc`,`g`.`totrga` AS `totrga`,`g`.`totrgb` AS `totrgb`,`g`.`totrgc` AS `totrgc`,`g`.`QUALITY` AS `QUALITY` from (select `a`.`rec_date` AS `rec_date`,`a`.`company_id` AS `company_id`,`a`.`wnd_q_code` AS `wnd_q_code`,`a`.`empla` AS `empla`,`a`.`emplb` AS `emplb`,`a`.`emplc` AS `emplc`,`a`.`topdka` AS `topdka`,`a`.`topdkb` AS `topdkb`,`a`.`topdkc` AS `topdkc`,`a`.`totrga` AS `totrga`,`a`.`totrgb` AS `totrgb`,`a`.`totrgc` AS `totrgc`,`b`.`QUALITY` AS `QUALITY` from ((select `g`.`rec_date` AS `rec_date`,`g`.`company_id` AS `company_id`,`g`.`wnd_q_code` AS `wnd_q_code`,(case when ((`g`.`company_id` = 2) and (`g`.`rec_date` >= '2024-06-13') and (`g`.`rec_date` <= '2024-06-30')) then round((sum(`g`.`hrsa`) / 6),1) else round((sum(`g`.`hrsa`) / 8),1) end) AS `empla`,(case when ((`g`.`company_id` = 2) and (`g`.`rec_date` >= '2024-06-13') and (`g`.`rec_date` <= '2024-06-30')) then round((sum(`g`.`hrsb`) / 6),1) else round((sum(`g`.`hrsb`) / 8),1) end) AS `emplb`,(case when ((`g`.`company_id` = 2) and (`g`.`rec_date` <= '2024-06-13')) then round((sum(`g`.`hrsc`) / 7.5),1) else round((sum(`g`.`hrsc`) / 7.5),1) end) AS `emplc`,round(sum(`g`.`proda`),0) AS `topdka`,round(sum(`g`.`prodb`),0) AS `topdkb`,round(sum(`g`.`prodc`),0) AS `topdkc`,round(sum(`g`.`tara`),0) AS `totrga`,round(sum(`g`.`tarb`),0) AS `totrgb`,round(sum(`g`.`tarc`),0) AS `totrgc` from (select `EMPMILL12`.`vwad`.`rec_date` AS `rec_date`,`EMPMILL12`.`vwad`.`company_id` AS `company_id`,`EMPMILL12`.`vwad`.`wnd_q_code` AS `wnd_q_code`,`EMPMILL12`.`vwad`.`whrs` AS `hrsa`,0 AS `hrsb`,0 AS `hrsc`,`EMPMILL12`.`vwad`.`prodkgs` AS `proda`,0 AS `prodb`,0 AS `prodc`,`EMPMILL12`.`vwad`.`targetprd` AS `tara`,0 AS `tarb`,0 AS `tarc`,0 AS `tdprod`,0 AS `tdemp` from `EMPMILL12`.`view_winding_all_data` `vwad` where (`EMPMILL12`.`vwad`.`shift` = 'A') union all select `EMPMILL12`.`vwad`.`rec_date` AS `rec_date`,`EMPMILL12`.`vwad`.`company_id` AS `company_id`,`EMPMILL12`.`vwad`.`wnd_q_code` AS `wnd_q_code`,0 AS `hrsa`,`EMPMILL12`.`vwad`.`whrs` AS `hrsb`,0 AS `hrsc`,0 AS `proda`,`EMPMILL12`.`vwad`.`prodkgs` AS `prodb`,0 AS `prodc`,0 AS `tara`,`EMPMILL12`.`vwad`.`targetprd` AS `tarb`,0 AS `tarc`,0 AS `tdprod`,0 AS `tdemp` from `EMPMILL12`.`view_winding_all_data` `vwad` where (`EMPMILL12`.`vwad`.`shift` = 'B') union all select `EMPMILL12`.`vwad`.`rec_date` AS `rec_date`,`EMPMILL12`.`vwad`.`company_id` AS `company_id`,`EMPMILL12`.`vwad`.`wnd_q_code` AS `wnd_q_code`,0 AS `hrsa`,0 AS `hrsb`,`EMPMILL12`.`vwad`.`whrs` AS `hrsc`,0 AS `proda`,0 AS `prodb`,`EMPMILL12`.`vwad`.`prodkgs` AS `prodc`,0 AS `tara`,0 AS `tarb`,`EMPMILL12`.`vwad`.`targetprd` AS `tarc`,0 AS `tdprod`,0 AS `tdemp` from `EMPMILL12`.`view_winding_all_data` `vwad` where (`EMPMILL12`.`vwad`.`shift` = 'C')) `g` group by `g`.`rec_date`,`g`.`company_id`,`g`.`wnd_q_code`) `a` join `EMPMILL12`.`WINDING_QUALITY_MASTER` `b` on((`a`.`wnd_q_code` = `b`.`WND_Q_CODE`)))) `g`;;
