/*
Alias: WORK
LiveAnswer: FALSE

*/

SELECT DentEconomics.LabID, DentalLabDB.Company, DentEconomics.DentistID, Doctors.LastName AS Doctor, 
			DentEconomics."Date", LabOrderItems.OrderPrice as Credit, LabOrderItems.OrderPriceEURO as CreditEURO, 
			DentEconomics.ReceiptNo, 0 AS ReceiptCredit, 0  AS ReceiptCreditEURO, DentEconomics.Sign, 
			Operations.Description, LabOrderItems.ToothNum, Patient.LastName, DentEconomics.EconomID
FROM (((((DentEconomics LEFT OUTER JOIN DentalLabDB ON DentEconomics.LabID = DentalLabDB.DentalLabID)
		LEFT OUTER JOIN LabOrders ON DentEconomics.HistoryID = LabOrders.OrderID)
		LEFT OUTER JOIN LabOrderItems ON LabOrders.OrderID = LabOrderItems.OrderID)
		LEFT OUTER JOIN Operations ON LabOrderItems.OperID = Operations.OperID)
		LEFT OUTER JOIN Patient ON LabOrderItems.PatientID = Patient.PatientID)
		LEFT OUTER JOIN Doctors ON DentEconomics.DentistID = Doctors.DocID
WHERE TransactionType = 1 AND HistoryID > 0
UNION
SELECT DentEconomics.LabID, DentalLabDB.Company, DentEconomics.DentistID, Doctors.LastName AS Doctor, 
			DentEconomics."Date", DentEconomics.Credit, DentEconomics.CreditEURO,
			DentEconomics.ReceiptNo, DentEconomics.ReceiptCredit, DentEconomics.ReceiptCreditEURO, DentEconomics.Sign, 
			CAST('' AS CHAR(40)) AS Description, 0 AS ToothNum, CAST('' AS CHAR(30))  AS LastName, DentEconomics.EconomID
FROM (DentEconomics LEFT OUTER JOIN DentalLabDB ON DentEconomics.LabID = DentalLabDB.DentalLabID)
		LEFT OUTER JOIN Doctors ON DentEconomics.DentistID = Doctors.DocID
WHERE TransactionType = 1 AND (HistoryID IS NULL) AND (Sign = -1)   
UNION
SELECT DentEconomics.LabID, DentalLabDB.Company, DentEconomics.DentistID, Doctors.LastName AS Doctor, 
			DentEconomics."Date", DentEconomics.Credit, DentEconomics.CreditEURO,
			DentEconomics.ReceiptNo, 0 AS ReceiptCredit, 0 AS ReceiptCreditEURO, DentEconomics.Sign, 
			CAST('' AS CHAR(40)) AS Description, 0 AS ToothNum, CAST('' AS CHAR(30))  AS LastName, DentEconomics.EconomID
FROM (DentEconomics LEFT OUTER JOIN DentalLabDB ON DentEconomics.LabID = DentalLabDB.DentalLabID)
		LEFT OUTER JOIN Doctors ON DentEconomics.DentistID = Doctors.DocID
WHERE TransactionType = 1 AND (HistoryID IS NULL) AND (Sign = 1)      
ORDER BY DentEconomics."Date", DentEconomics.EconomID

