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_address a WHERE a.partner_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.section_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'))
         )""")
Example #2
0
    def init(self, cr):

        tools.drop_view_if_exists(cr, 'account_summary')
		
        cr.execute("""
            create or replace view account_summary as (
            select
                l.id as id,
                am.date as date,
                to_char(am.date, 'YYYY') as year,
                p.fiscalyear_id as fiscalyear_id,
                am.period_id as period_id,
                l.account_id as account_id,
				a.code_2 as account_code,
                l.debit as debit,
                l.credit as credit,
                l.debit-l.credit as balance,
                l.credit-l.debit as balance_inv
            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)
                where l.state != 'draft'
            )
        """)
Example #3
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'account_move_line_report')
     cr.execute(""" 
                create or replace view account_move_line_report as (
                    select
                        aml.id as id,
                        aml.name as name,
                        aml.date as date,
                        aml.account_id as account_id,
                        aml.currency_id as currency_id,
                        aml.debit as debit,
                        aml.credit as credit,
                        aml.ref as ref,
                        aml.journal_id as journal_id,
                        aml.period_id as period_id,
                        aml.reconcile_id as reconcile_id,
                        aml.move_id as move_id,
                        aml.tot_balance as tot_balance,
                        res.id as partner_id
                        
                        
                    from account_move_line as aml
                         left join res_partner res on (aml.partner_id=res.id)
                    group by
                         aml.id,
                         res.id
                         
                     )
                """)
Example #4
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_vote')
     cr.execute("""
         create or replace view report_vote as (
            select
                 min(iv.id) as id,
                 count(*) as nbr,
                 to_date(to_char(ii.open_date, 'dd-MM-YYYY'),'dd-MM-YYYY') as date,
                 to_char(ii.open_date, 'YYYY') as year,
                 to_char(ii.open_date, 'MM') as month,
                 to_char(ii.open_date, 'YYYY-MM-DD') as day,
                 iv.user_id as user_id,
                 iv.idea_id as idea_id,
                 ii.state as idea_state,
                 ii.user_id as creater_id,
                 ii.category_id,
                 (sum(CAST(iv.score as integer))/count(iv.*)) as score
             from
                 idea_vote as iv
                 left join idea_idea as ii on (ii.id = iv.idea_id)
             group by
                 iv.id ,to_char(ii.open_date, 'dd-MM-YYYY'),to_char(ii.open_date, 'YYYY'),
                 to_char(ii.open_date, 'MM'),to_char(ii.open_date, 'YYYY-MM-DD'),ii.state,
                 iv.user_id,ii.user_id,ii.category_id,iv.idea_id
         )
         """)
Example #5
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'bpa_summary')
        cr.execute("""
			CREATE OR REPLACE view bpa_summary AS (
				SELECT 
					row_number() OVER () as id,
					a.type_of_charge,
					d.id as invoice_id, 
					d.date_invoice as bill_date, 
					d.date_due as due_date, 
					b.id as invoice_related_id, 
					b.bl_number, 
					b.bl_date, 
					a.picking_related_id, 
					d.partner_id, 
					d.currency_id, 
					sum(a.price_subtotal) as amount
				FROM
					account_invoice_line a
					LEFT JOIN account_invoice b ON b.id=a.invoice_related_id
					LEFT JOIN stock_picking c ON c.id=a.picking_related_id 
					LEFT JOIN account_invoice d ON d.id=a.invoice_id 
				WHERE
					d.type='in_invoice' and a.type_of_charge is not NULL and d.state not in ('cancel')
				GROUP BY
					d.id, d.partner_id, a.picking_related_id, b.id, 
					b.bl_number, b.bl_date, d.date_invoice, 
					d.date_due, d.currency_id, a.type_of_charge
				ORDER BY
					d.partner_id, a.picking_related_id, b.id
					   )
		""")
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_account_invoice_product')        
     cr.execute("""
         create or replace view report_account_invoice_product as (
             select
                 min(l.id) as id,
                 i.create_date as date,                    
                 to_char(date_trunc('day',i.date_invoice), 'YYYY') as year,
                 to_char(date_trunc('day',i.date_invoice), 'MM') as month,
                 to_char(date_trunc('day',i.date_invoice), 'YYYY-MM-DD') as day,                    
                 i.type,
                 i.state,
                 l.product_id,
                 t.categ_id,
                 i.partner_id,
                 sum(l.quantity * l.price_unit * (1.0 - l.discount/100.0)) as amount,
                 sum(l.quantity * l.cost_price) as cost_price,
                 sum((l.quantity * l.price_unit * (1.0 - l.discount/100.0) - (l.quantity * l.cost_price))) as margin,
                 sum(l.quantity) as quantity
             from account_invoice i
                 left join account_invoice_line l on (i.id = l.invoice_id)
                 left join product_product p on (p.id = l.product_id)
                 left join product_template t on (t.id = p.product_tmpl_id)                    
             group by t.categ_id,i.partner_id,l.product_id, i.date_invoice, i.type, i.state,i.create_date
         )
     """)
Example #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,
                    to_char(c.create_date, 'YYYY') as name,
                    to_char(c.create_date, 'MM') as month,
                    to_char(c.create_date, 'YYYY-MM-DD') as day,
                    to_char(c.date_open, 'YYYY-MM-DD') as opening_date,
                    to_char(c.create_date, 'YYYY-MM-DD') as creation_date,
                    c.state,
                    c.user_id,
                    c.working_hours_open,
                    c.working_hours_close,
                    c.section_id,
                    c.stage_id,
                    to_char(c.date_closed, 'YYYY-mm-dd') 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_id,
                    c.task_id,
                    date_trunc('day',c.create_date) as create_date,
                    extract('epoch' from (c.date_open-c.create_date))/(3600*24) as  delay_open,
                    extract('epoch' from (c.date_closed-c.date_open))/(3600*24) 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'
            )""")
