コード例 #1
0
 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' 
                 )"""
     )
コード例 #2
0
 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
         )
     """)
コード例 #3
0
 def init(self, cr):
     drop_view_if_exists(cr, 'report_timesheet_invoice')
     cr.execute("""
         create or replace view report_timesheet_invoice as (
             select
                 min(l.id) as id,
                 l.user_id as user_id,
                 l.account_id as account_id,
                 a.user_id as manager_id,
                 sum(l.unit_amount) as quantity,
                 sum(l.unit_amount * t.list_price) as amount_invoice
             from account_analytic_line l
                 left join hr_timesheet_invoice_factor f on (l.to_invoice=f.id)
                 left join account_analytic_account a on (l.account_id=a.id)
                 left join product_product p on (l.to_invoice=f.id)
                 left join product_template t on (l.to_invoice=f.id)
             where
                 l.to_invoice is not null and
                 l.invoice_id is null
             group by
                 l.user_id,
                 l.account_id,
                 a.user_id
         )
     """)
コード例 #4
0
 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);""")
コード例 #5
0
 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_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, 'report_timesheet_invoice')
     cr.execute("""
         create or replace view report_timesheet_invoice as (
             select
                 min(l.id) as id,
                 l.user_id as user_id,
                 l.account_id as account_id,
                 a.user_id as manager_id,
                 sum(l.unit_amount) as quantity,
                 sum(l.unit_amount * t.list_price) as amount_invoice
             from account_analytic_line l
                 left join hr_timesheet_invoice_factor f on (l.to_invoice=f.id)
                 left join account_analytic_account a on (l.account_id=a.id)
                 left join product_product p on (l.to_invoice=f.id)
                 left join product_template t on (l.to_invoice=f.id)
             where
                 l.to_invoice is not null and
                 l.invoice_id is null
             group by
                 l.user_id,
                 l.account_id,
                 a.user_id
         )
     """)
 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
         )
     """)
コード例 #9
0
    def write(self, cr, uid, ids, vals, context=None):
        """
        If the description change, we must update the action
        """
        if context is None:
            context = {}

        if vals.get('sql_name') or vals.get('sql_view'):
            sql_name = vals.get('sql_name',
                                self.browse(cr, uid, ids[0]).sql_name)
            sql_view = vals.get('sql_view',
                                self.browse(cr, uid, ids[0]).sql_view)
            drop_view_if_exists(cr, sql_name)
            sql_query = 'CREATE OR REPLACE VIEW %s AS\n%s' % (sql_name,
                                                              sql_view)
            cr.execute(sql_query, (ids, ))

        res = super(jasper_document, self).write(cr,
                                                 uid,
                                                 ids,
                                                 vals,
                                                 context=context)

        if not context.get('action'):
            for id in ids:
                self.make_action(cr, uid, id, context=context)

            if 'enabled' in vals:
                if vals['enabled']:
                    for id in ids:
                        self.create_values(cr, uid, id, context)
                else:
                    for id in ids:
                        self.unlink_values(cr, uid, id, context)
        return res
コード例 #10
0
    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);
""")
コード例 #11
0
    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""")
コード例 #12
0
    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);
""")
コード例 #13
0
        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)
)""")
コード例 #14
0
 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
         )""")
コード例 #15
0
    def write(self, cr, uid, ids, vals, context=None):
        """
        If the description change, we must update the action
        """
        if context is None:
            context = {}

        if vals.get('sql_name') or vals.get('sql_view'):
            sql_name = vals.get('sql_name', self.browse(cr, uid, ids[0]).sql_name)
            sql_view = vals.get('sql_view', self.browse(cr, uid, ids[0]).sql_view)
            drop_view_if_exists(cr, sql_name)
            sql_query = 'CREATE OR REPLACE VIEW %s AS\n%s' % (sql_name, sql_view)
            cr.execute(sql_query, (ids,))

        res = super(jasper_document, self).write(cr, uid, ids, vals, context=context)

        if not context.get('action'):
            for id in ids:
                self.make_action(cr, uid, id, context=context)

            if 'enabled' in vals:
                if vals['enabled']:
                    for id in ids:
                        self.create_values(cr, uid, id, context)
                else:
                    for id in ids:
                        self.unlink_values(cr, uid, id, context)
        return res
コード例 #16
0
 def init(self, cr):
         drop_view_if_exists(cr, "account_account_with_postings")
         cr.execute("""create or replace view account_account_with_postings
         as select
         a.id, a.code, a.name, a.company_id, a.shortcut, a.currency_id, a.note, a.user_type, a.reconcile
         from account_account a
         where exists ( select 'x' from account_account_period_sum where account_id = a.id and company_id = a.company_id);
         """)
コード例 #17
0
     def init(self, cr):
          drop_view_if_exists(cr, 'chricar_stock_product_by_location_prodlot')
          cr.execute("""create or replace view chricar_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, sum(move_value_cost) as amount,
       case when sum(name+product_qty_pending) != 0 then sum(move_value_cost)/sum(name+product_qty_pending) else 0 end as average_price,company_id
 from chricar_stock_move_by_location
