public async Task <ServiceResult <User> > FindUserBySignInName(string userSignInName)
{
var filter = new QueryOption(
"$filter"
, $"identities/any(x:x/issuer eq '{_options.TenantIssuerName}' and x/issuerAssignedId eq '{userSignInName}')"
);
var userList = await _graphClient.Users
.Request(new List <QueryOption>() { filter })
.Select(_options.UserFieldSelection)
.GetAsync();
if (!userList.Any())
{
return(ServiceResult <User> .FromError("user not found"));
}
if (userList.Count > 1)
{
return(ServiceResult <User> .FromError("too many users"));
}
var user = userList.Single();
if (user.VerifyAccess(_orgId, _options))
{
return(ServiceResult <User> .FromResult(user));
}
return(ServiceResult <User> .FromError("user has no org id or malformed"));
}
/// <summary>
/// Generates the query.
/// </summary>
/// <param name="queryOptions">The query options.</param>
/// <param name="sqlParams">The SQL parameters.</param>
/// <returns></returns>
private static string GenerateQuery(QueryOption queryOptions, out Dictionary <string, object> sqlParams, List <string> dateTypeFilters = null)
{
sqlParams = null;
var limit = GetNumberOfRecords(queryOptions.NumberofRecords);
var selectStatement = GenerateTableSelectTable(queryOptions.SelectColumns, queryOptions.TableName, limit, queryOptions.IsChartQuery);
var whereStatement = GetWhereStatement(queryOptions.WhereCriteria, dateTypeFilters);
var groupStatement = string.Empty;
if (queryOptions.IsChartQuery && queryOptions.GroupByColumns != null && queryOptions.GroupByColumns.Any())
{
groupStatement = GenerateGroupByStatement(queryOptions.GroupByColumns);
}
if (!string.IsNullOrWhiteSpace(whereStatement))
{
sqlParams = GenerateWhereParams(queryOptions.WhereCriteria);
}
var orderByStatement = GetOrderByStatement(queryOptions.OrderByColumn, queryOptions.IsOrderByDesc);
return(string.Concat(selectStatement, whereStatement, groupStatement, orderByStatement));
}
public async Task <IActionResult> CalendarView(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "calendarView")] HttpRequest req)
{
try
{
var graphClient = GetGraphClient(configuration.GraphV1);
var user = req.Query["user"];
var startDateTime = new QueryOption("startDateTime", req.Query["start"]);
var endDateTime = new QueryOption("endDateTime", req.Query["end"]);
var options = new List <Option> {
startDateTime, endDateTime
};
if (!string.IsNullOrWhiteSpace(req.Query["timeZone"]))
{
var timeZone = new HeaderOption("Prefer", $"outlook.timezone=\"{req.Query["timeZone"]}\"");
options.Add(timeZone);
}
var result = await graphClient.Users[user].CalendarView.Request(options).GetAsync();
var events = result.Select(e => e.ToEvent(ChangeType.None, user)).ToList();
return(new OkObjectResult(Result.Ok(events)));
}
catch (Exception e)
{
var error = $"{e.Message}\n\r{e.StackTrace}";
log.Error(error);
return(new OkObjectResult(Result.Fail <List <Event> >(error)));
}
}
public static ReportResult RunQuery(QueryOption queryOption)
{
ReportResult rptResult = new ReportResult();
Dictionary <string, object> sqlParams;
var query = GenerateQuery(queryOption, out sqlParams, queryOption.DateTypeFilters);
var data = ExecuteTextTable(query, queryOption.TableName, queryOption.ConnectionString, sqlParams, queryOption.ReportTimeOut);
rptResult.ResultData = data;
if (queryOption.IsGetDataColumns && data != null)
{
var listColumnNames = new List <string>();
foreach (DataColumn column in data.Columns)
{
listColumnNames.Add(column.ColumnName);
}
rptResult.ResultColums = listColumnNames;
}
return(rptResult);
}
/// <summary>
/// 資料個數取得
/// </summary>
/// <param name="option">資料查詢參數</param>
/// <param name="condition">查詢條件</param>
/// <param name="parms">其他傳入參數</param>
/// <returns></returns>
public int GetCount(QueryOption option, T condition = null, params string[] parms)
{
var context = QueryContextFactory.CreateInstance <T>(option);
DataQuery(context.Main, option, condition, parms);
return(context.GetCount());
}
public static QueryOption Page(this QueryOption queryOption, int page)
{
var option = queryOption.Clone();
option.CurrentPage = page;
return(option);
}
/// <summary>
/// 監控項目資訊取得
/// </summary>
/// <param name="detector">偵測器</param>
/// <param name="deviceSn">設備編號</param>
/// <param name="targetName">監控項目名稱</param>
/// <returns></returns>
public Target GetTarget(Detector detector, string deviceSn, string targetName)
{
var option = new QueryOption
{
Relation = true,
Plan = new QueryPlan {
Join = "AlarmConditions"
}
};
var condition = new Target
{
DEVICE_SN = deviceSn,
TARGET_NAME = targetName
};
var target = _dao.Get(option, condition);
if (target.OPERATOR_TYPE == null)
{
return(DefaultTarget(detector, condition));
}
return(target);
}
public IHttpActionResult GetGroupDevices(string gorupId)
{
try
{
//User Info
var login = GetUserInfo();
//查詢參數
var opt = new QueryOption {
Relation = true, Plan = new QueryPlan()
{
Join = "Detail"
}
};
var condition = new GroupService {
GROUP_SN = gorupId
};
var bll = GenericBusinessFactory.CreateInstance <GroupService>();
var output = bll.GetList(opt, login, condition);
return(Ok(output));
}
catch (Exception ex)
{
return(Content(HttpStatusCode.InternalServerError, ex.Message));
}
}
/// <summary>
/// 建立排序標題
/// </summary>
/// <param name="helper"></param>
/// <param name="actionName">Action方法</param>
/// <param name="query">查詢參數</param>
/// <param name="propertyName">屬性名稱</param>
/// <param name="displayName">顯示名稱</param>
/// <returns></returns>
public static MvcHtmlString BuildSortLink(this HtmlHelper helper,
string actionName, QueryOption query, string propertyName, string displayName)
{
var urlHelper = new UrlHelper(helper.ViewContext.RequestContext);
var isCurrentField = propertyName == query.SortField;
StringBuilder classBuilder = new StringBuilder("glyphicon glyphicon-sort");
if (isCurrentField)
{
classBuilder.Append("-by-alphabet");
if (query.SortOrder == SortOrder.DESC)
{
classBuilder.Append("-alt");
}
}
var outputQuery = query.Clone();
outputQuery.SortField = propertyName;
outputQuery.SortOrder = query.SortOrder == SortOrder.ASC ? SortOrder.DESC : SortOrder.ASC;
return(new MvcHtmlString(
$"<a href=\"{urlHelper.Action(actionName, outputQuery)}\">" +
$"{displayName} <span class=\"{classBuilder}\"></span>" +
"</a>"));
}
public List <Row> show()
{
bind(null);
// TBD: route to optimizer here
QueryOption queryOpt = new QueryOption();
physicPlan_ = logicPlan_.DirectToPhysical(queryOpt);
logicPlan_.ResolveColumnOrdinal(outputs_);
// actual execution
var finalplan = new PhysicCollect(physicPlan_);
physicPlan_ = finalplan;
var context = new ExecContext(queryOpt);
Console.WriteLine(physicPlan_.Explain());
finalplan.ValidateThis();
var code = finalplan.Open(context);
code += finalplan.Exec(null);
code += finalplan.Close();
return(finalplan.rows_);
}
public async Task It_gets_the_sites_drives()
{
try
{
// Specify the search query parameter.
var searchQuery = new QueryOption("search", "Office 365 Demos");
var options = new List <QueryOption>();
options.Add(searchQuery);
// Call the Microsoft Graph API. Expecting a single search entry from the tenant.
var siteSearchResults = await graphClient.Sites.Request(options).GetAsync();
Assert.True(siteSearchResults.Count > 0);
// Call the Microsoft Graph API. Get the drives collection page.
SiteDrivesCollectionPage drives = (SiteDrivesCollectionPage)graphClient.Sites[siteSearchResults[0].Id].Drives.Request().GetAsync().Result;
Assert.True(drives.Count > 0);
}
catch (Microsoft.Graph.ServiceException e)
{
Assert.False(true, $"Something happened, check out a trace. Error code: {e.Error.Code}");
}
}
public async Async.Task SharePointGetDocumentLibraries()
{
try
{
// Specify the search query parameter.
var searchQuery = new QueryOption("search", "Office 365 Demos");
var options = new List <QueryOption>();
options.Add(searchQuery);
// Call the Microsoft Graph API. Expecting a single search entry from the tenant.
var siteSearchResults = await graphClient.Sites.Request(options).GetAsync();
Assert.IsTrue(siteSearchResults.Count > 0, "Expected at least one search result. Got zero. Check test data.");
// Call the Microsoft Graph API. Get the drives collection page.
SiteDrivesCollectionPage drives = (SiteDrivesCollectionPage)graphClient.Sites[siteSearchResults[0].Id].Drives.Request().GetAsync().Result;
Assert.IsTrue(drives.Count > 0, "Expected at least one drive result. Got zero. Check test data.");
}
catch (Microsoft.Graph.ServiceException e)
{
Assert.Fail("Something happened, check out a trace. Error code: {0}", e.Error.Code);
}
}
public IActionResult Index(QueryOption option)
{
//Console.Clear();
var pro = _productRepository.GetAll(option, "Category");
return(View(pro));
}
/// <summary>
/// License key 設置
/// </summary>
/// <returns></returns>
private void SetLicense()
{
var dao = GenericDataAccessFactory.CreateInstance <LicenseConfig>();
var option = new QueryOption();
_licenseKey = dao.Get(option).LICENSE_KEY;
}
// todo: this will have to be paged & searchable - too much potential to be too large
// todo: cache this
public async Task <IEnumerable <AppRoleAssignment> > GetAppRoleAssignmentsByResource(Guid resourceId)
{
var app = await GetResourceUserCanAdminister(resourceId);
if (app == null)
{
return(new List <AppRoleAssignment>());
}
var assignments = await _graphClient.ServicePrincipals[app.ResourceId.ToString()]
.AppRoleAssignedTo
.Request()
.Select(_options.AppRoleAssignmentFieldSelection)
.GetAsync();
// get users in org --- eeeeeeeek
var filter = new QueryOption("$filter", $"{_options.OrgIdExtensionName} eq '{_orgId}'");
var users = await _graphClient.Users
.Request(new List <QueryOption>() { filter })
.Select(_options.UserFieldSelection)
.GetAsync()
;
// filter assignments to users in org
var userIdList = assignments.Select(x => x.PrincipalId.ToString()).Intersect(users.Select(x => x.Id));
// todo: resolve role values
return(assignments.Where(x => users.Any(u => u.Id == x.PrincipalId.ToString())));
}
/// <summary>
/// Injects an asyncronous waiter which will inject the provided response for one potential future request.
/// It will check every 5 milliseconds to see if a request item is inserted in the queue, and in that case respond to it.
/// </summary>
public static void InjectResponse <TRequest, TResponse>(TResponse injectedResponse)
{
var service = GetServiceKey(typeof(TRequest), typeof(TResponse));
// Get queue item:
while (true)
{
var latestCondition = QueryOption.OrderByDescending <IIntegrationQueueItem>(x => x.RequestDate);
var item = Database.Find <IIntegrationQueueItem>(x => x.IntegrationService == service && x.ResponseDate == null, latestCondition);
if (item != null)
{
item = item.Clone() as IIntegrationQueueItem;
item.Response = JsonConvert.SerializeObject(injectedResponse);
item.ResponseDate = LocalTime.Now;
Database.Save(item);
return;
}
Thread.Sleep(5);
}
}
//
// GET: /Backend/Article/
public ActionResult Index(QueryOption <ArticleViewModel> queryOption)
{
var article = db.Article.Include(a => a.Category)
.Select(x => new ArticleViewModel
{
ID = x.ID,
CategoryID = x.CategoryID,
Category = x.Category,
Subject = x.Subject,
Summary = x.Summary,
ContentText = x.ContentText,
IsPublish = x.IsPublish,
ViewCount = x.ViewCount,
PublishDate = x.PublishDate,
CreateUser = x.CreateUser,
CreateDate = x.CreateDate,
UpdateUser = x.UpdateUser,
UpdateDate = x.UpdateDate
});
if (!string.IsNullOrEmpty(queryOption.Keyword))
{
article = article.Where(x => x.Subject.Contains(queryOption.Keyword) ||
x.ContentText.Contains(queryOption.Keyword));
}
queryOption.SetSource(article);
return(View(queryOption));
}
// GET: backend/Admin
public ActionResult Index(int?page, string q, string column = "Date", EnumSort order = EnumSort.Ascending)
{
//分頁套件： Install-Package PagedList.Mvc
var pageIndex = page.HasValue ? page.Value < 1 ? 1 : page.Value : 1;
var pageSize = 10;
//為了範例最簡化，因此直接在 Controller 操作 DB ，實務上請盡量避免
var source = _AccountBookSvc.Lookup().AsQueryable();//_dbContext.AccountBook.AsQueryable();
if (string.IsNullOrWhiteSpace(q) == false)
{
// 只是單純示範搜尋條件應該如何累加
var category = Convert.ToInt32(q);
source = source.Where(d => d.Category == category);
}
var result = new QueryOption <AccountBook.Models.AccountBook>
{
Order = order,
Column = column,
Page = pageIndex,
PageSize = pageSize,
Keyword = q
};
//利用 SetSource 將資料塞入（塞進去之前不能將資料讀出來）
result.SetSource(source);
ViewData.Model = result;
return(View());
//return View(_AccountBookSvc.Lookup());
}
static void TestTpcds_LoadData()
{
var files = Directory.GetFiles(@"../../../tpcds", "*.sql");
string[] norun = { "q1", "q10" };
Tpcds.CreateTables();
Tpcds.LoadTables("tiny");
Tpcds.AnalyzeTables();
// make sure all queries can generate phase one opt plan
QueryOption option = new QueryOption();
option.optimize_.enable_subquery_unnest_ = true;
option.optimize_.remove_from_ = false;
option.optimize_.use_memo_ = false;
foreach (var v in files)
{
char[] splits = { '.', '/', '\\' };
var tokens = v.Split(splits, StringSplitOptions.RemoveEmptyEntries);
if (norun.Contains(tokens[1]))
{
continue;
}
var sql = File.ReadAllText(v);
var result = SQLStatement.ExecSQL(sql, out string phyplan, out _, option);
}
}
static void RunSQLFromFile(string filename)
{
// Not working as expected, need to investigate.
var option = new QueryOption();
option.explain_.mode_ = ExplainMode.full;
option.optimize_.use_memo_ = true;
option.explain_.show_estCost_ = false;
string allquery = File.ReadAllText(filename);
string[] listquery = allquery.Split(';');
List <string> listoutput = new List <string>();
int linenum = 0;
for (int i = 0; i < listquery.Length; ++i)
{
linenum = i + 1;
string sql = listquery[i].Trim();
if (sql.Length <= 0)
{
continue;
}
else if (sql.StartsWith("--"))
{
continue;
}
try
{
Console.WriteLine(sql);
string outline = linenum.ToString();
outline += ": " + sql + "\n";
listoutput.Add(outline);
var result = SQLStatement.ExecSQL(sql, out string physicplan, out string error_, option);
if (physicplan != null)
{
Console.WriteLine(physicplan);
listoutput.Add(physicplan);
}
if (result != null)
{
Console.WriteLine(result);
listoutput.Add(result.ToString());
}
}
catch (Exception e)
{
Console.WriteLine("SQL: " + sql + "\nEXCEPTION: " + e + "\n");
continue;
}
}
string alloutput = string.Join('\n', listoutput);
string outfile = filename + ".out";
File.WriteAllText(outfile, alloutput);
}
private async Task LoadFilesAsync(string driveItemId, int pageIndex = 0)
{
IsDetailPaneVisible = false;
HideDetailsPane();
if (!string.IsNullOrEmpty(_driveId))
{
try
{
_cancelLoadFile.Cancel(false);
_cancelLoadFile.Dispose();
_cancelLoadFile = new CancellationTokenSource();
_list.Items.Clear();
VisualStateManager.GoToState(this, NavStatesFolderReadonly, false);
QueryOption queryOption = new QueryOption("$top", PageSize.ToString());
await GraphService.TryLoginAsync();
GraphServiceClient graphServiceClient = GraphService.GraphProvider;
Task <IDriveItemChildrenCollectionPage> taskFiles = graphServiceClient.Drives[_driveId].Items[driveItemId].Children.Request(new List <Option> {
queryOption
}).GetAsync(_cancelLoadFile.Token);
IDriveItemChildrenCollectionPage files = await taskFiles;
if (!taskFiles.IsCanceled)
{
_list.Items.Clear();
foreach (DriveItem file in files)
{
_list.Items.Add(file);
}
_nextPageRequest = files.NextPageRequest;
HasMore = _nextPageRequest != null;
VisualStateManager.GoToState(this, NavStatesFolderReadonly, false);
_pathVisualState = NavStatesFolderReadonly;
if (_driveItemPath.Count > 1)
{
IDriveItemPermissionsCollectionPage permissions = await graphServiceClient.Drives[_driveId].Items[driveItemId].Permissions.Request().GetAsync();
foreach (Permission permission in permissions)
{
if (permission.Roles.Contains("write") || permission.Roles.Contains("owner"))
{
VisualStateManager.GoToState(this, NavStatesFolderEdit, false);
_pathVisualState = NavStatesFolderEdit;
break;
}
}
}
else
{
_pathVisualState = NavStatesFolderEdit;
VisualStateManager.GoToState(this, NavStatesFolderEdit, false);
}
}
}
catch (Exception)
{
}
}
}
public async Task <Beta.Message[]> GetGroup(string search)
{
var graphClient = GetAuthenticatedClient();
var searchQuery = new QueryOption("search", search);
var messages = await graphClient.Me.MailFolders.Inbox.Messages.Request(new List <Option>() { searchQuery }).GetAsync();
return(messages.Take(10).ToArray());
}
/// <summary>
/// 實體集合取得
/// </summary>
/// <param name="option">資料查詢參數</param>
/// <param name="condition">查詢條件</param>
/// <param name="parms">其他傳入參數</param>
/// <returns></returns>
public IEnumerable <T> GetList(QueryOption option, T condition = null, params string[] parms)
{
var context = QueryContextFactory.CreateInstance <T>(option);
DataQuery(context.Main, option, condition, parms);
DataPagerSort(context.Main, option);
return(context.GetEntities());
}
protected QueryConfiguration()
{
PropertyInfo[] props = this.GetType().GetProperties();
foreach (var prop in props)
{
QueryOption option = prop.GetValue(this) as QueryOption;
properties.Add(option);
option.SortChanged += SortChangedHandler;
}
}
public async Task <IEnumerable <User> > GetUsers()
{
var filter = new QueryOption("$filter", $"{_options.OrgIdExtensionName} eq '{_orgId}'");
var users = await _graphClient.Users
.Request(new List <QueryOption>() { filter })
.Select(_options.UserFieldSelection)
.GetAsync();
return(users.AsEnumerable());
}
// GET: 客戶銀行資訊
public ActionResult Index(QueryOption query)
{
var 客戶銀行資訊 = blankRepository.Search(query.Keyword)
.OrderBy(query.GetSortString())
.ToPagedList(query.Page, query.GetPageSize());
ViewBag.QueryOption = query;
return(View(客戶銀行資訊));
}
public PagedList <T> GetAll(Expression <Func <T, bool> > predicate, QueryOption option, params object[] includeParams)
{
var query = _dbSet.AsQueryable();
foreach (var item in includeParams)
{
query = query.Include(item.ToString());
}
return(new PagedList <T>(query.Where(predicate), option));
}
public PagedList <T> GetAll(QueryOption option, params object[] includeParams)
{
var query = _dbSet.AsQueryable();
foreach (var item in includeParams)
{
query = query.Include(item.ToString());
}
return(new PagedList <T>(query, option));
}
public static IEnumerable<string> GetQueryOption(this HttpRequestHeaders headers, QueryOption option)
{
IEnumerable<string> values = null;
if (!headers.TryGetValues(QueryOptionNameBuilder.CreateHeaderName(option), out values)) {
return Enumerable.Empty<string>();
}
return values;
}
protected void SortChangedHandler(QueryOption option, bool added)
{
if (added && sortStringOrder.Contains(option.ToSortString()) == false)
{
sortStringOrder.Add(option.ToSortString());
}
else if (!added && sortStringOrder.Contains(option.ToSortString()))
{
sortStringOrder.Remove(option.ToSortString());
}
}
/// <summary>
/// Constructor</summary>
/// <param name="parentNode">Node to receive child</param>
public QueryDomNodeProperty(QueryNode parentNode)
{
QueryTree.Add(parentNode, this);
QueryTree.AddLabel(this, "whose name");
new QueryPropertyNameInput(this, StringQuery.Matches, false);
QueryTree.AddLabel(this, "and whose");
QueryOption stringOrNumberOption = QueryTree.AddOption(this);
new QueryPropertyValueAsStringInput(QueryTree.AddOptionItem(stringOrNumberOption, "string value", 0), StringQuery.All, true);
new QueryPropertyValueAsNumberInput(QueryTree.AddOptionItem(stringOrNumberOption, "numerical value", 0), NumericalQuery.All, true);
}
public AcceptQueryOptionsAttribute(QueryOption options)
{
this.Options = options;
}
/**
* returns the task status which suits the query
*/
public List<TaskStatus> getWorkDetails(String userID, QueryOption option)
{
return _configurationStorageImp.getWorkDetails(userID, option);
}
/**
* This function gets UserID and QueryOption and returns the TaskID,TaskName,
* Status,ElapsedTime of all the tasks that have the given UserID and that they are
* in the state of the given QueryOpiton.
*/
public List<TaskStatus> getWorkDetails(String userID, QueryOption option)
{
// 1. Instantiate the connection
SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString());
SqlDataReader rdr = null;
List<TaskStatus> taskDetailsList = new List<TaskStatus>();
try
{
// 2. Open the connection
conn.Open();
SqlCommand cmd;
String statusString = "";
switch (option)
{
case QueryOption.ActiveTasks:
statusString = "ACTIVE";
break;
case QueryOption.IdleTasks:
statusString = "IDLE";
break;
case QueryOption.WaitingTasks:
statusString = "WAITING";
break;
default:
statusString = "";
break;
}
if (option == QueryOption.AllTasks)
{
cmd = new SqlCommand("SELECT TaskID,TaskName,Status,ElapsedTime from Task WHERE UserID=\'" + userID + "\'", conn);
}
else
{
cmd = new SqlCommand("SELECT TaskID,TaskName,Status,ElapsedTime from Task" +
" WHERE UserID=\'" + userID + "\' AND Status=\'" + statusString + "\'", conn);
}
// get query results
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
TaskStatus taskDetails = new TaskStatus(rdr["TaskID"].ToString());
taskDetails.setTaskElapsedTime((long)rdr["ElapsedTime"]);
taskDetails.setTaskName((String)rdr["TaskName"]);
Status statusOfTask = TaskStatus.convertToStatusObj((String)rdr["Status"]);
taskDetails.setTaskStatus(statusOfTask);
taskDetailsList.Add(taskDetails);
}
}
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}
// 5. Close the connection
if (conn != null)
{
conn.Close();
}
}
return taskDetailsList;
}
public static string CreateHeaderName(QueryOption option)
{
return string.Format("X-Option-{0}", option.ToString());
}
public void WriteQuery(
IDictionary<string, object> query,
string fullName,
int limit = -1,
int skip = 0,
IDictionary<string, object> fields = null,
QueryOption options = QueryOption.None)
{
Write(new MessageHeader { Id = NextId(), Operation = Operation.Query });
Write((int)options);
Write(fullName);
Write(skip);
Write(limit);
Write(query);
if (fields != null)
{
Write(fields);
}
var len = (int)BaseStream.Position;
Seek(0, SeekOrigin.Begin);
Write(len);
WriteStreamTo(_output);
Reset();
}