Example #8
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'report_timesheet_task_user')
        cr.execute(""" create or replace view report_timesheet_task_user as (
        select
         ((r.id*12)+to_number(months.m_id,'99'))::integer as id,
               months.name as name,
               r.id as user_id,
               to_char(to_date(months.name, 'YYYY/MM/DD'),'YYYY') as year,
               to_char(to_date(months.name, 'YYYY/MM/DD'),'MM') as month,
               (select sum(hours) from project_task_work where user_id = r.id and date between to_date(months.name, 'YYYY/MM/DD') and (to_date(months.name, 'YYYY/MM/DD') + interval '1 month' -
            interval '1 day') ) as task_hrs
        from res_users r,
                (select to_char(p.date,'YYYY-MM-01') as name,
            to_char(p.date,'MM') as m_id
                from project_task_work p

            union
                select to_char(h.name,'YYYY-MM-01') as name,
                to_char(h.name,'MM') as m_id
                from hr_timesheet_sheet_sheet_day h) as months

            group by
                r.id,months.m_id,months.name,
                to_char(to_date(months.name, 'YYYY/MM/DD'),'YYYY') ,
                to_char(to_date(months.name, 'YYYY/MM/DD'),'MM')
              ) """)
Example #9
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'campaign_analysis')
     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
         )
     """)
Example #10
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
         )
     """)
Example #11
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,
                 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,
                 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
         )
     """)
Example #12
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

                )
        """)
Example #13
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,
                 to_char(wl.date_planned, 'YYYY') as year,
                 to_char(wl.date_planned, 'MM') as month,
                 to_char(wl.date_planned, 'YYYY-MM-DD') as day,
                 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
     )""")
 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.discount) 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):
     """ 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.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
         )""")
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'campaign_analysis')
     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
         )
     """)
 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):
     tools.drop_view_if_exists(cr, 'analytic_entries_report')
     cr.execute("""
         create or replace view analytic_entries_report as (
              select
                  min(a.id) as id,
                  count(distinct a.id) as nbr,
                  a.create_date as date,
                  to_char(a.create_date, 'YYYY') as year,
                  to_char(a.create_date, 'MM') as month,
                  to_char(a.create_date, 'YYYY-MM-DD') as day,
                  a.user_id as user_id,
                  a.name as name,
                  analytic.partner_id as partner_id,
                  a.company_id as company_id,
                  a.currency_id as currency_id,
                  a.account_id as account_id,
                  a.general_account_id as general_account_id,
                  a.journal_id as journal_id,
                  a.move_id as move_id,
                  a.product_id as product_id,
                  a.product_uom_id as product_uom_id,
                  sum(a.amount) as amount,
                  sum(a.unit_amount) as unit_amount
              from
                  account_analytic_line a, account_analytic_account analytic
              where analytic.id = a.account_id
              group by
                  a.create_date, a.user_id,a.name,analytic.partner_id,a.company_id,a.currency_id,
                  a.account_id,a.general_account_id,a.journal_id,
                  a.move_id,a.product_id,a.product_uom_id
         )
     """)
    def init(self, cr):
        """  Display Number of cases and Average Probability
            @param cr: the current row, from the database cursor
        """

        tools.drop_view_if_exists(cr, 'crm_fundraising_report')
        cr.execute("""
            create or replace view crm_fundraising_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,
                    c.state,
                    c.user_id,
                    c.section_id,
                    c.categ_id,
                    c.type_id,
                    c.company_id,
                    c.partner_id,
                    count(*) as nbr,
                    date_trunc('day',c.create_date) as create_date,
                    sum(planned_revenue) as amount_revenue,
                    sum(planned_cost) as planned_cost,
                    sum(planned_revenue*probability)::decimal(16,2) as amount_revenue_prob,
                    avg(probability)::decimal(16,2) as probability,
                    avg(extract('epoch' from (c.date_closed-c.create_date)))/(3600*24) as  delay_close
                from
                    crm_fundraising c
                where c.active = 'true'
                group by to_char(c.date, 'YYYY'), to_char(c.date, 'MM'),\
                     c.state, c.user_id,c.section_id,c.categ_id,type_id,c.partner_id,c.company_id,
                     c.create_date,to_char(c.date, 'YYYY-MM-DD')
            )""")
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'sps_dashboard')
        
        cr.execute("""
            create or replace view sps_dashboard as (
       
       with S1 as (
Select 1 as nbr,pr.id as Xid, (select id from sps_state where code = tt.name) as state_id, p.id as id, aa.name as name, p.id as partner_id, tt.name as state from account_analytic_account aa 
inner join res_partner p on aa.partner_id = p.id 
inner join project_project pr ON aa.id = pr.analytic_account_id
inner join project_task_type tt on pr.project_task_stage = tt.id and tt.name not in ('Waiting Goods')
),
S2 as (
Select 1 as nbr, s.id as Xid,(select id from sps_state where code = s.state) as state_id, p.id as id, s.name as name, p.id as partner_id, s.state as state from sale_order s 
inner join res_partner p on s.partner_id = p.id where state not in ('confirmed','done','progress','manual','follow_up','cancel')
),
S3 as (
select 1 as nbr, l.id as Xid,(select id from sps_state where code = cs.name) as state_id, p.id as id, l.name as name, p.id as partner_id, cs.name as state from crm_lead l 
inner join crm_case_stage cs on l.stage_id = cs.id 
inner join res_partner p on l.partner_id = p.id 
where l.state not in ('cancel','done')
)
select 1 as nbr, state_id, 1000*id + 200*Xid as id, name, partner_id from S1 
union
select 1 as nbr, state_id, 20000*id + 305*Xid as id, name, partner_id from S2 where S2.id not in (select id from S1)
union
select 1 as nbr, state_id, 300000*id + 101*Xid as id, name, partner_id from S3
where S3.id not in (select partner_id from S1))""")        
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'hr_holidays_report')
     cr.execute("""
         create or replace view hr_holidays_report as (
              select
                  min(s.id) as id,
                  date_trunc('day',s.create_date) as date,
                  date_trunc('day',s.date_from) as date_from,
                  date_trunc('day',s.date_to) as date_to,
                  sum(s.number_of_days_temp) as number_of_days_temp,
                  s.employee_id,
                  s.user_id as user_id,
                  to_char(s.create_date, 'YYYY') as year,
                  to_char(s.create_date, 'MM') as month,
                  to_char(s.create_date, 'YYYY-MM-DD') as day,
                  s.holiday_status_id,
                  s.department_id,
                  s.state
                  from
              hr_holidays s
              where type='remove' and
              s.employee_id is not null
              group by
                  s.create_date,s.state,s.date_from,s.date_to,
                  s.employee_id,s.user_id,s.holiday_status_id,
                  s.department_id
         )
     """)
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'document_report_gbu')
     current_date=time.strftime('%Y-%m-%d %H:%M:%S')
     print "current date",current_date
     cr.execute("""
         CREATE OR REPLACE VIEW document_report_gbu as (
              SELECT
                  min(f.id) as id,
                  to_char(f.date_expired, 'YYYY') as name,
                  to_char(f.date_expired, 'MM') as month,
                  f.user_id as user_id,
                  f.name as doc_name,
                  p.name as partner_name,
                  u.name as user,
                  count(*) as nbr,
                  d.name as directory,
                  f.datas_fname as datas_fname,
                  f.create_date as create_date,
                  f.date_expired as date_expired,
                  f.file_size as file_size,
                  min(d.type) as type,
                  f.write_date as change_date
              FROM ir_attachment f
                  left join document_directory d on (f.parent_id=d.id and d.name<>'')
                  inner join res_users u on (f.user_id=u.id)
                  inner join res_partner p on (f.partner_id=p.id)
              group by to_char(f.date_expired, 'YYYY'), to_char(f.date_expired, 'MM'),f.user_id,f.name,p.name,u.name,d.name,f.datas_fname,f.create_date,f.date_expired,f.file_size,f.write_date,d.type
          )
     """)
