def init(self):
     tools.drop_view_if_exists(self.env.cr, self._table)
     # We disable the check for SQL injection. The only risk of sql
     # injection is from 'self._table' which is not controlled by an
     # external source.
     # pylint:disable=E8103
     self.env.cr.execute("""
         CREATE OR REPLACE VIEW %s AS
         SELECT c.id, c.end_date, c.end_reason, c.sub_sponsorship_id,
                c.sds_state, p.id as partner_id, %s, %s, %s,
                p.lang, 100/s.sponsored as active_percentage,
                100.0/s.sponsored_terminated as total_percentage,
                s.sponsored_terminated,
                s.study_date
         FROM recurring_contract c JOIN res_partner p
           ON c.correspondent_id = p.id
           %s
         WHERE c.state = 'terminated' AND c.child_id IS NOT NULL
         AND c.end_date IS NOT NULL
     """ % (self._table,
            self._select_fiscal_year('c.end_date'),
            self._select_category(),
            self._select_sub_category(),
            self._join_stats())
     )
Пример #2
0
 def init(self):
     tools.drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
         SELECT
             %s
         FROM hr_employee emp
     )""" % (self._table, self._get_fields()))
Пример #3
0
    def init(self):
        cr = self._cr
        tools.drop_view_if_exists(cr, 'report_lot_status')
        cr.execute(
            """
            create or replace view report_lot_status as (
                SELECT MIN(line.id) as id,
                        goods.name as goods,
                        uom.name as uom,
                        uos.name as uos,
                        line.lot as lot,
                        line.attribute_id as attribute_id,
                        '在库' as status,
                        wh.name as warehouse,
                        max(line.date) as date,
                        sum(line.qty_remaining) as qty,
                        sum(line.uos_qty_remaining) as uos_qty

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

                WHERE line.lot IS NOT NULL
                  AND line.qty_remaining > 0
                  AND wh.type = 'stock'
                  AND line.state = 'done'

                GROUP BY goods, uom, uos, lot, attribute_id, warehouse

                ORDER BY goods, lot, warehouse
            )
        """)
Пример #4
0
    def init(self):
        tools.drop_view_if_exists(self._cr, self._table)

        self._cr.execute(""" CREATE VIEW %s 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
        )
        """ % (self._table,))
Пример #5
0
 def init(self):
     """
         CRM Lead Report
         @param cr: the current row, from the database cursor
     """
     tools.drop_view_if_exists(self._cr, 'crm_partner_report_assign')
     self._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 nbr_opportunities,
                 i.price_total as turnover,
                 i.date
             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','in_payment','paid'))
         )""")
Пример #6
0
 def init(self):
     drop_view_if_exists(self._cr, 'report_mrp_inout')
     self._cr.execute("""
         create view report_mrp_inout as (
             select
                 min(sm.id) as id,
                 to_char(sm.date,'YYYY:IW') as date,
                 sum(case when (sl.usage='internal') then
                     sm.price_unit * sm.product_qty
                 else
                     0.0
                 end - case when (sl2.usage='internal') then
                     sm.price_unit * sm.product_qty
                 else
                     0.0
                 end) as value,
                 sm.company_id
             from
                 stock_move sm
             left join product_product pp
                 on (pp.id = sm.product_id)
             left join product_template pt
                 on (pt.id = pp.product_tmpl_id)
             left join stock_location sl
                 on ( sl.id = sm.location_id)
             left join stock_location sl2
                 on ( sl2.id = sm.location_dest_id)
             where
                 sm.state = 'done'
             group by
                 to_char(sm.date,'YYYY:IW'), sm.company_id
         )""")
Пример #7
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,
                    c.date_open as opening_date,
                    c.create_date as create_date,
                    c.date_last_stage_update as date_last_stage_update,
                    c.user_id,
                    c.working_hours_open,
                    c.working_hours_close,
                    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,
                    1 as nbr_issues,
                    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
                LEFT JOIN project_task t on c.task_id = t.id
                WHERE c.active= 'true'
            )""")
Пример #8
0
 def init(self):
     tools.drop_view_if_exists(self.env.cr, 'campaign_analysis')
     self.env.cr.execute("""
         CREATE OR REPLACE VIEW campaign_analysis AS (
         SELECT
             min(wi.id) AS id,
             min(wi.res_id) AS res_id,
             to_char(wi.date::date, 'YYYY') AS year,
             to_char(wi.date::date, 'MM') AS month,
             to_char(wi.date::date, 'YYYY-MM-DD') AS day,
             wi.date::date AS date,
             s.campaign_id AS campaign_id,
             wi.activity_id AS activity_id,
             wi.segment_id AS segment_id,
             wi.partner_id AS partner_id ,
             wi.state AS state,
             sum(act.revenue) AS revenue,
             count(*) AS count
         FROM
             marketing_campaign_workitem wi
             LEFT JOIN res_partner p ON (p.id=wi.partner_id)
             LEFT JOIN marketing_campaign_segment s ON (s.id=wi.segment_id)
             LEFT JOIN marketing_campaign_activity act ON (act.id= wi.activity_id)
         GROUP BY
             s.campaign_id,wi.activity_id,wi.segment_id,wi.partner_id,wi.state,
             wi.date::date
         )
     """)
Пример #9
0
 def init(self):
     tools.drop_view_if_exists(self._cr, 'crm_activity_report')
     self._cr.execute("""
         CREATE VIEW crm_activity_report AS (
             select
                 m.id,
                 m.subtype_id,
                 m.mail_activity_type_id,
                 m.author_id,
                 m.date,
                 m.subject,
                 l.id as lead_id,
                 l.user_id,
                 l.team_id,
                 l.country_id,
                 l.company_id,
                 l.stage_id,
                 l.partner_id,
                 l.type as lead_type,
                 l.active,
                 l.probability
             from
                 "mail_message" m
             join
                 "crm_lead" l
             on
                 (m.res_id = l.id)
             WHERE
                 (m.model = 'crm.lead' AND m.mail_activity_type_id IS NOT NULL)
         )""")
Пример #10
0
 def init(self):
     """Mass Mail Statistical Report: based on mail.mail.statistics that models the various
     statistics collected for each mailing, and mail.mass_mailing model that models the
     various mailing performed. """
     tools.drop_view_if_exists(self.env.cr, 'mail_statistics_report')
     self.env.cr.execute("""
         CREATE OR REPLACE VIEW mail_statistics_report AS (
             SELECT
                 min(ms.id) as id,
                 ms.scheduled as scheduled_date,
                 utm_source.name as name,
                 utm_campaign.name as campaign,
                 count(ms.bounced) as bounced,
                 count(ms.sent) as sent,
                 (count(ms.sent) - count(ms.bounced)) as delivered,
                 count(ms.opened) as opened,
                 count(ms.replied) as replied,
                 count(ms.clicked) as clicked,
                 mm.state,
                 mm.email_from
             FROM
                 mail_mail_statistics as ms
                 left join mail_mass_mailing as mm ON (ms.mass_mailing_id=mm.id)
                 left join mail_mass_mailing_campaign as mc ON (ms.mass_mailing_campaign_id=mc.id)
                 left join utm_campaign as utm_campaign ON (mc.campaign_id = utm_campaign.id)
                 left join utm_source as utm_source ON (mm.source_id = utm_source.id)
             GROUP BY ms.scheduled, utm_source.name, utm_campaign.name, mm.state, mm.email_from
         )""")
Пример #11
0
    def init(self):
        tools.drop_view_if_exists(self._cr, self._table)

        self._cr.execute("""
            CREATE or REPLACE view %s as (
                SELECT
                    lc.id as id,
                    lc.amount as amount,
                    lc.date as date,
                    lc.currency_id as currency_id,
                    lc.user_id as user_id,
                    lc.description as description
                FROM lunch_cashmove lc
                UNION ALL
                SELECT
                    -lol.id as id,
                    -lol.price as amount,
                    lol.date as date,
                    lol.currency_id as currency_id,
                    lol.user_id as user_id,
                    format('Order for %%s x %%s %%s', lol.quantity::text, lp.name, lol.display_toppings) as description
                FROM lunch_order lol
                JOIN lunch_product lp ON lp.id = lol.product_id
                WHERE
                    lol.state in ('ordered', 'confirmed')
                    AND lol.active = True
            );
        """ % self._table)
Пример #12
0
    def init(self):
        cr = 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,
                       goods.id as goods_id,
                       line.lot as lot,
                       attribute.name as attribute_id,
                       uom.name as uom,
                       uos.name as uos,
                       wh.name as warehouse,
                       sum(line.qty_remaining) as goods_qty,
                       sum(line.uos_qty_remaining) as goods_uos_qty,
                       sum(line.qty_remaining * line.cost_unit) 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 attribute attribute on attribute.id = line.attribute_id
                    LEFT JOIN uom uom ON goods.uom_id = uom.id
                    LEFT JOIN uom uos ON goods.uos_id = uos.id

                WHERE  wh.type = 'stock'
                  AND line.state = 'done'
                  AND ( goods.no_stock is null or goods.no_stock = FALSE)

                GROUP BY wh.name, line.lot, attribute.name, goods.name, goods.id, uom.name, uos.name

                ORDER BY goods.name, wh.name, goods_qty asc
            )
        """)
