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