 |
Список используемых источников
|
|
|
|
1. Мартин Груббер, "Понимание SQL", Москва, 2003 г
2. Т. Карпова - Базы данных: модели, разработка, реализация. Питер, 2001
3. Курс лекций по разработке баз данных, Терлецкая А.М., 2007 г
Приложения
Приложение А Концептуальная модель данных
 |
Приложение Б. Листинг программного кода
Unit1
procedure TForm1. TabControl1Change (Sender: TObject);
begin
case Tabcontrol1. TabIndex of
0: // управление вкладками
begin
DBGrid1. Visible: =true;
DBNavigator1. Visible: =true;
DBGrid2. Visible: =false;
DBNavigator2. Visible: =false;
DBGrid3. Visible: =false;
DBNavigator3. Visible: =false;
DBGrid4. Visible: =false;
ComboBox1. Visible: =false;
end;
1:
begin
DBGrid1. Visible: =false;
DBNavigator1. Visible: =false;
DBGrid2. Visible: =true;
DBNavigator2. Visible: =true;
DBGrid3. Visible: =false;
DBNavigator3. Visible: =false;
DBGrid4. Visible: =false;
ComboBox1. Visible: =false;
end;
2:
begin
DBGrid1. Visible: =false;
DBNavigator1. Visible: =false;
DBGrid2. Visible: =false;
DBNavigator2. Visible: =false;
DBGrid3. Visible: =true;
DBNavigator3. Visible: =true;
DBGrid4. Visible: =false;
ComboBox1. Visible: =false;
end;
3:
begin
DBGrid1. Visible: =false;
DBNavigator1. Visible: =false;
DBGrid2. Visible: =false;
DBNavigator2. Visible: =false;
DBGrid3. Visible: =false;
DBNavigator3. Visible: =false;
DBGrid4. Visible: =true;
ComboBox1. Visible: =true;
end;
end;
end;
procedure TForm1. Exit1Click (Sender: TObject);
begin
Close; закрытие программы
end;
procedure TForm1.comboBox1Change (Sender: TObject);
var
town: string;
prod: string;
price: string;
aktiv: string;
begin
case ComboBox1. ItemIndex of
0: // первый запрос
begin
ADOQuery1. Active: = false;
aktiv: =InputBox ('Введите','Вид деятельности предприятия',aktiv);
aktiv: =Edit1. Text+aktiv+Edit1. Text;
if aktiv<>'' then
ADOQuery1. SQL. Text: = 'select * from Organizations where Activity='+aktiv;
ADOQuery1. Active: = true;
// ------------------report----------------------------------------------
N2. Enabled: =true;
Form3. QRLabel2. Caption: ='Организации';
Form3. QRLabel4. Caption: ='Адрес';
Form3. QRLabel5. Caption: ='Деятельность';
Form3. QRLabel6. Caption: ='Телефон';
Form3. QRLabel7. Caption: ='E-mail';
end;
1: // второй запрос
begin
ADOQuery1. Active: = false;
price: =InputBox ('Введите','Цена за единицу продукции',price);
if (price<>'') or (StrToInt (price) >=1000) then
ADOQuery1. SQL. Text: = 'select * from Productions where Price_for_one>='+price+' order by Production_name';
|
|
|
ADOQuery1. Active: = true;
N2. Enabled: =false;
end;
2: // третий запрос
begin
ADOQuery1. Active: = false;
prod: =InputBox ('Введите','Наименование продукции',prod);
prod: =Edit1. Text+prod+Edit1. Text;
if prod<>'' then
aktiv: =InputBox ('Введите','Количество проданной продукции',aktiv);
if (aktiv<>'') or (StrToInt (aktiv) >0) then
ADOQuery1. SQL. Text: = 'select * from Sale where Production_name='+prod+' and number>='+aktiv;
ADOQuery1. Active: = true;
N2. Enabled: =false;
end;
3: // четвертый запрос
begin
ADOQuery1. Active: = false;
prod: =InputBox ('Введите','Наименование продукции',prod);
prod: =Edit1. Text+prod+Edit1. Text;
if prod<>'' then
ADOQuery1. SQL. Text: = 'select Production_name, Sum (number) as Number_saled from Sale where Production_name='+prod+' group by Production_name';
ADOQuery1. Active: = true;
N2. Enabled: =false;
end;
4: // пятый запрос
begin
ADOQuery1. Active: = false;
town: =InputBox ('Введите','Название города',town);
town: =Edit1. Text+town+Edit1. Text;
if town<>'' then
aktiv: =InputBox ('Введите','Вид деятельности предприятия',aktiv);
aktiv: =Edit1. Text+aktiv+Edit1. Text;
if aktiv<>'' then
ADOQuery1. SQL. Text: = 'select * from Organizations where (Address='+town+') and Activity='+aktiv;
ADOQuery1. Active: = true;
// -------------------------------отчет--------------------------
N2. Enabled: =true;
Form3. QRLabel2. Caption: ='Организации';
Form3. QRLabel4. Caption: ='Адрес';
Form3. QRLabel5. Caption: ='Деятельность';
Form3. QRLabel6. Caption: ='Телефон';
Form3. QRLabel7. Caption: ='E-mail';
end;
5: // шестой запрос
begin
ADOQuery1. Active: = false;
aktiv: =InputBox ('Введите','Количество прод. продукции за одну заявку >=',aktiv);
if aktiv<>'' then
ADOQuery1. SQL. Text: = 'select * from Sale where number>='+aktiv;
ADOQuery1. Active: = true;
N2. Enabled: =false;
end;
6: // седьмой запрос
begin
ADOQuery1. Active: = false;
ADOQuery1. SQL. Text: = 'select Production_name, MAX (number) as MAX from Sale group by Production_name';
ADOQuery1. Active: = true;
// ---------------------------отчет------------------------------------
N2. Enabled: =true;
Form3. QRLabel2. Caption: ='';
Form3. QRLabel4. Caption: ='Продукция';
Form3. QRLabel5. Caption: ='МАХ';
Form3. QRLabel6. Caption: ='';
Form3. QRLabel7. Caption: ='';
end;
7: // восьмой запрос
begin
ADOQuery1. Active: = false;
ADOQuery1. SQL. Text: = 'select distinct Organization_name,Production_name, MAX (number) as MAX from Sale group by Production_name,Organization_name';
ADOQuery1. Active: = true;
// ----------------------------------отчет--------------------------
N2. Enabled: =true;
Form3. QRLabel2. Caption: ='Организации';
Form3. QRLabel4. Caption: ='Продукция';
Form3. QRLabel5. Caption: ='МАХ';
Form3. QRLabel6. Caption: ='';
Form3. QRLabel7. Caption: ='';
|
|
|
end;
8:
begin
Form2. Visible: =true; // создать запрос
end;
end;
end;
procedure TForm1. N6Click (Sender: TObject);
begin
Form2. Visible: =true; // вызов окна создания запросов
end;
procedure TForm1. DataBase1Click (Sender: TObject);
begin
winhelp (Form1. Handle,'Справка. hlp',HELP_Context,1);
end;
procedure TForm1. N2Click (Sender: TObject);
begin
Form3. QuickRep1. Preview; // вызов отчета
end;
end.
Unit 2.
// -----------------------------------создание запросов-------------------------------
procedure TForm2. Button1Click (Sender: TObject);
var
name, sel, from, where: string;
having, group, order: string;
num: integer;
begin
if (Edit1. Text='') or (Edit2. Text='') or (Edit3. Text='') then
ShowMessage ('Заполните поля, отмеченные звездочкой! ')
else
begin
name: =Edit1. Text;
sel: =Edit2. Text;
from: =Edit3. Text;
where: =Edit4. Text;
group: =Edit5. Text;
order: =Edit6. Text;
having: =Edit7. Text;
Form1. ADOQuery1. Active: = false;
if (where='') and (group='') and (order='') then
Form1. ADOQuery1. SQL. Text: = 'select '+sel+' from '+from
else
if (group='') and (order='') then
begin
Form1. ADOQuery1. SQL. Text: = 'select '+sel+' from '+from+' where '+where;
end
else
if (having='') and (order='') then
begin
Form1. ADOQuery1. SQL. Text: = 'select '+sel+' from '+from+' where '+where+
' group by '+group;
end
else
if order='' then
begin
Form1. ADOQuery1. SQL. Text: = 'select '+sel+' from '+from+' where '+where+
' group by '+group+' having '+having;
end
else
begin
Form1. ADOQuery1. SQL. Text: = 'select '+sel+' from '+from+' where '+where+
' group by '+group+' having '+having+' order by '+order;
end;
Form1. ADOQuery1. Active: = true;
Form2. Visible: =false;
end;
end;
// ---------------------------------------------------------------------
procedure TForm2. Edit5Change (Sender: TObject);
begin
Memo1. Lines [8]: =' Group by '+Edit5. Text;
if Edit5. Text<>'' then
begin
Label7. Enabled: =true;
Edit7. Enabled: =true;
end
else
begin
Label7. Enabled: =false;
Edit7. Enabled: =false;
end;
end;
procedure TForm2. Edit2Change (Sender: TObject); // управление Мемо
begin
Memo1. Lines [2]: =' Select '+Edit2. Text;
end;
procedure TForm2. Edit3Change (Sender: TObject);
begin
Memo1. Lines [4]: =' From '+Edit3. Text;
end;
procedure TForm2. Edit4Change (Sender: TObject);
begin
Memo1. Lines [6]: =' Where '+Edit4. Text;
end;
procedure TForm2. Edit7Change (Sender: TObject);
begin
Memo1. Lines [10]: =' Having '+Edit7. Text;
end;
procedure TForm2. Edit6Change (Sender: TObject);
begin
Memo1. Lines [12]: =' Order by '+Edit6. Text;
end;
procedure TForm2. Button2Click (Sender: TObject);
begin
Form2. Visible: =false;
end;
procedure TForm2. Edit1Change (Sender: TObject);
begin
Memo1. Lines [0]: ='Запрос - '+Edit1. Text;
end;
end.
|
|
|
