NBDXSY/LeaRun.Application/LeaRun.Application.Web/Areas/NBManage/Controllers/NBEquipmentDebugController.cs

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