def get_instance_by_id(self, company_id):
"""
This Wrapper method returns the object of the class for using in accounting system
:param accounting_type:Name of the accounting system
:return: Object of the accounting system to be accessed
"""
accounting_type = Utils.capitalize(
Utils.get_accounting_type(company_id))
if accounting_type == Company.QUICKBOOKS:
return QuickBooks()
elif accounting_type == Company.XERO:
return XeroAccountings()
elif accounting_type == Company.SAGE:
return SageAccountings()
def process_chart_of_accounts_csv(company, csv_data):
# Sage
if Utils.capitalize(company.accounting_type) == Company.SAGE:
coas = []
qbd_coa_file_header = [
'', '', 'No.', 'Description', 'Type', 'Account Class'
]
header_found = False
filtered = [line for line in csv_data if len(line) > 4]
for line in filtered:
if len(line) != len(qbd_coa_file_header):
continue
elif CSVUtils.is_csv_header(
line, qbd_coa_file_header) and not header_found:
header_found = True
continue
if (line[0] == '' and line[1] == '') or (len(line[0]) < 2
and len(line[1]) < 2):
i = 4
account_type = None
if len(line[4]) != 1:
while i < len(line):
if len(line[i]) == 1:
account_type = line[i]
i += 1
else:
account_type = line[4]
if account_type in ["A", "G"]:
# todo: try catch here in case of data type issue, and return errors
exists = CoA.objects.filter(
company=company,
gl_account_id=line[2]).first()
if not exists:
c = CoA.objects.create(
company=company,
gl_account_id=line[2],
gl_account_name=line[3],
gl_account_currency=company.
default_currency,
gl_account_type=line[-1])
coas.append(c)
if not header_found:
return "INVALID_CSV"
return coas
# Quickbooks
elif Utils.capitalize(company.accounting_type) == Company.QUICKBOOKS:
coas = []
qbd_coa_file_header = [
'', 'Account', 'Type', 'Balance Total', 'Description',
'Tax Line'
]
header_found = False
for line in csv_data:
# Only process lines which match our expected format
if len(line) != len(qbd_coa_file_header):
continue
elif CSVUtils.is_csv_header(
line, qbd_coa_file_header) and not header_found:
header_found = True
continue
exists = CoA.objects.filter(company=company,
gl_account_name=line[1],
gl_account_type=line[2]).first()
if not exists:
# gl_account_id change made
c = CoA.objects.filter(
company=company).annotate(gl_account_id_int=Cast(
'gl_account_id', IntegerField()))
c = c.aggregate(Max('gl_account_id_int'))
# Quickbooks CSV file does not send account id with the data so we need to
# generate our own. We try to find the last id that was set, to increment by 1
# from there, or set to 1 if there's no existing IDs
gl_acct_id = 1
if c['gl_account_id_int__max']:
gl_acct_id = int(c['gl_account_id_int__max']) + 1
bal = CSVUtils.check_float_value(line[3])
c = CoA.objects.create(
company=company,
gl_account_id=gl_acct_id,
gl_account_name=line[1],
gl_account_type=line[2],
gl_account_bal=bal,
gl_account_currency=company.default_currency)
coas.append(c)
if not header_found:
return "INVALID_CSV"
return coas
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