def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_account_invoice_product')        
     cr.execute("""
         create or replace view report_account_invoice_product as (
             select
                 min(l.id) as id,
                 i.create_date as date,                    
                 to_char(date_trunc('day',i.date_invoice), 'YYYY') as year,
                 to_char(date_trunc('day',i.date_invoice), 'MM') as month,
                 to_char(date_trunc('day',i.date_invoice), 'YYYY-MM-DD') as day,                    
                 i.type,
                 i.state,
                 l.product_id,
                 t.categ_id,
                 i.partner_id,
                 sum(l.quantity * l.price_unit * (1.0 - l.discount/100.0)) as amount,
                 sum(l.quantity * l.cost_price) as cost_price,
                 sum((l.quantity * l.price_unit * (1.0 - l.discount/100.0) - (l.quantity * l.cost_price))) as margin,
                 sum(l.quantity) as quantity
             from account_invoice i
                 left join account_invoice_line l on (i.id = l.invoice_id)
                 left join product_product p on (p.id = l.product_id)
                 left join product_template t on (t.id = p.product_tmpl_id)                    
             group by t.categ_id,i.partner_id,l.product_id, i.date_invoice, i.type, i.state,i.create_date
         )
     """)

    def init(self, cr):

        """  Display Number of cases and Average Probability
            @param cr: the current row, from the database cursor
        """

        tools.drop_view_if_exists(cr, 'crm_fundraising_report')
        cr.execute("""
            create or replace view crm_fundraising_report as (
                select
                    min(c.id) as id,
                    to_char(c.date, 'YYYY') as name,
                    to_char(c.date, 'MM') as month,
                    to_char(c.date, 'YYYY-MM-DD') as day,
                    c.state,
                    c.user_id,
                    c.section_id,
                    c.categ_id,
                    c.type_id,
                    c.company_id,
                    c.partner_id,
                    count(*) as nbr,
                    date_trunc('day',c.create_date) as create_date,
                    sum(planned_revenue) as amount_revenue,
                    sum(planned_cost) as planned_cost,
                    sum(planned_revenue*probability)::decimal(16,2) as amount_revenue_prob,
                    avg(probability)::decimal(16,2) as probability,
                    avg(extract('epoch' from (c.date_closed-c.create_date)))/(3600*24) as  delay_close
                from
                    crm_fundraising c
                where c.active = 'true'
                group by to_char(c.date, 'YYYY'), to_char(c.date, 'MM'),\
                     c.state, c.user_id,c.section_id,c.categ_id,type_id,c.partner_id,c.company_id,
                     c.create_date,to_char(c.date, 'YYYY-MM-DD')
            )""")

 def init(self, cr):
     tools.drop_view_if_exists(cr, 'hr_timesheet_report')
     cr.execute("""
         create or replace view hr_timesheet_report as (
             select
                 min(t.id) as id,
                 l.date as date,
                 to_char(l.date, 'YYYY-MM-DD') as day,
                 to_char(l.date,'YYYY') as year,
                 to_char(l.date,'MM') as month,
                 sum(l.amount) as cost,
                 sum(l.unit_amount) as quantity,
                 l.account_id as account_id,
                 l.journal_id as journal_id,
                 l.product_id as product_id,
                 l.general_account_id as general_account_id,
                 l.user_id as user_id,
                 l.company_id as company_id,
                 l.currency_id as currency_id
             from
                 hr_analytic_timesheet as t
                 left join account_analytic_line as l ON (t.line_id=l.id)
             group by
                 l.date,
                 l.account_id,
                 l.product_id,
                 l.general_account_id,
                 l.journal_id,
                 l.user_id,
                 l.company_id,
                 l.currency_id
         )
     """)

 def init(self, cr):
     tools.drop_view_if_exists(cr, "mrp_workorder")
     cr.execute(
         """
         create or replace view mrp_workorder as (
             select
                 to_date(to_char(wl.date_planned, 'MM-dd-YYYY'),'MM-dd-YYYY') as date,
                 to_char(wl.date_planned, 'YYYY') as year,
                 to_char(wl.date_planned, 'MM') as month,
                 to_char(wl.date_planned, 'YYYY-MM-DD') as day,
                 min(wl.id) as id,
                 mp.product_id as product_id,
                 sum(wl.hour) as total_hours,
                 avg(wl.delay) as delay,
                 (w.costs_hour*sum(wl.hour)) as total_cost,
                 wl.production_id as production_id,
                 wl.workcenter_id as workcenter_id,
                 sum(wl.cycle) as total_cycles,
                 count(*) as nbr,
                 sum(mp.product_qty) as product_qty,
                 wl.state as state
             from mrp_production_workcenter_line wl
                 left join mrp_workcenter w on (w.id = wl.workcenter_id)
                 left join mrp_production mp on (mp.id = wl.production_id)
             group by
                 w.costs_hour, mp.product_id, mp.name, wl.state, wl.date_planned, wl.production_id, wl.workcenter_id
     )"""
     )

 def init(self, cr):
     tools.drop_view_if_exists(cr, 'payment_advice_report')
     cr.execute("""
         create or replace view payment_advice_report as (
             select
                 min(l.id) as id,
                 sum(l.bysal) as bysal,
                 p.name,
                 p.state,
                 p.date,
                 p.number,
                 p.company_id,
                 p.bank_id,
                 p.chaque_nos as cheque_nos,
                 p.neft,
                 l.employee_id,
                 l.ifsc_code,
                 l.name as employee_bank_no,
                 to_char(p.date, 'YYYY') as year,
                 to_char(p.date, 'MM') as month,
                 to_char(p.date, 'YYYY-MM-DD') as day,
                 1 as nbr
             from
                 hr_payroll_advice as p
                 left join hr_payroll_advice_line as l on (p.id=l.advice_id)
             where 
                 l.employee_id IS NOT NULL
             group by
                 p.number,p.name,p.date,p.state,p.company_id,p.bank_id,p.chaque_nos,p.neft,
                 l.employee_id,l.advice_id,l.bysal,l.ifsc_code, l.name
         )
     """)

    def init(self, cr):
        tools.drop_view_if_exists(cr,'tms_analisys_01')
        cr.execute("""

create or replace view tms_analisys_01 as (

select 
o.id, o.id as name, o.product_id, o.supervisor_id, o.maint_cycle_id, o.driver_id,
o.user_id, o.unit_id, o.date,
to_char(date_trunc('day',o.date), 'YYYY') as year,
to_char(date_trunc('day',o.date), 'MM') as month,
to_char(date_trunc('day',o.date), 'YYYY-MM-DD') as day,
o.date_start_real, o.date_end_real,
o.duration_real, o.notes,
sum(a.parts_cost) as parts_cost,
sum(a.cost_service) as cost_service,
sum(a.parts_cost_external) as parts_cost_external,
sum(a.cost_service_external) as cost_service_external
from tms_maintenance_order as o
left join tms_maintenance_order_activity a on o.id=a.maintenance_order_id and a.state='done'
where o.state = 'done'
group by o.id, o.product_id, o.supervisor_id, o.maint_cycle_id, o.driver_id,
o.user_id, o.unit_id, o.name, o.date, o.date_start_real, o.date_end_real,
o.duration_real, o.notes
order by o.date
);
  
        """)

 def init(self, cr):
     tools.drop_view_if_exists(cr, 'document_report_gbu')
     current_date=time.strftime('%Y-%m-%d %H:%M:%S')
     print "current date",current_date
     cr.execute("""
         CREATE OR REPLACE VIEW document_report_gbu as (
              SELECT
                  min(f.id) as id,
                  to_char(f.date_expired, 'YYYY') as name,
                  to_char(f.date_expired, 'MM') as month,
                  f.user_id as user_id,
                  f.name as doc_name,
                  p.name as partner_name,
                  u.name as user,
                  count(*) as nbr,
                  d.name as directory,
                  f.datas_fname as datas_fname,
                  f.create_date as create_date,
                  f.date_expired as date_expired,
                  f.file_size as file_size,
                  min(d.type) as type,
                  f.write_date as change_date
              FROM ir_attachment f
                  left join document_directory d on (f.parent_id=d.id and d.name<>'')
                  inner join res_users u on (f.user_id=u.id)
                  inner join res_partner p on (f.partner_id=p.id)
              group by to_char(f.date_expired, 'YYYY'), to_char(f.date_expired, 'MM'),f.user_id,f.name,p.name,u.name,d.name,f.datas_fname,f.create_date,f.date_expired,f.file_size,f.write_date,d.type
          )
     """)

    def init(self, cr):
        tools.drop_view_if_exists(cr, 'sps_dashboard')
        
        cr.execute("""
            create or replace view sps_dashboard as (
       
       with S1 as (
Select 1 as nbr,pr.id as Xid, (select id from sps_state where code = tt.name) as state_id, p.id as id, aa.name as name, p.id as partner_id, tt.name as state from account_analytic_account aa 
inner join res_partner p on aa.partner_id = p.id 
inner join project_project pr ON aa.id = pr.analytic_account_id
inner join project_task_type tt on pr.project_task_stage = tt.id and tt.name not in ('Waiting Goods')
),
S2 as (
Select 1 as nbr, s.id as Xid,(select id from sps_state where code = s.state) as state_id, p.id as id, s.name as name, p.id as partner_id, s.state as state from sale_order s 
inner join res_partner p on s.partner_id = p.id where state not in ('confirmed','done','progress','manual','follow_up','cancel')
),
S3 as (
select 1 as nbr, l.id as Xid,(select id from sps_state where code = cs.name) as state_id, p.id as id, l.name as name, p.id as partner_id, cs.name as state from crm_lead l 
inner join crm_case_stage cs on l.stage_id = cs.id 
inner join res_partner p on l.partner_id = p.id 
where l.state not in ('cancel','done')
)
select 1 as nbr, state_id, 1000*id + 200*Xid as id, name, partner_id from S1 
union
select 1 as nbr, state_id, 20000*id + 305*Xid as id, name, partner_id from S2 where S2.id not in (select id from S1)
union
select 1 as nbr, state_id, 300000*id + 101*Xid as id, name, partner_id from S3
where S3.id not in (select partner_id from S1))""")        

    def init(self, cr):
        tools.drop_view_if_exists(cr, 'report_timesheet_task_user')
        cr.execute(""" create or replace view report_timesheet_task_user as (
        select
         ((r.id*12)+to_number(months.m_id,'99'))::integer as id,
               months.name as name,
               r.id as user_id,
               to_char(to_date(months.name, 'YYYY/MM/DD'),'YYYY') as year,
               to_char(to_date(months.name, 'YYYY/MM/DD'),'MM') as month,
               (select sum(hours) from project_task_work where user_id = r.id and date between to_date(months.name, 'YYYY/MM/DD') and (to_date(months.name, 'YYYY/MM/DD') + interval '1 month' -
            interval '1 day') ) as task_hrs
        from res_users r,
                (select to_char(p.date,'YYYY-MM-01') as name,
            to_char(p.date,'MM') as m_id
                from project_task_work p

            union
                select to_char(h.name,'YYYY-MM-01') as name,
                to_char(h.name,'MM') as m_id
                from hr_timesheet_sheet_sheet_day h) as months

            group by
                r.id,months.m_id,months.name,
                to_char(to_date(months.name, 'YYYY/MM/DD'),'YYYY') ,
                to_char(to_date(months.name, 'YYYY/MM/DD'),'MM')
              ) """)

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

