public static SqlWhereCollection SetCanReadWhere(
SiteSettings ss,
SqlWhereCollection where,
bool checkPermission = true)
{
if (ss.ColumnHash.ContainsKey("SiteId"))
{
if (ss.AllowedIntegratedSites != null)
{
where.Or(new SqlWhereCollection()
.Add(
tableName: ss.ReferenceType,
raw: "#TableBracket#.[SiteId] in ({0})".Params(
ss.AllowedIntegratedSites.Join()))
.CheckRecordPermission(ss, ss.IntegratedSites));
}
else
{
where.Add(
tableName: ss.ReferenceType,
raw: "#TableBracket#.[SiteId]={0}".Params(ss.SiteId));
if (!ss.CanRead(site: true) && checkPermission)
{
where.CheckRecordPermission(ss);
}
}
}
return(where);
}
/// <summary>
/// Fixed:
/// </summary>
private static SqlWhereCollection ItemWhereLike(
this SqlWhereCollection where,
SiteSettings ss,
string columnName,
string searchText,
string name,
bool forward,
bool itemJoin)
{
var tableName = ItemTableName(
ss: ss,
itemJoin: itemJoin);
return(where.Add(Rds.ItemsWhere().SqlWhereLike(
tableName: "Items",
name: name,
searchText: searchText,
clauseCollection: itemJoin
? ItemWhereLike(
tableName: tableName,
columnName: columnName,
name: name,
forward: forward)
.ToSingleList()
: SelectItemWhereLike(
ss: ss,
tableName: tableName,
columnName: columnName,
name: name,
forward: forward)
.ToSingleList())));
}
private void CsStringColumns(Column column, string value, SqlWhereCollection where)
{
if (column.HasChoices())
{
var param = value.Deserialize <List <string> >();
if (param?.Any() == true)
{
where.Add(or: new SqlWhereCollection(
CsStringColumnsWhere(column, param),
CsStringColumnsWhereNull(column, param)));
}
}
else
{
if (!value.IsNullOrEmpty())
{
var name = Strings.NewGuid();
where.SqlWhereLike(
name: name,
searchText: value,
clauseCollection: "([{0}].[{1}] like '%' + @{2}#ParamCount#_#CommandCount# + '%')"
.Params(column.TableName(), column.Name, name)
.ToSingleList());
}
}
}
private static SqlWhereCollection CheckRecordPermission(
this SqlWhereCollection where, SiteSettings ss, List <long> siteIdList = null)
{
return(where.Add(
tableName: ss.ReferenceType,
subLeft: CheckRecordPermission(ss.IdColumnBracket(), siteIdList),
_operator: null));
}
private static SqlWhereCollection PermissionsWhere(this SqlWhereCollection where)
{
return(where.Add(or: Rds.PermissionsWhere()
.GroupId_In(sub: Rds.SelectGroupMembers(
column: Rds.GroupMembersColumn().GroupId(),
where : Rds.GroupMembersWhere()
.Add(raw: DeptOrUser("GroupMembers"))))
.Add(raw: DeptOrUser("Permissions"))));
}
private void CsNumericColumns(
Column column, List <string> param, SqlWhereCollection where)
{
if (param.Any())
{
where.Add(or: new SqlWhereCollection(
CsNumericColumnsWhere(column, param),
CsNumericColumnsWhereNull(column, param)));
}
}
private void CsDateTimeColumns(
Column column, string value, SqlWhereCollection where)
{
var param = value.Deserialize <List <string> >();
if (param.Any())
{
where.Add(or: new SqlWhereCollection(
CsDateTimeColumnsWhere(column, param),
CsDateTimeColumnsWhereNull(column, param)));
}
}
public static SqlWhereCollection SetCanReadWhere(
Context context,
SiteSettings ss,
SqlWhereCollection where,
bool checkPermission = true)
{
if (ss.IsSite(context: context) && ss.ReferenceType == "Sites")
{
where.Add(
tableName: "Sites",
raw: $"[Sites].[ParentId] in ({ss.SiteId})");
}
else
{
if (ss.ColumnHash.ContainsKey("SiteId"))
{
if (ss.AllowedIntegratedSites != null)
{
where.Add(or: new SqlWhereCollection()
.Add(
tableName: ss.ReferenceType,
raw: "[{0}].[SiteId] in ({1})".Params(
ss.ReferenceType, ss.AllowedIntegratedSites.Join()))
.CheckRecordPermission(ss, ss.IntegratedSites));
}
else
{
where.Add(
tableName: ss.ReferenceType,
raw: "[{0}].[SiteId] in ({1})".Params(
ss.ReferenceType, ss.SiteId));
if (!context.CanRead(ss: ss, site: true) && checkPermission)
{
where.CheckRecordPermission(ss);
}
}
}
}
return(where);
}
/// <summary>
/// Fixed:
/// </summary>
private static SqlWhereCollection FullTextWhere(
this SqlWhereCollection where,
Dictionary <string, string> words,
string itemsTableName = "Items")
{
words.ForEach(data => where.Add(
name: data.Key,
value: data.Value,
raw: FullTextWhere(
name: data.Key,
itemsTableName: itemsTableName)));
return(where);
}
private void CsNumericRangeColumns(
Column column,
List <string> param,
SqlWhereCollection where)
{
var parts = new SqlWhereCollection();
param.ForEach(data =>
{
var from = data.Split_1st();
var to = data.Split_2nd();
if (from == string.Empty)
{
parts.Add(new SqlWhere(
tableName: column.TableName(),
columnBrackets: ("[" + column.Name + "]").ToSingleArray(),
_operator: "<{0}".Params(to.ToDecimal())));
}
else if (to == string.Empty)
{
parts.Add(new SqlWhere(
tableName: column.TableName(),
columnBrackets: ("[" + column.Name + "]").ToSingleArray(),
_operator: ">={0}".Params(from.ToDecimal())));
}
else
{
parts.Add(new SqlWhere(
tableName: column.TableName(),
columnBrackets: ("[" + column.Name + "]").ToSingleArray(),
_operator: " between {0} and {1}".Params(
from.ToDecimal(), to.ToDecimal())));
}
});
where.Add(or: parts);
}
/// <summary>
/// Fixed:
/// </summary>
private static SqlWhereCollection FullTextWhere(
this SqlWhereCollection where,
Dictionary <string, string> words,
string idColumnBracket,
Sqls.TableTypes tableType)
{
words.ForEach(data => where.Add(
name: data.Key,
value: data.Value,
raw: FullTextWhere(
name: data.Key,
idColumnBracket: idColumnBracket,
tableType: tableType)));
return(where);
}
public static SqlWhereCollection CanRead(SiteSettings ss, SqlWhereCollection where)
{
return(!ss.CanRead(site: true)
? where.Add(
subLeft: Rds.SelectPermissions(
column: Rds.PermissionsColumn().PermissionsCount(),
where : Rds.PermissionsWhere()
.ReferenceId(raw: ss.IdColumnBracket())
.PermissionType(_operator: " & 1 = 1")
.Or(Rds.PermissionsWhere()
.GroupId_In(sub: Rds.SelectGroupMembers(
column: Rds.GroupMembersColumn().GroupId(),
where : Rds.GroupMembersWhere()
.Add(raw: DeptOrUser("GroupMembers"))))
.Add(raw: DeptOrUser("Permissions")))),
_operator: ">0")
: where);
}
/// <summary>
/// 适用场境,在测试库时,更新完数据后,验证数据是否有异常
///
/// 常用方法:
///var update = new SqlUpdateCollection(new
///{
/// id = id,
/// agentno = agentno
///})
///.PrimaryKeyFields("id");
///PFSqlUpdateValidateHelper.TestUpdate("t_hyzl_orders", update, sqlExec);
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="update"></param>
/// <param name="sql"></param>
public static void TestUpdate(string tableName, SqlUpdateCollection update, ProcManager sql)
{
string updateSqlString = string.Format(@" select * from {0} {1}
", tableName, update.ToWhereSql());
string totalSqlString = string.Format(@" select count(*) from {0}
", tableName);
//用set条件的字段做where来查总数,如果行数等于全表行数,那说明把整个表的值都更新了(where没有生效)
var updateSet = new SqlWhereCollection();
foreach (var i in update)
{
updateSet.Add(i.Key, i.Value.Value);
}
string updateSetTotalSqlString = string.Format(@" select count(*) from {0} {1}
", tableName, updateSet.ToSql());
var updated = sql.GetQueryTable(updateSqlString);
var total = PFDataHelper.ObjectToInt(sql.QuerySingleValue(totalSqlString));
var setTotal = PFDataHelper.ObjectToInt(sql.QuerySingleValue(updateSetTotalSqlString));
if (updated == null)
{
throw new Exception("更新后的数据全部丢失.异常");
}
if (total < 2)
{
throw new Exception("测试数据少于2条,这样不保险");
}
if (total == updated.Rows.Count)
{
throw new Exception("更新了整个表的数据,请确认是否缺少where条件.异常");
}
if (total == setTotal)
{
throw new Exception("更新了整个表的数据,请确认是否缺少where条件.异常");
}
AssertIsTrue(updated != null && updated.Rows.Count == 1);
AssertIsTrue(total > 1);
AssertIsTrue(IsDataRowMatchUpdate(updated.Rows[0], update));
AssertIsTrue(setTotal >= updated.Rows.Count && setTotal < total);
}
private void SetGeneralsWhere(Context context, SiteSettings ss, SqlWhereCollection where)
{
if (Incomplete == true && HasIncompleteColumns(
context: context,
ss: ss))
{
where.Add(
tableName: ss.ReferenceType,
columnBrackets: "[Status]".ToSingleArray(),
_operator: "<" + Parameters.General.CompletionCode);
}
if (Own == true && HasOwnColumns(context, ss))
{
where.Add(
tableName: ss.ReferenceType,
columnBrackets: new string[] { "[Manager]", "[Owner]" },
name: "_U",
value: context.UserId);
}
if (NearCompletionTime == true && HasNearCompletionTimeColumns(
context: context,
ss: ss))
{
where.Add(
tableName: ss.ReferenceType,
columnBrackets: "[CompletionTime]".ToSingleArray(),
_operator: " between '{0}' and '{1}'".Params(
DateTime.Now.ToLocal(context: context).Date
.AddDays(ss.NearCompletionTimeBeforeDays.ToInt() * (-1)),
DateTime.Now.ToLocal(context: context).Date
.AddDays(ss.NearCompletionTimeAfterDays.ToInt() + 1)
.AddMilliseconds(Parameters.Rds.MinimumTime * -1)
.ToString("yyyy/M/d H:m:s.fff")));
}
if (Delay == true && HasDelayColumns(
context: context,
ss: ss))
{
where
.Add(
tableName: ss.ReferenceType,
columnBrackets: "[Status]".ToSingleArray(),
name: "_U",
_operator: "<{0}".Params(Parameters.General.CompletionCode))
.Add(
tableName: ss.ReferenceType,
columnBrackets: "[ProgressRate]".ToSingleArray(),
_operator: "<",
raw: Def.Sql.ProgressRateDelay
.Replace("#TableName#", ss.ReferenceType));
}
if (Overdue == true && HasOverdueColumns(
context: context,
ss: ss))
{
where
.Add(
tableName: ss.ReferenceType,
columnBrackets: "[Status]".ToSingleArray(),
name: "_U",
_operator: "<{0}".Params(Parameters.General.CompletionCode))
.Add(
tableName: ss.ReferenceType,
columnBrackets: "[CompletionTime]".ToSingleArray(),
_operator: "<getdate()");
}
}
/// <summary>
/// Fixed:
/// </summary>
public static SqlWhereCollection FullTextWhere(
this SqlWhereCollection where,
SiteSettings ss,
string tableName,
string searchText)
{
if (ss != null && ss.TableType != Sqls.TableTypes.Normal)
{
return(where.Add(Rds.ItemsWhere().SqlWhereLike(
tableName: tableName + "_Items",
name: "SearchText",
searchText: searchText,
clauseCollection: Rds.Items_FullText_WhereLike(
tableName: tableName + "_Items",
forward: false)
.ToSingleList())));
}
switch (ss?.SearchType)
{
case SiteSettings.SearchTypes.PartialMatch:
return(where.Add(Rds.ItemsWhere().SqlWhereLike(
tableName: tableName + "_Items",
name: "SearchText",
searchText: searchText,
clauseCollection: Rds.Items_FullText_WhereLike(
tableName: tableName + "_Items",
forward: false)
.ToSingleList())));
case SiteSettings.SearchTypes.MatchInFrontOfTitle:
return(where.Add(Rds.ItemsWhere().SqlWhereLike(
tableName: tableName + "_Items",
name: "SearchText",
searchText: searchText,
clauseCollection: Rds.Items_Title_WhereLike(
tableName: tableName + "_Items",
forward: true)
.ToSingleList())));
case SiteSettings.SearchTypes.BroadMatchOfTitle:
return(where.Add(Rds.ItemsWhere().SqlWhereLike(
tableName: tableName + "_Items",
name: "SearchText",
searchText: searchText,
clauseCollection: Rds.Items_Title_WhereLike(
tableName: tableName + "_Items",
forward: false)
.ToSingleList())));
default:
switch (Parameters.Search.Provider)
{
case "FullText":
var words = Words(searchText);
if (words?.Any() != true)
{
return(where);
}
where.Add(new SqlWhere(
name: "SearchText",
value: words,
raw: FullTextWhere(
words: words,
itemsTableName: tableName + "_Items")));
return(where);
default:
return(where.Add(Rds.ItemsWhere().SqlWhereLike(
tableName: tableName + "_Items",
name: "SearchText",
searchText: searchText,
clauseCollection: Rds.Items_FullText_WhereLike(
tableName: tableName + "_Items",
forward: false)
.ToSingleList())));
}
}
}
/// <summary>
/// Fixed:
/// </summary>
public static SqlWhereCollection FullTextWhere(
this SqlWhereCollection where,
SiteSettings ss,
string searchText,
bool itemJoin)
{
var name = Strings.NewGuid();
if (ss != null && ss.TableType != Sqls.TableTypes.Normal)
{
return(where.ItemWhereLike(
ss: ss,
columnName: "FullText",
searchText: searchText,
name: name,
forward: false,
itemJoin: itemJoin));
}
switch (ss?.SearchType)
{
case SiteSettings.SearchTypes.FullText:
var words = Words(searchText);
if (words?.Any() != true)
{
return(where);
}
where.Add(new SqlWhere(
name: name,
value: words,
raw: itemJoin
? FullTextWhere(
words: words,
itemsTableName: ss.ReferenceType + "_Items",
name: name)
: FullTextWhere(
words: words,
idColumnBracket: ss.IdColumnBracket(),
tableType: ss.TableType,
name: name)));
return(where);
case SiteSettings.SearchTypes.MatchInFrontOfTitle:
return(where.ItemWhereLike(
ss: ss,
columnName: "Title",
searchText: searchText,
name: name,
forward: true,
itemJoin: itemJoin));
case SiteSettings.SearchTypes.BroadMatchOfTitle:
return(where.ItemWhereLike(
ss: ss,
columnName: "Title",
searchText: searchText,
name: name,
forward: false,
itemJoin: itemJoin));
case SiteSettings.SearchTypes.PartialMatch:
default:
return(where.ItemWhereLike(
ss: ss,
columnName: "FullText",
searchText: searchText,
name: name,
forward: false,
itemJoin: itemJoin));
}
}
public static SqlWhereCollection SetCanReadWhere(
Context context,
SiteSettings ss,
SqlWhereCollection where,
bool checkPermission = true)
{
if (ss.IsSite(context: context) && ss.ReferenceType == "Sites")
{
where.Add(
tableName: "Sites",
raw: $"\"Sites\".\"ParentId\"={ss.SiteId}");
}
else
{
if (ss.ColumnHash.ContainsKey("SiteId"))
{
if (ss.AllowedIntegratedSites == null)
{
where.Add(
tableName: ss.ReferenceType,
raw: $"\"{ss.ReferenceType}\".\"SiteId\"={ss.SiteId}");
if (!context.CanRead(ss: ss, site: true) && checkPermission)
{
where.CheckRecordPermission(ss);
}
}
else
{
var denySites = ss.IntegratedSites
.Where(siteId => !ss.AllowedIntegratedSites.Contains(siteId))
.ToList();
denySites = denySites.Any()
? Repository.ExecuteTable(
context: context,
statements: Rds.SelectSites(
column: Rds.SitesColumn().SiteId(),
where : Rds.SitesWhere()
.TenantId(context.TenantId)
.SiteId_In(denySites)))
.AsEnumerable()
.Select(dataRow => dataRow.Long("SiteId"))
.ToList()
: new List <long>();
if (!denySites.Any())
{
where.Add(
tableName: ss.ReferenceType,
raw: $"\"{ss.ReferenceType}\".\"SiteId\" in ({ss.AllowedIntegratedSites.Join()})");
}
else
{
where.Add(or: new SqlWhereCollection()
.Add(
tableName: ss.ReferenceType,
raw: $"\"{ss.ReferenceType}\".\"SiteId\" in ({ss.AllowedIntegratedSites.Join()})")
.Add(and: new SqlWhereCollection()
.Add(
tableName: ss.ReferenceType,
raw: $"\"{ss.ReferenceType}\".\"SiteId\" in ({denySites.Join()})")
.CheckRecordPermission(ss, ss.IntegratedSites)));
}
}
}
}
return(where);
}
private static SqlWhereCollection PermissionsWhere(
this SqlWhereCollection where,
Context context)
{
return(where.Add(raw: context.Sqls.PermissionsWhere));
}