create view  instock as SELECT Sum(purchases.inquantity*units.quantity)-Sum(purchases.outquantity*units.quantity) AS q, purchases.itemcode, purchases.expirecode, purchases.stockid
FROM purchases INNER JOIN units ON purchases.unitid = units.unitid
GROUP BY purchases.itemcode, purchases.expirecode, purchases.stockid;
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
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;
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
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;

go

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

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

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 FROM instock UNION ALL SELECT q, itemcode, expirecode, stockid FROM outstock UNION ALL SELECT q, itemcode,
  expirecode, stockid FROM taswyastock UNION ALL
 SELECT q, itemcode, expirecode, stockid FROM transinstock UNION ALL SELECT q, itemcode, expirecode, stockid FROM transoutstock) jard INNER JOIN stocks ON 
 jard.stockid=stocks.stockid) INNER JOIN items ON jard.itemcode=items.itemcode) INNER JOIN units ON items.dunitid=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