ind

/*
    Задача 2. База данных ≪Городская Дума≫.
В базе хранятся имена, адреса, домашние и служебные телефоны
всех членов Думы. В Думе работает порядка сорока комиссий,
все участники которых являются членами Думы. Каждая комиссия
имеет свой профиль, например, вопросы образования, проблемы,
связанные с жильем, и так далее. Данные по каждой из комиссий включают:
председатель и состав, прежние (за 10 предыдущих лет) председатели
и члены этой комиссии, даты включения и выхода из состава комиссии,
избрания ее председателей. Члены Думы могут заседать в нескольких
комиссиях. В базу заносятся время и место проведения каждого
заседания комиссии с указанием депутатов и служащих Думы, которые
участвуют в его организации. Создать триггер для проверки того, что
один и тот же депутат в одно время не заседает в двух комиссиях.
1) Показать список комиссий, для каждой – ее состав и председателя.
2) Для введенного пользователем интервала дат и названия комиссии
    показать в хронологическом порядке всех ее председателей.
3) Показать список членов Думы, для каждого из них – список комиссий,
    в которых он участвовал и/или был председателем.
4) Для указанного интервала дат и комиссии выдать список членов
    с указанием количества пропущенных заседаний.
5) Вывести список заседаний в указанный интервал дат в хронологическом
    порядке, для каждого заседания – список присутствующих.
6) По каждой комиссии показать количество проведенных заседаний
    в указанный период времени.
*/

USE master
GO

IF  EXISTS (
        SELECT name
                FROM sys.DATABASES
                WHERE name = N'ElenaBeklenishcheva'
)
ALTER DATABASE ElenaBeklenishcheva SET single_user WITH ROLLBACK immediate
GO

IF  EXISTS (
        SELECT name
                FROM sys.DATABASES
                WHERE name = N'ElenaBeklenishcheva'
)
DROP DATABASE [ElenaBeklenishcheva]
GO

CREATE DATABASE [ElenaBeklenishcheva]
GO

USE [ElenaBeklenishcheva]
GO

IF EXISTS(
  SELECT *
    FROM sys.schemas
   WHERE name = N'Scheme'
)
 DROP SCHEMA Scheme
GO

CREATE SCHEMA Scheme
GO


IF OBJECT_ID('units', 'U') IS NOT NULL
  DROP TABLE units
GO


--персональные данные
CREATE TABLE units (
    id INT PRIMARY KEY NOT NULL,
    surname VARCHAR(50) NOT NULL,
    name VARCHAR(50) NOT NULL,
    patronymic VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    street VARCHAR(50) NOT NULL,
    house INT NOT NULL,
    flat INT,
    personal_number BIGINT,
    home_number BIGINT
)
GO

--корректность номеров
ALTER TABLE units
ADD CONSTRAINT CHK_units CHECK (
    personal_number >= 9000000000 AND
    personal_number <= 9999999999 AND
    home_number > 9999
)
GO

INSERT INTO units VALUES
(1, 'Абрамов', 'Августин', 'Алексеевич', 'Екатеринбург', 'Ленина', 45, 5, 9012345678, NULL),
(2, 'Авдеев', 'Андрей', 'Антонович', 'Екатеринбург', 'Малышева', 1, 2, 9014569885, NULL),
(3, 'Агафонов', 'Константин', 'Кириллович', 'Богданович', 'Мира', 2, 3, NULL, 83439342793),
(4, 'Андреев', 'Дмитрий', 'Александрович', 'Хабаровск', 'Советская', 21, 13, 9051235660, 83432342793),
(5, 'Алексеев', 'Василий', 'Иванович', 'Новосибирск', 'Прокопьева', 115, 23, 9051235662, NULL),
(6, 'Баранов','Виктор', 'Константинович', 'Краснодар', 'Алюминиевая', 21, 1, 9051235664, 83432342794),
(7, 'Буров', 'Антон', 'Михайлович', 'Белореченск', 'Брянская', 212, 131, NULL, 83432342796),
(8, 'Быкова', 'Анастасия', 'Андреевна', 'Ханты-мансийск', 'Мичурина', 213, 113, NULL, NULL),
(9, 'Васильева', 'Екатерина', 'Васильевна', 'Екатеринбург', 'Победы', 21, 13, 9051435660, 83432342756),
(10, 'Воробьева', 'Дарья', 'Андреевна', 'Ревда', 'Гагарина', 219, 3, 9051234560, 83432395793),
(11, 'Власова', 'Ольга', 'Андреевна', 'Каменск-Уральский', 'Шестакова', 21, 13, 9051245660, NULL)
GO


