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 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('-');
}
/// <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 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('-');
}
public override void SetDataSource(object source)
{
var list = new List<EventCreatorPerson> {new EventCreatorPerson {Id = 0, Name = ""}};
using (var dataContext = new VistaMedDataContext(null))
{
var allPersons = dataContext.VistaMed.People.Where(c => !c.Deleted && c.RetireDate == null).ToList();
list.AddRange((from c in allPersons
select new EventCreatorPerson {Id = c.Id, Name = string.Format(@"{0} ({1})", c.FIO, c.Code)})
.OrderBy(x => x.Name).ToList());
}
eventCreatorPersonBindingSource.DataSource = list;
base.SetDataSource(source);
}
private static void verifyMkb(IBackgroundContext context, PrevalidatorParams args, VistaMedDataContext dataContext)
{
if (context.CancellationPending) return;
context.ReportProgress(@"Проверка на пустые МКБ");
var emptyMkbActions = (from action in dataContext.VistaMed.ActionsNotInAccount
where action.EndDate >= args.DateBegin && action.EndDate <= args.DateEnd
&& action.Event != null && action.Event.EventType != null
&& action.Event.EventType.Name.ToLower().Contains(@"стоматология")
&& action.MKB == null
select action).ToList();
if (emptyMkbActions.Any())
{
context.ReportError(@"Неопределенный МКБ для услуг");
foreach (var action in emptyMkbActions)
{
context.ReportProgress(@" > {0} {1}", action.Event, action.ToShortString());
if (context.CancellationPending) return;
}
}
if (context.CancellationPending) return;
var emptyMkbDs = from d in dataContext.VistaMed.Diagnoses
from ds in dataContext.VistaMed.Diagnostics
where (d.MKB == null || string.IsNullOrEmpty(d.MKB))
&& ds.DiagnosisId == d.Id
&& ds.Event != null && ds.Event.ExecDate >= args.DateBegin &&
ds.Event.ExecDate <= args.DateEnd
&& !dataContext.VistaMed.AccountItems.Any(x => x.EventId == ds.EventId)
select ds.Event;
if (emptyMkbDs.Any())
{
context.ReportError(@"Неопределенный МКБ для диагнозов");
foreach (var dsEvent in emptyMkbDs)
{
context.ReportProgress(@" > {0}", dsEvent);
if (context.CancellationPending) return;
}
}
var unknownMkbActions = (from action in dataContext.VistaMed.ActionsNotInAccount
where action.EndDate >= args.DateBegin && action.EndDate <= args.DateEnd
&& action.Event != null && action.Event.EventType != null
&& !string.IsNullOrEmpty(action.MKB)
&& !(from m in dataContext.VistaMed.MKBs where action.MKB == m.DiagID select m).Any()
select action).ToList();
if (context.CancellationPending) return;
if (unknownMkbActions.Any())
{
context.ReportError(@"Несуществующий МКБ");
foreach (var action in unknownMkbActions)
{
context.ReportProgress(@" > {0} {1}", action.Event, action.ToShortString());
if (context.CancellationPending) return;
}
}
}
private static void assignBillNumbers(IBackgroundContext context, VistaMedDataContext dataContext,
List<AccountData> billData)
{
if (billData.Any())
{
var fileName = Path.GetFullPath(Settings.Default.BillsListFileName);
var excel = new Application();
excel.Visible = true;
var wb = excel.Workbooks.Open(fileName);
try
{
var groupKey = "";
var billIndexInGroup = 1;
foreach (var bill in billData)
{
if (groupKey != bill.Name)
{
groupKey = bill.Name;
billIndexInGroup = 1;
}
var billKey = bill.Name + " " + billIndexInGroup;
var year = bill.BillDate.ToString("yyyy");
var ws = wb.Worksheets.Cast<Worksheet>().FirstOrDefault(x => x.Name == year);
if (ws != null)
{
Range keyColumnRange = ws.UsedRange.Columns[6];
Range foundCell = keyColumnRange.Find(billKey);
if (foundCell == null)
{
int lastNum = 0;
Range numColumn = ws.UsedRange.Columns[1];
for (int i = numColumn.Cells.Count; i >=1 ; i--)
{
var numCell = numColumn.Cells[i, 1];
var numVal = numCell.Value2;
if (numVal != null)
if ( int.TryParse(numVal.ToString(), out lastNum))
break;
}
lastNum++;
var newRowIndex = ws.UsedRange.Rows.Count + 1;
var colIndex = 1;
ws.Cells.SetCellValue(newRowIndex, colIndex++, lastNum);
ws.Cells.SetCellValue(newRowIndex, colIndex++, bill.CreateDate);
ws.Cells.SetCellValue(newRowIndex, colIndex++, bill.Name
+ (billIndexInGroup == 1 ? "" : " #" + billIndexInGroup));
ws.Cells.SetCellValue(newRowIndex, colIndex++, bill.Amount);
ws.Cells.SetCellValue(newRowIndex, colIndex++, bill.Summa);
ws.Cells.SetCellValue(newRowIndex, colIndex++, billKey);
dataContext.VistaMed.ExecuteSql(
string.Format(@"UPDATE Account SET number = '{0}' WHERE id = {1}",
lastNum, bill.Id));
}
}
else
context.ReportError(@"Не найден лист {0}", year);
billIndexInGroup++;
}
wb.Save();
}
finally
{
dataContext.VistaMed.SubmitChanges();
excel.Quit();
}
}
else
{
context.ReportError(@"Счета не найдены");
}
}
private static void verifyPolicies(IBackgroundContext context, VistaMedDataContext dataContext, List<Event> allEvents)
{
context.ReportProgress(@"Проверка длины номера полисов ...");
var errors = new List<string>();
var policies = (from e in allEvents
where e.Client != null
select e.Client.GetActivePolicy(dataContext.VistaMed)).Distinct()
.Where(x => x != null).ToList();
var errPol9 = (from p in policies
where p.RbPolicyKind != null
&& p.RbPolicyKind.FederalCode == "2"
&& !string.IsNullOrEmpty(p.Number)
&& p.Number.Length != 9
select p).ToList();
if (errPol9.Count > 0)
{
errors.Add(string.Format(@"ВРЕМЕННЫЕ СВИДЕТЕЛЬСТВА С НЕВЕРНОЙ ДЛИНОЙ НОМЕРА (НЕОБХОДИМО 9 СИМВОЛОВ)"));
foreach (var clientPolicy in errPol9)
{
#if NOPERSONALDATA
var policyNumber = clientPolicy.Number.OfChar('N');
#else
var policyNumber = clientPolicy.Number;
#endif
errors.Add(string.Format(@" {0} номер {1} длина номера {2}",
clientPolicy.Client.CardMarker, policyNumber, clientPolicy.Number.Length));
}
}
var errPol16 = (from p in policies
where p.RbPolicyKind != null
&& p.RbPolicyKind.FederalCode == "3"
&& !string.IsNullOrEmpty(p.Number)
&& p.Number.Length != 16
select p).ToList();
if (errPol16.Count > 0)
{
errors.Add(string.Format(@"ПОЛИСЫ НОВОГО ОБРАЗЦА С НЕВЕРНОЙ ДЛИНОЙ НОМЕРА (НЕОБХОДИМО 16 СИМВОЛОВ)"));
foreach (var clientPolicy in errPol16)
{
#if NOPERSONALDATA
var policyNumber = clientPolicy.Number.OfChar('N');
#else
var policyNumber = clientPolicy.Number;
#endif
errors.Add(string.Format(@" {0} номер {1} длина номера {2}",
clientPolicy.Client.CardMarker, policyNumber, clientPolicy.Number.Length));
}
}
if (errors.Count > 0 && !context.CancellationPending)
{
context.ReportError("ПРОВЕРКА ДЛИНЫ НОМЕРОВ ПОЛИСОВ");
errors.ForEach(x => context.ReportProgress(@" -> {0}", x));
}
errors.Clear();
var errPolType = (from p in policies
where p.RbPolicyKind != null
&& !(p.RbPolicyKind.FederalCode == "1" || p.RbPolicyKind.FederalCode == "2" || p.RbPolicyKind.FederalCode == "3")
select p).ToList();
if (errPolType.Count > 0)
{
errors.Add(string.Format(@"ПОЛИСЫ ОШИБОЧНОГО ВИДА"));
foreach (var clientPolicy in errPolType)
{
#if NOPERSONALDATA
var policyNumber = clientPolicy.Number.OfChar('N');
#else
var policyNumber = clientPolicy.Number;
#endif
errors.Add(string.Format(@" {0} код вида {1} вид '{2}'",
clientPolicy.Client.CardMarker, clientPolicy.RbPolicyKind.FederalCode, clientPolicy.RbPolicyKind.Name));
}
}
if (errors.Count > 0 && !context.CancellationPending)
{
errors.ForEach(x => context.ReportProgress(@" -> {0}", x));
}
}
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('-');
}
public Ambulance(IBackgroundContext context, VistaMedDataContext dataContext)
: base(context, dataContext)
{
}
private static void verifyKolUsl(IBackgroundContext context, List<Action> allActions, VistaMedDataContext dataContext)
{
var errorActions = (from a in allActions where Math.Abs(a.Amount - 0) < 0.00001 && a.Event != null
&& a.Event.EventType != null
&& !a.Event.EventType.Code.StartsWith("dd2013") select a).ToList();
if(errorActions.Any())
{
context.ReportError(@"МЕРОПРИЯТИЯ С НУЛЕВЫМ КОЛИЧЕСТВОМ");
foreach (var errorAction in errorActions)
{
context.ReportProgress(@" {0}", errorAction.Event, errorAction);
context.ReportProgress(@" {0}", errorAction);
}
}
}
/// <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('-');
}
private static void verifyStationarLittleStrangerFlag(IBackgroundContext context, List<Event> allEvents, VistaMedDataContext dataContext)
{
var littleStrangerEvents = (from e in allEvents where e.LittleStrangerId != null select e).ToList();
var errorEvents = from e in littleStrangerEvents
from d in e.Diagnostics
where d.Diagnosis != null && d.Diagnosis.MKB.StartsWith("O")
select new { Event = e, Mkb = d.Diagnosis.MKB};
var errors = new List<string>();
foreach (var badItem in errorEvents)
{
errors.Add(string.Format(@"{0}. Диагноз {1} для новорожденного. Уберите флаг новорожденного в талоне.",
badItem.Event, badItem.Mkb));
}
if (errors.Count > 0)
{
context.ReportError(@"СТАЦИОНАР: Недопустимые диагнозы для новорожденных");
errors.ForEach(x => context.ReportProgress(@" -> {0}", x));
}
}
private static void verifyStationar(IBackgroundContext context, List<Event> allEvents, VistaMedDataContext dataContext, List<Action> allActions)
{
if (context.CancellationPending) return;
context.ReportProgress(@"Проверка талонов стационара");
// тип мероприятия - "Движение"
var moveActType =
(from c in dataContext.VistaMed.ActionTypes where c.Name == "Движение" && c.Code == "2" select c)
.FirstOrDefault();
// тип свойства - "койка"
var bedPropType = (from c in moveActType.ActionPropertyTypes
where !c.Deleted && c.Name == "койка" && c.TypeName == "HospitalBed"
select c).FirstOrDefault();
verifyStationarBedProfile(context, allEvents, dataContext, moveActType, bedPropType, "Стационарная помощь");
verifyStationarBedProfile(context, allEvents, dataContext, moveActType, bedPropType, "Дневной стационар");
verifyStationarPeriods(context, allEvents, dataContext, moveActType);
verifyStationarMovePerson(context, allActions, dataContext, moveActType);
verifyStationarLittleStrangerFlag(context, allEvents, dataContext);
}
private static void afterFill(IBackgroundContext context, ReestrDdParams args, VistaMedDataContext dataConext,
Account smpBill, Workbook newDoc, List<ReestrTotals> totals, List<IGrouping<Event, AccountItem>> eventGroups, List<ErrorZeroSum> errors, List<string> zeroFioList)
{
var insurer = (from o in dataConext.VistaMed.Organisations
where o.Id == smpBill.Contract.PayerId
select o).FirstOrDefault();
if (insurer != null)
{
string insurerName = "";
if (insurer.MiacCode == "67002")
insurerName = "СОГАЗ";
else if (insurer.MiacCode == "67003")
insurerName = "МАКС";
else if (insurer.MiacCode == "67004")
insurerName = "РГС";
else if (insurer.MiacCode == "67005")
insurerName = "СПВ";
if (string.IsNullOrEmpty(insurerName))
{
insurerName = new string(insurer.ShortName.ToArray()
.Except(Path.GetInvalidFileNameChars()).ToArray());
}
saveReport(context, args, insurerName, smpBill, newDoc);
totals.Add(new ReestrTotals()
{
AccountType = smpBill.AccountType,
InsurerName = insurerName,
Kol = eventGroups.Count
});
errors.AddRange(zeroFioList.Select(x =>
new ErrorZeroSum() {AccountType = smpBill.AccountType, Fio = x, Insurer = insurerName}));
}
}
private static void verifyContracts(IBackgroundContext context, PrevalidatorParams prevalidatorParams, VistaMedDataContext dataContext)
{
if (context.CancellationPending) return;
var errors = (from a in dataContext.VistaMed.Actions
let e = a.Event
where e.ExecDate != null && e.Contract != null
&& e.ExecDate >= prevalidatorParams.DateBegin
&& e.ExecDate <= prevalidatorParams.DateEnd
&& !dataContext.VistaMed.AccountItems.Any(x => x.EventId == e.Id)
&& a.Contract != null && a.Contract != e.Contract
select new {Event = e, Action = a}).ToList();
var invalidGroups = (from s in errors group s by s.Event into g select g).ToList();
if (invalidGroups.Any())
{
context.ReportError(@"Несовпадение договора в талоне и услугах");
foreach (var invalidGroup in invalidGroups)
{
context.ReportProgress(@" > {0}", invalidGroup.Key);
context.ReportProgress(@" > договор события {0}",
invalidGroup.Key.Contract.GetFullName(dataContext.VistaMed));
foreach (var action in invalidGroup.Select(x => x.Action))
{
var actionStr = (action.ActionType == null ? "" : action.ActionType.Code);
context.ReportProgress(@" > договор мероприятия {0} {1}",
actionStr, action.Contract.GetFullName(dataContext.VistaMed));
}
}
}
}
private static void verifyStationarPeriods(IBackgroundContext context, List<Event> allEvents, VistaMedDataContext dataContext, ActionType moveActType)
{
var stationarEvents = from e in allEvents
where e.EventType.RbMedicalAidType != null
&& e.EventType.RbMedicalAidType.Name != null
&& e.EventType.RbMedicalAidType.Name.ToLower().Contains("стационар")
&& !e.EventType.RbMedicalAidType.Name.ToLower().Contains("дневной")
select e;
var moveActions = from e in stationarEvents
from c in e.Actions
where !c.Deleted && c.ActionType == moveActType
select c;
var errors = new List<string>();
foreach (var moveAction in moveActions.TakeWhile(x => !context.CancellationPending))
{
var dateRange = new DateRange(moveAction.BegDate, moveAction.EndDate);
var client = moveAction.Event.Client;
var clientActions = (from e in allEvents where e.Client == client && e != moveAction.Event select e).ToList();
var theEvents = (from a in clientActions let dr = new DateRange(a.SetDate, a.ExecDate)
where dr.Intersects(dateRange)
&& (dr.EndDate != dateRange.StartDate)
&& (dr.EndDate != dateRange.EndDate)
select a).ToList();
if (theEvents.Any())
{
errors.Add(string.Format(@"СТАЦИОНАР.ТАЛОН '{0}'", moveAction.Event));
errors.Add(string.Format(@" ДВИЖЕНИЕ '{0}'", moveAction));
foreach (var theEvent in theEvents)
{
errors.Add(string.Format(@" Пересечение периодов '{0}'", theEvent));
}
}
}
if (errors.Count > 0 && !context.CancellationPending)
{
context.ReportError(@"""{0}""", "ПЕРЕСЕЧЕНИЕ ПЕРИОДОВ");
errors.ForEach(x => context.ReportProgress(@" -> {0}", x));
}
}
private static void verifyStationarMovePerson(IBackgroundContext context, List<Action> allActions,
VistaMedDataContext dataContext, ActionType moveActType)
{
var badMoveActions = (from c in allActions
where c.ActionType == moveActType
&& c.Person_SetPersonId != c.Event.Person_ExecPersonId
select c).ToList();
var errors = new List<string>();
foreach (var badAction in badMoveActions)
{
errors.Add(string.Format(@"{0}. Исполнитель в мероприятии ""{1}"" а в талоне ""{2}""", badAction.Event,
badAction.Person_PersonId == null ? "не задан" : badAction.Person_PersonId.FIO,
badAction.Event.Person_ExecPersonId == null ? "не задан" : badAction.Event.Person_ExecPersonId.FIO));
}
if (errors.Count > 0)
{
context.ReportError(@"СТАЦИОНАР: МЕРОПРИЯТИЯ ДВИЖЕНИЯ С НЕВЕРНЫМ ИСПОЛНИТЕЛЕМ");
errors.ForEach(x => context.ReportProgress(@" -> {0}", x));
}
}
public static void BuildSvedProf(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 => args.IsValidAccountNumber(x.Number)
&& !x.Number.StartsWith(PlanExecution.ReserveAccountName)).ToList();
if (smpBills.Count == 0)
context.ReportError(@"Счета ДД за период с {0} по {1} не найдены", dateBegin, dateEnd);
else
{
var excel = new Application();
excel.Visible = true;
var plans = loadPlans(excel);
var templateName = Utils.GetReportFileName(@"SvedProf.xls");
if (File.Exists(templateName))
{
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;
}
var reportColumn = new ReportColumn();
reportColumn.Man = plans.Sum(x => x.Man);
reportColumn.Woman = plans.Sum(x => x.Woman);
fillReportColumn(ws, reportColumn, 3);
reportColumn = new ReportColumn();
reportColumn.Man = plans.Where(y => y.Month == dateEnd.Month).Sum(x => x.Man);
reportColumn.Woman = plans.Where(y => y.Month == dateEnd.Month).Sum(x => x.Woman);
fillReportColumn(ws, reportColumn, 7);
var allevents = (from b in smpBills
from a in b.AccountItems
where !a.Deleted && a.Event != null && a.Event.Client != null
group a by a.Event
into e
select new {Event = e.Key, Client = e.Key.Client, Summa = e.Sum(x => x.Sum)}).ToList();
var man = (from e in allevents where e.Client.Sex == 1 select e).ToList();
var woman = (from e in allevents where e.Client.Sex == 2 select e).ToList();
reportColumn = new ReportColumn();
reportColumn.Man = man.Sum(x => x.Summa);
reportColumn.Woman = woman.Sum(x => x.Summa);
fillReportColumn(ws, reportColumn, 8);
reportColumn = new ReportColumn();
reportColumn.Man = man.Count();
reportColumn.Woman = woman.Count();
fillReportColumn(ws, reportColumn, 9);
excel.ScreenUpdating = true;
ws.Activate();
}
}
}
}
public Stacionar(IBackgroundContext context, VistaMedDataContext dataContext)
: base(context, dataContext)
{
}
public static void BuildReestrDd(IBackgroundContext context, ReestrDdParams args)
{
var dateBegin = args.DateBegin;
var dateEnd = args.DateEnd;
var miacCode = args.LpuCode;
var billNum = args.AccountNumbers;
using (var dataConext = new VistaMedDataContext(context))
{
var smpBills = (from b in dataConext.VistaMed.Accounts
where b.SettleDate >= dateBegin && b.SettleDate <= dateEnd && b.Date == args.CreateDate
&& 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("061") || s.Code.StartsWith("161")
|| s.Code.StartsWith("062") || s.Code.StartsWith("162"))
select a).Any()
select b).ToList();
if (!string.IsNullOrEmpty(billNum))
{
var billNumList = billNum.Split(',');
smpBills = smpBills.Where(x => billNumList.Contains(x.Number)).ToList();
}
if (smpBills.Count == 0)
context.ReportError(@"Счета ДД за период с {0} по {1} не найдены", dateBegin, dateEnd);
else
{
var excel = new Application();
excel.Visible = true;
var totals = new List<ReestrTotals>();
var errors = new List<ErrorZeroSum>();
foreach (var smpBill in smpBills)
{
var templateFileName = @"ReestrDd.xls";
if (smpBill.AccountType == AccountType.DDR2)
templateFileName = @"ReestrDd2.xls";
var templateName = Utils.GetReportFileName(templateFileName);
var zeroFioList = new List<string>();
if (!File.Exists(templateName))
continue;
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;
var lpu = dataConext.VistaMed.Organisations.FirstOrDefault(x => x.MiacCode == miacCode);
if (lpu != null)
{
newDoc.Names.Item("ЛПУ").RefersToRange.Value2 = lpu.FullName + " ОГРН " + lpu.OGRN ?? "";
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;
}
newDoc.Names.Item("НомерСчета").RefersToRange.Value2 = smpBill.Number;
newDoc.Names.Item("ДатаСчета").RefersToRange.Value2 = smpBill.SettleDate;
string ddName = null;
string tarifHeader = null;
string header13 = null;
switch (smpBill.AccountType)
{
case AccountType.DdVet:
ddName = "в рамках первого этапа диспансеризации ветеранов ВОВ и инвалидов";
tarifHeader =
"Тариф на плату первого этапа диспансеризации, проведенной застрахованному лицу";
header13 = "Первый этап диспансеризации в объеме, утвержденном Минздравом России, " +
"специальность медицинского работника, проводящего диспансеризацию (код)";
break;
case AccountType.DDR1:
ddName = "в рамках первого этапа диспансеризации определенных групп взрослого населения";
tarifHeader =
"Тариф на плату первого этапа диспансеризации, проведенной застрахованному лицу";
header13 = "Первый этап диспансеризации в объеме, утвержденном Минздравом России, " +
"специальность медицинского работника, проводящего диспансеризацию (код)";
break;
case AccountType.DDR2:
ddName = "в рамках второго этапа диспансеризации определенных групп взрослого населения";
tarifHeader =
"Стоимость второго этапа диспансеризации, проведенной застрахованному лицу";
header13 = "Второй этап диспансеризации в объеме, утвержденном Минздравом России, " +
"специальность медицинского работника, проводящего диспансеризацию (код), " +
"тариф на оплату второго этапа диспансеризации, проведенной застрахованному лицу";
break;
case AccountType.DDOrhan:
ddName =
"в рамках диспансеризации детей-сирот и детей, оставшихся без попечения родителей, " +
"в том числе усыновленных (удочеренных), принятых под опеку (попечительство), " +
"в приемную или патронатную семью";
header13 = "Диспансеризация в объеме утвержденном Минздравом России, " +
"специальность медицинского работника, проводящего диспансеризацию (код)";
tarifHeader = "Тариф на оплату диспансеризации проведенной застрахованному лицу";
break;
case AccountType.DDHardLive:
ddName =
"в рамках диспансеризации детей сирот, пребывающих в стационарных учреждениях и детей, " +
"находящихся в трудной жизненной ситуации";
header13 = "Диспансеризация в объеме утвержденном Минздравом России, " +
"специальность медицинского работника, проводящего диспансеризацию (код)";
tarifHeader = "Тариф на оплату диспансеризации проведенной застрахованному лицу";
break;
case AccountType.DD14:
break;
case AccountType.Osm18:
ddName = "в рамках медицинских осмотров несовершеннолетних";
header13 =
"Медицинские осмотры несовершеннолетних в объеме, утвержденном Минздравом России, " +
"специальность медицинского работника, проводящего медицинские осмотры (код)";
tarifHeader = "Тариф на оплату медицинского осмотра проведенного застрахованному лицу";
break;
case AccountType.ProfOsm:
ddName = "за проведенные профилактические медицинские осмотры взрослого населения";
header13 = "Медицинские осмотры в объеме, утвержденном Минздравом России, " +
"специальность медицинского работника, проводящего профилактический " +
"медицинский осмотр (код)";
tarifHeader = "Тариф на оплату профилактических медицинских осмотров, " +
"проведенных застрахованному лицу";
break;
default:
break;
}
if (!string.IsNullOrEmpty(ddName))
newDoc.Names.Item("НазваниеДД").RefersToRange.Value2 = ddName;
if (!string.IsNullOrEmpty(tarifHeader))
newDoc.Names.Item("ЗаголовокТарифа").RefersToRange.Value2 = tarifHeader;
if (!string.IsNullOrEmpty(header13))
newDoc.Names.Item("Кол13").RefersToRange.Value2 = header13;
var eventGroups = (from accItem in smpBill.AccountItems
group accItem by accItem.Event
into g
orderby g.Key.Client.Fio
select g).ToList();
foreach (var eventGroup in eventGroups)
{
((Range)ws.Rows[14]).Insert();
((Range)ws.Rows[15]).Copy(Destination: ((Range)ws.Rows[14]));
}
var rowIndex = 1;
foreach (var eventGroup in eventGroups)
{
var client = eventGroup.Key.Client;
if (client != null)
{
var newRow = ((Range)ws.Rows[rowIndex + 13]);
((Range)newRow.Cells[1, 1]).Value2 = rowIndex;
((Range)newRow.Cells[1, 2]).Value2 = client.Fio;
((Range)newRow.Cells[1, 3]).Value2 = client.Sex == 1 ? "М" : "Ж";
((Range)newRow.Cells[1, 4]).Value2 = client.BirthDate;
var clientDocId = dataConext.VistaMed.GetClientDocumentId(client.Id);
var clientDoc =
(from c in dataConext.VistaMed.ClientDocuments
where c.Id == clientDocId
select c).FirstOrDefault();
if (clientDoc != null)
((Range)newRow.Cells[1, 6]).Value2 = clientDoc.Serial + " " + clientDoc.Number;
((Range)newRow.Cells[1, 7]).Value2 = dataConext.VistaMed.GetClientLocAddress(client.Id);
var clientPolicyId = dataConext.VistaMed.GetClientPolicyId(client.Id, 1);
var clientPolicy =
dataConext.VistaMed.ClientPolicies.FirstOrDefault(x => x.Id == clientPolicyId);
((Range)newRow.Cells[1, 9]).Value2 = client.GetSnilsFormatted();
if (clientPolicy != null)
((Range)newRow.Cells[1, 10]).Value2 =
(clientPolicy.Serial + " " + clientPolicy.Number).Trim();
((Range)newRow.Cells[1, 11]).Value2 = "1";
var diagnosisId = dataConext.VistaMed.GetEventDiagnosis(eventGroup.Key.Id);
var mkb =
(from d in dataConext.VistaMed.Diagnoses where d.Id == diagnosisId select d.MKB)
.FirstOrDefault();
((Range)newRow.Cells[1, 12]).Value2 = mkb ?? "";
var prnActions = (from item in eventGroup
where item.Action != null
&&
(item.Action.ActionType.Name.StartsWith(@"Комплексная услуга")
|| item.Action.ActionType.Name.StartsWith(@"Комп. услуга")
|| item.Action.ActionType.Name.StartsWith(@"Осмотр врача"))
select item).ToList();
((Range)newRow.Cells[1, 13]).Value2 = string.Join(" ",
prnActions.Select(x => x.Action.ActionType.Code));
((Range)newRow.Cells[1, 14]).Value2 = string.Join(" ",
prnActions.Select(x => x.Action.BegDate.Value.ToShortDateString()));
((Range)newRow.Cells[1, 15]).Value2 = string.Join(" ",
prnActions.Select(x => x.Action.EndDate.Value.ToShortDateString()));
((Range)newRow.Cells[1, 16]).Value2 = string.Join(" ",
prnActions.Select(
x =>
x.Action.Person_PersonId == null
? ""
: x.Action.Person_PersonId.RbSpeciality.FederalCode.PadRight(15)));
var colIndex = 17;
if (smpBill.AccountType == AccountType.DDR2)
{
((Range)newRow.Cells[1, colIndex++]).Value2 = string.Join(" ",
prnActions.Select(x => x.Sum.ToString(@"F2")));
}
var summa = eventGroup.Sum(x => x.Sum);
((Range) newRow.Cells[1, colIndex++]).Value2 = summa;
((Range)newRow.Cells[1, colIndex++]).Value2 = eventGroup.Key.RbResult.FederalCode;
if (Math.Abs(summa) < 0.01)
{
zeroFioList.Add(eventGroup.Key.Client.Fio + " " + eventGroup.Key.Client.BirthDate.ToShortDateString());
}
rowIndex++;
}
}
var totalRow = ((Range)ws.Rows[rowIndex + 13]);
((Range)totalRow.Cells[1, 2]).Value2 = @"ИТОГО";
((Range)totalRow.Cells[1, 17 + (smpBill.AccountType == AccountType.DDR2 ? 1 : 0)]).Value2 = smpBill.Sum;
excel.ScreenUpdating = true;
ws.Activate();
afterFill(context, args, dataConext, smpBill, newDoc, totals, eventGroups, errors, zeroFioList);
}
generateTotals(excel, totals, errors, args, context);
}
}
}
private static void verifyStationarBedProfile(IBackgroundContext context,
List<Event> allEvents,
VistaMedDataContext dataContext, ActionType moveActType,
ActionPropertyType bedPropType, string eventKindName)
{
var eventTypes = from c in dataContext.VistaMed.EventTypes
where c.RbMedicalAidType != null
&& c.RbMedicalAidType.Name == eventKindName
select c;
var stationarEvents = from e in allEvents
where eventTypes.Contains(e.EventType)
select e;
// мероприятия "Движение"
var moveActions = from e in stationarEvents
from c in e.Actions
where !c.Deleted && c.ActionType == moveActType
select new {Event = e, Action = c};
// выборка свойств "койка"
var actProp = from c in moveActions
from p in c.Action.ActionProperties
where p.ActionPropertyType == bedPropType && !p.Deleted
select new { Event = c.Event, Action = c.Action, BedProp = p };
// выборка значений "койка"
var actBed = (from c in actProp
join bedTypedProp in dataContext.VistaMed.ActionPropertyHospitalBeds on c.BedProp.Id equals bedTypedProp.Id
select new { c.Event, c.Action, Bed = bedTypedProp.OrgStructureHospitalBed }).ToList();
Func<RbHospitalBedProfile, bool> comparator;
if(eventKindName.Contains("Дневной"))
comparator = x => x.Name.Contains(" ДС");
else
comparator = x => !x.Name.Contains(" ДС");
var wrongBeds = from c in actBed
where c.Bed.RbHospitalBedProfile != null && !comparator(c.Bed.RbHospitalBedProfile)
select c;
var errors = new List<string>();
foreach (var wrongBed in wrongBeds)
{
errors.Add(string.Format(@"{0}. Неверный тип койки {1}", wrongBed.Event,
wrongBed.Bed.RbHospitalBedProfile.Name));
}
if (errors.Count > 0)
{
context.ReportError(@"""{0}""", eventKindName);
errors.ForEach(x => context.ReportProgress(@" -> {0}", x));
}
}
private static List<FomsReportRowData> getFomsReportData(IBackgroundContext context, AccountCorrectParams args, string excelFileName,
Application excel)
{
var result = new List<FomsReportRowData>();
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();
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.SelectFomsReportData(ws, args, result));
}
}
finally
{
dataContext.VistaMed.Connection.Close();
}
}
excel.ScreenUpdating = true;
}
else
context.ReportError(@"КоличествоМесяцев в файле не соответствует выбранному Вами периоду");
}
else
context.ReportError(@"Имя ячейки ""КоличествоМесяцев"" не найдено в файле {0}", excelFileName);
newDoc.Close(false);
}
return result;
}
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('-');
}
public Polyclinic(IBackgroundContext context, VistaMedDataContext dataContext)
: base(context, dataContext)
{
}
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 Section(int minRowIndex, int maxRowIndex,
VistaMedDataContext dataContext,
StacionarType stacionarType,
IBackgroundContext context)
{
this.minRowIndex = minRowIndex;
this.maxRowIndex = maxRowIndex;
this.dataContext = dataContext;
this.stacionarType = stacionarType;
this.context = context;
}
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('-');
}
protected PageType(IBackgroundContext context, VistaMedDataContext dataContext)
{
this.context = context;
this.dataContext = dataContext;
}
/// <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('-');
}
