/// <summary>
///
/// </summary>
/// <param name="pn"></param>
/// <param name="ts"></param>
/// <param name="pv"></param>
/// <param name="mi"></param>
/// <param name="pi"></param>
/// <returns></returns>
public int AddAvgRd(string pn, DateTime ts, double pv, params int[] mi_pi)
{
try
{
if (IsAvgRdExisted(pn, ts) == false)
{
//not existed
StringBuilder sb = new StringBuilder();
sb.Append("insert into PIAvgRecords(pname,timestamps,pvalue,updatetime) values('");
sb.Append(pn.ToString() + "','");
sb.Append(ts.ToString("yyyy-MM-dd HH:mm:ss") + "',");
sb.Append(pv.ToString() + ",'");
sb.Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')");
Database db = DatabaseFactory.CreateDatabase("dbconn");
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
db.ExecuteNonQuery(dbc);
return 0;
}
else
{
return -1;
}
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "AddAvgRd?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return -1;
}
}
/// <summary>
///
/// </summary>
public List<string> LoadOutputWebConfig()
{
try
{
List<string> output_web_config = new List<string>();
AppSettingsReader asr = new AppSettingsReader();
string filename = (string)asr.GetValue("OutputWeb", typeof(string));
FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
StreamReader sr = new StreamReader(fs, System.Text.Encoding.Default);
string tempstr;
while ((tempstr = sr.ReadLine()) != null)
{
if ((tempstr.Trim() != "") && (tempstr.Trim().Substring(0, 2) != "//"))
{
output_web_config.Add(tempstr);
}
}
sr.Close();
fs.Close();
return output_web_config;
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "LoadOutputWebConfig?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return null;
}
}
/// <summary>
/// update PI data into db 20150826
/// </summary>
/// <param name="ts"></param>
/// <param name="pn"></param>
/// <param name="pv"></param>
/// <param name="mi"></param>
/// <param name="pi"></param>
/// <returns></returns>
public int UpdatePiRecord(DateTime ts, string pn, float pv, int mi, int pi)
{
try
{
if (IsPiRdExisted(ts, pn, mi, pi) == true)
{
//existed, modify the following code to update functionality
StringBuilder sb = new StringBuilder();
sb.Append("update PiRecords set pvalue=" + pv.ToString() + " where ");
sb.Append("pname = '" + pn + "' and ");
sb.Append("timestamps = '" + ts.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("machineid =" + mi.ToString() + " and ");
sb.Append("plantid=" + pi.ToString());
Database db = DatabaseFactory.CreateDatabase("dbconn");
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
db.ExecuteNonQuery(dbc);
return 0;
}
else
{
return AddPiRecord(ts, pn, pv, mi, pi);
}
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "UpdatePiRecord?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return -1;
}
}
/// <summary>
///
/// </summary>
/// <param name="ts"></param>
/// <param name="pn"></param>
/// <param name="mi"></param>
/// <param name="pi"></param>
/// <returns></returns>
public bool? IsPiRdExisted(DateTime ts, string pn, int mi, int pi)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append("select count(*) from PiRecords t where ");
sb.Append("t.pname = '" + pn + "' and ");
sb.Append("t.timestamps = '" + ts.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.machineid =" + mi.ToString() + " and ");
sb.Append("t.plantid=" + pi.ToString());
Database db = DatabaseFactory.CreateDatabase("dbconn");
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
if ((int)db.ExecuteScalar(dbc) > 0)
{
return true;
}
return false;
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "IsPiRdExisted?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return null;
}
}
/// <summary>
/// 标定时间区间记录是否存在
/// </summary>
/// <param name="pn"></param>
/// <param name="st"></param>
/// <param name="et"></param>
/// <returns></returns>
public bool? IsCalibSpanRdExisted(string pn, DateTime st, DateTime et)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append("select count(*) from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime = '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime = '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "'");
Database db = DatabaseFactory.CreateDatabase("dbconn");
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
if ((int)db.ExecuteScalar(dbc) > 0)
{
return true;
}
return false;
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "IsCalibSpanRdExisted?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return null;
}
}
/// <summary>
/// 获得单条calib average value记录
/// </summary>
/// <param name="pn"></param>
/// <param name="ts"></param>
/// <returns></returns>
public DataSet GetSingleCalibAvgRd(string pn, DateTime ts)
{
try
{
StringBuilder sb = new StringBuilder();
Database db = DatabaseFactory.CreateDatabase("dbconn");
sb.Append("select * from CalibRuleValue t where ");
sb.Append("t.pname = '" + pn + "' and ");
sb.Append("t.timestamps = '" + ts.ToString("yyyy-MM-dd HH:mm:ss") + "'");
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
return db.ExecuteDataSet(dbc);
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "AddPiRecord?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return null;
}
}
/// <summary>
///
/// </summary>
/// <param name="mi"></param>
/// <param name="indicatorid"></param>
/// <param name="ts"></param>
/// <returns></returns>
public DataSet GetSingleAvgRd_Web(string mi, string indicatorid, DateTime ts)
{
try
{
StringBuilder sb = new StringBuilder();
Database db = DatabaseFactory.CreateDatabase("dbconn");
sb.Append("select indicatorvalue from EnvirIndicatorValue t where ");
//modified20160107
//sb.Append("substring(t.pointname,1,1) = '" + mi + "' and ");
sb.Append("t.pointname = '" + mi + "' and ");
sb.Append("t.indicatorid = " + indicatorid + " and ");
sb.Append("t.timestamps = '" + ts.ToString("yyyy-MM-dd HH:mm:ss") + "'");
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
return db.ExecuteDataSet(dbc);
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "AddSingleAvgRd_Web?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return null;
}
}
/// <summary>
/// 获得相关联的区间
/// </summary>
/// <param name="pn"></param>
/// <param name="st"></param>
/// <param name="et"></param>
/// <param name="rt"></param>
/// <returns></returns>
public DataSet GetRelatedSpans(string pn, DateTime st, DateTime et, RelatedType rt)
{
try
{
StringBuilder sb = new StringBuilder();
Database db = DatabaseFactory.CreateDatabase("dbconn");
//cst<=hst and cet>=het
if (rt == RelatedType.OutSide)
{
sb.Append("select * from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime <= '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime >= '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
//cst<=hst and (cet<het and cet>hst)
else if (rt == RelatedType.ForeIntersect)
{
sb.Append("select * from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime <= '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime < '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime > '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
//(cst>hst and cst<het) and cet>=het
else if (rt == RelatedType.BackIntersect)
{
sb.Append("select * from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime > '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.starttime < '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime >= '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
//cst>hst and cet<het
else if (rt == RelatedType.Inside)
{
sb.Append("select * from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime > '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime < '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
return db.ExecuteDataSet(dbc);
//return null;
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "GetRelatedSpans?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return null;
}
}
/// <summary>
/// get machine stop span rd from db
/// </summary>
/// <param name="st"></param>
/// <param name="et"></param>
/// <returns></returns>
public DataSet GetRelatedMachineStopRd(DateTime st, DateTime et)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append("select t.pointname,t.starttime,t.endtime,isnull(t.grouptype,'') as grouptype from machine_startstop t where (t.starttime>='");
sb.Append(st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.starttime<='" + et.ToString("yyyy-MM-dd HH:mm:ss") + "') or ");
sb.Append("(t.endtime>='");
sb.Append(st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime<='" + et.ToString("yyyy-MM-dd HH:mm:ss") + "') or ");
sb.Append("(t.starttime<'");
sb.Append(st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime>'" + et.ToString("yyyy-MM-dd HH:mm:ss") + "')");
Database db = DatabaseFactory.CreateDatabase("dbconn");
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
return db.ExecuteDataSet(dbc);
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "AddPiRecord?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return null;
}
}
/// <summary>
///
/// </summary>
/// <param name="machineid"></param>
/// <returns></returns>
public DataSet GetPointsOfMachine_avg(int machineid)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append("select t.pointname,s.shiftsecs from point_machine_map t inner join machineshiftsecs s on t.machineid=s.machineid where t.houravg=1 and t.machineid=");
sb.Append(machineid.ToString());
Database db = DatabaseFactory.CreateDatabase("dbconn");
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
return db.ExecuteDataSet(dbc);
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "AddPiRecord?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return null;
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public DataSet GetPIPoints()
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from point_machine_map");
Database db = DatabaseFactory.CreateDatabase("dbconn");
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
return db.ExecuteDataSet(dbc);
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "GetPiPoints?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return null;
}
}
/// <summary>
/// 获取标定结束时间对应的开始记录
/// </summary>
/// <param name="pn"></param>
/// <param name="et"></param>
/// <returns></returns>
public DataSet GetFirstCalibBeginRd(string pn, DateTime et)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append("select top 1 * from t_RulelogS t where t.rulename='");
sb.Append(pn + "' and ");
sb.Append("t.timelog<'" + et.ToString("yyyy-MM-dd HH:mm:ss") + "' and t.AlarmLog='仪表标定' order by t.timelog desc");
Database db = DatabaseFactory.CreateDatabase("dbconn");
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
return db.ExecuteDataSet(dbc);
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "AddPiRecord?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return null;
}
}
/// <summary>
///
/// </summary>
/// <param name="ts"></param>
/// <param name="pn"></param>
/// <param name="pv"></param>
/// <param name="mi"></param>
/// <param name="pi"></param>
/// <returns></returns>
public int AddPiRecord_Remote(DateTime ts, string pn, float pv, int mi, int pi)
{
try
{
if (IsPiRdExisted(ts, pn, mi, pi) == false)
{
//not existed
StringBuilder sb = new StringBuilder();
sb.Append("insert into PiRecords(pname,timestamps,pvalue,updatetime,machineid,plantid) values('");
sb.Append(pn.ToString() + "','");
sb.Append(ts.ToString("yyyy-MM-dd HH:mm:ss") + "',");
sb.Append(pv.ToString() + ",'");
sb.Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',");
sb.Append(mi.ToString() + ",");
sb.Append(pi.ToString() + ")");
Database db = DatabaseFactory.CreateDatabase("dbconn_r");
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
db.ExecuteNonQuery(dbc);
return 0;
}
else
{
return -1;
}
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "AddPiRecord_Remote?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return -1;
}
}
/// <summary>
/// 添加标定时间区间记录
/// </summary>
/// <param name="pn"></param>
/// <param name="st"></param>
/// <param name="et"></param>
/// <returns></returns>
public int AddCalibSpanRd(string pn, DateTime st, DateTime et)
{
try
{
if (IsCalibSpanRdExisted(pn, st, et) == false)
{
//not existed
StringBuilder sb = new StringBuilder();
sb.Append("insert into CalibRecords(pointid,starttime,endtime,updatetime) values('");
sb.Append(pn.ToString() + "','");
sb.Append(st.ToString("yyyy-MM-dd HH:mm:ss") + "','");
sb.Append(et.ToString("yyyy-MM-dd HH:mm:ss") + "','");
sb.Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')");
Database db = DatabaseFactory.CreateDatabase("dbconn");
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
db.ExecuteNonQuery(dbc);
return 0;
}
else
{
return -1;
}
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "AddCalibSpanRd?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return -1;
}
}
