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