public void UpdateMarketFile(MarketFileModel model)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp_MarketFileUpdate"))
{
command.AddWithValue("@MarketFileId", model.MarketFileId)
.AddIfNotEmptyOrDbNull("@FileName", model.FileName)
.AddIfNotEmptyOrDbNull("@FileType", model.FileType)
.AddIfNotEmptyOrDbNull("@ProcessStatus", model.ProcessStatus)
.AddIfNotEmptyOrDbNull("@ProcessError", model.ProcessError)
.AddWithValue("@ProcessDate", model.ProcessDate ?? DateTime.Now)
.AddIfNotEmptyOrDbNull("@SenderTranNum", model.SenderTranNum)
.AddWithValue("@DirectionFlag", model.DirectionFlag)
.AddWithValue("@Status", (short)model.Status)
.AddWithValue("@LDCID", model.LdcId ?? 0)
.AddWithValue("@CSPDUNSID", model.CspDunsId ?? 0)
.AddWithValueOrDbNull("@CspDunsTradingPartnerID", model.CspDunsTradingPartnerId)
.AddWithValueOrDbNull("@RefMarketFileId", model.RefMarketFileId)
.AddWithValueOrDbNull("@TransactionCount", model.TransactionCount);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.ExecuteNonQuery();
}
}
public void ExportResult(ExportFileContext context, IMarketFileExportResult result, IExportTransactionHandler handler)
{
var fileName = result.GenerateFileName(context.FileType, context.Extension);
var marketFile = new MarketFileModel
{
DirectionFlag = false,
FileName = fileName,
FileType = context.FileType,
ProcessError = string.Empty,
ProcessStatus = "N",
LdcId = result.LdcId,
CspDunsId = result.CspDunsId,
CspDunsTradingPartnerId = result.CspDunsTradingPartnerId,
};
try
{
var filePath = Path.Combine(context.DirectoryOut, fileName);
var fileInfo = new FileInfo(filePath);
if (fileInfo.Exists)
{
throw new InvalidOperationException();
}
var options = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (var scope = new TransactionScope(TransactionScopeOption.Required, options))
{
marketFile.ProcessDate = DateTime.Now;
marketFile.Status = MarketFileStatusOptions.Inserted;
var marketFileId = marketFileDataAccess
.InsertMarketFile(marketFile);
using (var stream = fileInfo.Open(FileMode.Create, FileAccess.Write, FileShare.None))
using (var writer = new StreamWriter(stream))
{
result.FinalizeDocument(marketFileId);
writer.Write(result.Content);
logger.DebugFormat("Wrote {0} \"{1}\" transaction(s) to file \"{2}\".",
result.HeaderCount, context.FileType, fileName);
}
foreach (var headerKey in result.HeaderKeys)
{
handler.UpdateHeader(headerKey, marketFileId, fileName);
}
logger.InfoFormat("Exported {0} \"{1}\" transaction(s). File Name \"{2}\".",
result.HeaderCount, context.FileType, fileName);
scope.Complete();
}
}
catch (Exception ex)
{
logger.ErrorFormat(ex, "Unknown error occurred while exporting file \"{0}\".", context.FileType);
}
}
public MarketFileModel[] ListInsertedOutboundMarketFiles()
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp_MarketFileList"))
{
command
.AddWithValue("@Status", (int)MarketFileStatusOptions.Inserted)
.AddWithValue("@DirectionFlag", false);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var collection = new List <MarketFileModel>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new MarketFileModel
{
DirectionFlag = false,
MarketFileId = reader.GetInt32("MarketFileId"),
Status = (MarketFileStatusOptions)reader.GetInt16("Status"),
};
reader.TryGetString("FileName", x => item.FileName = x);
reader.TryGetString("FileType", x => item.FileType = x);
reader.TryGetString("ProcessStatus", x => item.ProcessStatus = x);
reader.TryGetDateTime("ProcessDate", x => item.ProcessDate = x);
reader.TryGetString("ProcessError", x => item.ProcessError = x);
reader.TryGetString("SenderTranNum", x => item.SenderTranNum = x);
reader.TryGetInt32("LDCID", x => item.LdcId = x);
reader.TryGetInt32("CSPDUNSID", x => item.CspDunsId = x);
reader.TryGetInt32("RefMarketFileId", x => item.RefMarketFileId = x);
reader.TryGetDateTime("CreateDate", x => item.CreateDate = x);
reader.TryGetInt32("CspDunsTradingPartnerID", x => item.CspDunsTradingPartnerId = x);
reader.TryGetInt32("TransactionCount", x => item.TransactionCount = x);
collection.Add(item);
}
return(collection.ToArray());
}
}
}
public CspDunsPortModel IdentifyCspDunsPort(MarketFileModel marketFile, CspDunsPortModel[] ports)
{
var ldcId = marketFile.LdcId;
var fileType = marketFile.FileType;
var port = ports.FirstOrDefault(x =>
!string.IsNullOrWhiteSpace(x.FileType) && x.FileType.Equals(fileType) &&
(!x.LdcId.HasValue || x.LdcId.Equals(ldcId)));
if (port != null)
{
return(port);
}
return(ports.FirstOrDefault(x =>
string.IsNullOrWhiteSpace(x.FileType) && (!x.LdcId.HasValue || x.LdcId.Equals(ldcId))));
}
public MarketFileModel LoadOutboundMarketFileByName(string fileName)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp_MarketFileLoadByFileName"))
{
command
.AddWithValue("@FileName", fileName)
.AddWithValue("@DirectionFlag", false);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
var item = new MarketFileModel
{
DirectionFlag = false,
MarketFileId = reader.GetInt32("MarketFileId"),
Status = (MarketFileStatusOptions)reader.GetInt16("Status"),
};
reader.TryGetString("FileName", x => item.FileName = x);
reader.TryGetString("FileType", x => item.FileType = x);
reader.TryGetString("ProcessStatus", x => item.ProcessStatus = x);
reader.TryGetDateTime("ProcessDate", x => item.ProcessDate = x);
reader.TryGetString("ProcessError", x => item.ProcessError = x);
reader.TryGetString("SenderTranNum", x => item.SenderTranNum = x);
reader.TryGetInt32("LDCID", x => item.LdcId = x);
reader.TryGetInt32("CSPDUNSID", x => item.CspDunsId = x);
reader.TryGetInt32("RefMarketFileId", x => item.RefMarketFileId = x);
reader.TryGetDateTime("CreateDate", x => item.CreateDate = x);
reader.TryGetInt32("CspDunsTradingPartnerID", x => item.CspDunsTradingPartnerId = x);
reader.TryGetInt32("TransactionCount", x => item.TransactionCount = x);
return(item);
}
}
}
return(null);
}
public void TransmitMarketFile(TransmitFileContext context, MarketFileModel marketFile, CspDunsPortModel port)
{
var fileName = string.Concat(marketFile.FileName, ".pgp");
var encryptedFilePath = Path.Combine(context.DirectoryArchive, "Encrypted", fileName);
var sourceFilePath = Path.Combine(port.DirectoryOut, fileName);
var targetFilePath = Path.Combine(port.DirectoryOut, "Complete", fileName);
var encryptedFile = new FileInfo(encryptedFilePath);
if (encryptedFile.Exists)
{
encryptedFile.CopyTo(sourceFilePath, true);
}
var sourceFile = new FileInfo(sourceFilePath);
try
{
var result = TransmitFile(sourceFile, port);
marketFile.Status = (result.Transmitted)
? MarketFileStatusOptions.Transmitted
: MarketFileStatusOptions.Error;
marketFile.ProcessError = result.Message;
marketFile.ProcessDate = DateTime.Now;
marketDataAccess.UpdateMarketFile(marketFile);
if (result.Transmitted)
{
MoveFile(sourceFile, targetFilePath);
}
}
catch (Exception ex)
{
logger.ErrorFormat(ex, "Unknown error occurred while transmitting file \"{0}\".",
marketFile.FileName);
marketFile.Status = MarketFileStatusOptions.Error;
marketFile.ProcessError = ex.Message;
marketFile.ProcessDate = DateTime.Now;
marketDataAccess.UpdateMarketFile(marketFile);
}
}
public int InsertMarketFile(MarketFileModel model)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand("csp_MarketFileInsert"))
{
SqlParameter keyParameter;
command.AddOutParameter("@MarketFileId", SqlDbType.Int, out keyParameter)
.AddIfNotEmptyOrDbNull("@FileName", model.FileName)
.AddIfNotEmptyOrDbNull("@FileType", model.FileType)
.AddWithValue("@ProcessStatus", model.ProcessStatus)
.AddIfNotEmptyOrDbNull("@ProcessError", model.ProcessError)
.AddWithValue("@ProcessDate", model.ProcessDate ?? DateTime.Now)
.AddIfNotEmptyOrDbNull("@SenderTranNum", model.SenderTranNum)
.AddWithValue("@DirectionFlag", model.DirectionFlag)
.AddWithValue("@Status", (short)model.Status)
.AddWithValue("@LDCID", model.LdcId ?? 0)
.AddWithValue("@CSPDUNSID", model.CspDunsId ?? 0)
.AddWithValueOrDbNull("@CspDunsTradingPartnerID", model.CspDunsTradingPartnerId)
.AddWithValueOrDbNull("@RefMarketFileId", model.RefMarketFileId)
.AddWithValueOrDbNull("@TransactionCount", model.TransactionCount);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.ExecuteNonQuery();
if (keyParameter.Value == null)
{
throw new Exception();
}
return((int)keyParameter.Value);
}
}
public void ImportResult(ImportFileContext context, FileInfo file, IMarketFileParseResult result, IImportTransactionHandler handler)
{
var imported = false;
var marketFile = new MarketFileModel
{
DirectionFlag = true,
FileName = file.Name,
FileType = context.FileType,
ProcessError = string.Empty,
ProcessStatus = "N",
LdcId = 0,
CspDunsId = 0,
};
try
{
var options = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (var scope = new TransactionScope(TransactionScopeOption.Required, options))
{
if (context.ProviderId == 2)
{
marketFile.ProcessStatus = "Y";
marketFile.SenderTranNum = result.InterchangeControlNbr;
marketFile.TransactionCount = result.TransactionAuditCount;
}
marketFile.Status = MarketFileStatusOptions.Imported;
marketFile.ProcessDate = DateTime.Now;
var marketFileId = marketFileDataAccess.InsertMarketFile(marketFile);
foreach (var header in result.Headers)
{
handler.ProcessHeader(header, marketFileId);
}
marketFileDataAccess.InsertAuditRecord(marketFileId,
result.TransactionAuditCount, result.TransactionActualCount);
if (result.TransactionAuditCount != result.TransactionActualCount)
{
logger.ErrorFormat(
"Transaction Count does not match Transaction Audit Count. File Name \"{0}\".", file.Name);
}
logger.InfoFormat("Imported {0} \"{1}\" transaction(s). File Name \"{2}\".",
result.Headers.Length, context.FileType, file.Name);
imported = true;
scope.Complete();
}
var archivePath = Path.Combine(context.DirectoryInArchive, DateTime.Now.ToString("yyyyMM"));
MoveFile(file, archivePath);
}
catch (Exception ex)
{
logger.ErrorFormat(ex, "Unknown error occurred while import file \"{0}\".", file);
if (!imported)
{
MarkFileAsErrored(context, file, ex);
}
MoveFile(file, context.DirectoryInException);
}
}