ODS CSVALL FILE="filename.csv";

/* SAS code that produces output */

ODS CSVALL CLOSE;


/* Example CSV */
ods csvall file="&outpath/cars.csv";
proc print data=sashelp.cars noobs;
    var Make Model Type MSRP MPG_City MPG_Highway;
    format MSRP dollar8.;
run;
ods csvall close;

libname myxl xlsx "&outpath/cars.xlsx";

data myxl.asiacars;
    set sashelp.cars;
    where origin='Asia';
run;

libname myxl clear;

PROC EXPORT DATA=input-table OUTFILE="output-file"
    <DBMS =identifier> <REPLACE>;
RUN;

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

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 account_invoice ai ON ail.invoice_id = ai.id
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

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='8807622385605' 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 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 psp.boost_price is null and psp.price_comparison_position is not null and psp.sales_channel = 3

SELECT ai.origin, ai.state, ai.date_invoice, ai.date_due FROM account_invoice ai
WHERE ai.date_invoice >= '2019-03-19' AND ai.date_due < '2019-03-20'

select so.name, po.origin, pol.product_id, pt.name, psp.supplier, psp.stock, psp.price, psp.ek_price FROM sale_order so
LEFT JOIN purchase_order po ON so.name = po.origin
LEFT JOIN purchase_order_line pol ON po.id = pol.order_id
LEFT JOIN product_template pt ON pt.id = pol.product_id
LEFT JOIN product_saleschannel_price psp ON psp.product_tmpl_id = pol.product_id
LEFT JOIN res_partner rp on psp.supplier = rp.id
WHERE so.name = '286066'

delete from sale_order where id > 0

delete from account_invoice where create_date >= '2019-03-02'

select id from account_invoice where create_date >= '2019-03-02'

pg_restore -j 16 -C -d datavizbase  /storage/20190508_datavizbase.bak

pg_dump -Fd datavizbase -j 16 -f /db2/20190508_datavizbase.bak

IF expression THEN DO;
   	<executable statements>	
END; 
ELSE IF expression THEN DO;
   	<executable statements>
END;
ELSE DO;
    <executable statements>
END;

proc univariate data=pg1.np_summary;
    var Acres;
run;

proc univariate
	data=pg1.np_summary;
	var DayVisits TentCampers RVCampers;
run;

proc freq data=pg1.np_summary;
    tables Reg Type;
run;

proc freq
	data=pg1.np_summary;
	TABLES Reg Type;
run;