public List <ViewSalesModel> GetAllViewSales(string fromDate, string toDate) { connection = new SqlConnection(connectionString); string query = "SELECT Item.Item_Name, Company.Company_Name, SUM(StockOut.StockOut_Quantity)StockOut_Quantity " + "FROM StockOut " + "INNER JOIN Item " + "on StockOut.Item_Id = Item.Item_Id AND StockOut.Date BETWEEN '" + fromDate + "' AND '" + toDate + "' AND StockOut.Action='Sell' " + "INNER JOIN Company on Company.Company_Id = StockOut.Company_Id AND StockOut.Date BETWEEN '" + fromDate + "' AND '" + toDate + "' AND StockOut.Action='Sell'" + "GROUP BY Item.Item_Name,Company.Company_Name"; command = new SqlCommand(query, connection); connection.Open(); reader = command.ExecuteReader(); List <ViewSalesModel> itemListViewModel = new List <ViewSalesModel>(); while (reader.Read()) { ViewSalesModel itemViewModel = new ViewSalesModel(); itemViewModel.Item_Name = reader["Item_Name"].ToString(); itemViewModel.Company_Name = reader["Company_Name"].ToString(); itemViewModel.StockOut_Quantity = Convert.ToInt32(reader["StockOut_Quantity"]); itemListViewModel.Add(itemViewModel); } connection.Close(); return(itemListViewModel); }
protected void searchButton_Click(object sender, EventArgs e) { if (fromDateCalender.SelectedDate.Date == DateTime.MinValue.Date && toDateCalender.SelectedDate.Date == DateTime.MinValue.Date) { ClientScript.RegisterStartupScript(this.GetType(), "Oops!", "VaidationMsg1()", true); } else { DateTime d1 = fromDateCalender.SelectedDate; DateTime d2 = toDateCalender.SelectedDate; if (d1 <= d2) { ViewSalesModel viewSalesModel = new ViewSalesModel(); viewSalesModel.FromDate = fromDateCalender.SelectedDate.ToString("dd-MM-yyyy"); viewSalesModel.ToDate = toDateCalender.SelectedDate.ToString("dd-MM-yyyy"); allSellsItemGridView.DataSource = viewSalesManager.GetAllSellsByDate(viewSalesModel); allSellsItemGridView.DataBind(); } else { ClientScript.RegisterStartupScript(this.GetType(), "Oops!", "VaidationMsg()", true); allSellsItemGridView.DataSource = null; allSellsItemGridView.DataBind(); } } }
public List <ViewSalesModel> GetAllSellsByDate(ViewSalesModel viewSalesModel) { command = new SqlCommand("SELECT * FROM SellsInfo WHERE Date BETWEEN @fromDate AND @toDate ORDER BY ReorderLevel", connection); command.Parameters.AddWithValue("@fromDate", viewSalesModel.FromDate); command.Parameters.AddWithValue("@toDate", viewSalesModel.ToDate); connection.Open(); reader = command.ExecuteReader(); List <ViewSalesModel> itemList = new List <ViewSalesModel>(); while (reader.Read()) { ViewSalesModel aItem = new ViewSalesModel(); aItem.ItemId = Convert.ToInt32(reader["ItemId"]); aItem.ItemName = reader["ItemName"].ToString(); aItem.CompanyId = Convert.ToInt32(reader["CompanyId"]); aItem.CompanyName = reader["CompanyName"].ToString(); aItem.CategoryId = Convert.ToInt32(reader["CategoryId"]); aItem.CategoryName = reader["CategoryName"].ToString(); aItem.ReorderLevel = Convert.ToInt32(reader["ReorderLevel"]); aItem.SellQuantity = Convert.ToInt32(reader["SellQuanity"]); itemList.Add(aItem); } connection.Close(); return(itemList); }
public List <ViewSalesModel> GetAllSellsByDate(ViewSalesModel viewSalesModel) { return(viewSalesGateway.GetAllSellsByDate(viewSalesModel)); }