public ActionResult ReportEdit(int id)
{
REPORTS _report = database.REPORTS.Find(id);
ViewBag.RoleId = new SelectList(database.ROLES.OrderBy(p => p.RoleDescription), "Role_Id", "RoleName");
return(View(_report));
}
public ActionResult ReportEdit(REPORTS _report)
{
if (ModelState.IsValid)
{
_report.LastModified = System.DateTime.Now;
database.Entry(_report).State = EntityState.Modified;
database.SaveChanges();
return(RedirectToAction("ReportDetails", new RouteValueDictionary(new { id = _report.Report_Id })));
}
return(View(_report));
}
public PartialViewResult AddRole2ReportReturnPartialView(int id, int reportId)
{
ROLES role = database.ROLES.Find(id);
REPORTS _report = database.REPORTS.Find(reportId);
if (!role.REPORTS.Contains(_report))
{
role.REPORTS.Add(_report);
database.SaveChanges();
}
return(PartialView("_ListRolesTable4Report", _report));
}
public PartialViewResult DeleteRoleFromReportReturnPartialView(int id, int reportId)
{
ROLES role = database.ROLES.Find(id);
REPORTS report = database.REPORTS.Find(reportId);
if (role.REPORTS.Contains(report))
{
role.REPORTS.Remove(report);
database.SaveChanges();
}
return(PartialView("_ListRolesTable4Report", report));
}
public ActionResult ReportDelete(int id)
{
REPORTS _report = database.REPORTS.Find(id);
if (_report.ROLES.Count > 0)
{
_report.ROLES.Clear();
}
database.Entry(_report).State = EntityState.Deleted;
database.SaveChanges();
return(RedirectToAction("ReportIndex"));
}
public PartialViewResult AddAllRoles2ReportReturnPartialView(int id)
{
REPORTS _report = database.REPORTS.Where(p => p.Report_Id == id).FirstOrDefault();
List <ROLES> _roles = database.ROLES.ToList();
foreach (ROLES _role in _roles)
{
if (!_role.REPORTS.Contains(_report))
{
_role.REPORTS.Add(_report);
_role.LastModified = DateTime.Now;
}
}
database.SaveChanges();
return(PartialView("_ListRolesTable4Report", _report));
}
public ActionResult ReportCreate(REPORTS _report)
{
if (_report.ReportDescription == null)
{
ModelState.AddModelError("Report Description", "Report Description must be entered");
}
if (ModelState.IsValid)
{
_report.LastModified = System.DateTime.Now;
database.REPORTS.Add(_report);
database.SaveChanges();
return(RedirectToAction("ReportIndex"));
}
return(View(_report));
}
public PartialViewResult AddReport2RoleReturnPartialView(int roleId, int reportId)
{
ViewBag.roleId = roleId;
REPORTS _report = database.REPORTS.Where(p => p.Report_Id == reportId).FirstOrDefault();
ROLES _role = database.ROLES.Where(p => p.Role_Id == roleId).FirstOrDefault();
if (_role != null)
{
if (!_role.REPORTS.Contains(_report))
{
_role.REPORTS.Add(_report);
database.Entry(_role).State = EntityState.Modified;
database.SaveChanges();
}
}
return(PartialView("_ListReportsTable4Role", _role.REPORTS.ToList()));
}
public PartialViewResult DeleteReportParameterReturnPartialView(int reportId, int paramId)
{
try
{
REPORTS _report = database.REPORTS.Find(reportId);
PARAMETERS _filter = database.PARAMETERS.Find(paramId);
if (_report.PARAMETERS.Contains(_filter))
{
_report.PARAMETERS.Remove(_filter);
database.Entry(_report).State = EntityState.Modified;
database.PARAMETERS.Remove(_filter);
database.SaveChanges();
}
}
catch (Exception)
{
}
return(PartialView("_ListParamsTable4Report", database.REPORTS.Where(p => p.Report_Id == reportId).FirstOrDefault()));
}
public PartialViewResult AddReportParameterReturnPartialView(int reportId, string paramName, string paramType, string paramLabel, bool paramRequired)
{
try
{
REPORTS _report = database.REPORTS.Find(reportId);
//Create new parameter object and assign values...
PARAMETERS _reportParameter = new PARAMETERS();
_reportParameter.ParameterName = paramName;
_reportParameter.ParameterType = paramType;
_reportParameter.DisplayLabel = paramLabel;
_reportParameter.Required = paramRequired;
_report.PARAMETERS.Add(_reportParameter);
database.Entry(_report).State = EntityState.Modified;
database.SaveChanges();
}
catch (Exception)
{
}
return(PartialView("_ListParamsTable4Report", database.REPORTS.Where(p => p.Report_Id == reportId).FirstOrDefault()));
}
public PartialViewResult DeleteReportReturnPartialView(int id)
{
REPORTS _report = database.REPORTS.Find(id);
if (_report.ROLES.Count > 0)
{
_report.ROLES.Clear();
}
if (_report.PARAMETERS.Count > 0)
{
_report.PARAMETERS.Clear();
}
database.Entry(_report).State = EntityState.Modified;
database.SaveChanges();
database.REPORTS.Remove(_report);
database.Entry(_report).State = EntityState.Deleted;
database.SaveChanges();
return(PartialView("_ListReportsTable", database.REPORTS.ToList()));
}
public static List <dynamic> ExecuteStoredProcedure(REPORTS _report, string _rawParams, Controller _controller)
{
List <dynamic> _dataRows = new List <dynamic>();
try
{
using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["RBAC_Model"].ConnectionString))
{
using (var command = new SqlCommand(_report.StoredProcedureName, sqlConn)
{
CommandType = CommandType.StoredProcedure
})
{
//Read parameters...
string[] _parameters = _rawParams.Split('\\');
foreach (string _param in _parameters)
{
try
{
string _paramName = _param.Substring(0, _param.IndexOf('=')).Trim();
string _paramValue = _param.Substring(_param.IndexOf('=') + 1).Trim();
if (!string.IsNullOrEmpty(_paramValue))
{
dynamic _paramSqlValue = _paramValue;
switch (_report.PARAMETERS.Where(p => p.ParameterName == _paramName).FirstOrDefault().ParameterType.ToLower())
{
case "datetime":
_paramSqlValue = FormatDate4SqlStoredProcedureParameter(DateTime.ParseExact(_paramValue, "MM/dd/yyyy", CultureInfo.InvariantCulture));
break;
case "date":
_paramSqlValue = FormatDate4SqlStoredProcedureParameter(DateTime.ParseExact(_paramValue, "MM/dd/yyyy", CultureInfo.InvariantCulture));
break;
case "int":
_paramSqlValue = int.Parse(_paramValue);
break;
default:
_paramSqlValue = _paramValue;
break;
}
command.Parameters.Add(new SqlParameter(_paramName, _paramSqlValue));
}
}
catch (Exception ex)
{
}
}
sqlConn.Open();
SqlDataReader dbReader = command.ExecuteReader();
while (dbReader.Read())
{
DynamicDataRow _row = new DynamicDataRow();
for (int i = 0; i < dbReader.FieldCount; i++)
{
_row.AddColumn(dbReader.GetName(i), GetValue(dbReader, i), dbReader.GetDataTypeName(i).ToString());
}
_dataRows.Add(_row);
}
sqlConn.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(_dataRows);
}
public ViewResult ReportDetails(int id)
{
REPORTS _reports = database.REPORTS.Find(id);
return(View(_reports));
}
