internal static string GetCurrentRunningNumber(SqlConnection conn, ref SernumInfo sernumInfo)
{
try
{
string cmdText = string.Format("Select TOP 1 sn.RunningNumber " +
"From [VpdOF].[dbo].[CalLeakAutoSN] As sn " +
"Where sn.ModelNumber = '{0}' And YEAR(sn.LogDate) = YEAR(GetDate()) AND MONTH(sn.LogDate) = MONTH(GetDate()) " +
"Order By sn.LogDate DESC", sernumInfo.Model);
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
SqlCommand command = new SqlCommand(cmdText, conn);
using (SqlDataReader rdr = command.ExecuteReader())
{
if (rdr.HasRows)
{
rdr.Read();
int currNumber = rdr[0] as int? ?? default(int);
sernumInfo.RunningNumber = currNumber;
return(string.Format("P{0}", sernumInfo.RunningNumber.ToString("D3")));
}
else
{
sernumInfo.RunningNumber = 1;
return("P001");
}
}
}
catch (Exception)
{
throw;
}
}
internal static object SubmitAutoGeneratedSernum(SqlConnection conn, ref SernumInfo mySerialNumInfo)
{
try
{
string cmdText = "INSERT INTO [VpdOF].[dbo].[CalLeakAutoSN] (LogDate, ModelNumber, SerialNumber, SerialNumberDummy, IsPassTest, RunningNumber) " +
"VALUES (@LogDate, @ModelNumber, @SerialNumber, @SerialNumberDummy, @IsPassTest, @RunningNumber)";
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@LogDate", System.Data.SqlDbType.DateTime));
cmd.Parameters["@LogDate"].Value = mySerialNumInfo.LogDate;
cmd.Parameters.AddWithValue("@ModelNumber", mySerialNumInfo.Model);
cmd.Parameters.AddWithValue("@SerialNumber", mySerialNumInfo.Sernum);
cmd.Parameters.AddWithValue("@SerialNumberDummy", mySerialNumInfo.DummySernum);
cmd.Parameters.AddWithValue("@IsPassTest", mySerialNumInfo.IsPassTest);
cmd.Parameters.AddWithValue("@RunningNumber", mySerialNumInfo.RunningNumber);
return(cmd.ExecuteScalar());
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// This method is called when the test is completed
/// </summary>
/// <param name="_uutData"></param>
/// <param name="_commonData"></param>
public void UserCleanup(ref UUTData _uutData, ref CommonData _commonData)
{
try
{
// Disconnect 5V supply and TTL signal from the Sensor Board
InstrumentIO.DisconnectTempBoardMeasRoute(mySw, TestPort);
InstrumentIO.CloseAllTestValve(mySw);
if (myLD != null)
{
// at the end of the test, vent the Leak Detector
//Trace.WriteLine("Vent the Leak Detector");
//myLD.Vent();
Trace.WriteLine("Disconnecting Serial Port Communication....");
myLD.Close();
myLD = null;
Trace.WriteLine(" ... disconnected");
}
// Destruct the Test Classes
myInitStationTest = null;
myCalLeakTest = null;
mySystemCalTest = null;
myButtonUpTest = null;
// Check FinalTest outcome, Pass/Fail flag P,F,A,E and the test is in Production Mode
if (_commonData.TestOutcome.ToUpper() == "P" && _commonData.isProdMode)
{
Trace.WriteLine("Submitting Cal-Leak data summary to database...");
string sqlConStr = CalLeakConfigs.Default.SqlConString;
myCalLeakData.IsPass = true; // Pass
using (SqlConnection conn = new SqlConnection(sqlConStr))
{
conn.Open();
var retVal = SqlHelper.SubmitFinalCalLeakData(conn, ref myCalLeakData);
Trace.WriteLine("CalLeak Data transfered to database.");
// Create certificate history for the unit
int id = Convert.ToInt32(retVal);
string eqIds = SqlHelper.GetCsvStringEquipmentUsed(conn, 2, 1);
// save UUT certificate details
SqlHelper.SaveCertDetails(conn, id, eqIds);
}
Trace.WriteLine("Generating new Serial Number...");
// Autogenerate Serial Number
string modelNumber = _uutData.Model;
string serialNumber = string.Empty;
DateTime todayDate = DateTime.Now;
int workWeek = DateTimeHelper.GetIso8601WeekOfYear(todayDate);
SernumInfo mySerialNumInfo = new SernumInfo();
mySerialNumInfo.Model = modelNumber;
using (SqlConnection conn = new SqlConnection(sqlConStr))
{
Trace.WriteLine("Connecting to SQL Database for serial number retrieval");
conn.Open();
Trace.WriteLine("Connection succeeded");
var newSernum = SqlHelper.GetNextRunningNumber(conn, ref mySerialNumInfo);
//serialNumber = string.Format("MY{0}{1}{2}", todayDate.ToString("yy"), todayDate.ToString("MM"), newSernum); // commented out due to serial number generation must use YYWW
serialNumber = string.Format("MY{0}{1}{2}", todayDate.ToString("yy"), workWeek.ToString("00"), newSernum);
Trace.WriteLine("New Serial Number is = " + serialNumber);
}
Trace.WriteLine("Submitting new serial number to database..");
DateTime date = DateTime.Now;
mySerialNumInfo.DummySernum = _uutData.SerNum;
mySerialNumInfo.Model = _uutData.Model;
mySerialNumInfo.IsPassTest = true;
mySerialNumInfo.Sernum = serialNumber;
mySerialNumInfo.LogDate = date;
mySerialNumInfo.RunningNumber = mySerialNumInfo.RunningNumber;// +1;
string conStr = CalLeakConfigs.Default.SqlConString;
using (SqlConnection conn = new SqlConnection(conStr))
{
conn.Open();
var retVal = SqlHelper.SubmitAutoGeneratedSernum(conn, ref mySerialNumInfo);
Trace.WriteLine(retVal.ToString());
}
Trace.WriteLine("Done");
}
else if (_commonData.isProdMode)
{
Trace.WriteLine("Submitting Cal-Leak data summary to database...");
string sqlConStr = CalLeakConfigs.Default.SqlConString;
myCalLeakData.IsPass = false; // Failed
using (SqlConnection conn = new SqlConnection(sqlConStr))
{
conn.Open();
var retVal = SqlHelper.SubmitFinalCalLeakData(conn, ref myCalLeakData);
Trace.WriteLine("CalLeak Data transfered to database.");
}
}
}
catch (Exception)
{
}
finally
{
_uutData = null;
//_commonData = null;
// todo: play sound for test completion or tower light
}
}
