protected bool ParseTimeStamp(IXLRow row, IXLCell timeStampCell, out long?value)
{
value = null;
if (!string.IsNullOrWhiteSpace(timeStampCell.GetString()))
{
try
{
value = long.Parse(timeStampCell.RichText.Text, CultureInfo.InvariantCulture);
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{timeStampCell.GetString()}' in row {row.RowNumber()} as Int64", exception);
return(false);
}
catch (ArgumentException exception)
{
Logger.Error($"Unable to parse '{timeStampCell.GetString()}' in row {row.RowNumber()} as Int64", exception);
return(false);
}
catch (OverflowException exception)
{
Logger.Error($"Unable to parse '{timeStampCell.GetString()}' in row {row.RowNumber()} as Int64", exception);
return(false);
}
}
return(true);
}
internal void ParseDataTypeRange()
{
IXLWorksheet dataTypeSheet = Workbook.Worksheets.Worksheet("DataTypes");
IXLRows rows = dataTypeSheet.RowsUsed();
bool firstRow = true;
foreach (IXLRow row in rows)
{
if (firstRow)
{
firstRow = false;
continue;
}
IXLCell typeNameCell = row.Cell("A");
IXLCell nameSpaceCell = row.Cell("B");
IXLCell idCell = row.Cell("C");
string key = typeNameCell.GetString();
if (string.IsNullOrWhiteSpace(key))
{
Logger.Error($"Empty type name in row {row.RowNumber()}");
continue;
}
byte nsByte;
try
{
nsByte = nameSpaceCell.GetValue <byte>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse Namespace of DataType {key} | {nameSpaceCell.GetString()} | {idCell.GetString()}", exception);
continue;
}
byte idByte;
try
{
idByte = idCell.GetValue <byte>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse ID of DataType {key} | {nameSpaceCell.GetString()} | {idCell.GetString()}", exception);
continue;
}
NodeID dataTypeID = new NodeID
{
Namespace = nsByte,
Value = idByte
};
if (CommonConfig.KnownDataTypes.ContainsKey(key))
{
Logger.Warn($"DataType with name '{key}' already exists. Value will be overwritten from row {row.RowNumber()}");
}
CommonConfig.KnownDataTypes[key] = dataTypeID;
}
}
public TemplateCell(int row, int column, IXLCell xlCell)
: this()
{
XLCell = xlCell;
Row = row;
Column = column;
if (xlCell != null)
{
Value = xlCell.Value;
Style = xlCell.Style;
if (xlCell.HasFormula)
{
Formula = xlCell.FormulaA1;
CellType = TemplateCellType.Formula;
}
else
{
CellType = TemplateCellType.Value;
var strVal = xlCell.GetString();
if (strVal.StartsWith("&="))
{
CellType = TemplateCellType.Formula;
Formula = strVal.Substring(2);
}
if (strVal.Contains("{{"))
{
IsCalculated = true;
}
}
}
}
/// <summary>
/// Reads a row of values from the current position and moves down a row
/// </summary>
/// <returns></returns>
public List <string> ReadRow()
{
IXLCell position = current;
var values = new List <string>();
string value = current.GetString();
while (!string.IsNullOrEmpty(value))
{
values.Add(value);
current = current.CellRight();
value = current.GetString();
}
current = position.CellBelow();
return(values);
}
protected bool ParseOrcat(IXLRow row, IXLCell orcatCell, out byte?value)
{
value = null;
if (!string.IsNullOrWhiteSpace(orcatCell.GetString()))
{
try
{
value = orcatCell.GetValue <byte>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{orcatCell.GetString()}' in row {row.RowNumber()} as byte", exception);
return(false);
}
}
return(true);
}
private string GetStringValueFromCell(IXLCell cell)
{
if (cell.IsMerged())
{
return(cell.MergedRange().Cell(1, 1).GetString());
}
return(cell.GetString());
}
protected bool ParseQuality(IXLRow row, IXLCell qualityCell, out ushort?value)
{
value = null;
if (!string.IsNullOrWhiteSpace(qualityCell.GetString()))
{
try
{
value = qualityCell.GetValue <ushort>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{qualityCell.GetString()}' in row {row.RowNumber()} as UInt16", exception);
return(false);
}
}
return(true);
}
public void ValueSetToNull()
{
string expected = String.Empty;
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
cell.Value = new DateTime(2000, 1, 2);
cell.Value = null;
Assert.AreEqual(expected, cell.GetString());
Assert.AreEqual(expected, cell.Value);
cell.Value = new DateTime(2000, 1, 2);
cell.SetValue(null as string);
Assert.AreEqual(expected, cell.GetString());
Assert.AreEqual(expected, cell.Value);
}
private void MatchCallInfoListName(IXLCell cell, ref CallInfoList listInfo, ref CallInfoSymptom symptom, ref CallInfoTime time)
{
try
{
switch (cell.WorksheetColumn().FirstCell().GetString().Trim().ToLower())
{
case "contactperson":
listInfo.ContactPerson.Add(HttpUtility.UrlEncode(cell.GetString()));
break;
case "location":
listInfo.Location.Add(HttpUtility.UrlEncode(cell.GetString()));
break;
case "company":
listInfo.Company.Add(HttpUtility.UrlEncode(cell.GetString()));
break;
case "requesttype":
symptom.RequestType = HttpUtility.UrlEncode(cell.GetString());
break;
case "symptom":
symptom.Symptom = HttpUtility.UrlEncode(cell.GetString());
break;
case "scheduletime":
time.ScheduleTime = HttpUtility.UrlEncode(cell.GetString());
break;
case "servetime1":
time.ServeTime1 = HttpUtility.UrlEncode(cell.GetString());
break;
case "servetime2":
time.ServeTime2 = HttpUtility.UrlEncode(cell.GetString());
break;
case "servicedescription":
symptom.ServiceDescription = HttpUtility.UrlEncode(cell.GetString());
break;
}
}
catch (Exception)
{
throw;
}
}
public static string GetStringOrDefault(this IXLCell cell)
{
try
{
return(cell.HasFormula ? cell.CachedValue.ToString() : cell.GetString());
}
catch (Exception)
{
return(cell.CachedValue.ToString());
}
}
public static bool GetYesNo(this IXLCell cell)
{
var value = cell.GetString();
switch (value)
{
case "Y": return(true);
case "N": return(false);
}
throw new Exception();
}
public void ValueSetToNull()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
cell.Value = new DateTime(2000, 1, 2);
cell.Value = null;
string actual = cell.GetString();
string expected = String.Empty;
Assert.AreEqual(expected, actual);
}
private static float PrendiDatoSingolaCella(IXLCell contenutoCella)
{
if (!contenutoCella.IsEmpty())
{
if (contenutoCella.DataType == XLDataType.Text)
{
if (String.Compare(contenutoCella.GetString().Trim(), "assenti") == 0)
{
return(0f);
}
else
{
var datoCella = float.Parse(contenutoCella.GetString().Trim());
return(datoCella);
}
}
else if (contenutoCella.DataType == XLDataType.Number)
{
var datoCella = float.Parse(contenutoCella.GetString().Trim());
return(datoCella);
}
}
return(0f);
}
public static FuelInfo GetFuelInfo(this IXLCell cell)
{
var value = cell.GetString();
switch (value.First())
{
case 'D':
{
switch (value)
{
case "DU": return(new FuelInfo {
FuelType = FuelType.Diesel
});
default: throw new Exception();
}
}
case 'G':
{
switch (value)
{
case "G": return(new FuelInfo {
FuelType = FuelType.Gasoline, FuelGrade = FuelGrade.Regular
});
case "GM": return(new FuelInfo {
FuelType = FuelType.Gasoline, FuelGrade = FuelGrade.MidGradeRecommended
});
case "GP": return(new FuelInfo {
FuelType = FuelType.Gasoline, FuelGrade = FuelGrade.PremiumRecommended
});
case "GPR": return(new FuelInfo {
FuelType = FuelType.Gasoline, FuelGrade = FuelGrade.PremiumRequired
});
default: throw new Exception();
}
}
}
throw new Exception();
}
public static bool IsMarkedCell(IXLCell cell, MarkerOptions markerOptions)
{
if (cell.DataType == XLDataType.Text && !cell.HasFormula)
{
var stringCellValue = cell.GetString().Trim();
if (stringCellValue.Length < (markerOptions.Prefix.Length + markerOptions.Suffix.Length))
{
return(false);
}
var isPrefixMatch = stringCellValue.Substring(0, markerOptions.Prefix.Length) == markerOptions.Prefix;
var isSuffixMatch = stringCellValue.Substring(stringCellValue.Length - markerOptions.Suffix.Length, markerOptions.Suffix.Length) == markerOptions.Suffix;
if (isPrefixMatch && isSuffixMatch)
{
return(true);
}
}
return(false);
}
public void AddTextTest1()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
IXLRichText richString = cell.RichText;
string text = "Hello";
richString.AddText(text).SetBold().SetFontColor(XLColor.Red);
Assert.AreEqual(cell.GetString(), text);
Assert.AreEqual(cell.RichText.First().Bold, true);
Assert.AreEqual(cell.RichText.First().FontColor, XLColor.Red);
Assert.AreEqual(1, richString.Count);
richString.AddText("World");
Assert.AreEqual(richString.First().Text, text, "Item in collection is not the same as the one returned");
}
public static DrivetrainType GetDrivetrainType(this IXLCell cell)
{
var value = cell.GetString();
switch (value)
{
case "A": return(DrivetrainType.AllWheelDrive);
case "F": return(DrivetrainType.FrontWheelDrive);
case "R": return(DrivetrainType.RearWheelDrive);
case "4": return(DrivetrainType.FourWheelDrive);
case "P": return(DrivetrainType.PartTimeFourWheelDrive);
}
throw new Exception();
}
public void AccessRichTextTest1()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
cell.RichText.AddText("12");
cell.DataType = XLCellValues.Number;
Assert.AreEqual(12.0, cell.GetDouble());
IXLRichText richText = cell.RichText;
Assert.AreEqual("12", richText.ToString());
richText.AddText("34");
Assert.AreEqual("1234", cell.GetString());
Assert.AreEqual(XLCellValues.Number, cell.DataType);
Assert.AreEqual(1234.0, cell.GetDouble());
}
public new void Processing()
{
XLWorkbook wb = new XLWorkbook(FilePath);
foreach (var page in wb.Worksheets)
{
if (page.Name.ToUpper().Trim() != "СТАТИСТИКА" && page.Name.ToUpper().Trim() != "СВОДНАЯ")
{
const int numColPoint = 4;
IXLCell CellDate = page.Cell(1, numColPoint + 1);
while (CellDate.GetString() == "" && CellDate.Address.ColumnNumber <= page.LastColumnUsed().ColumnNumber())
{
CellDate = CellDate.CellRight();
}
DateTime curDate;
//if (!DateTime.TryParse(CellDate.GetValue<string>(), out curDate))
//{
// CellDate = CellDate.CellAbove();
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
//}
Regex rComment = new Regex(@"КОРРЕКЦИИ");
int corrRow = 5;
Match Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper());
while (!Mcomment.Success)
{
corrRow++;
Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper());
}
List <Call> calls = new List <Call>();
while (!(CellDate.CellBelow().IsEmpty() && CellDate.CellBelow().CellRight().IsEmpty()))
{
if (CellDate.GetValue <string>() != "")
{
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
}
string phoneNumber = CellDate.CellBelow().GetValue <string>();
var phoneCell = CellDate.CellBelow();
if (phoneNumber != "")
{
TimeSpan duration;
string link = "";
if (phoneCell.HasHyperlink)
{
link = phoneCell.Hyperlink.ExternalAddress.AbsoluteUri;
}
IXLCell CellPoint = CellDate.CellBelow().CellBelow().CellBelow();
if (CellPoint.DataType == XLDataType.DateTime)
{
CellPoint.DataType = XLDataType.TimeSpan;
}
TimeSpan.TryParse(CellPoint.GetString(), out duration);
IXLCell CellNamePoint;
List <Point> points = new List <Point>();
Point curPoint;
int markOfPoint;
CellPoint = CellPoint.CellBelow();
string DealName = "";
string comment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).GetString();
bool redComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Red ? true : false;
var Color = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor;
bool greenComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Lime ? true : false;
int maxMark;
page.Cell(corrRow - 3, CellPoint.Address.ColumnNumber).TryGetValue(out maxMark);
if (!CellPoint.TryGetValue <int>(out markOfPoint))
{
if (CellPoint.GetString() != "")
{
DealName = CellPoint.GetString();
}
}
else
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
curPoint.ColorForRNR = CellNamePoint.Style.Fill.BackgroundColor;
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
int weightPoint;
int numchl;
while (page.Cell(CellPoint.Address.RowNumber, 3).TryGetValue <int>(out numchl) || page.Cell(CellPoint.Address.RowNumber, 3).GetString() == "б\\н")
{
page.Cell(CellPoint.Address.RowNumber, 2).TryGetValue <int>(out weightPoint);
if (CellPoint.TryGetValue <int>(out markOfPoint))
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
if (error)
{
}
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
curPoint.ColorForRNR = CellNamePoint.Style.Fill.BackgroundColor;
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
}
bool outgoing = true;
if (Regex.Match(page.Name.ToUpper(), "ВХОДЯЩ").Success)
{
outgoing = false;
}
string Objections = "";
string howProcessObj = "";
string DealState = "";
string DateOfNext = "";
string doneObj = "";
if (curDate > new DateTime(2020, 5, 6))
{
Objections = page.Cell(corrRow + 2, CellPoint.Address.ColumnNumber).GetString();
howProcessObj = page.Cell(corrRow + 4, CellPoint.Address.ColumnNumber).GetString();
DealState = page.Cell(corrRow + 5, CellPoint.Address.ColumnNumber).GetString();
DateOfNext = page.Cell(corrRow + 6, CellPoint.Address.ColumnNumber).GetString();
DateTime ddateNext;
if (DateOfNext != "")
{
if (DateTime.TryParse(DateOfNext, out ddateNext))
{
DateOfNext = ddateNext.ToString("dd.MM.yyyy");
}
}
doneObj = page.Cell(corrRow + 3, CellPoint.Address.ColumnNumber).GetString();
}
if (points.Count > 0)
{
var curCall = new Call(phoneNumber, maxMark, duration, comment, DealName, points, redComment, curDate, outgoing, greenComment, Objections, howProcessObj, DealState, link, DateOfNext, doneObj);
calls.Add(curCall);
var testt = curCall.getAVGPersent();
if (testt > 1)
{
}
}
}
CellDate = CellDate.CellRight();
}
stages.Add(new Stage(page.Name, calls));
}
}
}
internal CommonConfig ReadCommon()
{
if (Workbook == null)
{
return(null);
}
IXLWorksheet commonSheet = Workbook.Worksheets.Worksheet("Common");
if (commonSheet == null)
{
Logger.Error("Unable to find \"Common\" sheet.");
return(null);
}
IXLRows rows = commonSheet.RowsUsed();
CommonConfig.MetaConfig.ConfigurationVersion = new ConfigurationVersion();
DateTime now = DateTime.UtcNow;
TimeSpan time = now - ConfigurationVersion.Base;
uint defaultConfigVersion = (uint)time.TotalSeconds;
foreach (IXLRow row in rows)
{
IXLCell cell = row.Cell("A");
IXLCell valueCell = row.Cell("B");
Logger.Debug($"Cell value: {cell.Value}");
switch (cell.Value)
{
case "Publisher ID":
CommonConfig.PublisherID = valueCell.GetString();
break;
case "DataSetWriterId":
ushort id;
try
{
id = valueCell.GetValue <ushort>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse DataSetWriterID: '{valueCell.GetString()}'");
Logger.Debug("FormatException:", exception);
break;
}
CommonConfig.DataSetWriterID = id;
break;
case "MetaData - Name":
CommonConfig.MetaConfig.MetaDataName = valueCell.GetString();
break;
case "MetaData - Description":
CommonConfig.MetaConfig.MetaDataDescription = valueCell.GetString();
break;
case "ConfigurationVersion - Major":
uint major;
try
{
major = valueCell.GetValue <uint>();
}
catch (FormatException exception)
{
Logger.Info($"Unable to parse ConfigurationVersion - Major: '{valueCell.GetString()}', using default value {defaultConfigVersion}");
major = defaultConfigVersion;
Logger.Debug("FormatException:", exception);
}
CommonConfig.MetaConfig.ConfigurationVersion.Major = major;
break;
case "ConfigurationVersion - Minor":
uint minor;
try
{
minor = valueCell.GetValue <uint>();
}
catch (FormatException exception)
{
Logger.Info($"Unable to parse ConfigurationVersion - Minor: '{valueCell.GetString()}', using default value {defaultConfigVersion}");
minor = defaultConfigVersion;
Logger.Debug("FormatException:", exception);
}
CommonConfig.MetaConfig.ConfigurationVersion.Minor = minor;
break;
}
}
return(CommonConfig);
}
internal void ParseEnumSheet()
{
IXLWorksheet dataTypeSheet = Workbook.Worksheets.Worksheet("Enums");
IXLRows rows = dataTypeSheet.RowsUsed();
bool firstRow = true;
foreach (IXLRow row in rows)
{
if (firstRow)
{
firstRow = false;
continue;
}
IXLCell dataTypeCell = row.Cell("A");
IXLCell qualifiedNameCell = row.Cell("B");
//IXLCell enumDescriptionDataTypeCell = row.Cell("C");
IXLCell valueCell = row.Cell("C");
IXLCell displayNameCell = row.Cell("D");
IXLCell descriptionCell = row.Cell("E");
IXLCell valueNameCell = row.Cell("F");
EnumEntry entry = new EnumEntry();
if (!TryGetNodeID(dataTypeCell.GetString(), out NodeID tempID, CommonConfig))
{
continue;
}
entry.DataType = tempID;
entry.QualifiedName = qualifiedNameCell.GetString();
int intValue;
try
{
intValue = valueCell.GetValue <int>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse integer value for enum '{valueCell.GetString()}' in row {row.RowNumber()}", exception);
continue;
}
entry.Value = intValue;
entry.DisplayName = displayNameCell.GetString();
entry.Description = descriptionCell.GetString();
entry.ValueName = valueNameCell.GetString();
EnumDescription enumDescription;
if (CommonConfig.EnumDescriptions.ContainsKey(entry.DataType))
{
enumDescription = CommonConfig.EnumDescriptions[entry.DataType];
}
else
{
enumDescription = new EnumDescription
{
Name = new QualifiedName(entry.QualifiedName),
DataTypeID = entry.DataType,
Fields = new List <EnumField>()
};
CommonConfig.EnumDescriptions.Add(entry.DataType, enumDescription);
}
EnumField enumField = new EnumField
{
Value = entry.Value,
Name = new String(entry.ValueName),
Description = new LocalizedText(),
DisplayName = new LocalizedText()
};
if (!string.IsNullOrEmpty(entry.DisplayName))
{
enumField.DisplayName.Locale = new String("en-US");
enumField.DisplayName.Text = new String(entry.DisplayName);
}
if (!string.IsNullOrEmpty(entry.Description))
{
enumField.Description.Locale = new String("en-US");
enumField.Description.Text = new String(entry.Description);
}
// TODO: Add check for duplicates
enumDescription.Fields.Add(enumField);
}
}
public string GetString()
{
return(_cell.GetString());
}
public List <KeyEntry> Read(string Name)
{
List <KeyEntry> keyEntries = new List <KeyEntry>();
if (CommonConfig == null || Workbook == null)
{
return(keyEntries);
}
IXLWorksheet keySheet = Workbook.Worksheets.Worksheet(Name);
IXLRows rows = keySheet.RowsUsed();
bool firstRow = true;
foreach (IXLRow row in rows)
{
if (firstRow)
{
firstRow = false;
continue;
}
KeyEntry entry = new KeyEntry();
// Index
IXLCell indexCell = row.Cell("A");
if (string.IsNullOrWhiteSpace(indexCell.GetString()))
{
Logger.Error($"Empty index cell in row {row.RowNumber()}");
continue;
}
ushort index = ushort.MinValue;
try
{
index = indexCell.GetValue <ushort>();
}
catch (FormatException e)
{
Logger.Error($"Unable to parse index '{indexCell.GetString()}' as int in row {row.RowNumber()}", e);
continue;
}
entry.Index = index;
// Name
IXLCell nameCell = row.Cell("B");
if (string.IsNullOrWhiteSpace(nameCell.GetString()))
{
Logger.Error($"Name is empty in row {row.RowNumber()}");
continue;
}
entry.Name = nameCell.GetString();
// Description
IXLCell descriptionCell = row.Cell("C");
if (string.IsNullOrWhiteSpace(descriptionCell.GetString()))
{
Logger.Info($"Description is empty in row {row.RowNumber()}");
}
entry.Description = descriptionCell.GetString();
// DataType
IXLCell dataTypeCell = row.Cell("D");
if (ReadConfiguration.TryGetNodeID(dataTypeCell.GetString(), out NodeID tempID, CommonConfig))
{
entry.DataType = tempID;
}
public static string ExtractMarkerValue(IXLCell cell, MarkerOptions markerOptions)
{
var stringCellValue = cell.GetString().Trim();
return(stringCellValue.Substring(markerOptions.Prefix.Length, stringCellValue.Length - (markerOptions.Prefix.Length + markerOptions.Suffix.Length)));
}
public new void Processing()
{
XLWorkbook wb = new XLWorkbook(FilePath);
foreach (var page in wb.Worksheets)
{
if (page.Name.ToUpper().Trim() != "СТАТИСТИКА" && page.Name.ToUpper().Trim() != "СВОДНЫЕ" && page.Name.ToUpper().Trim() != "СВОДНАЯ" && page.Name.ToUpper().Trim() != "СТАТИСТИКИ")
{
const int numColPoint = 4;
IXLCell CellDate = page.Cell(1, numColPoint + 1);
DateTime curDate;
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
Regex rComment = new Regex(@"КОРРЕКЦИИ");
int corrRow = 5;
Match Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper());
while (!Mcomment.Success)
{
corrRow++;
Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper());
}
List <Call> calls = new List <Call>();
while (!(CellDate.CellBelow().IsEmpty() && CellDate.CellBelow().CellRight().IsEmpty() && CellDate.CellBelow().CellBelow().IsEmpty() && CellDate.CellBelow().CellBelow().CellRight().IsEmpty()))
{
if (CellDate.GetValue <string>() != "")
{
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
}
string phoneNumber = CellDate.CellBelow().GetValue <string>();
var phoneCell = CellDate.CellBelow();
if (phoneNumber == "")
{
phoneNumber = CellDate.CellBelow().CellBelow().GetValue <string>();
phoneCell = CellDate.CellBelow().CellBelow();
}
if (phoneNumber != "")
{
string link = "";
if (phoneCell.HasHyperlink)
{
link = phoneCell.Hyperlink.ExternalAddress.AbsoluteUri;
}
TimeSpan duration;
TimeSpan wrongtime1 = new TimeSpan(1, 0, 0, 0);
TimeSpan wrongtime2 = new TimeSpan();
IXLCell CellPoint = CellDate.CellBelow().CellBelow().CellBelow();
if (CellPoint.DataType == XLDataType.DateTime)
{
CellPoint.DataType = XLDataType.TimeSpan;
}
TimeSpan.TryParse(CellPoint.GetString(), out duration);
IXLCell CellNamePoint;
List <Point> points = new List <Point>();
Point curPoint;
int markOfPoint;
if (wrongtime1 <= duration || duration == wrongtime2)
{
duration = wrongtime2;
if (CellPoint.TryGetValue <int>(out markOfPoint))
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
points.Add(curPoint);
}
}
CellPoint = CellDate.CellBelow().CellBelow().CellBelow().CellBelow();
string DealName = "";
string comment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).GetString();
bool redComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Red ? true : false;
int maxMark;
page.Cell(corrRow - 3, CellPoint.Address.ColumnNumber).TryGetValue(out maxMark);
if (!CellPoint.TryGetValue <int>(out markOfPoint))
{
if (CellPoint.GetString() != "")
{
DealName = CellPoint.GetString();
}
}
else
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
//int i = 0;
//while (page.Cell(CellPoint.Address.RowNumber - i, 1).GetString() == "")
//{
// i++;
//}
//curPoint.stageForBelfan = page.Cell(CellPoint.Address.RowNumber - i, 1).GetString();
curPoint.stageForBelfan = CellPoint.Address.RowNumber.ToString();
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
while (CellPoint.Address.RowNumber < corrRow - 4)
{
if (CellPoint.TryGetValue <int>(out markOfPoint))
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
//int i = 0;
//while (page.Cell(CellPoint.Address.RowNumber - i,1).GetString() == "")
//{
// i++;
//}
//curPoint.stageForBelfan = page.Cell(CellPoint.Address.RowNumber - i, 1).GetString();
curPoint.stageForBelfan = CellPoint.Address.RowNumber.ToString();
points.Add(curPoint);
}
else
{
string answer = CellPoint.GetString().ToLower();
if (answer == "нет" || answer == "да")
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), answer == "нет" ? 0 : 1, error, true);
curPoint.stageForBelfan = CellPoint.Address.RowNumber.ToString();
points.Add(curPoint);
}
}
CellPoint = CellPoint.CellBelow();
}
bool outgoing = true;
string Objections = "";
string howProcessObj = "";
string DealState = "";
string DateOfNext = "";
string doneObj = "";
if (curDate > new DateTime(2020, 5, 6))
{
Objections = page.Cell(corrRow + 2, CellPoint.Address.ColumnNumber).GetString();
howProcessObj = page.Cell(corrRow + 4, CellPoint.Address.ColumnNumber).GetString();
DealState = page.Cell(corrRow + 5, CellPoint.Address.ColumnNumber).GetString();
DateOfNext = page.Cell(corrRow + 6, CellPoint.Address.ColumnNumber).GetString();
doneObj = page.Cell(corrRow + 3, CellPoint.Address.ColumnNumber).GetString();
}
DateTime ddateNext;
if (DateOfNext != "")
{
if (DateTime.TryParse(DateOfNext, out ddateNext))
{
DateOfNext = ddateNext.ToString("dd.MM.yyyy");
}
}
if (Regex.Match(phoneNumber.ToUpper(), "ВХОДЯЩ").Success)
{
outgoing = false;
}
bool greenComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Lime ? true : false;
if (points.Count > 0)
{
calls.Add(new Call(phoneNumber, maxMark, duration, comment, DealName, points, redComment, curDate, outgoing, greenComment, Objections, howProcessObj, DealState, link, DateOfNext, doneObj));
}
}
CellDate = CellDate.CellRight();
}
stages.Add(new Stage(page.Name.Trim(), calls));
}
}
}
public List <DeltaEntry> Read(string Name, bool isGrouped = false)
{
List <DeltaEntry> deltaEntries = new List <DeltaEntry>();
if (CommonConfig == null || Workbook == null)
{
return(deltaEntries);
}
IXLWorksheet keySheet = Workbook.Worksheets.Worksheet(Name);
IXLRows rows = keySheet.RowsUsed();
bool firstRow = true;
foreach (IXLRow row in rows)
{
if (firstRow)
{
firstRow = false;
continue;
}
DeltaEntry entry = new DeltaEntry();
// Index
IXLCell indexCell = row.Cell("A");
if (string.IsNullOrWhiteSpace(indexCell.GetString()))
{
Logger.Error($"Empty index cell in row {row.RowNumber()}");
continue;
}
ushort index = ushort.MinValue;
try
{
index = indexCell.GetValue <ushort>();
}
catch (FormatException e)
{
Logger.Error($"Unable to parse index '{indexCell.GetString()}' as ushort in row {row.RowNumber()}", e);
continue;
}
entry.Index = index;
//Grouped Delta Entry
if (isGrouped)
{
entry.DataType = KeyEntries.First()
.DataType;
}
else
{
// Map DataType from KeyList
if (KeyEntries == null || KeyEntries.Count == 0)
{
Logger.Error("Cannot parse value of DeltaEntry, because KeyEntries are not available.");
continue;
}
if (KeyEntries.Count < entry.Index)
{
Logger.Error($"Cannot parse value of DeltaEntry, because index [{entry.Index}] of Delta Entry is not available in KeyEntries List (count is {KeyEntries.Count})."
);
continue;
}
KeyEntry keyEntry = KeyEntries[entry.Index];
entry.DataType = keyEntry.DataType;
}
// Orcat
IXLCell orcatCell = row.Cell("B");
if (ParseOrcat(row, orcatCell, out byte?orcatValue) && orcatValue.HasValue)
{
entry.Orcat = orcatValue.Value;
}
// Quality
IXLCell qualityCell = row.Cell("C");
if (ParseQuality(row, qualityCell, out ushort?qualityValue) && qualityValue.HasValue)
{
entry.Quality = qualityValue.Value;
}
// Timestamp
IXLCell timeStampCell = row.Cell("D");
if (ParseTimeStamp(row, timeStampCell, out long?timeStampValue) && timeStampValue.HasValue)
{
entry.TimeStamp = timeStampValue.Value;
}
// Value
IXLCell valueCell = row.Cell("E");
IXLCell value2Cell = row.Cell("F");
IEntry baseEntry = entry;
if (!TryParse(valueCell, value2Cell, ref baseEntry))
{
continue;
}
//Name
if (isGrouped)
{
IXLCell cellName = row.Cell("F");
if (!string.IsNullOrWhiteSpace(cellName.GetString()))
{
try
{
entry.Name = cellName.GetValue <string>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{qualityCell.GetString()}' in row {row.RowNumber()} as UInt16", exception);
}
}
}
deltaEntries.Add(entry);
}
return(deltaEntries);
}
public static TEnum GetEnum <TEnum>(this IXLCell cell) where TEnum : struct, Enum
{
var value = cell.GetString();
return(Enum.Parse <TEnum>(value));
}
/// <summary>
/// Return true if it have at least 1 DTOValidacionArchivo object instance. Q: Have any format error? YES(TRUE) or NO(FALSE)
/// </summary>
/// <param name="template">Template format object</param>
/// <param name="cell">IXL Cell objet</param>
/// <returns>True for any new instance DTOValidacionArchivo. If DTOValidacionArchivo is NULL then return false</returns>
private bool Validator_Cell(TemplateFormatCAC template, IXLCell cell)
{
bool flag = false;
try
{
DTOValidacionArchivo validation = null;
#region Validacion si es null
if (cell.IsEmpty() == true && template.Nullable == false)
{
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_EMPTY_OR_NULL, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Name),
Valor = cell.GetString(),
Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}",
Fila = $"{cell.WorksheetRow().RowNumber()}",
Columna = $"{cell.WorksheetColumn().ColumnLetter()}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_EMPTY_OR_NULL", validation.ToString(), cell.Worksheet.Name));
validator_result.Add(validation);
}
#endregion
#region Si no es null or empty
else if (cell.IsEmpty() == false)
{
bool hasFormula = cell.HasFormula;
if (hasFormula == true)
{
#region Si tiene formula
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_HAVE_FORMULA, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Name),
Valor = cell.GetString(),
Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}",
Fila = $"{cell.WorksheetRow().RowNumber()}",
Columna = $"{cell.WorksheetColumn().ColumnLetter()}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_HAVE_FORMULA", validation.ToString(), cell.Worksheet.Name));
validator_result.Add(validation);
#endregion Si tiene formula
}
else
{
#region Validacion del tipo de dato
var cell_datatype = cell.DataType.ToString();
if (template.Type.Contains(cell_datatype) == false)
{
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_NOT_VALID_TYPE, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Type, cell_datatype, cell.Value, template.Name),
Valor = cell.GetString(),
Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}",
Fila = $"{cell.WorksheetRow().RowNumber()}",
Columna = $"{cell.WorksheetColumn().ColumnLetter()}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_NOT_VALID_TYPE", validation.ToString(), cell.Worksheet.Name));
validator_result.Add(validation);
}
#endregion
#region Validacion si el valor está contenido en la lista predeterminado y si es texto
if (template.SelectList != null && template.SelectList.Count > 0)
{
bool isContained = false;
try
{
isContained = template.SelectList.Where(m => m.Value == cell.GetValue <string>()).Count() > 0 ? true : false;
}
catch (Exception)
{
isContained = false;
}
if (isContained == false)
{
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_DO_NOT_LIST_VALUE, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), cell.Value, template.Name),
Valor = cell.GetString(),
Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}",
Fila = $"{cell.WorksheetRow().RowNumber()}",
Columna = $"{cell.WorksheetColumn().ColumnLetter()}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_DO_NOT_LIST_VALUE", validation.ToString(), cell.Worksheet.Name));
validator_result.Add(validation);
}
}
#endregion
#region Si es fecha y obtener el valor segun el tipo de dato
var cell_type = cell.Value.GetType();
if (typeof(DateTime).ToString().Contains(cell_type.ToString()) == true || template.Type.Contains(typeof(DateTime).Name))
{
bool regexResult = false;
try
{
DateTime cell_datetime;
if (cell.TryGetValue <DateTime>(out cell_datetime) == true)
{
string datetime_to_comparer = cell_datetime.ToString(template.Format);
string regex = @"^\d{4}-((0\d)|(1[012]))-(([012]\d)|3[01])$";
regexResult = Regex.Match(datetime_to_comparer, regex).Success;
}
else
{
regexResult = false;
}
}
catch (Exception)
{
regexResult = false;
}
if (regexResult == false)
{
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_NOT_FORMAT, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Format, template.Name),
Valor = cell.GetString(),
Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}",
Fila = $"{cell.WorksheetRow().RowNumber()}",
Columna = $"{cell.WorksheetColumn().ColumnLetter()}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_NOT_FORMAT", validation.ToString(), cell.Worksheet.Name));
validator_result.Add(validation);
}
}
#endregion
}
}
#endregion
flag = validation == null ? false : true;
}
catch (Exception ex)
{
validator_result.Add(ExceptionWriter(ex));
flag = true;
throw ex;
}
template = null;
cell = null;
return(flag);
}
public void ParserCheckLists(IEnumerable <IFormFile> files)
{
using (var stream = files.First().OpenReadStream())
{
XLWorkbook wb = new XLWorkbook(stream);
FillStageDictionary(wb);
}
foreach (var file in files)
{
string Manager = Regex.Match(file.FileName, @"(\w+)").Groups[1].Value;
using (var stream = file.OpenReadStream())
{
XLWorkbook wb = new XLWorkbook(stream);
IXLWorksheet page = wb.Worksheets.First();
IXLCell cell = page.Cell(1, 5);
DateTime curDate;
if (cell.DataType == XLDataType.DateTime)
{
curDate = cell.GetDateTime();
}
else
{
if (!DateTime.TryParse(cell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate))
{
DateTime.TryParse(cell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out curDate);
}
}
string phoneNumber;
IXLCell phoneCell;
while (!(cell.IsEmpty() && cell.CellRight().IsEmpty() && !cell.IsMerged()))
{
if (cell.GetString() != "")
{
if (cell.DataType == XLDataType.DateTime)
{
curDate = cell.GetDateTime();
}
else
{
if (!DateTime.TryParse(cell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate))
{
DateTime.TryParse(cell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out curDate);
}
}
}
phoneCell = cell.CellBelow();
if (phoneCell.GetString() == "")
{
phoneCell = phoneCell.CellBelow();
}
if (phoneCell.GetString() != "")
{
string link;
if (phoneCell.HasHyperlink)
{
link = phoneCell.GetHyperlink().ExternalAddress.AbsoluteUri;
}
else
{
link = "";
}
Match outgoing = Regex.Match(phoneCell.GetString().ToUpper(), @"ИСХОДЯЩИЙ");
phoneNumber = Regex.Replace(phoneCell.GetString().ToUpper(), @"[^\d]", String.Empty);
string oldphonenum = phoneNumber;
oldphonenum = "8 (" + oldphonenum.Substring(1, 3) + ") " + oldphonenum.Substring(4, 3) + "-" + oldphonenum.Substring(7, 2) + "-" + oldphonenum.Substring(9);
while (phoneNumber[0] == '0')
{
phoneNumber = phoneNumber.Substring(1);
}
if (phoneNumber[0] == '9')
{
phoneNumber = '8' + phoneNumber;
}
if (phoneNumber[0] == '7' || phoneNumber[0] == '8')
{
phoneNumber = "8 (" + phoneNumber.Substring(1, 3) + ") " + phoneNumber.Substring(4, 3) + "-" + phoneNumber.Substring(7, 2) + "-" + phoneNumber.Substring(9);
}
if (processedCalls.Exists(c => c.Client == oldphonenum) && oldphonenum != phoneNumber)
{
var testCall = processedCalls.Where(c => c.Client == oldphonenum).First();
processedCalls.Remove(testCall);
testCall.Client = phoneNumber;
processedCalls.Add(testCall);
}
if (processedCalls.Exists(c => c.Client == phoneNumber && c.Link == ""))
{
var testCall = processedCalls.Where(c => c.Client == phoneNumber).First();
testCall.Link = link;
}
var CellStage = page.Cell("A5");
Regex rx = new Regex("ИТОГ");
int corrRow = 5;
Match Mcomment = Regex.Match(page.Cell(corrRow, 1).GetString().ToUpper(), @"КОРРЕКЦИИ");
while (!Mcomment.Success)
{
corrRow++;
Mcomment = Regex.Match(page.Cell(corrRow, 1).GetString().ToUpper(), @"КОРРЕКЦИИ");
}
while (!rx.Match(CellStage.GetString().ToUpper()).Success&& !rx.Match(CellStage.CellRight().CellRight().CellRight().GetString().ToUpper()).Success)
{
if (CellStage.GetString() != "" && page.Cell(CellStage.Address.RowNumber, cell.Address.ColumnNumber).GetString() != "")
{
var exCallSeq = processedCalls.Where(c => (c.Client == phoneNumber));
var exCall = new ProcessedCall();
if (exCallSeq.Count() > 0)
{
exCall = exCallSeq.First();
//exCall.StartDateAnalyze = curDate.AddDays(-1);
}
else
{
exCall.ClientState = "";
exCall.StartDateAnalyze = DateTime.MinValue;
}
if (curDate >= exCall.StartDateAnalyze ||
(
exCall.ClientState.ToUpper() == "В РАБОТЕ") &&
exCall.StartDateAnalyze < DateTime.Today.AddDays(1)
)
{
DateTime DateNext = new DateTime();
var NextContactCell = page.Cell(corrRow + 6, cell.Address.ColumnNumber);
if (NextContactCell.GetString() != "")
{
if (NextContactCell.DataType == XLDataType.DateTime)
{
DateNext = NextContactCell.GetDateTime();
}
else
{
if (!DateTime.TryParse(NextContactCell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out DateNext))
{
DateTime.TryParse(NextContactCell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out DateNext);
}
}
}
if (curDate > DateTime.Now.AddMonths(-1) && Regex.Match(file.Name, "Гакова|Малькова|Лукина|Кожевникова|Рыбачук", RegexOptions.IgnoreCase).Success)
{
phones.AddCall(new FullCall(phoneNumber, link, Regex.Replace(CellStage.GetString(), @"[\d()]", String.Empty).Trim(), curDate, outgoing.Success, page.Cell(corrRow, cell.Address.ColumnNumber).GetString(), Manager, page.Cell(corrRow + 5, cell.Address.ColumnNumber).GetString(), DateNext));
}
phonesForFirst.AddCall(new FullCall(phoneNumber, link, Regex.Replace(CellStage.GetString(), @"[\d()]", String.Empty).Trim(), curDate, outgoing.Success, page.Cell(corrRow, cell.Address.ColumnNumber).GetString(), Manager, page.Cell(corrRow + 5, cell.Address.ColumnNumber).GetString(), DateNext));
}
}
CellStage = CellStage.CellBelow();
}
}
cell = cell.CellRight();
}
phones.CleanSuccess(ref processedCalls);
}
}
}
public void ParserCheckLists(IEnumerable <IFormFile> files)
{
using (var stream = files.First().OpenReadStream())
{
XLWorkbook wb = new XLWorkbook(stream);
FillStageDictionary(wb);
}
foreach (var file in files)
{
string Manager = Regex.Match(file.FileName, @"(\w+)").Groups[1].Value;
using (var stream = file.OpenReadStream())
{
XLWorkbook wb = new XLWorkbook(stream);
foreach (var page in wb.Worksheets)
{
var statisticMatch = Regex.Match(page.Name.ToUpper().Trim(), "СТАТИСТИК");
var LastTableMatch = Regex.Match(page.Name.ToUpper().Trim(), "СВОДН");
if (!statisticMatch.Success && !LastTableMatch.Success)
{
IXLCell cell = page.Cell(1, 5);
DateTime curDate;
bool normalDate = false;
if (cell.DataType == XLDataType.DateTime)
{
curDate = cell.GetDateTime();
normalDate = true;
}
else
{
if (!DateTime.TryParse(cell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate))
{
normalDate = DateTime.TryParse(cell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out curDate);
}
else
{
normalDate = true;
}
}
string phoneNumber;
int corrRow = 5;
Match Mcomment = Regex.Match(page.Cell(corrRow, 1).GetString().ToUpper(), @"КОРРЕКЦИИ");
while (!Mcomment.Success)
{
corrRow++;
Mcomment = Regex.Match(page.Cell(corrRow, 1).GetString().ToUpper(), @"КОРРЕКЦИИ");
}
while (!(cell.CellBelow().IsEmpty() && cell.CellBelow().CellRight().IsEmpty() && cell.CellBelow().CellBelow().IsEmpty() && cell.CellBelow().CellBelow().CellRight().IsEmpty()))
{
if (cell.GetValue <string>() != "")
{
if (cell.DataType == XLDataType.DateTime)
{
curDate = cell.GetDateTime();
}
else
{
if (!DateTime.TryParse(cell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate))
{
DateTime.TryParse(cell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out curDate);
}
}
}
phoneNumber = cell.CellBelow().GetValue <string>().ToUpper().Trim();
var CellPhoneNumber = cell.CellBelow();
string link;
if (CellPhoneNumber.HasHyperlink)
{
link = CellPhoneNumber.GetHyperlink().ExternalAddress.AbsoluteUri;
}
else
{
link = "";
}
if (link == "")
{
}
if (phoneNumber != "")
{
Regex rx = new Regex("ВХОДЯЩ");
Match m = rx.Match(page.Name.ToUpper().Trim());
var exCallSeq = processedCalls.Where(c => (c.Client == phoneNumber && link == "") || (c.Link == link && link != ""));
var exCall = new ProcessedCall();
//exCall.StartDateAnalyze = curDate.AddDays(-1);
if (exCallSeq.Count() > 0)
{
exCall = exCallSeq.First();
//exCall.StartDateAnalyze = curDate.AddDays(-1);
}
else
{
exCall.ClientState = "";
exCall.StartDateAnalyze = DateTime.MinValue;
}
if ((curDate > exCall.StartDateAnalyze ||
(
exCall.ClientState.ToUpper() == "В РАБОТЕ") &&
exCall.StartDateAnalyze < DateTime.Today.AddDays(1)
) && normalDate)
{
DateTime DateNext = new DateTime();
var NextContactCell = page.Cell(corrRow + 6, cell.Address.ColumnNumber);
if (NextContactCell.GetString() != "")
{
if (NextContactCell.DataType == XLDataType.DateTime)
{
DateNext = NextContactCell.GetDateTime();
}
else
{
if (!DateTime.TryParse(NextContactCell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out DateNext))
{
DateTime.TryParse(NextContactCell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out DateNext);
}
}
}
if (curDate > new DateTime(2020, 5, 5))
{
phones.AddCall(new FullCall(phoneNumber, link, page.Name.ToUpper().Trim(), curDate, !m.Success, page.Cell(corrRow, cell.Address.ColumnNumber).GetString(), Manager, page.Cell(corrRow + 5, cell.Address.ColumnNumber).GetString(), DateNext));
}
else
{
phones.AddCall(new FullCall(phoneNumber, link, page.Name.ToUpper().Trim(), curDate, !m.Success, page.Cell(corrRow, cell.Address.ColumnNumber).GetString(), Manager));
}
}
}
cell = cell.CellRight();
}
phones.CleanSuccess(ref processedCalls);
}
}
}
}
}