/// <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); }