Esempio n. 1
0
 def init(self):
     drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""
                 create or replace view report_pos_commission as (
                     select * from pos_agent_commission
                 )
             """)
Esempio n. 2
0
 def init(self):
     drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""
         create or replace view payslip_report as (
             select
                 min(l.id) as id,
                 l.name,
                 p.struct_id,
                 p.state,
                 p.date_from,
                 p.date_to,
                 p.number,
                 p.company_id,
                 p.paid,
                 l.category_id,
                 l.employee_id,
                 sum(l.total) as total,
                 to_char(p.date_from, 'YYYY') as year,
                 to_char(p.date_from, 'MM') as month,
                 to_char(p.date_from, 'YYYY-MM-DD') as day,
                 to_char(p.date_to, 'YYYY') as to_year,
                 to_char(p.date_to, 'MM') as to_month,
                 to_char(p.date_to, 'YYYY-MM-DD') as to_day,
                 1 AS nbr
             from
                 hr_payslip as p
                 left join hr_payslip_line as l on (p.id=l.slip_id)
             where
                 l.employee_id IS NOT NULL
             group by
                 p.number,l.name,p.date_from,p.date_to,p.state,p.company_id,p.paid,
                 l.employee_id,p.struct_id,l.category_id
         )
     """)
Esempio n. 3
0
 def init(self):
     drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""
         create or replace view payment_advice_report as (
             select
                 min(l.id) as id,
                 sum(l.bysal) as bysal,
                 p.name,
                 p.state,
                 p.date,
                 p.number,
                 p.company_id,
                 p.bank_id,
                 p.chaque_nos as cheque_nos,
                 p.neft,
                 l.employee_id,
                 l.ifsc_code,
                 l.name as employee_bank_no,
                 to_char(p.date, 'YYYY') as year,
                 to_char(p.date, 'MM') as month,
                 to_char(p.date, 'YYYY-MM-DD') as day,
                 1 as nbr
             from
                 hr_payroll_advice as p
                 left join hr_payroll_advice_line as l on (p.id=l.advice_id)
             where
                 l.employee_id IS NOT NULL
             group by
                 p.number,p.name,p.date,p.state,p.company_id,p.bank_id,p.chaque_nos,p.neft,
                 l.employee_id,l.advice_id,l.bysal,l.ifsc_code, l.name
         )
     """)
Esempio n. 4
0
 def init(self):  #v10
     drop_view_if_exists(self._cr, 'pos_combo_pack_report')
     self.env.cr.execute(
         """create or replace view pos_combo_pack_report as (
         SELECT p.id as id,
           p.id as product_id,
           pt.name as name,
           po.date_order as date,
           po.state as state,
           p.pos_type as pos_type,
           sum(pol.qty) as qty,
           sum(pol.qty * pol.price_unit * (1 - pol.discount / 100.0)) as sub_total
         FROM
           product_product p,
           product_template pt,
           pos_order po,
           pos_order_line pol
         WHERE
           p.product_tmpl_id = pt.id AND
           pol.product_id = p.id AND
           po.id = pol.order_id AND
           p.pos_type != 'none'
         GROUP BY p.id,
           po.date_order,
           po.state,
           p.pos_type,
           pt.id,
           pt.name
     )""")
 def init(self):
     cr = self._cr
     drop_view_if_exists(cr, 'report_timesheet_line')
     cr.execute("""
         create or replace view report_timesheet_line as (
             select
                 min(l.id) as id,
                 l.date as date,
                 to_char(l.date,'YYYY') as name,
                 to_char(l.date,'MM') as month,
                 l.user_id,
                 to_char(l.date, 'YYYY-MM-DD') as day,
                 l.invoice_id,
                 l.product_id,
                 l.account_id,
                 l.general_account_id,
                 sum(l.unit_amount) as quantity,
                 sum(l.amount) as cost
             from
                 account_analytic_line l
             where
                 l.user_id is not null
             group by
                 l.date,
                 l.user_id,
                 l.product_id,
                 l.account_id,
                 l.general_account_id,
                 l.invoice_id
         )
     """)
 def init(self):
     drop_view_if_exists(self._cr, 'line_bus_employee_shedule4bus')
     self._cr.execute("""
         CREATE OR REPLACE VIEW line_bus_employee_shedule4bus AS (
             select
                  bus_code as id, 
                  company_id,
                  bus_group_id,
                  line_id,
                  bus_model,
                  bus_model_name,
                  bus_code,
                  entry_state
             
              from line_bus_employee_shedule
             
             
              group by 
                  company_id,
                  line_id,
                  bus_model,
                  bus_model_name,
                  bus_code,
                  bus_group_id,
                  entry_state        
         )""")                
Esempio n. 7
0
 def init(self):
     drop_view_if_exists(self._cr, 'account_invoice_line_sindescuento')
     self._cr.execute("""
         create or replace view account_invoice_line_sindescuento as (
          SELECT account_invoice_line.id, account_invoice_line.origin, 
             account_invoice_line.create_date, account_invoice_line.price_unit, 
             account_invoice_line.price_subtotal, account_invoice_line.write_uid, 
             account_invoice_line.currency_id, account_invoice_line.uom_id, 
             account_invoice_line.partner_id, account_invoice_line.create_uid, 
             
             account_invoice_line.sequence, account_invoice_line.company_id, 
             account_invoice_line.account_analytic_id, account_invoice_line.account_id, 
             account_invoice_line.discount, account_invoice_line.write_date, 
             account_invoice_line.price_subtotal_signed, account_invoice_line.name, 
             account_invoice_line.product_id, account_invoice_line.invoice_id, 
             account_invoice_line.quantity, 
             account_invoice_line.layout_category_sequence, 
             account_invoice_line.layout_category_id, 
             account_invoice_line.purchase_line_id, 
             account_invoice_line.asset_category_id, account_invoice_line.asset_end_date, 
             account_invoice_line.asset_start_date, account_invoice_line.asset_mrr, 
             account_invoice_line.serie
            FROM account_invoice_line
           WHERE account_invoice_line.product_id <> 19880
         )""")
Esempio n. 8
0
 def init(self):
     drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""
         create or replace view payment_advice_report as (
             select
                 min(l.id) as id,
                 sum(l.bysal) as bysal,
                 p.name,
                 p.state,
                 p.date,
                 p.number,
                 p.company_id,
                 p.bank_id,
                 p.chaque_nos as cheque_nos,
                 p.neft,
                 l.employee_id,
                 l.ifsc_code,
                 l.name as employee_bank_no,
                 to_char(p.date, 'YYYY') as year,
                 to_char(p.date, 'MM') as month,
                 to_char(p.date, 'YYYY-MM-DD') as day,
                 1 as nbr
             from
                 hr_payroll_advice as p
                 left join hr_payroll_advice_line as l on (p.id=l.advice_id)
             where
                 l.employee_id IS NOT NULL
             group by
                 p.number,p.name,p.date,p.state,p.company_id,p.bank_id,p.chaque_nos,p.neft,
                 l.employee_id,l.advice_id,l.bysal,l.ifsc_code, l.name
         )
     """)
