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