Пример #13
0
 def init(self):
     # 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(self.env.cr, 'im_livechat_report_channel')
     self.env.cr.execute("""
         CREATE OR REPLACE VIEW im_livechat_report_channel AS (
             SELECT
                 C.id as id,
                 C.uuid as uuid,
                 C.id as channel_id,
                 C.name as channel_name,
                 CONCAT(L.name, ' / ', C.id) as technical_name,
                 C.livechat_channel_id as livechat_channel_id,
                 C.create_date as start_date,
                 to_char(date_trunc('hour', C.create_date), 'YYYY-MM-DD HH24:MI:SS') as start_date_hour,
                 EXTRACT('epoch' FROM (max((SELECT (max(M.create_date)) FROM mail_message M JOIN mail_message_mail_channel_rel R ON (R.mail_message_id = M.id) WHERE R.mail_channel_id = C.id))-C.create_date)) as duration,
                 count(distinct P.id) as nbr_speaker,
                 count(distinct M.id) as nbr_message,
                 S.partner_id as partner_id
             FROM mail_channel C
                 JOIN mail_message_mail_channel_rel R ON (C.id = R.mail_channel_id)
                 JOIN mail_message M ON (M.id = R.mail_message_id)
                 JOIN mail_channel_partner S ON (S.channel_id = C.id)
                 JOIN im_livechat_channel L ON (L.id = C.livechat_channel_id)
                 JOIN res_partner P ON (S.partner_id = p.id)
                 JOIN res_users U ON (U.partner_id = P.id)
                 JOIN im_livechat_channel_im_user O ON (O.user_id = U.id and O.channel_id = L.id)
             GROUP BY C.id, C.name, C.livechat_channel_id, L.name, C.create_date, C.uuid, S.partner_id
         )
     """)
Пример #14
0
 def init(self):
     # 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(self.env.cr, 'im_livechat_report_channel')
     self.env.cr.execute("""
         CREATE OR REPLACE VIEW im_livechat_report_channel AS (
             SELECT
                 C.id as id,
                 C.uuid as uuid,
                 C.id as channel_id,
                 C.name as channel_name,
                 CONCAT(L.name, ' / ', C.id) as technical_name,
                 C.livechat_channel_id as livechat_channel_id,
                 C.create_date as start_date,
                 to_char(date_trunc('hour', C.create_date), 'YYYY-MM-DD HH24:MI:SS') as start_date_hour,
                 to_char(date_trunc('hour', C.create_date), 'HH24') as start_hour,
                 extract(dow from  C.create_date) as day_number, 
                 EXTRACT('epoch' FROM MAX(M.create_date) - MIN(M.create_date)) AS duration,
                 EXTRACT('epoch' FROM MIN(MO.create_date) - MIN(M.create_date)) AS time_to_answer,
                 count(distinct C.livechat_operator_id) as nbr_speaker,
                 count(distinct M.id) as nbr_message,
                 CASE 
                     WHEN EXISTS (select distinct M.author_id FROM mail_message M, mail_message_mail_channel_rel R 
                                     WHERE M.author_id=C.livechat_operator_id AND R.mail_channel_id = C.id 
                                     AND R.mail_message_id = M.id and C.livechat_operator_id = M.author_id)
                     THEN 0
                     ELSE 1
                 END as is_without_answer,
                 (DATE_PART('day', date_trunc('day', now()) - date_trunc('day', C.create_date)) + 1) as days_of_activity,
                 CASE
                     WHEN C.anonymous_name IS NULL THEN 0
                     ELSE 1
                 END as is_anonymous,
                 C.country_id,
                 CASE 
                     WHEN rate.rating = 10 THEN 1
                     ELSE 0
                 END as is_happy,
                 Rate.rating as rating,
                 CASE
                     WHEN Rate.rating = 1 THEN 'Unhappy'
                     WHEN Rate.rating = 10 THEN 'Happy'
                     WHEN Rate.rating = 5 THEN 'Neutral'
                     ELSE null
                 END as rating_text,
                 CASE 
                     WHEN rate.rating > 0 THEN 0
                     ELSE 1
                 END as is_unrated,
                 C.livechat_operator_id as partner_id
             FROM mail_channel C
                 JOIN mail_message_mail_channel_rel R ON (C.id = R.mail_channel_id)
                 JOIN mail_message M ON (M.id = R.mail_message_id)
                 JOIN im_livechat_channel L ON (L.id = C.livechat_channel_id)
                 LEFT JOIN mail_message MO ON (R.mail_message_id = MO.id AND MO.author_id = C.livechat_operator_id)
                 LEFT JOIN rating_rating Rate ON (Rate.res_id = C.id and Rate.res_model = 'mail.channel' and Rate.parent_res_model = 'im_livechat.channel')
                 WHERE C.livechat_operator_id is not null
             GROUP BY C.livechat_operator_id, C.id, C.name, C.livechat_channel_id, L.name, C.create_date, C.uuid, Rate.rating
         )
     """)
Пример #15
0
 def init(self):
     tools.drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""CREATE or REPLACE VIEW %s AS (
         %s
         FROM (
             %s %s %s
         ) AS sub %s)""" % (self._table, self._select(), self._sub_select(),
             self._from(), self._where(), self._group_by()))
Пример #16
0
 def init(self):
     # self._table = sale_report
     tools.drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
         %s
         FROM ( %s )
         %s
         )""" % (self._table, self._select(), self._from(), self._group_by()))
Пример #17
0
 def init(self):
     # union money_order(type = 'get'), money_invoice(type = 'income')
     cr = self._cr
     tools.drop_view_if_exists(cr, 'customer_statements_report')
     cr.execute("""
         CREATE or REPLACE VIEW customer_statements_report AS (
         SELECT  ROW_NUMBER() OVER(ORDER BY partner_id,done_date) AS id,
                 partner_id,
                 name,
                 date,
                 done_date,
                 sale_amount,
                 benefit_amount,
                 fee,
                 amount,
                 pay_amount,
                 discount_money,
                 balance_amount,
                 0 AS this_balance_amount,
                 note,
                 move_id
         FROM
             (
            SELECT m.partner_id,
                     m.name,
                     m.date,
                     m.write_date AS done_date,
                     0 AS sale_amount,
                     0 AS benefit_amount,
                     0 AS fee,
                     0 AS amount,
                     m.amount AS pay_amount,
                     m.discount_amount as discount_money,
                     0 AS balance_amount,
                     m.note,
                     0 AS move_id
             FROM money_order AS m
             WHERE m.type = 'get' AND m.state = 'done'
             UNION ALL
             SELECT  mi.partner_id,
                     mi.name,
                     mi.date,
                     mi.create_date AS done_date,
                     sd.amount + sd.discount_amount AS sale_amount,
                     sd.discount_amount AS benefit_amount,
                     sd.partner_cost AS fee,
                     mi.amount,
                     0 AS pay_amount,
                     0 as discount_money,
                     0 AS balance_amount,
                     Null AS note,
                     mi.move_id
             FROM money_invoice AS mi
             LEFT JOIN core_category AS c ON mi.category_id = c.id
             LEFT JOIN sell_delivery AS sd ON sd.sell_move_id = mi.move_id
             WHERE c.type = 'income' AND mi.state = 'done'
             ) AS ps)
     """)
 def init(self):
     # union money_order(type = 'pay'), money_invoice(type = 'expense')
     cr = self._cr
     tools.drop_view_if_exists(cr, 'supplier_statements_report')
     cr.execute("""
         CREATE or REPLACE VIEW supplier_statements_report AS (
         SELECT  ROW_NUMBER() OVER(ORDER BY partner_id, date, amount desc) AS id,
                 partner_id,
                 name,
                 date,
                 done_date,
                 amount,
                 pay_amount,
                 discount_money,
                 balance_amount,
                 note
         FROM
             (
             SELECT m.partner_id,
                     m.name,
                     m.date,
                     m.write_date AS done_date,
                     0 AS amount,
                     m.amount AS pay_amount,
                     m.discount_amount AS discount_money,
                     0 AS balance_amount,
                     m.note
             FROM money_order AS m
             WHERE m.type = 'pay' AND m.state = 'done'
             UNION ALL
             SELECT  mi.partner_id,
                     mi.name,
                     mi.date,
                     mi.create_date AS done_date,
                     mi.amount,
                     0 AS pay_amount,
                     0 AS discount_money,
                     0 AS balance_amount,
                     Null AS note
             FROM money_invoice AS mi
             LEFT JOIN core_category AS c ON mi.category_id = c.id
             WHERE c.type = 'expense' AND mi.state = 'done'
             UNION ALL
             SELECT  ro.partner_id,
                     ro.name,
                     ro.date,
                     ro.write_date AS done_date,
                     0 AS amount,
                     sol.this_reconcile AS pay_amount,
                     0 AS discount_money,
                     0 AS balance_amount,
                     Null AS note
             FROM reconcile_order AS ro
             LEFT JOIN money_invoice AS mi ON mi.name = ro.name
             LEFT JOIN source_order_line AS sol ON sol.payable_reconcile_id = ro.id
             WHERE ro.state = 'done' AND mi.state = 'done' AND mi.name ilike 'RO%'
             ) AS ps)
     """)
