/*
1.设备信息查询函数
     code 设备编码,设备编码为空时返回所有设备信息数据
     cid 公司ID
     oid 组织ID
*/
 */
CREATE OR REPLACE FUNCTION "manufacture"."device_running_info"("code" varchar, "cid" int4, "oid" int4)
  RETURNS TABLE("t_device_name" text, "t_device_code" text, "t_status" text, "t_temperature" numeric, "t_temperature1" numeric, "t_device_type" text, "t_total" int4, "t_uptime" numeric) AS $BODY$
begin

	  return  query

		with product_device as (
		select device_code,device_name,device_id,device_type from manufacture.device
		where
				company_id = 1
				and org_id = 1
		and ext #>'{deviceExt,isProductDevice}'='1'
		)
		,device_status as(
		select c.*,a.device_name,a.device_type,a.device_code  device_code
		from (
				select cast(b.device_running_record_info->>'currentStatus' as INTEGER)status,
				cast(b.device_running_record_info->>'temp' as DECIMAL) as t,
				cast(b.device_running_record_info->>'count' as INTEGER) as total,
				cast(b.device_running_record_info->>'upTime' as INTEGER)  as uptime,
				cast(b.device_running_record_info->>'temp1'as DECIMAL) as  t1,
				extract (min from now()-b.updated_at) span,
				b.device_id
				from
				 manufacture.device_daily_running_record b
				where
				company_id = 1
				and org_id = 1
				and b.created_at> (to_date(to_char(now(), 'YYYY-MM-DD'),'YYYY-MM-DD') - interval '8 hours')
		 )c right join product_device a on c.device_id =  a.device_id
		)
		select cast(device_name as text) device_name,
				cast(device_code as text) device_code,
				cast(
					(case when (status=3 or status=1) and span<10 then '运行中'
					when (status=2   or status=0 )and span<10 then '待机中'
					ELSE  '故障中' end) as text
				) status,
		   cast(COALESCE(t,0) as DECIMAL) temperature,
		   cast(COALESCE(t1,0) as DECIMAL) temperature1,
		   cast(COALESCE(device_type,'') as TEXT)  device_type,
	     cast(COALESCE(total,0) as INTEGER) total,
	     cast(COALESCE(uptime,0) as DECIMAL) uptime
			from device_status
			where device_code = code;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE