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