File indexing completed on 2024-04-28 16:30:10
0001 /*************************************************************************** 0002 * SPDX-FileCopyrightText: 2022 S. MANKOWSKI stephane@mankowski.fr 0003 * SPDX-FileCopyrightText: 2022 G. DE BURE support@mankowski.fr 0004 * SPDX-License-Identifier: GPL-3.0-or-later 0005 ***************************************************************************/ 0006 /** @file 0007 * This file implements classes SKGDocumentBank. 0008 * 0009 * @author Stephane MANKOWSKI / Guillaume DE BURE 0010 */ 0011 #include "skgdocumentbank.h" 0012 #ifdef SKG_DBUS 0013 #include <qdbusconnection.h> 0014 #endif 0015 #include <qicon.h> 0016 #include <qsqldatabase.h> 0017 0018 #include <cmath> 0019 0020 #include "skgaccountobject.h" 0021 #include "skgbankobject.h" 0022 #include "skgerror.h" 0023 #include "skgreportbank.h" 0024 #include "skgservices.h" 0025 #include "skgtraces.h" 0026 #include "skgtransactionmng.h" 0027 #include "skgunitobject.h" 0028 #include "skgunitvalueobject.h" 0029 0030 SKGDocumentBank::SKGDocumentBank() : SKGDocument() 0031 { 0032 SKGTRACEINFUNC(10) 0033 connect(this, &SKGDocumentBank::tableModified, this, &SKGDocumentBank::refreshCache); 0034 #ifdef SKG_DBUS 0035 QDBusConnection dbus = QDBusConnection::sessionBus(); 0036 dbus.registerObject(QStringLiteral("/skrooge/skgdocumentbank"), this, QDBusConnection::ExportAllContents); 0037 #endif 0038 0039 // Initialisation of not undoable tables 0040 SKGListNotUndoable.push_back(QStringLiteral("T.operationbalance")); 0041 SKGListNotUndoable.push_back(QStringLiteral("T.budgetsuboperation")); 0042 } 0043 0044 SKGDocumentBank::~SKGDocumentBank() 0045 { 0046 SKGTRACEINFUNC(10) 0047 } 0048 0049 SKGError SKGDocumentBank::computeBudgetSuboperationLinks() const 0050 { 0051 SKGError err; 0052 SKGTRACEINFUNCRC(5, err) 0053 // Remove computed values 0054 err = this->executeSqliteOrder(QStringLiteral("DELETE FROM budgetsuboperation")); 0055 0056 // Compute values 0057 IFOKDO(err, executeSqliteOrder( 0058 "INSERT INTO budgetsuboperation (id, id_suboperation, i_priority) " 0059 0060 // Garbage collector annualy 0061 "SELECT b.id, s.id, 6.0 FROM budget b, operation o, suboperation s WHERE +s.rd_operation_id=o.id AND b.rc_category_id=0 AND b.i_month=0 AND b.i_year=STRFTIME('%Y', IFNULL(s.d_date, o.d_date))" 0062 0063 // Garbage collectory monthly 0064 " UNION SELECT b.id, s.id, 5.0 FROM budget b, operation o, suboperation s WHERE +s.rd_operation_id=o.id AND b.rc_category_id=0 AND b.i_month<>0 AND b.i_year=STRFTIME('%Y', IFNULL(s.d_date, o.d_date)) AND b.i_month=STRFTIME('%m', IFNULL(s.d_date, o.d_date))" 0065 0066 // Garbage categories annualy 0067 " UNION SELECT b.id, s.id, 4.0 - (LENGTH(c2.t_fullname)-LENGTH(REPLACE(c2.t_fullname, '" % OBJECTSEPARATOR % "', '')))/(100.0*LENGTH('" % OBJECTSEPARATOR % "')) FROM budget b, operation o, v_suboperation_display s, category c2 WHERE +s.rd_operation_id=o.id AND b.rc_category_id<>0 AND b.i_month=0 AND b.i_year=STRFTIME('%Y', IFNULL(s.d_date, o.d_date)) AND b.t_including_subcategories='Y' AND s.t_CATEGORY LIKE c2.t_fullname||'" % OBJECTSEPARATOR % "%' AND c2.id=b.rc_category_id" 0068 0069 // Garbage categories monthly 0070 " UNION SELECT b.id, s.id, 3.0 - (LENGTH(c2.t_fullname)-LENGTH(REPLACE(c2.t_fullname, '" % OBJECTSEPARATOR % "', '')))/(100.0*LENGTH('" % OBJECTSEPARATOR % "')) FROM budget b, operation o, v_suboperation_display s, category c2 WHERE +s.rd_operation_id=o.id AND b.rc_category_id<>0 AND b.i_month<>0 AND b.i_year=STRFTIME('%Y', IFNULL(s.d_date, o.d_date)) AND b.i_month=STRFTIME('%m', IFNULL(s.d_date, o.d_date)) AND b.t_including_subcategories='Y' AND s.t_CATEGORY LIKE c2.t_fullname||'" % OBJECTSEPARATOR % "%' AND c2.id=b.rc_category_id" 0071 0072 // Strict category annualy 0073 " UNION SELECT b.id, s.id, 2.0 FROM budget b, operation o, v_suboperation_display s WHERE +s.rd_operation_id=o.id AND b.rc_category_id<>0 AND b.i_month=0 AND b.i_year=STRFTIME('%Y', IFNULL(s.d_date, o.d_date)) AND b.rc_category_id=s.r_category_id" 0074 0075 // Strict category monthly 0076 " UNION SELECT b.id, s.id, 1.0 FROM budget b, operation o, v_suboperation_display s WHERE +s.rd_operation_id=o.id AND b.rc_category_id<>0 AND b.i_month<>0 AND b.i_year=STRFTIME('%Y', IFNULL(s.d_date, o.d_date)) AND b.i_month=STRFTIME('%m', IFNULL(s.d_date, o.d_date)) AND +b.rc_category_id=s.r_category_id")); 0077 // Remove useless values 0078 IFOKDO(err, executeSqliteOrder(QStringLiteral("DELETE FROM budgetsuboperation WHERE EXISTS (SELECT 1 FROM budgetsuboperation b2 WHERE b2.id_suboperation=budgetsuboperation.id_suboperation AND b2.i_priority<budgetsuboperation.i_priority)"))) 0079 0080 return err; 0081 } 0082 0083 void SKGDocumentBank::setComputeBalances(bool iEnabled) 0084 { 0085 if (iEnabled != m_computeBalances) { 0086 m_computeBalances = iEnabled; 0087 computeBalances(); 0088 } 0089 } 0090 0091 SKGError SKGDocumentBank::computeBalances() const 0092 { 0093 SKGError err; 0094 SKGTRACEINFUNCRC(5, err) 0095 // Remove computed values 0096 err = this->executeSqliteOrder(QStringLiteral("DELETE FROM operationbalance")); 0097 0098 if (m_computeBalances) { 0099 SKGStringListList result; 0100 IFOKDO(err, executeSelectSqliteOrder(QStringLiteral("SELECT id, rd_account_id, f_CURRENTAMOUNT, f_QUANTITY FROM v_operation WHERE t_template='N' ORDER BY rd_account_id, d_date, id"), result)) 0101 int nb = result.count(); 0102 double sum = 0; 0103 double sum2 = 0; 0104 int currentAccount = 0; 0105 QStringList items; 0106 for (int i = 1; !err && i < nb; ++i) { // Ignore header 0107 const QStringList& line = result.at(i); 0108 const QString& idOp = line.at(0); 0109 int account = SKGServices::stringToInt(line.at(1)); 0110 double val = SKGServices::stringToDouble(line.at(2)); 0111 double val2 = SKGServices::stringToDouble(line.at(3)); 0112 0113 if (account != currentAccount) { 0114 sum = 0; 0115 sum2 = 0; 0116 currentAccount = account; 0117 } 0118 0119 sum += val; 0120 sum2 += val2; 0121 0122 items.push_back(idOp % "," % SKGServices::doubleToString(sum) % "," % SKGServices::doubleToString(sum2)); 0123 0124 if (items.count() == 490) { 0125 err = this->executeSqliteOrder("INSERT INTO operationbalance (r_operation_id,f_balance,f_balance_entered) " 0126 "SELECT " % items.join(QStringLiteral(" UNION SELECT "))); 0127 items.clear(); 0128 } 0129 } 0130 if (!err && !items.isEmpty()) { 0131 err = this->executeSqliteOrder("INSERT INTO operationbalance (r_operation_id,f_balance,f_balance_entered) " 0132 "SELECT " % items.join(QStringLiteral(" UNION SELECT "))); 0133 } 0134 } 0135 return err; 0136 } 0137 0138 SKGError SKGDocumentBank::endTransaction(bool succeedded) 0139 { 0140 SKGError err; 0141 if (succeedded && getDepthTransaction() == 1) { 0142 if (getCachedValue(QStringLiteral("SKG_REFRESH_VIEW")) == QStringLiteral("Y")) { 0143 QStringList listModifiedTables; 0144 err = this->getDistinctValues(QStringLiteral("doctransactionitem"), 0145 QStringLiteral("t_object_table"), 0146 QStringLiteral("rd_doctransaction_id=0"), 0147 listModifiedTables); 0148 if (!err && 0149 (listModifiedTables.contains(QStringLiteral("operation")) || listModifiedTables.contains(QStringLiteral("suboperation")) || listModifiedTables.contains(QStringLiteral("unit")) || listModifiedTables.contains(QStringLiteral("unitvalue"))) 0150 ) { 0151 // Computation of cache 0152 err = computeBalances(); 0153 } 0154 0155 if (!err && 0156 (listModifiedTables.contains(QStringLiteral("operation")) || listModifiedTables.contains(QStringLiteral("suboperation")) || listModifiedTables.contains(QStringLiteral("unit")) || listModifiedTables.contains(QStringLiteral("unitvalue")) || listModifiedTables.contains(QStringLiteral("category")) || listModifiedTables.contains(QStringLiteral("budget"))) 0157 ) { 0158 // Computation of cache 0159 err = computeBudgetSuboperationLinks(); 0160 } 0161 } 0162 // Clean main variations cache 0163 m_5mainVariations_cache.clear(); 0164 m_5mainVariationsCat_cache.clear(); 0165 m_5mainVariations_inputs = QLatin1String(""); 0166 } 0167 0168 SKGError err2 = SKGDocument::endTransaction(succeedded); 0169 if (!err && err2) { 0170 err = err2; 0171 } 0172 return err; 0173 } 0174 0175 QString SKGDocumentBank::getViewsIndexesAndTriggersVersion() const 0176 { 0177 return "2023.12.12_" % getParameter(QStringLiteral("SKG_LANGUAGE")); 0178 } 0179 0180 SKGError SKGDocumentBank::refreshViewsIndexesAndTriggers(bool iForce) const 0181 { 0182 SKGError err; 0183 SKGTRACEINFUNCRC(5, err) 0184 0185 QString version = getParameter(QStringLiteral("SKG_DB_BANK_VIEWS_VERSION")); 0186 if (!iForce && version == getViewsIndexesAndTriggersVersion()) { 0187 return err; 0188 } 0189 0190 err = setParameter(QStringLiteral("SKG_DB_BANK_VIEWS_VERSION"), getViewsIndexesAndTriggersVersion()); 0191 0192 QString dateFormatShort = QLocale().dateFormat(QLocale::ShortFormat); 0193 int firstDayOfWeek = QLocale().firstDayOfWeek(); 0194 0195 // WARNING: Do not forget to update getViewVersion when this method is modified 0196 /** 0197 * This constant is used to initialized the data model (trigger creation) 0198 * IF YOU MODIFY THIS METHOD, DO NOT FORGET TO MODIFY getViewsIndexesAndTriggersVersion TOO 0199 */ 0200 QStringList BankInitialDataModelTrigger; 0201 BankInitialDataModelTrigger << DELETECASCADEPARAMETER("bank") 0202 << DELETECASCADEPARAMETER("account") 0203 << DELETECASCADEPARAMETER("unit") 0204 << DELETECASCADEPARAMETER("unitvalue") 0205 << DELETECASCADEPARAMETER("category") 0206 << DELETECASCADEPARAMETER("operation") 0207 << DELETECASCADEPARAMETER("interest") 0208 << DELETECASCADEPARAMETER("suboperation") 0209 << DELETECASCADEPARAMETER("refund") 0210 << DELETECASCADEPARAMETER("payee") 0211 << DELETECASCADEPARAMETER("recurrentoperation") 0212 << DELETECASCADEPARAMETER("rule") 0213 << DELETECASCADEPARAMETER("budget") 0214 << DELETECASCADEPARAMETER("budgetrule") 0215 0216 0217 // Compute fullname 0218 << QStringLiteral("DROP TRIGGER IF EXISTS cpt_category_fullname3") 0219 /* << "CREATE TRIGGER cpt_category_fullname1 " // This trigger must be the first 0220 "AFTER UPDATE OF t_fullname ON category BEGIN " 0221 "UPDATE category SET t_name=t_name WHERE rd_category_id=new.id;" 0222 "END"*/ 0223 0224 << QStringLiteral("DROP TRIGGER IF EXISTS cpt_category_fullname1") 0225 << "CREATE TRIGGER cpt_category_fullname1 " 0226 "AFTER INSERT ON category BEGIN " 0227 "UPDATE category SET t_fullname=" 0228 "CASE WHEN rd_category_id IS NULL OR rd_category_id='' OR rd_category_id=0 THEN new.t_name ELSE (SELECT c.t_fullname FROM category c WHERE c.id=new.rd_category_id)||'" % OBJECTSEPARATOR % "'||new.t_name END " 0229 "WHERE id=new.id;" 0230 "END" 0231 0232 << QStringLiteral("DROP TRIGGER IF EXISTS cpt_category_fullname2") 0233 << "CREATE TRIGGER cpt_category_fullname2 " 0234 "AFTER UPDATE OF t_name, rd_category_id ON category BEGIN " 0235 "UPDATE category SET t_fullname=" 0236 "CASE WHEN rd_category_id IS NULL OR rd_category_id='' OR rd_category_id=0 THEN new.t_name ELSE (SELECT c.t_fullname FROM category c WHERE c.id=new.rd_category_id)||'" % OBJECTSEPARATOR % "'||new.t_name END " 0237 "WHERE id=new.id;" 0238 "UPDATE category SET t_name=t_name WHERE rd_category_id=new.id;" 0239 "END" 0240 0241 // -- Reparent suboperation on parent category when a category is removed 0242 << QStringLiteral("DROP TRIGGER IF EXISTS fkdc_category_delete") 0243 << "CREATE TRIGGER fkdc_category_delete " 0244 "BEFORE DELETE ON category " 0245 "FOR EACH ROW BEGIN " 0246 " UPDATE suboperation SET r_category_id=OLD.rd_category_id WHERE r_category_id IN (SELECT c.id FROM category c WHERE c.id=OLD.id OR c.t_fullname LIKE OLD.t_fullname||'" % OBJECTSEPARATOR % "%'); " 0247 " UPDATE payee SET r_category_id=0 WHERE r_category_id IN (SELECT c.id FROM category c WHERE c.id=OLD.id OR c.t_fullname LIKE OLD.t_fullname||'" % OBJECTSEPARATOR % "%'); " 0248 "END " 0249 0250 << QStringLiteral("DROP TRIGGER IF EXISTS fkdc_category_parent_id_category_id") 0251 0252 // Trigger for update on view 0253 << QStringLiteral("DROP TRIGGER IF EXISTS trgu_v_operation_prop_i_tmp") 0254 << QStringLiteral("CREATE TRIGGER trgu_v_operation_prop_i_tmp " 0255 "INSTEAD OF UPDATE OF i_tmp ON v_operation_prop " 0256 "FOR EACH ROW BEGIN " 0257 " UPDATE suboperation SET i_tmp=NEW.i_tmp WHERE id=OLD.i_SUBOPID; " 0258 " UPDATE operation SET i_tmp=NEW.i_tmp WHERE id=OLD.i_OPID; " 0259 " UPDATE parameters SET i_tmp=NEW.i_tmp WHERE id=OLD.i_PROPPID; " 0260 "END ") 0261 0262 << QStringLiteral("DROP TRIGGER IF EXISTS trgu_v_operation_prop_t_realcomment") 0263 << QStringLiteral("CREATE TRIGGER trgu_v_operation_prop_t_realcomment " 0264 "INSTEAD OF UPDATE OF t_REALCOMMENT ON v_operation_prop " 0265 "FOR EACH ROW BEGIN " 0266 " UPDATE suboperation SET t_comment=NEW.t_REALCOMMENT WHERE id=OLD.i_SUBOPID; " 0267 "END ") 0268 0269 << QStringLiteral("DROP TRIGGER IF EXISTS trgu_v_operation_prop_t_unit") 0270 << QStringLiteral("CREATE TRIGGER trgu_v_operation_prop_t_unit " 0271 "INSTEAD OF UPDATE OF t_UNIT ON v_operation_prop " 0272 "FOR EACH ROW BEGIN " 0273 " INSERT OR IGNORE INTO unit (t_name, t_symbol) VALUES (NEW.t_UNIT, NEW.t_UNIT); " 0274 " UPDATE operation set rc_unit_id=(SELECT id FROM unit WHERE t_name=NEW.t_UNIT) WHERE id=OLD.i_OPID; " 0275 "END ") 0276 0277 << QStringLiteral("DROP TRIGGER IF EXISTS trgu_v_operation_prop_t_account") 0278 << QStringLiteral("CREATE TRIGGER trgu_v_operation_prop_t_account " 0279 "INSTEAD OF UPDATE OF t_ACCOUNT ON v_operation_prop " 0280 "FOR EACH ROW BEGIN " 0281 " INSERT OR IGNORE INTO account (t_name, rd_bank_id) VALUES (NEW.t_ACCOUNT, (SELECT MIN(id) FROM bank)); " 0282 " UPDATE operation set rd_account_id=(SELECT id FROM account WHERE t_name=NEW.t_ACCOUNT) WHERE id=OLD.i_OPID; " 0283 "END ") 0284 0285 << QStringLiteral("DROP TRIGGER IF EXISTS trgu_v_operation_prop_t_payee") 0286 << QStringLiteral("CREATE TRIGGER trgu_v_operation_prop_t_payee " 0287 "INSTEAD OF UPDATE OF t_PAYEE ON v_operation_prop " 0288 "FOR EACH ROW BEGIN " 0289 " INSERT OR IGNORE INTO payee (t_name) VALUES (NEW.t_PAYEE); " 0290 " UPDATE operation set r_payee_id=(SELECT id FROM payee WHERE t_name=NEW.t_PAYEE) WHERE id=OLD.i_OPID; " 0291 "END ") 0292 0293 << QStringLiteral("DROP TRIGGER IF EXISTS trgu_v_operation_prop_t_realrefund") 0294 << QStringLiteral("CREATE TRIGGER trgu_v_operation_prop_t_realrefund " 0295 "INSTEAD OF UPDATE OF t_REALREFUND ON v_operation_prop " 0296 "FOR EACH ROW BEGIN " 0297 " INSERT OR IGNORE INTO refund (t_name) VALUES (NEW.t_REALREFUND); " 0298 " UPDATE suboperation set r_refund_id=(SELECT id FROM refund WHERE t_name=NEW.t_REALREFUND) WHERE id=OLD.i_SUBOPID; " 0299 "END ") 0300 0301 << QStringLiteral("DROP TRIGGER IF EXISTS trgu_v_operation_prop_d_dateop") 0302 << QStringLiteral("CREATE TRIGGER trgu_v_operation_prop_d_dateop " 0303 "INSTEAD OF UPDATE OF d_DATEOP ON v_operation_prop " 0304 "FOR EACH ROW BEGIN " 0305 " UPDATE suboperation set d_date=date(d_date, '+'||(julianday(NEW.d_DATEOP)-julianday(old.d_DATEOP))||' days') WHERE id=OLD.i_SUBOPID; " 0306 " UPDATE operation set d_date=NEW.d_DATEOP WHERE id=OLD.i_OPID; " 0307 "END "); 0308 0309 // Build triggers for normal attribute 0310 SKGServices::SKGAttributesList attributes; 0311 getAttributesDescription(QStringLiteral("operation"), attributes); 0312 int nb = attributes.count(); 0313 for (int i = 0; i < nb; ++i) { 0314 QString att = attributes.at(i).name; 0315 if (att == att.toLower() && att != QStringLiteral("i_tmp")) { 0316 BankInitialDataModelTrigger << QStringLiteral("DROP TRIGGER IF EXISTS trgu_v_operation_prop_") % att 0317 << "CREATE TRIGGER trgu_v_operation_prop_" % att % " " 0318 "INSTEAD OF UPDATE OF " % att % " ON v_operation_prop " 0319 "FOR EACH ROW BEGIN " 0320 " UPDATE operation SET " % att % "=NEW." % att % " WHERE id=OLD.i_OPID; " 0321 "END "; 0322 } 0323 } 0324 /** 0325 * This constant is used to initialized the data model (index creation) 0326 */ 0327 QStringList BankInitialDataModelIndex; 0328 BankInitialDataModelIndex << QStringLiteral("CREATE UNIQUE INDEX uidx_unit_name ON unit(t_name)") 0329 << QStringLiteral("CREATE UNIQUE INDEX uidx_unit_symbol ON unit(t_symbol)") 0330 0331 << QStringLiteral("CREATE INDEX idx_unit_unit_id ON unitvalue(rd_unit_id)") 0332 << QStringLiteral("CREATE UNIQUE INDEX uidx_unitvalue ON unitvalue(d_date,rd_unit_id)") 0333 << QStringLiteral("CREATE UNIQUE INDEX uidx_unitvalue2 ON unitvalue(rd_unit_id, d_date)") 0334 0335 << QStringLiteral("CREATE UNIQUE INDEX uidx_bank_name ON bank(t_name)") 0336 0337 << QStringLiteral("CREATE UNIQUE INDEX uidx_account_name ON account(t_name)") 0338 << QStringLiteral("CREATE INDEX idx_account_bank_id ON account(rd_bank_id)") 0339 << QStringLiteral("CREATE INDEX idx_account_type ON account(t_type)") 0340 0341 << QStringLiteral("CREATE INDEX idx_category_category_id ON category(rd_category_id)") 0342 << QStringLiteral("CREATE INDEX idx_category_t_fullname ON category(t_fullname)") 0343 << QStringLiteral("CREATE INDEX idx_category_close ON category(t_close)") 0344 0345 << QStringLiteral("CREATE UNIQUE INDEX uidx_category_parent_id_name ON category(t_name,rd_category_id)") 0346 0347 << QStringLiteral("CREATE INDEX idx_operation_tmp1_found_transfert ON operation (rc_unit_id, d_date)") 0348 << QStringLiteral("CREATE INDEX idx_operation_grouped_operation_id ON operation (i_group_id)") 0349 // << "CREATE INDEX idx_operation_t_mode ON operation (t_mode)" 0350 // << "CREATE INDEX idx_operation_t_payee ON operation (t_payee)" 0351 << QStringLiteral("CREATE INDEX idx_operation_t_number ON operation (t_number)") 0352 << QStringLiteral("CREATE INDEX idx_operation_i_tmp ON operation (i_tmp)") 0353 << QStringLiteral("CREATE INDEX idx_operation_rd_account_id ON operation (rd_account_id)") 0354 << QStringLiteral("CREATE INDEX idx_operation_rd_account_id_t_imported ON operation (rd_account_id, t_imported)") 0355 << QStringLiteral("CREATE INDEX idx_operation_rd_account_id_t_number ON operation (rd_account_id, t_number)") 0356 << QStringLiteral("CREATE INDEX idx_operation_rc_unit_id ON operation (rc_unit_id)") 0357 << QStringLiteral("CREATE INDEX idx_operation_t_status ON operation (t_status)") 0358 << QStringLiteral("CREATE INDEX idx_operation_t_import_id ON operation (t_import_id)") 0359 << QStringLiteral("CREATE INDEX idx_operation_d_date ON operation (d_date)") 0360 << QStringLiteral("CREATE INDEX idx_operation_t_template ON operation (t_template)") 0361 0362 << QStringLiteral("CREATE INDEX idx_operationbalance_operation_id ON operationbalance (r_operation_id)") 0363 0364 << QStringLiteral("CREATE INDEX idx_suboperation_operation_id ON suboperation (rd_operation_id)") 0365 << QStringLiteral("CREATE INDEX idx_suboperation_i_tmp ON suboperation (i_tmp)") 0366 << QStringLiteral("CREATE INDEX idx_suboperation_category_id ON suboperation (r_category_id)") 0367 << QStringLiteral("CREATE INDEX idx_suboperation_refund_id_id ON suboperation (r_refund_id)") 0368 0369 << QStringLiteral("CREATE INDEX idx_recurrentoperation_rd_operation_id ON recurrentoperation (rd_operation_id)") 0370 0371 << QStringLiteral("CREATE UNIQUE INDEX uidx_refund_name ON refund(t_name)") 0372 << QStringLiteral("CREATE INDEX idx_refund_close ON refund(t_close)") 0373 0374 << QStringLiteral("CREATE UNIQUE INDEX uidx_payee_name ON payee(t_name)") 0375 << QStringLiteral("CREATE INDEX idx_payee_close ON payee(t_close)") 0376 0377 << QStringLiteral("CREATE INDEX idx_interest_account_id ON interest (rd_account_id)") 0378 << QStringLiteral("CREATE UNIQUE INDEX uidx_interest ON interest(d_date,rd_account_id)") 0379 0380 << QStringLiteral("CREATE INDEX idx_rule_action_type ON rule(t_action_type)") 0381 0382 << QStringLiteral("CREATE UNIQUE INDEX uidx_budget ON budget(i_year,i_month, rc_category_id)") 0383 << QStringLiteral("CREATE INDEX idx_budget_category_id ON budget(rc_category_id)") 0384 << QStringLiteral("CREATE INDEX idx_budgetsuboperation_id ON budgetsuboperation (id)") 0385 << QStringLiteral("CREATE INDEX idx_budgetsuboperation_id_suboperation ON budgetsuboperation (id_suboperation)"); 0386 0387 /** 0388 * This constant is used to initialized the data model (view creation) 0389 */ 0390 QStringList BankInitialDataModelView; 0391 BankInitialDataModelView 0392 // ================================================================== 0393 // These following views contains only attributes used by corresponding class (better for performances) 0394 // unit 0395 << QStringLiteral("CREATE VIEW v_unit_displayname AS " 0396 "SELECT *, t_name||' ('||t_symbol||')' AS t_displayname FROM unit") 0397 0398 << "CREATE VIEW v_unit_tmp1 AS " 0399 "SELECT *," 0400 "(SELECT COUNT(1) FROM unitvalue s WHERE s.rd_unit_id=unit.id) AS i_NBVALUES, " 0401 "(CASE WHEN unit.rd_unit_id=0 THEN '' ELSE (SELECT (CASE WHEN s.t_symbol!='' THEN s.t_symbol ELSE s.t_name END) FROM unit s WHERE s.id=unit.rd_unit_id) END) AS t_UNIT," 0402 "(CASE unit.t_type " 0403 "WHEN '1' THEN '" % SKGServices::stringToSqlString(i18nc("Noun", "Primary currency")) % "' " 0404 "WHEN '2' THEN '" % SKGServices::stringToSqlString(i18nc("Noun", "Secondary currency")) % "' " 0405 "WHEN 'C' THEN '" % SKGServices::stringToSqlString(i18nc("Noun, a country's currency", "Currency")) % "' " 0406 "WHEN 'S' THEN '" % SKGServices::stringToSqlString(i18nc("Noun, a financial share, as in a stock market", "Share")) % "' " 0407 "WHEN 'I' THEN '" % SKGServices::stringToSqlString(i18nc("Noun, a financial index like the Dow Jones, NASDAQ, CAC40…", "Index")) % "' " 0408 "ELSE '" % SKGServices::stringToSqlString(i18nc("Noun, a physical object like a house or a car", "Object")) % "' END) AS t_TYPENLS, " 0409 "(SELECT MIN(s.d_date) FROM unitvalue s WHERE s.rd_unit_id=unit.id) AS d_MINDATE, " 0410 "(SELECT MAX(s.d_date) FROM unitvalue s WHERE s.rd_unit_id=unit.id) AS d_MAXDATE " 0411 "FROM unit" 0412 0413 << QStringLiteral("CREATE VIEW v_unit_tmp2 AS " 0414 "SELECT *," 0415 "CASE WHEN v_unit_tmp1.t_type='1' THEN 1 ELSE IFNULL((SELECT s.f_quantity FROM unitvalue s INDEXED BY uidx_unitvalue2 WHERE s.rd_unit_id=v_unit_tmp1.id AND s.d_date=v_unit_tmp1.d_MAXDATE),1) END AS f_LASTVALUE " 0416 "FROM v_unit_tmp1") 0417 0418 << QStringLiteral("CREATE VIEW v_unit AS " 0419 "SELECT *," 0420 "CASE WHEN v_unit_tmp2.t_type='1' THEN 1 ELSE v_unit_tmp2.f_LASTVALUE*IFNULL((SELECT s2.f_LASTVALUE FROM v_unit_tmp2 s2 WHERE s2.id=v_unit_tmp2.rd_unit_id) , 1) END AS f_CURRENTAMOUNT " 0421 "FROM v_unit_tmp2") 0422 0423 // unitvalue 0424 << "CREATE VIEW v_unitvalue_displayname AS " 0425 "SELECT *, (SELECT t_displayname FROM v_unit_displayname WHERE unitvalue.rd_unit_id=v_unit_displayname.id)||' '||IFNULL(TOFORMATTEDDATE(d_date,'" % SKGServices::stringToSqlString(dateFormatShort) % "'),STRFTIME('%Y-%m-%d',d_date)) AS t_displayname FROM unitvalue" 0426 0427 << QStringLiteral("CREATE VIEW v_unitvalue AS " 0428 "SELECT * " 0429 "FROM unitvalue") 0430 0431 // suboperation 0432 << QStringLiteral("CREATE VIEW v_suboperation AS " 0433 "SELECT * " 0434 "FROM suboperation") 0435 0436 // operation 0437 << QStringLiteral("CREATE VIEW v_operation_numbers AS " 0438 "SELECT DISTINCT t_number, rd_account_id FROM operation") 0439 0440 << QStringLiteral("CREATE VIEW v_operation_next_numbers AS " 0441 "SELECT NEXT(T1.t_number) AS t_number, T1.rd_account_id FROM v_operation_numbers AS T1 LEFT OUTER JOIN v_operation_numbers T2 " 0442 "ON T2.rd_account_id=T1.rd_account_id AND T2.t_number=NEXT(T1.t_number) " 0443 "WHERE T1.t_number!='' AND (T2.t_number IS NULL) ORDER BY T1.t_number") 0444 0445 << QStringLiteral("CREATE VIEW v_operation_tmp1 AS " 0446 "SELECT operation.*," 0447 "(CASE WHEN v_unit.t_symbol!='' THEN v_unit.t_symbol ELSE v_unit.t_name END) AS t_UNIT," 0448 "IFNULL((SELECT s.t_name FROM payee s WHERE s.id=operation.r_payee_id), '') AS t_PAYEE," 0449 "v_unit.i_nbdecimal AS i_NBDEC," 0450 "v_unit.t_type AS t_TYPEUNIT," 0451 "v_unit.f_CURRENTAMOUNT AS f_CURRENTAMOUNTUNIT," 0452 "(SELECT TOTAL(s.f_value) FROM suboperation s WHERE s.rd_operation_id=operation.ID) AS f_QUANTITY," 0453 "(SELECT COUNT(1) FROM suboperation s WHERE s.rd_operation_id=operation.ID) AS i_NBSUBOPERATIONS, " 0454 "account.t_name AS t_ACCOUNT, " 0455 "account.t_type AS t_TYPEACCOUNT, " 0456 "(CASE WHEN bank.t_name='' THEN '") % i18nc("Noun", "Wallets") % QStringLiteral("' ELSE bank.t_name END) AS t_BANK " 0457 "FROM operation, account, bank, v_unit WHERE +operation.rd_account_id=account.id AND +account.rd_bank_id=bank.id AND +operation.rc_unit_id=v_unit.id") 0458 0459 << QStringLiteral("CREATE VIEW v_operation AS " 0460 "SELECT *," 0461 "(SELECT s.id FROM suboperation s WHERE s.rd_operation_id=v_operation_tmp1.id AND ABS(s.f_value)=(SELECT MAX(ABS(s2.f_value)) FROM suboperation s2 WHERE s2.rd_operation_id=v_operation_tmp1.id)) AS i_MOSTIMPSUBOP," 0462 "v_operation_tmp1.f_CURRENTAMOUNTUNIT*v_operation_tmp1.f_QUANTITY AS f_CURRENTAMOUNT, " 0463 "(CASE WHEN v_operation_tmp1.i_group_id<>0 AND v_operation_tmp1.t_TYPEACCOUNT<>'L' AND v_operation_tmp1.t_TYPEUNIT IN ('1', '2', 'C') AND " 0464 "(SELECT COUNT(1) FROM operation WHERE i_group_id=v_operation_tmp1.i_group_id)=2 AND " 0465 "EXISTS (SELECT 1 FROM v_operation_tmp1 op2 WHERE op2.i_group_id=v_operation_tmp1.i_group_id " 0466 "AND op2.t_TYPEACCOUNT<>'L' AND op2.t_TYPEUNIT IN ('1', '2', 'C') AND op2.f_QUANTITY*v_operation_tmp1.f_QUANTITY<=0) THEN 'Y' ELSE 'N' END) AS t_TRANSFER " 0467 // "ROUND((SELECT s.f_CURRENTAMOUNT FROM v_unit s WHERE s.id=v_operation_tmp1.rc_unit_id)*v_operation_tmp1.f_QUANTITY, 2) AS f_CURRENTAMOUNT " 0468 "FROM v_operation_tmp1") 0469 0470 << "CREATE VIEW v_operation_displayname AS " 0471 "SELECT *, IFNULL(TOFORMATTEDDATE(d_date,'" % SKGServices::stringToSqlString(dateFormatShort) % "'),STRFTIME('%Y-%m-%d',d_date))||' '||IFNULL(t_PAYEE,'')||' '||TOCURRENCY(v_operation.f_quantity, (SELECT (CASE WHEN s.t_symbol!='' THEN s.t_symbol ELSE s.t_name END) FROM unit s WHERE s.id=v_operation.rc_unit_id)) AS t_displayname FROM v_operation" 0472 0473 << "CREATE VIEW v_operation_delete AS " 0474 "SELECT *, (CASE WHEN t_status='Y' THEN '" % 0475 SKGServices::stringToSqlString(i18nc("Error message", "You are not authorized to delete this transaction because in \"checked\" status")) % 0476 "' END) t_delete_message FROM operation" 0477 0478 // account 0479 << QStringLiteral("CREATE VIEW v_account AS " 0480 "SELECT " 0481 "account.*," 0482 "(CASE t_type " 0483 "WHEN 'C' THEN '") % SKGServices::stringToSqlString(i18nc("Adjective, a current account", "Current")) % "' " 0484 "WHEN 'D' THEN '" % SKGServices::stringToSqlString(i18nc("Noun", "Credit card")) % "' " 0485 "WHEN 'A' THEN '" % SKGServices::stringToSqlString(i18nc("Noun, the type of an account", "Assets")) % "' " 0486 "WHEN 'I' THEN '" % SKGServices::stringToSqlString(i18nc("Noun, a type of account WHERE you invest money", "Investment")) % "' " 0487 "WHEN 'W' THEN '" % SKGServices::stringToSqlString(i18nc("Noun, a type of account", "Wallet")) % "' " 0488 "WHEN 'L' THEN '" % SKGServices::stringToSqlString(i18nc("Noun, a type of account", "Loan")) % "' " 0489 "WHEN 'S' THEN '" % SKGServices::stringToSqlString(i18nc("Noun, a type of account", "Saving")) % "' " 0490 "WHEN 'P' THEN '" % SKGServices::stringToSqlString(i18nc("Noun, a type of account", "Pension")) % "' " 0491 "WHEN 'O' THEN '" % SKGServices::stringToSqlString(i18nc("Noun, as in other type of item", "Other")) % "' END) AS t_TYPENLS," 0492 "(CASE WHEN bank.t_name='' THEN '" % i18nc("Noun", "Wallets") % QStringLiteral("' ELSE bank.t_name END) AS t_BANK," 0493 "bank.t_bank_number AS t_BANK_NUMBER," 0494 "bank.t_icon AS t_ICON," 0495 "IFNULL((SELECT f_CURRENTAMOUNTUNIT FROM v_operation_tmp1 WHERE d_date='0000-00-00' AND rd_account_id=account.id), 1) AS f_CURRENTAMOUNTUNIT," 0496 "(SELECT MAX(s.d_date) FROM interest s WHERE s.rd_account_id=account.id) AS d_MAXDATE " 0497 "FROM account, bank WHERE +account.rd_bank_id=bank.id") 0498 0499 << QStringLiteral("CREATE VIEW v_account_amount AS " 0500 "SELECT " 0501 "v_account.*," 0502 "(SELECT TOTAL(s.f_CURRENTAMOUNT) FROM v_operation s WHERE s.rd_account_id=v_account.id AND s.t_template='N') AS f_CURRENTAMOUNT " 0503 "FROM v_account") 0504 0505 << "CREATE VIEW v_account_delete AS " 0506 "SELECT *, (CASE WHEN EXISTS(SELECT 1 FROM operation WHERE rd_account_id=account.id AND d_date<>'0000-00-00' AND t_template='N' AND t_status='Y') THEN '" % 0507 SKGServices::stringToSqlString(i18nc("Error message", "You are not authorized to delete this account because it contains some checked transactions")) % 0508 "' END) t_delete_message FROM account" 0509 0510 // bank 0511 << QStringLiteral("CREATE VIEW v_bank_displayname AS " 0512 "SELECT *, t_name AS t_displayname FROM bank") 0513 0514 << QStringLiteral("CREATE VIEW v_account_displayname AS " 0515 "SELECT *, (SELECT t_displayname FROM v_bank_displayname WHERE account.rd_bank_id=v_bank_displayname.id)||'-'||t_name AS t_displayname FROM account") 0516 0517 << QStringLiteral("CREATE VIEW v_bank AS " 0518 "SELECT * FROM bank") 0519 0520 << QStringLiteral("CREATE VIEW v_bank_amount AS " 0521 "SELECT *," 0522 "(SELECT TOTAL(s.f_CURRENTAMOUNT) FROM v_account_amount s WHERE s.rd_bank_id=v_bank.id) AS f_CURRENTAMOUNT " 0523 "FROM v_bank") 0524 0525 // category 0526 << QStringLiteral("CREATE VIEW v_category_displayname AS " 0527 "SELECT *, t_fullname AS t_displayname FROM category") 0528 0529 << QStringLiteral("CREATE VIEW v_category AS SELECT * " 0530 "FROM category") 0531 0532 // recurrentoperation 0533 << "CREATE VIEW v_recurrentoperation AS " 0534 "SELECT *," 0535 "date(d_date, '-'||((CASE t_period_unit WHEN 'W' THEN 7 ELSE 1 END)*i_period_increment)||' '||(CASE t_period_unit WHEN 'M' THEN 'month' WHEN 'Y' THEN 'year' ELSE 'day' END)) as d_PREVIOUS," 0536 "i_period_increment||' '||(CASE t_period_unit " 0537 "WHEN 'Y' THEN '" % SKGServices::stringToSqlString(i18nc("Noun", "year(s)")) % "' " 0538 "WHEN 'M' THEN '" % SKGServices::stringToSqlString(i18nc("Noun", "month(s)")) % "' " 0539 "WHEN 'W' THEN '" % SKGServices::stringToSqlString(i18nc("Noun", "week(s)")) % "' " 0540 "ELSE '" % SKGServices::stringToSqlString(i18nc("Noun", "day(s)")) % "' END) AS t_PERIODNLS " 0541 "FROM recurrentoperation" 0542 0543 << "CREATE VIEW v_recurrentoperation_displayname AS " 0544 "SELECT *, IFNULL(TOFORMATTEDDATE(d_date,'" % SKGServices::stringToSqlString(dateFormatShort) % "'),STRFTIME('%Y-%m-%d',d_date))||' '||(SELECT SUBSTR(t_displayname, INSTR(t_displayname, ' ')+1) FROM v_operation_displayname WHERE v_operation_displayname.id=v_recurrentoperation.rd_operation_id) AS t_displayname FROM v_recurrentoperation" 0545 0546 // ================================================================== 0547 // These following views contains all attributes needed for display 0548 // unitvalue 0549 << QStringLiteral("CREATE VIEW v_unitvalue_display AS " 0550 "SELECT *," 0551 "unitvalue.f_QUANTITY*(SELECT TOTAL(v_operation.f_QUANTITY) FROM v_operation WHERE v_operation.rc_unit_id=unitvalue.rd_unit_id AND v_operation.d_date<=unitvalue.d_date AND v_operation.t_template='N') AS f_AMOUNTOWNED," 0552 "IFNULL((SELECT (CASE WHEN s.t_symbol!='' THEN s.t_symbol ELSE s.t_name END) FROM unit s WHERE s.id=(SELECT s2.rd_unit_id FROM unit s2 WHERE s2.id=unitvalue.rd_unit_id)),'') AS t_UNIT," 0553 "STRFTIME('%Y-%m',unitvalue.d_date) AS d_DATEMONTH," 0554 "STRFTIME('%Y',unitvalue.d_date) AS d_DATEYEAR " 0555 "FROM unitvalue") 0556 0557 // suboperation 0558 << QStringLiteral("CREATE VIEW v_suboperation_display AS " 0559 "SELECT *," 0560 "IFNULL((SELECT s.t_fullname FROM category s WHERE s.id=v_suboperation.r_category_id),'') AS t_CATEGORY, " 0561 "IFNULL((SELECT s.t_name FROM refund s WHERE s.id=v_suboperation.r_refund_id),'') AS t_REFUND, " 0562 "IFNULL((SELECT s.t_name||\" (\"||TOCURRENCY((SELECT TOTAL(s2.f_value) FROM v_suboperation s2 WHERE s2.d_date<=v_suboperation.d_date AND s2.r_refund_id=v_suboperation.r_refund_id), (SELECT t_UNIT FROM v_operation WHERE v_suboperation.rd_operation_id = v_operation.id))||\")\" FROM refund s WHERE s.id=v_suboperation.r_refund_id),'') AS t_REFUNDDISPLAY, " 0563 "(CASE WHEN v_suboperation.f_value>=0 THEN v_suboperation.f_value ELSE 0 END) AS f_VALUE_INCOME, " 0564 "(CASE WHEN v_suboperation.f_value<=0 THEN v_suboperation.f_value ELSE 0 END) AS f_VALUE_EXPENSE " 0565 "FROM v_suboperation") 0566 0567 << QStringLiteral("CREATE VIEW v_suboperation_displayname AS " 0568 "SELECT *, t_CATEGORY||' : '||f_value AS t_displayname FROM v_suboperation_display") 0569 0570 // operation 0571 << "CREATE VIEW v_operation_display_all AS " 0572 "SELECT *," 0573 // "(SELECT s.t_comment FROM v_suboperation_display s WHERE s.id=v_operation.i_MOSTIMPSUBOP) AS t_COMMENT," 0574 "IFNULL((CASE WHEN v_operation.i_group_id=0 THEN '' ELSE (SELECT GROUP_CONCAT(DISTINCT(op2.t_ACCOUNT)) FROM v_operation_tmp1 op2 WHERE op2.i_group_id=v_operation.i_group_id AND op2.id<>v_operation.id) END), '') AS t_TOACCOUNT, " 0575 "(SELECT s.t_CATEGORY FROM v_suboperation_display s WHERE s.id=v_operation.i_MOSTIMPSUBOP) AS t_CATEGORY," 0576 "(SELECT s.t_REFUND FROM v_suboperation_display s WHERE s.id=v_operation.i_MOSTIMPSUBOP) AS t_REFUND," 0577 "(SELECT GROUP_CONCAT(s.t_REFUNDDISPLAY) FROM v_suboperation_display s WHERE s.rd_operation_id=v_operation.id AND s.t_REFUNDDISPLAY!='') AS t_REFUNDDISPLAY," 0578 "(CASE WHEN v_operation.f_QUANTITY<0 THEN '-' WHEN v_operation.f_QUANTITY=0 THEN '' ELSE '+' END) AS t_TYPEEXPENSE, " 0579 "(CASE WHEN v_operation.f_QUANTITY<=0 THEN '" % SKGServices::stringToSqlString(i18nc("Noun, financial transactions with a negative amount", "Expenditure")) % "' ELSE '" % SKGServices::stringToSqlString(i18nc("Noun, financial transactions with a positive amount", "Income")) % "' END) AS t_TYPEEXPENSENLS, " 0580 "TOWEEKYEAR(v_operation.d_date) AS d_DATEWEEK," 0581 "STRFTIME('%Y-%m',v_operation.d_date) AS d_DATEMONTH," 0582 "STRFTIME('%Y',v_operation.d_date)||'-Q'||(CASE WHEN STRFTIME('%m',v_operation.d_date)<='03' THEN '1' WHEN STRFTIME('%m',v_operation.d_date)<='06' THEN '2' WHEN STRFTIME('%m',v_operation.d_date)<='09' THEN '3' ELSE '4' END) AS d_DATEQUARTER, " 0583 "STRFTIME('%Y',v_operation.d_date)||'-S'||(CASE WHEN STRFTIME('%m',v_operation.d_date)<='06' THEN '1' ELSE '2' END) AS d_DATESEMESTER, " 0584 "STRFTIME('%Y',v_operation.d_date) AS d_DATEYEAR, " 0585 "(SELECT COUNT(1) FROM v_recurrentoperation s WHERE s.rd_operation_id=v_operation.id) AS i_NBRECURRENT, " 0586 "(CASE WHEN v_operation.f_QUANTITY>=0 THEN v_operation.f_QUANTITY ELSE 0 END) AS f_QUANTITY_INCOME, " 0587 "(CASE WHEN v_operation.f_QUANTITY<=0 THEN v_operation.f_QUANTITY ELSE 0 END) AS f_QUANTITY_EXPENSE, " 0588 "(SELECT o2.f_balance FROM operationbalance o2 WHERE o2.r_operation_id=v_operation.id ) AS f_BALANCE, " 0589 "(SELECT o2.f_balance_entered FROM operationbalance o2 WHERE o2.r_operation_id=v_operation.id ) AS f_BALANCE_ENTERED, " 0590 "(CASE WHEN v_operation.f_QUANTITY>=0 THEN v_operation.f_CURRENTAMOUNT ELSE 0 END) AS f_CURRENTAMOUNT_INCOME, " 0591 "(CASE WHEN v_operation.f_QUANTITY<=0 THEN v_operation.f_CURRENTAMOUNT ELSE 0 END) AS f_CURRENTAMOUNT_EXPENSE " 0592 "FROM v_operation" 0593 0594 << QStringLiteral("CREATE VIEW v_operation_display AS " 0595 "SELECT * FROM v_operation_display_all WHERE d_date!='0000-00-00' AND t_template='N'") 0596 0597 // unit 0598 << QStringLiteral("CREATE VIEW v_unit_display AS " 0599 "SELECT *," 0600 "i_nbdecimal AS i_NBDEC," 0601 "(SELECT TOTAL(o.f_QUANTITY) FROM v_operation_display_all o WHERE o.rc_unit_id=v_unit.id AND o.t_template='N') AS f_QUANTITYOWNED, " 0602 "(SELECT TOTAL(o.f_QUANTITY) FROM v_operation_display_all o WHERE o.rc_unit_id=v_unit.id AND o.t_template='N')*v_unit.f_CURRENTAMOUNT AS f_AMOUNTOWNED " 0603 "FROM v_unit") 0604 0605 // account 0606 << "CREATE VIEW v_account_display AS " 0607 "SELECT " 0608 "v_account_amount.*," 0609 "(v_account_amount.f_CURRENTAMOUNT/(SELECT u.f_CURRENTAMOUNT FROM v_unit u, operation s WHERE u.id=s.rc_unit_id AND s.rd_account_id=v_account_amount.id AND s.d_date='0000-00-00')) AS f_QUANTITY, " 0610 "(SELECT (CASE WHEN u.t_symbol!='' THEN u.t_symbol ELSE u.t_name END) FROM unit u, operation s WHERE u.id=s.rc_unit_id AND s.rd_account_id=v_account_amount.id AND s.d_date='0000-00-00') AS t_UNIT, " 0611 "(SELECT TOTAL(s.f_CURRENTAMOUNT) FROM v_operation s WHERE s.rd_account_id=v_account_amount.id AND s.t_status='Y' AND s.t_template='N') AS f_CHECKED, " 0612 "(SELECT TOTAL(s.f_CURRENTAMOUNT) FROM v_operation s WHERE s.rd_account_id=v_account_amount.id AND s.t_status!='N' AND s.t_template='N') AS f_CHECKEDANDPOINTED, " 0613 "(SELECT TOTAL(s.f_CURRENTAMOUNT) FROM v_operation s WHERE s.rd_account_id=v_account_amount.id AND s.t_status!='Y' AND s.t_template='N') AS f_COMING_SOON, " 0614 "(SELECT TOTAL(s.f_CURRENTAMOUNT) FROM v_operation s WHERE s.rd_account_id IN (SELECT id FROM account WHERE account.r_account_id=v_account_amount.id) AND s.t_status='N' AND s.t_template='N') AS f_COMING_SOON_FROM_LINKED_ACCOUNT, " 0615 "(SELECT TOTAL(s.f_CURRENTAMOUNT) FROM v_operation s WHERE s.rd_account_id=v_account_amount.id AND s.d_date<=(SELECT date('now', 'localtime')) AND s.t_template='N') AS f_TODAYAMOUNT, " 0616 "(SELECT COUNT(1) FROM v_operation_display s WHERE s.rd_account_id=v_account_amount.id) AS i_NBOPERATIONS, " 0617 "IFNULL((SELECT s.f_rate FROM interest s WHERE s.rd_account_id=v_account_amount.id AND s.d_date=v_account_amount.d_MAXDATE),0) AS f_RATE " 0618 "FROM v_account_amount" 0619 0620 // transactions 0621 << "CREATE VIEW v_suboperation_consolidated AS " 0622 "SELECT " 0623 "(SELECT s.t_TYPENLS FROM v_account_display s WHERE s.id=op.rd_account_id) AS t_ACCOUNTTYPE," 0624 "(SELECT s.t_BANK FROM v_account_display s WHERE s.id=op.rd_account_id) AS t_BANK," 0625 "(SELECT u.t_TYPENLS FROM v_unit u WHERE u.id=op.rc_unit_id) AS t_UNITTYPE," 0626 "sop.id AS id, " 0627 "sop.id AS i_SUBOPID, " 0628 "sop.r_refund_id AS r_refund_id, " 0629 "(CASE WHEN sop.t_comment='' THEN op.t_comment ELSE sop.t_comment END) AS t_REALCOMMENT, " 0630 "sop.t_CATEGORY AS t_REALCATEGORY, " 0631 "sop.t_REFUND AS t_REALREFUND, " 0632 "sop.r_category_id AS i_IDCATEGORY, " 0633 "(CASE WHEN sop.f_value<0 THEN '-' WHEN sop.f_value=0 THEN '' ELSE '+' END) AS t_TYPEEXPENSE, " 0634 "(CASE WHEN sop.f_value<=0 THEN '" % SKGServices::stringToSqlString(i18nc("Noun, financial transactions with a negative amount", "Expenditure")) % "' ELSE '" % SKGServices::stringToSqlString(i18nc("Noun, financial transactions with a positive amount", "Income")) % "' END) AS t_TYPEEXPENSENLS, " 0635 "sop.f_value AS f_REALQUANTITY, " 0636 "sop.f_VALUE_INCOME AS f_REALQUANTITY_INCOME, " 0637 "sop.f_VALUE_EXPENSE AS f_REALQUANTITY_EXPENSE, " 0638 "((SELECT u.f_CURRENTAMOUNT FROM v_unit u WHERE u.id=op.rc_unit_id)*sop.f_value) AS f_REALCURRENTAMOUNT, " 0639 "((SELECT u.f_CURRENTAMOUNT FROM v_unit u WHERE u.id=op.rc_unit_id)*sop.f_VALUE_INCOME) AS f_REALCURRENTAMOUNT_INCOME, " 0640 "((SELECT u.f_CURRENTAMOUNT FROM v_unit u WHERE u.id=op.rc_unit_id)*sop.f_VALUE_EXPENSE) AS f_REALCURRENTAMOUNT_EXPENSE, " 0641 "TOWEEKYEAR(sop.d_date) AS d_DATEWEEK," 0642 "STRFTIME('%Y-%m',sop.d_date) AS d_DATEMONTH," 0643 "STRFTIME('%Y',sop.d_date)||'-Q'||(CASE WHEN STRFTIME('%m',sop.d_date)<='03' THEN '1' WHEN STRFTIME('%m',sop.d_date)<='06' THEN '2' WHEN STRFTIME('%m',sop.d_date)<='09' THEN '3' ELSE '4' END) AS d_DATEQUARTER, " 0644 "STRFTIME('%Y',sop.d_date)||'-S'||(CASE WHEN STRFTIME('%m',sop.d_date)<='06' THEN '1' ELSE '2' END) AS d_DATESEMESTER, " 0645 "STRFTIME('%Y',sop.d_date) AS d_DATEYEAR, " 0646 "sop.d_date AS d_date, " 0647 "op.id AS i_OPID, " 0648 "op.d_date AS d_DATEOP, " 0649 "op.*, " 0650 "sop.* " 0651 "FROM v_operation_display_all AS op, v_suboperation_display AS sop WHERE +sop.rd_operation_id=op.ID AND op.t_template='N'" 0652 0653 << QStringLiteral("CREATE VIEW v_operation_prop AS " 0654 "SELECT " 0655 "p.id AS i_PROPPID, " 0656 "p.t_name AS i_PROPPNAME, " 0657 "p.t_value AS i_PROPVALUE, " 0658 "op.* " 0659 "FROM v_suboperation_consolidated AS op LEFT OUTER JOIN parameters AS p ON (p.t_uuid_parent=op.id||'-suboperation' OR p.t_uuid_parent=op.i_OPID||'-operation')") 0660 0661 // refund 0662 << "CREATE VIEW v_refund_delete AS " 0663 "SELECT *, (CASE WHEN EXISTS(SELECT 1 FROM v_suboperation_consolidated WHERE r_refund_id=refund.id AND t_status='Y') THEN '" % 0664 SKGServices::stringToSqlString(i18nc("Error message", "You are not authorized to delete this tracker because used by some checked transactions")) % 0665 "' END) t_delete_message FROM refund" 0666 0667 << QStringLiteral("CREATE VIEW v_refund AS SELECT * FROM refund") 0668 0669 << QStringLiteral("CREATE VIEW v_refund_amount AS " 0670 "SELECT *, " 0671 "(SELECT TOTAL(o.f_REALCURRENTAMOUNT) FROM v_suboperation_consolidated o WHERE o.r_refund_id=v_refund.id) AS f_CURRENTAMOUNT " 0672 "FROM v_refund") 0673 0674 << QStringLiteral("CREATE VIEW v_refund_display AS " 0675 "SELECT *," 0676 "(SELECT MIN(o.d_date) FROM v_suboperation_consolidated o WHERE o.r_refund_id=v_refund_amount.id) AS d_FIRSTDATE, " 0677 "(SELECT MAX(o.d_date) FROM v_suboperation_consolidated o WHERE o.r_refund_id=v_refund_amount.id) AS d_LASTDATE " 0678 " FROM v_refund_amount") 0679 0680 << QStringLiteral("CREATE VIEW v_refund_displayname AS " 0681 "SELECT *, t_name AS t_displayname FROM refund") 0682 0683 // Payee 0684 << "CREATE VIEW v_payee_delete AS " 0685 "SELECT *, (CASE WHEN EXISTS(SELECT 1 FROM operation WHERE r_payee_id=payee.id AND t_status='Y') THEN '" % 0686 SKGServices::stringToSqlString(i18nc("Error message", "You are not authorized to delete this payee because used by some checked transactions")) % 0687 "' END) t_delete_message FROM payee" 0688 0689 << QStringLiteral("CREATE VIEW v_payee_amount AS SELECT o.r_payee_id AS r_payee_id, TOTAL(o.f_CURRENTAMOUNT) AS f_CURRENTAMOUNT, COUNT(1) AS i_NBOPERATIONS FROM v_operation o GROUP BY o.r_payee_id") 0690 0691 << QStringLiteral("CREATE VIEW v_payee AS SELECT *," 0692 "IFNULL((SELECT s.t_fullname FROM category s WHERE s.id=payee.r_category_id),'') AS t_CATEGORY " 0693 "FROM payee") 0694 0695 << QStringLiteral("CREATE VIEW v_payee_display AS " 0696 "SELECT v_payee.*, " 0697 "(CASE WHEN p.f_CURRENTAMOUNT IS NULL THEN 0 ELSE p.f_CURRENTAMOUNT END) AS f_CURRENTAMOUNT, " 0698 "(CASE WHEN p.i_NBOPERATIONS IS NULL THEN 0 ELSE p.i_NBOPERATIONS END) AS i_NBOPERATIONS " 0699 "FROM v_payee LEFT OUTER JOIN v_payee_amount p ON p.r_payee_id=v_payee.id") 0700 0701 << QStringLiteral("CREATE VIEW v_payee_displayname AS " 0702 "SELECT *, t_name AS t_displayname FROM payee") 0703 0704 // category 0705 << "CREATE VIEW v_category_delete AS " 0706 "SELECT *, (CASE WHEN EXISTS(SELECT 1 FROM v_suboperation_consolidated WHERE (t_REALCATEGORY=category.t_fullname OR t_REALCATEGORY like category.t_fullname||'%') AND t_status='Y') THEN '" % 0707 SKGServices::stringToSqlString(i18nc("Error message", "You are not authorized to delete this category because used by some checked transactions")) % 0708 "' END) t_delete_message FROM category" 0709 0710 << QStringLiteral("CREATE VIEW v_category_amount AS SELECT o.i_IDCATEGORY AS i_IDCATEGORY, TOTAL(o.f_REALCURRENTAMOUNT) AS f_REALCURRENTAMOUNT FROM v_suboperation_consolidated o GROUP BY o.i_IDCATEGORY") 0711 0712 << QStringLiteral("CREATE VIEW v_category_display_tmp AS SELECT v_category.*, " 0713 "IFNULL(t.f_REALCURRENTAMOUNT, 0) AS f_REALCURRENTAMOUNT, " 0714 "(SELECT COUNT(DISTINCT(so.rd_operation_id)) FROM operation o, suboperation so WHERE +so.rd_operation_id=o.id AND so.r_category_id=v_category.ID AND o.t_template='N') AS i_NBOPERATIONS " 0715 "FROM v_category LEFT OUTER JOIN v_category_amount t ON t.i_IDCATEGORY=v_category.ID") 0716 0717 << QStringLiteral("CREATE VIEW v_category_used1 AS SELECT v_category.*, " 0718 "(CASE WHEN EXISTS(SELECT 1 FROM operation o, suboperation so WHERE +so.rd_operation_id=o.id AND so.r_category_id=v_category.ID AND o.t_template='N') THEN 'Y' ELSE 'N' END) AS t_ISUSED " 0719 "FROM v_category") 0720 0721 << "CREATE VIEW v_category_used2 AS SELECT v_category_used1.*, " 0722 "(CASE WHEN v_category_used1.t_ISUSED='Y' THEN 'Y' WHEN EXISTS(SELECT 1 FROM v_category_used1 c WHERE c.t_ISUSED='Y' AND c.t_fullname like v_category_used1.t_fullname||'" % OBJECTSEPARATOR % "%') THEN 'C' ELSE 'N' END) AS t_ISUSEDCASCADE " 0723 "FROM v_category_used1" 0724 0725 << "CREATE VIEW v_category_display AS SELECT *," 0726 "v_category_display_tmp.f_REALCURRENTAMOUNT+(SELECT TOTAL(c.f_REALCURRENTAMOUNT) FROM v_category_display_tmp c WHERE c.t_fullname LIKE v_category_display_tmp.t_fullname||'" % OBJECTSEPARATOR % "%') AS f_SUMCURRENTAMOUNT, " 0727 "v_category_display_tmp.i_NBOPERATIONS+(SELECT CAST(TOTAL(c.i_NBOPERATIONS) AS INTEGER) FROM v_category_display_tmp c WHERE c.t_fullname like v_category_display_tmp.t_fullname||'" % OBJECTSEPARATOR % "%') AS i_SUMNBOPERATIONS, " 0728 "(CASE WHEN v_category_display_tmp.t_bookmarked='Y' THEN 'Y' WHEN EXISTS(SELECT 1 FROM category c WHERE c.t_bookmarked='Y' AND c.t_fullname like v_category_display_tmp.t_fullname||'" % OBJECTSEPARATOR % "%') THEN 'C' ELSE 'N' END) AS t_HASBOOKMARKEDCHILD, " 0729 "(CASE WHEN v_category_display_tmp.f_REALCURRENTAMOUNT<0 THEN '-' WHEN v_category_display_tmp.f_REALCURRENTAMOUNT=0 THEN '' ELSE '+' END) AS t_TYPEEXPENSE," 0730 "(CASE WHEN v_category_display_tmp.f_REALCURRENTAMOUNT<0 THEN '" % SKGServices::stringToSqlString(i18nc("Noun, financial transactions with a negative amount", "Expenditure")) % "' WHEN v_category_display_tmp.f_REALCURRENTAMOUNT=0 THEN '' ELSE '" % SKGServices::stringToSqlString(i18nc("Noun, financial transactions with a positive amount", "Income")) % "' END) AS t_TYPEEXPENSENLS " 0731 "FROM v_category_display_tmp" 0732 0733 // recurrentoperation 0734 << QStringLiteral("CREATE VIEW v_recurrentoperation_display AS " 0735 "SELECT rop.*, op.t_ACCOUNT, op.t_number, op.t_mode, op.i_group_id, op.t_TRANSFER, op.t_PAYEE, op.t_comment, op.t_CATEGORY, op.t_status, op.f_CURRENTAMOUNT " 0736 "FROM v_recurrentoperation rop, v_operation_display_all AS op WHERE +rop.rd_operation_id=op.ID") 0737 0738 // rule 0739 << QStringLiteral("CREATE VIEW v_rule AS SELECT *," 0740 "(SELECT COUNT(1) FROM rule r WHERE r.f_sortorder<=rule.f_sortorder) AS i_ORDER " 0741 "FROM rule") 0742 << QStringLiteral("CREATE VIEW v_rule_displayname AS SELECT *, t_description AS t_displayname FROM rule") 0743 0744 << QStringLiteral("CREATE VIEW v_rule_display AS SELECT * FROM v_rule") 0745 0746 // interest 0747 << QStringLiteral("CREATE VIEW v_interest AS SELECT *," 0748 "(SELECT s.t_name FROM account s WHERE s.id=interest.rd_account_id) AS t_ACCOUNT " 0749 " FROM interest") 0750 << "CREATE VIEW v_interest_displayname AS SELECT *, IFNULL(TOFORMATTEDDATE(d_date,'" % SKGServices::stringToSqlString(dateFormatShort) % "'),STRFTIME('%Y-%m-%d',d_date))||' '||f_rate||'%' AS t_displayname FROM interest" 0751 0752 // budgetrule 0753 << "CREATE VIEW v_budgetrule AS " 0754 "SELECT *, " 0755 "(SELECT COUNT(1) FROM budgetrule r WHERE r.f_sortorder<=budgetrule.f_sortorder) AS i_ORDER, " 0756 "IFNULL((SELECT s.t_fullname FROM category s WHERE s.id=budgetrule.rc_category_id),'') AS t_CATEGORYCONDITION, " 0757 "IFNULL((SELECT s.t_fullname FROM category s WHERE s.id=budgetrule.rc_category_id_target),'') AS t_CATEGORY, " 0758 "(CASE " 0759 "WHEN budgetrule.i_condition=-1 THEN '" % SKGServices::stringToSqlString(i18nc("Noun", "Negative")) % "' " 0760 "WHEN budgetrule.i_condition=1 THEN '" % SKGServices::stringToSqlString(i18nc("Noun", "Positive")) % "' " 0761 "WHEN budgetrule.i_condition=0 THEN '" % SKGServices::stringToSqlString(i18nc("Noun", "All")) % "' " 0762 "END) AS t_WHENNLS, " 0763 "f_quantity||(CASE WHEN budgetrule.t_absolute='N' THEN '%' ELSE (SELECT t_symbol FROM unit WHERE t_type='1') END) AS t_WHATNLS," 0764 "(CASE " 0765 "WHEN budgetrule.t_rule='N' THEN '" % SKGServices::stringToSqlString(i18nc("Noun", "Next")) % "' " 0766 "WHEN budgetrule.t_rule='C' THEN '" % SKGServices::stringToSqlString(i18nc("Noun", "Current")) % "' " 0767 "WHEN budgetrule.t_rule='Y' THEN '" % SKGServices::stringToSqlString(i18nc("Noun", "Year")) % "' " 0768 "END) AS t_RULENLS " 0769 "FROM budgetrule" 0770 0771 << QStringLiteral("CREATE VIEW v_budgetrule_display AS " 0772 "SELECT * " 0773 " FROM v_budgetrule") 0774 0775 << QStringLiteral("CREATE VIEW v_budgetrule_displayname AS " 0776 "SELECT *, t_WHENNLS||' '||t_WHATNLS||' '||t_RULENLS||' '||t_CATEGORY AS t_displayname FROM v_budgetrule") 0777 0778 // budget 0779 << QStringLiteral("CREATE VIEW v_budget_tmp AS " 0780 "SELECT *, " 0781 "IFNULL((SELECT s.t_fullname FROM category s WHERE s.id=budget.rc_category_id),'') AS t_CATEGORY, " 0782 "(budget.i_year||(CASE WHEN budget.i_month=0 THEN '' WHEN budget.i_month<10 THEN '-0'||budget.i_month ELSE '-'||budget.i_month END)) AS t_PERIOD, " 0783 "(SELECT TOTAL(o.f_REALCURRENTAMOUNT) FROM v_suboperation_consolidated o WHERE o.t_TYPEACCOUNT<>'L' AND o.i_SUBOPID IN " 0784 "(SELECT b2.id_suboperation FROM budgetsuboperation b2 WHERE b2.id=budget.id)" 0785 ") AS f_CURRENTAMOUNT, " 0786 "(SELECT GROUP_CONCAT(v_budgetrule_displayname.t_displayname,',') FROM v_budgetrule_displayname WHERE " 0787 "(v_budgetrule_displayname.t_year_condition='N' OR budget.i_year=v_budgetrule_displayname.i_year) AND " 0788 "(v_budgetrule_displayname.t_month_condition='N' OR budget.i_month=v_budgetrule_displayname.i_month) AND " 0789 "(v_budgetrule_displayname.t_category_condition='N' OR budget.rc_category_id=v_budgetrule_displayname.rc_category_id) " 0790 "ORDER BY v_budgetrule_displayname.t_absolute DESC, v_budgetrule_displayname.id) AS t_RULES " 0791 "FROM budget") 0792 0793 << QStringLiteral("CREATE VIEW v_budget AS " 0794 "SELECT *, " 0795 "(f_CURRENTAMOUNT-f_budgeted_modified) AS f_DELTABEFORETRANSFER, " 0796 "(f_CURRENTAMOUNT-f_budgeted_modified-f_transferred) AS f_DELTA " 0797 "FROM v_budget_tmp") 0798 0799 << QStringLiteral("CREATE VIEW v_budget_display AS " 0800 "SELECT *, " 0801 "(f_CURRENTAMOUNT-f_budgeted_modified) AS f_DELTABEFORETRANSFER, " 0802 "(f_CURRENTAMOUNT-f_budgeted_modified-f_transferred) AS f_DELTA " 0803 "FROM vm_budget_tmp") 0804 0805 << QStringLiteral("CREATE VIEW v_budget_displayname AS " 0806 "SELECT *, t_CATEGORY||' '||t_PERIOD||' '||f_budgeted_modified AS t_displayname FROM v_budget") 0807 0808 << QStringLiteral("CREATE VIEW v_operation_all_comment AS " 0809 "SELECT t_comment FROM operation UNION SELECT t_comment FROM suboperation"); 0810 0811 IFOKDO(err, SKGDocument::refreshViewsIndexesAndTriggers(iForce)) 0812 QStringList tables; 0813 tables << QStringLiteral("account") << QStringLiteral("unit") << QStringLiteral("unitvalue") << QStringLiteral("bank") << QStringLiteral("recurrentoperation") << QStringLiteral("refund") << QStringLiteral("payee") << QStringLiteral("operation") 0814 << QStringLiteral("operationbalance") << QStringLiteral("interest") << QStringLiteral("rule") << QStringLiteral("suboperation") << QStringLiteral("budget") << QStringLiteral("budgetrule") << QStringLiteral("budgetcategory") << QStringLiteral("budgetsuboperation") << QStringLiteral("category"); 0815 IFOKDO(err, dropViewsAndIndexes(tables)) 0816 IFOKDO(err, executeSqliteOrders(BankInitialDataModelIndex)) 0817 IFOKDO(err, executeSqliteOrders(BankInitialDataModelView)) 0818 IFOKDO(err, executeSqliteOrders(BankInitialDataModelTrigger)) 0819 IFOKDO(err, executeSqliteOrder(QStringLiteral("ANALYZE"))) 0820 0821 return err; 0822 } 0823 0824 QStringList SKGDocumentBank::getMigationSteps() 0825 { 0826 SKGTRACEINFUNC(5) 0827 QStringList migrationSteps; 0828 migrationSteps.reserve(1000); 0829 migrationSteps 0830 // ============ 0831 << QLatin1String("") 0832 << QStringLiteral("0.1") 0833 << QStringLiteral("0.2") 0834 << QStringLiteral("ALTER TABLE unit ADD COLUMN rc_unit_id INTEGER NOT NULL DEFAULT 0") 0835 // ============ 0836 << QLatin1String("") 0837 << QStringLiteral("0.2") 0838 << QStringLiteral("0.3") 0839 << QStringLiteral("DROP TABLE IF EXISTS unitvalue2") 0840 << QStringLiteral("CREATE TABLE unitvalue2(" 0841 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 0842 "rd_unit_id INTEGER NOT NULL," 0843 "d_date DATE NOT NULL," 0844 "f_quantity FLOAT NOT NULL CHECK (f_quantity>=0))") 0845 0846 << QStringLiteral("INSERT INTO unitvalue2 (id,rd_unit_id,d_date,f_quantity) SELECT id,rd_unit_id,d_date,f_value FROM unitvalue") 0847 0848 << QStringLiteral("DROP TABLE IF EXISTS unitvalue") 0849 << QStringLiteral("ALTER TABLE unitvalue2 RENAME TO unitvalue") 0850 // ============ 0851 << QLatin1String("") 0852 << QStringLiteral("0.3") 0853 << QStringLiteral("0.4") 0854 << QStringLiteral("ALTER TABLE operation ADD COLUMN t_import_id TEXT DEFAULT ''") 0855 // ============ 0856 << QLatin1String("") 0857 << QStringLiteral("0.4") 0858 << QStringLiteral("0.5") 0859 << QStringLiteral("DROP TABLE IF EXISTS recurrentoperation") 0860 << QStringLiteral("CREATE TABLE recurrentoperation (" 0861 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 0862 "d_date DATE NOT NULL DEFAULT '0000-00-00'," 0863 "rd_operation_id INTEGER NOT NULL," 0864 "i_period_increment INTEGER NOT NULL DEFAULT 1 CHECK (i_period_increment>=0)," 0865 "t_period_unit TEXT NOT NULL DEFAULT 'M' CHECK (t_period_unit IN ('D', 'M', 'Y'))," 0866 "t_auto_write VARCHAR(1) DEFAULT 'Y' CHECK (t_auto_write IN ('Y', 'N'))," 0867 "i_auto_write_days INTEGER NOT NULL DEFAULT 5 CHECK (i_auto_write_days>=0)," 0868 "t_warn VARCHAR(1) DEFAULT 'Y' CHECK (t_auto_write IN ('Y', 'N'))," 0869 "i_warn_days INTEGER NOT NULL DEFAULT 5 CHECK (i_warn_days>=0)" 0870 ")") 0871 << QStringLiteral("ALTER TABLE operation ADD COLUMN r_recurrentoperation_id INTEGER NOT NULL DEFAULT 0") 0872 // ============ 0873 << QLatin1String("") 0874 << QStringLiteral("0.5") 0875 << QStringLiteral("0.6") 0876 << QStringLiteral("ALTER TABLE account ADD COLUMN t_comment TEXT NOT NULL DEFAULT ''") 0877 // ============ 0878 << QLatin1String("") 0879 << QStringLiteral("0.6") 0880 << QStringLiteral("0.7") 0881 << QStringLiteral("DROP TABLE IF EXISTS unit2") 0882 << QStringLiteral("CREATE TABLE unit2(" 0883 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 0884 "t_name TEXT NOT NULL," 0885 "t_symbol TEXT NOT NULL DEFAULT ''," 0886 "t_country TEXT NOT NULL DEFAULT ''," 0887 "t_type VARCHAR(1) NOT NULL DEFAULT 'C' CHECK (t_type IN ('1', '2', 'C', 'S', 'O'))," 0888 // 1=main currency, 2=secondary currency, C=currencies S=share O=object 0889 "t_internet_code TEXT NOT NULL DEFAULT ''," 0890 "rd_unit_id INTEGER NOT NULL DEFAULT 0)") 0891 0892 << QStringLiteral("INSERT INTO unit2 (id,t_name,t_symbol,t_country,t_type,t_internet_code,rd_unit_id) SELECT id,t_name,t_symbol,t_country,t_type,t_internet_code,rc_unit_id FROM unit") 0893 0894 << QStringLiteral("DROP TABLE IF EXISTS unit") 0895 << QStringLiteral("ALTER TABLE unit2 RENAME TO unit") 0896 // ============ 0897 << QLatin1String("") 0898 << QStringLiteral("0.7") 0899 << QStringLiteral("0.8") 0900 << QStringLiteral("DELETE FROM operation WHERE id IN (SELECT id FROM operation op WHERE NOT EXISTS(SELECT 1 FROM suboperation sop WHERE sop.rd_operation_id=op.id))") 0901 // ============ 0902 << QLatin1String("") 0903 << QStringLiteral("0.8") 0904 << QStringLiteral("0.9") 0905 << QStringLiteral("UPDATE operation SET i_group_id=0 WHERE i_group_id=''") 0906 // ============ 0907 << QLatin1String("") 0908 << QStringLiteral("0.9") 0909 << QStringLiteral("1.0") 0910 << QStringLiteral("DROP TABLE IF EXISTS unit2") 0911 << QStringLiteral("CREATE TABLE unit2(" 0912 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 0913 "t_name TEXT NOT NULL," 0914 "t_symbol TEXT NOT NULL DEFAULT ''," 0915 "t_country TEXT NOT NULL DEFAULT ''," 0916 "t_type VARCHAR(1) NOT NULL DEFAULT 'C' CHECK (t_type IN ('1', '2', 'C', 'S', 'I', 'O'))," 0917 // 1=main currency, 2=secondary currency, C=currencies S=share, I=index, O=object 0918 "t_internet_code TEXT NOT NULL DEFAULT ''," 0919 "rd_unit_id INTEGER NOT NULL DEFAULT 0)") 0920 0921 << QStringLiteral("INSERT INTO unit2 (id,t_name,t_symbol,t_country,t_type,t_internet_code,rd_unit_id) SELECT id,t_name,t_symbol,t_country,t_type,t_internet_code,rd_unit_id FROM unit") 0922 0923 << QStringLiteral("DROP TABLE IF EXISTS unit") 0924 << QStringLiteral("ALTER TABLE unit2 RENAME TO unit") 0925 // ============ 0926 << QLatin1String("") 0927 << QStringLiteral("1.0") 0928 << QStringLiteral("1.1") 0929 << QStringLiteral("DELETE FROM parameters WHERE t_name LIKE 'SKG_MONTHLY_REPORT_%'") 0930 // ============ 0931 << QLatin1String("") 0932 << QStringLiteral("1.1") 0933 << QStringLiteral("1.2") 0934 << QStringLiteral("ALTER TABLE suboperation ADD COLUMN t_comment TEXT NOT NULL DEFAULT ''") 0935 // ============ 0936 << QLatin1String("") 0937 << QStringLiteral("1.2") 0938 << QStringLiteral("1.3") 0939 << QStringLiteral("UPDATE node SET f_sortorder=id WHERE f_sortorder IS NULL OR f_sortorder=''") 0940 // ============ 0941 << QLatin1String("") 0942 << QStringLiteral("1.3") 0943 << QStringLiteral("1.4") 0944 << QStringLiteral("DROP TABLE IF EXISTS refund") 0945 << QStringLiteral("CREATE TABLE refund (" 0946 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 0947 "t_name TEXT NOT NULL DEFAULT ''," 0948 "t_comment TEXT NOT NULL DEFAULT ''," 0949 "t_close VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N')))") 0950 0951 << QStringLiteral("ALTER TABLE suboperation ADD COLUMN r_refund_id INTEGER NOT NULL DEFAULT 0") 0952 // ============ 0953 << QLatin1String("") 0954 << QStringLiteral("1.4") 0955 << QStringLiteral("1.5") 0956 << QStringLiteral("DELETE FROM parameters WHERE (t_name LIKE 'SKG_DEFAULT_%' AND t_name!='SKG_DEFAULT_PROPERTIES') OR t_name='DBVERSION'") 0957 // ============ 0958 << QLatin1String("") 0959 << QStringLiteral("1.5") 0960 << QStringLiteral("1.6") 0961 << QStringLiteral("CREATE TABLE rule (" 0962 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 0963 "t_description TEXT NOT NULL DEFAULT ''," 0964 "t_definition TEXT NOT NULL DEFAULT ''," 0965 "f_sortorder FLOAT" 0966 ")") 0967 // ============ 0968 << QLatin1String("") 0969 << QStringLiteral("1.6") 0970 << QStringLiteral("1.7") 0971 << QStringLiteral("CREATE TABLE action (" 0972 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 0973 "rd_rule_id INTEGER NOT NULL," 0974 "t_description TEXT NOT NULL DEFAULT ''," 0975 "t_definition TEXT NOT NULL DEFAULT ''" 0976 ")") 0977 << QStringLiteral("DELETE FROM rule") 0978 << QStringLiteral("DROP TABLE IF EXISTS budget") 0979 // ============ 0980 << QLatin1String("") 0981 << QStringLiteral("1.7") 0982 << QStringLiteral("1.8") 0983 << QStringLiteral("DROP TABLE IF EXISTS action") 0984 << QStringLiteral("ALTER TABLE rule ADD COLUMN t_action_description TEXT NOT NULL DEFAULT ''") 0985 << QStringLiteral("ALTER TABLE rule ADD COLUMN t_action_definition TEXT NOT NULL DEFAULT ''") 0986 << QStringLiteral("DROP TRIGGER IF EXISTS fkdc_rule_action_id_rd_rule_id") 0987 // ============ 0988 << QLatin1String("") 0989 << QStringLiteral("1.8") 0990 << QStringLiteral("1.9") 0991 << QStringLiteral("DROP TABLE IF EXISTS operation2") 0992 << QStringLiteral("CREATE TABLE operation2(" 0993 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 0994 "i_group_id INTEGER NOT NULL DEFAULT 0," 0995 "i_number INTEGER DEFAULT 0 CHECK (i_number>=0)," 0996 "d_date DATE NOT NULL DEFAULT '0000-00-00'," 0997 "rd_account_id INTEGER NOT NULL," 0998 "t_mode TEXT NOT NULL DEFAULT ''," 0999 "t_payee TEXT NOT NULL DEFAULT ''," 1000 "t_comment TEXT NOT NULL DEFAULT ''," 1001 "rc_unit_id INTEGER NOT NULL," 1002 "t_status VARCHAR(1) DEFAULT 'N' CHECK (t_status IN ('N', 'P', 'Y'))," 1003 "t_bookmarked VARCHAR(1) DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 1004 "t_imported VARCHAR(1) DEFAULT 'N' CHECK (t_imported IN ('Y', 'N', 'P'))," 1005 "t_import_id TEXT DEFAULT ''," 1006 "r_recurrentoperation_id INTEGER NOT NULL DEFAULT 0)") 1007 1008 << QStringLiteral("INSERT INTO operation2 (id,i_group_id,i_number,d_date,rd_account_id,t_mode,t_payee,t_comment,rc_unit_id," 1009 "t_status,t_bookmarked,t_imported,t_import_id,r_recurrentoperation_id) " 1010 "SELECT id,i_group_id,i_number,d_date,rd_account_id,t_mode,t_payee,t_comment,rc_unit_id," 1011 "(CASE WHEN t_status='C' THEN 'Y' ELSE t_status END),t_bookmarked,t_imported,t_import_id,r_recurrentoperation_id FROM operation") 1012 1013 << QStringLiteral("DROP TABLE IF EXISTS operation") 1014 << QStringLiteral("ALTER TABLE operation2 RENAME TO operation") 1015 // ============ 1016 << QLatin1String("") 1017 << QStringLiteral("1.9") 1018 << QStringLiteral("2.0") 1019 << QStringLiteral("ALTER TABLE operation ADD COLUMN i_tmp INTEGER NOT NULL DEFAULT 0") 1020 << QStringLiteral("ALTER TABLE suboperation ADD COLUMN i_tmp INTEGER NOT NULL DEFAULT 0") 1021 // ============ 1022 << QLatin1String("") 1023 << QStringLiteral("2.0") 1024 << QStringLiteral("2.1") 1025 << QStringLiteral("ALTER TABLE operation ADD COLUMN t_template VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_template IN ('Y', 'N'))") 1026 // ============ 1027 << QLatin1String("") 1028 << QStringLiteral("2.1") 1029 << QStringLiteral("2.2") 1030 << QStringLiteral("UPDATE recurrentoperation SET d_date=date(d_date,i_period_increment||(CASE WHEN t_period_unit='Y' THEN ' year' ELSE (CASE WHEN t_period_unit='M' THEN ' month' ELSE ' day' END) END))") 1031 1032 1033 << QStringLiteral("DROP TABLE IF EXISTS recurrentoperation2") 1034 << QStringLiteral("CREATE TABLE recurrentoperation2 (" 1035 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1036 "d_date DATE NOT NULL DEFAULT '0000-00-00'," 1037 "rd_operation_id INTEGER NOT NULL," 1038 "i_period_increment INTEGER NOT NULL DEFAULT 1 CHECK (i_period_increment>=0)," 1039 "t_period_unit TEXT NOT NULL DEFAULT 'M' CHECK (t_period_unit IN ('D', 'M', 'Y'))," 1040 "t_auto_write VARCHAR(1) DEFAULT 'Y' CHECK (t_auto_write IN ('Y', 'N'))," 1041 "i_auto_write_days INTEGER NOT NULL DEFAULT 5 CHECK (i_auto_write_days>=0)," 1042 "t_warn VARCHAR(1) DEFAULT 'Y' CHECK (t_warn IN ('Y', 'N'))," 1043 "i_warn_days INTEGER NOT NULL DEFAULT 5 CHECK (i_warn_days>=0)," 1044 "t_times VARCHAR(1) DEFAULT 'N' CHECK (t_times IN ('Y', 'N'))," 1045 "i_nb_times INTEGER NOT NULL DEFAULT 1 CHECK (i_nb_times>=0)" 1046 ")") 1047 1048 << QStringLiteral("INSERT INTO recurrentoperation2 (id,d_date,rd_operation_id,i_period_increment,t_period_unit,t_auto_write,i_auto_write_days,t_warn,i_warn_days) " 1049 "SELECT id,d_date,rd_operation_id,i_period_increment,t_period_unit,t_auto_write,i_auto_write_days,t_warn,i_warn_days FROM recurrentoperation") 1050 1051 << QStringLiteral("DROP TABLE IF EXISTS recurrentoperation") 1052 << QStringLiteral("ALTER TABLE recurrentoperation2 RENAME TO recurrentoperation") 1053 // ============ 1054 << QLatin1String("") 1055 << QStringLiteral("2.2") 1056 << QStringLiteral("2.3") 1057 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition,'%9','#ATT#')") 1058 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition,'''%1''','''#V1S#''')") 1059 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition,'''%2''','''#V2S#''')") 1060 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition,'''%%1%''','''%#V1S#%''')") 1061 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition,'''%1%''','''#V1S#%''')") 1062 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition,'''%%1''','''%#V1S#''')") 1063 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition,'%1','#V1#')") 1064 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition,'%2','#V2#')") 1065 1066 << QStringLiteral("UPDATE rule SET t_action_definition=replace(t_action_definition,'%9','#ATT#')") 1067 << QStringLiteral("UPDATE rule SET t_action_definition=replace(t_action_definition,'''%1''','''#V1S#''')") 1068 << QStringLiteral("UPDATE rule SET t_action_definition=replace(t_action_definition,'''%2''','''#V2S#''')") 1069 << QStringLiteral("UPDATE rule SET t_action_definition=replace(t_action_definition,'''%%1%''','''%#V1S#%''')") 1070 << QStringLiteral("UPDATE rule SET t_action_definition=replace(t_action_definition,'''%1%''','''#V1S#%''')") 1071 << QStringLiteral("UPDATE rule SET t_action_definition=replace(t_action_definition,'''%%1''','''%#V1S#''')") 1072 << QStringLiteral("UPDATE rule SET t_action_definition=replace(t_action_definition,'%1','#V1#')") 1073 << QStringLiteral("UPDATE rule SET t_action_definition=replace(t_action_definition,'%2','#V2#')") 1074 // ============ 1075 << QLatin1String("") 1076 << QStringLiteral("2.3") 1077 << QStringLiteral("2.4") 1078 << QStringLiteral("UPDATE operation SET t_template='N' WHERE t_template NOT IN ('Y', 'N')") 1079 // ============ 1080 << QLatin1String("") 1081 << QStringLiteral("2.4") 1082 << QStringLiteral("2.5") 1083 << QStringLiteral("ALTER TABLE rule ADD COLUMN t_action_type VARCHAR(1) DEFAULT 'S' CHECK (t_action_type IN ('S', 'U', 'A'))") 1084 << QStringLiteral("UPDATE rule SET t_action_type='S' WHERE t_action_type NOT IN ('S', 'U', 'A') AND t_action_definition=''") 1085 << QStringLiteral("UPDATE rule SET t_action_type='U' WHERE t_action_type NOT IN ('S', 'U', 'A') AND t_action_definition!=''") 1086 // ============ 1087 << QLatin1String("") 1088 << QStringLiteral("2.5") 1089 << QStringLiteral("2.6") 1090 << QStringLiteral("ALTER TABLE unit ADD COLUMN i_nbdecimal INT NOT NULL DEFAULT 2") 1091 << QStringLiteral("UPDATE unit SET i_nbdecimal=2") 1092 // ============ 1093 << QLatin1String("") 1094 << QStringLiteral("2.6") 1095 << QStringLiteral("2.7") 1096 << QStringLiteral("CREATE TABLE operation2(" 1097 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1098 "i_group_id INTEGER NOT NULL DEFAULT 0," 1099 "i_number INTEGER DEFAULT 0 CHECK (i_number>=0)," 1100 "d_date DATE NOT NULL DEFAULT '0000-00-00'," 1101 "rd_account_id INTEGER NOT NULL," 1102 "t_mode TEXT NOT NULL DEFAULT ''," 1103 "t_payee TEXT NOT NULL DEFAULT ''," 1104 "t_comment TEXT NOT NULL DEFAULT ''," 1105 "rc_unit_id INTEGER NOT NULL," 1106 "t_status VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_status IN ('N', 'P', 'Y'))," 1107 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 1108 "t_imported VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_imported IN ('Y', 'N', 'P', 'T'))," 1109 "t_template VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_template IN ('Y', 'N'))," 1110 "t_import_id TEXT NOT NULL DEFAULT ''," 1111 "i_tmp INTEGER NOT NULL DEFAULT 0," 1112 "r_recurrentoperation_id INTEGER NOT NULL DEFAULT 0)") 1113 1114 << QStringLiteral("INSERT INTO operation2 (" 1115 "id,i_group_id,i_number,d_date,rd_account_id,t_mode,t_payee,t_comment,rc_unit_id,t_status,t_bookmarked,t_imported,t_template,t_import_id,i_tmp,r_recurrentoperation_id) " 1116 "SELECT id,i_group_id,i_number,d_date,rd_account_id,t_mode,t_payee,t_comment,rc_unit_id,t_status,t_bookmarked,t_imported,t_template,t_import_id,i_tmp,r_recurrentoperation_id FROM operation") 1117 1118 << QStringLiteral("DROP TABLE IF EXISTS operation") 1119 << QStringLiteral("ALTER TABLE operation2 RENAME TO operation") 1120 // ============ 1121 << QLatin1String("") 1122 << QStringLiteral("2.7") 1123 << QStringLiteral("2.8") 1124 << QStringLiteral("UPDATE rule SET t_action_type='U' WHERE t_action_type='S' AND t_action_definition!=''") 1125 // ============ 1126 << QLatin1String("") 1127 << QStringLiteral("2.8") 1128 << QStringLiteral("2.9") 1129 << QStringLiteral("DROP TABLE IF EXISTS interest") 1130 << QStringLiteral("CREATE TABLE interest(" 1131 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1132 "rd_account_id INTEGER NOT NULL," 1133 "d_date DATE NOT NULL," 1134 "f_rate FLOAT NOT NULL CHECK (f_rate>=0)," 1135 "t_income_value_date_mode VARCHAR(1) NOT NULL DEFAULT 'F' CHECK (t_income_value_date_mode IN ('F', '0', '1', '2', '3', '4', '5'))," 1136 "t_expenditure_value_date_mode VARCHAR(1) NOT NULL DEFAULT 'F' CHECK (t_expenditure_value_date_mode IN ('F', '0', '1', '2', '3', '4', '5'))," 1137 "t_base VARCHAR(3) NOT NULL DEFAULT '24' CHECK (t_base IN ('24', '360', '365'))" 1138 ")") 1139 // ============ 1140 << QLatin1String("") 1141 << QStringLiteral("2.9") 1142 << QStringLiteral("3.0") 1143 // Current month 1144 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'period=\"\"1\"\"', 'period=\"\"1\"\" interval=\"\"2\"\" nb_intervals=\"\"1\"\"') WHERE t_data like '%Skrooge report plugin%'") 1145 // Previous month 1146 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'period=\"\"2\"\"', 'period=\"\"2\"\" interval=\"\"2\"\" nb_intervals=\"\"1\"\"') WHERE t_data like '%Skrooge report plugin%'") 1147 // Current year 1148 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'period=\"\"3\"\"', 'period=\"\"1\"\" interval=\"\"3\"\" nb_intervals=\"\"1\"\"') WHERE t_data like '%Skrooge report plugin%'") 1149 // Previous year 1150 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'period=\"\"4\"\"', 'period=\"\"2\"\" interval=\"\"3\"\" nb_intervals=\"\"1\"\"') WHERE t_data like '%Skrooge report plugin%'") 1151 // Last 30 days 1152 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'period=\"\"5\"\"', 'period=\"\"3\"\" interval=\"\"0\"\" nb_intervals=\"\"30\"\"') WHERE t_data like '%Skrooge report plugin%'") 1153 // Last 3 months 1154 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'period=\"\"6\"\"', 'period=\"\"3\"\" interval=\"\"2\"\" nb_intervals=\"\"3\"\"') WHERE t_data like '%Skrooge report plugin%'") 1155 // Last 6 months 1156 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'period=\"\"7\"\"', 'period=\"\"3\"\" interval=\"\"2\"\" nb_intervals=\"\"6\"\"') WHERE t_data like '%Skrooge report plugin%'") 1157 // Last 12 months 1158 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'period=\"\"8\"\"', 'period=\"\"3\"\" interval=\"\"2\"\" nb_intervals=\"\"12\"\"') WHERE t_data like '%Skrooge report plugin%'") 1159 // Last 2 years 1160 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'period=\"\"9\"\"', 'period=\"\"3\"\" interval=\"\"3\"\" nb_intervals=\"\"2\"\"') WHERE t_data like '%Skrooge report plugin%'") 1161 // Last 3 years 1162 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'period=\"\"10\"\"', 'period=\"\"3\"\" interval=\"\"3\"\" nb_intervals=\"\"3\"\"') WHERE t_data like '%Skrooge report plugin%'") 1163 // Last 5 years 1164 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'period=\"\"11\"\"', 'period=\"\"3\"\" interval=\"\"3\"\" nb_intervals=\"\"5\"\"') WHERE t_data like '%Skrooge report plugin%'") 1165 // Custom… 1166 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'period=\"\"12\"\"', 'period=\"\"4\"\"') WHERE t_data like '%Skrooge report plugin%'") 1167 1168 // All without transfers 1169 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'type=\"\"0\"\"', 'incomes=\"\"Y\"\" expenses=\"\"Y\"\" transfers=\"\"N\"\"') WHERE t_data like '%Skrooge report plugin%'") 1170 // Income without transfers 1171 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'type=\"\"1\"\"', 'incomes=\"\"Y\"\" expenses=\"\"N\"\" transfers=\"\"N\"\"') WHERE t_data like '%Skrooge report plugin%'") 1172 // Expenditure without transfers 1173 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'type=\"\"2\"\"', 'incomes=\"\"N\"\" expenses=\"\"Y\"\" transfers=\"\"N\"\"') WHERE t_data like '%Skrooge report plugin%'") 1174 // All with transfers 1175 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'type=\"\"3\"\"', 'incomes=\"\"Y\"\" expenses=\"\"Y\"\" transfers=\"\"Y\"\"') WHERE t_data like '%Skrooge report plugin%'") 1176 // Income with transfers 1177 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'type=\"\"4\"\"', 'incomes=\"\"Y\"\" expenses=\"\"N\"\" transfers=\"\"Y\"\"') WHERE t_data like '%Skrooge report plugin%'") 1178 // Expenditure with transfers 1179 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'type=\"\"5\"\"', 'incomes=\"\"N\"\" expenses=\"\"Y\"\" transfers=\"\"Y\"\"') WHERE t_data like '%Skrooge report plugin%'") 1180 // ============ 1181 << QLatin1String("") 1182 << QStringLiteral("3.0") 1183 << QStringLiteral("3.1") 1184 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'columns=\"\"2\"\"', 'columns=\"\"4\"\"') WHERE t_data like '%Skrooge report plugin%'") 1185 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'columns=\"\"1\"\"', 'columns=\"\"3\"\"') WHERE t_data like '%Skrooge report plugin%'") 1186 // ============ 1187 << QLatin1String("") 1188 << QStringLiteral("3.1") 1189 << QStringLiteral("3.2") 1190 << QStringLiteral("UPDATE parameters SET t_name='SKGSEARCH_DEFAULT_PARAMETERS' WHERE t_name='SKGIMPORT_DEFAULT_PARAMETERS'") 1191 // ============ 1192 << QLatin1String("") 1193 << QStringLiteral("3.2") 1194 << QStringLiteral("3.3") 1195 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns="4"', ' columns=""') WHERE t_data like '%graphicViewState=%'") 1196 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns="3"', ' columns="d_DATEYEAR"') WHERE t_data like '%graphicViewState=%'") 1197 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns="2"', ' columns="d_DATESEMESTER"') WHERE t_data like '%graphicViewState=%'") 1198 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns="1"', ' columns="d_DATEQUARTER"') WHERE t_data like '%graphicViewState=%'") 1199 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns="0"', ' columns="d_DATEMONTH"') WHERE t_data like '%graphicViewState=%'") 1200 1201 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns=\"\"4\"\"', ' columns=\"\"\"\"') WHERE t_data like '%graphicViewState=%'") 1202 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns=\"\"3\"\"', ' columns=\"\"d_DATEYEAR\"\"') WHERE t_data like '%graphicViewState=%'") 1203 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns=\"\"2\"\"', ' columns=\"\"d_DATESEMESTER\"\"') WHERE t_data like '%graphicViewState=%'") 1204 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns=\"\"1\"\"', ' columns=\"\"d_DATEQUARTER\"\"') WHERE t_data like '%graphicViewState=%'") 1205 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns=\"\"0\"\"', ' columns=\"\"d_DATEMONTH\"\"') WHERE t_data like '%graphicViewState=%'") 1206 1207 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' columns="4"', ' columns=""') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1208 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' columns="3"', ' columns="d_DATEYEAR"') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1209 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' columns="2"', ' columns="d_DATESEMESTER"') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1210 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' columns="1"', ' columns="d_DATEQUARTER"') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1211 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' columns="0"', ' columns="d_DATEMONTH"') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1212 1213 1214 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines="0"', ' lines="t_REALCATEGORY"') WHERE t_data like '%graphicViewState=%'") 1215 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines="1"', ' lines="t_payee"') WHERE t_data like '%graphicViewState=%'") 1216 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines="2"', ' lines="t_mode"') WHERE t_data like '%graphicViewState=%'") 1217 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines="3"', ' lines="t_TYPEEXPENSENLS"') WHERE t_data like '%graphicViewState=%'") 1218 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines="4"', ' lines="t_status"') WHERE t_data like '%graphicViewState=%'") 1219 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines="5"', ' lines="t_ACCOUNTTYPE"') WHERE t_data like '%graphicViewState=%'") 1220 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines="6"', ' lines="t_UNITTYPE"') WHERE t_data like '%graphicViewState=%'") 1221 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines="7"', ' lines="t_REALREFUND"') WHERE t_data like '%graphicViewState=%'") 1222 1223 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=\"\"0\"\"', ' lines=\"\"t_REALCATEGORY\"\"') WHERE t_data like '%graphicViewState=%'") 1224 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=\"\"1\"\"', ' lines=\"\"t_payee\"\"') WHERE t_data like '%graphicViewState=%'") 1225 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=\"\"2\"\"', ' lines=\"\"t_mode\"\"') WHERE t_data like '%graphicViewState=%'") 1226 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=\"\"3\"\"', ' lines=\"\"t_TYPEEXPENSENLS\"\"') WHERE t_data like '%graphicViewState=%'") 1227 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=\"\"4\"\"', ' lines=\"\"t_status\"\"') WHERE t_data like '%graphicViewState=%'") 1228 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=\"\"5\"\"', ' lines=\"\"t_ACCOUNTTYPE\"\"') WHERE t_data like '%graphicViewState=%'") 1229 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=\"\"6\"\"', ' lines=\"\"t_UNITTYPE\"\"') WHERE t_data like '%graphicViewState=%'") 1230 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=\"\"7\"\"', ' lines=\"\"t_REALREFUND\"\"') WHERE t_data like '%graphicViewState=%'") 1231 1232 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines="0"', ' lines="t_REALCATEGORY"') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1233 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines="1"', ' lines="t_payee"') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1234 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines="2"', ' lines="t_mode"') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1235 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines="3"', ' lines="t_TYPEEXPENSENLS"') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1236 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines="4"', ' lines="t_status"') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1237 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines="5"', ' lines="t_ACCOUNTTYPE"') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1238 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines="6"', ' lines="t_UNITTYPE"') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1239 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines="7"', ' lines="t_REALREFUND"') WHERE t_name='SKGDASHBOARD_DEFAULT_PARAMETERS'") 1240 // ============ 1241 << QLatin1String("") 1242 << QStringLiteral("3.3") 1243 << QStringLiteral("3.4") 1244 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' columns=\"4\"', ' columns=\"\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1245 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' columns=\"3\"', ' columns=\"d_DATEYEAR\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1246 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' columns=\"2\"', ' columns=\"d_DATESEMESTER\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1247 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' columns=\"1\"', ' columns=\"d_DATEQUARTER\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1248 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' columns=\"0\"', ' columns=\"d_DATEMONTH\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1249 1250 1251 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines=\"0\"', ' lines=\"t_REALCATEGORY\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1252 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines=\"1\"', ' lines=\"t_payee\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1253 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines=\"2\"', ' lines=\"t_mode\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1254 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines=\"3\"', ' lines=\"t_TYPEEXPENSENLS\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1255 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines=\"4\"', ' lines=\"t_status\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1256 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines=\"5\"', ' lines=\"t_ACCOUNTTYPE\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1257 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines=\"6\"', ' lines=\"t_UNITTYPE\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1258 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines=\"7\"', ' lines=\"t_REALREFUND\"') WHERE t_name='SKGREPORT_DEFAULT_PARAMETERS'") 1259 // ============ 1260 << QLatin1String("") 1261 << QStringLiteral("3.4") 1262 << QStringLiteral("3.5") 1263 << QStringLiteral("ALTER TABLE account ADD COLUMN t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))") 1264 << QStringLiteral("UPDATE account SET t_bookmarked='N'") 1265 // ============ 1266 << QLatin1String("") 1267 << QStringLiteral("3.5") 1268 << QStringLiteral("3.6") 1269 << QStringLiteral("ALTER TABLE rule ADD COLUMN t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))") 1270 << QStringLiteral("UPDATE rule SET t_bookmarked='N'") 1271 // ============ 1272 << QLatin1String("") 1273 << QStringLiteral("3.6") 1274 << QStringLiteral("3.7") 1275 << QStringLiteral("UPDATE suboperation SET r_category_id=0 WHERE r_category_id=(SELECT id FROM category WHERE t_name='')") 1276 << QStringLiteral("DELETE FROM category WHERE t_name=''") 1277 // ============ 1278 << QLatin1String("") 1279 << QStringLiteral("3.7") 1280 << QStringLiteral("3.8") 1281 << QStringLiteral("UPDATE recurrentoperation SET t_times='N' WHERE t_times IS NULL") 1282 // ============ 1283 << QLatin1String("") 1284 << QStringLiteral("3.8") 1285 << QStringLiteral("3.9") 1286 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 'Skrooge dashboard plugin', 'Dashboard plugin') WHERE t_data like '%Skrooge dashboard plugin%'") 1287 // ============ 1288 << QLatin1String("") 1289 << QStringLiteral("3.9") 1290 << QStringLiteral("4.0") 1291 << "UPDATE rule SET t_definition=replace(t_definition, '" % SKGServices::stringToSqlString(QStringLiteral("date('now', 'localtime','-1")) % "', '" % SKGServices::stringToSqlString(QStringLiteral("date('now', 'localtime','start of month','-1")) % "')" 1292 // ============ 1293 << QLatin1String("") 1294 << QStringLiteral("4.0") 1295 << QStringLiteral("4.1") 1296 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition,'t_REFUND','t_REALREFUND')") 1297 << QStringLiteral("UPDATE rule SET t_action_definition=replace(t_action_definition,'t_REFUND','t_REALREFUND')") 1298 // ============ 1299 << QLatin1String("") 1300 << QStringLiteral("4.1") 1301 << QStringLiteral("4.2") 1302 << QStringLiteral("UPDATE operation SET t_imported='Y' WHERE t_imported='T'") 1303 << QStringLiteral("UPDATE operation SET t_imported='N' WHERE t_imported!='N' AND t_import_id='';") 1304 // ============ 1305 << QLatin1String("") 1306 << QStringLiteral("4.2") 1307 << QStringLiteral("4.3") 1308 << QStringLiteral("CREATE TABLE payee (" 1309 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1310 "t_name TEXT NOT NULL DEFAULT ''," 1311 "t_address TEXT NOT NULL DEFAULT '')") 1312 1313 << QStringLiteral("INSERT INTO payee (t_name) " 1314 "SELECT distinct(operation.t_payee) FROM operation WHERE operation.t_payee<>''") 1315 1316 << QStringLiteral("CREATE TABLE operation2(" 1317 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1318 "i_group_id INTEGER NOT NULL DEFAULT 0," 1319 "i_number INTEGER DEFAULT 0 CHECK (i_number>=0)," 1320 "d_date DATE NOT NULL DEFAULT '0000-00-00'," 1321 "rd_account_id INTEGER NOT NULL," 1322 "t_mode TEXT NOT NULL DEFAULT ''," 1323 "r_payee_id INTEGER NOT NULL DEFAULT 0," 1324 "t_comment TEXT NOT NULL DEFAULT ''," 1325 "rc_unit_id INTEGER NOT NULL," 1326 "t_status VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_status IN ('N', 'P', 'Y'))," 1327 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 1328 "t_imported VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_imported IN ('Y', 'N', 'P', 'T'))," 1329 "t_template VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_template IN ('Y', 'N'))," 1330 "t_import_id TEXT NOT NULL DEFAULT ''," 1331 "i_tmp INTEGER NOT NULL DEFAULT 0," 1332 "r_recurrentoperation_id INTEGER NOT NULL DEFAULT 0)") 1333 1334 << QStringLiteral("INSERT INTO operation2 (" 1335 "id,i_group_id,i_number,d_date,rd_account_id,t_mode,r_payee_id,t_comment,rc_unit_id,t_status,t_bookmarked,t_imported,t_template,t_import_id,i_tmp,r_recurrentoperation_id) " 1336 "SELECT id,i_group_id,i_number,d_date,rd_account_id,t_mode,(CASE WHEN (SELECT payee.id FROM payee WHERE payee.t_name=operation.t_payee) IS NULL THEN 0 ELSE (SELECT payee.id FROM payee WHERE payee.t_name=operation.t_payee) END),t_comment,rc_unit_id,t_status,t_bookmarked,t_imported,t_template,t_import_id,i_tmp,r_recurrentoperation_id FROM operation") 1337 1338 << QStringLiteral("DROP TABLE IF EXISTS operation") 1339 << QStringLiteral("ALTER TABLE operation2 RENAME TO operation") 1340 1341 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, 't_payee', 't_PAYEE') WHERE t_name like '%_DEFAULT_PARAMETERS'") 1342 // ============ 1343 << QLatin1String("") 1344 << QStringLiteral("4.3") 1345 << QStringLiteral("4.4") 1346 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition, 't_payee', 't_PAYEE') WHERE t_definition like '%t_payee'") 1347 << QStringLiteral("UPDATE node SET t_data=replace(t_data, 't_payee', 't_PAYEE') WHERE t_data like '%t_payee'") 1348 // ============ 1349 << QLatin1String("") 1350 << QStringLiteral("4.4") 1351 << QStringLiteral("4.5") 1352 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition, 't_payee', 't_PAYEE') WHERE t_definition like '%t_payee%'") 1353 << QStringLiteral("UPDATE rule SET t_action_definition=replace(t_action_definition, 't_payee', 't_PAYEE') WHERE t_action_definition like '%t_payee%'") 1354 // ============ 1355 << QLatin1String("") 1356 << QStringLiteral("4.5") 1357 << QStringLiteral("4.6") 1358 << QStringLiteral("DELETE FROM suboperation WHERE NOT EXISTS (SELECT 1 FROM operation WHERE operation.id=suboperation.rd_operation_id)") 1359 // ============ 1360 << QLatin1String("") 1361 << QStringLiteral("4.6") 1362 << QStringLiteral("4.7") 1363 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' smoothScrolling="N"', ' zoomPosition="0"') WHERE t_data like '% smoothScrolling="N"%'") 1364 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' smoothScrolling="Y"', ' zoomPosition="0"') WHERE t_data like '% smoothScrolling="Y"%'") 1365 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' smoothScrolling="N"', ' zoomPosition="0"') WHERE t_value like '% smoothScrolling="N"%'") 1366 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' smoothScrolling="Y"', ' zoomPosition="0"') WHERE t_value like '% smoothScrolling="Y"%'") 1367 // ============ 1368 << QLatin1String("") 1369 << QStringLiteral("4.7") 1370 << QStringLiteral("4.8") 1371 << QStringLiteral("CREATE TABLE operationbalance(" 1372 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1373 "f_balance FLOAT NOT NULL DEFAULT 0," 1374 "r_operation_id INTEGER NOT NULL)") 1375 // ============ 1376 << QLatin1String("") 1377 << QStringLiteral("4.8") 1378 << QStringLiteral("4.9") 1379 << 1380 QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines="t_ACCOUNTTYPE" nbLevelLines="0"', ' lines="" nbLevelLines="0"')") 1381 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=\"\"t_ACCOUNTTYPE\"\" nbLevelLines=\"\"0\"\"', ' lines=\"\"\"\" nbLevelLines=\"\"0\"\"')") 1382 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=&quot;t_ACCOUNTTYPE&quot; nbLevelLines=&quot;0&quot;', ' lines=&quot;&quot; nbLevelLines=&quot;0&quot;')") 1383 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines=&quot;t_ACCOUNTTYPE&quot; nbLevelLines=&quot;0&quot;', ' lines=&quot;&quot; nbLevelLines=&quot;0&quot;')") 1384 1385 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines="t_UNITTYPE"', ' lines="t_UNITTYPE" lines2="t_UNIT"')") 1386 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=\"\"t_UNITTYPE\"\"', ' lines=\"\"t_UNITTYPE\"\" lines2=\"\"t_UNIT\"\"')") 1387 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=&quot;t_UNITTYPE&quot;', ' lines=&quot;t_UNITTYPE&quot; lines2=&quot;t_UNIT&quot;')") 1388 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines=&quot;t_UNITTYPE&quot;', ' lines=&quot;t_UNITTYPE&quot; lines2=&quot;t_UNIT&quot;')") 1389 1390 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines="t_ACCOUNTTYPE"', ' lines="t_ACCOUNTTYPE" lines2="t_ACCOUNT"')") 1391 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=\"\"t_ACCOUNTTYPE\"\"', ' lines=\"\"t_ACCOUNTTYPE\"\" lines2=\"\"t_ACCOUNT\"\"')") 1392 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=&quot;t_ACCOUNTTYPE&quot;', ' lines=&quot;t_ACCOUNTTYPE&quot; lines2=&quot;t_ACCOUNT&quot;')") 1393 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines=&quot;t_ACCOUNTTYPE&quot;', ' lines=&quot;t_ACCOUNTTYPE&quot; lines2=&quot;t_ACCOUNT&quot;')") 1394 1395 // ============ 1396 << QLatin1String("") 1397 << QStringLiteral("4.9") 1398 << QStringLiteral("5.0") 1399 << QStringLiteral("CREATE TABLE budget (" 1400 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1401 "rc_category_id INTEGER NOT NULL DEFAULT 0," 1402 "f_budgeted FLOAT NOT NULL DEFAULT 0.0," 1403 "i_year INTEGER NOT NULL DEFAULT 2010," 1404 "i_month INTEGER NOT NULL DEFAULT 0 CHECK (i_month>=0 AND i_month<=12)" 1405 ")") 1406 // ============ 1407 << QLatin1String("") 1408 << QStringLiteral("5.0") 1409 << QStringLiteral("5.1") 1410 << QStringLiteral("CREATE TABLE budgetrule (" 1411 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1412 "rc_category_id INTEGER NOT NULL DEFAULT 0," 1413 "i_year INTEGER NOT NULL DEFAULT 2010," 1414 "i_month INTEGER NOT NULL DEFAULT 0 CHECK (i_month>=0 AND i_month<=12)," 1415 "i_condition INTEGER NOT NULL DEFAULT 0 CHECK (i_condition IN (-1,0,1))," 1416 "f_quantity FLOAT NOT NULL DEFAULT 0.0," 1417 "t_absolute TEXT NOT NULL DEFAULT 'Y' CHECK (t_absolute IN ('Y', 'N'))," 1418 "rc_category_id_target INTEGER NOT NULL DEFAULT 0," 1419 "t_rule TEXT NOT NULL DEFAULT 'N' CHECK (t_rule IN ('N', 'C', 'Y'))" 1420 ")") 1421 // ============ 1422 << QLatin1String("") 1423 << QStringLiteral("5.1") 1424 << QStringLiteral("5.2") 1425 << QStringLiteral("CREATE TABLE budgetcategory(" 1426 "id INTEGER NOT NULL DEFAULT 0," 1427 "id_category INTEGER NOT NULL DEFAULT 0)") 1428 // ============ 1429 << QLatin1String("") 1430 << QStringLiteral("5.2") 1431 << QStringLiteral("5.3") 1432 << QStringLiteral("ALTER TABLE budget ADD COLUMN f_budgeted_modified FLOAT NOT NULL DEFAULT 0.0") 1433 << QStringLiteral("UPDATE budget SET f_budgeted_modified=f_budgeted") 1434 // ============ 1435 << QLatin1String("") 1436 << QStringLiteral("5.3") 1437 << QStringLiteral("5.4") 1438 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=""', ' lines="#NOTHING#"')") 1439 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=\"\"\"\"', ' lines=\"\"#NOTHING#\"\"')") 1440 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines=&quot;&quot;', ' lines=&quot;#NOTHING#&quot;')") 1441 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines=&quot;&quot;', ' lines=&quot;#NOTHING#&quot;')") 1442 1443 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines2=""', ' lines2="#NOTHING#"')") 1444 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines2=\"\"\"\"', ' lines2=\"\"#NOTHING#\"\"')") 1445 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' lines2=&quot;&quot;', ' lines2=&quot;#NOTHING#&quot;')") 1446 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' lines2=&quot;&quot;', ' lines2=&quot;#NOTHING#&quot;')") 1447 1448 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns=""', ' columns="#NOTHING#"')") 1449 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns=\"\"\"\"', ' columns=\"\"#NOTHING#\"\"')") 1450 << QStringLiteral("UPDATE node SET t_data=replace(t_data, ' columns=&quot;&quot;', ' columns=&quot;#NOTHING#&quot;')") 1451 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' columns=&quot;&quot;', ' columns=&quot;#NOTHING#&quot;')") 1452 // ============ 1453 << QLatin1String("") 1454 << QStringLiteral("5.4") 1455 << QStringLiteral("5.5") 1456 << QStringLiteral("ALTER TABLE budgetrule ADD COLUMN t_category_condition TEXT NOT NULL DEFAULT 'Y' CHECK (t_category_condition IN ('Y', 'N'))") 1457 << QStringLiteral("ALTER TABLE budgetrule ADD COLUMN t_year_condition TEXT NOT NULL DEFAULT 'Y' CHECK (t_year_condition IN ('Y', 'N'))") 1458 << QStringLiteral("ALTER TABLE budgetrule ADD COLUMN t_month_condition TEXT NOT NULL DEFAULT 'Y' CHECK (t_month_condition IN ('Y', 'N'))") 1459 1460 << QStringLiteral("UPDATE budgetrule SET t_year_condition='Y'") 1461 << QStringLiteral("UPDATE budgetrule SET t_year_condition='N', i_year=2010 WHERE i_year=0") 1462 << QStringLiteral("UPDATE budgetrule SET t_month_condition='Y'") 1463 << QStringLiteral("UPDATE budgetrule SET t_month_condition='N', i_month=1 WHERE i_month=0") 1464 << QStringLiteral("UPDATE budgetrule SET t_category_condition='Y'") 1465 << QStringLiteral("UPDATE budgetrule SET t_category_condition='N' WHERE rc_category_id=0") 1466 // ============ 1467 << QLatin1String("") 1468 << QStringLiteral("5.5") 1469 << QStringLiteral("5.6") 1470 << QStringLiteral("ALTER TABLE budgetrule ADD COLUMN t_category_target TEXT NOT NULL DEFAULT 'Y' CHECK (t_category_target IN ('Y', 'N'))") 1471 << QStringLiteral("UPDATE budgetrule SET t_category_target='N'") 1472 // ============ 1473 << QLatin1String("") 1474 << QStringLiteral("5.6") 1475 << QStringLiteral("5.7") 1476 << QStringLiteral("ALTER TABLE budget ADD COLUMN f_transferred FLOAT NOT NULL DEFAULT 0.0") 1477 << QStringLiteral("UPDATE budget SET f_transferred=0") 1478 // ============ 1479 << QLatin1String("") 1480 << QStringLiteral("5.7") 1481 << QStringLiteral("5.8") 1482 << QStringLiteral("ALTER TABLE budget ADD COLUMN t_including_subcategories TEXT NOT NULL DEFAULT 'N' CHECK (t_including_subcategories IN ('Y', 'N'));") 1483 << QStringLiteral("UPDATE budget SET t_including_subcategories='N'") 1484 // ============ 1485 << QLatin1String("") 1486 << QStringLiteral("5.8") 1487 << QStringLiteral("5.9") 1488 << QStringLiteral("DELETE FROM parameters WHERE t_uuid_parent='advices';") 1489 // ============ 1490 << QLatin1String("") 1491 << QStringLiteral("5.9") 1492 << QStringLiteral("6.0") 1493 << QStringLiteral("UPDATE category SET t_name=t_name;") 1494 // ============ 1495 << QLatin1String("") 1496 << QStringLiteral("6.0") 1497 << QStringLiteral("6.1") 1498 << QStringLiteral("UPDATE node SET t_data=replace(t_data,'t_type', 't_TYPENLS')") 1499 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, 't_type', 't_TYPENLS') where t_name like '%_DEFAULT_PARAMETERS'") 1500 // ============ 1501 << QLatin1String("") 1502 << QStringLiteral("6.1") 1503 << QStringLiteral("6.2") 1504 << QStringLiteral("CREATE TABLE account2(" 1505 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1506 "t_name TEXT NOT NULL," 1507 "t_number TEXT NOT NULL DEFAULT ''," 1508 "t_agency_number TEXT NOT NULL DEFAULT ''," 1509 "t_agency_address TEXT NOT NULL DEFAULT ''," 1510 "t_comment TEXT NOT NULL DEFAULT ''," 1511 "t_close VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))," 1512 "t_type VARCHAR(1) NOT NULL DEFAULT 'C' CHECK (t_type IN ('C', 'D', 'A', 'I', 'O', 'W'))," 1513 // C=current D=credit card A=assets (for objects) I=Investment W=Wallet O=other 1514 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 1515 "rd_bank_id INTEGER NOT NULL)") 1516 << QStringLiteral("INSERT INTO account2 (id, t_name, t_number, t_agency_number, t_agency_address, t_comment, t_close, t_type, t_bookmarked, rd_bank_id) " 1517 "SELECT id, t_name, t_number, t_agency_number, t_agency_address, t_comment, t_close, t_type, t_bookmarked, rd_bank_id FROM account") 1518 << QStringLiteral("DROP TABLE IF EXISTS account") 1519 << QStringLiteral("ALTER TABLE account2 RENAME TO account") 1520 // ============ 1521 << QLatin1String("") 1522 << QStringLiteral("6.2") 1523 << QStringLiteral("6.3") 1524 << QStringLiteral("ALTER TABLE suboperation ADD COLUMN t_formula TEXT NOT NULL DEFAULT '';") 1525 << QStringLiteral("UPDATE suboperation SET t_formula=''") 1526 // ============ 1527 << QLatin1String("") 1528 << QStringLiteral("6.3") 1529 << QStringLiteral("6.4") 1530 << QStringLiteral("CREATE TABLE vm_category_display_tmp( id INT, t_name TEXT, t_fullname TEXT, rd_category_id INT, i_NBOPERATIONS, f_REALCURRENTAMOUNT)") 1531 // ============ 1532 << QLatin1String("") 1533 << QStringLiteral("6.4") 1534 << QStringLiteral("6.5") 1535 << QStringLiteral("ALTER TABLE category ADD COLUMN t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'));") 1536 << QStringLiteral("ALTER TABLE payee ADD COLUMN t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'));") 1537 << QStringLiteral("UPDATE category SET t_bookmarked='N'") 1538 << QStringLiteral("UPDATE payee SET t_bookmarked='N'") 1539 // ============ 1540 << QLatin1String("") 1541 << QStringLiteral("6.5") 1542 << QStringLiteral("6.6") 1543 << QStringLiteral("CREATE TABLE vm_budget_tmp( id INT, rc_category_id INT, f_budgeted REAL, i_year INT, i_month INT, f_budgeted_modified REAL, f_transferred REAL, t_including_subcategories TEXT, t_CATEGORY, t_PERIOD, f_CURRENTAMOUNT, t_RULES)") 1544 // ============ 1545 << QLatin1String("") 1546 << QStringLiteral("6.6") 1547 << QStringLiteral("6.7") 1548 << QStringLiteral("DROP TABLE IF EXISTS vm_category_display_tmp") 1549 << QStringLiteral("CREATE TABLE vm_category_display_tmp( id INT, t_name TEXT, t_fullname TEXT, rd_category_id INT, i_NBOPERATIONS, f_REALCURRENTAMOUNT, t_bookmarked)") 1550 // ============ 1551 << QLatin1String("") 1552 << QStringLiteral("6.7") 1553 << QStringLiteral("6.8") 1554 << QStringLiteral("CREATE TABLE recurrentoperation2 (" 1555 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1556 "d_date DATE NOT NULL DEFAULT '0000-00-00'," 1557 "rd_operation_id INTEGER NOT NULL," 1558 "i_period_increment INTEGER NOT NULL DEFAULT 1 CHECK (i_period_increment>=0)," 1559 "t_period_unit TEXT NOT NULL DEFAULT 'M' CHECK (t_period_unit IN ('D', 'W', 'M', 'Y'))," 1560 "t_auto_write VARCHAR(1) DEFAULT 'Y' CHECK (t_auto_write IN ('Y', 'N'))," 1561 "i_auto_write_days INTEGER NOT NULL DEFAULT 5 CHECK (i_auto_write_days>=0)," 1562 "t_warn VARCHAR(1) DEFAULT 'Y' CHECK (t_warn IN ('Y', 'N'))," 1563 "i_warn_days INTEGER NOT NULL DEFAULT 5 CHECK (i_warn_days>=0)," 1564 "t_times VARCHAR(1) DEFAULT 'N' CHECK (t_times IN ('Y', 'N'))," 1565 "i_nb_times INTEGER NOT NULL DEFAULT 1 CHECK (i_nb_times>=0)" 1566 ")") 1567 1568 << QStringLiteral("INSERT INTO recurrentoperation2 (id,d_date,rd_operation_id,i_period_increment,t_period_unit,t_auto_write,i_auto_write_days,t_warn,i_warn_days,t_times,i_nb_times) " 1569 "SELECT id,d_date,rd_operation_id,i_period_increment,t_period_unit,t_auto_write,i_auto_write_days,t_warn,i_warn_days,t_times,i_nb_times FROM recurrentoperation") 1570 1571 << QStringLiteral("DROP TABLE IF EXISTS recurrentoperation") 1572 << QStringLiteral("ALTER TABLE recurrentoperation2 RENAME TO recurrentoperation") 1573 // ============ 1574 << QLatin1String("") 1575 << QStringLiteral("6.8") 1576 << QStringLiteral("6.9") 1577 << "CREATE TABLE category2 (" 1578 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1579 "t_name TEXT NOT NULL DEFAULT '' CHECK (t_name NOT LIKE '%" % OBJECTSEPARATOR % "%')," 1580 "t_fullname TEXT," 1581 "rd_category_id INT," 1582 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))" 1583 ")" 1584 << QStringLiteral("INSERT INTO category2 (id, t_name, t_fullname, rd_category_id, t_bookmarked) " 1585 "SELECT id, t_name, t_fullname, r_category_id, t_bookmarked FROM category") 1586 1587 << QStringLiteral("DROP TABLE IF EXISTS category") 1588 << QStringLiteral("ALTER TABLE category2 RENAME TO category") 1589 1590 << QStringLiteral("DROP TABLE IF EXISTS vm_category_display_tmp") 1591 << QStringLiteral("CREATE TABLE vm_category_display_tmp( id INT, t_name TEXT, t_fullname TEXT, rd_category_id INT, i_NBOPERATIONS, f_REALCURRENTAMOUNT, t_bookmarked)") 1592 // ============ 1593 << QLatin1String("") 1594 << QStringLiteral("6.9") 1595 << QStringLiteral("7.0") 1596 << QStringLiteral("DELETE FROM parameters WHERE t_name LIKE 'SKG_MONTHLY_REPORT_%'") 1597 // ============ SKROOGE 1.0.0 ^^^ 1598 << QLatin1String("") 1599 << QStringLiteral("7.0") 1600 << QStringLiteral("7.1") 1601 << QStringLiteral("ALTER TABLE unit ADD COLUMN t_source TEXT NOT NULL DEFAULT ''") 1602 << QStringLiteral("UPDATE unit SET t_source=''") 1603 // ============ 1604 << QLatin1String("") 1605 << QStringLiteral("7.1") 1606 << QStringLiteral("7.2") 1607 << QStringLiteral("UPDATE unit SET t_source='Yahoo' WHERE t_source='' AND t_internet_code<>''") 1608 // ============ 1609 << QLatin1String("") 1610 << QStringLiteral("7.2") 1611 << QStringLiteral("7.3") 1612 << QStringLiteral("CREATE TABLE account2(" 1613 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1614 "t_name TEXT NOT NULL," 1615 "t_number TEXT NOT NULL DEFAULT ''," 1616 "t_agency_number TEXT NOT NULL DEFAULT ''," 1617 "t_agency_address TEXT NOT NULL DEFAULT ''," 1618 "t_comment TEXT NOT NULL DEFAULT ''," 1619 "t_close VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))," 1620 "t_type VARCHAR(1) NOT NULL DEFAULT 'C' CHECK (t_type IN ('C', 'D', 'A', 'I', 'L', 'W', 'O'))," 1621 // C=current D=credit card A=assets (for objects) I=Investment W=Wallet L=Loan O=other 1622 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 1623 "rd_bank_id INTEGER NOT NULL)") 1624 << QStringLiteral("INSERT INTO account2 (id, t_name, t_number, t_agency_number, t_agency_address, t_comment, t_close, t_type, t_bookmarked, rd_bank_id) " 1625 "SELECT id, t_name, t_number, t_agency_number, t_agency_address, t_comment, t_close, t_type, t_bookmarked, rd_bank_id FROM account") 1626 << QStringLiteral("DROP TABLE IF EXISTS account") 1627 << QStringLiteral("ALTER TABLE account2 RENAME TO account") 1628 // ============ SKROOGE 1.1.0 ^^^ 1629 << QLatin1String("") 1630 << QStringLiteral("7.3") 1631 << QStringLiteral("7.4") 1632 << QStringLiteral("ALTER TABLE unit ADD COLUMN t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))") 1633 << QStringLiteral("UPDATE unit SET t_bookmarked='N'") 1634 // ============ 1635 << QLatin1String("") 1636 << QStringLiteral("7.4") 1637 << QStringLiteral("7.5") 1638 << QStringLiteral("DELETE FROM parameters WHERE t_name LIKE 'SKGOPERATION_%'") 1639 // ============ 1640 << QLatin1String("") 1641 << QStringLiteral("7.5") 1642 << QStringLiteral("7.6") 1643 << "CREATE TABLE category2 (" 1644 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1645 "t_name TEXT NOT NULL DEFAULT '' CHECK (t_name NOT LIKE '%" % OBJECTSEPARATOR % "%')," 1646 "t_fullname TEXT," 1647 "rd_category_id INTEGER NOT NULL DEFAULT 0," 1648 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))" 1649 ")" 1650 << QStringLiteral("INSERT INTO category2 (id, t_name, t_fullname, rd_category_id, t_bookmarked) " 1651 "SELECT id, t_name, t_fullname, (CASE WHEN rd_category_id IS NULL OR rd_category_id='' THEN 0 ELSE rd_category_id END), t_bookmarked FROM category") 1652 1653 << QStringLiteral("DROP TABLE IF EXISTS category") 1654 << QStringLiteral("ALTER TABLE category2 RENAME TO category") 1655 // ============ 1656 << QLatin1String("") 1657 << QStringLiteral("7.6") 1658 << QStringLiteral("7.7") 1659 << QStringLiteral("ALTER TABLE operationbalance ADD COLUMN f_balance_entered FLOAT NOT NULL DEFAULT 0") 1660 // ============ SKROOGE 1.3.2 ^^^ 1661 << QLatin1String("") 1662 << QStringLiteral("7.7") 1663 << QStringLiteral("7.8") 1664 << QStringLiteral("CREATE TABLE account2(" 1665 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1666 "t_name TEXT NOT NULL," 1667 "t_number TEXT NOT NULL DEFAULT ''," 1668 "t_agency_number TEXT NOT NULL DEFAULT ''," 1669 "t_agency_address TEXT NOT NULL DEFAULT ''," 1670 "t_comment TEXT NOT NULL DEFAULT ''," 1671 "t_close VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))," 1672 "t_type VARCHAR(1) NOT NULL DEFAULT 'C' CHECK (t_type IN ('C', 'D', 'A', 'I', 'L', 'W', 'S', 'O'))," 1673 // C=current D=credit card A=assets (for objects) I=Investment W=Wallet L=Loan S=Saving O=other 1674 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 1675 "rd_bank_id INTEGER NOT NULL)") 1676 << QStringLiteral("INSERT INTO account2 (id, t_name, t_number, t_agency_number, t_agency_address, t_comment, t_close, t_type, t_bookmarked, rd_bank_id) " 1677 "SELECT id, t_name, t_number, t_agency_number, t_agency_address, t_comment, t_close, t_type, t_bookmarked, rd_bank_id FROM account") 1678 << QStringLiteral("DROP TABLE IF EXISTS account") 1679 << QStringLiteral("ALTER TABLE account2 RENAME TO account") 1680 // ============ 1681 << QLatin1String("") 1682 << QStringLiteral("7.8") 1683 << QStringLiteral("7.9") 1684 << QStringLiteral("DROP TABLE IF EXISTS vm_budget_tmp") 1685 << QStringLiteral("CREATE TABLE vm_budget_tmp( id INT, rc_category_id INT, f_budgeted REAL, i_year INT, i_month INT, f_budgeted_modified REAL, f_transferred REAL, t_including_subcategories TEXT, t_CATEGORY, t_PERIOD, f_CURRENTAMOUNT, t_RULES)") 1686 << QStringLiteral("DROP TABLE IF EXISTS vm_category_display_tmp") 1687 << QStringLiteral("CREATE TABLE vm_category_display_tmp( id INT, t_name TEXT, t_fullname TEXT, rd_category_id INT, i_NBOPERATIONS, f_REALCURRENTAMOUNT, t_bookmarked)") 1688 // ============ SKROOGE 1.3.3 ^^^ 1689 << QLatin1String("") 1690 << QStringLiteral("7.9") 1691 << QStringLiteral("8.0") 1692 << QStringLiteral("DROP TABLE IF EXISTS operationbalance") 1693 << QStringLiteral("CREATE TABLE operationbalance(" 1694 "r_operation_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1695 "f_balance FLOAT NOT NULL DEFAULT 0," 1696 "f_balance_entered FLOAT NOT NULL DEFAULT 0)") 1697 << QLatin1String("") 1698 << QStringLiteral("8.0") 1699 << QStringLiteral("8.1") 1700 << QStringLiteral("DROP TABLE IF EXISTS operationbalance") 1701 << QStringLiteral("CREATE TABLE operationbalance(" 1702 "r_operation_id INTEGER NOT NULL," 1703 "f_balance FLOAT NOT NULL DEFAULT 0," 1704 "f_balance_entered FLOAT NOT NULL DEFAULT 0)") 1705 // ============ SKROOGE 1.4.0 ^^^ 1706 << QLatin1String("") 1707 << QStringLiteral("8.1") 1708 << QStringLiteral("8.2") 1709 << QStringLiteral("DROP TABLE IF EXISTS budgetcategory") 1710 << QStringLiteral("CREATE TABLE budgetsuboperation(" 1711 "id INTEGER NOT NULL DEFAULT 0," 1712 "id_suboperation INTEGER NOT NULL DEFAULT 0," 1713 "i_priority INTEGER NOT NULL DEFAULT 0)") 1714 << QLatin1String("") 1715 << QStringLiteral("8.2") 1716 << QStringLiteral("8.3") 1717 << QStringLiteral("DROP TABLE IF EXISTS vm_category_display_tmp") 1718 << QStringLiteral("DROP TRIGGER IF EXISTS fkdc_category_vm_category_display_tmp_id_rd_category_id") 1719 // ============ SKROOGE 1.7.4 ^^^ 1720 << QLatin1String("") 1721 << QStringLiteral("8.3") 1722 << QStringLiteral("8.4") 1723 << QStringLiteral("ALTER TABLE account ADD COLUMN f_maxamount FLOAT NOT NULL DEFAULT 10000.0") 1724 << QStringLiteral("ALTER TABLE account ADD COLUMN t_maxamount_enabled VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))") 1725 << QStringLiteral("ALTER TABLE account ADD COLUMN f_minamount FLOAT NOT NULL DEFAULT 0.0") 1726 << QStringLiteral("ALTER TABLE account ADD COLUMN t_minamount_enabled VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))") 1727 << QStringLiteral("UPDATE account SET f_maxamount=10000.0, t_maxamount_enabled='N', f_minamount=0.0, t_minamount_enabled='N'") 1728 // ============ SKROOGE 1.7.7 ^^^ 1729 << QLatin1String("") 1730 << QStringLiteral("8.4") 1731 << QStringLiteral("8.5") 1732 << QStringLiteral("ALTER TABLE account ADD COLUMN d_reconciliationdate DATE") 1733 // ============ 1734 << QLatin1String("") 1735 << QStringLiteral("8.5") 1736 << QStringLiteral("8.6") 1737 << QStringLiteral("CREATE TABLE account2(" 1738 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1739 "t_name TEXT NOT NULL," 1740 "t_number TEXT NOT NULL DEFAULT ''," 1741 "t_agency_number TEXT NOT NULL DEFAULT ''," 1742 "t_agency_address TEXT NOT NULL DEFAULT ''," 1743 "t_comment TEXT NOT NULL DEFAULT ''," 1744 "t_close VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))," 1745 "t_type VARCHAR(1) NOT NULL DEFAULT 'C' CHECK (t_type IN ('C', 'D', 'A', 'I', 'L', 'W', 'S', 'P', 'O'))," 1746 // C=current D=credit card A=assets (for objects) I=Investment W=Wallet L=Loan S=Saving P=Pension O=other 1747 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 1748 "f_maxamount FLOAT NOT NULL DEFAULT 10000.0," 1749 "t_maxamount_enabled VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))," 1750 "f_minamount FLOAT NOT NULL DEFAULT 0.0," 1751 "t_minamount_enabled VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))," 1752 "d_reconciliationdate DATE," 1753 "rd_bank_id INTEGER NOT NULL)") 1754 << QStringLiteral("INSERT INTO account2 (id, t_name, t_number, t_agency_number, t_agency_address, t_comment, t_close, t_type, t_bookmarked, f_maxamount, t_maxamount_enabled, f_minamount, t_minamount_enabled, d_reconciliationdate, rd_bank_id) " 1755 "SELECT id, t_name, t_number, t_agency_number, t_agency_address, t_comment, t_close, t_type, t_bookmarked, f_maxamount, t_maxamount_enabled, f_minamount, t_minamount_enabled, d_reconciliationdate, rd_bank_id FROM account") 1756 << QStringLiteral("DROP TABLE IF EXISTS account") 1757 << QStringLiteral("ALTER TABLE account2 RENAME TO account") 1758 // ============ SKROOGE 1.8.0 ^^^ 1759 << QLatin1String("") 1760 << QStringLiteral("8.6") 1761 << QStringLiteral("8.7") 1762 << QStringLiteral("ALTER TABLE suboperation ADD COLUMN d_date DATE NOT NULL DEFAULT '0000-00-00'") 1763 << QStringLiteral("UPDATE suboperation SET d_date=(SELECT d_date FROM operation WHERE suboperation.rd_operation_id=operation.id)") 1764 << QLatin1String("") 1765 << QStringLiteral("8.7") 1766 << QStringLiteral("8.8") 1767 << QStringLiteral("UPDATE rule SET t_action_definition=replace(t_action_definition, '\"d_date\"', '\"d_DATEOP\"') WHERE t_action_definition like '%\"d_date\"%'") 1768 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition, '\"d_date\"', '\"d_DATEOP\"') WHERE t_definition like '%\"d_date\"%'") 1769 // ============ SKROOGE 1.9.0 ^^^ 1770 << QLatin1String("") 1771 << QStringLiteral("8.8") 1772 << QStringLiteral("8.9") 1773 << QStringLiteral("ALTER TABLE suboperation ADD COLUMN i_order INTEGER NOT NULL DEFAULT 0") 1774 << QStringLiteral("UPDATE suboperation SET i_order=id") 1775 << QLatin1String("") 1776 << QStringLiteral("8.9") 1777 << QStringLiteral("9.0") 1778 << QStringLiteral("ALTER TABLE account ADD COLUMN r_account_id INTEGER NOT NULL DEFAULT 0") 1779 << QStringLiteral("UPDATE account SET r_account_id=0") 1780 << QLatin1String("") 1781 << QStringLiteral("9.0") 1782 << QStringLiteral("9.1") 1783 << QStringLiteral("CREATE TABLE rule2 (" 1784 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1785 "t_description TEXT NOT NULL DEFAULT ''," 1786 "t_definition TEXT NOT NULL DEFAULT ''," 1787 "t_action_description TEXT NOT NULL DEFAULT ''," 1788 "t_action_definition TEXT NOT NULL DEFAULT ''," 1789 "t_action_type VARCHAR(1) DEFAULT 'S' CHECK (t_action_type IN ('S', 'U', 'A', 'T'))," 1790 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 1791 "f_sortorder FLOAT" 1792 ")") 1793 << QStringLiteral("INSERT INTO rule2 (id, t_description, t_definition, t_action_description, t_action_definition, t_action_type, t_bookmarked,f_sortorder) SELECT id, t_description, t_definition, t_action_description, t_action_definition, t_action_type, t_bookmarked,f_sortorder FROM rule") 1794 << QStringLiteral("DROP TABLE IF EXISTS rule") 1795 << QStringLiteral("ALTER TABLE rule2 RENAME TO rule") 1796 << QLatin1String("") 1797 << QStringLiteral("9.1") 1798 << QStringLiteral("9.2") 1799 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' limitVisible=&amp;quot;Y&amp;quot; ', ' limitVisible=&amp;quot;Y&amp;quot; averageVisible=&amp;quot;Y&amp;quot; ')") 1800 << QStringLiteral("UPDATE parameters SET t_value=replace(t_value, ' limitVisible=&amp;quot;N&amp;quot; ', ' limitVisible=&amp;quot;N&amp;quot; averageVisible=&amp;quot;N&amp;quot; ')") 1801 << QLatin1String("") 1802 << QStringLiteral("9.2") 1803 << QStringLiteral("9.3") 1804 << QStringLiteral("CREATE TABLE operation2 (" 1805 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1806 "i_group_id INTEGER NOT NULL DEFAULT 0," 1807 "i_number INTEGER DEFAULT 0 CHECK (i_number>=0)," 1808 "d_date DATE NOT NULL DEFAULT '0000-00-00'," 1809 "d_createdate DATE NOT NULL DEFAULT CURRENT_TIMESTAMP," 1810 "rd_account_id INTEGER NOT NULL," 1811 "t_mode TEXT NOT NULL DEFAULT ''," 1812 "r_payee_id INTEGER NOT NULL DEFAULT 0," 1813 "t_comment TEXT NOT NULL DEFAULT ''," 1814 "rc_unit_id INTEGER NOT NULL," 1815 "t_status VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_status IN ('N', 'P', 'Y'))," 1816 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 1817 "t_imported VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_imported IN ('Y', 'N', 'P', 'T'))," 1818 "t_template VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_template IN ('Y', 'N'))," 1819 "t_import_id TEXT NOT NULL DEFAULT ''," 1820 "i_tmp INTEGER NOT NULL DEFAULT 0," 1821 "r_recurrentoperation_id INTEGER NOT NULL DEFAULT 0)") 1822 << QStringLiteral("INSERT INTO operation2 (id, i_group_id, i_number, d_date, d_createdate, rd_account_id, t_mode, r_payee_id, t_comment," 1823 "rc_unit_id, t_status, t_bookmarked, t_imported, t_template, t_import_id, i_tmp, r_recurrentoperation_id) " 1824 "SELECT id, i_group_id, i_number, d_date, CURRENT_TIMESTAMP, rd_account_id, t_mode, r_payee_id, t_comment," 1825 "rc_unit_id, t_status, t_bookmarked, t_imported, t_template, t_import_id, i_tmp, r_recurrentoperation_id FROM operation") 1826 << QStringLiteral("DROP TABLE IF EXISTS operation") 1827 << QStringLiteral("ALTER TABLE operation2 RENAME TO operation") 1828 << QLatin1String("") 1829 << QStringLiteral("9.3") 1830 << QStringLiteral("9.4") 1831 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition,'#ATT#>=#V1# AND #ATT#<=#V2#','((#ATT#>=#V1# AND #ATT#<=#V2#) OR (#ATT#>=#V2# AND #ATT#<=#V1#))')") 1832 << QLatin1String("") 1833 << QStringLiteral("9.4") 1834 << QStringLiteral("9.5") 1835 << QStringLiteral("ALTER TABLE account ADD COLUMN f_importbalance FLOAT") 1836 << QStringLiteral("UPDATE account SET f_importbalance=NULL") 1837 // ============ SKROOGE 2.4.0 ^^^ 1838 << QLatin1String("") 1839 << QStringLiteral("9.5") 1840 << QStringLiteral("9.6") 1841 << QStringLiteral("CREATE TABLE IF NOT EXISTS vm_budget_tmp( id INT, rc_category_id INT, f_budgeted REAL, i_year INT, i_month INT, f_budgeted_modified REAL, f_transferred REAL, t_including_subcategories TEXT, t_CATEGORY, t_PERIOD, f_CURRENTAMOUNT, t_RULES)") 1842 << QStringLiteral("ALTER TABLE budgetrule ADD COLUMN f_sortorder FLOAT") 1843 << QStringLiteral("UPDATE budgetrule SET f_sortorder=id WHERE f_sortorder IS NULL OR f_sortorder=''") 1844 << QLatin1String("") 1845 << QStringLiteral("9.6") 1846 << QStringLiteral("9.7") 1847 << QStringLiteral("ALTER TABLE budget ADD COLUMN t_modification_reasons TEXT NOT NULL DEFAULT ''") 1848 << QStringLiteral("UPDATE budget SET t_modification_reasons=''") 1849 // ============ SKROOGE 2.8.1 ^^^ 1850 << QLatin1String("") 1851 << QStringLiteral("9.7") 1852 << QStringLiteral("9.8") 1853 << QStringLiteral("UPDATE operation SET d_createdate=d_date WHERE d_createdate=''") 1854 // ============ SKROOGE 2.9.0 ^^^ 1855 << QLatin1String("") 1856 << QStringLiteral("9.8") 1857 << QStringLiteral("9.9") 1858 << QStringLiteral("ALTER TABLE payee ADD r_category_id INTEGER NOT NULL DEFAULT 0") 1859 << QStringLiteral("UPDATE payee SET r_category_id=0") 1860 << QLatin1String("") 1861 << QStringLiteral("9.9") 1862 << QStringLiteral("10.0") 1863 << QStringLiteral("ALTER TABLE payee ADD t_close VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))") 1864 << QStringLiteral("UPDATE payee SET t_close='N'") 1865 << QLatin1String("") 1866 << QStringLiteral("10.0") 1867 << QStringLiteral("10.1") 1868 << QStringLiteral("ALTER TABLE category ADD t_close VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))") 1869 << QStringLiteral("UPDATE category SET t_close='N'") 1870 // ============ SKROOGE 2.11.0 ^^^ 1871 << QLatin1String("") 1872 << QStringLiteral("10.1") 1873 << QStringLiteral("10.2") 1874 << QStringLiteral("CREATE TABLE operation2(" 1875 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1876 "i_group_id INTEGER NOT NULL DEFAULT 0," 1877 "t_number TEXT NOT NULL DEFAULT ''," 1878 "d_date DATE NOT NULL DEFAULT '0000-00-00'," 1879 "d_createdate DATE NOT NULL DEFAULT CURRENT_TIMESTAMP," 1880 "rd_account_id INTEGER NOT NULL," 1881 "t_mode TEXT NOT NULL DEFAULT ''," 1882 "r_payee_id INTEGER NOT NULL DEFAULT 0," 1883 "t_comment TEXT NOT NULL DEFAULT ''," 1884 "rc_unit_id INTEGER NOT NULL," 1885 "t_status VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_status IN ('N', 'P', 'Y'))," 1886 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 1887 "t_imported VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_imported IN ('Y', 'N', 'P', 'T'))," 1888 "t_template VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_template IN ('Y', 'N'))," 1889 "t_import_id TEXT NOT NULL DEFAULT ''," 1890 "i_tmp INTEGER NOT NULL DEFAULT 0," 1891 "r_recurrentoperation_id INTEGER NOT NULL DEFAULT 0)") 1892 << QStringLiteral("INSERT INTO operation2 (id, i_group_id, t_number, d_date, d_createdate, rd_account_id, t_mode, r_payee_id, t_comment," 1893 "rc_unit_id, t_status, t_bookmarked, t_imported, t_template, t_import_id, i_tmp, r_recurrentoperation_id) " 1894 "SELECT id, i_group_id, (CASE WHEN i_number=0 OR i_number IS NULL THEN '' ELSE i_number END), d_date, CURRENT_TIMESTAMP, rd_account_id, t_mode, r_payee_id, t_comment," 1895 "rc_unit_id, t_status, t_bookmarked, t_imported, t_template, t_import_id, i_tmp, r_recurrentoperation_id FROM operation") 1896 << QStringLiteral("DROP TABLE IF EXISTS operation") 1897 << QStringLiteral("ALTER TABLE operation2 RENAME TO operation") 1898 << QLatin1String("") 1899 << QStringLiteral("10.2") 1900 << QStringLiteral("10.3") 1901 << QStringLiteral("UPDATE rule SET t_definition=replace(t_definition, 'i_number', 't_number')") 1902 << QStringLiteral("UPDATE rule SET t_action_definition=replace(t_action_definition, 'i_number', 't_number')") 1903 // ============ SKROOGE 2.11.0 ^^^ 1904 << QLatin1String("") 1905 << QStringLiteral("10.3") 1906 << QStringLiteral("10.4") 1907 << QStringLiteral("ALTER TABLE account ADD COLUMN f_reconciliationbalance FLOAT") 1908 << QStringLiteral("UPDATE account SET f_reconciliationbalance=NULL") 1909 // ============ SKROOGE 2.13.0 ^^^ 1910 << QLatin1String("") 1911 << QStringLiteral("10.4") 1912 << QStringLiteral("10.5") 1913 << QStringLiteral("ALTER TABLE account ADD COLUMN d_importdate DATE") 1914 // ============ SKROOGE 2.26.0 ^^^ 1915 << QLatin1String("") 1916 << QStringLiteral("10.5") 1917 << QStringLiteral("10.6") 1918 << QStringLiteral("DROP TABLE IF EXISTS unitvalue3") 1919 << QStringLiteral("CREATE TABLE unitvalue3(" 1920 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1921 "rd_unit_id INTEGER NOT NULL," 1922 "d_date DATE NOT NULL," 1923 "f_quantity FLOAT NOT NULL)") 1924 << QStringLiteral("INSERT INTO unitvalue3 (id,rd_unit_id,d_date,f_quantity) SELECT id,rd_unit_id,d_date,f_quantity FROM unitvalue") 1925 << QStringLiteral("DROP TABLE IF EXISTS unitvalue") 1926 << QStringLiteral("ALTER TABLE unitvalue3 RENAME TO unitvalue"); 1927 1928 return migrationSteps; 1929 } 1930 1931 SKGError SKGDocumentBank::migrate(bool& oMigrationDone) 1932 { 1933 SKGError err; 1934 SKGTRACEINFUNCRC(5, err) 1935 oMigrationDone = false; 1936 QStringList migrationSteps = getMigationSteps(); 1937 1938 { 1939 int nbSteps = migrationSteps.count(); 1940 SKGBEGINPROGRESSTRANSACTION(*this, "#INTERNAL#" % i18nc("Progression step", "Migrate document"), err, 5) 1941 IFOK(err) { 1942 QString version = getParameter(QStringLiteral("SKG_DB_BANK_VERSION")); 1943 QString initialversion = version; 1944 QString lastversion = QStringLiteral("10.6"); 1945 if (version.isEmpty()) { 1946 SKGTRACEL(10) << "Initial creation" << SKGENDL; 1947 /** 1948 * This constant is used to initialized the data model. 1949 * Rules for attribute name: 1950 * t_xxx for TEXT and VARCHAR 1951 * d_xxx for DATE 1952 * f_xxx for FLOAT 1953 * i_xxx for INTEGER 1954 * r_xxx for a link without constraint 1955 * rc_pointed_table_pointed_attribute_xxx for link on other an object in named "pointed_table" with "pointed_attribute"=id of pointing object 1956 * a constraint will be created without DELETE CASCADE 1957 * rd_pointed_table_pointed_attribute_xxx for link on other an object in named "pointed_table" with "pointed_attribute"=id of pointing object 1958 * a constraint will be created with DELETE CASCADE 1959 * xxx must be in lower case for R/W attributes and in upper case for R/O attributes 1960 * Rules for table name: 1961 * v_yyy for views 1962 */ 1963 QStringList BankInitialDataModel; 1964 BankInitialDataModel 1965 // ================================================================== 1966 // Table unit 1967 << QStringLiteral("CREATE TABLE unit(" 1968 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1969 "t_name TEXT NOT NULL," 1970 "t_symbol TEXT NOT NULL DEFAULT ''," 1971 "t_country TEXT NOT NULL DEFAULT ''," 1972 "t_type VARCHAR(1) NOT NULL DEFAULT 'C' CHECK (t_type IN ('1', '2', 'C', 'S', 'I', 'O'))," 1973 // 1=main currency, 2=secondary currency, C=currencies S=share, I=index, O=object 1974 "t_internet_code TEXT NOT NULL DEFAULT ''," 1975 "i_nbdecimal INT NOT NULL DEFAULT 2," 1976 "rd_unit_id INTEGER NOT NULL DEFAULT 0," 1977 "t_source TEXT NOT NULL DEFAULT ''," 1978 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))" 1979 ")") 1980 1981 // ================================================================== 1982 // Table unitvalue 1983 << QStringLiteral("CREATE TABLE unitvalue(" 1984 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1985 "rd_unit_id INTEGER NOT NULL," 1986 "d_date DATE NOT NULL," 1987 "f_quantity FLOAT NOT NULL)") 1988 1989 // ================================================================== 1990 // Table bank 1991 << QStringLiteral("CREATE TABLE bank (" 1992 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 1993 "t_name TEXT NOT NULL DEFAULT ''," 1994 "t_bank_number TEXT NOT NULL DEFAULT ''," 1995 "t_icon TEXT NOT NULL DEFAULT '')") 1996 1997 // ================================================================== 1998 // Table account 1999 << QStringLiteral("CREATE TABLE account(" 2000 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 2001 "t_name TEXT NOT NULL," 2002 "t_number TEXT NOT NULL DEFAULT ''," 2003 "t_agency_number TEXT NOT NULL DEFAULT ''," 2004 "t_agency_address TEXT NOT NULL DEFAULT ''," 2005 "t_comment TEXT NOT NULL DEFAULT ''," 2006 "t_close VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))," 2007 "t_type VARCHAR(1) NOT NULL DEFAULT 'C' CHECK (t_type IN ('C', 'D', 'A', 'I', 'L', 'W', 'S', 'P', 'O'))," 2008 // C=current D=credit card A=assets (for objects) I=Investment W=Wallet L=Loan S=Saving P=Pension O=other 2009 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 2010 "f_maxamount FLOAT NOT NULL DEFAULT 10000.0," 2011 "t_maxamount_enabled VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))," 2012 "f_minamount FLOAT NOT NULL DEFAULT 0.0," 2013 "t_minamount_enabled VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))," 2014 "d_importdate DATE," 2015 "f_importbalance FLOAT," 2016 "d_reconciliationdate DATE," 2017 "f_reconciliationbalance FLOAT," 2018 "r_account_id INTEGER NOT NULL DEFAULT 0," 2019 "rd_bank_id INTEGER NOT NULL)") 2020 2021 // ================================================================== 2022 // Table interest 2023 << QStringLiteral("CREATE TABLE interest(" 2024 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 2025 "rd_account_id INTEGER NOT NULL," 2026 "d_date DATE NOT NULL," 2027 "f_rate FLOAT NOT NULL CHECK (f_rate>=0)," 2028 "t_income_value_date_mode VARCHAR(1) NOT NULL DEFAULT 'F' CHECK (t_income_value_date_mode IN ('F', '0', '1', '2', '3', '4', '5'))," 2029 "t_expenditure_value_date_mode VARCHAR(1) NOT NULL DEFAULT 'F' CHECK (t_expenditure_value_date_mode IN ('F', '0', '1', '2', '3', '4', '5'))," 2030 "t_base VARCHAR(3) NOT NULL DEFAULT '24' CHECK (t_base IN ('24', '360', '365'))" 2031 ")") 2032 2033 // ================================================================== 2034 // Table category 2035 << "CREATE TABLE category (" 2036 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 2037 "t_name TEXT NOT NULL DEFAULT '' CHECK (t_name NOT LIKE '%" % OBJECTSEPARATOR % "%')," 2038 "t_fullname TEXT," 2039 "t_close VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))," 2040 "rd_category_id INTEGER NOT NULL DEFAULT 0," 2041 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))" 2042 ")" 2043 2044 // ================================================================== 2045 // Table operation 2046 << QStringLiteral("CREATE TABLE operation(" 2047 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 2048 "i_group_id INTEGER NOT NULL DEFAULT 0," 2049 "t_number TEXT NOT NULL DEFAULT ''," 2050 "d_date DATE NOT NULL DEFAULT '0000-00-00'," 2051 "d_createdate DATE NOT NULL DEFAULT CURRENT_TIMESTAMP," 2052 "rd_account_id INTEGER NOT NULL," 2053 "t_mode TEXT NOT NULL DEFAULT ''," 2054 "r_payee_id INTEGER NOT NULL DEFAULT 0," 2055 "t_comment TEXT NOT NULL DEFAULT ''," 2056 "rc_unit_id INTEGER NOT NULL," 2057 "t_status VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_status IN ('N', 'P', 'Y'))," 2058 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 2059 "t_imported VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_imported IN ('Y', 'N', 'P', 'T'))," 2060 "t_template VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_template IN ('Y', 'N'))," 2061 "t_import_id TEXT NOT NULL DEFAULT ''," 2062 "i_tmp INTEGER NOT NULL DEFAULT 0," 2063 "r_recurrentoperation_id INTEGER NOT NULL DEFAULT 0)") 2064 2065 << QStringLiteral("CREATE TABLE operationbalance(" 2066 "r_operation_id INTEGER NOT NULL," 2067 "f_balance FLOAT NOT NULL DEFAULT 0," 2068 "f_balance_entered FLOAT NOT NULL DEFAULT 0)") 2069 2070 // ================================================================== 2071 // Table refund 2072 << QStringLiteral("CREATE TABLE refund (" 2073 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 2074 "t_name TEXT NOT NULL DEFAULT ''," 2075 "t_comment TEXT NOT NULL DEFAULT ''," 2076 "t_close VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N')))") 2077 2078 // ================================================================== 2079 // Table payee 2080 << QStringLiteral("CREATE TABLE payee (" 2081 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 2082 "t_name TEXT NOT NULL DEFAULT ''," 2083 "t_address TEXT NOT NULL DEFAULT ''," 2084 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 2085 "t_close VARCHAR(1) DEFAULT 'N' CHECK (t_close IN ('Y', 'N'))," 2086 "r_category_id INTEGER NOT NULL DEFAULT 0" 2087 ")") 2088 2089 // ================================================================== 2090 // Table suboperation 2091 << QStringLiteral("CREATE TABLE suboperation(" 2092 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 2093 "d_date DATE NOT NULL DEFAULT '0000-00-00'," 2094 "t_comment TEXT NOT NULL DEFAULT ''," 2095 "rd_operation_id INTEGER NOT NULL," 2096 "r_category_id INTEGER NOT NULL DEFAULT 0," 2097 "f_value FLOAT NOT NULL DEFAULT 0.0," 2098 "t_formula TEXT NOT NULL DEFAULT ''," 2099 "i_tmp INTEGER NOT NULL DEFAULT 0," 2100 "r_refund_id INTEGER NOT NULL DEFAULT 0," 2101 "i_order INTEGER NOT NULL DEFAULT 0" 2102 ")") 2103 2104 // ================================================================== 2105 // Table recurrentoperation 2106 << QStringLiteral("CREATE TABLE recurrentoperation (" 2107 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 2108 "d_date DATE NOT NULL DEFAULT '0000-00-00'," 2109 "rd_operation_id INTEGER NOT NULL," 2110 "i_period_increment INTEGER NOT NULL DEFAULT 1 CHECK (i_period_increment>=0)," 2111 "t_period_unit TEXT NOT NULL DEFAULT 'M' CHECK (t_period_unit IN ('D', 'W', 'M', 'Y'))," 2112 "t_auto_write VARCHAR(1) DEFAULT 'Y' CHECK (t_auto_write IN ('Y', 'N'))," 2113 "i_auto_write_days INTEGER NOT NULL DEFAULT 5 CHECK (i_auto_write_days>=0)," 2114 "t_warn VARCHAR(1) DEFAULT 'Y' CHECK (t_warn IN ('Y', 'N'))," 2115 "i_warn_days INTEGER NOT NULL DEFAULT 5 CHECK (i_warn_days>=0)," 2116 "t_times VARCHAR(1) DEFAULT 'N' CHECK (t_times IN ('Y', 'N'))," 2117 "i_nb_times INTEGER NOT NULL DEFAULT 1 CHECK (i_nb_times>=0)" 2118 ")") 2119 2120 // ================================================================== 2121 // Table rule 2122 << QStringLiteral("CREATE TABLE rule (" 2123 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 2124 "t_description TEXT NOT NULL DEFAULT ''," 2125 "t_definition TEXT NOT NULL DEFAULT ''," 2126 "t_action_description TEXT NOT NULL DEFAULT ''," 2127 "t_action_definition TEXT NOT NULL DEFAULT ''," 2128 "t_action_type VARCHAR(1) DEFAULT 'S' CHECK (t_action_type IN ('S', 'U', 'A', 'T'))," 2129 "t_bookmarked VARCHAR(1) NOT NULL DEFAULT 'N' CHECK (t_bookmarked IN ('Y', 'N'))," 2130 "f_sortorder FLOAT" 2131 ")") 2132 2133 // ================================================================== 2134 // Table budget 2135 << QStringLiteral("CREATE TABLE budget (" 2136 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 2137 "rc_category_id INTEGER NOT NULL DEFAULT 0," 2138 "t_including_subcategories TEXT NOT NULL DEFAULT 'N' CHECK (t_including_subcategories IN ('Y', 'N'))," 2139 "f_budgeted FLOAT NOT NULL DEFAULT 0.0," 2140 "f_budgeted_modified FLOAT NOT NULL DEFAULT 0.0," 2141 "t_modification_reasons TEXT NOT NULL DEFAULT ''," 2142 "f_transferred FLOAT NOT NULL DEFAULT 0.0," 2143 "i_year INTEGER NOT NULL DEFAULT 2010," 2144 "i_month INTEGER NOT NULL DEFAULT 0 CHECK (i_month>=0 AND i_month<=12)" 2145 ")") 2146 2147 << QStringLiteral("CREATE TABLE budgetsuboperation(" 2148 "id INTEGER NOT NULL DEFAULT 0," 2149 "id_suboperation INTEGER NOT NULL DEFAULT 0," 2150 "i_priority INTEGER NOT NULL DEFAULT 0)") 2151 2152 << QStringLiteral("CREATE TABLE budgetrule (" 2153 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," 2154 "rc_category_id INTEGER NOT NULL DEFAULT 0," 2155 "t_category_condition TEXT NOT NULL DEFAULT 'Y' CHECK (t_category_condition IN ('Y', 'N'))," 2156 "t_year_condition TEXT NOT NULL DEFAULT 'Y' CHECK (t_year_condition IN ('Y', 'N'))," 2157 "i_year INTEGER NOT NULL DEFAULT 2010," 2158 "i_month INTEGER NOT NULL DEFAULT 0 CHECK (i_month>=0 AND i_month<=12)," 2159 "t_month_condition TEXT NOT NULL DEFAULT 'Y' CHECK (t_month_condition IN ('Y', 'N'))," 2160 "i_condition INTEGER NOT NULL DEFAULT 0 CHECK (i_condition IN (-1,0,1))," 2161 "f_quantity FLOAT NOT NULL DEFAULT 0.0," 2162 "t_absolute TEXT NOT NULL DEFAULT 'Y' CHECK (t_absolute IN ('Y', 'N'))," 2163 "rc_category_id_target INTEGER NOT NULL DEFAULT 0," 2164 "t_category_target TEXT NOT NULL DEFAULT 'Y' CHECK (t_category_target IN ('Y', 'N'))," 2165 "t_rule TEXT NOT NULL DEFAULT 'N' CHECK (t_rule IN ('N', 'C', 'Y'))," 2166 "f_sortorder FLOAT" 2167 ")") 2168 2169 << QStringLiteral("CREATE TABLE vm_budget_tmp(" 2170 "id INT," 2171 "rc_category_id INT," 2172 "f_budgeted REAL," 2173 "i_year INT," 2174 "i_month INT," 2175 "f_budgeted_modified REAL," 2176 "t_modification_reasons TEXT," 2177 "f_transferred REAL," 2178 "t_including_subcategories TEXT," 2179 "t_CATEGORY TEXT," 2180 "t_PERIOD TEXT," 2181 "f_CURRENTAMOUNT REAL," 2182 "t_RULES TEXT)"); 2183 2184 IFOKDO(err, this->executeSqliteOrders(BankInitialDataModel)) 2185 2186 // Set new version 2187 version = lastversion; 2188 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_BANK_VERSION"), version)) 2189 } 2190 2191 if (!err && SKGServices::stringToDouble(version) > SKGServices::stringToDouble(lastversion)) { 2192 err = SKGError(ERR_ABORT, i18nc("Error message", "Impossible to load a document generated by a more recent version")); 2193 } 2194 2195 IFOK(err) { 2196 QString v1; 2197 QString v2; 2198 bool computeCaches = false; 2199 for (int i = 0; !err && i < nbSteps; ++i) { 2200 if (migrationSteps.at(i).isEmpty()) { 2201 ++i; 2202 v1 = migrationSteps.at(i); 2203 ++i; 2204 v2 = migrationSteps.at(i); 2205 if (version == v1) { 2206 SKGTRACEL(10) << "Migration from " << v1 << " to " << v2 << SKGENDL; 2207 for (int j = i + 1; !err && j < nbSteps; ++j) { 2208 const QString& sql = migrationSteps.at(j); 2209 if (!sql.isEmpty()) { 2210 ++i; 2211 IFOKDO(err, this->executeSqliteOrder(sql)) 2212 } else { 2213 break; 2214 } 2215 } 2216 2217 if (v1 == QStringLiteral("4.7") || 2218 v1 == QStringLiteral("5.1") || 2219 v1 == QStringLiteral("5.7") || 2220 v1 == QStringLiteral("5.9") || 2221 v1 == QStringLiteral("8.0") || 2222 v1 == QStringLiteral("8.1") || 2223 v1 == QStringLiteral("10.0") || 2224 v1 == QStringLiteral("10.5")) { 2225 computeCaches = true; 2226 } 2227 2228 // Set new version 2229 version = v2; 2230 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_BANK_VERSION"), version)) 2231 oMigrationDone = true; 2232 } 2233 } 2234 } 2235 IFOKDO(err, stepForward(1, i18nc("Progression step", "Refresh views"))) 2236 2237 if (!err && computeCaches) { 2238 err = refreshViewsIndexesAndTriggers(v1 == QStringLiteral("10.5")); 2239 IFOKDO(err, stepForward(2, i18nc("Progression step", "Computation of balances"))) 2240 IFOKDO(err, computeBalances()) 2241 IFOKDO(err, stepForward(3, i18nc("Progression step", "Computation of budgets"))) 2242 IFOKDO(err, computeBudgetSuboperationLinks()) 2243 IFOKDO(err, stepForward(4)) 2244 } 2245 2246 IFOK(err) { 2247 bool mig = false; 2248 err = SKGDocument::migrate(mig); 2249 oMigrationDone = oMigrationDone || mig; 2250 } else { 2251 err.addError(ERR_FAIL, i18nc("Error message: Could not perform database migration", "Database migration from version %1 to version %2 failed", initialversion, version)); 2252 } 2253 IFOKDO(err, stepForward(5)) 2254 } 2255 } 2256 } 2257 2258 return err; 2259 } 2260 2261 SKGError SKGDocumentBank::dump(int iMode) const 2262 { 2263 SKGError err; 2264 if (Q_LIKELY(getMainDatabase())) { 2265 // dump parameters 2266 SKGTRACE << "=== START DUMP BANK DOCUMENT ===" << SKGENDL; 2267 err = SKGDocument::dump(iMode); 2268 2269 if ((iMode & DUMPUNIT) != 0) { 2270 SKGTRACE << "=== DUMPUNIT (UNITS))===" << SKGENDL; 2271 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM v_unit_display ORDER BY id"))); 2272 2273 SKGTRACE << "=== DUMPUNIT (VALUES) ===" << SKGENDL; 2274 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM v_unitvalue_display ORDER BY rd_unit_id, d_date"))); 2275 } 2276 2277 if ((iMode & DUMPACCOUNT) != 0) { 2278 SKGTRACE << "=== DUMPACCOUNT (BANKS) ===" << SKGENDL; 2279 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM v_bank ORDER BY id"))); 2280 2281 SKGTRACE << "=== DUMPACCOUNT (ACCOUNTS) ===" << SKGENDL; 2282 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM v_account_display ORDER BY rd_bank_id, id"))); 2283 } 2284 2285 if ((iMode & DUMPOPERATION) != 0) { 2286 SKGTRACE << "=== DUMPOPERATION (OPERATIONS) ===" << SKGENDL; 2287 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM v_operation_display_all ORDER BY id"))); 2288 2289 SKGTRACE << "=== DUMPOPERATION (SUBOPERATIONS) ===" << SKGENDL; 2290 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM v_suboperation_display ORDER BY rd_operation_id, id"))); 2291 2292 SKGTRACE << "=== DUMPOPERATION (RECURRENT) ===" << SKGENDL; 2293 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM v_recurrentoperation ORDER BY rd_operation_id, id"))); 2294 2295 SKGTRACE << "=== DUMPOPERATION (TRACKER) ===" << SKGENDL; 2296 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM v_refund ORDER BY id"))); 2297 } 2298 2299 if ((iMode & DUMPPAYEE) != 0) { 2300 SKGTRACE << "=== DUMPOPERATION (PAYEE) ===" << SKGENDL; 2301 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM v_payee ORDER BY id"))); 2302 } 2303 2304 if ((iMode & DUMPCATEGORY) != 0) { 2305 SKGTRACE << "=== DUMPCATEGORY ===" << SKGENDL; 2306 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM v_category_display ORDER BY rd_category_id, id"))); 2307 } 2308 2309 if ((iMode & DUMPBUDGET) != 0) { 2310 SKGTRACE << "=== DUMPBUDGET (BUDGET) ===" << SKGENDL; 2311 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM v_budget_display ORDER BY t_PERIOD"))); 2312 2313 SKGTRACE << "=== DUMPBUDGET (RULES) ===" << SKGENDL; 2314 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM v_budgetrule_display ORDER BY t_absolute DESC, id"))); 2315 } 2316 2317 SKGTRACE << "=== END DUMP BANK DOCUMENT ===" << SKGENDL; 2318 } 2319 2320 return err; 2321 } 2322 2323 SKGError SKGDocumentBank::addOrModifyUnitValue(const QString& iUnitName, QDate iDate, double iValue, SKGUnitValueObject* oValue) const 2324 { 2325 SKGError err; 2326 SKGTRACEINFUNCRC(10, err) 2327 2328 // Creation or update of the unit 2329 bool insertOrUpdate = true; 2330 SKGUnitObject unit(const_cast<SKGDocumentBank*>(this)); 2331 err = unit.setName(iUnitName); 2332 IFOKDO(err, unit.setSymbol(iUnitName)) 2333 if (!unit.exist()) { 2334 insertOrUpdate = false; 2335 IFOKDO(err, unit.save(insertOrUpdate)) 2336 } else { 2337 err = unit.load(); 2338 } 2339 2340 // Creation or update of the value 2341 SKGUnitValueObject value; 2342 IFOKDO(err, unit.addUnitValue(value)) 2343 IFOKDO(err, value.setDate(iDate)) 2344 IFOKDO(err, value.setQuantity(iValue)) 2345 IFOKDO(err, value.save(insertOrUpdate)) 2346 2347 if (oValue != nullptr) { 2348 *oValue = value; 2349 } 2350 2351 // Add error if needed 2352 IFKO(err) err.addError(ERR_FAIL, i18nc("Error message", "Transaction '%1' on '%2' failed", QStringLiteral("SKGDocumentBank::addOrModifyUnitValue"), 2353 iUnitName % " / " % SKGServices::dateToSqlString(iDate) % " / " % SKGServices::doubleToString(iValue))); 2354 return err; 2355 } 2356 2357 SKGServices::SKGUnitInfo SKGDocumentBank::getPrimaryUnit() const 2358 { 2359 SKGServices::SKGUnitInfo output; 2360 2361 output.Name = getCachedValue(QStringLiteral("primaryUnitCache")); 2362 if (output.Name.isEmpty()) { 2363 this->refreshCache(QStringLiteral("unit")); 2364 output.Name = getCachedValue(QStringLiteral("primaryUnitCache")); 2365 } 2366 output.Value = 1; 2367 output.Symbol = getCachedValue(QStringLiteral("primaryUnitSymbolCache")); 2368 output.NbDecimal = SKGServices::stringToInt(getCachedValue(QStringLiteral("primaryUnitDecimalCache"))); 2369 2370 return output; 2371 } 2372 2373 SKGServices::SKGUnitInfo SKGDocumentBank::getSecondaryUnit() const 2374 { 2375 SKGServices::SKGUnitInfo output; 2376 2377 output.Name = getCachedValue(QStringLiteral("secondaryUnitCache")); 2378 if (output.Name.isEmpty()) { 2379 this->refreshCache(QStringLiteral("unit")); 2380 output.Name = getCachedValue(QStringLiteral("secondaryUnitCache")); 2381 } 2382 output.Symbol = getCachedValue(QStringLiteral("secondaryUnitSymbolCache")); 2383 output.Value = SKGServices::stringToDouble(getCachedValue(QStringLiteral("secondaryUnitValueCache"))); 2384 output.NbDecimal = SKGServices::stringToInt(getCachedValue(QStringLiteral("secondaryUnitDecimalCache"))); 2385 2386 return output; 2387 } 2388 2389 QString SKGDocumentBank::formatPrimaryMoney(double iValue, int iForcedNbOfDigit) const 2390 { 2391 auto unit = getPrimaryUnit(); 2392 if (iForcedNbOfDigit != -1) { 2393 unit.NbDecimal = iForcedNbOfDigit; 2394 } 2395 return formatMoney(iValue, unit, false); 2396 } 2397 2398 QString SKGDocumentBank::formatSecondaryMoney(double iValue, int iForcedNbOfDigit) const 2399 { 2400 auto unit = getSecondaryUnit(); 2401 if (iForcedNbOfDigit != -1) { 2402 unit.NbDecimal = iForcedNbOfDigit; 2403 } 2404 return formatMoney(iValue, unit, false); 2405 } 2406 2407 QString SKGDocumentBank::getCategoryForPayee(const QString& iPayee, bool iComputeAllPayees) const 2408 { 2409 SKGTRACEINFUNC(10) 2410 QString output; 2411 QString key = "categoryForPayee-" + iPayee; 2412 output = getCachedValue(key); 2413 if (output.isEmpty()) { 2414 QString sql = "SELECT * FROM (SELECT 9999, t_name, t_CATEGORY FROM v_payee WHERE t_CATEGORY!='' UNION " 2415 "SELECT COUNT(1), t_name, t_CATEGORY from (SELECT payee.t_name, t_CATEGORY FROM payee, v_suboperation_display sop, v_operation op WHERE r_payee_id=payee.id AND sop.rd_operation_id=op.ID) GROUP BY t_name, t_CATEGORY) ORDER BY 2, 1 DESC"; 2416 if (!iComputeAllPayees) { 2417 sql = "SELECT 9999, t_name, t_CATEGORY FROM v_payee WHERE t_name='" % SKGServices::stringToSqlString(iPayee) % "' AND t_CATEGORY!='' " 2418 "UNION ALL SELECT COUNT(1),t_PAYEE, t_REALCATEGORY FROM (SELECT t_PAYEE, t_REALCATEGORY, d_date FROM v_suboperation_consolidated " 2419 "WHERE t_PAYEE='" % SKGServices::stringToSqlString(iPayee) % "' ORDER BY d_date DESC LIMIT 50) GROUP BY t_REALCATEGORY ORDER BY COUNT(1) DESC"; 2420 } 2421 SKGStringListList result; 2422 executeSelectSqliteOrder(sql, result); 2423 int nb = result.count(); 2424 if (nb >= 1) { 2425 QString currentComputeKey; 2426 QString currentCat; 2427 int currentCount = 0; 2428 int sum = 0; 2429 for (int i = 1 ; i < nb; ++i) { 2430 int count = SKGServices::stringToInt(result.at(i).at(0)); 2431 QString newComputeKey = "categoryForPayee-" + result.at(i).at(1); 2432 if (newComputeKey != currentComputeKey) { 2433 // The computed key change 2434 if (!currentComputeKey.isEmpty()) { 2435 // Store the automatic category of the key 2436 if (sum > 0 && 100 * currentCount / sum > 70) { 2437 addValueInCache(currentComputeKey, currentCat); 2438 if (currentComputeKey == key) { 2439 output = currentCat; 2440 } 2441 } 2442 } 2443 2444 // Start to compute the new sum and keep this category 2445 currentCount = count; 2446 currentCat = result.at(i).at(2); 2447 currentComputeKey = newComputeKey; 2448 2449 sum = count; 2450 } else { 2451 // Continue to compute the sum 2452 sum += count; 2453 } 2454 } 2455 2456 // Compute the last 2457 if (!currentComputeKey.isEmpty()) { 2458 // Store the automatic category of the key 2459 if (sum > 0 && 100 * currentCount / sum > 70) { 2460 addValueInCache(currentComputeKey, currentCat); 2461 if (currentComputeKey == key) { 2462 output = currentCat; 2463 } 2464 } 2465 } 2466 } 2467 } 2468 2469 return output; 2470 } 2471 2472 void SKGDocumentBank::refreshCache(const QString& iTable) const 2473 { 2474 if (iTable == QStringLiteral("unit") || iTable.isEmpty()) { 2475 SKGTRACEINFUNC(10) 2476 SKGStringListList result; 2477 executeSelectSqliteOrder(QStringLiteral("SELECT t_name, t_symbol, i_nbdecimal FROM unit WHERE t_type='1'"), result); 2478 if (result.size() == 2) { 2479 addValueInCache(QStringLiteral("primaryUnitCache"), result.at(1).at(0)); 2480 addValueInCache(QStringLiteral("primaryUnitSymbolCache"), result.at(1).at(1)); 2481 addValueInCache(QStringLiteral("primaryUnitDecimalCache"), result.at(1).at(2)); 2482 } else { 2483 addValueInCache(QStringLiteral("primaryUnitCache"), QLatin1String("")); 2484 addValueInCache(QStringLiteral("primaryUnitSymbolCache"), QLatin1String("")); 2485 addValueInCache(QStringLiteral("primaryUnitDecimalCache"), QStringLiteral("2")); 2486 } 2487 2488 executeSelectSqliteOrder(QStringLiteral("SELECT t_name, t_symbol, f_CURRENTAMOUNT, i_nbdecimal FROM v_unit WHERE t_type='2'"), result); 2489 if (result.size() == 2) { 2490 addValueInCache(QStringLiteral("secondaryUnitCache"), result.at(1).at(0)); 2491 addValueInCache(QStringLiteral("secondaryUnitSymbolCache"), result.at(1).at(1)); 2492 addValueInCache(QStringLiteral("secondaryUnitValueCache"), result.at(1).at(2)); 2493 addValueInCache(QStringLiteral("secondaryUnitDecimalCache"), result.at(1).at(3)); 2494 } else { 2495 addValueInCache(QStringLiteral("secondaryUnitCache"), QLatin1String("")); 2496 addValueInCache(QStringLiteral("secondaryUnitSymbolCache"), QLatin1String("")); 2497 addValueInCache(QStringLiteral("secondaryUnitValueCache"), QStringLiteral("1")); 2498 addValueInCache(QStringLiteral("secondaryUnitDecimalCache"), QStringLiteral("2")); 2499 } 2500 } 2501 SKGDocument::refreshCache(iTable); 2502 } 2503 2504 SKGError SKGDocumentBank::addOrModifyAccount(const QString& iName, const QString& iNumber, const QString& iBankName) const 2505 { 2506 SKGError err; 2507 SKGTRACEINFUNCRC(10, err) 2508 2509 // Creation or update of the bank 2510 2511 SKGBankObject bank(const_cast<SKGDocumentBank*>(this)); 2512 err = bank.setName(iBankName); 2513 IFOKDO(err, bank.save()) 2514 2515 // Creation or update of the account 2516 SKGAccountObject account; 2517 IFOKDO(err, bank.addAccount(account)) 2518 IFOKDO(err, account.setAttribute(QStringLiteral("rd_bank_id"), SKGServices::intToString(bank.getID()))) 2519 IFOKDO(err, account.setName(iName)) 2520 IFOKDO(err, account.setAttribute(QStringLiteral("t_number"), iNumber)) 2521 IFOKDO(err, account.save()) 2522 2523 IFKO(err) err.addError(ERR_FAIL, i18nc("Error message", "Transaction '%1' on '%2' failed", QStringLiteral("SKGDocumentBank::addOrModifyAccount"), iName)); 2524 return err; 2525 } 2526 2527 QString SKGDocumentBank::getFileExtension() const 2528 { 2529 return QStringLiteral("skg"); 2530 } 2531 2532 QString SKGDocumentBank::getDocumentHeader() const 2533 { 2534 return QStringLiteral("SKROOGE"); 2535 } 2536 2537 SKGDocument::SKGModelTemplateList SKGDocumentBank::getDisplaySchemas(const QString& iRealTable) const 2538 { 2539 SKGModelTemplateList listSchema; 2540 listSchema.reserve(10); 2541 2542 // Get properties 2543 QStringList properties; 2544 QString tableForProperties = iRealTable; 2545 if (tableForProperties == QStringLiteral("suboperation")) { 2546 tableForProperties = QStringLiteral("operation"); 2547 } 2548 this->getDistinctValues(QStringLiteral("parameters"), QStringLiteral("t_name"), "(t_uuid_parent like '%-" % tableForProperties % "' OR t_uuid_parent like '%-sub" % tableForProperties % "') AND t_name NOT LIKE 'SKG_%'", properties); 2549 2550 // Build property schema 2551 QString propSchema; 2552 int nb = properties.count(); 2553 for (int i = 0; i < nb; ++i) { 2554 propSchema += ";p_" % properties.at(i) % "|N"; 2555 } 2556 2557 // Build schemas 2558 if (iRealTable == QStringLiteral("operation") || iRealTable == QStringLiteral("suboperation")) { 2559 SKGModelTemplate def; 2560 def.id = QStringLiteral("default"); 2561 def.name = i18nc("Noun, the default value of an item", "Default"); 2562 def.icon = QStringLiteral("edit-undo"); 2563 def.schema = "d_date;d_DATEWEEK|N;d_DATEMONTH|N;d_DATEQUARTER|N;d_DATESEMESTER|N;d_DATEYEAR|N;i_NBRECURRENT;t_bookmarked;t_ACCOUNT;t_TOACCOUNT|N;t_number;t_mode;t_PAYEE;t_comment;t_REALCOMMENT;t_CATEGORY;t_REALCATEGORY;t_status;" 2564 "f_REALCURRENTAMOUNT;f_REALCURRENTAMOUNT_EXPENSE|N;f_REALCURRENTAMOUNT_INCOME|N;" 2565 "f_CURRENTAMOUNT;f_CURRENTAMOUNT_EXPENSE|N;f_CURRENTAMOUNT_INCOME|N;" 2566 "f_QUANTITY|N;f_QUANTITY_EXPENSE|N;f_QUANTITY_INCOME|N;f_REALQUANTITY|N;f_REALQUANTITY_EXPENSE|N;f_REALQUANTITY_INCOME|N;t_UNIT|N;" 2567 "t_imported|N;t_REALREFUND|N;t_REFUND|N;t_REFUNDDISPLAY|N" 2568 ";f_BALANCE|N;f_BALANCE_ENTERED|N;d_createdate|N;i_OPID|N" % propSchema; 2569 listSchema.push_back(def); 2570 2571 SKGModelTemplate minimum; 2572 minimum.id = QStringLiteral("minimum"); 2573 minimum.name = i18nc("Noun, the minimum value of an item", "Minimum"); 2574 minimum.icon = QLatin1String(""); 2575 minimum.schema = "d_date;d_DATEWEEK|N;d_DATEMONTH|N;d_DATEQUARTER|N;d_DATESEMESTER|N;d_DATEYEAR|N;i_NBRECURRENT|N;t_bookmarked|N;t_ACCOUNT;t_TOACCOUNT|N;t_number|N;t_mode|N;t_PAYEE|N;t_comment|N;t_REALCOMMENT|N;t_CATEGORY|N;t_REALCATEGORY|N;t_status;" 2576 "f_REALCURRENTAMOUNT;f_REALCURRENTAMOUNT_EXPENSE|N;f_REALCURRENTAMOUNT_INCOME|N;" 2577 "f_CURRENTAMOUNT;f_CURRENTAMOUNT_EXPENSE|N;f_CURRENTAMOUNT_INCOME|N;" 2578 "f_QUANTITY|N;f_QUANTITY_EXPENSE|N;f_QUANTITY_INCOME|N;f_REALQUANTITY|N;f_REALQUANTITY_EXPENSE|N;f_REALQUANTITY_INCOME|N;t_UNIT|N;" 2579 "t_imported|N;t_REALREFUND|N;t_REFUND|N;t_REFUNDDISPLAY|N" 2580 ";f_BALANCE|N;f_BALANCE_ENTERED|N;d_createdate|N;i_OPID|N" % propSchema; 2581 listSchema.push_back(minimum); 2582 2583 SKGModelTemplate doubleColumn; 2584 doubleColumn.id = QStringLiteral("doublecolumn"); 2585 doubleColumn.name = i18nc("Noun", "Amount in 2 columns"); 2586 doubleColumn.icon = QLatin1String(""); 2587 doubleColumn.schema = "d_date;d_DATEWEEK|N;d_DATEMONTH|N;d_DATEQUARTER|N;d_DATESEMESTER|N;d_DATEYEAR|N;i_NBRECURRENT;t_bookmarked;t_ACCOUNT;t_TOACCOUNT|N;t_number;t_mode;t_PAYEE;t_comment;t_REALCOMMENT;t_CATEGORY;t_REALCATEGORY;t_status;" 2588 "f_REALCURRENTAMOUNT|N;f_REALCURRENTAMOUNT_EXPENSE|Y;f_REALCURRENTAMOUNT_INCOME|Y;" 2589 "f_CURRENTAMOUNT|N;f_CURRENTAMOUNT_EXPENSE|Y;f_CURRENTAMOUNT_INCOME|Y;" 2590 "f_QUANTITY|N;f_QUANTITY_EXPENSE|N;f_QUANTITY_INCOME|N;f_REALQUANTITY|N;f_REALQUANTITY_EXPENSE|N;f_REALQUANTITY_INCOME|N;t_UNIT|N;" 2591 "t_imported|N;t_REALREFUND|N;t_REFUND|N;t_REFUNDDISPLAY|N" 2592 ";f_BALANCE|N;f_BALANCE_ENTERED|N;d_createdate|N;i_OPID|N" % propSchema; 2593 listSchema.push_back(doubleColumn); 2594 2595 SKGModelTemplate amountEntered; 2596 amountEntered.id = QStringLiteral("amountentered"); 2597 amountEntered.name = i18nc("Noun", "Amount entered"); 2598 amountEntered.icon = QLatin1String(""); 2599 amountEntered.schema = "d_date;d_DATEWEEK|N;d_DATEMONTH|N;d_DATEQUARTER|N;d_DATESEMESTER|N;d_DATEYEAR|N;i_NBRECURRENT;t_bookmarked;t_ACCOUNT;t_TOACCOUNT|N;t_number;t_mode;t_PAYEE;t_comment;t_REALCOMMENT;t_CATEGORY;t_REALCATEGORY;t_status;" 2600 "f_REALCURRENTAMOUNT|N;f_REALCURRENTAMOUNT_EXPENSE|N;f_REALCURRENTAMOUNT_INCOME|N;" 2601 "f_CURRENTAMOUNT|N;f_CURRENTAMOUNT_EXPENSE|N;f_CURRENTAMOUNT_INCOME|N;" 2602 "f_QUANTITY|Y;f_QUANTITY_EXPENSE|N;f_QUANTITY_INCOME|N;f_REALQUANTITY|Y;f_REALQUANTITY_EXPENSE|N;f_REALQUANTITY_INCOME|N;t_UNIT|N;" 2603 "t_imported|N;t_REALREFUND|N;t_REFUND|N;t_REFUNDDISPLAY|N" 2604 ";f_BALANCE|N;f_BALANCE_ENTERED|N;d_createdate|N;i_OPID|N" % propSchema; 2605 listSchema.push_back(amountEntered); 2606 2607 SKGModelTemplate doubleColumnEntered; 2608 doubleColumnEntered.id = QStringLiteral("doublecolumnentered"); 2609 doubleColumnEntered.name = i18nc("Noun", "Amount entered in 2 columns"); 2610 doubleColumnEntered.icon = QLatin1String(""); 2611 doubleColumnEntered.schema = "d_date;d_DATEWEEK|N;d_DATEMONTH|N;d_DATEQUARTER|N;d_DATESEMESTER|N;d_DATEYEAR|N;i_NBRECURRENT;t_bookmarked;t_ACCOUNT;t_TOACCOUNT|N;t_number;t_mode;t_PAYEE;t_comment;t_REALCOMMENT;t_CATEGORY;t_REALCATEGORY;t_status;" 2612 "f_REALCURRENTAMOUNT|N;f_REALCURRENTAMOUNT_EXPENSE|N;f_REALCURRENTAMOUNT_INCOME|N;" 2613 "f_CURRENTAMOUNT|N;f_CURRENTAMOUNT_EXPENSE|N;f_CURRENTAMOUNT_INCOME|N;" 2614 "f_QUANTITY|N;f_QUANTITY_EXPENSE|Y;f_QUANTITY_INCOME|Y;f_REALQUANTITY|N;f_REALQUANTITY_EXPENSE|Y;f_REALQUANTITY_INCOME|Y;t_UNIT|N;" 2615 "t_imported|N;t_REALREFUND|N;t_REFUND|N;t_REFUNDDISPLAY|N" 2616 ";f_BALANCE|N;f_BALANCE_ENTERED|N;d_createdate|N;i_OPID|N" % propSchema; 2617 listSchema.push_back(doubleColumnEntered); 2618 } else if (iRealTable == QStringLiteral("recurrentoperation")) { 2619 SKGModelTemplate def; 2620 def.id = QStringLiteral("default"); 2621 def.name = i18nc("Noun, the default value of an item", "Default"); 2622 def.icon = QStringLiteral("edit-undo"); 2623 def.schema = "d_date;t_PERIODNLS;i_nb_times;i_auto_write_days;i_warn_days;t_ACCOUNT;t_number;t_mode;t_PAYEE;t_comment;t_CATEGORY;" 2624 "t_status;f_CURRENTAMOUNT" % propSchema; 2625 listSchema.push_back(def); 2626 2627 SKGModelTemplate minimum; 2628 minimum.id = QStringLiteral("minimum"); 2629 minimum.name = i18nc("Noun, the minimum value of an item", "Minimum"); 2630 minimum.icon = QLatin1String(""); 2631 minimum.schema = "d_date;t_PERIODNLS;i_nb_times;i_auto_write_days;i_warn_days;t_ACCOUNT;t_number|N;t_mode|N;t_PAYEE;t_comment|N;t_CATEGORY|N;" 2632 "t_status;f_CURRENTAMOUNT" % propSchema; 2633 listSchema.push_back(minimum); 2634 } else if (iRealTable == QStringLiteral("account")) { 2635 SKGModelTemplate def; 2636 def.id = QStringLiteral("default"); 2637 def.name = i18nc("Noun, the default value of an item", "Default"); 2638 def.icon = QStringLiteral("edit-undo"); 2639 def.schema = "t_BANK;t_close;t_bookmarked;t_name;t_TYPENLS;t_BANK_NUMBER;t_agency_number;t_number;t_agency_address;t_comment;f_CURRENTAMOUNT;f_QUANTITY|N;f_TODAYAMOUNT|N;f_CHECKED;f_COMING_SOON;f_importbalance|N;d_importdate|N;f_reconciliationbalance|N;d_reconciliationdate|N;i_NBOPERATIONS;f_RATE|N" % propSchema; 2640 listSchema.push_back(def); 2641 2642 SKGModelTemplate minimum; 2643 minimum.id = QStringLiteral("minimum"); 2644 minimum.name = i18nc("Noun, the minimum value of an item", "Minimum"); 2645 minimum.icon = QLatin1String(""); 2646 minimum.schema = "t_BANK;t_close;t_bookmarked|N;t_name;t_TYPENLS|N;t_BANK_NUMBER|N;t_agency_number|N;t_number|N;t_agency_address|N;t_comment|N;f_CURRENTAMOUNT|N;f_QUANTITY|N;f_TODAYAMOUNT|N;f_CHECKED|N;f_COMING_SOON|N;f_importbalance|N;d_importdate|N;f_reconciliationbalance|N;d_reconciliationdate|N;i_NBOPERATIONS|N;f_RATE|N" % propSchema; 2647 listSchema.push_back(minimum); 2648 2649 SKGModelTemplate intermediate; 2650 intermediate.id = QStringLiteral("intermediate"); 2651 intermediate.name = i18nc("Noun, an intermediate value between two extremums", "Intermediate"); 2652 intermediate.icon = QLatin1String(""); 2653 intermediate.schema = "t_BANK;t_close;t_bookmarked;t_name;t_TYPENLS|N;t_BANK_NUMBER|N;t_agency_number|N;t_number|N;t_agency_address|N;t_comment|N;f_CURRENTAMOUNT;f_QUANTITY|N;f_TODAYAMOUNT|N,f_CHECKED;f_COMING_SOON;f_importbalance|N;d_importdate|N;f_reconciliationbalance|N;d_reconciliationdate|N;i_NBOPERATIONS|N;f_RATE|N" % propSchema; 2654 listSchema.push_back(intermediate); 2655 } else if (iRealTable == QStringLiteral("category")) { 2656 SKGModelTemplate def; 2657 def.id = QStringLiteral("default"); 2658 def.name = i18nc("Noun, the default value of an item", "Default"); 2659 def.icon = QStringLiteral("edit-undo"); 2660 def.schema = "t_name;t_close;t_bookmarked;i_NBOPERATIONS;f_REALCURRENTAMOUNT;i_SUMNBOPERATIONS;f_SUMCURRENTAMOUNT" % propSchema; 2661 listSchema.push_back(def); 2662 2663 SKGModelTemplate minimum; 2664 minimum.id = QStringLiteral("minimum"); 2665 minimum.name = i18nc("Noun, the minimum value of an item", "Minimum"); 2666 minimum.icon = QLatin1String(""); 2667 minimum.schema = "t_name;t_close|N;t_bookmarked;i_NBOPERATIONS|N;f_REALCURRENTAMOUNT|N;i_SUMNBOPERATIONS|N;f_SUMCURRENTAMOUNT|N" % propSchema; 2668 listSchema.push_back(minimum); 2669 2670 SKGModelTemplate op; 2671 op.id = QStringLiteral("with_operations"); 2672 op.name = i18nc("Noun", "With transactions"); 2673 op.icon = QLatin1String(""); 2674 op.schema = "t_name;t_close;t_bookmarked;i_NBOPERATIONS;f_REALCURRENTAMOUNT;i_SUMNBOPERATIONS|N;f_SUMCURRENTAMOUNT|N" % propSchema; 2675 listSchema.push_back(op); 2676 2677 SKGModelTemplate op2; 2678 op2.id = QStringLiteral("with_cumulative_operations"); 2679 op2.name = i18nc("Noun", "With cumulative transactions"); 2680 op2.icon = QLatin1String(""); 2681 op2.schema = "t_name;t_close;t_bookmarked;i_NBOPERATIONS|N;f_REALCURRENTAMOUNT|N;i_SUMNBOPERATIONS;f_SUMCURRENTAMOUNT" % propSchema; 2682 listSchema.push_back(op2); 2683 } else if (iRealTable == QStringLiteral("unit")) { 2684 SKGModelTemplate def; 2685 def.id = QStringLiteral("default"); 2686 def.name = i18nc("Noun, the default value of an item", "Default"); 2687 def.icon = QStringLiteral("edit-undo"); 2688 def.schema = "t_name;t_symbol;t_bookmarked;t_country;t_TYPENLS;t_source;t_internet_code;f_CURRENTAMOUNT;f_QUANTITYOWNED;f_AMOUNTOWNED;i_nbdecimal;t_UNIT;d_MAXDATE|N" % propSchema; 2689 listSchema.push_back(def); 2690 2691 SKGModelTemplate minimum; 2692 minimum.id = QStringLiteral("minimum"); 2693 minimum.name = i18nc("Noun, the minimum value of an item", "Minimum"); 2694 minimum.icon = QLatin1String(""); 2695 minimum.schema = "t_name;t_symbol;t_bookmarked|N;t_country|N;t_TYPENLS;t_source|N;t_internet_code|N;f_CURRENTAMOUNT|N;f_QUANTITYOWNED|N;f_AMOUNTOWNED|N;i_nbdecimal|N;t_UNIT|N;d_MAXDATE|N" % propSchema; 2696 listSchema.push_back(minimum); 2697 } else if (iRealTable == QStringLiteral("unitvalue")) { 2698 SKGModelTemplate def; 2699 def.id = QStringLiteral("default"); 2700 def.name = i18nc("Noun, the default value of an item", "Default"); 2701 def.icon = QStringLiteral("edit-undo"); 2702 def.schema = "d_date;f_quantity;t_UNIT|N;f_AMOUNTOWNED|N" % propSchema; 2703 listSchema.push_back(def); 2704 } else if (iRealTable == QStringLiteral("refund")) { 2705 SKGModelTemplate def; 2706 def.id = QStringLiteral("default"); 2707 def.name = i18nc("Noun, the default value of an item", "Default"); 2708 def.icon = QStringLiteral("edit-undo"); 2709 def.schema = "t_name;t_comment;t_close;d_FIRSTDATE;d_LASTDATE;f_CURRENTAMOUNT" % propSchema; 2710 listSchema.push_back(def); 2711 2712 SKGModelTemplate minimum; 2713 minimum.id = QStringLiteral("minimum"); 2714 minimum.name = i18nc("Noun, the minimum value of an item", "Minimum"); 2715 minimum.icon = QLatin1String(""); 2716 minimum.schema = "t_name;t_comment|N;t_close;d_FIRSTDATE|N;d_LASTDATE|N;f_CURRENTAMOUNT" % propSchema; 2717 listSchema.push_back(minimum); 2718 } else if (iRealTable == QStringLiteral("payee")) { 2719 SKGModelTemplate def; 2720 def.id = QStringLiteral("default"); 2721 def.name = i18nc("Noun, the default value of an item", "Default"); 2722 def.icon = QStringLiteral("edit-undo"); 2723 def.schema = "t_name;t_close;t_bookmarked;t_address;i_NBOPERATIONS|N;f_CURRENTAMOUNT;t_CATEGORY|N" % propSchema; 2724 listSchema.push_back(def); 2725 2726 SKGModelTemplate minimum; 2727 minimum.id = QStringLiteral("minimum"); 2728 minimum.name = i18nc("Noun, the minimum value of an item", "Minimum"); 2729 minimum.icon = QLatin1String(""); 2730 minimum.schema = "t_name;t_close|N;t_bookmarked;t_address|N;i_NBOPERATIONS|N;f_CURRENTAMOUNT;t_CATEGORY|N" % propSchema; 2731 listSchema.push_back(minimum); 2732 } else if (iRealTable == QStringLiteral("rule")) { 2733 SKGModelTemplate def; 2734 def.id = QStringLiteral("default"); 2735 def.name = i18nc("Noun, the default value of an item", "Default"); 2736 def.icon = QStringLiteral("edit-undo"); 2737 def.schema = "i_ORDER;t_bookmarked;t_action_type;t_description;t_action_description" % propSchema; 2738 listSchema.push_back(def); 2739 } else if (iRealTable == QStringLiteral("interest")) { 2740 SKGModelTemplate def; 2741 def.id = QStringLiteral("default"); 2742 def.name = i18nc("Noun, the default value of an item", "Default"); 2743 def.icon = QStringLiteral("edit-undo"); 2744 def.schema = "d_date;f_rate;t_income_value_date_mode;t_expenditure_value_date_mode;t_base" % propSchema; 2745 listSchema.push_back(def); 2746 2747 SKGModelTemplate minimum; 2748 minimum.id = QStringLiteral("minimum"); 2749 minimum.name = i18nc("Noun, the minimum value of an item", "Minimum"); 2750 minimum.icon = QLatin1String(""); 2751 minimum.schema = "d_date;f_rate;t_income_value_date_mode|N;t_expenditure_value_date_mode|N;t_base|N" % propSchema; 2752 listSchema.push_back(minimum); 2753 } else if (iRealTable == QStringLiteral("interest_result")) { 2754 SKGModelTemplate def; 2755 def.id = QStringLiteral("default"); 2756 def.name = i18nc("Noun, the default value of an item", "Default"); 2757 def.icon = QStringLiteral("edit-undo"); 2758 def.schema = "d_date;d_valuedate;t_comment;f_currentamount;f_coef;f_rate;f_annual_interest;f_accrued_interest" % propSchema; 2759 listSchema.push_back(def); 2760 2761 SKGModelTemplate minimum; 2762 minimum.id = QStringLiteral("minimum"); 2763 minimum.name = i18nc("Noun, the minimum value of an item", "Minimum"); 2764 minimum.icon = QLatin1String(""); 2765 minimum.schema = "d_date;d_valuedate|N;t_comment|N;f_currentamount|N;f_coef|N;f_rate;f_annual_interest;f_accrued_interest|N" % propSchema; 2766 listSchema.push_back(minimum); 2767 } else if (iRealTable == QStringLiteral("budget")) { 2768 SKGModelTemplate def; 2769 def.id = QStringLiteral("default"); 2770 def.name = i18nc("Noun, the default value of an item", "Default"); 2771 def.icon = QStringLiteral("edit-undo"); 2772 def.schema = "t_CATEGORY;t_PERIOD;i_year|N;i_month|N;f_budgeted;f_budgeted_modified;f_CURRENTAMOUNT;f_DELTABEFORETRANSFER|N;t_RULES;f_DELTA" % propSchema; 2773 listSchema.push_back(def); 2774 2775 SKGModelTemplate minimum; 2776 minimum.id = QStringLiteral("minimum"); 2777 minimum.name = i18nc("Noun, the minimum value of an item", "Minimum"); 2778 minimum.icon = QLatin1String(""); 2779 minimum.schema = "t_CATEGORY;t_PERIOD;i_year|N;i_month|N;f_budgeted|N;f_budgeted_modified;f_CURRENTAMOUNT;f_DELTABEFORETRANSFER;t_RULES|N;f_DELTA|N" % propSchema; 2780 listSchema.push_back(minimum); 2781 } else if (iRealTable == QStringLiteral("budgetrule")) { 2782 SKGModelTemplate def; 2783 def.id = QStringLiteral("default"); 2784 def.name = i18nc("Noun, the default value of an item", "Default"); 2785 def.icon = QStringLiteral("edit-undo"); 2786 def.schema = "i_ORDER;t_CATEGORYCONDITION;i_year;i_month;t_WHENNLS;t_WHATNLS;t_RULENLS;t_CATEGORY" % propSchema; 2787 listSchema.push_back(def); 2788 } else { 2789 listSchema = SKGDocument::getDisplaySchemas(iRealTable); 2790 } 2791 2792 return listSchema; 2793 } 2794 2795 QString SKGDocumentBank::getIconName(const QString& iString) const 2796 { 2797 QString att = iString.toLower(); 2798 2799 if (att.endsWith(QLatin1String("t_bookmarked"))) { 2800 return QStringLiteral("bookmarks"); 2801 } 2802 if (att.endsWith(QLatin1String("f_balance")) || 2803 att.endsWith(QLatin1String("f_balance_entered")) || 2804 att.endsWith(QLatin1String("f_reconciliationbalance"))) { 2805 return QStringLiteral("office-chart-line"); 2806 } 2807 if (att.endsWith(QLatin1String("i_nbrecurrent"))) { 2808 return QStringLiteral("chronometer"); 2809 } 2810 if (att.endsWith(QLatin1String("t_status")) || 2811 att.endsWith(QLatin1String("f_checked")) || 2812 att.endsWith(QLatin1String("f_coming_soon")) || 2813 att.endsWith(QLatin1String("d_reconciliationdate"))) { 2814 return QStringLiteral("dialog-ok"); 2815 } 2816 if (att.endsWith(QLatin1String("t_close"))) { 2817 return QStringLiteral("window-close"); 2818 } 2819 if (att.endsWith(QLatin1String("t_categorycondition")) || 2820 att.endsWith(QLatin1String("t_category")) || 2821 att.endsWith(QLatin1String("t_realcategory"))) { 2822 return QStringLiteral("view-categories"); 2823 } 2824 if (att.endsWith(QLatin1String("t_symbol"))) { 2825 return QStringLiteral("taxes-finances"); 2826 } 2827 if (att.endsWith(QLatin1String("t_typeexpensenls"))) { 2828 return QStringLiteral("skrooge_type"); 2829 } 2830 if (att.endsWith(QLatin1String("t_typenls"))) { 2831 if (att.contains(QStringLiteral("v_unit"))) { 2832 return QStringLiteral("view-bank-account-savings"); 2833 } 2834 if (att.contains(QStringLiteral("v_account"))) { 2835 return QStringLiteral("skrooge_credit_card"); 2836 } 2837 } 2838 if (att.endsWith(QLatin1String("t_unit")) || 2839 att.endsWith(QLatin1String("t_unittype"))) { 2840 return QStringLiteral("taxes-finances"); 2841 } 2842 if (att.endsWith(QLatin1String("f_value")) || 2843 att.endsWith(QLatin1String("f_currentamount")) || 2844 att.endsWith(QLatin1String("f_todayamount")) || 2845 att.endsWith(QLatin1String("f_sumcurrentamount")) || 2846 att.endsWith(QLatin1String("quantity")) || 2847 att.endsWith(QLatin1String("f_realcurrentamount"))) { 2848 return QStringLiteral("skrooge_type"); 2849 } 2850 if (att.endsWith(QLatin1String("_expense"))) { 2851 return QStringLiteral("list-remove"); 2852 } 2853 if (att.endsWith(QLatin1String("_income")) || 2854 att.endsWith(QLatin1String("f_annual_interest")) || 2855 att.endsWith(QLatin1String("f_accrued_interest"))) { 2856 return QStringLiteral("list-add"); 2857 } 2858 if (att.endsWith(QLatin1String("t_description"))) { 2859 return QStringLiteral("edit-find"); 2860 } 2861 if (att.endsWith(QLatin1String("t_action_description"))) { 2862 return QStringLiteral("system-run"); 2863 } 2864 if (att.endsWith(QLatin1String("t_imported")) || 2865 att.endsWith(QLatin1String("f_importbalance")) || 2866 att.endsWith(QLatin1String("d_importdate"))) { 2867 return QStringLiteral("utilities-file-archiver"); 2868 } 2869 if (att.endsWith(QLatin1String("t_refund")) || 2870 att.endsWith(QLatin1String("t_refunddisplay")) || 2871 att.endsWith(QLatin1String("t_realrefund"))) { 2872 return QStringLiteral("crosshairs"); 2873 } 2874 if (att.endsWith(QLatin1String("t_mode"))) { 2875 return QStringLiteral("skrooge_credit_card"); 2876 } 2877 if (att.endsWith(QLatin1String("t_account")) || 2878 att.endsWith(QLatin1String("t_toaccount")) || 2879 att.endsWith(QLatin1String("t_accounttype"))) { 2880 return QStringLiteral("view-bank"); 2881 } 2882 if (att.endsWith(QLatin1String("t_payee"))) { 2883 return QStringLiteral("user-group-properties"); 2884 } 2885 if (att.endsWith(QLatin1String("t_comment")) || 2886 att.endsWith(QLatin1String("t_realcomment"))) { 2887 return QStringLiteral("draw-freehand"); 2888 } 2889 if (att.endsWith(QLatin1String("t_warn")) || 2890 att.endsWith(QLatin1String("i_warn_days"))) { 2891 return QStringLiteral("dialog-information"); 2892 } 2893 if (att.endsWith(QLatin1String("t_name"))) { 2894 if (att.contains(QStringLiteral("v_account"))) { 2895 return QStringLiteral("view-bank"); 2896 } 2897 if (att.contains(QStringLiteral("v_category"))) { 2898 return QStringLiteral("view-categories"); 2899 } 2900 if (att.contains(QStringLiteral("v_refund"))) { 2901 return QStringLiteral("crosshairs"); 2902 } 2903 if (att.contains(QStringLiteral("v_unit"))) { 2904 return QStringLiteral("taxes-finances"); 2905 } 2906 if (att.contains(QStringLiteral("v_payee"))) { 2907 return QStringLiteral("user-group-properties"); 2908 } 2909 } 2910 if (att.endsWith(QLatin1String("f_rate"))) { 2911 return QStringLiteral("skrooge_more"); 2912 } 2913 if (att.endsWith(QLatin1String("t_internet_code")) || att.endsWith(QLatin1String("t_source")) || att.endsWith(QLatin1String("d_maxdate"))) { 2914 return QStringLiteral("download"); 2915 } 2916 if (att.contains(QStringLiteral(".d_")) || att.startsWith(QLatin1String("d_"))) { 2917 return QStringLiteral("view-calendar"); 2918 } 2919 if (att.endsWith(QLatin1String("i_year")) || att.endsWith(QLatin1String("i_month")) || att.endsWith(QLatin1String("t_period"))) { 2920 return QStringLiteral("view-calendar"); 2921 } 2922 if (att.endsWith(QLatin1String("f_delta"))) { 2923 return QStringLiteral("security-high"); 2924 } 2925 if (att.endsWith(QLatin1String("f_deltabeforetransfer"))) { 2926 return QStringLiteral("security-medium"); 2927 } 2928 if (att.endsWith(QLatin1String("f_budgeted")) || att.endsWith(QLatin1String("f_budgeted_modified"))) { 2929 return QStringLiteral("view-calendar-whatsnext"); 2930 } 2931 if (att.endsWith(QLatin1String("t_rules"))) { 2932 return QStringLiteral("system-run"); 2933 } 2934 if (att.endsWith(QLatin1String("t_whennls"))) { 2935 return QStringLiteral("view-calendar"); 2936 } 2937 if (att.endsWith(QLatin1String("t_whatnls"))) { 2938 return QStringLiteral("skrooge_type"); 2939 } 2940 if (att.endsWith(QLatin1String("t_rulenls"))) { 2941 return QStringLiteral("view-calendar-whatsnext"); 2942 } 2943 if (att.endsWith(QLatin1String("t_bank"))) { 2944 return QStringLiteral("view-bank"); 2945 } 2946 if (att.endsWith(QLatin1String("t_transfer"))) { 2947 return QStringLiteral("exchange-positions"); 2948 } 2949 if (att.endsWith(QLatin1String("_number"))) { 2950 return QStringLiteral("dialog-information"); 2951 } 2952 if (att.endsWith(QLatin1String("i_auto_write_days"))) { 2953 return QStringLiteral("insert-text"); 2954 } 2955 if (att.endsWith(QLatin1String("_address"))) { 2956 return QStringLiteral("address-book-new"); 2957 } 2958 if (att.endsWith(QLatin1String("i_order"))) { 2959 return QStringLiteral("view-sort-ascending"); 2960 } 2961 if (att.endsWith(QLatin1String("t_periodnls"))) { 2962 return QStringLiteral("smallclock"); 2963 } 2964 if (att.endsWith(QLatin1String("i_nbsuboperations"))) { 2965 return QStringLiteral("exchange-positions"); 2966 } 2967 return SKGDocument::getIconName(iString); 2968 } 2969 2970 QString SKGDocumentBank::getDisplay(const QString& iString) const 2971 { 2972 QString output = iString.toLower(); 2973 2974 // Internationallization 2975 if (output.endsWith(QLatin1String("account.t_name")) || 2976 output.endsWith(QLatin1String("t_account"))) { 2977 return i18nc("Noun, an account as in a bank account", "Account"); 2978 } 2979 if (output.endsWith(QLatin1String("t_accounttype"))) { 2980 return i18nc("Noun, an account as in a bank account", "Account's type"); 2981 } 2982 if (output.endsWith(QLatin1String("t_operationname"))) { 2983 return i18nc("Noun, a financial operation", "Transaction"); 2984 } 2985 if (output.endsWith(QLatin1String("t_name"))) { 2986 return i18nc("Noun, the name of an item", "Name"); 2987 } 2988 if (output.endsWith(QLatin1String("account.f_value")) || 2989 output.endsWith(QLatin1String("f_balance"))) { 2990 return i18nc("Noun, as in commercial balance", "Balance"); 2991 } 2992 if (output.endsWith(QLatin1String("f_balance_entered"))) { 2993 return i18nc("Noun, as in commercial balance", "Balance entered"); 2994 } 2995 if (output.endsWith(QLatin1String("f_value"))) { 2996 return i18nc("Name, the numerical amount of a financial operation", "Amount"); 2997 } 2998 if (output.endsWith(QLatin1String("f_currentamount")) || 2999 output.endsWith(QLatin1String("f_realcurrentamount"))) { 3000 return i18nc("Name, the numerical amount of a financial operation", "Amount"); 3001 } 3002 if (output.endsWith(QLatin1String("f_todayamount"))) { 3003 return i18nc("Name, the numerical amount of a financial operation", "Today amount"); 3004 } 3005 if (output.endsWith(QLatin1String("f_currentamount_income")) || 3006 output.endsWith(QLatin1String("f_realcurrentamount_income"))) { 3007 return i18nc("Noun, financial transactions with a positive amount", "Income"); 3008 } 3009 if (output.endsWith(QLatin1String("f_currentamount_expense")) || 3010 output.endsWith(QLatin1String("f_realcurrentamount_expense"))) { 3011 return i18nc("Noun, financial transactions with a negative amount", "Expenditure"); 3012 } 3013 if (output.endsWith(QLatin1String("f_quantity_income")) || 3014 output.endsWith(QLatin1String("f_realquantity_income"))) { 3015 return i18nc("Noun", "Income entered"); 3016 } 3017 if (output.endsWith(QLatin1String("f_quantity_expense")) || 3018 output.endsWith(QLatin1String("f_realquantity_expense"))) { 3019 return i18nc("Noun", "Expenditure entered"); 3020 } 3021 if (output.endsWith(QLatin1String("f_quantityowned"))) { 3022 return i18nc("Noun", "Quantity owned"); 3023 } 3024 if (output.endsWith(QLatin1String("f_amountowned"))) { 3025 return i18nc("Noun", "Amount owned"); 3026 } 3027 if (output.endsWith(QLatin1String("quantity"))) { 3028 return i18nc("Noun", "Amount entered"); 3029 } 3030 if (output.endsWith(QLatin1String("account.t_number"))) { 3031 return i18nc("Noun", "Account number"); 3032 } 3033 if (output.endsWith(QLatin1String("t_number"))) { 3034 return i18nc("Noun, a number identifying an item", "Number"); 3035 } 3036 if (output.endsWith(QLatin1String("t_bank_number"))) { 3037 return i18nc("Noun", "Bank number"); 3038 } 3039 if (output.endsWith(QLatin1String("t_agency_number"))) { 3040 return i18nc("Noun", "Agency number"); 3041 } 3042 if (output.endsWith(QLatin1String("t_agency_address"))) { 3043 return i18nc("Noun", "Agency address"); 3044 } 3045 if (output.endsWith(QLatin1String("t_address"))) { 3046 return i18nc("Noun", "Address"); 3047 } 3048 if (output.endsWith(QLatin1String("t_payee"))) { 3049 return i18nc("A person or institution receiving a payment, or paying the operation", "Payee"); 3050 } 3051 if (output.endsWith(QLatin1String("t_comment"))) { 3052 return i18nc("Noun, a user comment on an item", "Comment"); 3053 } 3054 if (output.endsWith(QLatin1String("t_realcomment"))) { 3055 return i18nc("Noun, a user comment on an item", "Sub comment"); 3056 } 3057 if (output.endsWith(QLatin1String("t_mode"))) { 3058 return i18nc("Noun, the mode used for payment of the transaction (Credit Card, Cheque, Transfer…)", "Mode"); 3059 } 3060 if (output.contains(QStringLiteral("recurrentoperation")) && output.endsWith(QLatin1String("d_date"))) { 3061 return i18nc("Noun", "Next occurrence"); 3062 } 3063 if (output.endsWith(QLatin1String("d_date")) || 3064 output.endsWith(QLatin1String("d_dateop"))) { 3065 return i18nc("Noun, the date of an item", "Date"); 3066 } 3067 if (output.endsWith(QLatin1String("d_createdate"))) { 3068 return i18nc("Noun, the date of creation of an item", "Creation date"); 3069 } 3070 if (output.endsWith(QLatin1String("d_dateweek"))) { 3071 return i18nc("Noun, 7 days", "Week"); 3072 } 3073 if (output.endsWith(QLatin1String("d_datemonth"))) { 3074 return i18nc("Noun, the months in a year", "Month"); 3075 } 3076 if (output.endsWith(QLatin1String("d_datequarter"))) { 3077 return i18nc("Noun, 3 months", "Quarter"); 3078 } 3079 if (output.endsWith(QLatin1String("d_datesemester"))) { 3080 return i18nc("Noun, 6 months", "Semester"); 3081 } 3082 if (output.endsWith(QLatin1String("d_dateyear"))) { 3083 return i18nc("Noun, the years in a century", "Year"); 3084 } 3085 if (output.endsWith(QLatin1String("d_firstdate"))) { 3086 return i18nc("Noun, the date of an item", "First date"); 3087 } 3088 if (output.endsWith(QLatin1String("d_lastdate"))) { 3089 return i18nc("Noun, the date of an item", "Last date"); 3090 } 3091 if (output.endsWith(QLatin1String("d_maxdate"))) { 3092 return i18nc("Noun, the date of the last download", "Download date"); 3093 } 3094 if (output.endsWith(QLatin1String("d_reconciliationdate"))) { 3095 return i18nc("Noun, the date of the last reconciliation", "Reconciliation date"); 3096 } 3097 if (output.endsWith(QLatin1String("t_categorycondition")) || 3098 output.endsWith(QLatin1String("t_category")) || 3099 output.endsWith(QLatin1String("t_realcategory"))) { 3100 return i18nc("Noun, the category of an item", "Category"); 3101 } 3102 if (output.endsWith(QLatin1String("t_bank"))) { 3103 return i18nc("Noun, a financial institution", "Bank"); 3104 } 3105 if (output.endsWith(QLatin1String("t_unit"))) { 3106 return i18nc("Noun, the unit of an operation, usually a currency or a share", "Unit"); 3107 } 3108 if (output.endsWith(QLatin1String("t_unittype"))) { 3109 return i18nc("Noun, the unit of an operation, usually a currency or a share", "Unit's type"); 3110 } 3111 if (output.endsWith(QLatin1String("f_checked"))) { 3112 return i18nc("Adjective, has an item been checked or not", "Checked"); 3113 } 3114 if (output.endsWith(QLatin1String("f_coming_soon"))) { 3115 return i18nc("Adjective, a foreseen value", "To be Checked"); 3116 } 3117 if (output.endsWith(QLatin1String("t_symbol"))) { 3118 return i18nc("Noun, ahe unit symbol, something in the line of $, €, £…", "Symbol"); 3119 } 3120 if (output.endsWith(QLatin1String("t_country"))) { 3121 return i18nc("Noun, a country in the world (France, China…)", "Country"); 3122 } 3123 if (output.endsWith(QLatin1String("t_type")) || 3124 output.endsWith(QLatin1String("t_typenls"))) { 3125 return i18nc("Noun, the type of an item", "Type"); 3126 } 3127 if (output.endsWith(QLatin1String("t_typeexpensenls"))) { 3128 return i18nc("Noun, the type of an item", "Type"); 3129 } 3130 if (output.endsWith(QLatin1String("t_internet_code"))) { 3131 return i18nc("Noun", "Internet code"); 3132 } 3133 if (output.endsWith(QLatin1String("i_nboperations"))) { 3134 return i18nc("Noun", "Number of transactions"); 3135 } 3136 if (output.endsWith(QLatin1String("t_periodnls"))) { 3137 return i18nc("Noun, how frequently something occurs", "Periodicity"); 3138 } 3139 if (output.endsWith(QLatin1String("i_auto_write_days"))) { 3140 return i18nc("Automatically write something", "Auto write"); 3141 } 3142 if (output.endsWith(QLatin1String("i_nb_times"))) { 3143 return i18nc("Noun", "Nb of occurrences"); 3144 } 3145 if (output.endsWith(QLatin1String("i_warn_days"))) { 3146 return i18nc("Verb, warn the user about an event", "Warn"); 3147 } 3148 if (output.endsWith(QLatin1String("t_close"))) { 3149 return i18nc("Adjective, a closed item", "Closed"); 3150 } 3151 if (output.endsWith(QLatin1String("t_bookmarked"))) { 3152 return i18nc("Adjective, an highlighted item", "Highlighted"); 3153 } 3154 if (output.endsWith(QLatin1String("t_status"))) { 3155 return i18nc("Noun, the status of an item", "Status"); 3156 } 3157 if (output.endsWith(QLatin1String("i_nbrecurrent"))) { 3158 return i18nc("Adjective, an item scheduled to happen on a regular basis", "Scheduled"); 3159 } 3160 if (output.endsWith(QLatin1String("i_sumnboperations"))) { 3161 return i18nc("Noun", "Number of transactions (cumulative)"); 3162 } 3163 if (output.endsWith(QLatin1String("f_sumcurrentamount"))) { 3164 return i18nc("Noun", "Amount (cumulative)"); 3165 } 3166 if (output.endsWith(QLatin1String("t_description"))) { 3167 return i18nc("Noun", "Search description"); 3168 } 3169 if (output.endsWith(QLatin1String("t_action_description"))) { 3170 return i18nc("Noun", "Process description"); 3171 } 3172 if (output.endsWith(QLatin1String("t_action_type"))) { 3173 return i18nc("Noun, the type of action", "Action type"); 3174 } 3175 if (output.endsWith(QLatin1String("t_refund")) || 3176 output.endsWith(QLatin1String("t_realrefund"))) { 3177 return i18nc("Noun, something that is used to track items", "Tracker"); 3178 } 3179 if (output.endsWith(QLatin1String("t_refunddisplay"))) { 3180 return i18nc("Noun, something that is used to track items", "Trackers"); 3181 } 3182 if (output.endsWith(QLatin1String("t_imported"))) { 3183 return i18nc("Noun", "Import status"); 3184 } 3185 if (output.endsWith(QLatin1String("i_nbdecimal"))) { 3186 return i18nc("Noun, after the dot", "Nb decimal"); 3187 } 3188 if (output.endsWith(QLatin1String("f_rate"))) { 3189 return i18nc("Noun, for a share", "Rate"); 3190 } 3191 if (output.endsWith(QLatin1String("d_valuedate"))) { 3192 return i18nc("Noun", "Value date"); 3193 } 3194 if (output.endsWith(QLatin1String("f_coef"))) { 3195 return i18nc("Noun", "Coef"); 3196 } 3197 if (output.endsWith(QLatin1String("f_annual_interest"))) { 3198 return i18nc("Noun", "Annual Interest"); 3199 } 3200 if (output.endsWith(QLatin1String("f_accrued_interest"))) { 3201 return i18nc("Noun", "Accrued Interest"); 3202 } 3203 if (output.endsWith(QLatin1String("t_income_value_date_mode"))) { 3204 return i18nc("Noun", "Value date for credit"); 3205 } 3206 if (output.endsWith(QLatin1String("t_expenditure_value_date_mode"))) { 3207 return i18nc("Noun", "Value date for debit"); 3208 } 3209 if (output.endsWith(QLatin1String("t_base"))) { 3210 return i18nc("Noun", "Base computation"); 3211 } 3212 if (output.endsWith(QLatin1String("i_year"))) { 3213 return i18nc("Noun", "Year"); 3214 } 3215 if (output.endsWith(QLatin1String("i_month"))) { 3216 return i18nc("Noun", "Month"); 3217 } 3218 if (output.endsWith(QLatin1String("t_period"))) { 3219 return i18nc("Noun", "Period"); 3220 } 3221 if (output.endsWith(QLatin1String("i_order"))) { 3222 return i18nc("Noun, sort order", "Order"); 3223 } 3224 if (output.endsWith(QLatin1String("t_whennls"))) { 3225 return i18nc("Noun", "When"); 3226 } 3227 if (output.endsWith(QLatin1String("t_whatnls"))) { 3228 return i18nc("Noun", "What"); 3229 } 3230 if (output.endsWith(QLatin1String("t_rulenls"))) { 3231 return i18nc("Noun", "Impacted budget"); 3232 } 3233 if (output.endsWith(QLatin1String("t_rules"))) { 3234 return i18nc("Noun", "Rules"); 3235 } 3236 if (output.endsWith(QLatin1String("f_budgeted"))) { 3237 return i18nc("Noun", "Entered Budget"); 3238 } 3239 if (output.endsWith(QLatin1String("f_budgeted_modified"))) { 3240 return i18nc("Noun", "Corrected budget"); 3241 } 3242 if (output.endsWith(QLatin1String("f_delta"))) { 3243 return i18nc("Noun", "Delta after rules"); 3244 } 3245 if (output.endsWith(QLatin1String("f_deltabeforetransfer"))) { 3246 return i18nc("Noun", "Delta"); 3247 } 3248 if (output.endsWith(QLatin1String("t_source"))) { 3249 return i18nc("Noun", "Download source"); 3250 } 3251 if (output.endsWith(QLatin1String("t_transfer"))) { 3252 return i18nc("Noun", "Transfer"); 3253 } 3254 if (output.endsWith(QLatin1String("t_toaccount"))) { 3255 return i18nc("Noun, a target account of a transfer", "To account"); 3256 } 3257 if (output.endsWith(QLatin1String("f_maxamount"))) { 3258 return i18nc("Noun, a maximum limit", "Maximum limit"); 3259 } 3260 if (output.endsWith(QLatin1String("f_minamount"))) { 3261 return i18nc("Noun, a minimum limit", "Minimum limit"); 3262 } 3263 if (output.endsWith(QLatin1String("i_opid"))) { 3264 return i18nc("Noun, the id of an operation", "Transaction id"); 3265 } 3266 if (output.endsWith(QLatin1String("#nothing#"))) { 3267 return i18nc("Noun, the absence of anything", "-- Nothing --"); 3268 } 3269 if (output.endsWith(QLatin1String("f_importbalance"))) { 3270 return i18nc("Noun", "Balance import"); 3271 } 3272 if (output.endsWith(QLatin1String("f_reconciliationbalance"))) { 3273 return i18nc("Noun", "Balance reconciliation"); 3274 } 3275 if (output.endsWith(QLatin1String("d_importdate"))) { 3276 return i18nc("Noun, the date of the last import", "Import date"); 3277 } 3278 if (output.endsWith(QLatin1String("i_nbsuboperations"))) { 3279 return i18nc("Noun", "Number of split"); 3280 } 3281 3282 return SKGDocument::getDisplay(iString); 3283 } 3284 3285 QString SKGDocumentBank::getRealAttribute(const QString& iString) const 3286 { 3287 if (iString.endsWith(QLatin1String("t_BANK"))) { 3288 return QStringLiteral("bank.rd_bank_id.t_name"); 3289 } 3290 if (iString.endsWith(QLatin1String("t_BANK_NUMBER"))) { 3291 return QStringLiteral("bank.rd_bank_id.t_bank_number"); 3292 } 3293 return SKGDocument::getRealAttribute(iString); 3294 } 3295 3296 SKGServices::AttributeType SKGDocumentBank::getAttributeType(const QString& iAttributeName) const 3297 { 3298 SKGServices::AttributeType output = SKGServices::TEXT; 3299 if (iAttributeName == QStringLiteral("t_status") || iAttributeName == QStringLiteral("t_imported")) { 3300 return SKGServices::TRISTATE; 3301 } 3302 if (iAttributeName == QStringLiteral("t_close") || iAttributeName == QStringLiteral("t_bookmarked") || iAttributeName == QStringLiteral("t_auto_write") || 3303 iAttributeName == QStringLiteral("t_warn") || iAttributeName == QStringLiteral("t_TRANSFER") || iAttributeName == QStringLiteral("t_template") || 3304 iAttributeName == QStringLiteral("t_times") || 3305 iAttributeName == QStringLiteral("t_absolute") || iAttributeName == QStringLiteral("t_category_condition") || iAttributeName == QStringLiteral("t_month_condition") || iAttributeName == QStringLiteral("t_year_condition") || iAttributeName == QStringLiteral("t_including_subcategories")) { 3306 return SKGServices::BOOL; 3307 } 3308 output = SKGDocument::getAttributeType(iAttributeName); 3309 3310 return output; 3311 } 3312 3313 3314 QVariantList SKGDocumentBank::getBudget(const QString& iMonth) const 3315 { 3316 SKGTRACEINFUNC(10) 3317 QVariantList table; 3318 SKGStringListList listTmp; 3319 SKGError err = executeSelectSqliteOrder("SELECT t_CATEGORY, f_budgeted, f_CURRENTAMOUNT, f_DELTABEFORETRANSFER, f_budgeted_modified FROM v_budget " 3320 "where t_PERIOD='" % iMonth % "' ORDER BY t_CATEGORY;", 3321 listTmp); 3322 int nbval = listTmp.count(); 3323 if (!err && nbval > 1) { 3324 table.reserve(nbval + 1); 3325 table.push_back(QVariantList() << "sum" << getDisplay(QStringLiteral("t_CATEGORY")) << getDisplay(QStringLiteral("f_budgeted_modified")) << getDisplay(QStringLiteral("f_CURRENTAMOUNT")) << getDisplay(QStringLiteral("f_DELTA"))); 3326 double sum1 = 0; 3327 double sum2 = 0; 3328 double sum3 = 0; 3329 double sum4 = 0; 3330 for (int i = 1; i < nbval; ++i) { // Ignore header 3331 double v1 = SKGServices::stringToDouble(listTmp.at(i).at(1)); 3332 double v2 = SKGServices::stringToDouble(listTmp.at(i).at(2)); 3333 double v3 = SKGServices::stringToDouble(listTmp.at(i).at(3)); 3334 double v4 = SKGServices::stringToDouble(listTmp.at(i).at(4)); 3335 table.push_back(QVariantList() << false << listTmp.at(i).at(0) << v1 << v2 << v3 << v4); 3336 3337 sum1 += v1; 3338 sum2 += v2; 3339 sum3 += v3; 3340 sum4 += v4; 3341 } 3342 table.push_back(QVariantList() << true << i18nc("Noun, the numerical total of a sum of values", "Total") << sum1 << sum2 << sum3 << sum4); 3343 } 3344 return table; 3345 } 3346 3347 QVariantList SKGDocumentBank::getMainCategories(const QString& iPeriod, int iNb) 3348 { 3349 SKGTRACEINFUNC(10) 3350 QVariantList table; 3351 SKGServices::SKGUnitInfo primary = getPrimaryUnit(); 3352 3353 QString wc = "t_TRANSFER='N' AND t_TYPEEXPENSE='-' AND " + SKGServices::getPeriodWhereClause(iPeriod); 3354 3355 SKGStringListList listTmp; 3356 SKGError err = executeSelectSqliteOrder("SELECT t_REALCATEGORY, TOTAL(f_REALCURRENTAMOUNT), " 3357 "100*TOTAL(f_REALCURRENTAMOUNT)/(SELECT TOTAL(f_REALCURRENTAMOUNT) FROM v_suboperation_consolidated WHERE " % wc % ") " 3358 "FROM v_suboperation_consolidated " 3359 "WHERE " % wc % " GROUP BY t_REALCATEGORY ORDER BY TOTAL(f_REALCURRENTAMOUNT)", 3360 listTmp); 3361 int nbval = listTmp.count(); 3362 if (!err && (nbval != 0)) { 3363 table.reserve(nbval); 3364 table.push_back(QVariantList() << "sum" << getDisplay(QStringLiteral("t_REALCATEGORY")) << iPeriod << "url" << "percent"); 3365 3366 // Add X main categories 3367 for (int i = 1; i < nbval && i <= iNb; ++i) { // Ignore header 3368 QString cat = listTmp.at(i).at(0); 3369 double v = qAbs(SKGServices::stringToDouble(listTmp.at(i).at(1))); 3370 double p = qAbs(SKGServices::stringToDouble(listTmp.at(i).at(2))); 3371 table.push_back(QVariantList() << false << cat << v << QString(wc % " AND t_REALCATEGORY='" % SKGServices::stringToSqlString(cat) % "'") << p); 3372 } 3373 3374 // Build "Other" category 3375 QStringList listCat; 3376 listCat.reserve(nbval); 3377 double sum = 0.0; 3378 double sumPercent = 0.0; 3379 for (int i = iNb + 1; i < nbval; ++i) { 3380 listCat.push_back(SKGServices::stringToSqlString(listTmp.at(i).at(0))); 3381 sum += qAbs(SKGServices::stringToDouble(listTmp.at(i).at(1))); 3382 sumPercent += qAbs(SKGServices::stringToDouble(listTmp.at(i).at(2))); 3383 } 3384 if (listCat.count() != 0) { 3385 table.push_back(QVariantList() << false << i18nc("an other category", "Others") << sum << QString(wc % " AND t_REALCATEGORY IN ('" % listCat.join(QStringLiteral("','")) % "')") << sumPercent); 3386 } 3387 } 3388 return table; 3389 } 3390 3391 QStringList SKGDocumentBank::get5MainCategoriesVariationList(const QString& iPeriod, const QString& iPreviousPeriod, bool iOnlyIssues, QStringList* oCategoryList) 3392 { 3393 SKGTRACEINFUNC(10) 3394 // Compute input string 3395 QString inputString = iPeriod % iPreviousPeriod % (iOnlyIssues ? 'Y' : 'N') % (oCategoryList != nullptr ? 'Y' : 'N'); 3396 3397 // Use cache or not 3398 QStringList output; 3399 if (inputString == m_5mainVariations_inputs) { 3400 // Yes 3401 output = m_5mainVariations_cache; 3402 if (oCategoryList != nullptr) { 3403 *oCategoryList = m_5mainVariationsCat_cache; 3404 } 3405 } 3406 m_5mainVariations_inputs = inputString; 3407 3408 if (output.isEmpty()) { 3409 SKGServices::SKGUnitInfo primary = getPrimaryUnit(); 3410 3411 SKGStringListList listTmp; 3412 SKGError err = executeSelectSqliteOrder("select *, 100*(A2-A1)/ABS(A1) as 'V' from " 3413 "(SELECT t_REALCATEGORY as 'C1', TOTAL(f_REALCURRENTAMOUNT) as 'A1' FROM v_suboperation_consolidated where " 3414 "t_TRANSFER='N' AND " + SKGServices::getPeriodWhereClause(iPreviousPeriod) + " AND t_TYPEEXPENSE='-' group by t_REALCATEGORY) A," 3415 "(SELECT t_REALCATEGORY as 'C2', TOTAL(f_REALCURRENTAMOUNT) as 'A2' FROM v_suboperation_consolidated where " 3416 "t_TRANSFER='N' AND " + SKGServices::getPeriodWhereClause(iPeriod) + " AND t_TYPEEXPENSE='-' group by t_REALCATEGORY) B " 3417 "WHERE A.C1=B.C2 AND ABS(A2-A1)/ABS(A1)>0.1 " + 3418 (iOnlyIssues ? "AND ((A1<0 AND A2<0 AND A2<A1) OR (A1>0 AND A2>0 AND A2<A1))" : "") + 3419 " ORDER BY ABS(A2-A1) DESC LIMIT 5;", 3420 listTmp); 3421 IFOK(err) { 3422 // Clear the list 3423 m_5mainVariations_cache.clear(); 3424 m_5mainVariationsCat_cache.clear(); 3425 3426 // Fill list 3427 int nbval = listTmp.count(); 3428 for (int i = 1; i < nbval; ++i) { // Ignore header 3429 /*Example of sentences: 3430 Expenses in category "Food > Grocery" in November decreased by 14% for a total of 220,48€. 3431 Expenses in category "Food" (no subcategory) in November increased by 24% for a total of 70,20€. 3432 Expenses in category "Automotive > Fuel" in November increased by 24% for a total of 122,48€. 3433 Expenses in category "Misc" in November decreased by 30% for a total of 36,52€. 3434 This month, you spent 75,00€ in Category "Bills > Subscriptions". No expense in that category was found in previous month 3435 Expenses with mode "withdrawal" reprensented 60,00€ of your expenses in current month*/ 3436 QString c1 = listTmp.at(i).at(0); 3437 double a1 = SKGServices::stringToDouble(listTmp.at(i).at(1)); 3438 double a2 = SKGServices::stringToDouble(listTmp.at(i).at(3)); 3439 double v = SKGServices::stringToDouble(listTmp.at(i).at(4)); 3440 3441 QString a2f = formatMoney(qAbs(a2), primary); 3442 if (a1 < 0 && a2 < 0) { 3443 QString vf = formatPercentage(qAbs(v), v < 0); 3444 if (v < 0) { 3445 m_5mainVariations_cache.push_back(i18n("Expenses in category <b>'%1'</b> increased by <b>%2</b> for a total of <b>%3</b>.", c1, vf, a2f)); 3446 m_5mainVariationsCat_cache.push_back(c1); 3447 } else { 3448 if (!iOnlyIssues) { 3449 m_5mainVariations_cache.push_back(i18n("Expenses in category <b>'%1'</b> decreased by <b>%2</b> for a total of <b>%3</b>.", c1, vf, a2f)); 3450 m_5mainVariationsCat_cache.push_back(c1); 3451 } 3452 } 3453 } else if (a1 > 0 && a2 > 0) { 3454 QString vf = formatPercentage(qAbs(v)); 3455 if (v > 0) { 3456 if (!iOnlyIssues) { 3457 m_5mainVariations_cache.push_back(i18n("Incomes in category <b>'%1'</b> increased by <b>%2</b> for a total of <b>%3</b>.", c1, vf, a2f)); 3458 m_5mainVariationsCat_cache.push_back(c1); 3459 } 3460 } else { 3461 m_5mainVariations_cache.push_back(i18n("Incomes in category <b>'%1'</b> decreased by <b>%2</b> for a total of <b>%3</b>.", c1, vf, a2f)); 3462 m_5mainVariationsCat_cache.push_back(c1); 3463 } 3464 } 3465 } 3466 } 3467 output = m_5mainVariations_cache; 3468 if (oCategoryList != nullptr) { 3469 *oCategoryList = m_5mainVariationsCat_cache; 3470 } 3471 } 3472 return output; 3473 } 3474 3475 SKGReport* SKGDocumentBank::getReport() const 3476 { 3477 return new SKGReportBank(const_cast<SKGDocumentBank*>(this)); 3478 }