Untitled

 avatar
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