def test_it_works(db, django_file_storage):
with freeze_time("2020-01-02"):
de = DocusignEnvelopeFactory(
status=HP_DOCUSIGN_STATUS_CHOICES.SIGNED,
docs__kind=HP_ACTION_CHOICES.EMERGENCY,
docs__user__email="*****@*****.**",
)
OnboardingInfoFactory(user=de.docs.user)
HPActionDetailsFactory(
user=de.docs.user,
sue_for_harassment=True,
sue_for_repairs=False,
)
with connection.cursor() as cursor:
execute_ehpa_filings_query(cursor)
rows = list(generate_json_rows(cursor))
assert rows == [{
"created_at": datetime(2020, 1, 2, tzinfo=utc),
"first_name": "Boop",
"last_name": "Jones",
"borough": "BROOKLYN",
"phone_number": "5551234567",
"email": "*****@*****.**",
"sue_for_harassment": True,
"sue_for_repairs": False,
}]
def run_ddo_sql_query(bbl: str) -> Optional[Dict[str, Any]]:
sql_query = DDO_SQL_FILE.read_text()
with connections[settings.WOW_DATABASE].cursor() as cursor:
cursor.execute(sql_query, {"bbl": bbl})
results = list(generate_json_rows(cursor))
if results:
return results[0]
# No results are returned if a user goes directly to a query URL with an
# address and we can't find the requested bbl in our database.
return None
def test_it_includes_latest_ehpa_sign_date(db, django_file_storage):
oi = OnboardingInfoFactory()
with freeze_time("2020-08-21"):
DocusignEnvelopeFactory(status="SIGNED", docs__user=oi.user)
with connection.cursor() as cur:
execute_user_stats_query(cur)
result = list(generate_json_rows(cur))
assert len(result) == 1
assert result[0]["latest_signed_ehpa_date"].date().isoformat(
) == "2020-08-21"
def test_generate_json_rows_works(db):
from django.db import connection
with connection.cursor() as cursor:
cursor.execute(
'CREATE TABLE blarg (foo VARCHAR(5), bar VARCHAR(5), baz INTEGER[])'
)
cursor.execute("INSERT INTO blarg (foo, bar, baz) VALUES "
"('hi', 'there', '{1,2}'), ('yo', 'dawg', '{3,4}')")
cursor.execute('SELECT foo, bar, baz FROM blarg')
rows = generate_json_rows(cursor)
assert next(rows) == {'foo': 'hi', 'bar': 'there', 'baz': [1, 2]}
assert next(rows) == {'foo': 'yo', 'bar': 'dawg', 'baz': [3, 4]}
with pytest.raises(StopIteration):
next(rows)
def run_rent_stab_sql_query(bbl: str) -> Optional[Dict[str, Any]]:
sql_query = """
select uc2007, uc2008, uc2009, uc2010, uc2011, uc2012, uc2013,
uc2014, uc2015, uc2016, uc2017, uc2018, uc2019, uc2020
from rentstab
full join rentstab_v2 using(ucbbl)
where ucbbl = %(bbl)s
"""
with connections[settings.NYCDB_DATABASE].cursor() as cursor:
cursor.execute(sql_query, {"bbl": bbl})
json_result = list(generate_json_rows(cursor))
if not json_result:
return None
return json_result[0]
def run_ddo_sql_query(bbl: str) -> Dict[str, Any]:
sql_query = DDO_SQL_FILE.read_text()
with connections[settings.WOW_DATABASE].cursor() as cursor:
cursor.execute(sql_query, {'bbl': bbl})
return list(generate_json_rows(cursor))[0]
def generate_json_rows(self) -> Iterator[Dict[str, Any]]:
with self._get_cursor_and_execute_query() as cursor:
yield from generate_json_rows(cursor)
def resolve_conversations(
parent,
info,
query: str,
first: int,
after_or_at: float,
) -> LatestTextMessagesResult:
where_clauses: List[str] = []
with_clause = f"WITH latest_conversation_msg AS ({CONVERSATIONS_SQL_FILE.read_text()})"
select_with_user_info_statement = """
SELECT
msg.*,
usr.id as user_id,
usr.first_name || ' ' || usr.last_name as user_full_name
FROM
latest_conversation_msg AS msg
LEFT JOIN
users_justfixuser AS usr ON '+1' || usr.phone_number = msg.user_phone_number
"""
if after_or_at:
where_clauses.append("(ordering <= %(after_or_at)s)")
if ALL_DIGITS_RE.fullmatch(query):
where_clauses.append(
"(user_phone_number LIKE '+1' || %(query)s || '%%')")
elif query:
where_clauses.append(
"((usr.first_name || ' ' || usr.last_name) ILIKE '%%' || %(query)s || '%%')"
)
where_clause = ('WHERE ' +
' AND '.join(where_clauses)) if where_clauses else ''
order_clause = "ORDER BY ordering DESC"
limit_clause = f"LIMIT %(first)s"
sql_args = {
'first': first,
'after_or_at': after_or_at,
'query': query,
}
with connection.cursor() as cursor:
base_select = '\n'.join([
select_with_user_info_statement,
where_clause,
])
cursor.execute(
'\n'.join([
with_clause,
base_select,
order_clause,
limit_clause,
]), sql_args)
messages = [
LatestTextMessage(**row) for row in generate_json_rows(cursor)
]
cursor.execute(
'\n'.join([
f"{with_clause}, filtered_messages AS ({base_select})",
'SELECT COUNT(*) FROM filtered_messages',
]), sql_args)
count = cursor.fetchone()[0]
return LatestTextMessagesResult(messages=messages,
has_next_page=count > len(messages))
def resolve_conversations(
parent,
info,
query: str,
first: int,
after_or_at: float,
) -> LatestTextMessagesResult:
where_clauses: List[str] = []
extra_joins: List[str] = []
sql_args: Dict[str, Union[int, str, float]] = {}
parsed = Query.parse(query)
latest_conversation_sql = CONVERSATIONS_SQL_FILE.read_text()
if parsed.message_body:
latest_conversation_sql = insert_before(
source=latest_conversation_sql,
find="WINDOW",
insert="WHERE BODY ILIKE '%%' || %(message_body)s || '%%'\n",
)
sql_args["message_body"] = parsed.message_body
with_clause = f"WITH latest_conversation_msg AS ({latest_conversation_sql})"
select_with_user_info_statement = """
SELECT
msg.*,
usr.id as user_id,
usr.first_name || ' ' || usr.last_name as user_full_legal_name
FROM
latest_conversation_msg AS msg
LEFT JOIN
users_justfixuser AS usr ON '+1' || usr.phone_number = msg.user_phone_number
"""
if after_or_at:
where_clauses.append("(ordering <= %(after_or_at)s)")
sql_args["after_or_at"] = after_or_at
if parsed.phone_number:
where_clauses.append(
"(user_phone_number LIKE '+1' || %(phone_number)s || '%%')")
sql_args["phone_number"] = parsed.phone_number
if parsed.full_legal_name:
where_clauses.append(
"((usr.first_name || ' ' || usr.last_name) ILIKE '%%' || %(full_legal_name)s || '%%')"
)
sql_args["full_legal_name"] = parsed.full_legal_name
if parsed.has_hpa_packet:
extra_joins.append(
"INNER JOIN hpaction_hpactiondocuments AS hpadocs ON usr.id = hpadocs.user_id"
)
where_clause = ("WHERE " +
" AND ".join(where_clauses)) if where_clauses else ""
order_clause = "ORDER BY ordering DESC"
limit_clause = f"LIMIT %(first)s"
sql_args["first"] = first
with connection.cursor() as cursor:
base_select = "\n".join([
select_with_user_info_statement,
where_clause,
*extra_joins,
])
cursor.execute(
"\n".join([
with_clause,
base_select,
order_clause,
limit_clause,
]),
sql_args,
)
messages = [
LatestTextMessage(**row) for row in generate_json_rows(cursor)
]
cursor.execute(
"\n".join([
f"{with_clause}, filtered_messages AS ({base_select})",
"SELECT COUNT(*) FROM filtered_messages",
]),
sql_args,
)
count = cursor.fetchone()[0]
return LatestTextMessagesResult(messages=messages,
has_next_page=count > len(messages))
