def getDataFrame(self, UserId, conn):
sql = '''
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
UserId, PayDate, EvaluationDate,
TransactionId, ProviderTransactionId
FROM
Payroll.PayrollActiveStatusTracking AS pr
Where
ProviderTransactionId IS NOT NULL
AND UserId = ''' + str(UserId) + '''
ORDER BY EvaluationDate ASC'''
df_tmp = getSQLdata(sql, conn)
df_trsct = getESdataForPayroll(UserId, predict=True)
try:
df_tmp = pd.merge(df_tmp,
df_trsct,
left_on='ProviderTransactionId',
right_on='ProviderTransactionId',
how='inner')
df_tmp = df_tmp[[
'UserId', 'PayDate', 'EvaluationDate', 'CreatedOn', 'Amount',
'Description'
]]
df_tmp = df_tmp.drop_duplicates(
['PayDate', 'Amount', 'Description'])
return df_tmp
except:
return pd.DataFrame(columns=[
'UserId', 'PayDate', 'EvaluationDate', 'CreatedOn', 'Amount',
'Description'
])
def getDataFrame(self, UserId, conn):
sql = '''
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
UserId, FinancialInstitutionId,
p.UserProvidedBankConnectionId, a.BfpProvidedBankConnectionId,
p.isActive, p.CreatedOn AS connectionCreatedOn,
p.LastUpdatedOn AS connectionLastUpdatedOn,
a.AccountNumber_H, a.AccountNickName,
a.AccountTypeAtProvider, a.AccountDescriptionAtProvider,
a.CreatedOn AS accountCreatedOn
FROM
BankFeed.UserProvidedBankConnection AS u
JOIN
BankFeed.BfpProvidedBankConnection AS p
ON
p.UserProvidedBankConnectionId = u.UserProvidedBankConnectionId
JOIN
BankFeed.BfpProvidedBankAccount AS a
ON
p.BfpProvidedBankConnectionId = a.BfpProvidedBankConnectionId
WHERE
UserId = ''' + str(UserId)
df_tmp = getSQLdata(sql, conn)
return df_tmp
def getDataFrame(self, UserId, conn):
sql = '''
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
UserId, balance.BfpProvidedBankAccountId,
balance.Balance, balance.CreatedOn
FROM
BankFeed.BfpProvidedBankAccountBalanceHistory AS balance
JOIN
BankFeed.UserProvidedBankAccountHistory AS uba
ON
balance.BfpProvidedBankAccountId = uba.BfpProvidedBankAccountId
JOIN
BankFeed.UserProvidedBankConnection AS ubc
ON
uba.UserProvidedBankConnectionId = ubc.UserProvidedBankConnectionId
WHERE
UserId = ''' + str(UserId) + '''
ORDER BY UserId, BfpProvidedBankAccountId
'''
df_tmp = getSQLdata(sql, conn)
sql = '''
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
UserId, balance.UserAccountId AS BfpProvidedBankAccountId,
balance.BalanceAmount AS Balance, balance.CreatedOn
FROM
Bank.UserAccountBalances AS balance
JOIN
BankFeed.UserProvidedBankAccountHistory AS uba
ON
balance.UserAccountId = uba.BfpProvidedBankAccountId
JOIN
BankFeed.UserProvidedBankConnection AS ubc
ON
uba.UserProvidedBankConnectionId = ubc.UserProvidedBankConnectionId
WHERE
UserId = ''' + str(UserId) + '''
ORDER BY UserId, BfpProvidedBankAccountId
'''
df_out = pd.concat([df_tmp, getSQLdata(sql, conn)], ignore_index=True)
return df_out
def getDataFrame(self, UserId, conn):
sql = '''
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
uba.BfpProvidedBankAccountId, ubc.isActive AS bank_active,
uba.isActive AS account_active,
uba.RoutingNumber, uba.AccountNumber_H
FROM
BankFeed.UserProvidedBankAccountHistory AS uba
JOIN
BankFeed.UserProvidedBankConnectionHistory AS ubc
ON
ubc.UserProvidedBankConnectionId = uba.UserProvidedBankConnectionId
WHERE
ubc.isActive = 'True' AND uba.isActive = 'True'
AND BfpProvidedBankAccountId IS NOT NULL
AND UserId = ''' + str(UserId) + '''
ORDER BY uba.CreatedOn DESC '''
df_tmp = getSQLdata(sql, conn)
try:
ActNum = int(re.sub(r'\D', '', df_tmp.iloc[0]['AccountNumber_H']))
RtNum = df_tmp.iloc[0]['RoutingNumber']
df_tmp = df_tmp[(df_tmp['AccountNumber_H'].map(
lambda x: int(re.sub(r'\D', '', x)) == ActNum))
& (df_tmp['RoutingNumber'] == RtNum)]
df_tmp = df_tmp.drop_duplicates()
df_trsct = getESdata(UserId, predict=True)
df_tmp = pd.merge(df_tmp,
df_trsct,
left_on='BfpProvidedBankAccountId',
right_on='bfpProvidedBankAccountId',
how='inner')
df_tmp = df_tmp[[
'UserId', 'PostedDate', 'CreatedOn', 'Amount', 'Description'
]]
df_tmp = df_tmp.drop_duplicates(
['PostedDate', 'Amount', 'Description'])
return df_tmp
except:
return pd.DataFrame(columns=[
'UserId', 'PostedDate', 'CreatedOn', 'Amount', 'Description'
])
def getDataFrame(self):
with ah_db.open_db_connection('miscellaneous') as conn_misc:
sql = '''
SELECT t.UserId, t.Date, t.TotalHours,
t.CreatedOn, t.TimeSheetProviderId, tp.Name
FROM TimeSheets AS t
JOIN TimeSheetProviders as tp
ON t.TimeSheetProviderId = tp.TimeSheetProviderId
WHERE UserId={} '''.format(self.UserId)
df_out = getSQLdata(sql, conn_misc)
return df_out
def getDataFrame(self, UserId, conn):
sql = '''
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
UserId, PayDate, EvaluationDate,
(CASE WHEN TransactionId IS NOT NULL THEN TransactionId ELSE -1 END) AS TransactionId,
ProviderTransactionId
FROM
Payroll.PayrollActiveStatusTracking AS pr
Where
(ProviderTransactionId IS NOT NULL OR TransactionId IS NOT NULL)
AND UserId = ''' + str(UserId) + '''
ORDER BY EvaluationDate ASC'''
df_trsct = getESdataForPayroll(UserId, predict=False)
df_tmp = getSQLdata(sql, conn)
df_tmp['TransactionId'] = df_tmp['TransactionId'].astype('str')
try:
df_tmp1 = pd.merge(df_tmp,
df_trsct,
left_on='ProviderTransactionId',
right_on='ProviderTransactionId',
how='inner')
df_tmp1 = df_tmp1[[
'UserId', 'PayDate', 'EvaluationDate', 'CreatedOn', 'Amount',
'Description'
]]
df_tmp2 = pd.merge(df_tmp,
df_trsct,
left_on='TransactionId',
right_on='id',
how='inner')
df_tmp2 = df_tmp2[[
'UserId', 'PayDate', 'EvaluationDate', 'CreatedOn', 'Amount',
'Description'
]]
df_tmp = pd.concat([df_tmp1, df_tmp2])
df_tmp = df_tmp.drop_duplicates(
['PayDate', 'Amount', 'Description'])
return df_tmp
except:
return pd.DataFrame(columns=[
'UserId', 'PayDate', 'EvaluationDate', 'CreatedOn', 'Amount',
'Description'
])
def getDataFrame(self):
with ah_db.open_db_connection('miscellaneous') as conn_misc:
sql = '''
SELECT IsHeadLine, NoteCategoryId, Date, UserId
FROM Notes
WHERE UserId = {}
ORDER BY Date ASC'''.format(self.UserId)
df_out = getSQLdata(sql, conn_misc)
if df_out.shape[0] == 0:
df_out = pd.DataFrame(columns=['IsHeadLine',
'NoteCategoryId',
'Date'])
return df_out
def getDataFrame(self):
with ah_db.open_db_connection('miscellaneous') as conn_misc:
sql = '''
SELECT UserID, pt.PictureTypeId, pt.Typename,
LastUpdatedOn, ps.StatusName
FROM UserTimeSheetPictures AS utp
JOIN PictureStatuses AS ps
ON utp.PictureStatusId = ps.PictureStatusId
JOIN PictureTypes as pt
ON utp.PictureTypeId = pt.PictureTypeId
WHERE StatusName = 'processed'
AND UserId={} '''.format(self.UserId)
df_out = getSQLdata(sql, conn_misc)
return df_out
def getDataFrame(self):
with ah_db.open_db_connection('bankConnection') as conn_bf:
sql = '''
SELECT UserBankId, AuthenticationIssueTypeId,
AuthenticationIssueStatusId, CreatedOn, ResolvedOn
FROM AuthenticationIssues
WHERE UserId = {}
ORDER BY CreatedOn ASC'''.format(self.UserId)
df_out = getSQLdata(sql, conn_bf)
if df_out.shape[0] == 0:
df_out = pd.DataFrame(columns=['UserBankId',
'AuthenticationIssueTypeId',
'AuthenticationIssueStatusId',
'CreatedOn', 'ResolvedOn'])
return df_out
def getDataFrame(self):
with ah_db.open_db_connection('moneyMovement') as conn_mm:
sql = '''
SELECT ft.CreatedOn, ft.Amount, ft.PostingDate, c.CreditReasonId
FROM FundsTransfers ft
JOIN CustomerServiceCredits c
ON ft.FundsTransferId = c.TransactionId
WHERE UserId = {}
ORDER BY CreatedOn ASC'''.format(self.UserId)
df_out = getSQLdata(sql, conn_mm)
if df_out.shape[0] == 0:
df_out = pd.DataFrame(columns=['CreatedOn',
'Amount',
'PostingDate',
'CreditReasonId'])
return df_out
def getDataFrame(self, UserId, conn):
sql = '''
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
ubc.isActive AS bank_active,
uba.BfpProvidedBankAccountId,
uba.isActive AS account_active, uba.RoutingNumber, uba.AccountNumber_H
FROM
BankFeed.UserProvidedBankAccountHistory AS uba
JOIN
BankFeed.UserProvidedBankConnectionHistory AS ubc
ON
ubc.UserProvidedBankConnectionId = uba.UserProvidedBankConnectionId
WHERE
uba.BfpProvidedBankAccountId IS NOT NULL AND UserId = ''' + str(UserId)
df_tmp = getSQLdata(sql, conn)
df_tmp = df_tmp.drop_duplicates()
df_trsct = getESdata(UserId, predict=False)
try:
df_tmp = pd.merge(df_tmp,
df_trsct,
left_on='BfpProvidedBankAccountId',
right_on='bfpProvidedBankAccountId',
how='inner')
df_tmp = df_tmp[[
'UserId', 'PostedDate', 'CreatedOn', 'Amount', 'Description',
'AccountNumber_H', 'RoutingNumber'
]]
df_tmp = df_tmp.drop_duplicates(
['PostedDate', 'Amount', 'Description'])
return df_tmp
except:
return pd.DataFrame(columns=[
'UserId', 'PostedDate', 'CreatedOn', 'Amount', 'Description',
'AccountNumber_H', 'RoutingNumber'
])
def getDataFrame(self, UserId, conn):
sql = '''
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
UserId, balance.BfpProvidedBankAccountId,
balance.Balance, balance.CreatedOn
FROM
BankFeed.BfpProvidedBankAccountBalanceHistory AS balance
JOIN
BankFeed.UserProvidedBankAccount AS uba
ON
balance.BfpProvidedBankAccountId = uba.BfpProvidedBankAccountId
JOIN
BankFeed.UserProvidedBankConnection AS ubc
ON
uba.UserProvidedBankConnectionId = ubc.UserProvidedBankConnectionId
WHERE
UserId = ''' + str(UserId) + '''
'''
df_tmp = getSQLdata(sql, conn)
return df_tmp