Размещено на http://www.allbest.ru/

Міністерство освіти і науки України

Запорізька державна інженерна академія

Факультет автоматизованих систем виробництва

Кафедра програмного забезпечення автоматизованих систем

КУРСОВА РОБОТА

з курсу «Організація баз даних та знань»

тема: Інформаційна система фотоцентру

Спеціальність 7.05010301 «Програмне забезпечення систем»

Запоріжжя 2014



?????

1. ????????

2. ???? ??????

2.1 ????????? ??? ????????? ????????

2.2 ????????? ??? ???? ?????????????

2.3 ???? ????????? ???????

2.4 ???? Windows ??????????

2.5 ???? Web-??????????

??????????



1. Завдання

Для приведеної нижче задачі виповнюю наступний комплекс робіт:

1. Побудувати інформаційну концептуальну модель (ER - модель), необхідно для того щоб:

· Проаналізувати предметну область, при необхідності уточнити і удосконалити її, виявити необхідний набір сутностей

· Визначити потрібний набір атрибутів для кожної сутності, виділивши ідентифікуючий набір атрибутів

· Визначити зв'язки між об'єктами, формалізувати зв'язки між об'єктами ( множина и т.д.)

· Описати одержану ER - модель на язику ER - діаграми

2. Отримати реляційну схему із ER - моделі, для того щоб:

· Побудувати набір необхідних відносин бази даних;

· Виділити первісний і зовнішній ключі визначених відносин;

· Привести отримані відносини до третьої нормальної форми;

· Визначити обмеження цілісності для зовнішніх ключів відносин і для відносин взагалі;

· Представити зв'язки між зовнішніми і первинними ключами;

3. Використовуючи СУБД MS SQL Server 2000-2005 створити спроектовану базу даних на SQL.

4. Виконати на SQL необхідні дії з додавання, модифікації і видалення даних в таблицях створеної бази даних;

5. На мові SQL для вказаних у варіанті завдання запитів створити вистави(змінні і незмінні).

6. Перевірити працездатність вистав в інтерактивному режимі.

7. Призначені для користувача застосування

· Створити Windows- інтерфейс користувача і Web- інтерфейс.

· Створити систему безпеки.

Вивчити предметну область задану згідно варіанту. Продумати та спроектувати схему бази даних, яка має містити не менше 10 таблиць, та не менше 20 записів у кожній з них (за виключенням тих, які по змісту цього не потребують). Заповнити базу.

Інформаційна система фотоцентру

Фотоцентр має головний офіс і мережу філій і кіосків прийому замовлень, розташованих за певними адресами. Філії та кіоски розрізняються кількістю робочих місць. У кіосках здійснюється тільки прийом замовлень, тому кожен кіоск пpикpіплено до певної філії, в якій ці замовлення виконуються. У філіях є необхідне обладнання для пpоявки плівок і друку фотографій. Філії та кіоски приймають замовлення на пpоявку плівок, друк фотографій і пpоявку та друк разом. У замовленні на друк вказується кількість фотографій з кожного кадру, загальна кількість фотографій, формат, тип паперу та термін виконання замовлення. При замовленні великої кількості фотографій передбачаються знижки. Термінові замовлення приймаються тільки у філіях і вони мають ціну в два pази більшу, ніж звичайне замовлення. При придбанні дисконтної картки клієнт одержує значні знижки на друк фотографій. Плівка, яку придбали в тій самій філії, куди її принесли на пpоявку, проявляється безкоштовно. Клієнтів по всій базі розділяються на професіоналів і любителів. Професіоналам, які приносять замовлення в одну ту й саму філію, можуть бути запропоновані персональні знижки. Фотомагазин та кіоски пропонують на продаж різноманітні фототовари: фотоплівки, фотоапарати, альбоми. Фотомагазин також пропонує додаткові види послуг: фотографії на документи, реставрація фотографій, прокат фотоапаратів, художнє фото, послуги професійного фотографа. Відомості про виконані замовлення та продаж різних фототоварів збираються й обробляються, і на основі цієї інформації робиться загальне замовлення на поставку матеріалів (фотобумага, фотоплівка, хімічні реактиви), фототоварів і обладнання. Отримані товари і матеріали розподіляються відповідно до запитів по кіосках і магазинах. У фотоцентру може бути декілька постачальників, що спеціалізуються на різних поставках, або на постачанні фототоварів різних фірм.

Види запитів в інформаційній системі:

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

2. Одержати перелік і загальну кількість замовлень на фотороботи по філіях, кіоскам прийому замовлень, в цілому по фотоцентру, що надійшли протягом деякого періоду часу.

3. Одержати перелік і загальну кількість замовлень (окремо простих і термінових) на окремі види фоторобіт, що надійшли протягом деякого періоду часу, до вказаної філії, кіоску прийому замовлень.

4. Одержати суму виторгу із замовлень (окремо простих і термінових) на окремі види фоторобіт, що надійшли протягом деякого періоду часу, до вказаної філії, кіоску прийому замовлень.

5. Отримати кількість надрукованих фотографій у рамках простих і термінових замовлень фотоцентру в цілому за певний період часу.

6. Отримати кількість проявлених фотоплівок в рамках простих і термінових замовлень вказаної філії, кіоску прийому замовлень, фотоцентру в цілому за певний період часу.

7. Отримати перелік постачальників у цілому по фотоцентру, постачальників окремих видів фототоварів, що зробили поставки в певний період, поставки певного обсягу.

8. Отримати список клієнтів в цілому по фотоцентру, клієнтів зазначеної філії, які мають знижки; що зробили замовлення певного обсягу.

9. Одержати суму виторгу від реалізації фототоварів в цілому по фотоцентру, вказаної філії; проданих протягом деякого періоду часу.

10. Отримати перелік фототоварів і фірм, що їх виробляють, які користуються найбільшим попитом у цілому по фотоцентру, у зазначеному філії.

11. Отримати перелік реалізованих фототоварів та обсяги їх реалізації в цілому по фотоцентру, вказаній філії, проданих протягом деякого періоду часу.

12. Отримати перелік робочих місць фотоцентру в цілому та зазначеного профілю.



2. Опис логіки

2.1 Відомості про структуру програми

Рисунок 1

Програма складається з серверної частини, додатку для користувача, який працює у ОС Windows та web-інтерфейсу. Серверна частина - це Microsoft SQL Server 2008 з встановленою базою даних. Схема бази даних представлена на рисунку 1.



2.2 Відомості про мову програмування

Windows та web клієнт написаний на мові С# у середовищі Microsoft Visual Studio 2010 з використанням ADO.NET. Код бази даних написаний за допомогою Microsoft SQL Server Management Studio на мові Transact-SQL.

2.3 Опис серверної частини

Нижче приведено код створення бази даних на мові Transact-SQL. Код складається с декількох частин, спочатку створюються необхідні таблиці та індекси, потім створюються необхідні представлення, функції та процедури. Для забезпечення цілісності базі даних створюються тригери, а для забезпечення безпеки створюються ролі та користувачі.

-------------------------------------------------------------

-- Створення бази даних PhotoCenterDB --

-------------------------------------------------------------

USE master;

GO

IF DB_ID (N'PhotoCenterDB') IS NOT NULL

DROP DATABASE PhotoCenterDB;

GO

CREATE DATABASE PhotoCenterDB

ON ( NAME = photo_dat, FILENAME = 'c:\photo_dat.mdf')

LOG ON ( NAME = photo_log, FILENAME = 'c:\photo_dat.ldf')

GO

SET DATEFORMAT dmy

GO

USE PhotoCenterDB

GO

-- Таблиці

CREATE TABLE Должности (

"IDДолжности" tinyint PRIMARY KEY,

"Название" nvarchar(20) NOT NULL,

"Оклад" money CHECK("Оклад">0)

)

GO

CREATE TABLE Филиалы (

"ID" tinyint PRIMARY KEY,

"Адрес" nvarchar(50) NOT NULL,

"Киоск" bit DEFAULT 0,

"IDФилиала" tinyint FOREIGN KEY REFERENCES "Филиалы" ON UPDATE NO ACTION ON DELETE NO ACTION

)

GO

CREATE TABLE Сотрудники (

"IDСотрудника" smallint IDENTITY(1,1) PRIMARY KEY,

"IDДолжности" tinyint FOREIGN KEY REFERENCES "Должности" ON UPDATE CASCADE ON DELETE SET NULL,

"IDФилиала" tinyint FOREIGN KEY REFERENCES "Филиалы" ON UPDATE CASCADE ON DELETE SET NULL,

"Фамилия" nvarchar(30) NOT NULL,

"Имя" nvarchar(15) NOT NULL,

"Отчество" nvarchar(15) NOT NULL,

"UserName" nvarchar(15)

)

