use [ch_spravmedika_2]
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET LANGUAGE Russian

declare @stat_inet_UrlReferrer varchar(max) = '';
declare @stat_inet_params varchar(max) = '';
declare @stat_inet_path varchar(250) = '/catalog/laboratornye-analizy/analizy-otdelyaemogo-mochepolovyx-organov';
declare @stat_inet_host varchar(250) = 'chelny.spravmedika.ru';
declare @stat_inet_ip_n bigint = 54144055253;
declare @stat_inet_ip varchar(250) = '54.144.55.253';
declare @stat_inet_session_id_n bigint = 0;
declare @stat_inet_session_id varchar(250) = '';
declare @stat_inet_brouser varchar(max) = 'CCBot/2.0 (https://commoncrawl.org/faq/)';
declare @stat_inet_referrer varchar(max) = '';

declare @blocking_flag bit;
    declare @cnt int = 
    (select count(*)
        from ch_temp.dbo.php_blocking_ip with(nolock) where block_ip = @stat_inet_ip and block_date > getdate()
    )
    
    set @blocking_flag = (case when @cnt = 0 then 0 else 1 end);

exec common.dbo.p_add_php_stat_session
      @stat_inet_referrer,
      @stat_inet_brouser,
      @stat_inet_session_id,
      @stat_inet_ip,
      @stat_inet_host,
      @stat_inet_path,
      @stat_inet_params,
      @stat_inet_UrlReferrer,
      @blocking_flag
    
    select @blocking_flag as blocking_flag

use [ch_spravmedika_2]
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET LANGUAGE Russian

declare @filter varchar(250) = null;
declare @letter varchar(5) = null;
declare @is_top int = 1;
declare @is_mpn int = 1;
declare @international_group2_id int = 354;
declare @area_id int = 1;
declare @firm_id int = null;
declare @translate_lang_id int = 1;

set @letter = UPPER(@letter);

/* выбор алфавита */

declare @t_a table(letter varchar(5));

declare @t_a_cnt table(letter_id int, sort int, letter varchar(5), cnt int, cnt_short int);

declare @t_mpn_top table(medical_product_name_id int, php_letter_id int, php_letter_mpn_is_top int);

declare @l_cnt int;

declare @php_country_id int =
(
  select 
    ps.php_country_id 
  from
    area a with(nolock)
    inner join php_site ps with(nolock) on ps.php_site_id = a.php_site_id
  where
    a.translate_lang_id = @translate_lang_id and a.area_id = @area_id
)

----------------------------------------------------------------------------------------------
/* выборка алфавита с количеством наименований */
insert @t_a_cnt
select 
	php_letter_id,
	php_letter_sort,
	php_letter_name,
	php_letter_cnt,
	php_letter_cnt_short
from
	php_letter with(nolock)
where
	translate_lang_id = @translate_lang_id
and
        php_country_id = @php_country_id
and
	international_group2_id = @international_group2_id
and
	(@letter is null or php_letter_name = @letter)

set @l_cnt = (select SUM(cnt) from @t_a_cnt);

if @letter is null
begin
select * from @t_a_cnt order by sort asc, letter asc
end
 
if @is_mpn = 1
begin
insert @t_mpn_top
select
	medical_product_name_id,
	php_letter_id,
	php_letter_mpn_is_top
from
	php_letter_mpn plmpn with(nolock)
	inner join @t_a_cnt acnt on acnt.letter_id = plmpn.php_letter_id
where
(
    @letter is not null
or
    @filter is not null
or
    plmpn.php_letter_mpn_is_top = @is_top
)
and
    plmpn.php_country_id = @php_country_id
------------------------------------------------------------------------------------------------

/* выборка списка mpn по заданной букве */
declare @t_mpn table (
  sort int,
  letter varchar(5),
  medical_product_name_id int,
  medical_product_name_name varchar(250),
  international_name_name varchar(250),
  medical_product_name_latin varchar(250),
  presence_cnt int,
  price_min money
)

insert into @t_mpn
  select
    a_cnt.sort,
    a_cnt.letter as letter,
    mpn.medical_product_name_id,
    mpn.medical_product_name_name,
    iname.international_name_name,
    mpn.medical_product_name_latin,
    ds_mp.presence_cnt,
    case when a.usingPercentSystem = 0 or ig1.usingPercentSystem = 0 or (mpn.price_avg_calc_max > cast(a.minPrice as float)*cast(ig1.minPriceKoef as float)) then common.dbo.price_round(ds_mp.price_min) else null end as price_min
  from
    medical_product_name mpn with(nolock)
    inner join @t_mpn_top mpntop                    on mpntop.medical_product_name_id = mpn.medical_product_name_id
                                                   and (@is_top is null or mpntop.php_letter_mpn_is_top = @is_top)
    inner join @t_a_cnt a_cnt                        on a_cnt.letter_id               = mpntop.php_letter_id
    inner join international_group2 ig2 with(nolock) on ig2.translate_lang_id         = @translate_lang_id and
                                                        ig2.international_group2_id   = mpn.international_group2_id
    inner join international_group  ig1 with(nolock) on ig1.translate_lang_id         = @translate_lang_id and
                                                        ig1.international_group_id    = ig2.international_group_id
    inner join area                   a with(nolock) on a.translate_lang_id           = @translate_lang_id
                                                    and a.area_id                     = @area_id
    left join international_name iname with(nolock)  on iname.translate_lang_id       = @translate_lang_id and
                                                        iname.international_name_id   = mpn.international_name_id
-------------------------------------------------------------------------------------------------------------------------                                                          
/* динамическая часть */
    left  join php_a_mpn            ds_mp            on ds_mp.area_id                 = @area_id and
                                                        ds_mp.firm_id = isnull(@firm_id, -1) and
                                                        ds_mp.medical_product_name_id = mpn.medical_product_name_id
-------------------------------------------------------------------------------------------------------------------------                                                        
    left  join @t_a                 t_a              on t_a.letter                    = UPPER(SUBSTRING(mpn.medical_product_name_name, 1, 1))
  where
    (
    @filter is null 
  or 
    mpn.medical_product_name_name like ('%' + @filter + '%')
    )
  and
    mpn.translate_lang_id = @translate_lang_id
  and
    mpn.php_country_id = @php_country_id
  and
    mpn.international_group2_id = @international_group2_id
  order by 
    sort asc, mpn.medical_product_name_name asc

--------------------------------------------------------

select 'mpn' as query, * from @t_mpn

end

use [ch_spravmedika_2] SET ANSI_NULLS ON SET ANSI_WARNINGS ON SET LANGUAGE Russian SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_PADDING ON exec dbo.p_getNewHexId " area_id="1" lang="ru"xmlns="http://www.w3.org/1999/xhtml"> Справмедика: Анализы отделяемого мочеполовых органов в Набережных Челнах

Удобный поиск лекарств в приложении «Справмедика»

Анализы отделяемого мочеполовых органов

Фильтр по алфавиту:

ООО «Справмедика»
423824, г. Набережные Челны, ул. Машиностроительная, 91 (IT-парк), офис B305
Политика обработки персональных данных

статистика спроса по г. Набережные Челны:

6770 / сутки

Расширенная статистика

Личный кабинет для юрлиц

Вся информация на сайте - справочная.

Перед применением лекарственных препаратов консультируйтесь с врачом.

use [ch_spravmedika_2]
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET LANGUAGE Russian

declare @router_name varchar(250) = 'catalog\ig1\ig2';
declare @host varchar(250) = 'chelny.spravmedika.ru';
declare @port int = 80;

exec common.dbo.p_add_php_stat_sql
  @port,
  @host,
  @router_name,
  'getParentHost',
  2

exec common.dbo.p_add_php_stat_sql
  @port,
  @host,
  @router_name,
  'getArea',
  6

exec common.dbo.p_add_php_stat_sql
  @port,
  @host,
  @router_name,
  'getHorisontalMenu',
  915

exec common.dbo.p_add_php_stat_sql
  @port,
  @host,
  @router_name,
  'getMainMenu',
  5

