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

    def init(self, cr):
        """creates view when install"""
        drop_view_if_exists(cr, 'report_partner_affected_bycase')

        cr.execute("""
            create or replace view report_partner_affected_bycase as (
                select stock_move.id as id, prodlots.id as prodlot_id, prodlots.case_id, stock_move.picking_id, stock_move.date,
                stock_move.sale_line_id, res_partner.id as partner_id,
                res_partner.vat, res_partner.fax, res_partner.city, res_partner.phone,
                res_partner.zip, res_partner.country_id, res_partner.email, prodlots.product_id as product_id,
                stock_move.product_qty
                from stock_move inner join
                (select distinct stock_production_lot.*, case_id from stock_production_lot inner join
                blocked_prodlots_cases_ids on id = blocked_prodlot) as prodlots
                on stock_move.prodlot_id = prodlots.id inner join
                stock_location on stock_move.location_dest_id = stock_location.id inner join
                res_partner on res_partner.id = stock_location.partner_id) """)

 def init(self, cr, context=None):
     sql="""
         CREATE or REPLACE view intrastat_preview as( 
         SELECT 
             row_number() OVER (ORDER BY SP.id) as id  ,
             SP.id as picking_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,
             CPAR.code as country_code,
             CPAR.intrastat as country_intrastat,
             SP.transaction_type_id as transaction_type_id,
             SP.invoice_state as invoiced_state,
             URA.id as ura_id,
             URA.number as ura,
             IRA.id as ira_id,
             IRA.number as ira,
             PPROD.id as product_id,
             PTEM.name as product_name,
             PTEM.intrastat_id as intrastat_id,
             PTEM.weight_net as weight_net,
             CPRO.code as country_origin
             
             
         FROM stock_picking as SP
             LEFT JOIN res_partner PA on (PA.id = SP.partner_id)
             LEFT JOIN res_country CPAR on CPAR.id = PA.country_id
             LEFT JOIN account_invoice URA on URA.id = SP.purchase_id
             LEFT JOIN account_invoice IRA on IRA.id = SP.sale_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)
             LEFT JOIN res_country CPRO on CPRO.id = PTEM.country_origin
             
             /*croatia_id=98*   AND CPAR.id <> '98' */
         WHERE 
             SP.state='done' AND SP.type<>'internal' AND CPAR.id <> '98'
     
         )
         """
     drop_view_if_exists(cr, 'intrastat_preview')
     cr.execute(sql)

 def init(self, cr):
     drop_view_if_exists(cr, 'stock_report_prodlots')
     cr.execute("""
         create or replace view stock_report_prodlots as (
             select max(id) as id,
                 location_id,
                 product_id,
                 prodlot_id,
                 sum(qty) as qty
             from (
                 select -max(sm.id) as id,
                     sm.location_id,
                     sm.product_id,
                     sm.prodlot_id,
                     -sum(sm.product_qty / uo.factor * pu.factor) as qty
                 from stock_move as sm
                 left join stock_location sl
                     on (sl.id = sm.location_id)
                 left join product_uom uo
                     on (uo.id=sm.product_uom)
                 left join product_product pp on (sm.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)
                 where sm.state = 'done'
                 group by sm.location_id, sm.product_id, sm.product_uom, sm.prodlot_id
                 union all
                 select max(sm.id) as id,
                     sm.location_dest_id as location_id,
                     sm.product_id,
                     sm.prodlot_id,
                     sum(sm.product_qty / uo.factor * pu.factor) as qty
                 from stock_move as sm
                 left join stock_location sl
                     on (sl.id = sm.location_dest_id)
                 left join product_uom uo
                     on (uo.id=sm.product_uom)
                 left join product_product pp on (sm.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)
                 where sm.state = 'done'
                 group by sm.location_dest_id, sm.product_id, sm.product_uom, sm.prodlot_id
             ) as report
             group by location_id, product_id, prodlot_id
         )""")

    def init(self, cr):
        """overwrites the create query"""
        drop_view_if_exists(cr, 'stock_report_prodlots')

        cr.execute("""
            create or replace view stock_report_prodlots as (
                select max(id) as id,
                    location_id,
                    product_id,
                    prodlot_id,
                    partner_id,
                    sum(qty) as qty
                from (
                    select -max(sm.id) as id,
                        sm.location_id,
                        sm.product_id,
                        sm.prodlot_id,
                        sl.partner_id,
                        -sum(sm.product_qty /uo.factor) as qty
                    from stock_move as sm
                    left join stock_location sl
                        on (sl.id = sm.location_id)
                    left join product_uom uo
                        on (uo.id=sm.product_uom)
                    where state = 'done'
                    group by sm.location_id, sm.product_id, sm.product_uom, sm.prodlot_id, sl.partner_id
                    union all
                    select max(sm.id) as id,
                        sm.location_dest_id as location_id,
                        sm.product_id,
                        sm.prodlot_id,
                        sl.partner_id,
                        sum(sm.product_qty /uo.factor) as qty
                    from stock_move as sm
                    left join stock_location sl
                        on (sl.id = sm.location_dest_id)
                    left join product_uom uo
                        on (uo.id=sm.product_uom)
                    where sm.state = 'done'
                    group by sm.location_dest_id, sm.product_id, sm.product_uom, sm.prodlot_id, sl.partner_id
                ) as report
                group by location_id, product_id, prodlot_id, partner_id
            )""")

    def init(self, cr):
        drop_view_if_exists(cr, "stock_report_tracklots")
        cr.execute(
            """
           create or replace view stock_report_tracklots as (

            select max(id) as id,
                    location_id,
                    product_id,
                    tracking_id,
                    sum(qty) as name
                from (
                    select -max(sm.id) as id,
                        sm.location_id,
                        sm.product_id,
                        sm.tracking_id,
                        -sum(sm.product_qty /uo.factor) as qty
                    from stock_move as sm
                    left join stock_location sl
                        on (sl.id = sm.location_id)
                    left join product_uom uo
                        on (uo.id=sm.product_uom)
                    where state = 'done'
                    group by sm.location_id, sm.product_id, sm.product_uom, sm.tracking_id
                    union all
                    select max(sm.id) as id,
                        sm.location_dest_id as location_id,
                        sm.product_id,
                        sm.tracking_id,
                        sum(sm.product_qty /uo.factor) as qty
                    from stock_move as sm
                    left join stock_location sl
                        on (sl.id = sm.location_dest_id)
                    left join product_uom uo
                        on (uo.id=sm.product_uom)
                    where sm.state = 'done'
                    group by sm.location_dest_id, sm.product_id, sm.product_uom, sm.tracking_id
                ) as report
                group by location_id, product_id, tracking_id
            )"""
        )