/// <summary>
/// 查询全部绑定数据
/// </summary>
/// <param name="ent"></param>
/// <returns></returns>
public PageModel GetAllTemplate(FPiCiXQPlan request)
{
//var ret = Db.Context.From<TbShortMessageTemplate>().Select(
// TbSysMenu._.ID,
// TbSysMenu._.MenuCode,
// TbSysMenu._.MenuName,
// TbShortMessageTemplate._.TemplateCode,
// TbShortMessageTemplate._.TemplateContent,
// TbShortMessageTemplate._.TemplateType
// ).InnerJoin<TbSysMenu>((a, c) => a.TemplateCode == c.DuanXinTemplate).OrderByDescending(d => d.ID).ToPageList(ent.rows, ent.page);
//return ret;
//组装查询语句
#region 模糊搜索条件
var where = new Where <TbShortMessageTemplate>();
#endregion
try
{
var ret = Db.Context.From <TbShortMessageTemplate>()
.Select(TbShortMessageTemplate._.All)
.Where(where).OrderByDescending(d => d.ID).ToPageList(request.rows, request.page);
return(ret);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 新增明细对账弹窗
/// </summary>
/// <returns></returns>
public PageModel GetBalanceOfAccountOrder(FPiCiXQPlan request, string keyword)
{
string where = "";
StringBuilder sbSiteCode = new StringBuilder();
#region 数据权限新
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
where += " and a.ProcessFactoryCode='" + request.ProcessFactoryCode + "'";
}
//if (!string.IsNullOrWhiteSpace(request.ProjectId))
//{
// where += " and a.ProjectId='" + request.ProjectId + "'";
//}
if (!string.IsNullOrWhiteSpace(request.CXfbgqzd))
{
where += (" and a.SiteCode='" + request.CXfbgqzd + "'");
}
if (!string.IsNullOrWhiteSpace(keyword))
{
where += " and a.SigninNuber like '%" + keyword + "%'";
}
if (!string.IsNullOrWhiteSpace(request.KSdatetime))
{
where += ("and a.InsertTime>='" + request.KSdatetime + "'");
}
if (!string.IsNullOrWhiteSpace(request.JSdatetime))
{
where += ("and a.InsertTime<='" + request.JSdatetime + "'");
}
#endregion
string sql = @"select
a.SigninNuber,
a.InsertTime,
a.SignforTime,
a.ProjectId,
a.SiteCode,
a.ProcessFactoryCode,
sum(b.SteelWeight)/1000 as SteelWeight,
sum(b.GeGouZhongLiWeight)/1000 as GeGouZhongLiWeight,
sum(b.HSectionSteelWeight)/1000 as HSectionSteelWeight,
sum(b.AGrille)/1000 as AGrille
from TbSignforDuiZhang a
left join TbSignforDuiZhangDetail b on a.SigninNuber=b.SigninNuber
where a.SigninNuber not in(select BalanceOfAccountCode from TbSettlementOrderDetail) " + where + @"
group by a.SigninNuber,a.InsertTime,a.SignforTime, a.ProjectId,a.SiteCode,a.ProcessFactoryCode ";
//参数化
List <Dos.ORM.Parameter> para = new List <Dos.ORM.Parameter>();
var ret = Repository <TbSettlementOrder> .FromSqlToPageTable(sql, para, request.rows, request.page, "SigninNuber", "desc");
return(ret);
}
public HttpResponseMessage GetGridJson([FromUri] FPiCiXQPlan request)
{
try
{
var data = _deBus.GetAllOrBySearch(request);
return(AjaxResult.Success(data).ToJsonApi());
}
catch (Exception)
{
return(AjaxResult.Error("操作失败").ToJsonApi());
}
}
/// <summary>
/// 模板弹窗
/// </summary>
/// <param name="ent">分页</param>
/// <returns></returns>
public PageModel selectTemplate(FPiCiXQPlan ent, string keyword)
{
var where = new Where <TbShortMessageTemplate>();
if (!string.IsNullOrEmpty(keyword))
{
where.And(c => c.TemplateCode.Like(keyword));
}
var ret = Db.Context.From <TbShortMessageTemplate>().Select(
TbShortMessageTemplate._.All
).Where(where).OrderByDescending(d => d.ID).ToPageList(ent.rows, ent.page);
return(ret);
}
/// <summary>
/// 首页查询
/// </summary>
/// <param name="keyValue"></param>
/// <returns></returns>
public PageModel GetAllOrBySearch(FPiCiXQPlan entity)
{
//组装查询语句
#region 搜索条件
var where = new Where <TbPersonnelWorkDayConsume>();
if (!string.IsNullOrWhiteSpace(entity.ProcessFactoryCode))
{
where.And(p => p.ProcessFactoryCode == entity.ProcessFactoryCode);
}
if (!string.IsNullOrWhiteSpace(entity.OrderCode))
{
where.And(p => p.OrderCode.Like(entity.OrderCode));
}
if (!string.IsNullOrEmpty(entity.ProjectId))
{
where.And(p => p.ProjectId == entity.ProjectId);
}
#endregion
//数据权限
//var authorizaModel = new AuthorizeLogic().CheckAuthoriza(new AuthorizationParameterModel("PersonnelWorkDayConsume"));
//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));
//}
try
{
var ret = Db.Context.From <TbPersonnelWorkDayConsume>()
.Select(
TbPersonnelWorkDayConsume._.All
, TbCompany._.CompanyFullName.As("SiteName")
, TbUser._.UserName)
.LeftJoin <TbCompany>((a, c) => a.SiteCode == c.CompanyCode)
.AddSelect(Db.Context.From <TbCompany>().Select(p => p.CompanyFullName)
.Where(TbCompany._.CompanyCode == TbPersonnelWorkDayConsume._.ProcessFactoryCode), "ProcessFactoryName")
.LeftJoin <TbUser>((a, c) => a.InsertUserCode == c.UserCode)
.Where(where).OrderBy(d => d.ID).ToPageList(entity.rows, entity.page);
return(ret);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 模板弹窗
/// </summary>
/// <param name="ent">分页</param>
/// <returns></returns>
public ActionResult selectTemplate(FPiCiXQPlan ent, string keyword)
{
var data = _smtLogic.selectTemplate(ent, keyword);
return(Content(data.ToJson()));
}
/// <summary>
/// 图形3
/// </summary>
/// <param name="RebarType">钢筋类型</param>
/// <returns></returns>
public ActionResult Img3(FPiCiXQPlan ent)
{
var data = _fbnpBus.Img3(ent);
return(Content(data.ToJson()));
}
/// <summary>
/// 首页查询/点击查询
/// </summary>
/// <returns></returns>
public ActionResult GetMaterials(FPiCiXQPlan ent)
{
var data = _esLogic.GetMaterials(ent);
return(Content(data.ToJson()));
}
/// <summary>
/// 查询全部绑定短信模板数据
/// </summary>
/// <param name="ent"></param>
/// <returns></returns>
public ActionResult GetAllTemplate(FPiCiXQPlan ent)
{
var data = _smtLogic.GetAllTemplate(ent);
return(Content(data.ToJson()));
}
/// <summary>
/// 首页查询
/// </summary>
/// <param name="keyValue"></param>
/// <returns></returns>
public PageModel GetAllSettlement(FPiCiXQPlan request)
{
string where = " where 1=1 ";
StringBuilder sbSiteCode = new StringBuilder();
//组装查询语句
#region 数据权限新
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
where += " and a.ProcessFactoryCode='" + request.ProcessFactoryCode + "'";
}
//if (!string.IsNullOrWhiteSpace(request.ProjectId))
//{
// where += " and a.ProjectId='" + request.ProjectId + "'";
//}
if (!string.IsNullOrWhiteSpace(request.KSdatetime))
{
where += " and a.StartDate>='" + request.KSdatetime + "'";
}
if (!string.IsNullOrWhiteSpace(request.JSdatetime))
{
where += " and a.EndDate<='" + request.JSdatetime + "'";
}
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 a.SiteCode in(" + sbSiteCode + ")";
}
}
#endregion
try
{
var sql = @"select
a.*,c.CompanyFullName SiteName,d.CompanyFullName ProcessFactoryName,
e.UserName
from TbSettlementOrder a
left join TbCompany c on a.SiteCode=c.CompanyCode
left join TbCompany d on a.ProcessFactoryCode=d.CompanyCode
left join TbUser e on a.InsertUserCode=e.UserCode ";
//参数化
List <Dos.ORM.Parameter> para = new List <Dos.ORM.Parameter>();
var ret = Repository <TbSettlementOrder> .FromSqlToPageTable(sql + where, para, request.rows, request.page, "InsertTime", "desc");
return(ret);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取数据列表(分页)
/// </summary>
public DataTable GetExportList(FPiCiXQPlan request)
{
string where = " where 1=1 ";
StringBuilder sbSiteCode = new StringBuilder();
//组装查询语句
#region 数据权限新
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
where += " and a.ProcessFactoryCode='" + request.ProcessFactoryCode + "'";
}
if (!string.IsNullOrWhiteSpace(request.KSdatetime))
{
where += " and a.StartDate>='" + request.KSdatetime + "'";
}
if (!string.IsNullOrWhiteSpace(request.JSdatetime))
{
where += " and a.EndDate<='" + request.JSdatetime + "'";
}
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 a.SiteCode in(" + sbSiteCode + ")";
}
}
where += " order by a.InsertTime desc";
#endregion
try
{
var sql = @"select
a.*,c.CompanyFullName SiteName,d.CompanyFullName ProcessFactoryName,
e.UserName
from TbSettlementOrder a
left join TbCompany c on a.SiteCode=c.CompanyCode
left join TbCompany d on a.ProcessFactoryCode=d.CompanyCode
left join TbUser e on a.InsertUserCode=e.UserCode ";
var ret = Db.Context.FromSql(sql + where).ToDataTable();
return(ret);
}
catch (Exception)
{
throw;
}
}
public PageModel GetMaterials(FPiCiXQPlan ent)
{
string whereTb = " where 1=1 ";
string where = " ";
string where1 = " ";
if (!string.IsNullOrWhiteSpace(ent.SiteCode))
{
List <string> SiteList = workOrderLogic.GetCompanyWorkAreaOrSiteList(ent.SiteCode, 5); //站点
List <string> WorkAreaList = workOrderLogic.GetCompanyWorkAreaOrSiteList(ent.SiteCode, 4); //工区
string siteStr = string.Join("','", SiteList);
string workAreaStr = string.Join("','", WorkAreaList);
where += (" and (tb.SiteCode in('" + siteStr + "') or tb.WorkAreaCode in('" + workAreaStr + "'))");
where1 += (" and tb.SiteCode in('" + siteStr + "') ");
}
if (!string.IsNullOrWhiteSpace(ent.MaterialCode))
{
whereTb += " and Tb.MaterialCode='" + ent.MaterialCode + "'";
}
if (!string.IsNullOrWhiteSpace(ent.ProjectId))
{
whereTb += " and Tb.ProjectId='" + ent.ProjectId + "'";
}
if (!string.IsNullOrWhiteSpace(ent.ProcessFactoryCode))
{
whereTb += " and Tb.ProcessFactoryCode='" + ent.ProcessFactoryCode + "'";
}
if (!string.IsNullOrWhiteSpace(ent.SpecificationModel1))
{
whereTb += " and Tb.SpecificationModel='" + ent.SpecificationModel1 + "'";
}
if (!string.IsNullOrWhiteSpace(ent.MaterialNameSelect1))
{
whereTb += " and Tb.MaterialName='" + ent.MaterialNameSelect1 + "'";
}
string sql = @"select * from (select Tb1.*,isnull(ydxqjh.LjDemandNum,0) as LjDemandNum,isnull(LjBatchPlanQuantity,0) as LjBatchPlanQuantity,isnull(LjHasSupplier,0) as LjHasSupplier,isnull(jgdd.HistoryMonthCount,0) as HistoryMonthCount,isnull(dhrk.PassCount,0) as AcceptanceQuantity,isnull(yclll.WeightSmallPlanN,0) as WeightSmallPlan,(isnull(yclll.WeightSmallPlanN,0)-ISNULL(yclll.WeightSmallPlan,0)-isnull(ylk.yll,0)) as Loss,case when isnull(yclll.WeightSmallPlanN,0)<=0 or isnull(flk.StartAddUpHandleQuantity,0)+isnull(flk.UntreatedQuantity,0)<=0 then Convert(decimal(18,2),0) else Convert(decimal(18,2),((isnull(flk.StartAddUpHandleQuantity,0)+isnull(flk.UntreatedQuantity,0))*1.0/isnull(yclll.WeightSmallPlanN,0))*100) end as LossPC,(isnull(Tb1.InitialCount,0)+isnull(dhrk.PassCount,0)-isnull(yclll.WeightSmallPlanN,0)) as RawMaterialStockQuantity,isnull(yclll.WeightSmallPlan,0) as ComponentStockQuantity,isnull(flk.StartAddUpHandleQuantity,0) as StartAddUpHandleQuantity,isnull(flk.UntreatedQuantity,0) as UntreatedQuantity from (
select tb.MaterialCode,tb.MaterialName,tb.SpecificationModel,sdd.DictionaryText as MeasurementUnitName,sum(tb.Count) as InitialCount,tb.ProjectId,s.ProcessFactoryCode from TbRawMaterialStockRecord tb
left join TbRawMaterialArchives rma on tb.MaterialCode=rma.MaterialCode
left join TbSysDictionaryData sdd on rma.MeasurementUnit=sdd.DictionaryCode
left join TbStorage s on s.StorageCode=tb.StorageCode
where 1=1 and tb.ChackState=1 " + where + @"
group by tb.MaterialCode,tb.MaterialName,tb.SpecificationModel,sdd.DictionaryText,tb.ProjectId,s.ProcessFactoryCode) Tb1
left join(select sum(TbJh.LjDemandNum) as LjDemandNum,TbJh.MaterialCode,TbJh.ProjectId,TbJh.ProcessFactoryCode from(
select isnull(sum(DemandNum),0) as LjDemandNum,MaterialCode,tb.ProjectId,tb.ProcessFactoryCode from TbRawMaterialMonthDemandPlanDetail a
left join TbRawMaterialMonthDemandPlan tb on a.DemandPlanCode=tb.DemandPlanCode where tb.Examinestatus='审核完成' " + where + @"
group by MaterialCode,tb.ProjectId,tb.ProcessFactoryCode
union all
select isnull(sum(SupplyNum),0) as LjDemandNum,MaterialCode ,tb.ProjectId,tb.ProcessFactoryCode from TbRawMaterialMonthDemandSupplyPlanDetail a
left join TbRawMaterialMonthDemandSupplyPlan tb on a.SupplyPlanCode=tb.SupplyPlanCode where tb.Examinestatus='审核完成' " + where + @"
group by MaterialCode,tb.ProjectId,tb.ProcessFactoryCode) TbJh GROUP BY TbJh.MaterialCode,TbJh.ProjectId,TbJh.ProcessFactoryCode) ydxqjh on Tb1.MaterialCode=ydxqjh.MaterialCode and Tb1.ProjectId=ydxqjh.ProjectId and Tb1.ProcessFactoryCode=ydxqjh.ProcessFactoryCode
left join(select sum(BatchPlanQuantity) as LjBatchPlanQuantity,RawMaterialNum,tb.ProjectId,tb.ProcessFactoryCode from TbFactoryBatchNeedPlanItem a
left join TbFactoryBatchNeedPlan tb on a.BatchPlanNum=tb.BatchPlanNum where tb.Examinestatus='审核完成' " + where + @"
group by RawMaterialNum,tb.ProjectId,tb.ProcessFactoryCode) pcjh on pcjh.RawMaterialNum=Tb1.MaterialCode and pcjh.ProjectId=Tb1.ProjectId and pcjh.ProcessFactoryCode=Tb1.ProcessFactoryCode
left join(select sum(HasSupplier) as LjHasSupplier,RawMaterialNum,tb.ProjectId,tb.ProcessFactoryCode from TbSupplyListDetail a
left join TbSupplyList tb on a.BatchPlanNum=tb.BatchPlanNum where 1=1 " + where + @"
group by RawMaterialNum,tb.ProjectId,tb.ProcessFactoryCode) gh on gh.RawMaterialNum=Tb1.MaterialCode and gh.ProjectId=Tb1.ProjectId and gh.ProcessFactoryCode=Tb1.ProcessFactoryCode
left join(select sum(WeightSmallPlan) HistoryMonthCount,MaterialCode,tb.ProjectId,tb.ProcessFactoryCode from TbWorkOrderDetail a
left join TbWorkOrder tb on a.OrderCode=tb.OrderCode
where DaetailWorkStrat='加工完成' " + where1 + @"
group by MaterialCode,tb.ProjectId,tb.ProcessFactoryCode) jgdd on jgdd.MaterialCode=Tb1.MaterialCode and jgdd.ProjectId=Tb1.ProjectId and jgdd.ProcessFactoryCode=Tb1.ProcessFactoryCode
left join (select inoi.MaterialCode,isnull(sum(inoi.PassCount),0) as PassCount,tb.ProjectId,fbnp.ProcessFactoryCode from TbInOrder tb
left join TbInOrderItem inoi on tb.InOrderCode=inoi.InOrderCode
left join TbFactoryBatchNeedPlan fbnp on tb.BatchPlanCode=fbnp.BatchPlanNum
where 1=1 and tb.BatchPlanCode!='' and inoi.MaterialCode!='' and inoi.MaterialCode is not null " + where + @"
group by inoi.MaterialCode,tb.ProjectId,fbnp.ProcessFactoryCode) as dhrk on Tb1.MaterialCode=dhrk.MaterialCode and dhrk.ProjectId=Tb1.ProjectId and dhrk.ProcessFactoryCode=Tb1.ProcessFactoryCode
left join(select rpmd.MaterialCode,SUM(rpmd.WeightSmallPlan) as WeightSmallPlan,sum(rpmd.WeightSmallPlanN) as WeightSmallPlanN,tb.ProjectId,tb.ProcessFactoryCode from TbRMProductionMaterial tb
left join TbRMProductionMaterialDetail rpmd on tb.CollarCode=rpmd.CollarCode
where 1=1 " + where + @"
group by rpmd.MaterialCode,tb.ProjectId,tb.ProcessFactoryCode) as yclll on Tb1.MaterialCode=yclll.MaterialCode and yclll.ProjectId=Tb1.ProjectId and yclll.ProcessFactoryCode=Tb1.ProcessFactoryCode
left join (select tb.MaterialCode,SUM(tb.WeightYes) as StartAddUpHandleQuantity,SUM(tb.WeightNot) as UntreatedQuantity,tb.ProjectId,rm.ProcessFactoryCode from TbRubbishStock tb
left join TbRMProductionMaterial rm on tb.CollarCode=rm.CollarCode
where 1=1 " + where1 + @"
group by tb.MaterialCode,tb.ProjectId,rm.ProcessFactoryCode) as flk on flk.MaterialCode=Tb1.MaterialCode and flk.ProjectId=Tb1.ProjectId and flk.ProcessFactoryCode=Tb1.ProcessFactoryCode
left join (select tb.MaterialCode,SUM(tb.Weight) as yll,tb.ProjectId,tb.ProcessFactoryCode from TbCloutStock tb
left join TbRMProductionMaterial rm on tb.CollarCode=rm.CollarCode
where 1=1 and tb.SiteCode!='-1' " + where1 + @"
group by tb.MaterialCode,tb.ProjectId,tb.ProcessFactoryCode) as ylk on ylk.MaterialCode=Tb1.MaterialCode and ylk.ProjectId=Tb1.ProjectId and ylk.ProcessFactoryCode=Tb1.ProcessFactoryCode) Tb";
//参数化
List <Dos.ORM.Parameter> para = new List <Dos.ORM.Parameter>();
var data = Repository <TbRawMaterialStock> .FromSqlToPageTable(sql + whereTb, para, ent.rows, ent.page, "MaterialCode", "desc");
return(data);
}
/// <summary>
/// 配送签收单弹窗（系统新增）
/// </summary>
/// <returns></returns>
public ActionResult GetPSList(FPiCiXQPlan entity, string keyword)
{
var data = _sdzLogic.GetAllQianShou(entity, keyword);
return(Content(data.ToJson()));
}
/// <summary>
/// 获取所有列表信息
/// </summary>
/// <returns></returns>
public HttpResponseMessage fbnGetGridJson([FromUri] FPiCiXQPlan pt)
{
var data = _fbnPlan.GetAllOrBySearch(pt);
return(AjaxResult.Success(data).ToJsonApi());
}
/// <summary>
/// 新增明细对账弹窗
/// </summary>
/// <returns></returns>
public ActionResult GetBalanceOfAccountOrder(FPiCiXQPlan entity, string keyword)
{
var data = _soLogic.GetBalanceOfAccountOrder(entity, keyword);
return(Content(data.ToJson()));
}
/// <summary>
/// 供货质量问题统计
/// </summary>
/// <returns></returns>
public HttpResponseMessage fbnImg3([FromUri] FPiCiXQPlan pt)
{
var data = _fbnPlan.Img3(pt);
return(AjaxResult.Success(data).ToJsonApi());
}
/// <summary>
/// 全部查询/条件查询
/// </summary>
/// <param name="ent"></param>
/// <returns></returns>
public ActionResult GetAllSignforDuiZhang(FPiCiXQPlan ent)
{
var data = _sdzLogic.GetAllSignforDuiZhang(ent);
return(Content(data.ToJson()));
}
/// <summary>
/// 配送签收单弹窗（手动新增）
/// </summary>
/// <returns></returns>
public ActionResult GetJgWcWpsList(FPiCiXQPlan entity, string keyword, string SigninNuber, string DzOrderCode)
{
var data = _sdzLogic.GetJgWcWpsList(entity, keyword, SigninNuber, DzOrderCode);
return(Content(data.ToJson()));
}
/// <summary>
/// 首页查询
/// </summary>
/// <param name="keyValue"></param>
/// <returns></returns>
public ActionResult GetAllOrBySearch(FPiCiXQPlan ent)
{
var data = _fbnpBus.GetAllOrBySearch(ent);
return(Content(data.ToJson()));
}
/// <summary>
/// 签收单弹窗
/// </summary>
/// <returns></returns>
public PageModel GetAllQianShou(FPiCiXQPlan entity, string keyword)
{
StringBuilder sb = new StringBuilder();
sb.Append(" where 1=1 and TbQsdMx.OrderCode is not null and TbQsdMx.SN not in(select SignForNo from TbSignforDuiZhangDetail) ");
if (!string.IsNullOrWhiteSpace(keyword))
{
sb.Append(" and (TbQsdMx.SN like '%" + keyword + "%' or Tb.DistributionCode like '%" + keyword + "%' )");
}
if (!string.IsNullOrWhiteSpace(entity.ProjectId))
{
sb.Append(" and TbQsdMx.ProjectId='" + entity.ProjectId + "'");
}
if (!string.IsNullOrWhiteSpace(entity.ProcessFactoryCode))
{
sb.Append(" and TbQsdMx.ProcessFactoryCode='" + entity.ProcessFactoryCode + "'");
}
if (!string.IsNullOrWhiteSpace(entity.CXfbgqzd))
{
sb.Append(" and TbQsdMx.SiteCode='" + entity.CXfbgqzd + "'");
}
if (!string.IsNullOrWhiteSpace(entity.KSdatetime))
{
sb.Append("and TbQsdMx.SigninTime>='" + entity.KSdatetime + "'");
}
if (!string.IsNullOrWhiteSpace(entity.JSdatetime))
{
sb.Append("and TbQsdMx.SigninTime<='" + entity.JSdatetime + "'");
}
try
{
//参数化
string sql = @"select TbQsdMx.SN,TbQsdMx.OrderCode,TbQsdMx.SiteCode,TbQsdMx.SiteName,TbQsdMx.DistributionCode,TbQsdMx.ProjectId,TbQsdMx.ProcessFactoryCode,TbQsdMx.SigninTime,
cast(isnull(TbQsdMx.SteelWeight,0)/1000 as decimal(18,5)) as Rebar,
cast(isnull(TbQsdMx.GeGouZhongLiWeight,0)/1000 as decimal(18,5)) as MiddlePillar,
cast(isnull(TbQsdMx.HSectionSteelWeight,0)/1000 as decimal(18,5)) as HSectionSteel,
cast(isnull(TbQsdMx.AGrille,0)/1000 as decimal(18,5)) as AGrille,
cast((isnull(TbQsdMx.SteelWeight,0)+isnull(TbQsdMx.GeGouZhongLiWeight,0)+isnull(TbQsdMx.HSectionSteelWeight,0)+isnull(TbQsdMx.AGrille,0))/1000 as decimal(18,5)) as GratingWeight
from
(
select Tb.*,
case WHEN Tb.OrderCodeH IS NOT NULL THEN
(
SELECT sum(WeightSmallPlan) AS disWeight FROM TbWorkOrderDeliveryOverItem twodoi
WHERE twodoi.OrderCode= Tb.OrderCode and twodoi.ProcessingTechnology=2
)
ELSE
(
select sum(isnull(dei.Number,0)*isnull
(dei.UnitWeight,0)*isnull(dei.SingletonWeight,0)) as disWeight
from TbSiteDischargeCargo sdc
left join TbSiteDischargeCargoDetail sdcd on sdc.DischargeCargoCode=sdcd.DischargeCargoCode
left join TbDistributionEntItem dei on sdcd.DisEntOrderItemId=dei.ID
where dei.DisEntOrderIdentity is not null and dei.ProcessingTechnology=2 and sdcd.DischargeCargoCode=Tb.DischargeCargoCode
) end as SteelWeight,--类型1
case WHEN Tb.OrderCodeH IS NOT NULL THEN
(
SELECT sum(WeightSmallPlan) AS disWeight FROM TbWorkOrderDeliveryOverItem twodoi
WHERE twodoi.OrderCode= Tb.OrderCode and twodoi.ProcessingTechnology=4
)
ELSE
(
select sum(isnull(dei.Number,0)*isnull
(dei.UnitWeight,0)*isnull(dei.SingletonWeight,0)) as disWeight
from TbSiteDischargeCargo sdc
left join TbSiteDischargeCargoDetail sdcd on sdc.DischargeCargoCode=sdcd.DischargeCargoCode
left join TbDistributionEntItem dei on sdcd.DisEntOrderItemId=dei.ID
where dei.DisEntOrderIdentity is not null and dei.ProcessingTechnology=4 and sdcd.DischargeCargoCode=Tb.DischargeCargoCode
) end as GeGouZhongLiWeight,--类型2
case WHEN Tb.OrderCodeH IS NOT NULL THEN
(
SELECT sum(WeightSmallPlan) AS disWeight FROM TbWorkOrderDeliveryOverItem twodoi
WHERE twodoi.OrderCode= Tb.OrderCode and twodoi.ProcessingTechnology in(6,7,8,9,10)
)
ELSE
(
select sum(isnull(dei.Number,0)*isnull
(dei.UnitWeight,0)*isnull(dei.SingletonWeight,0)) as disWeight
from TbSiteDischargeCargo sdc
left join TbSiteDischargeCargoDetail sdcd on sdc.DischargeCargoCode=sdcd.DischargeCargoCode
left join TbDistributionEntItem dei on sdcd.DisEntOrderItemId=dei.ID
where dei.DisEntOrderIdentity is not null and dei.ProcessingTechnology in(6,7,8,9,10) and sdcd.DischargeCargoCode=Tb.DischargeCargoCode
) end as AGrille,--类型3
case WHEN Tb.OrderCodeH IS NOT NULL THEN
(
SELECT sum(WeightSmallPlan) AS disWeight FROM TbWorkOrderDeliveryOverItem twodoi
WHERE twodoi.OrderCode= Tb.OrderCode and twodoi.ProcessingTechnology in(12,13,14,15)
)
ELSE
(
select sum(isnull(dei.Number,0)*isnull
(dei.UnitWeight,0)*isnull(dei.SingletonWeight,0)) as disWeight
from TbSiteDischargeCargo sdc
left join TbSiteDischargeCargoDetail sdcd on sdc.DischargeCargoCode=sdcd.DischargeCargoCode
left join TbDistributionEntItem dei on sdcd.DisEntOrderItemId=dei.ID
where dei.DisEntOrderIdentity is not null
and dei.ProcessingTechnology in(12,13,14,15)
and sdcd.DischargeCargoCode=Tb.DischargeCargoCode
) end as HSectionSteelWeight--类型4
from
(select sfs.ID,sfs.OrderCodeH,ISNULL(sfs.OrderCodeH,sdc.OrderCode) AS OrderCode,
sfs.SigninNuber as SN,cp.CompanyFullName as SiteName,
sfs.SigninTime,sfs.DistributionCode,sfs.DischargeCargoCode,sfs.ProjectId,sfs.SiteCode,sfs.ProcessFactoryCode
from TbSemiFinishedSign sfs
left join TbCompany cp on cp.CompanyCode=sfs.SiteCode
left join TbSiteDischargeCargo sdc on sfs.DischargeCargoCode=sdc.DischargeCargoCode
) Tb
) TbQsdMx";
List <Dos.ORM.Parameter> para = new List <Dos.ORM.Parameter>();
var ret = Repository <TbSemiFinishedSign> .FromSqlToPageTable(sql + sb.ToString(), para, entity.rows, entity.page, "SN", "desc");
return(ret);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 全部查询/条件查询
/// </summary>
/// <param name="ent"></param>
/// <returns></returns>
public ActionResult GetAllSettlement(FPiCiXQPlan ent)
{
var data = _soLogic.GetAllSettlement(ent);
return(Content(data.ToJson()));
}
/// <summary>
/// 签收单弹窗
/// </summary>
/// <returns></returns>
public PageModel GetJgWcWpsList(FPiCiXQPlan entity, string keyword, string SigninNuber, string OrderCode)
{
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrWhiteSpace(keyword))
{
sb.Append(" and (Tb.OrderCode like '%" + keyword + "%' or Tb.TypeCode like '%" + keyword + "%' or Tb.TypeName like '%" + keyword + "%' )");
}
if (!string.IsNullOrWhiteSpace(entity.ProjectId))
{
sb.Append(" and Tb.ProjectId='" + entity.ProjectId + "'");
}
if (!string.IsNullOrWhiteSpace(entity.ProcessFactoryCode))
{
sb.Append(" and Tb.ProcessFactoryCode='" + entity.ProcessFactoryCode + "'");
}
if (!string.IsNullOrWhiteSpace(entity.CXfbgqzd))
{
sb.Append(" and Tb.SiteCode='" + entity.CXfbgqzd + "'");
}
if (!string.IsNullOrEmpty(OrderCode))
{
sb.Append(" and Tb.OrderCode not in(" + OrderCode + ")");
}
try
{
//参数化
string sql = @"select * from(select Tb1.*,isnull(Tb2.SteelWeight,0) as SteelWeight,isnull(Tb3.GeGouZhongLiWeight,0) as GeGouZhongLiWeight,isnull(Tb4.AGrille,0) as AGrille,isnull(Tb5.HSectionSteelWeight,0) as HSectionSteelWeight,isnull(Tb2.SteelWeight,0)+isnull(Tb3.GeGouZhongLiWeight,0)+isnull(Tb4.AGrille,0)+isnull(Tb5.HSectionSteelWeight,0) as GratingWeight from (
select wo.OrderCode,wo.TypeCode,wo.TypeName,wo.ProjectId,wo.ProcessFactoryCode,wo.ProcessingState,wo.SiteCode,wo.InsertTime from TbWorkOrder wo
left join TbWorkOrderDetail wod on wo.OrderCode=wod.OrderCode
left join (select entOrderItem.OrderCode,entOrderItem.WorkorderdetailId,sum(entOrderItem.Number) as PSNumber from TbDistributionEntOrder entOrder
left join TbDistributionEntItem entOrderItem on entOrder.DisEntOrderIdentity=entOrderItem.DisEntOrderIdentity where entOrderItem.OrderCode is not null group by entOrderItem.OrderCode,entOrderItem.WorkorderdetailId ) TbPs
on wo.OrderCode=TbPs.OrderCode and wod.id=TbPs.WorkorderdetailId where (wod.Number-isnull(TbPs.PSNumber,0))>0 and wod.RevokeStart='正常'
group by wo.OrderCode,wo.TypeCode,wo.TypeName,wo.ProjectId,wo.ProcessFactoryCode,wo.ProcessingState,wo.SiteCode,wo.InsertTime) Tb1
left join (select wod.OrderCode,cast(sum(wod.MeasurementUnitZl*wod.ItemUseNum*(wod.Number-isnull(TbPs.PSNumber,0)))/1000 as decimal(18,5)) as SteelWeight from TbWorkOrder wo
left join TbWorkOrderDetail wod on wo.OrderCode=wod.OrderCode
left join (select entOrderItem.OrderCode,entOrderItem.WorkorderdetailId,sum(entOrderItem.Number) as PSNumber from TbDistributionEntOrder entOrder
left join TbDistributionEntItem entOrderItem on entOrder.DisEntOrderIdentity=entOrderItem.DisEntOrderIdentity where entOrderItem.OrderCode is not null group by entOrderItem.OrderCode,entOrderItem.WorkorderdetailId ) TbPs
on wo.OrderCode=TbPs.OrderCode and wod.id=TbPs.WorkorderdetailId where (wod.Number-isnull(TbPs.PSNumber,0))>0 and wod.RevokeStart='正常' and wod.ProcessingTechnology=2 group by wod.OrderCode) Tb2 on Tb1.OrderCode=Tb2.OrderCode
left join (select wod.OrderCode,cast(sum(wod.MeasurementUnitZl*wod.ItemUseNum*(wod.Number-isnull(TbPs.PSNumber,0)))/1000 as decimal(18,5)) as GeGouZhongLiWeight from TbWorkOrder wo
left join TbWorkOrderDetail wod on wo.OrderCode=wod.OrderCode
left join (select entOrderItem.OrderCode,entOrderItem.WorkorderdetailId,sum(entOrderItem.Number) as PSNumber from TbDistributionEntOrder entOrder
left join TbDistributionEntItem entOrderItem on entOrder.DisEntOrderIdentity=entOrderItem.DisEntOrderIdentity where entOrderItem.OrderCode is not null group by entOrderItem.OrderCode,entOrderItem.WorkorderdetailId ) TbPs
on wo.OrderCode=TbPs.OrderCode and wod.id=TbPs.WorkorderdetailId where (wod.Number-isnull(TbPs.PSNumber,0))>0 and wod.RevokeStart='正常' and wod.ProcessingTechnology=4 group by wod.OrderCode) Tb3 on Tb1.OrderCode=Tb3.OrderCode
left join (select wod.OrderCode,cast(sum(wod.MeasurementUnitZl*wod.ItemUseNum*(wod.Number-isnull(TbPs.PSNumber,0)))/1000 as decimal(18,5)) as AGrille from TbWorkOrder wo
left join TbWorkOrderDetail wod on wo.OrderCode=wod.OrderCode
left join (select entOrderItem.OrderCode,entOrderItem.WorkorderdetailId,sum(entOrderItem.Number) as PSNumber from TbDistributionEntOrder entOrder
left join TbDistributionEntItem entOrderItem on entOrder.DisEntOrderIdentity=entOrderItem.DisEntOrderIdentity where entOrderItem.OrderCode is not null group by entOrderItem.OrderCode,entOrderItem.WorkorderdetailId ) TbPs
on wo.OrderCode=TbPs.OrderCode and wod.id=TbPs.WorkorderdetailId where (wod.Number-isnull(TbPs.PSNumber,0))>0 and wod.RevokeStart='正常' and wod.ProcessingTechnology in(6,7,8,9,10) group by wod.OrderCode) Tb4 on Tb1.OrderCode=Tb4.OrderCode
left join (select wod.OrderCode,cast(sum(wod.MeasurementUnitZl*wod.ItemUseNum*(wod.Number-isnull(TbPs.PSNumber,0)))/1000 as decimal(18,5)) as HSectionSteelWeight from TbWorkOrder wo
left join TbWorkOrderDetail wod on wo.OrderCode=wod.OrderCode
left join (select entOrderItem.OrderCode,entOrderItem.WorkorderdetailId,sum(entOrderItem.Number) as PSNumber from TbDistributionEntOrder entOrder
left join TbDistributionEntItem entOrderItem on entOrder.DisEntOrderIdentity=entOrderItem.DisEntOrderIdentity where entOrderItem.OrderCode is not null group by entOrderItem.OrderCode,entOrderItem.WorkorderdetailId ) TbPs
on wo.OrderCode=TbPs.OrderCode and wod.id=TbPs.WorkorderdetailId where (wod.Number-isnull(TbPs.PSNumber,0))>0 and wod.RevokeStart='正常' and wod.ProcessingTechnology in(12,13,14,15) group by wod.OrderCode) Tb5 on Tb1.OrderCode=Tb5.OrderCode) Tb
where 1=1 and Tb.ProcessingState='Finishing' and Tb.OrderCode not in(select OrderCode from TbSignforDuiZhangDetail where AddType='手动填报' and SigninNuber!='" + SigninNuber + "') ";
List <Dos.ORM.Parameter> para = new List <Dos.ORM.Parameter>();
var ret = Repository <TbWorkOrder> .FromSqlToPageTable(sql + sb.ToString(), para, entity.rows, entity.page, "OrderCode", "desc");
return(ret);
}
catch (Exception)
{
throw;
}
}