Mercurial > hg > syntilista

comparison src/sqlmodels.cpp @ 213:131463be208b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Split the custom SQL models code into sqlmodels.cpp
author Matti Hamalainen <ccr@tnsp.org>
date Mon, 18 Dec 2017 11:28:00 +0200
parents
children 58af72da7f60
comparison
equal deleted inserted replaced
212:70317bb39f05 213:131463be208b
1 //
2 // Syntilista - debt list/management database program
3 // Programmed and designed by Matti Hämäläinen <ccr@tnsp.org>
4 // (C) Copyright 2017 Tecnic Software productions (TNSP)
5 //
6 // Distributed under 3-clause BSD style license, refer to
7 // included file "COPYING" for exact terms.
8 //
9 #include "main.h"
10
11
12 SLPersonSQLModel::SLPersonSQLModel(QObject *parent) : QSqlQueryModel(parent)
13 {
14 }
15
16
17 QVariant SLPersonSQLModel::data(const QModelIndex &index, int role) const
18 {
19 QVariant value = QSqlQueryModel::data(index, role);
20
21 if (value.isValid() && role == Qt::DisplayRole)
22 {
23 // Format some of the displayed values
24 switch (index.column())
25 {
26 case 3:
27 return slMoneyValueToStr(value.toDouble());
28
29 case 4:
30 return slDateTimeToStr(value.toDateTime());
31 }
32 }
33
34 if (index.column() == 3 && role == Qt::ForegroundRole)
35 {
36 // Use fancy coloring for debt
37 double val = QSqlQueryModel::data(index, Qt::DisplayRole).toDouble();
38 if (val < 0)
39 return QVariant::fromValue(QColor(Qt::red));
40 else
41 return QVariant::fromValue(QColor(Qt::green));
42 }
43
44 return value;
45 }
46
47
48 int SLPersonSQLModel::updatePerson(const SLPersonInfo &info)
49 {
50 QSqlQuery np;
51
52 np.prepare(QStringLiteral("UPDATE people SET first_name=?,last_name=?,extra_info=?,updated=? WHERE id=?"));
53 np.addBindValue(info.firstName);
54 np.addBindValue(info.lastName);
55 np.addBindValue(info.extraInfo);
56 np.addBindValue(QDateTime::currentDateTimeUtc());
57 np.addBindValue(info.id);
58 np.exec();
59
60 if (!slCheckAndReportSQLError("SLPersonSQLModel::updatePerson()", np.lastError()))
61 return -1;
62
63 QSqlDatabase::database().commit();
64 updateModel();
65 return 0;
66 }
67
68
69 qint64 SLPersonSQLModel::addPerson(const SLPersonInfo &info)
70 {
71 QSqlQuery np;
72 np.prepare(QStringLiteral("INSERT INTO people (first_name,last_name,extra_info,added,updated) VALUES (?,?,?,?,?)"));
73 np.addBindValue(info.firstName);
74 np.addBindValue(info.lastName);
75 np.addBindValue(info.extraInfo);
76 np.addBindValue(QDateTime::currentDateTimeUtc());
77 np.addBindValue(QDateTime::currentDateTimeUtc());
78 np.exec();
79
80 if (!slCheckAndReportSQLError("SLPersonSQLModel::addPerson()", np.lastError()))
81 return -1;
82
83 QSqlDatabase::database().commit();
84
85 QVariant idp = np.lastInsertId();
86
87 updateModel();
88 return idp.isValid() ? idp.toInt() : -2;
89 }
90
91
92 int SLPersonSQLModel::deletePerson(qint64 id)
93 {
94 QSqlDatabase::database().transaction();
95 QSqlQuery del;
96
97 del.prepare(QStringLiteral("DELETE FROM people WHERE id=?"));
98 del.addBindValue(id);
99 del.exec();
100
101 if (!slCheckAndReportSQLError("delete user", del.lastError()))
102 {
103 QSqlDatabase::database().rollback();
104 return -1;
105 }
106
107 del.prepare(QStringLiteral("DELETE FROM transactions WHERE person=?"));
108 del.addBindValue(id);
109 del.exec();
110
111 if (!slCheckAndReportSQLError("delete user transactions", del.lastError()))
112 {
113 QSqlDatabase::database().rollback();
114 return -2;
115 }
116
117 QSqlDatabase::database().commit();
118 updateModel();
119 return 0;
120 }
121
122
123 void SLPersonSQLModel::updateModel()
124 {
125 query().exec();
126 emit dataChanged(index(0, 0), index(rowCount(), columnCount()));
127 }
128
129
130 SLTransactionSQLModel::SLTransactionSQLModel(QObject *parent) : QSqlQueryModel(parent)
131 {
132 }
133
134
135 QVariant SLTransactionSQLModel::data(const QModelIndex &index, int role) const
136 {
137 QVariant value = QSqlQueryModel::data(index, role);
138
139 if (value.isValid() && role == Qt::DisplayRole)
140 {
141 // Format some of the displayed values
142 switch (index.column())
143 {
144 case 1:
145 return slMoneyValueToStrSign(value.toDouble());
146
147 case 2:
148 return slDateTimeToStr(value.toDateTime());
149 }
150 }
151
152 if (index.column() == 1 && role == Qt::ForegroundRole)
153 {
154 // Use fancy coloring for debt
155 double val = QSqlQueryModel::data(index, Qt::DisplayRole).toDouble();
156 if (val < 0)
157 return QVariant::fromValue(QColor(Qt::red));
158 else
159 return QVariant::fromValue(QColor(Qt::green));
160 }
161
162 return value;
163 }
164
165
166 void SLTransactionSQLModel::updateModel()
167 {
168 query().exec();
169 emit dataChanged(QModelIndex(), QModelIndex());
170 }