private List <DataResponseDTO> getDataForCountryFromDb(DataRequestDTO dataRequestDTO)
{
List <DataResponseDTO> result = new List <DataResponseDTO>();
string sqlQuery = buildDataRequestQuery(dataRequestDTO);
try
{
string conString = ConfigurationExtensions.GetConnectionString(_configuration, "Default");
using (SqlConnection connection = new SqlConnection(conString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DataResponseDTO tempEntry = new DataResponseDTO
{
CountryName = reader.GetString(0),
Year = Convert.ToInt32(reader.GetString(1))
};
Dictionary <string, int> langsDictionary = new Dictionary <string, int>();
for (int i = 0; i < dataRequestDTO.Languages.Count; i++)
{
langsDictionary.Add(dataRequestDTO.Languages[i], reader.GetInt32(i + 2));
}
;
tempEntry.LanguagesPercent = langsDictionary;
result.Add(tempEntry);
}
}
}
}
}
catch (SqlException e)
{
result = new List <DataResponseDTO>();
}
return(result);
}
public async Task <IActionResult> GetDataForCountry(DataRequestDTO dataRequestDTO)
{
if (ModelState.IsValid)
{
try
{
var data = await _deliveryService.GetDataForCountry(dataRequestDTO);
var payload = JsonSerializer.Serialize(data);
return(Ok(payload));
}
catch (Exception)
{
return(BadRequest());
}
}
;
return(BadRequest());
}
private string buildDataRequestQuery(DataRequestDTO dataRequestDTO)
{
string countryName = dataRequestDTO.CountryName;
string alternateCountryName = getAlternateCountryName(countryName);
int[] years = { 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020 };
StringBuilder sb = new StringBuilder();
foreach (var year in years)
{
sb.Append("SELECT Country,");
sb.Append($"'{year}' as SurveyYear,");
for (int i = 0; i < dataRequestDTO.Languages.Count; i++)
{
string language = dataRequestDTO.Languages[i];
if (language.Equals("CPlusPlus"))
{
sb.Append($@"(
(SELECT COUNT(*) FROM SurveyResponses{year} WHERE (Country = '{countryName}' OR Country = '{alternateCountryName}') AND LanguagePro{language} = 'C++') * 100 /
(SELECT COUNT(*) FROM SurveyResponses{year} WHERE Country = '{countryName}' OR Country = '{alternateCountryName}')
)
as '{language}_Percent'" );
}
else if (language.Equals("CSharp"))
{
sb.Append($@"(
(SELECT COUNT(*) FROM SurveyResponses{year} WHERE (Country = '{countryName}' OR Country = '{alternateCountryName}') AND LanguagePro{language} = 'C#') * 100 /
(SELECT COUNT(*) FROM SurveyResponses{year} WHERE Country = '{countryName}' OR Country = '{alternateCountryName}')
)
as '{language}_Percent'" );
}
else if (language.Equals("ObjectiveC"))
{
sb.Append($@"(
(SELECT COUNT(*) FROM SurveyResponses{year} WHERE (Country = '{countryName}' OR Country = '{alternateCountryName}') AND LanguagePro{language} = 'Objective-C') * 100 /
(SELECT COUNT(*) FROM SurveyResponses{year} WHERE Country = '{countryName}' OR Country = '{alternateCountryName}')
)
as '{language}_Percent'" );
}
else
{
sb.Append($@"(
(SELECT COUNT(*) FROM SurveyResponses{year} WHERE (Country = '{countryName}' OR Country = '{alternateCountryName}') AND LanguagePro{language} = '{language}') * 100 /
(SELECT COUNT(*) FROM SurveyResponses{year} WHERE Country = '{countryName}' OR Country = '{alternateCountryName}')
)
as '{language}_Percent'
");
}
if (i < dataRequestDTO.Languages.Count - 1)
{
sb.Append(",");
}
}
sb.Append($"FROM SurveyResponses{year} WHERE Country = '{countryName}' OR Country = '{alternateCountryName}'");
if (year != 2020)
{
sb.Append("UNION ");
}
}
sb.Append("GROUP BY Country ORDER BY SurveyYear");
string dataQuery = sb.ToString();
return(dataQuery);
}
public async Task <List <DataResponseDTO> > GetDataForCountry(DataRequestDTO dataRequestDTO)
{
var data = await Task.Run(() => getDataForCountryFromDb(dataRequestDTO));
return(data);
}
