Qinvoice

SELECT FM_BILLDET_PAY.FM_INVOICE_ID,

Coalesce(PATIENTS.PATIENTS_ID,FM_PATIENTS.FM_PATIENTS_ID+1000000000) PATIENTS_ID, PATIENTS.NE_LE NE_LE,Coalesce(FM_CLINK_PATIENTS.POLICE,FM_PATIENTS.POLICE) POLICE,Coalesce(PATIENTS.NOM,FM_PATIENTS.NOM) NOM,Coalesce(PATIENTS.PRENOM,FM_PATIENTS.PRENOM) PRENOM,

Coalesce(PATIENTS.PATRONYME,FM_PATIENTS.PATRONYME) PATRONYME,sum(FM_BILLDET.CNT) CNT,Cast(Sum(FM_BILLDET_PAY.INVOICE_AMOUNT) as varchar)+' '+FM_DEVISE.CODE INVOICE_AMOUNT_SUM, FM_CONTR.MAIN_CONTR_ID MAIN_CONTR_ID, FM_CONTR.EXT_CODE EXT_CODE, FM_CONTR.FM_CONTR_ID FM_CONTR_ID

FROM

FM_BILLDET_PAY FM_BILLDET_PAY JOIN FM_BILLDET FM_BILLDET ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY.FM_BILLDET_ID

JOIN FM_BILL FM_BILL ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

LEFT OUTER JOIN PATIENTS PATIENTS ON PATIENTS.PATIENTS_ID = FM_BILL.PATIENTS_ID

LEFT OUTER JOIN FM_PATIENTS FM_PATIENTS ON FM_PATIENTS.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN FM_CLINK FM_CLINK ON FM_CLINK.FM_CLINK_ID = FM_BILLDET.FM_CLINK_ID

LEFT OUTER JOIN FM_CLINK_PATIENTS FM_CLINK_PATIENTS ON FM_CLINK_PATIENTS.FM_CLINK_PATIENTS_ID = FM_BILLDET.FM_CLINK_PATIENTS_ID

JOIN FM_INVOICE FM_INVOICE ON FM_INVOICE.FM_INVOICE_ID = FM_BILLDET_PAY.FM_INVOICE_ID

LEFT OUTER JOIN FM_CONTR FM_CONTR ON FM_CONTR.FM_CONTR_ID = FM_CLINK.FM_CONTR_ID

LEFT OUTER JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_INVOICE.FM_DEVISE_ID

WHERE

(FM_BILLDET_PAY.FM_INVOICE_ID=:InvoiceID) and FM_BILLDET_PAY.CANCEL=0

GROUP BY

 FM_BILLDET_PAY.FM_INVOICE_ID,Coalesce(PATIENTS.PATIENTS_ID,FM_PATIENTS.FM_PATIENTS_ID+1000000000),FM_CONTR.MAIN_CONTR_ID,FM_CONTR.FM_CONTR_ID,Coalesce(FM_CLINK_PATIENTS.POLICE,FM_PATIENTS.POLICE),Coalesce(PATIENTS.NOM,FM_PATIENTS.NOM),Coalesce(PATIENTS.PRENOM,FM_PATIENTS.PRENOM),Coalesce(PATIENTS.PATRONYME,FM_PATIENTS.PATRONYME),FM_DEVISE.CODE,FM_CONTR.EXT_CODE, PATIENTS.NE_LE

ORDER BY

 NOM, PRENOM, PATRONYME,PATIENTS_ID

QinvoiceAddon

SELECT

Coalesce(PATIENTS.PATIENTS_ID,FM_PATIENTS.FM_PATIENTS_ID+1000000000) PATIENTS_ID,PATIENTS.PASPORT_N PASPORT_N,

Coalesce(PATIENTS.NOM,FM_PATIENTS.NOM) NOM,Coalesce(PATIENTS.PRENOM,FM_PATIENTS.PRENOM) PRENOM,

Coalesce(PATIENTS.PATRONYME,FM_PATIENTS.PATRONYME) PATRONYME

FROM  FM_BILLDET_PAY FM_BILLDET_PAY JOIN FM_BILLDET FM_BILLDET ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY.FM_BILLDET_ID

JOIN FM_BILL FM_BILL ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

LEFT OUTER JOIN PATIENTS PATIENTS ON PATIENTS.PATIENTS_ID = FM_BILL.PATIENTS_ID

LEFT OUTER JOIN FM_PATIENTS FM_PATIENTS ON FM_PATIENTS.FM_BILL_ID = FM_BILL.FM_BILL_ID

WHERE

(FM_BILLDET_PAY.FM_INVOICE_ID=:InvoiceID) and FM_BILLDET_PAY.CANCEL=0

GROUP BY Coalesce(PATIENTS.PATIENTS_ID,FM_PATIENTS.FM_PATIENTS_ID+1000000000),

 Coalesce(PATIENTS.NOM,FM_PATIENTS.NOM),

 Coalesce(PATIENTS.PRENOM,FM_PATIENTS.PRENOM),

 Coalesce(PATIENTS.PATRONYME,FM_PATIENTS.PATRONYME),

 PATIENTS.PASPORT_N

ORDER BY

 NOM, PRENOM, PATRONYME,PATIENTS_ID

Qservices

SELECT

Coalesce(PATIENTS.PATIENTS_ID, FM_PATIENTS.FM_PATIENTS_ID+1000000000) PATIENTS_ID, FM_BILL.CIM10_ID, MEDECINS.NOM MEDECINS_NOM, MEDECINS.PRENOM MEDECINS_PRENOM, MEDECINS.KOD1 KOD1, FM_BILLDET.PRICE PRICE,FM_BILL.FM_BILL_ID FM_BILL_ID, FM_BILL.CIM10_ID CIM10_ID, FM_BILLDET.FM_SERV_ID FM_SERV_ID, FM_BILLDET.DM_MEDS_ID DM_MEDS_ID,

FM_SERV.CODE FM_SERV_CODE,FM_SERV.CODE_AN CODE_AN,Coalesce(

DM_MEDS.LABEL

,FM_SERV.LABEL) LABEL,FM_BILLDET.CNT,Cast(FM_BILLDET_PAY.PERC as varchar) + '%' PERC,FM_BILLDET_PAY.INVOICE_AMOUNT INVOICE_AMOUNT, FM_BILLDET_PAY.NDS_PERC, FM_BILLDET_PAY.NDS,

FM_BILL.BILL_DATE,Coalesce(FM_CLINK_PATIENTS.POLICE,FM_PATIENTS.POLICE) POLICE, FM_CONTR.MAIN_CONTR_ID MAIN_CONTR_ID, FM_CONTR.EXT_CODE EXT_CODE, FM_CONTR.FM_CONTR_ID FM_CONTR_ID

FROM

FM_BILLDET_PAY FM_BILLDET_PAY JOIN FM_BILLDET FM_BILLDET ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY.FM_BILLDET_ID

JOIN FM_BILL FM_BILL ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

LEFT OUTER JOIN MEDECINS MEDECINS ON MEDECINS.MEDECINS_ID = FM_BILL.MEDECINS1_ID

LEFT OUTER JOIN PATIENTS PATIENTS ON PATIENTS.PATIENTS_ID = FM_BILL.PATIENTS_ID

LEFT OUTER JOIN FM_PATIENTS FM_PATIENTS ON FM_PATIENTS.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN FM_CLINK FM_CLINK ON FM_CLINK.FM_CLINK_ID = FM_BILLDET.FM_CLINK_ID

