/// <summary>
/// Get User List with short info
/// </summary>
/// <returns></returns>
public async Task <List <UsersListViewModel> > GetUserListAsync()
{
return(await Task.Run(async() =>
{
List <UsersListViewModel> result = new List <UsersListViewModel>();
ConnectionDb conn = new ConnectionDb(_config);
using (IDbConnection sqlCon = conn.GetConnection)
{
string query = @"
select
u.id as UserId,
u.UserName,
u.Email as UserMail,
r.name as UserRole
from User u
join UserRole ur on u.id = ur.UserId
join Role r on r.id = ur.RoleId
";
ConnectionDb.OpenConnect(sqlCon);
var resultQuery = await sqlCon.QueryAsync <UsersListViewModel>(query);
result = resultQuery.ToList();
}
return result;
}));
}
/// <summary>
/// Get all reserved events on week by the date
/// </summary>
/// <param name="orgId"></param>
/// <param name="dateCal"></param>
/// <param name="currUser"></param>
/// <returns></returns>
public async Task <List <ReservItemsViewModel> > GetOrgReservByWeek(int orgId, DateTime dateCal, string currUser)
{
List <ReservItemsViewModel> result = new List <ReservItemsViewModel>();
if (orgId == 0 || dateCal == DateTime.MinValue)
{
return(result);
}
DateTime dateFrom = new DateTime(dateCal.Year, dateCal.Month, 1);
DateTime dateTo = dateFrom.AddMonths(1).AddDays(-1);
await Task.Run(async() =>
{
ConnectionDb conn = new ConnectionDb(_config);
using (IDbConnection sqlCon = conn.GetConnection)
{
string query = @"
select
cl.orgId,
ifnull(org.orgName,'') as orgName,
ifnull(fn.userName,ow.userName) as staffName,
case
when cl.userId = @currUser then 1
else 0
end as isMy,
cl.dateFrom,
cl.dateTo,
cl.isConfirm,
cl.comment
from orgReserve cl
left join org on org.orgId = cl.orgId
left join orgStaff st on st.orgStaffId = cl.orgStaffId
left join user fn on fn.id = st.staffId
left join user ow on ow.id = org.owner
where cl.orgId = @orgId
and dateFrom between @dateFrom and @dateTo
";
// Ensure that the connection state is Open
ConnectionDb.OpenConnect(sqlCon);
var resultQuery = await sqlCon.QueryAsync <ReservItemsViewModel>(
query,
new
{
orgId = orgId,
dateFrom = dateFrom,
dateTo = dateTo,
currUser = currUser
});
result = resultQuery.ToList();
}
});
return(result);
}
/// <summary>
/// Get User Info with Owner Role
/// User data, Organization data, Staff in organization data
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public async Task <EditOwnerViewModel> GetOwnerInfoAsync(User user)
{
if (user == null)
{
return(null);
}
return(await Task.Run(async() =>
{
EditOwnerViewModel result = new EditOwnerViewModel()
{
Id = user.Id,
Name = user.UserName,
Email = user.Email,
NewPassword = string.Empty,
OldPassword = string.Empty,
ConfirmNewPassword = string.Empty
};
result.Orgs = _dbOrg.GetOrgsByOwner(user.Id);
ConnectionDb conn = new ConnectionDb(_config);
using (IDbConnection sqlCon = conn.GetConnection)
{
string query = @"
select
u.UserName,
u.Email as emailUser,
s.orgStaffId,
s.isConfirm,
s.orgId,
s.staffId
from org o
join orgStaff s on s.orgId = o.orgId
join user u on u.id = s.staffId
where o.owner = @userId
";
// Ensure that the connection state is Open
ConnectionDb.OpenConnect(sqlCon);
var resultQuery = await sqlCon.QueryAsync <OwnerOrgStaffViewModel>(query, new { userId = user.Id });
result.Staff = resultQuery.ToList();
}
return result;
}));
}
/// <summary>
/// Confirm Reserved Event
/// </summary>
/// <param name="userId"></param>
/// <param name="resId"></param>
/// <param name="isAdmin"></param>
/// <returns></returns>
public async Task ConfirmResEvent(string userId, int resId, bool isAdmin)
{
if (string.IsNullOrEmpty(userId) || resId == 0)
{
return;
}
int resultQuery = 0;
await Task.Run(async() =>
{
ConnectionDb conn = new ConnectionDb(_config);
using (IDbConnection sqlCon = conn.GetConnection)
{
string query = @"
select
count(*) as kol
from orgreserve r
left join org on org.orgid = r.orgid
left join orgstaff s on s.orgid = r.orgid and s.orgstaffid = r.orgstaffid
where resid = 5
and (ifnull(org.owner,'') = @userId
or ifnull(s.staffid,'') = @userId)
";
// Ensure that the connection state is Open
ConnectionDb.OpenConnect(sqlCon);
resultQuery = await sqlCon.ExecuteScalarAsync <int>(
query, new { resid = resId, userId = userId }
);
}
});
if (resultQuery != 0 || isAdmin)
{
var reserveItem = await _dbContext.OrgReserve.Where(w => w.resId == resId).FirstOrDefaultAsync();
if (reserveItem != null)
{
reserveItem.isConfirm = true;
_dbContext.OrgReserve.Update(reserveItem);
await _dbContext.SaveChangesAsync();
}
}
}
/// <summary>
/// Get User calendar reserved items
/// </summary>
/// <param name="userId">user id</param>
/// <param name="dateCal">calendar date</param>
/// <returns></returns>
/// <exception cref="NotImplementedException"></exception>
public async Task <List <UserCalendarViewModel> > GetUserCalendar(string userId, DateTime dateCal)
{
List <UserCalendarViewModel> result = new List <UserCalendarViewModel>();
if (string.IsNullOrEmpty(userId) || dateCal == DateTime.MinValue)
{
return(result);
}
DateTime dateFrom = new DateTime(dateCal.Year, dateCal.Month, 1);
DateTime dateTo = dateFrom.AddMonths(1).AddDays(-1);
await Task.Run(async() =>
{
ConnectionDb conn = new ConnectionDb(_config);
using (IDbConnection sqlCon = conn.GetConnection)
{
string query = @"
select
cl.resId,
case
when ifnull(ow.id,'') = @userId then 1
else 0
end as isOwner,
cl.orgId,
ifnull(org.orgName,'') as orgName,
ifnull(fn.userName,ow.userName) as staffName,
cl.dateFrom,
cl.dateTo,
cl.isConfirm,
cl.comment
from orgReserve cl
left join org on org.orgId = cl.orgId
left join orgStaff st on st.orgStaffId = cl.orgStaffId
left join user fn on fn.id = st.staffId
left join user ow on ow.id = org.owner
where dateFrom between @dateFrom and @dateTo
and (
ifnull(cl.userId,'') = @userId
or ifnull(fn.id,'') = @userId
or ifnull(ow.id,'') = @userId
)
";
// Ensure that the connection state is Open
ConnectionDb.OpenConnect(sqlCon);
var resultQuery = await sqlCon.QueryAsync <UserCalendarViewModel>(
query,
new
{
dateFrom = dateFrom,
dateTo = dateTo,
userId = userId
});
result = resultQuery.ToList();
}
});
return(result);
}
