public Type650Name ParseName(XElement element, IDictionary <string, XNamespace> namespaces)
{
XNamespace empty;
if (!namespaces.TryGetValue(string.Empty, out empty))
{
empty = XNamespace.None;
}
var model = new Type650Name
{
EntityIdType = element.GetChildText(empty + "EntityIdType"),
EntityName = element.GetChildText(empty + "EntityName"),
EntityName2 = element.GetChildText(empty + "EntityName2"),
EntityName3 = element.GetChildText(empty + "EntityName3"),
EntityDuns = element.GetChildText(empty + "EntityDuns"),
EntityIdCode = element.GetChildText(empty + "EntityIdCode"),
Address1 = element.GetChildText(empty + "Address1"),
Address2 = element.GetChildText(empty + "Address2"),
City = element.GetChildText(empty + "City"),
State = element.GetChildText(empty + "State"),
PostalCode = element.GetChildText(empty + "PostalCode"),
CountryCode = element.GetChildText(empty + "CountryCode"),
ContactName = element.GetChildText(empty + "ContactName"),
ContactPhoneNbr1 = element.GetChildText(empty + "ContactPhoneNbr1"),
ContactPhoneNbr2 = element.GetChildText(empty + "CotnactPhoneNbr2"),
};
return(model);
}
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 Type650Name[] ListNames(int headerKey)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("esp_650NameList"))
{
command.AddWithValue("@650_Key", headerKey);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var collection = new List <Type650Name>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new Type650Name
{
HeaderKey = headerKey,
NameKey = reader.GetInt32("Name_Key"),
};
reader.TryGetString("EntityIdType", x => item.EntityIdType = x);
reader.TryGetString("EntityName", x => item.EntityName = x);
reader.TryGetString("EntityName2", x => item.EntityName2 = x);
reader.TryGetString("EntityName3", x => item.EntityName3 = x);
reader.TryGetString("EntityDuns", x => item.EntityDuns = x);
reader.TryGetString("EntityIdCode", x => item.EntityIdCode = x);
reader.TryGetString("Address1", x => item.Address1 = x);
reader.TryGetString("Address2", x => item.Address2 = x);
reader.TryGetString("City", x => item.City = x);
reader.TryGetString("State", x => item.State = x);
reader.TryGetString("PostalCode", x => item.PostalCode = x);
reader.TryGetString("CountryCode", x => item.CountryCode = x);
reader.TryGetString("ContactCode", x => item.ContactCode = x);
reader.TryGetString("ContactName", x => item.ContactName = x);
reader.TryGetString("ContactPhoneNbr1", x => item.ContactPhoneNbr1 = x);
reader.TryGetString("ContactPhoneNbr2", x => item.ContactPhoneNbr2 = x);
collection.Add(item);
}
return(collection.ToArray());
}
}
}
public int InsertName(Type650Name model)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp650NameInsert"))
{
SqlParameter keyParameter;
command.AddWithValue("@650_Key", model.HeaderKey)
.AddIfNotEmptyOrDbNull("@EntityIdType", model.EntityIdType)
.AddIfNotEmptyOrDbNull("@EntityName", model.EntityName)
.AddIfNotEmptyOrDbNull("@EntityName2", model.EntityName2)
.AddIfNotEmptyOrDbNull("@EntityName3", model.EntityName3)
.AddIfNotEmptyOrDbNull("@EntityDuns", model.EntityDuns)
.AddIfNotEmptyOrDbNull("@EntityIdCode", model.EntityIdCode)
.AddIfNotEmptyOrDbNull("@Address1", model.Address1)
.AddIfNotEmptyOrDbNull("@Address2", model.Address2)
.AddIfNotEmptyOrDbNull("@City", model.City)
.AddIfNotEmptyOrDbNull("@State", model.State)
.AddIfNotEmptyOrDbNull("@PostalCode", model.PostalCode)
.AddIfNotEmptyOrDbNull("@CountryCode", model.CountryCode)
.AddIfNotEmptyOrDbNull("@ContactName", model.ContactName)
.AddIfNotEmptyOrDbNull("@ContactPhoneNbr1", model.ContactPhoneNbr1)
.AddIfNotEmptyOrDbNull("@ContactPhoneNbr2", model.ContactPhoneNbr2)
.AddOutParameter("@Name_Key", SqlDbType.Int, out keyParameter);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.ExecuteNonQuery();
if (keyParameter.Value == null)
{
throw new Exception();
}
var nameKey = (int)keyParameter.Value;
model.NameKey = nameKey;
return(nameKey);
}
}
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());
}
}
}
