def create_mf_mapping_table(self):
create_mf_table = """ CREATE TABLE IF NOT EXISTS MF_MAPPING (
id integer PRIMARY KEY,
kuvera text NOT NULL
); """
conn = get_db_conn()
conn.create_table(create_mf_table)
def add_goal(self, name, curr_val, time_period, inflation, user, one_time,
expense_period, post_returns):
self.create_goal_table()
conn = get_db_conn()
select_stmnt = '''SELECT max(id) from GOALS'''
res = conn.get_one(select_stmnt)
print(res)
print(type(res))
if res and res[0] is not None:
max_id = res[0] + 1
else:
max_id = 1
if one_time:
final_val = self.one_time_pay_final_val(curr_val, inflation,
time_period)
else:
final_val = self.recur_revenue_final_val(curr_val, inflation,
time_period / 12,
expense_period / 12,
post_returns)
sql_stmnt = '''INSERT INTO GOALS (id, name, curr_val, time_period, inflation, final_val, user, one_time_pay, expense_period, post_returns) VALUES ('''
sql_stmnt += str(max_id) + ''',"''' + name + '''",''' + str(
curr_val) + ''','''
sql_stmnt += str(time_period) + ''',''' + str(
inflation) + ''', ROUND(''' + str(final_val) + ''',2),"'''
sql_stmnt += user + '''", '''
if one_time:
sql_stmnt += str(1) + ''',null,null'''
else:
sql_stmnt += str(0) + ''',''' + str(
expense_period) + ''',''' + str(post_returns)
sql_stmnt += ''')'''
print("running ", sql_stmnt)
conn.insert_data(sql_stmnt)
def populate_mf_table(self):
self.create_table()
conn = get_db_conn()
for k, v in self.get_schemes().items():
insert_stmnt = '''INSERT INTO MF(id, name, fund_house, type) VALUES ("'''
insert_stmnt = insert_stmnt + str(
k) + '''","''' + v["fund_name"] + '''","''' + v[
"fund_house"] + '''","''' + v["fund_type"] + '''")'''
conn.insert_data(insert_stmnt)
def create_table(self):
create_mf_table = """ CREATE TABLE IF NOT EXISTS MF (
id integer PRIMARY KEY,
name text NOT NULL,
fund_house text NOT NULL,
type text NOT NULL
); """
conn = get_db_conn()
conn.create_table(create_mf_table)
def create_ppf_rates_table(self):
rate_table = """CREATE TABLE IF NOT EXISTS PPF_RATES (
id integer PRIMARY KEY,
from_date text NOT NULL,
to_date text NOT NULL,
roi FLOAT NOT NULL
); """
conn = get_db_conn()
conn.create_table(rate_table)
def create_ppf_table(self):
ppf_table = """CREATE TABLE IF NOT EXISTS PPF (
id integer PRIMARY KEY,
number text NOT NULL,
start_date text NOT NULL,
user text NOT NULL,
goal integer
); """
conn = get_db_conn()
conn.create_table(ppf_table)
def insert_ppf_trans_entry(self, ppf_number, date, trans_type, amount, notes, reference, interest_component):
self.create_ppf_entry_table()
insert_stmnt = ''' REPLACE INTO PPF_ENTRY (id, trans_date, notes, reference, type, amount, interest_component) '''
insert_stmnt += '''SELECT A.id, "''' + date + \
'''", "''' + notes + '''", "''' + reference + '''","'''
insert_stmnt += trans_type + '''",''' + str(amount) + ''','''
if interest_component:
insert_stmnt += str(1)
else:
insert_stmnt += str(0)
insert_stmnt += ''' from PPF A where A.number="''' + ppf_number + '''"'''
conn = get_db_conn()
conn.insert_data(insert_stmnt)
def create_ppf_entry_table(self):
entry_table = """CREATE TABLE IF NOT EXISTS PPF_ENTRY (
id integer,
trans_date text NOT NULL,
notes text,
reference text,
type text NOT NULL,
amount FLOAT NOT NULL,
interest_component BOOLEAN,
PRIMARY KEY (id, trans_date, type)
); """
conn = get_db_conn()
conn.create_table(entry_table)
def populate_trans_kuvera(self, user):
conn = get_db_conn()
trans_dir = self.get_data_path() + user + "/kuvera/"
for f in listdir(trans_dir):
item = join(trans_dir, f)
if isfile(item) and item.endswith(".csv"):
with open(item,
mode='r',
encoding="ascii",
errors="surrogateescape") as trans_file:
print("opened file:", item)
#csv_reader = csv.DictReader(trans_file)
csv_reader = csv.DictReader(
(line.replace('\0', '') for line in trans_file))
line_count = 0
name_id_map = dict()
for row in csv_reader:
if line_count == 0:
print(f'Column names are {", ".join(row)}')
else:
print(row)
fund_name = row[" Name of the Fund"]
if fund_name in name_id_map:
id = name_id_map[fund_name]
else:
select_stmnt = '''SELECT id from MF_MAPPING WHERE kuvera="''' + fund_name + '''"'''
res = conn.get_one(select_stmnt)
print(res)
print(type(res))
if res:
name_id_map[fund_name] = res[0]
else:
name_id_map[fund_name] = None
id = name_id_map[fund_name]
if id is not None:
insert_stmnt = '''INSERT INTO MF_TRANSACTIONS(id, trans_date, folio, type, units, nav, amount, user, goal) '''
insert_stmnt += '''VALUES ("''' + str(
id) + '''","''' + row["Date"].strip()
insert_stmnt += '''","''' + row[
" Folio Number"] + '''","''' + row[" Order"]
insert_stmnt += '''","''' + row[
" Units"] + '''","''' + row[" NAV"]
insert_stmnt += '''","''' + row[
" Amount (INR)"] + '''","''' + user
insert_stmnt += '''",Null)'''
print("Executing:", insert_stmnt)
conn.insert_data(insert_stmnt)
line_count += 1
def create_transactions_table(self):
create_trans_table = """ CREATE TABLE IF NOT EXISTS MF_TRANSACTIONS (
id integer NOT NULL,
trans_date text NOT NULL,
folio text NOT NULL,
type text NOT NULL,
units FLOAT NOT NULL,
nav FLOAT NOT NULL,
amount FLOAT NOT NULL,
user text NOT NULL,
goal text,
PRIMARY KEY (id, trans_date, folio, type)
); """
conn = get_db_conn()
conn.create_table(create_trans_table)
def create_goal_table(self):
create_goal_table = """CREATE TABLE IF NOT EXISTS GOALS (
id integer PRIMARY KEY,
name text NOT NULL,
curr_val FLOAT NOT NULL,
time_period integer NOT NULL,
inflation FLOAT NOT NULL,
final_val FLOAT NOT NULL,
user text NOT NULL,
one_time_pay BOOLEAN NOT NULL,
expense_period integer,
post_returns FLOAT
); """
conn = get_db_conn()
conn.create_table(create_goal_table)
def create_ppf_summary_table(self):
summary_table = """CREATE TABLE IF NOT EXISTS PPF_SUMMARY (
id integer PRIMARY KEY,
number text NOT NULL,
start_date text NOT NULL,
user text NOT NULL,
goal integer,
curr_amount FLOAT NOT NULL,
principal FLOAT NOT NULL,
interest FLOAT NOT NULL,
withdrawal FLOAT NOT NULL,
avg_investment FLOAT NOT NULL,
roi_five FLOAT NOT NULL
); """
conn = get_db_conn()
conn.create_table(summary_table)
def insert_ppf(self, number, start_date, user, goal):
self.create_ppf_table()
conn = get_db_conn()
select_stmnt = '''SELECT IFNULL(MAX(A.id),0)+1, A.number FROM PPF A WHERE A.number= "''' + \
number + '''"'''
res = conn.get_one(select_stmnt)
print(res)
print(type(res))
if res and res[1] is None:
insert_helper = SQLiteReplaceHelper("PPF", True)
insert_helper.add_field("id", res[0])
insert_helper.add_field("number", number)
insert_helper.add_field("start_date", start_date)
insert_helper.add_field("user", user)
insert_helper.add_field("goal", goal)
insert_stmnt = insert_helper.get_statement()
conn.insert_data(insert_stmnt)
else:
print("PPF account exists:", number)
def populate_mf_mapping_table(self):
self.create_mf_mapping_table()
conn = get_db_conn()
mapping_file = self.get_data_path() + "mapping.csv"
with open(mapping_file, mode='r', encoding='utf-8-sig') as csv_file:
print("opened file:", mapping_file)
csv_reader = csv.DictReader(csv_file)
line_count = 0
for row in csv_reader:
if line_count == 0:
print(f'Column names are {", ".join(row)}')
else:
print(row)
if row["scheme_code"] == "":
continue
insert_stmnt = '''INSERT INTO MF_MAPPING(id, kuvera) VALUES ("'''
insert_stmnt = insert_stmnt + row[
"scheme_code"] + '''","''' + row["kuvera"] + '''")'''
conn.insert_data(insert_stmnt)
line_count += 1
def load_ppf_rates(self):
data_path = self.get_data_path()
conn = get_db_conn()
with open(data_path, mode='r', encoding='utf-8-sig') as csv_file:
print("opened file:", data_path)
csv_reader = csv.DictReader(csv_file)
line_count = 1
for row in csv_reader:
print(row)
if row["From"] == "" or row["To"] == "" or row["ROI"] == "":
print("Invalid row for ppf rates entry")
continue
insert_helper = SQLiteReplaceHelper("PPF_RATES")
insert_helper.add_field("id", line_count)
insert_helper.add_field("from_date", row["From"])
insert_helper.add_field("to_date", row["To"])
insert_helper.add_field("roi", float(row["ROI"]))
insert_stmnt = insert_helper.get_statement()
conn.insert_data(insert_stmnt)
line_count += 1
def get_last_five_years_avg_rate(self):
conn = get_db_conn()
select_stmnt = '''SELECT from_date, to_date, roi from PPF_RATES'''
rows = conn.get_data(select_stmnt)
rate = 0
total_months = 0
if rows:
for row in rows:
print(row)
start_date = datetime.strptime(
row[0], '%d/%m/%Y').date()
end_date = datetime.now()
if relativedelta(end_date, start_date).years <= 5:
months = relativedelta(datetime.strptime(row[1], '%d/%m/%Y').date(
), datetime.strptime(row[0], '%d/%m/%Y').date()).months + 1
print("For ", str(months), " months between ",
row[0], " and ", row[1], " roi is ", row[2])
rate += float(row[2])*months
total_months += months
if rate > 0:
return rate/total_months
else:
return 0
def refresh_summary(self):
self.create_ppf_summary_table()
conn = get_db_conn()
select_stmnt = '''SELECT A.id, sum(A.amount) from PPF_ENTRY A WHERE A.type="credit" AND A.interest_component = 0 GROUP BY A.id'''
rows = conn.get_data(select_stmnt)
summary = {}
if rows:
for row in rows:
print(row)
summary[row[0]] = {"principal": row[1]}
select_stmnt = '''SELECT A.id, sum(A.amount) from PPF_ENTRY A WHERE A.type="debit" GROUP BY A.id'''
rows = conn.get_data(select_stmnt)
if rows:
for row in rows:
if row[0] in summary.keys():
summary[row[0]]["debit"] = row[1]
else:
summary[row[0]] = {"debit": row[1]}
select_stmnt = '''SELECT A.id, sum(A.amount) from PPF_ENTRY A WHERE A.type="credit" AND A.interest_component = 1 GROUP BY A.id'''
rows = conn.get_data(select_stmnt)
if rows:
for row in rows:
if row[0] in summary.keys():
summary[row[0]]["interest"] = row[1]
else:
summary[row[0]] = {"interest": row[1]}
for k, v in summary.items():
summary[k]["curr_amount"] = v.get(
"principal", 0) + v.get("interest", 0) - v.get("debit", 0)
print(summary)
select_stmnt = '''SELECT A.id, A.number, A.start_date, A.user, A.goal from PPF A'''
rows = conn.get_data(select_stmnt)
for row in rows:
insert_helper = SQLiteReplaceHelper("PPF_SUMMARY")
insert_helper.add_field("id", row[0])
insert_helper.add_field("number", row[1])
insert_helper.add_field("start_date", row[2])
insert_helper.add_field("user", row[3])
insert_helper.add_field("goal", row[4])
if row[0] in summary.keys():
temp = summary[row[0]]
insert_helper.add_field(
"curr_amount", temp.get("curr_amount", 0))
insert_helper.add_field("principal", temp.get("principal", 0))
insert_helper.add_field("interest", temp.get("interest", 0))
insert_helper.add_field("withdrawal", temp.get("debit", 0))
insert_helper.add_field("avg_investment", temp.get("principal", 0) /
self.get_diff_in_years(row[2]))
else:
insert_helper.add_field("curr_amount", 0)
insert_helper.add_field("principal", 0)
insert_helper.add_field("interest", 0)
insert_helper.add_field("withdrawal", 0)
insert_helper.add_field("avg_investment", 0)
insert_helper.add_field(
"roi_five", self.get_last_five_years_avg_rate())
insert_ppf_summary = insert_helper.get_statement()
conn.insert_data(insert_ppf_summary)
