# Услуги
###### 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
```
Sign in with Wallet
Connect another wallet