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 ShowVisCarListInfo_ToExcel()
{
UserInfo user = new UserInfo();
user = (UserInfo)Session["LoginUser"];
IList <CarReport_LCTJView> irt = carReportBll.GetVis_Carlist_ViewPage(user.UserDeptcode);
ExcelUpLoad eu = new ExcelUpLoad();
MemoryStream ms = eu.CreateTerExcel(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("生成文件出错，请重新导出！");
}
}
public string ExportCarInfo(CarInfo tiv, string ShowFieldString, string CarNo, string DeptId, string TypeId, string ChildrenSel, string Businessdivisionid)
{
string[] Filed = null;
UserInfo user = new UserInfo();
user = (UserInfo)Session["LoginUser"];
DataSet ds = null;
DataSet dsCount = null;
DataTable dt = null;
#region
tiv.StartData = 0;
tiv.EndData = 0;
if (!string.IsNullOrEmpty(ShowFieldString))
{
ShowFieldString = ShowFieldString.Substring(0, ShowFieldString.Length - 1);
if (ShowFieldString.IndexOf("|") != -1)
{
Filed = ShowFieldString.Split(new char[] { '|' });
}
}
else
{
return("请选择导出的列,再进行导出！");
}
tiv.CarNo = CarNo;
tiv.DeptId = DeptId;
tiv.TypeId = TypeId;
tiv.Businessdivisionid = Businessdivisionid;
Hashtable ht = new Hashtable();
ht.Add("CarNo", tiv.CarNo);
ht.Add("TypeId", tiv.TypeId);
ht.Add("Businessdivisionid", DeptId);
ht.Add("DeptId", tiv.Businessdivisionid);
ht.Add("StartData", tiv.StartData);
ht.Add("EndData", tiv.EndData);
if (tiv.Businessdivisionid == null || tiv.Businessdivisionid.Trim() == "")
{
ht["DeptId"] = user.EnterId;
}
if (ChildrenSel == "true")
{
DeptInfo di = deptInfoBll.GetDeptInfo(tiv.Businessdivisionid);
if (di != null)
{
ht["Businessdivisioncode"] = di.Businessdivisioncode;
ht["DeptId"] = "";
}
}
#endregion
#region
if (user != null)
{
StringBuilder sb = new StringBuilder();
StringBuilder sbCount = new StringBuilder();
sb.Append("select ci.car_no as 车牌号,ct.type_name as 车辆类型,ci.car_color as 车辆颜色,di.businessdivisionname as 所属企业,ci.car_adminname as 车主姓名,ci.car_admincardid as 身份证号,ti.ter_no as 终端编号,");
sb.Append("ci.oweraddress as 车主地址,ci.owerphone as 车主电话,ci.installname as 安装人,ci.installaddress as 安装位置,ci.installphone as 安装人联系电话,ci.installplace as 安装地点,ci.car_frame as 车架号,");
sb.Append("ci.installtime as 安装时间,ci.entryname as 录入人,ci.entryphone as 录入人电话,ci.contractnum as 合同号,ci.safeorder as 保险单号,ci.loanmoney as 融资金额,ci.loanyear as 贷款年限,ci.carmodel as 车辆型号,ci.enginenumber as 发动机号,ci.car_frame as 车架号,ci.description as 备注");
sbCount.Append("select count(*) ");
sb.Append(" from Car_Info ci left join car_type ct on ci.type_id=ct.type_id left join terminal_info ti on ci.car_id=ti.car_id join dept_info di on ti.dept_id=di.businessdivisionid where 1=1");
sbCount.Append(" from Car_Info ci left join car_type ct on ci.type_id=ct.type_id left join terminal_info ti on ci.car_id=ti.car_id join dept_info di on ti.dept_id=di.businessdivisionid where 1=1");
if (tiv.CarNo != null && tiv.CarNo.Trim() != "" && tiv.CarNo != "undefined")
{
sb.Append(string.Format(" and ci.car_no like '%{0}%'", tiv.CarNo));
sbCount.Append(string.Format(" and ci.car_no like '%{0}%'", tiv.CarNo));
}
if (tiv.TypeId != null && tiv.TypeId.Trim() != "")
{
sb.Append(string.Format(" and ct.type_id='{0}'", tiv.TypeId));
sbCount.Append(string.Format(" and ct.type_id='{0}'", tiv.TypeId));
}
if (ChildrenSel != "true")
{
if (tiv.Businessdivisionid != null && tiv.Businessdivisionid.Trim() != "")
{
sb.Append(string.Format(" and di.businessdivisionid='{0}'", tiv.Businessdivisionid));
sbCount.Append(string.Format(" and di.businessdivisionid='{0}'", tiv.Businessdivisionid));
}
else
{
sb.Append(string.Format(" and di.businessdivisionid='{0}'", user.EnterId));
sbCount.Append(string.Format(" and di.businessdivisionid='{0}'", user.EnterId));
}
}
else
{
DeptInfo di = null;
if (tiv.Businessdivisionid != null && tiv.Businessdivisionid.Trim() != "")
{
di = deptInfoBll.GetDeptInfo(tiv.Businessdivisionid);
}
else
{
di = deptInfoBll.GetDeptInfo(user.EnterId);
}
sb.Append(string.Format(" and di.businessdivisioncode like '{0}%'", di.Businessdivisioncode));
sbCount.Append(string.Format(" and di.businessdivisioncode like '{0}%'", di.Businessdivisioncode));
}
sb.Append(" order by car_no ");
ds = c.GetColligateQuery("ColligateQuery.ProteanQuery", sb.ToString());
dsCount = c.GetColligateQuery("ColligateQuery.ProteanQuery", sbCount.ToString());
}
else
{
return("");
}
#endregion
if (dsCount != null && ds != null && dsCount.Tables[0].Rows.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
dt = ds.Tables[0];
IList <CarInfo> ltiv = new List <CarInfo>();
List <string> filedlist = new List <string>();
ExcelUpLoad eu = new ExcelUpLoad();
MemoryStream ms = eu.CreateCarInfoExcel(dt, Filed);
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("生成文件出错，请重新导出！");
}
}
else
{
new LogMessage().Save("生成文件出错，请重新导出！");
return("生成文件出错，请重新导出！");
}
}
public ActionResult UpLoadCarInfoForm(string DeptId, HttpPostedFileBase file)
{
UserInfo user = new UserInfo();
user = (UserInfo)Session["LoginUser"];
if (file != null && file.ContentLength > 0 && DeptId != null && DeptId.Trim() != "")
{
string filePath = Path.Combine(HttpContext.Server.MapPath("../Files"), System.Guid.NewGuid().ToString() + Path.GetExtension(file.FileName));
file.SaveAs(filePath);
new LogMessage().Save("文件:" + filePath + ";");
string val = "";
string msg = "";
ExcelUpLoad eu = new ExcelUpLoad();
List <UpLoadTerBind> lut = eu.ReadCarInfo_ZNX(filePath, ref msg);
if (lut != null && lut.Count > 0)
{
val = carInfoBll.insertCarInfoMul(DeptId, lut);
if (val.LastIndexOf(",") > 0)
{
val = val.Substring(0, val.Length - 1);
}
ViewBag.ReturnVal = val;
//绑车接口
for (int i = 0; i < lut.Count; i++)
{
string CarDeptcode = "";
string TypeName = "";
if (lut[i].BusinessDivisionId != "" && lut[i].BusinessDivisionId != null)
{
DeptInfo di = deptInfoBll.GetDeptInfo(lut[i].BusinessDivisionId);
CarDeptcode = di.Businessdivisioncode;
}
if (lut[i].CarType != "" && lut[i].CarType != null)
{
CarTypeBLL cartypebll = new CarTypeBLL();
CarType ct = cartypebll.GetCarType(lut[i].CarType);
if (ct != null)
{
TypeName = ct.TypeName;
}
}
string carinfostr = TypeName + "|" + lut[i].CarColor + "|||||||||||||||||||||||||||||";
Transfers.ClintSendCommData(1107, "50", "", lut[i].TerNo, "", "", "", "", "", "", "", CarDeptcode, lut[i].CarNo, carinfostr, lut[i].CarAdminName, lut[i].CarFrame, "", "", user.UserName);
}
}
else if (msg.Trim() != "")
{
if (msg.LastIndexOf(",") > 0)
{
msg = msg.Substring(0, msg.Length - 1);
}
ViewBag.ReturnVal = msg;
}
else
{
ViewBag.ReturnVal = "false";
}
}
else if (DeptId == null || DeptId.Trim() == "")
{
ViewBag.ReturnVal = "请选择导入车辆的所属企业！";
}
else if (file == null || file.ContentLength <= 0)
{
ViewBag.ReturnVal = "请选择要导入的文件！";
}
//刷新车辆
Transfers.ClintSendCommData(1160, "1108", "", "", "", "", "", "", "", "", "", "1", "2", "", "", "", "", "", "");
return(View());
}
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("生成文件出错，请重新导出！");
}
}
public ActionResult UpLoadForm(string DeptId, HttpPostedFileBase file)
{
//CreateExcel();
if (file != null && file.ContentLength > 0 && DeptId != null && DeptId.Trim() != "")
{
string filePath = Path.Combine(HttpContext.Server.MapPath("../Files"), System.Guid.NewGuid().ToString() + Path.GetExtension(file.FileName));
file.SaveAs(filePath);
new LogMessage().Save("文件:" + filePath + "。");
string val = "";
ExcelUpLoad eu = new ExcelUpLoad();
string msg = "";
List <UpLoadTerBind> lut = eu.ReadExcel(filePath, ref msg);
if (lut != null && lut.Count > 0)
{
val = tib.InsertExcelData(DeptId, lut);
DeptInfoBLL deptInfoBll = new DeptInfoBLL();
DeptInfo di = deptInfoBll.GetDeptInfo(DeptId);
string deptcode = di.Businessdivisioncode;
UserInfo user = (UserInfo)Session["LoginUser"];
for (int m = 0; m < lut.Count; m++)
{
if (!val.Contains(lut[m].TerNo) && val != "false")
{
string tertype = "";
if (lut[m].TerType == "一代无线GPS")
{
tertype = "104";
}
else if (lut[m].TerType == "二代无线GPS")
{
tertype = "102";
}
else if (lut[m].TerType == "Homer3M" || lut[m].TerType == "Homer3B-2")
{
tertype = "101";
}
else if (lut[m].TerType == "五代无线GPS")
{
tertype = "112";
}
else if (lut[m].TerType == "五代有线GPS")
{
tertype = "111";
}
//添加终端的接口
Transfers.ClintSendCommData(1108, "2", "1", lut[m].TerNo, "", "", "", lut[m].TerInnettime.ToString("yyyy-MM-dd HH:mm:ss"), "", "", "", deptcode, lut[m].SimCard, tertype, "", "", "", "", user.UserName);
}
}
ViewBag.ReturnVal = val.Trim(',');
}
else if (msg.Trim() != "")
{
ViewBag.ReturnVal = msg;
}
else
{
ViewBag.ReturnVal = "false";
}
}
else if (DeptId == null || DeptId.Trim() == "")
{
ViewBag.ReturnVal = "请选择导入终端的企业";
}
else if (file == null || file.ContentLength <= 0)
{
ViewBag.ReturnVal = "请选择导入的文件！";
}
return(View());
}
