Разработка учебного проекта «Автоматизированная система управления отделом бухгалтерии по расчету зарплаты»

Министерство образования Республики Беларусь Учреждение образования

«Гомельский государственный университет имени Франциска Скорины»

Математический факультет Кафедра математических проблем управления Курсовая работа по курсу «Базы данных»

рАЗРАБОТКА УЧЕБНОГО ПРОЕКТА «АВТОМАТИЗИРОВАННАЯ СИСТЕМА УПРАВЛЕНИЯ отделом бухгалтерии по расчету зарплаты»

Исполнитель:

студент группы ПО-41 Федоренко А.М.

Гомель 2014

Введение

Человек всегда стремился облегчить свою деятельность и расширить свои возможности. Компьютеры были созданы для решения вычислительных задач. Со временем они все чаще стали использоваться для построения систем обработки документов, содержащейся в них информации. Развитие в этом направлении привело к созданию концепции баз данных и систем управления базами данных.

Их использование позволяет:

— повысить надежность, целостность и сохранность данных;

— сохранить затраты интеллектуального труда;

— обеспечить простоту и легкость использования данных;

— обеспечить независимость прикладных программ от данных (изменений их описаний и способов хранения);

— обеспечить достоверность данных;

— сократить дублирование информации за счет структурирования данных.

Целью курсовой работы является изучение средств работы с системами управления базами данных, на примере MS SQL SERVER. Задача курсовой работы — разработать «клиент-серверное» приложение для автоматизации работы отдела бухгалтерии по учету зарплаты и изучить встроенные функции MS SQL SERVER.

В качестве среды разработки клиентской программы был использован C++ Builder.

Основная часть курсовой работы состоит из пяти разделов.

В первом разделе описано назначение разработанного в ходе курсового проектирования программного приложения, его основные возможности.

Второй раздел содержит информацию о стандартных функциях MS SQL SERVER.

В третьем разделе в табличном виде описан состав таблиц базы данных, приведена схема данных, описана структура таблиц базы данных, приведен SQL-код по созданию таблиц и описания ограничений.

В четвертом разделе описан диалог пользователя с приложением.

В пятом разделе в табличной форме описаны разработанные и используемые средства SQL-сервера и приведен SQL-текст данных средств.

1. Назначение разработанного приложения Разработанное при выполнении курсовой работы приложение предназначено для автоматизации работы отдела бухгалтерии по расчету зарплаты. Оно позволяет редактировать справочники отделов, должностей, видов начислений и удержаний, дат начисления зарплаты. Основная функциональность приложения: управление информацией о сотрудниках, их назначениях на должности, производить расчет зарплаты по данным, которые хранит табель с информацией о количестве отработанных часов, произведенных начислениях и удержаниях. При работе со списком сотрудников и записей табеля есть возможность фильтрации и поиска данных. При вставке новой даты в справочник, для походящих назначений автоматически осуществляется добавление информации в табель.

Так же есть возможность просмотреть диаграмму сравнения уровня выплат зарплат в отделах в определенном периоде.

Программа позволяет строить печатные формы с информацией:

— о должностях с окладом в определенном диапазоне;

— назначениях сотрудника;

— должностях, на которых работают сотрудники отдела и списком сотрудников на них;

— зарплатах работников, сгруппированных по месяцам, за определенный период.

Ведется журнал изменений одной из таблиц, с возможностью выполнения отката действий пользователей. Можно настраивать сервер и базу данных для работы, выбирать способ аутентификации.

Основные функции приложения представлены на рисунке 1.1.

Рисунок 1.1 — Основные функции приложения

2. Стандартные функции MS SQL-Сервера Среди стандартных функций MS SQL SERVER можно выделить следующие группы:

— функции агрегации;

— функции курсоров;

— функции даты и времени;

— математические функции;

— функции метаданных;

— функции наборов записей;

— функции безопасности;

— строковые функции;

— системные функции;

— функции текстовых данных и изображений [1],.

Функции агрегации применяются к наборам записей и часто используются в сочетании с оператором GROUP BY. С помощью них можно найти сумму (SUM), среднее значение (AVG), квадратичное отклонение (STDEV), дисперсию (VAR), минимальное (MIN) и максимальное (MAX) значение по полю или количество записей в группе (COUNT). С большинством из них могут использоваться ключевые слова ALL — применять ко всем значениям выражения в функции и DISTINCT — значение включается единственный раз. Выражение не может быть подзапросом.

Функции курсоров позволяют получить информацию о состоянии курсора по его имени или по имени переменной. К этой группе относится функция CURSOR_STATUS. Так же к функциям этой группы можно отнести @@FETCH_STATUS — результат выполнения оператора FETCH, и @@CURSOR_ROWS — количество строк в курсоре.

Функции даты и времени могут применяться для получения одного из элементов соответствующей переменной, как в числовом (DATEPART, DATE, MONTH, YEAR), так и в текстовом варианте (DATENAME), изменения (DATEADD) и подсчета разницы (DATEDIFF) между двумя значениями, получения текущих даты и времени (GETDATE, GETUTCDATE).

