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
   

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

   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
   

   1 2 3 4 5 6 7 8 9

   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
   

   1 2 3 4 5 6 7 8 9 10 11 12 13

   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
   

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

   <?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
   

   1 2 3 4 5 6 7 8 9 10 11 12 13

   {     '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.