public JEnterDataLineReply handleProductionDataLine(JMessage request)
{
SqlConnection conn = new SqlConnection(connStringBuilder.ConnectionString);
JEnterProductionDataLineRequest prodLine = JsonConvert.DeserializeObject <JEnterProductionDataLineRequest>(request.InnerMessage);
JEnterDataLineReply reply = new JEnterDataLineReply();
reply.ErrorCode = 0;
reply.Success = true;
reply.ErrorText = "OK";
try
{
for (int i = 0; i < prodLine.DataColumns.Count; i++)
{
JDataValue dataValue = prodLine.DataColumns[i];
if (!rowExists(request, prodLine, dataValue, conn))
{
if (!insertRow(request, prodLine, dataValue, conn))
{
reply.Success = false;
reply.ErrorCode = 300;
reply.ErrorText = "Could not insert line";
break;
}
}
else
{
Receiver.LogEntry(System.Diagnostics.EventLogEntryType.Warning, "DataBaseDriver.handleProductionLine Entry already exists for value " + prodLine.SourceFile + " Line " + prodLine.LineNumber + " value " + dataValue.Value.ToString());
Console.WriteLine("Entry already exists for " + prodLine.SourceFile + " Line " + prodLine.LineNumber + " value " + dataValue.Value.ToString());
}
}
}
catch (Exception ex)
{
Receiver.LogEntry(System.Diagnostics.EventLogEntryType.Error, "DataBaseDriver.handleProductionLine failed: " + ex.Message);
reply.Success = false;
reply.ErrorCode = 301;
reply.ErrorText = "DB Error";
}
finally
{
conn.Close();
}
if (reply.Success)
{
Receiver.LogEntry(System.Diagnostics.EventLogEntryType.SuccessAudit, "DataBaseDriver.handleProductionLineRequest success for " + prodLine.SourceFile + " Line " + prodLine.LineNumber);
Console.WriteLine("Success for " + prodLine.SourceFile + " Line " + prodLine.LineNumber);
}
return(reply);
}
/// <summary>
/// Checks if entry already exists in DB
/// </summary>
/// <param name="request"></param>
/// <param name="prodLine"></param>
/// <param name="dataValue"></param>
/// <returns>bool entry exists</returns>
private bool rowExists(JMessage request, JEnterProductionDataLineRequest prodLine, JDataValue dataValue, SqlConnection conn)
{
DataSet ds = new DataSet("ScaleCounter");
using (SqlCommand command = new SqlCommand("spGetSingleScaleCounter", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@DATE", prodLine.dtCreated));
command.Parameters.Add(new SqlParameter("@ARTICLE_NAME", dataValue.Article));
command.Parameters.Add(new SqlParameter("@UNIT_NAME", dataValue.Unity));
command.Parameters.Add(new SqlParameter("@DEVICE_NAME", dataValue.Device));
command.Parameters.Add(new SqlParameter("@LOCATION_NAME", request.Sender));
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
da.Fill(ds);
}
// Entry does not exist, insert the row to DB
if (ds.Tables[0].Rows.Count == 0)
{
return(false);
}
return(true);
}
private bool insertRow(JMessage request, JEnterProductionDataLineRequest prodLine, JDataValue dataValue, SqlConnection conn)
{
DataSet ds = new DataSet("ScaleCounter");
try
{
decimal counterValue = Decimal.Parse(dataValue.Value);
using (SqlCommand command = new SqlCommand("spAddScaleCounter", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@DATE", prodLine.dtCreated));
command.Parameters.Add(new SqlParameter("@PROCESSING_DATE", DateTime.Now));
command.Parameters.Add(new SqlParameter("@COUNTER_VALUE", counterValue));
command.Parameters.Add(new SqlParameter("@LOCATION_NAME", request.Sender));
command.Parameters.Add(new SqlParameter("@UNIT_NAME", dataValue.Unity));
command.Parameters.Add(new SqlParameter("@ARTICLE_NAME", dataValue.Article));
command.Parameters.Add(new SqlParameter("@DEVICE_NAME", dataValue.Device));
command.Parameters.Add(new SqlParameter("@SOURCE", prodLine.SourceFile));
command.Parameters.Add(new SqlParameter("@SOURCE_INDEX", prodLine.LineNumber));
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
da.Fill(ds);
}
// Entry does not exist, insert the row to DB
if (ds.Tables[0].Rows.Count == 0)
{
return(false);
}
return(true);
}
catch (Exception ex)
{
Receiver.LogEntry(System.Diagnostics.EventLogEntryType.Error, "DataBaseDriver.insertRow for ProductionDataLine failed: " + ex.Message);
return(false);
}
}
