public void WriteServiceChangeReason(Prism650Context context, Type650Service service)
{
if (service == null)
{
return;
}
var serviceKey = service.ServiceKey ?? 0;
if (serviceKey == 0)
{
return;
}
var changes = marketDataAccess.ListServiceChanges(serviceKey);
if (changes == null || changes.Length == 0)
{
return;
}
foreach (var change in changes)
{
if (!change.ServiceChangeKey.HasValue)
{
continue;
}
var line = string.Format("12|{0}|{1}|", context.TradingPartnerId, change.ChangeReason);
context.AppendLine(line);
logger.TraceFormat("Wrote 650 \"12\" PRISM line for Header {0}", service.HeaderKey);
}
}
public void WriteServiceReject(Prism650Context context, Type650Service service)
{
if (service == null)
{
return;
}
var serviceKey = service.ServiceKey ?? 0;
if (serviceKey == 0)
{
return;
}
var rejects = marketDataAccess.ListServiceRejects(serviceKey);
if (rejects == null || rejects.Length == 0)
{
return;
}
foreach (var reject in rejects)
{
if (!reject.ServiceRejectKey.HasValue)
{
continue;
}
var line = string.Format("13|{0}|{1}|{2}|||", context.TradingPartnerId, reject.RejectCode,
reject.RejectReason);
context.AppendLine(line);
logger.TraceFormat("Wrote 650 \"13\" PRISM line for Header {0}", service.HeaderKey);
}
}
public void WriteServicePole(Prism650Context context, Type650Service service)
{
if (service == null)
{
return;
}
var serviceKey = service.ServiceKey ?? 0;
if (serviceKey == 0)
{
return;
}
var poles = marketDataAccess.ListServicePoles(serviceKey);
if (poles == null || poles.Length == 0)
{
return;
}
foreach (var pole in poles)
{
if (!pole.ServicePoleKey.HasValue)
{
continue;
}
var line = string.Format("11|{0}|{1}|", context.TradingPartnerId, pole.PoleNbr);
context.AppendLine(line);
logger.TraceFormat("Wrote 650 \"11\" PRISM line for Header {0}", service.HeaderKey);
}
}
public Type650Service[] ListServices(int headerKey)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp650ExportListServiceRecords"))
{
command.AddWithValue("@650Key", headerKey);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var collection = new List <Type650Service>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new Type650Service
{
HeaderKey = headerKey,
ServiceKey = reader.GetInt32("Service_Key"),
};
reader.TryGetString("PurposeCode", x => item.PurposeCode = x);
reader.TryGetString("PriorityCode", x => item.PriorityCode = x);
reader.TryGetString("ESIID", x => item.EsiId = x);
reader.TryGetString("SpecialProcessCode", x => item.SpecialProcessCode = x);
reader.TryGetString("ServiceReqDate", x => item.ServiceReqDate = x);
reader.TryGetString("NotBeforeDate", x => item.NotBeforeDate = x);
reader.TryGetString("CallAhead", x => item.CallAhead = x);
reader.TryGetString("PremLocation", x => item.PremLocation = x);
reader.TryGetString("ReportRemarks", x => item.ReportRemarks = x);
reader.TryGetString("Directions", x => item.Directions = x);
reader.TryGetString("MeterNbr", x => item.MeterNbr = x);
reader.TryGetString("Membership", x => item.Membership = x);
reader.TryGetString("RemarksPermanentSuspend", x => item.RemarksPermanentSuspend = x);
reader.TryGetString("DisconnectAuthorization", x => item.DisconnectAuthorization = x);
collection.Add(item);
}
return(collection.ToArray());
}
}
}
public Type650Service ParseService(XElement element, IDictionary <string, XNamespace> namespaces)
{
XNamespace empty;
if (!namespaces.TryGetValue(string.Empty, out empty))
{
empty = XNamespace.None;
}
var model = new Type650Service
{
PurposeCode = element.GetChildText(empty + "PurposeCode"),
PriorityCode = element.GetChildText(empty + "PriorityCode"),
EsiId = element.GetChildText(empty + "ESIID"),
SpecialProcessCode = element.GetChildText(empty + "SpecialProcessCode"),
ServiceReqDate = element.GetChildText(empty + "ServiceReqDate"),
NotBeforeDate = element.GetChildText(empty + "NotBeforeDate"),
CallAhead = element.GetChildText(empty + "CallAhead"),
PremLocation = element.GetChildText(empty + "PremLocation"),
AccStatusCode = element.GetChildText(empty + "AccStatusCode"),
AccStatusDesc = element.GetChildText(empty + "AccStatusDesc"),
EquipLocation = element.GetChildText(empty + "EquipLocation"),
ServiceOrderNbr = element.GetChildText(empty + "ServiceOrderNbr"),
CompletionDate = element.GetChildText(empty + "CompletionDate"),
CompletionTime = element.GetChildText(empty + "CompletionTime"),
ReportRemarks = element.GetChildText(empty + "ReportRemarks"),
Directions = element.GetChildText(empty + "Directions"),
MeterNbr = element.GetChildText(empty + "MeterNbr"),
MeterReadDate = element.GetChildText(empty + "MeterReadDate"),
MeterTestDate = element.GetChildText(empty + "MeterTestDate"),
MeterTestResults = element.GetChildText(empty + "MeterTestResults"),
IncidentCode = element.GetChildText(empty + "IncidentCode"),
EstRestoreDate = element.GetChildText(empty + "EstRestoreDate"),
EstRestoreTime = element.GetChildText(empty + "EstRestoreTime"),
IntStartDate = element.GetChildText(empty + "IntStartDate"),
IntStartTime = element.GetChildText(empty + "IntStartTime"),
RepairRecommended = element.GetChildText(empty + "RepairRecommended"),
Rescheduled = element.GetChildText(empty + "Rescheduled"),
InterDurationPeriod = element.GetChildText(empty + "InterDurationPeriod"),
AreaOutage = element.GetChildText(empty + "AreaOutage"),
CustRepairRemarks = element.GetChildText(empty + "CustRepairRemarks"),
MeterReadUom = element.GetChildText(empty + "MeterReadUOM"),
MeterRead = element.GetChildText(empty + "MeterRead"),
MeterReadCode = element.GetChildText(empty + "MeterReadCode"),
Membership = element.GetChildText(empty + "Membership"),
RemarksPermanentSuspend = element.GetChildText(empty + "RemarksPermanentSuspend"),
DisconnectAuthorization = element.GetChildText(empty + "DisconnectAuthorization"),
};
var changeLoopElement = element.Element(empty + "ServiceChangeLoop");
if (changeLoopElement != null)
{
var changeElements = changeLoopElement.Elements(empty + "ServiceChange");
foreach (var changeElement in changeElements)
{
var changeModel = ParseServiceChange(changeElement, namespaces);
model.AddChange(changeModel);
}
}
var meterLoopElement = element.Element(empty + "ServiceMeterLoop");
if (meterLoopElement != null)
{
var meterElements = meterLoopElement.Elements(empty + "ServiceMeter");
foreach (var meterElement in meterElements)
{
var meterModel = ParseServiceMeter(meterElement, namespaces);
model.AddMeter(meterModel);
}
}
var poleLoopElement = element.Element(empty + "ServicePoleLoop");
if (poleLoopElement != null)
{
var poleElements = poleLoopElement.Elements(empty + "ServicePole");
foreach (var poleElement in poleElements)
{
var poleModel = ParseServicePole(poleElement, namespaces);
model.AddPole(poleModel);
}
}
var rejectLoopElement = element.Element(empty + "ServiceRejectLoop");
if (rejectLoopElement != null)
{
var rejectElements = rejectLoopElement.Elements(empty + "ServiceReject");
foreach (var rejectElement in rejectElements)
{
var rejectModel = ParseServiceReject(rejectElement, namespaces);
model.AddReject(rejectModel);
}
}
return(model);
}
public void ParseService(Prism650Context context, string[] marketFields)
{
var current = context.Current;
if (current == null || current.ModelType != Type650Types.Header)
{
throw new InvalidOperationException();
}
var header = current as Type650Header;
if (header == null)
{
throw new InvalidOperationException();
}
var model = new Type650Service
{
PurposeCode = marketFields.AtIndex(2),
PriorityCode = marketFields.AtIndex(3),
EsiId = marketFields.AtIndex(4),
SpecialProcessCode = marketFields.AtIndex(5),
ServiceReqDate = marketFields.AtIndex(6),
NotBeforeDate = marketFields.AtIndex(7),
CallAhead = marketFields.AtIndex(8),
PremLocation = marketFields.AtIndex(9),
AccStatusCode = marketFields.AtIndex(10),
AccStatusDesc = marketFields.AtIndex(11),
EquipLocation = marketFields.AtIndex(12),
ServiceOrderNbr = marketFields.AtIndex(13),
CompletionDate = marketFields.AtIndex(14),
CompletionTime = marketFields.AtIndex(15),
ReportRemarks = marketFields.AtIndex(16),
Directions = marketFields.AtIndex(17),
MeterNbr = marketFields.AtIndex(18),
MeterReadDate = marketFields.AtIndex(19),
MeterTestDate = marketFields.AtIndex(20),
MeterTestResults = marketFields.AtIndex(21),
IncidentCode = marketFields.AtIndex(22),
EstRestoreDate = marketFields.AtIndex(23),
EstRestoreTime = marketFields.AtIndex(24),
IntStartDate = marketFields.AtIndex(25),
IntStartTime = marketFields.AtIndex(26),
RepairRecommended = marketFields.AtIndex(27),
Rescheduled = marketFields.AtIndex(28),
InterDurationPeriod = marketFields.AtIndex(29),
AreaOutage = marketFields.AtIndex(30),
CustRepairRemarks = marketFields.AtIndex(31),
MeterReadUom = marketFields.AtIndex(32),
MeterRead = marketFields.AtIndex(33),
MeterReadCode = marketFields.AtIndex(34),
Membership = marketFields.AtIndex(35),
RemarksPermanentSuspend = marketFields.AtIndex(36),
DisconnectAuthorization = marketFields.AtIndex(37),
PremiseTypeVerification = marketFields.AtIndex(38),
PremiseTypeDesc = marketFields.AtIndex(39),
SwitchHoldIndicator = marketFields.AtIndex(40),
SwitchHoldDesc = marketFields.AtIndex(41),
};
header.AddService(model);
context.PushModel(model);
}
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 Type650Service[] ListServices(int headerKey)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("esp_650ServiceList"))
{
command.AddWithValue("@650_Key", headerKey);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var collection = new List <Type650Service>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new Type650Service
{
HeaderKey = headerKey,
ServiceKey = reader.GetInt32("Service_Key"),
};
reader.TryGetString("PurposeCode", x => item.PurposeCode = x);
reader.TryGetString("PriorityCode", x => item.PriorityCode = x);
reader.TryGetString("ESIID", x => item.EsiId = x);
reader.TryGetString("SpecialProcessCode", x => item.SpecialProcessCode = x);
reader.TryGetString("ServiceReqDate", x => item.ServiceReqDate = x);
reader.TryGetString("NotBeforeDate", x => item.NotBeforeDate = x);
reader.TryGetString("CallAhead", x => item.CallAhead = x);
reader.TryGetString("PremLocation", x => item.PremLocation = x);
reader.TryGetString("AccStatusCode", x => item.AccStatusCode = x);
reader.TryGetString("AccStatusDesc", x => item.AccStatusDesc = x);
reader.TryGetString("EquipLocation", x => item.EquipLocation = x);
reader.TryGetString("ServiceOrderNbr", x => item.ServiceOrderNbr = x);
reader.TryGetString("CompletionDate", x => item.CompletionDate = x);
reader.TryGetString("CompletionTime", x => item.CompletionTime = x);
reader.TryGetString("ReportRemarks", x => item.ReportRemarks = x);
reader.TryGetString("Directions", x => item.Directions = x);
reader.TryGetString("MeterNbr", x => item.MeterNbr = x);
reader.TryGetString("MeterReadDate", x => item.MeterReadDate = x);
reader.TryGetString("MeterTestDate", x => item.MeterTestDate = x);
reader.TryGetString("MeterTestResults", x => item.MeterTestResults = x);
reader.TryGetString("IncidentCode", x => item.IncidentCode = x);
reader.TryGetString("EstRestoreDate", x => item.EstRestoreDate = x);
reader.TryGetString("EstRestoreTime", x => item.EstRestoreTime = x);
reader.TryGetString("IntStartDate", x => item.IntStartDate = x);
reader.TryGetString("IntStartTime", x => item.IntStartTime = x);
reader.TryGetString("RepairRecommended", x => item.RepairRecommended = x);
reader.TryGetString("Rescheduled", x => item.Rescheduled = x);
reader.TryGetString("InterDurationPeriod", x => item.InterDurationPeriod = x);
reader.TryGetString("AreaOutage", x => item.AreaOutage = x);
reader.TryGetString("CustRepairRemarks", x => item.CustRepairRemarks = x);
reader.TryGetString("MeterReadUOM", x => item.MeterReadUom = x);
reader.TryGetString("MeterRead", x => item.MeterRead = x);
reader.TryGetString("MeterReadCode", x => item.MeterReadCode = x);
reader.TryGetString("Membership", x => item.Membership = x);
reader.TryGetString("RemarksPermanentSuspend", x => item.RemarksPermanentSuspend = x);
reader.TryGetString("DisconnectAuthorization", x => item.DisconnectAuthorization = x);
collection.Add(item);
}
return(collection.ToArray());
}
}
}
public int InsertService(Type650Service model)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp650ServiceInsert"))
{
SqlParameter keyParameter;
command.AddWithValue("@650_Key", model.HeaderKey)
.AddIfNotEmptyOrDbNull("@PurposeCode", model.PurposeCode)
.AddIfNotEmptyOrDbNull("@PriorityCode", model.PriorityCode)
.AddIfNotEmptyOrDbNull("@ESIID", model.EsiId)
.AddIfNotEmptyOrDbNull("@SpecialProcessCode", model.SpecialProcessCode)
.AddIfNotEmptyOrDbNull("@ServiceReqDate", model.ServiceReqDate)
.AddIfNotEmptyOrDbNull("@NotBeforeDate", model.NotBeforeDate)
.AddIfNotEmptyOrDbNull("@CallAhead", model.CallAhead)
.AddIfNotEmptyOrDbNull("@PremLocation", model.PremLocation)
.AddIfNotEmptyOrDbNull("@AccStatusCode", model.AccStatusCode)
.AddIfNotEmptyOrDbNull("@AccStatusDesc", model.AccStatusDesc)
.AddIfNotEmptyOrDbNull("@EquipLocation", model.EquipLocation)
.AddIfNotEmptyOrDbNull("@ServiceOrderNbr", model.ServiceOrderNbr)
.AddIfNotEmptyOrDbNull("@CompletionDate", model.CompletionDate)
.AddIfNotEmptyOrDbNull("@CompletionTime", model.CompletionTime)
.AddIfNotEmptyOrDbNull("@ReportRemarks", model.ReportRemarks)
.AddIfNotEmptyOrDbNull("@Directions", model.Directions)
.AddIfNotEmptyOrDbNull("@MeterNbr", model.MeterNbr)
.AddIfNotEmptyOrDbNull("@MeterReadDate", model.MeterReadDate)
.AddIfNotEmptyOrDbNull("@MeterTestDate", model.MeterTestDate)
.AddIfNotEmptyOrDbNull("@MeterTestResults", model.MeterTestResults)
.AddIfNotEmptyOrDbNull("@IncidentCode", model.IncidentCode)
.AddIfNotEmptyOrDbNull("@EstRestoreDate", model.EstRestoreDate)
.AddIfNotEmptyOrDbNull("@EstRestoreTime", model.EstRestoreTime)
.AddIfNotEmptyOrDbNull("@IntStartDate", model.IntStartDate)
.AddIfNotEmptyOrDbNull("@IntStartTime", model.IntStartTime)
.AddIfNotEmptyOrDbNull("@RepairRecommended", model.RepairRecommended)
.AddIfNotEmptyOrDbNull("@Rescheduled", model.Rescheduled)
.AddIfNotEmptyOrDbNull("@InterDurationPeriod", model.InterDurationPeriod)
.AddIfNotEmptyOrDbNull("@AreaOutage", model.AreaOutage)
.AddIfNotEmptyOrDbNull("@CustRepairRemarks", model.CustRepairRemarks)
.AddIfNotEmptyOrDbNull("@MeterReadUOM", model.MeterReadUom)
.AddIfNotEmptyOrDbNull("@MeterRead", model.MeterRead)
.AddIfNotEmptyOrDbNull("@MeterReadCode", model.MeterReadCode)
.AddWithValue("@RemarksPermanentSuspend", model.RemarksPermanentSuspend)
.AddWithValue("@Membership", model.Membership)
.AddWithValue("@DisconnectAuthorization", model.DisconnectAuthorization)
.AddOutParameter("@Service_Key", SqlDbType.Int, out keyParameter);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.ExecuteNonQuery();
if (keyParameter.Value == null)
{
throw new Exception();
}
var serviceKey = (int)keyParameter.Value;
model.ServiceKey = serviceKey;
return(serviceKey);
}
}
