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)); }
public static async Task <IQueryResult <TEntity> > QueryAsync <TEntity>(this IBucket bucket, Expression <Func <TEntity, bool> > expression) where TEntity : class, IBaseEntity { string condition = $"type='{BaseEntity<TEntity>.DocumentType}'"; var request = new QueryRequest(); if (expression != null) { var translator = new CouchQueryTranslator(); var command = translator.Translate(expression); condition = $"{command.Command} AND {condition}"; request.AddPositionalParameter(command.Parameters.Select(p => p.Value).ToArray()); } request.Statement($"SELECT {bucket.Name}.* FROM {bucket.Name} WHERE {condition}"); var query = await bucket.QueryAsync <TEntity>(request); return(query); }
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 <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); }
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); }