public DataTable GetAllOrders(int _customerID) { string query = "SELECT * FROM Orders WHERE CustomerID = @CustomerID"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@CustomerID", _customerID), }; return(SQL_CRUD_Methods.SQLRead(query, parameterlist)); }
public DataRow GetDeliveryByID(int _deliveryID) { string query = "SELECT * FROM Delivery WHERE ID = @DeliveryID"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@DeliveryID", _deliveryID) }; return(SQL_CRUD_Methods.SQLRead(query, parameterlist).Rows[0]); }
public DataTable GetAllDeliveryLinesForDelivery(int _deliveryID) { string query = "SELECT * FROM Delivery_Product WHERE DeliveryID = @DeliveryID"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@DeliveryID", _deliveryID) }; return(SQL_CRUD_Methods.SQLRead(query, parameterlist)); }
public DataRow GetOrderByID(int _orderID) { string query = "SELECT * FROM Orders WHERE ID = @OrderID"; List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@OrderID", _orderID) }; return(SQL_CRUD_Methods.SQLRead(query, parameters).Rows[0]); }
public DataRow GetProductByID(int _productID) { string query = "SELECT * FROM Product WHERE ID = @ProductID"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@ProductID", _productID) }; return(SQL_CRUD_Methods.SQLRead(query, parameterlist).Rows[0]); }
public int RegisterNewOrder(int _customerID) { string query = "INSERT INTO [Orders](CustomerID) VALUES (@CustomerID); SELECT SCOPE_IDENTITY();"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@CustomerID", _customerID) }; return(SQL_CRUD_Methods.SQLInsert(query, parameterlist)); }
public DataRow GetUserByID(int _userID) { string query = "SELECT ID, Name, Role, Active FROM Users WHERE ID = @UserID"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@UserID", _userID) }; return(SQL_CRUD_Methods.SQLRead(query, parameterlist).Rows[0]); }
public DataTable GetAllProducts(int _orderID) { string query = ""; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@OrderID", _orderID) }; return(SQL_CRUD_Methods.SQLRead(query, parameterlist)); }
public DataTable GetOrderStatusesForOrder(int _orderID) { string query = "SELECT * FROM OrderStatus WHERE OrderID = @OrderID"; List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@OrderID", _orderID) }; return(SQL_CRUD_Methods.SQLRead(query, parameters)); }
public DataTable GetAllOrderLinesForOrder(int _orderID) { string query = "SELECT * FROM Order_Product WHERE OrderID = @OrderID"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@OrderID", _orderID) }; return(SQL_CRUD_Methods.SQLRead(query, parameterlist)); }
public int GetNewDelivery() { string query = "INSERT INTO Delivery(DateTime,ExternalID) VALUES (@DateTime,@ExternalID); SELECT SCOPE_IDENTITY()"; List <KeyValuePair <string, Object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@DateTime", DateTime.Now), new KeyValuePair <string, object>("@ExternalID", "X") }; return(SQL_CRUD_Methods.SQLInsert(query, parameterlist)); }
public int UpdateAmountInStock(Product _product, int _amount) { string query = "DECLARE @Difference int SET @Difference = (SELECT AmountInStock FROM Product WHERE ID = @ProductID) + @AmountDelivered IF @Difference < 0 SET @AmountDelivered = @AmountDelivered - @Difference; UPDATE Product SET AmountInStock = AmountInStock + @AmountDelivered WHERE ID = @ProductID SELECT @AmountDelivered AS 'Result'"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@AmountDelivered", _amount), new KeyValuePair <string, object>("@ProductID", _product.ID) }; return(SQL_CRUD_Methods.SQLUpdateReturnInt(query, parameterlist)); }
public void RemoveOrderLineFromOrder(int _prodID, int _orderID) { string query = "DELETE FROM[Order_Product] WHERE ProductID = @ProductID AND OrderID = @OrderID"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@OrderID", _orderID), new KeyValuePair <string, object>("@ProductID", _prodID), }; SQL_CRUD_Methods.SQLDelete(query, parameterlist); }
public void UpdateOrder(Order _order) { string query = "UPDATE Orders SET CustomerID = @CustomerID WHERE ID = @OrderID"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@CustomerID", _order.Customer.ID), new KeyValuePair <string, object>("@OrderID", _order.ID) }; SQL_CRUD_Methods.SQLUpdate(query, parameterlist); }
public bool AddOrderLineToOrder(OrderLine _orderLine, int _orderID) { string query = "INSERT INTO Order_Product(OrderID, ProductID, Amount) VALUES (@OrderID, @ProductID, @Amount)"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@OrderID", _orderID), new KeyValuePair <string, object>("@ProductID", _orderLine.Product.ID), new KeyValuePair <string, object>("@Amount", _orderLine.Amount) }; return(SQL_CRUD_Methods.SQLInsertBoolReturn(query, parameterlist)); }
public bool AddProductToOrder(int _orderID, int _productID, int _productAmount) { string query = "INSERT INTO [Order_Product](OrderID, ProductID, Amount) VALUES (@OrderID, @ProductID, @Amount)"; List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@OrderID", _orderID), new KeyValuePair <string, object>("@ProductID", _productID), new KeyValuePair <string, object>("@Amount", _productAmount) }; return(SQL_CRUD_Methods.SQLInsertBoolReturn(query, parameters)); }
public void FurtherOrderStatus(int _orderID, OrderStatus.OrderStatusesEnum _orderStatus) { string query = "INSERT INTO [OrderStatus](Status, DateTime, OrderID) VALUES (@Status, @DateTime, @OrderID)"; List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@Status", _orderStatus), new KeyValuePair <string, object>("@DateTime", DateTime.Now), new KeyValuePair <string, object>("@OrderID", _orderID) }; SQL_CRUD_Methods.SQLInsertBoolReturn(query, parameters); }
public void EditOrderedAmount(int _prodID, int _orderID, int _amount) { string query = "UPDATE [dbo].[Order_Product] SET Amount = @Amount WHERE OrderID = @OrderID AND ProductID = @ProductID"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@OrderID", _orderID), new KeyValuePair <string, object>("@ProductID", _prodID), new KeyValuePair <string, object>("@Amount", _amount) }; SQL_CRUD_Methods.SQLUpdate(query, parameterlist); }
public bool SaveDeliveryLine(int _deliveryID, DeliveryLine _deliveryLine) { string query = "INSERT INTO Delivery_Product(DeliveryID,ProductID,Amount) VALUES (@DeliveryID,@ProductID,@Amount)"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@DeliveryID", _deliveryID), new KeyValuePair <string, object>("@ProductID", _deliveryLine.Product.ID), new KeyValuePair <string, object>("@Amount", _deliveryLine.Amount) }; return(SQL_CRUD_Methods.SQLInsertBoolReturn(query, parameterlist)); }
public bool SaveOrderStatusForOrder(OrderStatus _orderStatus) { string query = "INSERT INTO [OrderStatus](Status, DateTime, OrderID) VALUES (@Status, @DateTime, @OrderID)"; List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@Status", _orderStatus.RegisteredStatus), new KeyValuePair <string, object>("@DateTime", _orderStatus.RegisteredDate), new KeyValuePair <string, object>("@OrderID", _orderStatus.OrderID) }; return(SQL_CRUD_Methods.SQLInsertBoolReturn(query, parameters)); }
public void EditProduct(Product _product) { string query = "UPDATE Product SET Volume = @Volume, VolumeType = @VolumeType, Active = @Active WHERE ID = @ProductID"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@ProductID", _product.ID), new KeyValuePair <string, object>("@Volume", _product.Volume), new KeyValuePair <string, object>("@VolumeType", _product.VolumeType), new KeyValuePair <string, object>("@Active", _product.Active) }; SQL_CRUD_Methods.SQLUpdate(query, parameterlist); }
public int SaveNewProduct(Product _product) { string query = "INSERT INTO Product(Type,Name,Volume,VolumeType,AmountInStock,Active) VALUES (@Type,@Name,@Volume,@VolumeType,@AmountInStock,@Active)"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@Type", _product.VolumeType), new KeyValuePair <string, object>("@Name", _product.Name), new KeyValuePair <string, object>("@Volume", _product.Volume), new KeyValuePair <string, object>("@VolumeType", _product.VolumeType), new KeyValuePair <string, object>("@AmountInStock", 0), new KeyValuePair <string, object>("@Active", _product.Active) }; return(SQL_CRUD_Methods.SQLInsert(query, parameterlist)); }
public DataTable GetOrderedReport() { string query = @"DECLARE @ColumnNames NVARCHAR(MAX) = '' DECLARE @SQL NVARCHAR(MAX) = '' SELECT @ColumnNames += QUOTENAME(ID) + ',' FROM Product SET @ColumnNames = LEFT(@ColumnNames,LEN(@ColumnNames) -1) SET @SQL = 'SELECT * FROM ( SELECT p.ID AS ProductNaam, sq1.Name AS [Gebruiker], op.Amount FROM Product AS p INNER JOIN Order_Product AS op ON p.ID = op.ProductID INNER JOIN (SELECT O.ID, U.Name, MAX(OS.Status) AS Status FROM Orders AS O INNER JOIN Users AS U ON o.CustomerID = u.ID INNER JOIN OrderStatus AS OS ON o.ID = os.OrderID GROUP BY O.ID, U.Name ) AS SQ1 ON op.OrderID = SQ1.ID WHERE SQ1.Status != 4 AND SQ1.Status != 0 ) AS BaseData PIVOT ( SUM(Amount) FOR ProductNaam IN (' + @ColumnNames + ') ) AS PivotTable' EXECUTE(@SQL)"; DataTable result = SQL_CRUD_Methods.SQLRead(query); DataRow _dr; foreach (DataColumn _dc in result.Columns) { try { _dr = new ProductSQLQuery().GetProductByID(Convert.ToInt16(_dc.ColumnName)); _dc.ColumnName = Regex.Replace(_dr.Field <string>("Name"), "([a-z?])[_ ]?([A-Z])", "$1 $2") + " " + _dr.Field <decimal>("Volume") + " " + _dr.Field <string>("VolumeType"); } catch (Exception exc) { } } return(result); }
public void UpdateDelivery(Delivery _delivery) { string query = "UPDATE Delivery SET DateTime = @DateTime, ExternalID = @ExternalID WHERE ID = @DeliveryID"; string extID = _delivery.ExternalID; if (_delivery.ExternalID == null || _delivery.ExternalID == "") { extID = "X"; } List <KeyValuePair <string, Object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@DateTime", DateTime.Now), new KeyValuePair <string, object>("@ExternalID", extID), new KeyValuePair <string, object>("@DeliveryID", _delivery.ID) }; SQL_CRUD_Methods.SQLUpdate(query, parameterlist); }
public static Tuple <string, bool> ValidatePassword(string password, string _username) { string query = "SELECT [Password] FROM [Users] WHERE [Mail] = @User"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@User", _username) }; DataRow queryResult = SQL_CRUD_Methods.SQLRead(query, parameterlist).Rows[0]; string storedHash = queryResult.Field <string>("Password"); if (storedHash == null) { storedHash = GetRandomSalt(); } Tuple <string, bool> result = new Tuple <string, bool>(storedHash, BCrypt.Net.BCrypt.CheckPassword(password, storedHash)); return(result); }
public DataTable CheckForOpenOrder(int _userID) { string query = @"SELECT Q.Max_Status AS [Status] ,MIN(Q.OrderID) AS OrderID, Q.CustomerID FROM ( SELECT MAX (Status) AS [Max_Status], OrderID, O.CustomerID FROM OrderStatus AS OS INNER JOIN (SELECT * FROM Orders) AS O ON OS.OrderID = O.ID WHERE CustomerID = @UserID Group BY OrderID, O.CustomerID ) AS Q WHERE q.Max_Status = 0 GROUP BY Q.Max_Status, Q.CustomerID"; List <KeyValuePair <string, object> > parameterlist = new List <KeyValuePair <string, object> > { new KeyValuePair <string, object>("@UserID", _userID), }; return(SQL_CRUD_Methods.SQLRead(query, parameterlist)); }
public DataTable GetAllOrderLines() { string query = "SELECT * FROM Order_Product"; return(SQL_CRUD_Methods.SQLRead(query)); }
public DataTable GetReportData(string _query) { return(SQL_CRUD_Methods.SQLRead(_query)); }
public DataTable GetAllRelevantOrders() { string query = "SELECT t1.* FROM Orders t1 INNER JOIN (SELECT MAX( Status ) AS max_total, OrderID FROM OrderStatus GROUP BY OrderID) t2 ON t1.ID = t2.OrderID WHERE t2.max_total > 0 AND t2.max_total < 3"; return(SQL_CRUD_Methods.SQLRead(query)); }
public DataTable GetAllStatuses() { string query = "SELECT * FROM OrderStatus"; return(SQL_CRUD_Methods.SQLRead(query)); }