Ejemplo n.º 1
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'hr_timesheet_report')
     cr.execute("""
         create or replace view hr_timesheet_report as (
             select
                 min(t.id) as id,
                 l.date as date,
                 sum(l.amount) as cost,
                 sum(l.unit_amount) as quantity,
                 l.account_id as account_id,
                 l.journal_id as journal_id,
                 l.product_id as product_id,
                 l.general_account_id as general_account_id,
                 l.user_id as user_id,
                 l.company_id as company_id,
                 l.currency_id as currency_id
             from
                 hr_analytic_timesheet as t
                 left join account_analytic_line as l ON (t.line_id=l.id)
             group by
                 l.date,
                 l.account_id,
                 l.product_id,
                 l.general_account_id,
                 l.journal_id,
                 l.user_id,
                 l.company_id,
                 l.currency_id
         )
     """)
    def init(self, cr):

        tools.drop_view_if_exists(cr, "gym_class_detail_report")
        cr.execute(
            """
            CREATE OR REPLACE VIEW gym_class_detail_report AS  (

SELECT 
    date, 
    schedule_id, 
    activity_id, 
    class_code, 
    week_day, 
    trainer_id, 
    trainee_id, 
    gym_gym_class_detail.id,
    gym_gym_class.id as class_id,
    value 
FROM gym_gym_class_detail 
INNER JOIN gym_gym_class
ON gym_gym_class_detail.gym_class_id = gym_gym_class.id
)

"""
        )
