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('-');
}