GO

CREATE TABLE "Типы клиентов" (

"IDТипКлиента" tinyint PRIMARY KEY,

"Описание" nvarchar(20) NOT NULL

)

GO

CREATE TABLE Клиенты (

"IDКлиента" bigint IDENTITY(1,1) PRIMARY KEY,

"IDТипКлиента" tinyint FOREIGN KEY REFERENCES "Типы клиентов" ON UPDATE CASCADE ON DELETE SET NULL,

"Фамилия" nvarchar(30) NOT NULL,

"Имя" nvarchar(15) NOT NULL,

"Отчество" nvarchar(15) NOT NULL

)

GO

CREATE TABLE Заказы (

"IDЗаказа" int IDENTITY(1,1) PRIMARY KEY,

"IDФилиала" tinyint FOREIGN KEY REFERENCES Филиалы ON UPDATE NO ACTION ON DELETE NO ACTION,

"Дата приёма" datetime DEFAULT GETDATE(),

"Срочный" bit DEFAULT 0,

"IDКлиента" bigint FOREIGN KEY REFERENCES Клиенты ON UPDATE CASCADE ON DELETE SET NULL,

"IDСотрудника" smallint FOREIGN KEY REFERENCES Сотрудники ON UPDATE CASCADE ON DELETE SET NULL,

)

GO

CREATE TABLE "Дисконтные карты" (

"IDКарты" int PRIMARY KEY,

"IDКлиента" bigint UNIQUE FOREIGN KEY REFERENCES Клиенты ON UPDATE CASCADE ON DELETE CASCADE,

"Скидка" float NOT NULL

)

GO

CREATE TABLE "Проданные плёнки" (

"IDПлёнки" int IDENTITY(1,1) PRIMARY KEY,

"IDКлиента" bigint FOREIGN KEY REFERENCES Клиенты ON UPDATE CASCADE ON DELETE CASCADE,

"IDСотрудника" smallint FOREIGN KEY REFERENCES Сотрудники ON UPDATE CASCADE ON DELETE SET NULL,

"IDФилиала" tinyint FOREIGN KEY REFERENCES Филиалы ON UPDATE NO ACTION ON DELETE NO ACTION

)

GO

CREATE TABLE Продажи (

"IDПродажи" bigint IDENTITY(1,1) PRIMARY KEY,

"Дата" datetime DEFAULT GETDATE(),

"IDФилиала" tinyint FOREIGN KEY REFERENCES Филиалы ON UPDATE NO ACTION ON DELETE NO ACTION,

"IDСотрудника" smallint FOREIGN KEY REFERENCES Сотрудники ON UPDATE CASCADE ON DELETE SET NULL

)

GO

CREATE TABLE Категории (

"IDКатегории" smallint PRIMARY KEY,

"Описание" nvarchar(100) NOT NULL

)

GO

CREATE TABLE Поставщики (

"IDПоставщика" smallint PRIMARY KEY,

"Название" nvarchar(20) NOT NULL,

"Адрес" nvarchar(50)

)

GO

CREATE TABLE Товары (

"IDТовара" int IDENTITY(1,1) PRIMARY KEY,

"Описание" nvarchar(100),

"Цена" money CHECK("Цена">0),

"IDКатегории" smallint FOREIGN KEY REFERENCES Категории ON UPDATE CASCADE ON DELETE SET NULL,

"IDПоставщика" smallint FOREIGN KEY REFERENCES Поставщики ON UPDATE CASCADE ON DELETE SET NULL

)

GO

CREATE TABLE "Описание продажи" (

"IDПродажи" bigint NOT NULL,

"IDТовара" int NOT NULL,

"Количество" int CHECK ("Количество">0),

CONSTRAINT "PK_Описание_продажи" PRIMARY KEY

(

"IDПродажи",

"IDТовара"

),

CONSTRAINT "FK_Описание_продажи_Продажи" FOREIGN KEY

(

"IDПродажи"

) REFERENCES Продажи ("IDПродажи") ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT "FK_Описание_продажи_Продукты" FOREIGN KEY

(

"IDТовара"

) REFERENCES "Товары" ("IDТовара") ON UPDATE CASCADE ON DELETE CASCADE

)

GO

CREATE TABLE "Типы услуг" (

"IDТипУслуги" int PRIMARY KEY,

"Описание" nvarchar(100) NOT NULL,

"Стоимость" money CHECK("Стоимость">0)

)

GO

CREATE TABLE "Дополнительные услуги" (

"IDУслуги" int IDENTITY(1,1) PRIMARY KEY,

"IDТипУслуги" int FOREIGN KEY REFERENCES "Типы услуг" ON UPDATE CASCADE ON DELETE SET NULL,

"IDФилиала" tinyint FOREIGN KEY REFERENCES Филиалы ON UPDATE NO ACTION ON DELETE NO ACTION,

"Дата" datetime DEFAULT GETDATE(),

"IDКлиента" bigint FOREIGN KEY REFERENCES Клиенты ON UPDATE CASCADE ON DELETE SET NULL,

"IDСотрудника" smallint FOREIGN KEY REFERENCES Сотрудники ON UPDATE CASCADE ON DELETE SET NULL,

)

GO

CREATE TABLE "Тип Бумаги" (

"IDТипаБумаги" smallint PRIMARY KEY,

"Описание" nvarchar(100) NOT NULL

)

GO

CREATE TABLE "Формат Бумаги" (

"IDФормат" smallint PRIMARY KEY,

"Описание" nvarchar(100) NOT NULL

)

GO

CREATE TABLE "Типы фотографий" (

"IDТипаФото" smallint PRIMARY KEY,

"Цена" money CHECK("Цена">0),

"IDФормат" smallint FOREIGN KEY REFERENCES "Формат Бумаги" ON UPDATE CASCADE ON DELETE SET NULL,

"IDТипаБумаги" smallint FOREIGN KEY REFERENCES "Тип Бумаги" ON UPDATE CASCADE ON DELETE SET NULL

)

GO

CREATE TABLE Плёнка (

"IDПлёнки" int PRIMARY KEY

)

GO

CREATE TABLE "Описание заказа" (

"IDОписанияЗаказа" int IDENTITY(1,1) PRIMARY KEY,

"IDЗаказа" int FOREIGN KEY REFERENCES "Заказы" ON UPDATE CASCADE ON DELETE CASCADE,

"Проявка" bit,

"Печать" bit,

"IDПлёнки" int FOREIGN KEY REFERENCES "Плёнка" ON UPDATE CASCADE ON DELETE CASCADE

)

GO

CREATE TABLE Кадры (

"IDКадра" bigint IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,

"IDПленки" int FOREIGN KEY REFERENCES Плёнка ON UPDATE CASCADE ON DELETE CASCADE,

"НомерКадра" tinyint,

"Количество" tinyint CHECK("Количество">0),

"IDТипаФото" smallint FOREIGN KEY REFERENCES "Типы фотографий" ON UPDATE CASCADE ON DELETE SET NULL

)

GO

-- Індекси

CREATE CLUSTERED INDEX ИндексПоПленкам ON Кадры (IDПленки)

GO

CREATE INDEX ИндексКлиентов ON Клиенты(Фамилия,Имя,Отчество)

GO

CREATE INDEX ИндексПоКлиентамВЗаказах ON Заказы (IDКлиента)

GO

-- Функціі

CREATE FUNCTION СтоимостьЗаказа ( @IDЗаказа int)

RETURNS MONEY

AS

BEGIN

DECLARE @Сумма MONEY

DECLARE @СуммаЗаПроявку MONEY

DECLARE @Скидка float

DECLARE @СкидкаДисконт float

DECLARE @Срочный bit

SELECT @Срочный = Срочный FROM Заказы WHERE Заказы.IDЗаказа = @IDЗаказа

SELECT @Скидка = (CASE WHEN IDТипКлиента = 1THEN 1 WHEN IDТипКлиента = 2 THEN 0.95 END)

FROM Заказы INNER JOIN Клиенты ON Заказы.IDКлиента = Клиенты.IDКлиента

WHERE Заказы.IDЗаказа = @IDЗаказа

SELECT @СкидкаДисконт = Скидка

FROM [Дисконтные карты] INNER JOIN (Заказы INNER JOIN Клиенты ON Заказы.IDКлиента = Клиенты.IDКлиента)

ON Клиенты.IDКлиента = [Дисконтные карты].IDКлиента

WHERE Заказы.IDЗаказа = @IDЗаказа

IF @СкидкаДисконт IS NOT NULL

SET @Скидка = @Скидка * ((100-@СкидкаДисконт)/100)

--Сумма за печать фотографий

SELECT @Сумма = SUM(Количество*Цена)

