def init(self,cr):
tools.sql.drop_view_if_exists(cr, 'custrom_product_infomation_report')
cr.execute("""
create or replace view custrom_product_infomation_report as (
select t0.id,t0.hpartner_id as hpartner_id, t0.khwl_code as khwl_code,t1.product_tmpl_id as productn,
t3.pname,t3.material,t3.spec,
t4.id as name_uom, t5.price as custo_price,t0.product_meno as product_meno ,t0.meno as meno,t5.date_from,t5.date_to
from product_custo_info t0
LEFT JOIN product_product t1 on t0.product_tmpl_id=t1.product_tmpl_id
LEFT JOIN product_template t3 on t3.id=t1.product_tmpl_id
LEFT JOIN res_partner t2 on t2.id=t0.hpartner_id
LEFT JOIN product_uom t4 on t3.uom_id=t4.id
left join partner_product_price t5 on t5.ppp_line_id=t0.id
)
""")
sql语句当该前时间对比:
select * from (select t1.id,t0.product_meno,
t0.hpartner_id,t0.khwl_code ,t0.name_uom,t0.custo_price
,t0.product_meno,t0.meno,t2.pname ,t0.date_from,t0.date_to
from cust_product_info_report t0
LEFT JOIN product_product t1 on t1.product_tmpl_id=t0.productn
LEFT JOIN product_template t2 on t1.product_tmpl_id=t2.id
where (now()>=t0.date_from and now()<=t0.date_to) or (t0.date_from is null and t0.date_to is null ) or
(t0.date_from is null and (now()<=t0.date_to)) or (t0.date_to is null and (now()>=t0.date_from))
)
as p where hpartner_id='16404'
order by pname
sql 中 : case ........when........... then.......
else....... end 方法:
case tt3.send_invoice_type WHEN '1' then '汽车零担' WHEN '2' then '苏通快运'
WHEN '3' then '顺丰速运' WHEN '4' then '德邦快递' WHEN '5' then '优速快递'
WHEN '6' then '佳吉物流' WHEN '7' then '中通物流' WHEN '8' then '下午车'
else '' end 发运方式,