Timmy's Column: Odoo教育訓練學習筆記 (5)

　　由於轉換職場跑道的緣故，許久沒有撰寫學習筆記或書摘心得等文章，原本自學內容以Python為核心，著重資料科學的技術與應用，但由於新工作的需求，必須轉而學習開放原始碼ERP系統－Odoo的開發，對於僅有類似系統分析經驗的我而言，這是非常難得的機會，可以讓我真正跨入系統工程師的領域。

　　由於自己並非資訊相關科系畢業，基礎知識與技能相當貧乏，透過此次學習Odoo系統基礎建置與開發，我才初次接觸了如何在Windows系統透過VM來使用Linux系統，以及終端機指令的操作，來一步一步建置Odoo系統，包括所需的Python套件、PostgreSQL資料庫系統、pgAdmin資料庫管理工具、Odoo系統資料夾結構、Odoo系統設定文件、PyCharm整合開發環境、系統更新與維護方式等等，需要了解與學習的技術非常非常的多，自己也僅學習使用了一小部分的工具與技巧而已。

　　系統開發由於牽涉到許多資料夾結構與不同文件如csv、py、xml等，因此未來使用Git做為示範檔案存放空間的需求或許也將越來越迫切，現階段仍純粹在網誌整理、發布文章；本系列一共六篇學習筆記，為花費大量時間綜合整理Peter Wu老師課堂教學、《Odoo快速入門與實戰》簡體書，以及零散的網路教學文章而得，前五篇的學習筆記完全操作使用Odoo社群版，第六篇的學習筆記則加入「康虎雲報表」的安裝與教學，可至「康虎軟件工作室」官方網站下載相關軟體，配合學習筆記的說明來安裝使用。

　　自學Odoo系統的建置開發相當不容易：在台灣，Odoo系統的知名度與普及性遠不如SAP、鼎新、Oracle等大廠，資訊廠商與開發人員的數量也相對稀少，就連參考書籍也完全沒有繁體中文的版本，僅有少數英文書與簡體書可以購買(而且這些書都寫得很糟)，加上這幾年Odoo系統的快速發展，不同的版本之間有不小的變動，學會的技術可能過幾年就完全不能用了…。綜合上述許多原因，即便本系列學習筆記已經整理很清楚了，對於完全零經驗的開發者而言，相信仍舊難以閱讀、理解。

　　目前Odoo系統在歐美與中國大陸的資源、社群與討論，都比台灣頻繁、密集得多，想要入門Odoo系統的學習者建議還是需要老師來帶領，才有辦法掌握操作、管理、開發的各項眉眉角角。

data資料夾－初次安裝模組時匯入系統的資料

l   Step 1在data資料夾內創建openacademy_course_list.xml：
<?xml version="1.0" encoding="utf-8"?>
<odoo>
    
    <data noupdate="1">
       
       
       
       <record id="openacademy_course_1" model="openacademy.course">
            <field name="course_no">A0001</field>
            <field name="course_name">國文</field>
            <field name="course_type">1</field>
       </record>
        <record id="openacademy_course_2" model="openacademy.course">
            <field name="course_no">A0002</field>
            <field name="course_name">數學</field>
            <field name="course_type">1</field>
       </record>
        <record id="openacademy_course_3" model="openacademy.course">
            <field name="course_no">A0003</field>
            <field name="course_name">英文</field>
            <field name="course_type">1</field>
       </record>
        <record id="openacademy_course_4" model="openacademy.course">
            <field name="course_no">B0001</field>
            <field name="course_name">歷史</field>
            <field name="course_type">2</field>
       </record>
        <record id="openacademy_course_5" model="openacademy.course">
            <field name="course_no">B0002</field>
            <field name="course_name">地理</field>
            <field name="course_type">2</field>
       </record>
        <record id="openacademy_course_6" model="openacademy.course">
            <field name="course_no">A0004</field>
            <field name="course_name">物理</field>
            <field name="course_type">1</field>
        </record>
        <record id="openacademy_course_7" model="openacademy.course">
            <field name="course_no">A0005</field>
            <field name="course_name">化學</field>
            <field name="course_type">1</field>
        </record>
        <record id="openacademy_course_8" model="openacademy.course">
            <field name="course_no">B0003</field>
            <field name="course_name">生物</field>
            <field name="course_type">2</field>
        </record>
    </data>
