ЛАБОРАТОРНАЯ РАБОТА № 11. Тема: Создание формы для просмотра остатков продуктов на складе
Тема: Создание формы для просмотра остатков продуктов на складе
Цель: Научиться работать с формой и создавать SQL-запросы
Оборудование и/или программное обеспечение: ПК, Delphi
Теоретическая часть
Оператор выбора Select
Отбор записей из таблицы
В этом разделе мы познакомимся с наиболее часто используемым оператором SQL — оператором выбора Select. Этот оператор возвращает одно или множество значений, которые могут представлять собой значения указанных полей записей, удовлетворяющих заданному условию и упорядоченных по заданному критерию.
Хотя мы еще не рассматривали компонент Delphi Query, предназначенный для работы с SQL, но при знакомстве с оператором Select полезно сразу пробовать записывать его в различных вариантах и с помощью компонента Query смотреть получающиеся результаты.
Поэтому откройте новое приложение Delphi, перенесите на форму компонент Query со страницы библиотеки Data Access и установите его свойство DatabaseName равным ib (или равным dbP при использовании базы данных Paradox). Поместите на форму компонент DataSource и в его свойстве DataSet задайте Query 1. Поместите также на форму компонент DBGrid и в его свойстве DataSource задайте DataSourcel.
Теперь ваше тестовое приложение для экспериментов с языком SQL готово. Операторы SQL вы можете писать в свойстве SQL компонента Query 1, а чтобы увидеть результаты выполнения написанного оператора, вам надо будет устанавливать значение свойства Active компонента Queryl в true. Это надо будет делать после записи каждого нового оператора.
Теперь начнем рассмотрение оператора Select. Одна из форм этого оператора имеет синтаксис:
SELECT <список имен полей> FROM <таблица>
WHERE <условие отбора> ORDER BY <список имен полей>;
Элементы оператора WHERE и ORDER BY не являются обязательными. Элемент WHERE определяет условие отбора записей: отбираются только те, в которых условие выполняется. Элемент ORDER BY определяет упорядочивание возвращаемых записей.
<таблица> — это та таблица базы данных, из которой осуществляется отбор, например, Pers.
Начнем подробное рассмотрение данного оператора со списка полей после ключевого слова Select, содержащего имена тех полей таблицы, которые будут возвращены. Имена разделяются запятыми. Например, оператор
SELECT Fam, Nam, Par, Year_b FROM Pers указывает, что следует вернуть поля Fam, Nam, Par и Year_b из таблицы Pers. Запишите его в свойстве SQL компонента Queryl, установите значение свойства Active компонента Queryl в true и посмотрите результаты.
Если указать вместо списка полей символ «*» — это будет означать, что требуется вернуть все поля. Например, оператор
SELECT * FROM Pers означает выбор всех полей.
В списке могут быть не только сами поля, но и любые выражения от них с арифметическими операциями +, —, *, /. После выражения может записываться псевдоним выражения в форме: AS <псевдоним>. В качестве псевдонима может фигурировать любой идентификатор, на который потом можно будет при необходимости ссылаться. Указанный псевдоним будет при отображении результатов фигурировать в заголовке таблицы. Приведем пример использования выражения: SELECT Fam, Nam, (2000-Year_b) AS Age FROM Pers
Этот оператор создает поле Age, вычисляемое по формате (2000-YearJb).
При работе с некоторыми типами баз данных вы можете написать псевдоним по-русски. Например, если бы вы работали с базой данных dbP (база данных Paradox), то вы могли бы изменить предыдущий оператор следующим образом:
SELECT Fam AS Фамилия, Nam AS Имя,
(2000-Year_b) AS Возраст FROM Pers
В этом случае вы увидели бы, что в заголовках таблицы появились осмысленные русские заголовки:
| Фамилия | Имя | Возраст |
| Иванов | Иван | |
| Петров | Петр | |
| 1- | • • • | ... |
При работе с базами данных InterBase использование русских псевдонимов невозможно. Впрочем, позднее, при рассмотрении компонента Query вы увидите, что эффекта получения русских заголовков можно добиться и другими способами, не вводя русских обозначений в оператор Select.
В выражениях для полей могут использоваться строковые константы и операция «||», означающая сцепление строк. Например, оператор
SELECT "Отдел: " | I Dep, "Фамилия: " I I Fam FROM Pers будет выдавать строки вида:
Отдел: Бухгалтерия Фамилия: Иванов
Если желательно в подобном виде выдавать значения полей, содержащих не символы, а, например, числа, то можно воспользоваться функцией Cast, приводящей поле к указанному в ней типу. Например, в операторе
SELECT "Отдел: " II Dep, "Фамилия: " || Fam,
"г.р.: " II Cast(Year_b AS CHAR(4)) FROM Pers функция Cast преобразует значение числового поля Yearjb в строку из четырех символов. Результат будет выдаваться строками вида:
Отдел: Бухгалтерия Фамилия: Иванов г.р.: 1950
Практическая часть
На этом уроке мы дополним нашу базу еще двумя таблицами для учета информации о расходе. Создадим SQL запросы к базе для формирования остатков продуктов на складе и создадим форму для просмотра остатков продуктов по этим запросам.
Прежде чем приступить к созданию формы, необходимо добавить в нашу базу две таблицы первая для хранения информации о расходных накладных (назовем ее rashod_doc, она является аналогом таблицы prihod) и вторая для хранения информации о расходе продуктов по расходной накладной (назовем ееrashod, является аналогом таблицы storage). Откроем нашу базу storage.mdb в MS Access 2007 и создадим таблицу с именем rashod_doc.
Далее создаем следующие поля таблицы rashod_doc:
id (счетчик) – нужно задать как ключевое,
number_docum (текстовый, в свойствах поля- размер поля- 20) – номер расходной накладной
date_rashoda (тип данных - Дата/время) – дата расхода,
sum (тип данных -числовой, в свойствах поля- размер поля- Одинарное с плавающей точкой) – сумма расхода.
Затем создаем таблицу rashodсо следующими полями:
id (счетчик) – нужно задать как ключевое,
id_product (тип данных –числовой, в свойствах поля- размер поля- Длинное целое) – код продукта,
id_product_group (тип данных –числовой, в свойствах поля- размер поля- Длинное целое) – код группы продуктов,
quantity (тип данных -числовой, в свойствах поля- размер поля- Одинарное с плавающей точкой) – количество,
price (тип данных -числовой, в свойствах поля- размер поля- Одинарное с плавающей точкой) – цена,
id_rashod_doc (тип данных –числовой, в свойствах поля- размер поля- Длинное целое) – код расхода.
id_ed_izmer (тип данных –числовой, в свойствах поля- размер поля- Длинное целое) – код единицы измерения.
Сейчас немножко о том, как это все будет работать. Для того, чтобы посмотреть остатки продуктов на складе мы создадим три запроса к нашей базе. Первый запрос будет группировать продукты из таблицы storage по полям id_product и id_ed_izmer с одновременным суммированием по количеству (поле quantity) и сумме (поле price* quantity). Второй запрос будет делать тоже, что и первый, но по таблице rashod. А третий запрос будет находить разницу между первым и вторым запросом, и выводить эти данные в нашу форму.
И так приступим к созданию формы. Запускает наш проект, и создаем новую форму File->New->Form –Delphi
В инспекторе объектов устанавливаем следующие свойства для новой формы
Caption -> Остатки на складе;
FormStyle -> fsMDIChild;
Name -> Form_ostatki;
Сохраните модуль с именем ostatki.
Пропишем Uses general, unit_dm; после раздела implementation в программном модуле unit ostatki.
А программном модуле unit general главной формы пропишем Uses ostatki;
Переходим в дата модуль (unit_dm) и размещаем там три компонента TADOQuery из вкладки dbGo (ADO) и один компонент TDataSource из вкладки Data Access.
Для ADOQuery1 в инспекторе объектов устанавливаем следующие свойства:
Connection-> Form_general.ADOConnection1;
Name-> ADOQuery_prihod;
SQL-> (TStrings) -> вставляем следующий запрос
| SELECT storage.id_product, Sum([storage].[price]*[storage].[quantity]) AS sum_price, Sum(storage.quantity) AS [Sum-quantity], storage.id_ed_izmer FROM storage GROUP BY storage.id_product, storage.id_ed_izmer; |
Свойство Active устанавливаем в True;
Для ADOQuery2 в инспекторе объектов устанавливаем следующие свойства:
Connection-> Form_general.ADOConnection1;
Name-> ADOQuery_rashod;
SQL-> (TStrings) -> вставляем следующий запрос
| SELECT rashod.id_product, Sum([rashod].[price]*[rashod].[quantity]) AS sum_price, Sum(rashod.quantity) AS [Sum-quantity], rashod.id_ed_izmer FROM rashod GROUP BY rashod.id_product, rashod.id_ed_izmer; |
Свойство Active устанавливаем в True;
Для ADOQuery3 в инспекторе объектов устанавливаем следующие свойства:
Connection-> Form_general.ADOConnection1;
Name-> ADOQuery_ostatki;
SQL-> (TStrings) ->вставляем следующий запрос
| SELECT products.product_name, ADOQuery_prihod.id_product, [ADOQuery_prihod].[Sum-quantity]-IIf([ADOQuery_rashod].[Sum-quantity]>0,[ADOQuery_rashod].[Sum-quantity],0) AS ostatok, [ADOQuery_prihod].[sum_price]-IIf([ADOQuery_rashod].[sum_price]>0,[ADOQuery_rashod].[sum_price],0) AS summa, ADOQuery_prihod.id_ed_izmer, products.id_product_group, ed_izmer.ed_name FROM ((ADOQuery_prihod LEFT JOIN ADOQuery_rashod ON ADOQuery_prihod.id_product = ADOQuery_rashod.id_product) LEFT JOIN ed_izmer ON ADOQuery_prihod.id_ed_izmer = ed_izmer.id) LEFT JOIN products ON ADOQuery_prihod.id_product = products.id; |
Свойство Active устанавливаем в True;
Для составления запросов я использовал конструктор запросов в MS Access 2007, затем затем переключался в режимSQL и копировал запрос из в наш проект в Delphi. Созданные запросы в режиме конструктора MS Access 2007 можно посмотреть в исходниках к этому уроку в файле storage.mdb. Они называются ADOQuery_ostatki, ADOQuery_prihod, ADOQuery_rashod. Для просмотра запроса в режиме конструктора в MS Access 2007 нажмите правую кнопку мыши на этом запросе и выберите конструктор.
Внимание в этом уроке была обнаружена ошибка (смотрите комментарии 3-6) запросы ADOQuery_prihod, ADOQuery_rashod должны быть созданы в самой базе. Для их создания откройте базу storage.mdbв MS Accessвыберите создание -> конструктор запросов, закройте добавление таблиц и щелкнув правой кнопкой мыши выберите режим SQL. Далее скопируйте туда текст соответствующего запроса и сохраните его под соответствующим именем. К сожалению другого варианта исправления ошибки пока предложить не могу. Добавленные в Unit_dm компоненты ADOQuery_prihod и ADOQuery_rashod нужно удалить.
Для компонента DataSource1 устанавливаем свойства:
DataSet->ADOQuery_ostatki;
Name->ostatki.
Далее переходим к нашей форме ostatki и размещаем на ней из вкладки Data Controls компонент TDBGrid.
Для него устанавливаем следующие свойства:
Align-alClient;
DataSource->dm.ostatki;
Options->dgRowSelect->True.
Сейчас мы выберем нужные нам поля из запроса ADOQuery_ostatki для отображения их в форме. Для этого перейдем в дата модуль (unit_dm) проекта и выберем компонент ADOQuery_ostatki.
В структуре (Structure) -> ADOQuery_ostatki -> Fields -> щелкнем правой кнопкой мыши и выберем Add all fields.
Выбираем поле product_name и в инспекторе объектов устанавливаем свойство:
DisplayLabel -> Продукт;
DisplayWidth -> 30;
Выбираем поле ed_name и в инспекторе объектов устанавливаем свойство:
DisplayLabel -> Ед.изм.;
DisplayWidth -> 10;
Выбираем поле id_product и в инспекторе объектов устанавливаем свойство:
Visible->False;
Выбираем поле ostatok и в инспекторе объектов устанавливаем свойство:
DisplayFormat ->.###
DisplayLabel -> Остаток;
DisplayWidth -> 10;
Выбираем поле summa и в инспекторе объектов устанавливаем свойство:
DisplayFormat ->.##
DisplayLabel ->Сумма;
DisplayWidth -> 10;
Выбираем поле id_ed_izmer и в инспекторе объектов устанавливаем свойство:
Visible->False;
Выбираем поле id_product_group и в инспекторе объектов устанавливаем свойство:
Visible->False;
Далее создаем вычисляемое поле sred_price для вычисления средней стоимости продуктов» .
Для этого в структуре (Structure) -> products -> Fields -> щелкнем правой кнопкой мыши и выберем New field….
и заполняем
Name->sred_price;
Type->Float;
Calculated;
жмем ОК.
Выбираем созданное поле (sred_price) и в инспекторе объектов устанавливаем свойство:
DisplayFormat ->.##
DisplayLabel ->Средняя цена;
DisplayWidth -> 10;
переходим на вкладку Events и дважды жмем мышь на событии OnCalcFields;
В открывшемся обработчике событий пишем следующий код:
| procedure Tdm.ADOQuery_ostatkiCalcFields(DataSet: TDataSet); begin //если остатки продуктов не равны 0 то вычисляем среднюю стоимость продукта if ADOQuery_ostatki.FieldByName('ostatok').Value<>0 then ADOQuery_ostatki.FieldByName('sred_price').Value:= ADOQuery_ostatki.FieldByName('summa').Value/ ADOQuery_ostatki.FieldByName('ostatok').Value else ADOQuery_ostatki.FieldByName('sred_price').Value:=0; end; |
Возвращаемся снова к нашей форме ostatki и в обработчике событий для события OnActivate пишем следующий код:
| procedure TForm_ostatki.FormActivate(Sender: TObject); begin // переоткрываем запросы при активации формы //тем самым обновляем данные на форме dm.ADOQuery_ostatki.Active:=false; dm.ADOQuery_ostatki.Active:=true; end; |
Для событияOnClose:
| procedure TForm_ostatki.FormClose(Sender: TObject; var Action: TCloseAction); begin //закрываем запросы и закрываем форму dm.ADOQuery_ostatki.Active:=false; Action:=cafree; end; |
Сейчас сделаем запуск формы «Остатки на складе»из главной формы. Для этого выбираем в меню Project -> Options…
ВыбираемForms и перемещаем Form_ostatki изAuto-create forms в Avalable forms.
Переходим на главную форму, щелкаем мышкой по компоненту MainMenu1, а затем по пунктуПросмотр склада
И пишем следующий обработчик события:
| procedure TForm_general.N9Click(Sender: TObject); begin f:=0; //проверяем, активна ли наша форма if ActiveMDIChild.Caption<>'Остатки на складе' then begin //если нет то ищем ее среди неактивных и если находим, то показываем ее for i:=0 to form_general.MDIChildCount-1 do if form_general.MDIChildren[i].Caption='Остатки на складе' then begin MDIChildren[i].Show;f:=1;end; end else f:=1; //если форма еще не создана, то создаем ее if f<>1 then Tform_ostatki.Create(Application); end; |
Вот и все готово, сохраняем и запускаем проект.
Вопросы для контроля
1. Напишите синтаксис оператора Select.
2. Что происходит при выполнении данного кода:
procedure TForm_general.N9Click(Sender: TObject);
begin
f:=0;
if ActiveMDIChild.Caption<>'Остатки на складе' then begin
for i:=0 to form_general.MDIChildCount-1 do
if form_general.MDIChildren[i].Caption='Остатки на складе' then begin MDIChildren[i].Show;f:=1;end;
end
else f:=1;
if f<>1 then Tform_ostatki.Create(Application);
end;
3. Что происходит при выполнении данного кода:
procedure TForm_ostatki.FormClose(Sender: TObject; var Action: TCloseAction);
begin
dm.ADOQuery_ostatki.Active:=false;
Action:=cafree;
end;
4. Что происходит в данном запросе:
SELECT products.product_name, ADOQuery_prihod.id_product, [ADOQuery_prihod].[Sum-quantity]-IIf([ADOQuery_rashod].[Sum-quantity]>0,[ADOQuery_rashod].[Sum-quantity],0) AS ostatok, [ADOQuery_prihod].[sum_price]-IIf([ADOQuery_rashod].[sum_price]>0,[ADOQuery_rashod].[sum_price],0) AS summa, ADOQuery_prihod.id_ed_izmer, products.id_product_group, ed_izmer.ed_name
FROM ((ADOQuery_prihod LEFT JOIN ADOQuery_rashod ON ADOQuery_prihod.id_product = ADOQuery_rashod.id_product) LEFT JOIN ed_izmer ON ADOQuery_prihod.id_ed_izmer = ed_izmer.id) LEFT JOIN products ON ADOQuery_prihod.id_product = products.id;
5. Что происходит в запросе:
SELECT storage.id_product, Sum([storage].[price]*[storage].[quantity]) AS sum_price, Sum(storage.quantity) AS [Sum-quantity], storage.id_ed_izmer
FROM storage
GROUP BY storage.id_product, storage.id_ed_izmer;
Литература
1. Фаронов В.В. Программирование на языке высокого уровня: Учебник для вузов. -СПб.: Питер, 2003.
2. Бобровский С.И. Delphi 7. Учебный курс. - СПб.: Питер, 2005.
3. Кетков Ю.Л., Кетков А.Ю. Практика программирования: Visual Basic, C++ Builder, Delphi. - СПб.: БХВ - Петербург, 2005.