// Add Food Order this order add by only manager
public void AddFoodOrder(FoodOrderDTO dto)
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[FoodOrder]([StoreId],[OrderDateTime],[CreatedDateTime],[LastUpdateDateTime])VALUES ( "
+ " " + SQLUtility.getInteger(dto.StoreId) + " , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.FoodOrderDate) + "' , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.CreatedDateTime) + "' ,'" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.LastUpdateDateTime) + "' ) ";
db.ExecuteUpdate(query);
}
public JsonResult AddFoodOrder(string[] ids, string[] leftValue, string[] overValue)
{
UserContext cont = (UserContext)Session["UserContext"];
FoodOrderModel fom = new FoodOrderModel();
string error = "";
string msg = "";
try
{
if (!ValidationUtility.IsNull(ids) && ids.Length > 0 && !ValidationUtility.IsNull(cont))
{
FoodOrderDTO foodOrderDTO = new FoodOrderDTO();
foodOrderDTO.StoreId = fom.GetStoredId(cont.Id);
foodOrderDTO.FoodOrderDate = DateTime.Now;
foodOrderDTO.CreatedDateTime = DateTime.Now;
foodOrderDTO.LastUpdateDateTime = DateTime.Now;
fom.AddFoodOrder(foodOrderDTO);
int foodId = fom.GetFoodOrderId(foodOrderDTO);
for (int i = 0; i < ids.Length; i++)
{
int itemId = ValidationUtility.ToInteger(ids[i]);
int left = ValidationUtility.ToInteger(leftValue[i]);
int over = ValidationUtility.ToInteger(overValue[i]);
OrderDTO dto = new OrderDTO();
dto.ItemId = itemId;
dto.NumberRemaining = left;
dto.NumberOver = over;
dto.FoodOrderId = foodId;
dto.CreatedDateTime = DateTime.Now;
dto.LastUpdateDateTime = DateTime.Now;
fom.AddOrder(dto);
}
error = "Success";
msg = "Food Order Added Successfully";
}
}
catch (Exception ex)
{
error = "Error";
msg = "Food Order is not Added Please Try Again";
log.Error(" Exception in AddFoodItem Method ", ex);
}
ResponseDTO resDTO = new ResponseDTO { Error = error, Message = msg };
return Json(resDTO, JsonRequestBehavior.AllowGet);
}
//Get Orders List by admin
public ArrayList GetOrdersList(DateTime selectDate, int storeId)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
ArrayList list = new ArrayList();
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate);
DateTime weekEndDate = weekStartDate.AddDays(6);
try
{
// string query = "select o.Id,o.FoodOrderId,o.ItemId,o.NumberRemaining,o.NumberOver,o.CreatedDateTime,fi.FoodName from dbo.Orders o,dbo.FoodItem fi WHERE o.ItemId=fi.Id and o.CreatedDateTime BETWEEN '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' AND '" + SQLUtility.FormateDateYYYYMMDD(weekEndDate) + "' ";
// string query = "select Id,FoodOrderId,ItemId,NumberRemaining,NumberOver,CreatedDateTime from dbo.Orders WHERE CreatedDateTime BETWEEN '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' AND '" + SQLUtility.FormateDateYYYYMMDD(weekEndDate) + "'";
//string query = " select fi.FoodName, fd.Id,fd.OrderDateTime ,o.NumberRemaining,o.NumberOver from dbo.FoodOrder fd , dbo.Orders o ,dbo.FoodItem fi where "
// + " fd.Id = o.FoodOrderId and fi.Id = o.ItemId and fd.StoreId = " + SQLUtility.getInteger(storeId) + " and fd.OrderDateTime BETWEEN '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' AND '" + SQLUtility.FormateDateYYYYMMDD(weekEndDate) + "' ";
string query = "";
if (storeId == 0)
{
//query = " select fd.Id,fd.StoreId,fd.OrderDateTime from dbo.FoodOrder fd where fd.OrderDateTime "
// + " BETWEEN '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' AND '" + SQLUtility.FormateDateYYYYMMDD(weekEndDate) + "' ";
query = " select fd.Id,fd.StoreId,fd.OrderDateTime from dbo.FoodOrder fd where fd.OrderDateTime "
+ " BETWEEN '" + SQLUtility.FormateDateYYYYMMDDWtithTime(new DateTime(weekStartDate.Year, weekStartDate.Month, weekStartDate.Day, 0, 0, 0)) + "' AND '" + SQLUtility.FormateDateYYYYMMDDWtithTime(new DateTime(weekEndDate.Year, weekEndDate.Month, weekEndDate.Day,23,59,59)) + "' ";
}
else
{
query = " select fd.Id,fd.StoreId,fd.OrderDateTime from dbo.FoodOrder fd where fd.StoreId = " + SQLUtility.getInteger(storeId) + " "
+ " and fd.OrderDateTime BETWEEN '" + SQLUtility.FormateDateYYYYMMDDWtithTime(new DateTime(weekStartDate.Year, weekStartDate.Month, weekStartDate.Day, 0, 0, 0)) + "' AND '" + SQLUtility.FormateDateYYYYMMDDWtithTime(new DateTime(weekEndDate.Year, weekEndDate.Month, weekEndDate.Day, 23, 59, 59)) + "' ";
}
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int foodOrderId = ValidationUtility.ToInteger(reader["Id"].ToString());
int sId = ValidationUtility.ToInteger(reader["StoreId"].ToString());
DateTime orderDate = ValidationUtility.ToDate(reader["OrderDateTime"].ToString());
//string itemName = reader["FoodName"].ToString();
//DateTime createdDateTime = ValidationUtility.ToDate(reader["OrderDateTime"].ToString());
//int numberRemaining = ValidationUtility.ToInteger(reader["NumberRemaining"].ToString());
//int numberOver = ValidationUtility.ToInteger(reader["NumberOver"].ToString());
//OrderDTO dto = new OrderDTO { FoodOrderId = foodOrderId, NumberRemaining = numberRemaining, NumberOver = numberOver, CreatedDateTime = createdDateTime, OrderDateToString = createdDateTime.ToString("MM/dd/yyyy"), ItemName = itemName };
FoodOrderDTO dto = new FoodOrderDTO { Id = foodOrderId, StoreId = sId, OrderDateInString = orderDate.ToString("MM/dd/yyyy") };
list.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetOrdersList Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return list;
}
//Get Orders List by Corporate
public ArrayList GetFoodOrdersListByCorporate(DateTime selectDate, int userId)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
ArrayList list = new ArrayList();
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate);
DateTime weekEndDate = weekStartDate.AddDays(6);
try
{
//string query = " select fd.Id,fd.StoreId,fd.OrderDateTime from dbo.FoodOrder fd where fd.StoreId in (select su.StoreId from dbo.StoreUser su where su.UserId =" + SQLUtility.getInteger(userId) + ") "
// + " and fd.OrderDateTime BETWEEN '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' AND '" + SQLUtility.FormateDateYYYYMMDD(weekEndDate) + "' ";
string query = " select fd.Id,fd.StoreId,fd.OrderDateTime from dbo.FoodOrder fd where fd.StoreId in (select su.StoreId from dbo.StoreUser su where su.UserId =" + SQLUtility.getInteger(userId) + ") "
+ " and fd.OrderDateTime BETWEEN '" + SQLUtility.FormateDateYYYYMMDDWtithTime(new DateTime(weekStartDate.Year, weekStartDate.Month, weekStartDate.Day, 0, 0, 0)) + "' AND '" + SQLUtility.FormateDateYYYYMMDDWtithTime(new DateTime(weekEndDate.Year, weekEndDate.Month, weekEndDate.Day, 23, 59, 59)) + "' ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int foodOrderId = ValidationUtility.ToInteger(reader["Id"].ToString());
int sId = ValidationUtility.ToInteger(reader["StoreId"].ToString());
DateTime orderDate = ValidationUtility.ToDate(reader["OrderDateTime"].ToString());
FoodOrderDTO dto = new FoodOrderDTO { Id = foodOrderId, StoreId = sId, OrderDateInString = orderDate.ToString("MM/dd/yyyy") };
list.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetOrdersList Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return list;
}
// Get Food Order Id
public int GetFoodOrderId(FoodOrderDTO dto)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
int id = 0;
try
{
//string query = " select Id from [dbo].[FoodOrder] where StoreId = " + SQLUtility.getInteger(dto.StoreId) + " and OrderDateTime = '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.FoodOrderDate) + "' ";
string query = "select MAX(id) from dbo.FoodOrder where StoreId = " + SQLUtility.getInteger(dto.StoreId) + " ";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
id = ValidationUtility.ToInteger(reader[0].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetFoodOrderId Method ", ex);
}
finally
{
db.CloseConnection(conn);
}
return id;
}