工作总结（sql）

1.select so.name,so.state,so.unsettled_total ,so.contract_total - so.invoiced_total - so.bad_dept_amount,so.remain_brokerage,so.remain_award from sale_order so where
(so.unsettled_total > -0.1 and so.unsettled_total <0.1 ) and 
((so.contract_total - so.invoiced_total - so.bad_dept_amount) >-0.1 and (so.contract_total - so.invoiced_total - so.bad_dept_amount)<0.1 ) and 
(so.remain_brokerage > -0.1 and so.remain_brokerage <0.1) and
(so.remain_award > -0.1 and so.remain_award <0.1) and so.state !='done' and so.ticket_color = 'blue';

2.alter table ir_ui_menu add column web_icon_data bytea;

3.

update sale_order set actual_invoiced_total = (select coalesce(sum(amount), 0) from sale_order_invoice soi join sale_invoice si on soi.invoice_id = si.id where si.state in ('billed', 'done') and soi.order_line_id in (select id from sale_order_line where order_id =sale_order.id))
where order_type in ('sell', 'foreignSell', 'sellAlterReplace');

update sale_order set actual_not_invoiced_total = contract_total - actual_invoiced_total - coalesce(bad_dept_amount, 0) - coalesce(back_amount, 0) where order_type in ('sell', 'foreignSell', 'sellAlterReplace');