public DataTable GetDataTable(string sqlValue, /*
* MySqlParameter[] dic*/Array dic = null)
{
DataTable dt = new DataTable();
using (DbReport context = new DbReport())
{
try
{
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = sqlValue;
cmd.CommandType = CommandType.Text;
DbDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
if (dic != null && dic.Length > 0)
{
cmd.Parameters.AddRange(dic);
}
da.Fill(dt);
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
context.Database.Connection.Close();
}
}
return(dt);
}
public async Task StoreReportAsync(DbReport dbReport)
{
var connection = _redis.GetDatabase();
var serializedReport = JsonConvert.SerializeObject(dbReport);
await connection.StringSetAsync(dbReport.ReportId, serializedReport);
}
public DataSet GetDataSet(string sqlValue)
{
DataSet ds = new DataSet();
using (DbReport context = new DbReport())
{
try
{
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = sqlValue;
cmd.CommandType = CommandType.Text;
DbDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
context.Database.Connection.Close();
}
}
return(ds);
}
public int SqlQueryInt(string sql, object[] para)
{
using (DbReport context = new DbReport())
{
return(context.Database.SqlQuery <int>(sql, para).FirstOrDefault());
}
}
/// <summary>
/// 获取当前 sql 运行受影响行数
/// </summary>
/// <param name="sqlValue"></param>
/// <returns></returns>
public int ROWCOUNT(string sqlValue)
{
using (DbReport context = new DbReport())
{
return(context.Database.SqlQuery <int>(string.Format("select count(*) from ({0} limit 1 )a", sqlValue)).FirstOrDefault());
}
}
public DataSet GetDataSet(string sql, object[] para)
{
DataSet ds = new DataSet();
using (DbReport context = new DbReport())
{
try
{
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
DbDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
if (para != null && para.Length > 0)
{
cmd.Parameters.AddRange(para);
}
da.Fill(ds);
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
context.Database.Connection.Close();
}
}
return(ds);
}
public void TestReviewsCompletedNotActionable()
{
var report = new DbReport
{
Feedbacks = new List <DbReportFeedback>
{
new DbReportFeedback {
FeedbackId = 1, Feedback = new DbFeedback {
IsActionable = true
}
},
new DbReportFeedback {
FeedbackId = 2, Feedback = new DbFeedback {
IsActionable = false
}
},
},
RequiredFeedback = 2,
AllowedFeedback = new List <DbReportAllowedFeedback> {
new DbReportAllowedFeedback()
}
};
ReportProcessor.ProcessReport(report);
Assert.AreEqual(true, report.RequiresReview);
Assert.AreEqual(false, report.Conflicted);
}
public int ExecuteSqlCommand(string sql, object[] para)
{
using (DbReport context = new DbReport())
{
return(context.Database.ExecuteSqlCommand(sql, para));
}
}
public void TestInvalidatedConflicting()
{
var report = new DbReport
{
Feedbacks = new List <DbReportFeedback>
{
new DbReportFeedback {
FeedbackId = 1, InvalidatedDate = DateTime.UtcNow, Feedback = new DbFeedback {
IsActionable = true
}
},
new DbReportFeedback {
FeedbackId = 2, Feedback = new DbFeedback {
IsActionable = true
}
}
},
RequiredFeedback = 2,
AllowedFeedback = new List <DbReportAllowedFeedback> {
new DbReportAllowedFeedback()
}
};
ReportProcessor.ProcessReport(report);
Assert.AreEqual(true, report.RequiresReview);
Assert.AreEqual(false, report.Conflicted);
}
public void TestConflictBumpsRequiredReviews()
{
var report = new DbReport
{
Feedbacks = new List <DbReportFeedback>
{
new DbReportFeedback {
FeedbackId = 1, Feedback = new DbFeedback {
IsActionable = true
}
},
new DbReportFeedback {
FeedbackId = 1, Feedback = new DbFeedback {
IsActionable = true
}
},
new DbReportFeedback {
FeedbackId = 2, Feedback = new DbFeedback {
IsActionable = true
}
}
},
ConflictExceptions = new List <DbConflictException>(),
RequiredFeedback = 2,
RequiredFeedbackConflicted = 4,
AllowedFeedback = new List <DbReportAllowedFeedback> {
new DbReportAllowedFeedback()
}
};
ReportProcessor.ProcessReport(report);
Assert.AreEqual(true, report.RequiresReview);
Assert.AreEqual(true, report.Conflicted);
}
public Report MapToDomainModel(DbReport dbReport)
{
return(new Report
{
Id = dbReport.ReportId,
Content = dbReport.ReportContent,
Status = MapToReportStatusEnum(dbReport.ReportStatus)
});
}
public void TestNewReport()
{
var report = new DbReport
{
RequiredFeedback = 2,
Feedbacks = new List <DbReportFeedback>(),
AllowedFeedback = new List <DbReportAllowedFeedback> {
new DbReportAllowedFeedback()
}
};
ReportProcessor.ProcessReport(report);
Assert.AreEqual(true, report.RequiresReview);
Assert.AreEqual(false, report.Conflicted);
}
/// <summary>
/// 获取Sql语句的 DataTable 数据集
/// </summary>
/// <param name="sqlValue">sql语句</param>
/// <returns></returns>
public DataTable GetDataTable(string sqlValue)
{
DataTable dt = new DataTable();
using (DbReport context = new DbReport())
{
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = sqlValue;
cmd.CommandType = CommandType.Text;
DbDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(dt);
}
return(dt);
}
/// <summary>
/// Processes reports and mark them as requiring review, or conflicted
/// </summary>
/// <param name="report"></param>
public static void ProcessReport(DbReport report)
{
var distinctFeedbackIds = report.Feedbacks.Where(f => f.InvalidatedDate == null && f.Feedback.IsActionable).Select(f => f.FeedbackId).Distinct().ToList();
var isConflicted = false;
var actualRequiredReviews = report.RequiredFeedback;
if (distinctFeedbackIds.Count > 1)
{
var feedbackPairs = ConflictHelper.CreateFeedbackPairs(distinctFeedbackIds).ToList();
var conflictingPairs = report.ConflictExceptions.Where(ce => ce.IsConflict).Select(ce => new
{
ConflictException = ce,
Pairs = ConflictHelper.CreateFeedbackPairs(ce.ConflictExceptionFeedbacks.Select(cef => cef.FeedbackId))
});
foreach (var conflictingPair in conflictingPairs)
{
if (conflictingPair.Pairs.Intersect(feedbackPairs).Any())
{
isConflicted = true;
if (conflictingPair.ConflictException.RequiredFeedback.HasValue)
{
actualRequiredReviews = conflictingPair.ConflictException.RequiredFeedback.Value;
}
break;
}
}
if (!isConflicted)
{
var nonConflictingPairs = report.ConflictExceptions.Where(ce => !ce.IsConflict).SelectMany(ce =>
ConflictHelper.CreateFeedbackPairs(ce.ConflictExceptionFeedbacks.Select(cef => cef.FeedbackId)));
if (!nonConflictingPairs.Intersect(feedbackPairs).Any())
{
isConflicted = true;
actualRequiredReviews = report.RequiredFeedbackConflicted;
}
}
}
report.Conflicted = isConflicted;
report.RequiresReview = report.AllowedFeedback.Any() && report.Feedbacks.Count(f => f.InvalidatedDate == null && f.Feedback.IsActionable) < actualRequiredReviews;
}
public void TestSimpleConflictException()
{
var report = new DbReport
{
Feedbacks = new List <DbReportFeedback>
{
new DbReportFeedback {
FeedbackId = 1, Feedback = new DbFeedback {
IsActionable = true
}
},
new DbReportFeedback {
FeedbackId = 2, Feedback = new DbFeedback {
IsActionable = true
}
}
},
ConflictExceptions = new List <DbConflictException>
{
new DbConflictException
{
IsConflict = false,
ConflictExceptionFeedbacks = new List <DbConflictExceptionFeedback>
{
new DbConflictExceptionFeedback {
FeedbackId = 1
},
new DbConflictExceptionFeedback {
FeedbackId = 2
},
}
}
},
RequiredFeedback = 2,
RequiredFeedbackConflicted = 4,
AllowedFeedback = new List <DbReportAllowedFeedback> {
new DbReportAllowedFeedback()
}
};
ReportProcessor.ProcessReport(report);
Assert.AreEqual(false, report.RequiresReview);
Assert.AreEqual(false, report.Conflicted);
}
public DataTable GetDataTableWithParam(string sqlValue, /*
* MySqlParameter[] dic*/Array dic)
{
DataTable dt = new DataTable();
//using (MySqlConnection connection = new MySqlConnection(_db.Database.Connection.ConnectionString))
//{
// if (connection.State == ConnectionState.Closed) connection.Open();
// using (MySqlCommand cmd = new MySqlCommand(sqlValue, connection))
// {
// MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
// adapter.SelectCommand.CommandType = CommandType.Text;
// if (dic != null && dic.Length != 0)
// {
// cmd.Parameters.AddRange(dic);
// }
// adapter.Fill(dt);
// }
//}
using (DbReport context = new DbReport())
{
try
{
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = sqlValue;
cmd.CommandType = CommandType.Text;
DbDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
if (dic != null && dic.Length > 0)
{
cmd.Parameters.AddRange(dic);
}
da.Fill(dt);
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
context.Database.Connection.Close();
}
}
return(dt);
}
//Get info from IED
//Create tables to store IED reports
private void CreateButton_Click(object sender, EventArgs e)
{
if (!IEDConnected)
{
toolStatus.Text = "IED NOT CONNECTED"; return;
}
if (firstUse)
{
string Args = hostname + " " + port;
string pathToFile = Directory.GetCurrentDirectory() + "/Report_get.exe";
// MessageBox.Show(pathToFile);
Process Prog = new Process();
try
{
Prog.StartInfo.FileName = pathToFile;
Prog.StartInfo.Arguments = Args;
Prog.StartInfo.CreateNoWindow = true;
Prog.Start();
Prog.WaitForExit();
firstUse = false;
}
catch (Exception ex)
{
toolStatus.Text = ex.Message;
firstUse = true;
}
}
DbReport.Clear();
string[] x = File.ReadAllLines(Directory.GetCurrentDirectory() + "/device.txt");
string[] dbnames = File.ReadAllLines(Directory.GetCurrentDirectory() + "/db_names.txt");
string[] reportname = File.ReadAllLines(Directory.GetCurrentDirectory() + "/reports.txt");
foreach (string item in x)
{
Device_name = item;
}
for (int i = 0; i < reportname.Count(); i++)
{
DbReport.Add(reportname[i], dbnames[i]);//(report,dbnames)
NamesCombo.Items.Add(dbnames[i]);
}
CreateDbForReports();
}
public int GetCount(string sqlValue, Array para, bool optimization = true)
{
int count = 0;
try
{
using (DbReport context = new DbReport())
{
var fromIndex = sqlValue.IndexOf("from", StringComparison.OrdinalIgnoreCase);
var resutlSql = "select '' " + sqlValue.Substring(fromIndex);
//修改 select '' 空内容 来优化 sql //参考todo #001
count = context.Database.SqlQuery <int>(string.Format("select count(*) from ( {0} ) a", optimization ? resutlSql : sqlValue), (object[])para).FirstOrDefault();
}
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
}
return(count);
}
public ReportModel ToModel(DbReport report)
{
var model = new ReportModel
{
Id = report.Id,
Title = report.Title,
};
if (report.Arguments.Any(x => x.ReportArgument.Name == "Customer"))
{
model.IsCustomer = true;
}
if (report.Arguments.Any(x => x.ReportArgument.Name == "ShowNonCashTransactions"))
{
model.ShowNonCash = true;
}
return model;
}
/// <summary>
/// 获取Sql语句的 DataTable 数据集
/// </summary>
/// <param name="sqlValue">sql语句</param>
/// <returns></returns>
public DataTable GetDataTable(string sqlValue)
{
DataSet ds = new DataSet();
//using (MySqlConnection connection = new MySqlConnection(_db.Database.Connection.ConnectionString))
//{
// if (connection.State == ConnectionState.Closed) connection.Open();
// using (MySqlCommand cmd = new MySqlCommand(sqlValue, connection))
// {
// MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
// adapter.SelectCommand.CommandType = CommandType.Text;
// adapter.Fill(ds);
// }
//}
using (DbReport context = new DbReport())
{
try
{
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = sqlValue;
cmd.CommandType = CommandType.Text;
DbDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
context.Database.Connection.Close();
}
}
return(ds.Tables[0] ?? new DataTable());
}
public AdminDALController(DbReport db)
{
_db = db;
}
public LinkDALController(DbReport db)
{
_db = db;
}
public IActionResult RegisterPost([FromBody] RegisterPostRequest request)
{
var botId = GetBotId();
if (!botId.HasValue)
{
throw new HttpStatusException(HttpStatusCode.BadRequest, "Invalid or missing botId in claim");
}
if (string.IsNullOrWhiteSpace(request.Title))
{
throw new HttpStatusException(HttpStatusCode.BadRequest, "Title is required");
}
if (string.IsNullOrWhiteSpace(request.ContentUrl))
{
throw new HttpStatusException(HttpStatusCode.BadRequest, "ContentUrl is required");
}
if (!request.ContentId.HasValue)
{
throw new HttpStatusException(HttpStatusCode.BadRequest, "ContentId is required");
}
if (string.IsNullOrWhiteSpace(request.ContentSite))
{
throw new HttpStatusException(HttpStatusCode.BadRequest, "ContentSite is required");
}
if (string.IsNullOrWhiteSpace(request.ContentType))
{
throw new HttpStatusException(HttpStatusCode.BadRequest, "ContentType is required");
}
var report = new DbReport
{
AuthorName = request.AuthorName,
AuthorReputation = request.AuthorReputation,
DashboardId = botId.Value,
Title = request.Title,
ContentUrl = request.ContentUrl,
ContentId = request.ContentId.Value,
ContentSite = request.ContentSite,
ContentType = request.ContentType,
ContentCreationDate = request.ContentCreationDate?.ToUniversalTime(),
DetectedDate = request.DetectedDate?.ToUniversalTime(),
DetectionScore = request.DetectionScore,
Feedbacks = new List <DbReportFeedback>(),
ConflictExceptions = new List <DbConflictException>()
};
var dashboard = _dbContext.Dashboards.FirstOrDefault(d => d.Id == botId);
if (dashboard == null)
{
throw new HttpStatusException(HttpStatusCode.BadRequest, $"Dashboard with id {botId} does not exist");
}
report.RequiredFeedback = request.RequiredFeedback ?? dashboard.RequiredFeedback;
report.RequiredFeedbackConflicted = request.RequiredFeedbackConflicted ?? dashboard.RequiredFeedbackConflicted;
var contentFragments = request.ContentFragments ?? Enumerable.Empty <RegisterPostContentFragment>();
var fragments =
string.IsNullOrWhiteSpace(request.Content)
? contentFragments
: new[]
{
new RegisterPostContentFragment
{
Content = request.Content,
Name = "Original",
Order = 0,
RequiredScope = string.Empty
}
}.Concat(contentFragments.Select(cf => new RegisterPostContentFragment
{
Content = cf.Content,
Name = cf.Name,
Order = cf.Order + 1,
RequiredScope = cf.RequiredScope
}));
foreach (var contentFragment in fragments)
{
var dbContentFragment = new DbContentFragment
{
Order = contentFragment.Order,
Name = contentFragment.Name,
Content = contentFragment.Content,
RequiredScope = contentFragment.RequiredScope,
Report = report
};
_dbContext.ContentFragments.Add(dbContentFragment);
}
_dbContext.Reports.Add(report);
if (request.AllowedFeedback?.Any() ?? false)
{
var feedbackTypes = _dbContext.Feedbacks.Where(f => f.DashboardId == botId && request.AllowedFeedback.Contains(f.Name)).ToDictionary(f => f.Name, f => f.Id);
foreach (var allowedFeedback in request.AllowedFeedback)
{
if (feedbackTypes.ContainsKey(allowedFeedback))
{
_dbContext.ReportAllowedFeedbacks.Add(new DbReportAllowedFeedback
{
FeedbackId = feedbackTypes[allowedFeedback],
Report = report
});
}
else
{
throw new HttpStatusException(HttpStatusCode.BadRequest, $"Feedback '{allowedFeedback}' not registered for bot");
}
}
}
if (request.Reasons?.Any() ?? false)
{
var reasons = _dbContext.Reasons.Where(f => f.DashboardId == botId).ToDictionary(f => f.Name, f => f);
foreach (var reason in request.Reasons ?? Enumerable.Empty <RegisterPostReason>())
{
DbReason dbReason;
if (reasons.ContainsKey(reason.ReasonName))
{
dbReason = reasons[reason.ReasonName];
}
else
{
dbReason = new DbReason
{
Name = reason.ReasonName,
DashboardId = botId.Value
};
_dbContext.Reasons.Add(dbReason);
}
_dbContext.ReportReasons.Add(new DbReportReason
{
Confidence = reason.Confidence,
Tripped = reason.Tripped ?? false,
Reason = dbReason,
Report = report
});
}
}
foreach (var attribute in request.Attributes ?? Enumerable.Empty <RegisterPostAttribute>())
{
_dbContext.ReportAttributes.Add(new DbReportAttribute
{
Name = attribute.Key,
Value = attribute.Value,
Report = report
});
}
_dbContext.SaveChanges();
_dbContext.ProcessReport(report.Id);
_dbContext.SaveChanges();
return(Json(report.Id));
}
public ExportDALController(DbReport db)
{
_db = db;
}
public LoginDALController(DbReport db)
{
_db = db;
}
public CommondController(DbReport db)
{
_db = db;
}
public ActionResult Report()
{
var model = new DbReport().SelectAll();
return(View(model));
}
