455 lines
12 KiB
D
455 lines
12 KiB
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;
|
||
}
|