using ClosedXML.Excel; using DocumentFormat.OpenXml.EMMA; using Infrastructure; using Infrastructure.Extensions; using Infrastructure.Helpers; using Microsoft.AspNetCore.Hosting; using Moq; using NPOI.SS.Formula.Functions; using NUnit.Framework.Interfaces; using OpenAuth.App.BaseApp.Base; using OpenAuth.App.Common; using OpenAuth.App.Interface; using OpenAuth.App.Request; using OpenAuth.App.Response; using OpenAuth.App.ServiceApp.MiManager.Request; using OpenAuth.Repository; using OpenAuth.Repository.Domain; using Org.BouncyCastle.Ocsp; using SqlSugar; using static NPOI.HSSF.Util.HSSFColor; namespace OpenAuth.App { public class MiVehiclePickupApp : SqlSugarBaseApp { private readonly IWebHostEnvironment _env; private MiWordHelper _helper; public MiVehiclePickupApp( ISugarUnitOfWork unitWork, ISimpleClient repository, IAuth auth, IWebHostEnvironment env, MiWordHelper helper ) : base(unitWork, repository, auth) { _env = env; _helper = helper; } #region 查询 /// /// 电脑端分页查询 /// public async Task>>> LoadAllPage(MiVehiclePickUpreq req) { RefAsync totalCount = 0; var user = _auth.GetCurrentUser(); var users = user.User; //如果不是超级管理员 //if (user != null && users.Id != -1) //{ // //查询所有子部门 // List orgidlist = new List(); // var orgs = user.Orgs?.Select(r => r.Id).ToList(); // using (var uow = base.UnitWork.CreateContext()) // { // foreach (var item in orgs) // { // orgidlist.Add(item.ToString()); // var allchilds = uow.SysOrg.AsQueryable().ToChildList(r => r.ParentId, item); // if (allchilds.Count > 0) // { // orgidlist = orgidlist.Concat(allchilds.Select(r => r.Id.ToString())).ToList(); // } // } // var list = await uow.MiVehiclePickup.AsQueryable() // .WhereIF(req.begindate != null && req.enddate != null, p => p.InitiateTime >= req.begindate && p.InitiateTime < req.enddate) // .WhereIF(req.status != null, p => p.Status == req.status) // .LeftJoin((p, r) => p.ViolationReportId == r.Id) // .Where((p, r) => orgidlist.Contains(r.HandlingUnit)) // .LeftJoin((p, r, m) => r.MinePointId == m.Id) // .LeftJoin((p, r, m, u) => p.Initiator == u.Id.ToString()) // .WhereIF(!string.IsNullOrEmpty(req.pointname), (p, r, m, u) => m.Name.Contains(req.pointname)) // .WhereIF(!string.IsNullOrEmpty(req.key), (p, r, m, u) => u.Name.Contains(req.key)) // .OrderByDescending((p, r, m, u) => p.InitiateTime) // .Select((p, r, m, u) => new // { // Id = p.Id.SelectAll(), // InitiatorName = u.Name, // PointName = m.Name // }) // .ToPageListAsync(req.page, req.limit, totalCount); // return new Response>> // { // Result = new PageInfo> // { // Items = list, // Total = totalCount // } // }; // } //} //else //{ var list = await base.Repository.AsQueryable() .WhereIF(req.begindate != null && req.enddate != null, p => p.InitiateTime >= req.begindate && p.InitiateTime < req.enddate) .WhereIF(req.status != null, p => p.Status == req.status) .LeftJoin((p, r) => p.ViolationReportId == r.Id) .LeftJoin((p, r, m) => r.MinePointId == m.Id) .LeftJoin((p, r, m, u) => p.Initiator == u.Id.ToString()) .LeftJoin((p,r,m,u,i)=>p.Status.ToString()==i.ItemValue&&i.ItemCode== "JGPickUpStatus") .WhereIF(!string.IsNullOrEmpty(req.pointname), (p, r, m, u, i) => m.Name.Contains(req.pointname)) .WhereIF(!string.IsNullOrEmpty(req.key), (p, r, m, u, i) => u.Name.Contains(req.key)) .OrderByDescending((p, r, m, u, i) => p.InitiateTime) .Select((p, r, m, u, i) => new { Id = p.Id.SelectAll(), InitiatorName = u.Name, PointName = m.Name, r.ViolationTypeName, r.Title, StatusName=i.ItemName }) .ToPageListAsync(req.page, req.limit, totalCount); return new Response>> { Result = new PageInfo> { Items = list, Total = totalCount } }; //} } /// /// app扫码查询当前停车场提车信息 /// /// /// public async Task>> Loadpickupinfo(string parkingid) { var list = await base.Repository.AsQueryable() .Where(p => p.Status == 3) .LeftJoin((p, r) => p.ViolationReportId == r.Id) .Where((p, r) => r.ParkingId == parkingid) .LeftJoin((p, r, m) => r.MinePointId == m.Id) .LeftJoin((p, r, m, u) => p.Initiator == u.Id.ToString()) .OrderByDescending(p => p.InitiateTime) .Select((p, r, m, u) => new { Id = p.Id.SelectAll(), InitiatorName = u.Name, PointName = m.Name }).ToListAsync(); return new Response> { Result = list }; } /// /// 提车时查询所有车辆信息（未提取的） /// /// 违法上报id /// public async Task Loadvechileinfo(string violatid) { using (var uow = base.UnitWork.CreateContext()) { // 查询车辆信息 var vehicles = await uow.MiVehicle.AsQueryable() .Where(v => v.ViolationReportId == violatid && v.State == 0) .ToListAsync(); // 查询车辆图片 List vehicleImages = new List(); if (vehicles.Any()) { var vehicleIds = vehicles.Select(v => v.Id).ToList(); vehicleImages = await uow.MiVehicleImage.AsQueryable() .Where(vi => vehicleIds.Contains(vi.VehicleId)) .ToListAsync(); } return new { Vehicles = vehicles.Select(v => new { v.Id, v.LicensePlate, v.Type, v.Name, v.IdCard, v.Phone, v.TypeName, v.Remark, v.State, VehicleImages = vehicleImages .Where(img => img.VehicleId == v.Id) .Select(img => new { img.Id, img.Image, img.Lng, img.Lat, img.Angle, img.CreateTime }) .ToList() }).ToList(), }; } } #endregion public async Task Get(string id) { using (var uow = base.UnitWork.CreateContext()) { var result = await base.Repository.AsQueryable() .Where(p => p.Id == id) .LeftJoin((p, u) => p.Initiator == u.Id.ToString()) .LeftJoin((p, u, i) => p.Status.ToString() == i.ItemValue && i.ItemCode == "JGPickUpStatus") //.LeftJoin((p, u, u2) => p.Reviewer == u2.Id.ToString()) .Select((p,u, i) => new { // 提车信息 PickupId = p.Id, p.Status, p.Initiator, InitiatorName = u.Name, p.InitiateTime, p.Reviewer, p.ReviewerName, p.ReviewerSignature, p.SeReviewerName, p.SeReviewerSignature, p.SeReviewTime, p.SeReviewComments, p.ReviewComments, p.ReviewTime, p.Remark, p.ViolationReportId, p.Vehicles, i.ItemName }) .FirstAsync(); var result1 = await base.Repository.ChangeRepository>() .AsQueryable() .Where(p => p.Id == result.ViolationReportId) .LeftJoin((p, r) => p.ReportUnit == r.Id.ToString()) .LeftJoin((p, r, m) => p.MinePointId == m.Id) .LeftJoin((p, r, m, pk) => p.ParkingId == pk.Id) .LeftJoin((p, r, m, pk, u) => p.Reporter == u.Id.ToString()) .LeftJoin((p, r, m, pk, u, u2) => p.Handler == u2.Id.ToString()) .LeftJoin((p, r, m, pk, u, u2, o) => p.HandlingUnit == o.Id.ToString()) .Select((p, r, m, pk, u, u2, o) => new { // 违法上报信息 ReporterName = u.Name, HandlerName = u2.Name, ReportUnitName = r.Name, HandUnitName = o.Name, p.Id, p.Title, ReportStatus = p.Status, p.PartyName, p.PartyPhone, p.ViolationType, p.ProblemDescription, p.HandlingOpinion, p.HandlingUnit, p.Handler, p.HandlingTime, p.ReportTime, p.Reporter, p.ReportUnit, p.Lng, p.Lat, p.StatusName, p.ViolationTypeName, p.MineralTypes, // 盗采点信息 MinePointId = m.Id, MinePointName = m.Name, m.CountyName, MinePointLng = m.Lng, MinePointLat = m.Lat, // 停车场信息 ParkingId = pk.Id, ParkingCode = pk.Num, ParkingName = pk.Name, pk.Phone, pk.Address }) .FirstAsync(); if (result == null) return null; List vehicleids = new List(); if (!string.IsNullOrEmpty(result.Vehicles)) { vehicleids = result.Vehicles.Split(',').ToList(); } // 查询车辆信息 var vehicles = await uow.MiVehicle.AsQueryable() .Where(v => v.ViolationReportId == result.ViolationReportId && vehicleids.Contains(v.Id)) .ToListAsync(); // 查询车辆图片 List vehicleImages = new List(); if (vehicles.Any()) { var vehicleIds = vehicles.Select(v => v.Id).ToList(); vehicleImages = await uow.MiVehicleImage.AsQueryable() .Where(vi => vehicleIds.Contains(vi.VehicleId)) .ToListAsync(); } // 查询现场照片 var scenePhotos = await uow.MiScenePhoto.AsQueryable() .Where(sp => sp.ViolationReportId == result.ViolationReportId) .ToListAsync(); // 查询其他人员 var otherPersons = await uow.MiViolationUsers.AsQueryable() .Where(r => r.ViolationReportId == result1.Id) .ToListAsync(); // 查询其他人员图片 List personImages = new List(); if (otherPersons.Any()) { var personIds = otherPersons.Select(v => v.Id).ToList(); personImages = await uow.MiOtherpersonImage.AsQueryable() .Where(vi => personIds.Contains(vi.PersonId)) .ToListAsync(); } return new { PickupInfo = new { result.PickupId, result.Status, result.Initiator, result.InitiatorName, result.InitiateTime, result.Reviewer, result.ReviewerName, result.ReviewTime, result.Remark, result.ReviewComments, result.ReviewerSignature, result.SeReviewComments, result.SeReviewerName, result.SeReviewTime, result.SeReviewerSignature, PickupStatusName=result.ItemName }, ViolationReport = new { result1.Id, result1.Title, Status = result1.ReportStatus, result1.PartyName, result1.PartyPhone, result1.ViolationType, result1.ProblemDescription, result1.HandlingOpinion, result1.HandlingUnit, result1.Handler, result1.HandlingTime, result1.ReportTime, result1.Reporter, result1.ReporterName, result1.HandlerName, result1.ReportUnit, result1.ReportUnitName, result1.Lng, result1.Lat, result1.StatusName, result1.ViolationTypeName, result1.HandUnitName, result1.MineralTypes }, MinePoint = string.IsNullOrEmpty(result1.MinePointId) ? null : new { result1.MinePointId, result1.MinePointName, result1.CountyName, result1.MinePointLng, result1.MinePointLat }, Parking = string.IsNullOrEmpty(result1.ParkingId) ? null : new { result1.ParkingId, result1.ParkingCode, result1.ParkingName, result1.Phone, result1.Address }, Vehicles = vehicles.Select(v => new { v.Id, v.LicensePlate, v.Type, v.TypeName, v.Name, v.IdCard, v.Phone, VehicleImages = vehicleImages .Where(img => img.VehicleId == v.Id) .Select(img => new { img.Id, img.Image, img.Lng, img.Lat, img.Angle, img.CreateTime }) .ToList() }).ToList(), ScenePhotos = scenePhotos.Select(sp => new { sp.Id, sp.Image, sp.Lng, sp.Lat, sp.Angle, sp.CreateTime }).ToList(), OtherPersons = otherPersons.Select(v => new { v.Id, v.Name, v.Phone, v.RelationShip, Images = personImages .Where(img => img.PersonId == v.Id) .Select(img => new { img.Id, img.Image, img.Lng, img.Lat, img.Angle, img.CreateTime }) .ToList() }).ToList() }; } } /// /// 添加 /// public async Task> Add(MiVehiclePickup model) { if (string.IsNullOrEmpty(model.ViolationReportId)) { throw new Exception("请指定违法信息"); } var user = _auth.GetCurrentUser(); using (var uow = base.UnitWork.CreateContext()) { if (!string.IsNullOrEmpty(model.Vehicles)) { #region 添加提车信息 model.Id = Guid.NewGuid().ToString(); model.InitiateTime = DateTime.Now; model.Status = 0; model.Initiator = user.User.Id.ToString(); await uow.MiVehiclePickup.InsertAsync(model); #endregion #region 修改车辆状态、违法上报状态 //查询是否有没选择的车辆（当前违法上报） var vehicle = model.Vehicles.Split(',').ToList(); var vehicleinfo = uow.MiVehicle.AsQueryable() .Where(r => r.ViolationReportId == model.ViolationReportId && r.State == 0).ToList(); //已选车辆信息 var chose = vehicleinfo.Where(r => vehicle.Contains(r.Id)).ToList(); chose.ForEach(a => { a.State = 1; //更新车辆状态为已提车申请 }); await uow.MiVehicle.UpdateRangeAsync(chose); //未选车辆 var notchose = vehicleinfo.Where(r => !vehicle.Contains(r.Id)).ToList(); //查询违法上报信息 var violat = await uow.MiViolationReport.AsQueryable() .Where(R => R.Id == model.ViolationReportId).FirstAsync(); //如果没有未选车辆（全选了）--更新违法上报状态 if (notchose.Count() == 0) { violat.Status = 6; violat.StatusName = "已开单提车"; await uow.MiViolationReport.UpdateAsync(violat); } #endregion #region 添加提车单（解除扣押财务单） if (violat != null) { MiReleaseDocument mr = new MiReleaseDocument(); mr.Id = Guid.NewGuid().ToString(); mr.CreatedAt = DateTime.Now; mr.CreatedBy = user.User.Id.ToString(); mr.Year = DateTime.Now.Year.ToString(); mr.PickUpId = model.Id; var mrinfo = base.Repository.ChangeRepository>().AsQueryable().ToList(); if (mrinfo.Count == 0) { mr.SerialNumber = 1; } else { mr.SerialNumber = mrinfo.Max(r => r.SerialNumber) + 1; } mr.ViolationReportId = model.ViolationReportId; mr.ViolationType = violat.ViolationTypeName; mr.ReleaseDate = DateTime.Now; //扣押财务信息id var seizure = await uow.MiSeizureDocument.AsQueryable() .Where(r => r.ViolationReportId == model.ViolationReportId).FirstAsync(); mr.RelatedSeizureId = seizure?.Id; mr.SeizureData= seizure.SeizureDate; var parking= await uow.MiParking.AsQueryable() .Where(r => r.Id == violat.ParkingId).FirstAsync(); mr.ParkingLot = parking?.Name; mr.Party = violat.PartyName; //查询巡查点信息 var point = await uow.MiMinePoint.AsQueryable().Where(r => r.Id == violat.MinePointId).FirstAsync(); if (point != null) { mr.ClueLocation = point.CountyName + point.StreetName + point.CommunityName + point.Name; } //获取提车截止日期 var config = ConfigHelper.GetConfigRoot(); var day = Convert.ToInt32(config["PickupDeadline"]); //天 mr.PickupDeadline = DateTime.Now.AddDays(day); var type = vehicleinfo.Where(r => vehicle.Contains(r.Id)) .GroupBy(r => r.TypeName) .Select(g => new { name = g.Key, count = g.Count() }).ToList(); string items = ""; foreach (var item in type) { items += item.name + "*" + item.count + ";"; } mr.Items = items; //此处创建word并上传，调用新方法 string filePath = _helper.GenerateReleaseDocumentWord(mr, _env); mr.FilePath = filePath; await uow.MiReleaseDocument.InsertAsync(mr); } #endregion var flag = uow.Commit(); return new Response { Result = flag, Message = flag == true ? "success" : "error" }; } else { throw new Exception("请选择提车车辆"); } } } /// /// 删除 /// /// public async Task> Delete(List ids) { var flag = await Repository.DeleteAsync(r => ids.Contains(r.Id)); return new Response { Result = flag, Message = flag == true ? "success" : "error" }; } /// /// 更新 /// /// public async Task> Update(MiVehiclePickup model) { bool flag = await base.Repository.UpdateAsync(model); return new Response { Result = flag, Message = flag == true ? "success" : "error" }; } /// /// 审核 （已根据权限修改） /// /// public async Task> PickUpAudit(PickUpAuditReq model) { var user = _auth.GetCurrentUser(); var level = user.Orgs.ToList(); if (level.Count == 0) { throw new Exception("暂无权限"); } else { var info = await base.Repository.AsQueryable().Where(R => R.Id == model.Id).FirstAsync(); var violat = await base.Repository.ChangeRepository>().AsQueryable().Where(R => R.Id == info.ViolationReportId).FirstAsync(); if (info != null) { using (var uow = base.UnitWork.CreateContext()) { var userlevel = level.Min(x => x.Level); if (userlevel == 1) //初审 { if (info.Status != 0) { throw new Exception("不符合初审条件"); } if (model.Isagree) { info.Status = 1; } else { info.Status = 2; } info.ReviewComments = model.ReviewComments; info.Reviewer = user.User.Id.ToString(); info.ReviewTime = DateTime.Now; info.ReviewerSignature = user.User.Signature; info.ReviewerName = user.User.Name; await uow.MiVehiclePickup.UpdateAsync(info); } else { if (userlevel == 0) { if (info.Status != 1) { throw new Exception("不符合复审条件"); } if (model.Isagree) { info.Status = 3; List vechileids = new List(); if (!string.IsNullOrEmpty(info.Vehicles)) { vechileids = info.Vehicles.Split(',').ToList(); var ve = uow.MiVehicle.AsQueryable() .Where(r => r.ViolationReportId == info.ViolationReportId && !vechileids.Contains(r.Id) && r.State == 0) .ToList(); if (ve.Count == 0) { violat.Status = 7; violat.StatusName = "已提车审核"; await uow.MiViolationReport.UpdateAsync(violat); } var vechileinfo = uow.MiVehicle.AsQueryable() .Where(r => vechileids.Contains(r.Id)).ToList(); vechileinfo.ForEach(r => { r.State = 2; }); await uow.MiVehicle.UpdateRangeAsync(vechileinfo); } } else { info.Status = 4; } info.SeReviewComments = model.ReviewComments; info.SeReviewer = user.User.Id.ToString(); info.SeReviewTime = DateTime.Now; info.SeReviewerSignature = user.User.Signature; info.SeReviewerName = user.User.Name; await uow.MiVehiclePickup.UpdateAsync(info); } else { throw new Exception("暂无权限"); } } var flag = uow.Commit(); return new Response { Result = flag, Message = flag == true ? "success" : "error" }; } } else { throw new Exception("数据丢失"); } } } /// /// 提车 /// /// /// /// public async Task> PickUpCar(PickupCar model) { var user = _auth.GetCurrentUser().User; var info = await base.Repository.AsQueryable().Where(R => R.Id == model.Id).FirstAsync(); //查询违法上报信息 var violat = await base.Repository.ChangeRepository>().AsQueryable().Where(R => R.Id == info.ViolationReportId).FirstAsync(); if (info != null) { using (var uow = base.UnitWork.CreateContext()) { //更新提车信息状态 info.Status = 5; await uow.MiVehiclePickup.UpdateAsync(info); //更新车辆信息状态 List vechileids = new List(); if (!string.IsNullOrEmpty(info.Vehicles)) { vechileids = info.Vehicles.Split(',').ToList(); var vechileinfo = uow.MiVehicle.AsQueryable().Where(r => vechileids.Contains(r.Id)).ToList(); vechileinfo.ForEach(r => { r.State = 3; }); await uow.MiVehicle.UpdateRangeAsync(vechileinfo); var ve = uow.MiVehicle.AsQueryable().Where(r => r.ViolationReportId == info.ViolationReportId && !vechileids.Contains(r.Id) && r.State != 2).ToList(); if (ve.Count == 0) { violat.Status = 8; //更新违法上报状态 violat.StatusName = "已提车"; await uow.MiViolationReport.UpdateAsync(violat); } } var flag = uow.Commit(); return new Response { Result = flag, Message = flag == true ? "success" : "error" }; } } else { throw new Exception("数据丢失"); } } /// /// 事务示例 /// /// /// public async Task> AssignModule() { using (var uwo = UnitWork.CreateContext()) { //await uwo.SysRoleElement.InsertRangeAsync(model.ElementIds.Select(a => new SysRoleElement { RoleId = model.RoleId, ElementId = a }).ToList()); var flag = uwo.Commit(); return new Response { Result = flag, Message = flag == true ? "success" : "error" }; } } #region 查询解除扣押单信息 /// /// 根据id查询解除扣押单信息 /// /// /// public async Task GetReleaseDocument(string pickupid) { using (var uow = UnitWork.CreateContext()) { var item = await uow.MiReleaseDocument.AsQueryable() .Where(p => p.PickUpId == pickupid) .LeftJoin((p, r) => p.ParkingLot == r.Id) .OrderByDescending(p => p.CreatedAt) .Select((p, r) => new { Id = p.Id.SelectAll(), ParkingName = r.Name, }).FirstAsync(); var result = new { item.Id, item.Year, Number = item.SerialNumber.ToString("D3"), // 格式化为 "002" item.Party, item.ClueLocation, item.ViolationType, item.ReleaseDate, item.PickupDeadline, item.Items, item.CreatedAt, item.CreatedBy, item.ViolationReportId, item.ParkingName, item.FilePath }; return result; } } /// /// 根据id查询解除扣押单信息 /// /// 违法上报id /// public async Task GetReleaseDocumentByReportId(string reportid) { using (var uow = UnitWork.CreateContext()) { var item = await uow.MiReleaseDocument.AsQueryable() .Where(p => p.ViolationReportId == reportid) .LeftJoin((p, r) => p.ParkingLot == r.Id) .OrderByDescending(p => p.CreatedAt) .Select((p, r) => new { Id = p.Id.SelectAll(), ParkingName = r.Name, }).FirstAsync(); var result = new { item.Id, item.Year, Number = item.SerialNumber.ToString("D3"), // 格式化为 "002" item.Party, item.ClueLocation, item.ViolationType, item.ReleaseDate, item.PickupDeadline, item.Items, item.CreatedAt, item.CreatedBy, item.ViolationReportId, item.ParkingName, item.FilePath }; return result; } } #endregion } }