multi period financial report

3 files changed, 174 insertions, 49 deletions

diff --git a/indoteknik_custom/models/account_financial_report.py b/indoteknik_custom/models/account_financial_report.py
index 1bf6816a..315515f6 100644
--- a/indoteknik_custom/models/account_financial_report.py
+++ b/indoteknik_custom/models/account_financial_report.py
@@ -1,4 +1,4 @@
-from datetime import datetime
+from datetime import datetime, timedelta
 import json
 import datetime
 import io
@@ -12,6 +12,167 @@ except ImportError:
 
 class AccountingReport(models.TransientModel):
     _inherit = "accounting.report.xlsx"
+
+    by_month = fields.Boolean(_("Group By Month"))
+
+    def get_xlsx_report(self, options, response):
+        obj = self.search([('id', '=', options['form']['id'])])
+        if not obj.by_month:
+            res = super(AccountingReport, self).get_xlsx_report(options, response)
+            return res 
+        
+        output = io.BytesIO()
+        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
+
+        data = {}
+        data['form'] = obj.read([])[0]
+        
+        date_from = data['form']['date_from']
+        date_to = data['form']['date_to']
+
+        obj = self.search([('id', '=', options['form']['id'])])
+        data['form'] = obj.read([])[0]
+        comp_dic = {}
+        env_obj = obj.env['report.account.report_financial']
+        data['form']['used_context'] = {}
+        data['form']['used_context']['journal_ids'] = data['form']['journal_ids']
+        data['form']['used_context']['state'] = 'posted'
+        data['form']['used_context']['strict_range'] = True
+
+        sheet = workbook.add_worksheet()
+        format1 = workbook.add_format({'font_size': 16, 'align': 'center', 'bg_color': '#D3D3D3', 'bold': True})
+        format1.set_font_color('#000080')
+        format1.set_font_name('Times New Roman')
+        format2 = workbook.add_format({'font_size': 12, 'bold': True, 'bg_color': '#D3D3D3'})
+        format3 = workbook.add_format({'font_size': 10, 'bold': True})
+        format4 = workbook.add_format({'font_size': 10})
+        format6 = workbook.add_format({'font_size': 10, 'bold': True})
+        format7 = workbook.add_format({'font_size': 10})
+        format8 = workbook.add_format({'font_size': 12, 'bold': True, 'bg_color': '#D3D3D3'})
+        format1.set_align('center')
+        format1.set_align('vcenter')
+        format2.set_align('center')
+        format3.set_align('center')
+        format4.set_align('center')
+        format6.set_align('right')
+        format8.set_align('center')
+        sheet.set_column('E:E', 10, format4)
+        sheet.set_column('H:H', 10, format4)
+        sheet.set_column('I:I', 10, format4)
+        sheet.set_column('J:J', 10, format4)
+
+        date_ranges = self.generate_date_ranges(date_from, date_to)
+
+        col_number = 0
+        balance_col_number = 4
+        
+        for date_range in date_ranges:
+            row_number = 9
+            data['form']['used_context']['date_to'] = date_range['date_to']
+            data['form']['used_context']['date_from'] = date_range['date_from']
+            comp_dic['state'] = 'posted'
+            comp_dic['journal_ids'] = data['form']['journal_ids']
+            data['form']['comparison_context'] = comp_dic
+            data['account_report_id'] = data['form']['account_report_id']
+            accounting_data = env_obj.get_account_lines(data.get('form'))
+            currency = self.env.user.company_id.currency_id.symbol
+            format7.set_num_format('0.00 ' + currency)
+            format6.set_num_format('0.00 ' + currency)
+            report_date = datetime.datetime.now().strftime("%Y-%m-%d")
+            sheet.merge_range('A1:B1', _("Report Date"), format6)
+            sheet.merge_range('C1:D1', report_date, format7)
+            if obj.account_report_id.name:
+                sheet.merge_range(3, 0, 4, 3 + len(date_ranges), obj.account_report_id.name, format1)
+                sheet.merge_range('C3:G3', _("PT. Indoteknik Dotcom Gemilang"), format4)
+            if obj.target_move == 'all':
+                target_moves = 'All entries'
+            else:
+                target_moves = 'All posted entries'
+            sheet.merge_range('A7:B7', _("Target Moves:"), format6)
+            sheet.write('C7', target_moves, format7)
+            if obj.date_from:
+                sheet.write(row_number, col_number, _("Date From:"), format6)
+                sheet.write(row_number, col_number + 1, str(obj.date_from), format7)
+            if obj.date_to:
+                sheet.write(row_number, col_number + 3, _("Date To:"), format6)
+                sheet.write(row_number, balance_col_number, str(date_range['date_to']), format7)
+                
+            row_number += 1
+            if obj.debit_credit == 1:
+                sheet.merge_range('A9:G9', _("Name"), format8)
+                sheet.write('H9', _("Debit"), format2)
+                sheet.write('I9', _("Credit"), format2)
+                sheet.write('J9', _("Balance"), format2)
+                for values in accounting_data:
+                    if not self.env.user.company_id.parent_id:
+                        if values['level'] != 0:
+                            pass
+                    else:
+                        if values['level'] != 0:
+                            if values['level'] == 1:
+                                sheet.write(row_number, col_number, values['name'], format6)
+                                sheet.write(row_number, col_number + 7, values['debit'], format6)
+                                sheet.write(row_number, col_number + 8, values['credit'], format6)
+                                sheet.write(row_number, balance_col_number, values['balance'], format6)
+                                row_number += 1
+                            elif not values['account_type'] == 'sum':
+                                sheet.write(row_number, col_number, values['name'], format7)
+                                sheet.write(row_number, col_number + 7, values['debit'], format7)
+                                sheet.write(row_number, col_number + 8, values['credit'], format7)
+                                sheet.write(row_number, balance_col_number, values['balance'], format7)
+                                row_number += 1
+
+            if not obj.enable_filter and not obj.debit_credit:
+                sheet.write('A9', _("Name"), format8)
+                sheet.merge_range(8, 1, 8, len(date_ranges) + 3, _("Balance"), format2)
+                for values in accounting_data:
+                    if values['level'] != 0:
+                        if values['level'] == 1:
+                            # assets and liabilities
+                            sheet.write(row_number, col_number, values['name'], format6)
+                            sheet.write(row_number, balance_col_number, values['balance'], format6)
+                            row_number += 1
+                        elif not values['account_type'] == 'sum':
+                            sheet.write(row_number, col_number, values['name'], format7)
+                            sheet.write(row_number, balance_col_number, values['balance'], format7)
+                            row_number += 1
+            if obj.enable_filter and not obj.debit_credit:
+                sheet.merge_range('A9:H9', _("Name"), format8)
+                sheet.write('I9', _("Balance"), format2)
+                sheet.write('J9', data['form']['label_filter'], format2)
+                for values in accounting_data:
+                    if values['level'] != 0:
+                        if values['level'] == 1:
+                            sheet.write(row_number, col_number, values['name'], format6)
+                            sheet.write(row_number, balance_col_number, values['balance'], format6)
+                            sheet.write(row_number, col_number + 9, values['balance_cmp'], format6)
+                            row_number += 1
+                        elif not values['account_type'] == 'sum':
+                            sheet.write(row_number, col_number, values['name'], format7)
+                            sheet.write(row_number, balance_col_number, values['balance'], format7)
+                            sheet.write(row_number, col_number + 9, values['balance_cmp'], format7)
+                            row_number += 1
+            balance_col_number += 1
+        workbook.close()
+        output.seek(0)
+        response.stream.write(output.read())
+        output.close()
     
