/// <summary>
/// 获取T_TenantPermissions数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TTenantPermissionsModel, List <TTenantPermissionsModel> > > GetAsync(RequestObject <TTenantPermissionsModel> requestObject)
{
try
{
List <TTenantPermissionsModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TTenantPermissionsModel, TTenantsModel, TPMMenusDbModel, TUsersModel, TSMCompanyDbModel>(
(t, t0, t2, t3, t4) => new object[]
{
JoinType.Left, t.TenantId == t0.ID,
JoinType.Left, t.MenuId == t2.Id,
JoinType.Left, t.CreateId == t3.Id,
JoinType.Left, t0.ID == t4.CompanyInfoId
});
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where($"t.{p}"));
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
requestObject.OrderByConditions.ForEach(p => query.OrderBy($"{p.Column} {p.Condition}"));
}
//设置多表查询返回实体类
query.Select((t, t0, t2, t3, t4) => new TTenantPermissionsModel
{
Id = t.Id,
TenantId = t.TenantId,
TTenantsTenantName = t4.CompanyName,
MenuId = t.MenuId,
TMenusMenuName = t2.MenuName,
ButtonIds = t.ButtonIds,
CreateTime = t.CreateTime,
CreateId = t.CreateId,
TUsersUserName = t3.UserName,
MenuParentId = t2.ParentID
});
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.ToListAsync();
}
//返回执行结果
return(ResponseUtil <TTenantPermissionsModel, List <TTenantPermissionsModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TTenantPermissionsModel, List <TTenantPermissionsModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// 根据角色ID 获取菜单树
/// </summary>
/// <param name="requestObject"></param>
/// <returns></returns>
public async Task <ResponseObject <TRolePermissionsModel, List <MenuViewModel> > > LoadMenuByRoles(RequestObject <TRolePermissionsModel> requestObject)
{
try
{
List <MenuViewModel> queryData = null;//查询结果集对象
List <TButtonsModel> buttonsModels = _db.Instance.Queryable <TButtonsModel>().ToList();
RefAsync <int> totalNumber = -1;//总记录数
var query = _db.Instance.Queryable <TRolePermissionsModel, TRolesModel, TPMMenusDbModel>(
(t, t0, t2) => new object[]
{
JoinType.Inner, t.RoleId == t0.Id,
JoinType.Inner, t.MenuId == t2.Id
});
if (requestObject.QueryConditions == null || requestObject.QueryConditions.Count() == 0)
{
ResponseUtil <TRolePermissionsModel, List <MenuViewModel> > .FailResult(requestObject, null, "查询条件必须含有roleid");
}
else
{
if (!requestObject.QueryConditions.Any(p => p.Column.ToLower() == "roleid"))
{
ResponseUtil <TRolePermissionsModel, List <MenuViewModel> > .FailResult(requestObject, null, "查询条件必须含有roleid");
}
else
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where($"t.{p}"));
}
}
//获取菜单
var memu = query.Select((t, t0, t2) => new { buttons = t.ButtonIds, memu = t2 }).ToList();
var menuDictoray = memu.ToDictionary(p => p.memu.Id, p => p.buttons);
List <int> allMenuIDS = new List <int>();
foreach (var item in memu)
{
var ids = item.memu.LogicPath.Split('.').Select(p => Convert.ToInt32(p)).ToList();
allMenuIDS.AddRange(ids);
}
allMenuIDS = allMenuIDS.Distinct().ToList();
var allDisplayNodes = await _db.Instance.Queryable <TPMMenusDbModel>().Where(p => allMenuIDS.Contains(p.Id) && p.Status == true).ToListAsync(); //所有需要展示的菜单
var data = GetMenuTree(allDisplayNodes, menuDictoray, buttonsModels, -1);
return(ResponseUtil <TRolePermissionsModel, List <MenuViewModel> > .SuccessResult(requestObject, data, totalNumber));
}
catch (Exception ex)
{
return(ResponseUtil <TRolePermissionsModel, List <MenuViewModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// 获取T_Users数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TUsersModel, List <TUsersModel> > > GetAsync(RequestObject <TUsersModel> requestObject)
{
try
{
List <TUsersModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TUsersModel, TRolesModel>(
(t, t0) => new object[]
{
JoinType.Left, t.RoleId == t0.Id,
});
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where($"t.{p}"));
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
requestObject.OrderByConditions.ForEach(p => query.OrderBy($"{p.Column} {p.Condition}"));
}
//设置多表查询返回实体类
query.Select((t, t0) => new TUsersModel
{
Id = t.Id,
UserName = t.UserName,
LoginName = t.LoginName,
LoginPwd = t.LoginPwd,
Salt = t.Salt,
RoleId = t.RoleId,
TRolesRoleName = t0.RoleName,
Status = t.Status,
CreateTime = t.CreateTime,
CreateId = t.CreateId
});
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.ToListAsync();
}
queryData.ForEach(x => { x.LoginPwd = EncryptUtil.DeAESbyKey(x.LoginPwd, Encoding.UTF8, x.Salt); });
//返回执行结果
return(ResponseUtil <TUsersModel, List <TUsersModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TUsersModel, List <TUsersModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// 获取T_Bulletin数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TBulletinModel, List <TBulletinModel> > > GetAsync(RequestObject <TBulletinModel> requestObject)
{
try
{
List <TBulletinModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TBulletinModel, TUsersModel>(
(t, t0) => new object[]
{
JoinType.Left, t.Publisher == t0.Id,
});
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where($"t.{p}"));
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
requestObject.OrderByConditions.ForEach(p => query.OrderBy($"{p.Column} {p.Condition}"));
}
//设置多表查询返回实体类
query.Select((t, t0) => new TBulletinModel
{
Id = t.Id,
BulletinTitle = t.BulletinTitle,
BulletinContent = t.BulletinContent,
PublishDate = t.PublishDate,
Publisher = t.Publisher,
TUsersUserName = t0.UserName,
Receivers = t.Receivers,
RePublish = t.RePublish,
CreateTime = t.CreateTime,
CreateId = t.CreateId,
});
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.ToListAsync();
}
//返回执行结果
return(ResponseUtil <TBulletinModel, List <TBulletinModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TBulletinModel, List <TBulletinModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// 验证多次登录失败短信验证码合法性
/// </summary>
/// <param name="requestObject"></param>
/// <returns></returns>
public async Task <ResponseObject <string> > GetVerificationCodeTimePass(RequestGet requestObject)
{
try
{
String Mobile = "";
String Code = "";
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
if (item.FieldName.ToLower() == "mobile")
{
Mobile = item.FieldValue;
continue;
}
if (item.FieldName.ToLower() == "code")
{
Code = item.FieldValue;
continue;
}
}
}
var redis = CacheFactory.Instance(CacheType.Redis);
string redisKey = string.Format(CacheKeyString.TimePassMsgCode, Mobile);
string RedisCode = redis.GetValueByKey <string>(redisKey);
if (RedisCode == "")
{
return(ResponseUtil <string> .SuccessResult("0"));
}
else if (RedisCode != Code)
{
return(ResponseUtil <string> .SuccessResult("2"));
}
return(ResponseUtil <string> .SuccessResult("1"));
}
catch (Exception ex)
{
return(ResponseUtil <string>
.FailResult(null, $"验证注册短信验证码合法性异常 {ex.Message}"));
}
}
/// <summary>
/// 获取T_Templates数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TTemplatesModel, List <TTemplatesModel> > > GetAsync(RequestObject <TTemplatesModel> requestObject)
{
try
{
List <TTemplatesModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TTemplatesModel>();
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where(p));
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
requestObject.OrderByConditions.ForEach(p => query.OrderBy($"{p.Column} {p.Condition}"));
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.ToListAsync();
}
//返回执行结果
return(ResponseUtil <TTemplatesModel, List <TTemplatesModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TTemplatesModel, List <TTemplatesModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// 验证登录是否需要验证码
/// </summary>
/// <param name="requestObject"></param>
/// <returns></returns>
public async Task <ResponseObject <int> > GetVerification(RequestGet request)
{
try
{
String Mobile = "";
if (request.QueryConditions != null && request.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(request.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
if (item.FieldName.ToLower() == "mobile")
{
Mobile = item.FieldValue;
continue;
}
}
}
var verification = CacheFactory.Instance(CacheType.Redis);
if (verification.ContainsKey(string.Format(CacheKeyString.LoginTimes, Mobile)))
{
string RedisValue = verification.GetValueByKey <string>(string.Format(CacheKeyString.LoginTimes, Mobile));
if (Convert.ToInt32(RedisValue) >= 3)
{
return(ResponseUtil <int>
.FailResult(2, "登录次数超过限制，请输入验证码登录"));
}
}
return(ResponseUtil <int>
.FailResult(1, "无需验证"));
}
catch (Exception ex)
{
//返回异常信息
return(ResponseUtil <int>
.FailResult(0, ex.Message));
}
}
/// <summary>
/// 获取T_PSM_PurchaseOrderMain主表数据数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <param name="currentUser"></param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <List <TPSMPurchaseOrderMainQueryModel> > > GetMainListAsync(RequestGet requestObject, CurrentUser currentUser)
{
try
{
List <TPSMPurchaseOrderMainQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TPSMPurchaseOrderMainDbModel,
TSMUserAccountDbModel, TBMDictionaryDbModel, TBMDictionaryDbModel, TSMUserAccountDbModel,
TSMUserAccountDbModel, TMMPurchaseApplyMainDbModel>(
(t, t1, t2, t3, t4, t5, t6) => new object[]
{
JoinType.Left, t.BuyerId == t1.ID,
JoinType.Left, t.OrderTypeId == t2.ID,
JoinType.Left, t.SettlementTypeId == t3.ID,
JoinType.Left, t.AuditId == t4.ID,
JoinType.Left, t.OperatorId == t5.ID,
JoinType.Left, t.SourceId == t6.ID
});
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var lessSourceID = requestObject.QueryConditions.Where(p => p.Column.ToLower() == "sourceid" && p.Condition == ConditionEnum.LessThan).FirstOrDefault();
if (lessSourceID != null)
{
requestObject.QueryConditions.Remove(lessSourceID);
query = query.Where((t, t1, t2, t3, t4, t5, t6) => SqlFunc.IsNull(t6.SourceId, 0) == 0);
}
if (requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
if (item.FieldName.ToLower() == "suppliername")
{
item.FieldName = $"t0.{item.FieldName}";
continue;
}
item.FieldName = $"t.{item.FieldName}";
}
query.Where(conditionals);
}
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TPSMPurchaseOrderMainDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query
.Select((t, t1, t2, t3, t4, t5, t6) => new TPSMPurchaseOrderMainQueryModel
{
ID = t.ID,
BuyerId = t.BuyerId,
BuyerName = t1.AccountName,
OrderNo = t.OrderNo,
OrderDate = t.OrderDate,
OrderTypeId = t.OrderTypeId,
OrderTypeName = t2.DicValue,
SettlementTypeId = t.SettlementTypeId,
SettlementTypeName = t3.DicValue,
Currency = t.Currency,
AuditStatus = t.AuditStatus,
AuditId = t.AuditId,
AuditName = t4.AccountName,
AuditTime = t.AuditTime,
OperatorId = t.OperatorId,
OperatorName = t5.AccountName,
ContactName = t.ContactName,
ContactNumber = t.ContactNumber,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
TransferStatus = t.TransferStatus,
PurchaseNum = t.PurchaseNum,
PurchaseAmount = t.PurchaseAmount,
AllowEdit = t.AuditStatus != 2 && t.OperatorId == currentUser.UserID,
SourceId = t.SourceId,
SourceNo = t6.PurchaseNo
})
.Where((t) => t.CompanyId == currentUser.CompanyID && !t.DeleteFlag)
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query
.Select((t, t1, t2, t3, t4, t5, t6) => new TPSMPurchaseOrderMainQueryModel
{
ID = t.ID,
BuyerId = t.BuyerId,
BuyerName = t1.AccountName,
OrderNo = t.OrderNo,
OrderDate = t.OrderDate,
OrderTypeId = t.OrderTypeId,
OrderTypeName = t2.DicValue,
SettlementTypeId = t.SettlementTypeId,
SettlementTypeName = t3.DicValue,
Currency = t.Currency,
AuditStatus = t.AuditStatus,
AuditId = t.AuditId,
AuditName = t4.AccountName,
AuditTime = t.AuditTime,
OperatorId = t.OperatorId,
OperatorName = t5.AccountName,
ContactName = t.ContactName,
ContactNumber = t.ContactNumber,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
TransferStatus = t.TransferStatus,
PurchaseNum = t.PurchaseNum,
PurchaseAmount = t.PurchaseAmount,
SourceNo = t6.PurchaseNo,
SourceId = t.SourceId
})
.Where((t) => t.CompanyId == currentUser.CompanyID && !t.DeleteFlag)
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <List <TPSMPurchaseOrderMainQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TPSMPurchaseOrderMainQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取T_WM_InventoryMain主表数据数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <param name="currentUser"></param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <List <TWMInventoryMainQueryModel> > > GetMainListAsync(RequestGet requestObject, CurrentUser currentUser)
{
try
{
List <TWMInventoryMainQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TWMInventoryMainDbModel, TBMWarehouseFileDbModel, TSMUserAccountDbModel,
TSMUserAccountDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel>(
(t, t0, t1, t2, t3, t4) => new object[]
{
JoinType.Left, t.WarehouseId == t0.ID,
JoinType.Left, t.OperatorId == t1.ID,
JoinType.Left, t.WhAdminId == t2.ID,
JoinType.Left, t.AuditId == t3.ID,
JoinType.Left, t.InventoryId == t4.ID
}
).Where((t, t0, t1, t2, t3, t4) => t.CompanyId == currentUser.CompanyID && !t.DeleteFlag);
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TWMInventoryMainDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select(
(t, t0, t1, t2, t3, t4) => new TWMInventoryMainQueryModel
{
ID = t.ID,
WarehouseId = t.WarehouseId,
WarehouseName = t0.WarehouseName,
InventoryDate = t.InventoryDate,
InventoryOrder = t.InventoryOrder,
AuditStatus = t.AuditStatus,
OperatorId = t.OperatorId,
OperatorName = t1.AccountName,
WhAdminId = t.WhAdminId,
WhAdminName = t2.AccountName,
AuditId = t.AuditId,
AuditName = t3.AccountName,
AuditTime = t.AuditTime,
DeleteFlag = t.DeleteFlag,
CompanyId = t.CompanyId,
InventoryId = t.InventoryId,
InventoryName = t4.AccountName
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select(
(t, t0, t1, t2, t3, t4) => new TWMInventoryMainQueryModel
{
ID = t.ID,
WarehouseId = t.WarehouseId,
WarehouseName = t0.WarehouseName,
InventoryDate = t.InventoryDate,
InventoryOrder = t.InventoryOrder,
AuditStatus = t.AuditStatus,
OperatorId = t.OperatorId,
OperatorName = t1.AccountName,
WhAdminId = t.WhAdminId,
WhAdminName = t2.AccountName,
AuditId = t.AuditId,
AuditName = t3.AccountName,
AuditTime = t.AuditTime,
DeleteFlag = t.DeleteFlag,
CompanyId = t.CompanyId,
InventoryId = t.InventoryId,
InventoryName = t4.AccountName
})
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <List <TWMInventoryMainQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TWMInventoryMainQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取T_PM_Menus数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TPMMenusQueryModel, List <TPMMenusQueryModel> > > GetAsync(RequestObject <TPMMenusQueryModel> requestObject)
{
try
{
List <TPMMenusQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TPMMenusDbModel>();
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where(p));
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TPMMenusDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select(
(t) => new TPMMenusQueryModel
{
Id = t.Id,
ParentID = t.ParentID,
MenuName = t.MenuName,
MenuPath = t.MenuPath,
Seq = t.Seq,
IsMenu = t.IsMenu,
MenuAnotherName = t.MenuAnotherName,
Status = t.Status,
MenuIcon = t.MenuIcon,
MenuDesc = t.MenuDesc,
CreateTime = t.CreateTime,
CreateId = t.CreateId,
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select(
(t) => new TPMMenusQueryModel
{
Id = t.Id,
ParentID = t.ParentID,
MenuName = t.MenuName,
MenuPath = t.MenuPath,
Seq = t.Seq,
IsMenu = t.IsMenu,
MenuAnotherName = t.MenuAnotherName,
Status = t.Status,
MenuIcon = t.MenuIcon,
MenuDesc = t.MenuDesc,
CreateTime = t.CreateTime,
CreateId = t.CreateId,
})
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <TPMMenusQueryModel, List <TPMMenusQueryModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TPMMenusQueryModel, List <TPMMenusQueryModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// 获取获取部门下的员工
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <param name="UserID">用户ID</param>
/// <returns></returns>
public async Task <ResponseObject <TSMDeptQueryForDispatchModel, List <TSMUserAccountQueryAllModel> > > GetDispathUserAsync(RequestObject <TSMDeptQueryForDispatchModel> requestObject, int UserID)
{
try
{
List <TSMUserAccountQueryAllModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var condition = requestObject.QueryConditions.Where(p => p.Column.ToLower() == "deptid").FirstOrDefault();
if (condition == null)
{
return(ResponseUtil <TSMDeptQueryForDispatchModel, List <TSMUserAccountQueryAllModel> > .FailResult(requestObject, null, "deptid不能为空"));
}
int companyID = SMCurentUserManager.GetCurentUserID(UserID, _db.Instance).CompanyId.Value;
var mainDept = _db.Instance.Queryable <TSMDeptDbModel>().Where(p => p.Id.ToString() == condition.Content).First();
var query = _db.Instance.Queryable <TSMUserAccountDbModel, TSMUserInfoDbModel, TSMDeptUserRelationDbModel, TSMDeptDbModel>(
(t1, t2, t3, t4) => new object[]
{
JoinType.Inner, t1.UserInfoId == t2.ID,
JoinType.Left, t1.ID == t3.UserAccountId,
JoinType.Left, t3.DeptId == t4.Id
}).Where((t1, t2, t3, t4) => t1.CompanyId == companyID);
if (mainDept != null)
{
query = query.Where((t1, t2, t3, t4) => t4.PathLogic.StartsWith(mainDept.PathLogic));
}
if (condition.Content == "-100")
{
query = query.Where((t1, t2, t3, t4) => t3.Id == null);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TSMDeptDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
if (requestObject.IsPaging)
{
queryData = await query.Select((t1, t2, t3, t4) => new TSMUserAccountQueryAllModel
{
ID = t1.ID,
TelAccount = t1.TelAccount,
EmailAccount = t1.EmailAccount,
AccountName = t1.AccountName,
UserInfoId = t1.UserInfoId,
CompanyId = t1.CompanyId,
Status = t1.Status,
ExpDate = t1.ExpDate,
CreateTime = t1.CreateTime,
CId = t2.ID,
JobNumber = t2.JobNumber,
RealName = t2.RealName,
FixedNumber = t2.FixedNumber,
Address = t2.Address,
HeadPicPath = t2.HeadPicPath,
Remarks = t2.Remarks,
}).ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select((t1, t2, t3, t4) => new TSMUserAccountQueryAllModel
{
ID = t1.ID,
TelAccount = t1.TelAccount,
EmailAccount = t1.EmailAccount,
AccountName = t1.AccountName,
UserInfoId = t1.UserInfoId,
CompanyId = t1.CompanyId,
Status = t1.Status,
ExpDate = t1.ExpDate,
CreateTime = t1.CreateTime,
CId = t2.ID,
JobNumber = t2.JobNumber,
RealName = t2.RealName,
FixedNumber = t2.FixedNumber,
Address = t2.Address,
HeadPicPath = t2.HeadPicPath,
Remarks = t2.Remarks,
}).ToListAsync();
}
//返回执行结果
return(ResponseUtil <TSMDeptQueryForDispatchModel, List <TSMUserAccountQueryAllModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TSMDeptQueryForDispatchModel, List <TSMUserAccountQueryAllModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// 获取T_SM_Dept数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <param name="UserId">用户ID</param>
/// <returns></returns>
public async Task <ResponseObject <TSMDeptQueryModel, List <TSMDeptQueryTreeModel> > > GetAsync(RequestObject <TSMDeptQueryModel> requestObject, int UserId)
{
try
{
List <TSMDeptQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var user = _db.Instance.Queryable <TSMUserAccountDbModel, TSMCompanyDbModel>(
(t, t1) => new object[]
{
JoinType.Left, t.CompanyId == t1.ID,
}).Where((t, t1) => t.ID == UserId).Select((t, t1) => new { CompanyId = t.CompanyId, Name = t1.CompanyName }).First();
var query = _db.Instance.Queryable <TSMDeptDbModel>().Where(p => p.CompanyId == user.CompanyId);
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where(p));
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TSMDeptDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
queryData = await query.Select(
(t) => new TSMDeptQueryModel
{
Id = t.Id,
VitualId = t.Id,
ActualId = t.Id,
DeptName = t.DeptName,
ParentId = t.ParentId,
SeqNumber = t.SeqNumber,
PathLogic = t.PathLogic,
DeptDesc = t.DeptDesc,
CompanyId = t.CompanyId,
CreateId = t.CreateId,
CreateTime = t.CreateTime
})
.ToListAsync();
TSMDeptQueryModel tSMDeptQueryModel = new TSMDeptQueryModel();
tSMDeptQueryModel.ParentId = -2;
tSMDeptQueryModel.Id = user.CompanyId.Value;
tSMDeptQueryModel.DeptName = user.Name;
tSMDeptQueryModel.VitualId = -1;
tSMDeptQueryModel.ActualId = tSMDeptQueryModel.ActualId;
queryData.Add(tSMDeptQueryModel);
TSMDeptQueryModel tSVitual = new TSMDeptQueryModel();
tSVitual.ParentId = -1;
tSVitual.Id = -100;
tSVitual.DeptName = "未分配部门";
tSVitual.VitualId = -100;
tSVitual.ActualId = -100;
tSVitual.SeqNumber = 999999;
queryData.Add(tSVitual);
var result = GetMenuTree(queryData, -2);
//返回执行结果
return(ResponseUtil <TSMDeptQueryModel, List <TSMDeptQueryTreeModel> > .SuccessResult(requestObject, result, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TSMDeptQueryModel, List <TSMDeptQueryTreeModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
///
/// </summary>
/// <param name="requestObject"></param>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <List <InventoryResultModel> > > LoadReport(RequestGet requestObject, CurrentUser currentUser)
{
try
{
//数据字典
var tBMDictionary = _db.Instance.Queryable <TBMDictionaryDbModel>().Where(p => p.CompanyId == currentUser.CompanyID).ToList().ToDictionary(p => p.ID, p => p.DicValue);
var warehouseDic = _db.Instance.Queryable <TBMWarehouseFileDbModel>().Where(t => SqlFunc.IsNull(t.DeleteFlag, false) != true &&
t.CompanyId == currentUser.CompanyID).ToList().ToDictionary(p => p.ID, p => new { Name = p.WarehouseName, Code = p.Code });
List <InventoryResultModel> result = new List <InventoryResultModel>();
//其他出入库
var otherCount = _db.Instance.Queryable <TWMOtherCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//盘亏盘盈出入库
var pCount = _db.Instance.Queryable <TWMProfitDeficitCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//销售出入库
var SaleCount = _db.Instance.Queryable <TWMSalesCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//采购出入库
var PurchaseCount = _db.Instance.Queryable <TWMPurchaseCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//生产出入库
var ProductCount = _db.Instance.Queryable <TWMProductionCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//出入库数量
var allCount = _db.Instance.UnionAll(otherCount, pCount, SaleCount, PurchaseCount, ProductCount).GroupBy(p => new { p.MaterialId, p.WarehouseId }).
Select(p => new TradeInventoryModel()
{
TradeNumber = SqlFunc.AggregateSum(p.WhNumber) - SqlFunc.AggregateSum(p.WhSendNumber),
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId
}).AS("t100");
var materialFileQuery = _db.Instance.Queryable <TBMMaterialFileDbModel>().Where(p => p.CompanyId == currentUser.CompanyID);
var ts = _db.Instance.Queryable(materialFileQuery, allCount, JoinType.Inner, (p1, p2) => p1.ID == p2.MaterialId);
#region 待出库数量
//其他待出库
var otherToOut = _db.Instance.Queryable <TWMOtherWhSendMainDbModel, TWMOtherWhSendDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNumber
});
//盘亏出库
var deficitToOut = _db.Instance.Queryable <TWMDeficitMainDbModel, TWMDeficitDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNumber
});
//销售出库
var saleToOut = _db.Instance.Queryable <TWMSalesMainDbModel, TWMSalesDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNum
});
//生产待出库
var productToOut = _db.Instance.Queryable <TWMProductionMainDbModel, TWMProductionDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNum
});
var allToOut = _db.Instance.UnionAll(otherToOut, deficitToOut, saleToOut, productToOut).AS("t101");
var tsToOut = allToOut.ToList().GroupBy(p => p.MaterialId).Select(p => new InventoryOut()
{
MaterialId = p.Key, Amount = p.Sum(m => m.Amount)
}).ToList()
.ToDictionary(p => p.MaterialId, p => new { MaterialId = p.MaterialId, Amount = p.Amount });
#endregion
string[] cQuery = { "warehouseid" };
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var QueryConditions1 = requestObject.QueryConditions.Where(p => !cQuery.Contains(p.Column.ToLower())).ToList();
if (QueryConditions1.Count() > 0)
{
var conditionals1 = SqlSugarUtil.GetConditionalModels(QueryConditions1);
foreach (ConditionalModel item in conditionals1)
{
item.FieldName = $"p1.{item.FieldName}";
}
ts.Where(conditionals1);
}
var QueryConditions2 = requestObject.QueryConditions.Where(p => cQuery.Contains(p.Column.ToLower())).FirstOrDefault();
if (QueryConditions2 != null)
{
int WarehouseId = Convert.ToInt32(QueryConditions2.Content);
ts = ts.Where((p1, p2) => p2.WarehouseId == WarehouseId);
}
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TBMMaterialFileDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
ts.OrderBy($"p1.{item.Column} {item.Condition}");
}
}
#region 最新采购/生产时间
var Purchase = _db.Instance.Queryable <TPSMPurchaseOrderDetailDbModel, TPSMPurchaseOrderMainDbModel>(
(t1, t2) => new object[] { JoinType.Left, t1.MainId == t2.ID })
.Where((t1, t2) => t2.CompanyId == currentUser.CompanyID && SqlFunc.IsNull(t2.DeleteFlag, false) != true &&
t2.AuditStatus == 2
)
.GroupBy((t1, t2) => t1.MaterialId)
.Select((t1, t2) => new { MaterialId = t1.MaterialId, OrderDate = SqlFunc.AggregateMax(t2.OrderDate) }).ToList()
.ToDictionary(p => p.MaterialId, p => new { MaterialId = p.MaterialId, OrderDate = p.OrderDate });
var Production = _db.Instance.Queryable <TMMProductionOrderDetailDbModel, TMMProductionOrderMainDbModel>(
(t1, t2) => new object[] { JoinType.Left, t1.MainId == t2.ID })
.Where((t1, t2) => t2.CompanyId == currentUser.CompanyID && SqlFunc.IsNull(t2.DeleteFlag, false) != true &&
t2.AuditStatus == 2)
.GroupBy((t1, t2) => t1.MaterialId)
.Select((t1, t2) => new { MaterialId = t1.MaterialId, OrderDate = SqlFunc.AggregateMax(t2.OrderDate) }).ToList()
.ToDictionary(p => p.MaterialId, p => new { MaterialId = p.MaterialId, OrderDate = p.OrderDate });
#endregion
int totalNum = -1;
if (requestObject.IsPaging)
{
int skipNum = requestObject.PageSize * (requestObject.PageIndex - 1);
totalNum = ts.Count();
result = await ts.Select((p1, p2) => new InventoryResultModel
{
MaterialName = p1.MaterialName,
BaseUnitId = p1.BaseUnitId,
ColorId = p1.ColorId,
MaterialCode = p1.MaterialCode,
MaterialId = p1.ID,
Spec = p1.Spec,
WarehouseAmount = p2.TradeNumber,
WarehouseUnitId = p1.WarehouseUnitId,
WarehouseRate = p1.WarehouseRate,
WarehouseId = p2.WarehouseId,
ShelfLife = p1.ShelfLife
}).Skip(skipNum).Take(requestObject.PageSize).ToListAsync();
}
else
{
result = await ts.Select((p1, p2) => new InventoryResultModel
{
MaterialName = p1.MaterialName,
BaseUnitId = p1.BaseUnitId,
ColorId = p1.ColorId,
MaterialCode = p1.MaterialCode,
MaterialId = p1.ID,
Spec = p1.Spec,
WarehouseAmount = p2.TradeNumber,
WarehouseUnitId = p1.WarehouseUnitId,
WarehouseRate = p1.WarehouseRate,
WarehouseId = p2.WarehouseId,
ShelfLife = p1.ShelfLife
}).ToListAsync();
}
var TWMPrimeCountDbList = _db.Instance.Queryable <TWMPrimeCountDbModel>().Where(p => p.CompanyId == currentUser.CompanyID).ToList();
result.ForEach(x =>
{
#region 最新生产/采购时间
string Production_PurchaseDateTime = "";
if (Production.ContainsKey(x.MaterialId))
{
Production_PurchaseDateTime += "" + Convert.ToDateTime(Production[x.MaterialId].OrderDate).ToString("yyyy-MM-dd");
}
else
{
Production_PurchaseDateTime += "无生产";
}
if (Purchase.ContainsKey(x.MaterialId))
{
Production_PurchaseDateTime += "/" + Convert.ToDateTime(Purchase[x.MaterialId].OrderDate).ToString("yyyy-MM-dd");
}
else
{
Production_PurchaseDateTime += "/无采购";
}
if (Production_PurchaseDateTime != "")
{
x.Production_PurchaseDateTime = Production_PurchaseDateTime;
}
#endregion
if (tsToOut.ContainsKey(x.MaterialId))
{
x.WarehouseAvailabilityAmount = x.WarehouseAmount - tsToOut[x.MaterialId].Amount;
}
if (x.WarehouseUnitId.HasValue)
{
if (tBMDictionary.ContainsKey(x.WarehouseUnitId.Value))
{
x.WarehouseUnitName = tBMDictionary[x.WarehouseUnitId.Value];
}
}
if (warehouseDic.ContainsKey(x.WarehouseId))
{
x.WarehouseName = warehouseDic[x.WarehouseId].Name;
x.WarehouseCode = warehouseDic[x.WarehouseId].Code;
}
if (tBMDictionary.ContainsKey(x.BaseUnitId))
{
x.BaseUnitName = tBMDictionary[x.BaseUnitId];
}
if (string.IsNullOrEmpty(x.WarehouseUnitName))
{
x.WarehouseUnitName = x.BaseUnitName;
}
if (x.WarehouseRate == null)
{
x.WarehouseRate = 1;
}
if (x.ColorId.HasValue)
{
if (tBMDictionary.ContainsKey(x.ColorId.Value))
{
x.ColorName = tBMDictionary[x.ColorId.Value];
}
}
var firstEntity = TWMPrimeCountDbList.Where(p => p.WarehouseId == x.WarehouseId && p.MaterialId == x.MaterialId).FirstOrDefault();
if (firstEntity != null)
{
x.WarehouseAmount = x.WarehouseAmount + firstEntity.PrimeNum;
x.PrimeNum = firstEntity.PrimeNum;
}
});
return(ResponseUtil <List <InventoryResultModel> > .SuccessResult(result, totalNum));
}
catch (Exception ex)
{
return(ResponseUtil <List <InventoryResultModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取销售一览表信息
/// </summary>
/// <param name="request"></param>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <SalesListModel> > GetSalesList(RequestGet request, CurrentUser currentUser)
{
try
{
List <SalesCountListModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TSSMSalesOrderDetailDbModel, TSSMSalesOrderMainDbModel,
TBMDictionaryDbModel, TBMCustomerFileDbModel, TSMUserAccountDbModel, TBMPackageDbModel>(
(t, t0, t1, t2, t3, t4) => new object[]
{
JoinType.Left, t.MainId == t0.ID,
JoinType.Left, t0.OrderTypeId == t1.ID,
JoinType.Left, t0.CustomerId == t2.ID,
JoinType.Left, t0.SalesmanId == t3.ID,
JoinType.Left, t.PackageId == t4.ID
})
.Where((t, t0, t1, t2, t3, t4) => t0.CompanyId == currentUser.CompanyID && t4.DicValue != null);
//.OrderBy((t, t0, t1, t2, t3, t4) => t0.OrderNo);
//查询条件
if (request.QueryConditions != null && request.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(request.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
if (item.FieldName.ToLower() == "orderdate" ||
item.FieldName.ToLower() == "customerid" ||
item.FieldName.ToLower() == "ordertypeid" ||
item.FieldName.ToLower() == "salesmanid")
{
item.FieldName = $"t0.{item.FieldName}";
continue;
}
}
query.Where(conditionals);
}
//排序条件
if (request.OrderByConditions != null && request.OrderByConditions.Count > 0)
{
foreach (var item in request.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TSSMSalesOrderMainDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"t0.{item.Column} {item.Condition}");
}
}
else
{
query.OrderBy((t, t0, t1, t2, t3, t4) => t0.OrderNo);
}
SalesListModel _list = new SalesListModel();
_list.Total_Number = query.Count();
#region 统计数量
var SalesNum = query.Select((t, t0, t1, t2, t3, t4) => SqlFunc.AggregateSum(t.SalesNum)).ToList();
_list.Total_Num = SalesNum.Count > 0 ? Convert.ToDecimal(SalesNum.ToList()[0]) : 0;
#endregion
#region 统计金额
var SalesAmount = query.Select((t, t0, t1, t2, t3, t4) => SqlFunc.AggregateSum(t.SalesAmount)).ToList();
_list.Total_Amount = SalesAmount.Count > 0 ? Convert.ToDecimal(SalesAmount.ToList()[0]) : 0;
#endregion
if (request.IsPaging)
{
int skipNum = request.PageSize * (request.PageIndex - 1);
queryData = await query
.Select((t, t0, t1, t2, t3, t4) => new SalesCountListModel
{
OrderNo = t0.OrderNo,
OrderTypeId = t0.OrderTypeId,
OrderTypeName = t1.DicValue,
CustomerId = t0.CustomerId,
CustomerName = t2.CustomerName,
SalesManId = t0.SalesmanId,
SalesManName = t3.AccountName,
PackageId = t.PackageId,
PackageName = t4.DicValue,
SalesNum = t.SalesNum,
SalesAmount = t.UnitPrice,
AuditStatus = (byte)t0.AuditStatus,
OrderDate = t0.OrderDate
}).Skip(skipNum).Take(request.PageSize).ToListAsync();
}
else
{
queryData = await query
.Select((t, t0, t1, t2, t3, t4) => new SalesCountListModel
{
OrderNo = t0.OrderNo,
OrderTypeId = t0.OrderTypeId,
OrderTypeName = t1.DicValue,
CustomerId = t0.CustomerId,
CustomerName = t2.CustomerName,
SalesManId = t0.SalesmanId,
SalesManName = t3.AccountName,
PackageId = t.PackageId,
PackageName = t4.DicValue,
SalesNum = t.SalesNum,
SalesAmount = t.UnitPrice,
AuditStatus = (byte)t0.AuditStatus,
OrderDate = t0.OrderDate
})
.ToListAsync();
}
_list.List = queryData;
return(ResponseUtil <SalesListModel> .SuccessResult(_list));
}
catch (Exception ex)
{
return(ResponseUtil <SalesListModel>
.FailResult(null, $"查询销售一览表发生异常{System.Environment.NewLine}{ex.Message}"));
}
}
/// <summary>
/// 获取T_PM_SeedUsers数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <List <TPMSeedUsersQueryModel> > > GetAsync(RequestGet requestObject)
{
try
{
List <TPMSeedUsersQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TPMSeedUsersDbModel>();
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TPMSeedUsersDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select(
(t) => new TPMSeedUsersQueryModel
{
ID = t.ID,
Name = t.Name,
MobileNO = t.MobileNO,
EnterpriseName = t.EnterpriseName,
Address = t.Address,
CreateTime = t.CreateTime,
ChannelNo = t.ChannelNo,
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select(
(t) => new TPMSeedUsersQueryModel
{
ID = t.ID,
Name = t.Name,
MobileNO = t.MobileNO,
EnterpriseName = t.EnterpriseName,
Address = t.Address,
CreateTime = t.CreateTime,
ChannelNo = t.ChannelNo,
})
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <List <TPMSeedUsersQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TPMSeedUsersQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取T_SM_UserAccount数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TSMUserAccountQueryModel, List <TSMUserAccountQueryModel> > > GetAsync(RequestObject <TSMUserAccountQueryModel> requestObject)
{
try
{
List <TSMUserAccountQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TSMUserAccountDbModel>();
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where(p));
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
requestObject.OrderByConditions.ForEach(p => query.OrderBy($"{p.Column} {p.Condition}"));
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select(
(t) => new TSMUserAccountQueryModel
{
Id = t.ID,
TelAccount = t.TelAccount,
EmailAccount = t.EmailAccount,
Passwd = t.Passwd,
AccountName = t.AccountName,
UserInfoId = t.UserInfoId,
CompanyId = t.CompanyId,
Status = t.Status,
ExpDate = t.ExpDate,
CreateTime = t.CreateTime,
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select(
(t) => new TSMUserAccountQueryModel
{
Id = t.ID,
TelAccount = t.TelAccount,
EmailAccount = t.EmailAccount,
Passwd = t.Passwd,
AccountName = t.AccountName,
UserInfoId = t.UserInfoId,
CompanyId = t.CompanyId,
Status = t.Status,
ExpDate = t.ExpDate,
CreateTime = t.CreateTime,
})
.ToListAsync();
}
//查询用户详情信息
var userIds = queryData.Where(p => p.UserInfoId != null).Select(p => p.UserInfoId).ToArray();
var userInfoList = _db.Instance.Queryable <TSMUserInfoDbModel>()
.In(userIds)
.Select(p => new TSMUserInfoQueryModel
{
Id = p.ID,
Address = p.Address,
FixedNumber = p.FixedNumber,
HeadPicPath = p.HeadPicPath,
JobNumber = p.JobNumber,
RealName = p.RealName,
Remarks = p.Remarks
})
.ToList();
queryData.ForEach(p => p.UserInfoDetail = userInfoList.Where(p1 => p1.Id == p.UserInfoId).FirstOrDefault());
//返回执行结果
return(ResponseUtil <TSMUserAccountQueryModel, List <TSMUserAccountQueryModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TSMUserAccountQueryModel, List <TSMUserAccountQueryModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// 获取T_MM_BOMNTempMain主表数据数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <List <TMMBOMNTempMainQueryModel> > > GetMainListAsync(RequestGet requestObject, CurrentUser currentUser)
{
try
{
List <TMMBOMNTempMainQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TMMBOMNTempMainDbModel>();
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TMMBOMNTempMainDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select((t) => new TMMBOMNTempMainQueryModel
{
ID = t.ID,
PackageId = t.PackageId,
PagerCode = t.PagerCode,
Maker = t.Maker,
FrontImgPath = t.FrontImgPath,
SideImgPath = t.SideImgPath,
BackImgPath = t.BackImgPath,
TempName = t.TempName,
CompanyId = t.CompanyId,
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select((t) => new TMMBOMNTempMainQueryModel
{
ID = t.ID,
PackageId = t.PackageId,
PagerCode = t.PagerCode,
Maker = t.Maker,
FrontImgPath = t.FrontImgPath,
SideImgPath = t.SideImgPath,
BackImgPath = t.BackImgPath,
TempName = t.TempName,
CompanyId = t.CompanyId,
})
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <List <TMMBOMNTempMainQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TMMBOMNTempMainQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取T_SM_CompanyApply数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TSMCompanyApplyQueryModel, List <TSMCompanyApplyQueryModel> > > GetAsync(RequestObject <TSMCompanyApplyQueryModel> requestObject, CurrentUser currentUser)
{
try
{
List <TSMCompanyApplyQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TSMCompanyApplyDbModel, TSMUserAccountDbModel, TSMUserInfoDbModel>((t, t1, t2) => new object[] {
JoinType.Inner, t.AccountId == t1.ID,
JoinType.Inner, t1.UserInfoId == t2.ID
}).Where((t, t1, t2) => t.CompanyId == currentUser.CompanyID);
var company = _db.Instance.Queryable <TSMCompanyDbModel>().Where(p => p.ID == currentUser.CompanyID).First();
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
List <IConditionalModel> conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
if (item.FieldName.ToLower() == "telaccount")
{
item.FieldName = $"t1.{item.FieldName}";
}
else
{
if (item.ConditionalType == ConditionalType.LessThanOrEqual)
{
item.FieldValue = Convert.ToDateTime(item.FieldValue).AddDays(1).ToString("yyyyMMdd");
}
item.FieldName = $"t.{item.FieldName}";
}
}
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
requestObject.OrderByConditions.ForEach(p => query.OrderBy($"{p.Column} {p.Condition}"));
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select(
(t, t1, t2) => new TSMCompanyApplyQueryModel
{
Id = t.Id,
AccountId = t.AccountId,
CompanyId = t.CompanyId,
ApplyTime = t.ApplyTime,
ApplyStatus = t.ApplyStatus,
AccountName = t1.AccountName,
TelAccount = t1.TelAccount,
EmailAccount = t1.EmailAccount,
CompanyName = company.CompanyName
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select(
(t, t1, t2) => new TSMCompanyApplyQueryModel
{
Id = t.Id,
AccountId = t.AccountId,
CompanyId = t.CompanyId,
ApplyTime = t.ApplyTime,
ApplyStatus = t.ApplyStatus,
AccountName = t1.AccountName,
TelAccount = t1.TelAccount,
EmailAccount = t1.EmailAccount,
CompanyName = company.CompanyName
})
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <TSMCompanyApplyQueryModel, List <TSMCompanyApplyQueryModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TSMCompanyApplyQueryModel, List <TSMCompanyApplyQueryModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// 获取T_WM_SalesMain主表数据数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <List <TWMSalesMainQueryModel> > > GetMainListAsync(RequestGet requestObject, CurrentUser currentUser)
{
try
{
List <TWMSalesMainQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TWMSalesMainDbModel, TSSMSalesOrderMainDbModel, TBMCustomerFileDbModel,
TSMUserAccountDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel>((t, t1, t2, t3, t4, t5, t6) => new object[] {
JoinType.Inner, t.SourceId == t1.ID,
JoinType.Inner, t1.CustomerId == t2.ID,
JoinType.Left, t.OperatorId == t3.ID,
JoinType.Left, t.SendId == t4.ID,
JoinType.Left, t.WhAdminId == t5.ID,
JoinType.Left, t.AuditId == t6.ID
}).Where((t, t1, t2, t3, t4, t5, t6) => t.DeleteFlag == false && t.CompanyId == currentUser.CompanyID);
List <string> cQuery = new List <string>()
{
"customerid"
};
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var mconditionals = requestObject.QueryConditions.Where(p => !cQuery.Contains(p.Column.ToLower())).ToList();
if (mconditionals.Count() > 0)
{
List <IConditionalModel> conditionals = SqlSugarUtil.GetConditionalModels(mconditionals);
foreach (ConditionalModel item in conditionals)
{
item.FieldName = $"t.{item.FieldName}";
}
query.Where(conditionals);
}
var mconditionals2 = requestObject.QueryConditions.Where(p => cQuery.Contains(p.Column.ToLower())).ToList();
if (mconditionals2.Count() > 0)
{
List <IConditionalModel> conditionals = SqlSugarUtil.GetConditionalModels(mconditionals2);
foreach (ConditionalModel item in conditionals)
{
item.FieldName = $"t1.{item.FieldName}";
}
query.Where(conditionals);
}
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TWMSalesMainDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select((t, t1, t2, t3, t4, t5, t6) => new TWMSalesMainQueryModel
{
ID = t.ID,
WhSendType = t.WhSendType,
WhSendDate = t.WhSendDate,
WhSendOrder = t.WhSendOrder,
AuditStatus = t.AuditStatus,
CustomerName = t2.CustomerName,
CustomerId = t1.CustomerId,
OperatorId = t.OperatorId,
OperatorName = t3.AccountName,
SendId = t.SendId,
SendName = t4.AccountName,
WhAdminId = t.WhAdminId,
WhAdminName = t5.AccountName,
AuditId = t.AuditId,
AuditName = t6.AccountName,
AuditTime = t.AuditTime,
DeleteFlag = t.DeleteFlag,
SourceId = t.SourceId,
Number = t.Number,
Amount = t.Amount,
ReceiptAddress = t.ReceiptAddress,
SaleOrder = t1.OrderNo,
IsShowEdit = (t.AuditStatus != 2 && t.OperatorId == currentUser.UserID) ? true : false
}).ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select((t, t1, t2, t3, t4, t5, t6) => new TWMSalesMainQueryModel
{
ID = t.ID,
WhSendType = t.WhSendType,
WhSendDate = t.WhSendDate,
WhSendOrder = t.WhSendOrder,
AuditStatus = t.AuditStatus,
CustomerName = t2.CustomerName,
CustomerId = t1.CustomerId,
OperatorId = t.OperatorId,
OperatorName = t3.AccountName,
SendId = t.SendId,
SendName = t4.AccountName,
WhAdminId = t.WhAdminId,
WhAdminName = t5.AccountName,
AuditId = t.AuditId,
AuditName = t6.AccountName,
AuditTime = t.AuditTime,
DeleteFlag = t.DeleteFlag,
SourceId = t.SourceId,
Number = t.Number,
Amount = t.Amount,
ReceiptAddress = t.ReceiptAddress,
SaleOrder = t1.OrderNo,
IsShowEdit = (t.AuditStatus != 2 && t.OperatorId == currentUser.UserID) ? true : false
}).ToListAsync();
}
//返回执行结果
return(ResponseUtil <List <TWMSalesMainQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TWMSalesMainQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取T_BM_WarehouseFile数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TBMWarehouseFileQueryModel, List <TBMWarehouseFileQueryModel> > > GetAsync(RequestObject <TBMWarehouseFileQueryModel> requestObject, int UserID)
{
try
{
var curentDB = _db.Instance;
var smUserInfo = SMCurentUserManager.GetCurentUserID(UserID, curentDB);
List <TBMWarehouseFileQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = curentDB.Queryable <TBMWarehouseFileDbModel, TSMUserAccountDbModel, TSMUserInfoDbModel>(
(t, t0, t1) => new object[]
{
JoinType.Left, t.PrincipalId == t0.ID,
JoinType.Left, t0.UserInfoId == t1.ID
}).Where((t, t0, t1) => SqlFunc.IsNull(t.DeleteFlag, false) != true && t.CompanyId == smUserInfo.CompanyId);
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
item.FieldName = $"t.{item.FieldName}";
}
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TBMWarehouseFileDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select((t, t0, t1) => new TBMWarehouseFileQueryModel
{
ID = t.ID,
Code = t.Code,
WarehouseName = t.WarehouseName,
WarehouseAddress = t.WarehouseAddress,
PrincipalId = t.PrincipalId,
RealName = t.PrincipalId == null ? "" : t0.AccountName,
Status = t.Status,
Remark = t.Remark,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select((t, t0, t1) => new TBMWarehouseFileQueryModel
{
ID = t.ID,
Code = t.Code,
WarehouseName = t.WarehouseName,
WarehouseAddress = t.WarehouseAddress,
PrincipalId = t.PrincipalId,
RealName = t.PrincipalId == null ? "" : t0.AccountName,
Status = t.Status,
Remark = t.Remark,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
})
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <TBMWarehouseFileQueryModel, List <TBMWarehouseFileQueryModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TBMWarehouseFileQueryModel, List <TBMWarehouseFileQueryModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// 获取T_MM_ColorItem数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <param name="currentUser">当前操作用户</param>
/// <returns></returns>
public async Task <ResponseObject <List <TMMColorItemQueryModel> > > GetAsync(RequestGet requestObject, CurrentUser currentUser)
{
try
{
List <TMMColorItemQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TMMColorItemDbModel, TBMPackageDbModel, TBMDictionaryDbModel>(
(t, t0, t1) => new object[]
{
JoinType.Inner, t.PackageId == t0.ID,
JoinType.Left, t.ItemId == t1.ID
}
).Where((t, t0, t1) => t0.CompanyId == currentUser.CompanyID);
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TMMColorItemDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select(
(t, t0, t1) => new TMMColorItemQueryModel
{
ID = t.ID,
PackageId = t.PackageId,
PackageCode = t0.DicCode,
PackageName = t0.DicValue,
ItemId = t.ItemId,
ItemName = t1.DicValue
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select(
(t, t0, t1) => new TMMColorItemQueryModel
{
ID = t.ID,
PackageId = t.PackageId,
PackageCode = t0.DicCode,
PackageName = t0.DicValue,
ItemId = t.ItemId,
ItemName = t1.DicValue
})
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <List <TMMColorItemQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TMMColorItemQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 历史记录
/// </summary>
/// <param name="requestObject"></param>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <List <HistoryInventory> > > History(HistoryInventoryQuery requestObject, CurrentUser currentUser)
{
List <HistoryInventory> result = new List <HistoryInventory>();
//其他出库
var otherOut = _db.Instance.Queryable <TWMOtherWhDetailDbModel, TWMOtherWhMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WarehousingOrder,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 1,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WarehousingDate,
TypeName = "其他入库",
InventoryType = t2.WarehousingType,
WarehouseAmount = t1.ActualNumber,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//
var otherIn = _db.Instance.Queryable <TWMOtherWhSendDetailDbModel, TWMOtherWhSendMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WhSendOrder,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 2,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WhSendDate,
TypeName = "其他出库",
InventoryType = t2.WhSendType,
WarehouseAmount = t1.ActualNumber,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//盘盈入库
var profitDetail = _db.Instance.Queryable <TWMProfitDetailDbModel, TWMProfitMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WarehousingOrder,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 1,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WarehousingDate,
TypeName = "盘盈入库",
InventoryType = t2.WarehousingType,
WarehouseAmount = t1.ActualNumber,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//盘亏出库
var deficit = _db.Instance.Queryable <TWMDeficitDetailDbModel, TWMDeficitMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WhSendOrder,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 2,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WhSendDate,
TypeName = "盘亏出库",
InventoryType = t2.WhSendType,
WarehouseAmount = t1.ActualNumber,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//生产入库
var productionIn = _db.Instance.Queryable <TWMProductionWhDetailDbModel, TWMProductionWhMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WarehousingOrderNo,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 1,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WarehousingDate,
TypeName = "生产入库",
InventoryType = t2.WarehousingType,
WarehouseAmount = t1.ActualNum,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//生产出库
var productionOut = _db.Instance.Queryable <TWMProductionDetailDbModel, TWMProductionMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WhSendOrder,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 2,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WhSendDate,
TypeName = "生产出库",
InventoryType = t2.WhSendType,
WarehouseAmount = t1.ActualNum,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//销售出库
var saleDetail = _db.Instance.Queryable <TWMSalesDetailDbModel, TWMSalesMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WhSendOrder,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 1,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WhSendDate,
TypeName = "销售出库",
InventoryType = t2.WhSendType,
WarehouseAmount = t1.ActualNum,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//采购入库
var purchaseIn = _db.Instance.Queryable <TWMPurchaseDetailDbModel, TWMPurchaseMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WarehousingOrderNo,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 1,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WarehousingDate,
TypeName = "采购入库",
InventoryType = t2.WarehousingType,
WarehouseAmount = t1.ActualNum,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//单位
var unitList = _db.Instance.Queryable <TBMDictionaryDbModel, TBMDictionaryTypeDbModel>(
(t, t0) => new object[]
{
JoinType.Left, t.TypeId == t0.ID,
}).Where((t, t0) => SqlFunc.IsNull(t.DeleteFlag, false) == false && t.CompanyId == currentUser.CompanyID &&
SqlFunc.IsNull(t0.DeleteFlag, false) == false
).ToList();
//出入库数量
var allCountOrgin = _db.Instance.UnionAll(otherOut, otherIn, profitDetail,
deficit, productionIn, productionOut, saleDetail, purchaseIn)
.Where(p => p.MaterialId == requestObject.MaterialId && p.WarehouseId == requestObject.WarehouseId);
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <HistoryInventory>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
allCountOrgin.OrderBy($"{item.Column} {item.Condition}");
}
}
var allCount = allCountOrgin.AS("t101");
int totalNum = -1;
if (requestObject.IsPaging)
{
int skipNum = requestObject.PageSize * (requestObject.PageIndex - 1);
result = await allCount.Skip(skipNum).Take(requestObject.PageSize).ToListAsync();
totalNum = allCount.Count();
}
else
{
result = await allCount.ToListAsync();
}
result.ForEach((x) =>
{
var unit = unitList.Where(p => p.ID == x.WarehouseUnitId).FirstOrDefault();
if (unit != null)
{
x.WarehouseUnitName = unit.DicValue;
}
var basicunit = unitList.Where(p => p.ID == x.BaseUnitId).FirstOrDefault();
if (basicunit != null)
{
x.BaseUnitName = basicunit.DicValue;
}
});
return(ResponseUtil <List <HistoryInventory> > .SuccessResult(result, totalNum));
}
public ResponseObject <List <ToDoMgModel> > GetToDoModel(RequestGet requestObject, CurrentUser currentUser)
{
ResponseObject <List <ToDoMgModel> > responseObject = new ResponseObject <List <ToDoMgModel> >();
responseObject.Code = 0;
try
{
List <ToDoMgModel> result = new List <ToDoMgModel>();
Expression <Func <ToDoMgModel, bool> > queryConditionLam = (x) => x.To == currentUser.UserID && x.CompanyID == currentUser.CompanyID;
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalFull(requestObject.QueryConditions).Where(p => !string.IsNullOrWhiteSpace(p.FieldValue)).ToList();
var whereConditional = ConditionalModelToExpression.BuildExpression <ToDoMgModel>(conditionals);
queryConditionLam = queryConditionLam.And(whereConditional);
}
SortDefinition <ToDoMgModel> sort = null;
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetExpression <ToDoMgModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() == "asc")
{
if (sort == null)
{
sort = Builders <ToDoMgModel> .Sort.Ascending(exp);
}
else
{
sort = sort.Ascending(exp);
}
}
else if (item.Condition.ToLower() == "desc")
{
if (sort == null)
{
sort = Builders <ToDoMgModel> .Sort.Descending(exp);
}
else
{
sort = sort.Descending(exp);
}
}
}
}
long totalNum = 0;
if (requestObject.IsPaging == true)
{
result = MongoDbUtil.GetDoc <ToDoMgModel>(queryConditionLam, requestObject.PageIndex, requestObject.PageSize, sort, ref totalNum);
}
else
{
result = MongoDbUtil.GetDoc <ToDoMgModel>(queryConditionLam, sort).ToList();
}
return(ResponseUtil <List <ToDoMgModel> > .SuccessResult(result, totalNum));
}
catch (Exception ex)
{
return(ResponseUtil <List <ToDoMgModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取T_SSM_SalesOrderMain主表数据数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <param name="currentUser"></param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <List <TSSMSalesOrderMainQueryModel> > > GetMainListAsync(RequestGet requestObject, CurrentUser currentUser)
{
try
{
List <TSSMSalesOrderMainQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TSSMSalesOrderMainDbModel, TBMCustomerFileDbModel, TSMUserAccountDbModel,
TBMDictionaryDbModel, TBMDictionaryDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel>
(
(t, t0, t1, t2, t3, t4, t5) => new object[]
{
JoinType.Left, t.CustomerId == t0.ID,
JoinType.Left, t.SalesmanId == t1.ID,
JoinType.Left, t.OrderTypeId == t2.ID,
JoinType.Left, t.SettlementTypeId == t3.ID,
JoinType.Left, t.AuditId == t4.ID,
JoinType.Left, t.OperatorId == t5.ID
}
).Where((t, t0, t1, t2, t3, t4, t5) => t.DeleteFlag == false && SqlFunc.IsNull(t.IsMaterial, false) == false);
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
//只能查询主表数据，且主表别名必须是t
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
if (item.FieldName.ToLower() == "customername")
{
item.FieldName = $"t0.{item.FieldName}";
continue;
}
item.FieldName = $"t.{item.FieldName}";
}
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TSSMSalesOrderMainDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" && item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query
.Select((t, t0, t1, t2, t3, t4, t5) => new TSSMSalesOrderMainQueryModel
{
ID = t.ID,
CustomerId = t.CustomerId,
CustomerName = t0.CustomerName,
SalesmanId = t.SalesmanId,
SalesmanName = t1.AccountName,
OrderNo = t.OrderNo,
OrderTypeId = t.OrderTypeId,
OrderTypeName = t2.DicValue,
SettlementTypeId = t.SettlementTypeId,
SettementTypeName = t3.DicValue,
Currency = t.Currency,
ReceiptAddress = t.ReceiptAddress,
OrderDate = t.OrderDate,
AuditStatus = t.AuditStatus,
AuditId = t.AuditId,
AuditName = t4.AccountName,
AuditTime = t.AuditTime,
OperatorId = t.OperatorId,
OperatorName = t5.AccountName,
ContactName = t.ContactName,
ContactNumber = t.ContactNumber,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
TransferStatus = t.TransferStatus,
SalesAmount = t.SalesAmount,
SalesNum = t.SalesNum,
TransProdStatus = t.TransProdStatus
})
.Where(t => t.CompanyId == currentUser.CompanyID && !t.DeleteFlag)
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query
.Select((t, t0, t1, t2, t3, t4, t5) => new TSSMSalesOrderMainQueryModel
{
ID = t.ID,
CustomerId = t.CustomerId,
CustomerName = t0.CustomerName,
SalesmanId = t.SalesmanId,
SalesmanName = t1.AccountName,
OrderNo = t.OrderNo,
OrderTypeId = t.OrderTypeId,
OrderTypeName = t2.DicValue,
SettlementTypeId = t.SettlementTypeId,
SettementTypeName = t3.DicValue,
Currency = t.Currency,
ReceiptAddress = t.ReceiptAddress,
OrderDate = t.OrderDate,
AuditStatus = t.AuditStatus,
AuditId = t.AuditId,
AuditName = t4.AccountName,
AuditTime = t.AuditTime,
OperatorId = t.OperatorId,
OperatorName = t5.AccountName,
ContactName = t.ContactName,
ContactNumber = t.ContactNumber,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
TransferStatus = t.TransferStatus,
SalesAmount = t.SalesAmount,
SalesNum = t.SalesNum,
TransProdStatus = t.TransProdStatus
})
.Where(t => t.CompanyId == currentUser.CompanyID && !t.DeleteFlag)
.ToListAsync();
}
//是否可编辑
queryData.ForEach(p => p.AllowEdit = p.AuditStatus != 2 && p.OperatorId == currentUser.UserID);
//返回执行结果
return(ResponseUtil <List <TSSMSalesOrderMainQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TSSMSalesOrderMainQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取T_BM_CustomerFile主表数据数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <param name="currentUser"></param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <List <TBMCustomerFileQueryModel> > > GetMainListAsync(RequestGet requestObject, CurrentUser currentUser)
{
try
{
List <TBMCustomerFileQueryModel> queryData = new List <TBMCustomerFileQueryModel>(); //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TBMCustomerFileDbModel, TBMCustomerContactDbModel>((t, t1) => new object[] {
JoinType.Left, t1.CustomerId == t.ID
}).Where((t, t1) => t.CompanyId == currentUser.CompanyID && t.DeleteFlag == false);
//数据字典
List <TBMDictionaryCacheModel> dics = BasicCacheGet.GetDic(currentUser);
List <ChinaAreaRecord> Areas = BasicCacheGet.GetChinaArea(currentUser);
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var customerConditions1 = requestObject.QueryConditions.Where(p => p.Column.ToLower() == "contactname").ToList();
if (customerConditions1 != null && customerConditions1.Count() > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(customerConditions1);
foreach (ConditionalModel item in conditionals)
{
item.FieldName = $"t1.{item.FieldName}";
}
query.Where(conditionals);
}
var customerConditions2 = requestObject.QueryConditions.Where(p => p.Column.ToLower() != "contactname").ToList();
if (customerConditions2 != null && customerConditions2.Count() > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(customerConditions2);
foreach (ConditionalModel item in conditionals)
{
item.FieldName = $"t.{item.FieldName}";
}
query.Where(conditionals);
}
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TBMCustomerFileDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
var query1 = query.Select((t, t1) => t).Distinct();
List <TBMCustomerFileDbModel> queryDataTemp = new List <TBMCustomerFileDbModel>();
//执行查询
if (requestObject.IsPaging)
{
queryDataTemp = await query1
.Where(t => !t.DeleteFlag)
.Select((t) => t)
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryDataTemp = await query1
.Where(t => !t.DeleteFlag)
.Select(t => t)
.ToListAsync();
}
queryDataTemp.ForEach((x) =>
{
var itemEnity = ExpressionGenericMapper <TBMCustomerFileDbModel, TBMCustomerFileQueryModel> .Trans(x);
itemEnity.ChildList = new List <TBMCustomerContactQueryModel>();
foreach (var citem in x.Child.OrderBy(p => p.Priority))
{
var citemEnity = ExpressionGenericMapper <TBMCustomerContactDbModel, TBMCustomerContactQueryModel> .Trans(citem);
itemEnity.ChildList.Add(citemEnity);
}
var item = dics.Where(p => p.ID == x.CustomerTypeId).FirstOrDefault();
if (item != null)
{
itemEnity.CustomerTypeName = item.DicValue;
}
if (x.IndustryId != null)
{
var industry = dics.Where(p => p.ID == x.IndustryId).FirstOrDefault();
if (x.IndustryId != null)
{
itemEnity.IndustryName = industry?.DicValue;
}
}
if (!string.IsNullOrEmpty(x.City))
{
var cityEntity = Areas.Where(p => p.Code == x.City).FirstOrDefault();
itemEnity.CityName = cityEntity?.FullName;
}
queryData.Add(itemEnity);
});
//返回执行结果
return(ResponseUtil <List <TBMCustomerFileQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TBMCustomerFileQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取某角色下的所有员工
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TSMRoleUserRelationEditModel, List <TSMUserRoleModel> > > GetDispathUserAsync(RequestObject <TSMRoleUserRelationEditModel> requestObject)
{
try
{
List <TSMUserRoleModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var condition = requestObject.QueryConditions.Where(p => p.Column.ToLower() == "roleid").FirstOrDefault();
int roleID = 0;
if (condition == null)
{
return(ResponseUtil <TSMRoleUserRelationEditModel, List <TSMUserRoleModel> > .FailResult(requestObject, null, "deptid不能为空"));
}
if (!int.TryParse(condition.Content, out roleID))
{
return(ResponseUtil <TSMRoleUserRelationEditModel, List <TSMUserRoleModel> > .FailResult(requestObject, null, "roleID必须为正整数"));
}
var query = _db.Instance.Queryable <TSMRolesDbModel, TSMRoleUserRelationDbModel, TSMUserAccountDbModel, TSMUserInfoDbModel>(
(t, t1, t2, t3) => new object[]
{
JoinType.Inner, t.Id == t1.RoleId,
JoinType.Inner, t1.UserId == t2.ID,
JoinType.Left, t2.UserInfoId == t3.ID
}).Where((t, t1, t2, t3) => t.Id == roleID)
.Select((t, t1, t2, t3) => new {
t1 = t1, t2 = t2, t3 = t3
}
);
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TSMDeptDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
if (requestObject.IsPaging)
{
var queryData1 = await query.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
queryData = queryData1.Select(p => new TSMUserRoleModel()
{
RoleId = p.t1.RoleId,
ID = p.t1.Id,
UserId = p.t1.UserId,
UserAccount = new Models.TSMUserAccount.TSMUserAccountQueryAllModel()
{
ID = p.t2.ID,
TelAccount = p.t2.TelAccount,
EmailAccount = p.t2.EmailAccount,
AccountName = p.t2.AccountName,
UserInfoId = p.t2.UserInfoId,
CompanyId = p.t2.CompanyId,
Status = p.t2.Status,
ExpDate = p.t2.ExpDate,
CreateTime = p.t2.CreateTime,
CId = p.t3.ID,
JobNumber = p.t3.JobNumber,
RealName = p.t3.RealName,
FixedNumber = p.t3.FixedNumber,
Address = p.t3.Address,
HeadPicPath = p.t3.HeadPicPath,
Remarks = p.t3.Remarks
}
}).ToList();
}
else
{
var queryData1 = await query.ToListAsync();
queryData = queryData1.Select(p => new TSMUserRoleModel()
{
RoleId = p.t1.RoleId,
ID = p.t1.Id,
UserId = p.t1.UserId,
UserAccount = new Models.TSMUserAccount.TSMUserAccountQueryAllModel()
{
ID = p.t2.ID,
TelAccount = p.t2.TelAccount,
EmailAccount = p.t2.EmailAccount,
AccountName = p.t2.AccountName,
UserInfoId = p.t2.UserInfoId,
CompanyId = p.t2.CompanyId,
Status = p.t2.Status,
ExpDate = p.t2.ExpDate,
CreateTime = p.t2.CreateTime,
CId = p.t3.ID,
JobNumber = p.t3.JobNumber,
RealName = p.t3.RealName,
FixedNumber = p.t3.FixedNumber,
Address = p.t3.Address,
HeadPicPath = p.t3.HeadPicPath,
Remarks = p.t3.Remarks
}
}).ToList();
}
//返回执行结果
return(ResponseUtil <TSMRoleUserRelationEditModel, List <TSMUserRoleModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TSMRoleUserRelationEditModel, List <TSMUserRoleModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// 获取T_SM_UserInfo数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TSMUserInfoQueryModel, List <TSMUserInfoQueryModel> > > GetAsync(RequestObject <TSMUserInfoQueryModel> requestObject)
{
try
{
List <TSMUserInfoQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TSMUserInfoDbModel>();
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where(p));
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TSMUserInfoDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select(
(t) => new TSMUserInfoQueryModel
{
Id = t.ID,
JobNumber = t.JobNumber,
RealName = t.RealName,
FixedNumber = t.FixedNumber,
Address = t.Address,
HeadPicPath = t.HeadPicPath,
Remarks = t.Remarks,
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select(
(t) => new TSMUserInfoQueryModel
{
Id = t.ID,
JobNumber = t.JobNumber,
RealName = t.RealName,
FixedNumber = t.FixedNumber,
Address = t.Address,
HeadPicPath = t.HeadPicPath,
Remarks = t.Remarks,
})
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <TSMUserInfoQueryModel, List <TSMUserInfoQueryModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TSMUserInfoQueryModel, List <TSMUserInfoQueryModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// 采购一览表
/// </summary>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <PurchaseListModel> > GetPurchaseCountList(RequestGet request, CurrentUser currentUser)
{
try
{
List <PurchaseCountListModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TPSMPurchaseOrderDetailDbModel, TPSMPurchaseOrderMainDbModel
, TBMDictionaryDbModel, TBMSupplierFileDbModel, TSMUserAccountDbModel, TBMMaterialFileDbModel>(
(t1, t2, t3, t4, t5, t6) => new object[]
{
JoinType.Left, t1.MainId == t2.ID,
JoinType.Left, t2.OrderTypeId == t1.ID,
JoinType.Left, t1.SupplierId == t4.ID,
JoinType.Left, t2.BuyerId == t5.ID,
JoinType.Left, t1.MaterialId == t6.ID
}
).Where((t1, t2, t3, t4, t5, t6) => t2.CompanyId == currentUser.CompanyID)
.OrderBy((t1, t2, t3, t4, t5, t6) => t2.OrderNo);
//查询条件
if (request.QueryConditions != null && request.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(request.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
if (item.FieldName.ToLower() == "orderdate" ||
item.FieldName.ToLower() == "supplierid" ||
item.FieldName.ToLower() == "ordertypeid" ||
item.FieldName.ToLower() == "orderno")
{
item.FieldName = $"t2.{item.FieldName}";
continue;
}
}
query.Where(conditionals);
}
//排序条件
if (request.OrderByConditions != null && request.OrderByConditions.Count > 0)
{
foreach (var item in request.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TPSMPurchaseOrderMainDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"t2.{item.Column} {item.Condition}");
}
}
PurchaseListModel _list = new PurchaseListModel();
_list.Total_Number = query.Count();
#region 统计数量
var PurchaseNum = query.Select((t1, t2, t3, t4, t5, t6) => SqlFunc.AggregateSum(t1.PurchaseNum)).ToList();
_list.Total_Num = PurchaseNum.Count > 0 ? Convert.ToDecimal(PurchaseNum.ToList()[0]) : 0;
#endregion
#region 统计金额
var PurchaseAmount = query.Select((t1, t2, t3, t4, t5, t6) => SqlFunc.AggregateSum(t1.PurchaseAmount)).ToList();
_list.Total_Amount = PurchaseAmount.Count > 0 ? Convert.ToDecimal(PurchaseAmount.ToList()[0]) : 0;
#endregion
if (request.IsPaging)
{
int skipNum = request.PageSize * (request.PageIndex - 1);
queryData = await query
.Select((t1, t2, t3, t4, t5, t6) => new PurchaseCountListModel
{
OrderNo = t2.OrderNo,
OrderTypeId = t2.OrderTypeId,
OrderTypeName = t3.DicValue,
MaterialCode = t6.MaterialCode,
MaterialName = t6.MaterialName,
SupplierId = Convert.ToInt32(t1.SupplierId),
SupplierName = t4.SupplierName,
BuyerId = t2.BuyerId,
BuyerName = t5.AccountName,
PurchaseNum = t1.PurchaseNum,
UnitPrice = t1.UnitPrice,
PurchaseAmount = t1.PurchaseAmount,
AuditStatus = (byte)t2.AuditStatus,
OrderDate = t2.OrderDate
}).Skip(skipNum).Take(request.PageSize).ToListAsync();
}
else
{
queryData = await query
.Select((t1, t2, t3, t4, t5, t6) => new PurchaseCountListModel
{
OrderNo = t2.OrderNo,
OrderTypeId = t2.OrderTypeId,
OrderTypeName = t3.DicValue,
MaterialCode = t6.MaterialCode,
MaterialName = t6.MaterialName,
SupplierId = Convert.ToInt32(t1.SupplierId),
SupplierName = t4.SupplierName,
BuyerId = t2.BuyerId,
BuyerName = t5.AccountName,
PurchaseNum = t1.PurchaseNum,
UnitPrice = t1.UnitPrice,
PurchaseAmount = t1.PurchaseAmount,
AuditStatus = (byte)t2.AuditStatus,
OrderDate = t2.OrderDate
})
.ToListAsync();
}
_list.List = queryData;
return(ResponseUtil <PurchaseListModel> .SuccessResult(_list));
}
catch (Exception ex)
{
return(ResponseUtil <PurchaseListModel>
.FailResult(null, $"统计采购列表发生异常{System.Environment.NewLine} {ex.Message}"));
}
}
/// <summary>
/// 获取T_WM_ProductionWhMain主表数据数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <List <TWMProductionWhMainQueryModel> > > GetMainListAsync(RequestGet requestObject, CurrentUser currentUser)
{
try
{
List <TWMProductionWhMainQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TWMProductionWhMainDbModel, TMMWhApplyMainDbModel,
TSMUserAccountDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel, TMMProductionOrderMainDbModel>((t, t1, t2, t3, t4, t5, t6) => new object[] {
JoinType.Inner, t.SourceId == t1.ID,
JoinType.Left, t.OperatorId == t2.ID,
JoinType.Left, t.WhAdminId == t3.ID,
JoinType.Left, t.AuditId == t4.ID,
JoinType.Left, t.ReceiptId == t5.ID,
JoinType.Left, t1.SourceId == t6.ID
}).Where((t, t1, t2, t3, t4, t5, t6) => t.DeleteFlag == false && t.CompanyId == currentUser.CompanyID);
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
item.FieldName = $"t.{item.FieldName}";
}
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TWMProductionWhMainDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select((t, t1, t2, t3, t4, t5, t6) => new TWMProductionWhMainQueryModel
{
ID = t.ID,
WarehousingType = t.WarehousingType,
WarehousingDate = t.WarehousingDate,
WarehousingOrderNo = t.WarehousingOrderNo,
AuditStatus = t.AuditStatus,
OperatorId = t.OperatorId,
OperatorName = t2.AccountName,
ReceiptId = t.ReceiptId,
ReceiptName = t5.AccountName,
WhAdminId = t.WhAdminId,
WhAdminName = t3.AccountName,
AuditId = t.AuditId,
AuditName = t4.AccountName,
AuditTime = t.AuditTime,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
SourceId = t.SourceId,
SourceCode = t1.WhApplyNo,
Number = t.Number,
Amount = t.Amount,
IsShowEdit = (t.AuditStatus != 2 && t.OperatorId == currentUser.UserID) ? true : false,
ProductionNo = t6.ProductionNo,
ProductionID = t6.ID
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select((t, t1, t2, t3, t4, t5, t6) => new TWMProductionWhMainQueryModel
{
ID = t.ID,
WarehousingType = t.WarehousingType,
WarehousingDate = t.WarehousingDate,
WarehousingOrderNo = t.WarehousingOrderNo,
AuditStatus = t.AuditStatus,
OperatorId = t.OperatorId,
OperatorName = t2.AccountName,
ReceiptId = t.ReceiptId,
ReceiptName = t5.AccountName,
WhAdminId = t.WhAdminId,
WhAdminName = t3.AccountName,
AuditId = t.AuditId,
AuditName = t4.AccountName,
AuditTime = t.AuditTime,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
SourceId = t.SourceId,
SourceCode = t1.WhApplyNo,
Number = t.Number,
Amount = t.Amount,
IsShowEdit = (t.AuditStatus != 2 && t.OperatorId == currentUser.UserID) ? true : false,
ProductionNo = t6.ProductionNo,
ProductionID = t6.ID
})
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <List <TWMProductionWhMainQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TWMProductionWhMainQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取T_MM_ColorSolutionMain主表数据数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <DataTable> > GetAsync(RequestGet requestObject, CurrentUser currentUser)
{
try
{
List <TMMColorSolutionMainQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TMMColorSolutionMainDbModel>().Where(t => t.CompanyId == currentUser.CompanyID && t.DeleteFlag == false);
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TMMColorSolutionMainDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Where(t => !t.DeleteFlag)
.Select((t) => new TMMColorSolutionMainQueryModel
{
ID = t.ID,
PackageId = t.PackageId,
SolutionCode = t.SolutionCode,
ImagePath = t.ImagePath,
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Where(t => !t.DeleteFlag)
.Select((t) => new TMMColorSolutionMainQueryModel
{
ID = t.ID,
PackageId = t.PackageId,
SolutionCode = t.SolutionCode,
ImagePath = t.ImagePath,
})
.ToListAsync();
}
List <TMMColorSolutionDetailQueryModel> detailList = null;
if (queryData != null && queryData.Count > 0)
{
detailList = await _db.Instance.Queryable <TMMColorSolutionDetailDbModel, TBMDictionaryDbModel, TBMDictionaryDbModel>(
(t, t0, t1) => new object[]
{
JoinType.Left, t.ItemId == t0.ID,
JoinType.Left, t.ColorId == t1.ID
}
)
.In(t => t.MainId, queryData.Select(p => p.ID).ToArray())
.Select((t, t0, t1) => new TMMColorSolutionDetailQueryModel
{
ID = t.ID,
MainId = t.MainId,
ItemId = t.ItemId,
ItemName = t0.DicValue,
ColorId = t.ColorId,
ColorName = t1.DicValue
})
.ToListAsync();
}
var returnTable = GetReturnDataTable(queryData, detailList);
//返回执行结果
return(ResponseUtil <DataTable> .SuccessResult(returnTable, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <DataTable> .FailResult(null, ex.Message));
}
}
