using LeaRun.Application.Entity.NBManage;
using LeaRun.Data;
using LeaRun.Util;
using LeaRun.Util.WebControl;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Diagnostics;
using Newtonsoft.Json.Linq;
using LeaRun.Util.Log;
using LeaRun.Application.Code;

namespace LeaRun.Application.Service.NBManage
{
    /// <summary>
    /// 用户管理
    /// </summary>
    public class NBUserManageService
    {
        SqlHelper sqlHelper = new SqlHelper("NBDB");
        Log log = LogFactory.GetLogger(typeof(NBUserManageService));

        #region 获取用户列表
        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="keyword"></param>
        /// <param name="searchType"></param>
        /// <param name="id"></param>
        /// <param name="level"></param>
        /// <returns></returns>
        public string GetNBUserList(Pagination pagination, string keyword, string searchType, string id, string level)
        {
            if ("".Equals(id) && "".Equals(keyword))
            {
                return GETNoInfo(pagination);
            }

            return GetMeterInfo(pagination, keyword, id, level);
        }
        #endregion

        #region 没有数据的时候返回的数据
        /// <summary>
        /// 没有数据的时候返回的数据
        /// </summary>
        /// <param name="pagination"></param>
        /// <returns></returns>
        private string GETNoInfo(Pagination pagination)
        {
            var watch = CommonHelper.TimerStart();
            var JsonData = new
            {
                rows = "",
                total = pagination.total,
                page = pagination.page,
                records = pagination.records,
                costtime = CommonHelper.TimerEnd(watch)
            };
            return JsonData.ToJson();
        }
        #endregion

        #region 获取区域信息 废弃
        /// <summary>
        /// 获取区域信息
        /// </summary>
        /// <returns></returns>
        private string GetAreaInfo(Pagination pagination, string keyword, string id, string level)
        {
            string whereSql = GetLevel(level, id);
            var watch = CommonHelper.TimerStart();
            StringBuilder where = new StringBuilder(whereSql);
            StringBuilder sb = new StringBuilder();
            string leftJoinString = " from RMRS_AreaInfo A left join RMRS_BldgInfo B on A.AreaID = B.AreaID left join RMRS_ApmtInfo T on B.BldgID = T.BldgID left join RMRS_RoomInfo R on R.ApmtID = T.ApmtID left join RMRS_MeterInfo M on M.RoomID = R.RoomID  ";
            //if (!"".Equals(keyword))
            //{
            //    sb.Append(" and (R.RoomNo like '%").Append(keyword + "%' or R.RoomName like '%").Append(keyword + "%')");
            //}

            string sql = " select count(1) " + leftJoinString + where;
            pagination.records = Convert.ToInt32(sqlHelper.ExecuteScalar(sql, CommandType.Text, null));
            int start = (pagination.page - 1) * pagination.rows + 1;
            int end = pagination.page * pagination.rows;
            sql = "SELECT PageTable.AreaID, PageTable.AreaName FROM " +
                "(             " +
                "SELECT        " +
                "    ROW_NUMBER() OVER(ORDER BY RoomID) AS RowIndex," +
                "    *         " +
                "FROM          " +
                "    (select A.AreaID, A.AreaName, B.BldgID, B.BldgNo, B.BldgName, T.ApmtID, T.ApmtNo, T.ApmtName, R.RoomID, R.RoomNo, R.RoomName, R.UserNo, R.UserName, M.MeterID, M.ElecAddress " +
                leftJoinString + where + ") as mytable " +
                " )" +
                " AS PageTable " +
                " WHERE RowIndex BETWEEN " + start + " AND " + end;
            return GetData(watch, pagination, sql);
        }
        #endregion

