public static int AppointmentCount(string officeSequence, DateTime startDate, DateTime endDate) { var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcDental); int count = db.ExecuteScalar <int>($"select Count(1) from _portal_patient_appointment_h Inner JOin __sms_email_log on _portal_patient_appointment_h.Office_Sequence = __sms_email_log.Office_Sequence and _portal_patient_appointment_h.AppointmentCounter = __sms_email_log.Counter where _portal_patient_appointment_h.SystemDate between '{startDate.ToString("yyyy-MM-dd H:mm:ss")}' AND '{endDate.ToString("yyyy-MM-dd H:mm:ss")}' AND _portal_patient_appointment_h.Office_Sequence in (" + officeSequence + ") "); count += db.ExecuteScalar <int>($"select Count(1) from _portal_patient_appointment Inner JOin __sms_email_log on _portal_patient_appointment.Office_Sequence = __sms_email_log.Office_Sequence and _portal_patient_appointment.AppointmentCounter = __sms_email_log.Counter where _portal_patient_appointment.SystemDate between '{startDate.ToString("yyyy-MM-dd H:mm:ss")}' AND '{endDate.ToString("yyyy-MM-dd H:mm:ss")}' AND _portal_patient_appointment.Office_Sequence in (" + officeSequence + ") "); return(count); }
public static int AverageCountBeforeDate(int office_sequence, DateTime startDate) { // ltcdental var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcDental); return(db.ExecuteScalar <int>($"SELECT Count(ReviewID) FROM _review_patient_response where _review_patient_response.Office_Sequence = {office_sequence} And Rating > 0 AND (Month(ReviewDate) < Month('{startDate.ToString("yyyy-MM-dd H:mm:ss")}') AND Month(ReviewDate) < Month('{startDate.ToString("yyyy-MM-dd H:mm:ss")}') AND Year(ReviewDate) AND Month('{startDate.ToString("yyyy-MM-dd H:mm:ss")}'))")); }
public static int Count(int office_sequence, string ReviewType, DateTime startDate, DateTime endDate) { // ltcdental var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcDental); return(db.ExecuteScalar <int>($"SELECT Count(ReviewID) FROM _review_patient_response where _review_patient_response.Office_Sequence = {office_sequence} And ReviewType = '{ReviewType}' AND ReviewDate between '{startDate.ToString("yyyy-MM-dd H:mm:ss")}' AND '{endDate.ToString("yyyy-MM-dd H:mm:ss")}'")); }
//SELECT ltc_dental.patient.Account,Doctor,LastName,FirstName,Sex,Birthdate,LastVisit,BestPhone FROM ltc_dental.patient; public static int GetPatientCount(string connectionString, int OfficeId, string search) { var db = new LTCDataModel.PetaPoco.Database(connectionString, "MySql"); int count = db.ExecuteScalar <int>( @"SELECT Count(1) FROM patient pt where ( pt.PatientNumber like '%" + search + "%' OR pt.Doctor like '%" + search + "%' or pt.LastName like '%" + search + "%' OR pt.FirstName like '%" + search + "%' OR pt.Sex like '%" + search + "%' OR pt.Birthdate like '%" + search + "%' OR pt.LastVisit like '%" + search + "%') AND pt.Office_sequence=" + OfficeId); return(count); }
public static int DailyRecallCount(string officeSequence) { // ltcdental var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcDental); var res = db.ExecuteScalar <int?>($"select Sum(sendcount) from appointbookingstatsbyuserdetail where appointbookingstatsbyuserdetail.Office_sequence in (" + officeSequence + ") and DATE(auditdate) = CURDATE() and (smstype = 3) "); if (res == null) { return(0); } return(res.Value); }