SELECT a.product,
       COUNT(*) AS leads,
       b.sales,
       (b.sales/COUNT(*))*100 AS conversion
FROM bldslead_api.`italk_history` a
         JOIN (
    SELECT
        product,
        COUNT(DISTINCT idno) AS sales
    FROM bldslead_vas.`customers`
    WHERE date_of_sale = '2021-10-29'
    GROUP BY product
) b ON a.`product` = b.`product`
WHERE DATE(a.insertdt) = '2021-10-29'
  AND a.product IN ('cellular', 'loans', 'cards', 'blc')
  AND a.status = 'success'
GROUP BY a.product
;

SELECT *
FROM bldslead_api.`xds_icheck`
WHERE idno = '9809220978089'
  AND insertdt > DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY)
;


select * from bldslead_uccs_disposition.uccs_disposition where cellno = '0823850777'
;
select * from bldslead_vas.mandates where debtor_cellno = '0606196168'
;

select * from bldslead_api.italk_history WHERE product = 'realpeople' order by insertdt desc
;
select LocationID, count(*)
from bldslead_mercantile.mandates a
join bldslead_uccs_disposition.uccs_disposition b on a.debtor_cellno = b.cellno
where b.lead_disposition = 'Sale Failed QA Client Was Misled Agent'
and a.first_collection_date = '2021-08-25'
and a.bsva_description = 'Request transaction identifier must be unique'
group by LocationID
;

select *
from bldslead_uccs_disposition.uccs_disposition a
         left join (select * from bldslead_uccs_disposition.uccs_disposition where CampaignID = 'UFG Hot CellC Data' and lead_disposition = 'Sale Passed QA' and date(call_time) = '2021-07-20') b on a.cellno = b.cellno
where a.lead_disposition = 'Sale Failed QA Client Was Misled Agent'
and date(a.call_time) = '2021-07-20'
  and a.CampaignID = 'UFG Hot CellC Data'
and not isnull(b.cellno)
;

select * from bldslead_vas.customers where cellno = '0767857653'
;
select * from bldslead_api.uccs_customers where cellno = '0767857653'
;
select * from bldslead_campaigns.incoming_history where cellno = '0636353491';
select count(*) from bldslead_omutual.sms_outgoing_history where campaignid = 354
;

select *
from uccs_customers a
join uccs_policies b on a.penPolicy_Number = b.polPolicy_Number
where cellno = '0767629020'
;

update avm_incoming_history set qid = '' where isnull(result_json) and qid = '6152dd7786d052.98350717'
;

select * from avm_incoming_history where qid = '6152dd7786d052.98350717' and isnull(result_json)
;

create table italk_repost_20210926
SELECT trim('"' from JSON_EXTRACT(request_json, '$.leadid')) AS 'leadid', trim('"' from JSON_EXTRACT(result_json, '$.description')) AS 'reference', product, status from bldslead_api.italk_history where date(insertdt) = '2021-09-26';

select id, cellno, insertdt from bldslead_api.italk_history where status = 'error' and date(insertdt) = '2021-09-26' order by insertdt desc
;
select * from bldslead_api.italk_history where id = 2910195;
select * from bldslead_api.italk_history where cellno = '0838827520';

select * from bldslead_vas.mandates where debtor_cellno = '0785192456'
;
select * from bldslead_mercantile.mandates where debtor_cellno = '0767629020'
;

UPDATE bldslead_api.african_bank_history SET global_status = 'INPROGRESS' WHERE global_status = 'UNKNOWN' AND product = 'loans_nosms'
;

UPDATE bldslead_api.african_bank_history SET global_status = 'UPLOADED' WHERE global_status = 'INPROGRESS' AND product = 'loans_nosms'
;

SELECT vetting_consent, marketing_consent, idno, '' as title, name, surname, cellno, '' as email, if(product = 'loans_nosms', gross_income, 0) as gross_income, 'Loan' as product, nett_income, xds_category  FROM bldslead_api.african_bank_history WHERE global_status = 'UNKNOWN' AND product = 'loans_sms';
SELECT vetting_consent, marketing_consent, idno, '' as title, name, surname, cellno, '' as email, if(product = 'loans_nosms', gross_income, 0) as gross_income, 'Loan' as product, nett_income, xds_category  FROM bldslead_api.african_bank_history WHERE global_status = 'UNKNOWN' AND product = 'loans_sms';
select *
from bldslead_campaigns.incoming_history
where campaignid = 7
and batchid = 0
order by date_time desc
;
select * from bldslead_campaigns.send_batches where cellno = '0739495663'
;
select * from bldslead_campaigns.incoming_history where cellno = '0769776023'
;
SELECT * FROM bldslead_intecon.clients where client_number = '5107060085089'
;

select * from bldslead_api.all_data_summary where cellno = '0824425297'
;

SELECT cellno, last_dt, homeno, workno, guid FROM bldslead_api.all_data_summary WHERE idno = '7210145844187' AND optout = 0
;

select * from bldslead_api.cma_history where result like '%5297%'
;


update bldslead_intecon.clients a join bldslead_intecon.clients_import b on a.client_number = b.client_number and a.branch = b.branch
set a.global_status = 'ACCEPTED',
    a.updated = current_timestamp
where a.global_status = 'UPLOADED'
  and b.reply_cd = '207'
;

select * from bldslead_intecon.bank_results a
where FILENAME = 'IRP_batch1@bluelabelconnect_20210319_073113_processed.csv'
ORDER BY RESPONSE_DT ASC
;

update bldslead_intecon.clients a join bldslead_intecon.clients_import b on a.client_number = b.client_number and a.branch = b.branch
set a.global_status = 'ACCEPTED',
    a.updated = current_timestamp
where a.global_status = 'UPLOADED'
  and b.reply_cd = '3015'
;


select month(insertdt), count(*)
from realpeople_sales
group by month(insertdt)
;

select distinct type from bldslead_api.uccs_debit_order_history
;
-- collection rates
-- report on values
SELECT `group`,
       COUNT(*) AS volume,
       SUM(amount)-SUM(IF(acb_status = 'TRACKING', amount, 0)) AS totals,
       SUM(IF(acb_status = 'PAID', amount, 0))AS paid,
       SUM(IF(acb_status = 'DISPUTE', amount, 0))AS dispute,
       SUM(IF(acb_status = 'UNPAID', amount, 0))AS unpaid,
       SUM(IF(acb_status = 'TRACKING', amount, 0))AS tracking
-- (SUM(IF(acb_status = 'PAID', amount, 0))/SUM(amount))*100 as paid_perc,
-- (SUM(IF(acb_status = 'UNPAID', amount, 0))/SUM(amount))*100 AS unpaid_perc,
-- (SUM(IF(acb_status = 'DISPUTE', amount, 0))/SUM(amount))*100 AS dispute_perc,
-- (SUM(IF(acb_status = 'DISPUTE', amount, 0))/((SUM(IF(acb_status = 'PAID', amount, 0))+SUM(IF(acb_status = 'DISPUTE', amount, 0)))))*100 as dispute_of_paid
FROM `bldslead_api`.`uccs_debit_order_history`
WHERE action_date BETWEEN '2021-09-15' AND '2021-09-15'
-- and `type` = 'ACOL'
-- and user_code in ('OWTG')
-- and routing = 'MERCANTILE'
-- and `group` in ('NEW')
-- and polid in(select polid from `bldslead_api`.`uccs_debit_order_history` WHERE action_date BETWEEN '2021-06-01' AND '2021-06-30' and acb_status = 'UNPAID') -- unpaid month
-- and polid in(select polid from `bldslead_api`.`uccs_debit_order_history` WHERE action_date BETWEEN '2021-07-01' AND '2021-07-31' and `group` = 'NEW' and routing = 'MERCANTILE') -- mercantile NEW for mont
-- AND `polid` IN (SELECT contract_reference FROM bldslead_vas.mandates) -- check if debicheck
GROUP BY `group`
-- with rollup
;

