def manageProfile():
form = bankProfileForm()
search_form = SearchForm()
username = session.get('username')
if session.get('username') == None:
return redirect(url_for('home'))
if form.profile_submit.data and form.validate_on_submit():
cursor.execute("SELECT * FROM AMLOfficer WHERE email = '" + form.email.data + "'")
data2 = cursor.fetchone()
if not (data2 is None):
flash('This Email is already exists please try another email', 'danger')
return render_template('ManageProfile.html', form=form , form2 = search_form )
else:
cur, db , engine = connection2()
cur.execute("UPDATE SMI_DB.AMLOfficer SET fullname = '" + form.fullName.data + "' , email = '" + form.email.data + "' , password = '******' WHERE userName = '******'" )
db.commit()
# bring info from database
cur1, db1, engine1 = connection2()
cur1.execute("SELECT * FROM SMI_DB.AMLOfficer WHERE userName = '******'")
data = cur1.fetchall()
for each in data:
form.fullName.data = each[2]
form.email.data = each[1]
if search_form.search_submit.data and search_form.validate_on_submit():
return redirect((url_for('searchResult', id= search_form.search.data , form2 = search_form )))
return render_template("ManageProfile.html", form=form , form2 = search_form )
def case(id):
# Only logged in users can access bank profile
if session.get('username') == None:
return redirect(url_for('home'))
search_form = SearchForm()
if search_form.search_submit.data and search_form.validate_on_submit():
return redirect((url_for('searchResult', id=search_form.search.data, form2=search_form)))
cur, db, engine = connection2()
cur.execute("UPDATE ClientCase SET viewed = '0' WHERE caseID=%s " % (id))
cur, db, engine = connection2()
cur.execute("SELECT * FROM SMI_DB.ClientCase WHERE caseID=%s " % (id))
data = cur.fetchall()
client_ID = data[0][3]
profileLabel=''
if data[0][1] == 'Low':#Need to change it Meduim
profileLabel ='label label-warning'
else:#High
profileLabel = 'label label-danger'
cur.execute("SELECT * FROM SMI_DB.Client WHERE clientID=%s " % ( client_ID))
data2 = cur.fetchall()
client_BR = data2[0][5]
Br_flag = True
print('Br', client_BR)
Br_dic = {}
if client_BR == '0000':
Br_flag = False
else:
if client_BR[0] == '1':
Br_dic['1'] = 'Client Name is in sanction list'
if client_BR[1] == '1':
Br_dic['2'] = 'Client location in risk contries'
if client_BR[2] == '1':
Br_dic['3'] = 'Client exceeded avg amount of transactions'
if client_BR[3] == '1':
Br_dic['4'] = 'Client exceeded max amount of transaction'
cur.execute("SELECT * FROM SuspiciousTransaction WHERE clientID=%s " % (client_ID))
transaction = cur.fetchall()
return render_template("case.html",data= data, data2= data2, label= profileLabel, clientId = id, transaction=transaction , Br_flag=Br_flag ,Br_dic=Br_dic)
def register():
form = RegistrationForm()
if form.validate_on_submit():
# Checking If the account(user_name) is already registered.
cursor.execute("SELECT * FROM AMLOfficer WHERE userName = '******'")
data1 = cursor.fetchone()
# Checking If the account(email) is already registered.
cursor.execute("SELECT * FROM AMLOfficer WHERE email = '" + form.email.data + "'")
data2 = cursor.fetchone()
if not (data1 is None):
flash('This Username is already registered please try another username', 'danger')
return render_template('Register.html', form=form)
if not (data2 is None):
flash('This Email is already registered please try another email', 'danger')
return render_template('Register.html', form=form)
else:
cur, db , engine = connection2()
query = "INSERT INTO AMLOfficer (userName, email, fullname, password) VALUES(%s,%s,%s,%s)"
val = (form.username.data, form.email.data, form.fullName.data, form.password.data)
cur.execute(query, val)
db.commit()
cur.close()
db.close()
session["username"] = form.username.data
session['email'] = form.email.data
flash(f'Account created for {session["username"]} Successfully !', 'success')
return redirect(url_for('bankP'))
return render_template('Register.html', form=form)
def updatePassword(self):
cur, db = connection2()
query = "UPDATE SMI_DB.AMLOfficer SET password ='******' WHERE email = '" + self.clientEmail + "'"
cur.execute(query)
db.commit()
cur.close()
db.close()
def caseTOprint(id):
# Only logged in users can access bank profile
if session.get('username') == None:
return redirect(url_for('home'))
search_form = SearchForm()
cur, db, engine = connection2()
cur.execute("SELECT * FROM SMI_DB.ClientCase WHERE caseID=%s " % (id))
data = cur.fetchall()
client_ID = data[0][3]
profileLabel = ''
if data[0][1] == 'Low': #Need to change it Meduim
profileLabel = 'label label-warning'
else: #High
profileLabel = 'label label-danger'
cur.execute("SELECT * FROM SMI_DB.Client WHERE clientID=%s " % (client_ID))
data2 = cur.fetchall()
cur.execute("SELECT * FROM SuspiciousTransaction WHERE clientID=%s " %
(client_ID))
transaction = cur.fetchall()
return render_template("caseTOprint.html",
data=data,
data2=data2,
label=profileLabel,
clientId=id,
transaction=transaction)
def savingTransaction(self):
'''database'''
self.cur, self.db, self.engine = connection2()
query1 = (
"SELECT * FROM SMI_DB.SuspiciousTransaction WHERE transactionID= '%s'"
% (self.suspiciousTransactions2['transactionID'])
) # pre suspiciousTransactions2
self.cur.execute(query1)
record = self.cur.fetchall()
#print(record, "****record***")
if not record: # Dosen't exisit
LOACTION = self.df['location']
NAMES = self.df['clientName']
transactionID = self.df['transactionID']
del self.df['location']
del self.df['clientName']
del self.df['transactionID']
self.df = self.df.rename(columns={'clientID': 'nameDest'})
self.df['isFruad_result'] = 1
self.df['location'] = LOACTION
self.df['clientName'] = NAMES
self.df['transactionID'] = transactionID
self.df = self.df.rename(columns={'nameDest': 'clientID'})
### Save results ####
#self.df.to_csv('predictionsResults.csv', encoding='utf-8', index=False)
#self.suspiciousTransactions2 = self.df.loc[(self.df.amount == self.max_amount)] # reding form csv file
#self.suspiciousTransactions2.to_csv('suspiciousTransactions.csv', encoding='utf-8', index=False)
#self.suspiciousTransactions2.to_sql(name='SuspiciousTransaction', con=self.engine, if_exists='append', index=False)
#print("*****ADDDEDDD****")
self.db.commit()
def DecisionTreeClassifier(self):
warnings.filterwarnings("ignore", category=DeprecationWarning)
from DBconnection import BankConnection, connection2
status, cur, db, engine = BankConnection()
cur.execute('SELECT * FROM Bank_DB.transaction')
testX = DataFrame(cur.fetchall())
testX.columns = cur.column_names
#print(testX.head())
#print(testX.isnull().values.any())
LOACTION = testX['location']
NAMES = testX['clientName']
TransID = testX['transactionID']
del testX['location']
del testX['clientName']
del testX['transactionID']
testX = testX.rename(columns={'clientID': 'nameDest'})
#testX = pd.read_csv('testingRecordes.csv')
######## SAVE THE MODEL ###########
'''fileName = 'SMI_MODEL.sav'
pickle.dump(clf, open(fileName,'wb'))'''
######## LOAD THE MODEL ###########
loaded_model = pickle.load(
open('/Users/Noura/Desktop/SMI-working/SMI_MODEL2.sav', 'rb'))
predict = loaded_model.predict(testX)
testX['isFruad_result'] = predict
testX['location'] = LOACTION
testX['clientName'] = NAMES
testX['transactionID'] = TransID
testX = testX.rename(columns={'nameDest': 'clientID'})
### Save predictions results ####
#testX.to_csv('predictionsResults.csv', encoding='utf-8', index=False)
### Saving Suspicious Transactions in the testing dataset ####
suspiciousTransactions = testX.loc[(testX.isFruad_result == 1)]
#suspiciousTransactions.to_csv('suspiciousTransactions.csv', encoding='utf-8', index=False)
cur1, db1, engine2 = connection2()
cur1.execute('DROP TABLE `SMI_DB`.`SuspiciousTransaction`')
db1.commit()
cur1.close()
db1.close()
suspiciousTransactions.to_sql(name='SuspiciousTransaction',
con=engine2,
if_exists='append',
index=False)
db.commit()
cur.close()
db.close()
def deleteProfile():
username = session.get('username')
cur, db, engine = connection2()
query = "DELETE FROM Comment WHERE officerName = '" + username + "'"
cur.execute(query)
query = "DELETE FROM AMLOfficer WHERE userName = '******'"
cur.execute(query)
return redirect(url_for('home'))
def cases():
search = False
q = request.args.get('q')
if q:
search = True
# Only logged in users can access bank profile
if session.get('username') == None:
return redirect(url_for('home'))
else:
cur, db, engine = connection2()
form = ViewCasesForm()
search_form = SearchForm()
per_page = 4
page = request.args.get(get_page_parameter(), type=int, default=1)
offset = (page - 1) * per_page
query = "SELECT * FROM SMI_DB.ClientCase "
cur.execute(query)
total = cur.fetchall()
countCases = len(total)
cur.execute(
"SELECT * FROM SMI_DB.ClientCase ORDER BY caseID DESC LIMIT %s OFFSET %s",
(per_page, offset))
cases = cur.fetchall()
if search_form.search_submit.data and search_form.validate_on_submit():
return redirect((url_for('searchResult',
id=search_form.search.data,
form2=search_form)))
if form.validate_on_submit():
# id = form.hidden.data
# id = request.form.get('case_submit')
id = request.form['caseView']
# id2 = request.form['caseDownload']
print(id)
return redirect((url_for('case', id=id)))
pagination = Pagination(page=page,
per_page=per_page,
total=len(total),
offset=offset,
search=search,
record_name='cases',
css_framework='bootstrap3')
return render_template("cases.html",
cases=cases,
form=form,
form2=search_form,
pagination=pagination,
css_framework='foundation',
caseId=0,
countCases=countCases)
def login():
form = LoginForm()
if form.validate_on_submit():
# Check id user exisit in the database
cur, db, enginec = connection2()
cur.execute("SELECT COUNT(1) FROM AMLOfficer WHERE userName = %s;",
[form.username.data]) # CHECKS IF USERNAME EXSIST
if cur.fetchone()[0]:
cur.execute("SELECT password FROM AMLOfficer WHERE userName = %s;",
[form.username.data]) # FETCH THE HASHED PASSWORD
for row in cur.fetchall():
if form.password.data == row[0]:
session['username'] = form.username.data
query2 = "SELECT email FROM AMLOfficer WHERE userName = '******'"
cur.execute(query2)
useremail = cur.fetchone()
session["email"] = useremail
cur.execute(
"UPDATE SMI_DB.AMLOfficer SET numOfFailedLogin=%s WHERE userName='******' "
% (0, form.username.data)
) #SUCCESSFUL LOGIN SET #ofTries to zero
db.commit()
flash(f'Welcome back {form.username.data}', 'success')
return redirect(url_for('bankP'))
else:
cur.execute(
"SELECT numOfFailedLogin FROM AMLOfficer WHERE userName = %s;",
[form.username.data]) # FETCH THE HASHED PASSWORD
for row in cur.fetchall():
if row[0] == 3:
flash(
'Sorry You have entered your password 3 times wrong.. Enter your email for validation to reset your password',
'danger')
return redirect(url_for('forgotPass'))
else:
cur.execute(
"UPDATE SMI_DB.AMLOfficer SET numOfFailedLogin= numOfFailedLogin+1 WHERE userName='******' "
% (form.username.data)
) # SUCCESSFUL LOGIN SET #ofTries to zero
db.commit()
flash('Wrong Password try again!', 'danger')
else:
flash('Invalid Username try again!', 'danger')
db.commit()
cur.close()
db.close()
#task = long_task.apply_async()
return render_template('login.html', form=form)
def bankP():
if session.get('username') == None:
return redirect(url_for('home'))
cur, db, engine = connection2()
query = "SELECT * FROM SMI_DB.ClientCase WHERE viewed ='1'"
cur.execute(query)
totalAlert = cur.fetchall()
totalAlert = len(totalAlert)
print(totalAlert)
form = SearchForm()
if form.validate_on_submit():
return redirect((url_for('searchResult', id= form.search.data))) # or what you want
return render_template("bankProfile.html", form = form, alert = totalAlert)
def __init__(self):
self.cur, self.db, self.engine = connection2()
self.clientIDs = []
self.query = "SELECT * FROM bank_db.transaction"
self.cur.execute(self.query)
recored = self.cur.fetchall()
self.df = DataFrame(recored)
self.df.columns = self.cur.column_names
self.numOfRules = 0
self.sumOfFlags = 0
self.saving_transaction = pd.DataFrame(
) #for saving client suspsious transactions
self.transaction_IDs = []
for each in recored:
self.clientIDs.append(each[7])
def forgotPass():
form = forgotPassForm()
if form.validate_on_submit():
# Check id user exisit in the database
cur, db , engine = connection2()
query = "SELECT * FROM SMI_DB.AMLOfficer WHERE email ='" + form.email.data + "'"
cur.execute(query)
data1 = cur.fetchone()
if (data1 is None):
flash('This email is not registered in our system ', 'danger')
return render_template('forgotPassword.html', form=form)
else:
a = passwordRecovery(form.email.data)
a.sendEmail()
flash('A recovery password has been sent to your email', 'success')
return render_template('forgotPassword.html', form=form)
return render_template("forgotPassword.html", form=form)
def searchResult(id):
# Only logged in users can access bank profile
if session.get('username') == None:
return redirect(url_for('home'))
else:
cur, db , engine = connection2()
query = "SELECT * FROM SMI_DB.Client WHERE clientID = '" + id + "'"
cur.execute(query)
data = cur.fetchall()
form = ViewProfileForm()
search_form = SearchForm()
if form.view_submit.data and form.validate_on_submit():
return redirect((url_for('clientProfile', id = id , form = form )))
if search_form.search_submit.data and search_form.validate_on_submit():
return redirect((url_for('searchResult', id=search_form.search.data , form2 = search_form)))
return render_template("searchResult.html", data=data, form=form , form2 = search_form)
def rules_mapping(self, Firstoperand, nameDest):
self.nameDest = nameDest
self.Firstoperand = Firstoperand
ValueOfTheAtt = pd.DataFrame()
print(self.nameDest)
'''database'''
self.cur, self.db, self.engine = connection2()
query2 = ("SELECT * FROM bank_db.transaction WHERE clientID= '%s'" % (
self.nameDest)) # list all transaction for this id
self.cur.execute(query2)
record2 = self.cur.fetchall()
self.df = DataFrame(record2)
self.df.columns = self.cur.column_names
if Firstoperand in self.df.columns: # need to check if Firstoperand exist in the database
ValueOfTheAtt = self.df[Firstoperand]
else:
print("ERROR The column dosen't exist in the database")
return ValueOfTheAtt
def manageProfile():
form = bankProfileForm()
search_form = SearchForm()
username = session.get('username')
if session.get('username') == None:
return redirect(url_for('home'))
if form.profile_submit.data and form.validate_on_submit():
cur, db, engine = connection2()
cur.execute("UPDATE SMI_DB.AMLOfficer SET fullname = '" +
form.fullName.data + "' , email = '" + form.email.data +
"' , userName = '******', password = '******' WHERE userName = '******'")
db.commit()
if search_form.search_submit.data and search_form.validate_on_submit():
return redirect((url_for('searchResult',
id=search_form.search.data,
form2=search_form)))
return render_template("ManageProfile.html", form=form, form2=search_form)
def __init__(self):
'''database'''
self.cur, self.db, self.engine = connection2()
self.ADestID = []
'''
self.ADestname = []
self.Alocation = []
self.Aamount = []
self.Aavg = []
self.AtrnID = []
self.suspiciousTransactions2= []
'''
query2 = ("SELECT * FROM bank_db.transaction")
self.cur.execute(query2)
record2 = self.cur.fetchall()
self.df = DataFrame(record2)
self.df.columns = self.cur.column_names
for column2 in record2:
self.ADestID.append(column2[7]) # list with client id
'''
def Detect(self, businessRule_ID):
cur1, db1, engine1 = connection2() #SMI_DB
status, cur2, db2, engine2 = BankConnection() #bank_DB
self.businessRule_ID = businessRule_ID #0 from template , 1 bank uplaoded their rules
'''Firebase'''
firebase = firebaseConnection()
db = firebase.database()
if self.businessRule_ID == 0: #from template
self.risk_countries = db.child('Rule1').child(
'highRiskCountries').get().val()
self.sanction_list = db.child('Rule4').child(
'blackList').get().val()
self.exceed_avg_tran = db.child('Rule2').child(
'exceedingAvgTransaction').get().val()
self.amount = db.child('Rule3').child(
'suspiciousTransaction').child('amount').get().val()
if self.businessRule_ID == 1: # from bank file
self.Rules = db.child('Rules').get().val()
self.sanctionList = db.child('sanctionList').get().val()
self.highRiskCountries = db.child('highRiskCountries').get().val()
self.MultiLevelRules = db.child('MultiLevelRules').get().val()
'''df = pd.read_csv('GeneratedDataset.csv')
df.to_sql(name='transaction', con=engine2, if_exists='append',
index=False)'''
classifier = DecisionTree()
classifier.DecisionTreeClassifier()
#MULTI CERTIREA
#Create profiles
cur2.execute('SELECT clientID, clientName FROM bank_db.transaction')
result = list(set(list(cur2.fetchall())))
cur2.close()
db2.close()
#get client who flagged suspious transactions
cur1.execute('SELECT clientName FROM SMI_DB.SuspiciousTransaction')
suspsuoiusClient = list(set(list(cur1.fetchall())))
i = 1
numOFclean = 0
numOFLow = 0
numOfHigh = 0
numOFMeduim = 0
for id, name in result:
dt_class = 0
mc_class = 0
transaction_class = 0
profile_score = 0
profile_class = 'clean'
GeneralSearch_result = 0
GeneralSearch_class = 0
NumberOfRecord = 0
weightTree = 0.5
if self.businessRule_ID == 0: # from template
mc = MultiCriteria()
mc_class = mc.multi_criteria(id, self.risk_countries,
self.sanction_list,
self.exceed_avg_tran, self.amount)
if self.businessRule_ID == 1: #from bank file
mc = NewMultiCriteria()
mc_class = mc.getRules(self.Rules, self.sanctionList,
self.highRiskCountries,
self.MultiLevelRules, id)
print('mc_class', mc_class)
print('mc_class type', type(mc_class))
# If the client has any suspsuoius transaction run general search
if any(name in s for s in suspsuoiusClient):
dt_class = 1
'''search = GeneralSearch('"' + name + '"', id)
search.twitter_search()
GeneralSearch_result, GeneralSearch_class = search.google_search()'''
if (name in s for s in suspsuoiusClient
): #counter for the number of suspsuoius transactions
NumberOfRecord = NumberOfRecord + 1
if NumberOfRecord < 1:
weightTree = 0.75
try:
transaction_class = ((weightTree * dt_class) +
((1 - weightTree) * mc_class) / 2)
except ZeroDivisionError:
transaction_class = 0
profile_score = (0.5 * transaction_class) + (0.5 *
GeneralSearch_result)
if 0.7 < profile_score <= 1:
profile_class = 'High'
numOfHigh = numOfHigh + 1
elif 0.33 < profile_score <= 0.7:
profile_class = 'Medium'
numOFMeduim = numOFMeduim + 1
elif 0 < profile_score <= 0.33:
profile_class = 'Low'
numOFLow = numOFLow + 1
else:
profile_class = 'Clean'
numOFclean = numOFclean + 1
print('client ID: ', id)
print('client Name: ', name)
print('Multi Criteria Score: ', mc_class)
#print('Before IF statment', profile_class)
print('search_result: ', GeneralSearch_result)
print('transaction_class: ', transaction_class)
print('GeneralSearch_class: ', GeneralSearch_class)
print('profile class: ', profile_class)
print('***********************')
if (profile_class == 'High') or (profile_class == 'Medium'):
date_now = datetime.now()
formatted_date = date_now.strftime('%Y-%m-%d %H:%M:%S')
query = "INSERT INTO ClientCase (caseClassification, date, clientID) VALUES(%s,%s, %s)"
val = (profile_class, formatted_date, id)
cur1.execute(query, val)
cur1.execute(
"UPDATE SMI_DB.Client SET profileClassification= '%s'WHERE clientID='%s' "
% (profile_class, id))
cur1.close()
db1.close()
print('Summary:')
print('************')
print('Total Number of clients', len(result))
print('Number of clean clients:', numOFclean)
print('Number of Low clients:', numOFLow)
print('Number of Meduim clients:', numOFMeduim)
print('Number of High clients:', numOfHigh)
def Report(id):
# Only logged in users can access bank profile
if session.get('username') == None:
return redirect(url_for('home'))
form = reportCase()
cur, db, engine = connection2()
cur.execute("SELECT * FROM SMI_DB.ClientCase WHERE caseID=%s " % (id))
record = cur.fetchall()
client_ID = record[0][3]
caseNumber = ''
caseDate = ''
for each1 in record:
caseNumber = each1[0]
caseDate = each1[2]
profileLabel = ''
if record[0][1] == 'Low': # Need to change it Meduim
profileLabel = 'label label-warning'
else: # High
profileLabel = 'label label-danger'
label_name = record[0][1]
# --------------------#----------------------#--------------------------#-------------#
query1 = "SELECT * FROM SMI_DB.SuspiciousTransaction WHERE clientID=%s " % (
client_ID)
cur.execute(query1)
record1 = cur.fetchall()
transaction_number = ''
transaction_type = ''
transaction_amount = ''
transaction_location = ''
old_balance = ''
new_balance = ''
for each in record1:
transaction_number = each[15]
transaction_type = each[1]
transaction_amount = each[2]
transaction_location = each[13]
old_balance = each[7]
new_balance = each[8]
query2 = "SELECT * FROM SMI_DB.Client WHERE clientID=%s " % (client_ID)
cur.execute(query2)
record2 = cur.fetchall()
clientName = ''
for each in record2:
clientName = each[1]
rendered = render_template('CaseToPrint.html',
clientName=clientName,
caseNumber=caseNumber,
caseDate=caseDate,
label=profileLabel,
label_name=label_name,
transaction_number=transaction_number,
transaction_type=transaction_type,
transaction_amount=transaction_amount,
transaction_location=transaction_location,
old_balance=old_balance,
new_balance=new_balance)
#######save case to working dierctory ##########
pdfFile = pdfkit.from_string(rendered, 'case.pdf')
#request.files.get('file_case') = pdfFile
#request.form['file_case'] = pdfFile
form.subject.data = 'Case#{}_{}'.format(id, clientName)
form.email_body.data = 'Fruad Report'
if form.validate_on_submit():
'''target = os.path.join(APP_ROOT, 'Case_file/')
print('target',target)
if not os.path.isdir(target):
os.mkdir(target)
file = request.files.get('file_case')
print('file',file)
filename = file.filename
print('fileNAME',filename)
dest = "/".join([target, filename])
print(dest)
file.save(dest)'''
recipient = form.reciver.data
msg = Message(form.subject.data, recipient.split())
msg.body = form.email_body.data
with app.open_resource("case.pdf") as fp:
msg.attach("case.pdf", "case/pdf", fp.read())
print(msg)
mail.send(msg)
flash('Email has been sent Successfully..', 'success')
return render_template("email.html", form=form, clientID=id)
def download(id):
cur, db, engine = connection2()
query = "SELECT * FROM SMI_DB.ClientCase WHERE caseID = '" + id + "'"
cur.execute(query)
record = cur.fetchall()
client_ID = record[0][3]
caseNumber = ''
caseDate = ''
for each1 in record:
caseNumber = each1[0]
caseDate = each1[2]
profileLabel = ''
if record[0][1] == 'Low': # Need to change it Meduim
profileLabel = 'label label-warning'
else: # High
profileLabel = 'label label-danger'
label_name = record[0][1]
#--------------------#----------------------#--------------------------#-------------#
query1 = "SELECT * FROM SMI_DB.SuspiciousTransaction WHERE clientID=%s " % (client_ID)
cur.execute(query1)
transaction = cur.fetchall()
# --------------------#----------------------#--------------------------#-------------#
query2 = "SELECT * FROM SMI_DB.Client WHERE clientID=%s " % (client_ID)
cur.execute(query2)
record2 = cur.fetchall()
clientName = ''
for each in record2:
clientName = each[1]
client_BR = record2[0][5]
Br_flag = True
print('Br', client_BR)
Br_dic = {}
if client_BR == '0000':
Br_flag = False
else:
if client_BR[0] == '1':
Br_dic['1'] = 'Client Name is in sanction list'
if client_BR[1] == '1':
Br_dic['2'] = 'Client location in risk contries'
if client_BR[2] == '1':
Br_dic['3'] = 'Client exceeded avg amount of transactions'
if client_BR[3] == '1':
Br_dic['4'] = 'Client exceeded max amount of transaction'
rendered = render_template('CaseToPrint.html' , clientName = clientName , caseNumber = caseNumber ,caseDate = caseDate,label = profileLabel ,label_name = label_name , transaction = transaction ,Br_flag=Br_flag ,Br_dic=Br_dic )
pdf = pdfkit.from_string(rendered, False)
response = make_response(pdf)
response.headers['Content-Type'] = 'application/pdf'
response.headers['Content-Disposition'] = 'attachment; filename=case.pdf'
return response
def download(id):
cur, db, engine = connection2()
query = "SELECT * FROM SMI_DB.ClientCase WHERE caseID = '" + id + "'"
cur.execute(query)
record = cur.fetchall()
client_ID = record[0][3]
caseNumber = ''
caseDate = ''
for each1 in record:
caseNumber = each1[0]
caseDate = each1[2]
profileLabel = ''
if record[0][1] == 'Low': # Need to change it Meduim
profileLabel = 'label label-warning'
else: # High
profileLabel = 'label label-danger'
label_name = record[0][1]
# --------------------#----------------------#--------------------------#-------------#
query1 = "SELECT * FROM SMI_DB.SuspiciousTransaction WHERE clientID=%s " % (
client_ID)
cur.execute(query1)
record1 = cur.fetchall()
transaction_number = ''
transaction_type = ''
transaction_amount = ''
transaction_location = ''
old_balance = ''
new_balance = ''
for each in record1:
transaction_number = each[15]
transaction_type = each[1]
transaction_amount = each[2]
transaction_location = each[13]
old_balance = each[7]
new_balance = each[8]
query2 = "SELECT * FROM SMI_DB.Client WHERE clientID=%s " % (client_ID)
cur.execute(query2)
record2 = cur.fetchall()
clientName = ''
for each in record2:
clientName = each[1]
rendered = render_template('CaseToPrint.html',
clientName=clientName,
caseNumber=caseNumber,
caseDate=caseDate,
label=profileLabel,
label_name=label_name,
transaction_number=transaction_number,
transaction_type=transaction_type,
transaction_amount=transaction_amount,
transaction_location=transaction_location,
old_balance=old_balance,
new_balance=new_balance)
pdf = pdfkit.from_string(rendered, False)
#######save case to working dierctory ##########
#pdfkit.from_string(rendered, 'case.pdf')
response = make_response(pdf)
response.headers['Content-Type'] = 'application/pdf'
response.headers['Content-Disposition'] = 'attachment; filename=case.pdf'
return response
fh.close()
cursor.close()
except Exception as e:
print(str(e))'''
'''try:
cursor, conn = connection()
q = 'INSERT INTO KeyWord(wordID,word) VALUES (%s, %s)'
val = (84, 'test')
cursor.execute(q, val)
cursor.close()
except Exception as e:
print(str(e))'''
cur, db =connection2()
fh = open('searchFiles/"حجاج العجمي".txt', 'r')
while True:
i=0
line = fh.readline()
print(line)
# check if line is not empty
if not line:
break
query = 'INSERT INTO generalSearch (searchDate, searchContent) VALUES(%s, %s)'
val = (formatted_date, line)
cur.execute(query,val)
from DBconnection import firebaseConnection, connection2
import pandas as pd
from pandas import DataFrame
'''firebase = firebaseConnection()
fb = firebase.database()
isFB_Connected = fb.child().get().val()
print(isFB_Connected)'''
cur, db, engine = connection2()
clientIDs = []
ID = 644345897
q = "SELECT * FROM bank_db.transaction"
cur.execute(q)
recored = cur.fetchall()
df = DataFrame(recored)
df.columns = cur.column_names
for each in recored:
clientIDs.append(each[7])
print(clientIDs)
client_df = df[df['clientID'] == ID].drop_duplicates(keep='first')
def clientProfile(id):
client_form = clientForm()
new_comment = newCommentForm()
old_comment = oldCommentForm()
cur, db , engine = connection2()
# Only logged in users can access bank profile
if session.get('username') == None:
return redirect(url_for('home'))
else:
#Retrive client Info from database:
query = "SELECT * FROM SMI_DB.Client WHERE clientID = '" + id + "'"
cur.execute(query)
record = cur.fetchall()
result=[]
for column in record:
client_form.clientID.data = column[0] #clientID
client_form.clientName.data = column[1] #clientName
client_form.clientSalary.data = column[2] #clientSalary
client_form.clientClass.data = column[3] #clientClass
cur, db , engine= connection2()
query = "SELECT * FROM SMI_DB.Comment WHERE clientID = '" + id + "'"
cur.execute(query)
record = cur.fetchall()
#if not (record is None) :
#for column in record:
#old_comment.PrecommentDate.data = column[2] #comment date
#old_comment.PrecommentContent.data = column[1] #comment body
#return render_template("clientProfile.html", clientForm=client_form, commentForm=new_comment,oldCommentForm=old_comment)
if new_comment.add_submit.data and new_comment.validate_on_submit():
print("works")
cur, db, engine = connection2()
date_now = datetime.now()
formatted_date = date_now.strftime('%Y-%m-%d %H:%M:%S')
query = "INSERT INTO SMI_DB.Comment (commentBody, commentDate, clientID, officerName ) VALUES(%s,%s,%s,%s)"
val = (new_comment.commentBody.data, formatted_date, id, session['username'])
print(new_comment.commentBody.data)
cur.execute(query, val)
db.commit()
cur.close()
db.close()
return redirect(url_for('clientProfile', commentForm=new_comment, id=id))
print(old_comment.delete.data)
if old_comment.validate_on_submit():
print('the delete works')
cur, db , engine = connection2()
id1 = request.form['Delete_comment']
print(id)
print(id1)
query = "DELETE FROM SMI_DB.Comment WHERE commentID = '" + id1 + "'"
cur.execute(query)
return redirect(url_for('clientProfile' , oldCommentForm=old_comment , id1 = id1 , id = id))
return render_template("clientProfile.html", clientForm = client_form, commentForm = new_comment , record = record , oldCommentForm=old_comment)
def calculate_TFIDF(self):
'''
this function calculate the number of keywords appeared form the list
:param list:
:return:
'''
numOfApearance = 0
sum_of_frequencies = 0
max_frequency = 0
SearchClass = 0
clientClass = ''
for keys, values in self.keyWords.items():
if values > 0:
max_frequency = max(self.keyWords.values())
sum_of_frequencies += values
numOfApearance += 1
try:
SearchClass = ((numOfApearance * max_frequency) / (16))
#Normalization
if SearchClass > 1:
SearchClass = 1
except ZeroDivisionError:
SearchClass = 0
if 0.7 < SearchClass <= 1:
clientClass = 'High'
elif 0.33 < SearchClass <= 0.7:
clientClass = 'Medium'
elif 0 < SearchClass <= 0.33:
clientClass = 'Low'
else:
clientClass = 'Clean'
print('Number of Words apeared from the list', numOfApearance)
print('MAX FREQUENCY', max_frequency)
print('SUM OF Frequeancy', sum_of_frequencies)
print('SearchClass Weight', SearchClass)
print('Client Class', clientClass)
print('JSON Format')
print(self.cacheResult)
#with open("searchfiles/HajajAlajmi.json", "w") as write_file:
#json.dump(self.cacheResult, write_file)
#SAVING BEFORE CLEANING
'''f = open("searchfiles/%s.txt" % (self.clientName), "w+")
for each in self.tweets:
f.write(each)
for each in self.ResultGoogle:
f.write(each)
f.close()'''
date_now = datetime.now()
formatted_date = date_now.strftime('%Y-%m-%d %H:%M:%S')
cur, db, engine = connection2()
#SAVING AFTER CLEANING
f = open("searchfiles/%s.txt" % (self.clientName), "w+")
for each in self.cleanText:
f.write(each + '\n')
query = 'INSERT INTO generalSearch (searchDate, searchContent) VALUES(%s, %s)'
val = (formatted_date, each)
cur.execute(query, val)
f.close()
db.commit()
cur.close()
db.close()
