131 lines
10 KiB
SQL
131 lines
10 KiB
SQL
INSERT INTO order_rows (id, type, sub_type, status, order_id, designer_id, price, commission_amount, data, inserted)
|
|
OVERRIDING SYSTEM VALUE
|
|
SELECT rowid, itype::order_row_type, isubtype::order_row_sub_type, status::order_row_status, order_id, designer_id, price, commission_amount, data, inserted
|
|
FROM (
|
|
SELECT orow.orderid order_id,
|
|
orow.rowid,
|
|
orow.inserted,
|
|
(odata.odata ->> 'designerId')::int designer_id,
|
|
(odata.odata ->> 'commission_amount')::float commission_amount,
|
|
CASE
|
|
-- next line are for admin products
|
|
WHEN (odata.odata ->> 'type') = 'stock' AND (odata.odata ->> 'path') is NULL THEN 'fee'
|
|
WHEN (odata.odata ->> 'modifier') LIKE '%Discount%' THEN 'discount'
|
|
WHEN (odata.odata ->> 'modifier') LIKE '%discount%' THEN 'discount'
|
|
WHEN (odata.odata ->> 'modifier') LIKE '%Fee%' THEN 'fee'
|
|
WHEN (odata.odata ->> 'path') = 'sample' THEN 'sample'
|
|
WHEN (odata.odata ->> 'type') = 'stock' THEN 'stock'
|
|
WHEN (odata.odata ->> 'status') = 'cancelled' THEN 'refund'
|
|
ELSE 'print'
|
|
END itype,
|
|
|
|
CASE
|
|
-- next line are for admin products
|
|
WHEN (odata.odata ->> 'type') = 'stock' AND (odata.odata ->> 'path') is NULL THEN 'admin_product'
|
|
WHEN (odata.odata ->> 'modifier') = 'contractCustomerDiscount' THEN 'contract_customer_discount'
|
|
WHEN (odata.odata ->> 'modifier') = 'discount' THEN 'discount_code'
|
|
WHEN (odata.odata ->> 'path') = 'sample' THEN 'sample'
|
|
WHEN (odata.odata ->> 'path') = 'doityourselfframe' THEN 'canvas_frame'
|
|
WHEN (odata.odata ->> 'path') = 'wallpaper-kit' THEN 'wallpaper_kit'
|
|
WHEN (odata.odata ->> 'group') LIKE '%wallpaper' AND (odata.odata ->> 'inquiryid') IS NULL THEN 'wallpaper'
|
|
WHEN (odata.odata ->> 'group') LIKE 'canvas' AND (odata.odata ->> 'inquiryid') IS NULL THEN 'canvas'
|
|
WHEN (odata.odata ->> 'group') LIKE 'poster' AND (odata.odata ->> 'inquiryid') IS NULL THEN 'poster'
|
|
WHEN (odata.odata ->> 'group') LIKE 'framed-print' AND (odata.odata ->> 'inquiryid') IS NULL THEN 'framed_print'
|
|
WHEN (odata.odata ->> 'group') LIKE '%wallpaper' AND (odata.odata ->> 'inquiryid') IS NOT NULL THEN 'inquiry_wallpaper'
|
|
WHEN (odata.odata ->> 'group') LIKE 'canvas' AND (odata.odata ->> 'inquiryid') IS NOT NULL THEN 'inquiry_canvas'
|
|
WHEN (odata.odata ->> 'group') LIKE 'poster' AND (odata.odata ->> 'inquiryid') IS NOT NULL THEN 'inquiry_poster'
|
|
WHEN (odata.odata ->> 'group') LIKE 'framed-print' AND (odata.odata ->> 'inquiryid') IS NOT NULL THEN 'inquiry_framed_print'
|
|
ELSE 'no_subtype'
|
|
END isubtype,
|
|
|
|
|
|
CASE
|
|
-- next line are for admin products
|
|
WHEN (odata.odata ->> 'type') = 'stock' AND (odata.odata ->> 'path') is NULL THEN 'no_status'
|
|
WHEN (odata.odata ->> 'status') = 'pack' THEN 'process'
|
|
WHEN (odata.odata ->> 'status') IS NULL THEN 'no_status'
|
|
WHEN (odata.odata ->> 'imageprocessingrejected') = 'true' THEN 'error'
|
|
WHEN (odata.odata ->> 'imagedonotprint') = 'true' THEN 'cancelled'
|
|
ELSE (odata.odata ->> 'status')
|
|
END status,
|
|
(odata.odata ->> 'price')::float price,
|
|
odata.odata - '{designerId, commission_amount, status, price}'::text[] "data"
|
|
FROM "order-rows" orow
|
|
JOIN (
|
|
SELECT od.rowid,
|
|
json_object_agg(orf.name, od.value)::jsonb AS odata
|
|
FROM "order-rows_details" od
|
|
JOIN "order-row_fields" orf
|
|
ON od.fieldid = orf.fieldid
|
|
GROUP BY 1
|
|
) odata ON orow.rowid = odata.rowid
|
|
WHERE orow.orderid IN
|
|
(
|
|
SELECT oof.orderid
|
|
FROM "order-orders_fields" oof
|
|
WHERE fieldid = 168
|
|
AND "value" = 'true'
|
|
)
|
|
) s;
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{commission_resale}', to_jsonb((data ->> 'commission_resale')::int)) WHERE data->>'commission_resale' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{height}', to_jsonb((data ->> 'height')::int)) WHERE data->>'height' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{inquiryid}', to_jsonb((data ->> 'inquiryid')::int)) WHERE data->>'inquiryid' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{printId}', to_jsonb((data ->> 'printId')::int)) WHERE data->>'printId' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{productId}', to_jsonb((data ->> 'productId')::int)) WHERE data->>'productId' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{resolution}', to_jsonb((data ->> 'resolution')::int)) WHERE data->>'resolution' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{stockId}', to_jsonb((data ->> 'stockId')::int)) WHERE data->>'stockId' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{width}', to_jsonb((data ->> 'width')::int)) WHERE data->>'width' <> '';
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{commission}', to_jsonb((data ->> 'commission')::numeric)) WHERE data->>'commission' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{discountValue}', to_jsonb((data ->> 'discountValue')::numeric)) WHERE data->>'discountValue' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{displayHeight}', to_jsonb((data ->> 'displayHeight')::numeric)) WHERE data->>'displayHeight' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{displayWidth}', to_jsonb((data ->> 'displayWidth')::numeric)) WHERE data->>'displayWidth' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{fixedVAT}', to_jsonb((data ->> 'fixedVAT')::numeric)) WHERE data->>'fixedVAT' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{height_mm}', to_jsonb((data ->> 'height_mm')::numeric)) WHERE data->>'height_mm' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{imageHeight}', to_jsonb((data ->> 'imageHeight')::int)) WHERE data->>'imageHeight' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{imageWidth}', to_jsonb((data ->> 'imageWidth')::int)) WHERE data->>'imageWidth' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{percent}', to_jsonb((data ->> 'percent')::numeric)) WHERE data->>'percent' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{retouch_price}', to_jsonb((data ->> 'retouch_price')::numeric)) WHERE data->>'retouch_price' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{VAT}', to_jsonb((data ->> 'VAT')::numeric)) WHERE data->>'VAT' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{x}', to_jsonb((data ->> 'x')::numeric)) WHERE data->>'x' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{y}', to_jsonb((data ->> 'y')::numeric)) WHERE data->>'y' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{weight}', to_jsonb((data ->> 'weight')::numeric)) WHERE data->>'weight' <> '';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{width_mm}', to_jsonb((data ->> 'width_mm')::numeric)) WHERE data->>'width_mm' <> '';
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{automated}', to_jsonb(false)) WHERE data->>'automated' = '0';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{automated}', to_jsonb(true)) WHERE data->>'automated' = '1';
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{framed}', to_jsonb(false)) WHERE data->>'framed' = '0';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{framed}', to_jsonb(true)) WHERE data->>'framed' = '1';
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{uploaded}', to_jsonb(false)) WHERE data->>'uploaded' = '0';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{uploaded}', to_jsonb(true)) WHERE data->>'uploaded' = '1';
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{imagedonotprint}', to_jsonb(false)) WHERE data->>'imagedonotprint' = 'false';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{imagedonotprint}', to_jsonb(true)) WHERE data->>'imagedonotprint' = 'true';
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{imageprocessed}', to_jsonb(false)) WHERE data->>'imageprocessed' = 'false';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{imageprocessed}', to_jsonb(true)) WHERE data->>'imageprocessed' = 'true';
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{imageprocessingrejected}', to_jsonb(false)) WHERE data->>'imageprocessingrejected' = 'false';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{imageprocessingrejected}', to_jsonb(true)) WHERE data->>'imageprocessingrejected' = 'true';
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{mirrored}', to_jsonb(false)) WHERE data->>'mirrored' = '0';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{mirrored}', to_jsonb(true)) WHERE data->>'mirrored' = '1';
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{no_shipping}', to_jsonb(false)) WHERE data->>'no_shipping' = 'False';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{no_shipping}', to_jsonb(true)) WHERE data->>'no_shipping' = 'True';
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{packed}', to_jsonb(false)) WHERE data->>'packed' = 'false';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{packed}', to_jsonb(true)) WHERE data->>'packed' = 'true';
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{process}', to_jsonb(false)) WHERE data->>'process' = 'false';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{process}', to_jsonb(true)) WHERE data->>'process' = 'true';
|
|
|
|
UPDATE order_rows SET data = jsonb_set("data", '{edge_fixed}', to_jsonb(false)) WHERE data->>'edge_fixed' = 'false';
|
|
UPDATE order_rows SET data = jsonb_set("data", '{edge_fixed}', to_jsonb(true)) WHERE data->>'edge_fixed' = 'true';
|
|
|
|
-- Special case of a job row from 2015 that appeared in processing list
|
|
UPDATE order_rows SET type = 'refund' WHERE id = 303385;
|