EDU_EOD

/* Dates with problem
2017-04-09
2017-04-09
2017-04-22
2017-06-21
2017-06-22
2017-07-02
2017-07-03
2017-08-07
2017-09-10
2017-09-13
2017-09-16
2017-09-20
2017-09-21
2017-09-23
2017-10-10
2017-10-17
2017-11-08
2017-11-14
2017-11-18
2017-11-19
2017-11-20
2017-11-21
2017-11-24
2017-11-25
2017-11-29
2017-12-11

*/

-- v1 v2 data summary view
set @fromDate = '2017-09-01';
set @toDate = '2017-09-30';
set @wallet = '015312533355';

SELECT id, s.totalCredit, DATE(currentDate) from sc_daily_txn_status_v2 s
WHERE wallet = @wallet
 AND DATE(s.currentDate) BETWEEN @fromDate AND @toDate
AND totalCredit IS NOT NULL AND totalCredit > 0
;
SELECT id, s.totalCredit, DATE(currentDate) from sc_daily_txn_status s
WHERE wallet = @wallet
 AND DATE(s.currentDate) BETWEEN @fromDate AND @toDate
AND totalCredit IS NOT NULL AND totalCredit > 0
;


-- Getting list where v1-v2 conflicts
SELECT
  date(v1.currentDate),
  v2.wallet,
  v1.txnCode,
  v1.totalCredit                              totalCrV1,
  v2.totalCredit                          totalCrV2,
  (v1.totalCredit - v2.totalCredit)       diff,
  v2.totalCredit,
  v1.totalTxnNumber cnV1,
  v2.totalTxnNumber cnV2,
  (v1.totalTxnNumber - v2.totalTxnNumber) diffcnt
FROM profino_bcbl.sc_daily_txn_status v1 INNER JOIN profino_bcbl.sc_daily_txn_status_v2 v2 ON v1.id = v2.id
WHERE v1.totalCredit != v2.totalCredit
      AND v1.wallet = v2.wallet
      AND v1.txnCode = v2.txnCode
      AND v1.txnCode IN (107, 10702, 1202);



-- Checking switch
SELECT
  r.wallet,
  DATE(r.reportDate),
  lastDayA2bAmount,
  lastDayA2bCrAmount,
  lastDayA2bDrAmount,
  lastDayP2bAmount,
  lastDayP2bCrAmount,
  lastDayP2bDrAmount,
  lastDayA2bAmount +
  lastDayA2bCrAmount +
  lastDayA2bDrAmount +
  lastDayP2bAmount +
  lastDayP2bCrAmount +
  lastDayP2bDrAmount total
FROM switch.sc_merchant_payment_report r
WHERE wallet = '015354870402'
      AND date(reportDate) = '2017-04-10'

;

-- looking in txnlog
SELECT *
FROM sc_transaction_log
WHERE destAC = '019933406470'
      AND (isReversed IS NULL OR isReversed = FALSE)
      AND reversedTxnId IS NULL
      AND originalTxnId IS NULL
      AND status = 'PROCESSED'
      AND date(responseTime) = '2017-09-19'
AND trnxCode = 107
ORDER BY responseTime
;