SQLize | PHPize | SQLtest

-- Создание таблицы сотрудников CREATE TABLE users ( user_id INT PRIMARY KEY IDENTITY(1,1), surname NVARCHAR(50) NOT NULL, name NVARCHAR(50) NOT NULL, hire_date DATE NOT NULL, dismissal_date DATE NULL ); -- Создание таблицы расчетных периодов CREATE TABLE periods ( period_id INT PRIMARY KEY IDENTITY(1,1), start_date DATE NOT NULL, end_date DATE NOT NULL ); -- Создание таблицы статусов выплат CREATE TABLE payment_state ( state_id INT PRIMARY KEY IDENTITY(1,1), state_name NVARCHAR(50) NOT NULL ); -- Создание таблицы статей затрат CREATE TABLE cost_item ( cost_item_id INT PRIMARY KEY IDENTITY(1,1), cost_item_name NVARCHAR(100) NOT NULL, not_balance BIT NOT NULL DEFAULT 0 ); -- Создание таблицы ежемесячных начислений CREATE TABLE monthly_accrual ( accrual_id INT PRIMARY KEY IDENTITY(1,1), user_id INT NOT NULL, period_id INT NOT NULL, amount DECIMAL(16,2) NOT NULL, state_id INT NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (period_id) REFERENCES periods(period_id), FOREIGN KEY (state_id) REFERENCES payment_state(state_id) ); -- Создание таблицы дополнительных выплат CREATE TABLE additional_payment ( payment_id INT PRIMARY KEY IDENTITY(1,1), user_id INT NOT NULL, period_id INT NOT NULL, cost_item_id INT NOT NULL, amount DECIMAL(16,2) NOT NULL, state_id INT NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (period_id) REFERENCES periods(period_id), FOREIGN KEY (cost_item_id) REFERENCES cost_item(cost_item_id), FOREIGN KEY (state_id) REFERENCES payment_state(state_id) ); -- Заполнение таблицы сотрудников INSERT INTO users (surname, name, hire_date, dismissal_date) VALUES ('Иванов', 'Иван', '2009-11-23', NULL), ('Петрова', 'Елена', '2011-10-01', '2020-07-26'), ('Крыжовников', 'Евгений', '2022-01-15', NULL), ('Иванова', 'Александра', '2024-06-07', NULL), ('Сидоров', 'Матвей', '2015-12-02', '2024-09-21'), ('Петрова', 'Елена', '2024-04-29', NULL); -- Заполнение таблицы расчетных периодов INSERT INTO periods (start_date, end_date) VALUES ('2024-06-01', '2024-06-30'), ('2024-07-01', '2024-07-31'), ('2024-08-01', '2024-08-31'), ('2024-09-01', '2024-09-30'), ('2024-10-01', '2024-10-31'); -- Заполнение таблицы статусов выплат INSERT INTO payment_state (state_name) VALUES ('Новая'), ('На проверке'), ('Получено'), ('Отклонено'); -- Заполнение таблицы статей затрат INSERT INTO cost_item (cost_item_name, not_balance) VALUES ('Премия', 0), ('Материальные расходы', 0), ('Маркетинг и реклама', 1), ('Аренда и обслуживание', 0), ('Прочие расходы', 1); -- Заполнение таблицы ежемесячных начислений INSERT INTO monthly_accrual (user_id, period_id, amount, state_id) VALUES (1, 3, 60000, 3), -- Иванов Иван, Август 2024 (3, 3, 75000, 3), -- Крыжовников Евгений, Август 2024 (4, 3, 140000, 3), -- Иванова Александра, Август 2024 (5, 3, 80000, 3), -- Сидоров Матвей, Август 2024 (6, 3, 100000, 3), -- Петрова Елена, Август 2024 (1, 4, 60000, 3), -- Иванов Иван, Сентябрь 2024 (3, 4, 75000, 3), -- Крыжовников Евгений, Сентябрь 2024 (4, 4, 140000, 3), -- Иванова Александра, Сентябрь 2024 (5, 4, 80000, 3), -- Сидоров Матвей, Сентябрь 2024 (6, 4, 100000, 3), -- Петрова Елена, Сентябрь 2024 (1, 5, 60000, 2), -- Иванов Иван, Октябрь 2024 (3, 5, 75000, 2), -- Крыжовников Евгений, Октябрь 2024 (4, 5, 140000, 2), -- Иванова Александра, Октябрь 2024 (6, 5, 100000, 2); -- Петрова Елена, Октябрь 2024 -- Заполнение таблицы дополнительных выплат INSERT INTO additional_payment (user_id, period_id, cost_item_id, amount, state_id) VALUES (1, 3, 2, 4500, 4), -- Иванов Иван, Август 2024, Материальные расходы, Отклонено (6, 3, 1, 10000, 3), -- Петрова Елена, Август 2024, Премия, Получено (6, 4, 1, 15000, 3), -- Петрова Елена, Сентябрь 2024, Премия, Получено (1, 5, 5, 5000, 2), -- Иванов Иван, Октябрь 2024, Прочие расходы, На проверке (3, 5, 1, 10000, 1), -- Крыжовников Евгений, Октябрь 2024, Премия, Новая (6, 3, 1, 2500, 2); -- Петрова Елена, Август 2024, Премия, На проверке CREATE PROCEDURE balans_details AS BEGIN SELECT emp.name + ' ' + emp.surname as [Сотрудник], SUM(cast(isnull(dop.amount, 0) as decimal(16,2))) + SUM(cast(isnull(nach.amount, 0) as decimal(16,2))) as [Начислено], SUM(cast(isnull(poluch_dop.amount, 0) as decimal(16,2))) + SUM(cast(isnull(poluch_nach.amount, 0) as decimal(16,2))) as Получено FROM users emp (nolock) -- Дополнительные выплаты OUTER APPLY( SELECT isnull(sum(dop.amount), 0) as amount FROM dbo.additional_payment dop (nolock) --Доп выплаты WHERE emp.user_id = dop.user_id ) dop -- Начисления OUTER APPLY( SELECT isnull(nach.amount, 0) as amount FROM dbo.monthly_accrual nach (nolock) --Начисления WHERE emp.user_id = nach.user_id ) nach -- Получение доп. выплат OUTER APPLY( SELECT isnull(sum(poluch_dop.amount), 0) as amount FROM dbo.additional_payment poluch_dop (nolock) --Доп выплаты WHERE poluch_dop.state_id = 3 and emp.user_id = poluch_dop.user_id ) poluch_dop --Получение начислений OUTER APPLY( SELECT isnull(poluch_nach.amount, 0) as amount FROM dbo.monthly_accrual poluch_nach (nolock) --Начисления WHERE poluch_nach.state_id = 3 and emp.user_id = poluch_nach.user_id ) poluch_nach --where -- isnull(income.amount,0) != 0 -- or -- isnull(dop.amount, 0) != 0 -- or -- isnull(nach.amount, 0) != 0 -- or -- isnull(poluch_nach.amount, 0) != 0 -- or -- isnull(poluch_dop.amount, 0) != 0 GROUP BY emp.name, emp.surname, dop.amount, nach.amount ORDER BY emp.surname END -- Вызов процедуры для периода Август 2024 (period_id = 3) и сотрудника с user_id = 1 (Иванов Иван) EXEC balans_details @period_id = 3, @user_id = 1; SELECT * FROM balans_details