<html dir="ltr"><head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<meta content="MSHTML 6.00.6000.17098" name="GENERATOR">
<style title="owaParaStyle"><!--P {
MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px
}
--></style>
</head>
<body ocsi="x">
<div dir="ltr"><font face="Arial" color="#000000">Hi Joel,</font></div>
<div dir="ltr"><font face="Arial"></font> </div>
<div dir="ltr"><font face="Arial">Took these right off the Wiki (</font><a href="http://wiki.koha-community.org/wiki/SQL_Reports_Library"><font face="Arial">http://wiki.koha-community.org/wiki/SQL_Reports_Library</font></a><font face="Arial">), hope they help:</font></div>
<div dir="ltr"><font face="Arial"></font> </div>
<div dir="ltr">
<h4><span class="mw-headline" id="Orders_in_Date_Range"><font face="Arial">Orders in Date Range
</font></span></h4>
<ul>
<li><font face="Arial"><b>Developer:</b> Nicole C. Engard, ByWater Solutions </font>
</li><li><font face="Arial"><b>Module:</b> Acquisitions </font></li><li><font face="Arial"><b>Purpose:</b> Show order information for a specific date period.
</font></li><li><font face="Arial"><b>Status:</b> Complete </font></li></ul>
<div class="mw-geshi" dir="ltr" style="TEXT-ALIGN: left">
<div class="sql source-sql">
<pre class="de1"><font face="Arial"><span class="kw1"><strong><font color="#993333">SELECT</font></strong></span> v<span class="sy0"><font color="#66cc66">.</font></span>name <span class="kw1"><strong><font color="#993333">AS</font></strong></span> vendor<span class="sy0"><font color="#66cc66">,</font></span> b<span class="sy0"><font color="#66cc66">.</font></span>title <span class="kw1"><strong><font color="#993333">AS</font></strong></span> <span class="st0"><font color="#ff0000">'book title'</font></span><span class="sy0"><font color="#66cc66">,</font></span>
format<span class="br0"><font color="#66cc66">(</font></span>o<span class="sy0"><font color="#66cc66">.</font></span>listprice<span class="sy0"><font color="#66cc66">,</font></span><span class="nu0"><font color="#cc66cc">2</font></span><span class="br0"><font color="#66cc66">)</font></span> <span class="kw1"><strong><font color="#993333">AS</font></strong></span> <span class="st0"><font color="#ff0000">'list price'</font></span><span class="sy0"><font color="#66cc66">,</font></span>
format<span class="br0"><font color="#66cc66">(</font></span>o<span class="sy0"><font color="#66cc66">.</font></span>unitprice<span class="sy0"><font color="#66cc66">,</font></span><span class="nu0"><font color="#cc66cc">2</font></span><span class="br0"><font color="#66cc66">)</font></span> <span class="kw1"><strong><font color="#993333">AS</font></strong></span> <span class="st0"><font color="#ff0000">'actual price'</font></span><span class="sy0"><font color="#66cc66">,</font></span>
ba<span class="sy0"><font color="#66cc66">.</font></span>basketname<span class="sy0"><font color="#66cc66">,</font></span> o<span class="sy0"><font color="#66cc66">.</font></span>notes
<span class="kw1"><strong><font color="#993333">FROM</font></strong></span> aqorders o
<span class="kw1"><strong><font color="#993333">LEFT</font></strong></span> <span class="kw1"><strong><font color="#993333">JOIN</font></strong></span> aqbasket ba <span class="kw1"><strong><font color="#993333">USING</font></strong></span> <span class="br0"><font color="#66cc66">(</font></span>basketno<span class="br0"><font color="#66cc66">)</font></span>
<span class="kw1"><strong><font color="#993333">LEFT</font></strong></span> <span class="kw1"><strong><font color="#993333">JOIN</font></strong></span> aqbooksellers v <span class="kw1"><strong><font color="#993333">ON</font></strong></span> <span class="br0"><font color="#66cc66">(</font></span>v<span class="sy0"><font color="#66cc66">.</font></span>id <span class="sy0"><font color="#66cc66">=</font></span> ba<span class="sy0"><font color="#66cc66">.</font></span>booksellerid<span class="br0"><font color="#66cc66">)</font></span>
<span class="kw1"><strong><font color="#993333">LEFT</font></strong></span> <span class="kw1"><strong><font color="#993333">JOIN</font></strong></span> biblio b <span class="kw1"><strong><font color="#993333">USING</font></strong></span> <span class="br0"><font color="#66cc66">(</font></span>biblionumber<span class="br0"><font color="#66cc66">)</font></span>
<span class="kw1"><strong><font color="#993333">WHERE</font></strong></span> o<span class="sy0"><font color="#66cc66">.</font></span>entrydate <span class="kw1"><strong><font color="#993333">BETWEEN</font></strong></span> <span class="sy0"><font color="#66cc66"><<</font></span>Ordered <span class="kw1"><strong><font color="#993333">BETWEEN</font></strong></span> <span class="br0"><font color="#66cc66">(</font></span>yyyy<span class="sy0"><font color="#66cc66">-</font></span>mm<span class="sy0"><font color="#66cc66">-</font></span>dd<font color="#66cc66"><span class="br0">)</span><span class="sy0">>></span></font> <span class="kw1"><strong><font color="#993333">AND</font></strong></span> <span class="sy0"><font color="#66cc66"><<</font></span>and <span class="br0"><font color="#66cc66">(</font></span>yyyy<span class="sy0"><font color="#66cc66">-</font></span>mm<span class="sy0"><font color="#66cc66">-</font></span>dd<font color="#66cc66"><span class="br0">)</span><span class="sy0">>></span></font></font></pre>
</div>
</div>
<h4><span class="mw-headline" id="Ledger"><font face="Arial">Ledger </font></span></h4>
<ul>
<li><font face="Arial"><b>Developer:</b> Nicole C. Engard, ByWater Solutions </font>
</li><li><font face="Arial"><b>Module:</b> Acquisitions </font></li><li><font face="Arial"><b>Purpose:</b> Show's a ledger of all items ordered </font>
</li><li><font face="Arial"><b>Status:</b> Complete </font></li></ul>
<div class="mw-geshi" dir="ltr" style="TEXT-ALIGN: left">
<div class="sql source-sql">
<pre class="de1"><font face="Arial"><span class="kw1"><strong><font color="#993333">SELECT</font></strong></span> b<span class="sy0"><font color="#66cc66">.</font></span>name <span class="kw1"><strong><font color="#993333">AS</font></strong></span> vendor<span class="sy0"><font color="#66cc66">,</font></span> i<span class="sy0"><font color="#66cc66">.</font></span>itype<span class="sy0"><font color="#66cc66">,</font></span> p<span class="sy0"><font color="#66cc66">.</font></span>budget_branchcode <span class="kw1"><strong><font color="#993333">AS</font></strong></span> branch<span class="sy0"><font color="#66cc66">,</font></span> k<span class="sy0"><font color="#66cc66">.</font></span>basketno<span class="sy0"><font color="#66cc66">,</font></span>
o<span class="sy0"><font color="#66cc66">.</font></span>entrydate <span class="kw1"><strong><font color="#993333">AS</font></strong></span> <span class="st0"><font color="#ff0000">'order date'</font></span><span class="sy0"><font color="#66cc66">,</font></span> format<span class="br0"><font color="#66cc66">(</font></span>o<span class="sy0"><font color="#66cc66">.</font></span>listprice<span class="sy0"><font color="#66cc66">,</font></span><span class="nu0"><font color="#cc66cc">2</font></span><span class="br0"><font color="#66cc66">)</font></span> <span class="kw1"><strong><font color="#993333">AS</font></strong></span> <span class="st0"><font color="#ff0000">'list price'</font></span><span class="sy0"><font color="#66cc66">,</font></span>
format<span class="br0"><font color="#66cc66">(</font></span>o<span class="sy0"><font color="#66cc66">.</font></span>unitprice<span class="sy0"><font color="#66cc66">,</font></span><span class="nu0"><font color="#cc66cc">2</font></span><span class="br0"><font color="#66cc66">)</font></span> <span class="kw1"><strong><font color="#993333">AS</font></strong></span> <span class="st0"><font color="#ff0000">'unit price'</font></span><span class="sy0"><font color="#66cc66">,</font></span> o<span class="sy0"><font color="#66cc66">.</font></span>quantity<span class="sy0"><font color="#66cc66">,</font></span>
format<span class="br0"><font color="#66cc66">(</font></span>o<span class="sy0"><font color="#66cc66">.</font></span>totalamount<span class="sy0"><font color="#66cc66">,</font></span><span class="nu0"><font color="#cc66cc">2</font></span><span class="br0"><font color="#66cc66">)</font></span> <span class="kw1"><strong><font color="#993333">AS</font></strong></span> <span class="st0"><font color="#ff0000">'total amount'</font></span><span class="sy0"><font color="#66cc66">,</font></span> o<span class="sy0"><font color="#66cc66">.</font></span>datereceived <span class="kw1"><strong><font color="#993333">AS</font></strong></span> <span class="st0"><font color="#ff0000">'date received'</font></span>
<span class="kw1"><strong><font color="#993333">FROM</font></strong></span> aqbasket k
<span class="kw1"><strong><font color="#993333">LEFT</font></strong></span> <span class="kw1"><strong><font color="#993333">JOIN</font></strong></span> aqbooksellers b <span class="kw1"><strong><font color="#993333">ON</font></strong></span> <span class="br0"><font color="#66cc66">(</font></span>k<span class="sy0"><font color="#66cc66">.</font></span>booksellerid<span class="sy0"><font color="#66cc66">=</font></span>b<span class="sy0"><font color="#66cc66">.</font></span>id<span class="br0"><font color="#66cc66">)</font></span>
<span class="kw1"><strong><font color="#993333">LEFT</font></strong></span> <span class="kw1"><strong><font color="#993333">JOIN</font></strong></span> aqorders o <span class="kw1"><strong><font color="#993333">USING</font></strong></span> <span class="br0"><font color="#66cc66">(</font></span>basketno<span class="br0"><font color="#66cc66">)</font></span>
<span class="kw1"><strong><font color="#993333">LEFT</font></strong></span> <span class="kw1"><strong><font color="#993333">JOIN</font></strong></span> items i <span class="kw1"><strong><font color="#993333">USING</font></strong></span> <span class="br0"><font color="#66cc66">(</font></span>biblioitemnumber<span class="br0"><font color="#66cc66">)</font></span>
<span class="kw1"><strong><font color="#993333">LEFT</font></strong></span> <span class="kw1"><strong><font color="#993333">JOIN</font></strong></span> aqbudgets p <span class="kw1"><strong><font color="#993333">USING</font></strong></span> <span class="br0"><font color="#66cc66">(</font></span>budget_id<span class="br0"><font color="#66cc66">)</font></span></font></pre>
<pre class="de1"><span class="br0"><font face="Arial" color="#66cc66"></font></span> </pre>
<pre class="de1"><span class="br0"><font face="Arial">Respectfully,</font></span></pre>
<pre class="de1"><span class="br0"><div dir="ltr"><font face="Arial">James Ghiorzi</font></div><div dir="ltr"><font face="Arial">Library Assistant II</font></div><div dir="ltr"><font face="Arial">San Benito County Free Library</font></div><div dir="ltr"><font face="Arial">470 Fifth St.</font></div><div dir="ltr"><font face="Arial">Hollister, CA 95023</font></div><div dir="ltr"><font face="Arial">(831) 636-4107 </font></div></span><font face="Arial"><hr tabindex="-1"></font><font face="Tahoma" size="2"><b>From:</b> koha-bounces@lists.katipo.co.nz [koha-bounces@lists.katipo.co.nz] On Behalf Of Joel Harbottle [Joel.Harbottle@hotmail.com.au]<br><b>Sent:</b> Friday, July 29, 2011 3:39 AM<br><b>To:</b> Koha List<br><b>Subject:</b> [Koha] Acquisitions - Vendor Purchases History<br></font><br></pre>
</div>
</div>
</div>
<div></div>
<div><font face="Calibri, Verdana, Helvetica, Arial"><span style="FONT-SIZE: 11pt">Hi All,<br>
<br>
Just dropping a quick question for everyone. Does anyone know, if there is a to either view, directly in the Acquisitions, or via a generated Report, to get a list of all items purchased from each Vendor in the Acquisitions module?<br>
<br>
I’m just going through a few invoices but can’t find a way of searching them in Acquisitions... Is there a way... I would really love to have this functionality.<br>
<br>
Thanking you in advance :)<br>
<br>
Kindest Regards,<br>
Joel<br>
<br>
<br>
<br>
</span></font><font color="#000080"><font face="Cambria"><span style="FONT-SIZE: 12pt"><b>Joel Harbottle<br>
</b></span><font size="2"><span style="FONT-SIZE: 10pt">Library Technician<br>
<br>
Library Processing Services (Queensland)<br>
Email: <a href="UrlBlockedError.aspx" target="_blank">Library.Services@hotmail.com</a><br>
Web: <a href="http://libservices.wordpress.com/" target="_blank">http://libservices.wordpress.com/</a><br>
</span></font></font></font></div>
</body>
</html>