Пример #19
0
 def init(self):
     tools.drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""CREATE VIEW %s AS (
         %s
         %s
         %s
         %s
         %s
     )""" % (self._table, self._select(), self._from(), self._join(), self._where(), self._group_by()))
Пример #20
0
 def init(self):
     tools.drop_view_if_exists(self._cr, self._table)
     self._cr.execute("""
         CREATE view %s as
           %s
           FROM project_task t
             WHERE t.active = 'true'
             %s
     """ % (self._table, self._select(), self._group_by()))
 def init(self):
     tools.drop_view_if_exists(self._cr, self._table)
     query = """
         CREATE VIEW %s AS (
             WITH currency_rate as (%s)
             SELECT
                 ROW_NUMBER() OVER (ORDER BY P.id, SOL.id) AS id,
                 P.user_id AS user_id,
                 TIMESHEET_AMOUNT.project_id AS project_id,
                 TIMESHEET_AMOUNT.sale_line_id AS sale_line_id,
                 AA.id AS analytic_account_id,
                 AA.partner_id AS partner_id,
                 C.id AS company_id,
                 C.currency_id AS currency_id,
                 S.id AS sale_order_id,
                 S.date_order AS order_confirmation_date,
                 SOL.product_id AS product_id,
                 (SOL.price_reduce / COALESCE(CR.rate, 1.0)) * SOL.qty_to_invoice AS amount_untaxed_to_invoice,
                 (SOL.price_reduce / COALESCE(CR.rate, 1.0)) * SOL.qty_invoiced AS amount_untaxed_invoiced,
                 TIMESHEET_AMOUNT.timesheet_unit_amount AS timesheet_unit_amount,
                 TIMESHEET_AMOUNT.timesheet_cost AS timesheet_cost
             FROM project_project P
                 LEFT JOIN account_analytic_account AA ON P.analytic_account_id = AA.id
                 JOIN res_company C ON C.id = AA.company_id
                 LEFT JOIN
                     (
                         SELECT T.sale_line_id AS sale_line_id, T.project_id AS project_id
                         FROM project_task T
                         WHERE T.sale_line_id IS NOT NULL
                         UNION
                         SELECT P.sale_line_id AS sale_line_id, P.id AS project_id
                         FROM project_project P
                         WHERE P.sale_line_id IS NOT NULL
                     ) SOL_PER_PROJECT ON P.id = SOL_PER_PROJECT.project_id
                 RIGHT OUTER JOIN
                     (
                         SELECT
                             project_id AS project_id,
                             so_line AS sale_line_id,
                             array_agg(id),
                             SUM(TS.unit_amount) AS timesheet_unit_amount,
                             SUM(TS.amount) AS timesheet_cost
                         FROM account_analytic_line TS
                         WHERE project_id IS NOT NULL
                         GROUP BY project_id, so_line
                     ) TIMESHEET_AMOUNT ON TIMESHEET_AMOUNT.project_id = SOL_PER_PROJECT.project_id AND TIMESHEET_AMOUNT.sale_line_id = SOL_PER_PROJECT.sale_line_id
                 LEFT JOIN sale_order_line SOL ON SOL_PER_PROJECT.sale_line_id = SOL.id
                 LEFT JOIN sale_order S ON SOL.order_id = S.id
                 LEFT JOIN currency_rate CR ON (CR.currency_id = SOL.currency_id
                     AND CR.currency_id != C.currency_id
                     AND CR.company_id = SOL.company_id
                     AND CR.date_start <= COALESCE(S.date_order, now())
                     AND (CR.date_end IS NULL OR cr.date_end > COALESCE(S.date_order, now())))
         )
     """ % (self._table, self.env['res.currency']._select_companies_rates())
     self._cr.execute(query)
Пример #22
0
 def init(self):
     # union money_order(type = 'pay'), money_invoice(type = 'expense')
     cr = self._cr
     tools.drop_view_if_exists(cr, 'supplier_statements_report')
     cr.execute("""
         CREATE or REPLACE VIEW supplier_statements_report AS (
         SELECT  ROW_NUMBER() OVER(ORDER BY partner_id,done_date) AS id,
                 partner_id,
                 name,
                 date,
                 done_date,
                 purchase_amount,
                 benefit_amount,
                 amount,
                 pay_amount,
                 discount_money,
                 balance_amount,
                 0 AS this_balance_amount,
                 note,
                 move_id
         FROM
             (
             SELECT m.partner_id,
                     m.name,
                     m.date,
                     m.write_date AS done_date,
                     0 AS purchase_amount,
                     0 AS benefit_amount,
                     0 AS amount,
                     m.amount AS pay_amount,
                     m.discount_amount AS discount_money,
                     0 AS balance_amount,
                     m.note,
                     NULL AS move_id
             FROM money_order AS m
             WHERE m.type = 'pay' AND m.state = 'done'
             UNION ALL
             SELECT  mi.partner_id,
                     mi.name,
                     mi.date,
                     mi.create_date AS done_date,
                     br.amount + br.discount_amount AS purchase_amount,
                     br.discount_amount AS benefit_amount,
                     mi.amount,
                     0 AS pay_amount,
                     0 AS discount_money,
                     0 AS balance_amount,
                     Null AS note,
                     mi.move_id
             FROM money_invoice AS mi
             LEFT JOIN core_category AS c ON mi.category_id = c.id
             LEFT JOIN buy_receipt AS br ON br.buy_move_id = mi.move_id
             WHERE c.type = 'expense' AND mi.state = 'done'
             ) AS ps)
     """)
Пример #23
0
 def init(self):
     tools.drop_view_if_exists(self._cr, self._table)
     self._cr.execute("""
         CREATE OR REPLACE VIEW %s AS (
             %s
             %s
             %s
             %s
         )
     """ % (self._table, self._select(), self._from(), self._group_by(),self._having())
     )
Пример #24
0
    def init(self):
        cr = self._cr
        tools.drop_view_if_exists(cr, 'sell_order_detail')
        cr.execute("""
            CREATE or REPLACE VIEW sell_order_detail AS (
                SELECT  MIN(wml.id) AS id,
                    wm.date AS date,
                    wm.name AS order_name,
                    (CASE WHEN wm.origin = 'sell.delivery.sell' THEN '销货'
                    ELSE '退货' END) AS type,
                    wm.user_id AS user_id,
                    wm.partner_id AS partner_id,
                    goods.code AS goods_code,
                    goods.id AS goods_id,
                    attr.name AS attribute,
                    wh.id AS warehouse_id,
                    SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.goods_qty
                        ELSE - wml.goods_qty END) AS qty,
                    uom.name AS uom,
                    wml.price AS price,
                    SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.amount
                        ELSE - wml.amount END) AS amount,
                    SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.tax_amount
                        ELSE - wml.tax_amount END) AS tax_amount,
                    SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.subtotal
                        ELSE - wml.subtotal END) AS subtotal,
                    SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.goods_qty
                        ELSE - wml.goods_qty END) * (wml.price - wml.cost_unit) AS margin,
                    (CASE WHEN wm.origin = 'sell.delivery.sell' THEN sd.money_state
                    ELSE sd.return_state END) AS money_state,
                    wml.note AS note,
                    mi.get_amount_date AS last_receipt_date

                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 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_id = wh.id
                         OR wml.warehouse_dest_id = wh.id
                    LEFT JOIN uom ON goods.uom_id = uom.id
                    LEFT JOIN sell_delivery AS sd ON wm.id = sd.sell_move_id
                    LEFT JOIN money_invoice AS mi ON mi.id = sd.invoice_id

                WHERE wml.state = 'done'
                  AND wm.origin like 'sell.delivery%%'
                  AND wh.type = 'stock'

                GROUP BY wm.date, wm.name, origin, wm.user_id, wm.partner_id,
                    goods_code, goods.id, attribute, wh.id, uom,
                    wml.price, wml.cost_unit, sd.money_state, sd.return_state, wml.note,
                    mi.get_amount_date
                )
        """)
 def init(self):
     # self._table = construction_reduced_vat_agreement_report
     tools.drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
         SELECT inv.id, agg.agreement_code, cust.matricule, s_addr.zip, inv.date_invoice as date, inv.move_name as number, inv.amount_untaxed, inv.amount_tax, inv.company_id
         , to_char(date_trunc('quarter', current_date)::date - 1, 'yyyy-q') = to_char(inv.date_invoice, 'yyyy-q') as last_quarter
         , to_char(current_date, 'yyyy-q') = to_char(inv.date_invoice, 'yyyy-q') as current_quarter            
         , agg.active
         , inv.partner_id
         FROM account_invoice inv, res_partner cust, construction_reduced_vat_agreement agg, construction_building_asset ba, construction_building_site bs, res_partner s_addr
         WHERE inv.partner_id = cust.id AND inv.reduced_vat_agreement_id = agg.id AND inv.building_asset_id = ba.id AND ba.site_id = bs.id AND bs.address_id = s_addr.id
     )""" % (self._table))
