ProcessDataRecorder.cs 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Linq;
  5. using Aitex.Core.RT.DBCore;
  6. using Aitex.Core.RT.Log;
  7. using Aitex.Core.UI.ControlDataContext;
  8. using Aitex.Sorter.Common;
  9. using MECF.Framework.Common.CommonData;
  10. using MECF.Framework.Common.Equipment;
  11. namespace MECF.Framework.Common.DBCore
  12. {
  13. public class ProcessDataRecorder
  14. {
  15. public static void Start(string guid, string recipeName)
  16. {
  17. string sql = string.Format(
  18. "INSERT INTO \"process_data\"(\"guid\", \"process_begin_time\", \"recipe_name\" )VALUES ('{0}', '{1}', '{2}' );",
  19. guid,
  20. DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss.fff"),
  21. recipeName);
  22. DB.Insert(sql);
  23. }
  24. public static void Start(string guid, string recipeName, string waferDataGuid, string processIn, float recipeSettingTime = 0)
  25. {
  26. string sql = string.Format(
  27. "INSERT INTO \"process_data\"(\"guid\", \"process_begin_time\", \"recipe_name\" , \"wafer_data_guid\", \"process_in\", \"recipe_setting_time\" )VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}' );",
  28. guid,
  29. DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss.fff"),
  30. recipeName,
  31. waferDataGuid,
  32. processIn,
  33. recipeSettingTime);
  34. DB.Insert(sql);
  35. }
  36. public static void UpdateStatus(string guid, string status)
  37. {
  38. string sql = string.Format(
  39. "UPDATE \"process_data\" SET \"process_status\"='{0}' WHERE \"guid\"='{1}';",
  40. status,
  41. guid);
  42. DB.Insert(sql);
  43. }
  44. public static void End(string guid, string status)
  45. {
  46. string sql = string.Format(
  47. "UPDATE \"process_data\" SET \"process_end_time\"='{0}',\"process_status\"='{1}' WHERE \"guid\"='{2}';",
  48. DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss.fff"),
  49. status,
  50. guid);
  51. DB.Insert(sql);
  52. }
  53. public static void End(string guid)
  54. {
  55. string sql = string.Format(
  56. "UPDATE \"process_data\" SET \"process_end_time\"='{0}' WHERE \"guid\"='{1}';",
  57. DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss.fff"),
  58. guid);
  59. DB.Insert(sql);
  60. }
  61. public static void StepStart(string recipeGuid, int stepNumber, string stepName, float stepTime)
  62. {
  63. string guid = Guid.NewGuid().ToString();
  64. string sql = $"INSERT INTO \"recipe_step_data\"(\"guid\", \"step_begin_time\", \"step_name\" , \"step_time\", \"process_data_guid\", \"step_number\")VALUES ('{guid}', '{DateTime.Now:yyyy/MM/dd HH:mm:ss.fff}', '{stepName}', '{stepTime}', '{recipeGuid}', '{stepNumber}' );" ;
  65. //System.Diagnostics.Trace.WriteLine(sql);
  66. DB.Insert(sql);
  67. }
  68. public static void StepEnd(string recipeGuid, int stepNumber, List<FdcDataItem> stepData=null)
  69. {
  70. string sql = $"UPDATE \"recipe_step_data\" SET \"step_end_time\"='{DateTime.Now:yyyy/MM/dd HH:mm:ss.fff}' WHERE \"process_data_guid\"='{recipeGuid}' and \"step_number\"='{stepNumber}';" ;
  71. DB.Insert(sql);
  72. if (stepData != null && stepData.Count > 0)
  73. {
  74. foreach (var item in stepData)
  75. {
  76. sql = $"INSERT INTO \"step_fdc_data\"(\"process_data_guid\", \"create_time\", \"step_number\" , \"parameter_name\", \"sample_count\", \"min_value\", \"max_value\", \"setpoint\", \"std_value\", \"mean_value\")VALUES ('{recipeGuid}', '{DateTime.Now:yyyy/MM/dd HH:mm:ss.fff}', '{stepNumber}', '{item.Name}', '{item.SampleCount}', '{item.MinValue}', '{item.MaxValue}', '{item.SetPoint}', '{item.StdValue}', '{item.MeanValue}' );";
  77. DB.Insert(sql);
  78. }
  79. }
  80. }
  81. public List<string> GetHistoryRecipeList(DateTime begin, DateTime end)
  82. {
  83. List<string> result = new List<string>();
  84. string sql = string.Format("SELECT * FROM \"RecipeRunHistory\" where \"ProcessBeginTime\" >= '{0}' and \"ProcessBeginTime\" <= '{1}' order by \"ProcessBeginTime\" ASC;",
  85. begin.ToString("yyyy/MM/dd HH:mm:ss.fff"), end.ToString("yyyy/MM/dd HH:mm:ss.fff"));
  86. DataSet ds = DB.ExecuteDataset(sql);
  87. if (ds == null)
  88. return result;
  89. for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
  90. {
  91. string recipe = ds.Tables[0].Rows[i]["RecipeName"].ToString();
  92. if (!result.Contains(recipe))
  93. result.Add(recipe);
  94. }
  95. ds.Clear();
  96. return result;
  97. }
  98. public static List<HistoryProcessData> QueryDBProcess(string sql)
  99. {
  100. List<HistoryProcessData> result = new List<HistoryProcessData>();
  101. try
  102. {
  103. DataSet ds = DB.ExecuteDataset(sql);
  104. if (ds == null)
  105. return result;
  106. for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
  107. {
  108. HistoryProcessData ev = new HistoryProcessData();
  109. ev.RecipeName = ds.Tables[0].Rows[i]["recipe_name"].ToString();
  110. ev.Result = ds.Tables[0].Rows[i]["process_status"].ToString();
  111. ev.Guid = ds.Tables[0].Rows[i]["guid"].ToString();
  112. if (!ds.Tables[0].Rows[i]["process_begin_time"].Equals(DBNull.Value))
  113. ev.StartTime = ((DateTime)ds.Tables[0].Rows[i]["process_begin_time"]).ToString("yyyy/MM/dd HH:mm:ss.fff");
  114. if (!ds.Tables[0].Rows[i]["process_end_time"].Equals(DBNull.Value))
  115. ev.EndTime = ((DateTime)ds.Tables[0].Rows[i]["process_end_time"]).ToString("yyyy/MM/dd HH:mm:ss.fff");
  116. result.Add(ev);
  117. }
  118. }
  119. catch (Exception ex)
  120. {
  121. LOG.Write(ex);
  122. }
  123. return result;
  124. }
  125. public static List<HistoryDataItem> GetHistoryDataFromStartToEnd(IEnumerable<string> keys, DateTime begin, DateTime end, string module)
  126. {
  127. List<HistoryDataItem> result = new List<HistoryDataItem>();
  128. try
  129. {
  130. DateTime begintime = new DateTime(begin.Year, begin.Month, begin.Day, begin.Hour, begin.Minute, begin.Second, begin.Millisecond);
  131. DateTime endtime = new DateTime(begin.Year, begin.Month, end.Day, end.Hour, end.Minute, end.Second, end.Millisecond);
  132. string sql = "select time AS InternalTimeStamp";
  133. foreach (var dataId in keys)
  134. {
  135. sql += "," + string.Format("\"{0}\"", dataId);
  136. }
  137. sql += string.Format(" from \"{0}\" where time > {1} and time <= {2} order by time asc LIMIT 86400;",
  138. begin.ToString("yyyyMMdd") + "." + module, begintime.Ticks, endtime.Ticks);
  139. DataSet dataSet = DB.ExecuteDataset(sql);
  140. if (dataSet == null)
  141. return result;
  142. if (dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0)
  143. return result;
  144. DateTime dt = new DateTime();
  145. Dictionary<int, string> colName = new Dictionary<int, string>();
  146. for (int colNo = 0; colNo < dataSet.Tables[0].Columns.Count; colNo++)
  147. colName.Add(colNo, dataSet.Tables[0].Columns[colNo].ColumnName);
  148. for (int rowNo = 0; rowNo < dataSet.Tables[0].Rows.Count; rowNo++)
  149. {
  150. var row = dataSet.Tables[0].Rows[rowNo];
  151. for (int i = 0; i < dataSet.Tables[0].Columns.Count; i++)
  152. {
  153. HistoryDataItem data = new HistoryDataItem();
  154. if (i == 0)
  155. {
  156. long ticks = (long)row[i];
  157. dt = new DateTime(ticks);
  158. continue;
  159. }
  160. else
  161. {
  162. string dataId = colName[i];
  163. if (row[i] is DBNull || row[i] == null)
  164. {
  165. data.dateTime = dt;
  166. data.dbName = colName[i];
  167. data.value = 0;
  168. }
  169. else if (row[i] is bool)
  170. {
  171. data.dateTime = dt;
  172. data.dbName = colName[i];
  173. data.value = (bool)row[i] ? 1 : 0;
  174. }
  175. else
  176. {
  177. data.dateTime = dt;
  178. data.dbName = colName[i];
  179. data.value = float.Parse(row[i].ToString());
  180. }
  181. }
  182. result.Add(data);
  183. }
  184. }
  185. dataSet.Clear();
  186. }
  187. catch (Exception ex)
  188. {
  189. LOG.Write(ex);
  190. }
  191. return result;
  192. }
  193. public static List<HistoryDataItem> GetOneDayHistoryData(IEnumerable<string> keys, DateTime begin, string module)
  194. {
  195. List<HistoryDataItem> result = new List<HistoryDataItem>();
  196. try
  197. {
  198. DateTime begintime = new DateTime(begin.Year, begin.Month, begin.Day, 0, 0, 0, 0);
  199. DateTime endtime = new DateTime(begin.Year, begin.Month, begin.Day, 23, 59, 59, 999);
  200. string sql = "select time AS InternalTimeStamp";
  201. foreach (var dataId in keys)
  202. {
  203. sql += "," + string.Format("\"{0}\"", dataId);
  204. }
  205. sql += string.Format(" from \"{0}\" where time > {1} and time <= {2} order by time asc LIMIT 86400;",
  206. begin.ToString("yyyyMMdd") + "." + module, begintime.Ticks, endtime.Ticks);
  207. DataSet dataSet = DB.ExecuteDataset(sql);
  208. if (dataSet == null)
  209. return result;
  210. if (dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0)
  211. return result;
  212. DateTime dt = new DateTime();
  213. Dictionary<int, string> colName = new Dictionary<int, string>();
  214. for (int colNo = 0; colNo < dataSet.Tables[0].Columns.Count; colNo++)
  215. colName.Add(colNo, dataSet.Tables[0].Columns[colNo].ColumnName);
  216. for (int rowNo = 0; rowNo < dataSet.Tables[0].Rows.Count; rowNo++)
  217. {
  218. var row = dataSet.Tables[0].Rows[rowNo];
  219. for (int i = 0; i < dataSet.Tables[0].Columns.Count; i++)
  220. {
  221. HistoryDataItem data = new HistoryDataItem();
  222. if (i == 0)
  223. {
  224. long ticks = (long)row[i];
  225. dt = new DateTime(ticks);
  226. continue;
  227. }
  228. else
  229. {
  230. string dataId = colName[i];
  231. if (row[i] is DBNull || row[i] == null)
  232. {
  233. data.dateTime = dt;
  234. data.dbName = colName[i];
  235. data.value = 0;
  236. }
  237. else if (row[i] is bool)
  238. {
  239. data.dateTime = dt;
  240. data.dbName = colName[i];
  241. data.value = (bool)row[i] ? 1 : 0;
  242. }
  243. else
  244. {
  245. data.dateTime = dt;
  246. data.dbName = colName[i];
  247. data.value = float.Parse(row[i].ToString());
  248. }
  249. }
  250. result.Add(data);
  251. }
  252. }
  253. dataSet.Clear();
  254. }
  255. catch (Exception ex)
  256. {
  257. LOG.Write(ex);
  258. }
  259. return result;
  260. }
  261. public static List<HistoryDataItem> GetHistoryData(IEnumerable<string> keys, string recipeRunGuid, string module)
  262. {
  263. List<HistoryDataItem> result = new List<HistoryDataItem>();
  264. try
  265. {
  266. string sql = string.Format("SELECT * FROM \"process_data\" where \"guid\" = '{0}'",
  267. recipeRunGuid);
  268. DataSet ds = DB.ExecuteDataset(sql);
  269. if (ds == null)
  270. return result;
  271. if (ds.Tables[0].Rows.Count == 0)
  272. return result;
  273. object from = ds.Tables[0].Rows[0]["process_begin_time"];
  274. if (from is DBNull)
  275. {
  276. LOG.Write(string.Format("{0} not set start time", recipeRunGuid));
  277. return result;
  278. }
  279. DateTime begin = (DateTime)from;
  280. object to = ds.Tables[0].Rows[0]["process_end_time"];
  281. if (to is DBNull)
  282. {
  283. to = new DateTime(begin.Year, begin.Month, begin.Day, 23, 59, 59, 999);
  284. }
  285. DateTime end = (DateTime)to;
  286. sql = "select time AS InternalTimeStamp";
  287. foreach (var dataId in keys)
  288. {
  289. sql += "," + string.Format("\"{0}\"", dataId);
  290. }
  291. sql += string.Format(" from \"{0}\" where time > {1} and time <= {2} order by time asc LIMIT 2000;",
  292. begin.ToString("yyyyMMdd") + "." + module, begin.Ticks, end.Ticks);
  293. DataSet dataSet = DB.ExecuteDataset(sql);
  294. if (dataSet == null)
  295. return result;
  296. if (dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0)
  297. return result;
  298. DateTime dt = new DateTime();
  299. Dictionary<int, string> colName = new Dictionary<int, string>();
  300. for (int colNo = 0; colNo < dataSet.Tables[0].Columns.Count; colNo++)
  301. colName.Add(colNo, dataSet.Tables[0].Columns[colNo].ColumnName);
  302. for (int rowNo = 0; rowNo < dataSet.Tables[0].Rows.Count; rowNo++)
  303. {
  304. var row = dataSet.Tables[0].Rows[rowNo];
  305. for (int i = 0; i < dataSet.Tables[0].Columns.Count; i++)
  306. {
  307. HistoryDataItem data = new HistoryDataItem();
  308. if (i == 0)
  309. {
  310. long ticks = (long)row[i];
  311. dt = new DateTime(ticks);
  312. continue;
  313. }
  314. else
  315. {
  316. string dataId = colName[i];
  317. if (row[i] is DBNull || row[i] == null)
  318. {
  319. data.dateTime = dt;
  320. data.dbName = colName[i];
  321. data.value = 0;
  322. }
  323. else if (row[i] is bool)
  324. {
  325. data.dateTime = dt;
  326. data.dbName = colName[i];
  327. data.value = (bool)row[i] ? 1 : 0;
  328. }
  329. else
  330. {
  331. data.dateTime = dt;
  332. data.dbName = colName[i];
  333. data.value = float.Parse(row[i].ToString());
  334. }
  335. }
  336. result.Add(data);
  337. }
  338. }
  339. dataSet.Clear();
  340. }
  341. catch (Exception ex)
  342. {
  343. LOG.Write(ex);
  344. }
  345. return result;
  346. }
  347. }
  348. }