Esempio n. 9
0
    def write(self, vals):
        """
        If the description change, we must update the action
        """

        if vals.get('sql_name') or vals.get('sql_view'):
            sql_name = vals.get('sql_name', self[0].browse(cr, uid,
                                                           ids[0]).sql_name)
            sql_view = vals.get('sql_view',
                                self.browse(cr, uid, ids[0]).sql_view)
            drop_view_if_exists(cr, sql_name)
            sql_query = 'CREATE OR REPLACE VIEW %s AS\n%s' % (sql_name,
                                                              sql_view)
            self.env.cr.execute(sql_query, (ids, ))

        res = super(jasper_document, self).write(vals)

        if 'enabled' in vals:
            if vals['enabled']:
                for record in self:
                    _logger.debug('Request to create the report action: %s',
                                  record)
                    record.make_action()
            else:
                for record in self:
                    record.unlink_values()
        return res
    def init(self):
        cr = self._cr
        drop_view_if_exists(cr, 'report_timesheet_invoice')
        cr.execute("""
            create or replace view report_timesheet_invoice as (
                select 
					min(l.id) as id,
                    l.user_id as user_id,
                    l.account_id as account_id,
                    l.user_id as manager_id,
                    sum(l.unit_amount) as quantity,
                    sum(l.unit_amount * t.list_price) as amount_invoice 
                from account_analytic_line l
                        left join hr_timesheet_invoice_factor f on (l.to_invoice=f.id)
                        left join account_analytic_account a on (l.account_id=a.id)
                        left join product_product p on (l.to_invoice=f.id)
                        left join product_template t on (l.to_invoice=f.id)
                where
                        l.to_invoice is not null and
                        l.invoice_id is null
                group by
                        l.user_id,
                        l.account_id,
                        l.user_id
            )
        """)
