/// <summary>
/// 获取我的消息中的消息状态的列表数据
/// </summary>
/// <param name="pt"></param>
/// <param name="param"></param>
/// <returns></returns>
public ActionResult GetMessageStateList(PageSearchRequest pt, string param)
{
int state = 0;
DateTime?SDT = null;
DateTime?EDT = null;
string UserId = OperatorProvider.Provider.CurrentUser.UserId;
string ProjectId = OperatorProvider.Provider.CurrentUser.ProjectId;
if (!string.IsNullOrEmpty(param))
{
JObject jo = (JObject)JsonConvert.DeserializeObject(param);
state = Convert.ToInt32(jo["status"]);
if (jo["SDT"].ToString() != "{}" && jo["SDT"].ToString() != "")
{
SDT = Convert.ToDateTime(jo["SDT"]);
}
if (jo["EDT"].ToString() != "{}" && jo["EDT"].ToString() != "")
{
EDT = Convert.ToDateTime(jo["EDT"]).AddDays(1);
}
}
var data = _flowperform.GetMessageStateList(pt, UserId, ProjectId, state, SDT, EDT);
return(Content(data.ToJson()));
}
/// <summary>
/// 获取数据列表(分页)
/// </summary>
public PageModel GetDataListForPage(PageSearchRequest pt, string PhysicalTable, string keyword)
{
string sqlStr = @"select * from TbFormWindowsShowFields";
string whereStr = " where 1=1 and FormWindowsTableName is not null and (ISNULL('" + PhysicalTable + "','')='' or PhysicalTableName='" + PhysicalTable + "')";
#region 模糊搜索条件
var wherep = new List <Dos.ORM.Parameter>();
if (!string.IsNullOrWhiteSpace(keyword))
{
whereStr += " and (PhysicalTableName like '%'+@keyword+'%' or FieldName like '%'+@keyword+'%' or FieldCode like '%'+@keyword+'%')";
var p = new Dos.ORM.Parameter("@keyword", keyword, DbType.String, null);
wherep.Add(p);
}
#endregion
try
{
//取总数，以计算共多少页。
var count = Repository <TbFormWindowsShowFields> .FromSql(sqlStr + whereStr, wherep).Count();
var model = Repository <TbFormWindowsShowFields> .FromSql(sqlStr + whereStr, wherep, "PhysicalTableName,FieldShowOrder", pt.sord, pt.rows, pt.page).ToList();
var model1 = new PageModel(pt.page, pt.rows, count, model);
return(model1);
}
catch (Exception)
{
throw;
}
}
public ActionResult GetMyMessage(PageSearchRequest pt, string param)
{
int state = -1;
DateTime?SDT = null;
DateTime?EDT = null;
if (string.IsNullOrEmpty(param))
{
state = -1;
SDT = null;
EDT = null;
}
else
{
JObject jo = (JObject)JsonConvert.DeserializeObject(param);
state = Convert.ToInt32(jo["status"]);
if (jo["SDT"].ToString() != "{}" && jo["SDT"].ToString() != "")
{
SDT = Convert.ToDateTime(jo["SDT"]);
}
if (jo["EDT"].ToString() != "{}" && jo["EDT"].ToString() != "")
{
EDT = Convert.ToDateTime(jo["EDT"]).AddDays(1);
}
}
string usercode = Convert.ToString(Session["userid"]);
var data = _flowperform.GetMyMessage(pt, usercode, state, SDT, EDT);
return(Content(data.ToJson()));
}
/// <summary>
/// 查询本年本月所有信息
/// </summary>
/// <returns></returns>
public DataTable GetAllDayDatum(PageSearchRequest psr, int month)
{
DateTime now = DateTime.Now;
//本月第一天
DateTime FirstDay = new DateTime(now.Year, month, 1);
//本月最后一天
DateTime LastDay = Convert.ToDateTime(FirstDay.AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd") + " 23:59:59");
var where = new Where <TbSafeDatumManage>();
where.And(d => d.StartTime >= FirstDay && d.StartTime <= LastDay);
if (!string.IsNullOrWhiteSpace(psr.ProcessFactoryCode))
{
where.And(d => d.ProcessFactoryCode == psr.ProcessFactoryCode);
}
var ret = Db.Context.From <TbSafeDatumManage>().Select(
TbSafeDatumManage._.All
, TbCompany._.CompanyFullName.As("ProcessFactoryName")
, TbUser._.UserName.As("InsertUserName"))
.LeftJoin <TbCompany>((a, c) => a.ProcessFactoryCode == c.CompanyCode)
.AddSelect(Db.Context.From <TbUser>().Select(p => p.UserName)
.Where(TbUser._.UserCode == TbSafeDatumManage._.SpeechUser), "SpeechUserName")
.LeftJoin <TbUser>((a, c) => a.InsertUserCode == c.UserCode)
.Where(where).ToDataTable();
if (ret.Rows.Count > 0)
{
for (var i = 0; i < ret.Rows.Count; i++)
{
var data = ret.Rows[i]["SpeechContent"].ToString().Replace("<", "<").Replace(">", ">");
ret.Rows[i]["SpeechContent"] = data;
}
}
return(ret);
}
/// <summary>
/// 获取表单预警
/// </summary>
/// <param name="pt"></param>
/// <param name="param"></param>
/// <returns></returns>
public ActionResult GetEarlyWarningFormInfo(PageSearchRequest pt, string param)
{
DateTime?SDT = null;
DateTime?EDT = null;
int state = 0;
string UserId = OperatorProvider.Provider.CurrentUser.UserId;
string ProjectId = OperatorProvider.Provider.CurrentUser.ProjectId;
if (string.IsNullOrEmpty(param))
{
state = 0;
SDT = null;
EDT = null;
}
else
{
JObject jo = (JObject)JsonConvert.DeserializeObject(param);
state = Convert.ToInt32(jo["state"]);
if (jo["SDT"].ToString() != "{}" && jo["SDT"].ToString() != "")
{
SDT = Convert.ToDateTime(jo["SDT"]);
}
if (jo["EDT"].ToString() != "{}" && jo["EDT"].ToString() != "")
{
EDT = Convert.ToDateTime(jo["EDT"]).AddDays(1);
}
}
var data = _earlyWarning.GetEarlyWarningFormInfo(pt, UserId, ProjectId, state, SDT, EDT);
return(Content(data.ToJson()));
}
public ActionResult GetAllProjectJgc(PageSearchRequest request)
{
request.rows = 50;
var data = _User.GetAllProjectJgc(request);
return(Content(data.ToJson()));
}
/// <summary>
/// 以当日查询班前讲话
/// </summary>
/// <param name="keyValue"></param>
/// <returns></returns>
public DataTable GetToday(PageSearchRequest psr, string keyValue)
{
var KTime = DateTime.Parse(keyValue + " 00:00:00");
var JTime = DateTime.Parse(keyValue + " 23:59:59");
var where = new Where <TbSafeDatumManage>();
where.And(d => d.StartTime >= KTime && d.StartTime <= JTime);
//if (!string.IsNullOrWhiteSpace(psr.ProjectId))
//{
// where.And(d => d.ProjectId == psr.ProjectId);
//}
if (!string.IsNullOrWhiteSpace(psr.ProcessFactoryCode))
{
where.And(d => d.ProcessFactoryCode == psr.ProcessFactoryCode);
}
var ret = Db.Context.From <TbSafeDatumManage>()
.Select(
TbSafeDatumManage._.All
, TbCompany._.CompanyFullName.As("ProcessFactoryName")
, TbUser._.UserName.As("InsertUserName"))
.LeftJoin <TbCompany>((a, c) => a.ProcessFactoryCode == c.CompanyCode)
.AddSelect(Db.Context.From <TbUser>().Select(p => p.UserName)
.Where(TbUser._.UserCode == TbSafeDatumManage._.SpeechUser), "SpeechUserName")
.LeftJoin <TbUser>((a, c) => a.InsertUserCode == c.UserCode)
.Where(where).ToDataTable();
if (ret.Rows.Count > 0)
{
var data = ret.Rows[0]["SpeechContent"].ToString().Replace("<", "<").Replace(">", ">");
ret.Rows[0]["SpeechContent"] = data;
}
return(ret);
}
public HttpResponseMessage GetAllProjectJgc()
{
PageSearchRequest request = new PageSearchRequest();
var data = _User.GetAllProjectJgc(request);
return(AjaxResult.Success(data).ToJsonApi());
}
/// <summary>
/// 获取数据列表(分页)
/// </summary>
public PageModel GetDataListForPage(PageSearchRequest request, string EarlyWarningCode)
{
//组装查询语句
#region 模糊搜索条件
var where = new Where <TbFormEarlyWarningNode>();
if (!string.IsNullOrWhiteSpace(EarlyWarningCode))
{
where.And(p => p.EarlyWarningCode == EarlyWarningCode);
}
if (!string.IsNullOrWhiteSpace(OperatorProvider.Provider.CurrentUser.ProjectId))
{
where.And(d => d.ProjectId == OperatorProvider.Provider.CurrentUser.ProjectId);
}
#endregion
try
{
var ret = Db.Context.From <TbFormEarlyWarningNode>()
.Select(
TbFormEarlyWarningNode._.All
, TbSysMenu._.MenuName)
.LeftJoin <TbSysMenu>((a, c) => a.MenuCode == c.MenuCode)
.Where(where).OrderBy(d => d.ID).ToPageList(request.rows, request.page);
return(ret);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 以本周查询周例会
/// </summary>
/// <param name="keyValue"></param>
/// <returns></returns>
public DataTable GetWeek(PageSearchRequest psr, string sdate, string edate)
{
var KTime = DateTime.Parse(sdate + " 00:00:00");
var JTime = DateTime.Parse(edate + " 23:59:59");
var ret = Db.Context.From <TbSafeAccidentReport>()
.Select(
TbSafeAccidentReport._.All
, TbCompany._.CompanyFullName.As("ProcessFactoryName")
, TbUser._.UserName.As("InsertUserName"))
.LeftJoin <TbCompany>((a, c) => a.ProcessFactoryCode == c.CompanyCode)
.LeftJoin <TbUser>((a, c) => a.InsertUserCode == c.UserCode)
.Where(p => p.StartTime >= KTime && p.StartTime <= JTime && p.ProjectId == psr.ProjectId).ToDataTable();
if (ret.Rows.Count == 0)
{
return(ret);
}
ret.Columns.Add("ParticipantsName", typeof(string));
var code = ret.Rows[0]["Participants"].ToString();
var user = Db.Context.From <TbUser>().Where("UserCode in ('" + code + "')").ToList();
StringBuilder sb = new StringBuilder();
for (var i = 0; i < user.Count; i++)
{
sb.Append(user[i].UserName + ",");
}
ret.Rows[0]["ParticipantsName"] = sb;
if (ret.Rows.Count > 0)
{
var data = ret.Rows[0]["MeetingContent"].ToString().Replace("<", "<").Replace(">", ">");
ret.Rows[0]["MeetingContent"] = data;
}
return(ret);
}
/// <summary>
/// 获取该用户下的所有未处理的流程预警信息
/// </summary>
/// <returns></returns>
public PageModel GetEarlyWarningInfo(PageSearchRequest pt, string usercode, string ProjectId, int state, DateTime?sdt, DateTime?edt)
{
string where = "";
if (!string.IsNullOrWhiteSpace(ProjectId))
{
where += " and a.ProjectId=@ProjectId";
}
string sql = @"select a.ID,a.ProjectId,a.ProcessFactoryCode,a.EarlyWarningCode,a.FlowPerformID,a.FlowCode,a.EWFormCode,a.EWFormDataCode,a.EarlyWarningStart,case when a.EarlyWarningStart=0 then '未阅' when a.EarlyWarningStart=1 then '已阅' else '预警已撤销' end EarlyWarningStartName,a.FlowYjUserCode as EarlyWarningBTUser,u2.UserName as EarlyWarningBTUserName,a.FlowSpUserCode,u1.UserName as FlowUserName,b.PreNodeCompleteDate,EarlyWarningContent,a.EarlyWarningTime from TbFlowEarlyWarningOtherInfo a
left join TbFlowPerformOpinions b on a.FlowPerformID=b.FlowPerformID and a.FlowNodeCode=b.FlowNodeCode and a.FlowPerformOID=b.id
left join TbUser u1 on a.FlowSpUserCode=u1.UserId
left join TbUser u2 on a.FlowYjUserCode=u2.UserId
where a.EarlyWarningStart is not null
and EarlyWarningStart=@EarlyWarningStart
and (ISNULL(@UserId,'')='' or a.FlowYjUserCode=@UserId)
and (isnull(@SDT,'')='' or a.EarlyWarningTime>=@SDT)
and (isnull(@EDT,'')='' or a.EarlyWarningTime<=@EDT)";
List <Parameter> parameter = new List <Parameter>();
parameter.Add(new Parameter("@UserId", usercode, DbType.String, null));
parameter.Add(new Parameter("@EarlyWarningStart", state, DbType.Int32, null));
parameter.Add(new Parameter("@SDT", sdt, DbType.DateTime, null));
parameter.Add(new Parameter("@EDT", edt, DbType.DateTime, null));
parameter.Add(new Parameter("@ProjectId", ProjectId, DbType.String, null));
try
{
var ret = Repository <TbFormEarlyWarningNodeInfo> .FromSqlToPageTable(sql, parameter, pt.rows, pt.page, "EarlyWarningTime", "desc");
return(ret);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 查看审批消息列表
/// </summary>
/// <param name="pt"></param>
/// <param name="para"></param>
/// <returns></returns>
public string GetMyApproval(PageSearchRequest pt, string param)
{
//int state = -1;
//DateTime? SDT, EDT;
//if (string.IsNullOrEmpty(para))
//{
// state = -1;
// SDT = null;
// EDT = null;
//}
//else
//{
// JObject jo = (JObject)JsonConvert.DeserializeObject(para);
// state = Convert.ToInt32(jo["state"]);
// SDT = null;
// EDT = null;
// if (jo["SDT"].ToString() != "{}" && jo["SDT"].ToString() != "")
// {
// SDT = Convert.ToDateTime(jo["SDT"]);
// }
// if (jo["EDT"].ToString() != "{}" && jo["EDT"].ToString() != "")
// {
// EDT = Convert.ToDateTime(jo["EDT"]).AddDays(1);
// }
//}
//var data = _flowperform.GetMyApproval(pt, Convert.ToString(Session["userid"]), state, SDT, EDT);
//return Content(data.ToJson());
int state = -1;
DateTime?SDT = null;
DateTime?EDT = null;
if (string.IsNullOrEmpty(param))
{
state = -1;
SDT = null;
EDT = null;
}
else
{
JObject jo = (JObject)JsonConvert.DeserializeObject(param);
state = Convert.ToInt32(jo["state"]);
if (jo["SDT"].ToString() != "{}" && jo["SDT"].ToString() != "")
{
SDT = Convert.ToDateTime(jo["SDT"]);
}
if (jo["EDT"].ToString() != "{}" && jo["EDT"].ToString() != "")
{
EDT = Convert.ToDateTime(jo["EDT"]).AddDays(1);
}
}
string userId = OperatorProvider.Provider.CurrentUser.UserId;
return(_flowperform.GetMyApproval(pt, userId, state, SDT, EDT));
}
public List <TbSysLog> GetAllLog(PageSearchRequest pr, JObject queryParam)
{
string selectName = "";
string selectDate = "";
if (queryParam.Count != 0)
{
selectName = queryParam["keyword"].ToString();
selectDate = queryParam["timeType"].ToString();
}
var time = DateTime.Now;
var endTime = time.ToString("yyyy-MM-dd") + " 23:59:59";
var startime = time.AddDays(-7).ToString("yyyy-MM-dd") + " 00:00:00";
switch (selectDate)
{
case "1":
startime = time.ToString("yyyy-MM-dd") + " 00:00:00";
break;
case "2":
startime = time.AddDays(-7).ToString("yyyy-MM-dd") + " 00:00:00";
break;
case "3":
startime = time.AddMonths(-1).ToString("yyyy-MM-dd") + " 00:00:00";
break;
case "4":
startime = time.AddMonths(-3).ToString("yyyy-MM-dd") + " 00:00:00";
break;
}
StringBuilder sb = new StringBuilder();
sb.Append("select ");
sb.Append("a.id,a.LogDate,a.UserIP,a.UserCode,b.UserName as UserName,");
sb.Append("a.ActionMenu,a.HostName,c.MenuName as ActionMenuName,a.ActionType");
sb.Append(" from TbSysLog a left join TbUser b on a.UserCode = b.UserCode left join TbTableMenu c on a.ActionMenu=c.TableName where 1=1");
if (!string.IsNullOrEmpty(selectName))
{
sb.Append(" and (b.UserName like @name or c.MenuName like @name or a.ActionType like @name)");
}
sb.Append(" and (a.LogDate BETWEEN '" + startime + "' and '" + endTime + "')");
var list = Db.Context.FromSql(sb.ToString()).AddInParameter("@name", DbType.String, "%" + selectName + "%").ToList <TbSysLog>();
pr.records = list.Count();
List <Dos.ORM.Parameter> para = new List <Dos.ORM.Parameter>();
para.Add(new Dos.ORM.Parameter("@name", "%" + selectName + "%", DbType.String, null));
var model = Repository <TbSysLog> .FromSql(sb.ToString(), para, "LogDate", "desc", pr.rows, pr.page).ToList();
return(model);
}
public ActionResult GetAllLog(PageSearchRequest pr, string queryJson)
{
var queryParam = queryJson.ToJObject();
var data = new
{
rows = si.GetAllLog(pr, queryParam),
total = pr.total,
page = pr.page,
records = pr.records
};
return(Content(data.ToJson()));
}
/// <summary>
/// 查询用户所属加工厂
/// </summary>
/// <param name="userCode"></param>
/// <returns></returns>
public PageModel GetProcessFactory(PageSearchRequest psr, string type)
{
//参数化
List <Parameter> parameter = new List <Parameter>();
string where = " where 1=1 and cp.OrgType=@type";
parameter.Add(new Parameter("@type", type, DbType.String, null));
string sql = @"select cp.id,cp.CompanyCode,cp.CompanyFullName,cp.ParentCompanyCode,cp.Address,cp.OrgType from TbCompany cp
left join TbProjectCompany pc on cp.CompanyCode=pc.CompanyCode ";
var model = Repository <TbCompany> .FromSqlToPageTable(sql + where, parameter, psr.rows, psr.page, "ID", "asc");
return(model);
}
/// <summary>
/// 获取分页的列表数据
/// </summary>
/// <returns></returns>
public ActionResult GetGridJson(PageSearchRequest pt, string PhysicalTable, string keyword)
{
//var data = new
//{
// rows = _formFieldsImp.GetDataListForPage(pt, PhysicalTable, keyword),
// total = pt.total,
// page = pt.page,
// records = pt.records
//};
var data = _formFields.GetDataListForPage(pt, PhysicalTable, keyword);
return(Content(data.ToJson()));
}
/// <summary>
/// 根据部门Code查询用户信息
/// </summary>
/// <param name="psr">分页</param>
/// <param name="keyword">手动输入查询条件</param>
/// <param name="DCode">条件参数部门Code</param>
/// <param name="CCode">条件参数公司Code</param>
/// <returns></returns>
public List <TbUser> GetDepartmentUserList(PageSearchRequest psr, string keyword, string DCode, string CCode)
{
string sql = @"select A.ID,A.UserCode,A.UserName,A.UserSex,A.UserClosed from TbUser A inner join (select a.PositionCode,a.PositionName,a.CompanyCode,a.DepartmentCode,b.UserCode from TbPosition a inner join TbPositionUser b on a.PositionCode=b.PositionCode where a.DepartmentCode=@DCode and a.CompanyCode=@CCode) B on A.UserCode=B.UserCode where A.UserClosed='在职'";
if (!string.IsNullOrEmpty(keyword))
{
sql += " and A.UserName like @name";
}
sql += " group by A.ID,A.UserCode,A.UserName,A.UserSex,A.UserClosed";
var data = Db.Context.FromSql(sql).AddInParameter("@DCode", DbType.String, DCode).AddInParameter("@CCode", DbType.String, CCode).AddInParameter("@name", DbType.String, "%" + keyword + "%").ToList <TbUser>();
psr.records = data.Count();
return(data);
}
/// <summary>
/// 图形数据查询
/// </summary>
/// <returns></returns>
public DataTable GetAnalysis(PageSearchRequest psr)
{
string sql = @"select a.BranchCode,b.CompanyFullName,SUM(a.OrderMachiningAmount) JiaGong,SUM(a.AmountOfMoney) JinE from TbSettlementOrder a
left join TbCompany b on a.BranchCode=b.CompanyCode where DateDiff(month, a.InsertTime, GetDate()) = 0 ";
if (!string.IsNullOrEmpty(psr.ProjectId))
{
sql += " and a.ProjectId='" + psr.ProjectId + "'";
}
sql += " group by a.BranchCode,b.CompanyFullName";
var ret = Db.Context.FromSql(sql).ToDataTable();
return(ret);
}
public string GetMyInitiate(PageSearchRequest pt, string param)
{
MyInitiate request = new MyInitiate();
if (string.IsNullOrEmpty(param))
{
request.status = -1;
request.sdt = DateTime.Now.AddDays(-30);
request.edt = DateTime.Now.AddDays(1);
}
else
{
request = PM.Common.Extension.JsonEx.JsonToObj <MyInitiate>(param);
}
return(_flowperform.GetMyInitiate(pt, request.status, Convert.ToString(Session["usercode"]), request.sdt, request.edt.AddDays(1)));
}
/// <summary>
/// 查询本年所有信息
/// </summary>
/// <returns></returns>
public DataTable GetAllWeekAccidentReport(PageSearchRequest psr)
{
DateTime now = DateTime.Now;
string year = now.Year.ToString();
//本年第一天
DateTime FirstDay = Convert.ToDateTime(year + "-01-01" + " 00:00:00");
//本年最后一天
DateTime LastDay = Convert.ToDateTime(year + "-12-31" + " 23:59:59");
var where = new Where <TbSafeAccidentReport>();
where.And(d => d.StartTime >= FirstDay && d.StartTime <= LastDay);
if (!string.IsNullOrWhiteSpace(psr.ProcessFactoryCode))
{
where.And(d => d.ProcessFactoryCode == psr.ProcessFactoryCode);
}
var ret = Db.Context.From <TbSafeAccidentReport>().Select(
TbSafeAccidentReport._.All
, TbCompany._.CompanyFullName.As("ProcessFactoryName")
, TbUser._.UserName.As("InsertUserName"))
.LeftJoin <TbCompany>((a, c) => a.ProcessFactoryCode == c.CompanyCode)
.LeftJoin <TbUser>((a, c) => a.InsertUserCode == c.UserCode
).Where(where).ToDataTable();
if (ret.Rows.Count > 0)
{
ret.Columns.Add("ParticipantsName", typeof(string));
for (var j = 0; j < ret.Rows.Count; j++)
{
var code = ret.Rows[j]["Participants"].ToString();
var user = Db.Context.From <TbUser>().Where("UserCode in ('" + code + "')").ToList();
StringBuilder sb = new StringBuilder();
for (var i = 0; i < user.Count; i++)
{
sb.Append(user[i].UserName + ",");
}
ret.Rows[j]["ParticipantsName"] = sb;
var data = ret.Rows[j]["MeetingContent"].ToString().Replace("<", "<").Replace(">", ">");
ret.Rows[j]["MeetingContent"] = data;
}
}
else
{
return(ret);
}
return(ret);
}
/// <summary>
/// 各站点各订单进度分析图
/// </summary>
/// <returns></returns>
public DataTable GetGanttList(PageSearchRequest psr, string PId)
{
string addsql = "";
if (!string.IsNullOrWhiteSpace(PId))
{
addsql += "ProjectId='" + PId + "'";
}
else if (!string.IsNullOrWhiteSpace(psr.ProjectId))
{
addsql += "ProjectId='" + psr.ProjectId + "'";
}
string sql = "select OrderCode,InsertTime as 'start_date',DistributionTime as end_date,(AccumulativeQuantity/WeightTotal) progress from TbOrderProgress where AccumulativeQuantity <> 0 and AccumulativeQuantity<=WeightTotal and " + addsql;
var ret = Db.Context.FromSql(sql).ToDataTable();
return(ret);
}
/// <summary>
/// 站点订单进度分析
/// </summary>
/// <returns></returns>
public DataTable GetSiteSchedule(PageSearchRequest psr, string PId)
{
string addsql = "";
if (!string.IsNullOrWhiteSpace(PId))
{
addsql += "ProjectId='" + PId + "'";
}
else if (!string.IsNullOrWhiteSpace(psr.ProjectId))
{
addsql += "ProjectId='" + psr.ProjectId + "'";
}
string sql = "select a.SiteCode,b.CompanyFullName,Sum(a.WeightTotal) WeightTotal,Sum(a.AccumulativeQuantity) AccumulativeQuantity,Sum(ISNULL((a.WeightTotal-a.AccumulativeQuantity),0)) unfinished from TbOrderProgress a left join TbCompany b on a.SiteCode=b.CompanyCode where 1=1 and AccumulativeQuantity <> 0 and AccumulativeQuantity<=WeightTotal and a." + addsql + " group by a.SiteCode,b.CompanyFullName";
var ret = Db.Context.FromSql(sql).ToDataTable();
return(ret);
}
public PageModel GetMenu(PageSearchRequest request)
{
#region 模糊搜索条件
var where = new Where <TbFormEarlyWarningBegTime>();
if (!string.IsNullOrWhiteSpace(OperatorProvider.Provider.CurrentUser.ProjectId))
{
where.And(d => d.ProjectId == OperatorProvider.Provider.CurrentUser.ProjectId);
}
#endregion
var ret = Db.Context.From <TbFormEarlyWarningBegTime>()
.Select(
TbFormEarlyWarningBegTime._.All
, TbSysMenu._.MenuName)
.LeftJoin <TbSysMenu>((a, c) => a.MenuCode == c.MenuCode).Where(where).OrderBy(d => d.ID).ToPageList(request.rows, request.page);
return(ret);
}
/// <summary>
/// 二号加工厂
/// </summary>
/// <returns></returns>
public DataTable GetTwoJia(PageSearchRequest psr, string PId)
{
string addsql = "";
if (!string.IsNullOrWhiteSpace(PId))
{
addsql += "ProjectId='" + PId + "'";
}
else if (!string.IsNullOrWhiteSpace(psr.ProjectId))
{
addsql += "ProjectId='" + psr.ProjectId + "'";
}
string sql = @"select b.CompanyFullName,isnull(Sum(TotalAggregate),0.0000) TotalAggregate from TbDistributionEnt a left join TbCompany b on a.SiteCode=b.CompanyCode
where ProcessFactoryCode='6386683729561128960' and a." + addsql + " and DateDiff(month, InsertTime, GetDate()) = 0 group by b.CompanyFullName";
var ret = Db.Context.FromSql(sql).ToDataTable();
return(ret);
}
/// <summary>
/// 甘特图查询
/// </summary>
/// <returns></returns>
public ActionResult GetGanttListJson(PageSearchRequest psr, string PId)
{
string strLinks = "[]";//线条数据信息
string strDatas = "[]";
var data = _psLogic.GetGanttList(psr, PId);
if (data.Rows.Count > 0)
{
data.Columns.Add(new DataColumn("open"));
for (var i = 0; i < data.Rows.Count; i++)
{
var prog = decimal.Round(decimal.Parse(data.Rows[i]["progress"].ToString()), 2);
data.Rows[i]["progress"] = prog;
data.Rows[0]["open"] = "true";
}
}
strDatas = data.ToJson();
var strJSON = "{\"data\":" + strDatas + ",\"links\":" + strLinks + "}";
return(Content(strJSON));
}
/// <summary>
/// 构件加工厂当月配送情况分析
/// </summary>
/// <returns></returns>
public DataTable GetGJCDYPSAnalysis(PageSearchRequest psr, string PId)
{
string addsql = "";
if (!string.IsNullOrWhiteSpace(PId))
{
addsql += "ProjectId='" + PId + "'";
}
else if (!string.IsNullOrWhiteSpace(psr.ProjectId))
{
addsql += "ProjectId='" + psr.ProjectId + "'";
}
string sql = @"select a.CompanyFullName,isnull(a.TotalAggregate,0.0000) TotalAggregate,
isnull(b.ZCDis,0.0000) ZCDis,isnull(c.TQDis,0.0000) TQDis,isnull(d.YHDis,0.0000) YHDis
from (select b.CompanyFullName,isnull(Sum(a.TotalAggregate),0.0000) TotalAggregate from TbDistributionEnt a
left join TbCompany b on a.ProcessFactoryCode=b.CompanyCode
where DateDiff(month, a.InsertTime, GetDate()) = 0 and a." + addsql + @" group by b.CompanyFullName) a left join
(select b.CompanyFullName,isnull(Sum(a.TotalAggregate),0.0000) ZCDis from TbDistributionEnt a
left join TbCompany b on a.ProcessFactoryCode=b.CompanyCode
left join TbDistributionPlanInfo c on a.DistributionPlanCode=c.DistributionPlanCode
where DateDiff(month, a.InsertTime, GetDate()) = 0 and a.LoadCompleteTime=c.DistributionTime
and a." + addsql + " and c." + addsql + @"
group by b.CompanyFullName) b on a.CompanyFullName=b.CompanyFullName left join
(select b.CompanyFullName,isnull(Sum(a.TotalAggregate),0.0000) TQDis from TbDistributionEnt a
left join TbCompany b on a.ProcessFactoryCode=b.CompanyCode
left join TbDistributionPlanInfo c on a.DistributionPlanCode=c.DistributionPlanCode
where DateDiff(month, a.InsertTime, GetDate()) = 0 and a.LoadCompleteTime<c.DistributionTime
and a." + addsql + " and c." + addsql + @"
group by b.CompanyFullName) c on a.CompanyFullName=c.CompanyFullName left join
(select b.CompanyFullName,isnull(Sum(a.TotalAggregate),0.0000) YHDis from TbDistributionEnt a
left join TbCompany b on a.ProcessFactoryCode=b.CompanyCode
left join TbDistributionPlanInfo c on a.DistributionPlanCode=c.DistributionPlanCode
where DateDiff(month, a.InsertTime, GetDate()) = 0 and a.LoadCompleteTime>c.DistributionTime
and a." + addsql + " and c." + addsql + @"
group by b.CompanyFullName) d on a.CompanyFullName=d.CompanyFullName";
var ret = Db.Context.FromSql(sql).ToDataTable();
return(ret);
}
/// <summary>
/// 获取该用户下的所有的表单预警信息
/// </summary>
/// <returns></returns>
public PageModel GetEarlyWarningFormInfo(PageSearchRequest pt, string usercode, string ProjectId, int state, DateTime?sdt, DateTime?edt)
{
//获取表单预警信息数量
string where = "";
if (!string.IsNullOrWhiteSpace(usercode))
{
where += " and a.EWUserCode=@UserId";
}
if (!string.IsNullOrWhiteSpace(ProjectId))
{
where += " and a.ProjectId=@ProjectId";
}
string sql = @"select a.ID,a.ProjectId,a.ProcessFactoryCode,a.EWUserCode,case when c.MenuName is null then '取样订单' else c.MenuName end MenuName,a.EarlyWarningCode as EWNodeCode,b.EarlyWarningContent as EWNodeName,a.EWFormDataCode,a.WorkArea,e.CompanyFullName as WorkAreaName,d.UserName as EWUserName,a.EWContent as EarlyWarningContent,a.EWStart,case when a.EWStart=0 then '未阅' when a.EWStart=1 then '已阅' else '预警已撤销' end EWStartName,a.EWTime as EarlyWarningTime from TbFormEarlyWarningNodeInfo a
left join TbEarlyWarningSetUp b on a.EarlyWarningCode=b.EarlyWarningNewsCode
left join TbSysMenu c on a.MenuCode=c.MenuCode
left join TbUser d on a.EWUserCode=d.UserId
left join TbCompany e on a.WorkArea=e.CompanyCode
where a.MsgType=2
and a.EWStart=@EWStart
and (ISNULL(@SDT,'')='' or a.EWTime>=@SDT)
and (ISNULL(@EDT,'')='' or a.EWTime<=@EDT)" + where + @"";
List <Parameter> parameter = new List <Parameter>();
parameter.Add(new Parameter("@UserId", usercode, DbType.String, null));
parameter.Add(new Parameter("@EWStart", state, DbType.Int32, null));
parameter.Add(new Parameter("@SDT", sdt, DbType.DateTime, null));
parameter.Add(new Parameter("@EDT", edt, DbType.DateTime, null));
parameter.Add(new Parameter("@ProjectId", ProjectId, DbType.String, null));
try
{
var ret = Repository <TbFormEarlyWarningNodeInfo> .FromSqlToPageTable(sql, parameter, pt.rows, pt.page, "EarlyWarningTime", "desc");
return(ret);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 订单进度
/// </summary>
/// <returns></returns>
public DataTable OrderJinDu(PageSearchRequest psr, string PId)
{
string addsql = "";
if (!string.IsNullOrWhiteSpace(PId))
{
addsql += "ProjectId='" + PId + "'";
}
else if (!string.IsNullOrWhiteSpace(psr.ProjectId))
{
addsql += "ProjectId='" + psr.ProjectId + "'";
}
var sql = "select a.OrderCode,b.CompanyFullName,a.DistributionTime,c.LoadCompleteTime from TbDistributionPlanInfo a left join TbCompany b on a.SiteCode=b.CompanyCode inner join TbDistributionEnt c on a.DistributionPlanCode=c.DistributionPlanCode where 1=1 and a." + addsql + " and c." + addsql;
var ret = Db.Context.FromSql(sql).ToDataTable();
if (ret.Rows.Count > 0)
{
ret.Columns.Add(new DataColumn("Sta"));
for (var i = 0; i < ret.Rows.Count; i++)
{
var jh = Convert.ToDateTime(ret.Rows[i]["DistributionTime"].ToString());
var wc = Convert.ToDateTime(ret.Rows[i]["LoadCompleteTime"].ToString());
if (wc > jh)
{
ret.Rows[i]["Sta"] = "3";
}
else if (wc == jh)
{
ret.Rows[i]["Sta"] = "1";
}
else
{
ret.Rows[i]["Sta"] = "2";
}
}
}
return(ret);
}
/// <summary>
/// 获取数据列表(分页)
/// </summary>
public PageModel GetDataListForPage(PageSearchRequest request, string MenuCode)
{
//组装查询语句
#region 模糊搜索条件
var where = new Where <TbFormEarlyWarningBegTime>();
if (!string.IsNullOrWhiteSpace(MenuCode))
{
where.And(p => p.MenuCode.Like(MenuCode));
}
if (!string.IsNullOrWhiteSpace(OperatorProvider.Provider.CurrentUser.ProjectId))
{
where.And(d => d.ProjectId == OperatorProvider.Provider.CurrentUser.ProjectId);
}
#endregion
try
{
var ret = Db.Context.From <TbFormEarlyWarningBegTime>()
.Select(
TbFormEarlyWarningBegTime._.All
, TbSysMenu._.MenuName
, TbCompany._.CompanyFullName.As("BranchName"))
.LeftJoin <TbSysMenu>((a, c) => a.MenuCode == c.MenuCode)
.LeftJoin <TbCompany>((a, c) => a.BranchCode == c.CompanyCode)
.AddSelect(Db.Context.From <TbCompany>().Select(p => p.CompanyFullName)
.Where(TbCompany._.CompanyCode == TbFormEarlyWarningBegTime._.SiteCode), "SiteName")
.AddSelect(Db.Context.From <TbCompany>().Select(p => p.CompanyFullName)
.Where(TbCompany._.CompanyCode == TbFormEarlyWarningBegTime._.WorkAreaCode), "WorkAreaName")
.Where(where).OrderBy(d => d.ID).ToPageList(request.rows, request.page);
return(ret);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 加工厂各类订单状态量分析
/// </summary>
/// <returns></returns>
public DataTable PFKOSAnalysis(PageSearchRequest psr, string PId)
{
string addsql = "";
if (!string.IsNullOrWhiteSpace(PId))
{
addsql += "ProjectId='" + PId + "'";
}
else if (!string.IsNullOrWhiteSpace(psr.ProjectId))
{
addsql += "ProjectId='" + psr.ProjectId + "'";
}
var sql = @"select isnull(Sum(WeightTotal),0.0000) WeightTotal,(select '1') TypeCode from TbWorkOrder
where Examinestatus <> '未发起' and Examinestatus <> '已退回' and ProjectId=''
union all
select isnull(Sum(a.WeightTotal),0.0000) ConfirmedTotal,(select '2') TypeCode from TbWorkOrder a
left join TbSysDictionaryData b on a.ProcessingState=b.DictionaryCode
where b.FDictionaryCode='ProcessingState' and b.DictionaryText='确认加工' and a." + addsql + @"
union all
select isnull(SUM(AccumulativeQuantity),0.0000) ProcessingTotal,(select '3') TypeCode from TbOrderProgress
where AccumulativeQuantity>0 and AccumulativeQuantity<WeightTotal and " + addsql + @"
union all
select isnull(SUM(WeightTotal),0.0000) UnprocessedTotal,(select '4') TypeCode from TbOrderProgress
where AccumulativeQuantity=0 and " + addsql + @"
union all
select isnull(SUM(WeightTotal),0.0000) ProcessingNonDeliveryTotal,(select '5') TypeCode from TbOrderProgress
where OrderCode not in
(select a.OrderCode from TbDistributionPlanInfo a
inner join TbDistributionEnt b on a.DistributionPlanCode=b.DistributionPlanCode where a." + addsql + " and b." + addsql + @")
and WeightTotal=AccumulativeQuantity and " + addsql + @"
union all
select isnull(SUM(TotalAggregate),0.0000) DistributionTotal,(select '6') TypeCode
from TbDistributionEnt where " + addsql;
var ret = Db.Context.FromSql(sql).ToDataTable();
return(ret);
}