---
# System prepended metadata

title: Услуги
tags: [medialog]

---

# Услуги
###### tags: `medialog`
----

**Список услуг:**
```sql
SELECT 
       id, 
       description, 
       code, 
       name, 
       price, 
       sort, 
       group_code, 
       group_id,
       extra_servs,
       cash
FROM WEB_PRICELIST
```

**Виды приема:**
```sql
select DISTINCT PL_EXAM_ID,NAME, 
(ISNULL(web_ok,0)+ISNULL(plan_ok,0)+ISNULL(med_ok,0)) as EXAM_TYPE, DUREE,
SERVS,SPECIALISATION_ID, PL_SUBJ_ID, TREBUETSYA_BANKOVSKAYA_KA, sort
from (
    select
    PL_EXAM.PL_EXAM_ID,PL_EXAM.NAME,
    (case when isnull(pl_subj.TREBUETSYA_BANKOVSKAYA_KA,0)=1 or isnull(PL_EXAM.TREBUETSYA_BANKOVSKAYA_KA,0)=1 then 1 else 0 end) as TREBUETSYA_BANKOVSKAYA_KA,
    1 as web_ok,
    (select
    1 from PL_SUBJ_EXAM eo with(nolock) where eo.PL_subj_ID=PL_SUBJ_EXAM.PL_subj_ID and eo.PL_EXAM_ID=4074
    and PL_SUBJ_EXAM.EXAM_ORDER>eo.EXAM_ORDER) plan_ok,
    (select
    1 from PL_SUBJ_EXAM eo with(nolock) where eo.PL_subj_ID=PL_SUBJ_EXAM.PL_subj_ID and eo.PL_EXAM_ID=4075
    and PL_SUBJ_EXAM.EXAM_ORDER>eo.EXAM_ORDER) med_ok,
    PL_SUBJ_EXAM.EXAM_ORDER as sort,
    PL_EXAM.DUREE,
    case
    when PL_EXAM.FM_SERV_ID IS not null
    then convert(varchar(100),FM_SERV_ID)
    else
    left(
    ((SELECT convert(varchar(100),FM_DIR_SERV.FM_SERV_ID)+','
    FROM FM_DIR_SERV FM_DIR_SERV
    where PL_EXAM.PL_EXAM_ID=FM_DIR_SERV.PL_EXAM_ID
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')),
    LEN(
    ((SELECT convert(varchar(100),FM_DIR_SERV.FM_SERV_ID)+','
    FROM FM_DIR_SERV FM_DIR_SERV
    where PL_EXAM.PL_EXAM_ID=FM_DIR_SERV.PL_EXAM_ID
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')))-1 )
    end SERVS,
    isnull(PL_EXAM.SPECIALISATION_ID,MEDECINS.SPECIALISATION_ID) as SPECIALISATION_ID,
    PL_SUBJ_EXAM.PL_SUBJ_ID
    From PL_SUBJ_EXAM PL_SUBJ_EXAM with(nolock)
    inner join PL_EXAM PL_EXAM with(nolock) on PL_SUBJ_EXAM.PL_EXAM_ID=PL_EXAM.PL_EXAM_ID
    inner join PL_SUBJ pl_subj with(nolock) on PL_SUBJ_EXAM.PL_SUBJ_ID=PL_SUBJ.PL_SUBJ_ID
    left JOIN PL_SUROL PL_SUROL with(nolock) ON PL_SUBJ.PL_SUBJ_ID = PL_SUROL.PL_SUBJ_ID
    left JOIN PL_EXROL PL_EXROL with(nolock) ON PL_EXAM.PL_EXAM_ID = PL_EXROL.PL_EXAM_ID
    left join MEDECINS with(nolock) on MEDECINS.MEDECINS_ID = PL_SUBJ.MEDECINS_ID
    where
    isnull(PL_EXAM.REC_PLANNING,0)=1 and
    PL_SUBJ_EXAM.PL_subj_ID in
    (
    select distinct e1.PL_subj_ID from
    PL_SUBJ_EXAM e1 with(nolock)
    inner join PL_SUBJ_EXAM e2 with(nolock) on e1.PL_subj_ID=e2.PL_subj_ID
    where e1.PL_EXAM_ID=4074 and e2.PL_EXAM_ID=4075 and e1.EXAM_ORDER<e2.EXAM_ORDER
    )
    and (( PL_SUROL.PL_ROLE_ID= PL_EXROL.PL_ROLE_ID ) or (PL_EXROL.PL_ROLE_ID IS NULL))
    and PL_SUBJ_EXAM.PL_EXAM_ID not in (4074,4075)
    and isnull(PL_EXAM.DUREE,'')<>'' and isnull(PL_EXAM.REC_PLANNING, 0) = 1
    and isnull(PL_EXAM.ARCHIVE,0)<>1 and isnull(PL_SUBJ.ARCHIVE,0)<>1
) sub
order by EXAM_TYPE
```

