public List <Inventory_Report> getReceived(int warehousesid)
{
getConnection();
var inventory_report1 = new List <Inventory_Report>();
string sql = "" + "select Parts.name, SUM(amount) as 'received' " +
"from OrderItems inner join Parts on OrderItems.PartID = Parts.ID " +
"inner join Orders on OrderItems.OrderID = Orders.ID " +
"where DestinationWarehouseID = @des " +
"group by Parts.name";
SqlCommand cmd = new SqlCommand(sql, cnn);
cmd.Parameters.AddWithValue("des", warehousesid);
cmd.Parameters.AddWithValue("source", warehousesid);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Inventory_Report ir = new Inventory_Report();
ir.partname = (string)dr["name"];
ir.received = (decimal)dr["received"];
inventory_report1.Add(ir);
}
cnn.Close();
return(inventory_report1);
}
public List <Inventory_Report> getBuy(int warehousesid)
{
getConnection();
var inventory_report = new List <Inventory_Report>();
string sql = "" + "select Parts.name, SUM(amount) as 'buy' " +
"from OrderItems inner join Parts on OrderItems.PartID = Parts.ID " +
"inner join Orders on OrderItems.OrderID = Orders.ID " +
"where SourceWarehouseID = @source and amount > 0 " +
"group by Parts.name,PartID";
SqlCommand cmd = new SqlCommand(sql, cnn);
cmd.Parameters.AddWithValue("source", warehousesid);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Inventory_Report ir = new Inventory_Report();
ir.partname = (string)dr["name"];
ir.buy = (decimal)dr["buy"];
inventory_report.Add(ir);
}
cnn.Close();
return(inventory_report);
}