public DTOResponse GetList(MsgRequest request)
{
DTOResponse dtoResponse = new DTOResponse();
try
{
string str1 = (string)null;
string str2 = (string)null;
DateTime dt1;
DateTime dt2;
if (!string.IsNullOrEmpty(request.datetimerange) && request.datetimerange.Length > 10)
{
string s1 = request.datetimerange.Substring(0, 10);
string s2 = request.datetimerange.Substring(13, 10);
DateTime.TryParse(s1, out dt1);
DateTime.TryParse(s2, out dt2);
str1 = dt1.ToString("yyyy-MM-dd HH:mm:ss");
str2 = dt2.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
DateTime today = DateTime.Today;
dt1 = today.AddDays(-7.0);
today = DateTime.Today;
dt2 = today.AddDays(1.0);
str1 = dt1.ToString("yyyy-MM-dd HH:mm:ss");
str2 = dt2.ToString("yyyy-MM-dd HH:mm:ss");
}
using (IDbConnection dbConn = HelperConnection.GetConnectionFactory().OpenDbConnection())
{
SqlExpression <SysAlterInfoEntity> expression = dbConn.From <SysAlterInfoEntity>();
expression.And((Expression <Func <SysAlterInfoEntity, bool> >)(x => x.CREATEDATETIME >= dt1 && x.CREATEDATETIME <= dt2));
if (!string.IsNullOrEmpty(request.MSGTEXT))
{
expression.And((Expression <Func <SysAlterInfoEntity, bool> >)(x => x.MSGTEXT.Contains(request.MSGTEXT)));
}
if (!string.IsNullOrEmpty(request.MSGTYPE) && request.MSGTYPE != "0")
{
expression.And((Expression <Func <SysAlterInfoEntity, bool> >)(x => x.MSGTYPE == request.MSGTYPE));
}
if (!string.IsNullOrEmpty(request.MSGSTATE))
{
expression.And((Expression <Func <SysAlterInfoEntity, bool> >)(x => x.MSGSTATE == request.MSGSTATE));
}
long num = dbConn.Count <SysAlterInfoEntity>(expression);
List <SysAlterInfoEntity> list = dbConn.Select <SysAlterInfoEntity>(expression).OrderByDescending <SysAlterInfoEntity, long>((Func <SysAlterInfoEntity, long>)(x => x.ID)).ToList <SysAlterInfoEntity>().Skip <SysAlterInfoEntity>((request.page - 1) * request.limit).Take <SysAlterInfoEntity>(request.limit).ToList <SysAlterInfoEntity>();
dtoResponse.TotalCount = num;
dtoResponse.ResultObject = (object)list;
dtoResponse.IsSuccess = true;
dtoResponse.MessageText = "查询操作成功！";
}
}
catch (Exception ex)
{
dtoResponse.IsSuccess = false;
dtoResponse.MessageText = ex.Message;
MsgService.logger.Error((object)ex);
}
return(dtoResponse);
}
public DTOResponse GetList(LocationStnRequest request)
{
DTOResponse dtoResponse = new DTOResponse();
try
{
using (IDbConnection dbConn = HelperConnection.GetConnectionFactory().OpenDbConnection())
{
SqlExpression <LocStationInfoEntity> expression = dbConn.From <LocStationInfoEntity>();
if (!string.IsNullOrEmpty(request.STATIONTYPE) && request.STATIONTYPE != "0")
{
expression.And(x => x.STATIONTYPE == request.STATIONTYPE);
}
if (!string.IsNullOrEmpty(request.TECHNO) && request.TECHNO != "000")
{
expression.And(x => x.TECHNO == request.TECHNO);
}
List <LocStationInfoEntity> list = dbConn.Select <LocStationInfoEntity>(expression).OrderBy <LocStationInfoEntity, string>((Func <LocStationInfoEntity, string>)(x => x.ID)).ToList <LocStationInfoEntity>();
dtoResponse.ResultObject = (object)list;
dtoResponse.IsSuccess = true;
dtoResponse.MessageText = "查询操作成功！";
}
}
catch (Exception ex)
{
dtoResponse.IsSuccess = false;
dtoResponse.MessageText = ex.Message;
logger.Error((object)ex);
}
return(dtoResponse);
}
private static List <T> NacitatZoznam <T>(this IWebEasCoreRepositoryBase sourceType, SqlExpression <T> filter, string tenantId) where T : class
{
Guid filterTenantGuid;
Type typEntity = typeof(T);
if (typeof(ITenantEntity).IsAssignableFrom(typEntity))
{
if (string.IsNullOrWhiteSpace(tenantId) || !Guid.TryParse(tenantId, out filterTenantGuid))
{
return(new List <T>());
}
filter.And <ITenantEntity>(f => f.D_Tenant_Id == filterTenantGuid);
}
else if (typeof(ITenantEntityNullable).IsAssignableFrom(typEntity))
{
if (string.IsNullOrWhiteSpace(tenantId) || !Guid.TryParse(tenantId, out filterTenantGuid))
{
return(new List <T>());
}
filter.And <ITenantEntityNullable>(f => f.D_Tenant_Id == filterTenantGuid || f.D_Tenant_Id == null);
}
return(sourceType.Db.Select <T>(filter).ToList());
}
public DTOResponse GetList(PlanDetailRequest request)
{
DTOResponse dtoResponse = new DTOResponse();
try
{
using (IDbConnection dbConn = HelperConnection.GetConnectionFactory().OpenDbConnection())
{
SqlExpression <PlanDetailEntity> expression = dbConn.From <PlanDetailEntity>();
if (request.LINESTATE != null)
{
expression.And(x => x.LINESTATE == request.LINESTATE);
}
if (request.PLANTYPE != null)
{
expression.And(x => x.PLANTYPE == request.PLANTYPE);
}
if (!string.IsNullOrEmpty(request.PRODUCTTYPE) && request.PRODUCTTYPE != "0")
{
expression.And(x => x.PRODUCTTYPE == request.PRODUCTTYPE);
}
if (!string.IsNullOrEmpty(request.PRODUCTNO) && request.PRODUCTNO != "0")
{
expression.And(x => x.PRODUCTNO == request.PRODUCTNO);
}
if (!string.IsNullOrEmpty(request.PRODUCTNAME))
{
expression.And(x => x.PRODUCTNAME.Contains(request.PRODUCTNAME));
}
long count = dbConn.Count <PlanDetailEntity>(expression);
List <PlanDetailEntity> entityList = dbConn.Select <PlanDetailEntity>(expression)
.OrderByDescending(x => x.ID)
.Skip <PlanDetailEntity>((request.page - 1) * request.limit)
.Take <PlanDetailEntity>(request.limit)
.ToList <PlanDetailEntity>();
dtoResponse.TotalCount = count;
dtoResponse.ResultObject = (object)entityList;
dtoResponse.IsSuccess = true;
dtoResponse.MessageText = "查询操作成功！";
return(dtoResponse);
}
}
catch (Exception ex)
{
dtoResponse.IsSuccess = false;
dtoResponse.MessageText = ex.Message;
logger.Error((object)ex);
return(dtoResponse);
}
}
private void PrepareJoins(QueryTablePlanner tablePlanner, SqlQueryExpression queryExpression, QueryExpressionFrom queryFrom, ref SqlExpression searchExpression)
{
var fromClause = queryExpression.FromClause;
bool allInner = true;
for (int i = 0; i < fromClause.JoinPartCount; i++)
{
var joinPart = fromClause.GetJoinPart(i);
if (joinPart.JoinType != JoinType.Inner)
{
allInner = false;
}
}
for (int i = 0; i < fromClause.JoinPartCount; i++)
{
var joinPart = fromClause.GetJoinPart(i);
var joinType = joinPart.JoinType;
var onExpression = joinPart.OnExpression;
if (allInner)
{
// If the whole join set is inner joins then simply move the on
// expression (if there is one) to the WHERE clause.
if (searchExpression != null && onExpression != null)
{
searchExpression = SqlExpression.And(searchExpression, onExpression);
}
else if (searchExpression == null)
{
searchExpression = onExpression;
}
}
else
{
// Not all inner joins,
if (joinType == JoinType.Inner && onExpression == null)
{
// Regular join with no ON expression, so no preparation necessary
}
else
{
// Either an inner join with an ON expression, or an outer join with
// ON expression
if (onExpression == null)
{
throw new InvalidOperationException(String.Format("Join of type {0} requires ON expression.", joinType));
}
// Resolve the on_expression
onExpression = onExpression.Prepare(queryFrom.ExpressionPreparer);
// And set it in the planner
tablePlanner.JoinAt(i, joinType, onExpression);
}
}
}
}
public static SqlExpression <T> AndIf <T>(this SqlExpression <T> expr, bool where, string sqlFilter, params object[] filterParams)
{
if (where)
{
return(expr.And(sqlFilter, filterParams));
}
return(expr);
}
private static Privileges QueryPrivileges(IQuery queryContext, ITable grantTable, string grantee,
DbObjectType objectType, ObjectName objectName, bool withOption, bool withPublic)
{
var objectCol = grantTable.GetResolvedColumnName(1);
var paramCol = grantTable.GetResolvedColumnName(2);
var granteeCol = grantTable.GetResolvedColumnName(3);
var grantOptionCol = grantTable.GetResolvedColumnName(4);
var granterCol = grantTable.GetResolvedColumnName(5);
ITable t1 = grantTable;
// All that match the given object parameter
// It's most likely this will reduce the search by the most so we do
// it first.
t1 = t1.SimpleSelect(queryContext, paramCol, SqlExpressionType.Equal,
SqlExpression.Constant(Field.String(objectName.FullName)));
// The next is a single exhaustive select through the remaining records.
// It finds all grants that match either public or the grantee is the
// user or role, and that match the object type.
// Expression: ("grantee_col" = grantee OR "grantee_col" = 'public')
var granteeCheck = SqlExpression.Equal(SqlExpression.Reference(granteeCol),
SqlExpression.Constant(Field.String(grantee)));
if (withPublic)
{
granteeCheck = SqlExpression.Or(granteeCheck, SqlExpression.Equal(SqlExpression.Reference(granteeCol),
SqlExpression.Constant(Field.String(User.PublicName))));
}
// Expression: ("object_col" = object AND
// ("grantee_col" = grantee OR "grantee_col" = 'public'))
// All that match the given grantee or public and given object
var expr = SqlExpression.And(SqlExpression.Equal(SqlExpression.Reference(objectCol),
SqlExpression.Constant(Field.BigInt((int)objectType))), granteeCheck);
// Are we only searching for grant options?
if (withOption)
{
var grantOptionCheck = SqlExpression.Equal(SqlExpression.Reference(grantOptionCol),
SqlExpression.Constant(Field.BooleanTrue));
expr = SqlExpression.And(expr, grantOptionCheck);
}
t1 = t1.ExhaustiveSelect(queryContext, expr);
// For each grant, merge with the resultant priv object
Privileges privs = Privileges.None;
foreach (var row in t1)
{
var priv = (int)row.GetValue(0).AsBigInt();
privs |= (Privileges)priv;
}
return(privs);
}
private void RevokeAllGrantsFrom(DbObjectType objectType, ObjectName objectName, string revoker, string grantee,
bool withOption = false)
{
using (var query = Session.CreateQuery()) {
var grantTable = query.Access().GetMutableTable(SystemSchema.GrantsTableName);
var objectCol = grantTable.GetResolvedColumnName(1);
var paramCol = grantTable.GetResolvedColumnName(2);
var granteeCol = grantTable.GetResolvedColumnName(3);
var grantOptionCol = grantTable.GetResolvedColumnName(4);
var granterCol = grantTable.GetResolvedColumnName(5);
ITable t1 = grantTable;
// All that match the given object parameter
// It's most likely this will reduce the search by the most so we do
// it first.
t1 = t1.SimpleSelect(query, paramCol, SqlExpressionType.Equal,
SqlExpression.Constant(Field.String(objectName.FullName)));
// The next is a single exhaustive select through the remaining records.
// It finds all grants that match either public or the grantee is the
// user or role, and that match the object type.
// Expression: ("grantee_col" = grantee)
var userCheck = SqlExpression.Equal(SqlExpression.Reference(granteeCol),
SqlExpression.Constant(Field.String(grantee)));
// Expression: ("object_col" = object AND
// "grantee_col" = grantee)
// All that match the given grantee or public and given object
var expr =
SqlExpression.And(
SqlExpression.Equal(SqlExpression.Reference(objectCol),
SqlExpression.Constant(Field.BigInt((int)objectType))), userCheck);
// Are we only searching for grant options?
var grantOptionCheck = SqlExpression.Equal(SqlExpression.Reference(grantOptionCol),
SqlExpression.Constant(Field.Boolean(withOption)));
expr = SqlExpression.And(expr, grantOptionCheck);
// Make sure the granter matches up also
var granterCheck = SqlExpression.Equal(SqlExpression.Reference(granterCol),
SqlExpression.Constant(Field.String(revoker)));
expr = SqlExpression.And(expr, granterCheck);
t1 = t1.ExhaustiveSelect(query, expr);
// Remove these rows from the table
grantTable.Delete(t1);
}
}
public void And_WithLeftAndRightOperands_ReturnsCorrectExpression()
{
var expression = SqlExpression.And(SqlExpression.GreaterThan("Id", 5), SqlExpression.Equal("Name", "Wouter"));
Assert.NotNull(expression);
Assert.Equal(SqlBinaryOperator.And, expression.Operator);
Assert.IsType <SqlBinaryExpression>(expression.Left);
Assert.Equal(SqlBinaryOperator.GreaterThan, ((SqlBinaryExpression)expression.Left).Operator);
Assert.IsType <SqlBinaryExpression>(expression.Right);
Assert.Equal(SqlBinaryOperator.Equal, ((SqlBinaryExpression)expression.Right).Operator);
}
public DTOResponse GetList(PalletRequest request)
{
DTOResponse dtoResponse = new DTOResponse();
try
{
using (IDbConnection dbConn = HelperConnection.GetConnectionFactory().OpenDbConnection())
{
SqlExpression <PalletEntity> expression = dbConn.From <PalletEntity>();
if (!string.IsNullOrEmpty(request.STOCKNO))
{
expression.And(x => x.STOCKNO == request.STOCKNO);
}
if (!string.IsNullOrEmpty(request.GROUPNO))
{
expression.And(x => x.GROUPNO == request.GROUPNO);
}
if (!string.IsNullOrEmpty(request.LOCATIONNO))
{
expression.And(x => x.LOCATIONNO == request.LOCATIONNO);
}
if (!string.IsNullOrEmpty(request.PALLETNO))
{
expression.And(x => x.PALLETNO == request.PALLETNO);
}
if (!string.IsNullOrEmpty(request.STATE))
{
expression.And(x => x.STATE == int.Parse(request.STATE));
}
if (!string.IsNullOrEmpty(request.PLANNO))
{
expression.And(x => x.PLANNO == request.PLANNO);
}
long count = dbConn.Count <PalletEntity>(expression);
List <PalletEntity> entityList = dbConn.Select <PalletEntity>(expression)
.OrderBy(x => x.ID)
.Skip((request.page - 1) * request.limit)
.Take(request.limit)
.ToList();
dtoResponse.TotalCount = count;
dtoResponse.ResultObject = (object)entityList;
dtoResponse.IsSuccess = true;
dtoResponse.MessageText = "查询操作成功！";
return(dtoResponse);
}
}
catch (Exception ex)
{
dtoResponse.IsSuccess = false;
dtoResponse.MessageText = ex.Message;
logger.Error((object)ex);
return(dtoResponse);
}
}
private DTOResponse GetHistoryList(TaskRequest request)
{
using (IDbConnection dbConn = HelperConnection.GetConnectionFactory().OpenDbConnection())
{
DTOResponse dtoResponse = new DTOResponse();
SqlExpression <ZHISTaskEntity> expression = dbConn.From <ZHISTaskEntity>();
if (!string.IsNullOrEmpty(request.TASKTCLASS) && request.TASKTCLASS != "0")
{
expression.And((Expression <Func <ZHISTaskEntity, bool> >)(x => x.TASKTCLASS == request.TASKTCLASS));
}
if (!string.IsNullOrEmpty(request.PALLETNO))
{
expression.And((Expression <Func <ZHISTaskEntity, bool> >)(x => x.PALLETNO == request.PALLETNO));
}
if (!string.IsNullOrEmpty(request.RelateNo))
{
expression.And((Expression <Func <ZHISTaskEntity, bool> >)(x => x.RELATENO == request.RelateNo));
}
if (!string.IsNullOrEmpty(request.SourceNo))
{
expression.And((Expression <Func <ZHISTaskEntity, bool> >)(x => x.SOURCE02 == request.SourceNo));
}
if (!string.IsNullOrEmpty(request.DescNo))
{
expression.And((Expression <Func <ZHISTaskEntity, bool> >)(x => x.DESTINATION02 == request.DescNo));
}
long num = dbConn.Count <ZHISTaskEntity>(expression);
List <ZHISTaskEntity> list = dbConn.Select <ZHISTaskEntity>(expression).OrderByDescending <ZHISTaskEntity, long>((Func <ZHISTaskEntity, long>)(x => x.ID)).Skip <ZHISTaskEntity>((request.page - 1) * request.limit).Take <ZHISTaskEntity>(request.limit).ToList <ZHISTaskEntity>();
dtoResponse.TotalCount = num;
dtoResponse.ResultObject = (object)list;
dtoResponse.IsSuccess = true;
dtoResponse.MessageText = "查询操作成功！";
return(dtoResponse);
}
}
public void ToSql_WithSqlSelectWithWhereClause_ReturnsSql()
{
SqlTable people = new SqlTable("People", "p");
const string expected = "SELECT [p].[Id], [p].[FirstName], [p].[LastName] FROM [People] [p] WHERE ([p].[Age] > 18) AND ([p].[Age] < 40) ORDER BY [p].[LastName], [p].[FirstName], [p].[CreatedOn] DESC";
var actual = Sql
.Select(people + "Id", people + "FirstName", people + "LastName")
.From(people)
.Where(SqlExpression.And(SqlExpression.GreaterThan(people + "Age", 18), SqlExpression.LessThan(people + "Age", 40)))
.OrderBy(people + "LastName")
.OrderBy(people + "FirstName")
.OrderBy(people + "CreatedOn", SqlSortOrder.Descending)
.Go()
.ToSql();
Assert.Equal(expected, actual);
}
public void ToSql_WithMySqlDialect_WithSqlSelectWithWhereClause_ReturnsSql()
{
SqlTable people = new SqlTable("People", "p");
const string expected = "SELECT `p`.`Id`, `p`.`FirstName`, `p`.`LastName` FROM `People` `p` WHERE (`p`.`Age` > 18) AND (`p`.`Age` < 40) ORDER BY `p`.`LastName`, `p`.`FirstName`, `p`.`CreatedOn` DESC";
var actual = Sql
.Select(people + "Id", people + "FirstName", people + "LastName")
.From(people)
.Where(SqlExpression.And(SqlExpression.GreaterThan(people + "Age", 18), SqlExpression.LessThan(people + "Age", 40)))
.OrderBy(people + "LastName")
.OrderBy(people + "FirstName")
.OrderBy(people + "CreatedOn", SqlSortOrder.Descending)
.Go()
.ToSql(new MySqlDialect());
Assert.Equal(expected, actual);
}
private static SqlExpression VisitBetweenExpression(SqlBetweenExpressionNode expressionNode)
{
var testExp = Build(expressionNode.Expression);
var minValue = Build(expressionNode.MinValue);
var maxValue = Build(expressionNode.MaxValue);
var smallerExp = SqlExpression.SmallerOrEqualThan(testExp, maxValue);
var greaterExp = SqlExpression.GreaterOrEqualThan(testExp, minValue);
SqlExpression exp = SqlExpression.And(smallerExp, greaterExp);
if (expressionNode.Not)
{
exp = SqlExpression.Not(exp);
}
return(exp);
}
private static void AddSingleColumnPlan(IList <SingleColumnPlan> list, TablePlan table, ObjectName columnName, ObjectName uniqueName, SqlExpression[] expParts, SqlExpressionType op)
{
var exp = SqlExpression.Binary(expParts[0], op, expParts[1]);
// Is this source in the list already?
foreach (var existingPlan in list)
{
if (existingPlan.TablePlan == table &&
(columnName == null || existingPlan.ColumnName.Equals(columnName)))
{
// Append to end of current expression
existingPlan.SetSource(columnName, SqlExpression.And(existingPlan.Expression, exp));
return;
}
}
// Didn't find so make a new entry in the list.
list.Add(new SingleColumnPlan(table, columnName, uniqueName, exp));
}
public DTOResponse GetDetailList(PalletRequest request)
{
DTOResponse dtoResponse = new DTOResponse();
try
{
using (IDbConnection dbConn = HelperConnection.GetConnectionFactory().OpenDbConnection())
{
SqlExpression <PalletDetailEntity> expression = dbConn.From <PalletDetailEntity>();
if (request.PALLETID > -1)
{
expression.And(x => x.PALLETID == request.PALLETID);
}
long count = dbConn.Count <PalletDetailEntity>(expression);
List <PalletDetailEntity> entityList = dbConn.Select <PalletDetailEntity>(expression)
.OrderBy(x => x.ID)
.Skip((request.page - 1) * request.limit)
.Take(request.limit)
.ToList();
dtoResponse.TotalCount = count;
dtoResponse.ResultObject = (object)entityList;
dtoResponse.IsSuccess = true;
dtoResponse.MessageText = "查询操作成功！";
return(dtoResponse);
}
}
catch (Exception ex)
{
dtoResponse.IsSuccess = false;
dtoResponse.MessageText = ex.Message;
logger.Error((object)ex);
return(dtoResponse);
}
}
//public object Get(Details details)
//{
// Models.Recording recording = _recordingRepository.GetById(details.Id);
// if (recording.Restricted && !UserSession.IsAuthenticated)
// return HttpResult.Redirect("/Login".AddQueryParam("redirect", Request.AbsoluteUri));
// return new DetailsResponse {Recording = recording};
//}
//public object Get(Delete delete)
//{
// Models.Recording recording = _recordingRepository.GetById(delete.Id);
// if (recording.Restricted && !UserSession.IsAuthenticated) return HttpResult.Redirect("/Login".AddQueryParam("redirect", Request.AbsoluteUri));
// return new DeleteResponse {Recording = recording};
//}
//[Authenticate]
//[RequiredRole(RoleNames.Admin)]
//public object Post(Delete delete)
//{
// _recordingRepository.Delete(delete.Id);
// return this.Redirect("/Recording");
//}
public object Get(SpeakerDetails speaker)
{
if (speaker.EscapedFragment == null && !Request.RawUrl.IsNormalizedUrl())
{
return(this.RedirectPermanently(Request.RawUrl.ToNormalizedUrl()));
}
if (speaker.EscapedFragment != null && speaker.EscapedFragment.StartsWith("/"))
{
speaker.Album = speaker.EscapedFragment.Substring(1);
}
var includeRestricted = UserSession.IsAuthenticated;
NameInfo nameInfo = speaker.Name.ToNameInfo();
using (IDbConnection db = _dbConnectionFactory.Open())
{
DbSpeaker dbSpeaker = db.Single <DbSpeaker>(s => s.FirstName == nameInfo.FirstName && s.LastName == nameInfo.LastName && !s.Deleted);
if (dbSpeaker == null)
{
throw HttpError.NotFound("Speaker not found");
}
SqlExpression <DbRecording> albumIdQuery =
db.From <DbRecording>()
.Join <DbRecordingSpeaker>((recording, recordingSpeaker) => recording.Id == recordingSpeaker.RecordingId)
.Where <DbRecording, DbRecordingSpeaker>((recording, recordingSpeaker) => !recording.Deleted && recordingSpeaker.SpeakerId == dbSpeaker.Id)
.SelectDistinct(rs => rs.AlbumId);
SqlExpression <DbSpeaker> query = db.From <DbSpeaker>()
.Join <DbRecordingSpeaker>((sp, recordingSpeaker) => sp.Id == recordingSpeaker.SpeakerId)
.Join <DbRecordingSpeaker, DbRecording>((recordingSpeaker, recording) => recordingSpeaker.RecordingId == recording.Id)
.Join <DbRecording, DbAlbum>((recording, album) => recording.AlbumId == album.Id)
.Where <DbSpeaker, DbAlbum>((sp, album) => !sp.Deleted && !album.Deleted && Sql.In(album.Id, albumIdQuery))
.OrderBy <DbAlbum>(a => a.Name)
.ThenBy <DbRecording>(r => r.Track);
if (!includeRestricted)
{
query.And <DbAlbum>(a => !a.Restricted);
}
List <Tuple <DbSpeaker, DbRecordingSpeaker, DbRecording, DbAlbum> > recordings = db.SelectMulti <DbSpeaker, DbRecordingSpeaker, DbRecording, DbAlbum>(query);
Dictionary <long, DbAlbum> albums = recordings.DistinctBy(r => r.Item4.Id).Select(r => r.Item4).ToDictionary(a => a.Id);
ILookup <long, Tuple <DbSpeaker, DbRecordingSpeaker, DbRecording, DbAlbum> > albumLookup = recordings.ToLookup(r => r.Item4.Id);
ILookup <long, Tuple <DbSpeaker, DbRecordingSpeaker, DbRecording, DbAlbum> > speakers = recordings.ToLookup(r => r.Item3.Id);
List <AlbumInfo> albumInfos =
(from g in albumLookup
let album = albums[g.Key]
select new AlbumInfo
{
Uid = album.Uid,
Name = album.Name,
Description = album.Description,
AlbumSpeakers = GetSpeakers(g),
HasAlbumArt = album.AlbumArtPath != null,
Recordings = g.DistinctBy(r => r.Item3.Id)
.OrderBy(r => r.Item3.Track)
.Select(r => new RecordingInfo
{
Uid = r.Item3.Uid,
Title = r.Item3.Title,
Track = r.Item3.Track,
Year = r.Item3.Year,
Comment = r.Item3.Comment,
Speakers = speakers[r.Item3.Id]
.DistinctBy(rs => rs.Item1.Id)
.Select(rs => rs.Item1.ToSpeakerInfo())
.ToList()
}).ToList(),
Attachments = album.AttachmentPaths
.Select((attachment, index) => new AttachmentInfo
{
Name =
_fileSystem.Path.GetFileName(attachment),
Index = index
}).ToList()
}).ToList();
return(new SpeakerDetailsResponse
{
Uid = dbSpeaker.Uid,
Speaker = speaker.Name,
Albums = albumInfos.ToList(),
Speakers = _recordingRepository.GetSpeakers(includeRestricted).Select(s => s.ToSpeakerInfo(sp => sp.FullName == speaker.Name)).ToList(),
SelectedAlbum = albumInfos.FirstOrDefault(a => a.Name == (speaker.Album ?? speaker.EscapedFragment)),
HasPhoto = dbSpeaker.PhotoPath != null,
Description = dbSpeaker.Description
});
}
}
public DTOResponse GetList(BasicRequest request)
{
DTOResponse dtoResponse = new DTOResponse();
try
{
using (IDbConnection dbConn = HelperConnection.GetConnectionFactory().OpenDbConnection())
{
if (request.BasicType == "BUNIESSTYPE")
{
SqlExpression <BasicBuniessTypeEntity> expression = dbConn.From <BasicBuniessTypeEntity>();
expression.And(x => x.BUNIESSTYPESTATE == "1");
List <BasicBuniessTypeEntity> entityList = dbConn.Select <BasicBuniessTypeEntity>(expression)
.OrderBy(x => x.BUNIESSTYPENO)
.ToList <BasicBuniessTypeEntity>();
dtoResponse.ResultObject = (object)entityList;
dtoResponse.IsSuccess = true;
dtoResponse.MessageText = "查询操作成功！";
return(dtoResponse);
}
else if (request.BasicType == "PRODUCER")
{
SqlExpression <BasicProducerEntity> expression = dbConn.From <BasicProducerEntity>();
expression.And(x => x.PRODUCERSTATE == "1");
if (!string.IsNullOrEmpty(request.Code))
{
expression.And(x => x.PRODUCERNO == request.Code);
}
if (!string.IsNullOrEmpty(request.Name))
{
expression.And(x => x.PRODUCERNAME.Contains(request.Name));
}
if (!string.IsNullOrEmpty(request.Subdesc))
{
expression.And(x => x.PRODUCERSUBDESC.Contains(request.Subdesc));
}
List <BasicProducerEntity> entityList = dbConn.Select <BasicProducerEntity>(expression)
.OrderBy(x => x.ID)
.ToList <BasicProducerEntity>();
dtoResponse.ResultObject = (object)entityList;
dtoResponse.IsSuccess = true;
dtoResponse.MessageText = "查询操作成功！";
return(dtoResponse);
}
else if (request.BasicType == "VENDOR")
{
SqlExpression <BasicVendorEntity> expression = dbConn.From <BasicVendorEntity>();
expression.And(x => x.VENDORSTATE == "1");
if (!string.IsNullOrEmpty(request.Code))
{
expression.And(x => x.VENDORNO == request.Code);
}
if (!string.IsNullOrEmpty(request.Name))
{
expression.And(x => x.VENDORNAME.Contains(request.Name));
}
if (!string.IsNullOrEmpty(request.Subdesc))
{
expression.And(x => x.VENDORSUBDESC.Contains(request.Subdesc));
}
List <BasicVendorEntity> entityList = dbConn.Select <BasicVendorEntity>(expression)
.OrderBy(x => x.ID)
.ToList <BasicVendorEntity>();
dtoResponse.ResultObject = (object)entityList;
dtoResponse.IsSuccess = true;
dtoResponse.MessageText = "查询操作成功！";
return(dtoResponse);
}
else if (request.BasicType == "STOCK")
{
SqlExpression <LocStockEntity> expression = dbConn.From <LocStockEntity>();
expression.And(x => x.STOCKSTATE == "1" && x.STOCKTYPE == "1");
List <LocStockEntity> entityList = dbConn.Select <LocStockEntity>(expression)
.OrderBy(x => x.ID)
.ToList <LocStockEntity>();
dtoResponse.ResultObject = (object)entityList;
dtoResponse.IsSuccess = true;
dtoResponse.MessageText = "查询操作成功！";
return(dtoResponse);
}
else
{
dtoResponse.ResultObject = null;
dtoResponse.IsSuccess = false;
dtoResponse.MessageText = "查询" + request.BasicType + "失败！";
return(dtoResponse);
}
}
}
catch (Exception ex)
{
dtoResponse.IsSuccess = false;
dtoResponse.MessageText = ex.Message;
logger.Error((object)ex);
return(dtoResponse);
}
}
public object Any(SpeakerFeedRequest speakerFeedRequest)
{
bool includeRestricted = UserSession.IsAuthenticated;
const int limit = 50;
XNamespace itunes = "http://www.itunes.com/dtds/podcast-1.0.dtd";
XNamespace atom = "http://www.w3.org/2005/Atom";
string siteUrl = Request.GetApplicationUrl();
string logoUrl = $"{siteUrl}/content/images/podcast_logo.png";
const string subscribe = @"<h4>Abonner i iTunes</h4>
<ol>
<li>Start iTunes</li>
<li>Klikk Fil - Abonner på podcast (File - Subscribe to Podcast). (Trykk Alt-F for å få frem menyen i Windows.)
<li>Lim inn lenken til denne siden, og klikk OK</li>
</ol>";
using (IDbConnection db = _dbConnectionFactory.Open())
{
DbSpeaker speaker = null;
if (speakerFeedRequest.Speaker != null)
{
NameInfo nameInfo = speakerFeedRequest.Speaker?.ToNameInfo();
speaker = db.Single <DbSpeaker>(s => s.FirstName == nameInfo.FirstName && s.LastName == nameInfo.LastName && !s.Deleted);
}
string description = string.Empty;
SqlExpression <DbSpeaker> query = db.From <DbSpeaker>()
.Join <DbRecordingSpeaker>((sp, recordingSpeaker) => sp.Id == recordingSpeaker.SpeakerId)
.Join <DbRecordingSpeaker, DbRecording>((recordingSpeaker, recording) =>
recordingSpeaker.RecordingId == recording.Id)
.Join <DbRecording, DbAlbum>((recording, album) => recording.AlbumId == album.Id)
.Where <DbRecording, DbSpeaker>((recording, sp) => !recording.Deleted && !sp.Deleted)
.Take(limit);
if (speaker != null)
{
query.And <DbSpeaker>(s => s.Id == speaker.Id);
}
else
{
query.OrderByDescending <DbRecording>(r => r.CreatedOn);
}
if (!includeRestricted)
{
query.And <DbRecording>(r => r.Restricted == false);
}
List <Tuple <DbSpeaker, DbRecordingSpeaker, DbRecording, DbAlbum> > speakerInfo =
db.SelectMulti <DbSpeaker, DbRecordingSpeaker, DbRecording, DbAlbum>(query);
ILookup <long, Tuple <DbSpeaker, DbRecordingSpeaker, DbRecording, DbAlbum> > recordingLookup = speakerInfo.ToLookup(s => s.Item3.Id);
Dictionary <long, DbAlbum> albums = speakerInfo.DistinctBy(r => r.Item4.Id).Select(r => r.Item4).ToDictionary(a => a.Id);
IEnumerable <XElement> items =
from recording in speakerInfo.DistinctBy(r => r.Item3.Id).Select(r => r.Item3.ToRecording())
let album = albums[recording.AlbumId]
let speakerName = recordingLookup[recording.Id]
.Select(s => s.Item1).DistinctBy(s => s.Id)
.Select(s => s.ToSpeaker().FullName)
.ToSeparatedString('/')
let trackDescription =
$"{recording.Comment}{(recording.Year != null ? " ({0})".Fmt(recording.Year) : string.Empty)}".PadRight(1, '-')
let tagInfo = GetTag(recording)
let fileInfo = _fileSystem.FileInfo.FromFileName(recording.Path)
let titleSuffix =
tagInfo.Tag.TrackCount > 1
? " ({0}/{1})".Fmt(tagInfo.Tag.Track, tagInfo.Tag.TrackCount)
: string.Empty
let url =
$"{siteUrl}/Recording/Stream/{recording.Uid:N}/{fileInfo.Name.UrlEncode20()}"
let guid =
$"{siteUrl}/Recording/Stream/{recording.Uid:N}{(speakerFeedRequest.Speaker == null ? "/top50" : string.Empty)}"
select new XElement("item",
new XElement("title",
$"{album.Name}: {recording.Title}{titleSuffix}"
),
new XElement(itunes + "author", speakerName
),
new XElement(itunes + "subtitle",
$"{(speakerFeedRequest.Speaker == null ? $"{speakerName}: " : string.Empty)}{album.Name}"
),
new XElement(itunes + "summary", new XCData(trackDescription)
),
new XElement("description",
$"{trackDescription}{Environment.NewLine}{subscribe}"
),
new XElement(itunes + "image",
new XAttribute("href",
logoUrl)
),
new XElement("enclosure",
new XAttribute("url", url),
new XAttribute("length", fileInfo.Length),
new XAttribute("type", "audio/mpeg")
),
new XElement("guid", guid
),
new XElement("pubDate", recording.CreatedOn.ToString("r")
),
new XElement(itunes + "duration", tagInfo.Properties.Duration.ToString(@"hh\:mm\:ss")
),
new XElement(itunes + "explicit", "no"
)
);
string title = speakerFeedRequest.Speaker ?? $"Siste {limit}";
string link = speakerFeedRequest.Speaker != null
? $"{siteUrl}/Recording/Speaker/{speakerFeedRequest.Speaker.UrlEncode20()}"
: siteUrl;
string selfUrl = $"{siteUrl}{Request.RawUrl}";
List <XElement> categories = new List <XElement>();
for (int i = 0; i < Configuration.PodcastCategories.Count; i++)
{
categories.Add(new XElement(itunes + "category",
new XAttribute("text", Configuration.PodcastCategories[i]),
new XElement(itunes + "category",
new XAttribute("text", Configuration.PodcastSubcategories[i])
)
));
}
XElement element =
new XElement("rss",
new XAttribute(XNamespace.Xmlns + "itunes", "http://www.itunes.com/dtds/podcast-1.0.dtd"),
new XAttribute(XNamespace.Xmlns + "atom", "http://www.w3.org/2005/Atom"),
new XAttribute("version", "2.0"),
new XElement("channel",
new XElement(atom + "link",
new XAttribute("href", selfUrl),
new XAttribute("rel", "self"),
new XAttribute("type", "application/rss+xml"),
new XAttribute(XNamespace.Xmlns + "atom", atom)
),
new XElement("title", $"{Configuration.SiteName}: {title}"
),
new XElement("link", link
),
new XElement("language", "no"
),
new XElement("copyright", Configuration.CompanyName
),
new XElement(itunes + "subtitle", Configuration.Slogan,
new XAttribute(XNamespace.Xmlns + "itunes", itunes)
),
new XElement(itunes + "author", speakerFeedRequest.Speaker ?? Configuration.CompanyName
),
new XElement(itunes + "summary",
new XCData(WebUtility.HtmlDecode(Configuration.MetaDescription))
),
new XElement("description", description
),
new XElement(itunes + "owner",
new XElement(itunes + "name", Configuration.CompanyName
),
new XElement(itunes + "email", Configuration.CompanyEmail
)
),
new XElement(itunes + "image",
new XAttribute("href", logoUrl)
),
categories,
new XElement(itunes + "explicit", "no"
),
items
)
);
string podcastFeed = $@"<?xml version=""1.0"" encoding=""UTF-8""?>{Environment.NewLine}{element}";
return(new HttpResult(podcastFeed, "application/rss+xml"));
}
}
private DTOResponse GetList(TaskRequest request)
{
using (IDbConnection dbConn = HelperConnection.GetConnectionFactory().OpenDbConnection())
{
DTOResponse dtoResponse = new DTOResponse();
SqlExpression <TaskEntity> expression = dbConn.From <TaskEntity>();
DateTime dt1;
DateTime dt2;
bool s = ProjToolHelper.TryParseDatetimerange(request.datetimerange, out dt1, out dt2);
if (s)
{
//按时间段查询
expression.And(x => x.CREATEDATETIME >= dt1 && x.CREATEDATETIME <= dt2);
}
if (!string.IsNullOrEmpty(request.TASKTCLASS) && request.TASKTCLASS != "0")
{
expression.And((Expression <Func <TaskEntity, bool> >)(x => x.TASKTCLASS == request.TASKTCLASS));
}
if (!string.IsNullOrEmpty(request.ID) && request.ID != "0")
{
expression.And((Expression <Func <TaskEntity, bool> >)(x => x.ID == long.Parse(request.ID)));
}
if (!string.IsNullOrEmpty(request.TASKNO))
{
expression.And((Expression <Func <TaskEntity, bool> >)(x => x.TASKNO == request.TASKNO));
}
if (!string.IsNullOrEmpty(request.PALLETID))
{
expression.And((Expression <Func <TaskEntity, bool> >)(x => x.PALLETID == request.PALLETID));
}
if (!string.IsNullOrEmpty(request.PALLETNO))
{
expression.And((Expression <Func <TaskEntity, bool> >)(x => x.PALLETNO == request.PALLETNO));
}
if (!string.IsNullOrEmpty(request.RelateNo))
{
expression.And((Expression <Func <TaskEntity, bool> >)(x => x.RELATENO == request.RelateNo));
}
if (!string.IsNullOrEmpty(request.SourceNo))
{
expression.And((Expression <Func <TaskEntity, bool> >)(x => x.SOURCE02 == request.SourceNo));
}
if (!string.IsNullOrEmpty(request.DescNo))
{
expression.And((Expression <Func <TaskEntity, bool> >)(x => x.DESTINATION02 == request.DescNo));
}
if (!string.IsNullOrEmpty(request.TASKSTATE))
{
if (request.TASKSTATE == "notover")
{
List <string> status = new List <string>()
{
"99", "4"
};
expression.And(x => !status.Contains(x.TASKSTATE));
}
else
{
expression.And((Expression <Func <TaskEntity, bool> >)(x => x.TASKSTATE == request.TASKSTATE));
}
}
long num = dbConn.Count <TaskEntity>(expression);
List <TaskEntity> list = dbConn.Select <TaskEntity>(expression).OrderByDescending <TaskEntity, long>((Func <TaskEntity, long>)(x => x.ID)).Skip <TaskEntity>((request.page - 1) * request.limit).Take <TaskEntity>(request.limit).ToList <TaskEntity>();
dtoResponse.TotalCount = num;
dtoResponse.ResultObject = (object)list;
dtoResponse.IsSuccess = true;
dtoResponse.MessageText = "查询操作成功！";
return(dtoResponse);
}
}
public void Init()
{
try
{
string devHotName = ConfigurationManager.AppSettings["DEVHOSTNAME"];//开发计算机
string hostName = HelperEnvironmentSetting.GetHostName();
string appName = ConfigurationManager.AppSettings["APPNAME"];
if (string.IsNullOrEmpty(appName))
{
appName = AppDomain.CurrentDomain.FriendlyName.Replace(".vshost", "");
}
string responservername = null;
string responservernameBydev = null;
responservername = hostName + "&" + appName;
responservernameBydev = devHotName + "&" + appName;
System.Console.WriteLine("responservername：" + responservername);
System.Console.WriteLine("devHotName：" + responservername);
List <JobEntity> jobEntityList = new List <JobEntity>();
using (IDbConnection dbConn = HelperConnection.GetConnectionFactory().OpenDbConnection())
{
SqlExpression <JobEntity> expression = dbConn.From <JobEntity>();
expression.And(x => x.STATE == "1");//加载启用状态的定时器
if (devHotName == hostName)
{
expression.And(x => x.RESPONSESVRNAMEBYDEV == responservernameBydev);
}
else
{
expression.And(x => x.RESPONSESVRNAME == responservername);
}
jobEntityList = dbConn.Select <JobEntity>(expression).OrderBy <JobEntity, int>((Func <JobEntity, int>)(x => x.JOBID)).ToList <JobEntity>();
if (jobEntityList == null || jobEntityList.Count == 0)
{
this.logger.Info((object)"启动定时服务失败，未查询到相关数据！");
}
else
{
this.logger.Info((object)(responservername + ",加载" + jobEntityList.Count.ToString() + "个JOB"));
this.scheduler = this.schedulerFactory.GetScheduler();
this.scheduler.Clear();
foreach (JobEntity jobEntity in jobEntityList)
{
Type type = Type.GetType(jobEntity.CLASSFULLNAME);
if (type == (Type)null)
{
this.logger.Info((object)("无法获取：" + jobEntity.CLASSFULLNAME));
}
else
{
int jobid = jobEntity.JOBID;
IJobDetail jobDetail = (IJobDetail) new JobDetailImpl(jobid.ToString() + "-" + jobEntity.JOBDESC, "CIMS.Server", type, true, true);
TriggerBuilder triggerBuilder = TriggerBuilder.Create();
jobid = jobEntity.JOBID;
string key = jobid.ToString() + "-" + jobEntity.JOBTRIGGER;
ITrigger trigger = triggerBuilder.WithIdentity(key).WithCronSchedule(jobEntity.JOBTRIGGERPARA).Build();
jobDetail.JobDataMap.Put("BASE_JOB", (object)jobEntity);
jobDetail.JobDataMap.Put("JobKey", jobEntity.JOBID);
JobDataMap jobDataMap = jobDetail.JobDataMap;
jobid = jobEntity.JOBID;
string str2 = jobid.ToString() + "-" + jobEntity.JOBTRIGGER;
jobDataMap.Put("TriggerKey", str2);
this.scheduler.ScheduleJob(jobDetail, trigger);
logger.Info(jobEntity.JOBID + "创建成功！");
}
}
this.logger.Info((object)(responservername + ",启动了" + jobEntityList.Count.ToString() + "个JOB"));
this.scheduler.Start();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
this.logger.Info((object)ex.ToString());
this.logger.Error((object)ex.ToString());
}
}
public void And_WithNullRightOperand_ThrowsArgumentNull()
{
Assert.Throws <ArgumentNullException>(() => SqlExpression.And(SqlExpression.Equal("Id", 5), null));
}
public void And_WithNullLeftOperand_ThrowsArgumentNull()
{
Assert.Throws <ArgumentNullException>(() => SqlExpression.And(null, null));
}
virtual public async Task <Entity> GetSingleWhereAsync(string Property, object Value, SqlExpression <Entity> query = null, string cacheKey = null)
{
var cacheContainer = Cache.Get <Dictionary <string, Entity> >(CACHE_CONTAINER_GET_SINGLE_WHERE);
if (cacheContainer == null)
{
cacheContainer = new Dictionary <string, Entity>();
}
if (string.IsNullOrWhiteSpace(cacheKey))
{
cacheKey = CACHE_GET_SINGLE_WHERE;
}
cacheKey += $"_{ Property}_{Value}";
if (query == null)
{
query = Db.From <Entity>();
}
if (!string.IsNullOrWhiteSpace(Property) && Value != null)
{
string sPropertyName = Property;
PropertyInfo oProp = typeof(Entity).GetProperty(sPropertyName);
Type tProp = oProp.PropertyType;
//Nullable properties have to be treated differently, since we
// use their underlying property to set the value in the object
if (tProp.IsGenericType &&
tProp.GetGenericTypeDefinition().Equals(typeof(Nullable <>)))
{
//Get the underlying type property instead of the nullable generic
tProp = new NullableConverter(oProp.PropertyType).UnderlyingType;
}
var value = Expression.Constant(Convert.ChangeType(Value, tProp));
if (tProp == typeof(string))
{
query.Where($"{query.SqlColumn(Property)} like '%{value.Value}%'");
}
else
{
query.Where($"{query.SqlColumn(Property)} = {value.Value}");
}
}
foreach (var queryParam in Request.QueryString.AllKeys)
{
string queryParamValue = Request.QueryString[queryParam];
if (IsValidParam(queryParam) && IsValidJSValue(queryParamValue))
{
cacheKey += $"_{queryParam}={queryParamValue}";
string sPropertyName = queryParam;
PropertyInfo oProp = typeof(Entity).GetProperty(sPropertyName);
if (oProp == null)
{
continue; //Ignore non-existing properties, they could be just different query parameters.
}
Type tProp = oProp.PropertyType;
//Nullable properties have to be treated differently, since we
// use their underlying property to set the value in the object
if (tProp.IsGenericType &&
tProp.GetGenericTypeDefinition().Equals(typeof(Nullable <>)))
{
//Get the underlying type property instead of the nullable generic
tProp = new NullableConverter(oProp.PropertyType).UnderlyingType;
}
ParameterExpression entityParameter = Expression.Parameter(typeof(Entity), "entityParameter");
Expression childProperty = Expression.PropertyOrField(entityParameter, sPropertyName);
var value = Expression.Constant(Convert.ChangeType(queryParamValue, tProp));
// let's perform the conversion only if we really need it
var converted = value.Type != childProperty.Type
? Expression.Convert(value, childProperty.Type)
: (Expression)value;
Expression <Func <Entity, bool> > lambda;
if (tProp == typeof(String))
{
MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
lambda = Expression.Lambda <Func <Entity, bool> >(Expression.Call(converted, method, childProperty), entityParameter);
}
else
{
Expression comparison = Expression.Equal(childProperty, converted);
lambda = Expression.Lambda <Func <Entity, bool> >(comparison, entityParameter);
}
query.And(lambda);
}
}
cacheContainer.TryGetValue(cacheKey, out Entity cache);
if (cache != null)
{
return(AdapterOut(cache)[0]);
}
query = OnGetSingle(query);
var entity = (await Db.LoadSelectAsync(query)).FirstOrDefault();
if (entity != null)
{
AdapterOut(entity);
}
var response = entity;
cacheContainer[cacheKey] = response;
Cache.Replace(CACHE_CONTAINER_GET_SINGLE_WHERE, cacheContainer);
return(response);
}
private static SqlExpression Accept(IDbCommandStruct @struct, Expression expression, ref int pIndex)
{
if (expression is BinaryExpression binary)
{
SqlExpression lexpre = Accept(@struct, binary.Left, ref pIndex);
SqlExpression rexpre = Accept(@struct, binary.Right, ref pIndex);
switch (binary.NodeType)
{
case ExpressionType.AndAlso:
return(SqlExpression.And(lexpre, rexpre));
case ExpressionType.OrElse:
return(SqlExpression.Or(lexpre, rexpre));
case ExpressionType.GreaterThan:
return(SqlExpression.GreaterThan(lexpre, rexpre));
case ExpressionType.GreaterThanOrEqual:
return(SqlExpression.GreaterThanOrEqual(lexpre, rexpre));
case ExpressionType.Equal:
switch (rexpre)
{
case SqlConstantExpression constant when constant.Value is null:
return(SqlExpression.Is(lexpre, rexpre));
default:
return(SqlExpression.Equal(lexpre, rexpre));
}
case ExpressionType.NotEqual:
switch (rexpre)
{
case SqlConstantExpression constant when constant.Value is null:
return(SqlExpression.Not(SqlExpression.Is(lexpre, rexpre)));
default:
return(SqlExpression.NotEqual(lexpre, rexpre));
}
case ExpressionType.LessThanOrEqual:
return(SqlExpression.LessThanOrEqual(lexpre, rexpre));
case ExpressionType.LessThan:
return(SqlExpression.LessThan(lexpre, rexpre));
default:
throw new ArgumentException(nameof(binary.NodeType));
}
}
else if (expression is MemberExpression member)
{
if (member.Expression.NodeType == ExpressionType.Parameter)
{
return(SqlExpression.Member(member.Member.Name));
}
else
{
var name = "var" + pIndex++;
var parent = member.Expression?.GetValue();
var value = member.Member.GetValue(parent);
@struct.AddParameter(name, value);
return(SqlExpression.Paramter(name));
}
}
else if (expression is UnaryExpression unary)
{
var node = Accept(@struct, unary.Operand, ref pIndex);
if (unary.NodeType == ExpressionType.Not)
{
return(SqlExpression.Not(node));
}
else if (unary.NodeType == ExpressionType.Negate)
{
return(SqlExpression.Negate(node));
}
else if (unary.NodeType == ExpressionType.Convert)
{
return(node);
}
else
{
throw new ArgumentException();
}
}
else if (expression is ConstantExpression constant)
{
if (constant.Value == null)
{
return(SqlExpression.Constant(null));
}
var name = "var" + pIndex++;
@struct.AddParameter(name, constant.Value);
return(SqlExpression.Paramter(name));
}
else if (expression is NewExpression newexpr)
{
var objParams = new object[newexpr.Arguments.Count];
for (int i = 0; i < objParams.Length; i++)
{
objParams[i] = newexpr.Arguments[i].GetValue();
}
var obj = newexpr.Constructor.Invoke(objParams);
return(SqlExpression.Constant(obj));
}
else if (expression is MethodCallExpression methodCall)
{
if (methodCall.Method.Name == "Contains" && methodCall.Method.ReturnType == typeof(bool) && methodCall.Arguments.Count == 1)
{
var parameters = methodCall.Method.GetParameters();
if (parameters.Length == 1)
{
var collection = GetValue(methodCall.Object) as IEnumerable;
var expressions = new List <SqlExpression>();
foreach (var value in collection)
{
expressions.Add(SqlExpression.Constant(value));
}
int inIndex = 0;
var argument = Accept(@struct, methodCall.Arguments[0], ref inIndex);
var newArray = SqlExpression.NewArray(expressions);
return(SqlExpression.In(argument, newArray));
}
}
}
throw new ArgumentException();
}
public DTOResponse GetListLocGroup(LocationRequest request)
{
DTOResponse dtoResponse = new DTOResponse();
try
{
using (IDbConnection dbConn = HelperConnection.GetConnectionFactory().OpenDbConnection())
{
SqlExpression <LocGroupEntity> expression = dbConn.From <LocGroupEntity>();
if (!string.IsNullOrEmpty(request.LOCATIONTYPE) && request.LOCATIONTYPE != "0")
{
expression.And((x => x.GROUPTYPE == request.LOCATIONTYPE));
}
if (!string.IsNullOrEmpty(request.GROUPNO) && request.GROUPNO != "0")
{
expression.And((x => x.GROUPNO.Contains(request.GROUPNO)));
}
if (!string.IsNullOrEmpty(request.AREANO) && request.AREANO != "0")
{
expression.And((x => x.AREANO == request.AREANO));
}
if (!string.IsNullOrEmpty(request.USESTATE))
{
expression.And(x => x.USESTATE == int.Parse(request.USESTATE));
}
if (!string.IsNullOrEmpty(request.GROUPSTATE))
{
expression.And(x => x.GROUPSTATE == int.Parse(request.GROUPSTATE));
}
if (!string.IsNullOrEmpty(request.AREANO) && request.STOCKNO != "0")
{
expression.And(x => x.STOCKNO == request.STOCKNO);
}
if (request.ROWNO_MIN > 0 && request.ROWNO_MAX > 0)
{
expression.And(x => x.ROWNO >= request.ROWNO_MIN && x.ROWNO <= request.ROWNO_MAX);
}
long count = dbConn.Count <LocGroupEntity>(expression);
List <LocGroupEntity> listResult = dbConn.Select <LocGroupEntity>(expression)
.OrderBy(x => x.ID)
.Skip <LocGroupEntity>((request.page - 1) * request.limit)
.Take <LocGroupEntity>(request.limit)
.ToList <LocGroupEntity>();
dtoResponse.TotalCount = count;
dtoResponse.ResultObject = (object)listResult;
dtoResponse.IsSuccess = true;
dtoResponse.MessageText = "查询操作成功！";
return(dtoResponse);
}
}
catch (Exception ex)
{
dtoResponse.IsSuccess = false;
dtoResponse.MessageText = ex.Message;
logger.Error((object)ex);
return(dtoResponse);
}
}
public SqlExp <T> And(string sqlFilter, params object[] filterParams)
{
Exp.And(sqlFilter, filterParams);
return(this);
}