FROM (((Заказы INNER JOIN [Описание заказа] ON [Описание заказа].IDЗаказа = Заказы.IDЗаказа)

INNER JOIN Плёнка ON [Описание заказа].IDПлёнки = Плёнка.IDПлёнки )

INNER JOIN Кадры ON Кадры.IDПленки = Плёнка.IDПлёнки) INNER JOIN [Типы фотографий] ON [Типы фотографий].IDТипаФото = Кадры.IDТипаФото

WHERE Заказы.IDЗаказа = @IDЗаказа AND Печать = 1

--Сумма за проявку

SELECT @СуммаЗаПроявку = COUNT(*) * 5

FROM Заказы INNER JOIN [Описание заказа] ON [Описание заказа].IDЗаказа = Заказы.IDЗаказа

WHERE Заказы.IDЗаказа = @IDЗаказа AND Проявка = 1

IF @Сумма IS NULL

SET @Сумма = 0

IF @Срочный = 0

SET @Сумма =((@Сумма + @СуммаЗаПроявку)*@Скидка)

IF @Срочный = 1

SET @Сумма =(((@Сумма + @СуммаЗаПроявку)*@Скидка)*2)

RETURN @Сумма

END

GO

CREATE FUNCTION СтоимостьПродажи ( @IDПродажи int)

RETURNS MONEY

AS

BEGIN

DECLARE @Сумма MONEY

SELECT @Сумма = SUM(Цена*Количество)

FROM (Продажи INNER JOIN [Описание продажи] ON Продажи.IDПродажи =[Описание продажи].IDПродажи)

INNER JOIN Товары ON Товары.IDТовара = [Описание продажи].IDТовара

WHERE Продажи.IDПродажи = @IDПродажи

IF @Сумма IS NULL

SET @Сумма = 0

RETURN @Сумма

END

GO

CREATE FUNCTION ЗаказыЗаСегодня ()

RETURNS TABLE

AS

RETURN (

SELECT * FROM Заказы WHERE DATEPART(yyyy,Заказы.[Дата приёма]) = DATEPART(yyyy,GETDATE()) AND

DATEPART(dy,Заказы.[Дата приёма]) = DATEPART(dy,GETDATE()))

GO

CREATE FUNCTION Заказы_филиала( @fil tinyint)

RETURNS Table

AS

RETURN(SELECT * FROM Заказы WHERE Заказы.IDФилиала=@fil )

GO

-- Представлення

CREATE VIEW Список_всех_филиалов AS

SELECT *

FROM Филиалы

GO

CREATE VIEW Список_киосков_по_филиалам AS

SELECT ф1.ID,ф1.Адрес AS [Адрес филиала],ф2.ID AS IDКиоска,ф2.Адрес AS [Адрес киоска]

FROM Филиалы ф1, Филиалы ф2

WHERE ф1.ID = ф2.IDФилиала

GO

CREATE VIEW Список_филиалов AS

SELECT *

FROM Филиалы

WHERE Киоск = 0

GO

CREATE VIEW Список_киосков AS

SELECT *

FROM Филиалы

WHERE Киоск = 1

GO

CREATE VIEW Поставщики_по_категориям

AS

SELECT Distinct Поставщики.Название,Категории.Описание

FROM (Поставщики INNER JOIN Товары ON Поставщики.IDПоставщика = Товары.IDПоставщика) INNER JOIN Категории ON Категории.IDКатегории = Товары.IDКатегории

GO

CREATE VIEW Поставщики_по_товарам

AS

SELECT Поставщики.Название, Товары.Описание

FROM (Поставщики INNER JOIN Товары ON Поставщики.IDПоставщика = Товары.IDПоставщика)

GO

CREATE VIEW СписокЗаказов AS

SELECT Заказы.IDЗаказа AS ID, Клиенты.IDКлиента, Филиалы.ID AS IDФилиала, Сотрудники.IDСотрудника, [Типы клиентов].IDТипКлиента,

Сотрудники.Фамилия + ' ' + SUBSTRING(Сотрудники.Имя,0,2)+ '. ' + SUBSTRING(Сотрудники.Отчество,0,2) +'.' AS Сотрудник,

Филиалы.Адрес AS Филиал,[Дата приёма], Срочный AS Срочность,

Клиенты.Фамилия + ' ' + SUBSTRING(Клиенты.Имя,0,2)+ '. ' + SUBSTRING(Клиенты.Отчество,0,2) +'.' AS Клиент,

[Типы клиентов].Описание AS [Тип клиента],dbo.СтоимостьЗаказа(Заказы.IDЗаказа)AS Стоимость

FROM ((((Заказы INNER JOIN Филиалы ON Заказы.IDФилиала = Филиалы.ID)

INNER JOIN Клиенты ON Клиенты.IDКлиента = Заказы.IDКлиента) )INNER JOIN Сотрудники ON Сотрудники.IDСотрудника = Заказы.IDСотрудника)

INNER JOIN [Типы клиентов] ON [Типы клиентов].IDТипКлиента = Клиенты.IDТипКлиента

GO

CREATE VIEW СписокКлиентов AS

SELECT IDКлиента,Клиенты.Фамилия + ' ' + SUBSTRING(Клиенты.Имя,0,2)+ '. ' + SUBSTRING(Клиенты.Отчество,0,2) +'.' AS Клиент

FROM Клиенты

GO

CREATE VIEW СписокТиповБумаг AS

SELECT [Типы фотографий].IDТипаФото, [Тип Бумаги].Описание + ' ' + [Формат Бумаги].Описание AS Описание

FROM ([Типы фотографий] INNER JOIN [Формат Бумаги] ON [Типы фотографий].IDФормат = [Формат Бумаги].IDФормат)

INNER JOIN [Тип Бумаги] ON [Тип Бумаги].IDТипаБумаги = [Типы фотографий].IDТипаБумаги

GO

CREATE VIEW ОписаниеПлёнки AS

SELECT Кадры.IDПленки,Кадры.НомерКадра,Кадры.Количество, [Тип Бумаги].Описание AS Бумага, [Формат Бумаги].Описание AS Формат

FROM(([Типы фотографий] INNER JOIN [Формат Бумаги] ON [Типы фотографий].IDФормат = [Формат Бумаги].IDФормат)

INNER JOIN [Тип Бумаги] ON [Тип Бумаги].IDТипаБумаги = [Типы фотографий].IDТипаБумаги) INNER JOIN Кадры ON

Кадры.IDТипаФото = [Типы фотографий].IDТипаФото

GO

CREATE VIEW СписокПродаж AS

SELECT Продажи.IDПродажи AS ID, Продажи.Дата, Филиалы.ID AS IDФилиала, Сотрудники.IDСотрудника, Товары.IDТовара,

Сотрудники.Фамилия + ' ' + SUBSTRING(Сотрудники.Имя,0,2)+ '. ' + SUBSTRING(Сотрудники.Отчество,0,2) +'.' AS Сотрудник,

Филиалы.Адрес AS Филиал, Товары.Описание AS Товар,[Описание продажи].Количество, dbo.СтоимостьПродажи(Продажи.IDПродажи) AS Стоимость

FROM (((Продажи INNER JOIN [Описание продажи] ON Продажи.IDПродажи = [Описание продажи].IDПродажи)

INNER JOIN Товары ON Товары.IDТовара = [Описание продажи].IDТовара)

INNER JOIN Сотрудники ON Сотрудники.IDСотрудника = Продажи.IDСотрудника)

INNER JOIN Филиалы ON Продажи.IDФилиала = Филиалы.ID

GO

CREATE VIEW СписокКлиентовПолный AS

SELECT Клиенты.IDКлиента AS ID,Клиенты.Фамилия,Клиенты.Имя,Клиенты.Отчество,[Типы клиентов].Описание AS Тип,

ISNULL([Дисконтные карты].IDКарты,0) AS Карта, ISNULL([Дисконтные карты].Скидка,0) AS Скидка

FROM (Клиенты INNER JOIN [Типы клиентов] ON Клиенты.IDТипКлиента = [Типы клиентов].IDТипКлиента)

LEFT JOIN [Дисконтные карты] ON Клиенты.IDКлиента = [Дисконтные карты].IDКлиента

GO

CREATE VIEW Владельцы_дисконтных_карт AS

SELECT Фамилия,Имя,Отчество,IDКарты,Скидка

FROM КлиентыINNER JOIN [Дисконтные карты] ON [Дисконтные карты].IDКлиента = Клиенты.IDКлиента

GO

CREATE VIEW Список_поставщиков AS

SELECT *

FROM Поставщики

GO

CREATE VIEW ДопУслуги AS

SELECT[Дополнительные услуги].IDУслуги AS ID,Клиенты.IDКлиента, Филиалы.ID AS IDФилиала, Сотрудники.IDСотрудника,