group by location_id,prodlot_id,product_id,company_id
having round(sum(name),4) != 0 or round(sum(move_value_cost),2) != 0
;""")
コード例 #18
0
     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
;""")
コード例 #19
0
    def init(self, cr):
      drop_view_if_exists(cr, "account_account_period_sum_delta")
      cr.execute("""
create or replace view account_account_period_sum_delta as
select min(id) as id, company_id,account_id, name,period_id,date_start,fiscalyear_id,
   sum(balance_curr) as balance_curr,sum(balance_prev) as balance_prev,
   sum(balance_curr) - sum(balance_prev) as diff,
   case when sum(balance_prev) != 0 then ((sum(balance_curr) / sum(balance_prev)) -1)*100 else 0.0 end as diff_pro
from account_account_period_sum_cur_prev
group by company_id,account_id, name, period_id,fiscalyear_id,date_start
having company_id >0
;
""")
コード例 #20
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');"""
     )
コード例 #21
0
    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
            )"""
        )
コード例 #22
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');""")
コード例 #23
0
 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
         )
     """)
コード例 #24
0
 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
         )""")
コード例 #25
0
 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
         )""")
コード例 #26
0
ファイル: jasper_document.py プロジェクト: goldenboy/razvoj
    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
コード例 #27
0
 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))
         )
         """ )
コード例 #28
0
    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_address inv_address
                        left join res_country inv_country on (inv_country.id = inv_address.country_id))
                    on (inv_address.id = inv.address_invoice_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
            )""")
コード例 #29
0
 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
         )
     """)
コード例 #30
0
 def init(self, cr):
     drop_view_if_exists(cr, 'report_timesheet_account_date')
     cr.execute("""
         create or replace view report_timesheet_account_date as (
             select
                 min(id) as id,
                 date as name,
                 user_id,
                 account_id,
                 sum(unit_amount) as quantity
             from
                 account_analytic_line
             group by
                 date, user_id, account_id
         )
     """)
コード例 #31
0
 def init(self, cr):
         drop_view_if_exists(cr, "chricar_report_location_moves_sum")
         cr.execute("""
         create or replace view chricar_report_location_moves_sum
         as ( 
         select min(id) as id, 
          product_id,location_id, 
          sum(product_qty) as product_qty,
          sum(move_value_cost) as move_value_cost,
          sum(move_value_sale) as move_value_sale, 
          case when sum(product_qty) != 0 then  sum(move_value_cost)/sum(product_qty)  else 0 end as cost_price,
          case when sum(product_qty) != 0 then  sum(move_value_sale)/sum(product_qty)  else 0 end as sale_price
         from chricar_report_location_moves
         where state ='done'
         group by product_id,location_id
            )
         """)
コード例 #32
0
ファイル: l10n_hr_intrastat.py プロジェクト: badbole/mikra
 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)
コード例 #33
0
    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) """)
コード例 #34
0
    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) """)
コード例 #35
0
    def init(self, cr, uid=1):
        drop_view_if_exists(cr, 'report_random_timesheet')
        
        cr.execute("""create or replace view report_random_timesheet as (

            select 
                line.id as id, line.account_id as analytic_account_id, line.name as name,
                line.unit_amount as quantity, line.date as date, line.user_id as user_id
            from 
                account_analytic_line line, hr_department dept,hr_department_user_rel dept_user
            where
                (dept.id = dept_user.department_id AND dept_user.user_id=line.user_id AND line.user_id is not null)
                AND (dept.manager_id = %s)
                AND (line.date <= CURRENT_DATE AND line.date > (CURRENT_DATE-3))
            LIMIT 10
            )
            """, (uid,))
