private bool DuplicateTaxonomyIndb(ImportArgs importArgs)
{
using (var CurrentDbContext = new AryaDbDataContext(importArgs.ProjectId, importArgs.UserId))
{
return(CurrentDbContext.ExecuteQuery <string>(@"SELECT TaxonomyPath
FROM V_Taxonomy
WHERE TaxonomyPath <> ''
AND ProjectId = {0} GROUP BY TaxonomyPath Having Count(*) > 1", importArgs.ProjectId).Count() > 0);
}
}
protected void DisplayProjectList()
{
var availableProjects = new List <ListItem>();
using (var Dc = new AryaDbDataContext())
{
var currUser =
(from u in Dc.Users where u.EmailAddress == ((SiteMaster)Page.Master).Email select u)
.FirstOrDefault();
var userID = currUser.ID;
var userName = currUser.FullName;
var userRole = currUser.IsAdmin;
userId.Value = userID.ToString();
var existingDatabases = Dc.ExecuteQuery <string>("select name from sys.databases").ToList();
if (!userRole)
{
var projects = (from ug in currUser.UserProjects
where
ug.GroupID == Group.ImportAdminGroup ||
ug.GroupID == Group.ImportUserGroup &&
existingDatabases.Contains(ug.Project.DatabaseName)
orderby ug.Project.DatabaseName ascending
select ug.Project).Distinct();
availableProjects =
projects.Select(p => new ListItem {
Text = p.ClientDescription + " " + p.SetName
}).ToList();
}
else
{
var projects =
(from p in Dc.Projects
where existingDatabases.Contains(p.DatabaseName)
orderby p.DatabaseName ascending
select p);
availableProjects =
projects.Select(p => new ListItem {
Text = p.ClientDescription + " " + p.SetName
})
.Distinct()
.ToList();
}
ddlProject.Items.Insert(0, "Select a Project");
availableProjects.ForEach(ap => ddlProject.Items.Add(ap));
}
}
private void FetchProjects()
{
using (var dc = new AryaDbDataContext())
{
var currUser =
(from u in dc.Users where u.EmailAddress == ((SiteMaster)Page.Master).Email select u)
.FirstOrDefault();
if (currUser == null)
{
throw new UnauthorizedAccessException();
}
UserId.Value = currUser.ID.ToString();
var p = currUser.IsAdmin
? dc.Projects
: currUser.UserProjects.Where(
dp =>
dp.GroupID == Group.ExportAdminGroup || dp.GroupID == Group.StandardExportUserGroup ||
dp.GroupID == Group.CustomExportUserGroup).Select(dp => dp.Project);
var existingDatabases = dc.ExecuteQuery <string>("SELECT LOWER(name) FROM sys.databases").ToList();
p = p.Where(dp => existingDatabases.Contains(dp.DatabaseName.ToLower())).Select(dp => dp);
var availableProjects =
p.Select(
dp =>
new ListItem
{
Text = string.Format("{0} {1}", dp.ClientDescription, dp.SetName),
Value = dp.ID.ToString()
}).Distinct().OrderBy(li => li.Text).ToList();
availableProjects.Insert(0, new ListItem("---Select a Model---", "---Select a Model---"));
ddlModel.DataSource = availableProjects;
ddlModel.DataBind();
if (availableProjects.Count == 1)
{
ddlModel.SelectedIndex = 1;
FetchExportTypes();
}
}
}
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
{
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()
{
//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
{
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()
{
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);
}
}