</odoo>

l Step 2在openacademy資料夾內修改__manifest__.py：
'data': [
'data/openacademy_course_list.xml',
],

匯出Excel檔(xls)

l   Step 1在wizards資料夾內創建openacademy_student_export_wizard.py：
# -*- coding: utf-8 -*-
# Author: Peter Wu

from odoo import models, fields, api
from odoo.exceptions import UserError
from io import BytesIO
import xlwt, base64

class openacademystudentexportwizard(models.TransientModel):
    _name = 'openacademy.student_export_wizard'

    student_name = fields.Char(string = '學生姓名')
    student_class = fields.Selection([('1', '一年級'), ('2', '二年級'), ('3', '三年級')], string = '年級')
    student_fm = fields.Selection([('M', '男'), ('F', '女')], string = '性別')

    def student_export_list(self):
        domain = []
        if self.student_name:
            domain.append(('student_name', 'ilike', self.student_name))
        if self.student_class:
            domain.append(('student_class', '=', self.student_class))
        if self.student_fm:
            domain.append(('student_fm', '=', self.student_fm))
        if not domain:
            domain = [(1, '=', 1)]

        # 搜尋的標的
        myrec = self.env['openacademy.student'].search(domain)

        # 匯出Excel檔的名稱
        if self.student_class == '1':
            stu_cla_chi = '一年級'
        elif self.student_class == '2':
            stu_cla_chi = '二年級'
        elif self.student_class == '3':
            stu_cla_chi = '三年級'
        else:
            stu_cla_chi = '全部年級'

        if self.student_fm == 'M':
            stu_fm_chi = '男'
        elif self.student_fm == 'F':
            stu_fm_chi = '女'
        else:
            stu_fm_chi = '全部性別'

        if not self.student_name and not self.student_class and not self.student_fm:
            myexportname = '全校'
        else:
            myexportname = '%s - %s - %s' % (stu_cla_chi, stu_fm_chi, self.student_name if self.student_name else '全部學生姓名')

        myxlsfilename = '學生名單 %s.xls' % myexportname
        myrundesc = '學生名單 %s.xls' % myexportname
        mytitle = '學生名單列表: %s' % myexportname

        # 匯出Excel檔的樣式設定
        borders = xlwt.Borders()
        borders.left = xlwt.Borders.THIN
        borders.right = xlwt.Borders.THIN
        borders.top = xlwt.Borders.THIN
        borders.bottom = xlwt.Borders.THIN
        borders.left_colour = 0x40
        borders.right_colour = 0x40
        borders.top_colour = 0x40
        borders.bottom_colour = 0x40

        center_alignment = xlwt.Alignment()
        center_alignment.horz = xlwt.Alignment.HORZ_CENTER
        center_alignment.vert = xlwt.Alignment.VERT_CENTER

        title_font = xlwt.Font()
        title_font.height = 0x00C8 * 3

        title_style = xlwt.XFStyle()
        title_style.borders = borders
        title_style.alignment = center_alignment
        title_style.font = title_font

        content_font = xlwt.Font()
        content_font.height = 0x00C8 * 1

        content_style = xlwt.XFStyle()
        content_style.borders = borders

        content_alignment = xlwt.Alignment()
        content_alignment.horz = xlwt.Alignment.HORZ_LEFT
        content_alignment.vert = xlwt.Alignment.VERT_CENTER

        content_style.alignment = content_alignment
        content_style.alignment.wrap = 1
        content_style.font = content_font

        # 創建並將資料寫入Excel檔
        wb = xlwt.Workbook(encoding = 'utf-8')
        ws1 = wb.add_sheet('學生名單列表')

        row = 1
        ws1.write(row, 4, (mytitle if mytitle else ''), title_style)
        row += 3
        titles1 = ['學生學號', '學生姓名', '聯絡人', '年級', '性別', '備註', ]
        col = 0
        for title in titles1:
            ws1.write(row, col, title, content_style)
            col += 1
        ws1.row(row).height = 1000
        ws1.col(0).width = 3600 # student_no
        ws1.col(1).width = 3600 # student_name
        ws1.col(2).width = 3600 # student_contact
        ws1.col(3).width = 3600 # student_class
        ws1.col(4).width = 1800 # student_fm
        ws1.col(5).width = 7200 # student_memo

        for line in myrec:
            s1 = line.student_no
            s2 = line.student_name
            s3 = line.student_contact
            s4 = line.student_class
            if s4 == '1':
                myclass = '一年級'
            elif s4 == '2' :
                myclass = '二年級'
            elif s4 == '3' :
                myclass = '三年級'
            s5 = line.student_fm
            if s5 == 'M':
                mysex = '男'
            elif s5 == 'F':
                mysex = '女'
            s6 = line.student_memo

            row += 1
            ws1.write(row, 0, (s1 if s1 else ' '), content_style)
            ws1.write(row, 1, (s2 if s2 else ' '), content_style)
            ws1.write(row, 2, (s3 if s3 else ' '), content_style)
            ws1.write(row, 3, (myclass if s4 else ' '), content_style)
            ws1.write(row, 4, (mysex if s5 else ' '), content_style)
            ws1.write(row, 5, (s6 if s6 else ' '), content_style)

        output = BytesIO()
        wb.save(output)
        myxlsfile = base64.standard_b64encode(output.getvalue())

        # 匯出Excel檔的放置位置
        myrec = self.env['openacademy.excel_download']
        myrec.create({'xls_file': myxlsfile, 'xls_file_name': myxlsfilename, 'run_desc': myrundesc})

        # 匯出Excel檔之後跳轉到的畫面
        myviewid = self.env.ref('openacademy.openacademy_excel_download_tree')

        return {
            'view_name': 'openacademyexcelwizard',
            'name': ('Openacademy Student Records Export'),
            'type': 'ir.actions.act_window',
            'res_model': 'openacademy.excel_download',
            'view_id': myviewid.id,
            'flags': {'action_buttons': False},
            'view_type': 'form',
            'view_mode': 'tree',
            'target': 'main'}

