Telegram bots and sending messages to Telegram

How to set up a bot Telegram 

Creating a Bot in Telegram 

1. Find the bot BotFather - > command /newbot - > give it a description, name (ending in Bot) and picture. (more detailed- http://aftamat4ik.ru/pishem-bota-telegram-na-c/). As a result, we get token

2. On web.config / AppSettings configure the following parameters: 

    1. proxyUrl - specify the proxy for the Telegram in this form http://45.89.19.84:11498 (if you don't need a proxy, just leave the fields empty)
    2. proxyUser, proxyPassword - specify the proxy user and password. 
    3. telegramToken - specify the token that we received when creating the bot in Telegram (в botFather).
    4. telegramOnlyTextMessages - if 1, only text messages will be processed by the bot.

3. In the as_users table, add 2 fields:

IF COL_LENGTH( 'as_users', 'telegram') IS NULL BEGIN
	ALTER TABLE as_users ADD telegram nvarchar ( 128 )  NULL
END

IF COL_LENGTH( 'as_users', 'telegramChatID') IS NULL
BEGIN
   ALTER TABLE as_users ADD telegramChatID int NULL
END

4. Creating a stored procedure of this type (in the System SQL section/ Telegram Bot): 

ALTER PROCEDURE [dbo].[telegram_bot_action]

@parameters DictionaryParameter READONLY,  -- incoming parameters for internal
                                              processings
@text nvarchar(max),
@messageID int,
@chatID int,
@messageType nvarchar(128),
@updateType nvarchar(128),
@telegramUsername nvarchar(256),
@firstName nvarchar(256)

AS
BEGIN
-- select 1 - result
    -- select 2 - command  select  'sendMessage' [Type], @response [Text], @chatID ChatId, @messageID ReplyToMessageId,	0 DisableNotification
    -- select 3 - additional buttons  select '' Text, '' Url, '' CallbackData



DECLARE @userID int
SELECT @userID = id FROM as_users WHERE telegram = @telegramUsername and telegramChatID IS NULL

if(@userID is not null) BEGIN

UPDATE as_users SET telegramChatID = @chatID
WHERE id= @userID

END


if(lower(@text)='/tablecount') BEGIN

SELECT 1 Result, '' Msg
SELECT   'sendMessage' [Type], '123' [Text], @chatID ChatId, 
         @messageID ReplyToMessageId,0 DisableNotification
RETURN
END

if(lower(@text)='/hello') BEGIN

SELECT 1 Result, '' Msg
SELECT 'sendMessage' [Type], 'Привет ' + @telegramUsername+ ' / '+ @firstName [Text], 
       @chatID ChatId,@messageID ReplyToMessageId, 0 DisableNotification
RETURN
END


if(lower(@text)='/start') BEGIN

SELECT 1 Result, '' Msg
SELECT 'sendMessage' [Type], 'Привет ' + @telegramUsername+ ' / '+ @firstName [Text], 
       @chatID ChatId, @messageID ReplyToMessageId, 0 DisableNotification
RETURN
END

SELECT 0 Result, '' Msg
END

At the entrance: 

  • @parameters - various additional parameters (will be expanded later)
  • @text - text from the telegram user
  • @messageID - ID of the message from the user (you can use it to specify reply)
  • @chatID - ID of the chat that we will then send the response to.
  • @messageType - type of the message
  • @updateType - type of update received from the bot's hook
  • @telegramUsername - username of the user in telegram (for us this is  the ID)
  • @firstName - user name

At the output: 

  • SELECT 1  - Result и Msg 
  • SELECT 2 - a set of commands for telegram (relevant if SELECT1.Result=1).  Type, Text, ChatID, replyMessageID, DisableNotification, Channel, IsReplyButtons
  • SELECT 3 - set of buttons Text, Url, CallbackData
select  'sendMessage' [Type],  -- type of the command

'Привет ' + @telegramUsername+ ' / '+ @firstName   [Text], -- text of rhe command

@chatID ChatId, -- which room to send it to

@messageID ReplyToMessageId, -- if not NULL, which message is answered

0 DisableNotification, -- Notify the user or not. 

'' channel -- if you need to send to a certain channel, then write here. 

5. We check that the bot is working. Subscribe to it in telegram and execute /start (we will get a response). ). If there is no response, then the bot is not running. Try restarting it in the System SQL section (Start telegram bot).

