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

CREATE TABLE Назначения (Код int IDENTITY Primary key, Код_должности int, Код_сотрудника int, Дата_назначения datetime, Дата_ухода datetime) SELECT Назначения.Код Код, Отделы.Название Отдел, ФИО, Должности.Название Должность, Дата_назначения, Дата_ухода, Отделы.Код КОДОТДЕЛА FROM Назначения, Должности, Сотрудники, Отделы WHERE Код_должности = Должности.Код and Код_сотрудника= Сотрудники.Код and Код_отдела = Отделы.Код SELECT Код = сотрудники.Код 0, ФИО, Дата_рождения, Код_отдела = Код_отдела 0, название отдел FROM сотрудники, Отделы WHERE Код_отдела = Отделы.Код -Параметр - последняя запись, которая остается без изменений 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 INTO #table from Табель, Назначения, Сотрудники, Должности, Отделы, Даты where Код_назначения = Назначения.Код and Код_сотрудника = Сотрудники.Код and Код_должности = Должности.Код and Код_отдела = Отделы.Код and Код_даты = Даты.Код and Даты.Код = @date_id order by дата, отдел, ФиоВ ходе выполнения курсовой работы были изучены стандартные функции MS SQL SERVER и разработано «клиент-серверное» приложение «Автоматизированная система управления отделом бухгалтерии по расчету зарплаты». При работе над приложением была: - спроектирована база данных, соответствующая предметной области задачи;BASEFRAME->query = new TADOQUERY(this); BASEFRAME->query->Connection = connection; BASEFRAME->query->Active = False; "SELECT Код,Название Должность,Оклад FROM Должности order by Название"; BASEFRAME->query->Active = True;query->SQL->Add("Колличество_часов Всего, Код_назначения, Код_даты "); query->SQL->Add("WHERE Код_отдела = Отделы.Код "); query->SQL->Add("AND Код_должности = Должности.Код "); query->SQL->Add("AND Код_даты = Даты.Код "); query->SQL->Add("AND Код_назначения = Назначения.Код ");Получение списка сотрудников для построения отчета со списком должностей. query2 = new TADOQUERY(this); //получение списка сотрудников query2->SQL->Add("Select Код, ФИО"); query2->SQL->Add("from Сотрудники"); //Получение списка должностей с использование хранимой функции query->SQL->Add("Select Должности.Название Должность,"); query->SQL->Add("where Код_должности = должности.

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

Их использование позволяет: - повысить надежность, целостность и сохранность данных;

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

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

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

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

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

Целью курсовой работы является изучение средств работы с системами управления базами данных, на примере 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], [2].

Функции агрегации применяются к наборам записей и часто используются в сочетании с оператором 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. Структура базы данных приложения

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

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

Наименование Общие сведения о содержимом

Отделы Справочник отделов предприятия

Сотрудники Список сотрудников предприятия

Должности Справочник должностей предприятия

Назначения Список назначений сотрудников

Даты Справочник дат начисления зарплаты

Виды_начислений Справочник видов начислений

Виды_удержаний Справочник видов удержаний

Начисления Список начислений для записей табеля

Удержания Список удержаний для записей табеля

Табель Список записей табеля начисления зарплаты appointments_log Журнал изменений таблицы «Назначения»

Схема базы данных представлена на рисунке 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»

Наименование поля Формат поля Содержимое поля id числовой, счетчик уникальный код записи журнала oper текстовый произведенная операция t_user текстовый пользователь, выполнивший действие date дата/время дата совершения действия anum числовой код измененной записи aworker числовой код сотрудника aposition числовой код должности astart дата/время дата назначения на должность aend дата/время дата ухода с должности

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

№ п/п Название Назначение Где используется

1 full_appointments_info Получение расширенной информации о назначениях, включая отдел, должность, ФИО сотрудника. Используется при редактировании записей табеля для получения информации о назначениях сотрудников. Также это представление применяется при построении отчета по сотрудникам отдела.

2 Сотрудники_отделы Получение информации о сотрудниках вместе с названиями отделов с возможностью редактирования. Используется при редактировании информации о сотрудниках для получения и обновления сведений о сотруднике.

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 - Используемые хранимые процедуры

№ п/п Название Назначение Где используется

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

2 table_info Получение сведений о зарплате в виде таблицы, которая также содержит сведения о суммах начислений и удержаний. Используется при построении отчета с информацией о зарплате.

3 Insert_to_personal Вставка записи в таблицу «Сотрудники» и возвращение ее кода. Используется при добавлении записей в таблицу «Сотрудники»

4 Insert_to_appointments Вставка записи в таблицу «Назначения» и возвращение ее кода. Используется при добавлении записей в таблицу «Назначения»

5 Insert_to_table Вставка записи в таблицу «Табель» и возвращение ее кода. Используется при добавлении записей в таблицу «Табель»

6 Insert_to_charges Вставка записи в таблицу «Начисления» и возвращение ее кода. Используется при добавлении записей в таблицу «Начисления»

7 Insert_to_keepings Вставка записи в таблицу «Удержания» и возвращение ее кода. Используется при добавлении записей в таблицу «Удержания»

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 - Используемые хранимые функции

№ п/п Название Назначение Где используется

1 salary Получение суммы зарплаты для записи табеля. Используется при формировании отчета с информацией о зарплате. Также применена для построении диаграммы зарплат отделов.

2 Appointments_of_worker_by_id Получение информации о назначения сотрудника. Используется при построении отчета по назначениям сотрудника.

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 - Используемые временные таблицы

№ п/п Название Назначение Где используется

1 #table Содержит базовую информацию по записям табеля, включая начисляемый оклад. Используется в процедуре №2

2 #charges Содержит дополнительную информацию по записям табеля и величину зарплаты, а также промежуточные результаты для ее расчета. Используется в процедуре №2

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

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

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

№ п/п Название Назначение Где используется

1 logc Проход по записям журнала изменений. Используется в процедуре №1

2 dates Проход по добавляемым датам. Используется в триггере №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

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 с. сервер печатный справочник триггер