def _set_line(line):
sheet.write(i, 0, get_date_format(__(line.get('date',
''))))
sheet.write(i, 1, line.get('j_code', ''))
sheet.write(i, 2, line.get('a_code', ''))
sheet.write(i, 3, line.get('a_name', ''))
sheet.write(i, 4, line.get('move_name', ''))
sheet.write(i, 5, line.get('displayed_name', ''))
sheet.write(i, 6, line.get('partner_name', ''))
sheet.write(
i, 7, get_date_format(__(line.get('date_maturity',
''))))
sheet.write(i, 8, _get_data_float(line.get('current')),
currency_format)
sheet.write(i, 9, _get_data_float(line.get('age_30_days')),
currency_format)
sheet.write(i, 10,
_get_data_float(line.get('age_60_days')),
currency_format)
sheet.write(i, 11,
_get_data_float(line.get('age_90_days')),
currency_format)
sheet.write(i, 12,
_get_data_float(line.get('age_120_days')),
currency_format)
sheet.write(i, 13, _get_data_float(line.get('older')),
currency_format)
sheet.write(i, 14, _get_data_float(line.get('balance')),
currency_format)
sheet.write(i, 15, line.get('matching_number', ''))
def onchange_same_period_last_year(self):
if self.same_period_last_year:
if not __(self.date_to):
raise UserError('You must select End Date')
if __(self.date_from):
date_from = datetime.strptime(__(self.date_from),
DEFAULT_SERVER_DATE_FORMAT)
date_to = datetime.strptime(__(self.date_to),
DEFAULT_SERVER_DATE_FORMAT)
period_vals = []
for n in range(self.same_period_last_year):
if __(self.date_from):
las_date_from = date_from - relativedelta(years=n + 1)
las_date_to = date_to - relativedelta(years=n + 1)
period_vals.append({
'name':
'From (%s) To (%s)' %
(__(self.date_from)
and las_date_from.strftime(DEFAULT_SERVER_DATE_FORMAT)
or 'Beginning',
las_date_to.strftime(DEFAULT_SERVER_DATE_FORMAT)),
'date_from':
__(self.date_from)
and las_date_from.strftime(DEFAULT_SERVER_DATE_FORMAT)
or False,
'date_to':
las_date_to.strftime(DEFAULT_SERVER_DATE_FORMAT),
})
self.comparison_line_ids = [(5)]
self.comparison_line_ids = [(0, 0, vals) for vals in period_vals]
def _header_sheet(sheet):
# sheet.write(0, 4, report.name, report_format)
sheet.merge_range('A1:J1', report.name, report_name_format)
for r in range(7):
sheet.set_row(r, 20)
sheet.write(2, 0, _('Company:'), gray_format)
sheet.write(
2,
1,
wizard.company_id.name,
)
sheet.write(3, 0, _('Print on '), gray_format)
sheet.write(3, 1, report.print_time)
sheet.write(4, 6, _('Start Date : '), gray_format)
sheet.write(4, 7,
__(wizard.date_from) if __(wizard.date_from) else '')
sheet.write(4, 8, _('End Date :'), gray_format)
sheet.write(4, 9, __(wizard.date_to) if __(wizard.date_to) else '')
sheet.write(2, 3, _('Target Moves:'), gray_format)
sheet.write(
2, 4,
_('All Entries')
if wizard.target_move == 'all' else _('All Posted Entries'))
sheet.merge_range(
'D4:E4',
_('Only UnReconciled Entries') if wizard.reconciled is False
else _('With Reconciled Entries'), bold)
sheet.merge_range(
'D5:E5',
_('Initial balance with history : %s') %
'True' if wizard.summary is True else 'False')
sheet.merge_range(
'G3:G4',
_('Filter by account \n Filter = %s ') %
wizard.account_methode, gray_format)
accounts_str = ''.join(
[a.name + ' / \n' for a in wizard.account_in_ex_clude])
sheet.merge_range('H3:H4', accounts_str)
def check_period_for_this_date(self):
period = self.env['periods'].search([['date_start', '<=', __(self.date)], ['date_end', '>=', __(self.date)]])
if not period:
raise ValidationError(_(
"It seems that there is no active period for the selected date... In order to accept this transaction \
you have to communicate with your financial manager to create or activate a financial period for the \
selected date"))
if period.year_id.state != 'active':
raise ValidationError(_(
"It seems that there is no active financial year for the selected date... In order to accept this \
transaction you have to communicate with your financial manager to create or activate a financial \
period for the selected date"))
def _set_line(line):
doctype = {
'out_invoice': 'Customer Invoice',
'in_invoice': 'Vendor Bill',
'out_refund': 'Customer Refund',
'in_refund': 'Vendor Refund',
'outbound': 'Send Money',
'inbound': 'Receive Money',
'transfer': 'Internal Transfer',
'manual': 'Manual journal entry'
}
doctype = doctype[line.get('doctype', 'manual')
or 'manual']
sheet.write(
i, 0,
get_date_format(__(line.get('date', '')))
if line.get('type_view') != 'init' else 'INIT')
sheet.write(i, 1, line.get('j_code', ''))
sheet.write(i, 2, line.get('a_code', ''))
sheet.write(i, 3, line.get('a_name', ''))
sheet.write(
i, 4, "%s - %s" %
(line.get('an_code', ''), line.get('an_name', '')) if
line.get('an_code', '') else line.get('an_name', ''))
sheet.write(i, 5, line.get('move_name', ''))
sheet.write(i, 6, line.get('label', ''))
sheet.write(i, 7, line.get('displayed_name', ''))
sheet.write(i, 8, doctype)
sheet.write(i, 9, line.get('partner_name', ''))
sheet.write(
i, 10,
get_date_format(__(line.get('date_maturity', ''))))
sheet.write(i, 11, _get_data_float(line.get('debit', '')),
currency_format)
sheet.write(i, 12, _get_data_float(line.get('credit', '')),
currency_format)
sheet.write(i, 13,
_get_data_float(line.get('cumul_balance', '')),
currency_format)
def onchange_number_of_periods(self):
if self.periode_date and self.number_of_periods:
periods = self.env['periods'].search(
[('date_start', '<', __(self.periode_date.date_start))],
limit=self.number_of_periods,
order="date_start desc")
period_vals = []
for period in periods:
period_vals.append({
'name':
'From (%s) To (%s)' % (self.date_from and period.date_start
or 'beginning', period.date_end),
'date_from':
self.date_from and period.date_start or False,
'date_to':
period.date_end,
})
self.comparison_line_ids = [(5)]
self.comparison_line_ids = [(0, 0, vals) for vals in period_vals]
elif self.fiscal_year and self.number_of_periods:
periods = self.env['fiscal.year'].search(
[('start_date', '<', self.fiscal_year.start_date)],
limit=self.number_of_periods,
order="start_date desc")
period_vals = []
for period in periods:
period_vals.append({
'name':
'From (%s) To (%s)' % (self.date_from and period.start_date
or 'beginning', period.start_end),
'date_from':
self.date_from and period.start_date or False,
'date_to':
period.start_end,
})
self.comparison_line_ids = [(5)]
self.comparison_line_ids = [(0, 0, vals) for vals in period_vals]
def _sql_init_balance_tags(self):
company = self.company_id
# initial balance partner
query = """INSERT INTO account_report_standard_ledger_line(report_id, create_uid, create_date, account_id, partner_id, analytic_id, employee_id, analytic_account_id, type, type_view, date, debit, credit, balance, cumul_balance, company_currency_id, reconciled, report_object_id)
WITH matching_in_futur_before_init (id) AS
(
SELECT DISTINCT
afr.id as id
FROM
account_full_reconcile afr
INNER JOIN account_move_line aml ON aml.full_reconcile_id=afr.id
WHERE
aml.company_id = %s
AND aml.date >= %s
)
SELECT
%s AS report_id,
%s AS create_uid,
NOW() AS create_date,
MIN(aml.account_id),
CASE WHEN %s = 'partner' THEN MIN(aml.partner_id) ELSE NULL END,
CASE WHEN %s = 'sub_account' THEN MIN(tag.id) ELSE NULL END,
CASE WHEN %s = 'employees' THEN MIN(aml.employee_id) ELSE NULL END,
CASE WHEN %s = 'analytic' THEN MIN(aml.analytic_account_id) ELSE NULL END,
'0_init' AS type,
'init' AS type_view,
%s AS date,
CASE WHEN %s THEN COALESCE(SUM(aml.debit), 0) ELSE CASE WHEN COALESCE(SUM(aml.balance), 0) <= 0 THEN 0 ELSE COALESCE(SUM(aml.balance), 0) END END AS debit,
CASE WHEN %s THEN COALESCE(SUM(aml.credit), 0) ELSE CASE WHEN COALESCE(SUM(aml.balance), 0) >= 0 THEN 0 ELSE COALESCE(-SUM(aml.balance), 0) END END AS credit,
COALESCE(SUM(aml.balance), 0) AS balance,
COALESCE(SUM(aml.balance), 0) AS cumul_balance,
%s AS company_currency_id,
FALSE as reconciled,
MIN(ro.id) AS report_object_id
FROM
account_report_standard_ledger_report_object ro
JOIN account_move_line aml ON (ro.report_id ilike aml.tags_str)
LEFT JOIN account_account acc ON (aml.account_id = acc.id)
LEFT JOIN account_account_type acc_type ON (acc.user_type_id = acc_type.id)
LEFT JOIN account_move m ON (aml.move_id = m.id)
LEFT JOIN matching_in_futur_before_init mif ON (aml.full_reconcile_id = mif.id)
WHERE
m.state IN %s
AND ro.report_id = %s
AND aml.company_id = %s
AND aml.date < %s
--AND acc_type.include_initial_balance = TRUE
AND aml.journal_id IN %s
AND aml.account_id IN %s
--AND (%s != 'analytic' OR aml.analytic_account_id IN %s)
AND (%s IN ('account', 'journal', 'sub_account', 'employees','analytic') OR aml.partner_id IN %s)
--AND ((%s AND acc.compacted = TRUE) OR acc.type_third_parties = 'no' OR (aml.full_reconcile_id IS NOT NULL AND mif.id IS NULL))
""" + self._general_where_cluster() + """
GROUP BY
ro.object_id
HAVING
CASE
WHEN %s = FALSE THEN ABS(SUM(aml.balance)) > %s
ELSE ABS(SUM(aml.debit)) > %s OR ABS(SUM(aml.debit)) > %s OR ABS(SUM(aml.balance)) > %s
END
"""
params = [
# matching_in_futur
company.id,
__(self.report_id.date_from),
# init_account_table
# SELECT
self.report_id.id,
self.env.uid,
self.type,
self.type,
self.type,
self.type, # self.type, self.type, self.type, self.type,
__(self.report_id.date_from),
self.init_balance_history,
self.init_balance_history,
self.company_currency_id.id,
# FROM
# self.type, self.type, self.analytic_recursive, self.type, self.type,
# WHERE
(
'posted', ) if self.target_move == 'posted' else (
'posted',
'draft',
),
self.report_id.id,
company.id,
__(self.report_id.date_from),
tuple(self.journal_ids.ids) if self.journal_ids else (None, ),
tuple(self.account_ids.ids) if self.account_ids else (None, ),
self.type,
tuple(self.analytic_account_ids.ids)
if self.analytic_account_ids else (None, ),
self.type,
tuple(self.partner_ids.ids) if self.partner_ids else (None, ),
self.compact_account,
# HAVING
self.init_balance_history,
self.company_currency_id.rounding,
self.company_currency_id.rounding,
self.company_currency_id.rounding,
self.company_currency_id.rounding,
]
self.env.cr.execute(query, tuple(params))
# res = self.env.cr.dictfetchall()
# print res
pass
def generate_close_move(self):
accounts = self.env['account.account'].search([['user_type_id.location', 'in', ['income', 'expense']]])
if not accounts:
raise ValidationError(_("No Income or expense account"))
if not self.close_account_id:
raise ValidationError(_("Please assign Close Account to close Profit and loss in it"))
if not self.close_journal_id:
raise ValidationError(_("Please specify Close Journal"))
Accounts = [str(a.id) for a in accounts]
Accounts = ','.join(Accounts)
sql = """SELECT account_id, SUM(debit) , SUM(credit), analytic_account_id
FROM account_move_line
WHERE account_id IN(%s) AND %s AND NOT (debit=0 AND credit=0)
GROUP BY account_id, analytic_account_id
ORDER BY account_id, analytic_account_id """ % (Accounts, self.where_cluster())
self.env.cr.execute(sql)
rows = self.env.cr.fetchall()
ctx = dict(self.env.context.copy(), check_move_validity=False)
total = 0
lines = []
seq = 0
for row in rows:
seq += 1
debit = row[1]
credit = row[2]
balance = abs(debit - credit)
new_debit = (credit > debit) and balance or 0
new_credit = (debit > credit) and balance or 0
total = total + new_debit - new_credit
vals = {
# 'move_id': move.id,
'sequence': seq,
'name': 'Close %s' % self.name,
'debit': new_debit,
'credit': new_credit,
'account_id': row[0],
'analytic_account_id': row[3],
'date': __(self.start_end),
'date_maturity': __(self.start_end),
}
lines.append((0, 0, vals))
if total:
diff_vals = {
'sequence': seq + 1,
'name': 'Close %s' % self.name,
'account_id': self.close_account_id.id,
'debit': total < 0 and total or 0.0,
'credit': total > 0 and total or 0.0,
'analytic_account_id': False,
'date': __(self.start_end),
'date_maturity': __(self.start_end),
}
lines.append((0, 0, diff_vals))
move_vals = {
'journal_id': self.close_journal_id.id,
'date': __(self.start_end),
'ref': 'Close %s' % self.name,
'line_ids': lines
}
move = self.with_context(ctx).env['account.move'].create(move_vals)
self.close_move_id = move.id
def where_cluster(self):
return " date >='%s' and date <='%s'" % (__(self.start_date), __(self.start_end))
def get_period(self):
period = self.env['periods'].get_default_period(__(self.date))
self.period_id = period and period.id or False
