// 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);
}
