public Drug GetDrugByRecordNumber(string recordNumber)
{
using (var context = new DCDContext())
{
return(context.Drugs.FirstOrDefault(drug => drug.RecordNumber == recordNumber));
}
}
public static bool AddRelatedCompanies(this DCDContext dc, int companyId, List <CompanyPath> paths)
{
try
{
foreach (CompanyPath path in paths)
{
RelatedCompany relatedCompany =
dc.RelatedCompanies.FirstOrDefault(
c =>
c.Company.RecordId == companyId && c.RelatedCompanyRecordNumber == path.id);
if (relatedCompany == null)
{
relatedCompany = new RelatedCompany();
relatedCompany.CompanyRecordId = companyId;
relatedCompany.RelatedCompanyRecordNumber = path.id;
relatedCompany.RelatedCompanyPath = path.Value;
dc.RelatedCompanies.InsertOnSubmit(relatedCompany);
}
else
{
relatedCompany.RelatedCompanyPath = path.Value;
}
}
dc.SubmitChanges();
return(true);
}
catch (Exception e)
{
return(false);
}
}
public static void CreateRecordLogEntry <RecordLogType>(this DCDContext dc, int recordId, ImportLog importLog, RecordImportResult result, RecordImportOperation operation, string notes, Exception e) where RecordLogType : IDCDRecordImportLog, new()
{
try
{
RecordLogType log = new RecordLogType();
log.RecordId = recordId;
log.ImportLog = dc.ImportLogs.FirstOrDefault(w => w.Id == importLog.Id);
log.Result = result.ToString();
log.Operation = operation.ToString();
log.Notes = notes + (e != null ? $"[{e.ToString()}]" : string.Empty);
log.TimeStamp = DateTime.Now;
log.InsertRecordOnSubmit(dc);
dc.SubmitChanges();
}
catch (Exception ex)
{
DCDImportLogger.Error("Error writing log message to database", ex);
}
if (result == RecordImportResult.Failure)
{
if (e != null)
{
DCDImportLogger.Error("Error processing record " + recordId + ". " + notes, e);
}
else
{
DCDImportLogger.Error("Error processing record " + recordId + ". " + notes);
}
}
else
{
DCDImportLogger.Info("Successfully processed record " + recordId + ".");
}
}
public static void UpdateImportLogEntry(this DCDContext dc, ImportLog log)
{
try
{
var existingLog = dc.ImportLogs.FirstOrDefault(l => l.Id == log.Id);
if (existingLog == null)
{
dc.ImportLogs.InsertOnSubmit(log);
}
else
{
existingLog.FileName = log.FileName;
existingLog.ImportEnd = log.ImportEnd;
existingLog.ImportStart = log.ImportStart;
existingLog.Notes = log.Notes;
existingLog.Result = log.Result;
}
dc.SubmitChanges();
}
catch (Exception ex)
{
DCDImportLogger.Error("Error writing log message to database", ex);
}
}
public void DeleteRecordOnSubmit(DCDContext context)
{
//Remove related companies first
IQueryable <RelatedCompany> relatedRows = context.RelatedCompanies.Where(c => c.CompanyRecordId == this.RecordId);
context.RelatedCompanies.DeleteAllOnSubmit(relatedRows);
context.Companies.DeleteOnSubmit(this);
}
public Deal GetDealByRecordNumber(string recordNumber)
{
Deal dbDeal = null;
using (DCDContext dContext = new DCDContext())
{
dbDeal = dContext.Deals.FirstOrDefault(deal => deal.RecordNumber.Trim() == recordNumber.Trim());
}
return(dbDeal);
}
public ImportLog GetImportLogById(int id)
{
ImportLog log = null;
using (DCDContext dc = new DCDContext())
{
log = dc.ImportLogs.FirstOrDefault(i => i.Id == id);
}
return(log);
}
public Deal GetDealByRecordId(int recordId)
{
Deal dbDeal = null;
using (DCDContext dContext = new DCDContext())
{
dbDeal = dContext.Deals.FirstOrDefault(deal => deal.RecordId == recordId);
}
return(dbDeal);
}
public Company GetCompanyByRecordNumber(string recordNumber)
{
Company company = null;
using (DCDContext dContext = new DCDContext())
{
company = dContext.Companies.FirstOrDefault(deal => deal.RecordNumber.Trim() == recordNumber.Trim());
}
return(company);
}
public List <RelatedCompany> GetAllRelatedCompanies()
{
List <RelatedCompany> lstRelatedComp = null;
using (DCDContext dContext = new DCDContext())
{
lstRelatedComp = dContext.RelatedCompanies.ToList();
}
return(lstRelatedComp);
}
public Drug GetDrugByRecordId(int recordId)
{
Drug drug = null;
using (DCDContext dContext = new DCDContext())
{
drug = dContext.Drugs.FirstOrDefault(drg => drg.RecordId == recordId);
}
return(drug);
}
public Company GetCompanyByRecordId(int recordId)
{
Company company = null;
using (DCDContext dContext = new DCDContext())
{
company = dContext.Companies.FirstOrDefault(deal => deal.RecordId == recordId);
}
return(company);
}
public int GetTotalRecords(ImportLog log)
{
int total = 0;
using (DCDContext dc = new DCDContext())
{
total += dc.DealRecordImportLogs.Count(d => d.ImportLog.Id == log.Id);
total += dc.CompanyRecordImportLogs.Count(c => c.ImportLog.Id == log.Id);
total += dc.DrugRecordImportLogs.Count(d => d.ImportLog.Id == log.Id);
}
return(total);
}
public int GetTotalFailed(ImportLog log)
{
int total = 0;
using (DCDContext dc = new DCDContext())
{
total += dc.DealRecordImportLogs.Count(d => d.ImportLog.Id == log.Id && d.Result == RecordImportResult.Failure.ToString());
total += dc.CompanyRecordImportLogs.Count(c => c.ImportLog.Id == log.Id && c.Result == RecordImportResult.Failure.ToString());
total += dc.DrugRecordImportLogs.Count(d => d.ImportLog.Id == log.Id && d.Result == RecordImportResult.Failure.ToString());
}
return(total);
}
public List <IDCDRecordImportLog> GetRecordsForImport(int importId)
{
List <IDCDRecordImportLog> records = new List <IDCDRecordImportLog>();
using (DCDContext dc = new DCDContext())
{
records.AddRange(dc.DealRecordImportLogs.Where(d => d.ImportId == importId).Select(d => (IDCDRecordImportLog)d));
records.AddRange(dc.CompanyRecordImportLogs.Where(c => c.ImportId == importId).Select(c => (IDCDRecordImportLog)c));
records.AddRange(dc.DrugRecordImportLogs.Where(d => d.ImportId == importId).Select(d => (IDCDRecordImportLog)d));
}
return(records);
}
public static void ThrowCriticalImportError(string error, Exception e, string processingFileName, ImportLog importLog, StringBuilder importLogNotes)
{
try
{
//Log the error in the database and the log
importLogNotes.AppendLine(error);
importLog.ImportEnd = DateTime.Now;
importLog.Result = ImportResult.Failure.ToString();
importLog.Notes = importLogNotes.ToString();
using (DCDContext dc = new DCDContext())
dc.UpdateImportLogEntry(importLog);
}
catch (Exception ex)
{
DCDImportLogger.Error("Error writing log message to database", ex);
}
if (e != null)
{
DCDImportLogger.Error(error, e);
}
else
{
DCDImportLogger.Error(error);
}
string subject = "Error processing Deals, Companies, Drugs import file. ";
StringBuilder sb = new StringBuilder();
sb.AppendLine("There was an error processing the import file: " + processingFileName);
sb.AppendLine();
sb.AppendLine("ERROR:");
sb.AppendLine();
sb.AppendLine(error);
if (e != null)
{
sb.AppendLine(e.Message);
if (e.InnerException != null)
{
sb.AppendLine(e.InnerException.Message);
}
}
if (!string.IsNullOrEmpty(importLogNotes.ToString()))
{
sb.AppendLine(importLogNotes.ToString());
}
SendNotification(subject, sb.ToString());
}
public List <ImportLog> GetImports(DateTime startDate, DateTime endDate)
{
//change the span to be: start 12am to end 11:59pm
DateTime start = startDate.Date;
DateTime end = new DateTime(endDate.Year, endDate.Month, endDate.Day, 23, 59, 59);
List <ImportLog> logs = new List <ImportLog>();
using (DCDContext dc = new DCDContext())
{
logs = dc.ImportLogs.Where(i => i.ImportStart <= end && i.ImportEnd >= start).OrderBy(i => i.ImportStart).ToList();
}
return(logs);
}
public static IDCD GetRecord <T>(this DCDContext dc, int recordId) where T : IDCD
{
if (typeof(T) == typeof(Deal))
{
return(dc.Deals.FirstOrDefault(r => r.RecordId == recordId));
}
else if (typeof(T) == typeof(Informa.Models.DCD.Company))
{
return(dc.Companies.FirstOrDefault(r => r.RecordId == recordId));
}
else if (typeof(T) == typeof(Drug))
{
return(dc.Drugs.FirstOrDefault(r => r.RecordId == recordId));
}
return(null);
}
/// <summary>
/// Returns the associated record number to the record id
/// </summary>
/// <param name="recordId">The id whose associated record number is being searched for.</param>
/// <returns>The corresponding record number to the input record id. If the record id is not found, null is returned.</returns>
public string Get(int recordId)
{
_readWriteLock.EnterUpgradeableReadLock();
try
{
string recordNumber;
// If the recordNumber is not found successfully, set it.
if (!_recordIdToNumberCache.TryGetValue(recordId, out recordNumber))
{
_readWriteLock.EnterWriteLock();
try
{
// Second check is optimistic
if (_recordIdToNumberCache.TryGetValue(recordId, out recordNumber))
{
return(recordNumber);
}
// Otherwise, continue to grab from the database
using (var dcd = new DCDContext())
{
// Get the recordnumber
Company company = dcd.Companies.FirstOrDefault(f => f.RecordId == recordId);
// Return null if the company record isn't found.
if (company == null)
{
return(null);
}
recordNumber = company.RecordNumber;
// Set it in the cache so we don't have to hit the database next time.
_recordIdToNumberCache[recordId] = recordNumber;
}
}
finally
{
_readWriteLock.ExitWriteLock();
}
}
return(recordNumber);
}
finally
{
_readWriteLock.ExitUpgradeableReadLock();
}
}
/// <summary>
/// Replaces all company names within strong elements with their token containing their
/// record ID
///
/// It used to remove the strong element, but now it keeps the strong element.
/// </summary>
/// <param name="html"></param>
/// <param name="companyIdsCsv"></param>
/// <returns></returns>
public static string ReplaceStrongCompanyNamesWithToken(string html, out string companyIdsCsv)
{
var xhtml = XElement.Parse(html);
var strongs = xhtml.Descendants("strong");
var ids = new List <string>();
var elementReplacements = new List <KeyValuePair <XElement, string> >();
using (var dc = new DCDContext())
{
foreach (var strong in strongs)
{
if (strong == null || strong.Parent == null)
{
continue;
}
Company company = dc.Companies.FirstOrDefault(c => c.Title == strong.Value.Trim());
string compID = string.Empty;
if (company != null)
{
compID = company.RecordNumber;
}
//var id = dc.GetCompanyId(strong.Value.Trim());
if (string.IsNullOrEmpty(compID) == false)
{
elementReplacements.Add(new KeyValuePair <XElement, string>(strong, String.Format(DCDConstants.CompanyTokenFormat, compID, strong.Value.Trim())));
ids.Add(compID);
}
}
}
foreach (var replacement in elementReplacements)
{
if (replacement.Key == null || replacement.Key.Parent == null)
{
continue;
}
//replacement.Key.ReplaceWith(replacement.Value);
replacement.Key.Value = replacement.Value;
}
companyIdsCsv = ids.Count > 0 ? ids.Aggregate((total, next) => total + "," + next) : "";
return(xhtml.ToString());
}
public static void CreateImportLogEntry(this DCDContext dc, DateTime start, string fileName, out ImportLog log)
{
try
{
log = new ImportLog();
log.ImportStart = start;
log.ImportEnd = start;
log.Result = ImportResult.InProgress.ToString();
log.FileName = fileName;
dc.ImportLogs.InsertOnSubmit(log);
dc.SubmitChanges();
//return log;
}
catch (Exception ex)
{
DCDImportLogger.Error("Error writing log message to database", ex);
log = null;
}
//return null;
}
public static Models.DCD.Company GetCompanyByRecordNumber(string recordNumber)
{
if (!Sitecore.Configuration.Settings.ConnectionStringExists("dcd"))
{
return(null);
}
try
{
Models.DCD.Company company = null;
using (DCDContext dContext = new DCDContext(Sitecore.Configuration.Settings.GetConnectionString("dcd")))
{
company = dContext.Companies.FirstOrDefault(deal => deal.RecordNumber.Trim() == recordNumber.Trim());
}
return(company);
}
catch (Exception exc)
{
Log.Error("Error Trying to Get Company From DCD Database: " + exc.Message, "SearchCompanyUtil.GetCompanyByRecordNumber");
}
return(null);
}
public void InsertRecordOnSubmit(DCDContext context)
{
context.Deals.InsertOnSubmit(this);
}
protected void Page_Load(object sender, EventArgs e)
{
startDate = Request[startDateParam];
endDate = Request[endDateParam];
sortType = Request[sortParam];
sortDirection = Request[sortDirectionParam];
report = Request[reportParam];
backUrl = Request[backParam];
DCDContext dc = new DCDContext();
if (!IsPostBack)
{
DCDManager dcdMgr = new DCDManager();
//if there is a report parameter, show just that import
if (!string.IsNullOrEmpty(report))
{
ImportLog import = dcdMgr.GetImportLogById(int.Parse(report));
if (import != null)
{
litSingleImportTitle.Text = "Import: " + import.Id + ", " + import.FileName + "(" + import.ImportStart.ToShortDateString() + " " + import.ImportStart.ToShortTimeString() + ")";
phImports.Visible = false;
phSingleImport.Visible = true;
if (!string.IsNullOrEmpty(backUrl))
{
hlBack.NavigateUrl = backUrl;
}
else
{
hlBack.Visible = false;
}
DataTable results = new DataTable();
results.Columns.Add("Type");
results.Columns.Add("RecordId");
results.Columns.Add("RecordNumber");
results.Columns["RecordId"].DataType = typeof(int);
results.Columns.Add("Time");
results.Columns.Add("Operation");
results.Columns.Add("Result");
results.Columns.Add("Notes");
results.Columns["Time"].DataType = typeof(DateTime);
List <IDCDRecordImportLog> records = dcdMgr.GetRecordsForImport(int.Parse(report));
foreach (IDCDRecordImportLog log in records)
{
DataRow row = results.NewRow();
if (log is DealRecordImportLog)
{
row["Type"] = "Deal";
Deal record = dcdMgr.GetDealByRecordId(log.RecordId.Value);
if (record != null)
{
row["RecordNumber"] = record.RecordNumber;
}
}
else if (log is CompanyRecordImportLog)
{
row["Type"] = "Company";
Company record = dcdMgr.GetCompanyByRecordId(log.RecordId.Value);
if (record != null)
{
row["RecordNumber"] = record.RecordNumber;
}
}
else if (log is DrugRecordImportLog)
{
row["Type"] = "Drug";
Drug record = dcdMgr.GetDrugByRecordId(log.RecordId.Value);
if (record != null)
{
row["RecordNumber"] = record.RecordNumber;
}
}
row["RecordId"] = log.RecordId;
row["Time"] = log.TimeStamp.ToShortDateString() + " " + log.TimeStamp.ToShortTimeString();
row["Operation"] = log.Operation;
row["Result"] = log.Result;
row["Notes"] = log.Notes;
results.Rows.Add(row);
}
//set default sort
if (string.IsNullOrEmpty(sortType))
{
sortType = "Time";
}
if (string.IsNullOrEmpty(sortDirection))
{
sortDirection = "desc";
}
DataView view = new DataView(results);
view.Sort = sortType + " " + sortDirection;
dgSingleImport.ShowHeader = true;
dgSingleImport.DataSource = view;
dgSingleImport.DataBind();
}
}
else
{
if (string.IsNullOrEmpty(startDate) || string.IsNullOrEmpty(endDate))
{
txtDateEnd.Text = DateTime.Today.ToShortDateString();
txtDateStart.Text = DateTime.Today.Subtract(new TimeSpan(7, 0, 0, 0)).ToShortDateString();
return;
}
else
{
txtDateStart.Text = startDate;
txtDateEnd.Text = endDate;
}
DateTime start;
DateTime end;
if (!DateTime.TryParse(startDate, out start))
{
dgResults.Visible = false;
return;
}
if (!DateTime.TryParse(endDate, out end))
{
dgResults.Visible = false;
return;
}
List <ImportLog> logs = dcdMgr.GetImports(start, end);
DataTable results = new DataTable();
results.Columns.Add("Id");
results.Columns["Id"].DataType = typeof(int);
results.Columns.Add("Start Date");
results.Columns.Add("End Date");
results.Columns.Add("File Name");
results.Columns.Add("Result");
results.Columns.Add("Total");
results.Columns.Add("Succeeded");
results.Columns.Add("Skipped");
results.Columns.Add("Failed");
results.Columns["Start Date"].DataType = typeof(DateTime);
results.Columns["End Date"].DataType = typeof(DateTime);
results.Columns["Total"].DataType = typeof(int);
results.Columns["Succeeded"].DataType = typeof(int);
results.Columns["Skipped"].DataType = typeof(int);
results.Columns["Failed"].DataType = typeof(int);
foreach (ImportLog log in logs)
{
DataRow row = results.NewRow();
row["Id"] = log.Id;
row["Start Date"] = log.ImportStart.ToShortDateString() + " " + log.ImportStart.ToShortTimeString();
row["End Date"] = log.ImportEnd.ToShortDateString() + " " + log.ImportEnd.ToShortTimeString();
row["File Name"] = log.FileName;
row["Result"] = log.Result;
row["Total"] = dcdMgr.GetTotalRecords(log);
row["Succeeded"] = dcdMgr.GetTotalSuccess(log);
row["Skipped"] = dcdMgr.GetTotalSkipped(log);
row["Failed"] = dcdMgr.GetTotalFailed(log);
results.Rows.Add(row);
}
//set default sort
if (string.IsNullOrEmpty(sortType))
{
sortType = "Start Date";
}
if (string.IsNullOrEmpty(sortDirection))
{
sortDirection = "desc";
}
DataView view = new DataView(results);
view.Sort = sortType + " " + sortDirection;
dgResults.ShowHeader = true;
dgResults.DataSource = view;
dgResults.DataBind();
}
}
}
public static bool ProcessRecord <RecordType, RecordLogType>(this DCDContext dc, Record record, ImportLog importLog)
where RecordType : IDCD, new()
where RecordLogType : IDCDRecordImportLog, new()
{
//First see if the record already exists
IDCD entry = dc.GetRecord <RecordType>(record.Identification.RecordId);
//Process delete
if (record.Command == CommandType.Delete)
{
if (entry == null)
{
dc.CreateRecordLogEntry <RecordLogType>(record.Identification.RecordId, importLog, RecordImportResult.Skipped, RecordImportOperation.Delete, "Cannot delete. Record doesn't exist.");
return(true);
}
else
{
try
{
entry.DeleteRecordOnSubmit(dc);
dc.SubmitChanges();
}
catch (Exception e)
{
dc.CreateRecordLogEntry <RecordLogType>(record.Identification.RecordId, importLog, RecordImportResult.Failure, RecordImportOperation.Delete, "Error deleting record.", e);
return(false);
}
dc.CreateRecordLogEntry <RecordLogType>(record.Identification.RecordId, importLog, RecordImportResult.Success, RecordImportOperation.Delete, string.Empty);
return(true);
}
}
//Upsert
else
{
RecordImportOperation op = RecordImportOperation.Insert;
IDCD processingRecord;
try
{
//If the record already exists
if (entry != null)
{
op = RecordImportOperation.Update;
//Only records that are newer than the records in the feed will be skipped
if (entry.LastModified > record.Identification.LastModified)
{
dc.CreateRecordLogEntry <RecordLogType>(record.Identification.RecordId, importLog, RecordImportResult.Skipped, op, "Existing record has a newer Last Modified Date.");
return(true);
}
else if (record.Content == null || string.IsNullOrEmpty(record.Content.InnerXml))
{
dc.CreateRecordLogEntry <RecordLogType>(record.Identification.RecordId, importLog, RecordImportResult.Skipped, op, "Record does not have any content.");
return(true);
}
else
{
entry.RecordNumber = record.Identification.RecordNumber;
entry.Title = XMLFileUtilities.RemoveBrackets(record.Identification.Title);
entry.LastModified = record.Identification.LastModified;
entry.Created = record.Identification.Created;
entry.Published = record.Identification.Created;
if (!record.Identification.Published.ToString().Equals(DateTime.MinValue.ToString()))
{
entry.Published = record.Identification.Published;
}
entry.Content = record.Content.InnerXml;
}
}
else
{
op = RecordImportOperation.Insert;
processingRecord = new RecordType();
processingRecord.RecordId = record.Identification.RecordId;
processingRecord.RecordNumber = record.Identification.RecordNumber;
processingRecord.Title = XMLFileUtilities.RemoveBrackets(record.Identification.Title);
processingRecord.LastModified = record.Identification.LastModified;
processingRecord.Created = record.Identification.Created;
processingRecord.Published = record.Identification.Created;
if (!record.Identification.Published.ToString().Equals(DateTime.MinValue.ToString()))
{
processingRecord.Published = record.Identification.Published;
}
processingRecord.Content = record.Content.InnerXml;
processingRecord.InsertRecordOnSubmit(dc);
}
dc.SubmitChanges();
}
catch (Exception e)
{
dc.CreateRecordLogEntry <RecordLogType>(record.Identification.RecordId, importLog, RecordImportResult.Failure, op, "Error inserting/updating record.", e);
return(false);
}
dc.CreateRecordLogEntry <RecordLogType>(record.Identification.RecordId, importLog, RecordImportResult.Success, op, string.Empty);
return(true);
}
}
public static void CreateRecordLogEntry <RecordLogType>(this DCDContext dc, int recordId, ImportLog importLog, RecordImportResult result, RecordImportOperation operation, string notes) where RecordLogType : IDCDRecordImportLog, new()
{
dc.CreateRecordLogEntry <RecordLogType>(recordId, importLog, result, operation, notes, null);
}
public void InsertRecordOnSubmit(DCDContext context)
{
context.Companies.InsertOnSubmit(this);
}
public void DeleteRecordOnSubmit(DCDContext context)
{
context.Drugs.DeleteOnSubmit(this);
}
public void DeleteRecordOnSubmit(DCDContext context)
{
context.CompanyRecordImportLogs.DeleteOnSubmit(this);
}
public void InsertRecordOnSubmit(DCDContext context)
{
context.DrugRecordImportLogs.InsertOnSubmit(this);
}
