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