public static void DeleteSupplier(int p_SupplierId)
        {
            SQLiteConnection con     = Connection.GetConnection();
            SQLiteCommand    command = new SQLiteCommand(con);

            command.CommandText = string.Format(
                "SELECT {0} FROM {1} " +
                "WHERE {2} = @2",
                ToolConstants.DB_SUPPLIER_ADRESS_ID,
                ToolConstants.DB_SUPPLIER_TABLE,
                ToolConstants.DB_SUPPLIER_ID);
            command.Parameters.Add("@2", System.Data.DbType.Int32).Value = p_SupplierId;
            SQLiteDataReader reader = command.ExecuteReader();

            if (reader.Read())
            {
                int adressId = reader.GetInt32(0);
                AdressMapper.DeleteAdress(adressId);
            }

            command.CommandText = string.Format(
                "DELETE FROM {0} " +
                "WHERE {1} = @1",
                ToolConstants.DB_SUPPLIER_TABLE,
                ToolConstants.DB_SUPPLIER_ID);

            command.Parameters.Add("@1", System.Data.DbType.Int32).Value = p_SupplierId;

            command.ExecuteNonQuery();
        }
        public static Supplier GetSupplierById(int p_SupplierId)
        {
            SQLiteConnection con     = Connection.GetConnection();
            SQLiteCommand    command = new SQLiteCommand(con);

            command.CommandText = string.Format(
                "SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7} FROM {8}" +
                "WHERE {0} = @0",
                ToolConstants.DB_SUPPLIER_ID,
                ToolConstants.DB_SUPPLIER_LAST_NAME,
                ToolConstants.DB_SUPPLIER_FIRST_NAME,
                ToolConstants.DB_SUPPLIER_ADRESS_ID,
                ToolConstants.DB_SUPPLIER_BIRTHDAY,
                ToolConstants.DB_SUPPLIER_PHONE,
                ToolConstants.DB_SUPPLIER_COMPANY,
                ToolConstants.DB_SUPPLIER_EMAIL,
                ToolConstants.DB_SUPPLIER_TABLE);
            command.Parameters.Add("@0", System.Data.DbType.Int32).Value = p_SupplierId;

            SQLiteDataReader reader = command.ExecuteReader();

            if (reader.Read())
            {
                Supplier result = new Supplier();

                result.Id        = Convert.ToInt32(reader.GetValue(0));
                result.LastName  = Convert.ToString(reader.GetValue(1));
                result.FirstName = Convert.ToString(reader.GetValue(2));
                result.Adress    = AdressMapper.GetAdressById(reader.GetInt32(3));
                result.Birthday  = Convert.ToDateTime(reader.GetValue(4));
                result.Phone     = Convert.ToString(reader.GetValue(5));
                result.Company   = Convert.ToString(reader.GetValue(6));
                result.Email     = Convert.ToString(reader.GetValue(7));

                return(result);
            }
            else
            {
                return(null);
            }
        }
