//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;
}
}