public async Task CheckAppointment(int p_id, string app_date, string time, ICheckAppointmentCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
int count = await DBHandler.db.ExecuteScalarAsync <int>(String.Format("SELECT COUNT(*) FROM APPOINTMENT WHERE " +
"PATIENT_ID={0} AND " +
"APP_DATE='{1}' AND START_TIME='{2}'", p_id, app_date, time));
if (count >= 0)
{
callback.CheckAppointmentSuccess(count);
}
else
{
callback.CheckAppointmentFail();
}
}
public async Task GetHospitalByLocationAsync(string name, IHospitalListCallback hospitalCallback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
var results = await DBHandler.db.QueryAsync <Hospital>(String.Format("SELECT * FROM HOSPITAL " +
"WHERE LOCATION='{0}'", name));
if (results != null && results.Count > 0)
{
hospitalCallback.ReadSuccess(results);
//await DoctorDBHandler.db.CloseAsync();
}
else
{
hospitalCallback.ReadFail();
}
}
public async Task UpdateAppointment(int id, string app_date, string time, IUpdateAppCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
try
{
await DBHandler.db.ExecuteAsync(String.Format("UPDATE APPOINTMENT SET " +
"APP_DATE='{0}', START_TIME='{1}' WHERE ID={2}", app_date, time, id));
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Reschedule dao fail " + e.Message);
callback.UpdateAppFail();
return;
}
callback.UpdateAppSuccess(true);
}
public async Task GetLastTestDetail(int doc_id, ILastTestDetailCallback callback)
{
//TestDetails result;
try
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
var patients = await DBHandler.db.Table <Patient>().ToListAsync();
var tests = await DBHandler.db.Table <Testimonial>().ToListAsync();
var details = (from t in tests
join p in patients
on t.Patient_ID equals p.ID
where tests.Any(g => t.Doc_ID == doc_id)
select new TestDetails
{
doc_id = t.Doc_ID,
message = t.message,
patient_name = p.name,
posted_time = t.posted_time,
p_id = p.ID,
test_id = t.ID
}
).OrderBy(x => x.posted_time).Last();
if (details != null)
{
callback.LastTestReadSuccess(details);
}
else
{
callback.LastTestReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Testimonial details select exception=" + e.Message);
}
}
public async Task GetHospitalByDept(string location, int dept_id, IHospitalListCallback hospitalCallback, int rating = -1)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
List <Hospital> results = new List <Hospital>();
try
{
//var db = await dbHandler.DBConnection();
DBHandler.DBConnection();
if (dept_id > 0)
{
results = await DBHandler.db.QueryAsync <Hospital>(String.Format("SELECT * FROM HOSPITAL WHERE ID IN(" +
"SELECT HOSP_ID FROM ROSTER GROUP BY DOC_ID HAVING DOC_ID IN(" +
"SELECT ID FROM DOCTOR WHERE DEPT_ID={0})" +
"AND COUNT(*)>0) AND LOCATION='{1}' AND RATING >={2}", dept_id, location, rating)
);
}
else
{
results = await DBHandler.db.QueryAsync <Hospital>(String.Format("SELECT * FROM HOSPITAL WHERE ID IN(" +
"SELECT HOSP_ID FROM ROSTER GROUP BY DOC_ID HAVING COUNT(*)>0) AND LOCATION='{1}' AND " +
"RATING >={2}", dept_id, location, rating)
);
}
System.Diagnostics.Debug.WriteLine("results=" + results.Count());
if (results != null)
{
hospitalCallback.ReadSuccess(results);
//await DoctorDBHandler.db.CloseAsync();
}
else
{
hospitalCallback.ReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("SELECT EXCEPTION" + e.Message);
}
}
public async Task GetTimeSlots(int doc_id, int hosp_id, string app_date, IRosterCallback callback)
{
try
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
results = await DBHandler.db.QueryAsync <Roster>(String.Format("SELECT *, MAX_PATIENTS-" +
"(SELECT COUNT(*) FROM APPOINTMENT WHERE DOC_ID={0} AND HOS_ID={1} AND APP_DATE='{2}' AND " +
"APPOINTMENT.START_TIME=ROSTER.START_TIME) AS VAL" +
" FROM ROSTER WHERE DOC_ID={0} AND HOSP_ID={1} "
, doc_id, hosp_id, app_date));
//System.Diagnostics.Debug.WriteLine("results=" + DateTime.Now.ToString("HH:mm:ss"));
if (results != null)
{
List <Roster> resultmain = new List <Roster>();
foreach (var x in results)
{
string s = app_date + " " + x.start_time;
DateTime d1 = DateTime.Parse(s);
DateTime d2 = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd HH:mm"));
if (d1.CompareTo(d2) >= 0)
{
resultmain.Add(x);
}
}
callback.RosterReadSuccess(resultmain);
await DBHandler.db.CloseAsync();
}
else
{
callback.RosterReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("DOCTOR ASYNC SELECT EXCEPTION" + e.Message);
}
}
public async Task GetHospitalByNameAsync(string name, string location, IHospitalListCallback hospitalCallback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
var results = await DBHandler.db.QueryAsync <Hospital>(String.Format("SELECT * FROM HOSPITAL " +
"WHERE NAME LIKE'{0}%' AND LOCATION='{1}'", name, location));
if (results != null && results.Count > 0)
{
hospitalCallback.ReadSuccess(results);
//await DoctorDBHandler.db.CloseAsync();
}
else
{
hospitalCallback.ReadFail();
}
System.Diagnostics.Debug.WriteLine("hosp dao val=" + results[0].Number_Of_Rating);
}
public async Task GetDoctorsByDeptCount(int dept, int hosp_id, IDoctorCountByDeptCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
var count = await DBHandler.db.ExecuteScalarAsync <int>(String.Format("select count(*) from roster group by DOC_ID " +
"having DOC_ID in " +
"(select id from DOCTOR where DEPT_ID={0}) " +
"and HOSP_ID={1}", dept, hosp_id)
);
if (count > 0)
{
callback.ReadCountSuccess(count);
}
else
{
callback.ReadCountFail();
}
}
public async Task GetMostBookedDoctor(int id, IMostBookedDoctorCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
List <Doctor> docs = new List <Doctor>();
try
{
//docs=await DBHandler.db.QueryAsync<Doctor>(String.Format("SELECT * FROM DOCTOR WHERE ID IN(" +
//"SELECT DOC_ID FROM APPOINTMENT GROUP BY DOC_ID HAVING PATIENT_ID={0} ORDER BY COUNT(*) DESC)", id));
var doctors = await DBHandler.db.Table <Doctor>().ToListAsync();
var temp = await DBHandler.db.QueryAsync <Appointment>(String.Format(
"SELECT DOC_ID FROM APPOINTMENT GROUP BY DOC_ID HAVING PATIENT_ID={0} ORDER BY COUNT(*) DESC", id));
//var all = doctors.Where(d => temp.Any(t => t.DOC_ID==d.ID));
//foreach (var x in temp)
// System.Diagnostics.Debug.WriteLine(x.DOC_ID);
var all = from t in temp
join d in doctors on t.DOC_ID equals d.ID
select d;
docs = new List <Doctor>(all);
if (docs != null)
{
callback.MostBookedDocReadSuccess(docs);
}
else
{
callback.MostBookedDocReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("most booked doctor dao fail " + e.Message);
}
}
public async Task GetDoctorByHospitalNameAsync(string name, IDoctorCallback doctorCallback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
List <Doctor> results = await DBHandler.
db.QueryAsync <Doctor>(String.Format("SELECT * FROM DOCTOR WHERE ID IN (" +
"SELECT DOC_ID FROM ROSTER WHERE HOSP_ID IN (" +
"SELECT ID FROM HOSPITAL WHERE NAME='{0}'))", name));
System.Diagnostics.Debug.WriteLine("Hosp name " + results.Count());
if (results != null)
{
doctorCallback.ReadSuccess(results);
await DBHandler.db.CloseAsync();
}
else
{
doctorCallback.ReadFail();
}
}
public async Task AddDocSearch(Doc_Search d, IDoc_SearchCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
try
{
int x = await DBHandler.db.InsertAsync(d);
if (x > 0)
{
callback.Doc_SearchInsertSuccess(x);
}
else
{
callback.Doc_SearchInsertFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Doc_Search insert DAO fail");
}
}
public async Task AddTestimonial(int pid, int doc, string message, string time, ITestCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
try
{
await DBHandler.db.ExecuteAsync(String.Format("INSERT INTO TESTIMONIAL (PATIENT_ID,DOC_ID, MESSAGE, POSTED_TIME " +
") VALUES({0},{1},'{2}','{3}')", pid, doc, message, time)
);
//System.Diagnostics.Debug.WriteLine("Appointment insert dao Success");
await DBHandler.db.CloseAsync();
callback.TestReadSuccess();
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Testimonial insert dao exception=" + e.Message);
callback.TestReadFail();
return;
}
}
public async Task UpdateDoctorRating(int id, double rating, IDoctorUpdateCallback doctorCallback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
try
{
await DBHandler.db.ExecuteAsync(String.Format("UPDATE DOCTOR SET RATING=(((RATING*NUMBER_OF_RATING)+{0})/(NUMBER_OF_RATING+1))" +
"WHERE ID={1}", rating, id)
);
System.Diagnostics.Debug.WriteLine("Update rating dao Success");
await DBHandler.db.CloseAsync();
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Update rating dao exception=" + e.Message);
doctorCallback.DoctorUpdateFail();
}
try
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
await DBHandler.db.ExecuteAsync(String.Format("UPDATE DOCTOR SET NUMBER_OF_RATING=NUMBER_OF_RATING+1 " +
"WHERE ID={0}", id)
);
System.Diagnostics.Debug.WriteLine("Update number of rating dao Success");
await DBHandler.db.CloseAsync();
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Update number of rating dao exception=" + e.Message);
doctorCallback.DoctorUpdateFail();
}
try
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
var results = await DBHandler.db.QueryAsync <Doctor>(String.Format("SELECT * FROM DOCTOR " +
"WHERE ID={0}", id));
if (results != null)
{
System.Diagnostics.Debug.WriteLine("Update Select dao Success");
System.Diagnostics.Debug.WriteLine("Update Select rating=" + results[0].Number_of_Rating);
doctorCallback.DoctorUpdateSuccess(results.First());
await DBHandler.db.CloseAsync();
}
else
{
doctorCallback.DoctorUpdateFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Update Select dao exception=" + e.Message);
doctorCallback.DoctorUpdateFail();
}
//await DoctorDBHandler.db.CloseAsync();
}
public async Task GetAppointmentByDoc(int doc_id, int p_id, IAppByDocCallback callback)
{
List <AppointmentDetails> results = new List <AppointmentDetails>();
try
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
var apps = await DBHandler.db.Table <Appointment>().Where(d => d.DOC_ID == doc_id).ToListAsync();
var docs = await DBHandler.db.Table <Doctor>().Where(d => d.ID == doc_id).ToListAsync();
var hosp = await DBHandler.db.Table <Hospital>().ToListAsync();
var details = (from a in apps
join d in docs
on a.DOC_ID equals d.ID
join h in hosp
on a.HOS_ID equals h.ID
where apps.Any(g => g.PATIENT_ID.Equals(p_id))
select new AppointmentDetails
{
app_date = a.APP_DATE,
doc_name = d.Name,
hosp_name = h.Name,
id = a.ID,
location = h.Location,
Timeslot = a.start_time,
img = d.Image
}
).OrderBy(x => x.app_date).ThenBy(x => x.Timeslot);
foreach (var x in details.Where(a => DateTime.Parse(a.app_date) >=
DateTime.Parse(DateTime.Now.Date.ToString("yyyy-MM-dd"))
))
{
if (DateTime.Parse(x.app_date) == DateTime.Parse(DateTime.Now.Date.ToString("yyyy-MM-dd")))
{
if (DateTime.Parse(x.Timeslot).CompareTo(DateTime.Parse(DateTime.Now.TimeOfDay.ToString())) < 0)
{
continue;
}
}
//x.app_date = DateTime.ParseExact(x.app_date, "yyyy-MM-dd", null).ToString("dd/MM/yyyy");
results.Add(x);
}
if (results != null)
{
callback.AppByDocSuccess(results);
}
else
{
callback.AppByDocFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Appointment details select exception=" + e.Message);
}
}