1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
|
#include "TransactionsModel.hpp"
#include "Model/Project.hpp"
#include <filesystem>
#include <stdexcept>
namespace fs = std::filesystem;
SalesTable::SalesTable(TransactionModel& db)
// language=SQLite
: GetRowCount(db.GetSQLite(), "SELECT Count(*) FROM Sales")
// language=SQLite
, GetRows(db.GetSQLite(), "SELECT * FROM Sales WHERE Id >= ? AND Id < ?")
// language=SQLite
, GetItems(db.GetSQLite(), "SELECT * FROM SalesItems WHERE SaleId == ?")
{
}
PurchasesTable::PurchasesTable(TransactionModel& db)
// language=SQLite
: GetRowCount(db.GetSQLite(), "SELECT Count(*) FROM Purchases")
// language=SQLite
, GetRows(db.GetSQLite(), "SELECT * FROM Purchases WHERE Id >= ? AND Id < ?")
// language=SQLite
, GetItems(db.GetSQLite(), "SELECT * FROM PurchasesItems WHERE PurchaseId == ?")
{
}
DeliveryTable::DeliveryTable(TransactionModel& db)
// language=SQLite
: FilterByTypeAndId(db.GetSQLite(), "SELECT * FROM Deliveries WHERE AssociatedOrder == ? AND Outgoing = ?")
// language=SQLite
, GetItems(db.GetSQLite(), "SELECT * FROM DeliveriesItems WHERE DeliveryId == ?")
{
}
static std::string GetDatabaseFilePath(const Project& project)
{
auto dbsDir = project.GetPath() / "databases";
fs::create_directories(dbsDir);
auto dbFile = dbsDir / "transactions.sqlite3";
return dbFile.string();
}
/// Wrapper for SQLite::Database that creates the default tables
TransactionModel::DatabaseWrapper::DatabaseWrapper(TransactionModel& self)
: mSqlite(GetDatabaseFilePath(*self.mProject), SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE)
{
// If this table doesn't exist, the database probably just got initialized
if (mSqlite.tableExists("Sales")) {
return;
}
// 'Sales' schema
// - Customer: the customer item ID
// - Deadline: unix epoch time of order deadline
// - DeliveryTime: the time this order was completed (through a set of deliveries)
// 'Purchases' schema
// - Factory: the factory id,
// - OrderTime: the time this order was made
// - DeliveryTime: the time this order was completed (through a set of deliveries)
// 'Deliveries' schema
// - ShipmentTime: unix epoch time stamp of sending to delivery
// - ArrivalTime: unix epoch time stamp of delivery arrived at warehouse; 0 if not arrived yet
// - AssociatedOrder: Id of the order that this delivery is completing (which table: Outgoing=true -> Sales, Outgoing=false -> Purchases)
// - Outgoing: true if the delivery is from warehouse to customer; false if the delivery is from factory to warehouse
// Note: the 'Id' key would be unique (not recycled after row deletion) because it's explicit
// https://www.sqlite.org/rowidtable.html
// language=SQLite
mSqlite.exec(R"""(
CREATE TABLE IF NOT EXISTS Sales(
Id INT PRIMARY KEY,
Customer INT,
Deadline DATETIME,
DeliveryTime DATETIME
);
CREATE TABLE IF NOT EXISTS SalesItems(
SaleId INT,
ItemId INT,
Count INT
);
CREATE TABLE IF NOT EXISTS Purchases(
Id INT PRIMARY KEY,
Factory INT,
OrderTime DATETIME,
DeliveryTime DATETIME
);
CREATE TABLE IF NOT EXISTS PurchasesItems(
PurchaseId INT,
ItemId INT,
Count INT
);
CREATE TABLE IF NOT EXISTS Deliveries(
Id INT PRIMARY KEY,
ShipmentTime DATETIME,
ArrivalTime DATETIME,
AssociatedOrder INT,
Outgoing BOOLEAN
);
CREATE TABLE IF NOT EXISTS DeliveriesItems(
DeliveryId INT,
ItemId INT,
Count INT
);
)""");
}
TransactionModel::TransactionModel(Project& project)
: mProject{ &project }
, mDbWrapper(*this)
, mSales(*this)
, mPurchases(*this)
, mDeliveries(*this)
{
}
const SQLite::Database& TransactionModel::GetSQLite() const
{
return mDbWrapper.mSqlite;
}
SQLite::Database& TransactionModel::GetSQLite()
{
return mDbWrapper.mSqlite;
}
const SalesTable& TransactionModel::GetSales() const
{
return mSales;
}
SalesTable& TransactionModel::GetSales()
{
return mSales;
}
const PurchasesTable& TransactionModel::GetPurchases() const
{
return mPurchases;
}
PurchasesTable& TransactionModel::GetPurchases()
{
return mPurchases;
}
const DeliveryTable& TransactionModel::GetDeliveries() const
{
return mDeliveries;
}
DeliveryTable& TransactionModel::GetDeliveries()
{
return mDeliveries;
}
|
