public string DownLoadExcel(string CarId, string TerNo, bool isStart, DateTime st, DateTime ed)
{
//得到本车最新上传数据的终端编号
//string NewEastTerNo = "";
//if (CarId != "null" && CarId.Trim() != "")
//{
// DataSet ds = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("SELECT TI.TER_NO FROM REALTIME_DATA RD JOIN TERMINAL_INFO TI ON RD.TER_NO = TI.TER_NO JOIN CAR_INFO CI ON TI.CAR_ID = CI.CAR_ID and ti.car_id='{0}' order by rd.rtime desc", CarId));
// if (ds != null && ds.Tables[0].Rows.Count > 0)
// {
// NewEastTerNo = ds.Tables[0].Rows[0][0].ToString();
// }
//}
//else
//{
// NewEastTerNo = TerNo;
//}
Hashtable ht = new Hashtable();
ht.Add("TerNo", TerNo);
if (isStart == true)
{
ht.Add("EndData", 31);
ht.Add("StartData", 0);
}
else
{
if (ed != null)
{
ht.Add("EndData", 0);
ht.Add("st", st.ToString("yyyy-MM-dd HH:mm:ss"));
ht.Add("ed", ed.ToString("yyyy-MM-dd HH:mm:ss"));
}
}
IList <HistoricalData> ihrd = historicalDataBll.GetHistorical(ht);
ExcelUpLoad eu = new ExcelUpLoad();
MemoryStream ms = eu.CreateExcel(ihrd, 0);
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 DownLoadExcel2(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);
}
ht.Add("EndData", 0);
if (TerNo != null && TerNo.Trim() != "")
{
ht.Add("TerNo", TerNo);
}
else
{
ht.Add("TerNo", "");
}
if (st != "")
{
ht.Add("st", st);
}
if (ed != "")
{
ht.Add("ed", ed);
}
IList <TerData> iltd = historicalDataBll.GetTerHistoryData(ht);
ExcelUpLoad eu = new ExcelUpLoad();
MemoryStream ms = eu.CreateExcel(iltd);
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 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 YXDownLoadExcel(string CarId, string TerNo, DateTime st, DateTime ed)
{
//得到本车最新上传数据的终端编号
//string NewEastTerNo = "";
//if (CarId != "null" && CarId.Trim() != "")
//{
// DataSet ds = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("SELECT TI.TER_NO FROM REALTIME_DATA RD JOIN TERMINAL_INFO TI ON RD.TER_NO = TI.TER_NO JOIN CAR_INFO CI ON TI.CAR_ID = CI.CAR_ID and ti.car_id='{0}' order by rd.rtime desc", CarId));
// if (ds != null && ds.Tables[0].Rows.Count > 0)
// {
// NewEastTerNo = ds.Tables[0].Rows[0][0].ToString();
// }
//}
//else
//{
// NewEastTerNo = TerNo;
//}
string strTrackName = "ZTRACK" + st.ToString("yyyyMMdd");
string StartDate = st.ToString("yyyy-MM-dd HH:mm:ss");
string EndDate = ed.ToString("yyyy-MM-dd HH:mm:ss");
List <HistoricalData> Listhrd = new List <HistoricalData>();
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)
{
DataSet dsYXTrail = c.GetColligateQuery("ColligateQuery.ProteanQuery", string.Format("select ID AS Id,RTIME AS Rtime,nvl(POSITION,'设备周边无地理信息描述') AS Position,TER_NO AS TerNo,REPLYDATANAME as ReplydataName,IFPOSITION AS Ifposition,BAIDU_LATITUDE AS BaiduLatitude,BAIDU_LONGITUDE AS BaiduLongitude,GOOGLE_LATITUDE AS GoogleLatitude,GOOGLE_LONGITUDE AS GoogleLongitude from " + strTrackName + " where TER_NO='" + TerNo + "' and RTIME BETWEEN to_date('" + StartDate + "','yyyy-mm-dd hh24:mi:ss') and to_date('" + EndDate + "','yyyy-mm-dd hh24:mi:ss') order by RTIME desc"));
if (dsYXTrail != null && dsYXTrail.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dsYXTrail.Tables[0].Rows)
{
HistoricalData hd = new HistoricalData();
hd.Rtime = DateTime.Parse(dr["Rtime"].ToString());
hd.Position = dr["Position"].ToString();
hd.TerNo = dr["TerNo"].ToString();
hd.ReplydataName = dr["ReplydataName"].ToString();
hd.Ifposition = dr["Ifposition"].ToString();
hd.GoogleLatitude = double.Parse(dr["GoogleLatitude"].ToString());
hd.GoogleLongitude = double.Parse(dr["GoogleLongitude"].ToString());
hd.BaiduLatitude = double.Parse(dr["BaiduLatitude"].ToString());
hd.BaiduLongitude = double.Parse(dr["BaiduLongitude"].ToString());
Listhrd.Add(hd);
}
}
}
else
{
Hashtable ht = new Hashtable();
ht.Add("TerNo", TerNo);
ht.Add("EndData", 0);
ht.Add("st", StartDate);
ht.Add("ed", EndDate);
IList <HistoricalData> ihrd = historicalDataBll.GetHistorical(ht);
Listhrd = ihrd.ToList();
}
ExcelUpLoad eu = new ExcelUpLoad();
MemoryStream ms = eu.CreateExcel(Listhrd, 1);
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 DownLoadExcel(Selectcarmonitor rtv, string DeptCode, string SelType, string ZLflag)
{
UserInfo user = new UserInfo();
user = (UserInfo)Session["LoginUser"];
DeptInfo dif = new DeptInfo();
Hashtable ht = new Hashtable();
ht.Add("TerStatus", rtv.TerStatus);
ht.Add("TerNo", rtv.TerNo);
ht.Add("CarNo", rtv.CarNo);
ht.Add("StartData", rtv.StartData);
ht.Add("EndData", rtv.EndData);
ht.Add("ReplydataCode", rtv.ReplydataCode);
ht.Add("Northorsouth", rtv.Northorsouth);
if (rtv.Businessdivisionid == null || rtv.Businessdivisionid.Trim() == "")
{
if (user.EnterId != null)
{
ht.Add("Businessdivisionid", user.EnterId);
}
else
{
ht.Add("UserLname", user.UserLname);
}
}
else
{
ht.Add("Businessdivisionid", rtv.Businessdivisionid);
}
if (DeptCode == "true")
{
if (user.EnterId != null)
{
dif = deptInfoBll.GetDeptInfo(ht["Businessdivisionid"].ToString());
ht.Add("Businessdivisioncode", dif.Businessdivisioncode);
ht["Businessdivisionid"] = "";
}
}
if (rtv.CarWorkVMP > 0)
{
ht.Add("SelType", "");
}
else
{
if (SelType == "Ter" || SelType == "Dept")
{
ht.Add("SelType", "true");
}
else
{
ht.Add("SelType", "");
}
}
IList <Selectcarmonitor> irt = realtimeDataBll.SelectCarMonitor(ht);
IList <Selectcarmonitor> lxirt = new List <Selectcarmonitor>();
IList <Selectcarmonitor> zxirt = new List <Selectcarmonitor>();
for (int i = 0; i < irt.Count; i++)
{
if (irt[i].StateName == "lx")
{
lxirt.Add(irt[i]);
}
else if (irt[i].StateName == "zx")
{
zxirt.Add(irt[i]);
}
}
ExcelUpLoad eu = new ExcelUpLoad();
MemoryStream ms = new MemoryStream();
if (ZLflag == "ZX")
{
ms = eu.CreateExcel(zxirt);
}
else if (ZLflag == "LX")
{
ms = eu.CreateExcel(lxirt);
}
else
{
ms = eu.CreateExcel(irt);
}
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
{
new LogMessage().Save("生成文件出错，请重新导出！");
return("生成文件出错，请重新导出！");
}
}
