daster/source/sql.d

module sql;

import pgdb;
import singlog;
import structures;

import std.conv;

/***********************************************************
            Запросы для таблицы номеров телефонов
***********************************************************/

GroupDB[] sqlGetListGroups() {
    GroupDB[] groups;
    try {
        auto queryResult = pgsql.sql(
            "select distinct
                dan.da_group,
                dag.da_comment
            from da_numbers dan
            left join da_groups dag ON dag.da_name = dan.da_group"
        );
        foreach (row; queryResult) {
            GroupDB data;

            data.name = row["da_group"];
            data.comment = row["da_comment"];

            groups ~= data;
        }
    } catch (Exception e) {
        log.e("Не удалось выполнить запрос к БД. " ~ e.msg);
    }

    return groups;
}

NumberDB[] sqlGetListNumbers(string group) {
    NumberDB[] numbers;
    try {
        auto queryResult = pgsql.sql(
            "select
                dan.da_number,
                dal.da_comment da_list,
                dan.da_all_cc,
                dan.da_white_cc,
                dan.da_black_cc,
                dan.da_comment
            from da_numbers dan
            left join da_lists dal on dal.da_name = dan.da_list
            where dan.da_group = ?
            order by dan.da_number",
            group
        );
        foreach (row; queryResult) {
            NumberDB data;

            data.number = row["da_number"];
            data.list = row["da_list"];
            data.all_cc = row["da_all_cc"].to!int;
            data.white_cc = row["da_white_cc"].to!int;
            data.black_cc = row["da_black_cc"].to!int;
            data.comment = row["da_comment"];

            numbers ~= data;
        }
    } catch (Exception e) {
        log.e("Не удалось выполнить запрос к БД. " ~ e.msg);
    }

    return numbers;
}

NumberDB sqlGetDataNumber(string number) {
    NumberDB data;
    try {
        auto queryResult = pgsql.sql(
            "select
                da_number,
                da_group,
                da_list,
                da_all_cc,
                da_white_cc,
                da_black_cc,
                da_comment
            from da_numbers
            where da_number = ?",
            number
        );
        foreach (row; queryResult) {
            data.number = row["da_number"];
            data.group = row["da_group"];
            data.list = row["da_list"];
            data.all_cc = row["da_all_cc"].to!int;
            data.white_cc = row["da_white_cc"].to!int;
            data.black_cc = row["da_black_cc"].to!int;
            data.comment = row["da_comment"];
        }
    } catch (Exception e) {
        log.e("Не удалось выполнить запрос к БД. " ~ e.msg);
    }

    return data;
}

GroupDB[] sqlGetGroups() {
    GroupDB[] groups;
    try {
        auto queryResult = pgsql.sql(
            "select da_name, da_comment from da_groups"
        );
        foreach (row; queryResult) {
            GroupDB data;

            data.name = row["da_name"];
            data.comment = row["da_comment"];

            groups ~= data;
        }
    } catch (Exception e) {
        log.e("Не удалось выполнить запрос к БД. " ~ e.msg);
    }

    return groups;
}

ListDB[] sqlGetLists() {
    ListDB[] lists;
    try {
        auto queryResult = pgsql.sql(
            "select da_name, da_comment from da_lists"
        );
        foreach (row; queryResult) {
            ListDB data;

            data.name = row["da_name"];
            data.comment = row["da_comment"];

            lists ~= data;
        }
    } catch (Exception e) {
        log.e("Не удалось выполнить запрос к БД. " ~ e.msg);
    }

    return lists;
}

bool sqlUpdateNumber(NumberDB number) {
    try {
        pgsql.sql(
            "update da_numbers set
                da_group = ?,
                da_list = ?,
                da_all_cc = ?,
                da_white_cc = ?,
                da_black_cc = ?,
                da_comment = ?
            where da_number = ?",
            number.group,
            number.list,
            number.all_cc,
            number.white_cc,
            number.black_cc,
            number.comment,
            number.number
        );
    } catch (Exception e) {
        log.e("Ошибка обновления номера в БД. " ~ e.msg);
        return false;
    }
    return true;
}

bool sqlDeleteNumber(string number) {
    try {
        pgsql.sql(
            "delete from da_numbers where da_number = ?", number
        );
    } catch (Exception e) {
        log.e("Ошибка удаления номера в БД. " ~ e.msg);
        return false;
    }
    return true;
}

bool sqlInsertNumber(NumberDB number) {
    try {
        pgsql.sql(
            "insert into da_numbers
                (da_number, da_group, da_list, da_all_cc, da_white_cc, da_black_cc, da_comment)
            values
                (?, ?, ?, ?, ?, ?, ?)",
            number.number,
            number.group,
            number.list,
            number.all_cc,
            number.white_cc,
            number.black_cc,
            number.comment
        );
    } catch (Exception e) {
        log.error("Ошибка добавления номера телефона в БД. " ~ e.msg);
        return false;
    }
    return true;
}

/***********************************************************
                    Запросы для таблицы SMS
***********************************************************/

SMSDB[] sqlGetSMSNumbers() {
    SMSDB[] numbers;
    try {
        auto queryResult = pgsql.sql(
            "select distinct da_to from da_sms"
        );
        foreach (row; queryResult) {
            SMSDB data;

            data.to = row["da_to"];

            numbers ~= data;
        }
    } catch (Exception e) {
        log.e("Не удалось выполнить запрос к БД. " ~ e.msg);
    }

    return numbers;
}

