public ActionResult AccumulativeDrivieTimeoutToExcel(ReportSearchModels 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)); 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"] = "汇A00002"; 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<ReportListModel> list = ConvertToList<ReportListModel>.Convert(dt); model.PagedDatas = list.ToPagedList(1, 1, 1); ViewBag.ExportFlag = true; ViewData.Model = model; string viewHtml = RenderPartialViewToString(this, "_AccumulativeDrivieTimeoutGrid"); var reportName = this.GetUIText(base.GetFunctionsInfo("ReportManage", "AccumulativeDrivieTimeout", "SeachAccumulativeDrivieTimeout").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> /// <param name="pageSize">页大小</param> /// <param name="currentPage">当前页</param> /// <param name="actionName">动作名称</param> /// <param name="controllerID">动作所属控制器ID</param> /// <param name="areaID">动作所属控制器所属区域ID</param> /// <returns></returns> public static AsiatekPagedList <ActionListModel> GetPagedActions(int pageSize, int currentPage = 1, string actionName = "", int controllerID = -1, int areaID = -1) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "Actions act"), new SqlParameter("@joinStr", "INNER JOIN Controllers c ON act.ControllerID=c.ID INNER JOIN Areas a ON c.AreaID=a.ID"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", currentPage), new SqlParameter("@orderBy", "act.ID"), new SqlParameter("@showColumns", "act.ID,act.ActionName,act.Description,c.ControllerName,a.AreaName"), }; //如果选择了区域,但是控制器选择了全部 if (areaID != -1 && controllerID == -1) { paras.Add(new SqlParameter("@conditionStr", "act.ActionName LIKE '%" + actionName + "%' AND c.AreaID='" + areaID + "'")); } else if (controllerID != -1)//选择了具体的控制器 { paras.Add(new SqlParameter("@conditionStr", "act.ActionName LIKE '%" + actionName + "%' AND act.ControllerID='" + controllerID + "'")); } else { paras.Add(new SqlParameter("@conditionStr", "act.ActionName LIKE '%" + actionName + "%'")); } 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 <ActionListModel> list = ConvertToList <ActionListModel> .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> /// 根据区域编号获取全部功能下拉列表数据(用于级联) /// <para>包括功能编号、功能名称</para> /// </summary> /// <param name="areaID">功能对应的动作所属控制器所属区域编号</param> /// <returns></returns> public static List <FunctionDDLModel> GetFunctionsByAreaID(int areaID) { string sql = @"SELECT f.ID,f.FunctionName FROM dbo.Functions f LEFT JOIN dbo.Actions act ON f.ActionID=act.ID LEFT JOIN dbo.Controllers c ON act.ControllerID=c.ID LEFT JOIN dbo.Areas a ON c.AreaID=a.ID WHERE a.ID=@areaID"; List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@areaID", SqlDbType.Int), }; paras[0].Value = areaID; return(ConvertToList <FunctionDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()))); }
/// <summary> /// 查询运管所分页信息 /// </summary> /// <param name="model"></param> /// <param name="searchPage"></param> /// <param name="pageSize"></param> /// <returns></returns> public static AsiatekPagedList <TransportManagementListModel> GetPagedTransportManagement (TransportManagementSearchModel model, int searchPage, int pageSize) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "[dbo].[TransportManagement]"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "ID DESC"), new SqlParameter("@showColumns", @"[ID],[Code],[Name]") }; string conditionStr = " Status = 0 "; if (!string.IsNullOrWhiteSpace(model.Code)) { conditionStr += " AND Code LIKE '%" + model.Code + "%'"; } if (!string.IsNullOrWhiteSpace(model.Name)) { conditionStr += " AND Name LIKE '%" + model.Name + "%'"; } 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 <TransportManagementListModel> list = ConvertToList <TransportManagementListModel> . 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 AsiatekPagedList <NeedDealExceptionListModel> GetNeedDealExceptionList(NeedDealExceptionSearchModel model, int searchPage, int pageSize, int userID) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "VW_SZYDExceptions e"), new SqlParameter("@joinStr", @" INNER JOIN Func_GetVehiclesListByUserID_New(" + userID + @") v ON e.VIN=v.VIN INNER JOIN dbo.ExceptionType et ON et.ID=e.ExceptionTypeID INNER JOIN dbo.Terminals t ON t.LinkedVehicleID=v.VID INNER JOIN dbo.ServerInfo si ON t.ServerInfoID=si.ID AND si.ServerCode=e.ServerCode"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "e.SignalStartTime DESC"), new SqlParameter("@showColumns", @"e.ID AS ExID,et.ExName,v.VehicleName,e.SignalStartTime,e.StartAddress,e.SignalEndTime,e.EndAddress,si.LinkedServerName,e.TerminalCode"), }; #region 筛选条件 string conditionStr = "e.ExceptionTypeID IN(2,5,103) AND e.Status<>1 AND e.SignalStartTime BETWEEN '" + model.BeginDateTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + model.EndDateTime.ToString("yyyy-MM-dd HH:mm:ss") + "'"; if (model.EndState == 1)//结束 { conditionStr += " AND e.SignalEndTime IS NOT NULL"; } else if (model.EndState == 0)//未结束 { conditionStr += " AND e.SignalEndTime IS NULL"; } 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 list = ConvertToList <NeedDealExceptionListModel> .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 AsiatekPagedList <ReceiverMailInfoListModel> GetPagedReceiverMailInfo(ReceiverMailInfoSearchModel model, int searchPage, int pageSize) { string joinStr = string.Empty; List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "ReceiverMailInfo rmi"), new SqlParameter("@joinStr", joinStr), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "rmi.CreateTime desc"), new SqlParameter("@showColumns", @"rmi.ID ,rmi.Email,rmi.Name,rmi.Remark,rmi.Status"), }; string conditionStr = " 1=1 ";//不查询删除 if (!string.IsNullOrWhiteSpace(model.Email)) { conditionStr += (string.Format(" and rmi.Email like '%{0}%'", model.Email.Trim())); } if (!string.IsNullOrWhiteSpace(model.Name)) { conditionStr += (string.Format(" and rmi.Name like '%{0}%'", model.Name.Trim())); } 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 <ReceiverMailInfoListModel> list = ConvertToList <ReceiverMailInfoListModel> .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 GetLostTerminalInfo(LostReportModels model) { //model.GPSStartTime = "2016-10-10 00:00:00"; //model.GPSEndTime = "2016-10-10 23:59:59"; //model.PagedDatas = ReportBLL.GetLostPagedSpeed(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); List <LostReportListModel> list = ConvertToList <LostReportListModel> .Convert(dt); model.PagedDatas = list.ToPagedList(1, 1, 1); return(PartialView("_LostConnectionReportDetail", model)); }
public static AsiatekPagedList <SensorListModels> GetPagedSensor(SensorSearchModel model, int searchPage, int pageSize) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "SensorList s"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "s.TypeID"), new SqlParameter("@showColumns", @"s.TypeID ,s.SensorCode, s.SensorName, s.Value1, s.Value2, s.Remark, s.Status,s.TypeCode"), }; string conditionStr = " Status<>9 ";//不查询删除和报废的 if (!string.IsNullOrWhiteSpace(model.SensorName)) { conditionStr += " AND s.SensorName LIKE '%" + model.SensorName + "%'"; } 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 <SensorListModels> list = ConvertToList <SensorListModels> .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 <InspectionModel> GetInspectionInfoByID(long ID) { string sql = @"SELECT OBJECT_TYPE AS ObjType , OBJECT_ID AS ObjID , AccessCode , Version , CAST(M1 AS INT) M1 , CAST(IA1 AS INT) IA1 , CAST(IC1 AS INT) IC1 , FLAG, PLATNAMR AS PlatformName FROM InspectionServer.TMS.dbo.TB_PLATFORM_CHECKMSG WHERE MSG_ID = @ID"; List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@ID", Value = ID.ToString(), }, }; List <InspectionModel> list = ConvertToList <InspectionModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); InspectionModel data = null; string msg = string.Empty; if (list == null) { msg = PromptInformation.DBError; } else if (list.Count == 0) { msg = PromptInformation.NotExists; } else { data = list[0]; } return(new SelectResult <InspectionModel>() { DataResult = data, Message = msg }); }
public ActionResult SeachAccumulativeDrivieTimeout() { var model = new ReportSearchModels(); model.SearchPage = 1; model.GPSStartTime = DateTime.Now.AddDays(-1).ToString("yyyy-M-dd 00:00:00"); model.GPSEndTime = DateTime.Now.AddDays(-1).ToString("yyyy-M-dd 23:59:59"); 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); //model.PagedDatas = ReportBLL.GetLostPagedSpeed(model, 1, this.PageSize, base.CurrentUserID); List<ReportListModel> list = ConvertToList<ReportListModel>.Convert(dt); model.PagedDatas = list.ToPagedList(1, 1, 1); return PartialView("_SeachAccumulativeDrivieTimeout", model); }
public static AsiatekPagedList <MapLinesListModel> GetPagedMapLines(MapLinesSearchModel model, int searchPage, int pageSize) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "MapLinesList"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "ID DESC"), new SqlParameter("@showColumns", @"ID, LinesType, LinesName, StartTime, EndTime "), }; #region 筛选条件 string conditionStr = "Status=0"; if (!string.IsNullOrWhiteSpace(model.LinesName)) { conditionStr += " AND LinesName LIKE '%" + model.LinesName + "%'"; } 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 <MapLinesListModel> list = ConvertToList <MapLinesListModel> .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="functionID"></param> /// <returns></returns> public static SelectResult <FunctionEditModel> GetFunctionByID(int functionID) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@ID", SqlDbType.Int), }; paras[0].Value = functionID; string sql = @"SELECT fun.ID , fun.FunctionName , fun.ActionID , fun.ParentID , fun.Description, fun.IsBackground, fun.OrderIndex, act.ControllerID , c.AreaID, fun.IsAppFeatures, fun.FeaturesCode,fun.IsAppHomeModule,fun.IsAppShortcutMenu FROM dbo.Functions fun LEFT JOIN dbo.Actions act ON fun.ActionID = act.ID LEFT JOIN dbo.Controllers c ON act.ControllerID = c.ID WHERE fun.ID = @ID"; List <FunctionEditModel> list = ConvertToList <FunctionEditModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); FunctionEditModel data = null; string msg = string.Empty; if (list == null) { msg = PromptInformation.DBError; } else if (list.Count == 0) { msg = PromptInformation.NotExists; } else { data = list[0]; } return(new SelectResult <FunctionEditModel>() { DataResult = data, Message = msg }); }
/// <summary> /// 获取全部功能信息 /// </summary> /// <returns></returns> public static List <FunctionsInfoModel> GetAllFunctions() { string sql = @"SELECT f.ID, FunctionName , AreaName , ControllerName , ActionName, f.ParentID, f.OrderIndex FROM dbo.Functions f LEFT JOIN dbo.Actions act ON f.ActionID=act.ID LEFT JOIN dbo.Controllers c ON act.ControllerID=c.ID LEFT JOIN dbo.Areas a ON c.AreaID=a.ID"; return(ConvertToList <FunctionsInfoModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
/// <summary> /// 获取24小时内未回复的针对平台的查岗信息 /// </summary> /// <returns></returns> public static List <InspectionListModel> GetPlatformInspectionInfos() { string sql = @"SELECT CAST(MSG_ID AS BIGINT) AS ID , MSG_CONTENT AS Content , CONVERT(VARCHAR,CREATE_TIME,121) AS CheckDateTime, PLATNAMR AS PlatformName FROM InspectionServer.TMS.dbo.TB_PLATFORM_CHECKMSG tpc WHERE FLAG = 0 AND OBJECT_TYPE=1 AND DATEDIFF(hour, CREATE_TIME, GETDATE()) <= 24"; // string sql = @"SELECT CAST(MSG_ID AS BIGINT) AS ID , // MSG_CONTENT AS Content , // CONVERT(VARCHAR,CREATE_TIME,121) AS CheckDateTime, // PLATNAMR AS PlatformName //FROM InspectionServer.TMS.dbo.TB_PLATFORM_CHECKMSG tpc //WHERE OBJECT_TYPE=1"; return(ConvertToList <InspectionListModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
public ActionResult SeachFatigueDrive() { var model = new FatigueDriveSeachModel(); model.SearchPage = 1; model.GPSStartTime = DateTime.Now.AddDays(-1).ToString("yyyy-M-dd 00:00:00"); model.GPSEndTime = DateTime.Now.AddDays(-1).ToString("yyyy-M-dd 23:59:59"); 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); List <FatigueDriveListModel> list = ConvertToList <FatigueDriveListModel> .Convert(dt); model.PagedDatas = list.ToPagedList(1, 1, 1); return(PartialView("_SeachFatigueDrive", model)); }
public static List <ServerInfoModel> GetServerInfoByPlateNum(string PlateNum) { string sql = @" SELECT v.PlateNum,s.LinkedServerName FROM dbo.Vehicles v INNER JOIN dbo.Terminals t ON v.ID=t.LinkedVehicleID INNER JOIN dbo.ServerInfo s ON s.ID = t.ServerInfoID WHERE v.PlateNum=@PlateNum "; List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@PlateNum", SqlDbType.NVarChar) }; paras[0].Value = PlateNum; var list = ConvertToList <ServerInfoModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); return(list); }
/// <summary> /// 获取单位下拉列表信息 /// 包含单位ID、单位名称 /// </summary> public static List <SimCodeDDLModel> GetNotUserdSimCodeList(string simCode, int?simCodeId) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@SimCode", SqlDbType.VarChar) }; paras[0].Value = "%" + simCode + "%"; string sql = "SELECT [ID],[SimCode] FROM [dbo].[SimCodeList] WHERE [Status]<> 9 AND [Status] <> 1 AND SimCode LIKE @SimCode "; if (simCodeId.HasValue && simCodeId.Value > 0) { sql += " UNION SELECT [ID],[SimCode] FROM [dbo].[SimCodeList] WHERE ID = " + simCodeId; } return(ConvertToList <SimCodeDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()))); }
public ActionResult GetAccumulativeDrivieTimeoutInfo(ReportSearchModels 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)); 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"] = "汇A00002"; 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<ReportListModel> list = ConvertToList<ReportListModel>.Convert(dt); model.PagedDatas = list.ToPagedList(1, 1, 1); return PartialView("_AccumulativeDrivieTimeoutGrid", model); }
public static SelectResult <EditSimCodeModels> GetSimCodeID(int id) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@id", SqlDbType.Int), }; paras[0].Value = id; string sql = @" SELECT a.[ID] ,a.[SimCode] ,a.[CommMode] ,OStr.StrucName AS OwnerStrucName ,a.[OwnerStrucID] ,UStr.StrucName AS UseStrucName ,a.[UseStrucID] ,a.[ServiceProviderID] ,a.[PurchaseDate] ,a.[OpeningDate] ,a.[ExpiryDate] ,a.[Remark] ,a.[Status] FROM dbo.SimCodeList AS a LEFT JOIN Structures AS OStr ON a.OwnerStrucID = OStr.ID LEFT JOIN Structures AS UStr ON a.UseStrucID = UStr.ID WHERE a.ID=@id"; List <EditSimCodeModels> list = ConvertToList <EditSimCodeModels> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); EditSimCodeModels data = null; string msg = string.Empty; if (list == null) { msg = PromptInformation.DBError; } else if (list.Count == 0) { msg = PromptInformation.NotExists; } else { data = list[0]; } return(new SelectResult <EditSimCodeModels>() { DataResult = data, Message = msg }); }
public static SelectResult <UserEditModel> GetUserByID(int id) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@ID", SqlDbType.Int), }; paras[0].Value = id; string sql = @"SELECT a.ID , UserName , RoleID , NickName , StrucID , ContactNumber1 , ContactNumber2 , ContactAddress , a.Remark, b.StrucName, a.VehicleViewMode FROM dbo.Users AS a LEFT JOIN Structures AS b ON a.StrucID = b.ID WHERE a.ID = @ID AND a.[Status] <> 9"; List <UserEditModel> list = ConvertToList <UserEditModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); UserEditModel data = null; string msg = string.Empty; if (list == null) { msg = PromptInformation.DBError; } else if (list.Count == 0) { msg = PromptInformation.NotExists; } else { data = list[0]; } return(new SelectResult <UserEditModel>() { DataResult = data, Message = msg }); }
public static AsiatekPagedList <VehicleMaintainListModels> GetPagedPlateColors(VehicleMaintainSearchModels model, int searchPage, int pageSize) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "PlateColors p"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "p.Code"), new SqlParameter("@showColumns", @"p.Code ,p.Name"), }; string conditionStr = " 1=1 "; if (!string.IsNullOrWhiteSpace(model.PlateCode)) { conditionStr += " AND p.Code LIKE '%" + model.PlateCode + "%'"; } if (!string.IsNullOrWhiteSpace(model.PlateName)) { conditionStr += " AND p.Name LIKE '%" + model.PlateName + "%'"; } 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 <VehicleMaintainListModels> list = ConvertToList <VehicleMaintainListModels> .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 <EditPickUpTransportPointModel> GetPickUpTransportPointByID(int id) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@ID", SqlDbType = SqlDbType.Int, }, }; paras[0].Value = id; string sql = @"SELECT ID ,SettingType ,AddressName ,AddressCode ,EFType ,EFInfo FROM dbo.MGJH_TransportPointSetting WHERE ID=@ID"; List <EditPickUpTransportPointModel> list = ConvertToList <EditPickUpTransportPointModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); EditPickUpTransportPointModel data = null; string msg = string.Empty; if (list == null) { msg = PromptInformation.DBError; } else if (list.Count == 0) { msg = PromptInformation.NotExists; } else { data = list[0]; data.EFInfo = ChangeCoordinateSystem(data.EFType, data.EFInfo, 2); //将取出的车机坐标转成地图坐标,显示 } return(new SelectResult <EditPickUpTransportPointModel>() { DataResult = data, Message = msg }); }
/// <summary> /// 根据单位ID获取单位及子单位的路线信息 /// </summary> public static List <StrucMapLinesModel> GetStrucMapLines(int strucID, string lineName) { string sql = "SELECT * FROM dbo.Func_GetLineInfoByStrucID(@strucID) WHERE LinesName LIKE @lineName"; SqlParameter[] paras = new SqlParameter[2]; paras[0] = new SqlParameter() { ParameterName = "@strucID", Value = strucID, SqlDbType = SqlDbType.Int }; paras[1] = new SqlParameter() { ParameterName = "@lineName", Value = "%" + lineName + "%", }; return(ConvertToList <StrucMapLinesModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras))); }
/// <summary> /// 获取用户分配的车辆信息 获取其自己和自己子单位的所有车辆 /// </summary> public static AsiatekPagedList <MSBindVehicleListModel> GetPagedCustomerVehicle(int id, MSBindVehicleSearchModel model, int searchPage, int pageSize, int strucID) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "dbo.Vehicles ve "), new SqlParameter("@joinStr", @"INNER JOIN dbo.Structures st ON ve.StrucID = st.ID INNER JOIN Func_GetAllTheSubsetOfVehiclesByStrucID(" + strucID + ") AS vt ON ve.ID=vt.VID "), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "ve.ID DESC"), new SqlParameter("@showColumns", @"ve.ID AS VehicleID,ve.PlateNum,ve.VIN,ve.VehicleName,ve.StrucID,st.StrucName "), }; StringBuilder sbWhere = new StringBuilder("ve.Status<>9 AND st.Status<>9 AND ve.ID NOT IN (SELECT VehicleID FROM dbo.VehicleMaintenance WHERE ScheduleID=" + id + ")"); if (!string.IsNullOrWhiteSpace(model.VehicleName)) { sbWhere.Append(" AND ve.VehicleName LIKE '%" + model.VehicleName + "%'"); } if (!string.IsNullOrEmpty(sbWhere.ToString())) { paras.Add(new SqlParameter("@conditionStr", sbWhere.ToString())); } 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 <MSBindVehicleListModel> list = ConvertToList <MSBindVehicleListModel> .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 <RepairRecordModel> GetRecordByID(int id) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@id", SqlDbType.Int), }; paras[0].Value = id; string sql = @"SELECT * FROM dbo.RepairRecord WHERE ID=@id"; List <RepairRecordModel> list = ConvertToList <RepairRecordModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); RepairRecordModel data = null; string msg = string.Empty; if (list == null) { msg = PromptInformation.DBError; } else if (list.Count == 0) { msg = PromptInformation.NotExists; } else { data = list[0]; } string recordsql = @"select * from dbo.RepairRecordDetail where LinkedRecordID=@id"; List <SqlParameter> paras1 = new List <SqlParameter>() { new SqlParameter("@id", SqlDbType.Int), }; paras1[0].Value = id; List <RepairRecordDetail> detaillist = ConvertToList <RepairRecordDetail> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, recordsql, paras1.ToArray())); data.RepairRecordDetail = detaillist; data.RepairStartTime = Convert.ToDateTime(data.RepairStartTime).ToString("yyyy-MM-dd"); data.RepairEndTime = Convert.ToDateTime(data.RepairEndTime).ToString("yyyy-MM-dd"); return(new SelectResult <RepairRecordModel>() { DataResult = data, Message = msg }); }
/// <summary> /// 获取用户分配的车辆信息(车代号、车辆使用单位名) 默认模式 /// </summary> /// <param name="strucID"></param> /// <param name="vehicleName"></param> /// <returns></returns> public static List <UserVehicles> GetDefaultVehiclesAndStrucName(int strucID, string vehicleName) { string sql = @"SELECT vt.VID,vt.VehicleName,vt.VIN,s.StrucName FROM dbo.Structures s INNER JOIN Func_GetAllTheSubsetOfVehiclesByStrucID(@StrucID) AS vt ON s.ID=vt.StrucID WHERE vt.VehicleName LIKE @vehicleName"; SqlParameter[] paras = new SqlParameter[2]; paras[0] = new SqlParameter() { ParameterName = "@StrucID", Value = strucID, SqlDbType = SqlDbType.Int }; paras[1] = new SqlParameter() { ParameterName = "@vehicleName", Value = "%" + vehicleName + "%", }; return(ConvertToList <UserVehicles> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras))); }
// /// <summary> // /// 根据用户ID获取用户分配的有效车辆尚未结束的紧急报警异常 // /// </summary> // /// <param name="userID"></param> // /// <returns></returns> // public static List<EmergencyAlarmInfoModel> GetEmergencyAlarms(int userID) // { // //考虑到跨月情况,上月紧急报警还是需要查询出来, // //那么规定如果当前是这个月的前三天,需要同时查询上个月的异常表 // //之所以是3天,是因为考虑周末2天没人看,所以多留一天,否则第三天不刷出来,就没人知道了 // //天数多也没意义 // //还有关于上个月异常表可能不存在的情况,这个只针对刚使用的时候 // //那个时候没有上个月异常表 // //本质上这里需要判断上个月异常表是否存在,存在才去查询 // //但是我们这里采用人工添加上个月月表的方式避免这个问题。 // var now = DateTime.Now; // string currentMonth = now.ToString("yyyyMM"); // string prevMonth = now.AddMonths(-1).ToString("yyyyMM"); // string sql = string.Empty; // if (now.Day <= 3)//前三天,需要查询上月异常表 // { // #region 语句 // sql = @" // DECLARE @tempTable TABLE(VehicleName NVARCHAR(20),StrucName NVARCHAR(200),TerminalCode NVARCHAR(30)) // INSERT INTO @tempTable // SELECT userVehicles.VehicleName,s.StrucName,t.TerminalCode FROM dbo.Structures s // INNER JOIN // ( // SELECT v.ID AS VID,v.StrucID,v.VehicleName FROM dbo.Vehicles v // INNER JOIN // ( // SELECT StrucID FROM dbo.StructureDistributionInfo // WHERE UserID=@userID // ) AS temp1 // ON v.StrucID=temp1.StrucID // WHERE v.IsReceived=1 AND v.Status=0 // UNION // SELECT v.ID,v.StrucID,v.VehicleName FROM dbo.VehicleDistributionInfo vdi // INNER JOIN dbo.Vehicles v ON vdi.VehicleID=v.ID // WHERE UserID=@userID AND v.IsReceived=1 AND v.Status=0 // ) AS userVehicles ON userVehicles.StrucID=s.ID // INNER JOIN dbo.VehiclesTerminals vt ON userVehicles.VID=vt.VehicleID // INNER JOIN dbo.Terminals t ON vt.TerminalID=t.ID // WHERE vt.IsPrimary=1 // SELECT e.ID,e.GPSStartTime AS StartDateTime,e.Status,e.Address,[@tempTable].* FROM dbo.Exception_" + currentMonth + @" e // INNER JOIN @tempTable ON [@tempTable].TerminalCode=e.TerminalCode // WHERE ExceptionTypeID=1 AND GPSEndTime IS NULL // UNION ALL // SELECT e.ID,e.GPSStartTime AS StartDateTime,e.Status,e.Address,[@tempTable].* FROM dbo.Exception_" + prevMonth + @" e // INNER JOIN @tempTable ON [@tempTable].TerminalCode=e.TerminalCode // WHERE ExceptionTypeID=1 AND GPSEndTime IS NULL"; // #endregion // } // else//只查询当月的 // { // #region 语句 // sql = @" //SELECT e.ID,e.GPSStartTime AS StartDateTime,e.Status,e.Address,tempTable.* FROM dbo.Exception_" + currentMonth + @" e //INNER JOIN //( //SELECT userVehicles.VehicleName,s.StrucName,t.TerminalCode FROM dbo.Structures s //INNER JOIN //( // SELECT v.ID AS VID,v.StrucID,v.VehicleName FROM dbo.Vehicles v // INNER JOIN // ( // SELECT StrucID FROM dbo.StructureDistributionInfo // WHERE UserID=@userID // ) AS temp1 // ON v.StrucID=temp1.StrucID // WHERE v.IsReceived=1 AND v.Status=0 // UNION // SELECT v.ID,v.StrucID,v.VehicleName FROM dbo.VehicleDistributionInfo vdi // INNER JOIN dbo.Vehicles v ON vdi.VehicleID=v.ID // WHERE UserID=@userID AND v.IsReceived=1 AND v.Status=0 //) AS userVehicles ON userVehicles.StrucID=s.ID //INNER JOIN dbo.VehiclesTerminals vt ON userVehicles.VID=vt.VehicleID //INNER JOIN dbo.Terminals t ON vt.TerminalID=t.ID //WHERE vt.IsPrimary=1 //) AS tempTable // ON tempTable.TerminalCode=e.TerminalCode //WHERE ExceptionTypeID=1 AND GPSEndTime IS NULL"; // #endregion // } // SqlParameter[] paras = new SqlParameter[1]; // paras[0] = new SqlParameter() // { // ParameterName = "@userID", // Value = userID, // SqlDbType = SqlDbType.Int // }; // return ConvertToList<EmergencyAlarmInfoModel>.Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras)); // } // /// <summary> // /// 根据用户ID获取用户分配的有效车辆尚未结束的紧急报警异常 // /// </summary> // /// <param name="userID"></param> // /// <returns></returns> // public static List<EmergencyAlarmInfoModel> GetEmergencyAlarms(int userID) // { // string sql = @"SELECT e.ID,e.GPSStartTime AS StartDateTime,e.Status,e.Address,tempTable.* FROM dbo.Exception e //INNER JOIN //( //SELECT userVehicles.VehicleName,s.StrucName,t.TerminalCode FROM dbo.Structures s //INNER JOIN //( // SELECT v.ID AS VID,v.StrucID,v.VehicleName FROM dbo.Vehicles v // INNER JOIN // ( // SELECT StrucID FROM dbo.StructureDistributionInfo // WHERE UserID=@userID // ) AS temp1 // ON v.StrucID=temp1.StrucID // WHERE v.IsReceived=1 AND v.Status=0 // UNION // SELECT v.ID,v.StrucID,v.VehicleName FROM dbo.VehicleDistributionInfo vdi // INNER JOIN dbo.Vehicles v ON vdi.VehicleID=v.ID // WHERE UserID=@userID AND v.IsReceived=1 AND v.Status=0 //) AS userVehicles ON userVehicles.StrucID=s.ID //INNER JOIN dbo.VehiclesTerminals vt ON userVehicles.VID=vt.VehicleID //INNER JOIN dbo.Terminals t ON vt.TerminalID=t.ID //WHERE vt.IsPrimary=1 //) AS tempTable // ON tempTable.TerminalCode=e.TerminalCode //WHERE ExceptionTypeID=1 AND GPSEndTime IS NULL"; // SqlParameter[] paras = new SqlParameter[1]; // paras[0] = new SqlParameter() // { // ParameterName = "@userID", // Value = userID, // SqlDbType = SqlDbType.Int // }; // return ConvertToList<EmergencyAlarmInfoModel>.Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras)); // } #region 紧急告警 #region 自由模式 /// <summary> /// 根据用户ID获取用户分配的有效车辆尚未结束的紧急报警异常 自由模式 /// </summary> /// <param name="userID"></param> /// <returns></returns> public static List <EmergencyAlarmInfoModel> GetEmergencyAlarms(int userID) { // string sql = @"SELECT e.ID,e.TerminalCode,tempTable.StrucName,tempTable.VehicleName,e.SignalStartTime AS StartDateTime,e.StartAddress AS Address,e.Status FROM //dbo.VW_Exceptions e //INNER JOIN //( //SELECT userVehicles.VehicleName,userVehicles.VIN,s.StrucName FROM dbo.Structures s //INNER JOIN //( // SELECT v.ID AS VID,v.StrucID,v.VehicleName,v.VIN FROM dbo.Vehicles v // INNER JOIN // ( // SELECT StrucID FROM dbo.StructureDistributionInfo // WHERE UserID=@userID // ) AS temp1 // ON v.StrucID=temp1.StrucID // WHERE v.IsReceived=1 AND v.Status=0 // UNION // SELECT v.ID,v.StrucID,v.VehicleName,v.VIN FROM dbo.VehicleDistributionInfo vdi // INNER JOIN dbo.Vehicles v ON vdi.VehicleID=v.ID // WHERE UserID=@userID AND v.IsReceived=1 AND v.Status=0 //) AS userVehicles ON userVehicles.StrucID=s.ID //) AS tempTable ON e.VIN=tempTable.VIN //WHERE e.ExceptionTypeID=1 AND e.SignalEndTime IS NULL"; string sql = @"SELECT e.ID,e.TerminalCode,tempTable.StrucName,tempTable.VehicleName, e.SignalStartTime AS StartDateTime,e.StartAddress AS Address,e.Status FROM dbo.VW_Exceptions e INNER JOIN ( SELECT userVehicles.VehicleName,userVehicles.VIN,s.StrucName FROM dbo.Structures s INNER JOIN Func_GetVehiclesListByUserID_New(@userID) AS userVehicles ON userVehicles.StrucID=s.ID ) AS tempTable ON e.VIN=tempTable.VIN WHERE e.ExceptionTypeID=1 AND e.SignalEndTime IS NULL"; SqlParameter[] paras = new SqlParameter[1]; paras[0] = new SqlParameter() { ParameterName = "@userID", Value = userID, SqlDbType = SqlDbType.Int }; return(ConvertToList <EmergencyAlarmInfoModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras))); }
/// <summary> /// 获取角色分页数据 /// </summary> /// <param name="currentPage">当前页</param> /// <param name="pageSize">页大小</param> /// <param name="roleName">角色编号</param> /// <returns></returns> public static AsiatekPagedList <RoleListModel> GetPagedRoleInfo(int currentPage, int pageSize, bool isSuperAdmin, string roleName = "") { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "Roles"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", currentPage), new SqlParameter("@orderBy", "ID"), }; if (isSuperAdmin)//超级管理员查询全部角色信息 { paras.Add(new SqlParameter("@conditionStr", "RoleName LIKE '%" + roleName + "%'")); } else//其他则只查询普通用户 { paras.Add(new SqlParameter("@conditionStr", "RoleName LIKE '%" + roleName + "%' AND RoleLevel='2'")); } 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 <RoleListModel> list = ConvertToList <RoleListModel> .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 <EditSynchroReceivePointModel> GetSynchroReceivePointByID(int id) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@ID", SqlDbType = SqlDbType.Int, }, }; paras[0].Value = id; string sql = @"SELECT sr.ID ,sr.CustomerName ,sr.AddressName ,sr.AddressCode ,sr.AddressArea FROM dbo.MGJH_SynchroReceiveAddress sr WHERE sr.ID=@ID"; List <EditSynchroReceivePointModel> list = ConvertToList <EditSynchroReceivePointModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); EditSynchroReceivePointModel data = null; string msg = string.Empty; if (list == null) { msg = PromptInformation.DBError; } else if (list.Count == 0) { msg = PromptInformation.NotExists; } else { data = list[0]; } return(new SelectResult <EditSynchroReceivePointModel>() { DataResult = data, Message = msg }); }
public static List <EFPropertyPeriodModel> GetEFPropertyPeriodByID(int id) { List <EFPropertyPeriodModel> list = new List <EFPropertyPeriodModel>(); string sql = @" SELECT COUNT(0) FROM dbo.ElectricFencePropertyPeriod WHERE EFPropertyID=@EFPropertyID "; List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@EFPropertyID", SqlDbType.Int), }; paras[0].Value = id; var count = MSSQLHelper.ExecuteScalar(CommandType.Text, sql, paras.ToArray()); if (count != null) { string sqlTemp = string.Format(@" SELECT Week,CONVERT(VARCHAR(12), StartTime, 114) AS StartTime,CONVERT(VARCHAR(12), EndTime, 114) AS EndTime FROM dbo.ElectricFencePropertyPeriod WHERE EFPropertyID={0} ORDER BY Week ASC", id); list = ConvertToList <EFPropertyPeriodModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sqlTemp)); } return(list); }