/*
* db.cpp
*
* Created on: Mar 7, 2018
* Author: zlf
*/
#include <iostream>
#include <pqxx/pqxx>
#include <time.h>
#include <vector>
#include <string>
#include <string.h>
#include <sstream>
#include <iostream>
using namespace std;
using namespace pqxx;
//=======================================================//
struct DIAGNOSIS {
string patient_id;
string visit_number;
int sex;
string time_begin;
string time_end;
string icd;
string name;
};
//=======================================================//
void str2int(int &int_temp, const string &string_temp) {
stringstream stream(string_temp);
stream >> int_temp;
}
//=======================================================//
int interval_date(string begin, string end) {
string a1, a2, a3, b1, b2, b3;
a1 += begin[0];
a1 += begin[1];
a1 += begin[2];
a1 += begin[3];
a2 += begin[5];
a2 += begin[6];
a3 += begin[8];
a3 += begin[9];
b1 += end[0];
b1 += end[1];
b1 += end[2];
b1 += end[3];
b2 += end[5];
b2 += end[6];
b3 += end[8];
b3 += end[9];
//cout << "begin = " << a1 << " " << a2 << " " << a3 << endl;
//cout << "end = " << b1 << " " << b2 << " " << b3 << endl;
int int_a1, int_a2, int_a3, int_b1, int_b2, int_b3;
str2int(int_a1, a1);
str2int(int_a2, a2);
str2int(int_a3, a3);
str2int(int_b1, b1);
str2int(int_b2, b2);
str2int(int_b3, b3);
int interval_days;
interval_days = (int_b1 - int_a1) * 365 + (int_b2 - int_a2) * 30
+ (int_b3 - int_a3);
//cout << "interval_days = " << interval_days << endl;
return interval_days;
}
//=======================================================//
int interval_records(DIAGNOSIS begin, DIAGNOSIS end) {
int interval_days;
interval_days = interval_date(begin.time_begin, end.time_begin);
return interval_days;
}
//=======================================================//
bool flag_times_interval(int times, int interval, vector<DIAGNOSIS> vd) {
DIAGNOSIS origin = vd[0];
int interval_real;
int nums = 0;
for (int p = 1; p < vd.size(); p++) {
interval_real = interval_records(origin, vd[p]);
if (interval_real >= interval) {
nums = nums + 1;
origin = vd[p];
}
}
if (nums >= times) {
return true;
} else {
return false;
}
}
//=======================================================//
int select_insert(int times, int interval, vector<DIAGNOSIS> vd) {
cout << "select_insert() in the records of the same patient: " << endl;
connection C(
"dbname=hello user=postgres password=123 \
hostaddr=127.0.0.1 port=5432");
int num_selected = 0;
int num_managed = 0;
int my_flag = 0;
if (flag_times_interval(times, interval, vd)) {
for (int p = 1; p < vd.size(); p = p + 1) {
for (int q = 0; q < p; q = q + 1) {
if (vd[q].icd == vd[p].icd) { //bu xiang deng
break;
} else {
my_flag = my_flag + 1;
cout << vd[p].icd << " " << vd[q].icd << " "
<< "p q my_flag: " << p << " " << q << " "
<< my_flag << endl;
}
}
if (my_flag == p) {
//insert vd[p]
string sql1 =
"insert into diagnosis5 (patient_id, visit_number, time_begin, time_end, icd, name) values ('"
+ vd[p].patient_id + "', '" + vd[p].visit_number
+ "', '" + vd[p].time_begin + "', '"
+ vd[p].time_end + "', '" + vd[p].icd + "', '"
+ vd[p].name + "');";
char sql[300];
strcpy(sql, sql1.c_str());
/* Create a transactional object. */
work W(C);
/* Execute SQL query */
W.exec(sql);
W.commit();
num_selected = num_selected + 1;
}
my_flag = 0;
num_managed = num_managed + 1;
}
}
C.disconnect();
return num_selected;
}
//=======================================================//
void record2struct(result::const_iterator c, DIAGNOSIS *d) {
d->patient_id = c[0].as<string>();
d->visit_number = c[1].as<string>();
d->sex = c[2].as<int>();
d->time_begin = c[3].as<string>();
d->time_end = c[4].as<string>();
d->icd = c[5].as<string>();
d->name = c[6].as<string>();
}
//=======================================================//
//=======================================================//
int main(int argc, char* argv[]) {
int times = 2;
int interval = 2;
char * sql0;
try {
connection C0(
"dbname=hello user=postgres password=123 \
hostaddr=127.0.0.1 port=5432");
if (C0.is_open()) {
cout << "Opened database successfully: " << C0.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
/* Create SQL statement */
//sql = "select count(*) as num1 from diagnosis1";
//sql = "select * into diagnosis2 from diagnosis1 where icd is not null";
//sql = "select count(*) as num2 from diagnosis2";
//sql = "select * from diagnosis2 order by VisitNumber asc";
//sql = "select count(patient_id) ,patient_id from diagnosis2 group by patient_id having count(patient_id)>3";
//sql = "select count(patient_id), patient_id into diagnosis3 from diagnosis2 group by patient_id having count(patient_id)>3";
//sql = "select * into diagnosis4 from diagnosis2 where patient_id in (select patient_id from diagnosis3)";
sql0 = "select * from diagnosisx1 order by patient_id, time_begin asc";
/* Create a non-transactional object. */
nontransaction N(C0);
/* Execute SQL query */
result R(N.exec(sql0));
int num = 0;
/* List down all the records */
for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
cout << "PatientID = " << c[0].as<string>() << " ";
//cout << "VisitNumber = " << c[1].as<string>() << " ";
//cout << "Sex = " << c[2].as<int>() << " ";
cout << "time_begin = " << c[3].as<string>() << " ";
//cout << "time_end = " << c[4].as<string>() << " ";
//cout << "interval_date = " << interval_date(c[3].as<string>(), c[4].as<string>()) << " ";
cout << "ICD = " << c[5].as<string>() << " ";
cout << "name = " << c[6].as<string>() << " ";
num = num + 1;
cout << "num= " << num << endl;
if (num > 5000) {
break;
}
}
result::const_iterator p0 = R.begin();
DIAGNOSIS d;
record2struct(p0, &d);
vector<DIAGNOSIS> vd;
vd.push_back(d);
int num_managed = 0;
int num_selected = 0;
result::const_iterator p1 = R.begin();
for (int i = 0; i < R.size() - 1; i = i + 1) { //
cout << "i:::::" << i << endl;
p1 = p1 + 1;
num_managed++;
cout << "The number of the records of all patients been managed: "
<< num_managed << endl;
if (num_managed > 5000) {
break;
}
p0 = p1 - 1;
if (p0[0].as<string>() == p1[0].as<string>() && i < R.size() - 2) {
record2struct(p1, &d);
vd.push_back(d);
cout << "The same patient" << endl;
} else {
if (i == R.size() - 2) //p==R.size()-1,last record
{
record2struct(p1, &d);
vd.push_back(d);
num_selected = select_insert(times, interval, vd);
cout
<< "The number of the records of the same patient been selected: "
<< num_selected << endl;
cout
<< "Now all the records of the same patient have been push_back()"
<< endl;
cout
<< "The last record of the last patient have been push_back()"
<< endl;
} else {
record2struct(p1, &d);
num_selected = select_insert(times, interval, vd);
cout
<< "The number of the records of the same patient been selected: "
<< num_selected << endl;
cout
<< "Now all the records of the same patient have been push_back()"
<< endl;
vd.clear();
vd.push_back(d);
}
}
}
for (int i = 0; i < vd.size(); i++) {
cout << "The ICD of the last patient: " << i << " " << vd[i].icd
<< endl;
}
cout << "Operation done successfully" << endl;
C0.disconnect();
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
return 0;
}
/*
g++ $(pkg-config --libs libpqxx) Select.cpp -lpqxx -lpq -o Select
*/
C++ 连接postgresql数据库,进行数据筛选
最新推荐文章于 2024-08-09 15:28:00 发布