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