/// <summary>
/// 读取数据库内容
/// </summary>
/// <returns></returns>
private List <DevNodeInfo> GetDevNodes()
{
string strErr = "";
string strSql = "select n.devid,n.nodeid,n.vc_name,n.i_NodeType " +
"from m_devnodes n " +
"INNER JOIN m_devinfo d on n.devid = d.DevID " +
"inner JOIN m_blltree b on b.BindID = d.DevID " +
"where b.unitid = '" + UINTID + "' and b.i_BindType = 2 " +
"order by n.nodeid;";
DataTable dtDevNodes = MysqlDB.GetDataTable(out strErr, strSql);
List <DevNodeInfo> lstNodes = new List <DevNodeInfo>();
if (dtDevNodes != null && dtDevNodes.Rows.Count > 0)
{
for (int i = 0; i < dtDevNodes.Rows.Count; i++)
{
DevNodeInfo node = new DevNodeInfo()
{
DevID = dtDevNodes.Rows[i]["DevID"].ToString() + "",
Name = dtDevNodes.Rows[i]["vc_name"].ToString() + "",
NodeID = dtDevNodes.Rows[i]["NodeID"].ToString() + "",
NodeType = int.Parse(dtDevNodes.Rows[i]["i_NodeType"].ToString())
};
lstNodes.Add(node);
}
}
return(lstNodes);
}
private void LoginForm_Load(object sender, EventArgs e)
{
//MessageBox.Show(VariableDB.ConfigFile);
if (!System.IO.File.Exists(VariableDB.ConfigFile))
{
MessageBox.Show(this, "กรุณาตรวจสอบ Config File (server.big)", "แจ้งเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
VariableDB.ReadConfig();
//MessageBox.Show(VariableDB.Database);
MysqlDB DB = new MysqlDB();
MemberClass member = new MemberClass();
this.Text = "Login";
//connect DB
if (DB.OpenConnection() == false)
{
MetroMessageBox.Show(this, "Cannot Connect DB");
return;
}
login_panel_bg.BackColor = Color.FromArgb(200, 255, 255, 255);
}
/// <summary>
/// 直接拼成SQL语句（注意，此方法 一定 要自行过滤，否则会产生SQL注入的风险）
/// </summary>
/// <param name="db"></param>
/// <param name="table"></param>
/// <param name="where"></param>
/// <param name="group"></param>
/// <param name="order"></param>
/// <param name="start"></param>
/// <param name="offset"></param>
/// <returns></returns>
public static string select(string db, string table, string field, string where, string group, string order, int start, int offset)
{
string json_str = "";
StringBuilder sql = new StringBuilder();
sql.Append("select " + field);
sql.Append(" from " + table);
if (string.Empty == where.Trim())
{
where = " 1 = 1 ";
}
sql.Append(" where " + where);
if (string.Empty != group)
{
sql.Append(" group by " + group);
}
if (string.Empty != order.Trim())
{
sql.Append(" order by " + order);
}
DataSet ds = MysqlDB.getInstance(db).Query(sql.ToString());
json_str = JsonHelper.SerializeObject(ds);
return(json_str);
}
//Adding the selected movie to mysql data base
private void btnAdd_Click(object sender, RoutedEventArgs e)
{
int uID = ActiveUserId;
int MovieID = SelectedMovieID;
MysqlDB mysql = new MysqlDB();
try
{
//checking if the selected movie is already in the database or not
List <MoviesModel> Ml = mysql.MovieChecker(uID, MovieID);
if (Ml.Count == 0)
{
//adding the selecetd movie to mysql using movie id and active user id.
mysql.AddMovieToMysql(uID, MovieID);
MessageBox.Show($"{SelectedMovieTitle} Successfully added to your database.");
}
else
{
MessageBox.Show($"{SelectedMovieTitle} Already exist in your database. Please select another Movie!");
}
}
catch (Exception x)
{
MessageBox.Show(x.ToString());
}
}
/// <summary>
/// 读取数据库内容
/// </summary>
/// <returns></returns>
private List <BllTreeNodeInfo> GetBllTreeNodes()
{
string strErr = "";
string strSql = "SELECT treeid,parentid,vc_Name,vc_Code,i_BindType,BindID,i_Sort " +
"from m_blltree where unitid='" + UINTID + "'";
DataTable dtBllTree = MysqlDB.GetDataTable(out strErr, strSql);
List <BllTreeNodeInfo> lstNodes = new List <BllTreeNodeInfo>();
if (dtBllTree != null && dtBllTree.Rows.Count > 0)
{
for (int i = 0; i < dtBllTree.Rows.Count; i++)
{
BllTreeNodeInfo node = new BllTreeNodeInfo()
{
BindID = dtBllTree.Rows[i]["BindID"].ToString() + "",
//BindType = int.Parse(dtBllTree.Rows[i]["i_BindType"].ToString()),
//Code = dtBllTree.Rows[i]["vc_Code"].ToString() + "",
NodeName = dtBllTree.Rows[i]["vc_Name"].ToString() + "",
TreeID = dtBllTree.Rows[i]["TreeID"].ToString() + "",
TreeParentID = dtBllTree.Rows[i]["parentid"].ToString() + "",
// Sort = int.Parse(dtBllTree.Rows[i]["i_Sort"].ToString()),
};
lstNodes.Add(node);
}
}
return(lstNodes);
}
public MailController(IOptions <MysqlDB> settings, ILogger <GuideController> logger)
{
_logger = logger;
StarInfoConfig = settings.Value;
//注册依赖对象 //返回调用者
container.RegisterType <IGuideContract, GuideService>();
_guide = container.Resolve <GuideService>();
}
/// <summary>
/// 根据设备名称和告警状态更新告警状态
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strDevName">设备名称</param>
/// <param name="nWillAlarmStatus">要更新成什么告警状态</param>
/// <param name="nCurrentAlarmStatus">当前是什么告警状态</param>
/// <returns></returns>
public static int UpdateAlarmStatusByDevNameAndAlarmStatus(string strTableName, string strDevName, int nWillAlarmStatus, int nCurrentAlarmStatus)
{
string errMsg;
string strSql = string.Format("update {0} set alarm_status={1} where (device_name='{2}' or baohu_vs_dev='{3}') and alarm_status={4}", strTableName, nWillAlarmStatus, strDevName, strDevName, nCurrentAlarmStatus);
MysqlDB.UpdateData(out errMsg, strSql, true);
return(0);
}
public static void Insert(AccountData data)
{
MysqlQuery query = new MysqlQuery(INSERT_QUERY);
query.AddValue("GUID", data.Id, MySqlDbType.Int32);
query.AddValue("USERNAME", data.Login.Length > 16 ? data.Login.Substring(0, 16) : data.Login, MySqlDbType.VarChar);
query.AddValue("PASSWORD", data.Password.Length > 32 ? data.Password.Substring(0, 32) : data.Password, MySqlDbType.VarChar);
MysqlDB.Query(query);
}
protected static void Started()
{
MysqlDB.Init(new MysqlConfig(MysqlS.Default.Host, MysqlS.Default.Database, MysqlS.Default.User, MysqlS.Default.Password));
AuthService.Init();
LauncherService.Init();
Server = new TcpServer(NetworkS.Default.Ip, NetworkS.Default.Port, NetworkS.Default.MaxConnections);
GameBridgeService = new GsService();
GameBridgeService.Init("TRUEPASSWORD");
Server.BeginListening();
}
public static void UpdateAll(Dictionary <int, AccountData> accounts)
{
foreach (var a in accounts)
{
MysqlQuery query = new MysqlQuery(UPDATE_QUERY);
query.AddValue("GUID", a.Value.Id, MySqlDbType.Int32);
query.AddValue("ACCESSLEVEL", a.Value.AccessLevel, MySqlDbType.Int32);
MysqlDB.Query(query);
}
}
public string GetGroupMembers(string groupid)
{
using (var conn = MysqlDB.GetOpenConnection())
{
var Model = conn.Query <string>(@"SELECT MemberID
FROM tbl_GroupMembers
WHERE GroupID=@GroupID AND
IsAgree=1 AND IsAdminAgree=1;",
new { GroupID = groupid }).ToList();
return(JsonConvert.SerializeObject(Model));
}
}
public static string query(string db, string sql)
{
string json_str = "";
if (string.Empty == db.Trim() || string.Empty == sql.Trim())
{
return(json_str);
}
//json_str = JsonConvert.SerializeObject(MysqlDB.getInstance(db).Query(sql).Tables[0]);
json_str = JsonHelper.ToJson(MysqlDB.getInstance(db).Query(sql).Tables[0]);
return(json_str);
}
public bool FileInsert(string filemanager)
{
var FileManager = JsonConvert.DeserializeObject <tbl_FileManager>(filemanager);
using (var conn = MysqlDB.GetOpenConnection())
{
return(conn.Execute(@"INSERT INTO tbl_ImFileManager
(FileID, FileDirectory, FileDate)
VALUES (@FileID,@FileDirectory,@FileDate)"
, FileManager) > 0);
}
}
//通过变电站名称获取变电站ID
public static int GetStationIdByStationNameEx(string StationName)
{
int nStationID = 0;
string errMsg;
string strSql;
DataTable dt;
strSql = string.Format("SELECT station_id from ob_d5000_station where station_name_videoplant='{0}'", StationName);
dt = MysqlDB.GetDataTable(out errMsg, strSql);
if (dt.Rows.Count > 0)
{
nStationID = Convert.ToInt32(dt.Rows[0]["station_id"].ToString());
}
return(nStationID);
}
//Deleting the selected movie for mysql database
private void btnDelete_Click(object sender, RoutedEventArgs e)
{
try
{
if (MessageBox.Show($"Are you sure that you want to delete *{SelectedMovieTitle}* from your database?", $"Delete {SelectedMovieTitle}", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
MysqlDB.DeleteMovieFromMysql(SelectedMovieID);
dgMovies_Loaded(sender, e);
MessageBox.Show($"Movie Successfully deleted from your database!");
}
}
catch (Exception)
{
throw;
}
}
public string Delete(int idPersona)
{
string response = string.Empty;
MysqlDB mysql = new MysqlDB();
var cmd = mysql.Connection.CreateCommand();
cmd.CommandText = @"DELETE FROM integracion.persona WHERE idPersona=" + idPersona + ";";
try
{
response = cmd.ExecuteNonQuery().ToString();
}
catch (Exception ex)
{
response = ex.Message;
}
return(response);
}
public string GetFileByID(string fileid)
{
using (var conn = MysqlDB.GetOpenConnection())
{
var Model = conn.QueryFirst(@"SELECT FileID, FileType, FileDirectory, FileDate
FROM tbl_ImFileManager WHERE FileID=@FileID",
new tbl_FileManager {
FileID = fileid
});
return(JsonConvert.SerializeObject(new tbl_FileManager()
{
FileID = Convert.ToString(Model.FileID),
FileDirectory = Model.FileDirectory,
FileType = Model.FileType,
FileDate = Convert.ToString(Model.FileDate)
}));
}
}
public static Dictionary <int, AccountData> LoadAll()
{
Dictionary <int, AccountData> accounts = new Dictionary <int, AccountData>();
foreach (var a in MysqlDB.SelectAll(new MysqlQuery(SELECT_QUERY)))
{
AccountData data = new AccountData()
{
Id = Convert.ToInt32(a["guid"]),
Login = Convert.ToString(a["username"]),
Password = Convert.ToString(a["password"]),
AccessLevel = Convert.ToInt32(a["access_level"])
};
GUIDGenerator.SetGUID(data.Id);
accounts.Add(data.Id, data);
}
return(accounts);
}
public string GetFriendRelationList(string userid)
{
using (var conn = MysqlDB.GetOpenConnection())
{
var FriendRelation = conn.Query <tbl_FriendRelation>(@"
SELECT RelationId,
RelationFromUserId as 'FromUserID',
RelationToUserId as 'ToUserID',
RelationGroupId as 'FriendGroupID',
RelationSpaceGroupId as 'SpaceGroupID',
RelationMark as 'RelationRemark',
RelationBGFileID as 'Background',
RelationCreateTime as 'RelationDate'
FROM tbl_FriendRelationShip
WHERE RelationFromUserId=@UserID;",
new { UserID = userid }).ToList();
return(JsonConvert.SerializeObject(FriendRelation));
}
}
public string GetFriendGroupList(string userid)
{
using (var conn = MysqlDB.GetOpenConnection())
{
var FriendGroups = conn.Query <tbl_FriendGroups>(@"
SELECT GroupID as 'GroupID',
GroupCategory as 'GroupCategory',
GroupType as 'GroupType',
GroupName as 'GroupName',
GroupImage as 'GroupImage',
GroupMark as 'GroupRemark',
GroupUserID as 'GroupUserID',
GroupDate as 'GroupDate'
FROM tbl_CustomGroup
WHERE GroupUserID=@UserID;",
new { UserID = userid }).ToList();
return(JsonConvert.SerializeObject(FriendGroups));
}
}
private void dgMovies_Loaded(object sender, RoutedEventArgs e)
{
//getting the active user information and movies
try
{
int AcId = int.Parse(txtUserID.Text.ToString());
List <Model.UserModel> ActiveUser = new List <UserModel>();
ActiveUser = MysqlDB.GetActiveUserInfo(AcId);
if (ActiveUser.Count == 1)
{
txtName.Text = ActiveUser[0].FirstName;
txtLastName.Text = ActiveUser[0].LastName;
txtEmail.Text = ActiveUser[0].Email;
txtPhone.Text = ActiveUser[0].phone.ToString();
//setting the right profile picture if the profile picture path is not null or empty
string photoPath = ActiveUser[0].PhotoPath;
if (!string.IsNullOrEmpty(photoPath))
{
string folderpath = System.IO.Directory.GetParent(Environment.CurrentDirectory).Parent.FullName + "\\ProfilePic\\";
var uriSource = new Uri($"{folderpath}{photoPath}", UriKind.RelativeOrAbsolute);
BitmapImage image = new BitmapImage(uriSource);
imgProfilePic.Source = image;
}
}
dgMovies.UnselectAll();
int ActiveUId = int.Parse(ActiveUserId);
//getting the movies of current user from mysql and them TMDB
SearchResultMovies.Results = MovieViewModel.GetMovieByIDFromTMDB(ActiveUId);
dgMovies.ItemsSource = SearchResultMovies.Results;
dgMovies.Columns[0].Visibility = Visibility.Hidden;
dgMovies.Columns[5].Visibility = Visibility.Hidden;
dgMovies.Columns[6].Visibility = Visibility.Hidden;
}
catch (Exception x)
{
MessageBox.Show(x.ToString());
}
}
public List <string> GetChatRecords(string fromid, string toid)
{
using (var conn = MysqlDB.GetOpenConnection())
{
if (null != toid)
{
return(conn.Query <string>(@"SELECT MsgStanza FROM tbl_Message
WHERE (MsgFrom=@MsgFrom AND MsgTo=@MsgTo)
OR
(MsgFrom=@MsgTo AND MsgTo=@MsgFrom);",
new { MsgFrom = fromid, MsgTo = toid }).ToList());
}
else
{
return(conn.Query <string>(@"SELECT MsgStanza FROM tbl_Message
WHERE MsgFrom=@MsgFrom;",
new { MsgFrom = fromid }).ToList());
}
}
}
public string Update(int idPersona, Persona persona)
{
string response = string.Empty;
MysqlDB mysql = new MysqlDB();
var cmd = mysql.Connection.CreateCommand();
cmd.CommandText = @"UPDATE integracion.persona SET Nombre=@Nombre, Email = @Email, Edad = @Edad WHERE idPersona=" + idPersona + ";";
mysql.BindParams(cmd, persona);
try
{
cmd.ExecuteNonQuery();
response = cmd.LastInsertedId.ToString();
}
catch (Exception ex)
{
response = ex.Message;
}
return(response);
}
public string Insert(Persona persona)
{
string response = string.Empty;
MysqlDB mysql = new MysqlDB();
var cmd = mysql.Connection.CreateCommand();
cmd.CommandText = @"INSERT INTO `integracion`.Persona (`Nombre`, `Email`,`Edad`) VALUES (@Nombre, @Email,@Edad);";
mysql.BindParams(cmd, persona);
try
{
cmd.ExecuteNonQuery();
response = cmd.LastInsertedId.ToString();
}
catch (Exception ex)
{
response = ex.Message;
}
return(response);
}
public List <Persona> GetPersona()
{
List <Persona> lista = new List <Persona>();
Persona persona = null;
MysqlDB mysql = new MysqlDB();
var cmd = mysql.Connection.CreateCommand();
cmd.CommandText = @"SELECT idPersona, Nombre,Email,Edad FROM `integracion`.Persona ";
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
persona = new Persona();
persona.IdPersona = Int32.Parse(reader[0].ToString());
persona.Nombre = reader[1].ToString();
persona.Email = reader[2].ToString();
persona.Edad = Int32.Parse(reader[3].ToString());
lista.Add(persona);
}
return(lista);
}
private bool AddExceptionData(string tablename, string data, string error)
{
try
{
using (var conn = MysqlDB.GetOpenConnection())
{
return(conn.Execute(@"INSERT INTO tbl_TableException
(ID,TableName,Staza, Error)
VALUES (@ID,@TableName,@Staza, @Error)", new
{
ID = Guid.NewGuid().ToString(),
TableName = tablename,
Staza = data,
Error = error
}) > 0);
}
}
catch
{
return(false);
}
}
public string GetGroupsInfoByOA(string GroupID)
{
using (var conn = MysqlDB.GetOpenConnection())
{
var GroupInfo = conn.QueryFirst <dynamic>(@"SELECT * FROM tbl_GroupManage WHERE GroupID=@groupID;",
new { groupID = GroupID });
var GroupProp = conn.Query <string>(@"SELECT PropertyVal FROM tbl_GroupProperty
WHERE PropertyGroupID=@groupID and PropertyKey in ('groupImage', 'groupDescribe')
ORDER BY PropertyVal DESC ;",
new { groupID = GroupID }).ToList();
var GroupUser = conn.Query <string>(@"SELECT MemberID FROM tbl_GroupMembers WHERE GroupID=@groupID and IsAgree=1 and IsAdminAgree=1;",
new { groupID = GroupID }).ToList();
return(JsonConvert.SerializeObject(new
{
GroupID = GroupID,
GroupName = GroupInfo.GroupName,
GroupImage = GroupProp.Count > 0 ? GroupProp[0] : "",
GroupDescribe = GroupProp.Count > 1 ? GroupProp[1] : "",
GroupMember = GroupUser,
GroupDate = GroupInfo.CreateDate
}));
}
}
public AssignmentController(IConfiguration configuration, MysqlDB mysqlDB, ILogger <AssignmentController> logger)
{
_config = configuration;
_logger = logger;
MysqlDB = mysqlDB;
}
public bool MessageInsert(string message)
{
byte[] bytes = Convert.FromBase64String(message);
var XmlMessage = Encoding.Default.GetString(bytes);
try
{
var Message = new tbl_Message(XmlMessage);
if (null != Message.MsgID)
{
using (var conn = MysqlDB.GetOpenConnection())
{
switch (Message.MsgType)
{
case "chat":
case "groupchat":
{
return(conn.Execute(@"INSERT INTO tbl_Message
(MsgID, MsgFrom, MsgTo,MsgType,MsgStanza,MsgDate)
VALUES
(@MsgID, @MsgFrom, @MsgTo,@MsgType,@MsgStanza,@MsgDate)", Message) > 0);
};
case "chat-arrive-receipt":
{
System.Threading.Thread.Sleep(300);
return(conn.Execute(@"UPDATE tbl_Message
SET MsgStatus=3 WHERE MsgID=@MsgID",
new
{
MsgID = Message.MsgID
}) > 0);
};
case "chat-viewed-receipt":
{
System.Threading.Thread.Sleep(300);
return(conn.Execute(@"UPDATE tbl_Message
SET MsgStatus=4
WHERE MsgStatus=3 AND
MsgFrom=@MsgFrom AND
MsgTo=@MsgTo" ,
new
{
MsgFrom = Message.MsgTo,
MsgTo = Message.MsgFrom
}) > 0);
};
case "chat-recall-message":
case "groupchat-recall-message":
{
System.Threading.Thread.Sleep(300);
return(conn.Execute(@"UPDATE tbl_Message
SET MsgStanza=@MsgStanza
WHERE MsgID=@MsgID",
new
{
MsgID = Message.MsgID,
MsgStanza = Message.MsgStanza
}) > 0);
};
}
};
}
return(false);
}
catch (Exception ex)
{
AddExceptionData("tbl_Message", XmlMessage, ex.Message);
return(false);
}
}
internal PersonaService(MysqlDB db)
{
Db = db;
}
