I'd like to see an option on the 'reports' menu where you can type in your own SQL statements, and have that produced as a report. ( Actinic is great, but it doesn't tie in easily with other packages I use )
Anyone else agree ?
Anyway, I wrote a javascript program (within a webpage), to do some SQL'ing.
If anyone is interested, here it is :-
Anyone else agree ?
Anyway, I wrote a javascript program (within a webpage), to do some SQL'ing.
If anyone is interested, here it is :-
HTML Code:
<html> <head> <style> body { font-family: arial; font-size: 8pt; } thead { background-color: navy; color: white; font-weight: bold; } td { padding: 4px; } </style> </head> <body> <script> /* This program :- 1. Connects to the Actinic database 2. Queries the database (using SQL) to get a table of all products and their quantity tiered pricing (eg 1-2 items=£5 each, 3-4 items=£4 each, 5+ items=£3 each) 3. Displays it 4. Closes the database connection */ var filename = "C:\\Program Files\\Actinic v7\\Sites\\Site1\\ActinicCatalog.mdb"; var adoCn, adoRs, price, lower_qty, upper_qty, desc, prev_desc, bgcolour; adoCn = new ActiveXObject("ADODB.Connection"); // Create ADO objects adoRs = new ActiveXObject("ADODB.Recordset"); try { adoCn.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=" + filename ); // Try to connect to database } catch(e) { document.writeln("Open error: "); document.writeln(adoCn.Errors(0).Description + "<br />"); } adoRs.ActiveConnection = adoCn; // Execute SQL command adoRs.Open( "SELECT p.[Short description], pp.[nValue2], pp.[sString1], pp.[sString2] " + "FROM Product AS p, ProductProperties AS pp " + "WHERE p.[Product reference] = pp.sProductRef AND pp.nType=4 " + "ORDER BY p.[Short description], pp.[nValue2] DESC" ); adoRs.MoveFirst(); // Iterate recordset document.writeln("<table><thead><tr><td>EX VAT</td><td>INC VAT</td><td>FROM QTY</td><td>TO QTY</td><td>DESC</td></tr></thead><tbody>"); bgcolour="#dddddd"; while(!adoRs.EOF) //~~~~~~~~~~~~~~~~~~ { price = "0" + adoRs("nValue2"); price = price.substr(1); // Get a record lower_qty = "0" + adoRs("sString1"); lower_qty = lower_qty.substr(1); // [Slight farce to get upper_qty = "0" + adoRs("sString2"); upper_qty = upper_qty.substr(1); // actual values, and not desc = "0" + adoRs("Short description"); desc = desc.substr(1); // by reference] // Change row colour when product name changes if (desc != prev_desc) { if (bgcolour=="#dddddd") bgcolour="#eeeeee"; else bgcolour="#dddddd"; } if (lower_qty=="null") lower_qty="-"; if (upper_qty=="null") upper_qty="-"; if (upper_qty==-1) upper_qty="+"; document.writeln("<tr bgcolor='" + bgcolour + "'>"); // Start a table row document.writeln( "<td>£" + (price/100).toFixed(2) + "</td>"); // Show price document.writeln( "<td>£" + (price/100*1.175).toFixed(2) + "</td>"); // Price with UK's Value Added Tax document.writeln( "<td>" + lower_qty + "</td>"); // Lower qty needed for customer to get this price document.writeln( "<td>" + upper_qty + "</td>"); // Upper qty needed document.writeln( "<td>" + desc + "</td>"); // Product name document.writeln("</tr>"); // Complete table row prev_desc = desc; // Description stored for row colour change (above) adoRs.MoveNext(); // Get next record } document.writeln("</tbody></table>"); // Close table adoRs.Close() // Close objects adoCn.Close(); delete adoCn; // Clean up delete adoRs; </script> </body> </html>
Comment