コード例 #36
0
 def init(self, cr):
     drop_view_if_exists(cr, 'report_timesheet_user')
     cr.execute("""
         create or replace view report_timesheet_user as (
             select
                 min(l.id) as id,
                 l.date as name,
                 l.user_id,
                 sum(l.unit_amount) as quantity,
                 sum(l.amount) as cost
             from
                 account_analytic_line l
             where
                 user_id is not null
             group by l.date, l.user_id
         )
     """)
コード例 #37
0
    def init(self, cr):
        drop_view_if_exists(cr, "account_account_with_postings")
        cr.execute("""
create or replace view account_account_with_postings as 
  select
      a.id, 
      a.code, 
      a.name, 
      a.company_id, 
      a.shortcut, 
      a.currency_id, 
      a.note, 
      a.user_type, 
      a.reconcile
    from account_account a
    where exists (select 'x' from account_account_period_sum where account_id = a.id and company_id = a.company_id);
""")
コード例 #38
0
 def init(self, cr):
     drop_view_if_exists(cr, 'report_timesheet_account_date')
     cr.execute("""
         create or replace view report_timesheet_account_date as (
             select
                 min(id) as id,
                 to_char(date,'YYYY') as name,
                 to_char(date,'MM') as month,
                 user_id,
                 account_id,
                 sum(unit_amount) as quantity
             from
                 account_analytic_line
             group by
                 to_char(date,'YYYY'),to_char(date,'MM'), user_id, account_id
         )
     """)
 def init(self, cr):
     drop_view_if_exists(cr, 'report_timesheet_account')
     cr.execute("""
         create or replace view report_timesheet_account as (
             select
                 min(id) as id,
                 to_char(create_date, 'YYYY') as name,
                 to_char(create_date,'MM') as month,
                 user_id,
                 account_id,
                 sum(unit_amount) as quantity
             from
                 account_analytic_line
             group by
                 to_char(create_date, 'YYYY'),to_char(create_date, 'MM'), user_id, account_id
         )
     """)
コード例 #40
0
ファイル: report_stock.py プロジェクト: vseal001/wms-4
 def init(self, cr):
     drop_view_if_exists(cr, 'wms_report_stock_available')
     cr.execute("""
             CREATE OR REPLACE VIEW wms_report_stock_available AS (
                 SELECT  max(sub.id) AS id,
                         sl.company_id,
                         sl.warehouse_id,
                         sub.location_id,
                         sub.product_id,
                         pt.uom_id,
                         sub.prodlot_id,
                         sub.usage,
                         sum(sub.qty) AS product_qty,
                         0.0 as product_warehouse_qty
                 FROM (
                        SELECT   -max(sm.id) AS id,
                                 sm.location_id,
                                 sm.product_id,
                                 sm.prodlot_id,
                                 sl.usage,
                                 -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' AND sm.location_id != sm.location_dest_id
                        GROUP BY sm.location_id, sm.product_id, sm.product_uom, sm.prodlot_id, sl.usage
                        UNION ALL
                        SELECT   max(sm.id) AS id,
                                 sm.location_dest_id AS location_id,
                                 sm.product_id,
                                 sm.prodlot_id,
                                 sl.usage,
                                 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' AND sm.location_id != sm.location_dest_id
                        GROUP BY sm.location_dest_id, sm.product_id, sm.product_uom, sm.prodlot_id, sl.usage
                 ) AS sub
                 LEFT JOIN stock_location sl ON sl.id = sub.location_id
                 LEFT JOIN product_product pp ON pp.id = sub.product_id
                 LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
                 GROUP BY sub.location_id, sub.product_id, sub.prodlot_id, sub.usage, sl.warehouse_id, pt.uom_id, sl.company_id
                 HAVING sum(sub.qty) > 0)
     """)
