I'm working on a complex task which is giving me serious reasonings.
I developed a logic according to which by selecting/check and/or valuing the input of a series of checkboxes and textfields, a filter string (cTextSearch) is composed which is then passed to a method (set Filter) which then essentially applies a SET FILTER TO to a TableAlias. So far so good... now I need to add a discriminant, i.e. I should also check in some points whether this table (cAliasName) contains "expiring" records, i.e. contracts that are expiring or have expired or not... The logic here is a bit different because I need to check before on all records, flag and then filtering the result on the table which theoretically could be filtered for other reason. SO all this need to be integrated with the current system in a way or another.
If I make that different without this query and logic(tried), the string of the SET FILTER TO togheter with the expiring record will become too long and FOX will return me stack error.
For this reason I wrote a procedure that makes a query and returns me a cursor, which I then take from the form (the procedure is an external prg) and would theoretically like to integrate into the current filter if present. In the cursor I flagged in a column with 0 and 1 those records that interest me (0 no 1 expiring).
Once I get to the set filter I'm trying to tell it "Filter the current string and also check whether the record with that contract code (unique CodContr) is valued at 0 or 1 in the cursor, if so include/exclude from the filter).
I don't know how to get out of it, I tried using separate filters or using LOCATE, but I think something is missing in the logic itself or I need a new idea/approach....
The cursor created is visible in the form (checked with BROWSE) and it has this form (sample..) ->
id_contr codcontr codcli email datainiz datafine codlistino listinocli duratamesi importocli autocreate codassegn nofattpec inscadenza
79 00069 000054 xxxxxxx@xxxx.it 01/06/2023 30/06/2026 00002 50 36 50 FALSO 00002 FALSO 0
7 00002 000056 xxxxxxx@xxxx.it 01/01/2022 31/01/2025 00002 50 36 50 FALSO 00002 FALSO 0
61 00052 000107 xxxxxxx@xxxx.it 01/12/2022 31/12/2025 00002 50 36 50 FALSO 00013 FALSO 0
45 00037 000158 xxxxxxx@xxxx.it 01/07/2022 31/07/2025 00002 50 36 50 FALSO 00003 FALSO 0
42 00034 000235 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00002 FALSO 1
40 00032 000332 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00002 VERO 1
94 00084 000354 xxxxxxx@xxxx.it 01/10/2023 31/10/2026 00002 50 36 50 FALSO 00002 FALSO 0
134 00119 000364 xxxxxxx@xxxx.it 01/09/2021 30/09/2024 00003 70 36 70 FALSO 00006 FALSO 0
60 00051 000366 xxxxxxx@xxxx.it 01/12/2022 31/12/2025 00002 50 36 50 FALSO 00008 FALSO 0
29 00021 000381 xxxxxxx@xxxx.it 01/06/2022 30/06/2025 00002 50 36 50 FALSO 00002 FALSO 0
82 00072 000632 xxxxxxx@xxxx.it 01/06/2023 30/06/2026 00002 50 36 50 FALSO 00004 FALSO 0
84 00074 000648 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00002 FALSO 1
41 00033 000668 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00011 FALSO 1
<-
The procedure VerifyScadenz:
In the method SetFilter of the form in which I'll filter the table... this is the basic version without the attempts done, so cScadenzeCursorName is the cursor received from procedure, cTextSearch is the string received (will be something like CodCli=1 AND/OR other conditions .... need to filter the string normally but do the comparison in the cursor with the final flag (0/1) that tells me which to take.
I developed a logic according to which by selecting/check and/or valuing the input of a series of checkboxes and textfields, a filter string (cTextSearch) is composed which is then passed to a method (set Filter) which then essentially applies a SET FILTER TO to a TableAlias. So far so good... now I need to add a discriminant, i.e. I should also check in some points whether this table (cAliasName) contains "expiring" records, i.e. contracts that are expiring or have expired or not... The logic here is a bit different because I need to check before on all records, flag and then filtering the result on the table which theoretically could be filtered for other reason. SO all this need to be integrated with the current system in a way or another.
If I make that different without this query and logic(tried), the string of the SET FILTER TO togheter with the expiring record will become too long and FOX will return me stack error.
For this reason I wrote a procedure that makes a query and returns me a cursor, which I then take from the form (the procedure is an external prg) and would theoretically like to integrate into the current filter if present. In the cursor I flagged in a column with 0 and 1 those records that interest me (0 no 1 expiring).
Once I get to the set filter I'm trying to tell it "Filter the current string and also check whether the record with that contract code (unique CodContr) is valued at 0 or 1 in the cursor, if so include/exclude from the filter).
I don't know how to get out of it, I tried using separate filters or using LOCATE, but I think something is missing in the logic itself or I need a new idea/approach....
The cursor created is visible in the form (checked with BROWSE) and it has this form (sample..) ->
id_contr codcontr codcli email datainiz datafine codlistino listinocli duratamesi importocli autocreate codassegn nofattpec inscadenza
79 00069 000054 xxxxxxx@xxxx.it 01/06/2023 30/06/2026 00002 50 36 50 FALSO 00002 FALSO 0
7 00002 000056 xxxxxxx@xxxx.it 01/01/2022 31/01/2025 00002 50 36 50 FALSO 00002 FALSO 0
61 00052 000107 xxxxxxx@xxxx.it 01/12/2022 31/12/2025 00002 50 36 50 FALSO 00013 FALSO 0
45 00037 000158 xxxxxxx@xxxx.it 01/07/2022 31/07/2025 00002 50 36 50 FALSO 00003 FALSO 0
42 00034 000235 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00002 FALSO 1
40 00032 000332 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00002 VERO 1
94 00084 000354 xxxxxxx@xxxx.it 01/10/2023 31/10/2026 00002 50 36 50 FALSO 00002 FALSO 0
134 00119 000364 xxxxxxx@xxxx.it 01/09/2021 30/09/2024 00003 70 36 70 FALSO 00006 FALSO 0
60 00051 000366 xxxxxxx@xxxx.it 01/12/2022 31/12/2025 00002 50 36 50 FALSO 00008 FALSO 0
29 00021 000381 xxxxxxx@xxxx.it 01/06/2022 30/06/2025 00002 50 36 50 FALSO 00002 FALSO 0
82 00072 000632 xxxxxxx@xxxx.it 01/06/2023 30/06/2026 00002 50 36 50 FALSO 00004 FALSO 0
84 00074 000648 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00002 FALSO 1
41 00033 000668 xxxxxxx@xxxx.it 01/06/2023 30/06/2024 00001 20 12 20 FALSO 00011 FALSO 1
<-
The procedure VerifyScadenz:
Code:
PROCEDURE Verifica_Scadenze_PEC()
LOCAL crTemp, nPecDaysalert, nRecordScadenz, dToday, dStartDate, dEndDate, bHasScadenz, cFilter, crTempCursor
nPecDaysalert = oApp.oConfig.PecDaysalert
dToday = DATE()
dStartDate = dToday - nPecDaysalert
dEndDate = dToday + nPecDaysalert
crTemp = SYS(2015)
TableOpen("ContrattiPEC")
TableOpen("Clienti")
SET STEP ON
SELECT ContrattiPEC.*, ;
IIF(ContrattiPEC.DataFine < dToday OR ;
(ContrattiPEC.DataFine >= dStartDate AND ;
ContrattiPEC.DataFine <= dEndDate), 1, 0) AS InScadenza ;
FROM DbContratti!ContrattiPEC ;
INNER JOIN DbContratti!Clienti ;
ON ContrattiPEC.CodCli = Clienti.CodCli ;
WHERE NOT Clienti.DisPec ;
AND ContrattiPEC.DataFine = ;
(SELECT MAX(DataFine) ;
FROM DbContratti!ContrattiPEC c2 ;
WHERE c2.CodCli = ContrattiPEC.CodCli) ;
INTO CURSOR (crTemp) READWRITE
nRecordScadenz = RECCOUNT(crTemp)
IF nRecordScadenz > 0
cUniqueName = "crScadenzePEC_" + ALLTRIM(STR(VAL(SYS(2015)), 10, 0))
SELECT * ;
FROM (crTemp) ;
INTO CURSOR (cUniqueName) READWRITE
SELECT (cUniqueName)
INDEX ON CodCli TAG CodCli
INDEX ON CodCli + DTOS(DataFine) TAG PecScad
bHasScadenz = .T.
ELSE
bHasScadenz = .F.
cUniqueName = ""
ENDIF
RETURN cUniqueName
ENDPROC
In the method SetFilter of the form in which I'll filter the table... this is the basic version without the attempts done, so cScadenzeCursorName is the cursor received from procedure, cTextSearch is the string received (will be something like CodCli=1 AND/OR other conditions .... need to filter the string normally but do the comparison in the cursor with the final flag (0/1) that tells me which to take.
Code:
LPARAMETERS bResetFilter, cScadenzeCursorName
LOCAL cTextSearch, cFinalFilter, nPecDaysalert
cAliasName = ALLTRIM(This.cTableAlias)
ThisForm.bFilterOn = !bResetFilter
ThisForm.lNotvalidfilterdates = bResetFilter
ThisForm.Get_StrFilter(cAliasName, "")
cTextSearch = ThisForm.CfilterWhere
cFinalFilter = cTextSearch
IF !EMPTY(cFinalFilter)
SET FILTER TO &cFinalFilter IN (cAliasName)
ENDIF