LEFT OUTER JOIN FM_CLINK_PATIENTS FM_CLINK_PATIENTS ON FM_CLINK_PATIENTS.FM_CLINK_PATIENTS_ID = FM_BILLDET.FM_CLINK_PATIENTS_ID

LEFT OUTER JOIN FM_SERV FM_SERV ON FM_SERV.FM_SERV_ID = FM_BILLDET.FM_SERV_ID

LEFT OUTER JOIN DM_MEDS DM_MEDS ON DM_MEDS.DM_MEDS_ID = FM_BILLDET.DM_MEDS_ID

JOIN FM_INVOICE FM_INVOICE ON FM_INVOICE.FM_INVOICE_ID = FM_BILLDET_PAY.FM_INVOICE_ID

LEFT OUTER JOIN FM_CONTR FM_CONTR ON FM_CONTR.FM_CONTR_ID = FM_CLINK.FM_CONTR_ID

LEFT OUTER JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_INVOICE.FM_DEVISE_ID

WHERE

(FM_BILLDET_PAY.FM_INVOICE_ID = :InvoiceID) and FM_BILLDET_PAY.CANCEL=0

ORDER BY

 FM_BILL.BILL_DATE,FM_BILLDET.FM_BILLDET_ID

QInvoiceSum

select Sum(FM_BILLDET.CNT) SERV_COUNT, FM_INVOICE.FM_INVOICE_ID,

Cast(Sum(FM_BILLDET_PAY.INVOICE_AMOUNT) as Varchar) + ' ' + FM_DEVISE.CODE TOTAL_AMOUNT,

Min(FM_BILL.BILL_DATE) MIN_BILL_DATE,

Max(FM_BILL.BILL_DATE) MAX_BILL_DATE,

Count(FM_BILLDET_PAY.FM_BILLDET_PAY_ID) RECORD_COUNT,

FM_CONTR.FM_COURS_TYPE_ID, FM_INVOICE.FM_ORG_ID, FM_INVOICE.FM_ORGPAY_ID, FM_INVOICE.INVOICE_DATE, FM_DEVISE.CODE, FM_DEVISE.FM_DEVISE_ID, FM_INVOICE.TOTAL_AMOUNT CTOTAL_AMOUNT,

Cast((Sum(FM_BILLDET_PAY.INVOICE_AMOUNT) - Sum(Coalesce(FM_BILLDET_PAY.NDS,0))) as Varchar) + ' ' + FM_DEVISE.CODE TOTAL_AMOUNT_WOVAT,

Cast(Sum(Coalesce(FM_BILLDET_PAY.NDS,0)) as varchar) + ' ' + FM_DEVISE.CODE TOTAL_VAT_AMOUNT,

Cast(Coalesce(FM_BILLDET_PAY.NDS_PERC,0) as varchar) + ' %' TAXE_VAT_PERC

FROM

FM_INVOICE FM_INVOICE JOIN FM_BILLDET_PAY FM_BILLDET_PAY ON FM_INVOICE.FM_INVOICE_ID = FM_BILLDET_PAY.FM_INVOICE_ID

JOIN FM_BILLDET FM_BILLDET ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY.FM_BILLDET_ID

JOIN FM_BILL FM_BILL ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_INVOICE.FM_DEVISE_ID

LEFT OUTER JOIN FM_CONTR FM_CONTR ON FM_CONTR.FM_CONTR_ID = FM_INVOICE.FM_CONTR_ID

WHERE

FM_INVOICE.FM_INVOICE_ID=:InvoiceID and FM_BILLDET_PAY.CANCEL=0

GROUP BY

 FM_INVOICE.FM_INVOICE_ID,FM_DEVISE.CODE, FM_CONTR.FM_COURS_TYPE_ID, FM_INVOICE.FM_ORG_ID, FM_INVOICE.FM_ORGPAY_ID, FM_INVOICE.INVOICE_DATE, FM_DEVISE.FM_DEVISE_ID,FM_INVOICE.TOTAL_AMOUNT,

Cast(Coalesce(FM_BILLDET_PAY.NDS_PERC,0) as varchar) + ' %'

Corg

SELECT

FM_ORG.FM_ORG_ID,(Coalesce(FM_ORG.RECIEVER,FM_ORG.LABEL)) LABEL,FM_ADR.ADR,FM_ORG.ACCOUNT,FM_ORG.BANK,

FM_ORG.BIK,FM_ORG.ACCWITH,FM_ADR.VILLE,FM_ORG.INN,FM_ORG.OKONH,

FM_ORG.OKPO,FM_ORGINT.FONCTION_TYPE,(FM_ORGINT.NOM + ' ' + FM_ORGINT.PRENOM + ' ' + FM_ORGINT.PATRONYME) FIO,FM_ORGINT.TEL,FM_ORG.KPP,

FM_ORGINT.FAX

FROM

FM_ORG FM_ORG LEFT OUTER JOIN FM_ADR FM_ADR ON FM_ADR.FM_ADR_ID = FM_ORG.FM_ADR_JUR_ID

LEFT OUTER JOIN FM_ORGINT FM_ORGINT ON FM_ORG.FM_ORG_ID = FM_ORGINT.FM_ORG_ID

WHERE

((FM_ORG.FM_ORG_ID IN (:OrgID,:OrgPayID)))

QServGrp

SELECT

FM_SERV.FM_SERV_ID,Coalesce(FM_SERV.CODE,DM_MEDS.CODE) FM_SERV_CODE,Coalesce(FM_SERV.LABEL,

DM_MEDS.LABEL

) LABEL,Sum(FM_BILLDET.CNT) CNT,Cast (Sum(FM_BILLDET_PAY.INVOICE_AMOUNT) as varchar) + ' ' + FM_DEVISE.CODE INVOICE_AMOUNT

FROM

FM_BILLDET_PAY FM_BILLDET_PAY JOIN FM_BILLDET FM_BILLDET ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY.FM_BILLDET_ID

JOIN FM_BILL FM_BILL ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

LEFT OUTER JOIN PATIENTS PATIENTS ON PATIENTS.PATIENTS_ID = FM_BILL.PATIENTS_ID

LEFT OUTER JOIN FM_PATIENTS FM_PATIENTS ON FM_PATIENTS.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN FM_CLINK FM_CLINK ON FM_CLINK.FM_CLINK_ID = FM_BILLDET.FM_CLINK_ID

LEFT OUTER JOIN FM_CLINK_PATIENTS FM_CLINK_PATIENTS ON FM_CLINK_PATIENTS.FM_CLINK_PATIENTS_ID = FM_BILLDET.FM_CLINK_PATIENTS_ID

LEFT OUTER JOIN FM_SERV FM_SERV ON FM_SERV.FM_SERV_ID = FM_BILLDET.FM_SERV_ID

LEFT OUTER JOIN DM_MEDS DM_MEDS ON DM_MEDS.DM_MEDS_ID = FM_BILLDET.DM_MEDS_ID

JOIN FM_INVOICE FM_INVOICE ON FM_INVOICE.FM_INVOICE_ID = FM_BILLDET_PAY.FM_INVOICE_ID

LEFT OUTER JOIN FM_CONTR FM_CONTR ON FM_CONTR.FM_CONTR_ID = FM_INVOICE.FM_CONTR_ID

LEFT OUTER JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_INVOICE.FM_DEVISE_ID

WHERE

 FM_BILLDET_PAY.FM_INVOICE_ID = :InvoiceID and FM_BILLDET_PAY.CANCEL = 0

