Associated SQL for table and table-specific conditions

Use [Chiefmes]
GO

/****** object:storedprocedure [dbo]. [Kenta_rptempoee] Script date:05/12/2015 13:52:00 ******/
SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO

/*************************************************
Creator:wuchun
Create date:2013-10-22
Remark: Calculate the OEE Data report for health workforce
*************************************************/
ALTER procedure [dbo]. [Kenta_rptempoee]
(
@GroupTypevarchar (+) = ' Sum ',--Detail: Staff oee detail sum: Staff OEE Summary
@EmpGroupNOvarchar (30) = ",--people Group
@DateBeginvarchar (10) = ' 2013-04-26 ',
@DateEndvarchar (10) = ' 2015-05-08 ',
@EmpIDvarchar (10) = ",
@BCvarchar (10) = "
)

As
DECLARE @ProductName varchar (+), @MachineNo varchar (@BCCode varchar (10)
If object_id (' tempdb.. #DailyTableEmp ') is not a null drop table #DailyTableEmp
CREATE TABLE #DailyTableEmp
(
Machineno varchar (,--) Machine number
Empgroupno varchar (50),
EmpID varchar (,--) Worker number
Currdate varchar (,--) Date
BCCode varchar (,--frequency)
Dispatchno varchar (,--) Dispatch ticket Number
Tz_customerno varchar (,--) Demand customers
Mouldno varchar (30),
ProductName varchar,--Product Name
Productno varchar (,--) Product Code
Tz_mono varchar (,--) Order number
Dispatchnum int,--number of jobs dispatched
Productnum int,--Number of products
Standsocketnum int,--Standard
socketnum int,--Actual
Socketrate decimal (10,2),--rate
sumnormalmouldnum int,--Total number of modules
sumnormalcycle int,--Artificial period sum
Standcycle decimal (10,2),--standard cycle
Averagecycle decimal (10,2),--actual average period
Cyclediffrate decimal (10,2),--mean period difference rate (period percent = (averagecycle-standcycle)/standcycle)
Tz_standemp decimal (10,2),--standard number of people
Actualemp decimal (10,2),--actual population
----This is the Dispatch Ticket information section
Dispatchtime decimal (10,2),--Dispatch ticket Total time
Planproducttime decimal (10,2),--planned production time
Realmactime decimal (10,2),--actual time of movement
Noplantime decimal (10,2),--planned down time
Huanmo decimal (10,2),--Mold Change Time
Huanliao decimal (10,2),--change of material time
Huandan decimal (10,2),--Exchange Order Time
Jiqiguzhang decimal (10,2),--machine failure time
Mojuguzhang decimal (10,2),--Mold Failure Time
Fusheguzhang decimal (10,2),--auxiliary failure time
Dailiao decimal (10,2),--time to wait
Wudingdan decimal (10,2),--No order time
Qita decimal (10,2),--Online maintenance time
Dairen decimal (10,2),--raw material bad time
Macidletime decimal (10,2),--standby idle Time
Permissiontime Decimal (10,2) Default (0.00),--Approval Time
----This is the Stop time information segment
Planoutputqty int,--Planned output = (total time-planned down time) *3600/standard period * Standard
Actualoutputqty int,--Actual production
Packagenum int,--Number of packages
Theoreticalqty int,--theory actual production time capacity = actual time *3600/Standard period * Standard
Productednum int,--Production number
Badnum int,--Bad product number
Badrate decimal (10,4),--adverse rate
Efficiencyrate decimal (10,4),--machine Efficiency (efficiency) = total production/Theoretical actual production time capacity
Availability Decimal (10,4),--effective boot rate (performance, validity) = Actual active Time/(total time-scheduled down time-approval time)
OEE decimal (10,4),--= machine Efficiency * Effective boot rate * yield
Area varchar (,--) responsible for areas
Flag int--Level logo, 0-Dispatch work order (with production), 1-Dispatch ticket-level (no-production), 2 machine-numbering level, 3-person summary level
)

If object_id (' tempdb.. #PlanTime ') is not a null drop table #PlanTime
CREATE TABLE #PlanTime
(
Machineno varchar (30),
Planproducttime Decimal (10,2),
BCCode varchar (10)
)

Insert into #DailyTableEmp (Machineno,empgroupno, EmpID, Currdate,bccode,dispatchno,tz_customerno,mouldno, ProductName,
Productno,tz_mono,dispatchnum,productnum,standsocketnum,socketnum,socketrate,sumnormalmouldnum,sumnormalcycle,
Standcycle,averagecycle,cyclediffrate,tz_standemp,actualemp,dispatchtime, Planproducttime,realmactime, Noplantime,
Huanmo,huanliao,huandan,jiqiguzhang,mojuguzhang,fusheguzhang,dailiao,wudingdan,qita,dairen,macidletime, Permissiontime,
Planoutputqty,actualoutputqty,packagenum,theoreticalqty,productednum,badnum,badrate,efficiencyrate, Availability,oee,flag)
Select
P.machineno,e.empgroupno, E.empid, P.currdate,p.bccode,p.dispatchno,p.tz_customerno,p.mouldno,p.productname,p. Productno,
P.tz_mono,p.dispatchnum,p.productnum,p.standsocketnum,p.socketnum,p.socketrate,p.sumnormalmouldnum,p. Sumnormalcycle,
P.standcycle,p.averagecycle,p.cyclediffrate,p.tz_standemp,p.actualemp,p.dispatchtime, P.PlanProductTime,p. Realmactime,
P.noplantime,p.huanmo,p.huanliao,p.huandan,p.jiqiguzhang,p.mojuguzhang,p.fusheguzhang,p.dailiao,p.wudingdan,p. Qita,
P.dairen,p.macidletime,p.permissiontime,p.planoutputqty,p.actualoutputqty,p.packagenum,p.theoreticalqty,
P.productednum,p.badnum,p.badrate,p.efficiencyrate,p.availability,p.oee,p.flag
From Kenta_dailyproductioninfo P
Join Kenta_empoeeconfig E

