1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
| procedure Aids_Spisok_VV(s, po:string; k:Word);
var
ExcelApp, Workbook, Range : Variant;
sQuery, sRaj, sRaj1, sBomj,vv, diag, sdate : String;
i, j : integer;
begin
ProcessOn;
diag:=' and diag.diag in (125, 259, 260, 120, 119, 24, 116, 117, 118, 261, '+
'121, 122, 123, 124, 126, 127, 128, 129, 130, 131, 132, 262, 258, 256)'+
' and Diag.GDU in(32,133,134,135,136,137,138,139,140,141) ';
try
// Ñîçäàíèå Excel
ExcelApp := CreateOleObject('Excel.Application');
// Îòêëþ÷àåì ðåàêöèþ Excel íà ñîáûòèÿ, ÷òîáû óñêîðèòü âûâîä èíôîðìàöèè
ExcelApp.Application.EnableEvents := false;
// Ñîçäàåì Êíèãó (Workbook)
// Åñëè çàïîëíÿåì øàáëîí, òî
Workbook := ExcelApp.WorkBooks.Add(ExtractFileDir(Application.ExeName)+
'\reports\orgmet\Vperv_2_Spisok.xls');
//Îòìåíà çàïðîñà íà ñîõðàíåíèå
ExcelApp.DisplayAlerts:=False;
Range:=ExcelApp.Workbooks[1].WorkSheets['Ëèñò1'];
i:=5;
if fStat.cbbRaj.ItemIndex<>-1 then
sBomj:=' and (lico.inos=-1 and lico.Bomj=-1 and (lico.terr=-1 or Lico.terr is null))'
else
sBomj:='';
if fStat.cbbRaj.ItemIndex<>-1 then
if fStat.cbbRaj.ItemIndex<>22 then
begin
sRaj:=' Lico.Raj='+GetCodeCbb_Raj(fStat.cbbRaj);
sRaj1:=' Spr_Uchastok.Raj<>22';
end
else
begin
sRaj:=' Lico.Raj='+GetCodeCbb_Raj(fStat.cbbRaj);
sRaj1:=' ';
end
else
sRaj:=' Lico.Raj<>22 '; //âñå áåç ÓÈÍ
if GetCodeCbb_Raj(fStat.cbbRaj)='19' then
begin
sBomj:=' and lico.Bomj=1 ';
sRaj:='';
end;
if GetCodeCbb_Raj(fStat.cbbRaj)='20' then
begin
sBomj:=' and lico.inos=1 ';
sRaj:='';
end;
if GetCodeCbb_Raj(fStat.cbbRaj)='21' then
begin
sBomj:=' and lico.terr=1 ';
sRaj:='';
end;
case k of
1:
begin
//vv:=' and vv=1 and (snat=0 or snat is null) and (posm=0 or posm is null) and Osl2.Osl=545 ' ;//âïåðâûå âûÿâëåíûå + ÂÈ×
vv:=' and vv=1 and (snat=0 or snat is null) and (posm=0 or posm is null) and Osl2.Osl=545 ' ;//âïåðâûå âûÿâëåíûå + ÂÈ×
Range.Cells[1,1]:=' Êîíòèíãåíò ÂÈ× è òóáåðêóëåç îäíîâðåìåííî#3 '+fStat.cbbRaj.text+' '+'âïåðâûå âûÿâëåííûå çà ïåðèîä ñ: '+s+' ïî: '+po;
sdate:=' Diag.data between '+'CDate('+''''+DateToStr(fStat.dtpS.Date)+''''+') And '+
'CDate('+''''+DateToStr(fStat.dtpPo.Date)+''''+') and Osl2.data between '+'CDate('+''''+DateToStr(fStat.dtpS.Date)+''''+') And '+
'CDate('+''''+DateToStr(fStat.dtpPo.Date)+''''+')';
end;
2:
begin
//vv:=' and Osl2.Osl=545 '; //÷òî áû ñ÷èòàëèñü êîíòèíãåíòû àêòèâà (1 - ñíÿò);
vv:=' and (snat=0 or snat is null) and (posm=0 or posm is null) and Osl2.Osl=545 '; //÷òî áû ñ÷èòàëèñü êîíòèíãåíòû àêòèâà (1 - ñíÿò);
Range.Cells[1,1]:=' Êîíòèíãåíò ÂÈ× è òóáåðêóëåç îäíîâðåìåííî#4 '+fStat.cbbRaj.text+' '+' íà: '+po;
sdate:=' Diag.data <=CDate('+''''+DateToStr(fStat.dtpPo.Date)+''''+')';
end;
end;
// îòáèðàåì ðàéîí
sQuery:= 'select distinct spr_raj.id, spr_raj.znach from lico inner join Spr_raj ON Lico.Raj=Spr_Raj.id where '+ sRaj +' order by Spr_Raj.Znach' ;
fDM.qrySprRaj.Close;
fDM.qrySprRaj.SQL.Clear;
fDM.qrySprRaj.SQL.Add(sQuery);
fDM.qrySprRaj.Open;
while not fDM.qrySprRaj.Eof do
begin
//Îáðèñîâêà ÿ÷åéêè
Range.Range['A'+IntToStr(i)+':M'+IntToStr(i)].Borders.LineStyle := 0;
Range.Range['B'+IntToStr(i)+':M'+IntToStr(i)].Borders.LineStyle := 0;
Range.Range['A'+IntToStr(i)+':M'+IntToStr(i)].Interior.ColorIndex:= 15;
Range.Range['A'+IntToStr(i)+':M'+IntToStr(i)].Rows[1].Font.Bold := True;
Range.Cells[i,3]:=fDM.qrySprRaj.Fields[1].AsString;
Inc(i);
// çàïðîñ ïî ðàéîíó èç òàáëèöû ó÷åò
fDM.qryStat.Close;
fDM.qryStat.SQL.Clear;
fDM.qryStat.SQL.Text:=
' SELECT Lico.id FROM (((Uchet LEFT JOIN Spr_Uchastok ON uchet.Uch=Spr_Uchastok.id)'+
' RIGHT JOIN Lico ON Lico.id=Uchet.Lico_id) INNER JOIN DIAG ON LICO.ID=DIAG.LICO_ID)INNER JOIN Osl2 ON '+
'Lico.ID = Osl2.Lico_id where Spr_Uchastok.Raj='+fDM.qrySprRaj.Fields[0].AsString + ' and '+
sdate +vv + ' and Uchet.PovodVz in (19,99) order by Lico.FIO ' ;
fDM.qryStat.Open;
j:=1;
while not fDM.qryStat.Eof do
begin
fDM.qryRep.Close;
fDM.qryRep.SQL.Clear;
fDM.qryRep.SQL.Text:=
'SELECT Lico.FIO, Lico.Nomer,Diag.Data, DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd")) '+
'AS age, Spr.Znach, Spr_1.Znach, Spr_2.Znach, Spr_3.Znach, Spr_4.Znach, Spr_Raj.Znach, Spr_5.Znach, Spr_6.Znach, Diag.Lico_id '+
'FROM ((Spr_Raj INNER JOIN Lico ON Spr_Raj.Id = Lico.Raj) LEFT JOIN Spr AS Spr_6 ON Lico.Metod = Spr_6.Id) INNER '+
'JOIN (Spr AS Spr_5 INNER JOIN (Spr AS Spr_4 RIGHT JOIN (Spr AS Spr_3 RIGHT JOIN (Spr AS Spr_2 RIGHT JOIN '+
'(Spr AS Spr_1 RIGHT JOIN (Spr INNER JOIN Diag ON Spr.Id = Diag.Diag) ON Spr_1.Id = Diag.Loc1) ON Spr_2.Id = '+
'Diag.Loc2) ON Spr_3.Id = Diag.Faza1) ON Spr_4.Id = Diag.Faza2) ON Spr_5.Id = Diag.GDU) ON Lico.ID = Diag.Lico_id '+
' where Diag.Lico_Id='+fDM.qryStat.FieldByName('Id').AsString+
' order by Lico.FIO ';
fDM.qryRep.Open;
//j:=1;
while not fDM.qryRep.Eof do
begin
if (fDM.qryRep.FieldByName('Spr_5.Znach').AsString='I A (+)') or
(fDM.qryRep.FieldByName('Spr_5.Znach').AsString='I A (-)') or
(fDM.qryRep.FieldByName('Spr_5.Znach').AsString='I Á (+)') or
(fDM.qryRep.FieldByName('Spr_5.Znach').AsString='I Á (-)') or
(fDM.qryRep.FieldByName('Spr_5.Znach').AsString='I B (+)') or
(fDM.qryRep.FieldByName('Spr_5.Znach').AsString='I B (-)') or
(fDM.qryRep.FieldByName('Spr_5.Znach').AsString='II A (+)') or
(fDM.qryRep.FieldByName('Spr_5.Znach').AsString='II A (-)') or
(fDM.qryRep.FieldByName('Spr_5.Znach').AsString='II Á (+)') or
(fDM.qryRep.FieldByName('Spr_5.Znach').AsString='II Á (-)') then
begin
Range.Cells[i,1]:=IntToStr(j);
Range.Cells[i,2]:=fDM.qryRep.FieldByName('Spr_5.Znach').AsString;
Range.Cells[i,3]:=fDM.qryRep.FieldByName('data').AsString;
Range.Cells[i,4]:=fDM.qryRep.FieldByName('Nomer').AsString;
Range.Cells[i,5]:=fDM.qryRep.FieldByName('Spr_Raj.Znach').AsString;
//íàõîäèì ó÷àñòîê---------------------------------------------------------
fDM.qryDoc.Close;
fDM.qryDoc.SQL.Clear;
fDM.qryDoc.SQL.Text:=
'SELECT Spr_Uchastok.Uchastok, Spr_Uchastok.Doctor'+
' FROM Uchet INNER JOIN '+
'Spr_Uchastok ON Uchet.Uch = Spr_Uchastok.Id where Uchet.Lico_Id='+
fDM.qryRep.FieldByName('Lico_Id').AsString+' order by DataVz desc ';
fDM.qryDoc.Open;
fDM.qryDoc.First;
Range.Cells[i,6]:=fDM.qryDoc.FieldByName('Uchastok').AsString;
Range.Cells[i,7]:=fDM.qryDoc.FieldByName('Doctor').AsString;
//------------------------------------------------------------------------
Range.Cells[i,8]:=fDM.qryRep.FieldByName('FIO').AsString;
Range.Cells[i,9]:=fDM.qryRep.FieldByName('Age').AsString;
Range.Cells[i,10]:=fDM.qryRep.FieldByName('Spr.Znach').AsString;
Range.Cells[i,11]:=fDM.qryRep.FieldByName('Spr_1.Znach').AsString+' '+
fDM.qryRep.FieldByName('Spr_2.Znach').AsString;
Range.Cells[i,12]:=fDM.qryRep.FieldByName('Spr_3.Znach').AsString+' '+
fDM.qryRep.FieldByName('Spr_4.Znach').AsString;
Range.Cells[i,13]:=fDM.qryRep.FieldByName('Spr_6.Znach').AsString;
//Îáðèñîâêà ÿ÷åéêè
Range.Range['A'+IntToStr(i)+':M'+IntToStr(i+4)].Borders.LineStyle := 1;
Range.Range['A'+IntToStr(i)+':M'+IntToStr(i+4)].Borders.Weight := 2;
Inc(i);
Inc(j);
end;
fDM.qryRep.Next;
end;
fDM.qryStat.Next;
end;
//---- -------------Áëîê è ò î ã î ïî ðàéîíó------------------------
if fDM.qryStat.RecordCount=0 then
begin
i:=i-1;
fDM.qrySprRaj.Next
end
else
begin
Range.Cells[i,3]:=' È Ò Î Ã Î: '+ fDM.qrySprRaj.Fields[1].AsString +
' '+IntToStr(fDM.qryStat.RecordCount);
//îáðèñîâêà ÿ÷åéêè
Range.Range['A'+IntToStr(i)+':M'+IntToStr(i)].Borders.LineStyle := 1;
Range.Range['A'+IntToStr(i)+':M'+IntToStr(i)].Borders.Weight :=2 ;
//Range.Range['A3:' + chr(ord('C') + 2) +IntToStr(i)].Borders.Weight :=2 ;
Inc(i);
fDM.qrySprRaj.Next;
end;
end;
// Âñåãî
fDM.qryRep.Close;
fDM.qryRep.SQL.Clear;
fDM.qryRep.SQL.Text:=
'SELECT count (*) FROM '+
'(Lico INNER JOIN Diag ON Lico.ID = Diag.Lico_id) INNER JOIN Osl2 ON '+
'Lico.ID = Osl2.Lico_id where '+sdate+ vv;
fDM.qryRep.Open;
Range.Cells[i,3]:=' ÂÑÅÃÎ: '+ fDM.qryRep.Fields[0].AsString ;
//îáðèñîâêà ÿ÷åéêè
// Range.Range['A'+IntToStr(i)+':A'+IntToStr(i)].Borders.LineStyle := 0;
// Range.Range['A'+IntToStr(i)+':A'+IntToStr(i)].Borders.LineStyle := 0;
fDM.qrySprRaj.Close;
fDM.qryRep.Close;
fDM.qryStat.Close;
fDM.qryDoc.Close;
fDM.qryGdu.Close;
// Ñîðòèðîâêà ïî ÃÄÓ----------------------------------------------------------
//Range.Range[Range.Cells[5,1],Range.Cells[i,13]].Sort(Range.Cells[5,5],1);
//ïðîñòàíîâêà íîìåðà ïî ïîðÿäêó
//for g:=1 to i-1 do
// Range.Cells[g+4,1]:=IntToStr(g);
ProcessOff;
// Äåëàåì Excel âèäèìûì
ExcelApp.Visible := true;
except
ProcessOff;
ExcelApp.ActiveWorkbook.Close;
ExcelApp.Application.Quit;
if MessageDlg('Îøèáêà ôîðìèðîâàíèÿ îò÷åòà!'+
#13+'Êîä îøèáêè 300',mtError, [mbOK], 0)= mrOk then
fDM.qryRep.Close;
end;
end; |
(
