Пример #1
0
        public void Delete(params int[] idsToDelete)
        {
            if (idsToDelete.Count() == 0)
            {
                return;
            }

            StringBuilder sqlCommandBuilder = new StringBuilder("DELETE FROM user_details WHERE id IN (");

            for (int i = 0; i < idsToDelete.Count(); i++)
            {
                sqlCommandBuilder.Append(idsToDelete[i]);
                if (i != idsToDelete.Count() - 1)
                {
                    sqlCommandBuilder.Append(", ");
                }
                else
                {
                    sqlCommandBuilder.Append(")");
                }
            }

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
            }
        }
Пример #2
0
        public List <BlackList> GetBlackLists(int?userId = null, bool?banned = null, int?id = null)
        {
            StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM public.black_list");

            if (id != null)
            {
                sqlCommandBuilder.Append(" WHERE id=" + id);
            }
            else if (userId != null && banned != null)
            {
                sqlCommandBuilder.Append(String.Format(" WHERE user_id='{0}'", userId));
            }
            else if (userId != null)
            {
                sqlCommandBuilder.Append(" WHERE user_id=" + userId);
            }
            else if (banned != null)
            {
                sqlCommandBuilder.Append(" WHERE banned=" + (banned.Value ? "1" : "0"));
            }


            List <BlackList> userDetailsList = new List <BlackList>();

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);

                BlackList blackListToAdd = null;

                try
                {
                    while (reader.Read())
                    {
                        blackListToAdd = new BlackList()
                        {
                            Id       = Convert.ToInt32(reader["id"].ToString()),
                            Warnings = Convert.ToInt32(reader["warnings"].ToString()),
                            Banned   = reader["banned"].ToString().Equals("1"),
                            UserId   = Convert.ToInt32(reader["user_id"].ToString()),
                        };

                        userDetailsList.Add(blackListToAdd);
                    }
                }
                catch (FormatException ex)
                {
                    DebugLog.WriteLine(ex);
                }
                finally
                {
                    reader.Close();
                }
            }

            return(userDetailsList);
        }
Пример #3
0
        public void Update(BlackList blackList)
        {
            string sqlCommand = String.Format("UPDATE public.black_list SET warnings='{0}', banned=b'{1}', user_id='{2}' WHERE id='{3}'",
                                              blackList.Warnings, blackList.Banned ? "1" : "0", blackList.UserId, blackList.Id);

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DBUtils.ExecuteCommand(sqlCommand, connection);
            }
        }
Пример #4
0
        public void Update(User user)
        {
            string sqlCommand = String.Format("UPDATE public.user SET username='******', password='******', name='{2}', surname='{3}', user_details_id='{4}'," +
                                              "function='{5}' WHERE id='{6}'", user.Username, user.Password, user.Name, user.Surname,
                                              user.UserDetailsId, user.UserFunction, user.Id);

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DBUtils.ExecuteCommand(sqlCommand, connection);
            }
        }
Пример #5
0
        public List <UserDetails> GetUserDetails(int?userId = null, int?id = null)
        {
            StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM user_details");

            if (id != null)
            {
                sqlCommandBuilder.Append(" WHERE id=" + id);
            }
            else if (userId != null)
            {
                sqlCommandBuilder.Append(String.Format(" WHERE user_id='{0}'", userId));
            }

            List <UserDetails> damageList = new List <UserDetails>();

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);

                UserDetails userdetailsToAdd = null;

                try
                {
                    while (reader.Read())
                    {
                        userdetailsToAdd = new UserDetails()
                        {
                            Id        = Convert.ToInt32(reader["id"].ToString()),
                            Email     = reader["email"].ToString(),
                            Street    = reader["street"].ToString(),
                            City      = reader["city"].ToString(),
                            ZipCode   = reader["zipcode"].ToString(),
                            Country   = reader["country"].ToString(),
                            Premium   = reader["premium"].ToString().Equals("1"),
                            UserId    = Convert.ToInt32(reader["user_id"].ToString()),
                            BirthDate = Convert.ToDateTime(reader["birth_date"].ToString())
                        };

                        damageList.Add(userdetailsToAdd);
                    }
                }
                catch (FormatException ex)
                {
                    DebugLog.WriteLine(ex);
                }
                finally
                {
                    reader.Close();
                }
            }

            return(damageList);
        }
Пример #6
0
        public void Update(UserDetails userDetails)
        {
            string sqlCommand = String.Format("UPDATE user_details SET email='{0}', street='{1}', city='{2}', zipcode='{3}', country='{4}'," +
                                              "premium=b'{5}', user_id='{6}', birth_date='{7}' WHERE id='{8}'",
                                              userDetails.Email, userDetails.Street, userDetails.City, userDetails.ZipCode, userDetails.Country,
                                              userDetails.Premium ? "1" : "0", userDetails.UserId, userDetails.BirthDate.ToShortDateString(), userDetails.Id);

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DBUtils.ExecuteCommand(sqlCommand, connection);
            }
        }
Пример #7
0
        public void Update(BankAccount bankAccount)
        {
            string sqlCommand = String.Format("UPDATE public.bank_account SET security_number='{0}', card_type='{1}', bank_name='{2}'," +
                                              " user_id='{3}', iban='{4}', expiry_date='{5}' WHERE id='{6}'",
                                              bankAccount.SecurityNumber, bankAccount.CardType, bankAccount.BankName, bankAccount.UserId,
                                              bankAccount.Iban, bankAccount.ExpiryDate.ToShortDateString(), bankAccount.Id);

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DBUtils.ExecuteCommand(sqlCommand, connection);
            }
        }
