SQL View dengan Tampilan Pivot saat Kustomisasi Odoo 17

Dalam membuat pelaporan saat pengembangan aplikasi berbasisi Odoo 17 kita mungkin akan menggabungkan beberapa tabel sekaligus. Kita dapat memberikan solusi dengan SQL view pada database PostgreSQL.

SQL View dengan tampilan pivot saat kustomisasi Odoo 17 adalah seperti contoh berikut ini

1. Memulai SSH filesystem
   
2. Model asrama.room.availability
   

   from odoo import models, fields, tools
   
   class AsramaRoomAvailability(models.Model):
       _name = 'asrama.room.availability'
       _auto = False
   
       room_id = fields.Many2one('asrama.room','Room', readonly=True)
       student_per_room = fields.Integer(string='Siswa tiap Room', readonly=True)
       availability = fields.Integer(string='Availability', readonly=True)
       amount = fields.Integer(string='Amount', readonly=True)
   
       def init(self):
           tools.drop_view_if_exists(self.env.cr, self._table)
           query = """
           CREATE OR REPLACE VIEW asrama_room_availability AS (
           SELECT
               min(a_room.id) as id,
               a_room.id as room_id,
               a_room.student_per_room as student_per_room,
               a_room.availability as availability,
               a_room.rent_amount as amount
               FROM 
                   asrama_room as a_room
               GROUP BY a_room.id
           );
           """
           self.env.cr.execute(query)
   

3. Models – Init
   

   from . import hostel
   from . import asrama_room
   from . import asrama_student
   from . import asrama_student_copy
   from . import asrama_amenities
   from . import asrama_categ
   from . import res_partner
   from . import asrama_terminate
   from . import asrama_room_availability
   

4. Seting security pada ir.model.access
   

   id,name,model_id:id,group_id:id,perm_read,perm_write,perm_create,perm_unlink
   access_hostel_manager_id,access.hostel.manager,asramaku.model_asrama_hostel,group_hostel_manager,1,1,1,1
   access_hostel_user_id,access.hostel.user,asramaku.model_asrama_hostel,group_hostel_user,1,0,0,0
   access_hostel_room_manager_id,access.hostel.room.manager,asramaku.model_asrama_room,asramaku.group_hostel_manager,1,1,1,1
   access_hostel_room_user_id,access.hostel.room.user,asramaku.model_asrama_room,asramaku.group_hostel_user,1,0,0,0
   access_hostel_amenities_manager_id,access.hostel.amenities.manager,asramaku.model_asrama_amenities,asramaku.group_hostel_manager,1,1,1,1
   access_hostel_amenities_user_id,access.hostel.amenities.user,asramaku.model_asrama_amenities,asramaku.group_hostel_user,1,0,0,0
   access_hostel_student_manager_id,access.hostel.student.manager,asramaku.model_asrama_student,asramaku.group_hostel_manager,1,1,1,1
   access_hostel_student_user_id,access.hostel.student.user,asramaku.model_asrama_student,asramaku.group_hostel_user,1,0,0,0
   access_hostel_category_manager_id,access.hostel.category.manager,asramaku.model_asrama_category,asramaku.group_hostel_manager,1,1,1,1
   access_hostel_category_user_id,access.hostel.category.user,asramaku.model_asrama_category,asramaku.group_hostel_user,1,0,0,0
   access_hostel_assign_room_student_wizard_id,access.hostel.assign.room.student.wizard,asramaku.model_assign_room_student_wizard,asramaku.group_hostel_manager,1,1,1,1
   access_hostel_asrama_room_availability_id,access.hostel.asrama.room.availability,asramaku.model_asrama_room_availability,asramaku.group_hostel_manager,1,1,1,1
   

5. View asrama_room_availability
   

   <?xml version="1.0" encoding="utf-8"?>
   <odoo>
   
       <!-- asrama.room.availability pivot view -->
       <record id="asrama_room_availability_view_pivot" model="ir.ui.view">
           <field name="name">asrama.room.availability.view.pivot</field>
           <field name="model">asrama.room.availability</field>
           <field name="arch" type="xml">
               <pivot string="Availability Analysis" disable_linking="True">
                   <field name="room_id" type="row"/>
                   <field name="student_per_room" type="measure"/>
                   <field name="availability" type="measure"/>
                   <field name="amount" type="measure"/>                
               </pivot>
           </field>
       </record>
   
       <!-- asrama.room.availability action window -->
       <record id="asrama_room_availability_action" model="ir.actions.act_window">
           <field name="name">Asrama Room Availability</field>
           <field name="res_model">asrama.room.availability</field>
           <field name="view_mode">pivot</field>
       </record>
   
       <menuitem id="asrama_room_availability_menu" name="Asrama Room Availability" 
           parent="hostel_main_menu" action="asrama_room_availability_action" sequence="5"/>
   
   </odoo>
   

6. Isi manifest
   

   {
       'name': 'Manajemen Asrama',
       "data":[
           "security/hostel_security.xml",
           "security/ir.model.access.csv",
           "data/data.xml",
           "wizard/assign_room_student.xml",
           "views/hostel.xml",
           "views/asrama_room.xml",
           "views/asrama_student.xml",
           "views/asrama_room_availability_view.xml",
       ],
   }
   

7. Start Odoo dengan upgrade modul
   
   
8. Melihat hasil tampilan
   Klik menu Asrama Room Availability:
   
   
   Tampilan pivot yang datanya diambil dari SQL View:
   
9. Melihat SQL View pada PgAdmin
   

Kunjungi www.proweb.co.id/implementasi-odoo/ untuk layanan mplementasi Odoo ERP sesuai kebutuhan anda.