public static string GetQueryStr(LibQueryCondition condition, string name, LibDataType dataType, bool needAnd = false, string prefix = "A", string realName = "")
{
StringBuilder builder = new StringBuilder();
List <LibQueryField> queryFieldList = new List <LibQueryField>();
if (condition == null)
{
return(builder.ToString());
}
foreach (var item in condition.QueryFields)
{
if (item.Name.CompareTo(name) == 0)
{
queryFieldList.Add(item);
}
}
int count = queryFieldList.Count;
if (count > 0)
{
for (int i = 0; i < count; i++)
{
BuildQueryStr(dataType, queryFieldList[i], builder, prefix, needAnd || !(i == count - 1), realName);
}
}
if (condition.PowerQueryFieldDic.ContainsKey(name))
{
if (!needAnd)
{
builder.Append(" and ");
}
BuildPowserQueryStr(dataType, condition.PowerQueryFieldDic[name], builder, prefix, false, realName);
}
return(builder.ToString());
}
public RelField(string name, LibDataType dataType, int size, string displayText)
{
_name = name;
this._DataType = dataType;
this._Size = size;
this._DisplayText = displayText;
}
private string GetRelWhere(string relSource, int tableIndex, char prefix, object[] curPks)
{
StringBuilder whereBuilder = new StringBuilder();
LibSqlModel relModel = LibSqlModelCache.Default.GetSqlModel(relSource);
DataColumn[] cols = relModel.Tables[tableIndex].PrimaryKey;
for (int i = 0; i < curPks.Length; i++)
{
DataColumn pk = cols[i];
if (i != 0)
{
whereBuilder.Append(" AND ");
}
LibDataType dataType = (LibDataType)pk.ExtendedProperties[FieldProperty.DataType];
if (dataType == LibDataType.Text)
{
whereBuilder.AppendFormat("{0}.{1} = {2}", prefix, pk.ColumnName, LibStringBuilder.GetQuotObject(curPks[i]));
}
else
{
whereBuilder.AppendFormat("{0}.{1} = {2}", prefix, pk.ColumnName, LibSysUtils.ToString(curPks[i]) == "" ? 0 : curPks[i]);
}
}
return(whereBuilder.ToString());
}
public static string GetQueryFieldStr(LibDataType dataType, LibQueryField queryField, bool needAnd = false, string prefix = "A", string realName = "")
{
StringBuilder builder = new StringBuilder();
BuildQueryStr(dataType, queryField, builder, prefix, needAnd, realName);
return(builder.ToString());
}
/// <summary>
/// 将Type类型转换为LibType类型
/// 无法区分NText、Text、Binary，Type为String都转换为LibDataType.NText
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static LibDataType ConvertToLibType(Type type)
{
LibDataType libType = LibDataType.Text;
if (type == typeof(string))
{
libType = LibDataType.NText;
} //无法区分NText、Text、Binary
else
if (type == typeof(int))
{
libType = LibDataType.Int32;
}
else
if (type == typeof(long))
{
libType = LibDataType.Int64;
}
else
if (type == typeof(decimal))
{
libType = LibDataType.Numeric;
}
else
if (type == typeof(float))
{
libType = LibDataType.Float;
}
else
if (type == typeof(double))
{
libType = LibDataType.Double;
}
else
if (type == typeof(byte))
{
libType = LibDataType.Byte;
}
else
if (type == typeof(bool))
{
libType = LibDataType.Boolean;
}
return(libType);
}
public static string GetQueryData(string progId, LibQueryCondition condition, string prefix = "A", bool useRelativeField = true)
{
if (condition == null)
{
return(string.Empty);
}
StringBuilder builder = new StringBuilder();
LibSqlModel sqlModel = LibSqlModelCache.Default.GetSqlModel(progId);
if (sqlModel == null)
{
throw new ArgumentNullException("sqlModel", "GetQueryData方法解析的progId取不到sqlModel");
}
LibSqlModelTable table = (LibSqlModelTable)sqlModel.Tables[0];
foreach (LibQueryField queryField in condition.QueryFields)
{
if (!table.Columns.Contains(queryField.Name))
{
continue;
}
LibSqlModelColumn col = (LibSqlModelColumn)table.Columns[queryField.Name];
if (col.ExtendedProperties.ContainsKey(FieldProperty.FieldType))
{
FieldType fieldType = (FieldType)col.ExtendedProperties[FieldProperty.FieldType];
if (FieldType.Virtual == fieldType)
{
continue;
}
if (!useRelativeField && FieldType.Relative == fieldType)
{
continue;
}
}
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
BuildQueryStr(dataType, queryField, builder, prefix, true, string.Empty);
//加入权限
if (condition.PowerQueryFieldDic.ContainsKey(queryField.Name))
{
BuildPowserQueryStr(dataType, condition.PowerQueryFieldDic[queryField.Name], builder, prefix, true, string.Empty);
}
}
if (builder.Length > 0)
{
builder.Remove(builder.Length - 4, 4);
}
return(builder.ToString());
}
public static Type ConvertType(LibDataType libDataType)
{
Type t = null;
switch (libDataType)
{
case LibDataType.Text:
case LibDataType.NText:
t = typeof(string);
break;
case LibDataType.Int32:
t = typeof(int);
break;
case LibDataType.Int64:
t = typeof(long);
break;
case LibDataType.Numeric:
t = typeof(decimal);
break;
case LibDataType.Float:
t = typeof(float);
break;
case LibDataType.Double:
t = typeof(double);
break;
case LibDataType.Byte:
t = typeof(byte);
break;
case LibDataType.Boolean:
t = typeof(bool);
break;
case LibDataType.Binary:
t = typeof(string);
break;
default:
break;
}
return(t);
}
private void MergeQueryField(string progId, LibPermission curPermission, LibPermission otherPermission)
{
LibSqlModel sqlModel = null;
LibSqlModelTable table = null;
List <string> removeList = new List <string>();
foreach (var item in curPermission.QueryFieldDic)
{
if (otherPermission.QueryFieldDic.ContainsKey(item.Key))
{
LibQueryField other = otherPermission.QueryFieldDic[item.Key][0];
bool exist = false;
foreach (var subItem in item.Value)
{
if (table == null)
{
sqlModel = LibSqlModelCache.Default.GetSqlModel(progId);
if (sqlModel != null)
{
table = (LibSqlModelTable)sqlModel.Tables[0];
}
}
if (table != null)
{
LibSqlModelColumn col = (LibSqlModelColumn)table.Columns[item.Key];
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
exist = LibQueryConditionParser.GetQueryFieldStr(dataType, subItem).CompareTo(LibQueryConditionParser.GetQueryFieldStr(dataType, other)) == 0;
if (exist)
{
break;
}
}
}
if (!exist)
{
item.Value.Add(other);
}
}
else
{
removeList.Add(item.Key);
}
}
foreach (var item in removeList)
{
curPermission.QueryFieldDic.Remove(item);
}
}
/// <summary>
/// 获取功能单据 的主键数据集合和入口参数字段键值对集合
/// </summary>
/// <param name="masterRow">功能单据 主表行数据</param>
/// <returns>返回主表主键数据集和入口参数字段键值对集合</returns>
private string GetMsgData(DataRow masterRow)
{
string data = string.Empty;
StringBuilder pkBuilder = new StringBuilder();
//遍历主表所有的主键字段，将其字段值用','连接起来并存储在pkBuilder中，例如："'2017060900001',1,'TC001',"
foreach (DataColumn col in masterRow.Table.PrimaryKey)
{
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
if (dataType == LibDataType.Text || dataType == LibDataType.NText)
{
pkBuilder.AppendFormat("'{0}',", masterRow[col]);
}
else
{
pkBuilder.AppendFormat("{0},", masterRow[col]);
}
}
pkBuilder.Remove(pkBuilder.Length - 1, 1);
//构造功能单据的主键数据集合，例如："['2017060900001',1,'TC001']"
data = string.Format("[{0}]", pkBuilder.ToString());
//当前数据模板的功能许可的入库参数存在
if (this.Template.FuncPermission.EntryParam.Count > 0)
{
StringBuilder entryBuilder = new StringBuilder();
entryBuilder.Append("{ParamStore:{");
//遍历数据模板的入口参数，将其对应的主表上的字段值以“字段：字段值”的模式用','连接起来并存储在entryBuilder中，
//例如："{ParamStore:{ID:'2017060900001',ROW_ID:1,TYPE:'TC001'}}"
foreach (string entryParam in this.Template.FuncPermission.EntryParam)
{
DataColumn col = this.DataSet.Tables[0].Columns[entryParam];
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
if (dataType == LibDataType.Text || dataType == LibDataType.NText)
{
entryBuilder.AppendFormat("{0}:'{1}',", entryParam, masterRow[col]);
}
else
{
entryBuilder.AppendFormat("{0}:{1},", entryParam, masterRow[col]);
}
}
entryBuilder.Remove(entryBuilder.Length - 1, 1);
entryBuilder.Append("}}");
data += string.Format(";{0}", entryBuilder.ToString());
}
//功能单据的主数据集合，例如"['2017060900001',1,'TC001'];{ParamStore:{ID:'2017060900001',ROW_ID:1,TYPE:'TC001'}}"
return(data);
}
private string ReturnOracleType(LibDataType dataType)
{
string dbType = string.Empty;
switch (dataType)
{
case LibDataType.Text:
dbType = LibOracleSchema.VARCHAR;
break;
case LibDataType.NText:
dbType = LibOracleSchema.NVARCHAR;
break;
case LibDataType.Int32:
dbType = "NUMBER"; //conn.getschema时候为NUMBER
break;
case LibDataType.Int64:
dbType = "NUMBER";
break;
case LibDataType.Numeric:
dbType = LibOracleSchema.DECIMAL;
break;
case LibDataType.Float:
dbType = LibOracleSchema.FLOAT;
break;
case LibDataType.Double:
dbType = LibOracleSchema.FLOAT;
break;
case LibDataType.Byte:
dbType = "NUMBER";
break;
case LibDataType.Boolean:
dbType = "NUMBER";
break;
case LibDataType.Binary:
dbType = LibOracleSchema.BINARY;
break;
}
return(dbType);
}
private static void BuildPowserQueryStr(LibDataType dataType, List <LibQueryField> queryFieldList, StringBuilder builder, string prefix, bool needAnd, string realName)
{
StringBuilder tempBuilder = new StringBuilder();
for (int i = 0; i < queryFieldList.Count; i++)
{
tempBuilder.Append("(");
if (i != 0)
{
tempBuilder.Append(" or ");
}
BuildQueryStr(dataType, queryFieldList[i], tempBuilder, prefix, false, realName);
tempBuilder.Append(")");
}
builder.Append(tempBuilder.ToString());
if (needAnd)
{
builder.Append("and ");
}
}
private bool HasFieldChanged(DataColumn col, string dataType, Dictionary <string, DbFieldInfo> defaultDic)
{
bool hasDiff = false;
LibDataType tempDataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
string dbType = ReturnOracleType(tempDataType);
string curDefaultValue = string.Empty;
bool isText = tempDataType == LibDataType.Text || tempDataType == LibDataType.NText || tempDataType == LibDataType.Binary;
DbFieldInfo dbFieldInfo = defaultDic[col.ColumnName];
if (isText)
{
curDefaultValue = LibSysUtils.ToString(col.DefaultValue);
}
else if (tempDataType == LibDataType.Boolean)
{
if ((bool)col.DefaultValue)
{
curDefaultValue = "1";
}
}
else
{
curDefaultValue = col.DefaultValue.ToString();
}
if (string.Compare(dbType, dataType, true) != 0 || (isText && col.MaxLength != dbFieldInfo.Length))
{
hasDiff = true;
}
else
{
string defaultValue = string.Empty;
defaultValue = dbFieldInfo.DefaultValue;
if (curDefaultValue != defaultValue)
{
hasDiff = true;
}
}
return(hasDiff);
}
public void ReadObjectData(LibSerializationInfo info)
{
this.ColumnName = info.ReadString();
this.MaxLength = info.ReadInt32();
this.ExtendedProperties.Add(FieldProperty.FieldType, (FieldType)info.ReadInt32());
LibDataType libDataType = (LibDataType)info.ReadInt32();
this.ExtendedProperties.Add(FieldProperty.DataType, libDataType);
int count = info.ReadInt32();
RelativeSourceCollection relColl = null;
for (int i = 0; i < count; i++)
{
if (relColl == null)
{
relColl = new RelativeSourceCollection();
}
relColl.Add((RelativeSource)info.ReadObject());
}
this.ExtendedProperties.Add(FieldProperty.RelativeSource, relColl);
this.DataType = LibDataTypeConverter.ConvertType(libDataType);
}
private void SetTableInfo(DataTable table)
{
if (table.ExtendedProperties.ContainsKey(TableProperty.UsingApproveRow))
{
UsingApproveRow = (bool)table.ExtendedProperties[TableProperty.UsingApproveRow];
}
if (table.ExtendedProperties.ContainsKey(TableProperty.UsingAttachment))
{
UsingAttachment = (bool)table.ExtendedProperties[TableProperty.UsingAttachment];
}
if (table.ParentRelations != null && table.ParentRelations.Count > 0)
{
string parentName = table.ParentRelations[0].ParentTable.TableName;
for (int i = 0; i < table.DataSet.Tables.Count; i++)
{
if (string.Compare(table.DataSet.Tables[i].TableName, parentName, true) == 0)
{
this.ParentIndex = i;
break;
}
}
}
this.Pk = this.GetPk(table);
StringBuilder builder = new StringBuilder();
StringBuilder newRowObj = new StringBuilder();
StringBuilder tempBuilder = new StringBuilder();
int r = 0;
foreach (DataColumn item in table.Columns)
{
if (!_UsingRowNo && item.ColumnName == "ROWNO")
{
_UsingRowNo = true;
}
if (!_UsingRowId && item.ColumnName == "ROW_ID")
{
_UsingRowId = true;
}
if (!this.IsDynamic)
{
if (item.ExtendedProperties.ContainsKey(FieldProperty.IsDynamic))
{
this.IsDynamic = (bool)item.ExtendedProperties[FieldProperty.IsDynamic];
}
}
if (item.ExtendedProperties.ContainsKey(FieldProperty.SubTableIndex))
{
SubTableMap.Add(item.ColumnName, (int)item.ExtendedProperties[FieldProperty.SubTableIndex]);
}
tempBuilder.AppendFormat("name:'{0}'", item.ColumnName);
LibDataType dateType = (LibDataType)item.ExtendedProperties[FieldProperty.DataType];
switch (dateType)
{
case LibDataType.Text:
case LibDataType.NText:
newRowObj.AppendFormat("{0}:'{1}',", item.ColumnName, LibSysUtils.ToString(item.DefaultValue));
break;
case LibDataType.Int32:
tempBuilder.Append(",type:'number'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToInt32(item.DefaultValue));
break;
case LibDataType.Int64:
tempBuilder.Append(",type:'number'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToInt64(item.DefaultValue));
break;
case LibDataType.Numeric:
tempBuilder.Append(",type:'number'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToDecimal(item.DefaultValue));
break;
case LibDataType.Float:
tempBuilder.Append(",type:'number'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToSingle(item.DefaultValue));
break;
case LibDataType.Double:
tempBuilder.Append(",type:'number'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToDouble(item.DefaultValue));
break;
case LibDataType.Byte:
tempBuilder.Append(",type:'number'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToByte(item.DefaultValue));
break;
case LibDataType.Boolean:
tempBuilder.Append(",type:'boolean'");
newRowObj.AppendFormat("{0}:{1},", item.ColumnName, LibSysUtils.ToBoolean(item.DefaultValue) ? "true" : "false");
break;
case LibDataType.Binary:
newRowObj.AppendFormat("{0}:'{1}',", item.ColumnName, LibSysUtils.ToString(item.DefaultValue));
break;
}
if (r == 0)
{
builder.Append("{" + tempBuilder.ToString() + "}");
}
else
{
builder.Append(",{" + tempBuilder.ToString() + "}");
}
r++;
tempBuilder.Length = 0;
}
newRowObj.Remove(newRowObj.Length - 1, 1);
this.Fields = string.Format("[{0}]", builder.ToString());
this.NewRowObj = "{" + newRowObj.ToString() + "}";
}
public static LibQueryCondition MergeQueryCondition(DataTable table, LibQueryCondition condition, Dictionary <string, List <LibQueryField> > powerQueryFieldDic)
{
if (powerQueryFieldDic == null || powerQueryFieldDic.Count == 0)
{
return(condition);
}
if (condition == null || condition.QueryFields.Count == 0)
{
condition = new LibQueryCondition();
foreach (var item in powerQueryFieldDic)
{
foreach (var subItem in item.Value)
{
condition.QueryFields.Add(subItem);
}
}
return(condition);
}
List <LibQueryField> addList = new List <LibQueryField>();
List <string> removeList = new List <string>();
//将权限（仅存在一个权限设定，即非or的情况）合并到当前用户的选择条件中
foreach (var powerQuery in powerQueryFieldDic)
{
if (powerQuery.Value.Count == 1)
{
bool exist = false;
LibQueryField other = powerQuery.Value[0];
foreach (var item in condition.QueryFields)
{
if (item.Name == powerQuery.Key)
{
exist = true;
DataColumn col = table.Columns[item.Name];
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
switch (dataType)
{
case LibDataType.Text:
case LibDataType.NText:
case LibDataType.Binary:
string curStr1 = item.Value[0].ToString();
string otherStr1 = other.Value[0].ToString();
string curStr2 = string.Empty;
string otherStr2 = string.Empty;
if (item.Value.Count == 2)
{
curStr2 = item.Value[1].ToString();
}
if (other.Value.Count == 2)
{
otherStr2 = other.Value[1].ToString();
}
MergeFieldQuery(addList, item, other, curStr1, otherStr1, curStr2, otherStr2);
break;
case LibDataType.Int32:
int curInt1 = LibSysUtils.ToInt32(item.Value[0]);
int otherInt1 = LibSysUtils.ToInt32(other.Value[0]);
int curInt2 = 0;
int otherInt2 = 0;
if (item.Value.Count == 2)
{
curInt2 = LibSysUtils.ToInt32(item.Value[1]);
}
if (other.Value.Count == 2)
{
otherInt2 = LibSysUtils.ToInt32(other.Value[1]);
}
MergeFieldQuery(addList, item, other, curInt1, otherInt1, curInt2, otherInt2);
break;
case LibDataType.Int64:
long curLong1 = LibSysUtils.ToInt64(item.Value[0]);
long otherLong1 = LibSysUtils.ToInt64(other.Value[0]);
long curLong2 = 0;
long otherLong2 = 0;
if (item.Value.Count == 2)
{
curLong2 = LibSysUtils.ToInt64(item.Value[1]);
}
if (other.Value.Count == 2)
{
otherLong2 = LibSysUtils.ToInt64(other.Value[1]);
}
MergeFieldQuery(addList, item, other, curLong1, otherLong1, curLong2, otherLong2);
break;
case LibDataType.Numeric:
decimal curDecimal1 = LibSysUtils.ToDecimal(item.Value[0]);
decimal otherDecimal1 = LibSysUtils.ToDecimal(other.Value[0]);
decimal curDecimal2 = 0;
decimal otherDecimal2 = 0;
if (item.Value.Count == 2)
{
curDecimal2 = LibSysUtils.ToDecimal(item.Value[1]);
}
if (other.Value.Count == 2)
{
otherDecimal2 = LibSysUtils.ToDecimal(other.Value[1]);
}
MergeFieldQuery(addList, item, other, curDecimal1, otherDecimal1, curDecimal2, otherDecimal2);
break;
case LibDataType.Float:
float curFloat1 = LibSysUtils.ToSingle(item.Value[0]);
float otherFloat1 = LibSysUtils.ToSingle(other.Value[0]);
float curFloat2 = 0;
float otherFloat2 = 0;
if (item.Value.Count == 2)
{
curFloat2 = LibSysUtils.ToSingle(item.Value[1]);
}
if (other.Value.Count == 2)
{
otherFloat2 = LibSysUtils.ToSingle(other.Value[1]);
}
MergeFieldQuery(addList, item, other, curFloat1, otherFloat1, curFloat2, otherFloat2);
break;
case LibDataType.Double:
double curDouble1 = LibSysUtils.ToDouble(item.Value[0]);
double otherDouble1 = LibSysUtils.ToDouble(other.Value[0]);
double curDouble2 = 0;
double otherDouble2 = 0;
if (item.Value.Count == 2)
{
curDouble2 = LibSysUtils.ToDouble(item.Value[1]);
}
if (other.Value.Count == 2)
{
otherDouble2 = LibSysUtils.ToDouble(other.Value[1]);
}
MergeFieldQuery(addList, item, other, curDouble1, otherDouble1, curDouble2, otherDouble2);
break;
case LibDataType.Byte:
byte curByte1 = LibSysUtils.ToByte(item.Value[0]);
byte otherByte1 = LibSysUtils.ToByte(other.Value[0]);
byte curByte2 = 0;
byte otherByte2 = 0;
if (item.Value.Count == 2)
{
curByte2 = LibSysUtils.ToByte(item.Value[1]);
}
if (other.Value.Count == 2)
{
otherByte2 = LibSysUtils.ToByte(other.Value[1]);
}
MergeFieldQuery(addList, item, other, curByte1, otherByte1, curByte2, otherByte2);
break;
case LibDataType.Boolean:
item.QueryChar = other.QueryChar;
item.Value = other.Value;
break;
}
break;
}
}
if (!exist)
{
condition.QueryFields.Add(other);
removeList.Add(powerQuery.Key);
}
}
}
foreach (var item in addList)
{
condition.QueryFields.Add(item);
}
//仅添加合并后剩余的权限条件（仅剩下or条件的权限）
foreach (var item in powerQueryFieldDic)
{
if (!removeList.Contains(item.Key))
{
condition.PowerQueryFieldDic.Add(item.Key, item.Value);
}
}
return(condition);
}
private string GetFieldInfo(DataColumn col, bool addQuto = false)
{
string fieldStr = string.Empty;
string name = col.ColumnName;
int size = col.MaxLength;
string defaultStr = string.Empty;
//string constraint = string.Format("CONSTRAINT NN_{0}_{1} NOT NULL", shortTableName, name);
LibDataType libDataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
switch (libDataType)
{
case LibDataType.Text:
if (!string.IsNullOrEmpty(LibSysUtils.ToString(col.DefaultValue)))
{
defaultStr = string.Format(" DEFAULT '{0}'", addQuto ? string.Format("'{0}'", col.DefaultValue) : col.DefaultValue); //外层execute immediate已经有一个单引号，所以这里是有2个单引号转义
}
fieldStr = string.Format("{0} {1}({2}){3}", name, LibOracleSchema.VARCHAR, size, defaultStr);
break;
case LibDataType.NText:
if (!string.IsNullOrEmpty(LibSysUtils.ToString(col.DefaultValue)))
{
defaultStr = string.Format(" DEFAULT '{0}'", addQuto ? string.Format("'{0}'", col.DefaultValue) : col.DefaultValue);
}
fieldStr = string.Format("{0} {1}({2}){3}", name, LibOracleSchema.NVARCHAR, size, defaultStr);
break;
case LibDataType.Int32:
defaultStr = string.Format(" DEFAULT {0}", col.DefaultValue);
fieldStr = string.Format("{0} {1}{2}", name, LibOracleSchema.INT, defaultStr);
break;
case LibDataType.Int64:
defaultStr = string.Format(" DEFAULT {0}", col.DefaultValue);
fieldStr = string.Format("{0} {1}{2}", name, LibOracleSchema.BIGINT, defaultStr);
break;
case LibDataType.Numeric:
defaultStr = string.Format(" DEFAULT {0}", col.DefaultValue);
fieldStr = string.Format("{0} {1}{2}", name, LibOracleSchema.DECIMAL, defaultStr);
break;
case LibDataType.Float:
case LibDataType.Double:
defaultStr = string.Format(" DEFAULT {0}", col.DefaultValue);
fieldStr = string.Format("{0} {1}{2}", name, LibOracleSchema.FLOAT, defaultStr);
break;
case LibDataType.Byte:
defaultStr = string.Format(" DEFAULT {0}", col.DefaultValue);
fieldStr = string.Format("{0} {1}{2}", name, LibOracleSchema.TINYINT, defaultStr);
break;
case LibDataType.Boolean:
if (LibSysUtils.ToBoolean(col.DefaultValue))
{
defaultStr = " DEFAULT 1";
}
else
{
defaultStr = " DEFAULT 0";
}
fieldStr = string.Format("{0} {1}{2}", name, LibOracleSchema.BIT, defaultStr);
break;
case LibDataType.Binary:
if (!string.IsNullOrEmpty(LibSysUtils.ToString(col.DefaultValue)))
{
defaultStr = string.Format(" DEFAULT '{0}'", addQuto ? string.Format("'{0}'", col.DefaultValue) : col.DefaultValue);
}
fieldStr = string.Format("{0} {1}{2}", name, LibOracleSchema.BINARY, defaultStr);
break;
default:
break;
}
return(fieldStr);
}
public void ExportRadXMLData(string filePath, DataSet dataSet, HashSet <int> tableIndex = null, bool dbField = false)
{
Stopwatch watch = new Stopwatch();
watch.Start();
#region 文件名，路径
string templateFile = System.IO.Path.Combine(AxCRL.Comm.Runtime.EnvProvider.Default.RuningPath, "TempData", "ExcelModel", "List.xml");
String bodyXML = File.ReadAllText(templateFile, Encoding.UTF8);
#endregion
StringBuilder names = new StringBuilder("");
StringBuilder Worksheet = new StringBuilder("");
for (int index = 0; index < dataSet.Tables.Count; index++)
{
StringBuilder headCols = new StringBuilder("");
StringBuilder rows = new StringBuilder("");
if (tableIndex != null && !tableIndex.Contains(index))
{
continue;
}
System.Data.DataTable dt = dataSet.Tables[index];
string tableName = string.Empty;
if (dbField)
{
tableName = dt.TableName;
}
else
{
if (dt.ExtendedProperties.ContainsKey(TableProperty.DisplayText))
{
tableName = LibSysUtils.ToString(dt.ExtendedProperties[TableProperty.DisplayText]);
}
else
{
tableName = dt.TableName;
}
}
names.AppendLine(String.Format("<NamedRange ss:Name=\"{0}\" ss:RefersTo=\"={0}!R1C1:R{1}C{2}\"/>", tableName, (dt.Rows.Count + 1).ToString(), dt.Columns.Count.ToString()));
//如果存在文本列名相同时则需要此结构
Dictionary <string, int> sameColDic = null;
if (!dbField)
{
sameColDic = new Dictionary <string, int>();
}
#region 填充表头
foreach (DataColumn col in dt.Columns)
{
string name = dbField ? col.ColumnName : string.IsNullOrEmpty(col.Caption) ? col.ColumnName : col.Caption;
if (sameColDic.ContainsKey(name))
{
sameColDic[name]++;
name += sameColDic[name].ToString();
}
else
{
sameColDic.Add(name, 0);
}
headCols.Append(String.Format("<Cell><Data ss:Type=\"String\">{0}</Data><NamedCell ss:Name=\"{1}\"/></Cell>\r\n", name, tableName));
}
#endregion
#region 表格具体内容
string type = string.Empty;
object value = string.Empty;
foreach (DataRow curRow in dt.Rows)
{
if (curRow.RowState == DataRowState.Deleted)
{
continue;
}
StringBuilder builder = new StringBuilder();
#region 填充行的格式
rows.Append("<Row>\r\n");
for (int i = 0; i < dt.Columns.Count; i++)
{
string style = string.Empty;
#region 填充的值和类型
DataColumn col = dt.Columns[i];
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
LibControlType ctrlType = (LibControlType)col.ExtendedProperties[FieldProperty.ControlType];
switch (dataType)
{
case LibDataType.Text:
case LibDataType.NText:
case LibDataType.Binary:
case LibDataType.Int64:
if (dataType == LibDataType.Int64 && ctrlType == LibControlType.DateTime)
{
long dateTime = LibSysUtils.ToInt64(curRow[col]);
if (dateTime != 0)
{
type = "DateTime";
style = string.Format(" ss:StyleID=\"s23\"");
value = LibDateUtils.LibDateToDateTime(dateTime).ToString("yyyy-MM-ddTHH:mm:ss");
}
else
{
type = "String";
value = string.Empty;
}
}
else
{
type = "String";
value = curRow[col];
}
break;
case LibDataType.Int32:
case LibDataType.Numeric:
case LibDataType.Float:
case LibDataType.Double:
case LibDataType.Byte:
if (dataType == LibDataType.Int32 && ctrlType == LibControlType.Date)
{
int date = LibSysUtils.ToInt32(curRow[col]);
if (date != 0)
{
type = "DateTime";
value = string.Format("{0}T00:00:00.000", LibDateUtils.LibDateToDateTime(date).ToString("yyyy-MM-dd"));
style = string.Format(" ss:StyleID=\"s23\"");
}
else
{
type = "String";
value = string.Empty;
}
}
else if (dataType == LibDataType.Int32 && ctrlType == LibControlType.HourMinute)
{
type = "Number";
string time = LibSysUtils.ToString(curRow[col]);
switch (time.Length)
{
case 1: time = "000" + time + "00"; break;
case 2: time = "00" + time + "00"; break;
case 3: time = "0" + time + "00"; break;
case 4: time = time + "00"; break;
default: time = time + "00"; break;
}
time = "20150101" + time;
value = LibStringBuilder.GetQuotObject(LibDateUtils.LibDateToDateTime(LibSysUtils.ToInt64(time)).ToString("HH:mm"));
}
else if (dataType == LibDataType.Numeric)
{
type = "Number";
style = string.Format(" ss:StyleID=\"s24\"");
value = curRow[col];
}
else
{
type = "Number";
value = curRow[col];
}
break;
case LibDataType.Boolean:
type = "Number";
value = LibSysUtils.ToBoolean(curRow[col.ColumnName]) ? 1 : 0;
break;
}
#endregion
rows.Append(string.Format("<Cell{3}><Data ss:Type=\"{0}\">{1}</Data><NamedCell ss:Name=\"{2}\"/></Cell>\r\n", type, value, tableName, style));
}
rows.Append("</Row>\r\n");
#endregion
}
#endregion
#region 构建表格模板
Worksheet.AppendLine(string.Format("<Worksheet ss:Name=\"{0}\">\n<Table ss:ExpandedColumnCount=\"{1}\" ss:ExpandedRowCount=\"{2}\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultRowHeight=\"12\">", tableName, dt.Columns.Count.ToString(), (dt.Rows.Count + 1).ToString()));
Worksheet.AppendLine(string.Format(@"<Row>
{0}
</Row>
{1}
</Table>", headCols.ToString(), rows.ToString()));
Worksheet.AppendLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
Worksheet.AppendLine("<PageSetup>");
Worksheet.AppendLine("<Header x:Data=\"&A\"/>");
Worksheet.AppendLine("<Footer x:Data=\"Page &P\"/>");
Worksheet.AppendLine(@"</PageSetup>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>");
#endregion
}
#region 将数据替换到模板中
DateTime datetime = DateTime.Now;
bodyXML = bodyXML.Replace("{##Author##}", "Administrator");
bodyXML = bodyXML.Replace("{##Created##}", datetime.ToString());
bodyXML = bodyXML.Replace("{##Names##}", names.ToString());
bodyXML = bodyXML.Replace("{##Worksheet##}", Worksheet.ToString());
#endregion
try
{
string path = filePath;
using (System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Create))
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fs))
{
sw.Write(bodyXML);
}
}
watch.Stop();
string time = watch.ElapsedMilliseconds.ToString();
}
catch (Exception ex)
{
throw ex;
}
}
public void ExportToExcel(string filePath, DataSet dataSet, HashSet <int> tableIndex = null, bool dbField = false)
{
try
{
IList <string> dmlSqlList = new List <string>();
IList <string> sqlList = new List <string>();
for (int index = 0; index < dataSet.Tables.Count; index++)
{
if (tableIndex != null && !tableIndex.Contains(index))
{
continue;
}
System.Data.DataTable table = dataSet.Tables[index];
//如果存在文本列名相同时则需要此结构
Dictionary <string, int> sameColDic = null;
if (!dbField)
{
sameColDic = new Dictionary <string, int>();
}
string columnStr = string.Empty;
StringBuilder columnDefineBuilder = new StringBuilder();
StringBuilder columnBuilder = new StringBuilder();
foreach (DataColumn col in table.Columns)
{
string name = dbField ? col.ColumnName : string.IsNullOrEmpty(col.Caption) ? col.ColumnName : col.Caption;
if (sameColDic.ContainsKey(name))
{
sameColDic[name]++;
name += sameColDic[name].ToString();
}
else
{
sameColDic.Add(name, 0);
}
columnBuilder.AppendFormat("{0},", name);
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
switch (dataType)
{
case LibDataType.Text:
case LibDataType.NText:
//columnDefineBuilder.AppendFormat("{0} String,", name);
columnDefineBuilder.AppendFormat("{0} memo,", name);
break;
case LibDataType.Int32:
LibControlType ctrlType = (LibControlType)col.ExtendedProperties[FieldProperty.ControlType];
if (ctrlType == LibControlType.Date)
{
columnDefineBuilder.AppendFormat("{0} Date,", name);
}
else if (ctrlType == LibControlType.HourMinute)
{
columnDefineBuilder.AppendFormat("{0} String,", name);
}
else
{
columnDefineBuilder.AppendFormat("{0} Integer,", name);
}
break;
case LibDataType.Int64:
ctrlType = (LibControlType)col.ExtendedProperties[FieldProperty.ControlType];
if (ctrlType == LibControlType.DateTime)
{
columnDefineBuilder.AppendFormat("{0} DateTime,", name);
}
else
{
columnDefineBuilder.AppendFormat("{0} Long,", name);
}
break;
case LibDataType.Numeric:
columnDefineBuilder.AppendFormat("{0} Currency,", name);
break;
case LibDataType.Float:
columnDefineBuilder.AppendFormat("{0} Single,", name);
break;
case LibDataType.Double:
columnDefineBuilder.AppendFormat("{0} Double,", name);
break;
case LibDataType.Byte:
columnDefineBuilder.AppendFormat("{0} Integer,", name);
break;
case LibDataType.Boolean:
columnDefineBuilder.AppendFormat("{0} Integer,", name);
break;
case LibDataType.Binary:
columnDefineBuilder.AppendFormat("{0} memo,", name);
break;
}
}
if (columnBuilder.Length > 0)
{
columnBuilder.Remove(columnBuilder.Length - 1, 1);
columnDefineBuilder.Remove(columnDefineBuilder.Length - 1, 1);
}
columnStr = columnBuilder.ToString();
string tableName = string.Empty;
if (dbField)
{
tableName = table.TableName;
}
else
{
if (table.ExtendedProperties.ContainsKey(TableProperty.DisplayText))
{
tableName = LibSysUtils.ToString(table.ExtendedProperties[TableProperty.DisplayText]);
}
else
{
tableName = table.TableName;
}
}
dmlSqlList.Add(string.Format("CREATE TABLE {0} ({1})", tableName, columnDefineBuilder.ToString()));
foreach (DataRow curRow in table.Rows)
{
if (curRow.RowState == DataRowState.Deleted)
{
continue;
}
StringBuilder builder = new StringBuilder();
for (int i = 0; i < table.Columns.Count; i++)
{
DataColumn col = table.Columns[i];
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
LibControlType ctrlType = (LibControlType)col.ExtendedProperties[FieldProperty.ControlType];
switch (dataType)
{
case LibDataType.Text:
case LibDataType.NText:
case LibDataType.Binary:
case LibDataType.Int64:
if (dataType == LibDataType.Int64 && ctrlType == LibControlType.DateTime)
{
long dateTime = LibSysUtils.ToInt64(curRow[col]);
if (dateTime != 0)
{
builder.AppendFormat("{0},", LibStringBuilder.GetQuotObject(LibDateUtils.LibDateToDateTime(dateTime).ToString("yyyy-MM-dd HH:mm:ss")));
}
else
{
builder.Append("null,");
}
}
else
{
builder.AppendFormat("{0},", LibStringBuilder.GetQuotObject(curRow[col]));
}
break;
case LibDataType.Int32:
case LibDataType.Numeric:
case LibDataType.Float:
case LibDataType.Double:
case LibDataType.Byte:
if (dataType == LibDataType.Int32 && ctrlType == LibControlType.Date)
{
int date = LibSysUtils.ToInt32(curRow[col]);
if (date != 0)
{
builder.AppendFormat("{0},", LibStringBuilder.GetQuotObject(LibDateUtils.LibDateToDateTime(date).ToLongDateString()));
}
else
{
builder.Append("null,");
}
}
else if (dataType == LibDataType.Int32 && ctrlType == LibControlType.HourMinute)
{
string time = LibSysUtils.ToString(curRow[col]);
switch (time.Length)
{
case 1: time = "000" + time + "00"; break;
case 2: time = "00" + time + "00"; break;
case 3: time = "0" + time + "00"; break;
case 4: time = time + "00"; break;
default: time = time + "00"; break;
}
time = "20150101" + time;
builder.AppendFormat("{0},", LibStringBuilder.GetQuotObject(LibDateUtils.LibDateToDateTime(LibSysUtils.ToInt64(time)).ToString("HH:mm")));
}
else
{
builder.AppendFormat("{0},", curRow[col]);
}
break;
case LibDataType.Boolean:
builder.AppendFormat("{0},", LibSysUtils.ToBoolean(curRow[col.ColumnName]) ? 1 : 0);
break;
}
}
if (builder.Length > 0)
{
builder.Remove(builder.Length - 1, 1);
}
sqlList.Add(string.Format("insert into {0}({1}) values({2})", tableName, columnBuilder, builder.ToString()));
}
}
string connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}'; Extended Properties='Excel 8.0;HDR=Yes;IMEX=2,ReadOnly=False'", filePath);
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
try
{
foreach (string sql in dmlSqlList)
{
using (OleDbCommand command = new OleDbCommand(sql, conn))
{
command.ExecuteNonQuery();
}
}
foreach (string sql in sqlList)
{
using (OleDbCommand command = new OleDbCommand(sql, conn))
{
command.ExecuteNonQuery();
}
}
}
finally
{
conn.Close();
}
}
}
catch (Exception ex)
{
string path = System.IO.Path.Combine(AxCRL.Comm.Runtime.EnvProvider.Default.MainPath, "Output", "Error", "Excel", string.Format("{0}.txt", DateTime.Now.Ticks));
using (System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Create))
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fs))
{
sw.Write(ex);
}
}
throw;
}
}
private static void BuildQueryStr(LibDataType dataType, LibQueryField queryField, StringBuilder builder, string prefix, bool needAnd, string realName)
{
bool needQuot = dataType == LibDataType.Text || dataType == LibDataType.NText;
string addStr = needAnd ? "and " : string.Empty;
string fieldName = string.IsNullOrEmpty(realName) ? queryField.Name : realName;
if (!string.IsNullOrEmpty(prefix))
{
prefix = string.Format("{0}.", prefix);
}
switch (queryField.QueryChar)
{
case LibQueryChar.Equal:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
builder.AppendFormat("{0}{1}={2} {3}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), addStr);
}
else
{
builder.AppendFormat("{0}{1}={2} {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
case LibQueryChar.Contain:
if (queryField.Value.Count > 0)
{
builder.AppendFormat("{0}{1} like '%{2}%' {3}", prefix, fieldName, queryField.Value[0], addStr);
}
break;
case LibQueryChar.Region:
if (queryField.Value.Count == 2)
{
if (needQuot)
{
builder.AppendFormat("{0}{1} between {2} and {3} {4}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), LibStringBuilder.GetQuotObject(queryField.Value[1]), addStr);
}
else
{
builder.AppendFormat("{0}{1} between {2} and {3} {4}", prefix, fieldName, queryField.Value[0], queryField.Value[1], addStr);
}
}
break;
case LibQueryChar.GreaterOrEqual:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
builder.AppendFormat("{0}{1}>={2} {3}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), addStr);
}
else
{
builder.AppendFormat("{0}{1}>={2} {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
case LibQueryChar.LessOrEqual:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
builder.AppendFormat("{0}{1}<={2} {3}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), addStr);
}
else
{
builder.AppendFormat("{0}{1}<={2} {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
case LibQueryChar.GreaterThan:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
builder.AppendFormat("{0}{1}>{2} {3}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), addStr);
}
else
{
builder.AppendFormat("{0}{1}>{2} {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
case LibQueryChar.LessThan:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
builder.AppendFormat("{0}{1}<{2} {3}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), addStr);
}
else
{
builder.AppendFormat("{0}{1}<{2} {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
case LibQueryChar.UnequalTo:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
builder.AppendFormat("{0}{1}<>{2} {3}", prefix, fieldName, LibStringBuilder.GetQuotObject(queryField.Value[0]), addStr);
}
else
{
builder.AppendFormat("{0}{1}<>{2} {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
case LibQueryChar.Include:
if (queryField.Value.Count > 0)
{
if (needQuot)
{
StringBuilder tempBuilder = new StringBuilder();
string[] dest = queryField.Value[0].ToString().Split(',');
for (int i = 0; i < dest.Length; i++)
{
if (i == 0)
{
tempBuilder.AppendFormat("{0}", LibStringBuilder.GetQuotString(dest[i]));
}
else
{
tempBuilder.AppendFormat(",{0}", LibStringBuilder.GetQuotString(dest[i]));
}
}
builder.AppendFormat("{0}{1} in ({2}) {3}", prefix, fieldName, tempBuilder.ToString(), addStr);
}
else
{
builder.AppendFormat("{0}{1} in ({2}) {3}", prefix, fieldName, queryField.Value[0], addStr);
}
}
break;
}
}
public object GetValueByName(string progId, object[] pks, string name)
{
object value = null;
string key = BuildCacheKey(progId, pks);
Dictionary <string, object> destObj = this.Get <Dictionary <string, object> >(key);
if (destObj == null)
{
destObj = new Dictionary <string, object>();
LibSqlModel sqlModel = LibSqlModelCache.Default.GetSqlModel(progId);
DataColumnCollection columns = sqlModel.Tables[0].Columns;
//说明缓存不存在则需创建
StringBuilder whereBuilder = new StringBuilder();
for (int i = 0; i < sqlModel.Tables[0].PrimaryKey.Length; i++)
{
if (i != 0)
{
whereBuilder.AppendFormat(" AND ");
}
if (pks[i].GetType() == typeof(string))
{
whereBuilder.AppendFormat("A.{0}={1}", sqlModel.Tables[0].PrimaryKey[i].ColumnName, LibStringBuilder.GetQuotObject(pks[i]));
}
else
{
whereBuilder.AppendFormat("A.{0}={1}", sqlModel.Tables[0].PrimaryKey[i].ColumnName, pks[i]);
}
}
SqlBuilder sqlBuilder = new SqlBuilder(progId);
string sql = sqlBuilder.GetQuerySql(0, "A.*", whereBuilder.ToString());
//TODO固定字段应排除
LibDataAccess dataAccess = new LibDataAccess();
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
if (reader.Read())
{
int count = reader.FieldCount;
for (int i = 0; i < count; i++)
{
string columnName = reader.GetName(i);
object columnValue = reader.GetValue(i);
if (dataAccess.DatabaseType == LibDatabaseType.Oracle && columnValue.GetType() == typeof(decimal))
{ //如果是oracle 数值类型都是number
LibDataType dataType = (LibDataType)((int)columns[columnName].ExtendedProperties[FieldProperty.DataType]);
switch (dataType)
{
case LibDataType.Int32:
columnValue = decimal.ToInt32((decimal)columnValue);
break;
case LibDataType.Int64:
columnValue = decimal.ToInt64((decimal)columnValue);
break;
case LibDataType.Float:
columnValue = decimal.ToSingle((decimal)columnValue);
break;
case LibDataType.Double:
columnValue = decimal.ToDouble((decimal)columnValue);
break;
case LibDataType.Byte:
columnValue = decimal.ToByte((decimal)columnValue);
break;
case LibDataType.Boolean:
columnValue = (decimal)columnValue == decimal.Zero ? false : true;
break;
}
}
destObj.Add(columnName, columnValue);
}
}
}
if (destObj.Count > 0)
{
//CacheItemPolicy policy = new CacheItemPolicy();
//policy.SlidingExpiration = new TimeSpan(0, 180, 0); //30分钟内不访问自动剔除
_Default.Set(key, destObj, new TimeSpan(0, 180, 0));
}
}
destObj.TryGetValue(name, out value);
return(value);
}
public IList <FuzzyResult> FuzzySearchField(int tableIndex, string fieldName, string relSource, string relName,
string query, object[] curPks = null, Dictionary <string, object> selConditionParam = null, string[] currentPks = null)
{
IList <FuzzyResult> list = new List <FuzzyResult>();
RelativeSourceCollection relSources = (RelativeSourceCollection)this.DataSet.Tables[tableIndex].Columns[fieldName].ExtendedProperties[FieldProperty.RelativeSource];
RelativeSource curRelSource = null;
foreach (RelativeSource item in relSources)
{
if (string.Compare(relSource, item.RelSource, true) == 0)
{
curRelSource = item;
break;
}
}
if (curRelSource == null)
{
return(list);
}
SqlBuilder sqlBuilder = new SqlBuilder(relSource);
StringBuilder builder = new StringBuilder();
LibBcfBase bcfBase = LibBcfSystem.Default.GetBcfInstance(relSource);
BillType billType = bcfBase.Template.BillType;
if (relSource.Split(new string[] { "axp" }, StringSplitOptions.None).Length == 1 && (billType == BillType.Bill || billType == BillType.Master))
{
builder.AppendFormat("And A.CURRENTSTATE=2");
}
if (curPks != null && curPks.Length > 0)
{
builder.AppendFormat(" And {0}", GetRelWhere(relSource, curRelSource.TableIndex, 'A', curPks));
}
string selCondition = string.Empty;
if (curRelSource.SelConditions.Count > 0)
{
foreach (SelCondition item in curRelSource.SelConditions)
{
builder.AppendFormat(" And {0}", item.Condition);
}
selCondition = builder.ToString();
selCondition = selCondition.Replace("CURRENT_PERSON", LibStringBuilder.GetQuotObject(this.Handle.PersonId));
if (selConditionParam != null && selConditionParam.Count > 0)
{
LibSqlModel model = LibSqlModelCache.Default.GetSqlModel(this.ProgId);
if (model != null)
{
foreach (KeyValuePair <string, object> item in selConditionParam)
{
string[] temp = item.Key.Split('.');
int index = (int)temp[0][0] - (int)'A';
DataColumn col = model.Tables[index].Columns[temp[1]];
LibDataType dataType = (LibDataType)col.ExtendedProperties[FieldProperty.DataType];
if (dataType == LibDataType.Text)
{
selCondition = selCondition.Replace(string.Format("@{0}", item.Key), LibStringBuilder.GetQuotObject(item.Value));
}
else
{
selCondition = selCondition.Replace(string.Format("@{0}", item.Key), item.Value.ToString());
}
}
}
}
}
else
{
selCondition = builder.ToString();
}
string powerStr = LibPermissionControl.Default.GetShowCondition(this.Handle, relSource, this.Handle.PersonId);
if (!string.IsNullOrEmpty(powerStr))
{
selCondition = string.Format("{0} and {1}", selCondition, powerStr);
}
if (curRelSource.ContainsSub == false && string.IsNullOrEmpty(curRelSource.ParentColumnName) == false &&
currentPks != null && currentPks.Length > 0 && string.IsNullOrEmpty(currentPks[0]) == false)
{
//对于父子结构数据，如果不包含子数据且指定了父列外键列的名称，则添加额外的查询条件 Zhangkj 20170316
DataColumn keyColumn = this.DataSet.Tables[tableIndex].PrimaryKey[0];
string keyColumnName = this.DataSet.Tables[tableIndex].PrimaryKey[0].ColumnName;//目前仅支持单主键
string dataId = currentPks[0];
LibDataType dataType = keyColumn.ExtendedProperties.ContainsKey(FieldProperty.DataType) ? (LibDataType)keyColumn.ExtendedProperties[FieldProperty.DataType] : LibDataTypeConverter.ConvertToLibType(keyColumn.DataType);
List <object> subIds = this.GetSubDataIds(dataType, dataId, this.DataSet.Tables[tableIndex].TableName, keyColumnName, curRelSource.ParentColumnName, true);
if (subIds != null && subIds.Count > 0)
{
bool needQuot = dataType == LibDataType.Text || dataType == LibDataType.NText;
List <object> quotSubIds = new List <object>();
foreach (object obj in subIds)
{
quotSubIds.Add((needQuot) ? LibStringBuilder.GetQuotObject(obj) : obj);
}
selCondition = string.Format("{0} and A.{1} not in ({2})", selCondition, keyColumnName, string.Join(",", quotSubIds));
}
}
string sql = sqlBuilder.GetFuzzySql(curRelSource.TableIndex, relSources, query, selCondition, curRelSource.ParentColumnName);
LibDataAccess dataAccess = new LibDataAccess();
int count = 0;
int filterCount = curRelSource.SearchFilterCount;
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
while (reader.Read())
{
if (reader.FieldCount == 1)
{
list.Add(new FuzzyResult(LibSysUtils.ToString(reader[0]), string.Empty));
}
else if (reader.FieldCount == 2)
{
list.Add(new FuzzyResult(LibSysUtils.ToString(reader[0]), LibSysUtils.ToString(reader[1])));
}
else if (reader.FieldCount == 3)
{
FuzzyResult fuzzyResult = new FuzzyResult(LibSysUtils.ToString(reader[0]), LibSysUtils.ToString(reader[1]));
fuzzyResult.ContainsKeyField = LibSysUtils.ToString(reader[2]);//将除Id Name列以外的包含查询关键字的列的内容
list.Add(fuzzyResult);
}
else if (reader.FieldCount == 4)
{
FuzzyResult fuzzyResult = new FuzzyResult(LibSysUtils.ToString(reader[0]), LibSysUtils.ToString(reader[1]));
fuzzyResult.ContainsKeyField = LibSysUtils.ToString(reader[2]); //将除Id Name列以外的包含查询关键字的列的内容
fuzzyResult.ParentId = LibSysUtils.ToString(reader[3]); //树形结构的父数据Id
if (curRelSource.ExpandAll)
{
fuzzyResult.TreeNodeExpanded = true;
}
list.Add(fuzzyResult);
}
count++;
if (count == filterCount)
{
break;
}
}
}
if (list.Count > 1)
{
LibControlType controlType = (LibControlType)this.DataSet.Tables[tableIndex].Columns[fieldName].ExtendedProperties[FieldProperty.ControlType];
if (controlType == LibControlType.IdNameTree && string.IsNullOrEmpty(curRelSource.ParentColumnName) == false)
{
//处理树形结构数据
List <FuzzyResult> newList = list.ToList();//先全部放入
List <FuzzyResult> tempList = null;
using (MemoryStream stream = new MemoryStream())
{
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter formatter = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
formatter.Serialize(stream, list.ToList());
stream.Position = 0;
tempList = formatter.Deserialize(stream) as List <FuzzyResult>;//深度复制一份
}
//查找是其他节点的子节点的进行处理
int index = 0;
while (index < list.Count)
{
FuzzyResult child = list[index];
FuzzyResult parent = (from re in list
where re != null && re.Id.Equals(child.ParentId)
select re).FirstOrDefault();
if (parent != default(FuzzyResult))
{
newList.Remove(child);
parent.Children.Add(child);
}
index++;
}
newList[0].TotalList = tempList;
list = newList;
}
}
return(list);
}
/// <summary>
/// 获取指定数据的子数据主键值列表。
/// </summary>
/// <param name="dataType">数据类型</param>
/// <param name="dataId">数据主键标识</param>
/// <param name="tabName">数据表名称</param>
/// <param name="keyIdColumnName">主键列名</param>
/// <param name="parenetIdColumnName">关联到父数据的数据列名称</param>
/// <param name="isContainsSelf">是否包含自身，默认为false</param>
/// <returns></returns>
public List <object> GetSubDataIds(LibDataType dataType, object dataId, string tabName, string keyIdColumnName, string parenetIdColumnName, bool isContainsSelf = false)
{
List <object> list = new List <object>();
bool needQuot = dataType == LibDataType.Text || dataType == LibDataType.NText;
if (string.IsNullOrEmpty(tabName.Trim()) || string.IsNullOrEmpty(keyIdColumnName.Trim()) || string.IsNullOrEmpty(parenetIdColumnName.Trim()))
{
return(list);
}
try
{
//从本级数据开始到最底级目录。第一行为本级数据（dataId标识的数据），下面的是按层级的子级数据
string sqlFindSub = "";
string tempTableName = string.Format("{0}_{1}", "temp", DateTime.Now.Ticks);
if (this.DataAccess.DatabaseType == LibDatabaseType.SqlServer)
{
sqlFindSub = string.Format(" with {0} as " +
" ( " +
" select a.{1},a.{2} from {3} a where {1} = {4} " +
" union all " +
" select k.{1},k.{2} from {3} k inner " +
" join {0} t on t.{1} = k.{2} " +
" ) select * from " + tempTableName,
tempTableName, keyIdColumnName.Trim(), parenetIdColumnName.Trim(), tabName.Trim(), (needQuot) ? LibStringBuilder.GetQuotObject(dataId) : dataId);
}
else
{
//Oracle的递归查询待测试
sqlFindSub = string.Format("select {0},{1} " +
" from {2} " +
" START WITH {0} = {3} " +
" CONNECT BY PRIOR {0} = {1} ", keyIdColumnName.Trim(), parenetIdColumnName.Trim(), tabName.Trim(), (needQuot) ? LibStringBuilder.GetQuotObject(dataId) : dataId);
}
DataTable subDirDt = null;
DataSet ds2 = this.DataAccess.ExecuteDataSet(sqlFindSub);
if (ds2 != null && ds2.Tables.Count > 0)
{
subDirDt = ds2.Tables[0];
}
if (subDirDt != null && subDirDt.Rows.Count > 0)
{
DataRow row = null;
//正序，从当前数据向下级数据方向
object id = null;
for (int i = (isContainsSelf ? 0 : 1); i < subDirDt.Rows.Count; i++)
{
row = subDirDt.Rows[i];
if (needQuot)
{
id = LibSysUtils.ToString(row[keyIdColumnName.Trim()]);
}
else
{
if (row[keyIdColumnName.Trim()] != DBNull.Value)
{
id = row[keyIdColumnName.Trim()];
}
}
if (list.Contains(id) == false)
{
list.Add(id);
}
}
}
}
catch (Exception exp)
{
//to do log
throw exp;
}
return(list);
}
public LibQueryField(string field, string displayText, LibDataType dataType)
{
this._Field = field;
this._DisplayText = displayText;
this._DataType = dataType;
}
public ColumnSchema(DataColumn field, string tableName)
{
this.Name = field.ColumnName;
this.Size = field.MaxLength;
LibDataType libDataType = (LibDataType)field.ExtendedProperties[FieldProperty.DataType];
string defaultValueForamt = "CONSTRAINT [DF_{0}_{1}] DEFAULT {2} ";
switch (libDataType)
{
case LibDataType.Text:
this.ColumnType = LibDbSchema.VARCHAR;
this.DefaultValue = string.Format("('{0}')", LibSysUtils.ToString(field.DefaultValue));
if (this.Size == -1)
{
this._ColumnTypeStr = string.Format("[{0}] [{1}](MAX) NOT NULL ", this.Name, LibDbSchema.VARCHAR);
}
else
{
this._ColumnTypeStr = string.Format("[{0}] [{1}]({2}) NOT NULL ", this.Name, LibDbSchema.VARCHAR, this.Size);
}
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.NText:
this.ColumnType = LibDbSchema.NVARCHAR;
this.DefaultValue = string.Format("('{0}')", LibSysUtils.ToString(field.DefaultValue));
if (this.Size == -1)
{
this._ColumnTypeStr = string.Format("[{0}] [{1}](MAX) NOT NULL", this.Name, LibDbSchema.NVARCHAR);
}
else
{
this._ColumnTypeStr = string.Format("[{0}] [{1}]({2}) NOT NULL", this.Name, LibDbSchema.NVARCHAR, this.Size);
}
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Int32:
this.ColumnType = LibDbSchema.INT;
this.DefaultValue = string.Format("(({0}))", LibSysUtils.ToInt32(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] [{1}] NOT NULL", this.Name, LibDbSchema.INT);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Int64:
this.ColumnType = LibDbSchema.BIGINT;
this.DefaultValue = string.Format("(({0}))", LibSysUtils.ToInt64(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] [{1}] NOT NULL", this.Name, LibDbSchema.BIGINT);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Numeric:
this.ColumnType = LibDbSchema.DECIMAL;
this.DefaultValue = string.Format("(({0}))", LibSysUtils.ToDecimal(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] [{1}](18,9) NOT NULL", this.Name, LibDbSchema.DECIMAL);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Float:
case LibDataType.Double:
this.ColumnType = LibDbSchema.FLOAT;
this.DefaultValue = string.Format("(({0}))", LibSysUtils.ToSingle(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] [{1}] NOT NULL", this.Name, LibDbSchema.FLOAT);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Byte:
this.ColumnType = LibDbSchema.TINYINT;
this.DefaultValue = string.Format("(({0}))", LibSysUtils.ToByte(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] [{1}] NOT NULL", this.Name, LibDbSchema.TINYINT);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Boolean:
this.ColumnType = LibDbSchema.BIT;
this.DefaultValue = string.Format("(({0}))", LibSysUtils.ToInt32(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] [{1}] NOT NULL", this.Name, LibDbSchema.BIT);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Binary:
this.ColumnType = LibDbSchema.BINARY;
this.DefaultValue = string.Format("('{0}')", LibSysUtils.ToString(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] {1} NULL", this.Name, LibDbSchema.BINARY);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.DateTime:
this.ColumnType = LibDbSchema.DateTime;
this.DefaultValue = string.Format("('{0}')", LibSysUtils.ToString(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] {1} NULL", this.Name, LibDbSchema.DateTime);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Date:
this.ColumnType = LibDbSchema.DateTime;
this.DefaultValue = string.Format("('{0}')", LibSysUtils.ToString(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] {1} NULL", this.Name, LibDbSchema.Date);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
case LibDataType.Time:
this.ColumnType = LibDbSchema.Date;
this.DefaultValue = string.Format("('{0}')", LibSysUtils.ToString(field.DefaultValue));
this._ColumnTypeStr = string.Format("[{0}] {1} NULL", this.Name, LibDbSchema.Time);
this._DefaultValueStr = string.Format(defaultValueForamt, tableName, this.Name, this.DefaultValue);
break;
default:
break;
}
}
