//Get all child menu by user id and parent menu id (Access and Menu is active, this user has pemission to access) public List <IdentityMenu> GetChildMenuByUserId(string UserId, int ParentId) { //List<IdentityMenu> listMenu = new List<IdentityMenu>(); //using (var conn = new SqlConnection(_connectionString)) //{ // var parameters = new Dictionary<string, object> // { // {"@UserId", UserId}, // {"@ParentId",ParentId} // }; // using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.Text, // @"SELECT DISTINCT e.* FROM aspnetuserroles a // LEFT JOIN aspnetaccessroles b ON a.RoleId = b.RoleId // LEFT JOIN aspnetoperations c ON b.OperationId = c.Id // LEFT JOIN aspnetaccess d ON c.AccessId = d.Id // RIGHT JOIN aspnetmenus e ON (e.Action = c.ActionName AND e.Controller = d.AccessName) // AND (e.Action IS NOT NULL AND e.Controller IS NOT NULL) // WHERE a.UserId = @UserId AND d.Active = 1 AND e.Active = 1 and e.ParentId = @ParentId // ORDER BY e.SortOrder // ", parameters)) // { // while (reader.Read()) // { // listMenu.Add(ParsingMenuFromReader(reader)); // } // } //} //return listMenu.AsQueryable<IdentityMenu>(); //Common syntax var sqlCmd = @"Menu_GetChildMenuByUserId"; List <IdentityMenu> listMenu = new List <IdentityMenu>(); var parameters = new Dictionary <string, object> { { "@UserId", UserId }, { "@ParentId", ParentId } }; try { using (var conn = new SqlConnection(_connectionString)) { using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters)) { while (reader.Read()) { listMenu.Add(ParsingMenuFromReader(reader)); } if (listMenu != null && listMenu.Count > 0) { //All languages if (reader.NextResult()) { while (reader.Read()) { var langItem = ParsingMenuLangFromReader(reader); foreach (var item in listMenu) { if (item.Id == langItem.MenuId) { item.LangList.Add(langItem); } } } } } } } } catch (Exception ex) { var strError = "Failed to execute Menu_GetChildMenuByUserId. Error: " + ex.Message; throw new CustomSQLException(strError); } return(listMenu); }
public List <IdentityWarehouseActivity> GetHistoryByPage(IdentityWarehouseActivity filter, int currentPage, int pageSize) { //Common syntax var sqlCmd = @"Warehouse_Activity_GetByPage"; List <IdentityWarehouseActivity> listData = null; //For paging int offset = (currentPage - 1) * pageSize; //For parameters var parameters = new Dictionary <string, object> { { "@ProductId", filter.ProductId }, { "@StaffId", filter.StaffId }, { "@Keyword", filter.Keyword }, { "@ActivityType", filter.ActivityType }, { "@DeviceId", filter.DeviceId }, { "@FromDate", filter.FromDate }, { "@ToDate", filter.ToDate }, { "@Offset", offset }, { "@PageSize", pageSize } }; try { using (var conn = new SqlConnection(_connectionString)) { using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters)) { listData = new List <IdentityWarehouseActivity>(); while (reader.Read()) { var item = ExtractWarehouseActivityData(reader); listData.Add(item); } if (listData.Count > 0) { if (reader.NextResult()) { while (reader.Read()) { var productInfo = RpsProduct.ExtractProductData(reader); foreach (var item in listData) { if (productInfo.Id == item.ProductId) { item.ProductInfo = productInfo; } } } } } } } } catch (Exception ex) { var strError = "Failed to execute Warehouse_Activity_GetByPage. Error: " + ex.Message; throw new CustomSQLException(strError); } return(listData); }