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));
}
