boogerlad/mj - Change PQTLWOFEMHL6BXIVUAYBHF5Z4F4CK7AD2QS4NOWGAFGV63EWKJNAC

according to https://www.justice.gc.ca/eng/cj-jp/cannabis/ looks like there is a standardized way to calculate gram equivalency except for oils, sprays, capsules (need info from lp) For all other types, gram equivalency should be null and be computed dynamically also, use insert select syntax with cross join so that subsequent dependent inserts don't happen if first one fails (due to unique constraint violation, for example) now it's possible to detect duplicate products and return an error message instead of relying on try catch

Created by boogerlad on April 15, 2020

PQTLWOFEMHL6BXIVUAYBHF5Z4F4CK7AD2QS4NOWGAFGV63EWKJNAC

Dependencies

In channels

main

Change contents

Replacement in initialize.sql at line 144 [7.17]
∅:D[5.684] → [6.7531:7567]
B:BD[8.174] → [6.7531:7567]
```
	gram_equivalency decimal not null
```
[5.684]
[7.2921]
```
	gram_equivalency decimal
```

Replacement in initialize.sql at line 501 [7.17]

∅:D[9.400] → [10.0:192]

B:BD[11.1734] → [10.0:192]

create or replace function ios_variant_id(_product_id integer, _portions smallint, _quantity decimal, _flavor citext, _gram_equivalency decimal, out _variant_id integer, out g decimal) as $$

[9.400]

[11.1925]

create or replace function ios_variant_id(_product_id integer, _portions smallint, _quantity decimal, _flavor citext = null, _gram_equivalency decimal = null, out _variant_id integer, out g decimal) as $$

Replacement in scrape/ocs.ca.js at line 210 [12.11674]

B:BD[12.20159] → [2.0:129]

				let gram_equivalency = new Big(quickview.variants.find(v => v.id == e[i][j].objectID).metafields.variant.gram_equivalency);

[12.20159]

[13.6073]

				let gram_equivalency = e[i][0].type === 'oil' ||  e[i][0].type === 'spray' ||  e[i][0].type === 'capsule' ? new Big(quickview.variants.find(v => v.id == e[i][j].objectID).metafields.variant.gram_equivalency) : null;

Replacement in scrape/ocs.ca.js at line 232 [12.11674]

B:BD[14.885] → [3.0:95]

∅:D[3.95] → [11.5725:5805]

B:BD[11.5725] → [11.5725:5805]