Example #23
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_vote')
     cr.execute("""
         create or replace view report_vote as (
            select
                 min(iv.id) as id,
                 count(*) as nbr,
                 to_date(to_char(ii.open_date, 'dd-MM-YYYY'),'dd-MM-YYYY') as date,
                 to_char(ii.open_date, 'YYYY') as year,
                 to_char(ii.open_date, 'MM') as month,
                 to_char(ii.open_date, 'YYYY-MM-DD') as day,
                 iv.user_id as user_id,
                 iv.idea_id as idea_id,
                 ii.state as idea_state,
                 ii.user_id as creater_id,
                 ii.category_id,
                 (sum(CAST(iv.score as integer))/count(iv.*)) as score
             from
                 idea_vote as iv
                 left join idea_idea as ii on (ii.id = iv.idea_id)
             group by
                 iv.id ,to_char(ii.open_date, 'dd-MM-YYYY'),to_char(ii.open_date, 'YYYY'),
                 to_char(ii.open_date, 'MM'),to_char(ii.open_date, 'YYYY-MM-DD'),ii.state,
                 iv.user_id,ii.user_id,ii.category_id,iv.idea_id
         )
         """)
Example #24
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, "tms_analisys_05")
        cr.execute(
            """
            create or replace view tms_analisys_05 as (

select 
	pl.id                                                                       as id,
	pl.id                                                                       as product_line_id, 
	
	(select p.name_template from product_product as p where p.id=pl.product_id) as product_name,
	(select p.id from product_product as p where p.id=pl.product_id)            as product_id,
	
	pl.quantity                                                                 as quantity,
	pl.list_price                                                               as price,
	(pl.quantity*pl.list_price)                                                 as total_price,

	(select p.name_template  from product_product as p where p.id = activity.product_id) as activity_name,
	(activity.id                                                                       ) as activity_id,
	
	o.name                                                                               as order_name,
	o.id                                                                                 as order_id
	
from tms_product_line as pl, tms_maintenance_order_activity as activity, tms_maintenance_order as o
where pl.state like 'delivered' and pl.activity_id = activity.id and o.id = activity.maintenance_order_id      
                
            )
        """
        )
 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 or replace view account_followup_stat_by_partner as (
             SELECT
                 l.partner_id * 10000 + 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
         )""")
Example #26
0
    def init(self, cr):
        try:
            tools.drop_view_if_exists(cr, 'view_stockmove_accountmove_report')
        except:
            cr.rollback() # allows next statement to pass correctly
        cr.execute("""
            CREATE OR REPLACE VIEW view_stockmove_accountmove_report AS 
 SELECT row_number() over (order by move_report.date) as id, move_report.date, move_report.code, move_report.name, sum(move_report.inv_qty) AS inv_qty, sum(move_report.balance) AS balance, sum(move_report.acc_qty) AS acc_qty, sum(move_report.inventory) AS inventory, sum(COALESCE(move_report.balance, 0.0) - COALESCE(move_report.inventory, 0.0)) AS diff
   FROM (        ( SELECT l.date, p.default_code AS code, p.name_template AS name, 0.0 AS inv_qty, sum(l.quantity) AS acc_qty, COALESCE(sum(l.debit - l.credit), 0.0) AS balance, 0.0 AS inventory
                   FROM product_product p
              LEFT JOIN account_move_line l ON p.id = l.product_id
             WHERE (p.id = l.product_id OR l.product_id IS NULL AND l.name::text ~~ (('['::text || p.default_code::text) || '] %'::text)) AND (l.account_id = (( SELECT account_account.id
                      FROM account_account
                     WHERE account_account.code::text = '5020'::text)) OR l.id IS NULL)
             GROUP BY l.date, p.default_code, p.name_template, l.account_id
             ORDER BY l.date DESC)
        UNION ALL 
                ( SELECT rsi.date::date AS date, p.default_code AS code, p.name_template AS name, sum(rsi.product_qty) AS inv_qty, 0.0 AS acc_qty, 0.0 AS balance, round(sum(rsi.value), 6) AS inventory
                   FROM product_product p
              LEFT JOIN report_stock_inventory rsi ON rsi.product_id = p.id
             WHERE rsi.location_type::text = 'internal'::text AND rsi.state::text = 'done'::text
             GROUP BY rsi.date, p.default_code, p.name_template
             ORDER BY rsi.date DESC)) move_report
  GROUP BY move_report.date, move_report.code, move_report.name
 HAVING sum(move_report.balance) <> 0.0 OR sum(move_report.inventory) IS NOT NULL AND round(sum(move_report.inventory), 2) <> 0.00
  ORDER BY move_report.code, move_report.date;
        """)
Example #27
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
                )
        """)
