Ejemplo n.º 1
0
        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));
        }
Ejemplo n.º 2
0
        /// <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);
        }
Ejemplo n.º 3
0
        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));
        }