コード例 #41
0
    def init(self, cr):
        drop_view_if_exists(cr, "account_account_period_sum_delta")
        cr.execute("""
create or replace view account_account_period_sum_delta as
  select 
      min(id) as id, 
      company_id,account_id, 
      name,period_id,
      date_start,
      fiscalyear_id,
      sum(balance_curr) as balance_curr,
      sum(balance_prev) as balance_prev,
      sum(balance_curr) - sum(balance_prev) as diff,
      case when sum(balance_prev) != 0 then ((sum(balance_curr) / sum(balance_prev)) -1)*100 else 0.0 end as diff_pro
    from account_account_period_sum_cur_prev
    group by company_id,account_id, name, period_id,fiscalyear_id,date_start
    having company_id > 0;
""")
コード例 #42
0
 def init(self, cr):
     drop_view_if_exists(cr, 'report_timesheet_user')
     cr.execute("""
         create or replace view report_timesheet_user as (
             select
                 min(l.id) as id,
                 to_char(l.date,'YYYY') as name,
                 to_char(l.date,'MM') as month,
                 l.user_id,
                 sum(l.unit_amount) as quantity,
                 sum(l.amount) as cost
             from
                 account_analytic_line l
             where
                 user_id is not null
             group by l.date, to_char(l.date,'YYYY'),to_char(l.date,'MM'), l.user_id
         )
     """)
コード例 #43
0
 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
         )""")
コード例 #44
0
    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
            )""")
コード例 #45
0
    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
コード例 #46
0
    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
            )""")
コード例 #47
0
ファイル: report_stock.py プロジェクト: anilgs/openerp-addons
    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
            )"""
        )
コード例 #48
0
 def init(self, cr) :
         drop_view_if_exists(cr, "chricar_location_move_col")
         drop_view_if_exists(cr, "chricar_report_location_moves_sum")
         drop_view_if_exists(cr, "chricar_report_location_moves")
         
         cr.execute("""
         create or replace view chricar_report_location_moves
         as (
         select id*2 as id,name,
              picking_id,product_id, 
              location_dest_id as location_id,
              partner_id,period_id,prodlot_id,
              date,
              product_qty,move_value_cost,move_value_sale,
              case when product_qty != 0 then  move_value_cost/product_qty  else 0 end as cost_price,
              case when product_qty != 0 then  move_value_sale/product_qty  else 0 end as sale_price,
              state, company_id
         from stock_move
             
         union all
         select id*2-1 as id,name,
              picking_id,product_id, 
              location_id,
              partner_id,period_id,prodlot_id,
              date,
              -product_qty,-move_value_cost,-move_value_sale,
              case when product_qty != 0 then  move_value_cost/product_qty  else 0 end as cost_price,
              case when product_qty != 0 then  move_value_sale/product_qty  else 0 end as sale_price,
              state, company_id
         from stock_move
             
            )
         """)
