/// <summary> /// 获取功能分页信息 /// </summary> /// <param name="pageSize">页大小</param> /// <param name="currentPage">当前页</param> /// <param name="functionName">功能名称</param> /// <param name="controllerID">功能对应的动作所属控制器编号</param> /// <param name="areaID">功能对应的动作所属控制器所属区域编号</param> /// <param name="parentFunctionID">上级功能编号</param> /// <returns></returns> public static AsiatekPagedList <FunctionListModel> GetPagedFunctions(FunctionSettingModel model, int pageSize) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "VW_GetFunctionsWithLevelID f"), new SqlParameter("@joinStr", @"LEFT JOIN Actions act ON f.ActionID=act.ID LEFT JOIN Controllers c ON act.ControllerID=c.ID LEFT JOIN Areas a ON c.AreaID=a.ID LEFT JOIN Functions f2 ON f.ParentID=f2.ID"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", model.SearchPage), new SqlParameter("@orderBy", "f.LevelID"), new SqlParameter("@showColumns", @"f.ID ,f.IsBackground,f.LevelID, f.FunctionName , f2.FunctionName AS ParentFunctionName , act.ActionName , c.ControllerName , a.AreaName"), }; string conditionStr = "f.FunctionName LIKE '%" + model.FunctionName + "%'"; if (model.IsMenu)//搜索菜单,搜索菜单的情况下,只额外考虑功能名、顶级功能、上级功能 { conditionStr += " AND f.ActionID IS NULL"; if (model.IsTopFunction)//顶级功能 { conditionStr += " AND f.ParentID IS NULL"; } else if (model.ParentFunctionID != -1)//非顶级功能并且选了具体的上级功能 { conditionStr += " AND f.ParentID='" + model.ParentFunctionID + "'"; } } else//非菜单,正常与Action绑定的功能 { if (model.IsTopFunction) { conditionStr += " AND f.ParentID IS NULL"; } //1.选择了所属区域、未选所属控制器与具体上级功能 if (model.AreaID != -1 && model.ControllerID == -1 && model.ParentFunctionID == -1) { conditionStr += " AND c.AreaID='" + model.AreaID + "'"; } //2.选择了具体的控制器,但是没选具体上级功能 else if (model.ControllerID != -1 && model.ParentFunctionID == -1) { conditionStr += " AND act.ControllerID='" + model.ControllerID + "'"; } //3.非顶级功能且选择了具体的上级功能 else if (model.ParentFunctionID != -1 && !model.IsTopFunction) { conditionStr += " AND f.ParentID='" + model.ParentFunctionID + "'"; } } if (model.IsAppFeatures) { conditionStr += " AND f.IsAppFeatures= 1"; } //if (model.IsMenu) //{ // if (model.ParentFunctionID == -1)//没选上级功能 // { // paras.Add(new SqlParameter("@conditionStr", "f.FunctionName LIKE '%" + model.FunctionName + "%' AND f.ActionID IS NULL")); // } // else // { // paras.Add(new SqlParameter("@conditionStr", "f.FunctionName LIKE '%" + model.FunctionName + "%' AND f.ActionID IS NULL AND f.ParentID='" + model.ParentFunctionID + "'")); // } //} //else if (model.AreaID != -1 && model.ControllerID == -1 && model.ParentFunctionID == -1)//选择了区域,没选控制器与上级功能 //{ // paras.Add(new SqlParameter("@conditionStr", "f.FunctionName LIKE '%" + model.FunctionName + "%' AND c.AreaID='" + model.AreaID + "'")); //} //else if (model.ControllerID != -1 && model.ParentFunctionID == -1)//选择了具体的控制器,但是没选上级功能 //{ // paras.Add(new SqlParameter("@conditionStr", "f.FunctionName LIKE '%" + model.FunctionName + "%' AND act.ControllerID='" + model.ControllerID + "'")); //} //else if (model.ParentFunctionID != -1)//选择了上级功能 //{ // paras.Add(new SqlParameter("@conditionStr", "f.FunctionName LIKE '%" + model.FunctionName + "%' AND f.ParentID='" + model.ParentFunctionID + "'")); //} //else//什么都没选 //{ // paras.Add(new SqlParameter("@conditionStr", "f.FunctionName LIKE '%" + model.FunctionName + "%'")); //} paras.Add(new SqlParameter("@conditionStr", conditionStr)); paras.Add(new SqlParameter() { ParameterName = "@totalItemCount", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); paras.Add(new SqlParameter() { ParameterName = "@newCurrentPage", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); List <FunctionListModel> list = ConvertToList <FunctionListModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray())); int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value); int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value); return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount)); }
public ActionResult RiskEarlyWarnToExcel(RiskEarlyWarnSeachModel model) { //model.PagedDatas = ReportBLL.GetPagedSpeed(model, 1, Int32.MaxValue, base.CurrentUserID); DataTable dt = new DataTable(); dt.Columns.Add("StrucName", typeof(string)); dt.Columns.Add("VehicleName", typeof(string)); dt.Columns.Add("GPSStartTime", typeof(string)); dt.Columns.Add("GPSEndTime", typeof(string)); dt.Columns.Add("Time", typeof(string)); dt.Columns.Add("DealUser", typeof(string)); dt.Columns.Add("DealTime", typeof(string)); dt.Columns.Add("DealInfo", typeof(string)); DataRow dr = dt.NewRow(); dr["StrucName"] = "南京亚士德"; dr["VehicleName"] = "京TEX008"; dr["GPSStartTime"] = "2016-10-10 15:04:20"; dr["GPSEndTime"] = "2016-10-10 15:05:32"; dr["Time"] = "1分12秒"; dr["DealUser"] = "******"; dr["DealTime"] = "2016-10-12 10:15:32"; dr["DealInfo"] = "超速"; dt.Rows.Add(dr); DataRow dr1 = dt.NewRow(); dr1["StrucName"] = "南京亚士德"; dr1["VehicleName"] = "亚A00001"; dr1["GPSStartTime"] = "2016-10-10 17:06:52"; dr1["GPSEndTime"] = "2016-10-10 17:08:01"; dr1["Time"] = "1分09秒"; dr1["DealUser"] = "******"; dr1["DealTime"] = "2016-10-12 10:15:32"; dr1["DealInfo"] = "滚筒停转"; dt.Rows.Add(dr1); DataRow dr2 = dt.NewRow(); dr2["StrucName"] = "南京亚士德"; dr2["VehicleName"] = "京TEX201"; dr2["GPSStartTime"] = "2016-10-10 17:06:52"; dr2["GPSEndTime"] = "2016-10-10 17:08:01"; dr2["Time"] = "1分09秒"; dr2["DealUser"] = "******"; dr2["DealTime"] = "2016-10-12 10:15:32"; dr2["DealInfo"] = "超速"; dt.Rows.Add(dr2); DataRow dr3 = dt.NewRow(); dr3["StrucName"] = "南京亚士德"; dr3["VehicleName"] = "汇A00002"; dr3["GPSStartTime"] = "2016-10-10 15:04:20"; dr3["GPSEndTime"] = "2016-10-10 15:05:32"; dr3["Time"] = "1分12秒"; dr3["DealUser"] = "******"; dr3["DealTime"] = "2016-10-12 10:15:32"; dr3["DealInfo"] = "超速"; dt.Rows.Add(dr3); List <RiskEarlyWarnListModel> list = ConvertToList <RiskEarlyWarnListModel> .Convert(dt); model.PagedDatas = list.ToPagedList(1, 1, 1); ViewBag.ExportFlag = true; ViewData.Model = model; string viewHtml = RenderPartialViewToString(this, "_RiskEarlyWarnGrid"); var reportName = this.GetUIText(base.GetFunctionsInfo("ReportManage", "RiskEarlyWarn", "SeachRiskEarlyWarn").FunctionName); //功能名称 return(File(System.Text.Encoding.UTF8.GetBytes(viewHtml), "application/ms-excel", string.Format("{0}.xls", reportName + "(" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ")"))); }
/// <summary> /// 获取区域信息下拉列表数据 /// 包含区域编号、区域名称 /// </summary> /// <returns></returns> public static List <AreaDDLModel> GetAreas() { string sql = "SELECT ID,AreaName FROM Areas"; return(ConvertToList <AreaDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
//3、通过车机信息正常计算里程 #region 链接服务器写在配置文件中 // public static async Task<ReturnData> GetMilieageByHistoryAsync(CLCS_MileageModel model) // { // //根据车牌号和开始、结束时间,分别去历史轨迹表中查询对应的数据,计算车机里程 // var linkedServer = WebConfigurationManager.AppSettings["linkedServer"]; // #region 根据车牌号查询历史轨迹数据 // //判断查询月表,分开查询时间点里程数据然后计算 // string st = model.StartTime.ToString("yyyyMM"); // string et = model.EndTime.ToString("yyyyMM"); // //考虑到数据库月表数据量比较大,这边直接查询出所有满足条件的数据,然后再计算里程数据 // //查询同一张月表中的数据,只要关联一张月表就可以了 // string sql = ""; // if (st == et) // { // sql = string.Format(@" SELECT ve.[PlateNum] AS VehicleCode,ve.VehicleName,sc.StrucName, //(SELECT StrucName FROM dbo.Structures WHERE ID=sc.ParentID) AS PStrucName,gn.Mileage ,gn.SignalDateTime // FROM dbo.Vehicles ve // INNER JOIN {0}.GNSS.dbo.[{1}] gn ON gn.VIN = ve.VIN // INNER JOIN dbo.Structures sc ON sc.ID = ve.StrucID // WHERE ve.PlateNum='{2}' AND gn.SignalDateTime>='{3}' AND gn.SignalDateTime<='{4}' // ORDER BY gn.SignalDateTime ", linkedServer, st, model.PlateNum, model.StartTime, model.EndTime); // } // else { // sql = string.Format(@" SELECT * FROM ( //SELECT ve.[PlateNum] AS VehicleCode,ve.VehicleName,sc.StrucName, //(SELECT StrucName FROM dbo.Structures WHERE ID=sc.ParentID) AS PStrucName,gn.Mileage ,gn.SignalDateTime // FROM dbo.Vehicles ve // INNER JOIN {0}.GNSS.dbo.[{1}] gn ON gn.VIN = ve.VIN // INNER JOIN dbo.Structures sc ON sc.ID = ve.StrucID // WHERE ve.PlateNum='{2}' AND gn.SignalDateTime>='{3}' // UNION ALL //SELECT ve.[PlateNum] AS VehicleCode,ve.VehicleName,sc.StrucName, //(SELECT StrucName FROM dbo.Structures WHERE ID=sc.ParentID) AS PStrucName,gn.Mileage,gn.SignalDateTime // FROM dbo.Vehicles ve // INNER JOIN {0}.GNSS.dbo.[{4}] gn ON gn.VIN = ve.VIN // INNER JOIN dbo.Structures sc ON sc.ID = ve.StrucID // WHERE ve.PlateNum='{2}' AND gn.SignalDateTime<='{5}' ) AS rs //ORDER BY rs.SignalDateTime", linkedServer, st, model.PlateNum, model.StartTime, et, model.EndTime); // } // var dt = await MSDBHelper.ExecuteDataTableAsync(sql, System.Data.CommandType.Text).ConfigureAwait(false); // var list = ConvertToList<SearchResultData>.Convert(dt); // if (list == null) // { // return null; // } // if (list.Count == 0) // { // return null; // } // var rs = new ReturnData(); // var count = list.Count; // rs.VehicleCode = list[0].VehicleCode; // rs.VehicleName = list[0].VehicleName; // rs.StrucName = list[0].StrucName; // rs.PStrucName = list[0].PStrucName; // rs.BeginTime = model.StartTime; // rs.EndTime = model.EndTime; // rs.BeginMilieage = list[0].Mileage; // rs.EndMilieage = list[count - 1].Mileage; // rs.ReturnMileage = rs.EndMilieage - rs.BeginMilieage; // return rs; // #endregion // } #endregion #region 读取终端表中的链接服务器 public static async Task <ReturnData> GetMilieageByHistoryAsync(CLCS_MileageModel model) { //根据车牌号和开始、结束时间,分别去历史轨迹表中查询对应的数据,计算车机里程 List <ServerInfoModel> slist = GetServerInfoByPlateNum(model.PlateNum); if (slist == null || slist.Count == 0) { return(null); } var linkedServer = slist[0].LinkedServerName; #region 根据车牌号查询历史轨迹数据 //判断查询月表,分开查询时间点里程数据然后计算 string st = model.StartTime.ToString("yyyyMM"); string et = model.EndTime.ToString("yyyyMM"); //考虑到数据库月表数据量比较大,这边直接查询出所有满足条件的数据,然后再计算里程数据 //查询同一张月表中的数据,只要关联一张月表就可以了 string sql = ""; if (st == et) { sql = string.Format(@" SELECT ve.[PlateNum] AS VehicleCode,ve.VehicleName,sc.StrucName, (SELECT StrucName FROM dbo.Structures WHERE ID=sc.ParentID) AS PStrucName,gn.Mileage ,gn.SignalDateTime FROM dbo.Vehicles ve INNER JOIN {0}.GNSS.dbo.[{1}] gn ON gn.VIN = ve.VIN INNER JOIN dbo.Structures sc ON sc.ID = ve.StrucID WHERE ve.PlateNum='{2}' AND gn.SignalDateTime>='{3}' AND gn.SignalDateTime<='{4}' ORDER BY gn.SignalDateTime ", linkedServer, st, model.PlateNum, model.StartTime, model.EndTime); } else { sql = string.Format(@" SELECT * FROM ( SELECT ve.[PlateNum] AS VehicleCode,ve.VehicleName,sc.StrucName, (SELECT StrucName FROM dbo.Structures WHERE ID=sc.ParentID) AS PStrucName,gn.Mileage ,gn.SignalDateTime FROM dbo.Vehicles ve INNER JOIN {0}.GNSS.dbo.[{1}] gn ON gn.VIN = ve.VIN INNER JOIN dbo.Structures sc ON sc.ID = ve.StrucID WHERE ve.PlateNum='{2}' AND gn.SignalDateTime>='{3}' UNION ALL SELECT ve.[PlateNum] AS VehicleCode,ve.VehicleName,sc.StrucName, (SELECT StrucName FROM dbo.Structures WHERE ID=sc.ParentID) AS PStrucName,gn.Mileage,gn.SignalDateTime FROM dbo.Vehicles ve INNER JOIN {0}.GNSS.dbo.[{4}] gn ON gn.VIN = ve.VIN INNER JOIN dbo.Structures sc ON sc.ID = ve.StrucID WHERE ve.PlateNum='{2}' AND gn.SignalDateTime<='{5}' ) AS rs ORDER BY rs.SignalDateTime", linkedServer, st, model.PlateNum, model.StartTime, et, model.EndTime); } var dt = await MSDBHelper.ExecuteDataTableAsync(sql, System.Data.CommandType.Text).ConfigureAwait(false); var list = ConvertToList <SearchResultData> .Convert(dt); if (list == null) { return(null); } if (list.Count == 0) { return(null); } var rs = new ReturnData(); var count = list.Count; rs.VehicleCode = list[0].VehicleCode; rs.VehicleName = list[0].VehicleName; rs.StrucName = list[0].StrucName; rs.PStrucName = list[0].PStrucName; rs.BeginTime = model.StartTime; rs.EndTime = model.EndTime; rs.BeginMilieage = list[0].Mileage; rs.EndMilieage = list[count - 1].Mileage; rs.ReturnMileage = rs.EndMilieage - rs.BeginMilieage; return(rs); #endregion }
public ActionResult AbnormalRouteToExcel(AbnormalRouteSearchModel model) { //model.PagedDatas = ReportBLL.GetPagedSpeed(model, 1, Int32.MaxValue, base.CurrentUserID); DataTable dt = new DataTable(); dt.Columns.Add("StrucName", typeof(string)); dt.Columns.Add("VehicleName", typeof(string)); dt.Columns.Add("PathName", typeof(string)); dt.Columns.Add("ExceptionTypeName", typeof(string)); dt.Columns.Add("GPSStartTime", typeof(string)); dt.Columns.Add("GPSEndTime", typeof(string)); dt.Columns.Add("Time", typeof(string)); DataRow dr = dt.NewRow(); dr["StrucName"] = "南京亚士德"; dr["VehicleName"] = "京TEX008"; dr["PathName"] = "软件大道-将军山"; dr["ExceptionTypeName"] = "路段超速"; dr["GPSStartTime"] = "2016-10-10 15:04:20"; dr["GPSEndTime"] = "2016-10-10 15:05:32"; dr["Time"] = "1分12秒"; dt.Rows.Add(dr); DataRow dr1 = dt.NewRow(); dr1["StrucName"] = "南京亚士德"; dr1["VehicleName"] = "亚A00001"; dr1["PathName"] = "鸡鸣寺-中华门"; dr1["ExceptionTypeName"] = "进路线"; dr1["GPSStartTime"] = "2016-10-10 17:06:52"; dr1["GPSEndTime"] = "2016-10-10 17:08:01"; dr1["Time"] = "1分09秒"; dt.Rows.Add(dr1); DataRow dr2 = dt.NewRow(); dr2["StrucName"] = "南京亚士德"; dr2["VehicleName"] = "京TEX201"; dr2["PathName"] = "软件大道-将军山"; dr2["ExceptionTypeName"] = "出路线"; dr2["GPSStartTime"] = "2016-10-10 17:06:52"; dr2["GPSEndTime"] = "2016-10-10 17:08:01"; dr2["Time"] = "1分09秒"; dt.Rows.Add(dr2); DataRow dr3 = dt.NewRow(); dr3["StrucName"] = "南京亚士德"; dr3["VehicleName"] = "汇A00002"; dr3["PathName"] = "鸡鸣寺-中华门"; dr3["ExceptionTypeName"] = "出路线"; dr3["GPSStartTime"] = "2016-10-10 15:04:20"; dr3["GPSEndTime"] = "2016-10-10 15:05:32"; dr3["Time"] = "1分12秒"; dt.Rows.Add(dr3); List <AbnormalRouteListModel> list = ConvertToList <AbnormalRouteListModel> .Convert(dt); model.PagedDatas = list.ToPagedList(1, 1, 1); ViewBag.ExportFlag = true; ViewData.Model = model; string viewHtml = RenderPartialViewToString(this, "_AbnormalRouteGrid"); var reportName = this.GetUIText(base.GetFunctionsInfo("ReportManage", "AbnormalRoute", "SeachAbnormalRoute").FunctionName); //功能名称 return(File(System.Text.Encoding.UTF8.GetBytes(viewHtml), "application/ms-excel", string.Format("{0}.xls", reportName + "(" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ")"))); }
public ActionResult GetOverSpeedInfo(ReportSearchModels model) { #region 老版本 // SearchDataWithPagedDatas<ReportSearchModels, ReportListModel> result = //new SearchDataWithPagedDatas<ReportSearchModels, ReportListModel>(); // result.SearchModel = model; // result.PagedDatas = ReportBLL.GetPagedSpeed(model, searchPage, this.PageSize); // return PartialView("_SpeedReportDetailGrid", result); #endregion //model.GPSStartTime = "2016-10-10 00:00:00"; //model.GPSEndTime = "2016-10-10 23:59:59"; //model.PagedDatas = ReportBLL.GetPagedSpeed(model, model.SearchPage, this.PageSize, base.CurrentUserID); DataTable dt = new DataTable(); dt.Columns.Add("StrucName", typeof(string)); dt.Columns.Add("VehicleName", typeof(string)); dt.Columns.Add("GPSStartTime", typeof(string)); dt.Columns.Add("GPSEndTime", typeof(string)); dt.Columns.Add("Time", typeof(string)); DataRow dr = dt.NewRow(); dr["StrucName"] = "南京亚士德"; dr["VehicleName"] = "京TEX008"; dr["GPSStartTime"] = "2016-10-10 15:04:20"; dr["GPSEndTime"] = "2016-10-10 15:05:32"; dr["Time"] = "1分12秒"; dt.Rows.Add(dr); DataRow dr1 = dt.NewRow(); dr1["StrucName"] = "南京亚士德"; dr1["VehicleName"] = "亚A00001"; dr1["GPSStartTime"] = "2016-10-10 17:06:52"; dr1["GPSEndTime"] = "2016-10-10 17:08:01"; dr1["Time"] = "1分09秒"; dt.Rows.Add(dr1); DataRow dr2 = dt.NewRow(); dr2["StrucName"] = "南京亚士德"; dr2["VehicleName"] = "京TEX201"; dr2["GPSStartTime"] = "2016-10-10 17:06:52"; dr2["GPSEndTime"] = "2016-10-10 17:08:01"; dr2["Time"] = "1分09秒"; dt.Rows.Add(dr2); DataRow dr3 = dt.NewRow(); dr3["StrucName"] = "南京亚士德"; dr3["VehicleName"] = "京TEX008"; dr3["GPSStartTime"] = "2016-10-10 15:04:20"; dr3["GPSEndTime"] = "2016-10-10 15:05:32"; dr3["Time"] = "1分12秒"; dt.Rows.Add(dr3); DataRow dr4 = dt.NewRow(); dr4["StrucName"] = "南京亚士德"; dr4["VehicleName"] = "亚A00001"; dr4["GPSStartTime"] = "2016-10-10 17:06:52"; dr4["GPSEndTime"] = "2016-10-10 17:08:01"; dr4["Time"] = "1分09秒"; dt.Rows.Add(dr4); DataRow dr5 = dt.NewRow(); dr5["StrucName"] = "南京亚士德"; dr5["VehicleName"] = "京TEX201"; dr5["GPSStartTime"] = "2016-10-10 17:06:52"; dr5["GPSEndTime"] = "2016-10-10 17:08:01"; dr5["Time"] = "1分09秒"; dt.Rows.Add(dr5); DataRow dr6 = dt.NewRow(); dr6["StrucName"] = "南京亚士德"; dr6["VehicleName"] = "京TEX008"; dr6["GPSStartTime"] = "2016-10-10 15:04:20"; dr6["GPSEndTime"] = "2016-10-10 15:05:32"; dr6["Time"] = "1分12秒"; dt.Rows.Add(dr6); //DataRow dr7 = dt.NewRow(); //dr7["StrucName"] = "南京亚士德"; //dr7["VehicleName"] = "亚A00001"; //dr7["GPSStartTime"] = "2016-10-10 17:06:52"; //dr7["GPSEndTime"] = "2016-10-10 17:08:01"; //dr7["Time"] = "1分09秒"; //dt.Rows.Add(dr7); //DataRow dr8 = dt.NewRow(); //dr8["StrucName"] = "南京亚士德"; //dr8["VehicleName"] = "京TEX201"; //dr8["GPSStartTime"] = "2016-10-10 17:06:52"; //dr8["GPSEndTime"] = "2016-10-10 17:08:01"; //dr8["Time"] = "1分09秒"; //dt.Rows.Add(dr8); //DataRow dr9 = dt.NewRow(); //dr9["StrucName"] = "南京亚士德"; //dr9["VehicleName"] = "京TEX008"; //dr9["GPSStartTime"] = "2016-10-10 15:04:20"; //dr9["GPSEndTime"] = "2016-10-10 15:05:32"; //dr9["Time"] = "1分12秒"; //dt.Rows.Add(dr9); //DataRow dr10 = dt.NewRow(); //dr10["StrucName"] = "南京亚士德"; //dr10["VehicleName"] = "亚A00001"; //dr10["GPSStartTime"] = "2016-10-10 17:06:52"; //dr10["GPSEndTime"] = "2016-10-10 17:08:01"; //dr10["Time"] = "1分09秒"; //dt.Rows.Add(dr10); //DataRow dr11 = dt.NewRow(); //dr11["StrucName"] = "南京亚士德"; //dr11["VehicleName"] = "京TEX201"; //dr11["GPSStartTime"] = "2016-10-10 17:06:52"; //dr11["GPSEndTime"] = "2016-10-10 17:08:01"; //dr11["Time"] = "1分09秒"; //dt.Rows.Add(dr11); List <ReportListModel> list = ConvertToList <ReportListModel> .Convert(dt); model.PagedDatas = list.ToPagedList(1, 1, 1); return(PartialView("_SpeedReportDetailGrid", model)); }
public static AsiatekPagedList <ElectricFencePropertyListModel> GetPagedEFPropertys(ElectricFencePropertySearchModel model, int searchPage, int pageSize, int strucID) { #region 是否属于单位亚士德,是则查所有,否则查询前用户所属单位的非亚士德上级以及所有其子单位 string isSql = @" SELECT ParentID FROM dbo.Structures WHERE ID =@StrucID "; List <SqlParameter> isParas = new List <SqlParameter>() { new SqlParameter("@StrucID", SqlDbType.Int), }; isParas[0].Value = strucID; var isResult = MSSQLHelper.ExecuteScalar(CommandType.Text, isSql, isParas.ToArray()); #endregion List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "dbo.ElectricFenceProperty efp "), new SqlParameter("@joinStr", @" INNER JOIN dbo.Users AS us ON us.ID = efp.CreateUser "), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "efp.CreateTime DESC"), new SqlParameter("@showColumns", @"efp.ID,efp.PropertyName,efp.FenceState,efp.ValidStartTime,efp.ValidEndTime, efp.AlarmType,efp.IsSpeed,efp.MaxSpeed,efp.IsPeriod "), }; string conditionStr = " "; if (isResult != DBNull.Value) { conditionStr = "efp.Status<>9 AND us.StrucID IN (SELECT ID FROM Func_GetAllSubStructsAndParentStructsByStrucID(" + strucID + ") WHERE ParentID IS NOT NULL) "; } else { conditionStr = "efp.Status<>9 "; } if (!string.IsNullOrWhiteSpace(model.PropertyName)) { conditionStr += " AND efp.PropertyName LIKE '%" + model.PropertyName + "%'"; } if (!string.IsNullOrWhiteSpace(conditionStr)) { paras.Add(new SqlParameter("@conditionStr", conditionStr)); } paras.Add(new SqlParameter() { ParameterName = "@totalItemCount", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); paras.Add(new SqlParameter() { ParameterName = "@newCurrentPage", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); var rs = MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray()); List <ElectricFencePropertyListModel> list = ConvertToList <ElectricFencePropertyListModel> .Convert(rs); int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value); int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value); return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount)); }
/// <summary> /// 获取驾照状态信息 /// </summary> /// <returns></returns> public static List <DriveLicenseStateSelectModel> GetDriveLicenseStates() { string sql = "SELECT * FROM dbo.DriveLicenseState WHERE Status=0"; return(ConvertToList <DriveLicenseStateSelectModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
public static AsiatekPagedList <UserListModel> GetPagedUserInfo(UserSearchModel model, int searchPage, int pageSize, bool isSuperAdmin) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "Users u"), new SqlParameter("@joinStr", "INNER JOIN Structures s ON u.StrucID=s.ID"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "u.ID"), new SqlParameter("@showColumns", "u.ID,u.UserName,u.NickName,s.StrucName AS SubordinateStrucName,u.VehicleViewMode"), }; string conditionStr = "u.Status<>9"; if (!isSuperAdmin)//不是超级管理员只查询角色等级大于等于3的用户 { conditionStr += " AND u.RoleID<>1 AND u.RoleID<>2"; } bool b1 = !string.IsNullOrWhiteSpace(model.UserName); bool b2 = !string.IsNullOrWhiteSpace(model.NickName); if (b1 && b2)//条件同时包含用户名和昵称 { conditionStr += " AND (u.UserName LIKE '%" + model.UserName + "%' OR u.NickName LIKE '%" + model.NickName + "%')"; } else if (b1)//只有用户名 { conditionStr += " AND u.UserName LIKE '%" + model.UserName + "%'"; } else if (b2)//只有昵称 { conditionStr += " AND u.NickName LIKE '%" + model.NickName + "%'"; } if (model.StrucID != -1)//如果选择了具体的隶属单位 { conditionStr += " AND u.StrucID=" + model.StrucID + ""; } if (model.VehicleViewMode != -1) { conditionStr += " AND u.VehicleViewMode=" + model.VehicleViewMode + ""; } paras.Add(new SqlParameter("@conditionStr", conditionStr)); paras.Add(new SqlParameter() { ParameterName = "@totalItemCount", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); paras.Add(new SqlParameter() { ParameterName = "@newCurrentPage", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); List <UserListModel> list = ConvertToList <UserListModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray())); int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value); int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value); return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount)); }
/// <summary> /// 获取员工信息分页数据 /// </summary> /// <param name="model"></param> /// <param name="searchPage"></param> /// <param name="pageSize"></param> /// <returns></returns> public static AsiatekPagedList <EmployeeInfoListModel> GetPagedEmployeeInfos(EmployeeInfoSearchModel model, int searchPage, int pageSize) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "EmployeeInfo E"), new SqlParameter("@joinStr", @" LEFT JOIN (SELECT * FROM dbo.DriveType WHERE Status=0) DT ON E.DriveTypeID=DT.DriveTypeID LEFT JOIN (SELECT * FROM dbo.DriveLicenseState WHERE Status=0) DLS ON E.DriveLicenseStateID=DLS.DriveLicenseStateID "), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "E.ID DESC"), new SqlParameter("@showColumns", @"E.ID, E.EmployeeID, E.EmployeeName, E.EmployeeGender, E.CertificateCode, E.ContactPhone, E.EmergePhone, E.IsDriver, E.IsCarrier, DT.DriveTypeID, DT.DriveTypeName, DLS.DriveLicenseStateID, DLS.DriveLicenseStateName "), }; #region 筛选条件 string conditionStr = "E.Status=0"; if (!string.IsNullOrWhiteSpace(model.EmployeeID)) { conditionStr += " AND E.EmployeeID LIKE '%" + model.EmployeeID + "%'"; } if (!string.IsNullOrWhiteSpace(model.EmployeeName)) { conditionStr += " AND E.EmployeeName LIKE '%" + model.EmployeeName + "%'"; } if (model.DriveLicenseStateID != -1) { conditionStr += " AND DLS.DriveLicenseStateID =" + model.DriveLicenseStateID + ""; } if (!string.IsNullOrWhiteSpace(conditionStr)) { paras.Add(new SqlParameter("@conditionStr", conditionStr)); } #endregion paras.Add(new SqlParameter() { ParameterName = "@totalItemCount", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); paras.Add(new SqlParameter() { ParameterName = "@newCurrentPage", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); List <EmployeeInfoListModel> list = ConvertToList <EmployeeInfoListModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray())); int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value); int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value); return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount)); }
/// <summary> /// 获取证件类型信息 /// </summary> /// <returns></returns> public static List <CertificateTypeSelectModel> GetCertificateTypes() { string sql = "SELECT * FROM dbo.CertificateType WHERE Status=0"; return(ConvertToList <CertificateTypeSelectModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
/// <summary> /// 获取员工信息分页数据 /// </summary> /// <param name="model"></param> /// <param name="searchPage"></param> /// <param name="pageSize"></param> /// <returns></returns> public static AsiatekPagedList <EmployeeInfoPageModel> GetPagedEmployeeInfo_New(EmployeeInfoFindModel model, int searchPage, int pageSize, int strucID) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "EmployeeInfo AS E"), new SqlParameter("@joinStr", @" INNER JOIN Func_GetStrucAndSubStrucByUserAffiliatedStrucID(" + strucID + ") AS S ON E.StrucID = S.ID"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "E.ID DESC"), new SqlParameter("@showColumns", @"E.ID,E.EmployeeName,E.EmployeeGender,E.IsDriver,E.IsCarrier, E.CertificateCode,E.ContactPhone,E.EmergePhone,S.StrucName"), }; #region 筛选条件 string conditionStr = "E.Status=0"; if (!string.IsNullOrWhiteSpace(model.EmployeeName)) { conditionStr += " AND E.EmployeeName LIKE '%" + model.EmployeeName + "%'"; } if (!string.IsNullOrWhiteSpace(model.CertificateCode)) { conditionStr += " AND E.CertificateCode LIKE '%" + model.CertificateCode + "%'"; } if (!string.IsNullOrWhiteSpace(model.ContactPhone)) { conditionStr += " AND E.ContactPhone LIKE '%" + model.ContactPhone + "%'"; } if (model.IsCarriers != -1) { conditionStr += " AND E.IsCarrier LIKE '%" + model.IsCarriers + "%'"; } if (model.IsDrivers != -1) { conditionStr += " AND E.IsDriver LIKE '%" + model.IsDrivers + "%'"; } if (model.SearchStrucID.HasValue) { conditionStr += " AND E.StrucID = " + model.SearchStrucID + ""; } paras.Add(new SqlParameter("@conditionStr", conditionStr)); #endregion paras.Add(new SqlParameter() { ParameterName = "@totalItemCount", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); paras.Add(new SqlParameter() { ParameterName = "@newCurrentPage", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); List <EmployeeInfoPageModel> list = ConvertToList <EmployeeInfoPageModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray())); int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value); int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value); return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount)); }
public static List <MaintenanceCheckAlarmModel> GetDefaultMaintenanceCheckAlarmModel(int strucID) { //alarmMile=设置里程数-(当前里程数-初始里程数)%设定里程数 //alarmTime=设置天数-(初试时间和信号时间的间隔天数)%设置天数 //alarmMile<=提前提醒里程数,alarmTime<=提前提醒天数,车辆保养预警提醒 //因为sg.Mileage、vm.FirstMaintenanceMile是浮点数,ms.SettingMile是整数,数据类型 float 和 int 在 modulo 运算符中不兼容,两个float也无法运算,所以将前者转化成int型,四舍五入,会出现一点偏差 string sql = string.Format(@" SELECT vm.VehicleID,vm.ScheduleID,vm.FirstMaintenanceMile,vm.FirstMaintenanceTime, ms.ScheduleName,ms.RulesType,ms.SettingMile,ms.PreMile,ms.SettingDay,ms.PreDay,userVehicles.VehicleName,userVehicles.VIN,userVehicles.StrucName, CASE ms.RulesType WHEN 1 THEN ms.SettingMile-CAST((sg.Mileage-vm.FirstMaintenanceMile) AS INT)%ms.SettingMile WHEN 3 THEN ms.SettingMile-CAST((sg.Mileage-vm.FirstMaintenanceMile) AS INT)%ms.SettingMile END AS alarmMile, CASE ms.RulesType WHEN 2 THEN ms.SettingDay-(SELECT DATEDIFF(DAY,vm.FirstMaintenanceTime,sg.SignalDateTime))%ms.SettingDay WHEN 3 THEN ms.SettingDay-(SELECT DATEDIFF(DAY,vm.FirstMaintenanceTime,sg.SignalDateTime))%ms.SettingDay END AS alarmTime FROM dbo.VehicleMaintenance vm INNER JOIN dbo.MaintenanceSchedule ms ON ms.ID=vm.ScheduleID INNER JOIN ( SELECT vt.VID AS ID,vt.VehicleName,vt.VIN,s.StrucName FROM dbo.Structures s INNER JOIN Func_GetAllTheSubsetOfVehiclesByStrucID({0}) AS vt ON s.ID=vt.StrucID ) AS userVehicles ON vm.VehicleID=userVehicles.ID INNER JOIN [dbo].[VW_GetRealTimeSignals] sg ON sg.VIN = userVehicles.VIN ", strucID); List <MaintenanceCheckAlarmModel> list = ConvertToList <MaintenanceCheckAlarmModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)); List <MaintenanceCheckAlarmModel> result = new List <MaintenanceCheckAlarmModel>(); if (list == null || list.Count <= 0) { return(null); } else { //如果既设定了里程周期又设定了时间周期,只要有一个有预警就进行提醒,两者都有预警就都提醒 foreach (var model in list) { //里程预警提醒的 if (model.RulesType == 1 && model.alarmMile != null && model.alarmMile <= model.PreMile) { model.AlarmInfo = @DisplayText.Mileage2 + @UIText.MaintenanceCheckAlarm; result.Add(model); } //时间预警提醒的 if (model.RulesType == 2 && model.alarmTime != null && model.alarmTime <= model.PreDay) { model.AlarmInfo = @DisplayText.Time + @UIText.MaintenanceCheckAlarm; result.Add(model); } //里程或时间预警提醒的 if (model.RulesType == 3) { //两者都有 if (model.alarmMile != null && model.alarmTime != null && model.alarmMile <= model.PreMile && model.alarmTime <= model.PreDay) { model.AlarmInfo = @DisplayText.Mileage2 + @DisplayText.And + @DisplayText.Time + @UIText.MaintenanceCheckAlarm; result.Add(model); } //只有里程预警 else if (model.alarmMile != null && model.alarmMile <= model.PreMile) { model.AlarmInfo = @DisplayText.Mileage2 + @UIText.MaintenanceCheckAlarm; result.Add(model); } //只有时间预警 else if (model.alarmTime != null && model.alarmTime <= model.PreDay) { model.AlarmInfo = @DisplayText.Time + @UIText.MaintenanceCheckAlarm; result.Add(model); } } } } return(result); }
public static List <MaintenanceCheckAlarmModel> GetMaintenanceCheckAlarm(int userID) { string sql = string.Format(@"SELECT vm.VehicleID,vm.ScheduleID,vm.FirstMaintenanceMile,vm.FirstMaintenanceTime, ms.ScheduleName,ms.RulesType,ms.SettingMile,ms.PreMile,ms.SettingDay,ms.PreDay,v.VehicleName,v.VIN,s.StrucName, CASE ms.RulesType WHEN 1 THEN ms.SettingMile-CAST((sg.Mileage-vm.FirstMaintenanceMile) AS INT)%ms.SettingMile WHEN 3 THEN ms.SettingMile-CAST((sg.Mileage-vm.FirstMaintenanceMile) AS INT)%ms.SettingMile END AS alarmMile, CASE ms.RulesType WHEN 2 THEN ms.SettingDay-(SELECT DATEDIFF(DAY,vm.FirstMaintenanceTime,sg.SignalDateTime))%ms.SettingDay WHEN 3 THEN ms.SettingDay-(SELECT DATEDIFF(DAY,vm.FirstMaintenanceTime,sg.SignalDateTime))%ms.SettingDay END AS alarmTime FROM dbo.VehicleMaintenance vm INNER JOIN dbo.MaintenanceSchedule ms ON ms.ID=vm.ScheduleID INNER JOIN dbo.Vehicles v ON v.ID = vm.VehicleID INNER JOIN [dbo].[VW_GetRealTimeSignals] sg ON sg.VIN = v.VIN LEFT JOIN dbo.Structures s on v.StrucID=s.ID WHERE v.ID in (SELECT vt.VID FROM dbo.Structures s INNER JOIN Func_GetVehiclesListByUserID_New({0}) AS vt ON s.ID=vt.StrucID)", userID); List <MaintenanceCheckAlarmModel> list = ConvertToList <MaintenanceCheckAlarmModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)); List <MaintenanceCheckAlarmModel> result = new List <MaintenanceCheckAlarmModel>(); if (list == null || list.Count <= 0) { return(null); } else { //如果既设定了里程周期又设定了时间周期,只要有一个有预警就进行提醒,两者都有预警就都提醒 foreach (var model in list) { //里程预警提醒的 if (model.RulesType == 1 && model.alarmMile != null && model.alarmMile <= model.PreMile) { model.AlarmInfo = @DisplayText.Mileage2 + @UIText.MaintenanceCheckAlarm; result.Add(model); } //时间预警提醒的 if (model.RulesType == 2 && model.alarmTime != null && model.alarmTime <= model.PreDay) { model.AlarmInfo = @DisplayText.Time + @UIText.MaintenanceCheckAlarm; result.Add(model); } //里程或时间预警提醒的 if (model.RulesType == 3) { //只有里程预警 if (model.alarmMile != null && model.alarmTime == null && model.alarmMile <= model.PreMile) { model.AlarmInfo = @DisplayText.Mileage2 + @UIText.MaintenanceCheckAlarm; result.Add(model); } //只有时间预警 if (model.alarmTime != null && model.alarmMile == null && model.alarmTime <= model.PreDay) { model.AlarmInfo = @DisplayText.Time + @UIText.MaintenanceCheckAlarm; result.Add(model); } //两者都有 if (model.alarmMile != null && model.alarmTime != null && model.alarmMile <= model.PreMile && model.alarmTime <= model.PreDay) { model.AlarmInfo = @DisplayText.Mileage2 + @DisplayText.And + @DisplayText.Time + @UIText.MaintenanceCheckAlarm; result.Add(model); } } } } return(result); }
/// <summary> /// 获取控制器下拉列表信息 /// 包含控制器编号、控制器名称 /// </summary> /// <returns></returns> public static List <ControllerDDLModel> GetControllers() { string sql = "SELECT ID,ControllerName FROM Controllers"; return(ConvertToList <ControllerDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
public static List <ServerInfoDDLModel> GetServerInfoList() { string sql = "SELECT [ID],[ServerName] FROM [dbo].[ServerInfo]"; return(ConvertToList <ServerInfoDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
/// <summary> /// 根据用户ID获取用户分配的车辆的最新信号 自由模式 /// </summary> /// <param name="userID"></param> /// <returns></returns> public static List <RealTimeSignalTreeModel> GetRealTimeSignals(int userID) { #region 获取服务器名 List <ServerInfoModel> linkName_list = ReportBLL.GetServerInfo(userID, 0); // string linkName_sql = @" SELECT DISTINCT sv.LinkedServerName FROM dbo.Vehicles ve // INNER JOIN dbo.Terminals tt ON tt.LinkedVehicleID = ve.ID // INNER JOIN dbo.ServerInfo sv ON sv.ID = tt.ServerInfoID // WHERE ve.Status<>9 "; // var linkName_list = ConvertToList<GetLinkedServerName>.Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, linkName_sql)); if (linkName_list == null || linkName_list.Count == 0) { return(null); } #endregion string sql = ""; string linkedServerName = ""; //SqlParameter[] paras = new SqlParameter[2*linkName_list.Count]; for (int i = 0; i < linkName_list.Count; i++) { linkedServerName = linkName_list[i].LinkedServerName; // sql += string.Format(@"SELECT outerTB.ID AS VID,outerTB.VehicleName AS VN,outerTB.StrucID AS SID,outerTB.StrucName AS SN,outerTB.VIN, // outerTB.ParentID AS SPID,CASE WHEN DATEDIFF(MINUTE,sg.SignalDateTime,GETDATE())>10 THEN 0 // ELSE 1 END AS IsOnline,CASE WHEN sg.Speed >0 THEN 1 ELSE 0 END AS IsRunning, // sg.Latitude AS Latitude,sg.Longitude AS Longitude FROM {0}.GNSS.dbo.Signals sg WITH(NOLOCK) // INNER JOIN // ( // SELECT innerTB.*,s.StrucName,s.ParentID,t.TerminalCode FROM dbo.Structures s WITH(NOLOCK) // INNER JOIN // ( // SELECT v.ID,v.VehicleName,v.StrucID,v.VIN FROM dbo.Vehicles v WITH(NOLOCK) INNER JOIN // ( // SELECT StrucID FROM dbo.StructureDistributionInfo WITH(NOLOCK) // WHERE UserID={1}) AS temp1 ON v.StrucID=temp1.StrucID // WHERE v.Status=0 AND v.IsReceived=1 // UNION // SELECT v.ID,v.VehicleName,v.StrucID,v.VIN FROM dbo.Vehicles v WITH(NOLOCK) INNER JOIN // ( // SELECT VehicleID FROM dbo.VehicleDistributionInfo WITH(NOLOCK) // WHERE UserID={1}) AS temp1 ON v.ID=temp1.VehicleID // WHERE v.Status=0 AND v.IsReceived=1 // ) AS innerTB ON s.ID=innerTB.StrucID // INNER JOIN dbo.Terminals t WITH(NOLOCK) ON innerTB.ID=t.LinkedVehicleID // INNER JOIN dbo.ServerInfo sv ON sv.ID = t.ServerInfoID // WHERE sv.LinkedServerName= '{0}' // )AS outerTB // ON sg.VIN=outerTB.VIN ", linkedServerName, userID); sql += string.Format(@"SELECT outerTB.VID,outerTB.VehicleName AS VN,outerTB.StrucID AS SID,outerTB.StrucName AS SN,outerTB.VIN, outerTB.ParentID AS SPID,CASE WHEN DATEDIFF(MINUTE,sg.SignalDateTime,GETDATE())>10 THEN 0 ELSE 1 END AS IsOnline,CASE WHEN sg.Speed >0 THEN 1 ELSE 0 END AS IsRunning, sg.Latitude AS Latitude,sg.Longitude AS Longitude FROM {0}.GNSS.dbo.Signals sg WITH(NOLOCK) INNER JOIN ( SELECT innerTB.*,s.StrucName,s.ParentID,t.TerminalCode FROM dbo.Structures s WITH(NOLOCK) INNER JOIN Func_GetVehiclesListByUserID_New({1}) AS innerTB ON s.ID=innerTB.StrucID INNER JOIN dbo.Terminals t WITH(NOLOCK) ON innerTB.VID=t.LinkedVehicleID INNER JOIN dbo.ServerInfo sv ON sv.ID = t.ServerInfoID WHERE sv.LinkedServerName= '{0}' ) AS outerTB ON sg.VIN=outerTB.VIN ", linkedServerName, userID); if (i != linkName_list.Count - 1) { sql += " UNION "; } //paras[2*i] = new SqlParameter() //{ // ParameterName = "@userID", // Value = userID, // SqlDbType = SqlDbType.Int //}; //paras[2 * i + 1] = new SqlParameter() //{ // ParameterName = "@LinkedServerName", // Value = linkedServerName, // SqlDbType = SqlDbType.VarChar //}; } return(ConvertToList <RealTimeSignalTreeModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
public static AsiatekPagedList <TerminalTypeListModel> GetPagedTerminalTypes(TerminalTypeSearchModel model, int searchPage, int pageSize) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "TerminalTypes tt"), new SqlParameter("@joinStr", "INNER JOIN TerminalManufacturer tm ON tt.TerminalManufacturerID = tm.ID"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "tt.ID"), new SqlParameter("@showColumns", @"tt.ID ,tt.CommunicationMode, tt.TerminalName , tt.ACCOFF_Frequency , tt.ACCON_Frequency , tt.Filter , tm.ManufacturerName"), }; string conditionStr = string.Empty; if (!string.IsNullOrWhiteSpace(model.TerminalName)) { conditionStr += "tt.TerminalName LIKE '%" + model.TerminalName + "%'"; } if (model.TerminalManufacturerID != -1) { if (string.IsNullOrWhiteSpace(conditionStr)) { conditionStr += "tt.TerminalManufacturerID=" + model.TerminalManufacturerID + ""; } else { conditionStr += " AND tt.TerminalManufacturerID=" + model.TerminalManufacturerID + ""; } } if (!string.IsNullOrWhiteSpace(conditionStr)) { paras.Add(new SqlParameter("@conditionStr", conditionStr)); } paras.Add(new SqlParameter() { ParameterName = "@totalItemCount", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); paras.Add(new SqlParameter() { ParameterName = "@newCurrentPage", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); List <TerminalTypeListModel> list = ConvertToList <TerminalTypeListModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray())); int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value); int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value); return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount)); }
public static SelectResult <TemperatureAlarmRuleEditModel> GetTemperatureAlarmRuleByID(int id) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@ID", SqlDbType.Int), }; paras[0].Value = id; string sql = @"SELECT tar.ID,tar.Name,tar.[Description],tar.AffiliatedStrucID,struc.StrucName AS AffiliatedStrucName, CAST(tarp.StartTime AS CHAR(8)) AS StartTime,CAST(tarp.EndTime AS CHAR(8)) AS EndTime, tard.SensorCode,tard.InstallationPosition,tard.LowestTemperature,tard.HighestTemperature,tard.[Enabled],s.SensorName FROM dbo.TemperatureAlarmRules tar INNER JOIN dbo.TemperatureAlarmRuleDetails tard ON tar.ID=tard.TemperatureAlarmRuleID INNER JOIN dbo.TemperatureAlarmRuleTimePeriods tarp ON tar.ID=tarp.TemperatureAlarmRuleID INNER JOIN dbo.Structures struc ON tar.AffiliatedStrucID=struc.ID INNER JOIN dbo.SensorList s ON s.SensorCode=tard.SensorCode WHERE tar.ID=@ID ORDER BY tard.SensorCode ASC"; var list = ConvertToList <TemperatureAlarmRuleEditFlatModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); TemperatureAlarmRuleEditModel data = null; string msg = string.Empty; if (list == null) { msg = PromptInformation.DBError; } else if (list.Count == 0) { msg = PromptInformation.NotExists; } else { var result = from item in list group item by new { item.ID, item.Name, item.Description, item.AffiliatedStrucID, item.AffiliatedStrucName, item.StartTime, item.EndTime } into groupedResult select new TemperatureAlarmRuleEditModel { ID = groupedResult.Key.ID, Name = groupedResult.Key.Name, Description = groupedResult.Key.Description, AffiliatedStrucID = groupedResult.Key.AffiliatedStrucID, AffiliatedStrucName = groupedResult.Key.AffiliatedStrucName, StartTime = groupedResult.Key.StartTime, EndTime = groupedResult.Key.EndTime, TemperatureAlarmRuleDetails = (from subItem in list where subItem.ID == groupedResult.Key.ID select new TemperatureAlarmRuleDetailModel { Enabled = subItem.Enabled, SensorCode = subItem.SensorCode, SensorName = subItem.SensorName, HighestTemperature = subItem.HighestTemperature, LowestTemperature = subItem.LowestTemperature, InstallationPosition = subItem.InstallationPosition }).ToList() }; data = result.FirstOrDefault(); } return(new SelectResult <TemperatureAlarmRuleEditModel>() { DataResult = data, Message = msg }); }
/// <summary> /// 获取终端类型下拉信息数据 /// 包含ID、名称 /// </summary> public static List <TerminalTypeDDLModel> GetTerminalTypes() { string sql = "SELECT ID,TerminalName FROM dbo.TerminalTypes"; return(ConvertToList <TerminalTypeDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
public ActionResult UnusuallyDetailSummaryToExcel(EquipmentFailureSearchModel model) { //model.PagedDatas = ReportBLL.GetPagedSpeed(model, 1, Int32.MaxValue, base.CurrentUserID); DataTable dt = new DataTable(); dt.Columns.Add("StrucName", typeof(string)); dt.Columns.Add("VehicleName", typeof(string)); dt.Columns.Add("ExceptionTypeName", typeof(string)); dt.Columns.Add("GPSStartTime", typeof(string)); dt.Columns.Add("GPSEndTime", typeof(string)); dt.Columns.Add("Time", typeof(string)); DataRow dr = dt.NewRow(); dr["StrucName"] = "南京亚士德"; dr["VehicleName"] = "京TEX008"; dr["ExceptionTypeName"] = "GNSS天线未接或被剪断"; dr["GPSStartTime"] = "2016-10-10 15:04:20"; dr["GPSEndTime"] = "2016-10-10 15:05:32"; dr["Time"] = "1分12秒"; dt.Rows.Add(dr); DataRow dr1 = dt.NewRow(); dr1["StrucName"] = "南京亚士德"; dr1["VehicleName"] = "京TEX141"; dr1["ExceptionTypeName"] = "TTS模块故障"; dr1["GPSStartTime"] = "2016-10-10 17:06:52"; dr1["GPSEndTime"] = "2016-10-10 17:08:01"; dr1["Time"] = "1分09秒"; dt.Rows.Add(dr1); DataRow dr2 = dt.NewRow(); dr2["StrucName"] = "南京亚士德"; dr2["VehicleName"] = "京TEX201"; dr2["ExceptionTypeName"] = "TTS模块故障"; dr2["GPSStartTime"] = "2016-10-10 17:06:52"; dr2["GPSEndTime"] = "2016-10-10 17:08:01"; dr2["Time"] = "1分09秒"; dt.Rows.Add(dr2); DataRow dr3 = dt.NewRow(); dr3["StrucName"] = "南京亚士德"; dr3["VehicleName"] = "京TEX318"; dr3["ExceptionTypeName"] = "道路运输证IC卡模块故障"; dr3["GPSStartTime"] = "2016-10-10 15:04:20"; dr3["GPSEndTime"] = "2016-10-10 15:05:32"; dr3["Time"] = "1分12秒"; dt.Rows.Add(dr3); List <EquipmentFailureListModel> list = ConvertToList <EquipmentFailureListModel> .Convert(dt); model.PagedDatas = list.ToPagedList(1, 1, 1); ViewBag.ExportFlag = true; ViewData.Model = model; string viewHtml = RenderPartialViewToString(this, "_UnusuallyDetailSummaryGrid"); var reportName = this.GetUIText(base.GetFunctionsInfo("ReportManage", "UnusuallyDetailSummary", "SeachUnusuallyDetailSummary").FunctionName); //功能名称 return(File(System.Text.Encoding.UTF8.GetBytes(viewHtml), "application/ms-excel", string.Format("{0}.xls", reportName + "(" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ")"))); }
public static AsiatekPagedList <MGJH_ReceiveTransportPointListModel> GetPagedReceiveTransportPoints(MGJH_ReceiveTransportPointSearchModel model, int searchPage, int pageSize) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "dbo.MGJH_TransportPointSetting tp "), new SqlParameter("@joinStr", @" LEFT JOIN dbo.MGJH_TransportPointSetting tp2 ON tp2.ID=tp.SuperiorAddressID "), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "tp.CreateTime DESC"), new SqlParameter("@showColumns", @"tp.ID ,tp.SettingType ,tp.CustomerName ,tp.AddressName ,tp.AddressCode ,tp.AddressArea ,tp.SuperiorAddressID ,tp.IsUnloadPoint ,tp.EFType ,tp.EFInfo ,tp.UnloadTime/60 AS UnloadTime ,tp.UnloadTimeError/60 AS UnloadTimeError ,tp2.AddressName AS SuperiorAddressName"), }; #region 筛选条件 string conditionStr = " tp.SettingType=2"; if (!string.IsNullOrWhiteSpace(model.AddressName)) { conditionStr += " AND tp.AddressName LIKE '%" + model.AddressName + "%'"; } if (!string.IsNullOrWhiteSpace(model.AddressCode)) { conditionStr += " AND tp.AddressCode LIKE '%" + model.AddressCode + "%'"; } if (!string.IsNullOrWhiteSpace(model.CustomerName)) { conditionStr += " AND tp.CustomerName LIKE '%" + model.CustomerName + "%'"; } if (!string.IsNullOrWhiteSpace(model.AddressArea)) { conditionStr += " AND tp.AddressArea LIKE '%" + model.AddressArea + "%'"; } if (!string.IsNullOrWhiteSpace(conditionStr)) { paras.Add(new SqlParameter("@conditionStr", conditionStr)); } #endregion paras.Add(new SqlParameter() { ParameterName = "@totalItemCount", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); paras.Add(new SqlParameter() { ParameterName = "@newCurrentPage", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); var rs = MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray()); List <MGJH_ReceiveTransportPointListModel> list = ConvertToList <MGJH_ReceiveTransportPointListModel> .Convert(rs); int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value); int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value); return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount)); }
public static SelectResult <EditMaintenanceScheduleModel> GetMaintenanceScheduleByID(int id) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@ID", SqlDbType = SqlDbType.Int, }, }; paras[0].Value = id; string sql = @"SELECT ms.ID , ms.ScheduleName , ms.RulesType , ms.SettingMile , ms.PreMile , ms.SettingDay , ms.PreDay , ms.Remark ,num.BindVehicleNum FROM dbo.MaintenanceSchedule ms LEFT JOIN (SELECT COUNT(*) AS BindVehicleNum,ScheduleID FROM dbo.VehicleMaintenance GROUP BY ScheduleID) num ON num.ScheduleID=ms.ID WHERE ms.ID=@ID"; List <EditMaintenanceScheduleModel> list = ConvertToList <EditMaintenanceScheduleModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); EditMaintenanceScheduleModel data = null; string msg = string.Empty; if (list == null) { msg = PromptInformation.DBError; } else if (list.Count == 0) { msg = PromptInformation.NotExists; } else { //保养方案表有数据的情况下,查询保养内容表,填充Model.MaintenanceProjectDetails数据 List <SqlParameter> detailParas = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@ID", SqlDbType = SqlDbType.Int, }, }; detailParas[0].Value = id; string detailSql = @"SELECT ID AS ProjectID, ProjectName , ScheduleID , PartsName , PartsBrand , PartsVersion , Num , Unit , UnitPrice FROM dbo.MaintenanceProject WHERE ScheduleID=@ID"; List <MaintenanceProjectModel> detailList = ConvertToList <MaintenanceProjectModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, detailSql, detailParas.ToArray())); list[0].MaintenanceProjectDetails = detailList; data = list[0]; } return(new SelectResult <EditMaintenanceScheduleModel>() { DataResult = data, Message = msg }); }
public static List <SuperiorAddressModel> GetAddSuperiorAddress() { string sql = "SELECT ID,AddressName FROM dbo.MGJH_TransportPointSetting WHERE SettingType=2 "; return(ConvertToList <SuperiorAddressModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
public ActionResult GetAbnormalRouteInfo(AbnormalRouteSearchModel model) { DataTable dt = new DataTable(); dt.Columns.Add("StrucName", typeof(string)); dt.Columns.Add("VehicleName", typeof(string)); dt.Columns.Add("PathName", typeof(string)); dt.Columns.Add("ExceptionTypeName", typeof(string)); dt.Columns.Add("GPSStartTime", typeof(string)); dt.Columns.Add("GPSEndTime", typeof(string)); dt.Columns.Add("Time", typeof(string)); DataRow dr = dt.NewRow(); dr["StrucName"] = "南京亚士德"; dr["VehicleName"] = "京TEX008"; dr["PathName"] = "软件大道-将军山"; dr["ExceptionTypeName"] = "路段超速"; dr["GPSStartTime"] = "2016-10-10 15:04:20"; dr["GPSEndTime"] = "2016-10-10 15:05:32"; dr["Time"] = "1分12秒"; dt.Rows.Add(dr); DataRow dr1 = dt.NewRow(); dr1["StrucName"] = "南京亚士德"; dr1["VehicleName"] = "亚A00001"; dr1["PathName"] = "鸡鸣寺-中华门"; dr1["ExceptionTypeName"] = "进路线"; dr1["GPSStartTime"] = "2016-10-10 17:06:52"; dr1["GPSEndTime"] = "2016-10-10 17:08:01"; dr1["Time"] = "1分09秒"; dt.Rows.Add(dr1); DataRow dr2 = dt.NewRow(); dr2["StrucName"] = "南京亚士德"; dr2["VehicleName"] = "京TEX201"; dr2["PathName"] = "软件大道-将军山"; dr2["ExceptionTypeName"] = "出路线"; dr2["GPSStartTime"] = "2016-10-10 17:06:52"; dr2["GPSEndTime"] = "2016-10-10 17:08:01"; dr2["Time"] = "1分09秒"; dt.Rows.Add(dr2); DataRow dr3 = dt.NewRow(); dr3["StrucName"] = "南京亚士德"; dr3["VehicleName"] = "汇A00002"; dr3["PathName"] = "鸡鸣寺-中华门"; dr3["ExceptionTypeName"] = "出路线"; dr3["GPSStartTime"] = "2016-10-10 15:04:20"; dr3["GPSEndTime"] = "2016-10-10 15:05:32"; dr3["Time"] = "1分12秒"; dt.Rows.Add(dr3); List <AbnormalRouteListModel> list = ConvertToList <AbnormalRouteListModel> .Convert(dt); model.PagedDatas = list.ToPagedList(1, 1, 1); return(PartialView("_AbnormalRouteGrid", model)); }
public static AsiatekPagedList <MGJH_SynchroReceivePointListModel> GetPagedSynchroReceivePoints(MGJH_SynchroReceivePointSearchModel model, int searchPage, int pageSize) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "dbo.MGJH_SynchroReceiveAddress sr "), new SqlParameter("@joinStr", @" LEFT JOIN dbo.MGJH_TransportPointSetting tp ON tp.SourceID=sr.ID "), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "sr.ID DESC"), new SqlParameter("@showColumns", @"sr.ID ,sr.CustomerName ,sr.AddressName ,sr.AddressCode ,sr.AddressArea,tp.SourceID"), }; #region 筛选条件 string conditionStr = " 1=1 "; if (!string.IsNullOrWhiteSpace(model.AddressName)) { conditionStr += " AND sr.AddressName LIKE '%" + model.AddressName + "%'"; } if (!string.IsNullOrWhiteSpace(model.AddressCode)) { conditionStr += " AND sr.AddressCode LIKE '%" + model.AddressCode + "%'"; } if (!string.IsNullOrWhiteSpace(model.CustomerName)) { conditionStr += " AND sr.CustomerName LIKE '%" + model.CustomerName + "%'"; } if (!string.IsNullOrWhiteSpace(model.AddressArea)) { conditionStr += " AND sr.AddressArea LIKE '%" + model.AddressArea + "%'"; } if (model.IsSynchro != -1) //全部 { if (model.IsSynchro == 1) //已同步 { conditionStr += " AND tp.SourceID IS NOT NULL"; } else if (model.IsSynchro == 2) //未同步 { conditionStr += " AND tp.SourceID IS NULL"; } } if (!string.IsNullOrWhiteSpace(conditionStr)) { paras.Add(new SqlParameter("@conditionStr", conditionStr)); } #endregion paras.Add(new SqlParameter() { ParameterName = "@totalItemCount", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); paras.Add(new SqlParameter() { ParameterName = "@newCurrentPage", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); var rs = MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray()); List <MGJH_SynchroReceivePointListModel> list = ConvertToList <MGJH_SynchroReceivePointListModel> .Convert(rs); int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value); int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value); return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount)); }
public ActionResult GetRiskEarlyWarnInfo(RiskEarlyWarnSeachModel model) { DataTable dt = new DataTable(); dt.Columns.Add("StrucName", typeof(string)); dt.Columns.Add("VehicleName", typeof(string)); dt.Columns.Add("GPSStartTime", typeof(string)); dt.Columns.Add("GPSEndTime", typeof(string)); dt.Columns.Add("Time", typeof(string)); dt.Columns.Add("DealUser", typeof(string)); dt.Columns.Add("DealTime", typeof(string)); dt.Columns.Add("DealInfo", typeof(string)); DataRow dr = dt.NewRow(); dr["StrucName"] = "南京亚士德"; dr["VehicleName"] = "京TEX008"; dr["GPSStartTime"] = "2016-10-10 15:04:20"; dr["GPSEndTime"] = "2016-10-10 15:05:32"; dr["Time"] = "1分12秒"; dr["DealUser"] = "******"; dr["DealTime"] = "2016-10-12 10:15:32"; dr["DealInfo"] = "超速"; dt.Rows.Add(dr); DataRow dr1 = dt.NewRow(); dr1["StrucName"] = "南京亚士德"; dr1["VehicleName"] = "亚A00001"; dr1["GPSStartTime"] = "2016-10-10 17:06:52"; dr1["GPSEndTime"] = "2016-10-10 17:08:01"; dr1["Time"] = "1分09秒"; dr1["DealUser"] = "******"; dr1["DealTime"] = "2016-10-12 10:15:32"; dr1["DealInfo"] = "滚筒停转"; dt.Rows.Add(dr1); DataRow dr2 = dt.NewRow(); dr2["StrucName"] = "南京亚士德"; dr2["VehicleName"] = "京TEX201"; dr2["GPSStartTime"] = "2016-10-10 17:06:52"; dr2["GPSEndTime"] = "2016-10-10 17:08:01"; dr2["Time"] = "1分09秒"; dr2["DealUser"] = "******"; dr2["DealTime"] = "2016-10-12 10:15:32"; dr2["DealInfo"] = "超速"; dt.Rows.Add(dr2); DataRow dr3 = dt.NewRow(); dr3["StrucName"] = "南京亚士德"; dr3["VehicleName"] = "汇A00002"; dr3["GPSStartTime"] = "2016-10-10 15:04:20"; dr3["GPSEndTime"] = "2016-10-10 15:05:32"; dr3["Time"] = "1分12秒"; dr3["DealUser"] = "******"; dr3["DealTime"] = "2016-10-12 10:15:32"; dr3["DealInfo"] = "超速"; dt.Rows.Add(dr3); List <RiskEarlyWarnListModel> list = ConvertToList <RiskEarlyWarnListModel> .Convert(dt); model.PagedDatas = list.ToPagedList(1, 1, 1); return(PartialView("_RiskEarlyWarnGrid", model)); }
/// <summary> /// 运管所下拉列表 /// </summary> /// <returns></returns> public static List <TransportManagementDDLModel> GetTransportManagementDDL() { string sql = @"SELECT [ID],[Name] FROM [dbo].[TransportManagement] WHERE Status <> 9"; return(ConvertToList <TransportManagementDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
public static AsiatekPagedList <RepairRecordModel> GetPagedRepairRecord(RepairRecordSearchModel model, int searchPage, int pageSize, int currentStrucID) { string joinStr = string.Format(@" INNER JOIN dbo.Vehicles v ON v.ID = r.LinkedVehicleID INNER JOIN Func_GetStrucAndSubStrucByUserAffiliatedStrucID({0}) s ON v.StrucID = s.ID", currentStrucID); List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "RepairRecord r"), new SqlParameter("@joinStr", joinStr), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "r.ID"), new SqlParameter("@showColumns", @"r.ID,r.PlateNum,r.DriverName,r.DriverPhone,r.RepairStartTime,r.RepairEndTime ,r.RepairType ,r.TotalPrice,r.Status,(select NickName from Users where ID=r.OperateUserID) AS OperateUserName,(select NickName from Users where ID=r.AuditUserID) AS AuditUserName"), }; string conditionStr = " 1=1 ";//不查询删除和报废的 if (!string.IsNullOrWhiteSpace(model.PlateNum)) { conditionStr += " AND r.PlateNum LIKE '%" + model.PlateNum + "%'"; } if (!string.IsNullOrWhiteSpace(model.Status) && model.Status != "-1") { conditionStr += " AND r.Status='" + model.Status + "'"; } if (model.RepairStartTimestart != DateTime.MinValue && model.RepairStartTimeend != DateTime.MinValue && model.RepairStartTimeend >= model.RepairStartTimestart) { conditionStr += " AND r.RepairStartTime Between '" + model.RepairStartTimestart + "' and '" + model.RepairStartTimeend.AddDays(1) + "'"; } else if (model.RepairStartTimestart != DateTime.MinValue && model.RepairStartTimeend != DateTime.MinValue && model.RepairStartTimeend < model.RepairStartTimestart) { return(new AsiatekPagedList <RepairRecordModel>(new List <RepairRecordModel>(), 1, 10, 0)); } else if (model.RepairStartTimestart == DateTime.MinValue && model.RepairStartTimeend != DateTime.MinValue) { conditionStr += " AND r.RepairStartTime< '" + model.RepairStartTimeend.AddDays(1) + "'"; } else if (model.RepairStartTimestart != DateTime.MinValue && model.RepairStartTimeend == DateTime.MinValue) { conditionStr += " AND r.RepairStartTime> '" + model.RepairStartTimestart + "'"; } if (!string.IsNullOrWhiteSpace(conditionStr)) { paras.Add(new SqlParameter("@conditionStr", conditionStr)); } paras.Add(new SqlParameter() { ParameterName = "@totalItemCount", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); paras.Add(new SqlParameter() { ParameterName = "@newCurrentPage", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int }); List <RepairRecordModel> list = ConvertToList <RepairRecordModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray())); foreach (var recordmodel in list) { recordmodel.RepairStartTime = Convert.ToDateTime(recordmodel.RepairStartTime).ToString("yyyy-MM-dd"); recordmodel.RepairEndTime = Convert.ToDateTime(recordmodel.RepairEndTime).ToString("yyyy-MM-dd"); } int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value); int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value); return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount)); }
/// <summary> /// 获得除后台功能外的功能树节点信息 /// </summary> /// <returns></returns> public static List <FunctionTreeNodeModel> GetNormalFunctionsForTree() { string sql = @"SELECT ID,FunctionName,ParentID FROM dbo.Functions WHERE IsBackground=0 "; return(ConvertToList <FunctionTreeNodeModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }