//View for Register Movements public ActionResult RegisterMovement() { if (Session["accountOnline"] == null) { return RedirectToAction("Index", "Home"); } else { //Only load movtypes if nor yet in memory if(Session["movementTypes"] == null) GetMovementTypes(); AccountDataModel acc = (AccountDataModel)Session["accountOnline"]; //Get all movements of Account Online using (SGFEntitiess accMovements = new SGFEntitiess()) { var result = from ac in accMovements.SGF_T_ACCOUNT_MOVEMENT join mt in accMovements.SGF_R_MOVEMENT_TYPE on ac.MOV_TYPE_ID equals mt.MOV_TYPE_ID join c in accMovements.SGF_R_CATEGORY on ac.CAT_ID equals c.CAT_ID join sc in accMovements.SGF_R_SUB_CATEGORY on ac.SUB_CAT_ID equals sc.SUB_CAT_ID where ac.ACCOUNT_ID == acc.Account_Id orderby ac.CREATED_DATE descending select new { ac.MOV_ID, ac.ACCOUNT_ID, MOV_TYPE_DES = mt.DESCRIPTION, CAT_DES = c.DESCRIPTION, SUB_CAT_DES = sc.DESCRIPTION, ac.AMOUNT, ac.DESCRIPTION, ac.CREATED_DATE }; List<AccountMovementModel> lstResult = new List<AccountMovementModel>(); foreach (var obj in result.ToList()) { AccountMovementModel amm = new AccountMovementModel(); amm.MovId = obj.MOV_ID; amm.AccountId = obj.ACCOUNT_ID; amm.MovTypeDes = obj.MOV_TYPE_DES; amm.CatDes = obj.CAT_DES; amm.SubCatDes = obj.SUB_CAT_DES; amm.Amount = (double)obj.AMOUNT; amm.Description = obj.DESCRIPTION; amm.CreatedDate = obj.CREATED_DATE; lstResult.Add(amm); } Session["accountOnlineAllMovements"] = lstResult; return View(); } } }
//Return only the top 10 movements order by Date descending public void GetTop10Movements(int AccountId) { using (SGFEntitiess accMovements = new SGFEntitiess()) { /*SELECT mt.DESCRIPTION MOV_TYPE_DES, c.DESCRIPTION CAT_DES, sc.DESCRIPTION SUB_CAT_DES, ac.AMOUNT, ac.DESCRIPTION, CONVERT(VARCHAR(19),ac.CREATED_DATE,120) CREATED_DATE FROM SGF_T_ACCOUNT_MOVEMENT ac, SGF_R_MOVEMENT_TYPE mt, SGF_R_CATEGORY c, SGF_R_SUB_CATEGORY sc WHERE ac.MOV_TYPE_ID = mt.MOV_TYPE_ID AND c.CAT_ID = ac.CAT_ID AND sc.SUB_CAT_ID = ac.SUB_CAT_ID AND ac.ACCOUNT_ID = 2*/ var result = (from ac in accMovements.SGF_T_ACCOUNT_MOVEMENT join mt in accMovements.SGF_R_MOVEMENT_TYPE on ac.MOV_TYPE_ID equals mt.MOV_TYPE_ID join c in accMovements.SGF_R_CATEGORY on ac.CAT_ID equals c.CAT_ID join sc in accMovements.SGF_R_SUB_CATEGORY on ac.SUB_CAT_ID equals sc.SUB_CAT_ID where ac.ACCOUNT_ID == AccountId orderby ac.CREATED_DATE descending select new { ac.MOV_ID, ac.ACCOUNT_ID, MOV_TYPE_DES = mt.DESCRIPTION, CAT_DES = c.DESCRIPTION, SUB_CAT_DES = sc.DESCRIPTION, ac.AMOUNT, ac.DESCRIPTION, ac.CREATED_DATE}).Take(10); List<AccountMovementModel> lstResult = new List<AccountMovementModel>(); //foreach (var obj in result.ToList().Take(10)) foreach (var obj in result.ToList()) { AccountMovementModel amm = new AccountMovementModel(); amm.MovId = obj.MOV_ID; amm.AccountId = obj.ACCOUNT_ID; amm.MovTypeDes = obj.MOV_TYPE_DES; amm.CatDes = obj.CAT_DES; amm.SubCatDes = obj.SUB_CAT_DES; amm.Amount = (double)obj.AMOUNT; amm.Description = obj.DESCRIPTION; amm.CreatedDate = obj.CREATED_DATE; lstResult.Add(amm); } Session["accountOnlineMovements"] = lstResult; } }