def save_trial_balance(company, response):
"""
Parses the quickbooks JSON Response of the trial balance
follows the format provided here https://developer.intuit.com/docs/api/accounting/trial%20balance
:param company:
:param response:
:return: trial_balances
"""
period = Utils.format_period(response["Header"]["EndPeriod"])
currency = response["Header"]["Currency"]
headers = [
column["ColTitle"]
if column["ColTitle"] != "" else column["ColType"]
for column in response["Columns"]["Column"]
]
entries = []
if response["Rows"]:
for row in response["Rows"]["Row"]:
d = {}
if 'ColData' in row:
for i in range(len(row["ColData"])):
d[headers[i]] = row["ColData"][i]["value"]
if i == 0:
d['id'] = row["ColData"][i]["id"]
d['Debit'] = float(d["Debit"]) if d["Debit"] != "" else 0
d['Credit'] = float(
d["Credit"]) if d["Credit"] != "" else 0
rows_affected = TrialBalance.objects.filter(
company=company, period=period,
gl_account_id=d["id"]).update(
debit=d["Debit"],
credit=d["Credit"],
gl_account_name=d["Account"])
if rows_affected == 0:
entry = TrialBalance(company=company,
gl_account_name=d["Account"],
debit=d["Debit"],
credit=d["Credit"],
period=period,
currency=currency,
gl_account_id=d["id"])
entries.append(entry)
else:
print("Dont process the row")
#TrialBalance.objects.bulk_create(entries)
return entries
def save_trial_balance(company, response):
period = response["ReportTitles"][2]
months = dict(January=1, February=2, March=3, April=4, May=5, June=6, July=7, August=8, September=9, October=10,
November=11, December=12)
period = period.split(' ')
period = period[2:]
period[1] = str(months[period[1]])
temp = period[0]
period[0] = period[2]
period[2] = temp
print(period)
period = Utils.format_period(' '.join(period))
currency = 'CAD' # TODO: need to get currency from any where in xero
gl_account_id = 0
for row in response["Rows"]:
if row["RowType"] == "Header":
headers = [column for column in row["Cells"]]
if row["RowType"] == "Section":
for child_row in row["Rows"]:
d = {}
d[headers[0]["Value"]] = child_row["Cells"][0]["Value"]
d[headers[1]["Value"]] = float(child_row["Cells"][1]["Value"]) if child_row["Cells"][1][
"Value"] != "" else 0
d[headers[2]["Value"]] = float(child_row["Cells"][2]["Value"]) if child_row["Cells"][2][
"Value"] != "" else 0
try:
id = re.search(r"\(([0-9]+)\)", d["Account"])
d['Id'] = id.group(1)
except Exception:
continue
exists = TrialBalance.objects.filter(company=company,
period=period,
gl_account_id = d["Id"]).first()
if exists:
exists.debit, exists.credit = d["Debit"], d["Credit"]
exists.gl_account_name = d["Account"]
exists.save()
else:
trial = TrialBalance(company=company, gl_account_name=d["Account"],
debit=d["Debit"], credit=d["Credit"],
period=period, currency=currency,
gl_account_id=d["Id"])
trial.save()
return
def process_trial_balance_csv(company, csv_data):
# REMEMBER: when dealing with CSV files in this way, there are extra "lines" at the start of the data
# that contain the header.
print('###### accounting type ', company.accounting_type)
today = date.today()
century = str(
today.year
)[:
2] # QB CSV gives 2 digit year, so we append to century portion of current year.
print('#### century is ', century)
if Utils.capitalize(company.accounting_type) == Company.SAGE:
tbs = []
line_index = 0
period_ending = ''
sage_tb_file_header = [
'Account Number', 'Account Description', 'Debits', 'Credits'
]
header_found = False
for line in csv_data:
if line_index == 5: # get period ending from file
try:
words = line[0].split(' ')
d = words[-1].split('/')
period_ending = d[2] + '-' + d[0] + '-' + d[1]
period = Utils.format_period(period_ending)
meta = CompanyMeta.objects.filter(
company_id=company).first()
if meta.monthly_reporting_current_period != period:
return None
line_index += 1
continue
except Exception as e:
return "INVALID_CSV"
elif line_index == 9:
line_index += 1
continue
elif len(line) != len(sage_tb_file_header):
line_index += 1
continue
elif CSVUtils.is_csv_header(
line, sage_tb_file_header) and not header_found:
header_found = True
line_index += 1
continue
elif len(line[1]) == 0:
line_index += 1
continue
else:
print(line)
exists = TrialBalance.objects.filter(
company=company,
period=period_ending,
gl_account_id=line[0]).first()
if exists:
exists.debit = CSVUtils.check_float_value(line[2])
exists.credit = CSVUtils.check_float_value(line[3])
exists.save()
tbs.append(exists)
else:
d = TrialBalance.objects.create(
company=company,
gl_account_name=line[1],
gl_account_id=line[0],
debit=CSVUtils.check_float_value(line[2]),
credit=CSVUtils.check_float_value(line[3]),
period=period_ending,
currency=company.default_currency)
tbs.append(d)
line_index += 1
if not header_found:
return "INVALID_CSV"
return tbs
elif Utils.capitalize(company.accounting_type) == Company.QUICKBOOKS:
tbs = []
header_found = False
period_ending = ''
# the first line in this file contains the period ending
qbd_tb_file_header = ['', 'Debit', 'Credit']
line_index = 1
for line in csv_data:
print('### ', line)
if line_index == 5:
# QB CSV sends dates like: 30 Nov 17 ... DD MMM YY ... yuck :(
try:
period_ending = line[1].split(' ')
year = century + period_ending[2]
month_num = list(calendar.month_abbr).index(
period_ending[1])
period_ending = year + '-' + str(
month_num) + '-' + period_ending[0]
period = Utils.format_period(period_ending)
meta = CompanyMeta.objects.filter(
company_id=company).first()
if meta.monthly_reporting_current_period != period:
return None
line_index += 1
continue
except Exception as e:
return "INVALID_CSV"
# Only process lines which match our expected format
elif len(line) != len(qbd_tb_file_header):
line_index += 1
continue
elif CSVUtils.is_csv_header(
line, qbd_tb_file_header) and not header_found:
header_found = True
line_index += 1
continue
elif line[0] == 'TOTAL':
line_index += 1
continue
gl_account_id = AccountingUtils.get_gl_account_id_by_name(
company, line[0])
print('#### TB QB glaccountid is ', gl_account_id)
if not gl_account_id:
gl_account_id = 9999
exists = TrialBalance.objects.filter(
company=company,
period=period_ending,
gl_account_id=gl_account_id,
).first()
print('############# checking for FLOAT ', line[1], line[2])
if exists:
exists.debit = CSVUtils.check_float_value(line[1])
exists.credit = CSVUtils.check_float_value(line[2])
exists.save()
tbs.append(exists)
else:
d = TrialBalance.objects.create(
company=company,
gl_account_name=line[0],
gl_account_id=gl_account_id,
debit=CSVUtils.check_float_value(line[1]),
credit=CSVUtils.check_float_value(line[2]),
period=period_ending,
currency=company.default_currency)
tbs.append(d)
line_index += 1
if not header_found:
return "INVALID_CSV"
return tbs
elif Utils.capitalize(company.accounting_type) == Company.XERO:
# TODO: Handle Xero CSV Data
pass