B:BD[11.5805] → [3.96:234]

				let [variantID, g] = (await postgres.query(`select ios_variant_id($1, $2, $3, $4, $5)`, [
					productID,
					portions,
					quantity,
					e[i][j].options.flavour,
					gram_equivalency.toString()
				])).rows[0].ios_variant_id.slice(1, -1).split(',');
				if(g.length && !gram_equivalency.eq(g)) {

[14.885]

[3.234]

				let params = [productID, portions, quantity]
				let [variantID, g] = (await postgres.query(
					`select ios_variant_id($1, $2, $3${e[i][j].options.flavour ? `, $${params.push(e[i][j].options.flavour)}` : ''}${gram_equivalency === null ? '' : `, $${params.push(gram_equivalency.toString())}`})`,
					params
				)).rows[0].ios_variant_id.slice(1, -1).split(',');
				if(g.length && gram_equivalency !== null && !gram_equivalency.eq(g)) {

Deletion in server.js at line 513 [16.590]
B:BD[15.250] → [4.11550:11571]
```
								let values;
```

Replacement in server.js at line 514 [16.590]

B:BD[4.11615] → [4.11615:11733]

									//default portions. gram equivalency = quantity
									values = parameters.variants.reduce((acc, v) => {

[4.11615]

[4.11733]

									let values = parameters.variants.reduce((acc, v) => {

Replacement in server.js at line 516 [16.590]

B:BD[4.11771] → [4.11771:11908]

											let pNum = params.push(v.quantity);
											acc.push(`((select product_id from insert1), default, $${pNum}, $${pNum})`);

[4.11771]

[4.11908]

											acc.push(`($${params.push(v.quantity)})`);

Replacement in server.js at line 520 [16.590]

B:BD[4.11962] → [4.11962:12125]

								} else if(parameters.type === 'preroll') {
									//gram equivalency = portions * quantity
									values = parameters.variants.reduce((acc, v) => {

[4.11962]

[4.12125]

									if(values.length) {
										query += `, insert${++q} as (insert into variant (product_id, quantity) select product_id, x::numeric from insert1 cross join (values ${values.join(',')}) as t(x) on conflict do nothing)`;
									}
								} else if(parameters.type === 'preroll' || parameters.type === 'tea' || parameters.type === 'gummy' || parameters.type === 'mint' || parameters.type === 'chocolate' || parameters.type === 'baked') {
									let values = parameters.variants.reduce((acc, v) => {

Replacement in server.js at line 526 [16.590]

B:BD[4.12203] → [4.12203:12390]

											acc.push(`((select product_id from insert1), $${params.push(v.portions)}, $${params.push(v.quantity)}, $${params.push((new Big(v.quantity)).times(v.portions).toString())})`);

[4.12203]

[4.12390]

											acc.push(`($${params.push(v.portions)}, $${params.push(v.quantity)})`);

Replacement in server.js at line 530 [16.590]

B:BD[4.12444] → [4.12444:12869]

								} else if(parameters.type === 'capsule') {
									//null quantity possible
									values = parameters.variants.reduce((acc, v) => {
										if(isFinite(v.gram_equivalency) && Number.isInteger(Number(v.portions))) {
											acc.push(`((select product_id from insert1), $${params.push(v.portions)}, ${isFinite(v.quantity) ? '$' + params.push(v.quantity) : 'null'}, $${params.push(v.gram_equivalency)})`);

[4.12444]

[4.12869]

									if(values.length) {
										query += `, insert${++q} as (insert into variant (product_id, portions, quantity) select product_id, x::smallint, y::numeric from insert1 cross join (values ${values.join(',')}) as t(x, y) on conflict do nothing)`;
									}
								} else if(parameters.type === 'cartridge' || parameters.type === 'disposable') {
									let values = parameters.variants.reduce((acc, v) => {
										if(isFinite(v.quantity)) {
											acc.push(`($${params.push(v.quantity)}, ${v.flavor ? '$' + params.push(v.flavor) : 'null'})`);

Replacement in server.js at line 540 [16.590]

B:BD[4.12923] → [4.12923:13426]

								} else {
									//default if falsy portions
									values = parameters.variants.reduce((acc, v) => {
										if(isFinite(v.gram_equivalency) && isFinite(v.quantity)) {
											acc.push(`((select product_id from insert1), ${v.portions ? '$' + params.push(v.portions) : 'default'}, $${params.push(v.quantity)}, $${params.push(v.gram_equivalency)}${parameters.type === 'disposable' || parameters.type === 'cartridge' ? `, ${v.flavor ? '$' + params.push(v.flavor) : 'null'}` : ''})`);

[4.12923]

[4.13426]

									if(values.length) {
										query += `, insert${++q} as (insert into variant (product_id, quantity, flavor) select product_id, x::numeric, y from insert1 cross join (values ${values.join(',')}) as t(x, y) on conflict do nothing)`;
									}
								} else if (parameters.type === 'oil' || parameters.type === 'spray') {
									let values = parameters.variants.reduce((acc, v) => {
										if(isFinite(v.gram_equivalency) && Number.isInteger(Number(v.quantity))) {
											acc.push(`($${params.push(v.quantity)}, $${params.push(v.gram_equivalency)})`);

Replacement in server.js at line 550 [16.590]

B:BD[4.13480] → [4.13480:13491]

∅:D[4.13491] → [17.8668:8697]

B:BD[17.8668] → [17.8668:8697]

B:BD[17.8697] → [4.13492:13744]

								}
								if(values.length) {
									query += `, insert${++q} as (insert into variant (product_id, portions, quantity, gram_equivalency${parameters.type === 'disposable' || parameters.type === 'cartridge' ? ', flavor' : ''}) values ${values.join(', ')} on conflict do nothing)`;

[4.13480]

[15.795]

									if(values.length) {
										query += `, insert${++q} as (insert into variant (product_id, quantity, gram_equivalency) select product_id, y::numeric, z::numeric from insert1 cross join (values ${values.join(',')}) as t(y, z) on conflict do nothing)`;
									}
								} else if(parameters.type === 'capsule') {
									let values = parameters.variants.reduce((acc, v) => {
										if(isFinite(v.gram_equivalency) && Number.isInteger(Number(v.portions))) {
											acc.push(`($${params.push(v.portions)}, ${isFinite(v.quantity) ? '$' + params.push(v.quantity) : 'null'}, $${params.push(v.gram_equivalency)})`);
										}
										return acc;
									}, []);
									if(values.length) {
										query += `, insert${++q} as (insert into variant (product_id, portions, quantity, gram_equivalency) select product_id, x::smallint, y::numeric, z::numeric from insert1 cross join (values ${values.join(',')}) as t(x, y, z) on conflict do nothing)`;
									}

Replacement in server.js at line 566 [16.590]

B:BD[17.8987] → [17.8987:9209]

								query += `, insert${++q} as (insert into product_image (product_id, destination) values ${parameters.images.map(i => `((select product_id from insert1), $${params.push(i)})`).join(', ')} on conflict do nothing)`;

[17.8987]

[15.924]

								query += `, insert${++q} as (insert into product_image (product_id, destination) select product_id, x from insert1 cross join (values ${parameters.images.map(i => `(${params.push(i)})`).join(',')}) as t(x) on conflict do nothing)`;

Replacement in server.js at line 568 [16.590]

B:BD[15.934] → [17.9210:9322]

							let product = (await pool.query(query + ' select product_id from insert1', params)).rows[0].product_id;

[15.934]

[18.9626]

							let product = (await pool.query(query + ' select product_id from insert1', params)).rows;

Replacement in server.js at line 570 [16.590]

B:BD[18.9659] → [18.9659:9692]

								response_ID: request_ID

[18.9659]

[18.9692]

								response_ID: request_ID,
								data: product.length === 1 && Number.isInteger(Number(product[0].product_id)) ? 'successed' : 'product already exists'