SELECT action_date,
       monthname(action_date) as month,
       year(action_date) as year,
       COUNT(*) AS volume,
       SUM(amount)-SUM(IF(acb_status = 'TRACKING', amount, 0)) AS totals,
       SUM(IF(acb_status = 'PAID', amount, 0))AS paid,
       SUM(IF(acb_status = 'DISPUTE', amount, 0))AS dispute,
       SUM(IF(acb_status = 'UNPAID', amount, 0))AS unpaid,
       SUM(IF(acb_status = 'TRACKING', amount, 0))AS tracking,
       (SUM(IF(acb_status = 'PAID', amount, 0))/SUM(amount))*100 as paid_perc,
       (SUM(IF(acb_status = 'UNPAID', amount, 0))/SUM(amount))*100 AS unpaid_perc,
       (SUM(IF(acb_status = 'DISPUTE', amount, 0))/SUM(amount))*100 AS dispute_perc,
       (SUM(IF(acb_status = 'DISPUTE', amount, 0))/((SUM(IF(acb_status = 'PAID', amount, 0))+SUM(IF(acb_status = 'DISPUTE', amount, 0)))))*100 as dispute_of_paid
FROM `uccs_debit_order_history`
where action_date > '2021-10-01'
group by action_date
;
select * from uccs_debit_order_history where polid in(
    select polid from bldslead_api.uccs_debit_order_history
    where acb_status = 'dispute'
      and action_date BETWEEN '2021-09-01' AND '2021-09-30'
      and polid in (
        select polid
        from bldslead_api.uccs_debit_order_history
        where action_date BETWEEN '2021-06-01' AND '2021-06-30'
          and routing = 'mercantile'
          and `group` = 'new'
    )
      and routing = 'mercantile'
)
order by polid, action_date
;
select *
from bldslead_api.uccs_customers a
join bldslead_api.uccs_policies b on a.penPolicy_Number = b.polPolicy_Number
left outer join (select * from bldslead_vas.policies) vp on b.polID = vp.polid
where a.acc_no in (select acc_no from bldslead_vas.customers)
-- and b.polID not in (select polid from bldslead_vas.policies)
and a.date_of_sale between '2021-09-27' and '2021-10-04'
;
select * from bldslead_vas.policies where polid = 7310472
;

select * from bldslead_vas.customers where cellno = '0618476213'
;

select * from bldslead_mercantile.files where service_identification = 'NADREQ' and date(insertdt) = current_date;
select * from bldslead_mercantile.batches a join bldslead_mercantile.files b on a.upload_fileid = b.id where batchid = 69;
select * from bldslead_mercantile.transactions where transaction_action_date = '2021-09-23' and acb_status = 'tracking';
select * from bldslead_mercantile.transactions where transaction_acc_no = '01130080542';
select * from bldslead_mercantile.transactions where transaction_reference = '7174057';
select * from bldslead_mercantile.transactions where transaction_cellno = '0680695701';
select * from bldslead_mercantile.acresp where original_contract_reference = '7174057';
select * from bldslead_mercantile.bank_results where contract_reference = '7180607';
select * from bldslead_mercantile.bank_results where filename = 'OWTG_20211001_211001172800.TXT';
select * from bldslead_mercantile.bank_results order by insertdt desc;
select * from bldslead_mercantile.mandates where contract_reference = '7174057';

-- get paid duplicates
select * from bldslead_mercantile.bank_results where contract_reference in(
select contract_reference
from bldslead_mercantile.bank_results a
where a.action_date = '210923'
and user_inst in ('OWTE','OWTF','OWTG')
-- and acb_status = 'PAID'
group by contract_reference
having count(*) > 1) and action_date = '210923'
and user_inst in ('OWTE','OWTF','OWTG') ;

select * from bldslead_mercantile.transactions where transactionid = 22461
;
select max(file_sequence_number) from bldslead_mercantile.files where user_installation_code = 'OWTG' and service_identification = 'NADREQ';

SELECT * FROM bldslead_mercantile.files WHERE user_installation_code = 'OWTE' and file_sequence_number = 7 order by insertdt desc
;

SELECT * FROM bldslead_mercantile.batches WHERE upload_fileid = 97800
;
-- set file sequence numbers = 0
-- set batches = failed

-- confirmation of debit order
insert ignore into bldslead_mercantile.sms_confirmations
(cellno, message, contract_reference, user_code, first_collection_date)
select b.debtor_cellno,
    concat('Your ', c.abbreviated_name, ' Debit Order has been registered on your account for an amount of R', b.collection_amount, ' due on ', b.first_collection_date, '. For any queries please contact us on 0873303501.') as message,
    a.original_contract_reference,
    a.user_inst,
    b.first_collection_date
from bldslead_mercantile.acresp a
join bldslead_mercantile.mandates b on a.original_contract_reference = b.contract_reference
join bldslead_mercantile.users c on b.user_code = c.user_clientid
where pain12_response_code = 'TRUE'
and b.first_collection_date > current_date
;
insert ignore into bldslead_mercantile.sms_confirmations
(cellno, message, contract_reference, user_code, first_collection_date)
values ()
;

select filename, count(*)
from bldslead_mercantile.acresp
group by filename
;

UPDATE bldslead_mercantile.sms_confirmations SET global_status = 'INPROGRESS' WHERE global_status = 'NEW' AND id = 1
;

LOAD DATA LOCAL INFILE '../uploads/sms/MERCANTILE_SMS_210926153835.csv'
    INTO TABLE bldslead_sms.sms_outgoing_history
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (remoteid, msisdn, message)
    SET batchid = 31,
        tenant = 'mercantile',
        filename = 'MERCANTILE_SMS_210926153835.csv';

select month(date_time), count(distinct cellno), sum(if(accepted = 1, 1, 0))
from mobi_clicks
where date_time between '2021-01-01' and '2021-12-31'
group by month(date_time)
;

select count(*) from bldslead_api.uccs_policies where date_of_sale between '2021-08-19' and '2021-08-31'
;
-- session counts
select * -- date(insertdt) as dt, count(*) as sessions, count(distinct cellno) as unique_sessions
from ussd_incoming_history
where product = 'TPC'
  and query_string like '%request=*120*%'
and insertdt between '2021-04-01 00:00:00' and '2021-04-30 23:59:59'
;

select * from bldslead_omutual.sms_outgoing_history where cellno = '0813155554'
;

update ussd_incoming_history
set global_status = 'QUEUE'
where terms_accepted = 1 and data_sharing_agreed = 0
  and product = 'TPC'
  and global_status = 'UNKNOWN'
;
select * from ussd_incoming_history
where insertdt > '2021-07-29'
order by insertdt desc
;

update forms_incoming_history
set qid = '',
    global_status = 'UNKNOWN'
