static SOM CreateSomModel_v2(string tagname)
{
int samples = 1500;
var context = new TagDbContext();
double[] data = context.TagValues
.Where(t => t.Tag.TagName == tagname)
.OrderByDescending(v => v.DateTime)
.Select(v => v.Value)
.Take(samples + retrospective + 1)
.AsEnumerable()
.Reverse()
.ToArray();
var inputs = new Vector[samples];
for (int i = 0; i < samples; i++)
{
inputs[i] = new Vector();
inputs[i].Input = data.Skip(i).Take(retrospective).ToArray();
// set output
inputs[i].Output = new double[] { data[retrospective + i + 1] };
}
SOM model = new SOM(5, 1, 15, 15);
model.Train(inputs, 500);
return(model);
}
public ActionResult Create(TaggingOrg model)
{
var db = new TagDbContext();
if (ModelState.IsValid)
{
db.TaggingOrgs.Add(model);
db.SaveChanges();
var userManager = new UserManager <ApplicationUser>(new UserStore <ApplicationUser>(new UserDbContext()));
var user = new ApplicationUser()
{
UserName = model.UserName
};
var result = userManager.Create(user, model.Password);
user = userManager.FindByName(model.UserName);
userManager.AddToRole(user.Id, AccountController.RoleTagger);
return(RedirectToAction("Index"));
}
return(View(model));
}
public static void ForecastAndExport_v4(string forecast_tag, string data_tag, int forecastPerspective = 2)
{
string tag_name = forecast_tag;
DateTime fdate = DateTime.Today.AddDays(forecastPerspective); // дата прогноза
List <double> input = new List <double>(); // входной вектор
Dictionary <DateTime, double> solution = new Dictionary <DateTime, double>(); // прогноз
var context = new TagDbContext();
var model = CreateSomModel_v2(data_tag);
var data = context.TagValues
.Where(t => t.Tag.TagName.Trim() == data_tag && (SqlFunctions.DatePart("weekday", t.DateTime) - 1) == (int)fdate.DayOfWeek)
.OrderBy(v => v.DateTime)
.Select(v => v.Value)
.Take(24 + retrospective + 1)
.AsEnumerable()
.Reverse()
.ToArray();
for (int i = 0; i < 24; i++)
{
var vector = data.Skip(i).Take(retrospective).ToArray();
var value = model.Compute(vector)[0];
solution[fdate.AddHours(i + 1)] = value;
}
ExportToDB(tag_name, solution, context);
}
public static void ForecastAndExport_v3(string forecast_tag, string data_tag, int forecastPerspective = 2)
{
string tag_name = forecast_tag;
double[] vMax, vMin;
DateTime fdate = DateTime.Today.AddDays(forecastPerspective); // дата прогноза
SOM model = CreateSomModel(data_tag, out vMax, out vMin);
Dictionary <DateTime, double> solution = new Dictionary <DateTime, double>(); // прогноз
TagDbContext context = new TagDbContext();
var data = context.TagValues
.Where(t => t.Tag.TagName.Trim() == data_tag)
.OrderByDescending(v => v.DateTime)
.Select(v => new { v.DateTime.Hour, Day = SqlFunctions.DatePart("weekday", v.DateTime) + 1, Value = v.Value })
.Take(24 + 3 + 1)
.AsEnumerable()
.Reverse()
.ToArray();
for (int index = 0; index < 24; index++)
{
var vector = new double[] {
(double)(vMax[0] - (double)data[index].Hour) / (vMax[0] - vMin[0]),
(double)(vMax[1] - (double)data[index].Day) / (vMax[1] - vMin[1]),
(double)(vMax[2] - data[index + 1].Value) / (vMax[2] - vMin[2]),
(double)(vMax[3] - data[index + 2].Value) / (vMax[3] - vMin[3]),
(double)(vMax[4] - data[index + 3].Value) / (vMax[4] - vMin[4])
};
var value = model.Compute(vector)[0];
solution[fdate.AddHours(data[index].Hour)] = value;
}
ExportToDB(tag_name, solution, context);
}
static DistanceNetwork CreateSOM(string tagname)
{
int samples = 2 * 31 * 24;
int retro = 3;
var context = new TagDbContext();
var data = context.TagValues
.Where(t => t.Tag.TagName == tagname)
.OrderByDescending(v => v.DateTime)
.Select(v => new double[] { (double)v.DateTime.Hour, (double)v.DateTime.DayOfWeek, v.Value })
.Take(samples + retrospective + 3)
.AsEnumerable()
.Reverse()
.ToArray();
double[][] trainingSet = new double[data.Length][];
for (int index = 0; index < samples; index++)
{
trainingSet[index] = new double[] { data[index + 3][0], data[index + 3][1], data[index + 3][2],
data[index + 2][2], data[index + 1][2], data[index][2] };
}
var networkSize = 15;
var iterations = 500;
var learningRate = 0.3;
var learningRadius = 3;
Neuron.RandRange = new Range(0, 255);
// create network
DistanceNetwork network = new DistanceNetwork(2, networkSize * networkSize);
// create learning algorithm
SOMLearning trainer = new SOMLearning(network, networkSize, networkSize);
// create map
//map = new int[networkSize, networkSize, 3];
double fixedLearningRate = learningRate / 10;
double driftingLearningRate = fixedLearningRate * 9;
// iterations
int i = 0;
// loop
while (true)
{
trainer.LearningRate = driftingLearningRate * (iterations - i) / iterations + fixedLearningRate;
trainer.LearningRadius = (double)learningRadius * (iterations - i) / iterations;
// run training epoch
trainer.RunEpoch(trainingSet);
// increase current iteration
i++;
// stop ?
if (i >= iterations)
{
break;
}
}
return(network);
}
public ActionResult Groups(int id)
{
var db = new TagDbContext();
var model = db.TaggingOrgs.SingleOrDefault(org => org.TaggingOrgId == id);
return(View(model));
}
//
// GET: /Admin/TaggingOrg/
public ActionResult Index()
{
var db = new TagDbContext();
var model = db.TaggingOrgs.ToList();
return(View(model));
}
public static void ImportVTI(int vti, string tagname)
{
var context = new TagDbContext();
Tag tag = context.Tags.Where(t => t.TagName == tagname).First();
var values = context.TagValues
.Where(t => t.Tag.TagName == tagname)
.OrderByDescending(t => t.DateTime)
.Select(t => t.DateTime);
DateTime date = values.Count() > 0 ? values.First() : new DateTime(2012, 10, 1);//DateTime.Today; // <---- заменить DateTime на Today
using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["SourceDB"].ConnectionString))
{
sql.Open();
using (SqlCommand cmdClear = new SqlCommand("ep_AskVTIdata", sql))
{
cmdClear.CommandType = System.Data.CommandType.StoredProcedure;
cmdClear.Parameters.AddWithValue("@cmd", "Clear");
cmdClear.Parameters.AddWithValue("@idReq", 60000);
cmdClear.ExecuteNonQuery();
}
using (SqlCommand cmdSelect = new SqlCommand("ep_AskVTIdata", sql))
{
cmdSelect.CommandType = System.Data.CommandType.StoredProcedure;
cmdSelect.Parameters.AddWithValue("@cmd", "ListAdd");
cmdSelect.Parameters.AddWithValue("@idVTI", vti);
cmdSelect.Parameters.AddWithValue("@TimeStart", date);
cmdSelect.Parameters.AddWithValue("@ShiftBeg", 60);
cmdSelect.Parameters.AddWithValue("@idReq", 60000);
cmdSelect.ExecuteNonQuery();
}
string query = @"SELECT v1.TimeSQL, v1.idVTI, v1.ValueFl+ v2.ValueFl as Value
FROM VTIdataList v1
INNER JOIN VTIdataList v2 ON v1.TimeSQL = DATEADD(minute, 30, v2.TimeSQL)
and DATEPART(minute, v1.TimeSQL) != 30
and v1.idVTI = v2.idVTI
WHERE v1.idVTI=@vti and v1.idReq=@idReq and v1.ValueFl IS NOT NULL and v2.ValueFl IS NOT NULL";
using (SqlCommand cmdGetData = new SqlCommand(query, sql))
{
cmdGetData.Parameters.AddWithValue("@vti", vti);
cmdGetData.Parameters.AddWithValue("@idReq", 60000);
var reader = cmdGetData.ExecuteReader();
while (reader.Read())
{
context.TagValues.Add(new TagValue()
{
DateTime = (DateTime)reader["TimeSQL"], TagID = tag.TagID, Value = (double)reader["Value"]
});
}
context.SaveChanges();
}
};
}
public static void ForecastAndExport_v2(string forecast_tag, string data_tag, int forecastPerspective = 2)
{
string tag_name = forecast_tag;
DateTime fdate = DateTime.Today.AddDays(forecastPerspective); // дата прогноза
List <double> input = new List <double>(); // входной вектор
Dictionary <DateTime, double> solution = new Dictionary <DateTime, double>(); // прогноз
var context = new TagDbContext();
//double yMax, yMin;
//if (fdate.DayOfWeek == DayOfWeek.Saturday || fdate.DayOfWeek == DayOfWeek.Sunday) //составляем модель на субботу и воскресенье
//{
// Console.WriteLine("Создание модели.");
// var model = CreateTimeSeriesModel_v2(data_tag, out yMax, out yMin, fdate.DayOfWeek);
// Console.WriteLine("Модели создана.");
// Console.WriteLine("Подготовка входных данных.");
// input = context.TagValues
// .Where(t => t.Tag.TagName == data_tag && (SqlFunctions.DatePart("weekday", t.DateTime) - 1) == (int)fdate.DayOfWeek)
// .OrderByDescending(t => t.DateTime)
// .Select(v => (yMax - v.Value) / (yMax - yMin))
// .Take(retrospective)
// .AsEnumerable()
// .Reverse()
// .ToList();
// Console.WriteLine("Входные данные готовы.");
// Console.WriteLine("Прогнозирование.");
// for (int i = 1; i <= 24; i++)
// {
// double model_result = model.Compute(input.ToArray())[0];
// double result = yMax - model_result * (yMax - yMin);
// solution[fdate.AddHours(i)] = result;
// input.RemoveAt(0);
// input.Add(model_result);
// }
//}
//else // составляем модель на будние дни
//{
DateTime startDate = fdate.AddMonths(-1); //вычитаем
var tender = context.TagValues
.Where(tv => tv.Tag.TagName == data_tag && tv.DateTime > startDate &&
(SqlFunctions.DatePart("weekday", tv.DateTime) - 1) == (int)fdate.DayOfWeek)
.GroupBy(tv => SqlFunctions.DatePart("Hour", tv.DateTime))
.Select(t => new { Hour = t.Key, Value = t.Average(tv => tv.Value) })
.ToArray();
foreach (var entity in tender)
{
solution[fdate.AddHours((int)entity.Hour)] = entity.Value;
}
//}
Console.WriteLine("Запись прогноза...");
ExportToDB(tag_name, solution, context);
}
public ActionResult Start(int id)
{
var db = new TagDbContext();
var org = db.TaggingOrgs.SingleOrDefault(o => o.TaggingOrgId == id);
var model = new TaggingOrgViewModel {
Org = org
};
return(View(model));
}
public JsonResult GroupList(string name)
{
var db = new TagDbContext();
var list = db.TaggingGroups.Where(g => g.Name.Contains(name))
.OrderBy(l => l.Name)
.Select(l => new
{
name = l.Name,
id = l.TaggingGroupId,
})
.Take(10);
return(Json(list));
}
public PartialViewResult RemoveGroup(int orgid, int groupid)
{
var db = new TagDbContext();
var org = db.TaggingOrgs.SingleOrDefault(o => o.TaggingOrgId == orgid);
var group = db.TaggingGroups.SingleOrDefault(g => g.TaggingGroupId == groupid);
if (org != null && group != null)
{
org.Groups.Remove(group);
db.SaveChanges();
}
return(PartialView("_EmptyRow"));
}
public PartialViewResult AddGroup(int orgid, string groupname)
{
var db = new TagDbContext();
var org = db.TaggingOrgs.SingleOrDefault(o => o.TaggingOrgId == orgid);
var group = db.TaggingGroups.SingleOrDefault(g => g.Name.Equals(groupname));
if (org != null && group != null)
{
org.Groups.Add(group);
db.SaveChanges();
}
return(PartialView("_GroupList", org));
}
public ActionResult DisableTagging(int orgid, int groupid)
{
var db = new TagDbContext();
var org = db.TaggingOrgs.SingleOrDefault(o => o.TaggingOrgId == orgid);
var group = db.TaggingGroups.SingleOrDefault(g => g.TaggingGroupId == groupid);
if (org != null && group != null)
{
group.CreatedAt = DateTime.Now.AddSeconds(-1);
db.SaveChanges();
}
return(RedirectToAction("Start", new { id = org.TaggingOrgId }));
}
public ActionResult ShowImage(int id = 0)
{
TagDbContext db = new TagDbContext();
var artwork = db.Artworks.SingleOrDefault(img => img.ArtworkId == id);
if (artwork != null)
{
var image = artwork.Images.FirstOrDefault();
if (image != null)
{
return(File(image.ImageData, image.ImageFileType));
}
}
return(File("~/images/notfound.jpg", "image/jpg"));
}
public static void ForecastAndExport(string forecast_tag, string data_tag, int forecastPerspective = 2)
{
string tag_name = forecast_tag;
double yMax, yMin;
var model = CreateTimeSeriesModel(data_tag, out yMax, out yMin);
var context = new TagDbContext();
var values = context.TagValues
.Where(t => t.Tag.TagName == data_tag)
.OrderByDescending(t => t.DateTime)
.Select(t => t.DateTime);
DateTime date = values.First();
DateTime perspective = date.AddHours((forecastPerspective + 1) * 24); // прогнозируем на forecastPerspective часов вперёд
List <double> input = new List <double>();
input = context.TagValues
.Where(t => t.Tag.TagName == data_tag)
.OrderByDescending(t => t.DateTime)
.Select(v => (yMax - v.Value) / (yMax - yMin))
.Take(retrospective)
.AsEnumerable()
.Reverse()
.ToList();
Dictionary <DateTime, double> solution = new Dictionary <DateTime, double>();
while (date <= perspective)
{
date = date.AddHours(1);
double model_result = model.Compute(input.ToArray())[0];
double result = yMax - model_result * (yMax - yMin);
solution[date] = result;
input.RemoveAt(0);
input.Add(model_result);
}
ExportToDB(tag_name, solution, context);
}
static SOM CreateSomModel(string tagname, out double[] vMax, out double[] vMin)
{
int retro = 3;
int samples = 1500;
var context = new TagDbContext();
var data = context.TagValues
.Where(t => t.Tag.TagName.Trim() == tagname)
.OrderByDescending(v => v.DateTime)
.Select(v => new { v.DateTime.Hour, Day = SqlFunctions.DatePart("weekday", v.DateTime) + 1, Value = v.Value })
.Take(samples + retro + 1)
.AsEnumerable()
.Reverse()
.ToArray();
vMax = new double[] { data.Max(d => d.Hour), data.Max(d => (double)d.Day), data.Max(d => d.Value), data.Max(d => d.Value), data.Max(d => d.Value) };
vMin = new double[] { data.Min(d => d.Hour), data.Min(d => (double)d.Day), data.Min(d => d.Value), data.Min(d => d.Value), data.Min(d => d.Value) };
var inputs = new Vector[samples];
for (int index = 0; index < samples; index++)
{
inputs[index] = new Vector();
inputs[index].Input = new double[] {
(double)(vMax[0] - (double)data[index].Hour) / (vMax[0] - vMin[0]),
(double)(vMax[1] - (double)data[index].Day) / (vMax[1] - vMin[1]),
(double)(vMax[2] - data[index + 1].Value) / (vMax[2] - vMin[2]),
(double)(vMax[3] - data[index + 2].Value) / (vMax[3] - vMin[3]),
(double)(vMax[4] - data[index + 3].Value) / (vMax[4] - vMin[4])
};
inputs[index].Output = new double[] { data[index].Value };
}
SOM model = new SOM(5, 1, 15, 15);
model.Train(inputs, 500);
return(model);
}
public ActionResult Index(TagSearchModel searchData)
{
var TaggingStartTime = searchData.TaggingDateBegin.Add(searchData.TaggingTimeBegin);
var TaggingEndTime = searchData.TaggingDateEnd.Add(searchData.TaggingTimeEnd);
var context = new TagDbContext();
ICollection <ArtworkTag> taggings = null;
bool errorExist = false;
// 1. Vorbedingungen prüfen
// wenn Auswahl, dann muss auch was ausgewählt sein => sonst Fehler!
// wenn Zeitraum, dann muss startTime <= endTime sein => sonst fehler
/*
* Anzeige, wenn "Alle" gewählt ist und trotzdem Elemente der Liste angeklickt wurden.
* Nur zum Verständnis, wenn Liste nicht die gewünschten Ergebnisse anzeigt.
*
* if (searchData.IsArtworkSelection == false && searchData.ArtworkPoolId != null)
* {
* ModelState.AddModelError("ArtworkPoolId", "Sie haben in der Liste eine Auswahl getroffen, aber bereits 'Alle' gewählt.");
* }
*
* if (searchData.IsGroupTagSelection == false && searchData.GroupTagId != null)
* {
* ModelState.AddModelError("GroupTagId", "Sie haben in der Liste eine Auswahl getroffen, aber bereits 'Alle' gewählt.");
* }
*/
if (searchData.IsArtworkSelection == true && (searchData.ArtworkPoolId == null || searchData.ArtworkPoolId.Count() == 0))
{
ModelState.AddModelError("ArtworkPoolId", "Bitte wählen Sie Elemente aus der Liste.");
errorExist = true;
}
if (searchData.IsTimeSpanSelection == true && (TaggingStartTime >= TaggingEndTime))
{
ModelState.AddModelError("TaggingDateBegin", "Die Startzeit muss vor der Endzeit liegen.");
errorExist = true;
}
if (searchData.IsGroupTagSelection == true && (searchData.GroupTagId == null || searchData.GroupTagId.Count() == 0))
{
ModelState.AddModelError("GroupTagId", "Bitte wählen Sie Elemente aus der Liste.");
errorExist = true;
}
if (errorExist)
{
// das Modell für die Anzeige muss jetzt wieder gebaut werden
ViewBag.ArtworkPoolId = new MultiSelectList(db.ArtworkPools, "ArtworkPoolId", "Name", null);
ViewBag.GroupTagId = new MultiSelectList(db.GroupTags, "GroupTagId", "Tag", null);
return(View(searchData));
}
// 2. nur weitermachen, wenn keine Fehler
// ArtworkId == Auswahl && TimeSpan == Zeitraum && GroupTagId == Auswahl
if (searchData.IsArtworkSelection == true && searchData.IsTimeSpanSelection == true && searchData.IsGroupTagSelection == true)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingTime >= TaggingStartTime && t.TaggingTime <= TaggingEndTime &&
searchData.ArtworkPoolId.Contains(t.Artwork.ArtworkPool.ArtworkPoolId) &&
t.TaggingGroup.GroupTags.Any(g => searchData.GroupTagId.Contains(g.GroupTagId))
).ToList();
}
// ArtworkId == Auswahl && TimeSpan == Zeitraum && GroupTagId == Alle
else if (searchData.IsArtworkSelection == true && searchData.IsTimeSpanSelection == true && searchData.IsGroupTagSelection == false)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingTime >= TaggingStartTime && t.TaggingTime <= TaggingEndTime &&
searchData.ArtworkPoolId.Contains(t.Artwork.ArtworkPool.ArtworkPoolId)).ToList();
}
// ArtworkId == Auswahl && TimeSpan == keine Einschränkung && GroupTagId == Auswahl
else if (searchData.IsArtworkSelection == true && searchData.IsTimeSpanSelection == false && searchData.IsGroupTagSelection == true)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingGroup.GroupTags.Any(g => searchData.GroupTagId.Contains(g.GroupTagId) &&
searchData.ArtworkPoolId.Contains(t.Artwork.ArtworkPool.ArtworkPoolId))
).ToList();
}
// ArtworkId == Alle && TimeSpan == Zeitraum && GroupTagId == Auswahl
else if (searchData.IsArtworkSelection == false && searchData.IsTimeSpanSelection == true && searchData.IsGroupTagSelection == true)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingTime >= TaggingStartTime && t.TaggingTime <= TaggingEndTime &&
t.TaggingGroup.GroupTags.Any(g => searchData.GroupTagId.Contains(g.GroupTagId))
).ToList();
}
// ArtworkId == Auswahl && TimeSpan == keine Einschränkung && GroupTagId == Alle
else if (searchData.IsArtworkSelection == true && searchData.IsTimeSpanSelection == false && searchData.IsGroupTagSelection == false)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => searchData.ArtworkPoolId.Contains(t.Artwork.ArtworkPool.ArtworkPoolId))
.ToList();
}
// ArtworkId == Alle && TimeSpan == Zeitraum && GroupTagId == Alle
else if (searchData.IsArtworkSelection == false && searchData.IsTimeSpanSelection == true && searchData.IsGroupTagSelection == false)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingTime >= TaggingStartTime && t.TaggingTime <= TaggingEndTime
).ToList();
}
// ArtworkId == Alle && TimeSpan == keine Einschränkung && GroupTagId == Auswahl
else if (searchData.IsArtworkSelection == false && searchData.IsTimeSpanSelection == false && searchData.IsGroupTagSelection == true)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingGroup.GroupTags.Any(g => searchData.GroupTagId.Contains(g.GroupTagId))
).ToList();
}
// ArtworkId == Alle && TimeSpan == keine Einschränkung && GroupTagId == Alle
else if (searchData.IsArtworkSelection == false && searchData.IsTimeSpanSelection == false && searchData.IsGroupTagSelection == false)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.ToList();
}
var model = new List <TagAnalysisModel>();
foreach (var tag in taggings)
{
var tam = new TagAnalysisModel
{
Tag = tag.Tag,
Artwork = tag.Artwork.Title,
ImagePoolName = tag.Artwork.ArtworkPool.Name,
TaggingTime = tag.TaggingTime,
GroupTags = tag.TaggingGroup.GetTagList(),
};
model.Add(tam);
}
var ms = new MemoryStream();
var writer = new StreamWriter(ms, Encoding.Default);
//Response.Clear();
//Response.AddHeader("content-Disposition", "attachement; filename=taggings.csv");
//Response.ContentType = "text/csv";
writer.Write("Schlagwort;Bild;Bildpool;Zeitstempel;Gruppenschlagworte");
writer.Write(Environment.NewLine);
foreach (var item in model)
{
writer.Write(String.Format("{0};{1};{2};{3};{4}",
item.Tag,
item.Artwork,
item.ImagePoolName,
item.TaggingTime,
item.GroupTags
));
writer.Write(Environment.NewLine);
}
writer.Flush();
writer.Dispose();
// Anzeige, wenn "Alle" gewählt ist und trotzdem Elemente der Liste angeklickt wurden.
// Nur zum Verständnis, wenn Liste nicht die gewünschten Ergebnisse anzeigt.
//ViewBag.ArtworkPoolId = new MultiSelectList(db.ArtworkPools, "ArtworkPoolId", "Name", null);
//ViewBag.GroupTagId = new MultiSelectList(db.GroupTags, "GroupTagId", "Tag", null);
return(File(ms.GetBuffer(), "text/csv", "Taggings.csv")); // && View(searchData);
}
public TagDbData(TagDbContext context)
{
_context = context;
}
static void Main(string[] args)
{
TagDbContext context = new TagDbContext();
context.Database.CreateIfNotExists();
}
static ActivationNetwork CreateTimeSeriesModel_v2(string tagname, out double yMax, out double yMin, DayOfWeek model_type)
{
//основные параметры обучения
double sigmoidAlphaValue = 2;
double learningRate = 0.1;
double momentum = 0.9;
int iterations = 10000;
//количество примеров
int samples = 200;
var context = new TagDbContext();
double[] data;
if (model_type == DayOfWeek.Saturday || model_type == DayOfWeek.Sunday)
{
data = context.TagValues
.Where(t => t.Tag.TagName.Trim() == tagname && (SqlFunctions.DatePart("weekday", t.DateTime) - 1) == (int)model_type)
.OrderByDescending(v => v.DateTime)
.Select(v => v.Value)
.Take(samples + retrospective + 1)
.AsEnumerable()
.Reverse()
.ToArray();
}
else
{
data = context.TagValues
.Where(t => t.Tag.TagName.Trim() == tagname && (SqlFunctions.DatePart("weekday", t.DateTime) - 1) != (int)DayOfWeek.Sunday && (SqlFunctions.DatePart("weekday", t.DateTime) - 1) != (int)DayOfWeek.Saturday)
.OrderByDescending(v => v.DateTime)
.Select(v => v.Value)
.Take(samples + retrospective + 1)
.AsEnumerable()
.Reverse()
.ToArray();
}
// prepare learning data
double[][] input = new double[samples][];
double[][] output = new double[samples][];
// для приведения входных даннанных к промежутку от 0 до 1
double max = data.Max() == 0 ? 1 : data.Max();
double min = data.Min();
yMin = min;
yMax = max;
for (int i = 0; i < samples; i++)
{
input[i] = new double[retrospective];
output[i] = new double[1];
input[i] = data.Skip(i).Take(retrospective).Select(v => (max - v) / (max - min)).ToArray();
// set output
output[i][0] = (max - data[retrospective + i + 1]) / (max - min);
}
// create multi-layer neural network
ActivationNetwork network = new ActivationNetwork(
new BipolarSigmoidFunction(sigmoidAlphaValue),
retrospective, retrospective * 2, 1);
// create teacher
BackPropagationLearning teacher = new BackPropagationLearning(network);
// set learning rate and momentum
teacher.LearningRate = learningRate;
teacher.Momentum = momentum;
// iterations
for (int iteration = 0, trynumber = 1; iteration < iterations; iteration++)
{
double error = teacher.RunEpoch(input, output) / samples;
if (iteration == (iterations - 1) && error > 0.05 && trynumber < 4)
{
trynumber++;
iteration = 0;
}
;
}
return(network);
}
public ActionResult TagSearch(TagSearchModel searchData, string buttonType)
{
var TaggingStartTime = searchData.TaggingDateBegin.Add(searchData.TaggingTimeBegin);
var TaggingEndTime = searchData.TaggingDateEnd.Add(searchData.TaggingTimeEnd);
var context = new TagDbContext();
bool errorExist = false;
// 1. Vorbedingungen prüfen
// wenn Auswahl, dann muss auch was ausgewählt sein => sonst Fehler!
// wenn Zeitraum, dann muss startTime <= endTime sein => sonst fehler
/*
* Anzeige, wenn "Alle" gewählt ist und trotzdem Elemente der Liste angeklickt wurden.
* Nur zum Verständnis, wenn Liste nicht die gewünschten Ergebnisse anzeigt.
*
* if (searchData.IsArtworkSelection == false && searchData.ArtworkPoolId != null)
* {
* ModelState.AddModelError("ArtworkPoolId", "Sie haben in der Liste eine Auswahl getroffen, aber bereits 'Alle' gewählt.");
* }
*
* if (searchData.IsGroupTagSelection == false && searchData.GroupTagId != null)
* {
* ModelState.AddModelError("GroupTagId", "Sie haben in der Liste eine Auswahl getroffen, aber bereits 'Alle' gewählt.");
* }
*/
if (searchData.IsArtworkSelection == true && (searchData.ArtworkPoolId == null || searchData.ArtworkPoolId.Count() == 0))
{
ModelState.AddModelError("ArtworkPoolId", "Bitte wählen Sie Elemente aus der Liste.");
errorExist = true;
}
if (searchData.IsTimeSpanSelection == true && (TaggingStartTime >= TaggingEndTime))
{
ModelState.AddModelError("TaggingDateBegin", "Die Startzeit muss vor der Endzeit liegen.");
errorExist = true;
}
if (searchData.IsGroupTagSelection == true && (searchData.GroupTagId == null || searchData.GroupTagId.Count() == 0))
{
ModelState.AddModelError("GroupTagId", "Bitte wählen Sie Elemente aus der Liste.");
errorExist = true;
}
if (errorExist)
{
// das Modell für die Anzeige muss jetzt wieder gebaut werden
ViewBag.ArtworkPoolId = new MultiSelectList(db.ArtworkPools, "ArtworkPoolId", "Name", null);
ViewBag.GroupTagId = new MultiSelectList(db.GroupTags, "GroupTagId", "Tag", null);
return(View(searchData));
}
ICollection <ArtworkTag> taggings = null;
if (searchData.IsArtworkSelection == true && searchData.IsTimeSpanSelection == true && searchData.IsGroupTagSelection == true)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingTime >= TaggingStartTime && t.TaggingTime <= TaggingEndTime &&
searchData.ArtworkPoolId.Contains(t.Artwork.ArtworkPool.ArtworkPoolId) &&
t.TaggingGroup.GroupTags.Any(g => searchData.GroupTagId.Contains(g.GroupTagId))
).ToList();
}
// ArtworkId == Auswahl && TimeSpan == Zeitraum && GroupTagId == Alle
else if (searchData.IsArtworkSelection == true && searchData.IsTimeSpanSelection == true && searchData.IsGroupTagSelection == false)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingTime >= TaggingStartTime && t.TaggingTime <= TaggingEndTime &&
searchData.ArtworkPoolId.Contains(t.Artwork.ArtworkPool.ArtworkPoolId)).ToList();
}
// ArtworkId == Auswahl && TimeSpan == keine Einschränkung && GroupTagId == Auswahl
else if (searchData.IsArtworkSelection == true && searchData.IsTimeSpanSelection == false && searchData.IsGroupTagSelection == true)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingGroup.GroupTags.Any(g => searchData.GroupTagId.Contains(g.GroupTagId) &&
searchData.ArtworkPoolId.Contains(t.Artwork.ArtworkPool.ArtworkPoolId))
).ToList();
}
// ArtworkId == Alle && TimeSpan == Zeitraum && GroupTagId == Auswahl
else if (searchData.IsArtworkSelection == false && searchData.IsTimeSpanSelection == true && searchData.IsGroupTagSelection == true)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingTime >= TaggingStartTime && t.TaggingTime <= TaggingEndTime &&
t.TaggingGroup.GroupTags.Any(g => searchData.GroupTagId.Contains(g.GroupTagId))
).ToList();
}
// ArtworkId == Auswahl && TimeSpan == keine Einschränkung && GroupTagId == Alle
else if (searchData.IsArtworkSelection == true && searchData.IsTimeSpanSelection == false && searchData.IsGroupTagSelection == false)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => searchData.ArtworkPoolId.Contains(t.Artwork.ArtworkPool.ArtworkPoolId))
.ToList();
}
// ArtworkId == Alle && TimeSpan == Zeitraum && GroupTagId == Alle
else if (searchData.IsArtworkSelection == false && searchData.IsTimeSpanSelection == true && searchData.IsGroupTagSelection == false)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingTime >= TaggingStartTime && t.TaggingTime <= TaggingEndTime
).ToList();
}
// ArtworkId == Alle && TimeSpan == keine Einschränkung && GroupTagId == Auswahl
else if (searchData.IsArtworkSelection == false && searchData.IsTimeSpanSelection == false && searchData.IsGroupTagSelection == true)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingGroup.GroupTags.Any(g => searchData.GroupTagId.Contains(g.GroupTagId))
).ToList();
}
// ArtworkId == Alle && TimeSpan == keine Einschränkung && GroupTagId == Alle
else if (searchData.IsArtworkSelection == false && searchData.IsTimeSpanSelection == false && searchData.IsGroupTagSelection == false)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.ToList();
}
if (buttonType == "download")
{
return(TaggingAnalysisDownload(taggings));
}
else
{
return(TaggingAnalysisWeb(taggings));
}
}
public ActionResult TaggingAnalysisWeb(TagSearchModel searchData)
{
var TaggingStartTime = searchData.TaggingDateBegin.Add(searchData.TaggingTimeBegin);
var TaggingEndTime = searchData.TaggingDateEnd.Add(searchData.TaggingTimeEnd);
var context = new TagDbContext();
ICollection <ArtworkTag> taggings = null;
if (searchData.IsArtworkSelection == true && searchData.IsTimeSpanSelection == true && searchData.IsGroupTagSelection == true)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingTime >= TaggingStartTime && t.TaggingTime <= TaggingEndTime &&
searchData.ArtworkPoolId.Contains(t.Artwork.ArtworkPool.ArtworkPoolId) &&
t.TaggingGroup.GroupTags.Any(g => searchData.GroupTagId.Contains(g.GroupTagId))
).ToList();
}
// ArtworkId == Auswahl && TimeSpan == Zeitraum && GroupTagId == Alle
else if (searchData.IsArtworkSelection == true && searchData.IsTimeSpanSelection == true && searchData.IsGroupTagSelection == false)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingTime >= TaggingStartTime && t.TaggingTime <= TaggingEndTime &&
searchData.ArtworkPoolId.Contains(t.Artwork.ArtworkPool.ArtworkPoolId)).ToList();
}
// ArtworkId == Auswahl && TimeSpan == keine Einschränkung && GroupTagId == Auswahl
else if (searchData.IsArtworkSelection == true && searchData.IsTimeSpanSelection == false && searchData.IsGroupTagSelection == true)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingGroup.GroupTags.Any(g => searchData.GroupTagId.Contains(g.GroupTagId) &&
searchData.ArtworkPoolId.Contains(t.Artwork.ArtworkPool.ArtworkPoolId))
).ToList();
}
// ArtworkId == Alle && TimeSpan == Zeitraum && GroupTagId == Auswahl
else if (searchData.IsArtworkSelection == false && searchData.IsTimeSpanSelection == true && searchData.IsGroupTagSelection == true)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingTime >= TaggingStartTime && t.TaggingTime <= TaggingEndTime &&
t.TaggingGroup.GroupTags.Any(g => searchData.GroupTagId.Contains(g.GroupTagId))
).ToList();
}
// ArtworkId == Auswahl && TimeSpan == keine Einschränkung && GroupTagId == Alle
else if (searchData.IsArtworkSelection == true && searchData.IsTimeSpanSelection == false && searchData.IsGroupTagSelection == false)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => searchData.ArtworkPoolId.Contains(t.Artwork.ArtworkPool.ArtworkPoolId))
.ToList();
}
// ArtworkId == Alle && TimeSpan == Zeitraum && GroupTagId == Alle
else if (searchData.IsArtworkSelection == false && searchData.IsTimeSpanSelection == true && searchData.IsGroupTagSelection == false)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingTime >= TaggingStartTime && t.TaggingTime <= TaggingEndTime
).ToList();
}
// ArtworkId == Alle && TimeSpan == keine Einschränkung && GroupTagId == Auswahl
else if (searchData.IsArtworkSelection == false && searchData.IsTimeSpanSelection == false && searchData.IsGroupTagSelection == true)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.Where(t => t.TaggingGroup.GroupTags.Any(g => searchData.GroupTagId.Contains(g.GroupTagId))
).ToList();
}
// ArtworkId == Alle && TimeSpan == keine Einschränkung && GroupTagId == Alle
else if (searchData.IsArtworkSelection == false && searchData.IsTimeSpanSelection == false && searchData.IsGroupTagSelection == false)
{
taggings = context.Taggings.Include("Artwork").Include("TaggingGroup")
.ToList();
}
var modelGroupedByImage = new List <TagAnalysisImage>();
var model = new List <TagList>();
foreach (var tag in taggings)
{
var imageTags = new TagAnalysisImage
{
Artwork = tag.Artwork.Title,
ArtworkID = tag.ArtworkId.Value,
ImagePoolName = tag.Artwork.ArtworkPool.Name,
};
var tam = new TagList
{
Tag = tag.Tag,
Anzahl = 1,
};
if (modelGroupedByImage.Exists(x => x.ArtworkID == imageTags.ArtworkID && x.ImagePoolName == imageTags.ImagePoolName))
{
model = modelGroupedByImage.First(x => x.ArtworkID == imageTags.ArtworkID && x.ImagePoolName == imageTags.ImagePoolName).Tags.ToList();
if (model.Exists(x => x.Tag == tam.Tag))
{
model.Find(x => x.Tag == tam.Tag).Anzahl++;
}
else
{
model.Add(tam);
}
modelGroupedByImage.First(x => x.ArtworkID == imageTags.ArtworkID && x.ImagePoolName == imageTags.ImagePoolName).Tags = model;
}
else
{
var taglist = new List <TagList>();
taglist.Add(tam);
imageTags.Tags = taglist;
modelGroupedByImage.Add(imageTags);
}
}
;
// Bilder alphabetisch sortieren
modelGroupedByImage = modelGroupedByImage.OrderBy(x => x.Artwork).ToList();
// tags nach Anzahl der Nennungen sortieren
foreach (var image in modelGroupedByImage)
{
image.Tags = image.Tags.OrderByDescending(x => x.Anzahl).ToList();
}
;
TagDbContext db = new TagDbContext();
var img_model = db.Images.ToList();
ViewBag.Taglist = modelGroupedByImage;
return(View(img_model));
}
public async Task <ActionResult> Login(LoginViewModel model, string returnUrl)
{
if (ModelState.IsValid)
{
var user = await UserManager.FindAsync(model.UserName, model.Password);
if (user != null)
{
// Trick:den root-user als UserAdmin eintragen, falls noch nicht passiert
if (user.UserName.Equals("root"))
{
var RoleManager = new RoleManager <IdentityRole>(new RoleStore <IdentityRole>(new UserDbContext()));
RoleManager.Create(new IdentityRole(AccountController.RoleAnalyst));
RoleManager.Create(new IdentityRole(AccountController.RoleArtworkAdmin));
RoleManager.Create(new IdentityRole(AccountController.RoleTagger));
RoleManager.Create(new IdentityRole(AccountController.RoleTaggingAdmin));
RoleManager.Create(new IdentityRole(AccountController.RoleUserAdmin));
UserManager.AddToRole(user.Id, AccountController.RoleUserAdmin);
}
// Aktionen vor dem eigentlichen Login
var db = new TagDbContext();
// Die Gruppe raussuchen
var group = db.TaggingGroups.SingleOrDefault(g => g.UserName.Equals(model.UserName));
if (group != null && UserManager.IsInRole(user.Id, AccountController.RoleTagger))
{
var myOrg = db.TaggingOrgs.FirstOrDefault(o => o.Groups.Any(g => g.TaggingGroupId == group.TaggingGroupId));
// zu der Gruppe gibt es keine Org => dan zählt das generelle Ende
if (myOrg == null && DateTime.Now > group.UnitEnd)
{
return(RedirectToAction("Sorry", "Home", new { area = "" }));
}
// Gruppe mit Organisation => die Freiage durch Org
if (myOrg != null && DateTime.Now > group.CreatedAt)
{
return(RedirectToAction("Sorry", "Home", new { area = "" }));
}
}
await SignInAsync(user, model.RememberMe);
if (group != null && UserManager.IsInRole(user.Id, AccountController.RoleTagger))
{
return(RedirectToAction("Start", "Tagging", new { area = "Tagging" }));
}
// Prüfen, ob der User einer Organisation angehört, z.B. Schule / Leherer
var org = db.TaggingOrgs.SingleOrDefault(o => o.UserName.Equals(model.UserName));
if (org != null)
{
return(RedirectToAction("Start", "TaggingOrg", new { area = "Admin", id = org.TaggingOrgId }));
}
return(RedirectToAction("Index", "Home", new { area = "Admin" }));
}
else
{
if (model.UserName.Equals("root") && model.Password.Equals("p123456"))
{
var rootUser = new ApplicationUser()
{
UserName = model.UserName
};
var result = await UserManager.CreateAsync(rootUser, model.Password);
if (result.Succeeded)
{
await SignInAsync(rootUser, isPersistent : false);
// TODO: Rollen anlegen
var RoleManager = new RoleManager <IdentityRole>(new RoleStore <IdentityRole>(new UserDbContext()));
RoleManager.Create(new IdentityRole(AccountController.RoleAnalyst));
RoleManager.Create(new IdentityRole(AccountController.RoleArtworkAdmin));
RoleManager.Create(new IdentityRole(AccountController.RoleTagger));
RoleManager.Create(new IdentityRole(AccountController.RoleTaggingAdmin));
RoleManager.Create(new IdentityRole(AccountController.RoleUserAdmin));
var tempUser = UserManager.FindByName(rootUser.UserName);
UserManager.AddToRole(tempUser.Id, AccountController.RoleUserAdmin);
return(RedirectToAction("Index", "Home"));
}
else
{
AddErrors(result);
}
}
else
{
ModelState.AddModelError("", "Invalid username or password.");
}
}
}
// If we got this far, something failed, redisplay form
return(View(model));
}
private static void ExportToDB(string tag_name, Dictionary <DateTime, double> solution, TagDbContext context)
{
foreach (DateTime key in solution.Keys)
{
var value = context.TagValues.Where(v => v.Tag.TagName == tag_name && v.DateTime == key).FirstOrDefault();
if (value != null)
{
value.Value = Math.Round(solution[key], 2);
}
else
{
Tag tag = context.Tags.Where(t => t.TagName == tag_name).First();
value = new TagValue()
{
DateTime = key, Tag = tag, Value = Math.Round(solution[key], 2)
};
context.TagValues.Add(value);
}
context.SaveChanges();
}
}
public ActionResult ShowInBrowser(TagSearchModel searchData)
{
var TaggingStartTime = searchData.TaggingDateBegin.Add(searchData.TaggingTimeBegin);
var TaggingEndTime = searchData.TaggingDateEnd.Add(searchData.TaggingTimeEnd);
var context = new TagDbContext();
bool errorExist = false;
// 1. Vorbedingungen prüfen
// wenn Auswahl, dann muss auch was ausgewählt sein => sonst Fehler!
// wenn Zeitraum, dann muss startTime <= endTime sein => sonst fehler
/*
* Anzeige, wenn "Alle" gewählt ist und trotzdem Elemente der Liste angeklickt wurden.
* Nur zum Verständnis, wenn Liste nicht die gewünschten Ergebnisse anzeigt.
*
* if (searchData.IsArtworkSelection == false && searchData.ArtworkPoolId != null)
* {
* ModelState.AddModelError("ArtworkPoolId", "Sie haben in der Liste eine Auswahl getroffen, aber bereits 'Alle' gewählt.");
* }
*
* if (searchData.IsGroupTagSelection == false && searchData.GroupTagId != null)
* {
* ModelState.AddModelError("GroupTagId", "Sie haben in der Liste eine Auswahl getroffen, aber bereits 'Alle' gewählt.");
* }
*/
if (searchData.IsArtworkSelection == true && (searchData.ArtworkPoolId == null || searchData.ArtworkPoolId.Count() == 0))
{
ModelState.AddModelError("ArtworkPoolId", "Bitte wählen Sie Elemente aus der Liste.");
errorExist = true;
}
if (searchData.IsTimeSpanSelection == true && (TaggingStartTime >= TaggingEndTime))
{
ModelState.AddModelError("TaggingDateBegin", "Die Startzeit muss vor der Endzeit liegen.");
errorExist = true;
}
if (searchData.IsGroupTagSelection == true && (searchData.GroupTagId == null || searchData.GroupTagId.Count() == 0))
{
ModelState.AddModelError("GroupTagId", "Bitte wählen Sie Elemente aus der Liste.");
errorExist = true;
}
if (errorExist)
{
// das Modell für die Anzeige muss jetzt wieder gebaut werden
ViewBag.ArtworkPoolId = new MultiSelectList(db.ArtworkPools, "ArtworkPoolId", "Name", null);
ViewBag.GroupTagId = new MultiSelectList(db.GroupTags, "GroupTagId", "Tag", null);
return(View(searchData));
}
return(TaggingAnalysisWeb(searchData));
}
/// <summary>
/// Функция расичтывает показатели и записывает в БД
/// </summary>
/// <param name="in_sum">список входящих тэгов, которые будут суммироваться</param>
/// <param name="in_div">список входящих тэгов, на сумму которых будут делить</param>
/// <param name="outtag">тэг, по которому будет записан результат</param>
public static void CalculateAndExport_v2(string[] in_sum, string[] in_div, string outtag)
{
TagDbContext context = new TagDbContext();
Tag tag = context.Tags.Where(t => t.TagName == outtag).First();
var values = context.TagValues
.Where(t => t.Tag.TagName == outtag)
.OrderByDescending(t => t.DateTime)
.Select(t => t.DateTime);
DateTime date = values.Count() > 0 ? values.First() : new DateTime(2012, 10, 1);//DateTime.Today; // <---- заменить DateTime на Today
string query1 = string.Format("SELECT DateTime, SUM(Value) as Value FROM dbo.AnalogHistory WHERE TagName in ( '{0}' ) AND DateTime > @date AND Quality = 0 AND wwRetrievalMode = 'Cyclic' AND wwResolution=3600000 GROUP BY DateTime", string.Join("','", in_sum));
string query2 = string.Format("SELECT DateTime, SUM(Value) as Value FROM dbo.AnalogHistory WHERE TagName in ( '{0}' ) AND DateTime > @date AND Quality = 0 AND wwRetrievalMode = 'Cyclic' AND wwResolution=3600000 GROUP BY DateTime", string.Join("','", in_div));
List <TagValue> data1 = new List <TagValue>();
List <TagValue> data2 = new List <TagValue>();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SourceDB"].ConnectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(query1, conn))
{
command.CommandTimeout = 240000;
command.Parameters.AddWithValue("@date", date);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
DateTime d = reader.GetDateTime(0);
data1.Add(new TagValue()
{
DateTime = reader.GetDateTime(0), Value = reader.GetDouble(1)
});
}
reader.Close();
}
using (SqlCommand command = new SqlCommand(query2, conn))
{
command.CommandTimeout = 240000;
command.Parameters.AddWithValue("@date", date);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
data2.Add(new TagValue()
{
DateTime = reader.GetDateTime(0), Value = reader.GetDouble(1)
});
}
reader.Close();
}
}
var data_out = data1
.Join(data2,
d1 => d1.DateTime,
d2 => d2.DateTime,
(d1, d2) => new { DateTime = d1.DateTime, Value = (d2.Value == 0 ? 0 : d1.Value / d2.Value) });
foreach (var row in data_out)
{
TagValue value = new TagValue()
{
DateTime = row.DateTime, Tag = tag, Value = row.Value
};
context.TagValues.Add(value);
}
context.SaveChanges();
}
