264 lines
13 KiB
C#
264 lines
13 KiB
C#
|
using Dapper;
|
|||
|
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Threading.Tasks;
|
|||
|
|
using Traffic.Data.Models;
|
|||
|
|
using Traffic.Repository.Interfaces;
|
|||
|
|
|
|||
|
|
namespace Traffic.Repository.Implements
|
|||
|
|
{
|
|||
|
|
public class AccountRepository : IAccountRepository
|
|||
|
|
{
|
|||
|
|
public IDbTransaction Transaction { get; }
|
|||
|
|
public IDbConnection Connection => Transaction.Connection;
|
|||
|
|
|
|||
|
|
public AccountRepository(IDbTransaction transaction)
|
|||
|
|
{
|
|||
|
|
Transaction = transaction;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public IEnumerable<AccountUser> GetAccountUsers()
|
|||
|
|
{
|
|||
|
|
var sql = $@"SELECT * FROM {nameof(AccountUser)}";
|
|||
|
|
|
|||
|
|
return Connection.Query<AccountUser>(sql, null, Transaction);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public AccountUser GetAccountUserByAccount(string account)
|
|||
|
|
{
|
|||
|
|
var sql = $@"SELECT * FROM {nameof(AccountUser)}
|
|||
|
|
WHERE {nameof(AccountUser.Account)} = @{nameof(AccountUser.Account)}";
|
|||
|
|
|
|||
|
|
return Connection.QueryFirstOrDefault<AccountUser>(sql, new { Account = account }, Transaction);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public AccountUser GetAccountUserByAccount(string account, string password)
|
|||
|
|
{
|
|||
|
|
var sql = $@"SELECT * FROM {nameof(AccountUser)}
|
|||
|
|
WHERE {nameof(AccountUser.Account)} = @{nameof(AccountUser.Account)}
|
|||
|
|
AND {nameof(AccountUser.Password)} = @{nameof(AccountUser.Password)}";
|
|||
|
|
|
|||
|
|
return Connection.QueryFirstOrDefault<AccountUser>(sql, new { Account = account, Password = password }, Transaction);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public AccountUser GetAccountUserById(int id)
|
|||
|
|
{
|
|||
|
|
var sql = $@"SELECT * FROM {nameof(AccountUser)}
|
|||
|
|
WHERE {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";
|
|||
|
|
|
|||
|
|
return Connection.QueryFirstOrDefault<AccountUser>(sql, new { Id = id }, Transaction);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public IEnumerable<AccountRole> GetAccountRoles()
|
|||
|
|
{
|
|||
|
|
var sql = $@"SELECT * FROM {nameof(AccountRole)}";
|
|||
|
|
|
|||
|
|
return Connection.Query<AccountRole>(sql, null, Transaction);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public IEnumerable<AccountUserSite> GetAccountUserSite(int id)
|
|||
|
|
{
|
|||
|
|
var sql = $@"SELECT * FROM {nameof(AccountUserSite)}
|
|||
|
|
WHERE {nameof(AccountUserSite.UserId)} = @{nameof(AccountUserSite.UserId)}";
|
|||
|
|
|
|||
|
|
return Connection.Query<AccountUserSite>(sql, new { UserId = id }, Transaction);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public IEnumerable<AccountUserSite> GetAccountUserSites()
|
|||
|
|
{
|
|||
|
|
var sql = $@"SELECT * FROM {nameof(AccountUserSite)}";
|
|||
|
|
|
|||
|
|
return Connection.Query<AccountUserSite>(sql, null, Transaction);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public int InsertAccountUser(AccountUser accountUser)
|
|||
|
|
{
|
|||
|
|
var sql = $@"INSERT INTO {nameof(AccountUser)} (
|
|||
|
|
{nameof(AccountUser.Account)},
|
|||
|
|
{nameof(AccountUser.Password)},
|
|||
|
|
{nameof(AccountUser.UserName)},
|
|||
|
|
{nameof(AccountUser.PhoneNumber)},
|
|||
|
|
{nameof(AccountUser.Email)},
|
|||
|
|
{nameof(AccountUser.RoleCodeId)},
|
|||
|
|
{nameof(AccountUser.PoliceStationId)},
|
|||
|
|
{nameof(AccountUser.CreatorId)},
|
|||
|
|
{nameof(AccountUser.CreatedOn)},
|
|||
|
|
{nameof(AccountUser.ChangePwdOn)})
|
|||
|
|
VALUES (@{nameof(AccountUser.Account)},
|
|||
|
|
@{nameof(AccountUser.Password)},
|
|||
|
|
@{nameof(AccountUser.UserName)},
|
|||
|
|
@{nameof(AccountUser.PhoneNumber)},
|
|||
|
|
@{nameof(AccountUser.Email)},
|
|||
|
|
@{nameof(AccountUser.RoleCodeId)},
|
|||
|
|
@{nameof(AccountUser.PoliceStationId)},
|
|||
|
|
@{nameof(AccountUser.CreatorId)},
|
|||
|
|
@{nameof(AccountUser.CreatedOn)},
|
|||
|
|
@{nameof(AccountUser.ChangePwdOn)});
|
|||
|
|
SELECT LAST_INSERT_ID() AS `User_id`;";
|
|||
|
|
return Connection.QuerySingle<int>(sql, accountUser, Transaction);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public int InsertAccountRole(AccountRole accountUser)
|
|||
|
|
{
|
|||
|
|
var sql = $@"INSERT INTO {nameof(AccountRole)} (
|
|||
|
|
{nameof(AccountRole.RoleName)},
|
|||
|
|
{nameof(AccountRole.CreatorId)},
|
|||
|
|
{nameof(AccountRole.CreatedOn)})
|
|||
|
|
VALUES (@{nameof(AccountRole.RoleName)},
|
|||
|
|
@{nameof(AccountRole.CreatorId)},
|
|||
|
|
@{nameof(AccountRole.CreatedOn)});
|
|||
|
|
SELECT LAST_INSERT_ID() AS `User_id`;";
|
|||
|
|
return Connection.QuerySingle<int>(sql, accountUser, Transaction);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool InsertAccountUserSite(List<AccountUserSite> userSite)
|
|||
|
|
{
|
|||
|
|
var sql = $@"INSERT INTO {nameof(AccountUserSite)} (
|
|||
|
|
{nameof(AccountUserSite.UserId)},
|
|||
|
|
{nameof(AccountUserSite.SiteId)})
|
|||
|
|
VALUES (@{nameof(AccountUserSite.UserId)},
|
|||
|
|
@{nameof(AccountUserSite.SiteId)})";
|
|||
|
|
|
|||
|
|
var result = Connection.Execute(sql, userSite, Transaction);
|
|||
|
|
return result >= 1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool UpdateAccountUserbyAdmin(AccountUser accountUser)
|
|||
|
|
{
|
|||
|
|
var sql = $@"UPDATE {nameof(AccountUser)}
|
|||
|
|
SET {nameof(AccountUser.Account)}=@{nameof(AccountUser.Account)},
|
|||
|
|
{nameof(AccountUser.UserName)}=@{nameof(AccountUser.UserName)},
|
|||
|
|
{nameof(AccountUser.PhoneNumber)}=@{nameof(AccountUser.PhoneNumber)},
|
|||
|
|
{nameof(AccountUser.Email)}=@{nameof(AccountUser.Email)},
|
|||
|
|
{nameof(AccountUser.RoleCodeId)}=@{nameof(AccountUser.RoleCodeId)},
|
|||
|
|
{nameof(AccountUser.PoliceStationId)}=@{nameof(AccountUser.PoliceStationId)},
|
|||
|
|
{nameof(AccountUser.UpdatorId)}=@{nameof(AccountUser.UpdatorId)},
|
|||
|
|
{nameof(AccountUser.UpdatedOn)}=@{nameof(AccountUser.UpdatedOn)}
|
|||
|
|
WHERE {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";
|
|||
|
|
|
|||
|
|
var result = Connection.Execute(sql, accountUser, Transaction);
|
|||
|
|
return result == 1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool UpdateAccountUserPasswordByAdmin(int id, string newPassword, int byWho, DateTime updateOn)
|
|||
|
|
{
|
|||
|
|
var sql = $@"UPDATE {nameof(AccountUser)}
|
|||
|
|
SET {nameof(AccountUser.Password)}=@newPassword,
|
|||
|
|
{nameof(AccountUser.UpdatorId)}=@{nameof(AccountUser.UpdatorId)},
|
|||
|
|
{nameof(AccountUser.UpdatedOn)}=@{nameof(AccountUser.UpdatedOn)},
|
|||
|
|
{nameof(AccountUser.ChangePwdOn)}=@{nameof(AccountUser.UpdatedOn)},
|
|||
|
|
{nameof(AccountUser.ErrorCount)}=0
|
|||
|
|
WHERE {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";
|
|||
|
|
|
|||
|
|
var result = Connection.Execute(sql, new { Id = id, newPassword, UpdatorId = byWho, UpdatedOn = updateOn }, Transaction);
|
|||
|
|
return result == 1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool UpdateAccountUserbyUser(AccountUser accountUser)
|
|||
|
|
{
|
|||
|
|
var sql = $@"UPDATE {nameof(AccountUser)}
|
|||
|
|
SET {nameof(AccountUser.UserName)}=@{nameof(AccountUser.UserName)},
|
|||
|
|
{nameof(AccountUser.PhoneNumber)}=@{nameof(AccountUser.PhoneNumber)},
|
|||
|
|
{nameof(AccountUser.Email)}=@{nameof(AccountUser.Email)},
|
|||
|
|
{nameof(AccountUser.UpdatorId)}=@{nameof(AccountUser.UpdatorId)},
|
|||
|
|
{nameof(AccountUser.UpdatedOn)}=@{nameof(AccountUser.UpdatedOn)}
|
|||
|
|
WHERE {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";
|
|||
|
|
|
|||
|
|
var result = Connection.Execute(sql, accountUser, Transaction);
|
|||
|
|
return result == 1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool UpdateAccountUserPasswordByUser(int id, string oldPassword, string newPassword, DateTime updatedOn)
|
|||
|
|
{
|
|||
|
|
var sql = $@"UPDATE {nameof(AccountUser)}
|
|||
|
|
SET {nameof(AccountUser.Password)}=@newPassword,
|
|||
|
|
{nameof(AccountUser.UpdatorId)}=@{nameof(AccountUser.UpdatorId)},
|
|||
|
|
{nameof(AccountUser.UpdatedOn)}=@{nameof(AccountUser.UpdatedOn)},
|
|||
|
|
{nameof(AccountUser.ChangePwdOn)}=@{nameof(AccountUser.UpdatedOn)},
|
|||
|
|
{nameof(AccountUser.ErrorCount)}=0
|
|||
|
|
WHERE {nameof(AccountUser.Password)} = @oldPassword And {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";
|
|||
|
|
|
|||
|
|
var result = Connection.Execute(sql, new { Id = id, oldPassword, newPassword, UpdatorId = id, UpdatedOn = updatedOn }, Transaction);
|
|||
|
|
return result == 1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool InsertUserLoginLog(UserLoginLog userLogin)
|
|||
|
|
{
|
|||
|
|
var sql = $@"INSERT INTO {nameof(UserLoginLog)} (
|
|||
|
|
{nameof(UserLoginLog.UserId)},
|
|||
|
|
{nameof(UserLoginLog.LoginIP)},
|
|||
|
|
{nameof(UserLoginLog.LoginOn)},
|
|||
|
|
{nameof(UserLoginLog.Status)})
|
|||
|
|
VALUES (@{nameof(UserLoginLog.UserId)},
|
|||
|
|
@{nameof(UserLoginLog.LoginIP)},
|
|||
|
|
@{nameof(UserLoginLog.LoginOn)},
|
|||
|
|
@{nameof(UserLoginLog.Status)})";
|
|||
|
|
|
|||
|
|
var result = Connection.Execute(sql, userLogin, Transaction);
|
|||
|
|
return result == 1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool UpdateUserErrorCount(int id, int errorCount)
|
|||
|
|
{
|
|||
|
|
var sql = $@"UPDATE {nameof(AccountUser)}
|
|||
|
|
SET {nameof(AccountUser.ErrorCount)}=@{nameof(AccountUser.ErrorCount)}
|
|||
|
|
WHERE {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";
|
|||
|
|
|
|||
|
|
var result = Connection.Execute(sql, new { id, errorCount }, Transaction);
|
|||
|
|
return result == 1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool DisableUser(int byWho, int id, int status)
|
|||
|
|
{
|
|||
|
|
var sql = $@"UPDATE {nameof(AccountUser)}
|
|||
|
|
SET {nameof(AccountUser.Status)} = @{nameof(status)},
|
|||
|
|
{nameof(AccountUser.UpdatorId)} = @{nameof(byWho)},
|
|||
|
|
{nameof(AccountUser.UpdatedOn)} = NOW()
|
|||
|
|
WHERE {nameof(AccountUser.Id)} = @{nameof(id)}";
|
|||
|
|
|
|||
|
|
var result = Connection.Execute(sql, new { byWho, id, status }, Transaction);
|
|||
|
|
return result == 1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool DeleteUser(int id)
|
|||
|
|
{
|
|||
|
|
var sql = $@"DELETE FROM {nameof(AccountUser)} WHERE {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";
|
|||
|
|
var result = Connection.Execute(sql, new { id }, Transaction);
|
|||
|
|
return result == 1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool DeleteUserSite(int userId)
|
|||
|
|
{
|
|||
|
|
var sql = $@"DELETE FROM {nameof(AccountUserSite)} WHERE {nameof(AccountUserSite.UserId)} = @{nameof(AccountUserSite.UserId)}";
|
|||
|
|
var result = Connection.Execute(sql, new { UserId = userId }, Transaction);
|
|||
|
|
return result >= 1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public IEnumerable<AccountPwd> GetAccountPwdTop3(string account)
|
|||
|
|
{
|
|||
|
|
var sql = $@"SELECT * FROM {nameof(AccountPwd)}
|
|||
|
|
WHERE {nameof(AccountPwd.Account)} = {nameof(account)}
|
|||
|
|
ORDER BY {nameof(AccountPwd.CreatedOn)} DESC
|
|||
|
|
LIMIT 3";
|
|||
|
|
|
|||
|
|
return Connection.Query<AccountPwd>(sql, new { account }, Transaction);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool InsertAccountPwd(AccountPwd accountUser)
|
|||
|
|
{
|
|||
|
|
var sql = $@"INSERT INTO {nameof(AccountPwd)} (
|
|||
|
|
{nameof(AccountPwd.Account)},
|
|||
|
|
{nameof(AccountPwd.Password)},
|
|||
|
|
{nameof(AccountPwd.CreatedOn)})
|
|||
|
|
VALUES (@{nameof(AccountPwd.Account)},
|
|||
|
|
@{nameof(AccountPwd.Password)},
|
|||
|
|
@{nameof(AccountPwd.CreatedOn)})";
|
|||
|
|
var result = Connection.Execute(sql, accountUser, Transaction);
|
|||
|
|
return result >= 1;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|