/// <summary>
///
/// 供应清单 实体=加工厂批次需求计划
/// </summary>
/// <param name="keyValue"></param>
/// <returns></returns>
///
public PageModel GetAllOrBySearch(TSupplyListRequest entity)
{
//组装查询语句
#region 模糊搜索条件
List <Parameter> parameter = new List <Parameter>();
string where = " where 1=1";
if (!string.IsNullOrWhiteSpace(entity.ProcessFactoryCode))
{
where += " and a.ProcessFactoryCode=@ProcessFactoryCode";
parameter.Add(new Parameter("@ProcessFactoryCode", entity.ProcessFactoryCode, DbType.String, null));
}
if (!string.IsNullOrWhiteSpace(entity.SiteCode))
{
List <string> SiteList = _workOrderLogic.GetCompanyWorkAreaOrSiteList(entity.SiteCode, 5); //站点
List <string> WorkAreaList = _workOrderLogic.GetCompanyWorkAreaOrSiteList(entity.SiteCode, 4); //工区
where += " and (a.SiteCode in('" + string.Join("','", SiteList) + "') or a.WorkAreaCode in('" + string.Join("','", WorkAreaList) + "'))";
}
if (!string.IsNullOrWhiteSpace(entity.ProjectId))
{
where += " and a.ProjectId=@ProjectId";
parameter.Add(new Parameter("@ProjectId", entity.ProjectId, DbType.String, null));
}
if (!string.IsNullOrWhiteSpace(entity.BatchPlanNum))
{
where += " and a.BatchPlanNum like '%" + entity.BatchPlanNum + "%'";
}
if (entity.HistoryMonth.HasValue)
{
where += " and YEAR(a.InsertTime)=" + entity.HistoryMonth.Value.Year + " and MONTH(a.InsertTime)=" + entity.HistoryMonth.Value.Month;
}
if (!string.IsNullOrWhiteSpace(entity.StateCode))
{
if (entity.StateCode == "供货超时")
{
where += " and a.SupplyDate<GETDATE() and a.StateCode='未供货'";
}
else if (entity.StateCode == "未供货")
{
where += " and a.SupplyDate>=GETDATE() and a.StateCode='未供货'";
}
else
{
where += " and a.StateCode='" + entity.StateCode + "'";
}
}
if (!string.IsNullOrWhiteSpace(entity.DemandPlanCode))
{
where += " and( a.RawMaterialDemandNum='" + entity.DemandPlanCode + "' or i.DemandPlanCode='" +
entity.DemandPlanCode + "')";
}
#endregion
string sql = @"SELECT
a.*,
b.CompanyFullName AS BranchName,
c.CompanyFullName AS WorkAreaName,
d.CompanyFullName AS ProcessFactoryName,
e.UserName,
f.UserName AS AcceptorName,
g.DictionaryText AS RebarTypeNew,
h.UnqualifiedTotal,
case when i.DemandPlanCode is null then a.RawMaterialDemandNum else i.DemandPlanCode end as DemandPlanCode,
gh.GhCount
FROM
TbSupplyList a
left join (select COUNT(1) as GhCount,Tb.BatchPlanNum from (select a.ThisTime,b.BatchPlanNum,SUM(a.ThisTimeCount) as ThisTimeCount from TbSupplyListDetailHistory a
left join (select a.BatchPlanNum,case when b.DemandPlanCode is null then a.RawMaterialDemandNum else b.DemandPlanCode end as DemandPlanCode from TbFactoryBatchNeedPlan a
left join TbRawMaterialMonthDemandSupplyPlan b on a.RawMaterialDemandNum=b.SupplyPlanCode) b on a.BatchPlanNum=b.BatchPlanNum
group by a.ThisTime,b.BatchPlanNum) Tb group by Tb.BatchPlanNum) gh on a.BatchPlanNum=gh.BatchPlanNum
LEFT JOIN TbCompany b ON a.BranchCode=b.CompanyCode
LEFT JOIN TbCompany c ON a.WorkAreaCode=c.CompanyCode
LEFT JOIN TbCompany d ON a.ProcessFactoryCode=d.CompanyCode
LEFT JOIN TbUser e ON a.InsertUserCode=e.UserCode
LEFT JOIN TbUser f ON a.Acceptor=f.UserCode
LEFT JOIN TbSysDictionaryData g ON a.SteelsTypeCode=g.DictionaryCode
LEFT JOIN
(
SELECT ISNULL(SUM(bhgData.PassCount),0) AS UnqualifiedTotal,BatchPlanNum FROM TbFactoryBatchNeedPlanItem tfbnpi
LEFT JOIN(
SELECT tioi.PassCount,tioi.BatchPlanItemId
FROM TbInOrderItem tioi WHERE tioi.ChackState=3
) bhgData ON tfbnpi.ID=bhgData.BatchPlanItemId
GROUP BY tfbnpi.BatchPlanNum
) h ON a.BatchPlanNum=h.BatchPlanNum
left join TbRawMaterialMonthDemandSupplyPlan i on a.RawMaterialDemandNum=i.SupplyPlanCode
";
try
{
var data = Repository <TbSupplyList> .FromSqlToPageTable(sql + where, parameter, entity.rows, entity.page, "BatchPlanNum", "desc");
return(data);
}
catch (Exception)
{
throw;
}
}
public ActionResult GetAllOrBySearch(TSupplyListRequest entity)
{
var data = _suplistLogic.GetAllOrBySearch(entity);
return(Content(data.ToJson()));
}
/// <summary>
///
/// 供应清单 实体=加工厂批次需求计划 导出
/// </summary>
/// <param name="keyValue"></param>
/// <returns></returns>
///
public DataTable GetExportList(TSupplyListRequest entity)
{
//组装查询语句
#region 模糊搜索条件
List <Parameter> parameter = new List <Parameter>();
string where = " where 1=1";
if (!string.IsNullOrWhiteSpace(entity.ProcessFactoryCode))
{
where += " and a.ProcessFactoryCode=@ProcessFactoryCode";
parameter.Add(new Parameter("@ProcessFactoryCode", entity.ProcessFactoryCode, DbType.String, null));
}
if (!string.IsNullOrWhiteSpace(entity.SiteCode))
{
List <string> SiteList = _workOrderLogic.GetCompanyWorkAreaOrSiteList(entity.SiteCode, 5); //站点
List <string> WorkAreaList = _workOrderLogic.GetCompanyWorkAreaOrSiteList(entity.SiteCode, 4); //工区
where += " and (a.SiteCode in('" + string.Join("','", SiteList) + "') or a.WorkAreaCode in('" + string.Join("','", WorkAreaList) + "'))";
}
if (!string.IsNullOrWhiteSpace(entity.ProjectId))
{
where += " and a.ProjectId=@ProjectId";
parameter.Add(new Parameter("@ProjectId", entity.ProjectId, DbType.String, null));
}
if (!string.IsNullOrWhiteSpace(entity.BatchPlanNum))
{
where += " and a.BatchPlanNum=@BatchPlanNum";
parameter.Add(new Parameter("@BatchPlanNum", entity.BatchPlanNum, DbType.String, null));
}
if (entity.HistoryMonth.HasValue)
{
where += " and YEAR(a.InsertTime)=" + entity.HistoryMonth.Value.Year + " and MONTH(a.InsertTime)=" + entity.HistoryMonth.Value.Month;
}
if (!string.IsNullOrWhiteSpace(entity.StateCode))
{
if (entity.StateCode == "供货超时")
{
where += " and a.SupplyDate<GETDATE() and a.StateCode='未供货'";
}
else if (entity.StateCode == "未供货")
{
where += " and a.SupplyDate>=GETDATE() and a.StateCode='未供货'";
}
else
{
where += " and a.StateCode='" + entity.StateCode + "'";
}
}
//排序
where += " order by BatchPlanNum desc";
#endregion
string sql = @"SELECT
a.*,
b.CompanyFullName AS BranchName,
c.CompanyFullName AS WorkAreaName,
d.CompanyFullName AS ProcessFactoryName,
e.UserName,
f.UserName AS AcceptorName,
g.DictionaryText AS RebarTypeNew,
h.UnqualifiedTotal
FROM
TbSupplyList a
LEFT JOIN TbCompany b ON a.BranchCode=b.CompanyCode
LEFT JOIN TbCompany c ON a.WorkAreaCode=c.CompanyCode
LEFT JOIN TbCompany d ON a.ProcessFactoryCode=d.CompanyCode
LEFT JOIN TbUser e ON a.InsertUserCode=e.UserCode
LEFT JOIN TbUser f ON a.Acceptor=f.UserCode
LEFT JOIN TbSysDictionaryData g ON a.SteelsTypeCode=g.DictionaryCode
LEFT JOIN
(
SELECT ISNULL(SUM(bhgData.PassCount),0) AS UnqualifiedTotal,BatchPlanNum FROM TbFactoryBatchNeedPlanItem tfbnpi
LEFT JOIN(
SELECT tioi.PassCount,tioi.BatchPlanItemId
FROM TbInOrderItem tioi WHERE tioi.ChackState=3
) bhgData ON tfbnpi.ID=bhgData.BatchPlanItemId
GROUP BY tfbnpi.BatchPlanNum
) h ON a.BatchPlanNum=h.BatchPlanNum";
try
{
var data = Db.Context.FromSql(sql + where)
.AddInParameter("ProcessFactoryCode", DbType.String, entity.ProcessFactoryCode)
.AddInParameter("ProjectId", DbType.String, entity.ProjectId)
.AddInParameter("BatchPlanNum", DbType.String, entity.BatchPlanNum).ToDataTable();
return(data);
}
catch (Exception)
{
throw;
}
}
