Online Game Sheets

prepare( “select hash from wp_mysl_teams where (id = %d or id = %d) and hash = %s LIMIT 1”,$homeID,$awayID, $_GET[‘validated’] );

$validated = $wpdb->get_var( $qry );
}

if( isset($gameID) )
{
$singlerow = $wpdb->get_row( $wpdb->prepare(“select LM.home_team, LM.away_team, LM.league_id,LM.date,Home.title as ‘homeTitle’,Away.title as ‘awayTitle’,LL.id as ‘divisionID’,LL.title as ‘divisionTitle’,LM.season as ‘season’,LM.location,mf.fieldName, mf.streetAddress, mf.city from 4Ao_leaguemanager_matches LM join 4Ao_leaguemanager_teams Home on LM.home_team = Home.id join 4Ao_leaguemanager_teams Away on LM.away_team = Away.id join 4Ao_leaguemanager_leagues LL on LL.id = LM.league_id join wp_mysl_matches mm on LM.id = mm.id left outer join wp_mysl_fields mf on mm.fieldID = mf.id where LM.id=%d”,$gameID), ARRAY_A, 0 );

$homeID = $singlerow[home_team];
$awayID = $singlerow[away_team];
$leagueID = $singlerow[league_id];
$gameDate = $singlerow[date];

$homeTitle = $singlerow[homeTitle];
$awayTitle = $singlerow[awayTitle];
$divisionTitle = $singlerow[divisionTitle];
$divisionID = $singlerow[divisionID];
$season = $singlerow[season];

$gameLocation = $singlerow[fieldName].’ ‘.$singlerow[location].’ (‘.$singlerow[streetAddress].’; ‘.$singlerow[city].’)’;

$redirectURL = ‘http://michigansoccer.com/schedulesandstandings/?league_id=’.$divisionID.’&season=’.str_replace(‘ ‘,’%20’,$season);

$qry = $wpdb->prepare(“select max(pm1.post_id) from 4Ao_postmeta pm1 join 4Ao_postmeta pm2 on pm1.post_id = pm2.post_id WHERE pm1.meta_key = ‘entry_form’ and pm1.meta_value = ‘form_5b819c06494fd’ and pm2.meta_key = ‘game_id’ and pm2.meta_value = %d”,$gameID);

$postID = $wpdb->get_var($qry);

$qry = $wpdb->prepare(“select max(pm1.post_id) from 4Ao_postmeta pm1 join 4Ao_postmeta pm2 on pm1.post_id = pm2.post_id WHERE pm1.meta_key = ‘entry_form’ and pm1.meta_value = ‘form_5bc38a0bdd487’ and pm2.meta_key = ‘game_info_game_id’ and pm2.meta_value = %d”,$gameID);

$checkInID = $wpdb->get_var($qry);

}

if($_POST[‘validateBTN’])
{
$qry = $wpdb->prepare( “select hash from wp_mysl_teams where (id = %d or id = %d) and teamPIN = %d LIMIT 1”,$homeID,$awayID, $_POST[‘teamPIN’] );

$validated = $wpdb->get_var( $qry );

if(!$validated)
{
echo(“I’m sorry, that number does not check out. If you are repeatedly unable to validate, please contact your Club representative who can lookup your code.”);
}
}

if(af_has_submission())
{
echo(“Hey! A submission”);

}

