/// <summary>用于取整个数据源的查询语法</summary>
/// <param name="where">指主表的条件</param>
/// <returns></returns>
public string GetSQL(WhereObject where)
{
StringBuilder sql = new StringBuilder();
if (string.IsNullOrEmpty(this._id))
{
return(string.Empty);
}
StringBuilder fields = null;
LibFormPage form = ModelManager.GetFormSource(this._id);
var datasourse = ModelManager.GetDataSource(form.DSID);
if (datasourse != null)
{
foreach (LibDefineTable deftb in datasourse.DefTables)
{
if (deftb.TableStruct == null)
{
continue;
}
foreach (LibDataTableStruct tbstruct in deftb.TableStruct)
{
if (!tbstruct.Ignore)
{
continue;
}
fields = new StringBuilder();
char tbaliasnm = LibSysUtils.ToCharByTableIndex(tbstruct.TableIndex);
foreach (LibField f in tbstruct.Fields)
{
if (fields.Length > 0)
{
fields.Append(SysConstManage.Comma);
}
fields.AppendFormat("{0}.{1}", tbaliasnm, string.IsNullOrEmpty(f.AliasName) ? f.Name : f.AliasName);
}
if (fields.Length > 0)
{
if (string.IsNullOrEmpty(where.WhereFormat))
{
sql.AppendFormat("select {0} from {1} {2}", fields.ToString(), tbstruct.Name, tbaliasnm);
}
else
{
sql.AppendFormat("select {0} from {1} {2} where {3}", fields.ToString(), tbstruct.Name, tbaliasnm, where.WhereFormat);
}
sql.AppendLine();
}
}
}
}
return(sql.ToString());
}
public DataTable[] InternalFillData(List <string> whereformat, object[] valus)
{
StringBuilder sql = new StringBuilder();
DataTable[] dts = { };
StringBuilder where = new StringBuilder();
//DataTable mdt = null;
//Dictionary<int, int> dic = new Dictionary<int, int>();
//SDPCRL.DAL.COM.SQLBuilder sQLBuilder = new SDPCRL.DAL.COM.SQLBuilder();
foreach (var libdt in this.LibTables)
{
foreach (LibTableObj dtobj in libdt.Tables)
{
TableExtendedProperties tbextprop = this.JsonToObj <TableExtendedProperties>(dtobj.DataTable.ExtendedProperties[SysConstManage.ExtProp].ToString());
if (!tbextprop.Ignore)
{
continue;
}
Array.Resize(ref dts, dts.Length + 1);
dts[dts.Length - 1] = new DataTable(dtobj.TableName);
if (tbextprop.TableIndex == 0 || tbextprop.TableIndex != tbextprop.RelateTableIndex)
{
where.Clear();
foreach (string item in whereformat)
{
if (where.Length > 0)
{
where.Append(" And ");
}
where.AppendFormat("{0}.{1}", LibSysUtils.ToCharByTableIndex(tbextprop.TableIndex), item);
}
//sql.Append(this.SQLBuilder.GetSQL(dtobj.TableName, null, new WhereObject { WhereFormat=where.ToString (),Values=valus },false,false));
sql.Append(this.DSContext.GetSQL(dtobj.TableName, null, new WhereObject {
WhereFormat = where.ToString(), Values = valus
}, false, false));
sql.AppendLine();
}
}
}
this.DataAccess.GetDatatTables(sql.ToString(), ref dts);
return(dts);
}
private void DoGetSQL(StringBuilder builder, string tableNm, LibDataSource ds, WhereObject where, string[] sumaryfields, bool page = false, bool IsJoinRelateTable = true, bool IsJoinFromSourceField = true, bool IsRpt = false)
{
List <LibDataTableStruct> list = new List <LibDataTableStruct>();
StringBuilder joinstr = new StringBuilder();
StringBuilder joinfield = null;
StringBuilder allfields = new StringBuilder();
Dictionary <string, string> tablealiasmdic = new Dictionary <string, string>();
foreach (LibDefineTable item in ds.DefTables)
{
if (item.TableStruct == null)
{
continue;
}
list.AddRange(item.TableStruct.ToArray());
}
var tb = list.FirstOrDefault(i => i.Name == tableNm && i.Ignore);
char tbaliasnm = LibSysUtils.ToCharByTableIndex(tb.TableIndex);
#region 组织要查询表的字段
if (builder.Length == ResFactory.ResManager.SQLSelect.Length)
{
foreach (LibField f in tb.Fields)
{
if (!f.IsActive)
{
continue;
}
if (allfields.Length > 0)
{
allfields.Append(SysConstManage.Comma);
}
allfields.AppendFormat(" {0}.{1}", tbaliasnm, f.Name);
}
#region 日志列
if (allfields.Length > 0)
{
allfields.Append(SysConstManage.Comma);
}
allfields.AppendFormat(" {0}.{1}", tbaliasnm, SysConstManage.Sdp_LogId);
#endregion
}
#endregion
if (tb != null)
{
if (IsJoinRelateTable)
{
var relatetbs = list.Where(i => i.JoinTableIndex == tb.TableIndex && i.TableIndex != tb.TableIndex && i.Ignore).ToList();
List <int> tbindexs = null;
//char tbaliasnm2 = tbaliasnm;
while (relatetbs != null && relatetbs.Count > 0)
{
tbindexs = new List <int>();
foreach (var jointb in relatetbs)
{
tbindexs.Add(jointb.TableIndex);
#region 组织joinstr语句
joinstr.AppendFormat(" {0} {1} {2} {3} ",
ResFactory.ResManager.SQLLeftJoin,
jointb.Name,
LibSysUtils.ToCharByTableIndex(jointb.TableIndex),
//(char)(jointb.TableIndex+65),
ResFactory.ResManager.SQLOn);
joinfield = new StringBuilder();
foreach (var relatfield in jointb.JoinFields)
{
if (joinfield.Length > 0)
{
joinfield.Append(ResFactory.ResManager.SQLAnd);;
}
LibField libfd = jointb.Fields.FindFirst("Name", relatfield);
//if (string.IsNullOrEmpty(libfd.RelatePrimarykey))
joinfield.AppendFormat(" {0}.{1}={2}.{3} ",
LibSysUtils.ToCharByTableIndex(jointb.JoinTableIndex),
string.IsNullOrEmpty(libfd.RelatePrimarykey) ? relatfield : libfd.RelatePrimarykey,
LibSysUtils.ToCharByTableIndex(jointb.TableIndex),
relatfield);
//else
}
joinstr.Append(joinfield.ToString());
joinstr.AppendLine();
#endregion
#region 取关联表的字段
if (builder.Length == ResFactory.ResManager.SQLSelect.Length)
{
foreach (LibField f2 in jointb.Fields)
{
if (!f2.IsActive || jointb.JoinFields.Contains(f2.Name))
{
continue;
}
if (allfields.ToString().ToUpper().Contains(string.Format(".{0}", f2.Name.ToUpper())) && string.IsNullOrEmpty(f2.AliasName))
{
allfields.AppendFormat("{0}{1}.{2} as {3}", SysConstManage.Comma, LibSysUtils.ToCharByTableIndex(jointb.TableIndex), f2.Name, string.Format("{0}{2}{1}", LibSysUtils.ToCharByTableIndex(jointb.TableIndex), f2.Name, SysConstManage.Underline));
}
else
{
if (!string.IsNullOrEmpty(f2.AliasName))
{
allfields.AppendFormat("{0}{1}.{2} as {3}", SysConstManage.Comma, LibSysUtils.ToCharByTableIndex(jointb.TableIndex), f2.Name, f2.AliasName);
}
else
{
allfields.AppendFormat("{0}{1}.{2}", SysConstManage.Comma, LibSysUtils.ToCharByTableIndex(jointb.TableIndex), f2.Name);
}
}
}
}
#endregion
}
relatetbs = list.Where(i => tbindexs.Contains(i.JoinTableIndex) && i.TableIndex != i.JoinTableIndex && i.Ignore).ToList();
//tbaliasnm2=
}
}
#region 字段上的来源表。
var relatefields = tb.Fields.ToArray().Where(i => i.SourceField != null);
List <string> vals = new List <string>();
foreach (LibField f in relatefields)
{
foreach (LibFromSourceField fromfield in f.SourceField)
{
string key = string.Format("{0}{1}{2}", f.Name, fromfield.FromDataSource, fromfield.FromTableIndex);
string tbnm = string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(fromfield.FromTableIndex));
if (!tablealiasmdic.ContainsKey(key))
{
//vals.Add(tbnm);
int cout = vals.Where(i => i == tbnm).Count();
if (cout > 0)
{
tbnm = string.Format("{0}{1}", tbnm, cout);
//tablealiasmdic.Add(key, string.Format("{0}", tbnm));
}
vals.Add(tbnm);
tablealiasmdic.Add(key, tbnm);
//tablealiasmdic.Add(key,string.Format( tbnm);
//tablealiasmdic[tbnm]++;
//tbnm = string.Format("{0}{1}", tbnm, tablealiasmdic[tbnm]+1);
}
else
{
////int cout = vals.Where(i => i == tbnm).Count();
tbnm = tablealiasmdic[key];
//tablealiasmdic.Add(tbnm, 0);
}
#region 组织joinstr语句
joinstr.AppendFormat(" {0} {1} {2} {3} ",
ResFactory.ResManager.SQLLeftJoin,
fromfield.FromStructTableNm,
tbnm,
//(char)(jointb.TableIndex+65),
ResFactory.ResManager.SQLOn);
joinfield = new StringBuilder();
if (joinfield.Length > 0)
{
joinfield.Append(ResFactory.ResManager.SQLAnd);
}
joinfield.AppendFormat(" {0}.{1}={2}.{3} ",
tbaliasnm,
f.Name,
tbnm,
fromfield.FromFieldNm);
#region 处理JoinOnCondition
if (!string.IsNullOrEmpty(fromfield.JoinOnCondition))
{
if (where == null)
{
where = new WhereObject();
}
DoJoinOnCondition(fromfield.JoinOnCondition, where);
}
#endregion
joinstr.Append(joinfield.ToString());
joinstr.AppendLine();
if (IsJoinFromSourceField)
{
#region 取来源表所在数据源的 与来源表关联的表。
LibDataSource fromSouceDS = ModelManager.GetDataSource(fromfield.FromDataSource);
if (fromSouceDS != null)
{
List <LibDataTableStruct> list2 = new List <LibDataTableStruct>();
foreach (LibDefineTable def in fromSouceDS.DefTables)
{
if (def.TableStruct == null)
{
continue;
}
list2.AddRange(def.TableStruct.ToArray());
}
var relatetbs = list2.Where(i => i.JoinTableIndex == fromfield.FromTableIndex && i.TableIndex != fromfield.FromTableIndex && i.Ignore).ToList();
List <int> tbindexs2 = null;
while (relatetbs != null && relatetbs.Count > 0)
{
tbindexs2 = new List <int>();
foreach (var jointb in relatetbs)
{
string key2 = string.Format("{0}{1}{2}", f.Name, fromfield.FromDataSource, jointb.TableIndex);
tbindexs2.Add(jointb.TableIndex);
string tbnm2 = string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(jointb.TableIndex));
if (!tablealiasmdic.ContainsKey(key2))
{
//vals.Add(tbnm2);
int cout = vals.Where(i => i == tbnm2).Count();
if (cout > 0)
{
tbnm2 = string.Format("{0}{1}", tbnm2, cout);
//tablealiasmdic.Add(key, string.Format("{0}", tbnm));
}
tablealiasmdic.Add(key, tbnm2);
//tablealiasmdic.Add(key2, tbnm2);
//tablealiasmdic[tbnm2]++;
//tbnm2 = string.Format("{0}{1}", tbnm2, tablealiasmdic[tbnm2]+1);
}
else
{
tbnm2 = tablealiasmdic[key2];
//tablealiasmdic.Add(tbnm2, 0);
}
joinstr.AppendFormat(" {0} {1} {2} {3} ",
ResFactory.ResManager.SQLLeftJoin,
jointb.Name,
tbnm2,
//(char)(jointb.TableIndex+65),
ResFactory.ResManager.SQLOn);
joinfield = new StringBuilder();
foreach (var relatfield in jointb.JoinFields)
{
if (joinfield.Length > 0)
{
joinfield.Append(ResFactory.ResManager.SQLAnd);;
}
LibField libfd = jointb.Fields.FindFirst("Name", relatfield);
joinfield.AppendFormat(" {0}.{1}={2}.{3} ",
string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(jointb.JoinTableIndex)),
string.IsNullOrEmpty(libfd.RelatePrimarykey) ? relatfield : libfd.RelatePrimarykey,
string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(jointb.TableIndex)),
relatfield);
}
joinstr.Append(joinfield.ToString());
joinstr.AppendLine();
}
relatetbs = list2.Where(i => tbindexs2.Contains(i.JoinTableIndex) && i.TableIndex != i.JoinTableIndex && i.Ignore).ToList();
//tbaliasnm2=
}
}
#endregion
}
if (builder.Length == ResFactory.ResManager.SQLSelect.Length || IsRpt)
{
if (fromfield.RelateFieldNm != null)
{
foreach (LibRelateField relatef in fromfield.RelateFieldNm)
{
if (!IsJoinFromSourceField && relatef.FromTableIndex != fromfield.FromTableIndex)
{
continue;
}
key = string.Format("{0}{1}{2}", f.Name, fromfield.FromDataSource, relatef.FromTableIndex);
//tbnm = string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(relatef.FromTableIndex));
//if (IsJoinFromSourceField && relatef.FromTableIndex != fromfield.FromTableIndex)
//{
tbnm = tablealiasmdic[key];
//}
if (allfields.Length > 0)
{
allfields.Append(SysConstManage.Comma);
}
if (!string.IsNullOrEmpty(relatef.AliasName))
{
allfields.AppendFormat("{0}.{1} as {2}",
//SysConstManage.Comma,
tbnm,
relatef.FieldNm,
relatef.AliasName);
}
else
{
allfields.AppendFormat("{0}.{1}",
//SysConstManage.Comma,
tbnm,
relatef.FieldNm);
}
}
}
}
#endregion
}
}
#endregion
if (builder.Length == ResFactory.ResManager.SQLSelect.Length || IsRpt)
{
if (builder.Length != ResFactory.ResManager.SQLSelect.Length)
{
builder.Append(SysConstManage.Comma);
}
builder.Append(allfields.ToString());
}
if (page) //分页
{
StringBuilder orderstr = new StringBuilder();
foreach (string key in tb.PrimaryKey)
{
if (orderstr.Length > 0)
{
orderstr.Append(SysConstManage.Comma);
}
orderstr.AppendFormat("{0}.{1}", LibSysUtils.ToCharByTableIndex(tb.TableIndex), key);
}
builder.AppendFormat(",ROW_NUMBER()OVER(order by {0}) as rownumber ", orderstr.ToString());
}
//if (sumaryfields != null) //汇总字段
//{
// foreach (string field in sumaryfields)
// {
// builder.AppendFormat(",SUM({0})OVER() as {1}{2}", field,SysConstManage.sdp_summaryprefix,field .Substring(field.IndexOf(".")+1));
// }
//}
builder.AppendFormat(" {0}", ResFactory.ResManager.SQLFrom);
builder.AppendFormat(" {0} {1}", tableNm, tbaliasnm);
builder.Append(joinstr.ToString());
}
}
//public
#region 私有函数
private void GetColumns()
{
_cols = new Dictionary <string, LibFieldObj>();
List <string> vals = new List <string>();
ColExtendedProperties colExtended = null;
LibFieldObj fobj = null;
string ckey = string.Empty;
TableExtendedProperties tableExtended = DataTable.ExtendedProperties[SysConstManage.ExtProp] as TableExtendedProperties;
if (_tbaliasmdic == null)
{
_tbaliasmdic = new Dictionary <string, string>();
}
foreach (DataColumn c in DataTable.Columns)
{
fobj = new LibFieldObj();
colExtended = c.ExtendedProperties[SysConstManage.ExtProp] as ColExtendedProperties;
if (colExtended != null)
{
if (colExtended.IsRelate)
{
if (!string.IsNullOrEmpty(colExtended.AliasName))
{
ckey = colExtended.AliasName;
//fobj.FieldNm = colExtended.AliasName;
}
else if (!string.IsNullOrEmpty(colExtended.ObjectNm))
{
ckey = colExtended.ObjectNm;
//fobj.FieldNm = colExtended.ObjectNm;
}
else
{
ckey = c.ColumnName;
//fobj.FieldNm = c.ColumnName;
}
string key = string.Format("{0}{1}{2}", colExtended.FromDSID, colExtended.FromTableIndex, colExtended.SourceFieldNm);
if (!_tbaliasmdic.ContainsKey(key))
{
string v = string.Format("{0}{1}", LibSysUtils.ToCharByTableIndex(tableExtended.TableIndex), LibSysUtils.ToCharByTableIndex(colExtended.FromTableIndex));
//int t = vals.Where(i => i == v).Count();
int cout = vals.Where(i => i == v).Count();
if (cout == 0)
{
_tbaliasmdic.Add(key, string.Format("{0}", v));
}
else
{
_tbaliasmdic.Add(key, string.Format("{0}{1}", v, cout));
}
vals.Add(v);
}
fobj.TBAliasNm = _tbaliasmdic[key];
fobj.FieldNm = colExtended.FieldNm;
}
else
{
if (tableExtended != null)
{
fobj.TBAliasNm = LibSysUtils.ToCharByTableIndex(tableExtended.TableIndex).ToString();
}
ckey = string.IsNullOrEmpty(colExtended.ObjectNm) ? c.ColumnName : colExtended.ObjectNm;
fobj.FieldNm = c.ColumnName;
}
fobj.colNm = c.ColumnName;
_cols.Add(ckey, fobj);
}
else
{
fobj.FieldNm = c.ColumnName;
fobj.colNm = c.ColumnName;
_cols.Add(fobj.FieldNm, fobj);
}
}
Columns = new ColumnObj(_cols);
}