static void Example7() { List <MonthlySales> result = MonthlySales.GetMonthlySales() .SelectMany(x => x.Sales, (MonthlySalesObj, SalesObj) => new { MonthlySalesObj.Month, SalesObj } ).GroupBy(x => new { x.Month, ProductName = x.SalesObj.Key }) .Select(x => new MonthlySales { Month = x.Key.Month, Sales = new Dictionary <string, double> { { x.Key.ProductName, x.Sum(z => z.SalesObj.Value) } } }).ToList(); }
public IActionResult GetMonthlySales() { var salesOrders = _salesService.GetSalesInvoices().Where(a => a.GeneralLedgerHeaderId != null); IList <Dto.Sales.MonthlySales> monthlySalesDto = new List <Dto.Sales.MonthlySales>(); IList <Dto.Sales.MonthlySales> finalmonthlySalesDto = new List <Dto.Sales.MonthlySales>(); foreach (var item in salesOrders) { foreach (var line in item.SalesInvoiceLines) { var dtoSales = new MonthlySales(); dtoSales.Month = item.Date.Month.ToString(); dtoSales.Amount = line.Amount * line.Quantity; monthlySalesDto.Add(dtoSales); } } var totalSales = monthlySalesDto.ToList().GroupBy(a => a.Month) .Select(ms => new MonthlySales { Month = ms.First().Month, Amount = ms.Sum(x => x.Amount), }).ToList(); for (int i = 1; i <= DateTime.Now.Month; i++) { var sales = new MonthlySales(); var month = i + "/1/" + DateTime.Now.Year; sales.Month = Convert.ToDateTime(month).ToString("MMMMM"); sales.Amount = totalSales.Where(a => a.Month == i.ToString()).Select(x => x.Amount).FirstOrDefault(); finalmonthlySalesDto.Add(sales); } return(Json(finalmonthlySalesDto)); }
public IActionResult Create(MonthlySales obj) { _db.MonthlySalesFigure.Add(obj); _db.SaveChanges(); return(RedirectToAction("MonthlySales")); }
static void Example8() { var result = MonthlySales.GetMonthlySales().SelectMany((x, i) => x.Sales.Select(z => z.Value + i)); }
protected void Page_Load(object sender, EventArgs e) { string today = "SELECT Item_Name, Item_Price, qty, createdate FROM Order_Detail WHERE createdate >='" + DateTime.Today.ToString() + "'"; string todaysale = "SELECT SUM(Item_Price*qty) as ts from Order_Detail WHERE createdate >='" + DateTime.Today.ToString() + "'"; string week = "SELECT Item_Name, Item_Price, qty, createdate FROM Order_Detail WHERE createdate BETWEEN '" + DateTime.Today.ToString() + "' and '" + DateTime.Now.AddDays(-7) + "'"; string weeksale = "SELECT SUM(Item_Price*qty) as ts1 from Order_Detail WHERE createdate BETWEEN '" + DateTime.Today.ToString() + "' and '" + DateTime.Now.AddDays(-7) + "'"; string month = "SELECT Item_Name, Item_Price, qty, createdate FROM Order_Detail WHERE createdate BETWEEN '" + DateTime.Today.ToString() + "' and '" + DateTime.Now.AddDays(-30) + "'"; string monthsale = "SELECT SUM(Item_Price*qty) as ts2 from Order_Detail WHERE createdate BETWEEN '" + DateTime.Today.ToString() + "' and '" + DateTime.Now.AddDays(-30) + "'"; string yearly = "SELECT Item_Name, Item_Price, qty, createdate FROM Order_Detail WHERE createdate BETWEEN '" + DateTime.Today.ToString() + "' and '" + DateTime.Now.AddYears(-1) + "'"; string yearsale = "SELECT SUM(Item_Price*qty) as ts3 from Order_Detail WHERE createdate BETWEEN '" + DateTime.Today.ToString() + "' and '" + DateTime.Now.AddYears(-1) + "'"; // string q2="select sum(Item_Price*qty) as tds from Order_Detail where createdate="+DateTime.Today; SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbstring"].ConnectionString); //daily con.Open(); SqlCommand cmd = new SqlCommand(today, con); SqlDataAdapter adp = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adp.Fill(dt); todaysales.DataSource = dt; todaysales.DataBind(); SqlCommand cmdd = new SqlCommand(todaysale, con); SqlDataAdapter adpp = new SqlDataAdapter(cmdd); DataTable dtt = new DataTable(); adpp.Fill(dtt); if (dtt.Rows.Count == 1) { Label2.Text = dtt.Rows[0]["ts"].ToString(); } //weekly SqlCommand cmd1 = new SqlCommand(week, con); SqlDataAdapter adp1 = new SqlDataAdapter(cmd1); DataTable dt1 = new DataTable(); adp.Fill(dt1); WeeklySales.DataSource = dt1; WeeklySales.DataBind(); SqlCommand c1 = new SqlCommand(weeksale, con); SqlDataAdapter a1 = new SqlDataAdapter(c1); DataTable d1 = new DataTable(); a1.Fill(d1); if (d1.Rows.Count == 1) { Label4.Text = d1.Rows[0]["ts1"].ToString(); } //monthlly SqlCommand cmd2 = new SqlCommand(month, con); SqlDataAdapter adp2 = new SqlDataAdapter(cmd2); DataTable dt2 = new DataTable(); adp.Fill(dt2); MonthlySales.DataSource = dt; MonthlySales.DataBind(); SqlCommand c2 = new SqlCommand(monthsale, con); SqlDataAdapter a2 = new SqlDataAdapter(c2); DataTable d2 = new DataTable(); a2.Fill(d2); if (d2.Rows.Count == 1) { Label6.Text = d2.Rows[0]["ts2"].ToString(); } //yearly SqlCommand cmd3 = new SqlCommand(yearly, con); SqlDataAdapter adp3 = new SqlDataAdapter(cmd3); DataTable dt3 = new DataTable(); adp.Fill(dt3); YearlySales.DataSource = dt3; YearlySales.DataBind(); SqlCommand c3 = new SqlCommand(yearsale, con); SqlDataAdapter a3 = new SqlDataAdapter(c3); DataTable d3 = new DataTable(); a3.Fill(d3); if (d3.Rows.Count == 1) { Label8.Text = d3.Rows[0]["ts3"].ToString(); } con.Close(); }