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);
}
}
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();
}
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();
}
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;
}
}
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();
}
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 void Select(List<SourceData> sourceList, SvodDdr.DdrDataContext dc)
{
Man.Select(sourceList, Age, dc);
Woman.Select(sourceList, Age, dc);
}
public void Fill(SvodDdr.DdrDataContext dc, Worksheet ws, int rowIndex)
{
Man.Fill(ws, rowIndex);
Woman.Fill(ws, 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();
}
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));
}
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);
}
private static void fillDdResult(SvodDdr.DdrDataContext dc, Worksheet ws, List<SourceDataDdResult> sqlData)
{
for (int rowIndex = 8; rowIndex <= 15; rowIndex++)
{
fillRowDdResult(dc, rowIndex, ws, sqlData);
}
}
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();
}