public static void UpdateGroup(string name, List <string> operations)
{
var db = new SQLtoLinqDataContext(ConnString);
var result = (from g in db.groups
from o in db.operations
from g2o in db.groups2operations
where g.name == name && g.group_ID == g2o.group_ID &&
g2o.operation_ID == o.operation_ID
select g2o).ToList <groups2operation>();
db.groups2operations.DeleteAllOnSubmit(result);
foreach (string oper in operations)
{
var result1 = (from g in db.groups
from o in db.operations
where g.name == name && o.name == oper
select new
{
group_ID = g.group_ID,
operation_ID = o.operation_ID
}).SingleOrDefault();
if (result1 != null)
{
var newRec = new groups2operation();
newRec.group_ID = result1.group_ID;
newRec.operation_ID = result1.operation_ID;
db.groups2operations.InsertOnSubmit(newRec);
}
}
db.SubmitChanges();
}
public static void UpdateUser(string login, string newLogin, string name, string surname, string password)
{
var db = new SQLtoLinqDataContext(ConnString);
var result = (from u in db.Users
where u.login == login
select u).Single();
if (newLogin != null)
{
result.login = newLogin;
}
if (name != null)
{
result.name = name;
}
if (surname != null)
{
result.surname = surname;
}
if (password != null)
{
result.password = UserCtx.Encrypt(password);
}
db.SubmitChanges();
}
public static void UpdateProduct(string productID, string name, DateTime bestBefore, string category)
{
var db = new SQLtoLinqDataContext();
var cat = (from k in db.Kategoria_produktus
where k.nazwa == category
select k).Single();
var result = (from p in db.Produkts
where p.Id == Convert.ToInt32(productID)
select p).Single();
if (name != null)
{
result.nazwa = name;
}
if (bestBefore != null)
{
result.data_przydatnosci = bestBefore;
}
if (category != null)
{
result.id_kategorii = cat.Id;
}
db.SubmitChanges();
}
public static void AddUserGroups(string login, List <string> groups)
{
var db = new SQLtoLinqDataContext(ConnString);
var result = (from u in db.Users
from g in db.groups
from u2g in db.users2groups
where u.login == login && u.user_ID == u2g.user_ID &&
u2g.group_ID == g.group_ID
select u2g);
db.users2groups.DeleteAllOnSubmit(result);
db.SubmitChanges();
foreach (string s in groups)
{
var result1 = (from u in db.Users
from g in db.groups
where u.login == login && g.name == s
select new
{
user_ID = u.user_ID,
group_ID = g.group_ID
}).SingleOrDefault();
if (result1 != null)
{
var temp = new users2group();
temp.group_ID = result1.group_ID;
temp.user_ID = result1.user_ID;
db.users2groups.InsertOnSubmit(temp);
}
}
db.SubmitChanges();
}
public static void UpdatePallet(string palletID, string code, string orderID, string spot_code)
{
var db = new SQLtoLinqDataContext();
var result = (from p in db.Paletas
where p.Id == Convert.ToInt32(palletID)
select p).Single();
if (code != null)
{
result.kod = code;
}
if (orderID != null)
{
result.id_zamowienia = Convert.ToInt32(orderID);
}
if (spot_code != null)
{
var spot = (from m in db.Miejsce_w_mags
where m.kod == spot_code
select m).Single();
result.id_miejsca_w_mag = spot.Id;
}
db.SubmitChanges();
}
public static void AddUserOperations(string login, List <string> operations)
{
var db = new SQLtoLinqDataContext(ConnString);
var result = (from u in db.Users
from u2o in db.users2operations
where u.login == login && u.user_ID == u2o.user_ID
select u2o).ToList <users2operation>();
db.users2operations.DeleteAllOnSubmit(result);
db.SubmitChanges();
foreach (string s in operations)
{
var result1 = (from u in db.Users
from o in db.operations
where u.login == login && o.name == s
select new
{
user_ID = u.user_ID,
operation_ID = o.operation_ID
}).SingleOrDefault();
if (result1 != null)
{
var temp = new users2operation();
temp.user_ID = result1.user_ID;
temp.operation_ID = result1.operation_ID;
db.users2operations.InsertOnSubmit(temp);
}
}
db.SubmitChanges();
}
public static void UpdateOrder(string orderID, string sender, string reciever, DateTime sent, DateTime recieved, string state)
{
var db = new SQLtoLinqDataContext();
var result = (from z in db.Zamowienies
where z.Id == Convert.ToInt32(orderID)
select z).Single();
if (sender != null)
{
result.nadawca = sender;
}
if (reciever != null)
{
result.odbiorca = reciever;
}
if (sent != null)
{
result.data_nadania = sent;
}
if (recieved != null)
{
result.data_odbioru = recieved;
}
if (state != null)
{
var stan = (from s in db.Stans
where s.nazwa_stanu == state
select s).Single();
result.id_stanu = stan.Id;
}
db.SubmitChanges();
}
public static List <string> GetAllGroups()
{
var db = new SQLtoLinqDataContext(ConnString);
var result = (from g in db.groups
select g.name).ToList <string>();
return(result);
}
public static List <string> GetAllOperations()
{
var db = new SQLtoLinqDataContext(ConnString);
var result = (from o in db.operations
select o.name).ToList <string>();
return(result);
}
public static List <string> GetAllCategories()
{
var db = new SQLtoLinqDataContext();
var result = (from k in db.Kategoria_produktus
select k.nazwa
).ToList <string>();
return(result);
}
public static void AddCategory(string name)
{
var db = new SQLtoLinqDataContext();
var cat = new Kategoria_produktu();
cat.nazwa = name;
db.Kategoria_produktus.InsertOnSubmit(cat);
db.SubmitChanges();
}
public static void UpdateGroup(string oldName, string newName)
{
var db = new SQLtoLinqDataContext(ConnString);
var result = (from g in db.groups
where g.name == oldName
select g).Single();
result.name = newName;
db.SubmitChanges();
}
public static void DeleteProduct(string productID)
{
var db = new SQLtoLinqDataContext();
var product = (from p in db.Produkts
where p.Id == Convert.ToInt32(productID)
select p).SingleOrDefault();
db.Produkts.DeleteOnSubmit(product);
db.SubmitChanges();
}
public static void AddPallet(string idOrder, string code)
{
var db = new SQLtoLinqDataContext();
var paleta = new Paleta();
paleta.id_zamowienia = Convert.ToInt32(idOrder);
paleta.kod = code;
db.Paletas.InsertOnSubmit(paleta);
db.SubmitChanges();
}
public static List <string> GetGroupOperations(string name)
{
var db = new SQLtoLinqDataContext(ConnString);
var result = (from g in db.groups
from o in db.operations
from g2o in db.groups2operations
where g.group_ID == g2o.group_ID && g2o.operation_ID == o.operation_ID &&
g.name == name
select o.name).ToList <string>();
return(result);
}
public static List <string> GetUserGroups(string login)
{
var db = new SQLtoLinqDataContext(ConnString);
var result = (from u in db.Users
from g in db.groups
from u2g in db.users2groups
where u.user_ID == u2g.user_ID && u2g.group_ID == g.group_ID &&
u.login == login
select g.name).ToList <string>();
return(result);
}
public static void AddOrder(string sender, string reciever, DateTime sent, DateTime recieved)
{
var db = new SQLtoLinqDataContext();
var order = new Zamowienie();
order.nadawca = sender;
order.odbiorca = reciever;
order.data_nadania = sent;
order.data_odbioru = recieved;
db.Zamowienies.InsertOnSubmit(order);
db.SubmitChanges();
}
public static List <string> GetUserOperations(string login)
{
var db = new SQLtoLinqDataContext(ConnString);
var result = (from u in db.Users
from o in db.operations
from u2o in db.users2operations
where u.user_ID == u2o.user_ID && u2o.operation_ID == o.operation_ID &&
u.login == login
select o.name).ToList <string>();
return(result);
}
public static List <UserResult> GetAllUsers()
{
var db = new SQLtoLinqDataContext(ConnString);
var userResults = (from p in db.Users
select new UserResult
{
user_ID = p.user_ID,
name = p.name,
surname = p.surname,
login = p.login
}).ToList <UserResult>();
return(userResults);
}
public static UserResult GetUser(string login)
{
var db = new SQLtoLinqDataContext(ConnString);
var user = (from u in db.Users
where u.login == login
select new UserResult
{
login = u.login,
name = u.name,
surname = u.surname
}).Single();
return(user);
}
public static void UpdatePallet(string palletID, string code)
{
var db = new SQLtoLinqDataContext();
var result = (from p in db.Paletas
where p.Id == Convert.ToInt32(palletID)
select p).Single();
if (code != null)
{
result.kod = code;
}
db.SubmitChanges();
}
public static ProductResult GetProduct(string productID)
{
var db = new SQLtoLinqDataContext();
var product = (from p in db.Produkts
from k in db.Kategoria_produktus
where p.id_kategorii == k.Id && p.Id == Convert.ToInt32(productID)
select new ProductResult
{
Id = p.Id,
nazwa = p.nazwa,
nazwa_kategorii = k.nazwa,
data_przydatnosci = p.data_przydatnosci ?? DateTime.MinValue
}).Single();
return(product);
}
public static void AddOrder(string sender, string reciever, DateTime sent, DateTime recieved, string state)
{
var db = new SQLtoLinqDataContext();
var order = new Zamowienie();
var stan = (from s in db.Stans
where s.nazwa_stanu == state
select s).Single();
order.nadawca = sender;
order.odbiorca = reciever;
order.data_nadania = sent;
order.data_odbioru = recieved;
order.id_stanu = stan.Id;
db.Zamowienies.InsertOnSubmit(order);
db.SubmitChanges();
}
public static void DeleteOrder(string orderID)
{
var db = new SQLtoLinqDataContext();
var order = (from z in db.Zamowienies
where z.Id == Convert.ToInt32(orderID)
select z).SingleOrDefault();
List <PalletResult> pall = GetAllPallets(order.Id.ToString());
foreach (PalletResult element in pall)
{
DeletePallet(element.kod_palety);
}
db.Zamowienies.DeleteOnSubmit(order);
db.SubmitChanges();
}
public static void AddPallet(string idOrder, string code, string spot_code)
{
var db = new SQLtoLinqDataContext();
var paleta = new Paleta();
var spot = (from m in db.Miejsce_w_mags
where m.kod == spot_code
select m).Single();
paleta.id_zamowienia = Convert.ToInt32(idOrder);
paleta.kod = code;
paleta.id_miejsca_w_mag = spot.Id;
db.Paletas.InsertOnSubmit(paleta);
db.SubmitChanges();
}
public static List <PalletResult> GetAllPallets(string orderID)
{
var db = new SQLtoLinqDataContext();
var PalletResult = (from p in db.Paletas
join m in db.Miejsce_w_mags
on p.id_miejsca_w_mag equals m.Id into pallet
from m in pallet.DefaultIfEmpty()
where p.id_zamowienia == Convert.ToInt32(orderID)
select new PalletResult
{
Id = p.Id,
kod_miejsca_w_mag = m.kod,
kod_palety = p.kod,
id_zamowienia = Convert.ToInt32(p.id_zamowienia)
}).ToList <PalletResult>();
return(PalletResult);
}
public static OrderResult GetOrder(string orderID)
{
var db = new SQLtoLinqDataContext();
var order = (from z in db.Zamowienies
from s in db.Stans
where z.id_stanu == s.Id && z.Id == Convert.ToInt32(orderID)
select new OrderResult
{
Id = z.Id,
nazwa_stanu = s.nazwa_stanu,
nadawca = z.nadawca,
odbiorca = z.odbiorca,
data_nadania = (DateTime)z.data_nadania,
data_odbioru = (DateTime)z.data_odbioru
}).Single();
return(order);
}
public static PalletResult GetPallet(string palletCode)
{
var db = new SQLtoLinqDataContext();
var pall = (from p in db.Paletas
join m in db.Miejsce_w_mags
on p.id_miejsca_w_mag equals m.Id into pallet
from m in pallet.DefaultIfEmpty()
where p.kod == palletCode
select new PalletResult
{
Id = p.Id,
kod_miejsca_w_mag = m.kod,
kod_palety = p.kod,
id_zamowienia = Convert.ToInt32(p.id_zamowienia)
}).Single();
return(pall);
}
public static void AddOperation(string name)
{
var db = new SQLtoLinqDataContext(ConnString);
var newOperation = new operation();
var result = (from o in db.operations
where o.name == name
select o).SingleOrDefault();
if (result != null)
{
throw new Exception("Operation already exists");
}
newOperation.name = name;
db.operations.InsertOnSubmit(newOperation);
db.SubmitChanges();
}
public static void AddGroup(string name)
{
var db = new SQLtoLinqDataContext(ConnString);
var newGroup = new group();
var result = (from g in db.groups
where g.name == name
select g).SingleOrDefault();
if (result != null)
{
throw new Exception("Group already exists");
}
newGroup.name = name;
db.groups.InsertOnSubmit(newGroup);
db.SubmitChanges();
}