Example #3
0
        public static ObservableCollection <Producer> GetAllProducers()
        {
            ObservableCollection <Producer> result =
                new ObservableCollection <Producer>();

            SQLiteConnection con     = Connection.GetConnection();
            SQLiteCommand    command = new SQLiteCommand(con);

            //Build SQLite query
            command.CommandText = string.Format(
                "SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7} FROM {8}",
                ToolConstants.DB_PRODUCER_ID,
                ToolConstants.DB_PRODUCER_LAST_NAME,
                ToolConstants.DB_PRODUCER_FIRST_NAME,
                ToolConstants.DB_PRODUCER_ADRESS_ID,
                ToolConstants.DB_PRODUCER_BIRTHDAY,
                ToolConstants.DB_PRODUCER_PHONE,
                ToolConstants.DB_PRODUCER_COMPANY,
                ToolConstants.DB_PRODUCER_EMAIL,
                ToolConstants.DB_PRODUCER_TABLE);
            SQLiteDataReader reader = command.ExecuteReader();

            //Read result
            while (reader.Read())
            {
                Producer producer = new Producer();

                producer.Id        = Convert.ToInt32(reader.GetValue(0));
                producer.LastName  = Convert.ToString(reader.GetValue(1));
                producer.FirstName = Convert.ToString(reader.GetValue(2));
                producer.Adress    = AdressMapper.GetAdressById(reader.GetInt32(3));
                producer.Birthday  = Convert.ToDateTime(reader.GetValue(4));
                producer.Phone     = Convert.ToString(reader.GetValue(5));
                producer.Company   = Convert.ToString(reader.GetValue(6));
                producer.Email     = Convert.ToString(reader.GetValue(7));

                result.Add(producer);
            }
            return(result);
        }
        /// <summary>
        /// Saves an supplier to a database. If the supplier already exists,
        /// its values are updated. This function also calls the function
        /// for saving the suppliers adress.
        /// </summary>
        /// <param name="p_Supplier">Supplier to save</param>
        public static void SaveSupplier(Supplier p_Supplier)
        {
            SQLiteConnection con     = Connection.GetConnection();
            SQLiteCommand    command = new SQLiteCommand(con);

            bool alreadyExists = false;

            command.CommandText = string.Format(
                "SELECT {0} FROM {1} " +
                "WHERE {0} = @0",
                ToolConstants.DB_SUPPLIER_ID,
                ToolConstants.DB_SUPPLIER_TABLE);

            command.Parameters.Add("@0", System.Data.DbType.Int32).Value = p_Supplier.Id;

            SQLiteDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                alreadyExists = true;
            }

            reader.Close();

            if (alreadyExists) //update values
            {
                command.CommandText = string.Format(
                    "UPDATE {0} SET " +
                    "{1} = @1, " +
                    "{2} = @2, " +
                    "{3} = @3, " +
                    "{4} = @4, " +
                    "{5} = @5, " +
                    "{6} = @6, " +
                    "{7} = @7 " +
                    "WHERE {8} = @8",
                    ToolConstants.DB_SUPPLIER_TABLE,
                    ToolConstants.DB_SUPPLIER_LAST_NAME,
                    ToolConstants.DB_SUPPLIER_FIRST_NAME,
                    ToolConstants.DB_SUPPLIER_ADRESS_ID,
                    ToolConstants.DB_SUPPLIER_BIRTHDAY,
                    ToolConstants.DB_SUPPLIER_PHONE,
                    ToolConstants.DB_SUPPLIER_COMPANY,
                    ToolConstants.DB_SUPPLIER_EMAIL,
                    ToolConstants.DB_SUPPLIER_ID);

                command.Parameters.Add("@1", System.Data.DbType.String).Value   = p_Supplier.LastName;
                command.Parameters.Add("@2", System.Data.DbType.String).Value   = p_Supplier.FirstName;
                command.Parameters.Add("@3", System.Data.DbType.Int32).Value    = p_Supplier.Adress.Id;
                command.Parameters.Add("@4", System.Data.DbType.DateTime).Value = p_Supplier.Birthday;
                command.Parameters.Add("@5", System.Data.DbType.String).Value   = p_Supplier.Phone;
                command.Parameters.Add("@6", System.Data.DbType.String).Value   = p_Supplier.Company;
                command.Parameters.Add("@7", System.Data.DbType.String).Value   = p_Supplier.Email;
                command.Parameters.Add("@8", System.Data.DbType.Int32).Value    = p_Supplier.Id;

                command.ExecuteNonQuery();

                AdressMapper.SaveAdress(p_Supplier.Adress);
            }
            else //not exising --> create new one
            {
                var adressId = AdressMapper.SaveAdress(p_Supplier.Adress);
                p_Supplier.Adress.Id = adressId;

                command.CommandText = string.Format(
                    "INSERT INTO {0} ({1}, {2}, {3}, {4}, {5}, {6}, {7}) " +
                    "VALUES (@1, @2, @3, @4, @5, @6, @7)",
                    ToolConstants.DB_SUPPLIER_TABLE,
                    ToolConstants.DB_SUPPLIER_LAST_NAME,
                    ToolConstants.DB_SUPPLIER_FIRST_NAME,
                    ToolConstants.DB_SUPPLIER_ADRESS_ID,
                    ToolConstants.DB_SUPPLIER_BIRTHDAY,
                    ToolConstants.DB_SUPPLIER_PHONE,
                    ToolConstants.DB_SUPPLIER_COMPANY,
                    ToolConstants.DB_SUPPLIER_EMAIL);

                command.Parameters.Add("@1", System.Data.DbType.String).Value   = p_Supplier.LastName;
                command.Parameters.Add("@2", System.Data.DbType.String).Value   = p_Supplier.FirstName;
                command.Parameters.Add("@3", System.Data.DbType.Int32).Value    = p_Supplier.Adress.Id;
                command.Parameters.Add("@4", System.Data.DbType.DateTime).Value = p_Supplier.Birthday;
                command.Parameters.Add("@5", System.Data.DbType.String).Value   = p_Supplier.Phone;
                command.Parameters.Add("@6", System.Data.DbType.String).Value   = p_Supplier.Company;
                command.Parameters.Add("@7", System.Data.DbType.String).Value   = p_Supplier.Email;

                command.ExecuteNonQuery();
            }
        }
        public static ObservableCollection <ProductListData> GetAllProductListData()
        {
            var result = new ObservableCollection <ProductListData>();

            SQLiteConnection con     = Connection.GetConnection();
            SQLiteCommand    command = new SQLiteCommand(con);

            command.CommandText = string.Format(
                "SELECT {0}.{1}, {0}.{2}, {0}.{3}, {0}.{4}, {0}.{5}, {0}.{6}, {0}.{7}, {0}.{8}, {0}.{9}, {0}.{10}, {0}.{11}, {0}.{12}, " +
                "{13}.{14}, {13}.{15}, {13}.{16}, {13}.{17}, {13}.{18}, " +
                "{19}.{20}, {19}.{21}, {19}.{22}, {19}.{23}, {19}.{24} FROM {0} " +
                "INNER JOIN {13} ON {13}.{14} = {0}.{5} " +
                "INNER JOIN {19} ON {19}.{20} = {0}.{6}",
                ToolConstants.DB_PRODUCT_TABLE,
                ToolConstants.DB_PRODUCT_ID,
                ToolConstants.DB_PRODUCT_AMOUNT,
                ToolConstants.DB_PRODUCT_SORT,
                ToolConstants.DB_PRODUCT_ORIGIN,
                ToolConstants.DB_PRODUCT_PRODUCER_ID,
                ToolConstants.DB_PRODUCT_SUPPLIER_ID,
                ToolConstants.DB_PRODUCT_CATEGORY,
                ToolConstants.DB_PRODUCT_PURCHASEDATE,
                ToolConstants.DB_PRODUCT_EXPIRATION,
                ToolConstants.DB_PRODUCT_MATURE,
                ToolConstants.DB_PRODUCT_PURCHASEPRICE,
                ToolConstants.DB_PRODUCT_SALESPRICE,
                ToolConstants.DB_PRODUCER_TABLE,
                ToolConstants.DB_PRODUCER_ID,
                ToolConstants.DB_PRODUCER_LAST_NAME,
                ToolConstants.DB_PRODUCER_FIRST_NAME,
                ToolConstants.DB_PRODUCER_COMPANY,
                ToolConstants.DB_PRODUCER_ADRESS_ID,
                ToolConstants.DB_SUPPLIER_TABLE,
                ToolConstants.DB_SUPPLIER_ID,
                ToolConstants.DB_SUPPLIER_LAST_NAME,
                ToolConstants.DB_SUPPLIER_FIRST_NAME,
                ToolConstants.DB_SUPPLIER_COMPANY,
                ToolConstants.DB_SUPPLIER_ADRESS_ID);
            var reader = command.ExecuteReader();

            while (reader.Read())
            {
                var data     = new ProductListData();
                var product  = new Product();
                var producer = new Producer();
                var supplier = new Supplier();

                product.Id            = reader.GetInt32(0);
                product.Amount        = reader.GetInt32(1);
                product.Sort          = reader.GetString(2);
                product.Origin        = reader.GetString(3);
                product.ProducerId    = reader.GetInt32(4);
                product.SupplierId    = reader.GetInt32(5);
                product.Category      = reader.GetString(6);
                product.PurchaseDate  = reader.GetDateTime(7);
                product.Expiration    = reader.GetDateTime(8);
                product.Mature        = reader.GetInt32(9);
                product.PurchasePrice = reader.GetDouble(10);
                product.SalesPrice    = reader.GetDouble(11);

                producer.Id        = reader.GetInt32(12);
                producer.LastName  = reader.GetString(13);
                producer.FirstName = reader.GetString(14);
                producer.Company   = reader.GetString(15);
                producer.Adress    = AdressMapper.GetAdressById(reader.GetInt32(16));

                supplier.Id        = reader.GetInt32(17);
                supplier.LastName  = reader.GetString(18); //hier schmiert der ab
                supplier.FirstName = reader.GetString(19);
                supplier.Company   = reader.GetString(20);
                supplier.Adress    = AdressMapper.GetAdressById(reader.GetInt32(21));

                data.Product  = product;
                data.Producer = producer;
                data.Supplier = supplier;

                result.Add(data);
            }

            return(result);
        }