Cacher is the code snippet organizer for pro developers

select ps.name, res_partner.name, res_partner.csv_filename, last_csv_timestamp, in_cartomak, COUNT(ps.id) from product_supplierinfo ps
left join res_partner on ps.name = res_partner.id
WHERE price > 0 and stock > 0 and ps.write_date >= now()-interval '3 hours' group by ps.name, res_partner.name, res_partner.csv_filename, last_csv_timestamp, in_cartomak
ORDER BY res_partner.name

select product_tmpl_id, COUNT(id) from product_supplierinfo group by product_tmpl_id having count(*) > 6

select dc, pt, count from (select pp.default_code dc, ps.product_tmpl_id pt, COUNT(ps.id) count from product_supplierinfo ps
LEFT JOIN product_product pp on ps.product_id = pp.id WHERE ps.stock > 0 AND ps.price > 0
group by pp.default_code, ps.product_tmpl_id having count(*) > 0 ORDER BY count DESC) total;

select count, count(dc) from (select pp.default_code dc, ps.product_tmpl_id pt, COUNT(ps.id) count from product_supplierinfo ps
LEFT JOIN product_product pp on ps.product_id = pp.id WHERE ps.stock > 0 AND ps.price > 0
group by pp.default_code, ps.product_tmpl_id having count(*) > 0 ORDER BY count DESC) total GROUP BY count;

delete from product_saleschannel_price where id > 0

select name, last_csv_timestamp, connection_type, connection_host from res_partner where cartomark_ref is not null

select product_tmpl_id, count(id) from product_attribute_line group by product_tmpl_id

select name, product_tmpl_id, price, create_date from product_supplier_pricehistory order by product_tmpl_id, create_date

select product_tmpl_id, round(cast(float8 (sum(price)/count(id)) as numeric), 2) as average_price, count(id) from product_supplier_pricehistory group by product_tmpl_id order by product_tmpl_id

select
       round(cast(float8 (sum(price)/count(id)) as numeric), 2) as average_price
       from product_supplier_pricehistory
where product_tmpl_id=6666 and name=116398

select price from product_supplier_pricehistory where product_tmpl_id=6666 and name=116398 order by create_date DESC LIMIT 1

update res_partner set last_csv_timestamp=0 where cartomark_ref is not null
update res_partner set last_csv_timestamp=0 where id = 36

select product_tmpl_id, name from product_supplierinfo where stock = 1

select pt.id, res_id FROM product_template pt
LEFT JOIN ir_attachment ON res_id = pt.id
where res_id is not null
GROUP BY pt.id, res_id

update product_supplierinfo set price = 0, stock = 0 where name = 212

select name, product_tmpl_id from product_supplierinfo where product_tmpl_id IN
                                            (select product_tmpl_id from product_supplierinfo group by product_tmpl_id having count(product_tmpl_id) = 1) and name = 213

UPDATE product_saleschannel_price SET stock = 0, price = 0, boost_price = Null WHERE supplier not in (select id from res_partner where cartomark_ref is not null and tyre_supplier is TRUE)

select supplier from product_saleschannel_price WHERE supplier not in (select id from res_partner where cartomark_ref is not null and tyre_supplier is TRUE) group by supplier

select supplier from product_saleschannel_price group by supplier

select psp.supplier, rp.name from product_saleschannel_price psp
left join res_partner rp on psp.supplier = rp.id
where price > 0 and stock > 0 group by psp.supplier, rp.name

update product_supplierinfo set date_start='2018-12-12', date_end = '2018-12-12' where name=198 and write_date > '2018-12-12'

update product_saleschannel_price set boost_price = NUll where price = 0

delete from ir_attachment where res_model='product.template'

select pt.id, res_id FROM product_template pt
LEFT JOIN ir_attachment ON res_id = pt.id
left join product_supplierinfo ON pt.id = product_supplierinfo.product_tmpl_id
where res_id is not null and res_model='product.template' and product_supplierinfo.stock > 0
GROUP BY pt.id, res_id

select template2.default_code, template2.barcode, product_saleschannel_price.product_tmpl_id, price, boost_price from product_saleschannel_price
left join product_product template2 on product_saleschannel_price.product_tmpl_id = template2.product_tmpl_id
where boost_price > (price + (price *29/100))

