public IQueryable <ContractInfo> SearchConditionFiltration(ContractSearchRequest condition, IQueryable <ContractInfo> query)
{
//查询主键信息
if (!string.IsNullOrEmpty(condition.KeyWord))
{
query = query.Where(x => x.Name.Contains(condition.KeyWord) ||
x.Num.Contains(condition.KeyWord) ||
x.ProjectName.Contains(condition.KeyWord)
);
}
if (condition?.ProjectTypes?.Count > 0)
{
query = query.Where(x => condition.ProjectTypes.Contains(x.ProjectType));
}
if (condition?.ProjectTypes?.Count > 0)
{
query = query.Where(x => condition.ContractTypes.Contains(x.Type));
}
if (condition.CheckStatu > 0)
{
//query = from
}
if (condition.Discard == 1)
{
query = query.Where(x => x.IsDelete);//这个后面考虑
}
if (condition.OverTime == 1)//过期和时间限定分开
{
query = query.Where(x => x.EndTime < DateTime.Now);
}
if (condition?.CreateDateStart != null)
{
query = query.Where(x => (x.CreateTime >= condition.CreateDateStart.Value));
}
if (condition?.CreateDateEnd != null)
{
query = query.Where(x => (x.EndTime >= x.CreateTime.Value));
}
if (condition.Follow == 1)
{
query = query.Where(x => x.IsFollow.Value);
}
if (condition?.OrderRule == 0 || condition?.OrderRule == null)
{
query = query.OrderByDescending(x => x.StartTime);
}
else
{
query = query.OrderBy(x => x.Num);
}
return(query);
}
[HttpGet, Route("searchReport"), AllowAnonymous]//ShuttleClaimsAuthorize(ActionCode = ConstantsSecurity.ActionCode.WeeklyReportSearch)
public HttpResponseMessage SearchReport([FromUri] ContractSearchRequest search)
{
var response = Request.CreateResponse();
response.Content = new PushStreamContent((outputStream, httpContent, transportContext)
=> _contractApplicationService.GenerateDataCsvToReportExcel(outputStream, httpContent, transportContext, search), new MediaTypeHeaderValue("text/csv"));
response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = "contractReportDTOs.csv",
DispositionType = "inline"
};
return(response);
}
public async Task <ContractSearchResponse <ContractInfoResponse> > SearchContractList(UserInfo User, [FromBody] ContractSearchRequest condition)
{
if (User.Id == null)
{
{
User.Id = "66df64cb-67c5-4645-904f-704ff92b3e81";
User.UserName = "******";
User.KeyWord = "";
User.OrganizationId = "270";
User.PhoneNumber = "18122132334";
};
}
Logger.Trace($"用户{User?.UserName ?? ""}({User?.Id ?? ""})查询合同条件(condition)：\r\n请求参数为：\r\n" + (condition != null ? JsonHelper.ToJson(condition) : ""));
var pagingResponse = new ContractSearchResponse <ContractInfoResponse>();
if (!ModelState.IsValid)
{
pagingResponse.Code = ResponseCodeDefines.ModelStateInvalid;
Logger.Warn($"用户{User?.UserName ?? ""}({User?.Id ?? ""})查询合同条件(PostCustomerListSaleMan)模型验证失败：\r\n{pagingResponse.Message ?? ""}，\r\n请求参数为：\r\n" + (condition != null ? JsonHelper.ToJson(condition) : ""));
return(pagingResponse);
}
try
{
//if (await _permissionExpansionManager.HavePermission(User.Id, "SEARCH_CONTRACT"))
//{
// pagingResponse = await _contractListManager.SearchContract(User, condition, HttpContext.RequestAborted);
pagingResponse = await _contractListManager.SearchContract2(User, condition, HttpContext.RequestAborted);
//}
//else
//{
// pagingResponse.Code = ResponseCodeDefines.NotAllow;
// pagingResponse.Message = "权限不足";
//}
}
catch (Exception e)
{
pagingResponse.Code = ResponseCodeDefines.ServiceError;
pagingResponse.Message = "服务器错误:" + e.ToString();
Logger.Error($"用户{User?.UserName ?? ""}({User?.Id ?? ""})查询业务员条件(PostCustomerListSaleMan)请求失败：\r\n{pagingResponse.Message ?? ""}，\r\n请求参数为：\r\n" + (condition != null ? JsonHelper.ToJson(condition) : ""));
}
return(pagingResponse);
}
public async Task <ResponseDTO <PagedList <ContractSearchDTO> > > Search([FromUri] ContractSearchRequest search)
{
var resp = await _contractApplicationService.SearchContractAsync(search);
return(resp);
}
public ContractSearchRequest GetSearchRequest()
{
var searchRequest = new ContractSearchRequest
{
SearchContractStatusType = StatusType,
SearchValue = SearchValue,
SearchType = SearchType,
SearchStatus = ContractStatus,
User = Users.Current
};
return searchRequest;
}
public void Search_for_contract_using_phone_nummber()
{
// Setup mock behaviour
IList<Contract> contracts = new List<Contract>();
contracts.Add(new Contract(1, DateTime.Now) { AccountId = 1, ContractNumber = "CT9999", ContractStatus = ContractStatus.Active, PhoneNumber1 = "093233904", PhoneNumber2 = "0339430449" });
_contractRepository
.Expect(c => c.FindByPhoneNumber("0339430449"))
.Returns(contracts);
_accountRepository
.Expect(a => a.GetAccount(1))
.Returns(new MasterAccount(1) { AccountName = "Test Account", AccountNumber = "TST0001", ContactId = 1, CompanyId = 1 });
_contactRepository
.Expect(c => c.GetContactEntity(1))
.Returns(new Contact(1) { Name = "Test Contact" });
_companyRepository
.Expect(c => c.GetCompany(1))
.Returns(new Company(1) { CompanyName = "Test Company" });
User user = new User() { Username = "******", IsActive = true, Email = "*****@*****.**", IsAuthenticated = true, Name = "Test" };
ContractSearchRequest request = new ContractSearchRequest()
{
SearchValue = "0339430449",
SearchType = ContractSearchField.PhoneNumber,
SearchStatus = ContractStatus.Active,
SearchContractStatusType = SearchContractStatusType.UseStatus,
User = user
};
ContractSearchResponse response = _service.FindContracts(request);
Assert.IsNotNull(response, "Response is null");
Assert.IsTrue(response.IsSuccessful, "Response is not successful");
Assert.IsNotNull(response.Contracts, "Contracts is null");
Assert.AreEqual(1, response.Contracts.Count, "Contacts count");
Assert.AreEqual("0339430449", response.Contracts[0].PhoneNumber, "Phone Number");
}
public void FindContractsByContractNumberTest()
{
// Setup mock behaviour
IList<Contract> contracts = new List<Contract>();
contracts.Add(new Contract(1, DateTime.Now) { AccountId = 1, ContractNumber = "CT9999", ContractStatus = ContractStatus.Active });
User user = new User() { Username = "******", IsActive = true, Email = "*****@*****.**", IsAuthenticated = true, Name = "Test" };
ContractSearchRequest request = new ContractSearchRequest()
{
SearchValue = "CT",
SearchType = ContractSearchField.ContractNumber,
SearchStatus = ContractStatus.Active,
SearchContractStatusType = SearchContractStatusType.UseStatus,
User = user
};
ContractSearchResponse response = service.FindContracts(request);
Assert.IsNotNull(response, "Response is null");
Assert.IsTrue(response.IsSuccessful, "Response is not successful");
Assert.IsNotNull(response.Contracts, "Contracts is null");
Assert.AreEqual(1, response.Contracts.Count, "Contacts count");
}
/*EXCEL REPORT*/
public async Task GenerateDataCsvToReportExcel(Stream outputStream, HttpContent httpContent,
TransportContext transportContext, ContractSearchRequest search)
{
var list = await SearchContractAsync(search);
try
{
if (list.Data.Items.Count > 0)
{
using (var writer = new StreamWriter(outputStream))
{
var headers = new List <string>
{
"Contract Code",
"Period",
"Tenant",
"Property",
"Start",
"Finish",
"Deposit",
"Rent",
"Unpaid periods",
"Due Date",
"Days to collect"
};
await writer.WriteLineAsync(ExcelHelper.GetHeaderDetail(headers));
foreach (var item in list.Data.Items)
{
await writer.WriteLineAsync(ProcessCellDataToReport(item));
}
}
}
}
catch (HttpException ex)
{
}
finally
{
outputStream.Close();
}
}
public async Task <ResponseDTO <PagedList <ContractSearchDTO> > > SearchContractAsync(ContractSearchRequest search)
{
List <OrderExpression <ContractSearchDTO> > orderExpressionList = new List <OrderExpression <ContractSearchDTO> >();
orderExpressionList.Add(new OrderExpression <ContractSearchDTO>(OrderType.Asc, p => p.ContractCode));
Expression <Func <ContractSearchDTO, bool> > queryFilter = c => true;
if (search.BeginDate.HasValue && search.EndDate.HasValue)
{
var toPlusADay = search.EndDate.Value.AddDays(1);
queryFilter = queryFilter.And(p => p.CreationDate.Value >= search.BeginDate);
queryFilter = queryFilter.And(p => p.CreationDate.Value < toPlusADay);
}
else if (search.BeginDate.HasValue && !search.EndDate.HasValue)
{
queryFilter = queryFilter.And(p => p.CreationDate.Value >= search.BeginDate);
}
else if (!search.BeginDate.HasValue && search.EndDate.HasValue)
{
var toPlusADay = search.EndDate.Value.AddDays(1);
queryFilter = queryFilter.And(p => p.CreationDate.Value < toPlusADay);
}
if (!string.IsNullOrEmpty(search.ContractCode))
{
queryFilter = queryFilter.And(p => p.ContractCode.Contains(search.ContractCode));
}
if (!string.IsNullOrEmpty(search.TenantFullName))
{
queryFilter = queryFilter.And(p => p.TenantFullName.Contains(search.TenantFullName));
}
if (search.NextDaysToCollect > 0 && search.NextDaysToCollect == 1)
{
queryFilter = queryFilter.And(p => p.NextDaysToCollect >= 0 && p.NextDaysToCollect <= 5);
}
if (search.NextDaysToCollect > 0 && search.NextDaysToCollect == 2)
{
queryFilter = queryFilter.And(p => p.NextDaysToCollect >= 6 && p.NextDaysToCollect <= 10);
}
if (search.NextDaysToCollect > 0 && search.NextDaysToCollect == 3)
{
queryFilter = queryFilter.And(p => p.NextDaysToCollect >= 11 && p.NextDaysToCollect <= 15);
}
if (search.ContractStatusId.HasValue)
{
queryFilter = queryFilter.And(p => p.ContractStatusId == search.ContractStatusId);
}
if (search.HouseId.HasValue)
{
queryFilter = queryFilter.And(p => p.HouseId == search.HouseId);
}
if (search.UnpaidPeriods == "Y")
{
queryFilter = queryFilter.And(p => p.UnpaidPeriods > 0);
}
if (search.UnpaidPeriods == "N")
{
queryFilter = queryFilter.And(p => p.UnpaidPeriods == 0);
}
if (search.PeriodId.HasValue)
{
queryFilter = queryFilter.And(p => p.PeriodId == search.PeriodId);
}
var contract = await _contractSearchDataAccess.ListPagedAsync(queryFilter, search.Page, search.PageSize, orderExpressionList.ToArray());
var pagedResult = new PagedList <ContractSearchDTO>()
{
Items = contract.Items,
PageSize = contract.PageSize,
Page = contract.Page,
Total = contract.Total
};
return(ResponseBuilder.Correct(pagedResult));
}
public virtual async Task <ContractSearchResponse <ContractInfoResponse> > SearchContract2(UserInfo user, ContractSearchRequest condition, CancellationToken cancellationToken = default(CancellationToken))
{
if (condition == null)
{
throw new ArgumentNullException(nameof(condition));
}
var pagingResponse = new ContractSearchResponse <ContractInfoResponse>();
var query = _icontractInfoStore.ContractInfoAll().Where(a => true);
query = SearchConditionFiltration(condition, query);
var organsPer = await _permissionExpansionManager.GetOrganizationOfPermission(user.Id, "ContractSearchOrg");
//查询部门
if (!string.IsNullOrEmpty(condition.Organizate))
{
if (organsPer.Contains(condition.Organizate))//包含在此权限的范围内
{
var organs = await _permissionExpansionManager.GetLowerDepartments(condition.Organizate);
query = query.Where(x => organs.Contains(x.OrganizateID));
}
else
{
query = query.Where(x => organsPer.Contains(x.OrganizateID));
}
}
else
{
query = query.Where(x => organsPer.Contains(x.OrganizateID));
}
if (condition.CheckStatu > 0)
{
int nCheckState = 0;
// if ((condition.CheckStatu & 0x01) > 0)//1 2 4 8 未提交 审核中 通过 驳回
// {
// nCheckState = (condition.CheckStatu & 0x01);
// }
// if ((condition.CheckStatu & 0x02) > 0)
// {
// nCheckState = (condition.CheckStatu & 0x02);
// }
// if ((condition.CheckStatu & 0x04) > 0)
// {
// nCheckState = (condition.CheckStatu & 0x04);
// }
// if ((condition.CheckStatu & 0x08) > 0)
// {
// nCheckState = (condition.CheckStatu & 0x08);
// }
query = query.Where(x => (x.ExamineStatus.Value & condition.CheckStatu) > 0);
}
if (condition.pageIndex == -1)
{
var qlist = await query.ToListAsync(cancellationToken);
foreach (var item in qlist)
{
item.Organizate = _iorganizationExpansionStore.GetFullName(item.OrganizateID).Replace("默认顶级-", "");
item.CreateDepartment = _iorganizationExpansionStore.GetFullName(item.OrganizateID).Replace("默认顶级-", "");
List <string> fullId = await _permissionExpansionManager.GetParentDepartments(item.OrganizateID);
fullId.Remove("0");
item.OrganizateFullId = string.Join("*", fullId.ToArray());
}
pagingResponse.TotalCount = qlist.Count;
pagingResponse.ValidityContractCount = qlist.Count;
pagingResponse.PageIndex = condition.pageIndex;
pagingResponse.PageSize = condition.pageSize;
pagingResponse.Extension = _mapper.Map <List <ContractInfoResponse> >(qlist);
}
else
{
pagingResponse.TotalCount = await query.CountAsync(cancellationToken);
pagingResponse.ValidityContractCount = pagingResponse.TotalCount;
//需要加上排序
var qlist = await query.Skip(condition.pageIndex *condition.pageSize).Take(condition.pageSize).ToListAsync(cancellationToken);
foreach (var item in qlist)
{
item.Organizate = _iorganizationExpansionStore.GetFullName(item.OrganizateID).Replace("默认顶级-", "");
item.CreateDepartment = _iorganizationExpansionStore.GetFullName(item.OrganizateID).Replace("默认顶级-", "");
List <string> fullId = await _permissionExpansionManager.GetParentDepartments(item.OrganizateID);
fullId.Remove("0");
item.OrganizateFullId = string.Join("*", fullId.ToArray());
}
// qlist.Select(async x =>
// {
// x.Organizate = _iorganizationExpansionStore.GetFullName(x.OrganizateID);
// x.CreateDepartment = _iorganizationExpansionStore.GetFullName(x.OrganizateID).Replace("默认顶级-", "");
// List<string> fullId = await _permissionExpansionManager.GetParentDepartments(x.OrganizateID);
// fullId.Remove("0");
// x.OrganizateFullId = string.Join("*", fullId.ToArray());
// return x;
// });
pagingResponse.PageIndex = condition.pageIndex;
pagingResponse.PageSize = condition.pageSize;
pagingResponse.Extension = _mapper.Map <List <ContractInfoResponse> >(qlist);
}
return(pagingResponse);
}
public virtual async Task <ContractSearchResponse <ContractInfoResponse> > SearchContract(UserInfo user, ContractSearchRequest condition, CancellationToken cancellationToken = default(CancellationToken))
{
if (condition == null)
{
throw new ArgumentNullException(nameof(condition));
}
var Response = new ContractSearchResponse <ContractInfoResponse>();
var sql = @"SELECT a.* from XYH_DT_CONTRACTINFO as a where";
if (condition?.CheckStatu > 0)
{
sql = @"SELECT a.* from XYH_DT_CONTRACTINFO as a LEFT JOIN XYH_DT_MODIFY as b ON a.`CurrentModify`=b.`ID` where";
}
string connectstr = " ";
if (!string.IsNullOrEmpty(condition?.KeyWord))
{
sql += connectstr + @"LOCATE('" + condition.KeyWord + "', a.`Name`)>0";
connectstr = " and ";
}
else if (condition?.KeyWord != null)
{
sql += connectstr + @"a.`ID`!=''";
connectstr = " and ";
}
if (condition?.Discard == 1)
{
sql += connectstr + @"a.IsDelete";
connectstr = " and ";
}
if (condition?.OverTime == 1)//过期和时间限定分开
{
sql += connectstr + @"a.`EndTime`<=CURTIME()";
connectstr = " and ";
}
else if (condition?.CreateDateStart != null && condition?.CreateDateEnd != null)
{
sql += connectstr + @"(a.`StartTime`<='" + condition.CreateDateStart.Value + "'";
connectstr = " and ";
sql += connectstr + @"a.`EndTime`>='" + condition.CreateDateEnd.Value + "')";
}
if (condition?.Follow == 1)
{
sql += connectstr + @"a.`Follow`!=''";
connectstr = " and ";
}
if (condition?.CheckStatu > 0)
{
string head = "(", tail = ")";
if ((condition?.CheckStatu & 0x01) > 0)//1 2 4 8 未提交 审核中 通过 驳回
{
sql += connectstr + head + @"b.`ExamineStatus`=0";
connectstr = " or ";
head = "";
}
if ((condition?.CheckStatu & 0x02) > 0)
{
sql += connectstr + head + @"b.`ExamineStatus`=1";
connectstr = " or ";
head = "";
}
if ((condition?.CheckStatu & 0x04) > 0)
{
sql += connectstr + head + @"b.`ExamineStatus`=8";
connectstr = " or ";
head = "";
}
if ((condition?.CheckStatu & 0x08) > 0)
{
sql += connectstr + head + @"b.`ExamineStatus`=16";
connectstr = " or ";
head = "";
}
sql += tail;
connectstr = " and ";
}
if (!string.IsNullOrEmpty(condition?.Organizate))
{
sql += connectstr + @"a.`OrganizateID`='" + condition.Organizate + "'";
connectstr = " and ";
}
if (condition?.OrderRule == 0 || condition?.OrderRule == null)
{
sql += @" ORDER BY a.`StartTime`";
}
else if (condition?.OrderRule == 1)
{
sql += @" ORDER BY a.`ID`";
}
var query = _icontractInfoStore.DapperSelect <ContractInfo>(sql).ToList();
Response.ValidityContractCount = query.Count;
Response.TotalCount = query.Count;
List <ContractInfo> result = new List <ContractInfo>();
var begin = (condition.pageIndex) * condition.pageSize;
var end = (begin + condition.pageSize) > query.Count ? query.Count : (begin + condition.pageSize);
for (; begin < end; begin++)
{
result.Add(query.ElementAt(begin));
}
Response.PageIndex = condition.pageIndex;
Response.PageSize = condition.pageSize;
Response.Extension = _mapper.Map <List <ContractInfoResponse> >(result);
foreach (var item in Response.Extension)
{
item.ExamineStatus = 0;
if (item.CurrentModify != null)
{
var moinf = await _icontractInfoStore.GetModifyAsync(a => a.Where(b => b.ID == item.CurrentModify));
if (moinf != null)
{
item.ExamineStatus = moinf.ExamineStatus;
}
}
}
return(Response);
//sql += @" limit " + condition.pageIndex * condition.pageSize + "," + condition.pageSize + "";
//var query = _icontractInfoStore.DapperSelect<ContractInfo>(sql).ToList();
//Response.ValidityContractCount = query.Count;
//Response.PageIndex = condition.pageIndex;
//Response.PageSize = condition.pageSize;
//Response.Extension = _mapper.Map<List<ContractInfoResponse>>(query);
//foreach (var item in Response.Extension)
//{
// item.ExamineStatus = 0;
// if (item.CurrentModify != null)
// {
// var moinf = await _icontractInfoStore.GetModifyAsync(a => a.Where(b => b.ID == item.CurrentModify));
// if (moinf != null)
// {
// item.ExamineStatus = moinf.ExamineStatus;
// }
// }
//}
//return Response;
}