Сотрудники.Фамилия + ' ' + SUBSTRING(Сотрудники.Имя,0,2)+ '. ' + SUBSTRING(Сотрудники.Отчество,0,2) +'.' AS Сотрудник,

Филиалы.Адрес AS Филиал,[Дополнительные услуги].Дата,

Клиенты.Фамилия + ' ' + SUBSTRING(Клиенты.Имя,0,2)+ '. ' + SUBSTRING(Клиенты.Отчество,0,2) +'.' AS Клиент,

[Типы услуг].Описание AS Услуга,[Типы услуг].Стоимость

FROM((([Дополнительные услуги] INNER JOIN [Типы услуг] ON [Дополнительные услуги].IDТипУслуги =[Типы услуг].IDТипУслуги)

INNER JOIN Клиенты ON [Дополнительные услуги].IDКлиента = Клиенты.IDКлиента)

INNER JOIN Филиалы ON [Дополнительные услуги].IDФилиала = Филиалы.ID)

INNER JOIN Сотрудники ON [Дополнительные услуги].IDСотрудника = Сотрудники.IDСотрудника

GO

CREATE VIEW СписокПроданныхПлёнок AS

SELECT[Проданные плёнки].IDПлёнки AS ID,

Сотрудники.Фамилия + ' ' + SUBSTRING(Сотрудники.Имя,0,2)+ '. ' + SUBSTRING(Сотрудники.Отчество,0,2) +'.' AS Сотрудник,

Филиалы.Адрес AS Филиал,

Клиенты.Фамилия + ' ' + SUBSTRING(Клиенты.Имя,0,2)+ '. ' + SUBSTRING(Клиенты.Отчество,0,2) +'.' AS Клиент

FROM(([Проданные плёнки] INNER JOIN Клиенты ON [Проданные плёнки].IDКлиента = Клиенты.IDКлиента)

INNER JOIN Филиалы ON [Проданные плёнки].IDФилиала = Филиалы.ID)

INNER JOIN Сотрудники ON [Проданные плёнки].IDСотрудника = Сотрудники.IDСотрудника

GO

CREATE PROCEDURE Киоски_по_филиалу

@fil tinyint

AS

SELECT ф1.ID,ф1.Адрес AS [Адрес филиала],ф2.ID AS IDКиоска,ф2.Адрес AS [Адрес киоска]

FROM Филиалы ф1, Филиалы ф2

WHERE ф1.ID = ф2.IDФилиала AND ф1.ID = @fil

GO

CREATE PROCEDURE Филиал_по_киоску

@fil tinyint

AS

SELECT ф1.ID,ф1.Адрес AS [Адрес филиала],ф2.ID AS IDКиоска,ф2.Адрес AS [Адрес киоска]

FROM Филиалы ф1, Филиалы ф2

WHERE ф1.ID = ф2.IDФилиала AND ф2.ID = @fil

GO

CREATE PROCEDURE Количество_филиалов AS

DECLARE @cnt int

SELECT @cnt = COUNT(*)

FROM Филиалы

RETURN @cnt

GO

CREATE PROCEDURE СписокЗаказовПоТипу

@type int,

@urgent bit = NULL,

@fil tinyint =NULL,

@date1 date =NULL,

@date2 date =NULL

AS

SELECT DISTINCT Заказы.IDЗаказа AS ID, Клиенты.IDКлиента, Филиалы.ID AS IDФилиала, Сотрудники.IDСотрудника, [Типы клиентов].IDТипКлиента,

Сотрудники.Фамилия + ' ' + SUBSTRING(Сотрудники.Имя,0,2)+ '. ' + SUBSTRING(Сотрудники.Отчество,0,2) +'.' AS Сотрудник,

Филиалы.Адрес AS Филиал,[Дата приёма], Срочный AS Срочность,

Клиенты.Фамилия + ' ' + SUBSTRING(Клиенты.Имя,0,2)+ '. ' + SUBSTRING(Клиенты.Отчество,0,2) +'.' AS Клиент,

[Типы клиентов].Описание AS [Тип клиента],dbo.СтоимостьЗаказа(Заказы.IDЗаказа)AS Стоимость

FROM (((((Заказы INNER JOIN Филиалы ON Заказы.IDФилиала = Филиалы.ID)

INNER JOIN Клиенты ON Клиенты.IDКлиента = Заказы.IDКлиента) )INNER JOIN Сотрудники ON Сотрудники.IDСотрудника = Заказы.IDСотрудника)

INNER JOIN [Типы клиентов] ON [Типы клиентов].IDТипКлиента = Клиенты.IDТипКлиента) INNER JOIN [Описание заказа] ON [Описание заказа].IDЗаказа = Заказы.IDЗаказа

WHERE (Заказы.Срочный = @urgent OR @urgent IS NULL)

AND (Заказы.IDФилиала = @fil OR @fil IS NULL)

AND (Заказы.[Дата приёма]>@date1 OR @date1 IS NULL)

AND (Заказы.[Дата приёма]<@date2 OR @date2 IS NULL)

AND ([Описание заказа].Проявка = CASE @type WHEN 1 THEN 1 WHEN 3 THEN 1 ELSE 0 END OR @type = 0)

AND ([Описание заказа].Печать = CASE @type WHEN 2 THEN 1 WHEN 3 THEN 1 ELSE 0 END OR @type = 0)

GO

CREATE PROCEDURE СуммаВыторга

@type int,

@urgent bit = NULL,

@fil tinyint =NULL,

@date1 date =NULL,

@date2 date =NULL

AS

SELECT SUM(dbo.СтоимостьЗаказа(Заказы.IDЗаказа)) AS [Сумма выторга]

FROM Заказы

WHERE Заказы.IDЗаказа IN (SELECT DISTINCT Заказы.IDЗаказа

FROM Заказы INNER JOIN [Описание заказа] ON [Описание заказа].IDЗаказа = Заказы.IDЗаказа

WHERE (Заказы.Срочный = @urgent OR @urgent IS NULL)

AND (Заказы.IDФилиала = @fil OR @fil IS NULL)

AND (Заказы.[Дата приёма]>@date1 OR @date1 IS NULL)

AND (Заказы.[Дата приёма]<@date2 OR @date2 IS NULL)

AND ([Описание заказа].Проявка = CASE @type WHEN 1 THEN 1 WHEN 3 THEN 1 ELSE 0 END OR @type = 0)

AND ([Описание заказа].Печать = CASE @type WHEN 2 THEN 1 WHEN 3 THEN 1 ELSE 0 END OR @type = 0))

GO

CREATE PROCEDURE Количество_напечатанных_фотографий

@urgent bit = NULL,

@fil tinyint =NULL,

@date1 date =NULL,

@date2 date =NULL

AS

SELECT SUM(Количество) AS Количество

FROM ((Заказы INNER JOIN [Описание заказа] ON [Описание заказа].IDЗаказа = Заказы.IDЗаказа)

INNER JOIN Плёнка ON [Описание заказа].IDПлёнки = Плёнка.IDПлёнки )

INNER JOIN Кадры ON Кадры.IDПленки = Плёнка.IDПлёнки

WHERE (Заказы.Срочный = @urgent OR @urgent IS NULL)

AND (Заказы.IDФилиала = @fil OR @fil IS NULL)

AND (Заказы.[Дата приёма]>@date1 OR @date1 IS NULL)

AND (Заказы.[Дата приёма]<@date2 OR @date2 IS NULL)

GO

CREATE PROCEDURE Количество_проявленных_плёнок

@urgent bit = NULL,

@fil tinyint =NULL,

@date1 date =NULL,

@date2 date =NULL

AS

SELECT COUNT (*) AS Количество

FROM (Заказы INNER JOIN Филиалы ON Заказы.IDФилиала = Филиалы.ID) INNER JOIN [Описание заказа] ON [Описание заказа].IDЗаказа = Заказы.IDЗаказа

WHERE [Описание заказа].Проявка = 1 AND(Заказы.Срочный = @urgent OR @urgent IS NULL)

AND (Заказы.IDФилиала = @fil OR @fil IS NULL)

AND (Заказы.[Дата приёма]>@date1 OR @date1 IS NULL)

AND (Заказы.[Дата приёма]<@date2 OR @date2 IS NULL)

GO

CREATE PROCEDURE Список_клиентов_по_филиалам

@fil int = NULL

AS

SELECT DISTINCT Клиенты.IDКлиента AS ID,Клиенты.Фамилия,Клиенты.Имя,Клиенты.Отчество,[Типы клиентов].Описание AS Тип,

ISNULL([Дисконтные карты].IDКарты,0) AS Карта, ISNULL([Дисконтные карты].Скидка,0) AS Скидка

