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