---

Отлично, у меня есть все материалы для пункта 5 «Используемые методы». Структурирую его по трём разделам: Представления, Хранимые процедуры, Триггеры. Также добавлю простые и сложные запросы с листингами.

---

5 Используемые методы

В процессе разработки АИС «Автомастерская» были реализованы следующие объекты базы данных: представления, хранимые процедуры, триггеры, а также простые и сложные SQL-запросы.

5.1 Представления

Представления (views) используются для упрощения доступа к часто используемым данным и обеспечения дополнительного уровня безопасности.

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

В листинге 15 представлен SQL-код создания представления view_client_summary.

Листинг 15

```sql
CREATE VIEW public.view_client_summary AS
 SELECT cl.id,
    ((((cl.surname)::text || ' '::text) || (cl.first_name)::text) || COALESCE((' '::text || (cl.patronymic)::text), ''::text)) AS full_name,
    cl.phone,
    cl.email,
    count(DISTINCT c.id) AS cars_count,
    count(DISTINCT wo.id) AS orders_count,
    sum(
        CASE
            WHEN ((wo.status)::text = 'Выполнен'::text) THEN wo.total_cost
            ELSE (0)::numeric
        END) AS total_spent,
    max(wo.reception_date) AS last_visit_date
   FROM ((public.clients cl
     LEFT JOIN public.cars c ON ((cl.id = c.client_id)))
     LEFT JOIN public.work_orders wo ON ((c.id = wo.car_id)))
  GROUP BY cl.id, cl.surname, cl.first_name, cl.patronymic, cl.phone, cl.email;
```

Представление view_employee_performance отображает показатели эффективности сотрудников: количество выполненных заказов, выручку, средний чек и период активности.

В листинге 16 представлен SQL-код создания представления view_employee_performance.

Листинг 16

```sql
CREATE VIEW public.view_employee_performance AS
 SELECT e.id,
    (((e.surname)::text || ' '::text) || (e.first_name)::text) AS full_name,
    p.pos_name AS "position",
    count(DISTINCT wo.id) AS completed_orders,
    sum(wo.total_cost) AS generated_revenue,
    round(avg(wo.total_cost), 2) AS avg_order_value,
    min(wo.reception_date) AS first_order_date,
    max(wo.reception_date) AS last_order_date
   FROM ((public.employees e
     LEFT JOIN public.positions p ON ((e.position_id = p.id)))
     LEFT JOIN public.work_orders wo ON (((e.id = wo.master_id) AND ((wo.status)::text = 'Выполнен'::text))))
  GROUP BY e.id, e.surname, e.first_name, p.pos_name;
```

Представление view_inventory_status предоставляет детальный статус инвентаря запчастей: остатки, наценка, общее количество проданных единиц, стоимость запасов и уровень запасов.

В листинге 17 представлен SQL-код создания представления view_inventory_status.

Листинг 17

```sql
CREATE VIEW public.view_inventory_status AS
 SELECT sp.id,
    sp.part_name,
    sp.manufacturer,
    sp.quantity AS current_stock,
    sp.purchase_price,
    sp.sale_price,
    (sp.sale_price - sp.purchase_price) AS markup,
    round((((sp.sale_price - sp.purchase_price) / sp.purchase_price) * (100)::numeric), 2) AS markup_percent,
    COALESCE(sum(op.quantity), (0)::bigint) AS total_sold,
    ((sp.quantity)::numeric * sp.purchase_price) AS stock_value,
        CASE
            WHEN (sp.quantity = 0) THEN 'Нет в наличии'::text
            WHEN (sp.quantity < 5) THEN 'Критически мало'::text
            WHEN (sp.quantity < 15) THEN 'Мало'::text
            WHEN (sp.quantity < 30) THEN 'Достаточно'::text
            ELSE 'Много'::text
        END AS stock_level
   FROM (public.spare_parts sp
     LEFT JOIN public.order_parts op ON ((sp.id = op.part_id)))
  GROUP BY sp.id, sp.part_name, sp.manufacturer, sp.quantity, sp.purchase_price, sp.sale_price;
```

Представление view_monthly_financials отображает месячную финансовую статистику: количество заказов, уникальных клиентов, выручку, платежи и расчётную прибыль.

В листинге 18 представлен SQL-код создания представления view_monthly_financials.