l Step 2在wizards資料夾內修改__init__.py：
# 原程式碼增加以下程式碼
from . import openacademy_student_export_wizard

l   Step 3在wizards資料夾內創建openacademy_student_export_wizard.xml：
<?xml version="1.0" encoding="utf-8"?>
<odoo>
    <data>
        <record id="openacademy_export_form_wizard" model="ir.ui.view">
            <field name="name">openacademy.export.form.wizard</field>
            <field name="model">openacademy.student_export_wizard</field>
            <field name="arch" type="xml">
                <form string="">
                    <sheet>
                        <h1>學生成員名單匯出</h1>
                        <group>
                            <field name="student_name"/>
                            <field name="student_class"/>
                            <field name="student_fm"/>
                        </group>
                    </sheet>
                    <footer>
                        <button type="object" name="student_export_list" string="匯出" class="oe_highlight"/>
                        <button special="cancel" string="離開"/>
                    </footer>
                </form>
            </field>
        </record>
        <act_window id="openacademy_student_export_view_action"
            name="學生成員名單匯出精靈"
            res_model="openacademy.student_export_wizard"
            src_model=""
            view_mode="form"
            target="new"
            multi="False"/>
    </data>
</odoo>

l Step 4在openacademy資料夾內修改__manifest__.py：
'data': [
'wizards/openacademy_student_export_wizard.xml',
],

l   Step 5在models資料夾內創建openacademy_download_file.py：
# -*- coding: utf-8 -*-
# Author: Peter Wu

from odoo import models, fields, api
from odoo.exceptions import UserError

class openacademydownloadfile(models.Model):
    _name = 'openacademy.excel_download'
    _order = 'create_date desc'

    xls_file = fields.Binary(string = '下載')
    xls_file_name = fields.Char(string = '檔案名稱')
    run_desc = fields.Char(string = '匯出說明描述')

l Step 6在models資料夾內修改__init__.py：
# 原程式碼增加以下程式碼
from . import openacademy_download_file

