CREATE OR REPLACE VIEW `company_list` AS select `company`.`uuid` AS `uuid`,`company`.`aindex` AS `aindex`,`company`.`name` AS `name`,`company`.`enabled` AS `enabled`,`company`.`migrated` AS `migrated`,`company`.`migration` AS `migration`,`company`.`migration_target` AS `migration_target`,`company`.`StartTime` AS `StartTime`,`company`.`UpdateTime` AS `UpdateTime`,`company`.`datasource` AS `datasource`,`company`.`street_post` AS `street_post`,`company`.`street_visit` AS `street_visit`,`company`.`zip_code` AS `zip_code`,`company`.`city` AS `city`,`company`.`notes` AS `notes`,`company`.`state` AS `state`,`company`.`email` AS `email`,`company`.`customer_value` AS `customer_value`,`company`.`area_uusimaa` AS `area_uusimaa`,`company`.`area_varsinais` AS `area_varsinais`,`company`.`area_kymenlaakso` AS `area_kymenlaakso`,`company`.`area_hame` AS `area_hame`,`company`.`area_satakunta` AS `area_satakunta`,`company`.`area_pohjanmaa` AS `area_pohjanmaa`,`company`.`area_pohjois` AS `area_pohjois`,`company`.`area_lappi` AS `area_lappi`,`company`.`devgroup_paine` AS `devgroup_paine`,`company`.`devgroup_kemikaali` AS `devgroup_kemikaali`,`company`.`devgroup_nestekaasu` AS `devgroup_nestekaasu`,`company`.`devgroup_maakaasu` AS `devgroup_maakaasu`,`company`.`devgroup_biokaasu` AS `devgroup_biokaasu`,`company`.`devgroup_vakadr` AS `devgroup_vakadr`,`company`.`devgroup_a2` AS `devgroup_a2`,`company`.`devgroup_g` AS `devgroup_g`,`company`.`devgroup_other` AS `devgroup_other`,`company`.`role_manufacturer` AS `role_manufacturer`,`company`.`role_energyplant` AS `role_energyplant`,`company`.`role_government` AS `role_government`,`company`.`role_processplant` AS `role_processplant`,`company`.`role_transport` AS `role_transport`,`company`.`inspection_institution` AS `inspection_institution`,(select min(`device`.`inspection_date_next`) from `device` where ((`device`.`inspection_date_next` >= cast(now() as date)) and (`device`.`company_owner` = `company`.`uuid`))) AS `inspection_date_next`,(select count(`device`.`uuid`) from `device` where ((`device`.`inspection_date_next` < cast(now() as date)) and (`device`.`company_owner` = `company`.`uuid`))) AS `inspection_overdue_count`,(select count(`device`.`uuid`) from `device` where ((`device`.`inspection_date_next` >= cast(now() as date)) and (`device`.`company_owner` = `company`.`uuid`))) AS `inspection_upcoming_count`,(select count(`device`.`uuid`) from `device` where ((`device`.`inspection_date_next` >= cast(now() as date)) and (`device`.`inspection_date_next` <= (now() + interval 3 month)) and (`device`.`company_owner` = `company`.`uuid`))) AS `inspection_soon_count`,(select count(`device`.`uuid`) from `device` where (`device`.`company_owner` = `company`.`uuid`)) AS `device_count` from `company`;