private static string AppendListSql(Pagination page, AdminCredential admin, DBDatabase db, string sql)
{
Sys_Entity entity = db.FirstOrDefault <Sys_Entity>(new Sql("SELECT * FROM Sys_Entity WHERE ID=" + page.eid));
if (sql.IndexOf(entity.Name + ".ID") == -1)
{
sql = sql.Replace("SELECT", "SELECT " + entity.Name + ".ID AS " + entity.Name + "$ID,");
}
if (sql.IndexOf(entity.Name + ".Name") == -1)
{
sql = sql.Replace("SELECT", "SELECT " + entity.Name + ".Name AS " + entity.Name + "$Name,");
}
if (sql.IndexOf(entity.Name + ".OwnerID") == -1)
{
sql = sql.Replace("SELECT", "SELECT " + entity.Name + ".OwnerID AS " + entity.Name + "$OwnerID,");
}
//Where条件
if (!string.IsNullOrEmpty(page.WhereSql))
{
if (sql.IndexOf("WHERE") != -1)
{
sql += " AND ";
}
else
{
sql += " WHERE ";
}
sql += page.WhereSql;
}
if (page.IsSearch)
{
page.Page = 1;
}
//关键字
sql = AppendSqlForKeyWord(page.KeyWord, page.eid.Value, db, sql);
//权限过滤
if (entity.IsEnableDataAuthorize)
{
sql = AppendSqlForAuth(page, admin, sql, entity.Name);
}
//Tab
if (page.pid.HasValue)
{
sql = AppendSqlForTab(page, db, sql, entity.Name);
}
//统计数据
if (entity.IsEnableSumData)
{
CacheHelper.Single.TrySave($"{page.vid}-{admin.ID}-sumdata-sql", GetSumDataSql(entity.ID, db, sql));
}
//排序
var orderSql = GetOrderSql(page, db, entity.Name);
sql += orderSql;
//表单分页
CacheHelper.Single.TrySave($"{page.vid}-{admin.ID}-formpage-sql", GetFormPageSql(entity.Name, orderSql));
CacheHelper.Single.TrySave($"{page.vid}-{admin.ID}-list-sql", sql);
return(sql);
}
/// <summary>
/// 保存视图
/// </summary>
/// <param name="entity"></param>
/// <param name="view_request"></param>
/// <returns></returns>
public ItemResult <int> SaveView(ViewSaveEntity entity, Sys_View view_request, List <ViewFieldModel> field_list)
{
int ViewID = 0;
ItemResult <int> item = new ItemResult <int>();
Sql _sql = new Sql();
StringBuilder strb = new StringBuilder();
var db = CreateDao();
bool isKeepConnectionAlive = db.KeepConnectionAlive;
try
{
// 检查此前是否已经保持连接存活状态，如果没有，则进行设置
if (!isKeepConnectionAlive)
{
// 保持连接存活状态
db.KeepConnectionAlive = true;
}
// 开始事务
db.BeginTransaction();
//要显示的列
var _fieldsql = new Sql();
_fieldsql.Select("RelationEntity,EntityName,Name,FieldSql,JoinTableName,OnSql,Field,Title,Template,HeaderAttributes,Attributes,HeaderTemplate,Width,Columns_Type,Columns_Format")
.From("Sys_Field").Where("EntityID=" + entity.EntityID + " AND IsColumnShow=1");
var sql = new Sql();
StringBuilder strb_sql = new StringBuilder();
#region 构建视图
List <string> field = new List <string>();
List <JoinEntity> joinEntity = new List <JoinEntity>();
foreach (var itemq in field_list)
{
if (!string.IsNullOrEmpty(itemq.FieldSql))
{
field.Add(itemq.FieldSql);
}
//列表外键弹框模版对应的Sql语句
if (itemq.FieldType == EnumFieldType.关联其他表.ToString() && !string.IsNullOrEmpty(itemq.TemplateSql))
{
field.Add(itemq.TemplateSql);
}
//列表选项集显示值对应的Value值
if (itemq.FieldType == EnumFieldType.两个选项.ToString() || itemq.FieldType == EnumFieldType.项集.ToString())
{
field.Add(itemq.FieldSql.Replace(".Name", ".Value").Replace("$Name", "$Value"));
}
//列表外键显示值对应的ID值
if (itemq.FieldType == EnumFieldType.关联其他表.ToString())
{
field.Add(itemq.FieldSql.Replace(".Name", ".ID").Replace("$Name", "$ID"));
}
//外键表
if (joinEntity.Where(e => e.JoinTableName == itemq.JoinTableName).Count() == 0 && !string.IsNullOrEmpty(itemq.JoinTableName))
{
joinEntity.Add(new JoinEntity()
{
JoinTableName = itemq.JoinTableName, OnSql = itemq.OnSql
});
}
//格式处理
if (itemq.FieldType == "时间")
{
itemq.Filterable = new Filterable()
{
ui = "datetimepicker"
};
itemq.Format = "{0:yyyy-MM-dd HH:mm}";
itemq.Columns_Type = "date";
}
}
foreach (var itemq in entity.Relationentity)
{
if (joinEntity.Where(e => e.JoinTableName == itemq.JoinTableName).Count() == 0 && !string.IsNullOrEmpty(itemq.JoinTableName))
{
joinEntity.Add(new JoinEntity()
{
JoinTableName = itemq.JoinTableName, OnSql = itemq.OnSql
});
}
}
strb_sql.AppendFormat("SELECT {0} FROM {1}", string.Join(",", field), entity.EntityName);
foreach (var e in joinEntity)
{
strb_sql.AppendFormat(" LEFT JOIN {0} ON {1}", e.JoinTableName, e.OnSql);
}
if (!string.IsNullOrEmpty(entity.FilterSql))
{
strb_sql.AppendFormat(" WHERE {0}", entity.FilterSql);
}
string viewname = string.Format("View_{0}_{1}_{2}", entity.EntityName, view_request.OwnerID, DateTime.Now.ToString("yyyyMMddhhmmssffff"));
#endregion
#region 编辑视图表
if (view_request.ID == 0)
{
Sys_View view = new Sys_View()
{
CreateTime = DateTime.Now,
UpdateTime = DateTime.Now,
EntityID = entity.EntityID,
Name = viewname,
OwnerID = view_request.OwnerID,
FieldList = JsonConvert.SerializeObject(field_list),//EntityFieldsToViewFields(field_list)
Sql = strb_sql.ToString(),
Title = view_request.Title,
Remark = view_request.Remark,
Express = entity.Express,
IsMenu = view_request.IsMenu,
Sort = view_request.Sort.Replace("$", "."),
Type = string.IsNullOrEmpty(view_request.Type) ? "自定义视图" : view_request.Type
};
ViewID = Convert.ToInt32(db.Insert(view));
}
else
{
var view_list = db.Query <Sys_View>(new Sql(string.Format("SELECT * FROM Sys_View WHERE ID={0}", view_request.ID))).ToList();
Sys_View view = view_list.FirstOrDefault();
view.Sql = strb_sql.ToString();
view.UpdateTime = DateTime.Now;
view.Title = view_request.Title;
view.Remark = view_request.Remark;
view.IsMenu = view_request.IsMenu;
view.Express = entity.Express;
view.Sort = view_request.Sort.Replace("$", ".");
view.FieldList = JsonConvert.SerializeObject(field_list);
if (!string.IsNullOrEmpty(view_request.Type))
{
view.Type = view_request.Type;
}
db.Update(view);
ViewID = view_request.ID;
}
#endregion
db.Execute("DELETE FROM Sys_menu WHERE ViewID=@0", ViewID);
if (view_request.IsMenu.HasValue && view_request.IsMenu.Value)
{
Sys_Entity ventity = db.FirstOrDefault <Sys_Entity>("SELECT top 1 AreaName,ControllerName FROM dbo.Sys_entity WHERE ID=@0", entity.EntityID);
var menuid = base.Insert <Sys_Menu>(new Sys_Menu()
{
Icon = "icon-glass",
MenuName = view_request.Title,
ViewID = ViewID,
AccessOperation = "0,1,2,3,4,5,6,8,9,",
ParentID = 11029,
MenuUrl = string.Format("/{0}/{1}/List?v={2}", ventity.AreaName, ventity.ControllerName, ViewID),
});
}
// 完成事务
db.CompleteTransaction();
item.Success = true;
item.Data = ViewID;
}
catch (Exception)
{
item.Success = false;
// 中断事务
db.AbortTransaction();
}
finally
{
// 检查此前是否已经保持连接存活状态，如果没有，则关闭数据库连接
if (!isKeepConnectionAlive)
{
// 关闭连接存活状态
db.KeepConnectionAlive = false;
// 关闭数据库连接
db.CloseSharedConnection();
}
}
return(item);
}
