protected bool Update <T>(T obj) where T : class
{
using (var context = PODFactory.GetContext(true))
{
return(context.Database.Connection.Update <T>(obj));
}
}
public void Edit(Organization customer)
{
string adminUserName = GetByID <User>(customer.AdminUser.ID).UserName;
//admmin hasnt been changed
if (String.Compare(customer.AdminUser.UserName, adminUserName, true) == 0)
{
string sql = @"UPDATE ""Organization"" SET OrganizationName = @OrganizationName WHERE Id = @Id";
base.Execute(sql, new { OrganizationName = customer.OrganizationName, ID = customer.ID });
}
//admin user has been changed
else
{
try
{
DynamicParameters param = new DynamicParameters();
param.Add("@CustomerID", customer.ID, DbType.Guid, direction: ParameterDirection.Input);
param.Add("@CustomerName", customer.OrganizationName, DbType.String, direction: ParameterDirection.Input);
param.Add("@AdminID", customer.AdminUser.ID, DbType.Guid, direction: ParameterDirection.Input);
param.Add("@RoleID", customer.AdminUser.Role.ID, DbType.Guid, direction: ParameterDirection.Input);
using (var context = PODFactory.GetContext(true))
{
var multi = context.Database.Connection.QueryMultiple("[EditOrganization]", param, commandType: CommandType.StoredProcedure);
}
}
catch (Exception e)
{
throw;
}
}
}
protected int Execute <T>(string sql, T obj) where T : class
{
using (var context = PODFactory.GetContext(true))
{
return(context.Database.Connection.Execute(sql, obj));
}
}
protected IEnumerable <T> GetList <T>(object predicate) where T : class
{
using (var context = PODFactory.GetContext(true))
{
return(context.Database.Connection.GetList <T>(predicate));
}
}
public Organization GetOrganizationWithAdminUser(Guid ID)
{
try
{
var param = new DynamicParameters();
param.Add("@OrganizationID", ID, dbType: DbType.Guid, direction: ParameterDirection.Input);
using (var context = PODFactory.GetContext(true))
{
var multi = context.Database.Connection.QueryMultiple("[GetOrganizationByID]", param, commandType: CommandType.StoredProcedure);
Organization org = multi.Read <Organization>().FirstOrDefault();
if (org != null)
{
User user = multi.Read <User>().FirstOrDefault();
org.AdminUser = user;
}
return(org);
}
}
catch (Exception ex)
{
_loggingManager.Log.Error(ex, ex.Source);
throw;
}
}
protected Guid Insert <T>(T obj) where T : class
{
using (var context = PODFactory.GetContext(true))
{
return(context.Database.Connection.Insert <T>(obj));
}
}
public List <Organization> GetCustomers(Guid currentOrganizationID, string accountNumber, string accountName, string adminUser, string userName, string statusName, DateTime?lastLoginDate,
int currentPageNumber, int pageSize, string sortExpression, string sortDirection, out int totalRows)
{
totalRows = 0;
try
{
List <Organization> customers = new List <Organization>();
var offset = (currentPageNumber - 1) * pageSize;
var param = new DynamicParameters();
param.Add("@ParentOrganizationID", currentOrganizationID, dbType: DbType.Guid, direction: ParameterDirection.Input);
param.Add("@AccountNumber", accountNumber, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@AccountName", accountName, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@AdminUser", adminUser, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@UserName", userName, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@StatusName", statusName, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@LastLoginDate", lastLoginDate, dbType: DbType.DateTime, direction: ParameterDirection.Input);
param.Add("@Offset", offset, dbType: DbType.Int32, direction: ParameterDirection.Input);
param.Add("@PageSize", pageSize, dbType: DbType.Int32, direction: ParameterDirection.Input);
param.Add("@SortExpression", sortExpression, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@SortDirection", sortDirection, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
using (var context = PODFactory.GetContext(true))
{
var multi = context.Database.Connection.QueryMultiple("[GetOrganizations]", param, commandType: CommandType.StoredProcedure);
customers = multi.Read <Organization, User, Organization>((a, g) =>
{ a.AdminUser = g; return(a); }, splitOn: "UserName").ToList();
totalRows = param.Get <int>("RowCount");
}
if (customers == null || customers.Count == 0)
{
return(customers);
}
//foreach (Organization organization in customers)
//{
// var customerRole = customerRoles.FirstOrDefault(cr => cr.OrganizationID == organization.ID);
// if (customerRole != null)
// {
// organization.AdminUser = users.FirstOrDefault(u => u.ID == customerRole.UserID);
// };
//}
return(customers);
}
catch (Exception e)
{
throw;
}
}
//public Organization GetCustomerByID(Guid id)
//{
// Organization customer = null;
// try
// {
// // Basic DapperExtensions read
// // customer = get.<Organization>(new { ID = id });
// //if (res.Count() == 0)
// //{
// // // Not found - return null
// // return null;
// //}
// //else if (res.Count() > 1)
// //{
// // throw new InvalidOperationException("Get by username returned more than 1 user for username: "******"SELECT * from [Organization] Where AccountNumber='" + accountNumber + "'";
using (var context = PODFactory.GetContext(true))
{
var result = context.Database.Connection.Query <Organization>(sql).FirstOrDefault();
if (result != null)
{
return(result);
}
}
return(null);
}
protected T GetByID <T>(Guid id) where T : class
{
try
{
using (var context = PODFactory.GetContext(true))
{
return(context.Database.Connection.Get <T>(id));
}
}
catch (Exception ex)
{
return(null);
}
}
public Ticket GetTicket(int ticketNumber)
{
string sql = "SELECT * from [Ticket] Where TicketNumber='" + ticketNumber + "'";
List <Ticket> tickets = new List <Ticket>();
using (var context = PODFactory.GetContext(true))
{
var result = context.Database.Connection.Query <Ticket>(sql).FirstOrDefault();
if (result != null)
{
return(result);
}
return(null);
}
}
public IEnumerable <User> GetUsers(Guid organizationID, string organizationName, string fullName, string userName, string roleName, string statusName, DateTime?lastLoginDate, int currentPageNumber, int pageSize, string sortExpression, string sortDirection, out int totalRows)
{
try
{
List <User> users = new List <User>();
var offset = (currentPageNumber - 1) * pageSize;
var param = new DynamicParameters();
param.Add("@OrganizationID", organizationID, dbType: DbType.Guid, direction: ParameterDirection.Input);
param.Add("@OrganizationName", organizationName, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@FullName", fullName, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@UserName", userName, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@RoleName", roleName, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@StatusName", statusName, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@LastLoginDate", lastLoginDate, dbType: DbType.DateTime, direction: ParameterDirection.Input);
param.Add("@Offset", offset, dbType: DbType.Int32, direction: ParameterDirection.Input);
param.Add("@PageSize", pageSize, dbType: DbType.Int32, direction: ParameterDirection.Input);
param.Add("@SortExpression", sortExpression, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@SortDirection", sortDirection, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
using (var context = PODFactory.GetContext(true))
{
var multi = context.Database.Connection.QueryMultiple("[GetUsers]", param, commandType: CommandType.StoredProcedure);
users = multi.Read <User, UserRole, UserOrganization, User>((u, r, o) =>
{ u.Role = r; u.Organization = o; return(u); }, splitOn: "IsPrimary, OrganizationName").ToList();
totalRows = param.Get <int>("RowCount");
}
if (users == null || users.Count == 0)
{
// Not found
return(users);
}
return(users);
}
catch (Exception ex)
{
_loggingManager.Log.Error(ex, ex.Source);
throw;
}
}
public int Delete(Guid userId) //
{
try
{
var param = new DynamicParameters();
param.Add("@UserID", userId, dbType: DbType.Guid, direction: ParameterDirection.Input);
using (var context = PODFactory.GetContext(true))
{
int result = context.Database.Connection.Execute("[DeleteUser]", param, commandType: CommandType.StoredProcedure);
return(result);
}
}
catch (Exception ex)
{
_loggingManager.Log.Error(ex, ex.Source);
throw;
}
}
public Organization GetCustomerByName(string name)
{
try
{
var param = new DynamicParameters();
param.Add("@OrganizationName", name, DbType.String, direction: ParameterDirection.Input);
using (var context = PODFactory.GetContext(true))
{
var multi = context.Database.Connection.QueryMultiple("[GetOrganizationByName]", param, commandType: CommandType.StoredProcedure);
Organization org = multi.Read <Organization, User, Organization>((o, u) =>
{ o.AdminUser = u; return(o); }, splitOn: "FullName").FirstOrDefault();
return(org);
}
}
catch (Exception e)
{
throw;
}
}
public IEnumerable <Ticket> GetTickets(Guid organizationID, string accontNumber, string accountName, int?ticketNumber, DateTime?deliveryDate, string invoiceNumber, int currentPageNumber, int pageSize, string sortExpression, string sortDirection, out int totalRows)
{
try
{
//test
pageSize = 15;
currentPageNumber = 1;
List <Ticket> tickets = new List <Ticket>();
var offset = (currentPageNumber - 1) * pageSize;
var param = new DynamicParameters();
param.Add("@OrganizationID", organizationID, dbType: DbType.Guid, direction: ParameterDirection.Input);
param.Add("@AccontNumber", accontNumber, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@AccountName", accountName, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@TicketNumber", ticketNumber, dbType: DbType.Int32, direction: ParameterDirection.Input);
param.Add("@DeliveryDate", deliveryDate, dbType: DbType.DateTime, direction: ParameterDirection.Input);
param.Add("@InvoiceNumber", invoiceNumber, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@Offset", offset, dbType: DbType.Int32, direction: ParameterDirection.Input);
param.Add("@PageSize", pageSize, dbType: DbType.Int32, direction: ParameterDirection.Input);
param.Add("@SortExpression", sortExpression, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@SortDirection", sortDirection, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
using (var context = PODFactory.GetContext(true))
{
var query = context.Database.Connection.QueryMultiple("[GetTickets]", param, commandType: CommandType.StoredProcedure);
tickets = query.Read <Ticket>().ToList();
totalRows = param.Get <int>("RowCount");
}
return(tickets);
}
catch (Exception ex)
{
_loggingManager.Log.Error(ex, ex.Source);
throw;
}
}
public List <Organization> GetOrganizationsByParentID(Guid parentOrganization)
{
try
{
var param = new DynamicParameters();
param.Add("@ParentOrganization", parentOrganization, DbType.Guid, direction: ParameterDirection.Input);
using (var context = PODFactory.GetContext(true))
{
var multi = context.Database.Connection.QueryMultiple("[GetOrganizationsByParentID]", param, commandType: CommandType.StoredProcedure);
List <Organization> orgs = multi.Read <Organization>().ToList();
//if (orgs != null)
//{
//}
return(orgs);
}
}
catch (Exception e)
{
throw;
}
}
public void Update(User user) // void
{
try
{
using (var context = PODFactory.GetContext(true))
{
var param = new DynamicParameters();
param.Add("@UserID", user.ID, dbType: DbType.Guid, direction: ParameterDirection.Input);
param.Add("@FullName", user.FullName, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@RoleID", user.Role.RoleID, dbType: DbType.Guid, direction: ParameterDirection.Input);
param.Add("@IsPrimary", user.Role.IsPrimary, dbType: DbType.Boolean, direction: ParameterDirection.Input);
param.Add("@StatusID", user.StatusID, dbType: DbType.Int32, direction: ParameterDirection.Input);
context.Database.Connection.Execute("[dbo].[UpdateUser]", param, commandType: CommandType.StoredProcedure);
}
}
catch (Exception ex)
{
_loggingManager.Log.Error(ex, ex.Source);
throw;
}
}
public User GetUserByUsername(string username)
{
try
{
var param = new DynamicParameters();
param.Add("@Username", username, dbType: DbType.String, direction: ParameterDirection.Input);
using (var context = PODFactory.GetContext(true))
{
var multi = context.Database.Connection.QueryMultiple("[GetUserByUsername]", param, commandType: CommandType.StoredProcedure);
User user = multi.Read <User, UserRole, UserOrganization, User>((u, r, o) =>
{ u.Role = r; u.Organization = o; return(u); }, splitOn: "RoleID, OrganizationID").FirstOrDefault();
return(user);
}
}
catch (Exception ex)
{
_loggingManager.Log.Error(ex, ex.Source);
throw;
}
}
public IEnumerable <Report> GetReports(Guid userID, int?reportTypeID, string details, int currentPageNumber, int pageSize, string sortExpression, string sortDirection, out int totalRows)
{
try
{
//test
pageSize = 15;
currentPageNumber = 1;
List <Report> reports = new List <Report>();
var offset = (currentPageNumber - 1) * pageSize;
var param = new DynamicParameters();
param.Add("@UserID", userID, dbType: DbType.Guid, direction: ParameterDirection.Input);
param.Add("@ReportTypeID", reportTypeID, dbType: DbType.Int32, direction: ParameterDirection.Input);
param.Add("@Details", details, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@Offset", offset, dbType: DbType.Int32, direction: ParameterDirection.Input);
param.Add("@PageSize", pageSize, dbType: DbType.Int32, direction: ParameterDirection.Input);
param.Add("@SortExpression", sortExpression, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@SortDirection", sortDirection, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
using (var context = PODFactory.GetContext(true))
{
var query = context.Database.Connection.QueryMultiple("[GetReports]", param, commandType: CommandType.StoredProcedure);
reports = query.Read <Report>().ToList();
totalRows = param.Get <int>("RowCount");
}
return(reports);
}
catch (Exception ex)
{
_loggingManager.Log.Error(ex, ex.Source);
throw;
}
}
/// <summary>
/// Load your modules or register your services here!
/// </summary>
/// <param name="kernel">The kernel.</param>
private static void RegisterServices(IKernel kernel)
{
//kernel.Bind<POD.Interfaces.ICustomerDataService>().To<POD.Data.CustomerDataService>();
kernel.Bind <POD.Interfaces.ICustomerDataService>().To <POD.Data.Services.CustomerDataService>()
.WithConstructorArgument("context", PODFactory.GetContext(true));
}
