/// <summary>
/// 创建解除角色菜单按钮权限的sql
/// </summary>
/// <param name="cb"></param>
/// <param name="v"></param>
/// <returns></returns>
private string CreateUnDistributionButtonSql(List <dynamic> cb, string v)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < cb.Count; i++)
{
sb.AppendLine(
DBSqlHelper.GetUpdateSQL <EHECD_PrivilegeDTO>(
new EHECD_PrivilegeDTO {
bIsDeleted = true
},
string.Format(
@"WHERE
sPrivilegeMaster = 'role' --特权分配给角色的
AND sPrivilegeMasterValue = '{0}' --角色ID
AND sBelong = 'menu' --属于菜单的
AND sBelongValue = '{2}' --菜单ID
AND sPrivilegeAccess = 'button' --特权类型是按钮
AND sPrivilegeAccessValue = '{1}'--按钮ID",
v, cb[i].id.ToString(), cb[i].menuid.ToString()
)
)
);
}
return(sb.ToString());
}
//删除角色
public override int DeleteRole(string ID, dynamic p)
{
StringBuilder sb = new StringBuilder();
//1.逻辑删除这个角色的所有特权
sb.Append(DBSqlHelper.GetUpdateSQL <EHECD_PrivilegeDTO>(new EHECD_PrivilegeDTO {
bIsDeleted = true
}, string.Format("WHERE ((sPrivilegeMaster = 'role' AND sPrivilegeMasterValue = '{0}'/*类别是角色*/) OR (sBelong = 'role' AND sBelongValue = '{0}'/*所有者是角色*/) OR (sPrivilegeAccess = 'role' AND sPrivilegeAccessValue = '{0}'/*提供者是角色*/))", ID)));
//2.逻辑删除这个角色
sb.Append(DBSqlHelper.GetUpdateSQL <EHECD_RoleDTO>(new EHECD_RoleDTO {
ID = Guid.Parse(ID), bIsDeleted = true
}, String.Format("WHERE ID = '{0}'", ID)));
//3.逻辑删除这个角色绑定的客户
sb.Append(DBSqlHelper.GetUpdateSQL <EHECD_SystemUser_R_RoleDTO>(new EHECD_SystemUser_R_RoleDTO {
sRoleID = Guid.Parse(ID), bIsDeleted = true
}, String.Format("WHERE sRoleID = '{0}'", ID)));
var ret = excute.ExcuteTransaction(sb.ToString());
//4.记录系统日志
InsertSystemLog(
p.sLoginName.ToString(),
p.sUserName.ToString(),
p.IP.ToString(),
(Int16)(SYSTEM_LOG_TYPE.DELETE | SYSTEM_LOG_TYPE.ROLE),
"系统用户删除角色" + ID,
ID,
ret > 0);
return(ret);
}
/// <summary>
/// 根据条件删除，此删除方法，不支持分表数据的删除
/// </summary>
/// <typeparam name="T">要删除的类型</typeparam>
/// <param name="where">删除条件</param>
/// <param name="paras">参数</param>
/// <returns>影响行数</returns>
public int Delete <T>(string where, params object[] paras)
{
Type type = typeof(T);
DBTable table = MapHelper.GetDBTable(type);
DBSql dbsql = DBSqlHelper.GetDBSql(table.Name, _dbContext.DataType);
where = FormatWhereOrder(table, where);
Command.CommandText = string.Format("delete from {0} {1}", table.Name, where.Length > 0 ? " where " + where : "");
Command.CommandType = CommandType.Text;
Command.Parameters.Clear();
int i = 0;
foreach (object obj in paras)
{
AddParameter(FormatParameterName("p" + (i++).ToString()), ParameterDirection.Input, obj);
}
return(Command.ExecuteNonQuery());
}
/// <summary>
/// 写入数据
/// </summary>
/// <param name="instance"></param>
/// <param name="type">映射表类型</param>
/// <param name="date">要写入表的拆分日期。注意该日期必须与Id中的日期对应。比如按年拆分的表，Id中的年份与该日期中的年份必须相同</param>
public void Insert(object instance, Type type, DateTime date)
{
DBTable table = MapHelper.GetDBTable(type);
//取主键值
object primaryVal = null;
if (table.PrimaryKey[0].DBPrimaryType != DBPrimaryType.Identity)
{
primaryVal = this.GetValue(table.PrimaryKey[0], instance);
if (primaryVal == null)
{
throw new MyDBException("新增对象，非自增长表主键不能为空");
}
}
//取表名。如果是拆分表,则获取拆分表名
string tbName = table.Name;
if (table.SeparateType != SeparateType.None)
{
//如果传入时间为空，则取myId中的时间
if (date == DateTime.MinValue)
{
date = MyIdMake.GetMyIdDate(primaryVal);
}
//获取数据库表名
tbName = TableSeparate.GetTableName(table, date);
}
//获取该数据库表 对应的DBSql中的Insert语句
DBSql dbsql = DBSqlHelper.GetDBSql(tbName, _dbContext.DataType);
if (string.IsNullOrEmpty(dbsql.InsertSql))//如果该表的新增语句为空，则生成该表的Insert语句
{
dbsql.InsertSql = GetInsertSql(table, tbName);
}
//将数据写入数据库
Insert(instance, primaryVal, table, dbsql.InsertSql);
}
/// <summary>
/// 创建解除角色菜单的sql
/// </summary>
/// <param name="cm">要解除的菜单集合</param>
/// <param name="v">要解除的角色</param>
/// <returns>sql</returns>
private string CreateUnDistributionMenuSql(List <string> cm, string v)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < cm.Count; i++)
{
sb.AppendLine(
DBSqlHelper.GetUpdateSQL <EHECD_PrivilegeDTO>(
new EHECD_PrivilegeDTO {
bIsDeleted = true
},
string.Format(
"WHERE sPrivilegeMaster = 'role' AND sBelong = 'role' AND sPrivilegeMasterValue = '{0}' AND sBelongValue = '{0}' AND sPrivilegeAccess = 'menu' AND sPrivilegeAccessValue = '{1}'",
v, cm[i]
)
)
);
}
return(sb.ToString());
}
//添加角色
public override int AddRole(dynamic data, dynamic p)
{
//1.创建角色对象
EHECD_RoleDTO role = new EHECD_RoleDTO
{
ID = GuidHelper.GetSecuentialGuid(),
bEnable = Convert.ToBoolean(data.bEnable.Value),
bIsDeleted = false,
dCreateTime = DateTime.Now,
dModifyTime = DateTime.Now,
iOrder = Convert.ToInt32(data.iOrder.Value),
sRoleName = data.sRoleName.Value
};
var sqlIf = @"IF EXISTS(SELECT 1 FROM EHECD_Role WHERE sRoleName = @sRoleName)
BEGIN
SELECT -1 RET;
END
ELSE
BEGIN
{0}
END;";
sqlIf = string.Format(sqlIf, DBSqlHelper.GetInsertSQL <EHECD_RoleDTO>(role));
//2.插入角色信息
var ret = excute.Insert(sqlIf, new { sRoleName = role.sRoleName });
//3.记录系统日志
InsertSystemLog(
p.sLoginName.ToString(),
p.sUserName.ToString(),
p.IP.ToString(),
(Int16)(SYSTEM_LOG_TYPE.ADD | SYSTEM_LOG_TYPE.ROLE),
"系统用户添加角色" + role.ID,
role.ID.ToString(),
ret > 0);
return(ret);
}
/// <summary>
/// 修改对象，支持分表数据的修改
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="instance"></param>
/// <returns>受影响的行数</returns>
public int Update <T>(T instance)
{
Type type = typeof(T);
DBTable table = MapHelper.GetDBTable(type);
//获取修改对象的主键
object primaryVal = this.GetValue(table.PrimaryKey[0], instance);
//获取主键对应的数据库表名
string tbName = TableSeparate.GetTableName(table, primaryVal);
DBSql dbsql = DBSqlHelper.GetDBSql(tbName, _dbContext.DataType);
if (string.IsNullOrEmpty(dbsql.UpdateSql))//如果该表的修改语句为空，则生成该表的update语句
{
dbsql.UpdateSql = GetUpdateSql(table, tbName);
}
//修改数据
int retVal = Update <T>(instance, table, dbsql.UpdateSql, primaryVal);
return(retVal);
}
/// <summary>
/// This method will try to connect to the database.
/// </summary>
private async Task ConnectToDBAsync()
{
// Reset the values so we can start anew.
ConnectedSuccessfully = false;
ErroredConnecting = false;
// Build connection string and initialize connection with it.
Task <bool> result = DBSqlHelper.InitializeConnectionAsync();
bool conected = await result;
// Check if conection to database has been successfully established.
if (conected)
{
ConnectedSuccessfully = true;
ConnectedStatusTextBlock.Text = "Connected";
}
else
{
ErroredConnecting = true;
ConnectedStatusTextBlock.Text = "Error connecting";
}
}
//删除用户
public override int DeleteSystemUser(EHECD_SystemUserDTO user, dynamic p)
{
StringBuilder sb = new StringBuilder();
//1.删除用户
sb.AppendLine(DBSqlHelper.GetUpdateSQL <EHECD_SystemUserDTO>(new EHECD_SystemUserDTO()
{
bIsDeleted = true
}, string.Format("where ID = '{0}'", user.ID)));
//2.删除用户的特权
sb.AppendLine(DBSqlHelper.GetUpdateSQL <EHECD_PrivilegeDTO>(new EHECD_PrivilegeDTO()
{
bIsDeleted = true
}, string.Format("where ((sPrivilegeMaster = 'user' AND sPrivilegeMasterValue = '{0}') OR (sBelong = 'user' AND sBelongValue = '{0}'))", user.ID)));
//3.解除用户权限
sb.AppendLine(DBSqlHelper.GetUpdateSQL <EHECD_SystemUser_R_RoleDTO>(new EHECD_SystemUser_R_RoleDTO()
{
bIsDeleted = true
}, string.Format("where sUserID = '{0}' AND bIsDeleted = 0", user.ID)));
var ret = excute.ExcuteTransaction(sb.ToString());
//3.记录系统日志
InsertSystemLog(
p.sLoginName.ToString(),
p.sUserName.ToString(),
p.IP.ToString(),
(Int16)(SYSTEM_LOG_TYPE.DELETE | SYSTEM_LOG_TYPE.SYSTEMUSER),
"系统用户删除用户" + user.ID,
user.ID.ToString(),
ret > 0);
return(ret);
}
/// <summary>
/// 根据主键删除，支持分表数据的删除
/// </summary>
/// <typeparam name="T">要删除的类型</typeparam>
/// <typeparam name="IdT">主键类型</typeparam>
/// <param name="id">主键值</param>
/// <returns>影响行数</returns>
public int Delete <T, IdT>(IdT id)
{
Type type = typeof(T);
DBTable table = MapHelper.GetDBTable(type);
if (table.PrimaryKey.Count > 1)
{
throw new Exception("联合主键表，不支持根据主键删除");
}
//获取对应的数据库表名
string tbName = TableSeparate.GetTableName(table, id);
DBSql dbsql = DBSqlHelper.GetDBSql(tbName, _dbContext.DataType);
if (string.IsNullOrEmpty(dbsql.DeleteSql))//如果该表的修改语句为空，则生成该表的update语句
{
dbsql.DeleteSql = GetDeleteSql(table, tbName);
}
int retVal = Delete <T, IdT>(id, table, dbsql.DeleteSql);
return(retVal);
}
private void ContentDialog_PrimaryButtonClick(ContentDialog sender, ContentDialogButtonClickEventArgs args)
{
string CurrentPassword, NewPassword, NewPasswordRetype;
CurrentPassword = CurrentPasswordTextBox.Password;
NewPassword = NewPasswordTextBox.Password;
NewPasswordRetype = NewPasswordRetypeTextBox.Password;
// Check if all inputs are filled.
if (!string.IsNullOrEmpty(NewPassword) && !string.IsNullOrEmpty(NewPasswordRetype))
{
if (NewLogin)
{
// Check if both inputs are the same.
if (NewPassword == NewPasswordRetype)
{
// Check if password meets our security standards.
Regex regex = new Regex(@"^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#\$%\^&\*])(?=.{8,})");
Match match = regex.Match(NewPassword);
if (match.Success)
{
// Hash the password.
string hashedPassword = DBSqlHelper.SHA512(NewPassword);
string query = "UPDATE user_table " +
"SET pw = @password, first_time_login = 0 " +
"WHERE id_user = @id_user";
SqlCommand cmd = DBSqlHelper.Connection.CreateCommand();
cmd.CommandText = query;
SqlParameter sqlPassword = new SqlParameter("password", SqlDbType.NVarChar);
sqlPassword.Value = hashedPassword;
cmd.Parameters.Add(sqlPassword);
SqlParameter sqlUserId = new SqlParameter("id_user", DbType.Int32);
sqlUserId.Value = UserId;
cmd.Parameters.Add(sqlUserId);
cmd.ExecuteNonQuery();
ContentDialog successDialog = new ContentDialog
{
Title = "Success",
Content = "User details updated successfully!",
CloseButtonText = "OK"
};
App.ShowContentDialog(successDialog, null);
}
else
{
ResetTextBlock.Text = "The password must contain at least 1 lowercase, 1 uppercase, 1 numeric character, one special character, and be be 8 characters or longer!";
NewPasswordTextBox.Password = "";
NewPasswordRetypeTextBox.Password = "";
args.Cancel = true;
}
}
else
{
ResetTextBlock.Text = "Both fields must be the same!";
NewPasswordTextBox.Password = "";
NewPasswordRetypeTextBox.Password = "";
args.Cancel = true;
}
}
else
{
if (!string.IsNullOrEmpty(CurrentPassword))
{
// Check if both inputs are the same.
if (NewPassword == NewPasswordRetype)
{
// Check if password meets our security standards.
Regex regex = new Regex(@"^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#\$%\^&\*])(?=.{8,})");
Match match = regex.Match(NewPassword);
if (match.Success)
{
// Check if the current password is correct.
string hashedPassword = DBSqlHelper.SHA512(CurrentPassword);
string query = "SELECT COUNT(*) FROM user_table " +
"WHERE pw = @password";
SqlCommand cmd = DBSqlHelper.Connection.CreateCommand();
cmd.CommandText = query;
SqlParameter sqlPassword = new SqlParameter("password", SqlDbType.NVarChar);
sqlPassword.Value = hashedPassword;
cmd.Parameters.Add(sqlPassword);
int count = (int)cmd.ExecuteScalar();
if (count >= 1)
{
// Hash the password.
hashedPassword = DBSqlHelper.SHA512(NewPassword);
query = "UPDATE user_table " +
"SET pw = @password, first_time_login = 0 " +
"WHERE id_user = @id_user";
cmd = DBSqlHelper.Connection.CreateCommand();
cmd.CommandText = query;
sqlPassword = new SqlParameter("password", SqlDbType.NVarChar);
sqlPassword.Value = hashedPassword;
cmd.Parameters.Add(sqlPassword);
SqlParameter sqlUserId = new SqlParameter("id_user", DbType.Int32);
sqlUserId.Value = Data.Instance.LoggedInUser.Id;
cmd.Parameters.Add(sqlUserId);
cmd.ExecuteNonQuery();
ContentDialog successDialog = new ContentDialog
{
Title = "Success",
Content = "User details updated successfully!",
CloseButtonText = "OK"
};
App.ShowContentDialog(successDialog, null);
this.Hide();
}
else
{
ResetTextBlock.Text = "Current password is incorrect";
CurrentPasswordTextBox.Password = "";
NewPasswordTextBox.Password = "";
NewPasswordRetypeTextBox.Password = "";
args.Cancel = true;
}
}
else
{
ResetTextBlock.Text = "The password must contain at least 1 lowercase, 1 uppercase, 1 numeric character, 1 special character, and be be 8 characters or longer!";
CurrentPasswordTextBox.Password = "";
NewPasswordTextBox.Password = "";
NewPasswordRetypeTextBox.Password = "";
args.Cancel = true;
}
}
else
{
ResetTextBlock.Text = "Both new password fields must be the same!";
NewPasswordTextBox.Password = "";
NewPasswordRetypeTextBox.Password = "";
args.Cancel = true;
}
}
}
}
}
/// <summary>
/// 创建分配角色按钮权限的sql
/// </summary>
/// <param name="jb"></param>
/// <param name="v"></param>
/// <returns></returns>
private string CreateDistributionButtonSql(List <dynamic> jb, string v)
{
StringBuilder sb = new StringBuilder();
//赋予角色菜单按钮权限的sql
string dsql =
@"IF EXISTS (
SELECT
1
FROM
EHECD_Privilege
WHERE
sPrivilegeMaster = 'role'--分配给角色的
AND sPrivilegeMasterValue = '{0}'--角色ID
AND sPrivilegeAccess = 'button'--特权类型是按钮
AND sPrivilegeAccessValue = '{1}'--按钮ID
AND sBelong = 'menu'--属于菜单的
AND sBelongValue = '{4}'--菜单ID
)
BEGIN
--存在直接更新
{2}
END
ELSE
BEGIN
--不存在就插入一条
{3}
END;";
for (int i = 0; i < jb.Count; i++)
{
sb.AppendFormat(dsql, v, jb[i].id.ToString(),
DBSqlHelper.GetUpdateSQL <EHECD_PrivilegeDTO>(
new EHECD_PrivilegeDTO {
bIsDeleted = false
},
string.Format(
@"WHERE
sPrivilegeMaster = 'role'--分配给角色的
AND sPrivilegeMasterValue = '{0}'--角色ID
AND sPrivilegeAccess = 'button'--特权类型是按钮
AND sPrivilegeAccessValue = '{1}'--按钮ID
AND sBelong = 'menu'--属于菜单的
AND sBelongValue = '{2}'--菜单ID",
v, jb[i].id.ToString(), jb[i].menuid.ToString()
)
),
DBSqlHelper.GetInsertSQL <EHECD_PrivilegeDTO>(
new EHECD_PrivilegeDTO
{
bIsDeleted = false,
bPrivilegeOperation = false,
ID = GuidHelper.CreateRpcrt4Guid(),
sBelong = "menu",
sBelongValue = Guid.Parse(jb[i].menuid.ToString()),
sPrivilegeAccess = "button",
sPrivilegeAccessValue = Guid.Parse(jb[i].id.ToString()),
sPrivilegeMaster = "role",
sPrivilegeMasterValue = Guid.Parse(v)
}
), jb[i].menuid.ToString()
);
}
return(sb.ToString());
}
/// <summary>
/// 创建分配角色菜单的sql
/// </summary>
/// <param name="jm">要分配的菜单集合</param>
/// <param name="roleId">要分配的角色</param>
/// <returns>sql</returns>
private string CreateDistributionMenuSql(List <string> jm, string roleId)
{
StringBuilder sb = new StringBuilder();
//赋予角色菜单权限的sql
string dsql =
@"IF EXISTS (
SELECT
1
FROM
EHECD_Privilege
WHERE
sPrivilegeMaster = 'role'
AND sBelong = 'role'
AND sPrivilegeMasterValue = '{0}'--角色ID
AND sBelongValue = '{0}'--角色ID
AND sPrivilegeAccess = 'menu'
AND sPrivilegeAccessValue = '{1}'--菜单ID
)
BEGIN
--存在直接更新
{2}
END
ELSE
BEGIN
--不存在就插入一条
{3}
END;";
for (int i = 0; i < jm.Count; i++)
{
sb.AppendFormat(dsql, roleId, jm[i],
DBSqlHelper.GetUpdateSQL <EHECD_PrivilegeDTO>(
new EHECD_PrivilegeDTO {
bIsDeleted = false
},
string.Format(
"WHERE sPrivilegeMaster = 'role' AND sBelong = 'role' AND sPrivilegeMasterValue = '{0}' AND sBelongValue = '{0}' AND sPrivilegeAccess = 'menu' AND sPrivilegeAccessValue = '{1}'",
roleId, jm[i]
)
),
DBSqlHelper.GetInsertSQL <EHECD_PrivilegeDTO>(
new EHECD_PrivilegeDTO
{
bIsDeleted = false,
bPrivilegeOperation = false,
ID = GuidHelper.CreateRpcrt4Guid(),
sBelong = "role",
sBelongValue = Guid.Parse(roleId),
sPrivilegeAccess = "menu",
sPrivilegeAccessValue = Guid.Parse(jm[i]),
sPrivilegeMaster = "role",
sPrivilegeMasterValue = Guid.Parse(roleId)
}
)
);
}
return(sb.ToString());
}
//分配角色
public override int DistributionRole(EHECD_SystemUserDTO user, dynamic p)
{
var ret = 0;
if (p.data.ids != null)
{
string idsStr = p.data.ids.Value.ToString();
if (!string.IsNullOrWhiteSpace(idsStr))
{
//1.有角色id，表示要给他重新分配角色
var ids = idsStr.Split(new char[] { ',' });
StringBuilder sb = new StringBuilder();
//解除未选中的其他角色
sb.AppendFormat("UPDATE EHECD_SystemUser_R_Role SET bIsDeleted = 1 WHERE sRoleID NOT IN ({0}) AND sUserID = '{1}';",
string.Join(",", ids.Select(m => "'" + m + "'")), user.ID);
string sql = @"IF EXISTS(SELECT 1 FROM EHECD_SystemUser_R_Role WHERE sRoleID = '{2}' AND sUserID = '{3}')
BEGIN
{0}
END
ELSE
BEGIN
{1}
END;";
for (int i = 0; i < ids.Length; i++)
{
sb.AppendFormat(sql,
DBSqlHelper.GetUpdateSQL <EHECD_SystemUser_R_RoleDTO>(
new EHECD_SystemUser_R_RoleDTO {
bIsDeleted = false
},
string.Format("WHERE sRoleID = '{0}' AND sUserID = '{1}'", ids[i], user.ID)),
DBSqlHelper.GetInsertSQL <EHECD_SystemUser_R_RoleDTO>(
new EHECD_SystemUser_R_RoleDTO
{
ID = GuidHelper.GetSecuentialGuid(),
bIsDeleted = false,
sRoleID = Guid.Parse(ids[i]),
sUserID = user.ID
}),
ids[i],
user.ID
);
}
//2.执行插入和更新
ret = excute.ExcuteTransaction(sb.ToString());
//3.记录系统日志
InsertSystemLog(
p.sLoginName.ToString(),
p.sUserName.ToString(),
p.IP.ToString(),
(Int16)(SYSTEM_LOG_TYPE.MODIFY | SYSTEM_LOG_TYPE.ADD | SYSTEM_LOG_TYPE.ROLE | SYSTEM_LOG_TYPE.SYSTEMUSER),
"分配系统用户角色" + user.ID,
user.ID.ToString(),
ret > 0);
}
else
{
//1.没有角色id，表示这个用户没有分配角色，解除他以前的所有角色信息
ret = excute.UpdateSingle <EHECD_SystemUser_R_RoleDTO>(
new EHECD_SystemUser_R_RoleDTO {
bIsDeleted = true
},
string.Format("WHERE sUserID = '{0}'", user.ID));
//2.记录系统日志
InsertSystemLog(
p.sLoginName.ToString(),
p.sUserName.ToString(),
p.IP.ToString(),
(Int16)(SYSTEM_LOG_TYPE.MODIFY | SYSTEM_LOG_TYPE.DELETE | SYSTEM_LOG_TYPE.ROLE | SYSTEM_LOG_TYPE.SYSTEMUSER),
"解除系统用户角色" + user.ID,
user.ID.ToString(),
ret >= 0);
}
}
return(ret);
}
