public async Task GetLastHospital(int p_id, int doc_id, ILastHospitalCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
try
{
var results = await DBHandler.db.QueryAsync <Hospital>(String.Format("SELECT * FROM HOSPITAL " +
"WHERE ID in(" +
"SELECT HOS_ID FROM APPOINTMENT WHERE DOC_ID={0} AND PATIENT_ID={1} ORDER BY ID DESC LIMIT 1)", doc_id, p_id));
if (results != null)
{
callback.ILastHospitalSuccess(results.First());
}
else
{
callback.ILastHospitalFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("last hospital dao exception=" + e.Message);
}
}
public async Task GetHospitalsAsync(IHospitalListCallback hospitalCallback)
{
List <Hospital> results = new List <Hospital>();
try
{
//var db = await dbHandler.DBConnection();
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
results = await DBHandler.db.QueryAsync <Hospital>(
"SELECT * FROM HOSPITAL ");
System.Diagnostics.Debug.WriteLine("results=" + results.Count());
if (results != null && results.Count > 0)
{
hospitalCallback.ReadSuccess(results);
await DBHandler.db.CloseAsync();
}
else
{
hospitalCallback.ReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("SELECT EXCEPTION" + e.Message);
}
}
public async Task GetDoctorByNameAsync(string name, string location, IDoctorCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
List <Doctor> results = new List <Doctor>();
try
{
results = await DBHandler.db.QueryAsync <Doctor>(String.Format("SELECT * FROM DOCTOR " +
"WHERE NAME LIKE '{1}%' AND ID IN (" +
"SELECT DOC_ID FROM ROSTER WHERE HOSP_ID IN(" +
"SELECT ID FROM HOSPITAL WHERE LOCATION='{0}')" +
")", location, name));
System.Diagnostics.Debug.WriteLine("Results val=" + results.Count);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Dept loc select exception " + e.Message);
}
if (results != null)
{
callback.ReadSuccess(results);
}
else
{
callback.ReadFail();
}
}
//IDoctorCallback doctorCallback;
public async Task GetDoctorsAsync(IDoctorCallback doctorCallback)
{
List <Doctor> results = new List <Doctor>();
try
{
//var db = await dbHandler.DBConnection();
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
results = await DBHandler.db.QueryAsync <Doctor>(
"SELECT * FROM DOCTOR ");
System.Diagnostics.Debug.WriteLine("results=" + results.Count());
if (results != null)
{
doctorCallback.ReadSuccess(results);
await DBHandler.db.CloseAsync();
}
else
{
doctorCallback.ReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("DOCTOR ASYNC SELECT EXCEPTION" + e.Message);
}
}
public async Task LoginAsync(Logins l, ILoginCallback callback)
{
int count = -1;
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
try
{
await DBHandler.db.InsertAsync(l);
await DBHandler.db.CloseAsync();
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Login DAO fail " + e.Message);
return;
}
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
try
{
count = await DBHandler.db.ExecuteScalarAsync <int>("select count(*) from LOGINS where USER_ID=1");
}
catch (Exception e)
{
callback.GetLoginFail();
return;
}
callback.GetLoginSuccess(count);
}
public async Task GetDoctorByIdAsync(int id, IDoctorDetailCallback doctorCallback)
{
//Doctor d = new Doctor();
try
{
//var db = await dbHandler.DBConnection();
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
List <Doctor> results = await DBHandler.db.QueryAsync <Doctor>(String.Format("SELECT * FROM DOCTOR WHERE ID={0}", id));
if (results != null)
{
doctorCallback.DoctorDetailReadSuccess(results.First());
await DBHandler.db.CloseAsync();
}
else
{
doctorCallback.DoctorDetailReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("DOC BY ID SELECT EXCEPTION" + e.Message);
}
}
public async Task GetKeywordsAsync(IKeyWordCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
List <KeyWord> results = new List <KeyWord>();
try
{
results = await DBHandler.db.QueryAsync <KeyWord>("select * from keywords group by DEPT_ID");
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Keyword DB Exception" + e.Message);
}
if (results != null)
{
callback.KeyWordReadSuccess(results);
}
else
{
callback.KeyWordReadFail();
}
}
public async Task GetDepartmentList(IDepartmentListCallBack callback)
{
List <Department> results = new List <Department>();
try
{
//var db = await dbHandler.DBConnection();
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
results = await DBHandler.db.QueryAsync <Department>(
"SELECT * FROM DEPARTMENT ");
System.Diagnostics.Debug.WriteLine("results=" + results.Count());
if (results != null)
{
callback.DepartmentDataReadSuccess(results);
//await DBHandler.db.CloseAsync();
}
else
{
callback.DepartmentDataReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("DEPARTMENT SELECT EXCEPTION " + e.Message);
}
}
public async Task GetHospitalbyIdAsync(int id, IHospitalDetailCallback hospitalCallback)
{
List <Hospital> results = new List <Hospital>();
try
{
//var db = await dbHandler.DBConnection();
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
results = await DBHandler.db.QueryAsync <Hospital>(String.Format("SELECT * FROM HOSPITAL where ID ={0}", id)
);
System.Diagnostics.Debug.WriteLine("results=" + results.Count());
if (results != null)
{
hospitalCallback.HospitalDetailReadSuccess(results.First());
//await DoctorDBHandler.db.CloseAsync();
}
else
{
hospitalCallback.HospitalDetailReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("SELECT EXCEPTION" + e.Message);
}
}
public async Task GetAppointment(int p_id, IAppointmentListCallback callback)
{
List <AppointmentDetails> results = new List <AppointmentDetails>();
try
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
var apps = await DBHandler.db.Table <Appointment>().ToListAsync();
var docs = await DBHandler.db.Table <Doctor>().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")) && DateTime.Parse(a.Timeslot).CompareTo(
DateTime.Parse(DateTime.Now.TimeOfDay.ToString())) > 0
))
{
//x.app_date = DateTime.ParseExact(x.app_date, "yyyy-MM-dd", null).ToString("dd/MM/yyyy");
results.Add(x);
}
if (results != null)
{
callback.GetAppSuccess(results);
}
else
{
callback.GetAppFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Appointment details select exception=" + e.Message);
}
}
public async Task GetDoctorDetailByHospital(int hospid, IDoctorHospitalCallBack doctorHospitalCallBack)
{
List <DoctorInHospitalDetails> results = new
List <DoctorInHospitalDetails>();
try
{
//var db = await dbHandler.DBConnection();
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
var docs = await DBHandler.db.Table <Doctor>().ToListAsync();
var roster = await DBHandler.db.Table <Roster>().ToListAsync();
//var hosp = await DBHandler.db.Table<Hospital>().ToListAsync();
var details = (from d in docs
join r in roster
on d.ID equals r.doc_id
where r.hosp_id == hospid
select new DoctorInHospitalDetails
{
doc_id = d.ID,
Name = d.Name,
Designation = d.Designation,
Experience = d.Experience,
Start_Time = r.start_time,
End_Time = r.end_time,
fees = r.fee,
Rating = d.Rating,
Number_of_Rating = d.Number_of_Rating,
max_patients = r.max_patients,
Image = d.Image
}
);
results = details.ToList();
//System.Diagnostics.Debug.WriteLine("QUERY COUNT=" + results[0].fees);
if (results != null)
{
results = results.GroupBy(d => d.doc_id).Select(d => d.FirstOrDefault()).ToList();
doctorHospitalCallBack.ReadSuccess(results);
await DBHandler.db.CloseAsync();
}
else
{
doctorHospitalCallBack.ReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("DOCTOR HOSPITAL SELECT EXCEPTION" + e.Message);
}
}
public async Task GetHospitalByDoctor(int id, IHospitalDoctorCallback HospitalDoctorCallBack)
{
List <HospitalInDoctorDetails> results = new
List <HospitalInDoctorDetails>();
try
{
//var db = await dbHandler.DBConnection();
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
//var docs = await DBHandler.db.Table<Doctor>().ToListAsync();
var roster = await DBHandler.db.Table <Roster>().ToListAsync();
var hosp = await DBHandler.db.Table <Hospital>().ToListAsync();
var details = (from h in hosp
join r in roster
on h.ID equals r.hosp_id
where r.doc_id == id
select new HospitalInDoctorDetails
{
Hosp_ID = h.ID,
Name = h.Name,
Location = h.Location,
Start_Time = r.start_time,
End_Time = r.end_time,
Fees = r.fee,
Rating = h.Rating,
Number_of_Rating = h.Number_Of_Rating,
Image = h.Image
}
).Distinct();
results = details.ToList();
if (results != null)
{
results = results.GroupBy(h => h.Hosp_ID).Select(g => g.FirstOrDefault()).ToList();
HospitalDoctorCallBack.ReadSuccess(results);
//await DBHandler.db.CloseAsync();
}
else
{
HospitalDoctorCallBack.ReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("HOSPITAL DOCTOR SELECT EXCEPTION" + e.Message);
}
}
public async Task GetAppointmentByID(string app_date, string time, IAppointmentCallback callback)
{
List <AppointmentDetails> results = new List <AppointmentDetails>();
try
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
var apps = await DBHandler.db.Table <Appointment>().ToListAsync();
var docs = await DBHandler.db.Table <Doctor>().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 a.APP_DATE.Equals(app_date) && a.start_time.Equals(time)
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
}
);
foreach (var x in details)
{
//x.app_date = DateTime.ParseExact(x.app_date, "yyyy-MM-dd", null).ToString("dd/MM/yyyy");
results.Add(x);
}
if (results != null)
{
callback.GetAppByIDSuccess(results.First());
}
else
{
callback.GetAppByIDFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Appointment details select exception=" + e.Message);
}
}
public async Task GetRecentDoctor(int id, IRecentDoctorCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
List <Doctor> results = new List <Doctor>();
try
{
var doctors = await DBHandler.db.Table <Doctor>().ToListAsync();
var doc_searches = await DBHandler.db.Table <Doc_Search>().ToListAsync();
var details = (from doc in doctors
join doc_search in doc_searches
on doc.ID equals doc_search.doc_id
where doc_search.user_id == id
orderby doc_search.id descending
select new Doctor
{
Name = doc.Name,
Description = doc.Description,
Designation = doc.Designation,
Rating = doc.Rating,
Number_of_Rating = doc.Number_of_Rating,
Experience = doc.Experience,
ID = doc.ID,
Image = doc.Image
}
);
//details.Reverse();
foreach (var x in details.GroupBy(d => d.ID).Select(g => g.First()))
{
results.Add(x);
}
if (results != null)
{
callback.RecentDocSuccess(results);
}
else
{
callback.RecentDocFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("SEARCH DOC SELECT EXCEPTION" + e.Message);
}
}
public async Task AddAppointment(int p_id, int doc_id, int hosp_id, string app_date, string start,
IBookAppointmentCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
try
{
await DBHandler.db.ExecuteAsync(String.Format("INSERT INTO APPOINTMENT (PATIENT_ID,DOC_ID, HOS_ID, APP_DATE, " +
"START_TIME) VALUES({0},{1},{2},'{3}','{4}')", p_id, doc_id, hosp_id, app_date, start)
);
//System.Diagnostics.Debug.WriteLine("Appointment insert dao Success");
await DBHandler.db.CloseAsync();
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Appointment insert dao exception=" + e.Message);
callback.AppReadFail();
return;
}
try
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
var results = await DBHandler.db.QueryAsync <Appointment>("SELECT * FROM APPOINTMENT " +
"ORDER BY ID DESC LIMIT 1");
if (results != null && results.Count() > 0)
{
System.Diagnostics.Debug.WriteLine("appointment Select dao Success");
callback.AppReadSuccess(results.First());
await DBHandler.db.CloseAsync();
}
else
{
callback.AppReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("appointment select dao exception=" + e.Message);
callback.AppReadFail();
}
}
public async Task GetTestDetails(int doc_id, ITestDetailsCallback callback)
{
List <TestDetails> results = new List <TestDetails>();
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);
foreach (var x in details)
{
//x.app_date = DateTime.ParseExact(x.app_date, "yyyy-MM-dd", null).ToString("dd/MM/yyyy");
results.Add(x);
}
if (results != null)
{
callback.TestDetailsReadSuccess(results);
}
else
{
callback.TestDetailsReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Testimonial details select exception=" + e.Message);
}
}
public async Task ReminderForApps(int p_id, string app_date, string time, IReminderCallback callback)
{
AppointmentDetails result = new AppointmentDetails();
try
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
var apps = await DBHandler.db.Table <Appointment>().ToListAsync();
var docs = await DBHandler.db.Table <Doctor>().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 a.APP_DATE.Equals(app_date) && DateTime.Parse(a.start_time).Subtract(DateTime.Parse(time)).Minutes == 30 //DateTime.Parse(time)
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
}
);
result = details.FirstOrDefault();
if (result != null)
{
callback.ReminderReadSuccess(result);
}
else
{
callback.ReminderReadFail();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Appointment details select exception=" + e.Message);
}
}
public async Task GetAppointmentByID(int id, IAppCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
var results = await DBHandler.db.Table <Appointment>().Where(a => a.ID == id).ToListAsync();
if (results != null)
{
callback.AppSuccess(results.First());
}
else
{
callback.AppFail();
}
}
public async Task GetDoctorByDeptLocationAsync(string location, int dept, IDoctorCallback doctorCallback,
int lexp = -1, int uexp = 200, int rating = -1)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
List <Doctor> results = new List <Doctor>();
try
{
if (dept > 0)
{
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 LOCATION='{0}')" +
")" +
"AND DEPT_ID ={1} AND EXPERIENCE >= {2} AND EXPERIENCE <= {3} AND RATING >= {4}", location, dept, lexp, uexp, rating));
}
else
{
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 LOCATION='{0}')" +
")" +
" AND EXPERIENCE >= {2} AND EXPERIENCE <= {3} AND RATING >= {4}", location, dept, lexp, uexp, rating));
}
System.Diagnostics.Debug.WriteLine("Results val=" + results.Count);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Dept loc select exception " + e.Message);
}
if (results != null)
{
doctorCallback.ReadSuccess(results);
await DBHandler.db.CloseAsync();
}
else
{
doctorCallback.ReadFail();
}
}
public async Task CancelAppointment(int id, ICancelAppCallback callback)
{
if (DBHandler.db == null)
{
DBHandler.DBConnection();
}
try
{
await DBHandler.db.ExecuteAsync("DELETE FROM APPOINTMENT WHERE ID=" + id);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Cancel dao fail " + e.Message);
callback.CancelAppFail();
return;
}
callback.CancelAppSuccess(true);
}
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 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 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 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 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();
}
}