Esempio n. 11
0
 def init(self):
     drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""
         create or replace view payslip_report as (
             select
                 min(l.id) as id,
                 l.name,
                 p.struct_id,
                 p.state,
                 p.date_from,
                 p.date_to,
                 p.number,
                 p.company_id,
                 p.paid,
                 l.category_id,
                 l.employee_id,
                 sum(l.total) as total,
                 to_char(p.date_from, 'YYYY') as year,
                 to_char(p.date_from, 'MM') as month,
                 to_char(p.date_from, 'YYYY-MM-DD') as day,
                 to_char(p.date_to, 'YYYY') as to_year,
                 to_char(p.date_to, 'MM') as to_month,
                 to_char(p.date_to, 'YYYY-MM-DD') as to_day,
                 1 AS nbr
             from
                 hr_payslip as p
                 left join hr_payslip_line as l on (p.id=l.slip_id)
             where
                 l.employee_id IS NOT NULL
             group by
                 p.number,l.name,p.date_from,p.date_to,p.state,p.company_id,p.paid,
                 l.employee_id,p.struct_id,l.category_id
         )
     """)
Esempio n. 12
0
 def init(self):
     drop_view_if_exists(self._cr, 'sli_historical_employee')
     self._cr.execute("""
         create or replace view  sli_historical_employee as(
         select he.id as id,name_related as nombre, ah.admissiondate as fechaingreso,ah.dischargedate as fechabaja,
         ah.reason as motivo, ah.settlement as finiquito, ah.retracted as recontratado
         from hr_employee he
         left join applicant_historical ah on ah.employee_id = he.id
     )""")
Esempio n. 13
0
 def init(self):
     drop_view_if_exists(self._cr, 'report_mrp_production')
     self._cr.execute("""
             create or replace view report_mrp_production as (
                 SELECT
                     mrp_p."id" AS ID,
                     mrp_production."id" AS production_id,
                     pt."id" AS product_id,
                     mrp_wo."name" AS mrp_workorder_name,
                     wc."id" AS workcenter_id,
                     mrp_p.qty AS qty,
                     mrp_p.empleado_id AS employee_id,
                     pt2."id" AS service_id,
                     mrp_p.price AS price,
                     mrp_p.monto AS monto,
                     mrp_p."id" AS pago_id,
                     merma."name" AS merma,
                     mrp_p."fecha_pago" AS fecha_pago,
                     mrp_p.periodo_id AS periodo_id,
                     stock_move.product_id AS base,
                     mbl.numero_pasadas AS pasadas,
                     mrp_p.qty / (
                         mrp_production.product_qty / stock_move.product_uom_qty
                     ) AS total_bases,
                     pagoayudante.empleado_id AS ayudante
                 FROM
                     mrp_pago mrp_p
                 INNER JOIN product_template pt2 ON pt2."id" = mrp_p.servicio_id
                 LEFT JOIN mrp_workorder mrp_wo ON mrp_wo."id" = mrp_p.workorder_id
                 LEFT JOIN mrp_production ON mrp_production."id" = mrp_wo.production_id
                 LEFT JOIN mrp_bom ON mrp_bom."id" = mrp_production.bom_id
                 LEFT JOIN product_template pt ON pt."id" = mrp_production.product_id
                 LEFT JOIN mrp_workcenter wc ON wc."id" = mrp_wo.workcenter_id
                 LEFT JOIN mrp_merma merma ON merma."id" = mrp_p.merma_id
                 LEFT JOIN stock_move ON stock_move.raw_material_production_id = mrp_production."id"
                 AND stock_move.product_id IN (
                     SELECT
                         product_move."id"
                     FROM
                         product_product product_move
                     INNER JOIN product_template template_move ON template_move."id" = product_move.product_tmpl_id
                     WHERE
                         template_move.bases_x_carro IS NOT NULL
                 )
                 LEFT JOIN mrp_bom_line mbl ON mbl.bom_service_id = mrp_bom."id"
                 AND mbl.operation_id = mrp_wo.operation_id
                 AND mbl.product_id = mrp_p.servicio_id
                 LEFT JOIN mrp_pago pagoayudante ON pagoayudante.workorder_id = mrp_p.workorder_id
                 AND pagoayudante.servicio_id != mrp_p.servicio_id
                 AND pagoayudante.price > 0
                 AND mrp_p.price > 0
                 WHERE
                     mrp_p.qty IS NOT NULL
                 ORDER BY mrp_p."id"
             )""")
Esempio n. 14
0
    def _drop_relation_if_exists(self, cursor):
        """ Drops middle relation, both if it is a table or a query
        """

        if self._relation_is_actually_a_table(cursor):
            sql = 'DROP TABLE IF EXISTS {};'
            sql = sql.format(self.relation)
            cursor.execute(sql)
        else:
            self._drop_rules(cursor)
            sqltools.drop_view_if_exists(cursor, self.relation)
Esempio n. 15
0
 def init(self):
     cr = self._cr
     drop_view_if_exists(cr, 'stock_card_report')
     cr.execute("""
         CREATE OR REPLACE VIEW stock_card_report AS (
             SELECT
                 sm.id AS id,
                 sm.branch_id AS branch_id,
                 sm.date AS date,
                 sm.product_id AS product_id,
                 CASE WHEN spt.code = 'outgoing'
                     THEN SUM(sm.product_uom_qty)
                 END AS qty_out,
                 CASE WHEN spt.code = 'incoming'
                     THEN SUM(sm.product_uom_qty)
                 END AS qty_in,
                 CASE WHEN spt.code = 'outgoing'
                     THEN SUM(sm.product_uom_qty * pt.list_price)
                 END AS total_sale,
                 CASE WHEN spt.code = 'incoming'
                     THEN SUM(sm.product_uom_qty * ir.value_float)
                 END AS total_cost,
                 CASE WHEN spt.code = 'outgoing'
                     THEN 0 - SUM(sm.product_uom_qty)
                     ELSE SUM(sm.product_uom_qty)
                 END AS qty_left,
                 CASE WHEN spt.code = 'outgoing'
                     THEN SUM(sm.product_uom_qty * pt.list_price)
                     ELSE 0 - SUM(sm.product_uom_qty * ir.value_float)
                 END AS gain_loss
             FROM
                 stock_move sm
                 JOIN stock_picking sp ON (sm.picking_id = sp.id)
                 JOIN stock_picking_type spt ON (sm.picking_type_id = spt.id)
                 JOIN product_product pp ON (sm.product_id = pp.id)
                 LEFT JOIN product_template pt ON (pp.product_tmpl_id = pt.id)
                 JOIN ir_property ir ON (split_part(res_id, ',', 2)::integer = pp.id)
             WHERE
                 sm.state = 'done' AND
                 spt.code IN ('outgoing', 'incoming') AND
                 ir.name = 'standard_price'
             GROUP BY
                 sm.id,
                 sm.branch_id,
                 sm.date,
                 sm.product_id,
                 spt.code,
                 sm.product_uom_qty,
                 pt.list_price,
                 ir.value_float
         )
     """)
Esempio n. 16
0
    def create(self, vals):
        """
        Dynamicaly declare the wizard for this document
        """

        doc_id = super(jasper_document, self).create(vals)
        doc_id.make_action()

        # Check if view exists and create it in the database
        if vals.get('sql_name') and vals.get('sql_view'):
            drop_view_if_exists(cr, vals.get('sql_name'))
            sql_query = 'CREATE OR REPLACE VIEW %s AS\n%s' % (vals['sql_name'],
                                                              vals['sql_view'])
            self.env.cr.execute(sql_query)
        return doc_id
    def init(self):
        drop_view_if_exists(self.env.cr, self._table)
        self.env.cr.execute("""
            create or replace view report_intrastat as (
                select
                    to_char(inv.date_invoice, 'YYYY') as name,
                    to_char(inv.date_invoice, 'MM') as month,
                    min(inv_line.id) as id,
                    intrastat.id as intrastat_id,
                    upper(inv_country.code) as code,
                    sum(case when inv_line.price_unit is not null
                            then inv_line.price_unit * (1.0 - coalesce(inv_line.discount, 0.0) / 100.0) * inv_line.quantity
                            else 0
                        end) as value,
                    sum(
                        case when uom.category_id != puom.category_id then (coalesce(nullif(pp.weight, 0), pt.weight) * inv_line.quantity)
                        else (coalesce(nullif(pp.weight, 0), pt.weight) * inv_line.quantity * uom.factor) end
                    ) as weight,
                    sum(
                        case when uom.category_id != puom.category_id then inv_line.quantity
                        else (inv_line.quantity * uom.factor) end
                    ) as supply_units,

                    inv.currency_id as currency_id,
                    inv.number as ref,
                    case when inv.type in ('out_invoice','in_refund')
                        then 'export'
                        else 'import'
                        end as type,
                    inv.company_id as company_id
                from
                    account_invoice inv
                    left join account_invoice_line inv_line on inv_line.invoice_id=inv.id
                    left join (product_template pt
                        left join product_product pp on (pp.product_tmpl_id = pt.id))
                    on (inv_line.product_id = pp.id)
                    left join product_uom uom on uom.id=inv_line.uom_id
                    left join product_uom puom on puom.id = pt.uom_id
                    left join report_intrastat_code intrastat on pt.intrastat_id = intrastat.id
                    left join (res_partner inv_address
                        left join res_country inv_country on (inv_country.id = inv_address.country_id))
                    on (inv_address.id = coalesce(inv.partner_shipping_id, inv.partner_id))
                where
                    inv.state in ('open','paid')
                    and inv_line.product_id is not null
                    and inv_country.intrastat=true
                group by to_char(inv.date_invoice, 'YYYY'), to_char(inv.date_invoice, 'MM'),intrastat.id,inv.type,pt.intrastat_id, inv_country.code,inv.number,  inv.currency_id, inv.company_id
            )""")
Esempio n. 18
0
    def init(self):
        drop_view_if_exists(self.env.cr, self._table)
        self.env.cr.execute("""
            create or replace view report_intrastat as (
                select
                    to_char(inv.date_invoice, 'YYYY') as name,
                    to_char(inv.date_invoice, 'MM') as month,
                    min(inv_line.id) as id,
                    intrastat.id as intrastat_id,
                    upper(inv_country.code) as code,
                    sum(case when inv_line.price_unit is not null
                            then inv_line.price_unit * inv_line.quantity
                            else 0
                        end) as value,
                    sum(
                        case when uom.category_id != puom.category_id then (pt.weight * inv_line.quantity)
                        else (pt.weight * inv_line.quantity * uom.factor) end
                    ) as weight,
                    sum(
                        case when uom.category_id != puom.category_id then inv_line.quantity
                        else (inv_line.quantity * uom.factor) end
                    ) as supply_units,

                    inv.currency_id as currency_id,
                    inv.number as ref,
                    case when inv.type in ('out_invoice','in_refund')
                        then 'export'
                        else 'import'
                        end as type,
                    inv.company_id as company_id
                from
                    account_invoice inv
                    left join account_invoice_line inv_line on inv_line.invoice_id=inv.id
                    left join (product_template pt
                        left join product_product pp on (pp.product_tmpl_id = pt.id))
                    on (inv_line.product_id = pp.id)
                    left join uom_uom uom on uom.id=inv_line.uom_id
                    left join uom_uom puom on puom.id = pt.uom_id
                    left join report_intrastat_code intrastat on pt.intrastat_id = intrastat.id
                    left join (res_partner inv_address
                        left join res_country inv_country on (inv_country.id = inv_address.country_id))
                    on (inv_address.id = inv.partner_id)
                where
                    inv.state in ('open','paid')
                    and inv_line.product_id is not null
                    and inv_country.intrastat=true
                group by to_char(inv.date_invoice, 'YYYY'), to_char(inv.date_invoice, 'MM'),intrastat.id,inv.type,pt.intrastat_id, inv_country.code,inv.number,  inv.currency_id, inv.company_id
            )""")
Esempio n. 19
0
 def init(self):
     drop_view_if_exists(self._cr, 'hr_timesheet_sheet_sheet_account')
     self._cr.execute("""create view hr_timesheet_sheet_sheet_account as (
         select
             min(l.id) as id,
             l.account_id as name,
             s.id as sheet_id,
             sum(l.unit_amount) as total
         from
             account_analytic_line l
                 LEFT JOIN hr_timesheet_sheet_sheet s
                     ON (s.date_to >= l.date
                         AND s.date_from <= l.date
                         AND s.user_id = l.user_id)
         group by l.account_id, s.id
     )""")
Esempio n. 20
0
 def init(self):
     drop_view_if_exists(self._cr, 'hr_timesheet_sheet_sheet_account')
     self._cr.execute("""create view hr_timesheet_sheet_sheet_account as (
         select
             min(l.id) as id,
             l.account_id as name,
             s.id as sheet_id,
             sum(l.unit_amount) as total
         from
             account_analytic_line l
                 LEFT JOIN hr_timesheet_sheet_sheet s
                     ON (s.date_to >= l.date
                         AND s.date_from <= l.date
                         AND s.user_id = l.user_id)
         group by l.account_id, s.id
     )""")
Esempio n. 21
0
    def init(self):
        drop_view_if_exists(self._cr, 'report_service')
        self._cr.execute("""
            create or replace view report_service as (
                SELECT i.id,
			    i.id AS factura,
			    (
			        CASE
			            WHEN date_part('month'::text, i.date) = 1::double precision THEN 'Enero'::text
			            WHEN date_part('month'::text, i.date) = 2::double precision THEN 'Febrero'::text
			            WHEN date_part('month'::text, i.date) = 3::double precision THEN 'Marzo'::text
			            WHEN date_part('month'::text, i.date) = 4::double precision THEN 'Abril'::text
			            WHEN date_part('month'::text, i.date) = 5::double precision THEN 'Mayo'::text
			            WHEN date_part('month'::text, i.date) = 6::double precision THEN 'Junio'::text
			            WHEN date_part('month'::text, i.date) = 7::double precision THEN 'Julio'::text
			            WHEN date_part('month'::text, i.date) = 8::double precision THEN 'Agosto'::text
			            WHEN date_part('month'::text, i.date) = 9::double precision THEN 'Septiembre'::text
			            WHEN date_part('month'::text, i.date) = 10::double precision THEN 'Octubre'::text
			            WHEN date_part('month'::text, i.date) = 11::double precision THEN 'Noviembre'::text
			            WHEN date_part('month'::text, i.date) = 12::double precision THEN 'Diciembre'::text
			            ELSE NULL::text
			        END || ' '::text) || date_part('year'::text, i.date) AS date,
			    i.origin AS order,pr.name as service,
			    i.partner_id,
			    e.login AS executive,
			    c.name AS currency,
			    ( SELECT sum(price_subtotal) AS sum
			           FROM account_invoice_line 
			             LEFT JOIN product_template pr ON pr.id = product_id
			          WHERE invoice_id = i.id AND pr.type='service' ) AS monto,
			    i.tipo_cambio,
			    (( SELECT sum(price_subtotal) AS sum
			           FROM account_invoice_line 
			             LEFT JOIN product_template pr ON pr.id = product_id
			          WHERE invoice_id = i.id AND pr.type='service'))::double precision * i.tipo_cambio AS montomxn,
			    rp.name as vendedor
			   FROM account_invoice i
			     LEFT JOIN res_currency c ON c.id = i.currency_id
			     left join account_invoice_line il on il.invoice_id=i.id
			     LEFT JOIN product_template pr ON pr.id = il.product_id
			     LEFT JOIN res_partner p ON i.partner_id = p.id
			     LEFT JOIN res_users u ON u.id = i.user_id
			     LEFT JOIN res_users e ON e.id = p.user_id
			     left join res_partner rp on rp.id= u.partner_id
			  WHERE i.state::text = 'paid'::text AND date_part('year'::text, i.date) = date_part('year'::text, now()) and pr.type::text = 'service'::text
			  ORDER BY u.login, i.number
            )""")
Esempio n. 22
0
 def init(self):
     drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""
         create or replace view account_entries_report as (
         select
             l.id as id,
             am.date as date_effective,
             l.date_maturity as date_maturity,
             coalesce(am.ref,am.name) as ref,
             l.name as note,
             l.move_id as move_id,
             am.state as move_state,
             l.full_reconcile_id as reconcile_id,
             l.partner_id as partner_id,
             l.product_id as product_id,
             l.product_uom_id as product_uom_id,
             am.company_id as company_id,
             am.journal_id as journal_id,
             case when aj.type = 'opening' then 'opening' when aj.type = 'closing' then 'shutdown' else 'regular' end as journal_type,
             l.account_id as account_id,
             a.group_id as parent_account_id,
             l.analytic_account_id as analytic_account_id,
             aaa.parent_id as parent_analytic_id,
             a.user_type_id as user_type,
             at.financial_report_id as report_type,
             a.chart_account_id as chart_account,
             1 as nbr,
             l.quantity as product_quantity,
             l.currency_id as currency_id,
             l.amount_currency as amount_currency,
             l.debit as debit,
             l.credit as credit,
             coalesce(l.debit, 0.0) - coalesce(l.credit, 0.0) as balance
             %s
         from
             account_move_line l
             inner join account_account a on (l.account_id = a.id)
             inner join account_move am on (am.id=l.move_id)
             inner join account_journal aj on (aj.id=am.journal_id)
             left join account_account_type at on (a.user_type_id = at.id)
             left join account_analytic_account aaa on (l.analytic_account_id = aaa.id)
             
             %s
             where l.id > 0
             %s
         )
     """%(self._get_select(),self._get_from(),self._get_where()))
