public JsonResult LineChart()
{
DateTime dt = Convert.ToDateTime(DateTime.Today);
List <ClosingDisplay> lstclose = new List <ClosingDisplay>();
List <tblUserRole> lstur = _db.tblUserRoles.Where(u => u.RoleId == 2).ToList();
foreach (var ur in lstur)
{
tblUser tb = _db.tblUsers.Where(u => u.UserId == ur.UserId).FirstOrDefault();
tblUserBranch tub = _db.tblUserBranches.Where(u => u.UserId == ur.UserId).FirstOrDefault();
tblBranch tbbra = _db.tblBranches.Where(b => b.BranchId == tub.BranchId).FirstOrDefault();
decimal amount = 0M;
List <tblInvoice> lst = _db.tblInvoices.Where(t => t.InvoiceDate == dt).Where(u => u.UserId == ur.UserId).ToList();
if (lst.Count > 0)
{
foreach (var item in lst)
{
amount += Convert.ToDecimal(item.NetAmount);
}
ClosingDisplay cd = new ClosingDisplay();
cd.UserId = ur.UserId;
cd.TotalAmount = amount;
cd.Username = tb.Username;
cd.BranchName = tbbra.BranchName;
cd.BranchId = tbbra.BranchId;
lstclose.Add(cd);
}
}
List <object> iData = new List <object>();
//Creating sample data
DataTable dtt = new DataTable();
dtt.Columns.Add("Username", System.Type.GetType("System.String"));
dtt.Columns.Add("TotalAmount", System.Type.GetType("System.Decimal"));
foreach (var item in lstclose)
{
DataRow dr = dtt.NewRow();
dr["Username"] = item.Username;
dr["TotalAmount"] = item.TotalAmount;
dtt.Rows.Add(dr);
}
//Looping and extracting each DataColumn to List<Object>
foreach (DataColumn dc in dtt.Columns)
{
List <object> x = new List <object>();
x = (from DataRow drr in dtt.Rows select drr[dc.ColumnName]).ToList();
iData.Add(x);
}
//Source data returned as JSON
return(Json(iData, JsonRequestBehavior.AllowGet));
}
public ActionResult MakeClosing()
{
DateTime dt = Convert.ToDateTime(DateTime.Today);
List <ClosingDisplay> lstclose = new List <ClosingDisplay>();
List <tblUserRole> lstur = db.tblUserRoles.Where(u => u.RoleId == 2).ToList();
foreach (var ur in lstur)
{
tblUser tb = db.tblUsers.Where(u => u.UserId == ur.UserId).FirstOrDefault();
bool i = cdb.CheckClosingDate(tb.UserId);
if (!i)
{
tblUserBranch tub = db.tblUserBranches.Where(u => u.UserId == ur.UserId).FirstOrDefault();
tblBranch tbbra = db.tblBranches.Where(b => b.BranchId == tub.BranchId).FirstOrDefault();
decimal amount = 0M;
List <tblInvoice> lst = db.tblInvoices.Where(t => t.InvoiceDate == dt).Where(u => u.UserId == ur.UserId).ToList();
if (lst.Count > 0)
{
foreach (var item in lst)
{
amount += Convert.ToDecimal(item.NetAmount);
}
ClosingDisplay cd = new ClosingDisplay();
cd.UserId = ur.UserId;
cd.TotalAmount = amount;
cd.Username = tb.Username;
cd.BranchName = tbbra.BranchName;
cd.BranchId = tbbra.BranchId;
lstclose.Add(cd);
}
ViewBag.Records = lstclose;
}
}
return(View());
}