select 
	pl.id                                                                       as id,
	pl.id                                                                       as product_line_id, 
	
	(select p.name_template from product_product as p where p.id=pl.product_id) as product_name,
	(select p.id from product_product as p where p.id=pl.product_id)            as product_id,
	
	pl.quantity                                                                 as quantity,
	pl.list_price                                                               as price,
	(pl.quantity*pl.list_price)                                                 as total_price,

	(select p.name_template  from product_product as p where p.id = activity.product_id) as activity_name,
	(activity.id                                                                       ) as activity_id,
	
	o.name                                                                               as order_name,
	o.id                                                                                 as order_id
	
from tms_product_line as pl, tms_maintenance_order_activity as activity, tms_maintenance_order as o
where pl.state like 'delivered' and pl.activity_id = activity.id and o.id = activity.maintenance_order_id      
                
            )
        """
        )

    def init(self, cr):

        tools.drop_view_if_exists(cr, 'account_summary')
		
        cr.execute("""
            create or replace view account_summary as (
            select
                l.id as id,
                am.date as date,
                to_char(am.date, 'YYYY') as year,
                p.fiscalyear_id as fiscalyear_id,
                am.period_id as period_id,
                l.account_id as account_id,
				a.code_2 as account_code,
                l.debit as debit,
                l.credit as credit,
                l.debit-l.credit as balance,
                l.credit-l.debit as balance_inv
            from
                account_move_line l
                left join account_account a on (l.account_id = a.id)
                left join account_move am on (am.id=l.move_id)
                left join account_period p on (am.period_id=p.id)
                where l.state != 'draft'
            )
        """)

 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_vote')
     cr.execute("""
         create or replace view report_vote as (
            select
                 min(iv.id) as id,
                 count(*) as nbr,
                 to_date(to_char(ii.open_date, 'dd-MM-YYYY'),'dd-MM-YYYY') as date,
                 to_char(ii.open_date, 'YYYY') as year,
                 to_char(ii.open_date, 'MM') as month,
                 to_char(ii.open_date, 'YYYY-MM-DD') as day,
                 iv.user_id as user_id,
                 iv.idea_id as idea_id,
                 ii.state as idea_state,
                 ii.user_id as creater_id,
                 ii.category_id,
                 (sum(CAST(iv.score as integer))/count(iv.*)) as score
             from
                 idea_vote as iv
                 left join idea_idea as ii on (ii.id = iv.idea_id)
             group by
                 iv.id ,to_char(ii.open_date, 'dd-MM-YYYY'),to_char(ii.open_date, 'YYYY'),
                 to_char(ii.open_date, 'MM'),to_char(ii.open_date, 'YYYY-MM-DD'),ii.state,
                 iv.user_id,ii.user_id,ii.category_id,iv.idea_id
         )
         """)

 def init(self, cr):
     tools.drop_view_if_exists(cr, 'account_move_line_report')
     cr.execute(""" 
                create or replace view account_move_line_report as (
                    select
                        aml.id as id,
                        aml.name as name,
                        aml.date as date,
                        aml.account_id as account_id,
                        aml.currency_id as currency_id,
                        aml.debit as debit,
                        aml.credit as credit,
                        aml.ref as ref,
                        aml.journal_id as journal_id,
                        aml.period_id as period_id,
                        aml.reconcile_id as reconcile_id,
                        aml.move_id as move_id,
                        aml.tot_balance as tot_balance,
                        res.id as partner_id
                        
                        
                    from account_move_line as aml
                         left join res_partner res on (aml.partner_id=res.id)
                    group by
                         aml.id,
                         res.id
                         
                     )
                """)

    def init(self, cr):
        tools.drop_view_if_exists(cr, 'report_sales_by_margin_pos_month')
        cr.execute("""
            create or replace view report_sales_by_margin_pos_month as (
                select
                    min(pol.id) as id,
                    po.user_id as user_id,
                    pt.name as product_name,
                    to_char(date_trunc('month',po.date_order),'YYYY-MM-DD')::text as date_order,
                    sum(pol.qty) as qty,
                    0.00 as net_margin_per_qty,
                    0.00 *sum(pol.qty) as total
                from
                    product_template as pt,
                    product_product as pp,
                    pos_order_line as pol,
                    pos_order as po
                where
                    pol.product_id = pp.product_tmpl_id and
                    pp.product_tmpl_id = pt.id and
                    po.id = pol.order_id

                group by
                    pt.name,
                    po.user_id,
                    to_char(date_trunc('month',po.date_order),'YYYY-MM-DD')::text

                )
        """)

 def init(self, cr):
     tools.drop_view_if_exists(cr, 'analytic_entries_report')
     cr.execute("""
         create or replace view analytic_entries_report as (
              select
                  min(a.id) as id,
                  count(distinct a.id) as nbr,
                  a.create_date as date,
                  to_char(a.create_date, 'YYYY') as year,
                  to_char(a.create_date, 'MM') as month,
                  to_char(a.create_date, 'YYYY-MM-DD') as day,
                  a.user_id as user_id,
                  a.name as name,
                  analytic.partner_id as partner_id,
                  a.company_id as company_id,
                  a.currency_id as currency_id,
                  a.account_id as account_id,
                  a.general_account_id as general_account_id,
                  a.journal_id as journal_id,
                  a.move_id as move_id,
                  a.product_id as product_id,
                  a.product_uom_id as product_uom_id,
                  sum(a.amount) as amount,
                  sum(a.unit_amount) as unit_amount
              from
                  account_analytic_line a, account_analytic_account analytic
              where analytic.id = a.account_id
              group by
                  a.create_date, a.user_id,a.name,analytic.partner_id,a.company_id,a.currency_id,
                  a.account_id,a.general_account_id,a.journal_id,
                  a.move_id,a.product_id,a.product_uom_id
         )
     """)

 def init(self, cr):
     tools.drop_view_if_exists(cr, 'campaign_analysis')
     cr.execute("""
         create or replace view campaign_analysis as (
         select
             min(wi.id) as id,
             min(wi.res_id) as res_id,
             to_char(wi.date::date, 'YYYY') as year,
             to_char(wi.date::date, 'MM') as month,
             to_char(wi.date::date, 'YYYY-MM-DD') as day,
             wi.date::date as date,
             s.campaign_id as campaign_id,
             wi.activity_id as activity_id,
             wi.segment_id as segment_id,
             wi.partner_id as partner_id ,
             wi.state as state,
             sum(act.revenue) as revenue,
             count(*) as count
         from
             marketing_campaign_workitem wi
             left join res_partner p on (p.id=wi.partner_id)
             left join marketing_campaign_segment s on (s.id=wi.segment_id)
             left join marketing_campaign_activity act on (act.id= wi.activity_id)
         group by
             s.campaign_id,wi.activity_id,wi.segment_id,wi.partner_id,wi.state,
             wi.date::date
         )
     """)

 def init(self, cr):
     """
         CRM Lead Report
         @param cr: the current row, from the database cursor
     """
     tools.drop_view_if_exists(cr, 'crm_partner_report_assign')
     cr.execute("""
         CREATE OR REPLACE VIEW crm_partner_report_assign AS (
             SELECT
                 coalesce(i.id, p.id - 1000000000) as id,
                 p.id as partner_id,
                 (SELECT country_id FROM res_partner_address a WHERE a.partner_id=p.id AND country_id is not null limit 1) as country_id,
                 p.grade_id,
                 p.activation,
                 p.date_review,
                 p.date_partnership,
                 p.user_id,
                 p.section_id,
                 (SELECT count(id) FROM crm_lead WHERE partner_assigned_id=p.id) AS opp,
                 i.price_total as turnover,
                 i.period_id
             FROM
                 res_partner p
                 left join account_invoice_report i
                     on (i.partner_id=p.id and i.type in ('out_invoice','out_refund') and i.state in ('open','paid'))
         )""")

    def init(self, cr):

        """ Phone Calls By User And Section
            @param cr: the current row, from the database cursor,
        """
        tools.drop_view_if_exists(cr, 'crm_phonecall_report')
        cr.execute("""
            create or replace view crm_phonecall_report as (
                select
                    id,
                    to_char(c.date, 'YYYY') as name,
                    to_char(c.date, 'MM') as month,
                    to_char(c.date, 'YYYY-MM-DD') as day,
                    to_char(c.create_date, 'YYYY-MM-DD') as creation_date,
                    to_char(c.date_open, 'YYYY-MM-DD') as opening_date,
                    to_char(c.date_closed, 'YYYY-mm-dd') as date_closed,
                    c.state,
                    c.user_id,
                    c.section_id,
                    c.categ_id,
                    c.partner_id,
                    c.duration,
                    c.company_id,
                    c.priority,
                    1 as nbr,
                    date_trunc('day',c.create_date) as create_date,
                    extract('epoch' from (c.date_closed-c.create_date))/(3600*24) as  delay_close,
                    extract('epoch' from (c.date_open-c.create_date))/(3600*24) as  delay_open
                from
                    crm_phonecall c
            )""")

 def init(self, cr):
     tools.drop_view_if_exists(cr, 'report_pos_order')
     cr.execute("""
         create or replace view report_pos_order as (
             select
                 min(l.id) as id,
                 count(*) as nbr,
                 to_date(to_char(s.date_order, 'dd-MM-YYYY'),'dd-MM-YYYY') as date,
                 sum(l.qty * u.factor) as product_qty,
                 sum(l.qty * l.price_unit) as price_total,
                 sum(l.qty * l.discount) as total_discount,
                 (sum(l.qty*l.price_unit)/sum(l.qty * u.factor))::decimal(16,2) as average_price,
                 sum(cast(to_char(date_trunc('day',s.date_order) - date_trunc('day',s.create_date),'DD') as int)) as delay_validation,
                 to_char(s.date_order, 'YYYY') as year,
                 to_char(s.date_order, 'MM') as month,
                 to_char(s.date_order, 'YYYY-MM-DD') as day,
                 s.partner_id as partner_id,
                 s.state as state,
                 s.user_id as user_id,
                 s.shop_id as shop_id,
                 s.company_id as company_id,
                 s.sale_journal as journal_id,
                 l.product_id as product_id
             from pos_order_line as l
                 left join pos_order s on (s.id=l.order_id)
                 left join product_template pt on (pt.id=l.product_id)
                 left join product_uom u on (u.id=pt.uom_id)
             group by
                 to_char(s.date_order, 'dd-MM-YYYY'),to_char(s.date_order, 'YYYY'),to_char(s.date_order, 'MM'),
                 to_char(s.date_order, 'YYYY-MM-DD'), s.partner_id,s.state,
                 s.user_id,s.shop_id,s.company_id,s.sale_journal,l.product_id,s.create_date
             having
                 sum(l.qty * u.factor) != 0)""")

 def init(self, cr):
     tools.drop_view_if_exists(cr, 'account_followup_stat')
     cr.execute("""
         create or replace view account_followup_stat as (
             SELECT
                 l.partner_id as id,
                 l.partner_id AS partner_id,
                 min(l.date) AS date_move,
                 max(l.date) AS date_move_last,
                 max(l.followup_date) AS date_followup,
                 max(l.followup_line_id) AS followup_id,
                 sum(l.debit) AS debit,
                 sum(l.credit) AS credit,
                 sum(l.debit - l.credit) AS balance,
                 l.company_id AS company_id,
                 l.blocked as blocked,
                 l.period_id AS period_id
             FROM
                 account_move_line l
                 LEFT JOIN account_account a ON (l.account_id = a.id)
             WHERE
                 a.active AND
                 a.type = 'receivable' AND
                 l.reconcile_id is NULL AND
                 l.partner_id IS NOT NULL
             GROUP BY
                 l.id, l.partner_id, l.company_id, l.blocked, l.period_id
         )""")