where insertdt > '2021-07-29'
and global_status = 'error'
and result_json like '{"status": "error", "description": "Connection timeout %'
order by insertdt desc
;

select * from ussd_incoming_history order by insertdt desc;

select * from ussd_incoming_history where campaignid = 18 order by insertdt desc ; -- and global_status = 'success';

select * from thrive_history order by insertdt desc
;
select * from ompf_authorized where expiry_date >= current_date and cellno = '0787161540' order by dedupe_date desc
;
update bldslead_mercantile.mandates set global_status = 'UNKNOWN' where id = 32740
;
select * from bldslead_vas.customers where cellno = '0685888649'
;
SELECT *
FROM `bldslead_mercantile`.`mandates`
WHERE `first_collection_date` = '2021-08-25'
  AND global_status = 'ERROR'
  AND pain12_code = 'NRSP'
  AND bsva_code = '902152'
;

SELECT *
FROM `bldslead_mercantile`.`mandates`
WHERE `first_collection_date` = '2021-08-25'
  AND global_status = 'In Progress'
  AND bsva_code IN ('ACCP', '')
  AND bank_code IN ('900000', '')
  AND pain12_code = 'NRSP'
  and insertdt < date_sub(current_date, INTERVAL 4 DAY)
;

select * -- product, idno, name, surname, email, insertdt
from forms_incoming_history a
where email RLIKE '^[-0-9a-z_\\.]+@[-0-9a-z_\\.]+\\.[a-z]{2,3}$'
  and insertdt between '2021-08-01 00:00:00' and '2021-08-31 23:59:59'
and product = 'loan'
order by insertdt asc
-- and idno RLIKE '^([0-9]){2}([0-1][0-9])([0-3][0-9])([0-9]){4}([0-1])([0-9]){2}$'
;
select * from blc_scoring_history where date(insertdt) > '2021-02-01' and campaignid = 149 and result = 'PASS'
;

select date(insertdt), count(*), count(distinct idno) from bldslead_capital.capital_avsr group by date(insertdt)
;

select idno, count(*) from bldslead_capital.capital_avsr group by idno having count(*) > 2
;

select * from bldslead_api.xds_icheck where username = 'hellocredit'
;

select data_batch_source as source,
       count(*) as calls,
       count(distinct cellno) as leads,
       sum(if(lead_disposition = 'Sale Passed QA',1,0)) as transfer,
       sum(if(call_disposition = 'Voicemail',1,0)) as voicemail,
       sum(if(call_disposition = 'No Answer',1,0)) as no_answer,
       count(distinct UserID) as agents
from bldslead_uccs_disposition.uccs_disposition_202107
where CampaignID = 'MiWay Lead Warming'
-- and call_time between '2021-05-01' and '2021-05-31'
and lead_disposition != 'Application in Progress'
-- and idno = ''
group by data_batch_source
;

select date(call_time), count(*)
from bldslead_uccs_disposition.uccs_disposition_202108
where data_batch_source = 'Bl_miway_short_term_20210813_batch24_white_italk_noid'
group by date(call_time)
;

select distinct campaignid from bldslead_uccs_disposition.uccs_disposition where call_time between '2021-07-01' and '2021-07-31'
;
/* FORMS SQL */
select * from forms_incoming_history where campaignid = 588 order by insertdt desc
;
select * from forms_incoming_history where cellno = '0785907476'
;
select * from forms_incoming_history where product = 'TPC'
;
select * from forms_incoming_history where `name` like 'sebenzaTest2'
;
select * from forms_incoming_history where product = 'zasports' and referalid not in ('None', 'Not Set', '') order by insertdt desc
;

select * from bldslead_api.realpeople_sales where sale_xml like '%ITALK_339941%'
;

/* REAL PEOPLE SQL */
select date(insertdt), count(*)
from bldslead_api.realpeople_sales
where month(insertdt) = 8
group by date(insertdt)
;
select * from realpeople_history where cellno = '0798641288' order by insertdt desc
;
select * from velociti_history where cellno = '0798641288' order by insertdt desc
;
select * from italk_history where cellno = '0720897791' order by insertdt desc
;

select idno, insertdt from bldslead_api.cma_history order by insertdt desc
;

select * from bldslead_mercantile.mandates where contract_reference = '7164209'
;
select b.batchid, b.global_status, b.user_code, b.insertdt, count(*)
from bldslead_mercantile.transactions a
join bldslead_mercantile.batches b on a.transaction_batchid = b.batchid
where transaction_action_date = '2021-09-23'
group by b.batchid
;

select * from bldslead_mercantile.transactions
;
show full processlist
;
select month(insertdt), count(*)
from bldslead_api.realpeople_sales
group by month(insertdt)
;
/* INTECON SQL */
select * from bldslead_intecon.mandates order by insertdt desc
;
select * from bldslead_intecon.mandates where PROM_ID = '0004C5E66D'
;
select * from bldslead_intecon.mandates  where CLIENT_NUMBER = '9802161291083'
;
select * from bldslead_intecon.mandates where REFERENCE = '7107385'
;
select * from bldslead_intecon.mandates where REFERENCE = '7127179'
;
select * from bldslead_intecon.clients where idno = '9411290972082'
;
select * from bldslead_intecon.transactions where transaction_acc_no = '01733582205'
;
select * from bldslead_intecon.transactions where transaction_reference = '7127179'
;
select * from bldslead_intecon.bank_results where PROMISSORY_ID = '0004F4CE8E' order by RESPONSE_DT desc
;
select distinct RESPONSE_TYPE from bldslead_intecon.bank_results
;
select RESPONSE_STR, count(*) from bldslead_intecon.bank_results where RESPONSE_TYPE = '557' group by RESPONSE_STR
;
select * from bldslead_intecon.mandates where global_status = 'unknown' order by insertdt desc LIMIT 500
;
select * from bldslead_intecon.mandates where global_status = 'error' and REPLY_CD in('999','6913','6912') order by insertdt desc
;
select REPLY_STR, count(*)
from bldslead_intecon.mandates
where global_status = 'ERROR'
and insertdt > date_sub(current_timestamp, interval 30 day )
group by REPLY_STR
;
select * from bldslead_intecon.mandates order by insertdt desc
;
select * from bldslead_intecon.mandates where FIRST_DATE = '20210725' and global_status = 'ERROR' order by insertdt desc
;
select *
from bldslead_intecon.bank_results a
join bldslead_intecon.transactions c on a.REFERENCE = c.transaction_reference
where a.RESPONSE_TYPE = '548'
;

select * from journey_history where cellno = '0633732625'
;

select *
from bldslead_intecon.bank_results a
    left join bldslead_intecon.presentment_response_messages b on a.RESPONSE_CD = b.reply_code
where FILENAME = 'IRP_batch1@bluelabelconnect_20210428_200836_processed.csv'
ORDER BY RESPONSE_DT ASC
;

-- get ACB Code
select trim(')' from substr(a.RESPONSE_STR,instr(a.RESPONSE_STR,"(") + 1, instr(a.RESPONSE_STR,")")))
from bldslead_intecon.bank_results a
where a.RESPONSE_TYPE = '548'
;

select *
from bldslead_intecon.batches
where action_date = '2020-09-15'
  and global_status = 'COMPLETED'
  and success_count > 0
