public static List <gReviewPatientResponse> LoadReviews(int office_sequence, DateTime startDate, DateTime endDate)
{
// ltcdental
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcDental);
return(db.Fetch <gReviewPatientResponse>($"SELECT _review_patient_response.*, patient.lastname, patient.firstname FROM _review_patient_response Left Outer join patient on _review_patient_response.Patient_number = patient.patientnumber where _review_patient_response.ReviewType <> 'LO' AND _review_patient_response.Office_Sequence = {office_sequence} AND ReviewDate between '{startDate.ToString("yyyy-MM-dd H:mm:ss")}' AND '{endDate.ToString("yyyy-MM-dd H:mm:ss")}' Order By ReviewDate ").ToList());
}
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")}'"));
}
public List <gFormReportModel> GetSurveyReport(int officeId)
{
var dbprivateSurvey = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm);
var result = dbprivateSurvey.Fetch <gFormReportModel>($"select * from form_private where IsSurveyForm = 1 AND Office_Sequence=" + officeId).ToList();
//string qry = $"select IP_Address,DB_Name,DB_Port from authentication_office_ip where Office_sequence = {officeId}";
//var detail = dbprivateSurvey.Fetch<AuthenticationOfficeIp>(qry).FirstOrDefault();
//var connectionString = "Server=" + detail.IP_Address + ";userid=ltcuser;password="******";database=" + detail.DB_Name + ";Port=" + detail.DB_Port + ";Convert Zero Datetime=True;SslMode=none;Connection Timeout=190;";
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm);
var surveyAnswers = db.Fetch <gFormReportModel>($"SELECT count(SavedFormID) 'Count', MAX(SavedFormID) 'SavedFormID' FROM _survey_saved where Office_Sequence = {officeId} group by SavedFormID").ToList();
for (int i = 0; i < surveyAnswers.Count; i++)
{
var item = result.FirstOrDefault(x => x.SavedFormID == surveyAnswers[i].SavedFormID);
if (item != null)
{
surveyAnswers[i].Description = item.Description;
}
}
return(surveyAnswers);
}
public List <gFormReportModel> GetFormReport(int officeId)
{
var dbprivateSurveyDb = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcSystem);
var dbprivateSurvey = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm);
var result = dbprivateSurvey.Fetch <gPrivateFormModel>($"select * from form_private where IsSurveyForm = 0 AND Office_Sequence=" + officeId).ToList();
string qry = $"select IP_Address,DB_Name,DB_Port from authentication_office_ip where Office_sequence = {officeId}";
var detail = dbprivateSurveyDb.Fetch <AuthenticationOfficeIp>(qry).FirstOrDefault();
var connectionString = "Server=" + detail.IP_Address + ";userid=ltcuser;password="******";database=" + detail.DB_Name + ";Port=" + detail.DB_Port + ";Convert Zero Datetime=True;SslMode=none;Connection Timeout=190;";
// TODO
//var db = new LTCDataModel.PetaPoco.Database(connectionString, "MySql");
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm);
var formAnswers = db.Fetch <gFormReportModel>($"SELECT count(SavedFormID) 'Count', MAX(SavedFormID) 'SavedFormID' FROM _form_saved where Office_Sequence = {officeId} group by SavedFormID").ToList();
for (int i = 0; i < formAnswers.Count; i++)
{
var item = result.FirstOrDefault(x => x.FormID == formAnswers[i].SavedFormID);
if (item != null)
{
formAnswers[i].Description = item.Description;
}
}
return(formAnswers);
//var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcGateway);
//return db.Fetch<gFormReportModel>($"SELECT count(SS.FormID) Count ,SP.Description FROM form_saved SS LEFT JOIN form_private SP ON SS.FormID=SP.FormID where SS.Office_Sequence={officeId} group by SP.FormID;").ToList();
}
public List <gPrivateFormModel> GetAllPrivateForm(int officeId)
{
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm);
return(db.Fetch <gPrivateFormModel>($"select * from form_private where IsSurveyForm = 0 AND Office_Sequence=" + officeId).ToList());
//return db.Fetch<gPrivateFormModel>($"select * from form_private").ToList();
}
public void SavePublicDesign(gPrivateFormModel model, int officeId)
{
using (var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm))
{
int fid = model.FormID;
gPublicFormModel found = db.Fetch <gPublicFormModel>($"select * from form_public where FormID={fid}").FirstOrDefault();
if (found != null)
{
found.Description = model.Description;
found.SystemDate = DateTime.Now;
found.Content = model.Content;
found.IsActive = 1;
found.IsSurveyForm = 0;
db.Update(found, fid);
}
else
{
//Save Form Design Object
gPublicFormModel design = new gPublicFormModel();
design.Description = model.Description;
design.SystemDate = DateTime.Now;
design.Content = model.Content;
design.IsActive = 1;
design.IsSurveyForm = 0;
db.Save(design);
}
}
}
public static List <gSMS> LoadPreConfirmationByDate(string officeSequence, DateTime startDate, DateTime endDate)
{
// ltcdental
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcDental);
return(db.Fetch <gSMS>($"select sendcount, auditdate from appointbookingstatsbyuserdetail where (smstype = 11 OR smstype = 12) AND auditdate between '{startDate.ToString("yyyy-MM-dd H:mm:ss")}' AND '{endDate.ToString("yyyy-MM-dd H:mm:ss")}' AND Office_Sequence in (" + officeSequence + ") ").ToList());
}
public static List <gDentalProvider> GetProviders(int userId)
{
try
{
List <gDentalProvider> providerList = new List <gDentalProvider>();
var allowedOffices = Utility.GetAllowedOffices(userId);
foreach (var item in allowedOffices)
{
var providerDB = new LTCDataModel.PetaPoco.Database(Utility.CreateConnectionString(item), "MySql");
if (item.Providerrange.Trim() == "*")
{
var list = providerDB.Fetch <gDentalProvider>($"SELECT Provider,Name,hygienist,Office_Sequence FROM provider WHERE ActiveProvider=1 AND Office_Sequence = " + item.Office_Sequence).ToList();
Utility.RemoveDuplicates(ref providerList, list);
}
else
{
var list = providerDB.Fetch <gDentalProvider>($"SELECT Provider,Name,hygienist,Office_Sequence FROM provider WHERE ActiveProvider=1 AND Provider in (" + item.Providerrange.TrimEnd(',') + ") AND Office_Sequence = " + item.Office_Sequence).ToList();
Utility.RemoveDuplicates(ref providerList, list);
}
}
return(providerList);
}
catch (Exception ex)
{
//throw;
return(null);
}
}
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 List <gPendingSurveyViewModel> GetSurveys(string connectionString, int OfficeId)
{
var q = @"SELECT
ss.SavedFormID,ss.Content,ss.SystemDate,pt.FirstName,pt.LastName
from _survey_saved ss left join patient pt on ss.PatientNumber=pt.PatientNumber where ss.SurveyProcessed=0 and ss.Office_sequence=" + OfficeId;
var db = new LTCDataModel.PetaPoco.Database(connectionString, "MySql");
var results = db.Fetch <gPendingSurveyModel>(q).ToList();
var newResults = new List <gPendingSurveyViewModel>();
foreach (var item in results)
{
newResults.Add(new gPendingSurveyViewModel
{
SavedFormID = item.SavedFormID,
PatientName = item.FirstName + " " + item.LastName,
Content = item.Content,
SystemDate = item.SystemDate.ToString("MM/dd/yyyy")
});
}
return(newResults);
}
/// <summary>
/// TODO: the tables in the query are missing in ltcreview db
/// </summary>
/// <param name="office_sequence"></param>
/// <returns></returns>
public static List <gReviewOfficeSetting> GetAllOfficeReviewLinks(int office_sequence)
{
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcReview);
return(db.Fetch <gReviewOfficeSetting>(@"select review_office_settings.Office_Sequence , review_office_settings.Review_Link_Type, review_link_source.Review_Link_Name FROM review_office_settings Inner JOIN review_link_source on
review_office_settings.Review_Link_Type = review_link_source.Review_Link_Type
where Office_Sequence = " + office_sequence));
}
public static List <gAppointments> LoadAppointments(string officeSequence, DateTime startDate, DateTime endDate, int PageIndex = 1, int PageSize = 10)
{
// ltcdental
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcDental);
return(db.Fetch <gAppointments>($"select _portal_patient_appointment_h.SystemDate, _portal_patient_appointment_h.Name , _portal_patient_appointment_h.Office_Sequence, _portal_patient_appointment_h.ActionType, _portal_patient_appointment_h.AppointmentDate, _portal_patient_appointment_h.AppointmentTime, _portal_patient_appointment_h.ResponseFromPatient, _portal_patient_appointment_h.ActionDone, __sms_email_log.EMailSendDate, __sms_email_log.SMSSendDate 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 + ") union all " +
$"select _portal_patient_appointment.SystemDate,_portal_patient_appointment.Name , _portal_patient_appointment.Office_Sequence, _portal_patient_appointment.ActionType, _portal_patient_appointment.AppointmentDate, _portal_patient_appointment.AppointmentTime, _portal_patient_appointment.ResponseFromPatient, _portal_patient_appointment.ActionDone, __sms_email_log.EMailSendDate, __sms_email_log.SMSSendDate 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 + ") LIMIT " + PageSize + " OFFSET " + PageIndex).ToList());
}
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 void DeletePublicTags(int Id)
{
using (var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm))
{
db.Delete("form_tag_public", "TagID", new gFormPublicTag {
TagID = Id
});
}
}
public void DeletePublicDesign(int Id)
{
using (var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm))
{
db.Delete("form_public", "FormID", new gPublicFormModel {
FormID = Id
});
}
}
public List <gFormAnswerWithPrivateForm> GetFormsAnswers(int officeId)
{
//var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcGateway);
//return db.Fetch<gFormAnswerWithPrivateForm>($"select SS.*, SP.Description as FormDescription from form_saved ss Left Join form_private SP on SS.FormID = SP.FormID where SS.Office_Sequence = " + officeId).ToList();
// var db = new LTCDataModel.PetaPoco.Database(connectionString, "MySql");
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm);
return(db.Fetch <gFormAnswerWithPrivateForm>($"select * from _form_saved where Office_Sequence = " + officeId).ToList());
}
private bool IsFormAlreadyExist(gFormSavedModel model)
{
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm);
var id = db.Fetch <int>($"SELECT SavedFormID FROM _form_saved where office_sequence={model.Office_Sequence} and FormID={model.FormID} and PatientNumber={model.PatientNumber}").FirstOrDefault();
if (id == 0)
{
return(false);
}
return(true);
}
//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 string SaveFormAnswer(gFormSavedModel model)
{
if (!IsFormAlreadyExist(model))
{
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm);
model.SystemDate = DateTime.Now;
db.Save(model);
return("Form Saved Succefully");
}
return("Form Already Exists");
}
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);
}
public List <gDentalProvider> GetProviders(string connectionString)
{
try
{
var db = new LTCDataModel.PetaPoco.Database(connectionString, "MySql");
return(db.Fetch <gDentalProvider>($"select Provider,Name,hygienist FROM provider WHERE ActiveProvider=1").ToList());
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// This method should cache the authentication_office_list table detail rather than hitting the database on every call,
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static List <gOfficelist> GetAllowedOffices(int userId)
{
try
{
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcSystem);
var allowedOffices = db.Fetch <gOfficelist>($"SELECT AOL.Office_Sequence, AOL.Providerrange,AOI.IP_Address, AOI.DB_Name,AOI.DB_Port FROM authentication_office_list AOL LEFT JOIN authentication_office_ip AOI ON AOL.office_sequence = AOI.office_sequence WHERE AOL.UserID ={userId}").ToList();
return(allowedOffices);
}
catch (Exception ex)
{
throw;
}
}
public static List <gSearchPatientViewModel> GetPatients(string connectionString, int OfficeId, string search, int PageIndex = 1, int PageSize = 10)
{
var db = new LTCDataModel.PetaPoco.Database(connectionString, "MySql");
var newResults = new List <gSearchPatientViewModel>();
try
{
var q =
@"SELECT pt.Office_sequence,pt.Account,pt.PatientNumber,pt.Doctor,pt.LastName,pt.FirstName,pt.Sex,pt.Birthdate,pt.LastVisit,pt.BestPhone,
(Select phonenumber from patientphone ph where PatientNumber = pt.PatientNumber AND phoneType='P' AND Office_sequence=" +
OfficeId + @") AS PhoneNumber,
(Select phonenumber from patientphone ph where PatientNumber = pt.PatientNumber AND phoneType='W' AND Office_sequence=" +
OfficeId + @") As MobilePhone
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 + " LIMIT " +
PageSize +
" OFFSET " +
PageIndex;
var results = db.Fetch <gSearchPatient>(q).ToList();
foreach (var item in results)
{
newResults.Add(new gSearchPatientViewModel
{
Account = item.Account,
PatientNumber = item.PatientNumber,
Doctor = item.Doctor,
Name = item.FirstName + " " + item.LastName,
Sex_Age = item.Sex.ToUpper() + "" + CalculateAge(item.Birthdate),
Office_Sequence = item.Office_Sequence,
Birthdate = item.Birthdate.ToString("MM/dd/yyyy"),
LastVisit = item.LastVisit.ToString("MM/dd/yyyy"),
HomePhone = item.PhoneNumber,
CellPhone = item.MobilePhone
});
}
}
catch (Exception ex)
{
}
finally
{
db.CloseSharedConnection();
}
return(newResults);
}
//[Obsolete]
//public static int GetOfficeId(int UserId)
//{
// var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcSystem);
// return db.Fetch<int>(@"select Office_Sequence from authentication where Id=" + UserId).FirstOrDefault();
//}
public static List <gBusinesInfo> GetOffices(int userId)
{
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcSystem);
List <gBusinesInfo> offices = new List <gBusinesInfo>();
var allowedOfficeList = db.Fetch <string>($"SELECT Office_Sequence FROM authentication_office_list where UserId = " + userId).ToList();
//TODO: if allowed list is empty. then handle it properly, right now it throws an error
if (allowedOfficeList != null)
{
offices = db.Fetch <gBusinesInfo>($"SELECT Office_Sequence as Id,ClinicName FROM businessInfo where Office_Sequence in (" + string.Join(",", allowedOfficeList) + ")").ToList();
}
return(offices);
}
public void SavePrivateTag(gFormPrivateTag model)
{
using (var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm))
{
gFormPrivateTag found = db.Fetch <gFormPrivateTag>($"select * from form_tag_private where TagID={model.TagID}").FirstOrDefault();
if (found != null)
{
db.Update(model, model.TagID);
}
else
{
db.Save(model);
}
}
}
public void SavePublicTag(gPublicTagModel model)
{
model.IsSurveyForm = 1;
using (var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm))
{
gPublicTagModel found = db.Fetch <gPublicTagModel>($"select * from form_tag_public where TagID={model.TagID} ").FirstOrDefault();
if (found != null)
{
db.Update(model, model.TagID);
}
else
{
db.Save(model);
}
}
}
public static List <gPatient> GetPatientDetail(string connectionString, int officeSequence, int patientNumber)
{
var db = new LTCDataModel.PetaPoco.Database(connectionString, "MySql");
var result = new List <gPatient>();
try
{
result = db.Fetch <gPatient>($"Select patientnumber,account,title,title, lastname, firstname, initial,birthdate,sex,doctor from patient where office_Sequence = {officeSequence} and PatientNumber = {patientNumber}");
}
catch (Exception ex)
{
}
finally
{
db.CloseSharedConnection();
}
return(result);
}
public static List <gPatientPhone> GetPatientPhone(string connectionString, int officeSequence, int patientNumber)
{
var db = new LTCDataModel.PetaPoco.Database(connectionString, "MySql");
var result = new List <gPatientPhone>();
try
{
result = db.Fetch <gPatientPhone>($"Select phonetype,areacode,phonenumber,extension,bestcalltimefrom,bestcalltimeto,PhoneCounter from patientphone where office_Sequence = {officeSequence} and PatientNumber = {patientNumber}");
}
catch (Exception ex)
{
}
finally
{
db.CloseSharedConnection();
}
return(result);
}
public static List <gAddress> GetPatientAddress(string connectionString, int officeSequence, int patientNumber)
{
var db = new LTCDataModel.PetaPoco.Database(connectionString, "MySql");
var result = new List <gAddress>();
try
{
}
catch (Exception ex)
{
}
finally
{
db.CloseSharedConnection();
}
return(db.Fetch <gAddress>($"select A.Office_sequence, A.title, A.lastname, A.firstname, A.initial, A.street, A.street2, A.street3, A.citycode, A.postalcode, A.note, A.AddrCounter, A.PostCounter, C.line1 from address A LEFT JOIN code C on A.citycode = C.code where A.office_Sequence = {officeSequence} and A.PatientNumber = {patientNumber} AND A.type='-'"));
//return db.Fetch<gAddress>($"select Office_sequence, title, lastname, firstname, initial, street, street2, street3, citycode, postalcode, note, AddrCounter, PostCounter from address where office_Sequence = {officeSequence} and PatientNumber = {patientNumber}");
}
public static string GetConnectionStringByOfficeId(int officeSequence)
{
try
{
////TODO: Start Replace this code with [GetAllowedOffices] method as that will get the records from cache in future.
//Start
var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcSystem);
var connectionString = db.Fetch <gOfficelist>($"SELECT * FROM authentication_office_ip WHERE Office_sequence = {officeSequence}").FirstOrDefault();
//END
if (connectionString != null)
{
return(CreateConnectionString(connectionString));
}
else
{
return(Config.FallbackConnection);// ConfigurationManager.AppSettings["FallbackConnection"];
}
}
catch (Exception ex)
{
throw;
}
}