GROUP BY

 Coalesce(FM_SERV.CODE,DM_MEDS.CODE), Coalesce(FM_SERV.LABEL,

DM_MEDS.LABEL

), FM_DEVISE.CODE,FM_SERV.FM_SERV_ID

ORDER BY

 Coalesce(FM_SERV.CODE,DM_MEDS.CODE)

QinvoiceTotalSum

SELECT

 Sum(FM_BILLDET.CNT) SERV_COUNT,

 Cast(Sum(FM_BILLDET_PAY.INVOICE_AMOUNT) as Varchar) + ' ' + FM_DEVISE.CODE TOTAL_AMOUNT,

 FM_DEVISE.CODE, FM_INVOICE.FM_ORG_ID,

 FM_INVOICE.FM_ORGPAY_ID, FM_INVOICE.INVOICE_DATE, FM_DEVISE.FM_DEVISE_ID,FM_INVOICE.TOTAL_AMOUNT,

 Cast(Sum(Coalesce(FM_BILLDET_PAY.NDS,0)) as varchar) + ' ' + FM_DEVISE.CODE TOTAL_VAT_AMOUNT,

 Cast(Sum(FM_BILLDET_PAY.INVOICE_AMOUNT) - Sum(Coalesce(FM_BILLDET_PAY.NDS,0)) as Varchar) + ' ' + FM_DEVISE.CODE TOTAL_AMOUNT_WOVAT

FROM

FM_INVOICE FM_INVOICE

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_INVOICE.FM_DEVISE_ID

JOIN FM_BILLDET_PAY FM_BILLDET_PAY ON FM_INVOICE.FM_INVOICE_ID = FM_BILLDET_PAY.FM_INVOICE_ID

LEFT OUTER JOIN FM_BILLDET FM_BILLDET ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY.FM_BILLDET_ID

LEFT OUTER JOIN FM_BILL FM_BILL ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

WHERE

FM_INVOICE.FM_INVOICE_ID=:InvoiceID and FM_BILLDET_PAY.CANCEL = 0

GROUP BY

 FM_DEVISE.CODE, FM_DEVISE.FM_DEVISE_ID, FM_INVOICE.FM_ORG_ID, FM_INVOICE.FM_ORGPAY_ID, FM_INVOICE.INVOICE_DATE, FM_INVOICE.TOTAL_AMOUNT

QInvCim

SELECT

Coalesce(PATIENTS.PATIENTS_ID,FM_PATIENTS.FM_PATIENTS_ID+1000000000) PATIENTS_ID,Coalesce(FM_CLINK_PATIENTS.POLICE,FM_PATIENTS.POLICE) POLICE,Coalesce(PATIENTS.NOM,FM_PATIENTS.NOM) NOM,Coalesce(PATIENTS.PRENOM,FM_PATIENTS.PRENOM) PRENOM,

Coalesce(PATIENTS.PATRONYME,FM_PATIENTS.PATRONYME) PATRONYME,PATIENTS.NE_LE,Sum(FM_BILLDET_PAY.INVOICE_AMOUNT) INVOICE_AMOUNT_SUM,

CIM10.CIM10_ID, CIM10.CODE CIM10_CODE, CIM10.DESCRIPTION, FM_CONTR.MAIN_CONTR_ID MAIN_CONTR_ID, FM_CONTR.EXT_CODE EXT_CODE, FM_CONTR.FM_CONTR_ID FM_CONTR_ID

FROM

FM_BILLDET_PAY FM_BILLDET_PAY JOIN FM_BILLDET FM_BILLDET ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY.FM_BILLDET_ID

JOIN FM_BILL FM_BILL ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

LEFT OUTER JOIN PATIENTS PATIENTS ON PATIENTS.PATIENTS_ID = FM_BILL.PATIENTS_ID

LEFT OUTER JOIN FM_PATIENTS FM_PATIENTS ON FM_PATIENTS.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN CIM10 CIM10 ON CIM10.CIM10_ID = FM_BILL.CIM10_ID

LEFT OUTER JOIN FM_CLINK FM_CLINK ON FM_CLINK.FM_CLINK_ID = FM_BILLDET.FM_CLINK_ID

LEFT OUTER JOIN FM_CLINK_PATIENTS FM_CLINK_PATIENTS ON FM_CLINK_PATIENTS.FM_CLINK_PATIENTS_ID = FM_BILLDET.FM_CLINK_PATIENTS_ID

JOIN FM_INVOICE FM_INVOICE ON FM_INVOICE.FM_INVOICE_ID = FM_BILLDET_PAY.FM_INVOICE_ID

LEFT OUTER JOIN FM_CONTR FM_CONTR ON FM_CONTR.FM_CONTR_ID = FM_CLINK.FM_CONTR_ID

LEFT OUTER JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_INVOICE.FM_DEVISE_ID

WHERE

(FM_BILLDET_PAY.FM_INVOICE_ID=:InvoiceID) and FM_BILLDET_PAY.CANCEL = 0

GROUP BY

Coalesce(PATIENTS.PATIENTS_ID,FM_PATIENTS.FM_PATIENTS_ID+1000000000),FM_CONTR.MAIN_CONTR_ID,FM_CONTR.FM_CONTR_ID,Coalesce(FM_CLINK_PATIENTS.POLICE,FM_PATIENTS.POLICE),Coalesce(PATIENTS.NOM,FM_PATIENTS.NOM),Coalesce(PATIENTS.PRENOM,FM_PATIENTS.PRENOM),Coalesce(PATIENTS.PATRONYME,FM_PATIENTS.PATRONYME),PATIENTS.NE_LE, CIM10.CIM10_ID, CIM10.CODE, CIM10.DESCRIPTION, FM_DEVISE.CODE, FM_CONTR.EXT_CODE

ORDER BY

 NOM,PRENOM,PATRONYME,PATIENTS_ID,CIM10.CIM10_ID

CTDSum

Клиентский датасет

QServPat

SELECT

 FM_BILLDET.FM_BILLDET_ID FM_BILLDET_ID, Coalesce(FM_DEP.LABEL,Cast(FM_SERV.FM_SERV_ID/FM_SERV.FM_SERV_ID as Varchar(20))+'NODEP') FM_DEP_LABEL, Coalesce(FM_SERV.CODE,DM_MEDS.CODE) CODE, FM_BILLDET.CNT CNT,

 Cast(FM_BILLDET.PRICE as Varchar) + ' ' + FM_DEVISE.CODE PRICE,

 Cast(FM_BILLDET.DISCOUNT as Varchar) DISCOUNT,

 Cast(Cast(Sum(Coalesce(FM_BILLDET_PAY_PAT.PRICE,0)) as Money) as Varchar)+ ' ' +  FM_DEVISE.CODE PRICE_PAT,

 Cast(Cast(Sum(Coalesce(FM_BILLDET_PAY_ORG.PRICE,0)) as Money) as Varchar)+ ' ' +  FM_DEVISE.CODE PRICE_ORG,

 FM_CLINK_PATIENTS.POLICE POLICE, FM_ORG.CODE FM_ORG_CODE, Coalesce(

 FM_SERV.LABEL

 ,

DM_MEDS.LABEL

 ) LABEL

FROM

FM_BILL FM_BILL JOIN FM_BILLDET FM_BILLDET ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_BILLDET.FM_DEVISE_ID

LEFT OUTER JOIN FM_SERV FM_SERV ON FM_SERV.FM_SERV_ID = FM_BILLDET.FM_SERV_ID        LEFT OUTER JOIN DM_MEDS DM_MEDS ON DM_MEDS.DM_MEDS_ID = FM_BILLDET.DM_MEDS_ID

