private async Task StoreReportInDB(ITurnContext context, CallbackOptions options, ReportAnswers answers) { var userProfileTemporary = await _userProfileTemporaryAccessor.GetAsync(context); await Task.Run(() => { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder.DataSource = Consts.DB_SERVER; builder.UserID = Consts.DB_USER; builder.Password = Consts.DB_PASSWORD; builder.InitialCatalog = Consts.DB_NAME; using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) { connection.Open(); for (int i = 0; i < options.GeneratedPoints.Length; i++) { var attractor = options.GeneratedPoints[i].X; StringBuilder isb = new StringBuilder(); #if RELEASE_PROD isb.Append("INSERT INTO reports ("); #else isb.Append("INSERT INTO reports_dev ("); #endif // isb.Append("id,"); Automatically incremented from the CREATE TABLE... id uniqueidentifier default NEWSEQUENTIALID() primary key command isb.Append("user_id,"); isb.Append("platform,"); isb.Append("datetime,"); isb.Append("visited,"); isb.Append("point_type,"); if (!string.IsNullOrEmpty(answers.Intent)) { isb.Append("intent_set,"); } isb.Append("artifact_collected,"); isb.Append("fucking_amazing,"); isb.Append("rating_meaningfulness,"); isb.Append("rating_emotional,"); isb.Append("rating_importance,"); isb.Append("rating_strangeness,"); isb.Append("rating_synchroncity,"); isb.Append("text,"); isb.Append("photos,"); if (userProfileTemporary.IntentSuggestions != null && userProfileTemporary.IntentSuggestions.Length > 0) { isb.Append("intent_suggestions,"); isb.Append("time_intent_suggestions_set,"); } isb.Append("what_3_words,"); if (!string.IsNullOrEmpty(options.NearestPlaces[i])) { isb.Append("nearest_place,"); isb.Append("country,"); } isb.Append("short_hash_id,"); isb.Append("num_water_points_skipped,"); isb.Append("gid,"); isb.Append("tid,"); isb.Append("lid,"); isb.Append("idastep,"); isb.Append("idacount,"); isb.Append("type,"); isb.Append("x,"); isb.Append("y,"); isb.Append("center,"); isb.Append("latitude,"); isb.Append("longitude,"); if (attractor.center.bearing != null) { isb.Append("distance,"); isb.Append("initial_bearing,"); isb.Append("final_bearing,"); } isb.Append("side,"); isb.Append("distance_err,"); isb.Append("radiusM,"); isb.Append("number_points,"); isb.Append("mean,"); isb.Append("rarity,"); isb.Append("power_old,"); isb.Append("power,"); isb.Append("z_score,"); isb.Append("probability_single,"); isb.Append("integral_score,"); isb.Append("significance,"); isb.Append("probability"); isb.Append(") VALUES ("); isb.Append($"'{userProfileTemporary.UserId}',"); // sha256 hash of channel-issued userId isb.Append($"'{(int)Enum.Parse(typeof(Enums.ChannelPlatform), context.Activity.ChannelId)}',"); isb.Append($"'{context.Activity.Timestamp}',"); // datetime isb.Append($"'{(answers.WasPointVisited ? 1 : 0)}',"); // point visited or not? isb.Append($"'{options.PointTypes[i].ToString()}',"); // point type enum as a string if (!string.IsNullOrEmpty(answers.Intent)) { isb.Append($"'{SanitizeString(answers.Intent)}',"); // intent set by user } isb.Append($"'{(answers.ArtifactCollected ? 1 : 0)}',"); // were artifact(s) collected? isb.Append($"'{(answers.WasFuckingAmazing ? 1 : 0)}',"); // "yes" or "no" to the was it wow and astounding question isb.Append($"'{SanitizeString(answers.Rating_Meaningfulness)}',"); // Rating_Meaningfulness isb.Append($"'{SanitizeString(answers.Rating_Emotional)}',"); // Rating_Emotional isb.Append($"'{SanitizeString(answers.Rating_Importance)}',"); // Rating_Importance isb.Append($"'{SanitizeString(answers.Rating_Strangeness)}',"); // Rating_Strangeness isb.Append($"'{SanitizeString(answers.Rating_Synchronicty)}',"); // Rating_Synchronicty isb.Append($"'{SanitizeString(answers.Report)}',"); // text isb.Append($"'{(answers.PhotoURLs != null ? string.Join(",", answers.PhotoURLs) : "")}',"); // photos if (userProfileTemporary.IntentSuggestions != null && userProfileTemporary.IntentSuggestions.Length > 0) { isb.Append($"'{string.Join(",", SanitizeString(userProfileTemporary.IntentSuggestions))}',"); // intent suggestions isb.Append($"'{userProfileTemporary.TimeIntentSuggestionsSet}',"); } isb.Append($"'{(!string.IsNullOrEmpty(options.What3Words[i]) ? options.What3Words[i] : "")}',"); if (!string.IsNullOrEmpty(options.NearestPlaces[i])) { isb.Append($"'{options.NearestPlaces[i].Substring(0, options.NearestPlaces[i].IndexOf("(") - 1)}',"); isb.Append($"'{options.NearestPlaces[i].Substring(options.NearestPlaces[i].IndexOf("(") + 1).Replace(")", "")}',"); } isb.Append($"'{options.ShortCodes[i]}',"); isb.Append($"'{options.NumWaterPointsSkipped[i]}',"); //isb.Append($"'{attractor.GID}',");// was hardcoded at 23 in Fatumbot3 var shaGid = ""; if (options.ShaGids != null && options.ShaGids.Length >= 1) { shaGid = (options.ShaGids.Length == 1 ? options.ShaGids[0] : options.ShaGids[i]); } isb.Append($"'{shaGid}',"); isb.Append($"'{attractor.TID}',"); isb.Append($"'{attractor.LID}',"); isb.Append($"'{i+1}',"); // idastep (which element in idacount array) isb.Append($"'{options.GeneratedPoints.Length}',"); // total idacount isb.Append($"'{attractor.type}',"); isb.Append($"'{attractor.x}',"); isb.Append($"'{attractor.y}',"); isb.Append($"geography::Point({attractor.center.point.latitude},{attractor.center.point.longitude}, 4326),"); isb.Append($"'{attractor.center.point.latitude}',"); isb.Append($"'{attractor.center.point.longitude}',"); if (attractor.center.bearing != null) { isb.Append($"'{attractor.center.bearing.distance}',"); isb.Append($"'{attractor.center.bearing.initialBearing}',"); isb.Append($"'{attractor.center.bearing.finalBearing}',"); } isb.Append($"'{attractor.side}',"); isb.Append($"'{attractor.distanceErr}',"); isb.Append($"'{attractor.radiusM}',"); isb.Append($"'{attractor.n}',"); isb.Append($"'{attractor.mean}',"); isb.Append($"'{attractor.rarity}',"); isb.Append($"'{attractor.power_old}',"); isb.Append($"'{attractor.power}',"); isb.Append($"'{attractor.z_score}',"); isb.Append($"'{attractor.probability_single}',"); isb.Append($"'{attractor.integral_score}',"); isb.Append($"'{attractor.significance}',"); isb.Append($"'{attractor.probability}'"); isb.Append(")"); var insertSql = isb.ToString(); Console.WriteLine("SQL:" + insertSql.ToString()); using (SqlCommand command = new SqlCommand(insertSql, connection)) { // TODO: another way to execute the command? As it's only insert and don't need to read the results here using (SqlDataReader reader = command.ExecuteReader()) { string commandResult = ""; while (reader.Read()) { //commandResult += $"{reader.GetString(0)} {reader.GetString(1)}\n"; } //context.SendActivityAsync(commandResult); } } } } }); }
private async Task <DialogTurnResult> StartReportStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken) { //_logger.LogInformation($"TripReportDialog.StartReportStepAsync[{((FoundChoice)stepContext.Result)?.Value}]"); var callbackOptions = (CallbackOptions)stepContext.Options; switch (((FoundChoice)stepContext.Result)?.Value) { case "Yes and report!": // Go and start asking them about their trip var answers = new ReportAnswers() { WasPointVisited = true }; stepContext.Values[ReportAnswersKey] = answers; // TODO: [answers.PointNumberVisited] : implement the dialog steps/logic to ask this. switch (callbackOptions.PointTypes[answers.PointNumberVisited]) { case PointTypes.Attractor: case PointTypes.Void: case PointTypes.Anomaly: case PointTypes.PairAttractor: case PointTypes.PairVoid: case PointTypes.ScanAttractor: case PointTypes.ScanVoid: case PointTypes.ScanAnomaly: case PointTypes.ScanPair: case PointTypes.ChainAttractor: case PointTypes.ChainVoid: case PointTypes.ChainAnomaly: var options = new PromptOptions() { Prompt = MessageFactory.Text("Did you set an intent?"), RetryPrompt = MessageFactory.Text("That is not a valid choice."), Choices = new List <Choice>() { new Choice() { Value = "Yes" }, new Choice() { Value = "No" } } }; return(await stepContext.PromptAsync(nameof(ChoicePrompt), options, cancellationToken)); } ((ReportAnswers)stepContext.Values[ReportAnswersKey]).SkipGetIntentStep = true; return(await stepContext.NextAsync(cancellationToken : cancellationToken)); case "Yes sans reporting": await stepContext.Context.SendActivityAsync("Hope you had a fun trip!"); // At least mark the point as a visited one await StoreReportInDB(stepContext.Context, callbackOptions, new ReportAnswers() { WasPointVisited = true }); await stepContext.EndDialogAsync(cancellationToken : cancellationToken); return(await stepContext.BeginDialogAsync(nameof(MainDialog), cancellationToken : cancellationToken)); case "No": default: await StoreReportInDB(stepContext.Context, callbackOptions, new ReportAnswers()); await stepContext.EndDialogAsync(cancellationToken : cancellationToken); return(await stepContext.BeginDialogAsync(nameof(MainDialog), cancellationToken : cancellationToken)); } }