123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393 |
- using AutoMapper;
- using MathNet.Numerics.Distributions;
- using MathNet.Numerics.Statistics.Mcmc;
- using Newtonsoft.Json;
- using NPOI.SS.Formula.Functions;
- using OASystem.Domain;
- using OASystem.Domain.Dtos.Resource;
- using OASystem.Domain.Entities.Groups;
- using OASystem.Domain.Entities.Resource;
- using OASystem.Domain.ViewModels.Resource;
- using OASystem.Infrastructure.Tools;
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
- namespace OASystem.Infrastructure.Repositories.Resource
- {
- public class OfficialActivitiesRepository : BaseRepository<Res_OfficialActivities, OfficialActivitiesView>
- {
- private readonly IMapper _mapper;
- public OfficialActivitiesRepository(SqlSugarClient sqlSugar, IMapper mapper) : base(sqlSugar)
- {
- _mapper = mapper;
- }
- /// <summary>
- /// 根据Diid查询公务出访数据List
- /// </summary>
- /// <param name="dto"></param>
- /// <returns></returns>
- public async Task<JsonView> QueryOfficialActivitiesByDiId(OfficialActivitiesByDiIdDto dto)
- {
- JsonView result = new JsonView() { Code = StatusCodes.Status200OK, Msg = "暂无数据" };
- string sqlWhere = string.Empty;
- sqlWhere += string.Format(@"AND o.Isdel={0} AND o.DiId={1} ", 0, dto.DiId);
- if (!string.IsNullOrEmpty(sqlWhere.Trim()))
- {
- Regex r = new Regex("AND");
- sqlWhere = r.Replace(sqlWhere, "WHERE", 1);
- }
- string sql = string.Format(@"
- SELECT
- *,
- u.CnName AS CreateUserName,
- sd.Name AS OfficialFormName
- FROM
- Res_OfficialActivities o
- LEFT JOIN Sys_SetData sd ON o.OfficialForm = sd.Id
- LEFT JOIN Sys_Users u ON o.CreateUserId = u.Id
- {0}
- ORDER BY
- o.CreateTime desc
- ", sqlWhere);
- var OfficialActivities = await _sqlSugar.SqlQueryable<OfficialActivitiesView>(sql).ToListAsync();
- if (OfficialActivities.Count != 0)
- {
- if (dto.PageSize == 0 && dto.PageIndex == 0)
- {
- OfficialActivities.ForEach(x =>
- {
- //2024年4月1日 11:55:44 -蒋金辰 -日期处理
- DateTime dt;
- bool b_dt = DateTime.TryParse(x.Date, out dt);
- if (b_dt)
- {
- if (!string.IsNullOrEmpty(x.Time)) x.Date = dt.ToString("yyyy-MM-dd") + " " + x.Time;
- else x.Date = dt.ToString("yyyy-MM-dd HH:mm:ss");
- }
- });
- result = new JsonView() { Code = 200, Msg = "查询成功!", Data = OfficialActivities };
- }
- else
- {
- int count = OfficialActivities.Count;
- float totalPage = (float)count / dto.PageSize;//总页数
- if (totalPage == 0) totalPage = 1;
- else totalPage = (int)Math.Ceiling((double)totalPage);
- List<OfficialActivitiesView> _OfficialActivities = new List<OfficialActivitiesView>();
- for (int i = 0; i < dto.PageSize; i++)
- {
- var RowIndex = i + (dto.PageIndex - 1) * dto.PageSize;
- if (RowIndex < OfficialActivities.Count)
- {
- //2024年4月1日 11:55:44 -蒋金辰 -日期处理
- DateTime dt;
- bool b_dt = DateTime.TryParse(OfficialActivities[RowIndex].Date, out dt);
- if (b_dt)
- {
- OfficialActivities[RowIndex].Date = dt.ToString("yyyy-MM-dd HH:mm");
- }
- _OfficialActivities.Add(OfficialActivities[RowIndex]);
- }
- else
- {
- break;
- }
- }
- ListViewBase<OfficialActivitiesView> rst = new ListViewBase<OfficialActivitiesView>();
- rst.DataList = _OfficialActivities;
- rst.DataCount = count;
- rst.CurrPageIndex = dto.PageIndex;
- rst.CurrPageSize = dto.PageSize;
- result = new JsonView() { Code = 200, Msg = "查询成功!", Data = rst };
- }
- }
- else
- {
- result = new JsonView() { Code = StatusCodes.Status200OK, Msg = "暂无数据!" };
- if (dto.PageSize == 0 && dto.PageIndex == 0) { result.Data = OfficialActivities; }
- else {
- ListViewBase<OfficialActivitiesView> rst = new ListViewBase<OfficialActivitiesView>();
- rst.DataList = OfficialActivities;
- rst.DataCount = 0;
- rst.CurrPageIndex = dto.PageIndex;
- rst.CurrPageSize = dto.PageSize;
- result.Data = rst;
- }
- }
- return result;
- }
- /// <summary>
- /// 根据公务出访Id查询单个数据
- /// </summary>
- /// <param name="dto"></param>
- /// <returns></returns>
- /// <exception cref="NotImplementedException"></exception>
- public async Task<Result> QueryOfficialActivitiesById(OfficialActivitiesDiIdDto dto)
- {
- Result result = new Result() { Code = -2, Msg = "未知错误" };
- try
- {
- string sqlWhere = string.Empty;
- sqlWhere += string.Format(@"AND oa.Isdel={0} AND oa.DiId={1} AND oa.Id={2}", 0, dto.DiId, dto.Id);
- if (!string.IsNullOrEmpty(sqlWhere.Trim()))
- {
- Regex r = new Regex("AND");
- sqlWhere = r.Replace(sqlWhere, "WHERE", 1);
- }
- string sql = string.Format(@"
- SELECT
- oa.*,
- u.CnName AS CreateUserName,
- sd.Name AS OfficialFormName
- FROM
- Res_OfficialActivities oa
- LEFT JOIN Sys_Users u ON oa.CreateUserId = u.Id
- LEFT JOIN Sys_SetData sd ON oa.OfficialForm = sd.Id
- {0}", sqlWhere);
- var OfficialActivities = await _sqlSugar.SqlQueryable<OfficialActivitiesView>(sql)
- .FirstAsync();
- //OfficialActivities.ScreenshotOfMailUrls.ForEach(url => { url = AppSettingsHelper.Get("GrpFileBaseUrl") + url; });
- OfficialActivities.TranslatorIdItem = _sqlSugar.Queryable<Grp_OfficialDutyLinkTranslator>()
- .Where(x => x.IsDel == 0 && x.OfficialDutyId == dto.Id)
- .Select(x => x.TranslatorId)
- .ToArray();
- result = new Result() { Code = 0, Msg = "查询成功!", Data = OfficialActivities };
- }
- catch (Exception ex)
- {
- result = new Result() { Code = -2, Msg = "未知错误" };
- }
- return result;
- }
- public async Task<Result> OpOfficialActivities(OpOfficialActivitiesDto dto)
- {
- Result result = new Result() { Code = -2, Msg = "未知错误" };
- #region 特殊字符转码 037 - 4.28 15:17
- if (!string.IsNullOrEmpty(dto.Contact))
- {
- byte[] utf8Bytes = Encoding.UTF8.GetBytes(dto.Contact);
- byte[] utf16Bytes = Encoding.Convert(Encoding.UTF8, Encoding.Unicode, utf8Bytes);
- dto.Contact = Encoding.Unicode.GetString(utf16Bytes);
- }
- #endregion
- _sqlSugar.BeginTran();
- //添加到资料库
- Res_InvitationOfficialActivityData res_InvitationData = new Res_InvitationOfficialActivityData();
- res_InvitationData.Country = dto.Country;
- res_InvitationData.City = dto.Area;
- res_InvitationData.UnitName = dto.Client;
- res_InvitationData.Delegation = dto.DiId.ToString();
- res_InvitationData.Address = dto.Address;
- res_InvitationData.CreateUserId = dto.CreateUserId;
- res_InvitationData.Contact = dto.Contact;
- res_InvitationData.Job = dto.Job;
- res_InvitationData.Tel = dto.Tel;
- res_InvitationData.Field = dto.Field;
- Res_InvitationOfficialActivityData ifNullUp = await _sqlSugar.Queryable<Res_InvitationOfficialActivityData>().FirstAsync
- (a => a.Country == res_InvitationData.Country && a.City == res_InvitationData.City && a.UnitName == res_InvitationData.UnitName && a.IsDel == 0 && a.Address == res_InvitationData.Address);
- if (ifNullUp == null)
- {
- int DataID = await _sqlSugar.Insertable(res_InvitationData).ExecuteReturnIdentityAsync();
- }
- else
- {
- res_InvitationData.Id = ifNullUp.Id;
- await _sqlSugar.Updateable<Res_InvitationOfficialActivityData>(res_InvitationData).ExecuteCommandAsync();
- }
- if (dto.Status == 1)//添加
- {
- var res_InvitationOfficial = await _sqlSugar.Queryable<Res_OfficialActivities>().FirstAsync(x => x.Client == dto.Client && x.Address == dto.Address && x.IsDel == 0 && x.DiId == dto.DiId);
- if (res_InvitationOfficial != null)
- {
- _sqlSugar.RollbackTran();
- result = new Result() { Code = -1, Msg = "该信息已存在,请勿重复添加!" };
- }
- else//不存在,可添加
- {
- Res_OfficialActivities _InvitationOfficialActivityData = _mapper.Map<Res_OfficialActivities>(dto);
- int id = await _sqlSugar.Insertable(_InvitationOfficialActivityData).ExecuteReturnIdentityAsync();
- if (id == 0)
- {
- _sqlSugar.RollbackTran();
- result = new Result() { Code = -1, Msg = "添加失败!" };
- }
- else
- {
- #region 新增(公务信息关联翻译人员) 关联信息
- if (dto.TranslatorIdItem != null && dto.TranslatorIdItem.Length > 0)
- {
- var officialDutyLinkTranslators = new List<Grp_OfficialDutyLinkTranslator>();
- foreach (var translatorId in dto.TranslatorIdItem)
- {
- officialDutyLinkTranslators.Add(new Grp_OfficialDutyLinkTranslator()
- {
- TranslatorId = translatorId,
- OfficialDutyId = id,
- CreateUserId = dto.CreateUserId,
- Remark = $"公务出访客户资料-->添加"
- });
- }
- if (officialDutyLinkTranslators.Count > 0)
- {
- await _sqlSugar.Insertable(officialDutyLinkTranslators).ExecuteCommandAsync();
- }
- }
- #endregion
- _sqlSugar.CommitTran();
- result = new Result() { Code = 0, Msg = "添加成功!", Data = new { Id = id } };
- }
- }
- }
- else if (dto.Status == 2)//修改
- {
- bool res = await UpdateAsync(a => a.Id == dto.Id, a => new Res_OfficialActivities
- {
- Country = dto.Country,
- Area = dto.Area,
- Type = dto.Type,
- Client = dto.Client,
- Date = dto.Date,
- Time = dto.Time,
- Address = dto.Address,
- Contact = dto.Contact,
- Job = dto.Job,
- Tel = dto.Tel,
- OfficialForm = dto.OfficialForm,
- Field = dto.Field,
- ReqSample = dto.ReqSample,
- Setting = dto.Setting,
- Dresscode = dto.Dresscode,
- Attendees = dto.Attendees,
- IsNeedTrans = dto.IsNeedTrans,
- //Translators = dto.Translators,
- Language = dto.language,
- Trip = dto.Trip,
- CreateUserId = dto.CreateUserId,
- Remark = dto.Remark,
- IsPay = dto.IsPay,
- IsSubmitApproval = dto.IsSubmitApproval,
- EmailOrWeChat = dto.EmailOrWeChat,
- Website = dto.Website,
- Nature = dto.Nature,
- });
- if (res)
- {
- #region 更新(公务信息关联翻译人员) 关联信息
- var officialDutyLinkTranslators_select = await _sqlSugar.Queryable<Grp_OfficialDutyLinkTranslator>()
- .Where(x => x.IsDel == 0 && x.OfficialDutyId == dto.Id)
- .ToListAsync();
- //删除
- var officialDutyLinkTranslatorIds = officialDutyLinkTranslators_select.Select(x => x.Id).ToList();
- if (officialDutyLinkTranslatorIds.Count > 0)
- {
- await _sqlSugar.Updateable<Grp_OfficialDutyLinkTranslator>()
- .SetColumnsIF(officialDutyLinkTranslatorIds.Count > 0, x => new Grp_OfficialDutyLinkTranslator()
- {
- DeleteUserId = dto.CreateUserId,
- DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
- IsDel = 1,
- Remark = $"公务出访客户资料-->删除"
- })
- .Where(x => officialDutyLinkTranslatorIds.Contains(x.Id))
- .ExecuteCommandAsync();
- }
- //添加
- if (dto.TranslatorIdItem != null && dto.TranslatorIdItem.Length > 0)
- {
- var officialDutyLinkTranslators = new List<Grp_OfficialDutyLinkTranslator>();
- foreach (var translatorId in dto.TranslatorIdItem)
- {
- officialDutyLinkTranslators.Add(new Grp_OfficialDutyLinkTranslator()
- {
- TranslatorId = translatorId,
- OfficialDutyId = dto.Id,
- CreateUserId = dto.CreateUserId,
- Remark = $"公务出访客户资料-->更新"
- });
- }
- if (officialDutyLinkTranslators.Count > 0)
- {
- await _sqlSugar.Insertable(officialDutyLinkTranslators).ExecuteCommandAsync();
- }
- }
- #endregion
- _sqlSugar.CommitTran();
- result = new Result() { Code = 0, Msg = "修改成功!", Data = new { Id = dto.Id } };
- }
- else
- {
- _sqlSugar.RollbackTran();
- result = new Result() { Code = -1, Msg = "修改失败!" };
- }
- }
- else
- {
- _sqlSugar.RollbackTran();
- result = new Result() { Code = -1, Msg = "请传入Status参数,1添加 2修改!" };
- }
- return result;
- }
- public async Task<Result> PostReqReqSampleTips(string country, string Area,string client)
- {
- if (string.IsNullOrEmpty(country)) return new Result() { Code = -1, Msg = "国家为空!" };
- string sqlWhere = string.Empty;
- if (!string.IsNullOrEmpty(Area)) sqlWhere = string.Format(@$" And oa.Area Like '%{Area}%'");
- if (!string.IsNullOrEmpty(client)) sqlWhere = string.Format(@$" And oa.Client Like '%{client}%'");
- string sql = string.Format(@$"Select di.TeamName,oa.Id,oa.Country,oa.Area,oa.Client,oa.ReqSample
- From Res_OfficialActivities oa With(NoLock)
- Left Join Grp_DelegationInfo di On oa.DiId = di.Id
- Where oa.IsDel = 0 And oa.Country='{country}' {sqlWhere}");
- //ReqReqSampleTipsView
- var _views = await _sqlSugar.SqlQueryable<ReqReqSampleTipsView>(sql).ToListAsync();
- if (_views.Count > 0 )
- {
- return new Result() { Code = 0, Msg = "操作成功!", Data = _views };
- }
- return new Result() { Code = -1, Msg = "暂无相关数据!" };
- }
- }
- }
|