def demo():
"""Demo that connect to your API. and list the result of the division endpoint"""
api = ExactApi(get_storage())
api.refresh_token()
divisions, current_divisions = api.get_divisions()
print('Possible divisions {}'.format(divisions))
print('Current division {}'.format(current_divisions))
def demo():
"""Demo that connect to your API. and list the result of the division endpoint"""
api = ExactApi(get_storage())
api.refresh_token()
divisions, current_divisions = api.get_divisions()
print('Possible divisions {}'.format(divisions))
print('Current division {}'.format(current_divisions))
def setup(base_url, client_id, client_secret):
""" One time setup of the ini storage. This onlu need to run once"""
storage = get_storage();
storage.set('application', 'base_url', base_url)
storage.set('application', 'client_id', client_id)
storage.set('application', 'client_secret', client_secret)
api = ExactApi(storage)
# Open a web browser to get the code query parameter
print("A web browser is going to open in 5 seconds, authenticate and copy and paste the value of the code query parameter")
sleep(5)
webbrowser.open_new(api.create_auth_request_url())
code = unquote(input('Enter value of the code query parameter: '))
api.request_token(code)
print("High five!! it worked")
def setup(base_url, client_id, client_secret):
""" One time setup of the ini storage. This onlu need to run once"""
storage = get_storage();
storage.set('application', 'base_url', base_url)
storage.set('application', 'client_id', client_id)
storage.set('application', 'client_secret', client_secret)
api = ExactApi(storage)
# Open a web browser to get the code query parameter
print("A web browser is going to open in 5 seconds, authenticate and copy and paste the value of the code query parameter")
sleep(5)
webbrowser.open_new(api.create_auth_request_url())
code = unquote(raw_input('Enter value of the code query parameter: '))
api.request_token(code)
print("High five!! it worked")
def get_api(self):
"""
Return ExactAPI linked to config and token storage
"""
import os
from exactonline.api import ExactApi
from exactonline.exceptions import ObjectDoesNotExist
from exactonline.storage import IniStorage
storage = self.get_storage()
return ExactApi(storage=storage)
def get_api():
storage = IniStorage(BASE_DIR + '/tunga/exact.ini')
return ExactApi(storage=storage)
def factory():
return ExactApi(MyIniStorage(config['config_file']))
def get_api():
storage = ExactStorage()
return ExactApi(storage=storage)
def excel(annee_fiscale):
storage = get_storage()
api = ExactApi(storage)
# divisions, current_divisions = api.get_divisions()
current_divisions = 17923
print("Current division {}".format(current_divisions))
all_accounts = api.rest(
GET(
"v1/%d/financial/GLAccounts?$filter=startswith(Code,'6')+eq+true+or+startswith(Code,'7')+eq+true"
% current_divisions
)
)
result = collections.OrderedDict()
tableau = {}
with open("rapport_config.json") as data_file:
tableau = json.load(data_file, object_pairs_hook=collections.OrderedDict)
operator_map = {"+": operator.iadd, "-": operator.isub}
now = datetime.datetime.now()
current_year = now.year if now.month < 9 else now.year + 1
current_year = annee_fiscale if annee_fiscale else current_year
starting_date = datetime.date(current_year - 1, 10, 1)
end_date = datetime.date(current_year, 9, 30)
number_of_months = (
(end_date.year - starting_date.year) * 12
+ end_date.month
- starting_date.month
+ 1
)
months = range(number_of_months)
used_accounts = []
for key, value in tableau.items():
if is_subcategory(value):
subcategories = value
category_name = key
result[category_name] = {
"total": [0] * number_of_months,
"detail": collections.OrderedDict(),
"type": "subcategory",
}
for subcategory_name, account_list in subcategories.items():
result[category_name]["detail"][subcategory_name] = {
"total": [0] * number_of_months,
"detail": collections.OrderedDict(),
"type": "account_list",
}
for (_, account_number) in account_list:
# account_name = find_account_name(account_number, all_accounts)
result[category_name]["detail"][subcategory_name]["detail"][
account_number
] = [0] * number_of_months
used_accounts.append(account_number)
elif is_account_list(value):
category_name = key
account_list = value
result[category_name] = {
"total": [0] * number_of_months,
"detail": collections.OrderedDict(),
"type": "account_list",
}
for (_, account_number) in account_list:
# account_name = find_account_name(account_number, all_accounts)
result[category_name]["detail"][account_number] = [0] * number_of_months
used_accounts.append(account_number)
elif is_total_list(value):
total_name = key
category_list = value
result[total_name] = {"total": [0] * number_of_months, "type": "total_list"}
unused_lines = []
for month_index in months:
report_date = starting_date + relativedelta(months=month_index)
financial_lines = request_financial_lines(
api, current_divisions, report_date.year, report_date.month
)
unused_lines = unused_lines + find_unused_lines(financial_lines, used_accounts)
for key, value in tableau.items():
if is_subcategory(value):
subcategories = value
category_name = key
for subcategory_name, account_list in subcategories.items():
for (sign, account_number) in account_list:
make_account_list_totals(
operator_map[sign],
account_number,
financial_lines,
month_index,
result[category_name]["detail"][subcategory_name]["detail"][
account_number
],
result[category_name]["detail"][subcategory_name]["total"],
result[category_name]["total"],
)
elif is_account_list(value):
category_name = key
account_list = value
for (sign, account_number) in account_list:
make_account_list_totals(
operator_map[sign],
account_number,
financial_lines,
month_index,
result[category_name]["total"],
result[category_name]["detail"][account_number],
)
elif is_total_list(value):
total_name = key
category_list = value
for (sign, category_name) in category_list:
make_total_list_totals(
operator_map[sign],
category_name,
category_list,
result,
month_index,
result[total_name]["total"],
)
unused_accounts = [
account for account in all_accounts if int(account["Code"]) not in used_accounts
]
workbook = xlsxwriter.Workbook(
"compte_de_resultat-{}.xlsx".format(now.strftime("%Y%m%d%H%M%S"))
)
worksheet = workbook.add_worksheet("Rapport")
worksheet.fit_to_pages(1, 0) # 1 page wide and as long as necessary.
worksheet.set_landscape()
worksheet.repeat_rows(0, 2)
worksheet.set_row(0, 30)
date_header_format = workbook.add_format({"align": "center"})
title_format = workbook.add_format({"bold": 1, "border": 1, "align": "center"})
title_format.set_align("vcenter")
category_format = workbook.add_format({"bold": 1})
subcategory_format = workbook.add_format({"bold": 1})
money_format = workbook.add_format({"num_format": "#,##0.00"})
total_format = workbook.add_format({"bold": 1})
worksheet.merge_range(
"A1:N1",
u"Compte de resultat SIF du %s au %s"
% (starting_date.strftime("%m/%Y"), end_date.strftime("%m/%Y")),
title_format,
)
worksheet.write(2, 1, u"Intitulé de la ligne"),
[
worksheet.write(
2,
2 + month_index,
(starting_date + relativedelta(months=month_index)).strftime("%m/%Y"),
date_header_format,
)
for month_index in months
]
worksheet.set_column(1, 1, 35)
worksheet.set_column(2, 13, 12)
row = 4
col = 0
for category in result:
if result[category]["type"] == "subcategory":
worksheet.write(row, col + 1, category, category_format)
row += 1
for subcategory in result[category]["detail"]:
worksheet.write(
row, col + 1, u"{}".format(subcategory), subcategory_format
)
row += 1
for account_number in result[category]["detail"][subcategory]["detail"]:
account_description = find_account_name(
account_number, all_accounts
)
worksheet.write(row, col, account_number)
worksheet.write(
row,
col + 1,
u"{}".format(account_description).lower().capitalize(),
)
account_total = result[category]["detail"][subcategory]["detail"][
account_number
]
[
worksheet.write(row, col + 2 + index, total, money_format)
for index, total in enumerate(account_total)
]
row += 1
subcategory_total = result[category]["detail"][subcategory]["total"]
worksheet.write(
row, col + 1, u"Total %s" % subcategory, subcategory_format
)
[
worksheet.write(row, col + 2 + index, total, money_format)
for index, total in enumerate(subcategory_total)
]
row += 2
category_total = result[category]["total"]
worksheet.write(row, col + 1, u"TOTAL %s" % category)
[
worksheet.write(row, col + 2 + index, total, money_format)
for index, total in enumerate(category_total)
]
row += 2
elif result[category]["type"] == "account_list":
worksheet.write(row, col + 1, category, category_format)
row += 1
for account_number in result[category]["detail"]:
value = result[category]["detail"][account_number]
account_description = find_account_name(account_number, all_accounts)
worksheet.write(row, col, account_number)
worksheet.write(
row, col + 1, u"{}".format(account_description.lower().capitalize())
)
[
worksheet.write(row, col + 2 + index, total, money_format)
for index, total in enumerate(value)
]
row += 1
category_total = result[category]["total"]
worksheet.write(row, col + 1, u"TOTAL %s" % category)
[
worksheet.write(row, col + 2 + index, total, money_format)
for index, total in enumerate(category_total)
]
row += 2
elif result[category]["type"] == "total_list":
worksheet.write(row, col + 1, u"{}".format(category))
total = result[category]["total"]
[
worksheet.write(row, col + 2 + index, total, money_format)
for index, total in enumerate(total)
]
row += 2
unused_lines_worksheet = workbook.add_worksheet(
u"lignes non utilisées ({})".format(len(unused_lines))
)
row = 0
col = 0
unused_lines_worksheet.write_row(
row,
col,
(
u"Date",
u"Numéro de compte",
u"Description du compte",
u"Numéro d'écriture",
u"Montant",
),
)
row += 1
[
unused_lines_worksheet.write_row(
row + index,
col,
(
parse_date(line["Date"]).strftime("%d/%m/%Y"),
line["GLAccountCode"],
u"{}".format(line["GLAccountDescription"]),
line["EntryNumber"],
line["AmountDC"],
),
)
for index, line in enumerate(unused_lines)
]
unused_accounts_worksheet = workbook.add_worksheet(
u"Comptes non utilisés ({})".format(len(unused_accounts))
)
row = 0
col = 0
unused_accounts_worksheet.write(row, col, u"Numéro de compte")
unused_accounts_worksheet.write(row, col + 1, u"Description du compte")
row += 1
[
unused_accounts_worksheet.write_row(
row + index, col, (account["Code"], u"{}".format(account["Description"]))
)
for index, account in enumerate(unused_accounts)
]
workbook.close()
def excel(annee_fiscale):
storage = get_storage()
api = ExactApi(storage)
api.refresh_token()
divisions, current_divisions = api.get_divisions()
print('Possible divisions {}'.format(divisions))
print('Current division {}'.format(current_divisions))
all_accounts = api.rest(
GET("v1/%d/financial/GLAccounts?$filter=startswith(Code,'6')+eq+true+or+startswith(Code,'7')+eq+true"
% current_divisions))
result = collections.OrderedDict()
tableau = {}
with open('rapport_config.json') as data_file:
tableau = json.load(data_file,
object_pairs_hook=collections.OrderedDict)
operator_map = {"+": operator.iadd, "-": operator.isub}
now = datetime.datetime.now()
current_year = now.year if now.month < 9 else now.year + 1
current_year = annee_fiscale if annee_fiscale else current_year
starting_date = datetime.date(current_year - 1, 10, 1)
end_date = datetime.date(current_year, 9, 30)
number_of_months = (end_date.year - starting_date.year
) * 12 + end_date.month - starting_date.month + 1
months = range(number_of_months)
used_accounts = []
for key, value in tableau.items():
if is_subcategory(value):
subcategories = value
category_name = key
result[category_name] = {
'total': [0] * number_of_months,
'detail': collections.OrderedDict(),
'type': 'subcategory'
}
for subcategory_name, account_list in subcategories.items():
result[category_name]['detail'][subcategory_name] = {
'total': [0] * number_of_months,
'detail': collections.OrderedDict(),
'type': 'account_list'
}
for (_, account_number) in account_list:
#account_name = find_account_name(account_number, all_accounts)
result[category_name]['detail'][subcategory_name][
'detail'][account_number] = [0] * number_of_months
used_accounts.append(account_number)
elif is_account_list(value):
category_name = key
account_list = value
result[category_name] = {
'total': [0] * number_of_months,
'detail': collections.OrderedDict(),
'type': 'account_list'
}
for (_, account_number) in account_list:
#account_name = find_account_name(account_number, all_accounts)
result[category_name]['detail'][account_number] = [
0
] * number_of_months
used_accounts.append(account_number)
elif is_total_list(value):
total_name = key
category_list = value
result[total_name] = {
'total': [0] * number_of_months,
'type': 'total_list'
}
unused_lines = []
for month_index in months:
report_date = starting_date + relativedelta(months=month_index)
financial_lines = request_financial_lines(api, current_divisions,
report_date.year,
report_date.month)
unused_lines = unused_lines + find_unused_lines(
financial_lines, used_accounts)
if not financial_lines:
return
import pdb
pdb.set_trace()
for key, value in tableau.items():
if is_subcategory(value):
subcategories = value
category_name = key
for subcategory_name, account_list in subcategories.items():
for (sign, account_number) in account_list:
make_account_list_totals(
operator_map[sign], account_number,
financial_lines, month_index,
result[category_name]['detail'][subcategory_name]
['detail'][account_number], result[category_name]
['detail'][subcategory_name]['total'],
result[category_name]['total'])
elif is_account_list(value):
category_name = key
account_list = value
for (sign, account_number) in account_list:
make_account_list_totals(
operator_map[sign], account_number, financial_lines,
month_index, result[category_name]['total'],
result[category_name]['detail'][account_number])
elif is_total_list(value):
total_name = key
category_list = value
for (sign, category_name) in category_list:
make_total_list_totals(operator_map[sign], category_name,
category_list, result, month_index,
result[total_name]['total'])
unused_accounts = [
account for account in all_accounts
if int(account['Code']) not in used_accounts
]
workbook = xlsxwriter.Workbook('compte_de_resultat-{}.xlsx'.format(
now.strftime('%Y%m%d%H%M%S')))
worksheet = workbook.add_worksheet('Rapport')
worksheet.fit_to_pages(1, 0) # 1 page wide and as long as necessary.
worksheet.set_landscape()
worksheet.repeat_rows(0, 2)
worksheet.set_row(0, 30)
date_header_format = workbook.add_format({'align': 'center'})
title_format = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center'
})
title_format.set_align('vcenter')
category_format = workbook.add_format({'bold': 1})
subcategory_format = workbook.add_format({'bold': 1})
money_format = workbook.add_format({'num_format': '#,##0.00'})
total_format = workbook.add_format({'bold': 1})
worksheet.merge_range(
'A1:N1', u'Compte de resultat SIF du %s au %s' %
(starting_date.strftime('%m/%Y'), end_date.strftime('%m/%Y')),
title_format)
worksheet.write(2, 1, u'Intitulé de la ligne'),
[
worksheet.write(2, 2 + month_index,
(starting_date +
relativedelta(months=month_index)).strftime('%m/%Y'),
date_header_format) for month_index in months
]
worksheet.set_column(1, 1, 35)
worksheet.set_column(2, 13, 12)
row = 4
col = 0
for category in result:
if result[category]['type'] == 'subcategory':
worksheet.write(row, col + 1, category, category_format)
row += 1
for subcategory in result[category]['detail']:
worksheet.write(row, col + 1, u'{}'.format(subcategory),
subcategory_format)
row += 1
for account_number in result[category]['detail'][subcategory][
'detail']:
account_description = find_account_name(
account_number, all_accounts)
worksheet.write(row, col, account_number)
worksheet.write(
row, col + 1,
u'{}'.format(account_description).lower().capitalize())
account_total = result[category]['detail'][subcategory][
'detail'][account_number]
[
worksheet.write(row, col + 2 + index, total,
money_format)
for index, total in enumerate(account_total)
]
row += 1
subcategory_total = result[category]['detail'][subcategory][
'total']
worksheet.write(row, col + 1, u'Total %s' % subcategory,
subcategory_format)
[
worksheet.write(row, col + 2 + index, total, money_format)
for index, total in enumerate(subcategory_total)
]
row += 2
category_total = result[category]['total']
worksheet.write(row, col + 1, u'TOTAL %s' % category)
[
worksheet.write(row, col + 2 + index, total, money_format)
for index, total in enumerate(category_total)
]
row += 2
elif result[category]['type'] == 'account_list':
worksheet.write(row, col + 1, category, category_format)
row += 1
for account_number in result[category]['detail']:
value = result[category]['detail'][account_number]
account_description = find_account_name(
account_number, all_accounts)
worksheet.write(row, col, account_number)
worksheet.write(
row, col + 1,
u'{}'.format(account_description.lower().capitalize()))
[
worksheet.write(row, col + 2 + index, total, money_format)
for index, total in enumerate(value)
]
row += 1
category_total = result[category]['total']
worksheet.write(row, col + 1, u'TOTAL %s' % category)
[
worksheet.write(row, col + 2 + index, total, money_format)
for index, total in enumerate(category_total)
]
row += 2
elif result[category]['type'] == 'total_list':
worksheet.write(row, col + 1, u'{}'.format(category))
total = result[category]['total']
[
worksheet.write(row, col + 2 + index, total, money_format)
for index, total in enumerate(total)
]
row += 2
unused_lines_worksheet = workbook.add_worksheet(
u'lignes non utilisées ({})'.format(len(unused_lines)))
row = 0
col = 0
unused_lines_worksheet.write_row(
row, col, (u'Date', u'Numéro de compte', u'Description du compte',
u"Numéro d'écriture", u'Montant'))
row += 1
[
unused_lines_worksheet.write_row(
row + index, col,
(parse_date(line['Date']).strftime("%d/%m/%Y"),
line['GLAccountCode'], u'{}'.format(line['GLAccountDescription']),
line['EntryNumber'], line['AmountDC']))
for index, line in enumerate(unused_lines)
]
unused_accounts_worksheet = workbook.add_worksheet(
u'Comptes non utilisés ({})'.format(len(unused_accounts)))
row = 0
col = 0
unused_accounts_worksheet.write(row, col, u'Numéro de compte')
unused_accounts_worksheet.write(row, col + 1, u'Description du compte')
row += 1
[
unused_accounts_worksheet.write_row(
row + index, col,
(account['Code'], u'{}'.format(account['Description'])))
for index, account in enumerate(unused_accounts)
]
workbook.close()