def get_entry(invoice_type, invoice_number, exact_user_id, exact_api=None):
if not exact_api:
exact_api = get_api()
existing_invoice_refs = None
select_param = "EntryID,EntryNumber,EntryDate,Created,Modified,YourRef," \
"AmountDC,VATAmountDC,DueDate,Description,Journal,ReportingYear"
if invoice_type in ['client', 'developer']:
existing_invoice_refs = exact_api.restv1(
GET("salesentry/SalesEntries?{}".format(
urlencode({
'$filter':
"YourRef eq '{}' and Customer eq guid'{}'".format(
invoice_number, exact_user_id),
'$select':
'{},SalesEntryLines,Customer'.format(select_param)
}))))
elif invoice_type == 'tunga':
existing_invoice_refs = exact_api.restv1(
GET("purchaseentry/PurchaseEntries?{}".format(
urlencode({
'$filter':
"YourRef eq '{}' and Supplier eq guid'{}'".format(
invoice_number, exact_user_id),
'$select':
'{},PurchaseEntryLines,Supplier'.format(select_param)
}))))
return existing_invoice_refs
def synchronize_gammes(self):
gammes = self.api.restv1(
GET(
f"read/logistics/ItemExtraField?$filter=Description+eq+'gamme'+and+Modified+gt+DateTime'{self.since_string}'"
)
)
for exact_gamme in gammes:
gamme = Gamme.objects.filter(
exact_item__exact_id=exact_gamme["ItemID"]
).first()
if not gamme:
gamme = Gamme(exact_item_id=exact_gamme["ItemID"])
gamme.exact_value = exact_gamme["Value"]
gamme.exact_modified = parse_exact_api_date(exact_gamme["Modified"])
gamme.save()
return len(gammes)
def synchronize_articles(self):
articles = self.api.restv1(
GET(
f"logistics/Items?$select=ID,Code,Description,Modified&$filter=Modified+gt+DateTime'{self.since_string}'"
)
)
for exact_article in articles:
article = Article.objects.filter(exact_id=exact_article["ID"]).first()
if not article:
article = Article(exact_id=exact_article["ID"])
article.exact_code = exact_article["Code"]
article.exact_description = exact_article["Description"]
article.exact_modified = parse_exact_api_date(exact_article["Modified"])
article.save()
return len(articles)
def synchronize_commandes(self):
counter = 0
# fifteen_days_ago = datetime.datetime.now() - datetime.timedelta(15)
# commandes = self.api.restv1(GET(f"salesorder/SalesOrders?$select=SalesOrderLines,OrderID,Description,OrderedBy,YourRef,OrderNumber,Status,StatusDescription,Modified&$expand=SalesOrderLines&$filter=Modified+gt+DateTime'{fifteen_days_ago.isoformat()}'"))
commandes = self.api.restv1(
GET(
f"salesorder/SalesOrders?$select=SalesOrderLines,OrderID,Description,OrderedBy,YourRef,OrderNumber,Status,StatusDescription,Modified,SalesOrderLines&$expand=SalesOrderLines&$filter=Modified+gt+DateTime'{self.since_string}'"
)
)
for exact_order in commandes:
commande = Commande.objects.filter(
exact_order_id=exact_order["OrderID"]
).first()
tier = Tier.objects.filter(exact_id=exact_order["OrderedBy"]).first()
if not commande:
commande = Commande(exact_order_id=exact_order["OrderID"])
commande.exact_tier = tier
commande.exact_order_description = exact_order["Description"]
commande.exact_your_ref = exact_order["YourRef"]
commande.exact_order_number = exact_order["OrderNumber"]
commande.exact_status_description = exact_order["StatusDescription"]
commande.exact_status = exact_order["Status"]
commande.exact_modified = parse_exact_api_date(exact_order["Modified"])
commande.save()
exact_lines = exact_order["SalesOrderLines"]["results"]
# delete lines that are not in exact anymore
all_exact_line_ids = [exact_line["ID"] for exact_line in exact_lines]
for line in commande.lines.all():
if line.exact_id not in all_exact_line_ids:
line.delete()
for exact_line in exact_lines:
line = LigneDeCommande.objects.filter(exact_id=exact_line["ID"]).first()
if not line:
line = LigneDeCommande(exact_id=exact_line["ID"])
line.exact_item_id = exact_line["Item"]
line.exact_line_number = exact_line["LineNumber"]
line.exact_item_description = exact_line["ItemDescription"]
line.exact_notes = exact_line["Notes"]
line.exact_amount = exact_line["AmountDC"]
line.exact_order = commande
line.save()
counter = counter + 1
return counter
def get_project_entry_v3(entry_ref, exact_user_id, exact_api=None):
if not exact_api:
exact_api = get_api()
select_param = "EntryID,EntryNumber,EntryDate,Created,Modified,YourRef," \
"AmountDC,VATAmountDC,DueDate,Description,Journal,ReportingYear"
existing_project_entry_refs = exact_api.restv1(
GET("salesentry/SalesEntries?{}".format(
urlencode({
'$filter':
"YourRef eq '{}' and Customer eq guid'{}'".format(
entry_ref, exact_user_id),
'$select':
'{},SalesEntryLines,Customer'.format(select_param)
}))))
return existing_project_entry_refs
def request_financial_lines(api, current_division, year, month):
first_of_the_month = datetime.date(year, month, 1).strftime("%Y-%m-%d")
last_of_the_month = datetime.date(
year, month, calendar.monthrange(year, month)[1]
).strftime("%Y-%m-%d")
date_filter = "Date+ge+DateTime'%s'+and+Date+le+DateTime'%s'" % (
first_of_the_month,
last_of_the_month,
)
request = (
"v1/%d/bulk/Financial/TransactionLines?$select=JournalCode,AmountDC,Date,EntryNumber,FinancialPeriod,FinancialYear,GLAccountCode,GLAccountDescription&$filter=%s"
% (current_division, date_filter)
)
financial_lines = api.rest(GET(request))
## Filter out entries associated wit the period closing Journal as those reverse all journal entries when the financial period is closed
journal_codes_to_ignore = ["96"]
return [
fl for fl in financial_lines if fl["JournalCode"] not in journal_codes_to_ignore
]
def synchronize_tiers(self):
counter = 0
tiers = self.api.restv1(
GET(
f"bulk/crm/Accounts?$select=ID,Code,Name,IsSales,IsSupplier,IsReseller,IsSales,IsPurchase&$filter=Modified+gt+DateTime'{self.since_string}'"
)
)
for exact_tier in tiers:
tier = self._build_tier(exact_tier)
tier.save()
# Clients
if tier.exact_is_sales:
tier_client = self._build_tier_detail(TierClient, tier, "c_")
tier_client.save()
# Fournisseurs
if tier.exact_is_supplier is True:
tier_supplier = self._build_tier_detail(TierSupplier, tier, "f_")
tier_supplier.save()
counter = counter + 1
return counter
def synchronize_deleted_commandes(self):
counter = 0
partial_or_open_exact_commandes = self.api.restv1(
GET(
f"salesorder/SalesOrders?$select=OrderID,Status&$filter=Status+eq+{EXACT_STATUS_PARTIAL}+or+Status+eq+{EXACT_STATUS_OPEN}"
)
)
exact_commandes_id = [
commande["OrderID"] for commande in partial_or_open_exact_commandes
]
commandes = Commande.objects.filter(
exact_status__in=[EXACT_STATUS_PARTIAL, EXACT_STATUS_OPEN]
)
for commande in commandes:
if commande.exact_order_id not in exact_commandes_id:
commande.delete()
counter = counter + 1
return counter
def synchronize_devis(self):
counter = 0
devis = self.api.restv1(
GET(
f"crm/Quotations?$select=QuotationID,OrderAccount,QuotationNumber,YourRef,Description&$filter=Modified+gt+DateTime'{self.since_string}'"
)
)
for exact_devis in devis:
quote = Devis.objects.filter(
exact_quotation_id=exact_devis["QuotationID"]
).first()
tier = Tier.objects.filter(exact_id=exact_devis["OrderAccount"]).first()
if not quote:
quote = Devis(exact_quotation_id=exact_devis["QuotationID"])
quote.exact_quotation_number = exact_devis["QuotationNumber"]
quote.exact_order_description = exact_devis["Description"]
quote.exact_tier = tier
quote.exact_your_ref = exact_devis["YourRef"]
quote.save()
counter = counter + 1
return counter
def create_sales_entry(self, os_invoice):
"""
:param os_customer: OsCustomer object
:param os_invoice: OsInvoice Object
:return:
"""
from exactonline.resource import GET
from os_customer import Customer
from tools import OsTools
db = current.db
os_tools = OsTools()
authorized = os_tools.get_sys_property('exact_online_authorized')
if not authorized:
self._log_error('create', 'sales_entry', os_invoice.invoice.id,
"Exact online integration not authorized")
return
items = os_invoice.get_invoice_items_rows()
if not len(items):
return # Don't do anything for invoices without items
import pprint
from ConfigParser import NoOptionError
from exactonline.http import HTTPError
storage = self.get_storage()
api = self.get_api()
cuID = os_invoice.get_linked_customer_id()
os_customer = Customer(os_invoice.get_linked_customer_id())
eoID = os_customer.row.exact_online_relation_id
if not eoID:
self._log_error(
'create', 'sales_entry', os_invoice.invoice.id,
"This customer is not linked to Exact Online - " +
unicode(os_customer.row.id))
return
try:
selected_division = int(storage.get('transient', 'division'))
except NoOptionError:
selected_division = None
amounts = os_invoice.get_amounts()
remote_journal = os_invoice.invoice_group.JournalID
invoice_date = os_invoice.invoice.DateCreated
is_credit_invoice = os_invoice.is_credit_invoice()
local_invoice_number = os_invoice.invoice.id
payment_method = os_invoice.get_payment_method()
invoice_data = {
'AmountDC': str(amounts.TotalPriceVAT), # DC = default currency
'AmountFC': str(amounts.TotalPriceVAT), # FC = foreign currency
'EntryDate': invoice_date.strftime(
'%Y-%m-%dT%H:%M:%SZ'), # pretend we're in UTC
'Customer': eoID,
'Description': os_invoice.invoice.Description,
'Journal': remote_journal, # 70 "Verkoopboek"
'ReportingPeriod': invoice_date.month,
'ReportingYear': invoice_date.year,
'SalesEntryLines': self.format_os_sales_entry_lines(os_invoice),
'VATAmountDC': str(amounts.VAT),
'VATAmountFC': str(amounts.VAT),
'YourRef': local_invoice_number,
}
if payment_method and payment_method.AccountingCode:
invoice_data['PaymentCondition'] = payment_method.AccountingCode
if is_credit_invoice:
invoice_data['Type'] = 21
error = False
try:
result = api.invoices.create(invoice_data)
#
# print "Create invoice result:"
# pp = pprint.PrettyPrinter(depth=6)
# pp.pprint(result)
eoseID = result['EntryID']
os_invoice.invoice.ExactOnlineSalesEntryID = eoseID
os_invoice.invoice.InvoiceID = result['EntryNumber']
os_invoice.invoice.update_record()
uri = result[u'SalesEntryLines'][u'__deferred']['uri']
entry_lines = api.restv1(GET(str(uri)))
# pp.pprint(entry_lines)
for i, line in enumerate(entry_lines):
query = (db.invoices_items.invoices_id == os_invoice.invoice.id) & \
(db.invoices_items.Sorting == i + 1)
db(query).update(ExactOnlineSalesEntryLineID=line['ID'])
except HTTPError as e:
error = True
self._log_error('create', 'sales_entry', os_invoice.invoice.id, e)
if error:
return False
return eoseID
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()
def request_financial_lines(api, current_division, year, month):
date_filter = "year(Date)+eq+%d+and+month(Date)+eq+%d+and+(startswith(GLAccountCode,'6')+eq+true+or+startswith(GLAccountCode,'7')+eq+true)" % (
year, month)
request = "v1/%d/financialtransaction/TransactionLines?$select=AmountDC,Date,EntryNumber,FinancialPeriod,FinancialYear,GLAccountCode,GLAccountDescription&$filter=%s" % (
current_division, date_filter)
return api.rest(GET(request))