public IActionResult OnPostExecuteQuery(string query, string connections)
{
CloudQuery.Models.QueryResult qr = new Models.QueryResult();
qr.Message = "Query execution incomplete";
// get the connectionObject
var conn = Newtonsoft.Json.JsonConvert.DeserializeObject <CloudQuery.Models.DBConnection>(connections);
if (conn.DBName.StartsWith("#"))
{
PopulateConnectionDataFromMount(conn);
}
string connectionString = string.Format("Server=tcp:{0}.database.windows.net,1433;Database={1};Uid={2}@{0};Pwd={3};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;KeyStoreAuthentication=KeyVaultManagedIdentity", conn.DBServer, conn.DBName, conn.Username, conn.Password);
if ("true".CompareTo(System.Environment.GetEnvironmentVariable("MSI")) == 0)
{
connectionString = string.Format("Server=tcp:{0}.database.windows.net,1433;Database={1};Uid={2}@{0};Authentication=ActiveDirectoryMsi;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30", conn.DBServer, conn.DBName, conn.Username, conn.Password);
}
else if ("true".CompareTo(System.Environment.GetEnvironmentVariable("ODBC_ALWAYSENCRYPTED")) == 0)
{
connectionString = string.Format("Server=tcp:{0}.database.windows.net,1433;Database={1};Uid={2};Authentication=ActiveDirectoryMsi;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultManagedIdentity;KeyStorePrincipalId={2}", conn.DBServer, conn.DBName, conn.Username, conn.Password);
}
connectionString = "Driver={ODBC Driver 17 for SQL Server};" + connectionString;
Console.WriteLine($"ConnectionString: {connectionString}");
try
{
int rowsReturned = 0;
System.Data.DataSet ds;
using (System.Data.Odbc.OdbcDataAdapter adapter = new System.Data.Odbc.OdbcDataAdapter(query, connectionString))
{
ds = new System.Data.DataSet();
rowsReturned = adapter.Fill(ds);
}
// only selects will return a Table of results;
if (ds.Tables.Count == 0) // not a table
{
qr.Message = "Query completed successfully";
}
else if (ds.Tables.Count == 1)
{
// return the results
qr.Results = ds.Tables[0];
// return rowsAffected for message
qr.Message = string.Format("{0} rows returned.", rowsReturned);
}
}
catch (Exception ex)
{
qr.Exception = ex;
qr.Message = ex.Message;
}
ViewData["results"] = qr;
return(View("Index"));
}
//GET: transfer/statistic
/// <summary>
/// 获取数据转储统计信息，这里时间为年月日，直接在表中取当天9时-第二天8时之间的所有数据。只能查询一天的数据。
/// @param date:YYYY-MM-DD
/// </summary>
/// <returns></returns>
public ActionResult statistic()
{
DateTime from;
int id = 0;
try
{
from = DateTime.Parse(Request.Params.Get("date"));
id = int.Parse(Request.Params.Get("isubcenter"));
}
catch (Exception e)
{
return(Json(helper.DBHelper.SerializeDBReturnCode(helper.DBReturnCode.BAD_REQUEST), JsonRequestBehavior.AllowGet));
}
Models.QueryResult result = new Models.QueryResult();
Models.Maintain db = new Models.Maintain();
List <Models.Station> stations = DAL.StationService.ListStationBySubcenterId(id);
List <Models.M_status> datas = DAL.TransferDataService.ListMonitorData(db, from, 1); //仅查一天的数据
foreach (Models.Station station in stations)
{
Models.M_status data = datas.FirstOrDefault(i => i.STCD == station.stationId);
Models.TransferStatisticRecord record = new Models.TransferStatisticRecord(station, data);
result.records.Add(record);
}
JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
jsSerializer.MaxJsonLength = Int32.MaxValue;
string json = jsSerializer.Serialize(result);
return(Json(json, JsonRequestBehavior.AllowGet));
}
// GET:rain/realTime
//TODO 实时降雨的查询
public ActionResult realTime()
{
//DateTime from = Convert.ToDateTime(Request.Params.Get("from"));
//DateTime to = Convert.ToDateTime(Request.Params.Get("to"));
//string stationId = Request.Params.Get("station-id");
//var list = new List<KeyValuePair<string, IQueryable<Models.rain>>>();
//list.Add(new KeyValuePair<string, IQueryable<Models.rain>>("data", DAL.RainHandler.singleRainRecord(from, to, stationId)));
//ViewData.Add("data",list);
//ViewBag.Title = "实时降雨信息";
//return View();
/*显示所有站点的今日降雨*/
#region 初始化查询条件
DateTime today = DateTime.Now;
//还不到8点，今日按照从昨天早上8点开始计算
if (today.Hour <= 8)
{
today = today.AddDays(-1);
}
DateTime from = new DateTime(
year: today.Year,
month: today.Month,
day: today.Day,
hour: 8,
minute: 0,
second: 0
);
DateTime to = new DateTime(
year: today.Year,
month: today.Month,
day: today.Day + 1,
hour: DateTime.Now.Hour,
minute: 0,
second: 0
);
long?subcenterId = null;
List <Models.Station> stations = DAL.StationHandler.station(subcenterId);
#endregion
//初始化查询结果
Models.QueryResult result = new Models.QueryResult();
//为查询结果添加表头
result.dataTitles.Add("站号");
result.dataTitles.Add("站名");
result.dataTitles.Add("站类");
result.dataTitles.Add("所在地");
result.dataTitles.Add("累计");
DateTime current = DateTime.Now;
List <DateTime> period = new List <DateTime>();
while (from < current)
{
period.Add(current);
result.dataTitles.Add((current.AddHours(-1).Hour) + "-" + current.Hour);
current = current.AddHours(-1);
}
foreach (Models.Station station in stations)
{
IQueryable <Models.rain> queryResult = DAL.RainHandler.singleRainRecord(from, to, station.stationId);
//添加累计和
Models.Record record = new Models.Record();
decimal sum = queryResult.Where(r => r.datatime <= period.First() && r.datatime >= period.Last()).Sum(r => r.periodrain).Value;
record.station = station;
record.datas.Add(sum);
//每个时间段都分别求出结果
foreach (DateTime time in period)
{
decimal data = queryResult.Where(r => r.datatime < time.AddHours(1) && r.datatime >= time).Sum(r => r.periodrain).Value;
record.datas.Add(data);
}
result.records.Add(record);
ViewData.Add("table", result);
}
return(View());
}
