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) }); } }