Esempio n. 23
0
    def init(self):
        drop_view_if_exists(self._cr, 'assigned_boxes')
        self._cr.execute("""
            create or replace view assigned_boxes as (
                  select sl.id, s.name as pedido, pt.name as producto,sl.product_uom_qty as qty, pu.name unidad,  resp.name as vendedor,  usm.login as repartidor
                    from sale_order_line  sl
                    left join sale_order s on s.id= sl.order_id
                    left join stock_picking sp on sp.origin =s.name
                    left join product_product p on p.id =sl.product_id
                    left join product_template pt on p.product_tmpl_id=pt.id
                    left join product_uom pu on pu.id=sl.product_uom
                    left join res_users us on us.id = s.user_id
                    left join res_partner resp on resp.id= us.partner_id
                    left join res_users usm on usm.id = sp.deliveryman_id
                    group by resp.name,sl.id, s.name, pt.name,sl.product_uom_qty, pu.name,usm.login

            )""")
Esempio n. 24
0
    def init(self):
        drop_view_if_exists(self._cr, 'report_comissions')
        self._cr.execute("""
            create or replace view report_comissions as (
                  SELECT i.id,
			        i.id AS factura,
			        (
			            CASE
			                WHEN date_part('month'::text, i.date) = 1::double precision THEN 'Enero'::text
			                WHEN date_part('month'::text, i.date) = 2::double precision THEN 'Febrero'::text
			                WHEN date_part('month'::text, i.date) = 3::double precision THEN 'Marzo'::text
			                WHEN date_part('month'::text, i.date) = 4::double precision THEN 'Abril'::text
			                WHEN date_part('month'::text, i.date) = 5::double precision THEN 'Mayo'::text
			                WHEN date_part('month'::text, i.date) = 6::double precision THEN 'Junio'::text
			                WHEN date_part('month'::text, i.date) = 7::double precision THEN 'Julio'::text
			                WHEN date_part('month'::text, i.date) = 8::double precision THEN 'Agosto'::text
			                WHEN date_part('month'::text, i.date) = 9::double precision THEN 'Septiembre'::text
			                WHEN date_part('month'::text, i.date) = 10::double precision THEN 'Octubre'::text
			                WHEN date_part('month'::text, i.date) = 11::double precision THEN 'Noviembre'::text
			                WHEN date_part('month'::text, i.date) = 12::double precision THEN 'Diciembre'::text
			                ELSE NULL::text
			            END || ' '::text) || date_part('year'::text, i.date) AS date,
			            i.origin AS order,
			            i.partner_id,
			            e.login AS executive,
			            c.name AS currency,
					    (select sum(price_subtotal) from account_invoice_line il
					        Left join product_template  pr on pr.id = il.product_id 
					        where il.invoice_id=i.id  and pr.categ_id not in ('94') ) as monto
					    ,i.tipo_cambio
					    ,(select sum(price_subtotal) from account_invoice_line il
					        Left join product_template  pr on pr.id = il.product_id 
					        where il.invoice_id=i.id  and pr.categ_id not in ('94') ) *i.tipo_cambio as montomxn,
					     rp.name as vendedor,i.state,i.number as nofactura, i.date_invoice
					    from account_invoice i
					      left join res_currency c on c.id=i.currency_id
					      left join res_partner p on i.partner_id = p.id
					      left join res_users u on u.id=i.user_id
					      left join res_users e on e.id=p.user_id
					      left join res_partner rp on rp.id= u.partner_id

					  where 
					   i.state::text = 'paid'::text or i.state::text = 'open'::text  AND date_part('year'::text, i.date) = date_part('year'::text, now())
					   ORDER BY u.login, i.number
            )""")
 def init(self):
     cr = self._cr
     drop_view_if_exists(cr, 'report_timesheet_account_date')
     cr.execute("""
         create or replace view report_timesheet_account_date as (
             select
                 min(id) as id,
                 to_char(date,'YYYY') as name,
                 to_char(date,'MM') as month,
                 user_id,
                 account_id,
                 sum(unit_amount) as quantity
             from
                 account_analytic_line
             group by
                 to_char(date,'YYYY'),to_char(date,'MM'), user_id, account_id
         )
     """)
