-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathmain.py
281 lines (222 loc) · 8.19 KB
/
main.py
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
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
"""
Main Function
This script creates a .csv file according to the chart of accounts input and the general ledger input
This script requires that `pandas` and `csv` to be installed within the Python
environment you are running this script in.
"""
import pandas as pd
import csv
from input import get_ledger_data, get_accounts_data
class TreeNode:
"""
This class represents a TreeNode
Attributes
-----------------
account: str
a string that holds the account number of the node
level: int
an integer that states the level of a certain node within the tree
value: int
an integer that holds the value of the node
children: list
list that holds all the child nodes of the current node
parent: TreeNode
it holds the parent node of a certain node
Methods
----------------
add_child(child)
adds a child node to the children list of the current node
fill_values(dictionary)
populates all the values of a tree using a dictionary with ledger transactions as input
extract_data(dic)
populates a dictionary with all the accounts and its values and returns it
"""
def __init__(self, account):
self.account = account
self.level = len(account.split('.'))
self.value = 0
self.children = []
self.parent = None
def add_child(self, child):
"""
this method adds a child node to current node
Parameters
-----------
child: TreeNode
"""
child.parent = self
self.children.append(child)
def fill_values(self, dictionary):
"""
this method adds a child node to current node. It implements a logic similar to a
postorder tree traversal.
Parameters
--------------
dictionary: dict
this dictionary is a result of summing up all the ledger transactions,
therefore it holds all the leaf nodes' accounts of the tree and its respective
values already summed up.
"""
if len(self.children) == 0:
self.value = dictionary[self.account]
# self.parent.value += self.value
else:
for child in self.children:
child.fill_values(dictionary)
self.value += child.value
def extract_data(self, dic):
"""
this method populates an empty dictionary with all the accounts and their respective values
Parameters
--------------
dic: dict
an empty dictionary
"""
dic[self.account] = self.value
if self.children:
for child in self.children:
child.extract_data(dic)
def build_tree(accounts_data):
"""
this method iterates through the chart of accounts data and generates a
tree data structure, in which all the nodes contain the account numbers and
their respective children, but still leave the node values as default (zero)
Parameters
-----------
accounts_data: DataFrame
DataFrame containing account numbers
Returns
----------
it returns the root node of the tree
"""
root = TreeNode("root")
# populating the tree
def evaluate_data(prev, current):
if prev.level < current.level:
return prev.add_child(current)
else:
evaluate_data(prev.parent, current)
for i in accounts_data.index:
myObj = TreeNode(str(accounts_data['account'][i]))
if myObj.level == 1:
root.add_child(myObj)
previous = myObj
else:
evaluate_data(previous, myObj)
previous = myObj
return root
def calculate_ledger(ledger_data):
"""
this method iterates through general ledger data and sums up the values of
all repeated accounts
Parameters
-----------
ledger_data: DataFrame
DataFrame containing account numbers and their respective total value
Returns
--------
it returns a dictionary containing all the accounts that were in the ledger
and their respective values
"""
# generating dictionary
dictx = {}
for i in ledger_data.index:
if str(ledger_data['account'][i]) not in dictx:
dictx[str(ledger_data['account'][i])] = ledger_data['value'][i]
else:
# dict[x['account'][i]] = dict.get(x['account'][i]) + x['value'][i]
dictx[str(ledger_data['account'][i])] += ledger_data['value'][i]
return dictx
def create_csv(data):
"""
this method iterates through a dictionary and then creates a .csv file corresponding to this data.
The csv file created is the final output of this challenge project.
Parameters
------------
data: dictionary
contains all the account numbers and their respective values
"""
f = open('chart_of_accounts.csv', "w", newline="")
writer = csv.writer(f)
for key in data:
if key != 'root':
writer.writerow([key, data[key]])
f.close()
def build_tree_database(accounts_data):
"""
this method iterates through the chart of accounts data and generates a
tree data structure, in which all the nodes contain the account numbers and
their respective children, but still leave the node values as default (zero)
Parameters
-----------
accounts_data: list of tuples
list of tuples containing account numbers
Returns
----------
it returns the root node of the tree
"""
root = TreeNode("root")
# populating the tree
def evaluate_data(prev, current):
if prev.level < current.level:
return prev.add_child(current)
else:
evaluate_data(prev.parent, current)
for element in accounts_data:
myObj = TreeNode(str(element[0]))
if myObj.level == 1:
root.add_child(myObj)
previous = myObj
else:
evaluate_data(previous, myObj)
previous = myObj
return root
def calculate_ledger_database(ledger_data):
"""
this method iterates through general ledger data and sums up the values of
all repeated accounts
Parameters
-----------
ledger_data: list of tuples
list of tuples containing account numbers and their respective total value
Returns
--------
it returns a dictionary containing all the accounts that were in the ledger
and their respective values
"""
# generating dictionary
dictx = {}
for element in ledger_data:
if str(element[0]) not in dictx:
dictx[str(element[0])] = element[1]
else:
dictx[str(element[0])] += element[1]
return dictx
def main():
""""
This function creates a .csv file according to the chart of accounts input and the general ledger input
Important!!
Need to change the next 3 lines accordingly
option = 1 -> data will come from relational database. If this option is chosen then the
filenames can be left as empty strings
option = 2 -> data will come from .xlsx files. If this option is chosen then the filenames
need to be filled appropriately
"""
excel_filename_chartofaccounts = 'chart_of_accounts.xlsx'
excel_filename_generalledger = 'general_ledger.xlsx'
option = 2
if option == 2:
root = build_tree(get_accounts_data(excel_filename_chartofaccounts, option))
dictx = calculate_ledger(get_ledger_data(excel_filename_generalledger, option))
root.fill_values(dictx)
data = {}
root.extract_data(data)
create_csv(data)
else:
root = build_tree_database(get_accounts_data(excel_filename_chartofaccounts, option))
dictx = calculate_ledger_database(get_ledger_data(excel_filename_chartofaccounts, option))
root.fill_values(dictx)
data = {}
root.extract_data(data)
create_csv(data)
main()