SQLSERVER2000 存储过程与ORACLE存储过程对比
SQLSERVER版本
CREATE VIEW StatusTable AS
SELECT A.*,M.[MaTolID],D.[DeptName],G.[MaTolGrpName],D.[DeptID],G.[MaTolGrpID]
FROM (
SELECT [status]=
case [BaseState]
when 'IDLE' then '空闲'
when 'RUNNING ' then '运行'
end, [BaseStateDuring] as statusTimeValue,[StartTime],[MaTolName]
FROM [MachineBaseRunState]
UNION ALL
SELECT [status]=
case D.[IsError]
when 0 then '空闲'
when 1 then '故障'
end, [statusTimeValue]=[AlarmDuring],R.[StartTime],R.[MaTolName]
FROM [AlarmRunInfo]AS R ,[AlarmInfoDefine] AS D
WHERE R.[MaTolName]=(SELECT [MaTolName] From [MachineTools] WHERE MaTolID= D.[MaTolID]) AND R.[AlarmID]=D.[AlarmID]) AS A,
[MachineTools] AS M,[MachineToolGroups] AS G,[Departments] AS D
WHERE M.[DeptID]=D.[DeptID] AND M.[MaTolGrpID]=G.[MaTolGrpID] AND A.[MaTolName]=M.[MaTolName]
CREATE Proc WorkpieceDetailReport
@MaTolName nvarchar(50),
@dtpBegin Datetime=null,
@dtpEnd Datetime=null
as
begin
SELECT MaTolName,ProgramName, StartTime, During
FROM ProgramRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59'AND MaTolName=@MaTolName
end
GO
CREATE Proc Workpiece_DetailReport
@MaTolName nvarchar(50),
@dtpBegin Datetime=null,
@dtpEnd Datetime=null
as
begin
SELECT MaTolName,ProgramName, StartTime, dbo.GetTimeStr(During) AS During
FROM ProgramRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59'AND MaTolName=@MaTolName
end
GO
CREATE Proc Workpiece_CountReport
@MaTolName nvarchar(50)=null,
@dtpBegin Datetime=null,
@dtpEnd Datetime=null
as
begin
if(@MaTolName='#')
begin
set @MaTolName=null
end
if (@MaTolName IS not null)
begin
SELECT MaTolName,ProgramName, count(ProgramName) as ProgramNameCount,ProgramName As ReportGroup
FROM ProgramRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59'AND MaTolName=@MaTolName group by MaTolName,ProgramName
end
else
begin
SELECT MaTolName, count(*) as ProgramNameCount,MaTolName as ReportGroup
FROM ProgramRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59'group by MaTolName
end
end
GO
/*查询指定的信息*/
CREATE procedure Report_MaTolInfo
@ID nvarchar(40),
@table nvarchar(50)
as
begin
declare @value nvarchar(50)
set @value =@table
if(@table='Departments')
begin
SELECT D.* FROM [Departments] AS D,[UsersInDepartments] AS U WHERE D.[DeptID]=U.[DeptID] AND U.UserID=cast(@ID AS uniqueidentifier) --根据UserID查部门
end
if(@table='MachineToolGroups')
begin
SELECT * FROM [MachineToolGroups] WHERE DeptID=cast(@ID AS uniqueidentifier) --根据DeptID查机床组
end
if(@table='MachineTools')
begin
SELECT * FROM [MachineTools] WHERE MaTolGrpID=cast(@ID AS uniqueidentifier) --根据MaTolGrpID查机床
end
end
GO
CREATE Proc MachineBaseRunStateReport
@MaTolName nvarchar(50),
@dtpBegin Datetime=null,
@dtpEnd Datetime=null
as
begin
if(@MaTolName='ALL')
begin
select matolname,basestate =case basestate when 'IDLE' THEN '空闲' when 'RUNNING' THEN '运行' when 'OFFLINE' THEN '关机'when '' then '其他'end,starttime,basestateduring
from machinebaserunstate
where StartTime>=@dtpBegin and StartTime <=@dtpEnd group by basestate,matolname,starttime,basestateduring
end
else
begin
select matolname,basestate =case basestate when 'IDLE' THEN '空闲' when 'RUNNING' THEN '运行' when 'OFFLINE' THEN '关机'when '' then '其他'end,starttime,basestateduring
from machinebaserunstate
where StartTime>=@dtpBegin and StartTime <=@dtpEnd and MaTolName=@MaTolName group by basestate,matolname,starttime,basestateduring
end
end
GO
CREATE Proc Alarm_FrequencyReport1
@MaTolName nvarchar(50),
@Alarm_no nvarchar(50),
@dtpBegin Datetime=null,
@dtpEnd Datetime=null,
@grouptime int
as
begin
if(@grouptime=0)
begin
select count(AlarmID) as countAlarmID,@Alarm_no as AlarmID ,AlarmInfo as AlarmInfo,dbo.GetTimeStr(sum(AlarmDuring)) AS sumAlarmDuring ,dbo.uf_GetDayName(CONVERT(char(10), StartTime, 20)) as ReportGroup from AlarmRunInfo where AlarmID =@Alarm_no and StartTime>=@dtpBegin and StartTime <=@dtpEnd AND MaTolName=@MaTolName group by dbo.uf_GetDayName(CONVERT(char(10), StartTime, 20)),AlarmInfo
end
else if(@grouptime=1)
begin
select count(AlarmID) as countAlarmID,@Alarm_no as AlarmID ,AlarmInfo as AlarmInfo,dbo.GetTimeStr(sum(AlarmDuring)) AS sumAlarmDuring ,dbo.uf_GetMonthName(CONVERT(char(10), StartTime, 20)) as ReportGroup from AlarmRunInfo where AlarmID =@Alarm_no and StartTime>=@dtpBegin and StartTime <=@dtpEnd AND MaTolName=@MaTolName group by dbo.uf_GetMonthName(CONVERT(char(10), StartTime, 20)),AlarmInfo
end
else if(@grouptime=2)
begin
select count(AlarmID) as countAlarmID,@Alarm_no as AlarmID ,AlarmInfo as AlarmInfo,dbo.GetTimeStr(sum(AlarmDuring)) AS sumAlarmDuring ,dbo.uf_GetSeasonName(CONVERT(char(10), StartTime, 20)) as ReportGroup from AlarmRunInfo where AlarmID =@Alarm_no and StartTime>=@dtpBegin and StartTime <=@dtpEnd AND MaTolName=@MaTolName group by dbo.uf_GetSeasonName(CONVERT(char(10), StartTime, 20)),AlarmInfo
end
else if(@grouptime=3)
begin
select count(AlarmID) as countAlarmID,@Alarm_no as AlarmID ,AlarmInfo as AlarmInfo,dbo.GetTimeStr(sum(AlarmDuring)) AS sumAlarmDuring ,dbo.uf_GetYearName(CONVERT(char(10), StartTime, 20)) as ReportGroup from AlarmRunInfo where AlarmID =@Alarm_no and StartTime>=@dtpBegin and StartTime <=@dtpEnd AND MaTolName=@MaTolName group by dbo.uf_GetYearName(CONVERT(char(10), StartTime, 20)),AlarmInfo
end
end
GO
CREATE Proc Alarm_FrequencyReport
@MaTolName nvarchar(50),
@dtpBegin Datetime=null,
@dtpEnd Datetime=null
as
begin
SELECT count(AlarmID) as countAlarmID,AlarmID AS AlarmID, AlarmInfo AS AlarmInfo,dbo.GetTimeStr(sum(AlarmDuring)) AS sumAlarmDuring,AlarmID as ReportGroup
FROM AlarmRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59' and MaTolName=@MaTolName group by AlarmID,AlarmInfo
end
GO
CREATE Proc Alarm_DistributedReport
@MaTolName nvarchar(50),
@AlarmID nvarchar(50),
@dtpBegin Datetime=null,
@dtpEnd Datetime=null,
@grouptime int
as
begin
if(@grouptime=0)
begin
SELECT count(*) as countAlarm,AlarmID AS AlarmID, AlarmInfo AS AlarmInfo,
dbo.uf_GetDayName(CONVERT(char(10), StartTime, 20)) as AlarmTime,
dbo.uf_GetDayName(CONVERT(char(10), StartTime, 20)) as ReportGroup
FROM AlarmRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59'AND MaTolName=@MaTolName
and AlarmID=@AlarmID group by AlarmID,AlarmInfo,dbo.uf_GetDayName(CONVERT(char(10), StartTime, 20))
end
else if(@grouptime=1)
begin
SELECT count(*) as countAlarm,AlarmID AS AlarmID, AlarmInfo AS AlarmInfo,
dbo.uf_GetMonthName(CONVERT(char(10), StartTime, 20)) as AlarmTime,
dbo.uf_GetMonthName(CONVERT(char(10), StartTime, 20)) as ReportGroup
FROM AlarmRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59'AND MaTolName=@MaTolName
and AlarmID=@AlarmID group by AlarmID,AlarmInfo,dbo.uf_GetMonthName(CONVERT(char(10), StartTime, 20))
end
else if(@grouptime=2)
begin
SELECT count(*) as countAlarm,AlarmID AS AlarmID, AlarmInfo AS AlarmInfo,
dbo.uf_GetSeasonName(CONVERT(char(10), StartTime, 20)) as AlarmTime,
dbo.uf_GetSeasonName(CONVERT(char(10), StartTime, 20)) as ReportGroup
FROM AlarmRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59'AND MaTolName=@MaTolName
and AlarmID=@AlarmID group by AlarmID,AlarmInfo,dbo.uf_GetSeasonName(CONVERT(char(10), StartTime, 20))
end
else if(@grouptime=3)
begin
SELECT count(*) as countAlarm,AlarmID AS AlarmID, AlarmInfo AS AlarmInfo,
dbo.uf_GetYearName(CONVERT(char(10), StartTime, 20)) as AlarmTime,
dbo.uf_GetYearName(CONVERT(char(10), StartTime, 20)) as ReportGroup
FROM AlarmRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59'AND MaTolName=@MaTolName
and AlarmID=@AlarmID group by AlarmID,AlarmInfo,dbo.uf_GetYearName(CONVERT(char(10), StartTime, 20))
end
end
GO
CREATE Proc Alarm_StatisicReport
@UserId uniqueidentifier,
@Workshop uniqueidentifier,
@Team uniqueidentifier,
@Machine nvarchar(50)=null,
@machinetype nvarchar(20)=null,
@dtpBegin Datetime=null,
@dtpEnd Datetime=null
as
begin
--Declare @str varchar(2000)
begin
if(@Workshop='66755c63-9846-401d-a228-4c164b82330e')
begin
set @Workshop=null
end
if(@Team='66755c63-9846-401d-a228-4c164b82330e')
begin
set @Team=null
end
end
if (@Workshop IS null)AND(@machinetype IS null)AND(@Team is null)AND(@Machine is NULL)
begin
SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,dbo.GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=@UserId)AND(mr.StartTime<=@dtpEnd AND mr.StartTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName order by StartTime desc
end
else if(@machinetype IS null)/*DeptName不为空，MaTolType为空*/
begin
if(@Workshop IS NOT null)AND(@Team is null)AND(@Machine is NULL)
begin
SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,dbo.GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=@UserId )AND(mr.StartTime<=@dtpEnd AND mr.StartTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=@Workshop) order by StartTime desc
end
else if(@Workshop IS not null)and(@Team is not null)AND(@Machine is NULL)
begin
SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,dbo.GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=@UserId )AND(mr.StartTime<=@dtpEnd AND mr.StartTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=@Workshop)AND(MG.MaTolGrpID=@Team) order by StartTime desc
end
else
begin
SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,dbo.GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=@UserId )AND(mr.StartTime<=@dtpEnd AND mr.StartTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=@Workshop)AND(MG.MaTolGrpID=@Team)AND(mt.MaTolName=@Machine) order by StartTime desc
end
end
else if(@machinetype IS not null)
begin
--if (@Workshop IS null)AND(@machinetype IS null)AND(@Team is null)AND(@Machine is NULL)
if (@Workshop IS null)AND(@Team is null)AND(@Machine is NULL)
begin
SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,dbo.GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=@UserId)AND(mr.StartTime<=@dtpEnd AND mr.StartTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND mt.MaTolType=@machinetype order by StartTime desc
end
else if(@Workshop IS NOT null)AND(@Team is null)AND(@Machine is NULL)
begin
SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,dbo.GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=@UserId )AND(mr.StartTime<=@dtpEnd AND mr.StartTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=@Workshop)AND mt.MaTolType=@machinetype order by StartTime desc
end
else if(@Workshop IS not null)and(@Team is not null)AND(@Machine is NULL)
begin
SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,dbo.GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=@UserId )AND(mr.StartTime<=@dtpEnd AND mr.StartTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=@Workshop)AND(MG.MaTolGrpID=@Team)AND mt.MaTolType=@machinetype order by StartTime desc
end
else
begin
SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,dbo.GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=@UserId )AND(mr.StartTime<=@dtpEnd AND mr.StartTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=@Workshop)AND(MG.MaTolGrpID=@Team)AND(mt.MaTolName=@Machine)AND mt.MaTolType=@machinetype order by StartTime desc
end
end
--exec (@str)
end
GO
CREATE Proc DownTimeReasonReport
@UserId uniqueidentifier,
@Workshop uniqueidentifier,
@Team uniqueidentifier,
@Machine nvarchar(50)=null,
@machinetype nvarchar(20)=null,
@dtpBegin Datetime=null,
@dtpEnd Datetime=null
as
begin
--Declare @str varchar(2000)
begin
if(@Workshop='66755c63-9846-401d-a228-4c164b82330e')
begin
set @Workshop=null
end
if(@Team='66755c63-9846-401d-a228-4c164b82330e')
begin
set @Team=null
end
end
if (@Workshop IS null)AND(@machinetype IS null)AND(@Team is null)AND(@Machine is NULL)
begin
SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=@UserId)AND infotag='STOPCON' AND(mr.StateTime<=@dtpEnd AND mr.StateTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName) as a, downTimeReason as b where a.infoText=b.downTimeReasonNo order by StateTime desc
end
else if(@machinetype IS null)/*DeptName不为空，MaTolType为空*/
begin
if(@Workshop IS NOT null)AND(@Team is null)AND(@Machine is NULL)
begin
SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=@UserId)AND infotag='STOPCON' AND(mr.StateTime<=@dtpEnd AND mr.StateTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND (D.DeptID=@Workshop)) as a, downTimeReason as b where a.infoText=b.downTimeReasonNo order by StateTime desc
end
else if(@Workshop IS not null)and(@Team is not null)AND(@Machine is NULL)
begin
SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=@UserId)AND infotag='STOPCON' AND(mr.StateTime<=@dtpEnd AND mr.StateTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND (D.DeptID=@Workshop)AND(MG.MaTolGrpID=@Team)) as a, downTimeReason as b where a.infoText=b.downTimeReasonNo order by StateTime desc
end
else
begin
SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=@UserId)AND infotag='STOPCON' AND(mr.StateTime<=@dtpEnd AND mr.StateTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND (D.DeptID=@Workshop)AND(MG.MaTolGrpID=@Team)AND(mt.MaTolName=@Machine)) as a, downTimeReason as b where a.infoText=b.downTimeReasonNo order by StateTime desc
end
end
else if(@machinetype IS not null)
begin
--if (@Workshop IS null)AND(@machinetype IS null)AND(@Team is null)AND(@Machine is NULL)
if (@Workshop IS null)AND(@Team is null)AND(@Machine is NULL)
begin
SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=@UserId)AND infotag='STOPCON' AND(mr.StateTime<=@dtpEnd AND mr.StateTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND mt.MaTolType=@machinetype ) as a, downTimeReason as b where a.infoText=b.downTimeReasonNo order by StateTime desc
end
else if(@Workshop IS NOT null)AND(@Team is null)AND(@Machine is NULL)
begin
SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=@UserId)AND infotag='STOPCON' AND(mr.StateTime<=@dtpEnd AND mr.StateTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID
AND mt.MaTolName = mr.MaTolName AND (D.DeptID=@Workshop)AND mt.MaTolType=@machinetype ) as a, downTimeReason as b where a.infoText=b.downTimeReasonNo order by StateTime desc
end
else if(@Workshop IS not null)and(@Team is not null)AND(@Machine is NULL)
begin
SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=@UserId)AND infotag='STOPCON' AND(mr.StateTime<=@dtpEnd AND mr.StateTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID
AND mt.MaTolName = mr.MaTolName AND (D.DeptID=@Workshop)AND(MG.MaTolGrpID=@Team)AND mt.MaTolType=@machinetype ) as a, downTimeReason as b where a.infoText=b.downTimeReasonNo order by StateTime desc
end
else
begin
SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=@UserId)AND infotag='STOPCON' AND(mr.StateTime<=@dtpEnd AND mr.StateTime>=@dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID
AND mt.MaTolName = mr.MaTolName AND(D.DeptID=@Workshop)AND(MG.MaTolGrpID=@Team)AND(mt.MaTolName=@Machine)AND mt.MaTolType=@machinetype ) as a, downTimeReason as b where a.infoText=b.downTimeReasonNo order by StateTime desc
end
end
--exec (@str)
end
GO
/*查询指定表的所有信息*/
CREATE procedure Report_MaTolStartupEff
@ID nvarchar(50),
@Name nvarchar(50),
@begin DateTime,
@end DateTime,
@Time nvarchar(10)
as
DECLARE @MaTol nvarchar(100),@s nvarchar(4000),@Group nvarchar(200)
SET @Group='[dbo].[GetTimeGroup]([StartTime],'+QUOTENAME(@Time,'''')+')'
begin
if(@Name='All')
begin
SET @MaTol=''
end
if(@Name='Dept')
begin
SET @MaTol='[DeptID]='+QUOTENAME(@ID,'''')+' AND'
end
if(@Name='MaTolGrp')
begin
SET @MaTol='[MaTolGrpID]='+QUOTENAME(@ID,'''')+' AND'
end
if(@Name='MaTol')
begin
SET @MaTol='[MaTolName]='+QUOTENAME(@ID,'''')+' AND'
end
if(@Time='')
begin
select @Group='DeptName+'+QUOTENAME('.','''')+'+MaTolGrpName+'+QUOTENAME('.','''')+'+MaTolName'
end
select @s='SELECT [MaTolName],[ONLINE],[WORKING],dbo.GetTimeStr([ONLINE]) as STRONLINE,dbo.GetTimeStr([WORKING]) AS STRWORKING
,[STARTUPEFF]=cast([ONLINE]*10000/[WORKING] as decimal(10,2))/100,[Group]
from(Select sum([statusTimeValue]) AS [ONLINE],[WORKING]=sum([dbo].[GetWorkTime]([StartTime])),MaTolName=DeptName+'+QUOTENAME('.','''')+'+MaTolGrpName+'+QUOTENAME('.','''')+'+MaTolName ,[Group]='+@Group+'
From [StatusTable] WHERE '+@MaTol+' [StartTime] BETWEEN '+QUOTENAME(@begin,'''') +' AND '+QUOTENAME(@end,'''')+'
Group by MaTolName,DeptName,MaTolGrpName,'+@Group+'
)a'
EXECUTE(@s)
end
GO
SELECT MaTolName,sONLINE,WORKING,GetTimeStr(sONLINE) as STRONLINE,
GetTimeStr(WORKING) AS STRWORKING,cast(sONLINE*10000/WORKING as decimal(10,2))/100 as STARTUPEFF,
sGroup from(Select sum(statusTimeValue) AS sONLINE,sum(GetWorkTime(StartTime)) as WORKING,
(DeptName||'||QUOTENAME('.','''')||'||MaTolGrpName||'||QUOTENAME('.','''')||'||MaTolName) as MaTolName,'||vGroup||' as sGroup
From StatusTable WHERE '||MaTol||' StartTime BETWEEN '||QUOTENAME(dbegin,'''') ||' AND '||QUOTENAME(dend,'''')||'
Group by MaTolName,DeptName,MaTolGrpName,'||vGroup||');
/*查询指定机床的运行效率*/
CREATE procedure Report_MaTolRunEff
@ID nvarchar(50),
@Name nvarchar(50),
@begin DateTime,
@end DateTime,
@Time nvarchar(10)
as
DECLARE @MaTol nvarchar(100),@s nvarchar(4000),@Group nvarchar(200)
SET @Group='[dbo].[GetTimeGroup]([StartTime],'+QUOTENAME(@Time,'''')+')'
begin
if(@Name='All')
begin
SET @MaTol=''
end
if(@Name='Dept')
begin
SET @MaTol='[DeptID]='+QUOTENAME(@ID,'''')+' AND'
end
if(@Name='MaTolGrp')
begin
SET @MaTol='[MaTolGrpID]='+QUOTENAME(@ID,'''')+' AND'
end
if(@Name='MaTol')
begin
SET @MaTol='[MaTolName]='+QUOTENAME(@ID,'''')+' AND'
end
if(@Time='')
begin
select @Group='DeptName+'+QUOTENAME('.','''')+'+MaTolGrpName+'+QUOTENAME('.','''')+'+MaTolName'
end
select @s='SELECT [MaTolName],[RUNNING]=sum(case status when '+QUOTENAME('运行','''')+' then Value else 0 end),dbo.GetTimeStr(sum(case status when '+QUOTENAME('运行','''')+' then Value else 0 end)) AS STRRUNNING
,[ONLINE]=sum(Value),dbo.GetTimeStr(sum(Value)) AS STRONLINE
,[RUNEFF]=cast(sum(case status when '+QUOTENAME('运行','''')+' then Value else 0 end)*10000/sum(Value) as decimal(10,2))/100
,[Group]
From
(Select [status],sum([statusTimeValue]) AS Value,MaTolName=DeptName+'+QUOTENAME('.','''')+'+MaTolGrpName+'+QUOTENAME('.','''')+'+MaTolName ,[Group]='+@Group+'
From [StatusTable] WHERE '+@MaTol+' [StartTime] BETWEEN '+QUOTENAME(@begin,'''') +' AND '+QUOTENAME(@end,'''')+'
Group by [status],MaTolName,DeptName,MaTolGrpName,'+@Group+'
)a group by [MaTolName],[Group]'
--拼字符串做的--不知道效率高不高
EXECUTE(@s)
end
GO
/*查询指定机床的基本状态*/
CREATE procedure Report_MaTolStatus_Group
@ID nvarchar(50),
@Name nvarchar(50),
@begin DateTime,
@end DateTime
as
DECLARE @TimeValue BigInt
SET @TimeValue=DATEDIFF (second,@begin,@end)
begin
if(@Name='All')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [StartTime] BETWEEN @begin AND @end GROUP BY [MaTolName] ,[status] WITH ROLLUP
end
if(@Name='Dept')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [DeptID]=cast(@ID AS uniqueidentifier)
AND [StartTime] BETWEEN @begin AND @end GROUP BY [MaTolName],[status] WITH ROLLUP
end
if(@Name='MaTolGrp')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @TimeValue -sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [MaTolGrpID]=cast(@ID AS uniqueidentifier) AND [StartTime] BETWEEN @begin AND @end GROUP BY [MaTolName],[status] WITH ROLLUP
end
if(@Name='MaTol')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [MaTolName]=@ID AND [StartTime] BETWEEN @begin AND @end GROUP BY [MaTolName],[status] WITH ROLLUP
end
end
--和Report_MaTolStatus相比多一个机床分组
GO
/*查询指定机床的基本状态信息*/
CREATE procedure Report_MaTolStatus
@ID nvarchar(50),
@Name nvarchar(50),
@begin DateTime,
@end DateTime
as
DECLARE @TimeValue BigInt,@MaTolNum int,@Values bigint
SET @TimeValue=DATEDIFF (second,@begin,@end)
begin
if(@Name='All')
begin
SET @MaTolNum=(SELECT Count([MaTolName]) FROM [lmdc2].[dbo].[MachineTools])
SET @Values=@TimeValue * @MaTolNum
SELECT [status]=case
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @Values-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @Values-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[MaTolNum]=@MaTolNum,[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [StartTime] BETWEEN @begin AND @end GROUP BY [status] WITH ROLLUP
end
if(@Name='Dept')
begin
SET @MaTolNum=(SELECT Count([MaTolName]) FROM [lmdc2].[dbo].[MachineTools]WHERE [DeptID]=cast(@ID AS uniqueidentifier))
SET @Values=@TimeValue * @MaTolNum
SELECT [status]=case
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @Values-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @Values-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[MaTolNum]=@MaTolNum,[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [DeptID]=cast(@ID AS uniqueidentifier)
AND [StartTime] BETWEEN @begin AND @end GROUP BY [status] WITH ROLLUP
end
if(@Name='MaTolGrp')
begin
SET @MaTolNum=(SELECT Count([MaTolName]) FROM [lmdc2].[dbo].[MachineTools]WHERE [MaTolGrpID]=cast(@ID AS uniqueidentifier))
SET @Values=@TimeValue * @MaTolNum
SELECT [status]=case
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @Values -sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @Values-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[MaTolNum]=@MaTolNum,[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [MaTolGrpID]=cast(@ID AS uniqueidentifier) AND [StartTime] BETWEEN @begin AND @end GROUP BY [status] WITH ROLLUP
end
if(@Name='MaTol')
begin
SET @MaTolNum=1
SET @Values=@TimeValue * @MaTolNum
SELECT [status]=case
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @Values-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @Values-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[MaTolNum]=@MaTolNum,[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [MaTolName]=@ID AND [StartTime] BETWEEN @begin AND @end GROUP BY [status] WITH ROLLUP
end
--根据条件结果分别写入，待寻求更好的办法
end
GO
create FUNCTION [dbo].[GetDayName](@theDate varchar(10))
RETURNS varchar(64) AS
BEGIN
return left(@theDate, 4) + '年' + substring(@theDate, 6, 2) + '月' + right(@theDate, 2) + '日'
END
create FUNCTION [dbo].[GetGroupbyName](@theDate varchar(20))
RETURNS varchar(64) AS
BEGIN
set @theDate=SUBSTRING(@theDate, 1, 10)
return @theDate -- left(@theDate, 4) + '年' + substring(@theDate, 6, 2) + '月' + right(@theDate, 2) + '日'
END
--by javahen 杨思佳
--将时间转换为时间组字符串
CREATE FUNCTION GetTimeGroup(@time datetime,@fmt nvarchar(10))
RETURNS nvarchar(30) AS
BEGIN
declare @value nvarchar(30)
set @value=case @fmt
when 'day' then convert(nvarchar(12),@time,23)
when 'month' then convert(nvarchar(7),@time,23)
when 'quarter' then datename(year,@time)+'年'+datename(quarter,@time)+'季度'
when 'year' then convert(nvarchar(4),@time,23)
END
return @value
END
--by javahen 杨思佳
--将时间值转换为时间格式(hh:mm:ss)的字符串
CREATE FUNCTION GetTimeStr(@sec bigint)
RETURNS varchar(64) AS
BEGIN
declare @second int, @minute int, @hour bigint
set @hour = @sec / 3600
set @minute = (@sec % 3600) / 60
set @second = (@sec % 3600) % 60
return cast(@hour as varchar) + ':' + cast(@minute as varchar) + ':' + cast(@second as varchar)
END
--by javahen 杨思佳
--将时间转换为时间组字符串
CREATE FUNCTION GetWorkTime(@time datetime)
RETURNS bigInt AS
BEGIN
declare @value bigint,@week_d int
select @week_d=datepart(weekday,@time)
select @value=value from
(SELECT [WeekID]=case [WeekID]
when 0 then 7
when [WeekID] then [WeekID]
end,
sum(DATEDIFF(minute,[StartTime],[EndTime])-[RestTime])*60 as value FROM [lmdc2].[dbo].[WorkSheet]
group by WeekID)a where WeekID=@week_d
return @value
END
CREATE FUNCTION uf_GetDayName(@theDate varchar(10))
RETURNS varchar(64) AS
BEGIN
return left(@theDate, 4) + '年' + substring(@theDate, 6, 2) + '月' + right(@theDate, 2) + '日'
END
CREATE FUNCTION uf_GetMonthName(@theDate varchar(10))
RETURNS varchar(64) AS
BEGIN
return left(@theDate, 4) + '年' + substring(@theDate, 6, 2) + '月'
END
CREATE FUNCTION uf_GetSeasonName(@theDate varchar(10))
RETURNS varchar(64) AS
BEGIN
declare @season int;
set @season = cast(substring(@theDate, 6, 2) as int) ;
set @season = (@season-1)/3+1;
return left(@theDate, 4) + '年' + str(@season,1,0) + '季度'
END
CREATE FUNCTION uf_GetYearName(@theDate varchar(10))
RETURNS varchar(64) AS
BEGIN
return left(@theDate, 4) + '年'
END
CREATE FUNCTION uf_MillisecondToTimeString(@ms bigint)
RETURNS varchar(64) AS
BEGIN
declare @second bigint, @minute bigint, @hour bigint
set @second = @ms / 1000
set @hour = @second / 3600
set @minute = (@second % 3600) / 60
set @second = (@second % 3600) % 60
return cast(@hour as varchar) + ':' + cast(@minute as varchar) + ':' + cast(@second as varchar)
END
