| New Table |
TAD DB Table |
SQL / Script |
activity_types |
tad_activity_types |
insert into tadjeffditoro_migration.activity_types (id, activity_type_name) select id, name from tad_activity_types |
activities |
tad_activities |
insert into tadjeffditoro_migration.activities (id, activity_type_id, activity_name, db, abbrev, individual_activity) select id, activity_type_id, name, db, abbreviation, indiv from tad_activities |
activity_genders |
tad_genders |
insert into tadjeffditoro_migration.activity_genders (id, gender) select id, name from tad_genders |
classifications |
tad_classifications |
insert into tadjeffditoro_migration.classifications (id, classification_name) select id, name from tad_classifications |
districts |
tad_districts |
insert into tadjeffditoro_migration.districts (id, district_name) select id, name from tad_districts |
subdistricts |
tad_sub_districts |
insert into tadjeffditoro_migration.subdistricts (id, subdistrict_name, district_id) select id, name, 1 from tad_sub_districts |
divisions |
tad_divisions |
insert into tadjeffditoro_migration.divisions (id, division_name) select id, name from tad_divisions |
seasons |
tad_seasons |
insert into tadjeffditoro_migration.seasons (id, season_name) select id, name from tad_seasons |
regions |
tad_regions |
insert into tadjeffditoro_migration.regions (id, region_name) select id, name from tad_regions |
entity_types |
tad_school_types |
insert into tadjeffditoro_migration.entity_types (id, entity_type_name) select id, name from tad_school_types |
facility_types |
tad_facility_types |
insert into tadjeffditoro_migration.facility_types (id, facility_type_name) select id, name from tad_facility_types |
events |
tad_event_types |
insert into tadjeffditoro_migration.events (id, event_name, activity_id, sort, is_timed, is_distance, is_points, is_relay) select tad_event_types.id, tad_event_types.name, activity_id, `order`, if(type=1,1,0), if(type=2,1,0), if(type=3,1,0), relay from tad_event_types join tad_activities on tad_activities.id = tad_event_types.activity_id |
team_types |
tad_team_types |
insert into tadjeffditoro_migration.team_types (id, team_type) select id, name from tad_team_types; insert into tadjeffditoro_migration.team_types (id,team_type) select id,name from tad_honor_types; insert into tadjeffditoro_migration.team_types
(id,team_type) values (70,'Recruiting Class') |
rounds |
tad_rounds |
insert into tadjeffditoro_migration.rounds (id, round_name, sort) select id, name, `order` from tad_rounds |
personnel_types |
tad_personnel_types |
insert into tadjeffditoro_migration.personnel_types (id, personnel_type_name) select id, name from tad_personnel_types |
job_types |
tad_job_types |
insert into tadjeffditoro_migration.job_types (id, job_type_name, sort) select id, name, rank from tad_job_types |
countries |
tad_countries |
insert into tadjeffditoro_migration.countries (id, country_name) select id, name from tad_countries |
states |
tad_states |
insert into tadjeffditoro_migration.states (id, state_name, country_id, abbreviation) select id, name, country_id, abbreviation from tad_states |
counties |
tad_counties |
insert into tadjeffditoro_migration.counties (id, county_name, state_id) select id, name, state_id from tad_counties |
cities |
tad_cities |
insert into tadjeffditoro_migration.cities (id, city_name, state_id, country_id) select id, name, if(state_id > 0,state_id,null) as state_id, country_id from tad_cities |
organization_types |
|
insert into tadjeffditoro_migration.organization_types (id, organization_type_name) VALUES (1, 'School'), (2, 'Colleges'), (3, 'Media'), (4, 'Coaches'), (5, 'Professional'), (6, 'Amateur') |
oss |
tad_oss |
insert into tadjeffditoro_migration.oss (id, organization_type_name) select id, name from tad_oss |
game_types |
tad_game_types |
insert into tadjeffditoro_migration.game_types (id,game_type_name) select id,name from tad_game_types |
game_statuses |
tad_game_status |
insert into tadjeffditoro_migration.game_statuses (id,game_status_name,not_played,abbreviation) select id,name,if(not_played=1,1,null) as not_played,if(abbreviation != '',abbreviation,null) as abbreviation from tad_game_status |
event_rounds |
tad_game_types |
insert into tadjeffditoro_migration.event_rounds (id,event_round_name) select id,name from tad_game_types WHERE id>=5 |
person_types |
tad_person_classes |
insert into tadjeffditoro_migration.person_types (id, type_name, abbrev) select id, name, abbreviation from tad_person_classes |
person_genders |
- |
insert into tadjeffditoro_migration.person_genders (id, gender_name,abbreviation) values (1, 'Male','M'), (2,'Female','F') |
contact_types |
|
insert into tadjeffditoro_migration.contact_types (id, contact_type_name) VALUES (1, 'email-office'), (2, 'email-personal'), (3, 'email-other'), (4, 'phone-office'), (5, 'phone-home'), (6, 'phone-mobile'), (7, 'fax'), (8, 'url'), (9, 'facebook'), (10, 'twitter'), (11, 'instagram'), (12, 'tiktok'), (13, 'linkedin'), (14, 'phone-alt') |
colors |
tad_colors_sanitized |
insert into tadjeffditoro_migration.colors (id, color_name) select color_id, color_name from tad_colors_sanitized |
organizations, addresses, address_to_entities, colors, color_to_entities, contacts, contacts_to_entities |
tad_orgs |
organizations.php
|
alignments |
tad_rosters, tad_col_rosters, tad_recruit_rosters, tad_honor_rosters, tad_col_honor_rosters |
INSERT INTO tadjeffditoro_migration.`alignments` (`organization_id`, `season_id`, `district_id`, `region_id`, `division_id`, `classification_id`, `sub_district_id`)
SELECT IF(`org_id`>0,`org_id`, NULL) AS `org_id`, IF(`season_id`>0,`season_id`, NULL), IF(`district_id`>0,`district_id`, NULL), IF(`region_id`>0,`region_id`, NULL), IF(`division_id`>0,`division_id`, NULL), IF(`classification_id`>0,`classification_id`, NULL), IF(`sub_district_id`>0,`sub_district_id`, NULL)
FROM (
SELECT `org_id`, `season_id`, `district_id`, `region_id`, `division_id`, `classification_id`, `sub_district_id` FROM `tad_recruit_rosters` WHERE season_id > 0 AND year > 0
union
SELECT `org_id`, `season_id`, `district_id`, `region_id`, `division_id`, `classification_id`, `sub_district_id` FROM `tad_rosters` WHERE season_id > 0 AND year > 0
union
SELECT `org_id`, `season_id`, `district_id`, `region_id`, `division_id`, `classification_id`, `sub_district_id` FROM `tad_col_rosters` WHERE season_id > 0 AND year > 0
UNION
SELECT T.org_id, `season_id`, `district_id`, `region_id`, `division_id`, `classification_id`, `sub_district_id` FROM `tad_honor_rosters` R, tad_honor_teams T WHERE
R.team_id = T.id AND R.season_id > 0 AND R.year > 0
UNION
SELECT T.org_id, `season_id`, `district_id`, `region_id`, `division_id`, `classification_id`, `sub_district_id` FROM `tad_col_honor_rosters` R, tad_col_honor_teams T WHERE
R.team_id = T.id AND R.season_id > 0 AND R.year > 0
) AS T WHERE T.`org_id` NOT IN (30); |
school_districts, addresses, address_to_entities, colors, color_to_entities, contacts, contacts_to_entities |
tad_sd |
school_districts.php
|
entities, addresses, address_to_entities, colors, color_to_entities, contacts, contacts_to_entities |
tad_schools, tad_colleges, tad_new_entities |
entities_schools.php
entities_colleges.php
entities_others.php
|
tournaments |
tad_tournaments |
INSERT INTO tadjeffditoro_migration.`tournaments` (`id`, `tournament_name`, `display_name`,`activity_id`, `activity_gender_id`, `game_type_id`, `organization_id`, `entity_id`,`school_district_id`) SELECT `id`, `name`, if(trim(`preferred_name`)!='',`preferred_name`, `name`) as `preferred_name`, `activity_id`, `gender_id`, `game_type`, if(host_oss=4,host_id, NULL) as `organization_id`, if(host_oss=1 OR host_oss=3, host_id, NULL) as `entity_id`, if(host_oss=2,host_id, NULL) as `school_district_id` FROM `tad_tournaments` where host_oss != 5 |
brackets, allignments |
tad_brackets |
brackets.php (Joining with tournaments where host_oss != 5)
|
facilities, facility_details, facility_groups |
tad_facilities, tad_facility_details, tad_facility_groups |
facilities.php
|
persons, addresses, address_to_entities, contacts, contacts_to_entities |
tad_people |
persons.php
|
teams, mascots, mascots_to_teams |
tad_teams, tad_col_teams, tad_recruit_teams, tad_honor_teams, tad_col_honor_teams |
tad_team.php
tad_col_team.php
tad_recruit_team.php
tad_honor_team.php
|
rosters |
tad_rosters, tad_col_rosters, tad_recruit_rosters, tad_honor_rosters, tad_col_honor_rosters |
insert into tadjeffditoro_migration.rosters (alignment_id, school_year, team_id, original_roster_id, original_table_name, is_champion) SELECT distinct a.id as alignment_id, t1.school_year, t1.team_id, t1.original_roster_id, 'tad_rosters' as original_table_name, t1.is_champion from (select t.id as team_id,r.id as original_roster_id, r.`year` as school_year, if(r.org_id = 0,null,r.org_id) as org_id, if(r.season_id = 0, null,r.season_id) as season_id, if(r.division_id = 0, null,r.division_id) as division_id, if(r.classification_id = 0,null,r.classification_id) as classification_id, if(r.region_id=0,null,r.region_id) as region_id, if(r.district_id = 0,null,r.district_id) as district_id, if(r.sub_district_id = 0,null, r.sub_district_id) as sub_district_id, if(r.share = 1,2,if(trim(r.place) = 'C',1,null)) as is_champion FROM tad_rosters r join tadjeffditoro_migration.teams t on (t.original_team_id = r.team_id and t.original_table_name = 'tad_teams') where r.year > 0) t1 left join tadjeffditoro_migration.alignments a on a.`organization_id` <=> t1.org_id AND a.`season_id` <=> t1.season_id AND a.`division_id` <=> t1.division_id AND a.`classification_id` <=> t1.classification_id AND a.`region_id` <=> t1.region_id AND a.`district_id` <=> t1.district_id AND a.`sub_district_id` <=> t1.sub_district_id group by a.id,team_id,school_year
insert into tadjeffditoro_migration.rosters (alignment_id, school_year, team_id, original_roster_id, original_table_name, is_champion) SELECT distinct a.id as alignment_id, t1.school_year, t1.team_id, t1.original_roster_id, 'tad_col_rosters' as original_table_name, t1.is_champion from (select t.id as team_id,r.id as original_roster_id,r.`year` as school_year, if(r.org_id = 0,null,r.org_id) as org_id,
if(r.season_id = 0, null,r.season_id) as season_id, if(r.division_id = 0, null,r.division_id) as division_id, if(r.classification_id = 0,null,r.classification_id) as classification_id, if(r.region_id=0,null,r.region_id) as region_id,if(r.district_id = 0,null,r.district_id) as district_id, if(r.sub_district_id = 0,null,r.sub_district_id) as sub_district_id, null as is_champion FROM tad_col_rosters r join tadjeffditoro_migration.teams t on (t.original_team_id = r.team_id and t.original_table_name = 'tad_col_teams') where r.year > 0) t1 left join tadjeffditoro_migration.alignments a on a.`organization_id` <=> t1.org_id AND a.`season_id` <=> t1.season_id AND a.`division_id` <=> t1.division_id AND a.`classification_id` <=> t1.classification_id AND a.`region_id` <=> t1.region_id AND a.`district_id` <=> t1.district_id AND a.`sub_district_id` <=> t1.sub_district_id
group by a.id,team_id,school_year
insert into tadjeffditoro_migration.rosters (alignment_id, school_year, team_id, original_roster_id, original_table_name, is_champion) SELECT distinct a.id as alignment_id, t1.school_year, t1.team_id, t1.original_roster_id, 'tad_recruit_rosters' as original_table_name, t1.is_champion from (select t.id as team_id,r.id as original_roster_id,r.`year` as school_year, if(r.org_id = 0,null,r.org_id) as org_id, if(r.season_id = 0, null,r.season_id) as season_id, if(r.division_id = 0, null,r.division_id) as division_id, if(r.classification_id = 0,null, r.classification_id) as classification_id, if(r.region_id=0,null,r.region_id) as region_id, if(r.district_id = 0,null,r.district_id) as district_id, if(r.sub_district_id = 0,null,r.sub_district_id) as sub_district_id, null as is_champion FROM tad_recruit_rosters r join tadjeffditoro_migration.teams t on (t.original_team_id = r.team_id and t.original_table_name = 'tad_recruit_teams') where r.year > 0) t1 left join tadjeffditoro_migration.alignments a on a.`organization_id` <=> t1.org_id AND a.`season_id` <=> t1.season_id AND a.`division_id` <=> t1.division_id AND a.`classification_id` <=> t1.classification_id AND a.`region_id` <=> t1.region_id AND a.`district_id` <=> t1.district_id AND a.`sub_district_id` <=> t1.sub_district_id where a.id is not null group by a.id,team_id,school_year
insert into tadjeffditoro_migration.rosters (alignment_id, school_year, team_id, original_roster_id, original_table_name, is_champion) SELECT distinct a.id as alignment_id, t1.school_year, t1.team_id, t1.original_roster_id, 'tad_honor_rosters' as original_table_name, t1.is_champion from (select t.id as team_id,r.id as original_roster_id, r.`year` as school_year, t.organization_id as org_id, if(r.season_id = 0, null,r.season_id) as season_id, if(r.division_id = 0, null,r.division_id) as division_id, if(r.classification_id = 0,null,r.classification_id) as classification_id, if(r.region_id=0,null,r.region_id) as region_id,if(r.district_id = 0,null,r.district_id) as district_id, if(r.sub_district_id = 0,null,r.sub_district_id) as sub_district_id, null as is_champion FROM tad_honor_rosters r join tadjeffditoro_migration.teams t on (t.original_team_id = r.team_id and t.original_table_name = 'tad_honor_teams') where r.year > 0) t1 left join tadjeffditoro_migration.alignments a on a.`organization_id` <=> t1.org_id AND a.`season_id` <=> t1.season_id AND a.`division_id` <=> t1.division_id AND a.`classification_id` <=> t1.classification_id AND a.`region_id` <=> t1.region_id AND a.`district_id` <=> t1.district_id AND a.`sub_district_id` <=> t1.sub_district_id where a.id is not null group by a.id,team_id,school_year
insert into tadjeffditoro_migration.rosters (alignment_id, school_year, team_id, original_roster_id, original_table_name, is_champion) SELECT distinct a.id as alignment_id, t1.school_year, t1.team_id, t1.original_roster_id, 'tad_col_honor_rosters' as original_table_name, t1.is_champion from (select t.id as team_id, r.id as original_roster_id, r.`year` as school_year, t.organization_id as org_id, if(r.season_id = 0, null,r.season_id) as season_id, if(r.division_id = 0, null,r.division_id) as division_id, if(r.classification_id = 0,null,r.classification_id) as classification_id, if(r.region_id=0,null,r.region_id) as region_id,if(r.district_id = 0,null,r.district_id) as district_id, if(r.sub_district_id = 0,null,r.sub_district_id) as sub_district_id, null as is_champion FROM tad_col_honor_rosters r join tadjeffditoro_migration.teams t on (t.original_team_id = r.team_id and t.original_table_name = 'tad_col_honor_teams') where r.year > 0) t1 left join tadjeffditoro_migration.alignments a on a.`organization_id` <=> t1.org_id AND a.`season_id` <=> t1.season_id AND a.`division_id` <=> t1.division_id AND a.`classification_id` <=> t1.classification_id AND a.`region_id` <=> t1.region_id AND a.`district_id` <=> t1.district_id AND a.`sub_district_id` <=> t1.sub_district_id where a.id is not null group by a.id,team_id,school_year
|
roster_people |
tad_roster_people, tad_col_roster_people, tad_recruit_roster_people, tad_honor_roster_people, tad_col_honor_roster_people |
insert into tadjeffditoro_migration.roster_people
(original_table_id,original_table_name,roster_id,person_id,personnel_type_id,job_type_id,is_letter_winner,
job_title,person_weight,person_height,position,uniform_number)
select trp.id as original_table_id,'tad_roster_people' as original_table_name,
if(r.id <=> null,null,r.id) as roster_id,if( trp.person_id > 0,trp.person_id,null) as person_id,
if(trp.personnel_type_id > 0,trp.personnel_type_id,null) as personnel_type_id,
if(trp.job_type_id > 0,trp.job_type_id,null) as job_type_id,
if(trp.letter_winner >= 1,1,0) as is_letter_winner,
if(trp.job_title!= '',trp.job_title,null) as job_title,
if(weight>0,weight,null) as weight,
if(height > 0,height,null) as height,
if(position != '',position,null) as position,
if(uniform !='',uniform,null) as uniform
from tad_roster_people as trp
join tadjeffditoro_migration.rosters as r on (trp.roster_id=r.original_roster_id
and r.original_table_name = 'tad_rosters')
order by trp.id
insert into tadjeffditoro_migration.roster_people
(original_table_id,original_table_name,roster_id,person_id,personnel_type_id,job_type_id,is_letter_winner,
job_title,person_weight,person_height,position,uniform_number)
select trp.id as original_table_id,'tad_col_roster_people' as original_table_name,
if(r.id <=> null,null,r.id) as roster_id,if( trp.person_id > 0,trp.person_id,null) as person_id,
if(trp.personnel_type_id > 0,trp.personnel_type_id,null) as personnel_type_id,
if(trp.job_type_id > 0,trp.job_type_id,null) as job_type_id,
if(trp.letter_winner >= 1,1,0) as is_letter_winner,
if(trp.job_title!= '',trp.job_title,null) as job_title,
if(weight>0,weight,null) as weight,
if(height > 0,height,null) as height,
if(position != '',position,null) as position,
if(uniform !='',uniform,null) as uniform
from tad_col_roster_people as trp
join tadjeffditoro_migration.rosters as r on (trp.roster_id=r.original_roster_id
and r.original_table_name = 'tad_col_rosters')
order by trp.id
insert into tadjeffditoro_migration.roster_people
(original_table_id,original_table_name,roster_id,person_id,personnel_type_id,job_type_id,is_letter_winner,
job_title,person_weight,person_height,position,uniform_number)
select trp.id as original_table_id,'tad_recruit_roster_people' as original_table_name,
if(r.id <=> null,null,r.id) as roster_id,if( trp.person_id > 0,trp.person_id,null) as person_id,
if(trp.personnel_type_id > 0,trp.personnel_type_id,null) as personnel_type_id,
if(trp.job_type_id > 0,trp.job_type_id,null) as job_type_id,
if(trp.letter_winner >= 1,1,0) as is_letter_winner,
if(trp.job_title!= '',trp.job_title,null) as job_title,
if(weight>0,weight,null) as weight,
if(height > 0,height,null) as height,
if(position != '',position,null) as position,
if(uniform !='',uniform,null) as uniform
from tad_recruit_roster_people as trp
join tadjeffditoro_migration.rosters as r on (trp.roster_id=r.original_roster_id
and r.original_table_name = 'tad_recruit_rosters')
order by trp.id
insert into tadjeffditoro_migration.roster_people
(original_table_id,original_table_name,roster_id,person_id,personnel_type_id,job_type_id,is_letter_winner,
job_title,person_weight,person_height,position,uniform_number,honor,entity_id)
select trp.id as original_table_id,'tad_honor_roster_people' as original_table_name,
if(r.id <=> null,null,r.id) as roster_id,if( trp.person_id > 0,trp.person_id,null) as person_id,
if(trp.personnel_type_id > 0,trp.personnel_type_id,null) as personnel_type_id,
if(trp.job_type_id > 0,trp.job_type_id,null) as job_type_id,
if(trp.letter_winner >= 1,1,0) as is_letter_winner,
if(trp.job_title!= '',trp.job_title,null) as job_title,
if(weight>0,weight,null) as weight,
if(height > 0,height,null) as height,
if(position != '',position,null) as position,
if(uniform !='',uniform,null) as uniform,
if(award != '',award,null) as honor,
t.entity_id
from tad_honor_roster_people as trp
join tadjeffditoro_migration.rosters as r on (trp.roster_id=r.original_roster_id
and r.original_table_name = 'tad_honor_rosters')
left join tadjeffditoro_migration.teams as t on (r.team_id=t.id)
group by r.id,trp.person_id,award,position
order by trp.id
insert into tadjeffditoro_migration.roster_people
(original_table_id,original_table_name,roster_id,person_id,personnel_type_id,job_type_id,is_letter_winner,
job_title,person_weight,person_height,position,uniform_number,honor,entity_id)
select trp.id as original_table_id,'tad_col_honor_roster_people' as original_table_name,
if(r.id <=> null,null,r.id) as roster_id,if( trp.person_id > 0,trp.person_id,null) as person_id,
if(trp.personnel_type_id > 0,trp.personnel_type_id,null) as personnel_type_id,
if(trp.job_type_id > 0,trp.job_type_id,null) as job_type_id,
if(trp.letter_winner >= 1,1,0) as is_letter_winner,
if(trp.job_title!= '',trp.job_title,null) as job_title,
if(weight>0,weight,null) as weight,
if(height > 0,height,null) as height,
if(position != '',position,null) as position,
if(uniform !='',uniform,null) as uniform,
if(award != '',award,null) as honor,
t.entity_id
from tad_col_honor_roster_people as trp
join tadjeffditoro_migration.rosters as r on (trp.roster_id=r.original_roster_id
and r.original_table_name = 'tad_col_honor_rosters')
left join tadjeffditoro_migration.teams as t on (r.team_id=t.id)
group by r.id,trp.person_id,award,position
order by trp.id
|
games, game_details, games_to_rosters, game_team_stats_baseball, game_team_stats_football, game_team_stats_volleyball |
tad_games_baseball, tad_games_basketball, tad_games_football, tad_games_volleyball, tad_series_baseball, tad_series_softball, tad_col_games_basketball, tad_col_games_football, tad_col_games_volleyball |
game_baseball.php
game_basketball.php
game_football.php
game_softball.php
game_volleyball.php
|