IF OBJECT_ID('committee', 'U') IS NOT NULL
    DROP TABLE committee
GO
--существующие коммиссии
CREATE TABLE committee (
    id INT PRIMARY KEY NOT NULL,
    questions VARCHAR(100) NOT NULL
)
GO

INSERT INTO committee VALUES
(1, 'Аграрные вопросы'),
(2, 'Бюджет и налоги'),
(3, 'Общественные вопросы'),
(4, 'Культура'),
(5, 'Жилищная политика'),
(6, 'Оборона'),
(7, 'Медицина')
GO


IF OBJECT_ID('structure', 'U') IS NOT NULL
    DROP TABLE STRUCTURE
GO
--состав комиссий; 1 - был или есть предселатель
CREATE TABLE STRUCTURE (
    committee_id INT NOT NULL,
    unit_id INT NOT NULL,
    inclusion DATE NOT NULL,
    expulsion DATE,
    isChairman bit NOT NULL,
    chairmanFirstDay DATE,
    chairmanLastDay DATE

    FOREIGN KEY (committee_id) REFERENCES committee(id),
    FOREIGN KEY (unit_id) REFERENCES units(id)
)
GO

/*INSERT INTO structure VALUES
(1, 1, '20151128', '20151228', 0, null, null)
GO

INSERT INTO structure VALUES
(1, 2, '20151128', '20151228', 1, '20151128', null)
GO

INSERT INTO structure VALUES
(2, 3, '20151128', '20151228', 1, '20151201', '20151215')
GO*/

--корректные даты работы в комиссии
--корректные даты работы председателей
ALTER TABLE STRUCTURE
ADD CONSTRAINT chk_structure CHECK (
    (expulsion IS NULL OR expulsion > inclusion) AND
    (
        isChairman = 0 AND
        chairmanFirstDay IS NULL AND
        chairmanLastDay IS NULL
    OR
        isChairman = 1 AND
        NOT(chairmanFirstDay IS NULL) AND
        (inclusion < chairmanFirstDay OR inclusion = chairmanFirstDay) AND
        (
            chairmanLastDay IS NULL OR
            chairmanFirstDay < chairmanLastDay AND
            (expulsion IS NULL OR expulsion >chairmanLastDay OR expulsion = chairmanLastDay)
        )
    )
)
GO

IF OBJECT_ID('trigForChairman', 'TR') IS NOT NULL
    DROP TRIGGER trigForChairman
GO

