public static void deleteAccount(userAccount accountToDelete)
{
database.sqlStatement deleteSql = new database.sqlStatement();
deleteSql.connectionString = database.getConnectString();
/* Delete the Child Cash Flows from every profile */
foreach (fundingProfile profileToDelete in accountToDelete.profiles)
{
deleteSql.query = "DELETE FROM bmw_cash_flow " +
"WHERE profile_id = @profile_id ";
deleteSql.queryParameters.Add("@profile_id", profileToDelete.id);
database.executeNonQueryOnDatabase(deleteSql);
deleteSql.queryParameters.Clear();
}
/* Delete the Profiles */
deleteSql.query = "DELETE FROM bmw_funding_profile " +
"WHERE account_id = @account_id ";
deleteSql.queryParameters.Add("@account_id", accountToDelete.id);
database.executeNonQueryOnDatabase(deleteSql);
deleteSql.queryParameters.Clear();
/* Finally Delete the Account */
deleteSql.query = "DELETE FROM bmw_user_account " +
"WHERE id = @id ";
deleteSql.queryParameters.Add("@id", accountToDelete.id);
database.executeNonQueryOnDatabase(deleteSql);
}
/// <summary>
/// Validates the username to create.
/// A username is valid if it is unique, in the sense that
/// it does NOT already exist in the database.
/// </summary>
/// <param name="newUsername"></param>
/// <returns></returns>
public static bool validateNewUsername(string newUsername)
{
if (string.IsNullOrEmpty(newUsername))
{
/* no empty usernames */
return(false);
}
database.sqlStatement sql = new database.sqlStatement();
sql.connectionString = database.getConnectString();
sql.query = "SELECT DISTINCT COUNT(ua.id) " +
"FROM bmw_user_account ua " +
"WHERE ua.username = @username ";
sql.queryParameters.Add("@username", newUsername);
if (int.Parse(database.executeScalarOnDatabase(sql).ToString()) == 0)
{
return(true);
}
else
{
return(false);
}
}
public static void deleteProfileFromAccount(userAccount owningAccount, fundingProfile profileToDelete)
{
owningAccount.profiles.Remove(profileToDelete);
//Database stuff
database.sqlStatement deleteSql = new database.sqlStatement();
deleteSql.connectionString = database.getConnectString();
/* Delete the Child Cash Flows */
deleteSql.query = "DELETE FROM bmw_cash_flow " +
"WHERE profile_id = @profile_id ";
deleteSql.queryParameters.Add("@profile_id", profileToDelete.id);
database.executeNonQueryOnDatabase(deleteSql);
deleteSql.queryParameters.Clear();
/* Delete the Profile */
deleteSql.query = "DELETE FROM bmw_funding_profile " +
"WHERE id = @id ";
deleteSql.queryParameters.Add("@id", profileToDelete.id);
database.executeNonQueryOnDatabase(deleteSql);
}
/*Cash Flows*/
public static int getNextCashFlowId()
{
database.sqlStatement selectSql = new database.sqlStatement();
selectSql.connectionString = database.getConnectString();
selectSql.query = "SELECT MAX(id) + 1 " +
"FROM bmw_cash_flow ";
return(int.Parse(database.executeScalarOnDatabase(selectSql).ToString()));
}
/// <summary>
/// gets the "rememberance token" for a given account
/// </summary>
/// <param name="accountId"></param>
/// <returns></returns>
public static string getRememberMeToken(int accountId)
{
database.sqlStatement selectSql = new database.sqlStatement();
selectSql.connectionString = database.getConnectString();
selectSql.query = "SELECT SHA1(CONCAT(ua.id, ua.username)) " +
"FROM bmw_user_account ua " +
"WHERE ua.id = @id ";
selectSql.queryParameters.Add("@id", accountId);
return(database.executeScalarOnDatabase(selectSql).ToString());
}
private bool importCashFlowsIntoProfile(DataTable csvData, fundingProfile profileToRecieveFlows)
{
List <cashFlow> csvFlows = new List <cashFlow>();
foreach (DataRow row in csvData.Rows)
{
csvFlows.Add(new cashFlow(common.getNextCashFlowId(),
row["flow_name"].ToString(),
float.Parse(row["amount"].ToString()),
DateTime.Parse(row["due_date"].ToString()),
DateTime.Parse(row["transaction_date"].ToString()),
common.getCashFlowTypeByName(row["flow_type"].ToString())));
}
//Database stuff
database.sqlStatement insertSql = new database.sqlStatement();
insertSql.connectionString = database.getConnectString();
insertSql.query = "INSERT INTO bmw_cash_flow " +
"(profile_id,flow_name,flow_type,amount,transaction_date,due_date) " +
"VALUES "; //+
//"(@id,@profile_id,@flow_name,@flow_type,@amount,@transaction_date,@due_date) ";
List <string> values = new List <string>();
foreach (cashFlow flow in csvFlows)
{
values.Add("(@profile_id" + csvFlows.IndexOf(flow) + "," +
"@flow_name" + csvFlows.IndexOf(flow) + "," +
"@flow_type" + csvFlows.IndexOf(flow) + "," +
"@amount" + csvFlows.IndexOf(flow) + "," +
"@transaction_date" + csvFlows.IndexOf(flow) + "," +
"@due_date" + csvFlows.IndexOf(flow) + ") ");
//insertSql.queryParameters.Add("@id" + csvFlows.IndexOf(flow), flow.id);
insertSql.queryParameters.Add("@profile_id" + csvFlows.IndexOf(flow), profileToRecieveFlows.id);
insertSql.queryParameters.Add("@flow_name" + csvFlows.IndexOf(flow), flow.name);
insertSql.queryParameters.Add("@flow_type" + csvFlows.IndexOf(flow), flow.flowType);
insertSql.queryParameters.Add("@amount" + csvFlows.IndexOf(flow), flow.amount);
insertSql.queryParameters.Add("@transaction_date" + csvFlows.IndexOf(flow), flow.flowDate);
insertSql.queryParameters.Add("@due_date" + csvFlows.IndexOf(flow), flow.dueDate);
}
insertSql.query += string.Join(",", values);
database.executeNonQueryOnDatabase(insertSql);
profileToRecieveFlows.cashFlows.AddRange(csvFlows);
return(true);
}
public static void deleteCashFlowFromProfile(fundingProfile owningProfile, cashFlow flowToDelete)
{
owningProfile.cashFlows.Remove(flowToDelete);
//Database stuff
database.sqlStatement deleteSql = new database.sqlStatement();
deleteSql.connectionString = database.getConnectString();
deleteSql.query = "DELETE FROM bmw_cash_flow " +
"WHERE id = @id ";
deleteSql.queryParameters.Add("@id", flowToDelete.id);
database.executeNonQueryOnDatabase(deleteSql);
}
public static void updateAccount(userAccount oldAccount, userAccount updatedAccount)
{
//TODO
database.sqlStatement updateSql = new database.sqlStatement();
updateSql.connectionString = database.getConnectString();
updateSql.query = "UPDATE bmw_user_account " +
"SET username = @username " +
"WHERE id = @id ";
updateSql.queryParameters.Add("@id", oldAccount.id);
updateSql.queryParameters.Add("@username", updatedAccount.name);
database.executeNonQueryOnDatabase(updateSql);
}
private int getAccountId(string username, string password)
{
database.sqlStatement sql = new database.sqlStatement();
sql.connectionString = database.getConnectString();
sql.query = "SELECT DISTINCT ua.id " +
"FROM bmw_user_account ua " +
"WHERE ua.username = @username " +
"AND ua.password = @password";
sql.queryParameters.Add("@username", username);
sql.queryParameters.Add("@password", password);
return(int.Parse(database.executeScalarOnDatabase(sql).ToString()));
}
public static void updateProfileOnAccount(userAccount owningAccount, fundingProfile oldProfile, fundingProfile updatedProfile)
{
replaceItemInList(owningAccount.profiles, oldProfile, updatedProfile);
//Database stuff
database.sqlStatement updateSql = new database.sqlStatement();
updateSql.connectionString = database.getConnectString();
updateSql.query = "UPDATE bmw_funding_profile " +
"SET profile_name = @profile_name " +
"WHERE id = @id ";
updateSql.queryParameters.Add("@id", oldProfile.id);
updateSql.queryParameters.Add("@profile_name", updatedProfile.name);
database.executeNonQueryOnDatabase(updateSql);
}
public static void createNewAccount(userAccount accountToCreate, string password)
{
//Database stuff
database.sqlStatement insertSql = new database.sqlStatement();
insertSql.connectionString = database.getConnectString();
insertSql.query = "INSERT INTO bmw_user_account " +
"(username,password,email) " +
"VALUES " +
"(@username,@password,@email)";
insertSql.queryParameters.Add("@username", accountToCreate.name);
insertSql.queryParameters.Add("@password", password);
insertSql.queryParameters.Add("@email", null);
database.executeNonQueryOnDatabase(insertSql);
}
public static void addProfileToAccount(userAccount accountToAddProfile, fundingProfile profileToAdd)
{
accountToAddProfile.profiles.Add(profileToAdd);
//Database stuff
database.sqlStatement insertSql = new database.sqlStatement();
insertSql.connectionString = database.getConnectString();
insertSql.query = "INSERT INTO bmw_funding_profile " +
"(id,account_id,profile_name) " +
"VALUES " +
"(@id, @account_id, @profile_name) ";
insertSql.queryParameters.Add("@id", profileToAdd.id);
insertSql.queryParameters.Add("@account_id", accountToAddProfile.id);
insertSql.queryParameters.Add("@profile_name", profileToAdd.name);
database.executeNonQueryOnDatabase(insertSql);
}
private int?getAccountId(string rememberMeToken)
{
database.sqlStatement sql = new database.sqlStatement();
sql.connectionString = database.getConnectString();
sql.query = "SELECT DISTINCT ua.id " +
"FROM bmw_user_account ua " +
"WHERE SHA1(CONCAT(ua.id, ua.username)) = @token";
sql.queryParameters.Add("@token", rememberMeToken);
if (database.executeScalarOnDatabase(sql) == null)
{
return(null);
}
else
{
return(int.Parse(database.executeScalarOnDatabase(sql).ToString()));
}
}
/// <summary>
///
/// </summary>
/// <param name="accountId"></param>
/// <returns></returns>
public static List <fundingProfile> getProfilesForAccount(int accountId)
{
database.sqlStatement sql = new database.sqlStatement();
sql.connectionString = database.getConnectString();
sql.query = "SELECT fp.id, fp.profile_name " +
"FROM bmw_funding_profile fp " +
"WHERE fp.account_id = @account_id ";
sql.queryParameters.Add("@account_id", accountId);
List <fundingProfile> profiles = new List <fundingProfile>();
foreach (System.Data.DataRow row in database.selectFromDatabase(sql).Rows)
{
profiles.Add(new fundingProfile(int.Parse(row["id"].ToString()),
row["profile_name"].ToString(), getCashFlowsForProfile(int.Parse(row["id"].ToString()))));
}
return(profiles);
}
/// <summary>
///
/// </summary>
/// <param name="accountId"></param>
/// <returns></returns>
public static userAccount getAccountFromDatabase(int accountId)
{
database.sqlStatement sql = new database.sqlStatement();
sql.connectionString = database.getConnectString();
sql.query = "SELECT ua.id, ua.username " +
"FROM bmw_user_account ua " +
"WHERE ua.id = @account_id ";
sql.queryParameters.Add("@account_id", accountId);
foreach (System.Data.DataRow row in database.selectFromDatabase(sql).Rows)
{
return(new userAccount(int.Parse(row["id"].ToString()),
row["username"].ToString(),
getProfilesForAccount(int.Parse(row["id"].ToString()))));
}
return(null);
}
public static void addCashFlowToProfile(fundingProfile profileToRecieveFlow, cashFlow flowToAdd)
{
profileToRecieveFlow.cashFlows.Add(flowToAdd);
//Database stuff
database.sqlStatement insertSql = new database.sqlStatement();
insertSql.connectionString = database.getConnectString();
insertSql.query = "INSERT INTO bmw_cash_flow " +
"(id,profile_id,flow_name,flow_type,amount,transaction_date,due_date) " +
"VALUES " +
"(@id,@profile_id,@flow_name,@flow_type,@amount,@transaction_date,@due_date) ";
insertSql.queryParameters.Add("@id", flowToAdd.id);
insertSql.queryParameters.Add("@profile_id", profileToRecieveFlow.id);
insertSql.queryParameters.Add("@flow_name", flowToAdd.name);
insertSql.queryParameters.Add("@flow_type", flowToAdd.flowType);
insertSql.queryParameters.Add("@amount", flowToAdd.amount);
insertSql.queryParameters.Add("@transaction_date", flowToAdd.flowDate);
insertSql.queryParameters.Add("@due_date", flowToAdd.dueDate);
database.executeNonQueryOnDatabase(insertSql);
}
/// <summary>
/// Runs the passed user/pass combo against the database
/// returns a boolean if it exists
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
private bool isValidLogin(string username, string password)
{
database.sqlStatement sql = new database.sqlStatement();
sql.connectionString = database.getConnectString();
sql.query = "SELECT DISTINCT COUNT(ua.id) " +
"FROM bmw_user_account ua " +
"WHERE ua.username = @username " +
"AND ua.password = @password";
sql.queryParameters.Add("@username", username);
sql.queryParameters.Add("@password", password);
if (int.Parse(database.executeScalarOnDatabase(sql).ToString()) > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="profileId"></param>
/// <returns></returns>
public static List <cashFlow> getCashFlowsForProfile(int profileId)
{
database.sqlStatement sql = new database.sqlStatement();
sql.connectionString = database.getConnectString();
sql.query = "SELECT cf.id, cf.flow_name, cf.flow_type, " +
"cf.amount, cf.transaction_date, cf.due_date " +
"FROM bmw_cash_flow cf " +
"WHERE cf.profile_id = @profile_id ";
sql.queryParameters.Add("@profile_id", profileId);
List <cashFlow> cashFlows = new List <cashFlow>();
foreach (System.Data.DataRow row in database.selectFromDatabase(sql).Rows)
{
DateTime?dueDate;
if (string.IsNullOrEmpty(row["due_date"].ToString()))
{
dueDate = null;
}
else
{
dueDate = DateTime.Parse(row["due_date"].ToString());
}
cashFlows.Add(new cashFlow(int.Parse(row["id"].ToString()),
row["flow_name"].ToString(),
double.Parse(row["amount"].ToString()),
dueDate,
DateTime.Parse(row["transaction_date"].ToString()),
cashFlowType.income));
}
return(cashFlows);
}
public static void updateCashFlowOnAccount(fundingProfile owningProfile, cashFlow oldFlow, cashFlow updatedFlow)
{
replaceItemInList(owningProfile.cashFlows, oldFlow, updatedFlow);
//Database stuff
database.sqlStatement updateSql = new database.sqlStatement();
updateSql.connectionString = database.getConnectString();
updateSql.query = "UPDATE bmw_cash_flow " +
"SET flow_name = @flow_name, " +
"flow_type = @flow_type, " +
"amount = @amount, " +
"transaction_date = @transaction_date, " +
"due_date = @due_date " +
"WHERE id = @id ";
updateSql.queryParameters.Add("@id", oldFlow.id);
updateSql.queryParameters.Add("@flow_name", updatedFlow.name);
updateSql.queryParameters.Add("@flow_type", updatedFlow.flowType);
updateSql.queryParameters.Add("@amount", updatedFlow.amount);
updateSql.queryParameters.Add("@transaction_date", updatedFlow.flowDate);
updateSql.queryParameters.Add("@due_date", updatedFlow.dueDate);
database.executeNonQueryOnDatabase(updateSql);
}
