/// <summary>
/// Returns all users from the database but only with guid and username
/// fields filled out. (Performance...)
/// </summary>
/// <returns></returns>
public static IEnumerable<UserModel> GetUsers()
{
var dc = new BicikliDataClassesDataContext();
return from u in dc.Users
orderby u.UserName ascending
select new UserModel
{
guid = u.UserId,
username = u.UserName
};
}
/// <summary>
/// Returns all assigned lenders by username
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public static IEnumerable<LenderModel> GetAssignedLenders(string username)
{
var dc = new BicikliDataClassesDataContext();
return from l in dc.Lenders
join lu in dc.LenderUsers
on l equals lu.Lender
where (lu.User.UserId == (Guid)Membership.GetUser(username).ProviderUserKey)
orderby l.name ascending
select new LenderModel
{
id = l.id,
latitude = l.latitude,
longitude = l.longitude,
name = l.name,
address = l.address,
description = l.description,
printer_ip = l.printer_ip,
printer_password = l.printer_password
};
}
/// <summary>
/// Returns all lenders assigned to a certain user
/// </summary>
/// <param name="guid"></param>
/// <returns></returns>
public static IEnumerable<LenderModel> GetLendersOfUser(Guid guid)
{
var dc = new BicikliDataClassesDataContext();
return from lu in dc.LenderUsers
join l in dc.Lenders
on lu.lender_id equals l.id
where (lu.user_id == guid)
orderby l.name ascending
select new LenderModel
{
id = l.id,
address = l.address,
description = l.description,
latitude = l.latitude,
longitude = l.longitude,
name = l.name,
printer_ip = l.printer_ip,
printer_password = l.printer_password
};
}
/// <summary>
/// Returns the nearest dangerous zone to the specified coordinates
/// </summary>
/// <param name="latitude"></param>
/// <param name="longitude"></param>
/// <returns></returns>
public static ZoneModel GetNearestDangerousZone(double latitude, double longitude)
{
var db = new BicikliDataClassesDataContext();
return (from dz in db.GetDangerousZonesByDistance(latitude, longitude)
select new ZoneModel()
{
id = dz.id
}).FirstOrDefault();
}
/// <summary>
/// Deletes a dangerous zone
/// </summary>
/// <param name="id"></param>
public static void DeleteZone(int id)
{
var db = new BicikliDataClassesDataContext();
var zoneToRemove = db.DangerousZones.Single(z => z.id == id);
if (zoneToRemove.Sessions.Count() > 0)
{
foreach (Session s in zoneToRemove.Sessions)
{
s.dz_id = null;
}
}
db.DangerousZones.DeleteOnSubmit(zoneToRemove);
db.SubmitChanges();
}
/// <summary>
/// Inserts a bike into the database
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
public static int InsertBike(BikeModel m)
{
var db = new BicikliDataClassesDataContext();
var bike = new Bike()
{
description = m.description,
is_active = m.isActive,
name = m.name,
current_lender_id = m.currentLenderId
};
db.Bikes.InsertOnSubmit(bike);
db.SubmitChanges();
return bike.id;
}
/// <summary>
/// Returns all sessions (invoices) from the database
/// </summary>
/// <returns></returns>
public static IEnumerable<SessionModel> GetSessions()
{
var dc = new BicikliDataClassesDataContext();
return from s in dc.Sessions
orderby s.start_time descending
select new SessionModel()
{
address = s.address,
bike_id = s.bike_id,
dangerousZoneId = s.dz_id,
dangerousZoneTime = s.dz_time,
endTime = s.end_time,
id = s.id,
lastReport = s.last_report,
latitude = s.latitude,
longitude = s.longitude,
name = s.name,
normalTime = s.normal_time,
startTime = s.start_time,
paid = s.paid,
normal_price = s.normal_price,
danger_price = s.danger_price,
normal_vat = s.normal_vat,
danger_vat = s.danger_vat,
bikeModel = DataRepository.GetBike(s.bike_id)
};
}
/// <summary>
/// Returns the last lending data of a bike
/// </summary>
/// <param name="bike_id"></param>
/// <returns></returns>
public static LastLendingOfBike GetLastLendingOf(int bike_id)
{
var dc = new BicikliDataClassesDataContext();
return (from s in dc.Sessions
where s.bike_id == bike_id
orderby s.start_time descending
select new LastLendingOfBike()
{
lastLendingDate = s.start_time,
lastSession = new SessionModel()
{
address = s.address,
bike_id = s.bike_id,
dangerousZoneId = s.dz_id,
dangerousZoneTime = s.dz_time,
endTime = s.end_time,
id = s.id,
lastReport = s.last_report,
latitude = s.latitude,
longitude = s.longitude,
name = s.name,
normalTime = s.normal_time,
paid = s.paid,
normal_price = s.normal_price,
danger_price = s.danger_price,
normal_vat = s.normal_vat,
danger_vat = s.danger_vat,
startTime = s.start_time
}
}).FirstOrDefault();
}
/// <summary>
/// Returns all lenders for API
/// </summary>
/// <returns></returns>
public static IEnumerable<ApiModels.LenderListItemModel> GetLendersForApi()
{
var dc = new BicikliDataClassesDataContext();
return from li in dc.GetLendersList()
select new ApiModels.LenderListItemModel()
{
id = li.id,
latitude = li.latitude,
longitude = li.longitude,
name = li.name,
address = li.address
};
}
/// <summary>
/// Returns the nearest lender within a specified radius
/// </summary>
/// <param name="latitude"></param>
/// <param name="longitude"></param>
/// <param name="radius"></param>
/// <returns></returns>
public static LenderModel GetNearestLenderInRadius(double latitude, double longitude, double radius)
{
var dc = new BicikliDataClassesDataContext();
return (from l in dc.GetLendersByDistance(latitude, longitude, radius)
select new LenderModel()
{
id = l.id
}).FirstOrDefault();
}
/// <summary>
/// Inserts a Lender, then returns the generated primary key
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int InsertLender(LenderModel model)
{
var db = new BicikliDataClassesDataContext();
var lenderToInsert = new Lender();
lenderToInsert.name = model.name;
lenderToInsert.address = model.address;
lenderToInsert.description = model.description;
lenderToInsert.printer_ip = model.printer_ip;
lenderToInsert.latitude = model.latitude;
lenderToInsert.longitude = model.longitude;
if ((model.printer_password != null) && (model.printer_password != ""))
{
lenderToInsert.printer_password = model.printer_password;
}
else
{
lenderToInsert.printer_password = ((int)(new Random().NextDouble() * 899999) + 100000).ToString();
}
db.Lenders.InsertOnSubmit(lenderToInsert);
db.SubmitChanges();
return lenderToInsert.id;
}
/// <summary>
/// Returns all guids (user_id) assigned to a certain lender
/// </summary>
/// <param name="lender_id"></param>
/// <returns></returns>
public static IEnumerable<Guid> GetLenderAssignedGuids(int lender_id)
{
var dc = new BicikliDataClassesDataContext();
return from lug in dc.LenderUsers
where lug.lender_id == lender_id
select lug.user_id;
}
/// <summary>
/// Deletes a lender
/// </summary>
/// <param name="lender_id"></param>
public static void DeleteLender(int lender_id)
{
var dc = new BicikliDataClassesDataContext();
var lenderToRemove = dc.Lenders.Where(l => l.id == lender_id).Single();
foreach (Bike bike in lenderToRemove.Bikes)
{
bike.current_lender_id = null;
}
dc.SubmitChanges();
dc.LenderUsers.DeleteAllOnSubmit(lenderToRemove.LenderUsers);
dc.SubmitChanges();
dc.Lenders.DeleteOnSubmit(lenderToRemove);
dc.SubmitChanges();
}
/// <summary>
/// Returns all users assigned to a certain lender
/// </summary>
/// <param name="lender_id"></param>
/// <returns></returns>
public static IEnumerable<UserModel> GetLenderAssignedUsers(int lender_id)
{
var dc = new BicikliDataClassesDataContext();
return from lug in dc.LenderUsers
where lug.lender_id == lender_id
select new UserModel
{
username = lug.User.UserName,
guid = lug.User.UserId
};
}
/// <summary>
/// Returns a lender
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static LenderModel GetLender(int id)
{
var dc = new BicikliDataClassesDataContext();
return (from l in dc.Lenders
where l.id == id
orderby l.name ascending
select new LenderModel
{
id = l.id,
latitude = l.latitude,
longitude = l.longitude,
name = l.name,
address = l.address,
description = l.description,
printer_ip = l.printer_ip,
printer_password = l.printer_password
}).Single();
}
/// <summary>
/// Returns a lender for API
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static ApiModels.LenderModel GetLenderForApi(int id)
{
var dc = new BicikliDataClassesDataContext();
return (from l in dc.GetLenderDetails(id)
select new ApiModels.LenderModel()
{
name = l.name,
address = l.address,
description = l.description,
latitude = l.latitude,
longitude = l.longitude
}).SingleOrDefault();
}
/// <summary>
/// Inserts a new Zone into the database
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
public static int InsertZone(ZoneModel m)
{
var db = new BicikliDataClassesDataContext();
var zoneToInsert = new DangerousZone()
{
description = m.description,
latitude = m.latitude,
longitude = m.longitude,
name = m.name,
radius = m.radius
};
db.DangerousZones.InsertOnSubmit(zoneToInsert);
db.SubmitChanges();
return zoneToInsert.id;
}
/// <summary>
/// Returns ALL bikes from database with every collectable data
/// </summary>
/// <returns></returns>
public static IEnumerable<BikeModel> GetBikes()
{
// ide Ãrni kell egy tárolt eljárást, hogy a többi mezÅ‘t is ki tudjam tölteni
var dc = new BicikliDataClassesDataContext();
#region Get all bikes...
var allBikes = (from b in dc.Bikes
orderby b.name ascending
select new BikeModel()
{
id = b.id,
name = b.name,
description = b.description,
currentLenderId = b.current_lender_id,
imageUrl = b.image_url,
isActive = b.is_active
}).ToList();
#endregion
#region Get Active Sessions...
var allActiveSessions = (from s in dc.Sessions
where s.end_time == null
orderby s.start_time descending
select new SessionModel()
{
address = s.address,
bike_id = s.bike_id,
dangerousZoneId = s.dz_id,
dangerousZoneTime = s.dz_time,
endTime = s.end_time,
id = s.id,
lastReport = s.last_report,
latitude = s.latitude,
longitude = s.longitude,
name = s.name,
normalTime = s.normal_time,
startTime = s.start_time,
normal_price = s.normal_price,
danger_price = s.danger_price,
normal_vat = s.normal_vat,
danger_vat = s.danger_vat
}).ToList();
#endregion
#region Collect additional information...
foreach (var bike in allBikes)
{
var sessionsWithThisBike = allActiveSessions.Where(s => s.bike_id == bike.id);
if ((sessionsWithThisBike != null) && (sessionsWithThisBike.Count() > 0))
{
bike.session = sessionsWithThisBike.First();
bike.isInDangerousZone = (bike.session.dangerousZoneId != null);
}
var lastLendingData = GetLastLendingOf((int)bike.id);
if (lastLendingData != null)
{
bike.lastLendingDate = lastLendingData.lastLendingDate;
bike.lastSession = lastLendingData.lastSession;
}
if (bike.lastLendingDate == new DateTime())
{
bike.lastLendingDate = null;
}
if (bike.currentLenderId != null)
{
bike.lender = (from l in dc.Lenders
where l.id == bike.currentLenderId
select new LenderModel()
{
address = l.address,
description = l.description,
id = l.id,
latitude = l.latitude,
longitude = l.longitude,
name = l.name,
printer_ip = l.printer_ip,
printer_password = l.printer_password
}).SingleOrDefault();
}
}
#endregion
return allBikes;
}
/// <summary>
/// Updates a dangerous zone
/// </summary>
/// <param name="m"></param>
public static void UpdateZone(ZoneModel m)
{
var db = new BicikliDataClassesDataContext();
var zoneToUpdate = db.DangerousZones.Single(z => z.id == m.id);
if (zoneToUpdate.description != m.description)
{
zoneToUpdate.description = m.description;
}
if (zoneToUpdate.latitude != m.latitude)
{
zoneToUpdate.latitude = m.latitude;
}
if (zoneToUpdate.longitude != m.longitude)
{
zoneToUpdate.longitude = m.longitude;
}
if (zoneToUpdate.name != m.name)
{
zoneToUpdate.name = m.name;
}
if (zoneToUpdate.radius != m.radius)
{
zoneToUpdate.radius = m.radius;
}
db.SubmitChanges();
}
/// <summary>
/// Returns all users from the database with every collectable detail
/// </summary>
/// <returns></returns>
public static IEnumerable<UserModel> GetUsersWithDetails()
{
var result = new List<UserModel>();
#region Get users data
var dc = new BicikliDataClassesDataContext();
var membershipUsers = Membership.GetAllUsers();
foreach (MembershipUser mUser in membershipUsers)
{
var uModel = new UserModel();
#region Get user data
uModel.username = mUser.UserName;
uModel.guid = (Guid)mUser.ProviderUserKey;
uModel.email = mUser.Email;
uModel.countOfLenders = GetLendersOfUser(uModel.guid).Count();
uModel.lastLogin = mUser.LastLoginDate;
uModel.isSiteAdmin = Roles.IsUserInRole(mUser.UserName, "SiteAdmin");
uModel.isLockedOut = mUser.IsLockedOut;
uModel.isApproved = mUser.IsApproved;
#endregion
result.Add(uModel);
}
#endregion
return result;
}
/// <summary>
/// Updates a Lender
/// </summary>
/// <param name="model"></param>
public static void UpdateLender(LenderModel model)
{
var db = new BicikliDataClassesDataContext();
var lenderToUpdate = db.Lenders.First(l => l.id == model.id);
lenderToUpdate.address = model.address;
lenderToUpdate.description = model.description;
lenderToUpdate.latitude = model.latitude;
lenderToUpdate.longitude = model.longitude;
lenderToUpdate.name = model.name;
lenderToUpdate.printer_ip = model.printer_ip;
if ((model.printer_password != null) && (model.printer_password != ""))
{
lenderToUpdate.printer_password = model.printer_password;
}
else
{
lenderToUpdate.printer_password = ((int)(new Random().NextDouble() * 899999) + 100000).ToString();
}
db.SubmitChanges();
}
/// <summary>
/// Returns all lenders
/// </summary>
/// <returns></returns>
public static IEnumerable<LenderModel> GetLenders()
{
var dc = new BicikliDataClassesDataContext();
return from l in dc.Lenders
orderby l.name ascending
select new LenderModel
{
id = l.id,
latitude = l.latitude,
longitude = l.longitude,
name = l.name,
address = l.address,
description = l.description,
printer_ip = l.printer_ip,
printer_password = l.printer_password
};
}
/// <summary>
/// Returns only ONE bike from database with every collectable data
/// </summary>
/// <param name="bikeId"></param>
/// <returns></returns>
public static BikeModel GetBike(int bikeId)
{
var dc = new BicikliDataClassesDataContext();
#region Get the bike...
var bike = (from b in dc.Bikes
where b.id == bikeId
orderby b.name ascending
select new BikeModel()
{
id = b.id,
name = b.name,
description = b.description,
currentLenderId = b.current_lender_id,
imageUrl = b.image_url,
isActive = b.is_active
}).Single();
#endregion
#region Get Bike's Active Session if any...
bike.session = (from s in dc.Sessions
where ((s.end_time == null) && (s.bike_id == bikeId))
orderby s.start_time descending
select new SessionModel()
{
address = s.address,
bike_id = s.bike_id,
dangerousZoneId = s.dz_id,
dangerousZoneTime = s.dz_time,
endTime = s.end_time,
id = s.id,
lastReport = s.last_report,
latitude = s.latitude,
longitude = s.longitude,
name = s.name,
normalTime = s.normal_time,
startTime = s.start_time,
normal_price = s.normal_price,
danger_price = s.danger_price,
normal_vat = s.normal_vat,
danger_vat = s.danger_vat
}).SingleOrDefault();
#endregion
#region Collect additional information...
if (bike.session != null)
{
bike.isInDangerousZone = (bike.session.dangerousZoneId != null);
}
var lastLendingData = GetLastLendingOf((int)bike.id);
if (lastLendingData != null)
{
bike.lastLendingDate = lastLendingData.lastLendingDate;
bike.lastSession = lastLendingData.lastSession;
}
if (bike.lastLendingDate == new DateTime())
{
bike.lastLendingDate = null;
}
if (bike.currentLenderId != null)
{
bike.lender = (from l in dc.Lenders
where l.id == bike.currentLenderId
select new LenderModel()
{
address = l.address,
description = l.description,
id = l.id,
latitude = l.latitude,
longitude = l.longitude,
name = l.name,
printer_ip = l.printer_ip,
printer_password = l.printer_password
}).SingleOrDefault();
}
#endregion
return bike;
}
/// <summary>
/// Updates Lender-User assignments
/// </summary>
/// <param name="lender_id">Lender ID</param>
/// <param name="users">username list</param>
public static void UpdateAssignments(int lender_id, string[] users)
{
var db = new BicikliDataClassesDataContext();
#region Step 1: Extract selected user guids
var guidsToInsert = new HashSet<Guid>();
if (users != null)
{
foreach (string username in users)
{
var msUser = Membership.GetUser(username);
if (msUser != null)
{
Guid msUserGuid = (Guid)msUser.ProviderUserKey;
if (!guidsToInsert.Contains(msUserGuid))
{
guidsToInsert.Add(msUserGuid);
}
}
}
}
#endregion
#region Step 2: Apply changes to assignments
#region Insert new assignments
var lenderGuidAssignments = DataRepository.GetLenderAssignedGuids(lender_id);
foreach (Guid guid in guidsToInsert)
{
if (!lenderGuidAssignments.Contains(guid))
{
var luToInsert = new LenderUser();
luToInsert.user_id = guid;
luToInsert.lender_id = lender_id;
db.LenderUsers.InsertOnSubmit(luToInsert);
}
}
#endregion
#region Delete removed assignments
lenderGuidAssignments = DataRepository.GetLenderAssignedGuids(lender_id);
foreach (Guid guid in lenderGuidAssignments)
{
if (!guidsToInsert.Contains(guid))
{
var luToDelete = db.LenderUsers.Where(lu => (lu.lender_id == lender_id) && (lu.user_id == guid)).First();
db.LenderUsers.DeleteOnSubmit(luToDelete);
}
}
#endregion
db.SubmitChanges();
#endregion
}
/// <summary>
/// Updates a bike in the database
/// </summary>
/// <param name="m"></param>
public static void UpdateBike(BikeModel m)
{
var db = new BicikliDataClassesDataContext();
var bikeToUpdate = db.Bikes.Single(b => b.id == m.id);
if (bikeToUpdate.name != m.name)
{
bikeToUpdate.name = m.name;
}
if (bikeToUpdate.description != m.description)
{
bikeToUpdate.description = m.description;
}
if (bikeToUpdate.is_active != m.isActive)
{
bikeToUpdate.is_active = m.isActive;
}
if (bikeToUpdate.current_lender_id != m.currentLenderId)
{
bikeToUpdate.current_lender_id = m.currentLenderId;
}
if (bikeToUpdate.image_url != m.imageUrl)
{
bikeToUpdate.image_url = m.imageUrl;
}
db.SubmitChanges();
}
/// <summary>
/// Removes a printer from a lender
/// </summary>
/// <param name="lender_id"></param>
public static void RemovePrinter(int lender_id)
{
var db = new BicikliDataClassesDataContext();
var lenderWithPrinter = db.Lenders.Single(l => l.id == lender_id);
lenderWithPrinter.printer_ip = null;
db.SubmitChanges();
}
/// <summary>
/// Deletes a bike
/// </summary>
/// <param name="id"></param>
public static void DeleteBike(int id)
{
var db = new BicikliDataClassesDataContext();
db.Bikes.DeleteOnSubmit(db.Bikes.Single(b => b.id == id));
db.SubmitChanges();
}
/// <summary>
/// Returns a dangerous zone
/// </summary>
/// <returns></returns>
public static ZoneModel GetDangerousZone(int zoneId)
{
var dc = new BicikliDataClassesDataContext();
return (from z in dc.DangerousZones
where z.id == zoneId
orderby z.name ascending
select new ZoneModel()
{
id = z.id,
name = z.name,
description = z.description,
latitude = z.latitude,
longitude = z.longitude,
radius = z.radius,
bikesInThisZone = (from s in dc.Sessions
where ((s.end_time == null) && (s.dz_id == z.id))
select s.dz_id).Count()
}).Single();
}
/// <summary>
/// Returns a session (invoice) from the database
/// </summary>
/// <returns></returns>
public static SessionModel GetSession(int id)
{
var dc = new BicikliDataClassesDataContext();
return (from s in dc.Sessions
where s.id == id
select new SessionModel()
{
address = s.address,
bike_id = s.bike_id,
dangerousZoneId = s.dz_id,
dangerousZoneTime = s.dz_time,
endTime = s.end_time,
id = s.id,
lastReport = s.last_report,
latitude = s.latitude,
longitude = s.longitude,
name = s.name,
normalTime = s.normal_time,
startTime = s.start_time,
paid = s.paid,
normal_price = s.normal_price,
danger_price = s.danger_price,
normal_vat = s.normal_vat,
danger_vat = s.danger_vat,
bikeModel = DataRepository.GetBike(s.bike_id)
}).Single();
}
/// <summary>
/// Updates user assignments
/// </summary>
/// <param name="guid"></param>
/// <param name="lenders"></param>
public static void UpdateUserAssignments(string username, int[] lenders)
{
Guid guid = (Guid)Membership.GetUser(username).ProviderUserKey;
if (lenders != null)
{
var lendersToInsert = new List<int>();
foreach (int l in lenders)
{
if (!lendersToInsert.Contains(l))
{
lendersToInsert.Add(l);
}
}
var assignedLenders = DataRepository.GetLendersOfUser(guid);
var lendersToRemove = new List<int>();
foreach (LenderModel lm in assignedLenders)
{
if (lendersToInsert.Contains((int)lm.id))
{
lendersToInsert.Remove((int)lm.id);
}
else
{
lendersToRemove.Add((int)lm.id);
}
}
var db = new BicikliDataClassesDataContext();
foreach (int l in lendersToRemove)
{
db.LenderUsers.DeleteOnSubmit(db.LenderUsers.First(s => ((s.lender_id == l) && (s.user_id == guid))));
}
foreach (int l in lendersToInsert)
{
db.LenderUsers.InsertOnSubmit(new LenderUser() { lender_id = l, user_id = guid });
}
db.SubmitChanges();
}
else
{
var db = new BicikliDataClassesDataContext();
db.LenderUsers.DeleteAllOnSubmit(db.LenderUsers.Where(s => (s.user_id == guid)));
db.SubmitChanges();
}
}