6. To sign up a user for certain actions, you must first specify your telegram in the profile (as_users.telegram field). Then it should make any command in the telegram, sending the bot for example /start

Storing the chat binding to the system user

In as_users there are 2 fields: telegram and telegramChatID. At the first request, the system will update the telegramChatID field for the corresponding user with the specified telegram via a stored procedure. After that it will be able to receive messages from telegram. 

Note: 

  1. For a proxy, you can use IPv4 and IPv6 
  2. You can buy a proxy here - https://proxy.market/
  3. 2 telegram and telegramChatID fields were added to as_users, and the stored procedure [as_user_getUser] was added (check that they are in your database). 
  4. For an external action of the telegram type to work the system must have a stored procedure  as_user_getUser
Create PROCEDURE [dbo].[as_user_getUser]
  @username nvarchar(32)
AS
BEGIN
  select top 1 * from as_users where username = @username
END

The use of the bot

  • In telegram, we find a bot (for example, our bot FalconSpaceBot)
  • Write it /start for initialization (your user is linked to the chat). 
  • We run various commands, for example /getupdates - if the command is registered in the chat, it will be processed and a response will be issued. 

Enabling / disabling the bot.

The bot is enabled when the entire web application is launched (Global.asax/app_start). 

To disable it manually, you can use the command (go to URL) /Controls/Stuff/StopTelegramBot

For inclusion  - /Controls/Stuff/StartTelegramBot

The implementation details of the bot

It is implemented as a separate thread that rotates endlessly and waits for updates to be received from the bot. If there is an update, it starts the procedure for processing this update (messages in it)

Note: 

  • To translate a line in a bot message, use Char(10);

Problems with the bot Telegram 