Листинг 18

```sql
CREATE VIEW public.view_monthly_financials AS
 SELECT EXTRACT(year FROM wo.reception_date) AS year,
    EXTRACT(month FROM wo.reception_date) AS month,
    to_char((wo.reception_date)::timestamp with time zone, 'Month'::text) AS month_name,
    count(DISTINCT wo.id) AS orders_count,
    count(DISTINCT cl.id) AS unique_clients,
    sum(wo.total_cost) AS total_revenue,
    sum(p.amount) AS total_payments,
    (sum(wo.total_cost) - COALESCE(sum(((op.quantity)::numeric * sp.purchase_price)), (0)::numeric)) AS estimated_profit,
    round(avg(wo.total_cost), 2) AS avg_order_value
   FROM (((((public.work_orders wo
     LEFT JOIN public.cars c ON ((wo.car_id = c.id)))
     LEFT JOIN public.clients cl ON ((c.client_id = cl.id)))
     LEFT JOIN public.payments p ON ((wo.id = p."order_ID")))
     LEFT JOIN public.order_parts op ON ((wo.id = op.order_id)))
     LEFT JOIN public.spare_parts sp ON ((op.part_id = sp.id)))
  WHERE ((wo.status)::text = 'Выполнен'::text)
  GROUP BY (EXTRACT(year FROM wo.reception_date)), (EXTRACT(month FROM wo.reception_date)), (to_char((wo.reception_date)::timestamp with time zone, 'Month'::text))
  ORDER BY (EXTRACT(year FROM wo.reception_date)) DESC, (EXTRACT(month FROM wo.reception_date)) DESC;
```

Представление view_service_popularity предоставляет анализ популярности услуг: сколько раз выполнялась, в какие месяцы, общая выручка, средняя цена, скидки и доля рынка среди услуг.

В листинге 19 представлен SQL-код создания представления view_service_popularity.

Листинг 19

```sql
CREATE VIEW public.view_service_popularity AS
 SELECT s.id,
    s.category,
    s.service_name,
    count(DISTINCT os.order_id) AS times_performed,
    count(DISTINCT EXTRACT(month FROM wo.reception_date)) AS active_months,
    sum(os.actual_price) AS total_revenue,
    round(avg(os.actual_price), 2) AS avg_price,
    round(avg((os.discount * (100)::numeric)), 2) AS avg_discount_percent,
    min(wo.reception_date) AS first_performance_date,
    max(wo.reception_date) AS last_performance_date,
    round((((count(DISTINCT os.order_id))::numeric * 100.0) / (( SELECT count(*) AS count
           FROM public.order_services))::numeric), 2) AS market_share_percent
   FROM ((public.services s
     LEFT JOIN public.order_services os ON ((s.id = os.service_id)))
     LEFT JOIN public.work_orders wo ON (((os.order_id = wo.id) AND ((wo.status)::text = 'Выполнен'::text))))
  GROUP BY s.id, s.category, s.service_name
  ORDER BY (count(DISTINCT os.order_id)) DESC, (sum(os.actual_price)) DESC;
```

5.2 Хранимые процедуры

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

Процедура proc_add_new_client предназначена для добавления нового клиента в базу данных. Входные параметры: фамилия, имя, отчество, адрес, телефон, email, примечание. Выходной параметр: ID созданного клиента.

В листинге 20 представлен SQL-код создания процедуры proc_add_new_client.

Листинг 20

```sql
CREATE PROCEDURE public.proc_add_new_client(
    IN p_surname character varying,
    IN p_first_name character varying,
    IN p_patronymic character varying,
    IN p_address text,
    IN p_phone character varying,
    IN p_email character varying,
    IN p_note text,
    OUT p_client_id integer
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO clients (surname, first_name, patronymic, address, phone, email, note)
    VALUES (p_surname, p_first_name, p_patronymic, p_address, p_phone, p_email, p_note)
    RETURNING id INTO p_client_id;
END;
$$;
```

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

В листинге 21 представлен SQL-код создания процедуры proc_create_work_order.

Листинг 21

