public static OperationResult EditUser(UserEditModel model, int EditUserID) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@ID", SqlDbType.Int), new SqlParameter("@RoleID", SqlDbType.Int), new SqlParameter("@NickName", SqlDbType.NVarChar, 20), new SqlParameter("@StrucID", SqlDbType.Int), new SqlParameter("@ContactNumber1", SqlDbType.NVarChar, 50), new SqlParameter("@ContactNumber2", SqlDbType.NVarChar, 50), new SqlParameter("@ContactAddress", SqlDbType.NVarChar, 50), new SqlParameter("@Remark", SqlDbType.NVarChar, 50), new SqlParameter("@EditUserID", SqlDbType.Int), new SqlParameter("@VehicleViewMode", SqlDbType.Bit), }; paras[0].Value = model.ID; paras[1].Value = model.RoleID; paras[2].Value = model.NickName.Trim(); paras[3].Value = model.StrucID; #region 可NULL if (string.IsNullOrWhiteSpace(model.ContactNumber1)) { paras[4].Value = DBNull.Value; } else { paras[4].Value = model.ContactNumber1.Trim(); } if (string.IsNullOrWhiteSpace(model.ContactNumber2)) { paras[5].Value = DBNull.Value; } else { paras[5].Value = model.ContactNumber2.Trim(); } if (string.IsNullOrWhiteSpace(model.ContactAddress)) { paras[6].Value = DBNull.Value; } else { paras[6].Value = model.ContactAddress.Trim(); } if (string.IsNullOrWhiteSpace(model.Remark)) { paras[7].Value = DBNull.Value; } else { paras[7].Value = model.Remark; } paras[8].Value = EditUserID; paras[9].Value = model.VehicleViewMode; #endregion #region SQL string sql = @"UPDATE dbo.Users SET NickName = @NickName , RoleID = @RoleID , StrucID = @StrucID , Remark = @Remark, ContactAddress = @ContactAddress , ContactNumber1 = @ContactNumber1 , ContactNumber2 = @ContactNumber2 , EditDateTime = GETDATE(), EditUserID=@EditUserID, VehicleViewMode =@VehicleViewMode WHERE ID = @ID"; #endregion int result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()); string msg = string.Empty; switch (result) { case 1: msg = PromptInformation.OperationSuccess; break; case 0: msg = PromptInformation.NotExists; break; case -1: msg = PromptInformation.DBError; break; } return(new OperationResult() { Success = result > 0, Message = msg }); }
public static AsiatekPagedList <ControllerListModel> GetPagedControllerInfo(int pageSize, int currentPage = 1, int areaID = -1, string controllerName = "") { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "Controllers c"), new SqlParameter("@joinStr", "INNER JOIN Areas a ON c.AreaID=a.ID"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", currentPage), new SqlParameter("@orderBy", "c.ID"), new SqlParameter("@showColumns", "c.ID,c.ControllerName,c.Description,a.AreaName"), }; if (areaID != -1) { paras.Add(new SqlParameter("@conditionStr", "c.ControllerName LIKE '%" + controllerName + "%' AND AreaID='" + areaID + "'")); } else { paras.Add(new SqlParameter("@conditionStr", "c.ControllerName LIKE '%" + controllerName + "%'")); } 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 <ControllerListModel> list = ConvertToList <ControllerListModel> .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> public static string GetPreviewMenuMsgHtml(UserInfo user, string id) { DataTable dt = MSSQLHelper.Query(string.Format(@" select * from SWX_MenuMsg where OrgId='{0}' and MenuKey= (select MenuKey from SWX_MenuMsg where Id={1}) order by Sort", user.OrgID, id)).Tables[0]; StringBuilder sb = new StringBuilder(); if (dt.Rows.Count == 1) { #region 单条记录拼HTML sb.Append("<table align='center' cellpadding='0' cellspacing='0' style='border-collapse:collapse; width:310px;'><tr><td>"); string title = dt.Rows[0]["Title"].ToString(); if (title.Length > 36) { title = title.Substring(0, 36); } sb.Append("<div style='width:300px; padding:5px; font-size:15px; font-weight:bold; ' >"); sb.Append(string.Format("{0}", title)); sb.Append("</div>"); sb.Append("<div style='width:300px; padding:5px; font-size:12px; color:#666; ' >"); sb.Append(string.Format("{0}", DateTime.Now.ToString("M月d日"))); sb.Append("</div>"); sb.Append("<div style='width:300px; height:120px; padding:5px; ' >"); sb.Append(string.Format("<img alt='' src='{0}' style='width:300px; height:120px;' />", dt.Rows[0]["PicUrl"].ToString())); sb.Append("</div>"); sb.Append("<div style='width:300px; padding:5px; font-size:12px; color:#666;' >"); sb.Append(string.Format("{0}", dt.Rows[0]["Description"].ToString().Replace("\n", "<br />"))); sb.Append("</div>"); string url = dt.Rows[0]["Url"].ToString(); if (!string.IsNullOrWhiteSpace(url)) { sb.Append("<div style='width:300px; padding:5px; font-size:12px; margin-top:10px;' >"); sb.Append(string.Format("{0}", "查看全文")); sb.Append("</div>"); } sb.Append("</td></tr></table>"); #endregion } else { #region 多条记录拼HTML sb.Append("<table align='center' cellpadding='0' cellspacing='0' style='border-collapse:collapse; width:310px;'><tr><td>"); sb.Append("<div align='center' style='width:300px; height:120px; padding:5px; border-top:solid 1px #ddd; border-bottom:solid 1px #ddd; border-left:solid 1px #ddd; border-right:solid 1px #ddd;' >"); sb.Append(string.Format("<img alt='' src='{0}' style='width:300px; height:120px;' />", dt.Rows[0]["PicUrl"].ToString())); sb.Append("</div>"); string title = dt.Rows[0]["Title"].ToString(); if (title.Length > 36) { title = title.Substring(0, 36); } if (title.Length > 18) { sb.Append("<div style='position:absolute; z-index:999; width:290px; height:33px; font-size:15px; margin-left:6px; margin-top:-49px; padding:5px; color:#fff; font-weight:bold; filter:alpha(opacity=60); -moz-opacity:0.6; -khtml-opacity: 0.6; opacity: 0.6; background-color:#000; ' >"); } else { sb.Append("<div style='position:absolute; z-index:999; width:290px; height:17px; font-size:15px; margin-left:6px; margin-top:-33px; padding:5px; color:#fff; font-weight:bold; filter:alpha(opacity=60); -moz-opacity:0.6; -khtml-opacity: 0.6; opacity: 0.6; background-color:#000; ' >"); } sb.Append(string.Format("{0}", title)); sb.Append("</div>"); for (int i = 1; i < dt.Rows.Count; i++) { sb.Append("<div style='width:300px; padding:5px; border-bottom:solid 1px #ddd; border-left:solid 1px #ddd; border-right:solid 1px #ddd;'>"); sb.Append("<table cellpadding='0' cellspacing='0' style='border-collapse:collapse; width:100%; '><tr>"); string picUrl = dt.Rows[i]["PicUrl"].ToString(); if (string.IsNullOrWhiteSpace(picUrl)) { sb.Append("<td style='padding:5px; font-size:15px; line-height:20px;'>"); sb.Append(dt.Rows[i]["Title"].ToString().Replace("\n", "<br />")); sb.Append("</td>"); } else { sb.Append("<td style='width:250px; padding:5px; font-size:15px; line-height:20px;'>"); sb.Append(dt.Rows[i]["Title"].ToString().Replace("\n", "<br />")); sb.Append("</td>"); sb.Append("<td style='vertical-align:top; '>"); sb.Append(string.Format("<img alt='' src='{0}' style='width:50px; height:50px;' />", dt.Rows[i]["PicUrl"].ToString())); sb.Append("</td>"); } sb.Append("</tr></table>"); sb.Append("</div>"); } sb.Append("</td></tr></table>"); #endregion } return(sb.ToString()); }
public static OperationResult EditReceiveTransportPoint(EditReceiveTransportPointModel model, int currentUserID) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@AddressName", SqlDbType.NVarChar), new SqlParameter("@AddressCode", SqlDbType.NVarChar), new SqlParameter("@CustomerName", SqlDbType.NVarChar), new SqlParameter("@AddressArea", SqlDbType.NVarChar), new SqlParameter("@SuperiorAddressID", SqlDbType.Int), new SqlParameter("@IsUnloadPoint", SqlDbType.Bit), new SqlParameter("@EFType", SqlDbType.TinyInt), new SqlParameter("@EFInfo", SqlDbType.NVarChar), new SqlParameter("@UnloadTime", SqlDbType.Int), new SqlParameter("@UnloadTimeError", SqlDbType.Int), new SqlParameter("@UpdateUser", SqlDbType.Int), new SqlParameter("@UpdateTime", SqlDbType.DateTime), new SqlParameter("@ID", SqlDbType.Int), }; paras[0].Value = model.AddressName.Trim(); paras[1].Value = model.AddressCode.Trim(); paras[2].Value = model.CustomerName.Trim(); paras[3].Value = model.AddressArea.Trim(); //无上级收货地址时,SuperiorAddressID赋空值 if (model.SuperiorAddressID == -1) { paras[4].Value = DBNull.Value; } else { paras[4].Value = model.SuperiorAddressID; } paras[5].Value = model.IsUnloadPoint; //不是卸货点,紧紧是收货点时,以下信息为空 if (model.IsUnloadPoint == false) { paras[6].Value = DBNull.Value; paras[7].Value = DBNull.Value; paras[8].Value = DBNull.Value; paras[9].Value = DBNull.Value; } else //是卸货点时 { paras[6].Value = model.EFType; paras[7].Value = ChangeCoordinateSystem(model.EFType, model.EFInfo, 1); //地图坐标转车机坐标,存入数据库 //暂时去掉预计卸货时长和误差 操作人:戴天辰 paras[8].Value = DBNull.Value; paras[9].Value = DBNull.Value; //paras[8].Value = model.UnloadTime * 60; //数据库存的UnloadTime以秒为单位 //if (model.UnloadTimeError == null) //误差前台没有做验证,这里做下处理 //{ // paras[9].Value = DBNull.Value; //} //else //{ // paras[9].Value = model.UnloadTimeError * 60; //数据库存的UnloadTimeError以秒为单位 //} } paras[10].Value = currentUserID; paras[11].Value = DateTime.Now; paras[12].Value = model.ID; #region SQL string sql = @"UPDATE dbo.MGJH_TransportPointSetting SET AddressName = @AddressName , AddressCode = @AddressCode , CustomerName = @CustomerName , AddressArea = @AddressArea , SuperiorAddressID = @SuperiorAddressID , IsUnloadPoint = @IsUnloadPoint , EFType = @EFType , EFInfo = @EFInfo , UnloadTime = @UnloadTime , UnloadTimeError = @UnloadTimeError , UpdateUser = @UpdateUser, UpdateTime = @UpdateTime WHERE ID = @ID"; #endregion int result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()); string msg = string.Empty; switch (result) { case 1: msg = PromptInformation.OperationSuccess; break; case 0: msg = PromptInformation.NotExists; break; case -1: msg = PromptInformation.DBError; break; } return(new OperationResult() { Success = result > 0, Message = msg }); }
/// <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 <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 List <SuperiorAddressModel> GetAddSuperiorAddress() { string sql = "SELECT ID,AddressName FROM dbo.MGJH_TransportPointSetting WHERE SettingType=2 "; return(ConvertToList <SuperiorAddressModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
/// <summary> /// 根据终端号获取终端所属服务器信息 /// </summary> public static SelectResult <TerminalServerInfoModel> GetTerminalServerInfo(string terminalCode) { string sql = @"SELECT LinkedServerName,WCFAddress FROM dbo.ServerInfo s INNER JOIN dbo.Terminals t ON s.ID=t.ServerInfoID WHERE t.TerminalCode=@TerminalCode"; SqlParameter para = new SqlParameter() { ParameterName = "@TerminalCode", SqlDbType = SqlDbType.VarChar, Size = 20, Value = terminalCode.Trim() }; List <TerminalServerInfoModel> list = ConvertToList <TerminalServerInfoModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, para)); TerminalServerInfoModel 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 <TerminalServerInfoModel>() { DataResult = data, Message = msg }); }
public static SelectResult <ServerManagerEditModel> GetServerByID(int id) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@ID", SqlDbType.Int), }; paras[0].Value = id; string sql = @"SELECT [ID],[ServerCode],[ServerName],[LinkedServerName],IP,WCFAddress FROM [dbo].[ServerInfo] WHERE ID = @ID"; List <ServerManagerEditModel> list = ConvertToList <ServerManagerEditModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); ServerManagerEditModel 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 <ServerManagerEditModel>() { DataResult = data, Message = msg }); }
public static AsiatekPagedList <ServerManagerListModel> GetPagedServerManager(ServerManagerSearchModel model, int searchPage, int pageSize) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@tableName", "ServerInfo"), new SqlParameter("@pageSize", pageSize), new SqlParameter("@currentPage", searchPage), new SqlParameter("@orderBy", "ID"), new SqlParameter("@showColumns", "ID,ServerCode,ServerName,LinkedServerName,IP,WCFAddress"), }; if (!string.IsNullOrWhiteSpace(model.ServerName)) { paras.Add(new SqlParameter("@conditionStr", "ServerName LIKE '%" + model.ServerName + "%'")); } 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 <ServerManagerListModel> list = ConvertToList <ServerManagerListModel> .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 <ServerInfoDDLModel> GetServerInfoList() { string sql = "SELECT [ID],[ServerName] FROM [dbo].[ServerInfo]"; return(ConvertToList <ServerInfoDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql))); }
/// <summary> /// 新增角色信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static OperationResult AddRoleInfo(RoleAddModel model, int CreateUserID) { int count = 1 + model.FunctionIDs.Count; string[] sqls = new string[count]; #region 新增角色信息到角色表 sqls[0] = @"INSERT INTO dbo.Roles ( RoleName , [Description],CreateUserID )VALUES ( @RoleName ,@Description ,@CreateUserID);SELECT SCOPE_IDENTITY()"; SqlParameter[][] paras = new SqlParameter[count][]; paras[0] = new SqlParameter[3]; paras[0][0] = new SqlParameter() { ParameterName = "@RoleName", SqlDbType = SqlDbType.NVarChar, Size = 200, Value = model.RoleName.Trim() }; paras[0][1] = new SqlParameter("@Description", SqlDbType.NVarChar, 500); paras[0][2] = new SqlParameter("@CreateUserID", SqlDbType.Int); if (string.IsNullOrWhiteSpace(model.Description)) { paras[0][1].Value = DBNull.Value; } else { paras[0][1].Value = model.Description; } paras[0][2].Value = CreateUserID; #endregion #region 新增角色与功能关联信息 for (int i = 0; i < model.FunctionIDs.Count; i++) { int index = i + 1; sqls[index] = @"INSERT INTO dbo.RolesFunctions( RoleID, FunctionID ) VALUES ( @RoleID, @FunctionID )"; paras[index] = new SqlParameter[2]; paras[index][0] = new SqlParameter { ParameterName = "@RoleID", SqlDbType = SqlDbType.Int }; paras[index][1] = new SqlParameter() { ParameterName = "@FunctionID", SqlDbType = SqlDbType.Int, Value = model.FunctionIDs[i] }; } #endregion bool result = MSSQLHelper.ExecuteIdentityIncludeTransaction(CommandType.Text, sqls, paras) != 0; return(new OperationResult() { Success = result, Message = result ? PromptInformation.OperationSuccess : PromptInformation.DBError }); }
/// <summary> /// 修改角色信息 /// 修改角色名称、角色描述 /// 同时先删除所有角色与功能关联信息,再重新添加角色与功能关联信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static OperationResult ModifyRoleInfo(RoleEditModel model, int EditUserID) { int count = 2 + model.FunctionIDs.Count; string[] sqls = new string[count]; #region 更新角色 sqls[0] = @"UPDATE dbo.Roles SET RoleName=@RoleName,[Description]=@Description,EditUserID=@EditUserID,EditTime=@EditTime WHERE ID=@ID"; SqlParameter[][] paras = new SqlParameter[count][]; paras[0] = new SqlParameter[5]; paras[0][0] = new SqlParameter() { ParameterName = "@RoleName", SqlDbType = SqlDbType.NVarChar, Size = 200, Value = model.RoleName.Trim() }; paras[0][1] = new SqlParameter("@Description", SqlDbType.NVarChar, 500); if (string.IsNullOrWhiteSpace(model.Description)) { paras[0][1].Value = DBNull.Value; } else { paras[0][1].Value = model.Description; } paras[0][2] = new SqlParameter() { ParameterName = "@ID", SqlDbType = SqlDbType.Int, Value = model.ID }; paras[0][3] = new SqlParameter("@EditUserID", SqlDbType.Int); paras[0][4] = new SqlParameter("@EditTime", SqlDbType.DateTime); paras[0][3].Value = EditUserID; paras[0][4].Value = DateTime.Now; #endregion #region 除角色功能关联信息 sqls[1] = @"DELETE FROM dbo.RolesFunctions WHERE RoleID=@RoleID"; paras[1] = new SqlParameter[1]; paras[1][0] = new SqlParameter() { ParameterName = "@RoleID", SqlDbType = SqlDbType.Int, Value = model.ID }; #endregion for (int i = 0; i < model.FunctionIDs.Count; i++) { int index = i + 2; sqls[index] = @"INSERT INTO dbo.RolesFunctions( RoleID, FunctionID ) VALUES ( @RoleID, @FunctionID )"; paras[index] = new SqlParameter[2]; paras[index][0] = new SqlParameter() { ParameterName = "@RoleID", SqlDbType = SqlDbType.Int, Value = model.ID }; paras[index][1] = new SqlParameter() { ParameterName = "@FunctionID", SqlDbType = SqlDbType.Int, Value = model.FunctionIDs[i] }; } bool result = MSSQLHelper.ExecuteTransaction(CommandType.Text, sqls, paras); return(new OperationResult() { Success = result, Message = result ? PromptInformation.OperationSuccess : PromptInformation.DBError }); }
/// <summary> /// 获取待切换用户信息 /// </summary> public static OperationResult GetUserForSwitch(int id, out UserSessionModel userSession) { string sql = @"SELECT u.ID,RoleID,r.RoleName,r.RoleLevel,u.UserName,u.NickName,u.StrucID,u.VehicleViewMode FROM dbo.Users u INNER JOIN dbo.Roles r ON u.RoleID=r.ID INNER JOIN dbo.Structures s ON u.StrucID=s.ID WHERE u.ID=@ID"; List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@ID", SqlDbType.Int), }; paras[0].Value = id; DataTable dt = MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()); OperationResult result = new OperationResult(); userSession = null; if (dt == null) { result.Message = PromptInformation.DBError; return(result); } if (dt.Rows.Count == 0) { result.Message = PromptInformation.NotExists; return(result); } //获取到用户信息,组装session内容 int userID = Convert.ToInt32(dt.Rows[0][0].ToString()); int roleID = Convert.ToInt32(dt.Rows[0][1]); string roleName = dt.Rows[0][2].ToString(); int roleLevel = Convert.ToInt32(dt.Rows[0][3]); userSession = new UserSessionModel(); userSession.UserName = dt.Rows[0][4].ToString(); userSession.NickName = dt.Rows[0][5].ToString(); userSession.StrucID = Convert.ToInt32(dt.Rows[0][6]); userSession.VehicleViewMode = string.IsNullOrEmpty(dt.Rows[0][7].ToString()) ? true : Convert.ToBoolean(dt.Rows[0][7]); userSession.UserId = Convert.ToInt32(userID); userSession.RoleInfo = new RoleInfoModel() { RoleID = roleID, RoleLevel = (RoleLevelEnum)roleLevel, RoleName = roleName }; //获取用户功能信息 List <FunctionsInfoModel> funcList = null; if (userSession.RoleInfo.RoleLevel == RoleLevelEnum.SuperAdmin) { funcList = FunctionBLL.GetAllFunctions(); } else { funcList = FunctionBLL.GetFunctionsByUserID(userID); } if (funcList == null) { result.Message = Asiatek.Resource.PromptInformation.GetFunctionsError; return(result); } if (funcList.Count == 0) { result.Message = Asiatek.Resource.PromptInformation.NoFunctions; return(result); } userSession.Functions = funcList; result.Success = true; return(result); }
//获取指定需要同步的收货地址信息 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 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 }); }
public static SelectResult <VehicleTypeEditModel> GetVehicleTypeID(int id) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@Code", SqlDbType.TinyInt), }; paras[0].Value = id; string sql = @"SELECT Code ,Name ,CAST(ParentCode AS nvarchar(10))as ParentCode FROM dbo.VehicleTypes WHERE Code=@Code"; List <VehicleTypeEditModel> list = ConvertToList <VehicleTypeEditModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); VehicleTypeEditModel 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 <VehicleTypeEditModel>() { DataResult = data, Message = msg }); }
public static OperationResult AddReceiveTransportPoint(AddReceiveTransportPointModel model, int currentUserID) { #region 参数 List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@SettingType", SqlDbType.TinyInt), new SqlParameter("@AddressName", SqlDbType.NVarChar), new SqlParameter("@AddressCode", SqlDbType.NVarChar), new SqlParameter("@CustomerName", SqlDbType.NVarChar), new SqlParameter("@AddressArea", SqlDbType.NVarChar), new SqlParameter("@SuperiorAddressID", SqlDbType.Int), new SqlParameter("@IsUnloadPoint", SqlDbType.Bit), new SqlParameter("@EFType", SqlDbType.TinyInt), new SqlParameter("@EFInfo", SqlDbType.NVarChar), new SqlParameter("@UnloadTime", SqlDbType.Int), new SqlParameter("@UnloadTimeError", SqlDbType.Int), new SqlParameter("@CreateUser", SqlDbType.Int), new SqlParameter("@CreateTime", SqlDbType.DateTime), new SqlParameter("@SourceID", SqlDbType.Int), }; paras[0].Value = 2; //2-收货点 paras[1].Value = model.AddressName.Trim(); paras[2].Value = model.AddressCode.Trim(); paras[3].Value = model.CustomerName.Trim(); paras[4].Value = model.AddressArea.Trim(); //无上级收货地址时,SuperiorAddressID赋空值 if (model.SuperiorAddressID == -1) { paras[5].Value = DBNull.Value; } else { paras[5].Value = model.SuperiorAddressID; } paras[6].Value = model.IsUnloadPoint; //不是卸货点,紧紧是收货点时,以下信息为空 if (model.IsUnloadPoint == false) { paras[7].Value = DBNull.Value; paras[8].Value = DBNull.Value; paras[9].Value = DBNull.Value; paras[10].Value = DBNull.Value; } else //是卸货点时 { paras[7].Value = model.EFType; paras[8].Value = ChangeCoordinateSystem(model.EFType, model.EFInfo, 1); //地图坐标转车机坐标,存入数据库 //暂时去掉预计卸货时长和误差 操作人:戴天辰 paras[9].Value = DBNull.Value; paras[10].Value = DBNull.Value; //paras[9].Value = model.UnloadTime * 60; //数据库存的UnloadTime以秒为单位 //if (model.UnloadTimeError == null) //误差前台没有做验证,这里做下处理 //{ // paras[10].Value = DBNull.Value; //} //else //{ // paras[10].Value = model.UnloadTimeError * 60; //数据库存的UnloadTimeError以秒为单位 //} } paras[11].Value = currentUserID; paras[12].Value = DateTime.Now; paras[13].Value = model.SourceID ?? (object)DBNull.Value; #endregion #region SQL string sql = @"INSERT INTO dbo.MGJH_TransportPointSetting ( [SettingType] ,[AddressName] ,[AddressCode] ,[CustomerName] ,[AddressArea] ,[SuperiorAddressID] ,[IsUnloadPoint] ,[EFType] ,[EFInfo] ,[UnloadTime] ,[UnloadTimeError] ,[CreateUser] ,[CreateTime] ) VALUES ( @SettingType , @AddressName, @AddressCode, @CustomerName , @AddressArea, @SuperiorAddressID, @IsUnloadPoint , @EFType, @EFInfo, @UnloadTime , @UnloadTimeError, @CreateUser, @CreateTime )"; bool result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()) > 0; #endregion return(new OperationResult() { Success = result, Message = result ? PromptInformation.OperationSuccess : PromptInformation.DBError }); }
public static OperationResult EditVehicleType(VehicleTypeEditModel model, int EditUserID) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@Code", SqlDbType.TinyInt), new SqlParameter("@Name", SqlDbType.VarChar, 13), new SqlParameter("@ID", SqlDbType.TinyInt), new SqlParameter("@EditTime", SqlDbType.DateTime), new SqlParameter("@EditUserID", SqlDbType.Int) }; paras[0].Value = model.Code; paras[1].Value = model.Name.Trim(); paras[2].Value = model.ID; paras[3].Value = DateTime.Now; paras[4].Value = EditUserID; #region SQL string sql; #endregion if (!string.IsNullOrWhiteSpace(model.ParentCode)) { paras.Add(new SqlParameter("@ParentCode", SqlDbType.TinyInt)); paras[3].Value = model.ParentCode; sql = @"UPDATE dbo.VehicleTypes SET Code = @ID ,Name=@Name, ParentCode = @ParentCode,EditUserID=@EditUserID,EditTime=@EditTime WHERE Code = @ID"; } else { sql = @"UPDATE dbo.VehicleTypes SET Code = @ID ,Name=@Name,EditUserID=@EditUserID,EditTime=@EditTime WHERE Code = @ID"; } int result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()); string msg = string.Empty; switch (result) { case 1: msg = PromptInformation.OperationSuccess; break; case 0: msg = PromptInformation.NotExists; break; case -1: msg = PromptInformation.DBError; break; } return(new OperationResult() { Success = result > 0, Message = msg }); }
public static SelectResult <EditReceiveTransportPointModel> GetReceiveTransportPointByID(int id) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter() { ParameterName = "@ID", SqlDbType = SqlDbType.Int, }, }; paras[0].Value = id; string sql = @"SELECT 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 FROM dbo.MGJH_TransportPointSetting tp LEFT JOIN dbo.MGJH_TransportPointSetting tp2 ON tp2.ID=tp.SuperiorAddressID WHERE tp.ID=@ID"; List <EditReceiveTransportPointModel> list = ConvertToList <EditReceiveTransportPointModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); EditReceiveTransportPointModel 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 <EditReceiveTransportPointModel>() { DataResult = data, Message = msg }); }
public static OperationResult AddTerminalType(TerminalTypeAddModel model, int CreateUserID) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@TerminalName", SqlDbType.NVarChar, 200), new SqlParameter("@TerminalManufacturerID", SqlDbType.Int), new SqlParameter("@ACCON_Frequency", SqlDbType.Int), new SqlParameter("@ACCOFF_Frequency", SqlDbType.Int), new SqlParameter("@Filter", SqlDbType.Bit), new SqlParameter("@CommunicationMode", SqlDbType.NVarChar, 50), new SqlParameter("@Remark", SqlDbType.NVarChar, 500), new SqlParameter("@CreateUserID", SqlDbType.Int), }; paras[0].Value = model.TerminalName.Trim(); paras[1].Value = model.TerminalManufacturerID; paras[2].Value = model.ACCON_Frequency; paras[3].Value = model.ACCOFF_Frequency; paras[4].Value = model.Filter; if (string.IsNullOrWhiteSpace(model.CommunicationMode)) { paras[5].Value = DBNull.Value; } else { paras[5].Value = model.CommunicationMode.Trim(); } if (string.IsNullOrWhiteSpace(model.Remark)) { paras[6].Value = DBNull.Value; } else { paras[6].Value = model.Remark.Trim(); } paras[7].Value = CreateUserID; #region SQL string sql = @"INSERT INTO dbo.TerminalTypes ( TerminalName , TerminalManufacturerID , ACCON_Frequency , ACCOFF_Frequency , Filter , CommunicationMode , Remark, CreateUserID ) VALUES ( @TerminalName , -- TerminalName - nvarchar(20) @TerminalManufacturerID , -- TerminalManufacturerID - int @ACCON_Frequency , -- ACCON_Frequency - int @ACCOFF_Frequency , -- ACCOFF_Frequency - int @Filter , -- Filter - bit @CommunicationMode , -- CommunicationMode - nvarchar(50) @Remark, -- Remark - nvarchar(50) @CreateUserID )"; #endregion bool result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()) > 0; return(new OperationResult() { Success = result, Message = result ? PromptInformation.OperationSuccess : PromptInformation.DBError }); }
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 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)); }
/// <summary> /// 根据控制器编号获取待修改的控制器信息 /// </summary> /// <param name="controllerID">控制器编号</param> /// <returns></returns> public static SelectResult <ControllerEditModel> GetControllerInfoByID(int controllerID) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@ID", SqlDbType.Int), }; paras[0].Value = controllerID; string sql = "SELECT * FROM Controllers WHERE ID=@ID"; List <ControllerEditModel> list = ConvertToList <ControllerEditModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())); ControllerEditModel 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 <ControllerEditModel>() { DataResult = data, Message = msg }); }
public static OperationResult EditTerminalType(TerminalTypeEditModel model, int EditUserID) { List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@TerminalName", SqlDbType.NVarChar, 200), new SqlParameter("@TerminalManufacturerID", SqlDbType.Int), new SqlParameter("@ACCON_Frequency", SqlDbType.Int), new SqlParameter("@ACCOFF_Frequency", SqlDbType.Int), new SqlParameter("@Filter", SqlDbType.Bit), new SqlParameter("@CommunicationMode", SqlDbType.NVarChar, 50), new SqlParameter("@Remark", SqlDbType.NVarChar, 500), new SqlParameter("@ID", SqlDbType.Int), new SqlParameter("@EditUserID", SqlDbType.Int), new SqlParameter("@EditTime", SqlDbType.DateTime) }; paras[0].Value = model.TerminalName.Trim(); paras[1].Value = model.TerminalManufacturerID; paras[2].Value = model.ACCON_Frequency; paras[3].Value = model.ACCOFF_Frequency; paras[4].Value = model.Filter; if (string.IsNullOrWhiteSpace(model.CommunicationMode)) { paras[5].Value = DBNull.Value; } else { paras[5].Value = model.CommunicationMode.Trim(); } if (string.IsNullOrWhiteSpace(model.Remark)) { paras[6].Value = DBNull.Value; } else { paras[6].Value = model.Remark; } paras[7].Value = model.ID; paras[8].Value = EditUserID; paras[9].Value = DateTime.Now; #region SQL string sql = @"UPDATE dbo.TerminalTypes SET TerminalName = @TerminalName , TerminalManufacturerID = @TerminalManufacturerID , ACCON_Frequency = @ACCON_Frequency , ACCOFF_Frequency = @ACCOFF_Frequency , Filter = @Filter , CommunicationMode = @CommunicationMode , Remark = @Remark, EditTime=@EditTime, EditUserID=@EditUserID WHERE ID = @ID"; #endregion int result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()); string msg = string.Empty; switch (result) { case 1: msg = PromptInformation.OperationSuccess; break; case 0: msg = PromptInformation.NotExists; break; case -1: msg = PromptInformation.DBError; break; } return(new OperationResult() { Success = result > 0, Message = msg }); }
/// <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))); }
/// <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))); }
/// <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))); }
/// <summary> /// 用户登录 /// </summary> /// <param name="user">用户登录数据</param> /// <param name="userSession">登录后返回的用户相关信息</param> /// <returns></returns> public static OperationResult Login(UserLoginModel user, out UserSessionModel userSession) { string sql = @"SELECT u.ID,RoleID,r.RoleName,r.RoleLevel,u.NickName,u.StrucID,u.VehicleViewMode FROM dbo.Users u INNER JOIN dbo.Roles r ON u.RoleID=r.ID INNER JOIN dbo.Structures s ON u.StrucID=s.ID WHERE u.UserName=@UserName AND u.UserPassword=@UserPassword AND s.StrucAccount=@StrucAccount"; List <SqlParameter> paras = new List <SqlParameter>() { new SqlParameter("@UserName", SqlDbType.VarChar, 20), new SqlParameter("@UserPassword", SqlDbType.Char, 32), new SqlParameter("@StrucAccount", SqlDbType.VarChar, 50), }; paras[0].Value = user.UserName.Trim(); paras[1].Value = MD5Helper.GetMD5Str(user.Password.Trim()); paras[2].Value = user.StrucAccount.Trim(); DataTable dt = MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()); OperationResult result = new OperationResult(); userSession = null; if (dt == null) { result.Message = Asiatek.Resource.PromptInformation.LoginDBError; return(result); } if (dt.Rows.Count == 0) { result.Message = Asiatek.Resource.PromptInformation.LoginError; return(result); } //获取到用户信息,组装session内容 int userID = Convert.ToInt32(dt.Rows[0][0].ToString()); int roleID = Convert.ToInt32(dt.Rows[0][1]); string roleName = dt.Rows[0][2].ToString(); int roleLevel = Convert.ToInt32(dt.Rows[0][3]); userSession = new UserSessionModel(); userSession.UserName = user.UserName.Trim(); userSession.NickName = dt.Rows[0][4].ToString(); userSession.StrucID = Convert.ToInt32(dt.Rows[0][5]); userSession.UserId = Convert.ToInt32(userID); userSession.RoleInfo = new RoleInfoModel() { RoleID = roleID, RoleLevel = (RoleLevelEnum)roleLevel, RoleName = roleName }; userSession.VehicleViewMode = string.IsNullOrEmpty(dt.Rows[0][6].ToString()) ? true : Convert.ToBoolean(dt.Rows[0][6]); //获取用户功能信息 List <FunctionsInfoModel> funcList = null; if (userSession.RoleInfo.RoleLevel == RoleLevelEnum.SuperAdmin) { funcList = FunctionBLL.GetAllFunctions(); } else { funcList = FunctionBLL.GetFunctionsByUserID(userID); } if (funcList == null) { result.Message = Asiatek.Resource.PromptInformation.GetFunctionsError; return(result); } if (funcList.Count == 0) { result.Message = Asiatek.Resource.PromptInformation.NoFunctions; return(result); } userSession.Functions = funcList; result.Success = true; return(result); }