public static void GetUserPin()
{
if (mArgs.Count != 2)
{
Log("Usage: GetUserPin <useremail>");
return;
}
//if (!long.TryParse(mArgs[1], out long idUser)) throw new ArgumentException("userid");
using (var c = new PostgresqlDataLayer(mDbConfig).GetConn())
{
var dbUser = c.QueryFirstOrDefault <User>("SELECT * FROM users WHERE email ilike @email", new { email = mArgs[1] });
if (dbUser == null)
{
throw new Exception("Email not found");
}
var user = new User
{
Id = dbUser.Id,
Email = mArgs[1]
};
var tokenManager = new AuthTokenManager(new TokenAuthConfig());
var pin = UsersController.GetActivationPin(tokenManager, user);
Log($"Pin: {pin}");
}
}
public void LinqGroupBy()
{
var sql = "SELECT * FROM matchevents ORDER BY idMatch LIMIT 100";
using (var c = new PostgresqlDataLayer(mConfig).GetConn())
{
var events = c.Query <MatchEvent>(sql);
var eventsByMatch = events.GroupBy(e => e.IdMatch, e => e, (key, group) => new { IdMatch = key, Events = group });
}
}
public void MultiMapping2()
{
var sql = "SELECT * FROM players p JOIN users u ON p.iduser = u.id";
using (var c = new PostgresqlDataLayer(mConfig).GetConn())
{
var players = c.Query <Player, User, Player>(sql, (player, user) =>
{
player.UserData = user;
return(player);
},
new { },
splitOn: "id").AsList();
Assert.IsTrue(players.Count > 0);
}
}
public static void RandomizePlayers()
{
if (mArgs.Count != 2)
{
Log("Usage: RandomizePlayers <idTournament>");
return;
}
if (!long.TryParse(mArgs[1], out long idTournament))
{
throw new ArgumentException("idTournament");
}
using (var c = new PostgresqlDataLayer(mDbConfig).GetConn())
{
var t = c.BeginTransaction();
try
{
var players = c.Query <Player>("SELECT id, idUser FROM players p JOIN teamplayers tp ON tp.idPlayer = p.id JOIN tournamentteams tt ON tt.idteam = tp.idTeam WHERE tt.idTournament = @idTournament", new { idTournament }, t);
var sql = new StringBuilder();
foreach (var player in players)
{
var name = NameGenerator.GetRandomName();
var surname = NameGenerator.GetRandomSurname();
sql.AppendFormat("UPDATE players SET name = '{0}', surname='{1}' WHERE id = {2};\r\n", name, surname, player.Id);
sql.AppendFormat("UPDATE users SET name = '{0}' WHERE id = {1};\r\n", name + " " + surname, player.IdUser);
}
var query = sql.ToString();
c.Execute(query, t);
t.Commit();
}
catch
{
t.Rollback();
throw;
}
}
}
public void MultiMapping3()
{
var sql = "SELECT p.*, email, mobile, avatarimgurl, status FROM players p JOIN teamplayers t ON t.idplayer = p.id JOIN users u ON p.iduser = u.id WHERE t.idteam = @idteam";
using (var c = new PostgresqlDataLayer(mConfig).GetConn())
{
var players = c.Query <Player, User, TeamPlayer, Player>(
sql,
(player, user, teamPlayer) =>
{
player.UserData = user;
player.TeamData = teamPlayer;
return(player);
},
new { idteam = 9 },
splitOn: "email, status").AsList();
Assert.IsTrue(players.Count > 0);
}
}
public static void RandomizeTeams()
{
if (mArgs.Count != 2)
{
Log("Usage: RandomizeTeams <idTournament>");
return;
}
if (!long.TryParse(mArgs[1], out long idTournament))
{
throw new ArgumentException("idTournament");
}
using (var c = new PostgresqlDataLayer(mDbConfig).GetConn())
{
var t = c.BeginTransaction();
try
{
var teams = c.Query <Team>("SELECT id, name FROM teams t JOIN tournamentteams tt ON tt.idTeam = t.id WHERE tt.idTournament = @idTournament", new { idTournament }, t);
var sql = new StringBuilder();
foreach (var team in teams)
{
sql.AppendFormat("UPDATE teams SET name = '{0}', logoImgUrl = '' WHERE id = {1};\r\n", NameGenerator.GetTeamName(team.Id), team.Id);
}
var query = sql.ToString();
c.Execute(query, t);
t.Commit();
}
catch
{
t.Rollback();
throw;
}
}
}
// __ Commands ________________________________________________________
public static void CreateDb()
{
if (mArgs.Count <= 1)
{
Log("Usage: CreateDb <org|global> [drop]");
return;
}
bool drop = (mArgs.Count == 3 && mArgs[2].ToLower() == "drop");
switch (mArgs[1].ToLower())
{
case "org":
mDbConfig.DeleteExistingDb = drop;
mDbConfig.AdminUser = mAdminDbConfig.AdminUser;
mDbConfig.AdminPassword = mAdminDbConfig.AdminPassword;
Log("Creating organization database: " + mDbConfig.DatabaseName);
var dl = new PostgresqlDataLayer(mDbConfig);
dl.CreateOrgDb();
break;
case "global":
var config = GetGlobalDbConfig();
config.DeleteExistingDb = drop;
config.AdminUser = mAdminDbConfig.AdminUser;
config.AdminPassword = mAdminDbConfig.AdminPassword;
Log("Creating global database: " + config.DatabaseName);
var dl2 = new PostgresqlDataLayer(config);
dl2.CreateGlobalDb();
break;
default:
throw new Exception("Unknown option " + mArgs[1]);
}
}