public SoftwareVersion GetSoftwareVersionByNameOrInsert(string _name, int _terminalTypeId)
{
try
{
using (var context = new VeraEntities())
{
var SoftwareVersion = context.SoftwareVersion.FirstOrDefault(x => x.VersionCode.Trim() == _name.Trim() && x.Status > 0 && x.TerminalTypeId == _terminalTypeId);
if (SoftwareVersion == null)
{
SoftwareVersion = new SoftwareVersion()
{
VersionCode = _name.Trim(),
Status = 1,
TerminalTypeId = _terminalTypeId
};
context.SoftwareVersion.Add(SoftwareVersion);
context.SaveChanges();
return(SoftwareVersion);
}
return(SoftwareVersion);
}
}
catch (Exception exc)
{
throw exc;
}
}
public Sector GetSectorByNameOrInsert(string _name)
{
try
{
using (var context = new VeraEntities())
{
var Sector = context.Sector.FirstOrDefault(x => x.Name.Trim() == _name.Trim() && x.Status > 0);
if (Sector == null)
{
Sector = new Sector()
{
Name = _name.Trim(),
Status = 1
};
context.Sector.Add(Sector);
context.SaveChanges();
return(Sector);
}
return(Sector);
}
}
catch (Exception exc)
{
throw exc;
}
}
public WocheckerFlag GetWocheckerFlagByNameOrInsert(string _name)
{
try
{
using (var context = new VeraEntities())
{
var WocheckerFlag = context.WocheckerFlag.FirstOrDefault(x => x.Name.Trim() == _name.Trim() && x.Status > 0);
if (WocheckerFlag == null)
{
WocheckerFlag = new WocheckerFlag()
{
Name = _name.Trim(),
Status = 1
};
context.WocheckerFlag.Add(WocheckerFlag);
context.SaveChanges();
return(WocheckerFlag);
}
return(WocheckerFlag);
}
}
catch (Exception exc)
{
throw exc;
}
}
public DeviceMontageType GetDeviceMontageTypeByNameOrInsert(string _name)
{
try
{
using (var context = new VeraEntities())
{
var DeviceMontageType = context.DeviceMontageType.FirstOrDefault(x => x.Name.Trim() == _name.Trim() && x.Status > 0);
if (DeviceMontageType == null)
{
DeviceMontageType = new DeviceMontageType()
{
Name = _name.Trim(),
Status = 1
};
context.DeviceMontageType.Add(DeviceMontageType);
context.SaveChanges();
return(DeviceMontageType);
}
return(DeviceMontageType);
}
}
catch (Exception exc)
{
throw exc;
}
}
public List <Report> GetAllUserReportByUserIdToLeft(User _userObj)
{
try
{
using (var context = new VeraEntities())
{
var allReports = ReportDB.GetInstance().GetAllReportes();
var userCompany = CompanyUserDB.GetInstance().GetCompanyUserById(_userObj.Id);
if (userCompany.IsCompanyAdmin)
{
return(allReports);
}
else
{
var myCompanyId = context.CompanyUser.FirstOrDefault(x => x.UserId == _userObj.Id).CompanyId;
var myDevices = allReports.Where(x => x.Id == myCompanyId).ToList();
return(myDevices);
}
}
}
catch (Exception)
{
throw;
}
}
public RefTelemetryType GetRefTelemetryTypeByNameOrInsert(string _name)
{
try
{
using (var context = new VeraEntities())
{
var RefTelemetryType = context.RefTelemetryType.FirstOrDefault(x => x.Name.Trim() == _name.Trim() && x.Status > 0);
if (RefTelemetryType == null)
{
RefTelemetryType = new RefTelemetryType()
{
Name = _name.Trim(),
Status = 1
};
context.RefTelemetryType.Add(RefTelemetryType);
context.SaveChanges();
return(RefTelemetryType);
}
return(RefTelemetryType);
}
}
catch (Exception exc)
{
throw exc;
}
}
public JsonResult GetDevicesToShow()
{
var today = DateTime.Now;
var startDate = new DateTime(today.Year, today.Month, today.Day, 0, 0, 0);
var endDate = new DateTime(today.Year, today.Month, today.Day, 23, 59, 59);
using (var context = new VeraEntities())
{
var list = new List <DeviceInfoForMap>();
var deviceIds = context.DeviceData.Select(x => x.DeviceId).Distinct().ToList();
foreach (var item in deviceIds)
{
var device = context.Device.FirstOrDefault(x => x.Id == item);
var lastDeviceData = context.DeviceData.FirstOrDefault(x => x.Id == device.LastDeviceDataId);
list.Add(new DeviceInfoForMap()
{
lat = lastDeviceData.Latitude,
lng = lastDeviceData.Longtitude,
CarPlateNumber = device.CarPlateNumber,
Altitude = lastDeviceData.Altitude,
Date = Convert.ToString(lastDeviceData.CreateDate),
IoStatus = lastDeviceData.IoStatus.EndsWith("0") ? "Kapalı" : "Açık",
TotaLkm = GetTotalKMBetweenTwoDateOfADevice(item, startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss")),
KmPerHour = lastDeviceData.KmPerHour,
Location = DeviceDB.GetInstance().GetLocationFromLatLon2(Convert.ToString(lastDeviceData.Latitude), Convert.ToString(lastDeviceData.Longtitude)),
DirectionDegree = Convert.ToInt16(lastDeviceData.DirectionDegree),
DeviceId = item
});
//list.Add(lastDeviceData.Latitude + "--" + lastDeviceData.Longtitude);
}
return(Json(list));
}
}
public Messaging GetMessagingByNameOrInsert(string _name)
{
try
{
using (var context = new VeraEntities())
{
var Messaging = context.Messaging.FirstOrDefault(x => x.Name.Trim() == _name.Trim() && x.Status > 0);
if (Messaging == null)
{
Messaging = new Messaging()
{
Name = _name.Trim(),
Status = 1
};
context.Messaging.Add(Messaging);
context.SaveChanges();
return(Messaging);
}
return(Messaging);
}
}
catch (Exception exc)
{
throw exc;
}
}
public List <Group> GetGroupsByGroupName(User _userObj, string _groupName)
{
try
{
using (var context = new VeraEntities())
{
var groupName = _groupName.Trim().ToLower();
var companyId = context.CompanyUser.FirstOrDefault(x => x.UserId == _userObj.Id).CompanyId;
var companyGroups = context.CompanyGroup.Where(x => x.CompanyId == companyId).ToList();
var companyGroupsGroupIds = companyGroups.Select(x => x.GroupId).ToList();
var groups = context.Group.Where(x => companyGroupsGroupIds.Contains(x.Id)).ToList();
groups = groups.Where(x => x.Name != null && x.Name.Trim().ToLower().Contains(groupName) && x.Status == 1).ToList();
if (groups != null)
{
return(groups);
}
return(null);
}
}
catch (Exception exc)
{
throw exc;
}
}
public List <Area> GetAreaListOfCompany(string _areaNameTLA, int _areaTypeTLA, User _userObj)
{
try
{
using (var context = new VeraEntities())
{
var company = CompanyUserDB.GetInstance().GetCompanyByCompanyUserId(_userObj);
var companyAreas = context.CompanyArea.Where(x => x.CompanyId == company.Id).ToList();
var companyAreasAreaIds = companyAreas.Select(x => x.AreaId).ToList();
var areas = context.Area.Where(x => companyAreasAreaIds.Contains(x.Id) && x.Status == 1).ToList();
if (!String.IsNullOrEmpty(_areaNameTLA))
{
areas = areas.Where(x => x.Name.Trim().ToLower().Contains(_areaNameTLA.Trim().ToLower())).ToList();
}
if (_areaTypeTLA != 0)
{
var areaIsRestricted = _areaTypeTLA == 1 ? true : false;
areas = areas.Where(x => x.IsRestricted == areaIsRestricted).ToList();
}
if (areas != null)
{
return(areas);
}
return(null);
}
}
catch (Exception exc)
{
throw exc;
}
}
public List <Area> GetAreaList(User _userObj)
{
try
{
using (var context = new VeraEntities())
{
if (_userObj.Id == 774)
{
return(context.Area.Where(x => x.Status == 1).ToList());
}
var company = context.CompanyUser.FirstOrDefault(x => x.UserId == _userObj.Id);
var companyGroupsGroupIds = context.CompanyGroup.Where(x => x.CompanyId == company.Id).Select(x => x.GroupId).ToList();
var areas = context.Area.Where(x => companyGroupsGroupIds.Contains(x.Id) && x.Status == 1).ToList();
if (areas != null)
{
return(areas);
}
return(null);
}
}
catch (Exception exc)
{
throw exc;
}
}
public UserRepo GetUserByIdUserRepo(int _id)
{
try
{
using (var context = new VeraEntities())
{
var user = context.User.FirstOrDefault(x => x.Id == _id);
var companyUser = context.CompanyUser.FirstOrDefault(x => x.UserId == user.Id);
var company = context.Company.FirstOrDefault(x => x.Id == companyUser.CompanyId);
UserRepo userRepo = new UserRepo()
{
CompanyName = company.CompanyDescription,
Id = user.Id,
AuthorizationTypeId = companyUser.IsCompanyAdmin == true?1:0,
Mail = user.Mail,
Name = user.Name,
GeographicalAuthorityId = user.GeographicalAuthorityId,
Password = user.Password,
Status = user.Status,
Surname = user.Surname,
Telephone = user.Telephone,
UserCode = user.UserCode,
UserTypeId = user.UserTypeId
};
return(userRepo);
}
}
catch (Exception)
{
throw;
}
}
public CompanyPartner GetCompanyPartnerByNameOrInsert(string _name)
{
try
{
using (var context = new VeraEntities())
{
var CompanyPartner = context.CompanyPartner.FirstOrDefault(x => x.Name.Trim() == _name.Trim() && x.Status > 0);
if (CompanyPartner == null)
{
CompanyPartner = new CompanyPartner()
{
Name = _name.Trim(),
Status = 1
};
context.CompanyPartner.Add(CompanyPartner);
context.SaveChanges();
return(CompanyPartner);
}
return(CompanyPartner);
}
}
catch (Exception exc)
{
throw exc;
}
}
public UserRepo GetUserByUserObj(User _userObj)
{
try
{
using (var context = new VeraEntities())
{
var myCompanyId = context.CompanyUser.FirstOrDefault(x => x.UserId == _userObj.Id).CompanyId;
var myCompany = context.Company.FirstOrDefault(x => x.Id == myCompanyId);
var userType = context.UserType.FirstOrDefault(x => x.Id == _userObj.UserTypeId);
var userRepo = new UserRepo()
{
Id = _userObj.Id,
Authorization = userType.Name,
Name = String.IsNullOrEmpty(_userObj.Name) ? "" : _userObj.Name,
UserCode = _userObj.UserCode,
UserTypeId = _userObj.UserTypeId,
CompanyName = myCompany.CompanyDescription,
CompanyId = myCompany.Id,
Mail = String.IsNullOrEmpty(_userObj.Mail) ? "" : _userObj.Mail,
Password = _userObj.Password,
Status = _userObj.Status,
Surname = String.IsNullOrEmpty(_userObj.Surname) ? "" : _userObj.Surname,
Telephone = String.IsNullOrEmpty(_userObj.Telephone) ? "" : _userObj.Telephone,
HomepageRefreshTime = _userObj.HomepageRefreshTime
};
return(userRepo);
}
}
catch (Exception)
{
throw;
}
}
public List <Route> GetRouteListOfCompany(string _routeNameTLA, User _userObj)
{
try
{
using (var context = new VeraEntities())
{
var company = CompanyUserDB.GetInstance().GetCompanyByCompanyUserId(_userObj);
var companyRoutes = context.CompanyRoute.Where(x => x.CompanyId == company.Id).ToList();
var companyRoutesRouteIds = companyRoutes.Select(x => x.RouteId).ToList();
var routes = context.Route.Where(x => companyRoutesRouteIds.Contains(x.Id) && x.Status == 1).ToList();
if (!String.IsNullOrEmpty(_routeNameTLA))
{
routes = routes.Where(x => x.Name.Trim().ToLower().StartsWith(_routeNameTLA.Trim().ToLower()) && x.Status > 0).ToList();
}
if (routes != null)
{
return(routes);
}
return(null);
}
}
catch (Exception exc)
{
throw exc;
}
}
public List <UserReportInfo> GetUserReportByUser(User _userObj)
{
try
{
using (var context = new VeraEntities())
{
var userCompany = CompanyUserDB.GetInstance().GetCompanyUserById(_userObj.Id);
var userReports = context.UserReport.Where(x => x.UserId == _userObj.Id).ToList();
var userReportsIds = userReports.Select(x => x.ReportId).ToList();
var reports = context.Report.ToList();
var userReportList = new List <UserReportInfo>();
foreach (var item in userReports)
{
var report = reports.FirstOrDefault(x => x.Id == item.ReportId);
userReportList.Add(new UserReportInfo()
{
UserReportId = item.Id,
ReportId = report.Id,
Name = report.Name,
ReportStatus = item.AutomaticReportingStatus == 1 ? "Aktif":"Pasif",
ReportType = item.UserReportType == 2 ? "PDF":"EXCEL"
});
}
return(userReportList);
}
}
catch (Exception exc)
{
throw exc;
}
}
public OdometreStatus GetOdometreStatusByNameOrInsert(string _name)
{
try
{
using (var context = new VeraEntities())
{
var OdometreStatus = context.OdometreStatus.FirstOrDefault(x => x.Name.Trim() == _name.Trim() && x.Status > 0);
if (OdometreStatus == null)
{
OdometreStatus = new OdometreStatus()
{
Name = _name.Trim(),
Status = 1
};
context.OdometreStatus.Add(OdometreStatus);
context.SaveChanges();
return(OdometreStatus);
}
return(OdometreStatus);
}
}
catch (Exception exc)
{
throw exc;
}
}
public TerminalProtocol GetTerminalProtocolByNameOrInsert(string _name)
{
try
{
using (var context = new VeraEntities())
{
var TerminalProtocol = context.TerminalProtocol.FirstOrDefault(x => x.Name.Trim() == _name.Trim() && x.Status > 0);
if (TerminalProtocol == null)
{
TerminalProtocol = new TerminalProtocol()
{
Name = _name.Trim(),
Status = 1
};
context.TerminalProtocol.Add(TerminalProtocol);
context.SaveChanges();
return(TerminalProtocol);
}
return(TerminalProtocol);
}
}
catch (Exception exc)
{
throw exc;
}
}
public Gateway GetGatewayByNameOrInsert(string _name)
{
try
{
using (var context = new VeraEntities())
{
var Gateway = context.Gateway.FirstOrDefault(x => x.Name.Trim() == _name.Trim() && x.Status > 0);
if (Gateway == null)
{
Gateway = new Gateway()
{
Name = _name.Trim(),
Status = 1
};
context.Gateway.Add(Gateway);
context.SaveChanges();
return(Gateway);
}
return(Gateway);
}
}
catch (Exception exc)
{
throw exc;
}
}
public JsonResult GetSingleDeviceDetail(int _deviceId)
{
using (var context = new VeraEntities())
{
var list = new List <DeviceInfoForMap>();
var deviceIds = new int[] { _deviceId };
foreach (var item in deviceIds)
{
var device = context.Device.FirstOrDefault(x => x.Id == item);
var lastDeviceData = context.DeviceData.FirstOrDefault(x => x.Id == device.LastDeviceDataId);
list.Add(new DeviceInfoForMap()
{
lat = lastDeviceData.Latitude,
lng = lastDeviceData.Longtitude,
CarPlateNumber = device.CarPlateNumber,
Altitude = lastDeviceData.Altitude,
Date = Convert.ToString(lastDeviceData.CreateDate),
IoStatus = lastDeviceData.IoStatus.EndsWith("0") ? "Kapalı" : "Açık",
TotaLkm = lastDeviceData.TotalKm,
KmPerHour = lastDeviceData.KmPerHour,
Location = DeviceDB.GetInstance().GetLocationFromLatLon2(Convert.ToString(lastDeviceData.Latitude), Convert.ToString(lastDeviceData.Longtitude)),
DirectionDegree = Convert.ToInt16(lastDeviceData.DirectionDegree),
DeviceId = item
});
//list.Add(lastDeviceData.Latitude + "--" + lastDeviceData.Longtitude);
}
return(Json(list));
}
}
public List <Group> GetAllCompanyGroupsByUserId(User _userObj)
{
try
{
using (var context = new VeraEntities())
{
if (_userObj.Id == 774)
{
return(context.Group.Where(x => x.Status == 1).ToList());
}
var myCompanyId = context.CompanyUser.FirstOrDefault(x => x.UserId == _userObj.Id).CompanyId;
var myCompanyGroups = context.CompanyGroup.Where(x => x.CompanyId == myCompanyId).ToList();
var myCompanyGroupsIds = myCompanyGroups.Select(x => x.Id).ToList();
var myGroups = context.Group.Where(x => myCompanyGroupsIds.Contains(x.Id) && x.Status > 0).ToList();
if (myGroups != null)
{
return(myGroups);
}
return(null);
}
}
catch (Exception)
{
throw;
}
}
public Platform GetPlatformByNameOrInsert(string _name)
{
try
{
using (var context = new VeraEntities())
{
var Platform = context.Platform.FirstOrDefault(x => x.Name.Trim() == _name.Trim() && x.Status > 0);
if (Platform == null && _name != null)
{
Platform = new Platform()
{
Name = _name.Trim(),
Status = 1
};
context.Platform.Add(Platform);
context.SaveChanges();
return(Platform);
}
return(Platform);
}
}
catch (Exception exc)
{
throw exc;
}
}
public CompanyRepo GetCompanyByIdForDisplaying(int _id)
{
try
{
using (var context = new VeraEntities())
{
var company = context.Company.FirstOrDefault(a => a.Id == _id && a.Status == 1);
if (company != null)
{
var companyUser = context.CompanyUser.FirstOrDefault(b => b.CompanyId == company.Id);
var user = context.User.FirstOrDefault(x => x.Id == companyUser.UserId && x.Status == 1);
var returnObj = new CompanyRepo()
{
Id = company.Id,
MainCompanyId = company.MainCompanyId,
CompanyTypeId = company.CompanyTypeId,
CompanyPartnerId = company.CompanyPartnerId,
PlatformId = company.PlatformId,
DistributorId = company.DistributorId,
SectorId = company.SectorId,
Address1 = company.Address1,
CompanyReportId = company.CompanyReportId,
CompanyCode = company.CompanyCode,
CompanyDescription = company.CompanyDescription,
Phone = company.Phone,
TechnicalReport = company.TechnicalReport,
TechnicalEmail1 = company.TechnicalEmail1,
TechnicalEmail2 = company.TechnicalEmail2,
TechnicalEmail3 = company.TechnicalEmail3,
CompanyStatus = company.CompanyStatus,
AlarmSms = company.AlarmSms,
PasswordControl = company.PasswordControl,
CompanyVehicleProgramming = company.CompanyVehicleProgramming,
CompanyGroupName = company.CompanyGroupName,
CreationDate = company.CreationDate,
UserId = user.Id,
UserCode = user.UserCode,
UserEmail = user.Mail,
UserName = user.Name,
UserPassword = user.Password,
UserSurname = user.Surname,
UserTelephone = user.Telephone,
UserTypeId = user.UserTypeId,
UserStatus = company.UserStatus,
AccountingCode = company.AccountingCode
};
return(returnObj);
}
return(null);
}
}
catch (Exception)
{
throw;
}
}
public JsonResult GetGecmisKonum(int _deviceId, string _startDate = "", string _endDate = "")
{
_startDate += " 00:00:00";
_endDate += " 23:59:59";
using (var context = new VeraEntities())
{
using (var con = context.Database.GetDbConnection())
{
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
using (var cmd = con.CreateCommand())
{
string query = $@"SELECT *, (SELECT Location FROM LatLonLocation WHERE Latitude = DD.Latitude AND Longtitude = DD.Longtitude) AS LocationString FROM DeviceData AS DD
WHERE CreateDate >= '{_startDate}'
AND CreateDate <= '{_endDate}'
AND DeviceId = {_deviceId}
ORDER BY Id
FOR JSON AUTO";
cmd.CommandText = query;
var reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append(reader.GetString(0));
}
var json = sb.ToString();
var dt = JsonConvert.DeserializeObject <DataTable>(json);
return(Json(dt));
}
}
catch (Exception exc)
{
throw exc;
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
}
}
}
}
public UserReportIsAssigned GetUsersAssignedReports(User userObj)
{
try
{
using (var context = new VeraEntities())
{
var userReports = context.UserReport.Where(x => x.UserId == userObj.Id).Select(x => x.ReportId).ToList();
var reports = context.Report.Where(x => userReports.Contains(x.Id)).ToList();
var userReportIsAssigned = new UserReportIsAssigned();
foreach (var item in reports)
{
if (item.Name == "SeferOzetiRaporu")
{
userReportIsAssigned.SeferOzetiRaporu = true;
}
if (item.Name == "SeferOlayRaporu")
{
userReportIsAssigned.SeferOlayRaporu = true;
}
if (item.Name == "GecmisKonumRaporu")
{
userReportIsAssigned.GecmisKonumRaporu = true;
}
if (item.Name == "SonKonumRaporu")
{
userReportIsAssigned.SonKonumRaporu = true;
}
if (item.Name == "AracGiseRaporu")
{
userReportIsAssigned.AracGiseRaporu = true;
}
if (item.Name == "BolgeGirisCikisRaporu")
{
userReportIsAssigned.BolgeGirisCikisRaporu = true;
}
if (item.Name == "MesafeOzetRaporu")
{
userReportIsAssigned.MesafeOzetRaporu = true;
}
if (item.Name == "KontakDurumRaporu")
{
userReportIsAssigned.KontakDurumRaporu = true;
}
}
return(userReportIsAssigned);
}
}
catch (Exception exc)
{
throw exc;
}
}
public Route GetRouteById(int _RouteId)
{
try
{
using (var context = new VeraEntities())
{
var Route = context.Route.FirstOrDefault(a => a.Id == _RouteId && a.Status == 1);
return(Route != null ? Route : null);
}
}
catch (Exception)
{
throw;
}
}
public List <Route> GetAllRoutees()
{
try
{
using (var context = new VeraEntities())
{
var RouteList = context.Route.ToList();
return(RouteList);
}
}
catch (Exception)
{
throw;
}
}
public RefTelemetryType GetRefTelemetryTypeById(int _RefTelemetryTypeId)
{
try
{
using (var context = new VeraEntities())
{
var RefTelemetryType = context.RefTelemetryType.FirstOrDefault(a => a.Id == _RefTelemetryTypeId && a.Status == 1);
return(RefTelemetryType != null ? RefTelemetryType : null);
}
}
catch (Exception)
{
throw;
}
}
public List <RefTelemetryType> GetAllRefTelemetryTypes()
{
try
{
using (var context = new VeraEntities())
{
var RefTelemetryTypeList = context.RefTelemetryType.ToList();
return(RefTelemetryTypeList);
}
}
catch (Exception)
{
throw;
}
}
public List <UserType> GetAllUserTypes()
{
try
{
using (var context = new VeraEntities())
{
var UserTypeList = context.UserType.ToList();
return(UserTypeList);
}
}
catch (Exception)
{
throw;
}
}