if ($_POST[‘validated’] || $validated || is_user_logged_in()) //allow a bypass for if MYSL staff are logged in to the site.
{

if($postID) //we found a previous submission, lets load those answers
{
//get the home roster:
$qry = $wpdb->prepare(“select meta_key,meta_value from 4Ao_postmeta where post_id = %d and meta_key like ‘%home_team_roster%'”,$postID);

$homeRoster = $wpdb->get_results($qry,ARRAY_A);

$x = 0;
while(array_search(“_home_team_roster_”.$x.”_home_team_name”,array_column($homeRoster,’meta_key’)))
{
$key = array_search(“_home_team_roster_”.$x.”_home_team_name”,array_column($homeRoster,’meta_key’));
$fullNameFieldName = $homeRoster[$key][meta_value];

$key = array_search(“home_team_roster_”.$x.”_home_team_name”,array_column($homeRoster,’meta_key’));
$fullNameValue = $homeRoster[$key][meta_value];

$key = array_search(“_home_team_roster_”.$x.”_home_team_number”,array_column($homeRoster,’meta_key’));
$uniformNumberFieldName = $homeRoster[$key][meta_value];

$key = array_search(“home_team_roster_”.$x.”_home_team_number”,array_column($homeRoster,’meta_key’));
$uniformNumberValue = $homeRoster[$key][meta_value];

$key = array_search(“_home_team_roster_”.$x.”_home_attendance”,array_column($homeRoster,’meta_key’));
$attendenceFieldName = $homeRoster[$key][meta_value];

$key = array_search(“home_team_roster_”.$x.”_home_attendance”,array_column($homeRoster,’meta_key’));
$attendenceValue = $homeRoster[$key][meta_value];

$key = array_search(“_home_team_roster_”.$x.”_home_reason_for_sitting_out”,array_column($homeRoster,’meta_key’));
$reasonFieldName = $homeRoster[$key][meta_value];

$key = array_search(“home_team_roster_”.$x.”_home_reason_for_sitting_out”,array_column($homeRoster,’meta_key’));
$reasonValue = $homeRoster[$key][meta_value];

$key = array_search(“_home_team_roster_”.$x.”_home_goals_scored”,array_column($homeRoster,’meta_key’));
$goalsFieldName = $homeRoster[$key][meta_value];

$key = array_search(“home_team_roster_”.$x.”_home_goals_scored”,array_column($homeRoster,’meta_key’));
$goalsValue = $homeRoster[$key][meta_value];

$key = array_search(“_home_team_roster_”.$x.”_caution_number_home”,array_column($homeRoster,’meta_key’));
$cautionFieldName = $homeRoster[$key][meta_value];

$key = array_search(“home_team_roster_”.$x.”_caution_number_home”,array_column($homeRoster,’meta_key’));
$cautionValue = $homeRoster[$key][meta_value];

$key = array_search(“_home_team_roster_”.$x.”_send_off_letter_home”,array_column($homeRoster,’meta_key’));
$sendOffFieldName = $homeRoster[$key][meta_value];

$key = array_search(“home_team_roster_”.$x.”_send_off_letter_home”,array_column($homeRoster,’meta_key’));
$sendOffValue = $homeRoster[$key][meta_value];

$key = array_search(“_home_team_roster_”.$x.”_home_passcard_player”,array_column($homeRoster,’meta_key’));
$passCardFieldName = $homeRoster[$key][meta_value];

$key = array_search(“home_team_roster_”.$x.”_home_passcard_player”,array_column($homeRoster,’meta_key’));
$passCardValue = $homeRoster[$key][meta_value];

$homeTeamRoster[] = array(‘field_5b89465d1b280’ => array(
‘field_5bb16e531e4bc’ => array(
$fullNameFieldName => $fullNameValue,
$uniformNumberFieldName => $uniformNumberValue,
$goalsFieldName => $goalsValue
),
‘field_5bb1752c2c5cf’ => array(
$attendenceFieldName => $attendenceValue,
$reasonFieldName => $reasonValue,
$passcardFieldName => $passcardValue
),
$cautionFieldName => $cautionValue,
$sendOffFieldName => $sendOffValue
));
$x++;
}

if(!isset($homeTeamRoster)){
$homeTeamRoster = [];
}

$qry = $wpdb->prepare(“select meta_key,meta_value from 4Ao_postmeta where post_id = %d and meta_key like ‘%away_team_roster%'”,$postID);

$awayRoster = $wpdb->get_results($qry,ARRAY_A);

$x = 0;
while(array_search(“_away_team_roster_”.$x.”_away_team_name”,array_column($awayRoster,’meta_key’)))
{
$key = array_search(“_away_team_roster_”.$x.”_away_team_name”,array_column($awayRoster,’meta_key’));
$fullNameFieldName = $awayRoster[$key][meta_value];

$key = array_search(“away_team_roster_”.$x.”_away_team_name”,array_column($awayRoster,’meta_key’));
$fullNameValue = $awayRoster[$key][meta_value];

$key = array_search(“_away_team_roster_”.$x.”_away_team_number”,array_column($awayRoster,’meta_key’));
$uniformNumberFieldName = $awayRoster[$key][meta_value];

$key = array_search(“away_team_roster_”.$x.”_away_team_number”,array_column($awayRoster,’meta_key’));
$uniformNumberValue = $awayRoster[$key][meta_value];

$key = array_search(“_away_team_roster_”.$x.”_away_attendance”,array_column($awayRoster,’meta_key’));
$attendenceFieldName = $awayRoster[$key][meta_value];

$key = array_search(“away_team_roster_”.$x.”_away_attendance”,array_column($awayRoster,’meta_key’));
$attendenceValue = $awayRoster[$key][meta_value];

$key = array_search(“_away_team_roster_”.$x.”_away_reason_for_sitting_out”,array_column($awayRoster,’meta_key’));
$reasonFieldName = $awayRoster[$key][meta_value];

$key = array_search(“away_team_roster_”.$x.”_away_reason_for_sitting_out”,array_column($awayRoster,’meta_key’));
$reasonValue = $awayRoster[$key][meta_value];

$key = array_search(“_away_team_roster_”.$x.”_away_goals_scored”,array_column($awayRoster,’meta_key’));
$goalsFieldName = $awayRoster[$key][meta_value];

$key = array_search(“away_team_roster_”.$x.”_away_goals_scored”,array_column($awayRoster,’meta_key’));
$goalsValue = $awayRoster[$key][meta_value];

$key = array_search(“_away_team_roster_”.$x.”_caution_number_away”,array_column($awayRoster,’meta_key’));
$cautionFieldName = $awayRoster[$key][meta_value];

$key = array_search(“away_team_roster_”.$x.”_caution_number_away”,array_column($awayRoster,’meta_key’));
$cautionValue = $awayRoster[$key][meta_value];

$key = array_search(“_away_team_roster_”.$x.”_send_off_letter_away”,array_column($awayRoster,’meta_key’));
$sendOffFieldName = $awayRoster[$key][meta_value];

$key = array_search(“away_team_roster_”.$x.”_send_off_letter_away”,array_column($awayRoster,’meta_key’));
$sendOffValue = $awayRoster[$key][meta_value];

$key = array_search(“_away_team_roster_”.$x.”_away_passcard_player”,array_column($awayRoster,’meta_key’));
$passCardFieldName = $awayRoster[$key][meta_value];

$key = array_search(“away_team_roster_”.$x.”_away_passcard_player”,array_column($awayRoster,’meta_key’));
$passCardValue = $awayRoster[$key][meta_value];

$awayTeamRoster[] = array(‘field_5bb17636be8c6’ => array(
‘field_5bb17636be8c7’ => array(
$fullNameFieldName => $fullNameValue,
$uniformNumberFieldName => $uniformNumberValue,
$goalsFieldName => $goalsValue
),
‘field_5bb17636be8cb’ => array(
$attendenceFieldName => $attendenceValue,
$reasonFieldName => $reasonValue,
$passcardFieldName => $passcardValue
),
$cautionFieldName => $cautionValue,
$sendOffFieldName => $sendOffValue
));
$x++;
}

if(!isset($awayTeamRoster)){
$awayTeamRoster = [];
}

$qry = $wpdb->prepare(“select meta_key,meta_value from 4Ao_postmeta where post_id = %d and meta_key not like ‘%team_roster%'”,$postID);

$nonRosterData = $wpdb->get_results($qry,ARRAY_A);

$values = array(
‘game_date’ => $nonRosterData[array_search(“game_date”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba83526e9896’ => array(
‘field_5ba835c2e9897′ => $nonRosterData[array_search(“game_number”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba835dce9898′ => $nonRosterData[array_search(“playing_division”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba83d52bb892′ => $nonRosterData[array_search(“game_location”,array_column($nonRosterData,’meta_key’))][meta_value]
),
‘field_5ba83d3bbb891’ => array(
‘field_5ba83d71bb893′ => $nonRosterData[array_search(“home_team_name”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba83de6e905f’ => $nonRosterData[array_search(“home_team_goals”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba83ff844895′ => $nonRosterData[array_search(“home_team_officials_name”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba84247fbf66′ => $nonRosterData[array_search(“home_team_contact_email”,array_column($nonRosterData,’meta_key’))][meta_value]
),
‘field_5ba8400644896’ => array(
‘field_5ba8401644897′ => $nonRosterData[array_search(“away_team_name”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba8401c44898′ => $nonRosterData[array_search(“away_team_goals”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba8402444899′ => $nonRosterData[array_search(“away_team_officials_name”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba8425dfbf67′ => $nonRosterData[array_search(“away_team_contact_email”,array_column($nonRosterData,’meta_key’))][meta_value]
),
‘field_5ba83ca62a9ed’ => array(
‘field_5ba83cbb2a9ee’ => $nonRosterData[array_search(“head_referee_name”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba84292bb15e’ => $nonRosterData[array_search(“head_referee_id”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba83cc72a9ef’ => $nonRosterData[array_search(“asst_referee_1 “,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba83cde2a9f0′ => $nonRosterData[array_search(“asst_referee_2 “,array_column($nonRosterData,’meta_key’))][meta_value]
),
‘field_5ba83e9280818’ => array(
‘field_5ba83ea980819′ => $nonRosterData[array_search(“game_under_protest”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba83ec08081a’ => $nonRosterData[array_search(“were_cards_issued_to_the_home_team”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba83ed48081b’ => $nonRosterData[array_search(“were_cards_issued_to_the_away_team”,array_column($nonRosterData,’meta_key’))][meta_value]
),
‘field_5ba83f4f01542’ => array(
‘field_5ba83f5f01543′ => $nonRosterData[array_search(“referee_present”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba83f7201544′ => $nonRosterData[array_search(“game_type”,array_column($nonRosterData,’meta_key’))][meta_value],
‘field_5ba83f8601545′ => $nonRosterData[array_search(“passcards_checked”,array_column($nonRosterData,’meta_key’))][meta_value]

),
‘field_5b89465d1b280’ => $homeTeamRoster,
‘field_5bb17636be8c6’ => $awayTeamRoster

);
}
else
{

if($checkInID) //we found a check in form. Let’s bring in the rosters from there
{
$qry = $wpdb->prepare(“select meta_key,meta_value from 4Ao_postmeta where post_id = %d and meta_key like ‘%home_roster%'”,$checkInID);

$homeRoster = $wpdb->get_results($qry,ARRAY_A);

$x = 0;
while(array_search(“_home_roster_”.$x.”__home_player_name”,array_column($homeRoster,’meta_key’)))
{
$key = array_search(“home_roster_”.$x.”__home_player_name”,array_column($homeRoster,’meta_key’));
$fullNameValue = $homeRoster[$key][meta_value];

$key = array_search(“home_roster_”.$x.”__home_player_number”,array_column($homeRoster,’meta_key’));
$uniformNumberValue = $homeRoster[$key][meta_value];

$key = array_search(“home_roster_”.$x.”__home_present”,array_column($homeRoster,’meta_key’));
$presentFieldValue = $homeRoster[$key][meta_value];

$key = array_search(“home_roster_”.$x.”__home_passcard_player”,array_column($homeRoster,’meta_key’));
$passcardFieldValue = $homeRoster[$key][meta_value];

if(strcmp($presentFieldValue,’a:1:{i:0;s:3:”Yes”;}’)==0){
$presentFieldValue = “Yes”;

}
if(strcmp($presentFieldValue,’a:1:{i:0;s:3:”Yes”;}’)==0){
$passcardFieldValue = “Yes”;

}

$homeTeamRoster[] = array(
‘field_5bb16e531e4bc’ => array(
‘field_5bb16e6e1e4bd’ => $fullNameValue,
‘field_5bb16e821e4be’ => $uniformNumberValue
),
‘field_5bb1752c2c5cf’ => array(
‘field_5bb175382c5d0’ => $presentFieldValue,
‘field_5bc53971feb31’ => $passcardFieldValue
)
);
$x++;
unset($presentFieldValue);
unset($presentFieldValue);
}

if(!isset($homeTeamRoster)){
$homeTeamRoster = [];
}

$qry = $wpdb->prepare(“select meta_key,meta_value from 4Ao_postmeta where post_id = %d and meta_key like ‘%away_roster%'”,$checkInID);

$awayRoster = $wpdb->get_results($qry,ARRAY_A);

$x = 0;
while(array_search(“_away_roster_”.$x.”__away_player_name”,array_column($awayRoster,’meta_key’)))
{
$key = array_search(“away_roster_”.$x.”__away_player_name”,array_column($awayRoster,’meta_key’));
$fullNameValue = $awayRoster[$key][meta_value];

$key = array_search(“away_roster_”.$x.”__away_player_number”,array_column($awayRoster,’meta_key’));
$uniformNumberValue = $awayRoster[$key][meta_value];

$key = array_search(“away_roster_”.$x.”__away_present”,array_column($awayRoster,’meta_key’));
$presentFieldValue = $awayRoster[$key][meta_value];

$key = array_search(“away_roster_”.$x.”__away_passcard_player”,array_column($awayRoster,’meta_key’));
$passcardFieldValue = $awayRoster[$key][meta_value];

if(strcmp($presentFieldValue,’a:1:{i:0;s:3:”Yes”;}’)==0){
$presentFieldValue = “Yes”;
}
if(strcmp($passcardFieldValue,’a:1:{i:0;s:3:”Yes”;}’)==0){
$passcardFieldValue = “Yes”;
}

$awayTeamRoster[] = array(
‘field_5bb17636be8c7’ => array(
‘field_5bb17636be8c9’ => $fullNameValue,
‘field_5bb17636be8c8’ => $uniformNumberValue
),
‘field_5bb17636be8cb’ => array(
‘field_5bb17636be8cc’ => $presentFieldValue,
‘field_5bc539f4feb32’ => $passcardFieldValue
)
);
$x++;
unset($presentFieldValue);
unset($presentFieldValue);
}

if(!isset($awayTeamRoster)){
$awayTeamRoster = [];
}
}
else
{
$qry = $wpdb->prepare(“select playerNumber,playerName from wp_mysl_rosters where teamID = %d order BY playerNumber,playerName”,$homeID);

$homeRoster = $wpdb->get_results($qry);

$qry = $wpdb->prepare(“select playerNumber,playerName from wp_mysl_rosters where teamID = %d order BY playerNumber,playerName”,$awayID);

$awayRoster = $wpdb->get_results($qry);

foreach($homeRoster as $hval)
{
$homeTeamRoster[] = array(‘field_5bb16e531e4bc’ => array(‘field_5bb16e821e4be’ => $hval->playerNumber,’field_5bb16e6e1e4bd’ => $hval->playerName));
}
foreach($awayRoster as $aval)
{
$awayTeamRoster[] = array(‘field_5bb17636be8c7’ => array(‘field_5bb17636be8c8′ => $aval->playerNumber,’field_5bb17636be8c9’ => $aval->playerName));
}
}

$targetURL = ‘http://michigansoccer.com/process-online-game-sheet-submission/’;

$values = array(
‘game_date’ => $gameDate,
‘field_5ba83526e9896’ => array(
‘field_5ba835c2e9897’ => $gameID,
‘field_5ba835dce9898’ => $divisionTitle,
‘field_5ba83d52bb892’ => $gameLocation
),
‘field_5b89465d1b280’ => $homeTeamRoster,
‘field_5bb17636be8c6’ => $awayTeamRoster,
‘field_5ba83d3bbb891’ => array(
‘field_5ba83d71bb893’ => $homeTitle
),
‘field_5ba8400644896’ => array(
‘field_5ba8401644897’ => $awayTitle
)
);
}

//build the arguments array for rendering the form.
//The $values either came from the “there’s already a post” block or the “new post” block.

$formArgs = array(
// Whether the title should be displayed or not (true/false)
‘display_title’ => false,

// Whether the description should be displayed or not (true/false)
‘display_description’ => false,

// Text used for the submit button
‘submit_text’ => ‘Submit’,

// The URL to which the form points. Defaults to the current URL which will redirect and automatically display a success message after submission
// If this is overriden you may use af_has_submission to check for a form submission
//’target’ => $targetURL,

// Whether the form output should be echoed or returned
‘echo’ => true,

// Field values to pre-fill. Should be an array with format: $field_name_or_key => $field_prefill_value
‘values’ => $values,

// Array of field keys or names to exclude from form rendering
//’exclude_fields’ => array(),

// Either ‘wp’ or ‘basic’. Whether to use the WordPress media uploader or a regular file input for file/image fields.
‘uploader’ => ‘wp’,

// The URL to redirect to after a successful submission. Defaults to the current URL which will display a success message after submission.
‘redirect’ => $redirectURL,

// ID to use for form element. Defaults to form key.
‘id’ => FORM_KEY,

// Filter mode disables the success message after submission and instead displays all fields again with their submitted values.
‘filter_mode’ => false,
);

advanced_form (‘form_5b819c06494fd’,$formArgs);
}
else
{

In order to proceed to the game sheet for game number , in the division, please enter either a valid reg code (provided with the coaches packet) for either the home () or away () team.