//Lerchner Felix
 public static void Remove(Property property)
 {
     SQL_methods.SQL_exec(string.Format(
                              "DELETE FROM properties " +
                              "WHERE id = '{0}'",
                              property.ID));
 }
 public static void Remove(Unit unit)
 {
     SQL_methods.SQL_exec(string.Format(
                              "DELETE FROM units " +
                              "WHERE id = '{0}'",
                              unit.ID));
 }
Exemple #3
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="name"></param>
        /// <param name="description"></param>
        /// <param name="unit">-1 ignores this parameter</param>
        /// <param name="categorie">-1 ignores this parameter</param>
        /// <param name="l0">l0 - l1 range</param>
        /// <param name="w0">w0 - w1 range</param>
        /// <param name="h0">h0 - h1 range</param>
        /// <param name="l1">l0 - l1 range</param>
        /// <param name="w1">w0 - w1 range</param>
        /// <param name="h1">h0 - h1 range</param>
        /// <param name="image"></param>
        /// <returns></returns>
        //Lerchner Felix
        public static List <Item> Search(string name, string description, int unit, int category, int l0, int w0, int h0, int l1, int w1, int h1, string image)
        {
            List <Unit>     units      = Unit.All_Units();
            List <Category> categories = Category.All_Categories();
            List <Property> properties = Property.All_Properties();
            List <NtoN>     sNtopN     = Property.All_sn_to_pn();

            string sql = string.Format(
                "SELECT * FROM storage_elements WHERE " +
                "(element_name LIKE '{0}' OR " +
                "element_description LIKE '{1}' OR " +
                "(element_unit_id BETWEEN {2} AND {3}) OR " +
                "(element_category_id BETWEEN {4} AND {5}) OR " +
                "(element_size_l BETWEEN {6} AND {7}) OR" +
                "(element_size_w BETWEEN {8} AND {9}) OR " +
                "(element_size_h BETWEEN {10} AND {11}) OR " +
                "element_image LIKE '{12}') AND " +
                "active = {13}",
                Searching_Format(name),
                Searching_Format(description),
                unit, unit,
                category, category,
                l0, l1,
                w0, w1,
                h0, h1,
                Searching_Format(image)
                );
            OdbcCommand cmd = new OdbcCommand(sql, DB.Connection);

            SQL_methods.Open();
            OdbcDataReader sqlReader = cmd.ExecuteReader();

            List <Item> items = new List <Item>();

            while (sqlReader.Read())
            {
                List <NtoN>     sNtopN_     = (from x in sNtopN where x.storage == (int)sqlReader["id"] select x).ToList();
                List <Property> properties_ = (from x in properties where sNtopN_.Any(y => x.ID == y.property) select x).ToList();//I hope this functions correctly. I think it is alright.

                Unit unit_ = (from x in units where x.ID == (int)sqlReader["element_unit_id"] select x).First();
                //Unit unit_ = (Unit) units.Where(x => x.ID == ( int ) sqlReader["element_unit_id"]);
                Category category_ = (from x in categories where x.ID == (int)sqlReader["element_category_id"] select x).First();


                items.Add(new Item(
                              (string)sqlReader["element_name"],
                              (string)sqlReader["element_description"],
                              (float)sqlReader["element_size_l"],
                              (float)sqlReader["element_size_w"],
                              (float)sqlReader["element_size_h"],
                              unit_,
                              category_,
                              properties_,
                              (string)sqlReader["element_image"],
                              (string)sqlReader["id"]
                              ));
            }

            return(items);
        }
 public static void Remove(Category category)
 {
     SQL_methods.SQL_exec(string.Format(
                              "DELETE FROM categories " +
                              "WHERE id = '{0}'",
                              category.ID));
 }
