/****** Object: StoredProcedure [dbo].[DataCsvs_QueryDoMethod] Script Date: 2022/8/12 0:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Team QIUBB>
-- Create date: <2022.08.11>
-- Description: 按月查询动态表数据
-- =============================================
ALTER PROCEDURE [dbo].[DataCsvs_QueryDoMethod]
@I_Sender NVARCHAR(200)='', --客户端执行按钮
@I_ReturnMessage NVARCHAR(MAX)='' OUTPUT, --返回的信息,支持多语言
@I_ExceptionFieldName NVARCHAR(100)='' OUTPUT, --向客户端报告引起冲突的字段
@I_LanguageId CHAR(1)='1', --客户端传入的语言ID
@I_PlugInCommand VARCHAR(5)='', --插件命令
@I_OrBitUserId CHAR(12)='', --用户ID
@I_OrBitUserName NVARCHAR(100)='', --用户名
@I_ResourceId CHAR(12)='', --资源ID(如果资源不在资源清单中,那么它将是空的)
@I_ResourceName nvarchar(100)='', --资源名
@I_PKid char(12) ='', --主键
@I_ParentPKId char(12)='', --父级主键
@I_Parameter nvarchar(100)='', --插件参数
--以上变量为系统服务固定接口参数,必须在每一个DoEvent过程中实现.
@HostCoordinate NVARCHAR(50)='',
@HostName NVARCHAR(50)='',
@StartDate datetime=null,
@EndDate datetime=NULL,
@Mouth NVARCHAR(50)='',
@Year NVARCHAR(50)='',
@HostNumber NVARCHAR(50)=''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Sql NVARCHAR(MAX)
DECLARE @tablename NVARCHAR(MAX)
-- DECLARE @firstday DATETIME
--DECLARE @lastday DATETIME
--SET @firstday=CONVERT(VARCHAR(10),DATEADD(DAY,-DATEPART(DAY,GETDATE())+1,GETDATE()),23) +' 00:00:00' --当月第一天
--SET @lastday=CONVERT(VARCHAR(10),DATEADD(DAY,-1,DATEADD(MONTH,1,GETDATE()-DAY(GETDATE())+1)),23)+' 23:59:59' --当月最后一天
-- 检查查询条件
IF @HostName = '' AND @HostCoordinate = '' AND @Mouth IS NULL
BEGIN
SET @I_ReturnMessage = 'ServerMessage:请选择查询条件:设备品牌/设备产线/月份!'
RETURN -1
end
--产线不能为空
IF ISNULL(@HostCoordinate,'')=''
BEGIN
SET @I_ReturnMessage = 'ServerMessage:请选择设备产线!'
RETURN -1
end
--日期不能为空
IF ISNULL(@Mouth,'')=''
BEGIN
SET @I_ReturnMessage = 'ServerMessage:请选择需要查询几月的数据!'
RETURN -1
end
--查询1月数据
IF @Mouth='01'
BEGIN
SET @Mouth='01'
SET @Year=cast(datepart(year,GETDATE()) as nvarchar(50))
SET @tablename='DataCsv'+@Year+@Mouth
--SET @Sql='SELECT * FROM '+@tablename+' ORDER BY DetectionTime ASC'
--SET @Sql='SELECT * FROM '+@tablename+' WHERE DetectionTime BETWEEN '''+CONVERT(VARCHAR(30),@firstday,21)+''' AND '''+CONVERT(VARCHAR(30),@lastday,21)+''' ORDER BY DetectionTime ASC'
SET @Sql='SELECT SYDeviceHost.HostNumber, SYDeviceHost.HostName, SYDeviceHost.HostCoordinate,
'+@tablename+'.DetectionTime, '+@tablename+'. ProductQRCode,'+@tablename+'.ProductCode,
'+@tablename+'.TestTtems, '+@tablename+'. RelativeVUnit,
'+@tablename+'.AbsoluteVUnit, '+@tablename+'. UpperLimit,
'+@tablename+'.LowerLimit, '+@tablename+'. NominalValue,
'+@tablename+'.RelatValue, '+@tablename+'. AbsoluteValue,
'+@tablename+'. States
FROM '+@tablename+' ,SYDeviceHost
WHERE SYDeviceHost.HostCoordinate ='''+@HostCoordinate+''' and SYDeviceHost.HostNumber='+@tablename+'. HostNumber ORDER BY DetectionTime ASC'
EXEC (@Sql)
END
--查询2月数据
IF @Mouth='02'
BEGIN
SET @Mouth='02'
SET @Year=cast(datepart(year,GETDATE()) as nvarchar(50))
SET @tablename='DataCsv'+@Year+@Mouth
SET @Sql='SELECT SYDeviceHost.HostNumber, SYDeviceHost.HostName, SYDeviceHost.HostCoordinate,
'+@tablename+'.DetectionTime, '+@tablename+'. ProductQRCode,'+@tablename+'.ProductCode,
'+@tablename+'.TestTtems, '+@tablename+'. RelativeVUnit,
'+@tablename+'.AbsoluteVUnit, '+@tablename+'. UpperLimit,
'+@tablename+'.LowerLimit, '+@tablename+'. NominalValue,
'+@tablename+'.RelatValue, '+@tablename+'. AbsoluteValue,
'+@tablename+'. States
FROM '+@tablename+' ,SYDeviceHost
WHERE SYDeviceHost.HostCoordinate ='''+@HostCoordinate+''' and SYDeviceHost.HostNumber='+@tablename+'. HostNumber ORDER BY DetectionTime ASC'
EXEC (@Sql)
END
--查询3月数据
IF @Mouth='03'
BEGIN
SET @Mouth='03'
SET @Year=cast(datepart(year,GETDATE()) as nvarchar(50))
SET @tablename='DataCsv'+@Year+@Mouth
SET @Sql='SELECT SYDeviceHost.HostNumber, SYDeviceHost.HostName, SYDeviceHost.HostCoordinate,
'+@tablename+'.DetectionTime, '+@tablename+'. ProductQRCode,'+@tablename+'.ProductCode,
'+@tablename+'.TestTtems, '+@tablename+'. RelativeVUnit,
'+@tablename+'.AbsoluteVUnit, '+@tablename+'. UpperLimit,
'+@tablename+'.LowerLimit, '+@tablename+'. NominalValue,
'+@tablename+'.RelatValue, '+@tablename+'. AbsoluteValue,
'+@tablename+'. States
FROM '+@tablename+' ,SYDeviceHost
WHERE SYDeviceHost.HostCoordinate ='''+@HostCoordinate+''' and SYDeviceHost.HostNumber='+@tablename+'. HostNumber ORDER BY DetectionTime ASC'
EXEC (@Sql)
END
-----(月份可以自己加,动态表名也可拼接,这样就用一个SQL语句即可,根据自身需求修改)--
END