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 = { ' ' }; 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)) { 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); rootDoc.Add("device", new BsonDocument { { "SerialNumber", serNum}, { "TosaType", tosaType} }); 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(); } catch (Exception exc) { Program.log("TOSA import ERROR: " + exc.Message + "\n" + exc.StackTrace); } return cnt; }
// 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); } }