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));
}
}