SMSDB[] sqlGetListSMS(string to) {
    SMSDB[] sms;
    try {
        auto queryResult = pgsql.sql(
            "select
                da_id,
                to_char(da_date, 'YYYY.MM.DD HH24:MI:SS') da_date,
                da_to,
                da_from,
                da_text
            from da_sms
            where da_to = ?
            order by da_date desc",
            to
        );
        foreach (row; queryResult) {
            SMSDB data;

            data.id = row["da_id"].to!int;
            data.date = row["da_date"];
            data.to = row["da_to"];
            data.from = row["da_from"];
            data.text = row["da_text"];

            sms ~= data;
        }
    } catch (Exception e) {
        log.e("Не удалось выполнить запрос к БД. " ~ e.msg);
    }

    return sms;
}

SMSDB sqlGetSMS(int idsms) {
    SMSDB data;
    try {
        auto queryResult = pgsql.sql(
            "select
                da_id,
                to_char(da_date, 'YYYY.MM.DD HH24:MI:SS') da_date,
                da_to,
                da_from,
                da_text
            from da_sms
            where da_id = ?",
            idsms
        );
        foreach (row; queryResult) {
            data.id = row["da_id"].to!int;
            data.date = row["da_date"];
            data.to = row["da_to"];
            data.from = row["da_from"];
            data.text = row["da_text"];
        }
    } catch (Exception e) {
        log.e("Не удалось выполнить запрос к БД. " ~ e.msg);
    }

    return data;
}

bool sqlDeleteSMS(int idsms) {
    try {
        pgsql.sql(
            "delete from da_sms where da_id = ?", idsms
        );
    } catch (Exception e) {
        log.e("Ошибка удаления SMS в БД. " ~ e.msg);
        return false;
    }
    return true;
}

/***********************************************************
                Запросы для таблицы USSD
***********************************************************/

USSDDB[] sqlGetUSSDNumbers() {
    USSDDB[] numbers;
    try {
        auto queryResult = pgsql.sql(
            "select distinct da_to from da_ussd"
        );
        foreach (row; queryResult) {
            USSDDB data;

            data.to = row["da_to"];

            numbers ~= data;
        }
    } catch (Exception e) {
        log.e("Не удалось выполнить запрос к БД. " ~ e.msg);
    }

    return numbers;
}

USSDDB[] sqlGetListUSSD(string to) {
    USSDDB[] ussd;
    try {
        auto queryResult = pgsql.sql(
            "select
                dau.da_id,
                to_char(dau.da_date, 'YYYY.MM.DD HH24:MI:SS') da_date,
                dau.da_to,
                dau.da_type,
                daut.da_comment da_type_comment,
                dau.da_text
            from da_ussd dau
            inner join da_ussd_type daut
            on dau.da_type = daut.da_id
            where dau.da_to = ?
            order by dau.da_date desc",
            to
        );
        foreach (row; queryResult) {
            USSDDB data;

            data.id = row["da_id"].to!int;
            data.date = row["da_date"];
            data.to = row["da_to"];
            data.type = row["da_type"].to!int;
            data.type_comment = row["da_type_comment"];
            data.text = row["da_text"];

            ussd ~= data;
        }
    } catch (Exception e) {
        log.e("Не удалось выполнить запрос к БД. " ~ e.msg);
    }

    return ussd;
}

USSDDB sqlGetUSSD(int idussd) {
    USSDDB data;
    try {
        auto queryResult = pgsql.sql(
            "select
                da_id,
                to_char(da_date, 'YYYY.MM.DD HH24:MI:SS') da_date,
                da_to,
                da_type,
                da_text
            from da_ussd
            where da_id = ?",
            idussd
        );
        foreach (row; queryResult) {
            data.id = row["da_id"].to!int;
            data.date = row["da_date"];
            data.to = row["da_to"];
            data.type = row["da_type"].to!int;
            data.text = row["da_text"];
        }
    } catch (Exception e) {
        log.e("Не удалось выполнить запрос к БД. " ~ e.msg);
    }

    return data;
}

bool sqlDeleteUSSD(int idussd) {
    try {
        pgsql.sql(
            "delete from da_ussd where da_id = ?", idussd
        );
    } catch (Exception e) {
        log.e("Ошибка удаления USSD в БД. " ~ e.msg);
        return false;
    }
    return true;
}

/***********************************************************
            Запросы для таблицы информации о сервере
***********************************************************/

ServerDB sqlGetServerInfo() {
    ServerDB server;
    try {
        auto queryResult = pgsql.sql(
            "select
                case when da_transparent_mode then 1 else 0 end da_transparent_mode,
                da_internal_number,
                da_external_number,
                case when da_external_number_on then 1 else 0 end da_external_number_on
            from da_server
            where da_id = 1"
        );
        foreach (row; queryResult) {
            server.transparent_mode = row["da_transparent_mode"].to!int.to!bool;
            server.internal_number = row["da_internal_number"];
            server.external_number = row["da_external_number"];
            server.external_number_on = row["da_external_number_on"].to!int.to!bool;
        }
    } catch (Exception e) {
        log.e("Не удалось выполнить запрос к БД. " ~ e.msg);
    }

    return server;
}

bool sqlUpdateServerInfo(ServerDB server) {
    try {
        pgsql.sql(
            "update da_server set
                da_transparent_mode = ?,
                da_internal_number = ?,
                da_external_number = ?,
                da_external_number_on = ?
            where da_id = 1",
            server.transparent_mode,
            server.internal_number,
            server.external_number,
            server.external_number_on
        );
    } catch (Exception e) {
        log.e("Ошибка обновления параметров сервера в БД. " ~ e.msg);
        return false;
    }
    return true;
}