public static Sql BuildUpdateSql(FBDataModelObjects obj, DataRow row)
{
StringBuilder sb = new StringBuilder();
//拼接字段
string aliasTableName = obj.Label;
string tableName = obj.Code;//表名
sb.AppendFormat(" UPDATE {0} SET ", obj.Code);
int index = 0;
ArrayList paramvalues = new ArrayList();
foreach (FBDataModelCols col in obj.ColList)
{
if (col.isRelated != "1" && col.isUpdate == "1" && col.isPrimary != "1")
{
//关联字段的情况下
sb.AppendFormat("{0}=@{1},", col.Code, index.ToString());
paramvalues.Add(row[col.Code]);//记录字段值内容
index++;
}
}
sb.Remove(sb.Length - 1, 1);
sb.AppendFormat(" where {0}=@{1}", obj.PKCOLName, index.ToString());
paramvalues.Add(row[obj.PKCOLName]);
object[] para = new object[paramvalues.Count];
for (int i = 0; i < para.Length; i++)
{
para[i] = paramvalues[i];
}
Sql sql = new Sql(sb.ToString(), para);
return(sql);
}
public FBDataModelObjects GetObjectModel(string ModelID, string ObjectID, bool hasDetail)
{
Sql sql = new Sql(@"select * from FBDataModelObjects where ModelID=@0 and ID=@1", ModelID, ObjectID);
FBDataModelObjects model = base.Db.FirstOrDefault <FBDataModelObjects>(sql);
if (model.ParentID != "")
{
model.ParentObjectName = Db.Single <string>("select Name from FBDataModelObjects where ID=@0", model.ParentID);
}
if (hasDetail)
{
//获取字段信息
model.ColList = base.Db.Fetch <FBDataModelCols>(
new Sql(@"select * from FBDataModelCols where ModelObjectID=@0 and ModelID=@1 order by ord",
model.ID, model.ModelID));
model.DeleteCheckList = this.GetObjectDeleteCheckList(ModelID, model.ObjectID);
model.ModifyCheckList = this.GetObjectModifyCheckList(ModelID, model.ObjectID);
}
return(model);
}
/// <summary>
/// 获取最大分级码
/// </summary>
/// <param name="tree"></param>
/// <param name="path"></param>
/// <param name="level"></param>
/// <param name="isSame"></param>
/// <returns></returns>
public string getMaxPath(FBDataModelObjects objectModel, string path, string level, bool isSame, ref DataSet ds, string mainCode)
{
if (string.IsNullOrEmpty(level))
{
level = "1";
}
if (string.IsNullOrEmpty(path))
{
path = "";
}
var format = tree.format == "" ? "44444444444444444444" : tree.format;
var formatIndexLen = 4;
var alllen = 0;
//var indexLen = 0;
var i = 1;
foreach (var len in format)
{
//indexLen += int.Parse(len.ToString());
//2
if (i >= int.Parse(level))
{
formatIndexLen = Convert.ToInt32(len.ToString());
if (!isSame)
{
alllen += int.Parse(len.ToString());
}
break;
}
alllen += int.Parse(len.ToString());
i++;
}
if (isSame)
{
path = path.Substring(0, alllen); //3001
//处理path
}
else
{
level = (int.Parse(level) + 1).ToString();
}
var sql = "select max({0}) from {1} where {0} like '{2}%' and {3}='{4}'";
sql = string.Format(sql, tree.grade, objectModel.Code, path, tree.level, level);
var maxPath = db.Single <string>(sql);
if (string.IsNullOrEmpty(maxPath))
{
maxPath = path + "1".ToString().PadLeft(formatIndexLen, '0');
}
else
{
maxPath = maxPath.Substring(0, alllen)
+ (int.Parse(maxPath.Substring(alllen, maxPath.Length - alllen)) + 1).ToString().PadLeft(formatIndexLen, '0');
}
ds.Tables[mainCode].Rows[0][tree.grade] = maxPath;
return(maxPath);
}
public static Sql BuildDeleteSql(FBDataModelObjects obj)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("delete from {0} where 1=1 ", obj.Code);//删除主表信息
//拼接字段
Sql sql = new Sql(sb.ToString());
return(sql);
}
public static Sql BuildDeleteMainSql(FBDataModelObjects obj, string dataid)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("delete from {0} where 1=1 and {1}='{2}'", obj.Code, obj.PKCOLName, dataid);//删除主表信息
//拼接字段
Sql sql = new Sql(sb.ToString());
return(sql);
}
public void SaveObject(FBDataModelObjects model)
{
try
{
base.Db.BeginTransaction();
base.Db.Save <FBDataModelObjects>(model);
base.Db.Execute(new Sql(@" delete from FBDataModelCols where ModelID =@0 and ModelObjectID =@1 and isRelated='0'", model.ModelID, model.ID));
int i = 0;
foreach (FBDataModelCols col in model.ColList.Where(p => p.isRelated == "0").ToList())
{
if (string.IsNullOrEmpty(col.ID))
{
col.ID = Guid.NewGuid().ToString();
}
col.Ord = i.ToString();
i++;
col.ModelObjectID = model.ID;
base.Db.Save <FBDataModelCols>(col);
}
base.Db.Execute(new Sql(@" delete from FBModelDeleteCheck where ModelID =@0 and ObjectID =@1 ", model.ModelID, model.ObjectID));
foreach (FBModelDeleteCheck item in model.DeleteCheckList)
{
if (string.IsNullOrEmpty(item.ID))
{
item.ID = Guid.NewGuid().ToString();
}
base.Db.Save <FBModelDeleteCheck>(item);
}
base.Db.Execute(new Sql(@" delete from FBModelModifyCheck where ModelID =@0 and ObjectID =@1 ", model.ModelID, model.ObjectID));
foreach (FBModelModifyCheck item in model.ModifyCheckList)
{
if (string.IsNullOrEmpty(item.ID))
{
item.ID = Guid.NewGuid().ToString();
}
item.ModelID = model.ModelID;
item.ObjectID = model.ObjectID;
base.Db.Save <FBModelModifyCheck>(item);
}
saveDependence(model.ModelID, base.Db);
this.Db.CompleteTransaction();
}
catch (Exception ex)
{
base.Db.AbortTransaction();
throw ex;
}
}
/// <summary>
/// 创建新增语句
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static List <Sql> BuildInsertSql(FBDataModelObjects obj, DataSet ds)
{
//拼接字段
string aliasTableName = obj.Label;
string tableName = obj.Code;//表名
List <Sql> sqlList = new List <Sql>();
foreach (DataRow row in ds.Tables[obj.Code].Rows)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" INSERT INTO {0} (", obj.Code);
foreach (FBDataModelCols col in obj.ColList)
{
if (col.isRelated != "1" && col.isUpdate == "1")
{
//关联字段的情况下
sb.AppendFormat("{0},", col.Code);
}
}
//这里是否可以缓存？
sb.Remove(sb.Length - 1, 1);
sb.Append(") VALUES (");
int index = 0;
// ArrayList paramvalues = new ArrayList();
List <object> list = new List <object>();
foreach (FBDataModelCols col in obj.ColList)
{
if (col.isRelated != "1" && col.isUpdate == "1")
{
//关联字段的情况下
sb.AppendFormat("@{0},", index.ToString());
//paramvalues.Add(row[col.Code]);//记录字段值内容
list.Add(row[col.Code]);
index++;
}
}
//这里是否可以缓存？
object[] para = list.ToArray();
sb.Remove(sb.Length - 1, 1);
sb.Append(")");
Sql sql = new Sql(sb.ToString(), para);
sqlList.Add(sql);
}
return(sqlList);
}
public void updateMXField(FBDataModelObjects objectModel, TreeNode tree)
{
this.tree = Newtonsoft.Json.JsonConvert.DeserializeObject <JFBTreeStruct>(objectModel.Tree);
var sql = "update {0} set {1}='0' where {2}='{3}'";
if (!string.IsNullOrEmpty(this.tree.isdetail))
{
var datatypeList = objectModel.ColList.Where(p => p.Code == this.tree.isdetail).ToList();
if (datatypeList.Count == 1 && datatypeList[0].DataType == "6")
{
//bit类型
sql = "update {0} set {1}=@0 where {2}='{3}'";
Sql execSql = new Sql(string.Format(sql, objectModel.Code, this.tree.isdetail, objectModel.PKCOLName, tree.dataid), false);
db.Execute(execSql);
}
else
{
Sql execSql = new Sql(string.Format(sql, objectModel.Code, this.tree.isdetail, objectModel.PKCOLName, tree.dataid));
db.Execute(execSql);
}
}
if (!string.IsNullOrEmpty(this.tree.ischild) && this.tree.ischild != this.tree.isdetail)
{
// 是否child定义并且 两个字段不一致 才更新 一致的话不更新
var datatypeList = objectModel.ColList.Where(p => p.Code == this.tree.ischild).ToList();
if (datatypeList.Count == 1 && datatypeList[0].DataType == "6")
{
//bit类型
sql = "update {0} set {1}=@0 where {2}='{3}'";
Sql execSql = new Sql(string.Format(sql, objectModel.Code, this.tree.ischild, objectModel.PKCOLName, tree.dataid), false);
db.Execute(execSql);
}
else
{
Sql execSql = new Sql(string.Format(sql, objectModel.Code, this.tree.ischild, objectModel.PKCOLName, tree.dataid));
db.Execute(execSql);
}
}
}
public void updateParentMXField(FBDataModelObjects objectModel, string dataid, Dictionary <string, object> dict)
{
string parentID = dict[this.tree.parentid].ToString();
var tableName = objectModel.Code;
//this.tree = Newtonsoft.Json.JsonConvert.DeserializeObject<JFBTreeStruct>(objectModel.Tree);
if (!string.IsNullOrEmpty(this.tree.parentid))
{
//父子结构
string vssql = "select count(1) from {0} where {1}=@0";
vssql = string.Format(vssql, objectModel.Code, this.tree.parentid);
if (db.ExecuteScalar <long>(new Sql(vssql, parentID)) == 0)
{
Sql execSql = new Sql(string.Format("update {0} set {1}=@1 where {2}=@0", objectModel.Code, this.tree.isdetail, objectModel.PKCOLName), parentID, 1);
db.Execute(execSql);
}
}
}
public void AddObject(string modelID, string objectID)
{
//获取数据对象列表
//获取数据对象字段信息
//处理数据
//保存
try
{
base.Db.BeginTransaction();
if (base.Db.Single <long>(new Sql("select count(1) from FBDataModelObjects where objectid=@0 and modelID=@1", objectID, modelID)) > 0)
{
throw new Exception("数据模型中已存在该数据对象！");
}
List <FBDataModelCols> list = new List <FBDataModelCols>();
FBDataModelObjects model = new FBDataModelObjects();
FBDataObject obj_model = new FBDataObject();
obj_model = base.Db.SingleById <FBDataObject>(objectID);
List <FBDataObjectCols> obj_list = new List <FBDataObjectCols>();
obj_list = base.Db.Fetch <FBDataObjectCols>(new Sql(@"select * from FBDataObjectCols where ObjectID=@0", objectID));
model.ID = Guid.NewGuid().ToString();
model.Code = obj_model.Code;
model.Name = obj_model.AiasName;
model.ModelID = modelID;
model.ObjectID = objectID;
model.isMain = "0";
model.Label = "";
model.isSave = "1";//默认是否保存勾选
model.Level = "2";
model.ParentID =
base.Db.Single <string>(
new Sql(@"select id from FBDataModelObjects where ModelID=@0 and level='1' and isMain='1'", modelID));
model.PKCOLName = obj_list.Where(p => p.IsPrimary == "1").ToList()[0].Code;
model.Filter = "";
model.Sort = "";
base.Db.Save <FBDataModelObjects>(model);
int i = 0;
foreach (FBDataObjectCols col in obj_list)
{
FBDataModelCols mcol = new FBDataModelCols();
mcol.ID = Guid.NewGuid().ToString();
mcol.Code = col.Code;
mcol.Name = col.Name;
mcol.ModelID = modelID;
mcol.ModelObjectID = model.ID;
mcol.Label = col.Code;
mcol.isCard = "1";
mcol.isList = "1";
mcol.isReadOnly = "0";
mcol.isRelated = "0";
mcol.isUpdate = "1";
mcol.isVirtual = "0";
mcol.VirtualExpress = "";
mcol.RelationID = "";
mcol.ParentID = "";
mcol.Ord = i.ToString();
mcol.Length = col.Length.ToString();
mcol.DataType = col.DataType;
mcol.isPrimary = col.IsPrimary;
base.Db.Save <FBDataModelCols>(mcol);//保存列表
i++;
}
this.Db.CompleteTransaction();
}
catch (Exception ex)
{
base.Db.AbortTransaction();
throw ex;
}
}
public void AddModel(FBDataModel model)
{
try
{
base.Db.BeginTransaction();
//保存元数据
FBMetaData meta = new FBMetaData();
meta.ID = model.ID;
meta.Type = "2";
meta.State = "1";
meta.Name = model.Name;
meta.Code = model.Code;
base.Db.Save <FBMetaData>(meta);
//保存数据模型
base.Save(model);
//获取数据对象信息
FBDataObject obj = new FBDataObject();
FBDataObjectService svr = new FBDataObjectService(this.context);
List <FBDataObjectCols> list = new List <FBDataObjectCols>();
list = svr.GetColumn(model.MainObectID);
obj = svr.GetModel(model.MainObectID);
//保存主对象
FBDataModelObjects modelmain = new FBDataModelObjects();
modelmain.ID = Guid.NewGuid().ToString();
modelmain.ModelID = model.ID;
modelmain.Code = obj.Code;
modelmain.Name = obj.AiasName;
modelmain.ObjectID = model.MainObectID;
var listres = list.Where(p => p.IsPrimary == "1").ToList();
if (listres.Count <= 0)
{
throw new Exception("数据对象必须定义一个主键！");
}
modelmain.PKCOLName = list.Where(p => p.IsPrimary == "1").ToList()[0].Code;
modelmain.Level = "1";
modelmain.isMain = "1";
modelmain.Label = obj.Code;
modelmain.ParentID = "";
base.Db.Save <FBDataModelObjects>(modelmain);//保存列信息
//保存子对象
foreach (FBDataObjectCols col in list)
{
FBDataModelCols mcol = new FBDataModelCols();
mcol.ID = Guid.NewGuid().ToString();
mcol.Code = col.Code;
mcol.Name = col.Name;
mcol.ModelID = model.ID;
mcol.ModelObjectID = modelmain.ID;
mcol.Label = col.Code;
mcol.isCard = "1";
mcol.isList = "1";
mcol.isReadOnly = "0";
mcol.isRelated = "0";
mcol.isUpdate = "1";
mcol.isVirtual = "0";
mcol.VirtualExpress = "";
mcol.RelationID = "";
mcol.ParentID = "";
mcol.Length = col.Length.ToString();
mcol.DataType = col.DataType;
mcol.isPrimary = col.IsPrimary;
mcol.Ord = col.Ord;
Db.Save <FBDataModelCols>(mcol);
}
FBMeta.AddMeataData(model.ID, model.Code, model.Name, "1", model.parentID, base.Db);
this.Db.CompleteTransaction();
}
catch (Exception ex)
{
base.Db.AbortTransaction();
throw ex;
}
}
/// <summary>
/// 创建查询语句
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static StringBuilder BuildSelectSql(FBDataModelObjects obj, List <FBDataModelRealtions> relationList, bool isTree = false, bool isCard = false, bool isList = false)
{
//List<FBDataModelRealtions> relationList = new List<FBDataModelRealtions>();//当前关联信息集合
//relationList=DataModelCom.getModelRelations(obj.ModelID,obj.ID,db)
StringBuilder sb = new StringBuilder();
//拼接字段
string aliasTableName = obj.Label;
string tableName = obj.Code;//表名
sb.Append(" SELECT ");
var i = 1;
foreach (FBDataModelCols col in obj.ColList)
{
if ((isCard && col.isCard == "0") || (isList && col.isList == "0"))
{
//卡片模式但是 是否卡片没有勾选 或者 列表模式但是是否列表没有勾选
break;
}
//这里要处理mysql关键字 shit
col.Label = _stratergy.ReplaceToken(col.Label);
if (col.isRelated == "1")
{
//关联字段的情况下
sb.AppendFormat(" {0}.{1} AS {2}", relationList.Find(p => p.ID == col.RelationID).ObjectLabel, col.Code, col.Label);
}
else if (col.isVirtual == "1")
{
sb.AppendFormat("({0}) as {1}", col.VirtualExpress, col.Label);
}
else
{
sb.AppendFormat(" {0}.{1} AS {2}", aliasTableName, col.Code, col.Label);
}
if (i != obj.ColList.Count)
{
sb.Append(",");
}
i++;
}
if (isTree)
{
sb.Append(" , '0' as _istarget ");
}
sb.AppendFormat(" from {0} {1} ", tableName, aliasTableName);
foreach (FBDataModelRealtions model in relationList.Where(p => p.ModelObjectID == obj.ID))
{
sb.AppendFormat("{0} JOIN {1} {2} ON {2}.{3}={4}.{5}",
getJoinType(model.JoinType),
model.ObjectCode,
model.ObjectLabel,
model.Filter,
aliasTableName,
model.ModelObjectColCode);
}
sb.Append(" where 1=1 ");
// 这里应该加上模型本身的过滤条件\权限条件 todo
return(sb);
}
