def get_retained_earnings(companyDB, endDate):
sql_statement = f'''
SELECT SUM(a.Debit - a.Credit) as Actual
FROM JDT1 a
JOIN OACT b
ON a.Account = b.AcctCode
WHERE b.Levels = 4 and a.RefDate <= '{endDate}' and LEFT(FormatCode, 5) >= 40000
'''
dls = sql.SQL_Config('dlssap', companyDB, 'jason', 'Knights17?')
df = dls.sqlStatement(sql_statement)
return df.iloc[0][0]
def get_cash_flow_amount(companyDB, startDate, endDate, accounts):
str(accounts)
account_list = accounts.split(",")
sql_statement = f'''
SELECT SUM(a.Debit - a.Credit) as Actual
FROM JDT1 a
JOIN OACT b
ON a.Account = b.AcctCode
WHERE b.Levels = 4 and a.RefDate >= '{startDate}' and a.RefDate <= '{endDate}' and (
'''
for account in account_list:
sql_statement += f"LEFT(FormatCode, 5) = {account} or "
sql_statement = sql_statement[:-4]
sql_statement += ")"
dls = sql.SQL_Config('dlssap', companyDB, 'jason', 'Knights17?')
df = dls.sqlStatement(sql_statement)
return df.iloc[0][0]
import pandas as pd
import numpy as np
from pandas import datetime as dt
import sqlConfig as sql
import os
import shutil
import datetime
from csv import writer
# Open SQL Connections for Viewpoint & SpruceWare
vp_sql = sql.SQL_Config('JAM-APP-002.jamacdonald.com', 'Viewpoint')
wbs_sql = sql.SQL_Config('WOL-APP-001.jamacdonald.com', 'SpruceDotNet')
bis_sql = sql.SQL_Config('JAM-SQL-001.jamacdonald.com', 'GPPRD')
# grabs the actual date from the users inputed fiscal year and month.
def getActualDate(fiscal_year, month):
year = fiscal_year
if (month > 10):
year = fiscal_year - 1
print(datetime.date(year, month, 1))
return datetime.date(year, month, 1)
# determines the fiscal period start and end dates
def getFiscalDates(curr_date):
start_year = curr_date.year
end_year = curr_date.year
if (curr_date.month <= 10):
start_year = curr_date.year - 1