```sql
CREATE PROCEDURE public.proc_create_work_order(
    IN p_car_id integer,
    IN p_master_id integer,
    IN p_problem_description text,
    IN p_expected_days integer,
    OUT p_order_id integer
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_reception_date DATE := CURRENT_DATE;
    v_expected_date DATE := CURRENT_DATE + p_expected_days;
BEGIN
    INSERT INTO work_orders (car_id, master_id, reception_date, completion_date, status, total_cost, problem_description, expected_completion_date)
    VALUES (p_car_id, p_master_id, v_reception_date, NULL, 'В работе', 0, p_problem_description, v_expected_date)
    RETURNING id INTO p_order_id;
END;
$$;
```

Процедура proc_add_service_to_order предназначена для добавления услуги к существующему заказу с расчётом скидки. Входные параметры: ID заказа, ID услуги, размер скидки, примечание. Выходной параметр: ID созданной записи в order_services.

В листинге 22 представлен SQL-код создания процедуры proc_add_service_to_order.

Листинг 22

```sql
CREATE PROCEDURE public.proc_add_service_to_order(
    IN p_order_id integer,
    IN p_service_id integer,
    IN p_discount numeric,
    IN p_note text,
    OUT p_order_service_id integer
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_base_price NUMERIC(10,2);
    v_actual_price NUMERIC(10,2);
BEGIN
    SELECT base_price INTO v_base_price 
    FROM services 
    WHERE id = p_service_id;
    
    v_actual_price := v_base_price * (1 - p_discount);
    
    INSERT INTO order_services (order_id, service_id, discount, actual_price, note)
    VALUES (p_order_id, p_service_id, p_discount, v_actual_price, p_note)
    RETURNING id INTO p_order_service_id;
    
    UPDATE work_orders 
    SET total_cost = total_cost + v_actual_price
    WHERE id = p_order_id;
END;
$$;
```

Процедура proc_complete_work_order предназначена для завершения заказа, обновления статуса и создания платежа. Входные параметры: ID заказа, финальное примечание.

В листинге 23 представлен SQL-код создания процедуры proc_complete_work_order.

Листинг 23

```sql
CREATE PROCEDURE public.proc_complete_work_order(
    IN p_order_id integer,
    IN p_final_note text DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_total_cost NUMERIC(12,2);
BEGIN
    SELECT total_cost INTO v_total_cost 
    FROM work_orders 
    WHERE id = p_order_id;
    
    UPDATE work_orders 
    SET 
        status = 'Выполнен',
        completion_date = CURRENT_DATE,
        problem_description = COALESCE(p_final_note, problem_description)
    WHERE id = p_order_id;
    
    INSERT INTO payments (payment_type, amount, payment_method, date_payment, "order_ID")
    VALUES ('полная оплата', v_total_cost, 'Банковская карта', CURRENT_DATE, p_order_id);
END;
$$;
```

Процедура proc_generate_monthly_report предназначена для генерации детального месячного отчёта в формате JSON. Входные параметры: год и месяц. Выходной параметр: JSON с отчётом.

В листинге 24 представлен SQL-код создания процедуры proc_generate_monthly_report.

Листинг 24

```sql
CREATE PROCEDURE public.proc_generate_monthly_report(
    IN p_year integer,
    IN p_month integer,
    OUT p_report_json json
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_report_data JSON;
BEGIN
    SELECT json_build_object(
        'year', p_year,
        'month', p_month,
        'summary', json_build_object(
            'total_orders', COUNT(DISTINCT wo.id),
            'total_revenue', SUM(wo.total_cost),
            'unique_clients', COUNT(DISTINCT c.client_id),
            'most_popular_service', (
                SELECT s.service_name 
                FROM order_services os
                JOIN services s ON os.service_id = s.id
                JOIN work_orders wo2 ON os.order_id = wo2.id
                WHERE EXTRACT(YEAR FROM wo2.reception_date) = p_year
                    AND EXTRACT(MONTH FROM wo2.reception_date) = p_month
                GROUP BY s.service_name
                ORDER BY COUNT(*) DESC
                LIMIT 1
            )
        )
    ) INTO v_report_data
    FROM work_orders wo
    JOIN cars c ON wo.car_id = c.id
    WHERE EXTRACT(YEAR FROM wo.reception_date) = p_year
        AND EXTRACT(MONTH FROM wo.reception_date) = p_month
        AND wo.status = 'Выполнен';
    
    p_report_json := v_report_data;
END;
$$;
```