LEFT OUTER JOIN FM_DEP FM_DEP ON FM_DEP.FM_DEP_ID = FM_SERV.FM_DEP_ID

LEFT OUTER JOIN FM_BILLDET_PAY FM_BILLDET_PAY_PAT ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY_PAT.FM_BILLDET_ID and FM_BILLDET_PAY_PAT.PATIENTS_ID is not null

LEFT OUTER JOIN FM_BILLDET_PAY FM_BILLDET_PAY_ORG ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY_ORG.FM_BILLDET_ID and FM_BILLDET_PAY_ORG.FM_ORG_ID is not null

LEFT OUTER JOIN FM_CLINK_PATIENTS FM_CLINK_PATIENTS ON FM_CLINK_PATIENTS.FM_CLINK_PATIENTS_ID = FM_BILLDET.FM_CLINK_PATIENTS_ID

LEFT OUTER JOIN FM_CLINK FM_CLINK ON FM_CLINK.FM_CLINK_ID = FM_BILLDET.FM_CLINK_ID

LEFT OUTER JOIN FM_CONTR FM_CONTR ON FM_CONTR.FM_CONTR_ID = FM_CLINK.FM_CONTR_ID

LEFT OUTER JOIN FM_ORG FM_ORG ON FM_ORG.FM_ORG_ID = FM_CONTR.FM_ORG1_ID

LEFT OUTER JOIN MOTCONSU MOTCONSU ON MOTCONSU.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN DIR_ANSW DIR_ANSW ON DIR_ANSW.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN PATDIREC ON PATDIREC.PATDIREC_ID = DIR_ANSW.PATDIREC_ID

LEFT OUTER JOIN MOTCONSU M ON M.MOTCONSU_ID = PATDIREC.MOTCONSU_ID

LEFT OUTER JOIN FM_BILL B ON B.FM_BILL_ID = M.FM_BILL_ID

WHERE

((FM_BILL.FM_BILL_ID = :BillID) or (B.FM_BILL_ID = :BillID) or (MOTCONSU.MOTCONSU_ID = :MotconsuID) or (M.MOTCONSU_ID = :MotconsuID) ) and (FM_BILLDET_PAY_PAT.CANCEL = 0 or FM_BILLDET_PAY_PAT.CANCEL is NULL) and (FM_BILLDET_PAY_ORG.CANCEL = 0 or FM_BILLDET_PAY_ORG.CANCEL is NULL)

GROUP BY

 Coalesce(FM_DEP.LABEL,Cast(FM_SERV.FM_SERV_ID/FM_SERV.FM_SERV_ID as Varchar(20))+'NODEP'), FM_BILLDET.FM_BILLDET_ID, FM_SERV.CODE, FM_BILLDET.CNT, FM_BILLDET.PRICE, FM_BILLDET.DISCOUNT, FM_DEVISE.CODE, FM_CLINK_PATIENTS.POLICE, FM_ORG.CODE, DM_MEDS.CODE,

DM_MEDS.LABEL

 ,

 FM_SERV.LABEL

ORDER BY

 Coalesce(FM_DEP.LABEL,Cast(FM_SERV.FM_SERV_ID/FM_SERV.FM_SERV_ID as Varchar(20))+'NODEP'), FM_SERV.CODE

QMiddle

SELECT

 Coalesce(FM_DEP.LABEL,Cast(FM_SERV.FM_SERV_ID/FM_SERV.FM_SERV_ID as Varchar(20))+'NODEP') FM_DEP_LABEL,

 Cast(Sum(FM_BILLDET.PRICE) as Varchar) + ' ' + FM_DEVISE.CODE PRICE,

 Cast(Cast(Sum(Coalesce(FM_BILLDET_PAY_PAT.PRICE,0)) as Money) as Varchar)+ ' ' +  FM_DEVISE.CODE PRICE_PAT,

 Cast(Cast(Sum(Coalesce(FM_BILLDET_PAY_ORG.PRICE,0)) as Money) as Varchar)+ ' ' +  FM_DEVISE.CODE PRICE_ORG

FROM

FM_BILL FM_BILL JOIN FM_BILLDET FM_BILLDET ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_BILLDET.FM_DEVISE_ID

LEFT OUTER JOIN FM_SERV FM_SERV ON FM_SERV.FM_SERV_ID = FM_BILLDET.FM_SERV_ID             LEFT OUTER JOIN DM_MEDS DM_MEDS ON DM_MEDS.DM_MEDS_ID = FM_BILLDET.DM_MEDS_ID

LEFT OUTER JOIN FM_DEP FM_DEP ON FM_DEP.FM_DEP_ID = FM_SERV.FM_DEP_ID

LEFT OUTER JOIN FM_BILLDET_PAY FM_BILLDET_PAY_PAT ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY_PAT.FM_BILLDET_ID and FM_BILLDET_PAY_PAT.PATIENTS_ID is not null

LEFT OUTER JOIN FM_BILLDET_PAY FM_BILLDET_PAY_ORG ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY_ORG.FM_BILLDET_ID and FM_BILLDET_PAY_ORG.FM_ORG_ID is not null

LEFT OUTER JOIN MOTCONSU MOTCONSU ON MOTCONSU.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN DIR_ANSW DIR_ANSW ON DIR_ANSW.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN PATDIREC ON PATDIREC.PATDIREC_ID = DIR_ANSW.PATDIREC_ID

LEFT OUTER JOIN MOTCONSU M ON M.MOTCONSU_ID = PATDIREC.MOTCONSU_ID

LEFT OUTER JOIN FM_BILL B ON B.FM_BILL_ID = M.FM_BILL_ID

WHERE

((FM_BILL.FM_BILL_ID = :BillID) or (B.FM_BILL_ID = :BillID) or (MOTCONSU.MOTCONSU_ID = :MotconsuID) or (M.MOTCONSU_ID = :MotconsuID) ) and (FM_BILLDET_PAY_PAT.CANCEL = 0 or FM_BILLDET_PAY_PAT.CANCEL is NULL) and (FM_BILLDET_PAY_ORG.CANCEL = 0 or FM_BILLDET_PAY_ORG.CANCEL is NULL)

GROUP BY

 Coalesce(FM_DEP.LABEL,Cast(FM_SERV.FM_SERV_ID/FM_SERV.FM_SERV_ID as Varchar(20))+'NODEP'), FM_DEVISE.CODE

ORDER BY

 Coalesce(FM_DEP.LABEL,Cast(FM_SERV.FM_SERV_ID/FM_SERV.FM_SERV_ID as Varchar(20))+'NODEP')

QSubTotal

SELECT

 Cast(Sum(FM_BILLDET.PRICE) as Varchar) + ' ' + FM_DEVISE.CODE PRICE,

 Cast(Cast(Sum(Coalesce(FM_BILLDET_PAY_PAT.PRICE,0)) as Money) as Varchar)+ ' ' +  FM_DEVISE.CODE PRICE_PAT,

 Cast(Cast(Sum(Coalesce(FM_BILLDET_PAY_ORG.PRICE,0)) as Money) as Varchar)+ ' ' +  FM_DEVISE.CODE PRICE_ORG

FROM

FM_BILL FM_BILL JOIN FM_BILLDET FM_BILLDET ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_BILLDET.FM_DEVISE_ID

LEFT OUTER JOIN FM_BILLDET_PAY FM_BILLDET_PAY_PAT ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY_PAT.FM_BILLDET_ID and FM_BILLDET_PAY_PAT.PATIENTS_ID is not null

