public SouvenirStockViewModel GetSouvenirStockById(int id)
{
using (MarcommEntities db = new MarcommEntities())
{
var souvenir = (from p in db.t_souvenir
where p.id.Equals(id) && p.is_delete == false
select p).SingleOrDefault();
SouvenirStockViewModel Souvenir = new SouvenirStockViewModel();
Souvenir.id = souvenir.id;
Souvenir.code = souvenir.code;
Souvenir.tEventId = souvenir.t_event_id;
Souvenir.requestBy = souvenir.request_by;
Souvenir.requestDate = souvenir.request_date;
Souvenir.requestDueDate = souvenir.request_due_date;
Souvenir.note = souvenir.note;
Souvenir.status = souvenir.status;
Souvenir.requestByToFullName = GetFullNameFromIdEmployee(souvenir.request_by);
Souvenir.createdByToFullName = GetFullNameFromIdEmployee(Convert.ToInt32(souvenir.created_by));
Souvenir.eventIdToEventCode = GetEventCodeFromEventId(souvenir.t_event_id);
return(Souvenir);
}
}
private string GetEventCodeFromEventId(int id)
{
using (MarcommEntities db = new MarcommEntities())
{
return((from p in db.t_event where p.id.Equals(id) && p.is_delete == false select p.code).SingleOrDefault());
}
}
private string GetSouvenirNameFromSouvenirId(int id)
{
using (MarcommEntities db = new MarcommEntities())
{
return((from p in db.m_souvenir where p.id.Equals(id) && !p.is_delete select p.name).SingleOrDefault());
}
}
public List <RoleViewModel> GetAllRolesByMenuAccessNotExist()
{
using (MarcommEntities db = new MarcommEntities())
{
List <m_role> roles = (from p in db.m_role
where (!p.m_menu_access.Any(x => x.m_role_id.Equals(p.id) && !x.is_delete) && !p.is_delete)
select p
).ToList();
List <RoleViewModel> Roles = new List <RoleViewModel>();
foreach (var role in roles)
{
RoleViewModel Role = new RoleViewModel();
Role.ID = role.id;
Role.Code = role.code;
Role.Name = role.name;
Role.Description = role.description;
Role.IsDelete = role.is_delete;
Role.CreatedBy = role.created_by;
Role.CreatedDate = role.created_date;
Role.UpdatedBy = role.created_by;
Role.UpdatedDate = role.created_date;
Roles.Add(Role);
}
return(Roles);
}
}
private string GetFullNameFromIdEmployee(int id)
{
using (MarcommEntities db = new MarcommEntities())
{
return((from p in db.m_employee where p.id.Equals(id) && !p.id_delete select p.first_name + " " + p.last_name).SingleOrDefault());
}
}
public void EditMenuAccess(MenuAccessViewModel menuAccess)
{
using (MarcommEntities db = new MarcommEntities())
{
m_menu_access menu_access = new m_menu_access();
menu_access.m_role_id = menuAccess.mRoleId;
menu_access.is_delete = menuAccess.isDelete;
var menu_is_already_exist = (from p in db.m_menu_access
where p.m_role_id.Equals(menu_access.m_role_id) && !p.is_delete
select p).ToList();
foreach (var menu_id in menuAccess.selectedMenus)
{
if (!menu_is_already_exist.Any(x => x.m_menu_id.Equals(int.Parse(menu_id))))
{
menu_access.m_menu_id = int.Parse(menu_id);
menu_access.is_delete = false;
menu_access.created_by = menuAccess.createdBy;
menu_access.created_date = menuAccess.createdDate;
db.m_menu_access.Add(menu_access);
db.SaveChanges();
}
else
{
menu_access = menu_is_already_exist.Where(x => x.m_menu_id.Equals(int.Parse(menu_id))).SingleOrDefault();
menu_access.is_delete = true;
menu_access.updated_by = menuAccess.updateBy;
menu_access.updated_date = menuAccess.updatedDate;
db.Entry(menu_access).State = EntityState.Modified;
db.SaveChanges();
}
}
}
}
public MenuAccessViewModel GetMenuAccessByRoleId(int id)
{
using (MarcommEntities db = new MarcommEntities())
{
var menu_accesses = (from p in db.m_menu_access
where p.m_role_id.Equals(id) && !p.is_delete
select new
{
role_id = p.m_role_id,
role_code = p.m_role.code,
role_name = p.m_role.name,
menu_name = p.m_menu.name,
menu_id = p.m_menu_id
}).ToList();
MenuAccessViewModel MenuAccess = new MenuAccessViewModel();
var availableMenus = GetAllMenus();
foreach (var menu_access in menu_accesses)
{
availableMenus.FirstOrDefault(x => x.Value.Equals(menu_access.menu_id.ToString())).Selected = true;
}
MenuAccess.mRoleId = menu_accesses.FirstOrDefault().role_id;
MenuAccess.mRoleCode = menu_accesses.FirstOrDefault().role_code;
MenuAccess.mRoleName = menu_accesses.FirstOrDefault().role_name;
MenuAccess.availableMenus = availableMenus;
return(MenuAccess);
}
}
public void UpdateSettlementSouvenirRequest(SouvenirRequestViewModel souvenirItem)
{
using (MarcommEntities db = new MarcommEntities())
{
var dictSouvenirItem = souvenirItem.listId.Zip(souvenirItem.listQtySettlement, (id, qty) => new { id, qty })
.ToDictionary(x => x.id, x => x.qty);
var souvenir_items = (from p in db.t_souvenir_item
where souvenirItem.listId.ToList().Contains(p.id) && !p.is_delete
select p)
.ToList();
foreach (var souvenir_item in souvenir_items)
{
souvenir_item.qty_settlement = dictSouvenirItem[souvenir_item.id];
souvenir_item.updated_by = souvenirItem.updatedBy;
souvenir_item.updated_date = DateTime.Now;
souvenir_item.t_souvenir.status = 4;
souvenir_item.t_souvenir.settlement_by = Convert.ToInt32(souvenirItem.updatedBy);
souvenir_item.t_souvenir.settlement_date = DateTime.Now;
souvenir_item.t_souvenir.update_by = souvenirItem.updatedBy;
souvenir_item.t_souvenir.update_date = DateTime.Now;
db.Entry(souvenir_item).State = EntityState.Modified;
db.SaveChanges();
}
}
}
public int getRoleId(string username)
{
int roleId = 0;
using (MarcommEntities db = new MarcommEntities())
{
var getRoleid = (from role in db.m_role
join user in db.m_user on role.id equals user.m_role_id
join employ in db.m_employee on user.m_employee_id equals employ.id
where user.username == username && user.is_delete == false &&
employ.id_delete == false && role.is_delete == false
select role.id).SingleOrDefault();
if (getRoleid == 0)
{
roleId = 0;
}
else
{
roleId = getRoleid;
}
}
return(roleId);
}
public string getRoleName(string username)
{
string RoleName = string.Empty;
using (MarcommEntities db = new MarcommEntities())
{
var getRoleNm = (from role in db.m_role
join user in db.m_user on role.id equals user.m_role_id
join employ in db.m_employee on user.m_employee_id equals employ.id
where user.username == username && user.is_delete == false &&
employ.id_delete == false && role.is_delete == false
select role.name).SingleOrDefault();
if (string.IsNullOrEmpty(getRoleNm))
{
RoleName = "Not Assign Role";
}
else
{
RoleName = getRoleNm;
}
}
return(RoleName);
}
public List <SouvenirRequestViewModel> GetListSouvenirBySouvenirTransactionId(int id)
{
using (MarcommEntities db = new MarcommEntities())
{
var souvenir_items = (from p in db.t_souvenir
where p.id.Equals(id) && p.is_delete == false
select p.t_souvenir_item.ToList()
).SingleOrDefault();
List <SouvenirRequestViewModel> souvenirItems = new List <SouvenirRequestViewModel>();
foreach (var souvenir_item in souvenir_items)
{
SouvenirRequestViewModel souvenirItem = new SouvenirRequestViewModel();
souvenirItem.id = souvenir_item.id;
souvenirItem.mSouvenirId = souvenir_item.m_souvenir_id;
souvenirItem.qty = souvenir_item.qty;
souvenirItem.qtySettlement = souvenir_item.qty_settlement;
souvenirItem.note = souvenir_item.note;
souvenirItem.souvenirIdToSouvenirName = GetSouvenirNameFromSouvenirId(souvenir_item.m_souvenir_id);
souvenirItems.Add(souvenirItem);
}
return(souvenirItems);
}
}
public List <RoleViewModel> GetAllRolesDDL()
{
using (MarcommEntities db = new MarcommEntities())
{
var roles = (from p in db.m_role select p).ToList();
List <RoleViewModel> Roles = new List <RoleViewModel>();
foreach (var role in roles)
{
RoleViewModel Role = new RoleViewModel();
Role.ID = role.id;
Role.Code = role.code;
Role.Name = role.name;
Role.Description = role.description;
Role.IsDelete = role.is_delete;
Role.CreatedBy = role.created_by;
Role.CreatedDate = role.created_date;
Role.UpdatedBy = role.created_by;
Role.UpdatedDate = role.created_date;
Roles.Add(Role);
}
return(Roles);
}
}
public List <m_menu_access> GetAllMenuAccess()
{
using (MarcommEntities db = new MarcommEntities())
{
var ListMenuAccess = db.m_menu_access.Where(x => x.is_delete == false).ToList();
return(ListMenuAccess);
}
}
public m_employee getEmployeeByUserId(int employeeId)
{
using (MarcommEntities db = new MarcommEntities())
{
var getEmployee = db.m_employee.Where(x => x.id == employeeId).SingleOrDefault();
return(getEmployee);
}
}
public List <m_menu> GetAllMenu()
{
using (MarcommEntities db = new MarcommEntities())
{
var ListMenu = db.m_menu.Where(x => x.is_delete == false).ToList();
return(ListMenu);
}
}
public m_user getUserByUsername(string username)
{
using (MarcommEntities db = new MarcommEntities())
{
m_user user = db.m_user.Where(y => y.username == username && y.is_delete == false).SingleOrDefault();
return(user);
}
}
public List <SouvenirStockViewModel> GetAllSouvenirStock(SearchTransactionSouvenirViewModel parameters)
{
using (MarcommEntities db = new MarcommEntities())
{
bool NullCode = string.IsNullOrWhiteSpace(parameters.code);
bool NullRequestBy = string.IsNullOrWhiteSpace(parameters.requestBy);
bool NullRequestDate = string.IsNullOrWhiteSpace(parameters.requestDate);
bool NullDueDate = string.IsNullOrWhiteSpace(parameters.dueDate);
bool NullStatus = string.IsNullOrWhiteSpace(parameters.status);
bool NullCreatedDate = string.IsNullOrWhiteSpace(parameters.createdDate);
bool NullCreatedBy = string.IsNullOrWhiteSpace(parameters.createdBy);
DateTime?requestDate = NullRequestDate ? (DateTime?)null :
DateTime.Parse(
DateTime.ParseExact(parameters.requestDate, "dd'-'MM'-'yyyy", CultureInfo.InvariantCulture).ToString("MM'/'dd'/'yyyy"));
DateTime?dueDate = NullDueDate ? (DateTime?)null :
DateTime.Parse(
DateTime.ParseExact(parameters.dueDate, "dd'-'MM'-'yyyy", CultureInfo.InvariantCulture).ToString("MM'/'dd'/'yyyy"));
DateTime?createdDate = NullCreatedDate ? (DateTime?)null :
DateTime.Parse(
DateTime.ParseExact(parameters.createdDate, "dd'-'MM'-'yyyy", CultureInfo.InvariantCulture).ToString("MM'/'dd'/'yyyy"));
var souvenir_stock = (from p in db.t_souvenir where p.is_delete == false select p).ToList();
souvenir_stock = !NullCode ? (from p in souvenir_stock where p.code.Contains(parameters.code) select p).ToList() : souvenir_stock;
souvenir_stock = !NullRequestDate ? (from p in souvenir_stock where DateTime.Compare((DateTime)p.request_date, (DateTime)requestDate) == 0 select p).ToList() : souvenir_stock;
souvenir_stock = !NullDueDate ? (from p in souvenir_stock where DateTime.Compare((DateTime)p.request_due_date, (DateTime)dueDate) == 0 select p).ToList() : souvenir_stock;
souvenir_stock = !NullCreatedDate ? (from p in souvenir_stock where DateTime.Compare((DateTime)p.created_date, (DateTime)createdDate) == 0 select p).ToList() : souvenir_stock;
souvenir_stock = !NullRequestBy ? (from p in souvenir_stock where p.request_by.Equals(
(from q in db.m_employee where !q.id_delete && (q.first_name + " " + q.last_name).Contains(parameters.requestBy) select q.id).SingleOrDefault()) select p).ToList() : souvenir_stock;
souvenir_stock = !NullCreatedBy ? (from p in souvenir_stock where p.created_by.Equals(Convert.ToInt64(
(from q in db.m_employee where !q.id_delete && (q.first_name + " " + q.last_name).Contains(parameters.createdBy) select q.id).SingleOrDefault())) select p).ToList() : souvenir_stock;
souvenir_stock = !NullStatus ? (from p in souvenir_stock where GetStatusId(parameters.status.ToLower()).Contains((int)p.status) select p).ToList() : souvenir_stock;
List <SouvenirStockViewModel> SouvenirStock = new List <SouvenirStockViewModel>();
foreach (var souvenir in souvenir_stock)
{
SouvenirStockViewModel Souvenir = new SouvenirStockViewModel();
Souvenir.id = souvenir.id;
Souvenir.code = souvenir.code;
Souvenir.requestBy = souvenir.request_by;
Souvenir.requestDate = souvenir.request_date;
Souvenir.requestDueDate = souvenir.request_due_date;
Souvenir.status = souvenir.status;
Souvenir.createdDate = souvenir.created_date;
Souvenir.createdBy = souvenir.created_by;
Souvenir.requestByToFullName = GetFullNameFromIdEmployee(souvenir.request_by);
Souvenir.createdByToFullName = GetFullNameFromIdEmployee(Convert.ToInt32(souvenir.created_by));
SouvenirStock.Add(Souvenir);
}
return(SouvenirStock);
}
}
public List <m_role> getRoleByUserId(int userid)
{
using (MarcommEntities db = new MarcommEntities())
{
var RoleList = (from role in db.m_role
join user in db.m_user on role.id equals user.m_role_id
join employ in db.m_employee on user.m_employee_id equals employ.id
where user.id == userid && user.is_delete == false &&
employ.id_delete == false && role.is_delete == false
select role).ToList();
return(RoleList);
}
}
public void CloseSettlementSouvenirRequest(SouvenirStockViewModel Souvenir)
{
using (MarcommEntities db = new MarcommEntities())
{
var souvenir = (from p in db.t_souvenir where p.id.Equals(Souvenir.id) select p).SingleOrDefault();
souvenir.status = 6;
souvenir.update_by = Souvenir.updateBy;
souvenir.update_date = DateTime.Now;
db.Entry(souvenir).State = EntityState.Modified;
db.SaveChanges();
}
}
public void ApprovalSettlementSouvenirRequest(SouvenirStockViewModel Souvenir)
{
using (MarcommEntities db = new MarcommEntities())
{
var souvenir = (from p in db.t_souvenir where p.id.Equals(Souvenir.id) select p).SingleOrDefault();
souvenir.status = Souvenir.status;
souvenir.settlement_approved_by = Souvenir.settlementApprovedBy;
souvenir.settlement_approved_date = Souvenir.settlementApprovedDate;
souvenir.reject_reason = Souvenir.rejectReason;
souvenir.update_by = Souvenir.updateBy;
souvenir.update_date = DateTime.Now;
db.Entry(souvenir).State = EntityState.Modified;
db.SaveChanges();
}
}
public void CreateMenuAccess(MenuAccessViewModel menuAccess)
{
using (MarcommEntities db = new MarcommEntities())
{
m_menu_access menu_access = new m_menu_access();
menu_access.m_role_id = menuAccess.mRoleId;
menu_access.is_delete = false;
menu_access.created_by = menuAccess.createdBy;
menu_access.created_date = DateTime.Now;
foreach (var menu_id in menuAccess.selectedMenus)
{
menu_access.m_menu_id = int.Parse(menu_id);
db.m_menu_access.Add(menu_access);
db.SaveChanges();
}
}
}
public List <SelectListItem> GetAllMenus()
{
using (MarcommEntities db = new MarcommEntities())
{
var menus = from p in db.m_menu where !p.is_delete orderby p.name select p;
var availableMenus = new List <SelectListItem>();
foreach (var menu in menus)
{
availableMenus.Add(new SelectListItem
{
Text = menu.name,
Value = menu.id.ToString(),
Selected = false,
});
}
return(availableMenus);
}
}
public bool IsValid(string username, string password)
{
bool isValid = false;
using (MarcommEntities db = new MarcommEntities())
{
var user = db.m_user.Where(x => x.username == username && x.is_delete == false).SingleOrDefault();
if (user != null)
{
if (user.password == password)
{
isValid = true;
}
}
}
return(isValid);
}
public void DeleteMenuAccess(MenuAccessViewModel menuAccess)
{
using (MarcommEntities db = new MarcommEntities())
{
var menu_is_already_exist = (from p in db.m_menu_access
where p.m_role_id.Equals(menuAccess.mRoleId) && !p.is_delete
select p).ToList();
foreach (var menu in menu_is_already_exist)
{
m_menu_access menu_access = menu;
menu_access.is_delete = true;
menu_access.updated_by = menuAccess.updateBy;
menu_access.updated_date = DateTime.Now;
db.Entry(menu_access).State = EntityState.Modified;
db.SaveChanges();
}
}
}
public int TotalCompany()
{
int totalCompany = 0;
using (MarcommEntities db = new MarcommEntities())
{
var getCompany = db.m_company.Count();
if (getCompany == 0)
{
totalCompany = 0;
}
else
{
totalCompany = getCompany;
}
}
return(totalCompany);
}
public int TotalUser()
{
int totalUser = 0;
using (MarcommEntities db = new MarcommEntities())
{
var getUser = db.m_user.Count();
if (getUser == 0)
{
totalUser = 0;
}
else
{
totalUser = getUser;
}
}
return(totalUser);
}
public int TotalProduct()
{
int totalProduct = 0;
using (MarcommEntities db = new MarcommEntities())
{
var getProduct = db.m_product.Count();
if (getProduct == 0)
{
totalProduct = 0;
}
else
{
totalProduct = getProduct;
}
}
return(totalProduct);
}
public int TotalEmployee()
{
int totalEmployee = 0;
using (MarcommEntities db = new MarcommEntities())
{
var getEmployee = db.m_employee.Count();
if (getEmployee == 0)
{
totalEmployee = 0;
}
else
{
totalEmployee = getEmployee;
}
}
return(totalEmployee);
}
public List <RoleViewModel> GetAllRoles(string id)
{
using (MarcommEntities db = new MarcommEntities())
{
string[] parameter = id.Split('|');
string RoleCode = parameter[0];
string RoleName = parameter[1];
string tempCreatedDate = parameter[2];
string CreatedBy = parameter[3];
bool NullRoleCode = string.IsNullOrWhiteSpace(RoleCode);
bool NullRoleName = string.IsNullOrWhiteSpace(RoleName);
bool NullCreatedDate = string.IsNullOrWhiteSpace(tempCreatedDate);
bool NullCreatedBy = string.IsNullOrWhiteSpace(CreatedBy);
DateTime?CreatedDate = NullCreatedDate ? (DateTime?)null :
DateTime.Parse(
DateTime.ParseExact(tempCreatedDate, "dd'-'MM'-'yyyy", CultureInfo.InvariantCulture).ToString("MM'/'dd'/'yyyy"));
List <m_role> roles;
{
if (NullRoleCode && NullRoleName && NullCreatedDate && NullCreatedBy)
{
roles = (from p in db.m_role select p).ToList();
}
else if (NullRoleName && NullCreatedDate && NullCreatedBy) // 1
{
roles = (from p in db.m_role where p.code.Equals(RoleCode) select p).ToList();
}
else if (NullRoleCode && NullCreatedDate && NullCreatedBy) // 2
{
roles = (from p in db.m_role where p.name.Equals(RoleName) select p).ToList();
}
else if (NullRoleCode && NullRoleName && NullCreatedBy) // 3
{
roles = (from p in db.m_role where (DateTime.Compare(p.created_date, (DateTime)CreatedDate) == 0) select p).ToList();
}
else if (NullRoleCode && NullRoleName && NullCreatedDate) // 4
{
roles = (from p in db.m_role where p.created_by.Contains(CreatedBy) select p).ToList();
}
else if (NullCreatedDate && NullCreatedBy) // 1 2
{
roles = (from p in db.m_role where (p.code.Equals(RoleCode) && p.name.Equals(RoleName)) select p).ToList();
}
else if (NullRoleName && NullCreatedBy) // 1 3
{
roles = (from p in db.m_role where (p.code.Equals(RoleCode) && (DateTime.Compare(p.created_date, (DateTime)CreatedDate) == 0)) select p).ToList();
}
else if (NullRoleName && NullCreatedDate) // 1 4
{
roles = (from p in db.m_role where (p.code.Equals(RoleCode) && p.created_by.Contains(CreatedBy)) select p).ToList();
}
else if (NullRoleCode && NullCreatedBy) // 2 3
{
roles = (from p in db.m_role where (p.name.Equals(RoleName) && (DateTime.Compare(p.created_date, (DateTime)CreatedDate) == 0)) select p).ToList();
}
else if (NullRoleCode && NullCreatedDate) // 2 4
{
roles = (from p in db.m_role where (p.name.Equals(RoleName) && p.created_by.Contains(CreatedBy)) select p).ToList();
}
else if (NullRoleCode && NullRoleName) // 3 4
{
roles = (from p in db.m_role where ((DateTime.Compare(p.created_date, (DateTime)CreatedDate) == 0) && p.created_by.Contains(CreatedBy)) select p).ToList();
}
else if (NullCreatedBy) // 1 2 3
{
roles = (from p in db.m_role where (p.code.Equals(RoleCode) && p.name.Equals(RoleName) && (DateTime.Compare(p.created_date, (DateTime)CreatedDate) == 0)) select p).ToList();
}
else if (NullCreatedDate) // 1 2 4
{
roles = (from p in db.m_role where (p.code.Equals(RoleCode) && p.name.Equals(RoleName) && p.created_by.Contains(CreatedBy)) select p).ToList();
}
else if (NullRoleName) // 1 3 4
{
roles = (from p in db.m_role where (p.code.Equals(RoleCode) && (DateTime.Compare(p.created_date, (DateTime)CreatedDate) == 0) && p.created_by.Contains(CreatedBy)) select p).ToList();
}
else if (NullRoleCode) // 2 3 4
{
roles = (from p in db.m_role where (p.name.Equals(RoleName) && (DateTime.Compare(p.created_date, (DateTime)CreatedDate) == 0) && p.created_by.Contains(CreatedBy)) select p).ToList();
}
else // 1 2 3 4
{
roles = (from p in db.m_role where (p.code.Equals(RoleCode) && p.name.Equals(RoleName) && (DateTime.Compare(p.created_date, (DateTime)CreatedDate) == 0) && p.created_by.Contains(CreatedBy)) select p).ToList();
}
}
roles = (from p in roles
where (p.m_menu_access.Any(x => x.m_role_id.Equals(p.id) && !x.is_delete) && !p.is_delete)
select p).ToList();
List <RoleViewModel> Roles = new List <RoleViewModel>();
foreach (var role in roles)
{
RoleViewModel Role = new RoleViewModel();
Role.ID = role.id;
Role.Code = role.code;
Role.Name = role.name;
Role.Description = role.description;
Role.IsDelete = role.is_delete;
Role.CreatedBy = role.created_by;
Role.CreatedDate = role.created_date;
Role.UpdatedBy = role.created_by;
Role.UpdatedDate = role.created_date;
Roles.Add(Role);
}
return(Roles);
}
}