Пример #26
0
 def init(self):
     tools.drop_view_if_exists(self._cr, 'report_pos_order')
     self._cr.execute("""
         CREATE OR REPLACE VIEW report_pos_order AS (
             SELECT
                 MIN(l.id) AS id,
                 COUNT(*) AS nbr_lines,
                 s.date_order AS date,
                 SUM(l.qty * u.factor) AS product_qty,
                 SUM(l.qty * l.price_unit) AS price_sub_total,
                 SUM((l.qty * l.price_unit) * (100 - l.discount) / 100) 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 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.id as order_id,
                 s.partner_id AS partner_id,
                 s.state AS state,
                 s.user_id AS user_id,
                 s.location_id AS location_id,
                 s.company_id AS company_id,
                 s.sale_journal AS journal_id,
                 l.product_id AS product_id,
                 pt.categ_id AS product_categ_id,
                 p.product_tmpl_id,
                 ps.config_id,
                 pt.pos_categ_id,
                 pc.stock_location_id,
                 s.pricelist_id,
                 s.session_id,
                 s.invoice_id IS NOT NULL AS invoiced
             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)
                 LEFT JOIN pos_session ps ON (s.session_id=ps.id)
                 LEFT JOIN pos_config pc ON (ps.config_id=pc.id)
             GROUP BY
                 s.id, s.date_order, s.partner_id,s.state, pt.categ_id,
                 s.user_id, s.location_id, s.company_id, s.sale_journal,
                 s.pricelist_id, s.invoice_id, s.create_date, s.session_id,
                 l.product_id,
                 pt.categ_id, pt.pos_categ_id,
                 p.product_tmpl_id,
                 ps.config_id,
                 pc.stock_location_id
             HAVING
                 SUM(l.qty * u.factor) != 0
         )
     """)
 def init(self):
     tools.drop_view_if_exists(self._cr, 'stock_lot_revaluations')
     self._cr.execute("""
         CREATE VIEW stock_lot_revaluations AS (
             select
                 l.id,
                 r.id as revaluation_id,
                 l.quant_id,
                 l.old_cost,
                 l.new_cost,
                 r.post_date
             from stock_inventory_revaluation as r
             left join stock_inventory_revaluation_quant as l on l.revaluation_id=r.id
         )
     """)
Пример #28
0
 def init(self):
     tools.drop_view_if_exists(self._cr, 'hr_recruitment_report')
     self._cr.execute("""
         create or replace view hr_recruitment_report as (
              select
                  min(s.id) as id,
                  s.active,
                  s.create_date as date_create,
                  date(s.date_closed) as date_closed,
                  s.date_last_stage_update as date_last_stage_update,
                  s.partner_id,
                  s.company_id,
                  s.user_id,
                  s.job_id,
                  s.type_id,
                  s.department_id,
                  s.priority,
                  s.stage_id,
                  s.last_stage_id,
                  s.medium_id,
                  s.source_id,
                  sum(salary_proposed) as salary_prop,
                  (sum(salary_proposed)/count(*)) as salary_prop_avg,
                  sum(salary_expected) as salary_exp,
                  (sum(salary_expected)/count(*)) as salary_exp_avg,
                  extract('epoch' from (s.write_date-s.create_date))/(3600*24) as delay_close,
                  count(*) as nbr
              from hr_applicant s
              group by
                  s.active,
                  s.date_open,
                  s.create_date,
                  s.write_date,
                  s.date_closed,
                  s.date_last_stage_update,
                  s.partner_id,
                  s.company_id,
                  s.user_id,
                  s.stage_id,
                  s.last_stage_id,
                  s.type_id,
                  s.priority,
                  s.job_id,
                  s.department_id,
                  s.medium_id,
                  s.source_id
         )
     """)
Пример #29
0
 def init(self):
     drop_view_if_exists(self._cr, 'report_workcenter_load')
     self._cr.execute("""
         create view report_workcenter_load as (
             SELECT
                 min(wl.id) as id,
                 to_char(p.date_planned_start,'YYYY:mm:dd') as name,
                 SUM(wl.duration_expected) AS duration,
                 wl.workcenter_id as workcenter_id
             FROM
                 mrp_workorder wl
                 LEFT JOIN mrp_production p
                     ON p.id = wl.production_id
             GROUP BY
                 wl.workcenter_id,
                 to_char(p.date_planned_start,'YYYY:mm:dd')
         )""")
Пример #30
0
 def init(self):
     # self._table = account_invoice_report
     tools.drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
         WITH currency_rate AS (%s)
         %s
         FROM (
             %s %s WHERE ail.account_id IS NOT NULL %s
         ) AS sub
         LEFT JOIN currency_rate cr ON
             (cr.currency_id = sub.currency_id AND
              cr.company_id = sub.company_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.env['res.currency']._select_companies_rates(),
                 self._select(), self._sub_select(), self._from(), self._group_by()))
Пример #31
0
 def init(self):
     tools.drop_view_if_exists(self._cr, 'crm_alima_dashboard_deux')
     self._cr.execute("""
         CREATE VIEW crm_alima_dashboard_deux AS (
             SELECT
                 public.crm_alima_donateur.id as id,
                 1 as nombre,
                 public.crm_alima_don."montantEur" as montant,
                 date_part('year', crm_alima_donateur."datePremierDon") as anneedonateur,
                 date_part('year', crm_alima_don."date") as annedon,
                 (CASE WHEN date_part('year', crm_alima_don."date") = date_part('year', crm_alima_donateur."datePremierDon")
                 THEN 'nouveaux donateurs' ELSE 'anciens donateurs' END) as type,
                 crm_alima_don.mode_versement,
                 crm_alima_donateur.type_de_personne
             FROM
                 public.crm_alima_don,
                 public.crm_alima_donateur
             where
                 public.crm_alima_donateur.id = public.crm_alima_don.donateur
             ORDER BY
                 date_part('year', crm_alima_don."date")
         )""")
 def init(self):
     tools.drop_view_if_exists(self.env.cr, 'forecast_sale_ept_report')
     self.env.cr.execute(
         """ CREATE or REPLACE VIEW forecast_sale_ept_report as 
         (
             select 
                 min(sale.id) as id,
                 sale.product_id as product_id,
                 sale.sku as sku,
                 sale.period_id as period_id, 
                 sale.warehouse_id as warehouse_id,
                 sum (sale.forecast_sales)  as forecast_sales,
                 fp.date_start as date,
                 fp.fiscalyear_id as year_id
             from forecast_sale_ept sale 
                 join requisition_period_ept fp on fp.id = sale.period_id
             group by 
                 product_id,
                 sku,
                 period_id,
                 warehouse_id,date,year_id
         )""")