Процедура proc_reorder_parts предназначена для автоматического создания заказов на запчасти при низком уровне запасов. Входные параметры: минимальный уровень запаса, количество для заказа. Выходной параметр: количество созданных заказов.

В листинге 25 представлен SQL-код создания процедуры proc_reorder_parts.

Листинг 25

```sql
CREATE PROCEDURE public.proc_reorder_parts(
    IN p_min_stock_level integer,
    IN p_order_quantity integer,
    OUT p_orders_created integer
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_part RECORD;
    v_order_date DATE := CURRENT_DATE;
    v_expected_date DATE := CURRENT_DATE + INTERVAL '14 days';
BEGIN
    p_orders_created := 0;
    
    FOR v_part IN (
        SELECT sp.*, sc.supplier_id
        FROM spare_parts sp
        JOIN supplier_catalog sc ON sp.id = sc.part_id
        WHERE sp.quantity <= p_min_stock_level
            AND sc.is_active = true
        ORDER BY sp.quantity ASC
    ) LOOP
        INSERT INTO supply_orders (supplier_id, part_id, order_date, expected_date, quantity, status, employee_id)
        VALUES (v_part.supplier_id, v_part.id, v_order_date, v_expected_date, p_order_quantity, 'Обработка', 1);
        
        p_orders_created := p_orders_created + 1;
    END LOOP;
END;
$$;
```

Процедура proc_calculate_employee_bonus предназначена для расчёта бонусов сотрудникам на основе выручки за месяц. Входные параметры: год, месяц, процент бонуса. Выходные параметры: общая сумма бонусов, детали по сотрудникам в JSON.

В листинге 26 представлен SQL-код создания процедуры proc_calculate_employee_bonus.

Листинг 26

```sql
CREATE PROCEDURE public.proc_calculate_employee_bonus(
    IN p_year integer,
    IN p_month integer,
    IN p_bonus_percent numeric,
    OUT p_total_bonus numeric,
    OUT p_bonus_details json
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_bonus_data JSON;
BEGIN
    SELECT json_agg(
        json_build_object(
            'employee_id', e.id,
            'employee_name', e.surname || ' ' || e.first_name,
            'completed_orders', COUNT(DISTINCT wo.id),
            'generated_revenue', SUM(wo.total_cost),
            'bonus_amount', SUM(wo.total_cost) * p_bonus_percent / 100.0
        )
    ) INTO v_bonus_data
    FROM employees e
    LEFT JOIN work_orders wo ON e.id = wo.master_id
        AND EXTRACT(YEAR FROM wo.reception_date) = p_year
        AND EXTRACT(MONTH FROM wo.reception_date) = p_month
        AND wo.status = 'Выполнен'
    GROUP BY e.id;
    
    SELECT SUM((item->>'bonus_amount')::NUMERIC) INTO p_total_bonus
    FROM json_array_elements(v_bonus_data) AS item;
    
    p_bonus_details := v_bonus_data;
END;
$$;
```

5.3 Триггеры

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

Триггер check_vin_format предназначен для проверки формата VIN-номера при добавлении или изменении автомобиля. Проверяет длину (17 символов), допустимые символы (буквы A-HJ-NPR-Z и цифры) и уникальность VIN.

В листинге 27 представлен SQL-код создания функции и триггера check_vin_format.

Листинг 27

```sql
CREATE FUNCTION public.check_vin_format() RETURNS trigger
    LANGUAGE plpgsql
AS $$
BEGIN
    IF LENGTH(NEW.vin) != 17 THEN
        RAISE EXCEPTION 'VIN должен содержать ровно 17 символов';
    END IF;
    
    IF NEW.vin !~ '^[A-HJ-NPR-Z0-9]{17}$' THEN
        RAISE EXCEPTION 'VIN содержит недопустимые символы';
    END IF;
    
    IF EXISTS (SELECT 1 FROM cars WHERE vin = NEW.vin AND id != NEW.id) THEN
        RAISE EXCEPTION 'VIN % уже существует в базе данных', NEW.vin;
    END IF;
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_check_vin_format
    BEFORE INSERT OR UPDATE ON public.cars
    FOR EACH ROW
    EXECUTE FUNCTION public.check_vin_format();
```

Триггер log_order_status_change предназначен для логирования изменений статуса заказов. При изменении статуса в work_orders автоматически создаётся запись в таблице order_status_history.

