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