def collect_view_builders(
self) -> List[DirectIngestPreProcessedIngestViewBuilder]:
builders = [
DirectIngestPreProcessedIngestViewBuilder(
region=self.region.region_code,
ingest_view_name=tag,
view_query_template=f"SELECT * FROM {{{tag}}}",
order_by_cols="",
) for tag in self.controller_tag_rank_list
]
builders.append(
DirectIngestPreProcessedIngestViewBuilder(
ingest_view_name="gatedTagNotInTagsList",
region=self.region.region_code,
view_query_template=
"SELECT * FROM {tagA} LEFT OUTER JOIN {tagB} USING (col);",
order_by_cols="",
))
return builders
def query_raw_data_view_for_builder(
self,
view_builder: DirectIngestPreProcessedIngestViewBuilder,
dimensions: List[str],
query_run_dt: datetime.datetime = DEFAULT_QUERY_RUN_DATETIME,
) -> pd.DataFrame:
view: BigQueryView = view_builder.build()
view_query = view.expanded_view_query(
DirectIngestPreProcessedIngestView.QueryStructureConfig(
raw_table_view_type=RawTableViewType.PARAMETERIZED))
view_query = view_query.replace(
f"@{UPDATE_DATETIME_PARAM_NAME}",
f"TIMESTAMP '{query_run_dt.isoformat()}'",
)
return self.query_view(view.table_for_query,
view_query,
data_types={},
dimensions=dimensions)
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
# =============================================================================
"""Query containing offender cycle information."""
from recidiviz.ingest.direct.views.direct_ingest_big_query_view_types import (
DirectIngestPreProcessedIngestViewBuilder, )
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = """
SELECT *
FROM {LBAKRDTA_TAK040}
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_mo",
ingest_view_name="tak040_offender_cycles",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="DQ_DOC",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
# =============================================================================
"""Query for incarceration sentences."""
from recidiviz.ingest.direct.views.direct_ingest_big_query_view_types import (
DirectIngestPreProcessedIngestViewBuilder,
)
from recidiviz.ingest.direct.regions.us_id.ingest_views.templates_sentences import (
sentence_view_template,
SentenceType,
)
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = sentence_view_template(sentence_type=SentenceType.INCARCERATION)
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_id",
ingest_view_name="mittimus_judge_sentence_offense_sentprob_incarceration_sentences",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="docno, sent_no",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
LEFT OUTER JOIN
all_scd_codes_by_date start_codes
ON
incarceration_periods_from_supervision_sentence.F1_DOC = start_codes.BW_DOC AND
incarceration_periods_from_supervision_sentence.F1_CYC = start_codes.BW_CYC AND
incarceration_periods_from_supervision_sentence.SUB_SUBCYCLE_START_DT = start_codes.STATUS_DATE
LEFT OUTER JOIN
all_scd_codes_by_date end_codes
ON
incarceration_periods_from_supervision_sentence.F1_DOC = end_codes.BW_DOC AND
incarceration_periods_from_supervision_sentence.F1_CYC = end_codes.BW_CYC AND
incarceration_periods_from_supervision_sentence.SUB_SUBCYCLE_END_DT = end_codes.STATUS_DATE
LEFT OUTER JOIN
most_recent_status_updates
ON
incarceration_periods_from_supervision_sentence.F1_DOC = most_recent_status_updates.BW_DOC AND
incarceration_periods_from_supervision_sentence.F1_CYC = most_recent_status_updates.BW_CYC
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_mo",
ingest_view_name=
"tak158_tak024_tak026_incarceration_period_from_supervision_sentence",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="BU_DOC, BU_CYC, BU_SEO, F1_SQN",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
{docstars_officers}.OFFICER AS recent_officer_id,
{docstars_officers}.LNAME AS recent_officer_lname,
{docstars_officers}.FNAME AS recent_officer_fname,
{docstars_officers}.SITEID AS recent_officer_siteid,
CASE
-- If the period has terminated, we don't pull in a supervision level, since this level may correspond
-- to a later period of supervision
WHEN TERM_DATE IS NULL THEN NULL
-- Pick the supervision level override if there is one
ELSE COALESCE(SUPER_OVERRIDE, SUP_LVL)
END AS current_supervision_level
FROM cases_with_terminating_officers
LEFT JOIN {docstars_offenders}
ON (cases_with_terminating_officers.SID = {docstars_offenders}.SID)
LEFT JOIN {docstars_officers}
ON ({docstars_offenders}.AGENT = {docstars_officers}.OFFICER)
)
SELECT * FROM offendercases_with_terminating_and_recent_pos
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_nd",
ingest_view_name="docstars_offendercasestable_with_officers",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="CASE_NUMBER",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
start_movement.movement_code AS start_movement_code,
end_movement.movement_code AS end_movement_code,
start_movement.is_new_revocation AS start_is_new_revocation,
sentence_types.sentence_type,
FROM
critical_movements start_movement
LEFT OUTER JOIN
critical_movements end_movement
ON start_movement.recidiviz_master_person_id = end_movement.recidiviz_master_person_id
AND end_movement.prev_critical_movement_sequence_number = start_movement.sequence_number
LEFT OUTER JOIN
sentence_types
ON start_movement.inmate_number = sentence_types.inmate_number
WHERE NOT start_movement.is_delete_movement
)
SELECT *
FROM periods
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_pa",
ingest_view_name="sci_incarceration_period",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="control_number, sequence_number",
materialize_raw_data_table_views=True,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
full_address.legal_address_2 AS legal_address_2,
full_address.legal_city AS legal_city,
full_address.legal_state AS legal_state,
full_address.legal_zip_code AS legal_zip_code,
inmate_numbers
FROM
most_recent_info
LEFT OUTER JOIN (
SELECT recidiviz_master_person_id, STRING_AGG(DISTINCT inmate_number, ',' ORDER BY inmate_number) AS inmate_numbers
FROM search_inmate_info_with_master_ids
GROUP BY recidiviz_master_person_id
) AS inmate_numbers_grouping
USING (recidiviz_master_person_id)
)
SELECT
*
FROM people
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_pa",
ingest_view_name="doc_person_info_v2",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="recidiviz_master_person_id",
materialize_raw_data_table_views=True,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
assess_tst_id,
tst_dt,
score_by_name,
# Question 1-1 is violation date, which we already have access to from tst_dt
MAX(IF(qstn_num='2-2', qstn_answer, NULL)) AS violation_types,
MAX(IF(qstn_num='3-3', qstn_answer, NULL)) AS new_crime_types,
MAX(IF(qstn_num='4-4', qstn_answer, NULL)) AS parolee_placement_recommendation,
MAX(IF(qstn_num='5-5', qstn_answer, NULL)) AS probationer_placement_recommendation,
MAX(IF(qstn_num='6-6', qstn_answer, NULL)) AS legal_status
FROM
qstn_nums_with_descriptive_answers
GROUP BY
ofndr_num,
body_loc_cd,
ofndr_tst_id,
assess_tst_id,
tst_dt,
score_by_name
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_id",
ingest_view_name="ofndr_tst_tst_qstn_rspns_violation_reports",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="ofndr_num, ofndr_tst_id",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
-- DOS in this table is equivalent to CSQ in other tables.
{FINALLY_FORMED_CITATIONS_E6})
SELECT
JT_DOC,JT_CYC,JT_CSQ,VIOLATED_CONDITIONS,MAX_DATE,JS_DOC,JS_CYC,JS_SEO,JS_FSO,FINAL_FORMED_CREATE_DATE
FROM
citations_with_multiple_violations_jt
JOIN
valid_sentences_js
ON
citations_with_multiple_violations_jt.JT_DOC = valid_sentences_js.JS_DOC
AND citations_with_multiple_violations_jt.JT_CYC = valid_sentences_js.JS_CYC
AND citations_with_multiple_violations_jt.JT_CSQ = valid_sentences_js.JS_CSQ
LEFT JOIN
finally_formed_citations_e6
ON
citations_with_multiple_violations_jt.JT_DOC = finally_formed_citations_e6.E6_DOC
AND citations_with_multiple_violations_jt.JT_CYC = finally_formed_citations_e6.E6_CYC
AND citations_with_multiple_violations_jt.JT_CSQ = finally_formed_citations_e6.E6_DOS
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_mo",
ingest_view_name="tak291_tak292_tak024_citations",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="JT_DOC, JT_CYC, JT_CSQ",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# along with this program. If not, see <https://www.gnu.org/licenses/>.
# =============================================================================
"""Query containing incarceration incident information."""
from recidiviz.ingest.direct.views.direct_ingest_big_query_view_types import (
DirectIngestPreProcessedIngestViewBuilder, )
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = """
SELECT *
FROM {dbo_Miscon}
JOIN
-- In 20-ish cases, the control_number in dbo_Miscon does not correspond to any control number in
-- dbo_tblSearchInmateInfo. We ignore these ids - we only ingest information about people listed in
-- dbo_tblSearchInmateInfo;
(SELECT DISTINCT control_number FROM {dbo_tblSearchInmateInfo}) ids
USING (control_number)
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_pa",
ingest_view_name="dbo_Miscon",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="control_number ASC, misconduct_number ASC",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
CASE
WHEN Race in ("W", "B", "A", "I") THEN Race
ELSE NULL
END AS Race,
CASE
WHEN Race in ("H") THEN "HISPANIC"
ELSE "NOT_HISPANIC"
END AS Ethnicity,
CASE
WHEN Sex in ("F", "M") THEN Sex
ELSE NULL
END AS Sex,
# There are a number of people for whom there are two entries, one with the
# birthdate, and one without. Choose the one with a birthdate set, if present.
# There are also a number of corrupted entries for BirthDate, so set those instead to be null.
MAX(COALESCE(IF(BirthDate in ("A", "B", "F", "H", "I", "M", "W"), NULL, BirthDate), NULL)) as BirthDate,
FROM {OffenderName}
GROUP BY OffenderID, OffenderStatus, Race, Ethnicity, Sex
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_tn",
ingest_view_name="OffenderName",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="OffenderID",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# =============================================================================
"""Query for all external ids ever associated with any person in the DOC or PBPP systems."""
from recidiviz.ingest.direct.views.direct_ingest_big_query_view_types import (
DirectIngestPreProcessedIngestViewBuilder, )
from recidiviz.ingest.direct.regions.us_pa.ingest_views.templates_person_external_ids import (
MASTER_STATE_IDS_FRAGMENT, )
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = f"""WITH
{MASTER_STATE_IDS_FRAGMENT}
SELECT
recidiviz_master_person_id,
STRING_AGG(DISTINCT control_number, ',' ORDER BY control_number) AS control_numbers,
STRING_AGG(DISTINCT state_id, ',' ORDER BY state_id) AS state_ids,
STRING_AGG(DISTINCT parole_number, ',' ORDER BY parole_number) AS parole_numbers
FROM recidiviz_master_person_ids
GROUP BY recidiviz_master_person_id;"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_pa",
ingest_view_name="person_external_ids",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols=None,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# along with this program. If not, see <https://www.gnu.org/licenses/>.
# =============================================================================
"""Query containing sentence information."""
from recidiviz.ingest.direct.views.direct_ingest_big_query_view_types import (
DirectIngestPreProcessedIngestViewBuilder,
)
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = """SELECT ids.control_number AS control_number, sentences.*
FROM
{dbo_Senrec} sentences
-- As of 2020-06-10, there are only 6 sentences with no control numbers - we omit these since there's not way for us to
-- link to a person.
JOIN
(SELECT DISTINCT control_number, inmate_number FROM {dbo_tblSearchInmateInfo}) ids
ON ids.inmate_number = sentences.curr_inmate_num
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_pa",
ingest_view_name="dbo_Senrec",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="control_number, curr_inmate_num",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
JOIN
(SELECT DISTINCT recidiviz_master_person_id, parole_number FROM recidiviz_master_person_ids) ids
ON ids.parole_number = offender.ParoleNumber
),
races_ethnicities AS (
SELECT
recidiviz_master_person_id,
STRING_AGG(DISTINCT OffRaceEthnicGroup, ',' ORDER BY OffRaceEthnicGroup) AS races_ethnicities_list
FROM base_query
GROUP BY recidiviz_master_person_id
)
SELECT * EXCEPT (recency_rank)
FROM base_query
LEFT OUTER JOIN
races_ethnicities
USING (recidiviz_master_person_id)
WHERE recency_rank = 1
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_pa",
ingest_view_name="dbo_Offender",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols=None,
materialize_raw_data_table_views=True,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
DirectIngestPreProcessedIngestViewBuilder, )
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = """
SELECT
*
EXCEPT
(updt_usr_id, updt_dt, cert_xml_doc)
FROM
{ofndr_tst}
LEFT JOIN
{ofndr_tst_cert}
USING
(ofndr_tst_id, assess_tst_id)
WHERE
{ofndr_tst}.assess_tst_id = '2' # LSIR assessments
AND {ofndr_tst_cert}.cert_pass_flg = 'Y' # Test score has been certified
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_id",
ingest_view_name="ofndr_tst_ofndr_tst_cert",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="ofndr_num",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
BdActionID
),
sci_actions AS (
SELECT
ParoleNumber,
ParoleCountID,
BdActionID,
CONCAT(BdActEntryDateYear, BdActEntryDateMonth, BdActEntryDateDay) as ActionDate,
CndConditionCode
FROM
{dbo_BoardAction} dbo_BoardAction
INNER JOIN
distinct_codes
USING
(ParoleNumber, ParoleCountID, BdActionID)
)
SELECT *
FROM sci_actions
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_pa",
ingest_view_name="board_action",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="ParoleNumber, ActionDate",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
def test_template_periods_incarceration_only_movement(self) -> None:
# Arrange
raw_file_configs = get_region_raw_file_config("us_id").raw_file_configs
self.create_mock_raw_file("us_id", raw_file_configs["casemgr"], [])
self.create_mock_raw_file("us_id", raw_file_configs["employee"], [])
self.create_mock_raw_file("us_id", raw_file_configs["facility"], [])
self.create_mock_raw_file("us_id", raw_file_configs["location"], [])
self.create_mock_raw_file("us_id", raw_file_configs["lvgunit"], [])
self.create_mock_raw_file("us_id", raw_file_configs["mittimus"], [])
self.create_mock_raw_file(
"us_id",
raw_file_configs["movement"],
[
(
"10000001",
"11111",
"1",
"2020-01-01 8:00:00",
"I",
"1",
"",
"001",
"00",
"1",
"1",
"A",
None,
None,
)
],
)
self.create_mock_raw_file("us_id", raw_file_configs["offstat"], [])
self.create_mock_raw_file("us_id", raw_file_configs["ofndr_loc_hist"], [])
self.create_mock_raw_file("us_id", raw_file_configs["ofndr_wrkld"], [])
self.create_mock_raw_file("us_id", raw_file_configs["wrkld_cat"], [])
# Act
dimensions = ["docno", "incrno", "start_date", "end_date"]
results = self.query_raw_data_view_for_builder(
DirectIngestPreProcessedIngestViewBuilder(
region="us_id",
ingest_view_name="incarceration_periods",
view_query_template=f"""
WITH {get_all_periods_query_fragment(period_type=PeriodType.INCARCERATION)}
SELECT * FROM periods_with_previous_and_next_info
""",
order_by_cols="docno, incrno, start_date, end_date",
),
dimensions=dimensions,
)
# Assert
expected = pd.DataFrame(
[
[
"11111",
"1",
date(2020, 1, 1),
date(9999, 12, 31),
None,
None,
None,
"1",
]
+ [None] * 6
+ ["10000001"]
+ [None] * 9
],
columns=dimensions
+ [
"prev_fac_typ",
"prev_fac_cd",
"prev_loc_ldesc",
"fac_cd",
"fac_typ",
"fac_ldesc",
"loc_cd",
"loc_ldesc",
"lu_cd",
"lu_ldesc",
"move_srl",
"statuses",
"wrkld_cat_title",
"empl_cd",
"empl_sdesc",
"empl_ldesc",
"empl_title",
"next_fac_typ",
"next_fac_cd",
"next_loc_ldesc",
],
)
expected = expected.set_index(dimensions)
assert_frame_equal(expected, results)
IF(race_cd IS NULL OR race_cd = 'U',
ethnic_cd,
race_cd
)
) AS race_cd
FROM
{{offender}}
LEFT JOIN
{{ofndr_dob}}
ON
{{offender}}.docno = {{ofndr_dob}}.ofndr_num
LEFT JOIN
ethnicity
USING (docno)
LEFT JOIN
current_address_view
ON
current_address_view.offendernumber = {{offender}}.docno
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_id",
ingest_view_name="offender_ofndr_dob_address",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="docno",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
ViolationDate AS violation_timestamp,
1 AS is_history_row
FROM {dbo_Hist_SanctionTracking}
WHERE Type = 'V'
)
)
SELECT
parole_number,
parole_count_id,
set_id,
EXTRACT(DATE FROM MIN(PARSE_TIMESTAMP("%m/%d/%Y %T", violation_timestamp))) as violation_date,
TO_JSON_STRING(
ARRAY_AGG(STRUCT(sequence_id, violation_code) ORDER BY sequence_id)
) AS violation_types
FROM base_violations
WHERE row_rank = 1
GROUP BY parole_number, parole_count_id, set_id;
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_pa",
ingest_view_name="supervision_violation",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols=
"parole_number ASC, CAST(parole_count_id AS INT64) ASC, CAST(set_id AS INT64) ASC",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
UNION DISTINCT
SELECT
Test_Id,
Control_Number,
Test_Desc,
Inmate_number,
Test_Dt,
Fac_Cd,
Test_Score,
ModBy_EmpNum,
LstMod_Dt,
AsmtVer_Num,
Fab_ind,
RSTRvsd_Flg
FROM {dbo_tblInmTestScoreHist}
)
SELECT *
FROM all_test_scores
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_pa",
ingest_view_name="dbo_tblInmTestScore",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="Control_Number, Inmate_number",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
sentence_inst_bt.*,
most_recent_status_by_sentence.MOST_RECENT_SENTENCE_STATUS_SSO,
most_recent_status_by_sentence.MOST_RECENT_SENTENCE_STATUS_SCD,
most_recent_status_by_sentence.MOST_RECENT_SENTENCE_STATUS_DATE
FROM
{LBAKRDTA_TAK022} sentence_bs
JOIN
{LBAKRDTA_TAK023} sentence_inst_bt
ON
sentence_bs.BS_DOC = sentence_inst_bt.BT_DOC AND
sentence_bs.BS_CYC = sentence_inst_bt.BT_CYC AND
sentence_bs.BS_SEO = sentence_inst_bt.BT_SEO
LEFT OUTER JOIN
most_recent_status_by_sentence
ON
sentence_bs.BS_DOC = most_recent_status_by_sentence.BV_DOC AND
sentence_bs.BS_CYC = most_recent_status_by_sentence.BV_CYC AND
sentence_bs.BS_SEO = most_recent_status_by_sentence.BV_SEO
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_mo",
ingest_view_name="tak022_tak023_tak025_tak026_offender_sentence_institution",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="BS_DOC, BS_CYC, BS_SEO",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = f"""
WITH
{get_all_periods_query_fragment(period_type=PeriodType.INCARCERATION)}
# Filter to just incarceration periods
SELECT
# Living unit codes are not yet needed, so ignore them here. If
* EXCEPT(lu_cd, lu_ldesc, wrkld_cat_title, empl_cd, empl_sdesc, empl_ldesc, empl_title, prev_loc_ldesc),
ROW_NUMBER()
OVER (PARTITION BY docno ORDER BY start_date, end_date) AS period_id
FROM
periods_with_previous_and_next_info
WHERE
fac_typ = 'I' # Facility type incarceration
# TODO(#3509): Consider tracking escape incarceration periods in the same way we track absconscion.
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_id",
ingest_view_name="movement_facility_location_offstat_incarceration_periods",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="docno, incrno, start_date, end_date",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
# =============================================================================
"""Query for early discharges of incarceration sentences."""
from recidiviz.ingest.direct.views.direct_ingest_big_query_view_types import (
DirectIngestPreProcessedIngestViewBuilder, )
from recidiviz.ingest.direct.regions.us_id.ingest_views.templates_early_discharge import (
early_discharge_view_template,
EarlyDischargeType,
)
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = early_discharge_view_template(
discharge_type=EarlyDischargeType.INCARCERATION)
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_id",
ingest_view_name="early_discharge_incarceration_sentence",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="ofndr_num, early_discharge_id",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
CONTACT_DATE,
CONTACT_CODE,
# TODO(#5057): Properly ingest CONTACT_CODES 2-6, especially when CONTACT_CODE is null.
CONTACT_CODE_2,
CONTACT_CODE_3,
CONTACT_CODE_4,
CONTACT_CODE_5,
CONTACT_CODE_6,
CATEGORY,
contacts_with_split_supervisor_name.LNAME,
contacts_with_split_supervisor_name.FNAME,
OFFICER,
FROM contacts_with_split_supervisor_name LEFT JOIN
{docstars_officers} officers
ON (
contacts_with_split_supervisor_name.LNAME = officers.LNAME AND
contacts_with_split_supervisor_name.FNAME = officers.FNAME
)
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_nd",
ingest_view_name="docstars_contacts",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="SID",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# =============================================================================
"""Query containing ORAS assessments information."""
from recidiviz.ingest.direct.views.direct_ingest_big_query_view_types import (
DirectIngestPreProcessedIngestViewBuilder, )
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = """
SELECT
* EXCEPT(E01, E03) -- Omitting download dates to create query result stability
FROM
{FOCTEST_ORAS_ASSESSMENTS_WEEKLY}
WHERE
E18 = 'Complete'
-- explicitly filter out any test data from UCCI
AND E10 NOT LIKE '%Test%'
AND E10 NOT LIKE '%test%';
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_mo",
ingest_view_name="oras_assessments_weekly",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="E04",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
parole_number,
-- Recompute sequence number post-filtering to maintain consecutive sequence numbers
ROW_NUMBER() OVER (PARTITION BY parole_number ORDER BY sequence_number) AS period_sequence_number,
supervision_types,
admission_reason,
admission_date AS start_date,
termination_reason,
termination_date,
county_of_residence,
district_office,
district_sub_office_id,
supervision_location_org_code,
supervision_level,
supervising_officer_name,
condition_codes
FROM supervision_periods
-- Filter out periods created that start with termination edges
WHERE open_count != 0
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_pa",
ingest_view_name="supervision_period_v2",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="parole_number ASC, period_sequence_number ASC",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
'' AS OMS_OWNER_V_OIC_HEARINGS_INT_LOC_DESCRIPTION,
'' AS OMS_OWNER_V_OIC_HEARING_RESULTS_RESULT_SEQ,
'' AS OIC_OFFENCE_CATEGORY,
'' AS OIC_OFFENCE_CODE,
'' AS OIC_OFFENCE_DESCRIPTION,
'' AS PLEA_DESCRIPTION,
'' AS FINDING_DESCRIPTION,
'' AS RESULT_OIC_OFFENCE_CATEGORY,
'' AS RESULT_OIC_OFFENCE_CODE,
'' AS RESULT_OIC_OFFENCE_DESCRIPTION,
'' AS Expr1030,
'' AS SANCTION_SEQ,
'' AS COMPENSATION_AMOUNT,
'' AS SANCTION_MONTHS,
'' AS SANCTION_DAYS,
'' AS OMS_OWNER_V_OFFENDER_OIC_SANCTIONS_COMMENT_TEXT,
'' AS OMS_OWNER_V_OFFENDER_OIC_SANCTIONS_RESULT_SEQ,
'' AS ALIAS_NAME_TYPE,
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_nd",
ingest_view_name="elite_offense_in_custody_and_pos_report_data",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="ROOT_OFFENDER_ID",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
# =============================================================================
"""Query containing offender charges information."""
from recidiviz.ingest.direct.views.direct_ingest_big_query_view_types import (
DirectIngestPreProcessedIngestViewBuilder, )
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = """
SELECT charges.*, codes.description as CODE_DESCRIPTION, codes.severity_ranking as SEVERITY_RANKING
FROM {elite_offenderchargestable} charges
LEFT JOIN {RECIDIVIZ_REFERENCE_offense_codes} codes
USING (OFFENCE_CODE)
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_nd",
ingest_view_name="elite_offenderchargestable",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="OFFENDER_BOOK_ID",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
periods_with_officer_case_type_and_supervision_type_info.SUP_TYPE
FROM
periods_with_officer_case_type_and_supervision_type_info
LEFT OUTER JOIN
statuses_on_days start_statuses
ON
periods_with_officer_case_type_and_supervision_type_info.DOC = start_statuses.DOC AND
periods_with_officer_case_type_and_supervision_type_info.CYC = start_statuses.CYC AND
periods_with_officer_case_type_and_supervision_type_info.SUPV_PERIOD_BEG_DT =
start_statuses.STATUSES_DATE
LEFT OUTER JOIN
statuses_on_days end_statuses
ON
periods_with_officer_case_type_and_supervision_type_info.DOC = end_statuses.DOC AND
periods_with_officer_case_type_and_supervision_type_info.CYC = end_statuses.CYC AND
periods_with_officer_case_type_and_supervision_type_info.SUPV_PERIOD_END_DT =
end_statuses.STATUSES_DATE
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region="us_mo",
ingest_view_name=
"tak034_tak026_tak039_apfx90_apfx91_supervision_enhancements_supervision_periods",
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols="DOC, CYC",
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
