topic-36566

Posts: 7318

Joined: Thu Oct 18, 2012 07:17 PM

calculate days

Posted: Mon Jan 07, 2019 12:43 PM

I have a dbf where I save some periods, each period have a different price list
I must calculate numbers of days totals and for each period

Now there are 4 periods but can be more periods

I have two date: dDateFrom and DateTO and I must check if this interval ( dDateFrom and DateTO ) is on each period and calculate the number of days

How I can resolve ?
I explain you
I have to calculate how many days there are compared to the period table shown above

that is, for example:

if I have

dDateFrom 07.07.2018
DateTO 14.08.2018

I would have 39 days of which

low 0 days first record on table
low 0 days second record on table
Average 19 days 3th record on table
high 20 days 4th record on table

I tried but not run

Code (fw): Select all Collapse

Function Test()
Local aListini:= {}
Local nYear :=year(date())

SET DATE ITALIAN
SET DATE FORMAT "dd-mm-yyyy"

USE LISTINI ALIAS LI

DO While !LI->(eof())

AaDd(aListini,{ (LI->CHECK_IN),(LI->CHECK_OUT),LI->GUEST  })

LI->(dbskip())

ENDDO

XBROWSER aListini

dDateFrom:= Ctod( "7/07/2018")
dDateTo  := Ctod("14/08/2018")

Calcolo_Giorni(dDateFrom,dDateTo,aListini)

RETURN NIL




Function Calcolo_Giorni(dDateFrom,dDateTo,aListini)
Local n
 Local aDays:= array(len(alistini))

  For n= 1 to Len(alistini)
       IF dDateFrom >= cTOD(aListini[n][1] )  .and.    dDateTo <= cTOD(aListini[n][2] )
                adays[n] :=1 + ( dDateTo - dDateFrom )
       endif
   Next
xbrowser adays
return nil

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

alerchster

Posts: 137

Joined: Mon Oct 22, 2012 04:43 PM

Re: calculate days

Posted: Mon Jan 07, 2019 04:53 PM

Code (fw): Select all Collapse

#include "fivewin.ch"

Function Test()
Local aListini := {}
Local nYear :=year(date())
Local dDateFrom, dDateTo

SET DATE ITALIAN
SET DATE FORMAT "dd-mm-yyyy"

USE LISTINI ALIAS LI

DO While !LI->(eof())

AaDd(aListini,{ cTod(LI->CHECK_IN),cTod(LI->CHECK_OUT),LI->GUEST,0  })

LI->(dbskip())

ENDDO

XBROWSER aListini

dDateFrom:= Ctod( "7/07/2018")
dDateTo  := Ctod("14/08/2018")

Calcolo_Giorni(dDateFrom,dDateTo,aListini)

RETURN NIL




Function Calcolo_Giorni(dDateFrom,dDateTo,aListini)
 Local d, n
 For d = dDateFrom to dDateTo
    For n=1 to len(aListini)
       IF d >= aListini[n][1] .and. d <= aListini[n][2] 
          alistini[n][4] += 1 
       endif
    Next
 Next
 xbrowser alistini
return nil

Regards

Ing. Anton Lerchster

Silvio.Falconi

Posts: 7318

Joined: Thu Oct 18, 2012 07:17 PM

Re: calculate days

Posted: Tue Jan 08, 2019 09:07 AM

cmq thanks
I saw now that maybe the reasoning is wrong because each price list must have n example periods:
Price list 1 has the periods displayed
Price list 2 has other periods
so I still do not know how to do the procedure

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

alerchster

Posts: 137

Joined: Mon Oct 22, 2012 04:43 PM

Re: calculate days

Posted: Tue Jan 08, 2019 04:13 PM

Code (fw): Select all Collapse

#include "fivewin.ch"

Function Test()
Local aListini := {}
Local nYear :=year(date())
Local dDateFrom, dDateTo, cPriceList

Create_Testfile()

SET DATE ITALIAN
SET DATE FORMAT "dd-mm-yyyy"

USE LISTINI ALIAS LI

DO While !LI->(eof())

AaDd(aListini,{ cTod(LI->CHECK_IN),cTod(LI->CHECK_OUT),LI->GUEST,LI->PRICE_LIST,0  })

LI->(dbskip())

ENDDO

XBROWSER aListini

dDateFrom:= Ctod( "7/07/2018")
dDateTo  := Ctod("14/08/2018")
cPriceList:="PL1"

Calcolo_Giorni(dDateFrom,dDateTo,aListini,cPriceList)

dDateFrom:= Ctod( "07/07/2018")
dDateTo  := Ctod("20/08/2018")
cPriceList:="PL2"

Calcolo_Giorni(dDateFrom,dDateTo,aListini,cPriceList)

RETURN NIL




Function Calcolo_Giorni(dDateFrom,dDateTo,aListini, cPrice_List)
 Local d, n

 //reset days
 For n=1 to len(aListini)
  aListIni[n][5]=0
 next

 For d = dDateFrom to dDateTo
    For n=1 to len(aListini)
       IF aListini[n][4]=cPrice_List .and. d >= aListini[n][1] .and. d <= aListini[n][2] 
          alistini[n][5] += 1 
       endif
    Next
 Next

 // show result in col 5
 xbrowser alistini
