private void AddEditGroup_Load(object sender, EventArgs e)
{
if (IsEditMode)
{
this.Text = "Edit Group";
btnSubmit.Text = "Update Group";
txbGroupName.Text = sql.ExecuteScalarACon <string>($"SELECT Name FROM Groups WHERE ID = '{GroupID}'");
}
else
{
this.Text = "Add New Group";
}
// Load all devices and add then to the view
sql.Open();
using (SQLiteDataReader reader = sql.ExecuteQuery("SELECT * FROM Devices LEFT JOIN GroupAssigns ON Devices.MACAddress = GroupAssigns.MACAddress WHERE GroupAssigns.GroupID IS NULL ORDER BY Devices.Name ASC"))
{
while (reader.Read())
{
// Add device to view
GroupViewItem gvi = new GroupViewItem(
Convert.ToString(reader["Name"]),
grvDevices.SmallImageList.Images.IndexOfKey(
Convert.ToString(reader["DeviceType"]) + "_RAW")
);
gvi.Tag = Convert.ToString(reader["MACAddress"]);
grvDevices.GroupViewItems.Add(gvi);
}
}
if (!IsEditMode)
{
// Create new group
sql.ExecuteNonQuery($"INSERT INTO Groups (Name, Description, DeviceType) VALUES ('{newGroupGuid}', '', '{DeviceType.UnknownDevice}')");
GroupID = sql.ExecuteScalar <int>($"SELECT ID FROM Groups WHERE Name = '{newGroupGuid}'");
}
sql.Close();
UpdateGroupDeviceList();
}
private void lbxDeviceSites_SelectedIndexChanged(object sender, EventArgs e)
{
sql.Open();
using (SQLiteDataReader reader = sql.ExecuteQuery($"SELECT * FROM DeviceSites WHERE ID = '{lbxDeviceSites.SelectedValue}'"))
{
while (reader.Read())
{
txbName.Text = Convert.ToString(reader["Name"]);
txbDeviceSiteURL.Text = Convert.ToString(reader["Site"]);
}
}
sql.Close();
EnableInput();
}
/// <summary>
/// Connect to all Network drives of a specified QD-User
/// </summary>
/// <param name="pUserID">User-ID of the user</param>
/// <param name="pUserPassword">User-password</param>
/// <param name="pDBData">DB connection data</param>
/// <param name="pLogUserData">Log user data</param>
/// <param name="pDisconnectFirst">Disconnect all drives before reconecting</param>
/// <param name="drives">Drive-List</param>
/// <param name="ConnectOnlyIfNotAvailable">Only reconnect to a drive if it isn't already connected</param>
/// <returns></returns>
public static int ConnectQDDrives(string pUserID, string pUserPassword, WrapMySQLData pDBData, bool pLogUserData, bool pDisconnectFirst = true, List <DriveViewItem> drives = null, bool ConnectOnlyIfNotAvailable = false)
{
int connectCtr = 0;
// Disconnect all current drives
if (pDisconnectFirst)
{
DisconnectAllDrives(drives);
if (!string.IsNullOrEmpty(pUserID))
{
LogUserConnection(pUserID, QDLogAction.QDDrivesDisconnect, pDBData, pLogUserData);
}
}
// Connect online-drives (online-synced)
if (!string.IsNullOrEmpty(pUserID))
{
try
{
using (WrapMySQL sql = new WrapMySQL(pDBData))
{
if (!QDLib.ManagedDBOpen(sql))
{
QDLib.DBOpenFailed(); return(-1);
}
// Connect local network drives
using (MySqlDataReader reader = (MySqlDataReader)sql.ExecuteQuery("SELECT * FROM qd_drives INNER JOIN qd_assigns ON qd_drives.ID = qd_assigns.DriveID INNER JOIN qd_users ON qd_assigns.UserID = qd_users.ID WHERE qd_assigns.UserID = ?", pUserID))
{
while (reader.Read())
{
try
{
if (!ConnectOnlyIfNotAvailable || (ConnectOnlyIfNotAvailable && !Directory.Exists($@"{Convert.ToChar(reader["CustomDriveLetter"])}:\")))
{
//MessageBox.Show("Try to connect " + Convert.ToString(reader["CustomDriveName"]));
ConnectDrive(
Convert.ToChar(reader["CustomDriveLetter"]),
Convert.ToString(reader["LocalPath"]),
Cipher.Decrypt(Convert.ToString(reader["DUsername"]), pUserPassword),
Cipher.Decrypt(Convert.ToString(reader["DPassword"]), pUserPassword),
Convert.ToString(reader["CustomDriveName"]),
Cipher.Decrypt(Convert.ToString(reader["DDomain"]), pUserPassword)
);
connectCtr++;
}
}
catch
{
return(5);
}
}
}
sql.Close();
// Conenct remote network drives
// TODO
}
}
catch
{
return(4);
}
}
// Connect Private drives (not online-synced)
try
{
if (!File.Exists(QDInfo.ConfigFile))
{
return(1);
}
using (WrapSQLite sqlite = new WrapSQLite(QDInfo.ConfigFile))
{
if (!QDLib.ManagedDBOpen(sqlite))
{
QDLib.DBOpenFailed(); return(-1);
}
// Connect local network drives
using (SQLiteDataReader reader = (SQLiteDataReader)sqlite.ExecuteQuery("SELECT * FROM qd_drives"))
{
while (reader.Read())
{
try
{
if (!ConnectOnlyIfNotAvailable || (ConnectOnlyIfNotAvailable && Directory.Exists($@"{Convert.ToChar(reader["CustomDriveLetter"])}:\")))
{
//MessageBox.Show("Try to connect " + Convert.ToString(reader["CustomDriveName"]));
ConnectDrive(
Convert.ToChar(reader["DriveLetter"]),
Convert.ToString(reader["LocalPath"]),
Cipher.Decrypt(Convert.ToString(reader["Username"]), QDInfo.LocalCipherKey),
Cipher.Decrypt(Convert.ToString(reader["Password"]), QDInfo.LocalCipherKey),
Convert.ToString(reader["DriveName"]),
Cipher.Decrypt(Convert.ToString(reader["Domain"]), QDInfo.LocalCipherKey)
);
connectCtr++;
}
}
catch
{
return(3);
}
}
}
sqlite.Close();
// Conenct remote network drives
// TODO
}
}
catch
{
return(2);
}
// Log only if not local. Do not log if no drives connected
if (!string.IsNullOrEmpty(pUserID) && connectCtr > 0)
{
LogUserConnection(pUserID, QDLogAction.QDDrivesConnect, pDBData, pLogUserData);
}
return(0);
}
/// <summary>
/// Creates a list with all drives of a specified user
/// </summary>
/// <param name="pIsLocalConnection">Determines if the connection is a local connection</param>
/// <param name="pUserID">User-ID of the target user. Blank if local connection</param>
/// <param name="pUserPassword">Password of the user</param>
/// <param name="pDBConDat">DB connection data</param>
/// <returns>Drive-List</returns>
public static List <DriveViewItem> CreateDriveList(bool pIsLocalConnection, string pUserID, string pUserPassword, WrapMySQLData pDBConDat)
{
List <DriveViewItem> driveList = new List <DriveViewItem>();
using (WrapSQLite sqlite = new WrapSQLite(QDInfo.ConfigFile))
{
if (!QDLib.ManagedDBOpen(sqlite))
{
QDLib.DBOpenFailed(); return(null);
}
using (SQLiteDataReader reader = (SQLiteDataReader)sqlite.ExecuteQuery("SELECT * FROM qd_drives"))
{
while (reader.Read())
{
driveList.Add(new DriveViewItem(
Convert.ToString(reader["ID"]),
Convert.ToString(reader["DriveName"]),
Convert.ToString(reader["LocalPath"]),
Convert.ToString(reader["DriveLetter"]),
true,
false,
Cipher.Decrypt(Convert.ToString(reader["Username"]), QDInfo.LocalCipherKey),
Cipher.Decrypt(Convert.ToString(reader["Password"]), QDInfo.LocalCipherKey),
Cipher.Decrypt(Convert.ToString(reader["Domain"]), QDInfo.LocalCipherKey)
));
}
}
sqlite.Close();
}
if (!pIsLocalConnection)
{
try
{
using (WrapMySQL mysql = new WrapMySQL(pDBConDat))
{
if (!QDLib.ManagedDBOpen(mysql))
{
QDLib.DBOpenFailed(); return(null);
}
using (MySqlDataReader reader = (MySqlDataReader)mysql.ExecuteQuery("SELECT *, qd_assigns.ID as AID, qd_drives.ID AS DID FROM qd_drives INNER JOIN qd_assigns ON qd_drives.ID = qd_assigns.DriveID WHERE qd_assigns.UserID = ?", pUserID))
{
while (reader.Read())
{
driveList.Add(new DriveViewItem(
Convert.ToString(reader["AID"]),
Convert.ToString(reader["CustomDriveName"]),
Convert.ToString(reader["LocalPath"]),
Convert.ToString(reader["CustomDriveLetter"]),
false,
Convert.ToBoolean(Convert.ToInt16(reader["IsPublic"])),
Cipher.Decrypt(Convert.ToString(reader["DUsername"]), pUserPassword),
Cipher.Decrypt(Convert.ToString(reader["DPassword"]), pUserPassword),
Cipher.Decrypt(Convert.ToString(reader["DDomain"]), pUserPassword),
Convert.ToString(reader["DID"])
));
}
}
mysql.Close();
}
}
catch { }
}
driveList.Sort();
return(driveList);
}
private void bgwUpdateEntries_DoWork(object sender, DoWorkEventArgs e)
{
Dictionary <string, bool> DevicePowerState = new Dictionary <string, bool>();
// Get all devices, set powerstate to false
DevicePowerState.Clear();
sql.Open();
using (SQLiteDataReader reader = sql.ExecuteQuery("SELECT * FROM Devices"))
while (reader.Read())
{
DevicePowerState.Add(Convert.ToString(reader["MACAddress"]), false);
}
sql.Close();
Ping ping = new Ping();
StringBuilder sqlSB = new StringBuilder();
if (ipStartParts[0] == ipEndParts[0] && ipStartParts[1] == ipEndParts[1] && ipStartParts[2] == ipEndParts[2])
{
for (int i = Convert.ToInt32(ipStartParts[3]); i <= Convert.ToInt32(ipEndParts[3]); i++)
{
if (bgwUpdateEntries.CancellationPending)
{
return;
}
bgwUpdateEntries.ReportProgress(i);
string currentIP = ipStartParts[0] + "." + ipStartParts[1] + "." + ipStartParts[2] + "." + i.ToString();
PingReply reply = ping.Send(currentIP, 100);
if (reply.Status == IPStatus.Success)
{
string currentMAC = NetExplore.GetMacAddress(currentIP);
if (!string.IsNullOrEmpty(currentMAC))
{
// Update the power-state of the device
if (DevicePowerState.ContainsKey(currentMAC))
{
// Known device, only update values
DevicePowerState[currentMAC] = true;
sqlSB.Append($"UPDATE Devices SET IP4Address = '{currentIP}', LastSeen = '{DateTime.Now:yyyy-MM-dd H:mm:ss}' WHERE MACAddress = '{currentMAC}';");
}
else
{
// New, unknown device, add to DB
DevicePowerState.Add(currentMAC, true);
sqlSB.Append($"INSERT INTO Devices (MACAddress, DeviceType, Name, IP4Address, LastSeen) VALUES ('{currentMAC}','{DeviceType.UnknownDevice}','Device-{currentMAC}','{currentIP}','{DateTime.Now:yyyy-MM-dd H:mm:ss}')");
}
}
}
else
{
continue;
}
}
if (bgwUpdateEntries.CancellationPending)
{
return;
}
// Update IP and LastSeen, add new entries
sql.ExecuteNonQueryACon(sqlSB.ToString());
// Update last power-state
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair <string, bool> entry in DevicePowerState)
{
int state = 0;
if (entry.Value)
{
state = 1;
}
sb.Append($"UPDATE Devices SET LastPowerState = '{state}' WHERE MACAddress = '{entry.Key}';");
}
sql.ExecuteNonQueryACon(sb.ToString());
}
}
public static void PowerStateCheck(string pIPRangeStart, string pIPRangeEnd, BackgroundWorker pProgressReplyBGW = null)
{
using (WrapSQLite tsql = new WrapSQLite(SurgitManager.SurgitDatabaseLocation, true))
{
Dictionary <string, bool> DevicePowerState = new Dictionary <string, bool>();
// Get all devices, set powerstate to false
DevicePowerState.Clear();
tsql.Open();
using (SQLiteDataReader reader = tsql.ExecuteQuery("SELECT * FROM Devices"))
while (reader.Read())
{
DevicePowerState.Add(Convert.ToString(reader["MACAddress"]), false);
}
tsql.Close();
// Ping all devices in Network Range
Ping ping = new Ping();
StringBuilder sqlSB = new StringBuilder();
if (CheckIPClassCValidity(pIPRangeStart, pIPRangeEnd))
{
string[] ipStartParts = pIPRangeStart.Split('.');
string[] ipEndParts = pIPRangeEnd.Split('.');
for (int i = Convert.ToInt32(ipStartParts[3]); i <= Convert.ToInt32(ipEndParts[3]); i++)
{
if (pProgressReplyBGW.CancellationPending)
{
return;
}
if (pProgressReplyBGW != null)
{
pProgressReplyBGW.ReportProgress(i);
}
string currentIP = ipStartParts[0] + "." + ipStartParts[1] + "." + ipStartParts[2] + "." + i.ToString();
PingReply reply = ping.Send(currentIP, 100);
if (reply.Status == IPStatus.Success)
{
string currentMAC = NetExplore.GetMacAddress(currentIP);
if (!string.IsNullOrEmpty(currentMAC))
{
// Update the power-state of the device
if (DevicePowerState.ContainsKey(currentMAC))
{
// Known device, only update values
DevicePowerState[currentMAC] = true;
sqlSB.Append($"UPDATE Devices SET IP4Address = '{currentIP}', LastSeen = '{DateTime.Now:yyyy-MM-dd H:mm:ss}' WHERE MACAddress = '{currentMAC}';");
}
else
{
// New, unknown device, add to DB
DevicePowerState.Add(currentMAC, true);
sqlSB.Append($"INSERT INTO Devices (MACAddress, DeviceType, Name, IP4Address, LastSeen) VALUES ('{currentMAC}','{DeviceType.UnknownDevice}','Device-{currentMAC}','{currentIP}','{DateTime.Now:yyyy-MM-dd H:mm:ss}')");
}
}
}
else
{
continue;
}
}
// Update IP and LastSeen, add new entries
tsql.ExecuteNonQueryACon(sqlSB.ToString());
// Update last power-state
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair <string, bool> entry in DevicePowerState)
{
sb.Append($"UPDATE Devices SET LastPowerState = '{(entry.Value ? 1 : 0)}' WHERE MACAddress = '{entry.Key}';");
}
tsql.ExecuteNonQueryACon(sb.ToString());
}
else
{
if (pProgressReplyBGW.CancellationPending)
{
return;
}
MessageBox.Show("The given range is not valid. Make sure the IPs are in the same subnet. Note: Only C-Class IP-Adresses are currently supported", "Invalid Range", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
