protected override void fillFactData(Worksheet ws, MonthParams args, int smoId) { string sqlTextBase = @"SELECT spec.federalCode AS SpecCode, s.code AS ServiceCode, LEFT(s.code, 1) AS IsChild, SUM(ai.amount) AS Kol, SUM(ai.uet) AS Uet, COUNT(DISTINCT e.id) AS EventCount, IF(s.name LIKE '%профилакт%', 0, IF(s.name LIKE '%обращени%', 2, 1)) AS ActionKind FROM Account a JOIN Account_Item ai ON a.id = ai.master_id AND a.deleted = 0 AND ai.deleted = 0 AND a.number NOT LIKE '{3}%' JOIN rbService s ON s.id = ai.service_id JOIN Event e ON e.id = ai.event_id AND e.deleted = 0 JOIN Person p ON p.id = e.execPerson_id AND p.deleted = 0 JOIN rbSpeciality spec ON spec.id = p.speciality_id JOIN Contract c ON c.id = a.contract_id AND c.deleted=0 JOIN rbFinance f ON f.id = c.finance_id AND f.name='омс' WHERE a.date >= '{0}' AND a.date < '{1}' /*CodeFilter*/ AND (c.payer_id = {2}) AND ai.sum > 0 GROUP BY spec.federalCode, s.code"; var monthBeginDate = args.NextMonthBegin; var monthEndDate = args.NextMonthEnd.AddDays(1); var yearBeginDate = args.DateEnd.BeginOfYear(); var yearEndDate = args.DateEnd.AddDays(1); fillData(ws, args, sqlTextBase, smoId, monthBeginDate, monthEndDate, yearBeginDate, yearEndDate); }
protected override void fillFactData(Worksheet ws, MonthParams args, int smoId) { string sqlTextBase = @"SELECT s.code AS ServiceCode, COUNT(*) AS EventCount, COUNT(DISTINCT e.client_id) AS ClientCount FROM Account a JOIN Account_Item ai ON a.id = ai.master_id AND a.deleted = 0 AND ai.deleted = 0 AND a.number NOT LIKE '{3}%' JOIN Event e ON e.id = ai.event_id AND e.deleted = 0 JOIN Contract c ON c.id = a.contract_id AND c.deleted = 0 JOIN rbService s ON s.id = ai.service_id JOIN rbFinance f ON f.id = c.finance_id AND f.name='омс' WHERE a.date >= '{0}' AND a.date < '{1}' /*CodeFilter*/ AND (c.payer_id = {2}) AND ai.sum > 0 GROUP BY s.code"; var monthBeginDate = args.NextMonthBegin; var monthEndDate = args.NextMonthEnd.AddDays(1); var yearBeginDate = args.DateEnd.BeginOfYear(); var yearEndDate = args.DateEnd.AddDays(1); fillData(ws, args, sqlTextBase, smoId, monthBeginDate, monthEndDate, yearBeginDate, yearEndDate); }
public void FillCurrent(Worksheet ws, MonthParams args) { string smoCode; if (isTagretPage(ws.Name, out smoCode)) { int smoId = getSmoId(smoCode); context.ReportProgress("Заполняем текущее выполнение планов {0} ...", ws.Name); fillCurrentData(ws, args, smoId); } }
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('-'); }
protected override void fillCurrentData(Worksheet ws, MonthParams args, int smoId) { string sqlTextBase = @"SELECT s.code AS ServiceCode, COUNT(DISTINCT a.id) AS EventCount, 0 AS ClientCount FROM Action a JOIN ActionType s ON s.id = a.actionType_id AND a.deleted = 0 AND s.deleted = 0 JOIN Event e ON e.id = a.event_id AND e.deleted = 0 JOIN Contract c ON c.id = e.contract_id AND c.deleted = 0 JOIN rbFinance f ON f.id = c.finance_id AND f.name='омс' WHERE e.execDate >= '{0}' AND e.execDate < '{1}' /*CodeFilter*/ AND (c.payer_id = {2}) GROUP BY 1 UNION ALL SELECT s.code AS ServiceCode, COUNT(DISTINCT v.id) AS EventCount, COUNT(DISTINCT e.client_id) AS ClientCount FROM Visit v JOIN rbService s ON s.id = v.service_id AND v.deleted = 0 JOIN Event e ON e.id = v.event_id AND e.deleted = 0 JOIN Contract c ON c.id = e.contract_id AND c.deleted = 0 JOIN rbFinance f ON f.id = c.finance_id AND f.name='омс' WHERE e.execDate >= '{0}' AND e.execDate < '{1}' /*CodeFilter*/ AND (c.payer_id = {2}) GROUP BY 1"; var monthBeginDate = args.DateBegin; var monthEndDate = args.DateEnd.AddDays(1); var yearBeginDate = args.DateEnd.BeginOfYear(); var yearEndDate = args.DateEnd.AddDays(1); fillData(ws, args, sqlTextBase, smoId, monthBeginDate, monthEndDate, yearBeginDate, yearEndDate); }
private void fillData(Worksheet ws, MonthParams args, string sqlTextBase, int smoId, DateTime monthBeginDate, DateTime monthEndDate, DateTime yearBeginDate, DateTime yearEndDate) { if (smoId == 0) sqlTextBase = sqlTextBase.Replace(@"AND (c.payer_id = {2})", " "); fillServiceData(ws, args, sqlTextBase, smoId, doctorServiceCodes, doctorEventsRowIndex, monthBeginDate, monthEndDate, yearBeginDate, yearEndDate); fillServiceData(ws, args, sqlTextBase, smoId, feldServiceCodes, feldEventsRowIndex, monthBeginDate, monthEndDate, yearBeginDate, yearEndDate); }
private void fillData(Worksheet ws, MonthParams args, string sqlTextBase, int smoId, DateTime monthBeginDate, DateTime monthEndDate, DateTime yearBeginDate, DateTime yearEndDate) { var sections = new[] { new Section(5, 39, dataContext, StacionarType.Full, context), new Section(45, 72, dataContext, StacionarType.Day, context), new Section(78, 106, dataContext, StacionarType.Apu, context) }; if (smoId == 0) sqlTextBase = sqlTextBase.Replace(@"AND (c.payer_id = {2})", " "); string sqlTextMonth = string.Format(sqlTextBase, monthBeginDate.SqlStr(), monthEndDate.SqlStr(), smoId, ReserveAccountName); List<FactData> sqlDataMonth = dataContext.VistaMed.SelectSqlData<FactData>(sqlTextMonth); sections.ToList().ForEach(x => x.FillFactDataPeriod(ws, sqlDataMonth, columnIndexMonth)); string sqlTextYear = string.Format(sqlTextBase, yearBeginDate.SqlStr(), yearEndDate.SqlStr(), smoId, ReserveAccountName).Replace(@"a.date", @"a.settleDate"); List<FactData> sqlDataYear = dataContext.VistaMed.SelectSqlData<FactData>(sqlTextYear); sections.ToList().ForEach(x => x.FillFactDataPeriod(ws, sqlDataYear, columnIndexYear)); }
private void fillData(Worksheet ws, MonthParams args, string sqlTextBase, int smoId, DateTime monthBeginDate, DateTime monthEndDate, DateTime yearBeginDate, DateTime yearEndDate) { if (smoId == 0) sqlTextBase = sqlTextBase.Replace(@"AND (c.payer_id = {2})", " "); string sqlTextMain = sqlTextBase.Replace(@"/*CodeFilter*/", doctorServiceCodeFilter); string sqlTextMonth = string.Format(sqlTextMain, monthBeginDate.SqlStr(), monthEndDate.SqlStr(), smoId, ReserveAccountName); List<FactData> sqlDataMonth = dataContext.VistaMed.SelectSqlData<FactData>(sqlTextMonth); fillDataPeriod(ws, sqlDataMonth, columnIndexMonth, minDoctorsRowIndex, maxDoctorsRowIndex, SumType.Event); string sqlTextYear = string.Format(sqlTextMain, yearBeginDate.SqlStr(), yearEndDate.SqlStr(), smoId, ReserveAccountName).Replace(@"a.date", @"a.settleDate"); List<FactData> sqlDataYear = dataContext.VistaMed.SelectSqlData<FactData>(sqlTextYear); fillDataPeriod(ws, sqlDataYear, columnIndexYear, minDoctorsRowIndex, maxDoctorsRowIndex, SumType.Event); string sqlTextStoma = sqlTextBase.Replace(@"/*CodeFilter*/", stomaServiceCodeFilter); string sqlTextStomaMonth = string.Format(sqlTextStoma, monthBeginDate.SqlStr(), monthEndDate.SqlStr(), smoId, ReserveAccountName); List<FactData> sqlDataStomaMonth = dataContext.VistaMed.SelectSqlData<FactData>(sqlTextStomaMonth); fillDataPeriod(ws, sqlDataStomaMonth, columnIndexMonth, stomaRowRowIndex, stomaRowRowIndex, SumType.Event); fillDataPeriod(ws, sqlDataStomaMonth, columnIndexMonth, stomaUetRowIndex, stomaUetRowIndex, SumType.Uet); string sqlTextStomaYear = string.Format(sqlTextStoma, yearBeginDate.SqlStr(), yearEndDate.SqlStr(), smoId, ReserveAccountName).Replace(@"a.date", @"a.settleDate"); List<FactData> sqlDataStomaYear = dataContext.VistaMed.SelectSqlData<FactData>(sqlTextStomaYear); fillDataPeriod(ws, sqlDataStomaYear, columnIndexYear, stomaRowRowIndex, stomaRowRowIndex, SumType.Event); fillDataPeriod(ws, sqlDataStomaYear, columnIndexYear, stomaUetRowIndex, stomaUetRowIndex, SumType.Uet); string sqlTextEmergency = sqlTextBase.Replace(@"/*CodeFilter*/", emergencyServiceCodeFilter); string sqlTextEmergencyMonth = string.Format(sqlTextEmergency, monthBeginDate.SqlStr(), monthEndDate.SqlStr(), smoId, ReserveAccountName); List<FactData> sqlDataEmergencyMonth = dataContext.VistaMed.SelectSqlData<FactData>(sqlTextEmergencyMonth); fillDataPeriod(ws, sqlDataEmergencyMonth, columnIndexMonth, emergencyRowIndex, emergencyRowIndex, SumType.Event); string sqlTextEmergencyYear = string.Format(sqlTextEmergency, yearBeginDate.SqlStr(), yearEndDate.SqlStr(), smoId, ReserveAccountName).Replace(@"a.date", @"a.settleDate"); List<FactData> sqlDataEmergencyYear = dataContext.VistaMed.SelectSqlData<FactData>(sqlTextEmergencyYear); fillDataPeriod(ws, sqlDataEmergencyYear, columnIndexYear, emergencyRowIndex, emergencyRowIndex, SumType.Event); }
private void fillServiceData(Worksheet ws, MonthParams args, string sqlTextBase, int smoId, string serviceCode, int rowIndex, DateTime monthBeginDate, DateTime monthEndDate, DateTime yearBeginDate, DateTime yearEndDate) { string sqlText = sqlTextBase.Replace(@"/*CodeFilter*/", string.Format(@" AND s.code in ({0})", serviceCode)); string sqlTextMonth = string.Format(sqlText, monthBeginDate.SqlStr(), monthEndDate.SqlStr(), smoId, ReserveAccountName); List<FactData> sqlDataMonth = dataContext.VistaMed.SelectSqlData<FactData>(sqlTextMonth); string sqlTextYear = string.Format(sqlText, yearBeginDate.SqlStr(), yearEndDate.SqlStr(), smoId, ReserveAccountName).Replace(@"a.date", @"a.settleDate"); List<FactData> sqlDataYear = dataContext.VistaMed.SelectSqlData<FactData>(sqlTextYear); fillDataCell(ws, sqlDataMonth, columnIndexMonth, rowIndex, SumType.Event); fillDataCell(ws, sqlDataYear, columnIndexYear, rowIndex, SumType.Event); rowIndex++; fillDataCell(ws, sqlDataMonth, columnIndexMonth, rowIndex, SumType.Client); fillDataCell(ws, sqlDataYear, columnIndexYear, rowIndex, SumType.Client); }
protected abstract void fillCurrentData(Worksheet ws, MonthParams args, int smoId);
/// <summary> /// Текущее выполнение планов /// </summary> /// <param name="context"></param> /// <param name="args"></param> public static void FillCurrent(IBackgroundContext context, MonthParams args) { fillAccountCheckPlan(context, args, @"ОПРЕДЕЛЕНИЕ ТЕКУЩЕГО ВЫПОЛНЕНИЯ ПЛАНОВ (по данным мероприятий)", (pageType, ws, a) => pageType.FillCurrent(ws, args)); }
protected override void fillCurrentData(Worksheet ws, MonthParams args, int smoId) { string sqlTextBase = @"SELECT s.code AS ServiceCode, LEFT(s.code, 1) AS IsChild, SUM(a.amount) AS Kol, COUNT(DISTINCT e.id) AS EventCount FROM Action a JOIN ActionType aty ON aty.id = a.actionType_id AND a.deleted = 0 AND aty.name = 'Движение' AND aty.deleted = 0 JOIN Event e ON e.id = a.event_id AND e.deleted = 0 JOIN EventType et ON et.id = e.eventType_id AND et.deleted = 0 JOIN Contract c ON c.id = e.contract_id AND c.deleted=0 JOIN rbFinance f ON f.id = c.finance_id AND f.name = 'омс' JOIN ActionPropertyType apt ON apt.actionType_id = aty.id AND apt.deleted = 0 AND apt.name = 'койка' JOIN ActionProperty ap ON ap.action_id = a.id AND ap.deleted = 0 AND ap.type_id = apt.id JOIN ActionProperty_HospitalBed b ON b.id = ap.id AND b.`index` = 0 JOIN vHospitalBed hb ON hb.id = b.value JOIN rbHospitalBedProfile hbp ON hbp.id = hb.profile_id JOIN rbService s ON s.id = hbp.service_id WHERE 1=1 AND (c.payer_id = {2}) AND s.code LIKE '_96%' AND e.execDate >= '{0}' AND e.execDate < '{1}' GROUP BY s.id ORDER BY s.code"; var monthBeginDate = args.DateBegin; var monthEndDate = args.DateEnd.AddDays(1); var yearBeginDate = args.DateEnd.BeginOfYear(); var yearEndDate = args.DateEnd.AddDays(1); fillData(ws, args, sqlTextBase, smoId, monthBeginDate, monthEndDate, yearBeginDate, yearEndDate); }
protected override void fillCurrentData(Worksheet ws, MonthParams args, int smoId) { string sqlTextBase = @"SELECT spec.federalCode AS SpecCode, aty.code AS ServiceCode, LEFT(aty.code, 1) AS IsChild, SUM(a.amount) AS Kol, SUM(a.uet) AS Uet, COUNT(DISTINCT e.id) AS EventCount, IF(aty.name LIKE '%профилакт%', 0, IF(aty.name LIKE '%обращени%', 2, 1)) AS ActionKind FROM Action a JOIN ActionType aty ON aty.id = a.actionType_id AND a.deleted = 0 AND aty.deleted = 0 JOIN Event e ON e.id = a.event_id AND e.deleted = 0 JOIN Person p ON p.id = e.execPerson_id AND p.deleted = 0 JOIN rbSpeciality spec ON spec.id = p.speciality_id JOIN rbService s ON s.code = aty.code JOIN Contract c ON c.id = e.contract_id AND c.deleted=0 JOIN Contract_Tariff ct ON c.id = ct.master_id AND ct.deleted = 0 AND ct.service_id = s.id AND ct.price > 0 JOIN rbFinance f ON f.id = c.finance_id AND f.name='омс' WHERE e.execDate >= '{0}' AND e.execDate < '{1}' /*CodeFilter*/ AND (c.payer_id = {2}) GROUP BY spec.federalCode, s.code"; var monthBeginDate = args.DateBegin; var monthEndDate = args.DateEnd.AddDays(1); var yearBeginDate = args.DateEnd.BeginOfYear(); var yearEndDate = args.DateEnd.AddDays(1); fillData(ws, args, sqlTextBase, smoId, monthBeginDate, monthEndDate, yearBeginDate, yearEndDate); }
/// <summary> /// Заполнение формы контроля фактическими данными о выполнении плана /// </summary> /// <param name="context"></param> /// <param name="args"></param> public static void FillFact(IBackgroundContext context, MonthParams args) { fillAccountCheckPlan(context, args, @"ОПРЕДЕЛЕНИЕ ФАКТИЧЕСКОГО ВЫПОЛНЕНИЯ ПЛАНОВ (по данным счетов)", (pageType, ws, a) => pageType.FillFact(ws, args)); }