falcon_yandexturbo

CREATE OR ALTER PROCEDURE [dbo].[falcon_yandexturbo]
  @parameters DictionaryParameter READONLY,  -- as_domain - передача домена
  @s nvarchar(max) output
AS
BEGIN

declare @page int = (select try_cast(Value as int) from @parameters where  [key] = 'page')
if(@page is null) set @page = 1
declare @pageSize int = 40

declare @siteTitle nvarchar(max) = 'Falcon Space'
declare @siteLink nvarchar(max) = 'https://falcon.web-automation.ru'
declare @siteDesc nvarchar(max) = 'Веб-платформа для создания личных кабинетов на сайте'
declare @siteLang nvarchar(max) = 'ru'
declare @siteAuthor nvarchar(256) = 'Раянов Руслан'

declare @result table(link nvarchar(256) ,[date] date, topic nvarchar(max)  , author nvarchar(max), text nvarchar(max) )
declare @pagedResult table(link nvarchar(256) ,[date] date, topic nvarchar(max)  , author nvarchar(max), text nvarchar(max) )


declare @host nvarchar(256) = 'https://falcon.web-automation.ru'

insert into @result
select
    'http://falcon.web-automation.ru/blog/'+ code link,
    updated date,
    title topic,
    @siteAuthor author,
    text1 text
from as_articles

insert into @pagedResult
select * from @result
order by date
OFFSET @PageSize * (@Page - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;


/*insert into @result
select 'http://falcon.web-automation.ru/blog' link,
    getdate() date,
    'Некий заголовок страницы' topic,
    'author1' author,
    '<h1>Некий заголовок</h1>text1' text
union
select 'http://falcon.web-automation.ru' link,
    getdate() date,
    'А тут тоже заголовок' topic,
    'author2' author,
    '<h1>Некий заголовок</h1>text2' text
    */
set @s = '<?xml version="1.0" encoding="UTF-8"?>
<rss xmlns:yandex="http://news.yandex.ru"
     xmlns:media="http://search.yahoo.com/mrss/"
     xmlns:turbo="http://turbo.yandex.ru"
     version="2.0">' +char(10)
set @s = @s + ' <channel>' +char(10)+'
        <title>'+@siteTitle+'</title>' +char(10)+'
        <link>'+@siteLink+'</link>' +char(10)+'
        <description>'+@siteDesc+'</description>' +char(10)+'
        <language>'+@siteLang+'</language>' +char(10)

declare @p ExtendedDictionaryParameter
insert into @p(Value2)
select '
        <item turbo="true">
            <turbo:extendedHtml>true</turbo:extendedHtml>
            <link>'+isnull(link, '')+'</link>
            <turbo:source></turbo:source>
            <title>'+isnull(topic, '')+'</title>
            <turbo:topic>'+isnull(topic, '')+'</turbo:topic>
            <pubDate>'+cast([date] as nvarchar)+'</pubDate>
            <author>'+isnull(author, '')+'</author>

            <yandex:related></yandex:related>
            <turbo:content>
                <![CDATA[
                   '+isnull([text], '')+'
                ]]>
            </turbo:content>
        </item>
' Value2
from @pagedResult

set @s  = @s + isnull(dbo.as_agg(@p, ''), '')

set @s  = @s + '</channel>' + dbo.as_newLine(1)

set @s  = @s + '</rss>'+ dbo.as_newLine(1)

END