return nil


function Create_Testfile()
local aFields := { { "CHECK_IN", "C", 10, 0 },;
                   { "CHECK_OUT", "C", 10, 0 },;
                   { "GUEST", "C", 10, 0 },;
                   { "PRICE_LIST", "C", 10, 0 } }

REQUEST DBFCDX

DbCreate( "LISTINI.dbf", aFields, "DBFCDX" )

USE LISTINI.dbf NEW VIA "DBFCDX"

// generate periods for pricelist 1
appe blank
field->check_in:="17/05/2018"
field->check_out:="27/06/2018"
field->guest="BASSA"
field->price_list="PL1"

appe blank
field->check_in:="30/08/2018"
field->check_out:="20/09/2018"
field->guest="BASSA"
field->price_list="PL1"

appe blank
field->check_in:="28/06/2018"
field->check_out:="25/07/2018"
field->guest="MEDIA"
field->price_list="PL1"

appe blank
field->check_in:="26/07/2018"
field->check_out:="29/08/2018"
field->guest="ALTA"
field->price_list="PL1"

// generate periods for pricelist 2
appe blank
field->check_in:="01/05/2018"
field->check_out:="31/05/2018"
field->guest="BASSA"
field->price_list="PL2"

appe blank
field->check_in:="01/06/2018"
field->check_out:="30/06/2018"
field->guest="ALTA"
field->price_list="PL2"


appe blank
field->check_in:="15/08/2018"
field->check_out:="29/09/2018"
field->guest="BASSA"
field->price_list="PL2"

appe blank
field->check_in:="01/07/2018"
field->check_out:="31/07/2018"
field->guest="MEDIA"
field->price_list="PL2"

appe blank
field->check_in:="01/08/2018"
field->check_out:="14/08/2018"
field->guest="ALTA"
field->price_list="PL2"

close data
return nil

Regards

Ing. Anton Lerchster

Silvio.Falconi

Posts: 7318

Joined: Thu Oct 18, 2012 07:17 PM

Re: calculate days

Posted: Wed Jan 09, 2019 11:38 AM

thanks

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

MGA

Posts: 1286

Joined: Mon Feb 25, 2008 02:54 PM

Re: calculate days

Posted: Wed Jan 09, 2019 08:46 PM

para comparar datas com hora inicial e final
$DOC$
$FUNCNAME$
FT_ELAPSED()
$CATEGORY$
Date/Time
$ONELINER$
Return elapsed time between two days and/or times
$SYNTAX$
FT_ELAPSED([ <dStart> ], [ <dEnd> ], ;
<cTimeStart>, <cTimeEnd>) -> aTimedata
$ARGUMENTS$
<dStart> is any valid date in any date format. Defaults to DATE().
*
<dEnd> is any valid date in any date format. Defaults to DATE().
*
<cTimeStart> is a valid Time string of the format 'hh:mm:ss' where
hh is hours in 24-hour format.
*
<cTimeEnd> is a valid Time string of the format 'hh:mm:ss' where
hh is hours in 24-hour format.
$RETURNS$
A two-dimensional array containing elapsed time data.
$DESCRIPTION$
FT_ELAPSED() calculates the elapsed time between two Date/Time events.
*
It returns an array which contains the following data:
*
aRetVal[1,1] Integer Days aRetVal[1,2] Total Days (nn.nnnn)
aRetVal[2,1] Integer Hours aRetVal[2,2] Total Hours (nn.nnnn)
aRetVal[3,1] Integer Minutes aRetVal[3,2] Total Minutes (nn.nnnn)
aRetVal[4,1] Integer Seconds aRetVal[4,2] Total Seconds (nn)
$EXAMPLES$
FT_ELAPSED(CTOD('11/28/90'), CTOD('11/30/90'), '08:00:00', '12:10:30')
will return:
*
aRetVal[1,1] -> 2 (Days) aRetVal[1,2] -> 2.1740 Days
aRetVal[2,1] -> 4 (Hours) aRetVal[2,2] -> 52.1750 Hours
aRetVal[3,1] -> 10 (Minutes) aRetVal[3,2] -> 3130.5000 Minutes
aRetVal[4,1] -> 30 (Seconds) aRetVal[4,2] -> 187830 Seconds
$END$

FUNCTION FT_ELAPSED(dStart, dEnd, cTimeStart, cTimeEnd)
LOCAL nTotalSec, nCtr, nConstant, nTemp, aRetVal[4,2]

IF ! ( VALTYPE(dStart) $ 'DC' )
dStart := DATE()
ELSEIF VALTYPE(dStart) == 'C'
cTimeStart := dStart
dStart := DATE()
ENDIF

IF ! ( VALTYPE(dEnd) $ 'DC' )
dEnd := DATE()
ELSEIF VALTYPE(dEnd) == 'C'
cTimeEnd := dEnd
dEnd := DATE()
ENDIF

