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