internal static void Execute(string query, T t)
{
using (SqlConnection dbConnection = ConnectionDb.OpenConnection())
{
dbConnection.Execute(query, t);
}
}
internal static List <T> Execute(string query)
{
using (SqlConnection connection = ConnectionDb.OpenConnection())
{
return(connection.Query <T>(query).ToList());
}
}
/// <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);
}
public static ConnectionDb getInstance()
{
if (instance == null)
{
instance = new ConnectionDb();
}
return(instance);
}
public void Release()
{
if ((TransactionDb != null) || !IsOpened)
{
return;
}
ConnectionDb.Close();
IsOpened = false;
}
public void Open()
{
if (IsOpened)
{
return;
}
ConnectionDb.Open();
IsOpened = true;
}
public PostgreTransaction BeginTransaction()
{
Open();
TransactionDb = new PostgreTransaction(ConnectionDb.BeginTransaction());
TransactionDb.Disposed += TransactionDb_Disposed;
TransactionDb.Commited += TransactionDb_Commited;
TransactionDb.Rollbacked += TransactionDb_Rollbacked;
return(TransactionDb);
}
/// <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;
}));
}
protected ConnectionDb GetConnection()
{
if (_connDb == null)
{
lock (_lock)
{
if (_connDb == null)
{
_connDb = new ConnectionDb();
}
}
}
return(_connDb);
}
private ConnectionDb GetConnectionDb()
{
if (_connDb == null)
{
lock (_lock)
{
if (_connDb == null)
{
_connDb = new ConnectionDb();
}
}
}
return(_connDb);
}
/// <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();
}
}
}
public void nonQuery(string Password, string Nonquery)
{
try
{
if (Password.Equals("123"))
{
ConnectionDb.ExecuteNonQuery(Nonquery);
MessageBox.Show("La Accion se realizo correctamente!");
}
else
{
throw new AccessException("La contraseña que ingreso no es correcta!");
}
}
catch (AccessException ex)
{
MessageBox.Show(ex.Message);
}
}
public JsonResult LoadBook()
{
try
{
using (ConnectionDb cd = new ConnectionDb())
{
var draw = Request.Form.GetValues("draw").FirstOrDefault();
var start = Request.Form.GetValues("start").FirstOrDefault();
var length = Request.Form.GetValues("length").FirstOrDefault();
var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
var searchValue = Request.Form.GetValues("search[value]").FirstOrDefault();
int pageSize = length != null?Convert.ToInt32(length) : 0;
int skip = start != null?Convert.ToInt32(start) : 0;
int recordTotal = 0;
var bookData = (from b in db.Book select b);
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
bookData = bookData.OrderBy(sortColumn + " " + sortColumnDir);
}
if (!string.IsNullOrEmpty(searchValue))
{
bookData = bookData.Where(m => m.BookName == searchValue);
}
recordTotal = bookData.Count();
var data = bookData.Skip(skip).Take(pageSize).ToList();
return(Json(new { draw = draw, recordsFiltered = recordTotal, recordsTotal = recordTotal, data = data }));
}
}
catch (Exception)
{
throw;
}
}
public async Task <List <AmortizationScheduleEntiti> > GetAllAmortizationScheduleAsync()
{
var response = await ConnectionDb.QueryAsync <AmortizationScheduleEntiti>(sql : QueriesResources.GetAmortizationSchedule, commandType : CommandType.Text);
return(response.ToList());
}
public async Task <AmortizationScheduleEntiti> GetAmortizationScheduleAsync(uint amortizationScheduleId)
{
return(await ConnectionDb.QueryFirstOrDefaultAsync <AmortizationScheduleEntiti>(QueriesResources.GetAmortizationScheduleById, new { AmortizationScheduleId = (int)amortizationScheduleId }, commandType : CommandType.Text));
}
public void Dispose()
{
ConnectionDb?.Dispose();
TransactionDb?.Dispose();
}
public DataTable query(string query)
{
return(ConnectionDb.ExecuteQuery(query));
}
/// <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);
}
