from recidiviz.utils.environment import GAE_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = f"""
WITH
{get_all_periods_query_fragment(period_type=PeriodType.SUPERVISION)}
# Filter to just supervision periods
SELECT
*,
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 = 'P' # Facility type probation/parole
OR (fac_typ = 'F' AND prev_fac_typ = 'P') # Fugitive, but escaped from supervision
ORDER BY docno, incrno, start_date, end_date
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_id',
ingest_view_name='movement_facility_location_offstat_supervision_periods',
view_query_template=VIEW_QUERY_TEMPLATE,
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# 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 sentence aggs information."""
from recidiviz.ingest.direct.controllers.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 {elite_offendersentenceaggs}
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_nd',
ingest_view_name='elite_offendersentenceaggs',
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()
ParoleNumber,
ParoleCountID,
Sent16DGroupNumber,
sent.SentenceID,
sent.SentMonth, sent.SentDay, sent.SentYear,
sent.SentTerm, sent.SentType, sent.SentOTN,
sent.SentMinSentenceYear, sent.SentMinSentenceMonth, sent.SentMinSentenceDay,
sent.SentMaxSentenceYear, sent.SentMaxSentenceMonth, sent.SentMaxSentenceDay,
sent.SentCounty,
sent.SentOffense, sent.sentCodeSentOffense,
sent.SentOffense2, sent.sentCodeSentOffense2,
sent.SentOffense3, sent.sentCodeSentOffense3,
sg.SenProbInd,
sg.SenMaxYear, sg.SenMaxMonth, sg.SenMaxDay,
sg.SentEffectiveDate,
FROM {dbo_Sentence} sent
LEFT JOIN {dbo_SentenceGroup} sg
USING (ParoleNumber, ParoleCountID, Sent16DGroupNumber)
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_pa',
ingest_view_name='supervision_sentence',
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols='ParoleNumber ASC, ParoleCountID ASC, Sent16DGroupNumber ASC, SentenceID ASC'
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
AND {cis_offenderaddress}.validaddress = 'T' -- Valid address
AND {cis_offenderaddress}.enddate IS NULL -- Active address
AND {cis_personaddress}.codeaddresstypeid IN ('1')) -- Physical address
WHERE row_num = 1)
SELECT
*
EXCEPT(updt_dt, updt_usr_id) # Seem to update every week? (table is generated)
FROM
{offender}
LEFT JOIN
{ofndr_dob}
ON
{offender}.docno = {ofndr_dob}.ofndr_num
LEFT JOIN
current_address_view
ON
current_address_view.personid = {offender}.docno
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_id',
ingest_view_name='offender_ofndr_dob_address',
view_query_template=VIEW_QUERY_TEMPLATE,
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# 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 person demographic and identifier information from the DOC."""
from recidiviz.ingest.direct.controllers.direct_ingest_big_query_view_types import \
DirectIngestPreProcessedIngestViewBuilder
from recidiviz.utils.environment import GAE_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = "SELECT * FROM {dbo_IcsDoc};"
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_pa',
ingest_view_name='dbo_IcsDoc',
view_query_template=VIEW_QUERY_TEMPLATE)
if __name__ == '__main__':
with local_project_id_override(GAE_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()
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()
'' 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()
#
# 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 for early discharges of incarceration sentences."""
from recidiviz.ingest.direct.controllers.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 GAE_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(GAE_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# 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 for deleted early discharges hanging off of supervision sentences."""
from recidiviz.ingest.direct.controllers.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.SUPERVISION, ids_only=True)
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_id',
ingest_view_name='early_discharge_supervision_sentence_deleted_rows',
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols='ofndr_num, early_discharge_id',
is_detect_row_deletion_view=True,
primary_key_tables_for_entity_deletion=['early_discharge_sent'],
)
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 for all external ids ever associated with any person in the DOC or PBPP systems."""
from recidiviz.ingest.direct.controllers.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 GAE_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, ',') AS control_numbers,
STRING_AGG(DISTINCT state_id, ',') AS state_ids,
STRING_AGG(DISTINCT 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
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
AND violation_reports_by.BY_CYC = conditions_violated_cf.CF_CYC
AND violation_reports_by.BY_VSN = conditions_violated_cf.CF_VSN
JOIN
valid_sentences_cz
ON
violation_reports_by.BY_DOC = valid_sentences_cz.CZ_DOC
AND violation_reports_by.BY_CYC = valid_sentences_cz.CZ_CYC
AND violation_reports_by.BY_VSN = valid_sentences_cz.CZ_VSN
LEFT JOIN
finally_formed_violations_e6
ON
violation_reports_by.BY_DOC = finally_formed_violations_e6.E6_DOC
AND violation_reports_by.BY_CYC = finally_formed_violations_e6.E6_CYC
AND violation_reports_by.BY_VSN = finally_formed_violations_e6.E6_DOS
LEFT JOIN
officers_with_recent_role
ON
violation_reports_by.BY_PON = officers_with_recent_role.BDGNO
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_mo',
ingest_view_name='tak028_tak042_tak076_tak024_violation_reports_v2',
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols='BY_DOC, BY_CYC, BY_VSN',
)
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_incarceration_sentence.F1_DOC = start_codes.BW_DOC AND
incarceration_periods_from_incarceration_sentence.F1_CYC = start_codes.BW_CYC AND
incarceration_periods_from_incarceration_sentence.SUB_SUBCYCLE_START_DT = start_codes.STATUS_DATE
LEFT OUTER JOIN
all_scd_codes_by_date end_codes
ON
incarceration_periods_from_incarceration_sentence.F1_DOC = end_codes.BW_DOC AND
incarceration_periods_from_incarceration_sentence.F1_CYC = end_codes.BW_CYC AND
incarceration_periods_from_incarceration_sentence.SUB_SUBCYCLE_END_DT = end_codes.STATUS_DATE
LEFT OUTER JOIN
most_recent_status_updates
ON
incarceration_periods_from_incarceration_sentence.F1_DOC = most_recent_status_updates.BW_DOC AND
incarceration_periods_from_incarceration_sentence.F1_CYC = most_recent_status_updates.BW_CYC
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_mo',
ingest_view_name=
'tak158_tak023_tak026_incarceration_period_from_incarceration_sentence_v2',
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols='BT_DOC, BT_CYC, BT_SEO, F1_SQN',
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
#
# 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 for incarceration sentences."""
from recidiviz.ingest.direct.controllers.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 GAE_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(GAE_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# Question 1-1 is violation date, which we already have access to from tst_dt
MAX(IF(qstn_num='1-2', qstn_answer, NULL)) AS violation_types,
MAX(IF(qstn_num='1-3', qstn_answer, NULL)) AS new_crime_types,
MAX(IF(qstn_num='1-4', qstn_answer, NULL)) AS pv_initiated_by_prosecutor,
MAX(IF(qstn_num='1-5', qstn_answer, NULL)) AS parolee_placement_recommendation,
MAX(IF(qstn_num='1-6', qstn_answer, NULL)) AS probationer_placement_recommendation,
MAX(IF(qstn_num='1-7', 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
ORDER BY
ofndr_num,
ofndr_tst_id
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_id',
ingest_view_name='ofndr_tst_tst_qstn_rspns_violation_reports_old',
view_query_template=VIEW_QUERY_TEMPLATE,
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
usr_typ_cd,
# updt_usr_id,
# updt_dt,
lan_id,
st_id_num,
body_loc_cd,
body_loc_desc,
loc_typ_cd,
body_loc_cd_id
FROM
{ofndr_agnt} a
LEFT JOIN
{applc_usr} u
ON
(agnt_id = usr_id)
LEFT JOIN
{body_loc_cd}
USING
(body_loc_cd)
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_id',
ingest_view_name='ofndr_agnt_applc_usr_body_loc_cd_current_pos',
view_query_template=VIEW_QUERY_TEMPLATE,
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
(cntc_rslt_cd)
LEFT JOIN
{cntc_typ_cd}
USING
(cntc_typ_cd)
LEFT JOIN
{cntc_title_cd}
USING
(cntc_title_cd)
LEFT JOIN
{applc_usr}
ON
udc_agnt1_id = usr_id
WHERE
cntc_typ_desc = 'FACE TO FACE'
# TOOD(3394): Ingest all of contacts not just those since 2019 once we have time / need.
AND CAST(cntc_dt AS DATETIME) > CAST('2019-01-01' AS DATETIME)
ORDER BY ofndr_num, cntc_dt
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_id',
ingest_view_name='sprvsn_cntc',
view_query_template=VIEW_QUERY_TEMPLATE,
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
IF(period_sequence_number = 1,
parole_count_id_admission_reason, 'TRANSFER_WITHIN_STATE') AS admission_reason,
start_date,
IF(period_sequence_number_reverse = 1 AND termination_date IS NOT NULL,
parole_count_id_termination_reason,
IF(termination_date IS NOT NULL, 'TRANSFER_WITHIN_STATE', NULL)) AS termination_reason,
termination_date,
county_of_residence,
district_office,
district_sub_office_id,
supervision_level,
supervising_officer_name,
condition_codes,
case_types_list
FROM
supervision_periods_date_filtered
)
SELECT *
FROM supervision_periods
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_pa',
ingest_view_name='supervision_period',
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols='parole_number ASC, parole_count_id ASC')
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# 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 for supervision sentences."""
from recidiviz.ingest.direct.controllers.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 GAE_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = sentence_view_template(sentence_type=SentenceType.SUPERVISION)
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_id',
ingest_view_name='mittimus_judge_sentence_offense_sentprob_supervision_sentences',
view_query_template=VIEW_QUERY_TEMPLATE
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
DirectIngestPreProcessedIngestViewBuilder
from recidiviz.utils.environment import GAE_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(GAE_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# (at your option) any later version.
#
# 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 for early discharges of supervision sentences."""
from recidiviz.ingest.direct.controllers.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 GAE_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_QUERY_TEMPLATE = early_discharge_view_template(
discharge_type=EarlyDischargeType.SUPERVISION)
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_id',
ingest_view_name='early_discharge_supervision_sentence',
view_query_template=VIEW_QUERY_TEMPLATE,
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
end_movement.parole_status_code AS end_parole_status_code,
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.control_number = end_movement.control_number
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='incarceration_period',
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols='control_number, sequence_number',
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# 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.controllers.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_v2',
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()
FROM dbo_Offender_generated_view offender
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,
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# 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 information."""
from recidiviz.ingest.direct.controllers.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 {docstars_offenders}
"""
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_nd',
ingest_view_name='docstars_offenders',
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()
full_address.legal_address_1 AS legal_address_1,
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(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',
view_query_template=VIEW_QUERY_TEMPLATE,
order_by_cols='recidiviz_master_person_id',
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
#
# 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 supervision violation information."""
from recidiviz.ingest.direct.controllers.direct_ingest_big_query_view_types import \
DirectIngestPreProcessedIngestViewBuilder
from recidiviz.ingest.direct.regions.us_pa.ingest_views.templates_violations import generate_violation_view_query
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
VIEW_BUILDER = DirectIngestPreProcessedIngestViewBuilder(
region='us_pa',
ingest_view_name='supervision_violation',
view_query_template=generate_violation_view_query(
'violation_date', 'ViolationDate', 'parsed_violation_timestamp',
'violation_types', 'violation_code', 'V'
),
order_by_cols='parole_number ASC, parole_count_id ASC, set_id ASC'
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
VIEW_BUILDER.build_and_print()
# 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 sentence information."""
from recidiviz.ingest.direct.controllers.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()
WHERE seq_rank_among_inmate_numbers = 1
)
SELECT
COALESCE(ids.control_number, m.control_number) AS control_number,
spans.mov_cur_inmt_num AS inmate_number, m.* EXCEPT (control_number)
FROM
{dbo_Miscon} m
LEFT OUTER JOIN
inmate_number_time_spans spans
ON
m.control_number = spans.mov_cnt_num
AND m.misconduct_date >= spans.inmate_num_lower_bound_date_inclusive
AND (spans.inmate_num_upper_bound_date_exclusive IS NULL OR m.misconduct_date < spans.inmate_num_upper_bound_date_exclusive)
LEFT OUTER JOIN
-- In 20-ish cases, the control_number in dbo_Miscon does not correspond to any control number in
-- dbo_tblSearchInmateInfo. We generally want to rely on dbo_tblSearchInmateInfo, since that's the file we use to
-- ingest person id links.
(SELECT DISTINCT control_number, inmate_number FROM {dbo_tblSearchInmateInfo}) ids
ON spans.mov_cur_inmt_num = ids.inmate_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()
# along with this program. If not, see <https://www.gnu.org/licenses/>.
# =============================================================================
"""Query containing ORAS assessments information."""
from recidiviz.ingest.direct.controllers.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_v2',
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()