-
Notifications
You must be signed in to change notification settings - Fork 0
/
vouchers.py
173 lines (143 loc) · 6.58 KB
/
vouchers.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
from flask import redirect, render_template, request, url_for, flash
from sql import *
import ledgers
db = SQL("sqlite:///watchdog.db")
def getTaxrates():
rates = db.execute("""SELECT * FROM taxrates""")
return rates
def getSalesVoucherByInvNo(inv_no, company_id):
master_table = str(company_id) + '_master_sales'
secondary_table = str(company_id) + '_secondary_sales'
ledger_table = str(company_id) + '_ledgers'
rows = db.execute("""SELECT * FROM :table
INNER JOIN (SELECT id AS l_id,name,gstin FROM :ledger_table) ON l_id=ledger_id
WHERE inv_no = :inv_no""",
table=master_table,ledger_table=ledger_table,inv_no=inv_no)
if rows:
master_id = rows[0]['id']
secondary_data = db.execute("""SELECT * FROM :table WHERE master_id=:master_id""",
table=secondary_table, master_id=master_id)
voucher_data = dict(rows[0])
voucher_data['tax_data'] = secondary_data
return voucher_data
return []
def getSalesVoucherByMonth(month, year, company_id):
view_table = str(company_id) + '_sales_view'
ledger_table = str(company_id) + '_ledgers'
rows = db.execute("""SELECT * FROM :table
INNER JOIN (SELECT id AS l_id,name FROM :ledger_table) ON l_id=ledger_id
WHERE month=:month AND year=:year GROUP BY master_id
ORDER BY inv_no ASC""", table=view_table,ledger_table=ledger_table,
month=month, year=year)
return rows
def createSalesVoucher(request, company_id):
master_table = str(company_id) + '_master_sales'
secondary_table = str(company_id) + '_secondary_sales'
ledger_id = request.get('ledger_id')
ledger_data = ledgers.getLedgerById(ledger_id, company_id)
inv_no = request.get('inv_no')
date = request.get('date').split('-')
if len(date[0]) < 2:
day = '0' + date[0]
else:
day = date[0]
month = date[1]
year = date[2]
pos = ledger_data[0]['place_of_supply']
un_reg = ledger_data[0]['unregistered']
comp = ledger_data[0]['composition']
sez = ledger_data[0]['sez']
roundoff = request.get('roundoff')
request = dict(request)
# Check for duplicates
if len(getSalesVoucherByInvNo(inv_no, company_id)) > 0:
flash('Invoice Number must be unique','red')
return
# insert in master table
row_id = db.execute("""INSERT INTO :table
(ledger_id, day, month, year, inv_no, pos, comp, un_reg, sez, roundoff)
VALUES
(:ledger_id, :day, :month, :year, :inv_no, :pos, :comp, :un_reg, :sez, :roundoff)""",
table=master_table, ledger_id=ledger_id, day=day, month=month,year=year,
inv_no=inv_no, pos=pos, comp=comp, un_reg=un_reg, sez=sez, roundoff=roundoff)
if row_id is None:
flash('Server Error','red')
return
for rate,amount in zip(request.get('rate'),request.get('amount')):
db.execute("""INSERT INTO :table
(master_id, rate, amount) VALUES (:master_id, :rate, :amount)
""",table=secondary_table,master_id=row_id,rate=rate,amount=amount)
flash('Invoice Added','yellow')
def deleteSalesVoucher(voucher_id, company_id):
master_table = str(company_id) + '_master_sales'
secondary_table = str(company_id) + '_secondary_sales'
db.execute("""DELETE FROM :table WHERE id=:id""",table=master_table,
id=voucher_id)
db.execute("""DELETE FROM :table WHERE master_id=:id""",table= secondary_table,
id=voucher_id)
def getPurchaseVoucherByInvNo(inv_no, company_id):
master_table = str(company_id) + '_master_purchase'
secondary_table = str(company_id) + '_secondary_purchase'
ledger_table = str(company_id) + '_ledgers'
rows = db.execute("""SELECT * FROM :table
INNER JOIN (SELECT id AS l_id,name,gstin FROM :ledger_table) ON l_id=ledger_id
WHERE inv_no = :inv_no""",
table=master_table,ledger_table=ledger_table,inv_no=inv_no)
if rows:
master_id = rows[0]['id']
secondary_data = db.execute("""SELECT * FROM :table WHERE master_id=:master_id""",
table=secondary_table, master_id=master_id)
voucher_data = dict(rows[0])
voucher_data['tax_data'] = secondary_data
return voucher_data
return []
def getPurchaseVoucherByMonth(month, year, company_id):
view_table = str(company_id) + '_purchase_view'
ledger_table = str(company_id) + '_ledgers'
rows = db.execute("""SELECT * FROM :table
INNER JOIN (SELECT id AS l_id,name FROM :ledger_table) ON l_id=ledger_id
WHERE month=:month AND year=:year GROUP BY master_id
ORDER BY inv_no ASC""", table=view_table,ledger_table=ledger_table,
month=month, year=year)
return rows
def createPurchaseVoucher(request, company_id):
master_table = str(company_id) + '_master_purchase'
secondary_table = str(company_id) + '_secondary_purchase'
ledger_id = request.get('ledger_id')
ledger_data = ledgers.getLedgerById(ledger_id, company_id)
inv_no = request.get('inv_no')
date = request.get('date').split('-')
if len(date[0]) < 2:
day = '0' + date[0]
else:
day = date[0]
month = date[1]
year = date[2]
pos = ledger_data[0]['place_of_supply']
un_reg = ledger_data[0]['unregistered']
comp = ledger_data[0]['composition']
sez = ledger_data[0]['sez']
roundoff = request.get('roundoff')
request = dict(request)
# insert in master table
row_id = db.execute("""INSERT INTO :table
(ledger_id, day, month, year, inv_no, pos, comp, un_reg, sez, roundoff)
VALUES
(:ledger_id, :day, :month, :year, :inv_no, :pos, :comp, :un_reg, :sez, :roundoff)""",
table=master_table, ledger_id=ledger_id, day=day, month=month,year=year,
inv_no=inv_no, pos=pos, comp=comp, un_reg=un_reg, sez=sez, roundoff=roundoff)
if row_id is None:
flash('Server Error','red')
return
for rate,amount in zip(request.get('rate'),request.get('amount')):
db.execute("""INSERT INTO :table
(master_id, rate, amount) VALUES (:master_id, :rate, :amount)
""",table=secondary_table,master_id=row_id,rate=rate,amount=amount)
flash('Invoice Added','yellow')
def deletePurchaseVoucher(voucher_id, company_id):
master_table = str(company_id) + '_master_purchase'
secondary_table = str(company_id) + '_secondary_purchase'
db.execute("""DELETE FROM :table WHERE id=:id""",table=master_table,
id=voucher_id)
db.execute("""DELETE FROM :table WHERE master_id=:id""",table= secondary_table,
id=voucher_id)