-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathTrxnFactory.php
99 lines (81 loc) · 2.88 KB
/
TrxnFactory.php
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
<?php
require_once('DbFactory.php');
class TrxnFactory {
public static function addTrxn ($user, $desc, $amount, $category, $trxntype) {
$sql = "INSERT INTO tb_trxn(
fk_pk_user_id
,amount
,description
,fk_pk_category_id
,fk_pk_trxn_type_id
,datetime_created
,datetime_updated
)
VALUES(
:user
,:amount
,:desc
,:category
,:trxntype
,NOW()
,NOW()
)";
$parameters = array('user'=>$user, 'desc'=>$desc, 'amount'=>$amount, 'category'=>$category, 'trxntype'=>$trxntype);
$datatypes = array('user'=>'i', 'desc'=>'s', 'amount'=>'d', 'category'=>'i', 'trxntype'=>'i');
$result = DbFactory::queryDb($sql, $parameters, $datatypes);
return $result;
}
public static function getTrxnSummary ($user) {
$sql = "SELECT
t.description
,t.amount
,c.name category
,tt.name type
FROM tb_trxn t
JOIN tb_category c
ON t.fk_pk_category_id = c.pk_category_id
JOIN tb_trxn_type tt
ON t.fk_pk_trxn_type_id = tt.pk_trxn_type_id
WHERE fk_pk_user_id = :user
ORDER BY t.datetime_created";
$parameters = array('user'=>$user);
$datatypes = array('user'=>'i');
$result = DbFactory::queryDb($sql, $parameters, $datatypes);
$summary = array();
$summary['trxns'] = array();
$sum = 0;
foreach ($result as $r) {
$summary['trxns'][] = $r;
if ($r->type == 'spend') {
$sum += $r->amount;
} elseif ($r->type == 'earn') {
$sum -= $r->amount;
}
}
$summary['sum'] = $sum;
return $summary;
}
public static function getCategories () {
$sql = "SELECT
c.pk_category_id id
,c.name name
FROM tb_category c
ORDER BY pk_category_id";
$parameters = array();
$datatypes = array();
$result = DbFactory::queryDb($sql, $parameters, $datatypes);
return $result;
}
public static function getTypes () {
$sql = "SELECT
pk_trxn_type_id id
,name
FROM tb_trxn_type tt
ORDER BY pk_trxn_type_id";
$parameters = array();
$datatypes = array();
$result = DbFactory::queryDb($sql, $parameters, $datatypes);
return $result;
}
}
?>