Example #28
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'report_timesheet_task_user')
        cr.execute(""" create or replace view report_timesheet_task_user as (
        select
         ((r.id*12)+to_number(months.m_id,'99'))::integer as id,
               months.name as name,
               r.id as user_id,
               to_char(to_date(months.name, 'YYYY/MM/DD'),'YYYY') as year,
               to_char(to_date(months.name, 'YYYY/MM/DD'),'MM') as month,
               (select sum(hours) from project_task_work where user_id = r.id and date between to_date(months.name, 'YYYY/MM/DD') and (to_date(months.name, 'YYYY/MM/DD') + interval '1 month' -
            interval '1 day') ) as task_hrs
        from res_users r,
                (select to_char(p.date,'YYYY-MM-01') as name,
            to_char(p.date,'MM') as m_id
                from project_task_work p

            union
                select to_char(h.name,'YYYY-MM-01') as name,
                to_char(h.name,'MM') as m_id
                from hr_timesheet_sheet_sheet_day h) as months

            group by
                r.id,months.m_id,months.name,
                to_char(to_date(months.name, 'YYYY/MM/DD'),'YYYY') ,
                to_char(to_date(months.name, 'YYYY/MM/DD'),'MM')
              ) """)
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_account_invoice_product')
     cr.execute("""
         create or replace view report_account_invoice_product as (
             select
                 min(l.id) as id,
                 i.create_date as date,                    
                 to_char(date_trunc('day',i.date_invoice), 'YYYY') as year,
                 to_char(date_trunc('day',i.date_invoice), 'MM') as month,
                 to_char(date_trunc('day',i.date_invoice), 'YYYY-MM-DD') as day,                    
                 i.type,
                 i.state,
                 l.product_id,
                 t.name,
                 p.default_code,
                 t.categ_id,
                 i.partner_id,
                 sum(l.quantity * l.price_unit * (1.0 - l.discount/100.0)) as amount,
                 sum(l.quantity * l.cost_price) as cost_price,
                 avg(l.margin) as margin,
                 sum(l.quantity) as quantity,
                 avg(l.margin_perc) as pourc_marg,
                 avg(l.discount) as discount
             from account_invoice i
                 left join account_invoice_line l on (i.id = l.invoice_id)
                 left join product_product p on (p.id = l.product_id)
                 left join product_template t on (t.id = p.product_tmpl_id)        
             where i.date_invoice>='2012-01-01' and i.account_id=389 and l.partner_id is not NULL and l.product_id is not NULL and i.state!='draft' and i.state!='cancel'    
             group by t.categ_id,i.partner_id,l.product_id, i.date_invoice, i.type, i.state,i.create_date,t.name,p.default_code
         )
     """)
 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,
                 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, 'account_followup_stat')
     cr.execute("""
         create or replace view account_followup_stat as (
             SELECT
                 l.partner_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
         )""")
    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,
                    to_char(c.create_date, 'YYYY') as name,
                    to_char(c.create_date, 'MM') as month,
                    to_char(c.create_date, 'YYYY-MM-DD') as day,
                    to_char(c.date_open, 'YYYY-MM-DD') as opening_date,
                    to_char(c.create_date, 'YYYY-MM-DD') as creation_date,
                    c.state,
                    c.user_id,
                    c.working_hours_open,
                    c.working_hours_close,
                    c.section_id,
                    c.stage_id,
                    to_char(c.date_closed, 'YYYY-mm-dd') 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_id,
                    c.task_id,
                    date_trunc('day',c.create_date) as create_date,
                    extract('epoch' from (c.date_open-c.create_date))/(3600*24) as  delay_open,
                    extract('epoch' from (c.date_closed-c.date_open))/(3600*24) 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'
            )""")
Example #33
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, "tms_analisys_02")
        cr.execute(
            """
            create or replace view tms_analisys_02 as (

select a.id, o.date as date_order, o.id as order_id, a.id as name, a.product_id, e.categ_id as product_category_id,
a.date_start as date,
to_char(date_trunc('day',a.date_start), 'YYYY') as year,
to_char(date_trunc('day',a.date_start), 'MM') as month,
to_char(date_trunc('day',a.date_start), 'YYYY-MM-DD') as day,
a.hours_real as duration, a.date_start_real as date_start, a.date_end_real as date_end, 
a.external_workshop, a.invoice_id, c.supplier_invoice_number,
o.unit_id, o.driver_id, a.supplier_id,
a.parts_cost as spare_parts,
a.cost_service as manpower,
a.parts_cost_external as spare_parts_external,
a.cost_service_external as manpower_external
from tms_maintenance_order_activity a
left join tms_maintenance_order as o on o.id=a.maintenance_order_id and o.state='done'
left join account_invoice as c on c.id=a.invoice_id and c.state <> 'cancel'
left join product_product d on d.id=a.product_id
left join product_template e on e.id=d.product_tmpl_id
where a.state = 'done'
order by o.date, a.date_start_real
                
            )
        """
        )
 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_address a WHERE a.partner_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.section_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'))
         )""")
 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 followups 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 or replace view account_followup_stat_by_partner as (
             SELECT
                 l.partner_id * 10000 + 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
         )""")
Example #36
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_account_invoice_product')
     cr.execute("""
         create or replace view report_account_invoice_product as (
             select
                 min(l.id) as id,
                 i.create_date as date,                    
                 to_char(date_trunc('day',i.date_invoice), 'YYYY') as year,
                 to_char(date_trunc('day',i.date_invoice), 'MM') as month,
                 to_char(date_trunc('day',i.date_invoice), 'YYYY-MM-DD') as day,                    
                 i.type,
                 i.state,
                 l.product_id,
                 t.categ_id,
                 i.partner_id,
                 sum(l.quantity * l.price_unit * (1.0 - l.discount/100.0)) as amount,
                 sum(l.quantity * l.cost_price) as cost_price,
                 sum((l.quantity * l.price_unit * (1.0 - l.discount/100.0) - (l.quantity * l.cost_price))) as margin,
                 sum(l.quantity) as quantity
             from account_invoice i
                 left join account_invoice_line l on (i.id = l.invoice_id)
                 left join product_product p on (p.id = l.product_id)
                 left join product_template t on (t.id = p.product_tmpl_id)                    
             group by t.categ_id,i.partner_id,l.product_id, i.date_invoice, i.type, i.state,i.create_date
         )
     """)
Example #37
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(line.price_ded) 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
                )
        """)
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'sps_dashboard')

        cr.execute("""
            create or replace view sps_dashboard as (
       
       with S1 as (
Select 1 as nbr,pr.id as Xid, (select id from sps_state where code = tt.name) as state_id, p.id as id, aa.name as name, p.id as partner_id, tt.name as state from account_analytic_account aa 
inner join res_partner p on aa.partner_id = p.id 
inner join project_project pr ON aa.id = pr.analytic_account_id
inner join project_task_type tt on pr.project_task_stage = tt.id and tt.name not in ('Waiting Goods')
),
S2 as (
Select 1 as nbr, s.id as Xid,(select id from sps_state where code = s.state) as state_id, p.id as id, s.name as name, p.id as partner_id, s.state as state from sale_order s 
inner join res_partner p on s.partner_id = p.id where state not in ('confirmed','done','progress','manual','follow_up','cancel')
),
S3 as (
select 1 as nbr, l.id as Xid,(select id from sps_state where code = cs.name) as state_id, p.id as id, l.name as name, p.id as partner_id, cs.name as state from crm_lead l 
inner join crm_case_stage cs on l.stage_id = cs.id 
inner join res_partner p on l.partner_id = p.id 
where l.state not in ('cancel','done')
)
select 1 as nbr, state_id, 1000*id + 200*Xid as id, name, partner_id from S1 
union
select 1 as nbr, state_id, 20000*id + 305*Xid as id, name, partner_id from S2 where S2.id not in (select id from S1)
union
select 1 as nbr, state_id, 300000*id + 101*Xid as id, name, partner_id from S3
where S3.id not in (select partner_id from S1))""")
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'analytic_entries_report')
     cr.execute("""
         create or replace view analytic_entries_report as (
              select
                  min(a.id) as id,
                  count(distinct a.id) as nbr,
                  a.create_date as date,
                  to_char(a.create_date, 'YYYY') as year,
                  to_char(a.create_date, 'MM') as month,
                  to_char(a.create_date, 'YYYY-MM-DD') as day,
                  a.user_id as user_id,
                  a.name as name,
                  analytic.partner_id as partner_id,
                  a.company_id as company_id,
                  a.currency_id as currency_id,
                  a.account_id as account_id,
                  a.general_account_id as general_account_id,
                  a.journal_id as journal_id,
                  a.move_id as move_id,
                  a.product_id as product_id,
                  a.product_uom_id as product_uom_id,
                  sum(a.amount) as amount,
                  sum(a.unit_amount) as unit_amount
              from
                  account_analytic_line a, account_analytic_account analytic
              where analytic.id = a.account_id
              group by
                  a.create_date, a.user_id,a.name,analytic.partner_id,a.company_id,a.currency_id,
                  a.account_id,a.general_account_id,a.journal_id,
                  a.move_id,a.product_id,a.product_uom_id
         )
     """)
    def init(self, cr):

        """  Display Number of cases and Average Probability
            @param cr: the current row, from the database cursor
        """

        tools.drop_view_if_exists(cr, 'crm_fundraising_report')
        cr.execute("""
            create or replace view crm_fundraising_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,
                    c.state,
                    c.user_id,
                    c.section_id,
                    c.categ_id,
                    c.type_id,
                    c.company_id,
                    c.partner_id,
                    count(*) as nbr,
                    date_trunc('day',c.create_date) as create_date,
                    sum(planned_revenue) as amount_revenue,
                    sum(planned_cost) as planned_cost,
                    sum(planned_revenue*probability)::decimal(16,2) as amount_revenue_prob,
                    avg(probability)::decimal(16,2) as probability,
                    avg(extract('epoch' from (c.date_closed-c.create_date)))/(3600*24) as  delay_close
                from
                    crm_fundraising c
                where c.active = 'true'
                group by to_char(c.date, 'YYYY'), to_char(c.date, 'MM'),\
                     c.state, c.user_id,c.section_id,c.categ_id,type_id,c.partner_id,c.company_id,
                     c.create_date,to_char(c.date, 'YYYY-MM-DD')
            )""")
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'payment_advice_report')
     cr.execute("""
         create or replace view payment_advice_report as (
             select
                 min(l.id) as id,
                 sum(l.bysal) as bysal,
                 p.name,
                 p.state,
                 p.date,
                 p.number,
                 p.company_id,
                 p.bank_id,
                 p.chaque_nos as cheque_nos,
                 p.neft,
                 l.employee_id,
                 l.ifsc_code,
                 l.name as employee_bank_no,
                 to_char(p.date, 'YYYY') as year,
                 to_char(p.date, 'MM') as month,
                 to_char(p.date, 'YYYY-MM-DD') as day,
                 1 as nbr
             from
                 hr_payroll_advice as p
                 left join hr_payroll_advice_line as l on (p.id=l.advice_id)
             where 
                 l.employee_id IS NOT NULL
             group by
                 p.number,p.name,p.date,p.state,p.company_id,p.bank_id,p.chaque_nos,p.neft,
                 l.employee_id,l.advice_id,l.bysal,l.ifsc_code, l.name
         )
     """)