Ejemplo n.º 3
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'payslip_report')
     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
         )
     """)
Ejemplo n.º 4
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'project_issue_report')
        cr.execute("""
            CREATE OR REPLACE VIEW project_issue_report AS (
                SELECT
                    c.id as id,
                    date(c.date_open) as opening_date,
                    date(c.create_date) as create_date,
                    date(c.date_last_stage_update) as date_last_stage_update,
                    c.user_id,
                    c.working_hours_open,
                    c.working_hours_close,
                    c.section_id,
                    c.stage_id,
                    date(c.date_closed) as date_closed,
                    c.company_id as company_id,
                    c.priority as priority,
                    c.project_id as project_id,
                    c.version_id as version_id,
                    1 as nbr,
                    c.partner_id,
                    c.channel,
                    c.task_id,
                    c.day_open as delay_open,
                    c.day_close as delay_close,
                    (SELECT count(id) FROM mail_message WHERE model='project.issue' AND res_id=c.id) AS email

                FROM
                    project_issue c
                WHERE c.active= 'true'
            )""")
Ejemplo n.º 5
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_account_analytic_line_to_invoice')
     cr.execute("""
         CREATE OR REPLACE VIEW report_account_analytic_line_to_invoice AS (
             SELECT
                 DISTINCT(to_char(l.date,'MM')) as month,
                 to_char(l.date, 'YYYY') as name,
                 MIN(l.id) AS id,
                 l.product_id,
                 l.account_id,
                 SUM(l.amount) AS amount,
                 SUM(l.unit_amount*t.list_price) AS sale_price,
                 SUM(l.unit_amount) AS unit_amount,
                 l.product_uom_id
             FROM
                 account_analytic_line l
             left join
                 product_product p on (l.product_id=p.id)
             left join
                 product_template t on (p.product_tmpl_id=t.id)
             WHERE
                 (invoice_id IS NULL) and (to_invoice IS NOT NULL)
             GROUP BY
                 to_char(l.date, 'YYYY'), to_char(l.date,'MM'), product_id, product_uom_id, account_id
         )
     """)
 def init(self, cr):
     # self._table = sale_invoice_payment_report
     tools.drop_view_if_exists(cr, self._table)
     cr.execute("""CREATE or REPLACE VIEW sale_invoice_payment_report as (
         select *, ((case when coalesce(order_amount, 0) = 0 then 1 when paid_amount > order_amount then 1 else paid_amount / order_amount end) * 100)::decimal(16,2) as percent_paid
         from (
             select *, (case when coalesce(amount_total, 0) = 0 then 0 else ((invoiced_amount / amount_total) * residual) end)::decimal(16,2) as unpaid_amount,
                 (case when coalesce(amount_total, 0) = 0 then 0 else invoiced_amount - ((invoiced_amount / amount_total) * residual) end)::decimal(16,2) as paid_amount
                 from (
                             select so.id as id,
                                 so.user_id,
                                 so.partner_id,
                                 so.id as order_id,
                                 so.date_order as date,
                                 to_char(so.date_order::timestamp with time zone, 'YYYY'::text) AS year,
                                 to_char(so.date_order::timestamp with time zone, 'MM'::text) AS month,
                                 to_char(so.date_order::timestamp with time zone, 'YYYY-MM-DD'::text) AS day,
                                 (case when coalesce(so.amount_untaxed, 0) = 0 then so.amount_untaxed else so.amount_untaxed end) as order_amount,
                                 ai.id as invoice_id,
                                 ai.amount_beforetax as invoiced_amount,
                                 -- to be calculated
                                 ai.amount_total,
                                 ai.residual
                             from sale_order so
                             left outer join sale_order_invoice_rel sir on so.id = sir.order_id
                             left outer join account_invoice ai on ai.id = sir.invoice_id and ai.state not in ('draft','cancel')
                             where so.state not in ('draft', 'cancel')
                 ) a
             ) b
     )""")
Ejemplo n.º 7
0
    def init(self, cr):

        """ Phone Calls By User And Section
            @param cr: the current row, from the database cursor,
        """
        tools.drop_view_if_exists(cr, 'crm_phonecall_report')
        cr.execute("""
            create or replace view crm_phonecall_report as (
                select
                    id,
                    to_char(c.date, 'YYYY') as name,
                    to_char(c.date, 'MM') as month,
                    to_char(c.date, 'YYYY-MM-DD') as day,
                    to_char(c.create_date, 'YYYY-MM-DD') as creation_date,
                    to_char(c.date_open, 'YYYY-MM-DD') as opening_date,
                    to_char(c.date_closed, 'YYYY-mm-dd') as date_closed,
                    c.state,
                    c.user_id,
                    c.section_id,
                    c.categ_id,
                    c.partner_id,
                    c.opportunity_id,
                    c.duration,
                    c.company_id,
                    c.priority,
                    1 as nbr,
                    date_trunc('day',c.create_date) as create_date,
                    extract('epoch' from (c.date_closed-c.create_date))/(3600*24) as  delay_close,
                    extract('epoch' from (c.date_open-c.create_date))/(3600*24) as  delay_open
                from
                    crm_phonecall c
            )""")
Ejemplo n.º 8
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'asset_asset_report')
     cr.execute("""
         create or replace view asset_asset_report as (
             select
                 min(dl.id) as id,
                 dl.name as name,
                 dl.line_date as depreciation_date,
                 a.date_start as date_start,
                 a.date_remove as date_remove,
                 a.asset_value as asset_value,
                 dl.amount as depreciation_value,
                 (CASE WHEN dl.move_check
                   THEN dl.amount
                   ELSE 0
                   END) as posted_value,
                 (CASE WHEN NOT dl.move_check
                   THEN dl.amount
                   ELSE 0
                   END) as unposted_value,
                 dl.asset_id as asset_id,
                 dl.move_check as move_check,
                 a.category_id as asset_category_id,
                 a.partner_id as partner_id,
                 a.state as state,
                 count(dl.*) as nbr,
                 a.company_id as company_id
             from account_asset_depreciation_line dl
                 left join account_asset_asset a on (dl.asset_id=a.id)
             group by
                 dl.amount, dl.asset_id, dl.line_date, dl.name,
                 a.date_start, a.date_remove, dl.move_check, a.state,
                 a.category_id, a.partner_id, a.company_id, a.asset_value,
                 a.id, a.salvage_value
     )""")
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'account_followup_stat_by_partner')
     # Here we don't have other choice but to create a virtual ID based on the concatenation
     # of the partner_id and the company_id, because if a partner is shared between 2 companies,
     # we want to see 2 lines for him in this table. It means that both company should be able
     # to send him follow-ups separately . An assumption that the number of companies will not
     # reach 10 000 records is made, what should be enough for a time.
     cr.execute("""
         create view account_followup_stat_by_partner as (
             SELECT
                 l.partner_id * 10000::bigint + l.company_id as id,
                 l.partner_id AS partner_id,
                 min(l.date) AS date_move,
                 max(l.date) AS date_move_last,
                 max(l.followup_date) AS date_followup,
                 max(l.followup_line_id) AS max_followup_id,
                 sum(l.debit - l.credit) AS balance,
                 l.company_id as company_id
             FROM
                 account_move_line l
                 LEFT JOIN account_account a ON (l.account_id = a.id)
             WHERE
                 a.active AND
                 a.type = 'receivable' AND
                 l.reconcile_id is NULL AND
                 l.partner_id IS NOT NULL
                 GROUP BY
                 l.partner_id, l.company_id
         )""")
Ejemplo n.º 10
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_pos_order')
     cr.execute("""
         create or replace view report_pos_order as (
             select
                 min(l.id) as id,
                 count(*) as nbr,
                 to_date(to_char(s.date_order, 'dd-MM-YYYY'),'dd-MM-YYYY') as date,
                 sum(l.qty * u.factor) as product_qty,
                 sum(l.qty * l.price_unit) as price_total,
                 sum((l.qty * l.price_unit) * (l.discount / 100)) as total_discount,
                 (sum(l.qty*l.price_unit)/sum(l.qty * u.factor))::decimal(16,2) as average_price,
                 sum(cast(to_char(date_trunc('day',s.date_order) - date_trunc('day',s.create_date),'DD') as int)) as delay_validation,
                 to_char(s.date_order, 'YYYY') as year,
                 to_char(s.date_order, 'MM') as month,
                 to_char(s.date_order, 'YYYY-MM-DD') as day,
                 s.partner_id as partner_id,
                 s.state as state,
                 s.user_id as user_id,
                 s.shop_id as shop_id,
                 s.company_id as company_id,
                 s.sale_journal as journal_id,
                 l.product_id as product_id
             from pos_order_line as l
                 left join pos_order s on (s.id=l.order_id)
                 left join product_template pt on (pt.id=l.product_id)
                 left join product_uom u on (u.id=pt.uom_id)
             group by
                 to_char(s.date_order, 'dd-MM-YYYY'),to_char(s.date_order, 'YYYY'),to_char(s.date_order, 'MM'),
                 to_char(s.date_order, 'YYYY-MM-DD'), s.partner_id,s.state,
                 s.user_id,s.shop_id,s.company_id,s.sale_journal,l.product_id,s.create_date
             having
                 sum(l.qty * u.factor) != 0)""")
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'is_comparatif_gamme_standard_generique')
        cr.execute("""
            CREATE OR REPLACE view is_comparatif_gamme_standard_generique AS (
                SELECT 
                    mb.id,
                    mb.product_tmpl_id  as product_id,
                    mb.id               as bom_id,
                    mr1.id              as standard_routing_id,
                    mrw1.sequence       as standard_sequence,
                    mrw1.name           as standard_name,
                    mrw1.workcenter_id  as standard_workcenter_id,
                    mrw1.is_nb_secondes as standard_nb_secondes,

                    mr2.id              as generique_routing_id,
                    mrw2.sequence       as generique_sequence,
                    mrw2.name           as generique_name,
                    mrw2.workcenter_id  as generique_workcenter_id,
                    mrw2.is_nb_secondes as generique_nb_secondes,

                    (mrw1.is_nb_secondes-mrw2.is_nb_secondes) as delta_nb_secondes


                FROM mrp_bom mb inner join mrp_routing mr1             on mb.routing_id=mr1.id
                                inner join mrp_routing_workcenter mrw1 on mr1.id=mrw1.routing_id

                                inner join mrp_routing mr2             on mb.is_gamme_generique_id=mr2.id
                                inner join mrp_routing_workcenter mrw2 on mr2.id=mrw2.routing_id

                WHERE mb.id>0 and mrw1.sequence=mrw2.sequence
            )
        """)
Ejemplo n.º 12
0
 def init(self, cr):
     # Order Lines
     tools.drop_view_if_exists(cr, self._table)
     cr.execute("""CREATE or REPLACE VIEW %s as (
         select sub.id, purchase_id, invoice_id, product_id, sub.name, order_id, product_qty, product_uom,
             case when curr.type_ref_base = 'smaller' then
                 sub.price_unit / (case when sub.type in ('in_invoice', 'in_refund') then cr.rate else cr.rate_sell end)
                 else
                 sub.price_unit * (case when sub.type in ('in_invoice', 'in_refund') then cr.rate else cr.rate_sell end)
             end AS price_unit,
             case when curr.type_ref_base = 'smaller' then
                 sub.price_subtotal / (case when sub.type in ('in_invoice', 'in_refund') then cr.rate else cr.rate_sell end)
                 else
                 sub.price_subtotal * (case when sub.type in ('in_invoice', 'in_refund') then cr.rate else cr.rate_sell end)
             end AS price_subtotal
         from
         (select ai.type, ai.date_invoice, ai.currency_id, ail.id,  po.id as purchase_id, ail.invoice_id, po.ref_order_id as order_id, ail.product_id, ail.name,
             case when ai.type = 'in_invoice' then ail.quantity else -ail.quantity end as product_qty,
             ail.uos_id product_uom, ail.price_unit,
             case when ai.type = 'in_invoice' then price_subtotal else -price_subtotal end as price_subtotal
         from account_invoice ai
         join (select purchase_id purchase_id, invoice_id from purchase_invoice_rel) pil 
         on pil.invoice_id = ai.id
         join purchase_order po on po.id = pil.purchase_id and po.is_subcontract = True and po.ref_order_id is not null
         join account_invoice_line ail on ail.invoice_id = ai.id
         where ai.state not in ('draft', 'cancel') and ai.type in ('in_invoice', 'in_refund')) sub
         -- currency
         JOIN res_currency_rate cr ON cr.currency_id = sub.currency_id
         JOIN res_currency curr ON curr.id = cr.currency_id
           WHERE cr.id IN (
         SELECT cr2.id
            FROM res_currency_rate cr2
           WHERE cr2.currency_id = sub.currency_id AND (sub.date_invoice IS NOT NULL AND cr2.name <= sub.date_invoice OR  sub.date_invoice IS NULL AND cr2.name <= now())
           ORDER BY name DESC LIMIT 1)
     )""" % (self._table,))
Ejemplo n.º 13
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'account_followup_stat')
     cr.execute("""
         create or replace view account_followup_stat as (
             SELECT
                 l.id as id,
                 l.partner_id AS partner_id,
                 min(l.date) AS date_move,
                 max(l.date) AS date_move_last,
                 max(l.followup_date) AS date_followup,
                 max(l.followup_line_id) AS followup_id,
                 sum(l.debit) AS debit,
                 sum(l.credit) AS credit,
                 sum(l.debit - l.credit) AS balance,
                 l.company_id AS company_id,
                 l.blocked as blocked,
                 l.period_id AS period_id
             FROM
                 account_move_line l
                 LEFT JOIN account_account a ON (l.account_id = a.id)
             WHERE
                 a.active AND
                 a.type = 'receivable' AND
                 l.reconcile_id is NULL AND
                 l.partner_id IS NOT NULL
             GROUP BY
                 l.id, l.partner_id, l.company_id, l.blocked, l.period_id
         )""")
Ejemplo n.º 14
0
 def init(self, cr):
     # Order Lines
     tools.drop_view_if_exists(cr, self._table)
     cr.execute("""CREATE or REPLACE VIEW %s as (
         select sub.id, sub.expense_id, sub.product_id, sub.name, sub.order_id, sub.unit_quantity product_qty, sub.uom_id product_uom,
             case when curr.type_ref_base = 'smaller' then
                 sub.unit_amount / cr.rate
                 else
                 sub.unit_amount * cr.rate
             end AS price_unit,
             case when curr.type_ref_base = 'smaller' then
                 sub.price_subtotal / cr.rate
                 else
                 sub.price_subtotal * cr.rate
             end AS price_subtotal
         from (select x.date, x.currency_id, xl.id, expense_id, product_id, xl.name, cost_order_id as order_id, unit_quantity,
             uom_id, unit_amount, unit_quantity*unit_amount as price_subtotal
         from hr_expense_line xl
         join hr_expense_expense x on xl.expense_id = x.id
         where xl.cost_order_id is not null) sub
         -- currency
         JOIN res_currency_rate cr ON cr.currency_id = sub.currency_id
         JOIN res_currency curr ON curr.id = cr.currency_id
           WHERE cr.id IN (
         SELECT cr2.id
            FROM res_currency_rate cr2
           WHERE cr2.currency_id = sub.currency_id AND (sub.date IS NOT NULL AND cr2.name <= sub.date OR  sub.date IS NULL AND cr2.name <= now())
           ORDER BY name DESC LIMIT 1)
     )""" % (self._table,))
Ejemplo n.º 15
0
    def init(self, cr):

        """ Phone Calls By User And Team
            @param cr: the current row, from the database cursor,
        """
        tools.drop_view_if_exists(cr, 'crm_phonecall_report')
        cr.execute("""
            create or replace view crm_phonecall_report as (
                select
                    id,
                    c.date_open as opening_date,
                    c.date_closed as date_closed,
                    c.state,
                    c.user_id,
                    c.team_id,
                    c.categ_id,
                    c.partner_id,
                    c.duration,
                    c.company_id,
                    c.priority,
                    1 as nbr,
                    c.create_date as create_date,
                    extract('epoch' from (c.date_closed-c.create_date))/(3600*24) as  delay_close,
                    extract('epoch' from (c.date_open-c.create_date))/(3600*24) as  delay_open
                from
                    crm_phonecall c
            )""")
Ejemplo n.º 16
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_day_seo')
     cr.execute("""
         create or replace view report_day_seo as (
             SELECT
               row_number() over() as id,
               p.date_start,
               p.date_start date_end,
               extract(week FROM s.date) week_number,
               l.partner_id,
               l.service_id,
               p.specialist_id,
               p.process_type,
               p.campaign,
               s.top3,
               s.top10,
               s.fact,
               s.date,
               pl.name plan
             FROM
               process_seo p
               LEFT JOIN process_launch l on (l.id=p.launch_id)
               LEFT JOIN report_day_seo_statistic s on (p.campaign=s.campaign)
               LEFT JOIN kpi_period k on (k.name=to_char(s.date, 'YYYY/MM') and calendar='rus')
               LEFT JOIN process_seo_plan pl on (pl.period_id=k.id)
             WHERE p.state='implementation' and p.campaign is not null
         )""")
Ejemplo n.º 17
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'project_task_history_cumulative')

        cr.execute(""" CREATE VIEW project_task_history_cumulative AS (
            SELECT
                history.date::varchar||'-'||history.history_id::varchar AS id,
                history.date AS end_date,
                *
            FROM (
                SELECT
                    h.id AS history_id,
                    h.date+generate_series(0, CAST((coalesce(h.end_date, DATE 'tomorrow')::date - h.date) AS integer)-1) AS date,
                    h.task_id, h.type_id, h.user_id, h.kanban_state,
                    count(h.task_id) as nbr_tasks,
                    greatest(h.remaining_hours, 1) AS remaining_hours, greatest(h.planned_hours, 1) AS planned_hours,
                    t.project_id
                FROM
                    project_task_history AS h
                    JOIN project_task AS t ON (h.task_id = t.id)
                GROUP BY
                  h.id,
                  h.task_id,
                  t.project_id

            ) AS history
        )
        """)
Ejemplo n.º 18
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'mrp_workorder')
     cr.execute("""
         create or replace view mrp_workorder as (
             select
                 date(wl.date_planned) as date,
                 min(wl.id) as id,
                 mp.product_id as product_id,
                 p.product_tmpl_id,
                 t.categ_id as category_id,
                 sum(wl.hour) as total_hours,
                 avg(wl.delay) as delay,
                 (w.costs_hour*sum(wl.hour)) as total_cost,
                 wl.production_id as production_id,
                 wl.workcenter_id as workcenter_id,
                 sum(wl.cycle) as total_cycles,
                 count(*) as nbr,
                 sum(mp.product_qty) as product_qty,
                 wl.state as state,
                 mp.user_id,
                 mp.routing_id,
                 mp.bom_id
             from mrp_production_workcenter_line wl
                 left join mrp_workcenter w on (w.id = wl.workcenter_id)
                 left join mrp_production mp on (mp.id = wl.production_id)
                 left join product_product p on (mp.product_id=p.id)
                 left join product_template t on (p.product_tmpl_id=t.id)
             group by
                 w.costs_hour, mp.product_id, mp.name, mp.user_id, mp.routing_id, mp.bom_id, wl.state, wl.date_planned, wl.production_id, wl.workcenter_id, p.product_tmpl_id, t.categ_id
     )""")
Ejemplo n.º 19
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'crm_activity_report')
     cr.execute("""
         CREATE OR REPLACE VIEW crm_activity_report AS (
             select
                 m.id,
                 m.subtype_id,
                 m.author_id,
                 m.date,
                 l.user_id,
                 l.team_id,
                 l.country_id,
                 l.company_id,
                 l.stage_id,
                 l.partner_id,
                 l.type as lead_type
             from
                 "mail_message" m
             join
                 "crm_lead" l
             on
                 (m.res_id = l.id)
             WHERE
                 (m.model = 'crm.lead')
         )""")
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'account_due_statment')
     cr.execute("""
         create or replace view account_due_statment as (
             SELECT
                 l.id as id,
                 l.partner_id AS partner_id,
                 am.name AS invoice_number,
                 l.ref AS ref,
                 min(l.date) AS date_move,
                 max(l.date_maturity) AS date_maturity,
                 sum(l.debit) AS debit,
                 sum(l.credit) AS credit,
                 sum(l.debit - l.credit) AS balance,
                 l.company_id AS company_id,
                 l.period_id AS period_id
             FROM
                 account_move_line l
                 LEFT JOIN account_account a ON (l.account_id = a.id)
                 LEFT JOIN account_move am ON (l.move_id = am.id)
             WHERE
                 a.active AND
                 a.type = 'receivable' AND
                 l.reconcile_id is NULL AND
                 l.partner_id IS NOT NULL
             GROUP BY
                 l.id, l.partner_id,am.name,l.ref, l.company_id,  l.period_id
         )""")
Ejemplo n.º 21
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, self._table)
     cr.execute("""CREATE or REPLACE VIEW %s as (
         %s
         FROM ( %s )
         %s
         )""" % (self._table, self._select(), self._from(), self._group_by()))
Ejemplo n.º 22
0
 def init(self, cr):
     # Note : start_date_hour must be remove when the read_group will allow grouping on the hour of a datetime. Don't forget to change the view !
     tools.drop_view_if_exists(cr, 'im_livechat_report')
     cr.execute("""
         CREATE OR REPLACE VIEW im_livechat_report AS (
             SELECT
                 min(M.id) as id,
                 S.uuid as uuid,
                 CONCAT(C.name, ' / ', S.id) as session_name,
                 S.create_date as start_date,
                 to_char(date_trunc('hour', S.create_date), 'YYYY-MM-DD HH24:MI:SS') as start_date_hour,
                 EXTRACT('epoch' from ((SELECT (max(create_date)-min(create_date)) FROM im_chat_message WHERE to_id=S.id AND from_id = U.id))) as time_in_session,
                 EXTRACT('epoch' from ((SELECT min(create_date) FROM im_chat_message WHERE to_id=S.id AND from_id IS NOT NULL)-(SELECT min(create_date) FROM im_chat_message WHERE to_id=S.id AND from_id IS NULL))) as time_to_answer,
                 EXTRACT('epoch' from (max((SELECT (max(create_date)) FROM im_chat_message WHERE to_id=S.id))-S.create_date)) as duration,
                 (SELECT count(distinct COALESCE(from_id, 0)) FROM im_chat_message WHERE to_id=S.id) as nbr_speakers,
                 (SELECT count(id) FROM im_chat_message WHERE to_id=S.id) as nbr_messages,
                 count(M.id) as nbr_user_messages,
                 U.id as user_id,
                 S.channel_id as channel_id,
                 S.id as session_id
             FROM im_chat_message M
                 LEFT JOIN im_chat_session S on (S.id = M.to_id)
                 LEFT JOIN res_users U on (U.id = M.from_id)
                 LEFT JOIN im_livechat_channel C on (S.channel_id = C.id)
             WHERE S.channel_id IS NOT NULL
             GROUP BY U.id, M.to_id, S.id, C.name, S.uuid
         )
     """)
Ejemplo n.º 23
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'report_sales_by_margin_pos_month')
        cr.execute("""
            create or replace view report_sales_by_margin_pos_month as (
                select
                    min(pol.id) as id,
                    po.user_id as user_id,
                    pt.name as product_name,
                    to_char(date_trunc('month',po.date_order),'YYYY-MM-DD')::text as date_order,
                    sum(pol.qty) as qty,
                    pt.list_price-pt.standard_price as net_margin_per_qty,
                    (pt.list_price-pt.standard_price) *sum(pol.qty) as total
                from
                    product_template as pt,
                    product_product as pp,
                    pos_order_line as pol,
                    pos_order as po
                where
                    pol.product_id = pp.product_tmpl_id and
                    pp.product_tmpl_id = pt.id and
                    po.id = pol.order_id

                group by
                    pt.name,
                    pt.list_price,
                    pt.standard_price,
                    po.user_id,
                    to_char(date_trunc('month',po.date_order),'YYYY-MM-DD')::text

                )
        """)
Ejemplo n.º 24
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_pos_rest')
     cr.execute("""
         create or replace view report_pos_rest as (
             select
                 min(l.id) as id,
                 count(*) as nbr,
                 s.date_order as date,
                 ps.start_at as date_service,
                 sum(l.qty * u.factor) as product_qty,
                 sum(price_subtotal) as price_total,
                 sum(l.price_subtotal_incl) as total_incl,
                 sum((l.qty * l.price_unit) * (l.discount / 100)) as total_discount,
                 (sum(l.qty*l.price_unit)/sum(l.qty * u.factor))::decimal(16,2) as average_price,
                 sum(cast(to_char(date_trunc('day',s.date_order) - date_trunc('day',s.create_date),'DD') as int)) as delay_validation,
                 s.partner_id as partner_id,
                 s.state as state,
                 s.user_id as user_id,
                 s.warehouse_id as warehouse_id,
                 s.company_id as company_id,
                 s.sale_journal as journal_id,
                 l.product_id as product_id,
                 pt.categ_id,pc.parent_id as parent_categ_id
             from pos_order_line as l
                 left join pos_order s on (s.id=l.order_id)
                 left join pos_session ps on (ps.id=s.session_id)
                 left join product_product pp on (pp.id=l.product_id)
                 left join product_template pt on (pt.id=pp.product_tmpl_id)
                 left join product_category pc on (pc.id=pt.categ_id)
                 left join product_uom u on (u.id=pt.uom_id)
             group by
                 s.date_order,ps.start_at, s.partner_id,s.state,
                 s.user_id,s.warehouse_id,s.company_id,s.sale_journal,l.product_id,pt.categ_id,pc.parent_id,s.create_date
             having
                 sum(l.qty * u.factor) != 0)""")
Ejemplo n.º 25
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'phieunhap_report')
        cr.execute("""
            create or replace view phieunhap_report as (
                select
                   min(ctn.id) as id,
                    ctn.phieunhap_id as phieunhap_id,
                    ctn.nguyenlieu_id as nguyenlieu_id,
                    ctn.slnhap as slnhap,
                    ctn.dongia as dongia,
                    sum(ctn.slnhap * ctn.dongia) as tong,

                    count(pn.id) as slnl,
                    
                    to_char(pn.ngaynhap, 'YYYY') as year,
                    to_char(pn.ngaynhap, 'MM') as month,
                    to_char(pn.ngaynhap, 'YYYY-MM-DD') as day
                    
                from
                    chitiet_nhap ctn
                      join phieu_nhap pn on (pn.id=ctn.phieunhap_id) 
            
                group by
                    ctn.phieunhap_id,
                    ctn.nguyenlieu_id,
                    
                    pn.ngaynhap,
                    ctn.slnhap,
                    ctn.dongia
            )
        """)
Ejemplo n.º 26
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'is_pic_3mois')

        cr.execute("""
                CREATE OR REPLACE view is_pic_3mois AS (
                SELECT 
                    sol.id,
                    so.partner_id,
                    sol.product_id,
                    pt.is_category_id,
                    concat (pt.is_code, ' ', pt.name, ' (', im.name, ')') description,
                    pt.is_ref_client,
                    pt.is_mold_id,
                    sol.product_uom_qty,
                    sol.is_date_expedition,
                    sol.is_type_commande,
                    im.project,
                    (select route_id from stock_route_product srp where srp.product_id=pt.id  limit 1) route_id
                FROM sale_order so inner join sale_order_line sol on so.id=sol.order_id 
                                   inner join product_product pp on pp.id = sol.product_id
                                   inner join product_template pt on pt.id = pp.product_tmpl_id
                                   left outer join is_mold im on pt.is_mold_id = im.id


               )
        """)
Ejemplo n.º 27
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'report_stock_balance')
        cr.execute(
            """
            create or replace view report_stock_balance as (
                SELECT min(line.id) as id,
                       goods.name as goods,
                       line.lot as lot,
                       uom.name as uom,
                       wh.name as warehouse,
                       sum(line.qty_remaining) as goods_qty,
                       sum(line.subtotal) as cost

                FROM wh_move_line line
                LEFT JOIN warehouse wh ON line.warehouse_dest_id = wh.id
                LEFT JOIN goods goods ON line.goods_id = goods.id
                LEFT JOIN uom uom ON line.uom_id = uom.id

                WHERE line.qty_remaining > 0
                  AND wh.type = 'stock'
                  AND line.state = 'done'

                GROUP BY wh.name, line.lot, goods.name, uom.name

                ORDER BY goods.name, wh.name, goods_qty asc
            )
        """)
Ejemplo n.º 28
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'report_transaction_pos')
        cr.execute("""
            create or replace view report_transaction_pos as (
               select
                    min(absl.id) as id,
                    count(absl.id) as no_trans,
                    sum(absl.amount) as amount,
                    sum((100.0-line.discount) * line.price_unit * line.qty / 100.0) as disc,
                    to_char(date_trunc('day',absl.create_date),'YYYY-MM-DD')::text as date_create,
                    po.user_id as user_id,
                    po.sale_journal as journal_id,
                    abs.journal_id as jl_id,
                    count(po.invoice_id) as invoice_id,
                    count(p.id) as product_nb
                from
                    account_bank_statement_line as absl,
                    account_bank_statement as abs,
                    product_product as p,
                    pos_order_line as line,
                    pos_order as po
                where
                    absl.pos_statement_id = po.id and
                    line.order_id=po.id and
                    line.product_id=p.id and
                    absl.statement_id=abs.id

                group by
                    po.user_id,po.sale_journal, abs.journal_id,
                    to_char(date_trunc('day',absl.create_date),'YYYY-MM-DD')::text
                )
        """)
Ejemplo n.º 29
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'op_notas_bimestre_view')
        cr.execute(""" create or replace view op_notas_bimestre_view AS (
                   (select cast((cast(Extract(Year FROM exame.data_exame) as text) || aluno.roll_number || cast(exame.classroom_id as text) || cast(exame.type_id as  text)) as numeric(24,0)) as id, Extract(Year FROM exame.data_exame) as ano, 
            exame.course_id, exame.classroom_id, exame.type_id, exame.partner_id, 
              notas.student_id,  cast(aluno.roll_number as int) as numero
              , (select distinct b1.avaliacao from op_exam_session b1s,  op_exam b1 
                  where b1.session_id = b1s.id and b1s.bimestre = '01' and b1.student_id = notas.student_id  and b1s.course_id = exame.course_id
                    and b1s.classroom_id = exame.classroom_id and b1s.partner_id = exame.partner_id and b1s.type_id = exame.type_id ) as bim1 
              , (select distinct b1.avaliacao from op_exam_session b1s,  op_exam b1 
                  where b1.session_id = b1s.id and b1s.bimestre = '02' and b1.student_id = notas.student_id  and b1s.course_id = exame.course_id
                    and b1s.classroom_id = exame.classroom_id and b1s.partner_id = exame.partner_id and b1s.type_id = exame.type_id ) as bim2
              , (select distinct b1.avaliacao from op_exam_session b1s,  op_exam b1 
                  where b1.session_id = b1s.id and b1s.bimestre = '03' and b1.student_id = notas.student_id  and b1s.course_id = exame.course_id
                    and b1s.classroom_id = exame.classroom_id and b1s.partner_id = exame.partner_id and b1s.type_id = exame.type_id ) as bim3
              , (select distinct b1.avaliacao from op_exam_session b1s,  op_exam b1 
                  where b1.session_id = b1s.id and b1s.bimestre = '04' and b1.student_id = notas.student_id  and b1s.course_id = exame.course_id
                    and b1s.classroom_id = exame.classroom_id and b1s.partner_id = exame.partner_id and b1s.type_id = exame.type_id ) as bim4
                    
              from op_exam_session exame, op_exam notas, op_student aluno  
             where exame.id = notas.session_id
               and aluno.id = notas.student_id 
               and exame.type_id is not null 
               and exame.classroom_id is not null
               and exame.course_id is not null 
               and exame.partner_id is not null 
               and (Extract(Year FROM exame.data_exame) > 2014)

               group by exame.course_id, exame.classroom_id, exame.partner_id, 
              notas.student_id, ano, aluno.roll_number
              ,exame.type_id 
              order by cast(aluno.roll_number as int)
            ))
        """)
Ejemplo n.º 30
0
 def init(self, cr):
     """
         CRM Lead Report
         @param cr: the current row, from the database cursor
     """
     tools.drop_view_if_exists(cr, 'crm_partner_report_assign')
     cr.execute("""
         CREATE OR REPLACE VIEW crm_partner_report_assign AS (
             SELECT
                 coalesce(i.id, p.id - 1000000000) as id,
                 p.id as partner_id,
                 (SELECT country_id FROM res_partner a WHERE a.parent_id=p.id AND country_id is not null limit 1) as country_id,
                 p.grade_id,
                 p.activation,
                 p.date_review,
                 p.date_partnership,
                 p.user_id,
                 p.team_id,
                 (SELECT count(id) FROM crm_lead WHERE partner_assigned_id=p.id) AS opp,
                 i.price_total as turnover,
                 i.period_id
             FROM
                 res_partner p
                 left join account_invoice_report i
                     on (i.partner_id=p.id and i.type in ('out_invoice','out_refund') and i.state in ('open','paid'))
         )""")
Ejemplo n.º 31
0
    def init(self, cr):

        """
            CRM Lead Report
            @param cr: the current row, from the database cursor
        """
        tools.drop_view_if_exists(cr, 'crm_lead_report')
        cr.execute("""
            CREATE OR REPLACE VIEW crm_lead_report AS (
                SELECT
                    id,
                    c.date_deadline,

                    to_char(c.date_open, 'YYYY-MM-DD') as opening_date,
                    to_char(c.date_closed, 'YYYY-mm-dd') as date_closed,

                    date_trunc('day',c.date_last_stage_update) as date_last_stage_update,

                    c.user_id,
                    c.probability,
                    c.stage_id,
                    c.type,
                    c.company_id,
                    c.priority,
                    c.section_id,
                    c.channel_id,
                    c.type_id,
                    c.partner_id,
                    c.country_id,
                    c.planned_revenue,
                    c.planned_revenue*(c.probability/100) as probable_revenue,
                    date_trunc('day',c.create_date) as create_date,
                    extract('epoch' from (c.date_closed-c.create_date))/(3600*24) as  delay_close,
                    abs(extract('epoch' from (c.date_deadline - c.date_closed))/(3600*24)) as  delay_expected,
                    extract('epoch' from (c.date_open-c.create_date))/(3600*24) as  delay_open
                FROM
                    crm_lead c
                WHERE c.active = 'true'
            )""")
Ejemplo n.º 32
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'grp_anticipos_report')
        cr.execute("""
            CREATE OR replace VIEW grp_anticipos_report AS (
            SELECT 
	sa.id AS id,
	sa.id AS solicitud_anticipo_id,
	af.id AS anticipo_fondo_id,
	ra.id AS rendicion_anticipo_id,
	da.id AS devolucion_anticipo_id,
	sa.name AS sa_nro_solicitud,
	sa.user_uid AS sa_user_uid,
	sa.fecha_ingreso AS sa_fecha_ingreso,
	sa.aprobador_id AS sa_aprobador_id,
	sa.operating_unit_id AS sa_operating_unit_id,
	sa.currency_id AS sa_currency_id,
	sa.state AS sa_state,
	(SELECT SUM(amount) FROM grp_informacion_solicitud sal WHERE sal.solicitud_id = sa.id) AS sa_amount_total,
	af.number AS af_number,
	af.fecha_aprobacion_pago AS af_fecha_aprobacion_pago,
	af.date AS af_date,
	af.state AS af_state,
	ra.x_sequence AS ra_sequence,
	ra.entry_date AS ra_entry_date,
	ra.name AS ra_name,
	ra.amount AS ra_amount,
	ra.amount - ra.adelanto_anticipo AS ra_diferencia,
	ra.state AS ra_state,
	da.number AS da_number,
	da.fecha_aprobacion_pago AS da_fecha_aprobacion_pago,
	da.date AS da_date,
	da.state AS da_state
FROM
	grp_solicitud_anticipos_fondos sa LEFT JOIN
	(SELECT * FROM account_voucher WHERE type = 'payment') af ON af.solicitud_anticipos_id = sa.id LEFT JOIN
	hr_expense_expense ra ON ra.solicitud_anticipos_id = sa.id LEFT JOIN
	(SELECT * FROM account_voucher WHERE type = 'sale') da ON da.solicitud_anticipos_id = sa.id AND da.rendicion_anticipos_id = ra.id
            )
        """)
Ejemplo n.º 33
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'crm_opportunity_report')
        cr.execute("""
            CREATE OR REPLACE VIEW crm_opportunity_report AS (
                SELECT
                    id,
                    c.date_deadline,
                    count(id) as nbr_cases,

                    c.date_open as opening_date,
                    c.date_closed as date_closed,

                    c.date_last_stage_update as date_last_stage_update,

                    c.user_id,
                    c.probability,
                    c.stage_id,
                    c.type,
                    c.company_id,
                    c.priority,
                    c.team_id,
                    c.campaign_id,
                    c.source_id,
                    c.medium_id,
                    c.partner_id,
                    c.country_id,
                    c.planned_revenue as total_revenue,
                    c.planned_revenue*(c.probability/100) as expected_revenue,
                    c.create_date as create_date,
                    extract('epoch' from (c.date_closed-c.create_date))/(3600*24) as  delay_close,
                    abs(extract('epoch' from (c.date_deadline - c.date_closed))/(3600*24)) as  delay_expected,
                    extract('epoch' from (c.date_open-c.create_date))/(3600*24) as  delay_open,
                    c.lost_reason,
                    c.date_conversion as date_conversion
                FROM
                    crm_lead c
                WHERE c.active = 'true'
                GROUP BY c.id
            )""")
Ejemplo n.º 34
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_account_sales')
     cr.execute("""create or replace view report_account_sales as (
         select
            min(inv_line.id) as id,
            to_char(inv.date_invoice, 'YYYY') as name,
            to_char(inv.date_invoice,'MM') as month,
            sum(inv_line.price_subtotal) as amount_total,
            inv.currency_id as currency_id,
            inv.period_id,
            inv_line.product_id,
            sum(inv_line.quantity) as quantity,
            account.id as account_id
         from
             account_invoice_line inv_line
         inner join account_invoice inv on inv.id = inv_line.invoice_id
         inner join account_account account on account.id = inv_line.account_id
         where
             inv.state in ('open','paid')
         group by
             to_char(inv.date_invoice, 'YYYY'),to_char(inv.date_invoice,'MM'),inv.currency_id, inv.period_id, inv_line.product_id, account.id
         )""")
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_stock_lot_change_location')
     cr.execute("""
             CREATE OR REPLACE view report_stock_lot_change_location AS (
             SELECT
                     min(quant.id) as id,
                     sum(quant.qty) as qty,
                     quant.lot_id as lot_id,
                     quant.product_id as product_id,
                     quant.location_id as location_id,
                     'change_location' as operation,
                     max(quant.in_date) as in_date
             FROM
                     stock_quant quant
                     INNER JOIN stock_location location ON (quant.location_id = location.id)
             WHERE   quant.lot_id is not Null
             AND     location.usage = 'internal'
             GROUP BY quant.lot_id, quant.product_id, quant.location_id
             HAVING     sum(quant.qty) > 0
             ORDER BY quant.product_id
            )
     """)
Ejemplo n.º 36
0
    def init(self, cr):
        try:
            with cr.savepoint():
                cr.execute("CREATE EXTENSION tablefunc")
        except psycopg2.Error:
            pass
        # View
        tools.drop_view_if_exists(cr, self._table)
        _sql = """
            select aml.id, aml.id as move_line_id,
                aml.date_maturity, aml.date, aml.partner_id,
                aa.type account_type, new_title,
                case when letter.l1 is not null then true else false end as l1,
                letter.l1 l1_date,
                case when letter.l2 is not null then true else false end as l2,
                letter.l2 l2_date,
                case when letter.l3 is not null then true else false end as l3,
                letter.l3 l3_date
            from account_move_line aml
            join account_account aa on aa.id = aml.account_id
            join res_partner rp on rp.id = aml.partner_id
            left outer join pabi_dunning_config_title pdct
                on rp.title = pdct.title_id
            -- Crosstab table
            left outer join (
                select move_line_id, l1, l2, l3
                from crosstab('
                select move_line_id, letter_type, date_run
                from pabi_partner_dunning_letter_line order by 1, 2
                ') AS final_result(
                    move_line_id integer, l1 date, l2 date, l3 date)
            )as letter on letter.move_line_id = aml.id
            where aml.state = 'valid' and aa.type in ('receivable', 'payable')
            and aml.date_maturity is not null
            and aml.partner_id is not null
        """

        cr.execute("""CREATE or REPLACE VIEW %s as (%s)""" %
                   (self._table, _sql,))
Ejemplo n.º 37
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'buy_summary_partner')
        cr.execute("""
            CREATE OR REPLACE VIEW buy_summary_partner AS (

            SELECT MIN(wml.id) AS id,
               MIN(wm.date) AS date,
               s_categ.name AS s_category,
               partner.name AS partner,
               goods.code AS goods_code,
               goods.name AS goods,
               attr.name AS attribute,
               wh.name AS warehouse_dest,
               uos.name AS uos,
               SUM(wml.goods_uos_qty) AS qty_uos,
               uom.name AS uom,
               SUM(wml.goods_qty) AS qty,
               SUM(wml.amount) / SUM(wml.goods_qty) AS price,
               SUM(wml.amount) AS amount,
               SUM(wml.tax_amount) AS tax_amount,
               SUM(wml.subtotal) AS subtotal
            
            FROM wh_move_line AS wml
            LEFT JOIN wh_move wm ON wml.move_id = wm.id
            LEFT JOIN partner ON wm.partner_id = partner.id
            LEFT JOIN core_category AS s_categ ON partner.s_category_id = s_categ.id
            LEFT JOIN goods ON wml.goods_id = goods.id
            LEFT JOIN attribute AS attr ON wml.attribute_id = attr.id
            LEFT JOIN warehouse AS wh ON wml.warehouse_dest_id = wh.id
            LEFT JOIN uom AS uos ON goods.uos_id = uos.id
            LEFT JOIN uom ON goods.uom_id = uom.id
            
            WHERE wml.state = 'done'
            AND wm.origin like 'buy%'
            GROUP BY s_category,partner,goods_code,goods,attribute,warehouse_dest,uos,uom
            ORDER BY partner,goods,attribute,warehouse_dest
            )
        """)
Ejemplo n.º 38
0
    def init(self, cr):
        """
            Display Deadline ,Responsible user, partner ,Department
            @param cr: the current row, from the database cursor
        """

        tools.drop_view_if_exists(cr, 'crm_helpdesk_report')
        cr.execute("""
            create or replace view crm_helpdesk_report as (
                select
                    min(c.id) as id,
                    to_char(c.date, 'YYYY') as name,
                    to_char(c.date, 'MM') as month,
                    to_char(c.date, 'YYYY-MM-DD') as day,
                    to_char(c.create_date, 'YYYY-MM-DD') as create_date,
                    to_char(c.date_closed, 'YYYY-mm-dd') as date_closed,
                    c.state,
                    c.user_id,
                    c.section_id,
                    c.partner_id,
                    c.company_id,
                    c.priority,
                    c.date_deadline,
                    c.categ_id,
                    c.channel_id,
                    c.planned_cost,
                    count(*) as nbr,
                    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 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 to_char(c.date, 'YYYY'), to_char(c.date, 'MM'),to_char(c.date, 'YYYY-MM-DD'),\
                     c.state, c.user_id,c.section_id,c.priority,\
                     c.partner_id,c.company_id,c.date_deadline,c.create_date,c.date,c.date_closed,\
                     c.categ_id,c.channel_id,c.planned_cost,c.id
            )""")
Ejemplo n.º 39
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'ineco_sale_audit_delivery')
     cr.execute("""
     CREATE OR REPLACE VIEW ineco_sale_audit_delivery AS
         select 
           sp.id,
           garment_order_no,
           amount_untaxed,
           (select coalesce(sum(amount_untaxed),0.00) from account_invoice 
            where account_invoice.garment_order_no = so.garment_order_no and
              type = 'out_invoice' and
              state not in ('cancel')) as invoice_amount_untaxed,
           sp.name,
           sp.batch_no,
           (select sum(product_uom_qty) from sale_order_line
            join product_product pp on pp.id = sale_order_line.product_id
            join product_template pt on pt.id = pp.product_tmpl_id
            where order_id = so.id and pt.type not in ('service')
           ) as sale_quantity,
           case 
             when sp.type = 'in' then -sp.quantity
             when sp.type = 'out' then sp.quantity
             else 
               sp.quantity
           end as delivery_quantity
         from 
           sale_order so
           left join stock_picking sp on sp.sale_id = so.id
         where 
           extract(year from garment_order_date) >= 2015
           and so.state not in ('cancel')
           and sp.type not in ('internal')
           and sp.invoice_state = '2binvoiced'
           and so.amount_untaxed > 0
         order by
            so.garment_order_no,
            sp.batch_no
     """)
Ejemplo n.º 40
0
 def init(self, cr):
     """
         CRM Lead Report
         @param cr: the current row, from the database cursor
     """
     tools.drop_view_if_exists(cr, 'crm_lead_report_assign')
     cr.execute("""
         CREATE OR REPLACE VIEW crm_lead_report_assign AS (
             SELECT
                 c.id,
                 to_char(c.create_date, 'YYYY-MM-DD') as creation_date,
                 to_char(c.date_open, 'YYYY-MM-DD') as opening_date,
                 to_char(c.date_closed, 'YYYY-mm-dd') as date_closed,
                 c.date_assign,
                 c.user_id,
                 c.probability,
                 c.probability as probability_max,
                 c.stage_id,
                 c.type,
                 c.company_id,
                 c.priority,
                 c.section_id,
                 c.partner_id,
                 c.country_id,
                 c.planned_revenue,
                 c.partner_assigned_id,
                 p.grade_id,
                 p.date as partner_date,
                 c.planned_revenue*(c.probability/100) as probable_revenue, 
                 1 as nbr,
                 date_trunc('day',c.create_date) as create_date,
                 extract('epoch' from (c.write_date-c.create_date))/(3600*24) as  delay_close,
                 extract('epoch' from (c.date_deadline - c.date_closed))/(3600*24) as  delay_expected,
                 extract('epoch' from (c.date_open-c.create_date))/(3600*24) as  delay_open
             FROM
                 crm_lead c
                 left join res_partner p on (c.partner_assigned_id=p.id)
         )""")
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'is_sale_order_line')
        cr.execute("""
            CREATE OR REPLACE view is_sale_order_line AS (
                select  sol.id,
                        sol.is_date_expedition  as date_expedition,
                        sol.is_date_livraison   as date_livraison,
                        sol.is_client_order_ref as client_order_ref,
                        so.partner_id           as partner_id, 
                        pt.id                   as product_id, 
                        pt.segment_id           as segment_id,
                        pt.is_category_id       as is_category_id,
                        pt.is_gestionnaire_id   as is_gestionnaire_id,
                        pt.is_mold_id           as mold_id,
                        pt.is_ref_client        as ref_client,
                        sol.product_uom_qty     as product_uom_qty,
                        sol.product_uom         as product_uom,
                        sol.price_unit          as price_unit,
                        (sol.price_unit*sol.product_uom_qty) as price_subtotal,

                        get_amortissement_moule_a_date(rp.is_code, pt.id, sol.is_date_expedition) as amortissement_moule,
                        get_amt_interne_a_date(rp.is_code, pt.id, sol.is_date_expedition)  as amt_interne,
                        get_cagnotage_a_date(rp.is_code, pt.id, sol.is_date_expedition)    as cagnotage,

                        get_amortissement_moule_a_date(rp.is_code, pt.id, sol.is_date_expedition)*sol.product_uom_qty as montant_amt_moule,
                        get_amt_interne_a_date(rp.is_code, pt.id, sol.is_date_expedition)*sol.product_uom_qty         as montant_amt_interne,
                        get_cagnotage_a_date(rp.is_code, pt.id, sol.is_date_expedition)*sol.product_uom_qty           as montant_cagnotage,

                        get_cout_act_matiere_st(pp.id)*sol.product_uom_qty as montant_matiere,
                        so.id                   as order_id,
                        sol.id                  as order_line_id,
                        so.state                as state
                from sale_order so    inner join sale_order_line     sol on so.id=sol.order_id
                                      inner join product_product      pp on sol.product_id=pp.id
                                      inner join product_template     pt on pp.product_tmpl_id=pt.id
                                      inner join res_partner          rp on so.partner_id=rp.id
            )
        """)
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'asset_asset_report')
     cr.execute("""
 	    create or replace view asset_asset_report as (
             select 
                 min(dl.id) as id,
                 dl.name as name,
                 dl.depreciation_date as depreciation_date,
                 a.purchase_date as purchase_date,
                 (CASE WHEN (select min(d.id) from account_asset_depreciation_line as d
                             left join account_asset_asset as ac ON (ac.id=d.asset_id)
                             where a.id=ac.id) = min(dl.id)
                   THEN a.purchase_value
                   ELSE 0
                   END) as gross_value,
                 dl.amount as depreciation_value, 
                 (CASE WHEN dl.move_check
                   THEN dl.amount
                   ELSE 0
                   END) as posted_value,
                 (CASE WHEN NOT dl.move_check
                   THEN dl.amount
                   ELSE 0
                   END) as unposted_value,
                 dl.asset_id as asset_id,
                 dl.move_check as move_check,
                 a.category_id as asset_category_id,
                 a.partner_id as partner_id,
                 a.state as state,
                 count(dl.*) as nbr,
                 a.company_id as company_id
             from account_asset_depreciation_line dl
                 left join account_asset_asset a on (dl.asset_id=a.id)
             group by 
                 dl.amount,dl.asset_id,dl.depreciation_date,dl.name,
                 a.purchase_date, dl.move_check, a.state, a.category_id, a.partner_id, a.company_id,
                 a.purchase_value, a.id, a.salvage_value
     )""")
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'hr_timesheet_report')
     cr.execute("""
         create or replace view hr_timesheet_report as (
             select
                 min(t.id) as id,
                 l.date as date,
                 to_char(l.date, 'YYYY-MM-DD') as day,
                 to_char(l.date,'YYYY') as year,
                 to_char(l.date,'MM') as month,
                 sum(l.amount) as cost,
                 sum(l.unit_amount) as quantity,
                 l.account_id as account_id,
                 a.project_analytic_account_id as
                 project_analytic_account_id,
                 l.journal_id as journal_id,
                 l.product_id as product_id,
                 l.general_account_id as general_account_id,
                 l.user_id as user_id,
                 l.company_id as company_id,
                 l.currency_id as currency_id
             from
                 hr_analytic_timesheet as t
                 left join account_analytic_line as l ON (t.line_id=l.id)
                 left join account_analytic_account a ON
                 (l.account_id = a.id)
             group by
                 l.date,
                 l.account_id,
                 a.project_analytic_account_id,
                 l.product_id,
                 l.general_account_id,
                 l.journal_id,
                 l.user_id,
                 l.company_id,
                 l.currency_id
         )
     """)
 def init(self, cr):
     tools.drop_view_if_exists(cr, self._table)
     cr.execute("""CREATE or REPLACE VIEW %s as (
         select row_number() over (order by rpt.chart_view,
                                            rpt.fiscalyear_id) as id,
                rpt.chart_view, rpt.fiscalyear_id,
                coalesce(plan, 0.0) as plan,
                (select coalesce(sum(policy_amount), 0.0)
                 from budget_policy
                 where fiscalyear_id = rpt.fiscalyear_id
                 and chart_view = rpt.chart_view) as policy,
                coalesce(released, 0.0) as released,
                coalesce(pr_commit, 0.0) as pr_commit,
                coalesce(po_commit, 0.0) as po_commit,
                coalesce(exp_commit, 0.0) as exp_commit,
                coalesce(actual_total, 0.0) as actual_total,
                coalesce(balance, 0.0) as balance,
                coalesce(total, 0.0) as commit_and_actual,
                case when total = 0 then 0
                 else (actual_total/total) * 100 end as actual_percent,
                coalesce(pr_commit+po_commit+exp_commit, 0.0)
                 as commit_total,
                case when total = 0 then 0 else
                 coalesce(pr_commit+po_commit+exp_commit, 0.0)/total * 100
                 end as commit_percent
         from
                (select chart_view, fiscalyear_id,
                        sum(planned_amount) as plan,
                        sum(released_amount) as released,
                        sum(amount_pr_commit) as pr_commit,
                        sum(amount_po_commit) as po_commit,
                        sum(amount_exp_commit) as exp_commit,
                        sum(amount_actual) as actual_total,
                        sum(amount_balance) as balance,
                        sum(amount_consumed) as total
                 from budget_monitor_report
                 where budget_method = 'expense'
                 group by chart_view, fiscalyear_id) rpt)""" % self._table)
Ejemplo n.º 45
0
    def init(self, cr):

        """
            CRM Lead Report
            @param cr: the current row, from the database cursor
        """
        tools.drop_view_if_exists(cr, 'ineco_crm_dashboard_2')
        cr.execute("""
            CREATE OR REPLACE VIEW ineco_crm_dashboard_2 AS (
                select
                  date_part('day', cl.date_deadline) as day,
                  date_part('month', cl.date_deadline) as month,
                  date_part('year', cl.date_deadline) as year,
                  rp_ru.id as saleman_id,
                  ccs.id as stage_id,
                  sum(cl.planned_revenue) as planned_revenue,
                  cl.state
                from crm_lead cl
                left join res_users ru on cl.user_id = ru.id
                left join res_partner rp_ru on ru.id = rp_ru.id
                left join crm_case_stage ccs on cl.stage_id = ccs.id
                where date_part('year', cl.date_deadline) = date_part('year', now())
                and ccs.id = 6
                group by
                  EXTRACT(day FROM cl.date_deadline),
                  EXTRACT(MONTH FROM cl.date_deadline),
                  EXTRACT(year FROM cl.date_deadline),
                  rp_ru.id,
                  ccs.id,
                  cl.state
                order by
                  rp_ru.id,
                  ccs.id,
                  date_part('year', cl.date_deadline),
                  date_part('month', cl.date_deadline),
                  date_part('day', cl.date_deadline),
                  cl.state
            )""")
Ejemplo n.º 46
0
    def init(self, cr):
        """ Display Number of cases And Section Name
        @param cr: the current row, from the database cursor,
         """

        tools.drop_view_if_exists(cr, 'crm_claim_report')
        cr.execute("""
            create or replace view crm_claim_report as (
                select
                    min(c.id) as id,
                    c.date as claim_date,
                    c.date_closed as date_closed,
                    c.date_deadline as date_deadline,
                    c.user_id,
                    c.stage_id,
                    c.section_id,
                    c.partner_id,
                    c.company_id,
                    c.categ_id,
                    c.sub_categ_id,
                    c.name as subject,
                    count(*) as nbr,
                    c.priority as priority,
                    c.type_action as type_action,
                    c.create_date as create_date,
                    c.eq_costs,
                    c.eq_waste_parts,
                    c.eq_good_parts,
                    avg(extract('epoch' from (c.date_closed-c.create_date)))/(3600*24) as  delay_close,
                    (SELECT count(id) FROM mail_message WHERE model='crm.claim' AND res_id=c.id) AS email,
                    extract('epoch' from (c.date_deadline - c.date_closed))/(3600*24) as  delay_expected
                from
                    crm_claim c
                group by c.date,\
                        c.user_id,c.section_id, c.stage_id,\
                        c.categ_id, c.sub_categ_id,c.partner_id,c.company_id,c.create_date,
                        c.priority,c.type_action,c.date_deadline,c.date_closed,c.id
            )""")
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'is_comparatif_cde_draft_done')
     cr.execute("""
         CREATE OR REPLACE view is_comparatif_cde_draft_done AS (
             select
                 sol2.id,
                 pt.id                  product_id,
                 sol2.is_date_livraison date_livraison,
                 so.id                  order_id,
                 sol2.qty_done          qt_commande, 
                 sol2.qty_draft         qt_livree
             from (
                 select
                     sol.id,
                     sol.product_id,
                     sol.is_date_livraison,
                     sol.order_id, 
                     sol.product_uom_qty qty_done, 
                     (
                         select sol2.product_uom_qty
                         from sale_order_line sol2
                         where 
                             sol2.is_type_commande='ferme' and 
                             sol2.state='draft' and
                             sol2.product_id=sol.product_id and
                             sol2.is_date_livraison=sol.is_date_livraison
                         limit 1
                     ) qty_draft
                 from sale_order_line sol
                 where is_type_commande='ferme' and state='done'
                 order by product_id
             ) sol2 inner join product_product  pp on sol2.product_id=pp.id
                    inner join product_template pt on pp.product_tmpl_id=pt.id 
                    inner join sale_order       so on sol2.order_id=so.id
             where sol2.qty_draft>0
             order by pt.is_code, sol2.is_date_livraison
         )
     """)
Ejemplo n.º 48
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'annexure_2b_report')
        cr.execute("""CREATE OR REPLACE view annexure_2b_report AS (
            select
                row_number() OVER () AS id,
                ai.date_invoice AS date,
                ai.partner_id as partner_id,
                rp.tin_no as tin_no,
                ait.invoice_id as invoice_id,
                ait.account_id as account_id,
                ait.base as base,
                ait.amount as amount,
                ait.base_code_id as base_code_id,
                ait.base_amount as base_amount,
                ait.tax_code_id as tax_code_id,
                ait.tax_amount as tax_amount

            FROM 
                account_invoice_line ail
                LEFT JOIN account_invoice ai ON (ail.invoice_id = ai.id)
                LEFT JOIN account_invoice_tax ait ON (ait.invoice_id = ai.id)
                LEFT JOIN res_partner rp ON (ai.partner_id = rp.id)

                WHERE ai.type = 'out_invoice'
                GROUP BY ail.id,
                ai.date_invoice,
                ai.partner_id,
                rp.tin_no,
                ail.account_id,
                ait.invoice_id,
                ait.account_id,
                ait.base,
                ait.amount,
                ait.base_amount,
                ait.tax_amount,
                ait.base_code_id,
                ait.tax_code_id
                )""")
Ejemplo n.º 49
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_pos_order')
     cr.execute("""
         create or replace view report_pos_order as (
             select
                 min(l.id) as id,
                 count(*) as nbr,
                 to_date(to_char(s.date_order, 'dd-MM-YYYY'),'dd-MM-YYYY') as date,
                 sum(l.qty * u.factor) as product_qty,
                 sum(l.qty * l.price_unit) as total_wo_disc,
                 sum(l.qty * l.price_unit * (100.0-l.discount) / 100.0) as price_total,
                 sum((l.qty * l.price_unit) * (l.discount / 100)) as total_discount,
                 (sum(l.qty*l.price_unit)/sum(l.qty * u.factor))::decimal(16,2) as average_price,
                 sum(cast(to_char(date_trunc('day',s.date_order) - date_trunc('day',s.create_date),'DD') as int)) as delay_validation,
                 to_char(s.date_order, 'YYYY') as year,
                 to_char(s.date_order, 'MM') as month,
                 to_char(s.date_order, 'YYYY-MM-DD') as day,
                 s.partner_id as partner_id,
                 s.state as state,
                 s.user_id as user_id,
                 s.shop_id as shop_id,
                 s.company_id as company_id,
                 s.sale_journal as journal_id,
                 l.product_id as product_id,
                 s.section_id as section_id,
                 s.event_id as event_id
             from pos_order_line as l
                 left join pos_order s on (s.id=l.order_id)
                 left join product_product p on (l.product_id=p.id)
                 left join product_template pt on (p.product_tmpl_id=pt.id)
                 left join product_uom u on (u.id=pt.uom_id)
             group by
                 to_char(s.date_order, 'dd-MM-YYYY'),to_char(s.date_order, 'YYYY'),to_char(s.date_order, 'MM'),
                 to_char(s.date_order, 'YYYY-MM-DD'), s.partner_id,s.state,
                 s.user_id,s.shop_id,s.company_id,s.sale_journal,l.product_id,s.create_date,
                 s.section_id, s.event_id
             having
                 sum(l.qty * u.factor) != 0)""")
Ejemplo n.º 50
0
 def init(self, cr):
     drop_view_if_exists(cr, "account_query_payable_aging")
     strSQL = """
                 CREATE OR REPLACE VIEW account_query_payable_aging AS (
                     SELECT
                             a.id AS id,
                             a.name AS name,
                             a.move_id AS move_id,
                             a.account_id AS account_id,
                             b.company_id AS company_id,
                             b.date as date,
                             a.date_maturity as date_due,
                             b.journal_id AS journal_id,
                             a.partner_id AS partner_id,
                             b.period_id AS period_id,
                             CASE
                                 WHEN a.currency_id IS NULL
                                 THEN e.currency_id
                                 ELSE a.currency_id
                             END AS respective_currency_id,
                             e.currency_id AS base_currency_id,
                             a.reconcile_id AS reconcile_id,
                             a.reconcile_partial_id AS reconcile_partial_id,
                             a.debit AS debit,
                             a.credit AS credit,
                             a.amount_currency AS amount_currency,
                             b.state AS state
                     FROM account_move_line AS a
                     JOIN account_move AS b ON a.move_id = b.id
                     JOIN account_journal AS c ON b.journal_id = c.id
                     JOIN account_account AS d ON a.account_id = d.id
                     JOIN res_company AS e ON b.company_id = e.id
                     WHERE   (d.type = 'payable') AND
                             (b.state = 'posted') AND
                             (a.credit > 0)
                 )
                 """
     cr.execute(strSQL)
 def init(self, cr):
     drop_view_if_exists(cr, 'account_query_receivable_aging')
     strSQL = """
                 CREATE OR REPLACE VIEW account_query_receivable_aging AS (
                     SELECT
                             A.id AS id,
                             A.name AS name,
                             A.move_id AS move_id,
                             A.account_id AS account_id,
                             B.company_id AS company_id,
                             B.date as date,
                             A.date_maturity as date_due,
                             B.journal_id AS journal_id,
                             A.partner_id AS partner_id,
                             B.period_id AS period_id,
                             CASE
                                 WHEN A.currency_id IS NULL
                                 THEN E.currency_id
                                 ELSE A.currency_id
                             END AS respective_currency_id,
                             E.currency_id AS base_currency_id,
                             A.reconcile_id AS reconcile_id,
                             A.reconcile_partial_id AS reconcile_partial_id,
                             A.debit AS debit,
                             A.credit AS credit,
                             A.amount_currency AS amount_currency,
                             B.state AS state
                     FROM account_move_line AS A
                     JOIN account_move AS B ON A.move_id = B.id
                     JOIN account_journal C ON B.journal_id = C.id
                     JOIN account_account D ON A.account_id = D.id
                     JOIN res_company E ON B.company_id = E.id
                     WHERE   (D.type = 'receivable') AND
                             (B.state = 'posted') AND
                             (A.debit > 0)
                 )
                 """
     cr.execute(strSQL)
Ejemplo n.º 52
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'od_hr_document_history')
        cr.execute("""
            create or replace view od_hr_document_history as (
                select ROW_NUMBER () OVER (ORDER BY  od_holiday_document_line.id ) AS id,
document_type_id as document_type_id,recieved_date as process_date,hr_holidays.employee_id as employee_id,'issue' as doc_action,'Leave' as action_type
from od_holiday_document_line  
join hr_holidays on hr_holidays.id = od_holiday_document_line.holiday_id
join od_employee_document_type on od_employee_document_type.id = od_holiday_document_line.document_type_id
where recieved <> 't' and issued = 't' and od_employee_document_type.custodian = 'company'

UNION
select ROW_NUMBER () OVER (ORDER BY  od_holiday_document_line.id ) AS id, document_type_id,issued_date as process_date,hr_holidays.employee_id,'received' as doc_action,'Leave' as action_type
from od_holiday_document_line  
join hr_holidays on hr_holidays.id = od_holiday_document_line.holiday_id
join od_employee_document_type on od_employee_document_type.id = od_holiday_document_line.document_type_id
where recieved = 't' and od_employee_document_type.custodian = 'company'

UNION
select ROW_NUMBER () OVER (ORDER BY  od_document_request.id ) AS id, document_type_id,return_date as process_date,employee_id,'received' as doc_action,'Document' as action_type
from od_document_request 
join od_employee_document_type on od_employee_document_type.id = od_document_request.document_type_id
where is_returned ='t' and od_employee_document_type.custodian = 'company'

UNION
select ROW_NUMBER () OVER (ORDER BY  od_document_request.id ) AS id, document_type_id,issued_date as process_date,employee_id,'issue' as doc_action,'Document' as action_type
from od_document_request 
join od_employee_document_type on od_employee_document_type.id = od_document_request.document_type_id
where is_returned <> 't' and is_issued = 't' and od_employee_document_type.custodian = 'company'

UNION
select ROW_NUMBER () OVER (ORDER BY  od_employee_joining_document_line.id ) AS id, document_type_id,recieved_date as process_date,od_employee_joining.employee_id,'received' as doc_action,'Joining' as action_type
FROM  od_employee_joining_document_line 
join od_employee_joining on od_employee_joining_document_line.joining_id =od_employee_joining.id 
join od_employee_document_type on od_employee_document_type.id = od_employee_joining_document_line.document_type_id
where recieved = 't' and od_employee_document_type.custodian = 'company'
              )
               """)
Ejemplo n.º 53
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'academic_evaluation_analysis')
        cr.execute("""
            create or replace view academic_evaluation_analysis as (
SELECT
        survey_user_input_question_score.id as id,
        survey_user_input.score as avg_score, 
        survey_user_input.score as min_score, 
        survey_user_input.score as max_score, 
        survey_user_input.survey_id as survey_id,
        survey_user_input.partner_id as partner_id,
        survey_user_input.state as input_state,
        academic_observation_category.dont_consider as dont_consider,
        academic_group_evaluation.group_id as group_id,
        academic_group.level_id as course_level_id,
        academic_group_evaluation.state as group_evaluation_state,
        academic_group.company_id,
        res_partner.disabled_person as disabled_person,
        survey_survey.is_evaluation as is_evaluation,
        survey_survey.stage_id as survey_stage_id,
        survey_survey.period_id as period_id        
    FROM survey_user_input_question_score
    INNER JOIN survey_user_input
    on survey_user_input_question_score.user_input_id = survey_user_input.id
    INNER JOIN survey_survey
    on survey_user_input.survey_id = survey_survey.id
    FULL JOIN academic_group_evaluation
        on survey_user_input.group_evaluation_id = academic_group_evaluation.id  
    FULL JOIN academic_group
        on academic_group_evaluation.group_id = academic_group.id  
    LEFT JOIN academic_observation_category
    on survey_user_input.observation_category_id = academic_observation_category.id
    INNER JOIN res_partner
    on survey_user_input.partner_id = res_partner.id
    LEFT JOIN academic_level
    on academic_group.level_id = academic_level.id    
        )
        """)
 def init(self, cr):
     """ Event Question main report """
     tools.drop_view_if_exists(cr, 'event_question_report')
     cr.execute(""" CREATE VIEW event_question_report AS (
         SELECT
             att_answer.id as id,
             att_answer.event_registration_id as attendee_id,
             answer.question_id as question_id,
             answer.id as answer_id,
             question.event_id as event_id
         FROM
             event_registration_answer as att_answer
         LEFT JOIN
             event_answer as answer ON answer.id = att_answer.event_answer_id
         LEFT JOIN
             event_question as question ON question.id = answer.question_id
         GROUP BY
             attendee_id,
             event_id,
             question_id,
             answer_id,
             att_answer.id
     )""")
Ejemplo n.º 55
0
 def init(self, cr):
     # self._table = account_invoice_report
     tools.drop_view_if_exists(cr, self._table)
     cr.execute("""CREATE or REPLACE VIEW %s as (
         WITH currency_rate (currency_id, rate, date_start, date_end) AS (
             SELECT r.currency_id, 1/r.rate, r.name AS date_start,
                 (SELECT name FROM res_currency_rate r2
                  WHERE r2.name > r.name AND
                        r2.currency_id = r.currency_id
                  ORDER BY r2.name ASC
                  LIMIT 1) AS date_end
             FROM res_currency_rate r
         )
         %s
         FROM (
             %s %s %s
         ) AS sub
         JOIN currency_rate cr ON
             (cr.currency_id = sub.currency_id AND
              cr.date_start <= COALESCE(sub.date, NOW()) AND
              (cr.date_end IS NULL OR cr.date_end > COALESCE(sub.date, NOW())))
     )""" % (self._table, self._select(), self._sub_select(), self._from(),
             self._group_by()))
Ejemplo n.º 56
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'mrp_workorder')
     cr.execute("""
         create or replace view mrp_workorder as (
             select
                 to_date(to_char(wl.date_planned, 'MM-dd-YYYY'),'MM-dd-YYYY') as date,
                 min(wl.id) as id,
                 mp.product_id as product_id,
                 sum(wl.hour) as total_hours,
                 avg(wl.delay) as delay,
                 (w.costs_hour*sum(wl.hour)) as total_cost,
                 wl.production_id as production_id,
                 wl.workcenter_id as workcenter_id,
                 sum(wl.cycle) as total_cycles,
                 count(*) as nbr,
                 sum(mp.product_qty) as product_qty,
                 wl.state as state
             from mrp_production_workcenter_line wl
                 left join mrp_workcenter w on (w.id = wl.workcenter_id)
                 left join mrp_production mp on (mp.id = wl.production_id)
             group by
                 w.costs_hour, mp.product_id, mp.name, wl.state, wl.date_planned, wl.production_id, wl.workcenter_id
     )""")
Ejemplo n.º 57
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'grp_paid_consult')
     cr.execute("""
         CREATE OR replace VIEW grp_paid_consult AS (
             SELECT
                 avl.id AS id,
                 av.id AS voucher_id,
                 avl.id AS voucher_line_id,
                 av.journal_id AS journal_id,
                 aj.operating_unit_id AS voucher_ue_id,
                 ai.id AS invoice_id,
                 ai.operating_unit_id AS invoice_ue_id
                 FROM account_voucher AS av
                 INNER JOIN  account_voucher_line avl ON avl.voucher_id = av.id
                 INNER JOIN account_move_line aml ON avl.move_line_id = aml.id
                 INNER JOIN account_move am ON aml.move_id = am.id
                 INNER JOIN  account_invoice ai ON ai.move_id = am.id
                 INNER JOIN account_journal aj ON av.journal_id = aj.id
                 WHERE av.state = 'posted' AND av."type" = 'payment'
                 AND aj.operating_unit_id <> ai.operating_unit_id AND avl.amount > 0
                 ORDER BY voucher_id
         )
     """)
Ejemplo n.º 58
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_analytic_account_close')
     cr.execute("""
         create or replace view report_analytic_account_close as (
             select
                 a.id as id,
                 a.id as name,
                 a.state as state,
                 sum(l.unit_amount) as quantity,
                 sum(l.amount) as balance,
                 a.partner_id as partner_id,
                 a.quantity_max as quantity_max,
                 a.date as date_deadline
             from
                 account_analytic_line l
             right join
                 account_analytic_account a on (l.account_id=a.id)
             group by
                 a.id,a.state, a.quantity_max,a.date,a.partner_id
             having
                 (a.quantity_max>0 and (sum(l.unit_amount)>=a.quantity_max)) or
                 a.date <= current_date
         )""")
Ejemplo n.º 59
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, self._table)
     cr.execute("""CREATE or REPLACE VIEW %s as (
     select min(ct.id) id,
         c.state,
         ct.cert_id,
         c.income_tax_form,
         ap.name as wht_period,
         c.date as date_value,
         round(sum(ct.amount)/sum(ct.base)*100) as tax_percent,
         ct.wht_cert_income_type, ct.wht_cert_income_desc,
         case when c.state != 'cancel'
             then sum(ct.base) else 0.0 end as base,
         case when c.state != 'cancel'
             then sum(ct.amount) else 0.0 end as tax
     from account_wht_cert c join wht_cert_tax_line ct
         on ct.cert_id = c.id
         left outer join account_period ap on ap.id = c.period_id
     where c.state != 'draft'
     group by c.period_id, c.income_tax_form, ct.cert_id,
         ap.name, c.date, ct.wht_cert_income_type,
         ct.wht_cert_income_desc, c.state
     )""" % (self._table, ))
Ejemplo n.º 60
0
    def init(self, cr):
        """Initialize the sql view for the OpenAcademy """
        tools.drop_view_if_exists(cr, 'report_openacademy')

        # TOFIX this request won't select events that have no registration
        cr.execute(""" CREATE VIEW report_openacademy AS (
            SELECT
                s.id::varchar || '/' || coalesce(a.id::varchar,'') AS id,
                c.id AS course_id,
                c.responsible_id AS responsible_id,
                s.instructor_id AS instructor_id,
                s.name AS name_session,
                s.start_date AS start_date,
                s.seats AS seats,
                s.duration AS duration,
                a.partner_id AS partner_id,
                a.id AS attendee_id
            FROM
                openacademy_session s
                JOIN openacademy_attendee a ON (a.session_id=s.id)
                LEFT JOIN openacademy_course c ON (s.course_id=c.id)
        )
        """)