public bool DB_Query_Result() { //Initial Catalog needs to be assigned by the name of database //Data Source is the URI using (WMS_Project_DB db = new WMS_Project_DB(CONNECTION.conn)) { var dataQuery = (from o in db.DataBaseUsers where String.Equals(o.UserName, this.username) where String.Equals(o.UserPassword, this.Password) //needs to encrypt in the end select o).FirstOrDefault(); if (dataQuery == null) { return false; } else return true; } }
//fill table public List<DataBaseSOH> INV_Table_Query( string branchFilter, string ownerFilter, string proFilter, string locFilter, bool showZero) { //Initial Catalog needs to be assigned by the name of database //Data Source is the URI float lowerBound = (showZero == true) ? -1 : 0; //set lower bound for query //showZero then > -1, else > 0 using (WMS_Project_DB db = new WMS_Project_DB(CONNECTION.conn)) { List<DataBaseSOH> queryList = db.DataBaseSOHs.ToList(); List<DataBaseSOH> dataQuery; //setup filter if (isFirstTime) { branchList = (from o in db.DataBaseSOHs.ToList() select o.Branch).Distinct().ToList(); ownerList = (from o in db.DataBaseSOHs.ToList() select o.Owner).Distinct().ToList(); } if (!String.Equals("All", branchFilter) && !String.Equals("All", ownerFilter)) { dataQuery = (from o in queryList where o.QTY > lowerBound where String.Equals(o.Branch, branchFilter) where String.Equals(o.Owner, ownerFilter) where o.Location.StartsWith(locFilter) where o.ProductCode.StartsWith(proFilter) orderby o.Location select o).ToList(); return dataQuery; } else if (String.Equals("All", branchFilter) && !String.Equals("All", ownerFilter)) { dataQuery = (from o in queryList where o.QTY > lowerBound where String.Equals(o.Owner, ownerFilter) where o.Location.StartsWith(locFilter) where o.ProductCode.StartsWith(proFilter) orderby o.Location select o).ToList(); return dataQuery; } else if (!String.Equals("All", branchFilter)) { dataQuery = (from o in queryList where o.QTY > lowerBound where String.Equals(o.Branch, branchFilter) where o.Location.StartsWith(locFilter) where o.ProductCode.StartsWith(proFilter) //where o.Location.Contains(locFilter) //where o.ProductCode.Contains(proFilter) orderby o.Location select o).ToList(); return dataQuery; } else { dataQuery = (from o in queryList where o.QTY > lowerBound where o.Location.StartsWith(locFilter) where o.ProductCode.StartsWith(proFilter) //where o.Location.Contains(locFilter) //where o.ProductCode.Contains(proFilter) orderby o.Location select o).ToList(); return dataQuery; } } }
public static void addNewTransHistoryEntry(DataBaseTranHistory entryTrans, WMS_Project_DB db) { entryTrans.User = entryTrans.User.ToLower(); try { Table<DataBaseTranHistory> dbTrans = db.DataBaseTrans; dbTrans.InsertOnSubmit(entryTrans); db.SubmitChanges(); } catch (Exception e) { MessageBox.Show(e.ToString()); } }
public static void UpdateDataBaseSOH(DataBaseSOH entrySOH, DataBaseTranHistory newTrans) { //DataContext db = new DataContext(connection); //Table<DataBaseSOH> DataBaseSOHs = db.GetTable<DataBaseSOH>(); entrySOH.Location = entrySOH.Location.ToUpper(); newTrans.Location = newTrans.Location.ToUpper(); using (WMS_Project_DB db = new WMS_Project_DB(CONNECTION.conn)) { try { DataBaseSOH dbSOH = db.DataBaseSOHs.Single(o => o.Branch.Equals(entrySOH.Branch) && o.Owner.Equals(entrySOH.Owner) && o.Location.Equals(entrySOH.Location) && o.ProductCode.Equals(entrySOH.ProductCode) && o.Colour.Equals(entrySOH.Colour) && o.Size.Equals(entrySOH.Size) && o.ArrivalDate.Equals(entrySOH.ArrivalDate)); if (entrySOH.Location != newTrans.Location) //location changed { DataBaseSOH toLoc = db.DataBaseSOHs.SingleOrDefault(o => o.Branch.Equals(entrySOH.Branch) && o.Owner.Equals(entrySOH.Owner) && o.Location.Equals(newTrans.Location) && o.ProductCode.Equals(entrySOH.ProductCode) && o.Colour.Equals(entrySOH.Colour) && o.Size.Equals(entrySOH.Size) && o.ArrivalDate.Equals(entrySOH.ArrivalDate)); if (toLoc == null) { //dbSOH.Location = newTrans.Location; //newTrans stores updated location DataBaseSOH newSOH = new DataBaseSOH() //entrySOH stores the previous { ArrivalDate = dbSOH.ArrivalDate, Attribute1 = dbSOH.Attribute1, Attribute2 = dbSOH.Attribute2, Attribute3 = dbSOH.Attribute3, Attribute4 = dbSOH.Attribute4, Attribute5 = dbSOH.Attribute5, BatchNumber = dbSOH.BatchNumber, Branch = dbSOH.Branch, Colour = dbSOH.Colour, ExpiryDate = dbSOH.ExpiryDate, Location = newTrans.Location, //location changed Owner = dbSOH.Owner, ProductCode = dbSOH.ProductCode, QTY = dbSOH.QTY, RecordIdentifier = dbSOH.RecordIdentifier, SerialNumber = dbSOH.SerialNumber, Size = dbSOH.Size, StockStatus = dbSOH.StockStatus, UOM = dbSOH.UOM }; db.DataBaseSOHs.InsertOnSubmit(newSOH); } else { toLoc.QTY += dbSOH.QTY; } db.DataBaseSOHs.DeleteOnSubmit(dbSOH); } else { if (dbSOH.QTY != entrySOH.QTY) //QTY adjust { dbSOH.QTY = entrySOH.QTY; } else if (dbSOH.StockStatus != entrySOH.StockStatus) //stock adjust dbSOH.StockStatus = entrySOH.StockStatus; else { dbSOH.Attribute1 = entrySOH.Attribute1; dbSOH.Attribute2 = entrySOH.Attribute2; dbSOH.Attribute3 = entrySOH.Attribute3; dbSOH.Attribute4 = entrySOH.Attribute4; dbSOH.Attribute5 = entrySOH.Attribute5; } } //db.SubmitChanges(); DataBaseTranHistory.addNewTransHistoryEntry(newTrans, db); } catch (Exception e) { MessageBox.Show(e.ToString()); } } }
public static void AddDataBaseSOH(DataBaseSOH entrySOH, DataBaseTranHistory newTrans) { using (WMS_Project_DB db = new WMS_Project_DB(CONNECTION.conn)) { db.DataBaseSOHs.InsertOnSubmit(entrySOH); DataBaseTranHistory.addNewTransHistoryEntry(newTrans, db); } }
//fill table public List <DataBaseSOH> INV_Table_Query( string branchFilter, string ownerFilter, string proFilter, string locFilter, bool showZero) { //Initial Catalog needs to be assigned by the name of database //Data Source is the URI float lowerBound = (showZero == true) ? -1 : 0; //set lower bound for query //showZero then > -1, else > 0 using (WMS_Project_DB db = new WMS_Project_DB(CONNECTION.conn)) { List <DataBaseSOH> queryList = db.DataBaseSOHs.ToList(); List <DataBaseSOH> dataQuery; //setup filter if (isFirstTime) { branchList = (from o in db.DataBaseSOHs.ToList() select o.Branch).Distinct().ToList(); ownerList = (from o in db.DataBaseSOHs.ToList() select o.Owner).Distinct().ToList(); } if (!String.Equals("All", branchFilter) && !String.Equals("All", ownerFilter)) { dataQuery = (from o in queryList where o.QTY > lowerBound where String.Equals(o.Branch, branchFilter) where String.Equals(o.Owner, ownerFilter) where o.Location.StartsWith(locFilter) where o.ProductCode.StartsWith(proFilter) orderby o.Location select o).ToList(); return(dataQuery); } else if (String.Equals("All", branchFilter) && !String.Equals("All", ownerFilter)) { dataQuery = (from o in queryList where o.QTY > lowerBound where String.Equals(o.Owner, ownerFilter) where o.Location.StartsWith(locFilter) where o.ProductCode.StartsWith(proFilter) orderby o.Location select o).ToList(); return(dataQuery); } else if (!String.Equals("All", branchFilter)) { dataQuery = (from o in queryList where o.QTY > lowerBound where String.Equals(o.Branch, branchFilter) where o.Location.StartsWith(locFilter) where o.ProductCode.StartsWith(proFilter) //where o.Location.Contains(locFilter) //where o.ProductCode.Contains(proFilter) orderby o.Location select o).ToList(); return(dataQuery); } else { dataQuery = (from o in queryList where o.QTY > lowerBound where o.Location.StartsWith(locFilter) where o.ProductCode.StartsWith(proFilter) //where o.Location.Contains(locFilter) //where o.ProductCode.Contains(proFilter) orderby o.Location select o).ToList(); return(dataQuery); } } }