public void VSOP2()//DateTime start, DateTime end, ProgressBar progress)
{
var vm = DataContext as SendGroupViewModel;
excelReport excel = new excelReport();
excel.Init("ВСОП_2.xlsx", string.Format(@"ВСОП_2_{0}_{1}__{2}_{3}.xlsx",
vm.OtprDat.Value.ToString("dd.MM.yyyy"),
"Новосибирск",
vm.City,
vm.Persons.Count), otchetDir: otchetDir);
int ri = 1;
var db = new ProvodnikContext();
var ids = vm.Persons.Select(pp => pp.PersonId).ToList();
var rr = db.Persons.Where(pp => ids.Contains(pp.Id));
foreach (var r in rr)//vm.Persons)
{
ri++;
excel.cell[ri, 1].value2 = ri - 1;
excel.cell[ri, 2].value2 = vm.RegOtdelenie;
var fio = new StringHelper().ParseFio(r.Fio);
excel.cell[ri, 3].value2 = fio[0];
excel.cell[ri, 4].value2 = fio[1];
excel.cell[ri, 5].value2 = fio[2];
excel.cell[ri, 6].value2 = r.BirthDat.HasValue ? r.BirthDat.Value.ToString("dd.MM.yyyy") : "";
excel.cell[ri, 7].value2 = r.UchebCentr;
excel.cell[ri, 8].value2 = (r.ExamenDat.HasValue) ? r.ExamenDat.Value.Year.ToString()
: (r.UchebEndDat.HasValue ? r.UchebEndDat.Value.Year.ToString() : "");
}
excel.setAllBorders(excel.get_Range("A2", "I" + ri));
excel.Finish();
}
public void VSOP3(bool otdelno = false)//DateTime start, DateTime end, ProgressBar progress)
{
var vm = DataContext as SendGroupViewModel;
excelReport excel = new excelReport();
if (!otdelno)
{
excel.Init("ВСОП_3.xlsx", string.Format(@"ВСОП_3_{0}_{1}__{2}_{3}.xlsx",
vm.OtprDat.Value.ToString("dd.MM.yyyy"),
"Новосибирск",
vm.City,
vm.Persons.Count), otchetDir: otchetDir);
}
else
{
excel.Init("ВСОП_3.xlsx", string.Format(@"ВСОП_3_{0}_{1}__{2}_{3}.xlsx",
vm.OtprDat.Value.ToString("dd.MM.yyyy"),
"Новосибирск",
vm.City,
vm.Persons.Count), true);
}
int ri = 1;
var db = new ProvodnikContext();
var ids = vm.Persons.Select(pp => pp.PersonId).ToList();
var rr = db.Persons.Where(pp => ids.Contains(pp.Id));
ri = 2;
excel.cell[ri, 2].value2 = vm.RegOtdelenie;
excel.cell[ri, 3].value2 = vm.Persons.Count;
excel.cell[ri, 4].value2 = vm.Poezd;
excel.cell[ri, 5].value2 = vm.Vagon;
excel.cell[ri, 6].value2 = vm.OtprDat.HasValue ? vm.OtprDat.Value.ToString("dd.MM.yyyy") : "";
excel.cell[ri, 7].value2 = vm.PribDat.HasValue ? vm.PribDat.Value.ToString("dd.MM.yyyy") : "";
excel.cell[ri, 8].value2 = vm.PribTime;
var main = vm.Persons.FirstOrDefault(pp => pp.IsMain);
if (main != null)
{
excel.cell[ri, 9].value2 = main.Fio;
excel.cell[ri, 10].value2 = Helper.FormatPhone(main.Phone);
}
excel.cell[ri, 11].value2 = vm.Vstrechat?"да":"нет";
excel.cell[ri, 12].value2 = vm.Vokzal;
ri = 1;
foreach (var r in rr)//vm.Persons)
{
ri++;
excel.cell[ri, 13].value2 = r.Fio;
}
excel.setAllBorders(excel.get_Range("B2", "M" + ri));
excel.Finish(!otdelno);
}
private void ScansButton_Click(object sender, RoutedEventArgs e)
{
excelReport excel = new excelReport();
excel.Init("Сканы.xltx", $"Сканы.xlsx");//,visible:true);//, otchetDir: otchetDir);
int ri = 1;
var db = new ProvodnikContext();
var ids = vm.PersonList.Select(pp => pp.Id).ToList();
var qq = from p in db.Persons
join pd in db.PersonDocs on p.Id equals pd.PersonId
//join d in db.DocTypes on
where ids.Contains(p.Id) && pd.FileName != null
group pd by p into g
select new
{
g.Key.Fio,
g.Key.Otryad,
scans = g.Select(pp => pp.DocTypeId)
};
var dts = db.DocTypes.ToList();
int c = 2;
foreach (var dt in dts)
{
c++;
excel.cell[1, c].value2 = dt.Description;
}
foreach (var r in qq.OrderBy(pp => pp.Fio))
{
ri++;
excel.cell[ri, 1].value2 = r.Fio;
excel.cell[ri, 2].value2 = r.Otryad;
var set = new HashSet <int>(r.scans);
c = 2;
foreach (var dt in dts)
{
c++;
excel.cell[ri, c].value2 = set.Contains(dt.Id) ? 1 : 0;
}
}
excel.setAllBorders(excel.mySheet.Range[excel.cell[1, 1], excel.cell[ri, 2 + dts.Count]]);
excel.myExcel.Visible = true;
}
public void F6() //DateTime start, DateTime end, ProgressBar progress)
{
var vm = DataContext as SendGroupViewModel;
excelReport excel = new excelReport();
excel.Init(/*(vm.City== "Москва")?"Ф6_Msk.xlsx":*/ "Ф6_ost.xlsx",
$@"Ф6_{vm.OtprDat.Value.ToString("dd.MM.yyyy")}_Новосибирск__{ vm.City}_{vm.Persons.Count}.xlsx",
otchetDir: otchetDir//,visible:true
);
int ri = 7;
var db = new ProvodnikContext();
var ids = vm.Persons.Select(pp => pp.PersonId).ToList();
var rr = db.Persons.Where(pp => ids.Contains(pp.Id));
excel.cell[4, 1].value2 = vm.DepoRod;// new Repository().GetF6Depo(vm.City, vm.Depo);
if (vm.Persons.Count > 1)
{
excel.get_Range("A9", "A" + (8 + vm.Persons.Count - 1)).EntireRow.Select();
excel.myExcel.Selection.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
foreach (var r in rr)//vm.Persons)
{
ri++;
excel.cell[ri, 1].value2 = ri - 7;
excel.cell[ri, 2].value2 = r.Fio + Environment.NewLine + (r.BirthDat.HasValue ? r.BirthDat.Value.ToString("dd.MM.yyyy") : "")
+ Environment.NewLine + r.MestoRozd;
excel.cell[ri, 3].value2 = "Проводник" + Environment.NewLine + "пассажирского" + Environment.NewLine + "вагона";
excel.cell[ri, 5].value2 = r.PaspAdres;
excel.cell[ri, 6].value2 =
r.Grazdanstvo == "КЗ"? "Паспорт гр-на РК:" : "Паспорт гр-на РФ:"
+ Environment.NewLine + r.PaspSeriya + " " + r.PaspNomer
+ Environment.NewLine + Helper.FormatSnils(r.Snils);
excel.cell[ri, 7].value2 = (!string.IsNullOrWhiteSpace(vm.PeresadSt))
? $"Новосибирск – {vm.PeresadSt} – {vm.City} – {vm.PeresadSt} – Новосибирск"
: $"Новосибирск – {vm.City} – Новосибирск";
excel.cell[ri, 11].value2 = vm.Uvolnenie.HasValue ? vm.Uvolnenie.Value.ToString("dd.MM.yyyy") : "";
}
excel.setAllBorders(excel.get_Range("A8", "K" + ri));
excel.Finish();
}
public void VSOP1() //DateTime start, DateTime end, ProgressBar progress)
{
var vm = DataContext as SendGroupViewModel;
excelReport excel = new excelReport();
excel.Init("ВСОП_1.xlsx", string.Format(@"ВСОП_1_{0}_{1}__{2}_{3}.xlsx",
vm.OtprDat.Value.ToString("dd.MM.yyyy"),
"Новосибирск",
vm.City,
vm.Persons.Count), otchetDir: otchetDir);
int ri = 1;
var db = new ProvodnikContext();
var ids = vm.Persons.Select(pp => pp.PersonId).ToList();
var rr = db.Persons.Where(pp => ids.Contains(pp.Id));
foreach (var r in rr)//vm.Persons)
{
ri++;
excel.cell[ri, 1].value2 = vm.RegOtdelenie;
excel.cell[ri, 2].value2 = r.Fio;
excel.cell[ri, 3].value2 = vm.Depo;
excel.cell[ri, 4].value2 = Helper.FormatPhone(r.Phone);
excel.cell[ri, 5].value2 = r.VaccineSert;
excel.cell[ri, 6].value2 = r.VaccineSertDat?.ToString("dd.MM.yyyy");
excel.cell[ri, 7].value2 = r.UchZavedenie;
excel.cell[ri, 8].value2 = r.UchForma;
excel.cell[ri, 9].value2 = r.Grazdanstvo;
excel.cell[ri, 10].value2 = r.Otryad;
excel.cell[ri, 11].value2 = r.UchebCentr;
excel.cell[ri, 12].value2 = (r.ExamenDat.HasValue)?r.ExamenDat.Value.Year.ToString()
: (r.UchebEndDat.HasValue?r.UchebEndDat.Value.Year.ToString():"");
excel.cell[ri, 13].value2 = Helper.FormatPhone(r.RodPhone);
excel.cell[ri, 14].value2 = r.RodFio;
}
excel.setAllBorders(excel.get_Range("A2", "N" + ri));
excel.Finish();
}
private void SpravkiPSOButton_Click(object sender, RoutedEventArgs e)
{
var db = new ProvodnikContext();
var ids = vm.PersonList.Select(pp => pp.Id).ToList();
var idsWithSpravka = new HashSet <int>(from p in db.Persons
where ids.Contains(p.Id)
join pd in db.PersonDocs on p.Id equals pd.PersonId
where pd.DocTypeId == DocConsts.СправкаРСО && pd.FileName != null
select p.Id);
var persons = vm.PersonList.Where(p => !idsWithSpravka.Contains(p.Id));
if (!persons.Any())
{
MessageBox.Show("Справки есть у всех");
return;
}
excelReport excel = new excelReport();
excel.Init("Справки РСО.xltx", $"Справки РСО { DateTime.Now.Ticks}.xlsx");//,visible:true);//, otchetDir: otchetDir);
int ri = 1;
foreach (var r in persons)
{
ri++;
excel.cell[ri, 1].value2 = ri - 1;
excel.cell[ri, 2].value2 = r.Fio;
//excel.cell[ri, 3].value2 = "АО \"ФПК\"";
excel.cell[ri, 4].value2 = r.BirthDat?.ToString("dd.MM.yyyy");
excel.cell[ri, 5].value2 = r.UchZavedenie;
excel.cell[ri, 6].value2 = r.UchFac;
excel.cell[ri, 7].value2 = r.Inn;
excel.cell[ri, 8].value2 = Helper.FormatSnils(r.Snils);
//excel.cell[ri, 9].value2 = "01.04.2021 - 31.12.2021 г.";
}
excel.setAllBorders(excel.get_Range("A1", "I" + ri));
excel.myExcel.Visible = true;
}
public void Napravleniya() //DateTime start, DateTime end, ProgressBar progress)
{
var vm = DataContext as MedKomZayavkaViewModel;
var num = "";
foreach (var n in vm.Name)
{
if (char.IsDigit(n))
{
num += n;
}
else
{
break;
}
}
excelReport excel = new excelReport();
excel.Init("Направление мед.xltx", $"Список на мед.комиссию{num}Новосибирское РО_{vm.Depo}.xlsx", false, System.IO.Path.GetTempPath());//,visible:true);//, otchetDir: otchetDir);
int ri = 1;
var db = new ProvodnikContext();
foreach (var r in vm.Persons)
{
ri++;
excel.cell[ri, 1].value2 = ri - 1;
excel.cell[ri, 2].value2 = r.VihodDat?.ToString("dd.MM.yyyy");
excel.cell[ri, 3].value2 = vm.Depo;
excel.cell[ri, 4].value2 = "Новосибирское РО";
excel.cell[ri, 5].value2 = r.Fio;
excel.cell[ri, 6].value2 = r.BirthDat?.ToString("dd.MM.yyyy");
excel.cell[ri, 7].value2 = vm.BolnicaName;
excel.cell[ri, 8].value2 = vm.BolnicaAdres;
}
excel.setAllBorders(excel.get_Range("A1", "H" + ri));
excel.myExcel.Visible = true;
excel.Finish(false);
}
private void VoronkaButton_Click(object sender, RoutedEventArgs e)
{
excelReport excel = new excelReport();
excel.Init("Воронка.xltx", $"Воронка { DateTime.Today.ToString("dd.MM.yyyy")}.xlsx");//,visible:true);//, otchetDir: otchetDir);
int ri = 1;
var db = new ProvodnikContext();
var ids = vm.PersonList.Select(pp => pp.Id).ToList();
var qq = from p in db.Persons
where ids.Contains(p.Id)
select new
{
p.UchebGruppa,
seliObuch = p.UchebGruppa != null, //UchebStartDat.HasValue,
vibilObuch = p.UchebGruppa != null && p.IsVibil, //p.VibilPrichina == "выбыл с обучения",
ostObuch = p.UchebGruppa != null && !p.IsVibil, //p.VibilPrichina != "выбыл с обучения",
p.IsExamen,
p.IsExamenFailed,
oshibokSvidet = p.IsSertificatError,
poluchenoSvidet = p.SertificatDat.HasValue, //db.PersonDocs.Any(x=>x.PersonId==p.Id && x.DocTypeId==DocConsts.СвидетельствоПрофессии && x.FileName!=null),
zakazanoNapr = p.IsNaprMedZakazano,
gotovoNapr = p.IsNaprMedPolucheno,
vishel = p.IsNaprMedVidano,
goden = p.IsMedKomm,
neGoden = p.VibilPrichina == "не допущен медкомиссией", //"не годен",
p.IsNovichok
};
int startCol = 2;
foreach (var r in qq.GroupBy(x => x.UchebGruppa).OrderBy(x => x.Key).Where(x => x.Key != null))
{
ri++;
excel.cell[ri, startCol + 2].value2 = r.Key;
excel.cell[ri, startCol + 3].value2 = r.Count();
excel.cell[ri, startCol + 4].value2 = r.Count(x => x.vibilObuch);
excel.cell[ri, startCol + 5].value2 = r.Count(x => x.ostObuch);
excel.cell[ri, startCol + 6].value2 = r.Count(x => x.IsExamen);
excel.cell[ri, startCol + 7].value2 = r.Count(x => x.IsExamenFailed);
excel.cell[ri, startCol + 8].value2 = r.Count(x => x.oshibokSvidet);
excel.cell[ri, startCol + 9].value2 = r.Count(x => x.poluchenoSvidet);
}
foreach (var r in qq.GroupBy(x => x.IsNovichok).OrderByDescending(x => x.Key))
{
ri++;
excel.cell[ri, startCol + 2].value2 = r.Key ? "Всего новички" : "Старики";
excel.cell[ri, startCol + 3].value2 = r.Count(x => x.seliObuch);
excel.cell[ri, startCol + 4].value2 = r.Count(x => x.vibilObuch);
excel.cell[ri, startCol + 5].value2 = r.Count(x => x.ostObuch);
excel.cell[ri, startCol + 6].value2 = r.Count(x => x.IsExamen);
excel.cell[ri, startCol + 7].value2 = r.Count(x => x.IsExamenFailed);
excel.cell[ri, startCol + 8].value2 = r.Count(x => x.oshibokSvidet);
excel.cell[ri, startCol + 9].value2 = r.Count(x => x.poluchenoSvidet);
}
excel.cell[2, startCol + 10].value2 = qq.Count(x => x.zakazanoNapr);
excel.cell[2, startCol + 11].value2 = qq.Count(x => x.gotovoNapr);
excel.cell[2, startCol + 12].value2 = qq.Count(x => x.vishel);
excel.cell[2, startCol + 13].value2 = qq.Count(x => x.goden);
excel.cell[2, startCol + 14].value2 = qq.Count(x => x.neGoden);
ri++;
excel.cell[ri, startCol + 2].value2 = "ИТОГО:";
excel.cell[ri, startCol + 3].Formula = excel.cell[ri, startCol + 9].Formula = "=R[-1]C+R[-2]C";
for (int i = 10; i <= 14; i++)
{
excel.mySheet.Range[excel.cell[2, startCol + i], excel.cell[ri - 1, startCol + i]].Merge();
excel.cell[ri, startCol + i].Formula = "=R2C";
}
excel.cell[2, startCol + 1].value2 = ids.Count;//.Formula = $"=R{ri}C{startCol +3}";
excel.setAllBorders(excel.get_Range("A1", "P" + ri));
excel.myExcel.Visible = true;
}
public async void ExportToExcel(List <int> ids)
{
try
{
// await new ProgressRunner().RunAsync(doAction);
await new ProgressRunner().RunAsync(
new Action <ProgressHandler>((progressChanged) =>
{
progressChanged(1, "Экспорт");
excelReport excel = new excelReport();
excel.Init("Экспорт.xltx", $"Экспорт{DateTime.Now.Ticks}.xlsx"); //,visible:true);//, otchetDir: otchetDir);
int ri = 1;
var db = new ProvodnikContext();
var rr = db.Persons.Where(pp => ids.Contains(pp.Id)).ToList();
var labels = new string[] { //"№",
"Номер договора", "Дата договора", "ФИО", "Пол", "Телефон", "Отряд", "Логин ВК", "Гражданство"
, "Новичок", "Учебное заведение", "Форма обучения", "Год окончания обучения", "Есть льгота"
, "ФИО родителя", "Контактный телефон родителей", "Есть форма", "Размер формы"
, "Дата рождения", "Место рождения", "Серия паспорта", "Номер паспорта", "Кем выдан", "Когда выдан"
, "Прописка по паспорту", "Адрес фактического места жительства", "Дата врем.регистрации", "Номер ПФ (снилс)", "ИНН"
, "Учебный центр", "Номер учебной группы", "Дата окончания обучения"
, "Дата выхода", "Желаемый город работы", "Выбыл", "Причина", "Все сканы", "Примечания", "Заметки"
};
int c = 0;
foreach (var l in labels)
{
c++; excel.cell[1, c].value2 = l;
}
progressChanged(5);
var share = 95.0 / rr.Count;
var errors = new List <string>();
foreach (var r in rr) //vm.Persons)
{
ri++;
c = 0;
//c++; excel.cell[ri, c].value2 = ri - 1;
c++; excel.cell[ri, c].value2 = r.Dogovor;
c++; excel.cell[ri, c].value2 = r.DogovorDat?.ToString("dd.MM.yyyy");
c++; excel.cell[ri, c].value2 = r.Fio;
c++; excel.cell[ri, c].value2 = r.Pol;
c++; excel.cell[ri, c].value2 = Helper.FormatPhone(r.Phone);
c++; excel.cell[ri, c].value2 = r.Otryad;
c++; excel.cell[ri, c].value2 = r.Vk;
c++; excel.cell[ri, c].value2 = r.Grazdanstvo;
c++; excel.cell[ri, c].value2 = r.IsNovichok ? "да" : "нет";
c++; excel.cell[ri, c].value2 = r.UchZavedenie;
c++; excel.cell[ri, c].value2 = r.UchForma;
c++; excel.cell[ri, c].value2 = r.UchGod;
c++; excel.cell[ri, c].value2 = /*r.HasLgota*/ r.UchForma == UchFormaConsts.Ochnaya ? "да" : "нет";
c++; excel.cell[ri, c].value2 = r.RodFio;
c++; excel.cell[ri, c].value2 = Helper.FormatPhone(r.RodPhone);
c++; excel.cell[ri, c].value2 = r.HasForma ? "да" : "нет";
c++; excel.cell[ri, c].value2 = r.RazmerFormi;
c++; excel.cell[ri, c].value2 = r.BirthDat?.ToString("dd.MM.yyyy");
c++; excel.cell[ri, c].value2 = r.MestoRozd;
c++; excel.cell[ri, c].value2 = r.PaspSeriya;
c++; excel.cell[ri, c].value2 = r.PaspNomer;
c++; excel.cell[ri, c].value2 = r.PaspVidan;
c++; excel.cell[ri, c].value2 = r.VidanDat?.ToString("dd.MM.yyyy");
c++; excel.cell[ri, c].value2 = r.PaspAdres;
c++; excel.cell[ri, c].value2 = r.FactAdres;
c++; excel.cell[ri, c].value2 = r.VremRegDat?.ToString("dd.MM.yyyy");
c++; excel.cell[ri, c].value2 = Helper.FormatSnils(r.Snils);
c++; excel.cell[ri, c].value2 = r.Inn?.ToString();
/* c++; excel.cell[ri, c].value2 = r.PsihDat;
* c++; excel.cell[ri, c].value2 = r.IsPsih;
* c++; excel.cell[ri, c].value2 = r.IsPsihZabral;
* c++; excel.cell[ri, c].value2 = r.SanKnizkaDat;
* c++; excel.cell[ri, c].value2 = r.IsSanKnizka;
* c++; excel.cell[ri, c].value2 = r.MedKommDat;
* c++; excel.cell[ri, c].value2 = r.IsMedKomm;
* c++; excel.cell[ri, c].value2 = r.PraktikaDat;
* c++; excel.cell[ri, c].value2 = r.IsPraktika;
* c++; excel.cell[ri, c].value2 = r.ExamenDat;
* c++; excel.cell[ri, c].value2 = r.IsExamen; */
c++; excel.cell[ri, c].value2 = r.UchebCentr;
c++; excel.cell[ri, c].value2 = r.UchebGruppa; // c++; excel.cell[ri, c].value2 = r.UchebStartDat;
c++; excel.cell[ri, c].value2 = r.UchebEndDat?.ToString("dd.MM.yyyy");
c++; excel.cell[ri, c].value2 = r.VihodDat?.ToString("dd.MM.yyyy");
c++; excel.cell[ri, c].value2 = r.Gorod;
c++; excel.cell[ri, c].value2 = r.IsVibil ? "да" : "нет";
c++; excel.cell[ri, c].value2 = r.VibilPrichina;
c++; excel.cell[ri, c].value2 = r.AllScans ? "да" : "нет";
if (r.Messages == null)
{
errors.Add(r.Fio);
}
c++; excel.cell[ri, c].value2 = r.Messages?.Replace(Environment.NewLine, "; ");
c++; excel.cell[ri, c].value2 = r.Zametki;
progressChanged(share);
}
excel.setAllBorders(excel.get_Range("A1", "AL" + ri));
excel.myExcel.Visible = true;
// excel.Finish();
if (errors.Any())
{
throw new Exception("Выявлены ошибки примечаний для:" + Environment.NewLine + string.Join(Environment.NewLine + " ", errors)
+ Environment.NewLine + Environment.NewLine + "Возможно был сбой при сохранении карточки. Проверьте карточку и сохраните");
}
})
);
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}