///<summary>Returns a dicitonary such that the key is a clinicNum and the value is a count of patients whith a matching patient.ClinicNum. ///Excludes all patients with PatStatus of Deleted, Archived, Deceased, or NonPatient unless IsAllStatuses is set to true.</summary> public static SerializableDictionary <long, int> GetClinicalPatientCount(bool IsAllStatuses = false) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetSerializableDictionary <long, int>(MethodBase.GetCurrentMethod(), IsAllStatuses)); } string command = "SELECT ClinicNum,COUNT(*) AS Count FROM patient "; if (!IsAllStatuses) { command += "WHERE PatStatus NOT IN (" + POut.Int((int)PatientStatus.Deleted) + "," + POut.Int((int)PatientStatus.Archived) + "," + POut.Int((int)PatientStatus.Deceased) + "," + POut.Int((int)PatientStatus.NonPatient) + ") "; } command += "GROUP BY ClinicNum"; return(Db.GetTable(command).Select().ToSerializableDictionary(x => PIn.Long(x["ClinicNum"].ToString()), x => PIn.Int(x["Count"].ToString()))); }
///<summary>Gets all of the families and their corresponding InsSubs for all families passed in (saves calling RefreshForFam() one by one). ///Returns a dictionary of key: family and all of their corresponding value: InsSubs</summary> public static SerializableDictionary <Family, List <InsSub> > GetDictInsSubsForFams(List <Family> listFamilies) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetSerializableDictionary <Family, List <InsSub> >(MethodBase.GetCurrentMethod(), listFamilies)); } SerializableDictionary <Family, List <InsSub> > dictFamilyInsSubs = new SerializableDictionary <Family, List <InsSub> >(); if (listFamilies == null || listFamilies.Count < 1) { return(dictFamilyInsSubs); } List <long> listPatNums = listFamilies.SelectMany(x => x.ListPats).Select(x => x.PatNum).ToList(); if (listPatNums == null || listPatNums.Count < 1) { return(dictFamilyInsSubs); } //The command is written ina nested fashion in order to be compatible with both MySQL and Oracle. string command = "SELECT D.*,C.OnBehalfOf " + "FROM inssub D,((SELECT A.InsSubNum,A.Subscriber AS OnBehalfOf " + "FROM inssub A " + "WHERE A.Subscriber IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ")" //in union, distinct is implied + ") UNION (SELECT B.InsSubNum,P.PatNum AS OnBehalfOf " + "FROM inssub B,patplan P " + "WHERE B.InsSubNum=P.InsSubNum AND P.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + "))" + ") C " + "WHERE D.InsSubNum=C.InsSubNum " + "ORDER BY " + DbHelper.UnionOrderBy("DateEffective", 4); DataTable table = Db.GetTable(command); foreach (Family family in listFamilies) { List <long> listOnBehalfOfs = family.ListPats.Select(x => x.PatNum).ToList(); List <InsSub> listInsSubs = new List <InsSub>(); List <DataRow> listDataRows = table.Select().Where(x => listOnBehalfOfs.Exists(y => y == PIn.Long(x["OnBehalfOf"].ToString()))).ToList(); DataTable tableFamilyInsSubs = table.Clone(); foreach (DataRow row in listDataRows) { tableFamilyInsSubs.ImportRow(row); } dictFamilyInsSubs[family] = Crud.InsSubCrud.TableToList(tableFamilyInsSubs); } return(dictFamilyInsSubs); }
/// <summary>Takes in a list of patnums and returns a dictionary of PatNum to DiscountPlan.FeeSchedNum pairs. Value is 0 if no discount plan exists</summary> public static SerializableDictionary <long, long> GetFeeSchedNumsByPatNums(List <long> listPatNums) { if (listPatNums.IsNullOrEmpty()) { return(new SerializableDictionary <long, long>()); } if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetSerializableDictionary <long, long>(MethodBase.GetCurrentMethod(), listPatNums)); } string command = "SELECT patient.PatNum,discountplan.FeeSchedNum " + "FROM patient " + "LEFT JOIN discountplan ON discountplan.DiscountPlanNum=patient.DiscountPlanNum " + $"WHERE patient.PatNum IN ({string.Join(",",listPatNums)})"; return(Db.GetTable(command).Select() .ToSerializableDictionary(x => PIn.Long(x["PatNum"].ToString()), x => PIn.Long(x["FeeSchedNum"].ToString()))); }
///<summary>Returns a dictionary where key=DiscountPlanNum and value=count of patients for the DiscountPlanNum. ///Returns an empty dictionary if the list of plan nums is empty.</summary> public static SerializableDictionary <long, int> GetPatCountsForPlans(List <long> listPlanNums) { if (listPlanNums.Count == 0) { return(new SerializableDictionary <long, int>()); } if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetSerializableDictionary <long, int>(MethodBase.GetCurrentMethod(), listPlanNums)); } string command = "SELECT DiscountPlanNum,COUNT(PatNum) PatCount FROM patient " + "WHERE DiscountPlanNum IN (" + string.Join(",", listPlanNums) + ") " + "AND PatStatus NOT IN(" + POut.Int((int)PatientStatus.Deleted) + "," + POut.Int((int)PatientStatus.Deceased) + ") " + "GROUP BY DiscountPlanNum"; return(Db.GetTable(command).Select() .ToSerializableDictionary(x => PIn.Long(x["DiscountPlanNum"].ToString()), x => PIn.Int(x["PatCount"].ToString()))); }
///<summary>Returns a dicitonary such that the key is a clinicNum and the value is a count of patients whith a matching patient.ClinicNum</summary> public static SerializableDictionary <long, int> GetClinicalPatientCount() { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetSerializableDictionary <long, int>(MethodBase.GetCurrentMethod())); } SerializableDictionary <long, int> retVal = new SerializableDictionary <long, int>(); string command = @"SELECT ClinicNum, COUNT(*) AS Count FROM patient GROUP BY ClinicNum" ; DataTable table = Db.GetTable(command); for (int i = 0; i < table.Rows.Count; i++) { long clinicNum = PIn.Long(table.Rows[i]["ClinicNum"].ToString()); int clinicCount = PIn.Int(table.Rows[i]["Count"].ToString()); retVal.Add(clinicNum, clinicCount); } return(retVal); }