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);
}
