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