Пример #33
0
 def init(self):
     tools.drop_view_if_exists(self._cr, 'kw_report_one')
     self._cr.execute("""
     CREATE VIEW kw_report_one AS (
     SELECT 
         SUBJECT.id AS id,
         SUBJECT.subject_name AS employee_subject,
         EMPLOYEE.name AS employee_name,
         SUBJECT.subject_mark AS employee_marks
     FROM 
         hr_employee AS EMPLOYEE,
         kw_college AS COLLEGE,
         kw_stream AS STREAM,
         kw_semester AS SEMESTER,
         kw_subject AS SUBJECT
     WHERE 
         EMPLOYEE.emp_college_id = COLLEGE.id
     AND COLLEGE.id = STREAM.stream_college_id
     AND STREAM.id = SEMESTER.semester_stream_id
     AND SEMESTER.id = SUBJECT.subject_semester_id
     )
     """)
Пример #34
0
    def init_table(self):
        tools.drop_view_if_exists(self._cr, self._name.replace(".", "_"))

        select_query = """ 
                    SELECT ROW_NUMBER () OVER (ORDER BY move_id) as id, concat(so.name,' - ', t.name) as name, 
                    so.id as order_id, m.id as move_id, so.user_id, t.sku_code,
                    m.partner_id as partner_id, m.product_id as product_id, ml.qty_done as done_qty, 
                    (sl.price_unit * ml.qty_done) as cost_price,
                    sl.currency_id as currency_id,
                    DATE(m.date) as moved_date,ml.product_uom_id
                    FROM stock_move m INNER JOIN stock_move_line ml
                    ON m.id = ml.move_id
                    INNER JOIN sale_order_line sl ON sl.id = m.sale_line_id 
                    INNER JOIN sale_order so ON sl.order_id = so.id
                    INNER JOIN product_product p ON p.id = m.product_id
                    INNER JOIN product_template t ON p.product_tmpl_id = t.id
                    WHERE m.sale_line_id IS NOT NULL AND m.origin_returned_move_id IS NOT NULL AND m.state = 'done' 
                    AND m.scrapped IS FALSE """

        sql_query = "CREATE VIEW " + self._name.replace(
            ".", "_") + " AS ( " + select_query + " )"
        self._cr.execute(sql_query)
    def init(self):

        tools.drop_view_if_exists(self.env.cr, self._table)
        self.env.cr.execute(
            """CREATE or REPLACE VIEW %s as (
            WITH currency_rate AS (%s)
            %s
            FROM (
                %s
                FROM %s
                WHERE %s
                GROUP BY %s
            ) AS sub
            LEFT JOIN currency_rate cr ON
                (cr.currency_id = sub.currency_id AND
                 cr.company_id = sub.company_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.env['res.currency']._select_companies_rates(),
             self._select(), self._sub_select(), self._from(), self._where(),
             self._group_by()))
Пример #36
0
 def init(self):
     tools.drop_view_if_exists(self.env.cr, self._table)
     # Reference contract is the one with the latest start_date.
     self.env.cr.execute("""CREATE or REPLACE VIEW %s AS (
         WITH contract_information AS (
             SELECT DISTINCT employee_id,
                             company_id,
                             FIRST_VALUE(id) OVER w_partition AS id,
                             MAX(CASE WHEN state='open' THEN 1 ELSE 0 END) OVER w_partition AS is_under_contract
             FROM   hr_contract AS contract
             WHERE  contract.state <> 'cancel'
             AND contract.active = true
             WINDOW w_partition AS (
                 PARTITION BY contract.employee_id
                 ORDER BY
                     CASE
                         WHEN contract.state = 'open' THEN 0
                         WHEN contract.state = 'draft' THEN 1
                         WHEN contract.state = 'close' THEN 2
                         ELSE 3 END,
                     contract.date_start DESC
                 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
             )
         )
         SELECT     employee.id AS id,
                    employee.id AS employee_id,
                    contract.id AS contract_id,
                    contract_information.is_under_contract::bool AS is_under_contract,
                    employee.first_contract_date AS date_hired,
                    %s
         FROM       hr_contract AS contract
         INNER JOIN contract_information ON contract.id = contract_information.id
         RIGHT JOIN hr_employee AS employee
             ON  contract_information.employee_id = employee.id
             AND contract.company_id = employee.company_id
         WHERE   employee.employee_type IN ('employee', 'student')
         AND     (employee.active = true OR contract.state='draft')
     )""" % (self._table, self._get_fields()))
Пример #37
0
    def init(self):
        """
            Purpose: To extract database information and create tree view using the query
        """
        tools.drop_view_if_exists(self._cr, 'flsp_mrp_negative_inv_report')

        query = """
        CREATE or REPLACE VIEW flsp_mrp_negative_inv_report AS(
        SELECT
            sq.id as id,
            pp.id as product_id, 
            pt.id as product_tmpl_id, 
            --pt.name as pdct_name, 
            pp.default_code as default_code,
            sq.quantity as product_qty, 
            uom.id as product_uom, 
            sq.lot_id as lot, 
            --spl.name as lot_name, 
            sq.location_id as location_id, 
            --sl.name as stck_name, 
            sl.complete_name as location
            --sl.usage as usage_name
            
        FROM stock_quant as sq
            inner 	join product_product as pp
            on 		sq.product_id = pp.id
            inner 	join product_template as pt
            on 		pp.product_tmpl_id = pt.id
            inner 	join uom_uom as uom
            on 		pt.uom_id = uom.id
            full 	join stock_production_lot as spl
            on		sq.lot_id = spl.id 
            inner 	join stock_location as sl
            on 		sq.location_id = sl.id
            where 	sl.usage = 'internal' and sq.quantity < 0
        );
        """
        self.env.cr.execute(query)
Пример #38
0
	def init(self):
		tools.drop_view_if_exists(self._cr, 'margin_report')
		self._cr.execute("""
			CREATE OR REPLACE VIEW public.margin_report AS 
			SELECT 
				row_number() OVER () AS id,
				a.product,
				a.tax_rate,
				a.qty,
				a.cost,
				a.cost_tax,
				a.cost_total,
				a.date,
				a.sale_price,
				a.sale_amount_total,
				a.sale_amount_total - a.cost_total AS margin
			FROM 
				(SELECT 
					pt.name AS product,
					round(pol.tax * 100::numeric / pol.price_unit / pol.qty, 2) AS tax_rate,
					pol.qty,
					pol.cost,
					round(pol.cost * (pol.tax * 100::numeric / pol.price_unit) / pol.qty / 100::numeric * pol.qty, 2) AS cost_tax,
					round(pol.cost * (pol.tax * 100::numeric / pol.price_unit) / pol.qty / 100::numeric * pol.qty + pol.cost * pol.qty, 2) AS cost_total,
					po.date_order AS date,
					pol.price_unit AS sale_price,
					round(pol.price_unit * pol.qty, 2) AS sale_amount_total
				FROM 
					pos_order po,
					pos_order_line pol,
					product_product pp,
					product_template pt
				WHERE
					pp.id = pol.product_id 
					AND pp.product_tmpl_id = pt.id
					AND po.id = pol.order_id) a;

							""")
Пример #39
0
 def init(self):
     tools.drop_view_if_exists(self._cr, 'ancon_commission_report')
     self._cr.execute("""
         CREATE OR REPLACE VIEW ancon_commissions_report AS
         SELECT
             AIL.id AS id,
             AIL.id AS account_invoice_line_id,
             AI.id AS invoice_id,
             AIL.product_id AS product_id,
             AIL.commission_id AS commission_id,
             PT.categ_id AS category_id,
             AI.user_id AS vendor_id,
             AI.payment_term_id AS payment_term_id,
             AIL.name AS product_name,
             PC.name AS category_name,
             AI.number AS invoice_number,
             RP.name AS vendor_name,
             APT.name AS payment_term_name,
             AI.state AS invoice_state,
             AI.date_invoice AS invoice_date,
             AIL.quantity AS quantity,
             AIL.price_unit AS product_price_unit,
             AIL.price_subtotal AS subtotal,
             AIL.price_total AS total,
             AIL.discount AS discount,
             AIL.commission_percentage AS commission_percentage,
             AIL.commission_total AS commission_total
         FROM
             account_invoice_line AIL
             INNER JOIN ancon_commission AC ON AC.id = AIL.commission_id
             INNER JOIN product_product PP ON PP.id = AIL.product_id
             INNER JOIN product_template PT ON PT.id = PP.product_tmpl_id
             INNER JOIN product_category PC ON PC.id = PT.categ_id
             INNER JOIN account_invoice AI ON AI.id = AIL.invoice_id AND AI.state LIKE 'paid'
             INNER JOIN res_users RU ON RU.id = AI.user_id
             INNER JOIN res_partner RP ON RP.id = RU.partner_id
             INNER JOIN account_payment_term APT ON APT.id = AI.payment_term_id"""
                      )
Пример #40
0
 def init(self):
     tools.drop_view_if_exists(self._cr, self._table)
     query = """
         SELECT
             l.id as id,
             l.id as move_line_id,
             am.date as date,
             l.date_maturity as date_maturity,
             am.ref as ref,
             am.state as move_state,
             l.full_reconcile_id as full_reconcile_id,
             -- l.reconcile_partial_id as reconcile_partial_id,
             l.move_id as move_id,
             l.partner_id as partner_id,
             am.company_id as company_id,
             am.journal_id as journal_id,
             -- p.fiscalyear_id as fiscalyear_id,
             -- am.period_id as period_id,
             l.account_id as account_id,
             l.analytic_account_id as analytic_account_id,
             a.internal_type as type,
             a.user_type_id as account_type,
             l.currency_id as currency_id,
             l.amount_currency as amount_currency,
             pa.user_id as user_id,
             coalesce(l.debit, 0.0) - coalesce(l.credit, 0.0) as amount
         FROM
             account_move_line l
             left join account_account a on (l.account_id = a.id)
             left join account_move am on (am.id=l.move_id)
             -- left join account_period p on (am.period_id=p.id)
             left join res_partner pa on (l.partner_id=pa.id)
         WHERE
             -- l.state != 'draft' and
             a.internal_type IN ('payable', 'receivable')
     """
     self._cr.execute("""CREATE or REPLACE VIEW %s as (%s
     )""" % (self._table, query))
Пример #41
0
 def init(self):
     tools.drop_view_if_exists(self._cr,
                               'ancon_daily_sales_payments_report')
     self._cr.execute("""
         CREATE OR REPLACE VIEW ancon_daily_sales_payments_report AS
             select
                 ap.id as id,
                 aj.id as payment_journal_id,
                 aj.name as journal_name,
                 sum(CASE 
                     WHEN am.ref LIKE '%Inversa%'
                     THEN (round(( (((am.amount)::DOUBLE PRECISION))::NUMERIC), 2) * ('-1'::INTEGER)::NUMERIC)
                     ELSE round(( (((am.amount)::DOUBLE PRECISION))::NUMERIC), 2)
                     END) as subtotal,
                 sum(CASE 
                     WHEN am.ref LIKE '%Inversa%'
                     THEN (round(((((am.amount)::DOUBLE PRECISION * aj.x_porcentaje_comision))::NUMERIC), 2) * ('-1'::INTEGER)::NUMERIC)
                     ELSE round(((((am.amount)::DOUBLE PRECISION * aj.x_porcentaje_comision))::NUMERIC), 2)
                     END) as percentage_amount,
                 sum(CASE 
                     WHEN am.ref LIKE '%Inversa%'
                     THEN (round((am.amount - (((am.amount)::DOUBLE PRECISION * aj.x_porcentaje_comision))::NUMERIC), 2) * ('-1'::INTEGER)::NUMERIC)
                     ELSE round((am.amount - (((am.amount)::DOUBLE PRECISION * aj.x_porcentaje_comision))::NUMERIC), 2)
                     END) as payment_amount,
                 am.store_id as store_id,
                 am.date as date_invoice
                 FROM account_move am 
                 LEFT 
                 JOIN account_payment ap ON ap.move_name = am.name
                 LEFT
                 JOIN res_store rs ON rs.id  = ap.store_id
                 LEFT
                 JOIN res_partner rp ON rp.id = am.partner_id
                 LEFT JOIN account_journal aj ON aj.id = am.journal_id
                 where aj.at_least_one_outbound = false
                 group by ap.id ,aj.id, aj.name, am.store_id, am.date
     
     """)
Пример #42
0
 def init(self):
     #self._table = quotation_report
     tools.drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""CREATE or REPLACE VIEW  %s as(
     select  
         row_number() OVER () as id,to_char(to_timestamp(to_char(c.date_deadline_mnth, '999'), 'MM'), 'Mon') as mnth,
         c.date_deadline_year as year,
         c.planned_revenue as topline,
         c.bottom_line_revenue as bottom_line,
         u.id as user_id,
         c.name as oppor_name,
         c.opportunity_stages as oppor_stg,
         c.sales_stages as sale_stage,
         c.partner_id as cust,
         c.crm_pricelist_id as pricelist_id,
         d.oem1 as vendor,
         l.id as lob,ap.id as inside_sales,
         case when so.create_date is null then c.create_date::Date 
         else so.create_date::Date end as quote_date,
         so.name as quote_name
          
         from crm_lead c 
         
         inner join res_users u on u.id=c.user_id
         inner join res_partner r on r.id=c.partner_id 
         inner join product_pricelist p on p.id=c.crm_pricelist_id
         left join sale_order so on so.opportunity_id=c.id
         left join opportunity_stages_info o on o.id=c.opportunity_stages
         left join sales_stages_info s on s.id=c.sales_stages
         left join approval_info ap on ap.id=c.inside_sales
         left join dr_data_info d on c.id=d.crm_order_dr_id
         left join line_of_business_info l on l.id=d.dr_lob where so.state='draft'
         group by 
         mnth,year,topline,bottom_line,u.id,oppor_name,oppor_stg,sale_stage,cust,c.crm_pricelist_id,
         d.oem1,ap.id,quote_date,quote_name,c.id,l.id
         order by quote_date desc
         )
         """ % (self._table))