LEFT OUTER JOIN FM_BILLDET_PAY FM_BILLDET_PAY_ORG ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY_ORG.FM_BILLDET_ID and FM_BILLDET_PAY_ORG.FM_ORG_ID is not null

LEFT OUTER JOIN MOTCONSU MOTCONSU ON MOTCONSU.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN DIR_ANSW DIR_ANSW ON DIR_ANSW.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN PATDIREC ON PATDIREC.PATDIREC_ID = DIR_ANSW.PATDIREC_ID

LEFT OUTER JOIN MOTCONSU M ON M.MOTCONSU_ID = PATDIREC.MOTCONSU_ID

LEFT OUTER JOIN FM_BILL B ON B.FM_BILL_ID = M.FM_BILL_ID

WHERE

((FM_BILL.FM_BILL_ID = :BillID) or (B.FM_BILL_ID = :BillID) or (MOTCONSU.MOTCONSU_ID = :MotconsuID) or (M.MOTCONSU_ID = :MotconsuID) ) and (FM_BILLDET_PAY_PAT.CANCEL = 0 or FM_BILLDET_PAY_PAT.CANCEL is NULL) and (FM_BILLDET_PAY_ORG.CANCEL = 0 or FM_BILLDET_PAY_ORG.CANCEL is NULL)

GROUP BY

 FM_DEVISE.CODE

QPatient

select top 1 Cast(Sum(INVOICE_AMOUNT_PAT) as Varchar) + ' ' + Coalesce(CODE,'') INVOICE_AMOUNT_PAT,

    Cast(Sum(TAXE_AMOUNT) as Varchar) + ' ' + Coalesce(CODE,'') TAXE_AMOUNT,

    Cast(Sum(TOTAL_AMOUNT) as Varchar) + ' ' + Coalesce(CODE,'') TOTAL_AMOUNT,

    Cast(Sum(TOTAL_PAYED_AMOUNT) as Varchar) + ' ' + Coalesce(CODE,'') TOTAL_PAYED_AMOUNT,

    Cast(Sum(UNPAID) as Varchar) + ' ' + Coalesce(CODE,'') UNPAID

from

(

SELECT

FM_DEVISE_PAT.CODE CODE,

Cast(Sum(Coalesce(FM_BILLDET_PAY_PAT.INVOICE_AMOUNT,0)) as Money) INVOICE_AMOUNT_PAT,

(

  select Coalesce(Cast(Sum(Coalesce(P1.TAXE_AMOUNT,0)) as Money),0) FROM FM_PAYMENTS P1

  JOIN FM_BILLDET_PAY BDP1 ON P1.FM_BILLDET_PAY_ID=BDP1.FM_BILLDET_PAY_ID

  JOIN FM_BILLDET BD1 ON BD1.FM_BILLDET_ID=BDP1.FM_BILLDET_ID

  where FM_BILLDET.FM_BILL_ID=BD1.FM_BILL_ID and BDP1.CANCEL = 0

)

TAXE_AMOUNT,

Cast(Sum(Coalesce(FM_BILLDET_PAY_PAT.INVOICE_AMOUNT,0)) as Money) + (

  select Coalesce(Cast(Sum(Coalesce(P1.TAXE_AMOUNT,0)) as Money),0) FROM FM_PAYMENTS P1

  JOIN FM_BILLDET_PAY BDP1 ON P1.FM_BILLDET_PAY_ID=BDP1.FM_BILLDET_PAY_ID

  JOIN FM_BILLDET BD1 ON BD1.FM_BILLDET_ID=BDP1.FM_BILLDET_ID

  where FM_BILLDET.FM_BILL_ID=BD1.FM_BILL_ID and BDP1.CANCEL = 0

)

TOTAL_AMOUNT,

(

  select Coalesce(Cast(Sum(Coalesce(P1.TRAN_AMOUNT,0)) as Money),0) FROM FM_PAYMENTS P1

  JOIN FM_BILLDET_PAY BDP1 ON P1.FM_BILLDET_PAY_ID=BDP1.FM_BILLDET_PAY_ID

  JOIN FM_BILLDET BD1 ON BD1.FM_BILLDET_ID=BDP1.FM_BILLDET_ID

  where FM_BILLDET.FM_BILL_ID=BD1.FM_BILL_ID and BDP1.CANCEL = 0

)

TOTAL_PAYED_AMOUNT,

Cast(Sum(Coalesce(FM_BILLDET_PAY_PAT.INVOICE_AMOUNT,0)) as Money) - (

  select Coalesce(Cast(Sum(Coalesce(P1.TRAN_AMOUNT,0)-Coalesce(P1.TAXE_AMOUNT,0)) as Money),0) FROM FM_PAYMENTS P1

  JOIN FM_BILLDET_PAY BDP1 ON P1.FM_BILLDET_PAY_ID=BDP1.FM_BILLDET_PAY_ID

  JOIN FM_BILLDET BD1 ON BD1.FM_BILLDET_ID=BDP1.FM_BILLDET_ID

  where FM_BILLDET.FM_BILL_ID=BD1.FM_BILL_ID and BDP1.CANCEL = 0

)

UNPAID

FROM

FM_BILL FM_BILL JOIN FM_BILLDET FM_BILLDET ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_BILLDET.FM_DEVISE_ID

LEFT OUTER JOIN FM_BILLDET_PAY FM_BILLDET_PAY_PAT ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY_PAT.FM_BILLDET_ID and FM_BILLDET_PAY_PAT.PATIENTS_ID is not null

LEFT OUTER JOIN FM_BILLDET_PAY FM_BILLDET_PAY_ORG ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY_ORG.FM_BILLDET_ID and FM_BILLDET_PAY_ORG.FM_ORG_ID is not null

LEFT OUTER JOIN FM_INVOICE FM_INVOICE_PAT ON FM_INVOICE_PAT.FM_INVOICE_ID = FM_BILLDET_PAY_PAT.FM_INVOICE_ID and FM_INVOICE_PAT.INVOICE_STATE='V'

LEFT OUTER JOIN FM_DEVISE FM_DEVISE_PAT ON FM_DEVISE_PAT.FM_DEVISE_ID = FM_INVOICE_PAT.FM_DEVISE_ID

LEFT OUTER JOIN MOTCONSU MOTCONSU ON MOTCONSU.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN DIR_ANSW DIR_ANSW ON DIR_ANSW.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN PATDIREC ON PATDIREC.PATDIREC_ID = DIR_ANSW.PATDIREC_ID

LEFT OUTER JOIN MOTCONSU M ON M.MOTCONSU_ID = PATDIREC.MOTCONSU_ID

LEFT OUTER JOIN FM_BILL B ON B.FM_BILL_ID = M.FM_BILL_ID

WHERE

((FM_BILL.FM_BILL_ID = :BillID) or (B.FM_BILL_ID = :BillID) or (MOTCONSU.MOTCONSU_ID = :MotconsuID) or (M.MOTCONSU_ID = :MotconsuID) )

and (FM_INVOICE_PAT.FM_DEVISE_ID is not null) and ((FM_BILLDET_PAY_PAT.CANCEL = 0) or (FM_BILLDET_PAY_PAT.CANCEL is null)) and ((FM_BILLDET_PAY_ORG.CANCEL = 0) or (FM_BILLDET_PAY_ORG.CANCEL is null))

GROUP BY

 FM_BILLDET.FM_BILL_ID,FM_DEVISE.CODE, FM_DEVISE_PAT.CODE

) gq

group by

 CODE

QBillPrepaid

