|
Post by meerkat on Dec 20, 2022 23:51:17 GMT -5
Why html and JS. One reason is to free up the server by running code on the client. HTML does all the checks for numeric, decimal, date popup and checks and more.As you know Javascript runs in browsers. Browsers run on all machines regardless of the OS. Sometimes we have applications that require a lot of CPU time from the server. In those cases we can write that section in Javascript. This frees up the server and it places the burden on the Client. Here is a simple HTML output done with LB print statements.If you save the code as a *.html document and then click on it, it will run in your browser.
<!DOCTYPE html> <html lang="en">
<head><title>-------- Game of Life ------</title></head>
<body> <h1>Game of Life</h1> <canvas id="canvas" width="800" height="400" style="border:1px solid lightgrey;"> Your browser does not support the HTML5 canvas tag. </canvas> <script>
let x = 0; let y = 0; let cellHeight = 3 let cellWidth = 3 // Make a grid class Cell { static width = 10; static height = 10;
constructor (context, X, Y) { this.context = context;
// Store the position of this cell in the grid this.X = X; this.Y = Y;
// Make random cells on this.on = Math.random() > 0.5; }
draw() { // Draw a simple square this.context.fillStyle = this.on?'#ff8080':'#303030'; this.context.fillRect(this.X * cellWidth, this.Y * cellHeight, cellWidth, cellHeight); } }
class GridUniverse {
static numX = 175; static numY = 140;
constructor(gridId) { this.canvas = document.getElementById(gridId); this.context = this.canvas.getContext('2d'); this.gameObjects = []; this.createWorld(); // get first time window.requestAnimationFrame(() => this.loop()); }
createWorld() { for (y = 0; y < GridUniverse.numY; y++) { for (x = 0; x < GridUniverse.numX; x++) { this.gameObjects.push(new Cell(this.context, x, y)); } } }
isOn(x, y) { if (x < 0 || x >= GridUniverse.numX || y < 0 || y >= GridUniverse.numY){ return false; }
return this.gameObjects[this.gridToIndex(x, y)].on?1:0; }
gridToIndex(x, y){ return x + (y * GridUniverse.numX); }
lookAround () { // Loop over all cells for (x = 0; x < GridUniverse.numX; x++) { for (y = 0; y < GridUniverse.numY; y++) {
// Count the cells around me let numOn = this.isOn(x - 1, y - 1) + this.isOn(x, y - 1) + this.isOn(x + 1, y - 1) + this.isOn(x - 1, y) + this.isOn(x + 1, y) + this.isOn(x - 1, y + 1) + this.isOn(x, y + 1) + this.isOn(x + 1, y + 1); let centerCell = this.gridToIndex(x, y);
if (numOn == 2){ // Do nothing this.gameObjects[centerCell].nextCell = this.gameObjects[centerCell].on; }else if (numOn == 3){ // Make on this.gameObjects[centerCell].nextCell = true; }else{ // Make dead this.gameObjects[centerCell].nextCell = false; } } }
// Apply the new state to the cells for (let i = 0; i < this.gameObjects.length; i++) { this.gameObjects[i].on = this.gameObjects[i].nextCell; } }
loop() { this.lookAround(); // Check each cell around me
// Clear the screen this.context.clearRect(0, 0, this.canvas.width, this.canvas.height);
// Draw all x and y for (let i = 0; i < this.gameObjects.length; i++) { this.gameObjects[i].draw(); }
// The loop function has reached it's end, keep requesting new frames setTimeout( () => { window.requestAnimationFrame(() => this.loop()); }, 100) } }
window.onload = () => { // The page has loaded, start the game let gameWorld = new GridUniverse('canvas'); }
</script> </body>
</html>
|
|
|
Post by tenochtitlanuk on Dec 21, 2022 18:18:46 GMT -5
Thanks for your recent contributions, Meerkat. I keep intending to get into real database stuff, and enjoyed playing with RunBasic when it first came out, and you've provided a lot for me to get my teeth into if I have time. Keep it coming- and Happy Christmas.
|
|
|
Post by meerkat on Dec 22, 2022 4:47:24 GMT -5
Reference files. You always need cross reference files. You need them to ensure referential integrity. A dropdown box with state id will ensure that the user only enters valid state id's. Whoa! Now that's integrity.
It's a lot of work keeping separate tables like a state file to cross reference state id to state name, another file for country id to country name. and another for product types.
A simple trick is to use a single cross reference file. I use a file called xlate that translates most everything. The xlate file has the following fields;fieldName - this is usually the name of a field in other tables such as 'stateId' val - The value. In a state xref it would contain the state id such as 'CA','OR', 'NY'. descr - Is the translated description such as 'California','Oregon','New Work' short - A short description. Many times, especially in reports, you only want to see an abbreviation instead of the fill name. For example 'Calif','Orgn','NY'.
A sample; ' -------------------------------------------------------- ' Table structure for: xlate ' -------------------------------------------------------- CREATE TABLE xlate ( fieldName CHAR(16), val CHAR(4), descr CHAR(35), short CHAR(10), def CHAR(1), seq CHAR(5) ); CREATE UNIQUE INDEX xlateFieldName ON xlate(fieldName,val); CREATE INDEX xlateFieldSeq ON xlate(fieldName,seq);
' -------------------------------------------------------- ' sample xlate info ' --------------------------------------------------------
INSERT INTO xlate VALUES ('event','vac','Vacation','Vacation','','vac') ,('event','sic','Sick','Sick','','sic') ,('event','met','Meeting','Meeting','','met') ,('event','oth','Other','Other','','oth') ,('assetType','con','Conference Room','Conf Room','','con') ,('assetType','mov','Movie Room','Movie','','mov') ,('assetType','met','Meeting Room','Meet','','met') ,('assetType','rec','Recreation Room','Rec Room','','rec') ,('siteHdr','','Calendar Schedule','Calendar','','') ,('menuId','CAL','Calendar','Calendar','','10') ,('menuId','MNT','Maintenance','Maint','','14') ,('menuId','SEC','Security','Security','','16') ,('state','AK','Alaska','Alaska','U','AK') ,('state','AL','Alabama','Alabama','U','AL') ,('state','AR','Arkansas','Arkansas','U','AR') ,('state','AS','America Samoa','America Sa','U','AS') ,('state','AZ','Arizona','Arizona','U','AZ') ,('state','CA','California','California','U','CA') ,('state','CO','Colorado','Colorado','U','CO') ,('state','CT','Connecticut','Connecticu','U','CT') ....... ,('yesNo','Y','Yes','Yes','','1') ,('yesNo','N','No','No','','1')
|
|
|
Post by Rod on Dec 22, 2022 5:28:35 GMT -5
Ahh, nice, one file, many purposes.
|
|
|
Post by meerkat on Dec 22, 2022 16:02:00 GMT -5
Comparing fields in SQL.
When you compare fields in SQLite if they are equal you get 1 if not you get 0.
You have a bills table as: TABLE bills ( type CHAR(3), billDate DATE, postDate DATE, descr VARCHAR(35), amount DECIMAL(8,2) )
bill types are; utl = utility fod = food ins = insurance rnt = rent oth = other.
In a SELECT, inseted of simply getting the amount as a single amount column, you want it also into seperate bill type columns. Simple since a equal compare gives you 1 you can spread the amount into type columns as:
SELECT bills.billDate, bills.postDate, bills.descr, bills.amount, ((bills.type = 'utl') * bills.amount as utlAmt, ((bills.type = 'fod') * bills.amount as fodAmt, ((bills.type = 'ins') * bills.amount as insAmt, ((bills.type = 'rnt') * bills.amount as rntAmt, ((bills.type = 'oth') * bills.amount as othAmt
Remember a equal comparison gives you 1 for equal and 0 for not equal. If the type was 'fod' then the comparison of (bills.type = 'fod') is 1. 1 times the amount puts it into the fodAmt column. The others will be 0
Too simple.
|
|
|
Post by meerkat on Dec 24, 2022 8:03:37 GMT -5
You are correct metro.
I'm running into record locks.
So not much reason to continue posting SQLite examples..
I ran the example on other languages and it works ok.
Thanks everyone for your support..
begTime = time$("ms") sqliteconnect #sql, ":memory:" ' if it doesn't exist , make it sql$ = "DROP TABLE IF EXISTS x" ' we may have run this before so drop it #sql execute(sql$) ' create table sql$ = "CREATE TABLE x(a1,a2,a3)" ' table x with 3 fields a1,a2, and a3 #sql execute(sql$) ' create table ' ------------------------------------ ' make 1000 records with random stuff ' ------------------------------------ for i = 1 to 300 a1$ = chr$((rnd(0) * 24) + 64) a2$ = chr$((rnd(0) * 24) + 64) a3$ = chr$((rnd(0) * 24) + 64) sql$ = "INSERT INTO x VALUES('";a1$;"','";a2$;"','";a3$;"')" #sql execute(sql$) next i
' ----------------------------------------- ' change all a2 with a 'C' to 'C1' ' for all records that match on a1 ' ----------------------------------------- ' you will need another connection to the database 'test' ' one to read and one to write ' ------------------------------------------------- sqliteconnect #sql1, "test" ' another connection to test sql$ = " SELECT x1.rowid as id,x.rowid as idx FROM x JOIN x as x1 ON x1.a1 = x.a1 AND x1.rowid <> x.rowid AND x1.a2 = 'C' WHERE x.a1 = x1.a1 group by x1.rowid"
#sql execute(sql$)
WHILE #sql hasanswer() #row = #sql #nextrow() id$ = #row id$() 'you need the rowid to identify the record idx$ = #row idx$() sql1$ = "UPDATE x SET a2 = 'C1' WHERE rowid = ";id$ 'change a2 from 'C' to 'C1' print idx$,id$ #sql1 execute(sql1$) WEND
#sql disconnect() #sql1 disconnect() endTime = time$("ms") print "Total Time:";endTime - begTime end
|
|
|
Post by pierre on Dec 24, 2022 9:42:46 GMT -5
You are correct metro. I'm running into record locks. So not much reason to continue posting SQLite examples.. I ran the example on other languages and it works ok. Thanks everyone for your support..
Are you sure you were testing with the latest version of LB5 ? I ran your program under Windows 10 with LB5 alpha Build 353 and there were NO LOCKS. Everything works fine ! pierre
|
|
|
Post by meerkat on Dec 24, 2022 10:22:25 GMT -5
Thanks pierre..
Wow! I hope you are right. I'm using build 351 with windows 11. I'll give 353 a try. If it doesn't work with 353 then it could be SQLite. I think I'm using the latest release of SQLite. I'll see what I can do.
Dan
|
|
|
Post by meerkat on Dec 24, 2022 10:44:45 GMT -5
You are correct. Using 353 it works. This is great news!
I did have a error in the above program.. I fixed it if you want to copy and try it again. I should have been using the second sql connect whenn updating the record. Insetea of #sql execute(sql1$) it should have been #sql1 execute(sql1$) What was happening is after the update of #sql execute(sql1$) it basically destroyed the cursor for the #sql SELECT and after the first update it basically quit. The change should go through the whole file. I set the for loop to generate records to about 1000 just to give it a larger test. and it worked.
Thanks again pierre. Dan
|
|
|
Post by meerkat on Dec 24, 2022 11:28:03 GMT -5
There is always a silver lining. The above program gives us a perfect opportunity to show a better way. It's not an unusual way of hunting for matches and changing records. However, there is a faster and easier way using sub SELECTS.
The "IN" verb in SQL is an easy way to test for multiple values. For example if "WHERE a IN (1,3,5)" it will find all a's that have a 1,3 or 5.
In the example here, we find all records that match what we want and hold their record id in the IN statement. So it UPDATES all records that are IN the records that match what we want.
begTime = time$("ms") sqliteconnect #sql, ":memory:" ' if it doesn't exist , make it sql$ = "CREATE TABLE x(a1,a2,a3)" ' table x with 3 fields a1,a2, and a3 #sql execute(sql$) ' create table ' ------------------------------------ ' make 1000 records with random stuff ' ------------------------------------ for i = 1 to 300 a1$ = chr$((rnd(0) * 24) + 64) a2$ = chr$((rnd(0) * 24) + 64) a3$ = chr$((rnd(0) * 24) + 64) sql$ = "INSERT INTO x VALUES('";a1$;"','";a2$;"','";a3$;"')" #sql execute(sql$) next i
' ----------------------------------------- ' change all a2 with a 'C' to 'C1' ' for all records that match on a1 ' ----------------------------------------- ' you will need another connection to the database 'test' ' one to read and one to write ' -------------------------------------------------
sql$ = "UPDATE x SET a2 = 'C1' WHERE x.rowid IN (SELECT rowid FROM x as x1 WHERE x1.rowid <> x.rowid AND x1.a2 = 'C')"
#sql execute(sql$)
#sql disconnect()
endTime = time$("ms") print "Total Time:";endTime - begTime end
Run Time 52 ms in RB 24 ms in LB
|
|
|
Post by pierre on Dec 25, 2022 15:42:44 GMT -5
O.K.
Here we are again.
By chance, I kept a copy of the first version posted Dec 24 2:03 pm. I didn't see the problem immediately, but here it is.
1) There were no locks because the two cursors were not talking to the same database:
#sql created an in-memory database and populated it with 300 records.This cursor had only access to the in-memory database. #sql1 created an empty on-disk database named 'test'.This cursor had only access to the 'test' database.
2) As far as I could understand, the SELECT statement - joining the table 'x' with itself etc. - established a list of rowids with field a2 having the value 'C' ---> that was second column (id$) of the output. I don't know what the first column (idx$) meant, apparently some grouped data without any further usefulness (?). This query had to be executed with the #sql cursor - the one that had access to the in-memory database - so that was O.K.
Executing the UPDATE query with cursor #sql while retrieving the answer from the SELECT query, did not lock the database, but gave a truncated answer (only one row), without an error message. Then the program ended there and seemed to have cut off further results.
IMHO this is because you cannot read and write with the same cursor within a single transaction. Fetching the results is also part of the previous SELECT query, so the same cursor should not write in between, not even for the first result... I cannot explain why it did.
3) Executing the UPDATE query with cursor #sql1 (the corrected version) gave the following results: Cursor sql1 created and opened an empty database on disk with the name 'test'. The program ran and stopped with an error 'no such table' for every row retrieved by the previous SELECT statement. This was normal because the cursor #sql1 only was able to see the 'test' database on disk and there was no table 'x'. If by chance one had already a 'test' database on disk with a created 'x' table, then the program would continue normally and you would see the complete output of the SELECT query.
But the data ('C' value of field a2) in the in-memory database would NOT change, because of the cursor #sql1 not having access to that particular database.
I have tested that. There were no changes made.
As it is not possible to have more than one connection to an in-memory database (see the sqlite documentation), the example should have been made with one on-disk database named 'test' and two different cursors #sql and #sql1 connected to that database.
====> Doing that, we are exposed to a database lock. Why ? Because of the fact that the UPDATE cursor #sql1 has to change the data, not based on information directly available in the database, but based on the results of a particular recordset made by the SELECT cursor #sql and that is something different in memory.
We have to encapsulate both queries, SELECT and UPDATE in a "begin transaction" - "commit transaction" of the #sql1 cursor to make it work.
That is apparently the way the SmallTalk SQLite3 API works. Other languages may have different interfaces and different behaviors.
begTime = time$("ms") sqliteconnect #sql, "test.db" '<======== ' if it doesn't exist , make it sql$ = "DROP TABLE IF EXISTS x" ' we may have run this before so drop it #sql execute(sql$) ' create table sql$ = "CREATE TABLE x(a1,a2,a3)" ' table x with 3 fields a1,a2, and a3 #sql execute(sql$) ' create table
' ------------------------------------ ' make 1000 records with random stuff ' ------------------------------------
for i = 1 to 300 a1$ = chr$((rnd(0) * 24) + 64) a2$ = chr$((rnd(0) * 24) + 64) a3$ = chr$((rnd(0) * 24) + 64) sql$ = "INSERT INTO x VALUES('";a1$;"','";a2$;"','";a3$;"')" #sql execute(sql$) next i
' ----------------------------------------- ' change all a2 with a 'C' to 'C1' ' for all records that match on a1 ' ----------------------------------------- ' you will need another connection to the database 'test' ' one to read and one to write ' ------------------------------------------------- sqliteconnect #sql1, "test.db" ' another connection to test #sql1 execute("begin transaction") '<========= sql$ = " SELECT x1.rowid as id,x.rowid as idx FROM x JOIN x as x1 ON x1.a1 = x.a1 AND x1.rowid <> x.rowid AND x1.a2 = 'C' WHERE x.a1 = x1.a1 group by x1.rowid"
#sql execute(sql$)
WHILE #sql hasanswer() #row = #sql #nextrow() id$ = #row id$() 'you need the rowid to identify the record idx$ = #row idx$() sql1$ = "UPDATE x SET a2 = 'C1' WHERE rowid = ";id$ 'change a2 from 'C' to 'C1' print idx$,id$ #sql1 execute(sql1$) WEND
#sql1 execute("commit") '<========
print
'---------------------------------------------------- 'now we can see if changes have been made (<======== 'avoid to print out the whole database
sql$ = "select rowid, a2 from x where a2 = 'C1'" print sql$ #sql execute(sql$)
if #sql rowcount() = 0 then print "no records found" goto [getOut] end if
WHILE #sql hasanswer() #row = #sql #nextrow() rowid = #row rowid() a2$ = #row a2$() print rowid, a2$ wend
[getOut]
'------------------------------------------------------
#sql disconnect() #sql1 disconnect() print endTime = time$("ms") print "Total Time:";endTime - begTime end
The last example, simplifying the approach by using a unique cursor #sql, does not give the correct results.
Adding the same test as the above explained, we can see that the changes have not been made...We should always test the results, particularly when using in-memory databases.
So, why ? I think that the sub-query does not do anything. In this simple case, there are only one database and one table. The aim was: change all a2 with a 'C' to 'C1' << for all records that match on a1 >>".
You may call the table 'x' or 'x1', it is the same table and all values x.a1 are equal to x1.a1 and x.a2 are equal to x1.a2...... Am I missing something ?
So we have to replace the line: << FROM x as x1 WHERE x1.rowid <> x.rowid >> with << FROM x as x1 WHERE x1.rowid = x.rowid >> and it works....
begTime = time$("ms") sqliteconnect #sql, ":memory:" ' if it doesn't exist , make it sql$ = "CREATE TABLE x(a1,a2,a3)" ' table x with 3 fields a1,a2, and a3 #sql execute(sql$) ' create table ' ------------------------------------ ' make 1000 records with random stuff ' ------------------------------------ for i = 1 to 300 a1$ = chr$((rnd(0) * 24) + 64) a2$ = chr$((rnd(0) * 24) + 64) a3$ = chr$((rnd(0) * 24) + 64) sql$ = "INSERT INTO x VALUES('";a1$;"','";a2$;"','";a3$;"')" #sql execute(sql$) next i
' ----------------------------------------- ' change all a2 with a 'C' to 'C1' ' for all records that match on a1 ' ----------------------------------------- ' you will need another connection to the database 'test' ' one to read and one to write ' -------------------------------------------------
sql$ = "UPDATE x SET a2 = 'C1' WHERE x.rowid IN (SELECT rowid FROM x as x1 WHERE x1.rowid = x.rowid '<===== replaced <> with = AND x1.a2 = 'C')" #sql execute(sql$)
'--------------------------------------------------------------- 'now we can see if changes have been made (<======== 'avoid to print out the whole database sql$ = "select rowid, a2 from x where a2 = 'C1'" print sql$ #sql execute(sql$)
if #sql rowcount() = 0 then print "no records found" goto [getOut] end if
WHILE #sql hasanswer() #row = #sql #nextrow() rowid = #row rowid() a2$ = #row a2$() print rowid, a2$ wend
[getOut]
'---------------------------------------------------------------
#sql disconnect() endTime = time$("ms") print "Total Time:";endTime - begTime end
pierre
|
|
|
Post by meerkat on Dec 25, 2022 18:43:10 GMT -5
Ya! Thanks for pointing out the problem..
Don't know why I even bothered with comparing the rowid's. Must have had something else on my mind. All I wanted in the sub SELECT was all records with a2 = 'C'. Who cares about comparing rowid's. It needs do is pass all the rowid's that have a2 = 'C' to the UPDATE that will be IN those rowid's regardless of the rowid compare. It should have been:
sql$ = "UPDATE x SET a2 = 'C1' WHERE x.rowid IN (SELECT rowid FROM x as x1 WHERE x1.a2 = 'C')" #sql execute(sql$)
Thanks for the update.
I guess I was trying to point out the use of sub SELECTS. We don't need it here we could simple have said sql$ = "UPDARE x SET a2 = 'C1' WHERE a2 = 'C'"
Lots of reasons for a subSelect. One reason a sub SELECT could be necessare is when you have a LIMIT on the nymber of updates. You cannot use LIMIT in a UPDATE. But you can do it with a sub SELECT as ; sql$ = "UPDATE x SET x2 = 'C1' WHERE rowid IN (SELECT rowid FROM x as x1 WHERE x1.a2 = 'C' LIMIT 10)"
|
|
|
Post by meerkat on Dec 28, 2022 8:32:06 GMT -5
Scheduling Assets. SQLite has some useful time control. A good use if time is scheduling.
This is an attempt to schedule company assets such as conference rooms, access to the VP, projectors and others. This is a simple schedule. It does not take into consideration what days and/or times assets are available and other considerations. Like always there are probably better ways to do this, but this is one way.
Just to show example joins, it includes joins and outer joins of tables and translations.
The user has options and needs: 1. The asset selection 2. Asset type (optional) If they select type it overrides the asset selection. For example you may want a conference room type and don't care what conference run it selects. 3. Date to begin 4. Days of week. (options). May only want the schedule on Wednesdays or Fridays 5. Number of possible selections to show. You may not want to look at hundreds of times available but only the first 10
This example creates no files, everything is in memory.
Have a great new year..
begTime = time$("ms")
' --------- make memory database ------------------ sqliteconnect #mem, ":memory:" sql$ = " CREATE TABLE asset ( assetNum INT(3), assetType VARCHAR(3), short VARCHAR(10), descr VARCHAR(30) )" #mem execute(sql$)
sql$ = "INSERT INTO asset VALUES (1,'con','Com A','Conference Room A') ,(2,'con','Com B','Conference Room B') ,(4,'rec','Rec Room 2','Recreation Room 2') ,(3,'rec','Rec Room 1','Recreation Room 1') ,(6,'met','Meet Rm 1','Meeting Room 1') ,(5,'met','Meet Rm 2','Meeting Room 2') ,(7,'mov','Movie Rm','Movie Room') ,(8,'equ','Projector','Porjector (Over Head)')" #mem execute(sql$)
sql$ = "CREATE TABLE schTime ( schNum INT(3), userNum INT(3), schBeg DATE, schEnd DATE, assetNum INT(3), event CHAR(4), name VARCHAR(10), descr VARCHAR(50) )" #mem execute(sql$)
sql$ = "INSERT INTO schTime VALUES (8,2,'2021-04-01 05:44','2021-04-01 06:44',1,'met','my meeting','whatever') ,(9,2,'2021-04-05 07:46','2021-04-05 08:46',1,'sls','Hawaii','Our west coast division') ,(0,3,'2021-04-05 08:01','2021-04-05 08:21',1,'met','President','Yearly president update') ,(3,2,'2021-04-01 08:30','2021-04-01 09:30',1,'met','Sch Financial','Meeting with Schentific Financial') ,(6,3,'2021-09-29 08:00','2021-09-29 09:15',1,'met','Production','Production meeting') ,(2,3,'2021-10-01 04:00','2021-10-01 05:00',1,'met','Auditor','Here come the auditors') ,(5,2,'2021-10-01 06:00','2021-10-01 07:00',1,'met','Mexico','Our southern regional meeting') ,(4,1,'2021-10-01 10:30','2021-10-01 10:45',1,'sls','Promotion','Customer promotion') ,(1,1,'2021-11-07 08:45','2021-11-16 09:45',1,'sls','Sales Meeting','Upper management') ,(7,1,'2021-12-26 01:25','2021-12-31 02:16',1,'met','Year end','Year end product sales') " #mem execute(sql$)
sql$ = "CREATE TABLE xlate ( fieldName CHAR(16), val CHAR(4), descr CHAR(35), short CHAR(10), def CHAR(1), seq CHAR(5) )" #mem execute(sql$)
sql$ = "INSERT INTO xlate VALUES ('event','vac','Vacation','Vacation','','vac') ,('event','sic','Sick','Sick','','sic') ,('event','met','Meeting','Meeting','','met') ,('event','oth','Other','Other','','oth') ,('assetType','con','Conference Room','Conf Room','','con') ,('assetType','mov','Movie Room','Movie','','mov') ,('assetType','met','Meeting Room','Meet','','met') ,('assetType','rec','Recreation Room','Rec Room','','rec')" #mem execute(sql$)
'---------------- User Input Parameters -------------------------------------- needMins = 10 ' this is how many minutes you need scheduled needBegDate$ = "2021-01-01" ' the date that you want the schedule to begin looking shoTimes = 10 ' only show this number of possible open times needAssetNum = 1 ' what asset to schedule needDow$ = "0,3,2,4" ' Days of week need 0 -> 6, sun -. sat needType$ = "con" ' Only care about type - don't need exact asset ' in this case you only care about type 'con'ference room ' and don't care what one it picks '----------------------------------------------------------------------------
dow$ = "SunMonTueWedThrFriSat"
' if they ask for type than use that instead of actual asset number if needType$ <> "" then findAsset$ = "assetNum = ";needAssetNum else findAsset$ = "assetType = '";needType$;"'" end if
sql$ = " SELECT s.schNum, s.assetNum, s.schEnd as preEnd, s.event, evnt.short as eDescr, s1.schBeg as nxtBeg, ((strftime('%s',s1.schBeg) - strftime('%s',s.schEnd)) / 60) as laps, (strftime('%w',s.schEnd)) + 0 as dowBeg, substr(' JanFebMarAprMayJunJulAugSepOctNovDec',strftime('%m', s.schBeg) * 3,3) as mth, asset.descr as aDescr, asset.assetType, asetTyp.short as atDescr
FROM schTime as s
JOIN asset ON asset.assetNum = s.assetNum
LEFT JOIN xlate as asetTyp ON asetTyp.fieldName = 'assetType' AND asetTyp.val = asset.assetType
LEFT JOIN xlate as evnt ON evnt.fieldName = 'event' AND evnt.val = s.event
JOIN schTime as s1 ON s1.assetNum = s.assetNum AND s1.schBeg > s.schEnd AND ((strftime('%s',s1.schBeg) - strftime('%s',s.schEnd)) / 60) >= ";needMins;" AND s1.schBeg = ( SELECT min(s2.SchBeg) FROM schTime as s2 WHERE s2.assetNum = s.assetNum AND s2.schBeg > s.schEnd ) WHERE asset.";findAsset$;" AND dowBeg IN (";needDow$;") AND s.schBeg >= ";needBegDate$;" ORDER BY s1.schBeg LIMIT ";shoTimes
print print "------+-------+---------------+---+----------------+----------------+----------+"; print "------+--------------------+---------------+" print align$("sch#",6,"c");"|"; print align$("asset#",7,"l");"|"; print align$("event",15,"c");"|"; print "Mth|"; print align$("preEnd",16,"c");"|"; print align$("nxtBeg",16,"c");"|"; print align$("laps",10,"c");"|"; print align$("dowBeg",6,"l");"|"; print align$("Descr",20,"c");"|"; print align$("Type",15,"c");"|" print "------+-------+---------------+---+----------------+----------------+----------+"; print "------+--------------------+---------------+"
#mem execute(sql$) rows = #mem ROWCOUNT() 'Get the number of rows
WHILE #mem hasanswer() #row = #mem #nextrow() schNum$ = #row schNum$() assetNum$ = #row assetNum$() event$ = #row event$() eDescr$ = #row eDescr$() mth$ = #row mth$() preEnd$ = #row preEnd$() nxtBeg$ = #row nxtBeg$() laps$ = #row laps$() dowBeg$ = #row dowBeg$() dowBeg = #row dowBeg() aDescr$ = #row aDescr$() assetType$ = #row assetType$() atDescr$ = #row atDescr$() dow = dowBeg * 3 +1
print align$(schNum$,6,"r");"|"; print align$(assetNum$,7,"c");"|"; print align$(event$,4,"l");" "; print align$(eDescr$,10,"l");"|"; print mth$;"|"; print align$(preEnd$,16,"l");"|"; print align$(nxtBeg$,16,"l");"|"; print align$(laps$,10,"r");"|"; print align$(dowBeg$+" "+mid$(dow$,dow,3),6,"c");"|"; print align$(aDescr$,20,"l");"|"; print align$(assetType$,4,"c");" "; print atDescr$ WEND
#mem disconnect() endTime = time$("ms") print "Total Time:";endTime - begTime end
' ------------------------------------- ' Align fld$ to left right or center ' of a given width ' ------------------------------------- FUNCTION align$(fld$,width,lrc$) s = width - len(fld$) fld$ = left$(fld$,width) if s < 1 then align$ = fld$ else b$ = " " if lrc$ = "l" then align$ = fld$;left$(b$,s) if lrc$ = "r" then align$ = left$(b$,s);fld$ if lrc$ = "c" then align$ = left$(b$,int(s / 2));fld$;left$(b$,int(s / 2) + (s and 1)) end if END FUNCTION
|
|
|
Post by pierre on Dec 28, 2022 15:27:05 GMT -5
Nice example.
Took me a while to go through the whole query.
Event 'sls' , probably stands for 'Sales' or 'Sales Review' is missing in the cross reference table.
pierre
|
|
|
Post by meerkat on Dec 29, 2022 5:17:05 GMT -5
Thanks pierre, I didn't want sls to have a translation just to show that a LEFT JOIN will return the record without the RIGHT half. If I had used a JOIN instead of a LEFT JOIN the record would not have showed up. I put some time functions in the query like Day of Month just to show how get it. So actually we did not need to translate anything for this to work. The code was ripped out of one of my program that takes care of everything. Like when assets are available. Some are only available on certain week days, some are down for maintenance, others are only available on certain calendar days, weeks, or months. So it has maintenance to maintain when events are available. It was just to much for a demo and the query is a couple pages long. Dan
|
|