В листинге 28 представлен SQL-код создания функции и триггера log_order_status_change.

Листинг 28

```sql
CREATE FUNCTION public.log_order_status_change() RETURNS trigger
    LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO order_status_history (order_id, old_status, new_status, change_date)
        VALUES (NEW.id, OLD.status, NEW.status, CURRENT_TIMESTAMP);
    END IF;
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_log_order_status_change
    BEFORE UPDATE ON public.work_orders
    FOR EACH ROW
    EXECUTE FUNCTION public.log_order_status_change();
```

Триггер check_salary_range предназначен для контроля зарплаты сотрудников в установленных пределах. Если зарплата ниже минимальной, устанавливается минимальная; если выше максимальной — устанавливается максимальная.

В листинге 29 представлен SQL-код создания функции и триггера check_salary_range.

Листинг 29

```sql
CREATE FUNCTION public.check_salary_range() RETURNS trigger
    LANGUAGE plpgsql
AS $$
DECLARE
    v_min_salary REAL;
    v_max_salary REAL;
BEGIN
    SELECT salary_min, salary_max INTO v_min_salary, v_max_salary
    FROM positions
    WHERE id = NEW.position_id;
    
    IF NEW.salary < v_min_salary THEN
        NEW.salary := v_min_salary;
    ELSIF NEW.salary > v_max_salary THEN
        NEW.salary := v_max_salary;
    END IF;
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_check_salary_range
    BEFORE INSERT OR UPDATE ON public.employees
    FOR EACH ROW
    EXECUTE FUNCTION public.check_salary_range();
```

5.4 Простые запросы

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

Листинг 30

```sql
SELECT 
    cl.id,
    cl.surname || ' ' || cl.first_name AS client_name,
    cl.phone,
    cl.email,
    COUNT(DISTINCT c.id) AS cars_owned,
    COUNT(DISTINCT wo.id) AS total_orders,
    SUM(CASE WHEN wo.status = 'Выполнен' THEN wo.total_cost ELSE 0 END) AS total_spent
FROM clients cl
LEFT JOIN cars c ON cl.id = c.client_id
LEFT JOIN work_orders wo ON c.id = wo.car_id
GROUP BY cl.id, cl.surname, cl.first_name, cl.phone, cl.email
ORDER BY id;
```

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

Листинг 31

```sql
SELECT 
    c.license_plate,
    c.brand,
    c.model,
    c.year_manufacture,
    cl.surname || ' ' || LEFT(cl.first_name, 1) || '.' || COALESCE(LEFT(cl.patronymic, 1) || '.', '') AS owner
FROM cars c
JOIN clients cl ON c.client_id = cl.id
ORDER BY c.brand, c.model;
```

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

Листинг 32

```sql
SELECT 
    e.surname || ' ' || LEFT(e.first_name, 1) || '.' || COALESCE(LEFT(e.patronymic, 1) || '.', '') AS full_name,
    p.pos_name AS position,
    e.salary,
    e.hire_date
FROM employees e
LEFT JOIN positions p ON e.position_id = p.id
ORDER BY e.salary DESC;
```

В листинге 33 представлен простой запрос для вывода всех заказов с их статусом и стоимостью.

Листинг 33

```sql
SELECT 
    wo.id AS order_id,
    c.brand || ' ' || c.model AS car,
    wo.reception_date,
    wo.completion_date,
    wo.status,
    wo.total_cost
FROM work_orders wo
JOIN cars c ON wo.car_id = c.id
ORDER BY wo.reception_date DESC;
```

В листинге 34 представлен простой запрос для вывода услуг с количеством выполненных раз и средней фактической ценой.

Листинг 34

```sql
SELECT 
    s.category,
    s.service_name,
    s.base_price,
    COUNT(DISTINCT os.order_id) AS times_performed,
    ROUND(AVG(os.actual_price), 2) AS avg_actual_price,
    ROUND(AVG(os.discount * 100), 2) AS avg_discount_percent
FROM services s
LEFT JOIN order_services os ON s.id = os.service_id
LEFT JOIN work_orders wo ON os.order_id = wo.id AND wo.status = 'Выполнен'
GROUP BY s.category, s.service_name, s.base_price
HAVING COUNT(DISTINCT os.order_id) > 0
ORDER BY s.category, times_performed D