        #region 获取楼宇信息 废弃
        /// <summary>
        /// 获取楼宇信息
        /// </summary>
        /// <returns></returns>
        private string GetBldgInfo(Pagination pagination, string keyword, string id, string level)
        {
            string whereSql = GetLevel(level, id);
            var watch = CommonHelper.TimerStart();
            StringBuilder where = new StringBuilder(whereSql);
            StringBuilder sb = new StringBuilder();
            string leftJoinString = " from RMRS_AreaInfo A left join RMRS_BldgInfo B on A.AreaID = B.AreaID left join RMRS_ApmtInfo T on B.BldgID = T.BldgID left join RMRS_RoomInfo R on R.ApmtID = T.ApmtID left join RMRS_MeterInfo M on M.RoomID = R.RoomID  ";
            //if (!"".Equals(keyword))
            //{
            //    sb.Append(" and (R.RoomNo like '%").Append(keyword + "%' or R.RoomName like '%").Append(keyword + "%')");
            //}

            string sql = " select count(1) " + leftJoinString + where;
            pagination.records = Convert.ToInt32(sqlHelper.ExecuteScalar(sql, CommandType.Text, null));
            int start = (pagination.page - 1) * pagination.rows + 1;
            int end = pagination.page * pagination.rows;
            sql = "SELECT PageTable.AreaID, PageTable.AreaName, PageTable.BldgID, PageTable.BldgNo, PageTable.BldgName FROM " +
                "(             " +
                "SELECT        " +
                "    ROW_NUMBER() OVER(ORDER BY RoomID) AS RowIndex," +
                "    *         " +
                "FROM          " +
                "    (select A.AreaID, A.AreaName, B.BldgID, B.BldgNo, B.BldgName, T.ApmtID, T.ApmtNo, T.ApmtName, R.RoomID, R.RoomNo, R.RoomName, R.UserNo, R.UserName, M.MeterID, M.ElecAddress " +
                leftJoinString + where + ") as mytable " +
                " )" +
                " AS PageTable " +
                " WHERE RowIndex BETWEEN " + start + " AND " + end;
            return GetData(watch, pagination, sql);
        }
        #endregion

        #region 获取单元信息 废弃
        /// <summary>
        /// 获取单元信息
        /// </summary>
        /// <returns></returns>
        private string GetApmtInfo(Pagination pagination, string keyword, string id, string level)
        {
            string whereSql = GetLevel(level, id);
            var watch = CommonHelper.TimerStart();
            StringBuilder where = new StringBuilder(whereSql);
            StringBuilder sb = new StringBuilder();
            string leftJoinString = " from RMRS_AreaInfo A left join RMRS_BldgInfo B on A.AreaID = B.AreaID left join RMRS_ApmtInfo T on B.BldgID = T.BldgID left join RMRS_RoomInfo R on R.ApmtID = T.ApmtID left join RMRS_MeterInfo M on M.RoomID = R.RoomID  ";
            //if (!"".Equals(keyword))
            //{
            //    sb.Append(" and (R.RoomNo like '%").Append(keyword + "%' or R.RoomName like '%").Append(keyword + "%')");
            //}

            string sql = " select count(1) " + leftJoinString + where;
            pagination.records = Convert.ToInt32(sqlHelper.ExecuteScalar(sql, CommandType.Text, null));
            int start = (pagination.page - 1) * pagination.rows + 1;
            int end = pagination.page * pagination.rows;
            sql = "SELECT PageTable.AreaID, PageTable.AreaName, PageTable.BldgID, PageTable.BldgNo, PageTable.BldgName, PageTable.ApmtID, PageTable.ApmtNo, PageTable.ApmtName FROM " +
                "(             " +
                "SELECT        " +
                "    ROW_NUMBER() OVER(ORDER BY RoomID) AS RowIndex," +
                "    *         " +
                "FROM          " +
                "    (select A.AreaID, A.AreaName, B.BldgID, B.BldgNo, B.BldgName, T.ApmtID, T.ApmtNo, T.ApmtName, R.RoomID, R.RoomNo, R.RoomName, R.UserNo, R.UserName, M.MeterID, M.ElecAddress " +
                leftJoinString + where + ") as mytable " +
                " )" +
                " AS PageTable " +
                " WHERE RowIndex BETWEEN " + start + " AND " + end;
            return GetData(watch, pagination, sql);
        }
        #endregion

