--compute quantity in sales orders
select productID, sum(quantity) as QuantityInSO into #SO_product
from SalesOrderDetails 
where salesOrderID in (select salesOrderID from SalesOrderInfo where Status = 'New')
group by productID

--compute quantity in short
select a.*, b.QuantityInSO, (b.QuantityInSO - a.quantityInStock) AS QtyInShort into #product_in_short from products a, #SO_product b 
where a.productID = b.productID
	and (quantityInStock < QuantityInSO)

--use cursor to update poID
--Any way of updating poID is acceptable in lab2, but you should try to use cursor in lab3.
--cursor start
DECLARE supplierid CURSOR FOR
SELECT distinct supplierID FROM #product_in_short

declare @po_id int
declare @supp_id int

OPEN supplierid
FETCH NEXT FROM supplierid into @supp_id
WHILE @@FETCH_STATUS = 0
BEGIN
   if((select max(purchaseOrderID) from PurchaseOrderInfo) IS NULL)
	set @po_id = 1
   else
	set @po_id = (select max(purchaseOrderID) from PurchaseOrderInfo) +1

   insert into PurchaseOrderInfo
	values(@po_id, @supp_id, getdate(), getdate() +7, 'New')

   FETCH NEXT FROM supplierid into @supp_id
END
CLOSE supplierid
DEALLOCATE supplierid
--cursor end

--compute quantity to order
ALTER TABLE #product_in_short
add QtyToOrder int

select b.productID, sum(b.quantity) as totalSalesQty into #salesQtyInApr 
from SalesOrderInfo a, SalesOrderDetails b 
where a.salesOrderID = b.salesOrderID
	and convert(varchar(6), orderDate, 112) = '200504'
group by b.productID

update #product_in_short
set QtyToOrder = QtyInShort+0.6*(b.totalSalesQty)
from #product_in_short a, #salesQtyInApr b
where a.productID = b.productID

--consider quantity per unit
insert into PurchaseOrderDetails
select b.purchaseOrderID, a.productID, ((a.QtyInShort/a.quantityPerUnit)+1)*a.quantityPerUnit, a.price
from #product_in_short a, PurchaseOrderInfo b
where a.supplierID = b.supplierID
order by purchaseOrderID