def init(self): drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute(""" create or replace view payslip_report as ( select min(l.id) as id, l.name, p.struct_id, p.state, p.date_from, p.date_to, p.number, p.company_id, p.paid, l.category_id, l.employee_id, sum(l.total) as total, to_char(p.date_from, 'YYYY') as year, to_char(p.date_from, 'MM') as month, to_char(p.date_from, 'YYYY-MM-DD') as day, to_char(p.date_to, 'YYYY') as to_year, to_char(p.date_to, 'MM') as to_month, to_char(p.date_to, 'YYYY-MM-DD') as to_day, 1 AS nbr from hr_payslip as p left join hr_payslip_line as l on (p.id=l.slip_id) where l.employee_id IS NOT NULL group by p.number,l.name,p.date_from,p.date_to,p.state,p.company_id,p.paid, l.employee_id,p.struct_id,l.category_id ) """)
def init(self): drop_view_if_exists(self._cr, self._table) self._cr.execute(""" CREATE OR REPLACE VIEW report_account_analytic_line_to_invoice AS ( SELECT DISTINCT(to_char(l.date,'MM')) as month, to_char(l.date, 'YYYY') as year, MIN(l.id) AS id, l.product_id, l.account_id, SUM(l.amount) AS amount, SUM(l.unit_amount*t.list_price) AS sale_price, SUM(l.unit_amount) AS unit_amount, l.product_uom_id FROM account_analytic_line l left join product_product p on (l.product_id=p.id) left join product_template t on (p.product_tmpl_id=t.id) WHERE (invoice_id IS NULL) and (to_invoice IS NOT NULL) GROUP BY to_char(l.date, 'YYYY'), to_char( l.date,'MM'), l.product_id, l.product_uom_id, l.account_id ) """)
def init(self): drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute(""" create or replace view payment_advice_report as ( select min(l.id) as id, sum(l.bysal) as bysal, p.name, p.state, p.date, p.number, p.company_id, p.bank_id, p.chaque_nos as cheque_nos, p.neft, l.employee_id, l.ifsc_code, l.name as employee_bank_no, to_char(p.date, 'YYYY') as year, to_char(p.date, 'MM') as month, to_char(p.date, 'YYYY-MM-DD') as day, 1 as nbr from hr_payroll_advice as p left join hr_payroll_advice_line as l on (p.id=l.advice_id) where l.employee_id IS NOT NULL group by p.number,p.name,p.date,p.state,p.company_id,p.bank_id,p.chaque_nos,p.neft, l.employee_id,l.advice_id,l.bysal,l.ifsc_code, l.name ) """)
def init(self): cr = 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 = self._cr drop_view_if_exists(cr, 'report_analytic_account_close') cr.execute(""" create or replace view report_analytic_account_close as ( select a.id as id, a.id as name, a.state as state, sum(l.unit_amount) as quantity, sum(l.amount) as balance, a.partner_id as partner_id, a.quantity_max as quantity_max, a.date as date_deadline from account_analytic_line l right join account_analytic_account a on (l.account_id=a.id) group by a.id,a.state,a.quantity_max,a.date,a.partner_id having (a.quantity_max>0 and ( sum(l.unit_amount)>=a.quantity_max)) or a.date <= current_date )""")
def init(self): cr = 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, l.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, l.user_id ) """)
def init(self): drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute(""" create or replace view report_intrastat as ( select to_char(inv.date_invoice, 'YYYY') as name, to_char(inv.date_invoice, '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 * inv_line.quantity) else (pt.weight * 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, inv.company_id as company_id 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.uom_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.date_invoice, 'YYYY'), to_char(inv.date_invoice, 'MM'),intrastat.id,inv.type,pt.intrastat_id, inv_country.code,inv.number, inv.currency_id, inv.company_id )""")
def init(self): drop_view_if_exists(self._cr, 'report_timesheet_user') self._cr.execute(""" create or replace view report_timesheet_user as ( select min(l.id) as id, to_char(l.date,'YYYY') as year, to_char(l.date,'MM') as month, l.user_id, sum(l.unit_amount) as qty, 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 ) """)
def init(self): drop_view_if_exists(self._cr, 'hr_activity_sheet_account') self._cr.execute("""create view hr_activity_sheet_account as ( select min(line.id) as id, line.account_id as name, s.id as activity_sheet_id, sum(line.unit_amount) as utilized_hour from account_analytic_line line LEFT JOIN hr_activity_sheet s ON s.user_id = line.user_id AND ( s.end_date >= line.date AND s.start_date <= line.date) group by line.account_id, s.id )""")
def init(self): drop_view_if_exists(self._cr, 'report_timesheet_account') self._cr.execute(""" create or replace view report_timesheet_account as ( select min(id) as id, to_char(create_date, 'YYYY') as year, to_char(create_date,'MM') as month, user_id, account_id, sum(unit_amount) as qty from account_analytic_line group by to_char(create_date, 'YYYY'),to_char(create_date, 'MM'), user_id, account_id ) """)
def init(self): cr = 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 ) """)