public static bool WriteMessageMeterDataToDataTable(MeterReadingEntity sm, string strConnectionString, string strStorageTableName) { try { var storageAccount = Microsoft.WindowsAzure.Storage.CloudStorageAccount.Parse(CloudConfigurationManager.GetSetting(strConnectionString)); // Create the table client. var tableClient = storageAccount.CreateCloudTableClient(); var storageTable = tableClient.GetTableReference(strStorageTableName); storageTable.CreateIfNotExists(); try { if (sm != null) { var insertRecord = TableOperation.Insert(sm); storageTable.Execute(insertRecord); } return(true); } catch { return(false); } } catch (Exception) { return(false); } }
public void ProcessQueue(CloudBlockBlob blob, CloudQueueMessage receivedMessage, CloudQueue urbanWaterQueue) { EventSourceWriter.Log.MessageMethod( $"WaterMeterCSVProcessor_Ovod Message Processing Started. MessageId: {receivedMessage.Id}, BLOB name: {blob.Name}"); var currentContext = new SQLAzureDataContext(); var missingMeterIds = new List <string>(); var processedMeterIds = new List <string>(); using (var sr = new StringReader(blob.DownloadText())) { using (var csv = new CsvReader(sr)) { csv.Configuration.Delimiter = ","; while (csv.Read()) { try { if (csv.CurrentRecord[1] != null && csv.CurrentRecord[2] != null && csv.CurrentRecord[3] != null && csv.CurrentRecord[0] != null) { var creationDateTime = DateTime.ParseExact(csv.CurrentRecord[2], "MM/dd/yyyy hh:mm tt", null).ToUniversalTime(); var meterIdentity = csv.CurrentRecord[1]; try { var meterSet = currentContext.Meters.Where(x => x.MeterIdentity == meterIdentity); if (!meterSet.Any()) { if (!missingMeterIds.Contains(meterIdentity)) { missingMeterIds.Add(meterIdentity); } } else { if (!processedMeterIds.Contains(meterIdentity)) { processedMeterIds.Add(meterIdentity); } } } catch (Exception e) { EventSourceWriter.Log.MessageMethod( $"EXCEPTION: WaterMeterCSVProcessor_Ovod. {e.Message}"); } try { var firstOrDefault = currentContext.Meters.FirstOrDefault(x => x.MeterIdentity == meterIdentity); if (firstOrDefault != null) { var thisDma = firstOrDefault.DMA; var sm = new MeterReadingEntity { PartitionKey = meterIdentity, CreatedOn = creationDateTime, RowKey = creationDateTime.Ticks.ToString(), Reading = csv.CurrentRecord[3], Encrypted = false, DMA = thisDma.Identifier }; const string strWriteConnectionString = "MKWDNConnectionString"; var blAttempt = WriteMessageMeterDataToDataTable(sm, strWriteConnectionString, thisDma.Site.TableName); if (!blAttempt) { EventSourceWriter.Log.MessageMethod( $"ERROR writing to BLOB storage by WaterMeterCSVProcessor_Ovod. Received message Id: {receivedMessage.Id}BLOB name: {blob.Name}, PartitionKey: {sm.PartitionKey}"); } } } catch (Exception e) { EventSourceWriter.Log.MessageMethod( $"ERROR writing to BLOB storage in WaterMeterCSVProcessor_Ovod. Received message Id: {receivedMessage.Id}BLOB name: {blob.Name}, Exception: {e.Message}"); } } else { EventSourceWriter.Log.MessageMethod( $"ERROR: Null values parsed in CSV file by WaterMeterCSVProcessor_Ovod. Blob name: {blob.Name}"); } } catch (Exception e) { EventSourceWriter.Log.MessageMethod( $"Exception processing CSV meter reading in WaterMeterCSVProcessor_Ovod. Exception: {e.Message}"); } } } } EventSourceWriter.Log.MessageMethod( $"WaterMeterCSVProcessor_Ovod Message Processing Complete. MessageId: {receivedMessage.Id}"); urbanWaterQueue.DeleteMessage(receivedMessage); var newEvent = new Event { BusDispatched = false, EventDateTime = DateTime.Now, EventType = (int)EventTypes.NewMeteringData }; var processedMetercsv = processedMeterIds.Aggregate(string.Empty, (current, meterId) => current + (meterId + ", ")); newEvent.Description = processedMetercsv; currentContext.Events.InsertOnSubmit(newEvent); if (missingMeterIds.Count > 0) { var newMeterMissingEvent = new Event { BusDispatched = false, EventDateTime = DateTime.Now, EventType = (int)EventTypes.MissingMeter }; var metercsv = missingMeterIds.Aggregate(string.Empty, (current, meterId) => current + (meterId + ", ")); newMeterMissingEvent.Description = metercsv; currentContext.Events.InsertOnSubmit(newMeterMissingEvent); } currentContext.SubmitChanges(); }
public void ProcessQueue(CloudBlockBlob blob, CloudQueueMessage receivedMessage, CloudQueue urbanWaterQueue) { var currentContext = new SQLAzureDataContext(); var xml = blob.DownloadText(); var xDoc = XDocument.Parse(xml); var root = xDoc.Elements(); var gateways = root.Elements().FirstOrDefault(x => x.Name.LocalName == "gateways"); var missingMeterIds = new List <string>(); var processedMeterIds = new List <string>(); try { foreach (var gateway in gateways?.Elements().Where(x => x.Name.LocalName == "gateway")) { var collections = gateway.Elements().FirstOrDefault(x => x.Name.LocalName == "collections"); foreach (var collection in collections.Elements().Where(x => x.Name.LocalName == "collection")) { var producerId = collection.Elements().FirstOrDefault(x => x.Name.LocalName == "producer-id").Value; var data = collection.Elements().FirstOrDefault(x => x.Name.LocalName == "data"); foreach (var record in data.Elements()) { var recordDateTime = record.Attributes().FirstOrDefault(x => x.Name.LocalName == "ts").Value; var recordValue = record.Elements().FirstOrDefault(x => x.Name.LocalName == "CURRENT_INDEX").Value; if (!string.IsNullOrEmpty(producerId) && !string.IsNullOrEmpty(recordDateTime) && !string.IsNullOrEmpty(recordValue) ) { try { var meterSet = currentContext.Meters.Where(x => x.MeterIdentity == producerId); if (!meterSet.Any()) { if (!missingMeterIds.Contains(producerId)) { missingMeterIds.Add(producerId); } } else { if (!processedMeterIds.Contains(producerId)) { processedMeterIds.Add(producerId); } } } catch (Exception e) { EventSourceWriter.Log.MessageMethod("EXCEPTION: WaterMeterQueueXMLProcessor. " + e.Message); } try { var firstOrDefault = currentContext.Meters.FirstOrDefault(x => x.MeterIdentity == producerId); if (firstOrDefault != null) { var thisDma = firstOrDefault.DMA; var sm = new MeterReadingEntity { PartitionKey = producerId, CreatedOn = DateTime.Parse(recordDateTime), RowKey = DateTime.Parse(recordDateTime).Ticks.ToString(), Reading = recordValue, Encrypted = false, DMA = thisDma.Identifier }; const string strWriteConnectionString = "MKWDNConnectionString"; var blAttempt = WriteMessageMeterDataToDataTable(sm, strWriteConnectionString, thisDma.Site.TableName); if (!blAttempt) { EventSourceWriter.Log.MessageMethod( $"ERROR writing to BLOB storage by WaterMeterQueueXMLProcessor. Received message Id: {receivedMessage.Id}BLOB name: {blob.Name}, PartitionKey: {sm.PartitionKey}"); } } } catch (Exception e) { EventSourceWriter.Log.MessageMethod( $"ERROR writing to BLOB storage in WaterMeterQueueXMLProcessor. Received message Id: {receivedMessage.Id}BLOB name: {blob.Name}, Exception: {e.Message}"); } } else { EventSourceWriter.Log.MessageMethod( $"ERROR: Null values parsed in CSV file by WaterMeterQueueXMLProcessor. Blob name: {blob.Name}"); } } } } } catch (Exception e) { EventSourceWriter.Log.MessageMethod( $"Exception processing CSV meter reading in WaterMeterQueueXMLProcessor. Exception: {e.Message}"); } urbanWaterQueue.DeleteMessage(receivedMessage); var newEvent = new Event { BusDispatched = false, EventDateTime = DateTime.Now, EventType = (int)EventTypes.NewMeteringData }; var processedMetercsv = processedMeterIds.Aggregate(string.Empty, (current, meterId) => current + (meterId + ", ")); newEvent.Description = processedMetercsv; currentContext.Events.InsertOnSubmit(newEvent); if (missingMeterIds.Count > 0) { var newMeterMissingEvent = new Event { BusDispatched = false, EventDateTime = DateTime.Now, EventType = (int)EventTypes.MissingMeter }; var metercsv = missingMeterIds.Aggregate(string.Empty, (current, meterId) => current + (meterId + ", ")); newMeterMissingEvent.Description = metercsv; currentContext.Events.InsertOnSubmit(newMeterMissingEvent); } currentContext.SubmitChanges(); }
public void ProcessQueue(CloudBlockBlob blob, string strContainer, CloudQueueMessage receivedMessage, CloudQueue urbanWaterQueue) { EventSourceWriter.Log.MessageMethod("Worker Role Prcessing HeadEndSystem file " + receivedMessage.Id.ToString() + " " + blob.Name); SQLAzureDataContext currentContext = new SQLAzureDataContext(); using (TextReader sr = new StringReader(blob.DownloadText())) { using (var csv = new CsvReader(sr)) { csv.Configuration.Delimiter = ";"; while (csv.Read()) { try { MeterReadingEntity sm = new MeterReadingEntity(); DMAMeterReadingEntity dmasm = new DMAMeterReadingEntity(); if (csv.CurrentRecord[0] != null && csv.CurrentRecord[3] != null && csv.CurrentRecord[4] != null && csv.CurrentRecord[5] != null && string.Compare(csv.CurrentRecord[5], "LITER") == 0) { DateTime creationDateTime = DateTime.ParseExact(csv.CurrentRecord[3], "dd/MM/yyyy HH:mm:ss", null).ToUniversalTime(); // DMAMeterTableStorageContext dmamcontext = new DMAMeterTableStorageContext(strContainer + "_dmameter"); // DMAMeterEntity dmaMeterEntity = dmamcontext.MeterReadings.FirstOrDefault(x => x.PartitionKey == csv.CurrentRecord[0]); string dmaId = currentContext.DMAs.Where(x => x.Name == "El Toyo – Retamar").FirstOrDefault().Identifier; sm.PartitionKey = csv.CurrentRecord[0]; sm.CreatedOn = creationDateTime; sm.RowKey = creationDateTime.Ticks.ToString(); sm.Reading = csv.CurrentRecord[4]; sm.Encrypted = false; sm.DMA = dmaId; dmasm.PartitionKey = dmaId; dmasm.CreatedOn = creationDateTime; dmasm.RowKey = creationDateTime.Ticks.ToString(); dmasm.Reading = csv.CurrentRecord[4]; dmasm.Encrypted = false; dmasm.MeterID = csv.CurrentRecord[0]; } else { EventSourceWriter.Log.MessageMethod("ERROR: Null values parsed in HeadEndSystem file " + blob.Name); } string strWriteConnectionString = "MKWDNConnectionString"; bool blAttempt = WriteMessageMeterDataToDataTable(sm, dmasm, strWriteConnectionString, strContainer); if (!blAttempt) { EventSourceWriter.Log.MessageMethod("ERROR:Processing Entry NOT added to storage " + receivedMessage.Id.ToString()); EventSourceWriter.Log.MessageMethod("ERROR:Processing Entry NOT added to storage " + blob.Name + " " + sm.PartitionKey); } } catch (Exception e) { EventSourceWriter.Log.MessageMethod("Exception processing meter reading: " + e.Message); } } } } EventSourceWriter.Log.MessageMethod("Processing Complete Entry added to storage " + receivedMessage.Id.ToString()); urbanWaterQueue.DeleteMessage(receivedMessage); Event newEvent = new Event(); newEvent.BusDispatched = false; newEvent.EventDateTime = DateTime.Now; newEvent.EventType = (int)EventTypes.NewMeteringData; currentContext.Events.InsertOnSubmit(newEvent); currentContext.SubmitChanges(); }
public void ProcessQueue(CloudBlockBlob blob, CloudQueueMessage receivedMessage, CloudQueue urbanWaterQueue) { EventSourceWriter.Log.MessageMethod( $"WaterMeterExcelProcessor_Ovod Message Processing Started. MessageId: {receivedMessage.Id}, BLOB name: {blob.Name}"); var currentContext = new SQLAzureDataContext(); var missingMeterIds = new List <string>(); var processedMeterIds = new List <string>(); var stream = new MemoryStream(); blob.DownloadToStream(stream); using (var spreadsheetDocument = SpreadsheetDocument.Open(stream, false)) { var workbookPart = spreadsheetDocument.WorkbookPart; var worksheetPart = workbookPart.WorksheetParts.First(); var sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); foreach (var r in sheetData.Elements <Row>()) { if (r.RowIndex != 1) { var creationDateTime = DateTime.Now; string meterIdentity = null; string reading = null; string customer = null; var column = 1; foreach (var c in r.Elements <Cell>()) { switch (column) { case 1: customer = c.InnerText; break; case 2: meterIdentity = c.InnerText; break; case 3: creationDateTime = DateTime.ParseExact(c.InnerText, "MM/dd/yyyy hh:mm tt", null).ToUniversalTime(); break; case 4: reading = c.InnerText; break; default: break; } column = column + 1; } try { if (string.IsNullOrEmpty(meterIdentity)) { meterIdentity = currentContext.Customers.FirstOrDefault(x => x.CustomerNumber == customer).Meter.MeterIdentity; } var meterSet = currentContext.Meters.Where(x => x.MeterIdentity == meterIdentity); if (!meterSet.Any()) { if (!missingMeterIds.Contains(meterIdentity)) { missingMeterIds.Add(meterIdentity); } } else { if (!processedMeterIds.Contains(meterIdentity)) { processedMeterIds.Add(meterIdentity); } } } catch (Exception e) { EventSourceWriter.Log.MessageMethod($"EXCEPTION: WaterMeterExcelProcessor_Ovod. {e.Message}"); } try { var firstOrDefault = currentContext.Meters.FirstOrDefault(x => x.MeterIdentity == meterIdentity); if (firstOrDefault != null) { var thisDma = firstOrDefault.DMA; var sm = new MeterReadingEntity { PartitionKey = meterIdentity, CreatedOn = creationDateTime, RowKey = creationDateTime.Ticks.ToString(), Reading = reading, Encrypted = false, DMA = thisDma.Identifier }; const string strWriteConnectionString = "MKWDNConnectionString"; var blAttempt = WriteMessageMeterDataToDataTable(sm, strWriteConnectionString, thisDma.Site.TableName); if (!blAttempt) { EventSourceWriter.Log.MessageMethod( $"ERROR writing to BLOB storage by WaterMeterExcelProcessor_Ovod. Received message Id: {receivedMessage.Id}BLOB name: {blob.Name}, PartitionKey: {sm.PartitionKey}"); } } } catch (Exception e) { EventSourceWriter.Log.MessageMethod( $"ERROR writing to BLOB storage in WaterMeterExcelProcessor_Ovod. Received message Id: {receivedMessage.Id}BLOB name: {blob.Name}, Exception: {e.Message}"); } } } } EventSourceWriter.Log.MessageMethod("WaterMeterExcelProcessor_Ovod Message Processing Complete. MessageId: " + receivedMessage.Id); urbanWaterQueue.DeleteMessage(receivedMessage); var newEvent = new Event { BusDispatched = false, EventDateTime = DateTime.Now, EventType = (int)EventTypes.NewMeteringData }; var processedMetercsv = processedMeterIds.Aggregate(string.Empty, (current, meterId) => current + (meterId + ", ")); newEvent.Description = processedMetercsv; currentContext.Events.InsertOnSubmit(newEvent); if (missingMeterIds.Count > 0) { var newMeterMissingEvent = new Event { BusDispatched = false, EventDateTime = DateTime.Now, EventType = (int)EventTypes.MissingMeter }; var metercsv = missingMeterIds.Aggregate(string.Empty, (current, meterId) => current + (meterId + ", ")); newMeterMissingEvent.Description = metercsv; currentContext.Events.InsertOnSubmit(newMeterMissingEvent); } currentContext.SubmitChanges(); }
public void ProcessQueue(CloudBlockBlob blob, string strContainer, CloudQueueMessage receivedMessage, CloudQueue urbanWaterQueue) { EventSourceWriter.Log.MessageMethod("Worker Role Prcessing Water Meter Reading file " + receivedMessage.Id.ToString() + " " + blob.Name); SQLAzureDataContext currentContext = new SQLAzureDataContext(); var xml = blob.DownloadText(); var xDoc = XDocument.Parse(xml); IEnumerable <XElement> root = xDoc.Elements(); XElement gateways = root.Elements().Where(x => x.Name.LocalName == "gateways").FirstOrDefault(); try { foreach (XElement gateway in gateways.Elements().Where(x => x.Name.LocalName == "gateway")) { XElement collections = gateway.Elements().Where(x => x.Name.LocalName == "collections").FirstOrDefault(); foreach (XElement collection in collections.Elements().Where(x => x.Name.LocalName == "collection")) { string ProducerId = collection.Elements().Where(x => x.Name.LocalName == "producer-id").FirstOrDefault().Value; XElement data = collection.Elements().Where(x => x.Name.LocalName == "data").FirstOrDefault(); foreach (XElement record in data.Elements()) { string recordDateTime = record.Attributes().Where(x => x.Name.LocalName == "ts").FirstOrDefault().Value; string recordValue = record.Elements().Where(x => x.Name.LocalName == "CURRENT_INDEX").FirstOrDefault().Value; MeterReadingEntity sm = new MeterReadingEntity(); DMAMeterReadingEntity dmasm = new DMAMeterReadingEntity(); if (!string.IsNullOrEmpty(ProducerId) && !string.IsNullOrEmpty(recordDateTime) && !string.IsNullOrEmpty(recordValue) ) { string dmaId = currentContext.DMAs.Where(x => x.Name == "Lagar").FirstOrDefault().Identifier; // currentContext.Meters.Where(x => x.MeterIdentity == ProducerId).FirstOrDefault().Utility); // DMAMeterTableStorageContext dmamcontext = new DMAMeterTableStorageContext(container.Name + "_dmameter"); // DMAMeterEntity dmaMeterEntity = dmamcontext.MeterReadings.FirstOrDefault(x => x.PartitionKey == ProducerId); sm.PartitionKey = ProducerId; sm.CreatedOn = DateTime.Parse(recordDateTime); sm.RowKey = sm.CreatedOn.Ticks.ToString(); sm.Reading = recordValue; sm.Encrypted = false; sm.DMA = dmaId; dmasm.PartitionKey = dmaId; dmasm.CreatedOn = DateTime.Parse(recordDateTime); dmasm.RowKey = dmasm.CreatedOn.Ticks.ToString(); dmasm.Reading = recordValue; dmasm.Encrypted = false; dmasm.MeterID = ProducerId; } else { EventSourceWriter.Log.MessageMethod("ERROR: Null values parsed in water meter readings file " + blob.Name); } string strWriteConnectionString = "MKWDNConnectionString"; bool blAttempt = WriteMessageMeterDataToDataTable(sm, dmasm, strWriteConnectionString, strContainer); if (!blAttempt) { EventSourceWriter.Log.MessageMethod("ERROR:Processing Entry NOT added to storage " + receivedMessage.Id.ToString()); EventSourceWriter.Log.MessageMethod("ERROR:Processing Entry NOT added to storage " + blob.Name + " " + sm.PartitionKey); } } } } } catch (Exception e) { EventSourceWriter.Log.MessageMethod("ERROR:Processing water meter file: " + e.Message); } EventSourceWriter.Log.MessageMethod("Processing Complete Entry added to storage " + receivedMessage.Id.ToString()); urbanWaterQueue.DeleteMessage(receivedMessage); Event newEvent = new Event(); newEvent.BusDispatched = false; newEvent.EventDateTime = DateTime.Now; newEvent.EventType = (int)EventTypes.NewMeteringData; currentContext.Events.InsertOnSubmit(newEvent); currentContext.SubmitChanges(); }