business-86-101-185-173.business.broadband.hu/business-86-101-185-173.business.broadband.hu.zip/business-86-101-185-173.business.broadband.hu/SensorProject.ZIP/SensorDataProcessing.sql

Text File | 2010-10-27 | 3KB | 86 lines

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SensorDataProcessing]
AS
BEGIN
    SET NOCOUNT ON;
    declare @value decimal(5,2)
    declare @id int
    declare @cnt int
    declare @date datetime
    declare @alertdate datetime
    declare @message nvarchar(160)
    declare @alertsent bit
    declare @roundedvalue int
    declare @reccount int
    declare @min int
    declare @minalertsent bit
    declare @tsg varchar(30)
    set @tsg = 'TSG'

    -- clear alerts
    UPDATE dbo.SensorConfig SET alertsent = 1

    -- process collected temperature data
    SET @cnt = 0
    SET @roundedvalue = 0
    SET @reccount = 0
    DECLARE curData CURSOR FOR
    SELECT [id],[date],[value] FROM dbo.SensorData WHERE [processed] = 0 ORDER BY [date] ASC
    OPEN curData
    FETCH NEXT FROM curData
    INTO @id, @date, @value
    WHILE @@FETCH_STATUS = 0 BEGIN
        SET @reccount = @reccount + 1
        IF @roundedvalue <> FLOOR(@value) BEGIN
            SET @cnt = 0
            SET @roundedvalue = FLOOR(@value)
        END
        SET @cnt = @cnt + 1
        IF @cnt > 1 BEGIN
            -- the last two record must equal for updating the table to avoid false jumping
            UPDATE dbo.SensorConfig SET [alertsent] = 0, [alertdate] = @date, [actualvalue] = @value WHERE @value between [value1] and [value2] and [alertsent] = 1
        END
        -- clear processed flag
        UPDATE dbo.SensorData SET [processed] = 1 WHERE [id] = @id
        FETCH NEXT FROM curData INTO @id, @date, @value
    END
    CLOSE curData
    DEALLOCATE curData
    -- the last record remains unprocessed
    UPDATE dbo.SensorData SET [processed] = 0 WHERE [id] = @id
    PRINT CAST(@reccount AS VARCHAR(5)) + ' record(s) processed.'

    -- process alerts
    SELECT TOP 1 @id = [id], @message = [message], @alertdate = [alertdate], @value = [actualvalue] FROM dbo.SensorConfig WHERE [alertsent] = 0 ORDER BY [alertdate] DESC
    IF @@ROWCOUNT>0 BEGIN
        PRINT 'Sending alert for ' + CAST(@alertdate AS VARCHAR(20)) + ' ' + @message
        PRINT CAST(@value AS VARCHAR(5)) + ' Celsius degree'
        SELECT @roundedvalue = ISNULL(value,0), @alertsent = ISNULL(alertsent,0), @min = minvalue, @minalertsent = minalertsent FROM SensorAlert WHERE id = 1
        PRINT 'Last SMS sent for ' + CAST(@roundedvalue AS VARCHAR(5)) + ' Celsius degree'
        IF @roundedvalue <> FLOOR(@value) BEGIN
            IF FLOOR(@value)<=@min BEGIN
                IF @minalertsent = 1 BEGIN
                    PRINT 'No SMS alert needed, it was sent already.'
                END ELSE BEGIN
                    PRINT 'Sending SMS...'
                    UPDATE dbo.SensorAlert SET value = FLOOR(@value), alertsent = 1, alertdate = @alertdate, minalertsent = 1 WHERE id = 1
                    EXEC dbo.SendSMS @tsg, @message, 'EUR6MTO'
                END
            END
            ELSE BEGIN
                PRINT 'Sending SMS...'
                UPDATE dbo.SensorAlert SET value = FLOOR(@value), alertsent = 1, alertdate = @alertdate, minalertsent = 0 WHERE id = 1
                EXEC dbo.SendSMS @tsg, @message, 'EUR6MTO'
            END
        END
        UPDATE dbo.SensorConfig SET [alertsent] = 1 WHERE [alertsent] = 0
    END
END
GO