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);
}
