public static async Task <Response> IsHavePower(IsPowerIng ResultMsg, int Id)
{
Response response = new Response
{
code = Convert.ToInt32(StatusEnum.Failed)
};
response.msg = "暂无权限";
try
{
var list = await SqlDapperHelper.ReturnListTAsync <Role>("select SR.Id,SR.AdminId,SR.PowerSetId,SR.CreateTime,SP.ActionName,SP.ControllerNmae,SP.PowerName,SP.MenuId from Sys_Role SR join Sys_PowerSet SP on SR.PowerSetId=SP.Id where SR.AdminId=@AdminId", new { AdminId = Id });
var isHave = list.Any(x => x.ControllerNmae == ResultMsg.ControllerName && x.ActionName == ResultMsg.ActionName);
if (isHave)
{
response.code = Convert.ToInt32(StatusEnum.Succeed);
response.msg = "正常";
}
else
{
response.code = Convert.ToInt32(StatusEnum.Error);
response.msg = "暂无权限";
}
}
catch (Exception ex)
{
LogHelp.Error(ex);
}
return(response);
}
/// <summary>
/// 根据应用ID 查询分组
/// </summary>
/// <param name="secret_id"></param>
/// <param name="start"></param>
/// <param name="length"></param>
/// <returns></returns>
public static async Task <List <user_face_storage_group> > GetUser_Face_Storage_Groups_BySecretIdAsunc(string secret_id, int start, int length)
{
string where = " AND secret_id = @secret_id AND is_delete = @is_delete";
string limit = " limit @length offset @start ";
return(await SqlDapperHelper.ExecuteReaderRetListAsync <user_face_storage_group>(SelectSql + where + limit, new { secret_id, length, start, is_delete = false }));
}
/// <summary>
/// 修改用户金额
/// </summary>
/// <param name="user_Amount">日志实体类</param>
/// <param name="tra">事务</param>
/// <returns></returns>
public static async Task <long> MinusUserAmount_By_User_id(user_amount_detail user_Amount, IDbTransaction tra = null)
{
string set = "use_amount = use_amount+(@change_amount) , updated_at = @updated_at";
string where = " AND id = @user_id";
return(await SqlDapperHelper.ExecuteSqlIntAsync(string.Format(Update + where, set), new { user_Amount.user_id, user_Amount.updated_at, user_Amount.change_amount }, tra));
}
/// <summary>
/// 根据应用，分组，用户查询
/// </summary>
/// <param name="user_id"></param>
/// <param name="group_id"></param>
/// <param name="secret_id"></param>
/// <returns></returns>
public static async Task <List <user_face_storage> > GetUserList_By_UGS(string user_id, List <long> group_ids, string secret_id)
{
string where = @" AND api_user_id = @user_id AND api_group_id = ANY(@group_ids) AND secret_id = @secret_id AND is_delete=@is_delete";
return(await SqlDapperHelper.ExecuteReaderRetListAsync <user_face_storage>(Select + where,
new { user_id, group_ids, secret_id, is_delete = false }));
}
public int Insert(SqlDapperHelper db)
{
if (string.IsNullOrWhiteSpace(TITLE))
{
throw new Exception("제목은 빈값일 수 없습니다");
}
string sql = @"
INSERT INTO dbo.T_BOARD (
SEQ
,TITLE
,CONTENTS
,REG_U_ID
,REG_NAME
,REG_DATE
,STATUS
)
SELECT
ISNULL((SELECT MAX(SEQ)+1 FROM dbo.T_BOARD), 1)
,@TITLE
,@CONTENTS
,@REG_U_ID
,@REG_NAME
,@REG_DATE
,@STATUS
";
return(db.Execute(sql, this));
}
/// <summary>
/// 根据 应用ID 和 分组ID 查询用户
/// </summary>
/// <param name="secret_id"></param>
/// <param name="group_id"></param>
/// <param name="start"></param>
/// <param name="length"></param>
/// <returns></returns>
public static async Task <List <user_face_storage> > GetUser_Face_Storage_By_UGS_Asunc_All(string secret_id, List <long> group_ids)
{
string sql = "SELECT PUBLIC.user_face_storage.api_user_id FROM PUBLIC.user_face_storage WHERE 1 = 1 ";
string where = " AND secret_id = @secret_id AND api_group_id = ANY(@group_ids) AND is_delete = @is_delete";
return(await SqlDapperHelper.ExecuteReaderRetListAsync <user_face_storage>(sql + where, new { secret_id, group_ids, is_delete = false }));
}
public ActionResult Index()
{
using (var db = new SqlDapperHelper())
{
return(View(BoardModel.GetList(db, "")));
}
}
public static List <BoardModel> GetList(SqlDapperHelper db, string search)
{
if (string.IsNullOrWhiteSpace(search) == false)
{
search = search.Trim();
}
string sql = @"
SELECT
A.SEQ
,A.TITLE
,A.CONTENTS
,A.REG_U_ID
,A.REG_NAME
,A.REG_DATE
,A.STATUS
,A.UPDATE_DATE
FROM
dbo.T_BOARD A
WHERE
A.TITLE LIKE @search + '%'
";
var list = db.Query <BoardModel>(sql, new { search = search });
return(list);
}
private void button1_Click(object sender, EventArgs e)
{
using (var db = new SqlDapperHelper())
{
dataGridView1.DataSource = new BindingList <BoardModel>(BoardModel.GetList(db, ""));
}
}
/// <summary>
/// 修改是否删除状态
/// </summary>
/// <param name="group"></param>
/// <param name="tra"></param>
/// <returns></returns>
public static async Task <long> Remove_storage_group(user_face_storage_group group, IDbTransaction tra = null)
{
string set = "is_delete = @is_delete , updated_at = @updated_at";
string where = "AND user_id = @user_id AND group_name = @group_name AND secret_id = @secret_id";
return(await SqlDapperHelper.ExecuteSqlIntAsync(string.Format(UpdateSql + where, set), group, tra));
}
/// <summary>
/// 修改预存数量
/// </summary>
/// <param name="detail"></param>
/// <param name="tra"></param>
/// <returns></returns>
public static async Task <long> MinusUsernumber_By_User_id(user_face_compare_prepayment_detail detail, IDbTransaction tra = null)
{
string set = " number = number+(@change_number),updated_at=@updated_at";
string where = " AND user_id = @user_id";
return(await SqlDapperHelper.ExecuteSqlIntAsync(string.Format(Update + where, set), new { detail.change_number, detail.user_id, detail.updated_at }, tra));
}
private void button2_Click(object sender, EventArgs e)
{
var list = dataGridView1.DataSource as BindingList <BoardModel>;
using (var db = new SqlDapperHelper())
{
db.BeginTransaction();
try
{
foreach (var item in list)
{
switch (item.ChangedFlag)
{
case ChangedFlagEnum.Inserted:
item.Insert(db);
break;
case ChangedFlagEnum.Updated:
item.Update(db);
break;
}
}
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
MessageBox.Show(ex.Message);
}
}
}
/// <summary>
/// 根据应用，分组，用户查询
/// </summary>
/// <param name="user_id"></param>
/// <param name="secret_id"></param>
/// <returns></returns>
public static async Task <List <T> > GetStorage_By_UGS_ALL <T>(string user_id, string secret_id)
{
string sql = "SELECT * FROM PUBLIC.user_face_storage S left join PUBLIC.user_face_storage_group G on S.api_group_id=G.id WHERE 1 = 1";
string where = " AND S.api_user_id = @user_id AND S.secret_id = @secret_id AND S.is_delete=@is_delete";
return(await SqlDapperHelper.ExecuteReaderRetListAsync <T>(sql + where,
new { user_id, secret_id, is_delete = false }));
}
/// <summary>
/// 根据 应用ID 和 分组ID 查询用户
/// </summary>
/// <param name="secret_id"></param>
/// <param name="group_id"></param>
/// <param name="start"></param>
/// <param name="length"></param>
/// <returns></returns>
public static async Task <List <user_face_storage> > GetUser_Face_Storage_By_UGS_Asunc(string secret_id, long group_id, int start, int length)
{
string sql = "SELECT PUBLIC.user_face_storage.api_user_id FROM PUBLIC.user_face_storage WHERE 1 = 1 ";
string where = " AND secret_id = @secret_id AND api_group_id = @group_id AND is_delete = @is_delete";
string limit = " limit @length offset @start ";
return(await SqlDapperHelper.ExecuteReaderRetListAsync <user_face_storage>(sql + where + limit, new { secret_id, group_id, length, start, is_delete = false }));
}
public int DeleteByIds(List <long> ids)
{
if (ids.Count == 0)
{
return(0);
}
string sql = "DELETE FROM [examtotalcount_without] WHERE [id] IN @ids";
return(SqlDapperHelper.Execute(sql, new { ids = ids }));
}
public T QueryById <T>(long id, string returnFields = null)
{
if (returnFields == null)
{
returnFields = "[id],[exam_id],[grade_class],[exam_subject],[exam_avg],[exam_stdevp],[exam_max],[exam_allpassnum],[exam_onepassnum],[exam_twopassnum],[exam_threepassnum],[exam_greatnum],[exam_passnum],[exam_allrank],[exam_onerank],[exam_tworank],[exam_threerank]";
}
string sql = "SELECT " + returnFields + " FROM [examtotalcount_without] WHERE [id] = @id";
return(SqlDapperHelper.Query <T>(sql, new { id = id }).FirstOrDefault());
}
public T QueryById <T>(long id, string returnFields = null)
{
if (returnFields == null)
{
returnFields = "[id],[school_id],[schoolyear_nowyear],[schoolyear_mester],[grade_section],[exam_name],[exam_time],[exam_level]";
}
string sql = "SELECT " + returnFields + " FROM [exam] WHERE [id] = @id";
return(SqlDapperHelper.Query <T>(sql, new { id = id }).FirstOrDefault());
}
public int DeleteByIds(List <int> ids)
{
if (ids.Count == 0)
{
return(0);
}
string sql = "DELETE FROM [admins] WHERE EXISTS(SELECT 1 FROM @table as t WHERE t.id=[admins].[id])";
return(SqlDapperHelper.Execute(sql, new { table = ids.AsTableValuedParameter("type_int") }));
}
/// <summary>
/// 根据应用，分组，用户查询
/// </summary>
/// <param name="user_id"></param>
/// <param name="group_id"></param>
/// <param name="secret_id"></param>
/// <returns></returns>
public static async Task <user_face_storage> GetUser_By_UGS(string user_id, long group_id, string face_token, string secret_id)
{
string where = @" AND api_user_id = @user_id
AND api_group_id = @group_id
AND face_token = @face_token
AND secret_id = @secret_id
AND is_delete = @is_delete";
return(await SqlDapperHelper.ExecuteReaderRetTAsync <user_face_storage>(Select + where,
new { user_id, group_id, secret_id, face_token, is_delete = false }));
}
public T QueryById <T>(int id, string returnFields = null)
{
if (returnFields == null)
{
returnFields = "[id],[username],[pwd],[sex]";
}
string sql = "SELECT " + returnFields + " FROM [admins] WHERE [id] = @id";
return(SqlDapperHelper.Query <T>(sql, new { id = id }).FirstOrDefault());
}
/// <summary>
/// 获取人脸坐标(单个文件)
/// </summary>
/// <param name="files"></param>
/// <returns></returns>
public async Task <ActionResult> GetFaceLocation(IFormFile files)
{
var date = SqlDapperHelper.ExecuteReaderReturnList <object>("SELECT * FROM \"user\"");
var rectangle = await GetRectanglesAsync(new List <IFormFile>() { files });
if (rectangle.Count <= 0)
{
return(Ok(null));
}
return(Ok(rectangle[0]));
}
/// <summary>
/// 从所有组中删除用户
/// </summary>
/// <param name="user"></param>
/// <param name="tra"></param>
/// <returns></returns>
public static async Task <long> Remove_Storage_By_ApiUserID_All(user_face_storage user, IDbTransaction tra = null)
{
string set = "is_delete = @is_delete , updated_at = @updated_at";
string where = " AND secret_id = @secret_id";
return(await SqlDapperHelper.ExecuteSqlIntAsync(string.Format(Update + where, set),
new
{
is_delete = user.is_delete,
updated_at = user.updated_at,
secret_id = user.secret_id
}, tra));
}
public JsonResult Login(Login login)
{
Response <object> result = new Response <object>()
{
code = Convert.ToInt32(Status.Failed)
};
var count = SqlDapperHelper.ReturnT <int>("select count(1) from [User] where Phone=@Phone and Password=@Password", login);
if (count > 0)
{
result.code = Convert.ToInt32(Status.Succeed);
result.msg = "登录成功";
Login tokens = SqlDapperHelper.ReturnT <Login>("select * from [User] where Phone=@Phone and Password=@Password", login);
try
{
//存redis
RedisManager.redisHelp.SetValue("Login", JsonConvert.SerializeObject(tokens));
}
catch (Exception ex)
{
LogHelp.Error(ex);
}
try
{
//登录加密保存信息
//var token = AESEncrypt(JsonConvert.SerializeObject(tokens));
// Convert.ToBase64String(Encode(Encoding.UTF8.GetBytes(password)));
var token = Convert.ToBase64String(AESEncrypt(JsonConvert.SerializeObject(tokens)));
//SignIn(tokens, true).Wait();
//登录成功，信息加入MQ
PushMQ.SendMQ(tokens, Key.PushMQUserKey);
}
catch (Exception ex)
{
LogHelp.Error(ex);
}
return(Json(new { result }));
}
//测试日志
LogHelp.Debug("登录失败：账号" + login.Phone + " 密码：" + login.Password);
result.msg = "登录失败";
return(Json(new { result }));
}
public int UpdateAll(examTable entity, string updateFields = null)
{
if (updateFields == null)
{
updateFields = "[school_id]=@school_id,[schoolyear_nowyear]=@schoolyear_nowyear,[schoolyear_mester]=@schoolyear_mester,[grade_section]=@grade_section,[exam_name]=@exam_name,[exam_time]=@exam_time,[exam_level]=@exam_level";
}
else
{
updateFields = StringHelper.SqlUpdateFields(updateFields);
}
string sql = "UPDATE [exam] SET " + updateFields;
return(SqlDapperHelper.Execute(sql, entity));
}
public int UpdateAll(adminsTable entity, string updateFields = null)
{
if (updateFields == null)
{
updateFields = "[username]=@username,[pwd]=@pwd,[sex]=@sex";
}
else
{
updateFields = StringHelper.SqlUpdateFields(updateFields);
}
string sql = "UPDATE [admins] SET " + updateFields;
return(SqlDapperHelper.Execute(sql, entity));
}
public int UpdateAll(examtotalcount_withoutTable entity, string updateFields = null)
{
if (updateFields == null)
{
updateFields = "[exam_id]=@exam_id,[grade_class]=@grade_class,[exam_subject]=@exam_subject,[exam_avg]=@exam_avg,[exam_stdevp]=@exam_stdevp,[exam_max]=@exam_max,[exam_allpassnum]=@exam_allpassnum,[exam_onepassnum]=@exam_onepassnum,[exam_twopassnum]=@exam_twopassnum,[exam_threepassnum]=@exam_threepassnum,[exam_greatnum]=@exam_greatnum,[exam_passnum]=@exam_passnum,[exam_allrank]=@exam_allrank,[exam_onerank]=@exam_onerank,[exam_tworank]=@exam_tworank,[exam_threerank]=@exam_threerank";
}
else
{
updateFields = StringHelper.SqlUpdateFields(updateFields);
}
string sql = "UPDATE [examtotalcount_without] SET " + updateFields;
return(SqlDapperHelper.Execute(sql, entity));
}
/// <summary>
/// 根据用户组将用户的状态变为删除
/// </summary>
/// <param name="user"></param>
/// <param name="tra"></param>
/// <returns></returns> Remove_Storage_By_Group
public static async Task <long> Remove_Storage_By_Group(user_face_storage_group group, IDbTransaction tra = null)
{
string set = "is_delete = @is_delete , updated_at = @updated_at";
string where = " AND api_group_id = @api_group_id AND secret_id = @secret_id";
return(await SqlDapperHelper.ExecuteSqlIntAsync(string.Format(Update + where, set),
new
{
is_delete = group.is_delete,
updated_at = group.updated_at,
api_group_id = group.id,
secret_id = group.secret_id
}, tra));
}
/// <summary>
/// 删除用户组
/// </summary>
/// <returns></returns>
public async Task<ActionResult> RemoveGroup(FaceDatabaseModel.GroupModel model)
{
RequestFaceModel request = new RequestFaceModel()
{
Status = 500,
Message = null,
FaceList = null
};
// 基本验证
var userFaceSet = Verify(ref request, model);
if (userFaceSet == null)
{
return Ok(request);
}
// 是否重复验证
var group = await Service_user_face_storage_group.Get_storage_group_BySecretGroupAsync(model.secret_id, model.group_id);
if (group == null)
{
request.Enum = RequestEnum.数据不存在;
return Ok(request);
}
group.is_delete = true;//是否移除
group.updated_at = DateTime.Now;
try
{
using (var tra = SqlDapperHelper.GetOpenConnection().BeginTransaction())
{
// 修改用户的删除状态
await Service_user_face_storage.Remove_Storage_By_Group(group, tra);
// 修改用户组的删除状态
await Service_user_face_storage_group.Remove_storage_group(group, tra);
tra.Commit();
request.Enum = RequestEnum.Succeed;
}
}
catch (Exception ex)
{
LogHelperNLog.Error(ex, "操作日志添加失败");
request.Enum = RequestEnum.操作日志添加失败;
}
return Ok(request);
}
public int UpdateFieldsByWhere(examtotalcount_withoutTable entity, string whereFields, string updateFields = null)
{
if (updateFields == null)
{
updateFields = "[exam_id]=@exam_id,[grade_class]=@grade_class,[exam_subject]=@exam_subject,[exam_avg]=@exam_avg,[exam_stdevp]=@exam_stdevp,[exam_max]=@exam_max,[exam_allpassnum]=@exam_allpassnum,[exam_onepassnum]=@exam_onepassnum,[exam_twopassnum]=@exam_twopassnum,[exam_threepassnum]=@exam_threepassnum,[exam_greatnum]=@exam_greatnum,[exam_passnum]=@exam_passnum,[exam_allrank]=@exam_allrank,[exam_onerank]=@exam_onerank,[exam_tworank]=@exam_tworank,[exam_threerank]=@exam_threerank";
}
else
{
updateFields = StringHelper.SqlUpdateFields(updateFields);
}
string where = "WHERE " + StringHelper.SqlWhereFields(whereFields);
string sql = string.Format("UPDATE [examtotalcount_without] SET {0} " + where, updateFields);
return(SqlDapperHelper.Execute(sql, entity));
}
public int UpdateFieldsByWhere(adminsTable entity, string whereFields, string updateFields = null)
{
if (updateFields == null)
{
updateFields = "[username]=@username,[pwd]=@pwd,[sex]=@sex";
}
else
{
updateFields = StringHelper.SqlUpdateFields(updateFields);
}
string where = "WHERE " + StringHelper.SqlWhereFields(whereFields);
string sql = string.Format("UPDATE [admins] SET {0} " + where, updateFields);
return(SqlDapperHelper.Execute(sql, entity));
}
