public IActionResult Query([FromBody] PlanManageDto parm)
{
//开始拼装查询条件
var predicate = Expressionable.Create <view_ItemPlanVM>();
//switch(parm.status)
//{
// case 0:
// predicate = predicate.And(m => m.status == 30000);
// break;
// case 1:
// predicate = predicate.And(m => m.status == 30010);
// break;
// case 3:
// predicate = predicate.And(m => m.status == 30020);
// break;
// case 4:
// predicate = predicate.And(m => m.status == 39999);
// break;
// default:
// return toResponse(StatusCodeType.Error, "状态匹配失败,请核对！");
// //break;
//}
//项目编号
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.ContractItemNo), m => m.ContractItemNo.Contains(parm.ContractItemNo));
//客户名称
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.khmc), m => m.zzmc.Contains(parm.khmc));
//合作伙伴
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.hzhb), m => m.mc.Contains(parm.hzhb));
//审核开始起
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.AcceptStartDateStart), m => m.PlanStartDate >= DateTime.Parse(parm.AcceptStartDateStart));
//审核开始止
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.AcceptStartDateEnd), m => m.PlanStartDate <= DateTime.Parse(parm.AcceptStartDateEnd));
//审核结束起
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.AcceptEndDateStart), m => m.PlanEndDate >= DateTime.Parse(parm.AcceptEndDateStart));
//审核结束止
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.AcceptEndDateEnd), m => m.PlanEndDate <= DateTime.Parse(parm.AcceptEndDateEnd));
//var response = _contractitemsubService.GetPages(predicate.ToExpression(), parm);
var response = Core.DbContext.CurrentDB.SqlQueryable <view_ItemPlanVM>(@"
SELECT m.ID, m.ContractNo, m.CustomerID, m.CreateTime, k.zzmc, k.xzqhmc, h.mc, s.ID AS ContractItemSubID, s.MainContractID,
s.ContractItemID, s.ContractItemSubType, s.ContractItemSubTypeCode, c.ItemName, c.ContractItemNo,p.ContractPlanNo,
p.PlanStartDate,p.PlanEndDate,p.ApproveTime,
dbo.GetNodeName(p.status) as StatusName
FROM dbo.Biz_ContractItem_Sub AS s
inner join Biz_Contract_Plan p on s.ContractPlanID = p.ID
INNER JOIN
dbo.Biz_MainContract AS m ON m.ID = s.MainContractID INNER JOIN
dbo.Biz_ContractItem AS c ON c.ID = s.ContractItemID LEFT OUTER JOIN
dbo.uf_khxx AS k ON k.ID = m.CustomerID LEFT OUTER JOIN
dbo.uf_hzhb AS h ON k.hzdwID = h.ID
WHERE (m.deleted = 0)
").ToPage <view_ItemPlanVM>(predicate.ToExpression(), parm);
return(toResponse(response));
}
public IActionResult Query([FromBody] PlanManageDto parm)
{
//开始拼装查询条件
var predicate = Expressionable.Create <view_PlanVM>();
//switch(parm.status)
//{
// case 0:
// predicate = predicate.And(m => m.status == 30000);
// break;
// case 1:
// predicate = predicate.And(m => m.status == 30010);
// break;
// case 3:
// predicate = predicate.And(m => m.status == 30020);
// break;
// case 4:
// predicate = predicate.And(m => m.status == 39999);
// break;
// default:
// return toResponse(StatusCodeType.Error, "状态匹配失败,请核对！");
// //break;
//}
predicate = predicate.And(m => m.status == 30000); //待派人状态
//项目编号
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.ContractItemNo), m => m.ContractItemNos.Contains(parm.ContractItemNo));
//客户名称
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.khmc), m => m.zzmc.Contains(parm.khmc));
//合作伙伴
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.hzhb), m => m.mc.Contains(parm.hzhb));
//审核开始起
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.AcceptStartDateStart), m => m.PlanStartDate >= DateTime.Parse(parm.AcceptStartDateStart));
//审核开始止
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.AcceptStartDateEnd), m => m.PlanStartDate <= DateTime.Parse(parm.AcceptStartDateEnd));
//审核结束起
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.AcceptEndDateStart), m => m.PlanEndDate >= DateTime.Parse(parm.AcceptEndDateStart));
//审核结束止
predicate = predicate.AndIF(!string.IsNullOrEmpty(parm.AcceptEndDateEnd), m => m.PlanEndDate <= DateTime.Parse(parm.AcceptEndDateEnd));
//var response = _contractitemsubService.GetPages(predicate.ToExpression(), parm);
var response = Core.DbContext.CurrentDB.SqlQueryable <view_PlanVM>(@"
SELECT p.*, k.zzmc, h.mc,
[dbo].[GetNodeName](p.status) AS StatusName, stuff
((SELECT ' ' + ItemName + ':'
FROM Biz_ContractItem AS t
inner join Biz_ContractItem_Sub s on s.ContractItemID = t.ID
WHERE s .ContractPlanID = p.ID AND t .deleted = 0 FOR xml path('')), 1, 2, '') AS StandardNos, stuff
((SELECT ' ' + ContractItemNo
FROM Biz_ContractItem AS t
inner join Biz_ContractItem_Sub s on s.ContractItemID = t.ID
WHERE s .ContractPlanID = p.ID AND t .deleted = 0 FOR xml path('')), 1, 2, '') AS ContractNos, stuff
((SELECT ' ' + AuditTypeName
FROM Biz_ContractItem AS t
inner join Biz_ContractItem_Sub s on s.ContractItemID = t.ID
WHERE s .ContractPlanID = p.ID AND t .deleted = 0 FOR xml path('')), 1, 2, '') AS AuditTypeNames
FROM Biz_Contract_Plan p LEFT JOIN
uf_khxx k ON k.ID = p.CustomerID LEFT JOIN
uf_hzhb h ON k.hzdwID = h.ID
").ToPage <view_PlanVM>(predicate.ToExpression(), parm);
return(toResponse(response));
}
