def checkCurrentEmployer(user1, user2):
sql1 = '''
SELECT userid, employerid
FROM miscellaneous.UserEmploymentDetails
WHERE userid = {}
ORDER BY LastUpdatedOn
LIMIT 1
'''.format(user1['userid'])
sql2 = '''
SELECT userid, employerid
FROM miscellaneous.UserEmploymentDetails
WHERE userid = {}
ORDER BY LastUpdatedOn
LIMIT 1
'''.format(user2['userid'])
try:
User1Employer = ah_db.execute_to_json('miscellaneous', sql1)
User2Employer = ah_db.execute_to_json('miscellaneous', sql2)
if len(User2Employer) > 0:
employerid1 = User1Employer[0]['employerid']
employerid2 = User2Employer[0]['employerid']
if employerid1 == employerid2:
return 1
else:
return 0
else:
return 1
except:
return 1
def checkUsername(user, r):
# Check if username is email or phone number
isphone = usernameType(user['UserName'])
if isphone == 1:
pass
else:
sql = '''SELECT userid, UserName
FROM miscellaneous.users
WHERE userid != {0} AND (FirstName = "{1}" OR LastName = "{2}") AND statusid != 6
'''.format(user['userid'], user['FirstName'], user['LastName'])
matcheduser = ah_db.execute_to_json('miscellaneous', sql)
email = getEmailLocal(user['UserName'])
if len(matcheduser) > 0:
for m in matcheduser:
memail = getEmailLocal(m['UserName'])
# print (user['UserName'],m['UserName'],email,memail,editdistance.eval(memail,email))
# check longest substring
if editdistance.eval(memail, email) < 2:
if checkCurrentEmployer(user, m) == 1:
if checkzip(user, m) == 1:
r.score = 1
r.reasonCode = IDRiskReason.EmailName
r.ids.append(m['userid'])
return
def getData(self):
sql = '''
SELECT userid, AuditTypeId,auditapplicationid, username, CreatedOn
FROM audit
where userid={}
order by createdon
'''.format(self.uid)
return ah_db.execute_to_json('miscellaneous', sql)
def getData(self):
sql = '''
SELECT UserName, FirstName, LastName, CreatedOn, IdentityRiskUserName
FROM Users
WHERE userid=%s
'''
return ah_db.execute_to_json('miscellaneous', sql, (self.uid, ))
def data_activation(self, uid, startDate=None, endDate=None):
sql='''
SELECT userid,(case when IsRestoreFailureAch=1 or isRestoreFailureTransactionStatus=1 or recoverycount>0 then 1 else 0 end) as IsFail,IsLoss, RestoreReturnCodeAch,RestoreReturnDate, CAST(requestdate AS DATETIME) + CAST(requesttime AS DATETIME) as RequestTime, case when requesttime<'06:00:00' then 1 else 0 end as IsNight,ActivationDate, IsFirstRestore, platform as OS, (case when (isActivationFailureAch=1) or (IsCancelled=1) or (isError=1) then 1 else 0 end ) as ActFail, CAST(Amount as Float) as Amount, IsTipFailureTransactionStatus as IsTipFail,CAST(TipAmount as Float) as TipAmount, CAST(RestoreDate AS DATETIME) RestoreDate, IsLightningPay
FROM Analysis.Activations
where userid=%d and IsPending=0 and RestoreDate<DATEADD(week, -1, getdate())
order by RequestTime
'''
return ah_db.execute_to_json('sqlserver', sql, uid)
def getData(self):
sql = '''
SELECT userid, employerid, paytypeid,
LastUpdatedOn AS CreatedOn
FROM UserEmploymentDetails
WHERE userid=%s
ORDER BY LastUpdatedOn
'''
return ah_db.execute_to_json('miscellaneous', sql, (self.uid, ))
def getData(self):
sql = '''
SELECT userid, d.CreatedOn, PhoneNumber, DeviceTypeId, OS, Jailbroken, DevicePhoneNumber
FROM UserDevices ud
LEFT JOIN devices d
ON ud.deviceid=d.deviceid
WHERE userid=%s
ORDER BY ud.createdon
'''
return ah_db.execute_to_json('miscellaneous', sql, (self.uid, ))
def getData(self):
sql = '''
SELECT up.userid, HourlyRate, PayrollStatusId, up.CreatedOn, up.LastUpdatedOn, SetupBy, pt.PaycycleFrequencyId
FROM payroll.UserToPayrollHistory up
LEFT JOIN payroll.PayrollDefinition pd ON up.PayrollDefinitionId=pd.payrolldefinitionid
LEFT JOIN payroll.PayDateTypes pt ON pd.PayDateTypeId=pt.PayDateTypeId
WHERE up.userid=%s
ORDER BY up.LastUpdatedOn
'''
return ah_db.execute_to_json('payroll', sql, (self.uid, ))
def getDataFrame(self):
sql_curr = '''
select MaxLimit
from users
where userid={}
'''.format(self.UserId)
currMax = pd.DataFrame(ah_db.execute_to_json('miscellaneous',
sql_curr))
sql_prev = '''
SELECT MaxLimitBefore, MaxLimitAfter, AdjustmentAmount, createdon
FROM usermodelmaxadjustmentresulthistory
WHERE userid={}
ORDER BY createdon
'''.format(self.UserId)
prevMax = pd.DataFrame(ah_db.execute_to_json('risk', sql_prev))
return currMax, prevMax
def checkPhone(user, r):
r = IdentityRisk()
for u in user:
if u['OS'] == 'android':
u['DevicePhoneNumber'][-10:]
sql = '''SELECT DISTINCT u.userid
FROM miscellaneous.devices d
LEFT JOIN miscellaneous.UserDevices ud ON d.deviceid = ud.DeviceID
LEFT JOIN miscellaneous.users u ON ud.userid = u.userid
WHERE u.userid != {0} AND u.statusid != 6 AND DevicePhoneNumber LIKE '{1}'"
'''.format(u['userid'], u['DevicePhoneNumber'][-10:])
matcheduser = ah_db.execute_to_json('miscellaneous', sql)
if len(matcheduser) > 0:
r.score = 1
r.reasonCode = IDRiskReason.Device
r.ids = [m['userid'] for m in matcheduser]
return r
return r
def checkDevice(user, r):
DeviceStaticId = {}
# android = {'unknown': 1, '0123456789ABCDEF': 1} # need special handing in the future
for u in user:
if u['DeviceStaticId'] not in DeviceStaticId:
DeviceStaticId[u['DeviceStaticId']] = 1
sql = '''SELECT DISTINCT u.userid
FROM miscellaneous.devices d
LEFT JOIN miscellaneous.UserDevices ud ON d.deviceid = ud.DeviceID
LEFT JOIN miscellaneous.users u ON ud.userid=u.userid
WHERE u.userid != {0} AND u.statusid != 6
AND DeviceStaticId = '{1}' AND d.OS = '{2}'
'''.format(u['userid'], u['DeviceStaticId'], u['OS'])
matcheduser = ah_db.execute_to_json('miscellaneous', sql)
if len(matcheduser) > 0:
r.score = 1
r.reasonCode = IDRiskReason.Device
r.ids = [m['userid'] for m in matcheduser]
return
def checkIdentity(uid):
sql = '''
SELECT u.userid, UserName, FirstName, LastName, OS, Serial,
Jailbroken, DevicePhoneNumber, DeviceStaticId
FROM miscellaneous.users u
LEFT JOIN miscellaneous.UserDevices ud ON ud.userid = u.userid
LEFT JOIN miscellaneous.devices d ON d.deviceid = ud.DeviceID
WHERE u.userid = {} AND (Serial IS NOT NULL OR DeviceStaticId IS NOT NULL)
AND (Serial != '' OR DeviceStaticId != '')
'''.format(uid)
rows = ah_db.execute_to_json('miscellaneous', sql)
r = IdentityRisk(uid)
if len(rows) > 0:
checkDevice(rows, r)
if r.score < 1:
remail = checkUsername(rows[0], r)
else:
r.score = 1
r.reasonCode = IDRiskReason.NoDeviceInfoOrNoUserid
return r
def getData(self):
sql = '''
SELECT DISTINCT da.ActivationId
, MAX(da.UserId) UserId
, MAX((CASE WHEN (ta.FundsTransferStatusId = 5 AND tt.TypeName = 'Restore') OR (biiad.ReturnReasonCode LIKE 'R%%' AND tt.TypeName = 'Restore') THEN 1 ELSE 0 END)) IsFail
, COALESCE(MIN(CASE ##WHEN (tt.TypeName = 'Recovery' AND ReturnReasonCode IS NULL AND restorestatus.StatusName = 'Pass') THEN 0
WHEN ((tt.TypeName IN ('Restore', 'Recovery') AND ReturnReasonCode IS NOT NULL) OR (tt.TypeName IN ('Restore', 'Recovery') AND ta.FundsTransferStatusId <> 7)) THEN 1
WHEN (tt.TypeName = 'Recovery' AND ReturnReasonCode IS NULL AND ta.FundsTransferStatusId = 7) THEN 0 ELSE NULL END), 0) IsLoss
, MAX(biiad.DateReturned) RestoreReturnDate
, MAX(da.ActivationDate) ActivationDate
, MAX(CASE WHEN RestoreNumber.UserRestoreNumber = 0 THEN 1 ELSE 0 END) IsFirstRestore
, MAX((CASE WHEN (ta.FundsTransferStatusId = 5 AND tt.TypeName IN ('Activation', 'InstantActivation')) OR (biiad.ReturnReasonCode LIKE 'R%%' AND tt.TypeName IN ('Activation', 'InstantActivation')) THEN 1 ELSE 0 END)) AS ActFail
, MAX(da.CashoutAmount/100) Amount
, MAX(CASE WHEN tip.FundsTransferStatusId = 5 THEN 1 ELSE 0 END) IsTipFail
, MAX(CAST(da.Tip AS decimal(18,4))) TipAmount
, MAX(CASE WHEN tt.TypeName = 'InstantActivation' THEN 1 ELSE 0 END) IsLightningPay
, MAX(da.CreatedOn) RequestTime
, MAX(CASE WHEN CAST(da.CreatedOn AS time)<'06:00:00' THEN 1 ELSE 0 END) AS IsNight
, MAX(CAST(da.RestoreDate AS DATETIME)) RestoreDate
, MAX(CASE WHEN tt.TypeName = 'Restore' THEN biiad.ReturnReasonCode ELSE NULL END) RestoreReturnCodeAch
, MAX(da.ActivationOriginId) ActivationOriginId
FROM Activations da
LEFT JOIN transactions dt
ON dt.ActivationID = da.ActivationID
LEFT JOIN TransactionsToFundsTransfers mmttft
ON dt.TransactionID = mmttft.TransactionId
LEFT JOIN TransferAttempts ta
ON mmttft.FundsTransferId = ta.FundsTransferId
LEFT JOIN InboundAchDetails biiad
ON biiad.FundsTransferId = mmttft.FundsTransferId
LEFT JOIN TransactionTypes tt
ON tt.TransactionTypeID = dt.TransactionTypeID
LEFT JOIN HourStatuses hs
ON hs.HourStatusID = da.HourStatusID
LEFT JOIN
(SELECT distinct t.ActivationId, ta.FundsTransferStatusId
FROM Transactions t
JOIN TransactionsToFundsTransfers m
ON t.TransactionID = m.TransactionId
JOIN TransferAttempts ta
ON m.FundsTransferId = ta.FundsTransferId
WHERE t.TransactionTypeID = 2 AND t.UserId={0}
) tip
ON tip.ActivationId = da.ActivationID
LEFT JOIN (
SELECT a.UserID, a.RestoreDate, COUNT(b.RestoreDate) UserRestoreNumber
FROM (
SELECT DISTINCT a.UserID, CAST(a.RestoreDate AS Date) RestoreDate
from Activations a
where a.UserId={0}
) a
LEFT JOIN (
SELECT DISTINCT a.UserID, CAST(a.RestoreDate AS Date) RestoreDate
from Activations a
where a.UserId={0}
) b
ON b.UserID = a.UserID AND b.RestoreDate < a.RestoreDate
where a.UserId={0}
GROUP BY a.UserID, a.RestoreDate
) RestoreNumber
ON (RestoreNumber.UserID = da.UserID AND CAST(da.RestoreDate AS DATE) = RestoreNumber.RestoreDate)
WHERE da.UserId={0}
AND da.RestoreDate <= DATE_ADD(now(), interval -7 day)
GROUP BY da.ActivationId
ORDER BY MAX(da.CreatedOn)
'''.format(self.uid)
return ah_db.execute_to_json('moneyMovement', sql)