**Категории:**
```sql
SELECT 
  FM_SERVGRP.FM_SERVGRP_ID as id, 
  FM_SERVGRP.code as code, 
  FM_SERVGRP.LABEL as name, 
  PARENT as parent_id
FROM FM_SERVGRP
WHERE isnull(FM_SERVGRP.ARCHIVE,0)<>1 AND isnull(FM_SERVGRP.NO_WEB_ACCESS, 0) != 1
ORDER BY id, parent_id
```    

**Соплатежи:**
```sql
SELECT s.fm_serv_id               AS fm_serv_id,
       s.label                    AS serv_label,
       fmsp.price                 AS serv_price,
       extra.label                AS extra_label,
       fmsp2.price                AS extra_price,
       Cast(ch.date_from AS DATE) AS extra_start,
       fm_org.code                AS clinic_code
FROM   fm_serv s
       LEFT OUTER JOIN fm_servprice AS fmsp
                    ON fmsp.fm_servprice_id = (SELECT TOP 1
                                              fm_servprice.fm_servprice_id
                                               FROM   fm_servprice
                                               WHERE  (
                                              fm_servprice.fm_pricetype_id =
                                              '113' )
                                                      AND
                                              fm_servprice.fm_serv_id =
                                              s.fm_serv_id
                                                      AND
fm_servprice.date_from <= Getdate()
 ORDER  BY fm_servprice.date_from DESC)
INNER JOIN fm_serv extra
ON extra.fm_serv_id = s.extra_serv_id
LEFT OUTER JOIN fm_servprice AS fmsp2
ON fmsp2.fm_servprice_id = (SELECT TOP 1
 fm_servprice.fm_servprice_id
  FROM   fm_servprice
  WHERE  ( fm_servprice.fm_pricetype_id =
           '113' )
         AND fm_servprice.fm_serv_id =
             extra.fm_serv_id
         AND
 fm_servprice.date_from <= Getdate()
  ORDER  BY fm_servprice.date_from DESC)
INNER JOIN ch_extra_pay ch
ON ch.fm_serv_id = extra.fm_serv_id
LEFT OUTER JOIN fm_org
ON fm_org.fm_org_id = ch.fm_org_id
WHERE  s.state = 'a'
       AND ch.archive = 0 
```

**Доплаты:**
```sql
SELECT isnull(doc.nom + ' ', '') + isnull(doc.prenom + ' ', '') + isnull(doc.PATRONYME, '') AS doc,
       fm_org.LABEL AS clinic,
       fmsp.PRICE AS price
FROM ch_doc_rate_new
LEFT OUTER JOIN FM_SERV ON FM_SERV.FM_SERV_ID = ch_doc_rate_new.fm_serv_id
LEFT OUTER JOIN fm_org ON fm_org.FM_ORG_ID = ch_doc_rate_new.fm_org_id
LEFT OUTER JOIN medecins AS doc ON doc.MEDECINS_ID = ch_doc_rate_new.medecins_id
LEFT OUTER JOIN FM_SERVPRICE AS fmsp ON fmsp.FM_SERVPRICE_ID =
  (SELECT TOP 1 fm_servprice.FM_SERVPRICE_ID
   FROM FM_SERVPRICE
   WHERE (FM_SERVPRICE.DATE_FROM <= isnull(ch_doc_rate_new.date_From, getdate()))
     AND (FM_SERVPRICE.FM_PRICETYPE_ID = '113')
     AND fm_servprice.fm_serv_id = fm_serv.fm_serv_id
   ORDER BY FM_SERVPRICE.DATE_FROM DESC)
WHERE fm_Serv.FM_SERVGRP_ID = 2025
  AND isnull(ch_doc_rate_new.date_from, getdate()) >= isnull(
                                                               (SELECT TOP 1 cdrn.date_from
                                                                FROM ch_doc_rate_new AS cdrn
                                                                WHERE cdrn.medecins_id = ch_doc_rate_new.medecins_id
                                                                  AND cdrn.fm_org_id = ch_doc_rate_new.fm_org_id
                                                                  AND isnull(cdrn.date_from, '2017-09-01') <= getdate()
                                                                  AND cdrn.ARCHIVE = 0
                                                                  AND cdrn.FM_SERV_ID = FM_SERV.FM_SERV_ID
                                                                ORDER BY cdrn.date_from DESC), '2017-09-01')
  AND (ch_doc_rate_new.week > 0
       OR ch_doc_rate_new.sat > 0
       OR ch_doc_rate_new.sun > 0)
  AND doc.ARCHIVE = 0
  AND ch_doc_rate_new.ARCHIVE = 0
ORDER BY doc,
         clinic
```         










