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();
}
///
/// 获得数据
///
///
public ActionResult GetRecordJson()
{
string RecordJson = JsonConvert.SerializeObject(dtexcel);
//string RecordJson = bll.GetRecordJson();
return Content(RecordJson);
}
///
/// 联网测试
///
///
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 批量注册
///
/// 导入excle
///
///
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);
}
}
}
///
/// EXCEL 导入
///
///
///
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(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 批量删除
///
/// 导入excle
///
///
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);
}
}
}
///
/// EXCEL 导入
///
///
///
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
///
/// 导出excle
///
///
public FileResult ExportExcel(string datavalue)
{
return null;
}
#region 删除表具信息
///
/// 删除表具信息
///
///
///
public ActionResult DeleteMeterForm(string id)
{
switch (bll.DeleteMeterForm(id))
{
case 1:
return Success("删除成功。");
case 0:
return Error("删除失败。");
default:
return Error("删除失败。");
}
}
#endregion
///
/// 表具 Form
///
///
public ActionResult MeterForm()
{
return View();
}
#region 获取表具信息
///
/// 获取表具信息
///
///
///
public ActionResult GetMeterByMeterId(string keyValue)
{
DataTable dtMeter = bll.GetMeterByMeterId(keyValue);
return Content(LeaRun.Util.Json.ToJson(dtMeter));
}
#endregion
#region 保存表具数据
///
/// 保存表具数据
///
///
///
///
[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 数据同步
///
/// 导入excle
///
///
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);
}
}
}
///
/// 两种不同版本的操作excel
/// 扩展名*.xls
///
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
///
/// 表具 Form
///
///
public ActionResult NetworkBDelete()
{
return View();
}
}
}