public static void ClearColorMakers(IBackgroundContext context, LpuExecParams executorParams) { context.ProgressSeparator(); context.ReportProgress(@"УДАЛЕНИЕ МАРКЕРОВ НЕДЕЙСТВИТЕЛЬНЫХ ПОЛИСОВ"); context.ProgressSeparator(); using (var dataContext = new VistaMedDataContext(context)) { var invMarkerType = getInvMarkerType(dataContext); if(invMarkerType != null) { context.ReportProgress(@""); try { dataContext.VistaMed.ExecuteCommand(@"DELETE FROM Client_StatusObservation WHERE statusObservationType_id IS NULL"); dataContext.VistaMed.ExecuteCommand(@"DELETE FROM Client_StatusObservation WHERE statusObservationType_id = {0}", invMarkerType.Id); dataContext.VistaMed.SubmitChanges(); context.ReportProgress(@"Удаление маркеров проведено"); } catch (Exception exception) { context.ReportError(@"{0}", exception.Message); } } else { //context.ReportError(@"Тип маркера недействительного полиса не найден в БД ВистаМед"); } } context.ProgressSeparator('-'); }
public static void SetBillNumbers(IBackgroundContext context, BillsEnumeratorParams args) { if (!(args.UseBillDate || args.UseCreateDate)) return; context.ProgressSeparator(); context.ReportProgress(@"ПРОСТАНОВКА НОМЕРОВ СЧЕТОВ"); context.ReportProgress(""); using (var dataContext = new VistaMedDataContext(context)) { if (context.CancellationPending) return; var sqlText = string.Format(@"SELECT a.id AS Id, a.number AS Number, a.amount AS Amount, a.sum AS Summa, a.settleDate AS BillDate, a.date AS CreateDate, o.miacCode AS SmoCode, o.shortName AS SmoName, IF(DATE_ADD(a.settleDate, INTERVAL 1 MONTH) < a.date, 1, 0) AS IsDop, MAX(et.code) AS MaxEtCode FROM Account a JOIN Contract c ON a.contract_id = c.id JOIN rbFinance f ON c.finance_id = f.id JOIN Organisation o ON c.payer_id = o.id JOIN Account_Item ai ON a.id = ai.master_id JOIN Event e ON ai.event_id = e.id JOIN EventType et ON e.eventType_id = et.id WHERE a.deleted = 0 AND ai.deleted = 0 AND e.deleted = 0 AND f.name LIKE 'омс' AND ('{0}' = '0' OR a.settleDate = '{1}') AND ('{2}' = '0' OR a.date = '{3}') AND a.number NOT LIKE '%перевыпол%' GROUP BY 1", args.UseBillDate ? 1 : 0, args.BillDate.SqlStr(), args.UseCreateDate ? 1 : 0, args.CreateDate.SqlStr()); var billData = dataContext.VistaMed.SelectSqlData<AccountData>(sqlText). OrderByDescending(x => x.IsDop).ThenBy(x => x.SmoIndex). ThenBy(x => x.TypeIndex).ThenBy(x => x.Id).ToList(); assignBillNumbers(context, dataContext, billData); } context.ProgressSeparator('-'); }
/// <summary> /// Заполнение формы контроля /// </summary> /// <param name="context"></param> /// <param name="args"></param> /// <param name="methodCaption"></param> /// <param name="fillAction"></param> private static void fillAccountCheckPlan(IBackgroundContext context, MonthParams args, string methodCaption, Action<PageType, Worksheet, MonthParams> fillAction) { context.ProgressSeparator(); context.ReportProgress(methodCaption); context.ProgressSeparator(); string templateName = Path.Combine(Path.GetFullPath(@".\"), string.Format(@"AccountCheckPlan{0}.xls", args.DateEnd.Year)); if (File.Exists(templateName)) { var excel = new Application(); excel.Visible = true; Workbook newDoc = excel.Workbooks.Add(templateName); newDoc.Names.Item(@"Период").RefersToRange.Value2 = Utils.GetPeriodName(args.DateBegin, args.DateEnd); newDoc.Names.Item(@"КоличествоМесяцев").RefersToRange.Value2 = args.DateEnd.Month; excel.ScreenUpdating = false; using (var dataContext = new VistaMedDataContext(context)) { dataContext.VistaMed.Connection.Open(); try { List<PageType> pageTypes = (new PageType[] { new Polyclinic(context, dataContext), new Stacionar(context, dataContext), new Ambulance(context, dataContext) }).ToList(); foreach (Worksheet ws in newDoc.Worksheets.Cast<Worksheet>()) { pageTypes.ForEach(x => fillAction(x, ws, args)); } } finally { dataContext.VistaMed.Connection.Close(); } } excel.ScreenUpdating = true; } else context.ReportError(@"Не найден файл {0}", templateName); context.ProgressSeparator('-'); }
public static void AnalyzeErrors(IBackgroundContext context, LpuExecParams executorParams) { if (context.CancellationPending) return; context.ProgressSeparator(); context.ReportProgress(@"АНАЛИЗ ОШИБОК"); context.ReportProgress(""); using (var dataContext = new ErrorsAnalyzerDataContext()) { var dir = Settings.Default.DataDir; dataContext.Load(context, dir, dir, dir); if (context.CancellationPending) return; foreach (var errorDataSet in dataContext.Errors.TakeWhile(x => !context.CancellationPending)) { analyzeErrorsDataSet(context, dataContext, errorDataSet); } } context.ProgressSeparator('-'); }
public static void CheckPersonal(IBackgroundContext context, DateParams checkPersonalParams) { if (context.CancellationPending) return; var onDate = checkPersonalParams.DateEnd.Date; context.ProgressSeparator(); context.ReportProgress(@"ПРОВЕРКА СПРАВОЧНИКА СОТРУДНИКОВ ВистаМед на {0}", onDate.ToShortDateString()); context.ReportProgress(""); using (var dataContext = new PersonalCheckDataContext(context)) { var dir = Properties.Settings.Default.DataDir; dataContext.Load(context, dir); var vistaPersonal = (from p in dataContext.VistaMed.People where p.RetireDate == null && p.RbSpeciality != null && p.FederalCode != "000-000-000" select p).ToList(); var notFoundPers = (from p in vistaPersonal where !dataContext.DoctorList.Any(x => x.SNILS == p.FederalCode) select p); notFoundPers.ToList().ForEach(x => context.ReportError(@"сотрудник {0} не найден в списке ФРМР", x.ToLongString())); var nonCertPers = (from p in vistaPersonal from d in dataContext.DoctorList where d.SNILS == p.FederalCode && !d.SpecCodes.Contains(p.RbSpeciality.FederalCode) select new {p, d}); nonCertPers.ToList().ForEach(x => context.ReportError(@"сотрудник {0} не сертиф.по спец.""{1}"" (но имеет по ФРМР серт. {2})", x.p.ToLongString(), x.p.RbSpeciality.FederalCode + " " + x.p.RbSpeciality.Name, x.d.CertNames)); var expiredCert = (from p in vistaPersonal from d in dataContext.DoctorList from c in d.LastCertificates() where d.SNILS == p.FederalCode && p.RbSpeciality.FederalCode == c.SPEC_CODE && (onDate < c.CERT_DATE || onDate > c.CERT_DATE.AddYears(5)) select new { p, c }); expiredCert.ToList().ForEach(x => context.ReportError(@"сотрудник {0} имеет просроченный сертификат ""{1}""", x.p.ToLongString(), x.c.ToString())); } context.ProgressSeparator('-'); }
public static void ValidateBills(IBackgroundContext context, LpuExecParams executorParams) { if (context.CancellationPending) return; context.ProgressSeparator(); context.ReportProgress(@"ПРОВЕРКА СЧЕТОВ"); context.ReportProgress(""); using (var dataContext = new BillsValidatorDataContext()) { var dir = Properties.Settings.Default.DataDir; dataContext.Load(context, dir, dir, dir); if (context.CancellationPending) return; foreach (var bill in dataContext.Bills.TakeWhile(x => !context.CancellationPending)) { validateBill(context, dataContext, bill); } } context.ProgressSeparator('-'); }
public static void BuildSvedDdReport(IBackgroundContext context, MonthParams args) { var dateBegin = args.DateBegin; var dateEnd = args.DateEnd; var miacCode = args.LpuCode; using (var dataConext = new VistaMedDataContext(context)) { var ddrDataContext = new DdrDataContext(); ddrDataContext.dataConext = dataConext; ddrDataContext.context = context; ddrDataContext.dateEnd = dateEnd; ddrDataContext.dateBegin = dateBegin; ddrDataContext.LpuCode = miacCode; var templateFileName = @"SvodDdr.xls"; var templateName = Utils.GetReportFileName(templateFileName); if (File.Exists(templateName)) { var excel = new Application(); excel.Visible = true; ddrDataContext.planDd = PlanDd.LoadPlanDd(excel, context); var newDoc = excel.Workbooks.Add(templateName); ddrDataContext.newDoc = newDoc; excel.ScreenUpdating = false; var lpu = dataConext.VistaMed.Organisations.FirstOrDefault(x => x.MiacCode == miacCode); if (lpu != null) { ddrDataContext.LpuId = lpu.Id; newDoc.Names.Item("ЛПУ").RefersToRange.Value2 = lpu.FullName; newDoc.Names.Item("Адрес").RefersToRange.Value2 = lpu.Address; } SvodDdr1000.Fill1000(ddrDataContext); SvodDdr2000.Fill2000(ddrDataContext); SvodDdr3000.Fill3000(ddrDataContext); SvodDdr4000.Fill4000(ddrDataContext); SvodDdr5000.Fill5000(ddrDataContext); SvodDdr6000.Fill6000(ddrDataContext); SvodDdr7000.Fill7000(ddrDataContext); excel.ScreenUpdating = true; } } context.ReportProgress(@"Формирование отчета закончено"); context.ProgressSeparator('-'); }
public static void UpdateClientAttaches(IBackgroundContext context, LpuExecParams args) { if (context.CancellationPending) return; context.ProgressSeparator(); context.ReportProgress(@"ОБНОВЛЕНИЕ СВЕДЕНИЙ О ПРИКРЕПЛЕНИИ ЗАСТРАХОВАННЫХ ЛИЦ К ЛПУ"); context.ReportProgress(""); using (var dataContext = new AttacherDataContext(context)) { var dupMiac = (from org in dataContext.VistaMed.Organisations where !string.IsNullOrEmpty(org.MiacCode) group org by org.MiacCode into g select g).Where(x => x.Count() > 1); if (dupMiac.Any()) { foreach (var dupGroup in dupMiac) { context.ReportError(@"В справочнике организаций найдено {0} записей с кодом МИАЦ = {1}", dupGroup.Count(), dupGroup.Key); } context.ReportProgress(@"Пожалуйста устраните указанные ошибки"); } else { dataContext.Load(context, Properties.Settings.Default.DataDir); // словарь организаций по коду МИАЦ var lpuDict = (from lpu in dataContext.VistaMed.Organisations where !lpu.Deleted && !string.IsNullOrEmpty(lpu.MiacCode) select lpu).ToDictionary(x => x.MiacCode); var idOrgDict = (from lpu in dataContext.VistaMed.Organisations where !lpu.Deleted select lpu).ToDictionary(x => x.Id); var errorMiacCodes = new HashSet<string>(); var updateStatements = new List<string>(); var insertStatements = new List<string>(); foreach (var smoData in dataContext.SmoPacients) { context.ReportProgress(@"Обрабатываем СМО {0} - {1} застрахованных лиц ...", smoData.Key, smoData.Value.Count); int smoClientIndex = 0; foreach (var smoPacient in smoData.Value.Where(x => !string.IsNullOrEmpty(x.MCOD))) { if (context.CancellationPending) break; if (lpuDict.ContainsKey(smoPacient.MCOD)) { // выбираем ЛПУ по колу МИАЦ var lpu = lpuDict[smoPacient.MCOD]; // по ДР var clients = dataContext.VistaMed.Clients.Where(x => x.BirthDate == smoPacient.DR).ToList(); // клиенты по фио clients = clients.Where( x => x.LastName.ToUpper() == smoPacient.FAM.ToUpper() && x.FirstName.ToUpper() == smoPacient.IM.ToUpper() && x.PatrName.ToUpper() == smoPacient.OT.ToUpper()) .ToList(); // по ключу clients = clients.Where(x => x.GetKey(dataContext.VistaMed) == smoPacient.Key).ToList(); foreach (var client in clients) { var attachInfo = new AttachInfo(smoPacient, dataContext.VistaMed, lpu); // крайняя по дате запись о прикреплении var attachRec = client.ClientAttaches.OrderByDescending(x => x.BegDate).FirstOrDefault(); // создаем новую запись о прикреплении когда var shouldCreateNew = attachRec == null /* битый ID организации ЛПУ */ || !idOrgDict.ContainsKey(attachRec.LPUId) /* не то ЛПУ и код миац не пустой (при пустом коде миац корректируем последнюю запись о привязке) */ || ((attachInfo.Lpu.Id != attachRec.LPUId) && !string.IsNullOrEmpty(idOrgDict[attachRec.LPUId].MiacCode)); if (shouldCreateNew) attachInfo.CreateAttachRecord(client, insertStatements); else attachInfo.UpdateAttachRecord(attachRec, updateStatements); } } else { if (!errorMiacCodes.Contains(smoPacient.MCOD)) { context.ReportError( @"Код МИАЦ {0} для ЛПУ не найден в справочнике организаций Виста-Мед. Привязка к этому ЛПУ не проводится", smoPacient.MCOD); errorMiacCodes.Add(smoPacient.MCOD); } } smoClientIndex++; if(smoClientIndex%50000 == 0) context.ReportProgress(@"Обработано {0} записей", smoClientIndex); } if (context.CancellationPending) break; else { context.ReportProgress("Записываем изменения в БД ..."); execInsertStatements(insertStatements, dataContext, context); execUpdateStatements(updateStatements, dataContext, context); insertStatements.Clear(); updateStatements.Clear(); } context.ProgressSeparator(); } } } context.ProgressSeparator('-'); }
public void LoadErrors(IBackgroundContext context, string errorsDir) { if (context.CancellationPending) return; context.ProgressSeparator(); context.ReportProgress(@"Загрузка файлов с ошибками СМО ..."); if (string.IsNullOrEmpty(errorsDir)) errorsDir = @".\"; errorsDir = Path.GetFullPath(errorsDir); if (Directory.Exists(errorsDir)) { foreach (var xmlFileName in Directory.GetFiles(errorsDir, "FLK_*.xml").TakeWhile(x => !context.CancellationPending)) { if (File.Exists(xmlFileName) && File.Exists(xmlFileName)) { var errorDataSet = new SmoErrorDataSet(); errorDataSet.ReadXml(xmlFileName); Errors.Add(errorDataSet); } } } context.ReportProgress(@"Загрузка файлов с ошибками СМО закончена"); }
public static void BuildSvedReport(IBackgroundContext context, MonthAccountParams args) { var dateBegin = args.DateBegin; var dateEnd = args.DateEnd; var miacCode = args.LpuCode; using (var dataConext = new VistaMedDataContext(context)) { var smpBills = (from b in dataConext.VistaMed.Accounts where b.Date >= args.DateBegin.AddDays(15) && b.Date < args.NextMonthBegin.AddDays(15) && b.Contract != null && b.Contract.RbFinance != null && b.Contract.RbFinance.Name.ToLower() == "омс" && (from a in b.AccountItems from s in dataConext.VistaMed.RbServices where a.ServiceId == s.Id && s.Code.StartsWith("062") select a).Any() select b).ToList(); smpBills = smpBills.Where(x => x.AccountType == AccountType.ProfOsm).ToList(); smpBills = smpBills.Where(x => args.IsValidAccountNumber(x.Number)).ToList(); if (smpBills.Count == 0) context.ReportError(@"Счета ДД за период с {0} по {1} не найдены", dateBegin, dateEnd); else { var templateName = Utils.GetReportFileName(@"SvodSpravProf.xls"); if (File.Exists(templateName)) { var excel = new Application(); excel.Visible = true; foreach (var smpBill in smpBills) { var newDoc = excel.Workbooks.Add(templateName); excel.ScreenUpdating = false; var ws = ((Worksheet)newDoc.Worksheets[1]); newDoc.Names.Item(@"ОтчетныйПериод").RefersToRange.Value2 = Utils.GetPeriodName(smpBill.SettleDate.BeginOfMonth(), smpBill.SettleDate); var lpu = dataConext.VistaMed.Organisations.FirstOrDefault(x => x.MiacCode == miacCode); if (lpu != null) { newDoc.Names.Item("ЛПУ").RefersToRange.Value2 = lpu.FullName; newDoc.Names.Item("ФИОРуководителя").RefersToRange.Value2 = lpu.Chief; newDoc.Names.Item("ФИОГлавбуха").RefersToRange.Value2 = lpu.Accountant; } var smo = (from payer in dataConext.VistaMed.Organisations where payer.Id == smpBill.PayerId select payer).FirstOrDefault(); if (smo != null) { newDoc.Names.Item("СМО").RefersToRange.Value2 = smo.FullName; } ReportData colData; var eventList = (from a in smpBill.AccountItems where !a.Deleted && a.Event != null && a.Event.RbResult != null && a.Action != null && a.Action.ActionType != null group a by a.Event into e select e).ToList(); colData = new ReportData(); colData.Prof = eventList.Count; fillColumn(ws, colData, 2); colData = new ReportData(); colData.Prof = (from e in eventList from a in e select a).Sum(x => x.Sum); fillColumn(ws, colData, 3); excel.ScreenUpdating = true; ws.Activate(); } } } } context.ProgressSeparator('-'); }
/// <summary> /// Провести автоматическую коррекцию реестров (устранить пере(недо)выполнение) по результатам проверки /// </summary> /// <param name="context"></param> /// <param name="args"></param> public static void AutoCorrectAccounts(IBackgroundContext context, AccountCorrectParams args) { context.ProgressSeparator(); context.ReportProgress(@"АВТОМАТИЧЕСКАЯ КОРРЕКТИРОВКА РЕЕСТРОВ В СООТВЕТСТВИИ С ПЛАНАМИ"); context.ProgressSeparator(); // книга планов var excelFileName = args.AccountCheckPlanFileName; if (File.Exists(excelFileName)) { var excel = new Application(); excel.Visible = true; Workbook newDoc; try { context.ReportProgress(@"Открываем файл {0} ...", excelFileName); newDoc = excel.Workbooks.Open(excelFileName); } catch (Exception exception) { newDoc = null; context.ReportError(@"Ошибка открытия файла {0} : {1}", excelFileName, exception.Message); } if (newDoc != null) { // ищем имя КоличествоМесяцев var kolMonthExists = false; for (int i = 1; i <= newDoc.Names.Count; i++) { if (newDoc.Names.Item(i).Name == @"КоличествоМесяцев") { kolMonthExists = true; break; } } if (kolMonthExists) { // сравниваем КоличествоМесяцев из книги с отчетным периодом int kolMonth = (int) newDoc.Names.Item(@"КоличествоМесяцев").RefersToRange.Value2; if (args.DateEnd.Month == kolMonth) { excel.ScreenUpdating = false; using (var dataContext = new VistaMedDataContext(context)) { dataContext.VistaMed.Connection.Open(); // новая транзакция для обеспечения атомарности всех изменений БД для текущего метода dataContext.VistaMed.ActiveTransaction = dataContext.VistaMed.Connection .BeginTransaction(IsolationLevel.ReadCommitted); try { // массив с объектами - типами листов книги List<PageType> pageTypes = (new PageType[] { new Polyclinic(context, dataContext), new Stacionar(context, dataContext), new Ambulance(context, dataContext) }).ToList(); // сканируем все листы foreach (Worksheet ws in newDoc.Worksheets.Cast<Worksheet>()) { // попытка обработать текущий лист для всех типов листов // фактическая работа делается в этих объектах pageTypes.ForEach(x => x.AutoCorrectAccounts(ws, args)); } try { dataContext.VistaMed.ActiveTransaction.Commit(); context.ReportProgress(@"Изменения записаны в БД"); } catch (Exception exception) { dataContext.VistaMed.ActiveTransaction.Rollback(); context.ReportProgress(@"Ошибка записи изменений в БД: {0}", exception.Message); } } finally { dataContext.VistaMed.ActiveTransaction.Dispose(); dataContext.VistaMed.ActiveTransaction = null; dataContext.VistaMed.Connection.Close(); } } excel.ScreenUpdating = true; } else context.ReportError(@"КоличествоМесяцев в файле не соответствует выбранному Вами периоду"); } else context.ReportError(@"Имя ячейки ""КоличествоМесяцев"" не найдено в файле {0}", excelFileName); newDoc.Close(false); } } else context.ReportError(@"Файл {0} не найден", excelFileName); context.ProgressSeparator('-'); }
public static void BuildSvedReport(IBackgroundContext context, MonthAccountParams args) { var dateBegin = args.DateBegin; var dateEnd = args.DateEnd; var miacCode = args.LpuCode; using (var dataConext = new VistaMedDataContext(context)) { var smpBills = (from b in dataConext.VistaMed.Accounts where b.Date >= args.DateBegin.AddDays(15) && b.Date < args.NextMonthBegin.AddDays(15) && b.Contract != null && b.Contract.RbFinance != null && b.Contract.RbFinance.Name.ToLower() == "омс" && (from a in b.AccountItems from s in dataConext.VistaMed.RbServices where a.ServiceId == s.Id && (s.Code.StartsWith("161") || s.Code.StartsWith("162")) select a).Any() select b).ToList(); smpBills = smpBills.Where(x => x.AccountType == AccountType.Osm18 && args.IsValidAccountNumber(x.Number) && !x.Number.StartsWith(PlanExecution.ReserveAccountName)).ToList(); if (smpBills.Count == 0) context.ReportError(@"Счета ДД за период с {0} по {1} не найдены", dateBegin, dateEnd); else { var templateName = Utils.GetReportFileName(@"SvedOsm18.xls"); if (File.Exists(templateName)) { var excel = new Application(); excel.Visible = true; var planDataList = loadPlan(excel); var newDoc = excel.Workbooks.Add(templateName); excel.ScreenUpdating = false; var ws = ((Worksheet)newDoc.Worksheets[1]); newDoc.Names.Item(@"ДатаНачала").RefersToRange.Value2 = dateBegin; newDoc.Names.Item(@"ДатаКонца").RefersToRange.Value2 = dateEnd; newDoc.Names.Item(@"ДатаОтчета").RefersToRange.Value2 = DateTime.Today; newDoc.Names.Item(@"ОтчетныйПериод").RefersToRange.Value2 = Utils.GetPeriodName(dateBegin, dateEnd); var lpu = dataConext.VistaMed.Organisations.FirstOrDefault(x => x.MiacCode == miacCode); if (lpu != null) { newDoc.Names.Item("ЛПУ").RefersToRange.Value2 = lpu.FullName; newDoc.Names.Item("ФИОРуководителя").RefersToRange.Value2 = lpu.Chief; newDoc.Names.Item("ФИОГлавбуха").RefersToRange.Value2 = lpu.Accountant; } ReportData colData; colData = new ReportData(); colData.Prof = planDataList.Sum(x => x.Prof); colData.Pred = null; colData.Period = planDataList.Sum(x => x.Period); ; fillColumn(ws, colData, 3); colData = new ReportData(); colData.Prof = planDataList.Where(y => y.Month <= dateEnd.Month).Sum(x => x.Prof); colData.Pred = null; colData.Period = planDataList.Where(y => y.Month <= dateEnd.Month).Sum(x => x.Period); ; fillColumn(ws, colData, 4); var eventList = (from b in smpBills from a in b.AccountItems where !a.Deleted && a.Event != null && a.Event.RbResult != null && a.Action != null && a.Action.ActionType != null group a by a.Event into e select e).ToList(); var profActionCodes = new[] { "162043", "162044", "162045", "162046", "162002", "162003", "162005", "162006", "162007", "162008", "162009", "162010" }; var predActionCodes = new[] { "162040", "162041", "162042" }; var perActionsCodes = new[] { "162060", "162061", "162062"}; var txtFileName = Utils.GetOptionsFileName(@"SvedOsm18Services.txt"); if (File.Exists(txtFileName)) { var allLines = File.ReadAllLines(txtFileName, Encoding.GetEncoding(1251)); foreach (var currLine in allLines) { if (currLine.StartsWith(@"Профилактика")) { var lines2 = currLine.Split('='); profActionCodes = lines2[1].Split(','); } if (currLine.StartsWith(@"Предварительные")) { var lines2 = currLine.Split('='); predActionCodes = lines2[1].Split(','); } if (currLine.StartsWith(@"Периодические")) { var lines2 = currLine.Split('='); perActionsCodes = lines2[1].Split(','); } } } var profList = (from e in eventList where e.Any(a => profActionCodes.Any(x => a.Action.ActionType.Code == x)) select e).ToList(); var predList = (from e in eventList where e.Any(a => predActionCodes.Any(x => a.Action.ActionType.Code == x)) select e).ToList(); var periodList = eventList.Except(profList).Except(predList).ToList(); colData = new ReportData(); colData.Prof = (from e in profList from a in e select a).Sum(x => x.Sum) / 1000; colData.Pred = (from e in predList from a in e select a).Sum(x => x.Sum) / 1000; colData.Period = (from e in periodList from a in e select a).Sum(x => x.Sum) / 1000; fillColumn(ws, colData, 5); colData = new ReportData(); colData.Prof = profList.Count; colData.Pred = predList.Count; colData.Period = periodList.Count; fillColumn(ws, colData, 6); string grName = ""; int grColIndex = 0; grColIndex = 15; grName = @" I группа"; grZd(profList, grName, predList, ws, grColIndex++); grName = @" II группа"; grZd(profList, grName, predList, ws, grColIndex++); grName = @" III группа"; grZd(profList, grName, predList, ws, grColIndex++); grName = @" IV группа"; grZd(profList, grName, predList, ws, grColIndex++); grName = @" V группа"; grZd(profList, grName, predList, ws, grColIndex++); excel.ScreenUpdating = true; ws.Activate(); } } } context.ProgressSeparator('-'); }
public static void BuildSvedReport(IBackgroundContext context, MonthAccountParams args) { var dateBegin = args.DateBegin; var dateEnd = args.DateEnd; var miacCode = args.LpuCode; using (var dataConext = new VistaMedDataContext(context)) { var smpBills = (from b in dataConext.VistaMed.Accounts where b.Date >= args.DateBegin.AddDays(15) && b.Date < args.NextMonthBegin.AddDays(15) && b.Contract != null && b.Contract.RbFinance != null && b.Contract.RbFinance.Name.ToLower() == "омс" && (from a in b.AccountItems from s in dataConext.VistaMed.RbServices where a.ServiceId == s.Id && s.Code.StartsWith("162") select a).Any() select b).ToList(); smpBills = smpBills.Where(x => x.AccountType == AccountType.Osm18).ToList(); smpBills = smpBills.Where(x => args.IsValidAccountNumber(x.Number)).ToList(); if (smpBills.Count == 0) context.ReportError(@"Счета ДД за период с {0} по {1} не найдены", dateBegin, dateEnd); else { var templateName = Utils.GetReportFileName(@"SvodSpravOsm18.xls"); if (File.Exists(templateName)) { var excel = new Application(); excel.Visible = true; foreach (var smpBill in smpBills) { var newDoc = excel.Workbooks.Add(templateName); excel.ScreenUpdating = false; var ws = ((Worksheet)newDoc.Worksheets[1]); newDoc.Names.Item(@"ОтчетныйПериод").RefersToRange.Value2 = Utils.GetPeriodName(smpBill.SettleDate.BeginOfMonth(), smpBill.SettleDate); var lpu = dataConext.VistaMed.Organisations.FirstOrDefault(x => x.MiacCode == miacCode); if (lpu != null) { newDoc.Names.Item("ЛПУ").RefersToRange.Value2 = lpu.FullName; newDoc.Names.Item("ФИОРуководителя").RefersToRange.Value2 = lpu.Chief; newDoc.Names.Item("ФИОГлавбуха").RefersToRange.Value2 = lpu.Accountant; } var smo = (from payer in dataConext.VistaMed.Organisations where payer.Id == smpBill.PayerId select payer).FirstOrDefault(); if (smo != null) { newDoc.Names.Item("СМО").RefersToRange.Value2 = smo.FullName; } ReportData colData; var eventList = (from a in smpBill.AccountItems where !a.Deleted && a.Event != null && a.Event.RbResult != null && a.Action != null && a.Action.ActionType != null group a by a.Event into e select e).ToList(); var profActionCodes = new[] { "162043", "162044", "162045", "162046", "162002", "162003", "162005", "162006", "162007", "162008", "162009", "162010" }; var predActionCodes = new[] { "162040", "162041", "162042" }; var txtFileName = Utils.GetOptionsFileName(@"SvedOsm18Services.txt"); if (File.Exists(txtFileName)) { var allLines = File.ReadAllLines(txtFileName, Encoding.GetEncoding(1251)); foreach (var currLine in allLines) { if (currLine.StartsWith(@"Профилактика")) { var lines2 = currLine.Split('='); profActionCodes = lines2[1].Split(','); } if (currLine.StartsWith(@"Предварительные")) { var lines2 = currLine.Split('='); predActionCodes = lines2[1].Split(','); } } } var profList = (from e in eventList where e.Any(a => profActionCodes.Any(x => a.Action.ActionType.Code == x)) select e).ToList(); var predList = (from e in eventList where e.Any(a => predActionCodes.Any(x => a.Action.ActionType.Code == x)) select e).ToList(); var periodList = eventList.Except(profList).Except(predList).ToList(); colData = new ReportData(); colData.Prof = profList.Count; colData.Pred = predList.Count; colData.Period = periodList.Count; fillColumn(ws, colData, 2); colData = new ReportData(); colData.Prof = (from e in profList from a in e select a).Sum(x => x.Sum); colData.Pred = (from e in predList from a in e select a).Sum(x => x.Sum); colData.Period = (from e in periodList from a in e select a).Sum(x => x.Sum); fillColumn(ws, colData, 3); excel.ScreenUpdating = true; ws.Activate(); } } } } context.ProgressSeparator('-'); }
public static void SetColorMakers(IBackgroundContext context, LpuExecParams executorParams) { ClearColorMakers(context, null); context.ProgressSeparator(); context.ReportProgress(@"УСТАНОВКА МАРКЕРОВ НЕДЕЙСТВИТЕЛЬНЫХ ПОЛИСОВ"); context.ProgressSeparator(); using (var dataContext = new SetColorMakersDataContext(context)) { dataContext.Load(context); var invMarkerType = getInvMarkerType(dataContext); if (invMarkerType == null) { invMarkerType = new RbStatusObservationClientType(); invMarkerType.Code = InvalidPolicyMarkerCode; invMarkerType.Color = InvalidPolicyMarkerColor; invMarkerType.Name = InvalidPolicyMarkerName; dataContext.VistaMed.SubmitChanges(); } invMarkerType = getInvMarkerType(dataContext); if (invMarkerType != null) { context.ReportProgress(@""); foreach (var smoPacient in dataContext.SmoPacients.TakeWhile(x => !context.CancellationPending)) { var smoCode = smoPacient.Key; var smoPolicyNumbers = new HashSet<string>(); // smoPacient.Value.Select(x => x.SerialNum).Distinct().ToList().ForEach(z => smoPolicyNumbers.Add(z)); // 14/01/14 поиск по более полному ключу smoPacient.Value.Select(x => x.Key).Distinct().ToList().ForEach(z => smoPolicyNumbers.Add(z)); var smo = dataContext.VistaMed.Organisations.FirstOrDefault(x => x.MiacCode == smoCode && x.IsInsurer); if(smo != null) { var smoId = smo.Id; context.ReportProgress(@"СМО {0}", smo.FullName); try { var allPolicies = from p in dataContext.VistaMed.ClientPolicies where p.InsurerId == smoId && p.Id == dataContext.VistaMed.GetClientPolicyId(p.ClientId, 1) select new { p.Id, p.ClientId, p.Serial, p.Number, p.Client.LastName, p.Client.FirstName, p.Client.PatrName, p.Client.BirthDate }; context.ReportProgress(@"Проверяем полисы"); var invalidPolicies = new List<Tuple<int,int>>(); int i = 1; foreach (var policy in allPolicies) { if (context.CancellationPending) break; // 14/01/14 поиск по полному ключу var policyKey = (policy.Serial + "|" + policy.Number + "|" + policy.LastName + "|" + policy.FirstName + "|" + policy.PatrName + "|" + policy.BirthDate.ToShortDateString()).ToUpper(); if (!smoPolicyNumbers.Contains(policyKey)) invalidPolicies.Add(new Tuple<int, int>(policy.Id, policy.ClientId)); if(i % 50000 == 0) context.ReportProgress(); i++; } if(invalidPolicies.Count == 0) context.ReportProgress(@"Недействительные полисы не найдены"); else { context.ReportProgress(@"Обнаружено {0} недействительных полисов", invalidPolicies.Count); var invalidClients = invalidPolicies.Select(x => x.Item2).Distinct(); var batch = new List<int>(); context.ReportProgress(@"Простановка маркеров"); foreach (var invalidClient in invalidClients) { batch.Add(invalidClient); execBatch(context, dataContext, batch, invMarkerType.Id); } execBatch(context, dataContext, batch, invMarkerType.Id, true); } } catch (Exception exception) { context.ReportError(exception.Message); } context.ProgressSeparator('+'); } else context.ReportError(@"СМО с кодом МИАЦ {0} не найдена в БД ВистаМед", smoCode); } } else { context.ReportError(@"Тип маркера недействительного полиса не найден в БД ВистаМед"); } } context.ProgressSeparator('-'); }
private static void analyzeErrorsDataSet(IBackgroundContext context, ErrorsAnalyzerDataContext dataContext, SmoErrorDataSet errorDataSet) { context.ProgressSeparator(); context.ReportProgress("РЕВЬЮ {0}", errorDataSet.FileName); var bill = dataContext.Bills.FirstOrDefault( x => x.LFileName == errorDataSet.SourceFileName || x.HFileName == errorDataSet.SourceFileName); if (bill == null) { context.ReportProgress(@"Не найден файл счета {0}", errorDataSet.SourceFileName); return; } if (errorDataSet.PR.Rows.Count == 0) { context.ReportProgress(@"Ошибки не обнаружены", errorDataSet.FileName); return; } if (errorDataSet.SourceFileName.StartsWith("L")) { /* ошибки по списку персон */ foreach (var prRow in errorDataSet.PR) { context.ReportError(@"{0}", prRow); if(prRow.N_ZAP != "") { var pers = bill.Persons.PERS.FirstOrDefault(x => x.ID_PAC == prRow.N_ZAP); if(pers != null) context.ReportProgress(@" -> {0}", pers); } } } else { /* ошибки по услугам */ foreach (var prRow in errorDataSet.PR) { context.ReportError(@"{0}", prRow); if(prRow.OSHIB == "904") { var doc = dataContext.DoctorList.FirstOrDefault( x => x.SNILS == prRow.COMMENT.Substring(prRow.COMMENT.Length - 11)); if(doc != null) context.ReportProgress(@" Доктор {0}", doc.ToString()); } if (prRow.N_ZAP != "") { var zap = bill.Reestr.ZAP.FirstOrDefault(x => x.N_ZAP == prRow.N_ZAP); if (zap != null) { foreach (var sluchRow in zap.GetSLUCHRows()) { context.ReportProgress(@" -> {0}", sluchRow.ToString()); } } } } } }
public static void Build(IBackgroundContext context, DdSpecReportParams args) { using (var dataConext = new VistaMedDataContext(context)) { var sqlText = string.Format(@"SELECT et.code AS EventCode, aty.code AS ActionCode, et.name AS EventName, aty.name AS ActionName, COUNT(DISTINCT a.id) AS Kol FROM Event e JOIN Client c ON c.id = e.client_id AND c.deleted = 0 JOIN EventType et ON et.id = e.eventType_id AND et.deleted = 0 AND e.deleted = 0 JOIN Action a ON a.event_id = e.id AND a.deleted = 0 JOIN ActionType aty ON aty.id = a.actionType_id AND aty.deleted = 0 LEFT OUTER JOIN Person p ON p.id = e.execPerson_id AND p.deleted = 0 LEFT OUTER JOIN rbSpeciality s ON s.id = p.speciality_id WHERE e.execDate >= '{0}' AND e.execDate < '{1}' {2} {3} {4} {5} {6} {7} {8} {9} {10} GROUP BY 1,2 ORDER BY 1,2", (args.FromBeginOfYear ? args.DateBegin.BeginOfYear() : args.DateBegin).SqlStr(), args.DateEnd.AddDays(1).SqlStr(), dataConext.VistaMed.GetMaskSql("aty.code", args.ActionCodesSpec), dataConext.VistaMed.GetMaskSql("aty.name", args.ActionNamesSpec), dataConext.VistaMed.GetMaskSql("et.code", args.EventCodesSpec), dataConext.VistaMed.GetMaskSql("et.name", args.EventNamesSpec), dataConext.VistaMed.GetMaskSql("s.name", args.SpecNames), dataConext.VistaMed.GetMaskSql("aty.code", args.ExcludeActionCodes, true), dataConext.VistaMed.GetMaskSql("aty.name", args.ExcludeActionNames, true), (args.ExcludeZeroAmount ? " AND a.amount > 0": ""), (args.UseOnlyInAccount ? " AND EXISTS(SELECT ai.id FROM Account_Item ai WHERE ai.event_id = e.id AND ai.deleted=0)" : "") ); var sourceData = dataConext.VistaMed.SelectSqlData<SourceData>(sqlText); if (sourceData.Count == 0) context.ReportError(@"Даные за период с {0} по {1} не найдены", args.DateBegin, args.DateEnd); else { var templateName = Utils.GetReportFileName(@"DdSpecReport.xls"); if (File.Exists(templateName)) { var excel = new Application(); excel.Visible = true; var groups = from c in sourceData group c by c.EventName into g select g; foreach (var eventGroup in groups) { var newDoc = excel.Workbooks.Add(templateName); excel.ScreenUpdating = false; var ws = ((Worksheet) newDoc.Worksheets[1]); newDoc.Names.Item(@"Период").RefersToRange.Value2 = Utils.GetPeriodName(args.DateBegin, args.DateEnd); newDoc.Names.Item(@"ТипСобытия").RefersToRange.Value2 = eventGroup.Key; var lpu = dataConext.VistaMed.Organisations.FirstOrDefault( x => x.MiacCode == Properties.Settings.Default.LpuMiacCode); if (lpu != null) { newDoc.Names.Item("ЛПУ").RefersToRange.Value2 = lpu.FullName; } var rowIndex = 6; var npp = 1; foreach (var data in eventGroup) //.Where(x => !x.ActionName.Contains(@"врача-терапевт")) { var row = (Range) ws.Rows[rowIndex]; var colIndex = 1; ((Range) row.Cells[1, colIndex++]).Value2 = npp; ((Range) row.Cells[1, colIndex++]).Value2 = string.Concat(data.ActionCode, " ", data.ActionName); ((Range) row.Cells[1, colIndex++]).Value2 = ""; ((Range) row.Cells[1, colIndex++]).Value2 = data.Kol; rowIndex++; npp++; } excel.ScreenUpdating = true; ws.Activate(); } } } } context.ProgressSeparator('-'); }
public static void Prevalidate(IBackgroundContext context, PrevalidatorParams args) { if (context.CancellationPending) return; context.ProgressSeparator(); context.ReportProgress(@"ПРЕДВАРИТЕЛЬНАЯ ПРОВЕРКА ДАННЫХ Виста-Мед за период с {0} по {1}", args.DateBegin.ToShortDateString(), args.DateEnd.ToShortDateString()); context.ReportProgress(""); using (var dataContext = new VistaMedDataContext(context)) { context.ReportProgress(@"Выборка талонов ..."); var allEvents = (from e in dataContext.VistaMed.EventsNotInAccount where e.EventType != null && e.ExecDate != null && e.ExecDate.Value >= args.DateBegin && e.ExecDate.Value <= args.DateEnd && e.Client != null && (args.CreatePersonId ==0 || e.CreatePersonId == args.CreatePersonId) select e).ToList(); context.ReportProgress(@"Выборка мероприятий ..."); var allActions = (from action in dataContext.VistaMed.ActionsNotInAccount where action.EndDate >= args.DateBegin && action.EndDate <= args.DateEnd && action.Event != null && action.Event.EventType != null && !action.ActionType.Code.StartsWith("4-") && (args.CreatePersonId == 0 || action.CreatePersonId == args.CreatePersonId) select action).ToList(); verifyPolicies(context, dataContext, allEvents); if(args.ValidateAll || args.ValidateContracts) verifyContracts(context, args, dataContext); if (args.ValidateAll || args.ValidateFinance) verifyFinance(context, args, dataContext); if (args.ValidateAll || args.ValidateMkb) verifyMkb(context, args, dataContext); if (args.ValidateAll || args.ValidatePodrUsl) verifyPodrUsl(context, allEvents, allActions); if (args.ValidateAll || args.ValidateIspoln) verifyIspoln(context, allActions); if (args.ValidateAll || args.ValidateDdr) { verifyDispanDdr1(context, args, allEvents); // verifyDispanDdr2(context, args, allEvents); } if (args.ValidateAll || args.ValidateDdVet) { verifyDispanVet(context, args, allEvents); } if (args.ValidateAll || args.ValidateDds) verifyDispanDds(context, args, dataContext, allEvents); if (args.ValidateAll || args.ValidateDd14) verifyDispanDd14(context, args, allEvents); if (args.ValidateAll || args.ValidateProfOsm) verifyDispanProfOsm(context, args, allEvents); if (args.ValidateAll || args.ValidateViolet) verifyViolet(context, allEvents); if (args.ValidateAll || args.Validate1013) verify1013(context, allEvents); if (args.ValidateAll || args.ValidateStationar) verifyStationar(context, allEvents, dataContext, allActions); verifyKolUsl(context, allActions, dataContext); //if (args.ValidateAll || args.ValidatePodrUsl) // verifyNotInBill(context, allEvents, allActions, dataContext); } context.ProgressSeparator('-'); }
public static void LoadAddressAndSnils(IBackgroundContext context, LpuExecParams executorParams) { context.ProgressSeparator(); context.ReportProgress(@"ÇÀÃÐÓÇÊÀ ÀÄÐÅÑÎÂ È ÑÍÈËÑ"); context.ProgressSeparator(); using (var dataContext = new VistaMedDataContext(context)) { var dir = Properties.Settings.Default.DataDir; dataContext.LoadSmoPacients(context, dir); var npp = 0; foreach (var pacientList in dataContext.SmoPacients.Values) { if (context.CancellationPending) break; foreach (var smoPacient in pacientList.Where(x => x.MCOD == executorParams.LpuCode)) { if (context.CancellationPending) break; var policyList = dataContext.VistaMed.ClientPolicies.Where(x => x.Number == smoPacient.N_POL && x.Serial == smoPacient.S_POL && !x.Deleted); if (policyList.Count() == 1) { var client = policyList.First().Client; if (client.LastName.ToLower() == smoPacient.FAM.ToLower() && client.FirstName.ToLower() == smoPacient.IM.ToLower() && client.PatrName.ToLower() == smoPacient.OT.ToLower() && client.BirthDate == smoPacient.DR) { npp++; var address = string.Join(", ", (new[] { smoPacient.RNNAME, smoPacient.NPNAME, smoPacient.UL, string.IsNullOrEmpty(smoPacient.DOM) ? "" : "ä." + smoPacient.DOM, string.IsNullOrEmpty(smoPacient.KV) ? "" : "êâ." + smoPacient.KV }).Where(x => !string.IsNullOrEmpty(x))); var snils = smoPacient.SNILS; var phone = smoPacient.TEL; if (!string.IsNullOrEmpty(address)) { var ulCode = smoPacient.ULCODE; if (ulCode == "0") ulCode = ""; var adrReg = client.ClientAddresses.LastOrDefault(x => x.Type == 0); setAddress(adrReg, address, ulCode); var adrFact = client.ClientAddresses.LastOrDefault(x => x.Type == 1); setAddress(adrFact, address, ulCode); } if (!string.IsNullOrEmpty(snils)) client.SNILS = snils; if (npp % 10000 == 0) { dataContext.VistaMed.SubmitChanges(); context.ReportProgress(@"{0} çàïèñåé", npp); } } } } } } }
private static void validateBill(IBackgroundContext context, BillsValidatorDataContext dataContext, BillData bill) { if (context.CancellationPending) return; context.ProgressSeparator(); context.ReportProgress(@"Проверка файла {0}", bill.LFileName); if (!validateBillStructure(context, bill)) return; context.ReportProgress(@"Счет " + bill.Schet.ToString() + " " + bill.Zglv.ToString()); context.ReportProgress(""); validateFrmr(context, dataContext, bill); validatePolicyKind(context, dataContext, bill); checkForCrossPeriods(context, dataContext, bill); validateRequeredFields(context, dataContext, bill); checkForPersonDuplicates(context, dataContext, bill); checkForInvalidPolicies(context, dataContext, bill); if (bill.BillType == BillType.Common) { //checkForInvalidDs(context, dataContext, bill); //checkForExpertDs(context, dataContext, bill); } // checkForDoubleUsl(context, dataContext, bill); checkDateInOut(context, dataContext, bill); }
/// <summary> /// Заполняем сведения о выполнении заказ-задания /// </summary> /// <param name="context"></param> /// <param name="args"></param> public static void FillFomsReport(IBackgroundContext context, AccountCorrectParams args) { context.ProgressSeparator(); context.ReportProgress(@"ФОРМИРОВАНИЕ СВЕДЕНИЙ О ВЫПОЛНЕНИИ ЗАКАЗ-ЗАДАНИЯ"); context.ProgressSeparator(); // книга планов var excelFileName = args.AccountCheckPlanFileName; if (File.Exists(excelFileName)) { var excel = new Application(); excel.Visible = true; var reportData = getFomsReportData(context, args, excelFileName, excel); if (reportData.Count > 0) { excel.ScreenUpdating = false; using (var dataContext = new VistaMedDataContext(context)) { dataContext.VistaMed.Connection.Open(); var templateName = Path.Combine(Path.GetFullPath(@".\"), @"AccountFomsReport.xls"); Workbook newDoc = excel.Workbooks.Add(templateName); newDoc.Names.Item(@"Период").RefersToRange.Value2 = Utils.GetPeriodName(args.DateBegin, args.DateEnd); var lpu = dataContext.VistaMed.Organisations.FirstOrDefault(x => x.MiacCode == args.LpuCode); if (lpu != null) { newDoc.Names.Item("ЛПУ").RefersToRange.Value2 = lpu.FullName; newDoc.Names.Item("ФИОРуководителя").RefersToRange.Value2 = lpu.Chief; //newDoc.Names.Item("ФИОГлавбуха").RefersToRange.Value2 = lpu.Accountant; } try { var ws = (Worksheet)newDoc.Worksheets[1]; for (int rowIndex = 8; rowIndex < 19; rowIndex++) { var row = (Range)ws.Rows[rowIndex]; var rowName = row.GetCellValue<string>(1, 1); var smoCode = row.GetCellValue<string>(1, 8); if (!string.IsNullOrEmpty(smoCode)) { var rowDataType = (rowName.StartsWith("Плановое") ? FomsReportRowType.Plan : FomsReportRowType.Fact); var rowData = reportData.FirstOrDefault(x => x.SmoCode == smoCode && x.RowType == rowDataType); if (rowData != null) { var colIndex = 2; row.SetCellValue(1, colIndex++, rowData.StacFullCount + rowData.StacApuCount); row.SetCellValue(1, colIndex++, rowData.StacDayCount); row.SetCellValue(1, colIndex++, rowData.EmergencyCount); row.SetCellValue(1, colIndex++, rowData.TreatmentCount); row.SetCellValue(1, colIndex++, rowData.VisitCount + rowData.ProfilacticCount); row.SetCellValue(1, colIndex++, rowData.AmbulanceCount); } } } } finally { dataContext.VistaMed.Connection.Close(); } } excel.ScreenUpdating = true; } } else context.ReportError(@"Файл {0} не найден", excelFileName); context.ProgressSeparator('-'); }
public static void Build(IBackgroundContext context, DdActionListParams args) { using (var dataConext = new VistaMedDataContext(context)) { var dateField = args.SelectByEventDate ? "e.execDate" : "a.endDate"; var sqlInAccount = ""; if (args.InAccount ^ args.NotInAccount) { sqlInAccount = @"EXISTS(SELECT ai.id FROM Account_Item ai WHERE ai.deleted = 0 AND ai.action_id = a.id)"; if (args.NotInAccount) sqlInAccount = "NOT " + sqlInAccount; sqlInAccount = "AND " + sqlInAccount; } string gerderFilter = "1=1"; if (args.IsMan ^ args.IsWoman) { if (args.IsMan) gerderFilter = "c.Sex = 1"; if (args.IsWoman) gerderFilter = "c.Sex = 2"; } var sqlText = string.Format(@"SELECT CONCAT(c.lastName, ' ', c.firstName, ' ', c.patrName) AS ClientFio, c.birthDate AS ClientBirthDate, a.MKB AS Mkb, a.endDate AS ActionDate, aty.code AS ActionCode, aty.name AS ActionName, CONCAT(p.lastName, ' ', p.firstName, ' ', p.patrName) AS PersonFio, p.code AS PersonCode, et.code AS EventCode, et.name AS EventName, CONCAT_WS(' ', pc.lastName, pc.firstName, pc.patrName) AS OperFio FROM Event e JOIN Client c ON c.id = e.client_id JOIN EventType et ON et.id = e.eventType_id AND et.deleted = 0 AND e.deleted = 0 JOIN Action a ON a.event_id = e.id AND a.deleted = 0 JOIN ActionType aty ON aty.id = a.actionType_id AND aty.deleted = 0 LEFT OUTER JOIN Person p ON p.id = a.person_id AND p.deleted = 0 LEFT OUTER JOIN rbSpeciality spec ON spec.id = p.speciality_id LEFT OUTER JOIN Person pc ON pc.id = a.createPerson_id AND pc.deleted = 0 WHERE {7} >= '{0}' AND {7} < '{1}' {2} {3} {4} {5} {6} {8} {9} {10} {11} AND {12} {13} ORDER BY 1", args.DateBegin.SqlStr(), args.DateEnd.AddDays(1).SqlStr(), dataConext.VistaMed.GetMaskSql("aty.code", args.ActionCodes), dataConext.VistaMed.GetMaskSql("aty.name", args.ActionNames), dataConext.VistaMed.GetMaskSql("et.code", args.EventCodes), dataConext.VistaMed.GetMaskSql("et.name", args.EventNames), dataConext.VistaMed.GetMaskSql("CONCAT(p.lastName, ' ', p.firstName, ' ', p.patrName)", args.ExecPersonFio), dateField, sqlInAccount, dataConext.VistaMed.GetMaskSql("spec.federalCode", args.SpecCodes), dataConext.VistaMed.GetMaskSql("spec.name", args.SpecNames), dataConext.VistaMed.GetMaskSql("a.MKB", args.MkbCodes), gerderFilter, (string.IsNullOrEmpty(args.CreatorName) ? "" : "AND CONCAT_WS(' ', pc.lastName, pc.firstName, pc.patrName) LIKE '" + args.CreatorName + "%'") ); var sourceData = dataConext.VistaMed.SelectSqlData<SourceData>(sqlText); if (sourceData.Count == 0) context.ReportError(@"Даные за период с {0} по {1} не найдены", args.DateBegin, args.DateEnd); else { var templateName = Utils.GetReportFileName(@"DdActionList.xls"); if (File.Exists(templateName)) { var excel = new Application(); excel.Visible = true; var newDoc = excel.Workbooks.Add(templateName); excel.ScreenUpdating = false; var ws = ((Worksheet)newDoc.Worksheets[1]); newDoc.Names.Item(@"Период").RefersToRange.Value2 = Utils.GetPeriodName(args.DateBegin, args.DateEnd); var lpu = dataConext.VistaMed.Organisations.FirstOrDefault(x => x.MiacCode == Properties.Settings.Default.LpuMiacCode); if (lpu != null) { newDoc.Names.Item("ЛПУ").RefersToRange.Value2 = lpu.FullName; } var rowIndex = 6; var npp = 1; foreach (var data in sourceData) { var row = (Range) ws.Rows[rowIndex]; var colIndex = 1; ((Range)row.Cells[1, colIndex++]).Value2 = npp; ((Range)row.Cells[1, colIndex++]).Value2 = data.ClientFio; ((Range)row.Cells[1, colIndex++]).Value2 = data.ClientBirthDate; ((Range)row.Cells[1, colIndex++]).Value2 = data.Mkb; ((Range)row.Cells[1, colIndex++]).Value2 = data.ActionDate; ((Range)row.Cells[1, colIndex++]).Value2 = string.Concat(data.ActionCode , " " , data.ActionName); ((Range)row.Cells[1, colIndex++]).Value2 = string.Concat(data.PersonFio, " (", data.PersonCode, ")"); ((Range)row.Cells[1, colIndex++]).Value2 = data.EventName; ((Range)row.Cells[1, colIndex++]).Value2 = data.OperFio; rowIndex++; npp++; } excel.ScreenUpdating = true; ws.Activate(); } } } context.ProgressSeparator('-'); }