+    def generate_date_ranges(self, date_from, date_to):
+        current_date = date_from
+        date_ranges = []
+
+        while current_date <= date_to:
+            next_month = current_date.replace(day=1) + timedelta(days=32)
+            end_of_month = next_month - timedelta(days=next_month.day)
+            if end_of_month > date_to:
+                end_of_month = date_to
+            
+            date_ranges.append({
+                "date_from": current_date,
+                "date_to": end_of_month
+            })
+
+            current_date = end_of_month + timedelta(days=1)
 
-    
\ No newline at end of file
+        return date_ranges
\ No newline at end of file
diff --git a/indoteknik_custom/models/account_report_financial.py b/indoteknik_custom/models/account_report_financial.py
index c11fa25b..c1958842 100644
--- a/indoteknik_custom/models/account_report_financial.py
+++ b/indoteknik_custom/models/account_report_financial.py
@@ -38,10 +38,10 @@ class ReportFinancial(models.AbstractModel):
     def _compute_report_balance(self, reports):
         '''returns a dictionary with key=the ID of a record and value=the credit, debit and balance amount
            computed for this record. If the record is of type :
-               'accounts' : it's the sum of the linked accounts
-               'account_type' : it's the sum of leaf accoutns with such an account_type
-               'account_report' : it's the amount of the related report
-               'sum' : it's the sum of the children of this record (aka a 'view' record)'''
+               'accounts': it's the sum of the linked accounts
+               'account_type': it's the sum of leaf accoutns with such an account_type
+               'account_report': it's the amount of the related report
+               'sum': it's the sum of the children of this record (aka a 'view' record)'''
         res = {}
         fields = ['credit', 'debit', 'balance']
         for report in reports:
diff --git a/indoteknik_custom/views/account_financial_report_view.xml b/indoteknik_custom/views/account_financial_report_view.xml
index 7c156599..c524f1c0 100644
--- a/indoteknik_custom/views/account_financial_report_view.xml
+++ b/indoteknik_custom/views/account_financial_report_view.xml
@@ -1,51 +1,15 @@
 <?xml version="1.0" encoding="utf-8"?>
 <odoo>
- 
-    <record id="account_financial_report_view_custom" model="ir.ui.view">
-        <field name="name">Common Report</field>
+
+    <record id="custom_accounting_report_view" model="ir.ui.view">
+        <field name="name">Accounting Report</field>
         <field name="model">accounting.report.xlsx</field>
+        <field name="inherit_id" ref="account_reports_xlsx.accounting_report_view"/>
         <field name="arch" type="xml">
-        <form string="Report Options">
-            <group col="4">
-                <field name="account_report_id" domain="[('parent_id','=',False)]"/>
-                <field name="target_move" widget="radio"/>
-                <field name="enable_filter"/>
-                <field name="debit_credit" attrs="{'invisible': [('enable_filter','=',True)]}"/>
-                <field name="date_from"/>
-                <field name="date_to"/>
-            </group>
-            <group>
-                <notebook tabpos="up" colspan="4">
-                    <page string="Comparison" name="comparison" attrs="{'invisible': [('enable_filter','=',False)]}">
-                        <group>
-                            <field name="label_filter" attrs="{'required': [('enable_filter', '=', True)]}"/>
-                            <field name="filter_cmp"/>
-                        </group>
-                        <group string="Dates" attrs="{'invisible':[('filter_cmp', '!=', 'filter_date')]}">
-                            <field name="date_from_cmp" attrs="{'required':[('filter_cmp', '=', 'filter_date')]}"/>
-                            <field name="date_to_cmp" attrs="{'required':[('filter_cmp', '=', 'filter_date')]}"/>
-                        </group>
-                    </page>
-                </notebook>
-                <field name="company_id" options="{'no_create': True}" groups="base.group_multi_company"/>
-            </group>
-            <footer>
-                <button name="check_report" string="Print" type="object" default_focus="1" class="oe_highlight"/>
-                <button string="Cancel" class="btn btn-secondary" special="cancel" />
-            </footer>
-        </form>
+            <field name="date_to" position="after">
+                <field name="by_month"/>
+            </field>
         </field>
     </record>
 
-    <record id="action_account_report_custom" model="ir.actions.act_window">
-        <field name="name">Financial Reports</field>
-        <field name="res_model">accounting.report.xlsx</field>
-        <field name="type">ir.actions.act_window</field>
-        <field name="view_mode">form</field>
-        <field name="view_id" ref="account_financial_report_view_custom"/>
-        <field name="target">new</field>
-    </record>
-
-    <menuitem id="menu_account_report" name="Financial Report" action="action_account_report_custom" parent="account.menu_finance_reports" sequence="250"/>
-
 </odoo>