def _get_json_activity_data(self): for journal in self: activities = [] # search activity on move on the journal sql_query = ''' SELECT act.id, act.res_id, act.res_model, act.summary, act_type.name as act_type_name, act_type.category as activity_category, act.date_deadline, m.date, CASE WHEN act.date_deadline < CURRENT_DATE THEN 'late' ELSE 'future' END as status FROM account_move m LEFT JOIN mail_activity act ON act.res_id = m.id LEFT JOIN mail_activity_type act_type ON act.activity_type_id = act_type.id WHERE act.res_model = 'account.move' AND m.journal_id = %s ''' self.env.cr.execute(sql_query, (journal.id,)) for activity in self.env.cr.dictfetchall(): activities.append({ 'id': activity.get('id'), 'res_id': activity.get('res_id'), 'res_model': activity.get('res_model'), 'status': activity.get('status'), 'name': (activity.get('summary') or activity.get('act_type_name')), 'activity_category': activity.get('activity_category'), 'date': odoo_format_date(self.env, activity.get('date_deadline')) }) journal.json_activity_data = json.dumps({'activities': activities})
def _get_json_activity_data(self): for journal in self: activities = [] # search activity on move on the journal sql_query = ''' SELECT act.id, act.res_id, act.res_model, act.summary, act_type.name as act_type_name, act_type.category as activity_category, act.date_deadline, CASE WHEN act.date_deadline < CURRENT_DATE THEN 'late' ELSE 'future' END as status FROM account_move m LEFT JOIN mail_activity act ON act.res_id = m.id LEFT JOIN mail_activity_type act_type ON act.activity_type_id = act_type.id WHERE act.res_model = 'account.move' AND m.journal_id = %s ''' self.env.cr.execute(sql_query, (journal.id,)) for activity in self.env.cr.dictfetchall(): activities.append({ 'id': activity.get('id'), 'res_id': activity.get('res_id'), 'res_model': activity.get('res_model'), 'status': activity.get('status'), 'name': activity.get('summary') or activity.get('act_type_name'), 'activity_category': activity.get('activity_category'), 'date': odoo_format_date(self.env, activity.get('date_deadline')) }) journal.json_activity_data = json.dumps({'activities': activities})
def _get_json_activity_data(self): for journal in self: activities = [] # search activity on move on the journal sql_query = ''' SELECT act.id, act.res_id, act.res_model, act.summary, act_type.name as act_type_name, act_type.category as activity_category, act.date_deadline, m.date, CASE WHEN act.date_deadline < CURRENT_DATE THEN 'late' ELSE 'future' END as status FROM account_move m LEFT JOIN mail_activity act ON act.res_id = m.id LEFT JOIN mail_activity_type act_type ON act.activity_type_id = act_type.id WHERE act.res_model = 'account.move' AND m.journal_id = %s ''' self.env.cr.execute(sql_query, (journal.id, )) for activity in self.env.cr.dictfetchall(): act = { 'id': activity.get('id'), 'res_id': activity.get('res_id'), 'res_model': activity.get('res_model'), 'status': activity.get('status'), 'name': (activity.get('summary') or activity.get('act_type_name')), 'activity_category': activity.get('activity_category'), 'date': odoo_format_date(self.env, activity.get('date_deadline')) } if activity.get( 'activity_category') == 'tax_report' and activity.get( 'res_model') == 'account.move': if self.env['account.move'].browse( activity.get('res_id') ).company_id.account_tax_periodicity == 'monthly': act['name'] += ' (' + format_date( activity.get('date'), 'MMM', locale=get_lang(self.env).code) + ')' else: act['name'] += ' (' + format_date( activity.get('date'), 'QQQ', locale=get_lang(self.env).code) + ')' activities.append(act) journal.json_activity_data = json.dumps({'activities': activities})
def _add_header(self, sheet, data, record, workbook): quo_header_format = workbook.add_format({ 'bold': 1, }) quo_header_format_border_bottom = workbook.add_format({ 'bold': 1, 'bottom': 1, }) quo_header_format_border_right = workbook.add_format({ 'right': 1, }) quo_header_format_border_left = workbook.add_format({ 'left': 1, 'bold': 1, }) quo_header_format_border_left_bottom = workbook.add_format({ 'left': 1, 'bottom': 1, 'bold': 1, }) quo_header_format_border_right_bottom = workbook.add_format({ 'right': 1, 'bottom': 1, }) quo_header_format_border_left_top = workbook.add_format({ 'left': 1, 'top': 1, }) quo_header_format_border_right_top = workbook.add_format({ 'right': 1, 'top': 1, }) addr_format = workbook.add_format({ 'bold': 1, }) addr_format.set_text_wrap() quo_header_format_border = workbook.add_format({ 'bold': 1, 'top': 2, 'left': 1, 'right': 1, }) doubledotformat = workbook.add_format({'align': 'right'}) row = 1 if record.company_id.partner_id.image_medium: base_url = self.env['ir.config_parameter'].sudo().get_param( 'web.base.url') url = '%s/%s' % (base_url, 'web/image?model=%s&field=%s&id=%s' % ('res.partner', 'image_medium', record.company_id.partner_id.id)) f = BytesIO( base64.b64decode(record.company_id.partner_id.image_medium)) imgoptions = { 'y_scale': 0.9, 'x_scale': 0.9, 'image_data': f, 'x_offset': 10, 'y_offset': 10 } sheet.insert_image( 'A%s' % row, 'product%s.jpg' % (record.company_id.partner_id.name, ), imgoptions) # LEFT COMPANY INFO # COMPANY NAME IN BIG FONT sheet.merge_range( "H1:R1", record.company_id.partner_id.name.upper(), workbook.add_format({ 'font_size': 24, 'bold': 1, 'align': 'right' })) company_header2 = workbook.add_format({ 'font_size': 11, 'bold': 1, 'align': 'right' }) sheet.merge_range( "H2:R2", record.company_id.partner_id.street + " %s" % (record.company_id.partner_id.street2 or '', ), company_header2) sheet.merge_range( "H3:R3", '%s%s%s%s%s' % ( record.company_id.partner_id.city or '', ' - ' if record.company_id.partner_id.state_id.name else '', record.company_id.partner_id.state_id.name or '', ' - ' if record.company_id.partner_id.country_id.id else '', record.company_id.partner_id.country_id.name or '', ), company_header2) sheet.merge_range( "H4:R4", 'Phone: %s' % (record.company_id.partner_id.phone or '', ), company_header2) sheet.merge_range( "H5:R5", '%s' % (record.company_id.partner_id.website or '', ), workbook.add_format({ 'font_size': 11, 'font_color': 'green', 'bold': 1, 'align': 'right', })) sheet.merge_range( "H6:R6", '%s' % (record.company_id.report_header or '', ), workbook.add_format({ 'font_size': 11, 'bold': 1, 'align': 'right', })) row = header2row = 8 # worksheet.set_column('A:R', 12) TITLE = workbook.add_format({ 'bold': 1, 'border': 0, 'align': 'center', 'valign': 'vcenter', 'fg_color': '#d0f7f7', 'font_size': 16 }) sheet.merge_range('A%s:R%s' % ( row, row, ), 'INVOICE', TITLE) # single record row += 1 sheet.write('A%s' % row, 'Seller Details', quo_header_format) row += 1 sheet.write('A%s' % row, 'Name', quo_header_format) sheet.write('C%s' % row, record.company_id.display_name) row += 1 sheet.write('A%s' % row, 'Address', quo_header_format) sheet.write( 'C%s' % row, '{} {} {} {}'.format( record.company_id.partner_id.street, record.company_id.partner_id.street2, record.company_id.partner_id.city, record.company_id.partner_id.country_id.name, )) row += 1 sheet.write('A%s' % row, 'Phone No.', quo_header_format) sheet.write('C%s' % row, record.company_id.partner_id.phone) row += 1 sheet.write('A%s' % row, 'Email Address', quo_header_format) sheet.write('C%s' % row, record.company_id.partner_id.email) row += 1 sheet.write('A%s' % row, 'Buyer Detail', quo_header_format) sheet.write('C%s' % row, record.partner_id.display_name) row += 1 sheet.write('A%s' % row, 'Name', quo_header_format) sheet.write('C%s' % row, record.partner_id.display_name) row += 1 sheet.write('A%s' % row, 'Address', quo_header_format) sheet.write( 'C%s' % row, '{} {} {} {}'.format( record.partner_id.street, record.partner_id.street2, record.partner_id.city, record.partner_id.country_id.name, )) row += 1 sheet.write('A%s' % row, 'Phone No.', quo_header_format) sheet.write('C%s' % row, record.partner_id.phone) row += 1 sheet.write('A%s' % row, 'Email Address', quo_header_format) sheet.write('C%s' % row, record.partner_id.email) # RIGHT row = header2row + 1 sheet.write('M%s' % row, 'Invoice No', quo_header_format) sheet.write('P%s' % row, record.name or '-') row += 1 sheet.write('M%s' % row, 'Invoice Date', quo_header_format or '-') sheet.write('P%s' % row, record.date_invoice) row += 1 sheet.write('M%s' % row, 'Sales Person', quo_header_format or '-') sheet.write('P%s' % row, record.user_id.name) row += 1 sheet.write('M%s' % row, 'Mobile', quo_header_format) sheet.write('P%s' % row, record.user_id.partner_id.mobile or '-') row += 1 sheet.write('M%s' % row, 'Emails', quo_header_format) sheet.write('P%s' % row, record.user_id.partner_id.email or '-') row += 1 sheet.write('M%s' % row, 'Sales Terms', quo_header_format) sheet.write('P%s' % row, record.incoterms_id.display_name or '-') row += 1 sheet.write('M%s' % row, 'Estimated Deliveries', quo_header_format) sheet.write( 'P%s' % row, odoo_format_date(env=record.env, value=record.estimated_delivery_date) or '') row += 1 sheet.write('M%s' % row, 'Port of Loading', quo_header_format) sheet.write('P%s' % row, record.port_loading_id.name or '') row += 1 sheet.write('M%s' % row, 'Payment Terms', quo_header_format) sheet.write('P%s' % row, record.payment_term_id.display_name or '') row += 1 sheet.write('M%s' % row, 'Bank Name', quo_header_format) sheet.write('P%s' % row, record.bank_account_id.display_name or '') row += 1 sheet.write('M%s' % row, 'Bank Addr', quo_header_format) sheet.write('P%s' % row, record.bank_account_id.address or '') row += 1 sheet.write('M%s' % row, 'Bank A/C', quo_header_format) sheet.write('P%s' % row, record.bank_account_id.bank_acc_number or '') row += 1 sheet.write('M%s' % row, 'Swift Code', quo_header_format) sheet.write('P%s' % row, record.bank_account_id.swift_code or '') sheet.freeze_panes(row + 2, 0) sheet.set_row(row + 2, 40) return row
def _write_header(self, sheet, data, record, workbook): quo_header_format = workbook.add_format({ 'bold': 1, }) quo_header_format_border_bottom = workbook.add_format({ 'bold': 1, 'bottom': 1, }) quo_header_format_border_right = workbook.add_format({ 'right': 1, }) quo_header_format_border_left = workbook.add_format({ 'left': 1, 'bold': 1, }) quo_header_format_border_left_bottom = workbook.add_format({ 'left': 1, 'bottom': 1, 'bold': 1, }) quo_header_format_border_right_bottom = workbook.add_format({ 'right': 1, 'bottom': 1, }) quo_header_format_border_left_top = workbook.add_format({ 'left': 1, 'top': 1, }) quo_header_format_border_right_top = workbook.add_format({ 'right': 1, 'top': 1, }) quo_header_format_border_left_right_top = workbook.add_format({ 'left': 1, 'top': 1, 'right': 1, }) quo_header_format_border_left_right_top_bold = workbook.add_format({ 'left': 1, 'top': 1, 'right': 1, 'bold': 1, }) addr_format = workbook.add_format({ 'bold': 1, }) addr_format.set_text_wrap() quo_header_format_border = workbook.add_format({ 'bold': 1, 'top': 2, 'left': 1, 'right': 1, }) doubledotformat = workbook.add_format({'align': 'right'}) row = 1 if record.company_id.partner_id.image_medium: base_url = self.env['ir.config_parameter'].sudo().get_param( 'web.base.url') url = '%s/%s' % (base_url, 'web/image?model=%s&field=%s&id=%s' % ('res.partner', 'image_medium', record.company_id.partner_id.id)) f = BytesIO( base64.b64decode(record.company_id.partner_id.image_medium)) imgoptions = { 'y_scale': 0.9, 'x_scale': 0.9, 'image_data': f, 'x_offset': 40, 'y_offset': 10 } sheet.insert_image( 'A%s' % row, 'product%s.jpg' % (record.company_id.partner_id.name, ), imgoptions) # LEFT COMPANY INFO # COMPANY NAME IN BIG FONT sheet.merge_range( "F1:J1", record.company_id.partner_id.name.upper(), workbook.add_format({ 'font_size': 24, 'bold': 1, 'align': 'right' })) company_header2 = workbook.add_format({ 'font_size': 11, 'bold': 1, 'align': 'right' }) sheet.merge_range( "F2:J2", record.company_id.partner_id.street + ". %s" % (record.company_id.partner_id.street2 or '', ), company_header2) sheet.merge_range( "F3:J3", '%s%s%s%s%s' % ( record.company_id.partner_id.city or '', ' - ' if record.company_id.partner_id.state_id.name else '', record.company_id.partner_id.state_id.name or '', ' - ' if record.company_id.partner_id.country_id.id else '', record.company_id.partner_id.country_id.name or '', ), company_header2) sheet.merge_range( "F4:J4", 'Phone: %s' % (record.company_id.partner_id.phone or '', ), company_header2) sheet.merge_range( "F5:J5", '%s' % (record.company_id.partner_id.website or '', ), workbook.add_format({ 'font_size': 11, 'font_color': 'green', 'bold': 1, 'align': 'right', })) sheet.merge_range( "F6:J6", '%s' % (record.company_id.report_header or '', ), workbook.add_format({ 'font_size': 11, 'bold': 1, 'align': 'right', })) row = header2row = 8 QUOTATION_TITLE = workbook.add_format({ 'bold': 1, 'border': 0, 'align': 'center', 'valign': 'vcenter', 'fg_color': '#d0f7f7', 'font_size': 14 }) if record.state in ('draft', 'sent', 'to approve'): STATUS_CAPTION = 'REQUEST FOR QUOTATION' elif record.state in ('cancel'): STATUS_CAPTION = 'CANCELED PO' elif record.state in ('done', 'purchase'): STATUS_CAPTION = 'PURCHASE ORDER' sheet.merge_range('A%s:J%s' % ( row, row, ), STATUS_CAPTION, QUOTATION_TITLE) # single record row += 1 sheet.merge_range('A%s:C%s' % ( row, row, ), 'Buyer Details', quo_header_format_border_left_right_top) row += 1 sheet.write('A%s' % (row, ), 'Name', quo_header_format_border_left) sheet.write('C%s' % (row, ), ': %s' % record.company_id.display_name, quo_header_format_border_right) row += 1 sheet.write('A%s' % (row, ), 'Address', quo_header_format_border_left) sheet.write( 'C%s' % (row, ), ': {} {} {} {}'.format( record.company_id.partner_id.street, record.company_id.partner_id.street2, record.company_id.partner_id.city, record.company_id.partner_id.country_id.name, ), quo_header_format_border_right) row += 1 sheet.write('A%s' % (row, ), 'Phone No.', quo_header_format_border_left) sheet.write('C%s' % (row, ), ': %s' % record.company_id.partner_id.phone, quo_header_format_border_right) row += 1 sheet.write('A%s' % (row, ), 'Email Address', quo_header_format_border_left) sheet.write('C%s' % (row, ), ': %s' % record.company_id.partner_id.email, quo_header_format_border_right) row += 1 sheet.merge_range('A%s:C%s' % ( row, row, ), 'Vendor Detail', quo_header_format_border_left_right_top) # sheet.merge_range('A%s:A%s' % (row,(row+1),), 'No.', header_format) # sheet.write%sC % (row,)10', ': %s' % record.partner_id.display_name) row += 1 sheet.write('A%s' % (row, ), 'Name', quo_header_format_border_left) sheet.write('C%s' % (row, ), ': %s' % record.partner_id.display_name, quo_header_format_border_right) row += 1 sheet.write('A%s' % (row, ), 'Address', quo_header_format_border_left) sheet.write( 'C%s' % (row, ), ': {} {} {} {}'.format( record.partner_id.street, record.partner_id.street2, record.partner_id.city, record.partner_id.country_id.name, ), quo_header_format_border_right) row += 1 sheet.write('A%s' % (row, ), 'Phone No.', quo_header_format_border_left) sheet.write('C%s' % (row, ), ': %s' % record.partner_id.phone, quo_header_format_border_right) row += 1 sheet.merge_range('A%s:B%s' % ( row, row, ), 'Email Address', quo_header_format_border_left_bottom) sheet.write('C%s' % (row, ), ': %s' % record.partner_id.email, quo_header_format_border_right_bottom) # RIGHT # row = header2row header2row += 1 sheet.merge_range('I%s:J%s' % ( header2row, header2row, ), 'Purchase Details', quo_header_format_border_left_right_top_bold) header2row += 1 sheet.write('I%s' % header2row, 'Purchase No', quo_header_format_border_left) sheet.write('J%s' % header2row, ': %s' % record.name, quo_header_format_border_right) header2row += 1 sheet.write('I%s' % header2row, 'Purchase Date', quo_header_format_border_left) sheet.write( 'J%s' % header2row, ': %s' % odoo_format_date(env=record.env, value=record.date_order), quo_header_format_border_right) header2row += 1 sheet.write('I%s' % header2row, 'Purchaser', quo_header_format_border_left) sheet.write('J%s' % header2row, ': %s' % record.create_uid.name, quo_header_format_border_right) header2row += 1 sheet.write('I%s' % header2row, 'Mobile', quo_header_format_border_left) sheet.write( 'J%s' % header2row, ': %s' % record.create_uid.partner_id.mobile or '' if record.create_uid.partner_id.mobile else '', quo_header_format_border_right) header2row += 1 sheet.write('I%s' % header2row, 'Emails', quo_header_format_border_left) sheet.write('J%s' % header2row, ': %s' % record.create_uid.partner_id.email or '', quo_header_format_border_right) header2row += 1 sheet.write('I%s' % header2row, 'Terms', quo_header_format_border_left) sheet.write( 'J%s' % header2row, ': %s' % record.incoterm_id.name or '' if record.incoterm_id.id else '', quo_header_format_border_right) header2row += 1 sheet.write('I%s' % header2row, 'Estimated Deliveries', quo_header_format_border_left) sheet.write( 'J%s' % header2row, ': %s' % (odoo_format_date(env=record.env, value=record.date_planned)), quo_header_format_border_right) header2row += 1 sheet.write('I%s' % header2row, 'Payment Terms', quo_header_format_border_left_bottom) sheet.write('J%s' % header2row, ': %s' % record.payment_term_id.display_name or '', quo_header_format_border_right_bottom) # header2row+=1 # sheet.write('I%s' % header2row,'Port of Loading', quo_header_format) # sheet.write('J%s' % header2row,'') return row
def button_generate_excel(self): self = self.with_context(lang='vi_VN') output = io.BytesIO() workbook = xlsxwriter.Workbook(output) # format header_text = workbook.add_format({'text_wrap': True}) header_text.set_align('center') header_text.set_align('vcenter') header_text_bold = workbook.add_format({ 'text_wrap': True, 'bold': True }) header_text_bold.set_align('center') header_text_bold.set_align('vcenter') border_text = workbook.add_format({'text_wrap': True}) border_text.set_align('center') border_text.set_align('vcenter') border_text.set_border() header_table = workbook.add_format({ 'bold': True, 'valign': 'vcenter', 'align': 'center', 'text_wrap': True }) header_table.set_border() border_right_text = workbook.add_format({'text_wrap': True}) border_right_text.set_align('right') border_right_text.set_align('vcenter') border_right_text.set_border() border_currency_right_text = workbook.add_format() border_currency_right_text.set_align('right') border_currency_right_text.set_align('vcenter') border_currency_right_text.set_border() border_currency_right_text.set_num_format('#,##0.00') border_left_text = workbook.add_format() border_left_text.set_align('left') border_left_text.set_align('vcenter') border_left_text.set_border() #Header header_report_bold = workbook.add_format({ 'bold': True, 'valign': 'vcenter', 'align': 'center', 'text_wrap': True }) # set body row = 5 footer_row = 0 for acc in self.account_ids: sheet = workbook.add_worksheet(str(acc.code)) company_id = self.env.user.company_id if self.with_currency: total_init_debit_cur = 0 total_init_credit_cur = 0 total_init_cur = 0 currency = '' curr_initial_balance = self.env[ 'account.move.line.ctp'].search([ ('move_id.state', '=', 'posted'), ('move_id.date', '<', self.start_date), '|', ('dr_account_id', '=', acc.id), ('cr_account_id', '=', acc.id) ]).sorted(lambda r: (r.move_id.date, r.move_id.name)) for ctp in curr_initial_balance: if ctp.currency_id: currency = ctp.currency_id.name or '' amount_pos = 'debit' if ctp.dr_account_id.id == acc.id: amount_pos = 'debit' elif ctp.cr_account_id.id == acc.id: amount_pos = 'credit' if amount_pos == 'debit': total_init_debit_cur += ctp.countered_amt_currency elif amount_pos == 'credit': total_init_credit_cur += ctp.countered_amt_currency total_init_cur = total_init_debit_cur - total_init_credit_cur sheet.set_column('A:I', 25) sheet.set_column('J:K', 30) sheet.merge_range('A1:B1', _("%s") % (company_id.name, )) sheet.merge_range( "A2:E2", _("%s, %s, %s, %s, %s") % ( company_id.street or '', company_id.street2 or '', company_id.city or '', company_id.state_id.name or '', company_id.country_id.name or '', )) sheet.set_row(row - 1, 30) headmergecol = 'G' if self.with_currency: headmergecol = 'K' sheet.merge_range( _('A%s:%s%s') % (row, headmergecol, row), _("SỔ CHI TIẾT TÀI KHOẢN\n(Journal Ledger)"), header_report_bold) # sheet.set_row(row+1, 35) # sheet.merge_range(_('A%s:K%s') % (row+1,row+1),_("Tài khoản: %s - %s\nAccount: %s - %s") % (acc.code, acc.name, acc.code, acc.name, ), header_report_bold) sheet.merge_range( _('A%s:%s%s') % (row + 1, headmergecol, row + 1), _("Tài khoản: %s - %s") % ( acc.code, acc.name, ), header_report_bold) sheet.merge_range( _('A%s:%s%s') % (row + 2, headmergecol, row + 2), _("Account: %s - %s") % ( acc.code, acc.en_name, ), header_report_bold) sheet.merge_range( _('A%s:%s%s') % (row + 3, headmergecol, row + 3), _("Từ ngày %s đến ngày %s") % (odoo_format_date(self.env, self.start_date), odoo_format_date(self.env, self.end_date)), header_report_bold) sheet.merge_range( _('A%s:%s%s') % (row + 4, headmergecol, row + 4), _("From %s To %s") % (odoo_format_date(self.env, self.start_date), odoo_format_date(self.env, self.end_date)), header_report_bold) #Header Table sheet.set_row(row + 6, 30) sheet.merge_range( _('A%s:B%s') % (row + 7, row + 7), _("Chứng từ\n(Document)"), header_table) sheet.merge_range( _('F%s:G%s') % (row + 7, row + 7), _("Số phát sinh\n(Amount incurred)"), header_table) if self.with_currency: sheet.merge_range( _('J%s:K%s') % (row + 7, row + 7), _("Số phát sinh ngoại tệ\n(Amount incurred in foregin currency)" ), header_table) sheet.merge_range( _('C%s:C%s') % (row + 7, row + 8), _("Khách hàng\n(Partner)"), header_table) sheet.merge_range( _('D%s:D%s') % (row + 7, row + 8), _("Diễn giải\n(Description)"), header_table) sheet.merge_range( _('E%s:E%s') % (row + 7, row + 8), _("Tài khoản đối ứng\n(Counterpart)"), header_table) if self.with_currency: sheet.merge_range( _('H%s:H%s') % (row + 7, row + 8), _("Ngoại tệ\n(Currencies)"), header_table) sheet.merge_range( _('I%s:I%s') % (row + 7, row + 8), _("Tỷ giá\n(Exchange rate)"), header_table) sheet.set_row(row + 7, 30) sheet.write(_('A%s') % (row + 8), _("Ngày\n(Date)"), header_table) sheet.write(_('B%s') % (row + 8), _("Số\n(Number)"), header_table) sheet.write(_('F%s') % (row + 8), _("Nợ\n(Debit)"), header_table) sheet.write(_('G%s') % (row + 8), _("Có\n(Credit)"), header_table) if self.with_currency: sheet.write( _('J%s') % (row + 8), _("Nợ\n(Debit)"), header_table) sheet.write( _('K%s') % (row + 8), _("Có\n(Credit)"), header_table) # move_line = self.env['account.move.line'].search([('move_id.state','=','posted'), ('account_id','=',acc.id), ('move_id.date','>=',self.start_date), ('move_id.date','<=',self.end_date)]) # move_ids = move_line.filtered(lambda r: r.move_id.state == 'posted' and r.move_id.date >= self.start_date and r.move_id.date <= self.end_date).mapped('move_id') # counterpart_ids = self.env['account.move.line.ctp'].search([('move_id','in',move_ids.ids)]) # counterpart_ids = move_line.mapped(lambda r:r.ctp_aml_ids.mapped('ctp_ids')).sorted(lambda r:(r.move_id.date,r.move_id.name)) counterpart_ids = self.env['account.move.line.ctp'].search([ ('move_id.state', '=', 'posted'), ('move_id.date', '>=', self.start_date), ('move_id.date', '<=', self.end_date), '|', ('dr_account_id', '=', acc.id), ('cr_account_id', '=', acc.id) ]).sorted(lambda r: (r.move_id.date, r.move_id.name)) gl_obj = self.env['account.general.ledger'] line_id = 'account_%d' % (acc.id, ) options = { 'unfolded_lines': [line_id], 'date': { 'string': 'Custom', 'period_type': 'month', 'mode': 'range', 'date_from': self.start_date, 'date_to': self.end_date, 'filter': '' }, 'all_entries': False, 'analytic': True, 'journals': [{ 'id': 'divider', 'name': self.create_uid.company_id.name }, { 'id': 7, 'name': 'Bank', 'code': 'BNK1', 'type': 'bank', 'selected': False }, { 'id': 6, 'name': 'Cash', 'code': 'CSH1', 'type': 'cash', 'selected': False }, { 'id': 5, 'name': 'Cash Basis Taxes', 'code': 'CABA', 'type': 'general', 'selected': False }, { 'id': 8, 'name': 'Closing Balance', 'code': 'CLOSE', 'type': 'general', 'selected': False }, { 'id': 1, 'name': 'Customer Invoices', 'code': 'INV', 'type': 'sale', 'selected': False }, { 'id': 4, 'name': 'Exchange Difference', 'code': 'EXCH', 'type': 'general', 'selected': False }, { 'id': 3, 'name': 'Miscellaneous Operations', 'code': 'MISC', 'type': 'general', 'selected': False }, { 'id': 2, 'name': 'Vendor Bills', 'code': 'BILL', 'type': 'purchase', 'selected': False }], 'unfold_all': False, 'unposted_in_period': False } no_result = False try: gl = gl_obj.with_context( no_format=True, date_from=self.start_date, date_to=self.end_date)._get_general_ledger_lines( options, line_id=line_id) except Exception as e: no_result = True if no_result: colend = 'G' if self.with_currency: colend = 'K' sheet.merge_range( _('A%s:%s%s') % ( row + 9, colend, row + 9, ), _("No Result"), border_text) row_line = row + 8 # row = row + row_line + 3 footer_row = row_line + 5 if self.with_currency: sheet.write( _('A%s') % (footer_row), _("NGƯỜI GHI SỔ"), header_text_bold) sheet.write( _('A%s') % (footer_row + 1), _("(Ký, họ tên)"), header_text) sheet.write( _('F%s') % (footer_row), _("KẾ TOÁN TRƯỞNG"), header_text_bold) sheet.write( _('F%s') % (footer_row + 1), _("(Ký, họ tên)"), header_text) sheet.write( _('K%s') % (footer_row - 1), _("Ngày........tháng........năm................"), header_text) sheet.write( _('K%s') % (footer_row), _("GIÁM ĐỐC"), header_text_bold) sheet.write( _('K%s') % (footer_row + 1), _("(Ký, họ tên)"), header_text) elif not self.with_currency: sheet.write( _('A%s') % (footer_row), _("NGƯỜI GHI SỔ"), header_text) sheet.write( _('A%s') % (footer_row + 1), _("(Ký, họ tên)"), header_text) sheet.write( _('D%s') % (footer_row), _("KẾ TOÁN TRƯỞNG"), header_text) sheet.write( _('D%s') % (footer_row + 1), _("(Ký, họ tên)"), header_text) sheet.write( _('G%s') % (footer_row - 1), _("Ngày........tháng........năm................"), header_text) sheet.write( _('G%s') % (footer_row), _("GIÁM ĐỐC"), header_text) sheet.write( _('G%s') % (footer_row + 1), _("(Ký, họ tên)"), header_text) # end continue sheet.set_row(row + 8, 30) # opening_balance = float(gl[1].get('columns')[3].get('name').split(' ')[0].replace(',','').replace('.','')) opening_balance = 0.0 try: if acc.user_type_id.include_initial_balance: opening_balance = float( gl[1].get('columns')[3].get('name')) except: opening_balance = 0.0 credit_opening_balance = 0.0 debit_opening_balance = abs(opening_balance) if opening_balance < 0.0: credit_opening_balance = abs(opening_balance) debit_opening_balance = 0.0 sheet.merge_range( _('A%s:E%s') % (row + 9, row + 9), _("Số dư đầu kỳ\n(Openning balance)"), border_text) sheet.write_formula( _('F%s') % (row + 9), _("=ABS(%s)") % debit_opening_balance, border_currency_right_text) sheet.write_formula( _('G%s') % (row + 9), _("=ABS(%s)") % credit_opening_balance, border_currency_right_text) if self.with_currency: sheet.write( _('H%s') % (row + 9), _("%s") % (currency, ), border_right_text) sheet.write( _('I%s') % (row + 9), _("%s") % ('', ), border_right_text) # sheet.merge_range(_('H%s:K%s') % (row+9,row+9),'', border_text) if total_init_cur > 0: sheet.write( _('J%s') % (row + 9), total_init_cur, border_currency_right_text) sheet.write( _('K%s') % (row + 9), 0, border_currency_right_text) else: sheet.write( _('J%s') % (row + 9), 0, border_currency_right_text) sheet.write( _('K%s') % (row + 9), abs(total_init_cur), border_currency_right_text) row_line = row + 10 total_debit = 0.0 total_credit = 0.0 total_debit_cur = 0.0 total_credit_cur = 0.0 for ctp in counterpart_ids: sheet.write( _('A%s') % (row_line), _("%s") % (odoo_format_date(self.env, ctp.move_id.date) or '', ), border_right_text) sheet.write( _('B%s') % (row_line), _("%s") % (ctp.move_id.name or '', ), border_left_text) sheet.write( _('C%s') % (row_line), _("%s") % (ctp.cr_aml_id.partner_id.name or '' if ctp.dr_account_id. id == acc.id else ctp.dr_aml_id.partner_id.name or '', ), border_text) sheet.write( _('D%s') % (row_line), _("%s") % (ctp.move_id.ref or '', ), border_text) sheet.write( _('E%s') % (row_line), _("%s") % (ctp.cr_account_id.code or '' if ctp.dr_account_id.id == acc.id else ctp.dr_account_id.code or '', ), border_right_text) amount_pos = 'debit' if ctp.dr_account_id.id == acc.id: amount_pos = 'debit' elif ctp.cr_account_id.id == acc.id: amount_pos = 'credit' debit_val = ctp.countered_amt if ctp.dr_account_id.id == acc.id else 0.00 credit_val = ctp.countered_amt if ctp.cr_account_id.id == acc.id else 0.00 sheet.write( _('F%s') % (row_line), debit_val, border_currency_right_text) sheet.write( _('G%s') % (row_line), credit_val, border_currency_right_text) total_debit = total_debit + float(debit_val) total_credit = total_credit + float(credit_val) if self.with_currency: sheet.write( _('H%s') % (row_line), _("%s") % (ctp.currency_id.name or '', ), border_right_text) currency_rate = 0.0 if ctp.currency_id.id: currency_rate = ctp.currency_id._get_rates( ctp.move_id.company_id, ctp.move_id.date.strftime('%Y-%m-%d')).get( ctp.currency_id.id) # currency_rate = currency_rate = ctp.countered_amt_currency and ( ctp.countered_amt / ctp.countered_amt_currency) or 0 sheet.write( _('I%s') % (row_line), currency_rate, border_currency_right_text) if amount_pos == 'debit': sheet.write( _('J%s') % (row_line), ctp.countered_amt_currency or 0.00, border_currency_right_text) sheet.write( _('K%s') % (row_line), 0.00, border_currency_right_text) total_debit_cur += ctp.countered_amt_currency elif amount_pos == 'credit': sheet.write( _('J%s') % (row_line), 0.00, border_currency_right_text) sheet.write( _('K%s') % (row_line), ctp.countered_amt_currency or 0.00, border_currency_right_text) total_credit_cur += ctp.countered_amt_currency row_line = row_line + 1 sheet.set_row(row_line - 1, 30) # index set_row() mulai dari 0 sheet.set_row(row_line, 30) sheet.merge_range( _('A%s:E%s') % (row_line, row_line), _("Tổng số phát sinh trong kỳ\n(Total amount incurred in the period)" ), header_table) sheet.write( _('F%s') % (row_line), total_debit or 0.00, border_currency_right_text) sheet.write( _('G%s') % (row_line), total_credit or 0.00, border_currency_right_text) closed_balance = (debit_opening_balance + total_debit) - ( credit_opening_balance + total_credit) debit_cb = abs(closed_balance) credit_cb = 0.0 if closed_balance < 0.0: debit_cb = 0.0 credit_cb = abs(closed_balance) sheet.merge_range( _('A%s:E%s') % (row_line + 1, row_line + 1), _("Số dư cuối kỳ\n(Closing balance)"), header_table) sheet.write_formula( _('F%s') % (row_line + 1), _("=ABS(%s)") % debit_cb, border_currency_right_text) sheet.write_formula( _('G%s') % (row_line + 1), _("=ABS(%s)") % credit_cb, border_currency_right_text) if self.with_currency: # sheet.merge_range(_('H%s:K%s') % (row_line+1,row_line+1),'', border_text) # sheet.merge_range(_('H%s:K%s') % (row_line,row_line),'', border_text) sheet.write( _('H%s') % (row_line), '', border_currency_right_text) sheet.write( _('I%s') % (row_line), '', border_currency_right_text) sheet.write( _('H%s') % (row_line + 1), '', border_currency_right_text) sheet.write( _('I%s') % (row_line + 1), '', border_currency_right_text) sheet.write( _('J%s') % (row_line), total_debit_cur, border_currency_right_text) sheet.write( _('K%s') % (row_line), total_credit_cur, border_currency_right_text) closing_balance = (total_init_debit_cur + total_debit_cur) - ( total_init_credit_cur + total_credit_cur) if closing_balance > 0: sheet.write( _('J%s') % (row_line + 1), closing_balance, border_currency_right_text) sheet.write( _('K%s') % (row_line + 1), 0, border_currency_right_text) else: sheet.write( _('J%s') % (row_line + 1), 0, border_currency_right_text) sheet.write( _('K%s') % (row_line + 1), abs(closing_balance), border_currency_right_text) # row = row + row_line + 3 footer_row = row_line + 5 if self.with_currency: sheet.write( _('A%s') % (footer_row), _("NGƯỜI GHI SỔ"), header_text_bold) sheet.write( _('A%s') % (footer_row + 1), _("(Ký, họ tên)"), header_text) sheet.write( _('F%s') % (footer_row), _("KẾ TOÁN TRƯỞNG"), header_text_bold) sheet.write( _('F%s') % (footer_row + 1), _("(Ký, họ tên)"), header_text) sheet.write( _('K%s') % (footer_row - 1), _("Ngày........tháng........năm................"), header_text) sheet.write( _('K%s') % (footer_row), _("GIÁM ĐỐC"), header_text_bold) sheet.write( _('K%s') % (footer_row + 1), _("(Ký, họ tên)"), header_text) elif not self.with_currency: sheet.write( _('A%s') % (footer_row), _("NGƯỜI GHI SỔ"), header_text_bold) sheet.write( _('A%s') % (footer_row + 1), _("(Ký, họ tên)"), header_text) sheet.write( _('D%s') % (footer_row), _("KẾ TOÁN TRƯỞNG"), header_text_bold) sheet.write( _('D%s') % (footer_row + 1), _("(Ký, họ tên)"), header_text) sheet.write( _('G%s') % (footer_row - 1), _("Ngày........tháng........năm................"), header_text) sheet.write( _('G%s') % (footer_row), _("GIÁM ĐỐC"), header_text_bold) sheet.write( _('G%s') % (footer_row + 1), _("(Ký, họ tên)"), header_text) workbook.close() out = base64.b64encode(output.getvalue()) output.close() filename = ('journal_ledger_%s.xlsx') % (self.start_date) return self.set_data_excel(out, filename)
def _add_quotation_header(self, sheet, data, record, workbook): quo_header_format = workbook.add_format({ 'bold': 1, }) quo_header_format_border_bottom = workbook.add_format({ 'bold': 1, 'bottom': 1, }) quo_header_format_border_right = workbook.add_format({ 'right': 1, }) quo_header_format_border_left = workbook.add_format({ 'left': 1, 'bold': 1, }) quo_header_format_border_left_bottom = workbook.add_format({ 'left': 1, 'bottom': 1, 'bold': 1, }) quo_header_format_border_right_bottom = workbook.add_format({ 'right': 1, 'bottom': 1, }) quo_header_format_border_left_top = workbook.add_format({ 'left': 1, 'top': 1, }) quo_header_format_border_right_top = workbook.add_format({ 'right': 1, 'top': 1, }) addr_format = workbook.add_format({ 'bold': 1, }) addr_format.set_text_wrap() quo_header_format_border = workbook.add_format({ 'bold': 1, 'top': 2, 'left': 1, 'right': 1, }) doubledotformat = workbook.add_format({'align': 'right'}) row = 1 if record.company_id.partner_id.image_medium: base_url = self.env['ir.config_parameter'].sudo().get_param( 'web.base.url') url = '%s/%s' % (base_url, 'web/image?model=%s&field=%s&id=%s' % ('res.partner', 'image_medium', record.company_id.partner_id.id)) f = BytesIO( base64.b64decode(record.company_id.partner_id.image_medium)) imgoptions = { 'y_scale': 0.9, 'x_scale': 0.9, 'image_data': f, 'x_offset': 10, 'y_offset': 10 } sheet.insert_image( 'A%s' % row, 'product%s.jpg' % (record.company_id.partner_id.name, ), imgoptions) # LEFT COMPANY INFO # COMPANY NAME IN BIG FONT sheet.merge_range( "H1:S1", record.company_id.partner_id.name.upper(), workbook.add_format({ 'font_size': 24, 'bold': 1, 'align': 'right' })) company_header2 = workbook.add_format({ 'font_size': 11, 'bold': 1, 'align': 'right' }) sheet.merge_range( "H2:S2", record.company_id.partner_id.street + " %s" % (record.company_id.partner_id.street2 or '', ), company_header2) sheet.merge_range( "H3:S3", '%s%s%s%s%s' % ( record.company_id.partner_id.city or '', ' - ' if record.company_id.partner_id.state_id.name else '', record.company_id.partner_id.state_id.name or '', ' - ' if record.company_id.partner_id.country_id.id else '', record.company_id.partner_id.country_id.name or '', ), company_header2) sheet.merge_range( "H4:S4", 'Phone: %s' % (record.company_id.partner_id.phone or '', ), company_header2) sheet.merge_range( "H5:S5", '%s' % (record.company_id.partner_id.website or '', ), workbook.add_format({ 'font_size': 11, 'font_color': 'green', 'bold': 1, 'align': 'right', })) sheet.merge_range( "H6:S6", '%s' % (record.company_id.report_header or '', ), workbook.add_format({ 'font_size': 11, 'bold': 1, 'align': 'right', })) row = header2row = 8 QUOTATION_TITLE = workbook.add_format({ 'bold': 1, 'border': 0, 'align': 'center', 'valign': 'vcenter', 'fg_color': '#d0f7f7', 'font_size': 14 }) SO_STATUS = dict(record._fields['state'].selection).get(record.state) sheet.merge_range('A%s:S%s' % ( row, row, ), SO_STATUS.upper(), QUOTATION_TITLE) # single record row += 1 sheet.merge_range('A%s:D%s' % ( row, row, ), 'Seller Details.', quo_header_format_border) row += 1 sheet.write('A%s' % (row, ), 'Name:', quo_header_format) # sheet.write('B%s' % (row,), ':', doubledotformat) sheet.write('C%s' % (row, ), ':', quo_header_format) sheet.write('D%s' % (row, ), record.company_id.display_name, quo_header_format_border_right) # START ADDRESS row += 1 sheet.write('A%s' % (row, ), 'Address', quo_header_format) # sheet.write('B%s' % (row,), ':', doubledotformat) sheet.write('C%s' % (row, ), ':', quo_header_format) sheet.write( 'D%s' % (row, ), '{} {} {} {}'.format( record.company_id.partner_id.street, record.company_id.partner_id.street2, record.company_id.partner_id.city, record.company_id.partner_id.country_id.name, ), quo_header_format_border_right) # UNCOMMENT IF SPLITTED # sheet.writeC'D%s' % (row,':', quo_header_format) # sheet.write('D%s' % (row,), '%s. %s' % (record.company_id.partner_id.street, record.company_id.partner_id.street2,), quo_header_format) # row += 1 # sheet.writeC'D%s' % (row,':', quo_header_format) # sheet.write('D%s' % (row,), '%s - %s %s' % (record.company_id.partner_id.city, record.company_id.partner_id.state_id.name,record.company_id.partner_id.zip,), quo_header_format) # row += 1 # sheet.writeC'D%s' % (row,':', quo_header_format) # sheet.write('D%s' % (row,), '%s' % (record.company_id.partner_id.country_id.name,), quo_header_format) # END ADDRESS row += 1 sheet.write('A%s' % (row, ), 'Phone No.', quo_header_format) # sheet.write('B%s' % (row,), ':', doubledotformat) sheet.write('C%s' % (row, ), ':', quo_header_format) sheet.write('D%s' % (row, ), record.company_id.partner_id.phone or '', quo_header_format_border_right) row += 1 sheet.write('A%s' % (row, ), 'Email Address', quo_header_format) # sheet.write('B%s' % (row,), ':', doubledotformat) sheet.write('C%s' % (row, ), ':', quo_header_format) sheet.write('D%s' % (row, ), record.company_id.partner_id.email or '', quo_header_format_border_right) row += 1 # sheet.write('A%s' % (row,),'Buyer Detail', quo_header_format) sheet.merge_range('A%s:D%s' % ( row, row, ), 'Buyer Details.', quo_header_format_border) # sheet.write(BC%s' % (row,':', doubledotformat) # sheet.writeC'D%s' % (row,':'d.display_name) # sheet.write('D%s' % (row,), record.partner_id.display_name) row += 1 sheet.write('A%s' % (row, ), 'Name', quo_header_format_border_left) # sheet.write('B%s' % (row,), ':', doubledotformat) sheet.write('C%s' % (row, ), ':', quo_header_format) sheet.write('D%s' % (row, ), record.partner_id.display_name, quo_header_format_border_right) row += 1 sheet.write('A%s' % (row, ), 'PIC', quo_header_format_border_left) # sheet.write('B%s' % (row,), ':', doubledotformat) sheet.write('C%s' % (row, ), ':', quo_header_format) sheet.write('D%s' % (row, ), record.pic_id.name or '', quo_header_format_border_right) row += 1 sheet.write('A%s' % (row, ), 'Address', quo_header_format_border_left) # sheet.write('B%s' % (row,), ':', doubledotformat) sheet.write('C%s' % (row, ), ':', quo_header_format) sheet.write( 'D%s' % (row, ), '{} {} {} {}'.format( record.partner_id.street, record.partner_id.street2, record.partner_id.city, record.partner_id.country_id.name, ), quo_header_format_border_right) row += 1 sheet.write('A%s' % (row, ), 'Phone No:.', quo_header_format_border_left) # sheet.write('B%s' % (row,), ':', doubledotformat) sheet.write('C%s' % (row, ), ':', quo_header_format) sheet.write('D%s' % (row, ), record.partner_id.phone or '', quo_header_format_border_right) row += 1 sheet.write('A%s' % (row, ), 'Email Address', quo_header_format_border_left_bottom) # sheet.write('B%s' % (row,), ':', doubledotformat) sheet.write('C%s' % (row, ), ':', quo_header_format_border_bottom) sheet.write('D%s' % (row, ), record.partner_id.email or '', quo_header_format_border_bottom) # RIGHT row = header2row + 1 sheet.write('R%s' % row, 'Quotation No', quo_header_format_border_left) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write('S%s' % row, ": %s" % (record.name, ), quo_header_format_border_right) row += 1 sheet.write('R%s' % row, 'Quotation Date', quo_header_format_border_right_top) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write( 'S%s' % row, ": %s" % (odoo_format_date(env=record.env, value=record.date_order), ), quo_header_format_border_right_top) row += 1 sheet.write('R%s' % row, 'Sales Person', quo_header_format_border_left) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write('S%s' % row, ": %s" % (record.user_id.name, ), quo_header_format_border_right) row += 1 sheet.write('R%s' % row, 'Mobile', quo_header_format_border_left) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write('S%s' % row, ": %s" % (record.user_id.partner_id.mobile or '', ), quo_header_format_border_right) row += 1 sheet.write('R%s' % row, 'Emails', quo_header_format_border_left) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write('S%s' % row, ": %s" % (record.user_id.partner_id.email or '', ), quo_header_format_border_right) row += 1 sheet.write('R%s' % row, 'Sales Terms', quo_header_format_border_left_top) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write('S%s' % row, ": %s" % (record.incoterm.display_name or '', ), quo_header_format_border_right_top) row += 1 sheet.write('R%s' % row, 'Estimated Deliveries', quo_header_format_border_left) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write( 'S%s' % row, ": %s" % (odoo_format_date( env=record.env, value=record.estimated_delivery_date), ), quo_header_format_border_right) row += 1 sheet.write('R%s' % row, 'Payment Terms', quo_header_format_border_left) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write('S%s' % row, ": %s" % (record.payment_term_id.display_name or '', ), quo_header_format_border_right) row += 1 sheet.write('R%s' % row, 'Port of Loading', quo_header_format_border_left) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write('S%s' % row, ": %s" % (record.port_loading_id.name or '', ), quo_header_format_border_right) row += 1 sheet.write('R%s' % row, 'Bank Name', quo_header_format_border_left) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write('S%s' % row, ": %s" % (record.bank_account_id.display_name or '', ), quo_header_format_border_right) row += 1 sheet.write('R%s' % row, 'Bank Addr', quo_header_format_border_left) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write('S%s' % row, ": %s" % (record.bank_account_id.address or '', ), quo_header_format_border_right) row += 1 sheet.write('R%s' % row, 'Bank A/C', quo_header_format_border_left) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write('S%s' % row, ": %s" % (record.bank_account_id.bank_acc_number or '', ), quo_header_format_border_right) row += 1 sheet.write('R%s' % row, 'Swift Code', quo_header_format_border_left_bottom) # sheet.write('R%s' % row, ':', quo_header_format) sheet.write('S%s' % row, ": %s" % (record.bank_account_id.swift_code or '', ), quo_header_format_border_right_bottom) sheet.freeze_panes(row + 2, 0) sheet.set_row(row + 2, 40) return row