SELECT

 FM_CONTR.PAYMENT_TYPE,

 Cast(Cast(Sum(Coalesce(FM_BILLDET_PAY_ORG.PRICE,0)) as Money) as Varchar)+ ' ' +  FM_DEVISE.CODE PRICE_ORG

FROM

FM_BILL FM_BILL JOIN FM_BILLDET FM_BILLDET ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

JOIN FM_CLINK FM_CLINK ON FM_CLINK.FM_CLINK_ID = FM_BILLDET.FM_CLINK_ID

JOIN FM_CONTR FM_CONTR ON FM_CONTR.FM_CONTR_ID = FM_CLINK.FM_CONTR_ID

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_BILLDET.FM_DEVISE_ID

LEFT OUTER JOIN FM_BILLDET_PAY FM_BILLDET_PAY_ORG ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY_ORG.FM_BILLDET_ID and FM_BILLDET_PAY_ORG.FM_ORG_ID is not null

LEFT OUTER JOIN MOTCONSU MOTCONSU ON MOTCONSU.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN DIR_ANSW DIR_ANSW ON DIR_ANSW.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN PATDIREC ON PATDIREC.PATDIREC_ID = DIR_ANSW.PATDIREC_ID

LEFT OUTER JOIN MOTCONSU M ON M.MOTCONSU_ID = PATDIREC.MOTCONSU_ID

LEFT OUTER JOIN FM_BILL B ON B.FM_BILL_ID = M.FM_BILL_ID

WHERE

(FM_BILL.FM_BILL_ID = :BillID) or (B.FM_BILL_ID = :BillID) or (MOTCONSU.MOTCONSU_ID = :MotconsuID) or (M.MOTCONSU_ID = :MotconsuID) and ((FM_BILLDET_PAY_ORG.CANCEL = 0) or (FM_BILLDET_PAY_ORG.CANCEL is null))

GROUP BY

FM_CONTR.PAYMENT_TYPE,FM_DEVISE.CODE

QPayments

SELECT

FM_PAYMODE.LABEL LABEL, FM_ACCOUNT_TRAN.TRAN_DATE TRAN_DATE, Cast(Cast(Sum(Coalesce(FM_PAYMENTS_PAT.TRAN_AMOUNT,0)) as Money) as Varchar)+ ' ' + Coalesce(FM_DEVISE_PAT.CODE,'') PAYED_AMOUNT

FROM

FM_BILL FM_BILL JOIN FM_BILLDET FM_BILLDET ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

JOIN FM_BILLDET_PAY FM_BILLDET_PAY_PAT ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY_PAT.FM_BILLDET_ID and FM_BILLDET_PAY_PAT.PATIENTS_ID is not null

JOIN FM_INVOICE FM_INVOICE_PAT ON FM_INVOICE_PAT.FM_INVOICE_ID = FM_BILLDET_PAY_PAT.FM_INVOICE_ID and FM_INVOICE_PAT.INVOICE_STATE='V'

JOIN FM_DEVISE FM_DEVISE_PAT ON FM_DEVISE_PAT.FM_DEVISE_ID = FM_INVOICE_PAT.FM_DEVISE_ID

JOIN FM_PAYMENTS FM_PAYMENTS_PAT ON FM_PAYMENTS_PAT.FM_BILLDET_PAY_ID = FM_BILLDET_PAY_PAT.FM_BILLDET_PAY_ID

JOIN FM_ACCOUNT_TRAN FM_ACCOUNT_TRAN ON FM_PAYMENTS_PAT.FM_ACCOUNT_TRAN_ID = FM_ACCOUNT_TRAN.FM_ACCOUNT_TRAN_ID

JOIN FM_PAYMODE FM_PAYMODE ON FM_ACCOUNT_TRAN.FM_PAYMODE_ID = FM_PAYMODE.FM_PAYMODE_ID

LEFT OUTER JOIN MOTCONSU MOTCONSU ON MOTCONSU.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN DIR_ANSW DIR_ANSW ON DIR_ANSW.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN PATDIREC ON PATDIREC.PATDIREC_ID = DIR_ANSW.PATDIREC_ID

LEFT OUTER JOIN MOTCONSU M ON M.MOTCONSU_ID = PATDIREC.MOTCONSU_ID

LEFT OUTER JOIN FM_BILL B ON B.FM_BILL_ID = M.FM_BILL_ID

WHERE

(FM_BILL.FM_BILL_ID = :BillID) or (B.FM_BILL_ID = :BillID) or (MOTCONSU.MOTCONSU_ID = :MotconsuID) or (M.MOTCONSU_ID = :MotconsuID)  and ((FM_BILLDET_PAY_PAT.CANCEL = 0) or (FM_BILLDET_PAY_PAT.CANCEL is null))

GROUP BY FM_PAYMODE.LABEL, FM_DEVISE_PAT.CODE, FM_ACCOUNT_TRAN.TRAN_DATE

QAccount

SELECT

 Cast(Cast((select Coalesce(FM_ACCOUNT.ACCOUNT_SUM,0) from FM_ACCOUNT

  where

  FM_ACCOUNT.PATIENTS_ID = :PatientsID

  and FM_ACCOUNT.FM_DEVISE_ID=:DefDevise

 ) as Money) as varchar) + ' ' + FM_DEVISE.CODE ACCOUNT_SUM,

 Cast(Cast(Sum(Coalesce(FM_BILLDET_PAY.INVOICE_AMOUNT,0)) -

 (select Sum(Coalesce(FM_PAYMENTS.TRAN_AMOUNT,0) - Coalesce(FM_PAYMENTS.TAXE_AMOUNT,0)) from FM_PAYMENTS

 JOIN FM_BILLDET_PAY BDP1 ON BDP1.FM_BILLDET_PAY_ID = FM_PAYMENTS.FM_BILLDET_PAY_ID

 JOIN FM_INVOICE I ON I.FM_INVOICE_ID = BDP1.FM_INVOICE_ID and I.INVOICE_STATE='V'

 where BDP1.PATIENTS_ID = :PatientsID and BDP1.CANCEL=0

 and I.FM_DEVISE_ID=:DefDevise) AS Money) as Varchar) + ' ' + FM_DEVISE.CODE UNPAID

FROM

FM_BILLDET_PAY

JOIN FM_INVOICE FM_INVOICE ON FM_INVOICE.FM_INVOICE_ID = FM_BILLDET_PAY.FM_INVOICE_ID and FM_INVOICE.INVOICE_STATE='V'

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_INVOICE.FM_DEVISE_ID

WHERE

FM_BILLDET_PAY.PATIENTS_ID = :PatientsID

and FM_INVOICE.FM_DEVISE_ID=:DefDevise and FM_BILLDET_PAY.CANCEL=0

group by FM_DEVISE.CODE

QInvInfo

SELECT top 1

 FM_BILL.FM_BILL_ID FM_BILL_ID, Coalesce(Cast(MOTCONSU.DATE_CONSULTATION as Varchar),Cast(FM_BILL.FM_BILL_ID as Varchar)) DATE_CONSULTATION, FM_BILL.BILL_DATE BILL_DATE, FM_BILL.PATIENTS_ID PATIENTS_ID, PATIENTS.NE_LE NE_LE, PATIENTS.NOM + ' ' + PATIENTS.PRENOM + ' ' + PATIENTS.PATRONYME NAME,  CIM10.DESCRIPTION DESCRIPTION,

 FM_ORG.CODE

 CODE

FROM

FM_BILL FM_BILL JOIN FM_BILLDET FM_BILLDET ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

JOIN PATIENTS PATIENTS ON PATIENTS.PATIENTS_ID=FM_BILL.PATIENTS_ID

