public static string TagDelegateTwoLevel(MappingColumn column, ProcessedImportRow processedImportRow)
{
string mainCategory = processedImportRow.Row[column.TemplateColumnOrdinal - 1] == DBNull.Value ? "" : processedImportRow.Row[column.TemplateColumnOrdinal - 1].ToString().Trim();
string subCategory = processedImportRow.Row[column.TemplateColumnOrdinal] == DBNull.Value ? "" : processedImportRow.Row[column.TemplateColumnOrdinal].ToString().Trim();
//main category and all sub category is empty, we can continue with the import without categories
if (string.IsNullOrWhiteSpace(mainCategory) && string.IsNullOrWhiteSpace(subCategory))
{
column.IgnoreThisTable = true;
return("");
}
//only main Category is available
if (!string.IsNullOrWhiteSpace(mainCategory) && string.IsNullOrWhiteSpace(subCategory))
{
LookupValue categoryLookup = column.LookupValues.Where(tag => tag.Lookup == mainCategory.ToUpperInvariant()).FirstOrDefault();
if (categoryLookup != null)
{
return(categoryLookup.ReplacementId.ToString());
}
processedImportRow.AddException(new ImportDataException {
ExceptionType = ExceptionType.CategoryIsNotValid, TemplateMappingColumn = column.TemplateMappingColumn, TemplateColumnName = column.TemplateColumnName, IsValid = false
});
return("");
}
//We have main category and subcategory level 2
if (!string.IsNullOrWhiteSpace(mainCategory) && !string.IsNullOrWhiteSpace(subCategory))
{
string category = string.Format("{0}||{1}", mainCategory, subCategory).ToUpperInvariant();
LookupValue categoryLookup = column.LookupValues.Where(tag => tag.Lookup == category).FirstOrDefault();
if (categoryLookup != null)
{
return(categoryLookup.ReplacementId.ToString());
}
processedImportRow.AddException(new ImportDataException {
ExceptionType = ExceptionType.CategoryIsNotValid, TemplateMappingColumn = column.TemplateMappingColumn, TemplateColumnName = column.TemplateColumnName, IsValid = false
});
return("");
}
//All other conditions are invalid hierarchy
processedImportRow.AddException(new ImportDataException {
ExceptionType = ExceptionType.CategoryIsNotValid, TemplateMappingColumn = column.TemplateMappingColumn, TemplateColumnName = column.TemplateColumnName, IsValid = false
});
return("");
}
private static string InsertStringFor(MappingTable mappingTable, ProcessedImportRow processedImportRow, GeneratorDelegates generatorDelegates)
{
StringBuilder sb = new StringBuilder();
string columnNames;
string columnValues;
Dictionary <string, string> updateColumns;
foreach (Func <MappingTable, DataRow, ImportDataException> tableValidation in generatorDelegates.TableValidations)
{
processedImportRow.AddException(tableValidation(mappingTable, processedImportRow.Row));
}
foreach (Func <MappingTable, DataRow, string> injectSql in generatorDelegates.InjectSql)
{
sb.AppendLine(injectSql(mappingTable, processedImportRow.Row));
}
if (!string.IsNullOrWhiteSpace(mappingTable.StartQueryWith))
{
sb.AppendLine(mappingTable.StartQueryWith);
}
bool ignoreThisTable = GetTableAttributes(mappingTable, processedImportRow, generatorDelegates, out columnNames, out columnValues, out updateColumns);
if (!ignoreThisTable && mappingTable.GenerateQuery)
{
if (mappingTable.DmlType == DmlType.Insert)
{
sb.AppendFormat("Insert into {0} ({1}) values ({2})", mappingTable.TableName, columnNames,
columnValues);
}
else if (mappingTable.DmlType == DmlType.Update)
{
sb.AppendFormat("Update {0} Set ", mappingTable.TableName);
foreach (KeyValuePair <string, string> column in updateColumns)
{
sb.AppendFormat("{0} = {1}", column.Key, column.Value);
}
}
}
return(sb.ToString());
}
public static string ReplacementFor(this HashSet<LookupValue> lookupValues, MappingColumn column, string importValue,ProcessedImportRow processedImportRow)
{
if (string.IsNullOrWhiteSpace(importValue))
return "";
foreach (LookupValue lookupValue in lookupValues)
{
if (lookupValue.Lookup == importValue)
return lookupValue.ReplacementId.ToString();
}
processedImportRow.AddException(new ImportDataException
{
ExceptionType = ExceptionType.LookupMissing,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn
});
return "";
}
public static string ValidateAgainstDbSetting(this string importValue, MappingColumn column, ProcessedImportRow processedImportRow)
{
if (column.ValidationRules != null)
{
foreach (var rule in column.ValidationRules)
{
if (string.IsNullOrEmpty(importValue) && (rule.Mandatory || column.Mandatory))
{
processedImportRow.AddException
(new ImportDataException
{
ExceptionType = column.StarchefColumnName == "tag_id" ? ExceptionType.CategoryRequired : ExceptionType.DbSettingIsMandatory,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn
});
}
if (rule.StringLength < int.MaxValue && rule.StringLength > 0 && importValue.Length > rule.StringLength)
{
processedImportRow.AddException
(new ImportDataException
{
ExceptionType = ExceptionType.DbSettingMaxLength,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn,
MessageParam1 = rule.StringLength.ToString()
});
}
if (rule.MinimumValue != rule.MaximumValue && !string.IsNullOrEmpty(importValue))
{
//TODO:candidate for refactoring, perhaps the whole class
decimal value;
if (decimal.TryParse(importValue, out value))
{
if (value < rule.MinimumValue)
{
processedImportRow.AddException
(new ImportDataException
{
ExceptionType = ExceptionType.DbSettingMinimumValue,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn,
MessageParam1 = rule.MinimumValue.ToString(),
MessageParam2 = rule.MaximumValue.ToString()
});
}
if (value > rule.MaximumValue)
{
processedImportRow.AddException(new ImportDataException
{
ExceptionType = ExceptionType.DbSettingMaximumValue,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn,
MessageParam1 = rule.MinimumValue.ToString(),
MessageParam2 = rule.MaximumValue.ToString()
});
}
}
else
{
processedImportRow.AddException
(new ImportDataException
{
ExceptionType = ExceptionType.DbSettingMinimumValue,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn,
MessageParam1 = rule.MinimumValue.ToString(),
MessageParam2 = rule.MaximumValue.ToString()
});
}
}
if (!string.IsNullOrEmpty(rule.RegEx))
{
Regex regex = new Regex(rule.RegEx);
if (!regex.Match(importValue).Success)
{
processedImportRow.AddException
(new ImportDataException
{
ExceptionType = ExceptionType.DbSettingRegEx,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn,
MessageParam1 = rule.RegEx,
});
}
}
}
}
return(importValue);
}
private static bool GetTableAttributes(MappingTable mappingTable, ProcessedImportRow processedImportRow, GeneratorDelegates generatorDelegates, out string columnNames, out string columnValues, out Dictionary <string, string> columnNamesValues)
{
StringBuilder columnNamesSb = new StringBuilder();
StringBuilder columnValuesSb = new StringBuilder();
bool ignoreThisTable = false;
Dictionary <string, string> updateCollection = new Dictionary <string, string>();
foreach (MappingColumn column in mappingTable.MappingColumns.Where(i => !i.IgnoreForImport))
{
if (column.TemplateColumnOrdinal == -1 && !string.IsNullOrWhiteSpace(column.TemplateColumnName)) // when a column is missing in Excel
{
if (column.Mandatory)
{
processedImportRow.AddException(new ImportDataException
{
ExceptionType = ExceptionType.MandatoryInTable,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn
});
}
continue;
}
string columnValue = column.TemplateColumnOrdinal == -1 ? column.DefaultValue.WithSqlSyntax(column.Datatype) : GetImportValueOf(column, processedImportRow, generatorDelegates);
if (column.Mandatory && columnValue == "NULL")
{
processedImportRow.AddException(new ImportDataException
{
ExceptionType = ExceptionType.MandatoryInTable,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn
});
continue;
}
if (columnValue == "INVALID")
{
processedImportRow.AddException(new ImportDataException
{
ExceptionType = ExceptionType.LookupMissing,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn
});
continue;
}
columnValuesSb.Append(columnValue + ",");
if (column.IgnoreThisTable)
{
//Note : not neat, can't think of any eligent solution now
ignoreThisTable = column.IgnoreThisTable;
column.IgnoreThisTable = false;
}
columnNamesSb.Append(column.StarchefColumnName + ",");
updateCollection.Add(column.StarchefColumnName, columnValue);
}
columnNames = columnNamesSb.ToString().TrimEnd(',');
columnValues = columnValuesSb.ToString().TrimEnd(',');
columnNamesValues = updateCollection;
return(ignoreThisTable);
}