Математические функции предназначены для выполнения различных математических операций — возведение в степень (POWER, SQUARE, SQRT), логарифмические операции (LOG, LOG10, EXP), вычисление тригонометрических функций (SIN, COS, TAN, COT, ASIN, ACOS, ATAN, DEGREES, RADIANS, PI), округление (CEILING, FLOOR, ROUND), модуль (ABS), так же функция проверки на числовой тип данных (ISNUMERIC). Некоторые из них возвращают значение того же типа, что и переданный параметр.

Функции метаданных применяются для получения информации о базе данных и объектах базы данных.

Функции наборов записей возвращают объект, который можно использовать, как ссылку на таблицу. Они позволяют получать данные с другого сервера (OPENQUERY, OPENROWSET), работать с XML документами (OPENXML).

Функции безопасности позволяют проверить наличие прав у пользователя для доступа к базе данных (HAS_DBACCESS), принадлежность к определенной роли (IS_MEMBER, IS_SRVROLEMEMBER), а также получить идентификатор (SUSER_ID, SUSER_SID, USER_ID) и имя пользователя (SUSER_NAME, SUSER_SNAME, USER).

Строковые функции предоставляют набор средств для работы с текстовыми данными. Они позволяют изменять регистр (LOWER, UPPER), выделять подстроки (LEFT, RIGHT, SUBSTRING), производить поиск (CHARINDEX, PATINDEX) и замену выражений (REPLACE, STUFF), дублировать строки (REPLICATE), получить длину строки (LEN). Также можно получить код символа (ASCII, UNICODE) и символ по коду (CHAR, NCHAR), удалить пробельные символы (LTRIM, RTRIM) и вставить их (SPACE), изменить порядок символов в строке (REVERSE), преобразовать число в строку (STR).

Системные функции предоставляют доступ к сведениям о параметрах, объектах и настройках сервера. При этом они не позволяют изменить настройки сервера. К этой группе также относят функции преобразования типов CAST и CONVERT, последняя позволяет задать стиль для конвертирования даты в символьный тип данных; CURRENT_USER — позволяет получить имя пользователя, CURRENT_TIMESTAMP — текущие дату и время. Функция IDENT_CURRENT позволяет получить последнее значение счетчика сгенерированное для таблицы, IDENT_INCR возвращает приращение, а IDENT_SEED — начальное значение счетчика. IDENTITY — применяется для вставки счетчика в таблицу при использовании оператора SELECT INTO.

К группе для работы с текстовыми данными (text) и изображениями (image) относят функции TEXTPRT и TEXTVALID, осуществляющие проверку указателя.

Есть некоторые ограничения на применение некоторых встроенных функций MS SQL SERVER в определяемых пользователем функциях. Например, нельзя использовать недетерминированные функции, т. е. те, которые могут возвращать разный набор значений при выполнении с одним набором параметров. Аргументация этого следующая. Пользовательские функции могут использоваться для построения вычисляемых полей или полей в представлениях, и каждое из них может быть проиндексировано. А индексация может происходить только тогда, когда для полей можно гарантировать, что возвращаются одни и те же значения при любом вызове функции.

3. Структура базы данных приложения Состав таблиц базы данных приведен в таблице 3.1.

Таблица 3.1 — Состав таблиц базы данных приложения

Схема базы данных представлена на рисунке 3.1

Рисунок 3.1 — Схема базы данных приложения

Состав таблиц базы данных приведен в таблицах 3.2 — 3.12.

Таблица 3.2 — Состав таблицы «Отделы»

Таблица 3.3 — Состав таблицы «Сотрудники»

Таблица 3.4 — Состав таблицы «Должности»

Таблица 3.5 — Состав таблицы «Назначения»

Таблица 3.6 — Состав таблицы «Даты»

Таблица 3.7 — Состав таблицы «Виды_начислений»

Таблица 3.8 — Состав таблицы «Виды_удержаний»

Таблица 3.9 — Состав таблицы «Начисления»

Таблица 3.10 — Состав таблицы «Удержания»

Таблица 3.11 — Состав таблицы «Табель»

Таблица 3.12 — Состав таблицы «appointments_log»

SQL-текст создания таблиц и описания ограничений:

—Создание таблиц

CREATE TABLE Отделы (Код int IDENTITY Primary key, Название varchar (50) not null)

GO

CREATE TABLE Сотрудники (Код int IDENTITY Primary key, ФИО varchar (200), Дата_рождения datetime, Код_отдела int not null)

GO

CREATE TABLE Должности (Код int IDENTITY Primary key, Название varchar (50) not null, Оклад money not null)

GO

CREATE TABLE Назначения (Код int IDENTITY Primary key, Код_должности int, Код_сотрудника int, Дата_назначения datetime, Дата_ухода datetime)

GO

CREATE TABLE Даты (Код int IDENTITY Primary key, Дата datetime not null, Колличество_часов int not null)

GO

CREATE TABLE Табель (Код int IDENTITY Primary key, Код_назначения int, Часы int, Код_даты int)

GO

CREATE TABLE Виды_начислений (Код int IDENTITY Primary key, Название varchar (50), Величина int not null, Тип varchar (1) not null default 'О')

GO

CREATE TABLE Виды_удержаний (Код int IDENTITY Primary key, Название varchar (50), Величина int not null, Тип varchar (1) not null default 'О')

GO