;
-- response types
select *
from bldslead_intecon.bank_results
where FILENAME = 'IRP_batch1@bluelabelconnect_20210331_124003_processed.csv'
  AND RESPONSE_TYPE in ('555','546','547','548','549')
;

select * from bldslead_intecon.bank_results a
                  left join bldslead_intecon.presentment_response_messages b on a.RESPONSE_CD = b.reply_code
where FILENAME = 'IRP_batch1@bluelabelconnect_20210331_123151_processed.csv'
  AND RESPONSE_TYPE in ('546','547','548','549')
ORDER BY RESPONSE_DT ASC
;
select *
from bldslead_intecon.transactions a
         join bldslead_intecon.batches b on a.transaction_batchid = b.batchid
where b.debit_type = 'ACOL'
  and a.acb_response_file = 'IRP_batch1@bluelabelconnect_20210331_062517_processed.csv'
order by transaction_insertdrt desc
;
select * from bldslead_intecon.transactions where acb_response_file = 'IRP_batch1@bluelabelconnect_20210331_062517_processed.csv'
;
select *
from bldslead_intecon.bank_results a
         left join bldslead_intecon.transactions b on a.REFERENCE = b.transaction_reference
where FILENAME = 'IRP_batch1@bluelabelconnect_20210331_062517_processed.csv'
  AND RESPONSE_TYPE in ('546','547','548','549', '508', '509', '510', '511')
;

select * from bldslead_intecon.transactions where transaction_reference = '7089674'
;

select * from bldslead_intecon.mandates where REFERENCE = '7089674'
;

select * from bldslead_intecon.transactions where transaction_acc_no = '01589547118'
;

select *
from bldslead_intecon.transactions a
         join bldslead_intecon.batches b on a.transaction_batchid = b.batchid
where b.debit_type = 'ACOL'
order by transaction_insertdrt desc
;

select * from bldslead_intecon.bank_results a
                  left join bldslead_intecon.presentment_response_messages b on a.RESPONSE_CD = b.reply_code
where FILENAME = 'IRP_batch1@bluelabelconnect_20210510_135823_processed.csv'
  AND RESPONSE_TYPE in ('548','547')
ORDER BY RESPONSE_DT ASC
;

SELECT COUNT(*) FROM bldslead_intecon.bank_results where FILENAME = 'IRP_batch1@bluelabelconnect_20210319_073113_processed.csv';

update bldslead_mercantile.mandates
set global_status = 'UNKNOWN', debtor_authentication_code = '0227'
where id = 592
;
select * from bldslead_vas.mandates where contract_reference = '7164209'
;
/* MERCANTILE SQL */
select * from bldslead_mercantile.mandates where contract_reference = '7200047'
;
select * from bldslead_mercantile.mandates order by insertdt desc
;
select * from bldslead_mercantile.mandates where global_status = 'IN PROGRESS' order by insertdt desc
;
select * from bldslead_mercantile.mandates where debtor_authentication_code = '0227'
;
select * from bldslead_mercantile.mandates where debtor_cellno = '0745016184'
;
-- mandate errors
select *
from bldslead_mercantile.mandates
where global_status = 'ERROR'
and first_collection_date = '2021-08-25'
order by insertdt desc
;
select *
from bldslead_mercantile.mandates
where mandate_initiation_date = '2021-07-21'
and global_status = 'SUCCESS'
order by insertdt desc
;
select acb_status, count(*) from bldslead_mercantile.transactions where transaction_action_date = '2021-06-25' group by acb_status order by transaction_insertdrt desc
;
select * from bldslead_mercantile.bank_results order by insertdt desc
;
select * from bldslead_mercantile.mandates order by insertdt desc
;
select * from bldslead_mercantile.mandates where bsva_code = '902152' and filename != ''
;
select * from bldslead_mercantile.mandates where contract_reference = '7158525'
;
select * from bldslead_mercantile.acresp where original_contract_reference = '7211540'
;
update bldslead_mercantile.mandates a
set a.global_status = 'ERROR'
WHERE pain12_description = 'Other unspecified error (pain12_response_code)'
  and a.global_status = 'IN PROGRESS'
;

select date(insertdt), count(*)
from bldslead_mercantile.mandates
where global_status = 'IN PROGRESS'
group by date(insertdt)
;

select * from bldslead_mercantile.mandates where global_status = 'IN PROGRESS' and date(insertdt) = '2021-07-30' and user_code = 'OWTG';
select * from bldslead_mercantile.acresp where user_inst = 'OWTG' and date(insertdt) = '2021-07-30';
select *
from bldslead_mercantile.acresp b
join bldslead_mercantile.mandates a on a.contract_reference = b.original_contract_reference
where b.filename = 'OWTG0643.acresp'
;
-- batch mandates
SELECT * FROM bldslead_mercantile.mandates
WHERE global_status = 'UNKNOWN'
  and debtor_authentication_code = '0227'
  AND user_code = 'OWTC'
;
select * from bldslead_mercantile.batches_prepare
;
select *
from bldslead_mercantile.transactions a
join bldslead_mercantile.bank_results b on a.transaction_reference = b.contract_reference
where b.filename = 'OWTD_20210528_210528101717.TXT'
;
select *
from bldslead_mercantile.mandates
where pain12_code = 'TRUE'
;

-- update outcomes from acresp

-- Authenticated debits, final outcome
update bldslead_mercantile.mandates a
set a.global_status = 'SUCCESS'
WHERE pain12_code = 'TRUE'
and a.global_status = 'IN PROGRESS'
;

-- Rejected debits, final outcome
update bldslead_mercantile.mandates a
set a.global_status = 'ERROR'
WHERE pain12_code = 'FALSE'
  and a.global_status = 'IN PROGRESS'
;

-- RMS accepted, final outcome
update bldslead_mercantile.mandates a
    join (select * from bldslead_mercantile.acresp where original_transaction_type like '021%' and pain12_response_code = 'TRUE' order by insertdt asc) b on a.contract_reference = b.original_contract_reference
set a.global_status = 'success',
    a.filename = b.filename,
    last_updated = current_timestamp
where a.global_status = 'IN PROGRESS'
;

-- RMS failed, final outcome
update bldslead_mercantile.mandates a
    join (select * from bldslead_mercantile.acresp where original_transaction_type like '021%' and pain12_response_code = 'FALSE' order by insertdt asc) b on a.contract_reference = b.original_contract_reference
set a.global_status = 'error',
    a.pain12_description = 'RMS Failed - pain12 code - FALSE',
    a.filename = b.filename,
    last_updated = current_timestamp
where a.global_status = 'IN PROGRESS'
and b.filename = ''
;
-- when a mandate is suspended, suspend no matter what status
update bldslead_mercantile.mandates a
    join (select * from bldslead_mercantile.acresp where original_transaction_type like '021%' and pain12_response_code = 'SUSP ' order by insertdt asc) b on a.contract_reference = b.original_contract_reference
set a.global_status = 'error',
    a.pain12_description = 'Mandate Suspended - pain code - SUSP',
    a.filename = b.filename,
    last_updated = current_timestamp
;

-- bsva errors
select *
from bldslead_mercantile.mandates a
join bldslead_mercantile.acresp a2 on a.contract_reference = a2.original_contract_reference
join bldslead_mercantile.debicheck_response_codes a3 on a3.response_code = a2.bsva_response_code
where a.global_status != 'IN PROGRESS'
and a2.original_transaction_type = '021/MANIN'
  and a2.bsva_response_code not in ('ACCP', '900000')
  and a2.filename = 'OWTG0064.acresp'
