private static void fillRow(SvodDdr.DdrDataContext dc, int rowIndex, Worksheet ws, List<SourceData> sqlData, List<SourceData> usedData)
{
var row = (Range)ws.Rows[rowIndex];
string mkbRange = ((Range)row.Cells[1, 52]).Value2;
var rowData = new RowData();
rowData.SelectData(sqlData, mkbRange, usedData, false);
rowData.Fill(ws, rowIndex);
}
private static void fillRow(SvodDdr.DdrDataContext dc, int rowIndex, RowData totalRow, Worksheet ws,
List<SourceData> sqlData)
{
var rowData = new RowData();
var row = (Range) ws.Rows[rowIndex];
string serviceCode = ((Range)row.Cells[1, serviceCodeColumn]).Value2.ToString();
rowData.Select(sqlData, serviceCode);
rowData.Fill(ws, rowIndex);
totalRow.Accumulate(rowData);
}
private static void fillRow(SvodDdr.DdrDataContext dc, int rowIndex, Worksheet ws,
List<SourceData> sqlData)
{
var row = (Range)ws.Rows[rowIndex];
string propValidValue = ((Range)row.Cells[1, propNameColumn]).Value2;
propValidValue = propValidValue.Split('=')[1];
int propTypeId = (int)((Range)row.Cells[1, serviceCodeColumn]).Value2;
var rowData = new RowData();
rowData.SelectData(sqlData, propTypeId, propValidValue);
rowData.Fill(ws, rowIndex);
}
public static void Fill6000(SvodDdr.DdrDataContext dc)
{
dc.context.ReportProgress(@"Заполняем форму 6000 ...");
var ws = ((Worksheet)dc.newDoc.Worksheets[@"стр.10_12"]);
ws.Activate();
var sqlText = string.Format(@"SELECT
{2} - YEAR(Client.birthDate) as ClientAge,
Diagnosis.`MKB` AS Mkb,
Client.`sex` AS Sex,
COUNT(DISTINCT Event.id) as Kol
FROM Client
INNER JOIN EventType ON (EventType.`code` IN ('dd2013_1','dd2013_2'))
AND (EventType.`deleted` = 0) AND (Client.`deleted` = 0)
INNER JOIN Event ON (Event.`eventType_id` = EventType.`id`) AND (Event.`deleted` = 0)
AND (Event.`client_id` = Client.`id`)
INNER JOIN Diagnostic ON (Diagnostic.`event_id` = Event.`id`)
AND (Diagnostic.`deleted` = 0)
INNER JOIN Diagnosis ON (Diagnosis.`id` = Diagnostic.`diagnosis_id`)
AND (Diagnosis.`deleted` = 0)
INNER JOIN rbDiseaseCharacter ON (rbDiseaseCharacter.`id` = Diagnostic.`character_id`)
AND (rbDiseaseCharacter.`code` = '5')
WHERE (Event.`execDate` >= '{0}') AND (Event.`execDate` < '{1}')
AND COALESCE(Diagnosis.`MKB`, '') <> ''
AND Diagnosis.`MKB` NOT LIKE 'Z%'
GROUP BY 1,2,3",
dc.dateEnd.BeginOfYear().SqlStr(),
dc.dateEnd.AddDays(1).SqlStr(),
dc.dateEnd.Year);
var sqlData = selectSqlData<SourceData>(dc, sqlText);
var usedData = new List<SourceData>();
fillSection(dc, ws, sqlData, usedData, 8, 25);
fillSection(dc, ws, sqlData, usedData, 31, 46);
fillSection(dc, ws, sqlData, usedData, 52, 68);
var totalRowData = new RowData();
totalRowData.SelectData(sqlData, "", usedData, true);
totalRowData.Fill(ws, 69);
dc.context.ProgressSeparator();
}
public static void Fill2000(SvodDdr.DdrDataContext dc)
{
dc.context.ReportProgress(@"Заполняем форму 2000 ...");
var ws = ((Worksheet)dc.newDoc.Worksheets[@"стр.4"]);
ws.Activate();
var totalRow = new RowData();
string sqlText = string.Format(@"SELECT COUNT(DISTINCT Event.client_id) as CountClients,
COUNT(DISTINCT IF(Action.MKB != '' AND Action.MKB NOT LIKE 'Z%', Event.id, NULL)) as CountEvents,
ActionType.Code AS ActionTypeCode
FROM Event
INNER JOIN EventType ON (Event.`deleted` = 0)
AND (EventType.`code` IN ('dd2013_1', 'ДДвет'))
AND (EventType.`id` = Event.`eventType_id`) AND (EventType.`deleted` = 0)
INNER JOIN Action ON (Action.`deleted` = 0) /* AND (Action.`status` != 3) */
AND (Action.`event_id` = Event.`id`)
INNER JOIN ActionType ON (ActionType.`deleted` = 0)
AND (Action.`actionType_id` = ActionType.`id`)
INNER JOIN Client ON (Client.`deleted` = 0) AND (Client.`id` = Event.`client_id`)
WHERE (Event.`execDate` >= '{0}') AND (Event.`execDate` < '{1}')
GROUP BY ActionType.Code",
dc.dateEnd.BeginOfYear().SqlStr(),
dc.dateEnd.AddDays(1).SqlStr());
var sqlData = selectSqlData<SourceData>(dc, sqlText);
for (int rowIndex = 7; rowIndex <= 28; rowIndex++)
{
fillRow(dc, rowIndex, totalRow, ws, sqlData);
}
totalRow.Fill(ws, 29);
dc.context.ProgressSeparator();
}
private static void fillSection(SvodDdr.DdrDataContext dc, Worksheet ws,
int minAge, int maxAge, int rowIndex, List<SourceData> sourceDataList, RowData itogLine)
{
foreach (var age in ddrAges.Where(x => x >= minAge && x <= maxAge))
{
var lineData = new RowData();
lineData.Age = age;
lineData.Select(sourceDataList, dc);
lineData.Fill(dc, ws, rowIndex);
itogLine.Accumulate(lineData);
rowIndex++;
}
}
public static void Fill1000(SvodDdr.DdrDataContext dc)
{
dc.context.ReportProgress(@"Заполняем форму 1000 ...");
var ws = ((Worksheet)dc.newDoc.Worksheets[@"стр.2_3"]);
ws.Activate();
var itogLine = new RowData();
var sqlText = String.Format(@"SELECT
COUNT(DISTINCT IF(ClientAttach.id, Client.id, NULL)) as TotalClients,
COUNT(DISTINCT IF(ClientAttach.id AND Client.sex = 1, Client.id, NULL)) as TotalMen,
COUNT(DISTINCT IF(ClientAttach.id AND Client.sex = 2, Client.id, NULL)) as TotalWomen,
COUNT(DISTINCT IF(Event.id AND 1, Client.id, NULL)) as ProcessedClients,
COUNT(DISTINCT IF(Event.id AND Client.sex = 1 AND 1, Client.id, NULL)) as ProcessedMen,
COUNT(DISTINCT IF(Event.id AND Client.sex = 2 AND 1, Client.id, NULL)) as ProcessedWomen,
{2} - YEAR(Client.birthDate) as age
FROM Client LEFT JOIN EventType ON (EventType.`deleted` = 0)
AND (EventType.`code` IN ('dd2013_1','dd2013_2'))
LEFT JOIN Event ON (EventType.`id` = Event.`eventType_id`) AND (Event.`client_id` = Client.`id`)
AND (Event.`deleted` = 0) AND (Event.`execDate` >= '{0}')
AND (Event.`execDate` < '{1}')
LEFT JOIN ClientAttach ON (ClientAttach.`client_id` = Client.`id`)
AND (ClientAttach.`deleted` = 0) AND (ClientAttach.`LPU_id` = {3})
AND (ClientAttach.`attachType_id` = 1)
WHERE (Client.`deleted` = 0) AND
({2} - YEAR(Client.birthDate) IN (21, 24, 27, 30, 33, 36, 39, 42,
45, 48, 51, 54, 57, 60, 63, 66, 69, 72, 75, 78, 81, 84, 87, 90, 93, 96, 99))
GROUP BY {2} - YEAR(Client.birthDate)",
dc.dateEnd.BeginOfYear().SqlStr(),
dc.dateEnd.AddDays(1).SqlStr(),
dc.dateEnd.Year,
dc.LpuId);
var sqlData = selectSqlData<SourceData>(dc, sqlText);
var ddVetSql = string.Format(@"SELECT {2} - YEAR(c.birthDate) AS Age, c.Sex AS Sex
FROM Event e
JOIN EventType et ON e.eventType_id = et.id AND e.deleted = 0 AND et.deleted = 0 AND et.code = 'ДДвет'
JOIN Client c ON e.client_id = c.id
WHERE e.execDate >= '{0}' AND e.execDate < '{1}'",
dc.dateEnd.BeginOfYear().SqlStr(),
dc.dateEnd.AddDays(1).SqlStr(),
dc.dateEnd.Year);
var vetList = selectSqlData<DdVetEventData>(dc, ddVetSql);
foreach (var idData in vetList)
{
if (!ddrAges.Contains(idData.Age))
{
var validAge = (from a in ddrAges orderby Math.Abs(a - idData.Age) select a).FirstOrDefault();
if (validAge != 0)
idData.Age = validAge;
}
}
sqlData.ForEach(x =>
{
x.ProcessedMen += vetList.Count(y => y.Age == x.Age && y.Sex == 1);
x.ProcessedWomen += vetList.Count(y => y.Age == x.Age && y.Sex == 2);
});
fillSection(dc, ws, 21, 57, 8, sqlData, itogLine);
fillSection(dc, ws, 60, 99, 26, sqlData, itogLine);
itogLine.Fill(dc, ws, 40);
dc.context.ProgressSeparator();
}
private static void fillRowDdResult(SvodDdr.DdrDataContext dc, int rowIndex, Worksheet ws,
List<SourceDataDdResult> sqlData)
{
var row = (Range)ws.Rows[rowIndex];
string grZdName = ((Range)row.Cells[1, 2]).Value2;
var rowData = new RowData();
rowData.SelectDataDdResult(sqlData, grZdName);
rowData.Fill(ws, rowIndex);
}