public Tuple <DataTable, DataTable> GetProgressForm(OrderProgressRequest request)
{
DataTable dt1 = GetScProgress(request);
DataTable dt2 = GetDdProgress(request);
return(new Tuple <DataTable, DataTable>(dt1, dt2));
}
/// <summary>
/// 获取生产进度展示报表数据
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public DataTable GetScProgress(OrderProgressRequest request)
{
StringBuilder sbSiteCode = new StringBuilder();
string sqlChilde = "";
string sqlwhere = "";
if (!string.IsNullOrWhiteSpace(request.TypeName))
{
sqlwhere += " and op.TypeName like '%" + request.TypeName + "%' ";
}
if (!string.IsNullOrWhiteSpace(request.OrderCode))
{
sqlwhere += " and op.OrderCode like '%" + request.OrderCode + "%' ";
}
if (request.HistoryMonth.HasValue)
{
string InsertTime = Convert.ToDateTime(request.HistoryMonth).ToString("yyyy-MM");
sqlwhere += " and CONVERT(VARCHAR(7),op.InsertTime,120)='" + InsertTime + "'";
}
#region 数据权限
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
sqlwhere += "and ProcessFactoryCode='" + request.ProcessFactoryCode + "'";
}
if (!string.IsNullOrWhiteSpace(request.ProjectId))
{
sqlwhere += "and ProjectId='" + request.ProjectId + "'";
}
if (!string.IsNullOrWhiteSpace(request.SiteCode))
{
List <string> SiteList = orderProLogic.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)
{
sqlwhere += " and SiteCode in(" + sbSiteCode + ")";
}
}
#endregion
sqlChilde = @"select op.SiteCode,cp.CompanyFullName as SiteName,op.ProcessFactoryCode,SUM(op.WeightTotal) as SumWeightTotal,SUM(AccumulativeQuantity) as JgWcWeightTotal,(SUM(op.WeightTotal)-SUM(AccumulativeQuantity)) as JgWwcWeightTotal from TbOrderProgress op
left join TbCompany cp on op.SiteCode=cp.CompanyCode
where 1=1 " + sqlwhere + @"
group by op.SiteCode,cp.CompanyFullName,op.ProcessFactoryCode order by op.SiteCode asc";
var dt = Db.Context.FromSql(sqlChilde).ToDataTable();
return(dt);
}
/// <summary>
/// 获取数据列表(分页)
/// </summary>
public PageModel GetDataListForPage(OrderProgressRequest request)
{
//组装查询语句
#region 模糊搜索条件
var where = new Where <TbOrderProgress>();
if (!string.IsNullOrWhiteSpace(request.TypeName))
{
where.And(p => p.TypeName.Like(request.TypeName));
}
if (!string.IsNullOrWhiteSpace(request.OrderCode))
{
where.And(p => p.OrderCode.Like(request.OrderCode));
}
if (!string.IsNullOrWhiteSpace(request.ReportedStatus))
{
where.And(p => p.ReportedStatus == request.ReportedStatus);
}
if (!string.IsNullOrWhiteSpace(request.ProcessingState))
{
where.And(p => p.ProcessingState == request.ProcessingState);
}
if (request.HistoryMonth.HasValue)
{
var historyMonth = new WhereClip("YEAR(TbOrderProgress.InsertTime)=" + request.HistoryMonth.Value.Year + " and MONTH(TbOrderProgress.InsertTime)=" + request.HistoryMonth.Value.Month);
where.And(historyMonth);
}
if (!string.IsNullOrWhiteSpace(request.ProgressType))
{
if (request.ProgressType == "进度正常")
{
var progressType = new WhereClip("(TbOrderProgress.FinishProcessingDateTime is not null and CONVERT(varchar(100),TbOrderProgress.FinishProcessingDateTime,23)=CONVERT(varchar(100),TbOrderProgress.DistributionTime,23)) or (TbOrderProgress.FinishProcessingDateTime is null and CONVERT(varchar(100),TbOrderProgress.DistributionTime,23)>=CONVERT(varchar(100),GETDATE(),23))");
where.And(progressType);
}
else if (request.ProgressType == "进度超前")
{
var progressType = new WhereClip("(TbOrderProgress.FinishProcessingDateTime is not null and CONVERT(varchar(100),TbOrderProgress.FinishProcessingDateTime,23)<CONVERT(varchar(100),TbOrderProgress.DistributionTime,23))");
where.And(progressType);
}
else if (request.ProgressType == "进度滞后")
{
var progressType = new WhereClip("(TbOrderProgress.FinishProcessingDateTime is not null and (CONVERT(varchar(100),TbOrderProgress.FinishProcessingDateTime,23)>CONVERT(varchar(100),TbOrderProgress.DistributionTime,23)) or (TbOrderProgress.FinishProcessingDateTime is null and CONVERT(varchar(100),TbOrderProgress.DistributionTime,23)<CONVERT(varchar(100),GETDATE(),23)))");
where.And(progressType);
}
}
#endregion
#region 数据权限新
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
where.And(p => p.ProcessFactoryCode == request.ProcessFactoryCode);
}
if (!string.IsNullOrWhiteSpace(request.ProjectId))
{
where.And(p => p.ProjectId == request.ProjectId);
}
if (!string.IsNullOrWhiteSpace(request.SiteCode))
{
List <string> SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(request.SiteCode, 5);
if (SiteList.Count > 0)
{
where.And(p => p.SiteCode.In(SiteList));
}
}
#endregion
try
{
var ret = Db.Context.From <TbOrderProgress>()
.Select(
TbOrderProgress._.All
, TbCompany._.CompanyFullName.As("SiteName")
, TbSysDictionaryData._.DictionaryText.As("ProcessingStateNew")
, TbUser._.UserName)
.LeftJoin <TbCompany>((a, c) => a.SiteCode == c.CompanyCode)
.AddSelect(Db.Context.From <TbCompany>().Select(p => p.CompanyFullName)
.Where(TbCompany._.CompanyCode == TbOrderProgress._.ProcessFactoryCode), "ProcessFactoryName")
.AddSelect(Db.Context.From <TbSysDictionaryData>().Select(p => p.DictionaryText)
.Where(TbSysDictionaryData._.DictionaryCode == TbOrderProgress._.UrgentDegree && TbSysDictionaryData._.FDictionaryCode == "UrgentDegree"), "UrgentDegreeNew")
.LeftJoin <TbSysDictionaryData>((a, c) => a.ProcessingState == c.DictionaryCode && c.FDictionaryCode == "ProcessingState")
.LeftJoin <TbUser>((a, c) => a.InsertUserCode == c.UserCode)
.Where(where).OrderByDescending(d => d.OrderCode).ToPageList(request.rows, request.page);
return(ret);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 导出
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public DataTable GetExportList(OrderProgressRequest request)
{
//组装查询语句
#region 模糊搜索条件
var where = new Where <TbOrderProgress>();
if (!string.IsNullOrWhiteSpace(request.TypeName))
{
where.And(p => p.TypeName.Like(request.TypeName));
}
if (!string.IsNullOrWhiteSpace(request.OrderCode))
{
where.And(p => p.OrderCode.Like(request.OrderCode));
}
if (!string.IsNullOrWhiteSpace(request.ReportedStatus))
{
where.And(p => p.ReportedStatus == request.ReportedStatus);
}
if (!string.IsNullOrWhiteSpace(request.ProcessingState))
{
where.And(p => p.ProcessingState == request.ProcessingState);
}
if (request.HistoryMonth.HasValue)
{
var historyMonth = new WhereClip("YEAR(TbOrderProgress.InsertTime)=" + request.HistoryMonth.Value.Year + " and MONTH(TbOrderProgress.InsertTime)=" + request.HistoryMonth.Value.Month);
where.And(historyMonth);
}
#endregion
#region 数据权限新
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
where.And(p => p.ProcessFactoryCode == request.ProcessFactoryCode);
}
if (!string.IsNullOrWhiteSpace(request.ProjectId))
{
where.And(p => p.ProjectId == request.ProjectId);
}
if (!string.IsNullOrWhiteSpace(request.SiteCode))
{
List <string> SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(request.SiteCode, 5);
if (SiteList.Count > 0)
{
where.And(p => p.SiteCode.In(SiteList));
}
}
#endregion
try
{
var ret = Db.Context.From <TbOrderProgress>()
.Select(
TbOrderProgress._.All
, TbCompany._.CompanyFullName.As("SiteName")
, TbSysDictionaryData._.DictionaryText.As("ProcessingStateNew")
, TbUser._.UserName)
.LeftJoin <TbCompany>((a, c) => a.SiteCode == c.CompanyCode)
.AddSelect(Db.Context.From <TbCompany>().Select(p => p.CompanyFullName)
.Where(TbCompany._.CompanyCode == TbOrderProgress._.ProcessFactoryCode), "ProcessFactoryName")
.AddSelect(Db.Context.From <TbSysDictionaryData>().Select(p => p.DictionaryText)
.Where(TbSysDictionaryData._.DictionaryCode == TbOrderProgress._.UrgentDegree && TbSysDictionaryData._.FDictionaryCode == "UrgentDegree"), "UrgentDegreeNew")
.LeftJoin <TbSysDictionaryData>((a, c) => a.ProcessingState == c.DictionaryCode && c.FDictionaryCode == "ProcessingState")
.LeftJoin <TbUser>((a, c) => a.InsertUserCode == c.UserCode)
.Where(where).OrderByDescending(d => d.OrderCode).ToDataTable();
return(ret);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取当前登录人的组织机构下所有的下级站点/
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public List <string> GetSiteCodList(OrderProgressRequest request)
{
List <string> SiteList = new List <string>();
#region 数据权限新
bool Flog = OperatorProvider.Provider.CurrentUser == null;
if (!Flog)
{
int orgType = Convert.ToInt32(OperatorProvider.Provider.CurrentUser.OrgType);
string CompanyId = OperatorProvider.Provider.CurrentUser.CompanyId;
SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(CompanyId, 5); //加载当前登录的组织机构下所有的站点
if (!string.IsNullOrWhiteSpace(request.OrgType) && !string.IsNullOrWhiteSpace(request.CompanyCode)) //选择左侧组织机构
{
SiteList.Clear(); //清空SiteList的集合
//第一步判断当前登录账号的，下级组织机构
if (orgType == 2) //经理部
{
//第二步判断当前点击的组织机构类型
if (request.OrgType == "3" || request.OrgType == "4" || request.OrgType == "5")//这个人就可以加载任意分部工区下的站点数据
{
SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(request.CompanyCode, 5);
}
else
{
SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(CompanyId, 5);
}
}
else if (orgType == 3) //分部
{
if (request.OrgType == "4" || request.OrgType == "5") //这个人只能加载该分部下的所有所以工区下的所有站点数据
{
//第三步判断当前点击的组织机构类型是不是属于当前登录账号的下级
List <string> a = orderProLogic.GetCompanyAllChild(CompanyId);
if (a.Contains(request.CompanyCode))
{
SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(request.CompanyCode, 5);
}
else
{
SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(CompanyId, 5);
}
}
else
{
SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(CompanyId, 5);
}
}
else if (orgType == 4) //工区
{
if (request.OrgType == "5") //这个人只能加载这个工区下的站点数据
{
//第三步判断当前点击的组织机构类型是不是属于当前登录账号的下级
List <string> a = orderProLogic.GetCompanyAllChild(CompanyId);
if (a.Contains(request.CompanyCode))
{
SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(request.CompanyCode, 5);
}
else
{
SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(CompanyId, 5);
}
}
else
{
SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(CompanyId, 5);
}
}
else if (orgType == 5) //站点
{
SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(CompanyId, 5); //这个人只能加载该站点下的数据
}
else if (orgType == 1) //加工厂
{
}
}
}
#endregion
return(SiteList);
}
public DataTable GetDdProgress(OrderProgressRequest request)
{
try
{
string where = "";
#region 查询条件
if (!string.IsNullOrWhiteSpace(request.TypeName))
{
where += " and a.TypeName like '%" + request.TypeName + "%' ";
}
if (!string.IsNullOrWhiteSpace(request.OrderCode))
{
where += " and a.OrderCode like '%" + request.OrderCode + "%' ";
}
if (!string.IsNullOrWhiteSpace(request.ProjectId))
{
where += " and a.ProjectId='" + request.ProjectId + "'";
}
if (!string.IsNullOrWhiteSpace(request.ProcessFactoryCode))
{
where += " and a.ProcessFactoryCode='" + request.ProcessFactoryCode + "'";
}
if (!string.IsNullOrWhiteSpace(request.SiteCode))
{
List <string> SiteList = orderProLogic.GetCompanyWorkAreaOrSiteList(request.SiteCode, 5);
StringBuilder sbSiteCode = new StringBuilder();
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 + ")";
}
}
if (request.HistoryMonth.HasValue)
{
string InsertTimeMonth = Convert.ToDateTime(request.HistoryMonth).ToString("yyyy-MM");
where += " and CONVERT(VARCHAR(7),a.InsertTime,120)='" + InsertTimeMonth + "'";
}
#endregion
string sql = @"select TbProgressType.ProgressType,ISNULL(TbProgress.WeightTotal, 0) as JdCount from (select '进度正常' as ProgressType
union all
select '进度超前' as ProgressType
union all
select '进度滞后' as ProgressType) TbProgressType
left join (select sum(a.WeightTotal) as WeightTotal,'进度超前' as ProgressType from TbOrderProgress a
where 1=1 " + where + @" and (a.FinishProcessingDateTime is not null and CONVERT(varchar(100),a.FinishProcessingDateTime,23)<CONVERT(varchar(100),a.DistributionTime,23))
union all
select sum(a.WeightTotal) as WeightTotal,'进度正常' as ProgressType from TbOrderProgress a
where 1=1 " + where + @" and (a.FinishProcessingDateTime is not null and CONVERT(varchar(100),a.FinishProcessingDateTime,23)=CONVERT(varchar(100),a.DistributionTime,23))
or (a.FinishProcessingDateTime is null and CONVERT(varchar(100),a.DistributionTime,23)>=CONVERT(varchar(100),GETDATE(),23))
union all
select sum(b.WeightSmallPlan) as WeightTotal,'进度滞后' as ProgressType from TbOrderProgress a
left join TbOrderProgressDetail b on a.OrderCode=b.OrderCode
where 1=1 " + where + @" and (a.FinishProcessingDateTime is not null and (CONVERT(varchar(100),a.FinishProcessingDateTime,23)>CONVERT(varchar(100),a.DistributionTime,23))
or (a.FinishProcessingDateTime is null and CONVERT(varchar(100),a.DistributionTime,23)<CONVERT(varchar(100),GETDATE(),23)))) TbProgress on TbProgressType.ProgressType=TbProgress.ProgressType ";
DataTable dt = Db.Context.FromSql(sql).ToDataTable();
return(dt);
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 加载报表数据
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public ActionResult GetProgressForm(OrderProgressRequest request)
{
var data = _orderProLogic.GetProgressForm(request);
return(Content(data.ToJson()));
}
/// <summary>
/// 获取分页列表数据
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public ActionResult GetGridJson(OrderProgressRequest request)
{
var data = _orderProLogic.GetDataListForPage(request);
return(Content(data.ToJson()));
}