;

select * from bldslead_mercantile.mandates where global_status = 'ERROR' order by insertdt desc
;
/* VElociti */
select * from velociti_history where cellno = '072696559'
;
select * from velociti_history where product = 'realpeople' order by insertdt desc;

/* itlak */
select * from italk_history where product = 'realpeople' order by insertdt desc
;

/* metro */
select b.batchid, b.description, count(*)
from bldslead_campaigns.incoming_history a
join bldslead_campaigns.batches b on a.batchid = b.batchid
where b.campaignid = 46
  and date(a.date_time) = current_date
group by b.batchid
;


select count(*)
from bldslead_campaigns.sms_outgoing_history
where batchid in (10456,10457)
;

SELECT msisdn, message FROM bldslead_sms.sms_outgoing_history WHERE batchid = 2
;

select count(*)
from bldslead_campaigns.incoming_history
where batchid in (10456,10457)
;
INSERT IGNORE INTO `uccs_debit_order_history`
(`polid`,`productid`, `user_code`,`action_date`,`amount`,`type`, `acb_status`,
 `acb_reason_code`, `acb_reason`, `acb_status_date`, `routing`, `cellno`, `account_type`, `idno`, `group`,
 `branch_code`, `uploaded_filename`,`acc_no`, `account_holder`,`surname`, `tracking_days`)
SELECT `transaction_reference`,
       CASE
           WHEN `user_code` = 'OWTC' THEN 'OWTC'
           WHEN `user_code` = 'OWTD' THEN 'OWTD'
           WHEN `user_code` = 'OWTE' THEN 'OWTE'
           WHEN `user_code` = 'OWTF' THEN 'OWTF'
           WHEN `user_code` = 'OWTG' THEN 'OWTG'
           ELSE ''
           END AS productid,
       `user_code`,
       `transaction_action_date`,
       CAST(SUBSTRING(`transaction_amount` FROM 1 FOR CHAR_LENGTH(`transaction_amount`) - 2) AS DECIMAL(10,2)) AS amount,
       'NAEDO',
       `acb_status`,
       `acb_reason_code`,
       `acb_reason`,
       `acb_status_date`,
       'MERCANTILE',
       `transaction_cellno`, `transaction_account_type`,`transaction_account_holder_idno`,
       CASE
           WHEN `filename` LIKE '%PAID%' THEN 'PAID'
           WHEN `filename` LIKE '%15NPF_PRIOR_2X%' THEN '15NPF_PRIOR_2X'
           WHEN `filename` LIKE '%15NPF_PRIOR_3X%' THEN '15NPF_PRIOR_3X'
           WHEN `filename` LIKE '%15NPF_PRIOR_MONTH%' THEN '15NPF_PRIOR_MONTH'
           WHEN `filename` LIKE '%15_NPF_PRIOR_MONTH%' THEN '15NPF_PRIOR_MONTH'
           WHEN `filename` LIKE '%15REDEBIT_MOVED%' THEN '15REDEBIT_MOVED'
           WHEN `filename` LIKE '%15REDEBIT%' THEN '15REDEBIT'
           WHEN `filename` LIKE '%25NPF_PRIOR_2X%' THEN '25NPF_PRIOR_2X'
           WHEN `filename` LIKE '%25NPF_PRIOR_3X%' THEN '25NPF_PRIOR_3X'
           WHEN `filename` LIKE '%25NPF_PRIOR_MONTH%' THEN '25NPF_PRIOR_MONTH'
           WHEN `filename` LIKE '%25REDEBIT_MOVED%' THEN '25REDEBIT_MOVED'
           WHEN `filename` LIKE '%25REDEBIT%' THEN '25REDEBIT'
           WHEN `filename` LIKE '%31NPF_PRIOR_2X%' THEN '31NPF_PRIOR_2X'
           WHEN `filename` LIKE '%31NPF_PRIOR_3X%' THEN '31NPF_PRIOR_3X'
           WHEN `filename` LIKE '%31NPF_PRIOR_MONTH%' THEN '31NPF_PRIOR_MONTH'
           WHEN `filename` LIKE '%31REDEBIT%' THEN '31REDEBIT'
           WHEN `filename` LIKE '%DISPUTE%' THEN 'DISPUTE'
           WHEN `filename` LIKE '%HOLD_OUT%' THEN 'HOLD_OUT'
           WHEN `filename` LIKE '%HOLDOUT%' THEN 'HOLD_OUT'
           WHEN `filename` LIKE '%NEW%' THEN 'NEW'
           WHEN `filename` LIKE '%NORMAL%' THEN 'NORMAL'
           ELSE ''
           END AS grp,
       `transaction_branch_code`,
       b.`filename`,
       `transaction_acc_no`,
       `transaction_account_holder`,
       `transaction_surname`,
       c.days AS tracking_days
FROM `bldslead_mercantile`.`transactions` a
         JOIN `bldslead_mercantile`.`batches` b ON a.`transaction_batchid` = b.batchid
         JOIN `bldslead_mercantile`.`entry_classes` c ON b.`entry_class` = c.entry_class
WHERE a.acb_status NOT IN  ('REJECTED','NONE')
  AND a.global_status = 'NEW'
  AND a.transaction_action_date BETWEEN DATE_SUB(NOW(), INTERVAL 100 DAY) AND NOW()
;
UPDATE `bldslead_api`.`uccs_debit_order_history` hi
    JOIN `bldslead_mercantile`.`transactions` a ON (a.`transaction_reference` = hi.`polid` AND a.`transaction_action_date` = hi.`action_date` AND a.`transaction_user_code` = hi.`user_code`)
SET 	hi.acb_status = a.acb_status,
       hi.acb_reason_code = a.acb_reason_code,
       hi.acb_reason = a.acb_reason,
       hi.acb_status_date = a.acb_status_date
WHERE a.acb_status NOT IN  ('REJECTED','NONE')
  AND a.global_status = 'NEW'
  AND a.transaction_action_date BETWEEN DATE_SUB(NOW(), INTERVAL 90 DAY) AND NOW()
  AND a.transaction_clientid = 504
  AND hi.`routing` = 'MERCANTILE'
;

select global_status, count(*)
from bldslead_intecon.mandates
where month(insertdt) = 7
group by global_status
;

select bsva_description, count(*) from bldslead_mercantile.mandates where global_status = 'error' and month(insertdt) = 7 and pain12_code not in('SUSP','FALSE') group by bsva_description
;
select reply_str, count(*) from bldslead_intecon.mandates where global_status = 'error' and month(insertdt) = 7 group by REPLY_STR
;

select * from bldslead_mercantile.mandates where bsva_description = 'Request Transaction Identifier must be unique'
;

select * from bldslead_mercantile.mandates where fileid = 25807
;

select * from bldslead_mercantile.acresp where original_contract_reference = '7189533'
;

select * from bldslead_intecon.mandates where ACC_NUM = '01759154227'
;



select * from bldslead_campaigns.sms_outgoing_history where cellno = '0826031449'
;
select *
from bldslead_campaigns.incoming_history
where campaignid = 41 order by insertdt desc
;
-- facebook
select b.product, count(*)
from bldslead_api.uccs_customers a
join (select *
      from forms_incoming_history
      where request_json like '%"affid": "facebook"%'
        and (global_status = 'SUCCESS' or global_status = '')
        and insertdt between '2020-12-01 00:00:00' and '2020-12-31 23:59:59'
      order by insertdt desc) b on a.cellno = b.cellno
