public paraSeginfo GetParaSegInfo(TimeSeg seg) { paraSeginfo info = new paraSeginfo(); #region read collection condtion DbOperator opt = new DbOperator(); DataSet data = opt.CreateDataSetOra("select t.value_tag,r.gap_hdelay,r.gap_tdelay,r.ctrl_point,r.is_gap_judge,r.periodic,r.rst_value,r.gap_time,r.head_delay,r.tail_delay,r.batch_head_delay,r.batch_tail_delay from HT_QLT_COLLECTION r left join ht_pub_tech_para t on r.para_code = t.para_code where r.PARA_CODE = '" + seg.nodecode + "'"); if (data == null || data.Tables[0].Rows.Count == 0) { return(info); } DataRow row = data.Tables[0].Rows[0]; ////////////////////工艺点标签////////////////////////////////////////////// string tagname = row["value_tag"].ToString(); string interval = row["periodic"].ToString(); int timegap = Convert.ToInt32(row["gap_time"].ToString()); string tailRst = row["rst_value"].ToString(); ////////////////////是否为工艺段断流判定点///////////////////////////////////////////// bool gapctrl = ("1" == row["is_gap_judge"].ToString()); //////////////////////偏移///////////////////////////////////////////// int headDelay = Convert.ToInt32(row["head_delay"].ToString()); //料头 int tailDelay = Convert.ToInt32(row["tail_delay"].ToString()); //料尾 int batchheadDelay = Convert.ToInt32(row["batch_head_delay"].ToString()); //批头 int batchtailDelay = Convert.ToInt32(row["batch_tail_delay"].ToString()); //批尾 int gap_hdelay = Convert.ToInt32(row["gap_hdelay"].ToString()); //断流前 int gap_tdelay = Convert.ToInt32(row["gap_tdelay"].ToString()); //断流后 #endregion #region //////////////////////寻找数据批头尾、料头尾 以及断流信息/////////////////////////////////////////////////////////////////////// List <ParaInfo> paralist; if (seg.type == TimeSegType.ALL && Convert.ToDateTime(seg.starttime) - Convert.ToDateTime(seg.batchStart) < new TimeSpan(1, 0, 0)) { paralist = getParaRecord(tagname, "5", Convert.ToDateTime(seg.batchStart), Convert.ToDateTime(seg.endtime)); seg.type = TimeSegType.BEGIN; } else { paralist = getParaRecord(tagname, "5", Convert.ToDateTime(seg.starttime), Convert.ToDateTime(seg.endtime)); } if (gapctrl) { if (tagname != "" && tailRst != "" && interval != "") { info.batchBtime = info.tailBtime = seg.starttime; //初始化料头时间 info.batchEtime = info.tailEtime = seg.endtime; //初始化料尾时间 /////////自动寻找批头批尾///////// if (seg.type == TimeSegType.BEGIN || seg.type == TimeSegType.BOTH)//寻找批头 { DateTime temptime = Convert.ToDateTime(paralist.Find(s => s.value < Convert.ToDouble(tailRst)).timestamp); if (temptime - Convert.ToDateTime(seg.batchStart) > new TimeSpan(0, 30, 0)) { info.batchBtime = Convert.ToDateTime(seg.batchStart).AddSeconds(batchheadDelay).ToString("yyyy-MM-dd HH:mm:ss"); } else { temptime = temptime.AddSeconds(batchheadDelay); if (temptime - Convert.ToDateTime(seg.starttime) > new TimeSpan(0)) { info.batchBtime = temptime.ToString("yyyy-MM-dd HH:mm:ss"); } } if (Convert.ToDateTime(info.batchBtime) - Convert.ToDateTime(seg.starttime) < new TimeSpan(0)) { info.batchBtime = seg.starttime; } } if (seg.type == TimeSegType.END || seg.type == TimeSegType.BOTH)//寻找批尾 { double temp = paralist.ToArray()[paralist.Count - 1].value; if (temp >= Convert.ToDouble(tailRst)) { paralist = getParaRecord(tagname, "5", Convert.ToDateTime(seg.starttime), Convert.ToDateTime(seg.endtime).AddHours(1)); info.batchEtime = Convert.ToDateTime(paralist.Find(s => s.value > Convert.ToDouble(tailRst)).timestamp).AddSeconds(batchtailDelay).ToString("yyyy-MM-dd HH:mm:ss"); } else { info.batchEtime = Convert.ToDateTime(paralist.FindLast(s => s.value > Convert.ToDouble(tailRst)).timestamp).AddSeconds(batchtailDelay).ToString("yyyy-MM-dd HH:mm:ss"); } } ////////////////////////////////自动寻找料头料尾//////////////////////////////////////////////////////////////// if (seg.type == TimeSegType.BEGIN || seg.type == TimeSegType.BOTH)//寻找料头 { info.tailBtime = Convert.ToDateTime(paralist.Find(s => (s.value > Convert.ToDouble(tailRst) && Convert.ToDateTime(s.timestamp) - Convert.ToDateTime(info.batchBtime) >= new TimeSpan(0) && Convert.ToDateTime(s.timestamp) - Convert.ToDateTime(info.batchEtime) <= new TimeSpan(0))).timestamp).AddSeconds(headDelay).ToString("yyyy-MM-dd HH:mm:ss"); } if (seg.type == TimeSegType.END || seg.type == TimeSegType.BOTH)//寻找料尾 { info.tailEtime = Convert.ToDateTime(paralist.FindLast(s => s.value > Convert.ToDouble(tailRst) && Convert.ToDateTime(s.timestamp) - Convert.ToDateTime(info.batchBtime) >= new TimeSpan(0) && Convert.ToDateTime(s.timestamp) - Convert.ToDateTime(info.batchEtime) <= new TimeSpan(0)).timestamp).AddSeconds(tailDelay).ToString("yyyy-MM-dd HH:mm:ss"); } ////////////////////选取数据/判断是否断料,并记录下相应的断料时间////// info.paralist = paralist; info.gapinfo = GetGapTime(paralist, tailRst, gap_hdelay, gap_tdelay, timegap); info.tagname = tagname; info.interval = interval; //////将计算的时间存入数据库///////// //string[] bseg = { "ORDERNO", "PLANNO", "STARTTIME", "ENDTIME", "SECTION_CODE", "BATCH_BTIME", "BATCH_ETIME", "GAP_BTIME", "GAP_ETIME", "TYPE", }; //string[] bvalue = { "0", seg.planno, seg.starttime, seg.endtime, seg.nodecode.Substring(0, 5), info.batchBtime, info.batchEtime, info.tailBtime, info.tailEtime, "0" }; //opt.MergeInto(bseg, bvalue, 5, "HT_QLT_GAP_COLLECTION"); ////////////////////选取数据/判断是否断料,并记录下相应的断料时间////// gaptime = info.gapinfo; //int orderno = 1; //string[] gapseg = { "ORDERNO", "PLANNO", "STARTTIME", "ENDTIME", "SECTION_CODE", "GAPTIME ", "GAP_BTIME", "GAP_ETIME", "TYPE" }; //if (gaptime != null) //{ // foreach (Gaptime time in gaptime) // { // string[] gapvalue = { (orderno++).ToString(), seg.planno, seg.starttime, seg.endtime, seg.nodecode.Substring(0, 5), time.gaptime.ToString(), time.starttime, time.endtime, "1" }; // opt.MergeInto(gapseg, gapvalue, 5, "HT_QLT_GAP_COLLECTION"); // } //} } } else { //如果不是断流判定点,则读取该工艺段判定点的信息,获取批头批尾、料头料尾,以及断流区间信息 string ctrlpoint = row["ctrl_point"].ToString(); seg.nodecode = ctrlpoint; //DataSet gapdata = opt.CreateDataSetOra("select * from HT_QLT_GAP_COLLECTION where TYPE = '0' and PLANNO = '" + seg.planno + "' and STARTTIME = '" + seg.starttime + "' and ENDTIME = '" + seg.endtime + "' and SECTION_CODE = '" + ctrlpoint.Substring(0, 5) + "' "); //////如果数据库有己计算的断流判定点结果 //if (gapdata != null && gapdata.Tables[0].Rows.Count > 0) //{ // DataRow brow = gapdata.Tables[0].Rows[0]; // info.batchBtime = Convert.ToDateTime(brow["BATCH_BTIME"].ToString()).AddSeconds(batchheadDelay).ToString("yyyy-MM-dd HH:mm:ss"); // info.batchEtime = Convert.ToDateTime(brow["BATCH_ETIME"].ToString()).AddSeconds(batchtailDelay).ToString("yyyy-MM-dd HH:mm:ss"); // info.tailBtime = Convert.ToDateTime(brow["GAP_BTIME"].ToString()).AddSeconds(headDelay).ToString("yyyy-MM-dd HH:mm:ss"); // info.tailEtime = Convert.ToDateTime(brow["GAP_ETIME"].ToString()).AddSeconds(tailDelay).ToString("yyyy-MM-dd HH:mm:ss"); // gapdata = opt.CreateDataSetOra("select * from HT_QLT_GAP_COLLECTION where TYPE = '1' and PLANNO = '" + seg.planno + "' and STARTTIME = '" + seg.starttime + "' and ENDTIME = '" + seg.endtime + "' and SECTION_CODE = '" + ctrlpoint.Substring(0, 5) + "' "); // if (gapdata != null && gapdata.Tables[0].Rows.Count > 0) // { // if (gaptime == null) // gaptime = new List<Gaptime>(); // else // gaptime.Clear(); // foreach (DataRow grow in gapdata.Tables[0].Rows) // { // Gaptime time = new Gaptime(); // time.gaptime = Convert.ToInt32(grow["gaptime"].ToString()); // time.starttime = Convert.ToDateTime(grow["GAP_BTIME"].ToString()).AddSeconds(gap_hdelay).ToString("yyyy-MM-dd HH:mm:ss"); // time.endtime = Convert.ToDateTime(grow["GAP_ETIME"].ToString()).AddSeconds(gap_tdelay).ToString("yyyy-MM-dd HH:mm:ss"); // gaptime.Add(time); // } // info.gapinfo = gaptime; // info.paralist = paralist; // info.tagname = tagname; // info.interval = interval; // } //} ////如果没有 //else //{ info = GetParaSegInfo(seg); info.batchBtime = Convert.ToDateTime(info.batchBtime).AddSeconds(batchheadDelay).ToString("yyyy-MM-dd HH:mm:ss"); info.batchEtime = Convert.ToDateTime(info.batchEtime).AddSeconds(batchtailDelay).ToString("yyyy-MM-dd HH:mm:ss"); info.tailBtime = Convert.ToDateTime(info.tailBtime).AddSeconds(headDelay).ToString("yyyy-MM-dd HH:mm:ss"); info.tailEtime = Convert.ToDateTime(info.tailEtime).AddSeconds(tailDelay).ToString("yyyy-MM-dd HH:mm:ss"); List <Gaptime> gaps = new List <Gaptime>(); foreach (Gaptime gap in info.gapinfo) { Gaptime g = new Gaptime(); g.starttime = Convert.ToDateTime(gap.starttime).AddSeconds(gap_hdelay).ToString("yyyy-MM-dd HH:mm:ss"); g.endtime = Convert.ToDateTime(gap.endtime).AddSeconds(gap_tdelay).ToString("yyyy-MM-dd HH:mm:ss"); gaps.Add(g); } info.gapinfo = gaps; info.paralist = paralist; info.tagname = tagname; info.interval = interval; } #endregion return(info); }
public DataTable GetIHOrgDataSet(TimeSeg seg) { #region ///////////读取数据库中的采集条件//////////////////////////////////////////////////////////////////////////////////////////////// DbOperator opt = new DbOperator(); DataSet data = opt.CreateDataSetOra("select t.value_tag,r.gap_hdelay,r.gap_tdelay,r.ctrl_point,r.is_gap_judge,r.periodic,r.rst_value,r.gap_time,r.head_delay,r.tail_delay,r.batch_head_delay,r.batch_tail_delay from HT_QLT_COLLECTION r left join ht_pub_tech_para t on r.para_code = t.para_code where r.PARA_CODE = '" + seg.nodecode + "'"); if (data == null || data.Tables[0].Rows.Count == 0) { return(null); } DataRow row = data.Tables[0].Rows[0]; ////////////////////工艺点标签////////////////////////////////////////////// string tagname = row["value_tag"].ToString(); string interval = row["periodic"].ToString(); int timegap = Convert.ToInt32(row["gap_time"].ToString()); string tailRst = row["rst_value"].ToString(); ////////////////////是否为工艺段断流判定点///////////////////////////////////////////// bool gapctrl = ("1" == row["is_gap_judge"].ToString()); //////////////////////偏移///////////////////////////////////////////// int headDelay = Convert.ToInt32(row["head_delay"].ToString()); //料头 int tailDelay = Convert.ToInt32(row["tail_delay"].ToString()); //料尾 int batchheadDelay = Convert.ToInt32(row["batch_head_delay"].ToString()); //批头 int batchtailDelay = Convert.ToInt32(row["batch_tail_delay"].ToString()); //批尾 int gap_hdelay = Convert.ToInt32(row["gap_hdelay"].ToString()); //断流前 int gap_tdelay = Convert.ToInt32(row["gap_tdelay"].ToString()); //断流后 #endregion #region //////////////////////寻找数据批头尾、料头尾 以及断流信息/////////////////////////////////////////////////////////////////////// string query; string batchBtime = "", batchEtime = ""; //电子秤参与后判定的批头批尾时间 string tailBtime = "", tailEtime = ""; //电子秤参与后判定的料头料尾时间 if (gapctrl) { if (tagname != "" && tailRst != "" && interval != "") { batchBtime = tailBtime = seg.starttime; //初始化料头时间 batchEtime = tailEtime = seg.endtime; //初始化料尾时间 ///////////////////////////自动寻找批头批尾///////////////////////////////////////////////// if (seg.type == TimeSegType.BEGIN || seg.type == TimeSegType.BOTH) //寻找批头 { query = "SELECT Min(timestamp),tagname FROM ihrawdata where tagname = '" + tagname + "' and timestamp between '" + seg.starttime + "' and '" + seg.endtime + "' and value <" + tailRst + " group by tagname"; data = CreateDataSetIH(query); if (data != null && data.Tables[0].Select().Length > 0) { batchBtime = Convert.ToDateTime(data.Tables[0].Rows[0]["Min of timestamp"].ToString()).AddSeconds(batchheadDelay).ToString("yyyy-MM-dd HH:mm:ss"); } ; //自动获取批头时间,向前推20秒 } if (seg.type == TimeSegType.END || seg.type == TimeSegType.BOTH) //寻找批尾 { string temptime = Convert.ToDateTime(seg.endtime).AddHours(1).ToString("yyyy-MM-dd HH:mm:ss"); DataSet tempdata = CreateDataSetIH("select value from ihrawdata where tagname = '" + tagname + "' and timestamp between '" + seg.endtime + "' and '" + Convert.ToDateTime(seg.endtime).AddSeconds(60).ToString("yyyy-MM-dd HH:mm:ss") + "'"); double temp = 0; if (tempdata != null && tempdata.Tables[0].Select().Length > 0) { temp = Convert.ToDouble(tempdata.Tables[0].Select()[0][0].ToString()); } if (temp >= Convert.ToDouble(tailRst)) { query = "SELECT Min(timestamp),tagname FROM ihrawdata where tagname = '" + tagname + "' and timestamp between '" + seg.endtime + "' and '" + temptime + "' and value <" + tailRst + " group by tagname"; } else { query = "SELECT Max(timestamp),tagname FROM ihrawdata where tagname = '" + tagname + "' and timestamp between '" + tailBtime + "' and '" + seg.endtime + "' and value >" + tailRst + " group by tagname"; } data = CreateDataSetIH(query); if (data != null && data.Tables[0].Select().Length > 0) { if (temp >= Convert.ToDouble(tailRst)) { batchEtime = Convert.ToDateTime(data.Tables[0].Rows[0]["Min of timestamp"].ToString()).AddSeconds(batchtailDelay).ToString("yyyy-MM-dd HH:mm:ss");//自动获取批尾时间 } else { batchEtime = Convert.ToDateTime(data.Tables[0].Rows[0]["Max of timestamp"].ToString()).AddSeconds(batchtailDelay).ToString("yyyy-MM-dd HH:mm:ss");//自动获取批尾时间 } } } ////////////////////////////////自动寻找料头料尾//////////////////////////////////////////////////////////////// if (seg.type == TimeSegType.BEGIN || seg.type == TimeSegType.BOTH)//寻找料头 { query = "SELECT Min(timestamp),tagname FROM ihrawdata where tagname = '" + tagname + "' and timestamp between '" + batchBtime + "' and '" + batchEtime + "' and intervalmilliseconds = " + interval + "s and value >" + tailRst + " group by tagname"; data = CreateDataSetIH(query); if (data != null && data.Tables[0].Select().Length > 0) { tailBtime = Convert.ToDateTime(data.Tables[0].Rows[0]["Min of timestamp"].ToString()).AddSeconds(headDelay).ToString("yyyy-MM-dd HH:mm:ss");//经延时料头时间 } } if (seg.type == TimeSegType.END || seg.type == TimeSegType.BOTH)//寻找料尾 { query = "SELECT Max(timestamp),tagname FROM ihrawdata where tagname = '" + tagname + "' and timestamp between '" + batchBtime + "' and '" + batchEtime + "' and intervalmilliseconds = " + interval + "s and value >" + tailRst + " group by tagname"; data = CreateDataSetIH(query); if (data != null && data.Tables[0].Select().Length > 0) { tailEtime = Convert.ToDateTime(data.Tables[0].Rows[0]["Max of timestamp"].ToString()).AddSeconds(tailDelay).ToString("yyyy-MM-dd HH:mm:ss");//经延时料尾时间 } } string[] bseg = { "ORDERNO", "PLANNO", "STARTTIME", "ENDTIME", "SECTION_CODE", "BATCH_BTIME", "BATCH_ETIME", "GAP_BTIME", "GAP_ETIME", "TYPE", }; string[] bvalue = { "0", seg.planno, seg.starttime, seg.endtime, seg.nodecode.Substring(0, 5), batchBtime, batchEtime, tailBtime, tailEtime, "0" }; opt.MergeInto(bseg, bvalue, 5, "HT_QLT_GAP_COLLECTION"); ////////////////////选取数据/判断是否断料,并记录下相应的断料时间////// gaptime = GetGapTime(tailBtime, tailEtime, tagname, tailRst, gap_hdelay, gap_tdelay, timegap); int orderno = 1; string[] gapseg = { "ORDERNO", "PLANNO", "STARTTIME", "ENDTIME", "SECTION_CODE", "GAPTIME ", "GAP_BTIME", "GAP_ETIME", "TYPE" }; if (gaptime != null) { foreach (Gaptime time in gaptime) { string[] gapvalue = { (orderno++).ToString(), seg.planno, seg.starttime, seg.endtime, seg.nodecode.Substring(0, 5), time.gaptime.ToString(), time.starttime, time.endtime, "1" }; opt.MergeInto(gapseg, gapvalue, 5, "HT_QLT_GAP_COLLECTION"); } } } else { return(null); } } else { string ctrlpoint = row["ctrl_point"].ToString(); if (ctrlpoint != "") { DataSet gapdata = opt.CreateDataSetOra("select * from HT_QLT_GAP_COLLECTION where TYPE = '1' and PLANNO = '" + seg.planno + "' and STARTTIME = '" + seg.starttime + "' and ENDTIME = '" + seg.endtime + "' and SECTION_CODE = '" + ctrlpoint.Substring(0, 5) + "' "); if (gapdata != null && gapdata.Tables[0].Rows.Count > 0) { foreach (DataRow grow in gapdata.Tables[0].Rows) { Gaptime time = new Gaptime(); time.gaptime = Convert.ToInt32(grow["gaptime"].ToString()); time.starttime = Convert.ToDateTime(grow["STARTTIME"].ToString()).AddSeconds(gap_hdelay).ToString("yyyy-MM-dd HH:mm:ss"); time.endtime = Convert.ToDateTime(grow["ENDTIME"].ToString()).AddSeconds(gap_tdelay).ToString("yyyy-MM-dd HH:mm:ss"); if (gaptime == null) { gaptime = new List <Gaptime>(); } gaptime.Add(time); } } gapdata = opt.CreateDataSetOra("select * from HT_QLT_GAP_COLLECTION where TYPE = '0' and PLANNO = '" + seg.planno + "' and STARTTIME = '" + seg.starttime + "' and ENDTIME = '" + seg.endtime + "' and SECTION_CODE = '" + ctrlpoint.Substring(0, 5) + "' "); if (gapdata != null && gapdata.Tables[0].Rows.Count > 0) { DataRow brow = gapdata.Tables[0].Rows[0]; batchBtime = brow["BATCH_BTIME"].ToString(); batchEtime = brow["BATCH_ETIME"].ToString(); tailBtime = brow["GAP_BTIME"].ToString(); tailEtime = brow["GAP_ETIME"].ToString(); } } } #endregion #region //////////////////////////////选择数据/////////////////////////////////////////////////////////////////////////////////////////////// if (batchBtime != "") { seg.starttime = batchBtime; } if (batchEtime != "") { seg.endtime = batchEtime; } query = "SELECT timestamp as 时间,value as 值 FROM ihrawdata where tagname = '" + tagname + "' and timestamp between '" + seg.starttime + "' and '" + seg.endtime + "' and intervalmilliseconds = " + interval + "s order by timestamp ASC"; data = CreateDataSetIH(query); if (data != null && data.Tables[0].Select().Length > 0) { DataRow[] ResRows = data.Tables[0].Select(); int count = ResRows.Length; DataTable ResT = new DataTable(); ResT = data.Tables[0]; ResT.Columns.Add("状态"); foreach (DataRow Res in ResRows) { string tempstr = Convert.ToDateTime(Res["时间"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"); if (string.Compare(tempstr, tailBtime) < 0) { Res["状态"] = "料头"; } else if (tailEtime != seg.starttime && string.Compare(tempstr, tailEtime) > 0) { Res["状态"] = "料尾"; } else { Res["状态"] = "过程值"; if (gaptime != null) { int h = 0; while (h < gaptime.Count) { if (string.Compare(tempstr, ((Gaptime)gaptime[h]).starttime) > 0 && string.Compare(tempstr, ((Gaptime)gaptime[h]).endtime) < 0) { Res["状态"] = "断流值"; } h++; } } } } return(ResT); } else { return(null); } #endregion }