Example #42
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
         )
     """)
Example #43
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,
                    0.00 as net_margin_per_qty,
                    0.00 *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,
                    po.user_id,
                    to_char(date_trunc('month',po.date_order),'YYYY-MM-DD')::text

                )
        """)
 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.partner_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
         )""")
Example #45
0
    def init(self, cr):
        tools.drop_view_if_exists(cr,'tms_analisys_01')
        cr.execute("""

create or replace view tms_analisys_01 as (

select 
o.id, o.id as name, o.product_id, o.supervisor_id, o.maint_cycle_id, o.driver_id,
o.user_id, o.unit_id, o.date,
to_char(date_trunc('day',o.date), 'YYYY') as year,
to_char(date_trunc('day',o.date), 'MM') as month,
to_char(date_trunc('day',o.date), 'YYYY-MM-DD') as day,
o.date_start_real, o.date_end_real,
o.duration_real, o.notes,
sum(a.parts_cost) as parts_cost,
sum(a.cost_service) as cost_service,
sum(a.parts_cost_external) as parts_cost_external,
sum(a.cost_service_external) as cost_service_external
from tms_maintenance_order as o
left join tms_maintenance_order_activity a on o.id=a.maintenance_order_id and a.state='done'
where o.state = 'done'
group by o.id, o.product_id, o.supervisor_id, o.maint_cycle_id, o.driver_id,
o.user_id, o.unit_id, o.name, o.date, o.date_start_real, o.date_end_real,
o.duration_real, o.notes
order by o.date
);
  
        """)
Example #46
0
    def init(self, cr):
        try:
            tools.drop_view_if_exists(cr, "view_stockmove_accountmove_report")
        except:
            cr.rollback()  # allows next statement to pass correctly
        cr.execute(
            """
            CREATE OR REPLACE VIEW view_stockmove_accountmove_report AS 
 SELECT row_number() over (order by move_report.date) as id, move_report.date, move_report.code, move_report.name, sum(move_report.inv_qty) AS inv_qty, sum(move_report.balance) AS balance, sum(move_report.acc_qty) AS acc_qty, sum(move_report.inventory) AS inventory, sum(COALESCE(move_report.balance, 0.0) - COALESCE(move_report.inventory, 0.0)) AS diff
   FROM (        ( SELECT l.date, p.default_code AS code, p.name_template AS name, 0.0 AS inv_qty, sum(l.quantity) AS acc_qty, COALESCE(sum(l.debit - l.credit), 0.0) AS balance, 0.0 AS inventory
                   FROM product_product p
              LEFT JOIN account_move_line l ON p.id = l.product_id
             WHERE (p.id = l.product_id OR l.product_id IS NULL AND l.name::text ~~ (('['::text || p.default_code::text) || '] %'::text)) AND (l.account_id = (( SELECT account_account.id
                      FROM account_account
                     WHERE account_account.code::text = '5020'::text)) OR l.id IS NULL)
             GROUP BY l.date, p.default_code, p.name_template, l.account_id
             ORDER BY l.date DESC)
        UNION ALL 
                ( SELECT rsi.date::date AS date, p.default_code AS code, p.name_template AS name, sum(rsi.product_qty) AS inv_qty, 0.0 AS acc_qty, 0.0 AS balance, round(sum(rsi.value), 6) AS inventory
                   FROM product_product p
              LEFT JOIN report_stock_inventory rsi ON rsi.product_id = p.id
             WHERE rsi.location_type::text = 'internal'::text AND rsi.state::text = 'done'::text
             GROUP BY rsi.date, p.default_code, p.name_template
             ORDER BY rsi.date DESC)) move_report
  GROUP BY move_report.date, move_report.code, move_report.name
 HAVING sum(move_report.balance) <> 0.0 OR sum(move_report.inventory) IS NOT NULL AND round(sum(move_report.inventory), 2) <> 0.00
  ORDER BY move_report.code, move_report.date;
        """
        )
Example #47
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_account_invoice_product')   
     cr.execute("""
         create or replace view report_account_invoice_product as (
             select
                 min(l.id) as id,
                 i.create_date as date,                    
                 to_char(date_trunc('day',i.date_invoice), 'YYYY') as year,
                 to_char(date_trunc('day',i.date_invoice), 'MM') as month,
                 to_char(date_trunc('day',i.date_invoice), 'YYYY-MM-DD') as day,                    
                 i.type,
                 i.state,
                 l.product_id,
                 t.name,
                 p.default_code,
                 t.categ_id,
                 i.partner_id,
                 sum(l.quantity * l.price_unit * (1.0 - l.discount/100.0)) as amount,
                 sum(l.quantity * l.cost_price) as cost_price,
                 avg(l.margin) as margin,
                 sum(l.quantity) as quantity,
                 avg(l.margin_perc) as pourc_marg,
                 avg(l.discount) as discount
             from account_invoice i
                 left join account_invoice_line l on (i.id = l.invoice_id)
                 left join product_product p on (p.id = l.product_id)
                 left join product_template t on (t.id = p.product_tmpl_id)        
             where i.date_invoice>='2012-01-01' and i.account_id=389 and l.partner_id is not NULL and l.product_id is not NULL and i.state!='draft' and i.state!='cancel'    
             group by t.categ_id,i.partner_id,l.product_id, i.date_invoice, i.type, i.state,i.create_date,t.name,p.default_code
         )
     """)
 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.discount) 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):

        """ 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.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
            )""")
 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,
                 to_char(wl.date_planned, 'YYYY') as year,
                 to_char(wl.date_planned, 'MM') as month,
                 to_char(wl.date_planned, 'YYYY-MM-DD') as day,
                 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
     )"""
     )