LEFT OUTER JOIN FM_ORG FM_ORG ON FM_ORG.FM_ORG_ID=PATIENTS.FM_ORG_ID

LEFT OUTER JOIN CIM10 CIM10 ON CIM10.CIM10_ID=FM_BILL.CIM10_ID

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_BILLDET.FM_DEVISE_ID

LEFT OUTER JOIN FM_BILLDET_PAY FM_BILLDET_PAY_PAT ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY_PAT.FM_BILLDET_ID and FM_BILLDET_PAY_PAT.PATIENTS_ID is not null

LEFT OUTER JOIN FM_BILLDET_PAY FM_BILLDET_PAY_ORG ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY_ORG.FM_BILLDET_ID and FM_BILLDET_PAY_ORG.FM_ORG_ID is not null

LEFT OUTER JOIN MOTCONSU MOTCONSU ON MOTCONSU.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN DIR_ANSW DIR_ANSW ON DIR_ANSW.FM_BILL_ID = FM_BILL.FM_BILL_ID

LEFT OUTER JOIN PATDIREC ON PATDIREC.PATDIREC_ID = DIR_ANSW.PATDIREC_ID

LEFT OUTER JOIN MOTCONSU M ON M.MOTCONSU_ID = PATDIREC.MOTCONSU_ID

LEFT OUTER JOIN FM_BILL B ON B.FM_BILL_ID = M.FM_BILL_ID

WHERE

(FM_BILL.FM_BILL_ID = :BillID) or (B.FM_BILL_ID = :BillID) or (MOTCONSU.MOTCONSU_ID = :MotconsuID) or (M.MOTCONSU_ID = :MotconsuID)  and ((FM_BILLDET_PAY_PAT.CANCEL = 0) or (FM_BILLDET_PAY_PAT.CANCEL is null)) and ((FM_BILLDET_PAY_ORG.CANCEL = 0) or (FM_BILLDET_PAY_ORG.CANCEL is null))

ORDER BY

MOTCONSU.MOTCONSU_ID desc

CBillMed

SELECT

FM_BILL.FM_BILL_ID,MEDECINS.NOM,MEDECINS.PRENOM,(Cast(DATE_CONSULTATION as Varchar)) DATE_CONSULTATION

FROM

FM_BILL FM_BILL LEFT OUTER JOIN MOTCONSU MOTCONSU ON FM_BILL.FM_BILL_ID = MOTCONSU.FM_BILL_ID

LEFT OUTER JOIN MEDECINS MEDECINS ON MEDECINS.MEDECINS_ID = FM_BILL.MEDECINS1_ID

WHERE

((( FM_BILL.FM_BILL_ID = :BillID) or (MOTCONSU.MOTCONSU_ID = :MotconsuID)))

CInfo

SELECT

FM_INVOICE.FM_INVOICE_ID,FM_INVOICE.INVOICE_DATE,FM_INVOICE.PATIENTS_ID,PATIENTS.NOM,PATIENTS.PRENOM,

PATIENTS.PATRONYME,PATIENTS.NE_LE,FM_ORG.FM_ORG_ID,FM_ORG.LABEL,FM_ADR.ADR,

FM_ORGINT.TEL,FM_ORG.INN,FM_ORG.ACCOUNT,FM_ORG.BANK,FM_ORG.BIK,

FM_ORG.ACCWITH,FM_ADR.IND,FM_ADR.VILLE,FM_ADR.COUNTRY,FM_DEVISE.CODE,

FM_INVOICE.NUM,(Year(NE_LE)) YEAR

FROM

FM_INVOICE FM_INVOICE LEFT OUTER JOIN PATIENTS PATIENTS ON PATIENTS.PATIENTS_ID = FM_INVOICE.PATIENTS_ID

LEFT OUTER JOIN FM_ORG FM_ORG ON FM_ORG.FM_ORG_ID = FM_INVOICE.FM_ORG_ID

LEFT OUTER JOIN FM_ADR FM_ADR ON FM_ADR.FM_ADR_ID = FM_ORG.FM_ADR_JUR_ID

LEFT OUTER JOIN FM_ORGINT FM_ORGINT ON FM_ORG.FM_ORG_ID = FM_ORGINT.FM_ORG_ID

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_INVOICE.FM_DEVISE_ID

WHERE

(( FM_INVOICE.FM_INVOICE_ID = :InvoiceID))

CPatServ

SELECT

FM_BILLDET_PAY.FM_BILLDET_PAY_ID,FM_BILLDET.CNT,FM_BILLDET_PAY.PRICE,FM_BILLDET.FM_DEVISE_ID,FM_BILLDET_PAY.INVOICE_AMOUNT,

FM_DEVISE.CODE FM_DEVISE_CODE,(Coalesce(FM_SERV.FM_TAXENSP_ID,DM_LOTS.FM_TAXENSP_ID)) FM_TAXENSP_ID,FM_SERV.CODE,(Coalesce(FM_SERV.LABEL, DM_MEDS.LABEL)) LABEL,(Cast(0 as Money)) NSP_P,

(Cast(0 as Money)) SUM_NSP_P,(Cast(0 as Money)) NSP,(Cast(0 as Money)) SUM_NSP,FM_DEVISE.ABBRNAME,FM_SERV.FM_SERV_ID,

FM_BILLDET.DISCOUNT,(Cast(0 as  Money)) DISC,(Cast(0 as Money)) PRICEDISC,(FM_DEP.LABEL + ' ' + Coalesce(MEDECINS.NOM,'') + ' ' + Coalesce(MEDECINS.PRENOM,'')) FM_DEP_LABEL,FM_DEVISE_1.CODE CODE_1,

FM_SERV.CODE_AN2,FM_BILL.MEDECINS1_ID,(Cast(0 as Money)) NDS_PERC,(Cast(0 as Money)) NSP_PERC,FM_SERV.FM_TAXE_ID,

(Cast(0 as Money)) NDS,FM_SERV.LABEL FM_SERV_LABEL,FM_SERV.SHORT_LABEL,FM_SERV.DESCRIPTION,FM_BILL.FM_BILL_ID,

FM_BILL.BILL_DATE

FROM

FM_BILLDET_PAY FM_BILLDET_PAY JOIN FM_BILLDET FM_BILLDET ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY.FM_BILLDET_ID

LEFT OUTER JOIN FM_SERV FM_SERV ON FM_SERV.FM_SERV_ID = FM_BILLDET.FM_SERV_ID

LEFT OUTER JOIN FM_DEP FM_DEP ON FM_DEP.FM_DEP_ID = FM_SERV.FM_DEP_ID

LEFT OUTER JOIN DM_MEDS DM_MEDS ON DM_MEDS.DM_MEDS_ID = FM_BILLDET.DM_MEDS_ID

JOIN FM_BILL FM_BILL ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

LEFT OUTER JOIN MEDECINS MEDECINS ON MEDECINS.MEDECINS_ID = FM_BILL.MEDECINS1_ID

LEFT OUTER JOIN DM_TRANSFERS DM_TRANSFERS ON DM_TRANSFERS.DM_TRANSFERS_ID = FM_BILLDET.DM_TRANSFERS_ID

LEFT OUTER JOIN DM_LOTS DM_LOTS ON DM_LOTS.DM_LOTS_ID = DM_TRANSFERS.DM_LOTS_ID

JOIN FM_DEVISE FM_DEVISE_1 ON FM_DEVISE_1.FM_DEVISE_ID = FM_BILLDET.FM_DEVISE_ID