FROM ((Клиенты INNER JOIN [Типы клиентов] ON Клиенты.IDТипКлиента = [Типы клиентов].IDТипКлиента)

LEFT JOIN [Дисконтные карты] ON Клиенты.IDКлиента = [Дисконтные карты].IDКлиента)

INNER JOIN Заказы ON Клиенты.IDКлиента = Заказы.IDКлиента

WHERE (Заказы.IDФилиала = @fil OR @fil IS NULL)

GO

CREATE PROCEDURE Клиенты_сделавшие_заказы_больше_чем

@pr money

AS

SELECT IDКлиента AS ID,Клиент, SUM(Стоимость) AS Сумма

FROM СписокЗаказов

GROUP BY СписокЗаказов.IDКлиента,СписокЗаказов.Клиент

HAVING SUM(Стоимость)>@pr

GO

CREATE PROCEDURE СписокРаботников

@id tinyint = NULL

AS

IF @id IS NOT NULL

SELECT IDСотрудника AS ID,Фамилия,Имя,Отчество,Название AS Должность, Адрес AS Филиал

FROM (Сотрудники INNER JOIN Должности ON Сотрудники.IDДолжности = Должности.IDДолжности) INNER JOIN Филиалы ON Сотрудники.IDФилиала = Филиалы.ID

WHERE Сотрудники.IDДолжности = @id

ELSE

SELECT IDСотрудника AS ID,Фамилия,Имя,Отчество,Название AS Должность, Адрес AS Филиал

FROM (Сотрудники INNER JOIN Должности ON Сотрудники.IDДолжности = Должности.IDДолжности) INNER JOIN Филиалы ON Сотрудники.IDФилиала = Филиалы.ID

GO

CREATE PROCEDURE Выторг_от_фототоваров

@fil tinyint =NULL,

@date1 date =NULL,

@date2 date =NULL

AS

SELECT SUM(Цена*Количество) AS [Сумма выторга]

FROM (Продажи INNER JOIN [Описание продажи] ON Продажи.IDПродажи = [Описание продажи].IDПродажи) INNER JOIN Товары ON Товары.IDТовара = [Описание продажи].IDТовара

WHERE (Продажи.IDФилиала = @fil OR @fil IS NULL)

AND (Продажи.Дата>@date1 OR @date1 IS NULL)

AND (Продажи.Дата<@date2 OR @date2 IS NULL)

GO

CREATE PROCEDURE Список_фототоваров

@fil tinyint =NULL,

@date1 date =NULL,

@date2 date =NULL

AS

SELECT Описание AS Товар, SUM(Количество) AS Количество

FROM (Продажи INNER JOIN [Описание продажи] ON Продажи.IDПродажи = [Описание продажи].IDПродажи) INNER JOIN Товары ON Товары.IDТовара = [Описание продажи].IDТовара

WHERE (Продажи.IDФилиала = @fil OR @fil IS NULL)

AND (Продажи.Дата>@date1 OR @date1 IS NULL)

AND (Продажи.Дата<@date2 OR @date2 IS NULL)

GROUP BY Описание

GO

CREATE PROCEDURE Популярные_товары

@fil tinyint =NULL,

@date1 date =NULL,

@date2 date =NULL

AS

SELECT TOP 5 Описание AS Товар, SUM(Количество) AS [Продано штук]

FROM (Продажи INNER JOIN [Описание продажи] ON Продажи.IDПродажи = [Описание продажи].IDПродажи) INNER JOIN Товары ON Товары.IDТовара = [Описание продажи].IDТовара

WHERE (Продажи.IDФилиала = @fil OR @fil IS NULL)

AND (Продажи.Дата>@date1 OR @date1 IS NULL)

AND (Продажи.Дата<@date2 OR @date2 IS NULL)

GROUP BY Описание

ORDER BY SUM(Количество) DESC

GO

-- Тригери

CREATE TRIGGER Добавление_филиала ON Филиалы

AFTER INSERT,UPDATE

AS

SET NoCount ON

IF EXISTS( SELECT * FROM Inserted WHERE Киоск = 0 AND IDФилиала IS NOT NULL)

BEGIN

ROLLBACK

RAISERROR('Филиал не может быть привязан к филиалу',16,1)

RETURN

END

IF EXISTS( SELECT * FROM Inserted WHERE Киоск = 1 AND IDФилиала IS NULL)

BEGIN

ROLLBACK

RAISERROR('Киоск должен быть привязан к филиалу',16,1)

RETURN

END

IF EXISTS( SELECT * FROM Inserted INNER JOIN Филиалы ON Inserted.IDФилиала = Филиалы.ID

WHERE Inserted.Киоск = 1 AND Филиалы.Киоск = 1)

BEGIN

ROLLBACK

RAISERROR('Киоск не может быть привязан к киоску',16,1)

RETURN

END

GO

CREATE TRIGGER Добавление_проданной_плёнки ON [Проданные плёнки]

AFTER INSERT,UPDATE

AS

SET NOCOUNT ON

IF EXISTS ( SELECT * FROM Inserted INNER JOIN Сотрудники ON Inserted.IDСотрудника = Сотрудники.IDСотрудника

WHERE Inserted.IDФилиала != Сотрудники.IDФилиала)

BEGIN

ROLLBACK

RAISERROR('Сотрудник должен работать в данном филиале',16,1)

RETURN

END

GO

CREATE TRIGGER Добавление_описания_заказа ON [Описание заказа]

INSTEAD OF INSERT

AS

SET NOCOUNT ON

IF EXISTS(SELECT * FROM [Описание заказа] INNER JOIN INSERTED ON [Описание заказа].IDПлёнки = Inserted.IDПлёнки

WHERE [Описание заказа].IDПлёнки = Inserted.IDПлёнки)

BEGIN

ROLLBACK

RAISERROR('Данная плёнка уже обрабатывается',16,1)

RETURN

END

DECLARE @IDЗаказа int

DECLARE @IDПлёнки int

DECLARE @Проявка bit

DECLARE @Печать bit

SELECT @IDЗаказа = Inserted.IDЗаказа, @Проявка = Inserted.Проявка, @Печать = Inserted.Печать, @IDПлёнки = Inserted.IDПлёнки FROM Inserted

INSERT [Описание заказа] VALUES (@IDЗаказа,@Проявка,@Печать,@IDПлёнки)

GO

CREATE TRIGGER Удаление_описания_заказа ON [Описание заказа]

AFTER DELETE

AS

SET NOCOUNT ON

DECLARE @ID int

SELECT @ID = IDПлёнки FROM Deleted

DELETE FROM Плёнка WHERE IDПлёнки = @ID

GO

CREATE TRIGGER Срочность_заказа ON Заказы

AFTER INSERT,UPDATE

AS

SET NOCOUNT ON

IF EXISTS (SELECT * FROM Inserted INNER JOIN Филиалы ON Филиалы.ID = Inserted.IDФилиала

WHERE Inserted.Срочный = 1 AND Филиалы.Киоск = 1)

BEGIN

ROLLBACK

RAISERROR('Срочные заказы выполняются только в филиалах',16,1)

RETURN

END

GO

CREATE TRIGGER Добавление_дисконта ON Владельцы_дисконтных_карт

INSTEAD OF INSERT

AS

SET NOCOUNT ON

DECLARE @Фамилия nvarchar(30)

DECLARE @Имя nvarchar(15)

DECLARE @Отчество nvarchar(15)

SELECT @Фамилия = Inserted.Фамилия, @Имя = Inserted.Имя, @Отчество = Inserted.Отчество FROM Inserted

INSERT INTO Клиенты (IDТипКлиента, Фамилия, Имя, Отчество) VALUES(2,@Фамилия,@Имя,@Отчество)

DECLARE @IDКарты int

DECLARE @Скидка float

SELECT @IDКарты = Inserted.IDКарты,@Скидка = Inserted.Скидка FROM Inserted

INSERT [Дисконтные карты] VALUES (@IDКарты, @@IDENTITY, @Скидка)

GO

CREATE TRIGGER Запрет_на_изменение_таблиц

ON DATABASE

FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE

AS

ROLLBACK

RAISERROR('Изменять таблицы запрещено',16,1)

RETURN

GO

-- Дані

INSERT "Должности" VALUES(1,'Продавец',1500)

INSERT "Должности" VALUES(2,'Менеджер',2500)

INSERT "Должности" VALUES(3,'Фотограф',3000)

INSERT "Должности" VALUES(4,'Дизайнер',3500)

INSERT "Должности" VALUES(5,'Техник',2000)

INSERT "Должности" VALUES(6,'Водитель',2000)

INSERT "Должности" VALUES(7,'Охранник',2500)

