不良项：
  select distinct 
  QCE03 
  from [BMMES_JM].[dbo].[qce_file] as zb
left join [BMMES_JM].[dbo].[M_JOB_BadItemsForProcess]  as fb on zb.QCE01 = fb.BadCode  where ProcessCode in (select ECD01 from ecd_file  where IndexSort between 1 and 15 and TA_ECDUD01 = '1' and TA_ECDUD02 = '1') order by QCE03

工厂：
select GEM01,GEM02,GEMORIG from gem_file where GEM08 = 'Y' AND IsMaterialWarehouse = '1'

工序：
select ECD01,ECD02 from ecd_file  where IndexSort between 1 and 15 and TA_ECDUD01 = '1' and TA_ECDUD02 = '1' order by IndexSort asc


线体查型号：
SELECT xt.productLineId,xt.productLine,gd.SFBUD10,gd.Pname,gd.SFB05 FROM S_SY_ProductLine as xt
left join sfb_file as gd on xt.productLineId = gd.SFBUD10
where  SFB82 != 'ZY0.011' and gd.SFB02 = '1'   and SFBGRUP = (select GEMORIG from gem_file where GEM01 = '${gc}') 


ds1:
USE [BMMES_JM]
GO
/****** Object:  StoredProcedure [dbo].[sp_count]    Script Date: 01/10/2019 17:47:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<ming>
-- Create date: <2019-01-07>
-- Description:	<计算投料，合格数>
-- =============================================
ALTER proc [dbo].[sp_count]
@produceline varchar(10),
@workproc varchar(20),
@finishid varchar(30),
@auitdate datetime




as
select sum(RpAcceptCount) as 合格数,SUM(RpTotalNums) as 投料数  from [BMMES_JM].[dbo].[M_JOB_REPORTWORK] a 
left join [BMMES_JM].[dbo].[M_JOB_REPORTWORK_01] d 
	  on d.RpLPID = a.RpID where 
RpProduceLine = @produceline
AND RpWorkproc = @workproc 
AND RpFinishID = @finishid 
AND RpAuditStatus = '1' 
and RpAuditDate between convert(varchar(10),DATEADD(dd,0,@auitdate),120)+' 07:00' 
and convert(varchar(10),DATEADD(dd,1,@auitdate),120)+' 07:00'




ds2:
USE [BMMES_JM]
GO
/****** Object:  StoredProcedure [dbo].[usp_unaccept]    Script Date: 01/10/2019 17:46:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  proc [dbo].[usp_unaccept]
@produceline varchar(10),
@workproc varchar(20),
@finishid varchar(30),
@auitdate datetime,
@abtype varchar(10)

as

--declare @startdate varchar(20)
--declare @enddate varchar(20)
begin
	--set  @startdate=convert(varchar(10),DATEADD(dd,0,@auitdate),120)+' 07:00';
	--set  @enddate=convert(varchar(10),DATEADD(dd,1,@auitdate),120)+' 07:00';
	
	
	--if (@abtype= 'A')
        begin
			select * into #temp from (
select  case when RpUnAcceptType is null then (case when RpRejectType is null then RpDownlineType else RpRejectType end ) else RpUnAcceptType end as blx,(case  when sum((case  when 不良的不良项数 is null then 0 else 不良的不良项数 end)
			+(case  when 报废的不良项数  is null then 0 else 报废的不良项数  end)
			+(case  when 报损的不良项数 is null then 0 else 报损的不良项数 end))  is null then 0 else sum((case  when 不良的不良项数 is null then 0 else 不良的不良项数 end)
			+(case  when 报废的不良项数  is null then 0 else 报废的不良项数  end)
			+(case  when 报损的不良项数 is null then 0 else 报损的不良项数 end))  end) as 不良项综合
			 from (
			  select RpID from [BMMES_JM].[dbo].[M_JOB_REPORTWORK] 
			 )as bg
			 left join 
			 (
			 select 
			 RpBLID,RpUnAcceptType,SUM(RpUnAcceptCount)as 不良的不良项数 
			 from [BMMES_JM].[dbo].[M_JOB_REPORTWORK_02] as rp02
			 left join [BMMES_JM].[dbo].[M_JOB_REPORTWORK] as rp on rp.RpID = rp02.RpBLID
			 where 
			 rp.RpWorkproc = @workproc and RpUnAcceptCount >= 1 and RpUnAcceptType in (select distinct QCE03 
 from [BMMES_JM].[dbo].[qce_file] as zb
left join [BMMES_JM].[dbo].[M_JOB_BadItemsForProcess]  as fb on zb.QCE01 = fb.BadCode where ProcessCode in (select @workproc from ecd_file  where IndexSort between 1 and 15 and TA_ECDUD01 = '1' and TA_ECDUD02 = '1') )
			  and 
			  RpAuditDate between convert(varchar(10),DATEADD(dd,0,@auitdate),120)+' 07:00' 
			and convert(varchar(10),DATEADD(dd,1,@auitdate),120)+' 07:00' and RpTotalNums != 0 and
				  RpProduceLine = @produceline and RpFinishID = @finishid  and RpABMaterial = 'A' and CHARINDEX('_',RpERPWorkOrder)=0
			 and rp.RpAuditStatus = '1' 
			 group by RpBLID,RpUnAcceptType 
			 ) as rpw02 on bg.RpID = rpw02.RpBLID 
			 left join( 
			 select RpBFID, RpRejectType,sum(RpRejectCount)as 报废的不良项数 
			 from [BMMES_JM].[dbo].[M_JOB_REPORTWORK_03] as rp03
			 left join [BMMES_JM].[dbo].[M_JOB_REPORTWORK] as rp on rp.RpID = rp03.RpBFID
			 where  rp.RpWorkproc =@workproc and RpRejectCount >=1 and RpRejectType in (select distinct QCE03 
 from [BMMES_JM].[dbo].[qce_file] as zb
left join [BMMES_JM].[dbo].[M_JOB_BadItemsForProcess]  as fb on zb.QCE01 = fb.BadCode where ProcessCode in (select @workproc from ecd_file  where IndexSort between 1 and 15 and TA_ECDUD01 = '1' and TA_ECDUD02 = '1') )
			  and RpAuditDate between convert(varchar(10),DATEADD(dd,0,@auitdate),120)+' 07:00' 
			and convert(varchar(10),DATEADD(dd,1,@auitdate),120)+' 07:00' and RpTotalNums != 0 and
				  RpProduceLine = @produceline and RpFinishID = @finishid  and RpABMaterial = 'A' and CHARINDEX('_',RpERPWorkOrder)=0
			 and rp.RpAuditStatus = '1' 
			 group by RpBFID,RpRejectType
				) AS rpw03 on bg.RpID = rpw03.RpBFID
				left join(
				 select RpXXID,RpDownlineType,sum(RpDownlineCount)as 报损的不良项数 
				 from [BMMES_JM].[dbo].[M_JOB_REPORTWORK_04] as rp04
				 left join [BMMES_JM].[dbo].[M_JOB_REPORTWORK] as rp on rp.RpID = rp04.RpXXID
				 where  rp.RpWorkproc = @workproc and rp.RpAuditStatus = '1' and RpDownlineType in (select distinct QCE03 
 from [BMMES_JM].[dbo].[qce_file] as zb
left join [BMMES_JM].[dbo].[M_JOB_BadItemsForProcess]  as fb on zb.QCE01 = fb.BadCode where ProcessCode in (select @workproc from ecd_file  where IndexSort between 1 and 15 and TA_ECDUD01 = '1' and TA_ECDUD02 = '1') )
				  and RpAuditDate between convert(varchar(10),DATEADD(dd,0,@auitdate),120)+' 07:00' 
				and convert(varchar(10),DATEADD(dd,1,@auitdate),120)+' 07:00' and 
					  RpProduceLine = @produceline and RpFinishID = @finishid  and RpDownlineCount >=1 and RpTotalNums != 0 and RpABMaterial = 'A' and CHARINDEX('_',RpERPWorkOrder)=0
				 group by RpXXID,RpDownlineType
				) as rpw04 on  bg.RpID = rpw04.RpXXID group by RpUnAcceptType,RpRejectType,RpDownlineType)A
				

            
        end

    
     Select A.QCE03, (case when sl.不良项综合 is null then  0 else sl.不良项综合 END) 不良项综合  from (
		select distinct QCE03 
		  from [BMMES_JM].[dbo].[qce_file] as zb
		left join [BMMES_JM].[dbo].[M_JOB_BadItemsForProcess]  as fb on zb.QCE01 = fb.BadCode 
		where ProcessCode in (select ECD01 from ecd_file  where IndexSort between 1 and 15 and TA_ECDUD01 = '1' and TA_ECDUD02 = '1') 
		) A left join   #temp as sl on A.QCE03 = sl.blx order by A.QCE03 
end
	
	
	--exec [usp_unaccept] '','','${choose}',NULL,'${gdxh}'