// 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);
}