РЕФФЕР
DECLARE @from_date VARCHAR(23) =
'2018.04.01 00:00:00.000'
DECLARE @to_date VARCHAR(23) =
'2018.04.29 00:00:00.000'
SELECT
patdirec.PATDIREC_ID,
source_bill.fm_bill_id AS source_bill_id,
PATDIREC.CREATE_DATE_TIME,
patients.N_OMON,
fm_servgrp.code AS fmservgrp_code,
FM_SERV.CODE AS fmserv_code,
fm_Serv.LABEL,
fmsp1.price,
isnull(ref_doc.nom + ' ', '') + isnull(ref_doc.prenom + ' ', '') + isnull(ref_doc.PATRONYME, '') AS ref_doc,
ref_spec.code,
branch.code_an AS branch,
FM_BILL.fm_bill_id,
FM_BILL.BILL_DATE,
isnull(doc.nom + ' ', '') + isnull(doc.prenom + ' ', '') + isnull(doc.PATRONYME, '') AS doc,
coalesce(FM_ORGGRP.CODE, 'Частное лицо') AS payor_type,
CASE WHEN
ref_spec.code <> 'Администратор' AND ref_spec.code <> 'Сайт' AND ref_spec.code <> 'Системный администратор'
AND fm_bill.BILL_DATE IS NOT NULL AND doc.MEDECINS_ID <> ref_doc.MEDECINS_ID
THEN isnull(fmsp4.price,'0.00')
ELSE 0 END * (1 + isnull(FM_CLINK.doctor_participation, 0) * isnull(fm_billdet.discount, 0) / 100) AS referral,
fmsp1.price * fm_billdet.cnt * (1 - isnull(fmsp2.price / 10000, 0)) *
(1 + isnull(fm_billdet.discount, 0) / 100) AS net_rev,
--- отсюда начнется реферрал дедушки
CASE WHEN
ref_spec_OLD.code <> 'Администратор' AND ref_spec_OLD.code <> 'Сайт' AND ref_spec_OLD.code <> 'Системный администратор'
AND fm_bill.BILL_DATE IS NOT NULL AND doc.MEDECINS_ID <> ref_doc_OLD.MEDECINS_ID and doc.MEDECINS_ID <> ref_doc.MEDECINS_ID and ref_doc_OLD.MEDECINS_ID <> ref_doc.MEDECINS_ID
THEN ISNULL(fmsp5.price,'0.00')
ELSE 0 END * (1 + isnull(FM_CLINK.doctor_participation, 0) * isnull(fm_billdet.discount, 0) / 100) AS referral_granddaddy,
isnull(ref_doc_OLD.nom + ' ', '') + isnull(ref_doc_OLD.prenom + ' ', '') + isnull(ref_doc_OLD.PATRONYME, '') AS ref_doc_granddaddy
FROM FM_SERV
LEFT OUTER JOIN FM_BILLDET WITH ( NOLOCK ) ON FM_BILLDET.FM_SERV_ID = FM_SERV.FM_SERV_ID
LEFT OUTER JOIN FM_BILL WITH ( NOLOCK ) ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID
LEFT OUTER JOIN DIR_ANSW ON DIR_ANSW.FM_BILL_ID = FM_BILL.FM_BILL_ID
LEFT OUTER JOIN PLANNING ON PLANNING.PLANNING_ID = DIR_ANSW.PLANNING_ID
LEFT OUTER JOIN PATDIREC WITH ( NOLOCK ) ON PATDIREC.PATDIREC_ID = DIR_ANSW.PATDIREC_ID
LEFT OUTER JOIN patients WITH ( NOLOCK ) ON patients.patients_id = PATDIREC.patients_id
LEFT OUTER JOIN medecins AS ref_doc ON ref_doc.MEDECINS_ID = PATDIREC.MEDECINS_CREATOR_ID
LEFT OUTER JOIN FM_SERVgrp ON FM_SERVGRP.FM_SERVGRP_ID = FM_SERV.FM_SERVGRP_ID
LEFT OUTER JOIN medecins AS doc ON doc.MEDECINS_ID = FM_BILL.MEDECINS1_ID
LEFT OUTER JOIN FM_BILL AS source_bill ON source_bill.MOTCONSU_ID = PATDIREC.MOTCONSU_ID
LEFT OUTER JOIN FM_SERVPRICE AS fmsp4 ON fmsp4.FM_SERVPRICE_ID = (SELECT TOP 1 fm_servprice.FM_SERVPRICE_ID
FROM FM_SERVPRICE
WHERE
(FM_SERVPRICE.DATE_FROM <=
fm_bill.bill_date) AND
(FM_SERVPRICE.FM_PRICETYPE_ID = '124') AND
fm_servprice.fm_serv_id =
fm_serv.fm_serv_id
ORDER BY FM_SERVPRICE.DATE_FROM DESC)
LEFT OUTER JOIN MOTCONSU ON MOTCONSU.MOTCONSU_ID = PATDIREC.MOTCONSU_ID
LEFT OUTER JOIN fm_dep ON fm_dep.fm_dep_id = coalesce(motconsu.fm_dep_id, PLANNING.CREATE_FM_DEP_ID)
LEFT OUTER JOIN fm_org AS branch ON branch.fm_org_id = fm_dep.main_org_id
LEFT OUTER JOIN specialisation AS ref_spec ON ref_spec.specialisation_id = ref_doc.specialisation_id
LEFT OUTER JOIN fm_org AS payor ON payor.fm_org_id = fm_billdet.fm_org1_id
LEFT OUTER JOIN fm_orggrp ON fm_orggrp.fm_orggrp_id = payor.fm_orggrp_id
LEFT OUTER JOIN FM_SERVPRICE AS fmsp1 ON fmsp1.FM_SERVPRICE_ID = (SELECT TOP 1 fm_servprice.FM_SERVPRICE_ID
FROM FM_SERVPRICE
WHERE
(FM_SERVPRICE.DATE_FROM <=
fm_bill.bill_date) 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)
LEFT OUTER JOIN fm_clink ON fm_clink.fm_clink_id = FM_BILLDET.fm_clink_id
LEFT OUTER JOIN fm_contr AS leaf_contract ON leaf_contract.fm_contr_id = fm_clink.fm_contr_id
LEFT OUTER JOIN fm_contr AS root_contract ON root_contract.fm_contr_id = leaf_contract.main_contr_id
LEFT OUTER JOIN fm_serv AS dserv
ON dserv.fm_serv_id = COALESCE(leaf_contract.fm_serv_id, root_contract.fm_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.DATE_FROM <=
fm_bill.bill_date) AND
(FM_SERVPRICE.FM_PRICETYPE_ID = '119' OR
FM_SERVPRICE.FM_PRICETYPE_ID = '122') AND
FM_SERVPRICE.FM_SERV_ID = dserv.fm_serv_id
ORDER BY FM_SERVPRICE.DATE_FROM DESC,
FM_SERVPRICE.FM_PRICETYPE_ID DESC)
-- вот тут добавляю кусочек про рефералку дедушки
LEFT OUTER JOIN FM_SERVPRICE AS fmsp5 ON fmsp5.FM_SERVPRICE_ID = (SELECT TOP 1 fm_servprice.FM_SERVPRICE_ID
FROM FM_SERVPRICE
WHERE
(FM_SERVPRICE.DATE_FROM <=
fm_bill.bill_date) AND
(FM_SERVPRICE.FM_PRICETYPE_ID = '128') AND
fm_servprice.fm_serv_id =
fm_serv.fm_serv_id
ORDER BY FM_SERVPRICE.DATE_FROM DESC)
LEFT OUTER JOIN DIR_ANSW DIR_ANSW_OLD ON DIR_ANSW_OLD.MOTCONSU_RESP_ID=MOTCONSU.MOTCONSU_ID
LEFT OUTER JOIN PATDIREC PATDIREC_OLD ON PATDIREC_OLD.PATDIREC_ID=DIR_ANSW_OLD.PATDIREC_ID
LEFT OUTER JOIN medecins AS ref_doc_OLD ON ref_doc_OLD.MEDECINS_ID = PATDIREC_OLD.MEDECINS_CREATOR_ID
LEFT OUTER JOIN specialisation AS ref_spec_OLD ON ref_spec_OLD.specialisation_id = ref_doc_OLD.specialisation_id
WHERE fm_bill.bill_date >= @from_date AND
fm_bill.bill_date <= @to_date AND
(fmsp4.PRICE IS NOT NULL or fmsp5.price is not null) AND FM_SERV.pay_for_ref=1 and
PATDIREC.CANCELLED <> 1 and
ref_doc.medecins_id<>doc.medecins_id