public CallIncoming(string phoneNumber, string Link, string date, string comment, string Manager, ProcessedCall call = new ProcessedCall(),
string DealState = "", DateTime DateDeal = new DateTime())
{
if (Link != "")
{
this.Link = new XLHyperlink(new Uri(Link));
}
else
{
this.Link = null;
}
this.phoneNumber = phoneNumber;
this.date = date;
this.comment = comment;
this.Manager = Manager;
this.call = call;
this.DealState = DealState;
this.NoticeCRM = "";
this.DateDeal = "";
if (DealState.ToUpper() != "В РАБОТЕ" && DealState != "")
{
this.DealState = "Закрыт";
this.NoticeCRM = DealState;
this.DateDeal = DateDeal.ToString("dd.MM.yyyy");
}
if (DealState.ToUpper() == "В РАБОТЕ")
{
this.DateDeal = DateDeal.ToString("dd.MM.yyyy");
if (DateDeal.Year < 2000)
{
this.DateDeal = "";
}
}
}
static void ProcessWorkSheet(IXLWorksheet sheet, bool Belfan)
{
var data = sheet.RangeUsed();
int lastcol = data.LastColumn().ColumnNumber();
for (int numRow = data.LastRow().RowNumber(); numRow >= 2; numRow--)
{
var row = sheet.Row(numRow);
ProcessedCall call = new ProcessedCall();
var curCell = row.Cell(1);
call.Client = curCell.GetString();
if (curCell.HasHyperlink && curCell.GetHyperlink().IsExternal)
{
call.Link = curCell.Hyperlink.ExternalAddress.AbsoluteUri;
}
curCell = row.Cell(lastcol - 4);
call.Manager = curCell.GetString();
curCell = curCell.CellRight();
call.Comment = curCell.GetString();
curCell = curCell.CellRight();
call.NoticeCRM = curCell.GetString();
curCell = curCell.CellRight();
call.ClientState = curCell.GetString();
curCell = curCell.CellRight();
if (curCell.GetString() != "" && curCell.DataType == XLDataType.DateTime)
{
call.StartDateAnalyze = curCell.GetDateTime();
}
else
{
if (!DateTime.TryParse(curCell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out call.StartDateAnalyze))
{
DateTime.TryParse(curCell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out call.StartDateAnalyze);
}
}
if ((!Belfan && !calls.Exists(c => (c.Client == call.Client && call.Link == "") || (c.Link == call.Link && call.Link != null))) || (Belfan && !calls.Exists(c => (c.Client == call.Client))))
{
calls.Add(call);
}
else
{
ProcessedCall exCall;
if (!Belfan)
{
exCall = calls.Where(c => (c.Client == call.Client && call.Link == "") || (c.Link == call.Link && call.Link != null)).First();
}
else
{
exCall = calls.Where(c => (c.Client == call.Client)).First();
}
if (exCall.StartDateAnalyze < call.StartDateAnalyze)
{
calls.Remove(exCall);
calls.Add(call);
}
}
}
}
public List <CallIncoming> notRecallAnalyze()
{
List <CallIncoming> returnCalls = new List <CallIncoming>();
var Stages = phones.Stages;
foreach (var call in phones.getPhones())
{
FullCall LastCall = new FullCall(call.Value.phoneNumber,
call.Value.link,
call.Value.stages.First().Key,
call.Value.stages.First().Value.First().Date,
call.Value.stages.First().Value.First().Outgoing,
call.Value.stages.First().Value.First().comment,
call.Value.GetManager());
DateTime datelastcallfirststage = new DateTime();
DateTime datelastcallsecondstage = new DateTime();
foreach (var stage in call.Value.stages)
{
foreach (var curCall in stage.Value)
{
if (curCall.Date > datelastcallfirststage && phones.Stages[stage.Key] == 1)
{
datelastcallfirststage = curCall.Date;
LastCall.date = curCall.Date;
LastCall.stage = stage.Key;
LastCall.outgoing = curCall.Outgoing;
LastCall.Comment = curCall.comment;
}
if (curCall.Date > datelastcallsecondstage && phones.Stages[stage.Key] == 2)
{
datelastcallfirststage = curCall.Date;
}
}
}
if (datelastcallfirststage != datelastcallsecondstage)
{
var AddedCall = new ProcessedCall();
AddedCall.Client = call.Value.phoneNumber;
AddedCall.Link = call.Value.link;
AddedCall.Comment = LastCall.Comment;
if (!InputDoc.hasPhone(processedCalls, AddedCall))
{
returnCalls.Add(new CallIncoming(call.Value.phoneNumber, call.Value.link, String.Format("{0:dd.MM.yy}", datelastcallfirststage), LastCall.Comment + "\nДата второго звонка: " + (datelastcallsecondstage.Year > 2000 ? datelastcallsecondstage.ToString("dd.MM.yyyy") : "отсутствует"), call.Value.GetManager(), new ProcessedCall(), call.Value.DealState, call.Value.DateDeal));
}
else
{
var samecall = InputDoc.getSamePhone(processedCalls, AddedCall);
if (samecall.ClientState != null && samecall.ClientState.ToUpper() == "В РАБОТЕ")
{
returnCalls.Add(new CallIncoming(call.Value.phoneNumber, call.Value.link,
String.Format("{0:dd.MM.yy}", datelastcallfirststage),
LastCall.Comment + "\nДата второго звонка: " + (datelastcallsecondstage.Year > 2000 ? datelastcallsecondstage.ToString("dd.MM.yyyy") : "отсутствует"), call.Value.GetManager(), samecall, call.Value.DealState, call.Value.DateDeal));
}
}
}
}
return(returnCalls);
}
public List <CallPreAgreement> getCallsPreAgreement()
{
List <CallPreAgreement> returnCalls = new List <CallPreAgreement>();
var Stages = phones.Stages;
foreach (var phone in phones.getPhones())
{
string lastStage = getLastStage(phone.Value, phones.Stages);
if (lastStage == PreAgreementStage)
{
OneCall FirstCall = phone.Value.stages[lastStage].First();
foreach (var call in phone.Value.stages[lastStage])
{
if (call.Date < FirstCall.Date)
{
FirstCall = call;
}
}
OneCall LastCall = FirstCall;
foreach (var stage in phone.Value.stages)
{
foreach (var call in stage.Value)
{
if (LastCall.Date < call.Date)
{
LastCall = (OneCall)call.Clone();
if (stage.Key != lastStage)
{
LastCall.comment = LastCall.comment + " (" + stage.Key + ") ";
}
}
}
}
var AddedCall = new ProcessedCall();
AddedCall.Client = phone.Value.phoneNumber;
AddedCall.Comment = LastCall.comment;
if (!InputDoc.hasPhone(processedCalls, AddedCall))
{
returnCalls.Add(new CallPreAgreement(phone.Value.phoneNumber, phone.Value.link, String.Format("{0:dd.MM.yy}", LastCall.Date), LastCall.comment, lastStage, phone.Value.GetManager(), new ProcessedCall(), phone.Value.DealState, phone.Value.DateDeal));
}
else
{
var samecall = InputDoc.getSamePhone(processedCalls, AddedCall);
if (samecall.ClientState != null && samecall.ClientState.ToUpper() == "В РАБОТЕ")
{
returnCalls.Add(new CallPreAgreement(phone.Value.phoneNumber, phone.Value.link,
String.Format("{0:dd.MM.yy}", LastCall.Date),
LastCall.comment, lastStage, phone.Value.GetManager(), samecall, phone.Value.DealState, phone.Value.DateDeal));
}
}
}
}
return(returnCalls);
}
public List <CallIncoming> getIncomeWithoutOutGoing()
{
List <CallIncoming> returnCalls = new List <CallIncoming>();
var Stages = phones.Stages;
foreach (var call in phones.getPhones())
{
FullCall LastCall = new FullCall(call.Value.phoneNumber,
call.Value.link,
call.Value.stages.First().Key,
call.Value.stages.First().Value.First().Date,
call.Value.stages.First().Value.First().Outgoing,
call.Value.stages.First().Value.First().comment,
call.Value.GetManager());
foreach (var stage in call.Value.stages)
{
foreach (var curCall in stage.Value)
{
if (curCall.Date > LastCall.date || (curCall.Date == LastCall.date && curCall.Outgoing))
{
LastCall.date = curCall.Date;
LastCall.stage = stage.Key;
LastCall.outgoing = curCall.Outgoing;
LastCall.Comment = curCall.comment;
}
}
}
// !LastCall.outgoing && убрали, так как захотели, чтоб анализировали не только входящие
if (!call.Value.stages.ContainsKey(AgreementStage))
{
var AddedCall = new ProcessedCall();
AddedCall.Client = call.Value.phoneNumber;
AddedCall.Link = call.Value.link;
AddedCall.Comment = LastCall.Comment;
if (!InputDoc.hasPhone(processedCalls, AddedCall))
{
returnCalls.Add(new CallIncoming(call.Value.phoneNumber, call.Value.link, String.Format("{0:dd.MM.yy}", LastCall.date), LastCall.Comment, call.Value.GetManager(), new ProcessedCall(), call.Value.DealState, call.Value.DateDeal));
}
else
{
var samecall = InputDoc.getSamePhone(processedCalls, AddedCall);
if (samecall.ClientState != null && samecall.ClientState.ToUpper() == "В РАБОТЕ")
{
returnCalls.Add(new CallIncoming(call.Value.phoneNumber, call.Value.link,
String.Format("{0:dd.MM.yy}", LastCall.date),
LastCall.Comment, call.Value.GetManager(), samecall, call.Value.DealState, call.Value.DateDeal));
}
}
}
}
return(returnCalls);
}
public List <CallPreAgreement> getCallsPreAgreement()
{
List <CallPreAgreement> returnCalls = new List <CallPreAgreement>();
var Stages = phones.Stages;
foreach (var call in phones.getPhones())
{
int NumMaxStage = Stages.Values.Max();
string maxStage = call.Value.stages.First().Key;
foreach (var stage in call.Value.stages)
{
if (Stages[maxStage] < Stages[stage.Key])
{
maxStage = stage.Key;
}
}
if (Stages[maxStage] == NumMaxStage - 3)
{
OneCall compcall = call.Value.stages[maxStage].First();
foreach (var date in call.Value.stages[maxStage])
{
if (compcall.Date < date.Date)
{
compcall = date;
}
}
var AddedCall = new ProcessedCall();
AddedCall.Client = call.Key;
AddedCall.Link = "";
AddedCall.Comment = compcall.comment;
if (!InputDoc.hasPhone(processedCalls, AddedCall))
{
returnCalls.Add(new CallPreAgreement(call.Value.phoneNumber, call.Value.link, String.Format("{0:dd.MM.yy}", compcall.Date), compcall.comment, maxStage, call.Value.GetManager(), new ProcessedCall(), call.Value.DealState, call.Value.DateDeal));
}
else
{
var samecall = InputDoc.getSamePhone(processedCalls, AddedCall);
if (samecall.ClientState != null && samecall.ClientState.ToUpper() == "В РАБОТЕ")
{
returnCalls.Add(new CallPreAgreement(call.Value.phoneNumber, call.Value.link, String.Format("{0:dd.MM.yy}", compcall.Date), compcall.comment, maxStage, call.Value.GetManager(),
samecall, call.Value.DealState, call.Value.DateDeal));
}
}
}
}
return(returnCalls);
}
public static ProcessedCall getSamePhone(List <ProcessedCall> calls, ProcessedCall phone)
{
return(calls.Where(c =>
((c.Client == phone.Client && phone.Link == "" && c.Link == "") || (c.Link == phone.Link && phone.Link != ""))
//&&
//(
////Regex.Match(phone.Comment.Substring(0, 20), c.Comment.Substring(0, 20), RegexOptions.IgnoreCase).Success
//// || Regex.Match(c.Comment.Substring(0, 20), phone.Comment.Substring(0, 20), RegexOptions.IgnoreCase).Success
//// ||
// c.Comment == phone.Comment
// )
//&& !Regex.Match(c.ClientState, "Закрыт", RegexOptions.IgnoreCase).Success
//&& (c.StartDateAnalyze < DateTime.Today)
).FirstOrDefault());
}
public static bool hasPhone(List <ProcessedCall> calls, ProcessedCall phone)
{
if (calls.Exists(c =>
((c.Client == phone.Client && phone.Link == "" && c.Link == "") || (c.Link == phone.Link && phone.Link != ""))
// &&
//(
//Regex.Match(phone.Comment.Substring(0,20),c.Comment.Substring(0, 20), RegexOptions.IgnoreCase).Success
// || Regex.Match(c.Comment.Substring(0, 20), phone.Comment.Substring(0, 20), RegexOptions.IgnoreCase).Success
// ||
// c.Comment == phone.Comment
//)
)
)
{
return(true);
}
else
{
return(false);
}
}
public void CleanSuccess(ref List <ProcessedCall> processedCalls)
{
List <string> toDel = new List <string>();
foreach (var call in Calls)
{
if (Regex.Match(call.Value.DealState, "Успешн", RegexOptions.IgnoreCase).Success)
{
toDel.Add(call.Key);
ProcessedCall procCall = new ProcessedCall();
procCall.Client = call.Value.phoneNumber;
procCall.Manager = call.Value.GetManager();
procCall.StartDateAnalyze = call.Value.DateDeal;
procCall.NoticeCRM = call.Value.DealState;
procCall.ClientState = "Закрыт";
procCall.Link = call.Value.link;
procCall.Comment = "";
processedCalls.RemoveAll(c => (c.Client == call.Value.phoneNumber && call.Value.link == "") || (c.Link == call.Value.link && call.Value.link != ""));
processedCalls.Add(procCall);
}
if (call.Value.DealState.ToUpper().Trim() == "В РАБОТЕ" && call.Value.DateDeal > DateTime.Today.AddDays(-1))
{
toDel.Add(call.Key);
ProcessedCall procCall = new ProcessedCall();
procCall.Client = call.Value.phoneNumber;
procCall.Manager = call.Value.GetManager();
procCall.StartDateAnalyze = call.Value.DateDeal;
procCall.NoticeCRM = call.Value.DealState;
procCall.ClientState = "В работе";
procCall.Link = call.Value.link;
procCall.Comment = "";
processedCalls.RemoveAll(c => (c.Client == call.Value.phoneNumber && call.Value.link == "") || (c.Link == call.Value.link && call.Value.link != ""));
processedCalls.Add(procCall);
}
}
toDel.ForEach(k => Calls.Remove(k));
}
public List <CallPerWeek> getCallsPerWeek()
{
List <CallPerWeek> returnCalls = new List <CallPerWeek>();
var Stages = phones.Stages;
foreach (var call in phones.getPhones())
{
FullCall LastCall = new FullCall(call.Value.phoneNumber,
call.Value.link,
call.Value.stages.First().Key,
call.Value.stages.First().Value.First().Date,
call.Value.stages.First().Value.First().Outgoing,
call.Value.stages.First().Value.First().comment,
call.Value.GetManager());
foreach (var stage in call.Value.stages)
{
foreach (var curCall in stage.Value)
{
if (curCall.Date > LastCall.date)
{
LastCall.date = curCall.Date;
LastCall.stage = stage.Key;
LastCall.outgoing = curCall.Outgoing;
LastCall.Comment = curCall.comment;
}
}
}
TimeSpan t1 = DateTime.Now.Subtract(LastCall.date);
if (((t1.TotalDays >= 23 && !DS) || (DS && t1.TotalDays > 61)) && !call.Value.stages.ContainsKey(AgreementStage))
{
CallPerWeek curCall = new CallPerWeek();
curCall.FirstWeek = "-";
curCall.phoneNumber = call.Value.phoneNumber;
curCall.Manager = call.Value.GetManager();
if (call.Value.link != "")
{
curCall.Link = new XLHyperlink(new Uri(call.Value.link));
}
curCall.comment = LastCall.Comment;
if (!LastCall.outgoing)
{
curCall.comment = curCall.comment + " (Входящий)";
}
if (t1.TotalDays >= 30 && !DS)
{
curCall.SecondWeek = "-";
}
else
{
curCall.SecondWeek = "+";
}
//if (t1.TotalDays >= 22)
//{
// curCall.ThirdWeek = "-";
//}
//else
//{
// curCall.ThirdWeek = "+";
//}
var AddedCall = new ProcessedCall();
AddedCall.Client = curCall.phoneNumber;
AddedCall.Link = call.Value.link;
AddedCall.Comment = curCall.comment;
curCall.DateDeal = "";
if (call.Value.DealState.ToUpper() != "В РАБОТЕ" && call.Value.DealState != "")
{
curCall.DealState = "Закрыт";
curCall.NoticeCRM = call.Value.DealState;
curCall.DateDeal = call.Value.DateDeal.ToString("dd.MM.yyyy");
}
if (call.Value.DealState.ToUpper() == "В РАБОТЕ")
{
curCall.DealState = call.Value.DealState;
if (call.Value.DateDeal.Year > 2000)
{
curCall.DateDeal = call.Value.DateDeal.ToString("dd.MM.yyyy");
}
}
if (!InputDoc.hasPhone(processedCalls, AddedCall))
{
returnCalls.Add(curCall);
}
else
{
var samecall = InputDoc.getSamePhone(processedCalls, AddedCall);
if (samecall.ClientState != null && samecall.ClientState.ToUpper() == "В РАБОТЕ")
{
curCall.call = samecall;
returnCalls.Add(curCall);
}
}
}
}
return(returnCalls);
}
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 List <CallOneStage> getCallsOneStage()
{
List <CallOneStage> returnCalls = new List <CallOneStage>();
var Stages = phones.Stages;
foreach (var phone in phones.getPhones())
{
string lastStage = getLastStage(phone.Value, phones.Stages);
OneCall FirstCall = phone.Value.stages[lastStage].First();
foreach (var call in phone.Value.stages[lastStage])
{
if (call.Date < FirstCall.Date)
{
FirstCall = call;
}
}
List <OneCall> dt = new List <OneCall>();
dt.Add(FirstCall);
foreach (var stage in phone.Value.stages)
{
foreach (var call in stage.Value)
{
if (call.Date > FirstCall.Date)
{
OneCall addcall = (OneCall)call.Clone();
if (stage.Key != lastStage)
{
addcall.comment = call.comment + " (" + stage.Key + ")";
}
dt.Add(addcall);
}
}
}
if (lastStage != AgreementStage && dt.Count > 2)
{
CallOneStage curCall = new CallOneStage();
curCall.phoneNumber = phone.Value.phoneNumber;
curCall.Manager = phone.Value.GetManager();
if (phone.Value.link != "")
{
curCall.Link = new XLHyperlink(new Uri(phone.Value.link));
}
curCall.qty = dt.Count.ToString();
curCall.stage = lastStage;
curCall.date = "";
string comment = "";
DateTime lastDate = dt.First().Date;
List <DateTime> uniqDT = new List <DateTime>();
foreach (var call in dt)
{
if (!uniqDT.Contains(call.Date))
{
curCall.date = curCall.date + String.Format("{0:dd.MM.yy}", call.Date) + ", ";
uniqDT.Add(call.Date);
}
if (lastDate < call.Date)
{
comment = call.comment;
lastDate = call.Date;
}
}
curCall.date = curCall.date.TrimEnd(' ').Trim(',');
curCall.comment = comment;
var AddedCall = new ProcessedCall();
AddedCall.Client = phone.Value.phoneNumber;
AddedCall.Link = phone.Value.link;
AddedCall.Comment = curCall.comment;
curCall.DateDeal = "";
if (phone.Value.DealState.ToUpper() != "В РАБОТЕ" && phone.Value.DealState != "")
{
curCall.DealState = "Закрыт";
curCall.NoticeCRM = phone.Value.DealState;
curCall.DateDeal = phone.Value.DateDeal.ToString("dd.MM.yyyy");
}
if (phone.Value.DealState.ToUpper() == "В РАБОТЕ")
{
curCall.DealState = phone.Value.DealState;
if (phone.Value.DateDeal.Year > 2000)
{
curCall.DateDeal = phone.Value.DateDeal.ToString("dd.MM.yyyy");
}
}
if (!InputDoc.hasPhone(processedCalls, AddedCall))
{
returnCalls.Add(curCall);
}
else
{
var samecall = InputDoc.getSamePhone(processedCalls, AddedCall);
if (samecall.ClientState != null && samecall.ClientState.ToUpper() == "В РАБОТЕ")
{
curCall.call = samecall;
returnCalls.Add(curCall);
}
}
}
}
return(returnCalls);
}
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 List <CallOneStage> getCallsOneStage()
{
List <CallOneStage> returnCalls = new List <CallOneStage>();
var Stages = phones.Stages;
foreach (var call in phones.getPhones())
{
string maxStage = call.Value.stages.First().Key;
foreach (var stage in call.Value.stages)
{
if (Stages[maxStage] < Stages[stage.Key])
{
maxStage = stage.Key;
}
}
if (Stages[maxStage] < Stages.Values.Max() && call.Value.stages[maxStage].Count > 2)
{
CallOneStage curCall = new CallOneStage();
curCall.phoneNumber = call.Value.phoneNumber;
if (call.Value.link != "")
{
curCall.Link = new XLHyperlink(new Uri(call.Value.link));
}
curCall.qty = call.Value.stages[maxStage].Count.ToString();
curCall.stage = maxStage;
curCall.date = "";
curCall.Manager = call.Value.GetManager();
string comment = "";
foreach (var date in call.Value.stages[maxStage])
{
curCall.date = curCall.date + String.Format("{0:dd.MM.yy}", date.Date) + ", ";
comment = date.comment;
}
curCall.date = curCall.date.TrimEnd(' ').Trim(',');
curCall.comment = comment;
var AddedCall = new ProcessedCall();
AddedCall.Client = curCall.phoneNumber;
AddedCall.Link = "";
AddedCall.Comment = curCall.comment;
curCall.DateDeal = "";
if (call.Value.DealState.ToUpper() != "В РАБОТЕ" && call.Value.DealState != "")
{
curCall.DealState = "Закрыт";
curCall.NoticeCRM = call.Value.DealState;
curCall.DateDeal = call.Value.DateDeal.ToString("dd.MM.yyyy");
}
if (call.Value.DealState.ToUpper() == "В РАБОТЕ")
{
curCall.DealState = call.Value.DealState;
if (call.Value.DateDeal.Year > 2000)
{
curCall.DateDeal = call.Value.DateDeal.ToString("dd.MM.yyyy");
}
}
if (!InputDoc.hasPhone(processedCalls, AddedCall))
{
returnCalls.Add(curCall);
}
else
{
var samecall = InputDoc.getSamePhone(processedCalls, AddedCall);
if (samecall.ClientState != null && samecall.ClientState.ToUpper() == "В РАБОТЕ")
{
curCall.call = samecall;
returnCalls.Add(curCall);
}
}
}
}
return(returnCalls);
}