/// <summary>
/// 用户列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public static BasePageResponse <Sys_DepartInfoModel> GetDepartList(DepartistRequest request)
{
var response = new BasePageResponse <Sys_DepartInfoModel>();
var whereSql = string.Empty;
if (request.IsValid >= 0)
{
whereSql += $" AND DIIsValid = {request.IsValid}";
}
if (!String.IsNullOrEmpty(request.Name))
{
whereSql += $" AND DIName Like '%{request.Name.Replace("'", string.Empty).Replace(" ", string.Empty).Trim()}%' ";
}
var totalSql = $" SELECT Count(1) FROM Sys_DepartInfo WHERE 1=1 {whereSql}; ";
var total = MysqlHelper.GetScalar <int>(totalSql);
if (total > 0)
{
response.IsSuccess = 1;
response.Total = total;
var sql = $" SELECT * FROM Sys_DepartInfo WHERE 1=1 {whereSql} ";
sql += MysqlHelper.GetPageSql(request.PageIndex, request.PageSize);
response.List = MysqlHelper.GetList <Sys_DepartInfoModel>(sql) ?? new List <Sys_DepartInfoModel>();
}
return(response);
}
/// <summary>
/// 查询数据字典列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public static BasePageResponse <Sys_BaseDictionaryModel> GetDicList(GetDicListRequest request)
{
var response = new BasePageResponse <Sys_BaseDictionaryModel>();
var list = DicList;
if (list == null || !list.Any())
{
return(response);
}
if (request.ParentId > 0)
{
list = list?.Where(x => x.DParentId == request.ParentId).OrderBy(x => x.DCode).ToList();
}
if (request.Code > 0)
{
list = list?.Where(x => x.DCode.ToString().StartsWith($"{request.Code}")).OrderBy(x => x.DCode).ToList();
}
if (!string.IsNullOrEmpty(request.Name))
{
list = list?.Where(x => x.DName.Contains(request.Name)).OrderBy(x => x.DCode).ToList();
}
if (list == null || !list.Any())
{
return(response);
}
var total = list?.Count ?? 0;
if (total > 0)
{
response.IsSuccess = 1;
response.Total = total;
response.List = list.Skip((request.PageIndex - 1) * request.PageSize).Take(request.PageSize)?.ToList();
}
return(response);
}
public async Task <BaseResponse> GetMyDevice(string GroupId, string roles, bool isAdmin, BasePageRequest req)
{
var device = _dr.FindWithOnlineAndImages(a => a.GroupId == GroupId);
var sites = await _ps.GetMySitesIdAsync(GroupId, roles, isAdmin);
device = device.Where(a => sites.Contains(a.ProjectId.Value));
if (req.Search != null && req.Search.Trim() != "")
{
device = device.Where(a => a.DeviceName.Contains(req.Search));
}
int count = device.Count();
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "DeviceSn Asc";
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
var list = await device.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dto = _mapper.Map <List <DeviceDataDto> >(list);
var br = new BasePageResponse <List <DeviceDataDto> >();
br.Success = true;
br.Message = "获取数据成功";
br.PageSize = req.PageSize;
br.CurrentPage = req.PageNo;
br.Count = count;
br.TotalPage = (int)Math.Ceiling((decimal)count / req.PageSize);
br.Data = dto;
return(br);
}
///// <summary>
///// 获取项目或者场站的报警数据,需在Control中获取设备编号列表
/// </summary>
/// <param name="Devices">设备序列号列表</param>
/// <param name="begin">开始时间</param>
/// <param name="end">结束时间</param>
/// <param name="state">报警状态</param>
/// <param name="req">分页基本信息</param>
/// <returns>返回项目获取场站报警分页数据</returns>
public async Task <BaseResponse> GetProjectWarnAsync(List <string> Devices, DateTime begin, DateTime end, bool state, BasePageRequest req)
{
var data = _warn.FindWithCodeAndType(a => Devices.Contains(a.DeviceSn) && a.Dt > begin && a.Dt < end && a.State == state);
int count = data.Count();
if (!string.IsNullOrWhiteSpace(req.Search))
{
data = data.Where(a => a.Code == req.Search);
}
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "Id Asc";
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
var ret = await data.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dtos = _mapper.Map <List <WarnDto> >(ret);
var br = new BasePageResponse <List <WarnDto> >();
br.Success = true;
br.Message = "获取数据成功";
br.PageSize = req.PageSize;
br.CurrentPage = req.PageNo;
br.Count = count;
br.TotalPage = (int)Math.Ceiling((decimal)count / req.PageSize);
br.Data = dtos;
return(br);
}
/// <summary>
/// 查询供应商列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public static BasePageResponse <SupplierModel> GetList(SupplierSearchRequest request)
{
var data = H_SupplierAccess.GetList(request);
var response = new BasePageResponse <SupplierModel>()
{
IsSuccess = data.IsSuccess,
Total = data.Total,
List = new List <SupplierModel>()
};
data?.List?.ForEach(x =>
{
response.List.Add(new SupplierModel
{
Code = x.SCode,
Id = x.Id,
Linker = x.SLinker,
Name = x.SName,
SourceId = x.SSourceId,
Source = Sys_BaseDictionaryAccess.GetDicModel(0, x.SSourceId)?.DName ?? string.Empty,
PmName = x.SPMName,
});
});
return(response);
}
public async Task <BaseResponse> GetPageGroupsAsync(GroupPageListRequest req)
{
BasePageResponse <List <GroupViewModel> > br = new BasePageResponse <List <GroupViewModel> >();
var g = _group.Find(a => true);
if (!string.IsNullOrWhiteSpace(req.Search))
{
g = g.Where(a => a.GroupName.Contains(req.Search) || a.GroupCode.Contains(req.Search) || a.Description.Contains(req.Search));
}
int count = g.Count();
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "Id Asc";
//UserQuery = UserQuery.OrderBy(a => a.Id);
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
var GroupList = await g.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dto = _mapper.Map <List <GroupViewModel> >(GroupList);
br.Success = true;
br.Message = "获取数据成功";
br.PageSize = req.PageSize;
br.CurrentPage = req.PageNo;
br.Count = count;
br.TotalPage = (int)Math.Ceiling((decimal)count / req.PageSize);
br.Data = dto;
return(br);
}
public async Task <BaseResponse> CronJobList(CronJobListRequest request)
{
var filterBuilder = Builders <CronJob> .Filter;
var filter = filterBuilder.Empty;
if (!string.IsNullOrEmpty(request.Name))
{
filter = filter & filterBuilder.Eq("Name", request.Name);
}
if (request.JobState != 0)
{
filter = filter & filterBuilder.Eq("JobState", request.JobState);
}
if (request.CreateTime != null && request.CreateTime.Count == 2)
{
filter = filter & filterBuilder.Gte("CreateTime", request.CreateTime.First()) & filterBuilder.Lte("CreateTime", request.CreateTime.Last());
}
var sort = request.OrderBy == 0 ? Builders <CronJob> .Sort.Ascending(request.OrderByField) : Builders <CronJob> .Sort.Descending(request.OrderByField);
var list = await _cronJobRepository.GetListAsync(filter, request.Skip, request.PageSize, sort);
//最多数到300就不往下继续数了。
long count = await _cronJobRepository.Context.CountDocumentsAsync(filter, new CountOptions { Limit = 300 });
BasePageResponse <CronJob> res = new BasePageResponse <CronJob>();
res.DataList = list;
res.TotalCount = count;
return(BaseResponse <List <CronJob> > .Ok(list));
}
/// <summary>
/// 用户列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public static BasePageResponse <UserModel> GetUserList(UserListRequest request)
{
var response = new BasePageResponse <UserModel>();
var totalSql = "SELECT Count(1) FROM Sys_UserInfo ; ";
var total = MysqlHelper.GetScalar <int>(totalSql);
if (total > 0)
{
response.IsSuccess = 1;
response.Total = total;
response.List = new List <UserModel>();
var sql = "SELECT * FROM Sys_UserInfo ";
sql += MysqlHelper.GetPageSql(request.PageIndex, request.PageSize);
var list = MysqlHelper.GetList <Sys_UserInfoModel>(sql);
list?.ForEach(x =>
{
response.List.Add(new UserModel
{
Account = x.UIAccount,
DepartId = x.UIDepartId,
DepartName = x.UIDepartName,
Id = x.Id,
IsValid = x.UIIsValid,
Name = x.UIName,
R = x.UIResponsibility,
Responsibility = GetResponsibility(x.UIResponsibility)
});
});
}
return(response);
}
/// <summary>
/// 获取无项目的设备
/// </summary>
/// <param name="GroupId">组织编号</param>
/// <param name="req">分页参数</param>
/// <returns>返回无项目设备列表</returns>
public async Task <BaseResponse> GetNoProjectDeviceAsync(string GroupId, BasePageRequest req)
{
var device = _dr.FindWithOnlineAndImages(a => a.GroupId == GroupId && a.ProjectId == null);
int count = device.Count();
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "DeviceSn Asc";
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
var list = await device.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dto = _mapper.Map <List <DeviceDataDto> >(list);
var br = new BasePageResponse <List <DeviceDataDto> >();
br.Success = true;
br.Message = "获取数据成功";
br.PageSize = req.PageSize;
br.CurrentPage = req.PageNo;
br.Count = count;
br.TotalPage = (int)Math.Ceiling((decimal)count / req.PageSize);
br.Data = dto;
return(br);
}
public async Task <BaseResponse> GetMyProjectAsync(string GroupId, string roles, bool isAdmin, BasePageRequest req)
{
//获取用户所有的项目标识
//var pids = await GetMyProjectIdSync(GroupId, roles, isAdmin);
var pids = await GetMyTopProjectIdAsync(GroupId, roles, isAdmin);
//var project = _pr.Find(a => a.GroupId == GroupId && pids.Contains(a.Id));
var project = _pr.FindWithImageAndChildAsync(a => a.GroupId == GroupId && pids.Contains(a.Id));
if (!string.IsNullOrWhiteSpace(req.Search))
{
project = project.Where(a => a.Name.Contains(req.Search));
}
int count = project.Count();
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "Id Asc";
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
var list = await project.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dto = _mapper.Map <List <ProjectDto> >(list);
foreach (var item in dto)
{
var sites = await GetProjectSitesIdAsync(item.Id);
int num = await _dr.Find(a => sites.Contains(a.ProjectId.Value)).CountAsync();
item.DeviceCount = num;
}
//foreach (var item in list)
//{
// var dto = _mapper.Map<ProjectDto>(item);
// //if (item.Images.Count > 0)
// //{
// // dto.Image = item.Images.OrderByDescending(a => a.Rank).FirstOrDefault().url;
// //}
// //dto.Image = item.Images.OrderByDescending(a => a.Rank).FirstOrDefault().url;
// await GetChild(dto, item.Id);
// dtos.Add(dto);
//}
//var dto = _mapper.Map<List<ProjectData>>(list);
var br = new BasePageResponse <List <ProjectDto> >();
br.Success = true;
br.Message = "获取数据成功";
br.PageSize = req.PageSize;
br.CurrentPage = req.PageNo;
br.Count = count;
br.TotalPage = (int)Math.Ceiling((decimal)count / req.PageSize);
br.Data = dto;
return(br);
}
/// <summary>
/// 查询供应商列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public static BasePageResponse <H_SupplierModel> GetList(SupplierSearchRequest request)
{
var response = new BasePageResponse <H_SupplierModel>();
var sql = new StringBuilder();
var sqlTotal = new StringBuilder();
var sqlWhere = new StringBuilder();
var para = new DynamicParameters();
#region Where条件
if (request.Id > 0)
{
sqlWhere.Append(" AND Id = @Id ");
para.Add("@Id", request.Id);
}
if (request.SourceId > 0)
{
sqlWhere.Append(" AND SSourceId = @SourceId ");
para.Add("@SourceId", request.SourceId);
}
if (request.IsValid > 0)
{
sqlWhere.Append(" AND SIsValid = @IsValid ");
para.Add("@IsValid", request.IsValid == 1 ? 1 : 0);
}
if (!string.IsNullOrEmpty(request.Code))
{
sqlWhere.Append(" AND SCode = @Code ");
para.Add("@Code", request.Code);
}
if (!string.IsNullOrEmpty(request.Name))
{
sqlWhere.Append(" AND SName Like @Name ");
para.Add("@Name", $"%{request.Name}%");
}
if (!string.IsNullOrEmpty(request.LinkerName))
{
sqlWhere.Append(" AND SLinker Like @LinkerName ");
para.Add("@LinkerName", $"%{request.LinkerName}%");
}
#endregion
sqlTotal.Append(" SELECT Count(1) FROM H_Supplier WHERE 1=1 ");
sqlTotal.Append(sqlWhere);
var total = MysqlHelper.GetScalar <int>(sqlTotal.ToString(), para);
response.IsSuccess = 1;
if (total > 0)
{
sql.Append(" SELECT * FROM H_Supplier WHERE 1=1 ");
sql.Append(sqlWhere);
sql.Append(" ORDER BY ID DESC ");
sql.Append(MysqlHelper.GetPageSql(request.PageIndex, request.PageSize));
response.Total = total;
response.List = MysqlHelper.GetList <H_SupplierModel>(sql.ToString(), para);
}
return(response);
}
/// <summary>
/// 获取设备的报警，没有输入时间，则时间为昨天到今天的时间,搜索条件为Code
/// </summary>
/// <param name="deviceSn"></param>
/// <param name="req"></param>
/// <returns></returns>
public async Task <BaseResponse> GetWarnByDeviceSnAsync(string deviceSn, DeviceWarnPageRequest req)
{
var data = _warn.FindWithCodeAndType(a => a.DeviceSn == deviceSn);
if (req.BeginTime == null)
{
req.BeginTime = DateTime.Now.AddDays(-1);
}
if (req.EndTime == null)
{
req.EndTime = DateTime.Now;
}
if (req.BeginTime >= req.EndTime)
{
return(new BaseResponse {
Success = false, Message = "开始时间不能大于或者等于结束时间"
});
}
data = data.Where(a => a.Dt > req.BeginTime && a.Dt < req.EndTime);
if (req.State != 0)
{
data = data.Where(a => a.State == true);
}
else
{
data = data.Where(a => a.State == false);
}
int count = data.Count();
if (!string.IsNullOrWhiteSpace(req.Search))
{
data = data.Where(a => a.Code == req.Search);
}
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "Id Asc";
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
var ret = await data.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dtos = _mapper.Map <List <WarnDto> >(ret);
var br = new BasePageResponse <List <WarnDto> >();
br.Success = true;
br.Message = "获取数据成功";
br.PageSize = req.PageSize;
br.CurrentPage = req.PageNo;
br.Count = count;
br.TotalPage = (int)Math.Ceiling((decimal)count / req.PageSize);
br.Data = dtos;
return(br);
}
/// <summary>
/// 酒店查询
/// </summary>
/// <param name="request"></param>
public BasePageResponse <H_HotelPictureModel> GetList(HotelPicSearchRequest request)
{
var response = new BasePageResponse <H_HotelPictureModel>();
response.Total = Query().Count(x => x.HIId == request.HotelId);
if (response.Total > 0)
{
response.IsSuccess = 1;
var sql = "SELECT * FROM H_HotelPicture ";
sql += MysqlHelper.GetPageSql(request.PageIndex, request.PageSize);
response.List = MysqlHelper.GetList <H_HotelPictureModel>(sql) ?? new List <H_HotelPictureModel>();
}
return(response);
}
/// <summary>
/// 获取订单日志
/// </summary>
/// <param name="orderid"></param>
/// <returns></returns>
public static BasePageResponse <HO_HotelOrderLogModel> GetOrderLogList(OrderLogSearchRequset request)
{
var response = new BasePageResponse <HO_HotelOrderLogModel>();
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"SELECT * FROM ho_hotelorderlog WHERE HOLOrderId='{0}' LIMIT 1;", request.CustomerSerialId);
var list = MysqlHelper.GetList <HO_HotelOrderLogModel>(sb.ToString());
var total = list?.Count ?? 0;
if (total > 0)
{
response.IsSuccess = 1;
response.Total = total;
response.List = list.Skip((request.PageIndex - 1) * request.PageSize).Take(request.PageSize)?.ToList();
}
return(response);
}
public async Task <BaseResponse> GetPageWarnCodeAsync(WarnCodePageRequest req)
{
Expression <Func <WarnCodeModel, bool> > predicate;
if (req.warnTypeId == 0)
{
predicate = a => true;
}
else
{
predicate = a => a.WarnTypeId == req.warnTypeId;
}
var wc = _warnCode.FindWithWarnType(predicate);
if (!string.IsNullOrWhiteSpace(req.Search))
{
wc = wc.Where(a => a.Code.Contains(req.Search));
}
int count = wc.Count();
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "Code Asc";
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
var data = await wc.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dto = _mapper.Map <IEnumerable <WarnCodeDto> >(data);
BaseResponse br = new BasePageResponse <IEnumerable <WarnCodeDto> >()
{
Count = count,
CurrentPage = req.PageNo,
PageSize = req.PageSize,
Success = true,
Message = "获取数据成功",
Data = dto,
TotalPage = (int)Math.Ceiling((decimal)count / req.PageSize)
};
return(br);
}
/// <summary>
/// 查询数据字典
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public static Sys_BaseDictionaryModel GetDicModel(int id, int code)
{
var response = new BasePageResponse <Sys_BaseDictionaryModel>();
var list = DicList;
if (list == null || !list.Any())
{
return(null);
}
if (id > 0)
{
return(list.FirstOrDefault(x => x.Id == id));
}
if (code > 0)
{
return(list.FirstOrDefault(x => x.DCode == code));
}
return(null);
}
/// <summary>
/// 酒店查询
/// </summary>
/// <param name="request"></param>
public static BasePageResponse<H_HotelRoomModel> GetList(HotelRoomSearchRequest request)
{
var db = new H_HotelRoomAccess();
var query = db.Query().Where(x => x.HIId == request.HotelId).OrderByDescending(x => x.Id);
if (request.IsValiad >= 0)
{
query.Where(x => x.HRIsValid == request.IsValiad);
}
var list = query.ToList();
var response = new BasePageResponse<H_HotelRoomModel>()
{
IsSuccess = 1,
Total = list?.Count ?? 0,
List = list
};
return response;
}
public async Task <BaseResponse> GetUsersAsync(UserPageRequestViewModel req, string GroupId)
{
var UserQuery = _user.Find(a => a.GroupId == GroupId);
if (req.Status.HasValue)
{
UserQuery = UserQuery.Where(a => a.Status == (UserStatus)req.Status);
}
if (!string.IsNullOrWhiteSpace(req.Search))
{
UserQuery = UserQuery.Where(a => a.UserName == req.Search || a.Account == req.Search);
}
int count = UserQuery.Count();
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "Id Asc";
//UserQuery = UserQuery.OrderBy(a => a.Id);
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
var userList = await UserQuery.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dto = _mapper.Map <List <UserData> >(userList);
var ret = new BasePageResponse <List <UserData> >()
{
Count = count,
CurrentPage = req.PageNo,
PageSize = req.PageSize,
Success = true,
Message = "获取数据成功",
Data = dto,
TotalPage = (int)Math.Ceiling((decimal)count / req.PageSize)
};
return(ret);
}
public async Task <BaseResponse> GetMySiteAsync(string GroupId, string roles, bool isAdmin, BasePageRequest req)
{
//获取用户所有的项目标识
var sites = await GetMySitesIdAsync(GroupId, roles, isAdmin);
var project = _pr.FindWithImageAndDeviceAsync(a => a.GroupId == GroupId && sites.Contains(a.Id));
if (!string.IsNullOrWhiteSpace(req.Search))
{
project = project.Where(a => a.Name.Contains(req.Search));
}
int count = project.Count();
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "Id Asc";
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
var list = await project.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dto = _mapper.Map <List <SiteDto> >(list);
var br = new BasePageResponse <List <SiteDto> >();
br.Success = true;
br.Message = "获取数据成功";
br.PageSize = req.PageSize;
br.CurrentPage = req.PageNo;
br.Count = count;
br.TotalPage = (int)Math.Ceiling((decimal)count / req.PageSize);
br.Data = dto;
return(br);
}
public async Task <BaseResponse> GetDeviceHisDataAsync(string DeviceSn, DeviceHisDataPageRequest req)
{
var data = _dhr.FindWithDevice(a => a.DeviceSn == DeviceSn);
//if (!string.IsNullOrWhiteSpace(req.Search))
//{
// data = data.Where(a => a.Name.Contains(req.Search));
//}
data = data.Where(a => a.Dt > req.Begin && a.Dt < req.End);
int count = data.Count();
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "dt desc";
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
var list = await data.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dtos = _mapper.Map <List <DeviceHisDataDto> >(list);
var br = new BasePageResponse <List <DeviceHisDataDto> >()
{
Success = true,
Message = "获取数据成功",
PageSize = req.PageSize,
CurrentPage = req.PageNo,
Count = count,
TotalPage = (int)Math.Ceiling((decimal)count / req.PageSize),
Data = dtos
};
return(br);
}
//对datakey或者dataname进行查询
public async Task<BaseResponse> GetTypeDataDefines(int typeId, TypeDataDefinePageRequest req)
{
var data = _td.Find(a => a.TypeId == typeId);
if (!string.IsNullOrWhiteSpace(req.Search))
{
data = data.Where(a => a.DataKey.Contains(req.Search) || a.DataName.Contains(req.Search));
}
//单个标签查找，第一步先查找包含的id，第二步查找id包含的，如果是多个标签的或查找需要根据标签或
//Regex r = new Regex(@"(?=(,|\b)1\b)|(?=(,|\b)2\b)")
if (req.CategoryId != 0)
{
string txt = $@"(?=(,|\b){req.CategoryId}\b)";
Regex r = new Regex(@txt);
var Ids = _td.Find(a => a.TypeId == typeId).ToList().Where(a =>
{
if (a.Category == null)
{
return false;
}
else
{
return r.Match(a.Category).Success == true;
}
}).Select(a => a.Id).ToList();
data = data.Where(a => Ids.Contains(a.Id));
}
int count = data.Count();
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "Id Asc";
//UserQuery = UserQuery.OrderBy(a => a.Id);
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
var entityList = await data.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dtos = _mapper.Map<List<TypeDataDefineData>>(entityList);
#region 替换category
var cr = await _cr.Find(a => true).ToListAsync();
if (cr.Count > 0)
{
foreach (var item in dtos)
{
if (item.Category != null && item.Category.Length > 0)
{
var cid = item.Category.Split(',');
string cname = "";
for (int i = 0; i < cid.Length; i++)
{
if (cid[i].Trim() != "")
{
if (cname == "")
{
cname += cr.FirstOrDefault(a => a.Id.ToString() == cid[i]).Name;
}
else
{
cname += "," + cr.FirstOrDefault(a => a.Id.ToString() == cid[i]).Name;
}
}
}
item.Category = cname;
}//end if
}
}
#endregion
var ret = new BasePageResponse<List<TypeDataDefineData>>
{
Success = true,
Message = "获取数据成功",
Count = count,
CurrentPage = req.PageNo,
PageSize = req.PageSize,
TotalPage = (int)Math.Ceiling((decimal)count / req.PageSize),
Data = dtos
};
return ret;
}
/// <summary>
/// 统计订单
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public static BasePageResponse <OrderStaticResponse> GetOrderStaticList(OrderStaticRequest request)
{
var response = new BasePageResponse <OrderStaticResponse>();
StringBuilder sb = new StringBuilder();
StringBuilder sbwhere = new StringBuilder();
if (request.Type == 1 && request.TimeType != 0)
{
if (request.TimeType == 1)
{
sbwhere.AppendFormat(" AND ho.HOAddTime>= '{0}'", request.StartTime);
sbwhere.AppendFormat(" AND ho.HOAddTime< '{0}'", request.EndTime);
}
else
{
sbwhere.AppendFormat(" AND ho.HOCheckOutDate>= '{0}'", request.StartTime);
sbwhere.AppendFormat(" AND ho.HOCheckOutDate< '{0}'", request.EndTime);
}
}
if (request.PrivoceId > 0)
{
sbwhere.AppendFormat(" AND hb.HIProvinceId= {0}", request.PrivoceId);
}
if (request.CityId > 0)
{
sbwhere.AppendFormat(" AND hb.HICityId= {0}", request.CityId);
}
if (request.Part1 > 0)
{
sbwhere.AppendFormat(" AND ho.HoPlat1= {0}", request.Part1);
}
if (request.Part2 > 0)
{
sbwhere.AppendFormat(" AND hb.HoPlat2= {0}", request.Part2);
}
if (!string.IsNullOrWhiteSpace(request.HotelName))
{
sbwhere.AppendFormat(" AND ho.HName IN ({0})", request.HotelName);
}
if (!string.IsNullOrWhiteSpace(request.HotelId))
{
sbwhere.AppendFormat(" AND ho.HId IN ({0})", request.HotelId);
}
if (!string.IsNullOrWhiteSpace(request.SupplierName))
{
sbwhere.AppendFormat(" AND ho.HOSupperlierName Like '%{0}%'", request.SupplierName);
}
if (request.SupplierSource > 0)
{
sbwhere.AppendFormat(" AND ho.HOSupplierSourceId ={0}", request.SupplierSource);
}
sb.AppendFormat(@"SELECT
DATE_FORMAT(ho.HOAddTime, '%Y-%m-%d') AS CreateTime,
DATE_FORMAT(
ho.HOCheckOutDate,
'%Y-%m-%d'
) AS CheckOutDate,
hb.HIProvinceId AS ProvinceId,
hb.HIProvince AS ProvinceName,
hb.HICityId AS CityId,
hb.HICity AS CityName,
ho.HName AS HotelName,
ho.HIId AS HotelId,
ho.HOSupperlierName AS SupperlierName,
ho.HOSupplierId AS SupperlierId,
count(ho.Id) AS TotalCreate,
count(
CASE
WHEN ho.HOStatus = 1 THEN
1
END
) AS TotalSuccess,
sum(ho.HOSellPrice) AS TotalSell,
sum(ho.HOContractPrice) AS TotalContract,
sum(
ho.HOSellPrice - ho.HOContractPrice
) AS TotalRevenue
FROM
ho_hotelorder ho
INNER JOIN h_hotelinfo hb ON hb.Id = ho.HIId
WHERE
1 = 1
{0}
GROUP BY
DATE_FORMAT(ho.HOAddTime, '%Y-%m-%d'),
DATE_FORMAT(
ho.HOCheckOutDate,
'%Y-%m-%d'
),
hb.HIProvinceId,
hb.HIProvince,
hb.HICityId,
hb.HICity,
ho.HName,
ho.HIId,
ho.HOSupperlierName,
ho.HOSupplierId" , sbwhere.ToString());
var list = MysqlHelper.GetList <OrderStaticResponse>(sb.ToString());
var total = list?.Count ?? 0;
if (total > 0)
{
response.IsSuccess = 1;
response.Total = total;
response.List = list.Skip((request.PageIndex - 1) * request.PageSize).Take(request.PageSize)?.ToList();
}
return(response);
}
//根据项目标示获取项目下的子项目(不含嵌套数据,不包含项目下设备的数量)
public async Task <BaseResponse> GetChildProjectByIdAsync(int Id, ProjectPageRequest req)
{
var data = _pr.FindWithImageAndChildAsync(a => a.ParentId == Id);
if (req.ProjectType == 1) //项目
{
data = data.Where(a => a.ProjectType == ProjectType.Project);
}
else if (req.ProjectType == 2)//场站
{
data = data.Where(a => a.ProjectType == ProjectType.Site);
}
if (!string.IsNullOrWhiteSpace(req.Search))
{
data = data.Where(a => a.Name.Contains(req.Search));
}
int count = data.Count();
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "Id Asc";
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
var list = await data.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dtos = _mapper.Map <List <ProjectDto> >(list);
//获取项目的区域名称
foreach (var item in dtos)
{
//获取项目或者场站下设备的数量
if (item.ProjectType == (int)ProjectType.Project)
{
var sites = await GetProjectSitesIdAsync(item.Id);
int num = await _dr.Find(a => sites.Contains(a.ProjectId.Value)).CountAsync();
item.DeviceCount = num;
}
else
{
int num = await _dr.Find(a => a.ProjectId == item.Id).CountAsync();
item.DeviceCount = num;
}
//获取区域名称
if (item.RegionId != null && "" != item.RegionId)
{
var r = await _rr.FindAsync(item.RegionId, item.GroupId);
if (r != null)
{
item.RegionName = r.Name;
}
}
}
var br = new BasePageResponse <List <ProjectDto> >();
br.Success = true;
br.Message = "获取数据成功";
br.PageSize = req.PageSize;
br.CurrentPage = req.PageNo;
br.Count = count;
br.TotalPage = (int)Math.Ceiling((decimal)count / req.PageSize);
br.Data = dtos;
return(br);
}
//private static List<OrderSearchResponse> List = new List<OrderSearchResponse>();
/// <summary>
/// 订单列表
/// </summary>
public static BasePageResponse <OrderSearchResponse> GetOrderList(OrderSearchRequset request)
{
var response = new BasePageResponse <OrderSearchResponse>();
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT
Id,
HOCustomerSerialId,
HOSupplierSourceName,
HIId,
HName,
HOCheckInDate,
HOCheckOutDate,
HOLinkerName,
HOStatus,
HOSellPrice,
HOContractPrice
FROM
Ho_HotelOrder
WHERE
1 = 1");
//订单号
if (!string.IsNullOrWhiteSpace(request.HOCustomerSerialId))
{
sb.AppendFormat(" AND HOCustomerSerialId Like '%{0}%'", request.HOCustomerSerialId);
}
//人员归属查询
if (!string.IsNullOrWhiteSpace(request.PeopleMobile) || !string.IsNullOrWhiteSpace(request.PeopleName))
{
if (request.CustomerType == 1)
{
sb.AppendFormat(" AND HOCustomerName Like '%{0}%' AND HOCustomerMobile = '{1}'", request.PeopleName, request.PeopleMobile);
}
else
{
sb.AppendFormat(" AND HOLinkerName Like '%{0}%' AND HOLinkerMobile = '{1}'", request.PeopleName, request.PeopleMobile);
}
}
//时间
if (!string.IsNullOrWhiteSpace(request.StartTime) || !string.IsNullOrWhiteSpace(request.EndTime))
{
if (request.TimeType == 1)
{
sb.AppendFormat(" AND HOCheckInDate >= '{0}' AND HOCheckInDate<'{0}'", request.StartTime, Convert.ToDateTime(request.EndTime).AddDays(1).ToShortDateString());
}
else
{
sb.AppendFormat(" AND HOAddTime >= '{0}' AND HOAddTime<'{0}'", request.StartTime, Convert.ToDateTime(request.EndTime).AddDays(1).ToShortDateString());
}
}
//来源
if (request.SourceId > 0)
{
sb.AppendFormat(" AND HOSupplierSourceId = {0}", request.SourceId);
}
//酒店名称
if (!string.IsNullOrWhiteSpace(request.HotelName))
{
sb.AppendFormat(" AND HName Like '%{0}%'", request.HotelName);
}
//酒店Id
if (!string.IsNullOrWhiteSpace(request.HIId))
{
sb.AppendFormat(" AND HIId = '{0}'", request.HIId);
}
//第三方流水
if (!string.IsNullOrWhiteSpace(request.HOOutSerialId))
{
sb.AppendFormat(" AND HOOutSerialId = '{0}'", request.HOOutSerialId);
}
//订单状态
if (request.HOStatus != null)
{
string state = "";
foreach (var item in state)
{
state += "'" + item + "',";
}
sb.AppendFormat(" AND HOStatus IN ({0})", state.Substring(0, state.Length - 1));
}
var list = MysqlHelper.GetList <OrderSearchResponse>(sb.ToString());
var total = list?.Count ?? 0;
if (total > 0)
{
response.IsSuccess = 1;
response.Total = total;
response.List = list.Skip((request.PageIndex - 1) * request.PageSize).Take(request.PageSize)?.ToList();
}
return(response);
}
public async Task <BaseResponse> GetDataDefineLibrarysAsync(DataDefineLibraryPageRequest req)
{
var ret = _dlr.Find(a => true);
if (!string.IsNullOrWhiteSpace(req.Search))
{
ret = ret.Where(a => a.DataKey.Contains(req.Search) || a.DataName.Contains(req.Search));
}
//单个标签查找，第一步先查找包含的id，第二步查找id包含的，如果是多个标签的或查找需要根据标签或
//Regex r = new Regex(@"(?=(,|\b)1\b)|(?=(,|\b)2\b)")
if (req.CategoryId != 0)
{
string txt = $@"(?=(,|\b){req.CategoryId}\b)";
Regex r = new Regex(@txt);
var Ids = _dlr.Find(a => true).ToList().Where(a =>
{
if (a.Category == null)
{
return(false);
}
else
{
return(r.Match(a.Category).Success == true);
}
}).Select(a => a.Id).ToList();
ret = ret.Where(a => Ids.Contains(a.Id));
}
int Count = 0;
string OrderExpression = "";
if (string.IsNullOrEmpty(req.OrderBy))
{
OrderExpression = "Id Asc";
}
else
{
OrderExpression = string.Format("{0} {1}", req.OrderBy, req.OrderType);
}
Count = ret.Count();
var data = await ret.OrderBy(OrderExpression).Skip((req.PageNo - 1) * req.PageSize).Take(req.PageSize).ToListAsync();
var dto = _mapper.Map <IEnumerable <DataDefineLibraryDataDto> >(data);
#region 替换category
var cr = await _cr.Find(a => true).ToListAsync();
if (cr.Count > 0)
{
foreach (var item in dto)
{
if (item.Category != null && item.Category.Length > 0)
{
var cid = item.Category.Split(',');
string cname = "";
for (int i = 0; i < cid.Length; i++)
{
if (cid[i].Trim() != "")
{
if (cname == "")
{
cname += cr.FirstOrDefault(a => a.Id.ToString() == cid[i]).Name;
}
else
{
cname += "," + cr.FirstOrDefault(a => a.Id.ToString() == cid[i]).Name;
}
}
}
item.Category = cname;
}//end if
}
}
#endregion
BaseResponse br = new BasePageResponse <IEnumerable <DataDefineLibraryDataDto> >()
{
Count = Count,
CurrentPage = req.PageNo,
PageSize = req.PageSize,
Success = true,
Message = "获取数据成功",
Data = dto,
TotalPage = (int)Math.Ceiling((decimal)Count / req.PageSize)
};
return(br);
}
/// <summary>
/// 酒店查询
/// </summary>
/// <param name="request"></param>
public static BasePageResponse <HotelSearchResponse> GetList(HotelSearchRequest request)
{
var response = new BasePageResponse <HotelSearchResponse>();
var sql = new StringBuilder();
var sqlTotal = new StringBuilder();
var sqlWhere = new StringBuilder();
var para = new DynamicParameters();
var idList = new List <int>(); //酒店Id
var hrsList = new List <H_HotelRoomRuleModel>(); //价格政策查的供应商
var hotelList = new List <H_HotelInfoModel>(); //酒店列表
if (request.SourceId > 0 || !string.IsNullOrEmpty(request.SupplierName))
{//需要查政策
hrsList = GetSupplier(request.SourceId, request.SupplierName, null);
idList = hrsList?.Select(x => x.HIId)?.ToList();
}
#region Where条件
if (idList != null && idList.Count > 0)
{
sqlWhere.Append($" AND Id IN ({string.Join(",", idList)} ) ");
}
if (request.Id > 0)
{
sqlWhere.Append(" AND Id = @Id ");
para.Add("@Id", request.Id);
}
//if (request.SourceId > 0)
//{
// sqlWhere.Append(" AND SSourceId = @SourceId ");
// para.Add("@SourceId", request.SourceId);
//}
//if (!string.IsNullOrEmpty(request.LinkerName))
//{
// sqlWhere.Append(" AND SLinker Like @LinkerName ");
// para.Add("@LinkerName", $"%{request.LinkerName}%");
//}
if (request.IsValid > 0)
{
sqlWhere.Append(" AND SIsValid = @IsValid ");
para.Add("@IsValid", request.IsValid == 1 ? 1 : 0);
}
if (request.ProvId > 0)
{
sqlWhere.Append(" AND HIProvinceId = @ProvId ");
para.Add("@ProvId", request.ProvId);
}
if (request.CityId > 0)
{
sqlWhere.Append(" AND HICityId = @CityId ");
para.Add("@CityId", request.CityId);
}
if (!string.IsNullOrEmpty(request.Name))
{
sqlWhere.Append(" AND HIName Like @Name ");
para.Add("@Name", $"%{request.Name}%");
}
#endregion
sqlTotal.Append(" SELECT Count(1) FROM h_hotelinfo WHERE 1=1 ");
sqlTotal.Append(sqlWhere);
var total = MysqlHelper.GetScalar <int>(sqlTotal.ToString(), para);
response.IsSuccess = 1;
if (total > 0)
{
sql.Append(" SELECT * FROM h_hotelinfo WHERE 1=1 ");
sql.Append(sqlWhere);
sql.Append(" ORDER BY ID DESC ");
sql.Append(MysqlHelper.GetPageSql(request.PageIndex, request.PageSize));
response.Total = total;
hotelList = MysqlHelper.GetList <H_HotelInfoModel>(sql.ToString(), para);
//重新查资源
hrsList = GetSupplier(0, string.Empty, hotelList.Select(x => x.Id).ToList());
response.List = new List <HotelSearchResponse>();
hotelList?.ForEach(x =>
{
var price = hrsList.Where(s => s.HIId == x.Id)?.ToList();
var source = string.Empty;
var supplierName = string.Empty;
if (price != null && price.Count > 0)
{
source = string.Join(",", price.Select(s => s.HRRSourceName).Distinct());
supplierName = string.Join(",", price.Select(s => s.HRRSupplierName).Distinct());
}
response.List.Add(new HotelSearchResponse
{
Id = x.Id,
Name = x.HIName,
//SourceId = x.SSourceId,
CityId = x.HICityId,
CityName = x.HICity,
ProvName = x.HIProvince,
ProvId = x.HIProvinceId,
Valid = x.HIIsValid,
Source = source ?? string.Empty,
SupplierName = supplierName ?? string.Empty,
});
});
}
return(response);
}