On

(@EmpGroupNO = "or E.empgroupno = @EmpGroupNO)
and Charindex (P.machineno,e.machineno) >0
and P.bccode=e.bccode
and e.showoee=1
and ((P.currdate between E.startdate and E.enddate) or (p.currdate>e.startdate and e.enddate is null))

where p.currdate between @DateBegin and @DateEnd
and Dispatchno<> ' '
and (@EmpID = "or e.empid = @EmpID or e.empname like @EmpID + '% ')
Order by P.currdate, P.bccode, P.machineno



Update a set A.planproducttime=b.planproducttime
From #DailyTableEmp A
INNER JOIN #PlanTime B on A.machineno=b.machineno and A.bccode=b.bccode

Update #DailyTableEmp set socketrate=case when standsocketnum=0 then 0 else socketnum/(standsocketnum*1.0) end--rate
, badrate=case when productednum=0 then 0 else badnum/(productednum*1.0) end--bad rate
, efficiencyrate=case when theoreticalqty=0 then 0 else productednum/(theoreticalqty*1.0) end--machine Efficiency (efficient)
--, availability=case when dispatchtime-noplantime=0 then 0 else realmactime/(dispatchtime-noplantime-permissiontime) end--Effective boot rate (performance, availability)
, availability=case when dispatchtime-noplantime-permissiontime-dairen=0 then 0 else realmactime/( Dispatchtime-noplantime-permissiontime-dairen) end--Effective boot rate (performance, availability)
where flag=2

Update #DailyTableEmp set oee=efficiencyrate*availability* (1-badrate)-Machine efficiency * Effective boot rate * yield
where flag=2

--a single-layer OEE import completed
--============================================================================================================= =====================

INSERT INTO #PlanTime
Select distinct Machineno, Planproducttime, BCCode from #DailyTableEmp where currdate between @DateBegin and @DateEnd

Insert into #DailyTableEmp (Machineno,empgroupno, EmpID, Currdate, BCCode, Standsocketnum, Socketnum, Tz_standemp, Actualemp, Dispatchtime, Realmactime, Noplantime, Huanmo, Huanliao, Huandan, Jiqiguzhang, Mojuguzhang, FuSheGuZhang, Dailiao, Wudingdan, Qita, Dairen, Macidletime, Permissiontime, Planoutputqty, Actualoutputqty, PackageNum, Theoreticalqty, Productednum, Badnum, Flag)
Select Machineno, Empgroupno, EmpID, currdate, BCCode, sum (standsocketnum), sum (socketnum), sum (tz_standemp), SUM ( actualemp), sum (dispatchtime/productnum), sum (realmactime/productnum), sum (noplantime/productnum), SUM (HuanMo/ Productnum), sum (huanliao/productnum), sum (huandan/productnum), sum (jiqiguzhang/productnum), SUM (mojuguzhang/ Productnum), sum (fusheguzhang/productnum), sum (dailiao/productnum), sum (wudingdan/productnum), SUM (qita/productnum ), sum (dairen/productnum), sum (macidletime/productnum), sum (permissiontime/productnum), sum (planoutputqty), SUM ( Actualoutputqty), sum (packagenum), sum (theoreticalqty), sum (productednum), sum (badnum), 2
From #DailyTableEmp
where Flag in (0,1)
Group by Machineno,empgroupno, EmpID, Currdate, BCCode


Update a set a.planproducttime=b.planproducttime from #DailyTableEmp a inner join #PlanTime B on A.machineno=b.machineno a nd a.bccode=b.bccode

Update #DailyTableEmp set socketrate=case when standsocketnum=0 then 0 else socketnum/(standsocketnum*1.0) end--rate
, badrate=case when productednum=0 then 0 else badnum/(productednum*1.0) end--bad rate
, efficiencyrate=case when theoreticalqty=0 then 0 else productednum/(theoreticalqty*1.0) end--machine Efficiency (efficient)
--, availability=case when dispatchtime-noplantime=0 then 0 else realmactime/(dispatchtime-noplantime-permissiontime) end--Effective boot rate (performance, availability)
, availability=case when dispatchtime-noplantime-permissiontime-dairen=0 then 0 else realmactime/( Dispatchtime-noplantime-permissiontime-dairen) end--Effective boot rate (performance, availability)
where flag=2
Update #DailyTableEmp set oee=efficiencyrate*availability* (1-badrate)-Machine efficiency * Effective boot rate * yield
where flag=2
--according to the OEE of the machine number layer, this calculation is completed
--Supplementary requirements 2013-05-27 requires that the product names of all dispatch orders be separated by semicolons to each machine
--declare @rEmpGroupNO varchar (), @rEmpNO varchar (50)
--declare cur_daily Cursor FOR
--select Machineno, BCCode, ProductName, Empgroupno, EmpId from #DailyTableEmp where Flag in (0,1) and IsNull (ProductName, ' ') <> '
--open cur_daily
--fetch next from cur_daily to @MachineNo, @BCCode, @ProductName, @rEmpGroupNO, @rEmpNO
--while @ @FETCH_STATUS =0
--begin
--print @MachineNo + @[email protected]
--update #DailyTableEmp Set Productname=isnull (ProductName, ") [email protected]+ '; ‘
--where flag=2 and [email protected] and [email protected] and empgroupno = @rEmpGroupNO and EmpID = @rEmpNO

--fetch next from cur_daily to @MachineNo, @BCCode, @ProductName, @rEmpGroupNO, @rEmpNO
--end
--close cur_daily
--deallocate cur_daily
--============================================================================================================= =====================

Insert into #DailyTableEmp (Empgroupno,empid,currdate, BCCode, Standsocketnum, Socketnum, Tz_standemp, Actualemp, Dispatchtime, Planproducttime, Realmactime, Noplantime, Huanmo, Huanliao, Huandan, Jiqiguzhang, MoJuGuZhang, Fusheguzhang, Dailiao, Wudingdan, Qita, Dairen, Macidletime, Permissiontime, Planoutputqty, ActualOutputQty, PackageNum , Theoreticalqty, Productednum, Badnum,area, Flag)
Select A.empgroupno, A.empid, currdate, BCCode, sum (standsocketnum), sum (socketnum), sum (tz_standemp), sum (actualemp), SUM (dispatchtime), sum (planproducttime), sum (realmactime), sum (noplantime), sum (HUANMO), sum (Huanliao), sum (Huandan) , sum (Jiqiguzhang), sum (Mojuguzhang), sum (Fusheguzhang), sum (Dailiao), sum (Wudingdan), sum (Qita), sum (Dairen), SUM ( Macidletime), sum (permissiontime), sum (planoutputqty), sum (actualoutputqty), sum (packagenum), sum (Theoreticalqty), SUM (productednum), sum (badnum),
", 3
From #DailyTableEmp a inner joins MACHINEMSTR B on A.machineno=b.machine_code
where flag=2
Group by A.empgroupno, A.empid, Currdate, BCCode


Insert into #DailyTableEmp (Empgroupno,empid,currdate, BCCode, Standsocketnum, Socketnum, Tz_standemp, Actualemp, Dispatchtime, Planproducttime, Realmactime, Noplantime, Huanmo, Huanliao, Huandan, Jiqiguzhang, MoJuGuZhang, Fusheguzhang, Dailiao, Wudingdan, Qita, Dairen, Macidletime, Permissiontime, Planoutputqty, ActualOutputQty, PackageNum , Theoreticalqty, Productednum, Badnum, Flag)
Select ' Aggregate ', EmpID, ', ', sum (standsocketnum), sum (socketnum), sum (tz_standemp), sum (actualemp), sum (dispatchtime), SUM (planproducttime), sum (realmactime), sum (noplantime), sum (HUANMO), sum (Huanliao), sum (Huandan), sum (Jiqiguzhang), SUM (Mojuguzhang), sum (Fusheguzhang), sum (Dailiao), sum (Wudingdan), sum (Qita), sum (Dairen), sum (macidletime), SUM ( Permissiontime), sum (planoutputqty), sum (actualoutputqty), sum (packagenum), sum (theoreticalqty), sum (productednum), SUM (Badnum), 3
From #DailyTableEmp a inner joins MACHINEMSTR B on A.machineno=b.machine_code
where flag=2
Group BY Empgroupno, A.empid

Update #DailyTableEmp set socketrate=case when standsocketnum=0 then 0 else socketnum/(standsocketnum*1.0) end--rate
, badrate=case when productednum=0 then 0 else badnum/(productednum*1.0) end--bad rate
, efficiencyrate=case when theoreticalqty=0 then 0 else productednum/(theoreticalqty*1.0) end--machine Efficiency (efficient)
, availability=case when dispatchtime-noplantime-permissiontime-dairen=0 then 0 else realmactime/( Dispatchtime-noplantime-permissiontime-dairen) end--Effective boot rate (performance, availability)
--, availability=case when planproducttime=0 then 0 else realmactime/(planproducttime-permissiontime) end--effective boot rate (performance, Effectiveness
where flag=3
Update #DailyTableEmp set oee=efficiencyrate*availability* (1-badrate)-Machine efficiency * Effective boot rate * yield
where flag=3
--based on the staff level OEE to complete the calculation

Update #DailyTableEmp set Standsocketnum=null,socketnum=null,socketrate=null,tz_standemp=null,actualemp=null, Dispatchtime=null,
Planproducttime=null,efficiencyrate=null,availability=null,badnum=null,badrate=null where EmpGroupNO= ' rollup '
--============================================================================================================= =====================


If @GroupType = ' Detail '
Begin
Select T.machineno,t.empid,currdate,case when t.bccode= ' A ' then ' Day shift ' when t.bccode= ' B ' then ' night ' else ' end as Bccode,dis Patchno,tz_customerno,productname,productno,tz_mono,dispatchnum,standsocketnum,socketnum
, convert (varchar), CAST (socketrate*100 as Decimal (10,0))) + '% ' as socketrate,standcycle,averagecycle
, convert (varchar), CAST (cyclediffrate*100 as Decimal (10,0))) + '% ' as cyclediffrate,tz_standemp,actualemp, Dispatchtime, Planproducttime,realmactime,noplantime,huanmo,huanliao,huandan,jiqiguzhang,mojuguzhang, Fusheguzhang,dailiao,wudingdan,qita,dairen,macidletime,permissiontime,planoutputqty,actualoutputqty,packagenum , Theoreticalqty
, efficiencyrate,availability = IsNull (availability,0), Badnum,badrate,oee = IsNull (oee,0)
, Empgroupname = (select top 1 empgroupname from MESAlarm.dbo.AlarmEmpGroup g where g.empgroupno = T.empgroupno)
, EmployeeName = E.empname
From #DailyTableEmp t
Left join Kenta_empoeeconfig e on t.empid = E.empid
where t.flag=2 and t.bccode like '% ' [email protected]+ '% '
Order by T.currdate, T.machineno, T.bccode, T.dispatchno
End
else if @GroupType = ' Sum '
Begin
Select T.empgroupno,case when t.empgroupno= ' summary ' then ' else T.empid end Empid,t.empid EmpID2, currdate, case when T.bccod E= ' A ' then ' Day shift ' when t.bccode= ' B ' then ' late ' else ' end as Bccode,dispatchno,tz_customerno,productname,productno,tz_mono, Dispatchnum,standsocketnum,socketnum
, convert (varchar), CAST (socketrate*100 as Decimal (10,0))) + '% ' as socketrate,standcycle,averagecycle
, convert (varchar), CAST (cyclediffrate*100 as Decimal (10,0))) + '% ' as cyclediffrate,tz_standemp,actualemp, Dispatchtime, Planproducttime,realmactime,noplantime,huanmo,huanliao,huandan,jiqiguzhang,mojuguzhang, Fusheguzhang,dailiao,wudingdan,qita,dairen,macidletime,permissiontime,planoutputqty,actualoutputqty,packagenum , Theoreticalqty
, efficiencyrate,availability,badnum,case when t.empgroupno= ' summarize ' then ' else ' convert (varchar (), badrate) end Badrate,oee,
Dbo. Getmachinearea (E.machineno) area,case when t.empgroupno= ' summarize ' then ' else T.empgroupno end Empgroupname
, EmployeeName = case when t.empgroupno= ' summary ' then ' else e.empname end
From #DailyTableEmp t
Left join Kenta_empoeeconfig e on t.empid = E.empid
and ((T.currdate between E.startdate and E.enddate) or (t.currdate>e.startdate and e.enddate is null))
where t.flag=3 and t.bccode like '% ' [email protected]+ '% '
Order by EmpID2, case when t.empgroupno= ' rollup ' then 2 else 1 end,t.currdate, T.machineno, BCCode, Dispatchno
End

drop table #DailyTableEmp

GO

Associated SQL for table and table-specific conditions