Таблица 3.12 - Состав таблицы «appointments_log»
Наименование поляФормат поляСодержимое поляidчисловой, счетчикуникальный код записи журналаoperтекстовыйпроизведенная операцияt_userтекстовыйпользователь, выполнивший действиеdateдата/время дата совершения действияanumчисловойкод измененной записиaworkerчисловойкод сотрудникаapositionчисловойкод должностиastartдата/времядата назначения на должностьaendдата/времядата ухода с должности
SQL-текст создания таблиц и описания ограничений:
--Создание таблицTABLE Отделы (Код int IDENTITY Primary key, Название varchar(50) not null)TABLE Сотрудники (Код int IDENTITY Primary key, ФИО varchar(200), Дата_рождения datetime, Код_отдела int not null)TABLE Должности (Код int IDENTITY Primary key, Название varchar(50) not null, Оклад money not null)TABLE Назначения (Код int IDENTITY Primary key, Код_должности int, Код_сотрудника int, Дата_назначения datetime, Дата_ухода datetime)TABLE Даты (Код int IDENTITY Primary key, Дата datetime not null, Колличество_часов int not null)TABLE Табель (Код int IDENTITY Primary key, Код_назначения int, Часы int, Код_даты int)TABLE Виды_начислений (Код int IDENTITY Primary key, Название varchar(50), Величина int not null, Тип varchar(1) not null default 'О')TABLE Виды_удержаний (Код int IDENTITY Primary key, Название varchar(50), Величина int not null, Тип varchar(1) not null default 'О')TABLE Начисления (Код int IDENTITY Primary key, Код_вида_начисления int, Код_табеля int, Коэффициент float)TABLE Удержания (Код int IDENTITY Primary key, Код_вида_удержания int, Код_табеля int, Коэффициент float)
-Определение связей между таблицами TABLE Сотрудники ADD CONSTRAINT FK_Сотрудники_Отделы FOREIGN KEY (Код_отдела) REFERENCES ОтделыTABLE Назначения ADD CONSTRAINT FK_Назначения_Сотрудники FOREIGN KEY (Код_сотрудника) REFERENCES СотрудникиTABLE Назначения ADD CONSTRAINT FK_Назначения_Должности FOREIGN KEY (Код_должности) REFERENCES ДолжностиTABLE Табель ADD CONSTRAINT FK_Табель_Назначения FOREIGN KEY (Код_назначения) REFERENCES НазначенияTABLE Табель ADD CONSTRAINT FK_Табель_Даты FOREIGN KEY (Код_даты) REFERENCES ДатыTABLE Начисления ADD CONSTRAINT FK_Начисления_Табель FOREIGN KEY (Код_табеля) REFERENCES ТабельTABLE Удержания ADD CONSTRAINT FK_Удержания_Табель FOREIGN KEY (Код_табеля) REFERENCES ТабельTABLE Удержания ADD CONSTRAINT FK_Удержания_Виды_удержаний FOREIGN KEY (Код_вида_удержания) REFERENCES Виды_удержаний
-Журнал измененийtable appointments_log (id int identity primary key, oper char, t_user varchar(50), date datetime, anum int, aworker int, aposition int, astart datetime, aend datetime)
4. Схема диалога пользователя с приложением
4.1 Корректировка таблиц-справочников
Для того чтобы перейти к корректировке справочников, необходимо нажать на соответствующую кнопку в главном окне или зайти в пункт меню «Ведение» и выбрать в нем необходимый справочник. Меню представлено на рисунке 4.1.
Рисунок 4.1 - Меню редактирования справочников
В открывшемся окне, пример которого представлен на рисунке 4.2, можно непосредственно в таблице отредактировать данные. Для перемещения между записями следует использовать кнопки в правой части окна или курсорные клавиши. Для того чтобы добавить, изменить или удалить запись нужно нажать соответствующую кнопку или горячую клавишу. Можно отсортировать данные в столбце по возрастанию и по убыванию нажатием на его заголовок. Пример кода для работы со справочниками приведен в приложении А. При редактировании таблицы «Виды начислений» используются триггеры контроля ссылочной целостности (№1) и контроля данных (№10). При вставке данных в таблицу «Даты» осуществляется каскадная вставка в таблицу «Табель» с помощью соответствующего триггера (№6).
Рисунок 4.2 - Окно редактирования справочника
При удалении записи необходимо подтверждение выполняемого действия в диалоговом окне представленном на рисунке 4.3.
Рисунок 4.3 - Запрос подтверждения удаления
.2 Основная функциональность приложения по ведению базы данных
Для того чтобы просмотреть информацию о сотрудниках и их назначениях на должности необходимо нажать кнопку «Сотрудники» в главном окне программы или выбрать соответствующий пункт меню, как показано на рисунке 4.4.
Рисунок 4.4 - Меню ведения базы
В открывшемся окне, представленном на рисунке 4.5, можно задать параметры фильтрации и поиска данных, произвести сортировку в столбце. При поиске можно перемещаться между всеми найденными записями с помощью кнопок. При работе с данными используется обновляемое представление (№2), для вставки записей применяется процедура (№3).
Рисунок 4.5 - Окно со списком сотрудников
При добавлении сотрудника или изменении информации об уже внесенном в базу работнике открывается окно, приведенное на рисунке 4.6, в котором можно отредактировать данные о работнике и его назначениях на должности. Для вставки нового назначения используется процедура (№4).
Рисунок 4.6 - Окно редактирования информации о сотруднике
Редактирование информации о назначениях происходит в соответствующем окне, изображенном на рисунке 4.7. Изменения сохраняются в журнале.
Рисунок 4.7 - Окно редактирования информации о назначении сотрудника
При выборе пункта меню «Табель» открывается окно, которое представлено на рисунке 4.8. В нем можно просмотреть список записей табеля за определенный период. Доступен поиск записей и сортировка, аналогично окну со списком сотрудников. Код для работы с данной таблицей приведен в приложении Б. Вставка записей осуществляется с помощью хранимой процедуры (№5).
Рисунок 4.8 - Список записей табеля
При добавлении или изменении информации открывается окно, которое можно увидеть на рисунке 4.9. В этом окне можно отредактировать данные, в том числе список начислений и удержаний. Для получения списка назначений применяется представление full_appointments_info (№1).
Рисунок 4.9 - Окно редактирования записи табеля
Для редактирования данных по начислениям или удержаниям служит окно, изображенное на рисунке 4.10. Для контроля ссылочной целостности используются триггеры (№2, 3), для вставки - хранимые процедуры (№6, 7).
Рисунок 4.10 - Окно редактирования начислений и удержаний
.3 Построение печатных форм
Доступ к построению печатных форм возможен или через кнопки в главном окне программы, или через пункт меню «Отчеты». Всего предусмотрено четыре параметризованных отчета. Если нажать на кнопку «Табель», будет открыто окно, представленное на рисунке 4.11, в нем можно задать период, данные за который будут представлены в отчете.
Рисунок 4.11 - Окно задания параметров для отчета по табелю
После нажатия на кнопку «Показать», на экране появится отчет, пример которого представлен на рисунке 4.12. Для получения данных используются хранимая процедура (№2) и скалярная функция (№1).
Рисунок 4.12 - Пример отчета по табелю
В случае отсутствия данных за выбранный период будет выведено предупреждение, которое показано на рисунке 4.13.
Рисунок 4.13 - Сообщение об отсутствии данных
При выборе отчета «Назначения» появится окно, которое изображено на рисунке 4.14. В нем можно указать сотрудника, для получения списка его назначений на должности.
Рисунок 4.14 - Выбор сотрудника для построения отчета
Пример отчета по назначениям сотрудника приведен на рисунке 4.15. Для получения данных используется хранимая функция (№2).
Рисунок 4.15 - Пример отчета по назначениям сотрудника
При нажатии на кнопку «Должности» будет открыто окно, которое изображено на рисунке 4.16. В этом окне можно указать размер оклада выводимых должностей, если ограничение не нужно, соответствующее поле необходимо оставить пустым.
Рисунок 4.16 - Задание размера оклада для построения отчета
Пример отчета представлен на рисунке 4.17. Программный код приведен в приложении В.
Рисунок 4.17 - Отчет со списком должностей
По кнопке «Отделы» будет открыто диалоговое окно, в котором следует выбрать отдел для построения отчета. Оно представлено на рис. 4.18.
Рисунок 4.18 - Выбор отдела для построения отчета
Пример построенного отчета приведен на рисунке 4.19. Для получения данных используется представление (№1).
Рисунок 4.19 - Задание размера оклада для построения отчета
4.4 Дополнительная функциональность приложения
При входе в приложение появляется окно для выбора типа аутентификации и базы данных для работы, представленное на рисунке 4.20. Выбор пользователя сохраняется и выводится в окне при следующем его открытии.
Рисунок 4.20 - Окно данных для входа
Если при входе это окно закрыть, в главном окне будут блокированы возможности по работе с данными. Доступ к этим настройкам можно так же получить через пункт меню «Настройка». В случае если подключение невозможно, выводится соответствующее сообщение, показанное на рисунке 4.21.
Рисунок 4.21 - Сообщение об ошибке подключения
Для просмотра диаграммы, позволяющей сравнить суммы зарплат работников по отделам, необходимо в главном меню зайти в «Диаграммы» и выбрать пункт «Диаграмма сумм зарплат в отделах». В появившемся окне необходимо задать промежуток времени для выборки данных. Пример построенной диаграммы приведен на рисунке 4.22. Для получения данных используется скалярная функция (№1). Код приведен в приложении Г.
Рисунок 4.22 - Пример диаграммы
Также ведется журнал изменений в таблице «Назначения» с возможностью отката действий пользователей. Пример выборки записей из журнала приведен на рисунке 4.23. На рисунке 4.24 представлена выборка после выполнения отката с помощью хранимой процедуры (№1) произведенных изменений. Для ведения журнала используются триггеры (№4, 5).
Рисунок 4.23 - Выборка из журнала изменений
Рисунок 4.24 - Откат изменений по журналу
5. Использованные средства MS SQL Server
5.1 Использование представлений
Используемые представления приведены в таблице 5.1.
Таблица 5.1 - Используемые представления
№ п/пНазваниеНазначениеГде используется1full_appointments_infoПолучение расширенной информации о назначениях, включая отдел, должность, ФИО сотрудника.Используется при редактировании записей табеля для получения информации о назначениях сотрудников. Также это представление применяется при построении отчета по сотрудникам отдела.2Сотрудники_отделыПолучение информации о сотрудниках вместе с названиями отделов с возможностью редактирования.Используется при редактировании информации о сотрудниках для получения и обновления сведений о сотруднике.
SQL-текст представлений:
- Расширенная информация о назначениях
CREATE VIEW full_appointments_info AS
SELECT Назначения.Код Код, Отделы.Название Отдел, ФИО,
Должности.Название Должность, Дата_назначения, Дата_ухода,
Отделы.Код КодОтдела FROM Назначения, Должности, Сотрудники,
Отделы WHERE Код_должности = Должности.КодКод_сотрудника= Сотрудники.КодКод_отдела = Отделы.Код
- Обновляемое представление - сотрудники с указанием отделов VIEW Сотрудники_отделы ASКод = сотрудники.Код + 0, ФИО, Дата_рождения,
Код_отдела = Код_отдела + 0, название отдел FROM сотрудники,
Отделы WHERE Код_отдела = Отделы.Код
.2 Использование хранимых процедур
Используемые хранимые процедуры приведены в таблице 5.2.
Таблица 5.2 - Используемые хранимые процедуры
№ п/пНазваниеНазначениеГде используется1restore_appointmentsОткат изменений в таблице назначений.Используется при необходимости отката изменений. 2table_infoПолучение сведений о зарплате в виде таблицы, которая также содержит сведения о суммах начислений и удержаний.Используется при построении отчета с информацией о зарплате.3Insert_to_personalВставка записи в таблицу «Сотрудники» и возвращение ее кода.Используется при добавлении записей в таблицу «Сотрудники»4Insert_to_appointmentsВставка записи в таблицу «Назначения» и возвращение ее кода.Используется при добавлении записей в таблицу «Назначения»5Insert_to_tableВставка записи в таблицу «Табель» и возвращение ее кода.Используется при добавлении записей в таблицу «Табель»6Insert_to_chargesВставка записи в таблицу «Начисления» и возвращение ее кода.Используется при добавлении записей в таблицу «Начисления»7Insert_to_keepingsВставка записи в таблицу «Удержания» и возвращение ее кода.Используется при добавлении записей в таблицу «Удержания»
SQL-текст процедур:
-Откат изменений по журналу
-Параметр - последняя запись, которая остается без изменений
create proc restore_appointments @last_ok_id intlogc cursor for oper, anum, aworker, aposition, astart, aendappointments_logid > @last_ok_idby id desc@oper char, @anum int, @aworker int, @aposition int,
@astart datetime, @aend datetimeidentity_insert назначения onlogc logc into @oper, @anum, @aworker,
@aposition, @astart, @aend@@fetch_status = 0 begin(@oper = 'I')from назначения where код = @anum
else if (@oper = 'D')into назначения(Код, Код_сотрудника,
Код_должности, Дата_назначения, Дата_ухода)
values (@anum,@aworker,@aposition,@astart,@aend)
else назначения set Код_сотрудника = @aworker,
Код_должности = @aposition,
Дата_назначения = @astart, Дата_ухода = @aend
where Код = @anum logc into @oper, @anum, @aworker,
@aposition, @astart, @aendidentity_insert назначения off logc
deallocate logc
-возвращает таблицу с данными по зарплате
create proc table_info @date_id int as
select Табель.Код, Часы, ФИО, Должности.Название Должность,
Отделы.Название Отдел, Дата, Колличество_часов, Оклад#table from Табель, Назначения, Сотрудники,
Должности, Отделы, ДатыКод_назначения = Назначения.КодКод_сотрудника = Сотрудники.КодКод_должности = Должности.Код Код_отдела = Отделы.КодКод_даты = Даты.Код and Даты.Код = @date_idby дата, отдел, Фио
-вычисляемое поле с суммой оклада за отработанные часы
alter table #table add сумма_за_часы as cast (оклад * (cast(часы as float)/колличество_часов) as numeric(10,2))
-таблица для дополнительных данных
CREATE TABLE #charges (Код int, процент_начислений int,
процент_удержаний int, сумма_начислений float,
сумма_удержаний float, итого float)
INSERT INTO #charges(код) SELECT Код FROM #table
--подсчет процента для начисления и удержания#charges set Процент_начислений =
(SELECT SUM(Величина*Коэффициент) Виды_начислений, Начисления Код_вида_начисления = Виды_начислений.Код
AND Код_табеля = #charges.Код
AND Тип = 'О' )#charges set Процент_начислений =(Процент_начислений, 0)
update #charges set процент_удержаний =
(SELECT SUM(Величина*Коэффициент) Виды_удержаний, Удержания Код_вида_удержания = Виды_удержаний.Код
AND Код_табеля = #charges.Код
AND Тип = 'О' )
update #charges set процент_удержаний =(процент_удержаний, 0)
-подсчет сумм начисления и удержания
update #charges set Сумма_начислений =
(SELECT isnull(SUM(Величина*Коэффициент),0)Виды_начислений, Начисления Код_вида_начисления = Виды_начислений.Код
AND Код_табеля = #charges.КодТип = 'A' )#charges set Сумма_удержаний =
(SELECT isnull(SUM(Величина*Коэффициент),0)Виды_удержаний, Удержания Код_вида_удержания = Виды_удержаний.Код
AND Код_табеля = #charges.КодТип = 'A' )
--добавляем суммы по подсчитанным ранее процентам#charges set
Сумма_начислений = Сумма_начислений + сумма_за_часы *(Процент_начислений as float) / 100
from #table where #charges.Код = #table.Код
update #charges set
Сумма_удержаний = Сумма_удержаний +
(сумма_за_часы + Сумма_начислений ) * (Процент_удержаний as float) / 100
from #table where #charges.Код = #table.Код
--расчет зарплаты#charges set
итого = сумма_за_часы + Сумма_начислений -
Сумма_удержаний#table where #charges.Код = #table.Код
--возвращаем данныеДата, Отдел, ФИО, Должность, часы, сумма_за_часы,
Процент_начислений, процент_удержаний,(Сумма_начислений as numeric(10,2))
Сумма_начислений,
Cast(Сумма_удержаний as numeric(10,2)) Сумма_удержаний,
Cast(Итого as numeric(10,2)) Итого #table, #charges where #table.код = #charges.код
- Процедуры для вставки записей
- результат - код вставленной записи
CREATE PROC Insert_to_personal AS@code int;@code = min(код) from отделы
insert into сотрудники(ФИО, Код_отдела) values('', @code)
SELECT @@identity КодPROC Insert_to_appointments @worker_id int AS@code int;@code = min(код) from должности
insert into назначения (код_сотрудника, дата_назначения,
Код_должности) values(@worker_id, getdate(), @code)
SELECT IDENT_CURRENT('назначения') КодPROC Insert_to_table AS@code int;@code = min(код) from назначения into табель(часы, код_назначения) values(0, @code)
SELECT @@identity КодPROC Insert_to_charges @table_id int AS@code int;@code = min(код) from виды_начислений
insert into начисления(код_табеля, коэффициент,
код_вида_начисления) values(@table_id, 1, @code)
SELECT @@identity КодPROC Insert_to_keepings @table_id int AS@code int;@code = min(код) from виды_удержаний into удержания(код_табеля, коэффициент,
код_вида_удержания) values(@table_id, 1, @code) @@identity Код
.3 Использование хранимых функций
Используемые хранимые функции приведены в таблице 5.3.
Таблица 5.3 - Используемые хранимые функции
№ п/пНазваниеНазначениеГде используется1salaryПолучение суммы зарплаты для записи табеля.Используется при формировании отчета с информацией о зарплате. Также применена для построении диаграммы зарплат отделов.2Appointments_of_worker_by_idПолучение информации о назначения сотрудника.Используется при построении отчета по назначениям сотрудника.
SQL-текст функций:
- параметр - код табеля
-- результат - сумма зарплатыfunction salary(@table_id int) returns numeric(10,2)@sum float@sum = 0
-- сумма оклада за отработанные часы
declare @sum_by_hours float
select @sum_by_hours = cast (оклад *( cast (часы as float) /
колличество_часов ) as numeric(10,2))табель, назначения, должности, датыкод_назначения = назначения.кодкод_должности = должности.кодкод_даты = даты.код and табель.код = @table_id
set @sum = @sum_by_hours
- процент начислений и удержаний
declare @percent_add float@percent_keep float
SELECT @percent_add = SUM(Величина*Коэффициент) Виды_начислений, Начисления Код_вида_начисления = Виды_начислений.КодКод_табеля = @table_id AND Тип = 'О'
set @percent_add = isnull(@percent_add, 0)
SELECT @percent_keep = SUM(Величина*Коэффициент) Виды_удержаний, Удержания Код_вида_удержания = Виды_удержаний.КодКод_табеля = @table_id AND Тип = 'О'
set @percent_keep = isnull(@percent_keep, 0)
-- сумма начислений и удержаний
declare @sum_add float
declare @sum_keep float@sum_add = isnull(SUM(Величина*Коэффициент),0)
FROM Виды_начислений, Начисления Код_вида_начисления = Виды_начислений.Код
AND Код_табеля = @table_id AND Тип = 'A' @sum_keep = isnull(SUM(Величина*Коэффициент),0)Виды_удержаний, Удержания Код_вида_удержания = Виды_удержаний.Код
AND Код_табеля = @table_id AND Тип = 'A'@sum_add = @sum_add + @sum_by_hours * @percent_add / 100@sum_keep = @sum_keep + ( @sum_add + @sum_by_hours ) *
@percent_keep / 100
- зарплата
set @sum = @sum_by_hours + @sum_add - @sum_keep@sum
- параметр - код сотрудника
- результат - перечень назначений этого сотрудника
CREATE FUNCTION Appointments_of_worker_by_id (@worker int)
RETURNS @appointments table (Код int, Код_должности int,
Код_сотрудника int, Дата_назначения datetime,
Дата_ухода datetime, ФИО varchar(50)) AS@name varchar(50)@name = ФИО
FROM СотрудникиКод = @workerINTO @appointments (Код, Код_должности,
Код_сотрудника, Дата_назначения, Дата_ухода, ФИО)Код, Код_должности, @worker,
Дата_назначения, Дата_ухода, @nameНазначения WHERE Код_сотрудника = @worker
.4 Использование временных таблиц
Используемые временные таблицы приведены в таблице 5.4.
Таблица 5.4 - Используемые временные таблицы
№ п/пНазваниеНазначениеГде используется1#tableСодержит базовую информацию по записям табеля, включая начисляемый оклад.Используется в процедуре №22#chargesСодержит дополнительную информацию по записям табеля и величину зарплаты, а также промежуточные результаты для ее расчета.Используется в процедуре №2
5.5 Использование курсоров
Используемые курсоры приведены в таблице 5.4.
Таблица 5.4 - Используемые курсоры
№ п/пНазваниеНазначениеГде используется1logcПроход по записям журнала изменений.Используется в процедуре №12datesПроход по добавляемым датам.Используется в триггере №6
5.6 Использование триггеров
Используемые триггеры приведены в таблице 5.6.
Таблица 5.6 - Используемые триггеры
№ п/пНазваниеНазначениеГде используется1Виды_начислений_ DelКонтроль ссылочной целостности.Удаление записей из таблицы «Виды_начислений».2Начисления_InsКонтроль ссылочной целостности.Вставка записей в таблицу «Начисления».3Начисления_UpdКонтроль ссылочной целостности.Изменение записей таблицы «Начисления».4Назначения_logВедение журнала изменений.Вставка и удаление записей в таблицу «Назначения».5Назначения_log_updВедение журнала изменений.Изменение записей таблицы «Назначения».6Даты_InsКаскадная вставка данных в таблицу «Табель».Вставка записей в таблицу «Даты».7Сотрудники_отделы_InsОбновляемое представление.Вставка записей в представление «Сотрудники_отделы».8Сотрудники_отделы_UpdОбновляемое представление.Изменение записей представления «Сотрудники_отделы».9Сотрудники_отделы_DelОбновляемое представление.Удаление записей из представления «Сотрудники_отделы».10Виды_начислений_Ins_UpdКонтроль данных.Вставка и изменение записей таблицы «Виды_начислений».
SQL-текст триггеров:
-Триггеры контроля целостности
CREATE TRIGGER Виды_начислений_Del ON Виды_начислений
FOR DELETE EXISTS (SELECT * FROM Начисления WHERE Код_вида_начисления IN (SELECT Код FROM deleted))
BEGIN('Удаление записей невозможно, т.к. есть
связанные записи в другой таблице', 16, 1)
rollback tranTRIGGER Начисления_Ins ON НачисленияINSERT (SELECT count(*) FROM inserted) <>
(SELECT count(*) FROM inserted Код_вида_начисления IN
(SELECT Код FROM Виды_начислений))('Невозможно добавить запись: неизвестный вид начисления', 16, 1)
rollback tranTRIGGER Начисления_Upd ON НачисленияUPDATE update(Код_вида_начисления) BEGIN(SELECT count(*) FROM inserted) <>
(SELECT count(*) FROM inserted Код_вида_начисления IN
(SELECT Код FROM Виды_начислений))('Невозможно изменить запись: неизвестный вид начисления', 16, 1)
rollback tran
-Триггер контроля данныхTRIGGER Виды_начислений_Ins_Upd ON Виды_начисленийInsert, Update (SELECT count(*) FROM inserted) <>
(SELECT count(*) FROM inserted Тип IN ('А','О'))
BEGIN('Невозможно сохранить запись. Введен неизвестный тип начисления', 16, 1)
rollback tran
END
GO
- Триггеры ведения журнала по таблице назначения
create trigger Назначения_log on назначенияinsert, update, delete@date datetime@date = getdate()@user varchar(50)@user = current_userinto appointments_log'D', @user, @date, Код, Код_сотрудника,
Код_должности, Дата_назначения, дата_ухода
from deletedinto appointments_log'I',@user,@date,Код,Код_сотрудника,
Код_должности, Дата_назначения, дата_ухода
from insertedtrigger Назначения_log_upd on назначенияupdate@date datetime@date = getdate()@user varchar(50)@user = current_userinto appointments_log 'U', @user, @date, Код, Код_сотрудника, Код_должности,
Дата_назначения, дата_уходаdeleted
-Триггер каскадной вставки в табель для подходящих назначений
create trigger Даты_Ins on Датыinsert@date_id int@date datetimenocount ondates CURSOR FORКод, Дата inserteddatesdates INTO @date_id, @date @@Fetch_status = 0 BEGINinto табель (Код_даты, Код_назначения, Часы)
select @date_id, Код, 0 назначения where
( Дата_ухода is null or(Дата_ухода) > Year(@date) or(Дата_ухода) = Year(@date) and(Дата_ухода) >= Month(@date) )
and
( Year(Дата_назначения) < Year(@date) or(Дата_назначения) = Year(@date) and(Дата_назначения) < Month(@date) )
FETCH dates INTO @date_id, @datedatesdates
--Триггеры обновляемого представленияtrigger Сотрудники_отделы_Ins on Сотрудники_отделы
instead of insertinto сотрудники (ФИО, Дата_рождения, Код_отдела)
select ФИО, Дата_рождения, d.Код
from inserted i left join отделы d i.Код_отдела = d.Код and i.Отдел is Nulli.Отдел = d.Название and i.Код_отдела is null
or i.Код_отдела = d.Код and i.Отдел = d.Название
gotrigger Сотрудники_отделы_Upd on Сотрудники_отделы
instead of updateupdate (Отдел)not update (Код_отдела) begin сотрудники
set ФИО = i.ФИО,
Дата_рождения = i.Дата_рождения,
Код_отдела = d.Код
from сотрудники,inserted i left join отделы di.Отдел = d.Названиесотрудники.код = i.Код
end
else beginсотрудникиФИО = i.ФИО,
Дата_рождения = i.Дата_рождения,
Код_отдела = d.Код
from сотрудники,inserted i left join отделы di.Отдел = d.Название i.Код_отдела = d.Кодсотрудники.код = i.Код
end
else if not update (Код_отдела) begin сотрудники
set ФИО = i.ФИО,
Дата_рождения = i.Дата_рождения
from сотрудники, inserted i сотрудники.код = i.Код
else beginсотрудникиФИО = i.ФИО,
Дата_рождения = i.Дата_рождения,
Код_отдела = d.Код
from сотрудники,inserted i left join отделы di.Код_отдела = d.Кодсотрудники.код = i.Код
end
go
create trigger Сотрудники_отделы_Del on Сотрудники_отделы
instead of deletefrom сотрудники where код in
(select код from deleted)
Заключение
В ходе выполнения курсовой работы были изучены стандартные функции MS SQL SERVER и разработано «клиент-серверное» приложение «Автоматизированная система управления отделом бухгалтерии по расчету зарплаты».
При работе над приложением была:
-спроектирована база данных, соответствующая предметной области задачи;
-реализована клиентская программа для пользователей.
Разработанная программа позволяет:
-хранить в упорядоченном виде данные о начислении заработной платы сотрудников,
-работать с этой информацией,
-автоматизировать расчет заработной платы.
Клиентская программа обладает простым, понятным интерфейсом. Она предоставляет возможность удобной работы с необходимой информацией. Проведена некоторая автоматизация внесения новых данных.
Список использованных источников
1Вьейра Р. SQL Server 2000. Программирование. Часть 2 [Текст] / Р. Вьейра. - М.: Бином, 2004. - 807 с.
2Мамаев Е.В. Microsoft SQL Server 2000 . Наиболее полное руководство [Текст] / Е.В. Мамаев. - СПб.: BHV, 2005. - 1280 с.
3Дэвидсон Л. Проектирование баз данных на SQL Server 2000 [Текст] / Л. Дэвидсон. - М.: Бином, 2003. - 680 с.
сервер печатный справочник триггер
Приложение А
Корректировка справочников
Код выборки данных из таблицы «Должности». Применяется для получения информации при корректировке данного справочника.
//создание объекта для выполнения запроса к базе данных
BaseFrame->query = new TADOQuery(this);
//указание соединения с базой данных>query->Connection = connection;>query->Active = False;>query->SQL->Text =
"SELECT Код,Название Должность,Оклад FROM Должности order by Название";->query->Active = True;
//создание источника данных для грида
BaseFrame->dataSource = new TDataSource(this);
//связывание его с набором данных>dataSource->DataSet = BaseFrame->query;
//указание источника информации для грида>DBGrid->DataSource = BaseFrame->dataSource;>DBGrid->Columns->Items[0]->FieldName = "Код";>DBGrid->Columns->Items[1]->FieldName = "Должность";>DBGrid->Columns->Items[2]->FieldName = "Оклад";>DBGrid->Columns->Items[0]->Visible = false;
//сокрытие кнопок если нет данных
if (BaseFrame->query->IsEmpty()) {
BaseFrame->btnDel->Enabled = false;>btnEdit->Enabled = false;
}
Код позиционирования на записи. Используется после изменения информации в таблице.
//получение кода текущей записи
int k = query->FieldByName("Код")->AsInteger;
//сохранение строки сортировки
WideString s = query->Sort;
//обновление данных>Close();>Open();>DisableControls();
//сортировка >Sort = s;
//поиск записи>Locate("Код",k,TLocateOptions());>EnableControls();
Удаление записи.>Delete();
Сохранение записи.(query->State == dsEdit || query->State == dsInsert)>Post();
Добавление записи.>Insert();
Приложение Б
Работа с таблицей «Табель»
Получение данных из таблицы «Табель» за определенный период. Используется при нажатии на кнопку «Показать» в окне редактирования.
query->Active = False;
//задание нового запроса
query->SQL->Clear();>SQL->Add("SELECT Табель.Код, ");->SQL->Add("ФИО, Должности.Название Должность,");
query->SQL->Add("Дата, Отделы.Название Отдел, Часы, ");
query->SQL->Add("Колличество_часов Всего, Код_назначения, Код_даты ");
query->SQL->Add("FROM Табель, Сотрудники, Отделы, ");
query->SQL->Add("Даты, Назначения, Должности ");
query->SQL->Add("WHERE Код_отдела = Отделы.Код ");
query->SQL->Add("AND Код_должности = Должности.Код ");
query->SQL->Add("AND Код_даты = Даты.Код ");
query->SQL->Add("AND Код_назначения = Назначения.Код ");
query->SQL->Add("AND Код_сотрудника = Сотрудники.Код ");
query->SQL->Add("AND Дата BETWEEN :start and :end");>SQL->Add("ORDER BY Дата, ФИО");
//сохранение значений параметров для последующегоиспользования
start = dateStart->Date;
end = dateEnd->Date;
//задание параметров для запроса
query->Parameters->ParamByName("start")->Value = dateStart->Date;>Parameters->ParamByName("end")->Value = dateEnd->Date; ->Active = True;
Изменение записи табеля.
//получение кода текущей записи
int id = query->FieldByName("Код")->AsInteger;
//создание объекта для передачи информации
TRecordInfo* x = new TRecordInfo(id);
x->SetHours(query->FieldByName("Часы")->AsInteger);>SetTotal(query->FieldByName("Всего")->AsInteger);>SetAppointmentId(query->FieldByName("Код_назначения")->AsInteger);>SetDateId(query->FieldByName("Код_даты")->AsInteger);>SetStartDate(start);>SetEndDate(end);
//создание окна для редактирования запси табеля
TableRecord = new TTableRecord(this, connection, x);
//открытие транзакции>BeginTrans();mr = TableRecord->ShowModal();
//создание объекта для обновления информации
TADOQuery* queryUpd = new TADOQuery(this);
queryUpd->Connection = connection;>Active = False;>SQL->Clear();(mr == mrOk){
//запрос на изменение записи
queryUpd->SQL->Add("UPDATE Табель SET ");
queryUpd->SQL->Add("Часы = :hours, ");>SQL->Add("Код_назначения = :app_id, ");>SQL->Add("Код_даты = :date_id ");>SQL->Add("WHERE Код = :id");
//задание параметров>Parameters->ParamByName("id")->Value = id;>Parameters->ParamByName("hours")->Value = >GetHours();>Parameters->ParamByName("app_id")->Value = >GetAppointmentId();>Parameters->ParamByName("date_id")->Value = >GetDateId();>ExecSQL();
//закрытие транзакции->CommitTrans();
//обновление информации в гриде и позиционирование
int oldID = query->FieldByName("Код")->AsInteger;(oldID);
} else {
//откат транзакции>RollbackTrans();
}queryUpd;TableRecord;x;
Добавление записи.
//открытие транзакции->BeginTrans();
//создание объекта для выполнения хранимой процедуры
TADOStoredProc* storedProc = new TADOStoredProc(this);>Connection = connection;
//задание имени процедуры>ProcedureName = "Insert_to_table";->Active = True;
//получение кода добавленной записи
int id = storedProc->FieldByName("Код")->AsInteger;
//объект для передачи данных* x = new TRecordInfo(id);>SetStartDate(dateStart->Date);>SetEndDate(dateEnd->Date);
//окно редактирования= new TTableRecord(this, connection, x); mr = TableRecord->ShowModal();
//объект для сохранения изменений
TADOQuery* queryUpd = new TADOQuery(this);>Connection = connection;>Active = False;oldID;(mr == mrOk){>SQL->Clear();>SQL->Add("UPDATE Табель SET ");>SQL->Add("Часы = :hours, ");>SQL->Add("Код_назначения = :app_id, ");>SQL->Add("Код_даты = :date_id ");>SQL->Add("WHERE Код = :id");>Parameters->ParamByName("id")->Value = id;>Parameters->ParamByName("hours")->Value = >GetHours();>Parameters->ParamByName("app_id")->Value = >GetAppointmentId();>Parameters->ParamByName("date_id")->Value = ->GetDateId();
queryUpd->ExecSQL();
//запоминаем код добавленной записи для позиционирования
oldID = id;
//закрытие транзакции>CommitTrans();
} else {
//запоминаем код текущей записи для позиционирования
oldID = query->FieldByName("Код")->AsInteger;->RollbackTrans();
}
//обновление информации в гриде и позиционирование
positioning(oldID);storedProc;queryUpd;TableRecord;x;
Удаление записи.* queryUpd = new TADOQuery(this);>Connection = connection;id = query->FieldByName("Код")->AsInteger;->Active = False;
//проверка существования связанных записей
queryUpd->SQL->Add("Select Код FROM Начисления");
queryUpd->SQL->Add("WHERE Код_табеля = :id");
queryUpd->SQL->Add("UNION");
queryUpd->SQL->Add("Select Код FROM Удержания");
queryUpd->SQL->Add("WHERE Код_табеля = :id2");
queryUpd->Parameters->ParamByName("id")->Value = id;>Parameters->ParamByName("id2")->Value = id;>Open();(!queryUpd->IsEmpty()) {
//сообщение в случае существования связанных записей("Невозможно удалить запись");queryUpd;;
}
//запрос подтверждения удаления записиbtn;= Application->MessageBox("Удалить запись?", "Удаление записи",
MB_YESNO);(btn == IDNO) {;
}
//сохранение кода для позиционирования
query->Next();
int oldID = query->FieldByName("Код")->AsInteger;(oldID == id) {>Prior();= query->FieldByName("Код")->AsInteger;
}
//удаление записи
queryUpd->Active = False;
queryUpd->SQL->Text = "DELETE FROM Табель WHERE Код = :id";>Parameters->ParamByName("id")->Value = id;>ExecSQL();(oldID);queryUpd;
Поиск в таблицеid = query->FieldByName("Код")->AsInteger;>Close();>SQL->Clear();
//запрос на поиск данных>SQL->Add("Select табель.Код FROM табель,");>SQL->Add("full_appointments_info, Даты");>SQL->Add("WHERE Фио like :name");>SQL->Add("and отдел like :dep");>SQL->Add("and должность like :pos");>SQL->Add("and код_назначения = full_appointments_info.Код");>SQL->Add("AND Код_даты = Даты.Код ");>SQL->Add("AND Дата BETWEEN :start and :end");>SQL->Add("ORDER BY Дата, ФИО");>Parameters->ParamByName("name")->Value =
"%" + eSearch->Text + "%";>Parameters->ParamByName("dep")->Value =
"%" + eDepartment->Text + "%";>Parameters->ParamByName("pos")->Value =
"%" + ePosition->Text + "%";>Parameters->ParamByName("start")->Value = start;>Parameters->ParamByName("end")->Value = end;>Open();(querySearch->IsEmpty()){>Enabled = false;>Enabled = false;
} else {
//если записи найдены активация возможности перехода между ними
btnSearchNext->Enabled = true;>Enabled = true;
//и позиционирование на первойnewId = querySearch->FieldByName("Код")->AsInteger;>Locate("Код", newId, TLocateOptions());->SetFocus();
}
Обновление данных в гриде и позиционирование. Используется при вставке, изменении, удалении записей.
WideString s = query->Sort;>Close();>Open();>DisableControls();>Sort = s;>Locate("Код", key, TLocateOptions());>EnableControls();>SetFocus();
Приложение В
Построение печатных форм
Получение списка сотрудников для построения отчета со списком должностей.
query2 = new TADOQuery(this);= new TDataSource(this);->Connection = connection;->Close();
//получение списка сотрудников
query2->SQL->Add("Select Код, ФИО");
query2->SQL->Add("from Сотрудники");->SQL->Add("order by ФИО");->Open();(query2->IsEmpty()){
//выход если сотрудников нет
ShowMessage("Нет работников");
Close();
}
//отображение полученного списка
dataSource->DataSet = query2;
DBLookupComboBox1->ListSource = dataSource;->ListField = "ФИО";1->KeyField = "Код";
Построение отчета, после выбора сотрудника.
//Получение кода выбранного сотрудника
int worker = query2->FieldByName("Код")->AsInteger;
query->Connection = connection;>Close();->SQL->Clear();
//Получение списка должностей с использование хранимой функции
query->SQL->Add("Select Должности.Название Должность,");>SQL->Add("Дата_назначения, Дата_ухода, ФИО");
query->SQL->Add("From Appointments_of_worker_by_id(:wid), Должности");
query->SQL->Add("where Код_должности = должности.код");
query->SQL->Add("Order by Дата_назначения");>Parameters->ParamByName("wid")->Value = worker;>Open();(query->IsEmpty()){->Caption = "Нет данных";
} else {->Caption = "Назначения:";
}>Caption = query->FieldByName("ФИО")->AsString;
//связывание отчета и полей вывода с набором данных
QuickRep1->DataSet = query;>DataSet = query;>DataField = "Должность";>DataSet = query;>DataField = "Дата_назначения";>DataSet = query;>DataField = "Дата_ухода";->Preview();
Приложение Г
Построение диаграммы
Код для построения сравнительной диаграммы зарплат в отделах за выбранный период.
query->Active = False;>SQL->Clear();
//получение данных для диаграммы
query->SQL->Add("Select Отделы.Название Отдел,");
query->SQL->Add("Sum(amfedorenko.salary(Табель.Код)) Зарплата");
query->SQL->Add("From Табель, Назначения, Сотрудники, Даты, Отделы");
query->SQL->Add("where Код_назначения = назначения.код");
query->SQL->Add("and код_сотрудника = сотрудники.код");
query->SQL->Add("and код_даты = даты.код");
query->SQL->Add("and код_отдела = отделы.код");
query->SQL->Add("and Дата between :start and :end");>SQL->Add("group by Отделы.Название");>SQL->Add("order by Отделы.Название");>Parameters->ParamByName("start")->Value = dateStart->Date;>Parameters->ParamByName("end")->Value = dateEnd->Date;>Open();(!query->IsEmpty()) {>Title->Text->Clear();>Title->Text->Add("Зарплата по отделам за ");>Title->Text->Add(dateStart->Date.DateString());>Title->Text->Add(dateEnd->Date.DateString());
} else {>Title->Text->Clear();->Title->Text->Add("Нет данных за этот период");
}>SeriesList->Clear();
//серия для диаграммы* x = new TBarSeries(this); name = "";
Currency sum = 0;
//проход по всем возвращенным записям
while (!query->Eof){= query->FieldByName("Отдел")->AsString;= query->FieldByName("Зарплата")->AsCurrency;
//добавление столбца в серию
x->AddBar(sum,name,clBlue);
//переход к следующей записи
query->Next();
}>AddSeries(x);