protected override Task <SelectedSnapshot> LoadAsync(string persistenceId, SnapshotSelectionCriteria criteria)
{
// Create a Query with dynamic parameters
string N1QLQueryString = "select `" + _CBBucket.Name + "`.* from `" + _CBBucket.Name + "` where DocumentType = 'SnapshotEntry' AND PersistenceId = $PersistenceId ";
IQueryRequest N1QLQueryRequest = new QueryRequest()
.AddNamedParameter("PersistenceId", persistenceId);
string N1QLQueryOrderByClauseString = "ORDER BY SequenceNr DESC";
if (criteria.MaxSequenceNr > 0 && criteria.MaxSequenceNr < long.MaxValue)
{
N1QLQueryString += "AND SequenceNr <= $limit ";
N1QLQueryOrderByClauseString = "ORDER BY SequenceNr DESC,";
N1QLQueryRequest.AddNamedParameter("limit", criteria.MaxSequenceNr);
}
if (criteria.MaxTimeStamp != DateTime.MinValue && criteria.MaxTimeStamp != DateTime.MaxValue)
{
N1QLQueryString += " AND Timestamp <= $timelimit ";
N1QLQueryOrderByClauseString = "ORDER BY Timestamp DESC,";
N1QLQueryRequest.AddNamedParameter("timelimit", criteria.MaxTimeStamp.Ticks.ToString());
}
N1QLQueryString += N1QLQueryOrderByClauseString.TrimEnd(',') + " LIMIT 1";
N1QLQueryRequest.Statement(N1QLQueryString).AdHoc(false);
return(taskLoadAsync(N1QLQueryRequest));
}
private Task DeletePermanentlyMessages(SnapshotMetadata metadata)
{
string N1QLQueryString = "delete from `" + _CBBucket.Name + "` where DocumentType = 'SnapshotEntry' AND PersistenceId = $PersistenceId ";
IQueryRequest N1QLQueryRequest = new QueryRequest()
.AddNamedParameter("PersistenceId", metadata.PersistenceId)
.AdHoc(false);
if (metadata.SequenceNr > 0 && metadata.SequenceNr < long.MaxValue)
{
N1QLQueryString += " AND SequenceNr = $TargetSequenceId";
N1QLQueryRequest.AddNamedParameter("TargetSequenceId", metadata.SequenceNr);
}
if (metadata.Timestamp != DateTime.MinValue && metadata.Timestamp != DateTime.MaxValue)
{
N1QLQueryString += " AND Timestamp = $TargetTimeStamp";
N1QLQueryRequest.AddNamedParameter("TargetTimeStamp", metadata.Timestamp.Ticks.ToString());
}
N1QLQueryRequest.Statement(N1QLQueryString).AdHoc(false);
return(_CBBucket.QueryAsync <dynamic>(N1QLQueryRequest));
}
// serach by pageTitle or Ip
public object Search(String pageTitle = "", String ip = "")
{
// connect server
using (var cluster = new Cluster())
{
// autentication parameters
var authenticator = new PasswordAuthenticator(AppSettings.NoSqlUser, AppSettings.NoSqlPass);
cluster.Authenticate(authenticator);
// open bucket
using (var bucket = cluster.OpenBucket(AppSettings.NoSqlBucket))
{
// request with N1QL
// where clausules based on received parameters
String whereClausules = "";
if (!String.IsNullOrEmpty(pageTitle))
{
whereClausules = " pageTitle = $pageTitle";
}
if (!String.IsNullOrEmpty(ip))
{
if (!String.IsNullOrEmpty(pageTitle))
{
whereClausules += " AND "; // if both parameters has passed
}
whereClausules += " ip = $ip";
}
// add "where" if have where clausules
if (whereClausules.Length > 0)
{
whereClausules = " WHERE " + whereClausules;
}
// prepare query
var queryRequest = new QueryRequest()
.Statement("SELECT * FROM CustomerNavigations " + whereClausules + " ORDER BY id DESC LIMIT 100");
// add parameters values
if (!String.IsNullOrEmpty(pageTitle))
{
queryRequest.AddNamedParameter("$pageTitle", pageTitle);
}
if (!String.IsNullOrEmpty(ip))
{
queryRequest.AddNamedParameter("$ip", ip);
}
var result = bucket.Query <dynamic>(queryRequest);
return(result.Rows);
}
}
}
public async Task <List <Mark> > GetMarksByStudentAndCourse(string studentId, string courseId)
{
var query = new QueryRequest(
"SELECT m.* FROM HelpToTeachBucket m " +
"JOIN HelpToTeachBucket gc ON m.groupCourseId = gc.id " +
"WHERE m.type = 'mark' AND gc.type = 'groupcourse' " +
"AND gc.courseId = $courseId AND m.studentId = $studentId");
query.AddNamedParameter("$studentId", studentId);
query.AddNamedParameter("$courseId", courseId);
var result = await bucket.QueryAsync <Mark>(query);
return(result.Rows);
}
public async Task <List <Mark> > GetMarksByStudentAndGroupCourse(string studentId, string groupCourseId)
{
var query = new QueryRequest(
"SELECT m.*, l as `lesson` FROM HelpToTeachBucket m " +
"JOIN HelpToTeachBucket l ON m.lessonId = l.id " +
"WHERE m.type = 'mark' AND l.type = 'lesson' " +
"AND l.groupCourseId = $groupCourseId AND m.studentId = $studentId");
query.AddNamedParameter("$groupCourseId", groupCourseId);
query.AddNamedParameter("$studentId", studentId);
var result = await bucket.QueryAsync <Mark>(query);
return(result.Rows);
}
public async Task <List <Mark> > GetPredictedMarksByLesson(string lessonId, int type)
{
var query = new QueryRequest(
"SELECT m.*, l as `lesson` FROM HelpToTeachBucket m " +
"JOIN HelpToTeachBucket l ON m.lessonId = l.id " +
"WHERE m.type = 'mark' AND l.type = 'lesson' " +
"AND l.id = $lessonId AND l.lessontype = $type");
query.AddNamedParameter("$lessonId", lessonId);
query.AddNamedParameter("$type", type);
var result = await bucket.QueryAsync <Mark>(query);
return(result.Rows);
}
public async Task DeletePredictedMarksByLesson(string lessonId)
{
var query = new QueryRequest("DELETE FROM HelpToTeachBucket m WHERE m.type = 'mark' AND m.isPredicted = true AND m.lessonid = $lessonId");
query.AddNamedParameter("$lessonId", lessonId);
var result = await bucket.QueryAsync <Mark>(query);
}
public async Task <IActionResult> DefineQuiz([FromBody] QuizDefinition request)
{
if (request == null ||
string.IsNullOrEmpty(request.QuizName) ||
string.IsNullOrEmpty(request.QuizType))
// || request.QuestionSet?.Count == 0)
{
return(BadRequest("Mandatory Fields Missing."));
}
request.DocumentType = "Define";
var parameters = new Dictionary <string, object>();
var query = string.Format(@"SELECT quizName FROM {0} WHERE status=""{1}"" and quizName = $quizName and quizType = $quizType and documentType=""{2}""", CouchbaseHelper.Bucket, "Published", "Define");
parameters.Add("$quizName", request.QuizName);
parameters.Add("$quizType", request.QuizType);
var req = new QueryRequest(query);
req.AddNamedParameter(parameters.ToArray());
var result = await CouchbaseHelper.CouchbaseClient.GetByQueryAsync <QuizDefinition>(req);
if (result.Count > 0)
{
return(BadRequest("This Quiz is already Published."));
}
var response = await CouchbaseHelper.CouchbaseClient.UpsertAsync(request.QuizName + "_" + request.QuizType, request);
return(Ok(response));
}
public List <T> GetAll(Type t)
{
var type = t.Name.ToLower();
var query = new QueryRequest("SELECT MyFootball.* FROM MyFootball WHERE type = $type");
query.AddNamedParameter("type", type);
var result = _bucket.Query <T>(query);
return(!result.Success ? null : result.Rows);
}
public async Task <List <T> > GetAll(Type t)
{
var type = t.Name.ToLower();
var query = new QueryRequest("SELECT HelpToTeachBucket.* FROM HelpToTeachBucket WHERE type = $type");
query.AddNamedParameter("type", type);
var result = await _bucket.QueryAsync <T>(query);
return(!result.Success ? null : result.Rows);
}
public async Task <List <Mark> > GetMarksByStudent(string id)
{
var query = new QueryRequest(
"SELECT m.* FROM HelpToTeachBucket m " +
"WHERE m.type = 'mark' AND m.studentId = $studentId");
query.AddNamedParameter("$studentId", id);
var result = await bucket.QueryAsync <Mark>(query);
return(result.Rows);
}
public async Task <List <Lesson> > GetByGroupCourse(string id)
{
var query = new QueryRequest(
"SELECT l.* FROM HelpToTeachBucket l " +
"WHERE l.type = 'lesson' AND l.groupCourseId = $groupCourseId"
);
query.AddNamedParameter("$groupCourseId", id);
var result = await bucket.QueryAsync <Lesson>(query);
return(result.Rows);
}
public async Task <List <Lesson> > GetByLecturer(string id)
{
var query = new QueryRequest(
"SELECT l.*, gc as `groupCourse` FROM HelpToTeachBucket l " +
"JOIN HelpToTeachBucket gc ON l.groupCourseId = gc.id " +
"WHERE l.type = 'lesson' AND gc.type ='groupcourse' AND gc.userId = $lecturerId"
);
query.AddNamedParameter("$lecturerId", id);
var result = await bucket.QueryAsync <Lesson>(query);
return(result.Rows);
}
public async Task <List <Group> > GetByLecturer(string id)
{
var query = new QueryRequest(
"SELECT DISTINCT g.* FROM HelpToTeachBucket g " +
"JOIN HelpToTeachBucket gc ON gc.groupId = g.id " +
"WHERE g.type = 'group' AND gc.type = 'groupcourse' " +
"AND gc.userId = $userId"
);
query.AddNamedParameter("$userId", id);
var result = await bucket.QueryAsync <Group>(query);
return(result.Rows);
}
protected override Task DeleteAsync(string persistenceId, SnapshotSelectionCriteria criteria)
{
string N1QLQueryString = "delete from `" + _CBBucket.Name + "` where DocumentType = 'SnapshotEntry' AND PersistenceId = $PersistenceId ";
IQueryRequest N1QLQueryRequest = new QueryRequest()
.AddNamedParameter("PersistenceId", persistenceId);
if (criteria.MaxSequenceNr > 0 && criteria.MaxSequenceNr < long.MaxValue)
{
N1QLQueryString += " AND SequenceNr <= $limit ";
N1QLQueryRequest.AddNamedParameter("limit", criteria.MaxSequenceNr);
}
if (criteria.MaxTimeStamp != DateTime.MinValue && criteria.MaxTimeStamp != DateTime.MaxValue)
{
N1QLQueryString += " AND Timestamp <= $timelimit ";
N1QLQueryRequest.AddNamedParameter("timelimit", criteria.MaxTimeStamp.Ticks.ToString());
}
N1QLQueryRequest.Statement(N1QLQueryString).AdHoc(false);
return(_CBBucket.QueryAsync <dynamic>(N1QLQueryRequest));
}
public async Task <List <GroupCourse> > GetByLecturer(string id)
{
var query = new QueryRequest(
"SELECT gc.*, c as `course`, g as `group`, l as `lecturer` " +
"FROM HelpToTeachBucket gc " +
"JOIN HelpToTeachBucket g ON gc.groupId = g.id " +
"JOIN HelpToTeachBucket c ON gc.courseId = c.id " +
"JOIN HelpToTeachBucket l ON gc.userId = l.id " +
"WHERE c.type = 'course' AND g.type = 'group' " +
"AND gc.type = 'groupcourse' AND l.type='user' AND gc.userId = $userId"
);
query.AddNamedParameter("$userId", id);
var result = await bucket.QueryAsync <GroupCourse>(query);
return(result.Rows);
}
public List <string> postAttendance(string email, string user_id, string timeIn, int location)
{
var queryRequest = new QueryRequest();
DateTime TimeIn = DateTime.Now;
List <string> values = new List <string>();
TimeSpan work;
try
{
if (timeIn.CompareTo("") != 0)
{
TimeIn = DateTime.Parse(HttpUtility.UrlDecode(timeIn));
}
string userCond = "";
userCond = " and user_id=$user_id ";
if (user_id != null && !user_id.Equals(""))
{
queryRequest.AddNamedParameter("user_id", user_id);
}
else if (email != null && !email.Equals(""))
{
queryRequest.AddNamedParameter("email", email);
var queryGetUserId = new QueryRequest();
queryGetUserId.Statement("SELECT META(Testing).id AS id FROM `Testing` WHERE email=$email")
.AddNamedParameter("email", email);
IQueryResult <dynamic> resultId = bucket.Query <dynamic>(queryGetUserId);
if (resultId.Rows.Count() > 0)
{
JObject userAttendanceData = JsonConvert.DeserializeObject <JObject>(resultId.Rows.ElementAt(0) + "");
values.Add(userAttendanceData["id"].ToString());
queryRequest.AddNamedParameter("user_id", userAttendanceData["id"].ToString());
user_id = userAttendanceData["id"].ToString();
}
else
{
values.Add("No User Found");
return(values);
}
}
else
{
values.Add("No User Found");
return(values);
}
string dat = TimeIn.ToString("yyyy-MM-dd");
UserAttendance newAttendance = null;
queryRequest.Statement("SELECT META(Testing).id AS id,* FROM `Testing` WHERE date=$date2 " + userCond)
.AddNamedParameter("date2", dat);
IQueryResult <dynamic> result = bucket.Query <dynamic>(queryRequest);
if (result.Rows.Count() == 0)
{
DateTime defTime = DateTime.Parse(TimeIn.ToString("yyyy/MM/dd") + "T17:00:00");
work = defTime.Subtract(TimeIn);
newAttendance = new UserAttendance
{
user_id = user_id,
date = dat,
marked_at = new List <DateTime>(),
time_in = TimeIn.ToString("yyyy-MM-ddTHH:mm:ss"),
time_out = "",
default_out = defTime.ToString(),
shifttotalhours = "8",
worktotalhours = work.Hours + ":" + work.Minutes,
doctype = "user_attendance",
channels = new[] { "attendance" },
client = "Esajee",
shiftId = "1",
shiftName = "Default",
location = location
};
newAttendance.marked_at.Add(TimeIn);
values.Add(user_id);
toServerPost(newAttendance, "", "POST");
}
else
{
JObject userAttendanceData = JsonConvert.DeserializeObject <JObject>(result.Rows.ElementAt(0) + "");
string docId = "";
string revId = "";
docId = userAttendanceData["id"].ToString();
revId = userAttendanceData["Testing"]["_sync"]["rev"].ToString();
newAttendance = JsonConvert.DeserializeObject <UserAttendance>(JsonConvert.SerializeObject(userAttendanceData["Testing"]));
DateTime markTimeIn = DateTime.Parse(newAttendance.time_in);
if (newAttendance != null)
{
newAttendance.time_out = TimeIn.ToString("yyyy-MM-ddTHH:mm:ss");
TimeSpan work12 = TimeIn.Subtract(markTimeIn);
newAttendance.worktotalhours = work12.Hours + ":" + work12.Minutes;
if (newAttendance.marked_at != null)
{
newAttendance.marked_at.Add(TimeIn);
}
values.Add(toServerPost(newAttendance, docId + "?rev=" + revId, "PUT"));
}
else
{
values.Add("View Not Working");
}
}
}
catch (Exception re)
{
values.Add("Times convertion failed");
return(values);
}
return(values);
}
private void RunQuery(string query, IDictionary <string, object> parameters = null)
{
edtResults.Text = query + "\r\n\r\nRunning...";
tabControl.Enabled = false;
Task.Run(async() =>
{
var bucket = ClusterHelper.GetBucket("beer-sample");
var queryRequest = new QueryRequest(query);
if (parameters != null)
{
queryRequest.AddNamedParameter(parameters.ToArray());
}
;
var result = await
bucket.QueryAsync <dynamic>(queryRequest);
if (!result.Success)
{
if (result.Errors != null && result.Errors.Count > 0)
{
return(result.Errors.First().Message);
}
else if (result.Exception != null)
{
return(string.Format("{0}\r\n\r\n{1}\r\n{2}", query, result.Exception.Message,
result.Exception.StackTrace));
}
else
{
return("Unknown Error");
}
}
else if (result.Rows != null)
{
var sb = new StringBuilder();
sb.AppendFormat("{0}\r\n\r\n{1} rows returned\r\n\r\n", query, result.Rows.Count);
foreach (var row in result.Rows)
{
sb.AppendLine(row.ToString());
}
return(sb.ToString());
}
else
{
return(query + "\r\n\r\n0 row returned");
}
})
.ContinueWith(task =>
{
BeginInvoke(new Action(() =>
{
if (task.IsFaulted)
{
edtResults.Text = string.Format("{0}\r\n\r\n{1}\r\n{2}", query, task.Exception.Message,
task.Exception.StackTrace);
}
else
{
edtResults.Text = task.Result;
}
tabControl.Enabled = true;
}));
});
}
public async Task <dynamic> GetFhir(string type, Dictionary <string, string> predicates)
{
var query = "SELECT {0} FROM `" + _bucket.Name + "` {0} {1} WHERE {0}.resourceType = $type ";
var join = " INNER JOIN `" + _bucket.Name + "` {child} ON split({child}.subject.reference,':')[2] = {0}.id AND {child}.resourceType= $child ";
var queryRequest = new QueryRequest()
.AddNamedParameter("$type", type)
.Metrics(false);
var modifiedPredicates = new Dictionary <string, string>();
query = query.Replace("{0}", "`" + type.ToLower() + "`");
bool added = false;
foreach (var item in predicates)
{
var key = item.Key.Replace(":", ".");
var keys = key.Split(".");
if (keys.Length > 1)
{
if (Types.Contains(keys[0]))
{
if (!added)
{
join = join.Replace("{child}", "`" + keys[0].ToLower() + "`");
join = join.Replace("{0}", "`" + type.ToLower() + "`");
query = query.Replace("{1}", join);
queryRequest.AddNamedParameter("$child", keys[0]);
}
key = key.Replace(keys[0], keys[0].ToLower());
added = true;
}
else
{
key = type.ToLower() + "." + key;
}
}
else
{
key = type.ToLower() + "." + key;
}
modifiedPredicates.Add(key, item.Value);
}
query = query.Replace("{1}", "");
foreach (var item in modifiedPredicates)
{
var keys = item.Key.Split(".");
var key = item.Key.Replace(".", "`.`");
if (key.Contains('.'))
{
key = key.Insert(0, "`");
key = key.Insert(key.Length, "`");
}
var itemKey = item.Key.Replace(":", "A").Replace(".", "A");
var val = item.Value;
var op = "=";
if (!string.IsNullOrEmpty(val))
{
if (val.Length > 2)
{
var str = val.Substring(0, 2);
if (Eval.ContainsKey(str))
{
op = Eval[str];
val = val.Substring(2, val.Length - 2);
}
}
}
if (keys.LastOrDefault <string>() == keys.FirstOrDefault <string>(x => ItemArray.ContainsKey(x)))
{
var ik = keys.First <string>(x => ItemArray.ContainsKey(x));
query = query + ItemArray[ik];
query = query.Replace("{type}", keys[0].ToLower());
queryRequest.AddNamedParameter("$" + ik, (ik == "name") ? val + "%" : val);
}
else if (float.TryParse(val, out float fValue))
{
query = query + " AND " + key + op + "$" + itemKey;
queryRequest.AddNamedParameter("$" + itemKey, fValue);
}
else if (Int32.TryParse(val, out int iValue))
{
query = query + " AND " + key + op + "$" + itemKey;
queryRequest.AddNamedParameter("$" + itemKey, iValue);
}
else if (DateTime.TryParse(val, out DateTime dValue))
{
query = query + " AND " + key + op + "$" + itemKey;
queryRequest.AddNamedParameter("$" + itemKey, dValue);
}
else
{
query = query + " AND " + key + op + "$" + itemKey;
queryRequest.AddNamedParameter("$" + itemKey, val);
}
}
query = query + " LIMIT 10;";
queryRequest.Statement(query);
var result = await _bucket.QueryAsync <dynamic>(queryRequest);
return(result);
}