INSERT "Должности" VALUES(8,'Консультант',1800)

INSERT "Должности" VALUES(9,'Директор',10000)

INSERT "Должности" VALUES(10,'Администратор',10000)

GO

INSERT "Филиалы" VALUES(1,'пр.Ленина,150',0,NULL)

INSERT "Филиалы" VALUES(2,'ул.Победы,70',0,NULL)

INSERT "Филиалы" VALUES(3,'пр.Металлургов,30',0,NULL)

INSERT "Филиалы" VALUES(4,'пр.Ленина,100',1,1)

INSERT "Филиалы" VALUES(5,'ул.Героев Сталинграда',1,2)

INSERT "Филиалы" VALUES(6,'ул.Трегубенко,22',1,3)

GO

INSERT "Сотрудники" VALUES(1,4,'Петров', 'Василий', 'Александрович','kcashier1')

INSERT "Сотрудники" VALUES(1,5,'Иванов', 'Андрей', 'Васильевич',NULL)

INSERT "Сотрудники" VALUES(1,6,'Кукушкин', 'Константин', 'Валентинович',NULL)

INSERT "Сотрудники" VALUES(2,1,'Васильков', 'Федор', 'Александрович','manag')

INSERT "Сотрудники" VALUES(2,2,'Сапожников', 'Василий', 'Андреевич','OK')

INSERT "Сотрудники" VALUES(2,3,'Петренко', 'Валентин', 'Зиновьевич',NULL)

INSERT "Сотрудники" VALUES(3,1,'Гуров', 'Константин', 'Аркадьевич',NULL)

INSERT "Сотрудники" VALUES(3,2,'Сидоров', 'Петр', 'Прокофьевич',NULL)

INSERT "Сотрудники" VALUES(3,3,'Удалов', 'Валентин', 'Андреевич',NULL)

INSERT "Сотрудники" VALUES(4,1,'Пирогов', 'Владимир', 'Петрович',NULL)

INSERT "Сотрудники" VALUES(4,2,'Шапошников', 'Василий', 'Андреевич',NULL)

INSERT "Сотрудники" VALUES(4,3,'Суворый', 'Анатолий', 'Иванович',NULL)

INSERT "Сотрудники" VALUES(5,1,'Хвостков', 'Федор', 'Иванович',NULL)

INSERT "Сотрудники" VALUES(6,2,'Мечников', 'Аркадий', 'Владимирович',NULL)

INSERT "Сотрудники" VALUES(7,1,'Быков', 'Петр', 'Владимирович',NULL)

INSERT "Сотрудники" VALUES(7,2,'Крутой', 'Андрей', 'Денисович',NULL)

INSERT "Сотрудники" VALUES(7,3,'Прутков', 'Алексей', 'Петрович',NULL)

INSERT "Сотрудники" VALUES(8,3,'Сидоренко', 'Павел', 'Петрович',NULL)

INSERT "Сотрудники" VALUES(9,1,'Жучков', 'Владимир', 'Иванович',NULL)

INSERT "Сотрудники" VALUES(10,1,'Гаусс', 'Феофан', 'Иванович','admin1')

INSERT "Сотрудники" VALUES(1,1,'Серов', 'Пётр', 'Иванович','fcashier1')

GO

INSERT "Типы клиентов" VALUES(1,'Любитель')

INSERT "Типы клиентов" VALUES(2,'Профессионал')

GO

INSERT "Категории" VALUES(1,'Расходные материалы')

INSERT "Категории" VALUES(2,'Фотоаппараты')

INSERT "Категории" VALUES(3,'Аксессуары')

GO

INSERT "Поставщики" VALUES(1,'ООО ФотоМ','пр.Ленина.11')

INSERT "Поставщики" VALUES(2,'ЧП Петров','пр.Ленина.56')

INSERT "Поставщики" VALUES(3,'ФотоМир','пр.Ленина.120')

GO

INSERT "Товары" VALUES('Фотобумага Epson',10,1,1)

INSERT "Товары" VALUES('Фотобумага Canon',11,1,1)

INSERT "Товары" VALUES('Фотоаппарат Canon eos 50',2500,2,2)

INSERT "Товары" VALUES('Фотоаппарат Sony dsc 750',1500,2,2)

INSERT "Товары" VALUES('Фотоаппарат Sony dsc 350',800,2,2)

INSERT "Товары" VALUES('Рамка для фото',25,3,3)

INSERT "Товары" VALUES('Штатив',250,3,3)

GO

INSERT "Типы услуг" VALUES(1,'Фото на документы',15)

INSERT "Типы услуг" VALUES(2,'Реставрация',150)

INSERT "Типы услуг" VALUES(3,'Прокат',50)

INSERT "Типы услуг" VALUES(4,'Художественное фото',75)

INSERT "Типы услуг" VALUES(5,'Услуги фотографа',50)

GO

INSERT "Тип Бумаги" VALUES (1,'матовая Canon')

INSERT "Тип Бумаги" VALUES (2,'глянцевая Canon')

INSERT "Тип Бумаги" VALUES (3,'матовая Epson')

INSERT "Тип Бумаги" VALUES (4,'глянцевая Epson')

GO

INSERT "Формат Бумаги" VALUES(1,'10x15')

INSERT "Формат Бумаги" VALUES(2,'8x12')

INSERT "Формат Бумаги" VALUES(3,'15x17')

INSERT "Формат Бумаги" VALUES(4,'A4')

GO

INSERT "Типы фотографий" VALUES(1,0.70,1,1)

INSERT "Типы фотографий" VALUES(2,0.70,1,2)

INSERT "Типы фотографий" VALUES(3,0.60,2,1)

INSERT "Типы фотографий" VALUES(4,0.60,2,2)

INSERT "Типы фотографий" VALUES(5,1.80,3,3)

INSERT "Типы фотографий" VALUES(6,1.90,3,4)

INSERT "Типы фотографий" VALUES(7,3.80,4,2)

INSERT "Типы фотографий" VALUES(8,3.70,4,4)

GO

-- Ролі і користувачі

CREATE PROCEDURE RoleUser

AS

SET NOCOUNT ON

declare @curUser nvarchar(30)

declare @t table(UserName nvarchar(50), RoleName nvarchar(50), LoginName nvarchar(50), DefDBName nvarchar(50), DefSchemaName nvarchar(50), UserID smallint, [SID] varbinary(100))

declare @RoleName nvarchar(50)

set @curUser = USER

insert into @t

EXEC sp_helpuser @curUser

select @RoleName = RoleName from @t

declare @name nvarchar(30)

declare @name3 nvarchar(30)

declare @surname nvarchar(30)

declare @dolgnost nvarchar(30)

declare @filial int

declare @ID int

select@ID = Сотрудники.IDСотрудника,

@name = Сотрудники.Имя,

@name3 = Сотрудники.Отчество,

@surname = Сотрудники.Фамилия,

@dolgnost = Должности.Название,

@filial = Сотрудники.IDФилиала

from Сотрудники INNER JOIN Должности ON Сотрудники.IDДолжности = Должности.IDДолжности

WHERE Сотрудники.UserName = @curUser

select @ID as IDСотрудника,@name as Имя,@name3 as Отчество,@surname as Фамилия,

@dolgnost as Должность, @filial as IDФилиала,@RoleName as RoleName,@curUser as UserName

GO

-- Кассир_киоска

EXEC sp_addrole 'Кассир_киоска'

GO

GRANT SELECT,UPDATE,DELETE,INSERT ON Заказы TO Кассир_киоска

GRANT SELECT,UPDATE,DELETE,INSERT ON [Описание заказа] TO Кассир_киоска

GRANT SELECT,UPDATE,DELETE,INSERT ON Плёнка TO Кассир_киоска

GRANT SELECT,UPDATE,DELETE,INSERT ON Кадры TO Кассир_киоска

GRANT SELECT,UPDATE,DELETE,INSERT ON Клиенты TO Кассир_киоска

GRANT SELECT ON [Типы фотографий] TO Кассир_киоска

GRANT SELECT ON [Тип Бумаги] TO Кассир_киоска

GRANT SELECT ON [Формат Бумаги] TO Кассир_киоска

GRANT SELECT ON [Типы Клиентов] TO Кассир_киоска

GRANT SELECT ON [Дисконтные карты] TO Кассир_киоска

GRANT SELECT ON Сотрудники TO Кассир_киоска

GRANT SELECT ON Должности TO Кассир_киоска

GRANT SELECT ON Филиалы TO Кассир_киоска

GRANT EXECUTE ON СтоимостьЗаказа TO Кассир_киоска

GRANT SELECT ON СписокЗаказов TO Кассир_киоска

GRANT SELECT ON СписокКлиентов TO Кассир_киоска