Example #51
0
 def init(self, cr):
        tools.drop_view_if_exists(cr, 'fg_sale_order_report_daily_source')
        cr.execute("""
            create or replace view fg_sale_order_report_daily_source as (
                SELECT
                     MIN(line."id")AS "id",
                     o.date_order AS DATE,
                     SUM(line.subtotal_amount)AS amount,
                     COALESCE(product.source, '未知来源') as source
                FROM
                     fg_sale_order_line line
                INNER JOIN fg_sale_order o ON line.order_id = o. ID
                INNER JOIN product_product product ON line.product_id = product."id"
                WHERE
                     (o."state" = 'done' OR o.minus = TRUE)
                AND(
                     o.date_order > CURRENT_DATE - INTERVAL '3 months'
                )
                GROUP BY
                     o.date_order,
                     product."source"
                ORDER BY
                     o.date_order ASC
            )
            """)
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'account_report_gl')
        cr.execute("""
            CREATE OR REPLACE VIEW account_report_gl AS 
 SELECT min(account_move_line.move_id) AS id, account_move_line.date, account_move_line.partner_id, account_move_line.move_id, account_move_line.account_id, sum(account_move_line.debit) AS debit, sum(account_move_line.credit) AS credit, sum(account_move_line.debit - account_move_line.credit) AS balance
   FROM account_move_line
  WHERE account_move_line.date > '2012-09-01'::date AND account_move_line.date < '2012-09-29'::date
  GROUP BY account_move_line.account_id, account_move_line.partner_id, account_move_line.move_id, account_move_line.date
        """)
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'report_stock_inventory')
        cr.execute("""
CREATE OR REPLACE view report_stock_inventory AS (
    (SELECT
        min(m.id) as id, m.date as date,
        m.partner_id as partner_id, m.location_id as location_id,
        m.product_id as product_id, pt.categ_id as product_categ_id, l.usage as location_type,
        m.company_id,
        m.state as state, m.prodlot_id as prodlot_id,
        coalesce(sum(-pt.standard_price * m.product_qty * pu.factor / u.factor)::decimal, 0.0) as value,
        CASE when pt.uom_id = m.product_uom
        THEN
        coalesce(sum(-m.product_qty)::decimal, 0.0)
        ELSE
        coalesce(sum(-m.product_qty * pu.factor / u.factor )::decimal, 0.0) END as product_qty
    FROM
        stock_move m
            LEFT JOIN stock_picking p ON (m.picking_id=p.id)
            LEFT JOIN product_product pp ON (m.product_id=pp.id)
                LEFT JOIN product_template pt ON (pp.product_tmpl_id=pt.id)
                LEFT JOIN product_uom pu ON (pt.uom_id=pu.id)
            LEFT JOIN product_uom u ON (m.product_uom=u.id)
            LEFT JOIN stock_location l ON (m.location_id=l.id)
        where pt.sale_ok = 't'
    GROUP BY
        m.id, m.product_id, m.product_uom, pt.categ_id, m.partner_id, m.location_id,  m.location_dest_id,
        m.prodlot_id, m.date, m.state, l.usage, m.company_id,pt.uom_id
) UNION ALL (
    SELECT
        -m.id as id, m.date as date,
        m.partner_id as partner_id, m.location_dest_id as location_id,
        m.product_id as product_id, pt.categ_id as product_categ_id, l.usage as location_type,
        m.company_id,
        m.state as state, m.prodlot_id as prodlot_id,
        coalesce(sum(pt.standard_price * m.product_qty * pu.factor / u.factor )::decimal, 0.0) as value,
        CASE when pt.uom_id = m.product_uom
        THEN
        coalesce(sum(m.product_qty)::decimal, 0.0)
        ELSE
        coalesce(sum(m.product_qty * pu.factor / u.factor)::decimal, 0.0) END as product_qty
    FROM
        stock_move m
            LEFT JOIN stock_picking p ON (m.picking_id=p.id)
            LEFT JOIN product_product pp ON (m.product_id=pp.id)
                LEFT JOIN product_template pt ON (pp.product_tmpl_id=pt.id)
                LEFT JOIN product_uom pu ON (pt.uom_id=pu.id)
            LEFT JOIN product_uom u ON (m.product_uom=u.id)
            LEFT JOIN stock_location l ON (m.location_dest_id=l.id)
     where pt.sale_ok = 't'
    GROUP BY
        m.id, m.product_id, m.product_uom, pt.categ_id, m.partner_id, m.location_id, m.location_dest_id,
        m.prodlot_id, m.date, m.state, l.usage, m.company_id,pt.uom_id
    )
);
        """)
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'crm_super_calendar')
     try:
         cr.execute("""
             create or replace view crm_super_calendar AS (
                 SELECT 
                     row_number() OVER ()::INTEGER AS id,
                     *
                 FROM (
                     SELECT 
                         partner_id as partner_id,
                         user_id as user_id, 
                         date_action::DATE as date_action,
                         concat('crm.lead,', id) AS source_id,
                         'lead_opportunity' as type
                         from crm_lead
                         where state not in ('done') and date_action is not Null
                     
                     UNION
                     
                     SELECT 
                         partner_id as partner_id,
                         user_id as user_id, 
                         date::DATE as date_action,
                         concat('crm.meeting,', id) AS source_id,
                         'crm_meeting' as type
                         from crm_meeting
                         where state not in ('done', 'cancel') and date is not Null
                     
                     UNION
                     
                     SELECT 
                         partner_id as partner_id,
                         user_id as user_id, 
                         date::DATE as date_action,
                         concat('crm.phonecall,', id) AS source_id,
                         'crm_phonecall' as type
                         from crm_phonecall
                         where state not in ('done', 'cancel') and date is not Null
                     
                     UNION
                     
                     SELECT
                         partner_id as partner_id,
                         user_id as user_id, 
                         date_action_next::DATE as date_action,
                         concat('sale.order,', id) AS source_id,
                         'sale_order' as type
                         from sale_order
                         where state not in ('done', 'cancel') and date_action_next is not Null
                 ) as A        
                 ) 
         """)
     except Exception as e:
         _logger.error(u'Error: {error}'.format(error=e))