Пример #43
0
 def init(self):
     tools.drop_view_if_exists(self.env.cr, self._table)
     # We disable the check for SQL injection. The only risk of sql
     # injection is from 'self._table' which is not controlled by an
     # external source.
     # pylint:disable=E8103
     self.env.cr.execute("""
         CREATE OR REPLACE VIEW %s AS
         """ % self._table + """
         -- http://www.postgresqltutorial.com/postgresql-window-function/
         select *,
         case
             when cancellation_id IS NULL THEN true
             ELSE false
         end AS is_active
         from (
             select min(c.id) as acquisition_id,
             max(c.id) as last_sponsorship_id,
             sponsorship_line_id as id,
             max(p.id) as partner_id,
             row_number() over (partition by max(p.id)
                                order by sponsorship_line_id)
                 as sponsor_line
             from recurring_contract c JOIN res_partner p
                 ON c.correspondent_id = p.id
             where sponsorship_line_id is not null
             group by sponsorship_line_id
         ) AS acquisition
         FULL OUTER JOIN (
             select id as cancellation_id, sponsorship_line_id as id
             from recurring_contract
             WHERE child_id IS NOT NULL AND end_date IS NOT NULL
               AND sub_sponsorship_id IS NULL AND
               (activation_date IS NOT NULL OR parent_id IS NOT NULL)
         ) AS cancellation USING(id)
         where id is not null
         order by id
     """)
