73
11. Советов Б.Я., Цехановский В.В. Информационные технологии. – М.:
Юрайт, 2017. – 272 с.
12. Советов Б.Я., Водяхо А.И., Дубенецкий В.А., Цехановский В.В.
Архитектура информационных систем. – М.: Академия, 2016. – 288 с.
13. Советов Б.Я., Цехановский В.В., Чертовской В.Д. Представление знаний в
информационных системах. – М.: Академия, 2016. – 144 с.
14. Емельянова Н.З., Партыка Т.Л., Попов И.И. Проектирование
информационных систем. – М.: Форум, 2015. – 432 с.
15. Гвоздева Т.В., Баллод Б.А. Проектирование информационных систем. –
Ростов-на-Дону: Феникс, 2013. – 512 с.
16. Соловьев И.В., Майоров А.А. Проектирование информационных систем.
– М.: Академический Проект, 2014. – 400 с.
17. Пирогов В.Ю. Информационные системы и базы данных. Организация и
проектирование. – СПб.: БХВ-Петербург, 2015. – 528 с.
18. Гинзбург В.М. Проектирование информационных систем в строительстве.
Информационное обеспечение. – М.: Издательство Ассоциации строительных
вузов, 2016. – 368 с.
19. Мезенцев К.Н. Автоматизированные информационные системы. – М.:
Академия, 2013. – 176 с.
20. Советов Б.Я., Цехановский В.В., Чертовской В.Д. Представление знаний в
информационных системах. – М.: Академия, 2014. – 144 с.
21. Мезенцев К.Н. Автоматизированные информационные системы. – М.:
Академия, 2014. – 176 с.
22. Описание BPWin 4.0. [Электронный ресурс]. Режим доступа:
http://www.interface.ru/fset.asp?Url=/ca/bpwin2.htm
74
Приложение
Программный код системы
unit Unit1;
procedure TForm1.N5Click(Sender: TObject);
begin
close;
end;
procedure TForm1.N6Click(Sender: TObject);
begin
with form1.ADOQuery1 do
begin
close;
sql.Clear;
sql.Add('select * from vid_usl');
active:=true;
end;
form2.DBGrid1.Columns[0].Title.caption:='Код';
form2.DBGrid1.Columns[1].Title.caption:='Наименование';
form2.Show;
end;
procedure TForm1.N7Click(Sender: TObject);
begin
with form1.ADOQuery2 do
begin
close;
sql.Clear;
sql.Add('select * from spc');
active:=true;
end;
form3.DBGrid1.Columns[0].Title.caption:='Код';
form3.DBGrid1.Columns[1].Title.caption:='ФИО';
form3.DBGrid1.Columns[2].Title.caption:='Должность';
form3.DBGrid1.Columns[3].Title.caption:='Кабинет';
form3.DBGrid1.Columns[4].Title.caption:='Телефон';
form3.Show;
end;
procedure TForm1.N8Click(Sender: TObject);
begin
with form1.ADOQuery4 do
begin
close;
sql.Clear;
sql.Add('select * from clients');
active:=true;
end;
form5.DBGrid1.Columns[0].Title.caption:='Код';
form5.DBGrid1.Columns[1].Title.caption:='ФИО';
form5.DBGrid1.Columns[2].Title.caption:='Адрес';
form5.DBGrid1.Columns[3].Title.caption:='Телефон';
form5.DBGrid1.Columns[4].Title.caption:='Дата рождения';
form5.Show;
end;
procedure TForm1.N9Click(Sender: TObject);
var i:integer;
begin
with form1.ADOQuery2 do
75
begin
close;
sql.Clear;
sql.Add('select * from spc');
active:=true;
end;
form6.CheckListBox1.clear;
with form1.ADOQuery6 do
begin
close;
sql.Clear;
sql.Add('select * from vrem');
open;
end;
for i:=1 to form1.ADOQuery6.RecordCount do
begin
form6.CheckListBox1.Items.Add(form1.ADOQuery6chm.AsString);
form1.ADOQuery6.Next;
end;
form6.DateTimePicker1.date:=now;
form6.Show;
end;
procedure TForm1.N10Click(Sender: TObject);
begin
with form1.ADOQuery4 do
begin
close;
sql.Clear;
sql.Add('select * from clients where code_cli>1');
active:=true;
end;
with form1.ADOQuery1 do
begin
close;
sql.Clear;
sql.Add('select * from vid_usl');
active:=true;
end;
form7.DateTimePicker1.Date:=now;
form7.CheckListBox1.Clear;
form7.Show;
end;
procedure TForm1.N11Click(Sender: TObject);
begin
with form1.ADOQuery2 do
begin
close;
sql.Clear;
sql.Add('select * from spc');
active:=true;
end;
form8.DateTimePicker1.Date:=now;
form8.Show;
end;
procedure TForm1.N12Click(Sender: TObject);
begin
form9.DateTimePicker1.Date:=now;
form9.DateTimePicker2.Date:=now;
form9.Show;
76
end;
procedure TForm1.N13Click(Sender: TObject);
begin
form10.DateTimePicker1.Date:=now;
form10.DateTimePicker2.Date:=now;
form10.Show;
end;
procedure TForm1.FormCreate(Sender: TObject);
var f:textfile; q:string;
begin
assignfile(f,'c:\uch_usl\connect.txt');
reset(f);
readln(f,q);
closefile(f);
adoconnection1.close;
form1.ADOConnection1.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=uch_usl;Data Source='+q;
adoconnection1.open;
end;
procedure TForm10.RzBitBtn1Click(Sender: TObject);
var year1,mon1,dt1:word; m1,d1:string[2]; day1,day2:string[20]; excelapp:variant; i:integer;
begin
decodedate(datetimepicker1.date,year1,mon1,dt1);
if mon1<10 then m1:='0'+inttostr(mon1) else m1:=inttostr(mon1);
if dt1<10 then d1:='0'+inttostr(dt1) else d1:=inttostr(dt1);
day1:=inttostr(year1)+'/'+m1+'/'+d1;
decodedate(datetimepicker2.date,year1,mon1,dt1);
if mon1<10 then m1:='0'+inttostr(mon1) else m1:=inttostr(mon1);
if dt1<10 then d1:='0'+inttostr(dt1) else d1:=inttostr(dt1);
day2:=inttostr(year1)+'/'+m1+'/'+d1;
with form10.ADOQuery1 do
begin
close;
sql.Clear;
sql.Add('SELECT Count(gur_prm.code) AS ccode, vid_usl.nam FROM vid_usl INNER JOIN
gur_prm ON vid_usl.code = gur_prm.code_usl where (gur_prm.code_cli>1) and
(gur_prm.day>=(convert(datetime,'+form1.label1.caption+datetostr(datetimepicker1.date)+form1.Label1.Caption+',
104))) and
(gur_prm.day<=(convert(datetime,'+form1.label1.caption+datetostr(datetimepicker2.date)+form1.Label1.Caption+',
104))) GROUP BY vid_usl.nam ');
open;
end;
ExcelApp := CreateOleObject('Excel.Application');
ExcelApp.workbooks.open('C:\uch_usl\xlt\sv_usl.xlt');
ExcelApp.Visible := False;
for i:=1 to form10.ADOQuery1.RecordCount do
begin
excelapp.cells[5+i,1]:=trim(form10.ADOQuery1nam.AsString);
excelapp.cells[5+i,2]:=trim(form10.ADOQuery1ccode.AsString);
excelapp.cells[5+i,1].borders.linestyle:=1;
excelapp.cells[5+i,2].borders.linestyle:=1;
form10.ADOQuery1.Next;
end;
excelapp.cells[3,2]:=datetostr(datetimepicker1.date)+'-'+datetostr(datetimepicker2.date);
excelapp.visible:=true;
excelapp:=unassigned;
end;
end.
77
procedure TForm3.RzBitBtn1Click(Sender: TObject);
var i:integer;
begin
with form1.ADOQuery1 do
begin
close;
sql.Clear;
sql.Add('select * from vid_usl');
open;
end;
form4.CheckListBox1.clear;
for i:=1 to form1.ADOQuery1.RecordCount do
begin
form4.CheckListBox1.Items.Add(trim(form1.ADOQuery1nam.AsString));
with form1.ADOQuery3 do
begin
close;
sql.Clear;
sql.Add('select * from spc_usl where (code_spc='+form1.ADOQuery2code_spc.AsString+') and
(code_usl='+form1.ADOQuery1code.AsString+')');
open;
end;
if form1.ADOQuery3.RecordCount>0 then form4.CheckListBox1.Checked[i-1]:=true;
form4.Show;
form1.ADOQuery1.Next;
end;
end;
end.
procedure TForm4.RzBitBtn1Click(Sender: TObject);
var i:integer;
begin
with form1.ADOQuery3 do
begin
close;
sql.Clear;
sql.Add('delete from spc_usl where code_spc='+form1.ADOQuery2code_spc.AsString);
execsql;
close;
sql.Clear;
sql.Add('select * from spc_usl');
open;
end;
for i:=1 to form4.CheckListBox1.Items.Count do
begin
if form4.CheckListBox1.Checked[i-1]=true then
begin
with form1.ADOQuery1 do
begin
close;
sql.Clear;
sql.Add('select * from vid_usl where
nam='+form1.Label1.Caption+trim(form4.CheckListBox1.Items[i-1])+form1.Label1.Caption);
open;
end;
form1.ADOQuery3.AppendRecord([form1.ADOQuery2code_spc.AsInteger,form1.ADOQuery1code.AsInteger]);
end;
end;
showmessage('Записано');
end;
78
end.
procedure TForm6.RzBitBtn3Click(Sender: TObject);
var year1,mon1,dt1:word; m1,d1:string[2]; day1:string[20]; i:integer;
begin
decodedate(datetimepicker1.date,year1,mon1,dt1);
if mon1<10 then m1:='0'+inttostr(mon1) else m1:=inttostr(mon1);
if dt1<10 then d1:='0'+inttostr(dt1) else d1:=inttostr(dt1);
day1:=inttostr(year1)+'/'+m1+'/'+d1;
for i:=1 to form6.CheckListBox1.Items.Count do
begin
with form1.ADOQuery6 do
begin
close;
sql.Clear;
sql.Add('select * from vrem where chm='+form1.Label1.Caption+trim(checklistbox1.Items[i-
1])+form1.Label1.Caption);
open;
end;
with form1.ADOQuery5 do
begin
close;
sql.Clear;
sql.Add('select * from gur_prm where
(day=(convert(datetime,'+form1.label1.caption+datetostr(datetimepicker1.date)+form1.Label1.Caption+',104))) and
(code_spc='+form1.ADOQuery2code_spc.AsString+') and (code_vr='+form1.ADOQuery6code_vr.AsString+')');
open;
end;
if form1.ADOQuery5.RecordCount>0 then form6.CheckListBox1.Checked[i-1]:=true else
form6.CheckListBox1.Checked[i-1]:=false;
end;
end;
procedure TForm6.RzBitBtn1Click(Sender: TObject);
var year1,mon1,dt1:word; m1,d1:string[2]; day1:string[20]; i,j,code:integer;
begin
with form6.ADOQuery1 do
begin
close;
sql.clear;
sql.Add('select max(code) as expr1000 from gur_prm');
open;
end;
if form6.ADOQuery1.RecordCount=0 then code:=1 else
code:=1+form6.ADOQuery1Expr1000.AsInteger;
decodedate(datetimepicker1.date,year1,mon1,dt1);
if mon1<10 then m1:='0'+inttostr(mon1) else m1:=inttostr(mon1);
if dt1<10 then d1:='0'+inttostr(dt1) else d1:=inttostr(dt1);
day1:=inttostr(year1)+'/'+m1+'/'+d1;
with form1.ADOQuery5 do
begin
close;
sql.Clear;
sql.Add('delete from gur_prm where
(day=(convert(datetime,'+form1.label1.caption+datetostr(datetimepicker1.date)+form1.Label1.Caption+',104))) and
(code_spc='+form1.ADOQuery2code_spc.AsString+')');
execsql;
close;
sql.clear;
sql.Add('select * from gur_prm');
open;
79
end;
for i:=1 to form6.CheckListBox1.Items.Count do
begin
if form6.CheckListBox1.Checked[i-1]=true then
begin
with form1.ADOQuery6 do
begin
close;
sql.Clear;
sql.add('select * from vrem where
chm='+form1.Label1.Caption+trim(form6.CheckListBox1.Items[i-1])+form1.Label1.Caption);
open;
end;
with form1.ADOQuery3 do
begin
close;
sql.Clear;
sql.Add('select * from spc_usl where code_spc='+form1.ADOQuery2code_spc.AsString);
open;
end;
for j:=1 to form1.ADOQuery3.RecordCount do
begin
form1.ADOQuery5.AppendRecord([code,datetimepicker1.Date,form1.ADOQuery2code_spc.AsInteger,form1.ADO
Query3code_usl.asinteger,1,form1.ADOQuery6code_vr.AsInteger]);
code:=code+1;
form1.ADOQuery3.Next;
end;
end;
end;
showmessage('Записано');
end;
procedure TForm7.RzBitBtn1Click(Sender: TObject);
var i:integer;
begin
form7.CheckListBox1.clear;
with form7.ADOQuery1 do
begin
close;
sql.Clear;
sql.Add('SELECT gur_prm.code, gur_prm.day, spc.fio, vrem.chm, gur_prm.code_cli,
gur_prm.code_usl FROM vrem INNER JOIN (spc INNER JOIN gur_prm ON spc.code_spc = gur_prm.code_spc)
ON vrem.code_vr = gur_prm.code_vr WHERE (((gur_prm.code_cli)=1)');
sql.Add(' and
(gur_prm.day>=(convert(datetime,'+form1.label1.caption+datetostr(datetimepicker1.date)+form1.Label1.Caption+',
104))) and (gur_prm.code_usl='+form1.ADOQuery1code.AsString+'))');
open;
end;
for i:=1 to form7.ADOQuery1.RecordCount do
begin
form7.CheckListBox1.Items.Add(form7.ADOQuery1code.AsString+';
'+form7.ADOQuery1chm.AsString+' '+trim(form7.ADOQuery1fio.AsString));
form7.ADOQuery1.Next;
end;
end;
procedure TForm7.RzBitBtn4Click(Sender: TObject);
begin
close;
end;
80
procedure TForm7.RzBitBtn2Click(Sender: TObject);
var code:string; i,j,k:integer; year1,mon1,dt1:word; m1,d1:string[2]; day1,qq:string[20]; excelapp:variant;
label q;
begin
decodedate(datetimepicker1.date,year1,mon1,dt1);
if mon1<10 then m1:='0'+inttostr(mon1) else m1:=inttostr(mon1);
if dt1<10 then d1:='0'+inttostr(dt1) else d1:=inttostr(dt1);
day1:=inttostr(year1)+'/'+m1+'/'+d1;
j:=0;
for i:=1 to form7.CheckListBox1.Items.Count do
begin
if form7.CheckListBox1.Checked[i-1]=true then j:=j+1;
end;
if j=1 then
begin
ExcelApp := CreateOleObject('Excel.Application');
ExcelApp.workbooks.open('C:\uch_usl\xlt\talon.xlt');
ExcelApp.WorkSheets[1].Activate;
ExcelApp.Visible := False;
for i:=1 to form7.CheckListBox1.Items.Count do
begin
if form7.CheckListBox1.Checked[i-1]=true then
begin
for k:=1 to length(trim(form7.CheckListBox1.Items[i-1])) do
begin
code:= trim(form7.CheckListBox1.Items[i-1]);
if code[k]=';' then
begin
code:=copy(code,1,k-1);
goto q;
end;
end;
end;
end;
q:
with form1.ADOQuery5 do
begin
close;
sql.Clear;
sql.Add('update gur_prm set code_cli='+form1.ADOQuery4code_cli.AsString+' where code='+code);
execsql;
close;
sql.Clear;
sql.Add('select * from gur_prm where code='+code);
open;
end;
qq:= form1.ADOQuery5code_vr.AsString ;
with form1.ADOQuery5 do
begin
close;
sql.Clear;
sql.Add('delete from gur_prm where (code_cli=1) and (code_vr='+qq+') and
(gur_prm.day=(convert(datetime,'+form1.label1.caption+datetostr(datetimepicker1.date)+form1.Label1.Caption+',1
04)))');
execsql;
close;
sql.Clear;
sql.Add('select * from gur_prm ');
open;
end;
81
showmessage('Записано');
rzbitbtn1.Click;
with form1.ADOQuery2 do
begin
close;
sql.Clear;
sql.Add('select * from spc where
fio='+form1.Label1.Caption+trim(form7.adoquery1fio.AsString)+form1.Label1.Caption);
open;
end;
excelapp.cells[3,3]:=form7.ADOQuery1fio.AsString;
excelapp.cells[4,3]:=datetostr(datetimepicker1.DateTime)+' '+form7.ADOQuery1chm.AsString;
excelapp.cells[5,3]:=form1.ADOQuery2cab.AsString;
excelapp.cells[6,3]:=form1.ADOQuery2tel.AsString;
excelapp.cells[9,1]:=form1.ADOQuery4fio.AsString;
excelapp.cells[10,1]:=form1.ADOQuery4adr.AsString;
excelapp.visible:=true;
excelapp:=unassigned;
end
else showmessage('Не выбрано время, либо выбрано более одной позиции');
end;
end.
procedure TForm8.RzBitBtn1Click(Sender: TObject);
var year1,mon1,dt1:word; m1,d1:string[2]; day1,qq:string[20]; excelapp:variant; i:integer;
begin
decodedate(datetimepicker1.date,year1,mon1,dt1);
if mon1<10 then m1:='0'+inttostr(mon1) else m1:=inttostr(mon1);
if dt1<10 then d1:='0'+inttostr(dt1) else d1:=inttostr(dt1);
day1:=inttostr(year1)+'/'+m1+'/'+d1;
with form8.ADOQuery1 do
begin
close;
sql.Clear;
sql.Add('SELECT gur_prm.day, clients.fio, vid_usl.nam, vrem.chm FROM vrem INNER JOIN
(vid_usl INNER JOIN (clients INNER JOIN gur_prm ON clients.code_cli = gur_prm.code_cli) ON vid_usl.code =
gur_prm.code_usl) ON vrem.code_vr = gur_prm.code_vr ');
sql.Add('where (gur_prm.code_cli>1) and
(gur_prm.day=(convert(datetime,'+form1.label1.caption+datetostr(datetimepicker1.date)+form1.Label1.Caption+',1
04))) and (gur_prm.code_spc='+form1.ADOQuery2code_spc.AsString+') order by gur_prm.code_vr');
open;
end;
ExcelApp := CreateOleObject('Excel.Application');
ExcelApp.workbooks.open('C:\uch_usl\xlt\rasp.xlt');
ExcelApp.Visible := False;
for i:=1 to form8.ADOQuery1.RecordCount do
begin
excelapp.cells[8+i,1]:=form8.ADOQuery1chm.AsString;
excelapp.cells[8+i,1].borders.linestyle:=1;
excelapp.cells[8+i,2]:=form8.ADOQuery1fio.AsString;
excelapp.cells[8+i,2].borders.linestyle:=1;
excelapp.cells[8+i,3]:=form8.ADOQuery1nam.AsString;
excelapp.cells[8+i,3].borders.linestyle:=1;
form8.ADOQuery1.Next;
end;
excelapp.cells[3,2]:=form8.ADOQuery1day.AsString;
excelapp.cells[4,1]:='ФИО специалиста: '+trim(form1.ADOQuery2fio.AsString);
excelapp.cells[5,2]:=form1.ADOQuery2dolgn.AsString;
excelapp.cells[6,2]:=form1.ADOQuery2cab.AsString;
excelapp.visible:=true;
excelapp:=unassigned;
end;
82
end.
procedure TForm9.RzBitBtn1Click(Sender: TObject);
var year1,mon1,dt1:word; m1,d1:string[2]; day1,day2:string[20]; excelapp:variant; i:integer;
begin
decodedate(datetimepicker1.date,year1,mon1,dt1);
if mon1<10 then m1:='0'+inttostr(mon1) else m1:=inttostr(mon1);
if dt1<10 then d1:='0'+inttostr(dt1) else d1:=inttostr(dt1);
day1:=inttostr(year1)+'/'+m1+'/'+d1;
decodedate(datetimepicker2.date,year1,mon1,dt1);
if mon1<10 then m1:='0'+inttostr(mon1) else m1:=inttostr(mon1);
if dt1<10 then d1:='0'+inttostr(dt1) else d1:=inttostr(dt1);
day2:=inttostr(year1)+'/'+m1+'/'+d1;
with form9.ADOQuery1 do
begin
close;
sql.Clear;
sql.Add('SELECT spc.fio, Count(gur_prm.code) AS ccode FROM spc INNER JOIN gur_prm ON
spc.code_spc = gur_prm.code_spc where (gur_prm.code_cli>1) and
(gur_prm.day>=(convert(datetime,'+form1.label1.caption+datetostr(datetimepicker1.date)+form1.Label1.Caption+',
104))) and
(gur_prm.day<=(convert(datetime,'+form1.label1.caption+datetostr(datetimepicker2.date)+form1.Label1.Caption+',
104))) GROUP BY spc.fio');
open;
end;
ExcelApp := CreateOleObject('Excel.Application');
ExcelApp.workbooks.open('C:\uch_usl\xlt\sv_spc.xlt');
ExcelApp.Visible := False;
for i:=1 to form9.ADOQuery1.RecordCount do
begin
excelapp.cells[5+i,1]:=trim(form9.ADOQuery1fio.AsString);
excelapp.cells[5+i,2]:=trim(form9.ADOQuery1ccode.AsString);
excelapp.cells[5+i,1].borders.linestyle:=1;
excelapp.cells[5+i,2].borders.linestyle:=1;
form9.ADOQuery1.Next;
end;
excelapp.cells[3,2]:=datetostr(datetimepicker1.date)+'-'+datetostr(datetimepicker2.date);
excelapp.visible:=true;
excelapp:=unassigned;
end;
end.