public ActionResult DownRainStatistics(string stid)
{
string ssql = @" select * from WeaRainStatistics where stid = @stid ";
List <WeaRainStatistics> wrs = dbDapper.Query <WeaRainStatistics>(ssql, new { stid = stid });
List <dynamic> data = dbDapper.Query(ssql, new { stid = stid });
foreach (var LoopItem in data)
{
if (LoopItem.m01 == -99)
{
LoopItem.m01 = "*";
}
if (LoopItem.m02 == -99)
{
LoopItem.m02 = "*";
}
if (LoopItem.m03 == -99)
{
LoopItem.m03 = "*";
}
if (LoopItem.m04 == -99)
{
LoopItem.m04 = "*";
}
if (LoopItem.m05 == -99)
{
LoopItem.m05 = "*";
}
if (LoopItem.m06 == -99)
{
LoopItem.m06 = "*";
}
if (LoopItem.m07 == -99)
{
LoopItem.m07 = "*";
}
if (LoopItem.m08 == -99)
{
LoopItem.m08 = "*";
}
if (LoopItem.m09 == -99)
{
LoopItem.m09 = "*";
}
if (LoopItem.m10 == -99)
{
LoopItem.m10 = "*";
}
if (LoopItem.m11 == -99)
{
LoopItem.m11 = "*";
}
if (LoopItem.m12 == -99)
{
LoopItem.m12 = "*";
}
if (LoopItem.yearsum == -99)
{
LoopItem.yearsum = "*";
}
}
List <decimal> m01List = wrs.Select(c => c.m01).ToList <decimal>();
List <decimal> m02List = wrs.Select(c => c.m02).ToList <decimal>();
List <decimal> m03List = wrs.Select(c => c.m03).ToList <decimal>();
List <decimal> m04List = wrs.Select(c => c.m04).ToList <decimal>();
List <decimal> m05List = wrs.Select(c => c.m05).ToList <decimal>();
List <decimal> m06List = wrs.Select(c => c.m06).ToList <decimal>();
List <decimal> m07List = wrs.Select(c => c.m07).ToList <decimal>();
List <decimal> m08List = wrs.Select(c => c.m08).ToList <decimal>();
List <decimal> m09List = wrs.Select(c => c.m09).ToList <decimal>();
List <decimal> m10List = wrs.Select(c => c.m10).ToList <decimal>();
List <decimal> m11List = wrs.Select(c => c.m11).ToList <decimal>();
List <decimal> m12List = wrs.Select(c => c.m12).ToList <decimal>();
List <decimal> mavgList = wrs.Select(c => c.mavg).ToList <decimal>();
List <decimal> yearsumList = wrs.Select(c => c.yearsum).ToList <decimal>();
//平均雨量統計
WeaRainStatistics test = new WeaRainStatistics();
test.year = "平均雨量";
test.m01 = RainControlUtil.CalcRainAvg(m01List);
test.m02 = RainControlUtil.CalcRainAvg(m02List);
test.m03 = RainControlUtil.CalcRainAvg(m03List);
test.m04 = RainControlUtil.CalcRainAvg(m04List);
test.m05 = RainControlUtil.CalcRainAvg(m05List);
test.m06 = RainControlUtil.CalcRainAvg(m06List);
test.m07 = RainControlUtil.CalcRainAvg(m07List);
test.m08 = RainControlUtil.CalcRainAvg(m08List);
test.m09 = RainControlUtil.CalcRainAvg(m09List);
test.m10 = RainControlUtil.CalcRainAvg(m10List);
test.m11 = RainControlUtil.CalcRainAvg(m11List);
test.m12 = RainControlUtil.CalcRainAvg(m12List);
test.mavg = RainControlUtil.CalcRainAvg(mavgList);
test.yearsum = RainControlUtil.CalcRainAvg(yearsumList);
wrs.Add(test);
List <string> head = new List <string>();
head.Add("年份");
head.Add("1月");
head.Add("2月");
head.Add("3月");
head.Add("4月");
head.Add("5月");
head.Add("6月");
head.Add("7月");
head.Add("8月");
head.Add("9月");
head.Add("10月");
head.Add("11月");
head.Add("12月");
head.Add("月平均");
head.Add("年雨量");
head.Add("最大1日雨量");
head.Add("最大1日發生日");
head.Add("最大2日雨量");
head.Add("最大2日發生日");
head.Add("最大3日雨量");
head.Add("最大3日發生日");
head.Add("降雨日數");
List <string[]> datas = new List <string[]>();
foreach (WeaRainStatistics item in wrs)
{
List <string> cols = new List <string>();
cols.Add(item.year);
cols.Add(item.m01 == -99 ? "*" : item.m01.ToString());
cols.Add(item.m02 == -99 ? "*" : item.m02.ToString());
cols.Add(item.m03 == -99 ? "*" : item.m03.ToString());
cols.Add(item.m04 == -99 ? "*" : item.m04.ToString());
cols.Add(item.m05 == -99 ? "*" : item.m05.ToString());
cols.Add(item.m06 == -99 ? "*" : item.m06.ToString());
cols.Add(item.m07 == -99 ? "*" : item.m07.ToString());
cols.Add(item.m08 == -99 ? "*" : item.m08.ToString());
cols.Add(item.m09 == -99 ? "*" : item.m09.ToString());
cols.Add(item.m10 == -99 ? "*" : item.m10.ToString());
cols.Add(item.m11 == -99 ? "*" : item.m11.ToString());
cols.Add(item.m12 == -99 ? "*" : item.m12.ToString());
cols.Add(item.mavg.ToString());
cols.Add(item.yearsum == -99 ? "*" : item.yearsum.ToString());
cols.Add(item.max1.ToString());
cols.Add(item.max1date);
cols.Add(item.max2.ToString());
cols.Add(item.max2date);
cols.Add(item.max3.ToString());
cols.Add(item.max3date);
cols.Add(item.raindatecount.ToString());
datas.Add(cols.ToArray());
}
//產生檔案路徑
string sTempPath = Path.Combine(Server.MapPath("~/temp/"), DateTime.Now.ToString("yyyyMMdd"));
//建立資料夾
Directory.CreateDirectory(sTempPath);
string sFileName = "WeaRainStatistics_" + Guid.NewGuid().ToString() + ".xlsx";
string sSaveFilePath = Path.Combine(sTempPath, "WeaRainStatistics_" + Guid.NewGuid().ToString() + ".xlsx");
//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);
if (bSuccess)
{
string filename = string.Format("WeaRainStatistics_{0}_{1}.xlsx", stid, stid);
//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();
}
return(null);
}
public ActionResult DownSoilWaterRainHis(string sd, string ed, string Station)
{
DateTime dtStart;
DateTime dtEnd;
sd = sd.Replace(' ', '-');
ed = ed.Replace(' ', '-');
if (string.IsNullOrEmpty(sd) || string.IsNullOrEmpty(ed))
{
dtStart = DateTime.Now;
dtEnd = DateTime.Now;
}
else
{
string[] aSdt = sd.Split('-');
string[] aEdt = ed.Split('-');
dtStart = new DateTime(Convert.ToInt32(aSdt[0]), Convert.ToInt32(aSdt[1]), Convert.ToInt32(aSdt[2]), Convert.ToInt32(aSdt[3]), 0, 1);
dtEnd = new DateTime(Convert.ToInt32(aEdt[0]), Convert.ToInt32(aEdt[1]), Convert.ToInt32(aEdt[2]), Convert.ToInt32(aEdt[3]), 59, 59);
}
string sStartDate = dtStart.ToString("yyyyMMddHH");
string sEndDate = dtEnd.ToString("yyyyMMddHH");
string ssql = @" select * from SoilWaterRainDataHis where 1=1
and STID = @STID
and Time between @sd and @ed
order by Time ";
List <SoilWaterRainDataHis> DataList = new List <SoilWaterRainDataHis>();
DataList = dbDapper.Query <SoilWaterRainDataHis>(ssql, new { STID = Station, sd = sStartDate, ed = sEndDate });
//產生檔案路徑
string sTempPath = Path.Combine(Server.MapPath("~/temp/"), DateTime.Now.ToString("yyyyMMdd"));
//建立資料夾
Directory.CreateDirectory(sTempPath);
string sSaveFilePath = Path.Combine(sTempPath, "SoilWaterRainDataHisByStation_" + Guid.NewGuid().ToString() + ".xlsx");
DataTable dt = Utils.ConvertToDataTable <SoilWaterRainDataHis>(DataList);
List <string> head = new List <string>();
head.Add("站號");
head.Add("時間");
head.Add("Hour");
head.Add("Hour3");
head.Add("Hour6");
head.Add("Hour12");
head.Add("Hour24");
head.Add("DayRainFall");
head.Add("RT");
List <string[]> datas = new List <string[]>();
foreach (SoilWaterRainDataHis item in DataList)
{
//日期格式化
DateTime MainTime = DateTime.ParseExact(item.Time, "yyyyMMddHH", null);
List <string> cols = new List <string>();
cols.Add(item.Stid);
cols.Add(MainTime.ToString("yyyy/MM/dd HH:00"));
cols.Add(item.Hour.ToString());
cols.Add(item.Hour3.ToString());
cols.Add(item.Hour6.ToString());
cols.Add(item.Hour12.ToString());
cols.Add(item.Hour24.ToString());
cols.Add(item.DayRainfall.ToString());
cols.Add(item.RT.ToString());
datas.Add(cols.ToArray());
}
DataExport de = new DataExport();
Boolean bSuccess = de.ExportListToExcel(sSaveFilePath, head, datas);
if (bSuccess)
{
string filename = string.Format("SoilWaterRainDataHis_{0}_{1}_{2}.xlsx", Station, sd, ed).Replace("-", "");
//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();
}
return(null);
}
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));
}
