/// <summary> /// /// </summary> /// <param name="offices"></param> /// <param name="providerList">This list contains entries with [OfficeSequence]_[Providers]</param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public List <gBreakdown> GetTotalHygenistProductionBreakdown(int[] offices, string[] providerList, string startDate, string endDate, string types) { types = Utility.StringToCharacterString(types, _configuration.TotalHygenistProductionInvoiceTypes); List <gBreakdown> result = new List <gBreakdown>(); var providers = Utility.ProviderToList(providerList); foreach (var office in offices) { if (providers.All(x => x.Office_Sequence != office)) { continue; } var db = PocoDatabase.DbConnection(Utility.GetConnectionStringByOfficeId(office)); string query = $"SELECT i.Office_sequence,i.patientNumber,i.invoicedate,i.patamount/100 patamount ,i.insamount/100 insamount,i.provider,i.invoiceType, lastname, firstname,title FROM item i LEFT JOIN patient P on i.patientnumber = P.patientnumber AND i.Office_sequence = P.Office_sequence " + $"where provider in (SELECT provider FROM provider WHERE provider in ({Utility.FilterProviderToString(office, providers)}) AND i.Office_sequence = {office} AND activeProvider = 1 AND hygienist in ({Utility.HygenistType("TotalHygenistProductionType")})) and " + $"invoiceType in ({types}) and invoicedate >= '{startDate}' AND invoicedate <= '{endDate}' " + $"AND i.Office_sequence = {office} order by invoicedate,invoiceType"; var model = db.Fetch <gBreakdown>(query); result.AddRange(model); } return(AddInvoiceType(result)); }
/// <summary> /// /// </summary> /// <param name="offices"></param> /// <param name="providerList">This list contains entries with [OfficeSequence]_[Providers]</param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public decimal GetTotalHygenistProduction(int[] offices, string[] providerList, string startDate, string endDate, string types) { types = Utility.StringToCharacterString(types, _configuration.TotalHygenistProductionInvoiceTypes); decimal result = 0; var providers = Utility.ProviderToList(providerList); foreach (var office in offices) { if (providers.All(x => x.Office_Sequence != office)) { continue; } var db = PocoDatabase.DbConnection(Utility.GetConnectionStringByOfficeId(office)); string query = $"SELECT IFNULL(sum(patamount/100) + sum(insamount/100),0) as amount FROM item " + $"where provider in (SELECT provider FROM provider WHERE provider in ({Utility.FilterProviderToString(office, providers)}) AND Office_sequence = {office} AND activeProvider = 1 AND hygienist in ({Utility.HygenistType("TotalHygenistProductionType")})) and " + $"invoiceType in ({types}) and invoicedate >= '{startDate}' AND invoicedate <= '{endDate}' " + $"AND Office_sequence = {office}"; var model = db.ExecuteScalar <decimal>(query); result += model; } return(result); }
public static void DeleteChart(int chartId, int userId, int officeId) { using (var db = PocoDatabase.DbConnection(DbConfiguration.LtcDashboard)) { //db.Delete("user_charts", "ID", new gUserCharts { User_Id = userId, Chart_Id = chartId }, null); db.Execute($"SET SQL_SAFE_UPDATES = 0; DELETE FROM user_charts WHERE User_Id = {userId} AND Chart_Id={chartId}; SET SQL_SAFE_UPDATES = 1;"); } }
public List <gPatientAppointment> LoadPatientAppointment(int id, int officeSequence, string startDate, string endDate) { var db = PocoDatabase.DbConnection(DbConfiguration.LtcDental); string query = $"select invoicedate,provider,(patamount/100)+(insamount/100) as Fee from item WHERE patientNumber={id} AND invoicedate >= '{startDate}' AND invoicedate <= '{endDate}' AND Office_sequence = {officeSequence}"; var model = db.Fetch <gPatientAppointment>(query); return(model); }
public static void AddUserChart(int chartId, int userId, int officeId, string defaultFilterTypes) { using (var db = PocoDatabase.DbConnection(DbConfiguration.LtcDashboard)) { var model = db.Fetch <gUserCharts>($"SELECT * FROM user_charts WHERE User_Id = {userId} AND Chart_Id={chartId}").FirstOrDefault(); if (model == null) { gUserCharts userChart = new gUserCharts(); userChart.User_Id = userId; userChart.Chart_Id = chartId; userChart.FilterTypes = defaultFilterTypes; db.Save(userChart); } } }
public static void UpdateCardFilterTypes(int chartId, string filterTypes, int officeId) { using (var db = PocoDatabase.DbConnection(DbConfiguration.LtcDashboard)) { var model = db.Fetch <gUserCharts>($"SELECT * FROM user_charts WHERE Chart_Id={chartId}").FirstOrDefault(); if (model != null) { gUserCharts userChart = new gUserCharts(); userChart.User_Id = model.User_Id; userChart.ID = model.ID; userChart.Chart_Id = model.Chart_Id; userChart.FilterTypes = filterTypes; db.Save(userChart); } } }
/// <summary> /// /// </summary> /// <param name="offices"></param> /// <param name="providerList">This list contains entries with [OfficeSequence]_[Providers]</param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public string GetCancellationAndNoShows(int[] offices, string[] providerList, string startDate, string endDate, string types) { types = Utility.StringToCharacterString(types, _configuration.CancellationAndNoShowsStatusTypes); List <gCode> codes = new List <gCode>(); List <object> serviceAnalysisList = new List <object>(); var providers = Utility.ProviderToList(providerList); foreach (var office in offices) { if (providers.All(x => x.Office_Sequence != office)) { continue; } var db = PocoDatabase.DbConnection(Utility.GetConnectionStringByOfficeId(office)); //string query = $"SELECT contactdate as xLabel, count(r.patientNumber) as yValue FROM recall r " // + $"LEFT JOIN provider p on r.provider = p.provider " // + $"LEFT JOIN patient pt on r.patientNumber = pt.patientnumber " // + $"WHERE r.provider in (SELECT provider FROM provider WHERE provider in ({Utility.FilterProviderToString(office, providers)}) AND Office_sequence = {office} AND activeProvider = 1 AND hygienist in ({Utility.HygenistType("CancellationAndNoShowsTypes")})) " // + $"AND status in ({types}) " // + $"AND r.contactdate >= '{startDate}' " // + $"AND r.contactdate <= '{endDate}' " // + $"AND r.Office_sequence = {office} " // + $"Group By r.office_sequence, contactdate "; string query = $"SELECT DATE_FORMAT(contactdate, \"%Y/%m\") as contactDate, count(r.patientNumber) as count, status FROM recall r " + $"LEFT JOIN provider p on r.provider = p.provider " + $"LEFT JOIN patient pt on r.patientNumber = pt.patientnumber " + $"WHERE r.provider in (SELECT provider FROM provider WHERE provider in ({Utility.FilterProviderToString(office, providers)}) AND Office_sequence = {office} AND activeProvider = 1 AND hygienist in ({Utility.HygenistType("CancellationAndNoShowsTypes")})) " + $"AND status in ({types}) " + $"AND r.contactdate >= '{startDate}' " + $"AND r.contactdate <= '{endDate}' " + $"AND r.Office_sequence = {office} " + $"Group By r.office_sequence, DATE_FORMAT(contactdate, '%m %Y'), r.status " + $""; var seAnalysis = db.Fetch <object>(query); serviceAnalysisList.AddRange(seAnalysis); } return(JsonConvert.SerializeObject(serviceAnalysisList, Formatting.Indented)); }
public gPatientProfile GetPatientProfile(int patientNumber, int officeSequence) { var db = PocoDatabase.DbConnection(DbConfiguration.LtcDental); string query = "" + $"SELECT (Select CONCAT(street, '<br />', street2, '<br />', street2, '<br />', postalcode) FROM address WHERE Office_sequence = {officeSequence} AND patientNumber = {patientNumber}) as Address, " + $"(Select phonenumber from patientphone ph WHERE PatientNumber = pt.PatientNumber AND phoneType = 'P' AND Office_sequence = {officeSequence} AND patientNumber = {patientNumber}) AS HomeNumber, " + $"(Select phonenumber from patientphone ph WHERE PatientNumber = pt.PatientNumber AND phoneType = 'W' AND Office_sequence = {officeSequence} AND patientNumber = {patientNumber}) As WorkPhone, " + $"(Select phonenumber from patientphone ph WHERE PatientNumber = pt.PatientNumber AND phoneType = 'O' AND Office_sequence = {officeSequence} AND patientNumber = {patientNumber}) As OtherPhone, " + $"DATEDIFF(now(), birthdate) / 365.25 as Age, " + $"sex, " + $"maritalstatus, " + $"'' as InsuranceProvider " + $"FROM patient pt WHERE Office_sequence = {officeSequence} AND patientNumber = {patientNumber}"; var model = db.Fetch <gPatientProfile>(query).FirstOrDefault(); return(model); }
/// <summary> /// /// </summary> /// <param name="offices"></param> /// <param name="providerList">This list contains entries with [OfficeSequence]_[Providers]</param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public decimal GetAverageProductionPerPatient(int[] offices, string[] providerList, string startDate, string endDate, string types) { types = Utility.StringToCharacterString(types, _configuration.TotalAverageProductionPerPatientInvoiceTypes); decimal paidAmount = 0; decimal patientCount = 0; var providers = Utility.ProviderToList(providerList); foreach (var office in offices) { if (providers.All(x => x.Office_Sequence != office)) { continue; } var db = PocoDatabase.DbConnection(Utility.GetConnectionStringByOfficeId(office)); string query = $"SELECT IFNULL(sum(patamount/100) + sum(insamount/100),0) as amount FROM item " + $"where provider in (SELECT provider FROM provider WHERE provider in ({Utility.FilterProviderToString(office, providers)}) AND Office_sequence = {office} AND activeProvider = 1 AND hygienist in ({Utility.HygenistType("TotalAverageProductionPerPatientType")})) and " + $"invoiceType in ({types}) and invoicedate >= '{startDate}' AND invoicedate <= '{endDate}' " + $"AND Office_sequence = {office}"; var model = db.ExecuteScalar <decimal>(query); string numberOfPatientsQuery = $"SELECT count(accountopendate) as result FROM patient where activepatient=1 AND accountopendate >= '{startDate}' AND accountopendate <= '{endDate}' AND office_sequence = {office} AND doctor in ({Utility.FilterProviderToString(office, providers)}) "; //string numberOfPatientsQuery = $"SELECT count(accountopendate) as result FROM patient where activepatient=1 AND office_sequence = {office} AND doctor in ({Utility.FilterProviderToString(office, providers)}) "; var numberOfPatients = db.ExecuteScalar <decimal>(numberOfPatientsQuery); paidAmount += model; patientCount += numberOfPatients; } if (paidAmount != 0 && patientCount != 0) { return(paidAmount / patientCount); } return(0); }
/// <summary> /// /// </summary> /// <param name="offices"></param> /// <param name="providerList">This list contains entries with [OfficeSequence]_[Providers]</param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public List <gPatientList> GetPatientRecords(int[] offices, string[] providerList, string startDate, string endDate) { List <gPatientList> result = new List <gPatientList>(); var providers = Utility.ProviderToList(providerList); foreach (var office in offices) { if (providers.All(x => x.Office_Sequence != office)) { continue; } var db = PocoDatabase.DbConnection(Utility.GetConnectionStringByOfficeId(office)); string query = $"SELECT office_sequence,patientnumber,firstname,lastname FROM patient where activepatient=1 AND accountopendate >= '{startDate}' AND accountopendate <= '{endDate}' AND office_sequence = {office} AND doctor in ({Utility.FilterProviderToString(office, providers)})"; var model = db.Fetch <gPatientList>(query); result.AddRange(model); } return(result); }
/// <summary> /// /// </summary> /// <param name="offices"></param> /// <param name="providerList"> This list contains entries with [OfficeSequence]_[Providers] </param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public decimal GetNewPatient(int[] offices, string[] providerList, string startDate, string endDate) { decimal result = 0; var providers = Utility.ProviderToList(providerList); foreach (var office in offices) { if (providers.All(x => x.Office_Sequence != office)) { continue; } var db = PocoDatabase.DbConnection(Utility.GetConnectionStringByOfficeId(office)); string query = $"SELECT count(accountopendate) as result FROM patient where activepatient=1 AND accountopendate >= '{startDate}' AND accountopendate <= '{endDate}' AND office_sequence = {office} AND doctor in ({Utility.FilterProviderToString(office, providers)})"; var model = db.ExecuteScalar <decimal?>(query); if (model.HasValue) { result += model.Value; } } return(result); }
/// <summary> /// /// </summary> /// <param name="offices"></param> /// <param name="providerList">This list contains entries with [OfficeSequence]_[Providers]</param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public List <gCancellationAndNoShows> GetCancellationAndNoShowsBreakdown(int[] offices, string[] providerList, string startDate, string endDate, string types) { types = Utility.StringToCharacterString(types, _configuration.CancellationAndNoShowsStatusTypes); List <gCode> codes = new List <gCode>(); List <gCancellationAndNoShows> serviceAnalysisList = new List <gCancellationAndNoShows>(); var providers = Utility.ProviderToList(providerList); foreach (var office in offices) { if (providers.All(x => x.Office_Sequence != office)) { continue; } var db = PocoDatabase.DbConnection(Utility.GetConnectionStringByOfficeId(office)); string query = $"SELECT r.Office_sequence,p.provider as provider, CONCAT(p.title , ' ', p.name) as providerName, r.patientNumber, CONCAT(pt.title , ' ', pt.firstname ,' ', pt.lastname) as patientName, r.contactdate, r.job, r.timeslot FROM recall r " + $"LEFT JOIN provider p on r.provider = p.provider " + $"LEFT JOIN patient pt on r.patientNumber = pt.patientnumber " + $"WHERE r.provider in (SELECT provider FROM provider WHERE provider in ({Utility.FilterProviderToString(office, providers)}) AND Office_sequence = {office} AND activeProvider = 1 AND hygienist in ({Utility.HygenistType("CancellationAndNoShowsTypes")})) " + $"AND status in ({types}) " + $"AND r.contactdate >= '{startDate}' " + $"AND r.contactdate <= '{endDate}' " + $"AND r.Office_sequence = {office} "; var seAnalysis = db.Fetch <gCancellationAndNoShows>(query); foreach (var item in seAnalysis) { item.Time = TimeSpan.FromMinutes(item.TimeSlot); } serviceAnalysisList.AddRange(seAnalysis); } return(serviceAnalysisList); }
public static List <gUserCharts> GetUserCharts(int officeId, int userId) { var db = PocoDatabase.DbConnection(DbConfiguration.LtcDashboard); return(db.Fetch <gUserCharts>($"SELECT * FROM user_charts WHERE User_Id = {userId}").ToList()); }
public static gChartPermission GetChartPermissions(int officeId, int userId) { var db = PocoDatabase.DbConnection(DbConfiguration.LtcDashboard); return(db.Fetch <gChartPermission>($"SELECT CP.*, AG.Name 'Permission_Group_Name', AG.Permission_Level as 'Group_Permission_Level' FROM chart_permissions CP LEFT JOIN authentication_groups AG on CP.Permission_Group = AG.ID WHERE CP.Id = {userId} AND CP.Office_Sequence = {officeId}").FirstOrDefault()); }
/// <summary> /// /// </summary> /// <param name="offices"></param> /// <param name="providerList">This list contains entries with [OfficeSequence]_[Providers]</param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public List <gServiceAnalysis> GetPatientGrowthRateBreakdown(int[] offices, string[] providerList, string startDate, string endDate, string types) { types = Utility.StringToCharacterString(types, _configuration.ServiceAnalysisInvoiceTypes); List <gCode> codes = new List <gCode>(); List <gServiceAnalysis> serviceAnalysisList = new List <gServiceAnalysis>(); var providers = Utility.ProviderToList(providerList); foreach (var office in offices) { if (providers.All(x => x.Office_Sequence != office)) { continue; } var db = PocoDatabase.DbConnection(Utility.GetConnectionStringByOfficeId(office)); string query = $"Select Office_sequence,codecounter,code,line1,line2 from code where codetype = 'Y' AND Office_Sequence = {office};"; var officeCodeList = db.Fetch <gCode>(query); codes.AddRange(officeCodeList); query = $"Select " + $"i.Office_sequence,i.invoiceNumber, sum(i.insamount/100) as insamount,sum(i.patamount/100) as patamount, d.servicecode, " + $"d.provider, p.name as ProviderName, d.invoicedate from item i " + $"LEFT JOIN detail d on i.invoiceNumber = d.invoicenumber " + $"LEFT JOIN provider p on i.provider = p.provider " + $"WHERE i.provider in (SELECT provider FROM provider WHERE provider in ({Utility.FilterProviderToString(office, providers)}) AND Office_sequence = {office} AND activeProvider = 1 AND hygienist in ({Utility.HygenistType("ServiceAnalysisTypes")})) " + $"AND InvoiceType in ({types}) " + $"AND i.invoicedate >= '{startDate}' " + $"AND i.invoicedate <= '{endDate}' " + $"AND i.Office_sequence = {office} " + $"Group by i.Office_sequence, d.servicecode, i.invoicenumber, d.provider, d.invoicedate " + $"order by i.invoiceNumber, d.provider, d.invoicedate, d.servicecode; "; var seAnalysis = db.Fetch <gServiceAnalysis>(query); foreach (var item in seAnalysis) { var code = officeCodeList.FirstOrDefault(x => x.Office_Sequence == office && x.Code == item.ServiceCode); if (code != null) { item.CodeCategory = code.Code; } else { if (int.TryParse(item.ServiceCode, out var serviceCode)) { var intCode = officeCodeList.FirstOrDefault(x => x.Office_Sequence == office && serviceCode >= x.Line1Int && serviceCode <= x.Line2Int); if (intCode != null) { item.CodeCategory = intCode.Code; } } else { item.CodeCategory = item.ServiceCode; } } } serviceAnalysisList.AddRange(seAnalysis); } return(serviceAnalysisList); }
/// <summary> /// /// </summary> /// <param name="offices"></param> /// <param name="providerList">This list contains entries with [OfficeSequence]_[Providers]</param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public List <gPieChart> GetServiceAnalysis(int[] offices, string[] providerList, string startDate, string endDate, string types) { types = Utility.StringToCharacterString(types, _configuration.ServiceAnalysisInvoiceTypes); List <gCode> codes = new List <gCode>(); List <gPieChart> serviceAnalysisList = new List <gPieChart>(); var providers = Utility.ProviderToList(providerList); foreach (var office in offices) { if (providers.All(x => x.Office_Sequence != office)) { continue; } var db = PocoDatabase.DbConnection(Utility.GetConnectionStringByOfficeId(office)); string query = $"Select Office_sequence,codecounter,code,line1,line2 from code where codetype = 'Y' AND Office_Sequence = {office};"; var officeCodeList = db.Fetch <gCode>(query); codes.AddRange(officeCodeList); //xlabel = Total sum //yValue = CodeCategory query = $"Select " + $"IFNULL(sum(patamount/100) + sum(insamount/100),0) as yValue, IFNULL(d.servicecode,0) as UsableInformation from item i " + $"LEFT JOIN detail d on i.invoiceNumber = d.invoicenumber " + $"LEFT JOIN provider p on i.provider = p.provider " + $"WHERE i.provider in (SELECT provider FROM provider WHERE provider in ({Utility.FilterProviderToString(office, providers)}) AND Office_sequence = {office} AND activeProvider = 1 AND hygienist in ({Utility.HygenistType("ServiceAnalysisTypes")})) " + $"AND InvoiceType in ({types}) " + $"AND i.invoicedate >= '{startDate}' " + $"AND i.invoicedate <= '{endDate}' " + $"AND i.Office_sequence = {office} " + $"Group by d.servicecode "; var seAnalysis = db.Fetch <gPieChart>(query); foreach (var item in seAnalysis) { var code = officeCodeList.FirstOrDefault(x => x.Office_Sequence == office && x.Code == item.UsableInformation); if (code != null) { item.xLabel = code.Code; } else { if (int.TryParse(item.UsableInformation, out var serviceCode)) { var intCode = officeCodeList.FirstOrDefault(x => x.Office_Sequence == office && serviceCode >= x.Line1Int && serviceCode <= x.Line2Int); item.xLabel = intCode != null ? intCode.Code : ""; } else { item.xLabel = item.UsableInformation; } } } serviceAnalysisList.AddRange(seAnalysis); } return(serviceAnalysisList.OrderBy(x => x.xLabel).ToList()); }