public List <LocationsMac> GetLocationsMac(List <int> loc)
{
List <LocationsMac> cc = new List <LocationsMac>();
using (var context = new organisationdatabaseEntities())
{
try
{
cc = (from c in context.classdetails
where loc.Contains(c.classID)
select new
{
cid = c.classID,
ccmac = c.ccmac,
deskmac = c.deskmac
}).AsEnumerable().Select(x => new LocationsMac
{
ClassId = x.cid,
CCMac = x.ccmac,
DeskMac = x.deskmac
}).ToList();
}
finally
{
context.Dispose();
}
}
return(cc);
}
/// <summary>
/// count the occurence of a ccmac in classdetails.
/// </summary>
/// <param name="machinemac">The machinemac.</param>
/// <returns>System.Int32.</returns>
public int MachineCount(List <string> machinemac)
{
int r = 0;
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
if (c.Name.Contains("Entities"))
{
try
{
using (var context = new organisationdatabaseEntities(c.Name))
{
r = context.classdetails.Where(x => machinemac.Contains(x.ccmac)).Count();
if (r > 0)
{
found = true;
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
else
{
break;
}
}
return(r);
}
/// <summary>
/// Saves the alarm messages
/// </summary>
/// <param name="ip">The ip.</param>
/// <param name="msg">The MSG.</param>
/// <param name="altime">The altime.</param>
/// <returns>System.Int32.</returns>
public int SaveMonitoringDetails(string ip, string msg, string altime)
{
int result = 0;
try
{
using (var context = new organisationdatabaseEntities())
{
var cid = context.classdetails.Where(x => x.camipS == ip || x.camipT == ip).Select(x => x.classID).FirstOrDefault();
var monitorobj = new alarmmonitorlog()
{
almMessage = msg,
almTime = Convert.ToDateTime(altime),
Classid = cid,
deviceip = ip
};
context.alarmmonitorlogs.Add(monitorobj);
result = context.SaveChanges();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(result);
}
public KeyValuePair <string, string> GetMac(string[] mac)
{
KeyValuePair <string, string> result = new KeyValuePair <string, string>();
using (var context = new organisationdatabaseEntities())
{
try
{
foreach (string m in mac)
{
var temp = m.Replace(':', ' ').ToUpper();
if (context.classdetails.Any(x => x.deskmac.ToUpper() == temp))
{
var d = context.classdetails.Where(x => x.deskmac.ToUpper() == temp).Select(y => y.ccmac).SingleOrDefault();
result = new KeyValuePair <string, string>(temp, d.ToUpper());
break;
}
}
}
finally
{
context.Dispose();
}
}
return(result);
}
/// <summary>
/// Gets the test time data.
/// </summary>
/// <param name="time">The time.</param>
/// <param name="dbname">The dbname.</param>
/// <returns>List<StrategyDesc>.</returns>
public List <StrategyDesc> GetTestTimeData(string time, string dbname)
{
List <StrategyDesc> st = new List <StrategyDesc>();
var dayOfMonth = DateTime.Now.Day;
var dayOfWeek = (int)DateTime.Now.DayOfWeek;
var toDate = DateTime.Now.Date;
using (var context = new organisationdatabaseEntities(dbname))
{
try
{
var temp = (from p in context.strategydescriptions
join e in context.strategyequipments
on p.Equipmentid equals e.id
join s in context.strategymanagements on p.StrategyRefId equals s.strategyId
where s.CurrentStatus != 0 && p.strategyTime == time && p.Equipmentid == 1 &&
p.StrategyTimeFrame1 == "TestTime"
select new
{
StrategyDescId = p.id,
StrategyTimeFrame1 = p.StrategyTimeFrame1,
StrategyTimeFrame2 = p.StrategyTimeFrame2,
EquipmentId = e.id,
EquipmentName = e.EquipmentsNames,
ServiceConfig = p.Config ?? "",
StrategyTime = p.strategyTime.ToString(),
Location = s.StrategyLocation,
StrategyId = s.strategyId
}).AsEnumerable().Select(x => new StrategyDesc
{
StrategyDescId = x.StrategyDescId,
StrategyTimeFrame2 = x.StrategyTimeFrame2,
EquipmentId = x.EquipmentId,
EquipmentName = x.EquipmentName,
ServiceConfig = JsonConvert.DeserializeObject(x.ServiceConfig),
StrategyTime = x.StrategyTime,
Location = x.Location,
StrategyId = x.StrategyId
}).ToList();
if (temp.Count > 0)
{
st.AddRange(temp);
}
// var data = context.strategydescriptions.Where(x => x.strategyTime == time );
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
context.Dispose();
}
}
return(st);
}
/// <summary>
/// save desktop event or action as an asynchronous operation.
/// </summary>
/// <param name="deskmac">The deskmac.</param>
/// <param name="action">The action.</param>
/// <returns>A Task<System.Int32> representing the asynchronous operation.
/// No. of updated rows</returns>
public async Task <int> SaveInactiveDesktopAsync(string deskmac, string action)
{
var res = 0;
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
if (c.Name.Contains("Entities"))
{
using (var context = new organisationdatabaseEntities(c.Name))
{
try
{
var id = context.classdetails.Where(x => x.deskmac.ToUpper() == deskmac.ToUpper())
.Select(x => x.classID).FirstOrDefault();
if (id != 0)
{
found = true;
temp_desktopevents tmp = new temp_desktopevents()
{
Action = action,
ActionTime = DateTime.Now,
Deskmac = deskmac,
classid = id
};
context.temp_desktopevents.Add(tmp);
res = await context.SaveChangesAsync();
}
}
catch (Exception ex)
{
loggerFile.Debug(Environment.NewLine + DateTime.Now.ToLongDateString()
+ " " + DateTime.Now.ToLongTimeString() + "exception in recording machine logs: "
+ ex.StackTrace + " error message " + ex.InnerException);
}
}
}
}
else
{
break;
}
}
return(res);
}
/// <summary>
/// Updates the strategy status.
/// </summary>
/// <param name="instruction">The instruction.</param>
/// <param name="machinemac">The machinemac.</param>
/// <param name="stid">The stid.</param>
/// <param name="status">The status.</param>
/// <returns>System.Int32.</returns>
public async Task <int> UpdateStrategyStatus(string instruction, string machinemac, int stid, string status)
{
int r = 0;
var classid = 0;
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
try
{
using (var context = new organisationdatabaseEntities(c.Name))
{
classid = context.classdetails.Where(x => x.ccmac == machinemac).Select(x => x.classID).FirstOrDefault();
if (context.strategylogs.Any(x => x.Instruction == instruction && x.MachineMac == classid &&
x.StrategyDescId == stid))
{
found = true;
var log = context.strategylogs.Where(x => x.Instruction == instruction && x.MachineMac == classid && x.StrategyDescId == stid);
foreach (var l in log)
{
if (DateTime.Now.Subtract(l.ExecutionTime).TotalSeconds < 180)
{
l.Status = status;
break;
}
}
}
r = await context.SaveChangesAsync();
}
}
catch (Exception ex)
{
loggerFile.Debug(Environment.NewLine + DateTime.Now.ToLongDateString() +
" " + DateTime.Now.ToLongTimeString() + "exception in stratrgy logs: " +
ex.InnerException + " error message " + ex.Message + " data: " +
instruction + " " + machinemac + " " + stid + " " + status);
}
}
else
{
break;
}
}
return(r);
}
/// <summary>
/// Saves the power usage information.
/// </summary>
/// <param name="pow">The pow.</param>
/// <param name="mac">The mac.</param>
/// <param name="typ">The typ.</param>
/// <returns>System.Int32.</returns>
public int SavePowerUsageInfo(int pow, string mac, string typ)
{
int r = 0;
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
if (c.Name.Contains("Entities"))
{
try
{
using (var context = new organisationdatabaseEntities(c.Name))
{
var cid = context.classdetails.Where(x => x.ccmac == mac)
.Select(x => x.classID).FirstOrDefault();
if (cid > 0)
{
found = true;
machineusagelogs_minute m = new machineusagelogs_minute()
{
classid = cid,
value = pow,
attribute = typ,
recordtime = DateTime.Now
};
context.machineusagelogs_minute.Add(m);
r = context.SaveChanges();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
else
{
break;
}
}
return(r);
}
/// <summary>
/// Updates the projector configuration.
/// </summary>
/// <param name="mac">The mac.</param>
/// <param name="val">The value.</param>
/// <returns>System.Int32.</returns>
public int UpdateProjectorConfig(string mac, string val)
{
int r = 0; string stat = "";
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
if (c.Name.Contains("Entities"))
{
if (val == "True")
{
stat = "Registered";
}
else
{
stat = "Pending";
}
using (var context = new organisationdatabaseEntities(c.Name))
{
var classid = context.classdetails.Where(x => x.ccmac == mac).Select(x => x.classID).FirstOrDefault();
if (classid != 0)
{
found = true;
var d = context.projectorconfiginfoes.Where(x => x.Classid == classid).Select(x => x).FirstOrDefault();
if (d != null)
{
d.status = stat;
}
}
r = context.SaveChanges();
}
}
}
else
{
break;
}
}
return(r);
}
/// <summary>
/// Saves the strategy log information.
/// </summary>
/// <param name="instruction">The instruction.</param>
/// <param name="stid">The stid.</param>
/// <param name="status">The status.</param>
/// <param name="machinemac">The machinemac.</param>
/// <param name="equipid">The equipid.</param>
public async Task SaveStrategyLogInfo(string instruction, int stid, string status, string machinemac, int equipid)
{
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
try
{
using (var context = new organisationdatabaseEntities(c.Name))
{
var classid = context.classdetails.Where(x => x.ccmac == machinemac).Select(x => x.classID).FirstOrDefault();
if (classid != 0)
{
found = true;
var newLog = new strategylog()
{
StrategyDescId = stid,
MachineMac = classid,
ExecutionTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm")),
Instruction = instruction,
Status = status,
EquipmentId = equipid
};
context.strategylogs.Add(newLog);
await context.SaveChangesAsync();
}
}
}
catch (Exception ex)
{
loggerFile.Debug(Environment.NewLine + DateTime.Now.ToLongDateString()
+ " " + DateTime.Now.ToLongTimeString() + "exception in stratrgy logs: "
+ ex.StackTrace + " error message " + ex.InnerException);
}
}
else
{
break;
}
}
}
/// <summary>
/// Gets the camera detail by machine mac address.
/// </summary>
/// <param name="ccmac">The ccmac.</param>
/// <returns>CamDetails.</returns>
public CamDetails GetDeviceDetailByMachine(string ccmac)
{
var result = new CamDetails();
using (var context = new organisationdatabaseEntities())
{
result = context.classdetails.Where(x => x.ccmac == ccmac).Select(x => new CamDetails
{
ClassId = x.classID,
DeviceIpSt = x.camipS.Trim(),
DeviceIpT = x.camipT.Trim(),
Password = x.campass.Trim(),
UserId = x.camuserid.Trim(),
Port = x.camport,
Ccmac = ccmac.ToUpper()
}).FirstOrDefault();
}
return(result);
}
/// <summary>
/// Gets the power usage information.
/// </summary>
/// <param name="mac">The mac.</param>
/// <param name="typ">The typ.</param>
/// <returns>System.String.</returns>
public string GetPowerUsageInfo(string mac, string typ)
{
int r = 0;
var data = "";
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
if (c.Name.Contains("Entities"))
{
try
{
using (var context = new organisationdatabaseEntities(c.Name))
{
var cid = context.classdetails.Where(x => x.ccmac == mac).OrderBy(x => x.classID)
.Select(x => x.classID).FirstOrDefault();
if (cid > 0)
{
found = true;
data = context.Database.ExecuteSqlCommand("SELECT sum(value) as " +
"value from organisationdatabase.machineusagelogs_minute WHERE " +
"date(recordtime)= date(now()) and attribute = 'Power'" +
" and classid =" + cid).ToString();
r = context.SaveChanges();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
else
{
break;
}
}
return(data);
}
/// <summary>
/// Saves the reader log.
/// </summary>
/// <param name="message">The message.</param>
/// <param name="mac">The mac.</param>
/// <param name="cardid">The cardid.</param>
/// <returns>System.Int32.</returns>
public int SaveReaderLog(string message, string mac, string cardid)
{
int result = 0;
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
if (c.Name.Contains("Entities"))
{
try
{
using (var context = new organisationdatabaseEntities(c.Name))
{
var classid = context.classdetails.Where(x => x.ccmac == mac).Select(x => x.classID).FirstOrDefault();
if (classid != 0)
{
found = true;
cardlog cardlog = new cardlog()
{
Message = message,
ClassId = classid,
cardId = cardid,
ActionTime = DateTime.Now
};
context.cardlogs.Add(cardlog);
result = context.SaveChanges();
}
}
}
catch (Exception)
{
}
}
}
else
{
break;
}
}
return(result);
}
/// <summary>
/// Saves the machine logs.
/// </summary>
/// <param name="type">The type.</param>
/// <param name="data">The data.</param>
/// <param name="machinemac">The machinemac.</param>
public async Task SaveMachineLogs(string type, string data, string machinemac)
{
int r = 0;
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
try
{
using (var context = new organisationdatabaseEntities(c.Name))
{
var classid = context.classdetails.Where(x => x.ccmac == machinemac).Select(x => x.classID).FirstOrDefault();
if (classid != 0)
{
found = true;
var machineop = new machineoperationlog()
{
Operation = data, //JsonSerializer.Serialize(data),
Location = classid,
Type = type,
ExecutionTime = DateTime.Now
};
context.machineoperationlogs.Add(machineop);
}
r = await context.SaveChangesAsync();
}
}
catch (Exception ex)
{
loggerFile.Debug(Environment.NewLine + DateTime.Now.ToLongDateString()
+ " " + DateTime.Now.ToLongTimeString() + "exception in recording machine logs: "
+ ex.StackTrace + " error message " + ex.InnerException);
}
}
else
{
break;
}
}
}
/// <summary>
/// Gets the string by scheduleor section.
/// </summary>
/// <param name="timeFrameType">Type of the time frame.</param>
/// <param name="dbname">The dbname.</param>
/// <returns>List<StrategyDesc>.</returns>
public List <StrategyDesc> GetStrByScheduleorSection(string timeFrameType, string dbname)
{
List <StrategyDesc> st = new List <StrategyDesc>();
using (var context = new organisationdatabaseEntities(dbname))
{
var temp = (from p in context.strategydescriptions
join e in context.strategyequipments
on p.Equipmentid equals e.id
join s in context.strategymanagements on p.StrategyRefId equals s.strategyId
where s.CurrentStatus != 0 && p.StrategyTimeFrame1 == timeFrameType
select new
{
StrategyDescId = p.id,
StrategyTimeFrame1 = p.StrategyTimeFrame1,
StrategyTimeFrame2 = p.StrategyTimeFrame2,
EquipmentId = e.id,
EquipmentName = e.EquipmentsNames,
ServiceConfig = p.Config ?? "",
StrategyTime = p.strategyTime.ToString(),
Location = s.StrategyLocation,
StrategyId = s.strategyId
}).AsEnumerable().Select(x => new StrategyDesc
{
StrategyDescId = x.StrategyDescId,
StrategyTimeFrame1 = x.StrategyTimeFrame1,
StrategyTimeFrame2 = x.StrategyTimeFrame2,
EquipmentId = x.EquipmentId,
EquipmentName = x.EquipmentName,
ServiceConfig = System.Text.Json.JsonSerializer.Deserialize <Dictionary <string, object> >(x.ServiceConfig),
StrategyTime = x.StrategyTime,
Location = x.Location,
StrategyId = x.StrategyId
}).ToList();
if (temp.Count > 0)
{
st.AddRange(temp);
}
}
return(st);
}
/// <summary>
/// Gets the mac.
/// </summary>
/// <param name="mac">The array of desktop mac address .</param>
/// <returns>desktop mac and related central control machine mac(ccmac)</returns>
public KeyValuePair <string, string> GetMac(string[] mac)
{
KeyValuePair <string, string> result = new KeyValuePair <string, string>();
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
if (c.Name.Contains("Entities"))
{
using (var context = new organisationdatabaseEntities(c.Name))
{
try
{
foreach (string m in mac)
{
var temp = m.Replace(':', ' ').ToUpper();
var d = context.classdetails.Where(x => x.deskmac.ToUpper() == temp).Select(y => y.ccmac).SingleOrDefault();
if (d != null)
{
found = true;
result = new KeyValuePair <string, string>(temp, d.ToUpper());
break;
}
}
}
finally
{
context.Dispose();
}
}
}
}
else
{
break;
}
}
return(result);
}
/// <summary>
/// Updates the status of card reg.
/// </summary>
/// <param name="stat">The stat.</param>
/// <param name="mac">The mac.</param>
/// <param name="cid">The cid.</param>
/// <returns>System.Int32.</returns>
public int UpdateStatCardReg(string stat, string mac, string cid)
{
int result = 0;
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
if (c.Name.Contains("Entities"))
{
using (var context = new organisationdatabaseEntities(c.Name))
{
var classid = context.classdetails.Where(x => x.ccmac == mac).Select(x => x.classID).FirstOrDefault();
if (context.card_registration.Any(x => x.calssId == classid && x.OneCardId == cid))
{
var row = context.card_registration.Where(x => x.calssId == classid && x.OneCardId == cid)
.Select(x => x).FirstOrDefault();
if (row != null)
{
found = true;
Console.WriteLine("status: " + row.Status);
row.Status = stat;
row.UpdateTime = DateTime.Now;
}
result = context.SaveChanges();
}
Console.WriteLine("UpdatedRows:" + result);
}
}
}
else
{
break;
}
}
return(result);
}
/// <summary>
/// Updates the machine status.
/// this is used to save the status of machine On/Off
/// the table temp_machinestatus will be used in future purposes
/// </summary>
/// <param name="machinemac">The machinemac.</param>
/// <param name="status">The status.</param>
/// <returns>System.Int32.</returns>
public async Task <int> UpdateMachineStatus(string machinemac, string status)
{
int r = 0;
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
using (var context = new organisationdatabaseEntities(c.Name))
{
var cid = context.classdetails.Where(x => x.ccmac == machinemac).Select(x => x.classID).FirstOrDefault();
if (cid != 0)
{
found = true;
if (context.temp_machinestatus.Any(x => x.classid == cid))
{
var row = context.temp_machinestatus.Where(x => x.classid == cid).FirstOrDefault();
row.machineStatus = status;
}
else
{
var newrow = new temp_machinestatus {
classid = cid, machineStatus = status
};
context.temp_machinestatus.Add(newrow);
}
r = await context.SaveChangesAsync();
}
}
}
else
{
break;
}
}
return(r);
}
/// <summary>
/// Gets the class identifier.
/// </summary>
/// <param name="mac">The ccmac.</param>
/// <returns>System.Int32. classid</returns>
public int GetClassID(string mac)
{
int id = 0;
var found = false;
foreach (ConnectionStringSettings c in ConfigurationManager.ConnectionStrings)
{
if (!found)
{
if (c.Name.Contains("Entities"))
{
using (var context = new organisationdatabaseEntities(c.Name))
{
try
{
id = context.classdetails.Where(x => x.ccmac.ToUpper() == mac.ToUpper())
.Select(x => x.classID).FirstOrDefault();
if (id > 0)
{
found = true;
}
}
catch (Exception ex)
{
loggerFile.Debug(Environment.NewLine + DateTime.Now.ToLongDateString()
+ " " + DateTime.Now.ToLongTimeString() + "exception in getting classid from machine mac : "
+ ex.StackTrace + " error message " + ex.InnerException);
}
}
}
}
else
{
break;
}
}
return(id);
}
public void SaveStrategyLogInfo(string instruction, int stid, int descid, string status, string machinemac)
{
try
{
using (var context = new organisationdatabaseEntities())
{
var newLog = new strategylog()
{
StrategyDescId = descid,
StrategyId = stid,
MachineMac = machinemac,
ExecutionTime = DateTime.Now,
Instruction = instruction,
Status = status
};
context.strategylogs.Add(newLog);
}
}
catch (Exception ex)
{
File.WriteAllText(docPath, "exception in stratrgy logs: " + ex.StackTrace);
}
}
/// <summary>
/// Gets the data.
/// </summary>
/// <param name="time">The time.</param>
/// <param name="dbname">The dbname.</param>
/// <returns>List<StrategyDesc>.</returns>
public List <StrategyDesc> GetData(string time, string dbname)
{
List <StrategyDesc> st = new List <StrategyDesc>();
var dayOfMonth = DateTime.Now.Day;
var dayOfWeek = (int)DateTime.Now.DayOfWeek;
var toDate = DateTime.Now.Date;
try
{
using (var context = new organisationdatabaseEntities(dbname))
{
try
{
var temp = (from p in context.strategydescriptions
join e in context.strategyequipments
on p.Equipmentid equals e.id
join s in context.strategymanagements on p.StrategyRefId equals s.strategyId
where s.CurrentStatus != 0 && p.strategyTime == time
select new
{
StrategyDescId = p.id,
StrategyTimeFrame1 = p.StrategyTimeFrame1,
StrategyTimeFrame2 = p.StrategyTimeFrame2,
EquipmentId = e.id,
EquipmentName = e.EquipmentsNames,
ServiceConfig = p.Config ?? "",
StrategyTime = p.strategyTime.ToString(),
Location = s.StrategyLocation,
StrategyId = s.strategyId
}).AsEnumerable().Select(x => new StrategyDesc
{
StrategyDescId = x.StrategyDescId,
StrategyTimeFrame1 = x.StrategyTimeFrame1,
StrategyTimeFrame2 = x.StrategyTimeFrame2,
EquipmentId = x.EquipmentId,
EquipmentName = x.EquipmentName,
ServiceConfig = System.Text.Json.JsonSerializer.Deserialize <Dictionary <string, dynamic> >(x.ServiceConfig),
StrategyTime = x.StrategyTime,
Location = x.Location,
StrategyId = x.StrategyId
}).ToList();
if (temp.Count > 0)
{
st.AddRange(temp);
}
// var data = context.strategydescriptions.Where(x => x.strategyTime == time );
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
context.Dispose();
}
}
}
catch (Exception ex)
{
loggerFile.Debug(Environment.NewLine + "error in method GetData for strategy: " + ex.Message);
}
return(st);
}