Esempio n. 26
0
 def init(self):
     drop_view_if_exists(self._cr, 'report_stock_lines_date')
     self._cr.execute("""
         create or replace view report_stock_lines_date as (
             select
             p.id as id,
             p.id as product_id,
             max(s.date) as date,
             max(m.date) as move_date,
             p.active as active
         from
             product_product p
                 left join (
                     stock_inventory_line l
                     inner join stock_inventory s on (l.inventory_id=s.id and s.state = 'done')
                 ) on (p.id=l.product_id)
                 left join stock_move m on (m.product_id=p.id and m.state = 'done')
             group by p.id
         )""")
Esempio n. 27
0
 def init(self):
     drop_view_if_exists(self._cr, "report_stock_lines_date")
     self._cr.execute(
         """
         create or replace view report_stock_lines_date as (
             select
             p.id as id,
             p.id as product_id,
             max(s.date) as date,
             max(m.date) as move_date,
             p.active as active
         from
             product_product p
                 left join (
                     stock_inventory_line l
                     inner join stock_inventory s on (l.inventory_id=s.id and s.state = 'done')
                 ) on (p.id=l.product_id)
                 left join stock_move m on (m.product_id=p.id and m.state = 'done')
             group by p.id
         )"""
     )
Esempio n. 28
0
    def init(self):
        """
            Display Deadline, Responsible user, Partner, Department
        """

        drop_view_if_exists(self._cr, 'crm_helpdesk_report')
        self._cr.execute("""
            create or replace view crm_helpdesk_report as (
                select
                    min(c.id) as id,
                    c.date as date,
                    c.create_date,
                    c.date_closed,
                    c.state,
                    c.user_id,
                    c.team_id,
                    c.partner_id,
                    c.company_id,
                    c.priority,
                    c.date_deadline,
                    c.tag_ids,
                    c.channel_id,
                    count(*) as nbr_requests,
                    extract('epoch' from (c.date_closed-c.create_date)
                        )/(3600*24) as  delay_close,
                    (SELECT count(id)
                     FROM mail_message
                     WHERE model='crm.helpdesk'
                        AND res_id=c.id
                        AND message_type = 'email') AS email,
                    abs(avg(
                        extract('epoch' from (c.date_deadline - c.date_closed)
                            ))/(3600*24)) as delay_expected
                from
                    crm_helpdesk c
                where c.active = 'true'
                group by c.date,c.state,c.user_id,c.team_id,c.priority,
                     c.partner_id,c.company_id,c.date_deadline,c.create_date,
                     c.date,c.date_closed,c.tag_ids,c.channel_id,c.id
            )""")