CREATE TABLE Начисления (Код int IDENTITY Primary key, Код_вида_начисления int, Код_табеля int, Коэффициент float)

GO

CREATE TABLE Удержания (Код int IDENTITY Primary key, Код_вида_удержания int, Код_табеля int, Коэффициент float)

GO

—Определение связей между таблицами

ALTER TABLE Сотрудники ADD CONSTRAINT FK_Сотрудники_Отделы FOREIGN KEY (Код_отдела) REFERENCES Отделы

GO

ALTER TABLE Назначения ADD CONSTRAINT FK_Назначения_Сотрудники FOREIGN KEY (Код_сотрудника) REFERENCES Сотрудники

GO

ALTER TABLE Назначения ADD CONSTRAINT FK_Назначения_Должности FOREIGN KEY (Код_должности) REFERENCES Должности

GO

ALTER TABLE Табель ADD CONSTRAINT FK_Табель_Назначения FOREIGN KEY (Код_назначения) REFERENCES Назначения

GO

ALTER TABLE Табель ADD CONSTRAINT FK_Табель_Даты FOREIGN KEY (Код_даты) REFERENCES Даты

GO

ALTER TABLE Начисления ADD CONSTRAINT FK_Начисления_Табель FOREIGN KEY (Код_табеля) REFERENCES Табель

GO

ALTER TABLE Удержания ADD CONSTRAINT FK_Удержания_Табель FOREIGN KEY (Код_табеля) REFERENCES Табель

GO

ALTER TABLE Удержания ADD CONSTRAINT FK_Удержания_Виды_удержаний FOREIGN KEY (Код_вида_удержания) REFERENCES Виды_удержаний

GO

—Журнал изменений

Create 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)

go

4. Схема диалога пользователя с приложением

4.1 Корректировка таблиц-справочников

Для того чтобы перейти к корректировке справочников, необходимо нажать на соответствующую кнопку в главном окне или зайти в пункт меню «Ведение» и выбрать в нем необходимый справочник. Меню представлено на рисунке 4.1.

Рисунок 4.1 — Меню редактирования справочников В открывшемся окне, пример которого представлен на рисунке 4.2, можно непосредственно в таблице отредактировать данные. Для перемещения между записями следует использовать кнопки в правой части окна или курсорные клавиши. Для того чтобы добавить, изменить или удалить запись нужно нажать соответствующую кнопку или горячую клавишу. Можно отсортировать данные в столбце по возрастанию и по убыванию нажатием на его заголовок. Пример кода для работы со справочниками приведен в приложении А. При редактировании таблицы «Виды начислений» используются триггеры контроля ссылочной целостности (№ 1) и контроля данных (№ 10). При вставке данных в таблицу «Даты» осуществляется каскадная вставка в таблицу «Табель» с помощью соответствующего триггера (№ 6).

Рисунок 4.2 — Окно редактирования справочника При удалении записи необходимо подтверждение выполняемого действия в диалоговом окне представленном на рисунке 4.3.

Рисунок 4.3 — Запрос подтверждения удаления

4.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 — Окно редактирования начислений и удержаний

4.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 — Используемые представления

SQL-текст представлений:

— Расширенная информация о назначениях

CREATE VIEW full_appointments_info AS

SELECT Назначения. Код Код, Отделы. Название Отдел, ФИО,

Должности.Название Должность, Дата_назначения, Дата_ухода, Отделы. Код КодОтдела FROM Назначения, Должности, Сотрудники,

Отделы WHERE Код_должности = Должности. Код

and Код_сотрудника= Сотрудники. Код

and Код_отдела = Отделы. Код

— Обновляемое представление — сотрудники с указанием отделов

CREATE VIEW Сотрудники_отделы AS

SELECT Код = сотрудники. Код + 0, ФИО, Дата_рождения,

Код_отдела = Код_отдела + 0, название отдел FROM сотрудники,

Отделы WHERE Код_отдела = Отделы. Код

5.2 Использование хранимых процедур

Используемые хранимые процедуры приведены в таблице 5.2.

Таблица 5.2 — Используемые хранимые процедуры

SQL-текст процедур:

—Откат изменений по журналу

—Параметр — последняя запись, которая остается без изменений

create proc restore_appointments @last_ok_id int

as

declare logc cursor for

select oper, anum, aworker, aposition, astart, aend

from appointments_log

where id > @last_ok_id

order by id desc

declare @oper char, @anum int, @aworker int, @aposition int,

@astart datetime, @aend datetime

set identity_insert назначения on

open logc

fetch logc into @oper, @anum, @aworker,

@aposition, @astart, @aend

while @@fetch_status = 0 begin

if (@oper = 'I')

delete from назначения where код = @anum

else if (@oper = 'D')

insert into назначения (Код, Код_сотрудника, Код_должности, Дата_назначения, Дата_ухода)

values (@anum,@aworker,@aposition,@astart,@aend)

else

update назначения set Код_сотрудника = @aworker,

Код_должности = @aposition,

Дата_назначения = @astart, Дата_ухода = @aend

where Код = @anum

fetch logc into @oper, @anum, @aworker,

@aposition, @astart, @aend

end

set identity_insert назначения off

close logc

deallocate logc

—возвращает таблицу с данными по зарплате

