public List <ErrorTemplates> UniqueProductVariant(List <IGrouping <string, Mapping> > query, int product_index, int variant_index)
{
List <ErrorTemplates> errorTemp = new List <ErrorTemplates>();
List <string> List = new List <string>();
List <Mapping> maper = new List <Mapping>();
if (product_index != 0 && variant_index != 0)
{
List <List <Mapping> > mappin = new List <List <Mapping> >();
foreach (var key in query)
{
List <Mapping> tempMap = new List <Mapping>();
foreach (var item in key)
{
Mapping map = new Mapping();
map.c1 = item.c1;
map.row = item.row;
tempMap.Add(map);
}
if (tempMap.Distinct().Count() != tempMap.Count())
{
foreach (var item2 in tempMap)
{
ErrorTemplates error = new ErrorTemplates();
error.ErrorType = "UniqueProductVariantError";
error.Field_1 = item2.row.ToString();
errorTemp.Add(error);
}
}
}
}
return(errorTemp.OrderBy(x => x.Row).ToList());
}
public List <ErrorTemplates> Unique(List <IGrouping <string, Mapping> > query, int ErpId, string Column)
{
List <ErrorTemplates> errorTemp = new List <ErrorTemplates>();
if (ErpId != 0)
{
List <string> List = new List <string>();
List <Mapping> maper = new List <Mapping>();
List <Mapping> tempMap = new List <Mapping>();
foreach (var key in query)
{
Mapping map = new Mapping();
map.c1 = key.Key;
foreach (var item in key)
{
map.row = item.row;
}
maper.Add(map);
}
foreach (var item in maper)
{
ErrorTemplates error = new ErrorTemplates();
error.ErrorType = "Not Unique " + Column;
error.Field_1 = item.row.ToString();
errorTemp.Add(error);
}
}
return(errorTemp.OrderBy(x => x.Row).ToList());
}
public List <ErrorTemplates> EmailCheck(ExcelWorksheet file, int columnIndex, string field)
{
List <ErrorTemplates> errorTemp = new List <ErrorTemplates>();
if (columnIndex != 0)
{
for (int i = file.Dimension.Start.Row + 1; i <= file.Dimension.End.Row; i++)
{
var value = file.Cells[i, columnIndex].Value;
string newValue = "";
if (value != null)
{
newValue = value.ToString();
}
RegexUtilities util = new RegexUtilities();
if (value != null)
{
if (!util.IsValidEmail(newValue))
{
ErrorTemplates error = new ErrorTemplates();
error.ErrorType = "Email Format";
error.ErrorComments = "Email Format is incorrect";
error.Field_1 = field;
error.Row = i;
errorTemp.Add(error);
}
}
}
}
return(errorTemp.OrderBy(x => x.Row).ToList());
}
public List <ErrorTemplates> CheckPhoneDigit(ExcelWorksheet file, int ESM_flag)
{
List <ErrorTemplates> errorTemp = new List <ErrorTemplates>();
int phone = 0;
if (ESM_flag != 0)
{
for (int i = file.Dimension.Start.Row + 1; i <= file.Dimension.End.Row; i++)
{
phone = i;
try
{
if (file.Cells[i, ESM_flag].Value != null)
{
string number = file.Cells[i, ESM_flag].Value.ToString().Trim();
int count = number.Length;
if (number.Any(x => char.IsLetter(x)))
{
ErrorTemplates errors = new ErrorTemplates();
errors.ErrorType = "Phone Number is incorrect";
errors.Field_1 = "ESM Contact Number";
errors.Row = phone;
errors.ErrorComments = "phone number should only contain Numeric values";
// errors.LinkRow = i;
errorTemp.Add(errors);
}
else if (count != 10)
{
ErrorTemplates errors = new ErrorTemplates();
errors.ErrorType = "Phone Number is incorrect ";
errors.Field_1 = "ESM Contact Number";
errors.Row = i;
errors.ErrorComments = "phone number should of 10 digit";
// errors.LinkRow = i;
errorTemp.Add(errors);
}
}
}
catch
{
ErrorTemplates errors = new ErrorTemplates();
errors.ErrorType = "Phone Number is incorrect";
errors.Field_1 = "ESM Contact Number";
errors.Row = phone;
errors.ErrorComments = "phone number should of 10 digit";
// errors.LinkRow = i;
errorTemp.Add(errors);
}
}
}
return(errorTemp.OrderBy(x => x.Row).ToList());
}
public List <ErrorTemplates> CheckStateAndDistrict(ExcelWorksheet file, int flag_State, int flag_District)
{
List <ErrorTemplates> errorTemp = new List <ErrorTemplates>();
StateDistrict beatDistrict = new StateDistrict();
for (int i = file.Dimension.Start.Row + 1; i <= file.Dimension.End.Row; i++)
{
if (flag_State != 0 && flag_District != 0)
{
var statevalue = file.Cells[i, flag_State].Value;
var districtValue = file.Cells[i, flag_District].Value;
if (statevalue != null)
{
var newValue = statevalue.ToString().Replace(" ", "").ToLower();
var listState = beatDistrict.GetAllStates();
var boole = listState.Contains(newValue);
if (boole == false)
{
ErrorTemplates error = new ErrorTemplates();
error.ErrorType = "State";
error.ErrorComments = "State field is out of the list";
error.Field_1 = newValue;
error.Row = i;
errorTemp.Add(error);
}
else
{
if (districtValue != null)
{
var newValue2 = districtValue.ToString().Replace(" ", "").ToLower();
var listDistrict = beatDistrict.GetDistrictsOfState(newValue);
var boole2 = listDistrict.Contains(newValue2);
if (boole2 == false)
{
ErrorTemplates error = new ErrorTemplates();
error.ErrorType = "District";
error.ErrorComments = "District field is out of the list for State " + newValue;
error.Field_1 = newValue2;
error.Row = i;
errorTemp.Add(error);
}
}
}
}
}
}
return(errorTemp.OrderBy(x => x.Row).ToList());
}
public List <ErrorTemplates> AttributeMapping(List <IGrouping <string, Mapping> > query1, int flag_coloumn, int map_coloumn, string flagString, string mapString)
{
List <ErrorTemplates> errorTemp = new List <ErrorTemplates>();
try
{
if (flag_coloumn != 0 && map_coloumn != 0)
{
List <List <Mapping> > mappin = new List <List <Mapping> >();
foreach (var key in query1)
{
List <Mapping> tempMap = new List <Mapping>();
foreach (var item in key)
{
Mapping map = new Mapping();
map.c1 = item.c1;
map.row = item.row;
tempMap.Add(map);
}
mappin.Add(tempMap);
}
foreach (var item in mappin)
{
var comparer = item;
var list1 = item.Select(x => x.c1).ToList();
var key1 = list1.Distinct().Count();
if (key1 != 1)
{
foreach (var num in item)
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Attribute Mapping";
temp.Field_1 = flagString;
temp.Field_2 = mapString;
temp.Row = num.row;
errorTemp.Add(temp);
}
}
}
}
}
catch (Exception ex)
{
var x = ex.Message;
var temp = flag_coloumn;
var temp1 = map_coloumn;
}
return(errorTemp.OrderBy(x => x.Row).ToList());
}
public List <ErrorTemplates> One2OneValidationCheck(List <IGrouping <string, Mapping> > query1, int flag_coloumn, int map_coloumn, string flagString, string mapString)
{
List <ErrorTemplates> errorTemp = new List <ErrorTemplates>();
if (flag_coloumn != 0 && map_coloumn != 0)
{
List <List <Mapping> > mappin = new List <List <Mapping> >();
foreach (var key in query1)
{
List <Mapping> tempMap = new List <Mapping>();
foreach (var item in key)
{
Mapping map = new Mapping();
map.c1 = item.c1;
map.row = item.row;
tempMap.Add(map);
}
mappin.Add(tempMap);
}
foreach (var item in mappin)
{
var comparer = item;
var list1 = item.Select(x => x.c1).ToList();
var key1 = list1.Distinct().Count();
if (key1 != 1)
{
foreach (var num in item)
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "One to One mapping";
temp.Field_1 = flagString;
temp.Field_2 = mapString;
temp.Row = num.row;
errorTemp.Add(temp);
}
}
foreach (var item2 in mappin)
{
var list2 = item2.Select(x => x.c1).ToList();
if (item != item2)
{
var differenceMap = list1.Intersect(list2).ToList();
if (differenceMap.Count() != 0)
{
List <int> q = new List <int>();
List <int> r = new List <int>();
foreach (var key in differenceMap)
{
q.AddRange(item.Where(x => x.c1 == key).Select(x => x.row).ToList());
r.AddRange(item2.Where(x => x.c1 == key).Select(x => x.row).ToList());
}
foreach (var num in q.Distinct())
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "One to One mapping";
temp.Field_1 = flagString;
temp.Field_2 = mapString;
temp.Row = num;
errorTemp.Add(temp);
}
foreach (var num in r.Distinct())
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "One to One mapping";
temp.Field_1 = flagString;
temp.Field_2 = mapString;
temp.Row = num;
errorTemp.Add(temp);
}
}
}
}
}
}
return(errorTemp.OrderBy(x => x.Row).ToList());
}
public List <ErrorTemplates> HierarchyError(ExcelWorksheet file, int ESM_index, int ASM_index, int RSM_index, int ZSM_index, int NSM_index)
{
List <ErrorTemplates> errorTemp = new List <ErrorTemplates>();
for (int i = file.Dimension.Start.Row + 1; i <= file.Dimension.End.Row; i++)
{
if (ESM_index == 0)
{
break;
}
var flag_ESM = file.Cells[i, ESM_index].Value;
if (flag_ESM != null)
{
if (ASM_index == 0)
{
if (RSM_index != 0)
{
var flag_RSM = file.Cells[i, RSM_index].Value;
if (flag_RSM != null)
{
ErrorTemplates errors = new ErrorTemplates();
errors.ErrorType = "Error Hierachy is broken";
errors.Field_1 = "ASM";
errors.ErrorComments = "Header ASM is missing";
errorTemp.Add(errors);
break;
}
}
}
else
{
var flag_ASM = file.Cells[i, ASM_index].Value;
if (flag_ASM == null)
{
if (RSM_index != 0)
{
var flag_RSM = file.Cells[i, RSM_index].Value;
if (flag_RSM != null)
{
ErrorTemplates errors = new ErrorTemplates();
errors.ErrorType = "Error Hierachy is broken";
errors.Field_1 = "ASM";
errors.ErrorComments = "ASM is not present";
errorTemp.Add(errors);
break;
}
}
}
}
}
}
for (int i = file.Dimension.Start.Row + 1; i <= file.Dimension.End.Row; i++)
{
if (ASM_index == 0)
{
break;
}
var flag_ASM = file.Cells[i, ASM_index].Value;
if (flag_ASM != null)
{
if (RSM_index == 0)
{
if (ZSM_index != 0)
{
var flag_ZSM = file.Cells[i, ZSM_index].Value;
if (flag_ZSM != null)
{
ErrorTemplates errors = new ErrorTemplates();
errors.ErrorType = "Error Hierachy is broken";
errors.Field_1 = "RSM";
errors.Row = i;
errors.ErrorComments = "Header RSM is missing";
errorTemp.Add(errors);
}
}
}
else
{
var flag_RSM = file.Cells[i, RSM_index].Value;
if (flag_RSM == null)
{
if (ZSM_index != 0)
{
var flag_ZSM = file.Cells[i, ZSM_index].Value;
if (flag_ZSM != null)
{
ErrorTemplates errors = new ErrorTemplates();
errors.ErrorType = "Error Hierachy is broken";
errors.Field_1 = "RSM";
errors.Row = i;
errors.ErrorComments = "RSM is not present";
errorTemp.Add(errors);
}
}
}
}
}
}
for (int i = file.Dimension.Start.Row + 1; i <= file.Dimension.End.Row; i++)
{
if (RSM_index == 0)
{
break;
}
var flag_RSM = file.Cells[i, RSM_index].Value;
if (flag_RSM != null)
{
if (ZSM_index == 0)
{
if (NSM_index != 0)
{
var flag_NSM = file.Cells[i, NSM_index].Value;
if (flag_NSM != null)
{
ErrorTemplates errors = new ErrorTemplates();
errors.ErrorType = "Error Hierachy is broken";
errors.Field_1 = "ZSM";
errors.Row = i;
errors.ErrorComments = "headerZSM is missing";
}
}
}
else
{
var flag_ZSM = file.Cells[i, ZSM_index].Value;
if (flag_ZSM == null)
{
if (NSM_index != 0)
{
var flag_NSM = file.Cells[i, NSM_index].Value;
if (flag_NSM != null)
{
ErrorTemplates errors = new ErrorTemplates();
errors.ErrorType = "Error Hierachy is broken";
errors.Field_1 = "ZSM";
errors.Row = i;
errors.ErrorComments = "ZSM is not present";
}
}
}
}
}
}
return(errorTemp.OrderBy(x => x.Row).ToList());
}
public List <ErrorTemplates> One2ManyValidationCheck(List <IGrouping <string, Mapping> > query1, int flag_coloumn, int map_coloumn, string flagString, string mapString)
{
List <ErrorTemplates> errorTemp = new List <ErrorTemplates>();
int count1 = 0;
int count2 = 0;
try
{
// var flagCell = file.Cells[start_row, start_coloumn];
if (flag_coloumn != 0 && map_coloumn != 0)
{
List <List <Mapping> > mappin = new List <List <Mapping> >();
foreach (var key in query1)
{
List <Mapping> tempMap = new List <Mapping>();
foreach (var item in key)
{
Mapping map = new Mapping();
map.c1 = item.c1;
map.row = item.row;
tempMap.Add(map);
}
mappin.Add(tempMap);
}
foreach (var item in mappin)
{
var comparer = item;
var list1 = item.Select(x => x.c1).Distinct().ToList();
foreach (var item2 in mappin)
{
var list2 = item2.Select(x => x.c1).Distinct().ToList();
if (item != item2)
{
var differenceMap = list2.Intersect(list1).ToList();
List <Mapping> newItem = new List <Mapping>();
newItem.AddRange(item);
newItem.AddRange(item2);
if (differenceMap.Count() != 0)
{
List <int> q = new List <int>();
List <Rows> r = new List <Rows>();
foreach (var key in differenceMap)
{
q.AddRange(newItem.Where(x => x.c1 == key).Select(x => x.row).ToList());
}
foreach (var num1 in q.Distinct())
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "One to Many mapping";
temp.Field_1 = flagString;
temp.Field_2 = mapString;
temp.Row = num1;
errorTemp.Add(temp);
}
}
}
}
}
}
return(errorTemp.OrderBy(x => x.Row).ToList());
}
catch (Exception ex)
{
var x = ex.Message;
var c = count1;
var c2 = count2;
}
return(null);
}
public List <ErrorTemplates> Checker(ExcelWorksheet sheet, List <FileHeadersBeatHierarchy> list1, List <FileHeadersLocationAddtion> list2, List <FileHeadersProductAddition> list3, List <FileHeadersBeatPlanAddition> list4)
{
List <ErrorTemplates> newError = new List <ErrorTemplates>();
columnIndex indexer = new columnIndex();
MappingValidations checks = new MappingValidations();
var file = sheet.Cells[sheet.Dimension.Start.Row, sheet.Dimension.Start.Column, sheet.Dimension.Start.Row, sheet.Dimension.End.Column];
for (int i = sheet.Dimension.Start.Column - 1; i <= sheet.Dimension.End.Column - 1; i++) //To find Empty cells.(algo will be updated)
{
var value = ((object[, ])file.Value)[0, i];
if (value != null)
{
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == ("NSM").ToLower())
{
indexer.NSM = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == ("NSMZone").ToLower())
{
indexer.NSMZone = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == ("NSMEmailId").ToLower())
{
indexer.NSMEmailId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == ("NSMSecondaryEmailId").ToLower())
{
indexer.NSMSecondaryEmailId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == ("ZSM").ToLower())
{
indexer.ZSM = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == ("ZSMZone").ToLower())
{
indexer.ZSMZone = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == ("ZSMEmailId").ToLower())
{
indexer.ZSMEmailId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == ("ZSMSecondaryEmailId").ToLower())
{
indexer.ZSMSecondaryEmailId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == ("RSM").ToLower())
{
indexer.RSM = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "RSMZone".ToLower())
{
indexer.RSMZone = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "RSMEmailId".ToLower())
{
indexer.RSMEmailId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "RSMSecondaryEmailId".ToLower())
{
indexer.RSMSecondaryEmailId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ASM".ToLower())
{
indexer.ASM = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ASMZone".ToLower())
{
indexer.ASMZone = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ASMEmailId".ToLower())
{
indexer.ASMEmailId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ASMSecondaryEmailId".ToLower())
{
indexer.ASMSecondaryEmailId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ESM".ToLower())
{
indexer.ESM = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ESMZone".ToLower())
{
indexer.ESMZone = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ESMEmailId".ToLower())
{
indexer.ESMEmailId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ESMSecondaryEmailId".ToLower())
{
indexer.ESMSecondaryEmailId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ESMContactNumber".ToLower())
{
indexer.ESMContactNumber = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ESMHQ".ToLower())
{
indexer.ESMHQ = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ESMErpId".ToLower())
{
indexer.ESMErpId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "FinalBeatName".ToLower())
{
indexer.FinalBeatName = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "BeatErpId".ToLower())
{
indexer.BeatErpId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "BeatDistrict".ToLower())
{
indexer.BeatDistrict = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "BeatState".ToLower())
{
indexer.BeatState = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "BeatZone".ToLower())
{
indexer.BeatZone = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "DistributorName".ToLower())
{
indexer.DistributorName = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "DistributorLocation".ToLower())
{
indexer.DistributorLocation = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "DistributorErpId".ToLower())
{
indexer.DistributorErpId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "DistributorEmailId".ToLower())
{
indexer.DistributorEmailId = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ShopName".ToLower())
{
indexer.ShopName = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ShopErpId".ToLower())
{
indexer.ShopName = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "PrimaryCategory".ToLower())
{
indexer.PrimaryCategory = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "SecondaryCategory".ToLower())
{
indexer.SecondaryCategory = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "Product".ToLower())
{
indexer.Product = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "Variant".ToLower())
{
indexer.Variant = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "Price".ToLower())
{
indexer.Price = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "Unit".ToLower())
{
indexer.Unit = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "DisplayCategory".ToLower())
{
indexer.DisplayCategory = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "Image".ToLower())
{
indexer.Image = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "Description".ToLower())
{
indexer.Description = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "StandardUnitConversionFactor".ToLower())
{
indexer.StandardUnitConversionFactor = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "StandardUnit".ToLower())
{
indexer.StandardUnit = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ProductCode".ToLower())
{
indexer.ProductCode = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "VariantCode".ToLower())
{
indexer.VariantCode = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "ProductCategory".ToLower())
{
indexer.ProductCategory = i + 1;
}
if (((object[, ])file.Value)[0, i].ToString().Trim().Replace(" ", "").ToLower() == "BeatDay".ToLower())
{
indexer.BeatDay = i + 1;
}
}
}
//hierarchy break
if (list1 != null)
{
foreach (var item in list1)
{
if (item.NSM == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "NSM";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.NSMEmailId == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "NSMEmaialId";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.ZSM == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "ZSM";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.ZSMEmailId == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "ZSMEmailId";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.RSM == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "RSM";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.RSMEmailId == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "RSMEmaiilId";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.ASM == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "ASM";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.ASMEmailId == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "ASMEmaiId";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.ESM == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "ESM";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.DistributorName == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "DistributorName";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.ESMContactNumber == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "ESMContactNumber";
temp.Row = item.Row;
newError.Add(temp);
}
}
newError.AddRange(checks.HierarchyError(sheet, indexer.ESM, indexer.ASM, indexer.RSM, indexer.ZSM, indexer.NSM));
//Phone Digits checking
newError.AddRange(checks.CheckPhoneDigit(sheet, indexer.ESMContactNumber));
//Relationship Check
var query1 = list1.GroupBy(x => x.NSM, x => new Mapping {
c1 = x.NSMZone, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query1, indexer.NSM, indexer.NSMZone, "NSM", "NSMZone"));
//Nsm to Nsm EmailID
var query2 = list1.GroupBy(x => x.NSM, x => new Mapping {
c1 = x.NSMEmailId, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query2, indexer.NSM, indexer.NSMEmailId, "NSM", "NSMEmailId"));
//Nsm to Secondary EmailID
var query3 = list1.GroupBy(x => x.NSM, x => new Mapping {
c1 = x.NSMSecondaryEmailId, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query3, indexer.NSM, indexer.NSMSecondaryEmailId, "NSM", "NSMSecondaryEmailId"));
//Nsm to Zsm
var query4 = list1.GroupBy(x => x.NSM, x => new Mapping {
c1 = x.ZSM, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query4, indexer.NSM, indexer.ZSM, "NSM", "ZSM"));
//Zsm to Zsm Zone
var query5 = list1.GroupBy(x => x.ZSM, x => new Mapping {
c1 = x.ZSMZone, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query5, indexer.ZSM, indexer.ZSMZone, "ZSM", "ZSMZone"));
//Zsm to Zsm EmailId
var query6 = list1.GroupBy(x => x.ZSM, x => new Mapping {
c1 = x.ZSMEmailId, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query6, indexer.ZSM, indexer.ZSMEmailId, "ZSM", "ZSMEmailId"));
//Zsm to Zsm Secondary Email ID
var query7 = list1.GroupBy(x => x.ZSM, x => new Mapping {
c1 = x.ZSMSecondaryEmailId, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query7, indexer.ZSM, indexer.ZSMSecondaryEmailId, "ZSM", "ZSMSecondaryEmailId"));
//Zsm to Rsm
var query8 = list1.GroupBy(x => x.ZSM, x => new Mapping {
c1 = x.RSM, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query8, indexer.ZSM, indexer.RSM, "ZSM", "RSM"));
//Rsm to Rsm Zone
var query9 = list1.GroupBy(x => x.RSM, x => new Mapping {
c1 = x.RSMZone, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query9, indexer.RSM, indexer.RSMZone, "RSM", "RSMZone"));
//Rsm to Rsm EmailId
var query10 = list1.GroupBy(x => x.RSM, x => new Mapping {
c1 = x.RSMEmailId, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query10, indexer.RSM, indexer.RSMEmailId, "RSM", "RSMEmailId"));
//Rsm to Rsm SecondaryEmailId
var query11 = list1.GroupBy(x => x.RSM, x => new Mapping {
c1 = x.RSMSecondaryEmailId, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query11, indexer.RSM, indexer.RSMSecondaryEmailId, "RSM", "RSMSecondaryEmailId"));
//Rsm to Asm
var query12 = list1.GroupBy(x => x.RSM, x => new Mapping {
c1 = x.ASM, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query12, indexer.RSM, indexer.ASM, "RSM", "ASM"));
//Asm to Asm Zone
var query13 = list1.GroupBy(x => x.ASM, x => new Mapping {
c1 = x.ASMZone, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query13, indexer.ASM, indexer.ASMZone, "ASM", "ASMZone"));
//Asm to Asm EmailId
var query14 = list1.GroupBy(x => x.ASM, x => new Mapping {
c1 = x.ASMEmailId, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query14, indexer.ASM, indexer.ASMEmailId, "ASM", "ASMEmailId"));
//Asm to Secondary EmailId
var query15 = list1.GroupBy(x => x.ASM, x => new Mapping {
c1 = x.ASMSecondaryEmailId, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query15, indexer.ASM, indexer.ASMSecondaryEmailId, "ASM", "ASMSecondaryEmailId"));
//Asm to Esm
var query16 = list1.GroupBy(x => x.ASM, x => new Mapping {
c1 = x.ESM, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query16, indexer.ASM, indexer.ESM, "ASM", "ESM"));
//Esm to Esm Zone
var query17 = list1.GroupBy(x => x.ESM, x => new Mapping {
c1 = x.ESMZone, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query17, indexer.ESM, indexer.ESMZone, "ESM", "ESMZone"));
//Esm to Esm EmailId
var query18 = list1.GroupBy(x => x.ESM, x => new Mapping {
c1 = x.ESMEmailId, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query18, indexer.ESM, indexer.ESMEmailId, "ESM", "ESMEmailId"));
//Esm to Esm Secondary EmailId
var query19 = list1.GroupBy(x => x.ESM, x => new Mapping {
c1 = x.ESMSecondaryEmailId, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query19, indexer.ESM, indexer.ESMSecondaryEmailId, "ESM", "ESMSecondaryEmailId"));
//Esm to Esm HQ
var query20 = list1.GroupBy(x => x.ESM, x => new Mapping {
c1 = x.ESMHQ, row = x.Row
}).ToList();
newError.AddRange(checks.AttributeMapping(query20, indexer.ESM, indexer.ESMHQ, "ESM", "ESMHQ"));
//ESM to ESMErpId
var query21 = list1.GroupBy(x => x.ESM, x => new Mapping {
c1 = x.ESMErpId, row = x.Row
}).ToList();
newError.AddRange(checks.One2OneValidationCheck(query21, indexer.ESM, indexer.ESMErpId, "ESM", "ESMErpId"));
//ESM to ESMContact Number
var query22 = list1.GroupBy(x => x.ESM, x => new Mapping {
c1 = x.ESMContactNumber, row = x.Row
}).ToList();
newError.AddRange(checks.One2OneValidationCheck(query22, indexer.ESM, indexer.ESMContactNumber, "ESM", "ESMContactNumber"));
//BeatDistrict to FinalBeatName
var query23 = list1.GroupBy(x => x.BeatDistrict, x => new Mapping {
c1 = x.FinalBeatName, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query23, indexer.BeatDistrict, indexer.FinalBeatName, "BeatDistrict", "FinalBeatName"));
//BeatState FinalBeatName
var query24 = list1.GroupBy(x => x.BeatState, x => new Mapping {
c1 = x.FinalBeatName, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query24, indexer.BeatState, indexer.FinalBeatName, "BeatState", "FinalBeatName"));
//BeatZone to FinalBeatName
var query25 = list1.GroupBy(x => x.BeatZone, x => new Mapping {
c1 = x.FinalBeatName, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query25, indexer.BeatZone, indexer.FinalBeatName, "BeatZone", "FinalBeatName"));
//BeatName to BeatErpId
var query_25 = list1.GroupBy(x => x.FinalBeatName, x => new Mapping {
c1 = x.BeatErpId, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query_25, indexer.FinalBeatName, indexer.BeatErpId, "FinalBeatName", "BeatErpId"));
//FinalBeatName to DistributorName
var query26 = list1.GroupBy(x => x.DistributorName, x => new Mapping {
c1 = x.FinalBeatName, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query26, indexer.DistributorName, indexer.FinalBeatName, "DistributorName", "FinalBeatName"));
//FinalBeatName to DistributorLocation
var query27 = list1.GroupBy(x => x.FinalBeatName, x => new Mapping {
c1 = x.DistributorLocation, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query27, indexer.DistributorLocation, indexer.FinalBeatName, "DistributorLocation", "FinalBeatName"));
//FinalBeatName to DistributorLocation
var query28 = list1.GroupBy(x => x.FinalBeatName, x => new Mapping {
c1 = x.DistributorEmailId, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query28, indexer.DistributorEmailId, indexer.FinalBeatName, "DistributorEmailId", "FinalBeatName"));
//DistributorName to DistributorErpId
var query29 = list1.GroupBy(x => x.DistributorName, x => new Mapping {
c1 = x.DistributorErpId, row = x.Row
}).ToList();
newError.AddRange(checks.One2OneValidationCheck(query29, indexer.DistributorName, indexer.DistributorErpId, "DistributorName", "DistributorErpId"));
//Emails Check
newError.AddRange(checks.EmailCheck(sheet, indexer.NSMEmailId, "NSMEmailId"));
newError.AddRange(checks.EmailCheck(sheet, indexer.NSMSecondaryEmailId, "NSMSecondaryEmailId"));
newError.AddRange(checks.EmailCheck(sheet, indexer.ZSMEmailId, "ZSMEmailId"));
newError.AddRange(checks.EmailCheck(sheet, indexer.ZSMSecondaryEmailId, "ZSMSecondaryEmailId"));
newError.AddRange(checks.EmailCheck(sheet, indexer.RSMEmailId, "RSMEmailId"));
newError.AddRange(checks.EmailCheck(sheet, indexer.ZSMSecondaryEmailId, "RSMSecondaryEmailId"));
newError.AddRange(checks.EmailCheck(sheet, indexer.ASMEmailId, "ASMEmailId"));
newError.AddRange(checks.EmailCheck(sheet, indexer.ASMSecondaryEmailId, "ASMSecondaryEmailId"));
newError.AddRange(checks.EmailCheck(sheet, indexer.ESMEmailId, "ESMEmailId"));
newError.AddRange(checks.EmailCheck(sheet, indexer.ESMSecondaryEmailId, "ESMSecondaryEmailId"));
newError.AddRange(checks.CheckStateAndDistrict(sheet, indexer.BeatState, indexer.BeatDistrict));
//Unique ErpId
Random rnd = new Random();
var query30 = list1.Select(x => new Mapping {
c1 = x.BeatErpId ?? rnd.Next(0, 100000).ToString(), row = x.Row
}).GroupBy(y => y.c1).Where(y => y.Count() > 1).ToList();
newError.AddRange(checks.Unique(query30, indexer.BeatErpId, "BeatErpId"));
var query31 = list1.Select(x => new Mapping {
c1 = x.ESMErpId ?? rnd.Next(0, 100000).ToString(), row = x.Row
}).GroupBy(y => y.c1).Where(y => y.Count() > 1).ToList();
newError.AddRange(checks.Unique(query31, indexer.ESMErpId, "ESMErpId"));
}
//Checks For Location Addition
if (list2 != null)
{
foreach (var item in list2)
{
if (item.FinalBeatName == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "FinalBeatNamae";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.ShopName == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "ShopName";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.MarketName == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "MarketName";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.City == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "City";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.State == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "State";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.Country == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "Country";
temp.Row = item.Row;
newError.Add(temp);
}
}
Random rnd = new Random();
// var query= list1.GroupBy(x => x.DistributorName, x => new Mapping { c1 = x.DistributorErpId, row = x.Row }).ToList();
var query31 = list2.Select(x => new Mapping {
c1 = x.ShopCode ?? rnd.Next(0, 100000).ToString(), row = x.Row
}).GroupBy(y => y.c1).Where(y => y.Count() > 1).ToList();
// newError.AddRange(checks.One2OneValidationCheck(query, indexer.ShopName, indexer.FinalBeatName, "ShopName", "ShopErpId"));
newError.AddRange(checks.Unique(query31, indexer.ShopErpId, "ShopErpId"));
List <Mapping> mapList = new List <Mapping>();
foreach (var item in list2)
{
Mapping map = new Mapping();
map.c1 = item.ShopName + item.Address + item.MarketName + item.City;
map.row = item.Row;
mapList.Add(map);
}
var query32 = mapList.Select(x => new Mapping {
c1 = x.c1 ?? rnd.Next(0, 100000).ToString(), row = x.row
}).GroupBy(y => y.c1).Where(y => y.Count() > 1).ToList();
newError.AddRange(checks.Unique(query32, 1, "ShopName,Address,MarketName,City"));
}
// Beat State and District
//Product Addtion
if (list3 != null)
{
foreach (var item in list3)
{
if (item.PrimaryCategory == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "PrimaryCategory";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.SecondaryCategory == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "SecondaryCategory";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.Product == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Character Length Error";
temp.Field_1 = "Product";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.Price == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "Price";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.Unit == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "Unit";
temp.Row = item.Row;
newError.Add(temp);
}
}
var query1 = list3.GroupBy(x => x.PrimaryCategory, x => new Mapping {
c1 = x.SecondaryCategory, row = x.Row
}).ToList();
var query2 = list3.GroupBy(x => x.SecondaryCategory, x => new Mapping {
c1 = x.DisplayCategory, row = x.Row
}).ToList();
var query3 = list3.GroupBy(x => x.DisplayCategory, x => new Mapping {
c1 = x.Product, row = x.Row
}).ToList();
var query5 = list3.GroupBy(x => x.ProductCode, x => new Mapping {
c1 = x.VariantCode, row = x.Row
}).ToList();
var query6 = list3.GroupBy(x => x.Product, x => new Mapping {
c1 = x.Variant, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query1, indexer.PrimaryCategory, indexer.SecondaryCategory, "PrimaryCategory", "SecondaryCategory"));
newError.AddRange(checks.One2ManyValidationCheck(query2, indexer.SecondaryCategory, indexer.DisplayCategory, "SecondaryCategory", "DisplayCategory"));
newError.AddRange(checks.One2ManyValidationCheck(query3, indexer.DisplayCategory, indexer.Product, "DisplayCategory", "Product"));
newError.AddRange(checks.One2OneValidationCheck(query5, indexer.ProductCode, indexer.VariantCode, "ProductCode", "VariantCode"));
newError.AddRange(checks.UniqueProductVariant(query6, indexer.Product, indexer.Variant));
}
if (list4 != null)
{
foreach (var item in list4)
{
if (item.ESM == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "ESM";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.FinalBeatName == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "FinalBeatName";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.BeatPlanStartDate == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "BeatPlanStartDate";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.BeatPeriod == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "BeatPeriod";
temp.Row = item.Row;
newError.Add(temp);
}
if (item.BeatDay == "")
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Null Entry";
temp.Field_1 = "BeatDay";
temp.Row = item.Row;
newError.Add(temp);
}
try
{
if (int.Parse(item.BeatPeriod) < 1)
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Beat period cannot be less than 1";
temp.Field_1 = "BeatPeriod";
temp.Row = item.Row;
newError.Add(temp);
}
}
catch
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = " BeatDay must be integer";
temp.Field_1 = "BeatDay";
temp.Row = item.Row;
newError.Add(temp);
}
try
{
if (int.Parse(item.BeatDay) > int.Parse(item.BeatPeriod))
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = "Beat period cannot be less than 1";
temp.Field_1 = "BeatPeriod";
temp.Row = item.Row;
newError.Add(temp);
}
}
catch
{
ErrorTemplates temp = new ErrorTemplates();
temp.ErrorType = " BeatDay must be integer";
temp.Field_1 = "BeatDay";
temp.Row = item.Row;
newError.Add(temp);
}
}
var query = list4.GroupBy(x => x.BeatDay, x => new Mapping {
c1 = x.FinalBeatName, row = x.Row
}).ToList();
newError.AddRange(checks.One2ManyValidationCheck(query, indexer.BeatDay, indexer.FinalBeatName, "BeatDay", "FinalBeatName"));
}
return(newError);
}
