public int InsertHeader(Type867Header model)
{
using (var connection = new SqlConnection(_connectionString))
using (var command = connection.CreateCommand("csp867HeaderInsert"))
{
SqlParameter keyParameter;
command.AddWithValue("@MarketFileId", model.MarketFileId)
.AddWithValue("@TransactionSetId", model.TransactionSetId)
.AddWithValue("@TransactionSetControlNbr", model.TransactionSetControlNbr)
.AddIfNotEmptyOrDbNull("@TransactionSetPurposeCode", model.TransactionSetPurposeCode)
.AddIfNotEmptyOrDbNull("@TransactionNbr", model.TransactionNbr)
.AddIfNotEmptyOrDbNull("@TransactionDate", model.TransactionDate)
.AddIfNotEmptyOrDbNull("@ReportTypeCode", model.ReportTypeCode)
.AddIfNotEmptyOrDbNull("@ActionCode", model.ActionCode)
.AddIfNotEmptyOrDbNull("@ReferenceNbr", model.ReferenceNbr)
.AddIfNotEmptyOrDbNull("@DocumentDueDate", model.DocumentDueDate)
.AddIfNotEmptyOrDbNull("@EsiId", model.EsiId)
.AddIfNotEmptyOrDbNull("@PowerRegion", model.PowerRegion)
.AddIfNotEmptyOrDbNull("@OriginalTransactionNbr", model.OriginalTransactionNbr)
.AddIfNotEmptyOrDbNull("@TdspDuns", model.TdspDuns)
.AddIfNotEmptyOrDbNull("@TdspName", model.TdspName)
.AddWithValue("@CrDuns", model.CrDuns)
.AddWithValue("@CrName", model.CrName)
.AddWithValue("@Direction", model.DirectionFlag)
.AddWithValue("@UtilityAccountNumber", model.UtilityAccountNumber)
.AddWithValue("@PreviousUtilityAccountNumber", model.PreviousUtilityAccountNumber)
.AddWithValue("@TransactionTypeID", model.TransactionTypeID)
.AddWithValue("@MarketID", model.MarketID)
.AddWithValue("@ProviderID", model.ProviderID)
.AddWithValue("@EstimationReason", model.EstimationReason)
.AddWithValue("@EstimationDescription", model.EstimationDescription)
.AddWithValue("@DoorHangerFlag", model.DoorHangerFlag)
.AddWithValue("@ESNCount", model.EsnCount)
.AddWithValue("@QOCount", model.QoCount)
.AddWithValue("@NextMeterReadDate", model.NextMeterReadDate)
.AddWithValue("@InvoiceNbr", model.InvoiceNbr)
.AddWithValue("@UtilityContractID", model.UtilityContractID)
.AddWithValue("@ContainsConsumption", 0)//CISRFC-783
.AddOutParameter("@Key", SqlDbType.Int, out keyParameter);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.ExecuteNonQuery();
if (keyParameter.Value == null)
{
throw new Exception();
}
var headerKey = (int)keyParameter.Value;
model.HeaderKey = headerKey;
return(headerKey);
}
}
public void AddHeader(Type867Header item)
{
_headers.Add(item);
}
private void ParseHeader(Prism867Context context, string[] marketFields)
{
context.TransactionNumber = marketFields.AtIndex(6);
context.ReportTypeCode = marketFields.AtIndex(4);
// since we are going to end up with the value at 16
// if the value in 35 is null or empty...
context.EsiId = marketFields.AtIndex(16);
// let's try to the value at 35
// if there it will overwrite what we already have
marketFields.TryAtIndex(35, x => context.EsiId = x);
var model = new Type867Header
{
DirectionFlag = true,
TransactionSetId = context.Alias,
TransactionNbr = context.TransactionNumber,
TransactionDate = marketFields.AtIndex(12),
ReportTypeCode = context.ReportTypeCode,
EsiId = context.EsiId,
ActionCode = marketFields.AtIndex(5),
PowerRegion = marketFields.AtIndex(38),
OriginalTransactionNbr = marketFields.AtIndex(7),
ReferenceNbr = marketFields.AtIndex(39),
TdspDuns = marketFields.AtIndex(8),
TdspName = marketFields.AtIndex(9),
CrDuns = marketFields.AtIndex(10),
CrName = marketFields.AtIndex(11),
UtilityAccountNumber = marketFields.AtIndex(16),
EstimationReason = marketFields.AtIndex(48),
EstimationDescription = marketFields.AtIndex(49),
DoorHangerFlag = marketFields.AtIndex(50),
EsnCount = marketFields.AtIndex(51),
QoCount = marketFields.AtIndex(52)
};
marketFields.TryAtIndex(3, x => model.TransactionSetPurposeCode = x);
if (!string.IsNullOrEmpty(model.TransactionDate) && model.TransactionDate.Length > 8)
{
model.TransactionDate = model.TransactionDate.Substring(0, 8);
}
var identifiedMarket = clientDataAccess.IdentifyMarket(model.TdspDuns);
if (identifiedMarket.HasValue)
{
context.SetMarket(identifiedMarket.Value);
}
else
{
context.MarkAsInvalid(string.Format("Failed to load LDC Record for DUNS \"{0}\".", model.TdspDuns ?? "(null)"));
return;
}
model.MarketID = context.MarketId;
model.MarketFileId = context.MarketFileId;
context.PushModel(model);
}
public void SaveHeader(Type867Header header)
{
_logger.Trace("Start inserting header.");
var headerKey = _marketDataAccess.InsertHeader(header);
_logger.DebugFormat("Inserted Header \"{0}\".", headerKey);
foreach (var name in header.Names)
{
name.HeaderKey = headerKey;
_marketDataAccess.InsertName(name);
_logger.DebugFormat("Inserted Name \"{0}\" for Header \"{1}\".", name.EntityName, headerKey);
}
foreach (var intervalDetail in header.IntervalDetails)
{
intervalDetail.HeaderKey = headerKey;
var intervalDetailKey = _marketDataAccess.InsertIntervalDetail(intervalDetail);
_logger.DebugFormat("Inserted Interval Detail \"{0}\" for Header \"{1}\".", intervalDetailKey, headerKey);
foreach (var qty in intervalDetail.IntervalDetailQtys)
{
qty.IntervalDetailKey = intervalDetailKey;
_marketDataAccess.InsertIntervalDetailQty(qty);
_logger.DebugFormat("Inserted Interval Detail Qty \"{0}\" for Interval Detail \"{1}\" for Header\"{2}\".", qty.Qualifier, intervalDetailKey, headerKey);
}
}
foreach (var intervalSummary in header.IntervalSummaries)
{
intervalSummary.HeaderKey = headerKey;
var intervalSummaryKey = _marketDataAccess.InsertIntervalSummary(intervalSummary);
_logger.DebugFormat("Inserted Interval Summary \"{0}\" for Header \"{1}\".", intervalSummaryKey, headerKey);
foreach (var qty in intervalSummary.IntervalSummaryQtys)
{
qty.IntervalSummaryKey = intervalSummaryKey;
_marketDataAccess.InsertIntervalSummaryQty(qty);
_logger.DebugFormat("Inserted Interval Summary Qty \"{0}\" for Interval Summary \"{1}\" for Header\"{2}\".", qty.Qualifier, intervalSummaryKey, headerKey);
}
}
foreach (var acctBillQty in header.AccountBillQtys)
{
acctBillQty.HeaderKey = headerKey;
var acctBillQtyKey = _marketDataAccess.InsertAccountBillQuanity(acctBillQty);
_logger.DebugFormat("Inserted Account Bill Quantity \"{0}\" for Header \"{1}\".", acctBillQtyKey, headerKey);
}
foreach (var gasProfileFactorEval in header.GasProfileFactorEvaluations)
{
gasProfileFactorEval.HeaderKey = headerKey;
_marketDataAccess.InsertGasProfileFactorEvaluation(gasProfileFactorEval);
_logger.DebugFormat("Inserted Gas Profile Factor Evaluation with [CustomerServiceInitDate] \"{0}\" for Header \"{1}\".", gasProfileFactorEval.CustomerServiceInitDate, headerKey);
}
foreach (var gasProfileFactorSample in header.GasProfileFactorSamples)
{
gasProfileFactorSample.HeaderKey = headerKey;
_marketDataAccess.InsertGasProfileFactorSample(gasProfileFactorSample);
_logger.DebugFormat("Inserted Gas Profile Factor Sample with [AnnualPeriod] \"{0}\" for Header \"{1}\".", gasProfileFactorSample.AnnualPeriod, headerKey);
}
foreach (var intervalSummaryAcrossMeter in header.IntervalSummaryAcrossMeters)
{
intervalSummaryAcrossMeter.HeaderKey = headerKey;
var intervalSummaryAcrossMeterKey = _marketDataAccess.InsertIntervalSummaryAcrossMeters(intervalSummaryAcrossMeter);
_logger.DebugFormat("Inserted Interval Summary Across Meter \"{0}\" for Header \"{1}\".", intervalSummaryAcrossMeterKey, headerKey);
foreach (var qty in intervalSummaryAcrossMeter.IntervalSummaryAcrossMetersQtys)
{
qty.IntervalSummaryAcrossMetersKey = intervalSummaryAcrossMeterKey;
_marketDataAccess.InsertIntervalSummaryAcrossMetersQty(qty);
_logger.DebugFormat("Inserted Interval Summary Across Meter Qty with [Qualifier] \"{0}\" for Interval Summary Across Meter \"{1}\" for Header\"{2}\".", qty.Qualifier, intervalSummaryAcrossMeterKey, headerKey);
}
}
foreach (var netIntervalSummary in header.NetIntervalSummaries)
{
netIntervalSummary.HeaderKey = headerKey;
var netIntervalSummaryKey = _marketDataAccess.InsertNetIntervalSummary(netIntervalSummary);
_logger.DebugFormat("Inserted Net Interval Summary \"{0}\" for Header \"{1}\".", netIntervalSummaryKey, headerKey);
foreach (var qty in netIntervalSummary.NetIntervalSummaryQtys)
{
qty.NetIntervalSummaryKey = netIntervalSummaryKey;
_marketDataAccess.InsertNetIntervalSummaryQty(qty);
_logger.DebugFormat("Inserted Net Interval Summary Qty with [Qualifier] \"{0}\" for Net Interval Summary \"{1}\" for Header\"{2}\".", qty.Qualifier, netIntervalSummaryKey, headerKey);
}
}
foreach (var nonIntervalSummary in header.NonIntervalSummaries)
{
nonIntervalSummary.HeaderKey = headerKey;
var nonIntervalSummaryKey = _marketDataAccess.InsertNonIntervalSummary(nonIntervalSummary);
_logger.DebugFormat("Inserted Non Interval Summary \"{0}\" for Header \"{1}\".", nonIntervalSummaryKey, headerKey);
foreach (var qty in nonIntervalSummary.NonIntervalSummaryQtys)
{
qty.NonIntervalSummaryKey = nonIntervalSummaryKey;
_marketDataAccess.InsertNonIntervalSummaryQty(qty);
_logger.DebugFormat("Inserted Non Interval Summary Qty with [Qualifier] \"{0}\" for Non Interval Summary \"{1}\" for Header\"{2}\".", qty.Qualifier, nonIntervalSummaryKey, headerKey);
}
}
foreach (var nonIntervalDetail in header.NonIntervalDetails)
{
nonIntervalDetail.HeaderKey = headerKey;
var nonIntervalDetailKey = _marketDataAccess.InsertNonIntervalDetail(nonIntervalDetail);
_logger.DebugFormat("Inserted Non Interval Detail \"{0}\" for Header \"{1}\".", nonIntervalDetailKey, headerKey);
foreach (var qty in nonIntervalDetail.NonIntervalDetailQtys)
{
qty.NonIntervalDetailKey = nonIntervalDetailKey;
_marketDataAccess.InsertNonIntervalDetailQty(qty);
_logger.DebugFormat("Inserted Non Interval Detail Qty with [Qualifier] \"{0}\" for Non Interval Detail \"{1}\" for Header\"{2}\".", qty.Qualifier, nonIntervalDetailKey, headerKey);
}
}
foreach (var scheduledDeterminant in header.ScheduleDeterminants)
{
scheduledDeterminant.HeaderKey = headerKey;
var scheduledDeterminantKey = _marketDataAccess.InsertScheduleDeterminants(scheduledDeterminant);
_logger.DebugFormat("Inserted Scheduled Determinant \"{0}\" for Header \"{1}\".", scheduledDeterminantKey, headerKey);
}
foreach (var headerSwitch in header.Switches)
{
headerSwitch.HeaderKey = headerKey;
var headerSwitchKey = _marketDataAccess.InsertSwitch(headerSwitch);
_logger.DebugFormat("Inserted Switch \"{0}\" for Header \"{1}\".", headerSwitchKey, headerKey);
foreach (var qty in headerSwitch.SwitchQtys)
{
qty.SwitchKey = headerSwitchKey;
_marketDataAccess.InsertSwitchQty(qty);
_logger.DebugFormat("Inserted Switch Qty with [Qualifier] \"{0}\" for Switch \"{1}\" for Header\"{2}\".", qty.Qualifier, headerSwitchKey, headerKey);
}
}
foreach (var unMeterDetail in header.UnMeterDetails)
{
unMeterDetail.HeaderKey = headerKey;
var unMeterDetailKey = _marketDataAccess.InsertUnMeterDetail(unMeterDetail);
_logger.DebugFormat("Inserted UnMeter Detail \"{0}\" for Header \"{1}\".", unMeterDetailKey, headerKey);
foreach (var qty in unMeterDetail.UnMeterDetailQtys)
{
qty.UnMeterDetailKey = unMeterDetailKey;
_marketDataAccess.InsertUnMeterDetailQty(qty);
_logger.DebugFormat("Inserted UnMeter Detail Qty with [Qualifier] \"{0}\" for UnMeter Detail \"{1}\" for Header\"{2}\".", qty.Qualifier, unMeterDetailKey, headerKey);
}
}
foreach (var unMeterSummary in header.UnMeterSummaries)
{
unMeterSummary.HeaderKey = headerKey;
var unMeterSummaryKey = _marketDataAccess.InsertUnMeterSummary(unMeterSummary);
_logger.DebugFormat("Inserted UnMeter Summary \"{0}\" for Header \"{1}\".", unMeterSummaryKey, headerKey);
foreach (var qty in unMeterSummary.UnMeterSummaryQtys)
{
qty.UnMeterSummaryKey = unMeterSummaryKey;
_marketDataAccess.InsertUnMeterSummaryQty(qty);
_logger.DebugFormat("Inserted UnMeter Summary Qty with [Qualifier] \"{0}\" for UnMeter Summary \"{1}\" for Header\"{2}\".", qty.Qualifier, unMeterSummaryKey, headerKey);
}
}
_logger.Trace("Completed inserting header.");
}
public void SaveHeader(Type867Header header, int marketFileId)
{
header.MarketFileId = marketFileId;
SaveHeader(header);
}
public Type867Header ParseHeader(XElement element, IDictionary <string, XNamespace> namespaces)
{
XNamespace empty;
if (!namespaces.TryGetValue(string.Empty, out empty))
{
empty = XNamespace.None;
}
var model = new Type867Header
{
DirectionFlag = true,
TransactionSetId = element.GetChildText(empty + "TransactionSetId"),
TransactionSetControlNbr = element.GetChildText(empty + "TransactionSetControlNbr"),
TransactionSetPurposeCode = element.GetChildText(empty + "TransactionSetPurposeCode"),
TransactionNbr = element.GetChildText(empty + "TransactionNbr"),
TransactionDate = element.GetChildText(empty + "TransactionDate"),
ReportTypeCode = element.GetChildText(empty + "ReportTypeCode"),
ActionCode = element.GetChildText(empty + "ActionCode"),
ReferenceNbr = element.GetChildText(empty + "ReferenceNbr"),
DocumentDueDate = element.GetChildText(empty + "DocumentDueDate"),
EsiId = element.GetChildText(empty + "EsiId"),
PowerRegion = element.GetChildText(empty + "PowerRegion"),
OriginalTransactionNbr = element.GetChildText(empty + "OriginalTransactionNbr"),
TdspDuns = element.GetChildText(empty + "TdspDuns"),
TdspName = element.GetChildText(empty + "TdspName"),
CrDuns = element.GetChildText(empty + "CrDuns"),
CrName = element.GetChildText(empty + "CrName"),
UtilityAccountNumber = element.GetChildText(empty + "UtilityAccountNumber"),
PreviousUtilityAccountNumber = element.GetChildText(empty + "PreviousUtilityAccountNumber"),
EstimationReason = element.GetChildText(empty + "EstimationReason"),
EstimationDescription = element.GetChildText(empty + "EstimationDescription"),
DoorHangerFlag = element.GetChildText(empty + "DoorHangerFlag"),
EsnCount = element.GetChildText(empty + "ESNCount"),
QoCount = element.GetChildText(empty + "QOCount"),
NextMeterReadDate = element.GetChildText(empty + "NextMeterReadDate"),
InvoiceNbr = element.GetChildText(empty + "InvoiceNbr"),
UtilityContractID = element.GetChildText(empty + "UtilityContractID"),
};
var nameLoopElement = element.Element(empty + "NameLoop");
if (nameLoopElement != null)
{
var nameElements = nameLoopElement.Elements(empty + "Name");
foreach (var nameElement in nameElements)
{
var nameModel = ParseName(nameElement, namespaces);
model.AddName(nameModel);
}
}
var nonIntervalDetailLoopElement = element.Element(empty + "NonIntervalDetailLoop");
if (nonIntervalDetailLoopElement != null)
{
var nonIntervalDetailElements = nonIntervalDetailLoopElement.Elements(empty + "NonIntervalDetail");
foreach (var nonIntervalDetailElement in nonIntervalDetailElements)
{
var nonIntervalDetailModel = ParseNonIntervalDetail(nonIntervalDetailElement, namespaces);
if (String.IsNullOrEmpty(nonIntervalDetailModel.MeterNumber))//Logic that was in CSLA NonIntervalDetail.Insert method
{
nonIntervalDetailModel.MeterNumber = model.UtilityAccountNumber;
}
model.AddNonIntervalDetail(nonIntervalDetailModel);
}
}
var nonIntervalSummaryLoopElement = element.Element(empty + "NonIntervalSummaryLoop");
if (nonIntervalSummaryLoopElement != null)
{
var nonIntervalSummaryElements = nonIntervalSummaryLoopElement.Elements(empty + "NonIntervalSummary");
foreach (var nonIntervalSummaryElement in nonIntervalSummaryElements)
{
var nonIntervalSummaryModel = ParseNonIntervalSummary(nonIntervalSummaryElement, namespaces);
model.AddNonIntervalSummary(nonIntervalSummaryModel);
}
}
var intervalDetailLoopElement = element.Element(empty + "IntervalDetailLoop");
if (intervalDetailLoopElement != null)
{
var intervalDetailElements = intervalDetailLoopElement.Elements(empty + "IntervalDetail");
foreach (var intervalDetailElement in intervalDetailElements)
{
var intervalDetailModel = ParseIntervalDetail(intervalDetailElement, namespaces);
model.AddIntervalDetail(intervalDetailModel);
}
}
var intervalSummaryLoopElement = element.Element(empty + "IntervalSummaryLoop");
if (intervalSummaryLoopElement != null)
{
var intervalSummaryElements = intervalSummaryLoopElement.Elements(empty + "IntervalSummary");
foreach (var intervalSummaryElement in intervalSummaryElements)
{
var intervalSummaryModel = ParseIntervalSummary(intervalSummaryElement, namespaces);
model.AddIntervalSummary(intervalSummaryModel);
}
}
var unMeterDetailLoopElement = element.Element(empty + "UnmeterDetailLoop");
if (unMeterDetailLoopElement != null)
{
var unMeterDetailElements = unMeterDetailLoopElement.Elements(empty + "UnmeterDetail");
foreach (var unMeterDetailElement in unMeterDetailElements)
{
var unMeterDetailModel = ParseUnMeterDetail(unMeterDetailElement, namespaces);
model.AddUnMeterDetail(unMeterDetailModel);
}
}
var unMeterSummaryLoopElement = element.Element(empty + "UnmeterSummaryLoop");
if (unMeterSummaryLoopElement != null)
{
var unMeterSummaryElements = unMeterSummaryLoopElement.Elements(empty + "UnmeterSummary");
foreach (var unMeterSummaryElement in unMeterSummaryElements)
{
var unMeterSummaryModel = ParseUnMeterSummary(unMeterSummaryElement, namespaces);
model.AddUnMeterSummary(unMeterSummaryModel);
}
}
var scheduledDeterminantsLoopElement = element.Element(empty + "ScheduleDeterminantsLoop");
if (scheduledDeterminantsLoopElement != null)
{
var scheduledDeterminantsElements = scheduledDeterminantsLoopElement.Elements(empty + "ScheduleDeterminants");
foreach (var scheduledDeterminantsElement in scheduledDeterminantsElements)
{
var scheduledDeterminantModel = ParseScheduledDeterminant(scheduledDeterminantsElement, namespaces);
model.AddScheduledDeterminant(scheduledDeterminantModel);
}
}
var intervalSummaryAcrossMetersLoopElement = element.Element(empty + "IntervalSummaryAcrossMetersLoop");
if (intervalSummaryAcrossMetersLoopElement != null)
{
var intervalSummaryAcrossMetersElements = intervalSummaryAcrossMetersLoopElement.Elements(empty + "IntervalSummaryAcrossMeters");
foreach (var intervalSummaryAcrossMetersElement in intervalSummaryAcrossMetersElements)
{
var intervalSummaryAcrossMetersModel = ParseIntervalSummaryAcrossMeters(intervalSummaryAcrossMetersElement, namespaces);
model.AddIntervalSummaryAcrossMeters(intervalSummaryAcrossMetersModel);
}
}
var gasProfileFactorEvaluationLoopElement = element.Element(empty + "GasProfileFactorEvaluationLoop");
if (gasProfileFactorEvaluationLoopElement != null)
{
var gasProfileFactorEvaluationElements = gasProfileFactorEvaluationLoopElement.Elements(empty + "GasProfileFactorEvaluation");
foreach (var gasProfileFactorEvaluationElement in gasProfileFactorEvaluationElements)
{
var gasProfileFactorEvaluationModel = ParseGasProfileFactorEvaluation(gasProfileFactorEvaluationElement, namespaces);
model.AddGasProfileFactorEvaluation(gasProfileFactorEvaluationModel);
}
}
var gasProfileFactorSampleLoopElement = element.Element(empty + "GasProfileFactorSampleLoop");
if (gasProfileFactorSampleLoopElement != null)
{
var gasProfileFactorSampleElements = gasProfileFactorSampleLoopElement.Elements(empty + "GasProfileFactorSample");
foreach (var gasProfileFactorSampleElement in gasProfileFactorSampleElements)
{
var gasProfileFactorSampleModel = ParseGasProfileFactorSample(gasProfileFactorSampleElement, namespaces);
model.AddGasProfileFactorSample(gasProfileFactorSampleModel);
}
}
return(model);
}