create proc table_info @date_id int as

select Табель. Код, Часы, ФИО, Должности. Название Должность, Отделы. Название Отдел, Дата, Колличество_часов, Оклад

INTO #table from Табель, Назначения, Сотрудники,

Должности, Отделы, Даты

where Код_назначения = Назначения. Код

and Код_сотрудника = Сотрудники. Код

and Код_должности = Должности. Код

and Код_отдела = Отделы. Код

and Код_даты = Даты. Код and Даты. Код = @date_id

order by дата, отдел, Фио

—вычисляемое поле с суммой оклада за отработанные часы

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

—подсчет процента для начисления и удержания

update #charges set Процент_начислений =

(SELECT SUM (Величина*Коэффициент)

FROM Виды_начислений, Начисления

WHERE Код_вида_начисления = Виды_начислений.Код

AND Код_табеля = #charges.Код

AND Тип = 'О')

update #charges set Процент_начислений =

isnull (Процент_начислений, 0)

update #charges set процент_удержаний =

(SELECT SUM (Величина*Коэффициент)

FROM Виды_удержаний, Удержания

WHERE Код_вида_удержания = Виды_удержаний.Код

AND Код_табеля = #charges.Код

AND Тип = 'О')

update #charges set процент_удержаний =

isnull (процент_удержаний, 0)

—подсчет сумм начисления и удержания

update #charges set Сумма_начислений =

(SELECT isnull (SUM (Величина*Коэффициент), 0)

FROM Виды_начислений, Начисления

WHERE Код_вида_начисления = Виды_начислений.Код

AND Код_табеля = #charges.Код

AND Тип = 'A')

update #charges set Сумма_удержаний =

(SELECT isnull (SUM (Величина*Коэффициент), 0)

FROM Виды_удержаний, Удержания

WHERE Код_вида_удержания = Виды_удержаний.Код

AND Код_табеля = #charges.Код

AND Тип = 'A')

—добавляем суммы по подсчитанным ранее процентам

update #charges set

Сумма_начислений = Сумма_начислений + сумма_за_часы *

cast (Процент_начислений as float) / 100

from #table where #charges.Код = #table.Код

update #charges set

Сумма_удержаний = Сумма_удержаний +

(сумма_за_часы + Сумма_начислений) *

cast (Процент_удержаний as float) / 100

from #table where #charges.Код = #table.Код

—расчет зарплаты

update #charges set

итого = сумма_за_часы + Сумма_начислений ;

Сумма_удержаний

from #table where #charges.Код = #table.Код

—возвращаем данные

select Дата, Отдел, ФИО, Должность, часы, сумма_за_часы,

Процент_начислений, процент_удержаний,

Cast (Сумма_начислений as numeric (10,2))

Сумма_начислений,

Cast (Сумма_удержаний as numeric (10,2)) Сумма_удержаний,

Cast (Итого as numeric (10,2)) Итого

from #table, #charges where #table.код = #charges.код

go

— Процедуры для вставки записей

— результат — код вставленной записи

CREATE PROC Insert_to_personal AS

declare @code int;

select @code = min (код) from отделы

insert into сотрудники (ФИО, Код_отдела) values ('', @code)

SELECT @@identity Код

GO

CREATE PROC Insert_to_appointments @worker_id int AS

declare @code int;

select @code = min (код) from должности

insert into назначения (код_сотрудника, дата_назначения, Код_должности) values (@worker_id, getdate (), @code)

SELECT IDENT_CURRENT ('назначения') Код

GO

CREATE PROC Insert_to_table AS

declare @code int;

select @code = min (код) from назначения

insert into табель (часы, код_назначения) values (0, @code)

SELECT @@identity Код

GO

CREATE PROC Insert_to_charges @table_id int AS

declare @code int;

select @code = min (код) from виды_начислений

insert into начисления (код_табеля, коэффициент, код_вида_начисления) values (@table_id, 1, @code)

SELECT @@identity Код

GO

CREATE PROC Insert_to_keepings @table_id int AS

declare @code int;

select @code = min (код) from виды_удержаний

insert into удержания (код_табеля, коэффициент, код_вида_удержания) values (@table_id, 1, @code)

SELECT @@identity Код

GO

5.3 Использование хранимых функций

Используемые хранимые функции приведены в таблице 5.3.

Таблица 5.3 — Используемые хранимые функции

SQL-текст функций:

— параметр — код табеля

— результат — сумма зарплаты

create function salary (@table_id int) returns numeric (10,2)

begin

declare @sum float

set @sum = 0

— сумма оклада за отработанные часы

declare @sum_by_hours float

select @sum_by_hours = cast (оклад *(cast (часы as float) /

колличество_часов) as numeric (10,2))

from табель, назначения, должности, даты

where код_назначения = назначения. код

and код_должности = должности. код

and код_даты = даты. код and табель. код = @table_id

set @sum = @sum_by_hours

— процент начислений и удержаний

declare @percent_add float

declare @percent_keep float

SELECT @percent_add = SUM (Величина*Коэффициент)

FROM Виды_начислений, Начисления

WHERE Код_вида_начисления = Виды_начислений.Код

AND Код_табеля = @table_id AND Тип = 'О'

