public JsonResult getQueryXmlResult(string Params)
{
MVCControlClass.ApiResult aResult = new MVCControlClass.ApiResult();
dynamic dParams = JsonConvert.DeserializeObject <dynamic>(Params);
string station = dParams["station"];
string sdtstart = dParams["dtstart"];
string sdtend = dParams["dtend"];
DateTime dtstart = Utils.getStringToDateTime(sdtstart);
DateTime dtend = Utils.getStringToDateTime(sdtend);
string RG = dParams["RG"];
string TM = dParams["TM"];
string PM = dParams["PM"];
string GW = dParams["GW"];
string stimerange = dParams["timerange"];
List <string> lstSensor = new List <string>();
if (RG == "Y")
{
lstSensor.Add("RG");
}
if (TM == "Y")
{
lstSensor.Add("TM");
//DS011_01該站特殊TM有兩個
lstSensor.Add("TM1");
lstSensor.Add("TM2");
}
if (PM == "Y")
{
lstSensor.Add("PM");
}
if (GW == "Y")
{
lstSensor.Add("GW");
}
string sInSensor = string.Join("','", lstSensor.ToArray());
//轉換當天最大值
dtend = Utils.getStringToDateTime(dtend.ToString("yyyy-MM-dd 23:59:59"));
// 20200429 只顯示小時數據
ssql = @"
select a.*,b.datavalue from Result10MinData a
left join BasM11Setting b on a.siteid = b.dataitem and b.datatype = 'SiteCName'
where StationID = '{0}'
and SensorID in ( '{1}')
and DatetimeString between '{2}' and '{3}'
";
if (stimerange == "hr")
{
ssql = ssql + " and DatetimeString like '%:00:00' ";
}
ssql = string.Format(ssql, station, sInSensor, M11Utils.M11DatetimeToString(dtstart), M11Utils.M11DatetimeToString(dtend));
ssql = ssql + " order by StationID,SensorID,DatetimeString desc ";
List <Result10MinData> Stations = dbDapper.Query <Result10MinData>(ssql);
aResult.ApiResultStauts = "Y";
aResult.Data = Stations;
return(this.Json(aResult, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// CGI資料移動到備份資料夾(傳入的FileInfo檔案不刪除)
/// </summary>
/// <param name="fi"></param>
public void M11AddendumXMLByTime(DateTime dtCheck)
{
bool hasDbData = false;
bool hasXMLData = false;
ShowMessageToFront("M11資料補遺(XML、資料庫)==[檢核資料]" + M11Utils.M11DatetimeToString(dtCheck));
//判斷資料庫該時刻是否有資料
ssql = @"
select * from Result10MinData
where DatetimeString = '{0}'
";
ssql = string.Format(ssql, M11Utils.M11DatetimeToString(dtCheck));
List <Result10MinData> lstTemp = dbDapper.Query <Result10MinData>(ssql);
//該時刻有資料不用補
if (lstTemp.Count > 0)
{
hasDbData = true;
}
//取得資料庫最接近時刻的資料
if (hasDbData == false)
{
ShowMessageToFront("M11資料補遺(XML、資料庫)==[補遺]" + M11Utils.M11DatetimeToString(dtCheck));
//取得前一個時段的資料
//DateTime dtPre = dtCheck.AddMinutes(-10);
DateTime dtStart = dtCheck.AddDays(-10);
//取得十天中最接近現在時刻的資料
ssql = @"
select * from Result10MinData
where DatetimeString = (
select top 1 DatetimeString from Result10MinData
where DatetimeString between '{0}' and '{1}'
order by DatetimeString desc
)
";
ssql = string.Format(ssql, M11Utils.M11DatetimeToString(dtStart), M11Utils.M11DatetimeToString(dtCheck));
List <Result10MinData> lstPreTemp = dbDapper.Query <Result10MinData>(ssql);
foreach (Result10MinData item in lstPreTemp)
{
item.Datetime = dtCheck;
item.DatetimeString = M11Utils.M11DatetimeToString(dtCheck);
item.GetTime = M11Utils.M11DatetimeToString(dtCheck);
//寫入前一個時刻的資料
dbDapper.Insert <Result10MinData>(item);
}
}
//判斷檔案時刻是否有資料
//取得檔案前一時刻的資料
//string[] CgiNameSplit = fi.Name.Replace(fi.Extension, "").Split('-');
////避免舊檔案格式問題，排除沒有分析完整的檔案名稱
//if (CgiNameSplit.Length != 8)
//{
// return;
//}
////從檔案取得資料時間
//DateTime dt = DateTime.ParseExact(CgiNameSplit[2] + CgiNameSplit[3] + CgiNameSplit[4] + CgiNameSplit[5] + CgiNameSplit[6] + CgiNameSplit[7], "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
////建立資料夾
////string sFolderYear = dt.ToString("yyyy");
////string sFolderMonth = dt.ToString("yyyyMM");
//string sFolderDay = dt.ToString("yyyyMMdd");
////string sBackupFolder = Path.Combine(M11Const.Path_BackupCGIData, sFolderYear, sFolderMonth, sFolderDay);
//string sBackupFolder = Path.Combine(M11Const.Path_BackupCGIData, sFolderDay);
//Directory.CreateDirectory(sBackupFolder);
////存至備份資料夾
//fi.CopyTo(Path.Combine(sBackupFolder, fi.Name), true);
}
public ActionResult DownRainStatistics(string Params)
{
MVCControlClass.ApiResult aResult = new MVCControlClass.ApiResult();
dynamic dParams = JsonConvert.DeserializeObject <dynamic>(Params);
string station = dParams["station"];
string sdtstart = dParams["dtstart"];
string sdtend = dParams["dtend"];
DateTime dtstart = Utils.getStringToDateTime(sdtstart);
DateTime dtend = Utils.getStringToDateTime(sdtend);
string RG = dParams["RG"];
string TM = dParams["TM"];
string PM = dParams["PM"];
string GW = dParams["GW"];
string stimerange = dParams["timerange"];
List <string> lstSensor = new List <string>();
if (RG == "Y")
{
lstSensor.Add("RG");
}
if (TM == "Y")
{
lstSensor.Add("TM");
//DS011_01該站特殊TM有兩個
lstSensor.Add("TM1");
lstSensor.Add("TM2");
}
if (PM == "Y")
{
lstSensor.Add("PM");
}
if (GW == "Y")
{
lstSensor.Add("GW");
}
string sInSensor = string.Join("','", lstSensor.ToArray());
//轉換當天最大值
dtend = Utils.getStringToDateTime(dtend.ToString("yyyy-MM-dd 23:59:59"));
// 20200429 只顯示小時數據
ssql = @"
select distinct a.SiteID, a.StationID,a.DatetimeString,b.datavalue
,RG.value RGvalue,TM.value TMvalue,PM.value PMvalue,GW.value GWvalue,TM1.value TM1value,TM2.value TM2value
from Result10MinData a
left join BasM11Setting b on a.siteid = b.dataitem and b.datatype = 'SiteCName'
left join Result10MinData RG on a.StationID = RG.StationID and RG.SensorID = 'RG' and a.DatetimeString = RG.DatetimeString
left join Result10MinData TM on a.StationID = TM.StationID and TM.SensorID = 'TM' and a.DatetimeString = TM.DatetimeString
left join Result10MinData PM on a.StationID = PM.StationID and PM.SensorID = 'PM' and a.DatetimeString = PM.DatetimeString
left join Result10MinData GW on a.StationID = GW.StationID and GW.SensorID = 'GW' and a.DatetimeString = GW.DatetimeString
left join Result10MinData TM1 on a.StationID = TM1.StationID and TM1.SensorID = 'TM1' and a.DatetimeString = TM1.DatetimeString
left join Result10MinData TM2 on a.StationID = TM2.StationID and TM2.SensorID = 'TM2' and a.DatetimeString = TM2.DatetimeString
where a.StationID = '{0}'
and a.DatetimeString between '{2}' and '{3}'
";
if (stimerange == "hr")
{
ssql = ssql + " and a.DatetimeString like '%:00:00' ";
}
ssql = string.Format(ssql, station, sInSensor, M11Utils.M11DatetimeToString(dtstart), M11Utils.M11DatetimeToString(dtend));
ssql = ssql + " order by a.StationID,a.DatetimeString desc ";
//List<Result10MinData> Result10MinDatas = dbDapper.Query<Result10MinData>(ssql);
List <dynamic> Result10MinDatas = dbDapper.Query <dynamic>(ssql);
//建立Excel欄位
List <string> head = new List <string>();
head.Add("潛勢區");
head.Add("潛勢區名稱");
head.Add("測站");
head.Add("時間");
//head.Add("數據個數");
//[RG]
head.Add("[RG]10分鐘累積雨量(mm)");
head.Add("[RG]1小時累積雨量(mm)");
head.Add("[RG]3小時累積雨量(mm)");
head.Add("[RG]6小時累積雨量(mm)");
head.Add("[RG]12小時累積雨量(mm)");
head.Add("[RG]24小時累積雨量(mm)");
head.Add("[RG]48小時累積雨量(mm)");
head.Add("[RG]72小時累積雨量(mm)");
//TM
head.Add("[TM]方位一觀測值(秒)");
head.Add("[TM]方位二觀測值(秒)");
head.Add("[TM]方位一累積變位量(秒)");
head.Add("[TM]方位二累積變位量(秒)");
head.Add("[TM]方位一速率(秒/天)");
head.Add("[TM]方位二速率(秒/天)");
//PM
head.Add("[PM]水位高(m)");
head.Add("[PM]相對水位高(m)");
//GW
head.Add("[GW]水位高(m)");
head.Add("[GW]相對水位高(m)");
//TM1
head.Add("[TM1]方位一觀測值(秒)");
head.Add("[TM1]方位二觀測值(秒)");
head.Add("[TM1]方位一累積變位量(秒)");
head.Add("[TM1]方位二累積變位量(秒)");
head.Add("[TM1]方位一速率(秒/天)");
head.Add("[TM1]方位二速率(秒/天)");
//TM2
head.Add("[TM2]方位一觀測值(秒)");
head.Add("[TM2]方位二觀測值(秒)");
head.Add("[TM2]方位一累積變位量(秒)");
head.Add("[TM2]方位二累積變位量(秒)");
head.Add("[TM2]方位一速率(秒/天)");
head.Add("[TM2]方位二速率(秒/天)");
//Func<string,string,List<string>> SensorValuePaser
List <string[]> datas = new List <string[]>();
foreach (dynamic item in Result10MinDatas)
{
List <string> cols = new List <string>();
cols.Add(item.SiteID);
cols.Add(item.datavalue);
cols.Add(item.StationID);
//cols.Add(item.SensorID);
cols.Add(item.DatetimeString);
//cols.Add(item.observation_num);
//[RG]數據拆開各自一個欄位
List <string> lstTmp = new List <string>();
for (int i = 0; i < 8; i++)
{
lstTmp.Add("");
}
if (item.RGvalue != null)
{
string[] aValue = item.RGvalue.Split(' ');
for (int i = 0; i < aValue.Length; i++)
{
lstTmp[i] = aValue[i];
}
}
foreach (string value in lstTmp)
{
cols.Add(value);
}
//[TM]數據拆開各自一個欄位
lstTmp.Clear();
for (int i = 0; i < 6; i++)
{
lstTmp.Add("");
}
if (item.TMvalue != null)
{
string[] aValue = item.TMvalue.Split(' ');
for (int i = 0; i < aValue.Length; i++)
{
lstTmp[i] = aValue[i];
}
}
foreach (string value in lstTmp)
{
cols.Add(value);
}
//[PM]數據拆開各自一個欄位
lstTmp.Clear();
for (int i = 0; i < 2; i++)
{
lstTmp.Add("");
}
if (item.PMvalue != null)
{
string[] aValue = item.PMvalue.Split(' ');
for (int i = 0; i < aValue.Length; i++)
{
lstTmp[i] = aValue[i];
}
}
foreach (string value in lstTmp)
{
cols.Add(value);
}
//[GW]數據拆開各自一個欄位
lstTmp.Clear();
for (int i = 0; i < 2; i++)
{
lstTmp.Add("");
}
if (item.GWvalue != null)
{
string[] aValue = item.GWvalue.Split(' ');
for (int i = 0; i < aValue.Length; i++)
{
lstTmp[i] = aValue[i];
}
}
foreach (string value in lstTmp)
{
cols.Add(value);
}
//[TM1]數據拆開各自一個欄位
lstTmp.Clear();
for (int i = 0; i < 6; i++)
{
lstTmp.Add("");
}
if (item.TM1value != null)
{
string[] aValue = item.TM1value.Split(' ');
for (int i = 0; i < aValue.Length; i++)
{
lstTmp[i] = aValue[i];
}
}
foreach (string value in lstTmp)
{
cols.Add(value);
}
//[TM2]數據拆開各自一個欄位
lstTmp.Clear();
for (int i = 0; i < 6; i++)
{
lstTmp.Add("");
}
if (item.TM2value != null)
{
string[] aValue = item.TM2value.Split(' ');
for (int i = 0; i < aValue.Length; i++)
{
lstTmp[i] = aValue[i];
}
}
foreach (string value in lstTmp)
{
cols.Add(value);
}
datas.Add(cols.ToArray());
}
//產生檔案路徑
string sGUID = Guid.NewGuid().ToString();
string sTempPath = Path.Combine(Server.MapPath("~/temp/"), "QueryXmlResult", sGUID);
//建立資料夾
Directory.CreateDirectory(sTempPath);
string sFileName = string.Format("QueryXmlResult_{0}_{1}.xlsx", dtstart.ToString("yyyyMMdd"), dtend.ToString("yyyyMMdd"));
string sSaveFilePath = Path.Combine(sTempPath, sFileName);
//DataTable dt = Utils.ConvertToDataTable<RainStation>(wrs);
//DataTable dt = new DataTable();
DataExport de = new DataExport();
//Boolean bSuccess = de.ExportBigDataToCsv(sSaveFilePath, dt);
Boolean bSuccess = de.ExportListToExcel(sSaveFilePath, head, datas);
//產生提供下載的路徑，到前端後，提供下載
string sFileDownUrl = Request.Url.Scheme + "://" + Request.Url.Authority + Url.Content("~/temp/QueryXmlResult/" + sGUID + "/") + sFileName;
//if (bSuccess)
//{
// string filename = string.Format("WeaRainStatistics_{0}_{1}.xlsx", "", "");
// //ASP.NET 回應大型檔案的注意事項
// //http://blog.miniasp.com/post/2008/03/11/Caution-about-ASPNET-Response-a-Large-File.aspx
// //***** 下載檔案過大，使用特殊方法 *****
// HttpContext context = System.Web.HttpContext.Current;
// context.Response.TransmitFile(sSaveFilePath);
// context.Response.ContentType = "application/vnd.ms-excel";
// context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
// Response.End();
//}
//dynamic dd = new { test1:sSaveFilePath};
aResult.ApiResultStauts = "Y";
aResult.Data = sFileDownUrl;
return(this.Json(aResult, JsonRequestBehavior.AllowGet));
}