コード例 #49
0
    def init(self, cr):
        drop_view_if_exists(cr, 'chricar_stock_product_partner')
        cr.execute("""
create view chricar_stock_product_partner as
select
    get_id('chricar_stock_product_partner',
      (case when s.type = 'in' then 1
           when s.type = 'out' then 2
       end)::int , a.partner_id, p.id ,product_id) as id,
    s.type as type,
    a.partner_id, p.id as period_id ,product_id,
    sum(product_qty) as product_qty,
    sum(move_value_cost) as move_value_cost,
    sum(move_value_sale) as move_value_sale,
    case when sum(product_qty) > 0
       then sum(move_value_cost) / sum(product_qty)
       else 0
    end as avg_price,
    case when sum(product_qty) > 0
       then sum(move_value_sale) / sum(product_qty)
       else 0
    end as avg_sale_price
  from 
    stock_move,
    account_period p,
    res_partner_address a,
    stock_picking s
  where a.id= s.address_id
    and s.id=picking_id
    and stock_move.date between date_start and date_stop
    and s.type in ('in','out')
    and stock_move.state != 'cancel'
    and s.state != 'cancel'
  group by
    get_id('chricar_stock_product_partner',
      (case when s.type = 'in' then 1
            when s.type = 'out' then 2
      end)::int , a.partner_id, p.id ,product_id),
    s.type,a.partner_id, p.id ,product_id;
""")
コード例 #50
0
     def init(self, cr):
          drop_view_if_exists(cr, 'chricar_stock_product_by_location_prodlot')
          drop_view_if_exists(cr, 'chricar_stock_product_by_location')
          drop_view_if_exists(cr, 'chricar_stock_move_by_location')

          cr.execute("""create or replace view chricar_stock_move_by_location
as
select i.id ,
 l.id as location_id,product_id,
 case when state ='done' then product_qty else 0 end as name,
 case when state !='done' then product_qty else 0 end as product_qty_pending,
 i.move_value_cost, date, prodlot_id,
 case when product_qty != 0 then i.move_value_cost/product_qty else 0 end as average_price,
 picking_id,production_id,sale_line_id,l.company_id
from stock_location l,
     stock_move i
where l.usage='internal'
  and i.location_dest_id = l.id
  and state != 'cancel'
  and i.company_id = l.company_id
union all
select -o.id ,
l.id as location_id ,product_id,
 case when state ='done' then -product_qty else 0 end as name,
 case when state !='done' then -product_qty else 0 end as product_qty_pending,
    -o.move_value_cost, date, prodlot_id,
 case when product_qty != 0 then o.move_value_cost/product_qty else 0 end as average_price,
 picking_id,production_id,sale_line_id,l.company_id
from stock_location l,
     stock_move o
where l.usage='internal'
  and o.location_id = l.id
  and state != 'cancel'
  and o.company_id = l.company_id
;""")
コード例 #51
0
     def init(self, cr):
          drop_view_if_exists(cr, 'stock_product_by_location_prodlot')
          drop_view_if_exists(cr, 'stock_product_by_location')
          drop_view_if_exists(cr, 'stock_move_by_location')

          cr.execute("""create or replace view stock_move_by_location
as
select i.id ,
 l.id as location_id,product_id,
 i.name as description,
 case when state ='done' then product_qty else 0 end as name,
 case when state !='done' then product_qty else 0 end as product_qty_pending,
 date, prodlot_id,
 picking_id,l.company_id
from stock_location l,
     stock_move i
where l.usage='internal'
  and i.location_dest_id = l.id
  and state != 'cancel'
  and i.company_id = l.company_id
union all
select -o.id ,
l.id as location_id ,product_id,
 o.name as description,
 case when state ='done' then -product_qty else 0 end as name,
 case when state !='done' then -product_qty else 0 end as product_qty_pending,
 date, prodlot_id,
 picking_id,l.company_id
from stock_location l,
     stock_move o
where l.usage='internal'
  and o.location_id = l.id
  and state != 'cancel'
  and o.company_id = l.company_id
;""")
コード例 #52
0
    def init(self, cr):
        drop_view_if_exists(cr, "account_account_fy_period_sum")
        cr.execute("""
create or replace view account_account_fy_period_sum as
  select 
      s.id  as id,
      s.name,
      s.company_id,
      s.account_id,s. period_id, p.fiscalyear_id,
      s.debit as debit ,s.credit as credit,
      s.debit-s.credit as balance,
      sum(case when pcum.date_start <= p.date_start then cum.debit-cum.credit else 0 end) as balance_cumulative,
      --s.sum_fy_period_id as sum_fy_period_id,
      p.date_start,p.date_stop
    from 
      account_account_period_sum s,
      account_period p,
      account_fiscalyear y,
      account_account_period_sum cum,
      account_period pcum
    where 
      p.id = s.period_id
      and y.id = p.fiscalyear_id
      and pcum.id = cum.period_id
      and y.id = pcum.fiscalyear_id
      and pcum.date_start <= p.date_start
      and case when s.name like '%00' then s.id = cum.id else 1=1 end
      and cum.account_id = s.account_id
    group by 
      s.id,
      s.name,
      s.company_id,
      s.account_id,s.period_id,p.fiscalyear_id,
      s.debit,s.credit ,
      s.debit-s.credit ,
      s.sum_fy_period_id ,
      p.date_start,
      p.date_stop;
""")
コード例 #53
0
ファイル: tax_records_issued.py プロジェクト: mbenyoub/server
 def init(self, cr):
     drop_view_if_exists(cr, 'tax_records_issued')
     cr.execute("""
         create or replace view tax_records_issued as (
             SELECT
                 min(AML.id) as id,
                 AM.tax_period_id as period_id,
                 AM.date as date,
                 AM.name as document_name,
                 P.name as partner_name,
                 P.vat as vat,
                 sum(
                 case
                 when ATB.position = '7' 
                         then credit-debit
                         else 0
                 end) as col_7,
                 sum(
                 case
                 when ATB.position = '8' 
                         then credit-debit
                         else 0
                 end) as col_8,
                 sum(
                 case
                 when ATB.position = '9' 
                         then credit-debit
                         else 0
                 end) as col_9,
                 sum(
                 case
                 when ATB.position = '10.a' 
                         then credit-debit
                         else 0
                 end) as col_10a,
                 sum(
                 case
                 when ATB.position = '10.a1' 
                         then credit-debit
                         else 0
                 end) as col_10a1,
                 sum(
                 case
                 when ATB.position = '10b' 
                         then credit-debit
                         else 0
                 end) as col_10b,
                 sum(
                 case
                 when ATB.position = '11' 
                         then credit-debit
                         else 0
                 end) as col_11,
                 sum(
                 case
                 when ATB.position = '12'
                         then credit-debit
                         else 0
                 end) as col_12,
                 sum(
                 case
                 when ATB.position = '13' 
                         then credit-debit
                         else 0
                 end) as col_13,
                 sum(
                 case
                 when ATB.position = '14' 
                         then credit-debit
                         else 0
                 end) as col_14,
                 sum(
                 case
                 when ATB.position = '15'
                         then credit-debit
                         else 0
                 end) as col_15,
                 sum(
                 case
                 when ATB.position = '16'
                         then credit-debit
                         else 0
                 end) as col_16,
                 sum(
                 case
                 when ATB.position = '17'
                         then credit-debit
                         else 0
                 end) as col_17,
                 sum(
                 case
                 when ATB.position = '18'
                         then credit-debit
                         else 0
                 end) as col_18,
                 sum(
                 case
                 when ATB.position = '19'
                         then credit-debit
                         else 0
                 end) as col_19,
                 sum(
                 case
                 when ATB.position = '20'
                         then credit-debit
                         else 0
                 end) as col_20,
                 sum(
                 case
                 when ATB.position = '21'
                         then credit-debit
                         else 0
                 end) as col_21,
                 sum(
                 case
                 when ATB.position = '22'
                         then credit-debit
                         else 0
                 end) as col_22,
                 sum(
                 case
                 when ATB.position = '23'
                         then credit-debit
                         else 0
                 end) as col_23
             FROM 
                     account_move_line AML,
                     account_move AM,
                     account_tax_book_fields_issued_tax_code_rel REL,
                     account_tax_book_fields ATB,
                 res_partner P
             WHERE 
                     AML.tax_code_id = REL.tax_code_id and 
                     REL.tax_book_fields_id = ATB.id and 
                     AML.tax_code_id != 0 and
                 AML.partner_id = P.id and
                 AML.move_id = AM.id
             
             GROUP BY AM.name, AM.date, AM.tax_period_id, P.name, P.vat
             ORDER BY AM.name
         )""")