where a.date_of_sale >= b.insertdt
group by b.product
;

-- sales by product
select b.product, count(*)
from bldslead_api.uccs_customers a
         join (select *
               from italk_history
               where insertdt between '2020-10-01 00:00:00' and '2020-10-18 23:59:59'
             --     and product = 'cards'
             and status = 'success'
) b on a.cellno = b.cellno
where a.date_of_sale >= b.insertdt
group by b.product
;


select * from italk_history where cellno = '0678701441'
;

select * from bldslead_campaigns.incoming_history where cellno = '0813552466'
;

select * from bldslead_campaigns.sms_outgoing_history a join bldslead_campaigns.batches b on a.batchid = b.batchid where a.campaignid = 40
;
select * from uccs_customers where cellno = '0678701441'
;

-- leads  per product
select product, count(*) as leads
from italk_history
where insertdt between '2020-10-01 00:00:00' and '2020-10-31 23:59:59'
  -- and product = 'cards'
  and status = 'success'
group by product
;

-- sales per product
select bank, count(*)
from bldslead_api.uccs_customers
where date_of_sale between '2021-01-01' and '2021-12-31'
group by bank
;


select *
from bldslead_api.uccs_customers a
join bldslead_api.uccs_policies b on a.penPolicy_Number = b.polPolicy_Number
where a.date_of_sale between '2020-10-01' and '2020-10-31'
and campaign_name = ''
and b.policy_status = 'Duplicate'
;

select *
from bldslead_campaigns.send_batches a
    join bldslead_campaigns.batches b on a.batchid = b.batchid
where b.campaignid = 51
;

select * from realpeople_history where cellno = '0655076464'
;

delete from realpeople_history where cellno = '0672378722'
;

select * from miway_history where new_referrer = 0 --  and global_status = 'SUCCESS'
;

select * from bldslead_api.realpeople_history order by insertdt desc
;

select * from ussd_incoming_history where cellno = '27678481656' order by insertdt desc
;
select * from bldslead_campaigns.sms_outgoing_history where cellno = '0614760137'
;
select * from bldslead_campaigns.batches where batchid = 12338
;
-- USSD FUNCTIONS
select * from ussd_incoming_history_old where cellno = '27609868605' order by insertdt desc
;
select * from ussd_incoming_history where product = 'cellular' and date(insertdt) = '2021-09-02' order by insertdt desc
;

select * from ussd_incoming_history where date(insertdt) < '2021-09-13' and date(result_timestamp) = '2021-09-15' and product = 'cellular' and global_status = 'SUCCESS'
;

select * from bldslead_api.realpeople_sales where sale_xml like '%IT101914%'
;

select cellno, count(*)
from ussd_incoming_history
where product = 'tpc'
  and terms_accepted = 1
and date(insertdt) > '2021-09-01'
and global_status = 'success'
group by cellno
;

show full processlist
;
UPDATE avm_incoming_history SET terms_accepted = 1, global_status = 'QUEUE' where global_status = 'ERROR' and qid = '' order by insertdt desc
;

select * from avm_incoming_history where cellno in (
'27849248193',
'27743511266',
'27843564767',
'27849113178'
);
select * from ussd_incoming_history where id = 661708
;

select * from bldslead_api.uccs_policies where polPolicy_Number = 'N14_19747886'
;

select *
from bldslead_api.uccs_policies a
join bldslead_vas.policies b on a.polID = b.polid
where b.date_of_sale between '2021-09-27' and '2021-10-01'
and a.date_of_sale between '2021-09-27' and '2021-10-01'
-- and isnull(b.polid)
;

select *
from bldslead_api.uccs_policies
where date_of_sale = '2021-09-28'
and polid not in (select polid from bldslead_vas.policies)
;

UPDATE ussd_incoming_history SET global_status = 'QUEUE' where id = 661708
;
select qid, count(*) as total
from ussd_incoming_history
where global_status = 'QUEUE'
  and date(insertdt) between date_sub(current_date, INTERVAL 8 DAY ) and date_sub(current_date, INTERVAL 1 DAY )
and qid != ''
group by qid
;

select * from ussd_incoming_history where qid = '61555483026228.43529366'
;
select date(insertdt), count(*)
from realpeople_sales
group by date(insertdt)
;

update avm_incoming_history
set terms_accepted = 1, global_status = 'QUEUE'
where terms_accepted = 0 and qid = '';

select * from ussd_incoming_history where terms_accepted = 1 order by insertdt desc
;

INSERT INTO bldslead_uccs_disposition.uccs_disposition (ID, call_time, data_batch_source, batch_ref, idno, CampaignID, LocationID, UserID, lead_status, lead_disposition, cellno, call_disposition, callback_date, `LEAD`, refA, refC, score,TYPE, file_name)
SELECT ID,
       STR_TO_DATE(TIMESTAMP, '%e %M %Y %H:%i:%s') AS call_time,
       IF(ISNULL(Data_Batch_Source),'',Data_Batch_Source) AS data_batch_source,
       IF(ISNULL(Batch_Ref),'',Batch_Ref) AS batch_ref,
       IF(ISNULL(LeadID),'',LEFT(LeadID,13)) AS idno,
       CampaignID,
       LocationID,
       UserID,
       IF(ISNULL(STATUS),'',STATUS) AS lead_status,
       IF(ISNULL(Disposition),'',Disposition) AS lead_disposition,
       CONCAT('0', RIGHT(TRIM(cellno),9)) AS cellno,
       IF(ISNULL(CallDisposition),'',CallDisposition) AS call_disposition,
       IF(CallbackDate LIKE '01/01/1900%','0000-00-00 00:00:00',STR_TO_DATE(CallbackDate, '%d/%m/%Y %h:%i:%s')) AS callback_date,
       `LEAD`,
       IF(ISNULL(RefA),'',RefA) AS refA,
       IF(ISNULL(RefC),'',RefC) AS refC,
       IF(ISNULL(Score),'',Score) AS score,
       '',
       'ActivityReport_11_Sep_2021.csv'
FROM  bldslead_uccs_disposition.uccs_disposition_import
ORDER BY STR_TO_DATE(TIMESTAMP, '%e %M %Y %H:%i:%s') DESC


select * from ussd_incoming_history where qid = '6138585a4ff211.91849044'
;

select * from realpeople_history order by insertdt desc
-- where cellno = '0828941312'
;

select * from velociti_history where cellno = '0764721775'
;


select * from ussd_incoming_history where global_status != 'QUEUE' and qid = '613858d4112c76.91408292' order by id
;
select * from ussd_incoming_history where id = 511323
;
select * from bldslead_api.forms_incoming_history where email = 'planning4@wre.co.za' order by insertdt desc
;

select * from bldslead_mercantile.mandates where debtor_cellno = '0630049758'
;
select * from bldslead_mercantile.transactions where transaction_reference = '7173785'
;
select * from bldslead_mercantile.acresp where original_contract_reference = '7232305'
;
SELECT * FROM bldslead_vas.mandates WHERE debtor_cellno = '0764073339'-- AND global_status = 'QUEUE'
;

select * from  bldslead_vas.mandates WHERE mandate_inititation_date = current_date
;

