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); } }
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); } }
public override void Run() { //State = WorkerState.Working; List <string> ignoredTaxonomyPaths = new List <string>(); Dictionary <string, TaxonomyPathAndId> existingTaxonomyPathId; List <TaxonomyMetaDataInterchangeRecord> allData = new List <TaxonomyMetaDataInterchangeRecord>(); List <TaxonomyMetaDataInterchangeRecord> recordWithMissingMetaAttributes = new List <TaxonomyMetaDataInterchangeRecord>(); string queryResults = string.Empty; try { //initialize the context List <TaxonomyMetaDataInterchangeRecord> validImportRecords; using (CurrentDbContext = new AryaDbDataContext(CurrentProjectId, ImportRequestedBy)) { _currentImageManager = new ImageManager(CurrentDbContext, CurrentProjectId); allData = ImportData.TaxonomyMetaDatas; var invalidRecords = allData.GetInvalidRecords(); AddToWarning(invalidRecords.ToList(), Properties.Resources.RequiredValueNullWarningMessage); validImportRecords = allData.Except(invalidRecords.ToList()).ToList(); validImportRecords = validImportRecords.Distinct(new TaxonomyMetaDataInterchangeRecordComparer()).ToList(); List <TaxonomyMetaDataInterchangeRecord> updatableTaxonomyRecord = null; if (validImportRecords.Count() != 0) {// existingTaxonomyPathId = CurrentDbContext.ExecuteQuery <TaxonomyPathAndId>(@"SELECT TaxonomyPath, TaxonomyID FROM V_Taxonomy WHERE TaxonomyPath <> '' AND ProjectId = {0}", CurrentProjectId). ToDictionary(key => key.TaxonomyPath, value => value, StringComparer.OrdinalIgnoreCase); var allImportingTaxonomyPaths = validImportRecords.Select(ad => ad.TaxonomyPath).Distinct().ToList(); ignoredTaxonomyPaths = GetIgnoredTaxonomyPaths(allImportingTaxonomyPaths, existingTaxonomyPathId); updatableTaxonomyRecord = validImportRecords.Where(ad => !ignoredTaxonomyPaths.Contains(ad.TaxonomyPath)).ToList();// GetUpdatableTaxonomyRecord(ignoredTaxonomyPaths, allData); var taxonomyPathEnrichmentImages = updatableTaxonomyRecord.Where(ad => ad.TaxonomyMetaAttributeName == Resources.TaxonomyEnrichmentImageAttributeName) .ToDictionary(item => item.TaxonomyPath, item => item.TaxonomyMetaAttributeValue); //TODO: Change logic so that instead of passing existingTaxonomyPathId pass only the list of tax path that is always in the dictionary var insertedTaxonomyImageValueGuid = GetTaxonomyEnrichmentImageGuid(taxonomyPathEnrichmentImages, existingTaxonomyPathId); //RemoveFailedTaxonomyEnrichmentImageRecord(updatableTaxonomyRecord, insertedTaxonomyImageValueGuid); LogNonImageValues(validImportRecords, _noImageFileTaxEnrichementImage); UpdateSuccessfulEnrichmentImages(updatableTaxonomyRecord, insertedTaxonomyImageValueGuid); var sqlTempTableHelper = new SqlHelper(CurrentInterchangeRecordType); var tempTableName = TempTablePrefix + Guid.NewGuid(); 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(updatableTaxonomyRecord, tempTableName, "tempdb"); var missingAttributesRecordTable = tempTableName + "_missingAttributesRecords"; var currentRemarkId = CurrentRemarkId; //GetDefaultImportUtilRemarkId(); queryResults = CurrentDbContext.ExecuteQuery <string>(@"DECLARE @UserID AS UNIQUEIDENTIFIER DECLARE @ProjectID AS UNIQUEIDENTIFIER DECLARE @ResultText AS VARCHAR(2000) DECLARE @DefaultRemark AS UNIQUEIDENTIFIER DECLARE @Unchanged AS int SET @UserID = '" + ImportRequestedBy + @"' SET @ProjectID = '" + CurrentProjectId + @"' SET @DefaultRemark = '" + CurrentRemarkId + @"' IF OBJECT_ID('tempdb..#IgnoredTaxonomy') IS NOT NULL DROP TABLE #IgnoredTaxonomy IF OBJECT_ID('[tempdb]..[" + missingAttributesRecordTable + @"]') IS NOT NULL DROP TABLE [tempdb]..[" + missingAttributesRecordTable + @"] IF OBJECT_ID('tempdb..#ImportingTaxonomyMetaValue') IS NOT NULL DROP TABLE #ImportingTaxonomyMetaValue IF OBJECT_ID('tempdb..#NewTaxonomyMetaValue') IS NOT NULL DROP TABLE #NewTaxonomyMetaValue IF OBJECT_ID('tempdb..#UpdatableTaxonomyMetaValue') IS NOT NULL DROP TABLE #UpdatableTaxonomyMetaValue IF NOT EXISTS (SELECT ID FROM TaxonomyInfo WHERE ID = '00000000-0000-0000-0000-000000000000') BEGIN INSERT INTO TaxonomyInfo VALUES ('00000000-0000-0000-0000-000000000000', @ProjectID, 0, NULL) END SELECT tt.TaxonomyPath, tt.MetaAttributeName, tt.MetaAttributeValue, '" + Properties.Resources.MetaAttributeNotFoundWarningMessage + @"' AS ErrorMessage INTO [tempdb]..[" + missingAttributesRecordTable + @"] FROM [tempdb]..[" + tempTableName + @"] tt LEFT JOIN Attribute a ON tt.MetaAttributeName = a.attributename AND a.attributeType = '" + AttributeTypeEnum.TaxonomyMeta + @"' WHERE a.Id IS NULL --Delete the data from input table DELETE t1 FROM [tempdb]..[" + tempTableName + @"] t1 JOIN [tempdb]..[" + missingAttributesRecordTable + @"] mar ON mar.TaxonomyPath = t1.TaxonomyPath AND mar.MetaAttributeName = t1.MetaAttributeName --Record to inserted/updated SELECT vtm.TaxonomyID, a1.ID AS MetaAttributeID, tt.MetaAttributeValue INTO #ImportingTaxonomyMetaValue FROM [tempdb]..[" + tempTableName + @"] tt INNER JOIN V_Taxonomy vtm ON tt.TaxonomyPath = vtm.TaxonomyPath INNER JOIN Attribute a1 ON a1.AttributeName = tt.MetaAttributeName AND a1.AttributeType = '" + AttributeTypeEnum.TaxonomyMeta + @"' AND a1.ProjectID = @ProjectID --SELECT * FROM #ImportingTaxonomyMetaValue AS itmv --New Values SELECT NEWID() AS NewTaxonomyMetaInfoId, itm.TaxonomyID, itm.MetaAttributeID, itm.MetaAttributeValue INTO #NewTaxonomyMetaValue FROM #ImportingTaxonomyMetaValue itm LEFT OUTER JOIN TaxonomyMetaInfo tmi ON itm.TaxonomyID = tmi.TaxonomyID AND itm.MetaAttributeID = tmi.MetaAttributeID WHERE tmi.TaxonomyID IS NULL SELECT tmi.ID AS ExistingTaxonomyMetaInfoId, tmd.MetaID, itm.TaxonomyID, itm.MetaAttributeID, itm.MetaAttributeValue, tmd.value INTO #UpdatableTaxonomyMetaValue FROM #ImportingTaxonomyMetaValue itm INNER JOIN TaxonomyMetaInfo tmi ON itm.TaxonomyID = tmi.TaxonomyID AND itm.MetaAttributeID = tmi.MetaAttributeID INNER JOIN TaxonomyMetaData tmd ON tmd.MetaID = tmi.ID WHERE tmd.value <> itm.MetaAttributeValue --AND tmd.Active = 1 SELECT @ResultText = '" + Properties.Resources.IgnoredRecordCountIdentifierText + @"' + '=' + CAST(COUNT(*) AS VARCHAR(50)) + ';' FROM #ImportingTaxonomyMetaValue itm INNER JOIN TaxonomyMetaInfo tmi ON itm.TaxonomyID = tmi.TaxonomyID AND itm.MetaAttributeID = tmi.MetaAttributeID INNER JOIN TaxonomyMetaData tmd ON tmd.MetaID = tmi.ID WHERE tmd.value = itm.MetaAttributeValue AND tmd.Active = 1 --NewTaxMetaInfo INSERT INTO TaxonomyMetaInfo (ID, TaxonomyID, MetaAttributeID) SELECT NewTaxonomyMetaInfoId, TaxonomyID, MetaAttributeID FROM #NewTaxonomyMetaValue --NewTaxMetaData INSERT INTO TaxonomyMetaData (ID, MetaID, Value, CreatedOn, CreatedBy, CreatedRemark, Active) SELECT NEWID(), NewTaxonomyMetaInfoId, MetaAttributeValue, GETDATE(), @UserID, @DefaultRemark, 1 FROM #NewTaxonomyMetaValue SET @ResultText += '" + Properties.Resources.NewRecordCountIdentifierText + @"'+ '=' + CAST(@@ROWCOUNT AS VARCHAR(50)) + ';'; UPDATE TaxonomyMetaData SET Active = 0, DeletedBy = @UserID, DeletedOn = GETDATE(), DeletedRemark = @DefaultRemark FROM TaxonomyMetaData tmd INNER JOIN #UpdatableTaxonomyMetaValue up ON tmd.MetaID = up.ExistingTaxonomyMetaInfoId WHERE tmd.Active = 1 INSERT INTO TaxonomyMetaData (ID, MetaID, VALUE, CreatedOn, CreatedBy, CreatedRemark, Active) SELECT NEWID(), ExistingTaxonomyMetaInfoId, MetaAttributeValue, GETDATE(), @UserID, @DefaultRemark, 1 FROM #UpdatableTaxonomyMetaValue SET @ResultText += '" + Properties.Resources.UpdatedRecordCountIdentifierText + @"'+ '=' + CAST(@@ROWCOUNT AS VARCHAR(50)); SELECT @ResultText DROP TABLE #ImportingTaxonomyMetaValue DROP TABLE #NewTaxonomyMetaValue DROP TABLE #UpdatableTaxonomyMetaValue " ).Single(); recordWithMissingMetaAttributes = CurrentDbContext.ExecuteQuery <TaxonomyMetaDataInterchangeRecord>( @"SELECT TaxonomyPath, MetaAttributeName,MetaAttributeValue FROM [tempdb]..[" + missingAttributesRecordTable + @"] DROP TABLE [tempdb]..[" + missingAttributesRecordTable + @"]") .ToList(); CurrentDbContext.ExecuteCommand(deleteTempTableScript); Summary.Details = ProcessQueryResults(queryResults); } else { _warnings.Add(new WorkerWarning { ErrorMessage = "No Valid Record to import" }); } } ProcessAllWarnings(recordWithMissingMetaAttributes, ignoredTaxonomyPaths, validImportRecords); if (_warnings.Count != 0) { Summary.Warnings = _warnings; Summary.State = WorkerState.CompletedWithWarning; return; } Summary.State = WorkerState.Complete; } catch (IndexOutOfRangeException ex) { var newException = new Exception(Properties.Resources.InvalidRowInInputFileMessage, ex); Summary.SetError(newException); } catch (Exception ex) { Summary.SetError(ex); } }
public override void Run() { string queryResults; try { //initialize the context CurrentDbContext = new AryaDbDataContext(CurrentProjectId, ImportRequestedBy); List <SchemaInterchangeRecord> allData; allData = ImportData.Schemas; //read all the values into a list<T>, change this as its not very efficient and scalable. //TODO: Ask vivek how to dedupe the file //CsvConfiguration conf = GetCurrentConfiguration(); ////char delimiterChar = (char)FieldDelimiter.GetDisplayTextAndDbValue().DbValue; //using (var csvReader = new CsvReader(File.OpenText(InputFilePath), conf)) //{ // allData = csvReader.GetRecordsWithNulls<SchemaInterchangeRecord>().Distinct(new SchemaInterchangeRecordComparer()).ToList(); //} var invalidRecords = allData.GetInvalidRecords(); var schemaInterchangeRecords = invalidRecords as IList <SchemaInterchangeRecord> ?? invalidRecords.ToList(); schemaInterchangeRecords.ToList() .ForEach( ir => _warnings.Add(new WorkerWarning { LineData = ir.ToString(), ErrorMessage = Resources.RequiredValueNullWarningMessage })); var validImportRecords = allData.Except(schemaInterchangeRecords.ToList()).ToList(); //var newRecords = from item in validImportRecords // let attName = item.AttributeName.ToLower() // group item by new { item.TaxonomyPath, attName } // into rec // select new {rec.Key.TaxonomyPath, rec.Key.attName, col = rec};//validImportRecords.GroupBy((sir=>sir.TaxonomyPath,sir=>sir.AttributeName, sir=>sir,(key,g)=>new {tax = key} ) //prepare a table for delete var sqlTempTableHelper = new SqlHelper(CurrentInterchangeRecordType); var tempTableName = TempTablePrefix + Guid.NewGuid(); var warningTableName = tempTableName + "_warning"; var duplicateAttrSameNodeTableName = tempTableName + "_duplicateAttrSameNode"; 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 queryString = @"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 @DefaultTaxonomyID AS UNIQUEIDENTIFIER DECLARE @DefaultImportUtilRemarkID AS UNIQUEIDENTIFIER SET @DefaultImportUtilRemarkID = '" + CurrentRemarkId + @"' SET @UserID = '" + ImportRequestedBy + @"' SET @ProjectID = '" + CurrentProjectId + @"' SET @IgnoredCount = 0 SET @NewDataCount = 0 SET @ResultText = '' IF OBJECT_ID('tempdb..#SI_Data') IS NOT NULL DROP TABLE #SI_Data IF OBJECT_ID('tempdb..#tempGroup') IS NOT NULL DROP TABLE #tempGroup IF OBJECT_ID('[tempdb]..[" + warningTableName + @"]') IS NOT NULL DROP TABLE [tempdb]..[" + warningTableName + @"] IF OBJECT_ID('[tempdb]..[" + duplicateAttrSameNodeTableName + @"]') IS NOT NULL DROP TABLE [tempdb]..[" + duplicateAttrSameNodeTableName + @"] SELECT tdata.*, ft.TaxonomyId, a.ID AS AttributeID, ISNULL(sa.SchemaID,si.ID) AS SchemaID, sa.SchemaDataID INTO #SI_Data FROM [tempdb]..[" + tempTableName + @"] tdata LEFT OUTER JOIN V_Taxonomy ft ON tdata.TaxonomyPath = ft.TaxonomyPath and ft.ProjectId = @ProjectID LEFT OUTER JOIN Attribute a ON LOWER(tdata.AttributeName) = LOWER(a.AttributeName) AND a.AttributeType IN ('Sku','Global', 'Derived', 'Flag') and a.ProjectID = @ProjectID LEFT OUTER JOIN V_Schema sa ON ft.TaxonomyId = sa.TaxonomyId AND a.ID = sa.AttributeID LEFT OUTER JOIN SchemaInfo si ON si.AttributeID = a.ID and si.TaxonomyID = ft.TaxonomyId --Seperate Taxonomy and Attribute that does not exist select sd.TaxonomyPath, sd.AttributeName, sd.NavigationOrder, sd.DisplayOrder,sd.DataType, sd.InSchema INTO [tempdb]..[" + warningTableName + @"] FROM #SI_Data as sd WHERE sd.TaxonomyId IS NULL OR sd.AttributeID IS NULL --Delete record that has taxonomy or attribute does not exits in the db DELETE #SI_Data FROM #SI_Data sd WHERE sd.TaxonomyId IS NULL OR sd.AttributeID IS NULL --Find out the Duplicate attribute on taxonomy SELECT sd.TaxonomyId, sd.AttributeID, count(*) as Count INTO #tempGroup FROM #SI_Data as sd GROUP BY sd.TaxonomyId, sd.AttributeID --Insert All the import data so that it can read later SELECT sd.TaxonomyPath, sd.AttributeName, sd.NavigationOrder, sd.DisplayOrder,sd.DataType, sd.InSchema INTO [tempdb]..[" + duplicateAttrSameNodeTableName + @"] FROM #tempGroup tg INNER JOIN #SI_Data sd ON sd.TaxonomyId = tg.TaxonomyId AND sd.AttributeID = tg.AttributeID Where tg.Count >1 --remove from insert table DELETE #SI_Data FROM #SI_Data si INNER JOIN #tempGroup gt ON gt.TaxonomyId=si.TaxonomyId AND gt.AttributeID = si.AttributeID Where gt.Count >1 --Insert Schema Info for those that does not have entry already INSERT INTO SchemaInfo SELECT NEWID(), TaxonomyID, AttributeID FROM #SI_Data WHERE SchemaID IS NULL SET @ResultText = '" + Resources.NewRecordCountIdentifierText + @"'+ '=' + CAST(@@ROWCOUNT AS VARCHAR(50))+ ';' UPDATE #SI_Data SET SchemaID = si.ID FROM #SI_Data sid JOIN SchemaInfo si ON sid.TaxonomyID = si.TaxonomyID AND sid.AttributeID = si.AttributeID WHERE SchemaID IS NULL --Remove similar data in file and db DELETE #SI_Data FROM #SI_Data sd INNER JOIN V_Schema sa ON sd.SchemaID = sa.SchemaID WHERE ( sd.NavigationOrder = sa.NavigationOrder AND sd.Displayorder = sa.DisplayOrder AND sd.DataType = sa.DataType AND isnull(sd.InSchema, 1) = sa.InSchema ) SET @ResultText += '" + Resources.IgnoredRecordCountIdentifierText + @"' + '=' + CAST(@@ROWCOUNT AS VARCHAR(50))+ ';' --Deactivte schema data for already existing entries Update SchemaData SET Active = 0, DeletedBy = @UserID, DeletedOn = GETDATE(), DeletedRemark = @DefaultImportUtilRemarkID FROM SchemaData sd INNER JOIN #SI_Data sa ON sd.SchemaID = sa.SchemaID WHERE sd.Active = 1 SET @ResultText += '" + Resources.UpdatedRecordCountIdentifierText + @"'+ '=' + CAST(@@ROWCOUNT AS VARCHAR(50)) + ';'; --INSERT Schema data for all the infos for new data INSERT INTO SchemaData (ID, SchemaID, InSchema, DataType, NavigationOrder,DisplayOrder, CreatedOn, CreatedBy,CreatedRemark, Active) SELECT NEWID(), si.SchemaID, si.InSchema, si.DataType, si.NavigationOrder, si.DisplayOrder, GETDATE(), @UserID,@DefaultImportUtilRemarkID, 1 from #SI_Data si DROP TABLE #SI_Data SELECT @ResultText"; queryResults = CurrentDbContext.ExecuteQuery <string>(queryString).Single(); //delete the temp table. var duplicateAttrSingleTaxonomyRecords = CurrentDbContext.ExecuteQuery <SchemaInterchangeRecord>( @"SELECT w.TaxonomyPath, w.AttributeName, w.NavigationOrder, w.DisplayOrder,w.DataType, w.InSchema FROM [tempdb]..[" + duplicateAttrSameNodeTableName + @"] w ").ToList(); foreach (var itemTaxonomyWarning in duplicateAttrSingleTaxonomyRecords) { _warnings.Add(new WorkerWarning { LineData = itemTaxonomyWarning.ToString(), ErrorMessage = Resources.DuplicateAttributeSingleTaxonomyWarningMessage }); } var warningsRecords = CurrentDbContext.ExecuteQuery <SchemaInterchangeRecord>( @"SELECT w.TaxonomyPath, w.AttributeName, w.NavigationOrder, w.DisplayOrder,w.DataType, w.InSchema FROM [tempdb]..[" + warningTableName + @"] w").ToList(); //CurrentDbContext.ExecuteCommand(deleteTempTableScript); foreach (var itemTaxonomyWarning in warningsRecords) { _warnings.Add(new WorkerWarning { LineData = itemTaxonomyWarning.ToString(), ErrorMessage = Resources.TaxonomyOrAttributeMissingWarningMessage }); } ProcessSummaryReport(queryResults); //delete temperorary table CurrentDbContext.ExecuteCommand(deleteTempTableScript); } catch (IndexOutOfRangeException ex) { var newException = new Exception(Resources.InvalidRowInInputFileMessage, ex); Summary.SetError(newException); } catch (Exception ex) { Summary.SetError(ex); } }