        #region 获取用户信息 废弃
        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <returns></returns>
        private string GetRoomInfo(Pagination pagination, string keyword, string id, string level)
        {
            string whereSql = GetLevel(level, id);
            var watch = CommonHelper.TimerStart();
            StringBuilder where = new StringBuilder(whereSql);
            StringBuilder sb = new StringBuilder();
            string leftJoinString = " from RMRS_AreaInfo A left join RMRS_BldgInfo B on A.AreaID = B.AreaID left join RMRS_ApmtInfo T on B.BldgID = T.BldgID left join RMRS_RoomInfo R on R.ApmtID = T.ApmtID left join RMRS_MeterInfo M on M.RoomID = R.RoomID  ";
            //if (!"".Equals(keyword))
            //{
            //    sb.Append(" and (R.RoomNo like '%").Append(keyword + "%' or R.RoomName like '%").Append(keyword + "%')");
            //}

            string sql = " select count(1) " + leftJoinString + where;
            pagination.records = Convert.ToInt32(sqlHelper.ExecuteScalar(sql, CommandType.Text, null));
            int start = (pagination.page - 1) * pagination.rows + 1;
            int end = pagination.page * pagination.rows;
            sql = "SELECT PageTable.AreaID, PageTable.AreaName, PageTable.BldgID, PageTable.BldgNo, PageTable.BldgName, PageTable.ApmtID, PageTable.ApmtNo, PageTable.ApmtName, PageTable.RoomID, PageTable.RoomNo, PageTable.RoomName, PageTable.UserNo, PageTable.UserName  FROM " +
                "(             " +
                "SELECT        " +
                "    ROW_NUMBER() OVER(ORDER BY RoomID) AS RowIndex," +
                "    *         " +
                "FROM          " +
                "    (select A.AreaID, A.AreaName, B.BldgID, B.BldgNo, B.BldgName, T.ApmtID, T.ApmtNo, T.ApmtName, R.RoomID, R.RoomNo, R.RoomName, R.UserNo, R.UserName, M.MeterID, M.ElecAddress " +
                leftJoinString + where + ") as mytable " +
                " )" +
                " AS PageTable " +
                " WHERE RowIndex BETWEEN " + start + " AND " + end;
            return GetData(watch, pagination, sql);
        }
        /// <summary>
        /// 保存表移动后的位置信息
        /// </summary>
        /// <param name="meterId">NB表ID</param>
        /// <param name="areaId">小区ID</param>
        /// <param name="bldgId">楼宇ID</param>
        /// <param name="apmtId">单元ID</param>
        /// <param name="roomId">房间ID</param>
        /// <returns></returns>
        public string SaveMeterRemove(string meterId, string areaId, string bldgId, string apmtId, string roomId)
        {
            AreaInfoEntity areaInfoEntity; //小区信息
            BldgInfoEntity bldgInfoEntity; //楼宇信息
            ApmtInfoEntity apmtInfoEntity; //单元信息
            NBUserEntity nBUserEntity; // 房间信息
            NBMeterEntity nBMeterEntity;// NB表信息
            string resultMessage = "移表成功"; //返回结果消息
            string updateSql; //更新SQL
            int sqlResult = 0; //更新SQL 影响的行数

            areaInfoEntity = GetAreaInfo(areaId);
            bldgInfoEntity = GetBldgInfo(bldgId);
            apmtInfoEntity = GetApmtInfo(apmtId);
            nBUserEntity = GetUserInfo(roomId);
            nBMeterEntity = GetMeterByMeterId(meterId);

            // 判断五个ID 在数据库中是否真正的存在
            if (areaInfoEntity != null && bldgInfoEntity != null && apmtInfoEntity != null && nBUserEntity != null && nBMeterEntity != null)
            {
                updateSql = "UPDATE RMRS_MeterInfo SET AreaID=" + areaId + ", BldgID=" + bldgId + ", ApmtID=" + apmtId + ", RoomID=" + roomId + " WHERE MeterID =" + meterId;
                sqlResult = sqlHelper.ExecuteNoParams(updateSql, CommandType.Text);
                if (sqlResult > 0)
                {
                    resultMessage = "{code:1,message:\"移表操作成功\"}";
                }
                else
                {
                    resultMessage = "{code:0,message:\"移表操作失败\"}";
                }
            }
            else
            {
                resultMessage = "{code:0,message:\"参数不合法\"}";
            }

            return resultMessage;
        }

        #endregion

        #region 获取表具信息
        /// <summary>
        /// 获取表具信息
        /// </summary>
        /// <returns></returns>
        private string GetMeterInfo(Pagination pagination, string keyword, string id, string level)
        {
            StringBuilder where = new StringBuilder();
            if (!"".Equals(id))
            {
                string whereSql = GetLevel(level, id);
                where.Append(whereSql);
            }
            Stopwatch watch = CommonHelper.TimerStart();
            StringBuilder sb = new StringBuilder();

            if (!"".Equals(keyword))
            {
                if (!"".Equals(id))
                {
                    where.Append(" and ");
                }
                else
                {
                    where.Append(" where ");
                }
                where.Append(" (AreaName like '%")
                    //.Append(keyword + "%' or BldgNo like '%")
                    .Append(keyword + "%' or BldgName like '%")
                    .Append(keyword + "%' or ApmtName like '%")
                    //.Append(keyword + "%' or ApmtNo like '%")
                    //.Append(keyword + "%' or RoomNo like '%")
                    .Append(keyword + "%' or RoomName like '%")
                    .Append(keyword + "%' or UserNo like '%")
                    .Append(keyword + "%' or ElecAddress like '%")
                    .Append(keyword + "%' or UserName like '%").Append(keyword + "%')");
            }


            string sql = " SELECT * FROM dbo.V_WaterMeters " + where;

            if (!OperatorProvider.Provider.Current().IsSystem)
            {
                sql = sql + " AND CompanyId='" + OperatorProvider.Provider.Current().CompanyId + "'";
            }

            //return GetData(watch, pagination, sql);
            int total = pagination.records;
            DataTable dt = sqlHelper.ExecuteDataTable(sql, pagination.sidx, pagination.sord, pagination.rows, pagination.page, out total, null);
            pagination.records = total;
            if (dt.Rows.Count == 0)
            {
                return GETNoInfo(pagination);
            }
            //List<NBUserEntity> list = DataHelper.DataTableToT<NBUserEntity>(dt);
            var JsonData = new
            {
                rows = dt,
                total = pagination.total,
                page = pagination.page,
                records = pagination.records,
                costtime = CommonHelper.TimerEnd(watch)
            };
            return JsonData.ToJson();
        }
        #endregion

