/// <summary>
/// 此处去掉主键,即插入操作不能操作id
/// </summary>
/// <param name="tuple"></param>
/// <returns></returns>
private String GetInsertSql(Tuple <List <String>, List <String> > tuple)
{
String pk = EntityTableMapper.GetPkColumn(typeof(T));
StringBuilder colstr = new StringBuilder();
StringBuilder valstr = new StringBuilder();
List <String> cols = tuple.Item1;
List <String> props = tuple.Item2;
for (Int32 i = 0; i < cols.Count; i++)
{
//去掉主键
if (cols[i] == pk)
{
continue;
}
if (colstr.Length != 0)
{
colstr.Append(",");
valstr.Append(",");
}
colstr.Append(cols[i]);
valstr.Append($"@{props[i]}");
}
return($"({colstr.ToString()}) values ({valstr.ToString()})");
}
protected override List <TableRowActionViewModel> BuildTableViewActions()
{
var actions = base.BuildTableViewActions();
string searchQuery = GetHttpContextSearchQuery();
int pageNumber = GetHttpContextPageNumber();
string returnUrl = UrlEncoder.Encode($"/admin/foods?p={pageNumber}&q={searchQuery}");
string returnTitle = UrlEncoder.Encode("Foods");
actions.Insert(1, EntityTableMapper.CreateAction(
"Upload Images",
MaterialDesignIcons.ImagePlus,
TableRowActionMethod.Get,
$"/admin/roots/public/upload-files/uploads/images/foods/{{0}}?returnUrl={returnUrl}&returnTitle={returnTitle}",
"[Id]"));
actions.Insert(2, EntityTableMapper.CreateAction(
"Select From Gallery",
MaterialDesignIcons.ImageMove,
TableRowActionMethod.Get,
$"{this.ControllerRoute}{{0}}/gallery",
"[Id]"));
return(actions);
}
public virtual async Task <IActionResult> GetAll(
[FromQuery] int page = 1,
[FromQuery] string searchQuery = null,
[FromQuery] string orderBy = null,
[FromQuery] string orderType = null)
{
PaginatedList <TEntityViewModel> entitiesResult = await this.GetAllEntitiesPaginatedAsync(
page,
searchQuery,
this.InterceptOrderProperty(orderBy),
orderType);
TableViewViewModel model = new TableViewViewModel();
model.SingleEntityName = StringFunctions.SplitWordsByCapitalLetters(typeof(TEntity).Name);
model.Title = model.SingleEntityName.ToPluralString();
this.ViewData[BreadcrumbPageTitlePlaceholder] = model.Title;
model.Table = EntityTableMapper.Map(entitiesResult, this.BuildTableViewActions()?.ToArray());
model.Table.SetPaginationRedirection(this.AreaName, this.ControllerName, this.ActionName);
this.ViewData.Add("SearchQuery", searchQuery);
this.ViewData.Add("OrderProperties", this.GetOrderProperties());
this.ViewData.Add("OrderBy", orderBy);
this.ViewData.Add("OrderType", orderType);
this.InitializeNavigationActions(this.BuildTableViewNavigationActions());
return(this.GetAllView(model));
}
private String GetUpdateSql(Tuple <List <String>, List <String> > tuple)
{
String pk = EntityTableMapper.GetPkColumn(typeof(T));
StringBuilder colstr = new StringBuilder();
List <String> cols = tuple.Item1;
List <String> props = tuple.Item2;
for (Int32 i = 0; i < cols.Count; i++)
{
//去掉主键
if (cols[i] == pk)
{
continue;
}
if (colstr.Length != 0)
{
colstr.Append(",");
}
// synctimes = synctimes+1 的形式便直接拼接
//注意,此处在解析header时候,便是1:1的关系,如果是set中便是为空
if (String.IsNullOrEmpty(props[i]))
{
colstr.Append($" {cols[i]} ");
}
else
{
colstr.Append($"{cols[i]} = @{props[i]}");
}
}
return(colstr.ToString());
}
/// <summary>
/// 此处去掉主键,即插入操作不能操作id
/// </summary>
/// <param name="tuple"></param>
/// <returns></returns>
private String GetInsertSql(Tuple <List <String>, List <String> > tuple)
{
HashSet <String> isincrement = EntityTableMapper.Increment(typeof(T));
StringBuilder colstr = new StringBuilder();
StringBuilder valstr = new StringBuilder();
List <String> cols = tuple.Item1;
List <String> props = tuple.Item2;
for (Int32 i = 0; i < cols.Count; i++)
{
//去掉自增键
if (isincrement.Contains(cols[i]))
{
continue;
}
if (colstr.Length != 0)
{
colstr.Append(",");
valstr.Append(",");
}
colstr.Append(cols[i]);
valstr.Append($"@{props[i]}");
}
return($"({colstr.ToString()}) values ({valstr.ToString()})");
}
private String ConditionConvert(ColumnRelevanceMapper column, bool isNull = false)
{
StringBuilder sb = new StringBuilder();
//如果 null == item.id 的写法, 会解析成
//column.ColumnName == null && column.SqlOperatorEnum == SqlOperatorEnum.None
// null,那么就直接返回"", 接下来的 == item.id 会传递false,然后解析 item.id is null
if (column.ColumnName == null && column.SqlOperatorEnum == SqlOperatorEnum.None)
{
return("");
}
//如果是 item.id == null 中写法 那么 == null 则返回 is null
if (isNull || column.ColumnName == null)
{
column.SqlOperatorEnum = SqlEnumConvert(column.SqlOperatorEnum);
}
if (column.SqlOperatorEnum == SqlOperatorEnum.EqualNotNull ||
column.SqlOperatorEnum == SqlOperatorEnum.EqualNull)
{
String str = "";
if (!String.IsNullOrEmpty(column.ColumnName))
{
Type t = column.TableName;
String tableName = EntityTableMapper.GetTableName(t);
String columnName = EntityTableMapper.GetColoumName(t, column.ColumnName);
str = $"{tableName}.{columnName}";
}
return($"{str} {column.SqlOperatorEnum.GetOperator()}");
}
if (column.SqlOperatorEnum != SqlOperatorEnum.None)
{
sb.Append(column.SqlOperatorEnum.GetOperator());
}
//如果是 直接传递字符串 如 id in (select id from xxx)中,则 括号中则直接拼接,而不用@数字
//如果是 item.id == 1 中的 == 1,那么只 保存 = @ColumnName
if (column.TableName == null)
{
if (column.ColumnName.IndexOf("(") != -1)
{
sb.Append($" {column.ColumnName} ");
}
else
{
sb.Append($"@{column.ColumnName}");
}
}
//如果是 item.id == 1 中的 item.id ,那么就保存 tablename.id
else
{
Type t = column.TableName;
String tableName = EntityTableMapper.GetTableName(t);
String columnName = EntityTableMapper.GetColoumName(t, column.ColumnName);
sb.Append($" [{tableName}].[{columnName}] ");
}
return(sb.ToString());
}
protected override List <TableRowActionViewModel> BuildTableViewActions()
{
var actions = base.BuildTableViewActions();
actions.Insert(1, EntityTableMapper.CreateAction(
"Foods",
MaterialDesignIcons.FoodApple,
TableRowActionMethod.Get,
$"{this.ControllerRoute}{{0}}/foods",
"[Id]"));
return(actions);
}
private String ConditionConvert(ColumnRelevanceMapper column, bool isNull = false)
{
StringBuilder sb = new StringBuilder();
//如果 null == item.id 中写法 null,那么返回"", == item.id 返回item.id is null
//如果是 item.id == null 中写法 那么 == null 则返回 is null
if (column.ColumnName == null && column.SqlOperatorEnum == SqlOperatorEnum.None)
{
return("");
}
if (isNull || column.ColumnName == null)
{
column.SqlOperatorEnum = SqlEnumConvert(column.SqlOperatorEnum);
}
if (column.SqlOperatorEnum == SqlOperatorEnum.EqualNotNull ||
column.SqlOperatorEnum == SqlOperatorEnum.EqualNull)
{
String str = "";
if (!String.IsNullOrEmpty(column.ColumnName))
{
Type t = column.TableName;
String tableName = EntityTableMapper.GetTableName(t);
String columnName = EntityTableMapper.GetColoumName(t, column.ColumnName);
str = $"{tableName}.{columnName}";
}
return($"{str} {column.SqlOperatorEnum.GetOperator()}");
}
if (column.SqlOperatorEnum != SqlOperatorEnum.None)
{
sb.Append(column.SqlOperatorEnum.GetOperator());
}
//如果是 item.id = 1 中的 = 1,那么只 保存 = @ColumnName
if (column.TableName == null)
{
sb.Append($"@{column.ColumnName}");
}
else
{
Type t = column.TableName;
String tableName = EntityTableMapper.GetTableName(t);
String columnName = EntityTableMapper.GetColoumName(t, column.ColumnName);
sb.Append($" {tableName}.{columnName} ");
}
return(sb.ToString());
}
public override IBatch End()
{
IBatch batch = new SqlServerBatch();
Tuple <List <String>, List <String> > tuple = ConvertHeader(Reduce.GetHeader());
Type t = typeof(T);
StringBuilder sb = new StringBuilder();
TableRelevanceMapper table = Reduce.GetTable();
List <ColumnRelevanceMapper> whereColumn = Reduce.GetWhere();
sb.Append(table.TableOperatorEnum.GetOperator());
sb.Append($" {EntityTableMapper.GetTableName(t)} ");
switch (table.TableOperatorEnum)
{
case TableOperatorEnum.Insert:
sb.Append(GetInsertSql(tuple));
break;
case TableOperatorEnum.Update:
sb.Append(" set ");
sb.Append(GetUpdateSql(tuple));
if (whereColumn.Count == 0)
{
SetNoneWhere(t);
}
break;
case TableOperatorEnum.Delete:
if (whereColumn.Count == 0)
{
SetNoneWhere(t);
}
break;
default:
throw new Exception("请先选择操作种类");
}
String where = ConvertToWhere(whereColumn);
if (!String.IsNullOrEmpty(where))
{
sb.Append($" where {where}");
}
batch.SqlBuilder = sb.ToString();
batch.DynamicParameters = Reduce.Parameters;
return(batch);
}
private void SetNoneWhere(Type t)
{
String pk = EntityTableMapper.GetPkColumn(t);
if (String.IsNullOrEmpty(pk))
{
throw new ArgumentException("请使用带主键的或者添加删选条件");
}
String pkValue = ValuePairs[pk].ColumnName;
ColumnRelevanceMapper column = new ColumnRelevanceMapper();
column.ColumnName = pk;
column.TableName = t;
ColumnRelevanceMapper value = new ColumnRelevanceMapper();
value.ColumnName = pk;
value.SqlOperatorEnum = SqlOperatorEnum.Equal;
Reduce.AddWhere(column);
Reduce.AddWhere(value);
}
private String ConvertToQuery(List <ColumnRelevanceMapper> query)
{
StringBuilder sb = new StringBuilder(256);
foreach (var column in query)
{
if (sb.Length != 0)
{
sb.Append(",");
}
if (column.TableName == null)
{
sb.Append($" {column.ColumnName} ");
continue;
}
String tablename = EntityTableMapper.GetTableName(column.TableName);
String columnname = EntityTableMapper.GetColoumName(column.TableName, column.ColumnName);
sb.Append($"{tablename}.{columnname}");
}
return(sb.ToString());
}
/// <summary>
/// Build the default table view actions.
/// </summary>
/// <returns></returns>
protected virtual List <TableRowActionViewModel> BuildTableViewActions()
{
var actions = new List <TableRowActionViewModel>();
if (this.HasDetails)
{
actions.Add(EntityTableMapper.DetailsAction($"{this.ControllerRoute}{{0}}", "[Id]"));
}
if (this.HasEdit)
{
actions.Add(EntityTableMapper.EditAction($"{this.ControllerRoute}{{0}}/edit", "[Id]"));
}
if (this.HasDelete)
{
actions.Add(EntityTableMapper.DeleteAction($"{this.ControllerRoute}{{0}}/delete", "[Id]"));
}
return(actions);
}
private String ConvertToTable(List <TableRelevanceMapper> tables)
{
StringBuilder sb = new StringBuilder();
foreach (var table in tables)
{
String tableName = EntityTableMapper.GetTableName(table.TableName);
if (table.TableOperatorEnum == TableOperatorEnum.None)
{
sb.Insert(0, tableName);
}
else
{
String tableOperator = table.TableOperatorEnum.GetOperator();
sb.Append($" {tableOperator} {tableName} on ");
foreach (var on in table.ColumnOperator)
{
sb.Append($" {ConditionConvert(on)} ");
}
}
}
return(sb.ToString());
}
