public DataTable GetDataTable()
{
string query = @"SELECT [ID]
,[NAME]
,[SURNAME]
,[ADDRESS]
,[PHONE_NUMBER]
,[BOOK_NAME]
,[BOOK_PAGE_NUMBER]
,[CREATED_DATE]
,[MODIFIED_DATE]
,IIF([TREATMENT].[BALANCE] IS NULL, 0, [TREATMENT].[BALANCE]) AS [BALANCE]
FROM PATIENT
LEFT JOIN (
SELECT [PATIENT_ID]
,SUM([PRICE] - [PAID]) AS [BALANCE]
FROM TREATMENT
GROUP BY [PATIENT_ID]
) TREATMENT ON TREATMENT.PATIENT_ID = PATIENT.ID
WHERE [ACTIVE] = @active
ORDER BY [SURNAME], [NAME]";
OleDbParameter pACTIVE = new OleDbParameter("@active", true);
return(_dbService.GetDataTable(query, pACTIVE));
}
public DataTable GetDataTable(DateTime date)
{
string query = "SELECT [ROW_NUMBER], [NAME], [DESCRIPTION], [PHONE_NUMBER], [ROW_NUMBER], [COLOR_NAME], [COLOR_DESCRIPTION], [COLOR_PHONE_NUMBER] FROM APPOINTMENT WHERE [DATE] = @date ORDER BY [ROW_NUMBER]";
OleDbParameter pDATE = new OleDbParameter("@date", date.Date);
return(_dbService.GetDataTable(query, pDATE));
}
public DataTable GetDataTable(int patientId)
{
string query = @"SELECT [ID]
,[CREATED_DATE] AS [DATE]
,[DESCRIPTION]
,[PRICE]
,[PAID]
,[PRICE] - [PAID] AS NET
FROM [TREATMENT]
WHERE [PATIENT_ID] = @id
ORDER BY [CREATED_DATE]";
OleDbParameter pID = new OleDbParameter("@id", patientId);
return(_dbService.GetDataTable(query, pID));
}