Esempio n. 29
0
    def init(self):
        drop_view_if_exists(self._cr, 'map_line_station_info')
        self._cr.execute("""
            CREATE OR REPLACE VIEW map_line_station_info AS (

				select 
				   opertation_resources_station_platform.id  as id,
				   opertation_resources_station_platform.route_id,
				   opertation_resources_station_platform.station_id,
				   opertation_resources_station_platform.station_type,
				   opertation_resources_station_platform.sequence as squence,
				   opertation_resources_station.latitude,
				   opertation_resources_station.longitude,
				   opertation_resources_station.state,
				   opertation_resources_station.name as station_name,
				   opertation_resources_station_platform.direction as direction
				from   
				opertation_resources_station_platform
				left join opertation_resources_station on opertation_resources_station_platform.station_id = opertation_resources_station.id

				where 
				opertation_resources_station.state = 'inuse'
				
            )""")
Esempio n. 30
0
    def init(self):
        drop_view_if_exists(self._cr, 'quotation_price_line')
        self._cr.execute("""
			CREATE OR REPLACE FUNCTION pricelist_trg()
				  RETURNS trigger AS
				$BODY$ DECLARE 
				 v_categ integer;
				 v_quotation integer;
				 v_provider integer;
				 v_id integer;
				 v_utility double precision;
				 v_costo double precision;
				 v_RECORD RECORD;
				  v_logistic double precision;
				 v_value1 double precision;
				 v_value2 double precision;
				BEGIN
				
				IF TG_OP='INSERT' THEN
					select provider_id into v_provider from quotation_price where id=new.quotation_id ;
					select logistic into v_logistic from res_partner where id=v_provider;
					select id into v_id from res_partner_lines where categ_id =new.categ_id and partner_id=v_provider;
					  FOR v_RECORD IN (select pricelist_id, utility from res_partnerprice_lines where reslines_id=v_id )
						LOOP
						v_utility:=1-(v_RECORD.utility/100);
						v_value1:=1+ (v_logistic/100);
						v_costo:=(new.total*v_value1)/v_utility;
						
						--raise exception '%','utilidad:  '|| v_costo;
							INSERT INTO quotation_pricelist_line(
							     create_uid, quotationprice_id, write_uid, create_date, write_date, 
							   totals, pricelist_id)
							VALUES (new.create_uid, new.id, new.write_uid,  new.create_date, new.write_date,  
							     v_costo::double precision, v_RECORD.pricelist_id );
						END LOOP;
				END IF;
				IF TG_OP='UPDATE' THEN
					select provider_id into v_provider from quotation_price where id=new.quotation_id ;
					select logistic into v_logistic from res_partner where id=v_provider;
					select id into v_id from res_partner_lines where categ_id =new.categ_id and partner_id=v_provider;
					  FOR v_RECORD IN (select pricelist_id, utility from res_partnerprice_lines where reslines_id=v_id )
						LOOP
							v_utility:=1-(v_RECORD.utility/100);
							v_value1:=1+ (v_logistic/100);
							v_costo:=(new.total*v_value1)/v_utility;
							--raise exception '%','utilidad:  '|| v_costo;
							UPDATE quotation_pricelist_line SET totals=v_costo WHERE quotationprice_id=new.id AND pricelist_id=v_RECORD.pricelist_id;
								
						END LOOP;

				END IF;

				 RETURN NEW;
				END 

				; $BODY$
				  LANGUAGE plpgsql VOLATILE
				  COST 100;
				ALTER FUNCTION pricelist_trg()
				  OWNER TO odoo;



				CREATE TRIGGER pricelist_trg
				  AFTER INSERT OR UPDATE
				  ON quotation_price_line
				  FOR EACH ROW
				  EXECUTE PROCEDURE pricelist_trg();

	        """)
Esempio n. 31
0
    def init(self):
        drop_view_if_exists(self._cr, 'dispatch_line_citycode_ref')
        self._cr.execute("""
            create or replace view dispatch_line_citycode_ref as (
           select * from  
          (
             select         
                route_manage_route_manage."gprs_id" as line_id,
                route_manage_route_manage."line_name" as line_name,
                up_start.station_first_name as station_start,
                up_last.station_last_name as station_end,
                cast(up_start.direction as char(4)) as direction,
                hr_department.company_id as company_id,
                res_partner.zip as company_citycode
            from 
                route_manage_route_manage
            left join
         (
           select 
              opertation_resources_station_up.route_id,
              opertation_resources_station_up.station_id,
              opertation_resources_station.name as station_first_name,
              '1' as direction
           from 
              opertation_resources_station_up
           left join
               opertation_resources_station on  opertation_resources_station_up.station_id = opertation_resources_station.id
           where opertation_resources_station_up.station_type = 'first_station'
         ) as up_start  
         on up_start.route_id = route_manage_route_manage.id
            left join
         (
           select 
              opertation_resources_station_up.route_id,
              opertation_resources_station_up.station_id,
              opertation_resources_station.name as station_last_name,
              '1' as direction
           from 
              opertation_resources_station_up
           left join
               opertation_resources_station on  opertation_resources_station_up.station_id = opertation_resources_station.id
           where opertation_resources_station_up.station_type = 'last_station'
         ) as up_last  
         on up_last.route_id = route_manage_route_manage.id
         
            left join 
            
                hr_department on route_manage_route_manage.department_id = hr_department.id
            
            left join 
                res_partner on res_partner.company_id = res_partner.id
          ) as up   where up.direction is not null
   union 
   select * from 
      (
         select 
                route_manage_route_manage."gprs_id" as line_id,
                route_manage_route_manage."line_name" as line_name,
                down_start.station_first_name as station_start,
                down_last.station_last_name as station_end,
                cast(down_start.direction as char(4)) as direction,
                hr_department.company_id as company_id,
                res_partner.zip as company_citycode
            from 
                route_manage_route_manage
            left join
         (
           select 
              opertation_resources_station_down.route_id,
              opertation_resources_station_down.station_id,
              opertation_resources_station.name as station_first_name,
              '2' as direction
           from 
              opertation_resources_station_down
           left join
               opertation_resources_station on  opertation_resources_station_down.station_id = opertation_resources_station.id
           where opertation_resources_station_down.station_type = 'first_station'
         ) as down_start  
         on down_start.route_id = route_manage_route_manage.id
            left join
         (
           select 
              opertation_resources_station_down.route_id,
              opertation_resources_station_down.station_id,
              opertation_resources_station.name as station_last_name,
              '2' as direction
           from 
              opertation_resources_station_down
           left join
               opertation_resources_station on  opertation_resources_station_down.station_id = opertation_resources_station.id
           where opertation_resources_station_down.station_type = 'last_station'
         ) as down_last  
         on down_last.route_id = route_manage_route_manage.id
         
            left join 
            
                hr_department on route_manage_route_manage.department_id = hr_department.id
            
            left join 
                res_partner on res_partner.company_id = res_partner.id

        ) as down where down.direction is not null
            )""")