set @percent_add = isnull (@percent_add, 0)

SELECT @percent_keep = SUM (Величина*Коэффициент)

FROM Виды_удержаний, Удержания

WHERE Код_вида_удержания = Виды_удержаний.Код

AND Код_табеля = @table_id AND Тип = 'О'

set @percent_keep = isnull (@percent_keep, 0)

— сумма начислений и удержаний

declare @sum_add float

declare @sum_keep float

SELECT @sum_add = isnull (SUM (Величина*Коэффициент), 0)

FROM Виды_начислений, Начисления

WHERE Код_вида_начисления = Виды_начислений.Код

AND Код_табеля = @table_id AND Тип = 'A'

SELECT @sum_keep = isnull (SUM (Величина*Коэффициент), 0)

FROM Виды_удержаний, Удержания

WHERE Код_вида_удержания = Виды_удержаний.Код

AND Код_табеля = @table_id AND Тип = 'A'

set @sum_add = @sum_add + @sum_by_hours * @percent_add / 100

set @sum_keep = @sum_keep + (@sum_add + @sum_by_hours) *

@percent_keep / 100

— зарплата

set @sum = @sum_by_hours + @sum_add — @sum_keep

return @sum

end

— параметр — код сотрудника

— результат — перечень назначений этого сотрудника

CREATE FUNCTION Appointments_of_worker_by_id (@worker int)

RETURNS @appointments table (Код int, Код_должности int,

Код_сотрудника int, Дата_назначения datetime,

Дата_ухода datetime, ФИО varchar (50)) AS

BEGIN

DECLARE @name varchar (50)

SELECT @name = ФИО

FROM Сотрудники

WHERE Код = @worker

INSERT INTO @appointments (Код, Код_должности, Код_сотрудника, Дата_назначения, Дата_ухода, ФИО)

SELECT Код, Код_должности, @worker,

Дата_назначения, Дата_ухода, @name

FROM Назначения WHERE Код_сотрудника = @worker

RETURN

END

5.4 Использование временных таблиц

Используемые временные таблицы приведены в таблице 5.4.

Таблица 5.4 — Используемые временные таблицы

5.5 Использование курсоров

Используемые курсоры приведены в таблице 5.4.

Таблица 5.4 — Используемые курсоры

5.6 Использование триггеров

Используемые триггеры приведены в таблице 5.6.

Таблица 5.6 — Используемые триггеры

SQL-текст триггеров:

—Триггеры контроля целостности

CREATE TRIGGER Виды_начислений_Del ON Виды_начислений

FOR DELETE

AS

IF EXISTS (SELECT * FROM Начисления WHERE Код_вида_начисления IN (SELECT Код FROM deleted))

BEGIN

raiserror ('Удаление записей невозможно, т.к. есть

связанные записи в другой таблице', 16, 1)

rollback tran

END

GO

CREATE TRIGGER Начисления_Ins ON Начисления

FOR INSERT

AS

IF (SELECT count (*) FROM inserted) <>

(SELECT count (*) FROM inserted

WHERE Код_вида_начисления IN

(SELECT Код FROM Виды_начислений))

BEGIN

raiserror ('Невозможно добавить запись: неизвестный вид начисления', 16, 1)

rollback tran

END

GO

CREATE TRIGGER Начисления_Upd ON Начисления

FOR UPDATE

AS

IF update (Код_вида_начисления) BEGIN

IF (SELECT count (*) FROM inserted) <>

(SELECT count (*) FROM inserted

WHERE Код_вида_начисления IN

(SELECT Код FROM Виды_начислений))

BEGIN

raiserror ('Невозможно изменить запись: неизвестный вид начисления', 16, 1)

rollback tran

END

END

GO

—Триггер контроля данных

CREATE TRIGGER Виды_начислений_Ins_Upd ON Виды_начислений

FOR Insert, Update

AS

IF (SELECT count (*) FROM inserted) <>

(SELECT count (*) FROM inserted

WHERE Тип IN ('А','О'))

BEGIN

raiserror ('Невозможно сохранить запись. Введен неизвестный тип начисления', 16, 1)

rollback tran

END

GO

— Триггеры ведения журнала по таблице назначения

create trigger Назначения_log on назначения

after insert, update, delete

as

declare @date datetime

set @date = getdate ()

declare @user varchar (50)

set @user = current_user

insert into appointments_log

select 'D', @user, @date, Код, Код_сотрудника, Код_должности, Дата_назначения, дата_ухода

from deleted

insert into appointments_log

select 'I',@user,@date, Код, Код_сотрудника, Код_должности, Дата_назначения, дата_ухода

from inserted

go

create trigger Назначения_log_upd on назначения

after update

as

declare @date datetime

set @date = getdate ()

declare @user varchar (50)

set @user = current_user

insert into appointments_log

select 'U', @user, @date, Код, Код_сотрудника, Код_должности,

Дата_назначения, дата_ухода

from deleted

go

—Триггер каскадной вставки в табель для подходящих назначений

create trigger Даты_Ins on Даты

after insert

as

declare @date_id int

declare @date datetime

set nocount on

DECLARE dates CURSOR FOR

SELECT Код, Дата

FROM inserted

OPEN dates

FETCH dates INTO @date_id, @date

WHILE @@Fetch_status = 0 BEGIN

insert into табель (Код_даты, Код_назначения, Часы)

select @date_id, Код, 0

from назначения where

(Дата_ухода is null or

Year (Дата_ухода) > Year (@date) or

Year (Дата_ухода) = Year (@date) and

Month (Дата_ухода) >= Month (@date))

and

(Year (Дата_назначения) < Year (@date) or

Year (Дата_назначения) = Year (@date) and

Month (Дата_назначения) < Month (@date))

FETCH dates INTO @date_id, @date

END

CLOSE dates

DEALLOCATE dates

go

—Триггеры обновляемого представления

create trigger Сотрудники_отделы_Ins on Сотрудники_отделы

instead of insert

as

insert into сотрудники (ФИО, Дата_рождения, Код_отдела)

select ФИО, Дата_рождения, d. Код

from inserted i left join отделы d

on i. Код_отдела = d. Код and i. Отдел is Null

or i. Отдел = d. Название and i. Код_отдела is null

or i. Код_отдела = d. Код and i. Отдел = d. Название

go

create trigger Сотрудники_отделы_Upd on Сотрудники_отделы

instead of update

as

if update (Отдел)

if not update (Код_отдела) begin

update сотрудники

set ФИО = i. ФИО,

Дата_рождения = i. Дата_рождения,

Код_отдела = d. Код

from сотрудники, inserted i left join отделы d

on i. Отдел = d. Название

where сотрудники. код = i. Код

end

else begin

update сотрудники

set ФИО = i. ФИО,

Дата_рождения = i. Дата_рождения,

Код_отдела = d. Код

from сотрудники, inserted i left join отделы d

on i. Отдел = d. Название

and i. Код_отдела = d. Код

where сотрудники. код = i. Код

end

else if not update (Код_отдела) begin

update сотрудники

set ФИО = i. ФИО,

Дата_рождения = i. Дата_рождения

from сотрудники, inserted i

where сотрудники. код = i. Код

end

else begin

update сотрудники

set ФИО = i. ФИО,

Дата_рождения = i. Дата_рождения,

Код_отдела = d. Код

from сотрудники, inserted i left join отделы d

on i. Код_отдела = d. Код

where сотрудники. код = i. Код

end

go

create trigger Сотрудники_отделы_Del on Сотрудники_отделы

instead of delete

as

delete from сотрудники where код in

(select код from deleted)

go

Заключение

В ходе выполнения курсовой работы были изучены стандартные функции 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);

