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