We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
update payday self-checks to exclude manual exchanges
Reticketing from #2290.


--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `gittip/models/__init__.py`
Content:
```
1 """
2 
3 The most important object in the Gittip object model is Participant, and the
4 second most important one is Ccommunity. There are a few others, but those are
5 the most important two. Participant, in particular, is at the center of
6 everything on Gittip.
7 
8 """
9 from postgres import Postgres
10 import psycopg2.extras
11 
12 class GittipDB(Postgres):
13 
14     def self_check(self):
15         """
16         Runs all available self checks on the database.
17         """
18         self._check_balances()
19         self._check_tips()
20         self._check_orphans()
21         self._check_orphans_no_tips()
22         self._check_paydays_volumes()
23         self._check_claimed_not_locked()
24 
25     def _check_tips(self):
26         """
27         Checks that there are no rows in tips with duplicate (tipper, tippee, mtime).
28 
29         https://github.com/gittip/www.gittip.com/issues/1704
30         """
31         conflicting_tips = self.one("""
32             SELECT count(*)
33               FROM
34                  (
35                     SELECT * FROM tips
36                     EXCEPT
37                     SELECT DISTINCT ON(tipper, tippee, mtime) *
38                       FROM tips
39                   ORDER BY tipper, tippee, mtime
40                   ) AS foo
41         """)
42         assert conflicting_tips == 0
43 
44     def _check_balances(self):
45         """
46         Recalculates balances for all participants from transfers and exchanges.
47 
48         https://github.com/gittip/www.gittip.com/issues/1118
49         """
50         with self.get_cursor() as cursor:
51             if cursor.one("select exists (select * from paydays where ts_end < ts_start) as running"):
52                 # payday is running and the query bellow does not account for pending
53                 return
54             b = cursor.one("""
55                 select count(*)
56                   from (
57                     select username, sum(a) as balance
58                       from (
59                               select participant as username, sum(amount) as a
60                                 from exchanges
61                                where amount > 0
62                             group by participant
63 
64                                union all
65 
66                               select participant as username, sum(amount-fee) as a
67                                 from exchanges
68                                where amount < 0
69                             group by participant
70 
71                                union all
72 
73                               select tipper as username, sum(-amount) as a
74                                 from transfers
75                             group by tipper
76 
77                                union all
78 
79                               select tippee as username, sum(amount) as a
80                                 from transfers
81                             group by tippee
82                             ) as foo
83                     group by username
84 
85                     except
86 
87                     select username, balance
88                     from participants
89                   ) as foo2
90             """)
91         assert b == 0, "conflicting balances: {}".format(b)
92 
93     def _check_orphans(self):
94         """
95         Finds participants that
96             * does not have corresponding elsewhere account
97             * have not been absorbed by other participant
98 
99         These are broken because new participants arise from elsewhere
100         and elsewhere is detached only by take over which makes a note
101         in absorptions if it removes the last elsewhere account.
102 
103         Especially bad case is when also claimed_time is set because
104         there must have been elsewhere account attached and used to sign in.
105 
106         https://github.com/gittip/www.gittip.com/issues/617
107         """
108         orphans = self.all("""
109             select username
110                from participants
111               where not exists (select * from elsewhere where elsewhere.participant=username)
112                 and not exists (select * from absorptions where archived_as=username)
113         """)
114         assert len(orphans) == 0, "missing elsewheres: {}".format(list(orphans))
115 
116     def _check_orphans_no_tips(self):
117         """
118         Finds participants
119             * without elsewhere account attached
120             * having non zero outstanding tip
121 
122         This should not happen because when we remove the last elsewhere account
123         in take_over we also zero out all tips.
124         """
125         tips_with_orphans = self.all("""
126             WITH orphans AS (
127                 SELECT username FROM participants
128                 WHERE NOT EXISTS (SELECT 1 FROM elsewhere WHERE participant=username)
129             ), valid_tips AS (
130                   SELECT * FROM (
131                             SELECT DISTINCT ON (tipper, tippee) *
132                               FROM tips
133                           ORDER BY tipper, tippee, mtime DESC
134                       ) AS foo
135                   WHERE amount > 0
136             )
137             SELECT id FROM valid_tips
138             WHERE tipper IN (SELECT * FROM orphans)
139             OR tippee IN (SELECT * FROM orphans)
140         """)
141         known = set([25206, 46266]) # '4c074000c7bc', 'naderman', '3.00'
142         real = set(tips_with_orphans) - known
143         assert len(real) == 0, real
144 
145     def _check_paydays_volumes(self):
146         """
147         Recalculate *_volume fields in paydays table using exchanges table.
148         """
149         with self.get_cursor() as cursor:
150             if cursor.one("select exists (select * from paydays where ts_end < ts_start) as running"):
151                 # payday is running
152                 return
153             charge_volume = cursor.all("""
154                 select * from (
155                     select id, ts_start, charge_volume, (
156                             select coalesce(sum(amount+fee), 0)
157                             from exchanges
158                             where timestamp > ts_start
159                             and timestamp < ts_end
160                             and amount > 0
161                         ) as ref
162                     from paydays
163                     order by id
164                 ) as foo
165                 where charge_volume != ref
166             """)
167             assert len(charge_volume) == 0
168 
169             charge_fees_volume = cursor.all("""
170                 select * from (
171                     select id, ts_start, charge_fees_volume, (
172                             select coalesce(sum(fee), 0)
173                             from exchanges
174                             where timestamp > ts_start
175                             and timestamp < ts_end
176                             and amount > 0
177                         ) as ref
178                     from paydays
179                     order by id
180                 ) as foo
181                 where charge_fees_volume != ref
182             """)
183             assert len(charge_fees_volume) == 0
184 
185             ach_volume = cursor.all("""
186                 select * from (
187                     select id, ts_start, ach_volume, (
188                             select coalesce(sum(amount), 0)
189                             from exchanges
190                             where timestamp > ts_start
191                             and timestamp < ts_end
192                             and amount < 0
193                         ) as ref
194                     from paydays
195                     order by id
196                 ) as foo
197                 where ach_volume != ref
198             """)
199             assert len(ach_volume) == 0
200 
201             ach_fees_volume = cursor.all("""
202                 select * from (
203                     select id, ts_start, ach_fees_volume, (
204                             select coalesce(sum(fee), 0)
205                             from exchanges
206                             where timestamp > ts_start
207                             and timestamp < ts_end
208                             and amount < 0
209                         ) as ref
210                     from paydays
211                     order by id
212                 ) as foo
213                 where ach_fees_volume != ref
214             """)
215             assert len(ach_fees_volume) == 0
216 
217     def _check_claimed_not_locked(self):
218         locked = self.all("""
219             SELECT participant
220             FROM elsewhere
221             WHERE EXISTS (
222                 SELECT *
223                 FROM participants
224                 WHERE username=participant
225                 AND claimed_time IS NOT NULL
226             ) AND is_locked
227         """)
228         assert len(locked) == 0
229 
230 
231 def add_event(c, type, payload):
232     SQL = """
233         INSERT INTO events (type, payload)
234         VALUES (%s, %s)
235     """
236     c.run(SQL, (type, psycopg2.extras.Json(payload)))
237
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/gittip/models/__init__.py b/gittip/models/__init__.py
--- a/gittip/models/__init__.py
+++ b/gittip/models/__init__.py
@@ -158,6 +158,7 @@
                             where timestamp > ts_start
                             and timestamp < ts_end
                             and amount > 0
+                            and recorder is null
                         ) as ref
                     from paydays
                     order by id
@@ -174,6 +175,7 @@
                             where timestamp > ts_start
                             and timestamp < ts_end
                             and amount > 0
+                            and recorder is null
                         ) as ref
                     from paydays
                     order by id
@@ -190,6 +192,7 @@
                             where timestamp > ts_start
                             and timestamp < ts_end
                             and amount < 0
+                            and recorder is null
                         ) as ref
                     from paydays
                     order by id
@@ -206,6 +209,7 @@
                             where timestamp > ts_start
                             and timestamp < ts_end
                             and amount < 0
+                            and recorder is null
                         ) as ref
                     from paydays
                     order by id

{"golden_diff": "diff --git a/gittip/models/__init__.py b/gittip/models/__init__.py\n--- a/gittip/models/__init__.py\n+++ b/gittip/models/__init__.py\n@@ -158,6 +158,7 @@\n                             where timestamp > ts_start\n                             and timestamp < ts_end\n                             and amount > 0\n+                            and recorder is null\n                         ) as ref\n                     from paydays\n                     order by id\n@@ -174,6 +175,7 @@\n                             where timestamp > ts_start\n                             and timestamp < ts_end\n                             and amount > 0\n+                            and recorder is null\n                         ) as ref\n                     from paydays\n                     order by id\n@@ -190,6 +192,7 @@\n                             where timestamp > ts_start\n                             and timestamp < ts_end\n                             and amount < 0\n+                            and recorder is null\n                         ) as ref\n                     from paydays\n                     order by id\n@@ -206,6 +209,7 @@\n                             where timestamp > ts_start\n                             and timestamp < ts_end\n                             and amount < 0\n+                            and recorder is null\n                         ) as ref\n                     from paydays\n                     order by id\n", "issue": "update payday self-checks to exclude manual exchanges\nReticketing from #2290.\n\n", "before_files": [{"content": "\"\"\"\n\nThe most important object in the Gittip object model is Participant, and the\nsecond most important one is Ccommunity. There are a few others, but those are\nthe most important two. Participant, in particular, is at the center of\neverything on Gittip.\n\n\"\"\"\nfrom postgres import Postgres\nimport psycopg2.extras\n\nclass GittipDB(Postgres):\n\n    def self_check(self):\n        \"\"\"\n        Runs all available self checks on the database.\n        \"\"\"\n        self._check_balances()\n        self._check_tips()\n        self._check_orphans()\n        self._check_orphans_no_tips()\n        self._check_paydays_volumes()\n        self._check_claimed_not_locked()\n\n    def _check_tips(self):\n        \"\"\"\n        Checks that there are no rows in tips with duplicate (tipper, tippee, mtime).\n\n        https://github.com/gittip/www.gittip.com/issues/1704\n        \"\"\"\n        conflicting_tips = self.one(\"\"\"\n            SELECT count(*)\n              FROM\n                 (\n                    SELECT * FROM tips\n                    EXCEPT\n                    SELECT DISTINCT ON(tipper, tippee, mtime) *\n                      FROM tips\n                  ORDER BY tipper, tippee, mtime\n                  ) AS foo\n        \"\"\")\n        assert conflicting_tips == 0\n\n    def _check_balances(self):\n        \"\"\"\n        Recalculates balances for all participants from transfers and exchanges.\n\n        https://github.com/gittip/www.gittip.com/issues/1118\n        \"\"\"\n        with self.get_cursor() as cursor:\n            if cursor.one(\"select exists (select * from paydays where ts_end < ts_start) as running\"):\n                # payday is running and the query bellow does not account for pending\n                return\n            b = cursor.one(\"\"\"\n                select count(*)\n                  from (\n                    select username, sum(a) as balance\n                      from (\n                              select participant as username, sum(amount) as a\n                                from exchanges\n                               where amount > 0\n                            group by participant\n\n                               union all\n\n                              select participant as username, sum(amount-fee) as a\n                                from exchanges\n                               where amount < 0\n                            group by participant\n\n                               union all\n\n                              select tipper as username, sum(-amount) as a\n                                from transfers\n                            group by tipper\n\n                               union all\n\n                              select tippee as username, sum(amount) as a\n                                from transfers\n                            group by tippee\n                            ) as foo\n                    group by username\n\n                    except\n\n                    select username, balance\n                    from participants\n                  ) as foo2\n            \"\"\")\n        assert b == 0, \"conflicting balances: {}\".format(b)\n\n    def _check_orphans(self):\n        \"\"\"\n        Finds participants that\n            * does not have corresponding elsewhere account\n            * have not been absorbed by other participant\n\n        These are broken because new participants arise from elsewhere\n        and elsewhere is detached only by take over which makes a note\n        in absorptions if it removes the last elsewhere account.\n\n        Especially bad case is when also claimed_time is set because\n        there must have been elsewhere account attached and used to sign in.\n\n        https://github.com/gittip/www.gittip.com/issues/617\n        \"\"\"\n        orphans = self.all(\"\"\"\n            select username\n               from participants\n              where not exists (select * from elsewhere where elsewhere.participant=username)\n                and not exists (select * from absorptions where archived_as=username)\n        \"\"\")\n        assert len(orphans) == 0, \"missing elsewheres: {}\".format(list(orphans))\n\n    def _check_orphans_no_tips(self):\n        \"\"\"\n        Finds participants\n            * without elsewhere account attached\n            * having non zero outstanding tip\n\n        This should not happen because when we remove the last elsewhere account\n        in take_over we also zero out all tips.\n        \"\"\"\n        tips_with_orphans = self.all(\"\"\"\n            WITH orphans AS (\n                SELECT username FROM participants\n                WHERE NOT EXISTS (SELECT 1 FROM elsewhere WHERE participant=username)\n            ), valid_tips AS (\n                  SELECT * FROM (\n                            SELECT DISTINCT ON (tipper, tippee) *\n                              FROM tips\n                          ORDER BY tipper, tippee, mtime DESC\n                      ) AS foo\n                  WHERE amount > 0\n            )\n            SELECT id FROM valid_tips\n            WHERE tipper IN (SELECT * FROM orphans)\n            OR tippee IN (SELECT * FROM orphans)\n        \"\"\")\n        known = set([25206, 46266]) # '4c074000c7bc', 'naderman', '3.00'\n        real = set(tips_with_orphans) - known\n        assert len(real) == 0, real\n\n    def _check_paydays_volumes(self):\n        \"\"\"\n        Recalculate *_volume fields in paydays table using exchanges table.\n        \"\"\"\n        with self.get_cursor() as cursor:\n            if cursor.one(\"select exists (select * from paydays where ts_end < ts_start) as running\"):\n                # payday is running\n                return\n            charge_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, charge_volume, (\n                            select coalesce(sum(amount+fee), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount > 0\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where charge_volume != ref\n            \"\"\")\n            assert len(charge_volume) == 0\n\n            charge_fees_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, charge_fees_volume, (\n                            select coalesce(sum(fee), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount > 0\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where charge_fees_volume != ref\n            \"\"\")\n            assert len(charge_fees_volume) == 0\n\n            ach_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, ach_volume, (\n                            select coalesce(sum(amount), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount < 0\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where ach_volume != ref\n            \"\"\")\n            assert len(ach_volume) == 0\n\n            ach_fees_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, ach_fees_volume, (\n                            select coalesce(sum(fee), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount < 0\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where ach_fees_volume != ref\n            \"\"\")\n            assert len(ach_fees_volume) == 0\n\n    def _check_claimed_not_locked(self):\n        locked = self.all(\"\"\"\n            SELECT participant\n            FROM elsewhere\n            WHERE EXISTS (\n                SELECT *\n                FROM participants\n                WHERE username=participant\n                AND claimed_time IS NOT NULL\n            ) AND is_locked\n        \"\"\")\n        assert len(locked) == 0\n\n\ndef add_event(c, type, payload):\n    SQL = \"\"\"\n        INSERT INTO events (type, payload)\n        VALUES (%s, %s)\n    \"\"\"\n    c.run(SQL, (type, psycopg2.extras.Json(payload)))\n", "path": "gittip/models/__init__.py"}], "after_files": [{"content": "\"\"\"\n\nThe most important object in the Gittip object model is Participant, and the\nsecond most important one is Ccommunity. There are a few others, but those are\nthe most important two. Participant, in particular, is at the center of\neverything on Gittip.\n\n\"\"\"\nfrom postgres import Postgres\nimport psycopg2.extras\n\nclass GittipDB(Postgres):\n\n    def self_check(self):\n        \"\"\"\n        Runs all available self checks on the database.\n        \"\"\"\n        self._check_balances()\n        self._check_tips()\n        self._check_orphans()\n        self._check_orphans_no_tips()\n        self._check_paydays_volumes()\n        self._check_claimed_not_locked()\n\n    def _check_tips(self):\n        \"\"\"\n        Checks that there are no rows in tips with duplicate (tipper, tippee, mtime).\n\n        https://github.com/gittip/www.gittip.com/issues/1704\n        \"\"\"\n        conflicting_tips = self.one(\"\"\"\n            SELECT count(*)\n              FROM\n                 (\n                    SELECT * FROM tips\n                    EXCEPT\n                    SELECT DISTINCT ON(tipper, tippee, mtime) *\n                      FROM tips\n                  ORDER BY tipper, tippee, mtime\n                  ) AS foo\n        \"\"\")\n        assert conflicting_tips == 0\n\n    def _check_balances(self):\n        \"\"\"\n        Recalculates balances for all participants from transfers and exchanges.\n\n        https://github.com/gittip/www.gittip.com/issues/1118\n        \"\"\"\n        with self.get_cursor() as cursor:\n            if cursor.one(\"select exists (select * from paydays where ts_end < ts_start) as running\"):\n                # payday is running and the query bellow does not account for pending\n                return\n            b = cursor.one(\"\"\"\n                select count(*)\n                  from (\n                    select username, sum(a) as balance\n                      from (\n                              select participant as username, sum(amount) as a\n                                from exchanges\n                               where amount > 0\n                            group by participant\n\n                               union all\n\n                              select participant as username, sum(amount-fee) as a\n                                from exchanges\n                               where amount < 0\n                            group by participant\n\n                               union all\n\n                              select tipper as username, sum(-amount) as a\n                                from transfers\n                            group by tipper\n\n                               union all\n\n                              select tippee as username, sum(amount) as a\n                                from transfers\n                            group by tippee\n                            ) as foo\n                    group by username\n\n                    except\n\n                    select username, balance\n                    from participants\n                  ) as foo2\n            \"\"\")\n        assert b == 0, \"conflicting balances: {}\".format(b)\n\n    def _check_orphans(self):\n        \"\"\"\n        Finds participants that\n            * does not have corresponding elsewhere account\n            * have not been absorbed by other participant\n\n        These are broken because new participants arise from elsewhere\n        and elsewhere is detached only by take over which makes a note\n        in absorptions if it removes the last elsewhere account.\n\n        Especially bad case is when also claimed_time is set because\n        there must have been elsewhere account attached and used to sign in.\n\n        https://github.com/gittip/www.gittip.com/issues/617\n        \"\"\"\n        orphans = self.all(\"\"\"\n            select username\n               from participants\n              where not exists (select * from elsewhere where elsewhere.participant=username)\n                and not exists (select * from absorptions where archived_as=username)\n        \"\"\")\n        assert len(orphans) == 0, \"missing elsewheres: {}\".format(list(orphans))\n\n    def _check_orphans_no_tips(self):\n        \"\"\"\n        Finds participants\n            * without elsewhere account attached\n            * having non zero outstanding tip\n\n        This should not happen because when we remove the last elsewhere account\n        in take_over we also zero out all tips.\n        \"\"\"\n        tips_with_orphans = self.all(\"\"\"\n            WITH orphans AS (\n                SELECT username FROM participants\n                WHERE NOT EXISTS (SELECT 1 FROM elsewhere WHERE participant=username)\n            ), valid_tips AS (\n                  SELECT * FROM (\n                            SELECT DISTINCT ON (tipper, tippee) *\n                              FROM tips\n                          ORDER BY tipper, tippee, mtime DESC\n                      ) AS foo\n                  WHERE amount > 0\n            )\n            SELECT id FROM valid_tips\n            WHERE tipper IN (SELECT * FROM orphans)\n            OR tippee IN (SELECT * FROM orphans)\n        \"\"\")\n        known = set([25206, 46266]) # '4c074000c7bc', 'naderman', '3.00'\n        real = set(tips_with_orphans) - known\n        assert len(real) == 0, real\n\n    def _check_paydays_volumes(self):\n        \"\"\"\n        Recalculate *_volume fields in paydays table using exchanges table.\n        \"\"\"\n        with self.get_cursor() as cursor:\n            if cursor.one(\"select exists (select * from paydays where ts_end < ts_start) as running\"):\n                # payday is running\n                return\n            charge_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, charge_volume, (\n                            select coalesce(sum(amount+fee), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount > 0\n                            and recorder is null\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where charge_volume != ref\n            \"\"\")\n            assert len(charge_volume) == 0\n\n            charge_fees_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, charge_fees_volume, (\n                            select coalesce(sum(fee), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount > 0\n                            and recorder is null\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where charge_fees_volume != ref\n            \"\"\")\n            assert len(charge_fees_volume) == 0\n\n            ach_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, ach_volume, (\n                            select coalesce(sum(amount), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount < 0\n                            and recorder is null\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where ach_volume != ref\n            \"\"\")\n            assert len(ach_volume) == 0\n\n            ach_fees_volume = cursor.all(\"\"\"\n                select * from (\n                    select id, ts_start, ach_fees_volume, (\n                            select coalesce(sum(fee), 0)\n                            from exchanges\n                            where timestamp > ts_start\n                            and timestamp < ts_end\n                            and amount < 0\n                            and recorder is null\n                        ) as ref\n                    from paydays\n                    order by id\n                ) as foo\n                where ach_fees_volume != ref\n            \"\"\")\n            assert len(ach_fees_volume) == 0\n\n    def _check_claimed_not_locked(self):\n        locked = self.all(\"\"\"\n            SELECT participant\n            FROM elsewhere\n            WHERE EXISTS (\n                SELECT *\n                FROM participants\n                WHERE username=participant\n                AND claimed_time IS NOT NULL\n            ) AND is_locked\n        \"\"\")\n        assert len(locked) == 0\n\n\ndef add_event(c, type, payload):\n    SQL = \"\"\"\n        INSERT INTO events (type, payload)\n        VALUES (%s, %s)\n    \"\"\"\n    c.run(SQL, (type, psycopg2.extras.Json(payload)))\n", "path": "gittip/models/__init__.py"}]}