private AnalysisFormViewModel AnalyzingSearchInputs(DateTime startDate, DateTime endDate, Granulation granulation)
{
Tuple <List <string>, string, string, string> query = granulation == Granulation.Day ?
Query.AnalysisByDays(startDate, endDate) :
Query.AnalysisByHours(startDate, endDate);
using (NpgsqlConnection connection = new NpgsqlConnection(ConfigurationManager.ConnectionStrings[GlobalVariables.ConnectionStringName].ConnectionString))
{
connection.Open();
using (var createTempTable = new NpgsqlCommand(query.Item2, connection))
{
createTempTable.ExecuteNonQuery();
}
List <AnalysisResult> analysisList = new List <AnalysisResult>();
NpgsqlCommand pivotTable = new NpgsqlCommand(query.Item3, connection);
using (NpgsqlDataReader dataReader = pivotTable.ExecuteReader())
{
while (dataReader.Read())
{
var temp = new AnalysisResult()
{
SearchPattern = dataReader[0].ToString(),
DatesOrHours = new List <string>()
};
for (int i = 1; i <= query.Item1.Count; i++)
{
temp.DatesOrHours.Add(dataReader[i].ToString());
}
analysisList.Add(temp);
}
pivotTable.Dispose();
}
using (var deleteTempTable = new NpgsqlCommand(query.Item4, connection))
{
deleteTempTable.ExecuteNonQuery();
}
return(new AnalysisFormViewModel()
{
StartDate = startDate,
EndDate = endDate,
Granulation = (int)granulation,
AnalysisResults = new Tuple <List <string>, List <AnalysisResult> >(query.Item1, analysisList)
});
}
}
public Tuple <List <string>, List <Result> > Execute(DateTime startDate, DateTime endDate, Granulation gran)
{
using (var conn = new NpgsqlConnection(connectionString))
{
conn.Open();
var gen = gran == Granulation.Days ? generateDaysQuery(startDate, endDate) : generateHoursQuery(startDate, endDate);
using (var insert = new NpgsqlCommand(gen.Item2, conn))
{
insert.ExecuteNonQuery();
}
var resp = new List <Result>();
var select = new NpgsqlCommand(gen.Item3, conn);
using (var reader = select.ExecuteReader())
{
while (reader.Read())
{
var res = new Result()
{
SearchPattern = reader[0].ToString()
};
for (int i = 1; i <= gen.Item1.Count; i++)
{
res.Times.Add(reader[i].ToString());
}
resp.Add(res);
}
}
select.Dispose();
using (var del = new NpgsqlCommand(gen.Item4, conn))
{
del.ExecuteNonQuery();
}
return(new Tuple <List <string>, List <Result> >(gen.Item1, resp));
}
}
