public string YXDownLoadExcel2(string TerNo, string DeptId, string st, string ed) { Hashtable ht = new Hashtable(); if (DeptId != null && DeptId.Trim() != "") { ht.Add("DeptId", DeptId); } else { UserInfo user = new UserInfo(); user = (UserInfo)Session["LoginUser"]; ht.Add("DeptId", user.EnterId); } if (TerNo != null && TerNo.Trim() != "") { ht.Add("TerNo", TerNo); } else { ht.Add("TerNo", ""); } string strTrackName = ""; string NewStartDate = ""; string NewEndDate = ""; if (st == "") { strTrackName = "ZTRACK" + DateTime.Now.ToString("yyyyMMdd"); NewStartDate = DateTime.Now.ToString("yyyyMMdd") + " 00:00:00"; NewEndDate = DateTime.Now.ToString("yyyyMMdd") + " 23:59:59"; } else { strTrackName = "ZTRACK" + DateTime.Parse(st).ToString("yyyyMMdd"); NewStartDate = DateTime.Parse(st).ToString("yyyy-MM-dd HH:mm:ss"); NewEndDate = DateTime.Parse(ed).ToString("yyyy-MM-dd HH:mm:ss"); } ht.Add("table", strTrackName); ht.Add("st", NewStartDate); ht.Add("ed", NewEndDate); List <YXHistoricalData> Listhrd = new List <YXHistoricalData>(); DataSet dsYXTrailCount = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("select * from user_tab_cols where table_name='" + strTrackName + "' and column_name='TER_NO'")); if (dsYXTrailCount != null && dsYXTrailCount.Tables[0].Rows.Count > 0) { StringBuilder sb = new StringBuilder(); StringBuilder sbCount = new StringBuilder(); sb.Append("select ZTK.ID AS Id,ZTK.RTIME AS Rtime,nvl(ZTK.POSITION,'设备周边无地理信息描述') AS Position,ZTK.TER_NO AS TerNo,ZTK.REPLYDATANAME as ReplydataName,ZTK.IFPOSITION AS Ifposition,"); sb.Append("ZTK.NORTHORSOUTH AS Northorsouth,ZTK.EASTORWEST AS Eastorwest,ZTK.SPEED AS Speed,ZTK.DIRECTION AS Direction,ZTK.TER_VBATT AS TerVbatt,ZTK.GSMRSSI AS Gsmrssi,ZTK.PROGRAMVERSON AS Programverson,ZTK.GPSVERSON AS Gpsverson,ZTK.PROTOCOLVERSION AS Protocolversion,ZTK.ACCSTATE AS Accstate,"); sb.Append("ZTK.LATITUDE AS Latitude,ZTK.LONGITUDE AS Longitude,ZTK.BAIDU_LATITUDE AS BaiduLatitude,ZTK.BAIDU_LONGITUDE AS BaiduLongitude,ZTK.GOOGLE_LATITUDE AS GoogleLatitude,ZTK.GOOGLE_LONGITUDE AS GoogleLongitude,TI.TER_SIMCARD AS TerSimcard,TI.TER_INNETTIME AS Ter_Innettime from " + strTrackName + " ZTK JOIN TERMINAL_INFO TI ON ZTK.TER_NO=TI.TER_NO where 1=1"); sbCount.Append("select count(*) from " + strTrackName + " ZTK JOIN TERMINAL_INFO TI ON ZTK.TER_NO=TI.TER_NO where 1=1"); if (ht["TerNo"].ToString() != "") { sb.Append(" and TI.TER_NO='" + ht["TerNo"].ToString() + "'"); sbCount.Append(" and TI.TER_NO='" + ht["TerNo"].ToString() + "'"); } if (ht["DeptId"].ToString() != "") { sb.Append(" and TI.DEPT_ID ='" + ht["DeptId"].ToString() + "'"); sbCount.Append(" and TI.DEPT_ID ='" + ht["DeptId"].ToString() + "'"); } if (NewStartDate != "") { sb.Append(" and ZTK.RTIME >=to_date('" + NewStartDate + "','yyyy-mm-dd hh24:mi:ss')"); sbCount.Append(" and ZTK.RTIME >=to_date('" + NewStartDate + "','yyyy-mm-dd hh24:mi:ss')"); } if (NewEndDate != "") { sb.Append(" and ZTK.RTIME <=to_date('" + NewEndDate + "','yyyy-mm-dd hh24:mi:ss')"); sbCount.Append(" and ZTK.RTIME <=to_date('" + NewEndDate + "','yyyy-mm-dd hh24:mi:ss')"); } sb.Append(" order by ZTK.RTIME desc"); DataSet dsYXTrail = c.GetColligateQuery("ColligateQuery.ProteanQuery", sb.ToString()); DataSet dsYXTrailNum = c.GetColligateQuery("ColligateQuery.ProteanQuery", sbCount.ToString()); if (dsYXTrail != null && dsYXTrail.Tables[0].Rows.Count > 0) { foreach (DataRow dr in dsYXTrail.Tables[0].Rows) { YXHistoricalData yxhd = new YXHistoricalData(); yxhd.Rtime = DateTime.Parse(dr["Rtime"].ToString()); yxhd.Position = dr["Position"].ToString(); yxhd.TerNo = dr["TerNo"].ToString(); yxhd.ReplydataName = dr["ReplydataName"].ToString(); yxhd.Ifposition = dr["Ifposition"].ToString(); yxhd.Northorsouth = dr["Northorsouth"].ToString(); yxhd.Eastorwest = dr["Eastorwest"].ToString(); yxhd.Speed = dr["Speed"].ToString(); yxhd.Direction = dr["Direction"].ToString(); yxhd.TerVbatt = dr["TerVbatt"].ToString(); yxhd.Gsmrssi = dr["Gsmrssi"].ToString(); yxhd.Programverson = dr["Programverson"].ToString(); yxhd.Gpsverson = dr["Gpsverson"].ToString(); yxhd.Latitude = dr["Latitude"].ToString(); yxhd.Longitude = dr["Longitude"].ToString(); yxhd.GoogleLatitude = dr["GoogleLatitude"].ToString(); yxhd.GoogleLongitude = dr["GoogleLongitude"].ToString(); yxhd.BaiduLatitude = dr["BaiduLatitude"].ToString(); yxhd.BaiduLongitude = dr["BaiduLongitude"].ToString(); yxhd.TerSimcard = dr["TerSimcard"].ToString(); yxhd.Ter_Innettime = DateTime.Parse(dr["Ter_Innettime"].ToString()); Listhrd.Add(yxhd); } } } else { ht.Add("EndData", 0); IList <TerData> iltd = historicalDataBll.GetTerHistoryData(ht); Listhrd = iltd.ToList().Select(m => new YXHistoricalData { Rtime = m.Rtime, Position = m.Position, TerNo = m.TerNo, ReplydataName = m.ReplydataName, Ifposition = m.Ifposition, Northorsouth = m.Northorsouth, Eastorwest = m.Eastorwest, Speed = m.Speed, Direction = m.Direction, TerVbatt = m.TerVbatt, Gsmrssi = m.Gsmrssi, Programverson = m.Programverson, Gpsverson = m.Gpsverson, Latitude = m.Latitude.ToString() == null ? "" : m.Latitude.ToString(), Longitude = m.Longitude.ToString() == null ? "" : m.Longitude.ToString(), GoogleLatitude = m.GoogleLatitude.ToString() == null ? "" : m.GoogleLatitude.ToString(), GoogleLongitude = m.GoogleLongitude.ToString() == null ? "" : m.GoogleLongitude.ToString(), BaiduLatitude = m.BaiduLatitude.ToString() == null ? "" : m.BaiduLatitude.ToString(), BaiduLongitude = m.BaiduLongitude.ToString() == null ? "" : m.BaiduLongitude.ToString(), TerSimcard = m.TerSimcard, Ter_Innettime = m.Ter_Innettime }).ToList(); } ExcelUpLoad eu = new ExcelUpLoad(); MemoryStream ms = eu.CreateExcel(Listhrd); string xlsName = DateTime.Now.ToString("yyyyMMddHHmmssfff"); // 输出Excel using (FileStream fs = new FileStream(HttpContext.Server.MapPath("../Files/历史轨迹信息") + xlsName + ".xlsx", FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } if (System.IO.File.Exists(HttpContext.Server.MapPath("../Files/历史轨迹信息") + xlsName + ".xlsx")) { string ppphhh = "../../Files/历史轨迹信息" + xlsName + ".xlsx"; new LogMessage().Save("文件:" + ppphhh + "。"); return(ppphhh); } else { return("生成文件出错,请重新导出!"); } }
public string GetYXHistoryData(string TerNo, string DeptId, int rows, int page, string st, string ed) { Hashtable ht = new Hashtable(); if (DeptId != null && DeptId.Trim() != "") { ht.Add("DeptId", DeptId); } else { UserInfo user = new UserInfo(); user = (UserInfo)Session["LoginUser"]; ht.Add("DeptId", user.EnterId); } if (TerNo != null && TerNo.Trim() != "") { ht.Add("TerNo", TerNo); } else { ht.Add("TerNo", ""); } ht.Add("StartData", (page - 1) * rows + 1); ht.Add("EndData", Convert.ToInt32(ht["StartData"]) + rows); string strTrackName = ""; string NewStartDate = ""; string NewEndDate = ""; if (st == "") { strTrackName = "ZTRACK" + DateTime.Now.ToString("yyyyMMdd"); NewStartDate = DateTime.Now.ToString("yyyyMMdd") + " 00:00:00"; NewEndDate = DateTime.Now.ToString("yyyyMMdd") + " 23:59:59"; } else { strTrackName = "ZTRACK" + DateTime.Parse(st).ToString("yyyyMMdd"); NewStartDate = DateTime.Parse(st).ToString("yyyy-MM-dd HH:mm:ss"); NewEndDate = DateTime.Parse(ed).ToString("yyyy-MM-dd HH:mm:ss"); } ht.Add("table", strTrackName); ht.Add("st", NewStartDate); ht.Add("ed", NewEndDate); List <YXHistoricalData> Listhrd = new List <YXHistoricalData>(); DataSet dsYXTrailCount = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("select * from user_tab_cols where table_name='" + strTrackName + "' and column_name='TER_NO'")); if (dsYXTrailCount != null && dsYXTrailCount.Tables[0].Rows.Count > 0) { StringBuilder sb = new StringBuilder(); StringBuilder sbCount = new StringBuilder(); if ((int)ht["EndData"] > 0) { sb.Append("select * from (select op_a.*, rownum rn from ("); } sb.Append("select ZTK.ID AS Id,ZTK.RTIME AS Rtime,nvl(ZTK.POSITION,'设备周边无地理信息描述') AS Position,ZTK.TER_NO AS TerNo,ZTK.REPLYDATANAME as ReplydataName,ZTK.IFPOSITION AS Ifposition,"); sb.Append("ZTK.NORTHORSOUTH AS Northorsouth,ZTK.EASTORWEST AS Eastorwest,ZTK.SPEED AS Speed,ZTK.DIRECTION AS Direction,ZTK.TER_VBATT AS TerVbatt,ZTK.GSMRSSI AS Gsmrssi,ZTK.PROGRAMVERSON AS Programverson,ZTK.GPSVERSON AS Gpsverson,ZTK.PROTOCOLVERSION AS Protocolversion,ZTK.ACCSTATE AS Accstate,"); sb.Append("ZTK.LATITUDE AS Latitude,ZTK.LONGITUDE AS Longitude,ZTK.BAIDU_LATITUDE AS BaiduLatitude,ZTK.BAIDU_LONGITUDE AS BaiduLongitude,ZTK.GOOGLE_LATITUDE AS GoogleLatitude,ZTK.GOOGLE_LONGITUDE AS GoogleLongitude,TI.TER_SIMCARD AS TerSimcard,TI.TER_INNETTIME AS Ter_Innettime from " + strTrackName + " ZTK JOIN TERMINAL_INFO TI ON ZTK.TER_NO=TI.TER_NO where 1=1"); sbCount.Append("select count(*) from " + strTrackName + " ZTK JOIN TERMINAL_INFO TI ON ZTK.TER_NO=TI.TER_NO where 1=1"); if (ht["TerNo"].ToString() != "") { sb.Append(" and TI.TER_NO='" + ht["TerNo"].ToString() + "'"); sbCount.Append(" and TI.TER_NO='" + ht["TerNo"].ToString() + "'"); } if (ht["DeptId"].ToString() != "") { sb.Append(" and TI.DEPT_ID ='" + ht["DeptId"].ToString() + "'"); sbCount.Append(" and TI.DEPT_ID ='" + ht["DeptId"].ToString() + "'"); } if (NewStartDate != "") { sb.Append(" and ZTK.RTIME >=to_date('" + NewStartDate + "','yyyy-mm-dd hh24:mi:ss')"); sbCount.Append(" and ZTK.RTIME >=to_date('" + NewStartDate + "','yyyy-mm-dd hh24:mi:ss')"); } if (NewEndDate != "") { sb.Append(" and ZTK.RTIME <=to_date('" + NewEndDate + "','yyyy-mm-dd hh24:mi:ss')"); sbCount.Append(" and ZTK.RTIME <=to_date('" + NewEndDate + "','yyyy-mm-dd hh24:mi:ss')"); } sb.Append(" order by ZTK.RTIME desc"); if ((int)ht["EndData"] > 0) { sb.Append(") op_a) op_b Where op_b.rn < " + ht["EndData"] + " And op_b.rn >=" + ht["StartData"]); } DataSet dsYXTrail = c.GetColligateQuery("ColligateQuery.ProteanQuery", sb.ToString()); DataSet dsYXTrailNum = c.GetColligateQuery("ColligateQuery.ProteanQuery", sbCount.ToString()); if (dsYXTrail != null && dsYXTrail.Tables[0].Rows.Count > 0) { foreach (DataRow dr in dsYXTrail.Tables[0].Rows) { YXHistoricalData yxhd = new YXHistoricalData(); yxhd.Rtime = DateTime.Parse(dr["Rtime"].ToString()); yxhd.Position = dr["Position"].ToString(); yxhd.TerNo = dr["TerNo"].ToString(); yxhd.ReplydataName = dr["ReplydataName"].ToString(); yxhd.Ifposition = dr["Ifposition"].ToString(); yxhd.Northorsouth = dr["Northorsouth"].ToString(); yxhd.Eastorwest = dr["Eastorwest"].ToString(); yxhd.Speed = dr["Speed"].ToString(); yxhd.Direction = dr["Direction"].ToString(); yxhd.TerVbatt = dr["TerVbatt"].ToString(); yxhd.Gsmrssi = dr["Gsmrssi"].ToString(); yxhd.Programverson = dr["Programverson"].ToString(); yxhd.Gpsverson = dr["Gpsverson"].ToString(); yxhd.Latitude = dr["Latitude"].ToString(); yxhd.Longitude = dr["Longitude"].ToString(); yxhd.GoogleLatitude = dr["GoogleLatitude"].ToString(); yxhd.GoogleLongitude = dr["GoogleLongitude"].ToString(); yxhd.BaiduLatitude = dr["BaiduLatitude"].ToString(); yxhd.BaiduLongitude = dr["BaiduLongitude"].ToString(); yxhd.TerSimcard = dr["TerSimcard"].ToString(); yxhd.Ter_Innettime = DateTime.Parse(dr["Ter_Innettime"].ToString()); Listhrd.Add(yxhd); } int num = Convert.ToInt32(dsYXTrailNum.Tables[0].Rows[0][0].ToString()); return(ConvertToJson(Listhrd, num)); } else { return(ConvertToJson(Listhrd, Listhrd.Count)); } } else { IList <TerData> iltd = historicalDataBll.GetTerHistoryData(ht); int count = historicalDataBll.GetTerHistoryDataCount(ht); return(ConvertToJson(iltd, count)); } }