ВАЖНО!!! При указании в качестве идентификатора устройства ip-адреса нужно удалять начальные нули, т.е. вместо “010.100.019.027” следует использовать “10.100.19.27”

Usage: D:\Work\itrium_utility\release\itrium_utility [options]

Options:

  -?, -h, --help                       Displays this help.

  --itrium-server <host or ip>         Itrium database server.

  --itrium-database <database name>    Itrium database.

  --itrium-username <username>         Itrium username.

  --itrium-password <password>         Itrium password.

  --itrium-lastmessageid <MessageId>   Itrium last message id – номер последнего полученного сообщения ИТРИУМ

  --biosmart-server <host or ip>       Biosmart database server.

  --biosmart-port <port>               Biosmart database port.

  --biosmart-database <database name>  Biosmart database.

  --biosmart-username <username>       Biosmart username.

  --biosmart-password <password>       Biosmart password.

  --biosmart-objecttype <object_type>  Biosmart object type. – тип контроллера

  --biosmart-paramtype <param_type>    Biosmart param type. – параметр контроллера, содержащий его ip-адрес

  --biosmart-logging <true/false>      Do logging process. –true, чтобы добавить сообщения в журнал устройств.

UPD v.1.4

  --itrium-inIds <comma separated list>   Itrium input source list – список id контроллеров на вход.

  --itrium-outIds <comma separated list>  Itrium output source list – список id контроллеров на выход.

  --appname <string>                      Registy branch name – обязательный параметр имя приложения

USE [keeperMSSQL_20190625_2355]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[biosmartGetLastMessages]

    (@MessageId INT)

AS

BEGIN

    -- находим номер карты

    select b.id, b.code, b.code_name, b.sourser, b.sourser_name, b.destination, b.ip, b.origin, b.parameter1, b.pass_id, b.pass_name, b.card, a.property_value as card_number

    from [keeperMSSQL_20190625_2355].[dbo].[ta_string_property] as a

    inner join (

        -- находим карту

        select b.id, b.code, b.code_name, b.sourser, b.sourser_name, b.destination, b.ip, b.origin, b.parameter1, b.pass_id, b.pass_name, a.property_value as card

        from [keeperMSSQL_20190625_2355].[dbo].[ta_integer_property] as a

        inner join (

            -- находим пропуск

            select b.id, b.code, b.code_name, b.sourser, b.sourser_name, b.destination, b.ip, b.origin, b.parameter1, a.id as pass_id, a.name_copy as pass_name

            FROM [keeperMSSQL_20190625_2355].[dbo].[ta_sys_elem] as a

            inner join (

                select b.id, b.code, b.code_name, b.sourser, a.name_copy as sourser_name, b.destination, b.ip, b.origin, b.parameter1

                from [keeperMSSQL_20190625_2355].[dbo].[ta_sys_elem] as a

                inner join (

                    select b.id, b.code, a.name as code_name, b.sourser, b.destination, b.ip, b.origin, b.parameter1

                    from [keeperMSSQL_20190625_2355].[dbo].[ta_sys_message] as a

                    inner join (

                        SELECT id, code, sourser, destination, property_value as ip, origin, parameter1

                        FROM [keeperMSSQL_20190625_2355].[dbo].[ta_messages] as a

                        inner join (

                            SELECT distinct id_elem, property_value

                            FROM [keeperMSSQL_20190625_2355].[dbo].[ta_string_property]

                            where property_value = '10.100.19.32'

                                or property_value = '10.100.209.79'

                                or property_value = '10.100.209.56'

                                or property_value = '10.100.19.27'

                                or property_value = '10.100.209.183'

                            ) b

                            on a.destination = b.id_elem

                            and (a.id > @MessageId)

                            and (a.code = 1048 or a.code = 9650)

                        ) b

                    on (a.id = b.code)

                    ) b

                on  (a.id = b.sourser)

                ) b

            on a.id = b.parameter1

            ) b

        on a.id_property = 4201 and a.id_elem = b.pass_id

        ) b

    on a.id_property = 6364 and a.id_elem = b.card

    order by id

END

GO

USE [keeperMSSQL2010]

GO

/****** Object:  StoredProcedure [dbo].[biosmartGetLastMessages]    Script Date: 12/04/2019 10:39:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[biosmartGetLastMessagesByTimeAndSourceList]

        -- Add the parameters for the stored procedure here

        @MessageTime DateTime,

        @SourceList NVARCHAR(400)

AS

BEGIN

        -- SET NOCOUNT ON added to prevent extra result sets from

        -- interfering with SELECT statements.

        SET NOCOUNT ON;

        -- находим код серии

        select b.id, b.code, b.code_name, b.sourser, b.sourser_name, b.destination, b.origin, b.parameter1, b.pass_id, b.pass_name, b.card, a.property_value as card_number, b.series

        from [keeperMSSQL2010].[dbo].[ta_string_property] as a

        inner join (

                -- находим карту

                select b.id, b.code, b.code_name, b.sourser, b.sourser_name, b.destination, b.origin, b.parameter1, b.pass_id, b.pass_name, b.card, a.property_value as series

                from [keeperMSSQL2010].[dbo].[ta_integer_property] as a

                inner join (

                        -- находим пропуск

                        select b.id, b.code, b.code_name, b.sourser, b.sourser_name, b.destination, b.origin, b.parameter1, b.pass_id, b.pass_name, a.property_value as card

                        from [keeperMSSQL2010].[dbo].[ta_integer_property] as a

                        inner join (

                                select b.id, b.code, b.code_name, b.sourser, b.sourser_name, b.destination, b.origin, b.parameter1, a.id as pass_id, a.name_copy as pass_name

                                FROM [keeperMSSQL2010].[dbo].[ta_sys_elem] as a

                                inner join (

                                        select b.id, b.code, b.code_name, b.sourser, a.name_copy as sourser_name, b.destination, b.origin, b.parameter1

                                        from [keeperMSSQL2010].[dbo].[ta_sys_elem] as a

                                        inner join (

                                                select b.id, b.code, a.name as code_name, b.sourser, b.destination, b.origin, b.parameter1

                                                from [keeperMSSQL2010].[dbo].[ta_sys_message] as a

                                                inner join (

                                                        SELECT id, code, sourser, destination, origin, parameter1

                                                        FROM [keeperMSSQL2010].[dbo].[ta_messages] as a

                                                        where (a.origin > @MessageTime)

                                                                and (a.code = 1000)

                                                                and (a.sourser in (select * from [dbo].splitstring(@SourceList)))

                                                ) b

                                                on (a.id = b.code)

                                        ) b

                                        on (a.id = b.sourser)

                                ) b

                                on a.id = b.parameter1

                        ) b

                        on a.id_property = 4201 and a.id_elem = b.pass_id

                ) b

                on a.id_property = 4213 and a.id_elem = b.card

        ) b

        on a.id_property = 6364 and a.id_elem = b.card

        order by origin

END

GO

USE [keeperMSSQL2010]

GO

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )

RETURNS

 @returnList TABLE ([Name] [nvarchar] (500))

AS

BEGIN

 DECLARE @name NVARCHAR(255)

 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0

 BEGIN

  SELECT @pos  = CHARINDEX(',', @stringToSplit) 

  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList

  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)

 END

 INSERT INTO @returnList

 SELECT @stringToSplit

 RETURN

END

GO