public IActionResult GetDetailedSearchAnalyticsForDate([FromQuery] string websiteId, [FromQuery] int year, [FromQuery] int month, [FromQuery] int day)
{
try
{
if (string.IsNullOrWhiteSpace(websiteId))
{
return(BadRequest());
}
DateTime dt;
try
{
dt = new DateTime(year, month, day);
}
catch (Exception)
{
return(BadRequest());
}
List <DetailedSearchAnalytics> detailedSearchAnalytics = MySQLConnector.GetDetailedSearchAnalytics(websiteId, dt, dt.AddDays(1));
if (detailedSearchAnalytics != null)
{
return(Ok(detailedSearchAnalytics));
}
}
catch (Exception e)
{
return(StatusCode(StatusCodes.Status500InternalServerError, e));
}
return(Ok(null));
}
public static UserAccount Create(string username, string password, bool admin)
{
string role = (admin ? "admin" : "user");
if (username.Length > 16 || string.IsNullOrEmpty(password))
{
return(null);
}
if (Find(username) != null)
{
return(null);
}
MySqlConnection connection = MySQLConnector.GetConnection();
if (connection != null)
{
MySqlCommand command = new MySqlCommand("INSERT INTO `users` VALUES(NULL, @username, @password, NOW(), NOW(), @role)", connection);
command.Prepare();
command.Parameters.AddWithValue("@username", username);
command.Parameters.AddWithValue("@password", Hash(password));
command.Parameters.AddWithValue("@role", role);
command.ExecuteReader();
connection.Close();
return(Find(username));
}
return(null);
}
public IActionResult GetDailySearchAnalytics([FromQuery] string websiteId, [FromQuery] int year, [FromQuery] int month)
{
try
{
if (string.IsNullOrWhiteSpace(websiteId))
{
return(BadRequest());
}
DateTime dt;
try
{
dt = new DateTime(year, month, 1);
}
catch (Exception)
{
return(BadRequest());
}
var searchAnalyticsDict = MySQLConnector.GetDailySearchAnalytics(new string[] { websiteId }, dt.Year, dt.Month);
if (searchAnalyticsDict?.ContainsKey(websiteId) == true)
{
if (searchAnalyticsDict[websiteId]?.Count > 0)
{
return(Ok(searchAnalyticsDict[websiteId]));
}
}
}
catch (Exception e)
{
return(StatusCode(StatusCodes.Status500InternalServerError, e));
}
return(Ok(null));
}
private void LoadDataBase()
{
MySqlConnection connection = MySQLConnector.Connect();
MySqlDataAdapter tableSource = new MySqlDataAdapter("SELECT * FROM " + currentTableName, connection);
bindingDataTable = new DataTable();
tableSource.Fill(bindingDataTable);
MySqlDataAdapter tableInformation = new MySqlDataAdapter("DESCRIBE " + currentTableName, connection);
//コンボボックスを挿入する際に以前のものを消さなければ挿入される場所がおかしくなる
WordDataGrid.Columns.Clear();
WordDataGrid.DataSource = bindingDataTable;
DataTable tableInformationTable = new DataTable();
tableInformation.Fill(tableInformationTable);
foreach (var dataRow in tableInformationTable.Rows.Cast <DataRow>())
{
CreateEnumComboBox(dataRow);
}
WordDataGrid.Columns[0].ReadOnly = true;
FetchTableAutoIncrement();
}
private void button1_Click_1(object sender, EventArgs e)
{
this.login.Role = "manager";
if (this.login.isValid() == true)
{
MySQLConnector mySQLConnector = new MySQLConnector();
if (mySQLConnector.isMangerExist(login) == true)
{
MessageBox.Show("Username already exists!!");
}
else
{
try
{
mySQLConnector.addNewManager(this.login);
MessageBox.Show("Manegar created successfully!!");
clearFields();
}
catch (Exception ex)
{
MessageBox.Show("Failed to add manager!! An error occured!!");
}
}
}
else
{
MessageBox.Show("Invalid input!!");
}
}
public void Initialise(MySQLConnector conn)
{
if (!TableDefinition.Exists(conn))
{
ProgramLog.Admin.Log("Group table does not exist and will now be created");
TableDefinition.Create(conn);
}
}
public void Initialise(MySQLConnector conn)
{
if (!TableDefinition.Exists(conn))
{
ProgramLog.Admin.Log("Permission group table does not exist and will now be created");
TableDefinition.Create(conn);
}
}
public static Dictionary <string, string> GetRaceInfo(int id)
{
// id, name, creator, type, number of checkpoints
Dictionary <string, string> results = new Dictionary <string, string>();
Dictionary <string, string> row;
MySQLConnector mySQLConnector = MySQLConnector.Instance();
mySQLConnector = MySQLConnector.Instance();
Dictionary <string, object> param = new Dictionary <string, object>
{
{ "@id", id }
};
// On récupère l'id, le nom et le nom du créateur
mySQLConnector.OpenReader("SELECT race_id, race_name, race_creator FROM races WHERE race_id = @id", param);
row = mySQLConnector.GetNextRow();
foreach (KeyValuePair <string, string> kvp in row)
{
results.Add(MySQLConnector.Field.GetFieldName(kvp.Key), kvp.Value);
}
mySQLConnector.CloseReader();
// On récupère tous les checkpoints de la course
mySQLConnector.OpenReader("SELECT checkpoint_id, checkpoint_number, checkpoint_pos_x, checkpoint_pos_y, checkpoint_pos_z " +
"FROM race_checkpoints WHERE race_id = @id", param);
int nbrOfCheckpoints = 0;
row = mySQLConnector.GetNextRow();
Vector3 firstCheckpointPos = new Vector3();
while (row.Count > 0)
{
nbrOfCheckpoints++;
if (row["checkpoint_number"] == "0")
{
firstCheckpointPos = new Vector3(
(float)Convert.ToDouble(row["checkpoint_pos_x"]),
(float)Convert.ToDouble(row["checkpoint_pos_y"]),
(float)Convert.ToDouble(row["checkpoint_pos_z"])
);
}
row = mySQLConnector.GetNextRow();
}
results.Add("Nombre de checkpoints", nbrOfCheckpoints.ToString());
mySQLConnector.CloseReader();
// On récupère la zone du premier checkpoint
string zoneStr = "";
Zone zone = new Zone();
zoneStr = zone.GetZoneName(firstCheckpointPos);
results.Add("Zone", zoneStr);
return(results);
}
public static bool Create(MySQLConnector conn)
{
using (var bl = new MySQLQueryBuilder(SqlPermissions.SQLSafeName))
{
bl.TableCreate(TableName, Columns);
return ((IDataConnector)conn).ExecuteNonQuery(bl) > 0;
}
}
public static bool Exists(MySQLConnector conn)
{
using (var bl = new MySQLQueryBuilder(SqlPermissions.SQLSafeName))
{
bl.TableExists(TableName);
return ((IDataConnector)conn).Execute(bl);
}
}
static void Main()
{
Application.SetHighDpiMode(HighDpiMode.SystemAware);
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
MySQLConnector mySQLConnector = new MySQLConnector();
Application.Run(new LoginForm());
}
public static void Main(string[] args)
{
Database = new MySQLConnector();
Database.Connect();
CreateHostBuilder(args).Build().Run();
Database.Connection.Close();
}
public static bool Exists(MySQLConnector conn)
{
using (var bl = new MySQLQueryBuilder(SqlPermissions.SQLSafeName))
{
bl.TableExists(TableName);
return(((IDataConnector)conn).Execute(bl));
}
}
public static bool Create(MySQLConnector conn)
{
using (var bl = new MySQLQueryBuilder(SqlPermissions.SQLSafeName))
{
bl.TableCreate(TableName, Columns);
return(((IDataConnector)conn).ExecuteNonQuery(bl) > 0);
}
}
public static long Insert(MySQLConnector conn, string node, bool deny)
{
using (var bl = new MySQLQueryBuilder(SqlPermissions.SQLSafeName))
{
bl.InsertInto(TableDefinition.TableName,
new DataParameter(TableDefinition.ColumnNames.Node, node),
new DataParameter(TableDefinition.ColumnNames.Deny, deny)
);
return ((IDataConnector)conn).ExecuteInsert(bl);
}
}
public static long Insert(MySQLConnector conn, string node, bool deny)
{
using (var bl = new MySQLQueryBuilder(SqlPermissions.SQLSafeName))
{
bl.InsertInto(TableDefinition.TableName,
new DataParameter(TableDefinition.ColumnNames.Node, node),
new DataParameter(TableDefinition.ColumnNames.Deny, deny)
);
return(((IDataConnector)conn).ExecuteInsert(bl));
}
}
public WebHandler(MinecraftHandler mc, TcpTunnelServer tunnel)
{
this.tunnel = tunnel;
this.mc = mc;
string host = mc.Config.DatabaseHost;
string user = mc.Config.DatabaseUser;
string pass = mc.Config.DatabasePassword;
string database = mc.Config.Database;
int port = mc.Config.DatabasePort;
sql = MySQLConnector.GetInstance();
zma = new ZmaSQLConnection();
}
private void Write_Click(object sender, EventArgs e)
{
for (var rowIndex = 0; rowIndex < bindingDataTable.Rows.Count; ++rowIndex)
{
for (var columnIndex = 1; columnIndex < bindingDataTable.Columns.Count; ++columnIndex)
{
bindingDataTable.Rows[rowIndex][columnIndex] = WordDataGrid.Rows[rowIndex].Cells[columnIndex].Value;
}
}
MySqlConnection connection = MySQLConnector.Connect();
foreach (var dataRow in bindingDataTable.Rows.Cast <DataRow>())
{
MySqlCommand cmd = null;
switch (dataRow.RowState)
{
case DataRowState.Unchanged:
break;
case DataRowState.Deleted:
break;
case DataRowState.Modified:
cmd = new MySqlCommand(ModifyRow(dataRow), connection);
break;
case DataRowState.Added:
cmd = new MySqlCommand(AddRow(dataRow), connection);
break;
default:
break;
}
if (cmd == null)
{
continue;
}
cmd.ExecuteNonQuery();
}
LoadDataBase();
}
private void btTestConnection_Click(object sender, EventArgs e)
{
MySQLConnector con = MySQLConnector.GetInstance();
if (con.Open(mc.Config.DatabaseHost, mc.Config.Database, mc.Config.DatabaseUser, mc.Config.DatabasePassword))
{
MessageBox.Show("Connection successfull");
}
else
{
MessageBox.Show("Connection failed");
}
con.Close();
}
public IActionResult Quotes()
{
ViewData["Message"] = " All Quotes";
List <Dictionary <string, object> > AllQuotes = MySQLConnector.Query("SELECT * FROM quotes ORDER BY created_at DESC");
ViewBag.AllQuotes = AllQuotes;
System.Console.WriteLine(AllQuotes);
// this will to seperate objects into
foreach (var item in AllQuotes)
{
System.Console.WriteLine(" POST-QUOTE:" + string.Join(",", item));
}
return(View("Quotes"));
}
public IActionResult SubmitWebsiteOwnerFeedback([FromQuery] string Id, [FromQuery] string websiteId, [FromQuery] SearchQueryFeedbackEnum feedback)
{
try
{
if (string.IsNullOrWhiteSpace(Id) || string.IsNullOrWhiteSpace(websiteId))
{
return(BadRequest());
}
return(Ok(MySQLConnector.SubmitFeedbackForSearchAnalytics(Id, websiteId, feedback.ToString())));
}
catch (Exception e)
{
return(StatusCode(StatusCodes.Status500InternalServerError, e));
}
}
public static Dictionary <string, string> FindRace(string str)
{
MySQLConnector mySQLConnector = MySQLConnector.Instance();
mySQLConnector = MySQLConnector.Instance();
Dictionary <string, object> param = new Dictionary <string, object>
{
{ "@name", str }
};
mySQLConnector.OpenReader("SELECT race_id, race_name FROM races WHERE race_name LIKE @name", param);
Dictionary <string, string> results = mySQLConnector.GetNextRow();
mySQLConnector.CloseReader();
return(results);
}
/// <summary>
/// コマンドの実行
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Execution_btn_Click(object sender, EventArgs e)
{
SyncBindingSourceWithDataGridView();
MySqlConnection connection = MySQLConnector.Connect();
for (var i = 0; i < bindingDataTable.Rows.Count; ++i)
{
MySqlCommand cmd = null;
var dataRow = bindingDataTable.Rows[i];
var deleteColumnIndex = DataTable_dgv.ColumnCount - 1;
//削除CheckBoxはbindingSource
if (Convert.ToBoolean(DataTable_dgv.Rows[i].Cells[deleteColumnIndex].Value))
{
cmd = new MySqlCommand(CreateDeleteRowCmd(dataRow), connection);
cmd?.ExecuteNonQuery();
continue;
}
switch (dataRow.RowState)
{
case DataRowState.Modified:
cmd = new MySqlCommand(CreateModifyRowCmd(dataRow), connection);
break;
case DataRowState.Added:
cmd = new MySqlCommand(CreateAddRowCmd(dataRow), connection);
break;
default:
break;
}
cmd?.ExecuteNonQuery();
}
SetAdvanceDataTable();
}
/// <summary>
/// データグリッドの列挙子をコンボボックスに変更する
/// </summary>
private void OperateEnumColumn()
{
MySqlConnection connection = MySQLConnector.Connect();
MySqlDataAdapter tableInformation = new MySqlDataAdapter("DESCRIBE " + tableName, connection);
DataTable tableInformationTable = new DataTable();
tableInformation.Fill(tableInformationTable);
foreach (var dataRow in tableInformationTable.Rows.Cast <DataRow>())
{
if (!MeansEnumuration(GetTypeString(dataRow)))
{
continue;
}
ReplaceColumnWithComboBox(dataRow);
}
}
public bool SetPassword(string password)
{
if (string.IsNullOrEmpty(password) || string.IsNullOrWhiteSpace(password) || password.Length < 6)
{
return(false);
}
MySqlConnection connection = MySQLConnector.GetConnection();
MySqlCommand updateCommand = new MySqlCommand("UPDATE `users` SET `password`=@password WHERE `id`=@id ORDER BY `id` ASC LIMIT 1", connection);
updateCommand.Prepare();
updateCommand.Parameters.AddWithValue("@password", Hash(password));
updateCommand.Parameters.AddWithValue("@id", AccountId);
updateCommand.ExecuteReader();
connection.Close();
return(true);
}
public void loadData()
{
MySQLConnector mySQLConnector = new MySQLConnector();
List <ManagerBookEntry> managerBookEntries = mySQLConnector.getAllBooks();
foreach (ManagerBookEntry managerBookEntry in managerBookEntries)
{
string[] row = new string[] {
managerBookEntry.BookId.ToString(),
managerBookEntry.BookName,
managerBookEntry.BookPublishYear.ToString(),
managerBookEntry.WriterName,
managerBookEntry.QuantityOfBook.ToString(),
managerBookEntry.CatagoryName,
managerBookEntry.AvailableBook == true ? "Yes" : "No"
};
this.dataGridView1.Rows.Add(row);
}
}
public bool SetRole(string role)
{
if (!(role.Equals("admin") || role.Equals("user")))
{
return(false);
}
MySqlConnection connection = MySQLConnector.GetConnection();
MySqlCommand updateCommand = new MySqlCommand("UPDATE `users` SET `role`=@role WHERE `id`=@id ORDER BY `id` ASC LIMIT 1", connection);
updateCommand.Prepare();
updateCommand.Parameters.AddWithValue("@role", role);
updateCommand.Parameters.AddWithValue("@id", AccountId);
updateCommand.ExecuteReader();
connection.Close();
Role = StringToRole(role);
return(true);
}
/// <summary>
/// オートインクリメントの取得
/// </summary>
private void FetchAutoIncrement()
{
MySqlConnection connection = MySQLConnector.Connect();
MySqlDataAdapter autoIncrementData = new MySqlDataAdapter(
"SELECT auto_increment FROM information_schema.TABLES WHERE table_name = " + "\'" + tableName + "\';",
connection);
DataTable autoIncrementTable = new DataTable();
autoIncrementData.Fill(autoIncrementTable);
// テーブルの要素がまだ追加されたことがなかった場合
if (autoIncrementTable.Rows[0][0].GetType() == typeof(DBNull))
{
autoIncrement = 1;
return;
}
autoIncrement = int.Parse(autoIncrementTable.Rows[0][0].ToString());
}
public IActionResult addQuote(string Name, string Quote)
{
if (Name == null || Quote == null)
{
ViewBag.Message = "Quote and name must be filled out.";
Console.WriteLine("**************" + Name + "****************");
Console.WriteLine("**************" + Quote + "****************");
return(View("Index"));
}
else
{
Console.WriteLine("******POST********" + Name + "********YEA********");
Console.WriteLine("******POST********" + Quote + "********YEA********");
string query = $"insert into quotes (name, quote) values ('{Name}', '{ Quote.Replace( " ' " , " ' ' ")}')";
System.Console.WriteLine(query);
MySQLConnector.Execute(query);
return(Redirect("Quotes"));
}
// ViewData["Message"] = "Add Quote";
}
public bool ComparePassword(string password)
{
MySqlConnection connection = MySQLConnector.GetConnection();
if (connection != null)
{
MySqlCommand command = new MySqlCommand("SELECT `password` FROM `users` WHERE `id`=@id AND `password`=@password ORDER BY `id` ASC LIMIT 1", connection);
command.Prepare();
command.Parameters.AddWithValue("@id", AccountId);
command.Parameters.AddWithValue("@password", Hash(password));
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
connection.Close();
return(true);
}
}
return(false);
}
public static UserAccount Find(string username)
{
MySqlConnection connection = MySQLConnector.GetConnection();
if (connection != null)
{
MySqlCommand command = new MySqlCommand("SELECT `id`,`username`,`created_at`,`last_login`,`role` FROM `users` WHERE `username`=@username ORDER BY `id` ASC LIMIT 1", connection);
command.Prepare();
command.Parameters.AddWithValue("@username", username);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
UserAccount account = new UserAccount(reader.GetInt32("id"), reader.GetString("username"), reader.GetString("created_at"), reader.GetString("last_login"), reader.GetString("role"));
connection.Close();
return(account);
}
}
return(null);
}
public IActionResult GetDetailedSearchAnalyticsForDateRange([FromQuery] string websiteId, [FromQuery] DateTime startDate, [FromQuery] DateTime endDate)
{
try
{
if (string.IsNullOrWhiteSpace(websiteId))
{
return(BadRequest());
}
List <DetailedSearchAnalytics> detailedSearchAnalytics = MySQLConnector.GetDetailedSearchAnalytics(websiteId, startDate, endDate);
if (detailedSearchAnalytics != null)
{
return(Ok(detailedSearchAnalytics));
}
}
catch (Exception e)
{
return(StatusCode(StatusCodes.Status500InternalServerError, e));
}
return(Ok(null));
}
public IActionResult GetDailySearchAnalytics([FromBody] GWTSearchAnalyticsRequestModel requestModel)
{
try
{
if (requestModel == null || requestModel.WebsiteIds == null || requestModel.WebsiteIds?.Count() == 0 || requestModel.Offset < 0)
{
return(BadRequest());
}
if (requestModel.Limit < 10)
{
requestModel.Limit = 10;
}
return(Ok(MySQLConnector.GetSearchAnalytics(requestModel.WebsiteIds, requestModel.Limit, requestModel.Offset)));
}
catch (Exception e)
{
return(StatusCode(StatusCodes.Status500InternalServerError, e));
}
}
public static bool Create(MySQLConnector conn)
{
try
{
using (var bl = new MySQLQueryBuilder(SqlPermissions.SQLSafeName))
{
bl.TableCreate(TableName, Columns);
((IDataConnector)conn).ExecuteNonQuery(bl);
}
//Set defaults
var pc = CommandParser.GetAvailableCommands(AccessLevel.PLAYER);
var ad = CommandParser.GetAvailableCommands(AccessLevel.OP);
var op = CommandParser.GetAvailableCommands(AccessLevel.CONSOLE); //Funny how these have now changed
CreateGroup("Guest", true, null, 255, 255, 255, pc
.Where(x => !String.IsNullOrEmpty(x.Value.Node))
.Select(x => x.Value.Node)
.Distinct()
.ToArray(), conn);
CreateGroup("Admin", false, "Guest", 240, 131, 77, ad
.Where(x => !String.IsNullOrEmpty(x.Value.Node))
.Select(x => x.Value.Node)
.Distinct()
.ToArray(), conn);
CreateGroup("Operator", false, "Admin", 77, 166, 240, op
.Where(x => !String.IsNullOrEmpty(x.Value.Node))
.Select(x => x.Value.Node)
.Distinct()
.ToArray(), conn);
return(true);
}
catch (Exception e)
{
ProgramLog.Log(e);
return(false);
}
}
public static bool Create(MySQLConnector conn)
{
try
{
using (var bl = new MySQLQueryBuilder(SqlPermissions.SQLSafeName))
{
bl.TableCreate(TableName, Columns);
((IDataConnector)conn).ExecuteNonQuery(bl);
}
//Set defaults
var pc = CommandParser.GetAvailableCommands(AccessLevel.PLAYER);
var ad = CommandParser.GetAvailableCommands(AccessLevel.OP);
var op = CommandParser.GetAvailableCommands(AccessLevel.CONSOLE); //Funny how these have now changed
CreateGroup("Guest", true, null, 255, 255, 255, pc
.Where(x => !String.IsNullOrEmpty(x.Value.Node))
.Select(x => x.Value.Node)
.Distinct()
.ToArray(), conn);
CreateGroup("Admin", false, "Guest", 240, 131, 77, ad
.Where(x => !String.IsNullOrEmpty(x.Value.Node))
.Select(x => x.Value.Node)
.Distinct()
.ToArray(), conn);
CreateGroup("Operator", false, "Admin", 77, 166, 240, op
.Where(x => !String.IsNullOrEmpty(x.Value.Node))
.Select(x => x.Value.Node)
.Distinct()
.ToArray(), conn);
return true;
}
catch (Exception e)
{
ProgramLog.Log(e);
return false;
}
}
static void CreateGroup(string name, bool guest, string parent, byte r, byte g, byte b, string[] nodes, MySQLConnector conn)
{
long id;
using (var bl = new MySQLQueryBuilder(SqlPermissions.SQLSafeName))
{
bl.InsertInto(TableName,
new DataParameter(ColumnNames.Name, name),
new DataParameter(ColumnNames.ApplyToGuests, guest),
new DataParameter(ColumnNames.Parent, parent),
new DataParameter(ColumnNames.Chat_Red, r),
new DataParameter(ColumnNames.Chat_Green, g),
new DataParameter(ColumnNames.Chat_Blue, b)
);
id = ((IDataConnector)conn).ExecuteInsert(bl);
}
foreach (var nd in nodes)
{
var nodeId = PermissionTable.Insert(conn, nd, false);
GroupPermissions.Insert(conn, id, nodeId);
}
}
