[SOLVED]DATASET Doesn't Filter as Expected

[kito]

Here is the story, in brief, starting from the beginning:

"There is a group of workers who are employed on fixed-term contracts. Each worker is entitled to days off each month(at least 16 days from the first month) in contract = 3.33 days off, that is where CO comes from CO = Total of days off.

Code: Pascal  [Select][+][-]

1.   function TDataModule1.CONJ(A:TDate;B:TDate):float   ;
2.     var
3.       s:float   ;
4.         v:string;
5.     begin
6.      if (DayOf(A)) < 16 then
7.       begin
8.      v := ((MonthsBetween(B,A,True)+1)*3.33).ToString;
9.       end
10.      else
11.       v := ((MonthsBetween(B,A,True))*3.33).ToString;
12.      s:= round(StrToFloat(v));
13.     Result := s ;
14.     end;        
15.  

the vacation begins on ST and ends on ED, while DREP (date_de_reprise) is the day of resumption and must be a working day. Taking into account that the weekend in my region consists of Friday and Saturday, the worker cannot resume work during these two days.

Now we're getting somewhere!
Meaning, your first working day of the week is actually the Sunday!

I wanted the interface to be as simple as possible, where interaction is done through a dbgrid. Therefore, I avoided making the user go up or down to edit or add anything, just like how we deal with tables in spreadsheets

adjusted VAC2-Table in attachment
How to use: You only INSERT/UPDATE Fields ST and CO,  ED and DREP are recalculated everytime you ACCESS those two fields (a.k.a reading/SELECTing them).
You don't need youor "CalcField" anymore
Just look at VAC2 directly with any viewer of your choice.
As said: Requires SQLite 3.31 or later

Wow    I can't believe my eyes   I didn't expect, or even think, that it could be possible to achieve this  with SQLite thank you  Zvonic you made my day!

[Zvoni]

I don't want to complicate things further, but 'workday' is not the same as 'not weekend'. There are also official holidays, some of them not fixed at a date.
Additional 'workday' calendar should be maintained separately.

Be quiet! 
Don't open another can of worms 

[kito]

Only the fkInternalCalc and fkAggregate fields can be used in filtering .

I would not call myself a database guru, but I doubt that this statement is correct in general. The attached project uses a TBufDataset which has three predefined fields, IntField, Floatfield and TextField, plus a calculated field, IntFieldPlus1, which adds 1 to the IntField value. As you can see, filtering on the fkCalculated IntFieldPlus1 field works perfectly here.

The next step would be to study a SQLite3 database in the same way, in both LCL SqlDB and in the ZEOS implementations.

Unfortunately working with persistent fields in the IDE is quite a mess...

I tested it and indeed it filters a calculated field
wp I'm afraid I might be using another wrong method. Your observation is in place. It's good that you noticed that

[alpine]

I don't want to complicate things further, but 'workday' is not the same as 'not weekend'. There are also official holidays, some of them not fixed at a date.
Additional 'workday' calendar should be maintained separately.

Be quiet! 
Don't open another can of worms 

Okay 
But I feel like he's just getting introduced to the concept of date/time/DB handling. Besides the issues with LCL DB, Zeos, sqlite, etc.

[kito]

I don't want to complicate things further, but 'workday' is not the same as 'not weekend'. There are also official holidays, some of them not fixed at a date.
Additional 'workday' calendar should be maintained separately.

Yes alpine      nice observation  and  also the starting day mustn't be  a weekend or holiday either that's why I  used master/detail  relationship
 I made a table  OCA (each year must be manually updated ) :

Code: Pascal  [Select][+][-]

