public void WriteHeader(Prism650Context context, Type650Header header)
{
var line = string.Format("SH|{0}|{1}|O|", context.TradingPartnerId, header.TransactionNbr);
context.AppendLine(line);
logger.TraceFormat("Wrote 650 \"SH\" PRISM line for Header {0}", header.HeaderKey);
}
public void ParseHeader(Prism650Context context, string[] marketFields)
{
var headerModel = new Type650Header
{
TransactionSetPurposeCode = marketFields.AtIndex(3),
TransactionDate = marketFields.AtIndex(4),
TransactionNbr = marketFields.AtIndex(5),
ReferenceNbr = marketFields.AtIndex(6),
TransactionType = marketFields.AtIndex(7),
ActionCode = marketFields.AtIndex(8),
TdspName = marketFields.AtIndex(20),
TdspDuns = marketFields.AtIndex(21),
CrName = marketFields.AtIndex(22),
CrDuns = marketFields.AtIndex(23),
ProcessedReceivedDateTime = marketFields.AtIndex(24),
};
var identifiedMarket = clientDataAccess.IdentifyMarket(headerModel.TdspDuns);
if (identifiedMarket.HasValue)
{
context.SetMarket(identifiedMarket.Value);
}
headerModel.MarketId = context.MarketId;
headerModel.ProviderId = 1;
context.PushModel(headerModel);
var hasEntityName = false;
var hasContactName = false;
marketFields.TryAtIndex(9, x => hasEntityName = true);
marketFields.TryAtIndex(17, x => hasContactName = true);
if (!hasEntityName || !hasContactName)
{
return;
}
var nameModel = new Type650Name
{
EntityName = marketFields.AtIndex(9),
EntityName2 = marketFields.AtIndex(10),
EntityName3 = marketFields.AtIndex(11),
Address1 = marketFields.AtIndex(12),
Address2 = marketFields.AtIndex(13),
City = marketFields.AtIndex(14),
State = marketFields.AtIndex(15),
PostalCode = marketFields.AtIndex(16),
ContactName = marketFields.AtIndex(17),
ContactPhoneNbr1 = marketFields.AtIndex(18),
ContactPhoneNbr2 = marketFields.AtIndex(19),
};
headerModel.AddName(nameModel);
}
public Type650Header ParseHeader(XElement element, IDictionary <string, XNamespace> namespaces)
{
XNamespace empty;
if (!namespaces.TryGetValue(string.Empty, out empty))
{
empty = XNamespace.None;
}
var model = new Type650Header
{
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"),
ReferenceNbr = element.GetChildText(empty + "ReferenceNbr"),
TransactionType = element.GetChildText(empty + "TransactionType"),
ActionCode = element.GetChildText(empty + "ActionCode"),
TdspName = element.GetChildText(empty + "TdspName"),
TdspDuns = element.GetChildText(empty + "TdspDuns"),
CrName = element.GetChildText(empty + "CrName"),
CrDuns = element.GetChildText(empty + "CrDuns"),
ProcessedReceivedDateTime = element.GetChildText(empty + "ProcessedReceivedDateTime"),
};
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 serviceLoopElement = element.Element(empty + "ServiceLoop");
if (serviceLoopElement != null)
{
var serviceElements = serviceLoopElement.Elements(empty + "Service");
foreach (var serviceElement in serviceElements)
{
var serviceModel = ParseService(serviceElement, namespaces);
model.AddService(serviceModel);
}
}
return(model);
}
public void SaveHeader(Type650Header 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;
var nameKey = marketDataAccess.InsertName(name);
logger.DebugFormat("Inserted Name \"{0}\" for Header \"{1}\".", nameKey, headerKey);
}
foreach (var service in header.Services)
{
service.HeaderKey = headerKey;
var serviceKey = marketDataAccess.InsertService(service);
logger.DebugFormat("Inserted Service \"{0}\" for Header \"{1}\".", serviceKey, headerKey);
foreach (var change in service.Changes)
{
change.ServiceKey = serviceKey;
var changeKey = marketDataAccess.InsertServiceChange(change);
logger.DebugFormat("Inserted Service Change \"{0}\" for Service \"{1}\".", changeKey, serviceKey);
}
foreach (var meter in service.Meters)
{
meter.ServiceKey = serviceKey;
var meterKey = marketDataAccess.InsertServiceMeter(meter);
logger.DebugFormat("Inserted Service Meter \"{0}\" for Service \"{1}\".", meterKey, serviceKey);
}
foreach (var pole in service.Poles)
{
pole.ServiceKey = serviceKey;
var poleKey = marketDataAccess.InsertServicePole(pole);
logger.DebugFormat("Inserted Service Pole \"{0}\" for Service \"{1}\".", poleKey, serviceKey);
}
foreach (var reject in service.Rejects)
{
reject.ServiceKey = serviceKey;
var rejectKey = marketDataAccess.InsertServiceReject(reject);
logger.DebugFormat("Inserted Service Reject \"{0}\" for Service \"{1}\".", rejectKey, serviceKey);
}
}
logger.Trace("Completed inserting header.");
}
public Type650Header[] ListUnprocessed(string ldcDuns, string duns, int providerId)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("esp_650ExportList"))
{
command.AddWithValue("@CrDuns", duns)
.AddWithValue("@TdspDuns", ldcDuns);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var collection = new List <Type650Header>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new Type650Header
{
HeaderKey = reader.GetInt32("650_Key"),
Direction = reader.GetBoolean("Direction"),
ProcessFlag = reader.GetInt16("ProcessFlag"),
};
reader.TryGetInt32("MarketFileId", x => item.MarketFileId = x);
reader.TryGetString("TransactionSetId", x => item.TransactionSetId = x);
reader.TryGetString("TransactionSetControlNbr", x => item.TransactionSetControlNbr = x);
reader.TryGetString("TransactionSetPurposeCode", x => item.TransactionSetPurposeCode = x);
reader.TryGetString("TransactionDate", x => item.TransactionDate = x);
reader.TryGetString("TransactionNbr", x => item.TransactionNbr = x);
reader.TryGetString("ReferenceNbr", x => item.ReferenceNbr = x);
reader.TryGetString("TransactionType", x => item.TransactionType = x);
reader.TryGetString("ActionCode", x => item.ActionCode = x);
reader.TryGetString("TdspName", x => item.TdspName = x);
reader.TryGetString("TdspDuns", x => item.TdspDuns = x);
reader.TryGetString("CrName", x => item.CrName = x);
reader.TryGetString("CrDuns", x => item.CrDuns = x);
reader.TryGetString("ProcessedReceivedDateTime", x => item.ProcessedReceivedDateTime = 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);
collection.Add(item);
}
return(collection.ToArray());
}
}
}
public void WriteService(Prism650Context context, Type650Header header)
{
if (!header.HeaderKey.HasValue)
{
return;
}
var headerKey = header.HeaderKey.Value;
var services = marketDataAccess.ListServices(headerKey);
if (services == null || services.Length == 0)
{
logger.ErrorFormat("No service record for 650 Key {0}.", headerKey);
return;
}
var service = services.First();
var line =
string.Format(
"10|{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|||||||{9}|{10}|{11}|||||||||||||||||{12}|{13}|{14}|"
, context.TradingPartnerId
, service.PurposeCode.ToUpper()
, service.PriorityCode.ToUpper()
, service.EsiId.ToUpper()
, service.SpecialProcessCode.ToUpper()
, service.ServiceReqDate.ToUpper()
, service.NotBeforeDate.ToUpper()
, service.CallAhead.ToUpper()
, service.PremLocation.ToUpper()
, service.ReportRemarks.ToUpper()
, service.Directions.ToUpper().ToAscii()
, service.MeterNbr.ToUpper()
, service.Membership.ToUpper()
, service.RemarksPermanentSuspend.ToUpper()
, service.DisconnectAuthorization.ToUpper());
context.AppendLine(line);
logger.TraceFormat("Wrote 650 \"10\" PRISM line for Header {0}", header.HeaderKey);
if (context.TransactionSetPurposeCode.Equals("13"))
{
WriteServicePole(context, service);
WriteServiceChangeReason(context, service);
return;
}
WriteServiceReject(context, service);
}
public int InsertHeader(Type650Header model)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp650HeaderInsert"))
{
SqlParameter keyParameter;
command.AddWithValue("@MarketFileId", model.MarketFileId)
.AddIfNotEmptyOrDbNull("@TransactionSetPurposeCode", model.TransactionSetPurposeCode)
.AddIfNotEmptyOrDbNull("@TransactionDate", model.TransactionDate)
.AddIfNotEmptyOrDbNull("@TransactionNbr", model.TransactionNbr)
.AddIfNotEmptyOrDbNull("@ReferenceNbr", model.ReferenceNbr)
.AddIfNotEmptyOrDbNull("@TransactionType", model.TransactionType)
.AddIfNotEmptyOrDbNull("@ActionCode", model.ActionCode)
.AddIfNotEmptyOrDbNull("@TdspName", model.TdspName)
.AddIfNotEmptyOrDbNull("@TdspDuns", model.TdspDuns)
.AddWithValue("@CrName", model.CrName)
.AddWithValue("@CrDuns", model.CrDuns)
.AddIfNotEmptyOrDbNull("@ProcessedReceivedDateTime", model.ProcessedReceivedDateTime)
.AddWithValue("@Direction", true)
.AddWithValue("@MarketID", model.MarketId)
.AddWithValue("@ProviderID", model.ProviderId)
.AddWithValue("@TransactionTypeID", model.TransactionTypeId)
.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, Type650Header 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("ReferenceNbr", header.ReferenceNbr);
element.TryAddElement("TransactionType", header.TransactionType);
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("ProcessedReceivedDateTime", header.ProcessedReceivedDateTime);
container.Add(element);
logger.TraceFormat("Added 650 \"Header\" XML element for Header {0}", headerKey);
var names = exportDataAccess.ListNames(headerKey);
WriteName(element, names);
var services = exportDataAccess.ListServices(headerKey);
WriteService(element, services);
}
public void WriteAccount(Prism650Context context, Type650Header header)
{
if (!header.HeaderKey.HasValue)
{
return;
}
var headerKey = header.HeaderKey.Value;
var names = marketDataAccess.ListNames(headerKey);
var name = names.FirstOrDefault(x => x.EntityIdType.Equals("8R", StringComparison.Ordinal));
if (name == null)
{
name = new Type650Name
{
EntityName = string.Empty,
EntityName2 = string.Empty,
EntityName3 = string.Empty,
Address1 = string.Empty,
Address2 = string.Empty,
City = string.Empty,
State = string.Empty,
PostalCode = string.Empty,
ContactName = string.Empty,
ContactPhoneNbr1 = string.Empty,
ContactPhoneNbr2 = string.Empty,
};
}
var phone1 = numericExp.Replace(name.ContactPhoneNbr1, string.Empty);
var phone2 = numericExp.Replace(name.ContactPhoneNbr2, string.Empty);
var postalCode = numericExp.Replace(name.PostalCode, string.Empty);
if (phone1.Length > 16)
{
phone1 = phone1.Substring(0, 16);
}
if (phone2.Length > 16)
{
phone2 = phone2.Substring(0, 16);
}
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}|{11}|{12}|{13}|{14}|{15}|{16}|{17}|{18}|{19}|{20}|{21}|{22}|{23}|"
, partnerId
, stateId
, header.TransactionSetPurposeCode.ToUpper()
, header.TransactionDate.ToUpper()
, header.TransactionNbr.ToUpper()
, header.ReferenceNbr.ToUpper()
, header.TransactionType.ToUpper()
, header.ActionCode.ToUpper()
, name.EntityName.ToUpper().ToAscii()
, name.EntityName2.ToUpper().ToAscii()
, name.EntityName3.ToUpper().ToAscii()
, name.Address1.ToUpper()
, name.Address2.ToUpper()
, name.City.ToUpper()
, name.State.ToUpper()
, postalCode
, name.ContactName.ToUpper().ToAscii()
, phone1
, phone2
, header.TdspName.ToUpper()
, header.TdspDuns.ToUpper()
, header.CrName.ToUpper()
, header.CrDuns.ToUpper()
, header.ProcessedReceivedDateTime);
context.AppendLine(line);
logger.TraceFormat("Wrote 650 \"01\" PRISM line for Header {0}", header.HeaderKey);
}
public void Should_Produce_The_Same_Content_As_A_Sample_File()
{
const string expectedContent =
@"SH|ACTTX957877905MTR|8842008|O|
01|ACTTX957877905MTR|TX|13|20130319|8842008|8841948|79|IT|TYRA BROWN|||1800 EL PASEO ST|APT 205|HOUSTON|TX|77054|BROWN TYRA|6824338859|6824338859|CENTERPOINT ENERGY|957877905|ACCENT ENERGY TEXAS|133305370|201303191003|
10|ACTTX957877905MTR|RC001|01|1008901018191437145100|N|20130319||N||||||||||||||||||||||||||||||
TL|1";
// arrange
var port = new CspDunsPortModel
{
CspDunsId = 1,
CspDunsPortId = 1,
Duns = "133305370",
LdcDuns = string.Empty,
LdcShortName = string.Empty,
TradingPartnerId = "ACTTX{DUNS}",
ProviderId = 1,
FileType = string.Empty,
};
var header = new Type650Header
{
HeaderKey = 275531,
TransactionNbr = "8842008",
TransactionSetPurposeCode = "13",
TransactionDate = "20130319",
TransactionType = "79",
ReferenceNbr = "8841948",
ActionCode = "IT",
TdspName = "Centerpoint Energy",
TdspDuns = "957877905",
CrName = "Accent Energy Texas",
CrDuns = "133305370",
ProcessedReceivedDateTime = "201303191003",
TransactionTypeId = 18,
MarketId = 1,
ProviderId = 1,
};
var name = new Type650Name
{
HeaderKey = 275531,
NameKey = 122923,
EntityName = "Tyra Brown",
EntityName2 = string.Empty,
EntityName3 = string.Empty,
EntityIdType = "8R",
Address1 = "1800 EL PASEO ST",
Address2 = "APT 205",
City = "HOUSTON",
State = "TX",
PostalCode = "77054",
ContactName = "Brown Tyra",
ContactPhoneNbr1 = "682-433-8859",
ContactPhoneNbr2 = "682-433-8859"
};
var service = new Type650Service
{
HeaderKey = 275531,
ServiceKey = 275393,
PurposeCode = "RC001",
PriorityCode = "01",
EsiId = "1008901018191437145100",
SpecialProcessCode = "N",
ServiceReqDate = "20130319",
CallAhead = "N",
NotBeforeDate = string.Empty,
PremLocation = string.Empty,
ReportRemarks = string.Empty,
Directions = string.Empty,
MeterNbr = string.Empty,
Membership = string.Empty,
RemarksPermanentSuspend = string.Empty,
DisconnectAuthorization = string.Empty
};
clientDataAccess.Expect(x => x.ListCspDunsPort())
.Return(new[] { port });
clientDataAccess.Expect(x => x.IdentifyMarket(Arg <string> .Is.Anything))
.Return(1);
exportDataAccess.Expect(x => x.ListUnprocessed(Arg <string> .Is.Anything, Arg <string> .Is.Anything, Arg.Is(1)))
.Return(new[] { header });
exportDataAccess.Expect(x => x.ListNames(Arg <int> .Is.Anything))
.Return(new[] { name });
exportDataAccess.Expect(x => x.ListServices(Arg <int> .Is.Anything))
.Return(new[] { service });
exportDataAccess.Stub(x => x.ListServiceRejects(Arg <int> .Is.Anything))
.Return(new Type650ServiceReject[0]);
exportDataAccess.Stub(x => x.ListServiceMeters(Arg <int> .Is.Anything))
.Return(new Type650ServiceMeter[0]);
// act
var results = concern.Export(CancellationToken.None);
// assert
Assert.IsNotNull(results);
Assert.AreEqual(1, results.Length);
var result = results[0];
Assert.IsNotNull(result);
Assert.AreEqual(1, result.HeaderCount);
CollectionAssert.Contains(result.HeaderKeys, 275531);
result.FinalizeDocument(1);
Assert.AreEqual(expectedContent, result.Content);
}
public Type650Header[] ListUnprocessed(string ldcDuns, string duns, int providerId)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp650ExportList"))
{
command.AddWithValue("@TDSPDuns", ldcDuns)
.AddWithValue("@CrDuns", duns)
.AddWithValue("@ProviderID", providerId);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var collection = new List <Type650Header>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new Type650Header
{
HeaderKey = reader.GetInt32("650_Key"),
Direction = reader.GetBoolean("Direction"),
ProcessFlag = reader.GetInt16("ProcessFlag"),
};
reader.TryGetString("TransactionDate", x => item.TransactionDate = x);
reader.TryGetString("TransactionSetPurposeCode", x => item.TransactionSetPurposeCode = x);
reader.TryGetString("TransactionNbr", x => item.TransactionNbr = x);
reader.TryGetString("ReferenceNbr", x => item.ReferenceNbr = x);
reader.TryGetString("TransactionType", x => item.TransactionType = x);
reader.TryGetString("ActionCode", x => item.ActionCode = x);
reader.TryGetString("TdspName", x => item.TdspName = x);
reader.TryGetString("TdspDuns", x => item.TdspDuns = x);
reader.TryGetString("CrName", x => item.CrName = x);
reader.TryGetString("CrDuns", x => item.CrDuns = x);
reader.TryGetString("ProcessedReceivedDateTime", x => item.ProcessedReceivedDateTime = x);
reader.TryGetInt32("MarketID", x => item.MarketId = x);
reader.TryGetInt32("TransactionTypeID", x => item.TransactionTypeId = x);
reader.TryGetInt32("ProviderID", x => item.ProviderId = x);
// this is a little concerning since this stored procedure
// actually returns a name record as well
var name = new Type650Name {
HeaderKey = reader.GetInt32("650_Key")
};
reader.TryGetString("Customer", x => name.EntityName = x);
reader.TryGetString("CustomerName1", x => name.EntityName2 = x);
reader.TryGetString("CustomerName2", x => name.EntityName3 = x);
reader.TryGetString("Address1", x => name.Address1 = x);
reader.TryGetString("Address2", x => name.Address2 = x);
reader.TryGetString("City", x => name.City = x);
reader.TryGetString("State", x => name.State = x);
reader.TryGetString("PostalCode", x => name.PostalCode = x);
reader.TryGetString("TechnicalContact", x => name.ContactName = x);
reader.TryGetString("ContactPhoneNbr1", x => name.ContactPhoneNbr1 = x);
reader.TryGetString("ContactPhoneNbr2", x => name.ContactPhoneNbr2 = x);
item.AddName(name);
collection.Add(item);
}
return(collection.ToArray());
}
}
}
public void AddHeader(Type650Header item)
{
headers.Add(item);
}
public void SaveHeader(Type650Header header, int marketFileId)
{
header.MarketFileId = marketFileId;
SaveHeader(header);
}