Пример #44
0
 def init(self):
     tools.drop_view_if_exists(
         self._cr, 'conciliacion_bancaria_disponibilidad_resumen_report')
     self._cr.execute("""
         CREATE OR REPLACE VIEW conciliacion_bancaria_disponibilidad_resumen_report AS (
             select cuenta_id as id,
                 cuenta_id,
                 sum(debe_sin_conciliar) as debe_sin_conciliar,
                 sum(haber_sin_conciliar) as haber_sin_conciliar,
                 sum(saldo_conciliado) as saldo_conciliado,
                 sum(creditos_no_encontrados) as creditos_no_encontrados,
                 sum(debitos_no_encontrados) as debitos_no_encontrados,
                 sum(saldo_conciliado + debe_sin_conciliar - haber_sin_conciliar) as saldo
             from (
                 select l.id as id,
                     l.account_id as cuenta_id,
                     case when f.fecha is null and l.debit > 0 then l.debit else 0 end as debe_sin_conciliar,
                     case when f.fecha is null and l.credit > 0 then l.credit else 0 end as haber_sin_conciliar,
                     case when f.fecha is not null then l.debit - l.credit else 0 end as saldo_conciliado,
                     0 as creditos_no_encontrados,
                     0 as debitos_no_encontrados
                 from account_move_line l left join conciliacion_bancaria_fecha f on (l.id = f.move_id)
                 where account_id in (
                     select id from account_account where internal_type = 'liquidity'
                 )
                 union all
                 select l.id as id,
                     l.account_id as cuenta_id,
                     0 as debe_sin_conciliar,
                     0 as haber_sin_conciliar,
                     0 as saldo_conciliado,
                     case when monto > 0 then monto else 0 end as creditos_no_encontrados,
                     case when monto < 0 then monto else 0 end as debitos_no_encontrados
                     from conciliacion_bancaria_pendientes_excel l
             ) as detalles
             group by cuenta_id
         )
     """)
 def init(self):
     """
     This SQL view is returning useful statistics about sponsorships.
     The outer query is using window functions to compute cumulative numbers
     Each inner query is computing sum of numbers grouped by _date_format
     :return: None
     """
     tools.drop_view_if_exists(self.env.cr, self._table)
     date_format = self._date_format()
     # We disable the check for SQL injection. The only risk of sql
     # injection is from 'self._table' which is not controlled by an
     # external source.
     # pylint:disable=E8103
     self.env.cr.execute(
         (
             """
             CREATE OR REPLACE VIEW %s AS
             """
             % self._table
             + """
         -- Super query making windows over monthly data, for cumulative
         -- numbers
         -- http://www.postgresqltutorial.com/postgresql-window-function/
         SELECT
           c.id, c.translator_id, c.src_translation_lang_id AS src_lang,
           c.translation_language_id AS dst_lang,
           l1.name || ' to ' || l2.name AS language,
           to_char(date_trunc(%s, c.translate_date), %s) AS study_date,
           c.sponsorship_id, c.id AS correspondence_id, c.direction,
           c.translate_date
         FROM correspondence c
         JOIN res_lang_compassion l1 ON c.src_translation_lang_id = l1.id
         JOIN res_lang_compassion l2 ON c.translation_language_id = l2.id
         WHERE translator_id IS NOT NULL
     """
         ),
         date_format,
     )
    def init(self):
        tools.drop_view_if_exists(self._cr, 'crm_sales_person_report')
        self._cr.execute("""CREATE OR REPLACE VIEW crm_sales_person_report AS (
                                                        SELECT
                                                        max(id) as id,
                                                        user_id as user_id,
                                                        quote_by as quote,
                                                        phone_by as phone,
                                                        deal_by as  deal,
                                                        work_by as work,
                                                        opportunity_by as opportunity,
                                                        deal_date as deal_date,
                                                        work_date as work_date,
                                                        phone_date as phone_date,
                                                        opportunity_date as opportunity_date,
                                                        quote_date as quote_date,
                                                        count(user_id) as user_by_total,
                                                        count(quote_by) as quote_by_total,
                                                        count(phone_by) as phone_by_total,
                                                        count(deal_by) as  deal_by_total,
                                                        count(work_by) as work_by_total,
                                                        count(opportunity_by) as opportunity_by_total
                                                        FROM
                                                        crm_lead 
                                                        WHERE type = 'opportunity'
                                                        GROUP BY 
                                                        id,
                                                        user_id,
                                                        quote_by,
                                                        phone_by,
                                                        deal_by,
                                                        work_by,
                                                        deal_date, 
                                                        work_date,
                                                        phone_date,
                                                        opportunity_date,
                                                        quote_date
)""")
Пример #47
0
    def init(self):
        cr = 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,
                       goods.id as goods_id,
                       goods.brand as brand_id,
                       loc.name as location,
                       line.lot as lot,
                       attribute.name as attribute_id,
                       uom.name as uom,
                       uos.name as uos,
                       wh.name as warehouse,
                       sum(line.qty_remaining) as goods_qty,
                       sum(line.uos_qty_remaining) as goods_uos_qty,
                       sum(line.qty_remaining * line.cost_unit) 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 attribute attribute on attribute.id = line.attribute_id
                    LEFT JOIN uom uom ON goods.uom_id = uom.id
                    LEFT JOIN uom uos ON goods.uos_id = uos.id
                    LEFT JOIN location loc ON loc.goods_id = line.goods_id

                WHERE  wh.type = 'stock'
                  AND line.state = 'done'
                  AND ( goods.no_stock is null or goods.no_stock = FALSE)
                  AND line.qty_remaining != 0


                GROUP BY wh.name, line.lot, attribute.name, goods.name, goods.id, goods.brand, loc.name, uom.name, uos.name

                ORDER BY goods.name, wh.name, goods_qty asc
            )
        """)
 def init(self):
     """
     @author: Harsh Parekh 15 Jan, 2020
     :return: report data in pivot view based on warehouse and product selection and drop view if already exist then.
     """
     context = dict(self._context) or {}
     product_ids = context.get('product_ids') or []
     warehouse_ids = context.get('warehouse_ids') or []
     product_list = '(' + str(product_ids).strip('[]') + ')'
     warehouse_list = '(' + str(warehouse_ids).strip('[]') + ')'
     tools.drop_view_if_exists(self._cr,
                               'product_average_daily_sale_report')
     where_clause = ""
     if product_ids:
         where_clause += """product_id IN %s AND """ % product_list
     if warehouse_ids:
         where_clause += """warehouse_id IN %s AND """ % warehouse_list
     query = """
                 CREATE or REPLACE VIEW product_average_daily_sale_report AS (
                  Select
                     id,
                     period_start_date,
                     period_stop_date,
                     product_id,
                     period_id,
                     ads,
                     monthly_sale,
                     sw.code as warehouse_code,                        
                     type
                 From
                     get_product_average_daily_sale as get_product_avg_sale
                      INNER JOIN stock_warehouse sw on sw.id = get_product_avg_sale.warehouse_id
                 WHERE
                 %s
                 type='actual_past_sales'
                 );
                 """ % where_clause
     self._cr.execute(query)
Пример #49
0
 def init(self):
     tools.drop_view_if_exists(self._cr, "mis_account_analytic_line")
     self._cr.execute("""
         CREATE OR REPLACE VIEW mis_account_analytic_line AS (
             SELECT
                 aal.id AS id,
                 aal.id AS analytic_line_id,
                 aal.date as date,
                 aal.account_id as account_id,
                 aal.company_id as company_id,
                 'posted'::VARCHAR as state,
                 CASE
                   WHEN aal.amount >= 0.0 THEN aal.amount
                   ELSE 0.0
                 END AS credit,
                 CASE
                   WHEN aal.amount < 0 THEN (aal.amount * -1)
                   ELSE 0.0
                 END AS debit,
                 aal.amount as balance
             FROM
                 account_analytic_line aal
         )""")
Пример #50
0
 def init(self):
     tools.drop_view_if_exists(self._cr, 'psdc_project_task_histories_list')
     self._cr.execute("""
         CREATE OR REPLACE VIEW psdc_project_task_histories_list AS
         SELECT 
             PPTH.id AS id,
             PPTH.task_id AS task_id,
             PT.project_id AS project_id,
             PPTH.state AS history_state,
             CASE 
                 WHEN PPTH.state LIKE 'done' THEN 'Listo'
                 WHEN PPTH.state LIKE 'normal' THEN 'En proceso'
                 WHEN PPTH.state LIKE 'blocked' THEN 'Bloqueado'
             END AS history_state_formatted,
             PPTH.time AS history_time,
             PPTH.comments AS history_comments,
             PPTH.date AS history_date,
             PPTH.create_date AS history_create_date,
             PPTH.latitude AS latitude,
             PPTH.longitude AS longitude
         FROM 
             psdc_project_task_history PPTH
             INNER JOIN project_task PT ON PT.id = PPTH.task_id""")
Пример #51
0
    def init(self):
        tools.drop_view_if_exists(self._cr, "product_kks_report")
        self._cr.execute(
            """
            CREATE or REPLACE view product_kks_report as (
                SELECT
                    row_number() OVER () as id,t.*
                FROM (
                    select customer_id,kp.magasin_id,item,k.id as kks_id,ss_type_appareil_id,pc.description as type,maker_id,concat('- REP: ',pp.no_piece,' ',pp.name) as designation,
                    ref_fab,ref_com,reference,(select id from product_unite limit 1) as unite_id,
                    (select id from product_travaux limit 1) as travaux_id,
                     (select id from product_arret limit 1) as arret_id
                    from product_kks k
                    inner join product_kks_piece kp on k.id=kp.kks_id
                    left join product_category pc on pc.id=k.ss_type_appareil_id
                    left join product_piece pp on pp.id=kp.piece_id
                    group by customer_id,kp.magasin_id,item,k.id,ss_type_appareil_id,pc.description,maker_id,concat('- REP: ',pp.no_piece,' ',pp.name),
                    ref_fab,ref_com,reference) as t


            )
        """
        )
Пример #52
0
    def init(self):
        tools.drop_view_if_exists(self._cr, "product_kks_poids_report")
        self._cr.execute(
            """
            CREATE or REPLACE view product_kks_poids_report as (
                SELECT row_number() OVER () as id,t.*
                FROM ( SELECT t1.*, t2.value, t3.value2 FROM (
                select kks.id as kks_id,arret_id, customer_id,kks.reference as designation,kks.appareil_id,unite_id
                from product_kks kks
                inner join product_kks_arret pka on pka.kks_id=kks.id) AS t1
                LEFT JOIN
                (select pt.id as appareil_id, pal.value  as value from product_template pt
                left join product_attribute_line pal on pal.product_tmpl_id=pt.id
                inner join product_attribute pa on pa.id=pal.attribute_id
                where pa.name like 'Poids Corps' or pa.name like 'Poids') AS t2 on t1.appareil_id=t2.appareil_id
				LEFT JOIN
                (select pt.id as appareil_id, pal.value as value2 from product_template pt
                left join product_attribute_line pal on pal.product_tmpl_id=pt.id
                inner join product_attribute pa on pa.id=pal.attribute_id
                where pa.name like 'Poids Motorisation') AS t3 on t1.appareil_id=t3.appareil_id) AS t
            )
        """
        )
 def init(self):
     # 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(self.env.cr, 'im_livechat_report_operator')
     self.env.cr.execute("""
         CREATE OR REPLACE VIEW im_livechat_report_operator AS (
             SELECT
                 row_number() OVER () AS id,
                 P.id as partner_id,
                 L.id as livechat_channel_id,
                 count(C.id) as nbr_channel,
                 C.id as channel_id,
                 C.create_date as start_date,
                 EXTRACT('epoch' FROM (max((SELECT (max(M.create_date)) FROM mail_message M JOIN mail_message_mail_channel_rel R ON (R.mail_message_id = M.id) WHERE R.mail_channel_id = C.id))-C.create_date)) as duration,
                 EXTRACT('epoch' from ((SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=P.id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id)-(SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id IS NULL AND R.mail_channel_id = C.id AND R.mail_message_id = M.id))) as time_to_answer
             FROM im_livechat_channel_im_user O
                 JOIN res_users U ON (O.user_id = U.id)
                 JOIN res_partner P ON (U.partner_id = P.id)
                 JOIN im_livechat_channel L ON (L.id = O.channel_id)
                 JOIN mail_channel C ON (C.livechat_channel_id = L.id)
                 JOIN mail_channel_partner MCP ON (MCP.partner_id = P.id AND MCP.channel_id = C.id)
             GROUP BY P.id, L.id, C.id, C.create_date
         )
     """)
Пример #54
0
 def init(self):
     tools.drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
         SELECT
             min(ru.id) AS "id",
             ru.login as name,
             ru.id as user_id,
             pbl.bus_id as bus_id,
             pbl."action" as action,
             count(pbl."action") as number
             FROM
             "pos_bus_log" AS pbl,
             "res_users" AS ru
         WHERE
             pbl.user_id=ru.id
             and pbl.bus_id is not Null
         GROUP BY
             pbl.action,
             ru.login,
             ru.id,
             pbl.bus_id
         
         )""" % (self._table))
 def init(self):
     tools.drop_view_if_exists(self._cr, 'zk_report_daily_attendance')
     self._cr.execute("""
         create or replace view zk_report_daily_attendance as (
             select
                 min(z.id) as id,
                 z.employee_id as name,
                 z.write_date as punching_day,
                 z.address_id as address_id,
                 z.attendance_type as attendance_type,
                 z.punching_time as punching_time,
                 z.punch_type as punch_type
             from zk_machine_attendance z
                 join hr_employee e on (z.employee_id=e.id)
             GROUP BY
                 z.employee_id,
                 z.write_date,
                 z.address_id,
                 z.attendance_type,
                 z.punch_type,
                 z.punching_time
         )
     """)