update product_template set list_price = 0 where default_code is not null and type = 'product'

Select ail.name, product_id, pp.product_tmpl_id as productTemplateID, pp.default_code, COUNT(ail.id) as total from account_invoice_line ail
left join product_product pp on ail.product_id = pp.id
left join product_template pt on pp.product_tmpl_id = pt.id
WHERE pt.type = 'product' AND pp.active = TRUE AND pp.default_code ~ E'^\\d+$' AND LENGTH(pp.default_code) = 10
group by ail.name, product_id, pp.id, pp.default_code having Count(*) > 0 order by total DESC LIMIT 100

select product_tmpl_id, rp.name, COUNT(psi.id) from product_supplierinfo psi
left join res_partner rp on psi.name = rp.id
group by product_tmpl_id, rp.name having count(*) > 1

update product_supplierinfo SET warn = FALSE where id > 0

delete FROM product_supplier_pricehistory where id > 0

Select pp.barcode, pp.default_code, psp.price, psp.boost_price, psp.price_comparison_position, psp.product_tmpl_id from product_saleschannel_price psp
left join product_product pp ON pp.product_tmpl_id = psp.product_tmpl_id
where sales_channel = 3 AND pp.default_code = '1000338990'

SELECT id, product_tmpl_id, barcode, active FROM product_product WHERE default_code is not null AND LENGTH(default_code) = 10 and active = true

select ps.id, ps.product_tmpl_id, pp.id FROM product_supplierinfo ps
LEFT JOIN product_product pp ON ps.product_tmpl_id = pp.product_tmpl_id
WHERE pp.active is False

DELETE FROM product_supplierinfo where id IN (1675830, 1675938, 1675827, 1675937)

SELECT pp.default_code, rp.cartomark_ref, psi.price, psi.stock, ss.cost FROM product_supplierinfo psi
LEFT JOIN product_product pp on psi.product_id = pp.id
LEFT JOIN res_partner rp on psi.name = rp.id
LEFT JOIN supplier_shippinginfo ss on rp.id = ss.supplier
WHERE psi.price > 0 AND psi.stock > 0 AND psi.write_date >= now() - interval '1 hours' AND ss.country = 57 AND ss.type_of_shipping='paketdienst'
ORDER BY default_code ASC

SELECT pp.default_code FROM product_supplierinfo psi
LEFT JOIN product_product pp on psi.product_id = pp.id
WHERE psi.price > 0 AND psi.stock > 0 AND psi.write_date >= now() - interval '3 hours'
GROUP BY pp.default_code
ORDER BY default_code ASC

SELECT psi.product_tmpl_id FROM product_supplierinfo psi
WHERE psi.price > 0 AND psi.stock > 0 AND psi.write_date >= now() - interval '2 hours'
GROUP BY psi.product_tmpl_id
ORDER BY psi.product_tmpl_id ASC

SELECT psp.product_tmpl_id FROM product_saleschannel_price psp
LEFT JOIN product_supplierinfo ps ON ps.product_tmpl_id = psp.product_tmpl_id
WHERE psp.price > 0 AND psp.stock > 0 AND ps.write_date >= now() - interval '3 hours'
AND psp.sales_channel = 3
GROUP BY psp.product_tmpl_id
ORDER BY psp.product_tmpl_id ASC

delete from pricemonitor_products where id > 0

select pmp.type, pmp.name as productTmplId, pp.barcode, pp.default_code, psp.price, (psp.price*1.19) as with_tax, psp.boost_price, pmp.prices, psp.sales_channel, psp.price_comparison_position from pricemonitor_products pmp
left join product_product pp on pmp.name = pp.product_tmpl_id
left join product_saleschannel_price psp on pmp.name = psp.product_tmpl_id
where pp.barcode = '8807622502002' and psp.sales_channel = 3