Exemple #5
0
        //Lerchner Felix
        public static bool Is_active(Item item)
        {
            string      sql = "SELECT active FROM storage_elements WHERE id = " + item.ID;
            OdbcCommand cmd = new OdbcCommand(sql, DB.Connection);

            SQL_methods.Open();
            return((bool)cmd.ExecuteScalar());
        }
Exemple #6
0
 //Lerchner Felix
 public static void Save(Item item)
 {
     SQL_methods.SQL_exec(string.Format(
                              "INSERT INTO storage_elements " +
                              "(element_name, element_description, element_unit_id, element_category_id, element_size_l, element_size_w, element_size_h, element_image, element_dataID) " +
                              "VALUES " +
                              "('{0}', '{1}', {2}, {3}, {4}, {5}, {6}, '{7}', '{8}')",
                              item.Name, item.Description, item.Unit.ID, item.Category.ID, item.Length, item.Width, item.Height, item.Image, item.Artikelnummer));
 }
 //Lerchner Felix
 public static void Save(Property property)
 {
     SQL_methods.SQL_exec(string.Format(
                              "INSERT INTO properties " +
                              "(property_name, property_description)" +
                              "VALUES " +
                              "('{0}', '{1}')",
                              property.Name, property.Description));
 }
 /// <summary>
 /// Error by Lerchner der Beichtner
 /// </summary>
 /// <param name="unit"></param>
 public static void Save(Unit unit)
 {
     SQL_methods.SQL_exec(string.Format(
                              "INSERT INTO units " +
                              "(unit_si, unit_name, unit_description)" +
                              "VALUES " +
                              "('{0}', '{1}', '{2}')",
                              unit.SI_Unit, unit.Name, unit.Description));
 }
 public static void Save(Category category)
 {
     SQL_methods.SQL_exec(string.Format(
                              "INSERT INTO categories " +
                              "(category_name, category_description)" +
                              "VALUES " +
                              "('{0}', '{1}')",
                              category.Name, category.Description));
 }
Exemple #10
0
 //Lerchner Felix
 public static int In_how_many_storage_spaces_is_this_item_located(Item item)
 {
     if (Exists_in_DB(item))
     {
         int         id  = Get_DB_ID(item);
         string      sql = "SELECT Count(*) FROM element_location WHERE element_location.element_id = " + item.ID;
         OdbcCommand cmd = new OdbcCommand(sql, DB.Connection);
         SQL_methods.Open();
         return((int)cmd.ExecuteScalar());
     }
     else
     {
         return(0);
     }
 }
        //Lerchner Felix
        public static Unit Get_from_ID(int id)
        {
            string sql = "SELECT id as id, unit_si as si, unit_name as name, unit_description as description FROM units";

            OdbcCommand cmd = new OdbcCommand(sql, DB.Connection);

            SQL_methods.Open();
            OdbcDataReader sqlReader = cmd.ExecuteReader();

            sqlReader.Read();

            Unit unit = (new Unit((string)sqlReader["si"], (string)sqlReader["name"], (string)sqlReader["description"], (int)sqlReader["id"]));

            return(unit);
        }
