using AutoMapper;
using NPOI.SS.Formula.Functions;
using OASystem.Domain;
using OASystem.Domain.Dtos;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Infrastructure.Repositories.System;
using System;
using System.Collections.Generic;
using System.ComponentModel.Design;
using System.Linq;
using System.Runtime.Intrinsics.Arm;
using System.Text;
using System.Threading.Tasks;
namespace OASystem.Infrastructure.Repositories.Financial
{
/// <summary>
/// 财务 - 日付申请
/// </summary>
public class DailyFeePaymentRepository : BaseRepository<Fin_DailyFeePayment, Fin_DailyFeePaymentView>
{
private readonly IMapper _mapper;
private readonly System.SetDataTypeRepository _setDataTypeRep;
private readonly System.UsersRepository _UsersRep;
private readonly System.CompanyRepository _CompanyRep;
/// <summary>
///
/// </summary>
/// <param name="sqlSugar"></param>
/// <param name="mapper"></param>
/// <param name="setDataTypeRep"></param>
/// <param name="usersRep"></param>
/// <param name="companyRep"></param>
public DailyFeePaymentRepository(SqlSugarClient sqlSugar,
IMapper mapper,
System.SetDataTypeRepository setDataTypeRep,
UsersRepository usersRep,
CompanyRepository companyRep)
: base(sqlSugar)
{
this._mapper = mapper;
this._setDataTypeRep = setDataTypeRep;
this._UsersRep = usersRep;
this._CompanyRep = companyRep;
}
/// <summary>
/// 日付申请查询 使用的数据源
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GetPagePriceTypeData(PortDtoBase dto, int currUserId = 0)
{
Result result = new Result() { Code = -2 };
dynamic? DailyFeePaymentList = null;
var setTypeData = _setDataTypeRep.QueryDto<Sys_SetDataType, Fin_DailyFeePaymentPagePriceTypeView>().ToList();
var setData = _setDataTypeRep.QueryDto<Sys_SetData, Fin_DailyFeePaymentPagePriceSubTypeView>().ToList();
//48人员费用 49办公费用 50 销售费用 51 其他费用 55 大运会
var typeIds = new List<int>() {
48, //人员费用
49, //办公费用
50, //销售费用
51, //其他费用
55, //大运会
90, //各部门基础固定费用明细
104, //团组费用
};
var priceTypeData = setTypeData.Where(s => typeIds.Contains(s.Id)).ToList();
var priceSubTypeData = setData.Where(s => typeIds.Contains(s.STid)).ToList();
//var isEnable = false;
//员工姓名列表
var userData = _sqlSugar.Queryable<Sys_Users>()
.LeftJoin<Sys_Department>((u, d) => u.DepId == d.Id)
.LeftJoin<Sys_JobPost>((u, d, jp) => u.JobPostId == jp.Id)
.Where((u, d, jp) => u.IsDel == 0)
.Select((u, d, jp) => new
{
u.Id,
u.CnName,
u.DepId,
d.DepName,
u.JobPostId,
jp.JobName
})
.ToList();
//1 经理职位 查看该部门下所有人员
if (currUserId > 0)
{
//42
var auditUserIds = _sqlSugar.Queryable<Sys_UserAuthority>().Where(x => x.IsDel == 0 && x.SmId == 42 && x.FId == 12).Select(x => x.UId).ToList();
if (!auditUserIds.Contains(currUserId))
{
var screenWheres = new List<string>() { "经理", "主管" };
var userInfo = userData.Find(x => x.Id == currUserId && screenWheres.Contains(x.JobName));
if (userInfo != null)
{
userData = userData.Where(x => x.DepName.Equals(userInfo.DepName)).ToList();
}
else
{
userData = userData.Where(x => x.Id == currUserId).ToList();
}
}
else
{
userData.Insert(0, new { Id = -1, CnName = "全部", DepId = 0, DepName = "", JobPostId = 0, JobName = "" });
}
}
var userData1 = userData.Select(x => new { x.Id, x.CnName }).ToList();
//62 公转 63 私转
var feeMarkTypeData = setTypeData.Where(s => s.Id == 62 || s.Id == 63).ToList();
var feeMarkSubTypeData = setData.Where(s => s.STid == 62 || s.STid == 63).ToList();
var companyNameData = await _CompanyRep.GetCompanyNameData();
//审核状态
var auditStatusData = new List<dynamic>()
{
new { Id = -1, Name = "全部" },
new { Id = 0, Name = "未审核" },
new { Id = 1, Name = "已审核" },
new { Id = 2, Name = "审核未通过" },
new { Id = 3, Name = "自动审核" }
};
if (dto.PortType == 1) //web
{
DailyFeePaymentList = new
{
FeeTypeData = priceTypeData,
FeeSubTypeData = priceSubTypeData,
UserNameData = userData1,
FeeMarkTypeData = feeMarkTypeData,
FeeMarkSubTypeData = feeMarkSubTypeData,
CompanyNameData = companyNameData.Data,
AuditStatusData = auditStatusData
};
}
else if (dto.PortType == 2) //安卓
{
DailyFeePaymentList = new
{
UserNameData = userData1,
FeeTypeData = priceTypeData,
FeeTypeSubData = priceSubTypeData
};
}
result.Code = 0;
result.Msg = "查询成功!";
result.Data = DailyFeePaymentList;
return result;
}
/// <summary>
/// 总经理自动审核通过类型
/// 90 Parent 各部门基础固定费用明细
/// 104 Parent 团组费用
/// 672 Sub 办公费用-信息部申请费用
/// </summary>
/// <param name="typeId"></param>
/// <returns></returns>
public async Task<bool> GMAutoApprovalType(int typeId)
{
if (typeId < 1) return false;
var parentIds = new List<int>()
{
90, // 各部门基础固定费用明细
104, // 团组费用
};
var subIds = new List<int>()
{
672, //办公费用-信息部申请费用
};
var parentTypeDatas = await _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0 && parentIds.Contains(x.STid)).ToListAsync();
var subTypeDatas = await _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0 && subIds.Contains(x.Id)).ToListAsync();
var typeDatas = new List<Sys_SetData>();
if (parentTypeDatas.Any()) typeDatas.AddRange(parentTypeDatas);
if (subTypeDatas.Any()) typeDatas.AddRange(subTypeDatas);
if (typeDatas.Any(x => x.Id == typeId)) return true;
return false;
}
/// <summary>
/// 日付申请查询 使用的数据源
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GetPriceTypeAddData(PortDtoBase dto)
{
Result result = new Result() { Code = -2 };
dynamic? DailyFeePaymentList = null;
var setTypeData = _setDataTypeRep.QueryDto<Sys_SetDataType, Fin_DailyFeePaymentPagePriceTypeView>().ToList();
var setData = _setDataTypeRep.QueryDto<Sys_SetData, Fin_DailyFeePaymentPagePriceSubTypeView>().ToList();
//48人员费用 49办公费用 50 销售费用 51 其他费用 55 大运会
var priceTypeData = setTypeData.Where(s => s.Id == 48 || s.Id == 49 || s.Id == 50 || s.Id == 51 || s.Id == 55).ToList();
var priceSubTypeData = setData.Where(s => s.STid == 48 || s.STid == 49 || s.STid == 50 || s.STid == 51 || s.STid == 55).ToList();
//员工姓名列表
var userNameData = await _UsersRep.GetUserNameList(dto.PortType);
//62 公转 63 私转
var feeMarkTypeData = setTypeData.Where(s => s.Id == 62 || s.Id == 63).ToList();
var feeMarkSubTypeData = setData.Where(s => s.STid == 62 || s.STid == 63).ToList();
var companyNameData = await _CompanyRep.GetCompanyNameData();
if (dto.PortType == 1) //web
{
DailyFeePaymentList = new Fin_DailyFeePaymentPagePriceTypeDataView
{
FeeTypeData = priceTypeData,
FeeSubTypeData = priceSubTypeData,
UserNameData = userNameData.Data,
FeeMarkTypeData = feeMarkTypeData,
FeeMarkSubTypeData = feeMarkSubTypeData,
CompanyNameData = companyNameData.Data
};
}
else if (dto.PortType == 2) //安卓
{
DailyFeePaymentList = new
{
CompanyNameData = companyNameData.Data,
FeeTypeData = feeMarkTypeData,
FeeTypeSubData = feeMarkSubTypeData
};
}
result.Code = 0;
result.Msg = "查询成功!";
result.Data = DailyFeePaymentList;
return result;
}
/// <summary>
/// 日付申请 page 查询
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GetPageSearchAll(PageDailyFeePaymentDto dto)
{
Result result = new Result() { Code = -2 };
ListViewBase<Fin_DailyFeePaymentPageListView> dailyFeePaymentPageList = new ListViewBase<Fin_DailyFeePaymentPageListView>()
{
ReceiveDt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
};
#region sql条件处理
string sqlWhere = "";
if (dto.CompanyId != -1) //公司
sqlWhere += string.Format(" And dfp.CompanyId = {0}", dto.CompanyId);
if (dto.FinancialAuditStatus != -1) //财务审核
sqlWhere += string.Format(" And dfp.FAudit = {0}", dto.FinancialAuditStatus);
if (dto.ManagerAuditStatus != -1) //总经理审核
sqlWhere += string.Format(" And dfp.MAudit = {0}", dto.ManagerAuditStatus);
if (dto.IsPaySign != -1) //付款状态
{
sqlWhere += string.Format(" And dfp.IsPay = {0}", dto.IsPaySign);
}
if (dto.FeeTypeId != -1) //费用类型
{
if (dto.FeeSubTypeId != -1) //子类处理
{
sqlWhere += string.Format(" And dfp.PriceTypeId = {0}", dto.FeeSubTypeId);
}
else
{
var setData = _setDataTypeRep.QueryDto<Sys_SetData, SetDataView>(s => s.STid == dto.FeeTypeId).ToList();
if (setData.Count > 0)
{
string setDataIds = string.Join(",", setData.Select(x => x.Id).ToList());
sqlWhere += string.Format(" And dfp.PriceTypeId In ({0})", setDataIds);
}
}
}
if (!string.IsNullOrEmpty(dto.FeeDesc))
sqlWhere += string.Format(" And dfp.Instructions Like '%{0}%'", dto.FeeDesc);
if (dto.CreateUserId != -1)
sqlWhere += string.Format(" And dfp.CreateUserId = {0}", dto.CreateUserId);
int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
int endIndex = startIndex + dto.PageSize - 1;
string sqlPage = string.Format(@"Select * From (
Select row_number() over (order by dfp.Id Desc) as RowNumber,
dfp.Id,dfp.CompanyId,c.CompanyName,dfp.Instructions,dfp.SumPrice,
dfp.CreateUserId,u.CnName CreateUser,dfp.CreateTime,dfp.FAudit,dfp.MAudit,
dfp.PriceTypeId,dfp.IsPay
From Fin_DailyFeePayment dfp
Inner Join Sys_Company c On dfp.CompanyId = c.Id
Left Join Sys_Users u On dfp.CreateUserId = u.Id
Where dfp.IsDel=0 {0}
) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex);
string sqlCount = string.Format(@"Select COUNT(1) as Count From (
Select dfp.Id,dfp.CompanyId,c.CompanyName,dfp.Instructions,dfp.SumPrice,
dfp.CreateUserId,u.CnName CreateUser,dfp.CreateTime,dfp.FAudit,dfp.MAudit,
dfp.PriceTypeId
From Fin_DailyFeePayment dfp
Inner Join Sys_Company c On dfp.CompanyId = c.Id
Left Join Sys_Users u On dfp.CreateUserId = u.Id
Where dfp.IsDel=0 {0}
) temp ", sqlWhere);
#endregion
if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //web
{
//Fin_DailyFeePaymentPageCount
var dailyFeePaymentCount = await _sqlSugar.SqlQueryable<Fin_DailyFeePaymentPageCount>(sqlCount).FirstAsync();
var DailyFeePaymentData = await _sqlSugar.SqlQueryable<Fin_DailyFeePaymentPageListView>(sqlPage).ToListAsync();
int totalCount = dailyFeePaymentCount.Count;
dailyFeePaymentPageList = new ListViewBase<Fin_DailyFeePaymentPageListView>
{
CurrPageIndex = dto.PageIndex,
CurrPageSize = dto.PageSize,
DataCount = totalCount,
DataList = DailyFeePaymentData
};
}
result.Code = 0;
result.Msg = "查询成功!";
result.Data = dailyFeePaymentPageList;
return result;
}
/// <summary>
/// 日付申请 single 查询 By Id
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GetSearchById(SearchDailyFeePaymentDto dto)
{
Result result = new Result() { Code = -2 };
if (dto.PortType == 1) //web
{
Fin_DailyFeePaymentInfolView feeData = new Fin_DailyFeePaymentInfolView();
string feeSql = string.Format(@"Select * From Fin_DailyFeePayment
Where IsDel=0 And Id = {0} ", dto.Id);
feeData = await _sqlSugar.SqlQueryable<Fin_DailyFeePaymentInfolView>(feeSql).FirstAsync();
string feeContentSql = string.Format(@"Select * From Fin_DailyFeePaymentContent
Where IsDel=0 And DFPId = {0} ", dto.Id);
feeData.FeeContents = await _sqlSugar.SqlQueryable<Fin_DailyFeePaymentContentInfolView>(feeContentSql).ToListAsync();
result.Code = 0;
result.Msg = "查询成功!";
result.Data = feeData;
}
else if (dto.PortType == 2 || dto.PortType == 3) //android And ios
{
Fin_DailyFeePaymentInfoAndroidlView feeData = new Fin_DailyFeePaymentInfoAndroidlView();
string feeSql = string.Format(@"Select * From Fin_DailyFeePayment
Where IsDel=0 And Id = {0} ", dto.Id);
feeData = await _sqlSugar.SqlQueryable<Fin_DailyFeePaymentInfoAndroidlView>(feeSql).FirstAsync();
//feeData.TransferTypeId = feeData.TransferTypeId == 0 ? 62 : feeData.TransferTypeId == 1 ? 63 : 0;
string feeContentSql = string.Format(@"Select * From Fin_DailyFeePaymentContent
Where IsDel=0 And DFPId = {0} ", dto.Id);
feeData.FeeContents = await _sqlSugar.SqlQueryable<Fin_DailyFeePaymentContentInfolView>(feeContentSql).ToListAsync();
result.Code = 0;
result.Msg = "查询成功!";
result.Data = feeData;
}
return result;
}
/// <summary>
/// 日付申请 添加
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> Add(AddDailyFeePaymentDto dto)
{
Result result = new Result() { Code = -2 };
_sqlSugar.BeginTran();
var _fee = _mapper.Map<Fin_DailyFeePayment>(dto);
_fee.CreateUserId = dto.UserId;
_fee.GroupId = dto.GroupId;
//自动审核验证
var auditPer = await GMAutoApprovalType(dto.PriceTypeId);
var priceTypeInfo = await _sqlSugar.Queryable<Sys_SetData>().FirstAsync(x => x.IsDel == 0 && x.Id == dto.PriceTypeId);
if (auditPer)
{
_fee.MAudit = 3;
_fee.MAuditDate = DateTime.Now;
}
int? feeId = await _sqlSugar.Insertable(_fee).ExecuteReturnIdentityAsync();
if (dto.FeeContents.Any())
{
var _feeContents = _mapper.Map<List<Fin_DailyFeePaymentContent>>(dto.FeeContents);
foreach (var item in _feeContents)
{
item.DFPId = feeId == null ? -1 : Convert.ToInt32(feeId);
item.CreateUserId = dto.UserId;
}
await _sqlSugar.Insertable(_feeContents).ExecuteCommandAsync();
}
_sqlSugar.CommitTran();
result.Code = 0;
var data = new { dailyId = feeId, sign = 1 };
result.Data = data;
return result;
}
/// <summary>
/// 日付申请 编辑
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> Edit(EditDailyFeePaymentDto dto)
{
Result result = new Result() { Code = -2 };
//自动审核验证
var auditPer = await GMAutoApprovalType(dto.PriceTypeId);
#region 已审核的数据不可编辑
var dailyFeeInfo = await _sqlSugar.Queryable<Fin_DailyFeePayment>().Where(it => it.Id == dto.Id && it.IsDel == 0).FirstAsync();
if (dailyFeeInfo != null)
{
var auditIds = new List<int>() { 1, 3 };
if (auditIds.Contains(dailyFeeInfo.FAudit) || auditIds.Contains(dailyFeeInfo.MAudit))
{
if (auditPer)
{
if (dailyFeeInfo.IsPay == 1)
{
result.Msg = "该笔费用已付款，不可修改！";
return result;
}
}
else
{
result.Msg = "审核已通过，不可修改！";
return result;
}
}
}
#endregion
_sqlSugar.BeginTran();
try
{
Fin_DailyFeePayment _fee = _mapper.Map<Fin_DailyFeePayment>(dto);
_fee.CreateUserId = dto.UserId;
int? editFeeStatus = await _sqlSugar.Updateable<Fin_DailyFeePayment>()
.SetColumns(a => new Fin_DailyFeePayment
{
Instructions = dto.Instructions,
SumPrice = dto.SumPrice,
TransferTypeId = dto.TransferTypeId,
PriceTypeId = dto.PriceTypeId,
CompanyId = dto.CompanyId,
GroupId = dto.GroupId,
})
.SetColumnsIF(auditPer, a => a.MAudit == 3)
.SetColumnsIF(auditPer, a => a.MAuditDate == DateTime.Now)
.Where(a => a.Id == dto.Id)
.ExecuteCommandAsync();
var _feeContents = _mapper.Map<List<Fin_DailyFeePaymentContent>>(dto.FeeContents);
await _sqlSugar.Updateable<Fin_DailyFeePaymentContent>()
.Where(a => a.DFPId == _fee.Id)
.SetColumns(a => new Fin_DailyFeePaymentContent
{
IsDel = 1,
DeleteUserId = _fee.CreateUserId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
})
.ExecuteCommandAsync();
if (_feeContents.Any())
{
foreach (var item in _feeContents)
{
item.DFPId = _fee.Id;
item.CreateUserId = dto.UserId;
}
await _sqlSugar.Insertable(_feeContents).ExecuteCommandAsync();
}
_sqlSugar.CommitTran();
result.Code = 0;
var data = new { dailyId = dto.Id, sign = 2 };
result.Data = data;
}
catch (Exception ex)
{
_sqlSugar.RollbackTran();
result.Msg = ex.Message;
}
return result;
}
/// <summary>
/// 日付申请 删除
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> Del(DelDailyFeePaymentDto dto)
{
Result result = new Result() { Code = -2 };
int? delFeeStatus = await _sqlSugar.Updateable<Fin_DailyFeePayment>()
.Where(a => a.Id == dto.Id)
.SetColumns(a => new Fin_DailyFeePayment
{
IsDel = 1,
DeleteUserId = dto.Id,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
}).ExecuteCommandAsync();
result.Code = 0;
return result;
}
/// <summary>
/// 日付申请 审核
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> DailyPaymentAudit(DP_AuditStatusDto dto)
{
Result result = new Result() { Code = -2 };
if (dto.AuditType == 1) //财务审核
{
int? auditStatus = await _sqlSugar.Updateable<Fin_DailyFeePayment>()
.Where(a => a.Id == dto.Id)
.SetColumns(a => new Fin_DailyFeePayment
{
FAudit = dto.AuditStatus,
FAuditDate = DateTime.Now,
}).ExecuteCommandAsync();
if (auditStatus != null && auditStatus > 0)
result.Code = 0;
else
result.Msg = "财务审核操作失败";
}
else if (dto.AuditType == 2) //总经理
{
int? auditStatus = await _sqlSugar.Updateable<Fin_DailyFeePayment>()
.Where(a => a.Id == dto.Id)
.SetColumns(a => new Fin_DailyFeePayment
{
MAudit = dto.AuditStatus,
MAuditDate = DateTime.Now,
}).ExecuteCommandAsync();
if (auditStatus != null && auditStatus > 0)
result.Code = 0;
else
result.Msg = "总经理审核操作失败";
}
return result;
}
}
}