public Type824Header ParseHeader(XElement element, IDictionary <string, XNamespace> namespaces)
{
XNamespace empty;
if (!namespaces.TryGetValue(string.Empty, out empty))
{
empty = XNamespace.None;
}
var model = new Type824Header
{
TransactionSetId = element.GetChildText(empty + "TransactionSetId"),
TransactionSetControlNbr = element.GetChildText(empty + "TransactionSetControlNbr"),
TransactionSetPurposeCode = element.GetChildText(empty + "TransactionSetPurposeCode"),
TransactionDate = element.GetChildText(empty + "TransactionDate"),
TransactionNbr = element.GetChildText(empty + "TransactionNbr"),
ReportTypeCode = element.GetChildText(empty + "ReportTypeCode"),
ActionCode = element.GetChildText(empty + "ActionCode"),
TdspDuns = element.GetChildText(empty + "TdspDuns"),
TdspName = element.GetChildText(empty + "TdspName"),
CrDuns = element.GetChildText(empty + "CrDuns"),
CrName = element.GetChildText(empty + "CrName"),
AppAckCode = element.GetChildText(empty + "AppAckCode"),
ReferenceNbr = element.GetChildText(empty + "ReferenceNbr"),
TransactionSetNbr = element.GetChildText(empty + "TransactionSetNbr"),
EsiId = element.GetChildText(empty + "EsiId"),
CrQualifier = element.GetChildText(empty + "CrQualifier"),
TdspQualifier = element.GetChildText(empty + "TdspQualifier"),
EspUtilityAccountNumber = element.GetChildText(empty + "ESPUtilityAccountNumber"),
EspCustomerAccountNumber = element.GetChildText(empty + "ESPCustomerAccountNumber"),
PreviousUtilityAccountNumber = element.GetChildText(empty + "PreviousUtilityAccountNumber"),
CustomerName = element.GetChildText(empty + "CustomerName"),
};
var reasonLoopElement = element.Element(empty + "ReasonLoop");
if (reasonLoopElement != null)
{
var reasonElements = reasonLoopElement.Elements(empty + "Reason");
foreach (var reasonElement in reasonElements)
{
var reasonModel = ParseReason(reasonElement, namespaces);
model.AddReason(reasonModel);
}
}
var referenceLoopElement = element.Element(empty + "ReferenceLoop");
if (referenceLoopElement != null)
{
var referenceElements = referenceLoopElement.Elements(empty + "Reference");
foreach (var referenceElement in referenceElements)
{
var referenceModel = ParseReference(referenceElement, namespaces);
model.AddReference(referenceModel);
}
}
return(model);
}
public void WriteHeader(Prism824Context context, Type824Header header)
{
var line = string.Format("SH|{0}|{1}|O|", context.TradingPartnerId, header.TransactionNbr);
context.AppendLine(line);
logger.TraceFormat("Wrote 824 \"SH\" PRISM line for Header {0}", header.HeaderKey);
}
public void ParseTransaction(Prism824Context context, string[] marketFields)
{
var model = new Type824Header
{
TransactionDate = context.TransactionDate,
TransactionNbr = context.TransactionNumber,
ActionCode = context.TransactionTypeCode,
CrDuns = context.CrDuns,
CrName = context.CrName,
TdspDuns = context.TdspDuns,
TdspName = context.TdspName,
AppAckCode = marketFields.AtIndex(2),
TransactionSetNbr = marketFields.AtIndex(4),
ReferenceNbr = marketFields.AtIndex(3),
};
var identifiedMarket = clientDataAccess.IdentifyMarket(model.TdspDuns);
if (identifiedMarket.HasValue)
{
context.SetMarket(identifiedMarket.Value);
}
model.MarketId = context.MarketId;
model.ProviderId = 1;
model.EsiId = context.PremiseNumber;
if (context.Market == MarketOptions.Texas)
{
model.EsiId = marketFields.AtIndex(9);
}
context.PushModel(model);
}
public Type824Header[] ListUnprocessed(string ldcDuns, string duns, int providerId)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("esp_824ExportList"))
{
command.AddIfNotEmptyOrDbNull("@CrDuns", duns)
.AddIfNotEmptyOrDbNull("@TdspDuns", ldcDuns);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var collection = new List <Type824Header>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new Type824Header
{
HeaderKey = reader.GetInt32("824_Key"),
Direction = reader.GetBoolean("Direction"),
};
reader.TryGetString("TransactionSetId", x => item.TransactionSetId = x);
reader.TryGetString("TransactionSetControlNbr", x => item.TransactionSetControlNbr = x);
reader.TryGetString("TransactionSetPurposeCode", x => item.TransactionSetPurposeCode = x);
reader.TryGetString("TransactionNbr", x => item.TransactionNbr = x);
reader.TryGetString("TransactionDate", x => item.TransactionDate = x);
reader.TryGetString("ReportTypeCode", x => item.ReportTypeCode = x);
reader.TryGetString("ActionCode", x => item.ActionCode = x);
reader.TryGetString("TdspDuns", x => item.TdspDuns = x);
reader.TryGetString("TdspName", x => item.TdspName = x);
reader.TryGetString("CrDuns", x => item.CrDuns = x);
reader.TryGetString("CrName", x => item.CrName = x);
reader.TryGetString("AppAckCode", x => item.AppAckCode = x);
reader.TryGetString("ReferenceNbr", x => item.ReferenceNbr = x);
reader.TryGetString("TransactionSetNbr", x => item.TransactionSetNbr = x);
reader.TryGetString("EsiId", x => item.EsiId = x);
reader.TryGetInt32("ProcessFlag", x => item.ProcessFlag = x);
reader.TryGetDateTime("ProcessDate", x => item.ProcessDate = x);
reader.TryGetInt32("TransactionTypeID", x => item.TransactionTypeId = x);
reader.TryGetInt32("MarketID", x => item.MarketId = x);
reader.TryGetInt32("ProviderID", x => item.ProviderId = x);
reader.TryGetString("CrQualifier", x => item.CrQualifier = x);
reader.TryGetString("TdspQualifier", x => item.TdspQualifier = x);
reader.TryGetString("ESPUtilityAccountNumber", x => item.EspUtilityAccountNumber = x);
reader.TryGetString("CustomerName", x => item.CustomerName = x);
reader.TryGetString("ESPCustomerAccountNumber", x => item.EspCustomerAccountNumber = x);
reader.TryGetString("PreviousUtilityAccountNumber", x => item.PreviousUtilityAccountNumber = x);
collection.Add(item);
}
return(collection.ToArray());
}
}
}
public string IdentifyEsiId(Prism824Context context, Type824Header header)
{
if (context.Market == MarketOptions.Maryland)
{
return(string.Empty);
}
return(header.EsiId);
}
public void WriteTransactionInfo(Prism824Context context, Type824Header header)
{
var esiId = IdentifyEsiId(context, header);
var line = string.Format("10|{0}|{1}|{2}|{3}|||||{4}||", context.TradingPartnerId, header.AppAckCode,
header.ReferenceNbr, header.TransactionSetNbr, esiId);
context.AppendLine(line);
logger.TraceFormat("Wrote 824 \"10\" PRISM line for Header {0}", header.HeaderKey);
}
public int InsertHeader(Type824Header model)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp824HeaderInsert"))
{
SqlParameter keyParameter;
command.AddWithValue("@MarketFileId", model.MarketFileId)
.AddWithValue("@TransactionSetId", model.TransactionSetId)
.AddWithValue("@TransactionSetControlNbr", model.TransactionSetControlNbr)
.AddWithValue("@TransactionSetPurposeCode", model.TransactionSetPurposeCode)
.AddWithValue("@TransactionNbr", model.TransactionNbr)
.AddWithValue("@TransactionDate", model.TransactionDate)
.AddWithValue("@ReportTypeCode", model.ReportTypeCode)
.AddWithValue("@ActionCode", model.ActionCode)
.AddWithValue("@TdspDuns", model.TdspDuns)
.AddWithValue("@TdspName", model.TdspName)
.AddWithValue("@CrDuns", model.CrDuns)
.AddWithValue("@CrName", model.CrName)
.AddWithValue("@AppAckCode", model.AppAckCode)
.AddWithValue("@ReferenceNbr", model.ReferenceNbr)
.AddWithValue("@TransactionSetNbr", model.TransactionSetNbr)
.AddWithValue("@EsiId", model.EsiId)
.AddWithValue("@Direction", true)
.AddWithValue("@ProviderID", model.ProviderId)
.AddWithValue("@TransactionTypeID", model.TransactionTypeId)
.AddWithValue("@MarketID", model.MarketId)
.AddWithValue("@CrQualifier", model.CrQualifier)
.AddWithValue("@TdspQualifier", model.TdspQualifier)
.AddWithValue("@ESPUtilityAccountNumber", model.EspUtilityAccountNumber)
.AddWithValue("@ESPCustomerAccountNumber", model.EspCustomerAccountNumber)
.AddWithValue("@PreviousUtilityAccountNumber", model.PreviousUtilityAccountNumber)
.AddWithValue("@CustomerName", model.CustomerName)
.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 WriteHeader(XContainer container, Type824Header header)
{
if (header == null)
{
return;
}
if (!header.HeaderKey.HasValue)
{
return;
}
var headerKey = header.HeaderKey.Value;
var element = new XElement("Header",
new XElement("HeaderKey", headerKey));
element.TryAddElement("TransactionSetId", header.TransactionSetId);
element.TryAddElement("TransactionSetControlNbr", header.TransactionSetControlNbr);
element.TryAddElement("TransactionSetPurposeCode", header.TransactionSetPurposeCode);
element.TryAddElement("TransactionDate", header.TransactionDate);
element.TryAddElement("TransactionNbr", header.TransactionNbr);
element.TryAddElement("ReportTypeCode", header.ReportTypeCode);
element.TryAddElement("ActionCode", header.ActionCode);
element.TryAddElement("TdspName", header.TdspName);
element.TryAddElement("TdspDuns", header.TdspDuns);
element.TryAddElement("CrName", header.CrName);
element.TryAddElement("CrDuns", header.CrDuns);
element.TryAddElement("AppAckCode", header.AppAckCode);
element.TryAddElement("ReferenceNbr", header.ReferenceNbr);
element.TryAddElement("TransactionSetNbr", header.TransactionSetNbr);
element.TryAddElement("EsiId", header.EsiId);
element.TryAddElement("CrQualifier", header.CrQualifier);
element.TryAddElement("TdspQualifier", header.TdspQualifier);
element.TryAddElement("ESPUtilityAccountNumber", header.EspUtilityAccountNumber);
element.TryAddElement("CustomerName", header.CustomerName);
element.TryAddElement("ESPCustomerAccountNumber", header.EspCustomerAccountNumber);
element.TryAddElement("PreviousUtilityAccountNumber", header.PreviousUtilityAccountNumber);
container.Add(element);
logger.TraceFormat("Added 824 \"Header\" XML element for Header {0}", headerKey);
var reasons = exportDataAccess.ListReasons(headerKey);
WriteReason(element, reasons);
var references = exportDataAccess.ListReferences(headerKey);
WriteReference(element, references);
}
public Type824Header[] ListUnprocessed(string ldcDuns, string duns, int providerId)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp824ExportList"))
{
command.AddIfNotEmptyOrDbNull("@TDSPDuns", ldcDuns)
.AddIfNotEmptyOrDbNull("@CrDuns", duns)
.AddWithValue("@ProviderID", providerId);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var collection = new List <Type824Header>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new Type824Header
{
HeaderKey = reader.GetInt32("824_Key"),
Direction = reader.GetBoolean("Direction"),
};
reader.TryGetString("TransactionDate", x => item.TransactionDate = x);
reader.TryGetString("TransactionNbr", x => item.TransactionNbr = x);
reader.TryGetString("ActionCode", x => item.ActionCode = x);
reader.TryGetString("TdspDuns", x => item.TdspDuns = x);
reader.TryGetString("TdspName", x => item.TdspName = x);
reader.TryGetString("CrDuns", x => item.CrDuns = x);
reader.TryGetString("CrName", x => item.CrName = x);
reader.TryGetString("AppAckCode", x => item.AppAckCode = x);
reader.TryGetString("ReferenceNbr", x => item.ReferenceNbr = x);
reader.TryGetString("TransactionSetNbr", x => item.TransactionSetNbr = x);
reader.TryGetString("EsiId", x => item.EsiId = x);
reader.TryGetString("TransactionSetPurposeCode", x => item.TransactionSetPurposeCode = x);
collection.Add(item);
}
return(collection.ToArray());
}
}
}
public void WriteReason(Prism824Context context, Type824Header header)
{
if (!header.HeaderKey.HasValue)
{
return;
}
var headerKey = header.HeaderKey.Value;
var reasons = marketDataAccess.ListReasons(headerKey);
foreach (var reason in reasons)
{
var line = string.Format("20|{0}|{1}|{2}|", context.TradingPartnerId, reason.ReasonCode,
reason.ReasonText);
context.AppendLine(line);
logger.TraceFormat("Wrote 824 \"20\" PRISM line for Header {0}", header.HeaderKey);
}
}
public void WriteHeaderData(Prism824Context context, Type824Header header)
{
var clearingHouseName = string.Empty;
var clearingHouseDuns = string.Empty;
if (context.Market == MarketOptions.Texas)
{
clearingHouseName = "ERCOT";
clearingHouseDuns = "183529049";
}
var partnerId = context.TradingPartnerId;
var stateId = partnerId.Substring(3, 2);
var line = string.Format("01|{0}|{1}|{2}|{3}|{4}|{5}|{6}|||||{7}|{8}|||||||||||{9}|{10}|", partnerId,
stateId, header.TransactionDate, header.TransactionNbr, header.ActionCode, header.TdspName,
header.TdspDuns, header.CrName, header.CrDuns, clearingHouseName, clearingHouseDuns);
context.AppendLine(line);
logger.TraceFormat("Wrote 824 \"01\" PRISM line for Header {0}", header.HeaderKey);
}
public void SaveHeader(Type824Header header)
{
logger.Trace("Start inserting header.");
var headerKey = marketDataAccess.InsertHeader(header);
logger.DebugFormat("Inserted Header \"{0}\".", headerKey);
foreach (var reason in header.Reasons)
{
reason.HeaderKey = headerKey;
var reasonKey = marketDataAccess.InsertReason(reason);
logger.DebugFormat("Inserted Reason \"{0}\" for Header \"{1}\".", reasonKey, headerKey);
}
foreach (var reference in header.References)
{
reference.HeaderKey = headerKey;
var referenceKey = marketDataAccess.InsertReference(reference);
logger.DebugFormat("Inserted Reference \"{0}\" for Header \"{1}\".", referenceKey, headerKey);
if (!reference.TechErrors.Any())
{
continue;
}
foreach (var techError in reference.TechErrors)
{
techError.ReferenceKey = referenceKey;
var techKey = marketDataAccess.InsertTechError(techError);
logger.DebugFormat("Inserted Tech Error \"{0}\" for Reference \"{1}", techKey, referenceKey);
}
}
logger.Trace("Completed inserting header.");
}
public void AddHeader(Type824Header item)
{
headers.Add(item);
}
public void SaveHeader(Type824Header header, int marketFileId)
{
header.MarketFileId = marketFileId;
SaveHeader(header);
}
