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); } }
//[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 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 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 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 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 <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 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); }
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); }
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 <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 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 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; } }
public gOfficeSummary GetOfficeSummary(int officeId) { using (var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcDental)) { var result = db .Fetch <gOfficeSummary>( $"Select * from _portal_office_summary where Office_Sequence = {officeId} and SummaryPeriod = 'CD' and CalculationDate = current_date()") .FirstOrDefault(); if (result != null) { if (result.TotalCharge != null) { result.TotalCharge = result.TotalCharge / 100; } if (result.TotalPayment != null) { result.TotalPayment = result.TotalPayment / 100; } } return(result); } }
//public static gBusinesInfo GetOfficeDetailByOfficeId(int OfficeSequence) //{ // var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcGateway); // return db.Fetch<gBusinesInfo>($"select b.Office_Sequence as Id,b.Business_Name as ClinicName FROM authentication_businessinfo b WHERE b.Office_Number ={OfficeSequence} ").FirstOrDefault(); //} public static gOffice GetOfficeName(string connectionString, int officeSequence) { var db = new LTCDataModel.PetaPoco.Database(connectionString, "MySql"); return(db.Fetch <gOffice>($"SELECT Office_sequence, name FROM office WHERE Office_sequence=" + officeSequence).FirstOrDefault()); }
public static gBusinesInfo GetBuisnessInfo(string syncIdentifier) { var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcSystem); return(db.Fetch <gBusinesInfo>($"select Office_Sequence as Id,ClinicName, as ClinicName, Active, Newsletter from businessinfo WHERE Office_Sequence={syncIdentifier} ").FirstOrDefault()); }
//public static gOfficeInfo GetOfficeNames(int OfficeNumber) //{ // var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcSystem); // return db.Fetch<gOfficeInfo>($"SELECT * FROM businessInfo where Office_Sequence = '{OfficeNumber}' ").FirstOrDefault(); //} //public static gPatientOfficeInfo GetOffice(string email) //{ // var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcSystem); // return db.Fetch<gPatientOfficeInfo>($"select Office_Number, DoctorID from authentication where Email = '{email.Replace("@", "@@")}' ").FirstOrDefault(); //} //public static gPatientOfficeInfo GetPatientInfo(string email) //{ // var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcSystem); // return db.Fetch<gPatientOfficeInfo>($"select * from authentication where Email = '{email.Replace("@", "@@")}' ").FirstOrDefault(); //} //public static List<gBusinesInfo> GetOfficeDetailByUserId(int userId) //{ // var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcGateway); // return db.Fetch<gBusinesInfo>($"select a.Office_Sequence as Id,b.Business_Name as ClinicName from authentication_office_list a INNER JOIN authentication_BusinessInfo b ON a.Office_Sequence=b.Office_Sequence where a.UserId={userId} ").ToList(); //} public static List <gBusinesInfo> GetOfficeDetailByOfficeSequence(int officeSequence) { var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcSystem); return(db.Fetch <gBusinesInfo>($"select a.Office_Sequence as Id,b.ClinicName as ClinicName from businessinfo WHERE Office_Sequence={officeSequence} ").ToList()); }
public static gOfficeInfo GetOfficeName(int OfficeNumber) { var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcSystem); return(db.Fetch <gOfficeInfo>($"SELECT * FROM businessinfo where Office_Sequence = '{OfficeNumber}' ").FirstOrDefault()); }
public List <gFormPublicTag> GetPublicTags() { var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm); return(db.Fetch <gFormPublicTag>($"SELECT STP.*, SC.Description as CategoryDescription FROM form_tag_public STP Left Join form_category SC on STP.CategoryID = SC.CategoryID Where STP.IsSurveyForm = 0 ").ToList()); }
public gPublicFormModel GetPublicFormDesign(int formid) { var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm); return(db.Fetch <gPublicFormModel>($"select * from form_public where FormID={formid}").FirstOrDefault()); }
public List <gPublicFormModel> GetAllPublicForm() { var db = new LTCDataModel.PetaPoco.Database(DbConfiguration.LtcForm); return(db.Fetch <gPublicFormModel>($"select * from form_public where IsSurveyForm = 0 ").ToList()); }