This repository has been archived by the owner on Mar 25, 2021. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
zeroDollarReport.py
133 lines (103 loc) · 3.3 KB
/
zeroDollarReport.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
import os
from dotenv import load_dotenv
from dbConnect import mssql
from tumblerLogging import getLogger
# get custom logger
logger = getLogger()
# TODO: switch this over to using the REST API
def reportQuery(months=1):
"""This will select the ids the false transactions."""
return f"""
DECLARE @reportEndDate AS DATE = GETDATE();
DECLARE @reportStartDate AS DATE = DATEADD(Month, -{months}, @reportEndDate);
SET NOCOUNT ON
SELECT
T.Id,
T.FinancialGatewayId,
T.TransactionCode,
P.FirstName + ' ' + P.LastName as Person,
T.TransactionDateTime
INTO #zero
FROM [FinancialTransaction] as T
JOIN [PersonAlias] as PA ON T.AuthorizedPersonAliasId = PA.Id
JOIN [Person] AS P ON PA.PersonId = P.Id
WHERE
T.Status <> 'Failed' AND
NOT EXISTS
(
SELECT *
FROM [FinancialTransactionDetail] as TD
WHERE TD.TransactionId = T.Id
) OR
EXISTS
(
SELECT *
FROM [FinancialTransactionDetail] as TD
WHERE
TD.TransactionId = T.Id AND
TD.Amount = 0
)
SELECT
Id,
TransactionCode as 'NMI Reference Number',
Person,
TransactionDateTime as 'Date'
FROM #zero
WHERE
FinancialGatewayId = 3 AND
TransactionDateTime >= @reportStartDate AND
TransactionDateTime <= @reportEndDate
"""
def getRowQuery(transID, table):
"""This will return a row from a table."""
return f"""
SELECT *
FROM {table}
WHERE Id = {transID}
"""
def deleteQuery(transID):
"""This will delete a transaction from the two financial tables."""
return f"""
DELETE
FROM FinancialTransaction
WHERE Id = {transID}
DELETE
FROM FinancialTransactionDetail
WHERE Id = {transID}
"""
def report(months=1, safe=True):
"""This will run the $0.00 Transaction Report and optionally fix all false transactions."""
# get database credentials
load_dotenv()
server = os.getenv("ROCK_MSSQL_HOST")
db = os.getenv("ROCK_MSSQL_DB")
user = os.getenv("ROCK_MSSQL_USER")
pw = os.getenv("ROCK_MSSQL_PW")
cnxn = mssql(server, db, user, pw)
cursor = cnxn.cursor()
# array of transactions to delete
transToDelete = []
# get ids of false transactions
cursor.execute(reportQuery(months))
row = cursor.fetchone()
while row:
# TODO: get logging working
logger.info(row)
print(row)
transToDelete.append(row[0])
row = cursor.fetchone()
if transToDelete == []:
print("Nothing to report.")
return 0
# delete transactions
print("Rows to be deleted...")
for transID in transToDelete:
# show rows that will be deleted
for table in ["FinancialTransaction", "FinancialTransactionDetail"]:
cursor.execute(getRowQuery(transID, table))
print(f"from {table}:")
print(cursor.fetchone())
if not safe:
cursor.execute(deleteQuery(transID))
cnxn.commit()
cnxn.close()