The bot doesn't work

  1. Check that the proxy is registered in web.config
  2. Check that the proxy is not expired
  3. Check that the unit as a whole is responsible.to your commands in telegram (if it doesn't respond)
  4. Try restarting the bot /Controls/Stuff/StartTelegramBot (or restart the application pool in IIS)
  5. Check as_trace (Tg and Exception codes). It writes errors and initialization and termination of the bot thread. 
  6. Make as_print in the procedure and see if the bot gets there. 
  7. The bot can stop if you are running local debugging (in this case, there is a conflict of updates, so you need to disable the bot at local startup - remove the token in web. config).
  8. The bot may crash if the IIS pool is turned off automatically. You must configure it so that it does not turn off when it is idle. 

How to send a message to a user in Telegram

To do this, we use an External action (in forms and other elements) with the code telegram. 

Example: 

select 'telegram' type, 'admin' [to], 'Some message text' [text]

You can also send a message via notifications (if Immediate notification is enabled). 

The user must have correctly entered the fields telegram (entered first manually for the user's username) and telegramChatID(entered automatically when the user first accesses the bot via Telegram)  in as_users 

How to send a message to the Telegram channel

To do this, we use an External action (in forms and other elements) with the code telegram. 

Example: 

select 'telegram' type, '' [to], 'falconspace' channel, 'Some message text' [text]

The bot must be added by the administrator to the channel. 

How to send a newsletter to users when saving a form.

Specify all users in SELECT 2 + link them to the display settings for this message (i.e. show them to the themes. who requested sending this type of notification. The settings fields may change). Example: 

select 'telegram' type, username [to],
    	'Update Falcon Space: ' + isnull(@fieldname, '')
    	+ char(10) + isnull(@fielddesc, '') [text]
    from as_users where telegram in (select telegram from tg_settings where sendUpdates=1)

How to work with Telegram buttons

Specifying all users in SELECT 2 + associating them with the display settings for this message (i.e. showing them to the themes. who requested sending this type of notification. Fields

Buttons can be either Inline(in the chat when responding) or Reply (below the chat). This is set via the IsReplyButtons parameter in THE select 2 Bot. 

Buttons are passed to SELECT 3 Bots: 

  • Text - the name of the button. 
  • CallbackData-data to pass to the procedure< / li> < li>Url - if specified, the button will open the specified address. 

For reply buttons, the command is used in the text field. The callbackData field for reply buttons passes the level (when you need to output buttons in several rows) at which the button is output (from 1 to 10).  

The callbackData field will be passed to the procedure for inline buttons as a command, and the text field for reply buttons.< / p>

Additional information about the buttons Telegram.: 

https://groosha.gitbook.io/telegram-bot-lessons/chapter8

https://aftamat4ik.ru/pishem-bota-telegram-na-c/#toc-inline-8

Using Emojis in messages

Using this table https://apps.timwhitlock.info/emoji/tables/unicode

Select a symbol and take its code to the symbol page UTF16 LE

For example, for https://apps.timwhitlock.info/unicode/inspect/hex/1F601 это будет код 3DD801DE

In SQL we use this code as follows: 

CAST(0x3DD801DE AS NVARCHAR(MAX))

Multi-stage commands in the bot

To do this, use the following fields in tg_settings:

  1. currentCommand - current work command (where the user enters data).
  2. currentCommandStep - current step in the command (to determine which text to output within the command). 
  3. currentCommandData - this is where data is accumulated for executing the command.

Sample code for this command: 

CREATE PROCEDURE [dbo].[telegram_bot_action]
	@parameters DictionaryParameter READONLY,  -- incoming parameters for internal processing
	@text nvarchar(max),
	@messageID int,
	@chatID int,
	@messageType nvarchar(128),
    @updateType nvarchar(128),
	@telegramUsername nvarchar(256),
	@firstName nvarchar(256)
AS
BEGIN
	-- select 1 - result
    -- select 2 - comand  select  'sendMessage' [Type], @response [Text], @chatID ChatId, @messageID ReplyToMessageId,	0 DisableNotification
    -- select 3 - additional buttons  select '' Text, '' Url, '' CallbackData

    -- static menu buttons
	declare @mnuUpdates nvarchar(128) = CAST(0x3CD895DD AS NVARCHAR(MAX)) + ' Update',
    	@mnuHelp nvarchar(128) = CAST(0x5327 AS NVARCHAR(MAX)) + ' Help',
        @mnuConcept nvarchar(128) = CAST(0x3DD84BDC AS NVARCHAR(MAX)) + ' Project development'


	declare @request nvarchar(max)=@text, @response nvarchar(max)='',
    	@clearCommand bit = 1, -- indicates that you need to clear the current command for the user (set to false. when we run a multi-step multi-command)
        @currentCommand nvarchar(256), @currentCommandStep int, @currentCommandData nvarchar(max), -- current command (when the command is executed in several actions)
        @found bit = 0,   -- indicates that the command was found
		@tgSettingID int,  -- id the settings of the user from tg_settings
    	@userID int	-- id of the associated user from as_users
	/* PRINT...
	declare @temp nvarchar(128)
    set @temp = cast(@chatID as nvarchar) + '__'+ @telegramUsername
    exec as_print @str= @temp
    */
    set @telegramUsername = lower(@telegramUsername)


	select top 1 @userID = id from as_users where lower(telegram) = @telegramUsername  and telegramChatID is null
	if(@userID is not null) begin
		update as_users set telegramChatID = @chatID
		where id= @userID
	end


	select @tgSettingID = id, @currentCommand = currentCommand, @currentCommandStep = currentCommandStep, @currentCommandData = currentCommandData
    from tg_settings where lower(telegram) = @telegramUsername

    if(@tgSettingID is null) begin
		insert into tg_settings (telegram, chatID, name, sendUpdates, sendAdvices)
		values (@telegramUsername, @chatID, @firstName, 1, 1)

		set @tgSettingID = scope_identity()
	end


    -- |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
	if(lower(@text)='/start')
	begin
		set @response = 'Привет ' + @telegramUsername+ ' / '+ @firstName + ' ' + CAST(0x3DD801DE AS NVARCHAR(MAX))

		select 1 Result, '' Msg
		select  'sendMessage' [Type],
			@response   [Text],
			@chatID ChatId,
			@messageID ReplyToMessageId,
			1 DisableNotification,
            1 IsReplyButtons
           -- select 3 - кнопки дополнительные  select '' Text, '' Url, '' CallbackData
		select @mnuUpdates Text, '' Url, '1' CallbackData
        union
        select @mnuHelp Text, '' Url, '1' CallbackData
        union
        select @mnuConcept Text, '' Url, '2' CallbackData

        set @found = 1

	end
    -- |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
	if(lower(@text) =lower(@mnuConcept) or lower(@currentCommand)=lower(@mnuConcept))
	begin
    	if(isnull(@currentCommandStep,0)=0) begin
        	set @response ='I suggest that we agree on the place and time to discuss the concept of your project. '+char(10)+ CAST(0x3DD8DEDC AS NVARCHAR(MAX)) + ' Specify your contact (the most convenient way to do this is via whatsapp, telegram, or Skype):'
			-- we do not process it here

            set @clearCommand=0
        end
    	if(@currentCommandStep=1) begin
        	set @response =CAST(0x3DD852DD AS NVARCHAR(MAX)) + ' Specify a convenient time for discussion:'
            update tg_settings set currentCommandData=isnull(currentCommandData, '') + @text + ', '
        	where id = @tgSettingID

            set @clearCommand=0
        end
    	if(@currentCommandStep=2) begin
        	set @response =CAST(0x3DD8C4DC AS NVARCHAR(MAX)) + ' If you have an initial brief description of the project, please write here:'
            update tg_settings set currentCommandData=isnull(currentCommandData, '') + @text + ', '
        	where id = @tgSettingID

            set @clearCommand=0
        end

  		if(@currentCommandStep=3) begin
        	set @response =CAST(0x3CD8C1DF AS NVARCHAR(MAX)) + ' Thank you for your message! Have a nice day! ' + CAST(0x3DD80ADE AS NVARCHAR(MAX))
            update tg_settings set currentCommandData=isnull(currentCommandData, '') + @text + ', '
        	where id = @tgSettingID


            declare @notifyText nvarchar(max) =  'ЗThe entry on the concept of the telegram: ' +  isnull((select top 1 currentCommandData from tg_settings where id = @tgSettingID), '')
            exec [dbo].[as_nt_createNotification]
               @additional = '',
               @from = 'telegram',
               @to = 'admin',
               @url = '',
               @text =@notifyText,
               @typeCode ='client',
               @returnID =0

            set @clearCommand=1
        end

       	update tg_settings set currentCommand=@mnuConcept, currentCommandStep=@currentCommandStep + 1
        where id = @tgSettingID

		select 1 Result, '' Msg
		select  'sendMessage' [Type],
			@response [Text],
			@chatID ChatId,
			@messageID ReplyToMessageId,
			0 DisableNotification
      	set @found = 1

	end

    -- ... other commands


    -- |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
	-- Incomprehensible command
    if(@chatID>0 and @found=0) begin
		set @response = 'I don't understand you. Enter /help to view all commands'

    	select 1 Result, '' Msg
        select  'sendMessage' [Type],
			@response   [Text],
			@chatID ChatId,
			@messageID ReplyToMessageId,
			0 DisableNotification
    end

    if(@clearCommand=1)begin
    	update tg_settings set currentCommand='', currentCommandStep=0, currentCommandData = ''
        where id = @tgSettingID
    end


	-- save log
	insert into tg_log(telegram, created, request, response)
	values(@telegramUsername, getdate(), @request, @response)

	select 0 Result, '' Msg

END

The example shows the full code of the bot procedure with start processing, the menu, and the business logic of the step command. The code also contains an example of how to use Emojis in a bot.

Note