besure_server/sql/besure_new/初始化视图.sql

-- besure.mes_view_report_plan_detail source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `besure`.`mes_view_report_plan_detail` AS
select
    `wp`.`id` AS `id`,
    `wp`.`report_id` AS `report_id`,
    `wp`.`plan_id` AS `plan_id`,
    `wp`.`task_detail_id` AS `task_detail_id`,
    `wp`.`product_id` AS `product_id`,
    `wp`.`quality_number_plan` AS `quality_number_plan`,
    `wp`.`waste_number_plan` AS `waste_number_plan`,
    `wp`.`report_time_plan` AS `report_time_plan`,
    `wp`.`total_time_plan` AS `total_time_plan`,
    `wp`.`creator` AS `creator`,
    `wp`.`create_time` AS `create_time`,
    `wp`.`updater` AS `updater`,
    `wp`.`update_time` AS `update_time`,
    `wp`.`deleted` AS `deleted`,
    `wp`.`tenant_id` AS `tenant_id`,
    `pd`.`user_id` AS `user_id`,
    `pd`.`report_type` AS `report_type`,
    `pd`.`group_type` AS `group_type`,
    `pd`.`org_type` AS `org_type`,
    `pd`.`org_id` AS `org_id`,
    `pd`.`quality_number` AS `quality_number`,
    `pd`.`waste_number` AS `waste_number`,
    `pd`.`report_date` AS `report_date`,
    `pd`.`report_status` AS `report_status`,
    `pd`.`creator` AS `report_creator`
from
    (`besure`.`mes_work_report_plan` `wp`
join `besure`.`mes_produce_report_detail` `pd` on
    (((`wp`.`report_id` = `pd`.`id`) and (`wp`.`deleted` = 0) and (`pd`.`deleted` = 0))));


-- besure.mes_view_report_plan_summary source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `besure`.`mes_view_report_plan_summary` AS
select
    `besure`.`mes_view_report_plan_detail`.`plan_id` AS `plan_id`,
    `besure`.`mes_view_report_plan_detail`.`product_id` AS `product_id`,
    `besure`.`mes_view_report_plan_detail`.`org_type` AS `org_type`,
    sum(`besure`.`mes_view_report_plan_detail`.`quality_number_plan`) AS `total_quality_number`,
    sum(`besure`.`mes_view_report_plan_detail`.`waste_number_plan`) AS `total_waste_number`,
    sum(`besure`.`mes_view_report_plan_detail`.`report_time_plan`) AS `report_time_summary`,
    sum(`besure`.`mes_view_report_plan_detail`.`total_time_plan`) AS `total_time_summary`
from
    `besure`.`mes_view_report_plan_detail`
group by
    `besure`.`mes_view_report_plan_detail`.`plan_id`,
    `besure`.`mes_view_report_plan_detail`.`product_id`,
    `besure`.`mes_view_report_plan_detail`.`org_type`;


-- besure.mes_view_report_user_date_summary source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `besure`.`mes_view_report_user_date_summary` AS
select
    `besure`.`mes_produce_report_detail`.`user_id` AS `user_id`,
    cast(`besure`.`mes_produce_report_detail`.`report_date` as date) AS `report_day`,
    sum(`besure`.`mes_produce_report_detail`.`total_number`) AS `sum_number`,
    sum(`besure`.`mes_produce_report_detail`.`quality_number`) AS `total_quality_number`,
    sum(`besure`.`mes_produce_report_detail`.`waste_number`) AS `total_waste_number`
from
    `besure`.`mes_produce_report_detail`
where
    (`besure`.`mes_produce_report_detail`.`deleted` = 0)
group by
    `besure`.`mes_produce_report_detail`.`user_id`,
    cast(`besure`.`mes_produce_report_detail`.`report_date` as date);


-- besure.mes_view_task_plan_temp source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `besure`.`mes_view_task_plan_temp` AS
select
    `t`.`id` AS `task_id`,
    `t`.`code` AS `task_code`,
    `p`.`id` AS `product_id`,
    `p`.`name` AS `product_name`,
    `p`.`bar_code` AS `bar_code`,
    sum(`td`.`number`) AS `total_number`,
    sum(`td`.`package_number`) AS `plan_number`,
    group_concat(`td`.`id` order by `td`.`id` ASC separator ',') AS `task_detail_ids`
from
    ((`besure`.`mes_task` `t`
join `besure`.`mes_task_detail` `td` on
    (((`t`.`id` = `td`.`task_id`) and ((`td`.`deleted` is null) or (`td`.`deleted` = 0)))))
join `besure`.`erp_product` `p` on
    ((`td`.`product_id` = `p`.`id`)))
group by
    `t`.`id`,
    `p`.`id`;


-- besure.mes_view_task_product_summary source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `besure`.`mes_view_task_product_summary` AS
select
    `besure`.`v1`.`task_id` AS `task_id`,
    `besure`.`v1`.`task_code` AS `task_code`,
    `besure`.`v1`.`product_id` AS `product_id`,
    `besure`.`v1`.`product_name` AS `product_name`,
    `besure`.`v1`.`bar_code` AS `bar_code`,
    `besure`.`v1`.`total_number` AS `total_number`,
    sum(`mp`.`plan_number`) AS `plan_number`,
    `besure`.`v1`.`task_detail_ids` AS `task_detail_ids`
from
    (`besure`.`mes_view_task_plan_temp` `v1`
left join `besure`.`mes_plan` `mp` on
    (((`besure`.`v1`.`task_id` = `mp`.`task_id`) and (`besure`.`v1`.`product_id` = `mp`.`product_id`))))
group by
    `besure`.`v1`.`task_id`,
    `besure`.`v1`.`product_id`;


-- besure.workstation_assignment_view source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `besure`.`workstation_assignment_view` AS
select
    `w`.`report_id` AS `report_id`,
    `w`.`deleted` AS `deleted`,
    `w`.`id` AS `assignment_id`,
    `o`.`name` AS `workstation_name`,
    `u`.`username` AS `user_name`,
    `w`.`work_date` AS `work_date`,
    (case
        when (`w`.`group_type` = 3) then '长白班'
        when (`w`.`group_type` = 2) then '夜班'
        when (`w`.`group_type` = 1) then '白班'
    end) AS `班别`,
    (case
        when (`w`.`org_type` = 'zhijiang') then '制浆'
        when (`w`.`org_type` = 'chengxing') then '成型'
        when (`w`.`org_type` = 'honggan') then '烘干'
        when (`w`.`org_type` = 'zhuanyi') then '转移'
        when (`w`.`org_type` = 'reya') then '热压'
        when (`w`.`org_type` = 'qiebian') then '切边'
        when (`w`.`org_type` = 'pinjian') then '品检'
        when (`w`.`org_type` = 'jiashi') then '加湿'
        when (`w`.`org_type` = 'dabao') then '打包'
        when (`w`.`org_type` = 'tiebiao') then '贴标'
        when (`w`.`org_type` = 'sufeng') then '塑封'
        when (`w`.`org_type` = 'pinyin') then '品印'
    end) AS `工序`
from
    ((`besure`.`mes_org_worker` `w`
join `besure`.`mes_organization` `o` on
    ((`w`.`org_id` = `o`.`id`)))
join `besure`.`system_users` `u` on
    ((`w`.`worker_id` = `u`.`id`)));