Example #55
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'hr_expense_report')
     cr.execute("""
         create or replace view hr_expense_report as (
              select
                  min(l.id) as id,
                  date_trunc('day',s.date) as date,
                  s.employee_id,
                  s.journal_id,
                  s.currency_id,
                  to_date(to_char(s.date_confirm, 'dd-MM-YYYY'),'dd-MM-YYYY') as date_confirm,
                  to_date(to_char(s.date_valid, 'dd-MM-YYYY'),'dd-MM-YYYY') as date_valid,
                  s.invoice_id,
                  count(s.invoice_id) as invoiced,
                  s.user_valid as user_id,
                  s.department_id,
                  to_char(date_trunc('day',s.create_date), 'YYYY') as year,
                  to_char(date_trunc('day',s.create_date), 'MM') as month,
                  to_char(date_trunc('day',s.create_date), 'YYYY-MM-DD') as day,
                  avg(extract('epoch' from age(s.date_valid,s.date)))/(3600*24) as  delay_valid,
                  avg(extract('epoch' from age(s.date_valid,s.date_confirm)))/(3600*24) as  delay_confirm,
                  l.product_id as product_id,
                  l.analytic_account as analytic_account,
                  sum(l.unit_quantity * u.factor) as product_qty,
                  s.company_id as company_id,
                  sum(l.unit_quantity*l.unit_amount) as price_total,
                  (sum(l.unit_quantity*l.unit_amount)/sum(case when l.unit_quantity=0 or u.factor=0 then 1 else l.unit_quantity * u.factor end))::decimal(16,2) as price_average,
                  count(*) as nbr,
                  (select unit_quantity from hr_expense_line where id=l.id and product_id is not null) as no_of_products,
                  (select analytic_account from hr_expense_line where id=l.id and analytic_account is not null) as no_of_account,
                  s.state
              from hr_expense_line l
              left join hr_expense_expense s on (s.id=l.expense_id)
              left join product_uom u on (u.id=l.uom_id)
              group by
                  date_trunc('day',s.date),
                  to_char(date_trunc('day',s.create_date), 'YYYY'),
                  to_char(date_trunc('day',s.create_date), 'MM'),
                  to_char(date_trunc('day',s.create_date), 'YYYY-MM-DD'),
                  to_date(to_char(s.date_confirm, 'dd-MM-YYYY'),'dd-MM-YYYY'),
                  to_date(to_char(s.date_valid, 'dd-MM-YYYY'),'dd-MM-YYYY'),
                  l.product_id,
                  l.analytic_account,
                  s.invoice_id,
                  s.currency_id,
                  s.user_valid,
                  s.department_id,
                  l.uom_id,
                  l.id,
                  s.state,
                  s.journal_id,
                  s.company_id,
                  s.employee_id
         )
     """)
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'hr_agent_customer_commission')
     cr.execute("""
         create or replace view hr_agent_customer_commission as (
             select
                  min(s.id) as id,
                  s.amount_total as price_total,
                  s.date_order as date,
                  s.date_confirm as date_confirm,
                  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.internal_user_id as hr_employee_id,
                  s.id as sale_order_id,
                  s.trimester as trimester,
                  s.sale_commission_amount as sale_amount,
                  (case when s.flag_paid = True then
                      s.sale_commission_amount
                  else
                      0.0
                  end) as amount,
                  (case when s.state in ('cancel') then
                      s.sale_commission_amount
                  else
                      0.0
                  end) as invoice_amount,
                  s.sale_commission_amount as amount_for_graph,
                  p.res_partner_zone_id as zone_id,
                  s.partner_id as customer_id,
                  s.company_id as company_id,
                  s.state,
                  s.pricelist_id as pricelist_id,
                  s.project_id as analytic_account_id
             from
                  sale_order s
                  left join res_partner p on (p.id = s.partner_id)
             where
                  s.state not in ('draft','cancel')
              group by
                  s.amount_total,
                  s.date_order,
                  s.date_confirm,
                  s.internal_user_id,
                  s.id,
                  s.trimester,
                  s.sale_commission_amount,
                  s.flag_paid,
                  s.partner_id,
                  p.res_partner_zone_id,
                  s.company_id,
                  s.state,
                  s.pricelist_id,
                  s.project_id
         )
     """)
