/// <summary>
/// Checks if entry already exists in DB
/// </summary>
/// <param name="request"></param>
/// <param name="powerUsageLine"></param>
/// <param name="dataValue"></param>
/// <returns>bool entry exists</returns>
private bool rowExists(JMessage request, JEnterPowerUsageDataLineRequest powerUsageLine, JDataValue dataValue, SqlConnection conn)
{
DataSet ds = new DataSet("ConsumerCounter");
using (SqlCommand command = new SqlCommand("spGetSingleConsumerCounter", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@DATE", powerUsageLine.dtCreated));
command.Parameters.Add(new SqlParameter("@CONSUMPTION_TYPE_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);
}
public JEnterDataLineReply handlePowerUsageLine(JMessage request)
{
SqlConnection conn = new SqlConnection(connStringBuilder.ConnectionString);
JEnterPowerUsageDataLineRequest powerLine = JsonConvert.DeserializeObject <JEnterPowerUsageDataLineRequest>(request.InnerMessage);
JEnterDataLineReply reply = new JEnterDataLineReply();
reply.ErrorCode = 0;
reply.Success = true;
reply.ErrorText = "OK";
try
{
for (int i = 0; i < powerLine.DataColumns.Count; i++)
{
JDataValue dataValue = powerLine.DataColumns[i];
if (!rowExists(request, powerLine, dataValue, conn))
{
if (!insertRow(request, powerLine, dataValue, conn))
{
reply.Success = false;
reply.ErrorCode = 300;
reply.ErrorText = "Could not insert line";
break;
}
}
else
{
Receiver.LogEntry(System.Diagnostics.EventLogEntryType.Warning, "DataBaseDriver.handlePowerUsageLine Entry already exists for value " + powerLine.SourceFile + " Line " + powerLine.LineNumber + " value " + dataValue.Value.ToString());
Console.WriteLine("Entry already exists for " + powerLine.SourceFile + " Line " + powerLine.LineNumber + " value " + dataValue.Value.ToString());
}
}
}
catch (Exception ex)
{
Receiver.LogEntry(System.Diagnostics.EventLogEntryType.Error, "DataBaseDriver.handlePowerUsageLine 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.handlePowerUsageLine success for " + powerLine.SourceFile + " Line " + powerLine.LineNumber);
Console.WriteLine("Success for " + powerLine.SourceFile + " Line " + powerLine.LineNumber);
}
return(reply);
}
private bool insertRow(JMessage request, JEnterPowerUsageDataLineRequest prodLine, JDataValue dataValue, SqlConnection conn)
{
DataSet ds = new DataSet("ConsumerCounter");
try
{
decimal counterValue = Decimal.Parse(dataValue.Value);
using (SqlCommand command = new SqlCommand("spAddConsumerCounter", 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("@CONSUMPTION_TYPE_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 failed for PowerUsageLine: " + ex.Message);
return(false);
}
}
protected override void sendDataLine(List <String> lstData)
{
JMessage jMessage = new JMessage();
jMessage.Function = MessageFunctions.Str_JEnterPowerUsageDataLineRequest;
jMessage.Sender = this.locationName;
JEnterPowerUsageDataLineRequest jEnterPowerUsageDataLineRequest = new JEnterPowerUsageDataLineRequest();
jEnterPowerUsageDataLineRequest.SourceFile = this.fiSource.Name;
jEnterPowerUsageDataLineRequest.LineNumber = lineCounter;
jEnterPowerUsageDataLineRequest.DataColumns = new List <JDataValue>();
DataClient.logEntry(System.Diagnostics.EventLogEntryType.Information, "ConsumptionFile.sendDataLine begin for "
+ this.fiSource.Name + " Line " + lineCounter);
Console.WriteLine("ConsumptionFile.sendDataLine begin for " + this.fiSource.Name + " Line " + lineCounter);
int iHeaderIndex = 0;
// set the timestamp of the line, it is the first entry
jEnterPowerUsageDataLineRequest.dtCreated = DateTime.ParseExact(lstData[0].Replace("\"", ""), DateTimeFormat, System.Globalization.CultureInfo.InvariantCulture);
// begin to iterate at the second position, first is the timestamp
for (int i = 1; i < lstData.Count; i++)
{
jEnterPowerUsageDataLineRequest.DataColumns.Add(new JDataValue()
{
Key = lstHeader[iHeaderIndex].Key, Value = lstData[i], Unity = lstHeader[iHeaderIndex].Unity, Device = lstHeader[iHeaderIndex].Device, Article = lstHeader[iHeaderIndex].Article
});
iHeaderIndex++;
}
jMessage.InnerMessage = Newtonsoft.Json.JsonConvert.SerializeObject(jEnterPowerUsageDataLineRequest);
String message = Newtonsoft.Json.JsonConvert.SerializeObject(jMessage);
bool sendSuccess = false;
String reply = DataManager.Network.TCPClient.sendMessage(message, "", out sendSuccess);
if (handleReply(reply))
{
DataClient.logEntry(System.Diagnostics.EventLogEntryType.SuccessAudit, "ConsumptionFile.sendDataLine success: " + this.fiSource.Name + " Line " + lineCounter);
Console.WriteLine("ConsumptionFile.sendDataLine Success for " + this.fiSource.Name + " Line " + lineCounter);
}
}