private object CommandTypeSelector(RunType runSelector)
{
try
{
switch (runSelector)
{
case RunType.Insert:
Command?.ExecuteScalar();
return(Command?.LastInsertedId);
case RunType.Normal:
case RunType.Fast:
return(Command?.ExecuteNonQuery());
default:
return(null);
}
}
catch
{
Client?.GetConnectionHandler()?.SetOpened();
return(null);
}
}
void TransactionScopeInternal(bool commit)
{
MySqlConnection c = new MySqlConnection(GetConnectionString(true));
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES ('a', 'name', 'name2')", c);
try
{
using (TransactionScope ts = new TransactionScope())
{
c.Open();
cmd.ExecuteNonQuery();
if (commit)
ts.Complete();
}
cmd.CommandText = "SELECT COUNT(*) FROM Test";
object count = cmd.ExecuteScalar();
Assert.AreEqual(commit ? 1 : 0, count);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (c != null)
{
c.Close();
}
}
}
public void ExecuteReader()
{
if (Version < new Version(5, 0)) return;
execSQL("CREATE TABLE test (id int)");
execSQL("CREATE PROCEDURE spTest() BEGIN INSERT INTO test VALUES(1); " +
"SELECT SLEEP(2); SELECT 'done'; END");
MySqlCommand proc = new MySqlCommand("spTest", conn);
proc.CommandType = CommandType.StoredProcedure;
IAsyncResult iar = proc.BeginExecuteReader();
int count = 0;
while (!iar.IsCompleted)
{
count++;
System.Threading.Thread.Sleep(20);
}
using (MySqlDataReader reader = proc.EndExecuteReader(iar))
{
Assert.IsNotNull(reader);
Assert.IsTrue(count > 0, "count > 0");
Assert.IsTrue(reader.Read(), "can read");
Assert.IsTrue(reader.NextResult());
Assert.IsTrue(reader.Read());
Assert.AreEqual("done", reader.GetString(0));
reader.Close();
proc.CommandType = CommandType.Text;
proc.CommandText = "SELECT COUNT(*) FROM test";
object cnt = proc.ExecuteScalar();
Assert.AreEqual(1, cnt);
}
}
public void ArabicCalendars()
{
execSQL("DROP TABLE IF EXISTS test");
execSQL("CREATE TABLE test(dt DATETIME)");
execSQL("INSERT INTO test VALUES ('2007-01-01 12:30:45')");
CultureInfo curCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo curUICulture = Thread.CurrentThread.CurrentUICulture;
CultureInfo c = new CultureInfo("ar-SA");
Thread.CurrentThread.CurrentCulture = c;
Thread.CurrentThread.CurrentUICulture = c;
try
{
MySqlCommand cmd = new MySqlCommand("SELECT dt FROM test", conn);
DateTime dt = (DateTime)cmd.ExecuteScalar();
Assert.AreEqual(2007, dt.Year);
Assert.AreEqual(1, dt.Month);
Assert.AreEqual(1, dt.Day);
Assert.AreEqual(12, dt.Hour);
Assert.AreEqual(30, dt.Minute);
Assert.AreEqual(45, dt.Second);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
Thread.CurrentThread.CurrentCulture = curCulture;
Thread.CurrentThread.CurrentUICulture = curUICulture;
}
public void ExecuteNonQuery()
{
if (Version < new Version(5, 0)) return;
execSQL("CREATE TABLE test (id int)");
execSQL("CREATE PROCEDURE spTest() BEGIN SET @x=0; REPEAT INSERT INTO test VALUES(@x); " +
"SET @x=@x+1; UNTIL @x = 300 END REPEAT; END");
MySqlCommand proc = new MySqlCommand("spTest", conn);
proc.CommandType = CommandType.StoredProcedure;
IAsyncResult iar = proc.BeginExecuteNonQuery();
int count = 0;
while (!iar.IsCompleted)
{
count++;
System.Threading.Thread.Sleep(20);
}
proc.EndExecuteNonQuery(iar);
Assert.IsTrue(count > 0);
proc.CommandType = CommandType.Text;
proc.CommandText = "SELECT COUNT(*) FROM test";
object cnt = proc.ExecuteScalar();
Assert.AreEqual(300, cnt);
}
public void ProcedureFromCache()
{
return;
if (Version < new Version(5, 0)) return;
execSQL("DROP PROCEDURE IF EXISTS spTest");
execSQL("CREATE PROCEDURE spTest(id int) BEGIN END");
PerformanceCounter hardQuery = new PerformanceCounter(
".NET Data Provider for MySQL", "HardProcedureQueries", true);
PerformanceCounter softQuery = new PerformanceCounter(
".NET Data Provider for MySQL", "SoftProcedureQueries", true);
long hardCount = hardQuery.RawValue;
long softCount = softQuery.RawValue;
MySqlCommand cmd = new MySqlCommand("spTest", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?id", 1);
cmd.ExecuteScalar();
Assert.AreEqual(hardCount + 1, hardQuery.RawValue);
Assert.AreEqual(softCount, softQuery.RawValue);
hardCount = hardQuery.RawValue;
MySqlCommand cmd2 = new MySqlCommand("spTest", conn);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("?id", 1);
cmd2.ExecuteScalar();
Assert.AreEqual(hardCount, hardQuery.RawValue);
Assert.AreEqual(softCount + 1, softQuery.RawValue);
}
public void CP932()
{
using (MySqlConnection c = new MySqlConnection(GetConnectionString(true) + ";charset=cp932"))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("SELECT '涯割晦叶角'", c);
string s = (string)cmd.ExecuteScalar();
Assert.AreEqual("涯割晦叶角", s);
}
}
public void UseFunctions()
{
execSQL("CREATE TABLE Test (valid char, UserCode varchar(100), password varchar(100)) CHARSET latin1");
MySqlConnection c = new MySqlConnection(conn.ConnectionString + ";charset=latin1");
c.Open();
MySqlCommand cmd = new MySqlCommand("SELECT valid FROM Test WHERE Valid = 'Y' AND " +
"UserCode = 'username' AND Password = AES_ENCRYPT('Password','abc')", c);
cmd.ExecuteScalar();
c.Close();
}
private void CommandRunner(MySqlCommand cmdToRun)
{
try
{
object o = cmdToRun.ExecuteScalar();
Assert.IsNull(o);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
}
public void Latin1Connection()
{
if (Version < new Version(4, 1)) return;
execSQL("CREATE TABLE Test (id INT, name VARCHAR(200)) CHARSET latin1");
execSQL("INSERT INTO Test VALUES( 1, _latin1 'Test')");
MySqlConnection c = new MySqlConnection(conn.ConnectionString + ";charset=latin1");
c.Open();
MySqlCommand cmd = new MySqlCommand("SELECT id FROM Test WHERE name LIKE 'Test'", c);
object id = cmd.ExecuteScalar();
Assert.AreEqual(1, id);
c.Close();
}
public void CallingStoredProcWithOnlyExecPrivs()
{
if (Version < new Version(5, 0)) return;
execSQL("CREATE PROCEDURE spTest() BEGIN SELECT 1; END");
execSQL("CREATE PROCEDURE spTest2() BEGIN SELECT 1; END");
suExecSQL("CREATE USER abc IDENTIFIED BY 'abc'");
try
{
suExecSQL(String.Format("GRANT SELECT ON `{0}`.* TO 'abc'@'%'", database0));
suExecSQL(String.Format("GRANT EXECUTE ON PROCEDURE `{0}`.spTest TO abc", database0));
string connStr = GetConnectionString("abc", "abc", true) + ";check parameters=false";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("spTest", c);
cmd.CommandType = CommandType.StoredProcedure;
object o = cmd.ExecuteScalar();
try
{
cmd.CommandText = "spTest2";
cmd.ExecuteScalar();
}
catch (MySqlException ex)
{
string s = ex.Message;
}
}
}
finally
{
suExecSQL("DROP USER abc");
}
}
public void CloseReaderAfterFailedConvert()
{
execSQL("CREATE TABLE Test (dt DATETIME)");
execSQL("INSERT INTO Test VALUES ('00-00-0000 00:00:00')");
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
try
{
cmd.ExecuteScalar();
}
catch (Exception)
{
}
conn.BeginTransaction();
}
public void LastInsertid()
{
execSQL("CREATE TABLE Test(id int auto_increment, name varchar(20), primary key(id))");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(NULL, 'test')", conn);
cmd.ExecuteNonQuery();
Assert.AreEqual(1, cmd.LastInsertedId);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
}
Assert.AreEqual(2, cmd.LastInsertedId);
cmd.CommandText = "SELECT id FROM Test";
cmd.ExecuteScalar();
Assert.AreEqual(-1, cmd.LastInsertedId);
}
public void CP932()
{
MySqlConnection c = new MySqlConnection(GetConnectionString(true) + ";charset=cp932");
c.Open();
try
{
MySqlCommand cmd = new MySqlCommand("SELECT '涯割晦叶角'", c);
string s = (string)cmd.ExecuteScalar();
Assert.AreEqual("涯割晦叶角", s);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (c != null)
c.Close();
}
}
public void RollingBackOnClose()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id INT) TYPE=InnoDB");
string connStr = GetConnectionString(true) + ";pooling=true;";
MySqlConnection c = new MySqlConnection(connStr);
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (1)", c);
c.BeginTransaction();
cmd.ExecuteNonQuery();
c.Close();
MySqlConnection c2 = new MySqlConnection(connStr);
c2.Open();
MySqlCommand cmd2 = new MySqlCommand("SELECT COUNT(*) from Test", c2);
c2.BeginTransaction();
object count = cmd2.ExecuteScalar();
c2.Close();
Assert.AreEqual(0, count);
}
public void AllowUnnamedParameters()
{
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id,name) VALUES (?id, ?name)", conn);
MySqlParameter p = new MySqlParameter();
p.ParameterName = "?id";
p.Value = 1;
cmd.Parameters.Add(p);
p = new MySqlParameter();
p.ParameterName = "?name";
p.Value = "test";
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT id FROM Test";
Assert.AreEqual(1, cmd.ExecuteScalar());
cmd.CommandText = "SELECT name FROM Test";
Assert.AreEqual("test", cmd.ExecuteScalar());
}
void TransactionScopeInternal(bool commit)
{
createTable("CREATE TABLE Test (key2 VARCHAR(1), name VARCHAR(100), name2 VARCHAR(100))", "INNODB");
using (MySqlConnection c = new MySqlConnection(GetConnectionString(true)))
{
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES ('a', 'name', 'name2')", c);
using (TransactionScope ts = new TransactionScope())
{
c.Open();
cmd.ExecuteNonQuery();
if (commit)
ts.Complete();
}
cmd.CommandText = "SELECT COUNT(*) FROM Test";
object count = cmd.ExecuteScalar();
Assert.AreEqual(commit ? 1 : 0, count);
}
}
public void ArabicCalendars()
{
execSQL("CREATE TABLE test(dt DATETIME)");
execSQL("INSERT INTO test VALUES ('2007-01-01 12:30:45')");
CultureInfo curCulture = CultureInfo.CurrentCulture;
CultureInfo curUICulture = CultureInfo.CurrentUICulture;
CultureInfo c = new CultureInfo("ar-SA");
CultureInfo.CurrentCulture = c;
CultureInfo.CurrentUICulture = c;
MySqlCommand cmd = new MySqlCommand("SELECT dt FROM test", conn);
DateTime dt = (DateTime)cmd.ExecuteScalar();
Assert.AreEqual(2007, dt.Year);
Assert.AreEqual(1, dt.Month);
Assert.AreEqual(1, dt.Day);
Assert.AreEqual(12, dt.Hour);
Assert.AreEqual(30, dt.Minute);
Assert.AreEqual(45, dt.Second);
CultureInfo.CurrentCulture = curCulture;
CultureInfo.CurrentUICulture = curUICulture;
}
public void ExecuteScriptWithProcedures()
{
if (version < new Version(5, 0)) return;
statementCount = 0;
string scriptText = String.Empty;
for (int i=0; i < 10; i++)
{
scriptText += String.Format(statementTemplate1, i, "$$");
}
MySqlScript script = new MySqlScript(scriptText);
script.StatementExecuted += new MySqlStatementExecutedEventHandler(ExecuteScriptWithProcedures_QueryExecuted);
script.Connection = conn;
script.Delimiter = "$$";
int count = script.Execute();
Assert.AreEqual(10, count);
MySqlCommand cmd = new MySqlCommand(
String.Format(@"SELECT COUNT(*) FROM information_schema.routines WHERE
routine_schema = '{0}' AND routine_name LIKE 'spTest%'",
database0), conn);
Assert.AreEqual(10, cmd.ExecuteScalar());
}
public void NoSpaceAroundEquals()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test(name VARCHAR(40))");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test SET name='test -- test';", conn);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT name FROM Test";
object o = cmd.ExecuteScalar();
Assert.AreEqual("test -- test", o);
cmd.CommandText = "UPDATE Test SET name='Can you explain this ?';";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT name FROM Test";
o = cmd.ExecuteScalar();
Assert.AreEqual("Can you explain this ?", o);
}
private void CommandRunner(MySqlCommand cmdToRun)
{
object o = cmdToRun.ExecuteScalar();
Assert.IsNull(o);
}
public void FunctionParams()
{
if (Version < new Version(5, 0)) return;
execSQL("CREATE FUNCTION fnTest( val1 INT, val2 CHAR(40) ) RETURNS INT " +
" LANGUAGE SQL DETERMINISTIC BEGIN RETURN val1 + LENGTH(val2); END");
MySqlCommand cmd = new MySqlCommand("SELECT fnTest(22, 'Test')", conn);
cmd.CommandType = CommandType.Text;
if (prepare) cmd.Prepare();
object result = cmd.ExecuteScalar();
Assert.AreEqual(26, result);
}
public override void Load()
{
if (reading)
{
try
{
MySqlConnection connection = DBUtils.GetDBConnection();
MySqlCommand mySqlCommand;
const string selectDepartmentID = "SELECT department_id FROM department WHERE short_name = @DEPARTMENT";
const string selectDisciplineID = "SELECT discipline_id FROM discipline WHERE full_name = @DISCIPLINE";
const string insertLessons = "INSERT INTO lesson (discipline_id, type, countOfHours, control, department_id) "
+ "VALUES (@DISCIPLINE_ID, @TYPE, @HOURS, @CONTROL, @DEPARTMENT_ID)";
const string selectLessonID = "SELECT lesson_id FROM lesson ORDER BY lesson_id DESC LIMIT 1";
const string selectTeacherID = "SELECT teacher_id FROM teacher WHERE full_name = @TEACHER";
const string selectAuditoryID = "SELECT auditory_id FROM auditory WHERE auditory_name = @AUDITORY";
const string insertLesson_teacher = "INSERT INTO lesson_teacher (lesson_id, teacher_id) "
+ "VALUES (@LESSON_ID, @TEACHER_ID)";
const string insertLesson_auditory = "INSERT INTO lesson_auditory (lesson_id, auditory_id) "
+ "VALUES (@LESSON_ID, @AUDITORY_ID)";
connection.Open();
mySqlCommand = new MySqlCommand(selectDepartmentID, connection);
mySqlCommand.Parameters.AddWithValue("@DEPARTMENT", "ЕМ");
mySqlCommand.ExecuteNonQuery();
int departmentID = Convert.ToInt32(mySqlCommand.ExecuteScalar().ToString());
for (int i = 0; i < disciplines.Count; i++)
{
mySqlCommand = new MySqlCommand(selectDisciplineID, connection);
mySqlCommand.Parameters.AddWithValue("@DISCIPLINE", disciplines[i]);
mySqlCommand.ExecuteNonQuery();
int disciplineID = Convert.ToInt32(mySqlCommand.ExecuteScalar().ToString());
mySqlCommand = new MySqlCommand(insertLessons, connection);
mySqlCommand.Parameters.AddWithValue("@DISCIPLINE_ID", disciplineID);
mySqlCommand.Parameters.AddWithValue("@TYPE", lessonsType[i]);
mySqlCommand.Parameters.AddWithValue("@HOURS", hours[i]);
mySqlCommand.Parameters.AddWithValue("@CONTROL", lessonsControl[i]);
mySqlCommand.Parameters.AddWithValue("@DEPARTMENT_ID", departmentID);
mySqlCommand.ExecuteNonQuery();
mySqlCommand = new MySqlCommand(selectLessonID, connection);
mySqlCommand.ExecuteNonQuery();
int lessonID = Convert.ToInt32(mySqlCommand.ExecuteScalar().ToString());
string suggestedAuditories = auditories[i].ToString();
if (suggestedAuditories.Length != 0)
{
suggestedAuditories = suggestedAuditories.TrimEnd(new char [] { ',', ';' });
suggestedAuditories = suggestedAuditories.Replace(" ", "");
string [] separatedAuditories = suggestedAuditories.Split(new char[] { ',', ';' });
for (int j = 0; j < separatedAuditories.Length; j++)
{
mySqlCommand = new MySqlCommand(selectAuditoryID, connection);
mySqlCommand.Parameters.AddWithValue("@AUDITORY", separatedAuditories[j]);
mySqlCommand.ExecuteNonQuery();
int auditoryID = Convert.ToInt32(mySqlCommand.ExecuteScalar().ToString());
mySqlCommand = new MySqlCommand(insertLesson_auditory, connection);
mySqlCommand.Parameters.AddWithValue("@LESSON_ID", lessonID);
mySqlCommand.Parameters.AddWithValue("@AUDITORY_ID", auditoryID);
mySqlCommand.ExecuteNonQuery();
}
}
string teachersRecord = teachers[i].ToString();
teachersRecord = teachersRecord.TrimEnd(new char [] { ',', ';' });
string [] separatedTeachers = teachersRecord.Split(new char[] { ',', ';' });
for (int j = 0; j < separatedTeachers.Length; j++)
{
separatedTeachers[j] = separatedTeachers[j].Trim();
mySqlCommand = new MySqlCommand(selectTeacherID, connection);
mySqlCommand.Parameters.AddWithValue("@TEACHER", separatedTeachers[j]);
mySqlCommand.ExecuteNonQuery();
int teacherID = Convert.ToInt32(mySqlCommand.ExecuteScalar().ToString());
mySqlCommand = new MySqlCommand(insertLesson_teacher, connection);
mySqlCommand.Parameters.AddWithValue("@LESSON_ID", lessonID);
mySqlCommand.Parameters.AddWithValue("@TEACHER_ID", teacherID);
mySqlCommand.ExecuteNonQuery();
}
const string selectStudyGroups = "SELECT full_name FROM study_group";
const string insertLesson_group = "INSERT INTO lesson_group (lesson_id, group_id) "
+ "VALUES (@LESSON_ID, @GROUP_ID)";
const string insertStudy_group = "INSERT INTO study_group (department_id, full_name, study_group_code) "
+ "VALUES (@DEPARTMENT_ID, @NAME, @CODE)";
const string selectGroupID = "SELECT study_group_id FROM study_group WHERE full_name = @GROUP";
string groupsInCell = groups[i].ToString();
if (groupsInCell.Length != 0)
{
groupsInCell = groupsInCell.Trim();
groupsInCell = groupsInCell.TrimEnd(new char [] { ',', ';' });
groupsInCell = groupsInCell.Replace(" ", "");
groupsInCell = groupsInCell.Replace("\n", "");
string [] separatedGroups = groupsInCell.Split(new char[] { ',', ';' });
ArrayList studyGroupsFromDB = new ArrayList();
mySqlCommand = new MySqlCommand(selectStudyGroups, connection);
using (MySqlConnection connection2 = DBUtils.GetDBConnection())
{
using (MySqlDataReader dataReader = mySqlCommand.ExecuteReader())
{
while (dataReader.Read())
{
studyGroupsFromDB.Add(dataReader[0].ToString());
}
}
}
for (int j = 0; j < separatedGroups.Length; j++)
{
if (studyGroupsFromDB.Contains(separatedGroups[j]) == false)
{
int hyphen = separatedGroups[j].IndexOf('-');
string code = separatedGroups[j].Substring(0, hyphen + 2);
mySqlCommand = new MySqlCommand(insertStudy_group, connection);
mySqlCommand.Parameters.AddWithValue("@DEPARTMENT_ID", departmentID);
mySqlCommand.Parameters.AddWithValue("@NAME", separatedGroups[j]);
mySqlCommand.Parameters.AddWithValue("@CODE", code);
mySqlCommand.ExecuteNonQuery();
}
mySqlCommand = new MySqlCommand(selectGroupID, connection);
mySqlCommand.Parameters.AddWithValue("@GROUP", separatedGroups[j]);
mySqlCommand.ExecuteNonQuery();
int groupID = Convert.ToInt32(mySqlCommand.ExecuteScalar().ToString());
mySqlCommand = new MySqlCommand(insertLesson_group, connection);
mySqlCommand.Parameters.AddWithValue("@LESSON_ID", lessonID);
mySqlCommand.Parameters.AddWithValue("@GROUP_ID", groupID);
mySqlCommand.ExecuteNonQuery();
}
}
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Виникла помилка під час завантаження відомостей доручень до бази даних!" + "\n" + ex.Message);
}
}
}
public void RefreshServer()
{
try
{
MySqlConnection MySQLConnection = CreateConnection();
MySqlCommand MySQLCommand = MySQLConnection.CreateCommand();
// check if server exists in table
MySQLCommand.CommandText = "SELECT * FROM " + Livemap.Instance.Configuration.Instance.DatabaseTableLivemapServer + " WHERE server_id = '" + Provider.serverID + "'";
MySQLConnection.Open();
object result = MySQLCommand.ExecuteScalar();
if (result == null)
{
// server does not exist, create it
MySQLCommand.CommandText = "INSERT INTO " + Livemap.Instance.Configuration.Instance.DatabaseTableLivemapServer + " (server_id,server_name,app_version,map,online_players,max_players,is_pvp,is_gold,is_pro,has_cheats,hide_admins,cycle_time,cycle_length,full_moon,uptime,packets_received,packets_sent,port,mode) VALUES (" +
"'" + Provider.serverID + "'," +
"'" + Provider.serverName + "'," +
"'" + Provider.APP_VERSION + "'," +
"'" + Provider.map + "'," +
Provider.clients.Count + "," +
Provider.maxPlayers + "," +
Convert.ToInt32(Provider.isPvP) + "," +
Convert.ToInt32(Provider.isGold) + "," +
Convert.ToInt32(Provider.isPro) + "," +
Convert.ToInt32(Provider.hasCheats) + "," +
Convert.ToInt32(Provider.hideAdmins) + "," +
LightingManager.time + "," +
LightingManager.cycle + "," +
Convert.ToInt32(LightingManager.isFullMoon) + "," +
Time.time + "," +
Provider.packetsReceived + "," +
Provider.packetsSent + "," +
Provider.port + "," +
"'" + Provider.mode.ToString() + "'" +
")";
MySQLCommand.ExecuteNonQuery();
}
else
{
// update server info
MySQLCommand.CommandText = "UPDATE " + Livemap.Instance.Configuration.Instance.DatabaseTableLivemapServer + " SET " +
"server_name = '" + Provider.serverName + "'," +
"app_version = '" + Provider.APP_VERSION + "'," +
"map = '" + Provider.map + "'," +
"online_players = " + Provider.clients.Count + "," +
"max_players = " + Provider.maxPlayers + "," +
"is_pvp = " + Convert.ToInt32(Provider.isPvP) + "," +
"is_gold = " + Convert.ToInt32(Provider.isGold) + "," +
"is_pro = " + Convert.ToInt32(Provider.isPro) + "," +
"has_cheats = " + Convert.ToInt32(Provider.hasCheats) + "," +
"hide_admins = " + Convert.ToInt32(Provider.hideAdmins) + "," +
"cycle_time = " + LightingManager.time + "," +
"cycle_length = " + LightingManager.cycle + "," +
"full_moon = " + Convert.ToInt32(LightingManager.isFullMoon) + "," +
"uptime = " + Time.time + "," +
"packets_received = " + Provider.packetsReceived + "," +
"packets_sent = " + Provider.packetsSent + "," +
"port = " + Provider.port + "," +
"mode = '" + Provider.mode.ToString() + "' " +
"WHERE server_id = '" + Provider.serverID + "'";
MySQLCommand.ExecuteNonQuery();
}
MySQLConnection.Close();
}
catch (Exception ex)
{
Logger.LogException(ex);
}
}
private void InsertKaijiangStat(KaijiangStats stat)
{
string conStr = "server=localhost;User Id=root;database=aicai;" +
"Password=root;Character Set=utf8;";
object objresult = 0;
using (MySqlConnection mySqlCon = new MySqlConnection(conStr))
{
mySqlCon.Open();
using (MySqlCommand mySqlCom = mySqlCon.CreateCommand())
{
MySqlDataAdapter da = new MySqlDataAdapter();
//判断是否存在，不存在再添加，存在则修改
string sqlStr = @"
SELECT COUNT(*) as spfcount FROM kaijiang_stats
WHERE shengspint = ?shengspint
AND PingSpInt = ?pingspint
AND FuSpint = ?fuspint ";
mySqlCom.CommandText = sqlStr;
mySqlCom.Parameters.AddWithValue("?shengspint", stat.shengspint);
mySqlCom.Parameters.AddWithValue("?pingspint", stat.pingspint);
mySqlCom.Parameters.AddWithValue("?fuspint", stat.fuspint);
objresult = mySqlCom.ExecuteScalar();
}
using (MySqlCommand mySqlCom = mySqlCon.CreateCommand())
{
//判断是否存在，不存在再添加，存在则修改
if (Convert.ToInt32(objresult) == 0)
{
string strInsert = @"INSERT INTO kaijiang_stats(shengspint,pingspint,fuspint,totalcount,shengcount,pingcount,fucount,shenglv,pinglv,fulv)
VALUES(?shengspint,?pingspint,?fuspint,?totalcount,?shengcount,?pingcount,?fucount,?shenglv,?pinglv,?fulv)";
mySqlCom.CommandText = strInsert;
mySqlCom.Parameters.AddWithValue("?shengspint", stat.shengspint);
mySqlCom.Parameters.AddWithValue("?pingspint", stat.pingspint);
mySqlCom.Parameters.AddWithValue("?fuspint", stat.fuspint);
mySqlCom.Parameters.AddWithValue("?totalcount", stat.totalcount);
mySqlCom.Parameters.AddWithValue("?shengcount", stat.shengcount);
mySqlCom.Parameters.AddWithValue("?pingcount", stat.pingcount);
mySqlCom.Parameters.AddWithValue("?fucount", stat.fucount);
mySqlCom.Parameters.AddWithValue("?shenglv", stat.shenglv);
mySqlCom.Parameters.AddWithValue("?pinglv", stat.pinglv);
mySqlCom.Parameters.AddWithValue("?fulv", stat.fulv);
}
else
{
string strUpdate = @"UPDATE kaijiang_stats
SET totalcount = ?totalcount,
shengcount = ?shengcount,
pingcount = ?pingcount,
fucount = ?fucount,
shenglv = ?shenglv,
pinglv = ?pinglv,
fulv = ?fulv
WHERE shengspint = ?shengspint,
pingspint = ?pingspint,
fuspint = ?fuspint";
mySqlCom.CommandText = strUpdate;
mySqlCom.Parameters.AddWithValue("?shengspint", stat.shengspint);
mySqlCom.Parameters.AddWithValue("?pingspint", stat.pingspint);
mySqlCom.Parameters.AddWithValue("?fuspint", stat.fuspint);
mySqlCom.Parameters.AddWithValue("?totalcount", stat.totalcount);
mySqlCom.Parameters.AddWithValue("?shengcount", stat.shengcount);
mySqlCom.Parameters.AddWithValue("?pingcount", stat.pingcount);
mySqlCom.Parameters.AddWithValue("?fucount", stat.fucount);
mySqlCom.Parameters.AddWithValue("?shenglv", stat.shenglv);
mySqlCom.Parameters.AddWithValue("?pinglv", stat.pinglv);
mySqlCom.Parameters.AddWithValue("?fulv", stat.fulv);
}
mySqlCom.ExecuteNonQuery();
}
}
}
private void CreateNewDoctor(object sender, EventArgs e)
{
Messages msg = new Messages();
if (TextLastName.Text == "")
{
msg.DataError("Введите фамилию!");
TextLastName.Focus();
}
else if (TextFirstName.Text == "")
{
msg.DataError("Введите имя!");
TextFirstName.Focus();
}
else if (TextIIN.Text.Length != 12)
{
msg.DataError("Введите корректный ИИН!");
TextIIN.Focus();
}
else if (TextEmail.Text == "")
{
msg.DataError("Введите адрес электронной почты!");
TextEmail.Focus();
}
else if (TextPhone.Text == "")
{
msg.DataError("Введите номер телефона!");
TextPhone.Focus();
}
else if (TextPassword.Text == "")
{
msg.DataError("Введите пароль!");
TextPassword.Focus();
}
else if (TextPassword.Text != TextRepPassword.Text)
{
msg.DataError("Пароли не совпадают!");
TextPassword.Focus();
}
else if (CBSetSpec.Text == "")
{
msg.DataError("Выберите специализацию!");
CBSetSpec.Focus();
}
else
{
conn.Open();
sql = "SELECT COUNT(*) FROM DOCTOR WHERE DOC_EMAIL = '" + TextEmail.Text + "'";
MySqlCommand checkEmail = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(checkEmail.ExecuteScalar());
if (COUNT == 0)
{
sql = "SELECT COUNT(*) FROM DOCTOR WHERE DOC_IIN = '" + TextIIN.Text + "'";
MySqlCommand checkIIN = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(checkIIN.ExecuteScalar());
if (COUNT == 0)
{
sql = "INSERT INTO DOCTOR (DOC_LASTNAME, " +
"DOC_FIRSTNAME, " +
"DOC_PATRONYMIC, " +
"DOC_IIN, " +
"DOC_EMAIL, " +
"DOC_PHONE, " +
"DOC_PASSWORD, " +
"SPECIALISATION_ID_SPEC) " +
"VALUES ('" + TextLastName.Text + "', " +
"'" + TextFirstName.Text + "', " +
"'" + TextPatronymic.Text + "', " +
"'" + TextIIN.Text + "', " +
"'" + TextEmail.Text + "', " +
"'" + TextPhone.Text + "', " +
"'" + TextPassword.Text + "', " +
"" + idspec + ")";
MySqlCommand insdoc = new MySqlCommand(sql, conn);
if (insdoc.ExecuteNonQuery() == 1)
{
msg.WriteSuccess();
this.Close();
AdminMainForm amf = new AdminMainForm();
amf.Show();
conn.Close();
}
else
{
msg.WriteError();
conn.Close();
}
}
else
{
msg.DataError("ИИН уже существует в системе!");
conn.Close();
}
}
else
{
msg.DataError("Электронный адрес уже существует в системе!");
conn.Close();
}
}
}
public int IM_MySQLRakamGetir(string str_sorgu)
{
MySqlCommand komut = new MySqlCommand(str_sorgu, baglanti);
return(Convert.ToInt32(komut.ExecuteScalar().ToString()));
}
protected override void HandlePacket(Client client, CreatePacket packet)
{
using (Database dbx = new Database())
{
MySqlCommand cmd = dbx.CreateQuery();
int nextCharId = 1;
if (!client.Account.IsGuestAccount)
{
nextCharId = dbx.GetNextCharId(client.Account);
cmd.CommandText = "SELECT maxCharSlot FROM accounts WHERE id=@accId;";
cmd.Parameters.AddWithValue("@accId", client.Account.AccountId);
int maxChar = (int)cmd.ExecuteScalar();
cmd = dbx.CreateQuery();
cmd.CommandText = "SELECT COUNT(id) FROM characters WHERE accId=@accId AND dead = FALSE;";
cmd.Parameters.AddWithValue("@accId", client.Account.AccountId);
int currChar = (int)(long)cmd.ExecuteScalar();
if (currChar >= maxChar)
{
client.Disconnect();
return;
}
}
client.Character = Database.CreateCharacter(client.Manager.GameData, (ushort)packet.ClassType,
nextCharId);
int[] stats = new[]
{
client.Character.MaxHitPoints,
client.Character.MaxMagicPoints,
client.Character.Attack,
client.Character.Defense,
client.Character.Speed,
client.Character.Dexterity,
client.Character.HpRegen,
client.Character.MpRegen
};
int skin = client.Account.OwnedSkins.Contains(packet.SkinType) ? packet.SkinType : 0;
client.Character.Skin = skin;
cmd = dbx.CreateQuery();
cmd.Parameters.AddWithValue("@accId", client.Account.AccountId);
cmd.Parameters.AddWithValue("@charId", nextCharId);
cmd.Parameters.AddWithValue("@charType", packet.ClassType);
cmd.Parameters.AddWithValue("@items", Utils.GetCommaSepString(client.Character.EquipSlots()));
cmd.Parameters.AddWithValue("@stats", Utils.GetCommaSepString(stats));
cmd.Parameters.AddWithValue("@fameStats", client.Character.FameStats.ToString());
cmd.Parameters.AddWithValue("@skin", skin);
cmd.CommandText =
"INSERT INTO characters (accId, charId, charType, level, exp, fame, items, hp, mp, stats, dead, pet, fameStats, skin) VALUES (@accId, @charId, @charType, 1, 0, 0, @items, 100, 100, @stats, FALSE, -1, @fameStats, @skin);";
if (cmd.ExecuteNonQuery() > 0)
{
World target = client.Manager.Worlds[client.TargetWorld];
client.SendPacket(new Create_SuccessPacket
{
CharacterID = client.Character.CharacterId,
ObjectID =
client.Manager.Worlds[client.TargetWorld].EnterWorld(
client.Player = new Player(client.Manager, client))
});
client.Stage = ProtocalStage.Ready;
}
else
{
client.SendPacket(new FailurePacket
{
ErrorDescription = "Failed to Load character."
});
}
}
}
public void subject_progress()
{
string s_id = Session["s_id"].ToString();
string su_id = Session["subject_id"].ToString();
con.Open();
MySqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select count(total_quiz) from subject_result where srs_id=(select srs_id from student_registered_courses where s_id='" + s_id + "' && su_id='" + su_id + "')";
int b = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
if (b == 1)
{
con.Open();
MySqlCommand cmd2 = con.CreateCommand();
cmd2.CommandType = CommandType.Text;
cmd2.CommandText = "select total_quiz from subject_result where srs_id=(select srs_id from student_registered_courses where s_id='" + s_id + "' && su_id='" + su_id + "')";
d = Convert.ToInt32(cmd2.ExecuteScalar());
con.Close();
a = d + d;
float value1 = 0;
float value2 = 0;
float value = 0;
int k = 0;
con.Open();
MySqlCommand cmd3 = con.CreateCommand();
cmd3.CommandType = CommandType.Text;
cmd3.CommandText = "select q1,total_q1_marks,q2,total_q2_marks,q3,total_q3_marks,q4,total_q4_marks,q5,total_q5_marks,total_quiz_percentage from subject_result where srs_id=(select srs_id from student_registered_courses where s_id='" + s_id + "' && su_id='" + su_id + "')";
MySqlDataReader id1 = cmd3.ExecuteReader();
while (id1.Read())
{
float percentage = id1.GetFloat(10);
for (int i = 0; i <= a; i++)
{
if (id1[i] != DBNull.Value && i != a)
{
if ((i == 0 || i == 2 || i == 4 || i == 6 || i == 8) && i != a)
{
value1 = id1.GetFloat(i);
}
else if ((i == 1 || i == 3 || i == 5 || i == 7 || i == 9) && i != a)
{
k++;
if (value1 != 0)
{
value2 = id1.GetFloat(i);
value = (value1 / value2) * 100;
total_value = total_value + value;
}
else
{
total_value = total_value + 0;
}
}
}
else if (i == a)
{
total_value = total_value / k;
}
else
{
total_value = total_value + 0;
}
}
}
con.Close();
}
else if (b == 0)
{
total_value = total_value + 0;
}
float quiz_progress = total_value;//quiz progress value
total_value = 0;
b = 0;
con.Open();
MySqlCommand cmd1 = con.CreateCommand();
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = "select count(total_assignment) from subject_result where srs_id=(select srs_id from student_registered_courses where s_id='" + s_id + "' && su_id='" + su_id + "')";
b = Convert.ToInt32(cmd1.ExecuteScalar());
con.Close();
if (b == 1)
{
con.Open();
MySqlCommand cmd2 = con.CreateCommand();
cmd2.CommandType = CommandType.Text;
cmd2.CommandText = "select total_assignment from subject_result where srs_id=(select srs_id from student_registered_courses where s_id='" + s_id + "' && su_id='" + su_id + "')";
d = Convert.ToInt32(cmd2.ExecuteScalar());
con.Close();
a = d + d;
float value1 = 0;
float value2 = 0;
float value = 0;
int k = 0;
con.Open();
MySqlCommand cmd3 = con.CreateCommand();
cmd3.CommandType = CommandType.Text;
cmd3.CommandText = "select a1,total_a1_marks,a2,total_a2_marks,a3,total_a3_marks,a4,total_a4_marks,a5,total_a5_marks,total_assignment_percentage from subject_result where srs_id=(select srs_id from student_registered_courses where s_id='" + s_id + "' && su_id='" + su_id + "')";
MySqlDataReader id1 = cmd3.ExecuteReader();
while (id1.Read())
{
float percentage = id1.GetFloat(10);
for (int i = 0; i <= a; i++)
{
if (id1[i] != DBNull.Value && i != a)
{
if ((i == 0 || i == 2 || i == 4 || i == 6 || i == 8) && i != a)
{
value1 = id1.GetFloat(i);
}
else if ((i == 1 || i == 3 || i == 5 || i == 7 || i == 9) && i != a)
{
k++;
if (value1 != 0)
{
value2 = id1.GetFloat(i);
value = (value1 / value2) * 100;
total_value = total_value + value;
}
else
{
total_value = total_value + 0;
}
}
}
else if (i == a)
{
total_value = total_value / k;
}
else
{
total_value = total_value + 0;
}
}
}
con.Close();
}
else if (b == 0)
{
total_value = total_value + 0;
}
float assignment_progress = total_value;//assignment progress value
total_value = 0;
b = 0;
con.Open();
MySqlCommand cmd4 = con.CreateCommand();
cmd4.CommandType = CommandType.Text;
cmd4.CommandText = "select count(total_project) from subject_result where srs_id=(select srs_id from student_registered_courses where s_id='" + s_id + "' && su_id='" + su_id + "')";
b = Convert.ToInt32(cmd4.ExecuteScalar());
con.Close();
if (b == 1)
{
con.Open();
MySqlCommand cmd2 = con.CreateCommand();
cmd2.CommandType = CommandType.Text;
cmd2.CommandText = "select total_project from subject_result where srs_id=(select srs_id from student_registered_courses where s_id='" + s_id + "' && su_id='" + su_id + "')";
d = Convert.ToInt32(cmd2.ExecuteScalar());
con.Close();
a = d + d;
float value1 = 0;
float value2 = 0;
float value = 0;
int k = 0;
con.Open();
MySqlCommand cmd3 = con.CreateCommand();
cmd3.CommandType = CommandType.Text;
cmd3.CommandText = "select p1,total_p1_marks,p2,total_p2_marks,p3,total_p3_marks,p4,total_p4_marks,p5,total_p5_marks,total_project_percentage from subject_result where srs_id=(select srs_id from student_registered_courses where s_id='" + s_id + "' && su_id='" + su_id + "')";
MySqlDataReader id1 = cmd3.ExecuteReader();
while (id1.Read())
{
float percentage = id1.GetFloat(10);
for (int i = 0; i <= a; i++)
{
if (id1[i] != DBNull.Value && i != a)
{
if ((i == 0 || i == 2 || i == 4 || i == 6 || i == 8) && i != a)
{
value1 = id1.GetFloat(i);
}
else if ((i == 1 || i == 3 || i == 5 || i == 7 || i == 9) && i != a)
{
k++;
if (value1 != 0)
{
value2 = id1.GetFloat(i);
value = (value1 / value2) * 100;
total_value = total_value + value;
}
else
{
total_value = total_value + 0;
}
}
}
else if (i == a)
{
total_value = total_value / k;
}
else
{
total_value = total_value + 0;
}
}
}
con.Close();
}
else if (b == 0)
{
total_value = total_value + 0;
}
float project_progress = total_value;//project progress value
total_value = 0;
float value11 = 0;
float value22 = 0;
con.Open();
MySqlCommand cmd6 = con.CreateCommand();
cmd6.CommandType = CommandType.Text;
cmd6.CommandText = "select midterm_marks,total_midterm_marks from subject_result where srs_id=(select srs_id from student_registered_courses where s_id='" + s_id + "' && su_id='" + su_id + "')";
MySqlDataReader ids = cmd6.ExecuteReader();
while (ids.Read())
{
for (int i = 0; i < 2; i++)
{
if (ids[i] != DBNull.Value)
{
if (i == 0)
{
value11 = ids.GetFloat(i);
}
else if (i == 1)
{
if (value11 != 0)
{
value22 = ids.GetFloat(i);
total_value = (value11 / value22) * 100;
}
else
{
total_value = 0;
}
}
}
if (ids[i] == DBNull.Value)
{
total_value = 0;
}
}
}
con.Close();
float midterm_progress = total_value;//midterm progress value
total_value = 0;
float value111 = 0;
float value222 = 0;
con.Open();
MySqlCommand cmd7 = con.CreateCommand();
cmd7.CommandType = CommandType.Text;
cmd7.CommandText = "select finalterm_marks,total_finalterm_marks from subject_result where srs_id=(select srs_id from student_registered_courses where s_id='" + s_id + "' && su_id='" + su_id + "')";
MySqlDataReader id = cmd7.ExecuteReader();
while (id.Read())
{
for (int i = 0; i < 2; i++)
{
if (id[i] != DBNull.Value)
{
if (i == 0)
{
value111 = id.GetFloat(i);
}
else if (i == 1)
{
if (value111 != 0)
{
value222 = id.GetFloat(i);
total_value = (value111 / value222) * 100;
}
else
{
total_value = 0;
}
}
}
if (id[i] == DBNull.Value)
{
total_value = 0;
}
}
}
con.Close();
float finalterm_progress = Convert.ToInt32(total_value);//finalterm progress value
Chart1.Series["progress_status"].Points[0].YValues[0] = quiz_progress;
Chart1.Series["progress_status"].Points[1].YValues[0] = assignment_progress;
Chart1.Series["progress_status"].Points[2].YValues[0] = project_progress;
Chart1.Series["progress_status"].Points[3].YValues[0] = midterm_progress;
Chart1.Series["progress_status"].Points[4].YValues[0] = finalterm_progress;
Chart1.ChartAreas[0].AxisY.Minimum = 0;
Chart1.ChartAreas[0].AxisY.Maximum = 100;
Chart1.ChartAreas[0].AxisY.Interval = 5;
//Chart1.Series["progress_status"].Points[0].IsValueShownAsLabel = true;
// Chart1.Series["progress_status"].Points[1].Color = Color.RoyalBlue;
/* Chart1.Series["progress_status"].Points[1].IsValueShownAsLabel = true;
* Chart1.Series["progress_status"].Points[2].Color = Color.Yellow;
* Chart1.Series["progress_status"].Points[2].IsValueShownAsLabel = true;
* Chart1.Series["progress_status"].Points[3].Color = Color.RosyBrown;
* Chart1.Series["progress_status"].Points[3].IsValueShownAsLabel = true;
* Chart1.Series["progress_status"].Points[4].Color = Color.Green;
* Chart1.Series["progress_status"].Points[4].IsValueShownAsLabel = true; */
}
private void btn_delete_Click(object sender, EventArgs e)
{
//deleting items
DateTime time = DateTime.Now;
string format = "yyyy-MM-dd HH:mm:ss";
var mytime = time.ToString(format);
String sqlconstring = "Data Source = LOCALHOST; Initial Catalog = it_2d; username = root; password = '';Convert Zero Datetime=True";
MySqlConnection sqlconnect = new MySqlConnection(sqlconstring);
MySqlCommand sqlcommand = new MySqlCommand();
MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter();
DataSet DS = new DataSet();
sqlconnect.Open();
//checking if the accession no inputted is existing
MySqlCommand check_User_Name = new MySqlCommand("SELECT * FROM tbl_itemlist WHERE (`b_no` = '" + txt_accid.Text + "') ", sqlconnect);
if (check_User_Name.ExecuteScalar() != null)
{
string UserExist = check_User_Name.ExecuteScalar().ToString();
if (UserExist != null)
{
//delete the item
sqlcommand.CommandText = "DELETE FROM `tbl_itemlist` WHERE `b_no` = ('" + txt_accid.Text + "') ";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlcommand.CommandText = "INSERT INTO `it_2d`.`tbl_activitylog` (`librarian_id`, `activity`,`account_no`,`date_activity`) VALUES ('" + txt_id.Text + "' ,' Delete item','" + txt_accid.Text + "','" + mytime + "');";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlcommand.CommandText = "SELECT `b_no` as 'Accession No.', `b_medium` as 'Medium', `b_title` as 'Title', `b_author` as 'Author', `b_category` as 'Category', `b_copies` as 'Copies', `b_copyright` as 'Copyright', `b_dateadd` as 'Date Added' FROM `tbl_itemlist` ";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlDataAdapter.Fill(DS, "KAIBIGAN");
dataGridView1.DataSource = DS;
dataGridView1.DataMember = "KAIBIGAN";
sqlconnect.Close();
MessageBox.Show("Item Removed", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
txt_accid.Clear();
txt_title.Clear();
txt_author.Clear();
txt_copies.Clear();
txt_total.Clear();
txt_stitle.Clear();
txt_saccno.Clear();
cmb_search.SelectedIndex = 0;
cmb_category.SelectedIndex = 0;
cmb_medium.SelectedIndex = 0;
cmb_smedium.SelectedIndex = 0;
cmb_scategory.SelectedIndex = 0;
;
}
}
else
{
MessageBox.Show("Invalid Accession No.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public async Task ConnectDB(string response = null, params string[] args)
{
//taking the response from the user, converts it to string and removing the .connect part
if (response == null)
{
var embed = new EmbedBuilder();
embed.AddField("Connecting you...",
"no account name was provided!")
.WithAuthor(author => { author
.WithName("Birdie Bot")
.WithIconUrl(Global.Birdieicon); })
.WithThumbnailUrl(Global.Birdiethumbnail)
.WithColor(new Color(255, 83, 13))
.WithTitle("Birdie Bot notification")
.WithFooter(footer => { footer
.WithText(Global.Botcreatorname)
.WithIconUrl(Global.Birdieicon); })
.WithCurrentTimestamp()
.Build();
await Context.Channel.SendMessageAsync("", false, embed);
await Task.Delay(5000);
var messages = await Context.Channel.GetMessagesAsync(2).Flatten();
await Context.Channel.DeleteMessagesAsync(messages);
return;
}
string userMessage = Context.Message.ToString();
string name = userMessage.Substring(userMessage.IndexOf(' ') + 1);
//replacing space with "%20"
string account = name.Replace(" ", "%20");
//using c for webclient connections
WebClient c = new WebClient();
//getting league account ID
//using "i" and "f" for id
//if account doesnt exist - throw error and end
JObject f = null;
try
{
string responsename = c.DownloadString("https://euw1.api.riotgames.com/lol/summoner/v4/summoners/by-name/" + account + "?api_key=" + Global.apikey + "");
JObject i = JObject.Parse(responsename);
f = i;
}
catch (Exception)
{
var embed = new EmbedBuilder();
embed.AddField("Connecting you...",
"Summoner account doesnt exist!")
.WithAuthor(author => { author
.WithName("Birdie Bot")
.WithIconUrl(Global.Birdieicon); })
.WithThumbnailUrl(Global.Birdiethumbnail)
.WithColor(new Color(255, 83, 13))
.WithTitle("Birdie Bot notification")
.WithFooter(footer => { footer
.WithText(Global.Botcreatorname)
.WithIconUrl(Global.Birdieicon); })
.WithCurrentTimestamp()
.Build();
await Context.Channel.SendMessageAsync("", false, embed);
return;
}
try
{
//getting id, name and icon id from response
var id = f["id"];
var lolname = f["name"];
var icon = f["profileIconId"];
//to get the account icon, you first need to find the latest version of the game
//which i could have written in manually - but where's the fun in that.
// after getting the latest version of the game i use it with the DDragon api that have all the game assets
//then the version and icon id is used in the DDragon api string to retrive the icon as a png
string findlatestlolversion = c.DownloadString("https://ddragon.leagueoflegends.com/api/versions.json");
JArray latestlolversion = JArray.Parse(findlatestlolversion);
var version = latestlolversion[0];
string thumbnailURL = "http://ddragon.leagueoflegends.com/cdn/" + version + "/img/profileicon/" + icon + ".png";
//Getting the Discord user ID and converting their ID to a readable discord name
//If name contains letters that is used in SQL syntax, replace that letter with something that doesn't conflict with SQL query
string UserID = Context.User.Id.ToString();
ulong resultid = Context.User.Id;
ulong xx = Convert.ToUInt64(resultid);
var GottenName = Context.Guild.GetUser(xx);
var guildUser = GottenName as SocketGuildUser;
string DiscordName = null;
var guildID = Context.Guild.Id;
if (guildUser.ToString().Contains("'"))
{
DiscordName = guildUser.ToString().Replace("'", "''");
}
//SQL QUERY String used for commands
string Query = "INSERT INTO users_testing (Discord_Id,Discord_Name,League_Id,League_Name,Icon_Id,SOLO_QUEUE) VALUES ('" + UserID + "','" + DiscordName + "','" + id + "','" + lolname + "','" + icon + "','" + "Unranked" + "');";
string Duplicate = "SELECT Discord_Id FROM users_testing WHERE Discord_Id like '%" + UserID + "%'; ";
string InsertDiscordName = "UPDATE users_testing SET `Discord_Name` = '" + DiscordName + "' WHERE Discord_Id like '%" + UserID + "%';";
string UpdatestatusandID = "UPDATE users_testing SET Verified = '" + "false" + "',Guild_ID = '" + guildID.ToString() + "' WHERE Discord_Id like '%" + UserID + "%';";
string Result;
//sql connection and command
MySqlConnection myconn = new MySqlConnection(Global.connect);
MySqlCommand command = new MySqlCommand(Query, myconn);
MySqlCommand SetIDandStatus = new MySqlCommand(UpdatestatusandID, myconn);
MySqlCommand DuplicateCommand = new MySqlCommand(Duplicate, myconn);
MySqlCommand InsertDiscordname = new MySqlCommand(InsertDiscordName, myconn);
MySqlDataReader myreader;
myconn.Open();
Result = (string)DuplicateCommand.ExecuteScalar();
//check for duplicate of discord Ids
myreader = DuplicateCommand.ExecuteReader();
if (myreader.Read())
{
Result = myreader.GetString(myreader.GetOrdinal("Discord_Id"));
}
myconn.Close();
//if the user already exists in the DB - just tell them and do nothing.
if (Result == UserID)
{
myconn.Open();
myreader = InsertDiscordname.ExecuteReader();
myconn.Close();
var embed = new EmbedBuilder();
embed.AddField("Connecting you...",
"Your League and Discord account already exist in the Database!")
.WithAuthor(author => { author
.WithName("Birdie Bot")
.WithIconUrl(Global.Birdieicon); })
.WithThumbnailUrl(thumbnailURL)
.WithColor(new Color(255, 83, 13))
.WithTitle("Birdie Bot notification")
.WithFooter(footer => { footer
.WithText(Global.Botcreatorname)
.WithIconUrl(Global.Birdieicon); })
.WithCurrentTimestamp()
.Build();
await Context.Channel.SendMessageAsync("", false, embed);
await Task.Delay(5000);
var messages = await Context.Channel.GetMessagesAsync(2).Flatten();
await Context.Channel.DeleteMessagesAsync(messages);
}
//if they dont exist - open connection and run the command to push to DB.
else
{
//sends all info to DB
myconn.Open();
myreader = command.ExecuteReader();
myconn.Close();
try
{
//sets the user verification status to false and post the guild ID of the user to SQL
myconn.Open();
SetIDandStatus.ExecuteReader();
myconn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
var embed = new EmbedBuilder();
embed.AddField("Connecting you...",
"Your league account " + "`" + lolname + "`" + " has been added to the Database!")
.WithAuthor(author => { author
.WithName("Birdie Bot")
.WithIconUrl(Global.Birdieicon); })
.WithThumbnailUrl(thumbnailURL)
.WithColor(new Color(255, 83, 13))
.WithTitle("Birdie Bot notification")
.WithFooter(footer => { footer
.WithText(Global.Botcreatorname)
.WithIconUrl(Global.Birdieicon); })
.WithCurrentTimestamp()
.Build();
await Context.Channel.SendMessageAsync("", false, embed);
Console.WriteLine(Context.User.Username + " Just added their lol account: " + lolname + " To the Database!");
await Task.Delay(5000);
var messages = await this.Context.Channel.GetMessagesAsync(2).Flatten();
await Context.Channel.DeleteMessagesAsync(messages);
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
private void txtBox_KeyDown(object sender, KeyEventArgs e)
{
TextBox txtBox = (TextBox)sender;
string query;
MySqlCommand com;
string Name;
if (e.KeyCode == Keys.Enter)
{
try
{
if (txtBox.Text != "")
{
dbconnection.Open();
switch (txtBox.Name)
{
case "txtFactory":
query = "select Factory_Name from sets where Factory_ID='" + txtFactory.Text + "'";
com = new MySqlCommand(query, dbconnection);
if (com.ExecuteScalar() != null)
{
Name = (string)com.ExecuteScalar();
comFactory.Text = Name;
FilterSet();
}
else
{
MessageBox.Show("there is no item with this id");
dbconnection.Close();
return;
}
break;
case "txtType":
query = "select Type_Name from sets where Type_ID='" + txtType.Text + "'";
com = new MySqlCommand(query, dbconnection);
if (com.ExecuteScalar() != null)
{
Name = (string)com.ExecuteScalar();
comType.Text = Name;
txtFactory.Focus();
FilterFactoryAndSet();
}
else
{
MessageBox.Show("there is no item with this id");
dbconnection.Close();
return;
}
break;
case "txtSetsID":
query = "select Set_Name from sets where Set_ID='" + txtSetsID.Text + "'";
com = new MySqlCommand(query, dbconnection);
if (com.ExecuteScalar() != null)
{
Name = (string)com.ExecuteScalar();
comSets.Text = Name;
}
else
{
MessageBox.Show("there is no item with this id");
dbconnection.Close();
return;
}
break;
}
}
}
catch
{
// MessageBox.Show(ex.ToString());
}
dbconnection.Close();
}
}
public void DifferentParameterOrder()
{
execSQL("CREATE TABLE Test (id int NOT NULL AUTO_INCREMENT, " +
"id2 int NOT NULL, name varchar(50) DEFAULT NULL, " +
"id3 int DEFAULT NULL, PRIMARY KEY (id))");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id, id2, name, id3) " +
"VALUES(?id, ?id2, ?name,?id3)", conn);
MySqlParameter id = new MySqlParameter();
id.ParameterName = "?id";
id.DbType = DbType.Int32;
id.Value = DBNull.Value;
MySqlParameter id2 = new MySqlParameter();
id2.ParameterName = "?id2";
id2.DbType = DbType.Int32;
id2.Value = 2;
MySqlParameter name = new MySqlParameter();
name.ParameterName = "?name";
name.DbType = DbType.String;
name.Value = "Test";
MySqlParameter id3 = new MySqlParameter();
id3.ParameterName = "?id3";
id3.DbType = DbType.Int32;
id3.Value = 3;
cmd.Parameters.Add(id);
cmd.Parameters.Add(id2);
cmd.Parameters.Add(name);
cmd.Parameters.Add(id3);
cmd.Prepare();
Assert.AreEqual(1, cmd.ExecuteNonQuery());
cmd.Parameters.Clear();
id3.Value = DBNull.Value;
name.Value = DBNull.Value;
cmd.Parameters.Add(id);
cmd.Parameters.Add(id2);
cmd.Parameters.Add(id3);
cmd.Parameters.Add(name);
cmd.Prepare();
Assert.AreEqual(1, cmd.ExecuteNonQuery());
cmd.CommandText = "SELECT id3 FROM Test WHERE id=1";
Assert.AreEqual(3, cmd.ExecuteScalar());
cmd.CommandText = "SELECT name FROM Test WHERE id=2";
Assert.AreEqual(DBNull.Value, cmd.ExecuteScalar());
}
private static void ProcessStoredLocalOfficials(DataTable table)
{
const string countCmdText = "SELECT COUNT(*) FROM local_officials_raw " +
"WHERE localId=@localId AND officeId=@officeId AND " +
"candidateId=@candidateId";
const string insertCmdText =
"INSERT INTO local_officials_raw" +
"(candidateId,electionDistrictId,electionParties,fetch_id," +
"fetch_method,fetch_parameters,fetch_time," +
"firstName,lastName,localId,middleName,nickName,officeDistrictId," +
"officeDistrictName,officeId,officeName,officeParties," +
"officeStateId,officeTypeId,suffix,title)" +
" VALUES (@candidateId,@electionDistrictId,@electionParties," +
"@fetchId,@fetchMethod,@fetchParameters,@fetchTime," +
"@firstName,@lastName,@localId,@middleName,@nickName,@officeDistrictId," +
"@officeDistrictName,@officeId,@officeName,@officeParties," +
"@officeStateId,@officeTypeId,@suffix,@title);";
var fetchErrors = 0;
var officialsAdded = 0;
var duplicates = 0;
foreach (var row in table.Rows.OfType <DataRow>())
{
var localId = ParseIdFromParameters(row["fetch_parameters"], "localId");
var jsonObj = GetDataAsJson(row);
if (!jsonObj.ContainsKey("candidateList"))
{
fetchErrors++;
continue;
}
var candidateList = jsonObj["candidateList"] as Dictionary <string, object>;
if (candidateList == null || !candidateList.ContainsKey("candidate"))
{
fetchErrors++;
continue;
}
var candidate = AsArrayList(candidateList["candidate"]);
if (candidate == null)
{
fetchErrors++;
continue;
}
foreach (var c in candidate.Cast <Dictionary <string, object> >())
{
var officeId = Convert.ToInt32(c["officeId"]);
var candidateId = Convert.ToInt32(c["candidateId"]);
var officeDistrictId = c["officeDistrictId"];
if (string.IsNullOrWhiteSpace(officeDistrictId as string))
{
officeDistrictId = null;
}
var electionDistrictId = c["electionDistrictId"];
if (string.IsNullOrWhiteSpace(electionDistrictId as string))
{
electionDistrictId = null;
}
// skip duplicate official
var countCmd = new MySqlCommand(countCmdText);
countCmd.Parameters.AddWithValue("@localId", localId);
countCmd.Parameters.AddWithValue("@officeId", officeId);
countCmd.Parameters.AddWithValue("@candidateId", candidateId);
int count;
using (var cn = GetOpenConnection())
{
countCmd.Connection = cn;
count = Convert.ToInt32(countCmd.ExecuteScalar());
}
if (count != 0)
{
duplicates++;
continue;
}
var insertCmd = new MySqlCommand(insertCmdText);
insertCmd.Parameters.AddWithValue("@candidateId", candidateId);
insertCmd.Parameters.AddWithValue("@electionDistrictId", electionDistrictId);
insertCmd.Parameters.AddWithValue("@electionParties", c["electionParties"]);
insertCmd.Parameters.AddWithValue("@fetchId", row["id"]);
insertCmd.Parameters.AddWithValue("@fetchMethod", row["fetch_method"]);
insertCmd.Parameters.AddWithValue("@fetchParameters", row["fetch_parameters"]);
insertCmd.Parameters.AddWithValue("@fetchTime", row["fetch_time"]);
insertCmd.Parameters.AddWithValue("@firstName", c["firstName"]);
insertCmd.Parameters.AddWithValue("@lastName", c["lastName"]);
insertCmd.Parameters.AddWithValue("@localId", localId);
insertCmd.Parameters.AddWithValue("@middleName", c["middleName"]);
insertCmd.Parameters.AddWithValue("@nickName", c["nickName"]);
insertCmd.Parameters.AddWithValue("@officeDistrictId", officeDistrictId);
insertCmd.Parameters.AddWithValue("@officeDistrictName", c["officeDistrictName"]);
insertCmd.Parameters.AddWithValue("@officeId", officeId);
insertCmd.Parameters.AddWithValue("@officeName", c["officeName"]);
insertCmd.Parameters.AddWithValue("@officeParties", c["officeParties"]);
insertCmd.Parameters.AddWithValue("@officeStateId", c["officeStateId"]);
insertCmd.Parameters.AddWithValue("@officeTypeId", c["officeTypeId"]);
insertCmd.Parameters.AddWithValue("@suffix", c["suffix"]);
insertCmd.Parameters.AddWithValue("@title", c["title"]);
using (var cn = GetOpenConnection())
{
insertCmd.Connection = cn;
insertCmd.ExecuteNonQuery();
officialsAdded++;
}
}
}
MessageBox.Show(
String.Format(
"{0} rows processed, {1} had fetch errors," +
" {2} officials added, {3} duplicates.",
table.Rows.Count - fetchErrors, fetchErrors, officialsAdded,
duplicates));
}
static void Main(string[] args)
{
bool exist = false;
string connString = "server=localhost;port=3306;database=dietplan;user=newuser;password=password";
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
string userName;
char gender = Convert.ToChar("F");
int age = 0;
int height = 0;
int weight = 0;
int workOutLevel = 0;
int goalFatLoss = 0;
int dietLength = 0;
int caloriesConsumed, coffeeConsumed, caloriesBurnt;
double BMR, caloriesMaintenance, caloriesDeficitTarget, recommendedDailyCalories;
int currentDayCount = 2, daysRemaining;
double caloriesLost = 623.9, caloriesLostTotal = 1125.20, calorieDeficitAverage = 851.6, calorieDeficit = 854.85, fatLoss = 250;
double fatLossAverage = 245.63, fatLossSooFar = 236.54, fatLossExpected = 290.6;
string x;
BMR = 0;
caloriesMaintenance = 0;
Console.WriteLine("Enter your name");
userName = Console.ReadLine();
MySqlCommand cmd3 = new MySqlCommand(@"SELECT COUNT(*) FROM newuser WHERE userName = @userName", conn);
cmd3.Parameters.AddWithValue("@userName", userName);
exist = Convert.ToInt32(cmd3.ExecuteScalar()) > 0;
if (!exist)
{
Console.WriteLine("Enter your gender\n -Press M for male\n -Press F for female");
gender = Convert.ToChar(Console.ReadLine());
Console.WriteLine("Enter your age");
age = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter your height (cm)");
height = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter your weight (kg)");
weight = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter your workout level\n -Press 1 for Light/No exercise\n -Press 2 for Light exercise\n -Press 3 for Moderate exercise\n -Press 4 for very active exercise\n -Press 5 for extra active exercise");
workOutLevel = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter your fat loss goal (kg)");
goalFatLoss = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter your diet length (days)");
dietLength = Convert.ToInt32(Console.ReadLine());
MySqlCommand cmd = new MySqlCommand("INSERT INTO newUser(userName, age, gender, height, weight, workOutLevel, goalFatLoss, dietLength) VALUES(@userName, @age, @gender, @height, @weight, @workOutLevel, @goalFatLoss, @dietLength)", conn);
cmd.Parameters.AddWithValue("@userName", userName);
cmd.Parameters.AddWithValue("@age", age);
cmd.Parameters.AddWithValue("@gender", gender);
cmd.Parameters.AddWithValue("@height", height);
cmd.Parameters.AddWithValue("@weight", weight);
cmd.Parameters.AddWithValue("@workOutLevel", workOutLevel);
cmd.Parameters.AddWithValue("@goalFatLoss", goalFatLoss);
cmd.Parameters.AddWithValue("@dietLength", dietLength);
cmd.ExecuteNonQuery();
Console.WriteLine("Success!");
//If user already exists we offer these questions
}
else
{
do
{
Console.WriteLine("Enter your calories consumed (kcal)");
caloriesConsumed = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter your coffee doses consumed");
coffeeConsumed = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter your calories burnt (kcal)");
caloriesBurnt = Convert.ToInt32(Console.ReadLine());
MySqlCommand cmd2 = new MySqlCommand("INSERT INTO dietDays(caloriesConsumed, coffeeConsumed, caloriesBurnt) VALUES(@caloriesConsumed, @coffeeConsumed, @caloriesBurnt)", conn);
cmd2.Parameters.AddWithValue("@caloriesConsumed", caloriesConsumed);
cmd2.Parameters.AddWithValue("@coffeeConsumed", coffeeConsumed);
cmd2.Parameters.AddWithValue("@caloriesBurnt", caloriesBurnt);
cmd2.ExecuteNonQuery();
Console.WriteLine("Success!");
Console.Write("Do you have more data?");
}while (Console.ReadLine().ToLower() == "y");
conn.Close(); // had to change connection closure to close the connection after the loop is done
}
if (gender == 'M')
{
BMR = (10 * weight) + (6.25 * height) - (5 * age) + 5;
}
else if (gender == 'F')
{
BMR = (10 * weight) + (6.25 * height) - (5 * age) - 161;
}
if (workOutLevel == 1)
{
caloriesMaintenance = BMR * 1.2;
}
else if (workOutLevel == 2)
{
caloriesMaintenance = BMR * 1.375;
}
else if (workOutLevel == 3)
{
caloriesMaintenance = BMR * 1.55;
}
else if (workOutLevel == 4)
{
caloriesMaintenance = BMR * 1.725;
}
else if (workOutLevel == 5)
{
caloriesMaintenance = BMR * 1.9;
}
caloriesDeficitTarget = ((goalFatLoss / 0.0128) * 100) / dietLength;
recommendedDailyCalories = caloriesMaintenance - caloriesDeficitTarget;
daysRemaining = dietLength - currentDayCount;
Console.WriteLine("\n****Diet Report of " + userName + "****");
Console.WriteLine("\nBMR = {0}\nCalories Maintenance = {1} kcal\nRecommended Daily Calories = {2} kcal\nCurrent Day Count = Day {3}\nRemaining Days = {4} days", BMR, caloriesMaintenance, recommendedDailyCalories, currentDayCount, daysRemaining);
Console.WriteLine("\nCalories Lost = {0} kcal\nCalories Deficit Target = {1} kcal\nTotal Calories Lost = {2} kcal\nAverage Calorie Deficit = {3} kcal\nCalorie Deficit = {4} ", caloriesLost, caloriesDeficitTarget, caloriesLostTotal, calorieDeficitAverage, calorieDeficit);
Console.WriteLine("\nFat Loss = {0} g\nFat Loss Average = {1} g \nFat Loss So Far = {2} g\nFat Loss Expected = {3} g", fatLoss, fatLossAverage, fatLossSooFar, fatLossExpected);
Console.WriteLine("\n*******************************\nPress x to continue");
x = Console.ReadLine();
}
private static int ExecuteScalar(MySqlCommand command) => (int)command.ExecuteScalar();
/**
* Refresh Player Data
*
* This function collects aplayer's current stats and updates the database to reflect this change.
* @param UnturnedPlayer player The player that will be updated
*/
public void RefreshPlayer(UnturnedPlayer player)
{
try
{
MySqlConnection MySQLConnection = CreateConnection();
MySqlCommand MySQLCommand = MySQLConnection.CreateCommand();
// check if player exists in table
MySQLCommand.CommandText = "SELECT * FROM " + Livemap.Instance.Configuration.Instance.DatabaseTableLivemapData + " WHERE CSteamID = '" + player.CSteamID.ToString() + "'";
MySQLConnection.Open();
object result = MySQLCommand.ExecuteScalar();
if (result == null)
{
// player does not exist, create it
MySQLCommand.CommandText = "INSERT INTO " + Livemap.Instance.Configuration.Instance.DatabaseTableLivemapData + " (CSteamID,character_name,display_name,steam_group_id,steam_avatar_medium,server_id,ip_address,ping,is_pro,is_admin,is_god,is_vanished,in_vehicle,position,rotation,skin_color,hair,face,beard,hat,glasses,mask,is_bleeding,is_broken,health,stamina,hunger,thirst,infection,experience,reputation,skill_agriculture,skill_cardio,skill_cooking,skill_crafting,skill_dexerity,skill_diving,skill_engineer,skill_exercise,skill_fishing,skill_healing,skill_immunity,skill_mechanic,skill_outdoors,skill_overkill,skill_parkour,skill_sharpshooter,skill_sneakybeaky,skill_strength,skill_survival,skill_toughness,skill_vitality,skill_warmblooded,is_hidden) VALUES (" +
"'" + player.CSteamID.ToString() + "'," +
"'" + player.CharacterName.ToString() + "'," +
"'" + player.DisplayName.ToString() + "'," +
"'" + (player.SteamGroupID != null ? player.SteamGroupID.ToString() : "0") + "'," +
"'" + (player.SteamProfile.AvatarMedium != null ? player.SteamProfile.AvatarMedium.ToString() : Livemap.Instance.Configuration.Instance.PlayerDefaultSteamAvatar) + "'," +
"'" + Provider.serverID + "'," +
"'" + player.IP.ToString() + "'," +
"'" + player.Ping.ToString() + "'," +
Convert.ToInt32(player.IsPro) + "," +
Convert.ToInt32(player.IsAdmin) + "," +
Convert.ToInt32(player.GodMode) + "," +
Convert.ToInt32(player.VanishMode) + "," +
Convert.ToInt32(player.IsInVehicle) + "," +
"'" + player.Position.ToString() + "'," +
"'" + player.Rotation.ToString() + "'," +
"'" + player.Player.clothing.skin.ToString() + "'," +
player.Player.clothing.hair + "," +
player.Player.clothing.face + "," +
player.Player.clothing.beard + "," +
player.Player.clothing.hat + "," +
player.Player.clothing.glasses + "," +
player.Player.clothing.mask + "," +
Convert.ToInt32(player.Bleeding) + "," +
Convert.ToInt32(player.Broken) + "," +
player.Health + "," +
player.Stamina + "," +
player.Hunger + "," +
player.Thirst + "," +
player.Infection + "," +
player.Experience + "," +
player.Reputation + "," +
player.GetSkillLevel(UnturnedSkill.Agriculture) + "," +
player.GetSkillLevel(UnturnedSkill.Cardio) + "," +
player.GetSkillLevel(UnturnedSkill.Cooking) + "," +
player.GetSkillLevel(UnturnedSkill.Crafting) + "," +
player.GetSkillLevel(UnturnedSkill.Dexerity) + "," +
player.GetSkillLevel(UnturnedSkill.Diving) + "," +
player.GetSkillLevel(UnturnedSkill.Engineer) + "," +
player.GetSkillLevel(UnturnedSkill.Exercise) + "," +
player.GetSkillLevel(UnturnedSkill.Fishing) + "," +
player.GetSkillLevel(UnturnedSkill.Healing) + "," +
player.GetSkillLevel(UnturnedSkill.Immunity) + "," +
player.GetSkillLevel(UnturnedSkill.Mechanic) + "," +
player.GetSkillLevel(UnturnedSkill.Outdoors) + "," +
player.GetSkillLevel(UnturnedSkill.Overkill) + "," +
player.GetSkillLevel(UnturnedSkill.Parkour) + "," +
player.GetSkillLevel(UnturnedSkill.Sharpshooter) + "," +
player.GetSkillLevel(UnturnedSkill.Sneakybeaky) + "," +
player.GetSkillLevel(UnturnedSkill.Strength) + "," +
player.GetSkillLevel(UnturnedSkill.Survival) + "," +
player.GetSkillLevel(UnturnedSkill.Toughness) + "," +
player.GetSkillLevel(UnturnedSkill.Vitality) + "," +
player.GetSkillLevel(UnturnedSkill.Warmblooded) + "," +
Livemap.Instance.IsPlayerHidden(player) +
")";
MySQLCommand.ExecuteNonQuery();
}
else
{
// player already exists, update player row
MySQLCommand.CommandText = "UPDATE " + Livemap.Instance.Configuration.Instance.DatabaseTableLivemapData + " SET " +
"character_name = '" + player.CharacterName.ToString() + "'," +
"display_name = '" + player.DisplayName.ToString() + "'," +
"steam_group_id = '" + (player.SteamGroupID != null ? player.SteamGroupID.ToString() : "0") + "'," +
"steam_avatar_medium = '" + (player.SteamProfile.AvatarMedium != null ? player.SteamProfile.AvatarMedium.ToString() : Livemap.Instance.Configuration.Instance.PlayerDefaultSteamAvatar) + "'," +
"server_id = '" + Provider.serverID + "'," +
"ip_address = '" + player.IP.ToString() + "'," +
"ping = '" + player.Ping.ToString() + "'," +
"is_pro = " + Convert.ToInt32(player.IsPro) + "," +
"is_admin = " + Convert.ToInt32(player.IsAdmin) + "," +
"is_god = " + Convert.ToInt32(player.GodMode) + "," +
"is_vanished = " + Convert.ToInt32(player.VanishMode) + "," +
"in_vehicle = " + Convert.ToInt32(player.IsInVehicle) + "," +
"vehicle_is_driver = " + ReturnVehicleData(player, "vehicle_is_driver") + ", " +
"vehicle_instance_id = " + ReturnVehicleData(player, "vehicle_instance_id") + ", " +
"vehicle_id = " + ReturnVehicleData(player, "vehicle_id") + ", " +
"vehicle_fuel = " + ReturnVehicleData(player, "vehicle_fuel") + ", " +
"vehicle_health = " + ReturnVehicleData(player, "vehicle_health") + ", " +
"vehicle_headlights_on = " + ReturnVehicleData(player, "vehicle_headlights_on") + ", " +
"vehicle_taillights_on = " + ReturnVehicleData(player, "vehicle_taillights_on") + ", " +
"vehicle_sirens_on = " + ReturnVehicleData(player, "vehicle_sirens_on") + ", " +
"vehicle_speed = " + ReturnVehicleData(player, "vehicle_speed") + ", " +
"vehicle_has_battery = " + ReturnVehicleData(player, "vehicle_has_battery") + ", " +
"vehicle_battery_charge = " + ReturnVehicleData(player, "vehicle_battery_charge") + ", " +
"vehicle_exploded = " + ReturnVehicleData(player, "vehicle_exploded") + ", " +
"vehicle_locked = " + ReturnVehicleData(player, "vehicle_locked") + ", " +
"position = '" + player.Position.ToString() + "'," +
"rotation = '" + player.Rotation.ToString() + "'," +
"face = " + player.Player.clothing.face + "," +
"hat = " + player.Player.clothing.hat + "," +
"glasses = " + player.Player.clothing.glasses + "," +
"mask = " + player.Player.clothing.mask + "," +
"is_bleeding = " + Convert.ToInt32(player.Bleeding) + "," +
"is_broken = " + Convert.ToInt32(player.Broken) + "," +
"health = " + player.Health + "," +
"stamina = " + player.Stamina + "," +
"hunger = " + player.Hunger + "," +
"thirst = " + player.Thirst + "," +
"infection = " + player.Infection + "," +
"experience = " + player.Experience + "," +
"reputation = " + player.Reputation + "," +
"skill_agriculture = " + player.GetSkillLevel(UnturnedSkill.Agriculture) + "," +
"skill_cardio = " + player.GetSkillLevel(UnturnedSkill.Cardio) + "," +
"skill_cooking = " + player.GetSkillLevel(UnturnedSkill.Cooking) + "," +
"skill_crafting = " + player.GetSkillLevel(UnturnedSkill.Crafting) + "," +
"skill_dexerity = " + player.GetSkillLevel(UnturnedSkill.Dexerity) + "," +
"skill_diving = " + player.GetSkillLevel(UnturnedSkill.Diving) + "," +
"skill_engineer = " + player.GetSkillLevel(UnturnedSkill.Engineer) + "," +
"skill_exercise = " + player.GetSkillLevel(UnturnedSkill.Exercise) + "," +
"skill_fishing = " + player.GetSkillLevel(UnturnedSkill.Fishing) + "," +
"skill_healing = " + player.GetSkillLevel(UnturnedSkill.Healing) + "," +
"skill_immunity = " + player.GetSkillLevel(UnturnedSkill.Immunity) + "," +
"skill_mechanic = " + player.GetSkillLevel(UnturnedSkill.Mechanic) + "," +
"skill_outdoors = " + player.GetSkillLevel(UnturnedSkill.Outdoors) + "," +
"skill_overkill = " + player.GetSkillLevel(UnturnedSkill.Overkill) + "," +
"skill_parkour = " + player.GetSkillLevel(UnturnedSkill.Parkour) + "," +
"skill_sharpshooter = " + player.GetSkillLevel(UnturnedSkill.Sharpshooter) + "," +
"skill_sneakybeaky = " + player.GetSkillLevel(UnturnedSkill.Sneakybeaky) + "," +
"skill_strength = " + player.GetSkillLevel(UnturnedSkill.Strength) + "," +
"skill_survival = " + player.GetSkillLevel(UnturnedSkill.Survival) + "," +
"skill_toughness = " + player.GetSkillLevel(UnturnedSkill.Toughness) + "," +
"skill_vitality = " + player.GetSkillLevel(UnturnedSkill.Vitality) + "," +
"skill_warmblooded = " + player.GetSkillLevel(UnturnedSkill.Warmblooded) + "," +
"is_hidden = " + Livemap.Instance.IsPlayerHidden(player) + " " +
"WHERE CSteamID = '" + player.CSteamID.ToString() + "'";
MySQLCommand.ExecuteNonQuery();
}
MySQLConnection.Close();
}
catch (Exception ex)
{
Logger.LogException(ex);
}
}
/*
* Read in thread information from the database and display it.
* */
public void ListThreads()
{
System.Web.UI.HtmlControls.HtmlGenericControl newDiv;
Label posterLbl;
Label postLbl;
Label titleLbl;
string posterName;
string postTitle;
string message;
// Need a value but still flag that we have never found a match. The post id will be nonnegative so we are gauranteed.
System.Int64 reply = -1;
int rowsChecked = 0;
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
// Command to search for the start of thread integral posts.
// Forum board = 2 denotes integral boards.
cmd.CommandText = "SELECT COUNT(*) FROM posts";
cmd.Connection = sqlConn;
// Open connection and check amount of posts in the table.
sqlConn.Open();
System.Int64 rowCount = (System.Int64)cmd.ExecuteScalar();
sqlConn.Close();
posterLbl = new Label();
postLbl = new Label();
titleLbl = new Label();
deleteButton = new Button();
deleteButton.Click += Delete_Post_Click;
MySqlCommand postNameCommand = new MySqlCommand();
postNameCommand.CommandType = System.Data.CommandType.Text;
MySqlCommand postMessage = new MySqlCommand();
postMessage.CommandType = System.Data.CommandType.Text;
// Gather information for each div
cmd.CommandText = "SELECT users.username FROM users INNER JOIN posts ON posts.user_id=users.user_id AND post_id = " + Session["Reply"];
postNameCommand.CommandText = "SELECT posts.post_title FROM users INNER JOIN posts ON posts.user_id=users.user_id AND post_id = " + Session["Reply"];
postMessage.CommandText = "SELECT posts.post_message FROM users INNER JOIN posts ON posts.user_id=users.user_id AND post_id = " + Session["Reply"];
cmd.Connection = sqlConn;
postNameCommand.Connection = sqlConn;
postMessage.Connection = sqlConn;
// Open connection and check the row for owner.
sqlConn.Open();
posterName = (string)cmd.ExecuteScalar();
postTitle = (string)postNameCommand.ExecuteScalar();
message = (string)postMessage.ExecuteScalar();
sqlConn.Close();
// Print Info
titleLbl.Text = postTitle;
posterLbl.Text = posterName;
postLbl.Text = message;
// Create div and elements.
newDiv = new System.Web.UI.HtmlControls.HtmlGenericControl("DIV");
newDiv.Controls.Add(titleLbl);
newDiv.Controls.Add(new LiteralControl("<br />"));
newDiv.Controls.Add(posterLbl);
newDiv.Controls.Add(new LiteralControl("<br />"));
newDiv.Controls.Add(new LiteralControl("<br />"));
newDiv.Controls.Add(postLbl);
// Style the div and elements.
newDiv.Style.Value = "border-top-style: dotted; border-color: fixed; border-width: medium; margin-bottom: 60px";
titleLbl.Style.Value = "Font-Size:32px";
titleLbl.Text = Server.HtmlEncode(titleLbl.Text);
posterLbl.Style.Value = "margin-top:40px;";
postLbl.Style.Value = "margin-top:80px;";
postLbl.Text = Server.HtmlEncode(postLbl.Text);
postLbl.Text = postLbl.Text.Replace(Environment.NewLine, "<br/>");
// Add the div to the holder.
forumsPlaceholder.Controls.Add(newDiv);
// Loop through and get replies.
while (rowsChecked < rowCount)
{
// Command to check for each reply.
cmd.CommandText = "SELECT is_start_of_thread FROM posts WHERE post_id = " + idTracker;
cmd.Connection = sqlConn;
sqlConn.Open();
// Check that the id is there for the row.
if (cmd.ExecuteScalar() != null)
{
reply = (int)cmd.ExecuteScalar();
rowsChecked++;
}
sqlConn.Close();
if (reply.ToString() == Session["Reply"].ToString())
{
posterLbl = new Label();
postLbl = new Label();
// Gather information for each div
cmd.CommandText = "SELECT users.username FROM users INNER JOIN posts ON posts.user_id=users.user_id AND post_id = " + idTracker + " AND is_start_of_thread = " + Session["Reply"];
postMessage.CommandText = "SELECT posts.post_message FROM users INNER JOIN posts ON posts.user_id=users.user_id AND post_id = " + idTracker + " AND is_start_of_thread = " + Session["Reply"];
cmd.Connection = sqlConn;
postNameCommand.Connection = sqlConn;
postMessage.Connection = sqlConn;
// Open connection and check the row for owner.
sqlConn.Open();
posterName = (string)cmd.ExecuteScalar();
if (postNameCommand.ExecuteScalar().GetType() != typeof(DBNull))
{
postTitle = (string)postNameCommand.ExecuteScalar();
}
message = (string)postMessage.ExecuteScalar();
sqlConn.Close();
// Print Info
titleLbl.Text = postTitle;
posterLbl.Text = posterName;
postLbl.Text = message;
deleteButton.Text = "Delete";
// Create div and elements.
newDiv = new System.Web.UI.HtmlControls.HtmlGenericControl("DIV");
newDiv.Controls.Add(new LiteralControl("<br />"));
newDiv.Controls.Add(new LiteralControl("<br />"));
newDiv.Controls.Add(posterLbl);
newDiv.Controls.Add(new LiteralControl("<br />"));
newDiv.Controls.Add(new LiteralControl("<br />"));
newDiv.Controls.Add(postLbl);
if (posterName == (string)Session["user"])
{
newDiv.Controls.Add(new LiteralControl("<br />"));
newDiv.Controls.Add(new LiteralControl("<br />"));
newDiv.Controls.Add(deleteButton);
}
// Style the div and elements.
newDiv.Style.Value = "border-top-style: dotted; border-color: fixed; border-width: medium; margin-bottom: 60px";
posterLbl.Style.Value = "margin-top:40px;";
postLbl.Style.Value = "margin-top:80px;";
postLbl.Text = Server.HtmlEncode(postLbl.Text);
postLbl.Text = postLbl.Text.Replace(Environment.NewLine, "<br/>");
deleteButton.ID = idTracker.ToString();
// Add the div to the holder.
forumsPlaceholder.Controls.Add(newDiv);
}
idTracker++;
}
}
/// <summary>
/// Creates the HTML string that will be used to get the table for any individual
/// player
/// </summary>
/// <param name="name">The name of the player</param>
/// <returns></returns>
public string individualTableCreator(String name)
{
// Connect to the DB
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
// Open a connection
conn.Open();
MySqlCommand command = conn.CreateCommand();
command.CommandText = "Select PlayerID, Game, Score, Accuracy From Players";
//Lists used to hold information about the players
List <SessionModel> games = new List <SessionModel>();
List <uint> currGameIDs = new List <uint>();
List <uint> currGameDurs = new List <uint>();
List <uint> currGameScore = new List <uint>();
List <uint> currGameAccuracy = new List <uint>();
// Execute the command and cycle through the DataReader object
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
//Add all the player data to its aplicable table
if (reader["PlayerID"].Equals(name))
{
currGameIDs.Add(uint.Parse(reader["Game"].ToString()));
currGameScore.Add(uint.Parse(reader["Score"].ToString()));
currGameAccuracy.Add(uint.Parse(reader["Accuracy"].ToString()));
}
}
}
//If the player hasn't played any games, the url is invalid
if (currGameIDs.Count == 0)
{
return(errorMessage());
}
//Get the duration for each game the player has played
foreach (uint i in currGameIDs)
{
command.CommandText = "Select Duration From Games Where GameID=" + "'" + i + "'" + "";
currGameDurs.Add(uint.Parse(command.ExecuteScalar().ToString()));
}
//Turn each game the player has played into a session model
//Add those to the games list
for (int i = 0; i < currGameIDs.Count; i++)
{
SessionModel newGame = new SessionModel(currGameIDs[i], currGameDurs[i], currGameScore[i], currGameAccuracy[i]);
games.Add(newGame);
}
return(WebViews.GetPlayerGames(name, games));
}
catch (Exception e)
{
return("Error: " + e.Message);
}
}
}
//Load StatDetails
public void loadStatDetails()
{
//Count Lecturers
con.Open();
try
{
string queryCountLecurers = "SELECT COUNT(EmpID) FROM addlecture";
MySqlCommand sqlCmd = new MySqlCommand(queryCountLecurers, con);
Int32 lecCount = Convert.ToInt32(sqlCmd.ExecuteScalar());
sqlCmd.Dispose();
label6Reg.Text = lecCount.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
//Count StudentGroups
con.Open();
try
{
string queryCountSt_Groups = "SELECT COUNT(GrpNo) FROM addstudentgrp";
MySqlCommand sqlCmd = new MySqlCommand(queryCountSt_Groups, con);
Int32 stGroupCount = Convert.ToInt32(sqlCmd.ExecuteScalar());
sqlCmd.Dispose();
labelStudentGroups.Text = stGroupCount.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
//Count Subjects
con.Open();
try
{
string queryCount_Sub_Groups = "SELECT COUNT(SubjectCode) FROM addsubject";
MySqlCommand sqlCmd = new MySqlCommand(queryCount_Sub_Groups, con);
Int32 subCount = Convert.ToInt32(sqlCmd.ExecuteScalar());
sqlCmd.Dispose();
labelmodules.Text = subCount.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
//Room Count
con.Open();
try
{
string queryCountRoom = "SELECT COUNT(roomName) FROM addlocation";
MySqlCommand sqlCmd = new MySqlCommand(queryCountRoom, con);
Int32 roomCount = Convert.ToInt32(sqlCmd.ExecuteScalar());
sqlCmd.Dispose();
labelLocations.Text = roomCount.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private static void Main(string[] args)
{
TableStructure tableStructure;
EtkaDataReady etkaDataReady;
// Пути к файлам указаны тестово, прикрутить открытие файла
var tableHeaderFileFdt = @"..\..\..\Data\Katalog.fdt";
var fileDateBin = @"..\..\..\Data\KAT005.BIN";
// читаем заголовочный файл, получаем структуру таблицы
tableStructure = ReadTableHeaderFile.Read(tableHeaderFileFdt);
// подготавливаем данные для таблицы (возможно позднее объединю с tableStructure, пока так)
etkaDataReady = new EtkaDataReady(tableStructure);
// создаем метод в котором читается бинарник и асинхронно сохраняется в базу
// параллельно запускаем этот метод сколько раз, сколько бинарников нашли
using (var mySqlConnection = new MySqlCon().GetConntction())
{
var queryIsATable = @"select count(*)
from information_schema.tables
where table_type = 'BASE TABLE' and
table_name = 'test'";
var commandToMySql = new MySqlCommand(queryIsATable, mySqlConnection);
if (Convert.ToInt32(commandToMySql.ExecuteScalar()) > 0)
{
commandToMySql = new MySqlCommand("DROP TABLE test", mySqlConnection);
var a = commandToMySql.ExecuteNonQuery();
}
#if DEBUG
Console.WriteLine("Соединение успешно");
#endif
// создаем столбцы с типами данных из данных из описания таблицы
// создаем строку sql запроса из данный описания таблицы
string sqlCreateColumns = "CREATE TABLE test \n ( \n";
sqlCreateColumns += " Id int";
for (var i = 0; i < etkaDataReady.TableColumnCode.Length; i++)
{
//if (i > 0)
sqlCreateColumns += ",\n "; // вставляем запятую у предыдущей команды и переход на новую строку, если есть дальше данные
sqlCreateColumns += /*"ADD " +*/ etkaDataReady.TableColumnCode[i].Substring(0, 2) + " VARCHAR(200) NULL";
}
sqlCreateColumns += " \n) ";
// Вносим данные в таблицу
commandToMySql = new MySqlCommand(sqlCreateColumns, mySqlConnection);
commandToMySql.ExecuteNonQuery();
var answer = commandToMySql.UpdatedRowSource;
var an = commandToMySql.IsPrepared;
var ans = commandToMySql.EnableCaching;
mySqlConnection.Close();
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
public static int ExecuteSqlTran(System.Collections.Generic.List <CommandInfo> cmdList)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
MySqlCommand cmd = new MySqlCommand();
try
{ int count = 0;
//循环
foreach (CommandInfo myDE in cmdList)
{
string cmdText = myDE.CommandText;
MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
return(0);
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
trans.Rollback();
return(0);
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
trans.Rollback();
return(0);
}
continue;
}
int val = cmd.ExecuteNonQuery();
count += val;
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
trans.Rollback();
return(0);
}
cmd.Parameters.Clear();
}
trans.Commit();
return(count); }
catch
{
trans.Rollback();
throw;
}
}
}
}
/**
* Check Schema / Create Database Tables
*
* This function checks if the required database tables already exist in the database and creates
* them if they do not already exist.
*/
public void CheckSchema()
{
/* `livemap_server` table */
try
{
MySqlConnection MySQLConnection = CreateConnection();
MySqlCommand MySQLCommand = MySQLConnection.CreateCommand();
// check if the table exists
MySQLCommand.CommandText = "SHOW TABLES LIKE '" + Livemap.Instance.Configuration.Instance.DatabaseTableLivemapServer + "'";
MySQLConnection.Open();
object result = MySQLCommand.ExecuteScalar();
if (result == null)
{
// table doesn't exist, create it
MySQLCommand.CommandText = "CREATE TABLE " + Livemap.Instance.Configuration.Instance.DatabaseTableLivemapServer +
"(server_id VARCHAR(32) NOT NULL," +
"server_name VARCHAR(50) NULL," +
"app_version VARCHAR(32) NULL," +
"map VARCHAR(32) NULL," +
"online_players INT(8) NULL," +
"max_players INT(8) NULL," +
"is_pvp TINYINT(1) NULL," +
"is_gold TINYINT(1) NULL," +
"is_pro TINYINT(1) NULL," +
"has_cheats TINYINT(1) NULL," +
"hide_admins TINYINT(1) NULL," +
"cycle_time INT(8) NULL," +
"cycle_length INT(8) NULL," +
"full_moon TINYINT(1) NULL," +
"uptime INT(10) NULL," +
"packets_received INT(10) NULL," +
"packets_sent INT(10) NULL," +
"port INT(8) NULL," +
"mode VARCHAR(32) NULL," +
"last_refresh TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," +
"PRIMARY KEY(server_id));";
MySQLCommand.ExecuteNonQuery();
}
MySQLConnection.Close();
}
catch (Exception ex)
{
Logger.LogException(ex);
}
/* `livemap_data` table */
try
{
MySqlConnection MySQLConnection = CreateConnection();
MySqlCommand MySQLCommand = MySQLConnection.CreateCommand();
// check if the table exists
MySQLCommand.CommandText = "SHOW TABLES LIKE '" + Livemap.Instance.Configuration.Instance.DatabaseTableLivemapData + "'";
MySQLConnection.Open();
object result = MySQLCommand.ExecuteScalar();
if (result == null)
{
// table doesn't exist, create it!
MySQLCommand.CommandText = "CREATE TABLE " + Livemap.Instance.Configuration.Instance.DatabaseTableLivemapData +
"(CSteamID VARCHAR(32) NOT NULL," +
"character_name VARCHAR(100) NULL," +
"display_name VARCHAR(100) NULL," +
"steam_group_id VARCHAR(32) NULL," +
"steam_avatar_medium VARCHAR(255) NULL," +
"server_id VARCHAR(32) NULL," +
"ip_address VARCHAR(255) NULL," +
"ping DECIMAL(8,6) NULL," +
"is_pro TINYINT(1) NULL," +
"is_admin TINYINT(1) NULL," +
"is_god TINYINT(1) NULL," +
"is_vanished TINYINT(1) NULL," +
"in_vehicle TINYINT(1) NULL," +
"vehicle_is_driver TINYINT(1) NULL," +
"vehicle_instance_id INT(8) NULL," +
"vehicle_id INT(8) NULL," +
"vehicle_fuel INT(8) NULL," +
"vehicle_health INT(8) NULL," +
"vehicle_headlights_on TINYINT(1) NULL," +
"vehicle_taillights_on TINYINT(1) NULL," +
"vehicle_sirens_on TINYINT(1) NULL," +
"vehicle_speed INT(8) NULL," +
"vehicle_has_battery TINYINT(1) NULL," +
"vehicle_battery_charge INT(8) NULL," +
"vehicle_exploded TINYINT(1) NULL," +
"vehicle_locked TINYINT(1) NULL," +
"position VARCHAR(32) NULL," +
"rotation FLOAT(7,4) NULL," +
"is_dead TINYINT(1) NULL," +
"last_dead_position VARCHAR(32) NULL," +
"skin_color VARCHAR(32) NULL," +
"hair INT(8) NULL," +
"face INT(8) NULL," +
"beard INT(8) NULL," +
"hat INT(8) NULL," +
"glasses INT(8) NULL," +
"mask INT(8) NULL," +
"is_bleeding TINYINT(1) NULL," +
"is_broken TINYINT(1) NULL," +
"health INT(8) NULL," +
"stamina INT(8) NULL," +
"hunger INT(8) NULL," +
"thirst INT(8) NULL," +
"infection INT(8) NULL," +
"experience INT(8) NULL," +
"reputation INT(8) NULL," +
"skill_agriculture INT(8) NULL," +
"skill_cardio INT(8) NULL," +
"skill_cooking INT(8) NULL," +
"skill_crafting INT(8) NULL," +
"skill_dexerity INT(8) NULL," +
"skill_diving INT(8) NULL," +
"skill_engineer INT(8) NULL," +
"skill_exercise INT(8) NULL," +
"skill_fishing INT(8) NULL," +
"skill_healing INT(8) NULL," +
"skill_immunity INT(8) NULL," +
"skill_mechanic INT(8) NULL," +
"skill_outdoors INT(8) NULL," +
"skill_overkill INT(8) NULL," +
"skill_parkour INT(8) NULL," +
"skill_sharpshooter INT(8) NULL," +
"skill_sneakybeaky INT(8) NULL," +
"skill_strength INT(8) NULL," +
"skill_survival INT(8) NULL," +
"skill_toughness INT(8) NULL," +
"skill_vitality INT(8) NULL," +
"skill_warmblooded INT(8) NULL," +
"is_hidden TINYINT(1) DEFAULT 0," +
"last_refresh TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," +
"last_connect TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
"last_disconnect TIMESTAMP NULL," +
"PRIMARY KEY(CSteamID));";
MySQLCommand.ExecuteNonQuery();
}
MySQLConnection.Close();
}
catch (Exception ex)
{
Logger.LogException(ex);
}
/* `livemap_chat` table */
try
{
MySqlConnection MySQLConnection = CreateConnection();
MySqlCommand MySQLCommand = MySQLConnection.CreateCommand();
// check if the table exists
MySQLCommand.CommandText = "SHOW TABLES LIKE '" + Livemap.Instance.Configuration.Instance.DatabaseTableLivemapChat + "'";
MySQLConnection.Open();
object result = MySQLCommand.ExecuteScalar();
if (result == null)
{
// table doesn't exist, create it
MySQLCommand.CommandText = "CREATE TABLE " + Livemap.Instance.Configuration.Instance.DatabaseTableLivemapChat +
"(id INT(8) NOT NULL AUTO_INCREMENT," +
"server_id VARCHAR(32) NULL," +
"steam_id VARCHAR(50) NULL," +
"message VARCHAR(100) NULL," +
"timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
"PRIMARY KEY(id));";
MySQLCommand.ExecuteNonQuery();
}
MySQLConnection.Close();
}
catch (Exception ex)
{
Logger.LogException(ex);
}
}
public void UnsignedTinyInt()
{
if (Version < new Version(4, 1)) return;
execSQL("CREATE TABLE Test(ID TINYINT UNSIGNED NOT NULL, " +
"Name VARCHAR(50) NOT NULL, PRIMARY KEY (ID), UNIQUE (ID), " +
"UNIQUE (Name))");
execSQL("INSERT INTO Test VALUES ('127', 'name1')");
execSQL("INSERT INTO Test VALUES ('128', 'name2')");
execSQL("INSERT INTO Test VALUES ('255', 'name3')");
string sql = " SELECT count(*) FROM Test WHERE ID = ?id";
MySqlCommand command = new MySqlCommand();
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = (MySqlConnection)conn;
command.Parameters.AddWithValue("?id", (byte)127);
command.Prepare();
object count = command.ExecuteScalar();
Assert.AreEqual(1, count);
command.Parameters.Clear();
command.Parameters.AddWithValue("?id", (byte)128);
count = command.ExecuteScalar();
Assert.AreEqual(1, count);
command.Parameters.Clear();
command.Parameters.AddWithValue("?id", (byte)255);
count = command.ExecuteScalar();
Assert.AreEqual(1, count);
command.Parameters.Clear();
command.Parameters.AddWithValue("?id", "255");
count = command.ExecuteScalar();
Assert.AreEqual(1, count);
}
public void TimeoutDuringBatch()
{
if (Version < new Version(5, 0)) return;
execSQL(@"CREATE PROCEDURE spTest(duration INT)
BEGIN
SELECT SLEEP(duration);
END");
execSQL("CREATE TABLE test (id INT)");
MySqlCommand cmd = new MySqlCommand(
"call spTest(5);INSERT INTO test VALUES(4)", conn);
cmd.CommandTimeout = 2;
try
{
cmd.ExecuteNonQuery();
Assert.Fail("This should have timed out");
}
catch (MySqlException ex)
{
Assert.IsTrue(ex.Message.StartsWith("Timeout expired"), "Message is wrong" +ex);
}
// Check that connection is still usable
MySqlCommand cmd2 = new MySqlCommand("select 10", conn);
long res = (long)cmd2.ExecuteScalar();
Assert.AreEqual(10, res);
}
public List <Adelantos> obtenerFiltrado(ItemFiltro[] itemFiltro,
ItemOrden[] orden, bool busquedaAnd, double inicio, double fin, out double totalRegistros)
{
List <Adelantos> ret = new List <Adelantos>();
UltimoMensaje = null;
MySqlConnection conexion = Database.obtenerConexion(true);
MySqlCommand comando = new MySqlCommand();
comando.Connection = conexion;
comando.Transaction = Database.obtenerTransaccion();
totalRegistros = 0;
int parameterCount = 0;
string where = "";
string tipoBusqueda = " AND ";
if (!busquedaAnd)
{
tipoBusqueda = " OR ";
}
Varios.armarConsultaFiltros(itemFiltro, comando, ref parameterCount, ref where, tipoBusqueda);
string cadenaOrden = "";
comando.CommandText = "SELECT count(*) FROM (SELECT CONCAT(empleados.apellido, ', ', empleados.nombre) AS empleado, empleados_adelantos.adelanto_id, empleados_adelantos.empleado_id, empleados_adelantos.fecha, empleados_adelantos.descripcion, " +
"empleados_adelantos.importe FROM empleados_adelantos join empleados on empleados_adelantos.empleado_id=empleados.id) as base " + where;
try
{
if (Database.obtenerTransaccion() == null)
{
conexion.Open();
}
double.TryParse(comando.ExecuteScalar().ToString(), out totalRegistros);
if (inicio < 0)
{
inicio = 0;
}
if (inicio > totalRegistros)
{
inicio = totalRegistros - 1;
}
if (fin > totalRegistros || fin == -1)
{
fin = totalRegistros;
}
if (inicio < 1)
{
inicio = 1;
}
if (fin < 1)
{
fin = 1;
}
cadenaOrden = Varios.armarCadenaOrden(orden, cadenaOrden, dbCampoEmpleadoID);
//TODO: Hacer Paginacion
double rowcount = fin - (inicio - 1);
comando.CommandText = "SELECT * from (select empleados_adelantos.adelanto_id, empleados_adelantos.empleado_id, " +
"CONCAT(empleados.apellido, ', ', empleados.nombre) AS empleado, empleados_adelantos.fecha, empleados_adelantos.descripcion, " +
"empleados_adelantos.importe from empleados_adelantos join empleados on empleados_adelantos.empleado_id=empleados.id) as base " +
where + " "
+ cadenaOrden + ""
+ " LIMIT " + (inicio - 1) + ", " + rowcount;
MySqlDataReader dr = comando.ExecuteReader();
while (dr.Read())
{
Adelantos bar = new Adelantos();
bar.Subscribe(this);
cargarDatos(bar, dr);
ret.Add(bar);
}
dr.Close();
}
catch (Exception ex)
{
UltimoMensaje = GestionErrores.obtenerError(ex);
UltimoMensaje.cargar(
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.ToString(),
System.Reflection.MethodBase.GetCurrentMethod().ToString(),
new System.Diagnostics.StackFrame(0, true).GetFileLineNumber());
UltimoMensaje.EsError = true;
Notify(UltimoMensaje);
}
finally
{
comando.Parameters.Clear();
if (Database.obtenerTransaccion() == null)
{
if (conexion.State != ConnectionState.Closed)
{
conexion.Close();
}
}
}
return(ret);
}
public void WaitTimeoutExpiring()
{
string connStr = GetConnectionString(true);
MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder(connStr);
if (sb.ConnectionProtocol == MySqlConnectionProtocol.NamedPipe)
// wait timeout does not work for named pipe connections
return;
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
c.StateChange += new StateChangeEventHandler(c_StateChange);
// set the session wait timeout on this new connection
MySqlCommand cmd = new MySqlCommand("SET SESSION interactive_timeout=3", c);
cmd.ExecuteNonQuery();
cmd.CommandText = "SET SESSION wait_timeout=2";
cmd.ExecuteNonQuery();
stateChangeCount = 0;
// now wait 4 seconds
Thread.Sleep(4000);
try
{
cmd.CommandText = "SELECT now()";
cmd.ExecuteScalar();
}
catch (Exception ex)
{
Assert.IsTrue(ex.Message.StartsWith("Fatal"));
}
Assert.AreEqual(1, stateChangeCount);
Assert.AreEqual(ConnectionState.Closed, c.State);
}
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("SELECT now() as thetime, database() as db", c);
using (MySqlDataReader r = cmd.ExecuteReader())
{
Assert.IsTrue(r.Read());
}
}
}
public void VariousCollations()
{
if (version < new Version(4, 1)) return;
execSQL("CREATE TABLE `test_tbl` ( `test` VARCHAR( 255 ) NOT NULL) CHARACTER SET utf8 COLLATE utf8_swedish_ci TYPE = MYISAM");
execSQL("INSERT INTO test_tbl VALUES ('myval')");
try
{
MySqlCommand cmd = new MySqlCommand("SELECT test FROM test_tbl", conn);
cmd.ExecuteScalar();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
execSQL("DROP TABLE test_tbl");
}
}
public void ResetCommandText()
{
execSQL("CREATE TABLE Test (id int, name varchar(100))");
execSQL("INSERT INTO Test VALUES (1, 'Test')");
MySqlCommand cmd = new MySqlCommand("SELECT id FROM Test", conn);
cmd.Prepare();
object o = cmd.ExecuteScalar();
Assert.AreEqual(1, o);
cmd.CommandText = "SELECT name FROM Test";
cmd.Prepare();
o = cmd.ExecuteScalar();
Assert.AreEqual("Test", o);
}
private void btnAddEmployee_Click(object sender, EventArgs e)
{
try
{
byte[] img = null;
if (ImageBox.Image != null)
{
MemoryStream ms = new MemoryStream();
ImageBox.Image.Save(ms, ImageBox.Image.RawFormat);
img = ms.ToArray();
}
double x;
if (txtSalary.Text == "")
{
x = 0;
}
else
{
x = Double.Parse(txtSalary.Text);
}
dbconnection.Open();
if (rEmployee.Checked)
{
#region Add New Employee
string insert = "INSERT INTO Employee (Employee_Number,Employee_Name,Employee_Phone,Employee_Address,Employee_Info,Employee_Qualification,Employee_Start_Date,Employee_Job,Department_ID,Employee_Birth_Date,Employee_Salary,Employee_Mail,Branch_ID,Employee_Photo,National_ID,Social_Status,SocialInsuranceNumber,EmploymentType,ExperienceYears) VALUES (@Employee_Number,@Employee_Name,@Employee_Phone,@Employee_Address,@Employee_Info,@Employee_Qualification,@Employee_Start,@Employee_Job,@Department_ID,@Employee_Birth,@Employee_Salary,@Employee_Mail,@Branch_ID,@Employee_Photo,@National_ID,@Social_Status,@SocialInsuranceNumber,@EmploymentType,@ExperienceYears)";
MySqlCommand cmd = new MySqlCommand(insert, dbconnection);
cmd.Parameters.Add("@Employee_Number", MySqlDbType.Int16);
if (txtEmployeeNumber.Text != "")
{
cmd.Parameters["@Employee_Number"].Value = Convert.ToInt32(txtEmployeeNumber.Text);
labNumberReqired.Visible = false;
}
else
{
txtEmployeeNumber.Focus();
labNumberReqired.Visible = true;
dbconnection.Close();
return;
}
cmd.Parameters.Add("@Employee_Name", MySqlDbType.VarChar, 255);
if (txtEmployeeName.Text != "")
{
cmd.Parameters["@Employee_Name"].Value = txtEmployeeName.Text;
labName.Visible = false;
}
else
{
txtEmployeeName.Focus();
labName.Visible = true;
dbconnection.Close();
return;
}
cmd.Parameters.Add("@National_ID", MySqlDbType.VarChar, 255);
cmd.Parameters["@National_ID"].Value = txtNationalID.Text;
cmd.Parameters.Add("@Employee_Phone", MySqlDbType.VarChar, 255);
cmd.Parameters["@Employee_Phone"].Value = txtPhone.Text;
cmd.Parameters.Add("@Employee_Address", MySqlDbType.VarChar, 255);
cmd.Parameters["@Employee_Address"].Value = txtAddress.Text;
cmd.Parameters.Add("@Employee_Qualification", MySqlDbType.VarChar, 255);
cmd.Parameters["@Employee_Qualification"].Value = txtQualification.Text;
cmd.Parameters.Add("@Employee_Start", MySqlDbType.Date, 0);
cmd.Parameters["@Employee_Start"].Value = dateTimePickerStartDate.Value;
cmd.Parameters.Add("@Employee_Job", MySqlDbType.VarChar, 255);
cmd.Parameters["@Employee_Job"].Value = txtJob.Text;
cmd.Parameters.Add("@Employee_Salary", MySqlDbType.Decimal, 10);
cmd.Parameters["@Employee_Salary"].Value = x;
cmd.Parameters.Add("@Employee_Birth", MySqlDbType.Date, 0);
cmd.Parameters["@Employee_Birth"].Value = dateTimePickerBirthDate.Value;
cmd.Parameters.Add("@Employee_Mail", MySqlDbType.VarChar, 255);
cmd.Parameters["@Employee_Mail"].Value = txtMail.Text;
cmd.Parameters.Add("@Branch_ID", MySqlDbType.Int16);
//if (UserControl.userType == 1)
//{
// cmd.Parameters["@Branch_ID"].Value = null;
// labelBranch.Visible = false;
//}
//else
if (comBranch.Text != "")
{
cmd.Parameters["@Branch_ID"].Value = comBranch.SelectedValue;
labelBranch.Visible = false;
}
else
{
comBranch.Focus();
labelBranch.Visible = true;
dbconnection.Close();
return;
}
cmd.Parameters.Add("@Department_ID", MySqlDbType.Int16);
if (comDepartment.Text != "")
{
cmd.Parameters["@Department_ID"].Value = comDepartment.SelectedValue;
labelDepartement.Visible = false;
}
else
{
comDepartment.Focus();
labelDepartement.Visible = true;
dbconnection.Close();
return;
}
cmd.Parameters.Add("@Employee_Info", MySqlDbType.VarChar, 255);
cmd.Parameters["@Employee_Info"].Value = txtNotes.Text;
cmd.Parameters.Add("@Employee_Photo", MySqlDbType.Blob);
cmd.Parameters["@Employee_Photo"].Value = img;
cmd.Parameters.Add("@Social_Status", MySqlDbType.VarChar, 255);
cmd.Parameters["@Social_Status"].Value = txtSocialStatus.Text;
cmd.Parameters.Add("@SocialInsuranceNumber", MySqlDbType.Int16);
if (txtSocialInsuranceNumber.Text != "")
{
cmd.Parameters["@SocialInsuranceNumber"].Value = Convert.ToInt32(txtSocialInsuranceNumber.Text);
}
else
{
cmd.Parameters["@SocialInsuranceNumber"].Value = 0;
}
cmd.Parameters.Add("@EmploymentType", MySqlDbType.VarChar, 255);
cmd.Parameters["@EmploymentType"].Value = txtWorkType.Text;
cmd.Parameters.Add("@ExperienceYears", MySqlDbType.Int16);
if (txtExperienceYears.Text != "")
{
cmd.Parameters["@ExperienceYears"].Value = Convert.ToInt32(txtExperienceYears.Text);
}
else
{
cmd.Parameters["@ExperienceYears"].Value = 0;
}
#endregion
if (cmd.ExecuteNonQuery() == 1)
{
//MessageBox.Show("تم ادخال البيانات بنجاح");
employees.displayEmployee();
clear();
string query = "select Employee_ID from employee order by Employee_ID desc limit 1";
MySqlCommand com = new MySqlCommand(query, dbconnection);
UserControl.ItemRecord("employee", "اضافة", Convert.ToInt32(com.ExecuteScalar().ToString()), DateTime.Now, "", dbconnection);
XtraTabPage xtraTabPage = getTabPage("أضافة موظف");
xtraTabPage.ImageOptions.Image = null;
}
}
else if (rDelegate.Checked)
{
#region Add New Delegate
string insert = "INSERT INTO Delegate (Delegate_Number,Delegate_Name,Delegate_Phone,Delegate_Address,Delegate_Info,Delegate_Qualification,Delegate_Start_Date,Delegate_Job,Department_ID,Delegate_Birth_Date,Delegate_Salary,Delegate_Mail,Branch_ID,Delegate_Photo,National_ID,Social_Status,SocialInsuranceNumber,EmploymentType,ExperienceYears,Delegate_Taraget) VALUES (@Delegate_Number,@Delegate_Name,@Delegate_Phone,@Delegate_Address,@Delegate_Info,@Delegate_Qualification,@Delegate_Start,@Delegate_Job,@Department_ID,@Delegate_Birth,@Delegate_Salary,@Delegate_Mail,@Branch_ID,@Delegate_Photo,@National_ID,@Social_Status,@SocialInsuranceNumber,@EmploymentType,@ExperienceYears,@Delegate_Taraget)";
MySqlCommand cmd = new MySqlCommand(insert, dbconnection);
cmd.Parameters.Add("@Delegate_Number", MySqlDbType.Int16);
if (txtEmployeeNumber.Text != "")
{
cmd.Parameters["@Delegate_Number"].Value = Convert.ToInt32(txtEmployeeNumber.Text);
labNumberReqired.Visible = false;
}
else
{
txtEmployeeNumber.Focus();
labNumberReqired.Visible = true;
dbconnection.Close();
return;
}
cmd.Parameters.Add("@Delegate_Name", MySqlDbType.VarChar, 255);
if (txtEmployeeName.Text != "")
{
cmd.Parameters["@Delegate_Name"].Value = txtEmployeeName.Text;
labName.Visible = false;
}
else
{
txtEmployeeName.Focus();
labName.Visible = true;
dbconnection.Close();
return;
}
cmd.Parameters.Add("@National_ID", MySqlDbType.VarChar, 255);
cmd.Parameters["@National_ID"].Value = txtNationalID.Text;
cmd.Parameters.Add("@Delegate_Phone", MySqlDbType.VarChar, 255);
cmd.Parameters["@Delegate_Phone"].Value = txtPhone.Text;
cmd.Parameters.Add("@Delegate_Address", MySqlDbType.VarChar, 255);
cmd.Parameters["@Delegate_Address"].Value = txtAddress.Text;
cmd.Parameters.Add("@Delegate_Qualification", MySqlDbType.VarChar, 255);
cmd.Parameters["@Delegate_Qualification"].Value = txtQualification.Text;
cmd.Parameters.Add("@Delegate_Start", MySqlDbType.Date, 0);
cmd.Parameters["@Delegate_Start"].Value = dateTimePickerStartDate.Value;
cmd.Parameters.Add("@Delegate_Job", MySqlDbType.VarChar, 255);
cmd.Parameters["@Delegate_Job"].Value = txtJob.Text;
cmd.Parameters.Add("@Delegate_Salary", MySqlDbType.Decimal, 10);
cmd.Parameters["@Delegate_Salary"].Value = x;
cmd.Parameters.Add("@Delegate_Birth", MySqlDbType.Date, 0);
cmd.Parameters["@Delegate_Birth"].Value = dateTimePickerBirthDate.Value;
cmd.Parameters.Add("@Delegate_Mail", MySqlDbType.VarChar, 255);
cmd.Parameters["@Delegate_Mail"].Value = txtMail.Text;
cmd.Parameters.Add("@Branch_ID", MySqlDbType.Int16);
if (comBranch.Text != "")
{
cmd.Parameters["@Branch_ID"].Value = comBranch.SelectedValue;
labelBranch.Visible = false;
}
else
{
comBranch.Focus();
labelBranch.Visible = true;
dbconnection.Close();
return;
}
cmd.Parameters.Add("@Department_ID", MySqlDbType.Int16);
cmd.Parameters["@Department_ID"].Value = comDepartment.SelectedValue;
cmd.Parameters.Add("@Delegate_Info", MySqlDbType.VarChar, 255);
cmd.Parameters["@Delegate_Info"].Value = txtNotes.Text;
cmd.Parameters.Add("@Delegate_Photo", MySqlDbType.Blob);
cmd.Parameters["@Delegate_Photo"].Value = img;
cmd.Parameters.Add("@Social_Status", MySqlDbType.VarChar, 255);
cmd.Parameters["@Social_Status"].Value = txtSocialStatus.Text;
cmd.Parameters.Add("@Delegate_Taraget", MySqlDbType.Decimal, 10);
if (txtTaraget.Text != "")
{
cmd.Parameters["@Delegate_Taraget"].Value = Convert.ToDouble(txtTaraget.Text);
}
else
{
cmd.Parameters["@Delegate_Taraget"].Value = 0;
}
cmd.Parameters.Add("@SocialInsuranceNumber", MySqlDbType.Int16);
if (txtSocialInsuranceNumber.Text != "")
{
cmd.Parameters["@SocialInsuranceNumber"].Value = Convert.ToInt32(txtSocialInsuranceNumber.Text);
}
else
{
cmd.Parameters["@SocialInsuranceNumber"].Value = 0;
}
cmd.Parameters.Add("@EmploymentType", MySqlDbType.VarChar, 255);
cmd.Parameters["@EmploymentType"].Value = txtWorkType.Text;
cmd.Parameters.Add("@ExperienceYears", MySqlDbType.Int16);
if (txtExperienceYears.Text != "")
{
cmd.Parameters["@ExperienceYears"].Value = Convert.ToInt32(txtExperienceYears.Text);
}
else
{
cmd.Parameters["@ExperienceYears"].Value = 0;
}
#endregion
if (cmd.ExecuteNonQuery() == 1)
{
//MessageBox.Show("تم ادخال البيانات بنجاح");
employees.displayEmployee();
clear();
string query = "select Delegate_ID from delegate order by Delegate_ID desc limit 1";
MySqlCommand com = new MySqlCommand(query, dbconnection);
UserControl.ItemRecord("delegate", "اضافة", Convert.ToInt32(com.ExecuteScalar().ToString()), DateTime.Now, "", dbconnection);
XtraTabPage xtraTabPage = getTabPage("أضافة موظف");
xtraTabPage.ImageOptions.Image = null;
}
}
}
catch
{
MessageBox.Show("يوجد خطأ في البيانات");
}
dbconnection.Close();
}
public void InsertAccentedCharacters()
{
if (Version < new Version(4, 1)) return;
execSQL("CREATE TABLE Test (id INT UNSIGNED NOT NULL PRIMARY KEY " +
"AUTO_INCREMENT, input TEXT NOT NULL) CHARACTER SET UTF8");
// COLLATE " +
//"utf8_bin");
using (MySqlConnection conn2 = new MySqlConnection(GetConnectionString(true) +
";charset=utf8"))
{
conn2.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test(input) " +
"VALUES (?input) ON DUPLICATE KEY UPDATE " +
"id=LAST_INSERT_ID(id)", conn2);
cmd.Parameters.Add(new MySqlParameter("?input", ""));
cmd.Prepare();
cmd.Parameters[0].Value = "irache martí[email protected] aol.com";
cmd.ExecuteNonQuery();
MySqlCommand cmd2 = new MySqlCommand("SELECT input FROM Test", conn2);
Assert.AreEqual("irache martí[email protected] aol.com",
cmd2.ExecuteScalar());
}
}
private void btnCancel_Click(object sender, EventArgs e)
{
if (UserControl.userType == 1 || UserControl.userType == 6 || UserControl.userType == 7 || UserControl.userType == 19 || UserControl.userType == 24)
{
if (TransferProductID > 0)
{
if (WithBill == true && UserControl.userType == 24)
{
return;
}
else if (WithBill == false && UserControl.userType == 6)
{
return;
}
if (MessageBox.Show("هل انت متاكد انك تريد الالغاء؟", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
try
{
conn.Open();
if (!WithBill)
{
connectionReader1.Open();
string query = "SELECT data.Data_ID,transfer_product_details.Quantity as 'الكمية',transfer_product_details.CustomerBill_ID,transfer_product.From_Store,transfer_product.To_Store FROM transfer_product_details INNER JOIN transfer_product ON transfer_product_details.TransferProduct_ID = transfer_product.TransferProduct_ID left JOIN store as storeTo ON storeTo.Store_ID = transfer_product.To_Store left join store as storeFrom on storeFrom.Store_ID = transfer_product.From_Store left JOIN customer_bill ON transfer_product_details.CustomerBill_ID = customer_bill.CustomerBill_ID INNER JOIN data ON transfer_product_details.Data_ID = data.Data_ID LEFT JOIN color ON color.Color_ID = data.Color_ID LEFT JOIN size ON size.Size_ID = data.Size_ID LEFT JOIN sort ON sort.Sort_ID = data.Sort_ID INNER JOIN groupo ON data.Group_ID = groupo.Group_ID INNER JOIN factory ON factory.Factory_ID = data.Factory_ID INNER JOIN product ON product.Product_ID = data.Product_ID INNER JOIN type ON type.Type_ID = data.Type_ID where transfer_product.TransferProduct_ID=" + TransferProductID + " and transfer_product.Canceled=0 order by SUBSTR(data.Code, 1, 16),color.Color_Name,data.Description,data.Sort_ID";
MySqlCommand com = new MySqlCommand(query, connectionReader1);
MySqlDataReader dr = com.ExecuteReader();
while (dr.Read())
{
if (dr["CustomerBill_ID"].ToString() == "0")
{
query = "select sum(Total_Meters) from storage where Data_ID=" + dr[0].ToString() + " and Store_ID=" + dr["From_Store"].ToString() + " group by Data_ID";
com = new MySqlCommand(query, conn);
double quantity = Convert.ToDouble(com.ExecuteScalar().ToString());
double meters = quantity + Convert.ToDouble(dr["الكمية"].ToString());
query = "update storage set Total_Meters=" + meters + " where Data_ID=" + dr[0].ToString() + " and Store_ID=" + dr["From_Store"].ToString();
com = new MySqlCommand(query, conn);
com.ExecuteNonQuery();
query = "select sum(Total_Meters) from storage where Data_ID=" + dr[0].ToString() + " and Store_ID=" + dr["To_Store"].ToString() + " group by Data_ID";
com = new MySqlCommand(query, conn);
quantity = Convert.ToDouble(com.ExecuteScalar().ToString());
meters = quantity - Convert.ToDouble(dr["الكمية"].ToString());
query = "update storage set Total_Meters=" + meters + " where Data_ID=" + dr[0].ToString() + " and Store_ID=" + dr["To_Store"].ToString();
com = new MySqlCommand(query, conn);
com.ExecuteNonQuery();
}
}
dr.Close();
}
string query2 = "update transfer_product set Canceled=1 where TransferProduct_ID=" + TransferProductID;
MySqlCommand com2 = new MySqlCommand(query2, conn);
com2.ExecuteNonQuery();
txtBillNum.Text = "";
clearCom();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
conn.Close();
connectionReader1.Close();
}
}
else
{
MessageBox.Show("يجب تحديد التحويل المراد حذفه");
}
}
}
public void NonLatin1Exception()
{
string connStr = GetConnectionString(true) + ";charset=utf8";
execSQL("CREATE TABLE Test (id int)");
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
try
{
MySqlCommand cmd = new MySqlCommand("select `Numéro` from Test", c);
cmd.ExecuteScalar();
}
catch (Exception ex)
{
Assert.AreEqual("Unknown column 'Numéro' in 'field list'", ex.Message);
}
}
}
public void manual()
{
if (dataGridView1.SelectedRows != null && dataGridView2.SelectedRows != null)
{
myConnection.Open();
MySqlCommand command = new MySqlCommand();
command = myConnection.CreateCommand();
try
{
Console.WriteLine(PStatus);
if (((PStatus.Equals("Partial") && alloc < 4) || (PStatus.Equals("Full") && alloc < 8)) && SStatus.Equals("Not Assigned"))
{
String s = "insert into allocations values(" + PID + "," + UOB1 + ")";
command.CommandText = s;
command.ExecuteNonQuery();
String s1 = "Update students set status ='Assigned' where uob=" + UOB1;
command.CommandText = s1;
command.ExecuteNonQuery();
String s2 = "Update pat set Allocations = " + (alloc + 1) + " where ID = " + PID;
command.CommandText = s2;
command.ExecuteNonQuery();
myConnection.Close();
connect();
}
else if (((PStatus.Equals("Partial") && alloc < 4) || (PStatus.Equals("Full") && alloc < 8)) && SStatus.Equals("Assigned"))
{
var result = MessageBox.Show("Are you sure you want re-assign this student", "Confirm Yes", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
try {
command.CommandText = "select PAT_ID from allocations where UOB='" + UOB1 + "'";
int oid = Convert.ToInt32(command.ExecuteScalar().ToString());
command.CommandText = "select Name from pat where ID='" + oid + "'";
String opat = command.ExecuteScalar().ToString();
command.CommandText = "select Name from students where UOB='" + UOB1 + "'";
String student = command.ExecuteScalar().ToString();
command.CommandText = "select Name from pat where ID='" + PID + "'";
String npat = command.ExecuteScalar().ToString();
String sms = "Request has been recieved to change the PAT of " + student + " UOB: " + UOB1 + " to " + npat + " from " + opat;
MailClass mail = new MailClass("*****@*****.**", sms);
command.CommandText = "insert into request values('" + UOB1 + "','" + student + "','" + PID + "','" + npat + "','" + oid + "','" + opat + "')";
command.ExecuteNonQuery();
}
catch (Exception a)
{
MessageBox.Show("Error:" + a);
}
}
}
else if (((PStatus.Equals("Partial") && alloc == 4) || (PStatus.Equals("Full") && alloc == 8)) && SStatus.Equals("Assigned"))
{
MessageBox.Show("You can't assign Student to this PAT, because allocations to this PAT is already complete.");
}
else
{
MessageBox.Show("select student and pat to assign");
}
}
catch (Exception e)
{
MessageBox.Show("select student and pat to assign");
}
myConnection.Close();
}
else
{
MessageBox.Show("select student and pat to assign");
}
}
private void txtBox_KeyDown(object sender, KeyEventArgs e)
{
TextBox txtBox = (TextBox)sender;
string query;
MySqlCommand com;
string Name;
if (e.KeyCode == Keys.Enter)
{
try
{
if (txtBox.Text != "")
{
dbconnection.Open();
switch (txtBox.Name)
{
case "txtFactory":
query = "select DISTINCT Factory_Name from sets inner join factory on sets.Factory_ID=factory.Factory_ID where sets.Factory_ID=" + txtFactory.Text + "";
com = new MySqlCommand(query, dbconnection);
if (com.ExecuteScalar() != null)
{
Name = (string)com.ExecuteScalar();
comFactory.Text = Name;
Search();
//txtGroup.Focus();
}
else
{
MessageBox.Show("there is no item with this id");
dbconnection.Close();
return;
}
break;
case "txtType":
query = "select DISTINCT Type_Name from sets inner join type on sets.Type_ID=type.Type_ID where sets.Type_ID=" + txtType.Text + "";
com = new MySqlCommand(query, dbconnection);
if (com.ExecuteScalar() != null)
{
Name = (string)com.ExecuteScalar();
comType.Text = Name;
Search();
txtFactory.Focus();
}
else
{
MessageBox.Show("there is no item with this id");
dbconnection.Close();
return;
}
break;
//case "txtGroup":
// query = "select DISTINCT Group_Name from sets inner join groupo on sets.Group_ID=groupo.Group_ID where sets.Group_ID=" + txtGroup.Text + "";
// com = new MySqlCommand(query, dbconnection);
// if (com.ExecuteScalar() != null)
// {
// Name = (string)com.ExecuteScalar();
// comGroup.Text = Name;
// Search();
// txtSetsID.Focus();
// }
// else
// {
// MessageBox.Show("there is no item with this id");
// dbconnection.Close();
// return;
// }
// break;
case "txtStoreID":
query = "select Store_Name from store where Store_ID=" + txtStoreID.Text + "";
com = new MySqlCommand(query, dbconnection);
if (com.ExecuteScalar() != null)
{
Name = (string)com.ExecuteScalar();
comStore.Text = Name;
txtType.Focus();
}
else
{
MessageBox.Show("there is no item with this id");
dbconnection.Close();
return;
}
break;
case "txtSetsID":
if (flag)
{
dbconnection.Close();
dbconnection.Open();
if (txtStoreID.Text != "")
{
query = "select Set_Name from sets where Set_ID=" + txtSetsID.Text + "";
com = new MySqlCommand(query, dbconnection);
if (com.ExecuteScalar() != null)
{
Name = (string)com.ExecuteScalar();
comSets.Text = Name;
int id;
if (int.TryParse(txtSetsID.Text, out id))
{
double q = Tagme3Set(id);
txtSetQuantity.Text = q.ToString();
decreaseItemsQuantity(q, id);
}
else
{
MessageBox.Show("enter correct value");
}
txtStoreID.Focus();
}
else
{
MessageBox.Show("there is no item with this id");
dbconnection.Close();
return;
}
}
else
{
MessageBox.Show("حدد المخزن");
}
}
break;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
dbconnection.Close();
}
}
void loadStatus()
{
for (int i = 0; i < gridView1.RowCount; i++)
{
dbconnection.Open();
TimeSpan stattime = new TimeSpan();
TimeSpan worktime = new TimeSpan();
string query = "SELECT attendance.Status FROM attendance where Delegate_ID=" + gridView1.GetRowCellValue(i, colDelegateID).ToString() + " and date(attendance.Attendance_Date)='" + DateTime.Now.Date.ToString("yyyy-MM-dd") + "'";
MySqlCommand command = new MySqlCommand(query, dbconnection);
if (command.ExecuteScalar() != null)
{
string status = command.ExecuteScalar().ToString();
if (status == "متاح")
{
gridView1.SelectRow(i);
gridView1.SetRowCellValue(i, colStatus, 1);
gridView1.Columns[3].ColumnEdit = repositoryItemButtonEdit1;
}
else if (status == "مشغول")
{
gridView1.SelectRow(i);
gridView1.SetRowCellValue(i, colStatus, 2);
gridView1.Columns[3].ColumnEdit = repositoryItemButtonEdit1;
}
else if (status == "استراحة")
{
gridView1.SelectRow(i);
gridView1.SetRowCellValue(i, colStatus, 3);
gridView1.Columns[3].ColumnEdit = repositoryItemButtonEdit1;
}
dbconnection5.Open();
MySqlCommand adapter2 = new MySqlCommand("SELECT cast(Status_Duration as time) as 'Status_Duration',cast(Work_Duration as time) as 'Work_Duration' FROM attendance where attendance.Delegate_ID=" + gridView1.GetRowCellValue(i, colDelegateID).ToString() + " and DATE_FORMAT(attendance.Attendance_Date,'%Y-%m-%d') ='" + DateTime.Now.Date.ToString("yyyy-MM-dd") + "'", dbconnection5);
MySqlDataReader dr2 = adapter2.ExecuteReader();
if (dr2.HasRows)
{
while (dr2.Read())
{
if (TimeSpan.TryParse(dr2["Status_Duration"].ToString(), out stattime))
{
}
if (TimeSpan.TryParse(dr2["Work_Duration"].ToString(), out worktime))
{
}
gridView1.SetRowCellValue(i, colTimer, stattime);
gridView1.SetRowCellValue(i, colWorkTimer, worktime);
}
dr2.Close();
}
dbconnection5.Close();
}
else
{
gridView1.UnselectRow(i);
gridView1.SetRowCellValue(i, colStatus, -1);
gridView1.Columns[3].ColumnEdit = repositoryItemButtonEdit1;
gridView1.SetRowCellValue(i, colTimer, stattime);
gridView1.SetRowCellValue(i, colWorkTimer, worktime);
}
dbconnection.Close();
}
loaded = true;
}
public void UTF8Parameters()
{
execSQL("DROP TABLE IF EXISTS test");
execSQL("CREATE TABLE test (id int(11) NOT NULL, " +
"value varchar(100) NOT NULL, PRIMARY KEY (id)) " +
"ENGINE=MyISAM DEFAULT CHARSET=utf8");
string conString = GetConnectionString(true) + ";charset=utf8";
try
{
using (MySqlConnection con = new MySqlConnection(conString))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES (1, 'šđč枊ĐČĆŽ')", con);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT id FROM test WHERE value = ?parameter";
cmd.Parameters.Add("?parameter", MySqlDbType.VarString);
cmd.Parameters[0].Value = "šđč枊ĐČĆŽ";
object o = cmd.ExecuteScalar();
Assert.AreEqual(1, o);
}
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
}
private void txtBox_KeyDown(object sender, KeyEventArgs e)
{
TextBox txtBox = (TextBox)sender;
string query;
MySqlCommand com;
string Name;
if (e.KeyCode == Keys.Enter)
{
try
{
if (txtBox.Text != "")
{
radNewBill.Visible = false;
radOldBill.Visible = false;
txtBill.Visible = false;
labBill.Visible = false;
txtBill.Text = "";
txtBill.Enabled = false;
btnStart.Visible = false;
dbconnection.Open();
switch (txtBox.Name)
{
case "txtClientID":
query = "select Customer_Name from customer where Customer_ID=" + txtClientID.Text + "";
com = new MySqlCommand(query, dbconnection);
if (com.ExecuteScalar() != null)
{
Name = (string)com.ExecuteScalar();
dbconnection.Close();
comClient.Text = Name;
comClient.SelectedValue = txtClientID.Text;
}
else
{
MessageBox.Show("there is no item with this id");
dbconnection.Close();
return;
}
break;
case "txtCustomerID":
query = "select Customer_Name from customer where Customer_ID=" + txtCustomerID.Text + "";
com = new MySqlCommand(query, dbconnection);
if (com.ExecuteScalar() != null)
{
Name = (string)com.ExecuteScalar();
dbconnection.Close();
comEngCon.Text = Name;
comEngCon.SelectedValue = txtCustomerID.Text;
}
else
{
MessageBox.Show("there is no item with this id");
dbconnection.Close();
return;
}
break;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
dbconnection.Close();
}
}
/// <summary>
/// 执行MySql和Oracle滴混合事务
/// </summary>
/// <param name="list">SQL命令行列表</param>
/// <param name="oracleCmdSqlList">Oracle命令行列表</param>
/// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
public static int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
MySqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo myDE in list)
{
string cmdText = myDE.CommandText;
MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (isHave)
{
//引发事件
myDE.OnSolicitationEvent();
}
}
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
//return 0;
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
//return 0;
}
continue;
}
int val = cmd.ExecuteNonQuery();
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
//return 0;
}
cmd.Parameters.Clear();
}
string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
if (!res)
{
tx.Rollback();
throw new Exception("执行失败");
// return -1;
}
tx.Commit();
return(1);
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
tx.Rollback();
throw e;
}
catch (Exception e)
{
tx.Rollback();
throw e;
}
}
}
private void SetPrescription(object sender, EventArgs e)
{
Messages msg = new Messages();
iin = TextIIN.Text;
conn.Open();
switch (sw)
{
case 1:
if (CBSetName1.Text == "")
{
msg.DataError("Заполните данные!");
}
else
{
sql = "SELECT ID_MEDICINE FROM DRUG WHERE DRUG_NAME = '" + CBSetName1.Text + "'";
MySqlCommand med1 = new MySqlCommand(sql, conn);
m1 = Convert.ToInt32(med1.ExecuteScalar());
sql = "SELECT COUNT(*) FROM FORBIDDEN_DRUG " +
"WHERE PERSON_PERSON_IIN = '" + iin + "' " +
"AND MEDICINE_ID_MEDICINE = " + m1 + "";
MySqlCommand c1 = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(c1.ExecuteScalar());
if (COUNT == 0)
{
sql = "INSERT INTO PRESCRIPTION (MEDICINE_ID_MEDICINE, PRESCRIPTION_DATE_START, PERSON_PERSON_IIN, DOCTOR_ID_DOCTOR) " +
"VALUES (" + m1 + ",'" + date + "', '" + iin + "', " + did + ")";
MySqlCommand inspr1 = new MySqlCommand(sql, conn);
if (inspr1.ExecuteNonQuery() == 1)
{
msg.WriteSuccess();
this.Close();
DoctorMode doctorMode = new DoctorMode(did);
doctorMode.Show();
conn.Close();
}
else
{
msg.DataError("Создание рецепта не удалось!");
conn.Close();
}
}
else
{
msg.DataError("Ошибка совместимости!");
conn.Close();
}
}
break;
case 2:
if (CBSetName1.Text == CBSetName2.Text)
{
msg.DataError("Данные совпадают!");
}
else if (CBSetName1.Text == "" | CBSetName2.Text == "")
{
msg.DataError("Заполните данные!");
}
else
{
sql = "SELECT ID_MEDICINE FROM DRUG WHERE DRUG_NAME = '" + CBSetName1.Text + "'";
MySqlCommand med1 = new MySqlCommand(sql, conn);
m1 = Convert.ToInt32(med1.ExecuteScalar());
sql = "SELECT COUNT(*) FROM FORBIDDEN_DRUG " +
"WHERE PERSON_PERSON_IIN = '" + iin + "' " +
"AND MEDICINE_ID_MEDICINE = " + m1 + "";
MySqlCommand c1 = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(c1.ExecuteScalar());
if (COUNT == 0)
{
sql = "SELECT ID_MEDICINE FROM DRUG WHERE DRUG_NAME = '" + CBSetName2.Text + "'";
MySqlCommand med2 = new MySqlCommand(sql, conn);
m2 = Convert.ToInt32(med2.ExecuteScalar());
sql = "SELECT COUNT(*) FROM FORBIDDEN_DRUG " +
"WHERE PERSON_PERSON_IIN = '" + iin + "' " +
"AND MEDICINE_ID_MEDICINE = " + m2 + "";
MySqlCommand c2 = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(c2.ExecuteScalar());
if (COUNT == 0)
{
sqlins1 = "INSERT INTO PRESCRIPTION (MEDICINE_ID_MEDICINE, PRESCRIPTION_DATE_START, PERSON_PERSON_IIN, DOCTOR_ID_DOCTOR) " +
"VALUES (" + m1 + ",'" + date + "', '" + iin + "', " + did + ")";
MySqlCommand inspr1 = new MySqlCommand(sqlins1, conn);
sqlins2 = "INSERT INTO PRESCRIPTION (MEDICINE_ID_MEDICINE, PRESCRIPTION_DATE_START, PERSON_PERSON_IIN, DOCTOR_ID_DOCTOR) " +
"VALUES (" + m2 + ",'" + date + "', '" + iin + "', " + did + ")";
MySqlCommand inspr2 = new MySqlCommand(sqlins2, conn);
if (inspr1.ExecuteNonQuery() == 1)
{
if (inspr2.ExecuteNonQuery() == 1)
{
msg.WriteSuccess();
this.Close();
DoctorMode doctorMode = new DoctorMode(did);
doctorMode.Show();
conn.Close();
}
else
{
msg.DataError("Создание рецепта не удалось!");
conn.Close();
}
}
else
{
msg.DataError("Создание рецепта не удалось!");
conn.Close();
}
}
else
{
msg.DataError("Ошибка совместимости!");
conn.Close();
}
}
else
{
msg.DataError("Ошибка совместимости!");
conn.Close();
}
}
break;
case 3:
if (CBSetName1.Text == CBSetName2.Text |
CBSetName1.Text == CBSetName3.Text |
CBSetName3.Text == CBSetName2.Text)
{
msg.DataError("Данные совпадают!");
}
else if (CBSetName1.Text == "" | CBSetName2.Text == "" | CBSetName3.Text == "")
{
msg.DataError("Заполните данные!");
}
else
{
sql = "SELECT ID_MEDICINE FROM DRUG WHERE DRUG_NAME = '" + CBSetName1.Text + "'";
MySqlCommand med1 = new MySqlCommand(sql, conn);
m1 = Convert.ToInt32(med1.ExecuteScalar());
sql = "SELECT COUNT(*) FROM FORBIDDEN_DRUG " +
"WHERE PERSON_PERSON_IIN = '" + iin + "' " +
"AND MEDICINE_ID_MEDICINE = " + m1 + "";
MySqlCommand c1 = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(c1.ExecuteScalar());
if (COUNT == 0)
{
sql = "SELECT ID_MEDICINE FROM DRUG WHERE DRUG_NAME = '" + CBSetName2.Text + "'";
MySqlCommand med2 = new MySqlCommand(sql, conn);
m2 = Convert.ToInt32(med2.ExecuteScalar());
sql = "SELECT COUNT(*) FROM FORBIDDEN_DRUG " +
"WHERE PERSON_PERSON_IIN = '" + iin + "' " +
"AND MEDICINE_ID_MEDICINE = " + m2 + "";
MySqlCommand c2 = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(c2.ExecuteScalar());
if (COUNT == 0)
{
sql = "SELECT ID_MEDICINE FROM DRUG WHERE DRUG_NAME = '" + CBSetName3.Text + "'";
MySqlCommand med3 = new MySqlCommand(sql, conn);
m3 = Convert.ToInt32(med2.ExecuteScalar());
sql = "SELECT COUNT(*) FROM FORBIDDEN_DRUG " +
"WHERE PERSON_PERSON_IIN = '" + iin + "' " +
"AND MEDICINE_ID_MEDICINE = " + m3 + "";
MySqlCommand c3 = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(c3.ExecuteScalar());
if (COUNT == 0)
{
sqlins1 = "INSERT INTO PRESCRIPTION (MEDICINE_ID_MEDICINE, PRESCRIPTION_DATE_START, PERSON_PERSON_IIN, DOCTOR_ID_DOCTOR) " +
"VALUES (" + m1 + ",'" + date + "', '" + iin + "', " + did + ")";
MySqlCommand inspr1 = new MySqlCommand(sqlins1, conn);
sqlins2 = "INSERT INTO PRESCRIPTION (MEDICINE_ID_MEDICINE, PRESCRIPTION_DATE_START, PERSON_PERSON_IIN, DOCTOR_ID_DOCTOR) " +
"VALUES (" + m2 + ",'" + date + "', '" + iin + "', " + did + ")";
MySqlCommand inspr2 = new MySqlCommand(sqlins2, conn);
sqlins3 = "INSERT INTO PRESCRIPTION (MEDICINE_ID_MEDICINE, PRESCRIPTION_DATE_START, PERSON_PERSON_IIN, DOCTOR_ID_DOCTOR) " +
"VALUES (" + m3 + ",'" + date + "', '" + iin + "', " + did + ")";
MySqlCommand inspr3 = new MySqlCommand(sqlins3, conn);
if (inspr1.ExecuteNonQuery() == 1)
{
if (inspr2.ExecuteNonQuery() == 1)
{
if (inspr3.ExecuteNonQuery() == 1)
{
msg.WriteSuccess();
this.Close();
DoctorMode doctorMode = new DoctorMode(did);
doctorMode.Show();
conn.Close();
}
else
{
msg.DataError("Создание рецепта не удалось!");
conn.Close();
}
}
else
{
msg.DataError("Создание рецепта не удалось!");
conn.Close();
}
}
else
{
msg.DataError("Создание рецепта не удалось!");
conn.Close();
}
}
else
{
msg.DataError("Ошибка совместимости!");
conn.Close();
}
}
else
{
msg.DataError("Ошибка совместимости!");
conn.Close();
}
}
else
{
msg.DataError("Ошибка совместимости!");
conn.Close();
}
}
break;
case 4:
if (CBSetName1.Text == CBSetName2.Text |
CBSetName1.Text == CBSetName3.Text |
CBSetName1.Text == CBSetName4.Text |
CBSetName2.Text == CBSetName3.Text |
CBSetName2.Text == CBSetName4.Text |
CBSetName3.Text == CBSetName4.Text)
{
msg.DataError("Данные совпадают!");
}
else if (CBSetName1.Text == "" |
CBSetName2.Text == "" |
CBSetName3.Text == "" |
CBSetName4.Text == "")
{
msg.DataError("Заполните данные!");
}
else
{
sql = "SELECT ID_MEDICINE FROM DRUG WHERE DRUG_NAME = '" + CBSetName1.Text + "'";
MySqlCommand med1 = new MySqlCommand(sql, conn);
m1 = Convert.ToInt32(med1.ExecuteScalar());
sql = "SELECT COUNT(*) FROM FORBIDDEN_DRUG " +
"WHERE PERSON_PERSON_IIN = '" + iin + "' " +
"AND MEDICINE_ID_MEDICINE = " + m1 + "";
MySqlCommand c1 = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(c1.ExecuteScalar());
if (COUNT == 0)
{
sql = "SELECT ID_MEDICINE FROM DRUG WHERE DRUG_NAME = '" + CBSetName2.Text + "'";
MySqlCommand med2 = new MySqlCommand(sql, conn);
m2 = Convert.ToInt32(med2.ExecuteScalar());
sql = "SELECT COUNT(*) FROM FORBIDDEN_DRUG " +
"WHERE PERSON_PERSON_IIN = '" + iin + "' " +
"AND MEDICINE_ID_MEDICINE = " + m2 + "";
MySqlCommand c2 = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(c2.ExecuteScalar());
if (COUNT == 0)
{
sql = "SELECT ID_MEDICINE FROM DRUG WHERE DRUG_NAME = '" + CBSetName3.Text + "'";
MySqlCommand med3 = new MySqlCommand(sql, conn);
m3 = Convert.ToInt32(med2.ExecuteScalar());
sql = "SELECT COUNT(*) FROM FORBIDDEN_DRUG " +
"WHERE PERSON_PERSON_IIN = '" + iin + "' " +
"AND MEDICINE_ID_MEDICINE = " + m3 + "";
MySqlCommand c3 = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(c3.ExecuteScalar());
if (COUNT == 0)
{
sql = "SELECT ID_MEDICINE FROM DRUG WHERE DRUG_NAME = '" + CBSetName4.Text + "'";
MySqlCommand med4 = new MySqlCommand(sql, conn);
m4 = Convert.ToInt32(med2.ExecuteScalar());
sql = "SELECT COUNT(*) FROM FORBIDDEN_DRUG " +
"WHERE PERSON_PERSON_IIN = '" + iin + "' " +
"AND MEDICINE_ID_MEDICINE = " + m4 + "";
MySqlCommand c4 = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(c4.ExecuteScalar());
if (COUNT == 0)
{
sqlins1 = "INSERT INTO PRESCRIPTION (MEDICINE_ID_MEDICINE, PRESCRIPTION_DATE_START, PERSON_PERSON_IIN, DOCTOR_ID_DOCTOR) " +
"VALUES (" + m1 + ",'" + date + "', '" + iin + "', " + did + ")";
MySqlCommand inspr1 = new MySqlCommand(sqlins1, conn);
sqlins2 = "INSERT INTO PRESCRIPTION (MEDICINE_ID_MEDICINE, PRESCRIPTION_DATE_START, PERSON_PERSON_IIN, DOCTOR_ID_DOCTOR) " +
"VALUES (" + m2 + ",'" + date + "', '" + iin + "', " + did + ")";
MySqlCommand inspr2 = new MySqlCommand(sqlins2, conn);
sqlins3 = "INSERT INTO PRESCRIPTION (MEDICINE_ID_MEDICINE, PRESCRIPTION_DATE_START, PERSON_PERSON_IIN, DOCTOR_ID_DOCTOR) " +
"VALUES (" + m3 + ",'" + date + "', '" + iin + "', " + did + ")";
MySqlCommand inspr3 = new MySqlCommand(sqlins3, conn);
sqlins4 = "INSERT INTO PRESCRIPTION (MEDICINE_ID_MEDICINE, PRESCRIPTION_DATE_START, PERSON_PERSON_IIN, DOCTOR_ID_DOCTOR) " +
"VALUES (" + m4 + ",'" + date + "', '" + iin + "', " + did + ")";
MySqlCommand inspr4 = new MySqlCommand(sqlins4, conn);
if (inspr1.ExecuteNonQuery() == 1)
{
if (inspr2.ExecuteNonQuery() == 1)
{
if (inspr3.ExecuteNonQuery() == 1)
{
if (inspr4.ExecuteNonQuery() == 1)
{
msg.WriteSuccess();
this.Close();
DoctorMode doctorMode = new DoctorMode(did);
doctorMode.Show();
conn.Close();
}
else
{
msg.DataError("Создание рецепта не удалось!");
conn.Close();
}
}
else
{
msg.DataError("Создание рецепта не удалось!");
conn.Close();
}
}
else
{
msg.DataError("Создание рецепта не удалось!");
conn.Close();
}
}
else
{
msg.DataError("Создание рецепта не удалось!");
conn.Close();
}
}
else
{
msg.DataError("Ошибка совместимости!");
conn.Close();
}
}
else
{
msg.DataError("Ошибка совместимости!");
conn.Close();
}
}
else
{
msg.DataError("Ошибка совместимости!");
conn.Close();
}
}
else
{
msg.DataError("Ошибка совместимости!");
conn.Close();
}
}
break;
}
}