exec hwe.drop_view "trial_list"
go
print 'creating view trial_list'
go
create view trial_list AS
select distinct c.id
, c.file_number_ as file_number
, c.name_ as case_name
, case when cd.s$organization_ is null then
		(select case when pn.last_name_ is null then '' else coalesce(pn.last_name_,'')+' '+isnull(pv_sufix.string_value,'')+', '+coalesce(pn.first_name_,'')+' '+coalesce(pn.middle_name_,'') end as defendant_name from
		report.person_ prep join report.person_name_ pn on prep.primary_name_=pn.id
		left join picklist_value pv_sufix on pv_sufix.id=pn.suffix_
		where prep.id=cd.s$person_)
	else
		(select o.name_ from report.organization_ o where o.id=cd.s$organization_)
	end as defendant_name
, cd.id as defendant_id
, coalesce(cd.s$person_ ,cd.s$organization_) as defendant_person_id
, case when pn.last_name_ is null then '' else isnull(pn.last_name_,'')+' '+isnull(pv_sufix.string_value,'')+', '+isnull(pn.first_name_,'')+' '+isnull(pn.middle_name_,'') end as assigned_attorney
, case when opc.s$organization_ is null then
		(select coalesce(pn.last_name_,'')+','+coalesce(pn.first_name_,'')+' '+coalesce(pn.middle_name_,'') from
		report.person_ prep join report.person_name_ pn on prep.primary_name_=pn.id
		where prep.id=opc.s$person_)
	else
		(select o.name_ from report.organization_ o where o.id=opc.s$organization_)
	end as opposing_counsel_name
, cd.otn_ as otn
, p.id as assigned_attorney_id
, c.start_date_ as start_date
, c.end_date_ as end_date
, '' as disposition
, case when copn.last_name_ is null then '' else isnull(copn.last_name_,'')+' '+isnull(copv_sufix.string_value,'')+', '+isnull(copn.first_name_,'')+' '+isnull(copn.middle_name_,'') end as arresting_officer
, hwe.le_get_section_of_max_charge_only_description(cd.id) as violation
, hwe.le_get_section_of_max_charge(cd.id) as grade
, case when pv_cust.string_value='In Custody' then cust_loc.name else pv_cust.string_value end as jail_bail
, c.description_ as offer
, '' as ada_notes
, cc.court_number_ as court_number
, (select top 1 app.start_date from schedule_appearance app where app.reason like '%arr%' and defendant_id=cd.id)
 as ARRAIGNMENT_DATE
 , (select top 1 app.start_date from schedule_appearance app where (app.reason like 'Pretrial%' or app.reason like '%PTC%') and defendant_id=cd.id)
 as PTC_DATE
 , (select top 1 app.start_date from schedule_appearance app where app.reason like '%Call%' and defendant_id=cd.id)
 as CALL_DATE
  , (select top 1 app.start_date from schedule_appearance app where app.reason like 'TRIAL%' and defendant_id=cd.id)
 as TRIAL_MONTH
,
(
(select top 1 e.start_date_
from report.event_ e
join report.case_to_event_ ce on ce.s$event_=e.id
where e.type_ = (select pv.id from picklist_value pv
where pv.string_value='RULE600' and pv.picklist_id = (select p.id from picklist p where p.name='EventType'))
and coalesce(ce.t$collection_case_,t$traffic_case_,t$delinquency_case_,t$criminal_case_,t$mental_health_matter_,t$forfeiture_case_,t$contempt_case_,t$dependency_case_,t$litigation_matter_,t$general_matter_,t$contract_matter_,t$claim_) = c.id order by e.start_date_ desc
)
) as EXPIRATION180
,
(
(select top 1 e.start_date_
from report.event_ e
join report.case_to_event_ ce on ce.s$event_=e.id
where e.type_ = (select pv.id from picklist_value pv
where pv.string_value='RULE600b' and pv.picklist_id = (select p.id from picklist p where p.name='EventType'))
and coalesce(ce.t$collection_case_,t$traffic_case_,t$delinquency_case_,t$criminal_case_,t$mental_health_matter_,t$forfeiture_case_,t$contempt_case_,t$dependency_case_,t$litigation_matter_,t$general_matter_,t$contract_matter_,t$claim_) = c.id  order by e.start_date_ desc
)
) as EXPIRATION365
from
report.criminal_case_ c
left join report.criminal_defendant_ cd on cd.t$criminal_case_=c.id
left join picklist_value pv_cust on pv_cust.id=cd.custody_status_
left join organization cust_loc on cust_loc.id=cd.custody_location_
left join report.defender_assignment_ da on da.t$criminal_defendant_=cd.id and da.is_primary_=1 and da.assignment_end_date_ is null
left join report.person_ p on da.s$person_=p.id
left join report.person_name_ pn on pn.id=p.primary_name_
left join picklist_value pv_sufix on pv_sufix.id=pn.suffix_
left join report.case_officer_ co on co.t$criminal_case_=c.id and co.is_arresting_ =1
left join report.person_ cop on co.s$person_=cop.id
left join report.person_name_ copn on copn.id=cop.primary_name_
left join picklist_value copv_sufix on copv_sufix.id=copn.suffix_
left join report.opposing_counsel_ opc on opc.t$criminal_case_ is not null and opc.t$criminal_case_ = c.id and opc.id = (select max(id) from
report.opposing_counsel_ r2 where r2.t$criminal_case_ is not null and r2.t$criminal_case_ = c.id and r2.end_assign_date_ is null)
left join report.case_court_ cc on cc.t$criminal_case_ = c.id and cc.is_current_court_=1
go