GRANT SELECT ON СписокТиповБумаг TO Кассир_киоска

GRANT SELECT ON ОписаниеПлёнки TO Кассир_киоска

GRANT SELECT ON СписокКлиентовПолный TO Кассир_киоска

GRANT EXECUTE ON RoleUser TO Кассир_киоска

GRANT EXECUTE ON СписокЗаказовПоТипу TO Кассир_киоска

GRANT EXECUTE ON СуммаВыторга TO Кассир_киоска

GRANT EXECUTE ON Количество_напечатанных_фотографий TO Кассир_киоска

GRANT EXECUTE ON Количество_проявленных_плёнок TO Кассир_киоска

GO

-- Кассир_филиала

EXEC sp_addrole 'Кассир_филиала'

GO

GRANT SELECT,UPDATE,DELETE,INSERT ON Заказы TO Кассир_филиала

GRANT SELECT,UPDATE,DELETE,INSERT ON [Описание заказа] TO Кассир_филиала

GRANT SELECT,UPDATE,DELETE,INSERT ON Плёнка TO Кассир_филиала

GRANT SELECT,UPDATE,DELETE,INSERT ON Кадры TO Кассир_филиала

GRANT SELECT,UPDATE,DELETE,INSERT ON Клиенты TO Кассир_филиала

GRANT SELECT,UPDATE,DELETE,INSERT ON [Дисконтные карты] TO Кассир_филиала

GRANT SELECT,UPDATE,DELETE,INSERT ON Продажи TO Кассир_филиала

GRANT SELECT,UPDATE,DELETE,INSERT ON [Описание продажи] TO Кассир_филиала

GRANT SELECT ON Товары TO Кассир_филиала

GRANT SELECT ON Поставщики TO Кассир_филиала

GRANT SELECT,UPDATE,DELETE,INSERT ON [Дополнительные услуги] TO Кассир_филиала

GRANT SELECT ON [Типы услуг] TO Кассир_филиала

GRANT SELECT,UPDATE,DELETE,INSERT ON [Проданные плёнки] TO Кассир_филиала

GRANT SELECT ON Категории TO Кассир_филиала

GRANT SELECT ON [Типы фотографий] TO Кассир_филиала

GRANT SELECT ON [Тип Бумаги] TO Кассир_филиала

GRANT SELECT ON [Формат Бумаги] TO Кассир_филиала

GRANT SELECT ON [Типы Клиентов] TO Кассир_филиала

GRANT SELECT ON Сотрудники TO Кассир_филиала

GRANT SELECT ON Должности TO Кассир_филиала

GRANT SELECT ON Филиалы TO Кассир_филиала

GRANT EXECUTE ON СтоимостьЗаказа TO Кассир_филиала

GRANT SELECT ON СписокЗаказов TO Кассир_филиала

GRANT SELECT ON СписокКлиентов TO Кассир_филиала

GRANT SELECT ON СписокТиповБумаг TO Кассир_филиала

GRANT SELECT ON ОписаниеПлёнки TO Кассир_филиала

GRANT SELECT ON СписокКлиентовПолный TO Кассир_филиала

GRANT SELECT ON СписокПродаж TO Кассир_филиала

GRANT SELECT ON Список_поставщиков TO Кассир_филиала

GRANT SELECT ON Владельцы_дисконтных_карт TO Кассир_филиала

GRANT SELECT ON ДопУслуги TO Кассир_филиала

GRANT SELECT ON СписокПроданныхПлёнок TO Кассир_филиала

GRANT EXECUTE ON RoleUser TO Кассир_филиала

GRANT EXECUTE ON СписокЗаказовПоТипу TO Кассир_филиала

GRANT EXECUTE ON СуммаВыторга TO Кассир_филиала

GRANT EXECUTE ON Количество_напечатанных_фотографий TO Кассир_филиала

GRANT EXECUTE ON Количество_проявленных_плёнок TO Кассир_филиала

GRANT EXECUTE ON Список_клиентов_по_филиалам TO Кассир_филиала

GRANT EXECUTE ON Клиенты_сделавшие_заказы_больше_чем TO Кассир_филиала

GRANT EXECUTE ON Выторг_от_фототоваров TO Кассир_филиала

GRANT EXECUTE ON Список_фототоваров TO Кассир_филиала

GRANT EXECUTE ON Популярные_товары TO Кассир_филиала

GO

-- Менеджер

EXEC sp_addrole 'Менеджер'

GO

GRANT SELECT,UPDATE,DELETE,INSERT ON [Типы фотографий] TO Менеджер

GRANT SELECT,UPDATE,DELETE,INSERT ON [Тип Бумаги] TO Менеджер

GRANT SELECT,UPDATE,DELETE,INSERT ON [Формат Бумаги] TO Менеджер

GRANT SELECT,UPDATE,DELETE,INSERT ON [Типы Клиентов] TO Менеджер

GRANT SELECT,UPDATE,DELETE,INSERT ON [Дисконтные карты] TO Менеджер

GRANT SELECT,UPDATE,DELETE,INSERT ON Клиенты TO Менеджер

GRANT SELECT,UPDATE,DELETE,INSERT ON Филиалы TO Менеджер

GRANT SELECT ON Сотрудники TO Менеджер

GRANT SELECT ON Должности TO Менеджер

GRANT SELECT,UPDATE,DELETE,INSERT ON Товары TO Менеджер

GRANT SELECT,UPDATE,DELETE,INSERT ON Поставщики TO Менеджер

GRANT SELECT,UPDATE,DELETE,INSERT ON Категории TO Менеджер

GRANT SELECT,UPDATE,DELETE,INSERT ON [Типы услуг] TO Менеджер

GRANT SELECT,INSERT ON Владельцы_дисконтных_карт TO Менеджер

GRANT SELECT ON СписокКлиентовПолный TO Менеджер

GRANT SELECT ON Список_всех_филиалов TO Менеджер

GRANT SELECT ON Список_киосков_по_филиалам TO Менеджер

GRANT SELECT ON Список_филиалов TO Менеджер

GRANT SELECT ON Список_киосков TO Менеджер

GRANT SELECT ON Поставщики_по_категориям TO Менеджер

GRANT SELECT ON Поставщики_по_товарам TO Менеджер

GRANT EXECUTE ON RoleUser TO Менеджер

GRANT EXECUTE ON Киоски_по_филиалу TO Менеджер

GRANT EXECUTE ON Филиал_по_киоску TO Менеджер

GRANT EXECUTE ON Список_клиентов_по_филиалам TO Менеджер

GRANT EXECUTE ON Клиенты_сделавшие_заказы_больше_чем TO Менеджер

GO

-- Отдел_кадров

EXEC sp_addrole 'Отдел_кадров'

GO

GRANT SELECT,UPDATE,DELETE,INSERT ON Должности TO Отдел_кадров

GRANT SELECT,UPDATE,DELETE,INSERT ON Сотрудники TO Отдел_кадров

GRANT SELECT ON Филиалы TO Отдел_кадров

GRANT EXECUTE ON СписокРаботников TO Отдел_кадров

GRANT EXECUTE ON RoleUser TO Отдел_кадров

GO

-- Створення логінов

IF EXISTS(SELECT * FROM sys.syslogins WHERE name = N'admin1') DROP LOGIN admin1

IF EXISTS(SELECT * FROM sys.syslogins WHERE name = N'kcashier1') DROP LOGIN kcashier1

IF EXISTS(SELECT * FROM sys.syslogins WHERE name = N'fcashier1') DROP LOGIN fcashier1

IF EXISTS(SELECT * FROM sys.syslogins WHERE name = N'manag') DROP LOGIN manag

IF EXISTS(SELECT * FROM sys.syslogins WHERE name = N'OK') DROP LOGIN OK

GO

EXEC sp_addlogin 'admin1','pass@word1','PhotoCenterDB'

EXEC sp_addlogin 'kcashier1','pass@word1','PhotoCenterDB'

EXEC sp_addlogin 'fcashier1','pass@word1','PhotoCenterDB'

EXEC sp_addlogin 'manag','pass@word1','PhotoCenterDB'

EXEC sp_addlogin 'OK','pass@word1','PhotoCenterDB'

GO

-- Створення користувачів

EXEC sp_adduser 'admin1',NULL,db_owner

EXEC sp_adduser 'kcashier1',NULL,Кассир_киоска

EXEC sp_adduser 'fcashier1',NULL,Кассир_филиала

EXEC sp_adduser 'manag',NULL,Менеджер

EXEC sp_adduser 'OK',NULL,Отдел_кадров

GO

2.4 Опис Windows інтерфейсу

