// Load 1 trip public static TripModel LoadSingleTrip(int IDToFind) { TripModel result = new TripModel(); string sqlString = $"SELECT * FROM TRIP WHERE ID = {IDToFind}"; using (var cnn = new SQLiteConnection(LoadConnectionString())) { TripModel output = cnn.QueryFirst <TripModel>(sqlString, new DynamicParameters()); result = output; string getAllMemberSql = $"SELECT MEMBER.*, MONEYPAID " + $"FROM MEMBER JOIN MEMBERJOINTRIP ON MEMBER.ID = MEMBERJOINTRIP.MEMBERID" + $" WHERE TRIPID = {IDToFind} "; List <MemberInTripModel> outputMemberList = cnn.Query <MemberInTripModel>(getAllMemberSql, new DynamicParameters()).ToList(); result.memberList = outputMemberList; string getAllExpenseSql = $"SELECT * FROM EXPENSE WHERE TRIPID = {IDToFind} "; List <ExpenseModel> outputExpenseList = cnn.Query <ExpenseModel>(getAllExpenseSql, new DynamicParameters()).ToList(); result.expensesList = outputExpenseList; string getAllPlaceSql = $"SELECT * FROM PLACE WHERE TRIPID = {IDToFind}"; List <PlaceModel> outputPlaceList = cnn.Query <PlaceModel>(getAllPlaceSql, new DynamicParameters()).ToList(); result.placeList = outputPlaceList; } return(result); }
// Add an empty trip (without name only) into database and return TripModel public static TripModel AddEmptyTrip(string tripName) { TripModel temp = new TripModel { Name = tripName }; TripModel result = new TripModel(); using (var cnn = new SQLiteConnection(LoadConnectionString())) { // insert string sqlStringInsert = "INSERT INTO TRIP(NAME,ISFINISHED) VALUES (@Name,0)"; int x = cnn.Execute(sqlStringInsert, temp); // load int currentMaxTripID = cnn.QueryFirst <int>("SELECT IFNULL(MAX(ID), 0) FROM TRIP"); result = LoadSingleTrip(currentMaxTripID); }; return(result); }
public static TripModel AddNewTrip(string tripName, List <MemberInTripModel> memberInTripModels, List <ExpenseModel> expenseModels, List <PlaceModel> placeModels) { TripModel initializedTrip = AddEmptyTrip(tripName); // Add member foreach (var memberInTrip in memberInTripModels) { bool isNewMember = (memberInTrip.ID < 0); if (isNewMember) { initializedTrip.AddNewMemberToTrip(new MemberModel { Name = memberInTrip.Name }, memberInTrip.MoneyPaid); } else { initializedTrip.AddAlreadyExistedMember(new MemberModel { ID = memberInTrip.ID, Name = memberInTrip.Name }, memberInTrip.MoneyPaid); } } // Add expense foreach (var expense in expenseModels) { initializedTrip.AddExpense(expense); } // Add place foreach (var place in placeModels) { initializedTrip.AddPlace(place); } return(initializedTrip); }