l   Step 7在views資料夾內創建openacademy_download_file.xml：
<?xml version="1.0" encoding="utf-8"?>
<odoo>
    <data>
        <record id="openacademy_excel_download_tree" model="ir.ui.view">
           <field name="name">openacademy.excel.download.tree</field>
           <field name="model">openacademy.excel_download</field>
           <field name="arch" type="xml">
               <tree import="false" create="false">
                   <field name="create_uid" string="匯出所有人"/>
                   <field name="create_date" string="匯出日期"/>
                   <field name="xls_file" widget="binary" string="下載" filename="xls_file_name"/>
                    <field name="xls_file_name" string="檔案名稱"/>
                    <field name="run_desc" string="匯出說明描述"/>
               </tree>
           </field>
       </record>
        <record id="openacademy_excel_download_form" model="ir.ui.view">
            <field name="name">openacademy.excel.download.form</field>
            <field name="model">openacademy.excel_download</field>
            <field name="arch" type="xml">
                <form>
                    <sheet>
                        <group>
                            <field name="create_uid" string="匯出所有人" attrs="{'readonly':1}"/>
                            <field name="create_date" string="匯出日期" attrs="{'readonly':1}"/>
                            <field name="xls_file" widget="binary" string="下載" filename="xls_file_name"/>
                            <field name="xls_file_name" string="檔案名稱" attrs="{'readonly':1}"/>
                            <field name="run_desc" string="匯出說明描述" attrs="{'readonly':1}"/>
                        </group>
                    </sheet>
                </form>
            </field>
        </record>
        <record id="openacademy_excel_download_action" model="ir.actions.act_window">
            <field name="name">學生成員名單匯出檔</field>
            <field name="type">ir.actions.act_window</field>
            <field name="res_model">openacademy.excel_download</field>
            <field name="view_mode">tree,form</field>
            <field name="help" type="html">
                <p class="oe_view_nocontent_create"></p>
                <p></p>
            </field>
        </record>
    </data>
</odoo>

l Step 8在openacademy資料夾內修改__manifest__.py：
'data': [
'views/openacademy_download_file.xml',
],

l   Step 9在views資料夾內修改openacademy_menu.xml：

<menuitem id="menu_openacademy_export_wizard_view"
    name="" parent="menu_openacademy_search_categ"
    action="openacademy_student_export_view_action"
    sequence="20"/>
<menuitem id="menu_openacademy_excel_export_view"
    name="" parent="menu_openacademy_search_categ"
    action="openacademy_excel_download_action"
    sequence="100"/>

l Step 10在security資料夾內修改ir.model.access.csv：
id,name,model_id:id,group_id:id,perm_read,perm_write,perm_create,perm_unlink
"access_student_download_user","access.student.download.user","openacademy.model_openacademy_excel_download","base.group_user","True","True","True","True"

匯入Excel檔(xls、xlsx)

l   Step 1在wizards資料夾內創建openacademy_score_import_wizard.py：
# -*- coding: utf-8 -*-
# Author: Peter Wu

from odoo import models, fields, api
from odoo.exceptions import UserError
import xlrd, base64

# 定義儲存格種類
XL_CELL_EMPTY = 0
XL_CELL_TEXT = 1
XL_CELL_NUMBER = 2
XL_CELL_DATE = 3
XL_CELL_BOOLEAN = 4
XL_CELL_ERROR = 5
XL_CELL_BLANK = 6