Після запуску виконуваного файлу “PhotoCenter.exe”, програма перевіряє налаштування, які зберігаються у файлі “PhotoCenter.exe.Config”. Якщо клієнт не може з'єднатися з сервером, то буде виведено повідомлення користувачу (Рисунок 2) о неможливості з'єднання.

Рисунок 2

Якщо користувач вирішить зробити нове з'єднання то буде виведено вікно для налаштування нового з'єднання (Рисунок 3). Це вікно дозволяє вибрати сервер, та аутентифікацію. Якщо з'єднання буде успішне, то нові налаштування будуть збережені у файлі “PhotoCenter.exe.Config” і при наступному запуску програми користувачу не потрібно буде вводити ці налаштування.

Головне вікно програми зображене на рисунку 4. При запуску клієнта чи зміні налаштувань визначається роль користувача. Наприклад на рисунку 4 зображено інтерфейс доступний адміністратору, а на рисунку 5 інтерфейс користувача, який зайшов під роллю відділу кадрів, і він не має тих можливостей, що має адміністратор.

Рисунок 3

Інтерфейс програми розділено на 6 вкладок: “Закази” (Рисунок 4), ”Продажі” (Рисунок 6), ”Послуги”(Рисунок 7), “Клієнти”, ”Працівники” (Рисунок 5), ”Управління” (Рисунок 8). На приклад, через вкладку “Закази” відбувається робота з заказами, тобто додавання заказу, його редагування та видалення. Також у цій вкладці розміщені запити, котрі дозволяють виводити список заказів, суму виторгу, кількість роздрукованих фотографій та кількість проявлених плівок.

Рисунок 4

Рисунок 5

Рисунок 6

Рисунок 7

Рисунок 8

Розглянемо на приклад додавання нового заказу. Це відбувається натисканням кнопки «Добавити», після чого виводиться вікно (Рисунок 9) в якому треба вибрати клієнта, вибрати терміновість, ввести код плівки і те що з нею треба зробити, тобто друк, проявлення, або і те і інше. У разі друку треба вибрати кількість кадрів та папір.

Рисунок 9

Якщо потрібне буде ввести нового клієнта то потрібно натиснути кнопку «Новий», після чого з'явиться вікно (Рисунок 10), де потрібно ввести прізвище, ім'я, по батькові та тип клієнта.

Рисунок 10

Розглянемо код, який виконується при додаванні нового заказу. Через те що додавання заказу відбувається у таблиці «Закази», «Опис заказу», «Плівка» та «Кадри» треба використовувати транзакції, тобто якщо у процесі вставки даних у якусь з цих таблиць виникне помилка, то транзакція буде відкинута і данні не вставляться у всі таблиці, таким чином не порушується цілісність бази даних.

string sqlInsert = "INSERT Заказы (IDФилиала,Срочный,IDКлиента,IDСотрудника)" +

"VALUES (@IDФилиала,@Срочный,@IDКлиента,@IDСотрудника); SET @IDЗаказа =SCOPE_IDENTITY()";

string sqlInsertПленка = "INSERT Плёнка (IDПлёнки) VALUES (@IDПлёнки)";

string sqlInsertОписаниеЗаказа = "INSERT [Описание заказа]" +

(IDЗаказа,Проявка,Печать,IDПлёнки) VALUES (@IDЗаказа,@Проявка,@Печать,@IDПлёнки)";

string sqlInsertКадры = @"INSERT Кадры (IDПленки,НомерКадра,Количество,IDТипаФото) " + "VALUES "(@IDПленки,@НомерКадра,@Количество,@IDТипаФото)";

SqlConnection conn = new SqlConnection(conString);

conn.Open();

SqlTransaction tran = conn.BeginTransaction();

SqlCommand cmd = new SqlCommand(sqlInsert, conn, tran);

cmd.Parameters.Add(new SqlParameter("@IDФилиала", SqlDbType.TinyInt));

cmd.Parameters.Add(new SqlParameter("@Срочный", SqlDbType.Bit));

cmd.Parameters.Add(new SqlParameter("@IDКлиента", SqlDbType.BigInt));

cmd.Parameters.Add(new SqlParameter("@IDСотрудника", SqlDbType.SmallInt));

SqlParameter param = new SqlParameter("@IDЗаказа", SqlDbType.Int);

param.Direction = ParameterDirection.Output;

cmd.Parameters.Add(param);

SqlCommand cmdInsПленка = new SqlCommand(sqlInsertПленка, conn, tran);

cmdInsПленка.Parameters.Add(new SqlParameter("@IDПлёнки", SqlDbType.Int));

SqlCommand cmdInsОписание = new SqlCommand(sqlInsertОписаниеЗаказа,conn,tran);

cmdInsОписание.Parameters.Add(new SqlParameter("@IDЗаказа",SqlDbType.Int));

cmdInsОписание.Parameters.Add(new SqlParameter("@Проявка",SqlDbType.Bit));

cmdInsОписание.Parameters.Add(new SqlParameter("@Печать",SqlDbType.Bit));

cmdInsОписание.Parameters.Add(new SqlParameter("@IDПлёнки",SqlDbType.Int));

SqlCommand cmdInsКадры = new SqlCommand(sqlInsertКадры, conn, tran);

cmdInsКадры.Parameters.Add(new SqlParameter("@IDПленки",SqlDbType.Int));

cmdInsКадры.Parameters.Add(new SqlParameter("@НомерКадра", SqlDbType.TinyInt));

cmdInsКадры.Parameters.Add(new SqlParameter("@Количество",SqlDbType.TinyInt));

cmdInsКадры.Parameters.Add(new SqlParameter("@IDТипаФото",SqlDbType.SmallInt));

try

{

// Вставляем заказ

cmd.Parameters["@IDФилиала"].Value = IDФилиала;

cmd.Parameters["@IDСотрудника"].Value = IDСотрудника;

cmd.Parameters["@IDКлиента"].Value = (long)(comboBoxClient.SelectedValue);

cmd.Parameters["@Срочный"].Value = checkBoxUrgent.Checked;

cmd.ExecuteNonQuery();

int IDЗаказа = (int)param.Value;

// Вставляем плёнку

cmdInsПленка.Parameters["@IDПлёнки"].Value = integerInputIDFilm.Value;

cmdInsПленка.ExecuteNonQuery();

// Вставляем описание заказа

cmdInsОписание.Parameters["@IDЗаказа"].Value = IDЗаказа;

cmdInsОписание.Parameters["@Проявка"].Value = checkBoxProyavka.Checked;

cmdInsОписание.Parameters["@Печать"].Value = checkBoxPrint.Checked;

cmdInsОписание.Parameters["@IDПлёнки"].Value = integerInputIDFilm.Value;

cmdInsОписание.ExecuteNonQuery();

// Вставляем кадры

if (checkBoxPrint.Checked)

{

foreach (Cadr cadr in cadrs)

{

cmdInsКадры.Parameters["@IDПленки"].Value = integerInputIDFilm.Value;

cmdInsКадры.Parameters["@НомерКадра"].Value = cadr.Number;

cmdInsКадры.Parameters["@Количество"].Value = cadr.Count;

cmdInsКадры.Parameters["@IDТипаФото"].Value = cadr.Type;

cmdInsКадры.ExecuteNonQuery();

}

}

tran.Commit();

MessageBox.Show("Заказ добавлен", "Добавление заказа", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

catch (SqlException ex)

{

tran.Rollback();

MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

conn.Close();

}

2.5 Опис Web-інтерфейсу

Web-інтерфейс являє собою сайт, який дозволяє переглядати таблиці бази даних та виконувати запити. Сайт має три сторінки: сторінка для входу, сторінка для роботи з таблицями та сторінка для роботи з запитами. Сторінки з таблицями та запитами не доступні для неавторизованих користувачів. Для того щоб авторизуватись треба на головній сторінці (Рисунок 8) ввести ім'я користувача та пароль. Після цього відкривається сторінка «Таблиці» (Рисунок 9). Вибрати таблицю можна у випадаючому списку.

Для роботи з запитами треба перейти на сторінку «Запити» (Рисунок 10), вибрати необхідний запит та ввести параметри.

Рисунок 8

Рисунок 9

Рисунок 10



Література

1. Microsoft SQL Server 2005. Библия пользователя: Пер. с англ. М.: ООО «И.Д.Вильямс», 2008.

2. Дж. Грофф, П. Вайнберг SQL: Полное руководство: Пер. с англ. 2-е изд., перераб. И доп. К.: Издательская группа BHV, 2001.

3. Б. Гамильтон ADO.NET Сборник рецептов. Для профессионалов. СПб.: Питер, 2005.

4. Сеппа Д. Программирование на Microsoft ADO.NET 2.0 Мастер-класс. / Пер. с англ. М.: Издательство «Русская редакция», 2005.

Размещено на Allbest.ru