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();
}
