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 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()