JOIN FM_INVOICE FM_INVOICE ON FM_INVOICE.FM_INVOICE_ID = FM_BILLDET_PAY.FM_INVOICE_ID

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_INVOICE.FM_DEVISE_ID

WHERE

((FM_BILLDET_PAY.FM_INVOICE_ID = :InvoiceID and FM_BILLDET_PAY.CANCEL = 0))

ORDER BY

FM_BILL.FM_BILL_ID

CPatPayments

SELECT

FM_PAYMODE.FM_PAYMODE_ID,FM_PAYMODE.LABEL,FM_ACCOUNT_TRAN.TRAN_TYPE,FM_ACCOUNT_TRAN.TRAN_DATE,(Sum(FM_PAYMENTS.TRAN_AMOUNT)) TRAN_AMOUNT,

(Cast(FM_PAYMENTS.WODOLG as Integer)) WODOLG

FROM

FM_PAYMODE FM_PAYMODE JOIN FM_ACCOUNT_TRAN FM_ACCOUNT_TRAN ON FM_PAYMODE.FM_PAYMODE_ID = FM_ACCOUNT_TRAN.FM_PAYMODE_ID

JOIN FM_PAYMENTS FM_PAYMENTS ON FM_ACCOUNT_TRAN.FM_ACCOUNT_TRAN_ID = FM_PAYMENTS.FM_ACCOUNT_TRAN_ID

JOIN FM_BILLDET_PAY FM_BILLDET_PAY ON FM_BILLDET_PAY.FM_BILLDET_PAY_ID = FM_PAYMENTS.FM_BILLDET_PAY_ID

WHERE

((FM_BILLDET_PAY.FM_INVOICE_ID = :InvoiceID and FM_BILLDET_PAY.CANCEL = 0))

AND ((FM_ACCOUNT_TRAN.TRAN_TYPE <>'Z'))

GROUP BY

FM_PAYMODE.FM_PAYMODE_ID,FM_PAYMODE.LABEL,FM_ACCOUNT_TRAN.TRAN_TYPE,FM_ACCOUNT_TRAN.TRAN_DATE,Cast(FM_PAYMENTS.WODOLG as Integer)

ORDER BY

FM_ACCOUNT_TRAN.TRAN_TYPE,FM_ACCOUNT_TRAN.TRAN_DATE

CBill

SELECT

FM_BILL.FM_BILL_ID,MEDECINS.NOM MEDECINS_NOM,MEDECINS.PRENOM MEDECINS_PRENOM,FM_BILL.BILL_DATE,(Coalesce(PATIENTS.NOM, FM_PATIENTS.NOM)) NOM,

(Coalesce(PATIENTS.PRENOM, FM_PATIENTS.PRENOM)) PRENOM,(Coalesce(PATIENTS.PATRONYME, FM_PATIENTS.PATRONYME)) PATRONYME,FM_BILL.KABINET,PATIENTS.N_OMON,FM_BILL.EXT_NUM,

FM_BILL.PATIENTS_ID,FM_BILL.FM_DEP_ID,FM_BILL.MEDECINS1_ID,FM_BILL.MEDECINS2_ID,FM_BILL.MEDECINS3_ID,

FM_BILL.MEDECINS4_ID,FM_BILL.MEDECINS5_ID,FM_BILL.CIM10_ID,FM_BILL.MOTCONSU_ID,FM_BILL.MOTCONSU_DIR_ID,

FM_BILL.MOTCONSU_MAIN_ID,FM_BILL.MOTCONSU_EV_ID,FM_BILL.MEDECINS_CREATE_ID,FM_BILL.MEDECINS_MODIFY_ID,FM_BILL.DATE_MODIFY

FROM

FM_BILL FM_BILL LEFT OUTER JOIN FM_PATIENTS FM_PATIENTS ON FM_BILL.FM_BILL_ID = FM_PATIENTS.FM_BILL_ID

LEFT OUTER JOIN MEDECINS MEDECINS ON MEDECINS.MEDECINS_ID = FM_BILL.MEDECINS1_ID

LEFT OUTER JOIN PATIENTS PATIENTS ON PATIENTS.PATIENTS_ID = FM_BILL.PATIENTS_ID

WHERE

((FM_BILL.FM_BILL_ID = :FM_BILL_ID))

CBillServ

SELECT

FM_BILLDET.FM_BILLDET_ID,(Coalesce(FM_SERV.LABEL, DM_MEDS.LABEL)) LABEL,FM_BILLDET.PRICE_TO_PAY,FM_DEVISE.CODE,FM_BILLDET.CNT,

(Coalesce(FM_SERV.CODE, DM_MEDS.CODE)) SERV_CODE

FROM

FM_BILLDET FM_BILLDET LEFT OUTER JOIN DM_MEDS DM_MEDS ON DM_MEDS.DM_MEDS_ID = FM_BILLDET.DM_MEDS_ID

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_BILLDET.FM_DEVISE_ID

LEFT OUTER JOIN FM_SERV FM_SERV ON FM_SERV.FM_SERV_ID = FM_BILLDET.FM_SERV_ID

WHERE

((FM_BILLDET.FM_BILL_ID = :FM_BILL_ID))

CDefMedecin

SELECT

MEDECINS.MEDECINS_ID,(Coalesce(MEDECINS.NOM,'') + ' ' + Coalesce(MEDECINS.PRENOM,'')) NAME

FROM

MEDECINS MEDECINS

WHERE

(( MEDECINS.MEDECINS_ID = :MedecinsID))

CInvOrg

SELECT

FM_INVOICE.NUM,FM_INVOICE.FM_ORGPAY_ID,FM_INVOICE.FM_ORG_ID,FM_INVOICE.INVOICE_DATE,FM_DEVISE.ABBRNAME,

FM_BILL.BILL_DATE,(Sum(FM_BILLDET_PAY.INVOICE_AMOUNT)) INVOICE_AMOUNT,PATIENTS.NOM,PATIENTS.PRENOM,PATIENTS.PATRONYME,

FM_INVOICE.TOTAL_AMOUNT

FROM

FM_BILLDET_PAY FM_BILLDET_PAY JOIN FM_INVOICE FM_INVOICE ON FM_INVOICE.FM_INVOICE_ID = FM_BILLDET_PAY.FM_INVOICE_ID

JOIN FM_DEVISE FM_DEVISE ON FM_DEVISE.FM_DEVISE_ID = FM_INVOICE.FM_DEVISE_ID

JOIN FM_BILLDET FM_BILLDET ON FM_BILLDET.FM_BILLDET_ID = FM_BILLDET_PAY.FM_BILLDET_ID

LEFT OUTER JOIN FM_BILL FM_BILL ON FM_BILL.FM_BILL_ID = FM_BILLDET.FM_BILL_ID

LEFT OUTER JOIN PATIENTS PATIENTS ON PATIENTS.PATIENTS_ID = FM_BILL.PATIENTS_ID

WHERE

((FM_BILLDET_PAY.FM_INVOICE_ID = :InvoiceID and FM_BILLDET_PAY.CANCEL = 0))

GROUP BY

FM_INVOICE.NUM,FM_INVOICE.FM_ORGPAY_ID,FM_INVOICE.FM_ORG_ID,FM_INVOICE.INVOICE_DATE,FM_DEVISE.ABBRNAME,

FM_BILL.BILL_DATE,PATIENTS.NOM,PATIENTS.PRENOM,PATIENTS.PATRONYME,FM_INVOICE.TOTAL_AMOUNT

ORDER BY

FM_BILL.BILL_DATE,PATIENTS.NOM,PATIENTS.PRENOM,PATIENTS.PATRONYME