// Loops through domains and executing sync for each domain public void startSync() { // Import data from SQL server foreach (var domain in domains) { try { Type type = Type.GetType("kaiam.MongoSync.Sync." + domain); if (type != null) { SyncBase sync = (SyncBase)Activator.CreateInstance(type); sync.toMongoTestData(-15); } } catch (Exception exc) { Program.log(domain + " ERROR: " + exc.Message + "\n" + exc.StackTrace); } } // Import measurement records without any down // Import beta module data from excel files try { Type typeBm = Type.GetType("kaiam.MongoSync.Sync.BetaModule"); SyncBase syncBm = (SyncBase)Activator.CreateInstance(typeBm); syncBm.toMongoTestData(0); } catch (Exception exc) { Program.log("BetaModule ERROR: " + exc.Message + "\n" + exc.StackTrace); } // Import views from SQL server every night at 2 AM DateTime date1 = DateTime.Now; String hour = date1.ToString("%htt"); if (hour != "2AM") { return; } try { String connstr = "data source=dbs1.kaiam.local;initial catalog=KAIAM.Data.Test.Production49;user id=KAIAM.TestUser;password=5525Iamkaiam!"; SqlConnection remoteConnection = new SqlConnection(connstr); remoteConnection.Open(); foreach (var dbview in dbviews) { MongoViewHelper mvh = new MongoViewHelper(dbview); mvh.Collection.RemoveAll(); SqlCommand myCommand = new SqlCommand("select * from " + dbview, remoteConnection); SqlDataReader myReader = myCommand.ExecuteReader(); DataTable schemaTable = myReader.GetSchemaTable(); while (myReader.Read()) { BsonDocument bson = new BsonDocument(); foreach (DataRow row in schemaTable.Rows) { Dictionary <string, object> dictData = new Dictionary <string, object>(); if (myReader[row["ColumnName"].ToString()].GetType().ToString() == "System.TimeSpan") { int secs = 0; System.TimeSpan ts = (System.TimeSpan)myReader[row["ColumnName"].ToString()]; secs += ts.Seconds; secs += ts.Minutes * 60; secs += ts.Hours * 3600; secs += ts.Days * 86400; dictData.Add(row["ColumnName"].ToString(), secs); } else { dictData.Add(row["ColumnName"].ToString(), myReader[row["ColumnName"].ToString()]); } bson.AddRange(dictData); } mvh.Collection.Save(bson); } myReader.Close(); } remoteConnection.Close(); } catch (Exception exc) { Program.log("VIEW import ERROR: " + exc.Message + "\n" + exc.StackTrace); } }
public override int processTestData(DateTime start, DateTime end) { int cnt = 0; // Import TOSA data from Scotland database (mySQL try { MongoViewHelper mvh = new MongoViewHelper("testdata"); String connstr = "SERVER=liv-svr-mysql3;DATABASE=xosa;UID=newark;PASSWORD=GFS54ad:)4dfH;Connection Timeout=7000"; MySqlConnection connection = new MySqlConnection(connstr); connection.Open(); string query = "SELECT * FROM osa_test,osa_sub_test,stripe,osa_sub_test_osa_stripe " + "WHERE test_date >= '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' AND test_date < '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "' " + "AND osa_test.id = osa_sub_test.test_id " + "AND stripe.id = osa_sub_test_osa_stripe.osa_stripe_id " + "AND osa_sub_test.id = osa_sub_test_osa_stripe.osa_sub_test_stripes_id"; //Create Command MySqlCommand cmd = new MySqlCommand(query, connection); cmd.CommandTimeout = 7200; //Create a data reader and Execute the command MySqlDataReader dataReader = cmd.ExecuteReader(); DataTable schemaTable = dataReader.GetSchemaTable(); string[] lists = { "liv_current_ma", "liv_power_mw", "liv_voltagev", "liv_mpd_ua", "mpd_current_ua_stripe0", "mpd_current_ua_stripe1", "mpd_current_ua_stripe2", "mpd_current_ua_stripe3", "mpd_ratio_db_stripe0", "mpd_ratio_db_stripe1", "mpd_ratio_db_stripe2", "mpd_ratio_db_stripe3" }; char[] sep = { ' ' }; // ----- 2017-08-25: Adding connection to Livingston I-Track DB ----- string oradb = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.24.17.62)(PORT=1521)))(CONNECT_DATA=(SID=ITRK02)(SERVER=DEDICATED)));User Id=READ_ONLY_KAIAM;Password=I-TrackLLC123;"; OracleConnection oraConn = new OracleConnection(oradb); try { oraConn.Open(); } catch (Exception OraEx) { Program.log("TOSA I-Track DB Connection ERROR: " + OraEx.Message + "\n" + OraEx.StackTrace); return(-1); } // ------------------------------------------------------------------ while (dataReader.Read()) { cnt++; BsonDocument bson = new BsonDocument(); foreach (DataRow row in schemaTable.Rows) { String col = row["ColumnName"].ToString(); if (!excludeDataFields.Contains(col)) { Dictionary <string, object> dictData = new Dictionary <string, object>(); if (dataReader[col].GetType().ToString() == "System.TimeSpan") { int secs = 0; System.TimeSpan ts = (System.TimeSpan)dataReader[col]; secs += ts.Seconds; secs += ts.Minutes * 60; secs += ts.Hours * 3600; secs += ts.Days * 86400; dictData.Add(col, secs); } else { var s = dataReader[col].GetType().ToString(); if (s != "System.DBNull") { if (!lists.Contains(col)) { dictData.Add(col, dataReader[col]); } else { String sl = dataReader[col].ToString(); string[] sa = sl.Split(sep, StringSplitOptions.RemoveEmptyEntries); dictData.Add(col, Array.ConvertAll(sa, i => float.Parse(i))); } } } if (!bson.Contains(col)) { if (col == "test_date") { bson.Add(col, Convert.ToDateTime(dictData[col]).ToLocalTime()); } else { bson.AddRange(dictData); } } } } BsonDocument rootDoc = new BsonDocument { { "_id", "TOSA-" + bson["osa_stripe_id"] }, { "mid", "TOSAMID-" + bson["osa_sub_test_stripes_id"] }, { "timestamp", bson["test_date"] }, { "type", "tosa" }, { "subtype", "dc" }, { "result", bson["pass"] == 1 ? "P" : "F" }, { "measstatus", bson["pass"] == 1 ? "P" : "F" }, { "status", bson["pass"] == 1 ? "P" : "F" } }; rootDoc.Add("meta", new BsonDocument { { "StartDateTime", bson["test_date"] }, { "EndDateTime", bson["test_date"] }, { "Channel", bson["stripe_number"] } }); if (bson["tosa_serial_number"] != null) { string[] tsn = bson["tosa_serial_number"].ToString().Split('_'); bson["tsn"] = tsn[0]; bson.Add("laser_pn", new BsonArray()); for (int i = 1; i < tsn.Length; i++) { ((BsonArray)bson["laser_pn"]).Add(tsn[i]); } bson.Remove("tosa_serial_number"); string tosaType = bson["tsn"].ToString().Substring(0, 3); string serNum = bson["tsn"].ToString().Substring(3); // ----- 2017-08-25: Adding connection to Livingston I-Track DB ----- string oraQuery = "SELECT DEVICE_ID, ROUTE, DESCRIPTION FROM (SELECT UNIQUE ROUTE, DEVICE_ID FROM (SELECT DEVICE_ID, LOT_ID AS LID FROM KAIAM.DEVICE_LOT_LOOKUP WHERE DEVICE_ID = '" + serNum + "') CROSS JOIN KAIAM.ASSEMBLE_PROC WHERE LID = KAIAM.ASSEMBLE_PROC.LOT_ID) CROSS JOIN KAIAM.ROUTE_OBJ WHERE ROUTE = ROUTE_ID"; OracleCommand oraCmd = new OracleCommand(oraQuery, oraConn); oraCmd.CommandType = CommandType.Text; oraCmd.CommandTimeout = 7200; OracleDataReader dr = oraCmd.ExecuteReader(); try { dr.Read(); } catch (Exception executeException) { Program.log("TOSA I-Track DB query ERROR: " + executeException.Message + "\n" + executeException.StackTrace); return(-1); } var UKDeviceType = "Not Found"; var UKPartNumber = "Not Found"; var UKDescription = "Not Found"; try { var route = dr["Route"].ToString().Split('_'); UKDeviceType = route[1]; UKPartNumber = route[2]; UKDescription = dr["DESCRIPTION"].ToString(); } catch (Exception e) { Program.log("TOSA I-Track DB data output ERROR: " + e.Message + " (" + serNum + ")"); } // ------------------------------------------------------------------ rootDoc.Add("device", new BsonDocument { { "SerialNumber", serNum }, { "TosaType", tosaType }, // ----- 2017-08-25: Adding connection to Livingston I-Track DB ----- { "UKDeviceType", UKDeviceType }, { "UKDevicePartNumber", UKPartNumber }, { "UKDeviceDescription", UKDescription } // ------------------------------------------------------------------ }); bson.Remove("osa_stripe_id"); bson.Remove("osa_sub_test_stripes_id"); bson.Remove("test_date"); bson.Remove("stripe_number"); bson.Remove("pass"); rootDoc.Add("data", bson); mvh.Collection.Save(rootDoc); } } //close Data Reader and connection dataReader.Close(); connection.Close(); // ----- 2017-08-25: Adding connection to Livingston I-Track DB ----- oraConn.Close(); // ------------------------------------------------------------------ } catch (Exception exc) { Program.log("TOSA import ERROR: " + exc.Message + "\n" + exc.StackTrace); return(-1); } return(cnt); }
public override int processTestData(DateTime start, DateTime end) { int cnt = 0; // Import LS2 data from Scotland database (mySQL, OracleDb) try { MongoViewHelper mvh = new MongoViewHelper("testdata"); String connstr = "SERVER=liv-svr-mysql3;DATABASE=xosa;UID=newark;PASSWORD=GFS54ad:)4dfH;Connection Timeout=7000"; MySqlConnection connection = new MySqlConnection(connstr); connection.Open(); string query = "SELECT *, cob_dc_channel_test.id as test_id FROM cob_dc_test,cob_dc_channel_test " + "WHERE test_date >= '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' AND test_date < '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "' " + "AND cob_dc_test.id = cob_dc_channel_test.cob_test_id "; //Create Command MySqlCommand cmd = new MySqlCommand(query, connection); cmd.CommandTimeout = 7200; //Create a data reader and Execute the command MySqlDataReader dataReader = cmd.ExecuteReader(); DataTable schemaTable = dataReader.GetSchemaTable(); string[] lists = { "rx_cal_values", "tx_cal_values", "li_current", "li_power", "mpd_crosstalk", "rx_mpd_leakage", "spectrum_pwr_dbm", "spectrum_wl_nm" }; char[] sep = { ' ' }; // ----- 2017-08-25: Adding connection to Livingston I-Track DB ----- string oradb = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.24.17.62)(PORT=1521)))(CONNECT_DATA=(SID=ITRK02)(SERVER=DEDICATED)));User Id=READ_ONLY_KAIAM;Password=I-TrackLLC123;"; OracleConnection oraConn = new OracleConnection(oradb); try { oraConn.Open(); } catch (Exception OraEx) { Program.log("LS2 I-Track DB Connection ERROR: " + OraEx.Message + "\n" + OraEx.StackTrace); return(-1); } // ------------------------------------------------------------------ while (dataReader.Read()) { cnt++; BsonDocument bson = new BsonDocument(); foreach (DataRow row in schemaTable.Rows) { String col = row["ColumnName"].ToString(); if (!excludeDataFields.Contains(col)) { Dictionary <string, object> dictData = new Dictionary <string, object>(); if (dataReader[col].GetType().ToString() == "System.TimeSpan") { int secs = 0; System.TimeSpan ts = (System.TimeSpan)dataReader[col]; secs += ts.Seconds; secs += ts.Minutes * 60; secs += ts.Hours * 3600; secs += ts.Days * 86400; dictData.Add(col, secs); } else { var s = dataReader[col].GetType().ToString(); if (s != "System.DBNull") { if (!lists.Contains(col)) { dictData.Add(col, dataReader[col]); } else { String sl = dataReader[col].ToString(); string[] sa = sl.Split(sep, StringSplitOptions.RemoveEmptyEntries); dictData.Add(col, Array.ConvertAll(sa, i => float.Parse(i))); } } } if (!bson.Contains(col)) { if (col == "test_date") { bson.Add(col, Convert.ToDateTime(dictData[col]).ToLocalTime()); } else { bson.AddRange(dictData); } } } } BsonDocument rootDoc = new BsonDocument { { "_id", "COB-" + bson["test_id"] }, { "mid", "COBMID-" + bson["cob_test_id"] }, { "timestamp", bson["test_date"] }, { "step", "download" }, { "type", bson["dut_type"] }, { "subtype", "dc" }, { "result", bson["fail_code"].ToString().Equals("Pass", StringComparison.Ordinal) ? "P" : "F" }, { "measstatus", bson["pass_fail"].ToString().Equals("Pass", StringComparison.Ordinal) ? "P" : "F" }, { "status", bson["pass_fail"].ToString().Equals("Pass", StringComparison.Ordinal) ? "P" : "F" } }; rootDoc.Add("meta", new BsonDocument { { "StartDateTime", bson["test_date"] }, { "EndDateTime", bson["test_date"] }, { "Channel", bson["channel"] }, { "FirmwareVer", bson["fw_version"] }, { "SWVer", bson["sw_version"] }, { "TestStation", bson["test_station"] } }); if (bson["serial_number"] != null) { bson["sn"] = bson["serial_number"]; bson.Add("laser_pn", new BsonArray()); bson.Remove("serial_number"); string partNum = bson["part_number"].ToString(); if (partNum.Contains("REFUNIT")) { continue; } string serNum = bson["sn"].ToString(); string pcbSerNum = bson["pcb_serial_number"].ToString(); var UKDeviceType = "Not Found"; var UKPartNumber = "Not Found"; var UKDescription = "Not Found"; var UKPartRevision = "Not Found"; var hasRows = false; string oraQuery; OracleCommand oraCmd; OracleDataReader dr = null; try { if (!pcbSerNum.Equals("", StringComparison.Ordinal)) { // ----- 2017-08-25: Adding connection to Livingston I-Track DB ----- oraQuery = "SELECT DISTINCT DEVICE_OBJ.DEVICE_ID, ROUTE, ROUTE_OBJ.DESCRIPTION FROM KAIAM.DEVICE_OBJ, KAIAM.ASSEMBLE_PROC, KAIAM.ASSEMBLE_DATA, KAIAM.ROUTE_OBJ WHERE ASSEMBLE_PROC.ASSEMBLE_PROC_UID = ASSEMBLE_DATA.ASSEMBLE_PROC_UID AND ASSEMBLE_DATA.DEVICE_OBJ_UID = DEVICE_OBJ.DEVICE_OBJ_UID AND DEVICE_OBJ.DEVICE_ID = '" + pcbSerNum + "' AND ROUTE = ROUTE_ID ORDER BY ROUTE_SEQ DESC FETCH FIRST 1 ROWS ONLY"; oraCmd = new OracleCommand(oraQuery, oraConn); oraCmd.CommandType = CommandType.Text; oraCmd.CommandTimeout = 7200; dr = oraCmd.ExecuteReader(); hasRows = dr.Read(); } if (!hasRows) { oraQuery = "SELECT DISTINCT DEVICE_OBJ.DEVICE_ID, ROUTE, ROUTE_OBJ.DESCRIPTION FROM KAIAM.DEVICE_OBJ, KAIAM.ASSEMBLE_PROC, KAIAM.ASSEMBLE_DATA, KAIAM.ROUTE_OBJ WHERE ASSEMBLE_PROC.ASSEMBLE_PROC_UID = ASSEMBLE_DATA.ASSEMBLE_PROC_UID AND ASSEMBLE_DATA.DEVICE_OBJ_UID = DEVICE_OBJ.DEVICE_OBJ_UID AND DEVICE_OBJ.DEVICE_ID = '" + serNum + "' AND ROUTE = ROUTE_ID ORDER BY ROUTE_SEQ DESC FETCH FIRST 1 ROWS ONLY"; oraCmd = new OracleCommand(oraQuery, oraConn); oraCmd.CommandType = CommandType.Text; oraCmd.CommandTimeout = 7200; dr = oraCmd.ExecuteReader(); hasRows = dr.Read(); } } catch (Exception executeException) { Program.log("LS2 I-Track DB query ERROR: " + executeException.Message + "\n" + executeException.StackTrace); return(-1); } try { if (hasRows) { var route = dr["Route"].ToString().Split('_'); UKDeviceType = route[1] + route[2]; UKPartNumber = route[3]; UKPartRevision = route[4]; UKDescription = dr["DESCRIPTION"].ToString(); } } catch (Exception e) { Program.log("LS2 I-Track DB data output ERROR: " + e.Message + " (" + serNum + ")"); } // ------------------------------------------------------------------ rootDoc.Add("device", new BsonDocument { { "SerialNumber", serNum }, { "PartNumber", partNum }, { "PCBSerialNumber", bson["pcb_serial_number"] }, // ----- 2017-08-25: Adding connection to Livingston I-Track DB ----- { "UKDeviceType", UKDeviceType }, { "UKDevicePartNumber", UKPartNumber }, { "UKDeviceDescription", UKDescription }, { "UKDeviceRevision", UKPartRevision } // ------------------------------------------------------------------ }); bson.Remove("test_id"); bson.Remove("cob_test_id"); bson.Remove("test_date"); bson.Remove("channel"); bson.Remove("fail_code"); bson.Remove("pass_fail"); bson.Remove("dut_type"); bson.Remove("fw_version"); bson.Remove("part_number"); bson.Remove("sw_version"); bson.Remove("test_station"); bson.Remove("pcb_serial_number"); rootDoc.Add("data", bson); mvh.Collection.Save(rootDoc); } } //close Data Reader and connection dataReader.Close(); connection.Close(); // ----- 2017-08-25: Adding connection to Livingston I-Track DB ----- oraConn.Close(); // ------------------------------------------------------------------ } catch (Exception exc) { Program.log("LS2 import ERROR: " + exc.Message + "\n" + exc.StackTrace); return(-1); } return(cnt); }