private String BuildUpdateRiderCommand(Rider rdr, string username)
{
String command;
String sb;
String sb2;
String prefix = "UPDATE [Users] ";
sb = @"SET [UserEmail] = " + (rdr.RiderEmail != null ? "'" + rdr.RiderEmail + "'" : "[UserEmail]") + @"
,[City] = " + (rdr.City != null ? " (select city from Cities where CityName = '" + rdr.City + "' )" : "[City]") + @"
,[UserFname] = " + (rdr.RiderFname != null ? "'" + rdr.RiderFname + "'" : "[UserFname]") + @"
,[UserLname] = " + (rdr.RiderLname != null ? "'" + rdr.RiderLname + "'" : "[UserLname]") + @"
,[UserAddress] = " + (rdr.RiderAddress != null ? "'" + rdr.RiderAddress + "'" : "[UserAddress]") + @"
,[UserPhone] = " + (rdr.RiderPhone != null ? "'" + rdr.RiderPhone + "'" : "[UserPhone]") + @"
,[BicycleType] = " + (rdr.BicycleType != null ? "'" + rdr.BicycleType + "'" : "[BicycleType]") + @"
,[ImagePath] = " + (rdr.ImagePath != null ? "'" + rdr.ImagePath + "'" : "[ImagePath]") + @"
,[BirthDate] = " + (rdr.BirthDate != null ? "'" + rdr.BirthDate + "'" : "[BirthDate]") + @"
,[Gender] = " + (rdr.Gender != null ? "'" + rdr.Gender + "'" : "[Gender]") + @"
,[Captain] = 0
,[Organization] = " + (rdr.Organization != null && rdr.Group != null ? "(select [Organization] from Organizations where OrganizationDES = '" + rdr.Organization + "' AND Exists ( Select G.Organization From Groups G Where G.[GroupName] = '" + rdr.Group + @"' AND G.Organization = Organization ))" : "[Organization]") + @"
WHERE UserDes = '" + username + @"';";
sb2 = @" Update [UsersGroups] Set [Group] = " + (rdr.Group != null ? "( Select [Group] From Groups Where GroupName = '" + rdr.Group + "' )" : "[Group]") + @"
Where [User] = ( Select [User] From [Users] Where UserDes = '" + username + @"');
UPDATE [Users]
SET
[GroupDes] = (Select g.GroupDes From Groups g, UsersGroups ug Where ug.[User] = Users.[User] And ug.[Group] = g.[group] )
,[OrganizationDes] = (Select OrganizationDes From Organizations Where Organization = Users.Organization)
,[CityName] = (Select CityName From Cities Where City = Users.City)
Where UserDes = '" + username + @"' ";
command = prefix + sb + sb2;
return command;
}
// Insert new Rider
public int insertRider(Rider rdr)
{
SqlConnection con;
SqlCommand cmd;
SqlCommand crmd;
try
{
con = connect("DefaultConnection"); // create the connection
}
catch (Exception ex)
{
// write to log
lf.Main("Users", ex.Message);
return 0;
}
String cStr = BuildInsertRidersCommand(rdr); // helper method to build the insert string
cmd = CreateCommand(cStr, con); // create the command
try
{
int numEffected = cmd.ExecuteNonQuery(); // execute the command
int numEffected_2 = 0;
if (numEffected > 0)
{
String ins = BuildInsertRidersGroup(rdr);
crmd = CreateCommand(ins, con);
numEffected_2 = crmd.ExecuteNonQuery();
}
if (numEffected_2 == 0)
lf.Main("UsersGroups", "No record was inserted to the table check if the group " + rdr.Group + " or the username " + rdr.Username + " exists ");
return numEffected + numEffected_2;
}
catch (Exception ex)
{
// write to log
lf.Main("Users", ex.Message);
return 0;
//return 0;
}
finally
{
if (con != null)
{
// close the db connection
con.Close();
}
}
}
//--------------------------------------------------------------------
// Build the Insert command String
//--------------------------------------------------------------------
private String BuildInsertRidersCommand(Rider rdr)
{
String command;
StringBuilder sb = new StringBuilder();
// use a string builder to create the dynamic string
String prefix = "INSERT INTO Users( UserEmail, City, UserDes, UserFname, UserLname, UserAddress, UserPhone, Gender, BicycleType, ImagePath, BirthDate, [CurDate], [Id], Captain, [Organization] ) ";
sb.AppendFormat("Values('{0}', (select [city] from Cities where CityName = '{1}' ), '{2}', '{3}' ,'{4}', '{5}','{6}','{7}', '{8}','{9}', '{10}', '{11}', (select id from AspNetUsers where UserName = '******'), {13}, (select Organization from Organizations where OrganizationDes = '{14}'))", rdr.RiderEmail, rdr.City, rdr.RiderDes, rdr.RiderFname, rdr.RiderLname, rdr.RiderAddress, rdr.RiderPhone, rdr.Gender, rdr.BicycleType, rdr.ImagePath, rdr.BirthDate, DateTime.Now.Date.ToString("yyyy-MM-dd"), rdr.Username, rdr.Captain, rdr.Organization);
command = prefix + sb.ToString();
return command;
}
private String BuildInsertRidersGroup(Rider rdr)
{
String command;
StringBuilder sb = new StringBuilder();
// use a string builder to create the dynamic string
String prefix1 = @"Declare @Group_val int;
Declare @User_val int;
Set @Group_val = 0;
Set @User_val = 0;
Set @Group_val = ( Select [Group] From Groups Where GroupName = '" + rdr.Group + @"');
Set @User_val = ( Select [User] From Users Where UserDes = '" + rdr.Username + @"' ) ;";
String prefix = @" if ( @Group_val <> 0 AND @User_val <> 0 )
begin
if exists ( select 'x' from [UsersGroups] Where [GROUP] = @Group_val And [USER] = 0 )
begin
UPDATE [UsersGroups]
SET [User] = @User_val
WHERE [Group] = @Group_val
And [User] = 0
end
else
INSERT INTO [UsersGroups]([Group],[User]) Values( @Group_val, @User_val )
UPDATE [Users]
SET
[GroupDes] = (Select g.GroupDes From Groups g, UsersGroups ug Where ug.[User] = Users.[User] And ug.[Group] = g.[group] )
,[OrganizationDes] = (Select OrganizationDes From Organizations Where Organization = Users.Organization)
,[CityName] = (Select CityName From Cities Where City = Users.City)
Where [Users].[User] = @User_val
end";
command = prefix1 + prefix;
return command;
}
private Rider screenToModel()
{
Rider rider = new Rider();
rider.RiderId = Convert.ToInt32(tbRiderId.Text.Trim());
rider.FamilyName = tbSurname.Text.Trim();
rider.GivenName = tbGivenName.Text.Trim();
rider.Phone = tbPhone.Text.Trim();
rider.Email = tbEmail.Text.Trim();
rider.Password = tbPass.Text.Trim();
rider.Username = tbUserName.Text.Trim();
rider.Gender = RadioButtonListGender.SelectedValue.ToString();
rider.ClubId = Convert.ToInt32(DropDownListClubName.SelectedValue.ToString());
rider.Role = RadioButtonListRole.SelectedValue.ToString();
return rider;
}
// Update An Existing Rider
public int updateRiderInDatabase(Rider rdr, string username, string new_cap_user = "")
{
SqlConnection con;
SqlCommand cmd, cmd1;
try
{
con = connect("DefaultConnection"); // create the connection
}
catch (Exception ex)
{
// write to log
lf.Main("Users", ex.Message);
return 0;
}
String cStr = (new_cap_user == "" ? BuildUpdateRiderCommand(rdr, username) : BuildUpdateCaptainCommand(username, new_cap_user)); // helper method to build the insert string
cmd = CreateCommand(cStr, con); // create the command
try
{
int numEffected = cmd.ExecuteNonQuery(); // execute the command
if (numEffected == 2 && new_cap_user == "")
{
String cStr1 = @"Update [Users] Set [Captain] = 1
Where UserDes = '" + username + @"'
AND not exists (SELECT 'x'
FROM Groups G, Organizations O, Users U
Where G.[group] <> 77
AND G.Groupname = '" + rdr.Group + @"'
AND G.Organization = O.Organization
AND O.OrganizationName = '" + rdr.Organization + @"'
AND U.Captain = 1
AND U.[User] in ( SELECT UG.[User]
FROM UsersGroups UG
WHERE G.[Group] = UG.[Group]) )";
cmd1 = CreateCommand(cStr1, con);
int numEffected1 = cmd1.ExecuteNonQuery();
}
return numEffected;
}
catch (Exception ex)
{
// write to log
lf.Main("Users", ex.Message);
return 0;
//return 0;
}
finally
{
if (con != null)
{
// close the db connection
con.Close();
}
}
}
private void DeleteRider(object sender, RoutedEventArgs e)
{
Rider r = (sender as Button).Tag as Rider;
r.Removed();
}
/*
* ***************
* RIDER DETAILS*
* ***************
* */
private void modelToScreen(Rider rider)
{
tbRiderId.Text = "" + rider.RiderId;
tbSurname.Text = "" + rider.FamilyName;
tbGivenName.Text = "" + rider.GivenName;
tbPhone.Text = "" + rider.Phone;
tbEmail.Text = "" + rider.Email;
tbUserName.Text = "" + rider.Username;
RadioButtonListGender.SelectedValue = "" + rider.Gender;
RadioButtonListRole.SelectedValue = "" + rider.Role;
DropDownListClubName.SelectedValue = "" + rider.ClubId;
}
public void OnEndId_WithMatchingTiming_ShouldCompleteLap(bool includeUnmatchedTime, bool includeUnmatchedId, bool flipEndEvents)
{
Beacon martijnBeacon = new Beacon(new byte[] { 0, 0, 0, 0, 0, 1 }, 0);
Rider martijn = new Rider("Martijn", martijnBeacon);
subject.AddRider(martijn);
Beacon richardBeacon = new Beacon(new byte[] { 0, 0, 0, 0, 0, 2 }, 0);
Rider richard = new Rider("Richard", richardBeacon);
subject.AddRider(richard);
//rider enters start box
startId.EmitIdEvent(martijn, new DateTime(2000, 1, 1, 1, 1, 1));
//rider triggers timing gate
timer.EmitTriggerEvent(100, "Timer", 0, new DateTime(2000, 1, 1, 1, 1, 2));
//somebody walks through end timing gate 10 secs after rider has started
if (includeUnmatchedTime)
{
timer.EmitTriggerEvent(400, "Timer", 1, new DateTime(2000, 1, 1, 1, 1, 12));
}
//a different rider gets too close to the stop box
if (includeUnmatchedId)
{
endId.EmitIdEvent(richard, new DateTime(2000, 1, 1, 1, 1, 30));
}
List <Action> endEvents = new List <Action>
{
//rider triggers id in stop box
() => endId.EmitIdEvent(martijn, new DateTime(2000, 1, 1, 1, 2, 1)),
//rider triggers timing in stop box
() => timer.EmitTriggerEvent(500, "Timer", 1, new DateTime(2000, 1, 1, 1, 2, 2))
};
if (flipEndEvents)
{
endEvents.Reverse();
}
foreach (Action a in endEvents)
{
a.Invoke();
}
source.Cancel();
RaceSummary summary = race.Result;
var state = subject.GetState;
FinishedEvent finish = race.Result.Events.Last() as FinishedEvent;
//Martijn should have done a lightning fast 400 microsecond lap
Assert.AreEqual("Martijn", finish.Rider.Name);
Assert.AreEqual(400L, finish.LapTime);
//There should be nothing going on in the race at this point
Assert.AreEqual(0, state.waiting.Count);
Assert.AreEqual(0, state.unmatchedIds.Count);
Assert.AreEqual(0, state.unmatchedTimes.Count);
Assert.AreEqual(0, state.onTrack.Count);
}
/// <summary>
/// Makes events for a lap finish at start + 1 minute
/// </summary>
/// <param name="riderName"></param>
/// <param name="sensorId"></param>
/// <param name="end"></param>
/// <param name="id"></param>
/// <param name="time"></param>
private void MakeEndEvents(Rider rider, DateTime end, MockRiderIdUnit id, MockTimingUnit time)
{
id.EmitIdEvent(rider, end);
time.EmitTriggerEvent(100, "Timer", 1, end);
id.EmitExitEvent(rider, end);
}
/// <summary>
/// Makes events for a lap begin at start
/// </summary>
/// <param name="riderName"></param>
/// <param name="sensorId"></param>
/// <param name="start"></param>
/// <param name="id"></param>
/// <param name="time"></param>
private void MakeStartEvents(Rider rider, DateTime start, MockRiderIdUnit id, MockTimingUnit time)
{
id.EmitIdEvent(rider, start);
time.EmitTriggerEvent(100, "Timer", 0, start);
id.EmitExitEvent(rider, start);
}
public object GetRideDetails(string rideId)
{
try
{
Ride ride = carpoolDb.Rides.FirstOrDefault(c => c.Id.Equals(rideId));
Driver driver = carpoolDb.Drivers.FirstOrDefault(c => c.Id.Equals(ride.DriverId));
ApplicationUser user = carpoolDb.ApplicationUsers.FirstOrDefault(c => c.Id.Equals(driver.ApplicationUserId));
if (ride != null)
{
Location source = carpoolDb.Locations.FirstOrDefault(c => c.Id.Equals(ride.SourceId));
Location destination = carpoolDb.Locations.FirstOrDefault(c => c.Id.Equals(ride.DestinationId));
List <Booking> bookings = carpoolDb.Bookings.Where(c => c.RideId.Equals(rideId) && c.BookingState.Equals(BookingState.Ongoing)).ToList();
List <RideRequest> requests = carpoolDb.RideRequests.Where(c => c.RideId.Equals(rideId) && c.Status.Equals(RequestState.Pending)).ToList();
List <Seat> seats = carpoolDb.Seats.Where(c => c.RideId.Equals(rideId) && c.State.Equals(SeatState.Free)).ToList();
RideDetailsResponse model = new RideDetailsResponse(
ride.Id,
user.Name,
source.Name,
destination.Name,
ride.Time,
Convert.ToString(ride.Date),
seats.Count(),
bookings.Count(),
requests.Count()
);
List <MatchResponse> bookingsResponse = new List <MatchResponse>();
List <MatchResponse> requestsResponse = new List <MatchResponse>();
foreach (Booking booking in bookings)
{
Location boardingPoint = carpoolDb.Locations.FirstOrDefault(c => c.Id.Equals(booking.BoardingPointId));
Location dropoffPoint = carpoolDb.Locations.FirstOrDefault(c => c.Id.Equals(booking.DropOffPointId));
Bill bill = carpoolDb.Bills.FirstOrDefault(c => c.Id.Equals(booking.BillId));
Rider rider = carpoolDb.Riders.FirstOrDefault(c => c.Id.Equals(booking.RiderId));
ApplicationUser userBooking = carpoolDb.ApplicationUsers.FirstOrDefault(c => c.Id.Equals(rider.ApplicationUserId));
MatchResponse data = new MatchResponse
{
Name = userBooking.Name,
Source = boardingPoint.Name,
Destination = dropoffPoint.Name,
Id = booking.Id,
Price = bill.TotalAmount()
};
bookingsResponse.Add(data);
}
foreach (RideRequest request in requests)
{
Location boardingPoint = carpoolDb.Locations.FirstOrDefault(c => c.Id.Equals(request.BoardingPointId));
Location dropoffPoint = carpoolDb.Locations.FirstOrDefault(c => c.Id.Equals(request.DropoffPointId));
Bill bill = carpoolDb.Bills.FirstOrDefault(c => c.Id.Equals(request.BillId));
Rider rider = carpoolDb.Riders.FirstOrDefault(c => c.Id.Equals(request.RiderId));
ApplicationUser userBooking = carpoolDb.ApplicationUsers.FirstOrDefault(c => c.Id.Equals(rider.ApplicationUserId));
MatchResponse data = new MatchResponse()
{
Name = userBooking.Name,
Source = boardingPoint.Name,
Destination = dropoffPoint.Name,
Id = request.Id,
Price = bill.TotalAmount()
};
requestsResponse.Add(data);
}
return(new { Status = 200, Ride = model, Bookings = bookingsResponse, Requests = requestsResponse });
}
return(null);
}
catch (Exception e)
{
return(null);
}
}
public static Payment deposit(Rider rider, decimal amount, string description)
{
Payment payment = new Payment();
//TO DO: lots of hard-coding here. Also this really should transact with the bank app rather
//than poking values into the database directly. At the momen the bank app does not support
//this sort of interaction
try
{
//first get the account and check it is the drurys branch
Customer holder = getCustomer(rider.bank_customer_id.Value);
Account destAccount = getAccount(rider.bank_account_id.Value);
//Check that the account belongs to the user. Remove if other branches are to be allowed.
//Also need to implement getBranch() and use dynamic data for branch info.
if (destAccount.holderId != holder.id)
{
throw new ApplicationException("Cannot pay into bank. User is not an account holder.");
}
Account ledger = getLedger(holder.branchId);
//hard coded check that were are using drurys branch ledger,
//remove this check if the app is to be used for other branches
if (ledger.id != DrurysLedgerId)
{
throw new ApplicationException("Attempt to post transaction to non-drurys branch");
}
//post the transaction to the bank database
postTran(ledger.id, destAccount.id, (int)Math.Floor(amount * 100), description);
payment.success = true;
try
{
//create a record of the transaction in the bikes database
payment.rider = rider.name;
payment.amount = amount;
payment.paid_date = DateTime.Now;
payment.bank_branch = "Drumtochty Branch";
payment.bank_username = holder.name;
payment.bank_account = destAccount.name;
payment.save();
}
catch
{
//still report success even if we have failed to log the payment, otherwise
//it will keep on getting paid again on any further retries
}
}
catch (Exception e)
{
payment.success = false;
}
return payment;
}
