public static bool AddNewUser(UserModel user)
{
String query = "INSERT IGNORE INTO " + TblUsers;
query += " (UserName, UserPassword, UserFullName, UserEmail, UserPhone, UserIpAddress,"
+ " UserBlocked, UserAllowDataNet, UserAllowTickNet, UserAllowLocal,"
+ " UserAllowRemote, UserAllowAnyIp, UserAllowMissBars, UserAllowCollectFrCQG, UserAllowDexport) VALUES";
query += "('";
query += user.Name + "',";
query += "'" + user.Password + "',";
query += "'" + user.FullName + "',";
query += "'" + user.Email + "',";
query += "'" + user.Phone + "',";
query += "'" + user.IpAdress + "',";
query += user.Blocked + ",";
query += user.AllowDataNet + ",";
query += user.AllowTickNet + ",";
query += user.AllowLocalDb + ",";
query += user.AllowRemoteDb + ",";
query += user.AllowAnyIp + ",";
query += user.AllowMissBars + ",";
query += user.AllowCollectFrCqg + ",";
query += user.AllowDexport + ");COMMIT;";
return DoSql(query);
}
public static List<UserModel> GetUsersForSymbol(int symbolId, string appType)
{
var userList = new List<UserModel>();
var sql = "SELECT * FROM " + TblSymbolsForUsers
+ " LEFT JOIN " + TblUsers
+ " ON " + TblSymbolsForUsers + ".UserID = "
+ TblUsers + ".ID" + " WHERE " + TblSymbolsForUsers + ".SymbolID = '" + symbolId + "' AND " +
TblSymbolsForUsers + ".TNorDN = " + (appType == ApplicationType.TickNet.ToString()).ToString() +
" ; COMMIT;";
lock (LockReader)
{
var reader = GetReader(sql);
if (reader != null)
{
while (reader.Read())
{
var user = new UserModel
{
Id = reader.GetInt32(4),
Name = reader.GetString(5),
Password = reader.GetString(6),
FullName = reader.GetString(7),
Email = reader.GetString(8),
Phone = reader.GetString(9),
IpAdress = reader.GetString(10),
Blocked = reader.GetBoolean(11),
AllowDataNet = reader.GetBoolean(12),
AllowTickNet = reader.GetBoolean(13),
AllowLocalDb = reader.GetBoolean(14),
AllowRemoteDb = reader.GetBoolean(15),
AllowAnyIp = reader.GetBoolean(16),
AllowMissBars = reader.GetBoolean(17),
AllowCollectFrCqg = reader.GetBoolean(18),
};
userList.Add(user);
}
reader.Close();
}
}
return userList;
}
public static List<UserModel> GetUsersForGroup(int groupId, string appType)
{
var userList = new List<UserModel>();
var sql = "SELECT * FROM " + TblGroupsForUsers
+ " LEFT JOIN " + TblUsers
+ " ON " + TblGroupsForUsers + ".UserID = "
+ TblUsers + ".ID" + " WHERE GroupID = '" + groupId + "' AND " + TblGroupsForUsers + ".AppType = '" + appType + "' ; COMMIT;";
lock (LockReader)
{
var reader = GetReader(sql);
if (reader != null)
{
while (reader.Read())
{
var user = new UserModel
{
AdditionalPrivilege = reader.GetString(8),
Id = reader.GetInt32(10),
Name = reader.GetString(11),
Password = reader.GetString(12),
FullName = reader.GetString(13),
Email = reader.GetString(14),
Phone = reader.GetString(15),
IpAdress = reader.GetString(16),
Blocked = reader.GetBoolean(17),
AllowDataNet = reader.GetBoolean(18),
AllowTickNet = reader.GetBoolean(19),
AllowLocalDb = reader.GetBoolean(20),
AllowRemoteDb = reader.GetBoolean(21),
AllowAnyIp = reader.GetBoolean(22),
AllowMissBars = reader.GetBoolean(23),
AllowCollectFrCqg = reader.GetBoolean(24),
};
userList.Add(user);
}
reader.Close();
}
}
return userList;
}
public static List<UserModel> GetUsers()
{
var usersList = new List<UserModel>();
const string sql = "SELECT * FROM " + TblUsers;
lock (LockReader)
{
var reader = GetReader(sql);
if (reader != null)
{
try
{
while (reader.Read())
{
var user = new UserModel
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Password = reader.GetString(2),
FullName = reader.GetString(3),
Email = reader.GetString(4),
Phone = reader.GetString(5),
IpAdress = reader.GetString(6),
Blocked = reader.GetBoolean(7),
AllowDataNet = reader.GetBoolean(8),
AllowTickNet = reader.GetBoolean(9),
AllowLocalDb = reader.GetBoolean(10),
AllowRemoteDb = reader.GetBoolean(11),
AllowAnyIp = reader.GetBoolean(12),
AllowMissBars = reader.GetBoolean(13),
AllowCollectFrCqg = reader.GetBoolean(14),
AllowDexport = reader.GetBoolean(15)
};
usersList.Add(user);
}
}
finally
{
reader.Close();
}
}
}
return usersList;
}
public static UserModel GetUserData(int userId)
{
var user = new UserModel();
var sql = "SELECT * FROM " + TblUsers + " WHERE `ID`= " + userId;
lock (LockReader)
{
var reader = GetReader(sql);
if (reader != null)
{
if (reader.Read())
{
user = new UserModel
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Password = reader.GetString(2),
FullName = reader.GetString(3),
Email = reader.GetString(4),
Phone = reader.GetString(5),
IpAdress = reader.GetString(6),
Blocked = reader.GetBoolean(7),
AllowDataNet = reader.GetBoolean(8),
AllowTickNet = reader.GetBoolean(9),
AllowLocalDb = reader.GetBoolean(10),
AllowRemoteDb = reader.GetBoolean(11),
AllowAnyIp = reader.GetBoolean(12),
AllowMissBars = reader.GetBoolean(13),
AllowCollectFrCqg = reader.GetBoolean(14),
AllowDexport = reader.GetBoolean(15)
};
}
reader.Close();
}
}
return user;
}
public static bool EditUser(int userId, UserModel user)
{
String query = "UPDATE " + TblUsers + " SET "
+ " UserName = '******', "
+ " UserPassword = '******', "
+ " UserFullName = '" + user.FullName + "', "
+ " UserEmail = '" + user.Email + "', "
+ " UserPhone = '" + user.Phone + "', "
+ " UserIpAddress = '" + user.IpAdress + "', "
+ " UserBlocked = " + user.Blocked + ","
+ " UserAllowDataNet = " + user.AllowDataNet + ","
+ " UserAllowTickNet = " + user.AllowTickNet + ","
+ " UserAllowLocal = " + user.AllowLocalDb + ","
+ " UserAllowRemote = " + user.AllowRemoteDb + ","
+ " UserAllowAnyIp = " + user.AllowAnyIp + ","
+ " UserAllowMissBars = " + user.AllowMissBars + ","
+ " UserAllowMissBars = " + user.AllowCollectFrCqg + ","
+ " UserAllowDexport = " + user.AllowDexport;
query += " WHERE ID = '" + userId + "'; COMMIT;";
return DoSql(query);
}