//Gives the report URL once a year and code have been given.
public string GiveURL(ApiAiRequest request)
{
//If the user asked for a summary, that has a differnet URL so it has a different statement.
if (!string.IsNullOrEmpty(request.queryResult.parameters.year) && !string.IsNullOrEmpty(request.queryResult.parameters.code) && request.queryResult.parameters.code.ToUpper() != "SUMMARY")
{
string year = Regex.Replace(request.queryResult.parameters.year, "[^0-9.]", "");
string url = "http://datareports.lsu.edu/Reports.aspx?yr=" + year + "&rpt=" + request.queryResult.parameters.code + "&p=ci";
return("Here is your URL: " + url);
}
else if (!string.IsNullOrEmpty(request.queryResult.parameters.year) && !string.IsNullOrEmpty(request.queryResult.parameters.code) && request.queryResult.parameters.code.ToUpper() == "SUMMARY")
{
string year = Regex.Replace(request.queryResult.parameters.year, "[^0-9.]", "");
string url = "http://datareports.lsu.edu/Reports/TrafficReports/" + year + "/Summary/Summary.asp";
return("Here is your URL: " + url);
}
else if (!string.IsNullOrEmpty(request.queryResult.parameters.year))
{
return("What is the code of this report?");
}
else if (!string.IsNullOrEmpty(request.queryResult.parameters.code))
{
return("What year would you like to find this report for?");
}
else
{
return("Please provide a year and report code.");
}
}
//When the user gives keyword(s), lists reports containing those keywords.
public string ListByKeyword(ApiAiRequest request, bool and)
{
using (SQLiteConnection cn = new SQLiteConnection("Data Source=|DataDirectory|\\CRASH_LINKS.sqlite3; Version=3"))
{
//The "and" bool decides whether the query should fetch reports containing all keywords (x AND y) or all reports containing any of the keywords (x OR y)
string query = KeywordQueryBuilder(request, and);
SQLiteCommand cmd = new SQLiteCommand(query, cn);
string s = (and ? "Here are the reports that contain these keywords:" : "We could not find any results that contain all of these keywords, so here are results that contain one or more of these keywords:");
cn.Open();
SQLiteDataReader dr = cmd.ExecuteReader();
bool foundatleastone = false;
while (dr.Read())
{
s += Environment.NewLine + dr[0] + dr[1] + dr[2] + "- " + dr[3];
foundatleastone = true;
}
cn.Close();
//On the first run, "and" is true. x AND y is more likely to help the user find what they're looking for.
//If no results are returned this way, the function runs again with "and" being false, so the user can still get some results.
if (!foundatleastone)
{
return(and ? ListByKeyword(request, false) : "Could not find results for these keywords.");
}
return(s);
}
}
//IntVars are the name I use to refer to condition variables which involve a number. These are different from other conditions because they have a variable aspect
//in them ("Age greater than x" -> Age > x) unlike the other conditions ("fatality" -> InjuryCode = 'A') Thus I have to retrieve them a different way.
private List <string> GetIntVars(ApiAiRequest request, string table)
{
List <string> intconditions = new List <string>();
switch (table)
{
case "FactPerson":
for (int i = 0; i < request.queryResult.parameters.PersonConditionIntVar.Count(); i++)
{
if (string.IsNullOrEmpty(request.queryResult.parameters.PersonConditionIntVar[i].Inequality))
{
intconditions.Add(request.queryResult.parameters.PersonConditionIntVar[i].PersonConditionInt + " = " + request.queryResult.parameters.PersonConditionIntVar[i].number.ToString());
}
else if (string.IsNullOrEmpty(request.queryResult.parameters.PersonConditionIntVar[i].PersonConditionInt))
{
intconditions.Add(request.queryResult.parameters.PersonConditionIntVar[i].Inequality + " " + request.queryResult.parameters.PersonConditionIntVar[i].number.ToString());
}
else
{
intconditions.Add(request.queryResult.parameters.PersonConditionIntVar[i].PersonConditionInt + " " + request.queryResult.parameters.PersonConditionIntVar[i].Inequality + " " + request.queryResult.parameters.PersonConditionIntVar[i].number.ToString());
}
intconditions[i] = CheckForAgeThing(intconditions[i]);
}
break;
case "FactCrash":
for (int i = 0; i < request.queryResult.parameters.CrashConditionIntVar.Count(); i++)
{
if (string.IsNullOrEmpty(request.queryResult.parameters.CrashConditionIntVar[i].Inequality))
{
intconditions.Add(request.queryResult.parameters.CrashConditionIntVar[i].CrashConditionInt + " = " + request.queryResult.parameters.CrashConditionIntVar[i].number.ToString());
}
else
{
intconditions.Add(request.queryResult.parameters.CrashConditionIntVar[i].CrashConditionInt + " " + request.queryResult.parameters.CrashConditionIntVar[i].Inequality + " " + request.queryResult.parameters.CrashConditionIntVar[i].number.ToString());
}
}
break;
case "FactVehicle":
for (int i = 0; i < request.queryResult.parameters.VehicleConditionIntVar.Count(); i++)
{
if (string.IsNullOrEmpty(request.queryResult.parameters.VehicleConditionIntVar[i].Inequality))
{
intconditions.Add(request.queryResult.parameters.VehicleConditionIntVar[i].VehicleConditionInt + " = " + request.queryResult.parameters.VehicleConditionIntVar[i].number.ToString());
}
else
{
intconditions.Add(request.queryResult.parameters.VehicleConditionIntVar[i].VehicleConditionInt + " " + request.queryResult.parameters.VehicleConditionIntVar[i].Inequality + " " + request.queryResult.parameters.VehicleConditionIntVar[i].number.ToString());
}
}
break;
}
return(intconditions);
}
public IHttpActionResult Post([FromBody] ApiAiRequest request)
{
int num1 = Convert.ToInt32(request.result.parameters["num1"]);
int num2 = Convert.ToInt32(request.result.parameters["num2"]);
int sum = num1 + num2;
string speech = $"Sum of {num1} and {num2} is {sum}";
ApiAiResponse response = new ApiAiResponse
{
speech = speech,
displayText = speech
};
return(Ok(response));
}
//If a string is not null or empty, that means a user has provided it.
//Handles when the user asks to find a report.
public string FindReport(ApiAiRequest request)
{
if (!string.IsNullOrEmpty(request.queryResult.parameters.year) && !string.IsNullOrEmpty(request.queryResult.parameters.code))
{
return(GiveURL(request));
}
else if (!string.IsNullOrEmpty(request.queryResult.parameters.year))
{
return(CheckYear(request));
}
else
{
return("If you know the code for this report, you can tell me that. If not, we can search reports.");
}
}
//Gives the user a list of topics that reports may fall under.
public string ListTopics(ApiAiRequest request)
{
using (SQLiteConnection cn = new SQLiteConnection("Data Source=|DataDirectory|\\CRASH_LINKS.sqlite3; Version=3"))
{
string query = "SELECT [REPORTHEADER], [REPORTHEADERLONG] From TRAFFIC_CMV_HEADERS";
SQLiteCommand cmd = new SQLiteCommand(query, cn);
string s = "Here are the topics:";
cn.Open();
SQLiteDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
s += Environment.NewLine + dr[0] + "- " + dr[1];
}
cn.Close();
return(s);
}
}
//Checks the year given when finding a report.
public string CheckYear(ApiAiRequest request)
{
string year = Regex.Replace(request.queryResult.parameters.year, "[^0-9.]", "");
if (int.Parse(year) < 2005 || int.Parse(year) > DateTime.Now.Year)
{
return("Sorry, we don't have reports for year " + year);
}
else
{
if (!string.IsNullOrEmpty(request.queryResult.parameters.code))
{
return(GiveURL(request));
}
else
{
return("Alright, we will now find reports for year " + year + ". If you know the code for this report, you can tell me that and the year. If not, we can search reports.");
}
}
}
//When the user gives a topic, lists all reports belonging to that topic.
public string ListByTopic(ApiAiRequest request)
{
using (SQLiteConnection cn = new SQLiteConnection("Data Source=|DataDirectory|\\CRASH_LINKS.sqlite3; Version=3"))
{
string query = "SELECT [REPORTLETTER],[SUBHEADERNUM],[POSTNUMBER],[SUBHEADER] FROM TRAFFIC WHERE [REPORTLETTER] = '" + request.queryResult.parameters.Topic + "' AND [ACTIVE] = 1 ORDER BY [REPORTLETTER], [SUBHEADERNUM], [POSTNUMBER]";
SQLiteCommand cmd = new SQLiteCommand(query, cn);
string s = "Here are the reports in this topic:";
cn.Open();
SQLiteDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
s += Environment.NewLine + dr[0] + dr[1] + dr[2] + "- " + dr[3];
}
cn.Close();
return(s);
}
}
public IHttpActionResult Post([FromBody] ApiAiRequest request)
{
string intent = request.queryResult.intent.displayName;
FulfillmentFunctions ff = new FulfillmentFunctions();
//try
//{
switch (intent)
{
case "FindReport":
return(Respond(ff.FindReport(request), false));
case "FindReport.code":
return(Respond(ff.GiveURL(request), false));
case "FindReport.year":
return(Respond(ff.CheckYear(request), false));
case "SearchReport.topics":
return(Respond(ff.ListTopics(request), false));
case "SearchReport.topics.search":
return(Respond(ff.ListByTopic(request), true));
case "SearchReport.keywords.search":
return(Respond(ff.ListByKeyword(request, true), true));
case "Query.People.conditions":
return(Respond(ff.Query(request), false));
case "Query.Crashes.conditions":
return(Respond(ff.Query(request), false));
case "Query.Vehicles.conditions":
return(Respond(ff.Query(request), false));
}
//}
//catch { }
return(Respond("Hello World", false));
}
//This is the first function for the querying ability of the bot.
public string Query(ApiAiRequest request)
{
using (SqlConnection cn = new SqlConnection("Data Source=dev-sqlsrv;Initial Catalog=CRASHDWHSRG;Integrated Security=true"))
{
string[] ts = QueryQueryBuilder(request);
string query = ts[0];
string conditionsforpeople = ts[1];
string query_total = ts[2];
SqlCommand cmd = new SqlCommand(query, cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
double result = 0;
while (dr.Read())
{
result = double.Parse(dr[0].ToString());
}
cn.Close();
cmd = new SqlCommand(query_total, cn);
cn.Open();
dr = cmd.ExecuteReader();
double total = 1;
while (dr.Read())
{
total = double.Parse(dr[0].ToString());
}
cn.Close();
double percent = Math.Round((result / total) * 100, 3);
string s = "Here are the conditions we considered:" + Environment.NewLine;
s += conditionsforpeople;
s += "Here is the result from those conditions:" + Environment.NewLine;
s += result + " (" + percent + "%)";
return(s);
}
}
//Builds the query for searching by keyword.
private string KeywordQueryBuilder(ApiAiRequest request, bool and)
{
string query = "SELECT [REPORTLETTER],[SUBHEADERNUM],[POSTNUMBER],[SUBHEADER] FROM TRAFFIC WHERE (";
//Keywords are resolved through Dialogflow. If the user says phrases such as "death", "killed", "fatality,"
//they will all resolve to "fatal", which is a keyword many reports have.
List <string> keywords = request.queryResult.parameters.KeyWords;
for (int i = 0; i < keywords.Count(); i++)
{
//Makes it so the query checks if the keywords of a report contains each keyword given.
if (i == 0)
{
query += "[KEYWORDS] LIKE '%" + keywords[i] + "%'";
}
else
{
//If "and" is true, uses AND, otherwise uses OR
query += (and ? " AND [KEYWORDS] LIKE '%" + keywords[i] + "%'" : " OR [KEYWORDS] LIKE '%" + keywords[i] + "%'");
}
}
query += ") AND [ACTIVE] = 1 ORDER BY [REPORTLETTER], [SUBHEADERNUM], [POSTNUMBER]";
return(query);
}
private async Task MessageReceivedAsync(IDialogContext context, IAwaitable <object> result)
{
var activity = await result as Activity;
if (_isFirst && activity.ChannelId != "webchat" && activity.ChannelId != "emulator")
{
await context.PostAsync("Здравствуйте! Я Бот Электронной площадки РТС-тендер.");
_isFirst = false;
}
// Проверка на разговор с оператором
if (_operatorsConversation)
{
await ToOperator(context, activity);
if (ResetParametrs.Reset(activity?.Text))
{
MakeReset();
await context.PostAsync("До свидания");
}
return;
}
//-----------------------------------
if (_answerExistence)
{
if (activity.Text.ToLower() != "да" && activity.Text.ToLower() != "нет")
{
await context.PostAsync("Ответьте, пожалуйста, на вопрос. Нам очень важно Ваше мнение.");
Thread.Sleep(1500);
CardDialog.SatisfyingAnswer(context, activity);
return;
}
else
{
if (activity.Text.ToLower() == "да")
{
_correct = true;
try
{
// Работа с Azure Таблицами
AddQuestionInAzureTable.UpdateData(_platform, _role, _userQuestion, _answer, activity.ChannelId, true);
// Работа с гугл таблицами
// AddQuestionInGoogleSheet.SendError(_platform, _role, _userQuestion, _answer, _correct);
}
catch //(Exception ex)
{
//throw new Exception(ex.Message);
//_userQuestion = null;
//_answer = null;
//_answerExistence = false;
//_correct = false;
//return;//await context.PostAsync("Возникли проблемы с обработкой Вашего ответа");
}
await context.PostAsync("Благодарю, Ваш ответ очень помог нам");
Thread.Sleep(1500);
await context.PostAsync("Если Вас еще что-то интересует, напишите тему");
_userQuestion = null;
_answer = null;
_answerExistence = false;
_correct = false;
return;
}
if (activity.Text.ToLower() == "нет")
{
try
{
// Работа с Azure Таблицами
AddQuestionInAzureTable.UpdateData(_platform, _role, _userQuestion, _answer, activity.ChannelId, false);
// Работа с гугл таблицами
// AddQuestionInGoogleSheet.SendError(_platform, _role, _userQuestion, _answer, _correct);
}
catch //(Exception ex)
{
//throw new Exception(ex.Message);
//return; //await context.PostAsync("Возникли проблемы с обработкой Вашего ответа");
}
await context.PostAsync("Большое спасибо. Ваше сообщение передано в службу технической поддержки. Приносим извинения за неудобство");
Thread.Sleep(1500);
await context.PostAsync("Если Вас еще что-то интересует, напишите тему");
_answer = null;
_userQuestion = null;
_answerExistence = false;
return;
}
}
}
try
{
if (ResetParametrs.Reset(activity?.Text))
{
MakeReset();
}
}
catch
{
await context.PostAsync("Возникли проблемы с работой сервиса. Приносим извинения за неудобство");
}
if (_parametrs == false)
{
if (string.IsNullOrEmpty(_platform) || string.IsNullOrEmpty(_role))
{
if (!string.IsNullOrWhiteSpace(activity?.Text))
{
var apiAiResponse = ApiAiRequest.ApiAiBotRequest(activity.Text);
// Если есть ошибки
if (apiAiResponse.Errors != null && apiAiResponse.Errors.Count > 0)
{
await context.PostAsync("Что-то пошло не так");
}
// Если нет ошибок
else
{
// Проверка наличия, добавление или редактирование параметра "Площадка"
if (!string.IsNullOrEmpty(_platform))
{
if ((_platform != apiAiResponse.Platform) &&
(!string.IsNullOrEmpty(apiAiResponse.Platform)))
{
_platform = apiAiResponse.Platform;
}
}
else
{
_platform = apiAiResponse.Platform;
}
// Проверка наличия, добавление или редактирование параметра "Роль"
if (!string.IsNullOrEmpty(_role))
{
if ((_role != apiAiResponse.Role) && (!string.IsNullOrEmpty(apiAiResponse.Role)))
{
_role = apiAiResponse.Role;
}
}
else
{
_role = apiAiResponse.Role;
}
}
}
else
{
await context.PostAsync("Что-то пошло не так");
}
// Идет проверка наличия всех заполненных и незаполненных параметров с последующим информированием пользователя
if (string.IsNullOrEmpty(_platform) || string.IsNullOrEmpty(_role))
{
string checkParametrs = ParametrsDialog.CheckParametrs(_platform, _role);
if (string.IsNullOrEmpty(_platform))
{
try
{
CardDialog.PlatformCard(context, activity, checkParametrs);
}
catch
{
await context.PostAsync("Что-то пошло не так");
}
}
if (string.IsNullOrEmpty(_role) && !string.IsNullOrEmpty(_platform))
{
if (_platform == "Имущественные торги")
{
try
{
CardDialog.RoleCardImuchestvo(context, activity, checkParametrs);
return;
}
catch
{
await context.PostAsync("Что-то пошло не так");
}
}
if (_platform == "Электронный магазин ЗМО")
{
try
{
CardDialog.RoleCardRTS(context, activity, checkParametrs);
return;
}
catch
{
await context.PostAsync("Что-то пошло не так");
}
}
if (_platform == "615-ПП РФ")
{
try
{
CardDialog.RoleCard615(context, activity, checkParametrs);
return;
}
catch
{
await context.PostAsync("Что-то пошло не так");
}
}
else
{
try
{
CardDialog.RoleCard(context, activity, checkParametrs);
return;
}
catch
{
await context.PostAsync("Что-то пошло не так");
}
}
}
}
else
{
_parametrs = true;
await context.PostAsync(
"Напишите теперь интересующую Вас тему. Для возврата в исходное состояние наберите слово *сброс*");
activity.Text = null;
}
}
else
{
_parametrs = true;
await context.PostAsync("Напишите теперь интересующую Вас тему.");
}
}
if (!string.IsNullOrEmpty(activity?.Text) && _parametrs)
{
try
{
_userQuestion = activity.Text;
try
{
var qnaDialog = new QnADialog();
_answer = await qnaDialog.QnABotResponse(_platform, _role, _userQuestion);
}
catch// (Exception ex)
{
//throw new Exception(ex.Message);
await context.PostAsync("Что-то пошло не так");
}
if (_answer == "Прошу прощения, но я не понял вопроса. Попробуйте перефразировать его.")
{
await context.PostAsync(_answer);
_answerExistence = false;
// Для включения/выключения функции перенаправления оператору
// await ToOperator(context, activity);
//------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
return;
}
try
{
// Работа с Azure Таблицами
AddQuestionInAzureTable.AddData(_platform, _role, _userQuestion, _answer, activity.ChannelId);
}
catch { }
string strRegex = @"(\!\[(alt text)?\])\((ht|f)tp(s?)\:\/\/[0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*(:(0-9)*)*(\/?)([a-zA-Z0-9А-Яа-я \-\.\?\,\'\/\\\+&%\$#_]*)?([ ])?(\""?[a-zA-Z0-9А-Яа-я]*\""?)?\)([;\.,\!\?])?";
Regex myRegex = new Regex(strRegex);
string imageSubanswer = String.Empty;
// Создание копии ответа, для корректного занесения в таблицу ответов
string copyAnswer = _answer;
int intervalPoint = activity.ChannelId == "facebook" ? 600 : 3500;
// Проверка длины сообщения. Делается потому, как некоторые мессенджеры имеют ограничения на длину сообщения
if (_answer.Length > intervalPoint)
{
bool wasImages = false;
int startPoint = 0;
while (copyAnswer.Length > intervalPoint)
{
var substringPoint = intervalPoint;
// Данный цикл обрабатывает возможность корректного разделения больших сообщений на более мелкие
// Причем разделение проводится по предложениям (Ориентиром является точка)
while (copyAnswer[substringPoint] != '.')
{
substringPoint--;
}
var subanswer = copyAnswer.Substring(0, substringPoint + 1);
bool img = false;
foreach (Match myMatch in myRegex.Matches(subanswer))
{
if (!myMatch.Success)
{
await context.PostAsync(subanswer);
continue;
}
wasImages = true;
imageSubanswer = subanswer.Substring(startPoint, (myMatch.Index - startPoint) + myMatch.Length);
await context.PostAsync(imageSubanswer);
startPoint = myMatch.Index + myMatch.Length;
img = true;
}
if (!wasImages)
{
await context.PostAsync(subanswer.Substring(startPoint));
}
_answerExistence = true;
if (img)
{
copyAnswer = copyAnswer.Remove(0, startPoint);
startPoint = 0;
}
else
{
copyAnswer = copyAnswer.Remove(0, substringPoint + 1);
}
}
startPoint = 0;
foreach (Match myMatch in myRegex.Matches(copyAnswer))
{
if (!myMatch.Success)
{
await context.PostAsync(copyAnswer.Substring(startPoint));
continue;
}
imageSubanswer = copyAnswer.Substring(startPoint, (myMatch.Index - startPoint) + myMatch.Length);
wasImages = true;
await context.PostAsync(imageSubanswer);
startPoint = myMatch.Index + myMatch.Length;
}
if (!wasImages)
{
await context.PostAsync(copyAnswer);
}
else
{
await context.PostAsync(copyAnswer.Substring(startPoint));
}
_answerExistence = true;
}
else
{
int startPoint = 0;
foreach (Match myMatch in myRegex.Matches(copyAnswer))
{
if (!myMatch.Success)
{
await context.PostAsync(_answer);
continue;
}
imageSubanswer = copyAnswer.Substring(startPoint, (myMatch.Index - startPoint) + myMatch.Length);
await context.PostAsync(imageSubanswer);
startPoint = myMatch.Index + myMatch.Length;
}
await context.PostAsync(copyAnswer.Substring(startPoint));
_answerExistence = true;
}
Thread.Sleep(1500);
CardDialog.SatisfyingAnswer(context, activity);
}
catch //(Exception ex)
{
//throw new Exception(ex.Message);
await context.PostAsync("Что-то пошло не так");
}
}
context.Wait(MessageReceivedAsync);
}
//This function builds the query for the query functionality.
private string[] QueryQueryBuilder(ApiAiRequest request)
{
string table = "";
//If the base parameters don't contains the table (which they probably won't) searches the output contexts for the table's value until it finds it.
if (!string.IsNullOrEmpty(request.queryResult.parameters.Table))
{
table = request.queryResult.parameters.Table;
}
else
{
for (int i = 0; i < request.queryResult.outputContexts.Count(); i++)
{
if (!string.IsNullOrEmpty(request.queryResult.outputContexts[i].parameters.Table))
{
table = request.queryResult.outputContexts[i].parameters.Table;
break;
}
}
}
string query = "SELECT COUNT(*) FROM " + table + " WHERE ";
string query_total = query;
string conditionsforpeople = "";
List <string> conditions = new List <string>();
//Gets the base conditions based on which table the query is using.
//Conditions are resolved through Dialogflow, which takes something the user says such as "Moderate injury" and turns it into InjuryCode = 'C' for use in SQL
switch (table)
{
case "FactPerson":
conditions = request.queryResult.parameters.PersonConditions;
conditionsforpeople += "People" + Environment.NewLine;
break;
case "FactCrash":
conditions = request.queryResult.parameters.CrashConditions;
conditionsforpeople += "Crashes" + Environment.NewLine;
break;
case "FactVehicle":
conditions = request.queryResult.parameters.VehicleConditions;
conditionsforpeople += "Vehicles" + Environment.NewLine;
break;
}
List <string> conditionsintvars = GetIntVars(request, table);
conditions.AddRange(conditionsintvars);
//Every (non-int) condition resolved through Dialogflow has a 2nd part. The full string is something like Injury='C';Injury: Moderate.
//The purpose of the 2nd part of the string is to provide the user with a list of conditions that is readable to them.
//We want the user to make sure the number is actually what they were looking for, and they obviously wouldn't know what InjuryCode = 'C' means.
//So if a condition in this for loop has a semicolon, it splits the string into two parts, the first for the SQL query and the 2nd for the user.
for (int i = 0; i < conditions.Count(); i++)
{
if (conditions[i].Contains(";"))
{
string[] s = conditions[i].Split(';');
query += s[0] + " AND ";
conditionsforpeople += s[1] + Environment.NewLine;
}
else
{
query += conditions[i] + " AND ";
conditionsforpeople += conditions[i] + Environment.NewLine;
}
}
//Queries can be either for a single year or a range of years. If year2 is empty it's for a single year.
if (!string.IsNullOrEmpty(request.queryResult.parameters.year2))
{
string temp = "";
string year1 = request.queryResult.parameters.year1;
string year2 = request.queryResult.parameters.year2;
//Puts whichever year is smaller first.
if (int.Parse(year2) > int.Parse(year1))
{
if (table == "FactCrash")
{
temp = "LEFT(DateSK, 4) >= '" + request.queryResult.parameters.year1 + "'";
temp += " AND LEFT(DateSK, 4) <= '" + request.queryResult.parameters.year2 + "'";
}
else
{
temp = "CrashSK in (select CrashPK from FactCrash where LEFT(DateSK, 4) >= '" + request.queryResult.parameters.year1 + "')";
temp += " AND CrashSK in (select CrashPK from FactCrash where LEFT(DateSK, 4) <= '" + request.queryResult.parameters.year2 + "')";
}
conditionsforpeople += "From " + year1 + "-" + year2 + Environment.NewLine;
}
else
{
if (table == "FactCrash")
{
temp = "LEFT(DateSK, 4) <= '" + request.queryResult.parameters.year1 + "'";
temp += " AND LEFT(DateSK, 4) >= '" + request.queryResult.parameters.year2 + "'";
}
else
{
temp = "CrashSK in (select CrashPK from FactCrash where LEFT(DateSK, 4) <= '" + request.queryResult.parameters.year1 + "')";
temp += " AND CrashSK in (select CrashPK from FactCrash where LEFT(DateSK, 4) >= '" + request.queryResult.parameters.year2 + "')";
}
conditionsforpeople += "From " + year2 + "-" + year1 + Environment.NewLine;
}
query += temp;
query_total += temp;
}
else
{
string temp = "";
if (table == "FactCrash")
{
temp = "LEFT(DateSK, 4) = '" + request.queryResult.parameters.year1 + "'";
}
else
{
temp = "CrashSK in (select CrashPK from FactCrash where LEFT(DateSK, 4) = '" + request.queryResult.parameters.year1 + "')";
}
query += temp;
query_total += temp;
conditionsforpeople += "In " + request.queryResult.parameters.year1 + Environment.NewLine;
}
string[] ts = { query, conditionsforpeople, query_total };
return(ts);
}