NBDXSY/LeaRun.Application/LeaRun.Application.Service/DMAManage/PartitionSettingService.cs

using LeaRun.Application.Entity.DMAManage;
using LeaRun.Data;
using LeaRun.Util;
using LeaRun.Util.WebControl;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LeaRun.Application.Service.DMAManage
{
    /// <summary>
    /// 分区设置
    /// </summary>
    public class PartitionSettingService
    {
        SqlHelper sqlHelper = new SqlHelper("DMADb");

        #region 获取关系表数据
        /// <summary>
        /// 获取关系表数据
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="keyType"></param>
        /// <param name="keyWord"></param>
        /// <param name="DMAID"></param>
        /// <param name="FlowType"></param>
        /// <returns></returns>
        public string GetDMAMeterRelations(Pagination pagination, string keyType, string keyWord, string DMAID, string FlowType)
        {
            var watch = CommonHelper.TimerStart();
            var where = " where D.MeterAssessmentId = M.MeterAssessmentId and FlowType = " + FlowType + " and D.DMAID = " + DMAID;
            if (!String.IsNullOrEmpty(keyWord) && !String.IsNullOrEmpty(keyType))
            {
                where = " Where " + keyType + " Like '%" + keyWord + "%' and D.MeterAssessmentId = M.MeterAssessmentId and DMAID = " + DMAID + "and FlowType = " + FlowType;
            }
            List<DMAMeterRelationEntity> list = GetDMAMeterRelationData(pagination, where);
            var JsonData = new
            {
                rows = list,
                total = pagination.total,
                page = pagination.page,
                records = pagination.records,
                costtime = CommonHelper.TimerEnd(watch)
            };
            return JsonData.ToJson();
        }
        #endregion

        #region 查询关系表数据分页
        /// <summary>
        /// 查询关系表数据分页
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        private List<DMAMeterRelationEntity> GetDMAMeterRelationData(Pagination pagination, string where)
        {
            try
            {
                string sql = " select count(1) from DMAMeterRelation D, MeterAssessmentBase M " + 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 RelationID, DMAID, MeterAssessmentId, MeterAssessmentName, MeterAssessmentCode FROM " +
                    "(             " +
                    "SELECT        " +
                    "    ROW_NUMBER() OVER(ORDER BY RelationID) AS RowIndex," +
                    "    *         " +
                    "FROM          " +
                    "    (select D.DMAID, D.RelationID, M.MeterAssessmentId, M.MeterAssessmentName,  M.MeterAssessmentCode " +
                    "    From DMAMeterRelation D, MeterAssessmentBase M " + where + ") as mytable " +
                    " )" +
                    " AS PageTable " +
                    " WHERE RowIndex BETWEEN " + start + " AND " + end;

                DataTable dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
                if (dt.Rows.Count == 0)
                {
                    return null;
                }
                List<DMAMeterRelationEntity> list = DataHelper.DataTableToT<DMAMeterRelationEntity>(dt);
                return list;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region 获取考核表数据
        /// <summary>
        /// 获取考核表数据
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="keyType"></param>
        /// <param name="keyWord"></param>
        /// <param name="DMAID"></param>
        /// <returns></returns>
        public string GetMeterAssessments(Pagination pagination, string keyType, string keyWord, string DMAID)
        {
            var watch = CommonHelper.TimerStart();
            var where = " where  not exists (select MeterAssessmentId from DMAMeterRelation where MeterAssessmentBase.MeterAssessmentId = DMAMeterRelation.MeterAssessmentId and DMAID = " + DMAID + ") and  DeleteMark = 0 ";
            if (!String.IsNullOrEmpty(keyWord) && !String.IsNullOrEmpty(keyType))
            {
                where += " and " + keyType + " Like '%" + keyWord + "%'";
            }
            List<MeterAssessmentEntity> list = GetMetersData(pagination, where);
            var JsonData = new
            {
                rows = list,
                total = pagination.total,
                page = pagination.page,
                records = pagination.records,
                costtime = CommonHelper.TimerEnd(watch)
            };
            return JsonData.ToJson();
        }
        #endregion

        #region 查询数据分页
        /// <summary>
        /// 查询数据分页
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        private List<MeterAssessmentEntity> GetMetersData(Pagination pagination, string where)
        {
            try
            {
                string sql = " select count(1) from MeterAssessmentBase " + 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 * FROM " +
                    "(             " +
                    "SELECT        " +
                    "    ROW_NUMBER() OVER(ORDER BY MeterAssessmentId) AS RowIndex," +
                    "    *         " +
                    "FROM          " +
                    "    (select  MeterAssessmentId, MeterAssessmentName,  MeterAssessmentCode " +
                    "    From MeterAssessmentBase " + where + ") as mytable " +
                    " )" +
                    " AS PageTable " +
                    " WHERE RowIndex BETWEEN " + start + " AND " + end;

                DataTable dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
                if (dt.Rows.Count == 0)
                {
                    return null;
                }
                List<MeterAssessmentEntity> list = DataHelper.DataTableToT<MeterAssessmentEntity>(dt);
                return list;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region 考核表ID插入到关系表中 考核表 -> 关系表
        /// <summary>
        /// 考核表ID插入到关系表中 考核表 -> 关系表
        /// </summary>
        /// <param name="meterIds"></param>
        /// <param name="DMAID"></param>
        /// <param name="OperatorID"></param>
        /// <param name="OrganizeId"></param>
        /// <param name="FlowType"></param>
        /// <returns></returns>
        public string SettingInMeterToRelation(string[] meterIds, string DMAID, string OperatorID, string OrganizeId, string FlowType)
        {
            string result = "";
            foreach (var meterId in meterIds)
            {
                // 是否开启验证
                //string sql = "Select Count(1) from DMAMeterRelation where MeterAssessmentId = " + meterId + " and DMAID = " + DMAID + " and FlowType = 1";
                StringBuilder sb = new StringBuilder("Insert into DMAMeterRelation (DMAID, MeterAssessmentId, FlowType, OperatorID, OrganizeId, DeleteMark, EnabledMark) Values ( ");
                sb.Append(DMAID + ", ");
                sb.Append(meterId + ", ");
                sb.Append(FlowType + ", ");
                sb.Append("'" + OperatorID + "', ");
                sb.Append("'" + OrganizeId + "', ");
                sb.Append("0, ");
                sb.Append("1)");
                try
                {
                    if (sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text) < 1)
                    {
                        result += "数据插入失败，失败考核表ID=" + meterId + ",";
                    }
                }
                catch (Exception)
                {
                    result += "数据插入失败，失败考核表ID=" + meterId + ",";
                }
            }
            return result;
        } 
        #endregion

        #region 关系表插入到考核表中 关系表 -> 考核表
        /// <summary>
        /// 入口水表挂接关系表插入到考核表中 关系表 -> 考核表
        /// </summary>
        /// <param name="meterIds"></param>
        /// <param name="DMAID"></param>
        /// <returns></returns>
        public string SettingInRelationToMeter(string[] meterIds, string DMAID)
        {
            string result = "";
            foreach (var meterId in meterIds)
            {
                // 是否开启验证
                StringBuilder sb = new StringBuilder("delete from DMAMeterRelation where ");
                sb.Append(" MeterAssessmentId = " + meterId);
                sb.Append(" and DMAID = " + DMAID);
                try
                {
                    if (sqlHelper.ExecuteNoParams(sb.ToString(), CommandType.Text) < 1)
                    {
                        result += "数据删除失败，失败考核表ID=" + meterId + ",";
                    }
                }
                catch (Exception)
                {
                    result += "数据删除失败，失败考核表ID=" + meterId + ",";
                }
            }
            return result;
        } 
        #endregion

        #region 获取户表挂接数据
        /// <summary>
        /// 获取户表挂接数据
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="keyType"></param>
        /// <param name="keyWord"></param>
        /// <param name="DMAID"></param>
        /// <returns></returns>
        public string GetDMAMeterUsers(Pagination pagination, string keyType, string keyWord, string DMAID)
        {
            var watch = CommonHelper.TimerStart();
            var where = " where DMAID = " + DMAID;
            if (!String.IsNullOrEmpty(keyWord) && !String.IsNullOrEmpty(keyType))
            {
                where = " Where " + keyType + " Like '%" + keyWord + "%' and DMAID = " + DMAID;
            }
            List<MeterUserEntity> list = GetDMAMeterUsersData(pagination, where);
            var JsonData = new
            {
                rows = list,
                total = pagination.total,
                page = pagination.page,
                records = pagination.records,
                costtime = CommonHelper.TimerEnd(watch)
            };
            return JsonData.ToJson();
        } 
        #endregion

        #region 获取户表挂接数据分页
        /// <summary>
        /// 获取户表挂接数据分页
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        private List<MeterUserEntity> GetDMAMeterUsersData(Pagination pagination, string where)
        {
            try
            {
                string sql = " select count(1) from MeterUserBase " + 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 * FROM " +
                    "(             " +
                    "SELECT        " +
                    "    ROW_NUMBER() OVER(ORDER BY MeterUserId) AS RowIndex," +
                    "    *         " +
                    "FROM          " +
                    "    (select  MeterUserId, ClientNo,  ClientName, MeterCode, MeterAddress " +
                    "    From MeterUserBase " + where + ") as mytable " +
                    " )" +
                    " AS PageTable " +
                    " WHERE RowIndex BETWEEN " + start + " AND " + end;

                DataTable dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
                if (dt.Rows.Count == 0)
                {
                    return null;
                }
                List<MeterUserEntity> list = DataHelper.DataTableToT<MeterUserEntity>(dt);
                return list;
            }
            catch (Exception )
            {
                throw;
            }
        } 
        #endregion

        #region 获取可以挂接用户的数据
        /// <summary>
        /// 获取可以挂接用户的数据
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="keyType"></param>
        /// <param name="keyWord"></param>
        /// <returns></returns>
        public string GetMeterUsers(Pagination pagination, string keyType, string keyWord)
        {
            var watch = CommonHelper.TimerStart();
            var where = " where DMAID IS NULL ";
            if (!String.IsNullOrEmpty(keyWord) && !String.IsNullOrEmpty(keyType))
            {
                where = " Where " + keyType + " Like '%" + keyWord + "%' and DMAID IS NULL ";
            }
            List<MeterUserEntity> list = GetDMAMeterUsersData(pagination, where);
            var JsonData = new
            {
                rows = list,
                total = pagination.total,
                page = pagination.page,
                records = pagination.records,
                costtime = CommonHelper.TimerEnd(watch)
            };
            return JsonData.ToJson();
        } 
        #endregion

        #region 获取可以挂接用户的数据分页
        /// <summary>
        /// 获取可以挂接用户的数据分页
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        private List<MeterUserEntity> GetMeterUsersData(Pagination pagination, string where)
        {
            try
            {
                string sql = " select count(1) from MeterUserBase " + 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 * FROM " +
                    "(             " +
                    "SELECT        " +
                    "    ROW_NUMBER() OVER(ORDER BY MeterUserId) AS RowIndex," +
                    "    *         " +
                    "FROM          " +
                    "    (select  MeterUserId, ClientNo,  ClientName, MeterCode, MeterAddress " +
                    "    From MeterUserBase " + where + ") as mytable " +
                    " )" +
                    " AS PageTable " +
                    " WHERE RowIndex BETWEEN " + start + " AND " + end;

                DataTable dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
                if (dt.Rows.Count == 0)
                {
                    return null;
                }
                List<MeterUserEntity> list = DataHelper.DataTableToT<MeterUserEntity>(dt);
                return list;
            }
            catch (Exception)
            {
                throw;
            }
        } 
        #endregion

        #region 户表插入
        /// <summary>
        /// 户表插入
        /// </summary>
        /// <param name="meterIds"></param>
        /// <param name="DMAID"></param>
        /// <returns></returns>
        public string SettingMeterUserIn(string[] meterIds, string DMAID)
        {
            string result = "";
            foreach (var meterId in meterIds)
            {
                // 是否开启验证
                string sql = "update MeterUserBase set DMAID = " + DMAID + " where MeterUserId = " + meterId;
                try
                {
                    if (sqlHelper.ExecuteNoParams(sql, CommandType.Text) < 1)
                    {
                        result += "数据更新失败，失败户表ID=" + meterId + ",";
                    }
                }
                catch (Exception)
                {
                    result += "数据更新失败，失败户表ID=" + meterId + ",";
                }
            }
            return result;
        }
        #endregion

        #region 户表删除
        /// <summary>
        /// 户表删除
        /// </summary>
        /// <param name="meterIds"></param>
        /// <returns></returns>
        public string SettingMeterUserOut(string[] meterIds)
        {
            string result = "";
            foreach (var meterId in meterIds)
            {
                // 是否开启验证
                string sql = "update MeterUserBase set DMAID = NULL where MeterUserId = " + meterId;
                try
                {
                    if (sqlHelper.ExecuteNoParams(sql, CommandType.Text) < 1)
                    {
                        result += "数据更新失败，失败户表ID=" + meterId + ",";
                    }
                }
                catch (Exception)
                {
                    result += "数据更新失败，失败户表ID=" + meterId + ",";
                }
            }
            return result;
        }
        #endregion
    }
}