        #region 获取等级信息
        /// <summary>
        /// 获取等级信息
        /// </summary>
        /// <param name="level"></param>
        /// <returns></returns>
        private string GetLevel(string level, string id)
        {
            StringBuilder whereSql = new StringBuilder();
            switch (level)
            {
                case "level0":
                    whereSql.Append("where CompanyId = '").Append(id + " '");
                    break;
                case "level1":
                    whereSql.Append("where AreaID = ").Append(id);
                    break;
                case "level2":
                    whereSql.Append("where BldgID = ").Append(id);
                    break;
                case "level3":
                    whereSql.Append("where ApmtID = ").Append(id);
                    break;
                case "level4":
                    whereSql.Append("where RoomID = ").Append(id);
                    break;
                case "level5":
                    whereSql.Append("where MeterID = ").Append(id);
                    break;
                default:
                    break;
            }
            return whereSql.ToString();
        }
        #endregion

        #region 获取数据
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private string GetData(Stopwatch watch, Pagination pagination, string sql)
        {
            try
            {
                DataTable dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
                if (dt.Rows.Count == 0)
                {
                    return GETNoInfo(pagination);
                }
                //List<NBUserEntity> list = DataHelper.DataTableToT<NBUserEntity>(dt);
                var JsonData = new
                {
                    rows = dt,
                    total = pagination.total,
                    page = pagination.page,
                    records = pagination.records,
                    costtime = CommonHelper.TimerEnd(watch)
                };
                return JsonData.ToJson();
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region 保存区域数据
        /// <summary>
        /// 保存区域数据
        /// </summary>
        /// <param name="data"></param>
        /// <returns>0：失败，2:重复，1：成功</returns>
        public int SaveAreaForm(string data)
        {
            try
            {
                AreaInfoEntity entity = data.ToObject<AreaInfoEntity>();
                StringBuilder searchSql = new StringBuilder(" select count(1) from RMRS_AreaInfo where AreaName = ").Append("'" + entity.AreaName + "'");
                if (Convert.ToInt32(sqlHelper.ExecuteScalar(searchSql.ToString(), CommandType.Text, null)) > 0)
                {
                    return 2;
                }

                StringBuilder sb = new StringBuilder(" insert into RMRS_AreaInfo ( AreaName, AreaLoc, AreaMemo ,CompanyId ) values ( ")
                    .Append("'" + entity.AreaName + "', ")
                    .Append("'" + entity.AreaLoc + "', ")
                    .Append("'" + entity.AreaMemo + "',")
                    .Append("'" + OperatorProvider.Provider.Current().CompanyId + "')");
                return sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 获取区域信息
        /// <summary>
        /// 获取区域信息
        /// </summary>
        /// <returns></returns>
        public AreaInfoEntity GetAreaInfo(string id)
        {
            StringBuilder sb = new StringBuilder(" select AreaName, AreaLoc, AreaMemo from RMRS_AreaInfo where AreaID = ").Append(id);
            try
            {
                DataTable dt = sqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, null);
                if (dt.Rows.Count > 0)
                {
                    AreaInfoEntity entity = new AreaInfoEntity();
                    entity.AreaName = dt.Rows[0]["AreaName"].ToString();
                    entity.AreaLoc = dt.Rows[0]["AreaLoc"].ToString();
                    entity.AreaMemo = dt.Rows[0]["AreaMemo"].ToString();
                    return entity;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception)
            {
                return null;
            }
        }
        #endregion

        #region 修改小区信息
        /// <summary>
        /// 修改小区信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int EditAreaForm(string id, string data)
        {
            try
            {
                AreaInfoEntity entity = data.ToObject<AreaInfoEntity>();
                StringBuilder sb = new StringBuilder(" update RMRS_AreaInfo set AreaName = ")
                    .Append("'" + entity.AreaName + "', AreaLoc = ")
                    .Append("'" + entity.AreaLoc + "', AreaMemo = ")
                    .Append("'" + entity.AreaMemo + "'")
                    .Append(" where AreaID = ").Append(id)
                    ;
                return sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 删除区域信息
        /// <summary>
        /// 删除区域信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteAreaForm(string id)
        {
            //删除前判断 有无楼宇信息           
            string selSql = "select * from RMRS_BldgInfo where AreaID = " + id;
            DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
            if (dtResult.Rows.Count > 0)
            {
                return 2;
            }

            StringBuilder sb = new StringBuilder("delete from RMRS_AreaInfo where AreaID = ").Append(id);
            try
            {
                return sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 保存楼宇信息
        /// <summary>
        /// 保存楼宇信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int SaveBldgForm(string parentId, string data)
        {
            try
            {
                BldgInfoEntity entity = data.ToObject<BldgInfoEntity>();
                StringBuilder sb = new StringBuilder(" insert into RMRS_BldgInfo ( AreaID, BldgName ) values ( ")
                    .Append(parentId + ", ")
                    .Append("'" + entity.BldgName + "' )");
                return sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 批量保存楼宇信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int SaveBatchBldgForm(string parentId, string data)
        {
            try
            {
                JObject jObj = Json.ToJObject(data);
                int benginBldbNo = Convert.ToInt32(jObj["BldgBeginNo"].ToString());
                int bldgNum = Convert.ToInt32(jObj["BldgNum"].ToString());
                string bldgExtName = jObj["BldgExtName"].ToString();

                for (int i = 0; i < bldgNum; i++)
                {
                    BldgInfoEntity entity = new BldgInfoEntity();
                    entity.BldgName = (benginBldbNo + i) + bldgExtName;

                    StringBuilder sb = new StringBuilder(" insert into RMRS_BldgInfo ( AreaID, BldgName, BldgNo ) values ( ")
                        .Append(parentId + ", ")
                        .Append("'" + entity.BldgName + "' ) ");
                    sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text);
                }
                return 1;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 获取楼宇信息
        /// <summary>
        /// 获取楼宇信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public BldgInfoEntity GetBldgInfo(string id)
        {
            StringBuilder sb = new StringBuilder(" select BldgName, BldgNo ,AreaID from RMRS_BldgInfo where BldgID = ").Append(id);
            try
            {
                DataTable dt = sqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, null);
                if (dt.Rows.Count > 0)
                {
                    BldgInfoEntity entity = new BldgInfoEntity();
                    entity.BldgName = dt.Rows[0]["BldgName"].ToString();
                    entity.BldgNo = dt.Rows[0]["BldgNo"].ToString();
                    entity.AreaID = dt.Rows[0]["AreaID"].ToString();
                    return entity;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception)
            {
                return null;
            }
        }
        #endregion

        #region 修改楼宇信息
        /// <summary>
        /// 修改楼宇信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public int EditBldgForm(string parentId, string id, string data)
        {
            try
            {
                BldgInfoEntity entity = data.ToObject<BldgInfoEntity>();
                StringBuilder sb = new StringBuilder(" update RMRS_BldgInfo set BldgName = ")
                    .Append("'" + entity.BldgName + "' ")
                    .Append(" where BldgID = ").Append(id);
                return sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 删除楼宇信息
        /// <summary>
        /// 删除楼宇信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteBldgForm(string id)
        {
            //删除前判断 有无单元信息           
            string selSql = "select * from RMRS_ApmtInfo where BldgID = " + id;
            DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
            if (dtResult.Rows.Count > 0)
            {
                return 2;
            }

            StringBuilder sb = new StringBuilder("delete from RMRS_BldgInfo where BldgID = ").Append(id);
            try
            {
                return sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 保存单元信息
        /// <summary>
        /// 保存单元信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int SaveApmtForm(string parentId, string data)
        {
            try
            {
                ApmtInfoEntity entity = data.ToObject<ApmtInfoEntity>();

                StringBuilder sb = new StringBuilder(" insert into RMRS_ApmtInfo ( BldgID, ApmtName ) values ( ")
                    .Append(parentId + ", ")
                    .Append("'" + entity.ApmtName + "') ");
                return sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 批量保存楼宇信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int SaveBatchApmtForm(string parentId, string data)
        {
            try
            {

                JObject jObj = Json.ToJObject(data);
                int benginApmtNo = Convert.ToInt32(jObj["ApmtBeginNo"].ToString());
                int apmtNum = Convert.ToInt32(jObj["ApmtNum"].ToString());
                string apmtExtName = jObj["ApmtExtName"].ToString();

                for (int i = 0; i < apmtNum; i++)
                {
                    ApmtInfoEntity entity = new ApmtInfoEntity();
                    entity.ApmtName = (benginApmtNo + i) + apmtExtName;
                    entity.ApmtNo = (benginApmtNo + i).ToString();

                    StringBuilder sb = new StringBuilder(" insert into RMRS_ApmtInfo ( BldgID, ApmtName) values ( ")
                        .Append(parentId + ", ")
                        .Append("'" + entity.ApmtName + "')");
                    sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text);
                }

                return 1;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 获取单元信息
        /// <summary>
        /// 获取单元信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ApmtInfoEntity GetApmtInfo(string id)
        {
            StringBuilder sb = new StringBuilder(" select ApmtName, ApmtNo,BldgID from RMRS_ApmtInfo where ApmtID = ").Append(id);
            try
            {
                DataTable dt = sqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, null);
                if (dt.Rows.Count > 0)
                {
                    ApmtInfoEntity entity = new ApmtInfoEntity();
                    entity.ApmtName = dt.Rows[0]["ApmtName"].ToString();
                    entity.ApmtNo = dt.Rows[0]["ApmtNo"].ToString();
                    entity.BldgID = dt.Rows[0]["BldgID"].ToString();
                    return entity;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception)
            {
                return null;
            }
        }
        #endregion

        #region 修改单元信息
        /// <summary>
        /// 修改单元信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public int EditApmtForm(string parentId, string id, string data)
        {
            try
            {
                ApmtInfoEntity entity = data.ToObject<ApmtInfoEntity>();

                StringBuilder sb = new StringBuilder(" update RMRS_ApmtInfo set ApmtName = ")
                    .Append("'" + entity.ApmtName + "'")
                    .Append(" where ApmtID = ").Append(id);
                return sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 删除单元信息
        /// <summary>
        /// 删除单元信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteApmtForm(string id)
        {

            //删除前判断 有无用户数据
            string selSql = "select * from RMRS_RoomInfo where ApmtID = " + id;
            DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
            if (dtResult.Rows.Count > 0)
            {
                return 2;
            }

            StringBuilder sb = new StringBuilder("delete from RMRS_ApmtInfo where ApmtID = ").Append(id);
            try
            {
                return sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 保存用户信息
        /// <summary>
        /// 保存用户信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int SaveUserForm(string parentId, string data)
        {
            try
            {
                NBUserEntity entity = data.ToObject<NBUserEntity>();

                entity.ApmtID = parentId;
                string insertSql = DataHelper.ModelToInsertSql<NBUserEntity>(entity, "RMRS_RoomInfo");
                return Convert.ToInt32(sqlHelper.ExecuteScalar(insertSql + " SELECT @@IDENTITY", CommandType.Text).ToString());
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 批量保存用户信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int SaveBatchUserForm(string parentId, string data)
        {
            try
            {
                JObject jObj = Json.ToJObject(data);
                int roomBeginNo = Convert.ToInt32(jObj["RoomBeginNo"].ToString());
                int roomNum = Convert.ToInt32(jObj["RoomNum"].ToString());
                string roomExtName = jObj["RoomExtName"].ToString();

                for (int i = 0; i < roomNum; i++)
                {
                    //修改为同一楼宇 单元名字不能重复
                    StringBuilder searchSql = new StringBuilder(" select count(1) from RMRS_RoomInfo where RoomNo = ").Append("'" + (roomBeginNo + i).ToString() + "' And ApmtID = " + parentId);
                    if (Convert.ToInt32(sqlHelper.ExecuteScalar(searchSql.ToString(), CommandType.Text, null)) > 0)
                    {
                        continue;
                    }

                    StringBuilder sb = new StringBuilder(" insert into RMRS_RoomInfo ( ApmtID, RoomNo, RoomName ) values ( ")
                        .Append(parentId + ", ")
                        .Append("'" + (roomBeginNo + i) + roomExtName + "', ")
                        .Append("'" + (roomBeginNo + i).ToString() + "')");
                    sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text);
                }

                return 1;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 获取用户信息

        /// <summary>
        /// 获取单元信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public NBUserEntity GetUserInfo(string id)
        {
            StringBuilder sb = new StringBuilder(" select * from RMRS_RoomInfo where RoomID = ").Append(id);
            try
            {
                DataTable dt = sqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, null);
                if (dt.Rows.Count > 0)
                {
                    return DataHelper.DataTableToT<NBUserEntity>(dt)[0];
                }
                else
                {
                    return null;
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        #endregion

        #region 修改用户信息
        /// <summary>
        /// 修改单元信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public int EditUserForm(string parentId, string id, string data)
        {
            try
            {
                NBUserEntity entity = data.ToObject<NBUserEntity>();
                string updateSql = DataHelper.ModelToUpdateSql<NBUserEntity>(entity, "RMRS_RoomInfo");
                return sqlHelper.ExecuteNoParams(updateSql.ToString(), CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 删除用户信息
        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteUserForm(string id)
        {
            try
            {
                //删除用户的时候 同步删除表
                string selUserMeter = "SELECT * FROM RMRS_MeterInfo WHERE RoomID = " + id;
                DataTable dtMeter = sqlHelper.ExecuteDataTable(selUserMeter, CommandType.Text, null);
                string meterId = "";
                for (int row = 0; row < dtMeter.Rows.Count; row++)
                {
                    meterId = dtMeter.Rows[row]["MeterID"].ToString();
                    DeleteMeterForm(meterId);
                }

                StringBuilder sb = new StringBuilder("delete from RMRS_RoomInfo where RoomID = ").Append(id);

                return sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 添加用户时，先添加用户，再获得用户ID
        /// <summary>
        ///获得用户ID
        /// </summary>
        /// <param name="RoomNo">用户</param>
        /// <param name="ApmtID">单元ID</param>
        /// <param name="BldgID">楼宇ID</param>
        /// <param name="AreaID">小区ID</param>
        /// <returns></returns>
        public string GetRoomID(string RoomNo, string ApmtID, string BldgID, string AreaID)
        {
            string sql = @"select room.* from RMRS_RoomInfo room
	                            left join RMRS_ApmtInfo apmt on room.ApmtID=apmt.ApmtID
	                            left join RMRS_BldgInfo bldg on apmt.BldgID=bldg.BldgID
	                            left join RMRS_AreaInfo area on bldg.AreaID=area.AreaID
                            where room.RoomNo='" + RoomNo + @"' 
                             and apmt.ApmtID=" + ApmtID + @"
                             and bldg.BldgID=" + BldgID + @"
                             and area.AreaID=" + AreaID;
            DataTable dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
            string roomid = dt.Rows[0]["RoomID"].ToString();
            return roomid;
        }
        #endregion

        #region 保存表具信息
        /// <summary>
        /// 保存用户信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int SaveMeterForm(string parentId, string data)
        {
            try
            {
                NBMeterEntity entity = data.ToObject<NBMeterEntity>();
                entity.RoomID = Convert.ToInt32(parentId);
                NBUserEntity userEntity = GetUserInfo(parentId.ToString());
                entity.ApmtID = Convert.ToInt32(userEntity.ApmtID);
                ApmtInfoEntity apmtInfoEntity = GetApmtInfo(userEntity.ApmtID);
                entity.BldgID = Convert.ToInt32(apmtInfoEntity.BldgID);
                BldgInfoEntity bldgInfoEntity = GetBldgInfo(apmtInfoEntity.BldgID);
                entity.AreaID = Convert.ToInt32(bldgInfoEntity.AreaID);

                string insertSql = DataHelper.ModelToInsertSql<NBMeterEntity>(entity, "RMRS_MeterInfo");

                object meterId = sqlHelper.ExecuteScalar(insertSql + " SELECT @@identity ", CommandType.Text, null);//获取新插入数据的ID

                //华为平台注册                
                JObject jObj = NBCommon.RegisterMeter(entity.IMEI, "0");

                //注册成功
                if (jObj["deviceId"] != null)
                {
                    string deviceId = jObj["deviceId"].ToString();
                    //同步数据库
                    string updateSql = "UPDATE RMRS_MeterInfo SET IOT_Code = '" + deviceId + "', SyncMark = 1 where MeterID = " + meterId.ToString();
                    sqlHelper.ExecuteNoParams(updateSql, CommandType.Text);

                    string selSql = "SELECT * FROM dbo.V_WaterMeters WHERE IOT_Code='" + deviceId + "'";
                    DataTable dtMeter = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                    string protocolType = dtMeter.Rows[0]["MeterTypeName"].ToString();//协议类型
                    string deviceType = dtMeter.Rows[0]["NBDevTypeName"].ToString();
                    string manufacturerId = dtMeter.Rows[0]["NBDevManufacturerName"].ToString();
                    string manufacturerName = dtMeter.Rows[0]["NBDevManufacturerCodeName"].ToString();
                    string model = dtMeter.Rows[0]["NBTypeCodeName"].ToString();
                    //平台基础信息修改 Device Type，Manufacturer ID，Manufacturer Name，Model，Protocol Type，region，organization，timezone
                    //TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
                    //string totalSeconds = ts.TotalSeconds.ToString().Split('.')[0];
                    NBCommon.UpdateMeterParams(deviceId, entity.ElecAddress, deviceType, manufacturerId, manufacturerName, model, protocolType, "WWKJ_" + entity.ElecAddress);
                    return 0;
                }
                //平台设备已经存在
                else if (jObj["error_code"] != null && jObj["error_code"].ToString() == "200")
                {
                    //查找 IMEI 在本地存储的IOT_CODE 有的话 执行删除操作
                    return 2;
                }
                //获取失败
                if (jObj["error_code"] != null)
                {
                    log.Error(jObj.ToString() + "  time:" + DateTime.Now);
                }

                return 0;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message + "  time:" + DateTime.Now);
                return 1;
            }
        }
        #endregion


        #region 编辑表具信息
        /// <summary>
        /// 保存用户信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int EditMeterForm(string meterId, string data)
        {
            try
            {
                NBMeterEntity oldEntity = GetMeterByMeterId(meterId);
                NBMeterEntity entity = data.ToObject<NBMeterEntity>();
                entity.RoomID = oldEntity.RoomID;
                entity.ApmtID = oldEntity.ApmtID;
                entity.BldgID = oldEntity.BldgID;
                entity.AreaID = oldEntity.AreaID;
                entity.MeterID = int.Parse(meterId);
                string updateSql = DataHelper.ModelToUpdateSql<NBMeterEntity>(entity, "RMRS_MeterInfo");
                sqlHelper.ExecuteNonQuery(updateSql, CommandType.Text, null);


                string deviceId = oldEntity.IOT_Code;
                //同步数据库 
                string selSql = "SELECT * FROM dbo.V_WaterMeters WHERE IOT_Code='" + deviceId + "'";
                DataTable dtMeter = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                string protocolType = dtMeter.Rows[0]["MeterTypeName"].ToString();//协议类型
                string deviceType = dtMeter.Rows[0]["NBDevTypeName"].ToString();
                string manufacturerId = dtMeter.Rows[0]["NBDevManufacturerName"].ToString();
                string manufacturerName = dtMeter.Rows[0]["NBDevManufacturerCodeName"].ToString();
                string model = dtMeter.Rows[0]["NBTypeCodeName"].ToString();
                //平台基础信息修改 Device Type，Manufacturer ID，Manufacturer Name，Model，Protocol Type，region，organization，timezone
                TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
                string totalSeconds = ts.TotalSeconds.ToString().Split('.')[0];
                NBCommon.UpdateMeterParams(deviceId, entity.ElecAddress, deviceType, manufacturerId, manufacturerName, model, protocolType, "WWKJ_" + entity.ElecAddress);

                return 0;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message + "  time:" + DateTime.Now);
                return 1;
            }
        }
        #endregion

        #region 获取表具信息
        /// <summary>
        /// 保存用户信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public NBMeterEntity GetMeterByMeterId(string meterId)
        {
            try
            {
                string selSql = "SELECT * FROM RMRS_MeterInfo WHERE MeterID = " + meterId;
                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                List<NBMeterEntity> lists = DataHelper.DataTableToT<NBMeterEntity>(dtResult);
                if (lists != null)
                {
                    return lists[0];
                }
                return null;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        #endregion

        #region 删除表具信息
        /// <summary>
        /// 删除表具信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int DeleteMeterForm(string meterId)
        {
            try
            {
                NBMeterEntity entity = GetMeterByMeterId(meterId);
                string iot_code = entity.IOT_Code;
                //string delSql = "DELETE FROM RMRS_MeterInfo WHERE MeterID=" + meterId;
                string delSql = "UPDATE  RMRS_MeterInfo SET DeleteMark=1 WHERE MeterID=" + meterId;
                sqlHelper.ExecuteNoParams(delSql, CommandType.Text);
                //表具删除  同步的历史数据删除
                //string delHisSql = "DELETE FROM RMRS_HistoryRecord WHERE MeterID=" + meterId;
                //sqlHelper.ExecuteNoParams(delHisSql, CommandType.Text);
                //同步表 弃用  改为 回调模式
                //string delHisTaskSql = "DELETE FROM RMRS_HistoryTask WHERE IOT_Code='" + iot_code + "'";
                //sqlHelper.ExecuteNoParams(delHisTaskSql, CommandType.Text);
                NBCommon.DeleteMeter(iot_code);
                return 1;
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        #endregion
    }
}