CREATE TRIGGER trigForChairman ON STRUCTURE FOR INSERT AS
BEGIN
    -- чтобы в каждый период времени был только один предеседатель
    SELECT *
    FROM inserted
    SELECT * FROM STRUCTURE
    DECLARE @counter1 INT;
    DECLARE @counter2 INT;
    SET @counter1 = (
            SELECT COUNT(*)
            FROM inserted, STRUCTURE
            WHERE (
                STRUCTURE.isChairman=1 AND
                inserted.committee_id = STRUCTURE.committee_id AND
                inserted.isChairman=1 AND
                inserted.chairmanLastDay IS NULL AND
                STRUCTURE.chairmanLastDay IS NULL
            )
        );
    SET @counter2 = (
        SELECT COUNT(*)
        FROM STRUCTURE, inserted
        WHERE
            STRUCTURE.isChairman=1 AND
            inserted.committee_id = STRUCTURE.committee_id AND
            inserted.isChairman=1 AND
            (
                (
                    (
                        STRUCTURE.chairmanFirstDay < inserted.chairmanFirstDay
                        OR
                        STRUCTURE.chairmanFirstDay = inserted.chairmanFirstDay
                    )
                    AND (
                        STRUCTURE.chairmanLastDay > inserted.chairmanFirstDay
                        OR
                        STRUCTURE.chairmanLastDay IS NULL
                    )
                ) OR (
                    STRUCTURE.chairmanFirstDay > inserted.chairmanFirstDay
                    OR
                    STRUCTURE.chairmanFirstDay = inserted.chairmanFirstDay
                ) AND (
                    STRUCTURE.chairmanFirstDay < inserted.chairmanLastDay
                    OR
                    STRUCTURE.chairmanLastDay < inserted.chairmanLastDay
                    OR
                    STRUCTURE.chairmanLastDay = inserted.chairmanLastDay
                    OR inserted.chairmanLastDay IS NULL
                    OR (
                        STRUCTURE.chairmanLastDay IS NULL
                        AND
                        inserted.chairmanLastDay >STRUCTURE.chairmanFirstDay
                    )
                )
            )
        );
    print(@counter1)
    print(@counter2)
    IF (@counter1 > 1 OR @counter2 > 1)
    BEGIN
        print ('Председатель данной компании уже есть');
        ROLLBACK TRANSACTION;
        RETURN
    END
END
GO

--bad examples
/*INSERT INTO structure VALUES
(1, 1, '20150101', '20160101', 1, '20150401', '20150425')
GO*/

/*INSERT INTO structure VALUES
(1, 2, '20150101', '20151231', 1, '20150201', '20150410')
GO*/

/*INSERT INTO structure VALUES
(1, 2, '20150101', '20151231', 1, '20150201', '20150510')
GO*/

/*INSERT INTO structure VALUES
(1, 2, '20150101', '20151231', 1, '20150201', null)
GO*/
/*INSERT INTO structure VALUES
(2, 1, '20150101', '20151231', 1, '20150201', null)
GO

INSERT INTO structure VALUES
(1, 2, '20150101', '20151231', 1, '20150201', '20150510')
GO

INSERT INTO structure VALUES
(1, 2, '20150101', '20151231', 1, '20150301', '20150510')
GO*/

/*INSERT INTO structure VALUES
(3, 1, '20150101', '20151231', 1, '20150201', '20150510')
GO

INSERT INTO structure VALUES
(3, 2, '20150101', '20151231', 1, '20150301', '20150310')
GO*/

/*INSERT INTO structure VALUES
(4, 1, '20150101', '20151231', 1, '20150201', null)
GO

INSERT INTO structure VALUES
(4, 1, '20150101', '20151231', 1, '20150301', '20150401')
GO
*/
SELECT * FROM STRUCTURE


INSERT INTO STRUCTURE VALUES
(1, 11, '20000101', '20050101', 1, '20010101', '20030101')
GO
INSERT INTO STRUCTURE VALUES
(2, 2, '20121211', NULL, 1, '20121211', NULL)
GO
INSERT INTO STRUCTURE VALUES
(1, 1, '20110611', NULL, 1, '20110611', NULL)
GO
INSERT INTO STRUCTURE VALUES
(3, 3, '20101010', NULL, 1, '20101025', NULL)
GO
INSERT INTO STRUCTURE VALUES
(4, 4, '20131010', NULL, 1, '20131212', NULL)
GO
INSERT INTO STRUCTURE VALUES
(5, 5, '20141010', NULL, 1, '20150101', NULL)
GO
INSERT INTO STRUCTURE VALUES
(6, 6, '20151010', NULL, 0, NULL, NULL)
GO
INSERT INTO STRUCTURE VALUES
(7, 7, '20101010', NULL, 0, NULL, NULL)
GO
INSERT INTO STRUCTURE VALUES
(1, 8, '20110611', NULL, 0, NULL, NULL)
GO
INSERT INTO STRUCTURE VALUES
(2, 9, '20121211', NULL, 0, NULL, NULL)
GO
INSERT INTO STRUCTURE VALUES
(3, 10, '20101010', NULL, 0, NULL, NULL)
GO

SELECT * FROM STRUCTURE
GO


