public HttpResponseMessage GetGridJson([FromUri] SemiFinishedSignRequest request)
{
try
{
var data = _wasRFLogic.GetDataListForPage(request);
return(AjaxResult.Success(data).ToJsonApi());
}
catch (Exception)
{
return(AjaxResult.Error("操作失败").ToJsonApi());
}
}
/// <summary>
/// 获取数据列表(分页)
/// </summary>
public PageModel GetDataListForPage(SemiFinishedSignRequest request)
{
string where = " where 1=1 ";
StringBuilder sbSiteCode = new StringBuilder();
#region 数据权限新
if (!string.IsNullOrWhiteSpace(request.SigninNuber))
{
where += " and Tb.SigninNuber like '%" + request.SigninNuber + "%'";
}
if (!string.IsNullOrWhiteSpace(request.DischargeCargoCode))
{
where += " and Tb.DistributionCode like '%" + request.DischargeCargoCode + "%'";
}
if (!string.IsNullOrWhiteSpace(request.SigninTime))
{
where += " and Tb.SigninTime=" + Convert.ToDateTime(request.SigninTime) + "";
}
if (!string.IsNullOrWhiteSpace(request.OperateState))
{
where += " and Tb.OperateState='" + request.OperateState + "'";
}
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
where += " and Tb.ProcessFactoryCode='" + request.ProcessFactoryCode + "'";
}
if (!string.IsNullOrWhiteSpace(request.ProjectId))
{
where += " and Tb.ProjectId='" + request.ProjectId + "'";
}
if (!string.IsNullOrWhiteSpace(request.CarCph))
{
where += " and Tb.CarCph like '%" + request.CarCph + "%'";
}
if (!string.IsNullOrWhiteSpace(request.OrderCode))
{
where += " and (Tb.OrderCode like '%" + request.OrderCode + "%' or Tb.OrderCodeH like '%" + request.OrderCode + "%')";
}
if (!string.IsNullOrWhiteSpace(request.TypeCode))
{
where += " and Tb.TypeCode like '%" + request.TypeCode + "%'";
}
if (!string.IsNullOrWhiteSpace(request.SiteCode))
{
List <string> SiteList = _workOrderLogic.GetCompanyWorkAreaOrSiteList(request.SiteCode, 5);
for (int i = 0; i < SiteList.Count; i++)
{
if (i == (SiteList.Count - 1))
{
sbSiteCode.Append("'" + SiteList[i] + "'");
}
else
{
sbSiteCode.Append("'" + SiteList[i] + "',");
}
}
if (SiteList.Count > 0)
{
where += " and Tb.SiteCode in(" + sbSiteCode + ")";
}
}
#endregion
string sql = @"select * from (select sfs.*,case when sfs.DistributionCode is not null and sfs.DistributionCode!='' then '系统配送' else '线下登记' end DistributionType,sdc.OrderCode,ur1.UserName as CarUser,ur2.UserName as ContactsName,cp1.CompanyFullName as SiteName,cp2.CompanyFullName as ProcessFactoryName,car.CarCph
,
(
stuff((select ',' + CONVERT(varchar(100),DeliveryCompleteTime, 23) from TbDistributionPlanInfo
where CHARINDEX(OrderCode,sdc.OrderCode)>0 OR OrderCode=sfs.OrderCodeH
for xml path('')),1,1,'')
) AS DeliveryCompleteTime
from TbSemiFinishedSign sfs
left join (select sdc.DischargeCargoCode,case when sdc.OrderCode is null then dis.OrderCode else sdc.OrderCode end OrderCode from TbSiteDischargeCargo sdc
left join TbDistributionEnt dis on sdc.DistributionCode=dis.DistributionCode) sdc on sfs.DischargeCargoCode=sdc.DischargeCargoCode
left join TbUser ur1 on sfs.Driver=ur1.UserCode
left join TbUser ur2 on sfs.Contacts=ur2.UserCode
left join TbCarInfo car on sfs.VehicleCode=car.CarCode
left join TbCompany cp1 on sfs.SiteCode=cp1.CompanyCode
left join TbCompany cp2 on sfs.ProcessFactoryCode=cp2.CompanyCode) Tb ";
//参数化
List <Parameter> parameter = new List <Parameter>();
var model = Repository <TbSemiFinishedSign> .FromSqlToPageTable(sql + where, parameter, request.rows, request.page, "SigninNuber", "desc");
return(model);
////组装查询语句
//#region 模糊搜索条件
//var where = new Where<TbSemiFinishedSign>();
//if (!string.IsNullOrWhiteSpace(request.SigninTime))
//{
// DateTime sigTime = Convert.ToDateTime(request.SigninTime);
// where.And(p => p.SigninTime == sigTime);
//}
//if (!string.IsNullOrWhiteSpace(request.SiteCode))
//{
// List<string> SiteList = _workOrderLogic.GetCompanyWorkAreaOrSiteList(request.SiteCode, 5);
// where.And(p => p.SiteCode.In(SiteList));
//}
//if (!string.IsNullOrWhiteSpace(request.VehicleCode))
//{
// where.And(p => p.VehicleCode == request.VehicleCode);
//}
//if (!string.IsNullOrWhiteSpace(request.SigninNuber))
//{
// where.And(p => p.SigninNuber.StartsWith(request.SigninNuber));
//}
//#endregion
//#region 数据权限
////数据权限
//var authorizaModel = new AuthorizeLogic().CheckAuthoriza(new AuthorizationParameterModel() { FormCode = "SemiFinishedSign", UserCode = request.UserCode });
//if (authorizaModel.IsAuthorize)
//{
// if (authorizaModel.Ids.Count > 0 && authorizaModel.UserCodes.Count > 0)
// where.And(d => d.InsertUserCode.In(authorizaModel.UserCodes) || d.ID.In(authorizaModel.Ids));
// else if (authorizaModel.Ids.Count > 0)
// where.Or(d => d.ID.In(authorizaModel.Ids));
// else if (authorizaModel.UserCodes.Count > 0)
// where.And(d => d.InsertUserCode.In(authorizaModel.UserCodes));
//}
//if (!string.IsNullOrEmpty(request.ProjectId))
// where.And(p => p.ProjectId == request.ProjectId);
//#endregion
//try
//{
// var ret = Db.Context.From<TbSemiFinishedSign>()
// .Select(
// TbSemiFinishedSign._.All
// , TbCompany._.CompanyFullName.As("SiteName")
// , TbDistributionEntOrder._.OrderCode
// , TbUser._.UserName
// , TbCarInfo._.CarCph)
// .LeftJoin<TbCompany>((a, c) => a.SiteCode == c.CompanyCode)
// .AddSelect(Db.Context.From<TbCompany>().Select(p => p.CompanyFullName)
// .Where(TbCompany._.CompanyCode == TbSemiFinishedSign._.ProcessFactoryCode), "ProcessFactoryName")
// .AddSelect(Db.Context.From<TbUser>().Select(p => p.UserName)
// .Where(TbUser._.UserCode == TbSemiFinishedSign._.Contacts), "ContactsName")
// .AddSelect(Db.Context.From<TbUser>().Select(p => p.UserName)
// .Where(TbUser._.UserCode == TbSemiFinishedSign._.Driver), "CarUser")
// .LeftJoin<TbDistributionEntOrder>((a, c) => a.DistributionCode == c.DistributionCode)
// .LeftJoin<TbUser>((a, c) => a.InsertUserCode == c.UserCode)
// .LeftJoin<TbCarInfo>((a, c) => a.VehicleCode == c.CarCode)
// .Where(where).OrderBy(d => d.ID).ToPageList(request.rows, request.page);
// return ret;
//}
//catch (Exception e)
//{
// throw e;
//}
}
/// <summary>
/// 数据列表信息查询
/// </summary>
/// <returns></returns>
public DataTable GetExportList(SemiFinishedSignRequest request)
{
string where = " where 1=1 ";
StringBuilder sbSiteCode = new StringBuilder();
#region 数据权限新
if (!string.IsNullOrWhiteSpace(request.SigninNuber))
{
where += " and Tb.SigninNuber like '%" + request.SigninNuber + "%'";
}
if (!string.IsNullOrWhiteSpace(request.DischargeCargoCode))
{
where += " and Tb.DistributionCode like '%" + request.DischargeCargoCode + "%'";
}
if (!string.IsNullOrWhiteSpace(request.SigninTime))
{
where += " and Tb.SigninTime=" + Convert.ToDateTime(request.SigninTime) + "";
}
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
where += " and Tb.ProcessFactoryCode='" + request.ProcessFactoryCode + "'";
}
if (!string.IsNullOrWhiteSpace(request.ProjectId))
{
where += " and Tb.ProjectId='" + request.ProjectId + "'";
}
if (!string.IsNullOrWhiteSpace(request.CarCph))
{
where += " and Tb.CarCph like '%" + request.CarCph + "%'";
}
if (!string.IsNullOrWhiteSpace(request.OrderCode))
{
where += " and (Tb.OrderCode like '%" + request.OrderCode + "%' or Tb.OrderCodeH like '%" + request.OrderCode + "%')";
}
if (!string.IsNullOrWhiteSpace(request.TypeCode))
{
where += " and Tb.TypeCode like '%" + request.TypeCode + "%'";
}
if (!string.IsNullOrWhiteSpace(request.SiteCode))
{
List <string> SiteList = _workOrderLogic.GetCompanyWorkAreaOrSiteList(request.SiteCode, 5);
for (int i = 0; i < SiteList.Count; i++)
{
if (i == (SiteList.Count - 1))
{
sbSiteCode.Append("'" + SiteList[i] + "'");
}
else
{
sbSiteCode.Append("'" + SiteList[i] + "',");
}
}
if (SiteList.Count > 0)
{
where += " and Tb.SiteCode in(" + sbSiteCode + ")";
}
}
where += " order by Tb.SigninNuber desc";
#endregion
string sql = @"select * from (select sfs.*,sdc.OrderCode,ur1.UserName as CarUser,ur2.UserName as ContactsName,cp1.CompanyFullName as SiteName,cp2.CompanyFullName as ProcessFactoryName,car.CarCph
,
(
stuff((select ',' + CONVERT(varchar(100),DeliveryCompleteTime, 23) from TbDistributionPlanInfo
where CHARINDEX(OrderCode,sdc.OrderCode)>0 OR OrderCode=sfs.OrderCodeH
for xml path('')),1,1,'')
) AS DeliveryCompleteTime
from TbSemiFinishedSign sfs
left join (select sdc.DischargeCargoCode,case when sdc.OrderCode is null then dis.OrderCode else sdc.OrderCode end OrderCode from TbSiteDischargeCargo sdc
left join TbDistributionEnt dis on sdc.DistributionCode=dis.DistributionCode) sdc on sfs.DischargeCargoCode=sdc.DischargeCargoCode
left join TbUser ur1 on sfs.Driver=ur1.UserCode
left join TbUser ur2 on sfs.Contacts=ur2.UserCode
left join TbCarInfo car on sfs.VehicleCode=car.CarCode
left join TbCompany cp1 on sfs.SiteCode=cp1.CompanyCode
left join TbCompany cp2 on sfs.ProcessFactoryCode=cp2.CompanyCode) Tb ";
var model = Db.Context.FromSql(sql + where).ToDataTable();
return(model);
}
/// <summary>
/// 获取分页列表数据
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public ActionResult GetGridJson(SemiFinishedSignRequest request)
{
var data = _wasRFLogic.GetDataListForPage(request);
return(Content(data.ToJson()));
}