Python sql.drop_view_if_exists函数代码示例

        def init(self, cr):
                drop_view_if_exists(cr, "chricar_location_move_col")
                cr.execute("""
                create or replace view chricar_location_move_col as (
select id,company_id, product_id,location_id, picking_id, prodlot_id,
       date, date::date as date2,period_id,
       product_qty,
       case when product_qty >0 then product_qty else 0 end as qty_plus ,
       case when product_qty <0 then product_qty else 0 end as qty_minus,
       0 as qty_inventory,
       move_value_cost,
       case when move_value_cost >0 then move_value_cost else 0 end as cost_plus ,
       case when move_value_cost <0 then move_value_cost else 0 end as cost_minus,
       0 as cost_inventory,state
       from chricar_report_location_moves
       where state ='done' 
       and id not in (select 2*move_id from stock_inventory_move_rel
                      union
                      select 2*move_id-1 from stock_inventory_move_rel)
union all      
select id,company_id, product_id, location_id, picking_id, prodlot_id,
       date,date::date as date2,period_id,
       product_qty,
       0,0,
       product_qty as qty_inventory,
       move_value_cost,
       0,0,
       move_value_cost as cost_inventory,state
       from chricar_report_location_moves
       where state ='done'
       and id  in (select 2*move_id from stock_inventory_move_rel
                      union
                      select 2*move_id-1 from stock_inventory_move_rel)
)""")

    def init(self, cr):
            drop_view_if_exists(cr, "account_account_fiscalyear_sum")
            cr.execute("""create or replace view account_account_fiscalyear_sum
            as select
            --sum_fy_period_id as id, -- FIXME - how to create a unique seq. number to avoid chricar_view_id ?
            min(s.id) as id,
            s.company_id,
            account_id,
            to_char(y.date_stop,'YYYY') || case when to_char(y.date_stop,'MM')  != '12'
                                            then  '-'||to_char(y.date_stop,'MM')
                                            else '' end as name,
            y.id as fiscalyear_id,
            sum(case when s.name like '%00' then 0 else debit end) as debit,
            sum(case when s.name like '%00' then 0 else credit end) as credit,
            sum(debit) - sum(credit) as balance,
            sum(case when s.name like '%00' then debit - credit else 0 end) as opening_balance,
            y.date_start,y.date_stop
            from account_account_period_sum s,
                account_period p,
                account_fiscalyear y
            where p.id = s.period_id
            and y.id = p.fiscalyear_id
            group by s.company_id, sum_fy_period_id,  s.account_id, y.id,
	        to_char(y.date_stop,'YYYY') || case when to_char(y.date_stop,'MM')  != '12'
                                            then  '-'||to_char(y.date_stop,'MM')
                                            else '' end	    ,
	        y.date_start,y.date_stop""")

 def init(self, cr):
     drop_view_if_exists(cr, 'report_timesheet_line')
     cr.execute("""
         create or replace view report_timesheet_line as (
             select
                 min(l.id) as id,
                 l.date as date,
                 to_char(l.date,'YYYY') as name,
                 to_char(l.date,'MM') as month,
                 l.user_id,
                 to_char(l.date, 'YYYY-MM-DD') as day,
                 l.invoice_id,
                 l.product_id,
                 l.account_id,
                 l.general_account_id,
                 sum(l.unit_amount) as quantity,
                 sum(l.amount) as cost
             from
                 account_analytic_line l
             where
                 l.user_id is not null
             group by
                 l.date,
                 l.user_id,
                 l.product_id,
                 l.account_id,
                 l.general_account_id,
                 l.invoice_id
         )
     """)

 def init(self, cr):
     drop_view_if_exists(cr, "intrastat")
     drop_view_if_exists(cr, "l10n_hr_intrastat")
     cr.execute(
         """
         create or replace view intrastat as (
             SELECT 
                 SP.id ,
                 SP.name as picking_name,
                 to_char(SP.date_done, 'YYYY') || '-' || to_char(SP.date_done, 'MM') as date,
                 to_char(SP.date_done, 'MM') as month,
                 to_char(SP.date_done, 'YYYY') as year,
                 SP.partner_id as partner_id,
                 PA.name as partner,
                 C.code as country_code,
                 C.intrastat as country_intrastat,
                 SP.transaction_type_id as transaction_type_id,
                 SP.invoice_state as invoice_state,
                 URA.number as ura,
                 PTEM.intrastat_id as intrastat_id,
                 PPROD.id as product_id
                 
             FROM stock_picking as SP
                 LEFT JOIN res_partner PA on (PA.id = SP.partner_id)
                 LEFT JOIN res_country C on C.id = PA.country_id
                 LEFT JOIN account_invoice URA on URA.id = SP.purchase_id
                 LEFT JOIN stock_move SM on SM.picking_id=SP.id
                 left join (product_template PTEM
                     left join product_product PPROD on (PPROD.product_tmpl_id = PTEM.id))
                     on (SM.product_id = PPROD.id)
                 
                 /*croatia_id=98*   and c.id <>'98' */
             WHERE SP.state='done' 
                 )"""
     )

 def init(self, cr):
     drop_view_if_exists(cr, 'intrastat')
     cr.execute("""
         create or replace view intrastat as (
             select
                 MIN (SM.id) AS id,
                 to_char(SP.date_done, 'YYYY') || '-' || to_char(SP.date_done, 'MM') as date,
                 to_char(SP.date_done, 'MM') as month,
                 to_char(SP.date_done, 'YYYY') as year,
                 SP.transaction_type_id as transaction_type_id,
                 PT.intrastat_id AS intrastat_id,
                 SUM (SM.product_qty * PT.weight_net) AS weight,
                 SUM (SM.product_qty * SM.price_unit) AS value,
                 SM.country_origin_id AS country_origin,
                 PA.country_id AS country_supplier
             from
                 stock_picking SP
                 left join stock_move SM on SM.picking_id=SP.id
                 left join (product_template PT
                     left join product_product PP on (PP.product_tmpl_id = PT.id))
                     on (SM.product_id = PP.id)
                 left join res_partner PA on PA.id = SP.partner_id
                 left join res_country C on C.id = PA.country_id
             where
                 SP.state = 'done' AND PA.country_id != 201 AND SP.type = 'in' AND C.intrastat = true
             group by to_char(SP.date_done, 'YYYY') || '-' || to_char(SP.date_done, 'MM'), to_char(SP.date_done, 'MM'), to_char(SP.date_done, 'YYYY'),
                 SP.transaction_type_id, PT.intrastat_id, SM.country_origin_id, PA.country_id
         )""")

    def init(self, cr):
      drop_view_if_exists(cr, "account_account_period_sum_delta") # ???
      drop_view_if_exists(cr, "account_account_period_sum_cur_prev")
      cr.execute("""
create or replace view account_account_period_sum_cur_prev as
  select 
      c.id*2 as id, 
      c.company_id,
      account_id, 
      p.id as period_id,
      p.fiscalyear_id,
      case when c.name like '%00' then '00'  else p.code end as name, 
      c.debit-c.credit as balance_curr ,0 as balance_prev, 
      case when c.name like '%00' then p.date_start -1 else p.date_start end as date_start
    from 
      account_period p left outer join account_account_period_sum c on (c.period_id = p.id)
union 
  select 
      c.id*2 -1 as id,
      c.company_id,
      account_id,p.id as period_id,
      p.fiscalyear_id, 
      case when c.name like '%00' then '00'  else p.code end as name, 
      0 as balance_curr, 
      c.debit-c.credit  as balance_prev,
      case when c.name like '%00' then p.date_start -1 else p.date_start end as date_start
    from 
      account_period p left outer join account_account_period_sum c on (c.period_id = p.prev_fy_period);
""")

 def init(self, cr):
     drop_view_if_exists(cr, 'account_invoice_duplicate_line')
     cr.execute("""CREATE OR REPLACE VIEW account_invoice_duplicate_line AS
                   (WITH invoices AS (SELECT ail.invoice_id, ail.product_id, ROUND(AVG(ail.price_unit), 4) AS price_avg
                                        FROM account_invoice_line AS ail
                                             INNER JOIN account_invoice AS ai ON ai.id = ail.invoice_id
                                                                                 AND ai.type IN ('out_invoice', 'out_refund')
                                       WHERE ail.product_id IS NOT NULL
                                             AND ail.quantity <> 0.0
                                    GROUP BY ail.invoice_id, ail.product_id
                                      HAVING COUNT(ail.product_id) > 1
                                    ORDER BY ail.invoice_id),
                         in_lines AS (SELECT ai.id AS invoice_id,
                                             ai.partner_id AS partner_id,
                                             ail.product_id AS product_id,
                                             ai.state AS state
                                        FROM account_invoice_line AS ail
                                             INNER JOIN account_invoice AS ai ON ai.id = ail.invoice_id
                                                                                 AND ai.type IN ('out_invoice', 'out_refund')
                                             INNER JOIN invoices AS inv ON inv.invoice_id = ail.invoice_id
                                                                           AND inv.product_id = ail.product_id
                                                                           AND inv.price_avg <> ail.price_unit
                                    GROUP BY ai.id, ai.partner_id, ail.product_id, ai.state)
                      SELECT row_number() OVER (ORDER BY invoice_id, product_id) AS id,
                             invoice_id,
                             partner_id,
                             product_id,
                             state
                        FROM in_lines
                    ORDER BY invoice_id, product_id);""")

     def init(self, cr):
          drop_view_if_exists(cr, 'stock_product_by_location_prodlot')
          cr.execute("""create or replace view stock_product_by_location_prodlot
as
select min(id) as id ,location_id,product_id,prodlot_id,
       sum(name) as name, sum(product_qty_pending) as product_qty_pending, 
       company_id
 from stock_move_by_location
group by location_id,prodlot_id,product_id,company_id
having round(sum(name),4) != 0
;""")

 def init(self, cr):
     drop_view_if_exists(cr, "stock_product_moves")
     cr.execute(
         """CREATE OR REPLACE VIEW stock_product_moves AS
                   (SELECT sm.id AS id,
                           sm.product_id AS product_id,
                           sp.id AS picking_id,
                           sm.date AS date,
                           pa.id AS partner_id,
                           CASE
                             WHEN sp.type = 'in'
                               THEN pai.id
                             WHEN sp.type = 'out'
                               THEN sai.id
                             ELSE NULL
                           END AS invoice_id,
                           CASE
                             WHEN sp.type = 'in'
                                  OR sp.id IS NULL
                               THEN sm.product_qty
                             ELSE NULL
                           END AS qty_received,
                           CASE
                             WHEN sp.type = 'out'
                               THEN sm.product_qty
                             ELSE NULL
                           END AS qty_delivered,
                           (SELECT SUM(CASE
                                         WHEN sp1.type = 'in'
                                              OR sp1.id IS NULL
                                           THEN sm1.product_qty
                                         ELSE sm1.product_qty * -1
                                       END)
                              FROM stock_move AS sm1
                                   LEFT OUTER JOIN stock_picking AS sp1 ON sp1.id = sm1.picking_id
                             WHERE sm1.product_id = sm.product_id
                                   AND sm1.state = 'done'
                                   AND sm1.date <= sm.date) AS qty_stock,
                           sm.price_unit AS price_unit,
                           sm.product_qty * sm.price_unit AS amount
                      FROM stock_move AS sm
                           LEFT OUTER JOIN res_partner AS pa ON pa.id = sm.partner_id
                           LEFT OUTER JOIN stock_picking AS sp ON sp.id = sm.picking_id
                           LEFT OUTER JOIN sale_order_line_invoice_rel AS solir ON solir.order_line_id = sm.sale_line_id
                           LEFT OUTER JOIN purchase_order_line_invoice_rel AS polir ON polir.order_line_id = sm.purchase_line_id
                           LEFT OUTER JOIN account_invoice_line AS sail ON sail.id = solir.invoice_id
                           LEFT OUTER JOIN account_invoice AS sai ON sai.id = sail.invoice_id
                           LEFT OUTER JOIN account_invoice_line AS pail ON pail.id = polir.invoice_id
                           LEFT OUTER JOIN account_invoice AS pai ON pai.id = pail.invoice_id
                     WHERE sm.state = 'done');"""
     )

    def init(self, cr):
        drop_view_if_exists(cr, "report_intrastat")
        cr.execute(
            """
            create or replace view report_intrastat as (
                select
                    to_char(inv.create_date, 'YYYY') as name,
                    to_char(inv.create_date, 'MM') as month,
                    min(inv_line.id) as id,
                    intrastat.id as intrastat_id,
                    upper(inv_country.code) as code,
                    sum(case when inv_line.price_unit is not null
                            then inv_line.price_unit * inv_line.quantity
                            else 0
                        end) as value,
                    sum(
                        case when uom.category_id != puom.category_id then (pt.weight_net * inv_line.quantity)
                        else (pt.weight_net * inv_line.quantity * uom.factor) end
                    ) as weight,
                    sum(
                        case when uom.category_id != puom.category_id then inv_line.quantity
                        else (inv_line.quantity * uom.factor) end
                    ) as supply_units,

                    inv.currency_id as currency_id,
                    inv.number as ref,
                    case when inv.type in ('out_invoice','in_refund')
                        then 'export'
                        else 'import'
                        end as type
                from
                    account_invoice inv
                    left join account_invoice_line inv_line on inv_line.invoice_id=inv.id
                    left join (product_template pt
                        left join product_product pp on (pp.product_tmpl_id = pt.id))
                    on (inv_line.product_id = pp.id)
                    left join product_uom uom on uom.id=inv_line.uos_id
                    left join product_uom puom on puom.id = pt.uom_id
                    left join report_intrastat_code intrastat on pt.intrastat_id = intrastat.id
                    left join (res_partner inv_address
                        left join res_country inv_country on (inv_country.id = inv_address.country_id))
                    on (inv_address.id = inv.partner_id)
                where
                    inv.state in ('open','paid')
                    and inv_line.product_id is not null
                    and inv_country.intrastat=true
                group by to_char(inv.create_date, 'YYYY'), to_char(inv.create_date, 'MM'),intrastat.id,inv.type,pt.intrastat_id, inv_country.code,inv.number,  inv.currency_id
            )"""
        )

    def create(self, cr, uid, vals, context=None):
        """
        Dynamicaly declare the wizard for this document
        """
        if context is None:
            context = {}
        doc_id = super(jasper_document, self).create(cr, uid, vals, context=context)
        self.make_action(cr, uid, doc_id, context=context)

        # Check if view and create it in the database
        if vals.get('sql_name') and vals.get('sql_view'):
            drop_view_if_exists(cr, vals.get('sql_name'))
            sql_query = 'CREATE OR REPLACE VIEW %s AS\n%s' % (vals['sql_name'], vals['sql_view'])
            cr.execute(sql_query)
        return doc_id

 def init(self, cr):
     drop_view_if_exists(cr, 'report_stock_lines_date')
     cr.execute("""
         create or replace view report_stock_lines_date as (
             select
             p.id as id,
             p.id as product_id,
             max(s.date) as date
         from
             product_product p
                 left outer join stock_inventory_line l on (p.id=l.product_id)
                 left join stock_inventory s on (l.inventory_id=s.id)
             and s.state = 'done'
             group by p.id
         )""")

 def init(self, cr):
     drop_view_if_exists(cr, 'report_profit_partner')
     cr.execute("""
         create or replace view report_profit_partner as (
         select
             partner_id as id,
             partner_id,        
             SUM(last_cost) as sum_last_cost,
             SUM(price_subtotal) as sum_price_subtotal,
             SUM(qty_consol) as sum_qty_consol,
             p_uom_c_id
         from report_profit p
         group by partner_id,p_uom_c_id
         )
     """)

 def init(self, cr):
     drop_view_if_exists(cr, 'random_timesheet_lines')
     
     cr.execute("""create or replace view random_timesheet_lines as (
         select 
             line.id as id, line.date as date, line.name as name, line.unit_amount as quantity,
             line.product_id as product_id, line.account_id as analytic_account_id,
             line.product_uom_id as uom_id, line.amount as amount, line.to_invoice as to_invoice,
             line.general_account_id as general_account_id, line.user_id as user_id 
         from 
             account_analytic_line line
         where
             (line.date <= CURRENT_DATE AND line.date > (CURRENT_DATE-15))
         )
         """ )

 def init(self, cr):
     drop_view_if_exists(cr, 'report_profit_uxc')
     cr.execute("""
         create or replace view report_profit_uxc as (
         select
             ((user_id*1000000)+cat_id) as id,
             user_id,
             cat_id,        
             SUM(last_cost) as sum_last_cost,
             SUM(price_subtotal) as sum_price_subtotal,
             SUM(qty_consol) as sum_qty_consol,
             p_uom_c_id
         from report_profit p
         group by user_id,cat_id,p_uom_c_id
         )
     """)