public static bool AddGroupOfSymbols(GroupModel group)
{
string startDateStr = Convert.ToDateTime(group.Start).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture);
string endDateStr = Convert.ToDateTime(group.End).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture);
String query = "INSERT IGNORE INTO " + TblSymbolsGroups;
query += " (GroupName, TimeFrame, Start, End, CntType) VALUES";
query += "('" + group.GroupName + "',";
query += " '" + group.TimeFrame + "',";
query += " '" + startDateStr + "',";
query += " '" + endDateStr + "',";
query += " '" + group.CntType + "');COMMIT;";
return DoSql(query);
}
public static bool AddGroupForUser(int userId, GroupModel group)
{
string startDateStr = Convert.ToDateTime(group.Start).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture);
string endDateStr = Convert.ToDateTime(group.End).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture);
var sql = "INSERT IGNORE INTO " + TblGroupsForUsers
+ " (`UserID`, `GroupID`, `GroupName`, `TimeFrame`, `Start`, `End`, `CntType`, `Privilege`, `AppType`)"
+ "VALUES('" + userId + "',"
+ " '" + group.GroupId + "',"
+ " '" + group.GroupName + "',"
+ " '" + group.TimeFrame + "',"
+ " '" + startDateStr + "',"
+ " '" + endDateStr + "',"
+ " '" + group.CntType + "',"
+ " '" + group.Privilege.ToString() + "',"
+ " '" + group.AppType.ToString() + "');COMMIT;";
return DoSql(sql);
}
public static List<GroupModel> GetGroupsForUser(int userId)
{
var groupList = new List<GroupModel>();
var sql = "SELECT * FROM " + TblGroupsForUsers + " WHERE UserID = '" + userId + "' ; COMMIT;";
lock (LockReader)
{
var reader = GetReader(sql);
if (reader != null)
{
while (reader.Read())
{
var symbol = new GroupModel
{
GroupId = reader.GetInt32(2),
GroupName = reader.GetString(3),
TimeFrame = reader.GetString(4),
Start = reader.GetDateTime(5),
End = reader.GetDateTime(6),
CntType = reader.GetString(7)
};
GroupPrivilege privilege;
ApplicationType appType;
Enum.TryParse(reader.GetString(8), out privilege);
Enum.TryParse(reader.GetString(9), out appType);
symbol.Privilege = privilege;
symbol.AppType = appType;
groupList.Add(symbol);
}
reader.Close();
}
}
return groupList;
}
public static List<GroupModel> GetGroups()
{
var groupList = new List<GroupModel>();
const string sql = "SELECT * FROM " + TblSymbolsGroups;
lock (LockReader)
{
var reader = GetReader(sql);
if (reader != null)
{
while (reader.Read())
{
var group = new GroupModel
{
GroupId = reader.GetInt32(0),
GroupName = reader.GetString(1),
TimeFrame = reader.GetString(2),
Start = reader.GetDateTime(3),
End = reader.GetDateTime(4),
CntType = reader.GetString(5)
};
groupList.Add(group);
}
reader.Close();
}
}
return groupList;
}
public static bool EditGroupOfSymbols(int groupId, GroupModel group)
{
string startDateStr = Convert.ToDateTime(group.Start).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture);
string endDateStr = Convert.ToDateTime(group.End).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture);
String query = "UPDATE " + TblSymbolsGroups
+ " SET GroupName = '" + group.GroupName
+ "', TimeFrame = '" + group.TimeFrame
+ "', Start = '" + startDateStr
+ "', End = '" + endDateStr
+ "', CntType = '" + group.CntType
+ "' WHERE ID = '" + groupId + "' ; COMMIT;";
if (DoSql(query))
{
query = "UPDATE " + TblGroupsForUsers
+ " SET GroupName = '" + group.GroupName
+ "', TimeFrame = '" + group.TimeFrame
+ "', Start = '" + startDateStr
+ "', End = '" + endDateStr
+ "', CntType = '" + group.CntType
+ "' WHERE GroupID = '" + groupId + "' ; COMMIT;";
return DoSql(query);
}
return false;
}