public bool CreateOrganizationAccount(UserAccount UserAccount) { UserAccessRepository userAccRep = new UserAccessRepository(); Guid addressID = userAccRep.CreateAddress(UserAccount.Person.Organization.Address); bool status = false; if (addressID != Guid.Empty) { UserAccount.Person.Organization.Address.Id = addressID; UserAccount.Person.Organization.Phone = UserAccount.Person.MainPhone; UserAccount.Person.Organization.Fax = UserAccount.Person.MainPhone; Guid orgID = CreateOrganization(UserAccount.Person.Organization); if (orgID != Guid.Empty) { UserAccount.Person.Organization.Id = orgID; Guid personID = userAccRep.CreatePerson(UserAccount.Person, addressID); if (personID != Guid.Empty) { SqlCommand cmd = new SqlCommand("CreateUserAccount", new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString)); cmd.CommandType = CommandType.StoredProcedure; SqlParameter personIDParam = new SqlParameter("@personID", SqlDbType.UniqueIdentifier); personIDParam.Value = personID; SqlParameter userNameParam = new SqlParameter("@username", SqlDbType.VarChar, 25); userNameParam.Value = UserAccount.Username; SqlParameter roleIDParam = new SqlParameter("@roleID", SqlDbType.UniqueIdentifier); roleIDParam.Value = Guid.Parse("794c2858-17db-462c-ab13-065b8f6719bf"); //Defined in Seed Data Max, will give Third-Party Role cmd.Parameters.Add(personIDParam); cmd.Parameters.Add(userNameParam); cmd.Parameters.Add(roleIDParam); try { cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); status = true; } catch (Exception e) { } } } } return(status); }
//TODO: The Guardian1 ,and Guardian2 may use the same address with the Player, modifed it to support it public bool UpdatePlayer(PlayerApplication Player, string Coaches) { UserAccessRepository rep = new UserAccessRepository(); rep.UpdatePerson(Player.Player); rep.UpdatePerson(Player.Guardian1); rep.UpdatePerson(Player.Guardian2); SqlConnection conn = new SqlConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "UpdatePlayer"; cmd.Parameters.Add(new SqlParameter("@playerApplicationID", SqlDbType.UniqueIdentifier)).Value = Player.ID; cmd.Parameters.Add(new SqlParameter("@bcMedicalNumber", SqlDbType.VarChar, 10)).Value = Player.BCMedicalNumber; cmd.Parameters.Add(new SqlParameter("@birthDate", SqlDbType.DateTime)).Value = Player.BirthDate; cmd.Parameters.Add(new SqlParameter("@gender", SqlDbType.Char)).Value = Player.Gender; cmd.Parameters.Add(new SqlParameter("@medicalNotes", SqlDbType.VarChar, 200)).Value = (Player.MedicalAlert == null) ? DBNull.Value.ToString() : Player.MedicalAlert; cmd.Parameters.Add(new SqlParameter("@paid", SqlDbType.Bit)).Value = Player.Paid ? 1 : 0; cmd.Parameters.Add(new SqlParameter("@payment", SqlDbType.Money)).Value = Player.Payment; cmd.Parameters.Add(new SqlParameter("@previousTeam", SqlDbType.VarChar, 50)).Value = (Player.PreviousTeam == null) ? DBNull.Value.ToString() : Player.PreviousTeam; cmd.Parameters.Add(new SqlParameter("@school", SqlDbType.VarChar, 50)).Value = Player.School; if (!IsValidGUID(Coaches) || Coaches == "") { cmd.Parameters.Add(new SqlParameter("@coach_id", SqlDbType.UniqueIdentifier)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@coach_id", SqlDbType.UniqueIdentifier)).Value = Guid.Parse(Coaches); } try { cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } catch (Exception e) { cmd.Connection.Close(); return(false); } return(true); }
public Guid CreateNewTournament(Tournament Tournament) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "CreateNewTournament"; Guid TournamentID = Guid.NewGuid(); Guid RegistrationID = Guid.NewGuid(); Guid GameID = Guid.NewGuid(); UserAccessRepository user_rep = new UserAccessRepository(); Guid PersonID = user_rep.GetPersonID(System.Web.HttpContext.Current.User.Identity.Name); cmd.Parameters.Add(new SqlParameter("@tournamentId", SqlDbType.UniqueIdentifier)).Value = TournamentID; cmd.Parameters.Add(new SqlParameter("@registrationID", SqlDbType.UniqueIdentifier)).Value = RegistrationID; cmd.Parameters.Add(new SqlParameter("@gameId", SqlDbType.UniqueIdentifier)).Value = GameID; cmd.Parameters.Add(new SqlParameter("@location", SqlDbType.VarChar, 50)).Value = Tournament.Location; cmd.Parameters.Add(new SqlParameter("@createdById", SqlDbType.UniqueIdentifier)).Value = PersonID; cmd.Parameters.Add(new SqlParameter("@tournmentName", SqlDbType.VarChar, 50)).Value = Tournament.TournamentName; cmd.Parameters.Add(new SqlParameter("@tournamentInfo", SqlDbType.VarChar, 4000)).Value = Tournament.TournamentInfo; cmd.Parameters.Add(new SqlParameter("@registrationStartDate", SqlDbType.DateTime)).Value = Tournament.RegistrationStartDate; cmd.Parameters.Add(new SqlParameter("@registrationEndDate", SqlDbType.DateTime)).Value = Tournament.RegistrationEndDate; cmd.Parameters.Add(new SqlParameter("@tournamentStartDate", SqlDbType.DateTime)).Value = Tournament.TournamentStartDate; cmd.Parameters.Add(new SqlParameter("@tournamentEndDate", SqlDbType.DateTime)).Value = Tournament.TournamentEndDate; try { cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } catch (Exception e) { TournamentID = Guid.Empty; } return(TournamentID); }
/// <summary> /// Create a new organization /// </summary> /// <param name="organization"></param> /// <returns></returns> public Guid CreateOrganization(Organization Organization) { UserAccessRepository rep = new UserAccessRepository(); //rep.CreateUserAccount(UserAccount) SqlConnection conn = new SqlConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "CreateNewOrganization"; Guid organizationID = Guid.NewGuid(); cmd.Parameters.Add(new SqlParameter("@organizationID", SqlDbType.UniqueIdentifier)).Value = organizationID; cmd.Parameters.Add(new SqlParameter("@org_name", SqlDbType.VarChar, 80)).Value = Organization.Name; cmd.Parameters.Add(new SqlParameter("@registration_date", SqlDbType.DateTime)).Value = DateTime.Now; cmd.Parameters.Add(new SqlParameter("@phone", SqlDbType.VarChar, 30)).Value = Organization.Phone; cmd.Parameters.Add(new SqlParameter("@fax", SqlDbType.VarChar, 30)).Value = Organization.Fax; cmd.Parameters.Add(new SqlParameter("@url", SqlDbType.VarChar, 512)).Value = Organization.Url; cmd.Parameters.Add(new SqlParameter("@address_id", SqlDbType.UniqueIdentifier)).Value = Organization.Address.Id; try { cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } catch (Exception e) { organizationID = Guid.Empty; } return(organizationID); }
public List <Person> GetTeamPlayers(Guid TeamID) { List <Person> players = null; SqlCommand cmd = new SqlCommand("GetTeamPlayers", new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString)); SqlParameter param = new SqlParameter("@team_id", SqlDbType.UniqueIdentifier); param.Value = TeamID; cmd.Parameters.Add(param); cmd.CommandType = CommandType.StoredProcedure; try { cmd.Connection.Open(); DataTable dt = new DataTable(); SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); dataAdapter.Fill(dt); cmd.Connection.Close(); players = new List <Person>(); UserAccessRepository userRep = new UserAccessRepository(); for (int i = 0; i < dt.Rows.Count; i++) { Person person = userRep.GetPerson(Guid.Parse(dt.Rows[i]["player_id"].ToString())); players.Add(person); } } catch (Exception e) { cmd.Connection.Close(); } return(players); }
public PlayerApplication GetPlayer(Guid PlayerID) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "GetPlayerApplicationByID"; cmd.Parameters.Add(new SqlParameter("@playerAppID", SqlDbType.UniqueIdentifier)).Value = PlayerID; try { cmd.Connection.Open(); DataTable dt = new DataTable(); SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); dataAdapter.Fill(dt); cmd.Connection.Close(); if (dt.Rows.Count == 1) { //Player information PlayerApplication player = new PlayerApplication(PlayerID); player.BCMedicalNumber = (dt.Rows[0]["bcMedicalNumber"].ToString()); player.BirthDate = DateTime.Parse(dt.Rows[0]["birthdate"].ToString()); player.Gender = dt.Rows[0]["gender"].ToString()[0]; player.MedicalAlert = dt.Rows[0]["medicalNotes"].ToString(); player.Paid = Boolean.Parse(dt.Rows[0]["paid"].ToString()); player.Payment = Double.Parse(dt.Rows[0]["payment"].ToString()); player.PreviousTeam = dt.Rows[0]["previousTeam"].ToString(); player.School = dt.Rows[0]["school"].ToString(); UserAccessRepository rep = new UserAccessRepository(); player.Player = new Person(); player.Player = rep.GetPerson(Guid.Parse(dt.Rows[0]["player_id"].ToString())); player.Player.Address = new Address(player.Player.Address.Id); player.Player.Address = rep.GetAddress(player.Player.Address.Id); //Guardian1's information player.Guardian1 = new Person(); player.Guardian1 = rep.GetPerson(Guid.Parse(dt.Rows[0]["contact1_id"].ToString())); player.Guardian1.Address = new Address(player.Guardian1.Address.Id); player.Guardian1.Address = rep.GetAddress(player.Guardian1.Address.Id); if (Guid.Parse(dt.Rows[0]["contact2_id"].ToString()) != Guid.Empty) { player.Guardian2 = new Person(); player.Guardian2 = rep.GetPerson(Guid.Parse(dt.Rows[0]["contact2_id"].ToString())); player.Guardian2.Address = new Address(player.Guardian2.Address.Id); player.Guardian2.Address = rep.GetAddress(player.Guardian2.Address.Id); } if (Convert.IsDBNull(dt.Rows[0]["coach_id"])) { player.Coach = null; } else { Person coach = new Person(Guid.Parse(dt.Rows[0]["coach_id"].ToString())); player.Coach = coach; } return(player); } } catch (Exception e) { } return(null); }
public Guid CreatePlayerApplication(PlayerApplication PlayerApplication) { UserAccessRepository rep = new UserAccessRepository(); // Player's information Guid playerAddressID = rep.CreateAddress(PlayerApplication.Player.Address); Guid playerID = rep.CreatePerson(PlayerApplication.Player, playerAddressID); // Guardian1's information(required) Guid guardian1AddressID = rep.CreateAddress(PlayerApplication.Guardian1.Address); Guid guardian1ID = rep.CreatePerson(PlayerApplication.Guardian1, guardian1AddressID); // Guardian2's information(optional) Guid guardian2ID = Guid.Empty; if (PlayerApplication.Guardian2 != null) //If user enter the address, use the one entered { Guid guardian2AddressID = rep.CreateAddress(PlayerApplication.Guardian2.Address); guardian2ID = rep.CreatePerson(PlayerApplication.Guardian2, guardian2AddressID); } else //if not, user the player's address { Guid guardian2AddressID = rep.CreateAddress(PlayerApplication.Player.Address); guardian2ID = rep.CreatePerson(PlayerApplication.Guardian2, guardian2AddressID); } SqlConnection conn = new SqlConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "CreateNewPlayerApplication"; Guid playerApplicationID = Guid.NewGuid(); cmd.Parameters.Add(new SqlParameter("@playerApplicationID", SqlDbType.UniqueIdentifier)).Value = playerApplicationID; cmd.Parameters.Add(new SqlParameter("@bcMedicalNumber", SqlDbType.VarChar, 10)).Value = PlayerApplication.BCMedicalNumber; cmd.Parameters.Add(new SqlParameter("@registrationDate", SqlDbType.DateTime)).Value = PlayerApplication.RegistrationDate; cmd.Parameters.Add(new SqlParameter("@endDate", SqlDbType.DateTime)).Value = SqlDateTime.Null; cmd.Parameters.Add(new SqlParameter("@birthDate", SqlDbType.DateTime)).Value = PlayerApplication.BirthDate; cmd.Parameters.Add(new SqlParameter("@consentApproved", SqlDbType.Bit)).Value = PlayerApplication.ConsentApproved ? 1 : 0; cmd.Parameters.Add(new SqlParameter("@gender", SqlDbType.Char)).Value = PlayerApplication.Gender; cmd.Parameters.Add(new SqlParameter("@medicalAlert", SqlDbType.VarChar, 200)).Value = PlayerApplication.MedicalAlert; cmd.Parameters.Add(new SqlParameter("@paid", SqlDbType.Bit)).Value = 0; cmd.Parameters.Add(new SqlParameter("@payment", SqlDbType.Money)).Value = PlayerApplication.Payment; cmd.Parameters.Add(new SqlParameter("@previousTeam", SqlDbType.VarChar, 50)).Value = PlayerApplication.PreviousTeam; cmd.Parameters.Add(new SqlParameter("@school", SqlDbType.VarChar, 50)).Value = PlayerApplication.School; cmd.Parameters.Add(new SqlParameter("@playerID", SqlDbType.UniqueIdentifier)).Value = playerID; cmd.Parameters.Add(new SqlParameter("@guardian1ID", SqlDbType.UniqueIdentifier)).Value = guardian1ID; cmd.Parameters.Add(new SqlParameter("@guardian2ID", SqlDbType.UniqueIdentifier)).Value = guardian2ID; try { cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } catch (Exception e) { playerApplicationID = Guid.Empty; } return(playerApplicationID); }
public bool CreateEvent(Event Event) { bool status = false; UserAccessRepository rep = new UserAccessRepository(); SqlCommand cmd = new SqlCommand("CreateEvent", new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString)); cmd.CommandType = CommandType.StoredProcedure; Guid eventID = Guid.NewGuid(); SqlParameter idParam = new SqlParameter("@event_id", SqlDbType.UniqueIdentifier); idParam.Value = eventID; SqlParameter createdByParam = new SqlParameter("@created_by_id", SqlDbType.UniqueIdentifier); createdByParam.Value = Event.CreatedBy; SqlParameter nameParam = new SqlParameter("@name", SqlDbType.VarChar, 50); nameParam.Value = Event.Name; SqlParameter scheduledTimeParam = new SqlParameter("@scheduledTime", SqlDbType.DateTime); scheduledTimeParam.Value = Event.ScheduledDate; SqlParameter locationParam = new SqlParameter("@location", SqlDbType.VarChar, 50); locationParam.Value = Event.Location; SqlParameter eventTypeParam = new SqlParameter("@typetable_id", SqlDbType.UniqueIdentifier); eventTypeParam.Value = rep.GetTypeID("CoachEvent"); SqlParameter descriptionParam = new SqlParameter("@event_description", SqlDbType.VarChar, 4000); descriptionParam.Value = Event.Description; cmd.Parameters.Add(idParam); cmd.Parameters.Add(createdByParam); cmd.Parameters.Add(nameParam); cmd.Parameters.Add(scheduledTimeParam); cmd.Parameters.Add(locationParam); cmd.Parameters.Add(eventTypeParam); cmd.Parameters.Add(descriptionParam); try { cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); for (int i = 0; i < Event.Teams.Count; i++) { if (!CreateTeamEventAssociation(Event.Teams[i].Id, eventID)) { status = false; break; } else { status = true; } } } catch (Exception e) { cmd.Connection.Close(); status = false; } return(status); }