public IActionResult GecmisKonumRaporu(int _deviceId, string _startDate, string _endDate)
{
try
{
_startDate += " 00:00:00";
_endDate += " 23:59:59";
var result = ReportDB.GetInstance().GecmisKonumRaporu(_startDate, _endDate, new string[] { _deviceId.ToString() }).FirstOrDefault().Value;
var dt = JsonConvert.DeserializeObject <DataTable>(JsonConvert.SerializeObject(result));
dt.Columns.Remove("Grup");
dt.Columns.Remove("Arac");
dt.Columns.Remove("Enlem");
dt.Columns.Remove("Boylam");
foreach (DataRow dr in dt.Rows)
{
dr["Tarih"] = Convert.ToDateTime(dr["Tarih"].ToString()).ToString("dd.MM.yyyy HH:mm");
}
dt = dt.DefaultView.ToTable(true);
result = JsonConvert.DeserializeObject <List <GecmisKonumRaporuObjectRepo> >(JsonConvert.SerializeObject(dt));
ViewBag.ReportStartDate = DateTime.ParseExact(_startDate, "yyyy-MM-dd HH:mm:ss", null).ToString("dd.MM.yyyy HH:mm");
ViewBag.ReportEndDate = DateTime.ParseExact(_endDate, "yyyy-MM-dd HH:mm:ss", null).ToString("dd.MM.yyyy HH:mm");
ViewBag.PlateNumber = DeviceDB.GetInstance().GetDeviceById(_deviceId).CarPlateNumber;
return(PartialView("_GecmisKonumRaporuResult", result));
}
catch (Exception exc)
{
throw exc;
}
}
public TerminalDevice(DeviceDB device)
{
ID = device.DID;
IPAddress = device.IPAddress;
Port = device.Port;
AccKey = device.Key;
}
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 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 IActionResult Index()
{
var model = new SmsViewModel()
{
UserList = UserDB.GetInstance().GetAllUsersWhichHasTelephone(userObj),
DeviceList = DeviceDB.GetInstance().GetAllDevicesPhoneNumber(userObj)
};
return(View(model));
}
public JsonResult GetSoftwareVersionsByTerminalTypeId(int _terminalTypeId)
{
try
{
var list = DeviceDB.GetInstance().GetSoftwareVersionsByTerminalTypeId(_terminalTypeId);
return(Json(list));
}
catch (Exception exc)
{
throw exc;
}
}
public JsonResult GetDeviceById(int _id)
{
try
{
var device = DeviceDB.GetInstance().GetDeviceById(_id);
return(Json(device));
}
catch (Exception)
{
throw;
}
}
public JsonResult DeleteDevice(int _id)
{
try
{
DeviceDB.GetInstance().DeleteDevice(_id);
return(Json(true));
}
catch (Exception)
{
throw;
}
return(Json(false));
}
public JsonResult GetDeviceByIdToModal(int _id)
{
try
{
var device = DeviceDB.GetInstance().GetDeviceByIdToModal(_id);
if (device != null)
{
return(Json(device));
}
return(Json(false));
}
catch (Exception exc)
{
throw exc;
}
}
public IActionResult GetDeviceByCarPlateNumber(string _deviceCarPlateToSearch)
{
try
{
var deviceList = DeviceDB.GetInstance().GetAllDevicesPhoneNumber(userObj, _deviceCarPlateToSearch);
if (deviceList != null && deviceList.Count > 0)
{
return(Json(deviceList));
}
return(Json(false));
}
catch (Exception exc)
{
throw exc;
}
}
public IActionResult ExportFile()
{
var companyList = DeviceDB.GetInstance().GetAllDBDevicesForExcel();
var stream = new MemoryStream();
using (var package = new ExcelPackage(stream))
{
var sheet = package.Workbook.Worksheets.Add("Cihazlar");
sheet.Cells.LoadFromCollection(companyList, true);
package.Save();
}
stream.Position = 0;
var fileName = $"Cihazlar_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName));
}
public IActionResult GetAllDevices()
{
try
{
var deviceList = DeviceDB.GetInstance().GetAllDevicesPhoneNumber(userObj);
if (deviceList != null && deviceList.Count > 0)
{
return(Json(deviceList));
}
return(Json(false));
}
catch (Exception exc)
{
throw exc;
}
}
private void LoadItemsCombobox()
{
// Load ContainerIDs //
List <Container> containerList = ContainerDB.GetContainers(connectionstring);
foreach (Container container in containerList)
{
ComboAddContainerID.Items.Add(
container.ContainerID.ToString().PadRight(2) +
container.Plaats);
}
// Load DeviceIDs //
List <Device> deviceList = DeviceDB.GetDevice(connectionstring);
foreach (Device device in deviceList)
{
ComboAddDeviceID.Items.Add(
device.DeviceID.ToString().PadRight(2) +
device.Van +
device.Tot);
}
// Load EventIDs //
List <Events> eventsList = EventsDB.GetEvents(connectionstring);
foreach (Events events in eventsList)
{
ComboAddEventID.Items.Add(
events.EventID.ToString().PadRight(2) +
events.Naam.PadRight(10) +
events.Locatie);
}
// Load VerantwoorkelijkeIDs //
List <Persoon> persoonList = PersoonDB.GetPeople(connectionstring);
foreach (Persoon persoon in persoonList)
{
ComboAddVerantwoordelijkeID.Items.Add(
persoon.PersoonID.ToString().PadRight(2) +
persoon.Voornaam.PadRight(10) +
persoon.Achternaam);
}
}
public JsonResult SaveInfo(int _id, string _montageDate, string _montagePerson, string _technicalNote)
{
try
{
var deviceToUpdate = DeviceDB.GetInstance().GetDeviceById(_id);
deviceToUpdate.MontagePerson = _montagePerson;
deviceToUpdate.MontageDate = String.IsNullOrEmpty(_montageDate) ? null : (DateTime?)DateTime.ParseExact(_montageDate, "yyyy-MM-dd", CultureInfo.InvariantCulture);
deviceToUpdate.TechnicalNote = _technicalNote;
DeviceDB.GetInstance().UpdateDevice(deviceToUpdate);
return(Json(true));
}
catch (Exception)
{
throw;
}
return(Json(false));
}
public IActionResult SetKmOfDevice(int _deviceId, string _kmToSet)
{
try
{
var device = DeviceDB.GetInstance().GetDeviceById(_deviceId);
if (device.DeviceGsmNumber == null || device.DeviceGsmNumber == "")
{
return(Json(false));
}
//set km of device
return(Json(true));
}
catch (Exception exc)
{
throw exc;
}
}
public List <Device> GetAllUserDevicesByUserIdToRight(User _userObj)
{
try
{
using (var context = new VeraEntities())
{
var allDevices = DeviceDB.GetInstance().GetAllDBDevices();
var UserDevices = context.UserDevice.Where(x => x.UserId == _userObj.Id).ToList();
var UserDevicesIds = UserDevices.Select(x => x.DeviceId).ToList();
var devices = allDevices.Where(x => UserDevicesIds.Contains(x.Id)).ToList();
return(devices);
}
}
catch (Exception)
{
throw;
}
}
public IActionResult GetDeviceList(string _cihazId, string _cihazGsmNo, string _plaka, int _sirket, int _kullanimDurumu,
int _altFirmalıGoster, int _aktiflik, int _firmaTipi, int _terminalTipId, int _yazılımVersionId, int _cihazMontajTuru)
{
try
{
_cihazId = _cihazId == null ? "" : _cihazId;
_cihazGsmNo = _cihazGsmNo == null ? "" : _cihazGsmNo;
_plaka = _plaka == null ? "" : _plaka;
var deviceList = DeviceDB.GetInstance().GetAllDevices(userObj, _cihazId, _cihazGsmNo, _plaka, _sirket, _kullanimDurumu,
_altFirmalıGoster, _aktiflik, _firmaTipi, _terminalTipId, _yazılımVersionId, _cihazMontajTuru);
if (deviceList == null || deviceList.Count == 0)
{
return(Json(false));
}
return(PartialView("_DeviceList", deviceList));
}
catch (Exception exc)
{
throw exc;
}
}
public string GetDevicesInformation(string _username, string _password)
{
try
{
var userObj = UserDB.GetInstance().ControlUser(_username, _password);
//var result = DeviceDB.GetInstance().GetDevicesForFrontSide(userObj);
var result = DeviceDB.GetInstance().GetDevicesForFrontSide(userObj);
foreach (var item in result)
{
item.Konum = item.Konum == null ? "" : item.Konum;
item.KmPerHour = item.KmPerHour == null ? "0" : item.KmPerHour;
item.IoStatus = item.IoStatus != null && item.IoStatus.EndsWith("1") ? "1" : "0";
item.ActivityTime = Convert.ToInt32(item.ActivityTime) > (DateTime.Now - Convert.ToDateTime(item.LastDeviceDataCreateDate)).TotalMinutes ? "Aktif" : "Pasif";
item.LastLocationTime = item.LastLocationTime == null ? "" : item.LastLocationTime;
item.LastDeviceDataCreateDate = item.LastDeviceDataCreateDate.Replace("T", " ").Substring(0, 19);
item.Employee = item.Employee == null ? "" : item.Employee;
item.VehicleType = item.VehicleType == null ? "0" : item.VehicleType;
}
return(JsonConvert.SerializeObject(new ApiResult()
{
Response = result != null ? "Success" : "Error",
Message = result != null ? JsonConvert.SerializeObject(result) : "",
Status = result != null
}));
}
catch (Exception exc)
{
return(JsonConvert.SerializeObject(new ApiResult()
{
Response = "Error",
Message = exc.Message + "," + (exc.InnerException != null ? exc.InnerException.ToString() : ""),
Status = false
}));
}
}
public List <Device> GetAllUserDevicesByUserIdToLeft(User _userObj)
{
try
{
using (var context = new VeraEntities())
{
var allDevices = DeviceDB.GetInstance().GetAllDBDevices();
if (_userObj.Id == 774)
{
return(allDevices);
}
else
{
var myCompanyId = context.CompanyUser.FirstOrDefault(x => x.UserId == _userObj.Id).CompanyId;
var myDevices = allDevices.Where(x => x.CompanyId == myCompanyId).ToList();
return(myDevices);
}
}
}
catch (Exception)
{
throw;
}
}
public IActionResult GiseGecisRaporu(int _deviceId, string _startDate, string _endDate)
{
try
{
var returnList = new List <AracGiseRaporuResultRepo>();
var tempList = new List <GiseGecisRaporuTemp>();
_startDate += " 00:00:00";
_endDate += " 23:59:59";
var startDate = DateTime.ParseExact(_startDate, "yyyy-MM-dd HH:mm:ss", null);
var endDate = DateTime.ParseExact(_endDate, "yyyy-MM-dd HH:mm:ss", null);
var list = new List <TollBooth>();
//list.Add(new TollBooth()
//{
// Name = "Fatih Sultan Mehmet Köprüsü Gişesi Avrupa",
// LatLons = "41.091671, 29.037541,41.091407, 29.043650,41.090284, 29.043510,41.090463, 29.037225"
//});
//list.Add(new TollBooth()
//{
// Name = "Fatih Sultan Mehmet Köprüsü Gişesi Anadolu",
// LatLons = "41.092351, 29.070094,41.091108, 29.070067,41.091210, 29.074501,41.092534, 29.074379"
//});
using (var context = new VeraEntities())
{
list = context.TollBooth.ToList();
var deviceDatas = context.DeviceData.Where(x => x.DeviceId == _deviceId &&
x.CreateDate >= startDate &&
x.CreateDate <= endDate
).OrderBy(x => x.CreateDate).ToList();
foreach (var deviceData in deviceDatas)
{
foreach (var item in list)
{
var latLonList = new List <CustomLatLon>();
var points = item.LatLons.Split(',');
for (var i = 0; i < points.Length; i += 2)
{
var lat = Convert.ToDouble(points[i].Trim().Replace(".", ","), CultureInfo.GetCultureInfo("tr-TR"));
var lon = Convert.ToDouble(points[i + 1].Trim().Replace(".", ","), CultureInfo.GetCultureInfo("tr-TR"));
latLonList.Add(new CustomLatLon(lat, lon));
}
var result = Contains(new CustomLatLon((double)deviceData.Latitude, (double)deviceData.Longtitude), latLonList);
if (result)
{
tempList.Add(new GiseGecisRaporuTemp()
{
DirectionDegree = deviceData.DirectionDegree,
Gise = item.Name,
Zaman = deviceData.CreateDate
});
}
}
}
}
tempList = tempList.OrderBy(x => x.Zaman).ToList();
var dt = JsonConvert.DeserializeObject <DataTable>(JsonConvert.SerializeObject(tempList.OrderBy(x => x.Zaman).ToList()));
dt.Columns.Add("WillBeDeleted");
for (var i = 0; i < dt.Rows.Count; i++)
{
if ((i + 1) >= dt.Rows.Count)
{
break;
}
var currentGiseAdi = dt.Rows[i]["Gise"].ToString();
var nextGiseAdi = dt.Rows[i + 1]["Gise"].ToString();
var currentDegree = Convert.ToInt32(dt.Rows[i]["DirectionDegree"].ToString());
var nextDegree = Convert.ToInt32(dt.Rows[i + 1]["DirectionDegree"].ToString());
if (currentGiseAdi == nextGiseAdi)
{
if (currentDegree - 75 < nextDegree && currentDegree + 75 > nextDegree)
{
dt.Rows[i + 1]["WillBeDeleted"] = "true";
}
}
}
for (var i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["WillBeDeleted"].ToString() == "true")
{
dt.Rows.RemoveAt(i);
i = -1;
}
}
var ss = JsonConvert.SerializeObject(dt);
dt.Columns.Remove("WillBeDeleted");
for (var i = 0; i < dt.Rows.Count; i += 2)
{
returnList.Add(new AracGiseRaporuResultRepo()
{
GirisDirectionDegree = Convert.ToDecimal(dt.Rows[i]["DirectionDegree"].ToString()),
GirisGisesi = dt.Rows[i]["Gise"].ToString(),
GirisZamani = Convert.ToDateTime(dt.Rows[i]["Zaman"].ToString()),
CikisDirectionDegree = Convert.ToDecimal(dt.Rows[i + 1]["DirectionDegree"].ToString()),
CikisGisesi = dt.Rows[i + 1]["Gise"].ToString(),
CikisZamani = Convert.ToDateTime(dt.Rows[i + 1]["Zaman"].ToString())
});
}
foreach (var item in returnList)
{
item.GecisSuresi = ReportDB.GetInstance().TotalHoursToHumanReadableString((item.CikisZamani - item.GirisZamani).TotalHours.ToString());
}
ViewBag.ReportStartDate = DateTime.ParseExact(_startDate, "yyyy-MM-dd HH:mm:ss", null).ToString("dd.MM.yyyy HH:mm");
ViewBag.ReportEndDate = DateTime.ParseExact(_endDate, "yyyy-MM-dd HH:mm:ss", null).ToString("dd.MM.yyyy HH:mm");
ViewBag.PlateNumber = DeviceDB.GetInstance().GetDeviceById(_deviceId).CarPlateNumber;
return(PartialView("_GiseGecisRaporuResult", returnList));
}
catch (Exception exc)
{
throw exc;
}
}
public JsonResult SaveDevice(int _id, int _companyId, int _deviceMontageTypeId, int _softwareVersionId, int _usingStatusId,
int _messagingId, int _refTelemetryRecipeId, int _serviceTypeId, int _wocheckerFlagId, int _vehicleTypeId, string _deviceId, int _terminalProtocolId, int _terminalTypeId, int _gatewayId, string _carPlateNumber,
string _deviceGsmNumber, bool _rfidEngineBlock, bool _rfidEngineBlockStatus, int _programmedLocationSendTime, int _programmedLocationDistance, int _programmedSpeedLimit, int _programmedWaitingTime, int _programmedHeartBeat, int _activityTime,
string _cameraURL, bool _isSummerTime, int _timeDifference, string _IMEINumber, bool _journey, bool _telemetry, bool _doorSensor, bool _heatSensor, bool _engineBlock, bool _trafficData, bool _constantMobile, string _constantLatitude, string _constantLongitude, bool _GSensor, bool _driverScoring,
string _accountingCode, string _montageDate, string _montagePerson, string _technicalNote)
{
try
{
if (_id == 0)
{
Device deviceToAdd = new Device()
{
CompanyId = _companyId,
DeviceMontageTypeId = _deviceMontageTypeId,
SoftwareVersionId = _softwareVersionId,
UsingStatusId = _usingStatusId,
MessagingId = _messagingId,
RefTelemetryRecipeId = _refTelemetryRecipeId,
ServiceTypeId = _serviceTypeId,
WocheckerFlagId = _wocheckerFlagId,
VehicleTypeId = _vehicleTypeId,
DeviceId = _deviceId,
TerminalProtocolId = _terminalProtocolId,
TerminalTypeId = _terminalTypeId,
GatewayId = _gatewayId,
CarPlateNumber = _carPlateNumber,
DeviceGsmNumber = _deviceGsmNumber,
RFIDEngineBlock = _rfidEngineBlock,
RFIDEngineBlockStatus = _rfidEngineBlockStatus,
ProgrammedLocationSendTime = _programmedLocationSendTime,
ProgrammedLocationDistance = _programmedLocationDistance,
ProgrammedSpeedLimit = _programmedSpeedLimit,
ProgrammedWaitingTime = _programmedWaitingTime,
ProgrammedHeartBeat = _programmedHeartBeat,
ActivityTime = _activityTime,
CameraURL = _cameraURL,
IsSummerTime = _isSummerTime,
TimeDifference = _timeDifference,
IMEINumber = _IMEINumber,
Journey = _journey,
Telemetry = _telemetry,
DoorSensor = _doorSensor,
HeatSensor = _heatSensor,
EngineBlock = _engineBlock,
TrafficData = _trafficData,
ConstantMobile = _constantMobile,
ConstantLatitude = _constantLatitude,
ConstantLongitude = _constantLongitude,
GSensor = _GSensor,
DriverScoring = _driverScoring,
Status = 1,
CreationDate = DateTime.Now,
AccountingCode = _accountingCode
};
var result = DeviceDB.GetInstance().AddNewDevice(deviceToAdd);
return(Json(result != null));
}
else
{
Device deviceToUpdate = new Device()
{
Id = _id,
CompanyId = _companyId,
DeviceMontageTypeId = _deviceMontageTypeId,
SoftwareVersionId = _softwareVersionId,
UsingStatusId = _usingStatusId,
MessagingId = _messagingId,
RefTelemetryRecipeId = _refTelemetryRecipeId,
ServiceTypeId = _serviceTypeId,
WocheckerFlagId = _wocheckerFlagId,
VehicleTypeId = _vehicleTypeId,
DeviceId = _deviceId,
TerminalProtocolId = _terminalProtocolId,
TerminalTypeId = _terminalTypeId,
GatewayId = _gatewayId,
CarPlateNumber = _carPlateNumber,
DeviceGsmNumber = _deviceGsmNumber,
RFIDEngineBlock = _rfidEngineBlock,
RFIDEngineBlockStatus = _rfidEngineBlockStatus,
ProgrammedLocationDistance = _programmedLocationDistance,
ProgrammedLocationSendTime = _programmedLocationSendTime,
ProgrammedSpeedLimit = _programmedSpeedLimit,
ProgrammedWaitingTime = _programmedWaitingTime,
ProgrammedHeartBeat = _programmedHeartBeat,
ActivityTime = _activityTime,
CameraURL = _cameraURL,
IsSummerTime = _isSummerTime,
TimeDifference = _timeDifference,
IMEINumber = _IMEINumber,
Journey = _journey,
Telemetry = _telemetry,
DoorSensor = _doorSensor,
HeatSensor = _heatSensor,
EngineBlock = _engineBlock,
TrafficData = _trafficData,
ConstantMobile = _constantMobile,
ConstantLatitude = _constantLatitude,
ConstantLongitude = _constantLongitude,
GSensor = _GSensor,
DriverScoring = _driverScoring,
Status = 1,
CreationDate = DateTime.Now,
AccountingCode = _accountingCode,
MontageDate = String.IsNullOrEmpty(_montageDate) ? null : (DateTime?)DateTime.ParseExact(_montageDate, "yyyy-MM-dd", CultureInfo.InvariantCulture),
MontagePerson = _montagePerson,
TechnicalNote = _technicalNote
};
var result = DeviceDB.GetInstance().UpdateDevice(deviceToUpdate);
return(Json(result != null));
}
}
catch (Exception exc)
{
throw exc;
}
}
public JsonResult UploadFile(IFormFile file)
{
if (file == null)
{
return(Json(false));
}
if (file.FileName.EndsWith(".xlsx") || file.FileName.EndsWith(".xls"))
{
var fileName = Path.Combine(_env.WebRootPath, "Content", "TempUploads", Guid.NewGuid().ToString().Replace("-", "") + Path.GetExtension(file.FileName));
try
{
using (var fileStream = new FileStream(fileName, FileMode.Create))
{
var task = file.CopyToAsync(fileStream);
task.Wait();
}
Workbook workbook = new Workbook();
workbook.LoadFromFile(fileName);
Worksheet sheet = workbook.Worksheets[0];
var dt = sheet.ExportDataTable();
foreach (DataRow dr in dt.Rows)
{
var company = dr["Şirket"].ToString().Trim();
var carPlateNumber = dr["Plaka"].ToString().Trim();
var customerPlateNumber = dr["Müşteri Plakası"].ToString().Trim();
var deviceId = dr["Cihaz ID (Comm_id)"].ToString().Trim();
var deviceGsmNumber = dr["Cihaz Gsm No"].ToString().Trim();
var imei = dr["IMEI No"].ToString().Trim();
var carPhoneNumber = dr["Araç Telefonu"].ToString().Trim();
var aveaPlaka = dr["Avea Plaka"].ToString().Trim();
var terminalProtocol = dr["Terminal Protokol"].ToString().Trim();
var gateway = dr["Gateway"].ToString().Trim();
var terminalType = dr["Terminal Tipi"].ToString().Trim();
var odometreStatus = dr["Odometre Durumu"].ToString().Trim();
var softwareVersion = dr["Yazılım Ver."].ToString().Trim();
var programmedLocationSendTime = dr["Programlanan Konum Süresi(dk)"].ToString().Trim();
var programmedLocationDistance = dr["Programlanan Konum Mesafe(mt)"].ToString().Trim();
var programmedSpeedLimit = dr["Programlanan Hız Limiti(km)"].ToString().Trim();
var ProgrammedWaitingTime = dr["Programlanan Bekleme(dk)"].ToString().Trim();
var ProgrammedHeartBeat = dr["Programlanan HeartBeat(sn)"].ToString().Trim();
var activityTime = dr["Aktiflik Süresi(dk)"].ToString().Trim();
var journey = dr["Sefer"].ToString().Trim();
var messaging = dr["Mesajlaşma"].ToString().Trim();
var telemetry = dr["Telemetri"].ToString().Trim();
var heatSensor = dr["Isı Sensörü"].ToString().Trim();
var doorSensor = dr["Kapı Sensörü"].ToString().Trim();
var constantMobile = dr["Sabit Mobil"].ToString().Trim();
var lastActivityTime = dr["Son Aktiflik Zamanı"].ToString().Trim();
var constantLatitude = dr["Sabit Lat. (DD.DDDDDD)"].ToString().Trim();
var wocheckerFlag = dr["WocheckerFlag"].ToString().Trim();
var mobileNote = dr["Mobil Not"].ToString().Trim();
var techinalNote = dr["Teknik Not"].ToString().Trim();
var montageDate = dr["Montaj Tarihi"].ToString().Trim();
var montagePerson = dr["Montajı Yapan"].ToString().Trim();
var deviceMontageType = dr["Cihaz Montaj Türü"].ToString().Trim();
var engineBlock = dr["Motor Blokajı"].ToString().Trim();
var engineBlockStatus = dr["Motor Blokaj Durumu"].ToString().Trim();
var vehicleBlock = dr["Araç Blokajı"].ToString().Trim();
var refTelemetryType = dr["Ref. Telemetri Reçetesi"].ToString().Trim();
var gSensor = dr["G Sensör"].ToString().Trim();
var driverScoring = dr["Sürücü Skorlama"].ToString().Trim();
var rfidEngineBlock = dr["RFID Motor Blokaj"].ToString().Trim();
var rfidEngineBlockStatus = dr["RFID Motor Blokaj Durumu"].ToString().Trim();
var device = new Device()
{
CompanyId = CompanyDB.GetInstance().GetCompanyByName(company).Id,
CarPlateNumber = carPlateNumber,
CustomerPlateNumber = customerPlateNumber,
DeviceId = deviceId,
DeviceGsmNumber = deviceGsmNumber,
IMEINumber = imei,
CarPhoneNumber = carPhoneNumber,
AveaPlaka = aveaPlaka,
TerminalProtocolId = TerminalProtocolDB.GetInstance().GetTerminalProtocolByNameOrInsert(terminalProtocol).Id,
GatewayId = GatewayDB.GetInstance().GetGatewayByNameOrInsert(gateway).Id,
TerminalTypeId = TerminalTypeDB.GetInstance().GetTerminalTypeByNameOrInsert(terminalType).Id,
SoftwareVersionId = SoftwareVersionDB.GetInstance().GetSoftwareVersionByNameOrInsert(softwareVersion, TerminalTypeDB.GetInstance().GetTerminalTypeByNameOrInsert(terminalType).Id).Id,
OdometreStatusId = OdometreStatusDB.GetInstance().GetOdometreStatusByNameOrInsert(odometreStatus).Id,
ProgrammedLocationSendTime = Convert.ToInt32(programmedLocationSendTime),
ProgrammedLocationDistance = Convert.ToInt32(programmedLocationDistance),
ProgrammedSpeedLimit = Convert.ToInt32(programmedSpeedLimit),
ProgrammedWaitingTime = Convert.ToInt32(ProgrammedWaitingTime),
ProgrammedHeartBeat = Convert.ToInt32(ProgrammedHeartBeat),
ActivityTime = Convert.ToInt32(activityTime),
Journey = journey == "Var" ? true : false,
MessagingId = MessagingDB.GetInstance().GetMessagingByNameOrInsert(messaging).Id,
Telemetry = telemetry == "Var" ? true : false,
HeatSensor = heatSensor == "Var" ? true : false,
DoorSensor = doorSensor == "Var" ? true : false,
ConstantMobile = constantMobile == "Aktif" ? true : false,
LastActivityTime = GetExcelDateTime(lastActivityTime),
ConstantLatitude = constantLatitude,
WocheckerFlagId = WocheckerFlagDB.GetInstance().GetWocheckerFlagByNameOrInsert(wocheckerFlag).Id,
MobileNote = mobileNote,
TechnicalNote = techinalNote,
MontageDate = GetExcelDateTime(montageDate),
MontagePerson = montagePerson,
DeviceMontageTypeId = DeviceMontageTypeDB.GetInstance().GetDeviceMontageTypeByNameOrInsert(deviceMontageType).Id,
EngineBlock = engineBlock == "Var" ? true : false,
EngineBlockStatus = engineBlockStatus == "Aktif" ? true : false,
VehicleBlock = vehicleBlock == "Var" ? true : false,
RefTelemetryRecipeId = RefTelemetryTypeDB.GetInstance().GetRefTelemetryTypeByNameOrInsert(refTelemetryType).Id,
GSensor = gSensor == "Var" ? true : false,
DriverScoring = driverScoring == "Var" ? true : false,
RFIDEngineBlock = rfidEngineBlock == "Var" ? true : false,
RFIDEngineBlockStatus = rfidEngineBlockStatus == "Var" ? true : false,
CreationDate = DateTime.Now,
Status = 1,
};
var result = DeviceDB.GetInstance().AddNewDevice(device);
}
if (System.IO.File.Exists(fileName))
{
System.IO.File.Delete(fileName);
}
return(Json(true));
}
catch (Exception exc)
{
if (System.IO.File.Exists(fileName))
{
System.IO.File.Delete(fileName);
}
return(Json(exc.Message));
}
}
else
{
return(Json(false));
}
}
public JsonResult ImportFile(string _excelData)
{
_excelData = HttpUtility.HtmlDecode(_excelData);
var list2 = JsonConvert.DeserializeObject <List <DeviceExcelObject> >(_excelData);
try
{
foreach (var item in list2)
{
Device device = new Device()
{
CompanyId = CompanyDB.GetInstance().GetCompanyByName(item.Sirket).Id,
CarPlateNumber = item.Plaka,
CustomerPlateNumber = item.MusteriPlakasi,
DeviceId = item.CihazId,
DeviceGsmNumber = item.CihazGsmNo,
IMEINumber = item.IMEINo,
VehiclePhone = item.AracTelefonu,
AveaPlaka = item.AveaPlaka,
TerminalProtocolId = TerminalProtocolDB.GetInstance().GetTerminalProtocolByNameOrInsert(item.TerminalProtokol).Id,
GatewayId = GatewayDB.GetInstance().GetGatewayByNameOrInsert(item.Gateway).Id,
TerminalTypeId = TerminalTypeDB.GetInstance().GetTerminalTypeByNameOrInsert(item.TerminalTipi).Id,
SoftwareVersionId = SoftwareVersionDB.GetInstance().GetSoftwareVersionByNameOrInsert(item.YazilimVers, TerminalTypeDB.GetInstance().GetTerminalTypeByNameOrInsert(item.TerminalTipi).Id).Id,
OdometreStatusId = OdometreStatusDB.GetInstance().GetOdometreStatusByNameOrInsert(item.OdometreDurumu).Id,
ProgrammedLocationSendTime = Convert.ToInt32(item.ProgramlananKonumSuresi.Trim()),
ProgrammedLocationDistance = Convert.ToInt32(item.ProgramlananKonumMesafe.Trim()),
ProgrammedSpeedLimit = Convert.ToInt32(item.ProgramlananHizLimiti.Trim()),
ProgrammedHeartBeat = Convert.ToInt32(item.ProgramlananHeartBeat.Trim()),
ProgrammedWaitingTime = Convert.ToInt32(item.ProgramlananBekleme.Trim()),
ActivityTime = Convert.ToInt32(item.AktiflikSuresi.Trim()),
Journey = item.Sefer.Trim() == "Var" ? true : false,
MessagingId = MessagingDB.GetInstance().GetMessagingByNameOrInsert(item.Mesajlasma).Id,
Telemetry = item.Telemetri.Trim() == "Var" ? true : false,
HeatSensor = item.IsiSensoru.Trim() == "Var" ? true : false,
DoorSensor = item.KapiSensoru.Trim() == "Var" ? true : false,
ConstantMobile = item.SabitMobil.Trim() == "Aktif" ? true : false,
ConstantLatitude = item.SabitLatitude,
//ConstantLongitude= ?????????????????????????????????????????????
WocheckerFlagId = WocheckerFlagDB.GetInstance().GetWocheckerFlagByNameOrInsert(item.WocheckerFlag).Id,
MobileNote = item.MobilNot,
TechnicalNote = item.TeknikNot,
MontageDate = GetExcelDateTime(item.MontajTarihi),
MontagePerson = item.MontajiYapan,
DeviceMontageTypeId = DeviceMontageTypeDB.GetInstance().GetDeviceMontageTypeByNameOrInsert(item.CihazMontajTuru).Id,
EngineBlock = item.MotorBlokaji.Trim() == "Var" ? true : false,
EngineBlockStatus = item.MotorBlokajDurumu.Trim() == "Aktif" ? true : false,
GSensor = item.GSensor.Trim() == "Var" ? true : false,
DriverScoring = item.SurucuSkorlama.Trim() == "Var" ? true : false,
RFIDEngineBlock = item.RFIDMotorBlokaj.Trim() == "Var" ? true : false,
RFIDEngineBlockStatus = item.RFIDMotorBlokajDurumu.Trim() == "Aktif" ? true : false,
CreationDate = DateTime.Now,
Status = 1
};
if (device != null)
{
DeviceDB.GetInstance().AddNewDevice(device);
}
}
return(Json(true));
}
catch (Exception exc)
{
throw exc;
}
}
public IActionResult SeferOzetiRaporu(int _deviceId, string _startDate, string _endDate)
{
using (var context = new VeraEntities())
{
using (var con = context.Database.GetDbConnection())
{
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
using (var cmd = con.CreateCommand())
{
_startDate += " 00:00:00";
_endDate += " 23:59:59";
cmd.CommandText = $"EXEC USP_SeferOzetiRaporu '{_startDate}','{_endDate}',{_deviceId}";
cmd.CommandTimeout = Int32.MaxValue;
context.Database.SetCommandTimeout(Int32.MaxValue);
var reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append(reader.GetString(0));
}
var json = sb.ToString();
var list1 = JsonConvert.DeserializeObject <List <SeferOzetiRaporuObject> >(json);
list1 = list1 == null ? new List <SeferOzetiRaporuObject>() : list1;
list1 = list1.Where(x => x.EndKontakAcilmaTarihi != null).ToList();
var returnList = new List <SeferOzetiRaporuObjectRepo>();
var toplamSaat = 0d;
foreach (var item in list1)
{
var startDate = Convert.ToDateTime(item.StartKontakAcilmaTarihi);
var endDate = Convert.ToDateTime(item.EndKontakAcilmaTarihi);
toplamSaat += (endDate - startDate).TotalHours;
returnList.Add(new SeferOzetiRaporuObjectRepo()
{
Sure = ReportDB.GetInstance().TotalHoursToHumanReadableString((endDate - startDate).TotalHours.ToString()),
VarisZamani = item.EndKontakAcilmaTarihi,
KalkisZamani = item.StartKontakAcilmaTarihi,
OrtHiz = item.AverageKm,
MaxHiz = item.MaxSpeed,
Mesafe = (Convert.ToInt32(item.EndTotalKm) - Convert.ToInt32(item.StartTotalKm)).ToString(),
KalkisPozisyon = DeviceDB.GetInstance().GetLocationFromLatLon2(item.StartLat, item.StartLong),
VarisPozisyon = DeviceDB.GetInstance().GetLocationFromLatLon2(item.EndLat, item.EndLong),
ToplamKm = item.EndTotalKm,
StartTotalKm = item.StartTotalKm,
EndTotalKm = item.EndTotalKm
});
}
ViewBag.ToplamGecenSureString = ReportDB.GetInstance().TotalHoursToHumanReadableString(toplamSaat.ToString());
ViewBag.ReportStartDate = DateTime.ParseExact(_startDate, "yyyy-MM-dd HH:mm:ss", null).ToString("dd.MM.yyyy HH:mm");
ViewBag.ReportEndDate = DateTime.ParseExact(_endDate, "yyyy-MM-dd HH:mm:ss", null).ToString("dd.MM.yyyy HH:mm");
ViewBag.MinSure = ReportDB.GetInstance().TotalHoursToHumanReadableString(returnList.Select(x => (Convert.ToDateTime(x.VarisZamani) - Convert.ToDateTime(x.KalkisZamani)).TotalHours).ToList().OrderBy(x => x).FirstOrDefault().ToString());
ViewBag.MaxSure = ReportDB.GetInstance().TotalHoursToHumanReadableString(returnList.Select(x => (Convert.ToDateTime(x.VarisZamani) - Convert.ToDateTime(x.KalkisZamani)).TotalHours).ToList().OrderByDescending(x => x).FirstOrDefault().ToString());
ViewBag.PlateNumber = DeviceDB.GetInstance().GetDeviceById(_deviceId).CarPlateNumber;
reader.Close();
return(PartialView("_SeferOzetiRaporuResult", returnList));
}
}
catch (Exception exc)
{
throw exc;
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
}
}
}
}
private void updateRightList()
{
ListData.Items.Clear();
switch (selectedAdd)
{
case "Container":
ListData.Items.Add(
"ContainerID".PadRight(13) +
"Plaats".PadRight(15) +
"Van".PadRight(20) +
"Tot");
List <Container> containerList = ContainerDB.GetContainers(myConnectionString);
foreach (Container container in containerList)
{
ListData.Items.Add(
container.ContainerID.ToString().PadRight(13) +
container.Plaats.PadRight(15) +
container.Van.ToString("dd/MM/yyyy").PadRight(20) +
container.Tot.ToString("dd/MM/yyyy"));
}
break;
case "Device":
ListData.Items.Add(
"DeviceID".PadRight(10) +
"Van".PadRight(20) +
"Tot");
List <Device> deviceList = DeviceDB.GetDevice(myConnectionString);
foreach (Device device in deviceList)
{
ListData.Items.Add(
device.DeviceID.ToString().PadRight(10) +
device.Van.ToString().PadRight(20) +
device.Tot);
}
break;
case "Event":
ListData.Items.Add(
"ContactpersoonID".PadRight(18) +
"Naam".PadRight(15) +
"Locatie".PadRight(10) +
"ContactpersoonID".PadRight(18) +
"VerantwoordelijkeID");
List <Events> eventsList = EventsDB.GetEvents(myConnectionString);
foreach (Events events in eventsList)
{
ListData.Items.Add(
events.ContactpersoonID.ToString().PadRight(18) +
events.Naam.PadRight(15) +
events.Locatie.PadRight(10) +
events.ContactpersoonID.ToString().PadRight(18) +
events.VerantwoordelijkeID);
}
break;
case "Persoon":
ListData.Items.Add(
"PersoonID".PadRight(10) +
"Functie".PadRight(10) +
"Voornaam".PadRight(10) +
"Achternaam".PadRight(15) +
"GSM");
List <Persoon> persoonList = PersoonDB.GetPeople(myConnectionString);
foreach (Persoon persoon in persoonList)
{
ListData.Items.Add(
persoon.PersoonID.ToString().PadRight(10) +
persoon.Functie.PadRight(10) +
persoon.Voornaam.PadRight(10) +
persoon.Achternaam.PadRight(15) +
persoon.GSM);
}
break;
}
}