//указание соединения с базой данных

BaseFrame->query->Connection = connection;

BaseFrame->query->Active = False;

BaseFrame->query->SQL->Text =

" SELECT Код, Название Должность, Оклад FROM Должности order by Название" ;

BaseFrame->query->Active = True;

//создание источника данных для грида

BaseFrame->dataSource = new TDataSource (this);

//связывание его с набором данных

BaseFrame->dataSource->DataSet = BaseFrame->query;

//указание источника информации для грида

BaseFrame->DBGrid->DataSource = BaseFrame->dataSource;

BaseFrame->DBGrid->Columns->Items[0]->FieldName = «Код» ;

BaseFrame->DBGrid->Columns->Items[1]->FieldName = «Должность» ;

BaseFrame->DBGrid->Columns->Items[2]->FieldName = «Оклад» ;

BaseFrame->DBGrid->Columns->Items[0]->Visible = false;

//сокрытие кнопок если нет данных

if (BaseFrame->query->IsEmpty ()) {

BaseFrame->btnDel->Enabled = false;

BaseFrame->btnEdit->Enabled = false;

}

Код позиционирования на записи. Используется после изменения информации в таблице.

//получение кода текущей записи

int k = query->FieldByName («Код»)->AsInteger;

//сохранение строки сортировки

WideString s = query->Sort;

//обновление данных

query->Close ();

query->Open ();

query->DisableControls ();

//сортировка

query->Sort = s;

//поиск записи

query->Locate («Код», k, TLocateOptions ());

query->EnableControls ();

Удаление записи.

query->Delete ();

Сохранение записи.

if (query->State == dsEdit || query->State == dsInsert)

query->Post ();

Добавление записи.

query->Insert ();

Приложение Б Работа с таблицей «Табель»

Получение данных из таблицы «Табель» за определенный период. Используется при нажатии на кнопку «Показать» в окне редактирования.

query->Active = False;

//задание нового запроса

query->SQL->Clear ();

query->SQL->Add («SELECT Табель. Код, «);

query->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»);

query->SQL->Add («ORDER BY Дата, ФИО»);

//сохранение значений параметров для последующегоиспользования

start = dateStart->Date;

end = dateEnd->Date;

//задание параметров для запроса

query->Parameters->ParamByName («start»)->Value = dateStart->Date;

query->Parameters->ParamByName («end»)->Value = dateEnd->Date;

query->Active = True;

Изменение записи табеля.

//получение кода текущей записи

int id = query->FieldByName («Код»)->AsInteger;

//создание объекта для передачи информации

TRecordInfo* x = new TRecordInfo (id);

x->SetHours (query->FieldByName («Часы»)->AsInteger);

x->SetTotal (query->FieldByName («Всего»)->AsInteger);

