123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603 |
- using LeaRun.Application.Busines.NBManage;
- using LeaRun.Util;
- using LeaRun.Util.WebControl;
- using Newtonsoft.Json;
- using Newtonsoft.Json.Linq;
- using NPOI.HSSF.UserModel;
- using NPOI.XSSF.UserModel;
- using System;
- using System.Collections.Generic;
- using System.Data;
- using System.IO;
- using System.Linq;
- using System.Web;
- using System.Web.Mvc;
- using System.Web.Script.Serialization;
- namespace LeaRun.Application.Web.Areas.NBManage.Controllers
- {
- public class NBEquipmentDebugController : MvcControllerBase
- {
- NBEquipmentDebugBLL bll = new NBEquipmentDebugBLL();
- DataTable dtexcel = new DataTable();
- LeaRun.Util.Log.Log log = Util.Log.LogFactory.GetLogger("NBEquipmentDebug");
- public ActionResult BatchRegistr()
- {
- return View();
- }
- public ActionResult DetailForm()
- {
- return View();
- }
- public ActionResult NetworkTest()
- {
- return View();
- }
- public ActionResult SyncData()
- {
- return View();
- }
- /// <summary>
- /// 获得数据
- /// </summary>
- /// <returns></returns>
- public ActionResult GetRecordJson()
- {
- string RecordJson = JsonConvert.SerializeObject(dtexcel);
- //string RecordJson = bll.GetRecordJson();
- return Content(RecordJson);
- }
- /// <summary>
- /// 联网测试
- /// </summary>
- /// <returns></returns>
- public ActionResult GetRecord(Pagination pagination, string imei = "", string thisDate = "", string lastDate = "", string address = "")
- {
- string RecordJson = bll.GetRecordJson(pagination, imei, thisDate, lastDate,address);
- return Content(RecordJson);
- }
- public ActionResult GetAjaxData(string deviceId)
- {
- //log.Debug("Method:GetAjaxData" + deviceId + "Tiem:" + DateTime.Now.ToString());
- JObject jobj = NBCommon.GetDeviceHistoryData(deviceId, Time.GetDate(-10), Time.GetDate(1));
- //log.Debug("4--" + jobj.ToString() + DateTime.Now.ToString() + Environment.NewLine);
- DataTable meterHistory = NBCommon.FormatJObjectHistoryData(jobj, "");
- //log.Debug("5--"+ meterHistory.ToJson() + DateTime.Now.ToString() + Environment.NewLine);
- return Content(meterHistory.ToJson());
- }
- #region 导入Excel 批量注册
- /// <summary>
- /// 导入excle
- /// </summary>
- /// <returns></returns>
- public ActionResult ImportExcel()
- {
- //1.接收客户端传过来的数据
- HttpPostedFileBase file = Request.Files["file"];
- if (file == null || file.ContentLength <= 0)
- {
- return Json("请选择要上传的Excel文件", JsonRequestBehavior.AllowGet);
- }
- //获取一个streamfile对象,该对象指向一个上传文件,
- Stream streamfile = file.InputStream;
- string FinName = Path.GetExtension(file.FileName);
- if (FinName != ".xls" && FinName != ".xlsx")
- {
- return Json("只能上传Excel文档", JsonRequestBehavior.AllowGet);
- }
- else
- {
- try
- {
- this.ImExport(FinName, streamfile);
- string jsonTable = JsonConvert.SerializeObject(dtexcel);
- return Content(jsonTable);
- }
- catch (Exception ex)
- {
- return Json("导入失败 !" + ex.Message, JsonRequestBehavior.AllowGet);
- }
- }
- }
- /// <summary>
- /// EXCEL 导入
- /// </summary>
- /// <param name="FinName"></param>
- /// <param name="streamfile"></param>
- public void ImExport(string FinName, Stream streamfile)
- {
- try
- {
- NPOI.SS.UserModel.ISheet sheet;
- if (FinName == ".xls")
- {
- //创建一个webbook,对应一个Excel文件(用于xls文件导入类)
- HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);
- // 在webbook中添加一个sheet,对应Excel文件中的sheet,取出第一个工作表,索引是0
- sheet = hssfworkbook.GetSheetAt(0);
- }
- else
- {
- XSSFWorkbook xssfworkbook = new XSSFWorkbook(streamfile);
- // 在webbook中添加一个sheet,对应Excel文件中的sheet,取出第一个工作表,索引是0
- sheet = xssfworkbook.GetSheetAt(0);
- }
- System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
- for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
- {
- dtexcel.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
- }
- while (rows.MoveNext())
- {
- DataRow dr = dtexcel.NewRow();
- if (FinName == ".xls")
- {
- HSSFRow row = (HSSFRow)rows.Current;
- for (int i = 0; i < row.LastCellNum; i++)
- {
- NPOI.SS.UserModel.ICell cell = row.GetCell(i);
- if (cell == null)
- {
- dr[i] = null;
- }
- else
- {
- dr[i] = cell.ToString();
- }
- }
- }
- else
- {
- XSSFRow row = (XSSFRow)rows.Current;
- for (int i = 0; i < row.LastCellNum; i++)
- {
- NPOI.SS.UserModel.ICell cell = row.GetCell(i);
- if (cell == null)
- {
- dr[i] = null;
- }
- else
- {
- dr[i] = cell.ToString();
- }
- }
- }
- dtexcel.Rows.Add(dr);
- }
- dtexcel.Columns.Add("registerState", typeof(string));
- if (dtexcel != null && dtexcel.Rows.Count != 0)
- {
- for (int i = dtexcel.Rows.Count - 1; i >= 0; i--)
- {
- string IMEI = dtexcel.Rows[i]["IMEI"].ToString();
- string SIM = dtexcel.Rows[i]["SIM"].ToString();
- string MeterAddr = dtexcel.Rows[i]["MeterAddr"].ToString();
- if (string.IsNullOrEmpty(IMEI) || "IMEI".Equals(IMEI))
- {
- dtexcel.Rows[i].Delete();
- continue;
- }
- //查看是否已经注册过
- DataTable meterByIMEI = bll.GetBatchMeterByIMEI(IMEI);
- if (meterByIMEI.Rows.Count > 0)
- {
- dtexcel.Rows[i]["registerState"] = "注册失败,原因:此IMEI号已经注册过。";
- continue;
- }
- string resultCode = bll.SendCommand(IMEI, SIM, MeterAddr);//服务器返回消息
- JObject jObj = JsonConvert.DeserializeObject<JObject>(resultCode);
- if (null != jObj)
- {
- if (null != jObj["error_code"])
- {
- dtexcel.Rows[i]["registerState"] = "注册失败,原因:" + jObj["error_code"].ToString();
- }
- else if (null != jObj["deviceId"])
- {
- dtexcel.Rows[i]["registerState"] = "注册成功";
- }
- }
- else //(null==jObj1)
- {
- dtexcel.Rows[i]["registerState"] = "注册失败,原因未知";
- }
- }
- }
- }
- catch (Exception ex)
- {
-
- throw ex;
- }
- }
- #endregion
- #region 导入Excel 批量删除
- /// <summary>
- /// 导入excle
- /// </summary>
- /// <returns></returns>
- public ActionResult DeleteExcel()
- {
- //1.接收客户端传过来的数据
- HttpPostedFileBase file = Request.Files["file"];
- if (file == null || file.ContentLength <= 0)
- {
- return Json("请选择要上传的Excel文件", JsonRequestBehavior.AllowGet);
- }
- //获取一个streamfile对象,该对象指向一个上传文件,
- Stream streamfile = file.InputStream;
- string FinName = Path.GetExtension(file.FileName);
- if (FinName != ".xls" && FinName != ".xlsx")
- {
- return Json("只能上传Excel文档", JsonRequestBehavior.AllowGet);
- }
- else
- {
- try
- {
- this.DelExport(FinName, streamfile);
- string jsonTable = JsonConvert.SerializeObject(dtexcel);
- return Content(jsonTable);
- }
- catch (Exception ex)
- {
- return Json("导入失败 !" + ex.Message, JsonRequestBehavior.AllowGet);
- }
- }
- }
- /// <summary>
- /// EXCEL 导入
- /// </summary>
- /// <param name="FinName"></param>
- /// <param name="streamfile"></param>
- public void DelExport(string FinName, Stream streamfile)
- {
- try
- {
- NPOI.SS.UserModel.ISheet sheet;
- if (FinName == ".xls")
- {
- //创建一个webbook,对应一个Excel文件(用于xls文件导入类)
- HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);
- // 在webbook中添加一个sheet,对应Excel文件中的sheet,取出第一个工作表,索引是0
- sheet = hssfworkbook.GetSheetAt(0);
- }
- else
- {
- XSSFWorkbook xssfworkbook = new XSSFWorkbook(streamfile);
- // 在webbook中添加一个sheet,对应Excel文件中的sheet,取出第一个工作表,索引是0
- sheet = xssfworkbook.GetSheetAt(0);
- }
- System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
- for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
- {
- dtexcel.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
- }
- while (rows.MoveNext())
- {
- DataRow dr = dtexcel.NewRow();
- if (FinName == ".xls")
- {
- HSSFRow row = (HSSFRow)rows.Current;
- for (int i = 0; i < row.LastCellNum; i++)
- {
- NPOI.SS.UserModel.ICell cell = row.GetCell(i);
- if (cell == null)
- {
- dr[i] = null;
- }
- else
- {
- dr[i] = cell.ToString();
- }
- }
- }
- else
- {
- XSSFRow row = (XSSFRow)rows.Current;
- for (int i = 0; i < row.LastCellNum; i++)
- {
- NPOI.SS.UserModel.ICell cell = row.GetCell(i);
- if (cell == null)
- {
- dr[i] = null;
- }
- else
- {
- dr[i] = cell.ToString();
- }
- }
- }
- dtexcel.Rows.Add(dr);
- }
- dtexcel.Columns.Add("registerState", typeof(string));
- if (dtexcel != null && dtexcel.Rows.Count != 0)
- {
- for (int i = dtexcel.Rows.Count - 1; i >= 0; i--)
- {
- string IMEI = dtexcel.Rows[i]["IMEI"].ToString();
- string SIM = dtexcel.Rows[i]["ICCID"].ToString();
- string DeviceID = dtexcel.Rows[i]["DeviceID"].ToString();
- string MeterAddr = dtexcel.Rows[i]["表地址"].ToString();
- if (string.IsNullOrEmpty(IMEI) || "IMEI".Equals(IMEI))
- {
- dtexcel.Rows[i].Delete();
- continue;
- }
- //查看是否已删除过
- DataTable meterByIMEI = bll.GetBatchMeterByDeviceID(DeviceID);
- if (meterByIMEI.Rows.Count > 0)
- {
- dtexcel.Rows[i]["registerState"] = "删除失败,原因:此水表已经删除过。";
- continue;
- }
- Int32 resultCode = bll.DelMeter(IMEI, SIM, MeterAddr, DeviceID);//服务器返回消息
- if (resultCode==1)
- {
-
- dtexcel.Rows[i]["registerState"] = "删除成功";
-
- }
- else //(null==jObj1)
- {
- dtexcel.Rows[i]["registerState"] = "删除失败";
- }
- }
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- #endregion
- /// <summary>
- /// 导出excle
- /// </summary>
- /// <returns></returns>
- public FileResult ExportExcel(string datavalue)
- {
- return null;
- }
- #region 删除表具信息
- /// <summary>
- /// 删除表具信息
- /// </summary>
- /// <param name="id"></param>
- /// <returns></returns>
- public ActionResult DeleteMeterForm(string id)
- {
- switch (bll.DeleteMeterForm(id))
- {
- case 1:
- return Success("删除成功。");
- case 0:
- return Error("删除失败。");
- default:
- return Error("删除失败。");
- }
- }
- #endregion
- /// <summary>
- /// 表具 Form
- /// </summary>
- /// <returns></returns>
- public ActionResult MeterForm()
- {
- return View();
- }
- #region 获取表具信息
- /// <summary>
- /// 获取表具信息
- /// </summary>
- /// <param name="data"></param>
- /// <returns></returns>
- public ActionResult GetMeterByMeterId(string keyValue)
- {
- DataTable dtMeter = bll.GetMeterByMeterId(keyValue);
- return Content(LeaRun.Util.Json.ToJson(dtMeter));
- }
- #endregion
- #region 保存表具数据
- /// <summary>
- /// 保存表具数据
- /// </summary>
- /// <param name="keyValue"></param>
- /// <param name="data"></param>
- /// <returns></returns>
- [HttpPost]
- [ValidateAntiForgeryToken]
- [AjaxOnly]
- public ActionResult SaveMeterForm(string keyValue, string meterAddr, string imei, string sim)
- {
- switch (bll.EditMeterForm(keyValue, meterAddr, imei, sim))
- {
- case 0:
- return Success("保存成功。");
- case 1:
- return Error("修改失败。");
- case 2:
- return Error("重复数据。");
- default:
- return Error("修改失败。");
- }
- }
- #endregion
- #region 导入Excel 数据同步
- /// <summary>
- /// 导入excle
- /// </summary>
- /// <returns></returns>
- public ActionResult ImportExcelForSync()
- {
- //1.接收客户端传过来的数据
- HttpPostedFileBase file = Request.Files["file"];
- if (file == null || file.ContentLength <= 0)
- {
- return Json("请选择要上传的Excel文件", JsonRequestBehavior.AllowGet);
- }
- //获取一个streamfile对象,该对象指向一个上传文件,
- Stream streamfile = file.InputStream;
- string fileName = Path.GetExtension(file.FileName);
- if (fileName != ".xls" && fileName != ".xlsx")
- {
- return Json("只能上传Excel文档", JsonRequestBehavior.AllowGet);
- }
- else
- {
- try
- {
- ImExportForSync(fileName, streamfile);
- string jsonTable = JsonConvert.SerializeObject(dtexcel);
- string json1 = jsonTable;
- return Content(json1);
- }
- catch (Exception ex)
- {
- return Json("导入失败 !" + ex.Message, JsonRequestBehavior.AllowGet);
- }
- }
- }
- ///<summary>
- /// 两种不同版本的操作excel
- /// 扩展名*.xls
- /// </summary>
- public void ImExportForSync(string fileName, Stream streamfile)
- {
- NPOI.SS.UserModel.ISheet sheet;
- if (fileName == ".xls")
- {
- //创建一个webbook,对应一个Excel文件(用于xls文件导入类)
- HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);
- sheet = hssfworkbook.GetSheetAt(0);
- }
- else
- {
- XSSFWorkbook xssfworkbook = new XSSFWorkbook(streamfile);
- sheet = xssfworkbook.GetSheetAt(0);
- }
- sheet.ShiftRows(1, sheet.LastRowNum, -1);
- System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
- for (int j = 0; j < (sheet.GetRow(1).LastCellNum); j++)
- {
- dtexcel.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
- }
- while (rows.MoveNext())
- {
- DataRow dr = dtexcel.NewRow();
- if (fileName == ".xls")
- {
- HSSFRow row = (HSSFRow)rows.Current;
- for (int i = 0; i < row.LastCellNum; i++)
- {
- NPOI.SS.UserModel.ICell cell = row.GetCell(i);
- if (cell == null)
- {
- dr[i] = null;
- }
- else
- {
- dr[i] = cell.ToString();
- }
- }
- }
- else
- {
- XSSFRow row = (XSSFRow)rows.Current;
- for (int i = 0; i < row.LastCellNum; i++)
- {
- NPOI.SS.UserModel.ICell cell = row.GetCell(i);
- if (cell == null)
- {
- dr[i] = null;
- }
- else
- {
- dr[i] = cell.ToString();
- }
- }
- }
- dtexcel.Rows.Add(dr);
- }
- dtexcel.Columns.Add("syncState", typeof(string));
- if (dtexcel != null && dtexcel.Rows.Count != 0)
- {
- for (int i = dtexcel.Rows.Count - 1; i >= 0; i--)
- {
- if (string.IsNullOrEmpty(dtexcel.Rows[i]["IMEI"].ToString()) || "IMEI".Equals(dtexcel.Rows[i]["IMEI"].ToString().Trim()))
- {
- dtexcel.Rows[i].Delete();
- continue;
- }
- string area = dtexcel.Rows[i]["小区"].ToString();
- string bldg = dtexcel.Rows[i]["楼宇"].ToString();
- string apmt = dtexcel.Rows[i]["单元"].ToString();
- string room = dtexcel.Rows[i]["户号"].ToString();
- string IMEI = dtexcel.Rows[i]["IMEI"].ToString();
- string SIM = dtexcel.Rows[i]["ICCID"].ToString();
- string MeterAddr = dtexcel.Rows[i]["表地址"].ToString();
- string DeviceID = dtexcel.Rows[i]["DeviceID"].ToString();
- int resultCode = 0;
- if (!string.IsNullOrEmpty(IMEI))
- {
- resultCode = bll.SendCommandForSync(area, bldg, apmt, room, IMEI, SIM, MeterAddr, DeviceID);//服务器返回消息
- if (resultCode == 1)
- {
- dtexcel.Rows[i]["syncState"] = "同步成功";
- }
- else
- {
- dtexcel.Rows[i]["syncState"] = "同步失败";
- }
- }
- }
- }
- }
- #endregion
- /// <summary>
- /// 表具 Form
- /// </summary>
- /// <returns></returns>
- public ActionResult NetworkBDelete()
- {
- return View();
- }
- }
- }
|