public static void PopApprovalData(ref Models.RequestApproval approval)
{
var approvalQuery = "SELECT * FROM " + approval.TableName + " WHERE " + SibiApprovalColumns.UID + " ='" + approval.GUID + "'";
approval = new Models.RequestApproval(DBFactory.GetDatabase().DataTableFromQueryString(approvalQuery));
//var itemsQuery = "SELECT * FROM " + SibiRequestItemsCols.TableName + " WHERE " + SibiRequestItemsCols.ApprovalID + " = '" + approval.GUID + "'";
var approvalItemsQuery = "SELECT * FROM " + SibiApprovalItemsColumns.TableName + " WHERE " + SibiApprovalItemsColumns.ApprovalUID + " = '" + approval.GUID + "'";
using (var approvalItemsTable = DBFactory.GetDatabase().DataTableFromQueryString(approvalItemsQuery))
{
if (approvalItemsTable.Rows.Count > 0)
{
approval.ApprovalItems = new Models.ApprovalItem[approvalItemsTable.Rows.Count];
// approval.SibiRequestItems = new Models.SibiRequestItem[itemsTable.Rows.Count];
for (int i = 0; i < approvalItemsTable.Rows.Count; i++)
{
//approval.SibiRequestItems[i] = new Models.SibiRequestItem(approvalItemsTable.Rows[i]);
approval.ApprovalItems[i] = new Models.ApprovalItem(approvalItemsTable.Rows[i]);
}
}
}
var requestQuery = "SELECT * FROM " + SibiRequestCols.TableName + " WHERE " + SibiRequestCols.UID + " = '" + approval.SibiRequestUID + "'";
using (var requestTable = DBFactory.GetDatabase().DataTableFromQueryString(requestQuery))
{
approval.SibiRequest = new Models.SibiRequest(requestTable);
}
//approval.MapClassProperties(DBFactory.GetDatabase().DataTableFromQueryString(itemsQuery));
//approval.MapClassProperties(DBFactory.GetDatabase().DataTableFromQueryString("SELECT * FROM " + SibiRequestItemsCols.TableName + " WHERE " + SibiRequestItemsCols.ItemUID + " = '" + approval.SibiRequestUID + "'"));
}
public IActionResult MyApprovals(Models.RequestApproval r, string approverId)
{
//var m = (Models.RequestApproval)ViewBag.Approval;
Console.WriteLine(r.ApproverID);
ViewBag.ApprovalsTable = DBFunctions.GetApprovalsTable(approverId);
return(View(r));
}
// [HttpGet]
public IActionResult Approval(string id)
{
ViewData["id"] = id;
ViewData["state"] = "prepost";
Models.RequestApproval approval = new Models.RequestApproval(id);
//MySQLCommsOLD sqlContext = HttpContext.RequestServices.GetService(typeof(MySQLCommsOLD)) as MySQLCommsOLD;
//sqlContext.PopRequestData(approval);
DBFunctions.PopApprovalData(ref approval);
return(View(approval));
}
public static bool ApproveRequest(Models.RequestApproval request)
{
var database = DBFactory.GetDatabase();
bool isApproved = false;
var appVal = Convert.ToString(database.ExecuteScalarFromQueryString("SELECT " + SibiApprovalColumns.Status + " FROM " + request.TableName + " WHERE " + SibiApprovalColumns.UID + " ='" + request.GUID + "'"));
isApproved = (appVal == ApprovalStatus.approved.ToString());
if (!isApproved)
{
using (var trans = database.StartTransaction())
{
try
{
var approveQry = "UPDATE " + request.TableName + " SET " + SibiApprovalColumns.Status + " ='" + ApprovalStatus.approved.ToString() + "' WHERE " + SibiApprovalColumns.UID + " ='" + request.GUID + "'";
using (var cmd = database.GetCommand(approveQry))
{
// int affectedRows = DBFactory.GetDatabase().ExecuteQuery(approveQry);
int affectedRows = database.ExecuteQuery(cmd, trans);
// If the command returned affected rows, return true for a success.
if (affectedRows > 0)
{
if (!SetRequestItemsCurrent(request, trans))
{
return(false);
}
}
}
AddNewNotification(NotificationType.ACCEPTED, trans, request.GUID);
database.CommitTransaction(trans);
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
database.RollbackTransaction(trans);
return(false);
}
}
}
// The request is already approved or no rows were affected, return false for error.
return(false);
}
private static bool SetRequestItemsCurrent(Models.RequestApproval request, DbTransaction transaction)
{
var approvalItemsQuery = "SELECT " + SibiApprovalItemsColumns.RequestItemUID + " FROM " + SibiApprovalItemsColumns.TableName + " WHERE " + SibiApprovalItemsColumns.ApprovalUID + " = '" + request.GUID + "'";
using (var approvalItems = DBFactory.GetDatabase().DataTableFromQueryString(approvalItemsQuery))
{
int affectedRows = 0;
foreach (DataRow item in approvalItems.Rows)
{
var itemsQry = "UPDATE " + SibiRequestItemsCols.TableName +
" SET " + SibiRequestItemsCols.ModifyStatus + " ='" + ItemChangeStatus.MODCURR.ToString() + "' WHERE " + SibiRequestItemsCols.ItemUID + " = '" + item[SibiApprovalItemsColumns.RequestItemUID] + "'";
using (var cmd = DBFactory.GetDatabase().GetCommand(itemsQry))
{
affectedRows += DBFactory.GetDatabase().ExecuteQuery(cmd, transaction);
}
}
if (affectedRows > 0)
{
return(true);
}
return(false);
}
//var itemsQry = "UPDATE " + request.TableName +
// " SET " + SibiRequestItemsCols.ModifyStatus + " ='" + ItemChangeStatus.MODCURR.ToString() + "' WHERE " + SibiRequestItemsCols.ItemUID + " = '" + request.GUID + "'";
// int affectedRows = DBFactory.GetDatabase().ExecuteQuery(itemsQry);
//if (affectedRows > 0)
//{
// return true;
//}
//return false;
}
public ActionResult Approval(Models.RequestApproval r, string response)
{
Console.WriteLine("Approve CLicked! " + response);
ViewData["state"] = "posted";
ViewData["response"] = response;
// ViewBag.Approval = r;
if (response == "accept")
{
DBFunctions.PopApprovalData(ref r);
bool success = DBFunctions.ApproveRequest(r);//sqlContext.ApproveRequest(r.GUID);
r.PostSuccess = success;
return(View(r));
}
else if (response == "reject")
{
DBFunctions.PopApprovalData(ref r);
bool success = DBFunctions.RejectRequest(r);//sqlContext.ApproveRequest(r.GUID);
r.PostSuccess = success;
return(View(r));
}
return(View(r));
}