Untitled

avatar
unknown
plain_text
3 years ago
11 kB
2
Indexable
--Reconciliation
--1. Wallet funding
--a
select *
from freedom_proddb.dbo.transactions where tran_date between '2021-07-05 00:00:00.0000000' and '2021-07-06 00:00:00.0000000'
and transaction_type = 'Wallet Funding';

select (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.*
from freedom_wallet_transaction f
where remark like 'Fund Wallet%'
  and tran_date between  '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000'
and wallet_number <> '10000000001';

--2. Withdrawal
select *
from freedom_proddb.dbo.transactions where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000'
                                       and transaction_type = 'Withdrawal';

select  (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.*
from freedom_wallet_transaction f where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000'
and remark like 'Withdrawal%' and wallet_number <> '10000000001';

--3. Deposit
select *
from freedom_proddb.dbo.transactions where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000'
                                       and transaction_type = 'Deposit';

select  (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.*
from freedom_wallet_transaction f where tran_date between  '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000'
and remark like 'Deposit%' and wallet_number <> '10000000001';


--4. Recharge
select *
from freedom_proddb.dbo.transactions where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000'
                                       and transaction_type = 'Recharge';

select  (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.*
from freedom_wallet_transaction f where tran_date between  '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and wallet_number <> '10000000001'
and remark like '%Top-up%' Union
select  (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.*

from freedom_wallet_transaction f where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and wallet_number <> '10000000001'
and remark like '%E-Top Up%'

select  distinct (remark)
from freedom_wallet_transaction f where tranid = '10939667'
        and tran_date between '2021-07-02 00:00:00.0000000' and '2021-07-03 00:00:00.0000000'

--5. Bill payment
select *
from freedom_proddb.dbo.transactions where tran_date between  '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000'
                                       and transaction_type = 'Bill payment';

select  (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.*
from freedom_wallet_transaction f where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000'
and remark not like '%top up%' and remark not like '%top-up%'   and remark like 'Bill payment%' and wallet_number <> '10000000001';

--6. Transfer to Account
select *
from freedom_proddb.dbo.transactions where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000'
and description = 'Transfer to Settlement Account';

select  (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.*
from freedom_wallet_transaction f where tran_date between  '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000'
and remark like 'TRANSFER REQUEST FROM%' and wallet_number <> '10000000001';


select * from medusa.dbo.transaction_logs where institutionid = 'FREE444942'
and tran_date_time between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000'

--Transfer_to_Settlement_Account_12pm-512pm_24th_May_2021_Batch_9
select *, (select phone_number from agents where username =  f.agent_name) as phone_number from freedom_proddb.dbo.transactions f where tran_date
between '2021-05-24 12:00:00.0000000'  and '2021-05-24 17:12:00.0000000'and
description = 'Transfer to Settlement Account' order by tran_id desc


--Medusa responses for Freedom
Select count(*) as count, response_code,response_desc from medusa.dbo.transaction_logs
where tran_date_time between '2021-06-13 00:00:00.0000000' and  '2021-06-20 00:00:00.0000000'
and institutionid  = 'FREE444942'
group by response_code, response_desc
order by response_code asc

--Agents Performance summary
select * from (
select
concat(a.first_name,' ',a.last_name) as names, a.username, a.agent_type,
a.terminal_id, a.phone_number, a.email, a.datecreated, a.activated, a.state,
(select username from agents where id = a.parent_agent_id) as aggregator, (select username from agents where id = a.sub_parent_agent_id) as subaggregator,
d.dep_val,
d.dep_vol,
w.with_val,
w.with_vol,
x.bill_val,
x.bill_vol,
y.airtime_val,
y.airtime_vol
from agents a
left JOIN (
select agent_name,sum(amount) as dep_val ,count(0) as dep_vol from transactions
where transaction_type = 'Deposit'
and (tran_date BETWEEN '2021-05-28 00:00:00.0000000' and  '2021-07-05 00:00:00.0000000')
and status = 1
group by agent_name,transaction_type
) as d
ON a.username = d.agent_name
left JOIN (
select agent_name,sum(amount) as with_val,count(0) as with_vol from transactions
where transaction_type = 'Withdrawal'
and  (tran_date BETWEEN '2021-05-28 00:00:00.0000000' and  '2021-07-05 00:00:00.0000000')
and status = 1
group by agent_name,transaction_type
) as w
ON a.username = w.agent_name
left JOIN (
select agent_name,sum(amount) as bill_val,count(0) as bill_vol from transactions
where transaction_type = 'Bill Payment'
and  (tran_date BETWEEN '2021-05-28 00:00:00.0000000' and  '2021-07-05 00:00:00.0000000')
and status = 1
group by agent_name,transaction_type
) as x
ON a.username = x.agent_name
left JOIN (
select agent_name,sum(amount) as airtime_val,count(0) as airtime_vol from transactions
where transaction_type = 'Recharge'
and  (tran_date BETWEEN '2021-05-28 00:00:00.0000000' and  '2021-07-05 00:00:00.0000000')
and status = 1
group by agent_name,transaction_type
) as y
ON a.username = y.agent_name)b


--Sanef Agent Onboarding
select agent_id, username, bvn, account_no, bank_code, state, lga,
       wards, address, email, phone_number, datecreated,
       (select username from agents where id = a.parent_agent_id ) as aggregator,
       (select username from agents where id = a.sub_parent_agent_id ) as sub_aggregator
from agents a where datecreated between '2021-06-12' and '2021-06-26'


--Regent Performance
SELECT _3line_commission,
agent_commission,
agent_name,
bank_commission,
created_on,
parent_agent_commission,
transaction_amount,
tran_date,
CASE
 WHEN transaction_type =0 THEN 'SAVING'
 WHEN transaction_type =1 THEN 'WITHDRAWAL'
 WHEN transaction_type =2 THEN 'TRANSFER'
 WHEN transaction_type =3 THEN 'THRIFT'
 WHEN transaction_type =4 THEN 'BILL_PAYMENT'
else 'STAMP_DUTY_WITHDRAWAL'
END as tran_type,
transaction_channel
FROM freedom_proddb.dbo.gravity_daily_commission
WHERE created_on between '2021-06-01'
   AND '2021-07-01'
and agent_name in (select username from freedom_proddb.dbo.agents where parent_agent_id = 21746);

-- (select id from agents where agent_type = 'SUBAGGREGATOR');


select wallet_number, income_wallet_number from agents where username = 'Ahmed.Haruna036'
select * from freedom_wallet_transaction where wallet_number = '53147664427'
and created_on between '2021-06-01'
   AND '2021-06-21'

--PTSP Transactions
select * from ptsp_model where rrn = '162550156549';
select * from transactions where itex_tran_id = '162550156549';
select top(2) * from freedom_wallet_transaction where tranid =
(select tran_id from transactions where itex_tran_id = '13032560')

select
s.created_on,
s.amount,
s.balance_after,
s.balance_before,
s.channel,
s.remark,
s.tran_date,
s.tranid,
s.tran_type
from freedom_wallet_transaction s
where wallet_number = (select wallet_number from agents where username = 'CHIOMA.Onwuchekwa')
and created_on between '2021-05-28 00:00:00' and '2021-05-29 00:00:00' ORDER BY created_on desc

--Activated but inactive agents
select
a.agent_id, a.username, a.bvn, a.account_no, a.bank_code, a.state, a.lga,
       a.wards, a.address, a.email, a.phone_number, a.datecreated,
       (select username from agents where id = a.parent_agent_id ) as aggregator,
       (select username from agents where id = a.sub_parent_agent_id ) as sub_aggregator
from agents a where wallet_number in
(select wallet_number from freedom_wallet where last_tran_date < '2021-05-01')
and activated = 1

select sum(amount) as transaction_amount, count(*) as count, a.state from transactions t , agents a where t.agent_name = a.username and t.tran_date like '2021-05%'
group by a.state

--Active Terminal Daily
select count(*) a from (select distinct terminal_id from transactions where
tran_date between '2021-07-03' and '2021-07-04' and transaction_type = 'WITHDRAWAL'
and terminal_id <> '') a



select sum(cast(agent_commission as float)) as ag_1 , sum(cast(_3line_commission as float)) as ag_2
from gravity_daily_commission where tran_date BETWEEN '2021-06-24' AND '2021-06-25'

select wallet_number from agents where username like 'oyetunji.olorunwa%'
select  * from archived_transaction where agent_name = 'oyetunji.olorunwa' order by  tran_date desc
select * from freedom_wallet_transaction where
wallet_number = '10165944270'




--Generate Statement for 2020
select
s.created_on,
s.amount,
s.balance_after,
s.balance_before,
s.channel,
s.remark,
s.tran_date,
s.tranid,
s.tran_type
from dbo.freedom_wallet_transaction s
where wallet_number = '37990475017'
and created_on between '2020-01-01 00:00:00' and '2020-08-01 00:00:00'

union

select
s.created_on,
s.amount,
s.balance_after,
s.balance_before,
s.channel,
s.remark,
s.tran_date,
s.tranid,
s.tran_type
from dbo.freedom_wallet_transaction_aug_sept s
where wallet_number = '37990475017'
and created_on between '2020-08-01 00:00:00' and '2020-09-01 00:00:00'

union

select
s.created_on,
s.amount,
s.balance_after,
s.balance_before,
s.channel,
s.remark,
s.tran_date,
s.tranid,
s.tran_type
from dbo.freedom_wallet_transaction_aug_sept_2 s
where wallet_number = '37990475017'
and created_on between '2020-09-01 00:00:00' and '2020-10-01 00:00:00'

union

select
s.created_on,
s.amount,
s.balance_after,
s.balance_before,
s.channel,
s.remark,
s.tran_date,
s.tranid,
s.tran_type
from dbo.freedom_wallet_transaction_bk22022020 s
where wallet_number = '92887235063'
and created_on between '2020-12-01 00:00:00' and '2021-01-01 00:00:00' order by created_on desc;

select wallet_number from freedom_proddb.dbo.agents where username = 'SOTONYE.CHARLES';