public void WriteNiscTax(Prism810Context context, Type810Header header)
{
if (!context.IsCustomerInvoice)
{
return;
}
if (!header.HeaderKey.HasValue)
{
return;
}
var headerKey = header.HeaderKey.Value;
var taxes = marketDataAccess.ListDetailItemTaxesByHeader(headerKey);
if (taxes == null || taxes.Length == 0)
{
return;
}
foreach (var tax in taxes)
{
var amount = tax.TaxAmount;
if (amount.Contains("."))
{
amount = amount.Substring(0, amount.IndexOf('.') + 3);
}
var line = string.Format("45|{0}|{1}|{2}|||||", context.TradingPartnerId, tax.TaxTypeCode, amount);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"45\" PRISM line for Header {0}", header.HeaderKey);
}
}
public void WriteNiscAccount(Prism810Context context, Type810Header header)
{
if (!context.IsCustomerInvoice)
{
return;
}
var partnerId = context.TradingPartnerId;
var stateId = partnerId.Substring(3, 2);
var arSummary = clientDataAccess.LoadArSummaryByInvoice(header.InvoiceNbr);
var previousBalance = arSummary.PrevBal;
var currentBalance = arSummary.BalDue;
var billingBalance = (arSummary.PrevBal - arSummary.CurrPmts + arSummary.CurrAdjs);
var line =
string.Format(
"01|{0}|{1}|{2}|{3}|ME||||||||{4}|{5}|||||NISC|{6}|{7}|{8}|||||{9}|{10}|{11}|{12}|||||||||||||||||||||||||||00||||",
context.TradingPartnerId, stateId, header.TransactionDate, header.InvoiceNbr,
header.CrAccountNumber, header.EsiId, header.CustomerDUNS, context.BillFromName,
context.BillFromDuns, header.PaymentDueDate, previousBalance, billingBalance, currentBalance);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"01\" PRISM line for Header {0}", header.HeaderKey);
}
public void ParseHeader(Prism810Context context, string[] marketFields)
{
var model = new Type810Header
{
TransactionSetId = "810",
TransactionSetPurposeCode = marketFields.AtIndex(7),
InvoiceNbr = marketFields.AtIndex(4),
TransactionDate = marketFields.AtIndex(3),
ReleaseNbr = marketFields.AtIndex(5),
TransactionTypeCode = marketFields.AtIndex(6),
OriginalInvoiceNbr = marketFields.AtIndex(12),
EsiId = marketFields.AtIndex(53),
PaymentDueDate = marketFields.AtIndex(25),
TdspDuns = marketFields.AtIndex(20),
TdspName = marketFields.AtIndex(19),
CrDuns = marketFields.AtIndex(22),
CrName = marketFields.AtIndex(21),
Direction = true,
};
var identifiedMarket = clientDataAccess.IdentifyMarket(model.TdspDuns);
if (identifiedMarket.HasValue)
{
context.SetMarket(identifiedMarket.Value);
}
model.MarketId = context.MarketId;
model.ProviderId = 1;
context.PushModel(model);
}
public void WriteRemitanceAddress(Prism810Context context, Type810Header header)
{
if (!context.IsCustomerInvoice)
{
return;
}
var customer = clientDataAccess.LoadCustomerDetailByEsiId(header.EsiId);
if (customer == null)
{
logger.ErrorFormat("Could not load Customer from EsiId {0}.", header.EsiId);
return;
}
var address1 = customer.RemitAddress1.ToAscii();
var address2 = customer.RemitAddress2.ToAscii();
var city = customer.RemitCity.ToAscii();
var state = customer.RemitState;
var postalCode = customer.RemitZip;
var line = string.Format("05|{0}|RE|||||{1}|{2}|{3}|{4}|{5}||||||||", context.TradingPartnerId,
address1, address2, city, state, postalCode);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"05\" PRISM line for Header {0}", header.HeaderKey);
}
public string IdentifyLdcAccountNumber(Prism810Context context, Type810Header header)
{
if (context.Market == MarketOptions.Maryland)
{
return(header.EsiId);
}
return(string.Empty);
}
public int InsertHeader(Type810Header model)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp810HeaderInsert"))
{
SqlParameter keyParameter;
command.AddWithValue("@MarketFileId", model.MarketFileId)
.AddWithValue("@TransactionSetId", model.TransactionSetId)
.AddWithValue("@TransactionSetControlNbr", model.TransactionSetControlNbr)
.AddIfNotEmptyOrDbNull("@TransactionSetPurposeCode", model.TransactionSetPurposeCode)
.AddIfNotEmptyOrDbNull("@InvoiceNbr", model.InvoiceNbr)
.AddIfNotEmptyOrDbNull("@TransactionDate", model.TransactionDate)
.AddIfNotEmptyOrDbNull("@ReleaseNbr", model.ReleaseNbr)
.AddIfNotEmptyOrDbNull("@TransactionTypeCode", model.TransactionTypeCode)
.AddIfNotEmptyOrDbNull("@OriginalInvoiceNbr", model.OriginalInvoiceNbr)
.AddIfNotEmptyOrDbNull("@EsiId", model.EsiId)
.AddWithValue("@CRAccountNumber", model.CrAccountNumber)
.AddIfNotEmptyOrDbNull("@PaymentDueDate", model.PaymentDueDate)
.AddIfNotEmptyOrDbNull("@TdspDuns", model.TdspDuns)
.AddIfNotEmptyOrDbNull("@TdspName", model.TdspName)
.AddWithValue("@CrDuns", model.CrDuns)
.AddWithValue("@CrName", model.CrName)
.AddIfNotEmptyOrDbNull("@TotalAmount", model.TotalAmount)
.AddWithValue("@Direction", true)
.AddWithValue("@CustNoForESCO", model.CustNoForESCO)
.AddWithValue("@PreviousUtilityAccountNumber", model.PreviousUtilityAccountNumber)
.AddWithValue("@BillCalculator", model.BillCalculator)
.AddWithValue("@BillPresenter", model.BillPresenter)
.AddWithValue("@GasPoolId", model.GasPoolId)
.AddWithValue("@TransactionTypeID", model.TransasctionTypeId)
.AddWithValue("@ProviderID", model.ProviderId)
.AddWithValue("@MarketID", model.MarketId)
.AddWithValue("@CustomerDUNS", model.CustomerDUNS)
.AddWithValue("@ServiceDeliveryPoint", model.ServiceDeliveryPoint)
.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 WriteNiscHeader(Prism810Context context, Type810Header header)
{
if (!context.IsCustomerInvoice)
{
return;
}
var line = string.Format("SH|{0}|{1}|O|", context.TradingPartnerId, header.InvoiceNbr);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"SH\" PRISM line for Header {0}", header.HeaderKey);
}
public void WriteSummary(Prism810Context context, Type810Header header)
{
var amount = header.TotalAmount;
if (amount.Contains("."))
{
amount = amount.Substring(0, amount.IndexOf('.') + 3);
}
var line = string.Format("60|{0}|{1}|1|", context.TradingPartnerId, amount);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"60\" PRISM line for Header {0}", header.HeaderKey);
}
public void WriteDetail(Prism810Context context, Type810Header header)
{
if (!header.HeaderKey.HasValue)
{
return;
}
var headerKey = header.HeaderKey.Value;
var details = marketDataAccess.ListDetails(headerKey);
if (details == null || details.Length == 0)
{
logger.ErrorFormat("No 810 Detail records found for 810 Key {0}.", headerKey);
return;
}
var detail = details.First();
string line;
if (context.IsCustomerInvoice)
{
var premiseInfo = clientDataAccess.LoadPremiseByEsiId(header.EsiId);
if (premiseInfo == null)
{
logger.ErrorFormat("Could not load Premise information for EsiId {0}", header.EsiId);
return;
}
line = string.Format("10|{0}|BTC002|ELECTRIC|{1}||||{2}|{3}|{4}|{5}||{6}||{7}|{8}|{9}|{10}|{11}|||||",
context.TradingPartnerId, detail.ServiceClass, detail.ServicePeriodStartDate,
detail.ServicePeriodEndDate, premiseInfo.MeterNo, detail.RateClass, premiseInfo.CustName.ToAscii(),
premiseInfo.Addr1.ToAscii(), premiseInfo.Addr2.ToAscii(), premiseInfo.City.ToAscii(),
premiseInfo.State, premiseInfo.Zip);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"10\" PRISM line for Header {0}", header.HeaderKey);
return;
}
line = string.Format("10|{0}|1|ELECTRIC|{1}||{2}|{3}|||||||||", context.TradingPartnerId,
detail.ServiceClass, detail.ServicePeriodStartDate, detail.ServicePeriodEndDate);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"10\" PRISM line for Header {0}", header.HeaderKey);
}
public int InsertHeader(Type810Header model)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp810HeaderInsert"))
{
SqlParameter keyParameter;
// this differs from other imports as the import
// specifies the parameters that should be used
// the stored procedure takes many more parameters but...
command.AddWithValue("@MarketFileId", model.MarketFileId)
.AddIfNotEmptyOrDbNull("@TransactionSetPurposeCode", model.TransactionSetPurposeCode)
.AddIfNotEmptyOrDbNull("@InvoiceNbr", model.InvoiceNbr)
.AddIfNotEmptyOrDbNull("@TransactionDate", model.TransactionDate)
.AddIfNotEmptyOrDbNull("@ReleaseNbr", model.ReleaseNbr)
.AddIfNotEmptyOrDbNull("@TransactionTypeCode", model.TransactionTypeCode)
.AddIfNotEmptyOrDbNull("@OriginalInvoiceNbr", model.OriginalInvoiceNbr)
.AddIfNotEmptyOrDbNull("@EsiId", model.EsiId)
.AddIfNotEmptyOrDbNull("@PaymentDueDate", model.PaymentDueDate)
.AddIfNotEmptyOrDbNull("@TdspDuns", model.TdspDuns)
.AddIfNotEmptyOrDbNull("@TdspName", model.TdspName)
.AddWithValue("@CrDuns", model.CrDuns)
.AddWithValue("@CrName", model.CrName)
.AddIfNotEmptyOrDbNull("@TotalAmount", model.TotalAmount)
.AddWithValue("@Direction", true)
.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 WriteNiscAccountTaxCharges(Prism810Context context, Type810Header header)
{
if (!context.IsCustomerInvoice)
{
return;
}
if (!header.HeaderKey.HasValue)
{
return;
}
var headerKey = header.HeaderKey.Value;
var charges = marketDataAccess.ListDetailItemChargesByHeader(headerKey);
if (charges == null || charges.Length == 0)
{
return;
}
foreach (var charge in charges)
{
if (!charge.ChargeCode.Equals("D140", StringComparison.Ordinal))
{
continue;
}
var amount = charge.Amount;
if (amount.Contains("."))
{
amount = amount.Substring(0, amount.IndexOf('.') + 3);
}
var line = string.Format("20|{0}|LS|{1}||||", context.TradingPartnerId, amount);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"20\" PRISM line for Header {0}", header.HeaderKey);
}
}
public void WriteNiscMeterReads(Prism810Context context, Type810Header header)
{
if (!context.IsCustomerInvoice)
{
return;
}
var meterCons = clientDataAccess.ListMeterConsumptionByInvoice(header.InvoiceNbr);
if (meterCons == null || meterCons.Length == 0)
{
return;
}
foreach (var meterCon in meterCons)
{
var line = string.Format("30|{0}|AA|{1}|KH|51|{2}|{3}||||", context.TradingPartnerId,
meterCon.MeterFactor, meterCon.BegRead, meterCon.EndRead);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"30\" PRISM line for Header {0}", header.HeaderKey);
}
}
public void SaveHeader(Type810Header header)
{
logger.Trace("Start inserting header.");
var headerKey = marketDataAccess.InsertHeader(header);
logger.DebugFormat("Inserted Header \"{0}\".", headerKey);
foreach (var balance in header.Balances)
{
balance.HeaderKey = headerKey;
var balanceKey = marketDataAccess.InsertBalance(balance);
logger.DebugFormat("Inserted Balance \"{0}\" for Header \"{1}\".", balanceKey, headerKey);
}
foreach (var payment in header.Payments)
{
payment.HeaderKey = headerKey;
var paymentKey = marketDataAccess.InsertPayment(payment);
logger.DebugFormat("Inserted Payment \"{0}\" for Header \"{1}\".", paymentKey, headerKey);
}
foreach (var detail in header.Details)
{
detail.HeaderKey = headerKey;
var detailKey = marketDataAccess.InsertDetail(detail);
logger.DebugFormat("Inserted Detail \"{0}\" for Header \"{1}\".", detailKey, headerKey);
foreach (var detailItem in detail.Items)
{
detailItem.DetailKey = detailKey;
var detailItemKey = marketDataAccess.InsertDetailItem(detailItem);
logger.DebugFormat("Inserted DetailItem \"{0}\" for Detail \"{1}\".", detailItemKey, detailKey);
foreach (var detailItemCharge in detailItem.Charges)
{
detailItemCharge.ItemKey = detailItemKey;
var detailItemChargeKey = marketDataAccess.InsertDetailItemCharge(detailItemCharge);
logger.DebugFormat("Inserted DetailItemCharge \"{0}\" for DetailItem \"{1}\".", detailItemChargeKey, detailItemKey);
}
foreach (var detailItemTax in detailItem.Taxes)
{
detailItemTax.ItemKey = detailItemKey;
var detailItemTaxKey = marketDataAccess.InsertDetailItemTax(detailItemTax);
logger.DebugFormat("Inserted DetailItemTax \"{0}\" for DetailItem \"{1}\".", detailItemTaxKey, detailItemKey);
}
}
foreach (var detailTax in detail.Taxes)
{
detailTax.DetailKey = detailKey;
var detailTaxKey = marketDataAccess.InsertDetailTax(detailTax);
logger.DebugFormat("Inserted DetailTax \"{0}\" for Detail \"{1}\".", detailTaxKey, detailKey);
}
}
foreach (var name in header.Names)
{
name.HeaderKey = headerKey;
var nameKey = marketDataAccess.InsertName(name);
logger.DebugFormat("Inserted Name \"{0}\" for Header \"{1}\".", nameKey, headerKey);
}
foreach (var summary in header.Summaries)
{
summary.HeaderKey = headerKey;
var summaryKey = marketDataAccess.InsertSummary(summary);
logger.DebugFormat("Inserted Summary \"{0}\" for Header \"{1}\".", summaryKey, headerKey);
}
foreach (var message in header.Messages)
{
message.HeaderKey = headerKey;
var messageKey = marketDataAccess.InsertMessage(message);
logger.DebugFormat("Inserted Message \"{0}\" for Header \"{1}\".", messageKey, headerKey);
}
logger.Trace("Completed inserting header.");
}
public Type810Header ParseHeader(XElement element, IDictionary <string, XNamespace> namespaces)
{
XNamespace empty;
if (!namespaces.TryGetValue(string.Empty, out empty))
{
empty = XNamespace.None;
}
var model = new Type810Header
{
Direction = true,
TransactionSetId = element.GetChildText(empty + "TransactionSetId"),
TransactionSetControlNbr = element.GetChildText(empty + "TransactionSetControlNbr"),
TransactionDate = element.GetChildText(empty + "TransactionDate"),
InvoiceNbr = element.GetChildText(empty + "InvoiceNbr"),
ReleaseNbr = element.GetChildText(empty + "ReleaseNbr"),
TransactionTypeCode = element.GetChildText(empty + "TransactionTypeCode"),
TransactionSetPurposeCode = element.GetChildText(empty + "TransactionSetPurposeCode"),
OriginalInvoiceNbr = element.GetChildText(empty + "OriginalInvoiceNbr"),
EsiId = element.GetChildText(empty + "EsiId"),
CrAccountNumber = element.GetChildText(empty + "CRAccountNumber"),
PaymentDueDate = element.GetChildText(empty + "PaymentDueDate"),
TdspDuns = element.GetChildText(empty + "TdspDuns"),
TdspName = element.GetChildText(empty + "TdspName"),
CrDuns = element.GetChildText(empty + "CrDuns"),
CrName = element.GetChildText(empty + "CrName"),
TotalAmount = element.GetChildText(empty + "TotalAmount"),
CustNoForESCO = element.GetChildText(empty + "CustNoForESCO"),
PreviousUtilityAccountNumber = element.GetChildText(empty + "PreviousUtilityAccountNumber"),
BillPresenter = element.GetChildText(empty + "BillPresenter"),
BillCalculator = element.GetChildText(empty + "BillCalculator"),
GasPoolId = element.GetChildText(empty + "GasPoolId"),
CustomerDUNS = element.GetChildText(empty + "CustomerDUNS"),
ChangeCode = element.GetChildText(empty + "ChangeCode"),
ChangeCodeDesc = element.GetChildText(empty + "ChangeCodeDesc"),
BillingCycleNumber = element.GetChildText(empty + "BillingCycleNumber"),
InvoicePeriodStart = element.GetChildText(empty + "InvoicePeriodStart"),
InvoicePeriodEnd = element.GetChildText(empty + "InvoicePeriodEnd"),
AlternateEsiId = element.GetChildText(empty + "AlternateEsiId"),
ServiceDeliveryPoint = element.GetChildText(empty + "ServiceDeliveryPoint"),
TransasctionTypeId = 24,
};
var balanceLoopElement = element.Element(empty + "BalanceLoop");
if (balanceLoopElement != null)
{
var balanceElements = balanceLoopElement.Elements(empty + "Balance");
foreach (var balanceElement in balanceElements)
{
var balanceModel = ParseBalance(balanceElement, namespaces);
model.AddBalance(balanceModel);
}
}
var paymentLoopElement = element.Element(empty + "PaymentLoop");
if (paymentLoopElement != null)
{
var paymentElements = paymentLoopElement.Elements(empty + "Payment");
foreach (var paymentElement in paymentElements)
{
var paymentModel = ParsePayment(paymentElement, namespaces);
model.AddPayment(paymentModel);
}
}
var detailLoopElement = element.Element(empty + "DetailLoop");
if (detailLoopElement != null)
{
var detailElements = detailLoopElement.Elements(empty + "Detail");
foreach (var detailElement in detailElements)
{
var detailModel = ParseDetail(detailElement, namespaces);
model.AddDetail(detailModel);
}
}
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 summaryLoopElement = element.Element(empty + "SummaryLoop");
if (summaryLoopElement != null)
{
var summaryElements = summaryLoopElement.Elements(empty + "Summary");
foreach (var summaryElement in summaryElements)
{
var summaryModel = ParseSummary(summaryElement, namespaces);
model.AddSummary(summaryModel);
}
}
var messageLoopElement = element.Element(empty + "MessageLoop");
if (messageLoopElement != null)
{
var messageElements = messageLoopElement.Elements(empty + "Message");
foreach (var messageElement in messageElements)
{
var messageModel = ParseMessage(messageElement, namespaces);
model.AddMessage(messageModel);
}
}
return(model);
}
public void WriteHeader(XContainer container, Type810Header header)
{
if (header == null)
{
return;
}
if (!header.HeaderKey.HasValue)
{
return;
}
var headerKey = header.HeaderKey.Value;
var element = new XElement("Header",
new XElement("HeaderKey", headerKey),
new XElement("MarketFileId", header.MarketFileId));
element.TryAddElement("TransactionSetId", header.TransactionSetId);
element.TryAddElement("TransactionSetControlNbr", header.TransactionSetControlNbr);
element.TryAddElement("TransactionDate", header.TransactionDate);
element.TryAddElement("InvoiceNbr", header.InvoiceNbr);
element.Add(new XElement("ReleaseNbr", header.ReleaseNbr));
element.TryAddElement("TransactionTypeCode", header.TransactionTypeCode);
element.TryAddElement("TransactionSetPurposeCode", header.TransactionSetPurposeCode);
element.TryAddElement("OriginalInvoiceNbr", header.OriginalInvoiceNbr);
element.TryAddElement("EsiId", header.EsiId);
if (string.IsNullOrEmpty(header.AlternateEsiId))
{
element.TryAddElement("AlternateEsiId", header.EsiId);
}
else
{
element.TryAddElement("AlternateEsiId", header.AlternateEsiId);
}
element.TryAddElement("CRAccountNumber", header.CrAccountNumber);
element.TryAddElement("PaymentDueDate", header.PaymentDueDate);
element.TryAddElement("TdspDuns", header.TdspDuns);
element.TryAddElement("TdspName", header.TdspName);
element.TryAddElement("CrDuns", header.CrDuns);
element.TryAddElement("CrName", header.CrName);
decimal totalAmount;
if (decimal.TryParse(header.TotalAmount, out totalAmount))
{
totalAmount *= 100;
}
else
{
totalAmount = 0M;
}
element.TryAddElement("TotalAmount", totalAmount.ToString("0"));
element.TryAddElement("CustNoForESCO", header.CustNoForESCO);
element.TryAddElement("PreviousUtilityAccountNumber", header.PreviousUtilityAccountNumber);
element.TryAddElement("BillPresenter", header.BillPresenter);
element.TryAddElement("BillCalculator", header.BillCalculator);
element.TryAddElement("GasPoolId", header.GasPoolId);
element.TryAddElement("CustomerDUNS", header.CustomerDUNS);
element.TryAddElement("ServiceDeliveryPoint", header.ServiceDeliveryPoint);
container.Add(element);
logger.TraceFormat("Added 810 \"Header\" XML element for Header {0}", headerKey);
var balances = exportDataAccess.ListBalances(headerKey);
WriteBalance(element, balances);
var details = exportDataAccess.ListDetails(headerKey);
WriteDetail(element, details);
var names = exportDataAccess.ListNames(headerKey);
WriteName(element, names);
var summaries = exportDataAccess.ListSummaries(headerKey);
WriteSummary(element, summaries);
var messages = exportDataAccess.ListMessages(headerKey);
WriteMessage(element, messages);
if (clientDataAccess.ShouldExportMeterData(header.TdspDuns))
{
WriteMeter(element, details[0].Items);
}
}
public void SaveHeader(Type810Header header, int marketFileId)
{
header.MarketFileId = marketFileId;
SaveHeader(header);
}
public void WriteCustInvHeader(Prism810Context context, CustomerInvoiceConfigModel configModel, Type810Header header)
{
if (!context.IsCustomerInvoice)
{
return;
}
var referenceNbr = header.InvoiceNbr;
if (configModel.UseAccNumberForInvoice)
{
referenceNbr = header.EsiId;
}
var line = string.Format("SH|{0}|{1}|O|", context.TradingPartnerId, referenceNbr);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"SH\" PRISM line for Header {0}", header.HeaderKey);
}
public Type810Header[] ListUnprocessed(string ldcDuns, string duns, int providerId)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("esp_810ExportList"))
{
command.AddIfNotEmptyOrDbNull("@CrDuns", duns)
.AddIfNotEmptyOrDbNull("@TDSPDuns", ldcDuns);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var collection = new List <Type810Header>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new Type810Header
{
HeaderKey = reader.GetInt32("810_Key"),
Direction = reader.GetBoolean("Direction"),
};
reader.TryGetInt32("MarketFileId", x => item.MarketFileId = x);
reader.TryGetString("TransactionSetId", x => item.TransactionSetId = x);
reader.TryGetString("TransactionSetControlNbr", x => item.TransactionSetControlNbr = x);
reader.TryGetString("TransactionDate", x => item.TransactionDate = x);
reader.TryGetString("InvoiceNbr", x => item.InvoiceNbr = x);
reader.TryGetString("ReleaseNbr", x => item.ReleaseNbr = x);
reader.TryGetString("TransactionTypeCode", x => item.TransactionTypeCode = x);
reader.TryGetString("TransactionSetPurposeCode", x => item.TransactionSetPurposeCode = x);
reader.TryGetString("OriginalInvoiceNbr", x => item.OriginalInvoiceNbr = x);
reader.TryGetString("EsiId", x => item.EsiId = x);
reader.TryGetString("AlternateEsiId", x => item.AlternateEsiId = x);
reader.TryGetString("CRAccountNumber", x => item.CrAccountNumber = x);
reader.TryGetString("PaymentDueDate", x => item.PaymentDueDate = 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("TotalAmount", x => item.TotalAmount = x);
reader.TryGetInt32("ProcessFlag", x => item.ProcessFlag = x);
reader.TryGetDateTime("ProcessDate", x => item.ProcessDate = x);
reader.TryGetInt32("TransactionTypeID", x => item.TransasctionTypeId = x);
reader.TryGetInt32("MarketID", x => item.MarketId = x);
reader.TryGetInt32("ProviderID", x => item.ProviderId = x);
reader.TryGetString("CustNoForESCO", x => item.CustNoForESCO = x);
reader.TryGetString("PreviousUtilityAccountNumber", x => item.PreviousUtilityAccountNumber = x);
reader.TryGetString("BillPresenter", x => item.BillPresenter = x);
reader.TryGetString("BillCalculator", x => item.BillCalculator = x);
reader.TryGetString("GasPoolId", x => item.GasPoolId = x);
reader.TryGetString("CustomerDUNS", x => item.CustomerDUNS = x);
reader.TryGetString("ChangeCode", x => item.ChangeCode = x);
reader.TryGetString("ChangeCodeDesc", x => item.ChangeCodeDesc = x);
reader.TryGetString("BillingCycleNumber", x => item.BillingCycleNumber = x);
reader.TryGetString("InvoicePeriodStart", x => item.InvoicePeriodStart = x);
reader.TryGetString("InvoicePeriodEnd", x => item.InvoicePeriodEnd = x);
reader.TryGetString("ServiceDeliveryPoint", x => item.ServiceDeliveryPoint = x);
collection.Add(item);
}
return(collection.ToArray());
}
}
}
public void AddHeader(Type810Header item)
{
headers.Add(item);
}
public void WriteCustInvAccount(Prism810Context context, CustomerInvoiceConfigModel configModel, Type810Header header)
{
if (!context.IsCustomerInvoice)
{
return;
}
var partnerId = context.TradingPartnerId;
var stateId = partnerId.Substring(3, 2);
var invoiceNbr = header.InvoiceNbr;
if (configModel.UseAccNumberForInvoice)
{
invoiceNbr = header.EsiId;
}
var arSummary = clientDataAccess.LoadArSummaryByInvoice(header.InvoiceNbr);
if (arSummary == null)
{
logger.ErrorFormat("Could not load Customer AR Summary from Invoice {0}", header.InvoiceNbr);
return;
}
var previousBalance = arSummary.PrevBal;
var currentBalance = arSummary.BalDue;
var billingBalance = (arSummary.PrevBal - arSummary.CurrPmts + arSummary.CurrAdjs);
var billActionCode = "BD";
var ldcName = header.TdspName;
if (header.CustomerDUNS.Equals("007928344"))
{
billActionCode = "PR";
}
if (header.CustomerDUNS.Equals("055458350"))
{
billActionCode = "ME";
ldcName = "NISC";
}
if (header.CustomerDUNS.Equals("PROKARMAI", StringComparison.Ordinal))
{
billActionCode = "ME";
ldcName = "PROKARMA";
}
var line =
string.Format(
"01|{0}|{1}|{2}|{3}|{4}||||||||{5}|{6}|||||{7}|{8}|{9}|{10}|||||{11}|{12}|{13}|{14}|||||||||||||||||||||||||||00||||",
context.TradingPartnerId, stateId, header.TransactionDate, invoiceNbr, billActionCode,
header.CrAccountNumber, header.EsiId, ldcName, header.CustomerDUNS, context.BillFromName,
context.BillFromDuns, header.PaymentDueDate, previousBalance, billingBalance, currentBalance);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"01\" PRISM line for Header {0}", header.HeaderKey);
}
public void WriteCustInvAddress(Prism810Context context, CustomerInvoiceConfigModel configModel, Type810Header header)
{
if (!context.IsCustomerInvoice)
{
return;
}
string entityName;
string entityId;
string address1;
string address2;
string city;
string state;
string postalCode;
var attn = string.Empty;
if (configModel.BtCustomAddressLine)
{
entityName = configModel.BtEntityName;
entityId = configModel.BtEntityId;
attn = configModel.BtAttn;
address1 = configModel.BtEntityAddress1;
address2 = configModel.BtEntityAddress2;
city = configModel.BtEntityCity;
state = configModel.BtEntityState;
postalCode = configModel.BtEntityZip;
}
else
{
var customer = clientDataAccess.LoadCustomerDetailByEsiId(header.EsiId);
if (customer == null)
{
logger.ErrorFormat("Could not load Customer from EsiId {0}.", header.EsiId);
return;
}
entityName = customer.CustName.ToAscii();
entityId = customer.CustNo.ToAscii();
address1 = customer.Address1.ToAscii();
address2 = customer.Address2.ToAscii();
city = customer.City.ToAscii();
state = customer.State.ToAscii();
postalCode = customer.Zip.ToAscii();
}
var line = string.Format("05|{0}|BT|{1}|{2}|{3}||{4}|{5}|{6}|{7}|{8}||||||||", context.TradingPartnerId,
entityName, entityId, attn, address1, address2, city, state, postalCode);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"05\" PRISM line for Header {0}", header.HeaderKey);
}
public void WriteAccount(Prism810Context context, Type810Header header)
{
var partnerId = context.TradingPartnerId;
var stateId = partnerId.Substring(3, 2);
if (!header.HeaderKey.HasValue)
{
return;
}
var headerKey = header.HeaderKey.Value;
string line;
if (context.IsCustomerInvoice)
{
var details = marketDataAccess.ListDetails(headerKey);
if (details == null || details.Length == 0)
{
logger.ErrorFormat("No 810 Detail records found for 810 Key {0}", headerKey);
return;
}
var customer = clientDataAccess.LoadCustomerDetailByEsiId(header.EsiId);
if (customer == null)
{
logger.ErrorFormat("Could not load Customer from EsiId {0}.", header.EsiId);
return;
}
var arSummary = clientDataAccess.LoadArSummaryByInvoice(header.InvoiceNbr);
if (arSummary == null)
{
logger.ErrorFormat("Could not load Customer AR Summary from Invoice {0}", header.InvoiceNbr);
return;
}
var detail = details.First();
var previousBalance = arSummary.PrevBal;
var currentBalance = arSummary.BalDue;
var billingBalance = (arSummary.PrevBal - arSummary.CurrPmts + arSummary.CurrAdjs);
line =
string.Format(
"01|{0}|{1}|{2}|{3}|{4}|||||||{5}|{6}|||||||{7}|{8}|{9}|{10}|{11}|{12}|{13}|{14}|||||{15}|{16}|||{17}|{18}|{19}|{20}|{21}|||||||||00||||",
context.TradingPartnerId, stateId, header.TransactionDate, header.InvoiceNbr,
header.TransactionTypeCode, customer.CustNo, header.EsiId, context.BillFromName,
context.BillFromDuns, customer.CustName.ToAscii(), header.CustNoForESCO, header.PaymentDueDate,
previousBalance, billingBalance, currentBalance, detail.ServicePeriodStartDate,
detail.ServicePeriodEndDate, customer.Address1.ToAscii(), customer.Address2.ToAscii(),
customer.City.ToAscii(), customer.State, customer.Zip);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"01\" PRISM line for Header {0}", header.HeaderKey);
return;
}
var customerName = string.Empty;
if (context.Market == MarketOptions.Texas)
{
var firstName = marketDataAccess.LoadFirstName(headerKey);
if (firstName != null)
{
customerName = firstName.EntityName;
if (customerName.Length > 60)
{
customerName = customerName.Substring(0, 60);
}
}
}
var esiId = IdentifyEsiId(context, header);
var membershipId = IdentifyLdcAccountNumber(context, header);
line =
string.Format(
"01|{0}|{1}|{2}|{3}|{4}|{5}|{6}|||||{7}|{8}|{14}|||{15}|{16}|{9}|{10}|{11}|{12}|{17}||||||||||||||||||||||||||||||{13}|||||",
context.TradingPartnerId, stateId, header.TransactionDate, header.InvoiceNbr, header.ReleaseNbr,
header.TransactionTypeCode, header.TransactionSetPurposeCode, header.OriginalInvoiceNbr,
header.CrAccountNumber, context.BillFromName, context.BillFromDuns, header.CrName.ToAscii(),
header.CrDuns, esiId, membershipId, header.BillPresenter, header.BillCalculator,
customerName.ToAscii());
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"01\" PRISM line for Header {0}", header.HeaderKey);
}
public void WriteCustInvRemitanceAddress(Prism810Context context, CustomerInvoiceConfigModel configModel, Type810Header header)
{
if (!context.IsCustomerInvoice)
{
return;
}
if (!configModel.ReAddressLine)
{
return;
}
var entityName = configModel.ReEntityName.ToAscii();
var entityId = configModel.ReEntityId.ToAscii();
var address1 = configModel.ReEntityAddress1.ToAscii();
var address2 = configModel.ReEntityAddress2.ToAscii();
var city = configModel.ReEntityCity.ToAscii();
var state = configModel.ReEntityState;
var postalCode = configModel.ReEntityZip;
var line = string.Format("05|{0}|RE|{1}|{2}|||{3}|{4}|{5}|{6}|{7}||||||||", context.TradingPartnerId,
entityName, entityId, address1, address2, city, state, postalCode);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"05\" PRISM line for Header {0}", header.HeaderKey);
}
public void WriteNiscCharges(Prism810Context context, Type810Header header)
{
if (!context.IsCustomerInvoice)
{
return;
}
if (!header.HeaderKey.HasValue)
{
return;
}
var headerKey = header.HeaderKey.Value;
var charges = marketDataAccess.ListDetailItemChargesByHeader(headerKey);
if (charges == null || charges.Length == 0)
{
return;
}
var lineNumber = 1;
foreach (var charge in charges)
{
if (charge.ChargeCode.Equals("D140", StringComparison.Ordinal))
{
continue;
}
var rate = charge.Rate;
if (rate.Length > 0)
{
rate = decimal.Round(decimal.Parse(rate), 5).ToString();
}
var uom = charge.UOM;
if (string.IsNullOrEmpty(uom))
{
uom = "EA";
}
var amount = charge.Amount;
if (amount.Contains("."))
{
amount = amount.Substring(0, amount.IndexOf('.') + 3);
}
var chargeIndicator = "A";
double amountValue;
if (double.TryParse(charge.Amount, out amountValue))
{
if (amountValue > 0d)
{
chargeIndicator = "C";
}
}
var line = string.Format("40|{0}|{1}|{2}||{3}|{4}|{5}|{6}|{7}||{8}||", context.TradingPartnerId,
lineNumber, chargeIndicator, charge.ChargeCode, amount, rate, uom, charge.Quantity,
charge.Description);
lineNumber++;
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"40\" PRISM line for Header {0}", header.HeaderKey);
}
}
public void WriteCustInvSummary(Prism810Context context, CustomerInvoiceConfigModel configModel, Type810Header header)
{
if (!context.IsCustomerInvoice)
{
return;
}
var runningTotal = (Math.Truncate(context.RunningTotal * 100) / 100).ToString("#.#0");
var line = string.Format("60|{0}|{1}|1|", context.TradingPartnerId, runningTotal);
context.AppendLine(line);
logger.TraceFormat("Wrote 810 \"60\" PRISM line for Header {0}", header.HeaderKey);
}
