--drop view  instock
--drop view  outstock
--drop view  taswyastock
--drop view  transinstock
--drop view  transoutstock 
--drop view  expirecodes
--drop view stockjard

 
create view  instock as SELECT Sum(purchases.inquantity*units.quantity)-Sum(purchases.outquantity*units.quantity) AS q, purchases.itemcode, purchases.expirecode, purchases.stockid,purchases.unitid
FROM purchases INNER JOIN units ON purchases.unitid = units.unitid
GROUP BY purchases.itemcode, purchases.expirecode, purchases.stockid,purchases.unitid;
go

create view outstock as SELECT SUM(orderDetail.inquantity*units.quantity*compoundcompontents.quantity)-SUM(orderDetail.outquantity*units.quantity*compoundcompontents.quantity) AS q, orderDetail.expirecode, orderDetail.stockid, compoundcompontents.itemcode ,orderDetail.unitid
FROM (orderDetail INNER JOIN units ON orderDetail.unitid=units.unitid) INNER JOIN compoundcompontents ON orderDetail.compoundid=compoundcompontents.compoundid
GROUP BY orderDetail.expirecode, orderDetail.stockid, compoundcompontents.itemcode, compoundcompontents.quantity,orderDetail.unitid;
go

create view taswyastock as SELECT SUM(taswyaDetail.inquantity*units.quantity*compoundcompontents.quantity)-SUM(taswyaDetail.outquantity*units.quantity*compoundcompontents.quantity) AS q,
compoundcompontents.itemcode,  taswyaDetail.expirecode, taswyaDetail.stockid,taswyaDetail.unitid
FROM taswyaDetail INNER JOIN units ON taswyaDetail.unitid=units.unitid  INNER JOIN compoundcompontents ON taswyaDetail.itemcode=compoundcompontents.compoundid
GROUP BY compoundcompontents.itemcode, compoundcompontents.quantity, taswyaDetail.expirecode, taswyaDetail.stockid,taswyaDetail.unitid;

go

create view transinstock as SELECT SUM(transfereDetail.quantity*units.quantity) AS q, transfereDetail.itemcode, transfereDetail.expirecode, transfereDetail.instockid AS stockid,transfereDetail.unitid
FROM transfereDetail INNER JOIN units ON transfereDetail.unitid=units.unitid
GROUP BY transfereDetail.itemcode, transfereDetail.expirecode, transfereDetail.instockid,transfereDetail.unitid;
go

create view transoutstock as SELECT SUM(transfereDetail.quantity*units.quantity)*-1 AS q, transfereDetail.itemcode, transfereDetail.expirecode, transfereDetail.outstockid AS stockid,transfereDetail.unitid
FROM transfereDetail INNER JOIN units ON transfereDetail.unitid=units.unitid
GROUP BY transfereDetail.itemcode, transfereDetail.expirecode, transfereDetail.outstockid,transfereDetail.unitid;

go
create view expirecodes as SELECT DISTINCT expirecode FROM orderDetail UNION SELECT DISTINCT expirecode
 FROM purchases UNION SELECT DISTINCT expirecode FROM taswyaDetail;
 go


create view stockjard as SELECT jard.itemcode, jard.stockid, stocks.stockname, '' +  Sum(jard.q)/units.quantity  + '' AS q, units.unitname, jard.expirecode,
 expires.expiredate, items.itemname,items.groupcode
 FROM (((((SELECT q, itemcode, expirecode, stockid,unitid FROM instock UNION ALL SELECT q, itemcode, expirecode, stockid,unitid FROM outstock UNION ALL SELECT q, itemcode,
  expirecode, stockid,unitid FROM taswyastock UNION ALL
 SELECT q, itemcode, expirecode, stockid,unitid FROM transinstock UNION ALL SELECT q, itemcode, expirecode, stockid,unitid FROM transoutstock) jard INNER JOIN stocks ON 
 jard.stockid=stocks.stockid) INNER JOIN items ON jard.itemcode=items.itemcode) INNER JOIN units ON jard.unitid=units.unitid) LEFT JOIN expires ON 
 jard.expirecode=expires.expirecode)
GROUP BY jard.itemcode, jard.stockid, stocks.stockname, units.unitname, jard.expirecode, expires.expiredate, units.quantity, items.itemname  ,items.groupcode