public List <Auth_User> GetExport(DataSourceRequest request, string whereCondition)
{
List <SqlParameter> param = new List <SqlParameter>();
param.Add(new SqlParameter("@Page", 1));
param.Add(new SqlParameter("@PageSize", 99999));
param.Add(new SqlParameter("@WhereCondition", whereCondition));
DataTable dt = new SqlHelper().ExecuteQuery("p_Auth_User_Select_By_Page", param);
var lst = new List <Auth_User>();
foreach (DataRow row in dt.Rows)
{
var item = new Auth_User();
item.UserID = !row.IsNull("UserID") ? row["UserID"].ToString() : "";
item.FullName = !row.IsNull("FullName") ? row["FullName"].ToString() : "";
item.DisplayName = !row.IsNull("DisplayName") ? row["DisplayName"].ToString() : "";
item.Phone = !row.IsNull("Phone") ? row["Phone"].ToString() : "";
item.Email = !row.IsNull("Email") ? row["Email"].ToString() : "";
item.IsActive = !row.IsNull("IsActive") ? Convert.ToBoolean(row["IsActive"]) : false;
item.Note = !row.IsNull("Note") ? row["Note"].ToString() : "";
item.RowCreatedAt = !row.IsNull("RowCreatedAt") ? DateTime.Parse(row["RowCreatedAt"].ToString()) : DateTime.Parse("01/01/1900");
item.RowCreatedBy = !row.IsNull("RowCreatedBy") ? row["RowCreatedBy"].ToString() : "";
item.Roles = !row.IsNull("Roles") ? row["Roles"].ToString() : "";
lst.Add(item);
}
return(lst);
}
public DataSourceResult GetPage(DataSourceRequest request, string whereCondition)
{
List <SqlParameter> param = new List <SqlParameter>();
param.Add(new SqlParameter("@Page", request.Page));
param.Add(new SqlParameter("@PageSize", request.PageSize));
param.Add(new SqlParameter("@WhereCondition", whereCondition));
param.Add(new SqlParameter("@Sort", CustomModel.GetSortStringFormRequest(request)));
DataTable dt = new SqlHelper().ExecuteQuery("p_Auth_User_Select_By_Page", param);
var lst = new List <Auth_User>();
foreach (DataRow row in dt.Rows)
{
var item = new Auth_User();
item.UserID = !row.IsNull("UserID") ? row["UserID"].ToString() : "";
item.FullName = !row.IsNull("FullName") ? row["FullName"].ToString() : "";
item.DisplayName = !row.IsNull("DisplayName") ? row["DisplayName"].ToString() : "";
item.Phone = !row.IsNull("Phone") ? row["Phone"].ToString() : "";
item.Email = !row.IsNull("Email") ? row["Email"].ToString() : "";
item.IsActive = !row.IsNull("IsActive") ? Convert.ToBoolean(row["IsActive"]) : false;
item.Note = !row.IsNull("Note") ? row["Note"].ToString() : "";
item.RowCreatedAt = !row.IsNull("RowCreatedAt") ? DateTime.Parse(row["RowCreatedAt"].ToString()) : DateTime.Parse("01/01/1900");
item.RowCreatedBy = !row.IsNull("RowCreatedBy") ? row["RowCreatedBy"].ToString() : "";
item.Roles = !row.IsNull("Roles") ? row["Roles"].ToString() : "";
lst.Add(item);
}
request.Filters = null;
DataSourceResult result = new DataSourceResult();
result.Data = lst;
result.Total = dt.Rows.Count > 0 ? Convert.ToInt32(dt.Rows[0]["RowCount"]) : 0;
return(result);
}
public ActionResult Create(Auth_User item)
{
IDbConnection db = new OrmliteConnection().openConn();
try
{
if (!string.IsNullOrEmpty(item.UserID) &&
!string.IsNullOrEmpty(item.DisplayName) &&
!string.IsNullOrEmpty(item.FullName))
{
var isExist = db.GetByIdOrDefault<Auth_User>(item.UserID);
item.Phone = !string.IsNullOrEmpty(item.Phone) ? item.Phone : "";
item.Email = !string.IsNullOrEmpty(item.Email) ? item.Email : "";
item.Note = !string.IsNullOrEmpty(item.Note) ? item.Note : "";
if (userAsset.ContainsKey("Insert") && userAsset["Insert"] && item.RowCreatedAt == null && item.RowCreatedBy == null)
{
if(isExist != null)
return Json(new { success = false, message = "Người dùng đã tồn tại." });
item.Password = SqlHelper.GetMd5Hash("123456");
item.RowCreatedAt = DateTime.Now;
item.RowCreatedBy = currentUser.UserID;
db.Insert<Auth_User>(item);
return Json(new { success = true, UserID = item.UserID, RowCreatedAt = item.RowCreatedAt, RowCreatedBy = item.RowCreatedBy });
}
else if (userAsset.ContainsKey("Update") && userAsset["Update"] && isExist != null)
{
item.Password = isExist.Password;
item.RowUpdatedAt = DateTime.Now;
item.RowUpdatedBy = currentUser.UserID;
if (isExist.RowCreatedBy != "system")
{
db.Update<Auth_User>(item);
}
else
{
return Json(new { success = false, message = "Dữ liệu này không cho chỉnh sửa liên hệ admin để biết thêm chi tiết" });
}
return Json(new { success = true });
}
else
return Json(new { success = false, message = "Bạn không có quyền" });
}
else
{
return Json(new { success = false, message = "Chưa nhập giá trị" });
}
}
catch (Exception e)
{
log.Error("AD_User - Create - " + e.Message);
return Json(new { success = false, message = e.Message });
}
finally { db.Close(); }
}
public List<Auth_User> GetExport(DataSourceRequest request, string whereCondition)
{
List<SqlParameter> param = new List<SqlParameter>();
param.Add(new SqlParameter("@Page", 1));
param.Add(new SqlParameter("@PageSize", 99999));
param.Add(new SqlParameter("@WhereCondition", whereCondition));
DataTable dt = new SqlHelper().ExecuteQuery("p_Auth_User_Select_By_Page", param);
var lst = new List<Auth_User>();
foreach (DataRow row in dt.Rows)
{
var item = new Auth_User();
item.UserID = !row.IsNull("UserID") ? row["UserID"].ToString() : "";
item.FullName = !row.IsNull("FullName") ? row["FullName"].ToString() : "";
item.DisplayName = !row.IsNull("DisplayName") ? row["DisplayName"].ToString() : "";
item.Phone = !row.IsNull("Phone") ? row["Phone"].ToString() : "";
item.Email = !row.IsNull("Email") ? row["Email"].ToString() : "";
item.IsActive = !row.IsNull("IsActive") ? Convert.ToBoolean(row["IsActive"]) : false;
item.Note = !row.IsNull("Note") ? row["Note"].ToString() : "";
item.RowCreatedAt = !row.IsNull("RowCreatedAt") ? DateTime.Parse(row["RowCreatedAt"].ToString()) : DateTime.Parse("01/01/1900");
item.RowCreatedBy = !row.IsNull("RowCreatedBy") ? row["RowCreatedBy"].ToString() : "";
item.Roles = !row.IsNull("Roles") ? row["Roles"].ToString() : "";
lst.Add(item);
}
return lst;
}
public DataSourceResult GetPage(DataSourceRequest request, string whereCondition)
{
List<SqlParameter> param = new List<SqlParameter>();
param.Add(new SqlParameter("@Page", request.Page));
param.Add(new SqlParameter("@PageSize", request.PageSize));
param.Add(new SqlParameter("@WhereCondition", whereCondition));
param.Add(new SqlParameter("@Sort", CustomModel.GetSortStringFormRequest(request)));
DataTable dt = new SqlHelper().ExecuteQuery("p_Auth_User_Select_By_Page", param);
var lst = new List<Auth_User>();
foreach (DataRow row in dt.Rows)
{
var item = new Auth_User();
item.UserID = !row.IsNull("UserID") ? row["UserID"].ToString() : "";
item.FullName = !row.IsNull("FullName") ? row["FullName"].ToString() : "";
item.DisplayName = !row.IsNull("DisplayName") ? row["DisplayName"].ToString() : "";
item.Phone = !row.IsNull("Phone") ? row["Phone"].ToString() : "";
item.Email = !row.IsNull("Email") ? row["Email"].ToString() : "";
item.IsActive = !row.IsNull("IsActive") ? Convert.ToBoolean(row["IsActive"]) : false;
item.Note = !row.IsNull("Note") ? row["Note"].ToString() : "";
item.RowCreatedAt = !row.IsNull("RowCreatedAt") ? DateTime.Parse(row["RowCreatedAt"].ToString()) : DateTime.Parse("01/01/1900");
item.RowCreatedBy = !row.IsNull("RowCreatedBy") ? row["RowCreatedBy"].ToString() : "";
item.Roles = !row.IsNull("Roles") ? row["Roles"].ToString() : "";
lst.Add(item);
}
request.Filters = null;
DataSourceResult result = new DataSourceResult();
result.Data = lst;
result.Total = dt.Rows.Count > 0 ? Convert.ToInt32(dt.Rows[0]["RowCount"]) : 0;
return result;
}
protected override void Initialize(System.Web.Routing.RequestContext requestContext)
{
base.Initialize(requestContext);
if (this.User.Identity.IsAuthenticated)
{
IDbConnection dbConn = new OrmliteConnection().openConn();
lstAssetDefault = InitAssetDefault();
currentUser = dbConn.GetByIdOrDefault<Auth_User>(User.Identity.Name);
currentUserRole = dbConn.SqlList<Auth_Role>("EXEC p_Auth_UserInRole_Select_By_UserID @UserID", new { UserID = User.Identity.Name });
string controllerName = this.GetType().Name;
controllerName = controllerName.Substring(0, controllerName.IndexOf("Controller"));
var lstAsset = new List<Auth_Action>();
// Get MenuID from controller name
string menuID = dbConn.SingleOrDefault<Auth_Menu>("ControllerName = {0}", controllerName).MenuID;
foreach (var g in currentUserRole)
{
// Get List Asset
var temp = dbConn.Select<Auth_Action>(p => p.RoleID == g.RoleID && p.MenuID == menuID);
if (temp.Count > 0)
lstAsset.AddRange(temp);
}
if(lstAsset.Count == 0)
{
var item = new Auth_Action();
item.MenuID = menuID;
item.Note = "";
item.RowCreatedAt = DateTime.Now;
item.RowCreatedBy = "System";
if (currentUser.UserID == ConfigurationManager.AppSettings["superadmin"])
{
item.RoleID = 1;
item.IsAllowed = true;
foreach(var asset in lstAssetDefault)
{
item.Action = asset;
dbConn.Insert<Auth_Action>(item);
}
}
else
{
item.RoleID = currentUserRole.FirstOrDefault().RoleID;
item.IsAllowed = false;
foreach (var asset in lstAssetDefault)
{
item.Action = asset;
dbConn.Insert<Auth_Action>(item);
}
}
}
else
{
foreach (var g in currentUserRole)
{
// Asset
var lst = lstAsset.Where(p => p.RoleID == g.RoleID).ToList();
foreach(var item in lst)
{
if (!userAsset.ContainsKey(item.Action))
userAsset.Add(item.Action, item.IsAllowed);
else if(item.IsAllowed)
{
userAsset.Remove(item.Action);
userAsset.Add(item.Action, item.IsAllowed);
}
}
}
}
// Get Asset View Menu
foreach (var g in currentUserRole)
{
var lstView = dbConn.Select<Auth_Action>(p => p.RoleID == g.RoleID && p.Action == "View");
//var lstView = new Auth_Menu().GetMenuByRoleID(g.RoleID);
foreach (var i in lstView)
{
if (!dictView.ContainsKey("menu_" + i.MenuID))
{
if(i.IsAllowed)
{
dictView.Add("menu_" + i.MenuID, true);
}
}
}
}
ViewData["menuView"] = dictView;
dbConn.Close();
}
}
public ActionResult Read([DataSourceRequest]DataSourceRequest request)
{
log4net.Config.XmlConfigurator.Configure();
string whereCondition = "";
if (request.Filters.Count > 0)
{
whereCondition = " AND " + new KendoApplyFilter().ApplyFilter(request.Filters[0]);
}
var data = new Auth_User().GetPage(request, whereCondition);
return Json(data);
}
public FileResult Export([DataSourceRequest]DataSourceRequest request)
{
ExcelPackage pck = new ExcelPackage(new FileInfo(Server.MapPath("~/ExportTemplate/NguoiDung.xlsx")));
ExcelWorksheet ws = pck.Workbook.Worksheets["Data"];
if (userAsset["Export"])
{
string whereCondition = "";
if (request.Filters.Count > 0)
{
whereCondition = " AND " + new KendoApplyFilter().ApplyFilter(request.Filters[0]);
}
IDbConnection db = new OrmliteConnection().openConn();
var lstResult = new Auth_User().GetExport(request, whereCondition);
int rowNum = 2;
foreach (var item in lstResult)
{
ws.Cells["A" + rowNum].Value = item.UserID;
ws.Cells["B" + rowNum].Value = item.DisplayName;
ws.Cells["C" + rowNum].Value = item.FullName;
ws.Cells["D" + rowNum].Value = item.Email;
ws.Cells["E" + rowNum].Value = item.Phone;
ws.Cells["F" + rowNum].Value = item.Note;
ws.Cells["G" + rowNum].Value = item.IsActive ? "Đang hoạt động" : "Ngưng hoạt động";
rowNum++;
}
db.Close();
}
else
{
ws.Cells["A2:E2"].Merge = true;
ws.Cells["A2"].Value = "You don't have permission to export data.";
}
MemoryStream output = new MemoryStream();
pck.SaveAs(output);
return File(output.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"NguoiDung_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
public ActionResult Registry(RegistryModel item)
{
IDbConnection db = new OrmliteConnection().openConn();
try
{
var isExist = db.FirstOrDefault<Auth_User>(p => p.UserID == item.UserName);
item.Phone = !string.IsNullOrEmpty(item.Phone) ? item.Phone : "";
item.Email = !string.IsNullOrEmpty(item.Email) ? item.Email : "";
item.UserName = !string.IsNullOrEmpty(item.UserName) ? item.UserName : "";
if (isExist != null)
return Json(new { success = false, message = "Người dùng đã tồn tại" });
var user = new Auth_User();
user.UserID = item.UserName;
user.DisplayName = item.UserName;
user.Phone = item.Phone;
user.Email = item.Email;
user.IsActive = true;
user.FullName = item.UserName;
user.Password = SqlHelper.GetMd5Hash(item.Password);
user.RowCreatedAt = DateTime.Now;
user.RowCreatedBy = "CustomerRegistry";
user.Note = "";
db.Insert<Auth_User>(user);
var detail = new Auth_UserInRole();
detail.UserID = item.UserName;
detail.RoleID = 3;
detail.RowCreatedAt = DateTime.Now;
detail.RowCreatedBy = "CustomerRegistry";
db.Insert<Auth_UserInRole>(detail);
return Json(new { success = true, message = "Đăng ký thành công" });
}
catch (Exception e)
{
return Json(new { success = false, message = e.Message });
}
finally { db.Close(); }
}
