if (txtUser='') and (startTime='') and (endTime='') then
if (dataType='open') then
insert into tmp_table(AlarmId,BizData,startStaus,endStatus,startDate,endDate,name)
select b.AlarmId,a.BizData,b.HandleStatus,b.HandleStatus,min(b.CreateTime),min(b.CreateTime),b.Handler as name
from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId
where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and HandleStatus='1'
and b.CreateTime>curdate()
group by b.AlarmId,b.HandleStatus;
Create table Datas (
select b.AlarmId,b.Handler as name,b.HandleStatus,min(b.CreateTime) as CreateTime
from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId
where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and b.HandleStatus in('1','2')
and b.CreateTime>curdate()
group by b.AlarmId,b.HandleStatus
);
update tmp_table set name=b.name,a.startStaus=b.HandleStatus,a.endStatus=b.HandleStatus,
startDate=min(b.CreateTime),endDate=max(b.CreateTime)
from tmp_table a join Datas b on a.AlarmId=b.AlarmId
group by b.AlarmId;
END IF;
if dataType='doing' then
--初始化tmp_table
insert into tmp_table(AlarmId,BizData,startStaus,endStatus,startDate,endDate,name)
select b.AlarmId,a.BizData,b.HandleStatus,b.HandleStatus,min(b.CreateTime),min(b.CreateTime),b.Handler as name
from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId
where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and HandleStatus='2'
and b.CreateTime>curdate()
group by b.AlarmId,b.HandleStatus
--更新表
Create table Datas (
select b.AlarmId,b.Handler as name,b.HandleStatus,min(b.CreateTime) as CreateTime
from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId
where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and b.HandleStatus in('2','0')
and b.CreateTime>curdate()
group by b.AlarmId,b.HandleStatus
);
update tmp_table set name=b.name,a.startStaus=b.HandleStatus,a.endStatus=b.HandleStatus,
startDate=min(b.CreateTime),endDate=max(b.CreateTime)
from tmp_table a join Datas b on a.AlarmId=b.AlarmId
group by b.AlarmId;
END IF;
if dataType='close' then
--初始化tmp_table
insert into tmp_table(AlarmId,BizData,startStaus,endStatus,startDate,endDate,name)
select b.AlarmId,a.BizData,b.HandleStatus,b.HandleStatus,min(b.CreateTime),min(b.CreateTime),b.Handler as name
from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId
where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and HandleStatus='0'
and b.CreateTime>curdate()
group by b.AlarmId,b.HandleStatus;
--更新表
Create table Datas (
select b.AlarmId,b.Handler as name,b.HandleStatus,min(b.CreateTime) as CreateTime
from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId
where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and b.HandleStatus in('0','1','2')
and b.CreateTime>curdate()
group by b.AlarmId,b.HandleStatus
);
update tmp_table set name=b.name,a.startStaus=b.HandleStatus,a.endStatus=b.HandleStatus,
startDate=min(b.CreateTime),endDate=max(b.CreateTime)
from tmp_table a join Datas b on a.AlarmId=b.AlarmId
group by b.AlarmId;
END IF;
end if;
if (dataType='open') then
insert into tmp_table(AlarmId,BizData,startStaus,endStatus,startDate,endDate,name)
select b.AlarmId,a.BizData,b.HandleStatus,b.HandleStatus,min(b.CreateTime),min(b.CreateTime),b.Handler as name
from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId
where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and HandleStatus='1'
and b.CreateTime>curdate()
group by b.AlarmId,b.HandleStatus;
Create table Datas (
select b.AlarmId,b.Handler as name,b.HandleStatus,min(b.CreateTime) as CreateTime
from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId
where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and b.HandleStatus in('1','2')
and b.CreateTime>curdate()
group by b.AlarmId,b.HandleStatus
);
update tmp_table set name=b.name,a.startStaus=b.HandleStatus,a.endStatus=b.HandleStatus,
startDate=min(b.CreateTime),endDate=max(b.CreateTime)
from tmp_table a join Datas b on a.AlarmId=b.AlarmId
group by b.AlarmId;
END IF;
if dataType='doing' then
--初始化tmp_table
insert into tmp_table(AlarmId,BizData,startStaus,endStatus,startDate,endDate,name)
select b.AlarmId,a.BizData,b.HandleStatus,b.HandleStatus,min(b.CreateTime),min(b.CreateTime),b.Handler as name
from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId
where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and HandleStatus='2'
and b.CreateTime>curdate()
group by b.AlarmId,b.HandleStatus
--更新表
Create table Datas (
select b.AlarmId,b.Handler as name,b.HandleStatus,min(b.CreateTime) as CreateTime
from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId
where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and b.HandleStatus in('2','0')
and b.CreateTime>curdate()
group by b.AlarmId,b.HandleStatus
);
update tmp_table set name=b.name,a.startStaus=b.HandleStatus,a.endStatus=b.HandleStatus,
startDate=min(b.CreateTime),endDate=max(b.CreateTime)
from tmp_table a join Datas b on a.AlarmId=b.AlarmId
group by b.AlarmId;
END IF;
if dataType='close' then
--初始化tmp_table
insert into tmp_table(AlarmId,BizData,startStaus,endStatus,startDate,endDate,name)
select b.AlarmId,a.BizData,b.HandleStatus,b.HandleStatus,min(b.CreateTime),min(b.CreateTime),b.Handler as name
from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId
where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and HandleStatus='0'
and b.CreateTime>curdate()
group by b.AlarmId,b.HandleStatus;
--更新表
Create table Datas (
select b.AlarmId,b.Handler as name,b.HandleStatus,min(b.CreateTime) as CreateTime
from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId
where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and b.HandleStatus in('0','1','2')
and b.CreateTime>curdate()
group by b.AlarmId,b.HandleStatus
);
update tmp_table set name=b.name,a.startStaus=b.HandleStatus,a.endStatus=b.HandleStatus,
startDate=min(b.CreateTime),endDate=max(b.CreateTime)
from tmp_table a join Datas b on a.AlarmId=b.AlarmId
group by b.AlarmId;
END IF;
end if;
