Пример #1
0
 private static void fillSection(SvodDdr.DdrDataContext dc, Worksheet ws, List<SourceData> sqlData, List<SourceData> usedData, int minRow, int maxRow)
 {
     for (int rowIndex = minRow; rowIndex <= maxRow; rowIndex++)
     {
         fillRow(dc, rowIndex, ws, sqlData, usedData);
     }
 }
Пример #2
0
        public static void Fill3000(SvodDdr.DdrDataContext dc)
        {
            dc.context.ReportProgress(@"Заполняем форму 3000 ...");

            var ws = ((Worksheet)dc.newDoc.Worksheets[@"стр.5"]);
            ws.Activate();

            var totalRow = new RowData();
            var 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_2'))
                    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}')
                    AND (Action.`status` != 3) AND (Action.`status` != 6)
                GROUP BY ActionType.code",
                                    dc.dateEnd.BeginOfYear().SqlStr(),
                                    dc.dateEnd.AddDays(1).SqlStr());

            var sqlData = selectSqlData<SourceData>(dc, sqlText);

            for (int rowIndex = 7; rowIndex <= 19; rowIndex++)
            {
                fillRow(dc, rowIndex, totalRow, ws, sqlData);
            }

            totalRow.FillData(ws, 20);

            dc.context.ProgressSeparator();
        }
Пример #3
0
        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);
        }
Пример #4
0
        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);
        }
Пример #5
0
        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);
        }
Пример #6
0
        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();
        }
Пример #7
0
        private static void fillServiceCodes(SvodDdr.DdrDataContext dc, Worksheet ws, int paspZdId)
        {
            var pzp = (from c in dc.dataConext.VistaMed.ActionPropertyTypes
                       where c.ActionTypeId == paspZdId && !c.Deleted select c).ToList();

            for (int rowIndex = 8; rowIndex <= 21; rowIndex++)
            {
                var row = (Range)ws.Rows[rowIndex];

                string propName = ((Range) row.Cells[1, propNameColumn]).Value2;
                propName = propName.Split('=')[0];

                var propTypeId =
                    (from p in pzp where p.Name == propName select p.Id)
                        .FirstOrDefault();

                ((Range)row.Cells[1, serviceCodeColumn]).Value2 = propTypeId;
            }
        }
Пример #8
0
        public static void Fill4000(SvodDdr.DdrDataContext dc)
        {
            dc.context.ReportProgress(@"Заполняем форму 4000 ...");

            var ws = ((Worksheet)dc.newDoc.Worksheets[@"стр.6"]);
            ws.Activate();

            var paspZdId = (from c in dc.dataConext.VistaMed.ActionTypes where c.Name == "Паспорт здоровья" select c.Id).FirstOrDefault();

            var sqlText = string.Format(@"SELECT Client.`sex` AS Sex,
                 {2} - YEAR(Client.birthDate) as ClientAge,
                 ActionPropertyType.id as PropTypeId,
                 ActionPropertyType.`idx`,
                 ActionProperty_String.`value` AS PropValue,
                 COUNT(DISTINCT Client.id) as Kol
                 FROM Client INNER JOIN EventType ON (EventType.`code` IN ('dd2013_1', 'ДДвет'))
                    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 ActionType ON (ActionType.`code` = 'др') AND (ActionType.`deleted` = 0)
                 INNER JOIN Action ON (Action.`event_id` = Event.`id`)
                    AND (Action.`actionType_id` = ActionType.`id`) AND (Action.`deleted` = 0)
                 INNER JOIN ActionProperty ON (ActionProperty.`action_id` = Action.`id`)
                    AND (ActionProperty.`deleted` = 0)
                 INNER JOIN ActionPropertyType ON (ActionPropertyType.`id` = ActionProperty.`type_id`)
                    AND (ActionPropertyType.`deleted` = 0) AND (ActionPropertyType.`actionType_id` = ActionType.`id`)
                 INNER JOIN ActionProperty_String ON ActionProperty_String.`id` = ActionProperty.`id`
                 WHERE (Event.`execDate` >= '{0}') AND (Event.`execDate` < '{1}')
                 GROUP BY 1,2,3,4,5",
                                    dc.dateEnd.BeginOfYear().SqlStr(),
                                    dc.dateEnd.AddDays(1).SqlStr(),
                                    dc.dateEnd.Year);

            var sqlData = selectSqlData<SourceData>(dc, sqlText);
            fillServiceCodes(dc, ws, paspZdId);

            for (int rowIndex = 8; rowIndex <= 21; rowIndex++)
            {
                fillRow(dc, rowIndex, ws, sqlData);
            }

            dc.context.ProgressSeparator();
        }
