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);
}
}
}