Пример #8
0
        public List <BankAccount> GetBankAccounts(int?userId = null, int?id = null)
        {
            StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM public.bank_account");

            if (id != null)
            {
                sqlCommandBuilder.Append(" WHERE id=" + id);
            }
            else if (userId != null)
            {
                sqlCommandBuilder.Append(" WHERE user_id=" + userId);
            }

            List <BankAccount> bankAccountList = new List <BankAccount>();

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);

                BankAccount bankAccountToAdd = null;

                try
                {
                    while (reader.Read())
                    {
                        bankAccountToAdd = new BankAccount()
                        {
                            Id             = Convert.ToInt32(reader["id"].ToString()),
                            SecurityNumber = Convert.ToInt32(reader["security_number"].ToString()),
                            CardType       = (BankAccount.CardTypes)Enum.Parse(typeof(BankAccount.CardTypes), reader["card_type"].ToString()),
                            BankName       = reader["bank_name"].ToString(),
                            Iban           = reader["iban"].ToString(),
                            ExpiryDate     = Convert.ToDateTime(reader["expiry_date"].ToString()),
                            UserId         = Convert.ToInt32(reader["user_id"].ToString()),
                        };

                        bankAccountList.Add(bankAccountToAdd);
                    }
                }
                catch (FormatException ex)
                {
                    DebugLog.WriteLine(ex);
                }
                finally
                {
                    reader.Close();
                }
            }

            return(bankAccountList);
        }
Пример #9
0
        public void Insert(User user)
        {
            string sqlCommand = String.Format("INSERT INTO public.user (username, password, name, surname, user_details_id, function)" +
                                              "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}') RETURNING id",
                                              user.Username, user.Password, user.Name, user.Surname, user.UserDetailsId, user.UserFunction);

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);

                if (reader.Read())
                {
                    int id = -1;
                    int.TryParse(reader.GetValue(0).ToString(), out id);
                    user.Id = id;
                }
            }
        }
Пример #10
0
        public void Insert(BlackList blackList)
        {
            string sqlCommand = String.Format("INSERT INTO public.black_list (warnings, banned, user_id)" +
                                              "VALUES ('{0}',b'{1}','{2}') RETURNING id",
                                              blackList.Warnings, blackList.Banned ? "1" : "0", blackList.UserId);

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);

                if (reader.Read())
                {
                    int id = -1;
                    int.TryParse(reader.GetValue(0).ToString(), out id);
                    blackList.Id = id;
                }
            }
        }
Пример #11
0
        public void Insert(UserDetails userDetails)
        {
            string sqlCommand = String.Format("INSERT INTO user_details (email, street, city, zipcode, country, premium, user_id, birth_date)" +
                                              "VALUES ('{0}','{1}','{2}','{3}','{4}',b'{5}','{6}','{7}') RETURNING id",
                                              userDetails.Email, userDetails.Street, userDetails.City, userDetails.ZipCode, userDetails.Country,
                                              userDetails.Premium ? "1" : "0", userDetails.UserId, userDetails.BirthDate.ToShortDateString());

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);

                if (reader.Read())
                {
                    int id = -1;
                    int.TryParse(reader.GetValue(0).ToString(), out id);
                    userDetails.Id = id;
                }
            }
        }
Пример #12
0
        public void Insert(BankAccount bankAccount)
        {
            string sqlCommand = String.Format("INSERT INTO public.bank_account (security_number, card_type, bank_name, user_id, iban, expiry_date)" +
                                              "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}') RETURNING id",
                                              bankAccount.SecurityNumber, bankAccount.CardType, bankAccount.BankName, bankAccount.UserId,
                                              bankAccount.Iban, bankAccount.ExpiryDate.ToShortDateString());

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);

                if (reader.Read())
                {
                    int id = -1;
                    int.TryParse(reader.GetValue(0).ToString(), out id);
                    bankAccount.Id = id;
                }
            }
        }
Пример #13
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="username"></param>
        /// <param name="function"></param>
        /// <param name="id"></param>
        /// <returns>empty list if nothing in database</returns>
        public List <User> GetUsers(string username = null, string function = null, int?id = null)
        {
            StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM public.user");

            if (id != null)
            {
                sqlCommandBuilder.Append(" WHERE id=" + id);
            }
            else if (function != null)
            {
                sqlCommandBuilder.Append(String.Format(" WHERE function = '{0}'", function));
            }
            else if (username != null)
            {
                sqlCommandBuilder.Append(String.Format(" WHERE username = '******'", username));
            }


            List <User> userList = new List <User>();

            using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
            {
                DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);

                User userToAdd = null;

                try
                {
                    while (reader.Read())
                    {
                        userToAdd = new User()
                        {
                            Id            = Convert.ToInt32(reader["id"].ToString()),
                            Username      = reader["username"].ToString(),
                            Password      = reader["password"].ToString(),
                            Name          = reader["name"].ToString(),
                            Surname       = reader["surname"].ToString(),
                            UserFunction  = (User.Function)Enum.Parse(typeof(User.Function), reader["function"].ToString()),
                            UserDetailsId = Convert.ToInt32(reader["user_details_id"].ToString()),
                        };
                        try
                        {
                            userToAdd.UserDetails = new UserDetailsDAO().GetUserDetails(userId: userToAdd.Id).First();
                        }
                        catch (Exception)
                        {
                            DebugLog.WriteLine("Exception didnt find userdetails for user with id " + userToAdd.Id);
                        }

                        userList.Add(userToAdd);
                    }
                }
                catch (FormatException ex)
                {
                    DebugLog.WriteLine(ex);
                }
                finally
                {
                    reader.Close();
                }
            }

            return(userList);
        }