123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442 |
- 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
- }
- }
|