Пример #56
0
 def init(self):
     """
         CRM Lead Report
         @param cr: the current row, from the database cursor
     """
     tools.drop_view_if_exists(self._cr, 'project_agile_analytic_line_view')
     self._cr.execute("""
       CREATE OR REPLACE VIEW project_agile_analytic_line_view AS
       SELECT l.id,
              l.project_id,
              l.task_id,
              l. type_id,
              l.user_id,
              l.stage_id,
              l.start_date,
              l.end_date,
              l.company_id,
              COALESCE(EXTRACT(
               epoch FROM COALESCE(l.end_date, now() at time zone 'utc') -
               l.start_date)/3600, 0.0::double precision
              ) AS duration
       FROM project_agile_analytic_line l;
                 """)
Пример #57
0
    def init(self):
        tools.drop_view_if_exists(self._cr, self._table)
        self._cr.execute("""
            CREATE view %s as
                %s
                FROM
                
                hr_timesheet_sheet_sheet hts 
                    left join hr_employee as hr on (hts.employee_id = hr.id)
                    left join res_users u on u.id = hts.user_id
                    left join account_analytic_line aal on (aal.sheet_id = hts.id)
                    left join project_project pp on (aal.project_id = pp.id)
                    left join project_task pt  on (pt.id = aal.task_id and pt.project_id = pp.id)
                    left join  employee_time_history eth on (eth.task_id = pt.id and eth.employee_id = hr.id)
               WHERE pt.active = 'true'
                %s
        """ % (self._table, self._select(), self._group_by()))


# (CASE WHEN ((select count(*) from employee_time_history where (task_id = pt.id and employee_id = hr.id)) > 0)
#                     THEN  eth.related_rate/(select count(*) from employee_time_history where task_id = pt.id and employee_id = hr.id)
#                     ELSE eth.related_rate
#                     END) as cost,
 def init(self):
     """ Event Question main report """
     tools.drop_view_if_exists(self._cr, 'event_question_report')
     self._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
     )""")
 def init(self):
     # self._table = account_invoice_contact_report
     tools.drop_view_if_exists(self.env.cr, self._table)
     self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
         WITH currency_rate (currency_id, rate, date_start, date_end) AS (
             SELECT r.currency_id, 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()))
Пример #60
0
 def init(self):
     print("Connected")
     tools.drop_view_if_exists(self.env.cr, 'piros_renewals_overview')
     self.env.cr.execute("""
         CREATE OR REPLACE VIEW piros_renewals_overview AS (
           SELECT 
           row_number() OVER () as id,
           sale_order_line.x_enddate as x_date, 
           sale_order.name as x_so, 
           sale_order.id as x_order_id,
           rp1.commercial_company_name as x_customer, 
           rp2.commercial_company_name as x_end_customer, 
           sale_order_line.name as x_product, 
           sale_order_line.product_uom_qty as x_qty, 
           sale_order_line.x_contract as x_contract 
           FROM sale_order_line 
           JOIN sale_order ON sale_order_line.order_id = sale_order.id  
           JOIN res_partner rp1 ON sale_order.partner_id = rp1.id 
           JOIN res_partner rp2 ON sale_order.x_end_customer_id = rp2.id 
           WHERE sale_order.state = 'sale'
           GROUP BY sale_order_line.x_enddate,sale_order.id,sale_order.name,rp1.commercial_company_name,rp2.commercial_company_name,sale_order_line.name,sale_order_line.product_uom_qty,sale_order_line.x_contract 
           ORDER BY x_date
         )""")