Пример #9
0
        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++;
            }
        }
Пример #10
0
 public void Select(List<SourceData> sourceList, SvodDdr.DdrDataContext dc)
 {
     Man.Select(sourceList, Age, dc);
     Woman.Select(sourceList, Age, dc);
 }
Пример #11
0
 public void Fill(SvodDdr.DdrDataContext dc, Worksheet ws, int rowIndex)
 {
     Man.Fill(ws, rowIndex);
     Woman.Fill(ws, rowIndex);
 }
Пример #12
0
        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();
        }
Пример #13
0
            public void Select(List<SourceData> sourceList, int age, SvodDdr.DdrDataContext dc)
            {
                Plan = (int) (from c in dc.planDd from w in c.DdDataList where w.Age == age select w)
                    .Sum( x => (Sex == 1 ? x.NotWorkMan + x.WorkMan + x.StudentMan
                        : x.NotWorkWoman + x.WorkWoman + x.StudentWoman));

                Total = (from c in sourceList where c.Age == age select c)
                    .Sum(x => (Sex == 1 ? x.TotalMen : x.TotalWomen));

                Done = (from c in sourceList where c.Age == age select c)
                    .Sum(x => (Sex == 1 ? x.ProcessedMen : x.ProcessedWomen));
            }
Пример #14
0
        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);
        }
Пример #15
0
 private static void fillDdResult(SvodDdr.DdrDataContext dc, Worksheet ws, List<SourceDataDdResult> sqlData)
 {
     for (int rowIndex = 8; rowIndex <= 15; rowIndex++)
     {
         fillRowDdResult(dc, rowIndex, ws, sqlData);
     }
 }
