-
Notifications
You must be signed in to change notification settings - Fork 7
/
db.py
167 lines (140 loc) · 7.91 KB
/
db.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
#-*-coding=utf-8-*-
"""
Database wrapper for mikrobill
"""
#Post
import psycopg2, datetime
from re import escape
import os
from types import InstanceType, StringType, UnicodeType
class GpstTableException(Exception):
pass
class TraftransTableException(Exception):
pass
class dbRoutine(object):
@staticmethod
def execRoutine(*args, **kwargs):
'''@args[0] - method identifier'''
#add an opportunity to pass method name as a kwargs value
methodName = args[0]
#print methodName
self = 1
method = getattr(self, "db_" + methodName, None)
if callable(method):
try:
args = args[1:]
res = method(*args, **kwargs)
return res
except Exception, ex:
print "Exception upon executing dbRoutine #" + methodName + "# method: ", ex
raise ex
else:
raise Exception("dbRoutine method #" + args[0] + "# does not exist!" )
#Primitives
def transaction(cursor, account, approved, type, summ, description, created=None, bill='', tarif='Null', accounttarif='Null'):
#print 'new transaction'
if not created:
created=datetime.datetime.now()
#UPDATE billservice_account SET ballance=ballance-%s WHERE id=%s;
cursor.execute("""
INSERT INTO billservice_transaction(bill,
account_id, approved, type_id, tarif_id, accounttarif_id, summ, description, created)
VALUES (%s, %s, %s, %s, %s, %s, (-1)*%s, %s, %s) RETURNING id;
""" , (bill, account, approved, type, tarif, accounttarif, summ, description, created))
tr_id=cursor.fetchone()
if tr_id:
tr_id=tr_id[0]
return tr_id
def traffictransaction(cursor, traffictransmitservice_id, accounttarif_id, account_id, summ=0, created=None):
if not created:
created=datetime.datetime.now()
try:
cursor.execute("""INSERT INTO traftrans%s""" % created.strftime("%Y%m01")+"""(traffictransmitservice_id, accounttarif_id, account_id, summ, created) VALUES (%s, %s, %s, (-1)*%s, %s) RETURNING id;
""", (traffictransmitservice_id, accounttarif_id, account_id, summ, created,))
except psycopg2.ProgrammingError, e:
if e.pgcode=='42P01':
raise TraftransTableException()
else:
raise e
return cursor.fetchone()[0]
def radiustraffictransaction(cursor, radiustraffictransmitservice_id, accounttarif_id, account_id, summ=0, created=None):
if not created:
created=datetime.datetime.now()
try:
cursor.execute("""INSERT INTO traftrans%s""" % created.strftime("%Y%m01")+"""(radiustraffictransmitservice_id, accounttarif_id, account_id, summ, created) VALUES (%s, %s, %s, (-1)*%s, %s) RETURNING id;
""", (radiustraffictransmitservice_id, accounttarif_id, account_id, summ, created,))
except psycopg2.ProgrammingError, e:
if e.pgcode=='42P01':
raise TraftransTableException()
else:
raise e
return cursor.fetchone()[0]
def timetransaction(cursor, timeaccessservice_id, accounttarif_id, account_id, session_id, summ=0, created=None):
if not created:
created=datetime.datetime.now()
cursor.execute("""INSERT INTO billservice_timetransaction(timeaccessservice_id, accounttarif_id, account_id, session_id, summ, created) VALUES (%s, %s, %s, %s, (-1)*%s, %s);
""", (timeaccessservice_id, accounttarif_id, account_id, session_id, summ, created,))
def get_acctf_history(cursor, account_id):
"""
Выбрать текущий аккаунттариф и следующий, если на текущем тарифе ест период. услуги, но нет списаний ИЛИ списания есть, но нет финального списания
"""
cursor.execute("""SELECT id, date_trunc('second', datetime) as datetime, (SELECT min(id) FROM billservice_accounttarif WHERE account_id=at.account_id and
datetime>at.datetime) as next_accounttarif_id,
date_trunc('second', (SELECT datetime FROM billservice_accounttarif WHERE datetime>at.datetime and id=(SELECT min(id) FROM billservice_accounttarif WHERE account_id=at.account_id and datetime>at.datetime))) as next_date
, tarif_id
FROM billservice_accounttarif as at WHERE account_id=%s
and date_trunc('second', datetime)<now()
and
True = (SELECT True from billservice_periodicalservice as ps WHERE ps.tarif_id=at.tarif_id LIMIT 1 )
and
True =((SELECT True from billservice_periodicalservicelog WHERE accounttarif_id=at.id and last_billed=False LIMIT 1) or (SELECT count(*)=0 from billservice_periodicalservicelog WHERE accounttarif_id=at.id))
ORDER BY datetime;""", (account_id, ))
return cursor.fetchall()
def check_in_suspended(cursor, account_id, dttime):
cursor.execute("""
SELECT True from billservice_suspendedperiod where account_id=%s and %s between start_date and end_date;
""", (account_id, dttime))
return cursor.fetchone() is not None
def timetransaction_fn(cursor, timeaccessservice_id, accounttarif_id, account_id, summ=0, created=None, sessionid='', interrim_update=None):
if not created:
created=datetime.datetime.now()
if not interrim_update: interrim_update = created
cursor.execute("""SELECT timetransaction_insert(%s, %s, %s, (-1)*%s::decimal, %s::timestamp without time zone, %s::character varying(32), %s::timestamp without time zone);
""", (timeaccessservice_id, accounttarif_id, account_id, summ, created, sessionid, interrim_update))
def ps_history(cursor, ps_id, accounttarif, account_id, type_id, summ=0, created=None):
if not created:
created=datetime.datetime.now()
cursor.execute("""
INSERT INTO billservice_periodicalservicehistory(service_id, accounttarif_id, account_id, type_id, summ, created) VALUES (%s, %s, %s, %s, (-1)*%s, %s);
""", (ps_id, accounttarif, account_id, type_id, summ, created,))
def addon_history(cursor, addon_id, service_type, ps_id, accounttarif_id, account_id, type_id, summ=0, created=None):
if not created:
created=datetime.datetime.now()
cursor.execute("""
INSERT INTO billservice_addonservicetransaction(service_id, service_type, accounttarif_id, account_id, accountaddonservice_id,
summ, created, type_id) VALUES (%s, %s, %s, %s, %s, (-1)*%s, %s, %s);
""", (addon_id, service_type, accounttarif_id, account_id, ps_id, summ, created, type_id))
def get_last_checkout(cursor, ps_id, accounttarif, co_datetime=None):
if co_datetime:
cursor.execute("""
SELECT date_trunc('second', created) FROM billservice_periodicalservicehistory
WHERE service_id=%s AND accounttarif_id=%s AND created <= %s ORDER BY created DESC LIMIT 1
""" , (ps_id, accounttarif, co_datetime))
else:
cursor.execute("""
SELECT date_trunc('second', datetime) FROM billservice_periodicalservicelog
WHERE service_id=%s AND accounttarif_id=%s
""" , (ps_id, accounttarif,))
try:
return cursor.fetchone()[0]
except:
return None
def get_last_addon_checkout(cursor, accountaddonservice_id, accounttarif, co_datetime=None):
cursor.execute("""
SELECT date_trunc('second', created) FROM billservice_addonservicetransaction
WHERE accountaddonservice_id=%s ORDER BY created DESC LIMIT 1
""" , (accountaddonservice_id,))
try:
return cursor.fetchone()[0]
except:
return None