public void Delect(eSaleModel.Order AllData) { string SqlData = "Delect "; string Where = "a.OrderID = " + AllData; DoData(SqlData, Where, AllData); }
public ActionResult GetUpdateDialog(int id) { eSalesService.Order2Service orderService = new eSalesService.Order2Service(this.GetDBConnectionString()); eSalesService.EmpService empService = new eSalesService.EmpService(this.GetDBConnectionString()); eSalesService.CusService cusService = new eSalesService.CusService(this.GetDBConnectionString()); eSalesService.ShipService shipService = new eSalesService.ShipService(this.GetDBConnectionString()); eSaleModel.Order orderData = new eSaleModel.Order(); var data = orderService.GetOrderById(id); ViewBag.OrderId = data.OrderId; ViewBag.OrderDate = data.OrderDate; ViewBag.RequiredDate = data.RequiredDate; ViewBag.ShippedDate = data.ShippedDate; ViewBag.Freight = data.Freight; ViewBag.ShipCountry = data.ShipCountry; ViewBag.ShipCity = data.ShipCity; ViewBag.ShipRegion = data.ShipRegion; ViewBag.ShipPostalCode = data.ShipPostalCode; ViewBag.ShipAddress = data.ShipAddress; ViewBag.ShipName = data.ShipAddress; ViewBag.EmpId = data.EmpId; ViewBag.CustId = data.CustId; ViewBag.ShipperId = data.ShipperId; //ViewBag.OrderId = "updateProductGrid" + id; //ViewBag.EmpNameData = new SelectList(empService.GetEmpNameData(), "Value", "Text", orderData.EmpId); //ViewBag.CustNameData = new SelectList(cusService.GetCusNameData(), "Value", "Text", orderData.CustId); //ViewBag.ShipperNameData = new SelectList(shipService.GetShipperNameData(), "Value", "Text", orderData.ShipperId); return(PartialView()); }
public List <eSaleModel.Order> DoData(string SqlData, string Where, eSaleModel.Order AllData) { DataTable dt = new DataTable(); string Sql = @SqlData + "from Sales.Orders a inner join Sales.Customers b " + "on b.CustomerID = a.CustomerID " + "inner join HR.Employees c " + "on c.EmployeeID = a.EmployeeID " + "inner join Sales.Shippers d " + "on d.ShipperID = a.ShipperID " + Where; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBconn"].ConnectionString); using (conn) { conn.Open(); SqlCommand cmd = new SqlCommand(Sql, conn); SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); sqlAdapter.Fill(dt); conn.Close(); } List <eSaleModel.Order> Data = new List <eSaleModel.Order>(); Data = MapOrderDataToList(dt); return(Data); }
/// <summary> /// 依篩選條件取得訂單資料 /// </summary> /// <param name="condition"></param> /// <returns></returns> public DataTable GetOrderByCondition(eSaleModel.Order condition) { DataTable dt = new DataTable(); string sql = @"SELECT * FROM Sales.Orders AS a JOIN Sales.Customers AS b ON a.CustomerID =b.CustomerID WHERE (CAST(OrderId as CHAR) LIKE @OrderId OR @OrderId IS NULL) AND (b.CompanyName LIKE @CustName OR @CustName IS NULL ) AND (EmployeeId = @EmpId OR @EmpId = 0 ) AND (ShipperId=@ShipperId OR @ShipperId = 0 ) AND (OrderDate=@OrderDate OR @OrderDate = '1780-01-01' ) AND (RequiredDate=@RequiredDate OR @RequiredDate = '1780-01-01' ) AND (ShippedDate=@ShippedDate OR @ShippedDate = '1780-01-01' ) "; using (SqlConnection conn = new SqlConnection(this.DbConn)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@OrderId", condition.O_OrderId == null ? string.Empty : condition.O_OrderId)); cmd.Parameters.Add(new SqlParameter("@CustName", condition.CustName == null ? string.Empty : condition.CustName)); cmd.Parameters.Add(new SqlParameter("@EmpId", condition.EmpId == null ? 0 : condition.EmpId)); cmd.Parameters.Add(new SqlParameter("@ShipperId", condition.ShipperId == null ? 0 : condition.ShipperId)); cmd.Parameters.Add(new SqlParameter("@OrderDate", condition.OrderDate == null ? Convert.ToDateTime("1780-01-01") : condition.OrderDate)); cmd.Parameters.Add(new SqlParameter("@RequiredDate", condition.RequiredDate == null ? Convert.ToDateTime("1780-01-01") : condition.RequiredDate)); cmd.Parameters.Add(new SqlParameter("@ShippedDate", condition.ShippedDate == null ? Convert.ToDateTime("1780-01-01") : condition.ShippedDate)); SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); sqlAdapter.Fill(dt); conn.Close(); } return(dt); }
public JsonResult InsertOrder(eSaleModel.Order orderData) { var orderService = new eSalesService.OrderService(this.GetDBConnectionString()); try { var result = new eSaleModel.Order(); result.CustId = Int32.Parse(orderData.CustName); result.EmpId = Int32.Parse(orderData.EmpName); result.OrderDate = orderData.OrderDate; result.RequiredDate = orderData.RequiredDate; result.ShippedDate = orderData.ShippedDate; result.ShipperId = Int32.Parse(orderData.ShipperName); result.Freight = orderData.Freight; result.ShipCountry = orderData.ShipCountry; result.ShipCity = orderData.ShipCity; result.ShipRegion = orderData.ShipRegion; result.ShipPostalCode = orderData.ShipPostalCode; result.ShipAddress = orderData.ShipAddress; var error = new eSaleModel.ViewModel.ErrorMsg(); error.Orderid = orderService.InsertOrder(result); error.State = true; return(this.Json(error, JsonRequestBehavior.AllowGet)); }catch (Exception e) { var error = new eSaleModel.ViewModel.ErrorMsg(); error.Describe = "尚未填寫完成"; error.State = false; return(this.Json(error, JsonRequestBehavior.AllowGet)); } }
public List <eSaleModel.Order> GetOrderByCondition(eSaleModel.Order condition) { List <eSaleModel.Order> result = new List <eSaleModel.Order>(); eSaleDao.OrderDao orderDao = new eSaleDao.OrderDao(this.DbConn); var datalist = orderDao.GetOrderByCondition(condition); return(this.MapOrderStore(datalist)); }
public JsonResult GetOrderById(int id) { var result = new eSaleModel.Order(); var orderService = new eSalesService.OrderService(this.GetDBConnectionString()); result = orderService.GetOrderById(id); return(this.Json(result, JsonRequestBehavior.AllowGet)); }
public JsonResult GetOrder() { var result = new eSaleModel.Order(); var orderService = new eSalesService.OrderService(this.GetDBConnectionString()); var store = new eSaleModel.Store(); result.OrderId = 0; result.CustName = "%" + null + "%"; result.EmpId = 0; result.ShipperId = 0; result.OrderDate = null; result.RequiredDate = null; result.ShippedDate = null; store.identifier = "OrderId"; store.items = orderService.GetOrderByCondition(result); return(this.Json(store, JsonRequestBehavior.AllowGet)); }
public JsonResult InsertOrderDetail(eSaleModel.ViewModel.ProductDetailWithId data) { var orderService = new eSalesService.OrderService(this.GetDBConnectionString()); eSaleModel.ViewModel.SearchOrderGrid searchOrderGrid = new eSaleModel.ViewModel.SearchOrderGrid(); eSaleModel.Order orderData = new eSaleModel.Order(); try { eSaleModel.OrderDetails orderDetail = new eSaleModel.OrderDetails(); orderDetail.OrderId = data.id; orderDetail.ProductId = Convert.ToInt16(data.items[0].ProductName[0]); orderDetail.UnitPrice = data.items[0].UnitPrice[0]; orderDetail.Qty = data.items[0].Qty[0]; orderService.InsertOrderDetail(orderDetail); if (data.items.Count > 1) { for (int i = 1; i < data.items.Count; i++) { var orderService2 = new eSalesService.OrderService(this.GetDBConnectionString()); eSaleModel.OrderDetails orderDetail2 = new eSaleModel.OrderDetails(); orderDetail2.OrderId = data.id; orderDetail2.ProductId = Convert.ToInt16(data.items[0]._S._arrayOfAllItems[i].ProductName[0]); orderDetail2.UnitPrice = data.items[0]._S._arrayOfAllItems[i].UnitPrice[0]; orderDetail2.Qty = data.items[0]._S._arrayOfAllItems[i].Qty[0]; orderService2.InsertOrderDetail(orderDetail2); } } searchOrderGrid.State = true; searchOrderGrid.Order = orderService.GetOrderById(data.id); return(this.Json(searchOrderGrid)); } catch (Exception e) { var error = new eSaleModel.ViewModel.ErrorMsg(); error.Orderid = data.id; error.State = false; return(this.Json(error)); } }
public JsonResult GetOrderByCondition(eSaleModel.Order condition) { var result = new eSaleModel.Order(); var orderService = new eSalesService.Order2Service(this.GetDBConnectionString()); var store = new eSaleModel.Store(); result.O_OrderId = "%" + Convert.ToString(condition.OrderId) + "%"; result.CustName = "%" + (condition.CustName) + "%"; result.EmpId = condition.EmpName == null ? 0 : Int32.Parse(condition.EmpName); result.ShipperId = condition.ShipperName == null ? 0 : Int32.Parse(condition.ShipperName); result.OrderDate = condition.OrderDate; result.RequiredDate = condition.RequiredDate; result.ShippedDate = condition.ShippedDate; var data = orderService.GetOrderByCondition(result); return(this.Json(data, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 修改訂單資料 /// </summary> /// <param name="order"></param> /// <returns></returns> public int UpdateOrder(eSaleModel.Order order) { string sql = @" Update Sales.Orders SET CustomerID=@CustomerID, EmployeeID=@EmployeeID, orderdate=@OrderDate, requireddate=@Requireddate, shippeddate=@Shippeddate, shipperid=@Shipperid, freight=@Freight, shipname=@Shipname, shipaddress=@Shipaddress, shipcity=@Shipcity, shipregion=@Shipregion, shippostalcode=@Shippostalcode, shipcountry=@Shipcountry WHERE OrderId=@OrderId "; int orderId; using (SqlConnection conn = new SqlConnection(this.DbConn)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@CustomerID", order.CustId)); cmd.Parameters.Add(new SqlParameter("@EmployeeID", order.EmpId)); cmd.Parameters.Add(new SqlParameter("@OrderDate", order.OrderDate)); cmd.Parameters.Add(new SqlParameter("@RequireDdate", order.RequiredDate)); cmd.Parameters.Add(new SqlParameter("@ShippedDate", order.ShippedDate)); cmd.Parameters.Add(new SqlParameter("@ShipperId", order.ShipperId)); cmd.Parameters.Add(new SqlParameter("@Freight", order.Freight)); cmd.Parameters.Add(new SqlParameter("@Shipname", order.ShipName)); cmd.Parameters.Add(new SqlParameter("@ShipAddress", order.ShipAddress)); cmd.Parameters.Add(new SqlParameter("@ShipCity", order.ShipCity)); cmd.Parameters.Add(new SqlParameter("@ShipRegion", order.ShipRegion == null ? "" : order.ShipRegion)); cmd.Parameters.Add(new SqlParameter("@ShipPostalCode", order.ShipPostalCode)); cmd.Parameters.Add(new SqlParameter("@ShipCountry", order.ShipCountry)); cmd.Parameters.Add(new SqlParameter("@OrderId", order.OrderId)); Console.WriteLine(cmd); var aa = cmd.ExecuteNonQuery(); return(aa); } }
/// <summary> /// 新增訂單資料 /// </summary> /// <param name="order"></param> /// <returns></returns> public int InsertOrder(eSaleModel.Order order) { string sql = @" Insert INTO Sales.Orders ( CustomerID,EmployeeID,orderdate,requireddate,shippeddate,shipperid,freight, shipname,shipaddress,shipcity,shipregion,shippostalcode,shipcountry ) OUTPUT INSERTED.OrderID VALUES ( @CustomerID,@EmployeeID,@OrderDate,@Requireddate,@Shippeddate,@Shipperid,@Freight, @Shipname,@Shipaddress,@Shipcity,@Shipregion,@Shippostalcode,@Shipcountry ) " ; int orderId; using (SqlConnection conn = new SqlConnection(this.DbConn)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@CustomerID", order.CustId)); cmd.Parameters.Add(new SqlParameter("@EmployeeID", order.EmpId)); cmd.Parameters.Add(new SqlParameter("@OrderDate", order.OrderDate)); cmd.Parameters.Add(new SqlParameter("@RequireDdate", order.RequiredDate)); cmd.Parameters.Add(new SqlParameter("@ShippedDate", order.ShippedDate)); cmd.Parameters.Add(new SqlParameter("@ShipperId", order.ShipperId)); cmd.Parameters.Add(new SqlParameter("@Freight", order.Freight)); cmd.Parameters.Add(new SqlParameter("@Shipname", "aa")); cmd.Parameters.Add(new SqlParameter("@ShipAddress", order.ShipAddress)); cmd.Parameters.Add(new SqlParameter("@ShipCity", order.ShipCity)); cmd.Parameters.Add(new SqlParameter("@ShipRegion", order.ShipRegion)); cmd.Parameters.Add(new SqlParameter("@ShipPostalCode", order.ShipPostalCode)); cmd.Parameters.Add(new SqlParameter("@ShipCountry", order.ShipCountry)); Console.WriteLine(cmd); object aa = cmd.ExecuteScalar(); orderId = Convert.ToInt32(aa); conn.Close(); } return(orderId); }
/// <summary> /// 取得DB連線 /// </summary> /// <returns></returns> public List <eSaleModel.Order> Inquire(eSaleModel.Order AllData) { string SqlData = "Select OrderID, CustName, OrderDate, ShippedDate "; string Where = "Where "; if (AllData.OrderID != null) { Where += "a.OrderID = " + AllData.OrderID + " and "; } if (AllData.CustName != null) { Where += "CustName = 'Customer IBVRG'" + " and "; } if (!AllData.EmpName.Equals("null")) { Where += "a.EmployeeID = " + AllData.EmpName + " and "; } if (!AllData.CpyName.Equals("null")) { Where += "d.ShipperID = " + AllData.CpyName + " and "; } if (AllData.OrderDate != null) { Where += "OrderDate = '" + AllData.OrderDate.Substring(0, 10) + "' and "; } if (AllData.RequiredDate != null) { Where += "RequiredDate = '" + AllData.RequiredDate.Substring(0, 10) + "' and "; } if (AllData.ShippedDate != null) { Where += "ShippedDate = '" + AllData.ShippedDate.Substring(0, 10) + "' and "; } if (Where.Equals("Where ")) { Where = ""; } List <eSaleModel.Order> Data = DoData(SqlData, (Where.Substring(0, Where.Length - 5)), AllData); return(Data); }
public JsonResult UpdateOrder(eSaleModel.Order orderData) { var orderService = new eSalesService.OrderService(this.GetDBConnectionString()); try { var result = new eSaleModel.Order(); result.OrderId = orderData.OrderId; result.CustId = Int32.Parse(orderData.CustName); result.EmpId = Int32.Parse(orderData.EmpName); result.OrderDate = orderData.OrderDate; result.RequiredDate = orderData.RequiredDate; result.ShippedDate = orderData.ShippedDate; result.ShipperId = Int32.Parse(orderData.ShipperName); result.Freight = orderData.Freight; result.ShipCountry = orderData.ShipCountry; result.ShipCity = orderData.ShipCity; result.ShipRegion = orderData.ShipRegion; result.ShipPostalCode = orderData.ShipPostalCode; result.ShipAddress = orderData.ShipAddress; result.ShipName = orderData.ShipName; var error = new eSaleModel.ViewModel.ErrorMsg(); error.State = true; error.Orderid = orderService.UpdateOrder(result); //var aa = orderService.UpdateOrder(result); return(this.Json(error, JsonRequestBehavior.AllowGet)); } catch (Exception e) { var error = new eSaleModel.ViewModel.ErrorMsg(); //var msg = Convert.ToString(e); //var start = msg.IndexOf("參數化查詢"); //var end = msg.IndexOf("必須有參數"); //error.Describe = msg.Substring(start+8,end-start-9); error.Describe = "尚未填寫完成"; error.State = false; return(this.Json(error, JsonRequestBehavior.AllowGet)); } }
/// <summary> /// 新增訂單資料 /// </summary> /// <param name="data">訂單資料</param> /// <returns></returns> public int UpdateOrder(eSaleModel.Order data) { eSaleDao.OrderDao orderDao = new eSaleDao.OrderDao(this.DbConn); return(orderDao.UpdateOrder(data)); }