Esempio n. 32
0
    def init(self):
       drop_view_if_exists(self.env.cr,self._table)
       self._cr.execute('''
             create or replace view 
             registro_compras as (
                    
                    select ai.id, 
                           ai.date_invoice, 
                           ai.date_due, 
                           substr(ai.x_tipo_comprobante_pago,1,2) tipo_cp,                       
                           case when position('-' in ai.number) = 0 then '' 
                                else substr(ai.number, 1, position('-' in ai.number)-1) 
                           end serie,
                           case when position('-' in ai.number) = 0 then ai.number 
                                else substr(ai.number, position('-' in ai.number)+1) 
                           end number,
                           substr(p.x_tipo_persona,1,2) tipo_persona,
                           substr(p.x_tipo_documento_identidad,1,1) tipo_doc,
                           p.vat, 
                           ai.vendor_display_name, 
                           ai.amount_untaxed_signed amount_untaxed,
                           0 exempted,
                           0 no_vat,
                           0 isc,                      
                           (ai.amount_total_company_signed - ai.amount_untaxed_signed) amount_tax, 
                           0 others,
                           ai.amount_total_company_signed amount_total,
                           ai.type,
                           ai.state,
                           ai.date account_date,
                           concat(	
                               extract(year from ai.date),
                               right(concat('0',extract(month from ai.date)),2),
                               '00'
                            ) account_period,
                           concat('M',
                                  right(concat('00000000',aml.id), 8)       
                                 ) correlativo,
                           c.name currency_name,
                           case when c.name = 'USD' then 1
                                else r.rate
                           end rate,
                           ai.move_id cuo,

                           trim(
                               concat_ws('|',
                                   concat(	
                                       extract(year from ai.date),
                                       right(concat('0',extract(month from ai.date)),2),
                                       '00'
                                    ),
                                   ai.move_id,
                                   concat(
                                       'M',
                                       right(concat('00000000',aml.id), 8)       
                                    ),
                                   to_char(ai.date_invoice,'DD/MM/YYYY'),
                                   to_char(ai.date_due,'DD/MM/YYYY'),
                                   substr(ai.x_tipo_comprobante_pago,1,2),
                                   case when position('-' in ai.number) = 0 then '' 
                                        else substr(ai.number, 1, position('-' in ai.number)-1) 
                                   end,
                                   '',
                                   case when position('-' in ai.number) = 0 then ai.number 
                                        else substr(ai.number, position('-' in ai.number)+1) 
                                   end,
                                   '',
                                   substr(p.x_tipo_documento_identidad,1,1),
                                   p.vat,
                                   ai.vendor_display_name,
                                   ai.amount_untaxed_signed,
                                   (ai.amount_total_company_signed - ai.amount_untaxed_signed),
                                   0,
                                   0,
                                   0,
                                   0,
                                   0,
                                   0,
                                   0,
                                   ai.amount_total_company_signed,
                                   c.name,
                                   to_char(coalesce(r.rate,1),'9D999'),
                                   '',
                                   '',
                                   '',
                                   '',
                                   '',
                                   coalesce(to_char(ai.x_fecha_emision_detraccion,'DD/MM/YYYY'),''),
                                   coalesce(ai.x_nro_constancia_detraccion,''),
                                   '',
                                   trim(both '0' from coalesce(substr(ai.x_clasificacion_bienes_y_servicios,1,2),'')),
                                   '',
                                   '',
                                   '',
                                   '',
                                   '',
                                   '',
                                   1,
                                   '',
                                   '',
                                   ''
                               )
                           ) as texto

                    from account_invoice ai
                         inner join (account_invoice_line ail 
                                         inner join account_move_line aml
                                             on (ail.account_id = aml.account_id)
                                    )
                             on (ai.id = ail.invoice_id and ai.move_id = aml.move_id)
                         inner join res_partner p 
                             on (ai.partner_id = p.id)
                         inner join res_currency c 
                             on (ai.currency_id = c.id)
                         left join res_currency_rate r 
                             on (ai.currency_id = r.currency_id and ai.date_invoice = r.name)
                    where ai.type = 'in_invoice' 
                      and ai.state not in ('draft')                      
                      and substr(ai.x_tipo_comprobante_pago,1,2) not in ('91','97','98')

                    UNION ALL
                    select he.id,
                           he.x_fecha_factura, 
                           null, 
                           substr(he.x_tipo_comprobante_pago,1,2) tipo_cp,                       
                           case when position('-' in he.reference) = 0 then '' 
                                else substr(he.reference, 1, position('-' in he.reference)-1) 
                           end serie,
                           case when position('-' in he.reference) = 0 then he.reference 
                                else substr(he.reference, position('-' in he.reference)+1) 
                           end number,
                           substr(p.x_tipo_persona,1,2) tipo_persona,
                           substr(p.x_tipo_documento_identidad,1,1) tipo_doc,
                           p.vat, 
                           p.name, 
                           0 amount_untaxed,
                           0 exempted,
                           he.untaxed_amount no_vat,
                           0 isc,                      
                           0 amount_tax, 
                           0 others,
                           he.total_amount amount_total,
                           'expense',
                           he.state,
                           he.date account_date,
                           concat(    
                               extract(year from he.date),
                               right(concat('0',extract(month from he.date)),2),
                               '00'
                            ) account_period,
                           concat('M',
                                  right(concat('00000000',aml.id), 8)       
                                 ) correlativo,
                           c.name currency_name,
                           case when c.name = 'USD' then 1
                                else r.rate
                           end rate,
                           hes.account_move_id cuo,

                           trim(
                               concat_ws('|',
                                   concat(    
                                       extract(year from he.date),
                                       right(concat('0',extract(month from he.date)),2),
                                       '00'
                                    ),
                                   hes.account_move_id,
                                   concat('M',
                                          right(concat('00000000',aml.id), 8)       
                                         ),
                                   to_char(he.x_fecha_factura,'DD/MM/YYYY'),
                                   '',
                                   substr(he.x_tipo_comprobante_pago,1,2),
                                   case when position('-' in he.reference) = 0 then '' 
                                        else substr(he.reference, 1, position('-' in he.reference)-1) 
                                   end,
                                   '',
                                   case when position('-' in he.reference) = 0 then he.reference 
                                        else substr(he.reference, position('-' in he.reference)+1) 
                                   end,
                                   '',
                                   substr(p.x_tipo_documento_identidad,1,1),
                                   p.vat,
                                   p.name,
                                   0,
                                   0,
                                   0,
                                   0,
                                   0,
                                   0,
                                   he.untaxed_amount,
                                   0,
                                   0,
                                   he.total_amount,
                                   c.name,
                                   to_char(coalesce(r.rate,1),'9D999'),
                                   '',
                                   '',
                                   '',
                                   '',
                                   '',
                                   coalesce(to_char(he.x_fecha_constancia_detraccion,'DD/MM/YYYY'),''),
                                   coalesce(he.x_nro_constancia_detraccion,''),
                                   '',
                                   trim(both '0' from coalesce(substr(he.x_clasificacion_bienes_y_servicios,1,2),'')),
                                   '',
                                   '',
                                   '',
                                   '',
                                   '',
                                   '',
                                   1,
                                   '',
                                   '',
                                   ''
                               )
                           ) as texto

                    from hr_expense he
                         inner join (hr_expense_sheet hes 
                                         inner join account_move_line aml 
                                             on (hes.account_move_id = aml.move_id)
                                    )
                             on (he.sheet_id = hes.id and he.account_id = aml.account_id)
                         inner join res_partner p 
                             on (he.x_proveedor = p.id)
                         inner join res_currency c 
                             on (he.currency_id = c.id)
                         left join res_currency_rate r 
                             on (he.currency_id = r.currency_id and he.x_fecha_factura = r.name)                         
                    where he.state not in ('draft')                      
                      and substr(he.x_tipo_comprobante_pago_1,1,2) not in ('91','97','98')

             )
        ''')
