关键词:
delete from calculation where id > 0
delete from product_saleschannel_price where id > 0
delete from product_supplierinfo where id > 0
delete from product_supplierinfo where name = 23
delete from product_image where id > 0
delete from ir_attachment where res_model='product.image'
update res_partner SET last_csv_timestamp = 0 where cartomark_ref is not null
select default_code, Count(id) from product_template where id > 0 group by default_code having Count(*) > 1
select ps.name, res_partner.name, res_partner.csv_filename, res_partner.last_csv_timestamp, res_partner.in_cartomak, COUNT(ps.id) from product_supplierinfo ps
left join res_partner on ps.name = res_partner.id
WHERE price > 0 and ps.write_date >= now()-interval '1 hours' group by ps.name, res_partner.name, res_partner.csv_filename,res_partner.last_csv_timestamp, res_partner.in_cartomak ORDER BY res_partner.name
select ps.name, res_partner.name, res_partner.csv_filename, COUNT(ps.id) from product_supplierinfo ps
left join res_partner on ps.name = res_partner.id
group by ps.name, res_partner.name, res_partner.csv_filename
select product_tmpl_id, COUNT(id) from product_supplierinfo group by product_tmpl_id having count(*) > 5
select product_tmpl_id, COUNT(ps.id) from product_supplierinfo ps
left join product_template template2 on ps.product_tmpl_id = template2.id
where template2.weight > 15
group by product_tmpl_id having count(*) > 6
select product_tmpl_id from product_supplierinfo group by product_tmpl_id
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
select rp.name, product_tmpl_id, price, stock from product_supplierinfo ps
left join res_partner rp on ps.name = rp.id
where product_tmpl_id=124
select rp.name, template2.default_code, psp.product_tmpl_id, ps.price as supplierPrice, psp.price as totalPrice, round((psp.price * 1.19)::numeric, 2) as withTax, psp.boost_price, psp.transport_cost, psp.extra_cost, psp.sales_channel from product_saleschannel_price psp
left join product_supplierinfo ps on psp.supplier = ps.name
left join res_partner rp on ps.name = rp.id
left join product_template template2 on psp.product_tmpl_id = template2.id
where psp.product_tmpl_id = ps.product_tmpl_id and boost_price is not null and psp.sales_channel = 3 and template2.default_code='1000494845'
ORDER BY template2.default_code
delete from ir_attachment where res_model='product.template'
truncate product_saleschannel_price CASCADE
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
update product_template set list_price = (select (ek_price - transport_cost) as prs from product_saleschannel_price where product_tmpl_id = 141 and sales_channel = 3) where id=141
DELETE FROM sale_order where id > 29
DELETE FROM product_supplier_pricehistory where id > 0
Select 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 regexp_replace(pp.default_code, '\D', '', 'g') !='' AND LENGTH(pp.default_code) = 10 AND pp.barcode != ''
group by product_id, pp.id, pp.default_code having Count(*) > 0 order by total DESC LIMIT 1600
SELECT pmp.name, pmp."minPriceBoundary", pmp."maxPriceBoundary", pmp."referencePrice", pp.default_code as productId, pp.barcode as gtin, pt.name as manufacturer_name FROM pricemonitor_products pmp
LEFT JOIN product_product pp on pmp.name = pp.product_tmpl_id
LEFT JOIN product_template pt on pmp.name = pt.id
UPDATE pricemonitor_products set "referencePrice"=1 WHERE "referencePrice" = 0
select psi.name, rp.name, psi.product_code, psi.price, psi.stock, ssi.cost, (psi.price + ssi.cost) as sum FROM product_supplierinfo psi
LEFT JOIN supplier_shippinginfo ssi ON ssi.supplier = psi.name
LEFT JOIN res_partner rp on psi.name = rp.id
where psi.product_tmpl_id = 278946 AND ssi.country = 57 AND ssi.type_of_shipping = 'paketdienst'
ORDER BY sum ASC
update product_supplierinfo set price=0, stock=0 where id > 0
delete from pricemonitor_products where id > 0
SELECT pp.default_code, pp.barcode, pss.id, pss.price FROM product_saleschannel_price pss
LEFT JOIN product_product pp ON pp.product_tmpl_id = pss.product_tmpl_id
WHERE sales_channel=3
DELETE FROM product_supplierinfo
USING product_supplierinfo AS i
LEFT JOIN res_partner AS p ON i."name" = p.id
WHERE i.write_date < now() AND p.in_cartomak = TRUE
update product_supplierinfo set write_date = '2019-01-22 03:00:00.000000', create_date = '2019-01-20 03:00:00.000000' where id > 0
DELETE FROM product_supplierinfo
USING product_supplierinfo AS i
LEFT OUTER JOIN res_partner AS p ON i.name = p.id
WHERE product_supplierinfo.id = i.id AND i.write_date < '2019-01-24 11:20:43.001177' AND p.in_cartomak = TRUE
DELETE FROM product_supplierinfo
USING product_supplierinfo AS i
LEFT JOIN res_partner AS p ON i.name = p.id
WHERE i.write_date < '2019-01-24 11:20:43.023363' AND p.in_cartomak = TRUE
delete from product_supplierinfo where name = 36
delete from pricemonitor_products where id > 0
update product_saleschannel_price set boost_api=Null, api_position=Null where boost_api is not null
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 psp.id, pp.barcode, psp.product_tmpl_id from product_saleschannel_price psp
LEFT JOIN product_product pp on psp.product_tmpl_id = pp.product_tmpl_id
where pp.barcode='5452000660480'
select pmp.type, pmp.name as productTmplId, pp.barcode, pp.default_code from pricemonitor_products pmp
left join product_product pp on pmp.name = pp.product_tmpl_id
where pp.barcode='5452000660480'
select pmp.type, pmp.name as productTmplId, pp.barcode, pp.default_code from pricemonitor_products pmp
left join product_product pp on pmp.name = pp.product_tmpl_id
where pmp.prices is null
update product_saleschannel_price set boost_api=Null, api_position=Null where id > 0
update pricemonitor_products set prices = Null where id > 0
UPDATE product_saleschannel_price SET boost_price=Null, api_type=Null,
price_comparison_position=Null WHERE api_type is not Null
SELECT pp.default_code, pp.barcode, pmp.name, Count(pmp.id) 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.sales_channel = 3 AND psp.sales_channel = 3
GROUP BY pp.default_code, pp.barcode, pmp.name having count(*) > 1
select product_tmpl_id, barcode from product_product where barcode = '6927116190170'
INSERT INTO stock_route_product (route_id, product_id)
SELECT slr.id, pt.id FROM product_template pt, stock_location_route slr
WHERE pt.default_code is not null AND pt.type = 'product' AND slr.name='Drop Shipping'
ON CONFLICT DO NOTHING
delete from stock_route_product where route_id = 5