Untitled
unknown
plain_text
a year ago
9.4 kB
3
Indexable
async report(
user: UserEntity,
queryDto: PageOptionsReport
): Promise<ResponseTransactionReport> {
const today = now(0, "minutes", "YYYY-MM-DD");
const {
startDate = today,
endDate = today,
tenant_id: tenantIdParam,
order,
} = queryDto;
let tenantId = tenantIdParam;
if (!tenantId) {
tenantId = user?.role_access?.tenant?.id;
}
const connection = getConnection();
const item = await connection.query(
`
SELECT
ROUND(sum(COALESCE(total_income,0))) as total_income,
count(*) as total_transaction,
ROUND(SUM (
CASE
WHEN ts.payment_type = 'cash' THEN total_income
WHEN ts.payment_type = 'split-payment' THEN (
select sum(ptCash.amount - (ptCash.others_fee + ptCash.admin_fee) - ptCash.payment_mdr_fee)
from payment_transactions ptCash
where ptCash.reference_id like CONCAT('%IN-PAY_', ts.transaction_id, '%') and ptCash.channel_type='cash' and ptCash.deleted_at IS NULL
)
ELSE 0
END
)) as cash,
ROUND(SUM (
CASE
WHEN ts.payment_type = 'qris' THEN total_income
WHEN ts.payment_type = 'split-payment' THEN (
select sum(ptQris.amount - (ptQris.others_fee + ptQris.admin_fee) - (
CASE
WHEN transaction_tenants.type_charge_mdr = 'withdrawal'
THEN 0
ELSE ptQris.payment_mdr_fee
END
))
from payment_transactions ptQris
where ptQris.reference_id like CONCAT('%IN-PAY_', ts.transaction_id, '%') and ptQris.channel_type='qris' and ptQris.deleted_at IS NULL
)
ELSE 0
END
)) as qris,
SUM (
CASE
WHEN ts.payment_type = 'ewallet' OR ts.payment_type = 'balance' THEN total_income
ELSE 0
END
) as ewallet,
SUM (
CASE
WHEN ts.payment_type = 'omnichannel' THEN total_income
ELSE 0
END
) as omnichannel,
SUM (
CASE
WHEN ts.payment_type = 'brizzi' THEN total_income
ELSE 0
END
) as brizzi,
ROUND(SUM (
CASE
WHEN ts.payment_type = 'edc' THEN total_income
WHEN ts.payment_type = 'split-payment' THEN (
select sum(ptEdc.amount - (ptEdc.others_fee + ptEdc.admin_fee) - ptEdc.payment_mdr_fee)
from payment_transactions ptEdc
where ptEdc.reference_id like CONCAT('%IN-PAY_', ts.transaction_id, '%') and ptEdc.channel_type='edc' and ptEdc.deleted_at IS NULL
)
ELSE 0
END
)) as edc,
ROUND(SUM (
CASE
WHEN ts.payment_type = 'qris-merchant' THEN total_income
WHEN ts.payment_type = 'split-payment' THEN (
select sum(ptQrisMerchant.amount - (ptQrisMerchant.others_fee + ptQrisMerchant.admin_fee) - ptQrisMerchant.payment_mdr_fee)
from payment_transactions ptQrisMerchant
where ptQrisMerchant.reference_id like CONCAT('%IN-PAY_', ts.transaction_id, '%') and ptQrisMerchant.channel_type='qris-merchant' and ptQrisMerchant.deleted_at IS NULL
)
ELSE 0
END
)) as qris_merchant,
json_object(
'ovo', sum(
CASE
WHEN ts.channel_code = 'ID_OVO' THEN total_income
ELSE 0
END
),
'dana', sum(
CASE
WHEN ts.channel_code = 'ID_DANA' THEN total_income
ELSE 0
END
),
'shopee_pay', sum(
CASE
WHEN ts.channel_code = 'ID_SHOPEEPAY' THEN total_income
ELSE 0
END
),
'linkaja', sum(
CASE
WHEN ts.channel_code = 'ID_LINKAJA' THEN total_income
ELSE 0
END
),
'gopay', sum(
CASE
WHEN ts.channel_code = 'ID_GOPAY' THEN total_income
ELSE 0
END
),
'balance', sum(
CASE
WHEN ts.payment_type = 'balance' THEN total_income
ELSE 0
END
)
) as ewallet_detail
FROM inkanteen_db.transactions_summary ts
JOIN inkanteen_db.transaction_tenants ON ts.transaction_tenant_id = transaction_tenants.id
WHERE ts.tenant_id = ?
AND transaction_tenants.status = 'done'
AND date(ts.created_at) BETWEEN ? AND ?
and ts.type='sales'
`,
[tenantId, formatDate(startDate), formatDate(endDate)]
);
const result = new ResponseTransactionReport(
<ResponseTransactionReport>item[0]
);
// const refund = await connection.query(
// `SELECT SUM(ub.balance) as total_refund
// from user_balances ub
// join transaction_tenants tt
// on tt.id = ub.reference_id
// LEFT JOIN transaction_histories th ON tt.id = th.transaction_tenant_id
// where tt.tenant_id = ? and
// date(tt.created_at) between ? and ?
// AND th.status IN ('confirm', 'done')`,
// [tenantId, formatDate(startDate), formatDate(endDate)]
// );
result.refund = Number(0 || "0");
// Histories with view
// const histories = await connection.query(
// `
// SELECT date(t1.created_at) as date,
// sum(COALESCE(t1.total_income,0)) as income_total,
// sum(t1.total_order) as order_total,
// sum(t1.menu_total) as menu_total,
// (
// SELECT count(*) from inkanteen_db.transactions_summary
// JOIN inkanteen_db.transaction_tenants ON
// transactions_summary.transaction_tenant_id = transaction_tenants.id
// where transactions_summary.tenant_id = ? and transaction_tenants.status in('done') and
// date(transactions_summary.created_at)=date
// ) as transaction_total
// from inkanteen_db.transactions_summary t1
// JOIN inkanteen_db.transaction_tenants ON t1.transaction_tenant_id = transaction_tenants.id
// where t1.tenant_id = ? and
// transaction_tenants.status in('done') and
// date(t1.created_at) between ? and ?
// group by date
// order by date DESC
// `,
// [tenantId, tenantId, formatDate(startDate), formatDate(endDate)]
// );
const histories = await connection.query(
`
SELECT
date(t.created_at + interval 7 hour) as date,
(
SELECT
SUM(income_total) as total_income
FROM
(
SELECT
(
CASE
WHEN (tt1.type_charge_mdr = 'transaction') THEN CASE
WHEN (trx.borne_by = 'tenant') THEN ROUND(
SUM(
ttd.paid_price + COALESCE(ttd.markup_price, 0) - COALESCE(ttd.discount_per_item, 0) - COALESCE(ttd.markup_price, 0)
) - tt1.discount - tt1.total_mdr,
0
)
ELSE ROUND(
SUM(
ttd.paid_price + COALESCE(ttd.markup_price, 0) - COALESCE(ttd.discount_per_item, 0) - COALESCE(ttd.markup_price, 0)
) - tt1.total_mdr,
0
)
END
ELSE CASE
WHEN (trx.borne_by = 'tenant') THEN ROUND(
SUM(
ttd.paid_price + COALESCE(ttd.markup_price, 0) - COALESCE(ttd.discount_per_item, 0) - COALESCE(ttd.markup_price, 0)
) - tt1.discount,
0
)
ELSE ROUND(
SUM(
ttd.paid_price + COALESCE(ttd.markup_price, 0) - COALESCE(ttd.discount_per_item, 0) - COALESCE(ttd.markup_price, 0)
),
0
)
END
END
) - SUM(COALESCE(ttd.omnichannel_total,0)) AS income_total
FROM
transaction_details ttd
JOIN transaction_tenants tt1 ON tt1.id = ttd.transaction_tenant_id
JOIN transactions trx ON trx.id = tt1.transaction_id
WHERE
tt1.tenant_id = t1.tenant_id
AND tt1.status IN('done')
AND DATE(tt1.created_at + INTERVAL 7 HOUR) = date
and trx.type='sales'
GROUP BY
trx.id
) AS subquery
) as income_total,
COUNT(td.id) as order_total,
SUM(td.quantity) as menu_total,
(
SELECT
COUNT(DISTINCT tt.transaction_id)
FROM
transaction_tenants tt
join transactions trx1 on trx1.id = tt.transaction_id
WHERE
tt.tenant_id = t1.tenant_id
AND date(tt.created_at + interval 7 hour) = date
and tt.status in('done')
and trx1.type='sales'
) as transaction_total
FROM
transactions t
JOIN transaction_tenants t1 on t1.transaction_id = t.id
JOIN transaction_details td on td.transaction_tenant_id = t1.id
WHERE
t1.tenant_id = ?
and t1.status in('done')
and date(t1.created_at + interval 7 hour) between ? and ?
and t.type='sales'
GROUP BY
date
ORDER BY
date DESC;
`,
[tenantId, formatDate(startDate), formatDate(endDate)]
);
result.histories = histories;
return result;
}Editor is loading...
Leave a Comment