select * from bldslead_vas.customers where idno = '9708135890084'
;

-- 0791418170 / 8106095900087 - PHone Finder - No VAS sale. - 15 September
-- 0789440103 / 9409205802089 - Phone Finder - No VAS sale. - 15 September
-- 0607883824 / 9708135890084 - Phone Finder - No VAS sale. - 16 September

select request_json
from forms_incoming_history
where insertdt between '202-08-0100:00:00' and '2021-08-3123:59:59'
and product = 'survey'           ;


select * from bldslead_vas.policies where customer_id = 55911
;

select date(insertdt), count(*) as total, sum(rms) as RMS, sum(if(REPLY_CD = '207' AND rms = 0, 1, 0)) as accepted, sum(if(REPLY_CD != '6022' AND REPLY_CD != '207', 1, 0)) as bank_error
from bldslead_intecon.mandates a
where PMT_STREAM = 'ACOL'
and insertdt > '2021-04-02'
-- and REPLY_STR = 'User not logged in (3000)'
group by date(insertdt)
order by insertdt desc
;
select *
from bldslead_intecon.mandates a
where PMT_STREAM = 'ACOL'
  and insertdt > '2021-04-02'
  and global_status = 'error'
order by insertdt desc
;

SELECT request_identifier,
       CAST(SUBSTRING(`transaction_amount` FROM 1 FOR CHAR_LENGTH(`transaction_amount`) - 2) AS DECIMAL(10,2)) AS amount,
       'NORMAL' as collectionType,
       transaction_action_date as actionDate,
       b.days as trackingDays
FROM bldslead_easydebit.transactions a
join bldslead_easydebit.entry_classes b on a.transaction_entry_class = b.entry_class
WHERE transaction_action_date = '2021-08-25'
and a.request_identifier != ''
ORDER BY transaction_insertdrt
LIMIT 500
;

select * from bldslead_intecon.mandates where REPLY_STR = 'Account is not matched to ID number (910002)';

select *
from bldslead_vas.customers a
join bldslead_vas.policies b on a.id = b.customer_id
join bldslead_vas.mandates c on a.id = c.customer_id
where cellno = '0794161415'
;

select * from bldslead_mercantile.mandates where contract_reference = '7107151'
;




update bldslead_mercantile.mandates a
set a.global_status = 'success'
where pain12_code = 'true'
;

-- get debit orders
SELECT *
FROM bldslead_mercantile.mandates a
         JOIN bldslead_api.`uccs_customers` b ON a.debtor_identification = b.`idno`
         JOIN `bldslead_api`.`uccs_policies` c ON b.`penPolicy_Number` = c.`polPolicy_Number`
WHERE c.`userid` IN ('OWTC', 'OWTD', 'OWTE')
  -- AND a.`COLLECTION_DAY` = '31'
  -- AND a.`global_status` = 'success'
;

select *
from bldslead_mercantile.bank_results a
join bldslead_mercantile.transactions b on a.contract_reference
where acb_status = 'DISPUTE'
  and action_date = '210923'
;

select * from bldslead_mercantile.transactions where acb_status = 'DISPUTE' and transaction_action_date = '2021-09-23'
;

SELECT * FROM `bldslead_mercantile`.batches a JOIN `bldslead_mercantile`.users b on a.user_code = b.user_clientid WHERE a.action_date = '2021-04-25' and global_status = 'ACB'
;

select * from bldslead_intecon.mandates where PROM_ID = '0004E002AB' order by insertdt desc
;

select * from bldslead_intecon.mandates where global_status != 'success' order by insertdt desc
;

select * from bldslead_intecon.mandates where global_status = 'UNKNOWN' order by insertdt desc
;
select * from bldslead_intecon.transactions where transaction_reference in (
    select REFERENCE from bldslead_intecon.bank_results where RESPONSE_STR = 'Not allowed amount (AM02)'
    )
;
select * from bldslead_intecon.mandates where MODE = 'Offline'
;

select * from bldslead_intecon.mandates where REPLY_CD = '6369'
;
select *
from bldslead_intecon.mandates a
join bldslead_intecon.bank_results br on a.PROM_ID = br.PROMISSORY_ID
where br.RESPONSE_TYPE = '549'
;

UPDATE bldslead_intecon.mandates a
join bldslead_intecon.bank_results br on a.PROM_ID = br.PROMISSORY_ID
SET a.REPLY_CD = br.RESPONSE_CD, a.REPLY_STR = br.RESPONSE_STR, a.global_status = 'error'
WHERE br.RESPONSE_TYPE = '549'
and a.global_status = 'SUCCESS'
;
select * from bldslead_intecon.mandates where PROM_ID = '0004E002C8'
;
select * from bldslead_intecon.transactions where transaction_reference = '7126946'
;

select RESPONSE_STR, count(*) as requests, CAST(TRN_FEE / 100.00 as DECIMAL(10,2)) as fee
from bldslead_intecon.bank_results
-- where RESPONSE_TYPE in ('549','555','550','556','557')
-- where RESPONSE_TYPE in ('546','547','548')
    where RESPONSE_TYPE in ('508','509','510')
  and TRN_FEE > 0
      and RESPONSE_DT > '2021-04-01'
group by RESPONSE_STR
;

select count(distinct REFERENCE)
from bldslead_intecon.bank_results
-- where RESPONSE_TYPE in ('549','555','550','556','557')
where RESPONSE_TYPE in ('546','547','548')
-- where RESPONSE_TYPE in ('508','509','510')
  and TRN_FEE > 0
and RESPONSE_DT > '2021-04-01'
;

select * from forms_incoming_history where campaignid = '547'
;

/* BATCH RESUBMIT FAILED MANDATES */
-- check which mandates to do
select * from bldslead_intecon.mandates where global_status = 'UNKNOWN'
;
-- update the mandate to unknown status so it overwrites values
update bldslead_intecon.mandates
set MODE = 'Offline'
where global_status = 'unknown' -- which records
  and REPLY_CD = '' -- which reply codes
and FIRST_DATE = '20210625' -- check the action date is still valid
and insertdt > '2021-05-01' -- not more than 30 days old
limit 500 -- limit the records
;

-- select the records
select ACTION, ORG, BRANCH, CLIENT_NUMBER, REFERENCE, FIRST_DATE, 'Day Before Weekend' as DATE_ADJ,
       'Monthly' as FREQUENCY, LPAD(TRACK_DAYS, 2, '0') as TRACK_DAYS,
       INST_AMT as TOTAL_AMT, INST_AMT,
       'Active' as STATUS, BANK_BRANCH, 'Cheque' as ACC_TYPE, ACC_NUM,
       PMT_STREAM, 'False' as ALLOW_SCHEDULING, CARD_BIN, CARD_SEQ,  FIRST_AMT,
       cast(INST_AMT*1.5 as decimal(9,2)) as MAX_INST_AMT, COLLECTION_DAY,
       'Every 12 Months' as INST_ADJ_FREQ, INST_ADJ_AMT, '10' as INST_ADJ_RATE,
       'True' as ALLOW_TRACKING, 'True' as ALLOW_DATE_CHG, 'True' as ALLOW_MAX_INST, MODE
from bldslead_intecon.mandates
where global_status = 'unknown'
  and MODE = 'Offline'
order by insertdt desc
;

select * from bldslead_intecon.mandates where MODE = 'Offline'
;

