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();
}
