public override void Run()
{
try
{
if (!CurrentImportOptions.HasFlag(ImportOptions.CreateMissingAttributes))
{
State = WorkerState.Complete;
StatusMessage = string.Format("Skipped as per ImportOptions");
//TODO: Create new summary report
_warnings.Add(new WorkerWarning
{
LineData = "",
ErrorMessage =
Resources.CreateMissingAttributesFlagOffWarningMessage
});
ProcessSummaryReport();
return;
}
IEnumerable <AttributeInterchangeRecord> allData = ImportData.Attributes;
var attributeInterchangeRecords = allData as AttributeInterchangeRecord[] ?? allData.ToArray();
var totalRecordCount = attributeInterchangeRecords.Count();
CurrentLogWriter.DebugFormat("{0}: Total Records: {1}", Arguments.Id, totalRecordCount);
var invalidRecords = attributeInterchangeRecords.GetInvalidRecords();
var invalidAttributeInterchangeRecords = invalidRecords as IList <AttributeInterchangeRecord>
?? invalidRecords.ToList();
CurrentLogWriter.DebugFormat("{0}: Invalid Records: {1}", Arguments.Id,
invalidAttributeInterchangeRecords.Count);
invalidAttributeInterchangeRecords.ToList()
.ForEach(
ir =>
_warnings.Add(new WorkerWarning
{
LineData = ir.ToString(),
ErrorMessage = Resources.RequiredValueNullWarningMessage
}));
var validImportRecords =
attributeInterchangeRecords.Except(invalidAttributeInterchangeRecords.ToList()).ToList();
validImportRecords = validImportRecords.Distinct(new AttributeInterchangeRecordComparer()).ToList();// as List<AttributeInterchangeRecord>;
CurrentLogWriter.DebugFormat("{0}: Valid Records: {1}", Arguments.Id, validImportRecords.Count);
//Object level operation Start
//TODOs
//Report all the not found attributes types
using (CurrentDbContext = new AryaDbDataContext(CurrentProjectId, ImportRequestedBy))
{
CurrentLogWriter.DebugFormat("{0}: Reporting Warnings", Arguments.Id);
var existingNonMetaAttributes =
CurrentDbContext.Attributes.Where(
at => Attribute.NonMetaAttributeTypes.Contains(at.AttributeType)).ToList();
ReportAllWarinings(existingNonMetaAttributes, validImportRecords);
//create new attribute
CurrentLogWriter.DebugFormat("{0}: Get New Attributes", Arguments.Id);
var newAttributes = GetNewAttributes(existingNonMetaAttributes, validImportRecords).ToList();
foreach (var attributeInterchangeRecord in newAttributes)
{
CreateNewAttribute(attributeInterchangeRecord);
}
CurrentLogWriter.DebugFormat("{0}: Saving Changes", Arguments.Id);
SaveDataChanges();
ProcessSummaryReport(newAttributes.Count());
}
//Object level operation End
}
catch (IndexOutOfRangeException ex)
{
var newException = new Exception(Resources.InvalidRowInInputFileMessage, ex);
Summary.SetError(newException);
}
catch (Exception ex)
{
Summary.SetError(ex);
}
}
public override void Run()
{
var missingTaxonomies = new List <TaxonomyInterchangeRecord>();
//State = WorkerState.Working;
try
{
//initialize the context
using (
CurrentDbContext =
new AryaDbDataContext(CurrentProjectId, ImportRequestedBy))
{
Summary.StatusMessage = "Reading input file";
//reads all the values into a list<T>, change this as its not very efficient and scalable.
List <TaxonomyInterchangeRecord> allImportData = ImportData.Taxonomies;
var invalidRecords = allImportData.GetInvalidRecords();
//var invalidRecords = ImportData.Taxonomies;
var taxonomyInterchangeRecords = invalidRecords as IList <TaxonomyInterchangeRecord> ?? invalidRecords.ToList();
taxonomyInterchangeRecords.ToList().ForEach(ir => _warnings.Add(new WorkerWarning
{
LineData = ir.ToString(),
ErrorMessage = Properties.Resources.RequiredValueNullWarningMessage
}));
var validImportRecords = allImportData.Except(taxonomyInterchangeRecords.ToList()).ToList();
validImportRecords = validImportRecords.Distinct(new TaxonomyInterchangeRecordComparer()).ToList();
Summary.StatusMessage = "Fetching existing taxonomies";
_existingTaxonomyPathsAndIDs =
CurrentDbContext.ExecuteQuery <TaxonomyPathAndId>(
@"SELECT TaxonomyPath, TaxonomyID
FROM V_Taxonomy
WHERE TaxonomyPath <> ''
AND ProjectId = {0}", CurrentProjectId).
ToDictionary(key => key.TaxonomyPath.ToLower(), value => value.TaxonomyId,
StringComparer.OrdinalIgnoreCase);
missingTaxonomies.AddRange(
validImportRecords.Where(
importRecord => !_existingTaxonomyPathsAndIDs.ContainsKey(importRecord.TaxonomyPath.ToLower())));
missingTaxonomies = missingTaxonomies.Distinct(new TaxonomyInterchangeRecordComparer()).ToList();
var newTaxonomyCount = 0;
if (missingTaxonomies.Count != 0 && !CurrentImportOptions.HasFlag(ImportOptions.CreateMissingTaxonomies))
{
_warnings.Add(new WorkerWarning
{
LineData = Properties.Resources.CreateMissingTaxonomiesFlagOffLineDataText,
ErrorMessage = Properties.Resources.CreateMissingTaxonomiesFlagOffWarningMessage
});
ProcessSummaryReport(0);
return;
}
// var existingTaxonomyInFile = GetExistingTaxonomyInFile(validImportRecords, _existingTaxonomyPathsAndIDs);
Summary.StatusMessage = "Identifying missing taxonomies";
foreach (var missingTaxonomy in missingTaxonomies)
{
if (CreateTaxonomy(missingTaxonomy))
{
newTaxonomyCount++;
}
else
{
_warnings.Add(new WorkerWarning
{
LineData = missingTaxonomy.TaxonomyPath,
ErrorMessage = Properties.Resources.CreateNewTaxonomyFailWorningMessage
});
}
}
SaveDataChanges();
ProcessSummaryReport(newTaxonomyCount);
} //end of using
}
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
{
//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);
}
}
private void ProcessRecordWithEntityData(IEnumerable <SkuAttributeValueInterchangeRecord> recordsWithEntityInfo)
{
//TODO: IMPORTANT same value in file and db is being treated as different values. that means even if same record exists new entity data is created
SkuAttributeValueInterchangeRecord[] skuAttributeValueInterchangeRecords = recordsWithEntityInfo as SkuAttributeValueInterchangeRecord[] ?? recordsWithEntityInfo.ToArray();
//skuAttributeValueInterchangeRecords.RemoveAll(item => skuAttributeValueInterchangeRecords.Any()));
var duplicateValueEntityIds = skuAttributeValueInterchangeRecords.GroupBy(s => s.EntityID).SelectMany(grp => grp.Skip(1)).Select(s => s.EntityID).Distinct();
var valueEntityIds = duplicateValueEntityIds as Guid?[] ?? duplicateValueEntityIds.ToArray();
foreach (var warningRecord in valueEntityIds)
{
_warnings.Add(new WorkerWarning
{
LineData = warningRecord.ToString(),
ErrorMessage = Properties.Resources.DuplicateEntityIdWarningMessage
});
}
skuAttributeValueInterchangeRecords = skuAttributeValueInterchangeRecords.Where(sk => !valueEntityIds.Contains(sk.EntityID)).ToArray();
// var newList = dup.Where(d => skuAttributeValueInterchangeRecords.Contains(d.EntityID))
var sqlTempTableHelper = new SqlHelper(typeof(SkuAttributeValueInterchangeRecord));
var tempEntityInfoRecord = TempExistingEntityInfoTablePrefix + Guid.NewGuid();
var warningTableName = tempEntityInfoRecord + "_warning";
var createTempTableScript = sqlTempTableHelper.CreateTableScript(tempEntityInfoRecord, "tempdb");
var deleteTempTableScript = sqlTempTableHelper.DeleteTableScript(tempEntityInfoRecord, "tempdb");
//create the temp table.
CurrentDbContext.ExecuteCommand(createTempTableScript);
var markAsBeforeEntity = CurrentImportOptions.HasFlag(ImportOptions.MarkAsBeforeEntity);
CurrentLogWriter.DebugFormat("{0}: Inserting Records with EntityInfo", Arguments.Id);
CurrentDbContext.BulkInsertAll(skuAttributeValueInterchangeRecords, tempEntityInfoRecord, "tempdb");
CurrentLogWriter.DebugFormat("{0}: Processing Records with EntityInfo", Arguments.Id);
var entityInfoValueProcessorQueryString = @"DECLARE @UserID AS UNIQUEIDENTIFIER
DECLARE @ProjectID AS UNIQUEIDENTIFIER
DECLARE @ResultText AS VARCHAR(2000)
DECLARE @DefaultRemark AS UNIQUEIDENTIFIER
DECLARE @NewDataCount AS int
DECLARE @IgnoredCount AS int
DECLARE @AttributeUomUnique AS bit
DECLARE @CurrentRemarkId AS UNIQUEIDENTIFIER
Declare @MarkAsBeforeEntity as bit
SET @MarkAsBeforeEntity = '" + markAsBeforeEntity + @"'
SET @UserID = '" + ImportRequestedBy + @"'
SET @ProjectID = '" + CurrentProjectId + @"'
IF OBJECT_ID('tempdb..#NewEntityDatas') IS NOT NULL
DROP TABLE #NewEntityDatas
IF OBJECT_ID('[tempdb]..[" + warningTableName + @"]') IS NOT NULL
DROP TABLE [tempdb]..[" + warningTableName + @"]
CREATE TABLE [tempdb]..[" + warningTableName + @"]
(
ItemID varchar(255),
AttributeName varchar(255),
Uom varchar(255),
Value varchar(255),
Field1 varchar(255),
Field2 varchar(255),
Field3 varchar(255),
Field4 varchar(255),
Field5 varchar(255),
EntityID varchar(255),
WarningMessage varchar(255)
);
SET @IgnoredCount = 0
SET @NewDataCount = 0
SET @ResultText = ''
SET @AttributeUomUnique = 1
Select
sde.ItemID
,sde.[AttributeName]
,sde.[Uom]
,sde.[value]
,sde.[Field1]
,sde.[Field2]
,sde.[Field3]
,sde.[Field4]
,sde.[Field5]
,sde.[EntityID]
,sd.[EntityID] As ExistingEntityID
,sd.ItemID As ExistingItemId
,sd.[AttributeName] As ExistingAttributeName
,sd.[Uom] As ExistingUom
,sd.[value] As ExistingValue
,sd.[Field1] As ExistingField1
,sd.[Field2] As ExistingField2
,sd.[Field3] As ExistingField3
,sd.[Field4] As ExistingField4
,sd.[Field5] As ExistingField5
,a.ID as AttributeID
INTO #NewEntityDatas
FROM [tempdb]..[" + tempEntityInfoRecord + @"] sde
LEFT OUTER JOIN V_ActiveSkuData sd ON sd.EntityID = sde.[EntityID] LEFT OUTER JOIN Attribute a ON LOWER(a.AttributeName) = LOWER(sde.AttributeName) AND a.AttributeType IN ('Sku','Global', 'Derived', 'Flag')
--select * from #NewEntityDatas
INSERT into [tempdb]..[" + warningTableName
+ @"](ItemID,AttributeName,Uom,Value,Field1,Field2, Field3, Field4, Field5, EntityID,WarningMessage)
SELECT nd.ItemID,nd.AttributeName,nd.Uom,nd.Value,nd.Field1,nd.Field2,nd.Field3,nd.Field4,nd.Field5,nd.EntityID, '"
+ Resources.AttributeDoesNotExistWarningMessage + @"'
FROM #NewEntityDatas nd where nd.AttributeID is NULL
INSERT into [tempdb]..[" + warningTableName
+ @"](ItemID,AttributeName,Uom,Value,Field1,Field2, Field3, Field4, Field5,EntityID,WarningMessage)
SELECT nd.ItemID,nd.AttributeName,nd.Uom,nd.Value,nd.Field1,nd.Field2,nd.Field3,nd.Field4,nd.Field5,nd.EntityID,'"
+ Resources.EntityInfoIDDoesNotExistWarningMessage + @"'
FROM #NewEntityDatas nd where nd.ExistingEntityID is NULL
DELETE nd FROM #NewEntityDatas nd INNER JOIN [tempdb]..["
+ warningTableName + @"] w ON w.EntityID = nd.EntityID
--SELECT * FROM #NewEntityDatas
--UPDATE Entity Datas
UPDATE EntityData
SET Active = 0,DeletedOn = GETDATE(),DeletedBy = @UserID, DeletedRemark = @CurrentRemarkId
FROM EntityData ed inner join #NewEntityDatas nd ON nd.EntityID = ed.EntityID
WHERE ed.Active = 1
--Insert New Entity DATA
IF @MarkAsBeforeEntity = 1
BEGIN
INSERT INTO EntityData(ID, [AttributeID],[Value],[Uom],[Field1],[Field2],[Field3],[Field4],[Field5],[CreatedOn],[CreatedBy],[CreatedRemark],[Active],[BeforeEntity], EntityID)
SELECT NEWID(), td.AttributeId, td.Value, td.Uom,td.Field1, td.[Field2],td.[Field3],td.[Field4],td.[Field5], GETDATE(), @UserID, @CurrentRemarkId,1, 1, td.EntityID
FROM #NewEntityDatas td
END
ELSE
BEGIN
INSERT INTO EntityData(ID, [AttributeID],[Value],[Uom],[Field1],[Field2],[Field3],[Field4],[Field5],[CreatedOn],[CreatedBy],[CreatedRemark],[Active],[BeforeEntity], EntityID)
SELECT NEWID(), td.AttributeId, td.Value, td.Uom,td.Field1, td.[Field2],td.[Field3],td.[Field4],td.[Field5], GETDATE(), @UserID, @CurrentRemarkId,1, 0, td.EntityID
FROM #NewEntityDatas td
END
--SET @ResultText += '" + Resources.NewRecordCountIdentifierText
+ @"'+ '=' + CAST(@@ROWCOUNT AS VARCHAR(50)) ;
SELECT @@ROWCOUNT
";
var queryResults = CurrentDbContext.ExecuteQuery <int>(entityInfoValueProcessorQueryString).Single();
_successCountForEntityInfo = queryResults;
CurrentDbContext.ExecuteCommand(deleteTempTableScript);
var warningRecords =
CurrentDbContext.ExecuteQuery <string>(@"SELECT war.ItemID + char(9) + war.AttributeName + char(9) +
ISNULL(war.Uom,'') + char(9) + ISNULL(war.Value,'') + char(9) +
ISNULL(war.Field1,'') + char(9) + ISNULL(war.Field2,'') + char(9) +
ISNULL(war.Field3,'') + char(9) + ISNULL(war.Field4,'') + char(9) +
ISNULL(war.Field5,'') + char(9) +
ISNULL(war.EntityID,'') + char(9) + war.WarningMessage
FROM [tempdb]..[" + warningTableName
+ @"] war").ToList();
CurrentDbContext.ExecuteCommand(@"IF OBJECT_ID('[tempdb]..[" + warningTableName + @"]') IS NOT NULL
DROP TABLE [tempdb]..[" + warningTableName + @"]");
CurrentLogWriter.DebugFormat("{0}: {1} Warnings", Arguments.Id, warningRecords.Count);
foreach (var warningRecord in warningRecords)
{
_warnings.Add(new WorkerWarning
{
LineData = warningRecord.Substring(0, warningRecord.LastIndexOf('\t')),
ErrorMessage =
warningRecord.Substring(warningRecord.LastIndexOf('\t') + 1)
});
}
}
public override void Run()
{
//State = WorkerState.Working;
try
{
if (!CurrentImportOptions.HasFlag(ImportOptions.CreateMissingValues))
{
CurrentLogWriter.Debug(Resources.CreateMissingValuesFlagOffWanringMessage);
_warnings.Add(new WorkerWarning
{
LineData = Resources.CreateMissingValuesFlagOffLineDataText,
ErrorMessage = Resources.CreateMissingValuesFlagOffWanringMessage
});
}
using (CurrentDbContext = new AryaDbDataContext(CurrentProjectId, ImportRequestedBy))
{
var allData = ImportData.SkuAttributeValues;
CurrentLogWriter.DebugFormat("{0}: Total Records: {1}", Arguments.Id, allData.Count);
var invalidRecords = allData.GetInvalidRecords();
var attributeValueInterchangeRecords = invalidRecords as IList <SkuAttributeValueInterchangeRecord>
?? invalidRecords.ToList();
CurrentLogWriter.DebugFormat("{0}: Invalid Records: {1}", Arguments.Id, attributeValueInterchangeRecords.Count);
attributeValueInterchangeRecords.ToList()
.ForEach(
ir =>
_warnings.Add(new WorkerWarning
{
LineData = ir.ToString(),
ErrorMessage =
Resources.RequiredValueNullWarningMessage
}));
var validImportRecords = allData.Except(attributeValueInterchangeRecords.ToList()).ToList();
CurrentLogWriter.DebugFormat("{0}: Valid Records: {1}", Arguments.Id, validImportRecords.Count);
var recordsWithEntityInfo = validImportRecords.Where(ad => ad.EntityID != null);
validImportRecords = validImportRecords.Where(ad => ad.EntityID == null).ToList();
// (from row in validImportRecords where !recordsWithEntityInfo.Contains(row) select row).ToList();
var skuAttributeValueInterchangeRecords =
recordsWithEntityInfo as SkuAttributeValueInterchangeRecord[] ?? recordsWithEntityInfo.ToArray();
CurrentLogWriter.DebugFormat("{0}: Records with EntityInfo: {1}", Arguments.Id,
skuAttributeValueInterchangeRecords.Length);
if (skuAttributeValueInterchangeRecords.Count() != 0)
{
ProcessRecordWithEntityData(skuAttributeValueInterchangeRecords);
}
//TODO: take out multi value from import data and data bases Start
var groupByResults =
(from t in validImportRecords
let groupItems = GetGroup(t, _attPlusUom)
group t by groupItems
into grp select grp).ToHashSet();
var multiValues =
(from grp in groupByResults where grp.Count() > 1 from row in grp select row).ToHashSet();
CurrentLogWriter.DebugFormat("{0}: {1} multi-Value-sets", Arguments.Id, multiValues.Count);
var distinctValues =
groupByResults.Where(r => r.Count() == 1)
.Select(
r =>
new StringValueWrapperRecord
{
StringValue =
string.Format("{0}|{1}{2}", r.Key.Item1,
r.Key.Item2,
_attPlusUom
? string.Format("|{0}", r.Key.Item3)
: string.Empty)
});
//.ToList();}
//TODO : Bulk Insert n db and then join with
var sqlTempTableHelper = new SqlHelper(typeof(StringValueWrapperRecord));
var tempSkuKeyTableName = TempSkuKeyTablePrefix + Guid.NewGuid();
var createTempTableScript = sqlTempTableHelper.CreateTableScript(tempSkuKeyTableName, "tempdb");
var deleteTempTableScript = sqlTempTableHelper.DeleteTableScript(tempSkuKeyTableName, "tempdb");
//create the temp table.
CurrentDbContext.ExecuteCommand(createTempTableScript);
CurrentLogWriter.DebugFormat("{0}: Bulk Inserting Distinct Value (non-multi-value) records", Arguments.Id);
CurrentDbContext.BulkInsertAll(distinctValues, tempSkuKeyTableName, "tempdb");
//End of bulk insert
CurrentLogWriter.DebugFormat("{0}: Processing Distinct Value (non-multi-value) records", Arguments.Id);
var query = string.Format(@"
SELECT skd.StringValue
FROM [tempdb]..[{1}] skd
INNER JOIN V_ActiveSkuData vasd
ON vasd.ItemID + '|' + vasd.AttributeName {0} = skd.StringValue
GROUP BY skd.StringValue Having count(*) > 1
", _attPlusUom ? " + '|' + ISNULL(Uom,'')" : string.Empty, tempSkuKeyTableName);
var groupByResultsDb = CurrentDbContext.ExecuteQuery <StringValueWrapperRecord>(query);
CurrentDbContext.ExecuteCommand(deleteTempTableScript);
var dbResults = (from row in groupByResultsDb
let grp = row.StringValue
let parts = grp.Split('|')
let itemId = parts[0]
let attribute = parts[1]
let uom = parts.Length > 2 && parts[2] != string.Empty ? parts[2] : null
select new Tuple <string, string, string>(itemId, attribute, uom)).ToHashSet();
multiValues.AddRange(from grp in groupByResults
where dbResults.Contains(grp.Key)
from row in grp
select row);
ProcessMultivalues(multiValues);
//End multivalue vandle
validImportRecords =
(from row in validImportRecords where !multiValues.Contains(row) select row).ToList();
sqlTempTableHelper = new SqlHelper(CurrentInterchangeRecordType);
var tempTableName = TempTablePrefix + Guid.NewGuid();
var warningTableName = tempTableName + "_warning";
createTempTableScript = sqlTempTableHelper.CreateTableScript(tempTableName, "tempdb");
deleteTempTableScript = sqlTempTableHelper.DeleteTableScript(tempTableName, "tempdb");
//create the temp table.
CurrentDbContext.ExecuteCommand(createTempTableScript);
CurrentLogWriter.DebugFormat("{0}: Bulk Inserting valid Import Records", Arguments.Id);
CurrentDbContext.BulkInsertAll(validImportRecords, tempTableName, "tempdb");
CurrentLogWriter.DebugFormat("{0}: Processing valid Import Records", Arguments.Id);
CurrentDbContext.ExecuteCommand(@"
--Update entity data, SET active to 0
ALTER TABLE [tempdb]..[" + tempTableName + @"]
ADD SkuId UNIQUEIDENTIFIER
,AttributeId UNIQUEIDENTIFIER
,EntityInfoId UNIQUEIDENTIFIER
,EntityDataId UNIQUEIDENTIFIER
,DbField1 nvarchar(4000)
,DbField2 nvarchar(4000)
,DbField3 nvarchar(4000)
,DbField4 nvarchar(4000)
,DbField5 nvarchar(4000)");
var createMissingValues = CurrentImportOptions.HasFlag(ImportOptions.CreateMissingValues);
var markAsBeforeEntity = CurrentImportOptions.HasFlag(ImportOptions.MarkAsBeforeEntity);
var singleValueProcessorQueryString = string.Format(@"Declare @CreateMissingValues as bit
Declare @MarkAsBeforeEntity as bit
DECLARE @UserID AS UNIQUEIDENTIFIER
DECLARE @ProjectID AS UNIQUEIDENTIFIER
DECLARE @ResultText AS VARCHAR(2000)
DECLARE @CurrentRemarkId AS UNIQUEIDENTIFIER
DECLARE @NewDataCount AS int
DECLARE @IgnoredCount AS int
SET @UserID = '" + ImportRequestedBy + @"'
SET @ProjectID = '" + CurrentProjectId + @"'
SET @IgnoredCount = 0
SET @NewDataCount = 0
SET @ResultText = ''
SET @CreateMissingValues = '" + createMissingValues + @"'
SET @MarkAsBeforeEntity = '" + markAsBeforeEntity + @"'
SET @CurrentRemarkId = '" + CurrentRemarkId + @"'
IF OBJECT_ID('tempdb..#NewEntityInfo') IS NOT NULL
DROP TABLE #NewEntityInfo
IF OBJECT_ID('[tempdb]..[" + warningTableName + @"]') IS NOT NULL
DROP TABLE [tempdb]..[" + warningTableName + @"]
CREATE TABLE [tempdb]..[" + warningTableName + @"]
(
ItemID varchar(255),
AttributeName varchar(255),
Uom varchar(255),
Value varchar(4000),
Field1 varchar(255),
Field2 varchar(255),
Field3 varchar(255),
Field4 varchar(255),
Field5 varchar(255),
EntityID varchar(255),
WarningMessage varchar(255)
);
-- Take Out Missiing Skus
INSERT into [tempdb]..[" + warningTableName + @"](ItemID,AttributeName,Uom,Value,Field1,Field2, Field3, Field4, Field5, EntityID,WarningMessage)
Select td.ItemID,td.AttributeName,td.Uom,td.Value,td.Field1,td.Field2,td.Field3,td.Field4,td.Field5,td.EntityID, '" + Resources.ItemDoesNotExistWarningMessage + @"'
FROM [tempdb]..[" + tempTableName + @"] td LEFT OUTER JOIN Sku s on s.ItemID = td.ItemID where s.ItemID is NULL
-- Take Out Missiing attributes
INSERT into [tempdb]..[" + warningTableName + @"](ItemID,AttributeName,Uom,Value,Field1,Field2, Field3, Field4, Field5, EntityID ,WarningMessage)
Select td.ItemID,td.AttributeName,td.Uom,td.Value,td.Field1,td.Field2,td.Field3,td.Field4,td.Field5,td.EntityID, '" + Resources.AttributeDoesNotExistWarningMessage + @"'
FROM [tempdb]..[" + tempTableName + @"] td LEFT OUTER JOIN Attribute a on LOWER(a.AttributeName) = LOWER(td.AttributeName) AND a.AttributeType IN ('Sku','Global', 'Derived', 'Flag')
WHERE a.AttributeName is NULL
-- DELETE all the warning entries from temp table
Delete td from [tempdb]..[" + tempTableName + @"] td inner join [tempdb]..["
+ warningTableName
+ @"] w on td.ItemID = w.ItemID and td.AttributeName = w.AttributeName
-- Add a count here for ignored and delete this record
DELETE td
--select *
from [tempdb]..[" + tempTableName
+ @"] td inner join V_AllSkuData sd on sd.ItemID = td.ItemID
AND sd.AttributeName = td.AttributeName
AND ISNULL(sd.Uom,'') = ISNULL(td.Uom,'')
AND ISNULL(sd.Value,'') = ISNULL(td.Value,'')
AND ISNULL(sd.Field1,'') = ISNULL(td.Field1,'')
AND ISNULL(sd.Field2,'') = ISNULL(td.Field2,'')
AND ISNULL(sd.Field3,'') = ISNULL(td.Field3,'')
AND ISNULL(sd.Field4,'') = ISNULL(td.Field4,'')
AND ISNULL(sd.Field5,'') = ISNULL(td.Field5,'')
Where sd.Active = 1
-- SET @IgnoredCount = @@ROWCOUNT
SET @ResultText += '" + Resources.IgnoredRecordCountIdentifierText
+ @"'+ '=' + CAST(@@ROWCOUNT AS VARCHAR(50)) + ';';
--UPDATE Skuid and attribute id
UPDATE [tempdb]..[" + tempTableName + @"]
SET SkuId = s.ID, AttributeId = a.ID
FROM [tempdb]..[" + tempTableName + @"] td Inner join sku s on s.ItemID = td.ItemID inner join Attribute a on LOWER(a.AttributeName) = LOWER(td.AttributeName) AND a.AttributeType IN ('Sku','Global', 'Derived', 'Flag')
--Update Entity info and Entity Data
UPDATE [tempdb]..[" + tempTableName + @"]
SET EntityInfoId = vasd.EntityId, EntityDataId = vasd.EntityDataId,
DbField1 = vasd.Field1, DbField2 = vasd.Field2, DbField3 = vasd.Field3, DbField4 = vasd.Field4, DbField5 = vasd.Field5
FROM [tempdb]..[" + tempTableName + @"] td inner join V_ActiveSkuData vasd on vasd.SkuID = td.SkuId and vasd.AttributeID = td.AttributeId
IF @MarkAsBeforeEntity = 1
BEGIN
UPDATE EntityData
SET BeforeEntity = 0 FROM EntityData ed inner join [tempdb]..[" + tempTableName + @"] td on td.EntityDataId = ed.ID
END
IF @CreateMissingValues = 0
BEGIN
--Delete all the item the are not in the db from temp so that the update will work.
DELETE td from [tempdb]..[" + tempTableName + @"] td where EntityInfoId is null
END
--Update all the existing entitydata set to 0
UPDATE EntityData
SET Active = 0,DeletedBy = @UserID,DeletedOn = GETDATE(),DeletedRemark = @CurrentRemarkId
From EntityData ed inner join [tempdb]..[" + tempTableName + @"] td on td.EntityDataId = ed.ID
WHERE ed.Active = 1
SET @ResultText += '" + Resources.UpdatedRecordCountIdentifierText
+ @"'+ '=' + CAST(@@ROWCOUNT AS VARCHAR(50)) + ';';
--Update entity info in import data table
UPDATE [tempdb]..[" + tempTableName + @"]
SET EntityInfoId = NEWID()
where [tempdb]..[" + tempTableName + @"].EntityDataId is null
--Insert new entity info
Insert Into EntityInfo(ID, SkuID)
Select td.EntityInfoId, td.SkuId
FROM [tempdb]..[" + tempTableName + @"] td where td.EntityDataId is null
SET @ResultText += '" + Resources.NewRecordCountIdentifierText
+ @"'+ '=' + CAST(@@ROWCOUNT AS VARCHAR(50)) ;
IF @MarkAsBeforeEntity = 1
BEGIN
-- Insert all the record in entity data
Insert Into EntityData(ID, [AttributeID],[Value],[Uom],[Field1],[Field2],[Field3],[Field4],[Field5],[CreatedOn],[CreatedBy],[CreatedRemark],[Active],BeforeEntity, EntityID)
select NEWID(), td.AttributeId, td.Value, td.Uom, ISNULL(td.[Field1], td.DbField1), ISNULL(td.[Field2], td.DbField2), ISNULL(td.[Field3], td.DbField3),
ISNULL(td.[Field4], td.DbField4), ISNULL(td.[Field5], td.DbField5), GETDATE(), @UserID, @CurrentRemarkId,1,1, td.EntityInfoId
FROM [tempdb]..[" + tempTableName + @"] td
END
ELSE
BEGIN
-- Insert all the record in entity data
Insert Into EntityData(ID, [AttributeID],[Value],[Uom],[Field1],[Field2],[Field3],[Field4],[Field5],[CreatedOn],[CreatedBy],[CreatedRemark],[Active], EntityID)
select NEWID(), td.AttributeId, td.Value, td.Uom, ISNULL(td.[Field1], td.DbField1), ISNULL(td.[Field2], td.DbField2), ISNULL(td.[Field3], td.DbField3),
ISNULL(td.[Field4], td.DbField4), ISNULL(td.[Field5], td.DbField5), GETDATE(), @UserID, @CurrentRemarkId,1, td.EntityInfoId
FROM [tempdb]..[" + tempTableName + @"] td
END
Select @ResultText
");
_queryResults = CurrentDbContext.ExecuteQuery <string>(singleValueProcessorQueryString).Single();
CurrentLogWriter.DebugFormat("{0}: Fetching warnings", Arguments.Id);
var warningRecords =
CurrentDbContext.ExecuteQuery <string>(
@"SELECT war.ItemID + char(9) + war.AttributeName + char(9) +
ISNULL(war.Uom,'') + char(9) + ISNULL(war.Value,'') + char(9) +
ISNULL(war.Field1,'') + char(9) + ISNULL(war.Field2,'') + char(9) +
ISNULL(war.Field3,'') + char(9) + ISNULL(war.Field4,'') + char(9) +
ISNULL(war.Field5,'') + char(9) +
ISNULL(war.EntityID,'') + char(9) + war.WarningMessage
FROM[tempdb]..[" + warningTableName
+ @"] war
").ToList();
//var warningRecords =
// CurrentDbContext.ExecuteQuery<string>("select * from [tempdb]..[" + warningTableName + @"]").ToList();
CurrentDbContext.ExecuteCommand(@"IF OBJECT_ID('[tempdb]..[" + warningTableName + @"]') IS NOT NULL
DROP TABLE [tempdb]..[" + warningTableName + @"]");
CurrentLogWriter.DebugFormat("{0}: {1} warnings", Arguments.Id, warningRecords.Count);
foreach (var warningRecord in warningRecords)
{
_warnings.Add(new WorkerWarning
{
LineData =
warningRecord.Substring(0,
warningRecord.LastIndexOf('\t')),
ErrorMessage =
warningRecord.Substring(warningRecord.LastIndexOf('\t')
+ 1)
});
}
CurrentLogWriter.DebugFormat("{0}: Processing Summary Report", Arguments.Id);
ProcessSummaryReport(_queryResults);
CurrentDbContext.ExecuteCommand(deleteTempTableScript);
}
}
catch (IndexOutOfRangeException ex)
{
var newException = new Exception(Resources.InvalidRowInInputFileMessage, ex);
Summary.SetError(newException);
}
catch (Exception ex)
{
Summary.SetError(ex);
}
}
//This should be handles in the invalid value seperations
//private void ReportNullAttributeValues(List<SkuAttributeValueInterchangeRecord> validImportRecords)
//{
// var nullAttributeValueRecords = validImportRecords.Where(ad => ad.Value == null).ToList();
// nullAttributeValueRecords.ForEach(vr =>
// {
// _warnings.Add(new WorkerWarning
// {
// LineData = vr.ToString(),
// ErrorMessage =
// "Attribute Value can not be null."
// });
// validImportRecords.Remove(vr);
// });
//}
private void ProcessMultivalues(IEnumerable <SkuAttributeValueInterchangeRecord> multiValues)
{
CurrentLogWriter.DebugFormat("{0}: Processing Multi-Value records", Arguments.Id);
var existingEntityInfos = new List <EntityInfo>();
var results = multiValues.GroupBy(mv => new { mv.ItemID, mv.AttributeName },
(key, group) => new { key.ItemID, key.AttributeName, Values = group.ToList() });
foreach (var fileGroup in results)
{
var line = fileGroup;
var dbGroup = (from s in CurrentDbContext.Skus
where s.ItemID == line.ItemID
from ei in s.EntityInfos
from ed in ei.EntityDatas
where ed.Active && ed.Attribute.AttributeName.ToLower() == line.AttributeName.ToLower()
select ed).ToList();
//if its an exact match on value+Uom
var exactMatches = from fileLine in fileGroup.Values
join dbLine in dbGroup on new { fileLine.Value, fileLine.Uom } equals new { dbLine.Value, dbLine.Uom }
let ln = new { fileLine, dbLine }
group ln by ln.fileLine
into grp
select new { FileLine = grp.Key, DbLines = grp.Select(g => g.dbLine) };
foreach (var exactMatch in exactMatches)
{
//Processing general updates
if (!FieldsInFileAndDbFieldsMatches(exactMatch.DbLines, exactMatch.FileLine))
{
exactMatch.DbLines.ForEach(e => e.Active = false);
var ed = exactMatch.DbLines.First();
ed.EntityInfo.EntityDatas.Add(new EntityData(CurrentDbContext)
{
Attribute = ed.Attribute,
Value = ed.Value,
Uom = ed.Uom,
Field1 = exactMatch.FileLine.Field1,
Field2 = exactMatch.FileLine.Field2,
Field3 = exactMatch.FileLine.Field3,
Field4 = exactMatch.FileLine.Field4,
Field5 = exactMatch.FileLine.Field5,
BeforeEntity = false,
CreatedRemark = CurrentRemarkId
});
}
//remove the item from the eds
dbGroup.RemoveAll(exactMatch.DbLines);
//TODO: remove from file list??
_ignoredCountMultiValues++;
fileGroup.Values.Remove(exactMatch.FileLine);
}
//case 1
if (_replaceExistingValues)
{
//add to the entity info
existingEntityInfos = dbGroup.OrderBy(e => e.Value).Select(e => e.EntityInfo).ToList();
foreach (var entityData in dbGroup)
{
//deactivate all in dbs
entityData.Active = false;
}
}
if (existingEntityInfos.Count == 0 && !CurrentImportOptions.HasFlag(ImportOptions.CreateMissingValues))
{
continue;
}
//case 2
// var currentEntityInfoIndex = 0;
foreach (var itemFromFile in fileGroup.Values)
{
//create new entity data
if (itemFromFile == null)
{
continue;
}
EntityInfo currentEntityInfo;
if (existingEntityInfos.Count != 0)
{
currentEntityInfo = existingEntityInfos.First();
}
else
{
var currentSku = CurrentDbContext.Skus
.FirstOrDefault(
s =>
s.ItemID == itemFromFile.ItemID);
if (currentSku == null)
{
_warnings.Add(new WorkerWarning
{
LineData = itemFromFile.ToString(),
ErrorMessage = Resources.ItemDoesNotExistWarningMessage// Resources.AttributeDoesNotExistWarningMessage
});
continue;
}
else
{
currentEntityInfo = new EntityInfo(CurrentDbContext)
{
Sku = currentSku
};
CurrentDbContext.EntityInfos.InsertOnSubmit(currentEntityInfo);
}
}
var attributeFromName = Attribute.GetAttributeFromName(CurrentDbContext, itemFromFile.AttributeName, false, useChache: false);
if (attributeFromName == null)
{
_warnings.Add(new WorkerWarning
{
LineData = itemFromFile.ToString(),
ErrorMessage = Resources.AttributeDoesNotExistWarningMessage
});
continue;
}
var attId = attributeFromName.ID;
currentEntityInfo.EntityDatas.Add(new EntityData(CurrentDbContext)
{
AttributeID = attId,
Value = itemFromFile.Value,
Uom = itemFromFile.Uom,
Field1 = itemFromFile.Field1,
Field2 = itemFromFile.Field2,
Field3 = itemFromFile.Field3,
Field4 = itemFromFile.Field4,
Field5 = itemFromFile.Field5,
BeforeEntity = false,
CreatedRemark = CurrentRemarkId
});
_successCountForMultiValues++;
if (existingEntityInfos.Contains(currentEntityInfo))
{
existingEntityInfos.Remove(currentEntityInfo);
}
}
}
SaveDataChanges();
}
public override void Run()
{
string queryResults;
try
{
using (
CurrentDbContext = CurrentDbContext = new AryaDbDataContext(CurrentProjectId, ImportRequestedBy))
{
List <SkuTaxonomyInterchangeRecord> allData2;
allData2 = ImportData.SkuTaxonomies;
var invalidRecords = allData2.GetInvalidRecords();
var skuTaxonomyInterchangeRecords = invalidRecords as SkuTaxonomyInterchangeRecord[] ?? invalidRecords.ToArray();
skuTaxonomyInterchangeRecords.ToList().ForEach(ir => _warnings.Add(new WorkerWarning
{
LineData = ir.ToString(),
ErrorMessage = Properties.Resources.RequiredValueNullWarningMessage
}));
var validImportRecords = allData2.Except(skuTaxonomyInterchangeRecords.ToList()).ToList();
//prepare a table for delete
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 createMissingSku = CurrentImportOptions.HasFlag(ImportOptions.CreateMissingSkus);
var queryString = @"Declare @CreateMissingSku as bit
DECLARE @UserID AS UNIQUEIDENTIFIER
DECLARE @ProjectID AS UNIQUEIDENTIFIER
DECLARE @ResultText AS VARCHAR(2000)
DECLARE @DefaultRemark AS UNIQUEIDENTIFIER
DECLARE @NewSkuCount AS int
DECLARE @IgnoredCount AS int
DECLARE @DefaultTaxonomyID AS UNIQUEIDENTIFIER
DECLARE @CurrentRemarkId AS UNIQUEIDENTIFIER
SET @CurrentRemarkId = '" + CurrentRemarkId + @"'
SET @UserID = '" + ImportRequestedBy + @"'
SET @ProjectID = '" + CurrentProjectId + @"'
SET @CreateMissingSku = '" + createMissingSku + @"'
SET @IgnoredCount = 0
SET @NewSkuCount = 0
SET @ResultText = '';
IF OBJECT_ID('tempdb..#NewSkus') IS NOT NULL
DROP TABLE #NewSkus
IF OBJECT_ID('[tempdb]..[" + warningTableName + @"]') IS NOT NULL
DROP TABLE [tempdb]..[" + warningTableName + @"]
CREATE TABLE [tempdb]..[" + warningTableName + @"]
(
ItemID varchar(255),
TaxonomyPath varchar(1000),
WarningMessage varchar(255)
);
IF OBJECT_ID('tempdb..#UpdatableSkuInfo') IS NOT NULL
DROP TABLE #UpdatableSkuInfo
select @DefaultTaxonomyID = TaxonomyID FROM [TaxonomyData] WHERE [NodeName] = ''
--Process multiple taxonomy for single item
SELECT tt.ItemID
INTO #MultipleTaxonomyForSingleItem
FROM [tempdb]..[" + tempTableName + @"] tt
GROUP BY tt.ItemID HAVING COUNT(*) > 1
INSERT INTO [tempdb]..[" + warningTableName + @"] (ItemID,TaxonomyPath,WarningMessage )
SELECT ItemID,'','" + Properties.Resources.ItemIdContainsMultipleTaxonomiesWanringMessage + @"'
FROM #MultipleTaxonomyForSingleItem
DELETE [tempdb]..[" + tempTableName + @"] FROM [tempdb]..[" + tempTableName + @"] tf
INNER JOIN #MultipleTaxonomyForSingleItem mt ON mt.ItemID = tf.ItemID
INSERT INTO [tempdb]..[" + warningTableName + @"](ItemID,TaxonomyPath,WarningMessage)
SELECT tf.ItemID, tf.TaxonomyPath, '" + Properties.Resources.TaxonomyDoesNotExistsWarningMessage + @"'
FROM [tempdb]..[" + tempTableName + @"] tf LEFT OUTER JOIN V_Taxonomy tp
ON LOWER(tf.TaxonomyPath) = LOWER(tp.TaxonomyPath)
WHERE tp.TaxonomyPath IS NULL and tf.TaxonomyPath IS NOT NULL
--SET @IgnoredCount += @@ROWCOUNT
INSERT INTO [tempdb]..[" + warningTableName + @"](ItemID,TaxonomyPath,WarningMessage)
SELECT tf.ItemID, tf.TaxonomyPath, '" + Properties.Resources.SkuOnCrossListedTaxonomyDoesNotExistsWarningMessage + @"'
FROM [tempdb]..[" + tempTableName + @"] tf INNER JOIN V_Taxonomy tp
ON LOWER(tf.TaxonomyPath) = LOWER(tp.TaxonomyPath) Where tp.NodeType = '" + TaxonomyInfo.NodeTypeDerived + @"'
--Remove the items with tax does not exist in the db
Delete [tempdb]..[" + tempTableName + @"] FROM [tempdb]..[" + tempTableName + @"] tf Inner JOIN [tempdb]..[" + warningTableName + @"] wr ON tf.ItemID = wr.ItemID
--Process New Skus
--Find New Skus
select tf.ItemID, tf.TaxonomyPath
INTO #NewSkus
from [tempdb]..[" + tempTableName + @"] tf left OUTER JOIN sku on sku.ItemID = tf.[ItemID] where sku.ID is null
--SET @NewSkuCount = @@ROWCOUNT
SET @ResultText = '" + Properties.Resources.NewRecordCountIdentifierText + @"'+ '=' + CAST(@@ROWCOUNT AS VARCHAR(500)) + ';';
Delete [tempdb]..[" + tempTableName + @"] FROM [tempdb]..[" + tempTableName + @"] tf Inner JOIN #NewSkus wr ON tf.ItemID = wr.ItemID
IF @CreateMissingSku = 0
BEGIN
INSERT into [tempdb]..[" + warningTableName + @"](ItemID,TaxonomyPath,WarningMessage)
select ItemID,TaxonomyPath,'" + Properties.Resources.CreateMissingSkuFlagOffWarningMessage + @"' As Message from #NewSkus
Delete ns FROM #NewSkus ns
SET @ResultText = '" + Properties.Resources.NewRecordCountIdentifierText + @"'+ '=' + CAST(0 AS VARCHAR(500)) + ';';
--SET @IgnoredCount = @@ROWCOUNT
END
SELECT NEWID() AS SkuID, ns.ItemID, vt.TaxonomyId
INTO #taxSku
FROM #NewSkus ns INNER JOIN V_Taxonomy vt ON vt.TaxonomyPath = ns.TaxonomyPath
--SELECT * FROM #taxSku ts
INSERT INTO Sku (ID,ItemID,ProjectID,CreatedOn,CreatedBy,SkuType)
SELECT ts.SkuID,ItemID,@ProjectID,GETDATE(),@UserID,'Product'
FROM #taxSku ts
Insert Into SkuInfo(ID, SkuID,TaxonomyID, CreatedOn, CreatedBy,CreatedRemark, Active)
select NEWID(), ts.SkuID,ts.TaxonomyId,GETDATE(),@UserID,@CurrentRemarkId,1
from #taxSku ts
DELETE #NewSkus FROM #NewSkus ns Inner join #taxSku nt ON nt.ItemID = ns.ItemID
--SELECT * FROM #NewSkus
--Create new skus
INSERT INTO Sku (ID,ItemID,ProjectID,CreatedOn,CreatedBy,SkuType)
SELECT NEWID(),ItemID,@ProjectID,GETDATE(),@UserID,'Product'
FROM #NewSkus
SELECT sf.ItemID, sf.TaxonomyPath as newTaxPath,vt1.TaxonomyId as newTaxID, sku.ID AS SkuID,vt.TaxonomyPath as OldTaxonomyPath, vt.TaxonomyId as oldTaxId
INTO #UpdatableSkuInfo
FROM [tempdb]..[" + tempTableName + @"] sf
Inner join V_Taxonomy vt1 on vt1.TaxonomyPath = sf.TaxonomyPath
Inner join Sku on sku.ItemID = sf.ItemID
Inner Join SkuInfo si on si.SkuID = sku.ID and si.Active = 1
Inner join V_Taxonomy vt on vt.TaxonomyId = si.TaxonomyID
--Remove the items that has same data in import file and db
--Select * from #UpdatableSkuInfo
--SELECT @IgnoredCount = COUNT(*) from #UpdatableSkuInfo up WHERE up.newTaxPath = up.OldTaxonomyPath
DELETE #UpdatableSkuInfo FROM #UpdatableSkuInfo up WHERE up.newTaxPath = up.OldTaxonomyPath
--SET @IgnoredCount = @@ROWCOUNT
SET @ResultText += '" + Properties.Resources.IgnoredRecordCountIdentifierText + @"' + '=' + CAST(@@ROWCOUNT AS VARCHAR(500)) + ';';
--Update Sku Info and Create new
UPDATE SkuInfo
SET Active = 0
FROM SkuInfo si
INNER JOIN #UpdatableSkuInfo usi ON usi.SkuID = si.SkuID
WHERE si.Active = 1
INSERT INTO SkuInfo ( ID, SkuID,TaxonomyID, CreatedOn, CreatedBy,CreatedRemark, Active)
SELECT NEWID(), usi.SkuID,usi.newTaxID,GETDATE(),@UserID,@CurrentRemarkId,1
FROM #UpdatableSkuInfo usi
SET @ResultText += '" + Properties.Resources.UpdatedRecordCountIdentifierText + @"'+ '=' + CAST(@@ROWCOUNT AS VARCHAR(500))
-- Create Sku Info for those that does not have sku info
Insert Into SkuInfo(ID, SkuID,TaxonomyID, CreatedOn, CreatedBy,CreatedRemark, Active)
select NEWID(), s.ID,@DefaultTaxonomyID,GETDATE(),@UserID,@CurrentRemarkId,1
from sku s left outer join SkuInfo on s.ID = SkuInfo.SkuID and SkuInfo.Active = 1 where SkuInfo.SkuID is Null
SELECT @ResultText
DROP TABLE #NewSkus
DROP TABLE #UpdatableSkuInfo
";
queryResults = CurrentDbContext.ExecuteQuery <string>(queryString).Single();
var warningsRecords = CurrentDbContext.ExecuteQuery <ItemTaxonomyWarnings>(@"SELECT ItemID, TaxonomyPath,WarningMessage
FROM [tempdb]..[" + warningTableName + @"]").ToList();
CurrentDbContext.ExecuteCommand(@"IF OBJECT_ID('[tempdb]..[" + warningTableName + @"]') IS NOT NULL
DROP TABLE [tempdb]..[" + warningTableName + @"]");
foreach (var itemTaxonomyWarning in warningsRecords)
{
_warnings.Add(new WorkerWarning
{
LineData = itemTaxonomyWarning.ToString(),
ErrorMessage = itemTaxonomyWarning.WarningMessage
});
}
ProcessSummaryReport(queryResults);
//delete temperorary table
CurrentDbContext.ExecuteCommand(deleteTempTableScript);
}
}
catch (IndexOutOfRangeException ex)
{
var newException = new Exception(Properties.Resources.InvalidRowInInputFileMessage, ex);
Summary.SetError(newException);
}
catch (Exception ex)
{
Summary.SetError(ex);
}
}
