private void CheckForValidatedConnections(Object sender, EventArgs e) { Collection <ValidatedPatientConnection> connectionList = HVHelper.GetValidatedConnectionsInPastDays(0); using (var db = new ParticipantContext()) { try { foreach (ValidatedPatientConnection connection in connectionList) { Guid participantId = Guid.Parse(connection.ApplicationPatientId); Participant participant = db.Participants.Find(participantId); if (participant.HasAuthorised) { continue; } participant.HasAuthorised = true; participant.PersonId = connection.PersonId; participant.RecordId = connection.RecordId; db.SaveChanges(); } } catch (Exception ex) { throw new Exception("ERROR: Unable to update Participants information: " + ex.Message.ToString(), ex); } } }
public ActionResult Adhoc(AdhocModel model) { string sql = model.Sql; string currentBuiltin = model.CurrentBuiltin; model = new AdhocModel { Sql = sql, Schema = schema, Builtins = builtins, CurrentBuiltin = currentBuiltin }; try { if (sql.Trim().ToLower().StartsWith("select")) { var results = new List <List <string> >(); var rows = ParticipantContext.Query(sql); bool first = true; foreach (var row in rows) { if (first) { List <string> headers = new List <string>(); foreach (var column in (IDictionary <string, object>)row) // get column names { headers.Add(column.Key); } results.Add(headers); first = false; } var values = new List <string>(); foreach (var column in (IDictionary <string, object>)row) // get column values { string value = column.Value == null ? "" : column.Value.ToString(); values.Add(value); } results.Add(values); } model.Results = results; } else { ParticipantContext.Execute(sql); model.Results.Add(new List <string> { "Query ran successfully." }); } } catch (Exception ex) { model.Exception = ex.ToString(); } return(View(model)); }
public override SequenceDiagramViewModel VisitParticipant([NotNull] ParticipantContext context) { string alias = context.alias?.Text; string name = context.name?.Text; SequenceDiagram.GetOrCreateActor(alias, name); return(SequenceDiagram); }
// workaround for an issue with OAuthWebSecurity.IsAuthenticatedWithOAuth bool IsAuthenticatedWithOAuth() { // next line is commented out because IsAuthenticatedWithOAuth seems to always return false // return OAuthWebSecurity.IsAuthenticatedWithOAuth; // quick and easy workaround var count = (int)ParticipantContext.Scalar("SELECT COUNT(UserId) FROM [webpages_OAuthMembership] WHERE UserId = @0", CurrentUser.Id); return(count > 0); }
public ActionResult Search(string term) { // NOTE: jQuery control requires that parameter be named 'term' and returned values be named 'label' var label = new List <string>(); var items = ParticipantContext.Query("SELECT TOP 5 Title FROM [Product] WHERE Title Like @0 ORDER BY Title", term + "%"); foreach (var item in items) { string l = item.Title.ToString(); label.Add(l.Ellipsify(20)); } return(Json(label, JsonRequestBehavior.AllowGet)); }
void SubmitForm(Object sender, EventArgs e) { string name = NameTextBox.Text; string question = QuestionTextBox.Text; string answer = AnswerTextBox.Text; Guid id = Guid.NewGuid(); string code = HVHelper.CreateParticipantIdentityCode(name, question, answer, id.ToString()); CodeTextBox.Text = code; Participant participant = new Participant { ParticipantId = id, ParticipantName = name, TimeTokenGenerated = DateTime.Now, ParticipantCode = code, SecurityQuestion = question, SecurityAnswer = answer, HasAuthorised = false, PersonId = null, RecordId = null }; using (var db = new ParticipantContext()) { try { db.Participants.Add(participant); db.SaveChanges(); } catch (Exception ex) { throw new Exception("ERROR: Unable to save a new Participant: " + ex.Message.ToString(), ex); } } }
public ParticipantsService(ParticipantContext context) { this.context = context; }
static AdhocController() { // build schema cache (only once) schema = new Dictionary <string, List <AdhocColumn> >(); var sql = @"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME NOT LIKE 'aspnet_%' AND TABLE_NAME NOT LIKE 'webpages_%' AND TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME" ; var tables = ParticipantContext.Query(sql); foreach (var table in tables) { var adhocColumns = new List <AdhocColumn>(); sql = @"SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + table.TABLE_NAME + @"'"; var columns = ParticipantContext.Query(sql); foreach (var column in columns) { adhocColumns.Add(new AdhocColumn { Name = column.COLUMN_NAME, DataType = column.DATA_TYPE }); } schema.Add(table.TABLE_NAME, adhocColumns); } builtins = new List <Builtin>(); builtins.Add(new Builtin { Id = "builtin1", Text = "Record Counts", Description = "Record counts for all tables in database", Sql = @"SELECT Participants = (SELECT COUNT(Id) FROM [Participant]), Carts = (SELECT COUNT(Id) FROM [Cart]), CartItems = (SELECT COUNT(Id) FROM [CartItem]), Errors = (SELECT COUNT(Id) FROM [Error]), Orders= (SELECT COUNT(Id) FROM [Order]), OrderDetails= (SELECT COUNT(Id) FROM [OrderDetail]), Products= (SELECT COUNT(Id) FROM [Product]), Ratings= (SELECT COUNT(Id) FROM [Rating]), Users = (SELECT COUNT(Id) FROM [User])" }); builtins.Add(new Builtin { Id = "builtin2", Text = "Users with Orders", Description = "Display Users and all their orders", Sql = @"SELECT U.FirstName, U.LastName, U.Email, O.OrderDate, '$' + CONVERT(varchar, CAST(O.TotalPrice AS Money), 1) AS Total, O.OrderNumber FROM [Order] O JOIN [User] U ON O.UserId = U.Id ORDER BY O.OrderDate DESC" }); builtins.Add(new Builtin { Id = "builtin3", Text = "Participants with Products", Description = "Display Participants and all their arts works (i.e. products)", Sql = @"SELECT A.FirstName, A.LastName, '$' + CONVERT(VARCHAR, CAST(P.Price AS Money), 1) AS Total, P.Title FROM [Participant] A JOIN [Product] P ON A.Id = P.ParticipantId ORDER BY A.LastName" }); builtins.Add(new Builtin { Id = "builtin4", Text = "Update Statistics", Description = "Recompute totals and summary statistics in all relevant tables", Sql = @"UPDATE A SET TotalProducts = ISNULL(X.Total,0) FROM [Participant] A LEFT OUTER JOIN (SELECT ParticipantId, COUNT(Id) AS Total FROM [Product] GROUP BY ParticipantId) AS X ON A.Id = X.ParticipantId; UPDATE C SET ItemCount = ISNULL(X.Total,0) FROM [Cart] C LEFT OUTER JOIN (SELECT CartId, COUNT(Id) AS Total FROM [CartItem] GROUP BY CartId) AS X ON C.Id = X.CartId; UPDATE O SET ItemCount = ISNULL(X.Total,0) FROM [Order] O LEFT OUTER JOIN (SELECT OrderId, COUNT(Id) AS Total FROM [OrderDetail] GROUP BY OrderId) AS X ON O.Id = X.OrderId; UPDATE P SET QuantitySold = ISNULL(X.QSold,0) FROM [Product] P LEFT OUTER JOIN (SELECT ProductId, SUM(Quantity) AS QSold FROM [OrderDetail] GROUP BY ProductId) AS X ON P.Id = X.ProductId; UPDATE U SET OrderCount = ISNULL(X.Total,0) FROM [User] U LEFT OUTER JOIN (SELECT UserId, COUNT(Id) AS Total FROM [Order] GROUP BY UserId) AS X ON U.Id = X.UserId;" }); }
public ParticipantController(ParticipantContext context) { _participantContext = context; }
// // GET: /Dashboard/ public ActionResult Dashboard() { var model = new DashboardModel(); // lightweight query (only includes one column and data of first 4 months). var users = ParticipantContext.Users.Query("SELECT SignupDate FROM [User] WHERE SignupDate < '2013/5/1' ORDER BY SignupDate"); // get date range to partition in weekly buckets var min = new DateTime(2013, 1, 1); var max = new DateTime(2013, 5, 1); // (DateTime)users.Max(u => u.SignupDate); // create a bucket for each week int weeks = (((max - min).Days + 7) / 7) + 1; var buckets = Enumerable.Range(1, weeks).ToDictionary(w => w, w => 0); // number of new users each week foreach (var user in users) { buckets[WeekOfYear(user.SignupDate.Value)]++; } // create cumulative values int runningTotal = 0; for (int i = 1; i <= buckets.Count; i++) { runningTotal += buckets[i]; buckets[i] = runningTotal; } // flot formatting var usersData = new StringBuilder(@"[{ ""label"": ""Users"", ""color"" : ""#b00"", ""data"": ["); var usersTicks = new StringBuilder("["); int index = 1; foreach (var bucket in buckets) { usersData.Append("[" + index + ", " + bucket.Value + "],"); usersTicks.Append("[" + index + @", """ + bucket.Key + @"""],"); index++; } usersData = usersData.Remove(usersData.Length - 1, 1).Append("] }]"); usersTicks = usersTicks.Remove(usersTicks.Length - 1, 1).Append("]"); model.UsersData = usersData.ToString(); model.UsersTicks = usersTicks.ToString(); // sales data // lightweight query (only includes two columns and data of first 4 months). var orders = ParticipantContext.Orders.Query("SELECT OrderDate, TotalPrice FROM [Order] WHERE OrderDate < '2013/5/1' ORDER BY OrderDate"); for (int i = 1; i <= buckets.Count; i++) { buckets[i] = 0; // clear buckets } // sales by week foreach (var order in orders) { buckets[WeekOfYear(order.OrderDate.Value)] += (int)order.TotalPrice; } // create cumulative values runningTotal = 0; for (int i = 1; i <= buckets.Count; i++) { runningTotal += buckets[i]; buckets[i] = runningTotal; } // flot formatting var salesData = new StringBuilder(@"[{ ""label"": ""Sales"", ""color"" : ""#090"", ""data"": ["); var salesTicks = new StringBuilder("["); index = 1; foreach (var bucket in buckets) { salesData.Append("[" + index + ", " + bucket.Value + "],"); salesTicks.Append("[" + index + @", """ + bucket.Key + @"""],"); index++; } salesData = salesData.Remove(salesData.Length - 1, 1).Append("] }]"); salesTicks = salesTicks.Remove(salesTicks.Length - 1, 1).Append("]"); model.SalesData = salesData.ToString(); model.SalesTicks = salesTicks.ToString(); // Demographics of users // lightweight query (only includes two columns and data of first 4 months). string sql = "SELECT Country, COUNT(Id) AS Number FROM [User] GROUP BY Country ORDER BY Number DESC"; var countries = ParticipantContext.Query(sql); // flot formatting var demographics = new StringBuilder("["); foreach (var country in countries) { demographics.Append(@"{ ""label"": """ + country.Country + @""" , ""data"": " + country.Number + " },"); } demographics = demographics.Remove(demographics.Length - 1, 1).Append("]"); model.Demographics = demographics.ToString(); return(View(model)); }