Part4_SQL/Task_4_3_1/ClientDB.cpp - master - ArtT/Netology

Part4_SQL/Task_4_3_1/ClientDB.cpp
129 строк3 KB
ArtT
Изменил тип возвращаемого значения в функции find_client для задания 4_3_1
29 апр 2026, 08:26
29 апр 2026, 08:2623fa359
#include "ClientDB.h"
#include <iostream>

void ClientDB::create_tables()
{
    pqxx::work tx(conn_);

    tx.exec(R"(
        CREATE TABLE IF NOT EXISTS clients (
            id SERIAL PRIMARY KEY,
            first_name TEXT NOT NULL,
            last_name TEXT NOT NULL,
            email TEXT UNIQUE
        )
    )");

    tx.exec(R"(
        CREATE TABLE IF NOT EXISTS phones (
            id SERIAL PRIMARY KEY,
            client_id INT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
            phone TEXT NOT NULL
        )
    )");

    tx.commit();
}

int ClientDB::add_client(const std::string& first_name, const std::string& last_name, const std::string& email)
{
    pqxx::work tx(conn_);

    pqxx::result r = tx.exec_params(
        "INSERT INTO clients (first_name, last_name, email) "
        "VALUES ($1, $2, NULLIF($3, '')) RETURNING id",
        first_name, last_name, email);

    int id = r[0][0].as<int>();
    tx.commit();
    return id;
}

void ClientDB::add_phone(int client_id, const std::string& phone)
{
    pqxx::work tx(conn_);

    tx.exec_params(
        "INSERT INTO phones (client_id, phone) VALUES ($1, $2)",
        client_id, phone);

    tx.commit();
}

void ClientDB::update_client(int client_id, const std::string& first_name, const std::string& last_name, const std::string& email)
{
    pqxx::work tx(conn_);

    tx.exec_params(
        "UPDATE clients "
        "SET first_name = $1, last_name = $2, email = NULLIF($3, '') "
        "WHERE id = $4",
        first_name, last_name, email, client_id);

    tx.commit();
}

void ClientDB::delete_phone(int client_id, const std::string& phone)
{
    pqxx::work tx(conn_);

    tx.exec_params(
        "DELETE FROM phones WHERE client_id = $1 AND phone = $2",
        client_id, phone);

    tx.commit();
}

void ClientDB::delete_client(int client_id)
{
    pqxx::work tx(conn_);

    tx.exec_params(
        "DELETE FROM clients WHERE id = $1",
        client_id);

    tx.commit();
}

std::vector<Client> ClientDB::find_client(const std::string& search)
{
    pqxx::work tx(conn_);

    pqxx::result r = tx.exec_params(R"(
        SELECT c.id, c.first_name, c.last_name, c.email, p.phone
        FROM clients c
        LEFT JOIN phones p ON c.id = p.client_id
        WHERE c.first_name = $1 OR c.last_name = $1 OR c.email = $1 OR p.phone = $1
        ORDER BY c.id
    )", search);

    std::vector<Client> clients;
    if (r.empty()) {
        return clients;
    }

    Client* last_client = nullptr;
    int last_id = -1;

    for (const auto& row : r)
    {
        int current_id = row["id"].as<int>();
        if (current_id != last_id) {
            Client c;
            c.id = current_id;
            c.first_name = row["first_name"].as<std::string>();
            c.last_name = row["last_name"].as<std::string>();
            c.email = row["email"].is_null() ? "" : row["email"].as<std::string>();

            clients.push_back(c);
            last_client = &clients.back();
            last_id = current_id;
        }

        if (!row["phone"].is_null()) {
            last_client->phones.push_back(row["phone"].as<std::string>());
        }
    }

    return clients;
}