コード例 #54
0
    def init(self, cr):
        drop_view_if_exists(cr, 'stock_report_prodlots1')
        cr.execute("""
            create or replace view stock_report_prodlots1 as (
                    select max(id) as id,
                    location_id,
                    product_id,
                    prodlot_id,
                    life_date,
                    use_date, 
                    removal_date,
                    alert_date,
                    sum(qty) as name
                from (
                    select -max(sm.id) as id,
                        sm.location_id,
                        sm.product_id,
                        sm.prodlot_id,
                        spl.life_date,
                        spl.use_date, 
                        spl.removal_date,
                        spl.alert_date,
                        -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 stock_production_lot spl
                        on (sm.prodlot_id=spl.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,
                    spl.life_date,
                    spl.use_date, 
                    spl.removal_date,
                    spl.alert_date

                    union all
                    select max(sm.id) as id,
                        sm.location_dest_id as location_id,
                        sm.product_id,
                        sm.prodlot_id,
                        spl.life_date,
                        spl.use_date, 
                        spl.removal_date,
                        spl.alert_date,
                        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 stock_production_lot spl
                        on (sm.prodlot_id=spl.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,
                    spl.life_date,
                    spl.use_date, 
                    spl.removal_date,
                    spl.alert_date

                ) as report
                group by location_id, product_id, prodlot_id, 
                    life_date,
                    use_date, 
                    removal_date,
                    alert_date
            )""")
コード例 #55
0
def init(self, cr):
    drop_view_if_exists(cr, 'hr_expense_report')