private static SQLContext GetConditionedComprehensiveJoinedSelect_Inner(int methodId, string funcName, ref TableAndFields[] tblAndFldses, TableConnectionCollection tblCnnts, TableBiJoinedLogic tblBiJndLgc, bool andOrOr)
{
SQLContext ret = null;
bool res1 = GetFromCache(methodId, ref ret);
bool res2 = GetFromCacheTaF(methodId, ref tblAndFldses);
if (res1 && res2)
return ret;
ret.Sentence = "SELECT {0} FROM {1} WHERE {2} ";
//List<string> tables = new List<string>();
List<string> fields = new List<string>();
List<string> conds = new List<string>();
//List<string> cnnts = new List<string>();
int i = 0;
int j = 1;
foreach (TableAndFields tblAndFlds in tblAndFldses)
{
string alias = "t" + j.ToString();
tblAndFlds.alias = alias;
//if (tblAndFlds.subDBCalalog != null)
// tables.Add(tblAndFlds.subDBCalalog + ".." + tblAndFlds.Table.Name + " " + alias); //Sub Tables
//else
// tables.Add(tblAndFlds.Table.Name + " " + alias);
//object default_obj = Activator.CreateInstance(tblAndFlds.Table);
object default_obj = CreateCtor(tblAndFlds.Table)();
//FieldInfo[] fis = tblAndFlds.Table.GetFields(BindingFlags.Instance | BindingFlags.Public);
IDictionary<string, DBFieldAttribute> dbAttrList;
FieldInfo[] cfis;
FieldInfo[] fis = getCacheFieldInfos(tblAndFlds.Table, BindingFlags.Instance | BindingFlags.Public, out dbAttrList, out cfis);
foreach (FieldInfo fi in fis)
{
//string fieldName = TransField(tblAndFlds.Table, fi.Name);
string fieldName = TransField(cfis, fi.Name);
//object[] objs = fi.GetCustomAttributes(typeof(DBFieldAttribute), false);
//DBFieldAttribute dbfa = (DBFieldAttribute)objs[0];
DBFieldAttribute dbfa = dbAttrList[fi.Name];
object default_val = fi.GetValue(default_obj);
#region . Conditions .
if (tblAndFlds.equalcond != null)
{
object equalval = fi.GetValue(tblAndFlds.equalcond);
if (equalval != null && !equalval.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + "=@" + DecAlias(alias, ClearRectBrace(fieldName)));
SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, ClearRectBrace(fieldName)), dbfa.fieldType);
ret.Params.Add(DecAlias(alias, fieldName), sptr);
}
}
if (tblAndFlds.betweencond != null)
{
object betweenval = fi.GetValue(tblAndFlds.betweencond);
if (betweenval != null && !betweenval.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + " BETWEEN @" + DecAlias(alias, DecBeg(ClearRectBrace(fieldName))) + " AND @" + DecAlias(alias, DecEnd(ClearRectBrace(fieldName))));
SqlParameter sptr_beg = new SqlParameter("@" + DecAlias(alias, DecBeg(ClearRectBrace(fieldName))), dbfa.fieldType);
ret.Params.Add(DecAlias(alias, DecBeg(fieldName)), sptr_beg);
SqlParameter sptr_end = new SqlParameter("@" + DecAlias(alias, DecEnd(ClearRectBrace(fieldName))), dbfa.fieldType);
ret.Params.Add(DecAlias(alias, DecEnd(fieldName)), sptr_end);
}
}
if (tblAndFlds.likecond != null)
{
object likeval = fi.GetValue(tblAndFlds.likecond);
if (likeval != null && !likeval.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + " LIKE @" + DecAlias(alias, ClearRectBrace(fieldName)));
SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, ClearRectBrace(fieldName)), dbfa.fieldType);
ret.Params.Add(DecAlias(alias, fieldName), sptr);
}
}
if (tblAndFlds.greatercond != null)
{
object greaterval = fi.GetValue(tblAndFlds.greatercond);
if (greaterval != null && !greaterval.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + ">@" + DecAlias(alias, DecG(ClearRectBrace(fieldName))));
SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, DecG(ClearRectBrace(fieldName))), dbfa.fieldType);
ret.Params.Add(DecAlias(alias, DecG(fieldName)), sptr);
}
}
if (tblAndFlds.smallercond != null)
{
object smallerval = fi.GetValue(tblAndFlds.smallercond);
if (smallerval != null && !smallerval.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + "<@" + DecAlias(alias, DecS(ClearRectBrace(fieldName))));
SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, DecS(ClearRectBrace(fieldName))), dbfa.fieldType);
ret.Params.Add(DecAlias(alias, DecS(fieldName)), sptr);
}
}
if (tblAndFlds.greaterOrEqualcond != null)
{
object greaterOrEqualval = fi.GetValue(tblAndFlds.greaterOrEqualcond);
if (greaterOrEqualval != null && !greaterOrEqualval.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + ">=@" + DecAlias(alias, DecGE(ClearRectBrace(fieldName))));
SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, DecGE(ClearRectBrace(fieldName))), dbfa.fieldType);
ret.Params.Add(DecAlias(alias, DecGE(fieldName)), sptr);
}
}
if (tblAndFlds.smallerOrEqualcond != null)
{
object smallerOrEqualval = fi.GetValue(tblAndFlds.smallerOrEqualcond);
if (smallerOrEqualval != null && !smallerOrEqualval.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + "<=@" + DecAlias(alias, DecSE(ClearRectBrace(fieldName))));
SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, DecSE(ClearRectBrace(fieldName))), dbfa.fieldType);
ret.Params.Add(DecAlias(alias, DecSE(fieldName)), sptr);
}
}
if (tblAndFlds.inSetcond != null)
{
object inSetval = fi.GetValue(tblAndFlds.inSetcond);
if (inSetval != null && !inSetval.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + " IN (" + DecAlias(alias, DecInSet(fieldName)) + ")");
//SqlParameter sptr = new SqlParameter("@" + DecSE(fieldName), dbfa.fieldType);
//ret.Params.Add(DecSE(fieldName), sptr);
}
}
if (tblAndFlds.notNullcond != null)
{
object notNullVal = fi.GetValue(tblAndFlds.notNullcond);
if (notNullVal != null && !notNullVal.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + " IS NOT NULL ");
}
}
if (tblAndFlds.nullcond != null)
{
object nullval = fi.GetValue(tblAndFlds.nullcond);
if (nullval != null && !nullval.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + " IS NULL ");
}
}
if (tblAndFlds.notEqualcond != null)
{
object notEqualval = fi.GetValue(tblAndFlds.notEqualcond);
if (notEqualval != null && !notEqualval.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + "!=@" + DecAlias(alias, ClearRectBrace(fieldName)));
SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, ClearRectBrace(fieldName)), dbfa.fieldType);
ret.Params.Add(DecAlias(alias, fieldName), sptr);
}
}
if (tblAndFlds.notLikecond != null)
{
object notLikeval = fi.GetValue(tblAndFlds.notLikecond);
if (notLikeval != null && !notLikeval.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + " NOT LIKE @" + DecAlias(alias, ClearRectBrace(fieldName)));
SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, ClearRectBrace(fieldName)), dbfa.fieldType);
ret.Params.Add(DecAlias(alias, fieldName), sptr);
}
}
if (tblAndFlds.notInSetcond != null)
{
object notInSetval = fi.GetValue(tblAndFlds.notInSetcond);
if (notInSetval != null && !notInSetval.Equals(default_val))
{
conds.Add(DecAliasInner(alias, fieldName) + " NOT IN (" + DecAlias(alias, DecInSet(fieldName)) + ")");
}
}
if (tblAndFlds.nullOrEqual != null)
{
object nullOrEqVal = fi.GetValue(tblAndFlds.nullOrEqual);
if (nullOrEqVal != null && !nullOrEqVal.Equals(default_val))
{
conds.Add(string.Format("({0} IS NULL OR {0}={1})", DecAliasInner(alias, fieldName), "@" + DecAlias(alias, ClearRectBrace(fieldName))));
SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, ClearRectBrace(fieldName)), dbfa.fieldType);
ret.Params.Add(DecAlias(alias, fieldName), sptr);
}
}
#endregion
//string key = DecAlias(tblAndFlds.Table.Name, fieldName);
string key = DecAlias(tblAndFlds.GetHashCode().ToString(), fieldName);
if (tblCnnts.Regist.ContainsKey(key))
{
IDictionary<string, TableConnectionItem> point = tblCnnts.Regist[key];
foreach (TableConnectionItem tcti in point.Values)
{
if (!tcti.tbl1_Alias && tblAndFlds == tcti.Tb1)
{
tcti.alias1 = alias;
tcti.tbl1_Alias = true;
}
if (!tcti.tbl2_Alias && tblAndFlds == tcti.Tb2)
{
tcti.alias2 = alias;
tcti.tbl2_Alias = true;
}
}
}
if (/*tblAndFlds.ToGetFieldNames == null ||*/ tblAndFlds.ToGetFieldNames != null && (tblAndFlds.ToGetFieldNames.Count < 1 || tblAndFlds.ToGetFieldNames.Contains(fieldName)))
{
ret.Indexes.Add(DecAlias(alias, fieldName), i);
fields.Add(DecAliasInner(alias, fieldName));
i++;
}
}
j++;
}
string fStr = string.Join(",", fields.ToArray());
if (funcName != null)
{
if (funcName.ToUpper().Equals("DISTINCT") || funcName.ToUpper().StartsWith("TOP "))
{
fStr = string.Format("{0} {1} ", funcName, fStr);
}
else
{
fStr = string.Format("{0}({1})", funcName, fStr);
ret.Indexes.Clear();
ret.Indexes.Add(funcName, 0);
}
}
object lastObj = null;
string toStr = string.Empty;
IEnumerator enmrt = tblBiJndLgc.Enum;
while (enmrt.MoveNext())
{
object obj = enmrt.Current;
if (obj is TableAndFields)
{
toStr += obj.ToString();
}
else if (obj is TableConnectionItem)
{
if (lastObj is TableConnectionItem)
{
if (((TableConnectionItem)obj).AndOrOr)
toStr += _andStr;
else
toStr += _orStr;
}
else if (lastObj is TableAndFields)
{
toStr += _onStr;
}
toStr += obj.ToString();
}
else if (obj is string)
{
toStr += obj.ToString();
}
lastObj = obj;
}
string cStr = (conds.Count > 0 ? string.Join(andOrOr ? " AND " : " OR ", conds.ToArray()) : " 1=1 ");
ret.Sentence = string.Format(ret.Sentence, fStr, toStr, cStr);
return ret;
}
public static SQLContext GetConditionedComprehensiveJoinedSelectExtOr(int methodId, string funcName, ref TableAndFields[] tblAndFldses, TableConnectionCollection tblCnnts, TableBiJoinedLogic tblBiJndLgc)
{
return GetConditionedComprehensiveJoinedSelect_Inner(methodId, funcName, ref tblAndFldses, tblCnnts, tblBiJndLgc, false);
}