РЕФФЕР

РЕФФЕР


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