public JsonResult getTumdQuery(string Params) { MVCControlClass.ApiResult aResult = new MVCControlClass.ApiResult(); dynamic dParams = JsonConvert.DeserializeObject <dynamic>(Params); string station = dParams["station"]; //string ssql = @" select top 20 * // from stocktumd // order by stockdate desc // "; //for mysql string ssql = @" select * from stocktumd order by stockdate desc "; List <StockTumd> data = dbDapper.Query <StockTumd>(ssql); // // 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 = data; return(this.Json(aResult, JsonRequestBehavior.AllowGet)); }
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)); }
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)); }