rcKUyUf9

1from alerts.slack_alert import SlackAlert
2
3
4"""
5 This alert will trigger when a transaction is made at Apple Retail Voice Authorization. This is intended to counter a fraud ring.
6  Between 60-100% of transactions made at Apple Retail Voice Authorization between 4/9-5/8/19 were fraudulent.
7
8"""
9
10alert = SlackAlert(
11    alert="apple_retail_voice_authorization_alert",
12    timezone_conversion_for="accrual_time",
13    query = """
14        CREATE TABLE IF NOT EXISTS alerts.apple_retail_voice_authorization_alert (
15             alert_id VARCHAR primary key);
16
17          WITH apple AS (
18            SELECT
19                nto.transaction_id,
20                nto.card_id AS network_card_id,
21                nto.customer_account_id,
22                nto.accrual_time,
23                nto.transaction_data -> 'card_acceptor' ->> 'name' AS merchant,
24                nto.transaction_data -> 'card_acceptor' ->> 'mid' AS mid,
25                pt.merchant_id,
26                pt.id,
27                SUM(nto.amount/100.0) AS dollars
28            FROM core.network_v2_transaction_operations nto
29            LEFT JOIN core.present_transactions pt
30                ON pt.network_transaction_id = nto.transaction_id
31            WHERE  nto.accrual_time >= now() - interval '1 day'
32            AND (nto.transaction_data -> 'card_acceptor' ->> 'name' ilike '%APPLE RETAIL VOICEAUTH%')
33            GROUP BY 1,2,3,4,5,6,7,8),
34        card AS (
35            SELECT
36                a.id AS txn_id,
37                a.customer_account_id,
38                a.network_card_id,
39                a.accrual_time,
40                a.dollars AS usd_amount,
41                c.status,
42                c.holder_name,
43                c.company_name,
44                c.instrument_type,
45                c.last4,
46                a.merchant,
47                CONCAT(COALESCE(a.merchant_id,a.mid),'_',c.id) AS alert_id
48            FROM apple a
49            JOIN core.network_v2_cards c
50                ON c.id = a.network_card_id
51                AND c.status = 'active')
52
53            SELECT *, new.alert_id AS id 
54            FROM card new 
55            LEFT JOIN alerts.apple_retail_voice_authorization_alert old 
56                ON new.alert_id = old.alert_id
57            WHERE old.alert_id IS NULL 
58    """,
59    message_template = {
60        "channel": "#ambika_test_channel",
61        "username": "Apple Retail Voice Authorization Alert",
62        "text": "Flagging transactions at Apple Retail Voice Authorization which we think are fraudulent",
63        "attachments": [
64            {
65                "color": "warning",
66                "fields": [
67                    {"title": "Accrual Time", "value": "{timestamp}", "short": True},
68                    {
69                        "title": "Cardholder Name",
70                        "value": "{holder_name}",
71                        "short": True,
72                    } ,
73                    {
74                        "title": "Company Name",
75                        "value": "{company_name}",
76                        "short": True,
77                    },
78                    {
79                        "title": "Dollar Amount",
80                        "value": "{usd_amount}",
81                        "short": True,
82                    },
83                    {
84                        "title": "Merchant",
85                        "value": "{merchant}",
86                        "short": true
87                    },
88                    {
89                        "title": "Transaction ID",
90                        "value": "{txn_id}",
91                        "short": true
92                    },
93		                {
94                        "title": "Last 4",
95                        "value": "{last4}",
96                        "short": true
97                    }
98
99                ],
100            }
101        ],
102    },
103)
104
105def main():
106    alert.send()
107
108
109if __name__ == "__main__":