//TODO: Add try catch
public override void Run()
{
try
{
using (CurrentDbContext = new AryaDbDataContext(CurrentProjectId, ImportRequestedBy))
{
List <DerivedAttributeInterchangeRecord> allData = ImportData.DerivedAttributes;
//read all the data into a list<T>, change this as its not very efficient and scalable.
//CsvConfiguration conf = GetCurrentConfiguration();
////char delimiterChar = (char)FieldDelimiter.GetDisplayTextAndDbValue().DbValue;
//using (var csvReader = new CsvReader(File.OpenText(InputFilePath), conf))
//{
// allData = csvReader.GetRecordsWithNulls<DerivedAttributeInterchangeRecord>().Distinct(new DerivedAttributeInterchangeRecordComparer()).ToList();
//}
var invalidRecords = allData.GetInvalidRecords();
var derivedAttributeInterchangeRecords = invalidRecords as IList <DerivedAttributeInterchangeRecord> ?? invalidRecords.ToList();
derivedAttributeInterchangeRecords.ToList().ForEach(ir => _warnings.Add(new WorkerWarning
{
LineData = ir.ToString(),
ErrorMessage = Properties.Resources.RequiredValueNullWarningMessage
}));
var validImportRecords = allData.Except(derivedAttributeInterchangeRecords.ToList()).ToList();
int insertCount = 0;
int updateCount = 0;
int ignoreCount = 0;
// load a dictionary with taxonomy string / id pairs (taken from TaxonomyImport)
var taxDict =
CurrentDbContext.ExecuteQuery <TaxonomyPathAndId>(
@"SELECT TaxonomyPath, TaxonomyID
FROM V_Taxonomy
WHERE TaxonomyPath <> ''
AND ProjectId = {0}", CurrentProjectId).
ToDictionary(key => key.TaxonomyPath, value => value.TaxonomyId, StringComparer.OrdinalIgnoreCase);
// iterate through the input records.
foreach (var csvRecord in validImportRecords)
{
var currentRecord = csvRecord;
// check for AttributeName - pull Id
var attIds = from a in CurrentDbContext.Attributes
where a.AttributeName == currentRecord.DerivedAttributeName && a.AttributeType == AttributeTypeEnum.Derived.ToString() // AttributeTypeEnum.Derived.ToString()
select a.ID;
if (!attIds.Any())
{
_warnings.Add(new WorkerWarning()
{
LineData = currentRecord.ToString(), ErrorMessage = Properties.Resources.AttributeDoesNotExistWarningMessage
});
continue;
}
if (taxDict.ContainsKey(currentRecord.TaxonomyPath))
{
Guid taxId = taxDict[currentRecord.TaxonomyPath];
var dAtt = from d in CurrentDbContext.DerivedAttributes
where d.TaxonomyID == taxId && d.AttributeID == attIds.First()
select d;
// if derived attribute exists, update it, otherwise insert a new one.
if (dAtt.Any())
{
var updatedAttribute = dAtt.First();
if (SameValue(updatedAttribute, currentRecord))
{
ignoreCount++;
continue;
}
updatedAttribute.Expression = csvRecord.DerivedAttributeExpression.Trim();
updatedAttribute.MaxResultLength = csvRecord.MaxResultLength;
updateCount++;
}
else
{
var newAttribute = new DerivedAttribute
{
ID = Guid.NewGuid(),
TaxonomyID = taxId,
AttributeID = attIds.First(),
Expression = csvRecord.DerivedAttributeExpression,
MaxResultLength = csvRecord.MaxResultLength
};
CurrentDbContext.DerivedAttributes.InsertOnSubmit(newAttribute);
insertCount++;
}
}
else//its a global derived attribute record
{
var existingGlobalDerivedAttribute =
CurrentDbContext.DerivedAttributes.FirstOrDefault(da => da.AttributeID == attIds.First() && da.TaxonomyID == null);
// if derived attribute exists, update it, otherwise insert a new one.
if (existingGlobalDerivedAttribute != null)
{
// var updatedAttribute = existingGlobalDerivedAttribute.First();
if (SameValue(existingGlobalDerivedAttribute, currentRecord))
{
ignoreCount++;
continue;
}
existingGlobalDerivedAttribute.Expression = currentRecord.DerivedAttributeExpression.Trim();
existingGlobalDerivedAttribute.MaxResultLength = currentRecord.MaxResultLength;
updateCount++;
}
else
{
var newAttribute = new DerivedAttribute
{
ID = Guid.NewGuid(),
TaxonomyID = null,
AttributeID = attIds.First(),
Expression = csvRecord.DerivedAttributeExpression,
MaxResultLength = csvRecord.MaxResultLength
};
CurrentDbContext.DerivedAttributes.InsertOnSubmit(newAttribute);
insertCount++;
}
}
}
SaveDataChanges();
ProcessSummaryReport(insertCount, updateCount, ignoreCount);
}
}
catch (IndexOutOfRangeException ex)
{
var newException = new Exception(Properties.Resources.InvalidRowInInputFileMessage, ex);
Summary.SetError(newException);
}
catch (Exception ex)
{
Summary.SetError(ex);//.SummaryError = _derivedAttributeImportWorkerError;
}
}
public override void Run()
{
//State = WorkerState.Working;
try
{
//initialize the context
using (CurrentDbContext = new AryaDbDataContext(CurrentProjectId, ImportRequestedBy))
{
var imageMgr = new ImageManager(CurrentDbContext, CurrentProjectId);
List <ListOfValuesInterchangeRecord> allData = ImportData.ListOfValues;
//read all the data into a list<T>, change this as its not very efficient and scalable.
var invalidRecords = allData.GetInvalidRecords();
var listOfValuesInterchangeRecords = invalidRecords as IList <ListOfValuesInterchangeRecord> ?? invalidRecords.ToList();
listOfValuesInterchangeRecords.ToList().ForEach(ir => _warnings.Add(new WorkerWarning
{
LineData = ir.ToString(),
ErrorMessage = Properties.Resources.RequiredValueNullWarningMessage
}));
var validImportRecords = allData.Except(listOfValuesInterchangeRecords.ToList()).ToList();
var newValueCount = 0;
int ignoredCount = 0;
int updatedCount = 0;
// load a dictionary with taxonomy string / id pairs (taken from TaxonomyImport)
var taxDict =
CurrentDbContext.ExecuteQuery <TaxonomyPathAndId>(@"SELECT TaxonomyPath, TaxonomyId
FROM V_Taxonomy
WHERE TaxonomyPath <> ''
AND ProjectId = {0}", CurrentProjectId).ToDictionary(
key => key.TaxonomyPath, value => value.TaxonomyId,
StringComparer.OrdinalIgnoreCase);
// load attribute and schema dictionaries
var attDict =
CurrentDbContext.Attributes.Where(
a => a.AttributeType == AttributeTypeEnum.Sku.ToString())
.Select(a => a)
.ToDictionary(key => key.AttributeName, value => value);
var schDict = new DoubleKeyDictionary <Guid, Guid, SchemaInfo>();
CurrentDbContext.SchemaInfos.ForEach(si => schDict.Add(si.TaxonomyID, si.AttributeID, si));
// iterate through the input records.
foreach (var csvRecord in validImportRecords)
{
var currentRecord = csvRecord;
// check for taxonomy - if it doesn't exist, give up.
if (!taxDict.ContainsKey(currentRecord.TaxonomyPath))
{
_warnings.Add(new WorkerWarning()
{
LineData = currentRecord.ToString(), ErrorMessage = Properties.Resources.TaxonomyDoesNotExistsWarningMessage
});
continue;
}
var taxId = taxDict[currentRecord.TaxonomyPath];
var taxonomy = CurrentDbContext.TaxonomyInfos.First(ti => ti.ID == taxId);
// if attribute exists, get it, otherwise give up.
if (!attDict.ContainsKey(currentRecord.AttributeName))
{
_warnings.Add(new WorkerWarning()
{
LineData = currentRecord.ToString(), ErrorMessage = Properties.Resources.AttributeDoesNotExistWarningMessage
});
continue;
}
var att = attDict[currentRecord.AttributeName];
// if schema info exists, get it, otherwise create both it and schema data
SchemaInfo sch;
if (schDict.ContainsKeys(taxId, att.ID))
{
sch = schDict[taxId, att.ID];
}
else
{
sch = new SchemaInfo(CurrentDbContext)
{
TaxonomyID = taxId,
Attribute = att,
SchemaDatas =
{
new SchemaData(CurrentDbContext)
{
InSchema = true, NavigationOrder = 0, DisplayOrder = 0
}
}
};
att.SchemaInfos.Add(sch);
schDict.Add(taxId, att.ID, sch);
}
var lov = sch.ListOfValues.FirstOrDefault(v => v.Value.ToLower() == currentRecord.Lov.ToLower() && v.Active);
string enrichmentImageGuid = null;
int displayOrder;
if (lov != null || (lov == null && CurrentImportOptions.HasFlag(ImportOptions.CreateMissingLOVs)))
{
// if image url exists try to upload the image - "try" is used in case the url is badly formed.
// string enrichmentImage = null;
var success = false;
if (currentRecord.EnrichmentImage != null)
{
//Success is for valid uri. If the image name only exist not the real file it will be false and added as warning
success = imageMgr.UploadImage(currentRecord.EnrichmentImage);
enrichmentImageGuid = imageMgr.RemoteImageGuid;
var newSku = imageMgr.ImageSku;
newSku.EntityInfos.Add(new EntityInfo(CurrentDbContext)
{
EntityDatas =
{
new EntityData(CurrentDbContext)
{
Attribute =
Attribute.GetAttributeFromName(CurrentDbContext,
Framework.Properties.Resources.LovIdAttributeName, true, AttributeTypeEnum.Sku, false),
Value = imageMgr.RemoteImageGuid
}
}
});
newSku.EntityInfos.Add(new EntityInfo(CurrentDbContext)
{
EntityDatas =
{
new EntityData(CurrentDbContext)
{
Attribute =
Attribute.GetAttributeFromName(CurrentDbContext,
Framework.Properties.Resources.AttributeIdAttributeName,
true, AttributeTypeEnum.Sku, false),
Value = sch.AttributeID.ToString()
}
}
});
newSku.EntityInfos.Add(new EntityInfo(CurrentDbContext)
{
EntityDatas =
{
new EntityData(CurrentDbContext)
{
Attribute =
Attribute.GetAttributeFromName(CurrentDbContext, Framework.Properties.Resources.TaxonomyIdAttributeName,
true, AttributeTypeEnum.Sku, false),
Value = sch.TaxonomyID.ToString()
}
}
});
taxonomy.SkuInfos.Add(new SkuInfo(CurrentDbContext)
{
Sku = newSku
});
if (!success)
{
_warnings.Add(new WorkerWarning
{
LineData = currentRecord.ToString(),
ErrorMessage = Properties.Resources.EnrichmentImageFileNotPresentWarningMessage
});
}
}
if (!string.IsNullOrEmpty(currentRecord.DisplayOrder) && !int.TryParse(currentRecord.DisplayOrder, out displayOrder))
{
_warnings.Add(new WorkerWarning()
{
LineData = currentRecord.ToString(), ErrorMessage = Properties.Resources.DisplayOrderNotValidNumberWarningMessage
});
continue;
}
}
// if specific value record exists in this schema, get it, otherwise create it.
//var lov = sch.ListOfValues.FirstOrDefault(v => v.Value.ToLower() == currentRecord.Lov.ToLower() && v.Active);
if (lov == null)
{
if (CurrentImportOptions.HasFlag(ImportOptions.CreateMissingLOVs))
{
sch.ListOfValues.Add(entity: new ListOfValue(CurrentDbContext)
{
Value = currentRecord.Lov,
EnrichmentImage = enrichmentImageGuid,
EnrichmentCopy = currentRecord.EnrichmentCopy,
DisplayOrder = int.TryParse(currentRecord.DisplayOrder, out displayOrder) ? displayOrder : (int?)null,
CreatedOn = DateTime.Now,
CreatedBy = ImportRequestedBy
});
newValueCount++;
}
else
{
_warnings.Add(new WorkerWarning
{
LineData = currentRecord.ToString(),
ErrorMessage = Properties.Resources.CreateLovFlagOffWarningMessage
});
ProcessSummaryReport(0);
}
}
else
{
if (lov.Value == currentRecord.Lov && lov.EnrichmentCopy == currentRecord.EnrichmentCopy &&
lov.EnrichmentImage == currentRecord.EnrichmentImage && lov.DisplayOrder.ToString() == currentRecord.DisplayOrder)
{
ignoredCount++;
continue;
}
var updatedFlag = false;
if (!string.IsNullOrEmpty(enrichmentImageGuid))
{
lov.EnrichmentImage = enrichmentImageGuid;
updatedFlag = true;
}
if (!string.IsNullOrEmpty(currentRecord.EnrichmentCopy))
{
lov.EnrichmentCopy = currentRecord.EnrichmentCopy;
updatedFlag = true;
}
if (!string.IsNullOrEmpty(currentRecord.DisplayOrder))
{
lov.DisplayOrder = int.TryParse(currentRecord.DisplayOrder, out displayOrder)
? displayOrder
: (int?)null;
updatedFlag = true;
}
if (updatedFlag)
{
lov.CreatedBy = ImportRequestedBy;
updatedCount++;
lov.CreatedOn = DateTime.Now;
}
else
{
ignoredCount++;
}
}
}
SaveDataChanges();
ProcessSummaryReport(newValueCount, updatedCount, ignoredCount);
}
}
catch (IndexOutOfRangeException ex)
{
var newException = new Exception(Properties.Resources.InvalidRowInInputFileMessage, ex);
Summary.SetError(newException);
}
catch (Exception ex)
{
Summary.SetError(ex);
}
}
public override void Run()
{
//State = WorkerState.Working;
try
{
using (
CurrentDbContext = new AryaDbDataContext(CurrentProjectId, ImportRequestedBy))
{
_currentImageManager = new ImageManager(CurrentDbContext, CurrentProjectId);
List <SchemaMetaDataInterchangeRecord> allData;
allData = ImportData.SchemaMetaDatas;
//read all the values into a list<T>, change this as its not very efficient and scalable.
//CsvConfiguration conf = GetCurrentConfiguration();
////char delimiterChar = (char)FieldDelimiter.GetDisplayTextAndDbValue().DbValue;
//using (var csvReader = new CsvReader(File.OpenText(InputFilePath), conf))
//{
// allData =
// csvReader.GetRecordsWithNulls<SchemaMetaDataInterchangeRecord>().ToList();
//}
var invalidRecords = allData.GetInvalidRecords();
var schemaMetaDataInterchangeRecords = invalidRecords as IList <SchemaMetaDataInterchangeRecord> ?? invalidRecords.ToList();
schemaMetaDataInterchangeRecords.ToList().ForEach(ir => _warnings.Add(new WorkerWarning
{
LineData = ir.ToString(),
ErrorMessage = Properties.Resources.RequiredValueNullWarningMessage
}));
var validImportRecords = allData.Except(schemaMetaDataInterchangeRecords.ToList()).ToList();
validImportRecords =
validImportRecords.Distinct(new SchemaMetaDataInterchangeRecordComparer()).ToList();
// Take the enrichment images out of the list
var enrichmentImageRecords =
validImportRecords.Where(ad => ad.SchemaMetaAttributeName.ToLower() == Resources.SchemaEnrichmentImageAttributeName.ToLower()).ToList();
ProcessEnrichmentImage(enrichmentImageRecords);
validImportRecords = validImportRecords.Where(ad => !enrichmentImageRecords.Contains(ad)).ToList();
//adding list with updated enrichment guid to the main list
validImportRecords.AddRange(enrichmentImageRecords);
var sqlTempTableHelper = new SqlHelper(CurrentInterchangeRecordType);
var tempTableName = TempTablePrefix + Guid.NewGuid();
var warningTableName = tempTableName + "_warning";
var createTempTableScript = sqlTempTableHelper.CreateTableScript(tempTableName, "tempdb");
var deleteTempTableScript = sqlTempTableHelper.DeleteTableScript(tempTableName, "tempdb");
//create the temp table.
CurrentDbContext.ExecuteCommand(createTempTableScript);
//bulk insert data into tempdb
CurrentDbContext.BulkInsertAll(validImportRecords, tempTableName, "tempdb");
var schemaAttributeMetaQuery = @"DECLARE @UserID AS UNIQUEIDENTIFIER
DECLARE @ProjectID AS UNIQUEIDENTIFIER
DECLARE @ResultText AS VARCHAR(2000)
DECLARE @DefaultImportUtilRemarkID AS UNIQUEIDENTIFIER
DECLARE @UpdateCount AS int
SET @DefaultImportUtilRemarkID = '" + CurrentRemarkId + @"'
IF OBJECT_ID('tempdb..#SchemaMetaAttributeAllData') IS NOT NULL
DROP TABLE #SchemaMetaAttributeAllData
CREATE TABLE #SchemaMetaAttributeAllData
(
TaxonomyPath varchar(4000),
AttributeName varchar(255),
MetaAttributeName varchar(255),
MetaValue varchar(4000),
TaxonomyId varchar(255),
AttrributeId varchar(255),
MetaAttributeId varchar(255),
SchemaInfoId varchar(255),
DbValue varchar(4000),
SchemaMetaInfoId varchar(255),
SchemaMetaDataId varchar(255)
);
IF OBJECT_ID('[tempdb]..[" + warningTableName + @"]') IS NOT NULL
DROP TABLE [tempdb]..[" + warningTableName + @"]
SET @UserID = '"
+ ImportRequestedBy + @"'
SET @ProjectID = '"
+ CurrentProjectId + @"'
SET @ResultText = ''
INSERT INTO #SchemaMetaAttributeAllData(TaxonomyPath,AttributeName,MetaAttributeName,MetaValue,TaxonomyId,AttrributeId,MetaAttributeId,SchemaInfoId)
SELECT td.TaxonomyPath, td.AttributeName, td.MetaAttributeName, td.MetaAttributeValue, tx.TaxonomyId, att.ID AS AttrributeId, metaAtt.ID AS MetaAttributeId,si.ID AS SchemaInfoId
FROM
[tempdb]..[" + tempTableName + @"] td LEFT OUTER JOIN V_Taxonomy tx
ON td.TaxonomyPath = tx.TaxonomyPath
LEFT OUTER JOIN Attribute att
ON att.AttributeName = td.AttributeName and (att.AttributeType = 'Sku' OR att.AttributeType = 'Global' OR att.AttributeType = 'Derived')
LEFT OUTER JOIN SchemaInfo si
ON si.TaxonomyID = tx.TaxonomyId AND si.AttributeID = att.ID
LEFT OUTER JOIN Attribute metaAtt
ON metaAtt.AttributeName = td.MetaAttributeName and metaAtt.AttributeType = 'SchemaMeta'
-- Select * from #SchemaMetaAttributeAllData
SELECT sad.TaxonomyPath, sad.AttributeName, sad.MetaAttributeName, sad.MetaValue, 'TaxonomyPath/AttributeName/MetaAttributeName/SchemaInfo Does Not Exist' AS WarningMessage
INTO [tempdb]..[" + warningTableName + @"]
FROM #SchemaMetaAttributeAllData sad
WHERE sad.AttrributeId IS NULL OR sad.TaxonomyId IS NULL OR sad.MetaAttributeId IS NULL OR sad.SchemaInfoId IS NULL
DELETE sad
FROM #SchemaMetaAttributeAllData sad
INNER JOIN [tempdb]..[" + warningTableName + @"] wn
ON sad.TaxonomyPath = wn.TaxonomyPath AND sad.AttributeName = wn.AttributeName AND sad.MetaAttributeName = wn.MetaAttributeName
UPDATE #SchemaMetaAttributeAllData
SET DbValue = vsmd.SchemaMetaValue, SchemaMetaInfoId = vsmd.SchemaMetaID, SchemaMetaDataId = vsmd.SchemaMetaDataID
FROM #SchemaMetaAttributeAllData sad INNER JOIN V_SchemaMetaData vsmd
ON vsmd.SchemaID = sad.SchemaInfoId AND vsmd.SchemaMetaAttributeID = sad.MetaAttributeId
--SELECT * FROM #SchemaMetaAttributeAllData
DELETE sad FROM #SchemaMetaAttributeAllData sad
WHERE sad.MetaValue = sad.DbValue
SET @ResultText += '"
+ Properties.Resources.IgnoredRecordCountIdentifierText
+ @"' + '=' + CAST(@@ROWCOUNT AS VARCHAR(500)) + ';'
-- SELECT * FROM #SchemaMetaAttributeAllData
UPDATE SchemaMetaData
SET Active = 0, DeletedBy =@UserID, DeletedOn = GETDATE(), DeletedRemark = @DefaultImportUtilRemarkID
FROM SchemaMetaData smd INNER JOIN #SchemaMetaAttributeAllData sad ON sad.SchemaMetaDataId = smd.ID
WHERE smd.Active = 1
SET @UpdateCount = @@ROWCOUNT
UPDATE #SchemaMetaAttributeAllData
SET SchemaMetaInfoId = smi.ID
FROM #SchemaMetaAttributeAllData sad INNER JOIN SchemaMetaInfo smi
ON sad.SchemaInfoId = smi.SchemaID and sad.MetaAttributeId = smi.MetaAttributeID
INSERT INTO SchemaMetaInfo(ID, SchemaID, MetaAttributeID)
SELECT NEWID(), sad.SchemaInfoId, sad.MetaAttributeId
FROM #SchemaMetaAttributeAllData sad WHERE sad.SchemaMetaInfoId IS NULL
UPDATE #SchemaMetaAttributeAllData
SET SchemaMetaInfoId = smi.ID
FROM #SchemaMetaAttributeAllData sad INNER JOIN SchemaMetaInfo smi
ON sad.SchemaInfoId = smi.SchemaID and sad.MetaAttributeId = smi.MetaAttributeID
INSERT INTO SchemaMetaData(ID,MetaID, Value,CreatedOn,CreatedBy,CreatedRemark,Active)
SELECT NEWID(), sad.SchemaMetaInfoId, sad.MetaValue, GETDATE(), @UserID,@DefaultImportUtilRemarkID,1
FROM #SchemaMetaAttributeAllData sad
SET @ResultText += '"
+ Properties.Resources.NewRecordCountIdentifierText
+
@"'+ '=' + CAST((@@ROWCOUNT - @UpdateCount) AS VARCHAR(500)) + ';';
SET @ResultText += '"
+ Properties.Resources.UpdatedRecordCountIdentifierText
+ @"'+ '=' + CAST(@UpdateCount AS VARCHAR(500)) ;
SELECT @ResultText";
var queryResults = CurrentDbContext.ExecuteQuery <string>(schemaAttributeMetaQuery).Single();
var warningRecords =
CurrentDbContext.ExecuteQuery <string>(
@"SELECT war.TaxonomyPath + char(9) + war.AttributeName + char(9) +
war.MetaAttributeName + char(9) + war.MetaValue + char(9) +
war.WarningMessage
FROM [tempdb]..[" + warningTableName + @"] war
").ToList();
CurrentDbContext.ExecuteCommand(@"IF OBJECT_ID('[tempdb]..[" + warningTableName + @"]') IS NOT NULL
DROP TABLE [tempdb]..[" + warningTableName + @"]");
foreach (var warningRecord in warningRecords)
{
_warnings.Add(new WorkerWarning
{
LineData = warningRecord.Substring(0, warningRecord.LastIndexOf('\t')),
ErrorMessage = warningRecord.Substring(warningRecord.LastIndexOf('\t') + 1)
});
}
ProcessSummaryReport(queryResults);
CurrentDbContext.ExecuteCommand(deleteTempTableScript);
}
}
catch (IndexOutOfRangeException ex)
{
var newException = new Exception(Properties.Resources.InvalidRowInInputFileMessage, ex);
Summary.SetError(newException);
}
catch (Exception ex)
{
Summary.SetError(ex);
}
}