x->SetAppointmentId (query->FieldByName («Код_назначения»)->AsInteger);

x->SetDateId (query->FieldByName («Код_даты»)->AsInteger);

x->SetStartDate (start);

x->SetEndDate (end);

//создание окна для редактирования запси табеля

TableRecord = new TTableRecord (this, connection, x);

//открытие транзакции

connection->BeginTrans ();

TModalResult mr = TableRecord->ShowModal ();

//создание объекта для обновления информации

TADOQuery* queryUpd = new TADOQuery (this);

queryUpd->Connection = connection;

queryUpd->Active = False;

queryUpd->SQL->Clear ();

if (mr == mrOk){

//запрос на изменение записи

queryUpd->SQL->Add («UPDATE Табель SET «);

queryUpd->SQL->Add («Часы = :hours, «);

queryUpd->SQL->Add («Код_назначения = :app_id, «);

queryUpd->SQL->Add («Код_даты = :date_id «);

queryUpd->SQL->Add («WHERE Код = :id»);

//задание параметров

queryUpd->Parameters->ParamByName («id»)->Value = id;

queryUpd->Parameters->ParamByName («hours»)->Value =

x->GetHours ();

queryUpd->Parameters->ParamByName («app_id»)->Value =

x->GetAppointmentId ();

queryUpd->Parameters->ParamByName («date_id»)->Value =

x->GetDateId ();

queryUpd->ExecSQL ();

//закрытие транзакции

connection->CommitTrans ();

//обновление информации в гриде и позиционирование

int oldID = query->FieldByName («Код»)->AsInteger;

positioning (oldID);

} else {

//откат транзакции

connection->RollbackTrans ();

}

delete queryUpd;

delete TableRecord;

delete x;

Добавление записи.

//открытие транзакции

connection->BeginTrans ();

//создание объекта для выполнения хранимой процедуры

TADOStoredProc* storedProc = new TADOStoredProc (this);

storedProc->Connection = connection;

//задание имени процедуры

storedProc->ProcedureName = «Insert_to_table» ;

storedProc->Active = True;

//получение кода добавленной записи

int id = storedProc->FieldByName («Код»)->AsInteger;

//объект для передачи данных

TRecordInfo* x = new TRecordInfo (id);

x->SetStartDate (dateStart->Date);

x->SetEndDate (dateEnd->Date);

//окно редактирования

TableRecord = new TTableRecord (this, connection, x);

TModalResult mr = TableRecord->ShowModal ();

//объект для сохранения изменений

TADOQuery* queryUpd = new TADOQuery (this);

queryUpd->Connection = connection;

queryUpd->Active = False;

int oldID;

if (mr == mrOk){

queryUpd->SQL->Clear ();

queryUpd->SQL->Add («UPDATE Табель SET «);

queryUpd->SQL->Add («Часы = :hours, «);

queryUpd->SQL->Add («Код_назначения = :app_id, «);

queryUpd->SQL->Add («Код_даты = :date_id «);

queryUpd->SQL->Add («WHERE Код = :id»);

queryUpd->Parameters->ParamByName («id»)->Value = id;

queryUpd->Parameters->ParamByName («hours»)->Value =

x->GetHours ();

queryUpd->Parameters->ParamByName («app_id»)->Value =

x->GetAppointmentId ();

queryUpd->Parameters->ParamByName («date_id»)->Value =

x->GetDateId ();

queryUpd->ExecSQL ();

//запоминаем код добавленной записи для позиционирования

oldID = id;

//закрытие транзакции

connection->CommitTrans ();

} else {

//запоминаем код текущей записи для позиционирования

oldID = query->FieldByName («Код»)->AsInteger;

connection->RollbackTrans ();

}

//обновление информации в гриде и позиционирование

positioning (oldID);

delete storedProc;

delete queryUpd;

delete TableRecord;

delete x;

Удаление записи.

TADOQuery* queryUpd = new TADOQuery (this);

queryUpd->Connection = connection;

int id = query->FieldByName («Код»)->AsInteger;

queryUpd->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;

queryUpd->Parameters->ParamByName («id2»)->Value = id;

queryUpd->Open ();

if (!queryUpd->IsEmpty ()) {

//сообщение в случае существования связанных записей

ShowMessage («Невозможно удалить запись»);

delete queryUpd;

return;

}

//запрос подтверждения удаления записи

int btn;

btn = Application->MessageBox («Удалить запись?», «Удаление записи» ,

MB_YESNO);

if (btn == IDNO) {

return;

}

//сохранение кода для позиционирования

query->Next ();

int oldID = query->FieldByName («Код»)->AsInteger;

if (oldID == id) {

query->Prior ();

oldID = query->FieldByName («Код»)->AsInteger;

}

//удаление записи

queryUpd->Active = False;

queryUpd->SQL->Text = «DELETE FROM Табель WHERE Код = :id» ;

queryUpd->Parameters->ParamByName («id»)->Value = id;

queryUpd->ExecSQL ();

positioning (oldID);

delete queryUpd;

Поиск в таблице

int id = query->FieldByName («Код»)->AsInteger;

querySearch->Close ();

querySearch->SQL->Clear ();

//запрос на поиск данных