class openacademyscoreimportwizard(models.TransientModel):
    _name = 'openacademy.score_import_wizard'

    excel_file = fields.Binary(string = 'Upload Excel File')

    def score_import_list(self):
        if not self.excel_file:
            raise UserError('Error! Not Correct Excel File')
        xls = xlrd.open_workbook(file_contents = base64.decodestring(self.excel_file))
        sheet = xls.sheet_by_index(0)

        # 讀取Excel橫列的範圍
        nstartrow = 3
        nendrow = sheet.nrows

        # 搜尋的標的
        myrec = self.env['openacademy.score']

        for row in range(nstartrow - 1, nendrow):
            # 學年
            cell = sheet.cell(row, 0)
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                myyear = str(int(cell.value)).strip()
            else:
                myyear = ' '

            # 學生姓名
            cell = sheet.cell(row, 1)
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
               myname = cell.value
                myrec_name = self.env['openacademy.student'].search([('student_name', '=', myname)])
                if myrec_name:
                    myid = myrec_name.id
                else:
                    myid = 0

            # 國文分數
            cell = sheet.cell(row, 2)
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                mychinese = cell.value
            else:
                mychinese = 0

            # 數學分數
            cell = sheet.cell(row, 3)
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                mymath = cell.value
            else:
                mymath = 0

            # 英文分數
            cell = sheet.cell(row, 4)
            if cell.ctype in (XL_CELL_TEXT, XL_CELL_NUMBER):
                myenglish = cell.value
            else:
                myenglish = 0

            # 學生姓名是Many2one字段，關聯回原始的標的
            if myid > 0:
                mycount = self.env['openacademy.score'].search_count([('score_year', '=', myyear), ('score_student', '=', myid)])
                # 創建學生成績，前提是要有學生資料
                if mycount == 0:
                    myrec.create({'score_year': myyear, 'score_student': myid, 'score_chinese': mychinese, 'score_math': mymath, 'score_english': myenglish})
                # 修改學生成績，覆蓋現有學生成績
                else:
                    myrec = self.env['openacademy.score'].search([('score_year', '=', myyear), ('score_student', '=', myid)])
                  myrec.write({'score_chinese': mychinese, 'score_math': mymath, 'score_english': myenglish})

        # 匯入Excel檔之後跳轉到的畫面
        myviewid = self.env.ref('openacademy.view_openacademy_score_tree')

        return {'view_name': 'openacademyscoreimportwizard',
            'name': ('Openacademy Score Import'),
            'views': [[False, 'tree']],
            'res_model': 'openacademy.score',
            'context': self._context,
            'type': 'ir.actions.act_window',
            'target': 'main',
            'view_id': myviewid.id,
            'flags': {'action_buttons': True},
            'view_mode': 'tree',
            'view_type': 'form'}

l Step 2在wizards資料夾內修改__init__.py：
# 原程式碼增加以下程式碼
from . import openacademy_score_import_wizard

l Step 3在static資料夾放置匯入Excel檔的樣板：
/static/file/import_score.xlsx
/static/img/excel_file_sample.png

l   Step 4在wizards資料夾內創建openacademy_score_import_wizard.xml：
<?xml version="1.0" encoding="utf-8"?>
<odoo>
    <data>
        <record id="openacademy_score_import_form_wizard" model="ir.ui.view">
          <field name="name">openacademy.score.import.form.wizard</field>
            <field name="model">openacademy.score_import_wizard</field>
            <field name="arch" type="xml">
                <form string="Excel File Import">
                    <sheet>
                        <h1>學生成績匯入精靈</h1>
                        <group>
                            <field name="excel_file"/>
                        </group>
                        <notebook>
                            <page string="學生成績匯入Excel範本">
                                <group>
                                    <br/>
                                    <img src="/openacademy/static/img/excel_file_sample.png"/>
                                    <br/>
                                    <a href="/openacademy/static/file/import_score.xlsx">下載樣板格式檔案</a>
                                </group>
                            </page>
                        </notebook>
                    </sheet>
                    <footer>
                        <button type="object" name="score_import_list" string="成績匯入" class="oe_highlight"/>
                        <button special="cancel" string="放棄離開"/>
                    </footer>
                </form>
            </field>
        </record>
        <record id="openacademy_score_import_action" model="ir.actions.act_window">
            <field name="name">學生成績Excel匯入</field>
            <field name="type">ir.actions.act_window</field>
            <field name="res_model">openacademy.score_import_wizard</field>
            <field name="view_type">form</field>
            <field name="view_mode">form</field>
            <field name="view_id" ref="openacademy_score_import_form_wizard"/>
            <field name="target">new</field>
        </record>
    </data>
</odoo>

l Step 5在openacademy資料夾內修改__manifest__.py：
'data': [
'wizards/openacademy_score_import_wizard.xml',
],

l   Step 6在views資料夾內修改openacademy_menu.xml：

<menuitem id="menu_openacademy_score_import_view"
    name="" parent="menu_openacademy_score_categ"
    action="openacademy_score_import_action"
    sequence="20"/>

Timmy's Column

2020年2月27日星期四

Odoo教育訓練學習筆記 (5)

沒有留言:

張貼留言

2020年2月27日 星期四

Odoo教育訓練學習筆記 (5)

沒有留言:

張貼留言

2020年2月27日星期四