create view groups2 as select * from groups create view departs2 as select * from departs create view market_mndobin as SELECT mndobname as ee, round((select SUM(debit) from orders as sl where (sl.mndobid = custse.mndobid) AND sl.processid = 5 ),2) as tot, round((select count(*) from orders as sl where (sl.mndobid = custse.mndobid) AND sl.processid = 5 ),2) as cc ,round((select SUM(credit) from orders as sl where (sl.mndobid = custse.mndobid) AND sl.processid = 7 ),2) as retruntot, round((select count(*) from orders as sl where (sl.mndobid = custse.mndobid) AND sl.processid = 7 ),2) as retruntcc FROM (select distinct (mndobid) from orders )custse inner join mndobins on custse.mndobid=mndobins.mndobid create view jbs_bill as SELECT orderdetail.compoundid, items.itemname, convert(varchar,orderDetail.outquantity) AS quantity, units.unitname, orderDetail.price, orderDetail.discount, (orderDetail.outquantity * orderDetail.price) * (1 - (orderDetail.discount / 100)) AS sumi, stocks.stockname, orderDetail.netprice, expires.expiredate, (orderdetail.outquantity * units.quantity) / ( compoundsdata.rq * units2.quantity ) as compq FROM ((items inner join compoundsdata on compoundsdata.itemcode=items.itemcode INNER JOIN ((orderDetail INNER JOIN units ON orderDetail.unitid = units.unitid) INNER JOIN stocks ON orderDetail.stockid = stocks.stockid) ON compoundsdata.compoundid = orderDetail.compoundid inner join units units2 on units2.unitid=compoundsdata.runitid ) LEFT OUTER JOIN expires ON orderDetail.expirecode = expires.expirecode) WHERE (orderDetail.orderid = 54) create view UnpiadBills as SELECT customers.custid,customers.custname,checknote as clientname,xx.billno,xx.bill_order_NoTxt,debit,debit-tot as p, tot, orders_2.worthdate from (select * from (select customerid,billno,bill_order_NoTxt ,case when cr is null then debit else debit-cr end as tot from ( select customerid, billno,bill_order_NoTxt,debit , (select sum(credit) as cr from orders where processid in (4,13) and customerid =orders2.customerid and ( checkbillno = orders2.bill_order_NoTxt or reversebillno = orders2.billno )) as cr from orders orders2 where processid in (3) ) llxx)dd where tot>0 ) xx INNER JOIN customers ON xx.customerid = customers.custid INNER JOIN orders AS orders_2 ON xx.bill_order_NoTxt = orders_2.bill_order_NoTxt where customers.bybill=1 create view cars_data as select itemcode,itemname,itemplace, pq,unitname ,price ,expiredate,edate,suppname,cost,expn,case when expn >0 then expn +cost else cost end as tot,groupcode,departid from ( SELECT items.itemcode, items.itemname,itemplace, SUM(jard.q) / units.quantity AS pq,units.unitname,itemsunits.price, expiredate,pur.netprice as cost,pur.edate,pur.suppname, expn,groupcode,departid 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) AS jard right JOIN items ON jard.itemcode = items.itemcode and items.i=0 INNER JOIN units ON jard.unitid = units.unitid inner join itemsunits on itemsunits.itemcode=items.itemcode and itemsunits.unitid=items.dunitid left join expires on expires.expirecode=jard.expirecode left join (select expirecode,netprice,edate ,suppname from purchases inner join suppliersaccounts on suppliersaccounts.orderid=purchases.orderid inner join suppliers on suppliers.suppid=suppliersaccounts.supplierid where expirecode>0) pur on pur.expirecode=jard.expirecode left join (select item_ExpireCode,sum(amount) as expn from expensing where item_ExpireCode>0 group by item_ExpireCode) exp on exp.item_ExpireCode=jard.expirecode GROUP BY items.itemcode, items.itemname,itemplace,price,expiredate,quantity,items.groupcode,edate,suppname, departid,units.unitname,netprice,expn) xxx where pq > 0 create view deliveryCustomersRepots as select branchname,custname,tel1,tel2,tel3,surveydate as edate, empname , '' as count from delivery_customers inner join surveying on surveying.custid=delivery_customers.custid inner join employees on surveying.empid=employees.empid inner join branchs on branchs.branchid=surveying.brnachid create view agentacc as SELECT suppliersaccounts.supplierid as agentid,suppliersaccounts.edate, processes.process, suppliersaccounts.checkno, suppliersaccounts.billno, suppliersaccounts.reverseno, suppliersaccounts.orderid, 0 as bill_order_NoTxt ,0 as checkbillno,suppliersaccounts.credit, suppliersaccounts.debit, suppliersaccounts.notes, processes.proessid, suppliersaccounts.substracted, suppliersaccounts.added FROM suppliersaccounts INNER JOIN processes ON suppliersaccounts.processid = processes.proessid; create view empssalesStatics as select empname, groupcode,itemname, expiredate,q, unitname,tot ,countall from (SELECT groupcode, items.itemname, expires.expiredate,ROUND( SUM(orderDetail.outquantity * units.quantity) / units_1.quantity,3) AS q, units_1.unitname, ROUND(SUM(orderDetail.outquantity * (orderDetail.price *( 1 - orderDetail.discount / 100))),2) AS tot, count(*) AS countall,empname FROM orders inner join employees on employees.empid=orders.miterid INNER JOIN orderDetail ON orders.OrderID = orderDetail.orderid INNER JOIN units ON orderDetail.unitid = units.unitid INNER JOIN compoundsdata ON orderDetail.compoundid = compoundsdata.compoundid INNER JOIN items on items.itemcode=compoundsdata.itemcode INNER JOIN units AS units_1 ON items.dunitid = units_1.unitid LEFT OUTER JOIN expires ON orderDetail.expirecode = expires.expirecode WHERE (orders.billno > 0) and miterid=118 GROUP BY expires.expiredate, empname,items.itemname,groupcode, units_1.quantity, units_1.unitname)xx create view agentbills as SELECT items.itemname, CONVERT(varchar, purchases.inquantity ) AS quantity, CONVERT(varchar, purchases.outquantity ) AS quantity2, units.unitname, purchases.price, purchases.discount, (purchases.inquantity*purchases.price)*(1-purchases.discount/100) AS sumi, (purchases.outquantity*purchases.price)*(1-purchases.discount/100) AS sumii, stocks.stockname, purchases.netprice,purchases.expirecode, expires.expiredate, suppliersaccounts.orderid FROM suppliersaccounts INNER JOIN ((((purchases INNER JOIN items ON purchases.itemcode=items.itemcode) INNER JOIN units ON purchases.unitid=units.unitid) INNER JOIN stocks ON purchases.stockid=stocks.stockid) LEFT JOIN expires ON purchases.expirecode=expires.expirecode) ON suppliersaccounts.orderid=purchases.orderid; create view agentdata as SELECT suppliers.suppname, '1000' AS precredit, '0' AS predebit, '' AS peroid FROM suppliers WHERE (((suppliers.[suppid])=1)); create view agentsdata as SELECT suppid as agentid,suppliers.suppname, '' AS peroid FROM suppliers WHERE (((suppliers.[suppid])=1)) create view billdetail as SELECT suppliers.suppname AS agent, processes.process, processes.proessid, suppliersaccounts.billno AS numb, suppliersaccounts.substracted, suppliersaccounts.added, suppliersaccounts.credit AS billsum, suppliersaccounts.edate, suppliersaccounts.notes ,Total_VAT ,132 AS precredit, 123 AS predebit,'المؤسسة' as mndobname FROM (suppliersaccounts INNER JOIN suppliers ON suppliersaccounts.supplierid=suppliers.suppid) INNER JOIN processes ON suppliersaccounts.processid=processes.proessid; create view billitems as SELECT items.itemname,itemplace,convert(varchar, purchases.inquantity ) AS quantity, units.unitname, purchases.price, purchases.discount, (purchases.inquantity*purchases.price)*(1-purchases.discount/100) AS sumi,purchases.VAT,purchases.VAT_Value, stocks.stockname, purchases.netprice, expires.expiredate FROM (((purchases INNER JOIN items ON purchases.itemcode=items.itemcode) INNER JOIN units ON purchases.unitid=units.unitid) INNER JOIN stocks ON purchases.stockid=stocks.stockid) LEFT JOIN expires ON purchases.expirecode=expires.expirecode; create view receiptdetail as SELECT 3223 AS precredit, 33443 AS predebit, processes.process, processes.proessid, customers.custname AS agent, orders.billno AS num, orders.edate, orders.credit AS receiptcost, orders.notes,Null as mndobname,'' as bankname ,checkbillno FROM (orders INNER JOIN customers ON orders.customerid=customers.custid) INNER JOIN processes ON orders.processid=processes.proessid; create view custdebits as select orders.customerid ,customers.custname, round(SUM(debit)-SUM(credit),2) as tot from orders inner join customers on orders.customerid =customers.custid group by customerid,customers.custname having SUM(debit)-SUM(credit)> .01 CREATE VIEW CUSTMOVES AS SELECT customerS.custid,custname, round( (SELECT SUM(debit) - SUM(credit) AS z FROM orders AS od WHERE (customerid = custse.customerid AND edate < GETDATE()) or (customerid = custse.customerid AND edate between GETDATE() and GETDATE() and processid=0) ) ,2) AS minb, round((select SUM(debit) from orders as sl where (sl.customerid = custse.customerid) AND sl.processid in (3,6) ),2) as sales, round((select SUM(credit) from orders as sl where (sl.customerid = custse.customerid) AND sl.processid in (6,13) ),2) as paid, round((select SUM(credit) from orders as sl where (sl.customerid = custse.customerid) AND sl.processid in (4,7) ),2) as backedsales, round((select SUM(debit) from orders as sl where (sl.customerid = custse.customerid) AND sl.processid in (7,14) ),2) as backedpay, round((select SUM(credit) from orders as sl where (sl.customerid = custse.customerid) AND sl.processid =18 ),2) as subed, round((select SUM(debit) from orders as sl where (sl.customerid = custse.customerid) AND sl.processid =17 ),2) as added, round((SELECT SUM(debit) - SUM(credit) AS z FROM orders AS od WHERE (customerid = custse.customerid) AND edate <=GETDATE()),2) AS maxb FROM (select distinct (customerid) from orders where edate between GETDATE() and GETDATE())custse LEFT join customers on custid=customerid create view suppcredits as select suppliersaccounts.supplierid ,suppliers.suppname, round(SUM(credit)-SUM(debit),2) as tot from suppliersaccounts inner join suppliers on suppliersaccounts.supplierid =suppliers.suppid group by supplierid,suppliers.suppname having SUM(credit)-SUM(debit)> .01 create view suppmoves as SELECT suppliers.suppid,suppname, round( (SELECT SUM(credit) - SUM(debit) AS z FROM suppliersaccounts AS od WHERE (supplierid = custse.supplierid AND edate < GETDATE()) or (supplierid = custse.supplierid AND edate between GETDATE() and GETDATE() and processid=0) ) ,2) AS minb, round((select SUM(credit) from suppliersaccounts as sl where (sl.supplierid = custse.supplierid) AND sl.processid in (1) ),2) as sales, round((select SUM(debit) from suppliersaccounts as sl where (sl.supplierid = custse.supplierid) AND sl.processid in (11) ),2) as paid, round((select SUM(debit) from suppliersaccounts as sl where (sl.supplierid = custse.supplierid) AND sl.processid in (2) ),2) as backedsales, round((select SUM(credit) from suppliersaccounts as sl where (sl.supplierid = custse.supplierid) AND sl.processid in (12) ),2) as backedpay, round((select SUM(debit) from suppliersaccounts as sl where (sl.supplierid = custse.supplierid) AND sl.processid =16 ),2) as subed, round((select SUM(credit) from suppliersaccounts as sl where (sl.supplierid = custse.supplierid) AND sl.processid =15 ),2) as added, round((SELECT SUM(credit) - SUM(debit) AS z FROM suppliersaccounts AS od WHERE (supplierid = custse.supplierid) AND edate <=GETDATE()),2) AS maxb FROM (select distinct (supplierid) from suppliersaccounts where edate between GETDATE() and GETDATE())custse LEFT join suppliers on suppid=supplierid create view expensesreport as SELECT expensing.processno, expensing.edate, expenseslist.expenselist, expenses.expense, expensing.amount, expensing.person, expensing.notes FROM ((expensing INNER JOIN expenses ON expensing.expenseid = expenses.expenseid) INNER JOIN expenseslist ON expenses.expenselistid = expenseslist.expenselistid) create view expense as select processno,edate,person,expenselist,expense,amount,notes,'dsd' as bankname from expensing inner join expenses on expenses.expenseid=expensing.expenseid inner join expenseslist on expenseslist.expenselistid=expenses.expenselistid where processno=5 create view title as select '' as title create view custosuppdata as select custname as ename,tel,mobile,fax,address,email,notes from customers create view transeferitems as SELECT items.itemname, units.unitname, expires.expiredate, transfereDetail.itemcode, transfereDetail.unitid, transfereDetail.quantity, transfereDetail.expirecode, price,transfereDetail.netprice FROM transfereDetail INNER JOIN items ON items.itemcode = transfereDetail.itemcode INNER JOIN units ON transfereDetail.unitid = units.unitid LEFT OUTER JOIN expires ON transfereDetail.expirecode = expires.expirecode WHERE (transfereDetail.Processno =10) create view transeferdetail as select * from transfer create view taswyaitems as SELECT taswyaDetail.itemcode, taswyaDetail.unitid, items.itemname,CONVERT(varchar, taswyaDetail.outquantity) as outquantity,CONVERT(varchar,taswyaDetail.inquantity) as inquantity, units.unitname, taswyaDetail.price, stocks.stockname, taswyaDetail.netprice, expires.expiredate, taswyaDetail.expirecode, taswyaDetail.stockid , CASE WHEN inquantity=0 THEN 'عجز' ELSE 'زيادة' END as type FROM ((items INNER JOIN ((taswyaDetail INNER JOIN units ON taswyaDetail.unitid = units.unitid) INNER JOIN stocks ON taswyaDetail.stockid = stocks.stockid) ON items.itemcode = taswyaDetail.itemcode) LEFT OUTER JOIN expires ON taswyaDetail.expirecode = expires.expirecode) create view taswyadetailrpt as select * from taswya create view indusrty_items as SELECT units.unitname, items.itemname,round( compounditems.quantity*1,3) as quantity, compounditems.itemcompoundid, compounditems.unitid, compounditems.itemcode FROM ((units INNER JOIN compounditems ON units.unitid = compounditems.unitid) INNER JOIN items ON compounditems.itemcode = items.itemcode) WHERE compounditems.compoundid =1000003 create view industryq as select *,'-' as uname,'7.43' as q,'كباب ' as itmname,'مخزن حدائق القبة' as inst,'مخزن حدائق القبة' as outstk ,70 as cost from taswya where Processno=6 create view bank_move as SELECT processes.process, banksmoves.processno, banksmoves.edate, banks.bankname, banksmoves.inamount as amount, banksmoves.notes FROM (banks INNER JOIN banksmoves ON banks.bankid = banksmoves.bankid )INNER JOIN processes ON banksmoves.processid = processes.proessid WHERE (banksmoves.processno = 7) create view banktranfer as select processno,edate,banksout.bankname as outbank,banks.bankname as inbank,amount ,bankstransfers.notes,recieved from bankstransfers inner join banks on bankstransfers.inbankid=banks.bankid inner join banks banksout on bankstransfers.outbankid=banksout.bankid where processno= 5 create view bankrecords as select * from ( SELECT banksmoves.processno, banks.bankname, banksmoves.edate, processes.process, banksmoves.inamount, banksmoves.outamount, banksmoves.processid ,banksmoves.notes FROM ((banks INNER JOIN banksmoves ON banks.bankid = banksmoves.bankid) INNER JOIN processes ON banksmoves.processid = processes.proessid) where banksmoves.bankid=1 ) bankier create view stockjardrpt as SELECT jard.itemcode, jard.stockid, stocks.stockname, CONVERT(varchar, round(Sum(jard.q)/units.quantity,3) ) AS q, units.unitname, jard.expirecode, expires.expiredate, items.itemname,items.groupcode,items.departid 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,items.departid create view stockevalutation as select * from ( select itemcode,groupcode,itemname,convert(varchar,pq) as q,unitname ,round((SELECT top 1 purchases.netprice/units.quantity FROM purchases INNER JOIN suppliersaccounts ON purchases.orderid = suppliersaccounts.orderid and suppliersaccounts.processid=1 inner join units on purchases.unitid=units.unitid where itemcode=xxx.itemcode order by edate desc,suppliersaccounts.orderid desc)*qq,2) as price ,round((SELECT top 1 purchases.netprice/units.quantity FROM purchases INNER JOIN suppliersaccounts ON purchases.orderid = suppliersaccounts.orderid and suppliersaccounts.processid=1 inner join units on purchases.unitid=units.unitid where itemcode=xxx.itemcode order by edate desc,suppliersaccounts.orderid desc)*qq * pq,2) as price_cost ,round(netprice * qq,2) as netprice ,round(netprice *qq * pq,2) as netprice_cost ,convert(date,expiredate +'/28',111) as expiredate from ( SELECT jard.itemcode,items.groupcode, items.itemname,items.netprice, SUM(jard.q) / units.quantity AS pq, units.unitname,units.quantity as qq ,expiredate 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) AS jard INNER JOIN items ON jard.itemcode = items.itemcode and items.i=0 INNER JOIN units ON items.dunitid = units.unitid LEFT OUTER JOIN expires ON jard.expirecode = expires.expirecode GROUP BY jard.itemcode, items.itemname,items.groupcode,items.netprice, units.quantity, units.unitname ,expiredate having Sum(jard.q)>0) xxx )cx where cx.expiredate <='2012/10/28' create view itemsorders as select itemcode,itemname,convert(varchar,requestq) as requestq ,convert(varchar,pq) as pq,convert(varchar,requestq-pq) as requested,unitname ,(SELECT top 1 purchases.netprice/units.quantity FROM purchases INNER JOIN suppliersaccounts ON purchases.orderid = suppliersaccounts.orderid inner join units on purchases.unitid=units.unitid where itemcode=xxx.itemcode order by edate desc,suppliersaccounts.orderid desc)*qq as net ,(SELECT top 1 purchases.netprice/units.quantity FROM purchases INNER JOIN suppliersaccounts ON purchases.orderid = suppliersaccounts.orderid inner join units on purchases.unitid=units.unitid where itemcode=xxx.itemcode order by edate desc,suppliersaccounts.orderid desc)*qq * (requestq-pq) as cost ,(select suppname from suppliers where suppid =(SELECT top 1 suppliersaccounts.supplierid FROM purchases INNER JOIN suppliersaccounts ON purchases.orderid = suppliersaccounts.orderid where itemcode=xxx.itemcode order by edate desc,suppliersaccounts.orderid desc)) as suppname ,(SELECT top 1 suppliersaccounts.edate FROM purchases INNER JOIN suppliersaccounts ON purchases.orderid = suppliersaccounts.orderid where itemcode=xxx.itemcode order by edate desc,suppliersaccounts.orderid desc) as netdate , (select top 1 * from ( (SELECT MIN( purchases.netprice)/units.quantity * qq as x FROM purchases INNER JOIN suppliersaccounts ON purchases.orderid = suppliersaccounts.orderid inner join units on purchases.unitid=units.unitid where itemcode=xxx.itemcode and DATEDIFF(m,suppliersaccounts.edate,GETDATE())<= 2 group by units.quantity) union all (SELECT top 1 purchases.netprice/units.quantity*qq as x FROM purchases INNER JOIN suppliersaccounts ON purchases.orderid = suppliersaccounts.orderid inner join units on purchases.unitid=units.unitid where itemcode=xxx.itemcode order by edate desc,suppliersaccounts.orderid desc) )fdef where fdef.x > 0 ) as minprice from ( SELECT jard.itemcode, items.itemname, items.requestq, SUM(jard.q) / units.quantity AS pq,units.unitname,units.quantity as qq 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) AS jard INNER JOIN items ON jard.itemcode = items.itemcode and items.i=0 INNER JOIN units ON items.requestunitid = units.unitid GROUP BY jard.itemcode, items.itemname, items.requestq, units.quantity, units.unitname) xxx where pq0 then outquantity *( (price/100*(100-discount))-orderDetail.netprice ) else 0 end) ,2)AS subs, ROUND(SUM(orderDetail.outquantity * (orderDetail.netprice - orderDetail.unitnetprice * units.quantity)),2) AS earn, stocks.stockname ,count(*) AS countall FROM orders INNER JOIN orderDetail ON orders.OrderID = orderDetail.orderid INNER JOIN stocks ON orderDetail.stockid = stocks.stockid INNER JOIN units ON orderDetail.unitid = units.unitid INNER JOIN compoundsdata ON orderDetail.compoundid = compoundsdata.compoundid INNER JOIN items on items.itemcode=compoundsdata.itemcode INNER JOIN units AS units_1 ON items.dunitid = units_1.unitid LEFT OUTER JOIN expires ON orderDetail.expirecode = expires.expirecode WHERE (orders.billno > 0) and orders.branchid=1 GROUP BY expires.expiredate, items.itemname, units_1.quantity, units_1.unitname, stocks.stockname)xx create view items_sales as create view items_sales2 as SELECT items.groupcode, items.itemname, SUM(orderDetail.outquantity * units.quantity) AS q, SUM(orderDetail.price * orderDetail.outquantity) AS c, items.itemcode, items.dunitid, units_1.unitname, SUM(orderDetail.outquantity * units.quantity) / units_1.quantity AS qq FROM ((((((groups INNER JOIN items ON groups.groupcode = items.groupcode) INNER JOIN departs ON groups.departid = departs.departid) INNER JOIN compoundsdata ON items.itemcode = compoundsdata.itemcode) INNER JOIN (orders INNER JOIN orderDetail ON orders.OrderID = orderDetail.orderid) ON compoundsdata.compoundid = orderDetail.compoundid) INNER JOIN units ON orderDetail.unitid = units.unitid) INNER JOIN units units_1 ON items.dunitid = units_1.unitid) WHERE (orders.shiftsn >=1782 and orders.shiftsn <=1787) and orders.branchid=2 and (orders.ordertype<>4) and (orders.credit = orders.debit) GROUP BY items.itemcode, items.itemname,items.groupcode, items.dunitid, units_1.unitname, units_1.quantity create view detailedincome as select 'فرع الحدائق' as branchname ,0 as branch, ordertypes.ordertype as ee, grouping(ordertypes.ordertype) as ot,round( sum(debit),2) as tot,count(*) as cc from orders inner join ordertypes on orders.ordertype=ordertypes.ordertypeid where orders.shiftsn between 1520 and 1718 and orders.branchid= 1 group by ordertypes.ordertype with cube create view consumed_items as SELECT items.itemname, SUM(orderDetail.outquantity * units.quantity * compoundcompontents.quantity) / units_1.quantity AS q,units_1.unitname, expires.expiredate , stocks.stockname FROM orders INNER JOIN orderDetail ON orders.OrderID = orderDetail.orderid INNER JOIN stocks ON orderDetail.stockid = stocks.stockid INNER JOIN units ON orderDetail.unitid = units.unitid INNER JOIN compoundcompontents ON orderDetail.compoundid = compoundcompontents.compoundid INNER JOIN items ON compoundcompontents.itemcode = items.itemcode INNER JOIN units AS units_1 ON items.dunitid = units_1.unitid LEFT OUTER JOIN expires ON orderDetail.expirecode = expires.expirecode WHERE (orders.billno > 0) AND orders.branchid=0 GROUP BY stocks.stockname, units_1.unitname, expires.expiredate, items.itemname, units_1.quantity create view sheek as SELECT orders.* ,custname,tel1,tel2,tel3,delivery_customers.notes as custnotes,Delivery_address.address,bulidno,floor,flat,mark,Delivery_address.notes as adressnote,areaname,tablename,sectionname,ordertypes.ordertype as order_type,branchname,employees.empname as deliveryman ,miter.empname as MiterMAn,statusname FROM orders left JOIN employees ON employees.empid = orders.DeliverierID and orders.DeliverierID <>0 left JOIN employees miter ON miter.empid = orders.DeliverierID and orders.miterid <>0 INNER JOIN ordertypes ON orders.OrderType = ordertypes.ordertypeid left join Delivery_customers on delivery_customers.custid=orders.custid left join Delivery_address on Delivery_address.addresscode=orders.deliveryaddressid left JOIN areas on areas.areacode=area left JOIN tables ON orders.tableno = tables.tableid left join sections on sections.sectionid=tables.sectionid inner join branchs on branchs.branchid=orders.branchid inner JOIN orderstatus on orderstatus.statuscode=orders.orderstatus WHERE orders.orderid = 19 create view sheekDetail as SELECT orderDetail.compoundid, orderDetail.unitid, items.itemname, orderDetail.outquantity AS quantity, units.unitname, orderDetail.price,orderDetail.unitnetprice, orderDetail.discount, stocks.stockname, orderDetail.outquantity *orderDetail.price*(1-( orderDetail.discount/100))as sumi, orderDetail.stockid, orderDetail.netprice, orderDetail.expirecode, expires.expiredate FROM (((compoundsdata INNER JOIN items ON compoundsdata.itemcode = items.itemcode) INNER JOIN ((orderDetail INNER JOIN units ON orderDetail.unitid = units.unitid) INNER JOIN stocks ON orderDetail.stockid = stocks.stockid) ON compoundsdata.compoundid = orderDetail.compoundid) LEFT OUTER JOIN expires ON orderDetail.expirecode = expires.expirecode)WHERE (orderDetail.orderid = 19) create view sheekLog as SELECT custsdatalogs.notes,custsdatalogs.OrderDate, custsdatalogs.OrderTime, employees.empname, stations.stationname FROM ((custsdatalogs INNER JOIN employees ON custsdatalogs.EmpID = employees.empid) INNER JOIN stations ON custsdatalogs.stationid = stations.stationid)WHERE (custsdatalogs.orderid = 19) UNION ALL SELECT orders.notes,orders.OrderDate, orders.OrderTime, employees_1.empname, stations.stationname FROM ((orders INNER JOIN employees employees_1 ON orders.EmpID = employees_1.empid) INNER JOIN stations ON orders.stationid = stations.stationid) WHERE (orders.orderid =19) create view sheekSequences as SELECT sequenceid,ordersequences.compoundid, ordersequences.unitid, items.itemname, ordersequences.outquantity AS quantity, units.unitname, ordersequences.price,ordersequences.unitnetprice, ordersequences.discount, stocks.stockname, ordersequences.outquantity *ordersequences.price*(1-( ordersequences.discount/100))as sumi,ordersequences.stockid, ordersequences.netprice, ordersequences.expirecode, expires.expiredate FROM (((compoundsdata INNER JOIN items ON compoundsdata.itemcode = items.itemcode) INNER JOIN ((ordersequences INNER JOIN units ON ordersequences.unitid = units.unitid) left JOIN stocks ON ordersequences.stockid = stocks.stockid) ON compoundsdata.compoundid = ordersequences.compoundid) LEFT OUTER JOIN expires ON ordersequences.expirecode = expires.expirecode)WHERE (ordersequences.orderid = 19 ) create view jardsitemsrpt as SELECT jardDetail.itemcode, jardDetail.unitid, items.itemname, CONVERT(varchar,jardDetail.quantity) as quantity, units.unitname, jardDetail.price, stocks.stockname, jardDetail.netprice, expires.expiredate, jardDetail.expirecode, jardDetail.stockid FROM ((items INNER JOIN ((jardDetail INNER JOIN units ON jardDetail.unitid = units.unitid) INNER JOIN stocks ON jardDetail.stockid = stocks.stockid) ON items.itemcode = jardDetail.itemcode) LEFT OUTER JOIN expires ON jardDetail.expirecode = expires.expirecode) WHERE (jardDetail.Processno = 13) create view jardDetailrpt as select * from jards where Processno=13 create view SubscriptionPayment as select custname ,filename,fileno,orders.Customerid,ProcessID,edate,substracted,added,debit,credit,bankid,orders.notes,orders.OrderID,rentorders.fileid,Payed_date,Payed_Enddate,Subscriptions,change,pre_balance,rentorders.lastCopiesCount,CurrentCopiesCount,rentorders.allowedCopies,rentorders.additionalCopyCost,rentorders.itemorderid ,itemname,unitname,outquantity,expiredate,170.25 as prebalance from orders inner join rentorders on orders.orderid=rentorders.orderid inner join orderdetail on orderdetail.orderid=rentorders.itemorderid inner join customers on customers.CustID=orders.Customerid inner join machinesfiles on machinesfiles.fileid=rentorders.fileid inner join items on items.itemcode=orderdetail.compoundid inner join units on units.unitid=orderdetail.unitid left join expires on orderdetail.expirecode=expires.expirecode where orders.orderid=82000 create view machineProcess as select custname ,filename,fileno,orders.Customerid,Process,edate,debit,credit,bankid,orders.notes,orders.OrderID from orders inner join rentorders on orders.orderid=rentorders.orderid inner join processes on orders.processid=processes.ProessID inner join customers on customers.CustID=orders.Customerid inner join machinesfiles on machinesfiles.fileid=rentorders.fileid WHERE (orders.orderid = 82009) create view maintanenceRequests as select xx.*,orders.notes,orders.ordertime,empname from ( select fileid,fileno,filename,filecode, customers.custid,custname, (select max(orderid)from orders where orders.customerid=customers.custid and orders.processid=30 ) as idorder from customers inner join machinesfiles on customers.custid=machinesfiles.custid and machinesfiles.maintenanceRequired=1 )xx inner join orders on orders.orderid=xx.idorder inner join employees on orders.empid=employees.empid create view filearchive as SELECT orders.edate, processes.process, orders.checkno, orders.billno, orders.reverseno, orders.credit, orders.debit, orders.notes, processes.proessid ,orders.substracted,orders.added,rentorders.* FROM orders INNER JOIN processes ON orders.processid = processes.proessid inner join rentorders on rentorders.orderid=orders.orderid where orders.customerid=24 and processes.proessid <>0 and fileid=1 create view filedata as SELECT custname , machinesfiles.* ,'' as title FROM customers inner join machinesfiles on machinesfiles.custid=customers.custid WHERE (fileid = 1) create view machinesfilesrpt as select custname,machinesfiles.*,itemname,unitname,outquantity,expiredate from customers inner join machinesfiles on customers.custid=machinesfiles.custid inner join orderdetail on orderdetail.orderid=machinesfiles.itemorderid inner join items on items.itemcode=orderdetail.compoundid inner join units on units.unitid=orderdetail.unitid left join expires on orderdetail.expirecode=expires.expirecode where customers.custid>0 and machinesfiles.closed=0 and customers.i=0 create view custvatbills as SELECT customers.custid as agentid,customers.custname as agentname,xx.billno,edate,debit as tot,Total_VAT as vat from orders xx INNER JOIN customers ON xx.customerid = customers.custid and xx.Total_VAT>0 create view delivcustdata as select distinct Delivery_customers.*,areaname,address,branchid from Delivery_customers left join delivery_address on delivery_address.custid=Delivery_customers.custid left join areas on areas.areacode=delivery_address.area where (address like '%%' or bulidno like '%%') and branchid=5 and areacode=52