Exemple #12
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="item"></param>
        /// <returns>returns List of IDs or if not exists null</returns>
        //Lerchner Felix
        public static List <int> Where_is(Item item)
        {
            int         id  = Get_DB_ID(item);
            string      sql = "SELECT * FROM element_location WHERE element_location.element_id = " + item.ID;
            OdbcCommand cmd = new OdbcCommand(sql, DB.Connection);

            SQL_methods.Open();
            OdbcDataReader sqlReader = cmd.ExecuteReader();
            List <int>     ids       = new List <int>();

            while (sqlReader.Read())
            {
                ids.Add((int)sqlReader[0]);
            }
            return(ids);
        }
        /// <summary>
        /// Loads all storage to property ids
        /// </summary>
        /// <returns>List with all n to n connections</returns>
        //Lerchner Felix
        public static List <NtoN> All_sn_to_pn( )
        {
            string sql = "SELECT storage_id, property_id FROM storage_properties";

            OdbcCommand cmd = new OdbcCommand(sql, DB.Connection);

            SQL_methods.Open();
            OdbcDataReader sqlReader = cmd.ExecuteReader();

            List <NtoN> sNtopN = new List <NtoN>();

            while (sqlReader.Read())
            {
                sNtopN.Add(new NtoN(( int )sqlReader["storage_id"], ( int )sqlReader["property_id"]));
            }

            return(sNtopN);
        }
        //Lerchner Felix
        public static List <Unit> All_Units()
        {
            string sql = "SELECT id as id, unit_si as si, unit_name as name, unit_description as description FROM units";

            OdbcCommand cmd = new OdbcCommand(sql, DB.Connection);

            SQL_methods.Open();
            OdbcDataReader sqlReader = cmd.ExecuteReader();

            List <Unit> units = new List <Unit>();

            while (sqlReader.Read())
            {
                units.Add(new Unit((string)sqlReader["si"], (string)sqlReader["name"], (string)sqlReader["description"], (int)sqlReader["id"]));
            }

            return(units);
        }
        //Lerchner Felix
        public static List <Category> All_Categories( )
        {
            string sql = "SELECT id as id, category_name as name, category_description as description FROM categories";

            OdbcCommand cmd = new OdbcCommand(sql, DB.Connection);

            SQL_methods.Open();
            OdbcDataReader sqlReader = cmd.ExecuteReader();

            List <Category> categories = new List <Category>();

            while (sqlReader.Read())
            {
                categories.Add(new Category(( int )sqlReader["id"], ( string )sqlReader["name"], ( string )sqlReader["description"]));
            }

            return(categories);
        }
Exemple #16
0
        //Lerchner Felix
        public static bool Exists_in_DB(Item item)
        {
            string sql = string.Format("" +
                                       "SELECT COUNT(*) " +
                                       "FROM storage_elements WHERE " +
                                       "element_name = '{0}' " +
                                       "AND element_description = '{1}' " +
                                       "AND element_unit_id = '{2}' " +
                                       "AND element_category_id = '{3}' " +
                                       "AND element_size_l = '{4}' " +
                                       "AND element_size_w = '{5}' " +
                                       "AND element_size_h = '{6}' " +
                                       //"AND element_image = '{7}' " +
                                       "AND element_dataID = '{7}'", item.Name, item.Description, item.Unit.ID, item.Category.ID, item.Length, item.Width, item.Height, /*item.Image,*/ item.Artikelnummer);
            OdbcCommand cmd = new OdbcCommand(sql, DB.Connection);

            SQL_methods.Open();
            OdbcDataReader sqlReader = cmd.ExecuteReader();

            sqlReader.Read();
            return((long)sqlReader[0] != 0);
        }
Exemple #17
0
        //Lerchner Felix
        public static int Get_DB_ID(Item item)
        {
            string sql = string.Format("" +
                                       "SELECT ID " +
                                       "FROM storage_elements WHERE " +
                                       "element_name = '{0}' " +
                                       "AND element_description = '{1}' " +
                                       "AND element_unit_id = '{2}' " +
                                       "AND element_category_id = '{3}' " +
                                       "AND element_size_l = '{4}' " +
                                       "AND element_size_w = '{5}' " +
                                       "AND element_size_h = '{6}' " +
                                       "AND element_image = '{7}'",
                                       item.Name, item.Description, item.Unit.ID, item.Category.ID, item.Length, item.Width, item.Height, item.Image);

            OdbcCommand cmd = new OdbcCommand(sql, DB.Connection);

            SQL_methods.Open();
            OdbcDataReader sqlReader = cmd.ExecuteReader();

            return((int)sqlReader[0]);
        }
Exemple #18
0
 //Lerchner Felix
 public static void Deactivate(Item item)
 {
     SQL_methods.SQL_exec("UPDATE storage_elements SET active = false WHERE id = " + item.ID);
 }