-- check the prcessed madates
select a.*
from bldslead_intecon.mandates a
join bldslead_intecon.mandates_processed b on a.ORG = b.ORG and a.BRANCH = b.BRANCH and a.CLIENT_NUMBER = b.CLIENT_NUMBER
;


select * from bldslead_intecon.mandates where global_status != 'success' order by insertdt desc
;

select * from bldslead_intecon.mandates where REPLY_CD = '6913' order by insertdt desc
;
select * from bldslead_intecon.bank_results where REFERENCE in (select mandates.REFERENCE from bldslead_intecon.mandates where MODE = 'Online')
;

select * from bldslead_intecon.bank_results where PROMISSORY_ID = '0004DE9353'
;

select * from bldslead_intecon.mandates where PROM_ID = '0004DE935A'
;

select 'RegisterMandate' as ACTION, ORG, BRANCH, PROM_ID as PROMISSORY_ID, 'True' as UPDATE_INST
from bldslead_intecon.mandates
where MODE = 'Offline'
and PMT_STREAM = 'ACOL'
and (global_status = 'PENDING' or REPLY_STR = 'Request Successfully registered (000000)')
-- and REPLY_CD =  '6022'
;

select date(insertdt) as dt, count(*) as total
from bldslead_intecon.mandates
where PMT_STREAM = 'ACOL'
and global_status in ('success', 'pending', 'error')
group by dt
;


select *
from bldslead_mercantile.bank_results
where filename = 'OWTC_20211001_211001153158.TXT'
;
select *
from bldslead_mercantile.transactions
where transaction_reference = '7229069'
;
select *
from bldslead_mercantile.bank_results
where contract_reference = '7175876'
;

update bldslead_mercantile.transactions a
    join bldslead_mercantile.bank_results br on a.transaction_action_date = br.action_date_converted and a.transaction_reference = br.contract_reference
set a.acb_reason = br.acb_description, a.acb_status = br.acb_status, a.acb_reason_code = br.response_code
where br.filename = 'OWTD_20211001_211001153201.TXT'
  and br.acb_status = 'TRACKING'
;
update bldslead_mercantile.transactions a
    join bldslead_mercantile.bank_results br on a.transaction_action_date = br.action_date_converted and a.transaction_reference = br.contract_reference
set a.acb_reason = br.acb_description, a.acb_status = br.acb_status, a.acb_reason_code = br.response_code
where br.filename = 'OWTD_20211001_211001153201.TXT'
  and br.acb_status = 'PAID'
;

update bldslead_mercantile.transactions a
    join bldslead_mercantile.bank_results br on a.transaction_action_date = br.action_date_converted and a.transaction_reference = br.contract_reference
set a.acb_reason = br.acb_description, a.acb_status = br.acb_status, a.acb_reason_code = br.response_code
where br.filename = 'OWTD_20211001_211001153201.TXT'
  and br.acb_status = 'DISPUTE'
;

update bldslead_mercantile.transactions a
    join bldslead_mercantile.bank_results br on a.transaction_action_date = br.action_date_converted and a.transaction_reference = br.contract_reference
set a.acb_reason = br.acb_description, a.acb_status = br.acb_status, a.acb_reason_code = br.response_code
where br.filename = 'OWTD_20211001_211001153201.TXT'
  and br.acb_status = 'UNPAID'
;
select distinct filename
from bldslead_mercantile.bank_results
where user_inst = 'OWTD'
and action_date_converted = '2021-09-23'
order by filename asc
;
select a.action_date, a.contract_reference, a.acb_status, b.acb_status as tr_status
from bldslead_mercantile.bank_results a
join bldslead_mercantile.transactions b on b.transaction_reference = a.contract_reference
where a.acb_status = 'DISPUTE'
and b.transaction_action_date = '2021-09-23'
and a.action_date = '210923'
;

insert into bldslead_mercantile.transactions
select * from bldslead_mercantile.transactions_backup
;

select * from bldslead_mercantile.mandates where contract_reference = '7107230'
;
select *
from bldslead_mercantile.transactions a
    join bldslead_mercantile.bank_results br on a.transaction_action_date = br.action_date and a.transaction_reference = br.contract_reference
where br.filename = 'OWTD_20210429_210429190439.TXT'
;


select * from bldslead_vas.mandates where debtor_account_number = '62885638331'
;
-- errors
select date(insertdt) as dt, count(*) as total
from bldslead_intecon.mandates
where PMT_STREAM = 'ACOL'
  and global_status in ('error')
-- and REPLY_CD not in (6016, 6020) -- technical errors
and REPLY_CD in (6016, 6020) -- customer rejected
group by dt
;

select REPLY_STR, count(*)
from bldslead_intecon.mandates
where PMT_STREAM = 'ACOL'
  and global_status in ('error')
and REPLY_CD not in (6016, 6020) -- technical errors
and date(insertdt) = current_date
-- and REPLY_CD in (6016, 6020) -- customer rejected
group by REPLY_STR
  ;

select *
from bldslead_intecon.mandates
-- where REPLY_STR in ('Internal message response processing failure (E9903)')
order by insertdt desc
;

select * from velociti_history order by insertdt desc
;

select * from realpeople_history where cellno = '0736608414'
;

select * from forms_incoming_history where campaignid = 547
;
select * -- acb_status, count(*)
from uccs_debit_order_history  a
where routing = 'INTECON'
and month(action_date) = 8
  and `group` = 'NEW'
-- and a.polid in (select polid from uccs_debit_order_history where month(action_date) = 5 and `group` = 'NEW')
group by acb_status
;

select date_of_sale, count(*)
from bldslead_api.uccs_policies
where date_of_sale > '2021-03-31'
group by date_of_sale
;
select date(insertdt) as dt, count(*) as total
from bldslead_vas.mandates
group by dt
;
select date(insertdt) as dt, count(*) as total
from bldslead_vas.policies
group by dt
;

select date(insertdt) as dt, count(*) as total
from bldslead_vas.customers
group by dt
;
--

select *
from bldslead_intecon.transactions a
         join bldslead_intecon.mandates b on b.CLIENT_NUMBER = a.transaction_account_holder_idno
where b.global_status = 'SUCCESS' -- only mandates that have been accepted
  and b.PMT_STREAM = 'ACOL'
;

SELECT * FROM bldslead_mercantile.batches WHERE upload_fileid = 240
;

INSERT INTO `bldslead_easydebit`.`mandates`
SELECT id, user_reference, contract_reference, if(tracking_indicator = 'Y', true, false), 'TT1R' as debtor_authentication_code, installment_occurence, 'MONTHLY' as frequency, mandate_initiation_date, first_collection_date, collection_amount_currency, collection_amount, maximum_collection_currency, maximum_collection_amount, 'SCMSA' as entry_class, debtor_account_name, right(debtor_identification, 13) as debtor_identification, debtor_account_number, debtor_account_type, debtor_branch, debtor_cellno, debtor_email, collection_day, date_adjusment_rule, 'ANUALLY' as adjustment_category, adjustment_rate, 'RATE' as adjustment_type, adjustment_amount, first_collection_amount_currency, first_collection_amount, debit_value_type, global_status, '' as status_description, fileid, user_code, auto_rms_value, insertdt, last_updated, filename, '' as request_identifier, 0 as rms
FROM `bldslead_mercantile`.`mandates` ORDER BY insertdt DESC LIMIT 5
;