can refer this site
https://developer.cisco.com/meraki/api-v1/api-index/
namespace MyConsoleApp
{
class Program
{
private readonly IConfigurationService _configurationService;
private readonly IConfiguration _configuration; // Add this field
public Program(IConfigurationService configurationService, IConfiguration configuration)
{
_configurationService = configurationService;
_configuration = configuration; // Assign the injected IConfiguration instance
}
static async Task Main(string[] args)
{
IConfiguration configuration = new ConfigurationBuilder()
.SetBasePath(Directory.GetParent(AppContext.BaseDirectory).FullName)
.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
.Build();
// Setup dependency injection
var serviceProvider = new ServiceCollection()
.AddSingleton<IConfiguration>(configuration)
.AddSingleton<IConfigurationService, ConfigurationService>()
.AddHttpClient() // Add HttpClientFactory
.BuildServiceProvider();
// Resolve IConfigurationService and IConfiguration instances
var configurationService = serviceProvider.GetRequiredService<IConfigurationService>();
var config = serviceProvider.GetRequiredService<IConfiguration>();
// Initialize program
var program = new Program(configurationService, config); // Pass IConfiguration to Program constructor
// Call API to grab data
await program.GetNetworkDevices();
await program.GetNetworkFloorPlans();
await program.GetOrganizationDevicesStatuses();
await program.GetOrganizationCameraBoundariesAreasByDevice();
await program.GetOrganizationCameraBoundariesLinesByDevice();
await program.GetOrganizationCameraDetectionsHistoryByBoundaryByInterval();
await program.GetDeviceCameraVideoLink();
await program.GetOrganizationSensorReadingsHistory();
await program.GetDeviceSwitchPorts();
await program.GetDeviceSwitchPortsStatuses();
await program.GetNetworkClients();
await program.GetNetworkClientsUsageHistories();
await program.GetNetworkTopologyLinkLayer();
await program.GetDeviceLossAndLatencyHistory();
await program.GetNetworkObservations();
}
public static bool LogError(string connString, string functionName, object errorMessage)
{
try
{
using (MySqlConnection connection = new MySqlConnection(connString))
{
connection.Open();
string insertQuery = "INSERT INTO tblErrorLog (error_desc, error_table, error_date) " +
"VALUES (@error_desc, @error_table, CURRENT_TIMESTAMP())";
using (MySqlCommand command = new MySqlCommand(insertQuery, connection))
{
command.Parameters.AddWithValue("@error_desc", errorMessage);
command.Parameters.AddWithValue("@error_table", functionName);
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
LogError(connString, "LogError", ex.Message);
return false; // Indicate that an error occurred
}
return true; // Log operation was successful
}
#region NetworkStuff
public async Task GetNetworkDevices()
{
string connectionString = _configurationService.ConnectionString; // Ensure connectionString is accessible
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string networkId = _configurationService.NetworkId;
string baseurl = _configurationService.BaseUrl;
//ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
string url = $"{baseurl}/networks/{networkId}/devices";
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
List<Devices> devices = JsonConvert.DeserializeObject<List<Devices>>(apiResponseJson);
if (devices == null || !devices.Any())
{
throw new DataException("No devices data received from the API.");
}
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
// Begin a transaction for database operations
using (var transaction = connection.BeginTransaction())
{
try
{
foreach (var device in devices)
{
string checkQuery = "SELECT COUNT(*) FROM tblMasterDevicesMeraki WHERE DeviceSerial = @DeviceSerial AND NetworkId = @NetworkId";
using (var checkCommand = new MySqlCommand(checkQuery, connection, transaction))
{
checkCommand.Parameters.AddWithValue("@DeviceSerial", device.Serial);
checkCommand.Parameters.AddWithValue("@NetworkId", device.NetworkId);
var existingRecordCount = (long)await checkCommand.ExecuteScalarAsync();
if (existingRecordCount > 0)
{
// Record already exists, update it
string updateQuery = @"
UPDATE tblMasterDevicesMeraki
SET DeviceMacAddress = @DeviceMacAddress, Wan1Ip = @Wan1Ip, Wan2Ip = @Wan2Ip, LanIp = @LanIp, Url = @Url, DeviceName = @DeviceName, DeviceModel = @DeviceModel, DeviceFirmware = @DeviceFirmware, FloorPlanId = @FloorPlanId, DeviceLatitude = @DeviceLatitude, DeviceLongitude = @DeviceLongitude, Address = @Address, Tags = @Tags, Details = @Details, BeaconUuid = @BeaconUuid, BeaconMajor = @BeaconMajor, BeaconMinor = @BeaconMinor, SwitchProfileId = @SwitchProfileId, WirelessMac = @WirelessMac
WHERE DeviceSerial = @DeviceSerial AND NetworkId = @NetworkId";
using (var updateCommand = new MySqlCommand(updateQuery, connection, transaction))
{
updateCommand.Parameters.AddWithValue("@DeviceSerial", device.Serial);
updateCommand.Parameters.AddWithValue("@DeviceMacAddress", device.Mac);
updateCommand.Parameters.AddWithValue("@Wan1Ip", device.Wan1Ip);
updateCommand.Parameters.AddWithValue("@Wan2Ip", device.Wan2Ip);
updateCommand.Parameters.AddWithValue("@LanIp", device.LanIp);
updateCommand.Parameters.AddWithValue("@Url", device.Url);
updateCommand.Parameters.AddWithValue("@NetworkId", device.NetworkId);
updateCommand.Parameters.AddWithValue("@DeviceName", device.Name);
updateCommand.Parameters.AddWithValue("@DeviceModel", device.Model);
updateCommand.Parameters.AddWithValue("@DeviceFirmware", device.Firmware);
updateCommand.Parameters.AddWithValue("@FloorPlanId", device.FloorPlanId);
updateCommand.Parameters.AddWithValue("@DeviceLatitude", device.Lat);
updateCommand.Parameters.AddWithValue("@DeviceLongitude", device.Lng);
updateCommand.Parameters.AddWithValue("@Address", device.Address);
updateCommand.Parameters.AddWithValue("@Tags", string.Join(",", device.Tags));
updateCommand.Parameters.AddWithValue("@Details", string.Join(",", device.Details));
updateCommand.Parameters.AddWithValue("@BeaconUuid", device.BeaconIdParams?.Uuid);
updateCommand.Parameters.AddWithValue("@BeaconMajor", device.BeaconIdParams?.Major);
updateCommand.Parameters.AddWithValue("@BeaconMinor", device.BeaconIdParams?.Minor);
updateCommand.Parameters.AddWithValue("@SwitchProfileId", device.SwitchProfileId); // Add SwitchProfileId parameter
updateCommand.Parameters.AddWithValue("@WirelessMac", device.WirelessMac); // Add WirelessMac parameter
await updateCommand.ExecuteNonQueryAsync();
}
}
else
{
string insertQuery = @"
INSERT INTO tblMasterDevicesMeraki
(DeviceSerial, DeviceMacAddress, Wan1Ip, Wan2Ip, LanIp, Url, NetworkId, DeviceName, DeviceModel, DeviceFirmware, FloorPlanId, DeviceLatitude, DeviceLongitude, Address, Tags, Details, BeaconUuid, BeaconMajor, BeaconMinor, SwitchProfileId, WirelessMac)
VALUES
(@DeviceSerial, @DeviceMacAddress, @Wan1Ip, @Wan2Ip, @LanIp, @Url, @NetworkId, @DeviceName, @DeviceModel, @DeviceFirmware, @FloorPlanId, @DeviceLatitude, @DeviceLongitude, @Address, @Tags, @Details, @BeaconUuid, @BeaconMajor, @BeaconMinor, @SwitchProfileId, @WirelessMac)";
using (var insertCommand = new MySqlCommand(insertQuery, connection, transaction))
{
insertCommand.Parameters.AddWithValue("@DeviceSerial", device.Serial);
insertCommand.Parameters.AddWithValue("@DeviceMacAddress", device.Mac);
insertCommand.Parameters.AddWithValue("@Wan1Ip", device.Wan1Ip);
insertCommand.Parameters.AddWithValue("@Wan2Ip", device.Wan2Ip);
insertCommand.Parameters.AddWithValue("@LanIp", device.LanIp);
insertCommand.Parameters.AddWithValue("@Url", device.Url);
insertCommand.Parameters.AddWithValue("@NetworkId", device.NetworkId);
insertCommand.Parameters.AddWithValue("@DeviceName", device.Name);
insertCommand.Parameters.AddWithValue("@DeviceModel", device.Model);
insertCommand.Parameters.AddWithValue("@DeviceFirmware", device.Firmware);
insertCommand.Parameters.AddWithValue("@FloorPlanId", device.FloorPlanId);
insertCommand.Parameters.AddWithValue("@DeviceLatitude", device.Lat);
insertCommand.Parameters.AddWithValue("@DeviceLongitude", device.Lng);
insertCommand.Parameters.AddWithValue("@Address", device.Address);
insertCommand.Parameters.AddWithValue("@Tags", string.Join(",", device.Tags));
insertCommand.Parameters.AddWithValue("@Details", string.Join(",", device.Details));
insertCommand.Parameters.AddWithValue("@BeaconUuid", device.BeaconIdParams?.Uuid);
insertCommand.Parameters.AddWithValue("@BeaconMajor", device.BeaconIdParams?.Major);
insertCommand.Parameters.AddWithValue("@BeaconMinor", device.BeaconIdParams?.Minor);
insertCommand.Parameters.AddWithValue("@SwitchProfileId", device.SwitchProfileId); // Add SwitchProfileId parameter
insertCommand.Parameters.AddWithValue("@WirelessMac", device.WirelessMac); // Add WirelessMac parameter
await insertCommand.ExecuteNonQueryAsync();
}
}
}
}
// Commit the transaction if all database operations are successful
transaction.Commit();
}
catch (Exception ex)
{
// Rollback the transaction if an exception occurs
transaction.Rollback();
LogError(connectionString, nameof(GetNetworkDevices), ex.Message); // Log the error
throw; // Rethrow the exception for further handling
}
}
}
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"Network Devices inserted/updated successfully in {elapsed}");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetNetworkDevices), ex.Message);
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetNetworkDevices), ex.Message);
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetNetworkDevices), ex.Message);
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetNetworkDevices), ex.Message);
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (MySqlException ex)
{
LogError(connectionString, nameof(GetNetworkDevices), ex.Message);
Console.WriteLine($"MySQL Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
Console.WriteLine($"Error Code: {ex.ErrorCode}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetNetworkDevices), ex.Message);
Console.WriteLine($"Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
}
}
public async Task GetOrganizationDevicesStatuses()
{
string connectionString = _configurationService.ConnectionString; // Ensure connectionString is accessible
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string organizationId = _configurationService.OrganizationId;
string baseurl = _configurationService.BaseUrl;
//ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
string url = $"{baseurl}/organizations/{organizationId}/devices/statuses";
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
List<Devices> devices = JsonConvert.DeserializeObject<List<Devices>>(apiResponseJson);
if (devices == null || !devices.Any())
{
throw new DataException("No devices data received from the API.");
}
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
// Begin a transaction for database operations
using (var transaction = connection.BeginTransaction())
{
try
{
foreach (var device in devices)
{
string updateQuery = @"
UPDATE tblmasterdevicesmeraki
SET PublicIp = @PublicIp,
Status = @Status,
LastReportedAt = @LastReportedAt,
ProductType = @ProductType,
Gateway = @Gateway,
IpType = @IpType,
PrimaryDns = @PrimaryDns,
SecondaryDns = @SecondaryDns
WHERE DeviceSerial = @Serial AND NetworkId = @NetworkId";
using (var updateCommand = new MySqlCommand(updateQuery, connection, transaction))
{
updateCommand.Parameters.AddWithValue("@PublicIp", device.PublicIp);
updateCommand.Parameters.AddWithValue("@Status", device.Status);
updateCommand.Parameters.AddWithValue("@LastReportedAt", device.LastReportedAt);
updateCommand.Parameters.AddWithValue("@ProductType", device.ProductType);
updateCommand.Parameters.AddWithValue("@Gateway", device.Gateway);
updateCommand.Parameters.AddWithValue("@IpType", device.IpType);
updateCommand.Parameters.AddWithValue("@PrimaryDns", device.PrimaryDns);
updateCommand.Parameters.AddWithValue("@SecondaryDns", device.SecondaryDns);
updateCommand.Parameters.AddWithValue("@Serial", device.Serial);
updateCommand.Parameters.AddWithValue("@NetworkId", device.NetworkId);
await updateCommand.ExecuteNonQueryAsync();
}
}
// Commit the transaction if all database operations are successful
transaction.Commit();
}
catch (Exception ex)
{
// Rollback the transaction if an exception occurs
transaction.Rollback();
LogError(connectionString, nameof(GetOrganizationDevicesStatuses), ex.Message); // Log the error
throw; // Rethrow the exception for further handling
}
}
}
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"GetOrganizationDevicesStatuses: Organization Devices Statuses inserted/updated successfully in {elapsed}");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetOrganizationDevicesStatuses), ex.Message);
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetOrganizationDevicesStatuses), ex.Message);
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetOrganizationDevicesStatuses), ex.Message);
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetOrganizationDevicesStatuses), ex.Message);
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (MySqlException ex)
{
LogError(connectionString, nameof(GetOrganizationDevicesStatuses), ex.Message);
Console.WriteLine($"MySQL Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
Console.WriteLine($"Error Code: {ex.ErrorCode}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetOrganizationDevicesStatuses), ex.Message);
Console.WriteLine($"Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
}
}
public async Task GetNetworkFloorPlans()
{
string connectionString = _configurationService.ConnectionString; // Ensure connectionString is accessible
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string networkId = _configurationService.NetworkId;
string baseurl = _configurationService.BaseUrl;
//ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
string url = $"{baseurl}/networks/{networkId}/floorPlans";
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
List<FloorPlan> floorPlans = JsonConvert.DeserializeObject<List<FloorPlan>>(apiResponseJson);
if (floorPlans == null || !floorPlans.Any())
{
throw new DataException("No floorPlans data received from the API.");
}
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
// Begin a transaction for database operations
using (var transaction = connection.BeginTransaction())
{
try
{
foreach (var floorPlan in floorPlans)
{
string checkQuery = "SELECT COUNT(*) FROM tblFloorPlanInfo WHERE floorPlanId = @floorPlanId";
using (var checkCommand = new MySqlCommand(checkQuery, connection, transaction))
{
checkCommand.Parameters.AddWithValue("@floorPlanId", floorPlan.floorPlanId);
var existingRecordCount = (long)await checkCommand.ExecuteScalarAsync();
if (existingRecordCount > 0)
{
// Record already exists, update it
string updateQuery = @"
UPDATE tblFloorPlanInfo
SET imageUrl = @imageUrl, imageMd5 = @imageMd5, floorPlanName = @floorPlanName, imageExtension = @imageExtension,
imageUrlExpiresAt = @imageUrlExpiresAt, height = @height, width = @width, center_lat = @center_lat,
center_lng = @center_lng, bottomLeftCorner_lat = @bottomLeftCorner_lat, bottomLeftCorner_lng = @bottomLeftCorner_lng,
topLeftCorner_lat = @topLeftCorner_lat, topLeftCorner_lng = @topLeftCorner_lng, topRightCorner_lat = @topRightCorner_lat,
topRightCorner_lng = @topRightCorner_lng, bottomRightCorner_lat = @bottomRightCorner_lat, bottomRightCorner_lng = @bottomRightCorner_lng
, NetworkId = @NetworkId
WHERE floorPlanId = @floorPlanId";
using (var updateCommand = new MySqlCommand(updateQuery, connection, transaction))
{
updateCommand.Parameters.AddWithValue("@floorPlanId", floorPlan.floorPlanId);
updateCommand.Parameters.AddWithValue("@imageUrl", floorPlan.imageUrl);
updateCommand.Parameters.AddWithValue("@imageMd5", floorPlan.imageMd5);
updateCommand.Parameters.AddWithValue("@floorPlanName", floorPlan.name);
updateCommand.Parameters.AddWithValue("@imageExtension", floorPlan.imageExtension);
updateCommand.Parameters.AddWithValue("@imageUrlExpiresAt", floorPlan.imageUrlExpiresAt);
updateCommand.Parameters.AddWithValue("@height", floorPlan.height);
updateCommand.Parameters.AddWithValue("@width", floorPlan.width);
updateCommand.Parameters.AddWithValue("@center_lat", floorPlan.center.lat);
updateCommand.Parameters.AddWithValue("@center_lng", floorPlan.center.lng);
updateCommand.Parameters.AddWithValue("@bottomLeftCorner_lat", floorPlan.bottomLeftCorner.lat);
updateCommand.Parameters.AddWithValue("@bottomLeftCorner_lng", floorPlan.bottomLeftCorner.lng);
updateCommand.Parameters.AddWithValue("@topLeftCorner_lat", floorPlan.topLeftCorner.lat);
updateCommand.Parameters.AddWithValue("@topLeftCorner_lng", floorPlan.topLeftCorner.lng);
updateCommand.Parameters.AddWithValue("@topRightCorner_lat", floorPlan.topRightCorner.lat);
updateCommand.Parameters.AddWithValue("@topRightCorner_lng", floorPlan.topRightCorner.lng);
updateCommand.Parameters.AddWithValue("@bottomRightCorner_lat", floorPlan.bottomRightCorner.lat);
updateCommand.Parameters.AddWithValue("@bottomRightCorner_lng", floorPlan.bottomRightCorner.lng);
updateCommand.Parameters.AddWithValue("@NetworkId", networkId);
await updateCommand.ExecuteNonQueryAsync();
}
}
else
{
string insertQuery = @"
INSERT INTO tblFloorPlanInfo
(floorPlanId, imageUrl, imageMd5, floorPlanName, imageExtension, imageUrlExpiresAt, height, width, center_lat, center_lng,
bottomLeftCorner_lat, bottomLeftCorner_lng, topLeftCorner_lat, topLeftCorner_lng, topRightCorner_lat, topRightCorner_lng,
bottomRightCorner_lat, bottomRightCorner_lng,NetworkId)
VALUES
(@floorPlanId, @imageUrl, @imageMd5, @floorPlanName, @imageExtension, @imageUrlExpiresAt, @height, @width, @center_lat, @center_lng,
@bottomLeftCorner_lat, @bottomLeftCorner_lng, @topLeftCorner_lat, @topLeftCorner_lng, @topRightCorner_lat, @topRightCorner_lng,
@bottomRightCorner_lat, @bottomRightCorner_lng, @NetworkId)";
using (var insertCommand = new MySqlCommand(insertQuery, connection, transaction))
{
insertCommand.Parameters.AddWithValue("@floorPlanId", floorPlan.floorPlanId);
insertCommand.Parameters.AddWithValue("@imageUrl", floorPlan.imageUrl);
insertCommand.Parameters.AddWithValue("@imageMd5", floorPlan.imageMd5);
insertCommand.Parameters.AddWithValue("@floorPlanName", floorPlan.name);
insertCommand.Parameters.AddWithValue("@imageExtension", floorPlan.imageExtension);
insertCommand.Parameters.AddWithValue("@imageUrlExpiresAt", floorPlan.imageUrlExpiresAt);
insertCommand.Parameters.AddWithValue("@height", floorPlan.height);
insertCommand.Parameters.AddWithValue("@width", floorPlan.width);
insertCommand.Parameters.AddWithValue("@center_lat", floorPlan.center.lat);
insertCommand.Parameters.AddWithValue("@center_lng", floorPlan.center.lng);
insertCommand.Parameters.AddWithValue("@bottomLeftCorner_lat", floorPlan.bottomLeftCorner.lat);
insertCommand.Parameters.AddWithValue("@bottomLeftCorner_lng", floorPlan.bottomLeftCorner.lng);
insertCommand.Parameters.AddWithValue("@topLeftCorner_lat", floorPlan.topLeftCorner.lat);
insertCommand.Parameters.AddWithValue("@topLeftCorner_lng", floorPlan.topLeftCorner.lng);
insertCommand.Parameters.AddWithValue("@topRightCorner_lat", floorPlan.topRightCorner.lat);
insertCommand.Parameters.AddWithValue("@topRightCorner_lng", floorPlan.topRightCorner.lng);
insertCommand.Parameters.AddWithValue("@bottomRightCorner_lat", floorPlan.bottomRightCorner.lat);
insertCommand.Parameters.AddWithValue("@bottomRightCorner_lng", floorPlan.bottomRightCorner.lng);
insertCommand.Parameters.AddWithValue("@NetworkId", networkId);
await insertCommand.ExecuteNonQueryAsync();
}
}
}
}
// Commit the transaction if all database operations are successful
transaction.Commit();
}
catch (Exception ex)
{
// Rollback the transaction if an exception occurs
transaction.Rollback();
LogError(connectionString, nameof(GetNetworkFloorPlans), ex.Message); // Log the error
throw; // Rethrow the exception for further handling
}
}
}
stopwatch.Stop();
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"Floor plans inserted/updated successfully in {elapsed}");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetNetworkFloorPlans), ex.Message); // Log the error
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetNetworkFloorPlans), ex.Message); // Log the error
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetNetworkFloorPlans), ex.Message); // Log the error
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetNetworkFloorPlans), ex.Message); // Log the error
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (MySqlException ex)
{
LogError(connectionString, nameof(GetNetworkFloorPlans), ex.Message); // Log the error
Console.WriteLine($"MySQL Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
Console.WriteLine($"Error Code: {ex.ErrorCode}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetNetworkFloorPlans), ex.Message); // Log the error
Console.WriteLine($"Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
}
}
#endregion
private void ValidateConfiguration(string merakiApiKey, string organizationId, string baseurl, string connectionString)
{
List<string> missingConfigurations = new List<string>();
if (string.IsNullOrEmpty(merakiApiKey))
{
missingConfigurations.Add("MerakiApiKey");
}
if (string.IsNullOrEmpty(organizationId))
{
missingConfigurations.Add("OrganizationId");
}
if (string.IsNullOrEmpty(baseurl))
{
missingConfigurations.Add("Baseurl");
}
if (string.IsNullOrEmpty(connectionString))
{
missingConfigurations.Add("ConnectionString");
}
if (missingConfigurations.Any())
{
string missingConfigs = string.Join(", ", missingConfigurations);
throw new ConfigurationException($"Missing or invalid configuration values: {missingConfigs}");
}
}
#region Camera
public async Task GetOrganizationCameraBoundariesAreasByDevice()
{
string connectionString = _configurationService.ConnectionString; // Ensure connectionString is accessible
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string organizationId = _configurationService.OrganizationId;
string baseurl = _configurationService.BaseUrl;
ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
string url = $"{baseurl}/organizations/{organizationId}/camera/boundaries/areas/byDevice";
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
List<Camera> cameras = JsonConvert.DeserializeObject<List<Camera>>(apiResponseJson);
if (cameras == null || !cameras.Any())
{
throw new DataException("No Camera Boundaries Areas received from the API.");
}
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
var transaction = connection.BeginTransaction();
try
{
foreach (var camera in cameras)
{
if (camera.Boundaries == null || !camera.Boundaries.Any())
{
continue; // Skip if no boundaries for this camera
}
foreach (var boundary in camera.Boundaries)
{
if (boundary.Vertices == null || !boundary.Vertices.Any())
{
continue; // Skip if no vertices for this boundary
}
foreach (var vertex in boundary.Vertices)
{
// Check if the record already exists in the database
string checkQuery = @"
SELECT COUNT(*) FROM cameraboundaries
WHERE networkId = @networkId AND serial = @serial AND boundaryId = @boundaryId AND vertexX = @vertexX AND vertexY = @vertexY";
using (var checkCommand = new MySqlCommand(checkQuery, connection, transaction))
{
checkCommand.Parameters.AddWithValue("@networkId", camera.NetworkId);
checkCommand.Parameters.AddWithValue("@serial", camera.Serial);
checkCommand.Parameters.AddWithValue("@boundaryId", boundary.Id);
checkCommand.Parameters.AddWithValue("@vertexX", vertex.X);
checkCommand.Parameters.AddWithValue("@vertexY", vertex.Y);
var existingRecordCount = (long)await checkCommand.ExecuteScalarAsync();
if (existingRecordCount > 0)
{
// Record already exists, update it
string updateQuery = @"
UPDATE cameraboundaries
SET boundaryName = @boundaryName
WHERE networkId = @networkId AND serial = @serial AND boundaryId = @boundaryId AND vertexX = @vertexX AND vertexY = @vertexY";
using (var updateCommand = new MySqlCommand(updateQuery, connection, transaction))
{
updateCommand.Parameters.AddWithValue("@boundaryName", boundary.Name);
updateCommand.Parameters.AddWithValue("@networkId", camera.NetworkId);
updateCommand.Parameters.AddWithValue("@serial", camera.Serial);
updateCommand.Parameters.AddWithValue("@boundaryId", boundary.Id);
updateCommand.Parameters.AddWithValue("@vertexX", vertex.X);
updateCommand.Parameters.AddWithValue("@vertexY", vertex.Y);
await updateCommand.ExecuteNonQueryAsync();
}
}
else
{
// Insert the record
string insertQuery = @"
INSERT INTO cameraboundaries (networkId, serial, boundaryId, boundaryType, boundaryName, vertexX, vertexY)
VALUES (@networkId, @serial, @boundaryId, @boundaryType, @boundaryName, @vertexX, @vertexY)";
using (var insertCommand = new MySqlCommand(insertQuery, connection, transaction))
{
insertCommand.Parameters.AddWithValue("@networkId", camera.NetworkId);
insertCommand.Parameters.AddWithValue("@serial", camera.Serial);
insertCommand.Parameters.AddWithValue("@boundaryId", boundary.Id);
insertCommand.Parameters.AddWithValue("@boundaryType", boundary.Type);
insertCommand.Parameters.AddWithValue("@boundaryName", boundary.Name);
insertCommand.Parameters.AddWithValue("@vertexX", vertex.X);
insertCommand.Parameters.AddWithValue("@vertexY", vertex.Y);
await insertCommand.ExecuteNonQueryAsync();
}
}
}
}
}
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
LogError(connectionString, nameof(GetOrganizationCameraBoundariesAreasByDevice), ex.Message); // Log the error
throw; // Rethrow the exception for further handling
}
}
stopwatch.Stop();
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"Camera Boundaries Areas inserted/updated successfully in {elapsed}");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraBoundariesAreasByDevice), ex.Message); // Log the error
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraBoundariesAreasByDevice), ex.Message); // Log the error
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraBoundariesAreasByDevice), ex.Message); // Log the error
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraBoundariesAreasByDevice), ex.Message); // Log the error
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (MySqlException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraBoundariesAreasByDevice), ex.Message); // Log the error
Console.WriteLine($"MySQL Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
Console.WriteLine($"Error Code: {ex.ErrorCode}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetOrganizationCameraBoundariesAreasByDevice), ex.Message); // Log the error
Console.WriteLine($"Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
}
}
public async Task GetOrganizationCameraBoundariesLinesByDevice()
{
string connectionString = _configurationService.ConnectionString; // Ensure connectionString is accessible
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string organizationId = _configurationService.OrganizationId;
string baseurl = _configurationService.BaseUrl;
string networkId = _configurationService.NetworkId;
ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
string url = $"{baseurl}/organizations/{organizationId}/camera/boundaries/lines/byDevice";
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
List<Camera> cameras = JsonConvert.DeserializeObject<List<Camera>>(apiResponseJson);
HashSet<string> apiBoundaryIds = new HashSet<string>();
foreach (var camera in cameras)
{
if (camera.Boundaries != null)
{
foreach (var boundary in camera.Boundaries)
{
apiBoundaryIds.Add(boundary.Id);
}
}
}
if (cameras == null || !cameras.Any())
{
throw new DataException("No Camera Boundaries Lines received from the API.");
}
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
// Begin a transaction for database operations
using (var transaction = connection.BeginTransaction())
{
// Update IsActive flag for records that are not in the API data
var placeholders = string.Join(",", apiBoundaryIds.Select((id, index) => $"@id{index}"));
string updateFlag = $"UPDATE cameraboundaries SET IsActive = FALSE WHERE networkId = @networkId AND boundaryType = 'line' AND boundaryId NOT IN ({placeholders})";
using (var updateCommand = new MySqlCommand(updateFlag, connection, transaction))
{
updateCommand.Parameters.AddWithValue("@networkId", networkId);
for (int i = 0; i < apiBoundaryIds.Count; i++)
{
updateCommand.Parameters.AddWithValue($"@id{i}", apiBoundaryIds.ElementAt(i));
}
await updateCommand.ExecuteNonQueryAsync();
}
try
{
foreach (var camera in cameras)
{
if (camera.Boundaries == null || !camera.Boundaries.Any())
{
continue; // Skip if no boundaries for this camera
}
foreach (var boundary in camera.Boundaries)
{
if (boundary.Vertices == null || !boundary.Vertices.Any())
{
continue; // Skip if no vertices for this boundary
}
foreach (var vertex in boundary.Vertices)
{
//string checkQuery = @"
//SELECT COUNT(*) FROM cameraboundaries
//WHERE networkId = @networkId AND serial = @serial AND boundaryId = @boundaryId AND vertexX = @vertexX AND vertexY = @vertexY";
string checkQuery = @"
SELECT COUNT(*) FROM cameraboundaries
WHERE networkId = @networkId AND serial = @serial AND boundaryId = @boundaryId ";
using (var checkCommand = new MySqlCommand(checkQuery, connection, transaction))
{
checkCommand.Parameters.AddWithValue("@networkId", camera.NetworkId);
checkCommand.Parameters.AddWithValue("@serial", camera.Serial);
checkCommand.Parameters.AddWithValue("@boundaryId", boundary.Id);
//checkCommand.Parameters.AddWithValue("@vertexX", vertex.X);
//checkCommand.Parameters.AddWithValue("@vertexY", vertex.Y);
var existingRecordCount = (long)await checkCommand.ExecuteScalarAsync();
if (existingRecordCount > 0)
{
// Record already exists, update it
// string updateQuery = @"
//UPDATE cameraboundaries
//SET boundaryName = @boundaryName, directionVertexX = @directionVertexX, directionVertexY = @directionVertexY
//WHERE networkId = @networkId AND serial = @serial AND boundaryId = @boundaryId AND vertexX = @vertexX AND vertexY = @vertexY";
string updateQuery = @"
UPDATE cameraboundaries
SET boundaryName = @boundaryName, directionVertexX = @directionVertexX, directionVertexY = @directionVertexY
WHERE networkId = @networkId AND serial = @serial AND boundaryId = @boundaryId AND vertexX = @vertexX AND vertexY = @vertexY";
using (var updateCommand = new MySqlCommand(updateQuery, connection, transaction))
{
updateCommand.Parameters.AddWithValue("@boundaryName", boundary.Name);
updateCommand.Parameters.AddWithValue("@directionVertexX", boundary.DirectionVertex?.X);
updateCommand.Parameters.AddWithValue("@directionVertexY", boundary.DirectionVertex?.Y);
updateCommand.Parameters.AddWithValue("@networkId", camera.NetworkId);
updateCommand.Parameters.AddWithValue("@serial", camera.Serial);
updateCommand.Parameters.AddWithValue("@boundaryId", boundary.Id);
updateCommand.Parameters.AddWithValue("@vertexX", vertex.X);
updateCommand.Parameters.AddWithValue("@vertexY", vertex.Y);
await updateCommand.ExecuteNonQueryAsync();
}
}
else
{
// Insert the record if it doesn't already exist
string insertQuery = @"
INSERT INTO cameraboundaries (networkId, serial, boundaryId, boundaryType, boundaryName, vertexX, vertexY, directionVertexX, directionVertexY)
VALUES (@networkId, @serial, @boundaryId, @boundaryType, @boundaryName, @vertexX, @vertexY, @directionVertexX, @directionVertexY)";
using (var insertCommand = new MySqlCommand(insertQuery, connection, transaction))
{
insertCommand.Parameters.AddWithValue("@networkId", camera.NetworkId);
insertCommand.Parameters.AddWithValue("@serial", camera.Serial);
insertCommand.Parameters.AddWithValue("@boundaryId", boundary.Id);
insertCommand.Parameters.AddWithValue("@boundaryType", boundary.Type);
insertCommand.Parameters.AddWithValue("@boundaryName", boundary.Name);
insertCommand.Parameters.AddWithValue("@vertexX", vertex.X);
insertCommand.Parameters.AddWithValue("@vertexY", vertex.Y);
insertCommand.Parameters.AddWithValue("@directionVertexX", boundary.DirectionVertex.X);
insertCommand.Parameters.AddWithValue("@directionVertexY", boundary.DirectionVertex.Y);
await insertCommand.ExecuteNonQueryAsync();
}
}
}
}
}
}
// Commit the transaction if all database operations are successful
transaction.Commit();
}
catch (Exception ex)
{
// Rollback the transaction if an exception occurs
transaction.Rollback();
LogError(connectionString, nameof(GetOrganizationCameraBoundariesLinesByDevice), ex.Message); // Log the error
throw; // Rethrow the exception for further handling
}
}
}
stopwatch.Stop();
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"Camera Boundaries Lines inserted/updated successfully in {elapsed}");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraBoundariesLinesByDevice), ex.Message); // Log the error
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraBoundariesLinesByDevice), ex.Message); // Log the error
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraBoundariesLinesByDevice), ex.Message); // Log the error
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraBoundariesLinesByDevice), ex.Message); // Log the error
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (MySqlException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraBoundariesLinesByDevice), ex.Message); // Log the error
Console.WriteLine($"MySQL Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
Console.WriteLine($"Error Code: {ex.ErrorCode}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetOrganizationCameraBoundariesLinesByDevice), ex.Message); // Log the error
Console.WriteLine($"Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
}
}
public async Task GetOrganizationCameraDetectionsHistoryByBoundaryByInterval()
{
string connectionString = _configurationService.ConnectionString; // Ensure connectionString is accessible
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string organizationId = _configurationService.OrganizationId;
string baseurl = _configurationService.BaseUrl;
ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// Fetch boundary IDs from the cameraboundaries table
List<string> boundaryIds = await FetchBoundaryIdsFromDatabase(connectionString);
string url = $"{baseurl}/organizations/{organizationId}/camera/detections/history/byBoundary/byInterval";
// Get the current UTC time
DateTime currentUtcTime = DateTime.UtcNow.Date;
// Adjust the current UTC time by subtracting 8 hours
DateTime adjustedTime = currentUtcTime.AddHours(-8);
// Set the start time to the beginning of the day (00:00:00) adjusted to GMT-8
DateTime startTime = adjustedTime;
// Set the end time to the end of the day (23:59:59) adjusted to GMT-8
DateTime endTime = adjustedTime.AddDays(1).AddSeconds(-1);
// Format the start and end times to the desired format (e.g., "yyyy-MM-ddTHH:mm:ssZ")
string startTimeFormatted = startTime.ToString("yyyy-MM-ddTHH:mm:ssZ");
string endTimeFormatted = endTime.ToString("yyyy-MM-ddTHH:mm:ssZ");
var requestData = new
{
ranges = new[]
{
new
{
interval = 900,
startTime= startTimeFormatted,
endTime= endTimeFormatted
},
},
boundaryIds
};
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration, requestData);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
List<AreaOccupancy> cameras = JsonConvert.DeserializeObject<List<AreaOccupancy>>(apiResponseJson);
if (cameras == null || !cameras.Any())
{
throw new DataException("No Camera Detections History By Boundary received from the API.");
}
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
// Start a transaction
using (var transaction = connection.BeginTransaction())
{
try
{
// Delete existing records for today
string deleteQuery = "DELETE FROM cameradetectionshistory WHERE StartTime >= @StartTime AND EndTime <= @EndTime";
using (MySqlCommand deleteCommand = new MySqlCommand(deleteQuery, connection, transaction))
{
deleteCommand.Parameters.AddWithValue("@StartTime", startTime);
deleteCommand.Parameters.AddWithValue("@EndTime", endTime);
await deleteCommand.ExecuteNonQueryAsync();
}
// Insert new records
foreach (var areaOccupancy in cameras)
{
if (areaOccupancy.Results == null) continue;
foreach (var result in areaOccupancy.Results)
{
string query = "INSERT INTO cameradetectionshistory (BoundaryId,Type, StartTime, EndTime, ObjectType, InCount, OutCount) " +
"VALUES (@BoundaryId,@Type, @StartTime, @EndTime, @ObjectType, @InCount, @OutCount)";
using (var command = new MySqlCommand(query, connection, transaction))
{
command.Parameters.AddWithValue("@BoundaryId", areaOccupancy.BoundaryId);
command.Parameters.AddWithValue("@Type", areaOccupancy.Type);
command.Parameters.AddWithValue("@StartTime", result.StartTime);
command.Parameters.AddWithValue("@EndTime", result.EndTime);
command.Parameters.AddWithValue("@ObjectType", result.ObjectType);
command.Parameters.AddWithValue("@InCount", result.In);
command.Parameters.AddWithValue("@OutCount", result.Out);
await command.ExecuteNonQueryAsync();
}
}
}
// Commit the transaction if all operations succeed
transaction.Commit();
}
catch (Exception ex)
{
// Roll back the transaction if an error occurs
transaction.Rollback();
LogError(connectionString, nameof(GetOrganizationCameraDetectionsHistoryByBoundaryByInterval), ex.Message); // Log the error
throw; // Re-throw the exception to propagate it further
}
}
}
stopwatch.Stop();
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"Camera Detections History By Boundary inserted/updated successfully in {elapsed}");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraDetectionsHistoryByBoundaryByInterval), ex.Message); // Log the error
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraDetectionsHistoryByBoundaryByInterval), ex.Message); // Log the error
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraDetectionsHistoryByBoundaryByInterval), ex.Message); // Log the error
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraDetectionsHistoryByBoundaryByInterval), ex.Message); // Log the error
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (MySqlException ex)
{
LogError(connectionString, nameof(GetOrganizationCameraDetectionsHistoryByBoundaryByInterval), ex.Message); // Log the error
Console.WriteLine($"MySQL Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
Console.WriteLine($"Error Code: {ex.ErrorCode}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetOrganizationCameraDetectionsHistoryByBoundaryByInterval), ex.Message); // Log the error
Console.WriteLine($"Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
}
}
// Helper method to fetch boundary IDs from the cameraboundaries table
private async Task<List<string>> FetchBoundaryIdsFromDatabase(string connectionString)
{
List<string> boundaryIds = new List<string>();
string networkId = _configurationService.NetworkId;
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
string query = "SELECT DISTINCT boundaryId FROM cameraboundaries WHERE networkId = @NetworkId ";
using (var command = new MySqlCommand(query, connection))
{
command.Parameters.AddWithValue("@NetworkId", networkId);
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
string boundaryId = reader["boundaryId"].ToString();
boundaryIds.Add(boundaryId);
}
}
}
}
return boundaryIds;
}
private async Task<List<string>> GetDeviceSerialsByModel(string connectionString, string model , string networkId)
{
List<string> serialNumbers = new List<string>();
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
// Construct SQL query to select serial numbers with DeviceModel containing the specified model
string serialQuery = "SELECT DeviceSerial FROM tblMasterDevicesMeraki WHERE DeviceModel LIKE @Model AND NetworkId = @networkId";
using (var command = new MySqlCommand(serialQuery, connection))
{
// Add model parameter
command.Parameters.AddWithValue("@Model", $"%{model}%");
command.Parameters.AddWithValue("@networkId", networkId);
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
string serial = reader.GetString("DeviceSerial");
serialNumbers.Add(serial);
}
}
}
}
return serialNumbers;
}
public async Task GetDeviceCameraVideoLink()
{
string connectionString = _configurationService.ConnectionString; // Ensure connectionString is accessible
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string organizationId = _configurationService.OrganizationId;
string baseurl = _configurationService.BaseUrl;
string networkId = _configurationService.NetworkId;
ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// Execute the query to fetch the serial
List<string> serialNumbers = await GetDeviceSerialsByModel(connectionString, "MV", networkId);
foreach (var serial in serialNumbers)
{
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
try
{
string url = $"{baseurl}/devices/{serial}/camera/videoLink";
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
MerakiCameraUrls cameraUrls = JsonConvert.DeserializeObject<MerakiCameraUrls>(apiResponseJson);
string updateQuery = @"
UPDATE tblMasterDevicesMeraki
SET CameraUrl = @CameraUrl, CameraVisionUrl = @CameraVisionUrl
WHERE DeviceSerial = @DeviceSerial";
using (var command = new MySqlCommand(updateQuery, connection, transaction))
{
command.Parameters.AddWithValue("@CameraUrl", cameraUrls.Url);
command.Parameters.AddWithValue("@CameraVisionUrl", cameraUrls.VisionUrl);
command.Parameters.AddWithValue("@DeviceSerial", serial);
command.ExecuteNonQuery();
}
// Commit the transaction if all operations succeed
transaction.Commit();
}
catch (Exception ex)
{
// Roll back the transaction if an error occurs
transaction.Rollback();
LogError(connectionString, nameof(GetDeviceCameraVideoLink), ex.Message); // Log the error
Console.WriteLine($"Transaction rolled back for device {serial}. Exception: {ex.Message}");
}
}
}
}
stopwatch.Stop();
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"Device Camera Video Link inserted/updated successfully in {elapsed}");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetDeviceCameraVideoLink), ex.Message); // Log the error
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetDeviceCameraVideoLink), ex.Message); // Log the error
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetDeviceCameraVideoLink), ex.Message); // Log the error
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetDeviceCameraVideoLink), ex.Message); // Log the error
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (MySqlException ex)
{
LogError(connectionString, nameof(GetDeviceCameraVideoLink), ex.Message); // Log the error
Console.WriteLine($"MySQL Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
Console.WriteLine($"Error Code: {ex.ErrorCode}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetDeviceCameraVideoLink), ex.Message); // Log the error
Console.WriteLine($"Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
}
}
#endregion
#region Sensor
public async Task GetOrganizationSensorReadingsHistory()
{
string connectionString = _configurationService.ConnectionString; // Ensure connectionString is accessible
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string organizationId = _configurationService.OrganizationId;
string baseurl = _configurationService.BaseUrl;
ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
DateTime currentUtcTime = DateTime.UtcNow.Date;
DateTime adjustedTime = currentUtcTime.AddHours(-8);
DateTime startTime = adjustedTime;
DateTime endTime = adjustedTime.AddDays(1).AddSeconds(-1);
string startTimeFormatted = startTime.ToString("yyyy-MM-ddTHH:mm:ssZ");
string endTimeFormatted = endTime.ToString("yyyy-MM-ddTHH:mm:ssZ");
string url = $"{baseurl}/organizations/{organizationId}/sensor/readings/history?t0={startTimeFormatted}&t1={endTimeFormatted}";
string url2 = $"{baseurl}/organizations/{organizationId}/sensor/readings/latest";
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
HttpResponseMessage response2 = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url2, merakiApiKey, _configuration);
response2.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
List<Record> sensorData = JsonConvert.DeserializeObject<List<Record>>(apiResponseJson);
string apiResponseJsonLatest = await response2.Content.ReadAsStringAsync();
List<Record> sensorLatestData = JsonConvert.DeserializeObject<List<Record>>(apiResponseJsonLatest);
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
// Start a transaction
using (var transaction = connection.BeginTransaction())
{
try
{
// Delete existing records for today
string deleteQuery = "DELETE FROM tblSensorReadingsHistory WHERE reading_timestamp >= @StartTime AND reading_timestamp <= @EndTime";
using (MySqlCommand deleteCommand = new MySqlCommand(deleteQuery, connection, transaction))
{
deleteCommand.Parameters.AddWithValue("@StartTime", startTime.ToString("yyyy-MM-dd HH:mm:ss"));
deleteCommand.Parameters.AddWithValue("@EndTime", endTime.ToString("yyyy-MM-dd HH:mm:ss"));
await deleteCommand.ExecuteNonQueryAsync();
}
string query = @"INSERT INTO tblSensorReadingsHistory (Serial, NetworkId,NetworkName, reading_timestamp, Metric, TemperatureFahrenheit, TemperatureCelsius, BatteryPercentage)
VALUES (@Serial, @NetworkId, @NetworkName, @Timestamp, @Metric, @TemperatureFahrenheit, @TemperatureCelsius, @BatteryPercentage)";
// Create MySqlCommand object
using (MySqlCommand command = new MySqlCommand(query, connection, transaction))
{
foreach (Record record in sensorData)
{
command.Parameters.Clear();
command.Parameters.AddWithValue("@Serial", record.Serial);
command.Parameters.AddWithValue("@NetworkId", record.Network.Id);
command.Parameters.AddWithValue("@NetworkName", record.Network.Name);
command.Parameters.AddWithValue("@Timestamp", record.Ts);
command.Parameters.AddWithValue("@Metric", record.Metric);
if (record.Metric == "temperature")
{
command.Parameters.AddWithValue("@TemperatureFahrenheit", record.Temperature.Fahrenheit);
command.Parameters.AddWithValue("@TemperatureCelsius", record.Temperature.Celsius);
command.Parameters.AddWithValue("@BatteryPercentage", DBNull.Value);
}
else if (record.Metric == "battery")
{
command.Parameters.AddWithValue("@TemperatureFahrenheit", DBNull.Value);
command.Parameters.AddWithValue("@TemperatureCelsius", DBNull.Value);
command.Parameters.AddWithValue("@BatteryPercentage", record.Battery.Percentage);
}
await command.ExecuteNonQueryAsync();
}
}
string deleteQuery2 = "DELETE FROM tbllatestsensorreadings WHERE reading_timestamp >= @StartTime AND reading_timestamp <= @EndTime";
using (MySqlCommand deleteCommand2 = new MySqlCommand(deleteQuery2, connection, transaction))
{
deleteCommand2.Parameters.AddWithValue("@StartTime", startTime.ToString("yyyy-MM-dd HH:mm:ss"));
deleteCommand2.Parameters.AddWithValue("@EndTime", endTime.ToString("yyyy-MM-dd HH:mm:ss"));
await deleteCommand2.ExecuteNonQueryAsync();
}
string query2 = @"INSERT INTO tbllatestsensorreadings (Serial, NetworkId, NetworkName, reading_timestamp, Metric, TemperatureFahrenheit, TemperatureCelsius, BatteryPercentage)
VALUES (@Serial, @NetworkId, @NetworkName, @Timestamp, @Metric, @TemperatureFahrenheit, @TemperatureCelsius, @BatteryPercentage)";
// Create MySqlCommand object
using (MySqlCommand command = new MySqlCommand(query2, connection, transaction))
{
foreach (Record record in sensorLatestData)
{
foreach (Reading reading in record.Readings)
{
command.Parameters.Clear();
command.Parameters.AddWithValue("@Serial", record.Serial);
command.Parameters.AddWithValue("@NetworkId", record.Network.Id);
command.Parameters.AddWithValue("@NetworkName", record.Network.Name);
command.Parameters.AddWithValue("@Timestamp", reading.Ts); // Use the timestamp from readings
command.Parameters.AddWithValue("@Metric", reading.Metric);
if (reading.Metric == "temperature")
{
command.Parameters.AddWithValue("@TemperatureFahrenheit", reading.Temperature.Fahrenheit);
command.Parameters.AddWithValue("@TemperatureCelsius", reading.Temperature.Celsius);
command.Parameters.AddWithValue("@BatteryPercentage", DBNull.Value);
}
else if (reading.Metric == "battery")
{
command.Parameters.AddWithValue("@TemperatureFahrenheit", DBNull.Value);
command.Parameters.AddWithValue("@TemperatureCelsius", DBNull.Value);
command.Parameters.AddWithValue("@BatteryPercentage", reading.Battery.Percentage);
}
await command.ExecuteNonQueryAsync();
// command.Parameters.RemoveAt("@Timestamp"); // Remove the timestamp parameter for the next iteration
// command.Parameters.RemoveAt("@Metric"); // Remove the timestamp parameter for the next iteration
}
}
}
// Commit the transaction if all operations succeed
transaction.Commit();
}
catch (MySqlException ex)
{
// Handle MySQL-specific exceptions
LogError(connectionString, nameof(GetOrganizationSensorReadingsHistory), ex); // Log the error
Console.WriteLine($"MySQL Error: {ex.Number} - {ex.Message}");
// Roll back the transaction if an error occurs
transaction.Rollback();
Console.WriteLine("Transaction rolled back.");
}
catch (Exception ex)
{
// Handle other exceptions
LogError(connectionString, nameof(GetOrganizationSensorReadingsHistory), ex); // Log the error
Console.WriteLine($"Error: {ex.Message}");
// Roll back the transaction if an error occurs
transaction.Rollback();
Console.WriteLine("Transaction rolled back.");
}
}
}
stopwatch.Stop();
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"Organization Sensor Readings History inserted/updated successfully in {elapsed}");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetOrganizationSensorReadingsHistory), ex); // Log the error
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetOrganizationSensorReadingsHistory), ex); // Log the error
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetOrganizationSensorReadingsHistory), ex); // Log the error
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetOrganizationSensorReadingsHistory), ex); // Log the error
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetOrganizationSensorReadingsHistory), ex); // Log the error
Console.WriteLine($"Exception: {ex.Message}");
}
}
#endregion
#region Switch
public async Task GetDeviceSwitchPorts()
{
string connectionString = _configurationService.ConnectionString; // Ensure connectionString is accessible
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string organizationId = _configurationService.OrganizationId;
string baseurl = _configurationService.BaseUrl;
string networkId = _configurationService.NetworkId;
ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// Execute the query to fetch the serial
List<string> serialNumbers = await GetDeviceSerialsByModel(connectionString, "MS", networkId);
foreach (var serial in serialNumbers)
{
string url = $"{baseurl}/devices/{serial}/switch/ports";
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
List<Port> ports = JsonConvert.DeserializeObject<List<Port>>(apiResponseJson);
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
// Start the transaction
using (var transaction = connection.BeginTransaction())
{
try
{
string insertQuery = @"
INSERT INTO tblSwitchPortsInfo
(DeviceSerial, PortId, Name, Tags, Enabled, PoeEnabled, Type, Vlan, AllowedVlans, IsolationEnabled, RstpEnabled,
StpGuard, LinkNegotiation, Udld, LinkNegotiationCapabilities, AccessPolicyType, DaiTrusted, ProfileEnabled,
ProfileId, ProfileIname, ModuleModel, MirrorMode, VoiceVlan, PortScheduleId)
VALUES
(@DeviceSerial, @PortId, @Name, @Tags, @Enabled, @PoeEnabled, @Type, @Vlan, @AllowedVlans, @IsolationEnabled, @RstpEnabled,
@StpGuard, @LinkNegotiation, @Udld, @LinkNegotiationCapabilities, @AccessPolicyType, @DaiTrusted, @ProfileEnabled,
@ProfileId, @ProfileIname, @ModuleModel, @MirrorMode, @VoiceVlan, @PortScheduleId)
ON DUPLICATE KEY UPDATE
Name = VALUES(Name),
Tags = VALUES(Tags),
Enabled = VALUES(Enabled),
PoeEnabled = VALUES(PoeEnabled),
Type = VALUES(Type),
Vlan = VALUES(Vlan),
AllowedVlans = VALUES(AllowedVlans),
IsolationEnabled = VALUES(IsolationEnabled),
RstpEnabled = VALUES(RstpEnabled),
StpGuard = VALUES(StpGuard),
LinkNegotiation = VALUES(LinkNegotiation),
Udld = VALUES(Udld),
LinkNegotiationCapabilities = VALUES(LinkNegotiationCapabilities),
AccessPolicyType = VALUES(AccessPolicyType),
DaiTrusted = VALUES(DaiTrusted),
ProfileEnabled = VALUES(ProfileEnabled),
ProfileId = VALUES(ProfileId),
ProfileIname = VALUES(ProfileIname),
ModuleModel = VALUES(ModuleModel),
MirrorMode = VALUES(MirrorMode),
VoiceVlan = VALUES(VoiceVlan),
PortScheduleId = VALUES(PortScheduleId)
";
foreach (var port in ports)
{
using (var command = new MySqlCommand(insertQuery, connection))
{
command.Parameters.AddWithValue("@DeviceSerial", serial);
command.Parameters.AddWithValue("@PortId", port.PortId);
command.Parameters.AddWithValue("@Name", port.Name);
command.Parameters.AddWithValue("@Tags", JsonConvert.SerializeObject(port.Tags));
command.Parameters.AddWithValue("@Enabled", port.Enabled);
command.Parameters.AddWithValue("@PoeEnabled", port.PoeEnabled);
command.Parameters.AddWithValue("@Type", port.Type);
command.Parameters.AddWithValue("@Vlan", port.Vlan);
command.Parameters.AddWithValue("@AllowedVlans", port.AllowedVlans);
command.Parameters.AddWithValue("@IsolationEnabled", port.IsolationEnabled);
command.Parameters.AddWithValue("@RstpEnabled", port.RstpEnabled);
command.Parameters.AddWithValue("@StpGuard", port.StpGuard);
command.Parameters.AddWithValue("@LinkNegotiation", port.LinkNegotiation);
command.Parameters.AddWithValue("@Udld", port.Udld);
command.Parameters.AddWithValue("@LinkNegotiationCapabilities", JsonConvert.SerializeObject(port.LinkNegotiationCapabilities));
command.Parameters.AddWithValue("@AccessPolicyType", port.AccessPolicyType);
command.Parameters.AddWithValue("@DaiTrusted", port.DaiTrusted);
command.Parameters.AddWithValue("@ProfileEnabled", port.Profile?.Enabled);
command.Parameters.AddWithValue("@ProfileId", port.Profile?.Id);
command.Parameters.AddWithValue("@ProfileIname", port.Profile?.Iname ?? DBNull.Value);
command.Parameters.AddWithValue("@ModuleModel", port.Module?.Model ?? DBNull.Value);
command.Parameters.AddWithValue("@MirrorMode", port.Mirror?.Mode);
command.Parameters.AddWithValue("@VoiceVlan", port.VoiceVlan ?? DBNull.Value);
command.Parameters.AddWithValue("@PortScheduleId", port.PortScheduleId ?? DBNull.Value);
await command.ExecuteNonQueryAsync();
}
}
// Commit the transaction if all operations succeed
transaction.Commit();
}
catch (Exception ex)
{
// Log the error before rolling back the transaction
LogError(connectionString, nameof(GetDeviceSwitchPorts), ex);
// Rollback the transaction if any exception occurs
transaction.Rollback();
throw; // Re-throw the exception to handle it at a higher level
}
}
}
}
stopwatch.Stop();
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"Get Device SwitchPorts inserted/updated successfully in {elapsed}");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetDeviceSwitchPorts), ex);
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetDeviceSwitchPorts), ex);
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetDeviceSwitchPorts), ex);
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetDeviceSwitchPorts), ex);
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (MySqlException ex)
{
LogError(connectionString, nameof(GetDeviceSwitchPorts), ex);
Console.WriteLine($"MySQL Exception: {ex.Message}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetDeviceSwitchPorts), ex);
Console.WriteLine($"Exception: {ex.Message}");
}
}
public async Task GetDeviceSwitchPortsStatuses()
{
string connectionString = _configurationService.ConnectionString; // Ensure connectionString is accessible
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string organizationId = _configurationService.OrganizationId;
string baseurl = _configurationService.BaseUrl;
string networkId = _configurationService.NetworkId;
ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// Execute the query to fetch the serial
List<string> serialNumbers = await GetDeviceSerialsByModel(connectionString, "MS", networkId);
foreach (var serial in serialNumbers)
{
string url = $"{baseurl}/devices/{serial}/switch/ports/statuses";
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
List<Port> ports = JsonConvert.DeserializeObject<List<Port>>(apiResponseJson);
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
foreach (var port in ports)
{
string upsertQuery = @"
INSERT INTO tblSwitchAdditionalPortData
(
DeviceSerial,
PortId,
Status,
IsUplink,
Speed,
Duplex,
ClientCount,
PowerUsageInWh,
Errors,
Warnings,
UsageInKb,
Cdp,
Lldp,
TrafficInKbps,
SecurePort,
SpanningTree,
Poe
)
VALUES
(
@DeviceSerial,
@PortId,
@Status,
@IsUplink,
@Speed,
@Duplex,
@ClientCount,
@PowerUsageInWh,
@Errors,
@Warnings,
@UsageInKb,
@Cdp,
@Lldp,
@TrafficInKbps,
@SecurePort,
@SpanningTree,
@Poe
)
ON DUPLICATE KEY UPDATE
Status = VALUES(Status),
IsUplink = VALUES(IsUplink),
Speed = VALUES(Speed),
Duplex = VALUES(Duplex),
ClientCount = VALUES(ClientCount),
PowerUsageInWh = VALUES(PowerUsageInWh),
Errors = VALUES(Errors),
Warnings = VALUES(Warnings),
UsageInKb = VALUES(UsageInKb),
Cdp = VALUES(Cdp),
Lldp = VALUES(Lldp),
TrafficInKbps = VALUES(TrafficInKbps),
SecurePort = VALUES(SecurePort),
SpanningTree = VALUES(SpanningTree),
Poe = VALUES(Poe)";
using (var command = new MySqlCommand(upsertQuery, connection))
{
command.Parameters.AddWithValue("@DeviceSerial", serial);
command.Parameters.AddWithValue("@PortId", port.PortId);
command.Parameters.AddWithValue("@Status", port.Status);
command.Parameters.AddWithValue("@IsUplink", port.IsUplink);
command.Parameters.AddWithValue("@Speed", port.Speed);
command.Parameters.AddWithValue("@Duplex", port.Duplex);
command.Parameters.AddWithValue("@ClientCount", port.ClientCount);
command.Parameters.AddWithValue("@PowerUsageInWh", port.PowerUsageInWh);
command.Parameters.AddWithValue("@Errors", JsonConvert.SerializeObject(port.Errors));
command.Parameters.AddWithValue("@Warnings", JsonConvert.SerializeObject(port.Warnings));
command.Parameters.AddWithValue("@UsageInKb", JsonConvert.SerializeObject(port.UsageInKb));
command.Parameters.AddWithValue("@Cdp", JsonConvert.SerializeObject(port.Cdp));
command.Parameters.AddWithValue("@Lldp", JsonConvert.SerializeObject(port.Lldp));
command.Parameters.AddWithValue("@TrafficInKbps", JsonConvert.SerializeObject(port.TrafficInKbps));
command.Parameters.AddWithValue("@SecurePort", JsonConvert.SerializeObject(port.SecurePort));
command.Parameters.AddWithValue("@SpanningTree", JsonConvert.SerializeObject(port.SpanningTree));
command.Parameters.AddWithValue("@Poe", JsonConvert.SerializeObject(port.Poe));
await command.ExecuteNonQueryAsync();
}
string updateQuery = @"
UPDATE tblSwitchPortsInfo
SET Status = @Status
WHERE DeviceSerial = @DeviceSerial AND PortId = @PortId";
using (var command = new MySqlCommand(updateQuery, connection))
{
command.Parameters.AddWithValue("@DeviceSerial", serial);
command.Parameters.AddWithValue("@PortId", port.PortId);
command.Parameters.AddWithValue("@Status", port.Status);
await command.ExecuteNonQueryAsync();
}
}
}
}
stopwatch.Stop();
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"Device Switch Ports Statuses inserted/updated successfully in {elapsed}");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetDeviceSwitchPortsStatuses), ex);
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetDeviceSwitchPortsStatuses), ex);
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetDeviceSwitchPortsStatuses), ex);
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetDeviceSwitchPortsStatuses), ex);
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (MySqlException ex)
{
LogError(connectionString, nameof(GetDeviceSwitchPortsStatuses), ex);
Console.WriteLine($"MySQL Exception: {ex.Message}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetDeviceSwitchPortsStatuses), ex);
Console.WriteLine($"Exception: {ex.Message}");
}
}
#endregion
#region Wireless
public async Task GetNetworkClients()
{
string connectionString = _configurationService.ConnectionString;
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string networkId = _configurationService.NetworkId;
string baseurl = _configurationService.BaseUrl;
//ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
string url = $"{baseurl}/networks/{networkId}/clients";
DateTime currentUtcTime = DateTime.UtcNow.Date;
DateTime adjustedTime = currentUtcTime.AddHours(-8);
DateTime startTime = adjustedTime;
DateTime endTime = adjustedTime.AddDays(1).AddSeconds(-1);
string startTimeFormatted = startTime.ToString("yyyy-MM-ddTHH:mm:ssZ");
string endTimeFormatted = endTime.ToString("yyyy-MM-ddTHH:mm:ssZ");
// Hardcoded query parameters
string queryParams = $"t0={startTimeFormatted}&&t1={endTimeFormatted}&perPage=5000";
// Append query string to URL
url += "?" + queryParams;
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
List<Wireless> wirelesses = JsonConvert.DeserializeObject<List<Wireless>>(apiResponseJson);
if (wirelesses == null || !wirelesses.Any())
{
throw new DataException("No wirelesses data received from the API.");
}
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction = await connection.BeginTransactionAsync())
{
try
{
foreach (var wireless in wirelesses)
{
string query = @"
INSERT INTO tblWirelessInfo
(clientid, networkId, mac, description, ip, ip6, ip6Local, user, firstSeen, lastSeen, manufacturer, os, deviceTypePrediction, recentDeviceSerial, recentDeviceName, recentDeviceMac, recentDeviceConnection, ssid, vlan, switchport, sent, recv, total, status, groupPolicy8021x, adaptivePolicyGroup, smInstalled, namedVlan, pskGroup, wirelessCapabilities)
VALUES
(@ClientId,@NetworkId, @Mac, @Description, @Ip, @Ip6, @Ip6Local, @User, @FirstSeen, @LastSeen, @Manufacturer, @Os, @DeviceTypePrediction, @RecentDeviceSerial, @RecentDeviceName, @RecentDeviceMac, @RecentDeviceConnection, @Ssid, @Vlan, @Switchport, @Sent, @Recv, @Total, @Status, @GroupPolicy8021x, @AdaptivePolicyGroup, @SmInstalled, @NamedVlan, @PskGroup, @WirelessCapabilities)
AS new_data
ON DUPLICATE KEY UPDATE
networkId = new_data.networkId,
mac = new_data.mac,
description = new_data.description,
ip = new_data.ip,
ip6 = new_data.ip6,
ip6Local = new_data.ip6Local,
user = new_data.user,
firstSeen = new_data.firstSeen,
lastSeen = new_data.lastSeen,
manufacturer = new_data.manufacturer,
os = new_data.os,
deviceTypePrediction = new_data.deviceTypePrediction,
recentDeviceSerial = new_data.recentDeviceSerial,
recentDeviceName = new_data.recentDeviceName,
recentDeviceMac = new_data.recentDeviceMac,
recentDeviceConnection = new_data.recentDeviceConnection,
ssid = new_data.ssid,
vlan = new_data.vlan,
switchport = new_data.switchport,
sent = new_data.sent,
recv = new_data.recv,
total = new_data.total,
status = new_data.status,
groupPolicy8021x = new_data.groupPolicy8021x,
adaptivePolicyGroup = new_data.adaptivePolicyGroup,
smInstalled = new_data.smInstalled,
namedVlan = new_data.namedVlan,
pskGroup = new_data.pskGroup,
wirelessCapabilities = new_data.wirelessCapabilities;
";
using (var command = new MySqlCommand(query, connection))
{
command.Parameters.AddWithValue("@ClientId", wireless.id);
command.Parameters.AddWithValue("@NetworkId", networkId); // Add networkId parameter
command.Parameters.AddWithValue("@Mac", wireless.mac);
command.Parameters.AddWithValue("@Description", wireless.description);
command.Parameters.AddWithValue("@Ip", wireless.ip);
command.Parameters.AddWithValue("@Ip6", wireless.ip6);
command.Parameters.AddWithValue("@Ip6Local", wireless.ip6Local);
command.Parameters.AddWithValue("@User", wireless.user);
command.Parameters.AddWithValue("@FirstSeen", wireless.firstSeen);
command.Parameters.AddWithValue("@LastSeen", wireless.lastSeen);
command.Parameters.AddWithValue("@Manufacturer", wireless.manufacturer);
command.Parameters.AddWithValue("@Os", wireless.os);
command.Parameters.AddWithValue("@DeviceTypePrediction", wireless.deviceTypePrediction);
command.Parameters.AddWithValue("@RecentDeviceSerial", wireless.recentDeviceSerial);
command.Parameters.AddWithValue("@RecentDeviceName", wireless.recentDeviceName);
command.Parameters.AddWithValue("@RecentDeviceMac", wireless.recentDeviceMac);
command.Parameters.AddWithValue("@RecentDeviceConnection", wireless.recentDeviceConnection);
command.Parameters.AddWithValue("@Ssid", wireless.ssid);
command.Parameters.AddWithValue("@Vlan", wireless.vlan);
command.Parameters.AddWithValue("@Switchport", wireless.switchport);
command.Parameters.AddWithValue("@Sent", wireless.usage?.sent ?? 0);
command.Parameters.AddWithValue("@Recv", wireless.usage?.recv ?? 0);
command.Parameters.AddWithValue("@Total", wireless.usage?.total ?? 0);
command.Parameters.AddWithValue("@Status", wireless.status);
command.Parameters.AddWithValue("@GroupPolicy8021x", wireless.groupPolicy8021x);
command.Parameters.AddWithValue("@AdaptivePolicyGroup", wireless.adaptivePolicyGroup);
command.Parameters.AddWithValue("@SmInstalled", wireless.smInstalled);
command.Parameters.AddWithValue("@NamedVlan", wireless.namedVlan);
command.Parameters.AddWithValue("@PskGroup", wireless.pskGroup);
command.Parameters.AddWithValue("@WirelessCapabilities", wireless.wirelessCapabilities);
await command.ExecuteNonQueryAsync();
}
}
// Commit the transaction if all operations succeed
await transaction.CommitAsync();
}
catch (Exception ex)
{
await transaction.RollbackAsync();
LogError(connectionString, nameof(GetNetworkClients), ex);
Console.WriteLine("Transaction rolled back due to exception: " + ex.Message);
}
}
}
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"Network Clients inserted/updated successfully in {elapsed}.");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetNetworkClients), ex);
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetNetworkClients), ex);
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetNetworkClients), ex);
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetNetworkClients), ex);
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (MySqlException ex)
{
LogError(connectionString, nameof(GetNetworkClients), ex);
Console.WriteLine($"MySQL Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
Console.WriteLine($"Error Code: {ex.ErrorCode}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetNetworkClients), ex);
Console.WriteLine($"Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
}
}
public async Task GetNetworkClientsUsageHistories()
{
string connectionString = _configurationService.ConnectionString;
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string networkId = _configurationService.NetworkId;
string baseurl = _configurationService.BaseUrl;
//ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
string url = $"{baseurl}/networks/{networkId}/clients/usageHistories";
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
DateTime currentUtcTime = DateTime.UtcNow.Date;
DateTime adjustedTime = currentUtcTime.AddHours(-8);
DateTime startTime = adjustedTime;
List<string> clientIds = await FetchNetworkClientIdsFromDatabase(connectionString, startTime);
DateTime endTime = adjustedTime.AddDays(1).AddSeconds(-1);
string startTimeFormatted = startTime.ToString("yyyy-MM-ddTHH:mm:ssZ");
string endTimeFormatted = endTime.ToString("yyyy-MM-ddTHH:mm:ssZ");
// Hardcoded query parameters
string queryParams = $"t0={startTimeFormatted}&&t1={endTimeFormatted}&clients={string.Join(",", clientIds)}";
// Append query string to URL
url += "?" + queryParams;
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
List<ClientUsage> clientUsageHistories = JsonConvert.DeserializeObject<List<ClientUsage>>(apiResponseJson);
if (clientUsageHistories == null || !clientUsageHistories.Any())
{
throw new DataException("No wirelesses data received from the API.");
}
var clientUsageHistoriesWithNonNullUsage = clientUsageHistories
.Where(history => history.usageHistory == null)
.ToList();
List<Task> allTasks = new List<Task>();
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
// Start a transaction
using (var transaction = await connection.BeginTransactionAsync())
{
try
{
foreach (var clientUsageHistory in clientUsageHistories)
{
// Delete today's data for the current client
string deleteQuery = "DELETE FROM tblWirelessUsageHistory WHERE ClientId = @ClientId AND EventDateTime >= @StartTime AND EventDateTime <= @EndTime";
using (MySqlCommand deleteCommand = new MySqlCommand(deleteQuery, connection, transaction))
{
deleteCommand.Parameters.AddWithValue("@ClientId", clientUsageHistory.clientId);
deleteCommand.Parameters.AddWithValue("@StartTime", startTime);
deleteCommand.Parameters.AddWithValue("@EndTime", endTime);
// Execute the command and wait for it to complete
await deleteCommand.ExecuteNonQueryAsync();
}
string insertClientQuery = @"INSERT INTO tblWirelessUsageHistory (ClientId, ClientIp, ClientMac, EventDateTime, Received, Sent, system_created_at) VALUES (@ClientId, @ClientIp, @ClientMac, @Timestamp, @Received, @Sent, @CreatedAt)";
foreach (var usage in clientUsageHistory.usageHistory ?? Enumerable.Empty<UsageHistory>())
{
using (MySqlCommand usageInsertCommand = new MySqlCommand(insertClientQuery, connection, transaction))
{
usageInsertCommand.Parameters.AddWithValue("@ClientId", clientUsageHistory.clientId);
usageInsertCommand.Parameters.AddWithValue("@ClientIp", clientUsageHistory.clientIp);
usageInsertCommand.Parameters.AddWithValue("@ClientMac", clientUsageHistory.clientMac);
usageInsertCommand.Parameters.AddWithValue("@Timestamp", usage.ts);
usageInsertCommand.Parameters.AddWithValue("@Received", usage.received);
usageInsertCommand.Parameters.AddWithValue("@Sent", usage.sent);
usageInsertCommand.Parameters.AddWithValue("@CreatedAt", startTime);
// Execute the command and wait for it to complete
await usageInsertCommand.ExecuteNonQueryAsync();
}
}
}
// Wait for all tasks to complete
await Task.WhenAll(allTasks);
// Commit the transaction if all operations succeed
await transaction.CommitAsync();
}
catch (Exception ex)
{
await transaction.RollbackAsync();
LogError(connectionString, nameof(GetNetworkClientsUsageHistories), ex);
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"Network Clients Usage Histories inserted/updated successfully in {elapsed}.");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetNetworkClientsUsageHistories), ex);
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetNetworkClientsUsageHistories), ex);
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetNetworkClientsUsageHistories), ex);
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetNetworkClientsUsageHistories), ex);
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (MySqlException ex)
{
LogError(connectionString, nameof(GetNetworkClientsUsageHistories), ex);
Console.WriteLine($"MySQL Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
Console.WriteLine($"Error Code: {ex.ErrorCode}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetNetworkClientsUsageHistories), ex);
Console.WriteLine($"Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
}
}
private async Task<List<string>> FetchNetworkClientIdsFromDatabase(string connectionString, DateTime startTime)
{
List<string> clientids = new List<string>();
string networkId = _configurationService.NetworkId;
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
string query = "SELECT DISTINCT clientid FROM tblwirelessinfo WHERE networkId = @NetworkId AND lastSeen >= @StartDate";
using (var command = new MySqlCommand(query, connection))
{
command.Parameters.AddWithValue("@NetworkId", networkId);
command.Parameters.AddWithValue("@StartDate", startTime);
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
string clientid = reader["clientid"].ToString();
clientids.Add(clientid);
}
}
}
}
return clientids;
}
#endregion
#region Topology
public async Task GetNetworkTopologyLinkLayer()
{
string connectionString = _configurationService.ConnectionString;
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string networkId = _configurationService.NetworkId;
string baseurl = _configurationService.BaseUrl;
//ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
string url = $"{baseurl}/networks/{networkId}/topology/linkLayer";
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
var networkTopologies = JsonConvert.DeserializeObject<NetworkTopology>(apiResponseJson);
if (networkTopologies == null)
{
throw new DataException("No networkTopologies data received from the API.");
}
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
string query = @"
INSERT INTO tblnetworktopology (networkId, json_data, created_at, updated_at)
VALUES (@networkId, @json, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON DUPLICATE KEY UPDATE json_data = @json, updated_at = CURRENT_TIMESTAMP
";
await connection.ExecuteAsync(query, new { networkId = networkId, json = apiResponseJson }, transaction: transaction);
await transaction.CommitAsync();
}
}
//using (var connection = new MySqlConnection(connectionString))
//{
// await connection.OpenAsync();
// using (var transaction = connection.BeginTransaction())
// {
// try
// {
// // Clear existing data
// await ClearExistingData(connection, transaction);
// // Insert new data
// await InsertNewData(networkTopologies.Nodes, networkTopologies.Links, connection, transaction);
// // Commit the transaction if all operations succeed
// await transaction.CommitAsync();
// Console.WriteLine("Network topology data synchronized successfully.");
// }
// catch (Exception ex)
// {
// // Rollback the transaction if an error occurs
// await transaction.RollbackAsync();
// throw; // Rethrow the exception to indicate that the operation failed
// }
// }
//}
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine($"Network topology data inserted/updated successfully in {elapsed}");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetNetworkTopologyLinkLayer), ex);
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetNetworkTopologyLinkLayer), ex);
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetNetworkTopologyLinkLayer), ex);
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetNetworkTopologyLinkLayer), ex);
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (MySqlException ex)
{
LogError(connectionString, nameof(GetNetworkTopologyLinkLayer), ex);
Console.WriteLine($"MySQL Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
Console.WriteLine($"Error Code: {ex.ErrorCode}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetNetworkTopologyLinkLayer), ex);
Console.WriteLine($"Exception: {ex.Message}");
// Log exception with detailed information for debugging
Console.WriteLine($"Exception Details: {ex.StackTrace}");
}
}
private async Task ClearExistingData(MySqlConnection connection, MySqlTransaction transaction)
{
string clearNodesQuery = "DELETE FROM tblnetworktopologynodes;";
string clearLinksQuery = "DELETE FROM tblnetworktopologylinks;";
MySqlCommand clearNodesCommand = new MySqlCommand(clearNodesQuery, connection, transaction);
MySqlCommand clearLinksCommand = new MySqlCommand(clearLinksQuery, connection, transaction);
await clearNodesCommand.ExecuteNonQueryAsync();
await clearLinksCommand.ExecuteNonQueryAsync();
}
private async Task InsertNewData(List<Node> nodes, List<Link> links, MySqlConnection connection, MySqlTransaction transaction)
{
foreach (var node in nodes)
{
string insertNodeQuery = @"
INSERT INTO tblnetworktopologynodes (DerivedId, Mac, Type, Root, Serial, Name, Model, ProductType, Status, LastReportedAt, TotalClients, VlanId)
VALUES (@DerivedId, @Mac, @Type, @Root, @Serial, @Name, @Model, @ProductType, @Status, @LastReportedAt, @TotalClients, @VlanId);
";
MySqlCommand insertNodeCommand = new MySqlCommand(insertNodeQuery, connection, transaction);
insertNodeCommand.Parameters.AddWithValue("@DerivedId", node.NodeId);
insertNodeCommand.Parameters.AddWithValue("@Mac", node.MacAddress);
insertNodeCommand.Parameters.AddWithValue("@Type", node.NodeType);
insertNodeCommand.Parameters.AddWithValue("@Root", node.IsRootNode);
insertNodeCommand.Parameters.AddWithValue("@Serial", node.DeviceDetails?.SerialNumber != null ? (object)node.DeviceDetails.SerialNumber : DBNull.Value);
insertNodeCommand.Parameters.AddWithValue("@Name", node.DeviceDetails?.DeviceName != null ? (object)node.DeviceDetails.DeviceName : DBNull.Value);
insertNodeCommand.Parameters.AddWithValue("@Model", node.DeviceDetails?.DeviceModel != null ? (object)node.DeviceDetails.DeviceModel : DBNull.Value);
insertNodeCommand.Parameters.AddWithValue("@ProductType", node.DeviceDetails?.ProductType != null ? (object)node.DeviceDetails.ProductType : DBNull.Value);
insertNodeCommand.Parameters.AddWithValue("@Status", node.DeviceDetails?.DeviceStatus != null ? (object)node.DeviceDetails.DeviceStatus : DBNull.Value);
insertNodeCommand.Parameters.AddWithValue("@LastReportedAt", node.DeviceDetails?.LastReportedAt != null ? (object)node.DeviceDetails.LastReportedAt : DBNull.Value);
insertNodeCommand.Parameters.AddWithValue("@TotalClients", node.DeviceDetails?.ClientInformation?.Counts?.TotalCount ?? 0);
insertNodeCommand.Parameters.AddWithValue("@VlanId", node.DeviceDetails?.Uplinks?.FirstOrDefault()?.VlanId != null ? (object)node.DeviceDetails.Uplinks.FirstOrDefault().VlanId : DBNull.Value);
await insertNodeCommand.ExecuteNonQueryAsync();
}
foreach (var link in links)
{
string insertLinkQuery = @"
INSERT INTO tblnetworktopologylinks (FirstNodeId, SecondNodeId, PortId1, PortDescription1, NativeVlan1, PortId2, PortDescription2, NativeVlan2, LastReportedAt)
VALUES (@FirstNodeId, @SecondNodeId, @PortId1, @PortDescription1, @NativeVlan1, @PortId2, @PortDescription2, @NativeVlan2, @LastReportedAt);
";
MySqlCommand insertLinkCommand = new MySqlCommand(insertLinkQuery, connection);
insertLinkCommand.Parameters.AddWithValue("@FirstNodeId", link.LinkEnds[0].ConnectedNode.NodeId);
insertLinkCommand.Parameters.AddWithValue("@SecondNodeId", link.LinkEnds[1].ConnectedNode.NodeId);
insertLinkCommand.Parameters.AddWithValue("@PortId1", link.LinkEnds[0].ConnectedProtocols?.CdpInfo?.PortId != null ? (object)link.LinkEnds[0].ConnectedProtocols.CdpInfo.PortId : DBNull.Value);
insertLinkCommand.Parameters.AddWithValue("@PortDescription1", link.LinkEnds[0].ConnectedProtocols?.LldpInfo?.PortDescription != null ? (object)link.LinkEnds[0].ConnectedProtocols.LldpInfo.PortDescription : DBNull.Value);
insertLinkCommand.Parameters.AddWithValue("@NativeVlan1", link.LinkEnds[0].ConnectedProtocols?.CdpInfo?.NativeVlan != null ? (object)link.LinkEnds[0].ConnectedProtocols.CdpInfo.NativeVlan : DBNull.Value);
insertLinkCommand.Parameters.AddWithValue("@PortId2", link.LinkEnds[1].ConnectedProtocols?.CdpInfo?.PortId != null ? (object)link.LinkEnds[1].ConnectedProtocols.CdpInfo.PortId : DBNull.Value);
insertLinkCommand.Parameters.AddWithValue("@PortDescription2", link.LinkEnds[1].ConnectedProtocols?.LldpInfo?.PortDescription != null ? (object)link.LinkEnds[1].ConnectedProtocols.LldpInfo.PortDescription : DBNull.Value);
insertLinkCommand.Parameters.AddWithValue("@NativeVlan2", link.LinkEnds[1].ConnectedProtocols?.CdpInfo?.NativeVlan != null ? (object)link.LinkEnds[1].ConnectedProtocols.CdpInfo.NativeVlan : DBNull.Value);
insertLinkCommand.Parameters.AddWithValue("@LastReportedAt", (object)link.LastReportedAt ?? DBNull.Value);
await insertLinkCommand.ExecuteNonQueryAsync();
}
}
#endregion
#region Security
public async Task GetDeviceLossAndLatencyHistory()
{
string connectionString = _configurationService.ConnectionString;
try
{
string merakiApiKey = _configurationService.MerakiApiKey;
string organizationId = _configurationService.OrganizationId;
string baseurl = _configurationService.BaseUrl;
string networkId = _configurationService.NetworkId;
ValidateConfiguration(merakiApiKey, organizationId, baseurl, connectionString);
DateTime currentUtcTime = DateTime.UtcNow.Date;
DateTime adjustedTime = currentUtcTime.AddHours(-8);
DateTime startTime = adjustedTime;
DateTime endTime = adjustedTime.AddDays(1).AddSeconds(-1);
string startTimeFormatted = startTime.ToString("yyyy-MM-ddTHH:mm:ssZ");
string endTimeFormatted = endTime.ToString("yyyy-MM-ddTHH:mm:ssZ");
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
List<string> serialNumbers = await GetDeviceSerialsByModel(connectionString, "MX", networkId);
foreach (var serial in serialNumbers)
{
foreach (var uplink in new[] { "wan1", "wan2" })
{
string url = $"{baseurl}/devices/{serial}/lossAndLatencyHistory?t0={startTimeFormatted}&t1={endTimeFormatted}&uplink={uplink}&ip=8.8.8.8";
HttpResponseMessage response = await CommonFunction.ExecuteApiRequest<HttpResponseMessage>(url, merakiApiKey, _configuration);
response.EnsureSuccessStatusCode();
string apiResponseJson = await response.Content.ReadAsStringAsync();
List<Security> lossandlatency = JsonConvert.DeserializeObject<List<Security>>(apiResponseJson);
// Proceed only if there is data
if (lossandlatency.Any())
{
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
try
{
string deleteQuery = "DELETE FROM tblLossandlatency WHERE StartTs >= @StartTime AND EndTs <= @EndTime AND Uplink = @Uplink AND DeviceSerial = @DeviceSerial";
using (MySqlCommand deleteCommand = new MySqlCommand(deleteQuery, connection, transaction))
{
deleteCommand.Parameters.AddWithValue("@StartTime", startTime);
deleteCommand.Parameters.AddWithValue("@EndTime", endTime);
deleteCommand.Parameters.AddWithValue("@Uplink", uplink);
deleteCommand.Parameters.AddWithValue("@DeviceSerial", serial);
await deleteCommand.ExecuteNonQueryAsync();
}
foreach (var data in lossandlatency)
{
string insertQuery = @"
INSERT INTO tblLossandlatency (StartTs, EndTs, LossPercent, LatencyMs, Goodput, Jitter, DeviceSerial, Uplink)
VALUES (@StartTs, @EndTs, @LossPercent, @LatencyMs, @Goodput, @Jitter, @DeviceSerial, @Uplink);
";
using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection, transaction))
{
insertCommand.Parameters.AddWithValue("@StartTs", data.StartTs);
insertCommand.Parameters.AddWithValue("@EndTs", data.EndTs);
insertCommand.Parameters.AddWithValue("@LossPercent", data.LossPercent);
insertCommand.Parameters.AddWithValue("@LatencyMs", data.LatencyMs);
insertCommand.Parameters.AddWithValue("@Goodput", data.Goodput);
insertCommand.Parameters.AddWithValue("@Jitter", data.Jitter);
insertCommand.Parameters.AddWithValue("@DeviceSerial", serial);
insertCommand.Parameters.AddWithValue("@Uplink", uplink);
await insertCommand.ExecuteNonQueryAsync();
}
}
transaction.Commit();
}
catch (Exception ex)
{
// Rollback the transaction if an exception occurs
transaction.Rollback();
LogError(connectionString, nameof(GetDeviceLossAndLatencyHistory), ex.Message); // Log the error
throw; // Rethrow the exception for further handling
}
}
}
}
}
}
stopwatch.Stop();
TimeSpan elapsed = stopwatch.Elapsed;
Console.WriteLine("DeviceLoss And Latency History committed successfully.");
}
catch (ConfigurationException ex)
{
LogError(connectionString, nameof(GetDeviceLossAndLatencyHistory), ex);
Console.WriteLine($"Configuration Exception: {ex.Message}");
}
catch (HttpRequestException ex)
{
LogError(connectionString, nameof(GetDeviceLossAndLatencyHistory), ex);
Console.WriteLine($"HTTP Request Exception: {ex.Message}");
}
catch (JsonException ex)
{
LogError(connectionString, nameof(GetDeviceLossAndLatencyHistory), ex);
Console.WriteLine($"JSON Deserialization Exception: {ex.Message}");
}
catch (DataException ex)
{
LogError(connectionString, nameof(GetDeviceLossAndLatencyHistory), ex);
Console.WriteLine($"Data Exception: {ex.Message}");
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetDeviceLossAndLatencyHistory), ex);
Console.WriteLine($"Exception: {ex.Message}");
}
}
#endregion
#region MerakiLocation
public async Task GetNetworkObservations()
{
string connectionString = _configurationService.ConnectionString;
try
{
using (var conn = new MySqlConnection(connectionString))
{
await conn.OpenAsync();
using (var transaction = conn.BeginTransaction())
{
try
{
var networkObservation = await GetNetworkObservation(conn, transaction);
if (networkObservation == null)
return;
foreach (var observation in networkObservation.Data.Observations)
{
try
{
await UpdateMatchingDevices(conn, transaction, observation);
}
catch (Exception ex)
{
// Log the error message and stack trace
Console.WriteLine($"Exception during update for observation: {observation.Ipv4}, Exception: {ex}");
}
}
transaction.Commit();
// Display the name of the function upon success
Console.WriteLine("GetNetworkObservations executed successfully.");
}
catch (Exception ex)
{
// Rollback the transaction if an exception occurs
transaction.Rollback();
LogError(connectionString, nameof(GetNetworkFloorPlans), ex.Message); // Log the error
throw; // Rethrow the exception for further handling
}
}
}
}
catch (Exception ex)
{
LogError(connectionString, nameof(GetDeviceLossAndLatencyHistory), ex);
// Log the error message and stack trace
Console.WriteLine($"Exception: {ex}");
}
}
private async Task<MerakiLocationApi.NetworkObservation> GetNetworkObservation(MySqlConnection conn, MySqlTransaction transaction)
{
using (var cmd = new MySqlCommand("SELECT Data, CreatedAt FROM tblmerakilocationapi ORDER BY CreatedAt DESC LIMIT 1", conn))
{
cmd.Transaction = transaction;
using (var reader = await cmd.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
var data = reader.GetString("Data");
return JsonConvert.DeserializeObject<MerakiLocationApi.NetworkObservation>(data);
}
}
return null;
}
}
private async Task UpdateMatchingDevices(MySqlConnection conn, MySqlTransaction transaction, MerakiLocationApi.Observation observation)
{
var devices = new List<Device>();
using (var cmd = new MySqlCommand("SELECT DeviceName, DeviceIP, DeviceMacAddress, DeviceLocation, DeviceType, DeviceStatus, lat, lng, DeviceLatestRecordTime, DeviceCurrentLocationTime FROM tblwmsdevices WHERE DeviceType = 'Scanner' AND DeviceMacAddress = @ClientMac", conn))
{
cmd.Parameters.AddWithValue("@ClientMac", observation.ClientMac);
cmd.Transaction = transaction;
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var device = new Device
{
DeviceName = reader["DeviceName"].ToString(),
DeviceIP = reader["DeviceIP"].ToString(),
DeviceMacAddress = reader["DeviceMacAddress"].ToString(),
DeviceLocation = reader["DeviceLocation"].ToString(),
DeviceType = reader["DeviceType"].ToString(),
DeviceStatus = reader["DeviceStatus"].ToString(),
Lat = reader.IsDBNull(reader.GetOrdinal("lat")) ? (double?)null : Convert.ToDouble(reader["lat"]),
Lng = reader.IsDBNull(reader.GetOrdinal("lng")) ? (double?)null : Convert.ToDouble(reader["lng"]),
DeviceLatestRecordTime = reader.IsDBNull(reader.GetOrdinal("DeviceLatestRecordTime")) ? (DateTime?)null : Convert.ToDateTime(reader["DeviceLatestRecordTime"]),
DeviceCurrentLocationTime = reader.IsDBNull(reader.GetOrdinal("DeviceCurrentLocationTime")) ? (DateTime?)null : Convert.ToDateTime(reader["DeviceCurrentLocationTime"]),
};
devices.Add(device);
}
}
}
foreach (var device in devices)
{
await UpdateDevice(conn, transaction, device, observation);
}
}
private async Task UpdateDevice(MySqlConnection conn, MySqlTransaction transaction, Device device, MerakiLocationApi.Observation observation)
{
using (var cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.Transaction = transaction;
// Set common parameters
cmd.Parameters.AddWithValue("@latestRecordTime", DateTime.Parse(observation.LatestRecord.Time));
cmd.Parameters.AddWithValue("@DeviceMacAddress", device.DeviceMacAddress);
// Check if Locations list is not empty
if (observation.Locations.Any())
{
var location = observation.Locations[0];
cmd.CommandText = "UPDATE tblwmsdevices SET Lat = @lat, Lng = @lng, DeviceCurrentLocationTime = @currentLocationTime, DeviceStatus = 'online', DeviceLatestRecordTime = @latestRecordTime WHERE DeviceMacAddress = @DeviceMacAddress";
cmd.Parameters.AddWithValue("@lat", location.Lat);
cmd.Parameters.AddWithValue("@lng", location.Lng);
cmd.Parameters.AddWithValue("@currentLocationTime", DateTime.Parse(location.Time));
}
else // If Locations list is empty
{
cmd.CommandText = "UPDATE tblwmsdevices SET DeviceStatus = 'offline', DeviceLatestRecordTime = @latestRecordTime WHERE DeviceMacAddress = @DeviceMacAddress";
}
await cmd.ExecuteNonQueryAsync();
}
}
#endregion
}
}