public void WhereShouldSetCriteria()
{
var query = new SimpleQuery(null, "foo");
var criteria = new SimpleExpression(1, 1, SimpleExpressionType.Equal);
query = query.Where(criteria);
Assert.AreSame(criteria, query.Clauses.OfType<WhereClause>().Single().Criteria);
}
private IEnumerable<IDictionary<string, object>> FindByExpression(string tableName, SimpleExpression criteria)
{
var builder = new CommandBuilder(GetSchema().FindTable, GetKeyNames);
var cmd = builder.BuildCommand(tableName, criteria);
return FindEntries(cmd.CommandText);
}
public override IEnumerable<IDictionary<string, object>> Find(string tableName, SimpleExpression criteria)
{
if (criteria == null) return FindAll(tableName);
return GetTableElement(tableName).Elements()
.Where(XmlPredicateBuilder.GetPredicate(criteria))
.Select(e => e.AttributesToDictionary());
}
public Func<object[],IDictionary<string,object>> CreateFindOneDelegate(string tableName, SimpleExpression criteria)
{
if (criteria == null)
{
return _ => FindAll(_adapter.GetSchema().BuildObjectName(tableName)).FirstOrDefault();
}
var commandBuilder = new FindHelper(_adapter.GetSchema())
.GetFindByCommand(_adapter.GetSchema().BuildObjectName(tableName), criteria);
var command = commandBuilder.GetCommand(_adapter.CreateConnection(), _adapter.AdoOptions);
command = _adapter.CommandOptimizer.OptimizeFindOne(command);
var commandTemplate =
commandBuilder.GetCommandTemplate(
_adapter.GetSchema().FindTable(_adapter.GetSchema().BuildObjectName(tableName)));
var cloneable = command as ICloneable;
if (cloneable != null)
{
return args => ExecuteSingletonQuery((IDbCommand)cloneable.Clone(), args, commandTemplate.Index);
}
else
{
return args => ExecuteSingletonQuery(commandTemplate, args);
}
}
public override int Update(string tableName, IDictionary<string, object> data, SimpleExpression criteria)
{
int updated = 0;
var elementsToUpdate = GetTableElement(tableName).Elements()
.Where(XmlPredicateBuilder.GetPredicate(criteria));
foreach (var element in elementsToUpdate)
{
foreach (var kvp in data)
{
var attribute = element.TryGetAttribute(kvp.Key);
if (attribute != null)
{
attribute.Value = kvp.Value.ToString();
}
else
{
element.SetAttributeValue(kvp.Key, kvp.Value);
}
}
updated++;
}
return updated;
}
public ICommandBuilder GetUpdateCommand(string tableName, IDictionary<string, object> data, SimpleExpression criteria)
{
var table = _schema.FindTable(tableName);
var updateClause = GetUpdateClause(table, data);
if (string.IsNullOrWhiteSpace(updateClause)) throw new InvalidOperationException("No columns to update.");
_commandBuilder.Append(updateClause);
if (criteria != null )
{
string whereStatement = null;
if (criteria.GetOperandsOfType<ObjectReference>().Any(o => IsTableChain(tableName, o)))
{
if (table.PrimaryKey.Length == 1)
{
whereStatement = CreateWhereInStatement(criteria, table);
}
else if (table.PrimaryKey.Length > 1)
{
whereStatement = CreateWhereExistsStatement(criteria, table);
}
}
else
{
whereStatement = _expressionFormatter.Format(criteria);
}
if (!string.IsNullOrEmpty(whereStatement))
_commandBuilder.Append(" where " + whereStatement);
}
return _commandBuilder;
}
private Func<IDictionary<string, object>, bool> FunctionExpressionToWhereClause(SimpleExpression arg)
{
var function = arg.RightOperand as SimpleFunction;
if (ReferenceEquals(function, null)) throw new InvalidOperationException("Expression type of function but no function supplied.");
if (function.Name.Equals("like", StringComparison.OrdinalIgnoreCase) ||
function.Name.Equals("notlike", StringComparison.OrdinalIgnoreCase))
{
var pattern = function.Args[0].ToString();
if (pattern.Contains("%") || pattern.Contains("_")) // SQL Server LIKE
{
pattern = pattern.Replace("%", ".*").Replace('_', '.');
}
var regex = new Regex("^" + pattern + "$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
if (function.Name.Equals("like", StringComparison.OrdinalIgnoreCase))
{
return d => Resolve(d, arg.LeftOperand).Count > 0 && Resolve(d, arg.LeftOperand).OfType<string>().Any(regex.IsMatch);
}
if (function.Name.Equals("notlike", StringComparison.OrdinalIgnoreCase))
{
return d => Resolve(d, arg.LeftOperand).Count > 0 && Resolve(d, arg.LeftOperand).OfType<string>().All(input => !regex.IsMatch(input));
}
}
throw new NotSupportedException("Expression Function not supported.");
}
private string LogicalExpressionToWhereClause(SimpleExpression expression)
{
return string.Format("({0} {1} {2})",
Format((SimpleExpression)expression.LeftOperand),
expression.Type.ToString().ToUpperInvariant(),
Format((SimpleExpression)expression.RightOperand));
}
private string NotEqualExpressionToWhereClause(SimpleExpression expression)
{
if (expression.RightOperand == null) return string.Format("{0} IS NOT NULL", expression.LeftOperand);
if (CommonTypes.Contains(expression.RightOperand.GetType())) return FormatAsComparison(expression, "!=");
return FormatAsComparison(expression, "!=");
}
private static IEnumerable<Tuple<ObjectName,ObjectName>> GetTableNames(SimpleExpression expression, string schema)
{
return GetReferencesFromExpression(expression)
.SelectMany(r => DynamicReferenceToTuplePairs(r, schema))
.Select((table1, table2) => Tuple.Create(new ObjectName(schema, table1), new ObjectName(schema, table2)))
.Distinct();
}
private void SetQueryContext(SimpleQuery query)
{
_query = query;
_tableName = _schema.BuildObjectName(query.TableName);
_table = _schema.FindTable(_tableName);
var selectClause = _query.Clauses.OfType<SelectClause>().SingleOrDefault();
if (selectClause != null)
{
if (selectClause.Columns.OfType<AllColumnsSpecialReference>().Any())
{
_columns = ExpandAllColumnsReferences(selectClause.Columns).ToArray();
}
else
{
_columns = selectClause.Columns.ToArray();
}
}
else
{
_columns = _table.Columns.Select(c => ObjectReference.FromStrings(_table.Schema, _table.ActualName, c.ActualName)).ToArray();
}
HandleWithClauses();
_whereCriteria = _query.Clauses.OfType<WhereClause>().Aggregate(SimpleExpression.Empty,
(seed, where) => seed && where.Criteria);
_havingCriteria = _query.Clauses.OfType<HavingClause>().Aggregate(SimpleExpression.Empty,
(seed, having) => seed && having.Criteria);
_commandBuilder.SetText(GetSelectClause(_tableName));
}
public IEnumerable<IDictionary<string, object>> Find(string tableName, SimpleExpression criteria)
{
if (criteria == null) return FindAll(tableName);
var commandBuilder = new FindHelper(_schema).GetFindByCommand(tableName, criteria);
return ExecuteQuery(commandBuilder);
}
private string NotEqualExpressionToWhereClause(SimpleExpression expression)
{
if (expression.RightOperand == null) return string.Format("{0} {1}", FormatObject(expression.LeftOperand, null), Operators.IsNotNull);
if (CommonTypes.Contains(expression.RightOperand.GetType())) return FormatAsComparison(expression, "!=");
return FormatAsComparison(expression, Operators.NotEqual);
}
public QueryComplete Format(SimpleExpression expression)
{
switch (expression.Type)
{
case SimpleExpressionType.And:
return LogicalExpression(expression, (l, r) => Query.And(l, r));
case SimpleExpressionType.Equal:
return EqualExpression(expression);
case SimpleExpressionType.GreaterThan:
return BinaryExpression(expression, Query.GT);
case SimpleExpressionType.GreaterThanOrEqual:
return BinaryExpression(expression, Query.GTE);
case SimpleExpressionType.LessThan:
return BinaryExpression(expression, Query.LT);
case SimpleExpressionType.LessThanOrEqual:
return BinaryExpression(expression, Query.LTE);
case SimpleExpressionType.Function:
return FunctionExpression(expression);
case SimpleExpressionType.NotEqual:
return NotEqualExpression(expression);
case SimpleExpressionType.Or:
return LogicalExpression(expression, (l, r) => Query.Or(l, r));
}
throw new NotSupportedException();
}
public ICommandBuilder GetUpdateCommand(string tableName, IDictionary<string, object> data, SimpleExpression criteria)
{
var table = _schema.FindTable(tableName);
_commandBuilder.Append(GetUpdateClause(table, data));
if (criteria != null )
{
string whereStatement = null;
if (criteria.GetOperandsOfType<ObjectReference>().Any(o => !o.GetOwner().GetName().Equals(tableName)))
{
if (table.PrimaryKey.Length == 1)
{
whereStatement = CreateWhereInStatement(criteria, table);
}
else if (table.PrimaryKey.Length > 1)
{
whereStatement = CreateWhereExistsStatement(criteria, table);
}
}
else
{
whereStatement = _expressionFormatter.Format(criteria);
}
if (!string.IsNullOrEmpty(whereStatement))
_commandBuilder.Append(" where " + whereStatement);
}
return _commandBuilder;
}
private IDictionary<string, object> Upsert(string tableName, IDictionary<string, object> data, SimpleExpression criteria, bool resultRequired,
IDbConnection connection)
{
var finder = _transaction == null
? new AdoAdapterFinder(_adapter, connection)
: new AdoAdapterFinder(_adapter, _transaction);
var existing = finder.FindOne(tableName, criteria);
if (existing != null)
{
// Don't update columns used as criteria
var keys = criteria.GetOperandsOfType<ObjectReference>().Select(o => o.GetName().Homogenize());
var updateData = data.Where(kvp => keys.All(k => k != kvp.Key.Homogenize())).ToDictionary();
if (updateData.Count == 0)
{
return existing;
}
var commandBuilder = new UpdateHelper(_adapter.GetSchema()).GetUpdateCommand(tableName, updateData, criteria);
if (_transaction == null)
{
_adapter.Execute(commandBuilder, connection);
}
else
{
_adapter.Execute(commandBuilder, _transaction);
}
return resultRequired ? finder.FindOne(tableName, criteria) : null;
}
var inserter = _transaction == null
? new AdoAdapterInserter(_adapter, connection)
: new AdoAdapterInserter(_adapter, _transaction);
return inserter.Insert(tableName, data, resultRequired);
}
private string TryFormatAsRange(SimpleExpression expression, IRange range, string op)
{
return (range != null)
?
string.Format("{0} {1} {2}", FormatObject(expression.LeftOperand, expression.RightOperand), op, FormatRange(range, expression.LeftOperand))
:
null;
}
private string NotEqualExpressionToWhereClause(SimpleExpression expression)
{
if (expression.RightOperand == null)
return string.Format("({0} ge '')", FormatObject(expression.LeftOperand));
if (CommonTypes.Contains(expression.RightOperand.GetType())) return FormatAsComparison(expression, "ne");
return FormatAsComparison(expression, "ne");
}
public override IEnumerable<IDictionary<string, object>> Find(string tableName, SimpleExpression criteria)
{
var table = GetTable(tableName);
if (ReferenceEquals(criteria, null)) return table.GetAllRows();
var filter = new ExpressionFormatter().Format(criteria);
return table.Query(filter);
}
private string TryFormatAsInList(SimpleExpression expression, IEnumerable list, string op)
{
return (list != null)
?
string.Format("{0} {1} {2}", FormatObject(expression.LeftOperand, expression.RightOperand), op, FormatList(list, expression.LeftOperand))
:
null;
}
private string EqualExpressionToWhereClause(SimpleExpression expression)
{
if (expression.RightOperand == null)
return string.Format("not({0} ge '')", FormatObject(expression.LeftOperand));
if (CommonTypes.Contains(expression.RightOperand.GetType())) return FormatAsComparison(expression, "eq");
return TryFormatAsRange(expression.LeftOperand, expression.RightOperand as IRange)
?? FormatAsComparison(expression, "eq");
}
public override IEnumerable<IDictionary<string, object>> Find(string tableName, SimpleExpression criteria)
{
var query = new SimpleQuery(this, tableName)
.Where(criteria);
IEnumerable<SimpleQueryClauseBase> unhandledClauses;
return new MongoAdapterFinder(this, _expressionFormatter)
.Find(GetCollection(tableName), query, out unhandledClauses);
}
private IEnumerable<IDictionary<string, object>> FindByExpression(string tableName, SimpleExpression criteria)
{
var baseTable = GetBaseTable(tableName);
var concreteTableName = baseTable == null ? tableName : baseTable.ActualName;
var cmd = GetCommandBuilder().BuildCommand(concreteTableName, criteria);
return Utils.ExecuteAndUnwrap(() =>
GetODataClientCommand(cmd).FindEntriesAsync());
}
public string GetJoinClauses(string mainTableName, SimpleExpression expression)
{
_done.AddOrUpdate(mainTableName, string.Empty, (s, o) => string.Empty);
foreach (var tablePair in GetTablePairs(expression))
{
AddJoin(tablePair.Item1, tablePair.Item2);
}
return string.Join(" ", _done.Values.Where(s => !string.IsNullOrWhiteSpace(s)));
}
public Func<object[], IEnumerable<IDictionary<string, object>>> CreateFindDelegate(string tableName, SimpleExpression criteria)
{
if (criteria == null)
{
return _ => FindAll(ObjectName.Parse(tableName));
}
var commandTemplate = GetCommandTemplate(tableName, criteria);
return args => ExecuteQuery(commandTemplate, args);
}
public IDictionary<string, object> Upsert(string tableName, IDictionary<string, object> data, SimpleExpression criteria, bool resultRequired)
{
var connection = _connection ?? _adapter.CreateConnection();
using (connection.MaybeDisposable())
{
connection.OpenIfClosed();
return Upsert(tableName, data, criteria, resultRequired, connection);
}
}
public QueryCommand BuildCommand(string tablePath, SimpleExpression criteria)
{
return new QueryCommand()
{
TablePath = tablePath,
Criteria = criteria,
FilterExpression = new ExpressionConverter(_excludeResourceTypeExpressions).ConvertExpression(criteria),
};
}
public Func<object[],IDictionary<string,object>> CreateFindOneDelegate(string tableName, SimpleExpression criteria)
{
if (criteria == null)
{
return _ => FindAll(_adapter.GetSchema().BuildObjectName(tableName)).FirstOrDefault();
}
var commandTemplate = GetCommandTemplate(tableName, criteria);
return args => ExecuteSingletonQuery(commandTemplate, args);
}
public IDictionary<string, object> FindOne(MongoCollection<BsonDocument> collection, SimpleExpression criteria)
{
if (criteria == null)
return collection.FindOne().ToSimpleDictionary();
var mongoQuery = _expressionFormatter.Format(criteria);
var results = collection.FindOne(mongoQuery);
return results.ToSimpleDictionary();
}
public IEnumerable<string> GetJoinClauses(ObjectName mainTableName, SimpleExpression expression)
{
_done.AddOrUpdate(mainTableName, string.Empty, (s, o) => string.Empty);
var tablePairs = GetTableNames(expression, mainTableName.Schema).Distinct().ToList();
foreach (var tablePair in tablePairs)
{
AddJoin(tablePair.Item1, tablePair.Item2);
}
return tablePairs.Select(tp => _done[tp.Item2]).Distinct();
}
private static IEnumerable <Tuple <ObjectName, ObjectName> > GetTableNames(SimpleExpression expression, string schema)
{
return(expression == null?Enumerable.Empty <Tuple <ObjectName, ObjectName> >() : GetTableNames(GetReferencesFromExpression(expression), schema));
}
/// <summary>
/// 获取用户充值订单表
/// </summary>
/// <param name="model">model</param>
/// <returns>用户充值订单表</returns>
/// wuyf
public ResultModel GetZJ_RechargeOrderList(SearchZJ_RechargeOrderModel model)
{
var tb = _database.Db.ZJ_RechargeOrder;
var where = new SimpleExpression(1, 1, SimpleExpressionType.Equal); //
if (!string.IsNullOrEmpty(model.Email))
{
//用户Email
where = new SimpleExpression(where, _database.Db.YH_User.Email.Like("%" + model.Email.Trim() + "%"), SimpleExpressionType.And);
}
if (!string.IsNullOrEmpty(model.RealName))
{
//用户真实姓名
where = new SimpleExpression(where, _database.Db.YH_User.RealName.Like("%" + model.RealName.Trim() + "%"), SimpleExpressionType.And);
}
if (!string.IsNullOrEmpty(model.Account))
{
//用户登录账户
where = new SimpleExpression(where, _database.Db.YH_User.Account.Like("%" + model.Account.Trim() + "%"), SimpleExpressionType.And);
}
if (model.UserID > 0)
{
//用户ID
where = new SimpleExpression(where, tb.UserID == model.UserID, SimpleExpressionType.And);
}
if (!string.IsNullOrEmpty(model.OrderNO))
{
//订单编号
where = new SimpleExpression(where, tb.OrderNO.Like("%" + model.OrderNO.Trim() + "%"), SimpleExpressionType.And);
}
if (model.OrderSource != 10)
{
//订单来源
where = new SimpleExpression(where, tb.OrderSource == model.OrderSource, SimpleExpressionType.And);
}
if (model.BeginCreateDT != null && model.BeginCreateDT.Year != 0001)
{
//订单生成 开始时间
where = new SimpleExpression(where, tb.CreateDT >= model.BeginCreateDT, SimpleExpressionType.And);
}
if (model.EedCreateDT != null && model.EedCreateDT.Year != 0001)
{
//订单生成 结束时间
where = new SimpleExpression(where, tb.CreateDT < model.EedCreateDT, SimpleExpressionType.And);
}
if (model.BeginRechargeDT != null && model.BeginRechargeDT.Value.Year != 0001)
{
//充值 开始时间
where = new SimpleExpression(where, tb.RechargeDT >= model.BeginRechargeDT, SimpleExpressionType.And);
}
if (model.EndRechargeDT != null && model.EndRechargeDT.Value.Year != 0001)
{
//充值 结束时间
where = new SimpleExpression(where, tb.RechargeDT < model.EndRechargeDT, SimpleExpressionType.And);
}
if (model.RechargeChannel > 0)
{
//充值 通道
where = new SimpleExpression(where, tb.RechargeChannel == model.RechargeChannel, SimpleExpressionType.And);
}
if (model.RechargeResult != 10)
{
//充值 结果
where = new SimpleExpression(where, tb.RechargeResult == model.RechargeResult, SimpleExpressionType.And);
}
dynamic pc;
var query = tb
.Query()
.LeftJoin(_database.Db.YH_User, out pc)
.On(_database.Db.YH_User.UserID == tb.UserID)
.Select(
tb.UserID,
tb.OrderNO,
tb.RechargeChannel,
tb.RechargeAmount,
tb.RechargeDT,
tb.RechargeResult,
tb.CreateDT,
tb.IsDisplay,
tb.OrderSource,
pc.Phone,
pc.NickName,
pc.RealName,
pc.Email,
pc.Account
)
.Where(where)
.OrderByUserIDDescending();
var result = new ResultModel
{
Data = new SimpleDataPagedList <ZJ_RechargeOrderModel>(query,
model.PagedIndex, model.PagedSize)
};
return(result);
}
/// <summary>
/// 返回满足查询条件的人员基本信息实体列表
/// </summary>
/// <param name="param">查询条件</param>
/// <returns>人员基本信息实体列表</returns>
public IList <Sys_Person> GetSys_Persons(QueryParameter param)
{
string sql = @"SELECT KeyId, PersonCode, WorkNo, PersonName, DomainAcc, PassWord, SexId, Birthday, IDCard, Photo, Nation, NativePlace, PoliticalId, JoinDate, EducationId, GraduateFrom, Perfessional, Ability, IsFulltime, WorkType, WorkStartDate, JobLevel, JobSure, DutyId, DutySure, Contact, Officephone, Homephone, Mobilephone, HomeAddress, PostCode, EMail, Certificate, Status, DisplayOrder, Remark, HelperCode, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn FROM dbo.Sys_Person";
if (param != null)
{
sql = QueryParameter.CompleteSqlString(sql, param);
}
Database db = DatabaseFactory.CreateDatabase(DBLink.SysDBLink.ToString());
DbCommand command = db.GetSqlStringCommand(sql);
if (param != null)
{
//设置参数
foreach (IExpression exp in param.WhereExpressions)
{
if (exp is SimpleExpression)
{
SimpleExpression simple = exp as SimpleExpression;
db.AddInParameter(command, simple.ExpName, simple.DbType, simple.Value);
}
}
}
IList <Sys_Person> list = new List <Sys_Person>();
using (IDataReader dr = db.ExecuteReader(command))
{
while (dr.Read())
{
Sys_Person sys_Person = new Sys_Person();
sys_Person.KeyId = (int)dr["KeyId"];
sys_Person.PersonCode = (string)dr["PersonCode"];
sys_Person.WorkNo = dr["WorkNo"] == DBNull.Value ? null : (string)dr["WorkNo"];
sys_Person.PersonName = (string)dr["PersonName"];
sys_Person.DomainAcc = (string)dr["DomainAcc"];
sys_Person.PassWord = (string)dr["PassWord"];
sys_Person.SexId = (int)dr["SexId"];
sys_Person.Birthday = dr["Birthday"] == DBNull.Value ? null : (DateTime?)dr["Birthday"];
sys_Person.IDCard = dr["IDCard"] == DBNull.Value ? null : (string)dr["IDCard"];
sys_Person.Photo = dr["Photo"] == DBNull.Value ? null : (byte[])dr["Photo"];
sys_Person.Nation = dr["Nation"] == DBNull.Value ? null : (string)dr["Nation"];
sys_Person.NativePlace = dr["NativePlace"] == DBNull.Value ? null : (string)dr["NativePlace"];
sys_Person.PoliticalId = dr["PoliticalId"] == DBNull.Value ? null : (int?)dr["PoliticalId"];
sys_Person.JoinDate = dr["JoinDate"] == DBNull.Value ? null : (DateTime?)dr["JoinDate"];
sys_Person.EducationId = dr["EducationId"] == DBNull.Value ? null : (int?)dr["EducationId"];
sys_Person.GraduateFrom = dr["GraduateFrom"] == DBNull.Value ? null : (string)dr["GraduateFrom"];
sys_Person.Perfessional = dr["Perfessional"] == DBNull.Value ? null : (string)dr["Perfessional"];
sys_Person.Ability = dr["Ability"] == DBNull.Value ? null : (string)dr["Ability"];
sys_Person.IsFulltime = dr["IsFulltime"] == DBNull.Value ? null : (bool?)dr["IsFulltime"];
sys_Person.WorkType = dr["WorkType"] == DBNull.Value ? null : (int?)dr["WorkType"];
sys_Person.WorkStartDate = dr["WorkStartDate"] == DBNull.Value ? null : (DateTime?)dr["WorkStartDate"];
sys_Person.JobLevel = dr["JobLevel"] == DBNull.Value ? null : (int?)dr["JobLevel"];
sys_Person.JobSure = dr["JobSure"] == DBNull.Value ? null : (DateTime?)dr["JobSure"];
sys_Person.DutyId = dr["DutyId"] == DBNull.Value ? null : (int?)dr["DutyId"];
sys_Person.DutySure = dr["DutySure"] == DBNull.Value ? null : (DateTime?)dr["DutySure"];
sys_Person.Contact = dr["Contact"] == DBNull.Value ? null : (string)dr["Contact"];
sys_Person.Officephone = dr["Officephone"] == DBNull.Value ? null : (string)dr["Officephone"];
sys_Person.Homephone = dr["Homephone"] == DBNull.Value ? null : (string)dr["Homephone"];
sys_Person.Mobilephone = dr["Mobilephone"] == DBNull.Value ? null : (string)dr["Mobilephone"];
sys_Person.HomeAddress = dr["HomeAddress"] == DBNull.Value ? null : (string)dr["HomeAddress"];
sys_Person.PostCode = dr["PostCode"] == DBNull.Value ? null : (string)dr["PostCode"];
sys_Person.EMail = dr["EMail"] == DBNull.Value ? null : (string)dr["EMail"];
sys_Person.Certificate = dr["Certificate"] == DBNull.Value ? null : (string)dr["Certificate"];
sys_Person.Status = (bool)dr["Status"];
sys_Person.DisplayOrder = dr["DisplayOrder"] == DBNull.Value ? null : (int?)dr["DisplayOrder"];
sys_Person.Remark = dr["Remark"] == DBNull.Value ? null : (string)dr["Remark"];
sys_Person.HelperCode = dr["HelperCode"] == DBNull.Value ? null : (string)dr["HelperCode"];
sys_Person.CreatedBy = (int)dr["CreatedBy"];
sys_Person.CreatedOn = (DateTime)dr["CreatedOn"];
sys_Person.ModifiedBy = dr["ModifiedBy"] == DBNull.Value ? null : (int?)dr["ModifiedBy"];
sys_Person.ModifiedOn = dr["ModifiedOn"] == DBNull.Value ? null : (DateTime?)dr["ModifiedOn"];
list.Add(sys_Person);
}
}
return(list);
}
/// <summary>
/// 获取用户余额信息表
/// </summary>
/// <param name="model">model</param>
/// <returns>退换用户余额信息表</returns>
public ResultModel GetZJ_UserBalanceList(SearchZJ_UserBalanceModel model)
{
var tb = _database.Db.ZJ_UserBalance;
var where = new SimpleExpression(1, 1, SimpleExpressionType.Equal); //
if (!string.IsNullOrEmpty(model.RealName) && model.RealName.Trim() != "")
{
//用户真实姓名
where = new SimpleExpression(where,
_database.Db.YH_User.RealName.Like("%" + model.RealName.Trim() + "%"), SimpleExpressionType.And);
}
if (!string.IsNullOrEmpty(model.Phone) && model.Phone.Trim() != "")
{
//用户手机
where = new SimpleExpression(where, _database.Db.YH_User.Phone.Like("%" + model.Phone.Trim() + "%"),
SimpleExpressionType.And);
}
if (!string.IsNullOrEmpty(model.Email) && model.Email.Trim() != "")
{
//Email
where = new SimpleExpression(where, _database.Db.YH_User.Email.Like("%" + model.Email.Trim() + "%"),
SimpleExpressionType.And);
}
if (!string.IsNullOrEmpty(model.Account) && model.Account.Trim() != "")
{
//用户真实姓名
where = new SimpleExpression(where, _database.Db.YH_User.Account.Like("%" + model.Account.Trim() + "%"),
SimpleExpressionType.And);
}
if (model.UserID > 0)
{
//用户ID
where = new SimpleExpression(where, tb.UserID == model.UserID, SimpleExpressionType.And);
}
dynamic pc;
dynamic zjlog;
var query = tb
.Query()
//.LeftJoin(_database.Db.ZJ_UserBalanceChangeLog, out zjlog)
//.On(_database.Db.ZJ_UserBalanceChangeLog.UserID == tb.UserID)
.LeftJoin(_database.Db.YH_User, out pc)
.On(_database.Db.YH_User.UserID == tb.UserID)
.Select(
tb.UserID,
tb.ConsumeBalance,
tb.Vouchers,
tb.AccountStatus,
tb.CreateBy,
tb.CreateDT,
tb.UpdateBy,
tb.UpdateDT,
//zjlog.Remark,
pc.Phone,
pc.RealName,
pc.Email,
pc.NickName,
pc.Account
)
.Where(where)
.OrderByUserIDDescending();
var result = new ResultModel
{
Data = new SimpleDataPagedList <ZJ_UserBalanceModel>(query,
model.PagedIndex, model.PagedSize)
};
return(result);
}
internal SimpleExpression CompileLocationCriteria(IEnumerable <LocationSearchCriteria> criteria, SimpleExpression current)
{
foreach (var locationCriteria in criteria)
{
var column = db.Members[locationCriteria.InternalName];
var expr = CompileStringExpression(column, locationCriteria);
current = Combine(current, expr);
}
return(current);
}
/// <summary>
/// 获取列表
/// </summary>
/// <returns>列表</returns>
public ResultModel GetBannerProduct(SearchbannerProductModel model, out int total)
{
dynamic cl;
dynamic pc;
var tb = base._database.Db.bannerProduct;
var where = new SimpleExpression(1, 1, SimpleExpressionType.Equal);
//商品状态
where = new SimpleExpression(where, _database.Db.Product.Status == 4, SimpleExpressionType.And);
var result = new ResultModel();
total = 0;
if (model.PlaceCode > 0)
{
where = new SimpleExpression(where, tb.PlaceCode == model.PlaceCode, SimpleExpressionType.And);//位置（分类）（代表页面里的某个部位）
}
if (model.IdentityStatus > 0)
{
//标识ID （代表页面）
where = new SimpleExpression(where, tb.IdentityStatus == model.IdentityStatus, SimpleExpressionType.And);
}
if (model.LanguageID > 0)
{
//选择语言
where = new SimpleExpression(where, _database.Db.Product_Lang.languageId == model.LanguageID, SimpleExpressionType.And);
}
else
{
if (model.IdentityStatus > 1)
{
//1不是楼层
where = new SimpleExpression(where, _database.Db.Product_Lang.languageId == 1, SimpleExpressionType.And);
}
}
if (model.productId > 0)
{
where = new SimpleExpression(where, _database.Db.Product.productId == model.productId, SimpleExpressionType.And);
}
if (model.bannerProductId > 0)
{
where = new SimpleExpression(where, tb.bannerProductId == model.bannerProductId, SimpleExpressionType.And);
}
var query = _database.Db.bannerProduct
//.FindAllBybannerProductId(1215865440)
.Query()
//.FindAll()
.LeftJoin(_database.Db.Product_Lang, out cl)
.On(_database.Db.Product_Lang.ProductId == _database.Db.bannerProduct.ProductId)
.LeftJoin(_database.Db.Product, out pc)
.On(_database.Db.Product.ProductId == _database.Db.bannerProduct.ProductId)
.Select(
tb.productId,
tb.PicAddress,
tb.Sorts,
tb.PlaceCode,
tb.IdentityStatus,
tb.CreateBy,
tb.CreateDT,
tb.UpdateBy,
tb.UpdateDT,
tb.bannerProductId,
cl.ProductName,
cl.LanguageID,
pc.HKPrice,
pc.CategoryId,
pc.Status
)
//.Where(where);
//.Where(cl.languageId == null || cl.languageId == model.LanguageID)
.Where(where)
.OrderBySorts()
.ToList <bannerProductModel>()
;
//query = query.Skip(model.PagedIndex * model.PagedSize).Take(model.PagedSize).ToList<bannerProductModel>();
//total = query.Count;
result.Data = query;
//result.Data = new SimpleDataPagedList<bannerProductModel>(base._database.Db.bannerProduct.FindAll(where).OrderBySortsDescending(), model.PagedIndex, model.PagedSize);
return(result);
}
// 如果是Component，则用入款金额.数额，不需要用:，不需要创建子Criteria。
private static NHibernate.Criterion.ICriterion GetCriterion(ISessionFactory sessionFactory, ref ICriteria originalCriteria,
Dictionary <string, ICriteria> aliases,
ISearchExpression se, ref bool hasCollection, ref int paramCnt)
{
if (se == null)
{
return(null);
}
if (se is Feng.Search.QueryExpression)
{
return(null);
}
SimpleExpression cse = se as SimpleExpression;
if (cse == null)
{
LogicalExpression le = se as LogicalExpression;
NHibernate.Criterion.ICriterion left = GetCriterion(sessionFactory, ref originalCriteria, aliases, le.LeftHandSide, ref hasCollection, ref paramCnt);
NHibernate.Criterion.ICriterion right = GetCriterion(sessionFactory, ref originalCriteria, aliases, le.RightHandSide, ref hasCollection, ref paramCnt);
switch (le.LogicOperator)
{
case LogicalOperator.And:
return(NHibernate.Criterion.Expression.And(left, right));
case LogicalOperator.Or:
return(NHibernate.Criterion.Expression.Or(left, right));
case LogicalOperator.Not:
return(NHibernate.Criterion.Expression.Not(left));
default:
System.Diagnostics.Debug.Assert(false, "Not supported LogicOperator");
return(null);
}
}
else
{
// No PropertyName Process
if (cse.Operator == SimpleOperator.Sql)
{
return(NHibernate.Criterion.Expression.Sql(cse.FullPropertyName));
}
string propertyName = TryCreateCriteria(cse.FullPropertyName, ref originalCriteria, aliases);
if (!string.IsNullOrEmpty(cse.FullPropertyName))
{
// Convert Data to dest type
bool hasC;
Type destType;
if (cse.Operator != SimpleOperator.EqProperty)
{
destType = NHibernateHelper.GetPropertyType(sessionFactory, typeof(T), cse.FullPropertyName, out hasC);
hasCollection |= hasC;
}
else
{
destType = typeof(string);
}
IList array = cse.Values as IList;
if (array != null)
{
for (int i = 0; i < array.Count; ++i)
{
if (destType != array[i].GetType())
{
array[i] = ConvertHelper.ChangeType(array[i], destType);
}
}
paramCnt += array.Count;
}
else
{
if (cse.Values != null)
{
if (destType != cse.Values.GetType())
{
cse.Values = ConvertHelper.ChangeType(cse.Values, destType);
}
}
paramCnt++;
}
}
switch (cse.Operator)
{
case SimpleOperator.Eq:
return(NHibernate.Criterion.Expression.Eq(propertyName, cse.Values));
case SimpleOperator.NotEq:
return(NHibernate.Criterion.Expression.Not(NHibernate.Criterion.Expression.Eq(propertyName, cse.Values)));
case SimpleOperator.EqProperty:
{
if (cse.Values == null)
{
throw new ArgumentException("EqProperty's Value should not be null!", "cse");
}
string toPropertyName = TryCreateCriteria(cse.Values.ToString(), ref originalCriteria, aliases);
return(NHibernate.Criterion.Expression.EqProperty(propertyName, toPropertyName));
}
case SimpleOperator.Like:
{
int?propertyLength = NHibernateHelper.GetPropertyLength(sessionFactory, typeof(T), cse.FullPropertyName);
return(GetLikeCriterion(propertyName, cse.Values, propertyLength));
}
case SimpleOperator.InG:
return(NHibernate.Criterion.Expression.In(propertyName, cse.Values as IList));
case SimpleOperator.IsNotNull:
return(NHibernate.Criterion.Expression.IsNotNull(propertyName));
case SimpleOperator.IsNull:
return(NHibernate.Criterion.Expression.IsNull(propertyName));
case SimpleOperator.Gt:
return(NHibernate.Criterion.Expression.Gt(propertyName, cse.Values));
case SimpleOperator.Lt:
return(NHibernate.Criterion.Expression.Lt(propertyName, cse.Values));
case SimpleOperator.Ge:
return(NHibernate.Criterion.Expression.Ge(propertyName, cse.Values));
case SimpleOperator.Le:
return(NHibernate.Criterion.Expression.Le(propertyName, cse.Values));
case SimpleOperator.GInG:
{
int?propertyLength = NHibernateHelper.GetPropertyLength(sessionFactory, typeof(T), cse.FullPropertyName);
IList data = cse.Values as IList;
// 通过查询控件控制
// 当用于客户用途查找类型时，只能Like
//if (data.Count <= 1)
{
NHibernate.Criterion.ICriterion criterion = GetLikeCriterion(propertyName, data[0], propertyLength);
for (int i = 1; i < data.Count; ++i)
{
criterion = NHibernate.Criterion.Expression.Or(criterion, GetLikeCriterion(propertyName, data[i], propertyLength));
}
return(criterion);
}
//// 当多行时，不在模糊查找
//else
//{
// return NHibernate.Criterion.Expression.In(propertyName, data);
//}
}
case SimpleOperator.Any:
return(NHibernate.Criterion.Expression.EqProperty(propertyName, propertyName));
case SimpleOperator.Sql:
return(NHibernate.Criterion.Expression.Sql(cse.FullPropertyName));
default:
System.Diagnostics.Debug.Assert(false, "Not supported SimpleOperator");
return(null);
}
}
}
public IDictionary <string, object> Upsert(string tableName, IDictionary <string, object> data, SimpleExpression criteria, bool resultRequired)
{
var connection = _connection ?? _adapter.CreateConnection();
using (connection.MaybeDisposable())
{
connection.OpenIfClosed();
return(Upsert(tableName, data, criteria, resultRequired, connection));
}
}
private IDictionary <string, object> Upsert(string tableName, IDictionary <string, object> data, SimpleExpression criteria, bool resultRequired,
IDbConnection connection)
{
var finder = _transaction == null
? new AdoAdapterFinder(_adapter, connection)
: new AdoAdapterFinder(_adapter, _transaction);
var existing = finder.FindOne(tableName, criteria);
if (existing != null)
{
// Don't update columns used as criteria
var keys = criteria.GetOperandsOfType <ObjectReference>().Select(o => o.GetName().Homogenize());
var updateData = data.Where(kvp => keys.All(k => k != kvp.Key.Homogenize())).ToDictionary();
if (updateData.Count == 0)
{
return(existing);
}
var commandBuilder = new UpdateHelper(_adapter.GetSchema()).GetUpdateCommand(tableName, updateData, criteria);
if (_transaction == null)
{
_adapter.Execute(commandBuilder, connection);
}
else
{
_adapter.Execute(commandBuilder, _transaction);
}
return(resultRequired ? finder.FindOne(tableName, criteria) : null);
}
var inserter = _transaction == null
? new AdoAdapterInserter(_adapter, connection)
: new AdoAdapterInserter(_adapter, _transaction);
return(inserter.Insert(tableName, data, resultRequired));
}
public int Update(MongoCollection <BsonDocument> collection, IDictionary <string, object> data, SimpleExpression criteria)
{
var condition = _expressionFormatter.Format(criteria);
if (data.ContainsKey("Id"))
{
data["_id"] = data["Id"];
data.Remove("Id");
}
var update = new UpdateDocument("$set", data.ToBsonDocument());
var result = collection.Update(condition, update, UpdateFlags.Multi);
if (result != null)
{
return((int)result.DocumentsAffected);
}
return(int.MaxValue);
}
/// <summary>
/// 获取banner图片列表
/// </summary>
/// <returns>banner图片列表</returns>
public ResultModel GetBannerProduct(SearchbannerProductModel model)
{
var tb = base._database.Db.bannerProduct;
var where = new SimpleExpression(1, 1, SimpleExpressionType.Equal);
//商品状态
where = new SimpleExpression(where, _database.Db.Product.Status == 4, SimpleExpressionType.And);
if (model.PlaceCode > 0)
{
where = new SimpleExpression(where, tb.PlaceCode == model.PlaceCode, SimpleExpressionType.And);//位置（分类）（代表页面里的某个部位）
}
if (model.IdentityStatus > 0)
{
//标识ID （代表页面）
where = new SimpleExpression(where, tb.IdentityStatus == model.IdentityStatus, SimpleExpressionType.And);
}
var result = new ResultModel();
dynamic pc;
var query = _database.Db.bannerProduct
.Query()
.LeftJoin(_database.Db.Product, out pc)
.On(_database.Db.Product.ProductId == _database.Db.bannerProduct.ProductId)
.Select(
tb.productId,
tb.PicAddress,
tb.Sorts,
tb.PlaceCode,
tb.IdentityStatus,
tb.CreateBy,
tb.CreateDT,
tb.UpdateBy,
tb.UpdateDT,
tb.bannerProductId,
pc.HKPrice,
pc.CategoryId
)
//.Where(where);
//.Where(cl.languageId == null || cl.languageId == model.LanguageID)
.Where(where)
.OrderBySorts()
.ToList <bannerProductModel>()
;
result.Data = query;
//var result = new ResultModel()
//{
// Data = new SimpleDataPagedList<bannerProductModel>(base._database.Db.bannerProduct.FindAll(where).OrderBySorts(), model.PagedIndex, model.PagedSize)
//};
return(result);
}
public override int Update(string tableName, IDictionary <string, object> data, SimpleExpression criteria)
{
int updated = 0;
var elementsToUpdate = GetTableElement(tableName).Elements()
.Where(XmlPredicateBuilder.GetPredicate(criteria));
foreach (var element in elementsToUpdate)
{
foreach (var kvp in data)
{
var attribute = element.TryGetAttribute(kvp.Key);
if (attribute != null)
{
attribute.Value = kvp.Value.ToString();
}
else
{
element.SetAttributeValue(kvp.Key, kvp.Value);
}
}
updated++;
}
return(updated);
}
/// <summary>
/// 根据Sorts查询的上一条或者下一条
/// </summary>
/// <param name="Sorts"></param>
/// <param name="sx"></param>
/// <returns></returns>
public ResultModel GetBannerProduct(long Sorts, int sx, int IdentityStatus, long bannerId, int LanguageID, int BannerPlaceCode)
{
var tb = base._database.Db.bannerProduct;
var where = new SimpleExpression(1, 1, SimpleExpressionType.Equal);
where = new SimpleExpression(where, tb.IdentityStatus == IdentityStatus, SimpleExpressionType.And);
where = new SimpleExpression(where, tb.bannerProductId != bannerId, SimpleExpressionType.And);
//1上移动,2下移动
if (sx == 1)
{
where = new SimpleExpression(where, tb.Sorts >= Sorts, SimpleExpressionType.And);
}
else
{
where = new SimpleExpression(where, tb.Sorts <= Sorts, SimpleExpressionType.And);
}
if (IdentityStatus > 1)
{
//只有多楼层才需要有这个条件,没有楼层区分的不需要
where = new SimpleExpression(where, tb.PlaceCode == BannerPlaceCode, SimpleExpressionType.And);
}
if (LanguageID != null && LanguageID > 0)
{
where = new SimpleExpression(where, _database.Db.Product_Lang.languageId == LanguageID, SimpleExpressionType.And);
}
else
{
where = new SimpleExpression(where, _database.Db.Product_Lang.languageId == 1, SimpleExpressionType.And);
}
var result = new ResultModel();
dynamic cl;
dynamic pc;
var query = _database.Db.bannerProduct
//.FindAllBybannerProductId(1215865440)
.Query()
//.FindAll()
.LeftJoin(_database.Db.Product_Lang, out cl)
.On(_database.Db.Product_Lang.ProductId == _database.Db.bannerProduct.ProductId)
.LeftJoin(_database.Db.Product, out pc)
.On(_database.Db.Product.ProductId == _database.Db.bannerProduct.ProductId)
.Select(
tb.productId,
tb.PicAddress,
tb.Sorts,
tb.PlaceCode,
tb.IdentityStatus,
tb.CreateBy,
tb.CreateDT,
tb.UpdateBy,
tb.UpdateDT,
tb.bannerProductId,
cl.ProductName,
cl.LanguageID,
pc.HKPrice,
pc.CategoryId
)
//.Where(where);
//.Where(cl.languageId == null || cl.languageId == model.LanguageID)
.Where(where)
.OrderBySorts()
.ToList <bannerProductModel>()
;
//query = query.Skip(model.PagedIndex * model.PagedSize).Take(model.PagedSize).ToList<bannerProductModel>();
//total = query.Count;
result.Data = query;
//var result = new Result()
//{
// Data = new SimpleDataPagedList<bannerProductModel>(base._database.Db.bannerProduct.FindAll(where).OrderBySortsDescending(), 0, 10)
//};
return(result);
}
internal SimpleExpression CompileFacetCriteria(IEnumerable <FacetSearchCriteria> criteria, SimpleExpression expr)
{
foreach (var criterion in criteria)
{
var facetReference = db.Member.MemberFacet.As(criterion.InternalName);
var facetExpr = facetReference.FacetId == criterion.FacetId &&
CompileStringExpression(facetReference.FreeTextValue, criterion);
expr = Combine(expr, facetExpr);
}
return(expr);
}
public Func <object[], IDictionary <string, object> > CreateFindOneDelegate(string tableName, SimpleExpression criteria)
{
if (criteria == null)
{
return(_ => FindAll(_adapter.GetSchema().BuildObjectName(tableName)).FirstOrDefault());
}
var commandBuilder = new FindHelper(_adapter.GetSchema())
.GetFindByCommand(_adapter.GetSchema().BuildObjectName(tableName), criteria);
var command = commandBuilder.GetCommand(_adapter.CreateConnection(), _adapter.AdoOptions);
command = _adapter.CommandOptimizer.OptimizeFindOne(command);
var commandTemplate =
commandBuilder.GetCommandTemplate(
_adapter.GetSchema().FindTable(_adapter.GetSchema().BuildObjectName(tableName)));
var cloneable = command as ICloneable;
if (cloneable != null)
{
return(args => ExecuteSingletonQuery((IDbCommand)cloneable.Clone(), args, commandTemplate.Index));
}
else
{
return(args => ExecuteSingletonQuery(commandTemplate, args));
}
}
public override IEnumerable <IDictionary <string, object> > Find(string tableName, SimpleExpression criteria)
{
if (criteria == null)
{
return(FindAll(tableName));
}
return(GetTableElement(tableName).Elements()
.Where(XmlPredicateBuilder.GetPredicate(criteria))
.Select(e => e.AttributesToDictionary()));
}