Пример #16
0
        public static void Fill7000(SvodDdr.DdrDataContext dc)
        {
            dc.context.ReportProgress(@"Заполняем форму 7000 ...");

            var ws = ((Worksheet)dc.newDoc.Worksheets[@"стр.13"]);
            ws.Activate();

            var selectParams = new object []{
                dc.dateEnd.BeginOfYear().SqlStr(),
                dc.dateEnd.AddDays(1).SqlStr(),
                dc.dateEnd.Year
            };

            var sqlText = string.Format(@"SELECT
                {2} - YEAR(Client.birthDate) as ClientAge,
                Client.`sex` AS Sex,
                rbResult.name AS DdResultName,
                COUNT(DISTINCT Client.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 rbResult ON Event.result_id = rbResult.id
                WHERE (Event.`execDate` >= '{0}') AND (Event.`execDate` < '{1}')
                GROUP BY 1,2,3", selectParams);

            var sqlDataDdresult = selectSqlData<SourceDataDdResult>(dc, sqlText);

            fillDdResult(dc, ws, sqlDataDdresult);

            sqlText = string.Format(@"SELECT {2} - YEAR(Client.birthDate) as ClientAge,
                Client.`sex` AS Sex,
                COUNT(DISTINCT Client.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`)
                WHERE (Event.`execDate` >= '{0}') AND (Event.`execDate` < '{1}')
                GROUP BY 1,2", selectParams);

            var sqlDataClients = selectSqlData<SourceDataClient>(dc, sqlText);

            var newDoc = dc.newDoc;

            newDoc.Names.Item("стр7001").RefersToRange.Value2 =
                (from x in sqlDataClients where x.ClientAge > 24
                     && x.ClientAge < (x.Sex == 1 ? 60 : 55) select x).Sum(x => x.Kol);

            newDoc.Names.Item("стр7002").RefersToRange.Value2 =
                (from x in sqlDataClients
                 where x.ClientAge <= 24 || x.ClientAge >= (x.Sex == 1 ? 60 : 55)
                 select x).Sum(x => x.Kol);

            newDoc.Names.Item("стр7003").RefersToRange.Value2 =
                (from x in sqlDataClients
                 where x.ClientAge <= 24
                 select x).Sum(x => x.Kol);

            sqlText = string.Format(@"SELECT
                COUNT(DISTINCT Client.id) as Kol
                FROM Client
                INNER JOIN EventType ON (EventType.`code` IN ('ДДвет'))
                    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`)
                WHERE (Event.`execDate` >= '{0}') AND (Event.`execDate` < '{1}')", selectParams);

            var sqlDataKol = selectSqlData<SourceDataKol>(dc, sqlText);

            newDoc.Names.Item("стр7004").RefersToRange.Value2 = sqlDataKol.Sum(x => x.Kol);

            sqlText = string.Format(@"SELECT
                COUNT(DISTINCT Client.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 Action ON Action.event_id = Event.id AND Action.deleted = 0
                INNER JOIN ActionType ON ActionType.id = Action.actionType_id AND ActionType.deleted = 0
                AND  ActionType.name LIKE '%мобильными медицинскими бригадами%'
                WHERE (Event.`execDate` >= '{0}') AND (Event.`execDate` < '{1}')", selectParams);

            sqlDataKol = selectSqlData<SourceDataKol>(dc, sqlText);

            newDoc.Names.Item("стр7008").RefersToRange.Value2 = sqlDataKol.Sum(x => x.Kol);

            sqlText = string.Format(@"SELECT
                COUNT( DISTINCT Client.id ) AS Kol
                FROM Client
                INNER JOIN EventType ON (EventType.`code` = 'dd2013_1')
                    AND (EventType.`deleted` = 0)
                INNER JOIN Event ON (Event.`eventType_id` = EventType.`id`) AND (Event.`deleted` = 0)
                    AND (Event.`client_id` = Client.`id`)
                WHERE (Event.`execDate` >= '{0}') AND (Event.`execDate` < '{1}')
                AND NOT EXISTS(
                  SELECT e.id FROM Event e
                  INNER JOIN EventType et ON et.id = e.eventType_id AND (et.`code` = 'dd2013_2')  AND (et.`deleted` = 0)
                  WHERE e.deleted = 0 AND e.client_id = Client.id
                  AND (e.`execDate` >= '{0}') AND (e.`execDate` < '{1}'))", selectParams);

            sqlDataKol = selectSqlData<SourceDataKol>(dc, sqlText);

            newDoc.Names.Item("стр7011").RefersToRange.Value2 = sqlDataKol.Sum(x => x.Kol);

            sqlText = string.Format(@"SELECT
                COUNT( DISTINCT Client.id ) AS Kol
                FROM Client
                INNER JOIN EventType ON (EventType.`code` LIKE 'dd2013%')
                    AND (EventType.`deleted` = 0)
                INNER JOIN Event ON (Event.`eventType_id` = EventType.`id`) AND (Event.`deleted` = 0)
                    AND (Event.`client_id` = Client.`id`)
                INNER JOIN ClientAddress ca ON ca.id = getClientRegAddressId(Client.id)
                  AND NOT(ca.freeInput LIKE 'г.%' OR ca.freeInput LIKE '% г,%')
                WHERE (Event.`execDate` >= '{0}') AND (Event.`execDate` < '{1}')", selectParams);

            sqlDataKol = selectSqlData<SourceDataKol>(dc, sqlText);

            newDoc.Names.Item("стр7012").RefersToRange.Value2 = sqlDataKol.Sum(x => x.Kol);

            dc.context.ProgressSeparator();
        }