示例#1
0
    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);
    }
示例#2
0
    /// <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);
        }
    }
示例#3
0
    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);
    }
示例#4
0
    /*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()));
    }
示例#5
0
    /// <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());
    }
示例#6
0
        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);
        }
示例#7
0
    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);
    }
示例#8
0
    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);
    }
示例#9
0
        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()));
        }
示例#10
0
    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);
    }
示例#11
0
    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);
    }
示例#12
0
    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);
    }
示例#13
0
        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()));
            }
        }
示例#14
0
    /// <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);
    }
示例#15
0
    /// <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);
    }
示例#16
0
    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);
    }
示例#17
0
        /// <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);
            }
        }
示例#18
0
    /// <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);
    }
示例#19
0
    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);
    }