internal static void LoadTriplet(Triplet t, SqlConnection con)
{
if (t == null) { return; }
try {
using (SqlCommand command = con.CreateCommand())
{
command.CommandText = string.Format("select max(metrixid) from metrix where entityId = {0} and predicateId={1} and metrixobject = '{2}'",
t.EntityId, t.PredicateId, t.MetrixObject);
var o = command.ExecuteScalar();
if (o == null || o is DBNull)
{
if (t.MetrixDate == null)
{
command.CommandText = "insert into metrix(entityId, predicateId, metrixobject, metrixValue) values ("
+ t.EntityId + "," + t.PredicateId + ",'" + t.MetrixObject + "', " + t.MetrixValue.ToString().Replace(",",".") + ")";
}
else
{ //yyyy-mm-dd hh:mi:ss
command.CommandText = "insert into metrix(entityId, predicateId, metrixobject, metrixValue, metrixData) values ("
+ t.EntityId + "," + t.PredicateId + ",'" + String.Format("{0:yyyy/MM/dd H:mm:ss}", t.MetrixDate) + "', " + t.MetrixValue.ToString().Replace(",", ".") +
string.Format(", convert(datetime, '{0}-{1}-{2} {3}:{4}:{5}', 120))", t.MetrixDate.Value.Year, t.MetrixDate.Value.Month,
t.MetrixDate.Value.Day, t.MetrixDate.Value.Hour, t.MetrixDate.Value.Minute, t.MetrixDate.Value.Second);
}
command.ExecuteNonQuery();
}
}
}
catch{}
}
internal static void SetMetrix(Triplet triplet, SqlConnection con)
{
if (triplet == null) { return; }
using (SqlCommand command = con.CreateCommand())
{
command.CommandText = String.Format("select metrixid from metrix where predicateid={0} and entityid = {1}",
triplet.PredicateId, triplet.EntityId);
var s = String.IsNullOrEmpty(triplet.MetrixObject) ? string.Empty : triplet.MetrixObject;
command.CommandText += triplet.MetrixDate == null
? " and metrixObject ='" + s + "'"
: String.Format(" and metrixData=convert(datetime,'{0}-{1}-{2} {3}:{4}:{5}', 120)",
triplet.MetrixDate.Value.Year, triplet.MetrixDate.Value.Month, triplet.MetrixDate.Value.Day,
triplet.MetrixDate.Value.Hour, triplet.MetrixDate.Value.Minute, triplet.MetrixDate.Value.Second);
var o = command.ExecuteScalar();
if (o == null)
{
if (triplet.MetrixValue != 0)
{
LoadTriplet(triplet, con);
}
}
else
{
if (triplet.MetrixValue != 0)
{
command.CommandText = string.Format("update metrix set metrixvalue={0} where metrixid = {1}",
triplet.MetrixValue, (int)o);
}
else
{
command.CommandText = string.Format("delete metrix where metrixid = {0}", (int)o);
}
}
command.ExecuteNonQuery();
}
}
private void OnSaveButtonClick(object sender, EventArgs e)
{
object[][] selObj = objects.Where(o => (bool)((object[])o)[2]).ToArray();
using (var con = DataHelper.OpenOrCreateDb())
{
int formPredicateId = DataHelper.GetParameter(cbParameters.Text, con, true);
foreach (var d in itogData)
{
for (int i = 1; i < d.Length; i++)
{
Triplet t = new Triplet((int)selObj[i - 1][0], formPredicateId, d[0].ToString(), (decimal)d[i]);
DateTime dat;
if (DateTime.TryParse(d[0].ToString(), out dat))
{
t.MetrixDate = dat;
}
DataHelper.SetMetrix(t, con);
}
}
}
MessageBox.Show("Сохранено " + itogData.Count + " объектов");
}
