USE [ercproxy]
GO
/****** Object:  StoredProcedure [dbo].[spa_KSESendCommand]    Script Date: 01/26/2015 17:40:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
        Procedure for sending command to KSEs

        Created:    2014-08-12
        Creator:    Bartash
*/
ALTER PROCEDURE [dbo].[spa_KSESendCommand]
        @Command        INT             ,           -- integer value of command
                                                    --      20 - Go out of service
                                                    --      21 - Go in service
                                                    --      24 - Reload streets

        @TerminalId     INT     = NULL,             -- if NULL - All M-boxes
        @ForceIN        INT     = 1                 -- Force to update state
AS
BEGIN
--SET NOCOUNT ON

IF      object_id('tempdb..#KSECommandList') IS NOT NULL
    DROP TABLE  #KSECommandList

CREATE TABLE    #KSECommandList (
                TerminalId      INT)

IF  @TerminalId IS NULL                             -- All terminals
    INSERT INTO     #KSECommandList (TerminalId)
        SELECT      Id
        FROM        dbo.Shops
        WHERE       Id > 16777000
        AND         Id < 16777999
        AND         STATUS = 1
ELSE                                                -- Only one terminal
    INSERT INTO     #KSECommandList (TerminalId)
        SELECT      @TerminalId

DECLARE     @TermId     INT                         -- For terminal Id

DECLARE     curTerm     CURSOR FOR
    SELECT  TerminalId
    FROM    #KSECommandList

OPEN    curTerm

FETCH NEXT FROM curTerm INTO @TermId

DECLARE     @State      VARCHAR(60)

WHILE @@FETCH_STATUS = 0
BEGIN
    IF  @ForceIN = 0    -- Do not force to go IN!
    BEGIN
    SELECT      @State = Info
    FROM        KSEStates
    WHERE       Updated IN (
                            SELECT      MAX(Updated)
                            FROM        KSEStates
                            WHERE       TerminalId  = @TermId
                            AND         StatusId    = 3)
            AND TerminalId = @TermId  -- типо наверное вот так нужно
            AND StatusId = 3

        IF @@ROWCOUNT <> 1  -- [2015-01-26][Bartash] We need to get only one row in here!
    BEGIN
        RAISERROR('More than one row of State detected!', 5,1)
        GOTO FINAL
    END

    IF @State = 1       -- It went OUT!
        GOTO NEXT_STEP
    END


    INSERT INTO ##KSECommandsRT (TerminalId, CommandId, Command, Updated)
    VALUES (@TermId, @Command, 0, getdate())

NEXT_STEP:
    FETCH NEXT FROM curTerm INTO @TermId
END

FINAL:

CLOSE curTerm
DEALLOCATE curTerm

END