Python tools.drop_view_if_exists函数代码示例

 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
         )
     """)

    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, '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, "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, '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
         )
     """)

    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
);
  
        """)

 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
          )
     """)

    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, '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, "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_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'
            )
        """)

 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
         )
         """)

 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
                         
                     )
                """)

    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, '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
         )
     """)