/// <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);
}
//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());
}
//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);
}
/// <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);
}
//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);
}
/// <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 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);
}
//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]);
}