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