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