IF OBJECT_ID('meeting', 'U') IS NOT NULL
    DROP TABLE meeting
GO
--все собрания
CREATE TABLE meeting (
    meeting_date DATE NOT NULL,
    meeting_place VARCHAR(50) NOT NULL,
    committee_id INT NOT NULL,
    chairman_id INT NOT NULL,
    id_meeting INT PRIMARY KEY NOT NULL

    FOREIGN KEY (committee_id) REFERENCES committee(id),
    FOREIGN KEY (chairman_id) REFERENCES units(id)
)
GO

INSERT INTO meeting VALUES
('20151124', 'Ленина, 15', 1, 1, 1),
('20151124', 'Ленина, 45', 2, 2, 2),
('20151124', 'Ленина, 1', 3, 3, 3),
('20151128', 'Советская, 12', 1, 1, 4)
GO


IF OBJECT_ID('meeting_structure', 'U') IS NOT NULL
    DROP TABLE meeting_structure
GO
--участники собрания
CREATE TABLE meeting_structure (
    record_num INT PRIMARY KEY NOT NULL,
    meet_id INT NOT NULL,
    unit_id INT NOT NULL

    FOREIGN KEY (unit_id) REFERENCES units(id),
    FOREIGN KEY (meet_id) REFERENCES meeting(id_meeting)
)
GO

--проверить, чтобы один человек не может заседать в 2 коммиссиях сразу
IF OBJECT_ID('trig_meet', 'U') IS NOT NULL
    DROP TRIGGER trig_meet
GO

CREATE TRIGGER trig_meet ON meeting_structure AFTER INSERT
AS
BEGIN
    DECLARE @counter INT;
    SET @counter = (
        SELECT COUNT(*)
        FROM meeting_structure
            INNER JOIN meeting AS Smeet ON Smeet.id_meeting = meeting_structure.meet_id
        WHERE meeting_structure.record_num IN (
            SELECT inserted.record_num
            FROM inserted
                INNER JOIN meeting AS Imeet ON Imeet.id_meeting = inserted.meet_id
            WHERE
                inserted.unit_id = meeting_structure.unit_id AND
                inserted.meet_id != meeting_structure.meet_id AND
                Smeet.meeting_date = Imeet.meeting_date AND
                Smeet.committee_id != Imeet.committee_id
        )
    )
    IF (@counter > 1)
    BEGIN
        print ('Данный человек уже находится на одном из собраний в данное время');
        ROLLBACK TRANSACTION;
        RETURN
    END
END
GO

INSERT INTO meeting_structure VALUES
(1, 1, 1)
INSERT INTO meeting_structure VALUES
(2, 2, 2)
INSERT INTO meeting_structure VALUES
(3, 3, 3)
GO

INSERT INTO meeting_structure VALUES
(4, 4, 1),
(5, 4, 8)
GO

--списки коммиссий
SELECT committee.questions AS 'Название комитета',
       units.surname AS 'Фамилия',
       units.name AS 'Имя',
       units.patronymic AS 'Отчество'
FROM STRUCTURE
    INNER JOIN units ON units.id = STRUCTURE.unit_id
    INNER JOIN committee ON committee.id = STRUCTURE.committee_id
ORDER BY committee.questions
GO

SELECT* FROM STRUCTURE
GO

-- председатели за определенный период для конкретной коммиссии
DECLARE @START DATE = '20000101';
DECLARE @END DATE = '20151128';
DECLARE @committee INT=1;
SELECT units.surname AS 'Фамилия',
       units.name AS 'Имя',
       units.patronymic AS 'Отчество',
       STRUCTURE.chairmanFirstDay AS 'Начало работы председателем',
       STRUCTURE.chairmanLastDay AS 'Окончание работы председателем'
FROM STRUCTURE
    INNER JOIN units ON units.id = STRUCTURE.unit_id
WHERE STRUCTURE.committee_id = @committee AND
    (STRUCTURE.inclusion > @START OR STRUCTURE.inclusion = @START) AND
    (STRUCTURE.expulsion < @END OR STRUCTURE.expulsion = @END OR STRUCTURE.expulsion IS NULL)
    AND STRUCTURE.isChairman = 1