exec common.dbo.p_add_php_stat_sql
  @port,
  @host,
  @router_name,
  'getTopMenu',
  5

exec common.dbo.p_add_php_stat_sql
  @port,
  @host,
  @router_name,
  'getAreaPhones',
  5

exec common.dbo.p_add_php_stat_sql
  @port,
  @host,
  @router_name,
  'getLatinPrm',
  7

exec common.dbo.p_add_php_stat_sql
  @port,
  @host,
  @router_name,
  'getLatinPrm',
  5

exec common.dbo.p_add_php_stat_sql
  @port,
  @host,
  @router_name,
  'getLatinPrm',
  9

exec common.dbo.p_add_php_stat_sql
  @port,
  @host,
  @router_name,
  'getLatinPrm',
  922

exec common.dbo.p_add_php_stat_sql
  @port,
  @host,
  @router_name,
  'getLatinPrm',
  3

exec common.dbo.p_add_php_stat_sql
  @port,
  @host,
  @router_name,
  'getPhpPageListParam',
  26346

use [ch_spravmedika_2]
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET LANGUAGE Russian

declare @router_name varchar(250) = 'catalog\ig1\ig2';
declare @host varchar(250) = 'chelny.spravmedika.ru';
declare @port int = 80;

exec common.dbo.p_add_php_stat_php
  @port,
  @host,
  @router_name,
  'initModule1',
  337,
  2

exec common.dbo.p_add_php_stat_php
  @port,
  @host,
  @router_name,
  'getParentHost',
  328,
  2

exec common.dbo.p_add_php_stat_php
  @port,
  @host,
  @router_name,
  'initModule2',
  29,
  936

exec common.dbo.p_add_php_stat_php
  @port,
  @host,
  @router_name,
  'getMainMenu',
  12,
  5

exec common.dbo.p_add_php_stat_php
  @port,
  @host,
  @router_name,
  'initModule3',
  640,
  27292

exec common.dbo.p_add_php_stat_php
  @port,
  @host,
  @router_name,
  'getLatinPrm',
  638,
  946

exec common.dbo.p_add_php_stat_php
  @port,
  @host,
  @router_name,
  'render',
  12,
  0

exec common.dbo.p_add_php_stat_php
  @port,
  @host,
  @router_name,
  'initModuleHead',
  7,
  0

use [ch_spravmedika_2]
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET LANGUAGE Russian

declare @php_time float = 29362;
declare @sql_time float = 28230;
declare @sql_count int = 12;
declare @user_agent varchar(250) = ':CCBot/2.0 (https://commoncrawl.org/faq/)';
declare @router_name varchar(250) = 'catalog\ig1\ig2';
declare @url varchar(2000) = 'chelny.spravmedika.ru/catalog/laboratornye-analizy/analizy-otdelyaemogo-mochepolovyx-organov';
declare @refferer varchar(2000) = '';
declare @ip varchar(250) = '54.144.55.253';
declare @host varchar(250) = 'chelny.spravmedika.ru';
declare @port int = 80;

exec common.dbo.p_add_php_stat_page 
  @port,
  @host,
  @ip,
  @refferer,
  @url,
  @router_name,
  @user_agent,
  @sql_count,
  @sql_time,
  @php_time
  
-- дополнительная проверка наглых парсеров
if 
  @user_agent = ':Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E)'
begin
  declare @mm_cnt int = (
  select MAX(t.cnt) from (
  select 
    DATEPART(minute, tsdate) as mm,
    COUNT(*) as cnt
  from ch_temp..php_stat_page with(nolock) where IP = @ip and tsdate > dateadd(minute, -3, getdate())
  group by DATEPART(minute, tsdate)
  ) t )
  if @mm_cnt > 10 --открывает больше 10 страниц в минуту
  begin
    declare @c int = (select COUNT(*) from ch_stat..php_stat_ip_permitted with(nolock) where IP = @ip)
    if @c=0
    begin
      insert into ch_stat..php_stat_ip_permitted(IP,create_date,comment,ok_flag,ban_flag)
      values(@ip,GETDATE(),'Атоматическое добавление наглого парсера',0,1)
    end
  end
end