public static async Task <List <alarm> > SelectAlarms(string DB, string where, int from)
{
int i = 0;
List <alarm> alarms = new List <alarm>();
List <alarm_texts> titles = SelectAlarmsTexts(DB);
string connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
"192.168.2.12", 5432, "postgres", "Nordit0276", DB);
NpgsqlConnection conn = new NpgsqlConnection(connstring);
string sql = "SELECT * FROM alarm_history WHERE origin_pktime>" + from + " AND (" + where + ") ORDER BY origin_pktime DESC";
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
//Prepare DataReader
System.Data.Common.DbDataReader dr = await cmd.ExecuteReaderAsync();
while (await dr.ReadAsync())
{
alarm alarm = new alarm();
alarm.id = short.Parse(dr["alarm_id"].ToString());
int id = alarm.id;
//small improvment beacause alarm_id in table alarm_texts and alarm_id in table alarm_history are bind
alarm.title = titles[id - 1].title;
int originTime = int.Parse(dr["origin_pktime"].ToString());
alarm.originTime = pkTimeToDateTime(originTime);
int expTime = Int32.Parse(dr["expiry_pktime"].ToString());
alarm.expiryTime = pkTimeToDateTime(expTime);
alarms.Add(alarm);
i++;
}
conn.Close();
return(alarms);
}
public void saveChanges()
{
using (var db = new PlanningContext())
{
alarm data = null;
if (!this._isCreateMode)
{
var id = Convert.ToInt32(this.alarm_id.Text);
data = db.alarms.Where(d => d.alarm_id == id).FirstOrDefault();
if (data == null)
{
this._mainInterface.statusText = $"ERROR: ID '{this.alarm_id.Text}' does not exist.";
return;
}
}
else
{
data = new alarm();
}
data.alarm_id = Convert.ToInt32(this.alarm_id.Text);
data.value = Convert.ToDouble(this.value.Text);
data.comment = /**/ (this.comment.Text);
data.is_active = (bool)this.is_active.IsChecked;
data.alarm_type = new Func <alarm_type>(() => { foreach (var v in db.alarm_type)
{
if (v.alarm_type_id == (this.alarm_type.item as alarm_type).alarm_type_id)
{
return(v);
}
}
return(null); })();
data.device = new Func <device>(() => { foreach (var v in db.devices)
{
if (v.device_id == (this.device.item as device).device_id)
{
return(v);
}
}
return(null); })();
data.group_type = new Func <group_type>(() => { foreach (var v in db.group_type)
{
if (v.group_type_id == (this.group_type.item as group_type).group_type_id)
{
return(v);
}
}
return(null); })();
if (this._isCreateMode)
{
db.alarms.Add(data);
}
db.SaveChanges();
}
}
public void deleteItem(object item)
{
if (item == null)
{
return;
}
alarm data = item as alarm;
if (data == null)
{
return;
}
using (var db = new PlanningContext())
{
db.alarms.Remove(db.alarms.Where(d => d.alarm_id == data.alarm_id).First());
db.SaveChanges();
}
}
private void btn_alarm_Click(object sender, EventArgs e)
{
ButtonReset();
if (ActiveMdiChild != null)
{
if (this.ActiveMdiChild != mchildForm_Alarm)
{
//ActiveMdiChild.Close();
ActiveMdiChild.Hide();
}
mchildForm_Alarm = ShowActiveForm(mchildForm_Alarm, typeof(alarm)) as alarm;
mchildForm_Alarm.Show();
}
else
{
mchildForm_Alarm = ShowActiveForm(mchildForm_Alarm, typeof(alarm)) as alarm;
mchildForm_Alarm.Show();
}
btn_alarm.BackColor = Color.Red;
}
/// <summary>
/// Method to get alarms
/// </summary>
/// <param name="DB">db name</param>
/// <param name="count">count of alarms that you want to select</param>
/// <param name="ids"></param>
/// <returns></returns>
public async Task <List <alarm> > SelectAlarms(string DB, int offsetPage = 0, int count = 0, List <int> ids = null, int plcID = 1, string lang = "en")
{
int i = 0;
string sql = string.Empty;
string whereIds = string.Empty;
List <alarm> alarms = new List <alarm>();
List <alarm_texts> titles = SelectAlarmsTexts(DB, null, plcID, lang);
string connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
"192.168.2.12", 5432, "postgres", "Nordit0276", DB);
NpgsqlConnection conn = new NpgsqlConnection(connstring);
if (ids != null)
{
foreach (int id in ids)
{
whereIds += "alarm_id=" + id.ToString() + " OR ";
}
whereIds = whereIds.Substring(0, whereIds.Length - 4);
// Execute the query and obtain a result set
sql = string.Format("SELECT * FROM alarm_history WHERE {0} AND plc_id={1} ORDER BY origin_pktime DESC LIMIT {2}", whereIds, plcID, count);
}
else
{
if (count != 0)
{
sql = string.Format("SELECT * FROM alarm_history WHERE plc_id={0} ORDER BY origin_pktime DESC LIMIT {1}", plcID, count);
}
else
{
sql = "SELECT * FROM alarm_history ORDER BY origin_pktime DESC";
}
}
if (offsetPage > 0 && count > 0)
{
if (ids != null)
{
foreach (int id in ids)
{
whereIds += "alarm_id=" + id.ToString() + " OR ";
}
// Execute the query and obtain a result set
sql = string.Format("SELECT * FROM alarm_history WHERE plc_id={0} ORDER BY origin_pktime DESC LIMIT {1} OFFSET {2}", plcID, whereIds, count * offsetPage);
}
sql = string.Format("SELECT * FROM alarm_history WHERE plc_id={0} ORDER BY origin_pktime DESC LIMIT {1} OFFSET {2}", plcID, count, count * offsetPage);
}
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
//Prepare DataReader
System.Data.Common.DbDataReader dr = await cmd.ExecuteReaderAsync();
while (await dr.ReadAsync())
{
alarm alarm = new alarm();
alarm.id = short.Parse(dr["alarm_id"].ToString());
int id = alarm.id;
//small improvment beacause alarm_id in table alarm_texts and alarm_id in table alarm_history are bind
if (titles.Where(p => (p.id) == id && p.plcID == plcID && p.lang == lang).Count() == 1)
{
alarm.title = titles.Single(p => p.id == id && p.plcID == plcID && p.lang == lang).title;
}
else
{
alarm.title = "Title does not match with any of alarm id in texts and in db. DB id:" + id;
}
int originTime = int.Parse(dr["origin_pktime"].ToString());
int expTime = Int32.Parse(dr["expiry_pktime"].ToString());
alarm.originTime = pkTimeToDateTime(originTime);
alarm.expiryTime = pkTimeToDateTime(expTime);
alarms.Add(alarm);
i++;
}
conn.Close();
return(alarms);
}
//[ValidateAntiForgeryToken]
public JObject RecvAlarm()
{
/* 获取报警信息 */
var ip = Request.UserHostAddress;
string deviceID = null;
string p2pID = null;
string token = null;
string algID = null;
long timestamp = 0;
string image = null;
string msg = null;
string appendix = null;
Request.InputStream.Position = 0;
using (StreamReader inputStream = new StreamReader(Request.InputStream))
{
String str = inputStream.ReadToEnd();
JObject jo = (JObject)JsonConvert.DeserializeObject(str);
try { deviceID = jo["deviceID"].ToString(); }
catch (Exception ex) { Console.WriteLine(ex.Message); }
try { p2pID = jo["p2pID"].ToString(); }
catch (Exception ex) { Console.WriteLine(ex.Message); }
try { token = jo["token"].ToString(); }
catch (Exception ex) { Console.WriteLine(ex.Message); }
try { algID = jo["algID"].ToString(); }
catch (Exception ex) { Console.WriteLine(ex.Message); }
try { timestamp = long.Parse(jo["timestamp"].ToString()); }
catch (Exception ex) { Console.WriteLine(ex.Message); }
try { image = jo["image"].ToString(); }
catch (Exception ex) { Console.WriteLine(ex.Message); }
try { msg = jo["msg"].ToString(); }
catch (Exception ex) { Console.WriteLine(ex.Message); }
try { appendix = jo["appendix"].ToString(); }
catch (Exception ex) { Console.WriteLine(ex.Message); }
}
/* 存储报警 */
if (deviceID != null || p2pID != null)
{
//保存目录
string dir = "Public/AlarmSnap/";
//站点文件目录
string fileDir = HttpRuntime.AppDomainAppPath + dir; // HttpContext.Current.Server.MapPath("~" + dir);
if (!Directory.Exists(fileDir))
{
Directory.CreateDirectory(fileDir);
}
//文件名称
string fileName = DateTime.Now.ToString("yyyyMMdd_HHmmss") + "_" + timestamp.ToString() + ".jpg";
//保存文件所在站点位置
string filePath = Path.Combine(fileDir, fileName);
try
{
if (image != null) //保存报警截图
{
byte[] b = Convert.FromBase64String(image);
MemoryStream ms = new MemoryStream(b);
Bitmap bitmap = new Bitmap(ms);
bitmap.Save(filePath, System.Drawing.Imaging.ImageFormat.Jpeg);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace + ex.Message);
}
try
{
alarm alarm = new alarm();
//alarm.id = db.alarm.Count() + 1;
alarm.device_id = deviceID;
alarm.p2p_id = p2pID;
alarm.ip = ip;
alarm.token = token;
alarm.algorithm_id = algID;
alarm.timestamp = timestamp;
alarm.image = filePath;
alarm.message = msg;
alarm.appendix = appendix;
alarm.state = "red";
var query = from device in db.device
where device.ip == ip
select new
{ device.site, device.alias };
var queryList = query.ToList();
if (queryList.Count > 0)
{
alarm.site = queryList[0].site; //tostring
alarm.alias = queryList[0].alias; //tostring
}
//alarm.type
DateTime dtStart = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 1, 1));
TimeSpan toNow = new TimeSpan(timestamp);
alarm.datetime = dtStart.Add(toNow);
alarm.confirmed = 0;
db.alarm.Add(alarm);
db.SaveChanges();
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace + ex.Message);
}
}
/* 发送返回信息 */
JObject postedJObject = new JObject();
if (token == null || token == "")
{
postedJObject.Add("err", 411);
postedJObject.Add("errMsg", "token错误");
}
else
{
postedJObject.Add("err", 0);
postedJObject.Add("errMsg", "ok");
}
return(postedJObject);
}
