protected virtual void SaveSegment(
SqlTransaction tran,
DetachedSegment segment,
int positionInInterchange,
object interchangeId,
object functionalGroupId = null,
object transactionSetId = null,
object parentLoopId = null,
object loopId = null,
int?revisionId = null,
int?previousRevisionId = null,
bool deleted = false)
{
if (!revisionId.HasValue || SegmentHasChanged(segment, positionInInterchange, interchangeId, previousRevisionId) ||
deleted)
{
_segmentBatch.AddSegment(
tran,
interchangeId,
positionInInterchange,
revisionId ?? 0,
ConvertT(functionalGroupId),
ConvertT(transactionSetId),
ConvertT(parentLoopId),
ConvertT(loopId),
deleted,
segment,
_specs.ContainsKey(segment.SegmentId) ? _specs[segment.SegmentId] : null);
if (tran != null || _segmentBatch._segmentTable.Rows.Count >= _batchSize)
{
ExecuteBatch(tran);
}
}
}
private bool SegmentHasChanged(
DetachedSegment segment,
int positionInInterchange,
object interchangeId,
int?previousRevisionId)
{
using (var conn = new SqlConnection(this.Dsn))
{
var cmd = new SqlCommand(
string.Format(
@"SELECT RevisionId, Deleted, Segment, r.RevisedBy, r.RevisionDate
FROM [{0}].Segment s
LEFT JOIN [{1}].Revision r ON s.RevisionId = r.Id
WHERE InterchangeId = @interchangeId AND PositionInInterchange = @positionInInterchange
ORDER BY RevisionId DESC",
this.Schema,
this.CommonDb.Schema),
conn);
cmd.Parameters.AddWithValue("@interchangeId", interchangeId);
cmd.Parameters.AddWithValue("@positionInInterchange", positionInInterchange);
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.Read())
{
if (Convert.ToBoolean(reader["Deleted"]))
{
throw new InvalidOperationException(
string.Format(
Resources.SegmentAlreadyDeletedError,
segment.SegmentId,
interchangeId,
positionInInterchange,
reader["RevisedBy"],
reader["RevisionDate"]));
}
if (previousRevisionId.HasValue && Convert.ToInt64(reader["RevisionId"]) != Convert.ToInt64(previousRevisionId))
{
throw new InvalidOperationException(
string.Format(
Resources.SegmentAlreadyRevisedError,
segment.SegmentId,
interchangeId,
positionInInterchange,
reader["RevisedBy"],
reader["RevisionDate"]));
}
return(Convert.ToString(reader["Segment"]) != segment.SegmentString);
}
throw new InvalidOperationException(
string.Format(
Resources.SegmentDoesNotExist,
interchangeId,
positionInInterchange));
}
}
private bool SegmentHasChanged(
DetachedSegment segment,
int positionInInterchange,
object interchangeId,
int?previousRevisionId)
{
using (var conn = new SqlConnection(_dsn))
{
var cmd = new SqlCommand(string.Format(@"
select RevisionId, Deleted, Segment, r.RevisedBy, r.RevisionDate
from [{0}].Segment s
left join [{1}].Revision r on s.RevisionId = r.Id
where InterchangeId = @interchangeId and PositionInInterchange = @positionInInterchange
order by RevisionId desc", _schema, _commonDb.Schema), conn);
cmd.Parameters.AddWithValue("@interchangeId", interchangeId);
cmd.Parameters.AddWithValue("@positionInInterchange", positionInInterchange);
conn.Open();
var reader = cmd.ExecuteReader();
// only need to read first row
if (reader.Read())
{
if (Convert.ToBoolean(reader["Deleted"]))
{
throw new InvalidOperationException(
string.Format(
"Segment {0} of interchange {1} in position {2} has already been deleted by {3} at {4}.",
segment.SegmentId,
interchangeId,
positionInInterchange,
reader["RevisedBy"],
reader["RevisionDate"]));
}
if (previousRevisionId.HasValue && Convert.ToInt64(reader["RevisionId"]) != Convert.ToInt64(previousRevisionId))
{
throw new InvalidOperationException(
string.Format(
"Segment {0} of interchange {1} in position {2} has already been revised by {3} at {4}.",
segment.SegmentId,
interchangeId,
positionInInterchange,
reader["RevisedBy"],
reader["RevisionDate"]));
}
return(Convert.ToString(reader["Segment"]) != segment.SegmentString);
}
throw new InvalidOperationException(
string.Format(
"A segment does not exist for interchange {0} at position {1}.",
interchangeId,
positionInInterchange));
}
}
public void AddSegment(
SqlTransaction tran,
object interchangeId,
int positionInInterchange,
int revisionId,
object functionalGroupId,
object transactionSetId,
object parentLoopId,
object loopId,
bool deleted,
DetachedSegment segment,
SegmentSpecification spec)
{
this.SegmentTable.Rows.Add(
interchangeId,
positionInInterchange,
revisionId,
functionalGroupId,
transactionSetId,
parentLoopId,
loopId,
deleted,
segment.SegmentId,
segment.SegmentString);
if (spec != null)
{
var parsingError = new StringBuilder();
var fieldNames = new List <string>();
int maxElements = spec.Elements.Count;
for (var i = 1; i == 1 || i <= maxElements; i++)
{
fieldNames.Add($"{i:00}");
}
if (!this.ParsedTables.ContainsKey(segment.SegmentId))
{
this.ParsedTables.Add(segment.SegmentId, new DataTable());
this.ParsedTables[segment.SegmentId].Columns.Add("InterchangeId", this.identityType);
this.ParsedTables[segment.SegmentId].Columns.Add("PositionInInterchange", typeof(int));
this.ParsedTables[segment.SegmentId].Columns.Add("TransactionSetId", this.identityType);
this.ParsedTables[segment.SegmentId].Columns.Add("ParentLoopId", this.identityType);
this.ParsedTables[segment.SegmentId].Columns.Add("LoopId", this.identityType);
this.ParsedTables[segment.SegmentId].Columns.Add("RevisionId", typeof(int));
this.ParsedTables[segment.SegmentId].Columns.Add("Deleted", typeof(bool));
foreach (string f in fieldNames)
{
this.ParsedTables[segment.SegmentId].Columns.Add(f, typeof(string));
}
this.ParsedTables[segment.SegmentId].Columns.Add("ErrorId", this.identityType);
}
DataRow row = this.ParsedTables[segment.SegmentId].NewRow();
row["InterchangeId"] = interchangeId;
row["PositionInInterchange"] = positionInInterchange;
row["TransactionSetId"] = transactionSetId ?? DBNull.Value;
row["ParentLoopId"] = parentLoopId ?? DBNull.Value;
row["LoopId"] = loopId ?? DBNull.Value;
row["RevisionId"] = revisionId;
row["Deleted"] = deleted;
for (var i = 1; i <= segment.ElementCount && i <= maxElements; i++)
{
try
{
string val = segment.GetElement(i);
var elementSpec = spec.Elements[i - 1];
int maxLength = elementSpec.MaxLength;
var column = $"{i:00}";
if (maxLength > 0 && val.Length > maxLength)
{
var message =
string.Format(
Resources.ElementTruncatedWarning,
interchangeId,
positionInInterchange,
segment.SegmentId,
i,
maxLength);
Trace.TraceInformation(message);
parsingError.AppendLine(message);
val = val.Substring(0, maxLength);
}
if (elementSpec.Type == ElementDataType.Numeric && elementSpec.ImpliedDecimalPlaces > 0)
{
int intVal;
if (string.IsNullOrWhiteSpace(val))
{
row[column] = null;
}
else if (int.TryParse(val, out intVal))
{
var denominator = (decimal)Math.Pow(10, elementSpec.ImpliedDecimalPlaces);
row[column] = intVal / denominator;
}
else
{
var message =
string.Format(
"Element {2}{3:00} in position {1} of interchange {0} cannot be indexed because '{4}' could not be parsed into an implied decimal with precision {5}.",
interchangeId,
positionInInterchange,
segment.SegmentId,
i,
val,
elementSpec.ImpliedDecimalPlaces);
Trace.TraceInformation(message);
parsingError.AppendLine(message);
row[column] = null;
}
}
else if (elementSpec.Type == ElementDataType.Numeric || elementSpec.Type == ElementDataType.Decimal)
{
decimal decVal;
if (string.IsNullOrWhiteSpace(val))
{
row[column] = null;
}
else if (decimal.TryParse(val, out decVal))
{
row[column] = decVal;
}
else
{
var message =
string.Format(
"Element {2}{3:00} in position {1} of interchange {0} cannot be indexed because '{4}' could not be parsed into a decimal.",
interchangeId,
positionInInterchange,
segment.SegmentId,
i,
val);
Trace.TraceInformation(message);
parsingError.AppendLine(message);
row[column] = null;
}
}
else if (elementSpec.Type == ElementDataType.Date)
{
if (string.IsNullOrWhiteSpace(val))
{
row[column] = null;
}
else
{
DateTime date;
if (val.Length == 8 &&
DateTime.TryParse(
$"{val.Substring(0, 4)}-{val.Substring(4, 2)}-{val.Substring(6, 2)}",
out date))
{
row[column] = date;
}
else
{
var message =
string.Format(
"Element {2}{3:00} in position {1} of interchange {0} cannot be indexed because '{4}' could not be parsed into a date.",
interchangeId,
positionInInterchange,
segment.SegmentId,
i,
val);
Trace.TraceInformation(message);
parsingError.AppendLine(message);
row[column] = null;
}
}
}
else
{
row[column] = val;
}
}
catch (Exception e)
{
var message = string.Format(
"Error parsing '{0}' using spec {1} with {2} elements: {3}",
segment.SegmentString,
spec.SegmentId,
spec.Elements.Count(),
e.Message);
Trace.TraceInformation(message);
parsingError.AppendLine(message);
}
}
if (parsingError.Length > 0)
{
row["ErrorId"] = this.errorRepo.PersistParsingError(
interchangeId,
positionInInterchange,
revisionId,
parsingError.ToString());
}
this.ParsedTables[segment.SegmentId].Rows.Add(row);
}
}
public RepoSegment(string segmentString, char segmentTerminator, char elementSeparator, char componentSeparator)
{
Segment = new DetachedSegment(
new X12DelimiterSet(segmentTerminator, elementSeparator, componentSeparator),
segmentString);
}
protected virtual void SaveSegment(SqlTransaction tran, DetachedSegment segment, int positionInInterchange, T interchangeId, T?functionalGroupId = null, T?transactionSetId = null, T?parentLoopId = null, T?loopId = null, int?revisionId = null, int?previousRevisionId = null, bool deleted = false)
{
if (!revisionId.HasValue || SegmentHasChanged(segment, positionInInterchange, interchangeId, previousRevisionId) || deleted)
{
string segmentSql = string.Format(@"
INSERT INTO [{0}].[Segment] (InterchangeId, FunctionalGroupId, TransactionSetId, ParentLoopId, LoopId, RevisionId, Deleted, PositionInInterchange, SegmentId, Segment)
VALUES ({1}, {2}, {3}, {4}, {5}, isnull({6},0), {7}, {8}, '{9}', '{10}') ",
_schema,
string.Format("'{0}'", interchangeId),
(object)functionalGroupId == null ? "NULL" : string.Format("'{0}'", functionalGroupId),
(object)transactionSetId == null ? "NULL" : string.Format("'{0}'", transactionSetId),
(object)parentLoopId == null ? "NULL" : string.Format("'{0}'", parentLoopId),
(object)loopId == null ? "NULL" : string.Format("'{0}'", loopId),
(object)revisionId == null ? "NULL" : string.Format("'{0}'", revisionId),
deleted ? "1" : "0",
positionInInterchange,
segment.SegmentId.Replace("'", "''"),
segment.SegmentString.Replace("'", "''")).Replace("{", "{{").Replace("}", "}}");
if (tran != null)
{
SqlCommand cmd = new SqlCommand(segmentSql);
cmd.Connection = tran.Connection;
cmd.Transaction = tran;
ExecuteCmd(cmd);
}
else
{
AddSqlToBatch(segmentSql);
}
if (_specs.ContainsKey(segment.SegmentId))
{
StringBuilder parsingError = new StringBuilder();
List <string> fieldNames = new List <string>();
List <string> parameterNames = new List <string>();
var spec = _specs[segment.SegmentId];
int maxElements = spec != null ? spec.Elements.Count : 0;
for (int i = 1; i == 1 || i <= segment.ElementCount; i++)
{
if (i <= maxElements)
{
fieldNames.Add(string.Format("[{0:00}]", i));
}
else
{
string val = segment.GetElement(i);
string message = string.Format("Element {2}{3:00} in position {1} of interchange {0} with value {4} will NOT be indexed because it exceeds the {5} specified number of elements.", interchangeId, positionInInterchange, segment.SegmentId, i, val, maxElements);
Trace.TraceInformation(message);
parsingError.AppendLine(message);
}
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"INSERT INTO [{0}].[{1}] (InterchangeId, PositionInInterchange, TransactionSetId, ParentLoopId, LoopId, RevisionId, Deleted, {2}, ErrorId)
VALUES ({3}, {4}, {5}, {6}, {7}, isnull({8},0), {9}, ",
_schema, segment.SegmentId, string.Join(",", fieldNames),
string.Format("'{0}'", interchangeId),
positionInInterchange,
(object)transactionSetId == null ? "NULL" : string.Format("'{0}'", transactionSetId),
(object)parentLoopId == null ? "NULL" : string.Format("'{0}'", parentLoopId),
(object)loopId == null ? "NULL" : string.Format("'{0}'", loopId),
(object)revisionId == null ? "NULL" : string.Format("'{0}'", revisionId),
deleted ? "1" : "0");
if (segment.ElementCount == 0)
{
sql.AppendFormat("NULL, ");
}
else
{
for (int i = 1; i <= segment.ElementCount && i <= maxElements; i++)
{
string val = segment.GetElement(i);
if (spec != null)
{
var elementSpec = spec.Elements[i - 1];
int maxLength = elementSpec.MaxLength;
if (maxLength > 0 && val.Length > maxLength)
{
string message = string.Format("Element {2}{3:00} in position {1} of interchange {0} will be truncated because {4} exceeds the max length of {5}.", interchangeId, positionInInterchange, segment.SegmentId, i, val, maxLength);
Trace.TraceInformation(message);
parsingError.AppendLine(message);
val = val.Substring(0, maxLength);
}
if (elementSpec.Type == ElementDataTypeEnum.Numeric && elementSpec.ImpliedDecimalPlaces > 0)
{
int intVal = 0;
if (string.IsNullOrWhiteSpace(val))
{
sql.Append("NULL, ");
}
else if (int.TryParse(val, out intVal))
{
decimal denominator = (decimal)Math.Pow(10, elementSpec.ImpliedDecimalPlaces);
sql.AppendFormat("{0}, ", (decimal)intVal / denominator);
}
else
{
string message = string.Format("Element {2}{3:00} in position {1} of interchange {0} cannot be indexed because '{4}' could not be parsed into an implied decimal with precision {5}.", interchangeId, positionInInterchange, segment.SegmentId, i, val, elementSpec.ImpliedDecimalPlaces);
Trace.TraceInformation(message);
parsingError.AppendLine(message);
sql.AppendFormat("NULL, ");
}
}
else if (elementSpec.Type == ElementDataTypeEnum.Numeric || elementSpec.Type == ElementDataTypeEnum.Decimal)
{
decimal decVal = 0;
if (string.IsNullOrWhiteSpace(val))
{
sql.Append("NULL, ");
}
else if (decimal.TryParse(val, out decVal))
{
sql.AppendFormat("{0}, ", val);
}
else
{
string message = string.Format("Element {2}{3:00} in position {1} of interchange {0} cannot be indexed because '{4}' could not be parsed into a decimal.", interchangeId, positionInInterchange, segment.SegmentId, i, val);
Trace.TraceInformation(message);
parsingError.AppendLine(message);
sql.AppendFormat("NULL, ");
}
}
else if (elementSpec.Type == ElementDataTypeEnum.Date)
{
DateTime date = DateTime.MinValue;
if (val.Length == 8 && DateTime.TryParse(string.Format("{0}-{1}-{2}", val.Substring(0, 4), val.Substring(4, 2), val.Substring(6, 2)), out date))
{
sql.AppendFormat("'{0}', ", val.Replace("'", "''"));
}
else
{
string message = string.Format("Element {2}{3:00} in position {1} of interchange {0} cannot be indexed because '{4}' could not be parsed into a date.", interchangeId, positionInInterchange, segment.SegmentId, i, val);
Trace.TraceInformation(message);
parsingError.AppendLine(message);
sql.AppendFormat("NULL, ");
}
}
else
{
sql.AppendFormat("'{0}', ", val.Replace("'", "''"));
}
}
}
}
T? errorId = null;
string errorMessage = parsingError.ToString();
if (!string.IsNullOrWhiteSpace(errorMessage))
{
var errSql = string.Format(@"
INSERT INTO [{0}].ParsingError (InterchangeId, PositionInInterchange, RevisionId, Message)
VALUES (@interchangeId, @positionInInterchange, isnull(@revisionId,0), @message)
SELECT scope_identity()", _schema);
if (typeof(T) == typeof(Guid))
{
errSql = string.Format(@"DECLARE @id uniqueidentifier
SET @id = newid()
INSERT INTO [{0}].ParsingError (Id, InterchangeId, PositionInInterchange, RevisionId, Message)
VALUES (@id,@interchangeId, @positionInInterchange, isnull(@revisionId,0), @message)
SELECT @id", _schema);
}
SqlCommand errCmd = new SqlCommand(errSql.Replace("{", "{{").Replace("}", "}}"));
errCmd.Parameters.AddWithValue("@interchangeId", interchangeId);
errCmd.Parameters.AddWithValue("@positionInInterchange", positionInInterchange);
errCmd.Parameters.AddWithValue("@revisionId", (object)revisionId ?? DBNull.Value);
errCmd.Parameters.AddWithValue("@message", errorMessage);
errorId = ConvertT(ExecuteScalar(errCmd));
}
sql.AppendFormat("{0})", (object)errorId == null ? "NULL" : string.Format("'{0}'", errorId));
if (tran != null)
{
var cmd = new SqlCommand(sql.ToString().Replace("{", "{{").Replace("}", "}}"));
cmd.Connection = tran.Connection;
cmd.Transaction = tran;
ExecuteCmd(cmd);
}
else
{
AddSqlToBatch(sql.ToString().Replace("{", "{{").Replace("}", "}}"));
}
}
}
}
