public int AddOrder(OrderObject order) { int newID = -1; int userID = order.userID; bool newUser = false; if (userID < 1) { newUser = true; } try { db.OpenConnection(); if (newUser) { string sql = $"Insert Into tbl_User (Firstname, Lastname, Adress, Postalcode, City, Email, Telephone, Mobilephone, Pricegroup, Company, Admin) Values('{order.firstName}', '{order.lastName}', '{order.adress}', '{order.postalCode}', '{order.city}', '{order.email}', '{order.telephone}', '{order.mobile}', '1', '{order.company}', '0' );SELECT CAST(scope_identity() AS int)"; SqlCommand insertCmd = new SqlCommand(sql, db._connection); userID = (int)insertCmd.ExecuteScalar(); } string sql2 = $"Insert Into tbl_Order (UserID, CarrierID, PaymentID) Values('{userID}', '{order.carrierID}', '{order.paymentID}' );SELECT CAST(scope_identity() AS int)"; SqlCommand insertCmd2 = new SqlCommand(sql2, db._connection); newID = (int)insertCmd2.ExecuteScalar(); foreach (ProductObject product in order.products) { string sql3 = $"Insert Into [tbl_Order_Product-Attribute] (OrderID, ProductAttributeID, Quantity) Values('{newID}', '{product.ID}', '{product.quantity}' )"; SqlCommand insertCmd3 = new SqlCommand(sql3, db._connection); insertCmd3.ExecuteNonQuery(); } } catch { //TODO exeption } finally { db.CloseConnection(); } return(newID); }
public DataTable GetProducts(OrderObject ord) { using (DataTable dt = new DataTable("Products")) { dt.Columns.AddRange(new DataColumn[6] { new DataColumn("ArtNr"), new DataColumn("name"), new DataColumn("attribut"), new DataColumn("price"), new DataColumn("quantity"), new DataColumn("sum") }); foreach (ProductObject product in ord.products) { string attribut = string.Empty; var productDB = new Product(); Dictionary <string, string> atribut = productDB.GetAttribute(product); if (atribut != null) { foreach (KeyValuePair <string, string> atr in atribut) { attribut += atr.Value + " "; } } decimal sum = -1; if (ord.priceGroup == 2) { sum += product.priceB2B; } if (ord.priceGroup == 1) { sum += product.priceB2C; } decimal prodSum = sum * product.quantity; dt.Rows.Add(product.artNr, product.name, attribut, sum.ToString("#.##"), product.quantity, prodSum.ToString("#.##")); } return(dt); } }
public List <ProductObject> GetProductsToList(OrderObject ord) { List <ProductObject> productList = new List <ProductObject>(); foreach (ProductObject product in ord.products) { string attribut = string.Empty; var productDB = new Product(); Dictionary <string, string> atribut = productDB.GetAttribute(product); foreach (KeyValuePair <string, string> atr in atribut) { attribut += atr.Value + " "; } decimal sum = -1; if (ord.priceGroup == 2) { sum += product.priceB2B; } if (ord.priceGroup == 1) { sum += product.priceB2C; } decimal prodSum = sum * product.quantity; productList.Add(product); } return(productList); }
public OrderObject GetOrder(int id) { OrderObject order = new OrderObject(); try { db.OpenConnection(); string sql = @"Select tbl_Order.ID AS[orderID], tbl_User.Firstname AS[firstName], tbl_User.Lastname AS[lastName], tbl_User.Pricegroup AS[priceGroup], " + @"tbl_User.Company AS[company], tbl_User.ID AS [usrID], tbl_User.Adress AS[adress], tbl_User.Postalcode AS[postalCode], tbl_User.City AS[city], " + @"tbl_User.Pricegroup AS[priceGroup], tbl_User.Telephone AS[telephone], tbl_User.Mobilephone AS[mobile], tbl_User.Email AS[email], " + @"tbl_Product_Attribute.PriceB2B AS[priceB2B], tbl_Product_Attribute.PriceB2C AS[priceB2C], tbl_Product_Attribute.ArticleNumber AS[ArtNr], " + @"[tbl_Order_Product-Attribute].Quantity AS[quantity], tbl_Product.Name AS[productName], tbl_User.Pricegroup AS[priceGroup], tbl_Carrier.Carrier AS [carrier], " + @"tbl_Carrier.[Service] AS [carrierSevice], tbl_Carrier.Price AS[carrierPrice], tbl_User.Pricegroup AS[priceGroup], " + @"tbl_Payment.Provider AS [payment], tbl_Payment.[Service] AS [paymentService], tbl_payment.Price AS[paymentPrice], " + @"tbl_Product_Attribute.AttributeID1 AS[attribute1], tbl_Product_Attribute.AttributeID2 AS[attribute2], tbl_Product_Attribute.AttributeID3 AS[attribute3], tbl_Product_Attribute.AttributeID4 AS[attribute4], tbl_Order.Date " + @"From[tbl_Order_Product-Attribute] " + @"INNER JOIN tbl_Order " + @"ON[tbl_Order_Product-Attribute].OrderID = tbl_Order.ID " + @"INNER JOIN tbl_Product_Attribute " + @"ON[tbl_Order_Product-Attribute].ProductAttributeID = tbl_Product_Attribute.ID " + @"INNER JOIN tbl_User " + @"ON tbl_Order.UserID = tbl_User.ID " + @"INNER JOIN tbl_Carrier " + @"ON tbl_Order.CarrierID = tbl_Carrier.ID " + @"INNER JOIN tbl_Payment " + @"ON tbl_Order.PaymentID = tbl_Payment.ID " + @"INNER JOIN tbl_Product " + @"ON tbl_Product_Attribute.ProductID = tbl_Product.ID " + $"WHERE orderID = '{id}'"; SqlCommand myCommand = new SqlCommand(sql, db._connection); using (SqlDataReader myDataReader = myCommand.ExecuteReader()) { while (myDataReader.Read()) { decimal num = -1; order.orderId = int.Parse(myDataReader["orderID"].ToString()); order.priceGroup = int.Parse(myDataReader["priceGroup"].ToString()); order.company = myDataReader["company"].ToString(); order.firstName = myDataReader["firstName"].ToString(); order.lastName = myDataReader["lastName"].ToString(); order.adress = myDataReader["adress"].ToString(); order.postalCode = myDataReader["postalCode"].ToString(); order.city = myDataReader["city"].ToString(); order.date = myDataReader["Date"].ToString(); order.userID = int.Parse(myDataReader["usrID"].ToString()); order.telephone = myDataReader["telephone"].ToString(); order.mobile = myDataReader["mobile"].ToString(); order.email = myDataReader["email"].ToString(); ProductObject prod = new ProductObject(); prod.artNr = myDataReader["ArtNr"].ToString(); prod.name = myDataReader["productName"].ToString(); prod.quantity = int.Parse(myDataReader["quantity"].ToString()); decimal.TryParse(myDataReader["priceB2B"].ToString(), out num); prod.priceB2B = num; decimal.TryParse(myDataReader["priceB2C"].ToString(), out num); prod.priceB2C = num; order.carrier = myDataReader["carrier"].ToString(); order.carrierService = myDataReader["carrierSevice"].ToString(); decimal.TryParse(myDataReader["carrierPrice"].ToString(), out num); order.carrierPrice = num; order.payment = myDataReader["payment"].ToString(); order.paymentService = myDataReader["paymentService"].ToString(); decimal.TryParse(myDataReader["paymentPrice"].ToString(), out num); order.paymentPrice = num; int atr = -1; int.TryParse(myDataReader["attribute1"].ToString(), out atr); prod.attribute1 = atr; int.TryParse(myDataReader["attribute2"].ToString(), out atr); prod.attribute2 = atr; int.TryParse(myDataReader["attribute3"].ToString(), out atr); prod.attribute3 = atr; int.TryParse(myDataReader["attribute4"].ToString(), out atr); prod.attribute4 = atr; order.AddProduct(prod); } } return(order); } catch { return(null); } finally { db.CloseConnection(); } }