1. CREATE TABLE "OCA" (
2.         `ID`    INTEGER,
3.         `DEF`   VARCHAR ( 50 ),
4.         `ST`    VARCHAR ( 50 ),
5.         PRIMARY KEY(`ID`)

and I add every official holidays    (DEF:name of holiday/event ; ST: date   ) and I linked  VAC dataset with  OCA datasource linked field is  ST .

and after editing done  it goes like that :

Code: Pascal  [Select][+][-]

1. if  oca.recordcount <> 0 then  ED := ED+1 //  so recalculate DREP

and for starting day the same thing :

Code: Pascal  [Select][+][-]

1. procedure TForm1.DBGrid1PrepareCanvas(sender: TObject; DataCol: Integer;
2.   Column: TColumn; AState: TGridDrawState);
3. begin
4.   if Column.FieldName='STC' then
5.   begin
6.  
7.         if  (DayOfWeek(  Column.Field.AsDateTime )=6 )or (DayOfWeek( Column.Field.AsDateTime )=7 ) or (oca.recoredcount <> 0 ) then
8.  
9.     begin
10.       with (Sender As TDBGrid) do
11.       begin
12.    
13.         Canvas.Brush.Color:=clYellow;
14.         Canvas.Font.Color:=clRed;
15.         Canvas.Font.Style:=[fsBold];
16.       end;
17.     end;
18. end;
19.  
20. end;      

     

[Zvoni]

Yes alpine      nice observation  and  also the starting day mustn't be  a weekend or holiday either that's why I  used master/detail  relationship
 I made a table  OCA (each year must be manually updated ) :

Code: Pascal  [Select][+][-]

1. CREATE TABLE "OCA" (
2.         `ID`    INTEGER,
3.         `DEF`   VARCHAR ( 50 ),
4.         `ST`    VARCHAR ( 50 ),
5.         PRIMARY KEY(`ID`)

and I add every official holidays    (DEF:name of holiday/event ; ST: date   ) and I linked  VAC dataset with  OCA datasource linked field is  ST .

Well, to stay in the Spirit of this:
Could also easily be done in SQLite directly (untested, since it's been a long time i've done something like that):
Introduce a new Column "Offset" (or whatever you like to call it),
create two Triggers (After INSERT and After UPDATE), which checks if the calculated ED-Date falls on such a Holiday.
If yes, Offset=1 else Offset=0
The generated columns just have to be adjusted with that Offset

And now i'm going to introduce further complications:
What about if there are two consecutive Holidays?
Christmas-Day and Boxing-Day come to mind.
Your offset ED:=ED+1 wouldn't work

[kito]

I don't want to complicate things further, but 'workday' is not the same as 'not weekend'. There are also official holidays, some of them not fixed at a date.
Additional 'workday' calendar should be maintained separately.

Be quiet! 
Don't open another can of worms 

   I am sure there are a lot 

I don't want to complicate things further, but 'workday' is not the same as 'not weekend'. There are also official holidays, some of them not fixed at a date.
Additional 'workday' calendar should be maintained separately.

Be quiet! 
Don't open another can of worms 

Okay 
But I feel like he's just getting introduced to the concept of date/time/DB handling. Besides the issues with LCL DB, Zeos, sqlite, etc.

Indeed, in this discussion, I have corrected many misconceptions. Thank you very much guys, I am very grateful for your help and the way not neglecting any remarque

[kito]

Well, to stay in the Spirit of this:
Could also easily be done in SQLite directly (untested, since it's been a long time i've done something like that):
Introduce a new Column "Offset" (or whatever you like to call it),
create two Triggers (After INSERT and After UPDATE), which checks if the calculated ED-Date falls on such a Holiday.
If yes, Offset=1 else Offset=0
The generated columns just have to be adjusted with that Offset

Sounds great! a much way better

And now i'm going to introduce further complications:
What about if there are two consecutive Holidays?
Christmas-Day and Boxing-Day come to mind.
Your offset ED:=ED+1 wouldn't work

what about :

Code: Pascal  [Select][+][-]

1. while oca.recoredcount <> 0 do :
2. begin
3.  ED := ED+1;
4. dataset.applyupdates ;
5.  
6. end ;

[alpine]

*snip*
what about :

Code: Pascal  [Select][+][-]

1. while oca.recoredcount <> 0 do :
2. begin
3.  ED := ED+1;
4. dataset.applyupdates ;
5.  
6. end ;

 "Careful with That Axe, Eugene"

You're introducing an endless loop
Better: ED:=ED+oca.recoredcount; but it is not correct either.

Things can go very hairy pretty quickly. Enter the "time spans" abstraction. The idea is:
When the end of the period falls into a holiday time-span (e.g. start..end or start..start+duration) then move it after the end.
Thus you should introduce additional column to the holidays table, either end date or duration. And to replace linked table with a parametrized query.
Also, when you edit the holidays table, care must be taken not to allow adjacent holiday spans, or to merge them in the query.

[kito]

*snip*
what about :

Code: Pascal  [Select][+][-]

1. while oca.recoredcount <> 0 do :
2. begin
3.  ED := ED+1;
4. dataset.applyupdates ;
5.  
6. end ;

 "Careful with That Axe, Eugene"

You're introducing an endless loop
Better: ED:=ED+oca.recoredcount; but it is not correct either.

Things can go very hairy pretty quickly. Enter the "time spans" abstraction. The idea is:
When the end of the period falls into a holiday time-span (e.g. start..end or start..start+duration) then move it after the end.
Thus you should introduce additional column to the holidays table, either end date or duration. And to replace linked table with a parametrized query.
Also, when you edit the holidays table, care must be taken not to allow adjacent holiday spans, or to merge them in the query.

that's not even a can of warm I made a black hole !

[Zvoni]

Well, to stay in the Spirit of this:
Could also easily be done in SQLite directly (untested, since it's been a long time i've done something like that):
Introduce a new Column "Offset" (or whatever you like to call it),
create two Triggers (After INSERT and After UPDATE), which checks if the calculated ED-Date falls on such a Holiday.
If yes, Offset=1 else Offset=0
The generated columns just have to be adjusted with that Offset

Sounds great! a much way better

Here we go:
Important: You have to enable recursive Triggers!!!

Code: SQL  [Select][+][-]

1. CREATE TABLE "VAC2" (
2.         "ID" INTEGER PRIMARY KEY,
3.         "ST" TEXT,
4.         "CO" INTEGER DEFAULT 0,
5.         "OS" INTEGER DEFAULT 0,
6.         "ED" TEXT GENERATED ALWAYS AS (DATE(JulianDay(ST)+CO-1)) STORED,
7.         "DREP" TEXT GENERATED ALWAYS AS (
8.                 CASE strftime('%w',DATE(JulianDay(ED)+1+OS))
9.                         WHEN '6' THEN DATE(JulianDay(ED)+1+OS,'weekday 0')
10.                         WHEN '5' THEN DATE(JulianDay(ED)+1+OS,'weekday 0')
11.                         ELSE DATE(JulianDay(ED)+1+OS)
12.                         END) VIRTUAL
13. );
14. CREATE TABLE "OCA" (
15.         "ID"    INTEGER PRIMARY KEY,
16.         "DEF"   TEXT,
17.         "ST"    TEXT
18. );

Code: SQL  [Select][+][-]

1. CREATE TRIGGER "trgVACAftIns" AFTER INSERT ON "VAC2"
2. FOR EACH ROW
3. WHEN EXISTS (SELECT DATE(O.ST) FROM OCA AS O
4.                          INNER JOIN VAC2 AS V ON V.ID=NEW.ID
5.                          WHERE DATE(O.ST)=(CASE strftime('%w',DATE(JulianDay(NEW.ED)+1))
6.                          WHEN '6' THEN DATE(JulianDay(NEW.ED)+1,'weekday 0')
7.                          WHEN '5' THEN DATE(JulianDay(NEW.ED)+1,'weekday 0')
8.                          ELSE DATE(JulianDay(NEW.ED)+1)
9.                          END))
10.         BEGIN
11.                         UPDATE VAC2 SET OS=1 WHERE ID=NEW.ID;
12.         END;
13. CREATE TRIGGER "trgVACAftUpd" AFTER UPDATE ON "VAC2"
14. FOR EACH ROW
15. WHEN EXISTS (SELECT DATE(O.ST) FROM OCA AS O
16.                          INNER JOIN VAC2 AS V ON V.ID=NEW.ID
17.                          WHERE DATE(O.ST)=(CASE strftime('%w',DATE(JulianDay(NEW.ED)+1+NEW.OS))
18.                          WHEN '6' THEN DATE(JulianDay(NEW.ED)+1+NEW.OS,'weekday 0')
19.                          WHEN '5' THEN DATE(JulianDay(NEW.ED)+1+NEW.OS,'weekday 0')
20.                          ELSE DATE(JulianDay(NEW.ED)+1+NEW.OS)
21.                          END)
22.                          )
23.         BEGIN
24.                         UPDATE VAC2 SET OS=OS+1 WHERE ID=NEW.ID;
25.         END;

Adding three holidays, with 2 consecutive holidays

Code: SQL  [Select][+][-]

1. INSERT INTO OCA(DEF,ST) VALUES('Holiday1','2023-07-23');
2. INSERT INTO OCA(DEF,ST) VALUES('Holiday2','2023-07-24');
3. INSERT INTO OCA(DEF,ST) VALUES('Holiday3','2023-07-27');
4.  
5. INSERT INTO VAC2(ST,CO) VALUES('2023-07-01',18);
6. INSERT INTO VAC2(ST,CO) VALUES('2023-07-02',18);
7. INSERT INTO VAC2(ST,CO) VALUES('2023-07-03',18);
8. INSERT INTO VAC2(ST,CO) VALUES('2023-07-04',18);
9. INSERT INTO VAC2(ST,CO) VALUES('2023-07-05',18);
10. INSERT INTO VAC2(ST,CO) VALUES('2023-07-06',18);
11. INSERT INTO VAC2(ST,CO) VALUES('2023-07-07',18);
12. INSERT INTO VAC2(ST,CO) VALUES('2023-07-08',18);
13. INSERT INTO VAC2(ST,CO) VALUES('2023-07-09',18);
14. INSERT INTO VAC2(ST,CO) VALUES('2023-07-10',18);
15. INSERT INTO VAC2(ST,CO) VALUES('2023-07-11',18);
16. INSERT INTO VAC2(ST,CO) VALUES('2023-07-12',18);
17. INSERT INTO VAC2(ST,CO) VALUES('2023-07-13',18);
18. INSERT INTO VAC2(ST,CO) VALUES('2023-07-14',18);
19. INSERT INTO VAC2(ST,CO) VALUES('2023-07-15',18);

Database attached

Please check if DREP is correct

[Zvoni]

Just noticed something to complicate it even further:
Usually non working days are not counted for vacation days.
Do you have to respect that?

EDIT: just had an idea based on your holiday table to maybe slim everything down.
Will have to think on it

[alpine]

Just noticed something to complicate it even further:
Usually non working days are not counted for vacation days.
Do you have to respect that?

Not to say that in some rare occasions the authorities may decide to extend a holiday (sole day remaining in the working week) in behalf of some later date.
I have a table where any date can be overridden and I keep it resident in memory all the time. A function returns the type of the given day if it is listed as an exception, else simply checks the weekday number.

[kito]

Please check if DREP is correct

I am truly grateful for your time, efforts.
when I try to connect to the database  via Zconnection (Zeos comp ) error message displays as :Error SQL : database disk image  is malformed
 

Code: Pascal  [Select][+][-]

1. Usually non working days are not counted for vacation days.
2. Do you have to respect that?

Nope  but in the case of having a medical certificate, the vacation days during its validity are not counted, and the vacation must be extended according to the number of days in the certificate

[Zvoni]

Please check if DREP is correct

I am truly grateful for your time, efforts.
when I try to connect to the database  via Zconnection (Zeos comp ) error message displays as :Error SQL : database disk image  is malformed

OUCH!!
Which SQLite-Version do you have?
As i said: It requires 3.31 or later

Can anyone else confirm?

Have you tried build a complte new Database with my SQL's above?
Remember: You need to activate recursive Triggers in your Conncetion
https://www.sqlite.org/pragma.html#pragma_recursive_triggers

Code: Pascal  [Select][+][-]

1. Usually non working days are not counted for vacation days.
2. Do you have to respect that?

Nope  but in the case of having a medical certificate, the vacation days during its validity are not counted, and the vacation must be extended according to the number of days in the certificate

Really?
Weird. But not my Problem