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
{
///
/// 分区设置
///
public class PartitionSettingService
{
SqlHelper sqlHelper = new SqlHelper("DMADb");
#region 获取关系表数据
///
/// 获取关系表数据
///
///
///
///
///
///
///
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 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 查询关系表数据分页
///
/// 查询关系表数据分页
///
///
///
///
private List 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 list = DataHelper.DataTableToT(dt);
return list;
}
catch (Exception e)
{
throw e;
}
}
#endregion
#region 获取考核表数据
///
/// 获取考核表数据
///
///
///
///
///
///
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 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 查询数据分页
///
/// 查询数据分页
///
///
///
///
private List 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 list = DataHelper.DataTableToT(dt);
return list;
}
catch (Exception e)
{
throw e;
}
}
#endregion
#region 考核表ID插入到关系表中 考核表 -> 关系表
///
/// 考核表ID插入到关系表中 考核表 -> 关系表
///
///
///
///
///
///
///
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 关系表插入到考核表中 关系表 -> 考核表
///
/// 入口水表挂接关系表插入到考核表中 关系表 -> 考核表
///
///
///
///
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 获取户表挂接数据
///
/// 获取户表挂接数据
///
///
///
///
///
///
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 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 获取户表挂接数据分页
///
/// 获取户表挂接数据分页
///
///
///
///
private List 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 list = DataHelper.DataTableToT(dt);
return list;
}
catch (Exception )
{
throw;
}
}
#endregion
#region 获取可以挂接用户的数据
///
/// 获取可以挂接用户的数据
///
///
///
///
///
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 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 获取可以挂接用户的数据分页
///
/// 获取可以挂接用户的数据分页
///
///
///
///
private List 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 list = DataHelper.DataTableToT(dt);
return list;
}
catch (Exception)
{
throw;
}
}
#endregion
#region 户表插入
///
/// 户表插入
///
///
///
///
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 户表删除
///
/// 户表删除
///
///
///
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
}
}