Example #57
0
    def init(self, cr):
        tools.drop_view_if_exists(cr, 'report_stock_inventory')
        cr.execute("""
CREATE OR REPLACE view report_stock_inventory AS (
    (SELECT
        min(m.id) as id, m.date as date,
        to_char(m.date, 'YYYY') as year,
        to_char(m.date, 'MM') as month,
        m.address_id as partner_id, m.location_id as location_id,
        m.product_id as product_id, pt.categ_id as product_categ_id, l.usage as location_type,
        m.company_id,
        m.state as state, m.prodlot_id as prodlot_id,

        coalesce(sum(-pt.standard_price * m.product_qty * pu.factor / pu2.factor)::decimal, 0.0) as value,
        coalesce(sum(-m.product_qty * pu.factor / pu2.factor)::decimal, 0.0) as product_qty
    FROM
        stock_move m
            LEFT JOIN stock_picking p ON (m.picking_id=p.id)
            LEFT JOIN product_product pp ON (m.product_id=pp.id)
                LEFT JOIN product_template pt ON (pp.product_tmpl_id=pt.id)
                LEFT JOIN product_uom pu ON (pt.uom_id=pu.id)
                LEFT JOIN product_uom pu2 ON (m.product_uom=pu2.id)
            LEFT JOIN product_uom u ON (m.product_uom=u.id)
            LEFT JOIN stock_location l ON (m.location_id=l.id)
            WHERE m.state != 'cancel'
    GROUP BY
        m.id, m.product_id, m.product_uom, pt.categ_id, m.address_id, m.location_id,  m.location_dest_id,
        m.prodlot_id, m.date, m.state, l.usage, m.company_id, pt.uom_id
) UNION ALL (
    SELECT
        -m.id as id, m.date as date,
        to_char(m.date, 'YYYY') as year,
        to_char(m.date, 'MM') as month,
        m.address_id as partner_id, m.location_dest_id as location_id,
        m.product_id as product_id, pt.categ_id as product_categ_id, l.usage as location_type,
        m.company_id,
        m.state as state, m.prodlot_id as prodlot_id,
        coalesce(sum(pt.standard_price * m.product_qty * pu.factor / pu2.factor)::decimal, 0.0) as value,
        coalesce(sum(m.product_qty * pu.factor / pu2.factor)::decimal, 0.0) as product_qty
    FROM
        stock_move m
            LEFT JOIN stock_picking p ON (m.picking_id=p.id)
            LEFT JOIN product_product pp ON (m.product_id=pp.id)
                LEFT JOIN product_template pt ON (pp.product_tmpl_id=pt.id)
                LEFT JOIN product_uom pu ON (pt.uom_id=pu.id)
                LEFT JOIN product_uom pu2 ON (m.product_uom=pu2.id)
            LEFT JOIN product_uom u ON (m.product_uom=u.id)
            LEFT JOIN stock_location l ON (m.location_dest_id=l.id)
            WHERE m.state != 'cancel'
    GROUP BY
        m.id, m.product_id, m.product_uom, pt.categ_id, m.address_id, m.location_id, m.location_dest_id,
        m.prodlot_id, m.date, m.state, l.usage, m.company_id, pt.uom_id
    )
);
        """)
Example #58
0
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'sale_report')
     cr.execute("""
         create or replace view sale_report as (
             select
                 min(l.id) as id,
                 l.product_id as product_id,
                 t.uom_id as product_uom,
                 sum(l.product_uom_qty / u.factor * u2.factor) as product_uom_qty,
                 sum(l.product_uom_qty * l.price_unit * (100.0-l.discount) / 100.0) as price_total,
                 1 as nbr,
                 s.date_order as date,
                 s.date_confirm as date_confirm,
                 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.user_id as user_id,
                 s.shop_id as shop_id,
                 s.company_id as company_id,
                 extract(epoch from avg(date_trunc('day',s.date_confirm)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay,
                 s.state,
                 t.categ_id as categ_id,
                 s.shipped,
                 s.shipped::integer as shipped_qty_1,
                 s.pricelist_id as pricelist_id,
                 s.project_id as analytic_account_id,
                 s.active
             from
                 sale_order s
                 left join sale_order_line l on (s.id=l.order_id)
                     left join product_product p on (l.product_id=p.id)
                         left join product_template t on (p.product_tmpl_id=t.id)
                 left join product_uom u on (u.id=l.product_uom)
                 left join product_uom u2 on (u2.id=t.uom_id)
             group by
                 l.product_id,
                 l.product_uom_qty,
                 l.order_id,
                 t.uom_id,
                 t.categ_id,
                 s.date_order,
                 s.date_confirm,
                 s.partner_id,
                 s.user_id,
                 s.shop_id,
                 s.company_id,
                 s.state,
                 s.shipped,
                 s.pricelist_id,
                 s.project_id,
                 s.active
         )
     """)
 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_document_file')
     cr.execute("""
         create or replace view report_document_file as (
             select min(f.id) as id,
                    count(*) as nbr,
                    min(EXTRACT(MONTH FROM f.create_date)||'-'||to_char(f.create_date,'Month')) as month,
                    sum(f.file_size) as file_size
             from ir_attachment f
             group by EXTRACT(MONTH FROM f.create_date)
          )
     """)