querySearch->SQL->Add («Select табель. Код FROM табель,»);

querySearch->SQL->Add («full_appointments_info, Даты»);

querySearch->SQL->Add («WHERE Фио like: name»);

querySearch->SQL->Add («and отдел like: dep»);

querySearch->SQL->Add («and должность like: pos»);

querySearch->SQL->Add («and код_назначения = full_appointments_info.Код»);

querySearch->SQL->Add («AND Код_даты = Даты. Код «);

querySearch->SQL->Add («AND Дата BETWEEN: start and: end»);

querySearch->SQL->Add («ORDER BY Дата, ФИО»);

querySearch->Parameters->ParamByName («name»)->Value =

" %" + eSearch->Text + «%» ;

querySearch->Parameters->ParamByName («dep»)->Value =

" %" + eDepartment->Text + «%» ;

querySearch->Parameters->ParamByName («pos»)->Value =

" %" + ePosition->Text + «%» ;

querySearch->Parameters->ParamByName («start»)->Value = start;

querySearch->Parameters->ParamByName («end»)->Value = end;

querySearch->Open ();

if (querySearch->IsEmpty ()){

btnSearchNext->Enabled = false;

btnSearchPrev->Enabled = false;

} else {

//если записи найдены активация возможности перехода между ними

btnSearchNext->Enabled = true;

btnSearchPrev->Enabled = true;

//и позиционирование на первой

int newId = querySearch->FieldByName («Код»)->AsInteger;

query->Locate («Код», newId, TLocateOptions ());

DBGrid->SetFocus ();

}

Обновление данных в гриде и позиционирование. Используется при вставке, изменении, удалении записей.

WideString s = query->Sort;

query->Close ();

query->Open ();

query->DisableControls ();

query->Sort = s;

query->Locate («Код», key, TLocateOptions ());

query->EnableControls ();

DBGrid->SetFocus ();

Приложение В Построение печатных форм Получение списка сотрудников для построения отчета со списком должностей.

query2 = new TADOQuery (this);

dataSource = new TDataSource (this);

query2->Connection = connection;

query2->Close ();

//получение списка сотрудников

query2->SQL->Add («Select Код, ФИО»);

query2->SQL->Add («from Сотрудники»);

query2->SQL->Add («order by ФИО»);

query2->Open ();

if (query2->IsEmpty ()){

//выход если сотрудников нет

ShowMessage («Нет работников»);

Close ();

}

//отображение полученного списка

dataSource->DataSet = query2;

DBLookupComboBox1->ListSource = dataSource;

DBLookupComboBox1->ListField = «ФИО» ;

DBLookupComboBox1->KeyField = «Код» ;

Построение отчета, после выбора сотрудника.

//Получение кода выбранного сотрудника

int worker = query2->FieldByName («Код»)->AsInteger;

query->Connection = connection;

query->Close ();

query->SQL->Clear ();

//Получение списка должностей с использование хранимой функции

query->SQL->Add («Select Должности. Название Должность,»);

query->SQL->Add («Дата_назначения, Дата_ухода, ФИО»);

query->SQL->Add («From Appointments_of_worker_by_id (:wid), Должности»);

query->SQL->Add («where Код_должности = должности. код»);

query->SQL->Add («Order by Дата_назначения»);

query->Parameters->ParamByName («wid»)->Value = worker;

query->Open ();

if (query->IsEmpty ()){

QRLabel1->Caption = «Нет данных» ;

} else {

QRLabel1->Caption = «Назначения:» ;

}

lFio->Caption = query->FieldByName («ФИО»)->AsString;

//связывание отчета и полей вывода с набором данных

QuickRep1->DataSet = query;

tPosition->DataSet = query;

tPosition->DataField = «Должность» ;

tStart->DataSet = query;

tStart->DataField = «Дата_назначения» ;

tEnd->DataSet = query;

tEnd->DataField = «Дата_ухода» ;

QuickRep1->Preview ();

Приложение Г Построение диаграммы Код для построения сравнительной диаграммы зарплат в отделах за выбранный период.

query->Active = False;

query->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»);

query->SQL->Add («group by Отделы. Название»);

query->SQL->Add («order by Отделы. Название»);

query->Parameters->ParamByName («start»)->Value = dateStart->Date;

query->Parameters->ParamByName («end»)->Value = dateEnd->Date;

query->Open ();

if (!query->IsEmpty ()) {

Chart->Title->Text->Clear ();

Chart->Title->Text->Add («Зарплата по отделам за «);

Chart->Title->Text->Add (dateStart->Date.DateString ());

Chart->Title->Text->Add (dateEnd->Date.DateString ());

} else {

Chart->Title->Text->Clear ();

Chart->Title->Text->Add («Нет данных за этот период»);

}

Chart->SeriesList->Clear ();

//серия для диаграммы

TBarSeries* x = new TBarSeries (this);

AnsiString name = «» ;

Currency sum = 0;

//проход по всем возвращенным записям

while (!query->Eof){

name = query->FieldByName («Отдел»)->AsString;

sum = query->FieldByName («Зарплата»)->AsCurrency;

//добавление столбца в серию

x->AddBar (sum, name, clBlue);

//переход к следующей записи

query->Next ();

}

Chart->AddSeries (x);