Esempio n. 33
0
 def init(self):
     drop_view_if_exists(self.env.cr, self._table)
     self._cr.execute('''
         create or replace view 
         registro_ventas as (
             select ai.id, 
                    ai.date_invoice, 
                    ai.date_due,                       
                    ai.x_tipo_comprobante_pago tipo_cp,
                    case when position('-' in ai.number) = 0 then '' 
                             else substr(ai.number, 1, position('-' in ai.number)-1) 
                    end serie,
                    case when position('-' in ai.number) = 0 then ai.number 
                             else substr(ai.number, position('-' in ai.number)+1) 
                    end number,
                    p.x_tipo_persona tipo_persona,
                    p.x_tipo_documento_identidad tipo_doc,
                    p.vat,
                    p.name customer_name,
                    ai.amount_untaxed_signed amount_untaxed,
                    0 exempted,
                    0 no_vat,
                    0 isc,
                    (ai.amount_total_company_signed - ai.amount_untaxed_signed) amount_tax,
                    0 others,
                    ai.amount_total_company_signed amount_total,
                    ai.type,
                    ai.state,
                    ai.date account_date,
                    concat(extract(year from ai.date),
                           right(concat('0',extract(month from ai.date)),2),
                           '00'
                         ) account_period,
                    concat('M',
                           right(concat('00000000',aml.id), 8)       
                         ) correlativo,
                    c.name currency_name,
                    case when c.name = 'PEN' then 1
                         else r.rate
                    end rate,
                    ai.move_id cuo,
                    
                    trim(
                            concat_ws('|',
                                concat(	
                                    extract(year from ai.date),
                                    right(concat('0',extract(month from ai.date)),2),
                                    '00'
                                 ),
                                ai.move_id,
                                concat(
                                    'M',
                                    right(concat('00000000',aml.id), 8)       
                                 ),
                                to_char(ai.date_invoice,'DD/MM/YYYY'),
                                to_char(ai.date_due,'DD/MM/YYYY'),
                                ai.x_tipo_comprobante_pago,
                                case when position('-' in ai.number) = 0 then '' 
                                     else substr(ai.number, 1, position('-' in ai.number)-1) 
                                end,
                                case when position('-' in ai.number) = 0 then ai.number 
                                     else substr(ai.number, position('-' in ai.number)+1) 
                                end,
                                '',
                                p.x_tipo_documento_identidad,
                                p.vat,
                                p.name,
                                0,
                                ai.amount_untaxed_signed,
                                0,
                                (ai.amount_total_company_signed - ai.amount_untaxed_signed),
                                0,
                                0,
                                0,
                                0,
                                0,
                                0,
                                0,
                                ai.amount_total_company_signed,
                                c.name,
                                to_char(coalesce(r.rate,1),'9D999'),
                                '',
                                '',
                                '',
                                '',
                                '',
                                '',
                                '',
                                '1',
                                ''
                            )
                    ) as ple                       
                    
             from account_invoice ai
                 inner join (account_invoice_line ail 
                                 inner join account_move_line aml
                                     on (ail.account_id = aml.account_id)
                             )
                     on (ai.id = ail.invoice_id and ai.move_id = aml.move_id)
                 inner join res_partner p 
                     on (ai.partner_id = p.id)
                 inner join res_currency c 
                     on (ai.currency_id = c.id)
                 left join res_currency_rate r 
                     on (ai.currency_id = r.currency_id and ai.date_invoice = r.name)
             where ai.type = 'out_invoice' 
               and ai.state not in ('draft')            
           )
     ''')
Esempio n. 34
0
 def init(self):
     drop_view_if_exists(self._cr, self._table)
     self._cr.execute("""CREATE or REPLACE VIEW %s as ( %s )""" %
                      (self._table, self._select_sql()))
Esempio n. 35
0
 def init(self):
     drop_view_if_exists(self._cr, self._table)
     self._cr.execute("""CREATE or REPLACE VIEW %s as ( %s FROM %s %s )""" % (self._table, self._select(), self._from(), self._group_by()))