mimron’s gists

mimron / eod_depstore_sales_new.sql

Last active July 17, 2023 03:26


	SELECT
	s.code,
	TO_CHAR(ptrx.sales_date,'DDMMYYYY'),
	(case when ti.category_id = 'DEPTSTORE' then SUBSTRING(ti.ean13code, 6, 6) else ti.ean13code end)::varchar,
	'',
	(case when ti.category_id = 'DEPTSTORE' then SUBSTRING(ti.ean13code, 2, 4) else '' end)::varchar,
	((case when ptrx.type = 'SALE' then 1 when ptrx.type = 'REFUND' then 0 else -1 end) * (case when ti.sales_type = 'VOID' then -1 else 1 end * ti.quantity))::int quantity,
	(CASE WHEN ti.is_voided = 'N' AND ptrx.type = 'SALE' THEN 1 ELSE -1 END) * (ti.price_subtotal - ti.discount_amount - ti.member_discount_amount - ti.disc_btn_amount - ti.crm_member_discount_amount )::bigint,
	'',

mimron / eod_depstore_sales_original.sql

Last active July 17, 2023 03:26

	SELECT
	s.code,
	TO_CHAR(ptrx.sales_date,'DDMMYYYY'),
	(case when ti.category_id = 'DEPTSTORE' then SUBSTRING(ti.ean13code, 6, 6) else ti.ean13code end)::varchar,
	'',
	(case when ti.category_id = 'DEPTSTORE' then SUBSTRING(ti.ean13code, 2, 4) else '' end)::varchar,
	((case when ptrx.type = 'SALE' then 1 when ptrx.type = 'REFUND' then 0 else -1 end) * (case when ti.sales_type = 'VOID' then -1 else 1 end * ti.quantity))::int quantity,
	(CASE WHEN ti.is_voided = 'N' AND ptrx.type = 'SALE' THEN 1 ELSE -1 END) * (ti.price_subtotal - ti.discount_amount - ti.member_discount_amount - ti.disc_btn_amount - ti.crm_member_discount_amount )::bigint,
	'',
	right(itr.id_staff,10) id_staff,

mimron / eod_new

Last active July 17, 2023 03:26

	SELECT
	store_code AS ISCSITCD,
	sales_date AS ISCSALDAT,
	product_id ISCPLUNUM,
	type AS ISCSALTYPE,
	case when type = 'R' then -1 * sum(total_rtcnt - total_voided_rtcnt) else SUM((total_cnt - total_voided_cnt) + (total_pcnt - total_voided_pcnt)) end AS ISCSALQTY,
	ROUND(SUM((total_amt - total_voided_amt) + (total_pamt - total_voided_pamt) - (total_rfamt - total_voided_rfamt) - (total_rtamt - total_voided_rtamt))) AS ISCSALAMT,
	case when vat_code = '1' then ROUND(SUM((total_amt - total_voided_amt - (total_amt_vat - total_voided_amt_vat)) + (total_pamt - total_voided_pamt - (total_pamt_vat - total_voided_pamt_vat)) - (total_rfamt - total_voided_rfamt - (total_rfamt_vat - total_voided_rfamt_vat)) - (total_rtamt - total_voided_rtamt - (total_rtamt_vat - total_voided_rtamt_vat)))) else 0 end AS ISCVATSALAMT,
	ROUND(SUM((total_pamt_disc - total_voided_pamt_disc))) AS ISCSALDISC,
	ROUND(SUM ( total_pamt_disc - total_voided_pamt_disc - (total_pamt_disc_vat - total_voided_pamt_disc_v))) AS ISCVATSALDISC,

mimron / eod_old

Created May 10, 2023 07:27

	SELECT
	store_code AS ISCSITCD,
	sales_date AS ISCSALDAT,
	product_id ISCPLUNUM,
	type AS ISCSALTYPE,
	case when type = 'R' then -1 * sum(total_rtcnt - total_voided_rtcnt) else SUM((total_cnt - total_voided_cnt) + (total_pcnt - total_voided_pcnt)) end AS ISCSALQTY,
	ROUND(SUM((total_amt - total_voided_amt) + (total_pamt - total_voided_pamt) - (total_rfamt - total_voided_rfamt) - (total_rtamt - total_voided_rtamt))) AS ISCSALAMT,
	case when vat_code = '1' then ROUND(SUM((total_amt - total_voided_amt - (total_amt_vat - total_voided_amt_vat)) + (total_pamt - total_voided_pamt - (total_pamt_vat - total_voided_pamt_vat)) - (total_rfamt - total_voided_rfamt - (total_rfamt_vat - total_voided_rfamt_vat)) - (total_rtamt - total_voided_rtamt - (total_rtamt_vat - total_voided_rtamt_vat)))) else 0 end AS ISCVATSALAMT,
	ROUND(SUM((total_pamt_disc - total_voided_pamt_disc))) AS ISCSALDISC,
	ROUND(SUM ( total_pamt_disc - total_voided_pamt_disc - (total_pamt_disc_vat - total_voided_pamt_disc_v))) AS ISCVATSALDISC,

mimron / trx_online

Created February 21, 2023 07:28

	{
	"reference_number": "1303114365-567DSF5ADS6",
	"transaction_date": "2022-06-20",
	"sales_date": "2022-06-20",
	"customer_id": "RED&WHITECUSTOMOER001",
	"orders": [
	{
	"order_id": "1303114365-567DSF5ADS6",
	"merchant_id": "REDWHITE",
	"store_code": "10019",

mimron / trx_offline

Created February 21, 2023 07:27