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 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 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 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 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 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 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 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 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 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 GetAllStatuses() { string query = "SELECT * FROM OrderStatus"; return(SQL_CRUD_Methods.SQLRead(query)); }
public DataTable GetAllUsers() { string query = "SELECT ID, Name, Role, Active FROM Users"; 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 GetReportData(string _query) { return(SQL_CRUD_Methods.SQLRead(_query)); }
public DataTable GetAllReportCharts() { string query = "SELECT * FROM Chart"; return(SQL_CRUD_Methods.SQLRead(query)); }
public DataTable GetAllDeliveryLines() { string query = "SELECT * FROM Delivery_Product"; return(SQL_CRUD_Methods.SQLRead(query)); }