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