private static DateTime FormatDateCell(IXLCell cell)
{
cell.SetDataType(XLDataType.DateTime);
var value = cell.GetDateTime().ToString(FormatoDataExcel, Execute.CulturaPtBr);
return(DateTime.Parse(value));
}
public IDictionary <ExcelDataType, object> ReadExcelNamedRange(IXLWorkbook workbook, string namedRange)
{
IXLNamedRange range = workbook.NamedRange(namedRange);
if (range == null)
{
throw new ClosedXMLReadException($"There is no named range called {namedRange} in this workbook");
}
int count = range.Ranges.First().Cells().Count();
//if solo then don't return collection.
if (count.Equals(1))
{
IXLCell cell = range.Ranges.First().Cells().First();
switch (cell.DataType)
{
case XLDataType.DateTime:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, cell.GetDateTime().ToOADate() }
});
case XLDataType.Number:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, cell.GetDouble() }
});
case XLDataType.Text:
string textValue = GetSingleCellTextValue(cell);
KeyValuePair <ExcelDataType, object> parsed = ParseString(textValue);
switch (parsed.Key)
{
case ExcelDataType.Numeric:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, (double)parsed.Value }
});
case ExcelDataType.Text:
string[,] array = new string[1, 1];
array[0, 0] = textValue;
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Text, array }
});
default:
throw new NotImplementedException("I haven't implemented formulas yet");
}
}
}
IXLTable table = range.Ranges.First().AsTable();
return(ReadTable(table));
}
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);
}
}
}
}
}
public 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() != "СТАТИСТИКИ")
{
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>() != "")
{
if (CellDate.DataType == XLDataType.DateTime)
{
curDate = CellDate.GetDateTime();
}
else
{
if (!DateTime.TryParse(CellDate.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate))
{
DateTime.TryParse(CellDate.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, 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);
}
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);
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;
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);
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);
int weightPoint = CellNamePoint.CellLeft().CellLeft().GetValue <int>();
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error, CellPoint.Address.RowNumber.ToString());
//if (notTakenPoint(CellNamePoint.GetString()))
// maxMark -= weightPoint;
//else
points.Add(curPoint);
}
else
{
string answer = CellPoint.GetString().ToLower();
if (answer == "нет" || answer == "да")
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
curPoint = new Point(CellNamePoint.GetString(), answer == "нет" ? 0 : 1, answer == "нет" ? true : false, true);
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();
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 (Regex.Match(page.Name.ToUpper(), "ВХОДЯЩ").Success)
{
outgoing = 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, calls));
}
}
}
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);
}
}
}
private void ColumnValidator(TemplateRulesPriorityPatient template, IXLCell columndata, IXLTableRow row)
{
// Si el tipo es DATETIME
if (template.Type == Configuration.GetValueConf(Constants.DATE_TIME_TYPE))
{
// Se valida si la fecha del examen esta desactualizada
if (template.ValidateOutdated && !columndata.GetString().Trim().Equals(""))
{
if (template.UnknowValue != null &&
template.NotApply != null &&
!template.UnknowValue.Equals("") &&
!template.NotApply.Equals(""))
{
// Se compara si el valor es el de default si es no se realiza validaciones
if (Convert.ToDateTime(columndata.GetString().Trim()).Equals(Convert.ToDateTime(template.UnknowValue)) ||
Convert.ToDateTime(columndata.GetString().Trim()).Equals(Convert.ToDateTime(template.NotApply)))
{
return;
}
}
try
{
DateTime dateValidation = DateTime.Now.AddMonths(template.MonthOutdated * -1);
if (columndata.GetDateTime() < dateValidation)
{
var val = columndata.GetDateTime() < DateTime.Now ? DateTime.Now - columndata.GetDateTime() : columndata.GetDateTime() - DateTime.Now;
string result = $"Vigencia minima: {template.MonthOutdated} Total: {(int)Math.Truncate((val.TotalDays) / 30)}";
// Inserta en variables desactualizadas y no realiza mas validaciones
insertOutdated(template.Name, columndata.GetString(), result, row);
return;
}
}
catch (Exception ex)
{
IOUtilities.WriteExceptionLog(ex, Configuration.GetClassName <PriorityPatient>());
}
}
}
// Existe un valor desconocido para la columna tipo fecha
if ((template.UnknowValue != null && !template.UnknowValue.Equals("")) || (template.NotApply != null && !template.NotApply.Equals("")))
{
// Se compara si el valor es el de default si es no se realiza validaciones
if (Convert.ToDateTime(columndata.GetString().Trim()).Equals(Convert.ToDateTime(template.UnknowValue)) ||
Convert.ToDateTime(columndata.GetString().Trim()).Equals(Convert.ToDateTime(template.NotApply)))
{
return;
}
}
// Existen un valores permitidos para la columna tipo numero
if (template.AllowValues != null && !template.AllowValues.Equals(""))
{
int dataTmp;
if (int.TryParse(columndata.GetString(), out dataTmp))
{
if (template.AllowValues.Split(',').Where(val => Convert.ToInt32(val) == dataTmp).Count() != 0)
{
return;
}
}
}
validateRangeEq(template, row, columndata.GetString(), template.Type);
validateRangeDif(template, row, columndata.GetString(), template.Type);
validateRangeMin(template, row, columndata.GetString());
validateRangeHigher(template, row, columndata.GetString());
}