/// <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);
            }
        }
Beispiel #4
0
        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);
            }
        }