ORDER BY STRUCTURE.chairmanFirstDay
GO


--для каждого члена думы коммитеты, в которых он состоит
DECLARE @curr_date DATE = CONVERT (DATE, GETDATE());
print(@curr_date)
SELECT units.surname AS 'Фамилия',
       units.name AS 'Имя',
       units.patronymic AS 'Отчество',
       committee.questions AS 'Коммитет',
       STRUCTURE.isChairman AS 'Был ли председателем'
FROM STRUCTURE
    INNER JOIN units ON units.id = STRUCTURE.unit_id
    INNER JOIN committee ON committee.id = STRUCTURE.committee_id
WHERE STRUCTURE.expulsion IS NULL OR STRUCTURE.expulsion > @curr_date
GO


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


CREATE FUNCTION consisted()
RETURNS @ret_consist TABLE(
    id INT PRIMARY KEY,
    missed INT NOT NULL)
AS
BEGIN

    DECLARE @START DATE = '20000101';
    DECLARE @END DATE = '20151128';
    DECLARE @committee INT=1;

    DECLARE @units INT = (SELECT COUNT(*) FROM units)
    DECLARE @counter INT = 1
    DECLARE @temp_table TABLE (
        tid INT PRIMARY KEY,
        tmissed INT NOT NULL
    )
    WHILE (@counter <= @units)
    BEGIN
        DECLARE @meetings INT = (SELECT COUNT(*) FROM meeting)
        DECLARE @counter_m INT = 1
        DECLARE @missed INT = 0
        WHILE(@counter_m <= @meetings)
        BEGIN
             IF (EXISTS (SELECT *
                         FROM meeting_structure
                         INNER JOIN meeting ON meeting.id_meeting = meeting_structure.meet_id
                         WHERE meeting_structure.meet_id = @counter_m AND
                               meeting_structure.unit_id = @counter AND
                               (meeting_date > @START OR  meeting_date = @START) AND
                               (meeting_date < @END OR meeting_date = @END)
                        )
                )
                BEGIN
                SET @missed = @missed + 1
                END
            SET @counter_m = @counter_m + 1

        END
        INSERT INTO @temp_table VALUES(@counter, @missed)
        SET @counter = @counter + 1
    END

    INSERT @ret_consist
    SELECT *
    FROM @temp_table
    RETURN
END
GO

SELECT * FROM units
GO

SELECT * FROM STRUCTURE
GO

SELECT * FROM meeting
GO

SELECT * FROM meeting_structure
GO

SELECT units.surname AS 'Фамилия',
       units.name AS 'Имя',
       units.patronymic AS 'Отчество',
       consisted.missed AS 'Пропущено'
FROM consisted()
    INNER JOIN units ON units.id = consisted.id
GO


-- Вывести список заседаний в указанный интервал дат в хронологическом порядке, для каждого заседания – список присутствующих.
DECLARE @START DATE = '20000101';
DECLARE @END DATE = '20151128';
SELECT committee.questions AS 'Комитет',
       meeting.meeting_date AS 'Дата собрания',
       units.surname AS 'Фамилия',
       units.name AS 'Имя',
       units.patronymic AS 'Отчество'
FROM meeting
    INNER JOIN committee ON committee.id = meeting.committee_id
    INNER JOIN meeting_structure ON meeting_structure.meet_id = meeting.id_meeting
    INNER JOIN units ON units.id = meeting_structure.unit_id
WHERE (meeting.meeting_date > @START OR meeting.meeting_date = @START) AND
    (meeting.meeting_date < @END OR meeting.meeting_date = @END)
ORDER BY meeting.meeting_date
GO


--По каждой комиссии показать количество проведенных заседаний в указанный период времени
DECLARE @START DATE = '20000101';
DECLARE @END DATE = '20151128';

SELECT committee.questions AS 'Комитет',
       COUNT(meeting.id_meeting) AS 'Число собраний'
FROM meeting
    INNER JOIN committee ON committee.id = meeting.committee_id
GROUP BY committee.questions