// GET: Vehicles
public ActionResult SearchResult(string sort, QueryObj queryObj)
{
var query = db.Vehicles.Where(v => true);
if (queryObj.Type != null)
{
query = query.Where(v => v.Type == queryObj.Type);
}
if (queryObj.RegNr != null)
{
query = query.Where(v => v.RegNr.ToLower().StartsWith(queryObj.RegNr.ToLower()));
}
if (queryObj.Color != null)
{
query = query.Where(v => v.Color.ToLower().StartsWith(queryObj.Color.ToLower()));
}
if (queryObj.Brand != null)
{
query = query.Where(v => v.Brand.ToLower().StartsWith(queryObj.Brand.ToLower()));
}
return(View(query.OrderBy(v => v.RegNr).ToList())); // Always sort by regnr
}
public override BaseHelper Get(Dictionary <string, object> wheres, QueryObj query)
{
string showCols;
if (string.IsNullOrEmpty(query.Select) || query.Select == "*")
{
showCols = allColumnNames;
}
else
{
showCols = query.Select;
}
string sortOn = string.IsNullOrEmpty(query.SortOn) ? CurrentTable.PrimaryKey : query.SortOn;
string sortType = string.Compare(query.SortType, "asc", StringComparison.CurrentCultureIgnoreCase) == 0 ? " ASC" : " DESC";
int pz;
StringBuffer sql = "SELECT SQL_CALC_FOUND_ROWS ";
WhereCondition which = MakeWhere(wheres);
string tbWhere = Schema + CurrentTable.Name + which.WhereSql;
//paginated data
if (query.PageSize > 0)
{
pz = query.PageSize;
int offset = pz * query.PageIndex;
sql.AppendFormat("{0} FROM {1} ORDER BY {2} {3} LIMIT {4},{5};", showCols, tbWhere, sortOn, sortType, offset, pz);
sql.AppendFormat("SELECT FOUND_ROWS() ;");
//HttpContext.Current.Response.Write("<hr>" + sql + which.Params.Length + "<hr><hr>");
//return this;
DataSet ds = dac.GetDataSet(sql, which.Params);
int count = ds.Tables[1].Rows[0][0].TryToInt();
DataPage dp = new DataPage {
Table = ds.Tables[0],
Page = MakePageInfo(count, pz, query.PageIndex)
};
return(End(dp, sql));
}
else
{
//sql += DefaultPagesize + " " + showCols + " From " + tbWhere + " Order By " + sortOn + sortType ;
sql.AppendFormat("{0} FROM {1} ORDER BY {2} {3} LIMIT {4}", showCols, tbWhere, sortOn, sortType, DefaultPagesize);
//HttpContext.Current.Response.Write("<hr>" + sql + "<hr><hr>");
//return this;
DataTable dt = dac.GetTableByReader(sql, which.Params);
return(End(dt, sql));
}
}
public async Task <IEnumerable <Attachment> > GetAttachments(TEntity entity)
{
var queryObj = QueryObj.CreateQueryObj(entity);
var elements = await
_attachmentRepository.GetAllListAsync(
a => a.EntityName == queryObj.EntityName && a.EntityId == queryObj.EntityId);
return(elements);
}
// 一个显式的分页查询调用
public DataPage Get(Dictionary <string, object> wheres, QueryObj which, int pageSize = 0, int pageIndex = 0)
{
which.PageIndex = pageIndex;
which.PageSize = pageSize;
var db = initDB();
return(db.Get(wheres, which).PageResult);
}
public ActionResult Index()
{
SharedVM.LogPageHit("Query/Index", User.Identity.Name);
var queryObj = new QueryObj()
{
QuerySql = ""
};
return(View(queryObj));
}
public ActionResult Scripts()
{
SharedVM.LogPageHit("Query/Scripts/", User.Identity.Name);
var queryObj = new QueryObj()
{
SavedQueries = LoadSavedQueries()
};
var vm = new QueryVM();
return(View(queryObj));
}
public Task RemoveAttachment(TEntity entity, int attachmentId)
{
var info = QueryObj.CreateQueryObj(entity);
var attachment = _attachmentRepository.FirstOrDefault(a => a.EntityName == info.EntityName &&
a.EntityId == info.EntityId &&
a.Id == attachmentId);
FileSystemHelper.RemoveFile(attachment.ContentUrl);
return(_attachmentRepository.DeleteAsync(attachment));
}
public LocalizableContent(T entity, TContentType contentType, string lang)
{
var queryObj = QueryObj.CreateQueryObj(entity);
var dtoObj = QueryObj.CreateQueryObj(typeof(TContentType));
EntityId = queryObj.EntityId;
EntityName = queryObj.EntityName;
EntityDtoName = dtoObj.EntityName;
Lang = lang;
SerializeContent(contentType);
}
public async Task <AbpCinotamLocalizableContent> GetLocalizableContent(T entity, string lang, Type dtoType)
{
var queryObj = QueryObj.CreateQueryObj(entity);
var dtoInfo = QueryObj.CreateQueryObj(dtoType);
var content = await Task.FromResult(_localizationContentStore.LocalizableContents
.FirstOrDefault(a => a.EntityId == queryObj.EntityName &&
a.EntityId == queryObj.EntityId &&
a.EntityDtoName == dtoInfo.EntityName && lang == a.Lang));
return(content);
}
/// <summary>
/// 点击NPC处理任务相关
/// </summary>
///<returns>是否有任务需要处理</returns>
private bool ClickNPC_Task()
{
INowTaskState iNowTaskState = GameState.Instance.GetEntity <INowTaskState>();
//先判断是否完成了任务
bool checkNPCTaskResult = iNowTaskState.CheckNowTask(EnumCheckTaskType.NPC, _ClickInteractiveNPCID);
if (checkNPCTaskResult)//如果任务完成了则不用后面的检测了
{
return(false);
}
//如果任务没有完成则需要后面的检测
//接取任务的npcid与点击npcid相同并且存在的任务没有被完成也没有被接取
TaskMap.RunTimeTaskInfo[] runTimeTaskInfos = runtimeTaskData.GetAllToDoList()
.Where(temp => temp.TaskInfoStruct.ReceiveTaskNpcId == _ClickInteractiveNPCID && temp.IsOver == false && temp.IsStart == false)
.ToArray();
//存在主线任务则展开主线(主线任务在这里直接开始)
if (runTimeTaskInfos.Where(temp => temp.TaskInfoStruct.TaskType == TaskMap.Enums.EnumTaskType.Main).Count() > 0)
{
//INowTaskState iNowTaskState = GameState.Instance.GetEntity<INowTaskState>();
iNowTaskState.StartTask = runTimeTaskInfos.Where(temp => temp.TaskInfoStruct.TaskType == TaskMap.Enums.EnumTaskType.Main).First().ID;
return(true);
}
//如果存在支线则展开支线(支线任务存在选择项,在UI处开始)
if (runTimeTaskInfos.Where(temp => temp.TaskInfoStruct.TaskType == TaskMap.Enums.EnumTaskType.Spur).Count() > 0)
{
if (QueryObj != null)
{
QueryObj.SetActive(true);
}
return(true);
}
//如果存在重复任务则展开重复任务(重复任务存在选择项,在UI处开始)
if (runTimeTaskInfos.Where(temp => temp.TaskInfoStruct.TaskType == TaskMap.Enums.EnumTaskType.Repeat).Count() > 0)
{
if (QueryObj != null)
{
QueryObj.SetActive(true);
}
return(true);
}
//如果不存在任何任务则在人物头顶显示文字
if (TalkShowObj != null)
{
try
{
TalkShowObj.SendMessage("ClickInteractiveNPCID");
}
catch
{
Debug.Log("显示人物对话的面板没有ClickInteractiveNPCID函数用来接收消息");
}
}
return(false);
}
/// <summary>
/// Gets the one.
/// </summary>
/// <returns>The one.</returns>
/// <param name="wheres">Wheres.</param>
/// <param name="which">Which.</param>
/// <typeparam name="T">The 1st type parameter.</typeparam>
public T GetOne <T>(Dictionary <string, object> wheres, QueryObj which)
{
var db = initDB();
var rv = db.GetRow(wheres, which);
var row = rv.RowResult;
if (row != null)
{
return(row.Populate <T>());
}
return(default(T));
}
public override BaseHelper GetRow(Dictionary <string, object> wheres, QueryObj query)
{
query.PageSize = 1;
DataTable tb = Get(wheres, query).TableResult;
if (tb.Rows.Count > 0)
{
return(End(tb.Rows[0], string.Empty));
}
return(End(null, string.Empty));
}
private string GetTimeData(string strconn, DateTime etime, DateTime stime)
{
QueryObj query = new QueryObj()
{
etime = etime,
stime = stime
};
string sql = "select * from LineUserSpeak where SpeakDate between @stime and @etime ";
if (string.IsNullOrWhiteSpace(this.Groupid))
{
sql += " and UserId=@Groupid ";
query.Groupid = this.UserId;
}
else
{
sql += " and Groupid=@Groupid ";
query.Groupid = this.Groupid;
}
List <LineUserSpeak> datas = null;
using (var conn = new SQLiteConnection(strconn))
{
conn.Open();
datas = conn.Query <LineUserSpeak>(sql, param: new { stime = query.stime, etime = query.etime, Groupid = query.Groupid }).ToList();
}
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append($"{datas.FirstOrDefault().SpeakDate.Date.ToString("yyyy-MM-dd")} 共有 {datas.Count} 則對話 \r\n ");
var speakName = (from c in datas
select c.UserId
).Distinct();
List <Speaker> speakers = new List <Speaker>();
foreach (var item in speakName)
{
if (!speakers.Any(d => d.Id == item))
{
speakers.Add(new Speaker()
{
Id = item, Name = datas.FirstOrDefault(d => d.UserId == item).DisplayName, CountData = datas.Where(d => d.UserId == item).Count()
});
}
}
speakers.OrderByDescending(d => d.CountData).ToList().ForEach(d =>
{
sb.Append($"{d.Name}：共{d.CountData}則訊息\r\n");
});
return(sb.ToString());
}
public async Task <IEnumerable <AbpCinotamLocalizableContent> > GetLocalizableContent(T entity)
{
var queryObj = QueryObj.CreateQueryObj(entity);
var dtoInfo = QueryObj.CreateQueryObj(typeof(TContentType));
var contents =
await
Task.FromResult(
_localizationContentStore.LocalizableContents.Where(a =>
a.EntityId == queryObj.EntityId &&
a.EntityName == queryObj.EntityName &&
a.EntityDtoName == dtoInfo.EntityName));
return(contents);
}
public ActionResult Scripts(QueryObj queryObj)
{
int queryId = queryObj.QueryId;
SharedVM.LogPageHit("Query/RunSavedQuery/" + queryId, User.Identity.Name);
var dt = new DataTable();
var vm = new QueryVM();
queryObj.QuerySql = vm.GetSavedQuerySql(queryId);
queryObj.QueryDescription = vm.GetSavedQueryDesc(queryId);
try
{
var sqlResults = vm.ExecuteSql(queryObj);
if (sqlResults != null)
{
BuildTableHeader(sqlResults, false, ref dt);
if (sqlResults != null && sqlResults.Any())
{
foreach (var dataRow in sqlResults.ToList())
{
DataRow row = dt.NewRow();
int rowNum = 0;
foreach (var item in dataRow)
{
row[rowNum] = item.Value;
rowNum++;
}
dt.Rows.Add(row);
}
}
queryObj.ResultsDataTable = dt;
}
}
catch (Exception)
{
//Should probably do something with this, but for now just swallowing it
}
queryObj.SavedQueries = LoadSavedQueries();
return(View(queryObj));
}
public static Attachment CreateAttachment <TEntity>
(IHasAttachment <TEntity> hasAttachmentElement)
where TEntity : class
{
var entityInfo = QueryObj.CreateQueryObj(hasAttachmentElement.Entity);
return(new Attachment()
{
Description = hasAttachmentElement.Description,
Active = hasAttachmentElement.Active,
ContentUrl = hasAttachmentElement.ContentUrl,
EntityName = entityInfo.EntityName,
EntityId = entityInfo.EntityId,
StoredInCdn = hasAttachmentElement.StoredInCdn,
Properties = hasAttachmentElement.Properties,
FileName = hasAttachmentElement.FileName
});
}
public IEnumerable <dynamic> ExecuteSql(QueryObj queryObj)
{
IEnumerable <dynamic> results = null;
using (IDbConnection myConn = new SqlConnection(dbConn))
{
myConn.Open();
using (var tran = myConn.BeginTransaction(IsolationLevel.ReadUncommitted))
{
try
{
results = myConn.Query(queryObj.QuerySql, null, tran);
tran.Commit();
}
catch { }
}
}
return(results);
}
public override BaseHelper GetField(Dictionary <string, object> wheres, QueryObj query, string whichColumn = null)
{
string showCols = whichColumn ?? CurrentTable.PrimaryKey;
if (!CurrentTable.Columns.ContainsKey(whichColumn))
{
return(End(string.Empty, string.Empty));
}
StringBuffer orderBySql = string.IsNullOrEmpty(query.SortOn) ? string.Empty : (" Order By " + query.SortOn);
WhereCondition which = MakeWhere(wheres);
var sql = new StringBuffer(); //"Select " + showCols + " FROM " + CurrentTable.Name + which.WhereSql + orderBySql + " ;";
sql.AppendFormat("SELECT {0} FROM {1} {2} {3};", showCols, CurrentTable.Name, which.WhereSql, orderBySql);
//HttpContext.Current.Response.Write("<hr>"+sql + "<hr><hr>");
//return this;
object objVal = dac.ExecuteScalar(sql, which.Params);
return(End(objVal, sql));
}
public override BaseHelper Get(Dictionary <string, object> wheres, QueryObj query)
{
string showCols;
if (string.IsNullOrEmpty(query.Select) || query.Select == "*")
{
showCols = allColumnNames;
}
else
{
showCols = query.Select;
}
string sortOn = string.IsNullOrEmpty(query.SortOn) ? CurrentTable.PrimaryKey : query.SortOn;
string sortType = string.Compare(query.SortType, "asc", StringComparison.CurrentCultureIgnoreCase) == 0 ? " ASC" : " DESC";
int pz;
StringBuffer sql = "SELECT TOP ";
WhereCondition which = MakeWhere(wheres);
string tbWhere = Schema + CurrentTable.Name + which.WhereSql;
//paginated data
if (query.PageSize > 0)
{
pz = query.PageSize;
int offset = pz * query.PageIndex;
//sql += pz + " * FROM (Select " + showCols + ", ROW_NUMBER() OVER (ORDER BY " + sortOn + sortType + ") AS TabRowNumber FROM " ;
//sql += tbWhere + ") as TA WHERE TA.TabRowNumber>" + offset + ";" ;
sql.AppendFormat("{0} * FROM (SELECT {1},ROW_NUMBER() OVER (ORDER BY {2} {3}) AS TabRowNumber FROM {4}) as TA WHERE TA.TabRowNumber>{5};", pz, showCols, sortOn, sortType, tbWhere, offset);
//sql += "Select @rowCount=Count(" + sortOn + ") FROM " + tbWhere ;
sql.AppendFormat("SELECT @rowCount=Count({0}) FROM {1};", sortOn, tbWhere);
SqlParameter rowCount = new SqlParameter("@rowCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
SqlParameter[] pms = new SqlParameter[which.Params.Length + 1];
which.Params.CopyTo(pms, 0);
pms[which.Params.Length] = rowCount;
//HttpContext.Current.Response.Write("<hr>" + sql + which.Params.Length + "<hr><hr>");
//return this;
DataTable dt = dac.GetTable(sql, pms);
int count = (int)rowCount.Value;
DataPage dp = new DataPage {
Table = dt,
Page = MakePageInfo(count, pz, query.PageIndex)
};
return(End(dp, sql));
}
else
{
//sql += DefaultPagesize + " " + showCols + " From " + tbWhere + " Order By " + sortOn + sortType ;
sql.AppendFormat("{0} {1} FROM {2} ORDER BY {3} {4}", DefaultPagesize, showCols, tbWhere, sortOn, sortType);
//HttpContext.Current.Response.Write("<hr>" + sql + "<hr><hr>");
//return this;
DataTable dt = dac.GetTableByReader(sql, which.Params);
return(End(dt, sql));
}
}
public override BaseHelper Get(Dictionary <string, object> wheres)
{
QueryObj query = new QueryObj();
return(Get(wheres, query));
}
public override BaseHelper Get(Dictionary<string , object> wheres, QueryObj query)
{
string showCols;
if (string.IsNullOrEmpty(query.Select) || query.Select == "*") {
showCols = allColumnNames;
}
else {
showCols = query.Select;
}
string sortOn = string.IsNullOrEmpty(query.SortOn) ? CurrentTable.PrimaryKey : query.SortOn;
string sortType = string.Compare(query.SortType, "asc" , StringComparison.CurrentCultureIgnoreCase)==0 ? " ASC" : " DESC";
int pz;
StringBuffer sql = "SELECT TOP " ;
WhereCondition which = MakeWhere(wheres);
string tbWhere = Schema + CurrentTable.Name + which.WhereSql ;
//paginated data
if (query.PageSize > 0) {
pz = query.PageSize;
int offset = pz * query.PageIndex;
//sql += pz + " * FROM (Select " + showCols + ", ROW_NUMBER() OVER (ORDER BY " + sortOn + sortType + ") AS TabRowNumber FROM " ;
//sql += tbWhere + ") as TA WHERE TA.TabRowNumber>" + offset + ";" ;
sql.AppendFormat("{0} * FROM (SELECT {1},ROW_NUMBER() OVER (ORDER BY {2} {3}) AS TabRowNumber FROM {4}) as TA WHERE TA.TabRowNumber>{5};" , pz , showCols , sortOn , sortType , tbWhere , offset) ;
//sql += "Select @rowCount=Count(" + sortOn + ") FROM " + tbWhere ;
sql.AppendFormat("SELECT @rowCount=Count({0}) FROM {1};" , sortOn , tbWhere);
SqlParameter rowCount = new SqlParameter("@rowCount", SqlDbType.Int) {
Direction = ParameterDirection.Output
};
SqlParameter[] pms = new SqlParameter[which.Params.Length + 1];
which.Params.CopyTo(pms, 0);
pms[which.Params.Length] = rowCount;
//HttpContext.Current.Response.Write("<hr>" + sql + which.Params.Length + "<hr><hr>");
//return this;
DataTable dt = dac.GetTable(sql, pms);
int count = (int)rowCount.Value;
DataPage dp = new DataPage {
Table = dt,
Page = MakePageInfo(count, pz, query.PageIndex)
};
return End(dp, sql);
}
else {
//sql += DefaultPagesize + " " + showCols + " From " + tbWhere + " Order By " + sortOn + sortType ;
sql.AppendFormat("{0} {1} FROM {2} ORDER BY {3} {4}" , DefaultPagesize , showCols , tbWhere , sortOn , sortType);
//HttpContext.Current.Response.Write("<hr>" + sql + "<hr><hr>");
//return this;
DataTable dt = dac.GetTableByReader(sql, which.Params);
return End(dt, sql);
}
}
public abstract BaseHelper GetRow(Dictionary<string , object> wheres, QueryObj query);
public DataTable Get(Dictionary <string, object> wheres, QueryObj which)
{
var db = initDB();
return(db.Get(wheres, which).TableResult);
}
// public ActionResult SearchVehicles([Bind(Include = "Id,Type,RegNr,Color,CheckInTime,Tyres,Brand,Model")] Vehicle vehicle) {
public ActionResult SearchVehicles(QueryObj queryObj)
{
// return RedirectToAction("Index");
return(RedirectToAction("SearchResult", queryObj));
}
public List <T> Get <T>(Dictionary <string, object> wheres, QueryObj which)
{
var tb = Get(wheres, which);
return(tb.Populate <T>());
}
public override BaseHelper Get(Dictionary<string , object> wheres)
{
QueryObj query = new QueryObj();
return Get(wheres, query);
}
/// <summary>
/// Validates the state of the query for saving
/// </summary>
/// <returns>True: Proceed with writing</returns>
private bool CanSaveQuery()
{
return(QueryObj.isValid());
}
public override BaseHelper Get(Dictionary<string , object> wheres, QueryObj query)
{
string showCols;
if (string.IsNullOrEmpty(query.Select) || query.Select == "*") {
showCols = allColumnNames;
} else {
showCols = query.Select;
}
string sortOn = string.IsNullOrEmpty(query.SortOn) ? CurrentTable.PrimaryKey : query.SortOn;
string sortType = string.Compare(query.SortType, "asc", StringComparison.CurrentCultureIgnoreCase) == 0 ? " ASC" : " DESC";
int pz;
StringBuffer sql = "SELECT SQL_CALC_FOUND_ROWS ";
WhereCondition which = MakeWhere(wheres);
string tbWhere = Schema + CurrentTable.Name + which.WhereSql;
//paginated data
if (query.PageSize > 0) {
pz = query.PageSize;
int offset = pz * query.PageIndex;
sql.AppendFormat("{0} FROM {1} ORDER BY {2} {3} LIMIT {4},{5};", showCols, tbWhere, sortOn, sortType, offset, pz);
sql.AppendFormat("SELECT FOUND_ROWS() ;");
//HttpContext.Current.Response.Write("<hr>" + sql + which.Params.Length + "<hr><hr>");
//return this;
DataSet ds = dac.GetDataSet(sql, which.Params);
int count = ds.Tables[1].Rows[0][0].TryToInt();
DataPage dp = new DataPage {
Table = ds.Tables[0],
Page = MakePageInfo(count, pz, query.PageIndex)
};
return End(dp, sql);
} else {
//sql += DefaultPagesize + " " + showCols + " From " + tbWhere + " Order By " + sortOn + sortType ;
sql.AppendFormat("{0} FROM {1} ORDER BY {2} {3} LIMIT {4}", showCols, tbWhere, sortOn, sortType, DefaultPagesize);
//HttpContext.Current.Response.Write("<hr>" + sql + "<hr><hr>");
//return this;
DataTable dt = dac.GetTableByReader(sql, which.Params);
return End(dt, sql);
}
}
public abstract BaseHelper GetField(Dictionary<string , object> wheres, QueryObj query, string whichColumn = null);
public abstract BaseHelper GetField(Dictionary <string, object> wheres, QueryObj query, string whichColumn = null);
public override BaseHelper GetRow(Dictionary<string , object> wheres, QueryObj query)
{
query.PageSize = 1;
DataTable tb = Get(wheres, query).TableResult;
if (tb.Rows.Count > 0)
return End(tb.Rows[0], string.Empty);
return End(null, string.Empty);
}
public override BaseHelper GetField(Dictionary<string , object> wheres, QueryObj query, string whichColumn = null)
{
string showCols = whichColumn ?? CurrentTable.PrimaryKey;
if (!CurrentTable.Columns.ContainsKey(whichColumn))
return End(string.Empty, string.Empty);
StringBuffer orderBySql = string.IsNullOrEmpty(query.SortOn) ? string.Empty : (" Order By " + query.SortOn);
WhereCondition which = MakeWhere(wheres);
var sql = new StringBuffer();//"Select " + showCols + " FROM " + CurrentTable.Name + which.WhereSql + orderBySql + " ;";
sql.AppendFormat("SELECT {0} FROM {1} {2} {3};" , showCols , CurrentTable.Name, which.WhereSql , orderBySql);
//HttpContext.Current.Response.Write("<hr>"+sql + "<hr><hr>");
//return this;
object objVal = dac.ExecuteScalar(sql, which.Params);
return End(objVal, sql);
}
public abstract BaseHelper GetRow(Dictionary <string, object> wheres, QueryObj query);
