USE [GuangHong]
GO
/****** Object: StoredProcedure [dbo].[st_MES_SelInspctDetail] Script Date: 11/23/2015 11:45:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[st_MES_SelInspctDetail] (
@MachineNo varchar(50) = ''
,@BCCode varchar(20)=''
,@CreateDateBegin varchar(19) = ''
,@CreateDateEnd varchar(19)=''
,@in_pagesize smallint =20
,@in_page smallint = 1
)
/*************
exec [st_MES_SelInspctDetail] '216','BC','',''
creator: zhuss
create date: 2014.06.26
Remark: 查询设备点检记录
**************/
as
begin
Create table #tbInspectDetail
(
BillNO varchar(50),
MachineNo varchar(20),
ItemID int,
Result int,
InspectTime datetime
)
declare @InsertRow int
insert into #tbInspectDetail(BillNO,MachineNo,ItemID,Result,InspectTime)
select r.BillNO,r.MachineNo,s.ItemID,s.Result,r.InspectTime
from MES_InspectItemRec r
join MES_InspectItemRecSon s on r.BillNO=s.BillNO
join MES_InspectItem a on a.ItemID=s.ItemID
select @InsertRow=@@ROWCOUNT
select a.ItemID,a.ItemName from MES_InspectItem a
join (select distinct ItemID from MES_InspectItemRecSon) b on a.ItemID=b.ItemID
declare @sql varchar(8000),
@StrWhere varchar(1000),
@StrGroup varchar(200),
@StrDataPage varchar(4000),
@StrOrder varchar(100),
@StrTemp varchar(300)
if (@InsertRow>0)
begin
select @StrWhere=' where 1=1 ',
@StrGroup='group by m.MachineNo,m.InspectTime,c.Name',
@StrOrder=' order by a.Date Desc',
@StrTemp='if OBJECT_ID(''temp..#tbInspectResult'') is not null drop table #tbInspectResult
if OBJECT_ID(''temp..#tbInspectResult2'') is not null drop table #tbInspectResult2'
select @sql=isnull(@sql+',','')
+'max(case m.ItemID when '''+Convert(varchar(5),ItemID)+''' then case when m.Result=1 then ''OK'' when m.Result=2 then ''未开机'' else ''NG'' end else ''NG'' end) '''+Convert(varchar(5),ItemID)+''''
from #tbInspectDetail m group by m.ItemID
print @sql
set @sql=' select m.MachineNo MachineNo,m.InspectTime Date
,c.BCCode,'+@sql+' into #tbInspectResult from #tbInspectDetail m
left join MES_BCDetail c on
c.BCCode=[dbo].[Fn_GetBCCodeByTime](Convert(varchar(19),m.InspectTime,120))
group by m.MachineNo,m.InspectTime,c.BCCode
order by m.InspectTime Desc
select ID=identity(int), * into #tbInspectResult2 from #tbInspectResult a'
if(@MachineNo<>'') set @StrWhere=@StrWhere+' and a.MachineNo like ''%'+@MachineNo+'%'''
if(@BCCode<>'') set @StrWhere=@StrWhere+' and a.BCCode='''+@BCCode+''''
if(@CreateDateBegin<>'') set @StrWhere=@StrWhere+' and a.Date>='''+@CreateDateBegin+''''
if(@CreateDateEnd<>'') set @StrWhere=@StrWhere+' and a.Date<='''+@CreateDateEnd+''''
--分页查询数据
select @StrDataPage=' declare @row_from int, @row_to int,@out_total_rows int
select @out_total_rows= count(*) from #tbInspectResult2
select TotalCount=@out_total_rows
execute st_MES_RS_Pages @out_total_rows, '+convert(varchar(5),@in_pagesize)+','+CONVERT(varchar(5),@in_page)+','+
'@row_from output, @row_to output
select r.*,BCCodeName=c.Name from #tbInspectResult2 r
left join MES_BCDetail c on c.BCCode=r.BCCode
where r.ID between @row_from and @row_to order by ID'
set @sql=@StrTemp+@sql+@StrWhere+@StrDataPage
--select @sql
print @sql
exec (@sql)
end
else
begin
select TotalCount=0
select r.ID,r.MachineNo,r.InspectTime Date,'' 'BCCodeName' from MES_InspectItemRec r where 1=0
end
drop table #tbInspectDetail
end
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('temp..#tbInspectResult') is not null drop table #tbInspectResult
if OBJECT_ID('temp..#tbInspectResult2') is not null drop table #tbInspectResult2
select m.MachineNo MachineNo,m.InspectTime Date,c.BCCode,
max(case m.ItemID when '9' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '9',
max(case m.ItemID when '3' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '3',
max(case m.ItemID when '6' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '6',
max(case m.ItemID when '7' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '7',
max(case m.ItemID when '1' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '1',
max(case m.ItemID when '10' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '10',
max(case m.ItemID when '4' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '4',
max(case m.ItemID when '5' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '5',
max(case m.ItemID when '2' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '2',
max(case m.ItemID when '8' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '8'
into #tbInspectResult
from #tbInspectDetail m left join MES_BCDetail c on c.BCCode=[dbo].[Fn_GetBCCodeByTime](Convert(varchar(19),m.InspectTime,120))
group by m.MachineNo,m.InspectTime,c.BCCode
order by m.InspectTime Desc
select ID=identity(int), * into #tbInspectResult2 from #tbInspectResult a where 1=1
declare @row_from int, @row_to int,@out_total_rows int
select @out_total_rows= count(*) from #tbInspectResult2
select TotalCount=@out_total_rows
execute st_MES_RS_Pages @out_total_rows, 20,1,@row_from output, @row_to output
select r.*,BCCodeName=c.Name from #tbInspectResult2 r left join MES_BCDetail c on c.BCCode=r.BCCode
where r.ID between @row_from and @row_to order by ID
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
max(case m.ItemID when '9' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '9',
max(case m.ItemID when '3' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '3',
max(case m.ItemID when '6' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '6',
max(case m.ItemID when '7' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '7',
max(case m.ItemID when '1' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '1',
max(case m.ItemID when '10' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '10',
max(case m.ItemID when '4' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '4',
max(case m.ItemID when '5' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '5',
max(case m.ItemID when '2' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '2',
max(case m.ItemID when '8' then case when m.Result=1 then 'OK' when m.Result=2 then '未开机' else 'NG' end else 'NG' end) '8'
if OBJECT_ID('temp..#tbInspectResult') is not null drop table #tbInspectResult