odoo自定义报表

py源码:

from odoo.report import report_sxw
from odoo import models
from odoo import models, api, fields, _


class stock_picking_report(models.AbstractModel):
    _name = 'report.stock_report.stock_picking_report'

    @api.model
    def render_html(self, docids, data=None):
        report_obj = self.env['report']
        report = report_obj._get_report_from_name('stock_report.stock_picking_report')
        spk = self.env['stock.picking'].search([('id','=',docids)])
        docargs = {
            'doc_ids': docids,
            'doc_model': report.model,
            'docs': self,
            'spk': spk,
            'get_all': self.get_all,
            'print_data': self.print_data,
            'get_qty_done_sum': self.get_qty_done_sum,
        }
        return report_obj.render('stock_report.stock_picking_report', docargs)

    def get_all(self, para):
        self._cr.execute("""\
                SELECT pp.id as product_id,pt.id as tmpl_id,pu.name as uom_name,spol.lot_uom_ratio,
                spol.lot_uom_qty,spol.qty,t.often_uom,concat(spl.ref,'-',spl.name) as lot,sl.name origin_name,
                loc.loc_id dest_location FROM stock_picking sp
                    LEFT JOIN stock_pack_operation spo ON spo.picking_id=sp.id
                    LEFT JOIN stock_pack_operation_lot spol ON spol.operation_id=spo.id
                    LEFT JOIN product_uom pu ON pu.id = spol.lot_uom
                    LEFT JOIN stock_production_lot spl on spl.id = spol.lot_id
                    LEFT JOIN product_product pp on pp.id=spo.product_id
                    LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
                    LEFT JOIN stock_location sl on sl.id = spo.location_id
                    LEFT JOIN
                    (select sl.id as loc_id,sl.name,spol.id FROM stock_picking sp
                            LEFT JOIN stock_pack_operation spo ON spo.picking_id=sp.id
                            LEFT JOIN stock_pack_operation_lot spol ON spol.operation_id=spo.id
                            LEFT JOIN stock_location sl on sl.id = spo.location_dest_id
                            where sp.id =%s) loc on loc.id=spol.id
                        LEFT JOIN
                        (SELECT pu.name AS often_uom,pp.id FROM product_product pp
                            LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
                            LEFT JOIN product_uom pu ON pu.id=pt.uom_id)t ON t.id=spo.product_id
                        where sp.id =%s order by pt.id
                """ % (para,para))
        return self._cr.dictfetchall()

    def print_data(self,para):
        dict = {}
        list = []
        flagA='1'
        flagB='1'
        sum=0
        i=0
        for pd in self.get_all(para):
            i=i+1
            flagA = pd['tmpl_id']
            if flagA != flagB:
                if flagB != '1':
                    dict['line'] = ''
                    dict['product_id'] = '小计'
                    dict['tmpl_id'] = ''
                    dict['uom_name'] = ''
                    dict['lot_uom_ratio'] = ''
                    dict['lot_uom_qty'] = ''
                    dict['often_uom'] = ''
                    dict['lot'] = ''
                    dict['origin_name'] = ''
                    dict['dest_location'] = ''
                    dict['qty'] = sum
                    list.append(dict.copy())
                    sum = 0
                flagB = flagA
            sum = sum + pd['qty']
            dict['line'] = i
            prd = self.env['product.product'].search([('id', '=', pd['product_id'])])
            dict['product_id'] = prd.display_name
            dict['tmpl_id'] = pd['tmpl_id']
            dict['uom_name'] = pd['uom_name']
            dict['lot_uom_ratio'] = pd['lot_uom_ratio']
            dict['lot_uom_qty'] = pd['lot_uom_qty']
            dict['qty'] = pd['qty']
            dict['often_uom'] = pd['often_uom']
            dict['lot'] = pd['lot']
            dict['origin_name'] = pd['origin_name']
            sl = self.env['stock.location'].search([('id', '=', pd['dest_location'])])
            dict['dest_location'] = sl.display_name
            list.append(dict.copy())
        dict['product_id'] = '小计'
        dict['line'] = ''
        dict['tmpl_id'] = ''
        dict['uom_name'] = ''
        dict['lot_uom_ratio'] = ''
        dict['lot_uom_qty'] = ''
        dict['often_uom'] = ''
        dict['lot'] = ''
        dict['origin_name'] = ''
        dict['dest_location'] = ''
        dict['qty'] = sum
        list.append(dict.copy())
        return list

    def get_qty_done_sum(self, para):

        self._cr.execute("""\
                select sum(qty_done) from stock_picking sp
                left join stock_pack_operation spo on sp.id=spo.picking_id where sp.name=\'%s\'
                """ % para)
        return self._cr.fetchall()

 