Select product_id, pp.product_tmpl_id as productTemplateID, pp.default_code, pp.barcode, price.api_type, COUNT(ail.id) as total from account_invoice_line ail
left join product_product pp on ail.product_id = pp.id
left join product_template pt on pp.product_tmpl_id = pt.id
LEFT JOIN product_saleschannel_price price on pt.id = price.product_tmpl_id
WHERE pt.type = 'product' AND pp.active = TRUE AND regexp_replace(pp.default_code, '\D', '', 'g') !='' AND LENGTH(pp.default_code) = 10
AND pp.barcode != '' AND price.sales_channel = 3 AND price.stock > 0 AND ail.create_date > now()-interval '28 days' group by product_id, pp.id, pp.default_code, price.api_type having Count(*) > 0 order by total DESC LIMIT 1600

SELECT si.supplier, cost FROM supplier_shippinginfo si
LEFT JOIN res_partner ON res_partner.id = si.supplier
WHERE country=57 AND type_of_shipping='paketdienst' AND res_partner.tyre_supplier = True
AND res_partner.active = True

update product_supplierinfo set write_date='2019-02-05 03:22:45.104484' where name = 211

update product_saleschannel_price set boost_api = null, api_position=null where id > 0

SELECT pp.default_code, pp.barcode, pmp.name, psp.price, pmp.prices FROM pricemonitor_products pmp
LEFT JOIN product_product pp ON pp.product_tmpl_id = pmp.name
LEFT JOIN product_saleschannel_price psp ON psp.product_tmpl_id = pmp.name
WHERE pmp.api_provider = 1 AND psp.sales_channel = 3 AND pmp.type = 'top1000'

select pp.default_code, pp.barcode, pmp.prices, pmp.name from pricemonitor_products pmp
left join product_product pp on pmp.name = pp.product_tmpl_id
where api_provider = 1

UPDATE product_saleschannel_price set boost_price = null where boost_price is not null

select pmp.type, pmp.name as productTmplId, pp.barcode, pp.default_code, psp.price, (psp.price*1.19) as with_tax, psp.boost_price, (psp.boost_price*1.19) as boost_price_with_tax, pmp.prices, psp.price_comparison_position from pricemonitor_products pmp
left join product_product pp on pmp.name = pp.product_tmpl_id
left join product_saleschannel_price psp on pmp.name = psp.product_tmpl_id
where psp.boost_price - psp.price > 10 and psp.sales_channel = 3

select pmp.type, pmp.name as productTmplId, pp.barcode, pp.default_code, psp.price, (psp.price*1.19) as with_tax, psp.boost_price, (psp.boost_price*1.19) as boost_price_with_tax, pmp.prices, psp.price_comparison_position from pricemonitor_products pmp
left join product_product pp on pmp.name = pp.product_tmpl_id
left join product_saleschannel_price psp on pmp.name = psp.product_tmpl_id
where pp.barcode='8808563372907' and psp.sales_channel = 3

select pmp.type, pmp.name as productTmplId, pp.barcode, pp.default_code, psp.price, (psp.price*1.19) as with_tax, psp.boost_price, (psp.boost_price*1.19) as boost_price_with_tax, pmp.prices, psp.price_comparison_position from pricemonitor_products pmp
left join product_product pp on pmp.name = pp.product_tmpl_id
left join product_saleschannel_price psp on pmp.name = psp.product_tmpl_id
where pp.product_tmpl_id = 458 and psp.sales_channel = 3

select default_code, barcode from product_product where default_code='1000234884' or default_code = '1000356003'

update sale_order set date_order=date_order - interval '2 hours' where date_order >= '2019-04-04 12:01:38'

SELECT psi.name, psi.price, psi.stock, psi.product_tmpl_id,
psi.product_code, pt.default_code, pt.weight FROM product_supplierinfo psi
LEFT JOIN product_template pt on psi.product_tmpl_id = pt.id
WHERE psi.price > 0 AND psi.stock > 0 AND warn is not True AND psi.write_date >='2019-04-08 00:00:00.000000'

SELECT ai.name, count(ai.id) FROM account_invoice ai where ai.state != 'cancel' GROUP BY ai.name HAVING COUNT(*) > 2

SELECT ai.origin, count(ai.id) FROM account_invoice ai where ai.state != 'cancel' GROUP BY ai.origin HAVING COUNT(*) > 2

SELECT name, count(id) FROM account_invoice ai where ai.state = 'open' AND create_date >= '2019-04-04 12:01:38' GROUP BY name having count(*) > 2