public DataTable GetPartBySource(int WareHouseSource)
{
string sql = "select DISTINCT PartID as ID, PartName from OrderItems inner join PARTS on OrderItems.PartID = PARTS.ID inner join Orders on Orders.ID = OrderItems.OrderID where Orders.Destination = @Source ";
DataTable data = Utility.ExecuteDataReader(sql, new object[] { WareHouseSource });
return(data);
}
public DataTable GetAllItems(int first, int last)
{
//string sql = "select p.PartName, e.TranSactionName , e.OrderDate, e.Amount, e.source, e.Desnitaion, e.OrderItem from (select o.source, o.Desnitaion , o.TranSactionName , o.OrderDate, od.ID as 'OrderItem', od.PartID, od.Amount from (select c.ID, c.source, c.Desnitaion , t.TranSactionName , c.OrderDate from (select a.ID ,a.Source, a.TranSactionID, a.OrderDate, b.Desnitaion from (select o.ID, w.WareHouseName as 'Source',o.TranSactionID, o.OrderDate from Orders o inner join WareHouses w on o.SourceWareHouse = w.ID) AS a inner join (select o.ID, w.WareHouseName as 'Desnitaion', o.TranSactionID, o.OrderDate from Orders o inner join WareHouses w on o.Destination = w.ID) AS b on a.ID = b.ID) AS c inner join TranSactionTypes t on c.TranSactionID = t.ID) AS o inner join OrderItems od on o.ID = od.OrderID) AS e inner join PARTS p on e.PartID = p.ID";
string sql = "select e.OrderItem,e.PartID,e.IDDes, e.IDSource,e.TranID, p.PartName, e.TranSactionName , e.OrderDate, e.Amount, e.source, e.Desnitaion from (select o.source, o.Desnitaion , o.TranSactionName , o.OrderDate, od.ID as 'OrderItem', od.PartID, od.Amount, o.IDSource,o.IDDes,o.TranID from (select c.IDSource , c.source, c.Desnitaion , t.TranSactionName, t.ID as 'TranID' , c.OrderDate, c.IDDes from (select a.IDSource ,a.Source, a.TranSactionID, a.OrderDate, b.Desnitaion, b.IDDes from (select o.ID as 'IDSource', w.WareHouseName as 'Source',o.TranSactionID, o.OrderDate from Orders o inner join WareHouses w on o.SourceWareHouse = w.ID) a inner join (select o.ID as 'IDDes', w.WareHouseName as 'Desnitaion', o.TranSactionID, o.OrderDate from Orders o left join WareHouses w on o.Destination = w.ID) b on a.IDSource = b.IDDes) c left join TranSactionTypes t on c.TranSactionID = t.ID) o left join OrderItems od on o.IDSource = od.OrderID) e inner join PARTS p on e.PartID = p.ID order by e.OrderItem DESC offset @first rows fetch next @last rows only";
return(Utility.ExecuteDataReader(sql, new object[] { first, last }));
}
public DataTable GetBatchNumber(int Ware_House, string PartName)
{
DataTable data = new DataTable();
string sql = "select * from (select DISTINCT t.PartName, t.Recevied - ISNULL(h.Ban, 0 ) as 'CurrentStock', t.Recevied ,t.BathNumber from ( select PARTS.PartName,OrderItems.BathNumber , sum(Amount) as 'Recevied' from OrderItems inner join PARTS on OrderItems.PartID = PARTS.ID inner join Orders on Orders.ID = OrderItems.OrderID where Orders.Destination = @Ware group by PARTS.PartName, OrderItems.BathNumber) t full join ( select PARTS.PartName, OrderItems.BathNumber, sum(Amount) as 'Ban' from OrderItems inner join PARTS on OrderItems.PartID = PARTS.ID inner join Orders on Orders.ID = OrderItems.OrderID where Orders.SourceWareHouse = @WareHouse and Orders.TranSactionID = 2 group by PARTS.PartName, OrderItems.BathNumber) h on t.PartName = h.PartName) l where l.PartName = @PartName ";
data = Utility.ExecuteDataReader(sql, new object[] { Ware_House, Ware_House, PartName });
return(data);
}
public List <Supply> GetAllSupplies()
{
List <Supply> ListSupplies = new List <Supply>();
string SqlWareHouse = "Select * from Suppliers";
DataTable data = Utility.ExecuteDataReader(SqlWareHouse);
foreach (DataRow row in data.Rows)
{
int ID = int.Parse(row["ID"].ToString());
string WareHouseName = row["SupplyName"].ToString();
Supply supply = new Supply(ID, WareHouseName, null);
ListSupplies.Add(supply);
}
return(ListSupplies);
}
public List <WareHouse> GetAllSourceOrder()
{
string sql = "select DISTINCT w.ID, w.WareHouseName from WareHouses w inner join Orders o on w.ID = o.Destination";
List <WareHouse> ListWareHouses = new List <WareHouse>();
DataTable data = Utility.ExecuteDataReader(sql);
foreach (DataRow row in data.Rows)
{
int ID = int.Parse(row["ID"].ToString());
string WareHouseName = row["WareHouseName"].ToString();
WareHouse wareHouse = new WareHouse(ID, WareHouseName, null);
ListWareHouses.Add(wareHouse);
}
return(ListWareHouses);
}
public List <WareHouse> GetAllWareHouse()
{
List <WareHouse> ListWareHouses = new List <WareHouse>();
string SqlWareHouse = "Select * from WareHouses";
DataTable data = Utility.ExecuteDataReader(SqlWareHouse);
foreach (DataRow row in data.Rows)
{
int ID = int.Parse(row["ID"].ToString());
string WareHouseName = row["WareHouseName"].ToString();
WareHouse wareHouse = new WareHouse(ID, WareHouseName, null);
ListWareHouses.Add(wareHouse);
}
return(ListWareHouses);
}
public Part GetPartByID(int PartID)
{
Part part = new Part();
string sql = "Select * from PARTS where ID = @PartID";
DataTable data = Utility.ExecuteDataReader(sql, new object[] { PartID });
foreach (DataRow row in data.Rows)
{
int ID = int.Parse(row["ID"].ToString());
string PartName = row["PartName"].ToString();
string EffectiveLife = row["EffectiveLife"].ToString();
bool BatchNumberHasRequired = bool.Parse(row["BatchNumberHasRequired"].ToString());
int MinimumAmount = int.Parse(row["MinimumAmount"].ToString());
part = new Part(ID, PartName, EffectiveLife, BatchNumberHasRequired, MinimumAmount, null);
}
return(part);
}
public List <Part> GetAllParts()
{
List <Part> ListParts = new List <Part>();
string sql = "Select * from PARTS";
DataTable data = Utility.ExecuteDataReader(sql);
foreach (DataRow row in data.Rows)
{
int ID = int.Parse(row["ID"].ToString());
string PartName = row["PartName"].ToString();
string EffectiveLife = row["EffectiveLife"].ToString();
bool BatchNumberHasRequired = bool.Parse(row["BatchNumberHasRequired"].ToString());
int MinimumAmount = int.Parse(row["MinimumAmount"].ToString());
Part part = new Part(ID, PartName, EffectiveLife, BatchNumberHasRequired, MinimumAmount, null);
ListParts.Add(part);
}
return(ListParts);
}
public DataTable GetByPartAndBath(int PartID, string Bath)
{
string sql = "select o.ID, o.OrderID, o.PartID, o.BathNumber, o.Amount from OrderItems o inner join Orders od on o.OrderID = od.ID where o.PartID = @PartID and o.BathNumber = @Batch ";
return(Utility.ExecuteDataReader(sql, new object[] { PartID, Bath }));
}
public DataTable GetBathByPart(int PartID)
{
string sql = "select p.ID, p.PartName, o.BathNumber from PARTS p inner join OrderItems o on p.ID = o.PartID where p.ID = @PartID";
return(Utility.ExecuteDataReader(sql, new object[] { PartID }));
}
public DataTable GetLastRow()
{
string sql = "select top(13) e.OrderItem,e.PartID,e.IDDes, e.IDSource,e.TranID, p.PartName, e.TranSactionName , e.OrderDate, e.Amount, e.source, e.Desnitaion from (select o.source, o.Desnitaion , o.TranSactionName , o.OrderDate, od.ID as 'OrderItem', od.PartID, od.Amount, o.IDSource,o.IDDes,o.TranID from (select c.IDSource , c.source, c.Desnitaion , t.TranSactionName, t.ID as 'TranID' , c.OrderDate, c.IDDes from (select a.IDSource ,a.Source, a.TranSactionID, a.OrderDate, b.Desnitaion, b.IDDes from (select o.ID as 'IDSource', w.WareHouseName as 'Source',o.TranSactionID, o.OrderDate from Orders o inner join WareHouses w on o.SourceWareHouse = w.ID) a inner join (select o.ID as 'IDDes', w.WareHouseName as 'Desnitaion', o.TranSactionID, o.OrderDate from Orders o left join WareHouses w on o.Destination = w.ID) b on a.IDSource = b.IDDes) c left join TranSactionTypes t on c.TranSactionID = t.ID) o left join OrderItems od on o.IDSource = od.OrderID) e inner join PARTS p on e.PartID = p.ID order by e.OrderItem desc";
return(Utility.ExecuteDataReader(sql));
}
public DataTable GetAllTranSaction()
{
string sql = "Select * from TranSactionTypes";
return(Utility.ExecuteDataReader(sql));
}