IF( VALTYPE(cTimeStart) != 'C', cTimeStart := '00:00:00', )
IF( VALTYPE(cTimeEnd) != 'C', cTimeEnd := '00:00:00', )

nTotalSec := (dEnd - dStart) * 86400 + ;
VAL(cTimeEnd) * 3600 + ;
VAL(SUBSTR(cTimeEnd,AT(':', cTimeEnd)+1,2)) * 60 + ;
IF(RAT(':', cTimeEnd) == AT(':', cTimeEnd), 0, ;
VAL(SUBSTR(cTimeEnd,RAT(':', cTimeEnd)+1))) - ;
VAL(cTimeStart) * 3600 - ;
VAL(SUBSTR(cTimeStart,AT(':', cTimeStart)+1,2)) * 60 - ;
IF(RAT(':', cTimeStart) == AT(':', cTimeStart), 0, ;
VAL(SUBSTR(cTimeStart,RAT(':', cTimeStart)+1)))

nTemp := nTotalSec

FOR nCtr = 1 to 4
nConstant := IF(nCtr == 1, 86400, IF(nCtr == 2, 3600, IF( nCtr == 3, 60, 1)))
aRetVal[nCtr,1] := INT(nTemp/nConstant)
aRetval[nCtr,2] := nTotalSec / nConstant
nTemp -= aRetVal[nCtr,1] * nConstant
NEXT

RETURN aRetVal

ubiratanmga@gmail.com

FWH24.04
BCC7.3
HARBOUR3.2
xMate
Pelles´C
TDolphin

Silvio.Falconi

Posts: 7318

Joined: Thu Oct 18, 2012 07:17 PM

Re: calculate days

Posted: Thu Jan 10, 2019 08:33 AM

Dear Ing. Anton Lerchster,

on my database I have the filed date

if ! File( "periodi.dbf" )

aStructure = { { "date" , "D", 8, 0 },;
{ "rooms_id" , "C", 4, 0 },;
{ "check_in" , "D", 8, 0 },;
{ "check_out", "D", 8, 0 },;
{ "status" , "C", 2, 0 },;
{ "guest" , "C", 30, 0 } }

DBCreate( "periodi", aStructure, "DBFCDX" )

ENDIF

If I made

Code (fw): Select all Collapse

function Create_Testfile()
local aFields     := { { "date"     , "D",   8, 0 },;
                      { "rooms_id" , "C",   4, 0 },;
                      { "check_in" , "D",   8, 0 },;
                      { "check_out", "D",   8, 0 },;
                      { "status"   , "C",   2, 0 },;
                      { "guest"    , "C",  30, 0 } }

REQUEST DBFCDX

DbCreate( "periodi.dbf", aFields, "DBFCDX" )

USE PERIODI.dbf NEW VIA "DBFCDX"

// generate periods for pricelist 1
appe blank
field->check_in:=ctod("17/05/2018")
field->check_out:=ctod("27/06/2018")
field->guest="BASSA"
field->rooms_id="01"

appe blank
field->check_in:=ctod("30/08/2018")
field->check_out:=ctod("20/09/2018")
field->guest="BASSA"
field->rooms_id="01"

appe blank
field->check_in:=ctod("28/06/2018")
field->check_out:=ctod("25/07/2018")
field->guest="MEDIA"
field->rooms_id="01"

appe blank
field->check_in:=ctod("26/07/2018")
field->check_out:=ctod("29/08/2018")
field->guest="ALTA"
field->rooms_id="01"




// generate periods for pricelist 2
appe blank
field->check_in:=ctod("01/05/2018")
field->check_out:=ctod("31/05/2018")
field->guest="BASSA"
field->rooms_id="02"

appe blank
field->check_in:=ctod("01/06/2018")
field->check_out:=ctod("30/06/2018")
field->guest="ALTA"
field->rooms_id="02"


appe blank
field->check_in:=ctod("15/08/2018")
field->check_out:=ctod("29/09/2018")
field->guest="BASSA"
field->rooms_id="02"

appe blank
field->check_in:=ctod("01/07/2018")
field->check_out:=ctod("31/07/2018")
field->guest="MEDIA"
field->rooms_id="02"

appe blank
field->check_in:=ctod("01/08/2018")
field->check_out:=ctod("14/08/2018")
field->guest="ALTA"
field->rooms_id="02"
close data
return nil

room_id is the code of price_list sample "01"

It not appe nothing why

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

Silvio.Falconi

Posts: 7318

Joined: Thu Oct 18, 2012 07:17 PM

Re: calculate days

Posted: Thu Jan 10, 2019 08:59 AM

Now run I add

SET DATE FORMAT "dd/mm/yyyy"
SET DATE ITALIAN
SET CENTURY ON
SET EPOCH TO ( YEAR( DATE() ) - 50 )

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

FiveTech Support Forums

calculate days

Re: calculate days

Re: calculate days

Re: calculate days

Re: calculate days

Re: calculate days

Re: calculate days

Re: calculate days

Continue the discussion