代码上方注释部分为原生odoo pdf开发文件,下方的def render_html(XXX)为自定义模型.

return self._cr.dictfetchall() #返回字典型结果集

return self._cr.fetchall() #返回列表型结果集

Xml文件:

<?xml version="1.0" encoding="utf-8"?>
<odoo>
    <template id="stock_picking_report">
        <t t-call="report.html_container">
            <div t-if="spk.state=='done'" class="page">
                <t>
                    <img src="/stock_report/static/src/jpg/tuntex.jpg"
                         width="154" height="45"/>
                </t>
                <div class="row">
                    <div class="col-xs-12 text-center">
                        <strong>
                            <h4 t-esc="spk.picking_type_id.name">单</h4>
                        </strong>
                    </div>
                </div>
                <div class="row">
                    <div class="col-xs-4">
                        <strong>合作伙伴:</strong>
                        <p t-if="spk.picking_type_id.name=='采购入库单'" t-esc="spk.partner_id.name"/>
                    </div>
                    <div class="col-xs-4">
                        <strong>开单单位：</strong>
                        <p t-esc="spk.create_uid.department_id.name"/>
                    </div>
                    <div class="col-xs-4">
                        <strong>单据号：</strong>
                        <p t-esc="spk.name"/>
                    </div>
                    <div class="col-xs-4">
                        <strong>单据日期：</strong>
                        <p t-esc="spk.date"/>
                    </div>
                </div>
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>行号</th>
                            <th>产品名称</th>
                            <th>辅助单位</th>
                            <th>换算率</th>
                            <th>辅助单位数量</th>
                            <th>数量</th>
                            <th>主计量单位</th>
                            <th>批次/序列号</th>
                            <th>来源位置</th>
                            <th>目的位置</th>
                            <th>备注</th>
                        </tr>
                    </thead>
                    <!--<t t-set="i" t-value="1"/>-->
                    <tbody>
                        <tr style="page-break-inside : avoid"
                            t-foreach="print_data(doc_ids[0])" t-as="lot">
                            <!--<td>
                                <span t-esc="i"/>
                                <t t-set="i" t-value="i+1"/>
                            </td>-->
                            <td>
                                <span t-esc="lot['line']"/>
                            </td>
                            <td>
                                <span t-esc="lot['product_id']"/>
                            </td>
                            <td>
                                <span t-esc="lot['uom_name']"/>
                            </td>
                            <td>
                                <span t-esc="lot['lot_uom_ratio']"/>
                            </td>
                            <td>
                                <span t-esc="lot['lot_uom_qty']"/>
                            </td>
                            <td>
                                <span t-esc="lot['qty']"/>
                            </td>
                            <td>
                                <span t-esc="lot['often_uom']"/>
                            </td>
                            <td>
                                <span t-esc="lot['lot']"/>
                            </td>
                            <td>
                                <span t-esc="lot['origin_name']"/>
                            </td>
                            <td>
                                <span t-esc="lot['dest_location']"/>
                            </td>
                            <td>
                                <span/>
                            </td>
                        </tr>
                        <tr>
                            <td colspan="2" class="text-center">合计</td>
                            <td colspan="2"/>
                            <td colspan="1"/>
                            <td class="text-right">
                                <span t-esc="get_qty_done_sum(spk.name)[0][0]"/>
                            </td>
                            <td colspan="5"/>
                        </tr>
                    </tbody>
                </table>
                <div class="row mt32 mb32">
                    <div class="col-xs-12">
                        <div>
                            <font>备注：</font>
                            <span t-esc="spk.note"/>
                        </div>
                    </div>
                    <div class="col-xs-4">
                        <div>
                            <font>核决:</font>
                        </div>
                    </div>
                    <div class="col-xs-4">
                        <div>
                            <font>审核：</font>
                        </div>
                    </div>
                    <div class="col-xs-4">
                        <div>
                            <font>经办：</font>
                            <span t-esc="spk.create_uid.name"/>
                        </div>
                    </div>
                </div>
            </div>
            <div t-if="spk.state!='done'" class="page">
                <h3 class="text-center">请打印已完成单据</h3>
            </div>
        </t>
    </template>
</odoo>