///<summary>Gets new List for the specified family. The only insSubs it misses are for claims with no current coverage. These are handled as needed.</summary>
public static List <InsSub> RefreshForFam(Family Fam)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <InsSub> >(MethodBase.GetCurrentMethod(), Fam));
}
//The command is written in a nested fashion in order to be compatible with both MySQL and Oracle.
string command =
"SELECT D.* FROM inssub D," +
"((SELECT A.InsSubNum FROM inssub A WHERE";
//subscribers in family
for (int i = 0; i < Fam.ListPats.Length; i++)
{
if (i > 0)
{
command += " OR";
}
command += " A.Subscriber=" + POut.Long(Fam.ListPats[i].PatNum);
}
//in union, distinct is implied
command += ") UNION (SELECT B.InsSubNum FROM inssub B,patplan P WHERE B.InsSubNum=P.InsSubNum AND (";
for (int i = 0; i < Fam.ListPats.Length; i++)
{
if (i > 0)
{
command += " OR";
}
command += " P.PatNum=" + POut.Long(Fam.ListPats[i].PatNum);
}
command += "))) C "
+ "WHERE D.InsSubNum=C.InsSubNum "
+ "ORDER BY " + DbHelper.UnionOrderBy("DateEffective", 4);
return(Crud.InsSubCrud.SelectMany(command));
}
///<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);
}
